You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
DS7/DSWeb/Storage/DAL/MsWMSDAL.cs

438 lines
17 KiB
C#

2 years ago
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.Areas.CommMng.Models;
using DSWeb.Areas.Storage.Models;
namespace DSWeb.Storage.DAL
{
public class MsWMSDAL
{
#region 查询入库
static public List<IndoModel> GetIndoDataList(string mblno, string condition = null, string sort = null, string order = null)
{
var strSql = new StringBuilder();
strSql.AppendLine(" SELECT BSNO,MBLNO,DODATE,GOODSNAME,PKGS,KGS,CHANGETYPE,CNTRNO ");
strSql.AppendLine(" FROM VW_OP_REP_WMSINDO S");
strSql.AppendLine($" WHERE MBLNO like '%{mblno}%' ");
//if (!hasNoFee)
//{
// strSql.AppendLine("AND (SELECT COUNT(1) FROM CH_FEE WHERE FEETYPE=1 AND FEESTATUS IN (0,8,9) AND CUSTOMERNAME=S.CUSTOMERNAME AND BSNO=S.BSNO)>0");
//}
if (!string.IsNullOrWhiteSpace(condition))
{
var obj = new { OPENID="", MBLNO = "", BSNO = "" };
var jsonObj = Newtonsoft.Json.JsonConvert.DeserializeAnonymousType(condition, obj);
if (!string.IsNullOrWhiteSpace(jsonObj.MBLNO))
{
strSql.Append($" and MBLNO='{jsonObj.MBLNO.Replace("'", "")}'");
}
if (!string.IsNullOrWhiteSpace(jsonObj.BSNO))
{
strSql.Append($" and BSNO='{jsonObj.BSNO.Replace("'", "")}'");
}
if (!string.IsNullOrWhiteSpace(jsonObj.OPENID))
{
var openid = jsonObj.BSNO.Replace("'", "");
strSql.Append($" and CUSTOMERNAME in(select infoclient from Disp_InfoClient where openid='{openid}')");
}
else {
strSql.Append($" and 1=2 ");
}
}
if (!string.IsNullOrEmpty(sort))
{
strSql.AppendLine($" order by {sort} {(string.IsNullOrWhiteSpace(order) ? string.Empty : order)}");
}
else
{
strSql.AppendLine(" order by BSNO desc");
}
return SetData(strSql);
}
private static List<IndoModel> SetData(StringBuilder strSql)
{
var headList = new List<IndoModel>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
IndoModel data = new IndoModel();
#region Set DB data to Object
//data.WMSID = Guid.Parse(reader["WMSID"].ToString());
data.MBLNO = Convert.ToString(reader["MBLNO"]);
data.BSNO = Convert.ToString(reader["BSNO"]);
data.DODATE =reader["DODATE"].ToString();
data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
data.PKGS = reader["PKGS"].ToString();
data.KGS = reader["KGS"].ToString();
data.CHANGETYPE = reader["CHANGETYPE"].ToString();
data.CNTRNO = reader["CNTRNO"].ToString();
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region 查询出库
static public List<OutdoModel> GetOutdoDataList(string mblno, string condition = null, string sort = null, string order = null)
{
var strSql = new StringBuilder();
strSql.AppendLine("SELECT WMSNO,MBLNO,DODATE,GOODSNAME,PKGS,KGS,CHANGETYPE,CNTRNO");
strSql.AppendLine("FROM VW_OP_REP_WMSOUTDO S");
strSql.AppendLine($"WHERE MBLNO like '%{mblno}%' ");
//if (!hasNoFee)
//{
// strSql.AppendLine("AND (SELECT COUNT(1) FROM CH_FEE WHERE FEETYPE=1 AND FEESTATUS IN (0,8,9) AND CUSTOMERNAME=S.CUSTOMERNAME AND BSNO=S.BSNO)>0");
//}
if (!string.IsNullOrWhiteSpace(condition))
{
var obj = new { MBLNO = "", OPENID = "", BSNO = "" };
var jsonObj = Newtonsoft.Json.JsonConvert.DeserializeAnonymousType(condition, obj);
if (!string.IsNullOrWhiteSpace(jsonObj.MBLNO))
{
strSql.Append($" and MBLNO='{jsonObj.MBLNO.Replace("'", "")}'");
}
if (!string.IsNullOrWhiteSpace(jsonObj.BSNO))
{
strSql.Append($" and BSNO='{jsonObj.BSNO.Replace("'", "")}'");
}
if (!string.IsNullOrWhiteSpace(jsonObj.OPENID))
{
var openid = jsonObj.BSNO.Replace("'", "");
strSql.Append($" and CUSTOMERNAME in(select infoclient from Disp_InfoClient where openid='{openid}')");
}
else
{
strSql.Append($" and 1=2 ");
}
}
if (!string.IsNullOrEmpty(sort))
{
strSql.AppendLine($" order by {sort} {(string.IsNullOrWhiteSpace(order) ? string.Empty : order)}");
}
else
{
strSql.AppendLine(" order by WMSNO desc");
}
return SetData2(strSql);
}
private static List<OutdoModel> SetData2(StringBuilder strSql)
{
var headList = new List<OutdoModel>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
OutdoModel data = new OutdoModel();
#region Set DB data to Object
//data.WMSID = Guid.Parse(reader["WMSID"].ToString());
data.MBLNO = Convert.ToString(reader["MBLNO"]);
data.WMSNO = Convert.ToString(reader["WMSNO"]);
data.DODATE = reader["DODATE"].ToString();
data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
data.PKGS = reader["PKGS"].ToString();
data.KGS = reader["KGS"].ToString();
data.CHANGETYPE = reader["CHANGETYPE"].ToString();
data.CNTRNO = reader["CNTRNO"].ToString();
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region 查询库存
static public List<KuCunModel> GetKuCunDataList(string openid, string condition = null, string sort = null, string order = null)
{
//调试用 如openid="" 赋值一个值以便可以继续查询
openid = "ovViM0zP5XX6nMjk4HB--iKGfCfk";
var strSql = new StringBuilder();
strSql.AppendLine("SELECT WMSID,CUSTOMERNAME,CUSTOMNO,GOODSNAME,PKGS_FIRST,PKGS,MBLNO,BSNO,CNTRNO,STOREHOUSENAME,AREANAME,AREACODE,STOREHOUSE,STORAGEUNITCOUNT,INPKGS,INKGS,INDATE,GOODSFEETYPE,GOODSOWNER,KGS");
strSql.AppendLine("FROM VW_OP_WMS_AREAGOODS S");
strSql.AppendLine($"WHERE CUSTOMERNAME in(select infoclient from Disp_InfoClient where openid='{openid}') ");
if (!string.IsNullOrEmpty(condition))
{
strSql.AppendLine(" and " + condition);
}
if (!string.IsNullOrEmpty(sort))
{
strSql.AppendLine($" order by {sort} {(string.IsNullOrWhiteSpace(order) ? string.Empty : order)}");
}
else
{
strSql.AppendLine(" order by BSNO desc");
}
return SetData3(strSql);
}
static public List<KuCunModel> GetDQKuCunDataList(string openid, string condition = null, string sort = null, string order = null)
{
var strSql = new StringBuilder();
strSql.AppendLine("SELECT MBLNO,count(PKGS_FIRST) as PKGS_FIRST,count(PKGS) as PKGS ");
strSql.AppendLine("FROM VW_OP_WMS_AREAGOODS S");
strSql.AppendLine($"WHERE CUSTOMERNAME in(select infoclient from Disp_InfoClient where openid='{openid}')");
if (!string.IsNullOrWhiteSpace(condition)) {
strSql.AppendLine("and "+ condition);
}
strSql.AppendLine(" group by MBLNO ");
return SetData4(strSql);
}
private static List<KuCunModel> SetData4(StringBuilder strSql)
{
var headList = new List<KuCunModel>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
KuCunModel data = new KuCunModel();
#region Set DB data to Object
data.MBLNO = Convert.ToString(reader["MBLNO"]);
data.PKGS = reader["PKGS"].ToString();
data.PKGS_FIRST = reader["PKGS_FIRST"].ToString();
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
private static List<KuCunModel> SetData3(StringBuilder strSql)
{
var headList = new List<KuCunModel>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
KuCunModel data = new KuCunModel();
#region Set DB data to Object
data.WMSID = Guid.Parse(reader["WMSID"].ToString());
data.MBLNO = Convert.ToString(reader["MBLNO"]);
data.BSNO = Convert.ToString(reader["BSNO"]);
data.CUSTOMERNAME = reader["CUSTOMERNAME"].ToString();
data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
data.PKGS = reader["PKGS"].ToString();
data.PKGS_FIRST = reader["PKGS_FIRST"].ToString();
data.CUSTOMNO = reader["CUSTOMNO"].ToString();
data.CNTRNO = reader["CNTRNO"].ToString();
data.STOREHOUSENAME = reader["STOREHOUSENAME"].ToString();
data.AREANAME = reader["AREANAME"].ToString();
data.STOREHOUSE = reader["STOREHOUSE"].ToString();
data.AREACODE = reader["AREACODE"].ToString();
data.STORAGEUNITCOUNT = reader["STORAGEUNITCOUNT"].ToString();
data.INPKGS = reader["INPKGS"].ToString();
data.INKGS = reader["INKGS"].ToString();
data.INDATE = reader["INDATE"].ToString();
data.GOODSFEETYPE = reader["GOODSFEETYPE"].ToString();
data.GOODSOWNER = reader["GOODSOWNER"].ToString();
data.KGS = reader["KGS"].ToString();
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
private static List<KuCunModel> SetData_WUTONG(StringBuilder strSql)
{
var headList = new List<KuCunModel>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
KuCunModel data = new KuCunModel();
#region Set DB data to Object
data.WMSID = Guid.Parse(reader["WMSID"].ToString());
data.MBLNO = Convert.ToString(reader["MBLNO"]);
data.BSNO = Convert.ToString(reader["BSNO"]);
data.CUSTOMERNAME = reader["CUSTOMERNAME"].ToString();
data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
data.PKGS = reader["PKGS"].ToString();
data.PKGS_FIRST = reader["PKGS_FIRST"].ToString();
data.CUSTOMNO = reader["CUSTOMNO"].ToString();
data.CNTRNO = reader["CNTRNO"].ToString();
data.STOREHOUSENAME = reader["STOREHOUSENAME"].ToString();
data.AREANAME = reader["AREANAME"].ToString();
data.STOREHOUSE = reader["STOREHOUSE"].ToString();
data.AREACODE = reader["AREACODE"].ToString();
#endregion
if (headList.Exists(x => x.MBLNO == data.MBLNO && x.GOODSNAME == data.GOODSNAME)) {
var _e = headList.Find(x => x.MBLNO == data.MBLNO && x.GOODSNAME == data.GOODSNAME);
var areanamelist = _e.AREANAME.Split(',');
var needinsert = true;
foreach (var areaname in areanamelist) {
if (areaname == data.AREANAME)
{
needinsert = false;
break;
}
}
if (needinsert) {
_e.AREANAME += "," + data.AREANAME;
}
}
else {
headList.Add(data);
}
}
reader.Close();
}
return headList;
}
static public List<KuCunModel> GetKuCunDataList_WUTONG( string condition = null, string sort = null, string order = null)
{
var strSql = new StringBuilder();
strSql.AppendLine("SELECT WMSID,CUSTOMERNAME,CUSTOMNO,GOODSNAME,PKGS_FIRST,PKGS,MBLNO,BSNO,CNTRNO,STOREHOUSENAME,AREANAME,AREACODE,STOREHOUSE");
strSql.AppendLine("FROM VW_OP_WMS_AREAGOODS S");
strSql.AppendLine($"WHERE 1=1 ");
if (!string.IsNullOrEmpty(condition))
{
strSql.AppendLine(" and " + condition);
}
if (!string.IsNullOrEmpty(sort))
{
strSql.AppendLine($" order by {sort} {(string.IsNullOrWhiteSpace(order) ? string.Empty : order)}");
}
else
{
strSql.AppendLine(" order by BSNO desc");
}
return SetData_WUTONG(strSql);
}
#endregion
public static IndoModel GetOneIndoByWMSID(string wmsid)
{
var strSql = new StringBuilder();
strSql.AppendLine("SELECT * ");
strSql.AppendLine("FROM VW_OP_REP_WMSINDO S");
strSql.AppendLine($"WHERE wmsid='{wmsid}'");
var list = SetData(strSql);
if (list.Count > 0)
{
return list[0];
}
return null;
}
public static OutdoModel GetOneOutdoByWMSID(string wmsid)
{
var strSql = new StringBuilder();
strSql.AppendLine("SELECT * ");
strSql.AppendLine("FROM VW_OP_REP_WMSINDO S");
strSql.AppendLine($"WHERE wmsid='{wmsid}'");
var list = SetData2(strSql);
if (list.Count > 0)
{
return list[0];
}
return null;
}
#region 查询库存
static public List<OP_WMS_STOREHOUSE> GetSTOREHOUSEList(string AREACODE="")
{
var strSql = new StringBuilder();
strSql.AppendLine($"SELECT * from OP_WMS_STOREHOUSE where AREACODE='{AREACODE}'");
return SetData_STOREHOUSE(strSql);
}
static public OP_WMS_STOREHOUSE GetSTOREHOUSE(string AREACODE = "")
{
var strSql = new StringBuilder();
strSql.AppendLine($"SELECT * from OP_WMS_STOREHOUSE where AREACODE='{AREACODE}'");
var _r = SetData_STOREHOUSE(strSql);
if (_r.Count > 0) return _r[0];
return new OP_WMS_STOREHOUSE();
}
private static List<OP_WMS_STOREHOUSE> SetData_STOREHOUSE(StringBuilder strSql)
{
var headList = new List<OP_WMS_STOREHOUSE>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
OP_WMS_STOREHOUSE data = new OP_WMS_STOREHOUSE();
#region Set DB data to Object
data.Id = int.Parse(reader["Id"].ToString());
data.AREACODE = Convert.ToString(reader["AREACODE"]);
data.AREANAME = Convert.ToString(reader["AREANAME"]);
data.MAPHTML = Convert.ToString(reader["MAPHTML"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
}
}