|
|
using Newtonsoft.Json;using Newtonsoft.Json.Linq;using NFine.Code;using NFine.Data.Extensions;using NFine.Domain._03_Entity.SRM;using NFine.Repository;using System;using System.Collections.Generic;using System.Data;using System.Data.Common;using System.Linq;using System.Text;using System.Threading.Tasks;
namespace NFine.Application.KBSSRM{ public class PalletplateApp : RepositoryFactory<ICSVendor> { public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam) { // string sqlDelete = @" DELETE a FROM ICSContainer a
//LEFT JOIN dbo.ICSContainerLot b ON a.ContainerID=b.ContainerID
// WHERE b.ContainerID IS NULL and a.EATTRIBUTE3='SRM'";
// SqlHelper.ExecuteNonQuery(sqlDelete);
DataTable dt = new DataTable(); var queryParam = queryJson.ToJObject(); List<DbParameter> parameter = new List<DbParameter>(); string sql = @"SELECT a.ID,a.ContainerCode,a.MUSERName, CONVERT(NVARCHAR(50),a.MTIME,23) as MTIME,b.VenName,w.WorkPointName,w.WorkPointCode,a.EATTRIBUTE8
FROM dbo.ICSContainer a LEFT JOIN Sys_WorkPoint w on a.WorkPoint=w.WorkPointCode LEFT JOIN dbo.ICSVendor b ON a.EATTRIBUTE8=b.VenCode AND a.WorkPoint=b.WorkPoint WHERE 1=1 and a.EATTRIBUTE3='SRM' and a.ContainerType='ContainerType04' ";
if (queryParam["Type"].ToString() == "2") { sql += "and a.EATTRIBUTE1='2' "; } else if (queryParam["Type"].ToString() == "1") { sql += "and a.EATTRIBUTE1='1' "; } else { sql += ""; } if (!string.IsNullOrWhiteSpace(queryJson)) { if (!string.IsNullOrWhiteSpace(queryParam["CartonNo"].ToString())) { sql += " and ContainerCode like '%" + queryParam["CartonNo"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString())) { sql += " and a.EATTRIBUTE8 like '%" + queryParam["VenCode"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString())) { sql += " and b.VenName like '%" + queryParam["VenName"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString())) { sql += " and CONVERT(NVARCHAR(50),a.MTIME,23) >= '" + queryParam["TimeFrom"].ToString() + "' "; } if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString())) { sql += " and CONVERT(NVARCHAR(50),a.MTIME,23) <= '" + queryParam["TimeTo"].ToString() + "' "; } } if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin") { sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")"; } if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor") { sql += " and a.EATTRIBUTE8='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'"; } return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); }
/// <summary>
/// 子表查询
/// </summary>
/// <param name="queryJson"></param>
/// <param name="jqgridparam"></param>
/// <returns></returns>
public DataTable GetSubGridJson(string ContainerID, ref Pagination jqgridparam) { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(','); DataTable dt = new DataTable(); List<DbParameter> parameter = new List<DbParameter>(); string sql = @"
select a.ID, a.ContainerCode,a.ContainerName,a.Memo,a.ContainerType,a.MTIME,a.MUSER,a.MUSERName from ICSContainer a WHERE a.ContainerType='ContainerType01' and a.EATTRIBUTE3='SRM' and a.ContainerID='" + ContainerID + "' ";
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin") { sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")"; } return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); }
public DataTable GetSubGridJson_Add(string queryJson, ref Pagination jqgridparam) { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(','); DataTable dt = new DataTable(); var queryParam = queryJson.ToJObject(); List<DbParameter> parameter = new List<DbParameter>(); string sql = @" select a.ID, a.ContainerCode,a.ContainerName,a.Memo,a.ContainerType,a.MUSERName,a.MTIME from ICSContainer a
where a.ContainerType='ContainerType01' and a.EATTRIBUTE3='SRM' and a.ContainerID='" + queryParam["CartonNo"].ToString() + "' and a.WorkPoint='" + queryParam["WorkPoint"].ToString() + "'";
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); } public DataTable GetSubGridJson_AddByASN(string queryJson, ref Pagination jqgridparam) { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(','); DataTable dt = new DataTable(); var queryParam = queryJson.ToJObject(); List<DbParameter> parameter = new List<DbParameter>();
string sql = @" SELECT DISTINCT c.id,c.ContainerCode,c.MUSERName,c.MTIME FROM dbo.ICSASNDETAIL a
inner JOIN dbo.ICSContainerLot b ON a.LotNO =b.LotNO AND a.WorkPoint=b.WorkPoint LEFT JOIN ICSContainer c ON b.ContainerID=c.id AND c.WorkPoint=b.WorkPoint AND c.ContainerType='ContainerType01' LEFT JOIN ICSContainer d ON d.id=c.ContainerID AND d.WorkPoint=c.WorkPoint AND d.ContainerType='ContainerType04' LEFT JOIN ICSInventoryLot e ON b.LotNO=e.LotNO AND b.WorkPoint=e.WorkPoint WHERE c.id IS NOT NULL AND d.id IS NULL and a.ASNCode='" + queryParam["ASNCode"].ToString() + "' and a.WorkPoint='" + queryParam["WorkPoint"].ToString() + "'";
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString())) { sql += " and e.InvCode like '%" + queryParam["InvCode"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["LotNo"].ToString())) { sql += " and e.LotNo like '%" + queryParam["LotNo"].ToString() + "%' "; }
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
}
public string GetContainerCode(string RoleEnCode,string WorkPoint) { DataTable dt = new DataTable(); string Date = DateTime.Now.ToString("yyMMdd"); string Pre = "Z" + RoleEnCode + Date; string CartonNo = GetSerialCode(WorkPoint, "ICSContainer", "ContainerCode", Pre, 3); return CartonNo; }
public string GetSerialCode(string workPointCode, string tbName, string colName, string Pre, int numLen) { string sql = "EXEC Addins_GetSerialCode '{0}','{1}','{2}','{3}',{4}"; sql = string.Format(sql, new object[] { workPointCode, tbName, colName, Pre, numLen }); return DbHelper.ExecuteScalar(CommandType.Text, sql).ToString(); }
public DataTable GetContainerForBox(string invcode, string Code, string LotNo, string VenCode, string WorkPoint, string Type,ref Pagination jqgridparam) { /// string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
List<DbParameter> parameter = new List<DbParameter>(); DataTable table = new DataTable(); string wherestr = ""; string sql = ""; if (!string.IsNullOrEmpty(invcode)) { wherestr += " and c.InvCode like '%" + invcode + "%'"; } if (!string.IsNullOrEmpty(Code)) { wherestr += " and a.ContainerCode like '%" + Code + "%'"; } if (!string.IsNullOrEmpty(LotNo)) { wherestr += " and b.LotNo like '%" + LotNo + "%'"; } if (Type == "1") { if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor")) { GetPOTypeCondition("<>"); } if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Business")) { GetPOTypeCondition("="); } sql = @" SELECT DISTINCT a.ContainerCode,a.ID,
a.MUSERName, a.MTIME,Dil.F_ItemName,d.InvCode,d.InvName,d.InvStd FROM ICSContainer a LEFT JOIN dbo.ICSContainerLot c ON a.ID=c.ContainerID and a.WorkPoint=c.WorkPoint LEFT join dbo.ICSInventoryLot lot on c.LotNo=lot.LotNo and a.WorkPoint=lot.WorkPoint left join Sys_SRM_ItemsDetail Dil on F_ItemId='6230217e-4413-4427-9bec-c67ba789eca9' and F_EnabledMark='1' and a.ContainerType=Dil.F_ItemCode LEFT JOIN ICSInventory d ON d.InvCode=lot.InvCode AND d.WorkPoint=lot.WorkPoint LEFT JOIN dbo.ICSInventoryLotDetail e ON lot.LotNo=e.LotNo AND lot.WorkPoint=e.WorkPoint LEFT JOIN dbo.ICSPurchaseOrder f ON e.TransCode=f.POCode AND e.TransSequence=f.Sequence AND e.WorkPoint=f.WorkPoint WHERE a.ID not IN (SELECT b.ContainerID FROM dbo.ICSASNDETAIL a inner JOIN dbo.ICSContainerLot b ON a.LotNO =b.LotNO AND a.WorkPoint=b.WorkPoint) and a.ContainerID='' and a.ContainerType='ContainerType01' and a.WorkPoint = '" + WorkPoint + "' and a.EATTRIBUTE8 = '" + VenCode + "' and a.EATTRIBUTE1='" + Type + "' " + wherestr;
} else { sql = @" SELECT DISTINCT a.ContainerCode,a.ID,
a.MUSERName, a.MTIME,Dil.F_ItemName,d.InvCode,d.InvName,d.InvStd FROM ICSContainer a LEFT JOIN dbo.ICSContainerLot c ON a.ID=c.ContainerID and a.WorkPoint=c.WorkPoint LEFT join dbo.ICSInventoryLot lot on c.LotNo=lot.LotNo and a.WorkPoint=lot.WorkPoint left join Sys_SRM_ItemsDetail Dil on F_ItemId='6230217e-4413-4427-9bec-c67ba789eca9' and F_EnabledMark='1' and a.ContainerType=Dil.F_ItemCode LEFT JOIN ICSInventory d ON d.InvCode=lot.InvCode AND d.WorkPoint=lot.WorkPoint WHERE a.ID not IN (SELECT b.ContainerID FROM dbo.ICSASNDETAIL a inner JOIN dbo.ICSContainerLot b ON a.LotNO =b.LotNO AND a.WorkPoint=b.WorkPoint) and a.ContainerID='' and a.ContainerType='ContainerType01' and a.WorkPoint = '" + WorkPoint + "' and a.EATTRIBUTE8 = '" + VenCode + "' and a.EATTRIBUTE1='" + Type + "' " + wherestr;
} return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); }
private string GetPOTypeCondition(string comparisonOperator) { string query = @"SELECT a.F_Define1, a.F_Define2
FROM Sys_SRM_ItemsDetail a LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id WHERE b.F_EnCode = 'POTypeExclude' AND a.F_EnabledMark = '1'";
DataTable dt = SqlHelper.GetDataTableBySql(query); if (dt.Rows.Count > 0) { string field = dt.Rows[0]["F_Define1"].ToString(); // 字段名（如 a.EATTRIBUTE1）
string value = dt.Rows[0]["F_Define2"].ToString(); // 比较值（如 'XX'）
return $" and isnull(f.{field}, '') {comparisonOperator} '{value}'"; } return ""; }
public string SaveICSContainerForPalletplate(string ICSASN) { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode; string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName; string msg = ""; string sql = string.Empty; string str1 = ""; string ID = Guid.NewGuid().ToString(); JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN); foreach (var item in res) { JObject jo = (JObject)item; //创建栈板
sql += @" INSERT INTO dbo.ICSContainer
( ID ,ContainerCode ,ContainerName,InventoryMixed,ProjectMixed,BatchMixed,Multiplex, MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3,ContainerType,ContainerID,EATTRIBUTE1) VALUES ( '{0}','{1}','{1}','1','1','1','1','{2}','{3}',getdate(),'{4}','{5}','SRM','ContainerType04','','{6}')";
sql = string.Format(sql, ID, jo["ContainerCode"].ToString(),MUSER, MUSERNAME, jo["WorkPoint"].ToString(), jo["VenCode"].ToString(), jo["Type"].ToString());
JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString()); foreach (var detail in resdetail) { JObject det = (JObject)detail; sql += @"update ICSContainer set ContainerID='{0}' where ContainerCode='{1}' and EATTRIBUTE3='SRM' and ContainerType='ContainerType01'"; sql = string.Format(sql, ID, det["CartonNo"].ToString());
} }
try { if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0) {
} else { msg = "新增失败"; } } catch (Exception ex) { msg = ex.Message; } return msg; } public string SaveICSContainerForPalletplateByASN(string ICSASN) { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode; string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName; string msg = ""; string sql = string.Empty; string str1 = ""; string ID = Guid.NewGuid().ToString(); JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN); foreach (var item in res) { JObject jo = (JObject)item; //创建栈板
sql += @" INSERT INTO dbo.ICSContainer
( ID ,ContainerCode ,ContainerName,InventoryMixed,ProjectMixed,BatchMixed,Multiplex, MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3,ContainerType,ContainerID,EATTRIBUTE1) VALUES ( '{0}','{1}','{1}','1','1','1','1','{2}','{3}',getdate(),'{4}','{5}','SRM','ContainerType04','','{6}')";
sql = string.Format(sql, ID, jo["ContainerCode"].ToString(), MUSER, MUSERNAME, jo["WorkPoint"].ToString(), jo["VenCode"].ToString(), jo["Type"].ToString());
JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString()); foreach (var detail in resdetail) { JObject det = (JObject)detail; sql += @"update ICSContainer set ContainerID='{0}' where ContainerCode='{1}' and EATTRIBUTE3='SRM' and ContainerType='ContainerType01'"; sql = string.Format(sql, ID, det["CartonNo"].ToString());
} }
try { if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0) {
} else { msg = "新增失败"; } } catch (Exception ex) { msg = ex.Message; } return msg; }
public string UpdateICSContainerForPalletplate(string ICSASN) { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode; string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName; string msg = ""; string sql = string.Empty; JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN); foreach (var item in res) { JObject jo = (JObject)item; JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString()); foreach (var detail in resdetail) { JObject det = (JObject)detail; string DetailID = Guid.NewGuid().ToString(); sql += @" update ICSContainer set ContainerID='{0}' where ContainerCode='{1}' and EATTRIBUTE3='SRM' and ContainerType='ContainerType01'"; sql = string.Format(sql, jo["ContainerID"].ToString() , det["CartonNo"].ToString()); } } try { if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0) {
} else { msg = "修改失败"; } } catch (Exception ex) { msg = ex.Message; } return msg; }
public string DeleteInfo(string keyValue, string WorkPoint) { string msg = ""; string sql = string.Format(@"update ICSContainer set ContainerID='' where ContainerCode in ({0}) and EATTRIBUTE3='SRM' and ContainerType='ContainerType01' and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);
try { SqlHelper.ExecuteNonQuery(sql); } catch (Exception ex) { msg = ex.Message; } return msg; }
public string DeleteContainer(string keyValue, string WorkPoint) { WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2); string msg = ""; keyValue = keyValue.Substring(1, keyValue.Length - 2); string sql = string.Empty; string sqls = string.Format(@"SELECT * FROM dbo.ICSASNDETAIL
WHERE LOTNO IN ( SELECT LotNO FROM dbo.ICSContainer a LEFT JOIN ICSContainerLot b ON a.ContainerID=b.ContainerID WHERE a.ContainerID in ({0}) and a.WorkPoint in ({1}))", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
DataTable dt = SqlHelper.GetDataTableBySql(sqls); if (dt == null || dt.Rows.Count <= 0) { sql += string.Format(@" DELETE ICSContainer
WHERE ID in ({0}) AND WorkPoint in ({1})", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
sql += string.Format(@" update ICSContainer set ContainerID=''
WHERE ContainerID in ({0}) AND WorkPoint in ({1})", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
SqlHelper.CmdExecuteNonQueryLi(sql); } else { msg = "所选栈板中已有加入送货单中，请先在送单号中删除！"; } return msg; }
}}
|
