282 lines
16 KiB
C#
282 lines
16 KiB
C#
using Kingdee.BOS;
|
||
using Kingdee.BOS.App.Data;
|
||
using Kingdee.BOS.Contracts;
|
||
using Kingdee.BOS.Contracts.Report;
|
||
using Kingdee.BOS.Core.Metadata;
|
||
using Kingdee.BOS.Core.Report;
|
||
using Kingdee.BOS.Core.SqlBuilder;
|
||
using Kingdee.BOS.Orm.DataEntity;
|
||
using Kingdee.BOS.Util;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.ComponentModel;
|
||
using System.Text;
|
||
|
||
namespace Pilot.Report.Exploitation.SBUYingShouBiao
|
||
{
|
||
[Description("应收款情况表(SBU)报表插件"), HotUpdate]
|
||
public class SBUYingShouBiaoReport : SysReportBaseService
|
||
{
|
||
|
||
public DateTime? beginTime = null;
|
||
public DateTime? endTime = null;
|
||
//初始化方法
|
||
public override void Initialize()
|
||
{
|
||
base.Initialize();
|
||
//设置报表类型为普通类型
|
||
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
|
||
//设置是否通过插件创建临时表
|
||
this.IsCreateTempTableByPlugin = true;
|
||
//设置是否分组汇总
|
||
this.ReportProperty.IsGroupSummary = true;
|
||
}
|
||
//获取过滤条件信息(构造单据信息)
|
||
public override ReportTitles GetReportTitles(IRptParams filter)
|
||
{
|
||
//创建标题对象
|
||
ReportTitles reportTitles = new ReportTitles();
|
||
//获取自定义过滤条件
|
||
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
|
||
if (customFilter != null)
|
||
{
|
||
//获取组织名称
|
||
//string multiOrgnNameValues = this.GetMultiOrgnNameValues(customFilter["F_YKQC_OrgId_uky"] as DynamicObject);
|
||
////获取起始日期
|
||
//string startValue = (customFilter["F_YKQC_Date_qtr"] == null) ? string.Empty :
|
||
// Convert.ToDateTime(customFilter["F_YKQC_Date_qtr"])
|
||
// .ToString("yyyy-MM-dd");
|
||
////获取结束日期
|
||
//string endValue = (customFilter["F_YKQC_Date_83g"] == null) ? string.Empty :
|
||
// Convert.ToDateTime(customFilter["F_YKQC_Date_83g"])
|
||
// .ToString("yyyy-MM-dd");
|
||
////添加标题信息
|
||
//reportTitles.AddTitle("F_YKQC_OrgId_re5", multiOrgnNameValues);
|
||
//reportTitles.AddTitle("F_YKQC_Date_qtr", startValue);
|
||
//reportTitles.AddTitle("F_YKQC_Date_83g", endValue);
|
||
}
|
||
//返回标题
|
||
return reportTitles;
|
||
}
|
||
//获取组织名称
|
||
private string GetMultiOrgnNameValues(DynamicObject orgIdStrings)
|
||
{
|
||
//创建组织名称列表
|
||
List<string> list = new List<string>();
|
||
//初始化结果字符串
|
||
string result = string.Empty;
|
||
//如果组织ID字符串不为空
|
||
if (String.IsNullOrEmpty(orgIdStrings["Id"].ToString()))
|
||
{
|
||
//获取查询服务
|
||
IQueryService service = Kingdee.BOS.Contracts.ServiceFactory.GetService<IQueryService>(base.Context);
|
||
//创建查询参数
|
||
QueryBuilderParemeter para = new QueryBuilderParemeter
|
||
{
|
||
//表单ID
|
||
FormId = "ORG_Organizations",
|
||
//查询组织名称
|
||
SelectItems = SelectorItemInfo.CreateItems("FNAME"),
|
||
//过滤条件，根据组织ID和区域ID
|
||
FilterClauseWihtKey = string.Format("FORGID IN ({0}) AND FLOCALEID = {1}", orgIdStrings["Id"].Long2Int(), base.Context.UserLocale.LCID)
|
||
};
|
||
//获取动态对象集合
|
||
DynamicObjectCollection dynamicObjects = service.GetDynamicObjectCollection(base.Context, para, null);
|
||
//遍历获取到的动态对象
|
||
foreach (DynamicObject current in dynamicObjects)
|
||
{
|
||
//将组织名称添加到列表
|
||
list.Add(current["FNAME"].ToString());
|
||
}
|
||
//如果列表不为空
|
||
if (list.Count > 0)
|
||
{
|
||
result = string.Join(",", list.ToArray());
|
||
}
|
||
}
|
||
//返回组织名称字符串
|
||
return result;
|
||
}
|
||
//设置单据列
|
||
public override ReportHeader GetReportHeaders(IRptParams filter)
|
||
{
|
||
//创建表头对象
|
||
ReportHeader header = new ReportHeader();
|
||
header.AddChild("SBU", new LocaleValue("SBU", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("明细区域", new LocaleValue("明细区域", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
|
||
header.AddChild("仪表发货金额", new LocaleValue("仪表发货金额", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header.AddChild("系统验收金额", new LocaleValue("系统验收金额", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header.AddChild("已到期应收账款", new LocaleValue("已到期应收账款(含子公司)", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
//一级表头
|
||
var header1 = header.AddChild("应收账款账龄", new LocaleValue("应收账款账龄"));
|
||
header1.AddChild("A6", new LocaleValue("1-6个月", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header1.AddChild("A12", new LocaleValue("7-12个月", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header1.AddChild("A2Y", new LocaleValue("1-2年", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header1.AddChild("A3Y", new LocaleValue("2-3年", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header1.AddChild("A5Y", new LocaleValue("4-5年", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header1.AddChild("A5YY", new LocaleValue("5年以上", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header.AddChild("本年回款额", new LocaleValue("本年回款额(含子公司)", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header.AddChild("本年专责小组回款额", new LocaleValue("本年专责小组回款额", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header.AddChild("本年实际业绩额", new LocaleValue("本年实际业绩额(含往年变更金额)", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
//header.AddChild("上年度应收额", new LocaleValue("上年度应收额", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header.AddChild("本年专责小组应收额", new LocaleValue("本年专责小组应收额", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header.AddChild("回款率", new LocaleValue("回款率", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header.AddChild("预发货到期应收账款", new LocaleValue("预发货到期应收账款", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header.AddChild("预发货回款额", new LocaleValue("预发货回款额", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header.AddChild("预发货回款率", new LocaleValue("预发货回款率", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
header.AddChild("回款目标", new LocaleValue("回款目标(万元)", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
//header.AddChild("应收款上限", new LocaleValue("应收款上限", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
//header.AddChild("回款达标率", new LocaleValue("回款达标率", this.Context.UserLocale.LCID), SqlStorageType.SqlMoney);
|
||
return header;
|
||
}
|
||
|
||
//创建临时表
|
||
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
|
||
{
|
||
DateTime now = DateTime.Now.AddDays(1).Date;
|
||
string begin = now.Year + "-01-01";
|
||
string end = now.ToString();
|
||
//获取过滤条件
|
||
string Filter = GetFilterWhere(filter);
|
||
if (beginTime.HasValue)
|
||
begin = beginTime.Value.ToString("yyyy-MM-dd");
|
||
if (endTime.HasValue)
|
||
end = endTime.Value.ToString("yyyy-MM-dd");
|
||
|
||
/*
|
||
取数SQL
|
||
SQL查询字符串，提取所需数据并将结果存入临时表
|
||
*/
|
||
string sql = string.Format($@"/*dialect*/
|
||
SELECT ROW_NUMBER() OVER(ORDER BY SBU) AS FIDENTITYID,a.*,[上年度应收额]=[当前业绩总和]-[本年实际业绩额]
|
||
,[回款率]=CASE WHEN ([当前应收]-[本年专责小组应收额]) <>0 THEN ([本年回款额]-[本年专责小组回款额])/([当前应收]-[本年专责小组应收额])
|
||
ELSE 0 END
|
||
,[预发货回款率]=CASE WHEN [预发货到期应收账款] >0 THEN [预发货回款额]/[预发货到期应收账款] ELSE 0 end
|
||
into {tableName}
|
||
FROM (
|
||
SELECT ISNULL(SBU,'其他') AS SBU,a.明细区域 as [明细区域]
|
||
,SUM([1-6个月]*A.汇率) [A6],SUM([7-12个月]*A.汇率) [A12],SUM([1-2年]*A.汇率)[A2Y],SUM([2-3年]*A.汇率) [A3Y],SUM([4-5年]*A.汇率) [A5Y],SUM([5年以上]*A.汇率) [A5YY]
|
||
|
||
,[本年回款额]=ISNULL((SELECT SUM(aa.[BenCiHeXiao]*aa.[FExchangeRate]) FROM [ZZZ_HeXiaoBook] aa
|
||
WHERE [XiaoShouFid] IN (SELECT akk.销售单主键ID FROM ZRP_YingShouMingXi_yuyubo akk WHERE akk.SBU=a.SBU )
|
||
AND aa.[ShouKuanFDate] >='{begin}' AND aa.[ShouKuanFDate]< '{end}'),0)
|
||
,[本年专责小组回款额]=ISNULL((SELECT SUM(aa.[BenCiHeXiao]*aa.[FExchangeRate]) FROM [ZZZ_HeXiaoBook] aa
|
||
WHERE [XiaoShouFid] IN (SELECT akk.销售单主键ID FROM ZRP_YingShouMingXi_yuyubo akk WHERE akk.SBU=a.SBU AND akk.[账龄月]>12 )
|
||
AND aa.[ShouKuanFDate] >='{begin}' AND aa.[ShouKuanFDate]< '{end}'),0)
|
||
,[本年实际业绩额]=
|
||
(ISNULL((
|
||
SELECT SUM(
|
||
CASE WHEN B.FCHANGEDATE IS not NULL AND b.FDate <'{begin}' THEN
|
||
aa.FBILLALLAMOUNT_LC - b.F_AMOUNTSTAR WHEN b.FDate >= '{begin}' THEN aa.FBILLALLAMOUNT_LC end )
|
||
FROM T_SAL_ORDERFIN aa
|
||
LEFT JOIN T_SAL_ORDER B
|
||
ON aa.FID = B.FID
|
||
WHERE b.FDocumentStatus='C' AND aa.FID IN
|
||
(
|
||
SELECT DISTINCT akk.销售单主键ID
|
||
FROM ZRP_YingShouMingXi_yuyubo akk
|
||
WHERE akk.SBU = a.SBU
|
||
)
|
||
AND (( B.FCHANGEDATE >= '{begin}' AND B.FCHANGEDATE < '{end}') OR (b.FDate >= '{begin}' AND B.FDate < '{end}' ) )
|
||
)
|
||
,0))
|
||
,[本年专责小组应收额]=ISNULL((SELECT SUM(aa.实际已出货应收款合计) FROM ZRP_YingShouMingXi_yuyubo aa where aa.账龄月>12 AND aa.SBU=A.SBU),0)
|
||
,[当前业绩总和]= (ISNULL((
|
||
SELECT SUM(aa.FBILLALLAMOUNT_LC)
|
||
FROM T_SAL_ORDERFIN aa
|
||
LEFT JOIN T_SAL_ORDER B
|
||
ON aa.FID = B.FID
|
||
WHERE b.FDocumentStatus='C' AND aa.FID IN
|
||
(
|
||
SELECT DISTINCT akk.销售单主键ID
|
||
FROM ZRP_YingShouMingXi_yuyubo akk
|
||
WHERE akk.SBU = a.SBU
|
||
) )
|
||
,0))
|
||
,[当前应收]= (SUM(a.应收款合计*ISNULL(a.[收款条件分摊比例],100)*0.01 ))
|
||
,[预发货到期应收账款]=ISNULL((SELECT SUM(aa.到期金额*aa.汇率) FROM ZRP_YingShouMingXi_yuyubo aa where aa.收款条件 LIKE '款到发货%'AND aa.SBU=A.SBU),0)
|
||
,[预发货回款额]=ISNULL((SELECT SUM(aa.累计到款金额*aa.[收款条件分摊比例]*0.01) FROM ZRP_YingShouMingXi_yuyubo aa where aa.收款条件 LIKE '款到发货%'AND aa.SBU=A.SBU AND aa.到期金额 >0),0)
|
||
,[已到期应收账款]=SUM(a.到期金额*a.汇率)
|
||
,[回款目标]=ISNULL(( SELECT SUM(FPAYMENTSUM) FROM (SELECT DISTINCT bb.FEntryID,FPAYMENTSUM FROM MBBA_t_Cust_Entry100003 bb
|
||
LEFT JOIN V_BD_SALESMAN_L L WITH(NOLOCK) ON bb.FNAME=L.fid
|
||
LEFT JOIN (SELECT DISTINCT ACL22.名称,ACL22.明细区域 ,ACL22.区域 FROM ERPTOHR.HYHRV3.dbo.v_erp_empinfo ACL22 ) ACL
|
||
ON ACL.名称=L.FNAME AND ISNULL(ACL.明细区域,'') != ''
|
||
WHERE ACL.区域=a.SBU
|
||
AND bb.fid = (SELECT MAX(fid) FROM MBBA_t_Cust100002 aaa WHERE aaa.FDOCUMENTSTATUS='C' AND YEAR(aaa.F_VRYF_DATE_83G)=YEAR( '{begin}' ) )) ccc),0)
|
||
,[仪表发货金额]=sum([仪表发货金额])
|
||
,[系统验收金额]=sum([系统验收金额])
|
||
FROM ZRP_YingShouMingXi_yuyubo a
|
||
{Filter}
|
||
GROUP BY a.SBU,a.明细区域 ) a
|
||
");
|
||
|
||
//执行SQL并动态创建报表
|
||
DBUtils.ExecuteDynamicObject(this.Context, sql);
|
||
}
|
||
//获取过滤条件
|
||
private string GetFilterWhere(IRptParams fileter)
|
||
{
|
||
//获取自定义过滤条件
|
||
DynamicObject customFilter = fileter.FilterParameter.CustomFilter;
|
||
//创建字符串构建器
|
||
StringBuilder strwhere = new StringBuilder();
|
||
//初始化过滤条件
|
||
strwhere.AppendLine("Where 1=1");
|
||
var orgFid = customFilter["F_YKQC_OrgId_uky"] as DynamicObject;
|
||
if (orgFid != null)
|
||
{
|
||
string org = string.IsNullOrWhiteSpace(customFilter["F_YKQC_OrgId_uky"].ToString()) ? "" :
|
||
string.Format("AND a.组织ID={0}", orgFid["Id"].Long2Int());
|
||
//添加组织过滤条件
|
||
strwhere.AppendLine(org);
|
||
}
|
||
|
||
//日期
|
||
string startValue = (customFilter["F_YKQC_Date_qtr"] == null) ? string.Empty :
|
||
Convert.ToDateTime(customFilter["F_YKQC_Date_qtr"]).ToString("yyyy-MM-dd");
|
||
string endValue = (customFilter["F_YKQC_Date_83g"] == null) ? string.Empty :
|
||
Convert.ToDateTime(customFilter["F_YKQC_Date_83g"]).ToString("yyyy-MM-dd");
|
||
//添加日期过滤条件
|
||
if (!String.IsNullOrWhiteSpace(startValue))
|
||
{
|
||
//strwhere.AppendLine(string.Format(@"AND A.FDate >= '{0}'", startValue));
|
||
beginTime = Convert.ToDateTime(startValue).Date;
|
||
}
|
||
if (!String.IsNullOrWhiteSpace(endValue))
|
||
{
|
||
endTime= Convert.ToDateTime(endValue).AddDays(1).Date;
|
||
//strwhere.AppendLine(string.Format(@"AND A.FDate <= '{0}'", endValue));
|
||
}
|
||
return strwhere.ToString();
|
||
}
|
||
|
||
public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
|
||
{
|
||
var result = base.GetSummaryColumnInfo(filter);
|
||
result.Add(new SummaryField("已到期应收账款", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
//result.Add(new SummaryField("应收账款账龄", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("A6", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("A12", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("A2Y", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("A3Y", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("A5Y", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("A5YY", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("本年回款额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("本年专责小组回款额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("本年实际业绩额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("上年度应收额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("本年专责小组应收额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("回款率", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("预发货到期应收账款", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("预发货回款额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("预发货回款率", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("回款目标", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("仪表发货金额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
result.Add(new SummaryField("系统验收金额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
|
||
|
||
return result;
|
||
}
|
||
}
|
||
}
|
||
|