_Test测试(Admin)

-- 还原数据，Price数据更新错误

UPDATE d
SET
	d.Price = s.Price,
	d.Amount = s.Amount	
FROM tb_IN_ProductDetails d INNER JOIN  tb_SOs s ON d.PFNO = s.PFNO
where isnull(d.Currency,'')!=isnull(s.Currency,'') 
	or isnull(d.CurrencyRate,0)!=isnull(s.CurrencyRate,0) 
	or isnull(d.Price,0)!=isnull(s.Price,0)
	or isnull(d.PriceSO,0)!=isnull(s.Price,0)
	or isnull(d.LocalPrice,0)!=isnull(s.LocalPrice,0);


--计算原币金额
UPDATE tb_IN_ProductDetails
	SET Amount=round(Price*Quantity,2),
	    AmountSO=round(PriceSO*Quantity,2),
	    LocalAmount=round(LocalPrice*Quantity,2)
WHERE 1=0
	or isnull(Amount,0)!= round(isnull(Price,0)*isnull(Quantity,0),2)
	or isnull(AmountSO,0)!= round(isnull(PriceSO,0)*isnull(Quantity,0),2)
	or isnull(LocalAmount,0)!= round(isnull(LocalPrice,0)*isnull(Quantity,0),2);

--更新税金------------------------------------------------------------------------------------
--更新不含税金额
UPDATE tb_IN_ProductDetails
SET AmountExclTax = ROUND(Amount / (1 + isnull(TaxRate,0)), 2)
WHERE isnull(AmountExclTax,0)!=ROUND(Amount / (1 + isnull(TaxRate,0)), 2);

--更新税金=本币金额-不含税金额
--UPDATE tb_IN_ProductDetails
--SET TaxAmount = ROUND(AmountExclTax * TaxRate, 2)
--WHERE isnull(TaxAmount,0)!=ROUND(AmountExclTax * TaxRate, 2);
UPDATE tb_IN_ProductDetails set TaxAmount=LocalAmount-AmountExclTax
where isnull(TaxAmount,0)!=isnull(LocalAmount,0)-isnull(AmountExclTax,0);

--更新主表------------------------------------------------------------------------------------
-- 汇总更新主表 tb_IN_Product
UPDATE p
SET
    TotalQuantity       = ISNULL(d.SumQty, 0),
    TotalAmountExclTax  = ISNULL(d.SumAmountExclTax, 0),
    TotalTaxAmount      = ISNULL(d.SumTaxAmount, 0),
    TotalAmount         = ISNULL(d.SumAmount, 0),
	TotalLocalAmount    = ISNULL(d.SumLocalAmount, 0),
    Currency            = ISNULL(d.Currency, ''),
    CurrencyRate        = ISNULL(d.CurrencyRate, 0),
    TaxRate             = ISNULL(d.TaxRate, 0)
FROM tb_IN_Product p
INNER JOIN (
    SELECT
        INNO,
        SUM(Quantity)          AS SumQty,  -- 假设明细数量字段叫 Quantity
        SUM(AmountExclTax)     AS SumAmountExclTax,
        SUM(TaxAmount)         AS SumTaxAmount,
        SUM(LocalAmount)       AS SumLocalAmount,
		SUM(Amount)            AS SumAmount, --取Amount

        MAX(Currency)          AS Currency,
        MAX(CurrencyRate)      AS CurrencyRate,
        MAX(TaxRate)           AS TaxRate
    FROM tb_IN_ProductDetails
    GROUP BY INNO
) d ON p.INNO = d.INNO
where 1=0
	or isnull(p.TotalQuantity,0)!=isnull(d.SumQty,0) 
	or isnull(p.TotalAmountExclTax,0)!=isnull(d.SumAmountExclTax,0) 
	or isnull(p.TotalTaxAmount,0)!=isnull(d.SumTaxAmount,0) 
	or isnull(p.TotalLocalAmount,0)!=isnull(d.SumLocalAmount,0) 
	or isnull(p.TotalAmount,0)!=isnull(d.SumAmount,0);