|
|
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;using System.Data.Common;using System.Collections.Generic;using System.Configuration;using Newtonsoft.Json.Linq;using Newtonsoft.Json;using NFine.Domain._03_Entity.WMS;
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); var SeachLotNoInfo = data.AsEnumerable().Select(p => p.Field<string>("条码")).OrderBy(name => name); //数组以特定字符分割拼接成字符串
string SeachLotNo = "'" + string.Join("','", SeachLotNoInfo) + "'"; string SeachLotNoSql = @"select Lotno from ICSInventoryLot where lotNo in ("+ SeachLotNo + ")"; DataTable SeachLotNoDt = SqlHelper.GetDataTableBySql(SeachLotNoSql); if (SeachLotNoDt.Rows.Count>0) { throw new Exception("导入模板中所输入的条码已存在！"); }
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(
CASE WHEN LEFT(A.LotNO, 2) = 'QC' THEN SUBSTRING(A.LotNO, 3, LEN(A.LotNO) - 2) ELSE A.LotNO END ) AS LOTNO FROM ICSInventoryLot A WHERE (CASE WHEN LEFT(A.LotNO, 2) = 'QC' THEN SUBSTRING(A.LotNO, 3, LEN(A.LotNO) - 2) ELSE A.LotNO END) LIKE '{0}%' AND (CASE WHEN LEFT(A.LotNO, 2) = 'QC' THEN LEN(A.LotNO) - 2 ELSE LEN(A.LotNO) END) = 13;", Tday);
sql += string.Format(@"SELECT top 0 ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,Amount,ExtensionID,Type,PrintTimes,LastPrintUser,LastPrintTime,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1,EATTRIBUTE2, EATTRIBUTE3, EATTRIBUTE4, EATTRIBUTE5, EATTRIBUTE6, EATTRIBUTE7, EATTRIBUTE8, EATTRIBUTE9, EATTRIBUTE10, EATTRIBUTE11, EATTRIBUTE12, EATTRIBUTE13, EATTRIBUTE14, EATTRIBUTE15, EATTRIBUTE16, EATTRIBUTE17, EATTRIBUTE18, EATTRIBUTE19, EATTRIBUTE20, EATTRIBUTE21, EATTRIBUTE22, EATTRIBUTE23, EATTRIBUTE24, EATTRIBUTE25, EATTRIBUTE26, EATTRIBUTE27, EATTRIBUTE28, EATTRIBUTE29, EATTRIBUTE30 FROM ICSInventoryLot "); sql += string.Format(@"SELECT top 0 ID,LotNo,WarehouseCode,LocationCode,InvCode,Quantity,InDate,LockQuantity,Freeze,Inspect,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1 FROM ICSWareHouseLotInfo "); sql += string.Format(@"SELECT top 0 ID,Identification,TransID,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,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,EATTRIBUTE9,EATTRIBUTE10 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,TransID,TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,EATTRIBUTE9,EATTRIBUTE10 FROM ICSInventoryLotDetail"); 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[0].TableName ="ICSInventoryLot"; dsSave.Tables[1].TableName = "ICSWareHouseLotInfo"; dsSave.Tables[2].TableName = "ICSWareHouseLotInfoLog"; dsSave.Tables[3].TableName = "ICSExtension"; dsSave.Tables[4].TableName = "ICSInventoryLotDetail";
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));
string lotStr = dts.Rows[0]["LOTNO"].ToString(); string lot; if (lotStr.Length >= 2 && lotStr.Substring(0, 2) == "QC") { lot = lotStr.Substring(2); } else { lot = lotStr; }
if (!string.IsNullOrWhiteSpace(lot)) Lot_NO = Convert.ToInt64(lot.Substring(lot.Length - 5)); }
if (data != null && data.Rows.Count > 0) { string SeachColumnEnable = @"select ColumnCode,Name from ICSColumnEnable where TableCode='ICSInventoryLot' and Enable='1' and WorkPoint='"+ WorkPoint + "'"; DataTable ColumnsEnable = SqlHelper.GetDataTableBySql(SeachColumnEnable); foreach (DataRow Columns in ColumnsEnable.Rows) { if (!data.Columns.Contains(Columns["Name"].ToString())) { throw new Exception("导入模板中未包含列:'"+ Columns["Name"].ToString() + "'!"); } }
foreach (DataRow dr in data.Rows) { index++; string OldLotNo = dr["条码"].ToString(); string ItemCode = dr["物料编码"].ToString(); string StackCode = dr["库位"].ToString(); string Date = dr["入库日期"].ToString(); string VENCODE = dr["供应商"].ToString(); string ProductDate = dr["生产日期"].ToString(); string ExpirationDate = dr["失效日期"].ToString(); //decimal MinQty = Convert.ToDecimal(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 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("自由项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 + "~" + 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() + " 数量不正确，为负值！"); } if (string.IsNullOrWhiteSpace(dr["生产日期"].ToString())) { throw new Exception("第 " + index + " 行，生产日期不能为空！"); } if (decimal.Parse(dr["最小包装数量"].ToString()) < 0) { throw new Exception("第 " + index + " 行，最小包装数量:" + dr["最小包装数量"].ToString() + " 数量不正确，为负值！"); } int count = 0; string ZBCount = (Math.Ceiling(Convert.ToDecimal(dr["库位数量"].ToString()) / Convert.ToDecimal(dr["最小包装数量"].ToString()))).ToString(); bool resultCount = int.TryParse(ZBCount, 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 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 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--) { var Quantity = Convert.ToDecimal(dr["最小包装数量"].ToString()); if (OldLotNo != ""&& Quantity!= Convert.ToDecimal(dr["库位数量"].ToString())) { throw new Exception("第 " + index + " 行，条码:" + OldLotNo + " 所填写的最小包装量与库位数量不一致！"); } if (OldLotNo!="") {
LotNO = OldLotNo; } else { LotNO = "QC"+Tday + (Lot_NO + countLot++).ToString().PadLeft(5, '0'); } if (count == i) { Quantity= Convert.ToDecimal(dr["库位数量"].ToString())- (Convert.ToDecimal(dr["最小包装数量"].ToString())*(Convert.ToDecimal(count) - 1)); } #region 条码
DataRow drInventory = dsSave.Tables[0].NewRow(); drInventory["ID"] = GetNewid(); drInventory["LotNo"] = LotNO; drInventory["InvCode"] = ItemCode; drInventory["ProductDate"] = ProductDate; //drInventory["ProductDate"] = dataTime;
drInventory["ExpirationDate"] = string.IsNullOrEmpty(ExpirationDate)? Convert.ToDateTime("2999-12-31"): Convert.ToDateTime(ExpirationDate); 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; foreach (DataRow Columns in ColumnsEnable.Rows) { drInventory[Columns["ColumnCode"].ToString()] = dr[Columns["Name"].ToString()].ToString() ; } dsSave.Tables[0].Rows.Add(drInventory); #endregion
#region 条码关联单据
DataRow drInventoryLotDetail = dsSave.Tables[4].NewRow(); drInventoryLotDetail["LotNo"] = LotNO; drInventoryLotDetail["TransID"] = ""; 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["Freeze"] = 0; drHouseInfo["Inspect"] = 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();
}
/// <summary>
/// 获取仓库信息
/// </summary>
/// <param name="jqgridparam"></param>
/// <param name="queryJson"></param>
/// <returns></returns>
public DataTable GetICSWarehouse(ref Pagination jqgridparam, string queryJson) { DataTable dt = new DataTable(); var queryParam = queryJson.ToJObject(); List<DbParameter> parameter = new List<DbParameter>(); string sql = @" SELECT WarehouseCode,WarehouseName,WorkPoint FROM dbo.ICSWarehouse"; sql += " WHERE 1=1 and WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location+"'"; sql = string.Format(sql); DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); } public DataTable ICSWarehouseExportAll(string keyvalue) { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; DataTable dt = new DataTable(); string[] PrintParas = keyvalue.TrimEnd(',').Split(','); List<ICSWarehouse> list = new List<ICSWarehouse>(); foreach (var p in PrintParas) { ICSWarehouse DetailList = new ICSWarehouse(); DetailList.WHCode = p.ToString(); DetailList.WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; list.Add(DetailList); } string json = JsonConvert.SerializeObject(list); string msg = ""; string APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "CurrentStock/Get"; string result = DeciliterApp.HttpPost(APIURL, json); JObject Obj = (JObject)JsonConvert.DeserializeObject(result);//或者JObject jo = JObject.Parse(jsonText);
string MessAge = Obj["Message"].ToString(); string Success = Obj["Success"].ToString(); if (Success.ToUpper() == "FALSE") { throw new Exception("ERP获取现存量失败:"+MessAge); } string Data = Obj["Data"].ToString(); if (string.IsNullOrWhiteSpace(Data)) { throw new Exception("获取现存量失败!"); } var dataSet = JsonConvert.DeserializeObject<DataSet>(Data); dt = dataSet.Tables[0];
string sqls = @"SELECT ColCode,ColName,Enable
from ICSExtensionEnable where WorkPoint='" + WorkPoint + "'";
DataTable dtExtension = SqlHelper.GetDataTableBySql(sqls); foreach (DataRow drE in dtExtension.Rows) { if (dt.Columns.Contains(drE["ColCode"].ToString())) { if (!Convert.ToBoolean(drE["Enable"])) { dt.Columns.Remove(drE["ColCode"].ToString()); } else if (Convert.ToBoolean(drE["Enable"])) { dt.Columns[drE["ColCode"].ToString()].ColumnName = drE["ColName"].ToString(); }
} } string SeachColumnEnable = @"select ColumnCode,Name,Enable from ICSColumnEnable where TableCode='ICSInventoryLot' and Enable='1' and WorkPoint='" + WorkPoint + "'"; DataTable dtColumns = SqlHelper.GetDataTableBySql(SeachColumnEnable); foreach (DataRow drColumns in dtColumns.Rows) { if (!dt.Columns.Contains(drColumns["Name"].ToString())) { dt.Columns.Add(drColumns["Name"].ToString()); } }
return dt; }
public string DeleteInitialImport(string keyValue) { //站点信息
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; string msg = ""; keyValue = keyValue.Substring(1, keyValue.Length - 2); string sqlseach = string.Format(@"select a.LotNo,a.Quantity,b.Quantity from dbo.ICSInventoryLot a
left join dbo.ICSWareHouseLotInfo b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint where a.LotNo in ({0}) and a.Quantity<>b.Quantity", keyValue.TrimEnd(','));
DataTable dtASN = SqlHelper.GetDataTableBySql(sqlseach); if (dtASN != null && dtASN.Rows.Count > 0) { msg = "所选条码已进行过出入库操作,无法删除！"; return msg; } string sql = string.Empty; sql += string.Format(@"DELETE FROM dbo.ICSInventoryLot WHERE LotNo IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint); sql += string.Format(@"DELETE FROM dbo.ICSInventoryLotDetail WHERE LotNo IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint); sql += string.Format(@"DELETE FROM dbo.ICSWareHouseLotInfo WHERE LotNo IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint); sql += string.Format(@"update ICSWareHouseLotInfoLog set TransType='16' ,MTIME=getdate() where LotNo IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint); try { SqlHelper.CmdExecuteNonQueryLi(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return msg; }
public string LotNoSetData_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"); 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[0].TableName = "ICSInventoryLot"; dsSave.Tables[1].TableName = "ICSWareHouseLotInfo"; dsSave.Tables[2].TableName = "ICSWareHouseLotInfoLog"; dsSave.Tables[3].TableName = "ICSExtension"; dsSave.Tables[4].TableName = "ICSInventoryLotDetail";
// string LotNO = "";
//string Lot_NO = "";
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(); string LotNO = 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 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("自由项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 + "~" + 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 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 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 = Lot_NO;
#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;
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;
}
}
}}
|
