SQLSERVER 备份每天跑的报表-优快云博客

1.insert
insert into DataMonthBalanceTable select a.*,getdate() as date
from MonthBalanceTable a where (coalesce(a.ttt_end_item,0)+coalesce(a.YAMOUNT_FINAL,0)+ coalesce(a.OTHERAMOUNT_FINAL,0) +
coalesce(a.LENDAMOUNT_FINAL,0) +coalesce(a.STOCKRETURNAMOUNT_FINAL,0))<>0

2.dropmax
DROP TABLE MAXINCODETAXPRICE

3.insertmax
SELECT * INTO MAXINCODETAXPRICE FROM (
SELECT     s.CONTRACTDTLID, s.incode, s1.TAXPRICE,s1.totalmoney
FROM         (SELECT     d.CONTRACTDTLID, MAX(h.INCODE) AS incode
                       FROM          dbo.D_SINGLESALEORDER AS h INNER JOIN
                                              dbo.D_SINGLESALEORDER_DTL AS d ON h.id = d.D_TKTID
                       WHERE      (h.deleted = 0) AND (d.deleted = 0)
                       GROUP BY d.CONTRACTDTLID) AS s INNER JOIN
                          (SELECT     h.totalmoney,h.INCODE, d.CONTRACTDTLID, d.TAXPRICE
                            FROM          dbo.D_SINGLESALEORDER AS h INNER JOIN
                                                   dbo.D_SINGLESALEORDER_DTL AS d ON h.id = d.D_TKTID) AS s1 ON s.CONTRACTDTLID = s1.CONTRACTDTLID AND
                      s.incode = s1.INCODE

UNION ALL

SELECT     s.CONTRACTDTLID, s.incode, s1.TAXPRICE,s1.totalmoney
FROM         (SELECT     d.CONTRACTDTLID, MAX(h.INCODE) AS incode
                       FROM          dbo.D_SALEORDER AS h INNER JOIN
                                              dbo.D_SALEORDER_DTL AS d ON h.id = d.D_TKTID
                       WHERE      (h.deleted = 0) AND (d.deleted = 0)
                       GROUP BY d.CONTRACTDTLID) AS s INNER JOIN
                          (SELECT     h.totalmoney,h.INCODE, d.CONTRACTDTLID, d.TAXPRICE
                            FROM          dbo.D_SALEORDER AS h INNER JOIN
                                                   dbo.D_SALEORDER_DTL AS d ON h.id = d.D_TKTID) AS s1 ON s.CONTRACTDTLID = s1.CONTRACTDTLID AND s.incode = s1.INCODE
) AS S

4.INSERT LENDOUT

INSERT INTO DATELENDOUT
select getdate() as date,b.id,a.incode,DATEADD(dd, 0, DATEDIFF(dd, 0, A.TICKETTIME)) AS tickettime,
DATEADD(dd, 0, DATEDIFF(dd, 0, A.checktime)) AS checktime,
c.name as P_CLERKNAME3,d.name as P_CLERKNAME, e.code as itemcode,e.name as itemname,
f.code as locationcode,f.name as locationname, g.code as housecode,g.name as housename,
b.SHOULDAMOUNT as outshould,b.SHOULDAMOUNT*b.costprice as outmoney,sum(COALESCE(h.SHOULDAMOUNT,0)) as inshould,
sum(COALESCE(h.SHOULDAMOUNT*h.costprice,0)) as inmoney,
(b.saleamount) as saleamount,(b.saleamount*b.costprice) as salemeony,
(b.shouldamount-sum(COALESCE(h.SHOULDAMOUNT,0))-COALESCE(b.saleamount,0)) as saleorderamount ,
(b.shouldamount*b.costprice-sum(COALESCE(h.SHOULDAMOUNT*h.costprice,0))-COALESCE(b.saleamount*b.costprice,0)) as saleordermoney

from ST_LEND_OUT a inner join ST_LENDOUT_DTL b on a.id=b.D_TKTID
left join CP_DEPARTMENT c on c.id=a.P_CLERKID3
left join CP_EMPLOYEE d on d.id=a.P_CLERKID
left join P_item e on e.id=b.P_ITEMID
left join CP_LOCATION f on f.id=b.P_STOCKID
left join cp_warehouse g on g.id=b.WAREHOUSEID
left join ST_LEND_IN_DTL h on h.D_TKT_DTLID2=b.id and h.deleted=0
where a.deleted=0 and a.billstatus=5
group by b.id,a.incode,a.tickettime,c.name ,d.name , e.code ,e.name ,f.code ,f.name ,
g.code ,g.name ,b.SHOULDAMOUNT,b.saleamount ,A.checktime,b.costprice

5.INSERT PAYABLEBALANCE

insert into DATEPAYABLEBALANCE
select * from
(select getdate() as date,ISNULL(b.notes,'') as notes,b.month,a.incode,
DATEADD(dd, 0, DATEDIFF(dd, 0, b.TICKETTIME)) as tickettime,
DATEADD(dd, 0, DATEDIFF(dd, 0, b.checktime)) as checktime,
d.code as dealercode,d.name as dealername ,c.code as itemcode,
c.name as itemname,cast(bdtl.shouldamount as decimal(18,0)) as shouldamount,
bdtl.taxprice,coalesce(a.TAXMONEY_DEBIT,0) as TAXMONEY_DEBIT,
coalesce(a.TAXMONEY_CREDIT,0) as TAXMONEY_CREDIT,
coalesce(a.YCHECKMONEY_FINAL,0) as YCHECKMONEY_FINAL,
coalesce(a.PAYMONEY_FINAL,0) as PAYMONEY_FINAL,
coalesce(a.RETURNMONEY_FINAL,0) as RETURNMONEY_FINAL,
coalesce(a.CHECKMONEY_FINAL,0) as CHECKMONEY_FINAL,
coalesce(bd.BFHAVEHOOKMONEY,0) as BFHAVEHOOKMONEY,
coalesce(bd.REHAVEHOOKMONEY,0) as REHAVEHOOKMONEY,
coalesce(bd.STOCKRETURNHOOKMONEY,0) as STOCKRETURNHOOKMONEY,
case when bdtl.taxprice <>0
then cast((coalesce(a.TAXMONEY_DEBIT,0) - coalesce(a.PAYMONEY_FINAL,0)
-coalesce(a.RETURNMONEY_FINAL,0)-coalesce(a.YCHECKMONEY_FINAL,0)
-coalesce(a.CHECKMONEY_FINAL,0))/bdtl.taxprice as decimal(18,0))
else 0 end as wfAmount,
(coalesce(a.TAXMONEY_final,0) - coalesce(a.PAYMONEY_FINAL,0)+
coalesce(a.RETURNMONEY_FINAL,0)-coalesce(a.YCHECKMONEY_FINAL,0)
-coalesce(a.CHECKMONEY_FINAL,0)) as havenotpay,
coalesce(a.STOCKRETURNHOOKMONEY_FINAL,0) as tuihuodikou ,a.DIXIAOLEIXING as DIXIAOLEIXING
from PAYABLEBALANCE a
inner join d_stockin b on a.incode = b.incode
inner join d_stockin_dtl bdtl on bdtl.id= a.dtlid
left join p_item c on a.p_itemid = c.id
left join cp_dealer d on a.p_dealerid = d.id
left join (select b.d_tkt_dtlid2 as id,sum(b.BFHAVEHOOKMONEY) as BFHAVEHOOKMONEY,
sum(b.REHAVEHOOKMONEY) as REHAVEHOOKMONEY,
sum(b.STOCKRETURNHOOKMONEY) as STOCKRETURNHOOKMONEY
from bd_outpay a
inner join bd_outpaydetail b on a.id = b.d_tktid
where a.deleted = 0 and b.deleted = 0
group by b.d_tkt_dtlid2) as bd on bd.id = bdtl.id
where b.checktime >= '2009-05-01' and b.deleted=0 and b.billstatus=5
and bdtl.deleted=0 and a.deleted=0 and (a.dixiaobatch is null or
a.dixiaobatch='1' )
union all select getdate() as date,'' as notes,
'' as month,'' as incode,DATEADD(dd, 0, DATEDIFF(dd, 0, a.TICKETTIME)) as tickettime,
DATEADD(dd, 0, DATEDIFF(dd, 0, a.checktime)) as checktime,
d.code as dealercode,d.name as dealername,'' as itemcode,
'' as itemname,0 as shouldamount,0 as taxprice,
coalesce(a.应付额,0) as TAXMONEY_DEBIT,0 AS TAXMONEY_CREDIT,
coalesce(bb.YCHECKMONEY_FINAL,0) as YCHECKMONEY_FINAL,
coalesce(bb.PAYMONEY_FINAL,0) as PAYMONEY_FINAL,0 as RETURNMONEY_FINAL,
coalesce(bb.CHECKMONEY_FINAL,0) as CHECKMONEY_FINAL,0 as BFHAVEHOOKMONEY,
0 as REHAVEHOOKMONEY,0 as STOCKRETURNHOOKMONEY,0 as wfAmount,
(coalesce(a.应付额,0) - coalesce(bb.PAYMONEY_FINAL,0)-coalesce(bb.YCHECKMONEY_FINAL,0)
-coalesce(bb.CHECKMONEY_FINAL,0)) as havenotpay, coalesce(bb.tuihuodikou,0)
as tuihuodikou,'' as DIXIAOLEIXING from yingfudaoru a left join cp_dealer d on d.code = a.供应商代码
left join (select a.p_dealerid as dealerid,
coalesce(sum(b.TOTALMoney),0) as PAYMONEY_FINAL,
coalesce(sum(b.BFHAVEHOOKMONEY),0) as YCHECKMONEY_FINAL,
coalesce(sum(b.REHAVEHOOKMONEY),0) as CHECKMONEY_FINAL,
coalesce(sum(b.STOCKRETURNHOOKMONEY),0) as tuihuodikou
from bd_outppay a left join bd_outppaydetail b on a.id = b.d_tktid
where a.deleted = 0 and b.deleted = 0 and a.billstatus=5 and
a.notes like '%期初%'group by a.p_dealerid ) as bb on bb.dealerid = d.id )
as s where ( DEALERNAME != '1+1' ) order by incode,dealercode

6.drop RESTRICTPAY

drop table RESTRICTPAY

7.insert RESTRICTPAY

select s0.PAYDEPARTMENTNAME ,s0.totalmoney-s1.havepaymoney-s2.returntotalmoney+s3.returntotalmoney AS YINGFU
INTO RESTRICTPAY from (
select sum(d.newshouldamount*d.taxprice) as totalmoney,PAYDEPARTMENTNAME from d_singlesaleorder h
inner join d_singlesaleorder_dtl d
on h.id=d.d_tktid
where h.billstatus=5 and h.deleted=0 and d.deleted=0 and h.invoicecode>'LSD200906010000' group by PAYDEPARTMENTNAME )
as s0
left join (
select sum(d.havepaymoney) as havepaymoney,h.PAYDEPARTMENTNAME from d_singlesaleorder h
inner join d_singlesaleorder_inpay d
on h.id=d.d_tktid
where h.billstatus=5 and h.deleted=0 and d.deleted=0 and h.invoicecode>'LSD200906010000' group by h.PAYDEPARTMENTNAME
) s1 on s0.PAYDEPARTMENTNAME=s1.PAYDEPARTMENTNAME
left join (
select sum(d.shouldamount*d.taxprice) as returntotalmoney,h1.PAYDEPARTMENTNAME
from d_singlesalereturn h
inner join d_singlesalereturn_dtl d
on h.id=d.d_tktid
left join d_singlesaleorder h1 on h1.incode=h.sourcecode
where h.billstatus=5 and h.deleted=0 and d.deleted=0 and h.invoicecode>'LSD200906010000' group by h1.PAYDEPARTMENTNAME
) s2 on s2.PAYDEPARTMENTNAME=s0.PAYDEPARTMENTNAME
left join (
select sum(havepaymoney) as returntotalmoney,h1.PAYDEPARTMENTNAME
from d_singlesalereturn h
inner join d_singlesalereturn_money d
on h.id=d.d_tktid
left join d_singlesaleorder h1 on h1.incode=h.sourcecode
where h.billstatus=5 and h.deleted=0 and d.deleted=0 and h.invoicecode>'LSD200906010000' group by h1.PAYDEPARTMENTNAME
) s3 on s3.PAYDEPARTMENTNAME=s0.PAYDEPARTMENTNAME

8.drop yinshouxianzhi

drop table yinshouxianzhi

9.insert yinshouxianzhi

select * into yinshouxianzhi from (
select id,code,name,isnull(helpcode,0) as ysxz,isnull(helpcode,0) -(s1.totalmoney -
s2.havepaymoney -s3.returntotalmoney +s4.returntotalmoney ) as syys
from cp_department s0
left join (select isnull(sum(d.newshouldamount*d.taxprice),0) as totalmoney,
vdp.parentname ,vdp.parentid
from d_singlesaleorder h
inner join d_singlesaleorder_dtl d
on h.id=d.d_tktid
left join dbo.VIEW_PDEPARTMENT2 vdp on vdp.id=h.P_CLERKID3
where h.billstatus=5 and h.deleted=0 and d.deleted=0 and h.invoicecode>'LSD200906010000'
and vdp.parentname is not null
group by vdp.parentname,vdp.parentid) as s1
on s1.parentid=s0.id
left join (
select isnull(sum(d.havepaymoney),0) as havepaymoney,
vdp.parentname ,vdp.parentid from d_singlesaleorder h
inner join d_singlesaleorder_inpay d
on h.id=d.d_tktid
left join dbo.VIEW_PDEPARTMENT2 vdp on vdp.id=h.P_CLERKID3
where h.billstatus=5 and h.deleted=0 and d.deleted=0 and h.invoicecode>'LSD200906010000'
and vdp.parentname is not null
group by vdp.parentname,vdp.parentid) as s2
on s2.parentid=s0.id
left join (select isnull(sum(d.shouldamount*d.taxprice),0) as returntotalmoney,
vdp.parentname ,vdp.parentid
from d_singlesalereturn h
inner join d_singlesalereturn_dtl d
on h.id=d.d_tktid
left join dbo.VIEW_PDEPARTMENT2 vdp on vdp.id=h.P_CLERKID3
where h.billstatus=5 and h.deleted=0 and d.deleted=0 and h.invoicecode>'LSD200906010000'
and vdp.parentname is not null
group by vdp.parentname ,vdp.parentid) as s3
on s3.parentid=s0.id
left join (select isnull(sum(havepaymoney),0) as returntotalmoney,vdp.parentname ,vdp.parentid
from d_singlesalereturn h
inner join d_singlesalereturn_money d
on h.id=d.d_tktid
left join dbo.VIEW_PDEPARTMENT2 vdp on vdp.id=h.P_CLERKID3
where h.billstatus=5 and h.deleted=0 and d.deleted=0 and h.invoicecode>'LSD200906010000'
and vdp.parentname is not null
group by vdp.parentname,vdp.parentid) as s4
on s4.parentid=s0.id
where s0.id in(select parentid from view_pdepartment2)) as t

10.update saleoutdetail

update a set a.costmoney=a.newshouldamount*b.taxprice,a.maoli=a.taxmoney-a.costmoney
from REPORT_SALEOUTDETAIL a left join D_STOCKIN_DTL b on a.batch=b.batch
where b.MTime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120)

11.delete_reportsaleoutdetail

delete from report_saleoutdetail where incode in(
select www.incode FROM (
SELECT      h.INCODE
FROM         dbo.D_SINGLESALEORDER AS h INNER JOIN
                      dbo.D_SINGLESALEORDER_DTL AS d ON h.id = d.D_TKTID
WHERE     (h.deleted = 0) AND (h.BILLSTATUS = 5) AND (d.deleted = 0) AND (d.LENDOUTINCODE IS NOT NULL)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
UNION ALL
SELECT      h.INCODE
FROM         dbo.D_SALEORDER AS h INNER JOIN
                      dbo.D_SALEORDER_DTL AS d ON h.id = d.D_TKTID
WHERE     (h.deleted = 0) AND (d.deleted = 0) AND (d.LENDOUTINCODE IS NOT NULL) AND (h.BILLSTATUS = 5)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
UNION ALL
SELECT      h.INCODE
FROM         dbo.D_SINGLESALEOUT AS h INNER JOIN
                      dbo.D_SINGLESALEOUT_DTL AS d ON h.id = d.D_TKTID
WHERE     (h.deleted = 0) AND (h.BILLSTATUS = 5) AND (d.deleted = 0)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
UNION ALL
SELECT      h.INCODE
FROM         dbo.D_SALEOUT AS h INNER JOIN
                      dbo.D_SALEOUT_DTL AS d ON h.id = d.D_TKTID
WHERE     (h.deleted = 0) AND (d.deleted = 0) AND (h.BILLSTATUS = 5)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
UNION ALL
SELECT      h.INCODE
FROM         dbo.D_SINGLESALERETURN AS h INNER JOIN
                      dbo.D_SINGLESALERETURN_DTL AS d ON h.id = d.D_TKTID
WHERE     (h.deleted = 0) AND (h.BILLSTATUS = 5) AND (d.deleted = 0) AND (d.RETURNAMOUNT > 0)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
UNION ALL
SELECT     h.INCODE
FROM         dbo.D_SALERETURN AS h INNER JOIN
                      dbo.D_SALERETURN_DTL AS d ON h.id = d.D_TKTID
WHERE     (h.deleted = 0) AND (h.BILLSTATUS = 5) AND (d.deleted = 0) AND (d.RETURNAMOUNT > 0)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
) as www)

12.insert saleout

insert INTO REPORT_SALEOUTDETAIL select * FROM (
SELECT     h.INVOICECODE AS 合同号码, h.INCODE, d.LENDOUTINCODE, h.TICKETTIME, h.TICKETTIME AS ordertime, PD.NAME AS DEARLERNAME, d.P_ITEMID,
                      pi.CODE AS p_itemcode, pi.NAME AS p_itemname, pi.ITEMKINDNAME AS itemkindname, pi.ITEMSUPLYNAME, pi.KINDDTLTAI, pi.DESCRIPTION,
                      '零售' AS 零售或直销, cdp_1.parname AS 区域, cdp_1.Name AS 部门, vpn.parentname AS 上级部门, emp.Name AS 业务员, d.WAREHOUSEID,
                      cw.CODE AS warehousecode, cw.NAME AS warehousename, d.BATCH, d.NEWSHOULDAMOUNT, CAST(d.NEWSHOULDAMOUNT AS decimal(10, 0))
                      * d.TAXPRICE AS taxmoney, 0 AS havepaymoeny, d.NEWSHOULDAMOUNT * d.COSTPRICE AS costmoney,
                      CAST(d.NEWSHOULDAMOUNT AS decimal(10, 0)) * d.TAXPRICE - d.NEWSHOULDAMOUNT * d.COSTPRICE AS maoli, '零售借库' AS outtype,
                      '' AS jiangetime, h.CHECKTIME AS 审核日期, h.totalmoney AS 总金额,d.Sellprice as Sellprice,h.notes as notes,d.Jiangli,d.houfan
FROM         dbo.D_SINGLESALEORDER AS h INNER JOIN
                      dbo.D_SINGLESALEORDER_DTL AS d ON h.id = d.D_TKTID LEFT OUTER JOIN
                      dbo.p_item AS pi ON pi.id = d.P_ITEMID LEFT OUTER JOIN
                      dbo.ST_LEND_OUT AS so ON so.INCODE = d.LENDOUTINCODE LEFT OUTER JOIN
                      dbo.CP_DEALER AS PD ON PD.id = h.P_DEALERID LEFT OUTER JOIN
                      dbo.cp_warehouse AS cw ON cw.id = d.WAREHOUSEID LEFT OUTER JOIN
                          (SELECT     cp1.id, cp1.Code, cp1.Name, cp2.Name AS parname, cp1.PARENTID
                            FROM          dbo.CP_DEPARTMENT AS cp1 LEFT OUTER JOIN
                                                   dbo.CP_DEPARTMENT AS cp2 ON cp2.id = cp1.PARENTID) AS cdp_1 ON cdp_1.id = h.P_CLERKID3 LEFT OUTER JOIN
                      dbo.CP_EMPLOYEE AS emp ON emp.id = h.P_CLERKID LEFT OUTER JOIN

                      dbo.VIEW_PDEPARTMENT2 AS vpn ON vpn.id = h.P_CLERKID3
WHERE     (h.deleted = 0) AND (h.BILLSTATUS = 5) AND (d.deleted = 0) AND (d.LENDOUTINCODE IS NOT NULL)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
UNION ALL
SELECT     h.INVOICECODE AS 合同号码, h.INCODE, d.LENDOUTINCODE, h.TICKETTIME, h.TICKETTIME AS ordertime, PD.NAME AS DEALERNAME, d.P_ITEMID,
                      pi.CODE AS p_itemcode, pi.NAME AS p_itemname, pi.ITEMKINDNAME AS itemkindname, pi.ITEMSUPLYNAME, pi.KINDDTLTAI, pi.DESCRIPTION,
                      '直销' AS 零售或直销, cdp.parname AS 区域, cdp.Name AS 部门, vpn.parentname AS 上级部门, emp.Name AS 业务员, d.WAREHOUSEID,
                      cw.CODE AS warehousecode, cw.NAME AS warehousename, d.BATCH, d.NEWSHOULDAMOUNT, CAST(d.NEWSHOULDAMOUNT AS decimal(10, 0))
                      * d.TAXPRICE AS taxmoney, 0 AS havepaymoney, d.NEWSHOULDAMOUNT * d.COSTPRICE AS costmoney,
                      CAST(d.NEWSHOULDAMOUNT AS decimal(10, 0)) * d.TAXPRICE - d.NEWSHOULDAMOUNT * d.COSTPRICE AS maoli, '直销借库' AS outtype,
                      CASE WHEN h.RECEIVEMONEYDATE IS NULL THEN 0 ELSE Datediff(day, h.tickettime, h.RECEIVEMONEYDATE) END AS jiangetime,
                      h.CHECKTIME AS 审核日期, h.totalmoney AS 总金额,d.Sellprice as Sellprice,h.notes as notes,d.Jiangli ,0 as houfan
FROM         dbo.D_SALEORDER AS h INNER JOIN
                      dbo.D_SALEORDER_DTL AS d ON h.id = d.D_TKTID LEFT OUTER JOIN
                      dbo.ST_LEND_OUT AS so ON so.INCODE = d.LENDOUTINCODE LEFT OUTER JOIN
                      dbo.p_item AS pi ON pi.id = d.P_ITEMID LEFT OUTER JOIN
                      dbo.CP_DEALER AS PD ON PD.id = h.P_DEALERID LEFT OUTER JOIN
                      dbo.cp_warehouse AS cw ON cw.id = d.WAREHOUSEID LEFT OUTER JOIN
                          (SELECT     cp1.id, cp1.Code, cp1.Name, cp2.Name AS parname, cp1.PARENTID
                            FROM          dbo.CP_DEPARTMENT AS cp1 LEFT OUTER JOIN
                                                   dbo.CP_DEPARTMENT AS cp2 ON cp2.id = cp1.PARENTID) AS cdp ON cdp.id = h.P_CLERKID3 LEFT OUTER JOIN
                      dbo.CP_EMPLOYEE AS emp ON emp.id = h.P_CLERKID LEFT OUTER JOIN

                      dbo.VIEW_PDEPARTMENT2 AS vpn ON vpn.id = h.P_CLERKID3
WHERE     (h.deleted = 0) AND (d.deleted = 0) AND (d.LENDOUTINCODE IS NOT NULL) AND (h.BILLSTATUS = 5)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
UNION ALL
SELECT     h.INVOICECODE AS 合同号码, h.INCODE, '' AS LENDOUTINCODE, h.TICKETTIME, H1.TICKETTIME AS ordertime, PD.NAME AS DEALERNAME,
                      d.P_ITEMID, pi.CODE AS p_itemcode, pi.NAME AS p_itemname, pi.ITEMKINDNAME AS itemkindname, pi.ITEMSUPLYNAME, pi.KINDDTLTAI,
                      pi.DESCRIPTION, '零售' AS 零售或直销, cdp_5.parname AS 区域, cdp_5.Name AS 部门, vpn.parentname AS 上级部门, emp.Name AS 业务员,
                      d.P_WAREHOUSEID, cw.CODE AS warehousecode, cw.NAME AS warehousename, d.BATCH, d.SHOULDAMOUNT AS newshouldamount,
                      CAST(d.SHOULDAMOUNT AS decimal(10, 0)) * d.TAXPRICE AS taxmoney, 0 AS havepaymoney, d.SHOULDAMOUNT * d.COSTPRICE AS costmoney,
                      CAST(d.SHOULDAMOUNT AS decimal(10, 0)) * d.TAXPRICE - d.SHOULDAMOUNT * d.COSTPRICE AS maoli, '零售出库库' AS outtype, '' AS jiangetime,
                      h.CHECKTIME AS 审核日期, H1.totalmoney AS 总金额,d.Sellprice as Sellprice ,h.notes as notes,d.Jiangli ,d.houfan
FROM         dbo.D_SINGLESALEOUT AS h INNER JOIN
                      dbo.D_SINGLESALEOUT_DTL AS d ON h.id = d.D_TKTID LEFT OUTER JOIN
                      dbo.D_SINGLESALEORDER AS H1 ON d.SOURCECODE = H1.INCODE LEFT OUTER JOIN
                      dbo.p_item AS pi ON pi.id = d.P_ITEMID LEFT OUTER JOIN
                      dbo.CP_DEALER AS PD ON PD.id = h.P_DEALERID LEFT OUTER JOIN
                      dbo.cp_warehouse AS cw ON cw.id = d.P_WAREHOUSEID LEFT OUTER JOIN
                          (SELECT     cp1.id, cp1.Code, cp1.Name, cp2.Name AS parname, cp1.PARENTID
                            FROM          dbo.CP_DEPARTMENT AS cp1 LEFT OUTER JOIN
                                                   dbo.CP_DEPARTMENT AS cp2 ON cp2.id = cp1.PARENTID) AS cdp_5 ON cdp_5.id = h.P_CLERKID3 LEFT OUTER JOIN
                      dbo.CP_EMPLOYEE AS emp ON emp.id = h.P_CLERKID LEFT OUTER JOIN

                      dbo.VIEW_PDEPARTMENT2 AS vpn ON vpn.id = h.P_CLERKID3
WHERE     (h.deleted = 0) AND (h.BILLSTATUS = 5) AND (d.deleted = 0)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
UNION ALL
SELECT     h.INVOICECODE AS 合同号码, h.INCODE, '' AS LENDOUTINCODE, h.TICKETTIME, H1.TICKETTIME AS ordertime, PD.NAME AS DEALERNAME,
                      d.P_ITEMID, pi.CODE AS p_itemcode, pi.NAME AS p_itemname, pi.ITEMKINDNAME AS itemkindname, pi.ITEMSUPLYNAME, pi.KINDDTLTAI,
                      pi.DESCRIPTION, '直销' AS 零售或直销, cdp_4.parname AS 区域, cdp_4.Name AS 部门, vpn.parentname AS 上级部门, emp.Name AS 业务员,
                      d.P_WAREHOUSEID, cw.CODE AS warehousecode, cw.NAME AS warehousename, d.BATCH, d.SHOULDAMOUNT AS newshouldamount,
                      CAST(d.SHOULDAMOUNT AS decimal(10, 0)) * d.TAXPRICE AS taxmoney, 0 AS havepaymoney, d.SHOULDAMOUNT * d.COSTPRICE AS costmoney,
                      d.SHOULDAMOUNT * d.TAXPRICE - d.SHOULDAMOUNT * d.COSTPRICE AS maoli, '直销出库库' AS outtype, '' AS jiangetime,
                      h.CHECKTIME AS 审核日期, H1.totalmoney AS 总金额,d.Sellprice as Sellprice,h.notes as notes,d.Jiangli,0 as houfan
FROM         dbo.D_SALEOUT AS h INNER JOIN
                      dbo.D_SALEOUT_DTL AS d ON h.id = d.D_TKTID LEFT OUTER JOIN
                      dbo.D_SALEORDER AS H1 ON d.SOURCECODE = H1.INCODE LEFT OUTER JOIN
                      dbo.p_item AS pi ON pi.id = d.P_ITEMID LEFT OUTER JOIN
                      dbo.CP_DEALER AS PD ON PD.id = h.P_DEALERID LEFT OUTER JOIN
                      dbo.cp_warehouse AS cw ON cw.id = d.P_WAREHOUSEID LEFT OUTER JOIN
                          (SELECT     cp1.id, cp1.Code, cp1.Name, cp2.Name AS parname, cp1.PARENTID
                            FROM          dbo.CP_DEPARTMENT AS cp1 LEFT OUTER JOIN
                                                   dbo.CP_DEPARTMENT AS cp2 ON cp2.id = cp1.PARENTID) AS cdp_4 ON cdp_4.id = h.P_CLERKID3 LEFT OUTER JOIN
                      dbo.CP_EMPLOYEE AS emp ON emp.id = h.P_CLERKID LEFT OUTER JOIN

                      dbo.VIEW_PDEPARTMENT2 AS vpn ON vpn.id = h.P_CLERKID3
WHERE     (h.deleted = 0) AND (d.deleted = 0) AND (h.BILLSTATUS = 5)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
UNION ALL
SELECT     h.INVOICECODE AS 合同号码, h.INCODE, '' AS LENDOUTINCODE, h.TICKETTIME, H1.TICKETTIME AS ordertime, PD.NAME AS DEALERNAME,
                      d.P_ITEMID, pi.CODE AS p_itemcode, pi.NAME AS p_itemname, pi.ITEMKINDNAME AS itemkindname, pi.ITEMSUPLYNAME, pi.KINDDTLTAI,
                      pi.DESCRIPTION, '零售' AS 零售或直销, cdp_3.parname AS 区域, cdp_3.Name AS 部门, vpn.parentname AS 上级部门, emp.Name AS 业务员,
                      d.WAREHOUSEID, cw.CODE AS warehousecode, cw.NAME AS warehousename, d.BATCH, - d.RETURNAMOUNT AS newshouldamount,
                      - (CAST(d.RETURNAMOUNT AS decimal(10, 0)) * d.TAXPRICE) AS taxmoney, 0 AS havepaymoney, - (d.RETURNAMOUNT * d.COSTPRICE)
                      AS costmoney, - (CAST(d.RETURNAMOUNT AS decimal(10, 0)) * d.TAXPRICE - d.RETURNAMOUNT * d.COSTPRICE) AS maoli,
                      '零售冲红入库' AS outtype, '' AS jiangetime, h.CHECKTIME AS 审核日期, H1.totalmoney AS 总金额,d.Sellprice as Sellprice,h.notes as notes,d.Jiangli,d.houfan
FROM         dbo.D_SINGLESALERETURN AS h INNER JOIN
                      dbo.D_SINGLESALERETURN_DTL AS d ON h.id = d.D_TKTID LEFT OUTER JOIN
                      dbo.D_SINGLESALEORDER AS H1 ON d.SOURCECODE = H1.INCODE LEFT OUTER JOIN
                      dbo.p_item AS pi ON pi.id = d.P_ITEMID LEFT OUTER JOIN
                      dbo.CP_DEALER AS PD ON PD.id = h.P_DEALERID LEFT OUTER JOIN
                      dbo.cp_warehouse AS cw ON cw.id = d.WAREHOUSEID LEFT OUTER JOIN
                          (SELECT     cp1.id, cp1.Code, cp1.Name, cp2.Name AS parname, cp1.PARENTID
                            FROM          dbo.CP_DEPARTMENT AS cp1 LEFT OUTER JOIN
                                                   dbo.CP_DEPARTMENT AS cp2 ON cp2.id = cp1.PARENTID) AS cdp_3 ON cdp_3.id = h.P_CLERKID3 LEFT OUTER JOIN
                      dbo.CP_EMPLOYEE AS emp ON emp.id = h.P_CLERKID LEFT OUTER JOIN

                      dbo.VIEW_PDEPARTMENT2 AS vpn ON vpn.id = h.P_CLERKID3
WHERE     (h.deleted = 0) AND (h.BILLSTATUS = 5) AND (d.deleted = 0) AND (d.RETURNAMOUNT > 0)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
UNION ALL
SELECT     h.INVOICECODE AS 合同号码, h.INCODE, '' AS LENDOUTINCODE, h.TICKETTIME, H1.TICKETTIME AS ordertime, PD.NAME AS DEALERNAME,
                      d.P_ITEMID, pi.CODE AS p_itemcode, pi.NAME AS p_itemname, pi.ITEMKINDNAME AS itemkindname, pi.ITEMSUPLYNAME, pi.KINDDTLTAI,
                      pi.DESCRIPTION, '直销' AS 零售或直销, cdp_2.parname AS 区域, cdp_2.Name AS 部门, vpn.parentname AS 上级部门, emp.Name AS 业务员,
                      d.WAREHOUSEID, cw.CODE AS warehousecode, cw.NAME AS warehousename, d.BATCH, - d.RETURNAMOUNT AS newshouldamount,
                      - (CAST(d.RETURNAMOUNT AS decimal(10, 0)) * d.TAXPRICE) AS taxmoney, 0 AS havepaymoney, - (d.RETURNAMOUNT * d.COSTPRICE)
                      AS costmoney, - (CAST(d.RETURNAMOUNT AS decimal(10, 0)) * d.TAXPRICE - d.RETURNAMOUNT * d.COSTPRICE) AS maoli,
                      '直销冲红入库' AS outtype, '' AS jiangetime, h.CHECKTIME AS 审核日期, H1.totalmoney AS 总金额,d.Sellprice as Sellprice,h.notes as notes ,d.Jiangli,0 as houfan
FROM         dbo.D_SALERETURN AS h INNER JOIN
                      dbo.D_SALERETURN_DTL AS d ON h.id = d.D_TKTID LEFT OUTER JOIN
                      dbo.D_SALEORDER AS H1 ON d.SOURCECODE = H1.INCODE LEFT OUTER JOIN
                      dbo.p_item AS pi ON pi.id = d.P_ITEMID LEFT OUTER JOIN
                      dbo.CP_DEALER AS PD ON PD.id = h.P_DEALERID LEFT OUTER JOIN
                      dbo.cp_warehouse AS cw ON cw.id = d.WAREHOUSEID LEFT OUTER JOIN
                          (SELECT     cp1.id, cp1.Code, cp1.Name, cp2.Name AS parname, cp1.PARENTID
                            FROM          dbo.CP_DEPARTMENT AS cp1 LEFT OUTER JOIN
                                                   dbo.CP_DEPARTMENT AS cp2 ON cp2.id = cp1.PARENTID) AS cdp_2 ON cdp_2.id = h.P_CLERKID3 LEFT OUTER JOIN
                      dbo.CP_EMPLOYEE AS emp ON emp.id = h.P_CLERKID LEFT OUTER JOIN

                      dbo.VIEW_PDEPARTMENT2 AS vpn ON vpn.id = h.P_CLERKID3
WHERE     (h.deleted = 0) AND (h.BILLSTATUS = 5) AND (d.deleted = 0) AND (d.RETURNAMOUNT > 0)
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120))
) as www

13.update_jiangli_houfan

--批处理修改奖励
update report_saleoutdetail
set jiangli=p.jiangli
from report_saleoutdetail d left join p_item p on d.p_itemid=p.id
where p_itemid in (select id from p_item where jiangli >0 )
and (d.审核日期>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120)) and d.incode like '%CKD%'

update report_saleoutdetail
set jiangli=0-p.jiangli
from report_saleoutdetail d left join p_item p on d.p_itemid=p.id
where p_itemid in (select id from p_item where jiangli >0 )
and (d.审核日期>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120)) and d.incode like '%THD%'

--批处理修改后返
update report_saleoutdetail
set houfan=p.houfan
from report_saleoutdetail d left join p_item p on d.p_itemid=p.id
where d.p_itemid in (select id from p_item where houfan >0 )
and d.审核日期>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120) and d.incode like '%CKD%'

update report_saleoutdetail
set houfan=0-p.houfan
from report_saleoutdetail d left join p_item p on d.p_itemid=p.id
where d.p_itemid in (select id from p_item where houfan >0 )
and d.审核日期>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120) and d.incode like '%THD%'

--批处理修改奖励
update report_saleoutdtl
set jiangli=p.jiangli
from report_saleoutdtl d left join p_item p on d.p_itemid=p.id
where p_itemid in (select id from p_item where jiangli >0 )
and (d.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120)) and d.incode like '%CKD%'

--批处理修改后返
update report_saleoutdtl
set houfan=p.houfan
from report_saleoutdtl d left join p_item p on d.p_itemid=p.id
where d.p_itemid in (select id from p_item where houfan >0 )
and d.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120) and d.incode like '%CKD%'

update report_saleoutdtl
set houfan=0-p.houfan
from report_saleoutdtl d left join p_item p on d.p_itemid=p.id
where d.p_itemid in (select id from p_item where houfan >0 )
and d.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120) and d.incode like '%THD%'

update report_saleoutdtl
set jiangli=0-p.jiangli
from report_saleoutdtl d left join p_item p on d.p_itemid=p.id
where p_itemid in (select id from p_item where jiangli >0 )
and (d.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120)) and d.incode like '%THD%'
--批处理修改后返
update report_saleoutdtl
set houfan=p.houfan
from report_saleoutdtl d left join p_item p on d.p_itemid=p.id
where d.p_itemid in (select id from p_item where houfan >0 )
and d.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120) and d.incode like '%CKD%'

--出库单奖励部分
update D_SINGLESALEOUT_DTL
set jiangli=p.jiangli
from D_SINGLESALEOUT AS h
INNER JOIN D_SINGLESALEOUT_DTL AS d ON h.id = d.D_TKTID
left join p_item as p on d.p_itemid=p.id
where p_itemid in (select id from p_item where jiangli >0 )
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120)) ;

update D_SALEOUT_DTL
set jiangli=p.jiangli
from D_SALEOUT AS h
INNER JOIN D_SALEOUT_DTL AS d ON h.id = d.D_TKTID
left join p_item as p on d.p_itemid=p.id
where p_itemid in (select id from p_item where jiangli >0 )
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120)) ;

-- 冲红单奖励部分
update D_SINGLESALERETURN_DTL
set jiangli=p.jiangli
from D_SINGLESALERETURN AS h
INNER JOIN D_SINGLESALERETURN_DTL AS d ON h.id = d.D_TKTID
left join p_item as p on d.p_itemid=p.id
where p_itemid in (select id from p_item where jiangli >0 )
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120));;

update D_SALERETURN_DTL
set jiangli=p.jiangli
from D_SALERETURN AS h
INNER JOIN D_SALERETURN_DTL AS d ON h.id = d.D_TKTID
left join p_item as p on d.p_itemid=p.id
where p_itemid in (select id from p_item where jiangli >0 )
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120));

--出库单后返部分
update D_SINGLESALEOUT_DTL
set houfan=p.houfan
from D_SINGLESALEOUT AS h
INNER JOIN D_SINGLESALEOUT_DTL AS d ON h.id = d.D_TKTID
left join p_item as p on d.p_itemid=p.id
where p_itemid in (select id from p_item where houfan >0 )
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120)) ;

update D_SALEOUT_DTL
set houfan=p.houfan
from D_SALEOUT AS h
INNER JOIN D_SALEOUT_DTL AS d ON h.id = d.D_TKTID
left join p_item as p on d.p_itemid=p.id
where p_itemid in (select id from p_item where houfan >0 )
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120)) ;

-- 冲红单后返部分
update D_SINGLESALERETURN_DTL
set houfan=p.houfan
from D_SINGLESALERETURN AS h
INNER JOIN D_SINGLESALERETURN_DTL AS d ON h.id = d.D_TKTID
left join p_item as p on d.p_itemid=p.id
where p_itemid in (select id from p_item where houfan >0 )
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120));

update D_SALERETURN_DTL
set houfan=p.houfan
from D_SALERETURN AS h
INNER JOIN D_SALERETURN_DTL AS d ON h.id = d.D_TKTID
left join p_item as p on d.p_itemid=p.id
where p_itemid in (select id from p_item where houfan >0 )
and (h.checktime>=CONVERT(VARCHAR(10),dateadd(day,-1,getdate()),120));

14.insert datedkmxbb(report_d_datedkmxbb)

insert into DATEDKMXBB
select * from(
select getdate() as date,isnull(ptype.code,'') as dikoutypecode,
ptype.name as dikoutypename,a.incode as incode,a.month as month,a.tickettime as tickettime,
pp.code as p_dealercode,pp.name as p_dealername,pe.code as p_clerkcode,pe.name as p_clerkname,
pd.code as p_clerkcode3,pd.name as p_clerkname3,coalesce(b.taxmoney,0) as taxmoney,
coalesce(HAVEHOOKMONEY,0) as HAVEHOOKMONEY,coalesce(NOTHOOKMONEY,0) as NOTHOOKMONEY,
a.billstatus as billstatus,ptype.name as BILLTYPE,b.id+'-'+cast(getdate() as varchar) as id,
b.deleted,b.lock_counter, b.status,b.ownerid,b.version_number

from d_returnmoney a inner join d_returnmoney_dtl b on a.id = b.d_tktid
and a.deleted = 0 and b.deleted = 0
left join cp_dealer pp on pp.id = a.p_dealerid
left join cp_employee pe on pe.id = a.p_clerkid
left join BD_PUBDIC_VALUE ptype on ptype.code = b.valuetype
and ptype.KindId='8281816215c5b3320115c64142100002'
left join cp_department pd on pd.id = a.p_clerkid3 where NOTHOOKMONEY<>0 ) as ss

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16313359/viewspace-682344/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16313359/viewspace-682344/