|
|
using NFine.Data.Extensions;using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Threading.Tasks;using NFine.Code;using NFine.Repository;using System.Data.Common;using NFine.Domain._03_Entity.SRM;using ICS.Application.Entity;using Newtonsoft.Json;using System.Configuration;using System.Data.SqlClient;using ICS.Data;using System.Net;using System.IO;using Newtonsoft.Json.Linq;using NFine.Domain._03_Entity.WMS;
namespace NFine.Application.WMS{ public class ICSMTDOCApp : 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,6)) as Quantity,a.Amount,CAST( a.MTDOCQuantity AS DECIMAL(18,6)) as MTDOCQuantity,
g.Name AS MTDOCType ,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4 ,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10,b.EATTRIBUTE10 as VenInvCode 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 left join dbo.ICSExtension f on e.ExtensionID=f.ID and e.WorkPoint=f.WorkPoint left join ICSType g on g.Code=a.MTDOCType and g.TableCode='ICSMTDOC' and ColumnCode='MTDOCType' and a.WorkPoint=g.WorkPoint WHERE a.MTDOCCode='{0}' and a.WorkPoint in ('{1}') order by a.Sequence,a.MTDOCType ";
sql = string.Format(sql, MTDOCCode, WorkPoint, Figure); return Repository().FindTableBySql(sql.ToString()); }
public DataTable GetRepertory(string LotNo, string InvCode, string InvName, string Invstd, string VenInvCode, string WHCode, ref Pagination jqgridparam) { try { 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 + "%'"; } if (!string.IsNullOrEmpty(InvCode)) { wherestr += " and a.InvCode like '%" + InvCode + "%'"; } if (!string.IsNullOrEmpty(InvName)) { wherestr += " and c.InvName like '%" + InvName + "%'"; } if (!string.IsNullOrEmpty(Invstd)) { wherestr += " and c.InvStd like '%" + Invstd + "%'"; } if (!string.IsNullOrEmpty(VenInvCode)) { wherestr += " and c.EATTRIBUTE10 like '%" + VenInvCode + "%'"; } if (!string.IsNullOrEmpty(WHCode)) { wherestr += " and a.WarehouseCode like '%" + WHCode + "%'"; } string sql = @"select a.WarehouseCode,
CASE WHEN ISNULL(E.F_ItemCode,'')='' THEN '否' ELSE '是' END AS LKChk ,a.LocationCode,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,c.EATTRIBUTE10 as VenInvCode 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 LEFT JOIN (select B.F_ItemCode from Sys_SRM_Items A LEFT JOIN Sys_SRM_ItemsDetail B ON B.F_ItemId=A.F_Id WHERE A.F_EnCode='JinHuaLiKu') E ON E.F_ItemCode=A.WarehouseCode WHERE a.Quantity>0 and a.WorkPoint = '" + WorkPoint + "'" + wherestr;
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); } catch (Exception ex) { throw new Exception(ex.Message); }
}
public DataTable GetInvcode(string Invcode, string InvName, string Invstd, 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 + "%'";
} if (!string.IsNullOrEmpty(InvName)) { wherestr += " and InvName like '%" + InvName + "%'";
} if (!string.IsNullOrEmpty(Invstd)) { wherestr += " and InvStd like '%" + Invstd + "%'";
} 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; string GetDepCode = @" select isnull(b.DepCode,'') as DepCode from dbo.Sys_SRM_User a
left join ICSDepartment b on a.F_DepartmentId=b.DepCode where a.F_Account='" + MUSER + "'";
object DepCode = SqlHelper.ExecuteScalar(GetDepCode); 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 SeachLotInfo = @"select * from ICSMTDOC where MTDOCType='1' and Status='1' and LotNo='" + LotNo + "' "; DataTable DtLotInfo = SqlHelper.GetDataTableBySql(SeachLotInfo); if (DtLotInfo != null && DtLotInfo.Rows.Count > 0) { msg = "所选条码存在于未审核的形态转换单中,无法创建新的形态转换单！"; return msg; }
string sqls = @"SELECT TOP 1 LotNO as NewLotNo FROM ICSInventoryLot WHERE EATTRIBUTE1='{0}'
ORDER BY CAST(SUBSTRING(LotNO, (LEN(LotNO)-CHARINDEX('-',REVERSE(LotNO))+1)+1,CHARINDEX('-',REVERSE(LotNO))-1) AS INT) 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 from ICSExtension a inner join dbo.ICSInventoryLot b on a.ID=b.ExtensionID and a.WorkPoint=b.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(); 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 (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); } } 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,DepCode ) 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,DepCode ) values(NEWID(),'{0}','{1}','{14}','{13}','{18}','{5}','{18}','2','{7}','1','{8}','{16}',newid(),newid(),'{9}','{10}','{11}','{16}','{12}','{17}' ,'{19}')";
//新增条码
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"].ToString() + @"',0.00)=0 Then Quantity ELSE '" + jo["InvCodeQuantity"].ToString() + @"' end ,CASE WHEN ISNULL('" + jo["InvCodeQuantity"].ToString() + @"',0.00)=0 Then Amount ELSE Amount/Quantity*" + jo["InvCodeQuantity"].ToString() + @" 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,TransID)
select '{13}',TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint,TransID 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, jo["LocationCode"].ToString(), jo["InvCodeQuantity"].ToString() == "0" ? jo["Quantity"].ToString() : jo["InvCodeQuantity"].ToString(), DepCode); Num++; } 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(); string SeachLotInfo = @"select * from dbo.ICSMTDOC a
left join dbo.ICSWareHouseLotInfo b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint where a.MTDOCType='1' and a.Quantity<>b.Quantity and a.MTDOCCode='" + MTDOCCode + "' ";
DataTable DtLotInfo = SqlHelper.GetDataTableBySql(SeachLotInfo); if (DtLotInfo != null && DtLotInfo.Rows.Count > 0) { msg = "所选单据中的条码库存异常！"; return msg; } 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,TransID,TransCode,TransSequence,LotNo,InvCode,FromWarehouseCode,FromLocationCode,
Quantity,Lock,TransType,BusinessCode, MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1) select newid(),'" + Identification + "',c.TransID,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 and d.MTDOCType='1' 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 += @"IF EXISTS( select a.Quantity,d.Quantity from ICSWareHouseLotInfo a
// left join dbo.ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
// left join dbo.ICSMTDOC d on a.LotNo=d.LotNo and d.WorkPoint=a.WorkPoint
// where d.MTDOCCode='"+ MTDOCCode + @"' and a.Quantity!=d.Quantity)
// BEGIN
// RAISERROR('库存数量跟创建单据时数量,不一致!',16,1);
// RETURN
// END;
// UPDATE ICSWareHouseLotInfo set Quantity=0 ,MTIME='" + TimeNow + "',MUSER='" + MUSER + "',MUSERName='" + MUSERNAME + "' WHERE LotNo='" + OldLotNo + "'";
//库存记录加入新条码记录
sql += @"INSERT into ICSWareHouseLotInfoLog(ID,Identification,TransID,TransCode,TransSequence,LotNo,InvCode,ToWarehouseCode,ToLocationCode,
Quantity,Lock,TransType,BusinessCode, MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1) select newid(),'" + Identification + "',c.TransID,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 and d.MTDOCType='2' 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,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,
// '' 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
// 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 FROM #TempERP
// select Costre, Sequence,GroupNO,[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 += @" select isnull(a.DepCode,'')+convert(nvarchar(30),(row_number() over(order by cast(a.Sequence as int) ,a.WHCode,b.InvCode)-1)/2+1)+a.MUSER as Costre , isnull(a.DepCode,'') as DepCode,
a.WHCode as InWhCode, a.MTDOCType,a.Memo, (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,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, case when e.BatchEnable='1' then isnull(c.BatchCode,'') else '' end 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.WorkPointleft join dbo.ICSInventory e on b.InvCode=e.InvCode and b.WorkPoint=e.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 a.WorkPoint=d.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock010' AND a.WorkPoint=conStock.WorkPoint where a.MTDOCCode='{0}' and a.WorkPoint='{1}' SELECT DISTINCT t1.Costre, t1.GroupNO, t1.DepCode,t1.OutWhCode,t2.WHCode AS InWhCode, t1.[User],GETDATE() AS MTime,t1.WorkPoint,t1.UpdateStock,t1.Memo AS cMemo FROM #TempERP t1 LEFT JOIN #TempERP t2 ON t1.GroupNO = t2.GroupNO AND t2.MTDOCType = '2' WHERE t1.MTDOCType = '1' select Costre, Sequence,GroupNO,[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("ERP接口调用失败:" + MessAge); } else { string Data = Obj["Data"].ToString(); char firstChar = Data[0]; if (firstChar == '{')//走U9接口返回逻辑
{ try { //string Data = Obj["Data"].ToString();
JObject DataObj = (JObject)JsonConvert.DeserializeObject(Data);
JArray resdetail = (JArray)JsonConvert.DeserializeObject(DataObj["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, DataObj["ID"].ToString(), det["ID"].ToString(), DataObj["TransCode"].ToString(), det["Sequence"].ToString()); } SqlCommandHelper.CmdExecuteNonQuery(ERPSql, cmd);
} catch (Exception ex) { msg = ex.Message; } } else if (firstChar == '[') { try { JArray res = (JArray)JsonConvert.DeserializeObject(Obj["Data"].ToString()); foreach (var item in res) { JObject jo = (JObject)item; JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["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, jo["ID"].ToString(), det["DetailID"].ToString(), jo["MTCode"].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 { string seachsql = string.Format(@"select * from ICSMTDOC a where a.MTDOCCode in ({0}) and a.WorkPoint='{1}' and a.Status='2'", keyValue.TrimEnd(','), WorkPoint); DataTable dt = SqlHelper.GetDataTableBySql(seachsql); if (dt.Rows.Count > 0) { msg = "所选形态转换单存在已审核单据，无法删除!"; return msg; }
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; }
}}
|
