Files

326 lines
20 KiB
C#
Executable File

using System;
using System.Collections.Generic;
using System.Text;
namespace Opsoft.NetCore.O3App.Repositories.WMS.APP
{
/// <summary>
/// 调拨管理
/// </summary>
public class STK_TRANSFERMANAGE
{
public static string InSoureBillData(string orgID, string sourceTranType, string sourceBillNO = "", int entryID = 0)
{
string exeSql = string.Empty, whereSql = string.Empty;
if (!string.IsNullOrWhiteSpace(orgID))
{
switch (sourceTranType)
{
case "SAL_DELIVERYNOTICE": //发货通知单
whereSql += $" AND S2.FCOMPANYID='{orgID}'";
break;
case "PRD_MO": //生产任务单
whereSql += $" AND S1.FCOMPANYID='{orgID}'";
break;
case "SUB_SUBREQORDER": //委外订单
whereSql += $" AND S2.FCOMPANYID='{orgID}'";
break;
case "STK_TRANSFERAPPLY": //调拨申请单
//whereSql += $" AND S2.FCOMPANYID='{orgID}'";
break;
default:
whereSql += $" AND S2.FCOMPANYID='{orgID}'";
break;
}
}
if (!string.IsNullOrWhiteSpace(sourceBillNO))
{
switch (sourceTranType)
{
case "SAL_DELIVERYNOTICE": //发货通知单
whereSql += $" AND S2.FBILLNO='{sourceBillNO}'";
break;
case "PRD_MO": //生产任务单
whereSql += $" AND S1.FBILLNO='{sourceBillNO}'";
break;
case "SUB_SUBREQORDER": //委外订单
whereSql += $" AND S2.FBILLNO='{sourceBillNO}'";
break;
case "STK_TRANSFERAPPLY": //调拨申请单
whereSql += $" AND S2.FBILLNO='{sourceBillNO}'";
break;
default:
whereSql += $" AND S2.FBILLNO='{sourceBillNO}'";
break;
}
}
if (entryID > 0)
{
switch (sourceTranType)
{
case "SAL_DELIVERYNOTICE": //发货通知单
whereSql += $" AND S.FENTRYID={entryID}";
break;
case "PRD_MO": //生产任务单
whereSql += $" AND S.FENTRYID={entryID}";
break;
case "SUB_SUBREQORDER": //委外订单
whereSql += $" AND S1.FSUBREQENTRYSEQ={entryID}";
break;
case "STK_TRANSFERAPPLY": //调拨申请单
whereSql += $" AND S.FENTRYID={entryID}";
break;
default:
whereSql += $" AND S.FENTRYID={entryID}";
break;
}
}
switch (sourceTranType)
{
case "SAL_DELIVERYNOTICE": //发货通知单
exeSql = $@"
SELECT
S.*,
CONVERT(FLOAT,S.FAUXQTY-S.FRELATIONQTY) AS FREMINANQTY, --剩余可调拨数量
CONVERT(FLOAT,S.FBASEUNITQTY-S.FBASERELATIONQTY) AS FBASEREMINANQTY --剩余可调拨数量
FROM
(
SELECT
S.FROWTYPE,
CONVERT(FLOAT,S.FTAXPRICE) AS FTAXPRICE,
CONVERT(FLOAT,S.FTAXRATE) AS FTAXRATE,
CONVERT(FLOAT,S.FDISCOUNTRATE) AS FDISCOUNTRATE,
S2.FBILLTYPEID,
S2.FSETTLECURRID AS FCURRENCYID,
CONVERT(FLOAT,S2.FEXCHANGERATE) AS FEXCHANGERATE,
S2.FDATE,
S2.FBILLNO AS FSRCBILLNO,
S2.FINTERID AS FSRCID,
S.FENTRYID AS FSRCENTRYID,
S.FDETAILID AS FSRCDETAILID,
S.FORDERINTERID,
S.FORDERBILLNO,
S.FORDERDETAILID,
S.FORDERENTRYID,
CONVERT(FLOAT,S.FQTY) AS FAUXQTY, --数量
CONVERT(FLOAT,S.FBASEUNITQTY) AS FBASEUNITQTY, --总基本单位数量
CONVERT(FLOAT,ISNULL((SELECT SUM(FQTY) FROM T_STK_STKTRANSFERINENTRY WHERE FSRCDETAILID=S.FDETAILID),0)) AS FRELATIONQTY, --累计调拨数量
CONVERT(FLOAT,ISNULL((SELECT SUM(FBASEUNITQTY) FROM T_STK_STKTRANSFERINENTRY WHERE FSRCDETAILID=S.FDETAILID),0)) AS FBASERELATIONQTY, --累计调拨基本数量
S.FLOT,--批号
S.FAUXPROPID, --辅助属性
S7.FNAME as FAUXPROPNAME,
S.FMATERIALID,--物料内码
S3.FNUMBER AS FITEMNUMBER,--物料代码
S3.FNAME AS FITEMNAME,--物料名称
S3.FSPECIFICATION, --规格型号
S3.FERPCLSID,--物料属性
S3.FISKFPERIOD,--是否启用保质期
S3.FKFPERIOD,--保质期(天)
S3.FKFUNIT,--保质期单位
S3.FISBATCHMANAGE, --是否批次管理
S.FUNITID,
S.FBASEUNITID,
S4.FNUMBER AS FUNITNUMBER,--单位代码
S4.FNAME AS FUNITNAME,--单位名称
S5.FINTERID AS FCUSTID, --客户内码
S5.FNUMBER AS FCUSTNUMBER,--客户代码
S5.FNAME AS FCUSTNAME, --客户名称
S6.FINTERID AS FDEPTID, --部门内码
S6.FNUMBER AS FDEPTNUMBER,--部门代码
S6.FNAME AS FDEPTNAME, --部门名称
S2.FCOMPANYID
FROM T_SAL_DELIVERYNOTICEENTRY S (nolock)
LEFT JOIN T_SAL_DELIVERYNOTICE S2 (nolock) ON S2.FINTERID=S.FINTERID
LEFT JOIN T_BD_MATERIAL S3(nolock) ON S.FMATERIALID =S3.FINTERID
LEFT JOIN T_BD_UNIT S4 ON S.FUNITID = S4.FINTERID
LEFT JOIN T_BD_CUSTOMER S5(nolock) ON S5.FINTERID=S2.FCUSTOMERID
LEFT JOIN T_BD_DEPARTMENT S6(nolock) ON S6.FINTERID=S2.FSALEDEPTID
LEFT JOIN T_BD_FLEXSITEMDETAILV S7 on S.FAUXPROPID = S7.FINTERID
WHERE S2.FSTATUS=40 AND S2.FDELETED=0 {whereSql}
)S
";
break;
case "PRD_MO": //生产任务单
exeSql = $@"
SELECT
S.*,
CONVERT(FLOAT,S.FAUXQTY-S.FRELATIONQTY) AS FREMINANQTY, --剩余可调拨数量
CONVERT(FLOAT,S.FBASEUNITQTY-S.FBASERELATIONQTY) AS FBASEREMINANQTY --剩余可调拨数量
FROM
(
SELECT
S1.FBILLTYPE AS FBILLTYPEID,
S1.FCOMPANYID,--组织内码
S1.FINTERID AS FMOID,--工单内码
S1.FBILLNO AS FMOBILLNO,--工单编号
S.FENTRYID AS FMOENTRYID,--工单行号
S.FDETAILID AS FMODETIALID,--工单行内码
S1.FINTERID AS FORDERINTERID,--订单内码
S1.FBILLNO AS FORDERBILLNO,--订单编号
S.FENTRYID AS FORDERENTRYID,--订单行号
S.FDETAILID AS FORDERDETAILID,--订单行内码
S2.FINTERID AS FPPBOMID,--用料清单内码
S2.FBILLNO AS FPPBOMBILLNO,--用料清单编号
S5.FDETAILID AS FPPBOMDETAILID,--用料清单行内码
S5.FENTRYID AS FPPBOMENTRYID,--用料清单行号
S2.FINTERID AS FSRCID,--源单内码
S2.FBILLNO AS FSRCBILLNO,--源单编号
S5.FENTRYID AS FSRCENTRYID,--源单行号
S5.FDETAILID AS FSRCDETAILID,--源单行内码
CONVERT(FLOAT,S5.FMUSTQTY) AS FAUXQTY,--总数量
CONVERT(FLOAT,S5.FBASEQTY) AS FBASEUNITQTY, --总基本单位数量
CONVERT(FLOAT,ISNULL((SELECT SUM(FQTY) FROM T_STK_STKTRANSFERINENTRY WHERE FSRCDETAILID=S5.FDETAILID),0)) AS FRELATIONQTY, --累计调拨数量
CONVERT(FLOAT,ISNULL((SELECT SUM(FBASEUNITQTY) FROM T_STK_STKTRANSFERINENTRY WHERE FSRCDETAILID=S5.FDETAILID),0)) AS FBASERELATIONQTY, --累计调拨基本数量
S10.FNUMBER AS FPRODUCTNUMBER,--产品代码
S10.FNAME AS FPRODUCTNAME,--产品名称
S10.FSPECIFICATION AS FPRODUCTSPEC,--产品规格型号
S5.FMATERIALID,--物料内码
S6.FNUMBER AS FITEMNUMBER,--物料编号
S6.FNAME AS FITEMNAME,--物料名称
S6.FSPECIFICATION,--规格型号
S6.FISKFPERIOD,--是否启用保质期
S6.FKFPERIOD,--保质天数
S5.FAUXPROPID,
S9.FNAME as FAUXPROPNAME,
S5.FCOMMONUNITID AS FUNITID,--单位内码
S7.FNUMBER AS FUNITNUMBER,--单位编号
S7.FNAME AS FUNITNAME,--单位名称
S2.FWORKSHOPID AS FDEPTID,--部门内码
S8.FNUMBER AS FDEPTNUMBER,--部门编号
S8.FNAME AS FDEPTNAME --部门名称
FROM T_PRD_MOENTRY S
LEFT JOIN T_PRD_MO S1 ON S.FINTERID=S1.FINTERID
LEFT JOIN T_PRD_PPBOM S2 ON S2.FMOENTRYID=S.FDETAILID
LEFT JOIN T_PRD_PPBOMENTRY S5 ON S5.FINTERID= S2.FINTERID
LEFT JOIN T_BD_MATERIAL S6 ON S6.FINTERID=S5.FMATERIALID
LEFT JOIN T_BD_UNIT S7 ON S7.FINTERID=S5.FCOMMONUNITID
LEFT JOIN T_BD_DEPARTMENT S8 ON S8.FINTERID= S2.FWORKSHOPID
LEFT JOIN T_BD_FLEXSITEMDETAILV S9 on S5.FAUXPROPID = S9.FINTERID
LEFT JOIN T_BD_MATERIAL S10 ON S10.FINTERID=S.FMATERIALID
WHERE S1.FSTATUS=40 AND S1.FDELETED=0 {whereSql}
)S
";
break;
case "SUB_SUBREQORDER": //委外订单
exeSql = $@"
SELECT
S.*,
CONVERT(FLOAT,S.FAUXQTY-S.FRELATIONQTY) AS FREMINANQTY, --剩余可调拨数量
CONVERT(FLOAT,S.FBASEUNITQTY-S.FBASERELATIONQTY) AS FBASEREMINANQTY --剩余可调拨数量
FROM
(
SELECT
S2.FBILLTYPEID,
S2.FDATE,
S1.FBILLNO AS FSRCBILLNO,
S1.FINTERID AS FSRCID,
S.FENTRYID AS FSRCENTRYID,
S.FDETAILID AS FSRCDETAILID,
S1.FSUBREQID AS FMOID,--工单内码
S1.FSUBREQBILLNO AS FMOBILLNO,--工单编号
S1.FSUBREQENTRYSEQ AS FMOENTRYID,--工单行号
S1.FSUBREQENTRYID AS FMODETIALID,--工单行内码
S1.FINTERID AS FPPBOMID,--用料清单内码
S1.FBILLNO AS FPPBOMBILLNO,--用料清单编号
S.FDETAILID AS FPPBOMDETAILID,--用料清单行内码
S.FENTRYID AS FPPBOMENTRYID,--用料清单行号
S1.FSUBREQID AS FORDERINTERID,-- 委外订单内码
S1.FSUBREQBILLNO AS FORDERBILLNO,-- 委外订单编号
S1.FSUBREQENTRYID AS FORDERDETAILID,-- 委外订单行内码
S1.FSUBREQENTRYSEQ AS FORDERENTRYID,-- 委外订单行号
CONVERT(FLOAT,S.FMUSTQTY) AS FAUXQTY, --数量
CONVERT(FLOAT,S.FBASEMUSTQTY) AS FBASEUNITQTY, --总基本单位数量
CONVERT(FLOAT,ISNULL((SELECT SUM(FQTY) FROM T_STK_STKTRANSFERINENTRY WHERE FSRCDETAILID=S.FDETAILID),0)) AS FRELATIONQTY, --累计调拨数量
CONVERT(FLOAT,ISNULL((SELECT SUM(FBASEUNITQTY) FROM T_STK_STKTRANSFERINENTRY WHERE FSRCDETAILID=S.FDETAILID),0)) AS FBASERELATIONQTY, --累计调拨基本数量
S10.FNUMBER AS FPRODUCTNUMBER,--产品代码
S10.FNAME AS FPRODUCTNAME,--产品名称
S10.FSPECIFICATION AS FPRODUCTSPEC,--产品规格型号
S.FLOT,--批号
S.FAUXPROPID, --辅助属性
S7.FNAME as FAUXPROPNAME,
S.FMATERIALID,--物料内码
S3.FNUMBER AS FITEMNUMBER,--物料代码
S3.FNAME AS FITEMNAME,--物料名称
S3.FSPECIFICATION, --规格型号
S3.FERPCLSID,--物料属性
S3.FISKFPERIOD,--是否启用保质期
S3.FKFPERIOD,--保质期(天)
S3.FKFUNIT,--保质期单位
S3.FISBATCHMANAGE, --是否批次管理
S.FUNITID,
S4.FNUMBER AS FUNITNUMBER,--单位代码
S4.FNAME AS FUNITNAME,--单位名称
S6.FINTERID AS FDEPTID,--部门内码
S6.FNUMBER AS FDEPTNUMBER,--部门代码
S6.FNAME AS FDEPTNAME, --部门名称
S2.FCOMPANYID
FROM T_SUB_PPBOMENTRY S (nolock)
LEFT JOIN T_SUB_PPBOM S1 (nolock) ON S1.FINTERID=S.FINTERID
LEFT JOIN T_SUB_REQORDER S2 (nolock) ON S2.FINTERID=S1.FSUBREQID
LEFT JOIN T_BD_MATERIAL S3(nolock) ON S.FMATERIALID =S3.FINTERID
LEFT JOIN T_BD_UNIT S4 ON S.FUNITID = S4.FINTERID
LEFT JOIN T_SAL_ORDER S5(nolock) ON S1.FSALEORDERID=S5.FINTERID
LEFT JOIN T_BD_DEPARTMENT S6(nolock) ON S5.FSALEDEPTID = S6.FINTERID
LEFT JOIN T_BD_FLEXSITEMDETAILV S7 on S.FAUXPROPID = S7.FINTERID
LEFT JOIN T_BD_MATERIAL S10 ON S10.FINTERID=S1.FMATERIALID
WHERE S2.FSTATUS=40 AND S2.FDELETED=0 {whereSql}
)S
";
break;
case "STK_TRANSFERAPPLY": //调拨申请单
exeSql = $@"
SELECT
S.*,
CONVERT(FLOAT,S.FAUXQTY-S.FRELATIONQTY) AS FREMINANQTY, --剩余可调拨数量
CONVERT(FLOAT,S.FBASEUNITQTY-S.FBASERELATIONQTY) AS FBASEREMINANQTY --剩余可调拨数量
FROM
(
SELECT
S2.FBILLTYPEID,
S2.FDATE,
S2.FBILLNO AS FSRCBILLNO,
S2.FINTERID AS FSRCID,
S.FENTRYID AS FSRCENTRYID,
S.FDETAILID AS FSRCDETAILID,
CONVERT(FLOAT,S.FQTY) AS FAUXQTY, --数量
CONVERT(FLOAT,S.FBASEQTY) AS FBASEUNITQTY, --总基本单位数量
CONVERT(FLOAT,ISNULL((SELECT SUM(FQTY) FROM T_STK_STKTRANSFERINENTRY WHERE FSRCDETAILID=S.FDETAILID and FSRCFORMID='STK_TRANSFERAPPLY' ),0)) AS FRELATIONQTY, --累计调拨数量
CONVERT(FLOAT,ISNULL((SELECT SUM(FBASEUNITQTY) FROM T_STK_STKTRANSFERINENTRY WHERE FSRCDETAILID=S.FDETAILID and FSRCFORMID='STK_TRANSFERAPPLY'),0)) AS FBASERELATIONQTY, --累计调拨基本数量
S.FLOT,--批号
S.FAUXPROPID, --辅助属性
S7.FNAME as FAUXPROPNAME,
S.FMATERIALID,--物料内码
S3.FNUMBER AS FITEMNUMBER,--物料代码
S3.FNAME AS FITEMNAME,--物料名称
S3.FSPECIFICATION, --规格型号
S3.FERPCLSID,--物料属性
S3.FISKFPERIOD,--是否启用保质期
S3.FKFPERIOD,--保质期(天)
S3.FKFUNIT,--保质期单位
S3.FISBATCHMANAGE, --是否批次管理
S.FUNITID,
case when S2.FTRANSTYPE='OverOrgTransfer' then 1 else 0 end FTRANSTYPE,
S.FBASEUNITID,
S4.FNUMBER AS FUNITNUMBER,--单位代码
S4.FNAME AS FUNITNAME,--单位名称
S2.FCOMPANYID,
S.FSTOCKORGID, -- 调出组织
S.FSTOCKORGINID -- 调入组织
FROM T_STK_STKTRANSFERAPPENTRY S (nolock)
LEFT JOIN T_STK_STKTRANSFERAPP S2 (nolock) ON S2.FINTERID=S.FINTERID
LEFT JOIN T_BD_MATERIAL S3(nolock) ON S.FMATERIALID =S3.FINTERID
LEFT JOIN T_BD_UNIT S4 ON S.FUNITID = S4.FINTERID
LEFT JOIN T_BD_FLEXSITEMDETAILV S7 on S.FAUXPROPID = S7.FINTERID
WHERE S2.FSTATUS=40 AND S2.FDELETED=0 {whereSql}
)S
";
break;
}
return exeSql;
}
}
}