using System; using System.Collections.Generic; using System.Linq; using System.Text; using ICSSoft.Frame.Data.Entity; using ICSSoft.Base.Config.DBHelper; using System.Data; using ICSSoft.Base.Config.AppConfig; using System.Data.SqlClient; using Newtonsoft.Json; using ICSSoft.Entity; using System.Net; using System.IO; using ICSSoft.Entity.AssemVouch; namespace ICSSoft.Frame.Data.DAL { public class ICSWBSSpecailTransferDAL { static string message = System.Configuration.ConfigurationSettings.AppSettings["IsOpen"].ToString(); static string APIURL = System.Configuration.ConfigurationSettings.AppSettings["APIURL"].ToString(); static string IAssemVouch = APIURL + "APIConfirmAssemVouch"; public static void AddAndEdit(List InfoList, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { foreach (ICSWBSSpecialTransfer TransferInfo in InfoList) { var line = new ICSWBSSpecialTransfer(); line.ProjectType = TransferInfo.ProjectType; line.WareHouseCode = TransferInfo.WareHouseCode; line.LotNo = TransferInfo.LotNo; line.BATCHNew = TransferInfo.BATCHNew; line.BATCHOld = TransferInfo.BATCHOld; line.INVCOENew = TransferInfo.INVCOENew; line.INVCOEOld = TransferInfo.INVCOEOld; line.TransferReason = TransferInfo.TransferReason; line.MUSER = TransferInfo.MUSER; line.MUSERNAME = TransferInfo.MUSERNAME; line.MTIME = TransferInfo.MTIME; line.WorkPoint = TransferInfo.WorkPoint; line.EATTRIBUTE1 = TransferInfo.EATTRIBUTE1; db.ICSWBSSpecialTransfer.InsertOnSubmit(line); db.SubmitChanges(); } db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } public static void UpdateTransfer(string ItemCode, string WbsOld, string WbsNew, string TransferQTY, string WareHouse, string dsonn) { string sql = @"select * from ICSWBS WHERE ItemCode='" + ItemCode + "'"; sql += " AND WBSCode='" + WbsNew + "'"; sql = string.Format(sql); DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0]; if (dt.Rows.Count == 0) { sql = @"Insert into ICSWBS VALUES ('" + ItemCode + "','" + WbsNew + "','" + TransferQTY + "','" + WareHouse + "','" + AppConfig.WorkPointCode + "','" + AppConfig.UserId + "','" + AppConfig.UserName + "','" + DateTime.Now + "','')"; } else { sql = @"Update ICSWBS set ItemNumber=ItemNumber+" + TransferQTY + " where WBSCode='" + WbsNew + "'"; } DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql); string strsql = @"Update ICSWBS set ItemNumber=ItemNumber-" + TransferQTY + " where WBSCode='" + WbsOld + "'"; DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, strsql); } #region 非标件WBS转换 public static void UpdateWBS(SqlConnection sqlConnection, SqlTransaction trans, string LotCod, string QtyCode, string IDCode, string dsconn) { decimal LotQTY = 0; string LotNo = ""; string NewLotNo = ""; string cIDCode = ""; string TransID = ""; string invcode = ""; string batch = ""; string MinvId = "";//物料ID string sql = ""; int result = 0; string[] Lotcodelist = LotCod.Split(','); string[] QtyCodeList = QtyCode.Split(','); string[] IDCodeList = IDCode.Split(','); SqlCommand cmd = new SqlCommand(); cmd.Connection = sqlConnection; cmd.Transaction = trans; try { for (int i = 0; i < IDCodeList.Length; i++) { if (cIDCode == "") { cIDCode += "'" + IDCodeList[i] + "'"; } else { cIDCode += ",'" + IDCodeList[i] + "'"; } } sql = @"select C.LotNO ,A.TransNO ,A.TransLine ,B.INVCODE ,C.INVCOENew ,ISNULL(B.LotQty, 0) as LOTQTY ,B.WHCode ,A.VenderLotNO ,C.BATCHNew,ISNULL(A.EATTRIBUTE3, 0.00) EATTRIBUTE3 from ICSWBSSpecialTransfer C LEFT JOIN ICSITEMLot A ON C.LotNo=A.LotNO AND A.WorkPoint=C.WorkPoint LEFT JOIN ICSWareHouseLotInfo B ON A.LotNO=B.LotNO AND A.WorkPoint=B.WorkPoint WHERE C.ID IN ({0}) AND A.WorkPoint='{1}' ORDER BY A.VenderLotNO,B.INVCODE "; sql = string.Format(sql, cIDCode, AppConfig.WorkPointCode); DataTable dw = SQlReturnData(sql, cmd); if (dw == null || dw.Rows.Count <= 0) return; List contextlist = new List(); AssemVouch context = new AssemVouch(); List contextlists = new List(); AssemVouchs contexts = new AssemVouchs(); string cWBC = ""; string cBatch = ""; string transfer = ""; string cInvcode = ""; for (int i = 0; i < dw.Rows.Count; i++) { if (i > 0) { context.list = contextlists; contextlist.Add(context); } context = new AssemVouch(); context.UserCode = AppConfig.UserCode; context.cTVCode = dw.Rows[i]["TransNO"].ToString(); context.cIWhCode = dw.Rows[i]["WHCode"].ToString(); context.cOWhCode = dw.Rows[i]["WHCode"].ToString(); context.UserCode = AppConfig.UserCode; contextlists = new List(); contexts = new AssemVouchs(); //批号 if (dw.Rows[i]["BATCHNew"].ToString() == "") { contexts.cTVBatchAfter = dw.Rows[i]["VenderLotNO"].ToString(); } else { contexts.cTVBatchAfter = dw.Rows[i]["BATCHNew"].ToString(); } contexts.cTVBatchBefore = dw.Rows[i]["VenderLotNO"].ToString(); //料品 if (dw.Rows[i]["INVCOENew"].ToString() == "") { contexts.cInvCodeAfter = dw.Rows[i]["INVCODE"].ToString(); } else { contexts.cInvCodeAfter = dw.Rows[i]["INVCOENew"].ToString(); } contexts.cInvCodeBefore = dw.Rows[i]["INVCODE"].ToString(); contexts.iQuantity = Convert.ToDecimal(dw.Rows[i]["LOTQTY"].ToString()); contexts.iNum = Convert.ToDecimal(dw.Rows[i]["LOTQTY"].ToString()) * Convert.ToDecimal(dw.Rows[i]["EATTRIBUTE3"].ToString()); contexts.cWhCode = dw.Rows[i]["WHCode"].ToString(); contexts.bAVType = "转换"; contextlists.Add(contexts); if (i == dw.Rows.Count - 1) { context.list = contextlists; contextlist.Add(context); } } sql = @"UPDATE ICSWBSSpecialTransfer SET LotStatus = '已审核' WHERE ID IN ({0}) AND WorkPoint = '{1}' "; sql = string.Format(sql, cIDCode, AppConfig.WorkPointCode); cmd.CommandText = sql; result = cmd.ExecuteNonQuery(); if (result <= 0) { throw new Exception("审核状态更新失败！"); } for (int i = 0; i < IDCodeList.Length; i++) { LotNo = Lotcodelist[i]; TransID = IDCodeList[i]; LotQTY = Convert.ToDecimal(QtyCodeList[i]); string sqltr = @"select LotNo,NewLotNo,INVCOENew,INVCOEOld,BATCHNew,BATCHOld,WareHouseCode from ICSWBSSpecialTransfer where ID='{0}'AND WorkPoint='{1}'"; sqltr = string.Format(sqltr, TransID, AppConfig.WorkPointCode); DataTable dttr = SQlReturnData(sqltr, cmd); if (dttr != null || dttr.Rows.Count != 0) { invcode = dttr.Rows[0]["INVCOENew"].ToString(); batch = dttr.Rows[0]["BATCHNew"].ToString(); NewLotNo = dttr.Rows[0]["NewLotNo"].ToString(); } #region 物料更新 if (invcode != "") { string WHGUID = ""; string WHCode = ""; string BinGUID = ""; string BinCode = ""; string INVGUID = ""; string INVCode = ""; sql = @"select ID,INVCODE from ICSINVENTORY where INVCODE='{0}'AND WorkPoint='{1}'"; sql = string.Format(sql, invcode, AppConfig.WorkPointCode); DataTable dtc = DBHelper.ExecuteDataset(AppConfig.GetDataBaseConnectStringByKey("[DB.SYS]"), CommandType.Text, sql).Tables[0]; if (dtc.Rows.Count > 0) { MinvId = dtc.Rows[0]["ID"].ToString(); } string sqlv = @"select * from ICSWareHouseLotInfo where lotNO='{0}'AND WorkPoint='{1}'"; sqlv = string.Format(sqlv, LotNo, AppConfig.WorkPointCode); DataTable dtv = SQlReturnData(sqlv, cmd); if (dtv != null || dtv.Rows.Count != 0) { WHGUID = dtv.Rows[0]["WHGUID"].ToString(); WHCode = dtv.Rows[0]["WHCode"].ToString(); BinGUID = dtv.Rows[0]["BinGUID"].ToString(); BinCode = dtv.Rows[0]["BinCode"].ToString(); INVGUID = dtv.Rows[0]["INVGUID"].ToString(); INVCode = dtv.Rows[0]["INVCode"].ToString(); } //原库存主表 string sqls = @"select * from ICSWareHouseInfo where INVGUID='{0}' AND WHGUID='{1}' AND BinGUID='{2}' AND WorkPoint='{3}'"; sqls = string.Format(sqls, INVGUID, WHGUID, BinGUID, AppConfig.WorkPointCode); DataTable dt = SQlReturnData(sqls, cmd); //目标库存主表 string sqly = @"select * from ICSWareHouseInfo where INVGUID='{0}' AND WHGUID='{1}' AND BinGUID='{2}' AND WorkPoint='{3}'"; sqly = string.Format(sqly, MinvId, WHGUID, BinGUID, AppConfig.WorkPointCode); DataTable dy = SQlReturnData(sqly, cmd); //更新原库存数据 if (dt.Rows.Count != 0) { #region 更新原条码ICSWareHouseLotInfo表 string sqln = @"Update ICSWareHouseLotInfo SET LotQty=0 WHERE LotNO='" + LotNo + "' AND WorkPoint='" + AppConfig.WorkPointCode + "'"; sqln = string.Format(sqln); cmd.CommandText = sqln; result = cmd.ExecuteNonQuery(); if (result <= 0) { throw new Exception("库存物料更新失败！"); } #endregion string sqlw = @"Update ICSWareHouseInfo SET QTY=QTY-{0} where INVGUID='{1}' AND WHGUID='{2}' AND BinGUID='{3}' AND WorkPoint='{4}'"; sqlw = string.Format(sqlw, LotQTY, INVGUID, WHGUID, BinGUID, AppConfig.WorkPointCode); cmd.CommandText = sqlw; result = cmd.ExecuteNonQuery(); if (result <= 0) { throw new Exception("库存主表物料更新失败！"); } #region 存入ICSWareHouseLotInfoLog表（日志） sql = @"insert into ICSWareHouseLotInfoLog Values (NEWID(),'{0}','{1}','{2}','{3}','{9}','{10}','','' ,{4},null,'{5}','形态转换前','{6}','{7}',GETDATE(),'{8}','','','','','未回写')"; sql = string.Format(sql, "", "", INVCode, LotNo, LotQTY, "形态", AppConfig.UserCode, AppConfig.UserName, AppConfig.WorkPointCode, WHCode, BinCode); cmd.CommandText = sql; result = cmd.ExecuteNonQuery(); if (result <= 0) { throw new Exception("日志表存入失败！"); } #endregion } //更新目标库存数据 if (dy.Rows.Count != 0) { string sqlh = @"Update ICSWareHouseInfo SET QTY=QTY+{0} where INVGUID='{1}' AND WHGUID='{2}' AND BinGUID='{3}' AND WorkPoint='{4}'"; sqlh = string.Format(sqlh, LotQTY, MinvId, WHGUID, BinGUID, AppConfig.WorkPointCode); cmd.CommandText = sqlh; result = cmd.ExecuteNonQuery(); if (result <= 0) { throw new Exception("库存主表物料更新失败！"); } #region 存入ICSWareHouseLotInfo表 sql = @"insert into ICSWareHouseLotInfo Values (NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},GETDATE(),'{9}','{10}','{11}',GETDATE(),'{13}','','')"; sql = string.Format(sql, NewLotNo, WHGUID, WHCode, BinGUID, BinCode, MinvId, invcode, LotQTY, DateTime.Now, AppConfig.WorkPointCode, AppConfig.UserCode, AppConfig.UserName, DateTime.Now, "形态转换", 0); cmd.CommandText = sql; result = cmd.ExecuteNonQuery(); if (result <= 0) { throw new Exception("库存物料更新失败！"); } #endregion #region 存入ICSWareHouseLotInfoLog表（日志） sql = @"insert into ICSWareHouseLotInfoLog Values (NEWID(),'{0}','{1}','{2}','{3}','{9}','{10}','','' ,{4},null,'{5}','形态转换后','{6}','{7}',GETDATE(),'{8}','','','','','未回写')"; sql = string.Format(sql, "", "", INVCode, NewLotNo, LotQTY, "形态", AppConfig.UserCode, AppConfig.UserName, AppConfig.WorkPointCode, WHCode, BinCode); cmd.CommandText = sql; result = cmd.ExecuteNonQuery(); if (result <= 0) { throw new Exception("日志表存入失败！"); } #endregion } //更新目标库存数据 if (dy.Rows.Count == 0) { #region 存入ICSWareHouseLotInfo表 sql = @"insert into ICSWareHouseLotInfo Values (NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},GETDATE(),'{9}','{10}','{11}',GETDATE(),'{13}','','')"; sql = string.Format(sql, NewLotNo, WHGUID, WHCode, BinGUID, BinCode, MinvId, invcode, LotQTY, DateTime.Now, AppConfig.WorkPointCode, AppConfig.UserCode, AppConfig.UserName, DateTime.Now, "形态转换", 0); cmd.CommandText = sql; result = cmd.ExecuteNonQuery(); if (result <= 0) { throw new Exception("库存物料更新失败！"); } #endregion #region 更新原条码ICSWareHouseLotInfo表 string sqln = @"Update ICSWareHouseLotInfo SET LotQty=0 WHERE LotNO='" + LotNo + "' AND WorkPoint='" + AppConfig.WorkPointCode + "'"; sqln = string.Format(sqln); cmd.CommandText = sqln; result = cmd.ExecuteNonQuery(); if (result <= 0) { throw new Exception("库存物料更新失败！"); } #endregion #region 存入ICSWareHouseInfo表 sql = @"insert into ICSWareHouseInfo Values ('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}','')"; sql = string.Format(sql, WHGUID, WHCode, BinGUID, BinCode, MinvId, invcode, LotQTY, AppConfig.WorkPointCode, AppConfig.UserCode, AppConfig.UserName, DateTime.Now); cmd.CommandText = sql; result = cmd.ExecuteNonQuery(); if (result <= 0) { throw new Exception("库存主表存入失败！"); } #endregion #region 存入ICSWareHouseLotInfoLog表（日志） sql = @"insert into ICSWareHouseLotInfoLog Values (NEWID(),'{0}','{1}','{2}','{3}','{9}','{10}','','' ,{4},null,'{5}','形态转换后','{6}','{7}',GETDATE(),'{8}','','','','','未回写')"; sql = string.Format(sql, "", "", invcode, NewLotNo, LotQTY, "形态", AppConfig.UserCode, AppConfig.UserName, AppConfig.WorkPointCode, WHCode, BinCode); cmd.CommandText = sql; result = cmd.ExecuteNonQuery(); if (result <= 0) { throw new Exception("日志表存入失败！"); } #endregion } } #endregion #region 批次更新 // if (batch!="") // { // sql = @"Update ICSITEMLot // SET VenderLotNO='" + batch + "' WHERE LotNO='" + LotNo + "'AND WorkPoint='" + AppConfig.WorkPointCode + "'"; // sql = string.Format(sql); // cmd.CommandText = sql; // result = cmd.ExecuteNonQuery(); // if (result <= 0) // { // throw new Exception("条码批次更新失败！"); // } // } #endregion } if (message == "true") { #region 接口调用 string Istr = JsonConvert.SerializeObject(contextlist); string iresult = HttpPost(IAssemVouch, Istr); Result INVDismResult = new Result(); INVDismResult = JsonConvert.DeserializeObject(iresult); if (INVDismResult.code != "200") { throw new Exception(INVDismResult.msg); } } } catch (Exception ex) { throw new Exception(ex.Message); } #endregion } #endregion #region 更新WMS public static void UpdateTransWMS(SqlConnection sqlConnection, SqlTransaction trans, List InfoList, List lotList, string dsconn) { string sql = ""; int result = 0; FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { foreach (ICSWBSSpecialTransfer TransferInfo in InfoList) { bool isNew = false; var line = db.ICSWBSSpecialTransfer.SingleOrDefault(a => a.ID == TransferInfo.ID && a.WorkPoint == AppConfig.WorkPointCode); if (line == null) { isNew = true; line = new ICSWBSSpecialTransfer(); } line.ProjectType = TransferInfo.ProjectType; line.WareHouseCode = TransferInfo.WareHouseCode; line.LotNo = TransferInfo.LotNo; line.WBSCodeOld = TransferInfo.WBSCodeOld; line.WBSCodeNew = TransferInfo.WBSCodeNew; line.TransferReason = TransferInfo.TransferReason; line.INVCOEOld = TransferInfo.INVCOEOld; line.INVCOENew = TransferInfo.INVCOENew; line.BATCHOld = TransferInfo.BATCHOld; line.BATCHNew =TransferInfo.BATCHNew; line.LotStatus = TransferInfo.LotStatus; line.NewLotNo = TransferInfo.NewLotNo; line.MUSER = AppConfig.UserCode; line.MUSERNAME = AppConfig.UserName; line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); line.WorkPoint = AppConfig.WorkPointCode; if (isNew) { db.ICSWBSSpecialTransfer.InsertOnSubmit(line); } db.SubmitChanges(); } foreach (ICSITEMLot info in lotList) { var inv = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.ItemCode && a.WorkPoint == AppConfig.WorkPointCode); if (inv == null) throw new Exception("物料不存在！"); var lines = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == info.LotNO && a.WorkPoint == AppConfig.WorkPointCode); if (lines != null) throw new Exception("条码已存在！"); bool isNew = false; var line = db.ICSITEMLot.SingleOrDefault(a => a.ID == info.ID && a.WorkPoint == AppConfig.WorkPointCode); if (line == null) { isNew = true; line = new ICSITEMLot(); line.ID = AppConfig.GetGuid(); } line.LotNO = info.LotNO; line.ItemCode = info.ItemCode; line.TransNO = info.TransNO; line.TransLine = info.TransLine; line.VENDORITEMCODE = info.VENDORITEMCODE; line.VENDORCODE = info.VENDORCODE; line.VenderLotNO = info.VenderLotNO; line.PRODUCTDATE = info.PRODUCTDATE; line.LOTQTY = info.LOTQTY; line.EATTRIBUTE3 = info.LOTQTY; line.EATTRIBUTE4 = 0; line.ACTIVE = info.ACTIVE; line.Exdate = info.Exdate; line.TYPE = "形态转换"; line.EATTRIBUTE3 = info.EATTRIBUTE3; line.MUSER = AppConfig.UserCode; line.MUSERName = AppConfig.UserName; line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); line.WorkPoint = AppConfig.WorkPointCode; if (isNew) { db.ICSITEMLot.InsertOnSubmit(line); } db.SubmitChanges(); } db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion /// /// 接口调用方法 /// /// /// /// public static string HttpPost(string url, string body) { try { Encoding encoding = Encoding.UTF8; HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url); request.Method = "POST"; request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*"; request.ContentType = "application/json; charset=utf-8"; byte[] buffer = encoding.GetBytes(body); request.ContentLength = buffer.Length; request.GetRequestStream().Write(buffer, 0, buffer.Length); HttpWebResponse response = (HttpWebResponse)request.GetResponse(); using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding)) { return reader.ReadToEnd(); } } catch (WebException ex) { var res = (HttpWebResponse)ex.Response; StringBuilder sb = new StringBuilder(); StreamReader sr = new StreamReader(res.GetResponseStream(), Encoding.UTF8); sb.Append(sr.ReadToEnd()); //string ssb = sb.ToString(); throw new Exception(sb.ToString()); } } public static DataTable SQlReturnData(string SQl, SqlCommand cmd) { DataTable dt = new DataTable(); cmd.CommandText = SQl; SqlDataAdapter dr = new System.Data.SqlClient.SqlDataAdapter(); dr.SelectCommand = cmd; dr.Fill(dt); return dt; } } }