|
|
using NFine.Data.Extensions;using System;using System.Data;using NFine.Code;using NFine.Repository;using NFine.Domain._03_Entity.SRM;using System.Data.SqlClient;
namespace NFine.Application.WMS{ public class HomeWorkApp : RepositoryFactory<ICSVendor> { public string SetData_PR(String savePath, string Year) { //数据获取
try {
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode; string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName; SqlConnection conn = SqlHelper.GetDataCenterConn(); DataTable data = FileToExcel.ExcelToTable(savePath); int index = 1; int countLot = 1; string msg = ""; string StorageCode = ""; DateTime time = DateTime.Now; string Tday = time.ToString("yyyyMMdd"); string sql = string.Empty; sql = string.Format(@"select InvCode FROM dbo.ICSInventory where WorkPoint ='{0}' ", WorkPoint); sql += string.Format(@"select LocationCode ,WarehouseCode FROM dbo.ICSLocation a
inner join ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint where b.Enable='1' and a.WorkPoint ='{0}' ", WorkPoint);
sql += string.Format(@" SELECT MAX(A.LotNO) AS LOTNO FROM ICSInventoryLot A WHERE A.LotNO LIKE '{0}%' and A.WorkPoint='{1}' AND LEN(a.LotNO) = 13", Tday, WorkPoint); sql += string.Format(@" SELECT top 0 ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,Amount,ExtensionID,Type,PrintTimes,LastPrintUser,LastPrintTime,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1 FROM ICSInventoryLot "); sql += string.Format(@" SELECT top 0 ID,LotNo,WarehouseCode,LocationCode,InvCode,Quantity,InDate,LockQuantity,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1 FROM ICSWareHouseLotInfo "); sql += string.Format(@" SELECT top 0 ID, Identification, TransCode, TransSequence, LotNo, InvCode, FromWarehouseCode, FromLocationCode, ToWarehouseCode, ToLocationCode, Quantity, Memo, Lock, TransType, BusinessCode, ERPUpload, ERPID, ERPDetailID, ERPCode, ERPSequence, LogID, MergeID, MUSER, MUSERName, MTIME, WorkPoint, EATTRIBUTE1, EATTRIBUTE2, EATTRIBUTE3 FROM dbo.ICSWareHouseLotInfoLog "); sql += string.Format(@" SELECT ID,Colspan,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,MTIME,MUSER,MUSERName,WorkPoint,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5 FROM ICSExtension where WorkPoint='{0}' ", WorkPoint); sql += string.Format(@" SELECT top 0 LotNo,TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1 FROM ICSInventoryLotDetail"); sql += string.Format(@" select InvCode,BatchEnable from ICSInventory"); DataSet ds = SqlHelper.GetDataSetBySql(sql); DataSet dsSave = new DataSet(); dsSave.Tables.Add(ds.Tables[3].Copy()); dsSave.Tables.Add( ds.Tables[4].Copy()); dsSave.Tables.Add( ds.Tables[5].Copy()); dsSave.Tables.Add(ds.Tables[6].Clone()); dsSave.Tables.Add(ds.Tables[7].Copy()); //dsSave.Tables.Add(ds.Tables[8].Copy());
dsSave.Tables[0].TableName="ICSInventoryLot"; dsSave.Tables[1].TableName = "ICSWareHouseLotInfo"; dsSave.Tables[2].TableName = "ICSWareHouseLotInfoLog"; dsSave.Tables[3].TableName = "ICSExtension"; dsSave.Tables[4].TableName = "ICSInventoryLotDetail"; // dsSave.Tables[5].TableName = "ICSInventory";
string LotNO = ""; Int64 Lot_NO = 0;
DataTable dts = ds.Tables[2];
if (dts != null && dts.Rows.Count > 0) { string lot = dts.Rows[0]["LOTNO"].ToString(); if(!string.IsNullOrWhiteSpace(lot)) Lot_NO = Convert.ToInt64(lot.Substring(lot.Length - 5)); } if (data != null && data.Rows.Count > 0) { foreach (DataRow dr in data.Rows) { index++; string ItemCode = dr["物料编码"].ToString(); string StackCode = dr["库位"].ToString(); string Date = dr["入库日期"].ToString(); string VENCODE = dr["供应商"].ToString();
decimal Amount = 0; if (data.Columns.Contains("辅计量数量")) { Amount = string.IsNullOrWhiteSpace(dr["辅计量数量"].ToString())?0:Convert.ToDecimal(dr["辅计量数量"].ToString()); } string ProjectCode = string.Empty; string BatchCode = string.Empty; string Version = string.Empty; string Brand = string.Empty; string oldLotno = string.Empty; string cFree1 = string.Empty; string cFree2 = string.Empty; string cFree3 = string.Empty; string cFree4 = string.Empty; string cFree5 = string.Empty; string cFree6 = string.Empty; string cFree7 = string.Empty; string cFree8 = string.Empty; string cFree9 = string.Empty; string cFree10 = string.Empty; if (data.Columns.Contains("项目号")) { ProjectCode = dr["项目号"].ToString(); } if (data.Columns.Contains("批次")) { BatchCode= dr["批次"].ToString(); } if (data.Columns.Contains("版本")) { Version = dr["版本"].ToString(); } if (data.Columns.Contains("厂牌")) { Brand = dr["厂牌"].ToString(); } if (data.Columns.Contains("旧条码")) { oldLotno = dr["旧条码"].ToString(); } if (data.Columns.Contains("自由项1")) { cFree1 = dr["自由项1"].ToString(); } if (data.Columns.Contains("自由项2")) { cFree2 = dr["自由项2"].ToString(); } if (data.Columns.Contains("自由项3")) { cFree3 = dr["自由项3"].ToString(); } if (data.Columns.Contains("自由项4")) { cFree4 = dr["自由项4"].ToString(); } if (data.Columns.Contains("自由项5")) { cFree5 = dr["自由项5"].ToString(); } if (data.Columns.Contains("自由项6")) { cFree6 = dr["自由项6"].ToString(); } if (data.Columns.Contains("自由项7")) { cFree7 = dr["自由项7"].ToString(); } if (data.Columns.Contains("自由项8")) { cFree8 = dr["自由项8"].ToString(); } if (data.Columns.Contains("自由项9")) { cFree9 = dr["自由项9"].ToString(); } if (data.Columns.Contains("自由项10")) { cFree10 = dr["自由项10"].ToString(); } string Colspan = ProjectCode + "~" + BatchCode + "~" + Version + "~" + Brand + "~" + oldLotno + "~" + cFree1 + "~" + cFree2 + "~" + cFree3 + "~" + cFree4 + "~" + cFree5 + "~" + cFree6 + "~" + cFree7 + "~" + cFree8 + "~" + cFree9 + "~" + cFree10;
DateTime dataTime;
if (Date == "") { dataTime = DateTime.Now; } else { dataTime = Convert.ToDateTime(Date); } decimal QTY = 0; bool result = decimal.TryParse(dr["库位数量或最小包装数量"].ToString(), out QTY); if (!result) { throw new Exception("第 " + index + " 行，库位数量或最小包装数量:" + dr["库位数量或最小包装数量"].ToString() + " 格式不正确！"); } if (decimal.Parse(dr["库位数量或最小包装数量"].ToString()) < 0) { throw new Exception("第 " + index + " 行，库位数量或最小包装数量:" + dr["库位数量或最小包装数量"].ToString() + " 数量不正确，为负值！"); }
int count = 0; bool resultCount = int.TryParse(dr["整盘数"].ToString(), out count); if (!resultCount) { throw new Exception("第 " + index + " 行，整盘数:" + dr["整盘数"].ToString() + " 格式不正确！"); }
var itemdrs = ds.Tables[0].Select(string.Format("InvCode='{0}'", ItemCode)); if (itemdrs == null || itemdrs.Length <= 0) { throw new Exception("第 " + index + " 行，物料编码:" + ItemCode + " 不存在！"); } var itemOldLotno = ds.Tables[3].Select(string.Format("LotNo='{0}'", oldLotno)); if (itemOldLotno != null && itemOldLotno.Length > 0) { throw new Exception("第 " + index + " 行，旧条码:" + oldLotno + " 已存在！"); } var itemdrs2 = ds.Tables[1].Select(string.Format("LocationCode='{0}'", StackCode)); if (itemdrs2 == null || itemdrs2.Length <= 0) { throw new Exception("第 " + index + " 行，库位:" + StackCode + " 不存在！"); } else { StorageCode = itemdrs2[0]["WarehouseCode"].ToString(); } var Enable = ds.Tables[8].Select(string.Format("InvCode='{0}'", ItemCode)); if (Enable[0]["BatchEnable"].ToString()== "True") { if (BatchCode=="") { throw new Exception("第 " + index + " 行，料品:" + ItemCode + " 已启用批次管理,请输入批次！"); } } var ID = ""; var Extension = ds.Tables[6].Select(string.Format("Colspan='{0}'", Colspan)); if (Extension == null || Extension.Length <= 0) { DataRow drExtension = dsSave.Tables[3].NewRow();
ID = GetNewid(); drExtension["ID"] = ID; drExtension["Colspan"] = Colspan; drExtension["ProjectCode"] = ProjectCode; drExtension["BatchCode"] = BatchCode; drExtension["Version"] = Version; drExtension["Brand"] = Brand; drExtension["cFree1"] = cFree1; drExtension["cFree2"] = cFree2; drExtension["cFree3"] = cFree3; drExtension["cFree4"] = cFree4; drExtension["cFree5"] = cFree5; drExtension["cFree6"] = cFree6; drExtension["cFree7"] = cFree7; drExtension["cFree8"] = cFree8; drExtension["cFree9"] = cFree9; drExtension["cFree10"] = cFree10; drExtension["MUSER"] = MUSER; drExtension["MUSERName"] = MUSERNAME; drExtension["MTIME"] = DateTime.Now; drExtension["WorkPoint"] = WorkPoint; dsSave.Tables[3].Rows.Add(drExtension);
DataRow drNew = ds.Tables[6].NewRow(); drNew.ItemArray = drExtension.ItemArray; ds.Tables[6].Rows.Add(drNew); } else { ID = Extension[0]["ID"].ToString();
} var Quantity= Convert.ToDecimal(dr["库位数量或最小包装数量"].ToString()); var logID = GetNewid(); for (int i = count; i > 0; i--) { LotNO = Tday + (Lot_NO + countLot++).ToString().PadLeft(5, '0');
#region 条码
DataRow drInventory = dsSave.Tables[0].NewRow(); drInventory["ID"] = GetNewid(); drInventory["LotNo"] = LotNO; drInventory["InvCode"] = ItemCode; drInventory["ProductDate"] = dataTime; drInventory["ExpirationDate"] = Convert.ToDateTime("2999-12-31"); drInventory["Quantity"] = Quantity; drInventory["Amount"] = Amount; drInventory["Type"] = "0"; drInventory["ExtensionID"] = ID; drInventory["MUSER"] = MUSER; drInventory["MUSERName"] = MUSERNAME; drInventory["MTIME"] = DateTime.Now; drInventory["WorkPoint"] = WorkPoint; drInventory["EATTRIBUTE1"] = oldLotno; dsSave.Tables[0].Rows.Add(drInventory); #endregion
#region 条码关联单据
DataRow drInventoryLotDetail = dsSave.Tables[4].NewRow(); drInventoryLotDetail["LotNo"] = LotNO; drInventoryLotDetail["TransCode"] = ""; drInventoryLotDetail["TransSequence"] = ""; drInventoryLotDetail["MUSER"] = MUSER; drInventoryLotDetail["MUSERName"] = MUSERNAME; drInventoryLotDetail["MTIME"] = DateTime.Now; drInventoryLotDetail["WorkPoint"] = WorkPoint; dsSave.Tables[4].Rows.Add(drInventoryLotDetail); #endregion
#region 库存
DataRow drHouseInfo = dsSave.Tables[1].NewRow(); drHouseInfo["ID"] = GetNewid(); drHouseInfo["LotNo"] = LotNO; drHouseInfo["WarehouseCode"] = StorageCode; drHouseInfo["LocationCode"] = StackCode; drHouseInfo["InvCode"] = ItemCode; drHouseInfo["Quantity"] = Quantity; drHouseInfo["InDate"] = dataTime; drHouseInfo["LockQuantity"] = 0; drHouseInfo["MUSER"] = MUSER; drHouseInfo["MUSERName"] = MUSERNAME; drHouseInfo["MTIME"] = DateTime.Now; drHouseInfo["WorkPoint"] = WorkPoint; dsSave.Tables[1].Rows.Add(drHouseInfo); #endregion
#region 记录表
DataRow drHouseLog = dsSave.Tables[2].NewRow(); drHouseLog["ID"] = GetNewid(); drHouseLog["Identification"] = logID; drHouseLog["LotNo"] = LotNO; drHouseLog["InvCode"] = ItemCode; drHouseLog["ToWarehouseCode"] = StorageCode; drHouseLog["ToLocationCode"] = StackCode; drHouseLog["Quantity"] = Quantity; drHouseLog["Lock"] = 0; drHouseLog["TransType"] = "1"; drHouseLog["BusinessCode"] = "39"; drHouseLog["ERPUpload"] = 0; drHouseLog["MUSER"] = MUSER; drHouseLog["MUSERName"] = MUSERNAME; drHouseLog["MTIME"] = DateTime.Now; drHouseLog["WorkPoint"] = WorkPoint; dsSave.Tables[2].Rows.Add(drHouseLog); #endregion
}
}
SqlHelper.ExecuteDataSet(dsSave);
} else { return "无有效的导入数据。";
} return "true";
} catch (Exception ex) { return ex.Message;
}
}
public string GetNewid() { string sql = "select newid() AS ID"; return Repository().FindTableBySql(sql, null).Rows[0]["ID"].ToString();
}
}}
|
