435 lines
19 KiB
Python
435 lines
19 KiB
Python
|
||
#-*- encoding:utf-8 -*-
|
||
|
||
import clr
|
||
clr.AddReference("System")
|
||
clr.AddReference("Kingdee.BOS")
|
||
clr.AddReference("Kingdee.BOS.Core")
|
||
clr.AddReference("Kingdee.BOS.DataEntity")
|
||
clr.AddReference("Kingdee.BOS.App")
|
||
clr.AddReference("Kingdee.BOS.Contracts")
|
||
from Kingdee.BOS import *
|
||
from Kingdee.BOS.Contracts import *
|
||
from Kingdee.BOS.Contracts.Report import *
|
||
from Kingdee.BOS.Core import *
|
||
from Kingdee.BOS.Core.Metadata import *
|
||
from Kingdee.BOS.Core.Report import *
|
||
from Kingdee.BOS.Core.SqlBuilder import *
|
||
from Kingdee.BOS.App.Data import *
|
||
from Kingdee.BOS.Orm.DataEntity import *
|
||
from System import *
|
||
from System.ComponentModel import *
|
||
from System.Collections.Generic import *
|
||
from System.Text import *
|
||
from System.Threading.Tasks import *
|
||
|
||
|
||
|
||
#初始化
|
||
def Initialize():
|
||
this.ReportProperty.ReportName = LocaleValue("回款报表#", this.Context.UserLocale.LCID)
|
||
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL
|
||
this.IsCreateTempTableByPlugin = True
|
||
this.ReportProperty.IsGroupSummary = True
|
||
this.ReportProperty.SimpleAllCols = False
|
||
|
||
#设置标题
|
||
def GetReportTitles(Filter):
|
||
reportTitles = ReportTitles()
|
||
customFiler = Filter.FilterParameter.CustomFilter
|
||
if customFiler:
|
||
F_YWZZ = customFiler["F_YWZZ"]
|
||
FStartDate = customFiler["F_SDate"]
|
||
FEndDate = customFiler["F_EDate"]
|
||
FCust = customFiler["FCust"]
|
||
FZZHTH = customFiler["F_contractnumber"]
|
||
if FStartDate != None and FStartDate.ToString() != "":
|
||
reportTitles.AddTitle("FSDate",str(FStartDate.ToString("yyyy-MM-dd")))
|
||
if FEndDate != None and FEndDate.ToString() != "":
|
||
reportTitles.AddTitle("FEDate",str(FEndDate.ToString("yyyy-MM-dd")))
|
||
if FCust != None:
|
||
reportTitles.AddTitle("FCust",str(FCust["Name"].ToString()))
|
||
reportTitles.AddTitle("FZZHTH",FZZHTH)
|
||
return reportTitles
|
||
|
||
|
||
#设置单据列
|
||
def GetReportHeaders(Filter):
|
||
header = ReportHeader()
|
||
# headerch = ReportHeader()
|
||
header.AddChild("业务日期",LocaleValue("业务日期",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
#header.AddChild("收款明细",LocaleValue("收款明细",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("日期",LocaleValue("日期",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("ShouKuanBillNo",LocaleValue("收款单号",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("付款名称",LocaleValue("付款名称",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("合同名称",LocaleValue("合同名称",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
|
||
header.AddChild("金额",LocaleValue("金额（本位币）",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
header.AddChild("分摊金额",LocaleValue("分摊金额（本位币）",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
|
||
header.AddChild("订单号",LocaleValue("订单号",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("销售员",LocaleValue("销售员",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("SBU",LocaleValue("SBU",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("明细区域",LocaleValue("明细区域",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("备注",LocaleValue("备注",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("到期日期",LocaleValue("到期日期（银承）",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("款到发货",LocaleValue("款到发货",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
header.AddChild("一至六个月",LocaleValue("1-6个月",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
header.AddChild("七至十二个月",LocaleValue("7-12个月",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
header.AddChild("一至二年",LocaleValue("1-2年",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
header.AddChild("二至三年",LocaleValue("2-3年",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
header.AddChild("三至四年",LocaleValue("3-4年",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
header.AddChild("四至五年",LocaleValue("4-5年",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
header.AddChild("五年以上",LocaleValue("5年以上",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
|
||
header.AddChild("账龄阶段",LocaleValue("账龄/阶段",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("账龄月",LocaleValue("账龄/月",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
header.AddChild("币别",LocaleValue("币别",this.Context.UserLocale.LCID),SqlStorageType.Sqlvarchar)
|
||
header.AddChild("分摊比例",LocaleValue("分摊比例",this.Context.UserLocale.LCID),SqlStorageType.SqlDecimal)
|
||
|
||
return header
|
||
|
||
#创建临时报表
|
||
def BuilderReportSqlAndTempTable(Filter,tableName):
|
||
# strFilter = GetFilterWhere(Filter)
|
||
strFilter = ""
|
||
seqFld = String.format(this.KSQL_SEQ,OrderColumn(Filter))
|
||
|
||
customFiler = Filter.FilterParameter.CustomFilter
|
||
#组织ID
|
||
ZZID = "0" if customFiler["F_YWZZ"] == None else str(customFiler["F_YWZZ"]["Id"])
|
||
#客户ID
|
||
KHID = "0" if customFiler["FCust"] == None else str(customFiler["FCust"]["Id"])
|
||
#日期类型
|
||
RQLX = str(customFiler["F_DateType"])
|
||
#币别
|
||
BB = "0" if customFiler["F_Currency"] == None else str(customFiler["F_Currency"]["Name"])
|
||
#只看特殊核销
|
||
# raise Exception(BB)
|
||
ZKTSHX = customFiler["F_ZKTSHX"]
|
||
#不看特殊核销
|
||
BKTSHX = customFiler["F_BKTSHX"]
|
||
#收款单号
|
||
SKDH = "" if customFiler["F_RECEIVEBILLNO"] == None else str(customFiler["F_RECEIVEBILLNO"])
|
||
|
||
#收款开始/结束日期
|
||
SKRQS = ""
|
||
SKRQE = ""
|
||
#核销开始/结束日期
|
||
HXRQS = ""
|
||
HXRQE = ""
|
||
|
||
tempsql = "SELECT * FROM #TEMP3 WHERE 1 = 1"
|
||
|
||
if customFiler["F_SDate"] != None and customFiler["F_SDate"].ToString() != "":
|
||
if RQLX == "1":
|
||
HXRQS = str(customFiler["F_SDate"].ToString("yyyy-MM-dd"))
|
||
strFilter += " AND (业务日期 >= '{0}' OR ISNULL(业务日期,'') = '' OR 分组 IN (3,4))".format(str(customFiler["F_SDate"].ToString("yyyy-MM-dd")))
|
||
if RQLX == "2":
|
||
strFilter += " AND (日期 >= '{0}' OR ISNULL(日期,'') = '' OR 分组 IN (3,4))".format(str(customFiler["F_SDate"].ToString("yyyy-MM-dd")))
|
||
SKRQS = str(customFiler["F_SDate"].ToString("yyyy-MM-dd"))
|
||
if customFiler["F_EDate"] != None and customFiler["F_EDate"].ToString() != "":
|
||
if RQLX == "1":
|
||
strFilter += " AND (业务日期 <= '{0}' OR ISNULL(业务日期,'') = '' OR 分组 IN (3,4))".format(str(customFiler["F_EDate"].ToString("yyyy-MM-dd")))
|
||
HXRQE = str(customFiler["F_EDate"].ToString("yyyy-MM-dd"))
|
||
if RQLX == "2":
|
||
strFilter += " AND (日期 <= '{0}' OR ISNULL(日期,'') = '' OR 分组 IN (3,4))".format(str(customFiler["F_EDate"].ToString("yyyy-MM-dd")))
|
||
SKRQE = str(customFiler["F_EDate"].ToString("yyyy-MM-dd"))
|
||
if customFiler["F_contractnumber"] != None and customFiler["F_contractnumber"].ToString() != "":
|
||
strFilter += " AND 订单号 LIKE '%{0}%'".format(str(customFiler["F_contractnumber"].ToString()))
|
||
if customFiler["F_Currency"] != None and customFiler["F_Currency"].ToString() != "":
|
||
strFilter += " AND (币别 = '{0}' OR 分组 IN (3,4) OR '{0}' = '0')".format(BB)
|
||
if ZKTSHX:
|
||
strFilter += " AND 分组 IN (3,4)"
|
||
if BKTSHX:
|
||
strFilter += " AND 分组 NOT IN (3,4)"
|
||
if SKDH != "":
|
||
strFilter += " AND ShouKuanBillNo = '{0}'".format(SKDH)
|
||
if customFiler["FSaler"] != None and customFiler["FSaler"].ToString() != "":
|
||
strFilter += " AND (ISNULL('{0}','') = '' OR 销售员 LIKE '%{0}%' OR 业务日期 = '特殊核销')".format(str(customFiler["FSaler"].ToString()))
|
||
if customFiler["FSBU"] != None and customFiler["FSBU"].ToString() != "":
|
||
strFilter += " AND (ISNULL('{0}','') = '' OR SBU LIKE '%{0}%' OR 业务日期 = '特殊核销')".format(str(customFiler["FSBU"].ToString()))
|
||
|
||
sql = String.format("""/*dialect*/
|
||
|
||
/*抓取销售订单及分摊单数据*/
|
||
SELECT T1.FID,T1.F_CONTRACTNUMBER AS '订单号',CASE ISNULL(T3.F_RES,'') WHEN '' THEN ISNULL(T5.FNAME,CASE ISNULL(T1.F_RESPONSIBLE,'') WHEN '' THEN T4.FNAME ELSE T1.F_RESPONSIBLE END) ELSE F_RES END AS '销售员'
|
||
,FSHARERATE AS '分摊比例',FEXCHANGERATE AS '汇率',T7.FNAME AS '合同名称'
|
||
INTO #TEMP1
|
||
FROM T_SAL_ORDER T1
|
||
LEFT JOIN T_PerformanceSharing_LK T2 ON T2.FSID = T1.FID
|
||
LEFT JOIN T_PerformanceSharingEntry T3 ON T2.FID = T3.FID
|
||
LEFT JOIN V_BD_SALESMAN_L T4 ON T4.FID = T1.FSALERID AND T4.FLOCALEID = 2052
|
||
LEFT JOIN V_BD_SALESMAN_L T5 ON T5.FID = T3.FSALEID AND T5.FLOCALEID = 2052
|
||
LEFT JOIN T_SAL_ORDERFIN T6 ON T1.FID = T6.FID
|
||
LEFT JOIN T_BD_CUSTOMER_L T7 ON T1.FCUSTID = T7.FCUSTID AND T7.FLOCALEID = 2052
|
||
WHERE T1.FID IN (SELECT XiaoShouFid FROM ZZZ_HeXiaoBook)
|
||
AND T1.FDOCUMENTSTATUS = 'C'
|
||
--AND (T1.FSALEORGID = {4} OR {4} = 0)
|
||
AND (T1.FCUSTID = {5} OR {5} = 0)
|
||
|
||
/*抓取收款单及应收票据数据*/
|
||
SELECT T1.FID,FORMAT(T1.FDATE,'yyyy-MM-dd') AS '收款日期',T1.FRECAMOUNT AS '收款金额'
|
||
,T3.FNAME AS '付款单位',T4.FNAME AS '结算方式',FORMAT(T6.FDUEDATE,'yyyy-MM-dd') AS '到期日期'
|
||
INTO #TEMP2
|
||
FROM T_AR_RECEIVEBILL T1
|
||
LEFT JOIN T_AR_RECEIVEBILLENTRY T2 ON T1.FID = T2.FID
|
||
LEFT JOIN T_BD_CUSTOMER_L T3 ON T1.FPAYUNITTYPE = 'BD_Customer' AND T1.FPAYUNIT = T3.FCUSTID AND T3.FLOCALEID = 2052
|
||
LEFT JOIN T_BD_SETTLETYPE_L T4 ON T2.FSETTLETYPEID = T4.FID AND T4.FLOCALEID = 2052
|
||
LEFT JOIN T_AR_RECEIVEBILLREC T5 ON T5.FID = T2.FID
|
||
LEFT JOIN T_CN_BILLRECEIVABLE T6 ON T5.FBILLID = T6.FID
|
||
WHERE 1=1
|
||
--AND T1.FID IN (SELECT ShouKuanFid FROM ZZZ_HeXiaoBook)
|
||
AND T1.FDOCUMENTSTATUS = 'C'
|
||
AND (T1.FPAYORGID = {4} OR {4} = 0)
|
||
AND (T1.FPAYUNIT = {5} OR {5} = 0)
|
||
--AND (T1.FDATE >= '{6}' OR '{6}' = '')
|
||
--AND (T1.FDATE <= '{7}' OR '{7}' = '')
|
||
|
||
/*抓取核销流水中间表正常核销部分*/
|
||
SELECT
|
||
T1.Fid,T1.XiaoShouFid,T1.ShouKuanFid,T1.ShouKuanBillNo
|
||
,CAST(ISNULL(T2.分摊比例,0)AS decimal(10,2)) AS '分摊比例'
|
||
,ROW_NUMBER() OVER(PARTITION BY T1.ShouKuanFid ORDER BY T1.ShouKuanFid,ShouKuanFDate,XiaoShouFid) AS '分摊序号'
|
||
,FORMAT(T1.ShouKuanFDate,'yyyy-MM-dd') AS '业务日期'
|
||
,CAST('' AS VARCHAR(500)) AS '收款明细'
|
||
,CAST('' AS VARCHAR(500)) AS '日期'
|
||
,CAST('' AS VARCHAR(500)) AS '付款名称'
|
||
,ISNULL(T2.合同名称,'') AS '合同名称'
|
||
,CAST(0 AS decimal(10,2)) AS '金额'
|
||
,CAST((ISNULL(T2.分摊比例,100)/100 * T1.BenCiHeXiao * T2.汇率) AS decimal(10,2)) AS '分摊金额'
|
||
,T2.订单号 AS '订单号'
|
||
,ISNULL(T2.销售员,'') AS '销售员'
|
||
,CAST('' AS VARCHAR(500)) AS 'SBU'
|
||
,CAST('' AS VARCHAR(500)) AS '明细区域'
|
||
,T1.Remark AS '备注'
|
||
,CAST('' AS VARCHAR(500)) AS '到期日期'
|
||
,CAST(0 AS decimal(10,2)) AS '款到发货'
|
||
,CAST(0 AS decimal(10,2)) AS '一至六个月'
|
||
,CAST(0 AS decimal(10,2)) AS '七至十二个月'
|
||
,CAST(0 AS decimal(10,2)) AS '一至二年'
|
||
,CAST(0 AS decimal(10,2)) AS '二至三年'
|
||
,CAST(0 AS decimal(10,2)) AS '三至四年'
|
||
,CAST(0 AS decimal(10,2)) AS '四至五年'
|
||
,CAST(0 AS decimal(10,2)) AS '五年以上'
|
||
,CAST('' AS VARCHAR(500)) AS '账龄阶段'
|
||
,T1.ZhangLing AS '账龄月'
|
||
,CAST('' AS VARCHAR(500)) AS '币别'
|
||
,1 AS '分组'
|
||
INTO #TEMP3
|
||
FROM ZZZ_HeXiaoBook T1
|
||
INNER JOIN #TEMP1 T2 ON T1.XiaoShouFid = T2.FID
|
||
WHERE T1.ShouKuanType = 0
|
||
AND (T1.FSaleOrgId = {4} OR {4} = 0)
|
||
AND (FORMAT(T1.ShouKuanFDate,'yyyy-MM-dd') >= '{8}' OR '{8}' = '')
|
||
AND (FORMAT(T1.ShouKuanFDate,'yyyy-MM-dd') <= '{9}' OR '{9}' = '')
|
||
|
||
/*插入待核销金额行*/
|
||
INSERT INTO #TEMP3 (Fid,ShouKuanFid,ShouKuanBillNo,分摊序号,日期,销售员,订单号,金额,分摊金额,付款名称,合同名称,款到发货,一至六个月,七至十二个月,一至二年,二至三年,三至四年,四至五年,五年以上,账龄阶段,账龄月,分组)
|
||
SELECT DISTINCT
|
||
'99999999' AS 'Fid'
|
||
,T1.FID
|
||
,T1.FBILLNO
|
||
,'99999999' AS '分摊序号'
|
||
,FORMAT(T1.FDATE,'yyyy-MM-dd') AS '日期'
|
||
,'' AS '销售员'
|
||
,'待核销' AS '订单号'
|
||
,0 AS '金额'
|
||
,ISNULL(T1.FRECAMOUNT,0)-(ISNULL(T1.F_AMOUNT,0)*ISNULL(T1.FEXCHANGERATE,0)) AS '分摊金额'
|
||
,'' AS '付款名称'
|
||
,ISNULL(T3.FNAME,'') AS '合同名称'
|
||
,0 AS '款到发货'
|
||
,0 AS '一至六个月'
|
||
,0 AS '七至十二个月'
|
||
,0 AS '一至二年'
|
||
,0 AS '二至三年'
|
||
,0 AS '三至四年'
|
||
,0 AS '四至五年'
|
||
,0 AS '五年以上'
|
||
,CAST('' AS VARCHAR(500)) AS '账龄阶段'
|
||
,0
|
||
,1 AS '分组'
|
||
FROM T_AR_RECEIVEBILL T1
|
||
--INNER JOIN #TEMP3 T2 ON T1.FID = T2.ShouKuanFid
|
||
LEFT JOIN T_BD_CUSTOMER_L T3 ON T1.FPAYUNITTYPE = 'BD_Customer' AND T1.FPAYUNIT = T3.FCUSTID AND T3.FLOCALEID = 2052
|
||
WHERE ISNULL(T1.FRECAMOUNTFOR,0)-ISNULL(T1.F_AMOUNT,0) > 0
|
||
AND T1.FDOCUMENTSTATUS = 'C'
|
||
AND (T1.FPAYORGID = {4} OR {4} = 0)
|
||
AND ((T1.FPAYUNIT = {5} AND T1.FPAYUNITTYPE = 'BD_Customer') OR {5} = 0)
|
||
AND (T1.FDATE >= '{6}' OR '{6}' = '')
|
||
AND (T1.FDATE <= '{7}' OR '{7}' = '')
|
||
|
||
/*抓取核销流水中间表特殊核销部分*/
|
||
INSERT INTO #TEMP3 (Fid,XiaoShouFid,ShouKuanFid,ShouKuanBillNo,分摊比例,分摊序号,业务日期,付款名称,合同名称,金额,分摊金额,订单号,销售员,备注,账龄月,币别,分组)
|
||
SELECT a.* FROM(
|
||
SELECT 0 Fid,0 XiaoShouFid,0 ShouKuanFid,'' ShouKuanBillNo,0 分摊比例,0 分摊序号,'特殊核销' 业务日期,'' 付款名称,'' 合同名称,0 金额,0 分摊金额,'' 订单号,'' 销售员,'' 备注,0 账龄月,'' 币别,3 分组
|
||
UNION ALL
|
||
SELECT
|
||
T1.Fid,T1.XiaoShouFid,T1.ShouKuanFid,T1.ShouKuanBillNo
|
||
,CAST(ISNULL(T2.分摊比例,0)AS decimal(10,2)) AS '分摊比例'
|
||
,ROW_NUMBER() OVER(PARTITION BY T1.ShouKuanFid ORDER BY T1.ShouKuanFDate) AS '分摊序号'
|
||
,FORMAT(T1.ShouKuanFDate,'yyyy-MM-dd') AS '业务日期'
|
||
,ISNULL(T2.合同名称,'') AS '付款名称'
|
||
,ISNULL(T2.合同名称,'') AS '合同名称'
|
||
,CAST((ISNULL(T2.分摊比例,100)/100 * T1.BenCiHeXiao * T2.汇率) AS decimal(10,2)) AS '金额'
|
||
,CAST((ISNULL(T2.分摊比例,100)/100 * T1.BenCiHeXiao * T2.汇率) AS decimal(10,2)) AS '分摊金额'
|
||
,T2.订单号 AS '订单号'
|
||
,ISNULL(T2.销售员,'') AS '销售员'
|
||
,T1.Remark AS '备注'
|
||
,T1.ZhangLing AS '账龄月'
|
||
,CAST('' AS VARCHAR(500)) AS '币别'
|
||
--,CASE T1.ShouKuanType WHEN 1 THEN 3 WHEN 2 THEN 4 END AS '分组'
|
||
,3 '分组'
|
||
FROM ZZZ_HeXiaoBook T1
|
||
INNER JOIN #TEMP1 T2 ON T1.XiaoShouFid = T2.FID
|
||
WHERE T1.ShouKuanType IN (1,2)
|
||
AND (T1.FSaleOrgId = {4} OR {4} = 0)
|
||
AND (FORMAT(T1.ShouKuanFDate,'yyyy-MM-dd') >= '{8}' OR '{8}' = '')
|
||
AND (FORMAT(T1.ShouKuanFDate,'yyyy-MM-dd') <= '{9}' OR '{9}' = '')
|
||
)a
|
||
|
||
|
||
/*修改待核销分摊序号*/
|
||
SELECT *, ROW_NUMBER() OVER(PARTITION BY ShouKuanFid ORDER BY ShouKuanFid,分摊序号) rownumber
|
||
INTO #TEMP4
|
||
FROM #TEMP3
|
||
|
||
UPDATE T1 SET 分摊序号 =T2.rownumber FROM #TEMP3 T1
|
||
INNER JOIN #TEMP4 T2 ON T1.Fid = T2.Fid
|
||
AND T1.ShouKuanFid = T2.ShouKuanFid
|
||
AND T1.分摊序号 = T2.分摊序号
|
||
|
||
|
||
/*更新备注字段*/
|
||
UPDATE T1 SET 备注 = T2.结算方式 +备注 FROM #TEMP3 T1
|
||
INNER JOIN #TEMP2 T2 ON T1.ShouKuanFid = T2.FID
|
||
WHERE T2.结算方式 <> '电汇' AND T1.分组 = 1
|
||
|
||
/*更新收款日期*/
|
||
UPDATE T1 SET 日期 = T2.收款日期 FROM #TEMP3 T1
|
||
INNER JOIN #TEMP2 T2 ON T1.ShouKuanFid = T2.FID AND T1.分组 = 1
|
||
|
||
/*更新付款单位字段*/
|
||
UPDATE A SET 付款名称 = T2.付款单位 FROM #TEMP3 A
|
||
INNER JOIN (SELECT A.FID
|
||
,CASE WHEN REPLACE(F_OTHER,' ','') <> '' AND F_OTHER IS NOT NULL THEN F_OTHER ELSE C.FNAME END AS '付款单位'
|
||
FROM T_AR_RECEIVEBILL A
|
||
LEFT JOIN T_AR_RECEIVEBILLENTRY B ON A.FID = B.FID
|
||
LEFT JOIN T_BD_CUSTOMER_L C ON A.FPAYUNITTYPE = 'BD_Customer' AND A.FPAYUNIT = C.FCUSTID AND C.FLOCALEID = 2052
|
||
) T2 ON A.ShouKuanFid = T2.FID
|
||
WHERE 分摊序号 = 1 AND A.分组 = 1
|
||
|
||
|
||
/*更新币别字段*/
|
||
UPDATE A SET 币别 = T2.币别 FROM #TEMP3 A
|
||
INNER JOIN (SELECT A.FID
|
||
,D.FNAME AS '币别'
|
||
FROM T_AR_RECEIVEBILL A
|
||
LEFT JOIN T_AR_RECEIVEBILLENTRY B ON A.FID = B.FID
|
||
LEFT JOIN T_BD_CURRENCY_L D ON D.FCURRENCYID = A.FCURRENCYID AND D.FLOCALEID = 2052
|
||
) T2 ON A.ShouKuanFid = T2.FID AND A.分组 = 1
|
||
|
||
|
||
--/*更新待核销合同名称字段*/
|
||
--UPDATE A SET 合同名称 = ISNULL(付款名称,'') FROM #TEMP3 A
|
||
--WHERE 订单号 = '待核销' AND 分摊序号 = 1
|
||
|
||
|
||
/*只更新分摊序号为1的收款金额字段*/
|
||
UPDATE T1 SET 金额 = T2.收款金额 FROM #TEMP3 T1
|
||
INNER JOIN #TEMP2 T2 ON T1.ShouKuanFid = T2.FID
|
||
WHERE T1.分摊序号 = 1 AND T1.分组 = 1
|
||
|
||
/*更新到期日期（银承）字段*/
|
||
UPDATE T1 SET 到期日期 = T2.到期日期 FROM #TEMP3 T1
|
||
INNER JOIN #TEMP2 T2 ON T1.ShouKuanFid = T2.FID
|
||
WHERE T2.结算方式 IN ('银行承兑汇票','商业承兑汇票') AND T1.分组 = 1
|
||
|
||
/*更新SBU、明细区域字段*/
|
||
UPDATE #TEMP3 SET SBU=b.区域,[明细区域]=b.明细区域
|
||
FROM (SELECT DISTINCT ACO22.名称,ACO22.明细区域,ACO22.区域 FROM ERPTOHR.HYHRV3.dbo.v_erp_empinfo ACO22 ) b
|
||
WHERE 销售员=b.名称
|
||
|
||
/*销售员'应收款专责小组'时，SBU和明细区域填'应收款专责小组'*/
|
||
UPDATE #TEMP3 SET SBU=销售员,[明细区域]=销售员
|
||
WHERE 销售员='应收款专责小组'
|
||
|
||
/*处理分摊金额尾差*/
|
||
UPDATE T1 SET 分摊金额 = ISNULL(T1.分摊金额,0)+(ISNULL(T1.金额,0)-ISNULL(T2.分摊金额,0)) FROM #TEMP3 T1
|
||
INNER JOIN(SELECT ShouKuanFid,SUM(ISNULL(分摊金额,0)) 分摊金额 FROM #TEMP3 GROUP BY ShouKuanFid) T2 ON T1.ShouKuanFid = T2.ShouKuanFid
|
||
WHERE T1.分摊序号 = 1 AND T1.金额>0 AND T1.分组 = 1
|
||
|
||
/*更新账龄字段*/
|
||
UPDATE #TEMP3 SET
|
||
[账龄阶段]=CASE WHEN [账龄月]>=1 AND [账龄月]<=6 THEN '1-6个月' WHEN [账龄月]>=7 AND [账龄月]<=12 THEN '7-12个月' WHEN [账龄月]>=13 AND [账龄月]<=24
|
||
THEN '1-2年' WHEN [账龄月]>=25 AND [账龄月]<=36 THEN '2-3年' WHEN [账龄月]>=37 AND [账龄月]<=48 THEN '3-4年' WHEN [账龄月]>=49 THEN '5年以上' ELSE '款到发货' END
|
||
--,[风险评估级别]=CASE WHEN [账龄月]>=1 AND [账龄月]<=6 THEN '正常' WHEN [账龄月]>=7 AND [账龄月]<=12 THEN '催款函' WHEN [账龄月]>=13 AND [账龄月]<=18
|
||
--THEN '律师' WHEN [账龄月]>=19 THEN '诉讼' ELSE '' END
|
||
,[一至六个月]=CASE WHEN [账龄月]>=1 AND [账龄月]<=6 THEN 分摊金额 ELSE 0 END
|
||
,[七至十二个月]=CASE WHEN [账龄月]>=7 AND [账龄月]<=12 THEN 分摊金额 ELSE 0 END
|
||
,[一至二年]=CASE WHEN [账龄月]>=13 AND [账龄月]<=24 THEN 分摊金额 ELSE 0 END
|
||
,[二至三年]=CASE WHEN [账龄月]>=25 AND [账龄月]<=36 THEN 分摊金额 ELSE 0 END
|
||
,[三至四年]=CASE WHEN [账龄月]>=37 AND [账龄月]<=48 THEN 分摊金额 ELSE 0 END
|
||
,[四至五年]=CASE WHEN [账龄月]>=49 AND [账龄月]<=60 THEN 分摊金额 ELSE 0 END
|
||
,[五年以上]=CASE WHEN [账龄月]> 60 THEN 分摊金额 ELSE 0 END
|
||
,[款到发货]=CASE WHEN [账龄月]<= 0 THEN 分摊金额 ELSE 0 END
|
||
WHERE 业务日期 <> '特殊核销'
|
||
|
||
/*无特殊核销时清空特殊核销行栏头*/
|
||
IF (SELECT COUNT(*) FROM #TEMP3 WHERE 分组 IN (3,4)) = 1
|
||
BEGIN
|
||
DELETE FROM #TEMP3 WHERE 分组 IN (3,4)
|
||
END
|
||
|
||
UPDATE #TEMP3 SET 分组 = CASE WHEN Fid = '99999999' AND 分摊序号 = 1 THEN 2 ELSE 1 END WHERE 分组 NOT IN (3,4)
|
||
|
||
/*分组1为正常核销，分组2为待核销，分组3,4为特殊核销*/
|
||
SELECT t1.*,{0}
|
||
INTO {1}
|
||
FROM (
|
||
{3}
|
||
{2}
|
||
) t1
|
||
|
||
DROP TABLE #TEMP1,#TEMP2,#TEMP3,#TEMP4
|
||
""",seqFld,tableName,strFilter,tempsql,ZZID,KHID,SKRQS,SKRQE,HXRQS,HXRQE)
|
||
DBUtils.ExecuteDynamicObject(this.Context, sql)
|
||
|
||
#设置汇总信息
|
||
def GetSummaryColumnInfo(Filter):
|
||
lstfield = List[SummaryField]()
|
||
sField1 = SummaryField("金额",Core.Enums.BOSEnums.Enu_SummaryType.SUM)
|
||
sField2 = SummaryField("分摊金额",Core.Enums.BOSEnums.Enu_SummaryType.SUM)
|
||
sField3 = SummaryField("款到发货",Core.Enums.BOSEnums.Enu_SummaryType.SUM)
|
||
sField4 = SummaryField("一至六个月",Core.Enums.BOSEnums.Enu_SummaryType.SUM)
|
||
sField5 = SummaryField("七至十二个月",Core.Enums.BOSEnums.Enu_SummaryType.SUM)
|
||
sField6 = SummaryField("一至二年",Core.Enums.BOSEnums.Enu_SummaryType.SUM)
|
||
sField7 = SummaryField("二至三年",Core.Enums.BOSEnums.Enu_SummaryType.SUM)
|
||
sField8 = SummaryField("三至四年",Core.Enums.BOSEnums.Enu_SummaryType.SUM)
|
||
sField9 = SummaryField("四至五年",Core.Enums.BOSEnums.Enu_SummaryType.SUM)
|
||
sField10 = SummaryField("五年以上",Core.Enums.BOSEnums.Enu_SummaryType.SUM)
|
||
lstfield.Add(sField1)
|
||
lstfield.Add(sField2)
|
||
lstfield.Add(sField3)
|
||
lstfield.Add(sField4)
|
||
lstfield.Add(sField5)
|
||
lstfield.Add(sField6)
|
||
lstfield.Add(sField7)
|
||
lstfield.Add(sField8)
|
||
lstfield.Add(sField9)
|
||
lstfield.Add(sField10)
|
||
return lstfield
|
||
|
||
#排序
|
||
def OrderColumn(Filter):
|
||
OrderBy = ""
|
||
datasort = Filter.FilterParameter.SortString.ToString()
|
||
if datasort != "":
|
||
OrderBy = datasort
|
||
else:
|
||
OrderBy = " 分组,ShouKuanFid,分摊序号,业务日期 "
|
||
return OrderBy
|
||
|
||
#关闭清空临时表
|
||
def CloseReport():
|
||
this.DropTempTable() |