|
|
using ICS.Data;using Newtonsoft.Json.Linq;using Newtonsoft.Json;using NFine.Code;using NFine.Data.Extensions;using System;using System.Collections.Generic;using System.Configuration;using System.Data.Common;using System.Data.SqlClient;using System.Data;using System.IO;using System.Linq;using System.Net;using System.Text;using System.Threading.Tasks;using NFine.Domain._03_Entity.SRM;using NFine.Repository;
namespace NFine.Application.DHAY{ public class ICSMTDOCDHApp : RepositoryFactory<ICSVendor> { public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam) { DataTable dt = new DataTable(); var queryParam = queryJson.ToJObject(); List<DbParameter> parameter = new List<DbParameter>();
#region [SQL]
string sql = @" select Row_Number() OVER (ORDER BY MTDOCCode desc) AS ID,a.MTDOCCode,a.CreatePerson,a.CreateDateTime,a.Status,a.MUSER,a.MUSERName,a.MTIME,a.WorkPoint from (select distinct MTDOCCode,CreatePerson,CreateDateTime,Status,MUSER,MUSERName,MTIME,WorkPoint from dbo.ICSMTDOC )a"; sql += " WHERE 1=1"; sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName()); #endregion
if (!string.IsNullOrWhiteSpace(queryJson)) { if (!string.IsNullOrWhiteSpace(queryParam["MTDOCCode"].ToString())) { sql += " and a.MTDOCCode like '%" + queryParam["MTDOCCode"].ToString() + "%' "; } } if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin") { sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'"; } //if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); }
public DataTable GetSubGridJson(string MTDOCCode) { DataTable dt = new DataTable(); //var queryParam = queryJson.ToJObject();
string sql = string.Empty; object Figure = GetDecimalDigits(); List<DbParameter> parameter = new List<DbParameter>(); string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(','); sql = @" select e.ID,a.DepCode,d.DepName,a.WHCode,c.WarehouseName,a.Sequence,a.LotNo,b.InvCode,b.InvName, CAST( a.Quantity AS DECIMAL(18,{2})) as Quantity,a.Amount,CAST( a.MTDOCQuantity AS DECIMAL(18,{2})) as MTDOCQuantity,
a.MTDOCType from ICSMTDOC a left join dbo.ICSInventoryLot e on a.LotNo=e.LotNo and a.WorkPoint=e.WorkPoint left join ICSInventory b on e.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint left join ICSWarehouse c on a.WHCode=c.WarehouseCode and a.WorkPoint=c.WorkPoint left join ICSDepartment d on a.DepCode=d.DepCode and a.WorkPoint=d.WorkPoint WHERE a.MTDOCCode='{0}' and a.WorkPoint in ('{1}') order by a.Sequence ";
sql = string.Format(sql, MTDOCCode, WorkPoint, Figure); return Repository().FindTableBySql(sql.ToString()); }
public DataTable GetRepertory(string LotNo, ref Pagination jqgridparam) { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; List<DbParameter> parameter = new List<DbParameter>(); DataTable table = new DataTable(); string wherestr = ""; if (!string.IsNullOrEmpty(LotNo)) { wherestr += " and a.LotNO like '%" + LotNo + "%'"; } string sql = @"select a.WarehouseCode,a.LotNo,b.Amount,a.InvCode,c.InvName,c.InvDesc,c.InvStd,c.InvUnit,a.Quantity ,
d.ProjectCode,d.BatchCode,d.Version,d.Brand,d.cFree1,d.cFree2,d.cFree3,d.cFree4,d.cFree5,d.cFree6,d.cFree7,d.cFree8,d.cFree9,d.cFree10 from ICSWareHouseLotInfo a inner join ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint left join ICSInventory c on b.InvCode=c.InvCode and b.WorkPoint=c.WorkPoint left join dbo.ICSExtension d on b.ExtensionID=d.ID and b.WorkPoint=d.WorkPoint WHERE a.Quantity>0 and a.WorkPoint = '" + WorkPoint + "'" + wherestr;
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
}
public DataTable GetInvcode(string Invcode, ref Pagination jqgridparam) { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; List<DbParameter> parameter = new List<DbParameter>(); DataTable table = new DataTable(); string wherestr = ""; if (!string.IsNullOrEmpty(Invcode)) { wherestr += " and InvCode like '%" + Invcode + "%'";
} string sql = @"select distinct InvCode,InvName,InvStd,InvUnit,InvDesc from dbo.ICSInventory
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
} /// <summary>
/// 获取库位信息
/// </summary>
/// <param name="LocationCode"></param>
/// <param name="jqgridparam"></param>
/// <returns></returns>
public DataTable GetLocationCode(string LocationCode, ref Pagination jqgridparam) { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; List<DbParameter> parameter = new List<DbParameter>(); DataTable table = new DataTable(); string wherestr = ""; if (!string.IsNullOrEmpty(LocationCode)) { wherestr += " and a.LocationCode like '%" + LocationCode + "%'";
} string sql = @"select a.LocationCode as LocationCode ,a.ID,a.LocationName,b.WarehouseCode, b.WarehouseName,a.Musername as 'MUSER',a.MTIME
from ICSLocation a WITH (NOLOCK) inner join ICSWarehouse b WITH (NOLOCK) on a.WHID=b.ID where 1=1 and a.WorkPoint = '" + WorkPoint + "'" + wherestr;
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
}
public DataTable GetCode(string Type, string Common, ref Pagination jqgridparam) { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; List<DbParameter> parameter = new List<DbParameter>(); DataTable dt = new DataTable(); DataTable table = new DataTable(); string wherestr = ""; string sql = string.Empty; if (Type == "1") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and ProjectCode like '%" + Common + "%'"; } sql = @"select distinct ProjectCode as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "2") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and BatchCode like '%" + Common + "%'"; } sql = @"select distinct BatchCode as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "3") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and Version like '%" + Common + "%'"; } sql = @"select distinct Version as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "4") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and Brand like '%" + Common + "%'"; } sql = @"select distinct Brand as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "5") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and cFree1 like '%" + Common + "%'"; } sql = @"select distinct cFree1 as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "6") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and cFree2 like '%" + Common + "%'"; } sql = @"select distinct cFree2 as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "7") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and cFree3 like '%" + Common + "%'"; } sql = @"select distinct cFree3 as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "8") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and cFree4 like '%" + Common + "%'"; } sql = @"select distinct cFree4 as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "9") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and cFree5 like '%" + Common + "%'"; } sql = @"select distinct cFree5 as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "10") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and cFree6 like '%" + Common + "%'"; } sql = @"select distinct cFree6 as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "11") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and cFree7 like '%" + Common + "%'"; } sql = @"select distinct cFree7 as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "12") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and cFree8 like '%" + Common + "%'"; } sql = @"select distinct cFree8 as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "13") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and cFree9 like '%" + Common + "%'"; } sql = @"select distinct cFree9 as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} if (Type == "14") { if (!string.IsNullOrEmpty(Common)) { wherestr += " and cFree10 like '%" + Common + "%'"; } sql = @"select distinct cFree10 as Code from ICSExtension
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
} return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); }
//新增形态转换
public string CreateICSMTDOC(string ICSMTDOC, string InvCode, string LocationCode, string Memo) { string Colspan = ""; 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 msg = ""; string MTDOCCode = ""; DateTime TimeNow = DateTime.Now; string sql = string.Empty; JArray res = (JArray)JsonConvert.DeserializeObject(ICSMTDOC); int Num = 1; DataTable dts = new DataTable(); string Time = DateTime.Now.ToString("yyyyMMdd"); string sqlss = @"select max(MTDOCCode) as NewMTDOCCode from ICSMTDOC where substring(MTDOCCode,1,8)='{0}'"; sqlss = string.Format(sqlss, Time); dts = Repository().FindTableBySql(sqlss.ToString()); if (dts == null || dts.Rows.Count == 0 || dts.Rows[0]["NewMTDOCCode"].ToString() == "") { MTDOCCode = Time + "000001"; } else { string NewMTDOCCode = dts.Rows[0]["NewMTDOCCode"].ToString(); int COUNT = Convert.ToInt32(NewMTDOCCode.Substring(8)) + 1; MTDOCCode = Time + COUNT.ToString().PadLeft(6, '0'); } Dictionary<string, object> dic = new Dictionary<string, object>(); foreach (var item in res) { JObject jo = (JObject)item; DataTable dt = new DataTable();
string NewLotNo = "";
string LotNo = jo["LotNo"].ToString();
string sqls = @"SELECT top 1 LotNo NewLotNo FROM(
select CAST( RIGHT(LotNo, CHARINDEX('-', REVERSE(LotNo)) - 1) AS INT) AS result ,LotNofrom ICSInventoryLot where EATTRIBUTE1='{0}' ) aORDER BY result DESC";
sqls = string.Format(sqls, LotNo); dt = Repository().FindTableBySql(sqls.ToString()); if (dt == null || dt.Rows.Count == 0 || dt.Rows[0]["NewLotNo"].ToString() == "") { NewLotNo = LotNo + "-1"; } else { string newLotNO = dt.Rows[0]["NewLotNo"].ToString(); int COUNT = Convert.ToInt32(newLotNO.Substring(newLotNO.LastIndexOf('-') + 1)) + 1; NewLotNo = LotNo + "-" + COUNT.ToString(); } string sqloldLotNo = string.Format(@"select a.ProjectCode,a.BatchCode,a.Version,a.Brand,a.cFree1,a.cFree2,a.cFree3,a.cFree4,a.cFree5,a.cFree6,a.cFree7,a.cFree8,a.cFree9,a.cFree10,c.EffectiveDays,c.EffectiveEnable
from ICSExtension a inner join dbo.ICSInventoryLot b on a.ID=b.ExtensionID and a.WorkPoint=b.WorkPoint left JOIN ICSInventory c ON b.InvCode=c.InvCode AND b.WorkPoint=c.WorkPoint where b.LotNo='{0}'", LotNo);
DataTable oldLotNo = SqlHelper.GetDataTableBySql(sqloldLotNo); string oProjectCode = oldLotNo.Rows[0]["ProjectCode"].ToString(); string oBatchCode = oldLotNo.Rows[0]["BatchCode"].ToString(); string oVersion = oldLotNo.Rows[0]["Version"].ToString(); string oBrand = oldLotNo.Rows[0]["Brand"].ToString(); string ocFree1 = oldLotNo.Rows[0]["cFree1"].ToString(); string ocFree2 = oldLotNo.Rows[0]["cFree2"].ToString(); string ocFree3 = oldLotNo.Rows[0]["cFree3"].ToString(); string ocFree4 = oldLotNo.Rows[0]["cFree4"].ToString(); string ocFree5 = oldLotNo.Rows[0]["cFree5"].ToString(); string ocFree6 = oldLotNo.Rows[0]["cFree6"].ToString(); string ocFree7 = oldLotNo.Rows[0]["cFree7"].ToString(); string ocFree8 = oldLotNo.Rows[0]["cFree8"].ToString(); string ocFree9 = oldLotNo.Rows[0]["cFree9"].ToString(); string ocFree10 = oldLotNo.Rows[0]["cFree10"].ToString(); string productDate = jo["ProductDate"].ToString(); if (jo["FlagProjectCode"].ToString() != "" && jo["FlagProjectCode"].ToString() == "1") { oProjectCode = jo["ProjectCode"].ToString(); } if (jo["FlagBatchCode"].ToString() != "" && jo["FlagBatchCode"].ToString() == "1") { oBatchCode = jo["BatchCode"].ToString(); } if (jo["FlagVersion"].ToString() != "" && jo["FlagVersion"].ToString() == "1") { oVersion = jo["Version"].ToString(); } if (jo["FlagBrand"].ToString() != "" && jo["FlagBrand"].ToString() == "1") { oBrand = jo["Brand"].ToString(); } if (jo["FlagcFree1"].ToString() != "" && jo["FlagcFree1"].ToString() == "1") { ocFree1 = jo["cFree1"].ToString(); }
if (jo["FlagcFree2"].ToString() != "" && jo["FlagcFree2"].ToString() == "1") { ocFree2 = jo["cFree2"].ToString(); } if (jo["FlagcFree3"].ToString() != "" && jo["FlagcFree3"].ToString() == "1") { ocFree3 = jo["cFree3"].ToString(); } if (jo["FlagcFree4"].ToString() != "" && jo["FlagcFree4"].ToString() == "1") { ocFree4 = jo["cFree4"].ToString(); } if (jo["FlagcFree5"].ToString() != "" && jo["FlagcFree5"].ToString() == "1") { ocFree5 = jo["cFree5"].ToString(); } if (jo["FlagcFree6"].ToString() != "" && jo["FlagcFree6"].ToString() == "1") { ocFree6 = jo["cFree6"].ToString(); } if (jo["FlagcFree7"].ToString() != "" && jo["FlagcFree7"].ToString() == "1") { ocFree7 = jo["cFree7"].ToString(); } if (jo["FlagcFree8"].ToString() != "" && jo["FlagcFree8"].ToString() == "1") { ocFree8 = jo["cFree8"].ToString(); } if (jo["FlagcFree9"].ToString() != "" && jo["FlagcFree9"].ToString() == "1") { ocFree9 = jo["cFree9"].ToString(); } if (jo["FlagcFree10"].ToString() != "" && jo["FlagcFree10"].ToString() == "1") { ocFree10 = jo["cFree10"].ToString(); }
//Colspan = jo["ProjectCode"].ToString() + "~" + jo["BatchCode"].ToString() + "~" + jo["Version"].ToString() + "~" + jo["Brand"].ToString() + "~" + jo["cFree1"].ToString() + "~" + jo["cFree2"].ToString() + "~" + jo["cFree3"].ToString() + "~" + jo["cFree4"].ToString() + "~" + jo["cFree5"].ToString() + "~" + jo["cFree6"].ToString() + "~" + jo["cFree7"].ToString() + "~" + jo["cFree8"].ToString() + "~" + jo["cFree9"].ToString() + "~" + jo["cFree10"].ToString();
Colspan = oProjectCode + "~" + oBatchCode + "~" + oVersion + "~" + oBrand + "~" + ocFree1 + "~" + ocFree2 + "~" + ocFree3 + "~" + ocFree4 + "~" + ocFree5 + "~" + ocFree6 + "~" + ocFree7 + "~" + ocFree8 + "~" + ocFree9 + "~" + ocFree10; string sqlsss = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint); object ExtensionID = SqlHelper.ExecuteScalar(sqlsss); if (ExtensionID == null) { if (string.IsNullOrEmpty(productDate)) { msg = "创建新的批次，生产日期为必填选项"; return msg; } if (dic.Keys.Contains(Colspan)) { ExtensionID = dic[Colspan]; } else { ExtensionID = Guid.NewGuid(); sql += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint)
Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
ExtensionID, Colspan, oProjectCode, oBatchCode, oVersion, oBrand, ocFree1, ocFree2, ocFree3, ocFree4, ocFree5, ocFree6, ocFree7, ocFree8, ocFree9, ocFree10, MUSER, MUSERNAME, WorkPoint); dic.Add(Colspan, ExtensionID); } } else { productDate = ""; } //如果生产日期不为空 计算条码的失效日期
string exDate = string.Empty; if (!string.IsNullOrWhiteSpace(productDate)) { if (oldLotNo.Rows[0]["EffectiveEnable"].ToString()=="True") { exDate = productDate.ToDate().AddDays(+oldLotNo.Rows[0]["EffectiveDays"].ToDouble()).ToString("yyyy-MM-dd hh:mm:ss"); } else { exDate = "2999-12-31"; }
} string sqloldInvcode = string.Format(@"select InvCode from dbo.ICSInventoryLot where LotNo='{0}'", LotNo); object OldInvcode = SqlHelper.ExecuteScalar(sqloldInvcode); if (jo["FlagInvCode"].ToString() != "" && jo["FlagInvCode"].ToString() == "1") { OldInvcode = InvCode; } //老
sql += @" INSERT INTO dbo.ICSMTDOC
( ID ,MTDOCCode,WHCode ,Sequence ,LotNo,Quantity,Amount,MTDOCQuantity,MTDOCType,Memo,Status,CreatePerson, CreateDateTime,MTDOCID,MTDOCDetailID,ExtensionID,MUSER,MUSERName ,MTIME ,WorkPoint,LocationCode,EATTRIBUTE1 ) select NEWID(),'{0}',b.WarehouseCode,'{2}','{3}','{4}','{5}','{6}','1','{7}','1','{8}','{16}',newid(),newid(),a.ExtensionID,'{10}','{11}','{16}','{12}',b.LocationCode ,'{19}' from ICSInventoryLot a inner join dbo.ICSWareHouseLotInfo b on a.LotNo=b.LotNo where a.LotNo='{3}'";
//新
sql += @"INSERT INTO dbo.ICSMTDOC
( ID ,MTDOCCode,WHCode ,Sequence ,LotNo,Quantity,Amount,MTDOCQuantity,MTDOCType,Memo,Status,CreatePerson, CreateDateTime,MTDOCID,MTDOCDetailID,ExtensionID,MUSER,MUSERName ,MTIME ,WorkPoint,LocationCode,EATTRIBUTE1 ) values(NEWID(),'{0}','{1}','{14}','{13}','{18}','{5}','{6}','2','{7}','1','{8}','{16}',newid(),newid(),'{9}','{10}','{11}','{16}','{12}','{17}','{19}' )";
if (string.IsNullOrEmpty(exDate)) { //新增条码
sql += @"INSERT into ICSInventoryLot
(ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,Amount,ExtensionID,Type,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) select newid(),'{13}','{15}',ProductDate,ExpirationDate,CASE WHEN ISNULL(" + jo["InvCodeQuantity"].ToDecimal() + @",0)=0 Then Quantity ELSE '" + jo["InvCodeQuantity"].ToDecimal() + @"' end ,CASE WHEN ISNULL(" + jo["InvCodeQuantity"].ToDecimal() + @",0)=0 Then Amount ELSE Amount/Quantity*" + jo["InvCodeQuantity"].ToDecimal() + @" end ,'{9}','102',MUSER,MUSERName,MTIME,WorkPoint,'{3}',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 where LotNo='{3}'";
} else { //新增条码
sql += @"INSERT into ICSInventoryLot
(ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,Amount,ExtensionID,Type,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) select newid(),'{13}','{15}','{19}','{20}',CASE WHEN ISNULL(" + jo["InvCodeQuantity"].ToDecimal() + @",0)=0 Then Quantity ELSE '" + jo["InvCodeQuantity"].ToDecimal() + @"' end ,CASE WHEN ISNULL(" + jo["InvCodeQuantity"].ToDecimal() + @",0)=0 Then Amount ELSE Amount/Quantity*" + jo["InvCodeQuantity"].ToDecimal() + @" end ,'{9}','102',MUSER,MUSERName,MTIME,WorkPoint,'{3}',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 where LotNo='{3}'";
} //新增条码关联
sql += @" INSERT into ICSInventoryLotDetail(LotNo,TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint)
select '{13}',TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint from ICSInventoryLotDetail where LotNo='{3}'";
sql = string.Format(sql, MTDOCCode, jo["WHCode"].ToString(), Num++, jo["LotNo"].ToString(), jo["Quantity"].ToString(), jo["Amount"].ToString(), jo["MTDOCQuantity"].ToString(), Memo, MUSER, ExtensionID, MUSER, MUSERNAME, WorkPoint, NewLotNo, Num++, OldInvcode, TimeNow, LocationCode, jo["InvCodeQuantity"].ToDecimal() == 0 ? jo["Quantity"].ToDecimal() : jo["InvCodeQuantity"].ToDecimal(), productDate,exDate);
} if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0) { msg = ""; } else { msg = "形态转换失败!"; }
return msg; }
//审核
public string ICSMTDOCAudit(string MTDOCCode) { 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; DateTime TimeNow = DateTime.Now; string sql = string.Empty; string sqlAsn = @" select LotNo,WHCode,LocationCode,Quantity from ICSMTDOC where MTDOCCode='{0}' and MTDOCType='2'"; sqlAsn = string.Format(sqlAsn, MTDOCCode); DataTable dt = Repository().FindTableBySql(sqlAsn.ToString()); string Identification = Guid.NewGuid().ToString(); for (int i = 0; i < dt.Rows.Count; i++) { string NewLotNo = dt.Rows[i]["LotNo"].ToString(); string NewWHCode = dt.Rows[i]["WHCode"].ToString(); string NewLocationCode = dt.Rows[i]["LocationCode"].ToString(); string Quantity = dt.Rows[i]["Quantity"].ToString(); string sqlAsnD = @"select EATTRIBUTE1,InvCode from dbo.ICSInventoryLot where LotNo='{0}' "; sqlAsnD = string.Format(sqlAsnD, NewLotNo); DataTable dtD = Repository().FindTableBySql(sqlAsnD.ToString()); string OldLotNo = dtD.Rows[0]["EATTRIBUTE1"].ToString(); string NewInvCode = dtD.Rows[0]["InvCode"].ToString(); //修改形态表状态
sql += @"UPDATE ICSMTDOC set Status='2',MTIME='" + TimeNow + "',MUSER ='" + MUSER + "',MUSERName='" + MUSERNAME + "' WHERE MTDOCCode='" + MTDOCCode + "' and WorkPoint='" + WorkPoint + "'"; //加入一条老条码的出库记录
sql += @"INSERT into ICSWareHouseLotInfoLog(ID,Identification,TransCode,TransSequence,LotNo,InvCode,FromWarehouseCode,FromLocationCode,
Quantity,Lock,TransType,BusinessCode, MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1) select newid(),'" + Identification + "',d.MTDOCCode,d.Sequence, a.LotNo,a.InvCode,d.WHCode,d.LocationCode,a.Quantity,'0' ,'10','35','" + MUSER + @"','" + MUSERNAME + @"','" + TimeNow + @"',a.WorkPoint,'' from ICSWareHouseLotInfo a left join dbo.ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint left join dbo.ICSInventoryLotDetail c on b.LotNo=c.LotNo and b.WorkPoint=c.WorkPoint left join dbo.ICSMTDOC d on a.LotNo=d.LotNo and d.WorkPoint=c.WorkPoint where a.LotNo='" + OldLotNo + "' and a.WorkPoint='" + WorkPoint + "'";
//新条码加入库存表
sql += @"INSERT into ICSWareHouseLotInfo(ID
,LotNo ,WarehouseCode ,LocationCode ,InvCode ,Quantity ,InDate ,LockQuantity ,MUSER ,MUSERName ,MTIME ,WorkPoint ,EATTRIBUTE1) select newid() ,'" + NewLotNo + @"' ,CASE WHEN ISNULL('" + NewWHCode + @"','')='' THEN a.WarehouseCode ELSE '" + NewWHCode + @"' end ,CASE WHEN ISNULL('" + NewLocationCode + @"','')='' THEN a.LocationCode ELSE '" + NewLocationCode + @"' end ,'" + NewInvCode + @"' ,CASE WHEN ISNULL(" + Quantity + @",0)=0 Then a.Quantity ELSE " + Quantity + @" end ,a.InDate ,a.LockQuantity ,'" + MUSER + @"' ,'" + MUSERNAME + @"' ,'" + TimeNow + @"' ,a.WorkPoint ,a.EATTRIBUTE1 from ICSWareHouseLotInfo a left join dbo.ICSInventoryLot b on a.LotNo=b.EATTRIBUTE1 and a.WorkPoint=b.WorkPoint and b.LotNo='" + NewLotNo + @"' where a.LotNo='" + OldLotNo + "'";
//修改老条码库存
//sql += @"UPDATE ICSWareHouseLotInfo set CASE WHEN Quantity=0 THEN Quantity-Quantity ELSE Quantity=Quantity-'"+Quantity+@"' END ,MTIME='" + TimeNow + "',MUSER='" + MUSER + "',MUSERName='" + MUSERNAME + "' WHERE LotNo='" + OldLotNo + "'";
sql += @"UPDATE ICSWareHouseLotInfo set Quantity=0 ,MTIME='" + TimeNow + "',MUSER='" + MUSER + "',MUSERName='" + MUSERNAME + "' WHERE LotNo='" + OldLotNo + "'"; //库存记录加入新条码记录
sql += @"INSERT into ICSWareHouseLotInfoLog(ID,Identification,TransCode,TransSequence,LotNo,InvCode,ToWarehouseCode,ToLocationCode,
Quantity,Lock,TransType,BusinessCode, MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1) select newid(),'" + Identification + "',d.MTDOCCode,d.Sequence, a.LotNo,a.InvCode,d.WHCode,d.LocationCode,a.Quantity,'0' ,'10','36','" + MUSER + @"','" + MUSERNAME + @"','" + TimeNow + @"',a.WorkPoint,'' from ICSWareHouseLotInfo a left join dbo.ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint left join dbo.ICSInventoryLotDetail c on b.LotNo=c.LotNo and b.WorkPoint=c.WorkPoint left join dbo.ICSMTDOC d on a.LotNo=d.LotNo and d.WorkPoint=c.WorkPoint where a.LotNo='" + NewLotNo + "' and a.WorkPoint='" + WorkPoint + "'";
}
sql += @" select isnull(a.DepCode,'')+a.WHCode+a.MUSER as Costre , isnull(a.DepCode,'') as DepCode,a.WHCode as InWhCode, (row_number() over(order by cast(a.Sequence as int) ,a.WHCode,b.InvCode)-1)/2+1 as GroupNO ,
a.WHCode as OutWhCode,row_number() over(order by cast(a.Sequence as int) ,a.WHCode,b.InvCode) as Sequence,a.EATTRIBUTE1 as ProductDate,b.InvCode,d.Name as [Type] ,a.WHCode as WHCode,conStock.Enable AS UpdateStock, a.Quantity as Quantity ,a.Amount,a.MUSER as [User],a.WorkPoint, isnull(c.BatchCode,'') as BatchCode ,isnull(c.ProjectCode,'') as ProjectCode,isnull(c.Version,'') as Version,isnull(c.Brand,'') as Brand,isnull(c.cFree1,'') as cFree1,isnull(c.cFree2,'') as cFree2,isnull(c.cFree3,'') as cFree3,isnull(c.cFree4,'') as cFree4,isnull(c.cFree5,'') as cFree5,isnull(c.cFree6,'') as cFree6, isnull(c.cFree7,'') as cFree7,isnull(c.cFree8,'') as cFree8,isnull(c.cFree9,'') as cFree9,isnull(c.cFree10,'') as cFree10 INTO #TempERP from ICSMTDOC a left join ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint left join ICSExtension c on a.ExtensionID=c.ID and a.WorkPoint=c.WorkPoint left join ICSType d on d.TableCode='ICSMTDOC' and d.ColumnCode='MTDOCType' and d.Code=a.MTDOCType and d.WorkPoint=a.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock010' AND a.WorkPoint=conStock.WorkPoint where a.MTDOCCode='{0}' and a.WorkPoint='{1}' select distinct Costre, DepCode,InWhCode,OutWhCode,[User],getdate() as MTime,WorkPoint,UpdateStock,'TransForm001' as TransferType FROM #TempERP select Costre, Sequence,GroupNO,ProductDate,[Type],InvCode,WHCode,BatchCode,ProjectCode,Quantity,Amount,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 FROM #TempERP DROP TABLE #TempERP";
sql = string.Format(sql, MTDOCCode, WorkPoint); msg = CmdExecuteData(sql, MTDOCCode, Identification);
return msg; }
public string CmdExecuteData(string sql, string MTDOCCode, string Identification) { string msg = ""; try {
string connString = SqlHelper.DataCenterConnString; SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); conn.Open(); SqlTransaction sqlTran = conn.BeginTransaction(); SqlCommand cmd = new SqlCommand(); cmd.Transaction = sqlTran; cmd.Connection = conn; try { DataSet DSet = SqlCommandHelper.SQlReturnDataSet(sql, cmd);
string Inputstr = SqlHelper.DataSetToJson(DSet, "details", "Costre"); string APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "MorphologicalTransformationDoc/Create"; string result = HttpPost(APIURL, Inputstr); JObject Obj = (JObject)JsonConvert.DeserializeObject(result);//或者JObject jo = JObject.Parse(jsonText);
string MessAge = Obj["Message"].ToString(); string Success = Obj["Success"].ToString(); string ERPSql = ""; if (Success.ToUpper() == "FALSE") { throw new Exception(MessAge); } else { try { //JArray res = (JArray)JsonConvert.DeserializeObject(Obj["Data"].ToString());
JObject res = (JObject)JsonConvert.DeserializeObject(Obj["Data"].ToString()); //foreach (var item in res)
//{
//JObject jo = (JObject)item;
JArray resdetail = (JArray)JsonConvert.DeserializeObject(res["details"].ToString()); foreach (var detail in resdetail) { JObject det = (JObject)detail; ERPSql += @"
UPDATE a set ERPID='{3}',ERPDetailID='{4}',ERPCode='{5}',ERPSequence='{6}',ERPUpload='1' from ICSWareHouseLotInfoLog a inner join ICSMTDOC b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint where b.MTDOCCode='{0}' --and b.Sequence='{1}' and a.Identification='{2}'";
ERPSql += @" update ICSMTDOC set MTDOCID='{3}',MTDOCDetailID='{4}' where MTDOCCode='{0}' --and Sequence='{1}'"; ERPSql = string.Format(ERPSql, MTDOCCode, det["Sequence"].ToString(), Identification, res["ID"].ToString(), det["ID"].ToString(), res["TransCode"].ToString(), det["Sequence"].ToString()); } //}
SqlCommandHelper.CmdExecuteNonQuery(ERPSql, cmd);
} catch (Exception ex) { msg = ex.Message; } }
cmd.Transaction.Commit(); //return dt;
} catch (Exception ex) { cmd.Transaction.Rollback(); msg = ex.Message; } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Dispose(); } } catch (Exception ex) { msg = ex.Message;
} return msg;
}
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 ToLead(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; string Time = DateTime.Now.ToString("yyyyMMdd"); string MTDOCCode = ""; string Memo = ""; decimal Amount = 0; DateTime TimeNow = DateTime.Now; int Num = 1; DateTime time = DateTime.Now; string sql = string.Empty; string sqltxt = string.Empty; sql = string.Format(@"SELECT a.LotNo, a.InvCode, b.ProjectCode, b.BatchCode, b.Version, b.Brand, b.cFree1, b.cFree2, b.cFree3, b.cFree4, b.cFree5, b.cFree6, b.cFree7, b.cFree8, b.cFree9, b.cFree10 FROM ICSInventoryLot AS a LEFT JOIN ICSExtension AS b ON a.ExtensionID = b.ID AND a.WorkPoint = b.WorkPoint where a.WorkPoint ='{0}' ", WorkPoint); sql += string.Format(@"select LotNo,InvCode from ICSInventoryLot where WorkPoint ='{0}' ", WorkPoint); sql += string.Format(@"select a.LotNo,a.WarehouseCode,a.InvCode,a.Quantity,b.Amount from ICSWareHouseLotInfo a left JOIN ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint where a.WorkPoint ='{0}' ", WorkPoint); sql += string.Format(@"select max(MTDOCCode) as NewMTDOCCode from ICSMTDOC where substring(MTDOCCode,1,8)='{0}'", Time); DataSet ds = SqlHelper.GetDataSetBySql(sql);
if (data != null && data.Rows.Count > 0) { foreach (DataRow dr in data.Rows) { index++; #region 获取数据及卡控
string LotNo = dr["条码"].ToString(); string FrontInvCode = dr["转换前料号"].ToString(); string LaterInvCode = dr["转换后料号"].ToString(); string FrontProjectCode = string.Empty; string LaterProjectCode = string.Empty; string FrontBatchCode = string.Empty; string LaterBatchCode = string.Empty; string FrontVersion = string.Empty; string LaterVersion = string.Empty; string FrontBrand = string.Empty; string LaterBrand = string.Empty; string FrontcFree1 = string.Empty; string LatercFree1 = string.Empty; string FrontcFree2 = string.Empty; string LatercFree2 = string.Empty; string FrontcFree3 = string.Empty; string LatercFree3 = string.Empty; string FrontcFree4 = string.Empty; string LatercFree4 = string.Empty; string FrontcFree5 = string.Empty; string LatercFree5 = string.Empty; string FrontcFree6 = string.Empty; string LatercFree6 = string.Empty; string FrontcFree7 = string.Empty; string LatercFree7 = string.Empty; string FrontcFree8 = string.Empty; string LatercFree8 = string.Empty; string FrontcFree9 = string.Empty; string LatercFree9 = string.Empty; string FrontcFree10 = string.Empty; string LatercFree10 = string.Empty;
if (data.Columns.Contains("转换前项目号")) { FrontProjectCode = dr["转换前项目号"].ToString(); } if (data.Columns.Contains("转换后项目号")) { LaterProjectCode = string.IsNullOrEmpty(dr["转换后项目号"].ToString()) ? dr["转换前项目号"].ToString() : dr["转换后项目号"].ToString(); } if (data.Columns.Contains("转换前批次")) { FrontBatchCode = dr["转换前批次"].ToString(); } if (data.Columns.Contains("转换后批次")) { LaterBatchCode = string.IsNullOrEmpty(dr["转换后批次"].ToString()) ? dr["转换前批次"].ToString() : dr["转换后批次"].ToString(); } if (data.Columns.Contains("转换前版本")) { FrontVersion = dr["转换前版本"].ToString(); } if (data.Columns.Contains("转换后版本")) { LaterVersion = string.IsNullOrEmpty(dr["转换后版本"].ToString()) ? dr["转换前版本"].ToString() : dr["转换后版本"].ToString(); } if (data.Columns.Contains("转换前厂牌")) { FrontBrand = dr["转换前厂牌"].ToString(); } if (data.Columns.Contains("转换后厂牌")) { LaterBrand = string.IsNullOrEmpty(dr["转换后厂牌"].ToString()) ? dr["转换前厂牌"].ToString() : dr["转换后厂牌"].ToString(); } if (data.Columns.Contains("转换前自由项1")) { FrontcFree1 = dr["转换前自由项1"].ToString(); } if (data.Columns.Contains("转换后自由项1")) { LatercFree1 = string.IsNullOrEmpty(dr["转换后自由项1"].ToString()) ? dr["转换前自由项1"].ToString() : dr["转换后自由项1"].ToString(); } if (data.Columns.Contains("转换前自由项2")) { FrontcFree2 = dr["转换前自由项2"].ToString(); } if (data.Columns.Contains("转换后自由项2")) { LatercFree2 = string.IsNullOrEmpty(dr["转换后自由项2"].ToString()) ? dr["转换前自由项2"].ToString() : dr["转换后自由项2"].ToString(); } if (data.Columns.Contains("转换前自由项3")) { FrontcFree3 = dr["转换前自由项3"].ToString(); } if (data.Columns.Contains("转换后自由项3")) { LatercFree3 = string.IsNullOrEmpty(dr["转换后自由项3"].ToString()) ? dr["转换前自由项3"].ToString() : dr["转换后自由项3"].ToString(); } if (data.Columns.Contains("转换前自由项4")) { FrontcFree4 = dr["转换前自由项4"].ToString(); } if (data.Columns.Contains("转换后自由项4")) { LatercFree4 = string.IsNullOrEmpty(dr["转换后自由项4"].ToString()) ? dr["转换前自由项4"].ToString() : dr["转换后自由项4"].ToString(); } if (data.Columns.Contains("转换前自由项5")) { FrontcFree5 = dr["转换前自由项5"].ToString(); } if (data.Columns.Contains("转换后自由项5")) { LatercFree5 = string.IsNullOrEmpty(dr["转换后自由项5"].ToString()) ? dr["转换前自由项5"].ToString() : dr["转换后自由项5"].ToString(); } if (data.Columns.Contains("转换前自由项6")) { FrontcFree6 = dr["转换前自由项6"].ToString(); } if (data.Columns.Contains("转换后自由项6")) { LatercFree6 = string.IsNullOrEmpty(dr["转换后自由项6"].ToString()) ? dr["转换前自由项6"].ToString() : dr["转换后自由项6"].ToString(); } if (data.Columns.Contains("转换前自由项7")) { FrontcFree7 = dr["转换前自由项7"].ToString(); } if (data.Columns.Contains("转换后自由项7")) { LatercFree7 = string.IsNullOrEmpty(dr["转换后自由项7"].ToString()) ? dr["转换前自由项7"].ToString() : dr["转换后自由项7"].ToString(); } if (data.Columns.Contains("转换前自由项8")) { FrontcFree8 = dr["转换前自由项8"].ToString(); } if (data.Columns.Contains("转换后自由项8")) { LatercFree8 = string.IsNullOrEmpty(dr["转换后自由项8"].ToString()) ? dr["转换前自由项8"].ToString() : dr["转换后自由项8"].ToString(); } if (data.Columns.Contains("转换前自由项9")) { FrontcFree9 = dr["转换前自由项9"].ToString(); } if (data.Columns.Contains("转换后自由项9")) { LatercFree9 = string.IsNullOrEmpty(dr["转换后自由项9"].ToString()) ? dr["转换前自由项9"].ToString() : dr["转换后自由项9"].ToString(); } if (data.Columns.Contains("转换前自由项10")) { FrontcFree10 = dr["转换前自由项10"].ToString(); } if (data.Columns.Contains("转换后自由项10")) { LatercFree10 = string.IsNullOrEmpty(dr["转换后自由项10"].ToString()) ? dr["转换前自由项10"].ToString() : dr["转换后自由项10"].ToString(); }
string FrontColspan = FrontProjectCode + "~" + FrontBatchCode + "~" + FrontVersion + "~" + FrontBrand + "~" + FrontcFree1 + "~" + FrontcFree2 + "~" + FrontcFree3 + "~" + FrontcFree4 + "~" + FrontcFree5 + "~" + FrontcFree6 + "~" + FrontcFree7 + "~" + FrontcFree8 + "~" + FrontcFree9 + "~" + FrontcFree10;
string LaterColspan = LaterProjectCode + "~" + LaterBatchCode + "~" + LaterVersion + "~" + LaterBrand + "~" + LatercFree1 + "~" + LatercFree2 + "~" + LatercFree3 + "~" + LatercFree4 + "~" + LatercFree5 + "~" + LatercFree6 + "~" + LatercFree7 + "~" + LatercFree8 + "~" + LatercFree9 + "~" + LatercFree10;
//if (LotNo == null || LotNo == "")
//{
// throw new Exception("第 " + index + " 行，请输入条码！");
//}
if (!string.IsNullOrEmpty(FrontInvCode)) { if (string.IsNullOrEmpty(LaterInvCode)) { throw new Exception("第 " + index + " 行，已输入转换前料号,请输入转换后料号！"); } } else if (!string.IsNullOrEmpty(LaterInvCode)) { if (string.IsNullOrEmpty(FrontInvCode)) { throw new Exception("第 " + index + " 行，已输入转换后料号,请输入转换前料号！"); } } var LotNoResult = ds.Tables[2].Select(string.Format(" LotNo='{0}'", LotNo)); if (LotNoResult == null || LotNoResult.Length <= 0) { throw new Exception("第 " + index + " 行，条码:" + LotNo + "未查询到库存信息！"); } var FrontInvCodeResult = ds.Tables[2].Select(string.Format("InvCode='{0}' and LotNo='{1}'", FrontInvCode, LotNo)); if (FrontInvCodeResult == null || FrontInvCodeResult.Length <= 0) { throw new Exception("第 " + index + " 行，转换前料号:" + FrontInvCode + " 与条码对应信息不符！"); } if (!string.IsNullOrEmpty(FrontProjectCode) || !string.IsNullOrEmpty(LaterProjectCode)) { var ProjectCodeResult = ds.Tables[0].Select(string.Format("ProjectCode='{0}' and LotNo='{1}'", FrontProjectCode, LotNo)); if (ProjectCodeResult == null || ProjectCodeResult.Length <= 0) { throw new Exception("第 " + index + " 行，转换前项目号:" + FrontProjectCode + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontBatchCode) || !string.IsNullOrEmpty(LaterBatchCode)) { var BatchCodeResult = ds.Tables[0].Select(string.Format("BatchCode='{0}' and LotNo='{1}'", FrontBatchCode, LotNo)); if (BatchCodeResult == null || BatchCodeResult.Length <= 0) { throw new Exception("第 " + index + " 行，转换前批次:" + FrontBatchCode + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontVersion) || !string.IsNullOrEmpty(LaterVersion)) { var VersionResult = ds.Tables[0].Select(string.Format("Version='{0}' and LotNo='{1}'", FrontVersion, LotNo)); if (VersionResult == null || VersionResult.Length <= 0) { throw new Exception("第 " + index + " 行，转换前版本:" + FrontVersion + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontBrand) || !string.IsNullOrEmpty(LaterBrand)) { var BrandResult = ds.Tables[0].Select(string.Format("Brand='{0}' and LotNo='{1}'", FrontBrand, LotNo)); if (BrandResult == null || BrandResult.Length <= 0) { throw new Exception("第 " + index + " 行，转换前厂牌:" + FrontBrand + " 与条码对应信息不符合！"); } }
if (!string.IsNullOrEmpty(FrontcFree1) || !string.IsNullOrEmpty(LatercFree1)) { var cFree1Result = ds.Tables[0].Select(string.Format("cFree1='{0}' and LotNo='{1}'", FrontcFree1, LotNo)); if (cFree1Result == null || cFree1Result.Length <= 0) { throw new Exception("第 " + index + " 行，转换前自由项1:" + FrontcFree1 + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontcFree2) || !string.IsNullOrEmpty(LatercFree2)) { var cFree2Result = ds.Tables[0].Select(string.Format("cFree2='{0}' and LotNo='{1}'", FrontcFree2, LotNo)); if (cFree2Result == null || cFree2Result.Length <= 0) { throw new Exception("第 " + index + " 行，转换前自由项2:" + FrontcFree2 + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontcFree3) || !string.IsNullOrEmpty(LatercFree3)) { var cFree3Result = ds.Tables[0].Select(string.Format("cFree3='{0}' and LotNo='{1}'", FrontcFree3, LotNo)); if (cFree3Result == null || cFree3Result.Length <= 0) { throw new Exception("第 " + index + " 行，转换前自由项3:" + FrontcFree3 + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontcFree4) || !string.IsNullOrEmpty(LatercFree4)) { var cFree4Result = ds.Tables[0].Select(string.Format("cFree4='{0}' and LotNo='{1}'", FrontcFree4, LotNo)); if (cFree4Result == null || cFree4Result.Length <= 0) { throw new Exception("第 " + index + " 行，转换前自由项4:" + FrontcFree4 + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontcFree5) || !string.IsNullOrEmpty(LatercFree5)) { var cFree5Result = ds.Tables[0].Select(string.Format("cFree5='{0}' and LotNo='{1}'", FrontcFree5, LotNo)); if (cFree5Result == null || cFree5Result.Length <= 0) { throw new Exception("第 " + index + " 行，转换前自由项5:" + FrontcFree5 + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontcFree6) || !string.IsNullOrEmpty(LatercFree6)) { var cFree6Result = ds.Tables[0].Select(string.Format("cFree6='{0}' and LotNo='{1}'", FrontcFree6, LotNo)); if (cFree6Result == null || cFree6Result.Length <= 0) { throw new Exception("第 " + index + " 行，转换前自由项6:" + FrontcFree6 + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontcFree7) || !string.IsNullOrEmpty(LatercFree7)) { var cFree7Result = ds.Tables[0].Select(string.Format("cFree7='{0}' and LotNo='{1}'", FrontcFree7, LotNo)); if (cFree7Result == null || cFree7Result.Length <= 0) { throw new Exception("第 " + index + " 行，转换前自由项7:" + FrontcFree7 + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontcFree8) || !string.IsNullOrEmpty(LatercFree8)) { var cFree8Result = ds.Tables[0].Select(string.Format("cFree8='{0}' and LotNo='{1}'", FrontcFree8, LotNo)); if (cFree8Result == null || cFree8Result.Length <= 0) { throw new Exception("第 " + index + " 行，转换前自由项8:" + FrontcFree8 + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontcFree9) || !string.IsNullOrEmpty(LatercFree9)) { var cFree9Result = ds.Tables[0].Select(string.Format("cFree9='{0}' and LotNo='{1}'", FrontcFree9, LotNo)); if (cFree9Result == null || cFree9Result.Length <= 0) { throw new Exception("第 " + index + " 行，转换前自由项9:" + FrontcFree9 + " 与条码对应信息不符合！"); } } if (!string.IsNullOrEmpty(FrontcFree10) || !string.IsNullOrEmpty(LatercFree10)) { var cFree10Result = ds.Tables[0].Select(string.Format("cFree10='{0}' and LotNo='{1}'", FrontcFree10, LotNo)); if (cFree10Result == null || cFree10Result.Length <= 0) { throw new Exception("第 " + index + " 行，转换前自由项10:" + FrontcFree10 + " 与条码对应信息不符合！"); } } #endregion
if (ds.Tables[3] == null || ds.Tables[3].Rows.Count == 0 || ds.Tables[3].Rows[0]["NewMTDOCCode"].ToString() == "") { MTDOCCode = Time + "000001"; } else { string NewMTDOCCode = ds.Tables[3].Rows[0]["NewMTDOCCode"].ToString(); int COUNT = Convert.ToInt32(NewMTDOCCode.Substring(8)) + 1; MTDOCCode = Time + COUNT.ToString().PadLeft(6, '0'); }
string NewLotNo = "";
string sqls = @"select max(LotNo) as NewLotNo from ICSInventoryLot where EATTRIBUTE1='{0}' "; sqls = string.Format(sqls, LotNo); DataTable dt = Repository().FindTableBySql(sqls.ToString()); if (dt == null || dt.Rows.Count == 0 || dt.Rows[0]["NewLotNo"].ToString() == "") { NewLotNo = LotNo + "-1"; } else { string newLotNO = dt.Rows[0]["NewLotNo"].ToString(); int COUNT = Convert.ToInt32(newLotNO.Substring(newLotNO.LastIndexOf('-') + 1)) + 1; NewLotNo = LotNo + "-" + COUNT.ToString(); }
var info = ds.Tables[2].Select(string.Format(" LotNo='{0}'", LotNo)); if (!string.IsNullOrEmpty(info[0]["Amount"].ToString())) { Amount = Convert.ToDecimal(info[0]["Amount"].ToString()); }
string sqlsss = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", LaterColspan, WorkPoint); object ExtensionID = SqlHelper.ExecuteScalar(sqls); if (ExtensionID == null) { ExtensionID = Guid.NewGuid(); sqltxt += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint)
Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
ExtensionID, LaterColspan, LaterProjectCode, LaterBatchCode, LaterVersion, LaterBrand, LatercFree1, LatercFree2, LatercFree3, LatercFree4, LatercFree5, LatercFree6, LatercFree7, LatercFree8, FrontcFree9, LatercFree10, MUSER, MUSERNAME, WorkPoint); } //老
sqltxt += @"INSERT INTO dbo.ICSMTDOC
( ID ,MTDOCCode,WHCode ,Sequence ,LotNo,Quantity,Amount,MTDOCQuantity,MTDOCType,Memo,Status,CreatePerson, CreateDateTime,MTDOCID,MTDOCDetailID,ExtensionID,MUSER,MUSERName ,MTIME ,WorkPoint ) values(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','1','{7}','1','{8}','{16}',newid(),newid(),'{9}','{10}','{11}','{16}','{12}')";
//新
sqltxt += @"INSERT INTO dbo.ICSMTDOC
( ID ,MTDOCCode,WHCode ,Sequence ,LotNo,Quantity,Amount,MTDOCQuantity,MTDOCType,Memo,Status,CreatePerson, CreateDateTime,MTDOCID,MTDOCDetailID,ExtensionID,MUSER,MUSERName ,MTIME ,WorkPoint ) values(NEWID(),'{0}','{1}','{14}','{13}','{4}','{5}','{6}','2','{7}','1','{8}','{16}',newid(),newid(),'{9}','{10}','{11}','{16}','{12}')";
//新增条码
sqltxt += @"INSERT into ICSInventoryLot(ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,Amount,ExtensionID,Type,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1)
select newid(),'{13}','{15}',ProductDate,ExpirationDate,Quantity,Amount,ExtensionID,'102',MUSER,MUSERName,MTIME,WorkPoint,'{3}' from ICSInventoryLot where LotNo='{3}'";
//新增条码关联
sqltxt += @" INSERT into ICSInventoryLotDetail(LotNo,TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint)
select '{13}',TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint from ICSInventoryLotDetail where LotNo='{3}'";
sqltxt = string.Format(sqltxt, MTDOCCode, info[0]["WarehouseCode"].ToString(), Num++, info[0]["LotNo"].ToString(), info[0]["Quantity"].ToString(), Amount, info[0]["Quantity"].ToString(), Memo, MUSER, ExtensionID, MUSER, MUSERNAME, WorkPoint, NewLotNo, Num++, info[0]["InvCode"].ToString(), TimeNow); }
SqlHelper.CmdExecuteNonQueryLi(sqltxt); } 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();
}
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()); } }
public string DeleteICSMTDOC(string keyValue) { //站点信息
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; string msg = ""; keyValue = keyValue.Substring(1, keyValue.Length - 2); string sql = string.Empty; try { sql += string.Format(@"delete b from ICSMTDOC a
left join ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint where a.MTDOCCode in ({0}) and a.WorkPoint='{1}' and a.MTDOCType='2'", keyValue.TrimEnd(','), WorkPoint);
sql += string.Format(@"delete c from ICSMTDOC a
inner join ICSInventoryLotDetail c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint where a.MTDOCCode in ({0}) and a.WorkPoint='{1}' and a.MTDOCType='2'", keyValue.TrimEnd(','), WorkPoint);
sql += string.Format(@"delete ICSMTDOC where MTDOCCode in ({0}) and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);
SqlHelper.CmdExecuteNonQueryLi(sql); } catch (Exception ex) { msg = ex.Message; } return msg; }
}}
|
