ALTER PROCEDURE UpdateYSDZBJY_GZTH            
AS            
BEGIN            
          
TRUNCATE TABLE YingShouZhanKuanJY_GZTH          
          
INSERT INTO YingShouZhanKuanJY_GZTH(FID,FSaleOrgId,FSalerId,[DATE],ContractNumber,ProjectName,OrderAmount,FALLAmountFor,PaymentAmount,ShippedDebt,InvoicedDebt,OwedTickets,IsEnded,Remark,[K3专票金额],[K3普票金额],ZPFALLAmountFor,PPFALLAmountFor
,[K3发货数量],[K3发货金额],[K3退货数量],[K3退货金额],[星空发货数量],[星空发货金额],[星空退货数量],[星空退货金额],ShipmentsAmount,FBillAllAmount)          
SELECT FID,FSaleOrgId,FSalerId,[Date],CONTRACTNUMBER,ProjectName,SUM(OrderAmount) OrderAmount,SUM(BillingAmount) BillingAmount,SUM(PaymentAmount) PaymentAmount,SUM(ShippedDebt) ShippedDebt
,SUM(InvoicedDebt) InvoicedDebt,SUM(OwedTickets) OwedTickets,null,Remark,SUM([K3专票金额]) [K3专票金额],SUM([K3普票金额]) [K3普票金额],SUM(ZPBillingAmount) [专票开票金额],SUM(PPBillingAmount) [普票开票金额] 
,SUM([K3发货数量]) [K3发货数量],SUM([K3发货金额]) [K3发货金额],SUM([K3退货数量]) [K3退货数量],SUM([K3退货金额]) [K3退货金额],SUM([星空发货数量]) [星空发货数量],SUM([星空发货金额]) [星空发货金额],SUM([星空退货数量]) [星空退货数量],SUM([星空退货金额]) [星空退货金额] 
,SUM(ShipmentsAmount) 发货金额,SUM(ReturnAmount) 退货金额   
FROM YingShouZhanKuanMX_GZTH  
WHERE Number <> '合计' OR Number IS NULL
GROUP BY FID,FSaleOrgId,FSalerId,[Date],CONTRACTNUMBER,ProjectName,Remark
          
UPDATE YingShouZhanKuanJY_GZTH SET IsEnded= b.IsEnded
FROM (SELECT DISTINCT CONTRACTNUMBER,IsEnded FROM YingShouZhanKuanMX_GZTH aa 
 WHERE IsEnded IS NOT NULL) b
 WHERE  b.CONTRACTNUMBER=YingShouZhanKuanJY_GZTH.CONTRACTNUMBER

----对表里未添加数据进行添加          
--select FID,sum(shipmentsamount) as '发货金额',          
--sum(ReturnAmount) AS '退货金额',          
--SUM(PPBillingAmount) AS '普票金额',          
--SUM(ZPBillingAmount) AS '专票金额'          
--INTO #TEMP1          
--from yingshouzhankuanmx_gzth          
--group by fid          
          
--UPDATE A          
--SET A.ShipmentsAmount = B.发货金额,          
--A.FBillAllAmount = B.退货金额,          
--A.PPFALLAmountFor = B.普票金额,          
--A.ZPFALLAmountFor = B.专票金额          
--FROM YingShouZhanKuanJY_GZTH A          
--INNER JOIN #TEMP1 B ON A.FID = B.FID          
                                
SELECT        
*,           
ROW_NUMBER() OVER (PARTITION BY FID ORDER BY FID) as row_num1 
INTO #CFTEMP1
FROM YingShouZhanKuanJY_GZTH          

UPDATE A
SET A.row_num = B.row_num1
FROM YingShouZhanKuanJY_GZTH A
INNER JOIN #CFTEMP1 B On A.FID = B.FID 

          
DELETE FROM YingShouZhanKuanJY_GZTH          
WHERE row_num <> 1; 

UPDATE A
SET A.Number = B.row_num1
FROM YingShouZhanKuanJY_GZTH A
INNER JOIN (
	SELECT        
	FID,           
	ROW_NUMBER() OVER (ORDER BY [DATE] ,[FID]) as row_num1 
	FROM YingShouZhanKuanJY_GZTH  
	) B On A.FID = B.FID 
    
      
--更新专票和普票的开票时间          
UPDATE A          
SET A.PPBillingDate = B.PPBillingDate,A.ZPBillingDate = B.ZPBillingDate          
FROM YingShouZhanKuanJY_GZTH A          
INNER JOIN YingShouZhanKuanMX_GZTH B ON A.FID = B.FID          
                       
--DROP TABLE #TEMP1          
        
END