You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
835 lines
42 KiB
835 lines
42 KiB
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);
|
|
|
|
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 = "";
|
|
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 OldLotNo = dr["条码"].ToString();
|
|
string ItemCode = dr["物料编码"].ToString();
|
|
string StackCode = dr["库位"].ToString();
|
|
string Date = dr["入库日期"].ToString();
|
|
string VENCODE = dr["供应商"].ToString();
|
|
string ProductDate = dr["生产日期"].ToString();
|
|
string KWQty = "";
|
|
decimal MinQty = 0;
|
|
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(KWQty, out QTY);
|
|
//if (!result)
|
|
//{
|
|
// throw new Exception("第 " + index + " 行，库位数量:" + KWQty + " 格式不正确！");
|
|
//}
|
|
//if (decimal.Parse(KWQty) < 0)
|
|
//{
|
|
// throw new Exception("第 " + index + " 行，库位数量:" + KWQty + " 数量不正确，为负值！");
|
|
//}
|
|
if (string.IsNullOrWhiteSpace(dr["生产日期"].ToString()))
|
|
{
|
|
throw new Exception("第 " + index + " 行，生产日期不能为空！");
|
|
}
|
|
//if (decimal.Parse(dr["最小包装数量"].ToString()) < 0)
|
|
//{
|
|
// throw new Exception("第 " + index + " 行，最小包装数量:" + dr["最小包装数量"].ToString() + " 数量不正确，为负值！");
|
|
//}
|
|
int count = 1;
|
|
//string ZBCount = (Math.Ceiling(Convert.ToDecimal(KWQty) / 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(KWQty);
|
|
var logID = GetNewid();
|
|
for (int i = count; i > 0; i--)
|
|
{
|
|
decimal Quantity ;
|
|
string SeachEnable = @"select AmountEnable,EATTRIBUTE1 from dbo.ICSInventory where InvCode='" + ItemCode + "'";
|
|
DataTable dtEnable = SqlHelper.GetDataTableBySql(SeachEnable);
|
|
if (dtEnable.Rows[0]["AmountEnable"].ToString() == "True")
|
|
{
|
|
Quantity = Amount * Convert.ToDecimal(dtEnable.Rows[0]["EATTRIBUTE1"].ToString());
|
|
KWQty = Quantity.ToString();
|
|
MinQty = Quantity;
|
|
}
|
|
else
|
|
{
|
|
Quantity = Amount;
|
|
Amount = 0;
|
|
KWQty = Quantity.ToString();
|
|
MinQty = Quantity;
|
|
}
|
|
|
|
if (OldLotNo != ""&& Quantity!= Convert.ToDecimal(KWQty))
|
|
{
|
|
throw new Exception("第 " + index + " 行，条码:" + OldLotNo + " 所填写的最小包装量与库位数量不一致！");
|
|
}
|
|
if (OldLotNo!="")
|
|
{
|
|
LotNO = OldLotNo.ToUpper();
|
|
string SeachLotNo = @"select * from dbo.ICSInventoryLot where LotNo='"+ LotNO + "'";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(SeachLotNo);
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
throw new Exception("条码:"+ LotNO + "已存在!");
|
|
|
|
}
|
|
|
|
}
|
|
else
|
|
{
|
|
LotNO = Tday + (Lot_NO + countLot++).ToString().PadLeft(5, '0');
|
|
}
|
|
|
|
if (count == i)
|
|
{
|
|
Quantity= Convert.ToDecimal(KWQty)- (Convert.ToDecimal(MinQty) *(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"] = 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;
|
|
|
|
}
|
|
|
|
}
|
|
|
|
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 + "CurrentStockNEW/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(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();
|
|
}
|
|
|
|
}
|
|
}
|
|
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 LotNo from dbo.ICSWareHouseLotInfoLog where BusinessCode<>'39' and LotNo in({0}) and WorkPoint in ('{1}')", keyValue.TrimEnd(','), WorkPoint);
|
|
DataTable dtASN = SqlHelper.GetDataTableBySql(sqlSeach);
|
|
if (dtASN != null && dtASN.Rows.Count > 0)
|
|
{
|
|
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;
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
}
|