317 lines
17 KiB
C#
317 lines
17 KiB
C#
using Kingdee.BOS.App.Data;
|
||
using Kingdee.BOS.Contracts.Report;
|
||
using Kingdee.BOS.Core.Report;
|
||
using Kingdee.BOS.Orm.Cvp;
|
||
using Kingdee.BOS.Orm.DataEntity;
|
||
using Kingdee.BOS.Util;
|
||
using Kingdee.BOS;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.ComponentModel;
|
||
using System.Data;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
|
||
namespace TenderItem.Induct
|
||
{
|
||
/// <summary>
|
||
/// 【列表插件】投标项目数据简单报表
|
||
/// </summary>
|
||
[Description("【列表插件】投标项目数据报表"), HotUpdate]
|
||
public class TenderProjectReportPlugin : 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;
|
||
}
|
||
|
||
public override ReportHeader GetReportHeaders(IRptParams filter)
|
||
{
|
||
base.GetReportHeaders(filter);
|
||
ReportHeader header = new ReportHeader();
|
||
if (this.Context.UserLocale.LCID == 2052)
|
||
{
|
||
header.AddChild("Year", new LocaleValue("年份"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("TenderNumber", new LocaleValue("招标编号"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("TenderMain", new LocaleValue("标书主编号"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Tender", new LocaleValue("标书编号"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("ProjectName", new LocaleValue("项目名称"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("TenderingUnit", new LocaleValue("招标单位"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("BidProjectType", new LocaleValue("中标项目分类"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("ProvincesHead", new LocaleValue("省区负责人"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Authorizer", new LocaleValue("授权人"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Region", new LocaleValue("大区"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Provinces", new LocaleValue("省区"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("BidProductsType", new LocaleValue("中标产品分类"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("BidProducts", new LocaleValue("中标产品"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("BidPackageNumber", new LocaleValue("中标包号"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("WinningBid", new LocaleValue("中标额（万元）"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("FrameVolume", new LocaleValue("框架标预估体量"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("WinningBidSubtotal", new LocaleValue("中标额小计"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("BidsDate", new LocaleValue("开标时间"), SqlStorageType.SqlDatetime);
|
||
header.AddChild("InstallationIllustrate", new LocaleValue("安装说明"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Remark", new LocaleValue("备注"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("ContractOriginalReturns", new LocaleValue("合同原件返回额"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("AmountOrder", new LocaleValue("下单金额"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("AmountOriginalOrder", new LocaleValue("下单无原件金额"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("ContractAmountOrder", new LocaleValue("合同返回未下单金额"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("BidderNotAgency", new LocaleValue("中标未签约且未下单（万元）"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("Sales", new LocaleValue("销售额"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("InvoicedAmount", new LocaleValue("已开票额"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("UnbilledAmount", new LocaleValue("未开票额"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("ProjectSpecialIllustrate", new LocaleValue("项目特殊情况说明"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Cost", new LocaleValue("成本"), SqlStorageType.SqlDecimal);
|
||
}
|
||
else
|
||
{
|
||
header.AddChild("Year", new LocaleValue("Year"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("TenderNumber", new LocaleValue("TenderNumber"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("TenderMain", new LocaleValue("TenderMain"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Tender", new LocaleValue("Tender"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("ProjectName", new LocaleValue("ProjectName"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("TenderingUnit", new LocaleValue("TenderingUnit"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("BidProjectType", new LocaleValue("BidProjectType"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("ProvincesHead", new LocaleValue("ProvincesHead"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Authorizer", new LocaleValue("Authorizer"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Region", new LocaleValue("Region"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Provinces", new LocaleValue("Provinces"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("BidProductsType", new LocaleValue("BidProductsType"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("BidProducts", new LocaleValue("BidProducts"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("BidPackageNumber", new LocaleValue("BidPackageNumber"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("WinningBid", new LocaleValue("WinningBid"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("FrameVolume", new LocaleValue("FrameVolume"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("WinningBidSubtotal", new LocaleValue("WinningBidSubtotal"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("BidsDate", new LocaleValue("InstallationIllustrate"), SqlStorageType.SqlDatetime);
|
||
header.AddChild("InstallationIllustrate", new LocaleValue("InstallationIllustrate"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Remark", new LocaleValue("Remark"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("ContractOriginalReturns", new LocaleValue("ContractOriginalReturns"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("AmountOrder", new LocaleValue("AmountOrder"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("AmountOriginalOrder", new LocaleValue("AmountOriginalOrder"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("ContractAmountOrder", new LocaleValue("ContractAmountOrder"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("BidderNotAgency", new LocaleValue("BidderNotAgency"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("Sales", new LocaleValue("Sales"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("InvoicedAmount", new LocaleValue("InvoicedAmount"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("UnbilledAmount", new LocaleValue("UnbilledAmount"), SqlStorageType.SqlDecimal);
|
||
header.AddChild("ProjectSpecialIllustrate", new LocaleValue("ProjectSpecialIllustrate"), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("Cost", new LocaleValue("Cost"), SqlStorageType.SqlDecimal);
|
||
}
|
||
foreach (var item in header.GetChilds())
|
||
{
|
||
item.Width = 150;
|
||
}
|
||
return header;
|
||
}
|
||
|
||
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
|
||
{
|
||
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
|
||
|
||
// 年份
|
||
string fDate = customFilter["FDate"]?.ToString();
|
||
// 标书编号
|
||
string fTender = customFilter["FTender"]?.ToString();
|
||
|
||
base.BuilderReportSqlAndTempTable(filter, tableName);
|
||
|
||
string sql = string.Format(@"/*dialect*/
|
||
Select
|
||
A.FYEAR as [Year],
|
||
A.FTenderNumber as TenderNumber,
|
||
A.FTenderMain as TenderMain,
|
||
A.FTender as Tender,
|
||
A.FProjectName as ProjectName,
|
||
A.FTenderingUnit as TenderingUnit,
|
||
A.FBidProjectType as BidProjectType,
|
||
A.FProvincesHead as ProvincesHead,
|
||
A.FAuthorizer as Authorizer,
|
||
A.FRegion as Region,
|
||
A.FProvinces as Provinces,
|
||
A.FBidProductsType as BidProductsType,
|
||
A.FBidProducts as BidProducts,
|
||
A.FBidPackageNumber as BidPackageNumber,
|
||
A.FWinningBid as WinningBid,
|
||
A.FFrameVolume as FrameVolume,
|
||
A.FWinningBidSubtotal as WinningBidSubtotal,
|
||
A.FBidsDate as BidsDate,
|
||
A.FInstallationIllustrate as InstallationIllustrate,
|
||
A.FRemark as Remark,
|
||
CASE WHEN ISNULL(E.F_CYG_Paperreturndate,'')='' THEN 0
|
||
ELSE (Select FCONTRACTAMOUNT_LC From T_CRM_CONTRACTFIN WHere FId = E.FID)
|
||
END as ContractOriginalReturns, --合同原件返回额
|
||
ISNULL((Select
|
||
Sum(q.FAllAmount_LC)
|
||
From T_SAL_ORDERENTRY_F q
|
||
JOIN
|
||
T_SAL_ORDERENTRY w
|
||
on q.FID = w.FID
|
||
Where q.FID = B.FID
|
||
AND w.F_CYG_CheckBalance1 != 1),0) as AmountOrder, --下单金额
|
||
CASE WHEN ISNULL(E.F_CYG_Paperreturndate,'')='' THEN ISNULL((Select FCONTRACTAMOUNT_LC From T_CRM_CONTRACTFIN WHere FId = E.FID),0)
|
||
ELSE 0
|
||
END as AmountOriginalOrder, --下单无原件金额
|
||
((CASE WHEN ISNULL(E.F_CYG_PAPERRETURNDATE,'')='' THEN 0
|
||
ELSE (Select FCONTRACTAMOUNT_LC From T_CRM_CONTRACTFIN WHere FId = E.FID)
|
||
END)-(ISNULL((Select
|
||
Sum(q.FAllAmount_LC)
|
||
From T_SAL_ORDERENTRY_F q
|
||
JOIN
|
||
T_SAL_ORDERENTRY w
|
||
on q.FID = w.FID
|
||
Where q.FID = B.FID
|
||
AND w.F_CYG_CheckBalance1 != 1),0))) as ContractAmountOrder, --合同返回未下单金额
|
||
((A.FWinningBidSubtotal)-(CASE WHEN ISNULL(E.F_CYG_Paperreturndate,'')='' THEN 0
|
||
ELSE (Select FCONTRACTAMOUNT_LC From T_CRM_CONTRACTFIN WHere FId = E.FID)
|
||
END)-(CASE WHEN ISNULL(E.F_CYG_Paperreturndate,'')='' THEN ISNULL((Select FCONTRACTAMOUNT_LC From T_CRM_CONTRACTFIN WHere FId = E.FID),0)
|
||
ELSE 0
|
||
END)) as BidderNotAgency, --中标未签约且未下单（万元）
|
||
CASE WHEN D.FID in (Select Ae.FID From
|
||
(Select WW.FID,SUM(EE.FMustqty) as 'FMustqty',Sum(EE.FRealQty) as 'FRealQty'
|
||
From
|
||
(Select W.FSID,E.FID
|
||
From T_AR_RECEIVABLEENTRY_LK W
|
||
--Where FSTABLENAME = 'T_SAL_RETURNSTOCKENTRY'
|
||
JOIN
|
||
T_AR_RECEIVABLEENTRY E
|
||
on W.FENTRYID = E.FENTRYID AND W.FSTABLENAME = 'T_SAL_RETURNSTOCKENTRY') WW
|
||
JOIN
|
||
T_SAL_RETURNSTOCKENTRY EE
|
||
on WW.FSID = EE.FENTRYID
|
||
Group by WW.FID) Ae
|
||
Where Ae.FMustqty=Ae.FRealQty)
|
||
THEN 0
|
||
ELSE
|
||
ISNULL((Select SUM(FALLAMOUNT) From T_AR_RECEIVABLEENTRY Where FID = D.FID),0)
|
||
END as Sales,--销售额
|
||
ISNull((Select SUM(FOPENAMOUNTFOR) From T_AR_RECEIVABLEENTRY Where FID = D.FID),0) as InvoicedAmount, --已开票额
|
||
((CASE WHEN D.FID in (Select Ae.FID From
|
||
(Select WW.FID,SUM(EE.FMustqty) as 'FMustqty',Sum(EE.FRealQty) as 'FRealQty'
|
||
From
|
||
(Select W.FSID,E.FID
|
||
From T_AR_RECEIVABLEENTRY_LK W
|
||
--Where FSTABLENAME = 'T_SAL_RETURNSTOCKENTRY'
|
||
JOIN
|
||
T_AR_RECEIVABLEENTRY E
|
||
on W.FENTRYID = E.FENTRYID AND W.FSTABLENAME = 'T_SAL_RETURNSTOCKENTRY') WW
|
||
JOIN
|
||
T_SAL_RETURNSTOCKENTRY EE
|
||
on WW.FSID = EE.FENTRYID
|
||
Group by WW.FID) Ae
|
||
Where Ae.FMustqty=Ae.FRealQty)
|
||
THEN 0
|
||
ELSE
|
||
ISNULL((Select SUM(FALLAMOUNT) From T_AR_RECEIVABLEENTRY Where FID = D.FID),0)
|
||
END)
|
||
-(ISNull((Select SUM(FOPENAMOUNTFOR) From T_AR_RECEIVABLEENTRY Where FID = D.FID),0))) as UnbilledAmount, --未开票额
|
||
A.FProjectSpecialIllustrate as ProjectSpecialIllustrate, --项目特殊情况说明
|
||
0 as Cost --成本
|
||
Into #TempTable1 ");
|
||
|
||
sql += string.Format(@"From MBBA_t_Cust100005 A --投标项目表
|
||
Left JOIN --关联销售订单表
|
||
T_SAL_ORDER B
|
||
on A.FTender = B.FTENDERNO
|
||
AND B.FBusinessType != 'RETURNSO'
|
||
AND B.F_CYG_CheckBalance != 1
|
||
AND B.FDocumentStatus = 'C'
|
||
AND B.FCloseStatus = 'A'
|
||
AND B.FCancelStatus = 'A'
|
||
Left JOIN --销售出库单
|
||
T_SAL_OUTSTOCK C
|
||
on A.FTENDER = C.FTENDERNO
|
||
AND C.FDocumentStatus = 'C'
|
||
AND C.FCancelStatus = 'A'
|
||
Left JOIN --应收单
|
||
T_AR_RECEIVABLE D
|
||
on A.FTENDER = D.FTENDERNO
|
||
AND D.FDocumentStatus = 'C'
|
||
AND D.FWRITTENOFFSTATUS != 'C' --过滤完全核销单据
|
||
AND D.FCancelStatus = 'A'
|
||
Left JOIN --销售合同
|
||
T_CRM_CONTRACT E
|
||
on E.F_CYG_BidSerial = A.FTENDER
|
||
AND E.FDocumentStatus = 'C'
|
||
AND E.FCloseStatus = 'A'
|
||
AND E.FCancelStatus = 'A'
|
||
Where 1=1");
|
||
|
||
//过滤条件
|
||
if (!string.IsNullOrEmpty(fDate))
|
||
{
|
||
string dateString = fDate.Split('-')[0];
|
||
sql += string.Format(@" AND A.FYEAR = {0}", dateString);
|
||
}
|
||
if (!string.IsNullOrEmpty(fTender))
|
||
{
|
||
sql += string.Format(@" AND A.FTender = '{0}'", fTender);
|
||
}
|
||
|
||
sql += string.Format(@";");
|
||
|
||
sql += string.Format(@"/*dialect*/
|
||
Select
|
||
ROW_NUMBER() OVER (ORDER BY Year) AS FID,
|
||
ROW_NUMBER() OVER (ORDER BY Year) AS FIDENTITYID,
|
||
*
|
||
into {0}
|
||
From #TempTable1", tableName);
|
||
|
||
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 fTender = customFilter["FTender"]?.ToString();
|
||
|
||
//这里是报表页面的标识
|
||
reportTitles.AddTitle("FTender", fTender == null ? "" : fTender);
|
||
reportTitles.AddTitle("FDate", fDate == null ? "" : Convert.ToDateTime(fDate).ToString("yyyy-MM-dd"));
|
||
}
|
||
return reportTitles;
|
||
}
|
||
}
|
||
}
|