ALTER PROCEDURE .
@region AS nvarchar , -- 业务地区
@personCode AS nvarchar , -- 客户代码
@shopCode AS nvarchar , -- 店铺代码
@person AS nvarchar , -- 业务员
@endDateBalance AS int , -- 结束日期期末余额>=
@personType AS nvarchar , -- 客户性质
@cooperationPattern AS nvarchar , -- 合作模式
@closeDate AS nvarchar , -- 结算周期
@beginDate AS nvarchar , -- 开始日期 *
@endDate AS nvarchar -- 结束日期 *
AS
BEGIN
-- routine body goes here, e.g.
-- SELECT 'Navicat for SQL Server'
--常规
create table #Temp_Oughts --创建临时表#Tmp
(
RegionMaster varchar(MAX), --四级大区
Region varchar(MAX), --五级大区
PersonCode varchar(MAX), --客户代码
ShopCode varchar(MAX), --店铺代码
Account varchar(MAX), --默认贷款账户
TakeDate datetime , --日期
Type varchar(MAX), --类型
Remark varchar(MAX), --说明
ExpenseItemName varchar (MAX), --费用项目
BillNo varchar(MAX) , -- 单据号
SourceType varchar(MAX) , --来源单类型
SourceBillNo varchar(MAX) , -- 来源单号
Counts varchar(MAX) , -- 货品总数
PersonAlias varchar(MAX) , --客户简称
PersonType varchar(MAX) , --客户性质
CooperationPattern varchar(MAX), -- 合作模式
CloseDate varchar(MAX) , -- 结算周期
Person varchar(MAX) , -- 业务员
LinkPerson varchar(MAX), -- 客户联系人
AmountOught Numeric(10,2) -- 本期应收
);
create table #Temp_Readys --创建临时表#Tmp
( RegionMaster varchar(MAX), --四级大区
Region varchar(MAX), --五级大区
PersonCode varchar(MAX), --客户代码
ShopCode varchar(MAX), --店铺代码
Account varchar(MAX), --默认贷款账户
TakeDate datetime , --日期
Type varchar(MAX), --类型
Remark varchar(MAX), --说明
ExpenseItemName varchar (MAX), --费用项目
BillNo varchar(MAX) , -- 单据号
SourceType varchar(MAX) , --来源单类型
SourceBillNo varchar(MAX) , -- 来源单号
Counts varchar(MAX) , -- 货品总数
PersonAlias varchar(MAX) , --客户简称
PersonType varchar(MAX) , --客户性质
CooperationPattern varchar(MAX), -- 合作模式
CloseDate varchar(MAX) , -- 结算周期
Person varchar(MAX) , -- 业务员
LinkPerson varchar(MAX), -- 客户联系人
AmountOught Numeric(10,2) -- 本期应收
);
INSERT into #Temp_Oughts(RegionMaster,Region,PersonCode,ShopCode,Account,TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceType,SourceBillNo,Counts,PersonAlias,PersonType,CooperationPattern,CloseDate,Person,LinkPerson,AmountOught)
select left(right(manerag.OperationAreaFullName,10),5) as headquarters , manerag.OperationAreaName ,manerag.VendCustCode ,manerag.VendCustCode as shopCode,account.BookAccountName , voucher.CheckDate as thisDate,case when convert(nvarchar(10) ,voucher.CheckDate,120) !='' then '应收' end as type , case when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '10' then '第一次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '15' then '第一次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '20' then '第二次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '21' and datepart(dd,cbjm.EndDate) >= '28' then '第三次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '16' and datepart(dd,cbjm.EndDate) >= '28' then '第二次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '1' and datepart(dd,cbjm.EndDate) >= '28' then '第一次'
else voucher.ManualBillNo
end as Remark , expenseItem.ExpenseItemName,voucher.BillNo ,type.BillTypeName, voucher.SourceBillNo
,
(select sum(D_Qty)* -1
from FIRP_Bal_CustomerContactAccount s
left join Sys_BillType billType on billType.BillTypeID = s.SourcebtID
where s.BillNo = voucher.BillNo) as counts
,manerag.ShortName,manerag.CustomerTypeName,manerag.RetailPriceProjName,condition.PaymentConditionName,manerag.PersonnelName ,manerag.LinkMan ,voucher.Amount
from FIRP_ARP_VoucherMaster voucher
left join vwSD_CustomerManerage manerag on voucher.ObjectValue = manerag.VendCustID
left join ( SELECT case when isnull(ExpenseItemName,'')='' then '' else ExpenseItemName end as ExpenseItemName ,fav.BillNo
FROM dbo.FIRP_ARP_VoucherOtherExpense fav
LEFT JOIN Bas_ExpenseItem be ON (be.CompanyID='00000000' or be.CompanyID = fav.CompanyID) AND be.ExpenseItemID = fav.ExpenseItemID and ExpenseItemName is not null and ExpenseItemName <> ''
where fav.CompanyID='YM') expenseItem on expenseItem.BillNo = voucher.BillNo
LEFT JOIN vwFIRP_ARP_PlanDetail detail on detail.BillNo = voucher.BillNo
LEFT JOIN Bas_BookAccount account on account.BookAccountID = detail.BookAccountID and account.CompanyID = 'YM'
LEFT JOIN SD_Sal_CustBalanceJointMaster cbjm on cbjm.BillNo = voucher.SourceBillNo
LEFT JOIN Sys_BillType type on type.BillTypeID = detail.SourcebtID
left join Bas_PaymentCondition condition on condition.PaymentConditionID = manerag.PaymentConditionID and condition.CompanyID = 'YM'
where convert(nvarchar(10) , voucher.CheckDate,120) like '2022-08' + '%'
and manerag.VendCustCode <> '' and manerag.VendCustCode is not null
ORDER BY voucher.CheckDate
INSERT INTO #Temp_Readys (RegionMaster,Region,PersonCode,ShopCode,Account,TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceType,SourceBillNo,Counts,PersonAlias,PersonType,CooperationPattern,CloseDate,Person,LinkPerson,AmountOught)
select left(right(manerag.OperationAreaFullName,10),5) as headquarters , manerag.OperationAreaName ,manerag.VendCustCode ,manerag.VendCustCode as shopCode,account.BookAccountName , voucher.CheckDate as thisDate,case when convert(nvarchar(10) ,voucher.CheckDate,120) !='' then '预收' end as type , case when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '10' then '第一次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '15' then '第一次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '20' then '第二次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '21' and datepart(dd,cbjm.EndDate) >= '28' then '第三次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '16' and datepart(dd,cbjm.EndDate) >= '28' then '第二次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '1' and datepart(dd,cbjm.EndDate) >= '28' then '第一次'
else voucher.ManualBillNo
end as Remark , expenseItem.ExpenseItemName,voucher.BillNo ,type.BillTypeName, voucher.SourceBillNo
,
(select sum(D_Qty)* -1
from FIRP_Bal_CustomerContactAccount s
left join Sys_BillType billType on billType.BillTypeID = s.SourcebtID
where s.BillNo = voucher.BillNo) as counts
,manerag.ShortName,manerag.CustomerTypeName,manerag.RetailPriceProjName,condition.PaymentConditionName,manerag.PersonnelName ,manerag.LinkMan ,voucher.Amount
from FIRP_Pre_RPVoucherMaster voucher
left join vwSD_CustomerManerage manerag on voucher.ObjectValue = manerag.VendCustID
left join ( SELECT case when isnull(ExpenseItemName,'')='' then '' else ExpenseItemName end as ExpenseItemName ,fav.BillNo
FROM dbo.FIRP_ARP_VoucherOtherExpense fav
LEFT JOIN Bas_ExpenseItem be ON (be.CompanyID='00000000' or be.CompanyID = fav.CompanyID) AND be.ExpenseItemID = fav.ExpenseItemID and ExpenseItemName is not null and ExpenseItemName <> ''
where fav.CompanyID='YM') expenseItem on expenseItem.BillNo = voucher.BillNo
LEFT JOIN vwFIRP_ARP_PlanDetail detail on detail.BillNo = voucher.BillNo
LEFT JOIN Bas_BookAccount account on account.BookAccountID = detail.BookAccountID and account.CompanyID = 'YM'
LEFT JOIN SD_Sal_CustBalanceJointMaster cbjm on cbjm.BillNo = voucher.SourceBillNo
LEFT JOIN Sys_BillType type on type.BillTypeID = detail.SourcebtID
left join Bas_PaymentCondition condition on condition.PaymentConditionID = manerag.PaymentConditionID and condition.CompanyID = 'YM'
where convert(nvarchar(10) , voucher.CheckDate,120) like '2022-08' + '%'
and manerag.VendCustCode <> '' and manerag.VendCustCode is not null
ORDER BY voucher.CheckDate
SELECT * INTO #end_Temp FROM
(
SELECT DISTINCT RegionMaster,Region,PersonCode,ShopCode,Account,TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceType,SourceBillNo,Counts,PersonAlias,PersonType,CooperationPattern,CloseDate,Person,LinkPerson,AmountOught,CONVERT(decimal(18,2),0) SHOURU ,AmountOught AS HEJI FROM #Temp_Oughts
UNION ALL
SELECT DISTINCT RegionMaster,Region,PersonCode,ShopCode,Account,TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceType,SourceBillNo,Counts,PersonAlias,PersonType,CooperationPattern,CloseDate,Person,LinkPerson,00, AmountOught,-AmountOught AS HEJI FROM #Temp_Readys
) T
--select * FROM #end_Temp order by shopCode,TakeDate
-- 计算期初数据 。查询没个客户的余额，插入至masterTempTable中，分组完成
SELECT e.CompanyID, b.OperationAreaCode AS AreaCode1, b.OperationAreaName AS AreaName1,
c.OperationAreaCode AS AreaCode2, c.OperationAreaName AS AreaName2, d.OperationAreaCode AS OuterAreaCode3,
d.OperationAreaName AS AreaName3,
case when f.OperationAreaName =d.OperationAreaName then null else
f.OperationAreaName end AS AreaName4,
e.VendCustCode, e.VendCustID, e.VendCustName, e.ProAllowUsed,
a.fullName
into #Cust_OperationArea
FROM (SELECT CompanyID, OperationAreaID, dbo.fn_SD_GetStringByIndex(ISNULL(FullParentID, ''), '.', 1) AS AreaID,
dbo.fn_SD_GetStringByIndex(ISNULL(FullParentID, ''), '.', 2) AS provinceID,
dbo.fn_SD_GetStringByIndex(ISNULL(FullParentID, ''), '.', 3) AS CityID,
dbo.fn_SD_GetStringByIndex(ISNULL(fullName, ''), '.', 4) AS fullName
FROM dbo.Bas_OperationArea) AS a LEFT OUTER JOIN
dbo.Bas_OperationArea AS b ON a.CompanyID = b.CompanyID AND a.AreaID = b.OperationAreaID LEFT OUTER JOIN
dbo.Bas_OperationArea AS c ON a.CompanyID = c.CompanyID AND a.provinceID = c.OperationAreaID LEFT OUTER JOIN
dbo.Bas_OperationArea AS d ON a.CompanyID = d.CompanyID AND a.CityID = d.OperationAreaID RIGHT OUTER JOIN
Bas_InterCompanyParam AS ve ON a.OperationAreaID =ve.OperationAreaID
Left Join Bas_InterCompany as e On ve.VendCustID = e.VendCustID
left join dbo.Bas_OperationArea f on f.OperationAreaID =ve.OperationAreaID
where
-- e.VendCustType ='1' and.
VE.CompanyID ='YM'
select CheckDate,Period,VendCustCode,VendCustName,AreaName4 as AreaName4 ,AreaName2 as AreaName2,fullName as fullName ,billno,ExpenseItemName,Amount,CurrencyName
into #FIRP_ARP_Voucher
from (
select distinct m.CheckDate, LEFT ( CONVERT(varchar(100), m.CheckDate, 112),6) as period, ve.VendCustCode, ve.VendCustName, ar.AreaName4 ,ar.AreaName2,ar.fullName, d.* from FIRP_Arp_VoucherMaster m
left join Bas_InterCompany ve on ve.VendCustID =m.ObjectValue
left join #Cust_OperationArea ar on ar.VendCustID =ve.VendCustID
left join (
--应收凭单-其它应收明细
select m.BillNo, be.ExpenseItemName,sum (m.Amount) Amount,cu.CurrencyName
from dbo.FIRP_ARP_VoucherOtherExpense m
--left join Bas_InterCompany ve on ve.VendCustID =m.ObjectValue
left join Bas_Currency cu on cu.CurrencyID =m.CurrencyID
LEFT JOIN Bas_ExpenseItem be ON be.CompanyID = m.CompanyID AND be.ExpenseItemID = m.ExpenseItemID
group by m.BillNo, be.ExpenseItemName,cu.CurrencyName
union all
--应收凭单-明细
select
d.billno,'结算款' ExpenseItemName
,sum (d.amount) Amount,cu.CurrencyName
from
FIRP_Arp_VoucherDetail d
left join Bas_Currency cu on cu.CurrencyID =d.CurrencyID
left join Sys_BillType st on st.BillTypeID =d.SourcebtID
group by d.billno,cu.CurrencyName
) d on d.BillNo =m.BillNo
where m.BillTypeID ='Fin_RecVoucher' AND M.BillStatus ='4' and m.ObjectType ='1'
union all
--预付凭单
select m.CheckDate,LEFT ( CONVERT(varchar(100), m.CheckDate, 112),6) as period ,ve.VendCustCode, ve.VendCustName,
--case when ar.AreaName4 is not null then ar.AreaName4 else sh.AreaName4 end AreaName4
AR.AreaName4,AR.AreaName2,AR.fullName
,m.BillNo,'汇款' BookAccountTypes,Amount*(-1) as Amount,cu.CurrencyName
from FIRP_Pre_RPVoucherMaster m
--left join vwFI_BookAccountTypes bt on bt.BillNo =m.BillNo
left join Bas_InterCompany ve on ve.VendCustID =m.ObjectValue
left join #Cust_OperationArea ar on ar.VendCustID =ve.VendCustID
left join Bas_Currency cu on cu.CurrencyID =m.CurrencyID
where BillTypeID ='Fin_PreRecVoucher' AND M.BillStatus ='4' and m.ObjectType ='1'
) as FIRP
select VendCustCode,VendCustName,AreaName2,AreaName4,fullName,'期初余额'ExpenseItemName,CurrencyName ,sum (vars.Amount) Amount -- ,account.BookAccountName
into #result
from #FIRP_ARP_Voucher vars
-- LEFT JOIN vwFIRP_ARP_PlanDetail detail on detail.BillNo = vars.BillNo
-- LEFT JOIN Bas_BookAccount account on account.BookAccountID = detail.BookAccountID and account.CompanyID = 'YM'
where
(CheckDate < '2022-08-01' )
--AND (VendCustCode = @VendCustCode Or ISNULL(@VendCustCode,'') = '')
group by VendCustCode,VendCustName,AreaName4,AreaName2, CurrencyName ,fullName--,account.BookAccountName
INSERT into #end_Temp(RegionMaster,Region,PersonCode,ShopCode,Account,TakeDate,Type,PersonAlias,PersonType,CooperationPattern,CloseDate,Person,LinkPerson,HEJI)
select results.fullName,results.AreaName4,results.VendCustCode,results.VendCustCode,'默认货款账户','2022-08-01','期初',manerag.ShortName,manerag.CustomerTypeName,manerag.RetailPriceProjName,condition.PaymentConditionName,manerag.PersonnelName ,manerag.LinkMan,results.Amount from #result results
left join vwSD_CustomerManerage manerag on results.VendCustCode = manerag.VendCustCode
left join Bas_PaymentCondition condition on condition.PaymentConditionID = manerag.PaymentConditionID and condition.CompanyID = 'YM'
where results.fullName not like '%停用%' and results.AreaName4 not like '%停用%'
SELECT *,SUM(HEJI)OVER(PARTITION BY shopCode ORDER BY TakeDate) AS YUE FROM #end_Temp
END
