Jjie
/
OMAY_WMS
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
133 Commits
1 Branch
0 Tags
32 MiB
 
 
 
 
 
Branch: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
OMAY_WMS/WMS-BS/NFine.Application/OMAY/ICSOOCreateLotApp.cs

814 lines
45 KiB
Raw Permalink Blame History

using NFine.Data.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using NFine.Code;
using NFine.Repository;
using System.Data.Common;
using NFine.Domain._03_Entity.SRM;
using Newtonsoft.Json;
using System.Configuration;
using System.Net;
using System.IO;
using Newtonsoft.Json.Linq;
namespace NFine.Application.OMAY
{
public class ICSOOCreateLotApp : RepositoryFactory<ICSVendor>
{
//奥美工单生成条码主表查询
public DataTable GetGridJsonChengPing(string queryJson, ref Pagination jqgridparam)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
object Figure = GetDecimalDigits();
#region [SQL]
string sql = @" select distinct a.ID, a.OODetailID,a.OOCode,a.Sequence, CAST(a.InQuantity AS DECIMAL(18,{0})) as RCVQuantity ,a.InvCode,b.InvName,b.InvDesc,b.InvStd,b.InvUnit,CAST( a.Quantity AS DECIMAL(18,{0})) as Quantity,CAST( a.Amount as DECIMAL(18,{0})) as Amount
,a.Status,a.ExtensionID,a.MUSERName,a.MTIME ,CAST(c.LOTQTY AS DECIMAL(18,{0})) as LOTQTY ,CAST( a.Quantity AS DECIMAL(18,{0})) -CAST(a.InQuantity AS DECIMAL(18,{0})) as ERPNum
,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,a.EATTRIBUTE1,a.EATTRIBUTE2,a.EATTRIBUTE3,a.EATTRIBUTE4,a.EATTRIBUTE5,a.EATTRIBUTE6,a.EATTRIBUTE7,a.EATTRIBUTE8,
a.EATTRIBUTE9,a.EATTRIBUTE10
from ICSOutsourcingOrder a
left join ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join (
select mm.TransCode,mm.TransSequence,sum(isnull(ee.Quantity,0)) LOTQTY,ee.WorkPoint from ICSInventoryLot ee
left join ICSInventoryLotDetail mm on ee.LotNo=mm.LotNo and ee.type='3'
where isnull(ee.EATTRIBUTE1,'')=''
group by mm.TransCode,mm.TransSequence,ee.WorkPoint
)c
on a.OOCode=c.TransCode and a.Sequence=c.TransSequence and a.WorkPoint=c.WorkPoint
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint WHERE 1=1 and a.Status='2'";
sql = string.Format(sql, Figure);
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
{
sql += " and a.OOCode like '%" + queryParam["POCode"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
{
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
{
sql += " and b.InvName like '%" + queryParam["InvName"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["BatchCode"].ToString()))
{
sql += " and f.BatchCode like '%" + queryParam["BatchCode"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["SelGDLX"].ToString()))
{
sql += " and a.MOType like '%" + queryParam["SelGDLX"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["POStatus"].ToString()))
{
string POStatus = queryParam["POStatus"].ToString();
if (POStatus == "0")
{
//sql += " and a.Quantity=ISNULL(c.LotQty,0)";
}
else if (POStatus == "1")
{
sql += " and ISNULL(c.LOTQTY,0)>=a.Quantity";
}
else
{
sql += " and ISNULL(c.LOTQTY,0)<a.Quantity";
}
}
}
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 ApplyNegCode, string Sequence, string Type, ref Pagination jqgridparam)
{
DataTable dt = new DataTable();
object Figure = GetDecimalDigits();
List<DbParameter> parameter = new List<DbParameter>();
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
string sql = @" select c.TransCode,c.TransSequence ,a.ID,a.LotNo, CAST( a.Quantity AS DECIMAL(18,{0})) as Quantity ,a.MUSERName,a.MTIME
,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,
case when isnull(d.ID,'')='' then '未入库' else '已入库' end as isStorage,
case when g.InvIQC='1'and isnull(e.ID,'')='' then '未检验' else '免检' end as isInspection,
case when g.InvIQC='1' then '是' else '否' end as isExemption,a.Amount,
convert(varchar(20),a.ProductDate,23) as ProductDate,convert(varchar(20),a.ExpirationDate,23) as ExpirationDate ,g.EffectiveDays
from ICSInventoryLot a
left join (select count(LotNo) LotNoCount,LotNo,WorkPoint from ICSWareHouseLotInfo
group by LotNo,WorkPoint)b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
left join ICSInventoryLotDetail c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
left join dbo.ICSWareHouseLotInfo d on a.LotNo=d.LotNo and a.WorkPoint=d.WorkPoint
left join ICSInspection e on a.LotNo=e.LotNo and e.Enable='1' and a.WorkPoint=e.WorkPoint
left join ICSInventory g on a.InvCode=g.InvCode and a.WorkPoint=g.WorkPoint
WHERE isnull(a.EATTRIBUTE1,'')='' and a.Type='" + Type + "' and c.TransCode='" + ApplyNegCode + "' and c.TransSequence='" + Sequence + "' and a.WorkPoint in ('" + WorkPoint + "')";
sql = string.Format(sql, Figure);
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
}
//获取小数点数位
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 DataTable GetSubGridJsonChengPingByCreate(string OOCode, string Sequence, string WorkPoint)
{
DataTable dt = new DataTable();
object Figure = GetDecimalDigits();
List<DbParameter> parameter = new List<DbParameter>();
string sql = @"select d.Enable, a.InvCode,b.ClassCode,b.InvName,b.InvStd,b.InvUnit, CAST( a.Quantity AS DECIMAL(18,{0})) as Quantity,CAST( a.Amount as DECIMAL(18,{0})) as Amount, CAST( isnull(c.LOTQTY,0) as DECIMAL(18,{0})) as CreatedQty,
CAST( (a.Quantity-ISNULL(c.LOTQTY,0)) as DECIMAL(18,{0})) as thisCreateQty,
case when b.AmountEnable='1' then CAST( (a.Quantity-ISNULL(c.LOTQTY,0)) as DECIMAL(18,{0}))/isnull(b.EATTRIBUTE1,1) else CAST( (a.Quantity-ISNULL(c.LOTQTY,0)) as DECIMAL(18,{0})) end as SurplusCreateQty,
isnull(b.EffectiveEnable,0) as EffectiveEnable, CAST( isnull(b.EffectiveDays,0) as DECIMAL(18,{0})) as EffectiveDays
,e.ProjectCode,e.BatchCode,e.Version,e.Brand,e.cFree1,e.cFree2,e.cFree3,e.cFree4,e.cFree5,e.cFree6,e.cFree7,e.cFree8,e.cFree9,e.cFree10
from ICSOutsourcingOrder a
left join ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join (
select b.TransCode,b.TransSequence,sum(isnull(Quantity,0)) LOTQTY from ICSInventoryLot a
left join ICSInventoryLotDetail b on a.LotNo=b.LotNo and a.Type='3'
where isnull(a.EATTRIBUTE1,'')=''
group by b.TransCode,b.TransSequence
) c on a.OOCode=c.TransCode and a.Sequence=c.TransSequence
left join ICSConfiguration d on d.Code='CompleteVerification001' and a.WorkPoint =d.WorkPoint
left join ICSExtension e on a.ExtensionID=e.ID and a.WorkPoint=e.WorkPoint
WHERE 1 =1 and a.Status='2'
and a.OOCode='" + OOCode + "' and a.Sequence='" + Sequence + "'";
sql += " and a.WorkPoint='" + WorkPoint + "'";
sql = string.Format(sql, Figure);
return Repository().FindTableBySql(sql.ToString());
}
//查询超生比例
public DataTable GetScale()
{
DataTable dt = new DataTable();
List<DbParameter> parameter = new List<DbParameter>();
string sql = @" SELECT Top 1 a.F_Define1 as Prorate
FROM [dbo].[Sys_SRM_ItemsDetail] a
LEFT JOIN [dbo].[Sys_SRM_Items] b ON a.F_ItemId=b.F_Id AND b.F_EnabledMark='1'
WHERE b.F_EnCode='CS0001' ORDER BY a.F_ItemCode";
return Repository().FindTableBySql(sql.ToString());
}
//调用接口来显示本次可生产数量
public object GetAbsolveQty(string OOCode, string Sequence,string Qty)
{
decimal AvailQty = 0;
decimal AllQty = 0;
string msg = "";
string sql = @"select
a.WorkPoint as WorkPoint,
a.OODetailID as DetailID,
'生产订单' as Type,
cast( isnull(c.LOTQTY,0) as DECIMAL(18,4)) as InQty,
" + Qty + @" as Qty,
flag.F_Define1 as Prorate
from ICSOutsourcingOrder a
left join ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join (
select b.TransCode,b.TransSequence,sum(isnull(Quantity,0)) LOTQTY from ICSInventoryLot a
left join ICSInventoryLotDetail b on a.LotNo=b.LotNo and a.Type='3'
where isnull(a.EATTRIBUTE1,'')=''
group by b.TransCode,b.TransSequence
) c on a.OOCode=c.TransCode and a.Sequence=c.TransSequence
left join (
select b.TransCode,PARSENAME(REPLACE(b.TransSequence, '~', '.'), 2) as TransSequence ,sum(isnull(Quantity,0)) OOPickQTY from ICSInventoryLot a
left join ICSInventoryLotDetail b on a.LotNo=b.LotNo and a.Type='20'
where isnull(a.EATTRIBUTE1,'')=''
group by b.TransCode,PARSENAME(REPLACE(b.TransSequence, '~', '.'), 2)
) f on a.OOCode=f.TransCode and a.Sequence=f.TransSequence
left join ICSConfiguration d on d.Code='CompleteVerification001' and a.WorkPoint =d.WorkPoint
left join ICSExtension e on a.ExtensionID=e.ID and a.WorkPoint=e.WorkPoint
left join Sys_SRM_ItemsDetail flag on flag.F_ItemCode='GDCS0001'
WHERE 1 =1 and a.Status='2'
and a.OOCode='" + OOCode + "' and a.Sequence='" + Sequence + "'";
DataTable dt = SqlHelper.GetDataTableBySql(sql);
string Inputstr = JsonConvert.SerializeObject(dt);
string APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "Completeamnew/Approve";
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();
if (Success.ToUpper() == "FALSE")
{
msg = MessAge;
}
else
{
if (msg == "")
{
JObject dataObj = JObject.Parse(Obj["Data"].ToString());
AvailQty = decimal.Parse(dataObj["AvailQty"].ToString());
AllQty = decimal.Parse(dataObj["AllQty"].ToString());
//JArray res = (JArray)JsonConvert.DeserializeObject(Obj["Data"].ToString());
//foreach (var item in res)
//{
// JObject jo = (JObject)item;
// //var aa= jo["min_lotqty"].ToString();
// Num = Convert.ToDecimal(jo["AllQty"].ToString());
//}
}
}
var Header = new
{
msg = msg,
AvailQty = AvailQty,
AllQty= AllQty,
};
return Header;
}
//接口api解析
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 int SubmitFormChengPing(string OOCode, string Sequence, string keyValue, string WorkPoint,string AMEnable)
{
var queryParam = keyValue.ToJObject();
string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
int createPageCount = Convert.ToInt32(queryParam["createPageCount"].ToString());
decimal minPackQty = Convert.ToDecimal(queryParam["minPackQty"].ToString());
decimal thisCreateQty = Convert.ToDecimal(queryParam["thisCreateQty"].ToString());
decimal LOTQTY = minPackQty;
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
//string VenCode = GetVendorCode(ApplyNegCode, Sequence, WorkPoint);
string Pre = "CP" + OOCode + (Convert.ToInt32(Sequence)).ToString("D4");
string sql = string.Empty;
//string VendorLot = queryParam["VendorLot"].ToString();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string PRODUCTDATE = queryParam["PRODUCTDATE"].ToString();
string InvCode = queryParam["ClassCode"].ToString();
string sqls = string.Empty;
string Colspan = "";
string str1 = "";
string LotNo=string.Empty;
List<string> ExtensionIDList = new List<string>();
for (int i = 0; i < createPageCount; i++)
{
if (i + 1 == createPageCount)
{
if (minPackQty * createPageCount > thisCreateQty)
{
LOTQTY = thisCreateQty - (minPackQty * (createPageCount - 1));
}
}
Colspan = queryParam["ProjectCode"].ToString() + "~" + queryParam["BatchCode"].ToString() + "~" + queryParam["Version"].ToString() + "~" + queryParam["Brand"].ToString() + "~" + queryParam["cFree1"].ToString() + "~" + queryParam["cFree2"].ToString() + "~" + queryParam["cFree3"].ToString() + "~" + queryParam["cFree4"].ToString() + "~" + queryParam["cFree5"].ToString() + "~" + queryParam["cFree6"].ToString() + "~" + queryParam["cFree7"].ToString() + "~" + queryParam["cFree8"].ToString() + "~" + queryParam["cFree9"].ToString() + "~" + queryParam["cFree10"].ToString();
sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
object ExtensionID = SqlHelper.ExecuteScalar(sqls);
bool flag = true;
foreach (var item in ExtensionIDList)
{
if (item == Colspan + WorkPoint)
{
flag = false;
}
}
if (ExtensionID == null && flag == true)
{
str1 = Guid.NewGuid().ToString();
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}' )",
str1, Colspan, queryParam["ProjectCode"].ToString(), queryParam["BatchCode"].ToString(), queryParam["Version"].ToString(), queryParam["Brand"].ToString(), queryParam["cFree1"].ToString(), queryParam["cFree2"].ToString(), queryParam["cFree3"].ToString(), queryParam["cFree4"].ToString(), queryParam["cFree5"].ToString(), queryParam["cFree6"].ToString(), queryParam["cFree7"].ToString(), queryParam["cFree8"].ToString(), queryParam["cFree9"].ToString(), queryParam["cFree10"].ToString(),
MUSER, MUSERNAME, WorkPoints);
}
else if (ExtensionID != null)
{
str1 = ExtensionID.ToString();
}
ExtensionIDList.Add(Colspan + WorkPoint);
//传过来的参数判断是否是奥美的项目奥美项目执行独有生成条码规则
if (AMEnable == "true")
{
LotNo = SqlHelper.GetLOTNumber(InvCode, WorkPoint);
}
else
{
LotNo = GetSerialCode(WorkPoint, "ICSInventoryLot", "LotNO", Pre, 5);
}
sql += string.Format(@"Insert into ICSInventoryLotDetail(LotNo, TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint)
Values('{0}','{1}','{2}','{3}','{4}',getdate(),'{5}' )",
LotNo, OOCode, Sequence, MUSER, MUSERNAME, WorkPoints);
//if (SqlHelper.ExecuteNonQuery(sqls) > 0)
//{
sql += string.Format(@"insert into ICSInventoryLot
(ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,ExtensionID,Type,MUSER,MUSERName,MTIME,WorkPoint,Amount
,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,EATTRIBUTE9,EATTRIBUTE10)
select
newid(),'{0}',InvCode,'{1}','{10}','{2}','{8}','201','{3}','{4}', getdate(),'{5}','{9}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}'
from ICSOutsourcingOrder where OOCode='{6}' and Sequence='{7}' and WorkPoint='{5}' ",
LotNo, PRODUCTDATE, LOTQTY, MUSER, MUSERNAME, WorkPoints, OOCode, Sequence, str1, Convert.ToDecimal(queryParam["Amount"].ToString()) / minPackQty * LOTQTY, queryParam["ExpirationDate"].ToString()
, queryParam["EATTRIBUTE1"].ToString(), queryParam["EATTRIBUTE2"].ToString(), queryParam["EATTRIBUTE3"].ToString(), queryParam["EATTRIBUTE4"].ToString(), queryParam["EATTRIBUTE5"].ToString(),
queryParam["EATTRIBUTE6"].ToString(), queryParam["EATTRIBUTE7"].ToString(), queryParam["EATTRIBUTE8"].ToString(), queryParam["EATTRIBUTE9"].ToString(), queryParam["EATTRIBUTE10"].ToString());
sql += "\r\n";
//}
}
int count = SqlHelper.CmdExecuteNonQueryLi(sql);
return count;
}
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 Repository().FindTableBySql(sql.ToString());
return SqlHelper.ExecuteScalar(sql).ToString();
//return DbHelper.ExecuteScalar(CommandType.Text, sql).ToString();
}
//委外子件
public DataTable GetGridJsonOOPick(string queryJson, ref Pagination jqgridparam)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"select a.ID,d.OOCode,d.Sequence+'~'+ a.Sequence as Sequence,a.PickID,a.InvCode as ZInvCode,d.InvCode as MInvCode,b.InvName,b.InvDesc,b.InvStd,b.InvUnit,
CAST(a.Quantity AS DECIMAL(18,{0})) as Quantity ,a.OODetailID,
CAST(a.Amount AS DECIMAL(18,{0})) as Amount,CAST(a.IssueQuantity AS DECIMAL(18,{0})) as IssueQuantity ,a.ExtensionID,a.MUSERName,a.MTIME ,CAST(c.LOTQTY AS DECIMAL(18,{0})) as LOTQTY
,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,a.EATTRIBUTE1,a.EATTRIBUTE2,a.EATTRIBUTE3,a.EATTRIBUTE4,a.EATTRIBUTE5,a.EATTRIBUTE6,a.EATTRIBUTE7,a.EATTRIBUTE8,
a.EATTRIBUTE9,a.EATTRIBUTE10
from ICSOOPick a
inner join ICSOutsourcingOrder d on a.OODetailID=d.OODetailID and a.WorkPoint=d.WorkPoint and d.Status='2'
inner join ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join (
select mm.TransCode,mm.TransSequence,sum(isnull(ee.Quantity,0)) LOTQTY,ee.WorkPoint from ICSInventoryLot ee
left join ICSInventoryLotDetail mm on ee.LotNo=mm.LotNo and ee.type='20'
where isnull(ee.EATTRIBUTE1,'')=''
group by mm.TransCode,mm.TransSequence,ee.WorkPoint
)c
on d.OOCode=c.TransCode and d.Sequence+'~'+a.Sequence=c.TransSequence and a.WorkPoint=c.WorkPoint
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
where a.EATTRIBUTE1='1' ";
sql = string.Format(sql, Figure);
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
{
sql += " and d.OOCode like '%" + queryParam["POCode"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
{
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
{
sql += " and b.InvName like '%" + queryParam["InvName"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["BatchCode"].ToString()))
{
sql += " and f.BatchCode like '%" + queryParam["BatchCode"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["POStatus"].ToString()))
{
string POStatus = queryParam["POStatus"].ToString();
if (POStatus == "0")
{
//sql += " and a.Quantity=ISNULL(c.LotQty,0)";
}
else if (POStatus == "1")
{
sql += " and ISNULL(c.LOTQTY,0)>=a.Quantity";
}
else
{
sql += " and ISNULL(c.LOTQTY,0)<a.Quantity";
}
}
}
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 GetSubGridJsonOOPickCreate(string OOPickCode, string Sequence, string WorkPoint)
{
DataTable dt = new DataTable();
object Figure = GetDecimalDigits();
List<DbParameter> parameter = new List<DbParameter>();
string sql = @"
select
a.InvCode,b.InvName,b.ClassCode,b.InvStd,b.InvUnit, a.Quantity as Quantity,
cast( isnull(c.LOTQTY,0)+isnull(f.GDQTY,0) as DECIMAL(18,4)) as CreatedQty,
cast( a.Amount as DECIMAL(18,4)) as Amount,
isnull(b.EffectiveEnable,0) as EffectiveEnable,
cast( isnull(b.EffectiveDays,0) as DECIMAL(18,4)) as EffectiveDays ,
CAST( (a.Quantity-ISNULL(c.LOTQTY,0)-isnull(f.GDQTY,0)) as DECIMAL(18,4)) as thisCreateQty,
case when b.AmountEnable='1' then CAST( (a.Quantity-ISNULL(c.LOTQTY,0)-isnull(f.GDQTY,0)) as DECIMAL(18,4))/isnull(b.EATTRIBUTE1,1) else CAST( (a.Quantity-ISNULL(c.LOTQTY,0)-isnull(f.GDQTY,0)) as DECIMAL(18,4)) end SurplusCreateQty
,e.ProjectCode,e.BatchCode,e.Version,e.Brand,e.cFree1,e.cFree2,e.cFree3,e.cFree4,e.cFree5,e.cFree6,e.cFree7,e.cFree8,e.cFree9,e.cFree10
from ICSOOPick a
left join ICSOutsourcingOrder d on a.OODetailID=d.OODetailID and a.WorkPoint=d.WorkPoint and d.Status='2'
--汇总副产品已生成条码数量
left join ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join (
select b.TransCode,b.TransSequence,sum(isnull(Quantity,0)) LOTQTY from ICSInventoryLot a
left join ICSInventoryLotDetail b on a.LotNo=b.LotNo and a.Type='20'
where isnull(a.EATTRIBUTE1,'')=''
group by b.TransCode,b.TransSequence
) c on d.OOCode=c.TransCode and d.Sequence+'~'+a.Sequence=c.TransSequence
--汇总工单已生成条码
left join (
select b.TransCode,b.TransSequence ,sum(isnull(Quantity,0)) GDQTY from ICSInventoryLot a
left join ICSInventoryLotDetail b on a.LotNo=b.LotNo and a.Type='3'
where isnull(a.EATTRIBUTE1,'')=''
group by b.TransCode,b.TransSequence
) f on d.OOCode=f.TransCode and d.Sequence=f.TransSequence
left join ICSExtension e on d.ExtensionID=e.ID and a.WorkPoint=e.WorkPoint
WHERE 1=1
and d.OOCode='" + OOPickCode + "' and d.Sequence+'~'+a.Sequence='" + Sequence + "'";
sql += " and a.WorkPoint='" + WorkPoint + "'";
sql = string.Format(sql, Figure);
return Repository().FindTableBySql(sql.ToString());
}
//副产品调用接口获取可生成数量
public object GetOOPickAbsolveQty(string OOCode, string Sequence, string Qty)
{
decimal AvailQty = 0;
decimal AllQty = 0;
string msg = "";
string sql = @"
select
a.WorkPoint as WorkPoint,
a.OODetailID as DetailID,
'生产订单' as Type,
cast( isnull(c.LOTQTY,0) as DECIMAL(18,4)) as InQty,
" + Qty + @" as Qty,
flag.F_Define1 as Prorate
from ICSOOPick a
left join ICSOutsourcingOrder d on a.OODetailID=d.OODetailID and a.WorkPoint=d.WorkPoint and d.Status='2'
--汇总副产品已生成条码数量
left join ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join (
select b.TransCode,b.TransSequence,sum(isnull(Quantity,0)) LOTQTY from ICSInventoryLot a
left join ICSInventoryLotDetail b on a.LotNo=b.LotNo and a.Type='20'
where isnull(a.EATTRIBUTE1,'')=''
group by b.TransCode,b.TransSequence
) c on d.OOCode=c.TransCode and d.Sequence+'~'+a.Sequence=c.TransSequence
--汇总工单已生成条码
left join (
select b.TransCode,b.TransSequence ,sum(isnull(Quantity,0)) GDQTY from ICSInventoryLot a
left join ICSInventoryLotDetail b on a.LotNo=b.LotNo and a.Type='3'
where isnull(a.EATTRIBUTE1,'')=''
group by b.TransCode,b.TransSequence
) f on d.OOCode=f.TransCode and d.Sequence=f.TransSequence
left join ICSExtension e on d.ExtensionID=e.ID and a.WorkPoint=e.WorkPoint
left join Sys_SRM_ItemsDetail flag on flag.F_ItemCode='GDCS0001'
WHERE 1=1
and d.OOCode='" + OOCode + "' and d.Sequence+'~'+a.Sequence='" + Sequence + "'";
DataTable dt = SqlHelper.GetDataTableBySql(sql);
string Inputstr = JsonConvert.SerializeObject(dt);
string APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "Completeamnew/Approve";
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();
if (Success.ToUpper() == "FALSE")
{
msg = MessAge;
}
else
{
if (msg == "")
{
JObject dataObj = JObject.Parse(Obj["Data"].ToString());
AvailQty = decimal.Parse(dataObj["AvailQty"].ToString());
AllQty = decimal.Parse(dataObj["AllQty"].ToString());
}
}
var Header = new
{
msg = msg,
AvailQty = AvailQty,
AllQty = AllQty,
};
return Header;
}
//联副产品
public int SubmitFormOOPick(string OOPickCode, string Sequence, string keyValue, string WorkPoint,string AMEnable)
{
var queryParam = keyValue.ToJObject();
string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
int createPageCount = Convert.ToInt32(queryParam["createPageCount"].ToString());
decimal minPackQty = Convert.ToDecimal(queryParam["minPackQty"].ToString());
decimal thisCreateQty = Convert.ToDecimal(queryParam["thisCreateQty"].ToString());
decimal LOTQTY = minPackQty;
string Sequences = Sequence.Replace("~", "");
string Pre = "OOPick" + OOPickCode + (Convert.ToInt32(Sequences)).ToString("D4");
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string sql = string.Empty;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string PRODUCTDATE = queryParam["PRODUCTDATE"].ToString();
string InvCode = queryParam["ClassCode"].ToString();
string sqls = string.Empty;
string Colspan = "";
string str1 = "";
string LotNo = string.Empty;
List<string> ExtensionIDList = new List<string>();
for (int i = 0; i < createPageCount; i++)
{
if (i + 1 == createPageCount)
{
if (minPackQty * createPageCount > thisCreateQty)
{
LOTQTY = thisCreateQty - (minPackQty * (createPageCount - 1));
}
}
Colspan = queryParam["ProjectCode"].ToString() + "~" + queryParam["BatchCode"].ToString() + "~" + queryParam["Version"].ToString() + "~" + queryParam["Brand"].ToString() + "~" + queryParam["cFree1"].ToString() + "~" + queryParam["cFree2"].ToString() + "~" + queryParam["cFree3"].ToString() + "~" + queryParam["cFree4"].ToString() + "~" + queryParam["cFree5"].ToString() + "~" + queryParam["cFree6"].ToString() + "~" + queryParam["cFree7"].ToString() + "~" + queryParam["cFree8"].ToString() + "~" + queryParam["cFree9"].ToString() + "~" + queryParam["cFree10"].ToString();
sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
object ExtensionID = SqlHelper.ExecuteScalar(sqls);
bool flag = true;
foreach (var item in ExtensionIDList)
{
if (item == Colspan + WorkPoint)
{
flag = false;
}
}
if (ExtensionID == null && flag == true)
{
str1 = Guid.NewGuid().ToString();
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}' )",
str1, Colspan, queryParam["ProjectCode"].ToString(), queryParam["BatchCode"].ToString(), queryParam["Version"].ToString(), queryParam["Brand"].ToString(), queryParam["cFree1"].ToString(), queryParam["cFree2"].ToString(), queryParam["cFree3"].ToString(), queryParam["cFree4"].ToString(), queryParam["cFree5"].ToString(), queryParam["cFree6"].ToString(), queryParam["cFree7"].ToString(), queryParam["cFree8"].ToString(), queryParam["cFree9"].ToString(), queryParam["cFree10"].ToString(),
MUSER, MUSERNAME, WorkPoints);
}
else if (ExtensionID != null)
{
str1 = ExtensionID.ToString();
}
ExtensionIDList.Add(Colspan + WorkPoint);
//传过来的参数判断是否是奥美的项目奥美项目执行独有生成条码规则
if (AMEnable == "true")
{
LotNo = SqlHelper.GetLOTNumber(InvCode, WorkPoint);
}
else
{
LotNo = GetSerialCode(WorkPoint, "ICSInventoryLot", "LotNO", Pre, 5);
}
sql += string.Format(@"Insert into ICSInventoryLotDetail(LotNo, TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint)
Values('{0}','{1}','{2}','{3}','{4}',getdate(),'{5}' )",
LotNo, OOPickCode, Sequence, MUSER, MUSERNAME, WorkPoints);
sql += string.Format(@"insert into ICSInventoryLot
(ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,ExtensionID,Type,MUSER,MUSERName,MTIME,WorkPoint,Amount ,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,EATTRIBUTE9,EATTRIBUTE10)
select
newid(),'{0}',a.InvCode,'{1}','{10}','{2}','{8}','20','{3}','{4}', getdate(),'{5}','{9}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}'
from ICSOOPick a left join ICSOutsourcingOrder d on a.OODetailID=d.OODetailID and a.WorkPoint=d.WorkPoint and d.Status='2' where d.OOCode='{6}' and d.Sequence+'~'+a.Sequence='{7}' and a.WorkPoint='{5}' ",
LotNo, PRODUCTDATE, LOTQTY, MUSER, MUSERNAME, WorkPoints, OOPickCode, Sequence, str1, Convert.ToDecimal(queryParam["Amount"].ToString()) / minPackQty * LOTQTY, queryParam["ExpirationDate"].ToString()
, queryParam["EATTRIBUTE1"].ToString(), queryParam["EATTRIBUTE2"].ToString(), queryParam["EATTRIBUTE3"].ToString(), queryParam["EATTRIBUTE4"].ToString(), queryParam["EATTRIBUTE5"].ToString(),
queryParam["EATTRIBUTE6"].ToString(), queryParam["EATTRIBUTE7"].ToString(), queryParam["EATTRIBUTE8"].ToString(), queryParam["EATTRIBUTE9"].ToString(), queryParam["EATTRIBUTE10"].ToString());
sql += "\r\n";
}
int count = SqlHelper.CmdExecuteNonQueryLi(sql);
return count;
}
public DataTable GetByproductGridJson(string queryJson, ref Pagination jqgridparam)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
#region [SQL]
string sql = @" select a.ID,a.LotNo,a.InvCode,c.InvName,c.InvStd,a.Quantity,a.MUSERName,b.MTIME as WMTIME,a.MTIME
from dbo.ICSInventoryLot a
left join dbo.ICSWareHouseLotInfo b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
inner join dbo.ICSInventory c on a.InvCode=c.InvCode
where a.Type='19'";
// sql += " WHERE 1=1";
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["LotNo"].ToString()))
{
sql += " and a.LotNo like '%" + queryParam["LotNo"].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 GetInvCode()
{
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
string sql = @"
select '' as Code,'' as InvCode
union all
SELECT InvCode as Code ,InvCode as InvCode FROM dbo.ICSInventory WITH (NOLOCK) WHERE 1=1 and SUBSTRING(ClassCode, 1, 2) IN ('05') ";
sql += " and WorkPoint='" + WorkPoint + "'";
DataTable dt = SqlHelper.GetDataTableBySql(sql);
return dt;
}
public int SubmitFormOOPickNoCode (string keyValue, string WorkPoint, string AMEnable)
{
var queryParam = keyValue.ToJObject();
string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
int createPageCount = Convert.ToInt32(queryParam["createPageCount"].ToString());
decimal minPackQty = Convert.ToDecimal(queryParam["minPackQty"].ToString());
string InvCode = queryParam["InvCode"].ToString();
decimal LOTQTY = minPackQty;
//string Sequences = Sequence.Replace("~", "");
//string Pre = "OOPick" + OOPickOOCode + (Convert.ToInt32(Sequences)).ToString("D4");
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string sql = string.Empty;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
// string PRODUCTDATE = queryParam["PRODUCTDATE"].ToString();
string ClassCode = queryParam["ClassCode"].ToString();
string sqls = string.Empty;
string Colspan = "";
string str1 = "";
string LotNo = string.Empty;
List<string> ExtensionIDList = new List<string>();
for (int i = 0; i < createPageCount; i++)
{
//if (i + 1 == createPageCount)
//{
// if (minPackQty * createPageCount > thisCreateQty)
// {
// LOTQTY = thisCreateQty - (minPackQty * (createPageCount - 1));
// }
//}
Colspan = queryParam["ProjectCode"].ToString() + "~" + queryParam["BatchCode"].ToString() + "~" + queryParam["Version"].ToString() + "~" + queryParam["Brand"].ToString() + "~" + queryParam["cFree1"].ToString() + "~" + queryParam["cFree2"].ToString() + "~" + queryParam["cFree3"].ToString() + "~" + queryParam["cFree4"].ToString() + "~" + queryParam["cFree5"].ToString() + "~" + queryParam["cFree6"].ToString() + "~" + queryParam["cFree7"].ToString() + "~" + queryParam["cFree8"].ToString() + "~" + queryParam["cFree9"].ToString() + "~" + queryParam["cFree10"].ToString();
sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
object ExtensionID = SqlHelper.ExecuteScalar(sqls);
bool flag = true;
foreach (var item in ExtensionIDList)
{
if (item == Colspan + WorkPoint)
{
flag = false;
}
}
if (ExtensionID == null && flag == true)
{
str1 = Guid.NewGuid().ToString();
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}' )",
str1, Colspan, queryParam["ProjectCode"].ToString(), queryParam["BatchCode"].ToString(), queryParam["Version"].ToString(), queryParam["Brand"].ToString(), queryParam["cFree1"].ToString(), queryParam["cFree2"].ToString(), queryParam["cFree3"].ToString(), queryParam["cFree4"].ToString(), queryParam["cFree5"].ToString(), queryParam["cFree6"].ToString(), queryParam["cFree7"].ToString(), queryParam["cFree8"].ToString(), queryParam["cFree9"].ToString(), queryParam["cFree10"].ToString(),
MUSER, MUSERNAME, WorkPoints);
}
else if (ExtensionID != null)
{
str1 = ExtensionID.ToString();
}
ExtensionIDList.Add(Colspan + WorkPoint);
//传过来的参数判断是否是奥美的项目奥美项目执行独有生成条码规则
if (AMEnable == "true")
{
LotNo = SqlHelper.GetLOTNumber(ClassCode, WorkPoint);
}
sql += string.Format(@"insert into ICSInventoryLot (ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,ExtensionID,Type,MUSER,MUSERName,MTIME,WorkPoint,Amount ,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,EATTRIBUTE9,EATTRIBUTE10)
values(newid(),'{0}','{1}',getdate(),'{2}','{3}','{4}','19','{5}','{6}',getdate(),'{7}','{8}','','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}')",
LotNo, InvCode, queryParam["ExpirationDate"].ToString(), LOTQTY, str1, MUSER, MUSERNAME, WorkPoints, queryParam["Amount"].ToString(),
queryParam["EATTRIBUTE2"].ToString(), queryParam["EATTRIBUTE3"].ToString(), queryParam["EATTRIBUTE4"].ToString(), queryParam["EATTRIBUTE5"].ToString(),
queryParam["EATTRIBUTE6"].ToString(), queryParam["EATTRIBUTE7"].ToString(), queryParam["EATTRIBUTE8"].ToString(), queryParam["EATTRIBUTE9"].ToString(), queryParam["EATTRIBUTE10"].ToString());
sql += "\r\n";
}
int count = SqlHelper.CmdExecuteNonQueryLi(sql);
return count;
}
}
}