Files

265 lines
15 KiB
C#
Executable File

using System;
using System.Collections.Generic;
using System.Text;
namespace Opsoft.NetCore.O3App.Repositories.WMS.APP
{
public class SUB_INSTKPICKMTL
{
//委外入库
public static string InSoureBillData(string orgID, string sourceTranType, string sourceBillNO = "", int entryID = 0)
{
string exeSql = string.Empty, whereSql = string.Empty;
if (!string.IsNullOrWhiteSpace(orgID))
{
whereSql += $" AND S2.FCOMPANYID='{orgID}'";
}
if (!string.IsNullOrWhiteSpace(sourceBillNO))
{
whereSql += $" AND S2.FBILLNO='{sourceBillNO}'";
}
if (entryID > 0)
{
whereSql += $" AND S.FENTRYID={entryID}";
}
switch (sourceTranType)
{
case "PUR_PURCHASEORDER": //采购订单
exeSql = $@"
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.FINSTOCKQTY) AS FRELATIONQTY, -- 已入数量
CONVERT(FLOAT,S.FQTY-S.FINSTOCKQTY) AS FREMINANQTY, --剩余入库数量
CONVERT(FLOAT,S.FBASEUNITQTY-S.FINSTOCKBASEQTY) AS FBASEREMINANQTY, --剩余入库数量
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,--单位名称
S5.FINTERID AS FSUPPLYID, --供应商
S5.FNUMBER AS FSUPPLYNUMBER,--供应商代码
S5.FNAME AS FSUPPLYNAME, --供应商名称
S2.FCOMPANYID
FROM T_PUR_POOrderEntry S (nolock)
LEFT JOIN T_PUR_POORDER 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_SUPPLIER S5(nolock) ON S5.FINTERID=S2.FSUPPLYID
LEFT JOIN T_BD_FLEXSITEMDETAILV S7 on S.FAUXPROPID = S7.FINTERID
WHERE S2.FSTATUS=40 AND S2.FDELETED=0 {whereSql}
";
break;
}
return exeSql;
}
/// <summary>
/// 委外退货
/// </summary>
/// <param name="orgID"></param>
/// <param name="sourceTranType"></param>
/// <param name="sourceBillNO"></param>
/// <param name="entryID"></param>
/// <returns></returns>
public static string OutSoureBillData(string orgID, string sourceTranType, string sourceBillNO = "", int entryID = 0)
{
string exeSql = string.Empty, whereSql = string.Empty;
if (!string.IsNullOrWhiteSpace(orgID))
{
whereSql += $" AND S2.FCOMPANYID='{orgID}'";
}
if (!string.IsNullOrWhiteSpace(sourceBillNO))
{
whereSql += $" AND S2.FBILLNO='{sourceBillNO}'";
}
if (entryID > 0)
{
whereSql += $" AND S.FENTRYID={entryID}";
}
switch (sourceTranType)
{
case "PUR_PURCHASEORDER": //采购订单
exeSql = $@"
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,ISNULL((
SELECT
SUM(FREALQTY)
FROM dbo.T_SUB_OUTSTKPICKMTLENTRY
WHERE FSRCDETAILID = S.FDETAILID
),0
)) AS FRELATIONQTY, --已退数量(基本单位)
CONVERT(FLOAT,S.FINSTOCKQTY) AS FREMINANQTY, --剩余可退库数量
CONVERT(FLOAT,S.FINSTOCKBASEQTY) AS FBASEREMINANQTY, --剩余可退库数量
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,--单位名称
S5.FINTERID AS FSUPPLYID, --供应商
S5.FNUMBER AS FSUPPLYNUMBER,--供应商代码
S5.FNAME AS FSUPPLYNAME, --供应商名称
S2.FCOMPANYID
FROM T_PUR_POOrderEntry S (nolock)
LEFT JOIN T_PUR_POORDER 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_SUPPLIER S5(nolock) ON S5.FINTERID=S2.FSUPPLYID
LEFT JOIN T_BD_FLEXSITEMDETAILV S7 on S.FAUXPROPID = S7.FINTERID
WHERE S2.FSTATUS=40 AND S2.FDELETED=0 {whereSql}
";
break;
case "PUR_MOBPURRETURN"://退料通知单
exeSql = $@"
SELECT S.*,
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.FMRAPPQTY) AS FAUXQTY, --总数量
CONVERT(FLOAT,S.FBASEUNITQTY) AS FBASEUNITQTY, --总数量
CONVERT(FLOAT,ISNULL((
SELECT
SUM(FBASEUNITQTY)
FROM dbo.T_SUB_OUTSTKPICKMTLENTRY
WHERE FSRCDETAILID = S.FDETAILID
),0
)) AS FBASERELATIONQTY, --已退数量(基本单位)
CONVERT(FLOAT,ISNULL((
SELECT
SUM(FREALQTY)
FROM dbo.T_SUB_OUTSTKPICKMTLENTRY
WHERE FSRCDETAILID = S.FDETAILID
),0
)) AS FRELATIONQTY, --已退数量(基本单位)
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,--单位名称
S5.FINTERID AS FSUPPLYID, --供应商
S5.FNUMBER AS FSUPPLYNUMBER,--供应商代码
S5.FNAME AS FSUPPLYNAME, --供应商名称
S2.FCOMPANYID
FROM T_PUR_MRAPPENTRY S (nolock)
LEFT JOIN T_PUR_MRAPP 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_SUPPLIER S5(nolock) ON S5.FINTERID=S2.FSUPPLYID
LEFT JOIN T_BD_FLEXSITEMDETAILV S7 on S.FAUXPROPID = S7.FINTERID
WHERE S2.FSTATUS=40 AND S2.FDELETED=0 {whereSql}
)S
";
break;
case "SUB_INSTKPICKMTL"://委外入库单
exeSql = $@"
SELECT S.*,
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.FREALQTY) AS FAUXQTY, --总数量
CONVERT(FLOAT,S.FBASEUNITQTY) AS FBASEUNITQTY, --总数量
CONVERT(FLOAT,ISNULL((
SELECT
SUM(FREALQTY)
FROM dbo.T_SUB_OUTSTKPICKMTLENTRY
WHERE FSRCDETAILID = S.FDETAILID
),0
)) AS FRELATIONQTY, --已退数量
CONVERT(FLOAT,ISNULL((
SELECT
SUM(FBASEUNITQTY)
FROM dbo.T_SUB_OUTSTKPICKMTLENTRY
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,
S4.FNUMBER AS FUNITNUMBER,--单位代码
S4.FNAME AS FUNITNAME,--单位名称
S5.FINTERID AS FSUPPLYID, --供应商
S5.FNUMBER AS FSUPPLYNUMBER,--供应商代码
S5.FNAME AS FSUPPLYNAME, --供应商名称
S2.FCOMPANYID
FROM T_SUB_INSTKPICKMTLENTRY S (nolock)
LEFT JOIN T_SUB_INSTKPICKMTL 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_SUPPLIER S5(nolock) ON S5.FINTERID=S2.FSUPPLYID
LEFT JOIN T_BD_FLEXSITEMDETAILV S7 on S.FAUXPROPID = S7.FINTERID
WHERE S2.FSTATUS=40 AND S2.FDELETED=0 {whereSql}
)S
";
break;
}
return exeSql;
}
}
}