using Newtonsoft.Json.Linq; using Newtonsoft.Json; using NFine.Code; using NFine.Data.Extensions; using NFine.Domain._03_Entity.SRM; using NFine.Repository; using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Configuration; using System.IO; using System.Net; using NFine.Application.WMS; using NFine.Application.Models; using System.Reflection; using NFine.Application.Entity; using NFine.Domain.Entity.SystemSecurity; using System.Xml; using System.Collections; using System.Data.SqlClient; namespace NFine.Application.KBSWMS { public class ICSManufactureReceiveApp : RepositoryFactory { #region 获取工单主表数据 /// /// 获取产品入库单数据 /// /// /// /// public DataTable GetManufactureReceive(string queryJson, ref Pagination jqgridparam) { DataTable dt = new DataTable(); var queryParam = queryJson.ToJObject(); List parameter = new List(); object Figure = GetDecimalDigits(); string wheresql = string.Empty; if (!string.IsNullOrWhiteSpace(queryJson)) { if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString())) { wheresql += " and a.RCVCode like '%" + queryParam["POCode"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString())) { wheresql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString())) { wheresql += " and b.InvName like '%" + queryParam["InvName"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["BatchCode"].ToString())) { wheresql += " and d.BatchCode like '%" + queryParam["BatchCode"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["InvStd"].ToString())) { wheresql += " and b.InvStd like '%" + queryParam["InvStd"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["WHStatus"].ToString())) { string WHStatus = queryParam["WHStatus"].ToString(); if (WHStatus == "0") { wheresql += " and a.Quantity-ISNULL(a.RCVQuantity,0)>0 "; } else { wheresql += " and a.Quantity-ISNULL(a.RCVQuantity,0)=0 "; } } } if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin") { wheresql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'"; } #region [SQL] string sql = $@" SELECT a.ID,a.RCVCode,a.Sequence,a.SourceCode ProductionCode,b.InvCode,b.InvName,b.InvStd,Isnull(a.RCVQuantity,0) RCVQuantity,a.WHCode,a.CreatePerson,a.CreateDateTime,sum(ISNULL(c.Quantity, 0)) BoundQuantity,a.Quantity,case when a.Quantity-ISNULL(a.RCVQuantity,0)=0 then '是' else '否' end IsWH from ICSManufactureReceive a INNER JOIN ICSInventory b ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint LEFT JOIN ICSManufactureReceiveLot c ON a.SourceCode=c.ProductionCode AND a.RCVCode=c.RCVCode AND a.Sequence=c.RCVSequence AND a.WorkPoint=c.WorkPoint AND a.InvCode=c.InvCode LEFT JOIN ICSExtension d ON a.ExtensionID=d.ID AND a.WorkPoint=d.WorkPoint WHERE 1=1 {wheresql} GROUP BY a.RCVCode,a.Sequence,a.SourceCode,b.InvCode,b.InvName,b.InvStd,a.RCVQuantity,a.WHCode,a.CreatePerson,a.CreateDateTime,a.ID,a.Quantity "; #endregion return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); } #endregion #region 获取条码信息 /// /// 获取条码信息 /// /// /// /// /// /// /// public DataTable GetManufactureReceiveLot(string ProductionCode, string WHCode, string InvCode,ref Pagination jqgridparam) { DataTable dt = new DataTable(); List parameter = new List(); string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(','); //多个出库单会有重复数据,需要去重 string sql = $@" SELECT distinct c.ID,c.LotNo,c.Quantity,a.SourceCode ProductionCode,b.InvCode,b.InvName,b.InvStd,c.MUSERName,c.MTIME FROM ICSManufactureReceive a INNER JOIN ICSInventory b ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint INNER JOIN ICSManufactureReceiveLot c ON a.SourceCode=c.ProductionCode AND a.WorkPoint=c.WorkPoint AND a.InvCode=c.InvCode and IsBind='0' WHERE a.SourceCode='{ProductionCode}' AND a.WHCode='{WHCode}' and a.InvCode='{InvCode}' and a.WorkPoint='{WorkPoint}' "; return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); } #endregion #region 入库单绑定条码 /// /// 入库单绑定条码 /// /// /// /// /// public string BindingLot(string ICSASN) { try { var moModels = ICSASN.ToJObject(); string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(','); string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode; string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName; string ids = moModels["IDs"].ToString(); string RCVCode = moModels["RCVCode"].ToString(); string RCVSequence = moModels["RCVSequence"].ToString(); string sql = $" UPDATE ICSManufactureReceiveLot SET IsBind='1' ,RCVCode='{RCVCode}',RCVSequence='{RCVSequence}' WHERE ID IN({ids.TrimEnd(',')}) and WorkPoint='{WorkPoints}' "; int count = SqlHelper.CmdExecuteNonQueryLi(sql); return ""; } catch (Exception ex) { return ex.Message; } } #endregion #region 入库单解绑条码 /// /// 入库单解绑条码 /// /// /// public string PostUnBind(string ids) { try { string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(','); string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode; string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName; var idList = JsonConvert.DeserializeObject>(ids); string sqls = string.Empty; string sql = @"SELECT RCVCode,Sequence FROM ICSManufactureReceive WHERE id IN ('" + string.Join("','", idList) + $"') AND WorkPoint='{WorkPoints}' and Quantity!=isnull(RCVQuantity,0) "; var dt = SqlHelper.CmdExecuteDataTable(sql); if (dt.Rows.Count<=0) { return "所选的入库单绑定的条码已经入库,操作失败!"; } for (int i=0;i< dt.Rows.Count;i++) { sqls += $"UPDATE a SET a.IsBind='0' ,a.RCVCode=null,a.RCVSequence=null from ICSManufactureReceiveLot a LEFT JOIN ICSWareHouseLotInfo b ON a.LOTNO=b.LOTNO AND a.WorkPoint=b.WorkPoint WHERE a.RCVCode='{dt.Rows[i]["RCVCode"]}' and a.RCVSequence='{dt.Rows[i]["Sequence"]}' and a.WorkPoint='{WorkPoints}' and b.id is null "; } int count = SqlHelper.CmdExecuteNonQueryLi(sqls); return ""; } catch (Exception ex) { return ex.Message; } } #endregion public object GetDecimalDigits() { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(','); try { string sql = string.Empty; sql = @"select Figure from ICSConfiguration where Code='Figure001' and Enable='1' and WorkPoint='" + WorkPoint + "'"; object Figure = SqlHelper.ExecuteScalar(sql); return Figure; } catch (Exception ex) { throw new Exception(ex.Message.ToString()); } } #region 入库单提交过账 /// /// 入库单提交过账 /// /// 入库单号 /// public string PostExamine(string RCVCodes) { try { var moModels = RCVCodes.ToList(); string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(','); string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode; string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName; if (moModels.Count <= 0) { throw new Exception("请选择单据信息!"); } List examineViewModels = new List(); List detailEntities = new List(); for (int i = 0; i < moModels.Count; i++) { //验证入库单是否全部绑定条码 string checksql = $@"SELECT a.RCVCode FROM ICSManufactureReceive a INNER JOIN (SELECT RCVCode,WorkPoint,InvCode,Sum(Quantity) Quantity FROM ICSManufactureReceiveLot WHERE IsBind = '1' GROUP BY RCVCode,WorkPoint,InvCode ) b ON a.RCVCode = b.RCVCode AND a.WorkPoint = b.WorkPoint AND a.InvCode = b.InvCode WHERE a.RCVCode = '{moModels[i]}' AND a.Quantity=B.Quantity"; var checkdt = SqlHelper.CmdExecuteDataTable(checksql); if (checkdt.Rows.Count <= 0) { throw new Exception($"单据{moModels[i]}绑定数量小于单据数量,操作失败!"); } //查询入库条码信息 string sql = $@"SELECT a.LotNo, a.RCVCode, a.RCVSequence, a.Quantity,a.InvCode ,a.LocationCode,c.WarehouseCode FROM ICSManufactureReceiveLot a LEFT JOIN ICSLocation b ON a.LocationCode=b.LocationCode AND a.WorkPoint=b.WorkPoint LEFT JOIN ICSWarehouse c ON b.WHID=c.ID WHERE a.RCVCode = '{moModels[i]}' AND a.WorkPoint = '{WorkPoints}' "; var dt = SqlHelper.CmdExecuteDataTable(sql); if (dt.Rows.Count > 0) { for (int j = 0; j < dt.Rows.Count; j++) { DetailEntity detailEntity = new DetailEntity(); detailEntity.LotNo = dt.Rows[j]["LotNo"].ToString(); detailEntity.TransCode = dt.Rows[j]["RCVCode"].ToString(); detailEntity.TransSequence = dt.Rows[j]["RCVSequence"].ToString(); detailEntity.Quantity = dt.Rows[j]["Quantity"].ToString(); detailEntity.InvCode = dt.Rows[j]["InvCode"].ToString(); detailEntity.LocationCode = dt.Rows[j]["LocationCode"].ToString(); detailEntity.WarehouseCode = dt.Rows[j]["WarehouseCode"].ToString(); detailEntities.Add(detailEntity); } } } //根据 var detailEntities1=detailEntities.GroupBy(x => new { x.TransCode, x.TransSequence }).ToList(); detailEntities1.ForEach(infor=> { ExamineViewModel examineViewModel = new ExamineViewModel(); examineViewModel.MTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); examineViewModel.User = MUSER; examineViewModel.WorkPoint = WorkPoints; examineViewModel.TransCode = infor.Key.TransCode; examineViewModel.TransSequence = infor.Key.TransSequence; examineViewModel.Detail = infor.ToList(); examineViewModels.Add(examineViewModel); }); #region 调用PDA入库接口 if (!examineViewModels.IsEmpty()) { string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "LOTStockUp/Create"; var erpinput = JsonConvert.SerializeObject(examineViewModels); string result1 = HttpPost(APIURL, erpinput); JObject Obj = (JObject)JsonConvert.DeserializeObject(result1);//或者JObject jo = JObject.Parse(jsonText); string success = Obj["Success"].ToString(); string message = Obj["Message"].ToString(); if (success.ToUpper() == "FALSE") { throw new Exception(message); } } #endregion return ""; } catch (Exception ex) { return ex.Message; } } #endregion /// /// 删除条码 /// /// /// public string DeleteItemLot(string MOCodes) { string msg = string.Empty; try { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; string sql = $@"SELECT * FROM dbo.ICSMOSubInventoryLot WHERE MOCode IN ({MOCodes}) and WorkPoint in ('{WorkPoint}') and isnull(EATTRIBUTE1,'') !='' "; DataTable dtASN = SqlHelper.GetDataTableBySql(sql); if (dtASN != null && dtASN.Rows.Count > 0) { msg = "所选条码已绑定,无法删除!"; return msg; } if (string.IsNullOrEmpty(msg)) { string sqls = $"DELETE FROM ICSMOSubInventoryLot WHERE MOCode in({MOCodes}) AND WorkPoint in ('{WorkPoint}') "; SqlHelper.CmdExecuteNonQueryLi(sqls); } } catch (Exception ex) { return ex.Message; } return msg; } public string GetSerialCode(string workPointCode, string tbName, string colName, string Pre, int numLen) { string sql = "EXEC Addins_GetSerialCode '{0}','{1}','{2}','{3}',{4}"; sql = string.Format(sql, new object[] { workPointCode, tbName, colName, Pre, numLen }); //return Repository().FindTableBySql(sql.ToString()); return SqlHelper.ExecuteScalar(sql).ToString(); //return DbHelper.ExecuteScalar(CommandType.Text, sql).ToString(); } 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) { throw new Exception(ex.Message); } } /// /// 出库单条码绑定导入 /// /// /// public string UploadFileBinding(String savePath) { //数据获取 try { int index = 1; string msg = ""; string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode; string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName; string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; string MTIME = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); string sql = string.Empty; SqlConnection conn = SqlHelper.GetDataCenterConn(); int count = 0; DataTable data = FileToExcel.ExcelToTable(savePath); var parent = data.DefaultView.ToTable(true, "入库单号", "入库单行号", "生产订单号", "物料编码", "条码", "数量", "库位编码"); parent.PrimaryKey = new DataColumn[] { parent.Columns["column1"], parent.Columns["column2"], parent.Columns["column3"], parent.Columns["column4"], parent.Columns["column5"], parent.Columns["column6"] }; DataTable distinctTable = parent.DefaultView.ToTable(true); foreach (DataRow distinctds in distinctTable.Rows) { string ID = Guid.NewGuid().ToString(); index++; string RCVCode = distinctds["入库单号"].ToString().Trim(); string RCVSequence = distinctds["入库单行号"].ToString().Trim(); string ProductionCode = distinctds["生产订单号"].ToString().Trim(); string InvCode = distinctds["物料编码"].ToString().Trim(); string LotNo = distinctds["条码"].ToString().Trim(); string Quantity = distinctds["数量"].ToString().Trim(); string LocationCode = distinctds["库位编码"].ToString().Trim(); #region 判断必填项 if (ProductionCode == "" || ProductionCode == null) { throw new Exception("第 " + index + " 行生产订单号不能为空!"); } if (InvCode == "" || InvCode == null) { throw new Exception("第 " + index + " 行物料编码不能为空!"); } if (LotNo == "" || LotNo == null) { throw new Exception("第 " + index + " 行条码不能为空!"); } if (Quantity == "" || Quantity == null) { throw new Exception("第 " + index + " 行数量不能为空!"); } if (LocationCode == "" || LocationCode == null) { throw new Exception("第 " + index + " 行库位编码不能为空!"); } #endregion sql += $@" IF NOT EXISTS(SELECT ID FROM ICSLocation where LocationCode='{LocationCode}' and Workpoint='{WorkPoint}') BEGIN RAISERROR('第{index}行填写的库位{LocationCode}不存在,绑定失败', 16, 1); RETURN END INSERT INTO [dbo].[ICSInventoryLot] ([ID], [LotNo], [InvCode], [ProductDate], [ExpirationDate], [Quantity], [Amount], [ExtensionID], [Type], [PrintTimes], [LastPrintUser], [LastPrintTime], [MUSER], [MUSERName], [MTIME], [WorkPoint]) SELECT NEWID(), N'{LotNo}', N'{InvCode}', '{MTIME}', case when EffectiveEnable='1' THEN DATEADD(day,EffectiveDays,GETDATE()) else '2099-12-31' END, {Quantity}, 0.000000, (SELECT ID FROM ICSExtension WHERE Colspan='~~~~~~~~~~~~~'), N'9', NULL, NULL, NULL, N'{MUSER}', N'{MUSERNAME}', '{MTIME}', N'KC47' FROM ICSInventory WHERE InvCode='{InvCode}' AND WorkPoint='{WorkPoint}' ; INSERT INTO [dbo].[ICSInventoryLotDetail] ([LotNo], [TransID], [TransCode], [TransSequence], [MUSER], [MUSERName], [MTIME], [WorkPoint]) SELECT N'{LotNo}', ID, MOCode, Sequence,'{MUSER}', N'{MUSERNAME}', '{MTIME}', N'{WorkPoint}' FROM ICSMO WHERE MOCode='{ProductionCode}' AND Sequence='1' AND WorkPoint='{WorkPoint}' ; INSERT INTO [dbo].[ICSManufactureReceiveLot] ([ID], [ProductionCode], [ProductionSequence], [RCVCode], [RCVSequence], [LotNo], [InvCode], [WarehouseCode], [LocationCode], [Quantity], [Memo], [IsBind], [MUSER], [MUSERName], [MTIME], [WorkPoint]) SELECT NEWID(), N'{ProductionCode}', N'1', N'', N'', N'{LotNo}', N'{InvCode}', a.WarehouseCode, N'{LocationCode}', '{Quantity}', NULL, '0', N'{MUSER}', N'{MUSERNAME}', '{MTIME}', N'{WorkPoint}' FROM ICSWarehouse a INNER JOIN ICSLocation b ON a.Id=b.WHID AND a.workpoint=b.WorkPoint where b.LocationCode='{LocationCode}' and a.Workpoint='{WorkPoint}'; "; if (RCVCode != "" && RCVCode != null && RCVSequence != "" && RCVSequence != null) { sql += $@" IF NOT EXISTS(SELECT a.ID FROM ICSManufactureReceive a INNER JOIN ICSManufactureReceiveLot b ON a.SourceCode = b.ProductionCode AND a.WorkPoint = b.WorkPoint AND a.InvCode = b.InvCode WHERE a.RCVCode = '{RCVCode}' AND a.InvCode = '{InvCode}' AND b.LOTNO = '{LotNo}' and a.Sequence = '{RCVSequence}' and a.WorkPoint = '{WorkPoint}') BEGIN RAISERROR('绑定的条码:{LotNo}和入库单:{RCVCode}的生产订单不一致,绑定失败', 16, 1); RETURN END; UPDATE ICSManufactureReceiveLot SET IsBind = '1', RCVCode = '{RCVCode}', RCVSequence = '1' WHERE LotNo = '{LotNo}' and WorkPoint = '{WorkPoint}'; IF EXISTS(SELECT a.Quantity FROM ICSManufactureReceive a INNER JOIN ICSManufactureReceiveLot b ON a.SourceCode = b.ProductionCode AND a.WorkPoint = b.WorkPoint AND a.InvCode = b.InvCode WHERE a.RCVCode = '{RCVCode}' and a.Sequence = '{RCVSequence}' AND a.InvCode = '{InvCode}' AND b.IsBind = '1' GROUP BY a.Quantity HAVING sum(b.Quantity) > a.Quantity) BEGIN RAISERROR('绑定条码数量超过入库单:{RCVCode}的行数量', 16, 1); RETURN END; "; } } count = SqlHelper.CmdExecuteNonQueryLi(sql); if (count > 0) { msg = "导入成功"; } else { return "无有效的导入数据。"; } return msg; } catch (Exception ex) { return ex.Message; } } } #region 工单子线实体对象 /// /// 新增工单子线实体对象 /// public class MOModel { /// /// 工单编码 /// public string MOCode { get; set; } /// /// 行号 /// public string Sequence { get; set; } /// /// 物料编码 /// public string InvCode { get; set; } /// /// 数量 /// public decimal Quantity { get; set; } /// /// /// public string ExtensionID { get; set; } } #endregion }