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.
506 lines
26 KiB
506 lines
26 KiB
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.Common;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using System.Globalization;
|
|
|
|
namespace NFine.Application.DHAY
|
|
{
|
|
public class ICSFinishedProductShipmentApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
ICSCreateItemLotApp app = new ICSCreateItemLotApp();
|
|
public DataTable GetICSSDN(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string User = UserName.Substring(0, 2);
|
|
|
|
#region [SQL]
|
|
string sql = @" select
|
|
DISTINCT
|
|
a.id
|
|
,SDNCode
|
|
,a.Sequence
|
|
,a.SOCode
|
|
,a.SOSequence
|
|
,b.EATTRIBUTE3
|
|
,a.InvCode
|
|
,c.InvName
|
|
,c.EATTRIBUTE2
|
|
,b.EATTRIBUTE6
|
|
,a.EATTRIBUTE6 as Project
|
|
,a.EATTRIBUTE7
|
|
,c.InvStd
|
|
,isnull(a.Quantity,0) AS Quantity
|
|
,ISNULL(SDNQuantity, 0) AS SDNQuantity
|
|
,isnull(a.Quantity,0)-ISNULL(SDNQuantity, 0) as SYQty
|
|
,ISNULL(SDNQuantity, 0)*(1.1) AS KQty
|
|
,ISNULL(SDNQuantity, 0)*(1.1)-ISNULL(d.LotQty,0) AS SYSDNQuantity
|
|
,d.LotQty
|
|
,a.MUSER
|
|
,a.MTIME
|
|
,a.CusName
|
|
from ICSSDN a
|
|
left join ICSSO b on a.socode=b.socode and a.SOSequence=b.Sequence
|
|
left join ICSInventory c on a.InvCode=c.InvCode and a.WorkPoint=c.WorkPoint
|
|
left JOIN (SELECT SUM(ISNULL(Quantity,0))AS LotQty,TransCode,TransSequence FROM ICSFinishedProductShipment
|
|
WHERE type='1'
|
|
GROUP BY TransCode,TransSequence)d on SDNCode=d.TransCode and a.Sequence=d.TransSequence
|
|
WHERE a.Type='1' and isnull(SDNQuantity,0)<>0";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["SDNCode"].ToString()))
|
|
{
|
|
sql += " and a.SDNCode like '%" + queryParam["SDNCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["SOCode"].ToString()))
|
|
{
|
|
sql += " and a.SOCode like '%" + queryParam["SOCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["CusName"].ToString()))
|
|
{
|
|
sql += " and a.CusName like '%" + queryParam["CusName"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["CusInv"].ToString()))
|
|
{
|
|
sql += " and b.EATTRIBUTE3 like '%" + queryParam["CusInv"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
|
|
{
|
|
sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
|
|
{
|
|
sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
|
|
}
|
|
|
|
}
|
|
#endregion
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
/// <summary>
|
|
/// 获取流水号，条码信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSFinishedProductShipment(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
|
|
#region [SQL]
|
|
string sql = @"SELECT
|
|
ID
|
|
,LotNO
|
|
,quantity as minPackQty
|
|
,ExtensionID
|
|
,ProductDate
|
|
,PrintTimes
|
|
,LastPrintTime
|
|
,LastPrintUser
|
|
,MUSER
|
|
,MTIME
|
|
,EATTRIBUTE1
|
|
FROM ICSFinishedProductShipment";
|
|
sql += " WHERE 1=1 and TransCode='" + queryParam["SDNCode"].ToString() + "'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["FlagVersion"].ToString()))
|
|
{
|
|
sql += " and Type= '" + queryParam["FlagVersion"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["Sequence"].ToString()))
|
|
{
|
|
sql += " and TransSequence= '" + queryParam["Sequence"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["MOCode"].ToString()))
|
|
{
|
|
sql += " and EATTRIBUTE1= '" + queryParam["MOCode"].ToString() + "' ";
|
|
}
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取生成条码信息
|
|
/// </summary>
|
|
/// <param name="SDNCode"></param>
|
|
/// <param name="Sequence"></param>
|
|
/// <param name="WorkPoint"></param>
|
|
/// <param name="Type"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetSubGridJsonByCreate(string SDNCode, string Sequence, string WorkPoint,string Type)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string Workpont = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = $@"SELECT a.InvCode,b.InvName,b.EATTRIBUTE5 AS OldInvCode,'' as OldInvName,a.SOCode,
|
|
ISNULL(c.UseQuantity, 0) UseQuantity ,d.EATTRIBUTE3 as CusInvCode,a.EATTRIBUTE7 as CusOrders
|
|
FROM ICSSDN a
|
|
LEFT JOIN ICSInventory b ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
left JOIN (SELECT TransCode,TransSequence,WorkPoint,sum(Quantity) as UseQuantity FROM ICSFinishedProductShipment WHERE Type='{Type}' AND TransCode='{SDNCode}' AND TransSequence='{Sequence}' GROUP BY TransCode,TransSequence,WorkPoint )c ON a.SDNCode=c.TransCode AND a.Sequence=c.TransSequence AND a.WorkPoint=c.WorkPoint
|
|
left JOIN ICSSO d ON a.SOCode=d.SOCode AND a.SOSequence=d.Sequence and a.WorkPoint=d.WorkPoint
|
|
WHERE a.SDNCode='{SDNCode}' AND a.Sequence='{Sequence}'
|
|
AND a.WorkPoint='{Workpont}' ";
|
|
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取生产日期
|
|
/// </summary>
|
|
/// <param name="SDNCode"></param>
|
|
/// <param name="Sequence"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetProductDate(string SDNCode, string Sequence)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string Workpont = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = $@" SELECT top 1 ISNULL(ProductDate, '') ProductDate FROM ICSFinishedProductShipment WHERE TransCode='{SDNCode}' AND TransSequence='{Sequence}' and WorkPoint='{Workpont}' ORDER BY ProductDate asc
|
|
";
|
|
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
/// <summary>
|
|
/// 批次信息
|
|
/// </summary>
|
|
/// <param name="POCode"></param>
|
|
/// <param name="PORow"></param>
|
|
/// <param name="WorkPoint"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetBatchInformation(string SDNCode, string Sequence)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string Workpont = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
|
|
string sql = $@"SELECT '' as Code,'' as Name
|
|
UNION all
|
|
--非期初工单（生成条码的来源单据为工单）
|
|
SELECT DISTINCT b.TransCode as Code,b.TransCode as Name
|
|
FROM ICSWareHouseLotInfoLog a
|
|
INNER JOIN ( SELECT a.LotNo, ISnull(b.type,a.Type) Type,a.WorkPoint,a.ExtensionID,a.EATTRIBUTE5 FROM ICSInventoryLot a
|
|
left JOIN ICSInventoryLot b ON a.EATTRIBUTE1=b.LotNo AND a.WorkPoint =b.WorkPoint ) d ON a.LotNo=d.lotNo and a.workPoint=d.WorkPoint AND d.Type<>'0' AND d.Type<>'4'
|
|
INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.lotNo and a.workPoint=b.WorkPoint
|
|
INNER JOIN ICSMO c ON c.MOCode=b.TransCode AND c.Sequence=b.TransSequence AND c.WorkPoint=b.WorkPoint
|
|
WHERE a.TransCode='{SDNCode}' AND a.TransSequence='{Sequence}' AND a.WorkPoint='{Workpont}' AND a.BusinessCode='19'
|
|
|
|
UNION
|
|
--非期初非工单（生成条码的来源单据并非工单，显示批次）如果是分批取分批前条码类型
|
|
SELECT DISTINCT e.BatchCode as Code,e.BatchCode as Name
|
|
FROM ICSWareHouseLotInfoLog a
|
|
INNER JOIN (SELECT a.LotNo, ISnull(b.type,a.Type) Type,a.WorkPoint,a.ExtensionID FROM ICSInventoryLot a
|
|
left JOIN ICSInventoryLot b ON a.EATTRIBUTE1=b.LotNo AND a.WorkPoint =b.WorkPoint ) b ON a.LotNo=b.lotNo and a.workPoint=b.WorkPoint AND b.Type<>'0' AND b.Type<>'4'
|
|
INNER JOIN ICSInventoryLotDetail d ON a.LotNo=d.lotNo and a.workPoint=d.WorkPoint
|
|
left JOIN ICSMO c ON c.MOCode=d.TransCode AND c.Sequence=d.TransSequence AND c.WorkPoint=d.WorkPoint
|
|
left JOIN ICSExtension e ON e.Id=b.ExtensionID
|
|
WHERE a.TransCode='{SDNCode}' AND a.TransSequence='{Sequence}' AND a.WorkPoint='{Workpont}' AND a.BusinessCode='19' AND c. MOCode is NULL
|
|
|
|
UNION
|
|
--期初
|
|
SELECT DISTINCT b.BatchCode as Code,b.BatchCode as Name
|
|
FROM ICSWareHouseLotInfoLog a
|
|
INNER JOIN ( SELECT a.LotNo, ISnull(b.type,a.Type) Type,a.WorkPoint,a.ExtensionID,a.EATTRIBUTE5 FROM ICSInventoryLot a
|
|
left JOIN ICSInventoryLot b ON a.EATTRIBUTE1=b.LotNo AND a.WorkPoint =b.WorkPoint ) c ON a.LotNo=c.lotNo and a.workPoint=c.WorkPoint AND c.Type ='0'
|
|
INNER JOIN ICSExtension b ON c.ExtensionID=b.ID and a.workPoint=b.WorkPoint
|
|
WHERE a.TransCode='{SDNCode}' AND a.TransSequence='{Sequence}' AND a.WorkPoint='{Workpont}' AND a.BusinessCode='19'
|
|
UNION
|
|
--销售退货原条码(取销售退货备注工单) 如果是分批取分批前条码类型
|
|
SELECT DISTINCT c.EATTRIBUTE5 as Code,c.EATTRIBUTE5 as Name
|
|
FROM ICSWareHouseLotInfoLog a
|
|
INNER JOIN ( SELECT a.LotNo, ISnull(b.type,a.Type) Type,a.WorkPoint,a.ExtensionID,a.EATTRIBUTE5 FROM ICSInventoryLot a
|
|
left JOIN ICSInventoryLot b ON a.EATTRIBUTE1=b.LotNo AND a.WorkPoint =b.WorkPoint ) c ON a.LotNo=c.lotNo and a.workPoint=c.WorkPoint AND c.Type ='4'
|
|
WHERE a.TransCode='{SDNCode}' AND a.TransSequence='{Sequence}' AND a.WorkPoint='{Workpont}' AND a.BusinessCode='19' ";
|
|
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取工单信息
|
|
/// </summary>
|
|
/// <param name="SDNCode"></param>
|
|
/// <param name="Sequence"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetMOCode(string SDNCode, string TransSequence, string Type)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string Workpont = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
|
|
string sql = $@"SELECT '' as Code,'' as Name
|
|
UNION all
|
|
SELECT distinct EATTRIBUTE1 AS Code,EATTRIBUTE1 AS Name From ICSFinishedProductShipment WHERE TransCode='{SDNCode}' AND TransSequence='{TransSequence}' AND Type='{Type}' AND WorkPoint='{Workpont}'
|
|
";
|
|
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取工单信息
|
|
/// </summary>
|
|
/// <param name="SDNCode"></param>
|
|
/// <param name="Sequence"></param>
|
|
/// <returns></returns>
|
|
public string MarkPrint(string Key)
|
|
{
|
|
try
|
|
{
|
|
string Workpont = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
Key = Key.Substring(2, Key.Length - 5);
|
|
string sql = $@"UPDATE ICSFinishedProductShipment SET PrintTimes=ISNULL(PrintTimes, 0)+1 ,LastPrintUser='{UserName}',LastPrintTime=GETDATE() WHERE ID IN({Key.TrimEnd('|')})
|
|
";
|
|
if (SqlHelper.CmdExecuteNonQuery(sql) > 0)
|
|
{
|
|
return "操作成功";
|
|
}
|
|
else
|
|
{
|
|
return "操作失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ex.Message;
|
|
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 批次数量
|
|
/// </summary>
|
|
/// <param name="MOCode"></param>
|
|
/// <param name="SDNCode"></param>
|
|
/// <param name="Sequence"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetBatchCodeCount(string MOCode, string SDNCode, string Sequence, string Type)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string Workpont = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = string.Empty;
|
|
// 1.工单数量 2.批次数量 3.销售退货数量
|
|
if (Type == "3")
|
|
{
|
|
sql = $@"
|
|
|
|
SELECT SUM(Quantity) as Quantity,sum(CurrentQuantity) as CurrentQuantity,aa.MOCode,aa.TransCode,d.UseQuantity from ( SELECT c.TransCode as MOCode,a.TransCode as TransCode,a.WorkPoint,sum(ISnull(a.Quantity,0)) as Quantity,sum(ISnull(a.Quantity,0)) as CurrentQuantity
|
|
from ICSWareHouseLotInfoLog a
|
|
INNER JOIN ICSWareHouseLotInfo b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
INNER JOIN ( SELECT a.LotNo, ISnull(b.type,a.Type) Type,a.WorkPoint,a.ExtensionID,a.EATTRIBUTE5
|
|
FROM (SELECT LotNo, Type, WorkPoint, ExtensionID, EATTRIBUTE5
|
|
,CASE WHEN Type='101' THEN dbo.fn_GetRootBarcode(LotNo) else EATTRIBUTE1 end EATTRIBUTE1
|
|
from ICSInventoryLot ) a
|
|
left JOIN ICSInventoryLot b ON a.EATTRIBUTE1=b.LotNo AND a.WorkPoint =b.WorkPoint
|
|
where ISnull(b.type,a.Type)='3') f ON a.LotNo=f.lotNo and a.workPoint=f.WorkPoint and f.Type <>'0'
|
|
inner JOIN ICSInventoryLotDetail c ON b.LotNo=c.LotNo AND b.WorkPoint=c.WorkPoint
|
|
WHERE a.TransCode='{SDNCode}' AND a.TransSequence='{Sequence}' AND c.TransCode='{MOCode}' and a.WorkPoint='{Workpont}' AND a.BusinessCode='19' GROUP BY
|
|
c.TransCode,a.TransCode,a.WorkPoint
|
|
|
|
UNION all
|
|
|
|
SELECT e.BatchCode as MOCode,a.TransCode as TransCode,a.WorkPoint,sum(ISnull(a.Quantity,0)) as Quantity,sum(ISnull(a.Quantity,0)) as CurrentQuantity
|
|
from ICSWareHouseLotInfoLog a
|
|
INNER JOIN ICSWareHouseLotInfo b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
INNER JOIN ( SELECT a.LotNo, ISnull(b.type,a.Type) Type,a.WorkPoint,a.ExtensionID,a.EATTRIBUTE5 FROM ICSInventoryLot a
|
|
left JOIN ICSInventoryLot b ON a.EATTRIBUTE1=b.LotNo AND a.WorkPoint =b.WorkPoint
|
|
where ISnull(b.type,a.Type)<>'4' and ISnull(b.type,a.Type)<>'3') f ON a.LotNo=f.lotNo and a.workPoint=f.WorkPoint
|
|
INNER JOIN ICSExtension e ON e.Id=f.ExtensionID and e.workPoint=f.WorkPoint
|
|
inner JOIN ICSInventoryLotDetail c ON b.LotNo=c.LotNo AND b.WorkPoint=c.WorkPoint
|
|
WHERE a.TransCode='{SDNCode}' AND a.TransSequence='{Sequence}' AND e.BatchCode='{MOCode}' and a.WorkPoint='{Workpont}' AND a.BusinessCode='19' GROUP BY
|
|
e.BatchCode,a.TransCode,a.WorkPoint
|
|
UNION all
|
|
|
|
SELECT f.EATTRIBUTE5 as MOCode,a.TransCode as TransCode,a.WorkPoint,sum(ISnull(a.Quantity,0)) as Quantity,sum(ISnull(a.Quantity,0)) as CurrentQuantity
|
|
from ICSWareHouseLotInfoLog a
|
|
INNER JOIN ICSWareHouseLotInfo b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
INNER JOIN ( SELECT a.LotNo, ISnull(b.type,a.Type) Type,a.WorkPoint,a.ExtensionID,a.EATTRIBUTE5 FROM ICSInventoryLot a
|
|
left JOIN ICSInventoryLot b ON a.EATTRIBUTE1=b.LotNo AND a.WorkPoint =b.WorkPoint where ISnull(b.type,a.Type)='4') f ON a.LotNo=f.lotNo and a.workPoint=f.WorkPoint AND f.Type ='4'
|
|
WHERE a.TransCode='{SDNCode}' AND a.TransSequence='{Sequence}' AND f.EATTRIBUTE5='{MOCode}' AND a.BusinessCode='19' GROUP BY
|
|
f.EATTRIBUTE5,a.TransCode,a.WorkPoint) aa
|
|
left join (SELECT sum(Quantity) as UseQuantity,WorkPoint,EATTRIBUTE1,TransCode FROM ICSFinishedProductShipment WHERE Type='{Type}' AND EATTRIBUTE1='{MOCode}' AND TransCode='{SDNCode}' AND TransSequence='{Sequence}' GROUP BY WorkPoint,TransCode,EATTRIBUTE1 ) d ON d.WorkPoint=aa.WorkPoint AND d.EATTRIBUTE1=aa.MOCode
|
|
GROUP BY aa.MOCode,aa.TransCode,d.UseQuantity ";
|
|
}
|
|
else
|
|
{
|
|
sql = $@"
|
|
|
|
SELECT SUM(Quantity) as Quantity,sum(CurrentQuantity) as CurrentQuantity,aa.MOCode,aa.TransCode,d.UseQuantity from ( SELECT c.TransCode as MOCode,a.TransCode as TransCode,a.WorkPoint,sum(ISnull(a.Quantity,0))*1.1 as Quantity,sum(ISnull(a.Quantity,0)) as CurrentQuantity
|
|
from ICSWareHouseLotInfoLog a
|
|
INNER JOIN ICSWareHouseLotInfo b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
INNER JOIN ( SELECT a.LotNo, ISnull(b.type,a.Type) Type,a.WorkPoint,a.ExtensionID,a.EATTRIBUTE5
|
|
FROM (SELECT LotNo, Type, WorkPoint, ExtensionID, EATTRIBUTE5
|
|
,CASE WHEN Type='101' THEN dbo.fn_GetRootBarcode(LotNo) else EATTRIBUTE1 end EATTRIBUTE1
|
|
from ICSInventoryLot ) a
|
|
left JOIN ICSInventoryLot b ON a.EATTRIBUTE1=b.LotNo AND a.WorkPoint =b.WorkPoint
|
|
where ISnull(b.type,a.Type)='3' ) f ON a.LotNo=f.lotNo and a.workPoint=f.WorkPoint and f.Type <>'0'
|
|
inner JOIN ICSInventoryLotDetail c ON b.LotNo=c.LotNo AND b.WorkPoint=c.WorkPoint
|
|
WHERE a.TransCode='{SDNCode}' AND a.TransSequence='{Sequence}' AND c.TransCode='{MOCode}' and a.WorkPoint='{Workpont}' AND a.BusinessCode='19' GROUP BY
|
|
c.TransCode,a.TransCode,a.WorkPoint
|
|
|
|
UNION all
|
|
|
|
SELECT e.BatchCode as MOCode,a.TransCode as TransCode,a.WorkPoint,sum(ISnull(a.Quantity,0))*1.1 as Quantity,sum(ISnull(a.Quantity,0)) as CurrentQuantity
|
|
from ICSWareHouseLotInfoLog a
|
|
INNER JOIN ICSWareHouseLotInfo b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
INNER JOIN ( SELECT a.LotNo, ISnull(b.type,a.Type) Type,a.WorkPoint,a.ExtensionID,a.EATTRIBUTE5 FROM ICSInventoryLot a
|
|
left JOIN ICSInventoryLot b ON a.EATTRIBUTE1=b.LotNo AND a.WorkPoint =b.WorkPoint
|
|
where ISnull(b.type,a.Type)<>'4' and ISnull(b.type,a.Type)<>'3') f ON a.LotNo=f.lotNo and a.workPoint=f.WorkPoint
|
|
INNER JOIN ICSExtension e ON e.Id=f.ExtensionID and e.workPoint=f.WorkPoint
|
|
inner JOIN ICSInventoryLotDetail c ON b.LotNo=c.LotNo AND b.WorkPoint=c.WorkPoint
|
|
WHERE a.TransCode='{SDNCode}' AND a.TransSequence='{Sequence}' AND e.BatchCode='{MOCode}' and a.WorkPoint='{Workpont}' AND a.BusinessCode='19' GROUP BY
|
|
e.BatchCode,a.TransCode,a.WorkPoint
|
|
UNION all
|
|
|
|
SELECT f.EATTRIBUTE5 as MOCode,a.TransCode as TransCode,a.WorkPoint,sum(ISnull(a.Quantity,0))*1.1 as Quantity,sum(ISnull(a.Quantity,0)) as CurrentQuantity
|
|
from ICSWareHouseLotInfoLog a
|
|
INNER JOIN ICSWareHouseLotInfo b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
INNER JOIN ( SELECT a.LotNo, ISnull(b.type,a.Type) Type,a.WorkPoint,a.ExtensionID,a.EATTRIBUTE5 FROM ICSInventoryLot a
|
|
left JOIN ICSInventoryLot b ON a.EATTRIBUTE1=b.LotNo AND a.WorkPoint =b.WorkPoint where ISnull(b.type,a.Type)='4') f ON a.LotNo=f.lotNo and a.workPoint=f.WorkPoint AND f.Type ='4'
|
|
WHERE a.TransCode='{SDNCode}' AND a.TransSequence='{Sequence}' AND f.EATTRIBUTE5='{MOCode}' and a.WorkPoint='{Workpont}' AND a.BusinessCode='19' GROUP BY
|
|
f.EATTRIBUTE5,a.TransCode,a.WorkPoint) aa
|
|
left join (SELECT sum(Quantity) as UseQuantity,WorkPoint,EATTRIBUTE1,TransCode FROM ICSFinishedProductShipment WHERE Type='{Type}' AND EATTRIBUTE1='{MOCode}' AND TransCode='{SDNCode}' AND TransSequence='{Sequence}' GROUP BY WorkPoint,TransCode,EATTRIBUTE1 ) d ON d.WorkPoint=aa.WorkPoint AND d.EATTRIBUTE1=aa.MOCode
|
|
GROUP BY aa.MOCode,aa.TransCode,d.UseQuantity ";
|
|
|
|
}
|
|
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 生成条码
|
|
/// </summary>
|
|
/// <param name="SDNCode"></param>
|
|
/// <param name="Sequence"></param>
|
|
/// <param name="Type"></param>
|
|
/// <param name="keyValue"></param>
|
|
/// <param name="WorkPoint"></param>
|
|
/// <returns></returns>
|
|
public int SubmitForm(string SDNCode, string Sequence,string Type, string keyValue, string WorkPoint)
|
|
{
|
|
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 Pre = "";
|
|
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 sqls = string.Empty;
|
|
int num = 0;
|
|
string LotNo=string.Empty;
|
|
string sqlCount = $@"SELECT max(CONVERT(int,Lotno)) as count from ICSFinishedProductShipment WHERE TransCode='{SDNCode}' And Type='{Type}' And EATTRIBUTE1='{queryParam["MOCode"]}' and EATTRIBUTE5='{queryParam["CusOrders"]}' ";
|
|
var numer= SqlHelper.CmdExecuteDataTable(sqlCount)?.Rows[0]["count"].ToInt();
|
|
|
|
|
|
for (int i = 0; i < createPageCount; i++)
|
|
{
|
|
if (i + 1 == createPageCount)
|
|
{
|
|
if (minPackQty * createPageCount > thisCreateQty)
|
|
{
|
|
LOTQTY = thisCreateQty - (minPackQty * (createPageCount - 1));
|
|
}
|
|
}
|
|
if (Type == "1")
|
|
{
|
|
if (numer.IsEmpty() && i == 0)
|
|
{
|
|
LotNo = "00001";
|
|
}
|
|
else
|
|
{
|
|
LotNo = (numer + i + 1)?.ToString("D5");
|
|
}
|
|
|
|
}
|
|
else if (Type == "2")
|
|
{
|
|
if (numer.IsEmpty() && i == 0)
|
|
{
|
|
LotNo = "0001";
|
|
}
|
|
else
|
|
{
|
|
LotNo = (numer + i + 1)?.ToString("D4");
|
|
}
|
|
}
|
|
else if (Type == "3")
|
|
{
|
|
if (numer.IsEmpty() && i == 0)
|
|
{
|
|
LotNo = "001";
|
|
}
|
|
else
|
|
{
|
|
LotNo = (numer + i + 1)?.ToString("D3");
|
|
}
|
|
|
|
|
|
}
|
|
else
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
//LotNo = app.GetSerialCode(WorkPoints, "ICSFinishedProductShipment", "LotNO", Pre, num);
|
|
|
|
sql += $@"INSERT INTO [dbo].[ICSFinishedProductShipment] ([ID], [LotNo], [InvCode], [ProductDate], [ExpirationDate], [Quantity], [TransCode], [TransSequence], [ExtensionID], [Type], [PrintTimes], [LastPrintUser], [LastPrintTime], [MUSER], [MUSERName], [MTIME], [WorkPoint], [minPackQty],[EATTRIBUTE1],[EATTRIBUTE5],[EATTRIBUTE6]) VALUES (newID(), N'{LotNo}', N'{queryParam["InvCode"]}', '{queryParam["PRODUCTDATE"]}', '2099-12-31', {LOTQTY}, N'{SDNCode}', N'{Sequence}', NULL, N'{Type}', NULL, NULL, NULL, N'{MUSER}', N'{MUSERNAME}', GetDate(), N'{WorkPoints}','{minPackQty}','{queryParam["MOCode"]}','{queryParam["CusOrders"]}','{queryParam["Remark"]}');";
|
|
|
|
}
|
|
int count = SqlHelper.CmdExecuteNonQueryLi(sql);
|
|
return count;
|
|
}
|
|
/// <summary>
|
|
/// 删除送货单
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string DeleteFinishedLot(string keyValue)
|
|
{
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = "";
|
|
try
|
|
{
|
|
sql = string.Format(@" DELETE FROM dbo.ICSFinishedProductShipment WHERE ID IN ({0})", keyValue.TrimEnd(','));
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
}
|
|
}
|