960 lines
39 KiB
C#
960 lines
39 KiB
C#
using Kingdee.BOS.App.Data;
|
||
using Kingdee.BOS;
|
||
using Kingdee.BOS.Contracts.Report;
|
||
using Kingdee.BOS.Core.Report;
|
||
using Kingdee.BOS.Orm.DataEntity;
|
||
using Kingdee.BOS.Util;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.ComponentModel;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
using Pilot.Report.Exploitation.Common;
|
||
using Kingdee.BOS.Log;
|
||
|
||
namespace Pilot.Report.Exploitation.SpecialDetails
|
||
{
|
||
/// <summary>
|
||
/// 特价明细报表
|
||
/// </summary>
|
||
[Description("【列表插件】特价明细报表【报表】"), HotUpdate]
|
||
public class SpecialDetailsReport : SysReportBaseService
|
||
{
|
||
/// <summary>
|
||
/// 初始化
|
||
/// </summary>
|
||
public override void Initialize()
|
||
{
|
||
base.Initialize();
|
||
this.ReportProperty.ReportName = new LocaleValue("特价明细表", base.Context.UserLocale.LCID);
|
||
this.ReportProperty.IsUIDesignerColumns = false;
|
||
// 简单账表类型：普通、树形、分页
|
||
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
|
||
this.IsCreateTempTableByPlugin = true;
|
||
//
|
||
this.IsCreateTempTableByPlugin = true;
|
||
//
|
||
this.ReportProperty.IsGroupSummary = true;
|
||
//
|
||
this.ReportProperty.SimpleAllCols = false;
|
||
// 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
|
||
this.ReportProperty.PrimaryKeyFieldName = "FID";
|
||
//
|
||
this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
|
||
|
||
// 设置精度控制
|
||
var list = new List<DecimalControlField>
|
||
{
|
||
new DecimalControlField("FDECIMALS", "OrigInvoiceAmount"),
|
||
new DecimalControlField("FDECIMALS", "ExchangeRate"),
|
||
new DecimalControlField("FDECIMALS", "InvoiceAmountTHB"),
|
||
new DecimalControlField("FDECIMALS", "MonthEndExRate"),
|
||
new DecimalControlField("FDECIMALS", "RevaluationAountInTHB"),
|
||
new DecimalControlField("FDECIMALS", "UnrealizedGainLoss"),
|
||
};
|
||
this.ReportProperty.DecimalControlFieldList = list;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 报表列表头
|
||
/// </summary>
|
||
/// <param name="filter"></param>
|
||
/// <returns></returns>
|
||
public override ReportHeader GetReportHeaders(IRptParams filter)
|
||
{
|
||
base.GetReportHeaders(filter);
|
||
|
||
ReportHeader header = new ReportHeader();
|
||
header.AddChild("TableName", new LocaleValue("开发看的"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Date", new LocaleValue("日期"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Month", new LocaleValue("月份"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Client", new LocaleValue("购货单位"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("CreditType", new LocaleValue("客户信用类型"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("ContractNumber", new LocaleValue("纸质合同号"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("SizeModel", new LocaleValue("规格型号"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Quantity", new LocaleValue("数量"), SqlStorageType.SqlInt);
|
||
header.AddChild("TaxPrice", new LocaleValue("实际含税单价"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("AllAmount", new LocaleValue("价税合计"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("ProjectName", new LocaleValue("项目名称"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("SalerName", new LocaleValue("业务员"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("SaleDeptName", new LocaleValue("部门"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Supervisor", new LocaleValue("审批人"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("SpecialCauseType", new LocaleValue("特价原因分类"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("CRMSpecialCause", new LocaleValue("CRM特价原因"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("CurrentSettledUpsetJinE", new LocaleValue("当期结算价*数量（仪表）"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("F_VRYF_Amount_yrr", new LocaleValue("整单结算总价"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("CurrentSettledUpset1", new LocaleValue("当期结算价（仪表）"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("newJieSuanJia", new LocaleValue("最新结算价（仪表）"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("CurrentSettledUpset2", new LocaleValue("当期结算价（系统）"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("Difference", new LocaleValue("差额(单价-结算价）"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("TotalSpreadYiBiao", new LocaleValue("仪表总价差（终）"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("TotalSpread", new LocaleValue("系统总价差（终）"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("PaymentMethods", new LocaleValue("付款方式"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("ProjectPhase", new LocaleValue("项目阶段"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("OrderAllAmount", new LocaleValue("订单总额"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("NotShipmentsAmount", new LocaleValue("未发货金额"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("ExpireUnpaidAmount", new LocaleValue("到期未付金额"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("ProductsCode", new LocaleValue("产品长代码"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Currency", new LocaleValue("币别"), SqlStorageType.Sqlvarchar);
|
||
|
||
foreach (var item in header.GetChilds())
|
||
{
|
||
item.Width = 160;
|
||
}
|
||
return header;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 显示报表数据
|
||
/// </summary>
|
||
/// <param name="filter"></param>
|
||
/// <param name="tableName"></param>
|
||
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
|
||
{
|
||
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
|
||
|
||
// 客户 FClient
|
||
var fClient = (customFilter["FClient"] as DynamicObject)?["Id"]?.ToString();
|
||
// 产品
|
||
var fChanPin = (customFilter["FChanPin"] as DynamicObject)?["Id"]?.ToString();
|
||
// 项目 FProject
|
||
var fProject = (customFilter["FProject"] as DynamicObject)?["Id"]?.ToString();
|
||
// 销售员
|
||
var fSalesman = (customFilter["FSalesman"] as DynamicObject)?["Id"]?.ToString();
|
||
// 日期
|
||
string fDate = customFilter["FDate"]?.ToString();
|
||
// 日期
|
||
string fEndDate = customFilter["FEndDate"]?.ToString();
|
||
// 纸质合同号
|
||
string fPaperContract = customFilter["FPaperContract"]?.ToString();
|
||
// 组织
|
||
var organizationList = (customFilter["FZuZhi"] as DynamicObjectCollection)?.Select(n => (n["FZuZhi"] as DynamicObject)["ID"].ToString()).ToList();
|
||
|
||
var organization = organizationList == null ? "" : (string.Join(",", organizationList));
|
||
|
||
base.BuilderReportSqlAndTempTable(filter, tableName);
|
||
string wheresql = " AND 1=1 ";
|
||
|
||
if (!string.IsNullOrEmpty(organization))
|
||
{
|
||
wheresql += string.Format(@" AND A.FSaleOrgId in ({0})", organization);
|
||
}
|
||
if (!string.IsNullOrEmpty(fClient))
|
||
{
|
||
wheresql += string.Format(@" AND A.FCUSTID in ({0})", fClient);
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(fProject))
|
||
{
|
||
wheresql += string.Format(@" AND A.F_PROJECTNAME in ({0})", fProject);
|
||
}
|
||
if (!string.IsNullOrEmpty(fSalesman))
|
||
{
|
||
wheresql += string.Format(@" AND A.FSALERID in ({0})", fSalesman);
|
||
}
|
||
if (!string.IsNullOrEmpty(fDate))
|
||
{
|
||
wheresql += string.Format(@" AND FORMAT(A.FApproveDate,'yyyy-MM-dd') >= '{0}'", fDate);
|
||
}
|
||
if (!string.IsNullOrEmpty(fEndDate))
|
||
{
|
||
wheresql += string.Format(@"AND FORMAT(A.FApproveDate,'yyyy-MM-dd') <= '{0}'", fEndDate);
|
||
}
|
||
if (!string.IsNullOrEmpty(fPaperContract))
|
||
{
|
||
wheresql += string.Format(@" AND a.F_CONTRACTNUMBER like '%{0}%'", fPaperContract);
|
||
}
|
||
|
||
|
||
//过滤公司
|
||
string sql = string.Format($@"/*dialect*/
|
||
SELECT A.FID AS FID
|
||
INTO #PaiChuGongSi
|
||
FROM T_SAL_ORDER A
|
||
INNER JOIN T_BD_CUSTOMER CC ON CC.FCUSTID= A.FCUSTID
|
||
INNER JOIN T_BD_CUSTOMER_L C
|
||
ON C.FCUSTID = A.FCUSTID
|
||
AND C.FLOCALEID = 2052
|
||
LEFT JOIN V_BD_SALESMAN_L L
|
||
ON L.fid = A.FSALERID
|
||
LEFT JOIN T_PerformanceSharing M
|
||
ON M.FSOURCEBILLNO = A.FBILLNO
|
||
AND M.FDOCUMENTSTATUS = 'C'
|
||
LEFT JOIN T_PerformanceSharingEntry N
|
||
ON N.FID = M.FID
|
||
LEFT JOIN V_BD_SALESMAN_L O
|
||
ON O.fid = N.FSALEID
|
||
LEFT JOIN ERPTOHR.HYHRV3.dbo.v_erp_empinfo ACO
|
||
ON ACO.名称 = O.FNAME
|
||
LEFT JOIN ERPTOHR.HYHRV3.dbo.v_erp_empinfo ACL
|
||
ON ACL.名称 = L.FNAME
|
||
WHERE A.FSALEORGID = 100302
|
||
AND ACO.区域 = '碳索智控'
|
||
OR ACL.区域 = '碳索智控'
|
||
OR C.FNAME IN ( '珠海派诺科技股份有限公司', '派诺科技（香港）有限公司', '武汉派诺科技发展有限公司', '珠海华夏云联技术有限公司', '广东碳索智控科技有限公司', '武汉派诺科技发展有限公司', '珠海兴诺能源技术有限公司','广东诺瓦数能科技有限公司' )
|
||
OR L.FNAME = '兴诺总' OR CC.F_correlation = '1'
|
||
");
|
||
|
||
//仪表类
|
||
sql += string.Format($@"/*dialect*/
|
||
SELECT A.FBILLNO AS 'AFBILLNO',
|
||
'YB' F_ORDERTYPE,
|
||
CONVERT(NVARCHAR, A.FApproveDate, 23) AS 'Date', --日期
|
||
MONTH(A.FApproveDate) AS 'Month', --月份
|
||
A.F_CONTRACTNUMBER AS 'ContractNumber', --纸质合同号
|
||
a.FSALEORGID ,A.FID,B.FENTRYID ,'#YiBiaoHeTong' AS TableName
|
||
INTO #YiBiaoHeTong
|
||
FROM T_SAL_ORDERENTRY B --销售订单明细表
|
||
JOIN T_SAL_ORDER A --销售订单表
|
||
ON A.FID = B.FID
|
||
LEFT JOIN T_SAL_ORDERENTRY_F B2 --销售订单明细_F表
|
||
ON B.FENTRYID = B2.FENTRYID
|
||
left join T_SAL_ORDERFIN fin on a.fid=fin.fid
|
||
LEFT JOIN
|
||
(
|
||
SELECT FNUMBER,
|
||
FMASTERID
|
||
FROM T_BAS_ASSISTANTDATAENTRY
|
||
WHERE FID = '670cc977263353'
|
||
AND FDOCUMENTSTATUS = 'C'
|
||
AND FFORBIDSTATUS = 'A'
|
||
) D --合同类型#（辅助资料表）
|
||
ON A.F_ORDERCATEGORY = D.FMASTERID
|
||
WHERE
|
||
(D.FNUMBER IN ( 'P', 'E', 'C','B' ))
|
||
AND (B.F_SPECIALOFFER = '1' OR B2.FTaxPrice*fin.FExchangeRate < B.F_SettlementP OR (B2.FTaxPrice*fin.FExchangeRate < B.F_SHOUGONGJIESUANJIA AND B.F_SettlementP=0 ) )
|
||
AND B.F_YKQC_CHECKBOX_QTR != '1'
|
||
AND A.FDOCUMENTSTATUS = 'C'
|
||
AND B.FQTY >= 0
|
||
AND A.FMANUALCLOSE = '0'
|
||
AND A.FMANUALCLOSE <> '1'
|
||
AND B.FMANUALROWCLOSE <> '1'
|
||
AND A.F_CONTRACTNUMBER NOT LIKE 'Z%'
|
||
AND A.FID NOT IN
|
||
(
|
||
SELECT FID FROM #PaiChuGongSi
|
||
) {wheresql} ;
|
||
");
|
||
|
||
//系统合同 20250903 郭靖要求把S类合同改成系统合同特价
|
||
sql += string.Format($@"/*dialect*/
|
||
SELECT A.FBILLNO AS 'AFBILLNO',
|
||
'XT' F_ORDERTYPE,
|
||
CONVERT(NVARCHAR, A.FApproveDate, 23) AS 'Date', --日期
|
||
MONTH(A.FApproveDate) AS 'Month', --月份
|
||
A.F_CONTRACTNUMBER AS 'ContractNumber', --纸质合同号
|
||
a.FSALEORGID ,A.FID,B.FENTRYID ,'#XiTongHeTong' AS TableName
|
||
INTO #XiTongHeTong
|
||
FROM T_SAL_ORDERENTRY B --销售订单明细表
|
||
JOIN T_SAL_ORDER A --销售订单表
|
||
ON A.FID = B.FID
|
||
LEFT JOIN T_SAL_ORDERENTRY_F B2 --销售订单明细_F表
|
||
ON B.FENTRYID = B2.FENTRYID
|
||
JOIN T_SAL_ORDERFIN C --销售订单_财务表
|
||
ON A.FID = C.FID
|
||
LEFT JOIN
|
||
(
|
||
SELECT FNUMBER,
|
||
FMASTERID
|
||
FROM T_BAS_ASSISTANTDATAENTRY
|
||
WHERE FID = '670cc977263353'
|
||
AND FDOCUMENTSTATUS = 'C'
|
||
AND FFORBIDSTATUS = 'A'
|
||
) D --合同类型#（辅助资料表）
|
||
ON A.F_ORDERCATEGORY = D.FMASTERID
|
||
|
||
--ES、W、SX、CW、D、CY
|
||
WHERE D.FNUMBER NOT IN ( 'P', 'E', 'C','CS','CW','B' )
|
||
AND (A.F_SpecialContracts = '是' OR a.F_GROSSMARGIN1='<35%' OR a.F_VRYF_Amount_yrr > c.FBillAllAmount_LC OR a.F_ManuallySpecialPrices ='是' )
|
||
AND (a.F_ManuallySpecialPrices !='否' or a.F_CancelSpecialContracts ='' )
|
||
AND B.FQTY >= 0
|
||
AND A.FDOCUMENTSTATUS = 'C'
|
||
AND A.FBUSINESSTYPE = 'NORMAL'
|
||
|
||
AND A.FMANUALCLOSE = '0'
|
||
AND A.F_CONTRACTNUMBER NOT LIKE 'Z%'
|
||
AND A.FMANUALCLOSE <> '1'
|
||
AND B.FMANUALROWCLOSE <> '1'
|
||
AND A.FID NOT IN
|
||
(
|
||
SELECT FID FROM #PaiChuGongSi
|
||
) {wheresql};
|
||
|
||
INSERT INTO #XiTongHeTong (AFBILLNO,F_ORDERTYPE,Date,Month,ContractNumber,FSALEORGID,FID,FENTRYID,TableName)
|
||
(SELECT o.FBILLNO,o.F_ORDERTYPE, CONVERT(NVARCHAR, o.FApproveDate, 23) AS 'Date', --日期
|
||
MONTH(o.FApproveDate) AS 'Month', --月份
|
||
o.F_CONTRACTNUMBER AS 'ContractNumber', --纸质合同号
|
||
o.FSALEORGID ,o.FID,b.FENTRYID ,'#XiTongHeTong' AS TableName FROM
|
||
T_SAL_ORDERENTRY B --销售订单明细表
|
||
LEFT JOIN
|
||
dbo.T_SAL_ORDER o ON b.fid=o.FID
|
||
LEFT JOIN T_SAL_ORDERENTRY_F B2 --销售订单明细_F表
|
||
ON B.FENTRYID = B2.FENTRYID
|
||
JOIN T_SAL_ORDERFIN C --销售订单_财务表
|
||
ON o.FID = C.FID
|
||
WHERE EXISTS (
|
||
SELECT 1
|
||
FROM #XiTongHeTong x
|
||
WHERE o.F_CONTRACTNUMBER LIKE x.ContractNumber + '%' AND o.F_CONTRACTNUMBER <> x.ContractNumber ) AND b.FQTY>0 ) ;
|
||
");
|
||
|
||
|
||
//兴诺明细的充电桩系统合同CS、CW
|
||
sql += $@"/*dialect*/
|
||
SELECT A.FBILLNO AS 'AFBILLNO',
|
||
'CDZ' F_ORDERTYPE,
|
||
CONVERT(NVARCHAR, A.FApproveDate, 23) AS 'Date', --日期
|
||
MONTH(A.FApproveDate) AS 'Month', --月份
|
||
A.F_CONTRACTNUMBER AS 'ContractNumber', --纸质合同号
|
||
a.FSALEORGID,A.FID,B.FENTRYID ,'#XNChongDianZhuangHeTong' AS TableName
|
||
INTO #XNChongDianZhuangHeTong
|
||
FROM T_SAL_ORDERENTRY B --销售订单明细表
|
||
JOIN T_SAL_ORDER A --销售订单表
|
||
ON A.FID = B.FID
|
||
LEFT JOIN T_SAL_ORDERENTRY_F B2 --销售订单明细_F表
|
||
ON B.FENTRYID = B2.FENTRYID
|
||
LEFT JOIN T_SAL_ORDERFIN fin ON a.fid=fin.fid
|
||
LEFT JOIN
|
||
(
|
||
SELECT FNUMBER,
|
||
FMASTERID
|
||
FROM T_BAS_ASSISTANTDATAENTRY
|
||
WHERE FID = '670cc977263353'
|
||
AND FDOCUMENTSTATUS = 'C'
|
||
AND FFORBIDSTATUS = 'A'
|
||
) D --合同类型#（辅助资料表）
|
||
ON A.F_ORDERCATEGORY = D.FMASTERID
|
||
LEFT JOIN
|
||
(
|
||
SELECT B.FNAME,
|
||
B.FSPECIFICATION,
|
||
A.*
|
||
FROM T_BD_MATERIAL A
|
||
JOIN T_BD_MATERIAL_L B
|
||
ON A.FMATERIALID = B.FMATERIALID
|
||
WHERE B.FLOCALEID = 2052
|
||
AND A.FDOCUMENTSTATUS = 'C'
|
||
AND A.FFORBIDSTATUS = 'A'
|
||
) G --物料表
|
||
ON G.FMATERIALID = B.FMATERIALID
|
||
WHERE
|
||
(D.FNUMBER IN ('CS','CW' ))
|
||
AND ( B.F_SPECIALOFFER = '1' or ABS(B2.FAllAmount_LC)< ABS(B.F_SettlementP*B.FQTY) OR (B2.FTaxPrice*fin.FExchangeRate < B.F_SHOUGONGJIESUANJIA AND B.F_SettlementP=0 ) )
|
||
AND B.F_YKQC_CHECKBOX_QTR != '1'
|
||
AND A.FDOCUMENTSTATUS = 'C'
|
||
AND B.FQTY >= 0
|
||
AND A.FMANUALCLOSE = '0'
|
||
AND A.FMANUALCLOSE <> '1'
|
||
AND B.FMANUALROWCLOSE <> '1'
|
||
AND A.F_CONTRACTNUMBER NOT LIKE 'Z%'
|
||
AND A.FID NOT IN
|
||
(
|
||
SELECT FID FROM #PaiChuGongSi
|
||
)
|
||
AND G.FNUMBER LIKE 'V%' {wheresql} ;
|
||
";
|
||
|
||
//派诺明细的充电桩系统合同CS、CW
|
||
sql += $@"/*dialect*/
|
||
SELECT A.FBILLNO AS 'AFBILLNO',
|
||
A.F_ORDERTYPE,
|
||
CONVERT(NVARCHAR, A.FApproveDate, 23) AS 'Date', --日期
|
||
MONTH(A.FApproveDate) AS 'Month', --月份
|
||
A.F_CONTRACTNUMBER AS 'ContractNumber', --纸质合同号
|
||
a.FSALEORGID ,A.FID,B.FENTRYID ,'#PLChongDianZhuangHeTong' AS TableName
|
||
INTO #PLChongDianZhuangHeTong
|
||
FROM T_SAL_ORDERENTRY B --销售订单明细表
|
||
JOIN T_SAL_ORDER A --销售订单表
|
||
ON A.FID = B.FID
|
||
LEFT JOIN T_SAL_ORDERENTRY_F B2 --销售订单明细_F表
|
||
ON B.FENTRYID = B2.FENTRYID
|
||
|
||
LEFT JOIN
|
||
(
|
||
SELECT FNUMBER,
|
||
FMASTERID
|
||
FROM T_BAS_ASSISTANTDATAENTRY
|
||
WHERE FID = '670cc977263353'
|
||
AND FDOCUMENTSTATUS = 'C'
|
||
AND FFORBIDSTATUS = 'A'
|
||
) D --合同类型#（辅助资料表）
|
||
ON A.F_ORDERCATEGORY = D.FMASTERID
|
||
|
||
|
||
LEFT JOIN
|
||
(
|
||
SELECT B.FNAME,
|
||
B.FSPECIFICATION,
|
||
A.*
|
||
FROM T_BD_MATERIAL A
|
||
JOIN T_BD_MATERIAL_L B
|
||
ON A.FMATERIALID = B.FMATERIALID
|
||
WHERE B.FLOCALEID = 2052
|
||
AND A.FDOCUMENTSTATUS = 'C'
|
||
AND A.FFORBIDSTATUS = 'A'
|
||
) G --物料表
|
||
ON G.FMATERIALID = B.FMATERIALID
|
||
WHERE
|
||
(D.FNUMBER IN ('CS','CW' ))
|
||
--AND B.F_SPECIALOFFER = '1'
|
||
--AND B.F_YKQC_CHECKBOX_QTR != '1'
|
||
AND (A.F_SpecialContracts = '是' OR a.F_GROSSMARGIN1='<35%') and (a.F_CancelSpecialContracts !='是' or a.F_CancelSpecialContracts is null or a.F_ManuallySpecialPrices='是' )
|
||
AND (a.F_ManuallySpecialPrices !='否' or a.F_CancelSpecialContracts ='' )
|
||
AND A.FDOCUMENTSTATUS = 'C'
|
||
AND B.FQTY >= 0
|
||
AND A.FMANUALCLOSE = '0'
|
||
AND A.FMANUALCLOSE <> '1'
|
||
AND B.FMANUALROWCLOSE <> '1'
|
||
AND A.F_CONTRACTNUMBER NOT LIKE 'Z%'
|
||
AND A.FID NOT IN
|
||
(
|
||
SELECT FID FROM #PaiChuGongSi
|
||
)
|
||
AND G.FNUMBER not LIKE 'V%' {wheresql};
|
||
|
||
";
|
||
|
||
//几个临时表合并
|
||
sql += string.Format(@"/*dialect*/
|
||
/*dialect*/
|
||
SELECT *
|
||
INTO #TempTable3
|
||
FROM #YiBiaoHeTong
|
||
UNION ALL
|
||
SELECT *
|
||
FROM #XiTongHeTong
|
||
UNION ALL
|
||
SELECT *
|
||
FROM #XNChongDianZhuangHeTong
|
||
UNION ALL
|
||
SELECT *
|
||
FROM #PLChongDianZhuangHeTong ;
|
||
|
||
");
|
||
|
||
if (!string.IsNullOrEmpty(fChanPin))
|
||
{
|
||
wheresql += string.Format(@" AND G.FMATeRIALID in ({0})", fChanPin);
|
||
}
|
||
//组合其他字段的大表
|
||
sql += $@"/*dialect*/
|
||
SELECT AAA.TableName, A.FBILLNO AS 'AFBILLNO',
|
||
AAA.F_ORDERTYPE,
|
||
CONVERT(NVARCHAR, A.FApproveDate, 23) AS 'Date', --日期
|
||
MONTH(A.FApproveDate) AS 'Month', --月份
|
||
E.FNAME AS 'Client', --购货单位
|
||
F.FNAME AS 'Currency', --币别
|
||
ISNULL(E.FDATAVALUE, '') AS 'CreditType', --客户信用类型
|
||
A.F_CONTRACTNUMBER AS 'ContractNumber', --纸质合同号
|
||
ISNULL(G.FSPECIFICATION, '') AS 'SizeModel', --规格型号
|
||
B.FQTY - ISNULL(B3.FRETNOTICEQTY, 0) AS 'Quantity', --数量
|
||
B2.FTAXPRICE*C.FExchangeRate AS 'TaxPrice', --实际含税单价
|
||
B2.FALLAMOUNT_LC - ISNULL(ABS(B3.FALLAMOUNT_LC), 0) AS 'AllAmount', --价税合计
|
||
ISNULL(H.FNAME, '') AS 'ProjectName', --项目名称
|
||
ISNULL(H2.FNAME, '') AS 'SalerName', --业务员
|
||
ISNULL(H3.FNAME, '') AS 'SaleDeptName', --部门
|
||
ISNULL(H4.FNAME, '') AS 'Supervisor', --审批人
|
||
H5.FDATAVALUE AS 'SpecialCauseType', --特价原因分类
|
||
A.FCRMSPECIALPRICINGREASON AS 'CRMSpecialCause', --CRM特价原因
|
||
|
||
CASE
|
||
WHEN (AAA.TableName IN ( '#YiBiaoHeTong','#XNChongDianZhuangHeTong')) THEN
|
||
(CASE when B.F_SETTLEMENTP=0 THEN g.F_JSJ ELSE B.F_SETTLEMENTP END) *B.FQTY
|
||
ELSE
|
||
NULL
|
||
END AS 'CurrentSettledUpsetJinE', --当期结算价*数量（仪表）
|
||
|
||
CASE
|
||
WHEN ROW_NUMBER() OVER (PARTITION BY A.FID ORDER BY a.FID,AAA.TableName,b.FENTRYID) = 1 THEN
|
||
CAST(ISNULL(A.F_VRYF_Amount_yrr, 0) AS FLOAT)
|
||
ELSE
|
||
NULL
|
||
END AS 'F_VRYF_Amount_yrr', -- 整单结算总价#
|
||
|
||
|
||
CASE
|
||
WHEN (AAA.TableName IN ( '#YiBiaoHeTong','#XNChongDianZhuangHeTong')) THEN
|
||
B.F_SETTLEMENTP
|
||
ELSE
|
||
NULL
|
||
END AS 'CurrentSettledUpset1', --当期结算价（仪表）
|
||
|
||
g.F_JSJ AS newJieSuanJia, --最新结算价
|
||
|
||
CASE
|
||
WHEN (AAA.TableName IN ('#XiTongHeTong','#PLChongDianZhuangHeTong'))
|
||
AND ROW_NUMBER() OVER (PARTITION BY A.FID,AAA.TableName ORDER BY a.FID,AAA.TableName,b.FENTRYID) = 1 THEN
|
||
CAST(ISNULL(A.F_VRYF_AMOUNT_YRR, 0) AS FLOAT)
|
||
ELSE
|
||
NULL
|
||
END AS 'CurrentSettledUpset2', --当期结算价（系统）
|
||
CASE
|
||
WHEN (AAA.TableName IN ( '#YiBiaoHeTong','#XNChongDianZhuangHeTong')) THEN (B2.FTAXPRICE - (CASE when B.F_SETTLEMENTP=0 THEN g.F_JSJ ELSE B.F_SETTLEMENTP END) )
|
||
ELSE NULL END AS 'Difference', --差额（单价-底价）
|
||
CASE
|
||
WHEN (AAA.TableName IN ( '#YiBiaoHeTong','#XNChongDianZhuangHeTong')) THEN (B2.FTAXPRICE - (CASE when B.F_SETTLEMENTP=0 THEN g.F_JSJ ELSE B.F_SETTLEMENTP END) )*B.FQTY
|
||
ELSE NULL END AS 'TotalSpreadYiBiao', --仪表总价差（终）
|
||
CASE
|
||
WHEN (AAA.TableName IN ('#XiTongHeTong','#PLChongDianZhuangHeTong'))
|
||
AND ROW_NUMBER() OVER (PARTITION BY A.FID,AAA.TableName ORDER BY a.FID,AAA.TableName,b.FENTRYID) = 1 THEN
|
||
C.FBillAllAmount_LC - CAST(ISNULL(A.F_VRYF_Amount_yrr, 0) AS FLOAT)
|
||
|
||
ELSE
|
||
NULL
|
||
END AS 'TotalSpread', --系统总价差（终）
|
||
C2.FNAME AS 'PaymentMethods', --付款方式
|
||
CASE
|
||
WHEN A.F_ORDERTYPE = 'YB' THEN
|
||
'仪表合同' --（仪表）
|
||
WHEN A.F_ORDERTYPE = 'XT' THEN --（系统）
|
||
(CASE
|
||
WHEN ISNULL(A.F_XMYSSJ, '') != '' THEN
|
||
CONVERT(NVARCHAR, A.F_XMYSSJ, 23)
|
||
WHEN C.FBILLALLAMOUNT_LC - ISNULL(ABS(B3.FBillAllAmount_LC), 0) - ISNULL(H6.FALLAMOUNT_LC, 0) <= 0 THEN
|
||
'发货完成'
|
||
WHEN C.FBILLALLAMOUNT_LC - ISNULL(ABS(B3.FBillAllAmount_LC), 0) - ISNULL(H6.FALLAMOUNT_LC, 0) = C.FBILLALLAMOUNT_LC
|
||
- ISNULL(
|
||
ABS(B3.FBillAllAmount_LC),
|
||
0
|
||
) THEN
|
||
'未发货'
|
||
ELSE
|
||
'部分发货'
|
||
END
|
||
)
|
||
ELSE
|
||
''
|
||
END AS 'ProjectPhase', --项目阶段
|
||
CASE
|
||
WHEN ROW_NUMBER() OVER (PARTITION BY A.F_CONTRACTNUMBER ORDER BY a.FID,AAA.TableName,b.FENTRYID) = 1 THEN
|
||
C.FBILLALLAMOUNT_LC - ISNULL(ABS(B3.FBillAllAmount_LC), 0)
|
||
ELSE
|
||
NULL
|
||
END AS 'OrderAllAmount', --订单总额
|
||
CASE
|
||
WHEN ROW_NUMBER() OVER (PARTITION BY A.F_CONTRACTNUMBER ORDER BY a.FID,AAA.TableName,b.FENTRYID) = 1 THEN
|
||
C.FBILLALLAMOUNT_LC - ISNULL(H6.FALLAMOUNT_LC, 0)
|
||
ELSE
|
||
NULL
|
||
END AS 'NotShipmentsAmount', --未发货金额
|
||
|
||
CASE
|
||
WHEN ROW_NUMBER() OVER (PARTITION BY A.F_CONTRACTNUMBER ORDER BY a.FID,AAA.TableName,b.FENTRYID) = 1 THEN
|
||
(SELECT SUM([bbb].[到期金额]*BBB.汇率) FROM dbo.ZRP_YingShouMingXi_yuyubo bbb WHERE a.FID=bbb.销售单主键ID )
|
||
ELSE
|
||
NULL
|
||
END
|
||
AS 'ExpireUnpaidAmount', --到期未付金额
|
||
G.FNUMBER AS 'ProductsCode' --产品长代码
|
||
,aaa.FSALEORGID,AAA.FENTRYID,aaa.FID,B.F_K3SEQ
|
||
INTO #tempDaBiao
|
||
FROM #TempTable3 AAA
|
||
INNER JOIN T_SAL_ORDERENTRY B ON AAA.FENTRYID=B.FENTRYID
|
||
INNER JOIN T_SAL_ORDER A --销售订单表
|
||
ON A.FID = B.FID
|
||
LEFT JOIN T_SAL_ORDERENTRY_F B2 --销售订单明细_F表
|
||
ON B.FENTRYID = B2.FENTRYID
|
||
LEFT JOIN
|
||
(
|
||
SELECT Aq.FALLAMOUNT_LC AS FBillAllAmount_LC,
|
||
Aq.*
|
||
FROM
|
||
(
|
||
SELECT W2.FSID,
|
||
W2.FSBILLID,
|
||
STRING_AGG(CONVERT(NVARCHAR, W1.FDATE, 23), ',') AS 'FDate',
|
||
SUM(W1.FPRICEUNITQTY) AS 'FRETNOTICEQTY',
|
||
SUM(W1.FALLAMOUNT_LC) AS 'FALLAMOUNT_LC'
|
||
FROM
|
||
(
|
||
SELECT q1.FDATE,
|
||
q3.FSID,
|
||
q2.FALLAMOUNT_LC,
|
||
q2.FPRICEUNITQTY
|
||
FROM T_SAL_RETURNNOTICE q1
|
||
JOIN T_SAL_RETURNNOTICEENTRY_F q2
|
||
ON q1.FID = q2.FID
|
||
JOIN T_SAL_RETURNNOTICEENTRY_LK q3
|
||
ON q2.FENTRYID = q3.FENTRYID
|
||
WHERE q1.FDOCUMENTSTATUS = 'C'
|
||
AND q1.FCANCELSTATUS = 'A'
|
||
AND q3.FSTABLENAME = 'T_SAL_ORDERENTRY'
|
||
) W1
|
||
JOIN T_SAL_ORDERENTRY_LK W2
|
||
ON W1.FSID = W2.FENTRYID
|
||
GROUP BY W2.FSID,
|
||
W2.FSBILLID
|
||
) Aq
|
||
) B3 --销售订单-表关联表（销售退货）
|
||
ON B.FENTRYID = B3.FSID
|
||
JOIN T_SAL_ORDERFIN C --销售订单_财务表
|
||
ON A.FID = C.FID
|
||
LEFT JOIN
|
||
(
|
||
SELECT w.FID,
|
||
STUFF(
|
||
(
|
||
SELECT ', ' + X2.FNAME + '--' + CONVERT(NVARCHAR(50), CONVERT(INT, w2.F_DAY)) + '天--'
|
||
+ CONVERT(NVARCHAR(50), CONVERT(DECIMAL(18, 2), w2.FRECADVANCERATE)) + '%'
|
||
FROM T_SAL_ORDERPLAN w2 WITH (NOLOCK)
|
||
LEFT JOIN T_BD_RECCONDITION_L X2
|
||
ON X2.FID = w2.F_RECCONDITIONID
|
||
WHERE w2.FID = w.FID
|
||
FOR XML PATH('')
|
||
), 1, 2, ''
|
||
) AS FNAME
|
||
FROM T_SAL_ORDERPLAN w WITH (NOLOCK)
|
||
LEFT JOIN T_BD_RECCONDITION_L X
|
||
ON X.FID = w.F_RECCONDITIONID
|
||
GROUP BY w.FID
|
||
) C2 ON C2.FID=A.FID --销售订单_收款计划
|
||
LEFT JOIN
|
||
(
|
||
SELECT FNUMBER,
|
||
FMASTERID
|
||
FROM T_BAS_ASSISTANTDATAENTRY
|
||
WHERE FID = '670cc977263353'
|
||
AND FDOCUMENTSTATUS = 'C'
|
||
AND FFORBIDSTATUS = 'A'
|
||
) D --合同类型#（辅助资料表）
|
||
ON A.F_ORDERCATEGORY = D.FMASTERID
|
||
LEFT JOIN
|
||
(
|
||
SELECT A2.FDATAVALUE,
|
||
A1.*
|
||
FROM
|
||
(
|
||
SELECT q2.FNAME,
|
||
q1.*
|
||
FROM T_BD_CUSTOMER q1
|
||
JOIN T_BD_CUSTOMER_L q2
|
||
ON q1.FCUSTID = q2.FCUSTID
|
||
) A1
|
||
LEFT JOIN
|
||
(
|
||
SELECT A.FMASTERID,
|
||
B.FDATAVALUE
|
||
FROM T_BAS_ASSISTANTDATAENTRY A
|
||
JOIN T_BAS_ASSISTANTDATAENTRY_L B
|
||
ON A.FENTRYID = B.FENTRYID
|
||
WHERE A.FID = '673af6092e2877'
|
||
AND A.FDOCUMENTSTATUS = 'C'
|
||
AND A.FFORBIDSTATUS = 'A'
|
||
) A2
|
||
ON A1.F_CREDITCLASSIFICATION = A2.FMASTERID
|
||
) E --客户_L表
|
||
ON A.FCUSTID = E.FCUSTID
|
||
LEFT JOIN
|
||
(SELECT * FROM T_BD_CURRENCY_L WHERE FLOCALEID = 2052) F --币别表
|
||
ON C.FSETTLECURRID = F.FCURRENCYID
|
||
LEFT JOIN
|
||
(
|
||
SELECT B.FNAME,
|
||
B.FSPECIFICATION,
|
||
A.*
|
||
FROM T_BD_MATERIAL A
|
||
JOIN T_BD_MATERIAL_L B
|
||
ON A.FMATERIALID = B.FMATERIALID
|
||
WHERE B.FLOCALEID = 2052
|
||
AND A.FDOCUMENTSTATUS = 'C'
|
||
AND A.FFORBIDSTATUS = 'A'
|
||
) G --物料表
|
||
ON G.FMATERIALID = B.FMATERIALID
|
||
LEFT JOIN
|
||
(
|
||
SELECT q2.FNAME,
|
||
q1.*
|
||
FROM T_BAS_PREBDONE q1
|
||
JOIN T_BAS_PREBDONE_L q2
|
||
ON q1.FID = q2.FID
|
||
WHERE q2.FLocaleID = 2052
|
||
AND q1.FDOCUMENTSTATUS = 'C'
|
||
AND q1.FFORBIDSTATUS = 'A'
|
||
) H --工程项目表（预置基础资料1）
|
||
ON H.FID = A.F_PROJECTNAME
|
||
LEFT JOIN
|
||
(
|
||
SELECT q2.FNAME,
|
||
q1.*
|
||
FROM V_BD_SALESMAN q1
|
||
LEFT JOIN V_BD_SALESMAN_L q2
|
||
ON q1.fid = q2.fid
|
||
WHERE q2.FLOCALEID = 2052
|
||
AND q1.FDOCUMENTSTATUS = 'C'
|
||
AND q1.FFORBIDSTATUS = 'A'
|
||
AND q1.FFORBIDDENSTATUS = '0'
|
||
) H2 --销售员表
|
||
ON A.FSALERID = H2.fid
|
||
LEFT JOIN
|
||
(
|
||
SELECT q2.FNAME,
|
||
q1.*
|
||
FROM T_BD_DEPARTMENT q1
|
||
JOIN T_BD_DEPARTMENT_L q2
|
||
ON q1.FDEPTID = q2.FDEPTID
|
||
WHERE q2.FLOCALEID = 2052
|
||
AND q1.FDOCUMENTSTATUS = 'C'
|
||
AND q1.FFORBIDSTATUS = 'A'
|
||
) H3 --销售部门表
|
||
ON A.FSALEDEPTID = H3.FDEPTID
|
||
LEFT JOIN
|
||
(
|
||
SELECT q2.FNAME,
|
||
q1.*
|
||
FROM T_HR_EMPINFO q1
|
||
JOIN T_HR_EMPINFO_L q2
|
||
ON q1.FID = q2.FID
|
||
WHERE q2.FLOCALEID = 2052
|
||
AND q1.FDOCUMENTSTATUS = 'C'
|
||
AND q1.FFORBIDSTATUS = 'A'
|
||
) H4 --主管#（员工表）
|
||
ON A.F_VRYF_BASE_FG2 = H4.FID
|
||
LEFT JOIN
|
||
(
|
||
SELECT A.FNUMBER,
|
||
A.FMASTERID,
|
||
B.FDATAVALUE
|
||
FROM T_BAS_ASSISTANTDATAENTRY A
|
||
JOIN T_BAS_ASSISTANTDATAENTRY_L B
|
||
ON A.FENTRYID = B.FENTRYID
|
||
WHERE A.FID = '674f073de0bc25'
|
||
AND A.FDOCUMENTSTATUS = 'C'
|
||
AND A.FFORBIDSTATUS = 'A'
|
||
) H5
|
||
ON H5.FMASTERID = A.FSPECIALRESON
|
||
LEFT JOIN
|
||
(
|
||
SELECT W2.FSBILLID,
|
||
SUM(W1.FALLAMOUNT_LC) AS 'FALLAMOUNT_LC'
|
||
FROM
|
||
(
|
||
SELECT q2.FALLAMOUNT_LC,
|
||
q2.FENTRYID
|
||
FROM T_SAL_DELIVERYNOTICE q1
|
||
JOIN T_SAL_DELIVERYNOTICEENTRY_F q2
|
||
ON q1.FID = q2.FID
|
||
WHERE q1.FDOCUMENTSTATUS = 'C'
|
||
AND q1.FCANCELSTATUS = 'A'
|
||
) W1
|
||
JOIN T_SAL_DELIVERYNOTICEENTRY_LK W2
|
||
ON W1.FENTRYID = W2.FENTRYID
|
||
WHERE W2.FSTABLENAME = 'T_SAL_ORDERENTRY'
|
||
GROUP BY W2.FSBILLID
|
||
) H6 --发货通知单表
|
||
ON B.FID = H6.FSBILLID
|
||
LEFT JOIN
|
||
(
|
||
SELECT W2.FSID,
|
||
W1.*
|
||
FROM
|
||
(
|
||
SELECT B.FALLAMOUNTFOR,
|
||
B.FENTRYID
|
||
FROM T_AR_RECEIVABLE A
|
||
JOIN T_AR_RECEIVABLEENTRY B
|
||
ON A.FID = B.FID
|
||
WHERE A.FDOCUMENTSTATUS = 'C'
|
||
AND B.FORDERNUMBER != ''
|
||
AND A.FENDDATE <= GETDATE()
|
||
) W1
|
||
JOIN T_AR_RECEIVABLEENTRY_LK W2
|
||
ON W1.FENTRYID = W2.FENTRYID
|
||
WHERE W2.FSTABLENAME = 'T_SAL_ORDERENTRY'
|
||
) H7
|
||
ON B.FENTRYID = H7.FSID {wheresql} ;
|
||
|
||
|
||
|
||
UPDATE #tempDaBiao SET NotShipmentsAmount=NotShipmentsAmount-ISNULL(k3.含税单价*(isnull(K3.销售出库数量,0)+isnull(K3.其他出库数量,0))*1.000,0)
|
||
FROM dbo.oldk3seorder0701 k3
|
||
WHERE F_K3SEQ=k3.行号 AND #tempDaBiao.ContractNumber=k3.合同号;
|
||
|
||
SELECT DISTINCT a.fid,b.F_SettlementAmount,jiashui
|
||
INTO #PLChongDianZhuangHeTong_yibiao
|
||
FROM #PLChongDianZhuangHeTong a
|
||
INNER JOIN (SELECT a.FID, SUM(B.F_SettlementAmount) AS F_SettlementAmount,SUM(B2.FTAXPRICE*C.FExchangeRate*b.FQTY) AS jiashui
|
||
FROM T_SAL_ORDERENTRY B --销售订单明细表
|
||
LEFT JOIN T_SAL_ORDERENTRY_F B2 --销售订单明细_F表
|
||
ON B.FENTRYID = B2.FENTRYID
|
||
JOIN T_SAL_ORDER A --销售订单表
|
||
ON A.FID = B.FID
|
||
JOIN T_SAL_ORDERFIN C --销售订单_财务表
|
||
ON A.FID = C.FID
|
||
LEFT JOIN
|
||
(
|
||
SELECT B.FNAME,
|
||
B.FSPECIFICATION,
|
||
A.*
|
||
FROM T_BD_MATERIAL A
|
||
JOIN T_BD_MATERIAL_L B
|
||
ON A.FMATERIALID = B.FMATERIALID
|
||
WHERE B.FLOCALEID = 2052
|
||
AND A.FDOCUMENTSTATUS = 'C'
|
||
AND A.FFORBIDSTATUS = 'A'
|
||
) G --物料表
|
||
ON G.FMATERIALID = B.FMATERIALID
|
||
WHERE G.FNUMBER LIKE 'V%'
|
||
GROUP BY a.fid
|
||
) b
|
||
ON a.fid=b.fid
|
||
|
||
|
||
|
||
UPDATE #tempDaBiao SET TotalSpread= TotalSpread-b.jiashui +b.F_SettlementAmount
|
||
,CurrentSettledUpset2=CurrentSettledUpset2-b.F_SettlementAmount
|
||
FROM (SELECT fid,F_SettlementAmount ,jiashui FROM #PLChongDianZhuangHeTong_yibiao ) b
|
||
where TableName IN ('#PLChongDianZhuangHeTong') AND #tempDaBiao.FID=b.FID;
|
||
|
||
DELETE #tempDaBiao
|
||
WHERE TableName IN ('#PLChongDianZhuangHeTong') AND FID NOT IN (
|
||
SELECT a.fid FROM #tempDaBiao b INNER JOIN dbo.T_SAL_ORDER a
|
||
ON a.FID=b.FID
|
||
WHERE
|
||
(A.F_SpecialContracts = '是' OR a.F_GROSSMARGIN1='<35%' OR b.TotalSpread < 0 ) and (a.F_CancelSpecialContracts !='是' or a.F_CancelSpecialContracts is null )
|
||
);
|
||
|
||
";
|
||
|
||
|
||
//sql += $@"/*dialect*/
|
||
//Select
|
||
//*,
|
||
//ROW_NUMBER() OVER (ORDER BY FID DESC,FENTRYID) AS FIDENTITYID
|
||
//INTO {tableName}
|
||
//From
|
||
//(SELECT * FROM #tempDaBiao) A ";
|
||
|
||
sql += string.Format($@"/*dialect*/
|
||
Select
|
||
*,
|
||
ROW_NUMBER() OVER (ORDER BY Date desc,FID DESC ,[TableName],FENTRYID) AS FIDENTITYID
|
||
INTO {tableName}
|
||
From
|
||
(SELECT [TableName]
|
||
,[AFBILLNO]
|
||
,[F_ORDERTYPE]
|
||
,[Date]
|
||
,[Month]
|
||
,[Client]
|
||
,[Currency]
|
||
,[CreditType]
|
||
,[ContractNumber]
|
||
,[SizeModel]
|
||
,[Quantity]
|
||
,[TaxPrice]
|
||
,[AllAmount]
|
||
,[ProjectName]
|
||
,[SalerName]
|
||
,[SaleDeptName]
|
||
,[Supervisor]
|
||
,[SpecialCauseType]
|
||
,[CRMSpecialCause]
|
||
,[CurrentSettledUpset1]
|
||
,newjiesuanjia
|
||
,CurrentSettledUpsetJinE
|
||
,F_VRYF_Amount_yrr
|
||
,[CurrentSettledUpset2]
|
||
,[Difference]
|
||
,[TotalSpreadYiBiao]
|
||
,[TotalSpread]
|
||
,[PaymentMethods]
|
||
,[ProjectPhase]
|
||
,[OrderAllAmount]
|
||
,[NotShipmentsAmount]
|
||
,[ExpireUnpaidAmount]
|
||
,[ProductsCode]
|
||
,[FSALEORGID]
|
||
,[FENTRYID]
|
||
,[FID],1 Sort FROM #tempDaBiao
|
||
where Quantity<>0
|
||
UNION ALL
|
||
SELECT '',
|
||
'',
|
||
'',
|
||
'合计：',
|
||
null,
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
SUM(t1.Quantity),--数量
|
||
SUM(t1.TaxPrice),
|
||
SUM(t1.AllAmount),
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
SUM(t1.CurrentSettledUpset1),
|
||
SUM(newjiesuanjia),
|
||
SUM(CurrentSettledUpsetJinE)
|
||
,SUM(F_VRYF_Amount_yrr),
|
||
SUM(t1.CurrentSettledUpset2),
|
||
SUM(t1.Difference),
|
||
SUM(t1.TotalSpreadYiBiao),
|
||
SUM(t1.TotalSpread),
|
||
'',
|
||
'',
|
||
SUM(t1.OrderAllAmount),
|
||
SUM(t1.NotShipmentsAmount),
|
||
SUM(t1.ExpireUnpaidAmount),
|
||
'',0,0,999999,
|
||
0 Sort
|
||
FROM #tempDaBiao t1
|
||
where Quantity<>0
|
||
) t1
|
||
|
||
order by Sort desc,Date desc ,fid desc ,FIDENTITYID
|
||
|
||
", tableName);
|
||
Logger.Error("特价明细报表", sql, new Exception());
|
||
DBUtils.Execute(this.Context, sql);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取前端组件转换过滤条件
|
||
/// </summary>
|
||
/// <param name="filter"></param>
|
||
/// <returns></returns>
|
||
public override ReportTitles GetReportTitles(IRptParams filter)
|
||
{
|
||
ReportTitles reportTitles = new ReportTitles();
|
||
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
|
||
|
||
if (customFilter != null)
|
||
{
|
||
// 下单时间
|
||
string fDate = customFilter["FDate"]?.ToString();
|
||
// 下单时间
|
||
string fEndDate = customFilter["FEndDate"]?.ToString();
|
||
// 纸质合同号
|
||
string fPaperContract = customFilter["FPaperContract"]?.ToString();
|
||
// 组织
|
||
var organizationList = (customFilter["FZuZhi"] as DynamicObjectCollection)?.Select(n => (n["FZuZhi"] as DynamicObject)["Name"].ToString()).ToList();
|
||
// 客户 FClient
|
||
var fClient = (customFilter["FClient"] as DynamicObject)?["Name"]?.ToString();
|
||
// 产品
|
||
var fChanPin = (customFilter["FChanPin"] as DynamicObject)?["Name"]?.ToString();
|
||
// 项目 FProject
|
||
var fProject = (customFilter["FProject"] as DynamicObject)?["Name"]?.ToString();
|
||
// 销售员
|
||
var fSalesman = (customFilter["FSalesman"] as DynamicObject)?["Name"]?.ToString();
|
||
|
||
//这里是报表页面的标识
|
||
reportTitles.AddTitle("FPaperContract", fPaperContract == null ? "" : fPaperContract);
|
||
reportTitles.AddTitle("FClient", fClient == null ? "" : fClient);
|
||
reportTitles.AddTitle("FChanPin", fChanPin == null ? "" : fChanPin);
|
||
reportTitles.AddTitle("FProject", fProject == null ? "" : fProject);
|
||
reportTitles.AddTitle("FSalesman", fSalesman == null ? "" : fSalesman);
|
||
reportTitles.AddTitle("FDate", fDate == null ? "" : Convert.ToDateTime(fDate).ToString("yyyy-MM-dd"));
|
||
reportTitles.AddTitle("FEndDate", fEndDate == null ? "" : Convert.ToDateTime(fEndDate).ToString("yyyy-MM-dd"));
|
||
reportTitles.AddTitle("FZuZhi", organizationList == null ? "" : string.Join(",", organizationList));
|
||
}
|
||
return reportTitles;
|
||
}
|
||
}
|
||
}
|