_Test测试(Admin)
SQL 全选
-- 还原数据，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);
采购入库
C# 全选
-----------------------------------------------------
-- 更新采购入库明细单价金额、主表金额
-- 注意：采购入库单价保留5位小数，金额保留4位
-- 20260729：采购入库单明细表金额保留4位小数。
-----------------------------------------------------
select * from dt_Supplier where ShortName='驰邦';
--HD
select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01';
--JLM
select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01';
select sum(LocalAmount) from tb_IN_MaterialDetails
where INNO in (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01' and DocDate<='2026/06/30')
----------------------------------------------------------------------------------
--
--1、更新原币金额，金额4位小数
--
/*
select Price,Quantity,Amount,round(cast(Price as decimal(19,5))*cast(Quantity as decimal(19,5)),4)
from tb_IN_MaterialDetails
where 1=1
and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and Amount!=round(cast(Price as decimal(19,5))*cast(Quantity as decimal(19,5)),4);
*/
update tb_IN_MaterialDetails set Amount=round(cast(Price as decimal(19,5))*cast(Quantity as decimal(19,5)),4)
where 1=1
--and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and INNO IN (select INNO from tb_IN_Material where DocDate>='2026/07/01')
and Amount!=round(cast(Price as decimal(19,5))*cast(Quantity as decimal(19,5)),4);
------------------------------------------------------------------------------------
--2、更新明细表本币金额 = 原币金额*汇率
--
/*
select INNO,CurrencyRate,Amount,LocalAmount,round(cast(Amount as decimal(19,6))*cast(CurrencyRate as decimal(19,6)),4)
from tb_IN_MaterialDetails
where LocalAmount!=round(cast(Amount as decimal(19,6))*cast(CurrencyRate as decimal(19,6)),4);
*/
update tb_IN_MaterialDetails set LocalAmount=round(cast(Amount as decimal(19,6))*cast(CurrencyRate as decimal(19,6)),4)
where 1=1
--and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and INNO IN (select INNO from tb_IN_Material where DocDate>='2026/07/01')
and LocalAmount!=round(cast(Amount as decimal(19,6))*cast(CurrencyRate as decimal(19,6)),4);
------------------------------------------------------------------------------------
--3、更新明细表的本币单价=本币金额/数量
--
--更新本币单价=本币金额/数量 （5位小数）
/*
select LocalPrice,round(LocalAmount/cast(Quantity as decimal(19,5)),5) from tb_IN_MaterialDetails
where LocalPrice!=round(LocalAmount/cast(Quantity as decimal(19,5)),5);
*/
update tb_IN_MaterialDetails set LocalPrice=round(LocalAmount/cast(Quantity as decimal(19,5)),5)
where 1=1
--and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and INNO IN (select INNO from tb_IN_Material where DocDate>='2026/07/01')
and LocalPrice!=round(LocalAmount/cast(Quantity as decimal(19,5)),5);
----------------------------------------------------------------------------------
--4、计算税金=含税金额 ÷（1+13%）* 13%
----------------------------------------------------------------------------------
--
--重要：汉都采购单没有税金的！！！ 更新税金！！！
--
--HD: 清空含税标识\税金0
/*
--只有HD更新，慎重 ！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
update tb_IN_Material set TaxRate=null,TotalTaxAmount=0 where isnull(TaxRate,0)!=0 or isnull(TotalTaxAmount,0)!=0;
update tb_IN_MaterialDetails set TaxRate=null,TaxAmount=0 where isnull(TaxAmount,0)!=0;
--只有HD更新，慎重 ！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
*/
---------------------------------------------------------------------------------
--计算税额
/*
select LocalAmount,TaxRate, TaxAmount,dbo.ufn_GetTaxAmount(LocalAmount,TaxRate)
from tb_IN_MaterialDetails
where 1=1
and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and isnull(TaxAmount,0)!=dbo.ufn_GetTaxAmount(LocalAmount,TaxRate);
*/
update tb_IN_MaterialDetails set TaxAmount=dbo.ufn_GetTaxAmount(LocalAmount,TaxRate)
where 1=1
--and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and INNO IN (select INNO from tb_IN_Material where DocDate>='2026/07/01')
and isnull(TaxAmount,0)!=dbo.ufn_GetTaxAmount(LocalAmount,TaxRate);
---------------------------------------------------------------------------------
--4、更新不含税金额 = 含税金额-税金
---------------------------------------------------------------------------------
/*
select INNO,AmountExclTax,LocalAmount-TaxAmount,TaxAmount from tb_IN_MaterialDetails
where AmountExclTax!=LocalAmount-TaxAmount;
*/
update tb_IN_MaterialDetails set AmountExclTax=LocalAmount-TaxAmount
where 1=1
--and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and INNO IN (select INNO from tb_IN_Material where DocDate>='2026/07/01')
and AmountExclTax!=LocalAmount-TaxAmount;
---------------------------------------------------------------------------------
--5、计算不含税单价 = 不含税金额 / 数量
---------------------------------------------------------------------------------
--无金额
-- select * from tb_IN_MaterialDetails where isnull(AmountExclTax,0)=0 and PriceExclTax!=0;
update tb_IN_MaterialDetails set PriceExclTax=0 where isnull(AmountExclTax,0)=0 and PriceExclTax!=0;
/*
select Quantity,Amount, AmountExclTax,PriceExclTax,round(AmountExclTax/cast(Quantity as numeric(19,6)),5) from tb_IN_MaterialDetails
where AmountExclTax>0 and PriceExclTax!=round(AmountExclTax/cast(Quantity as numeric(19,6)),5);
*/
update tb_IN_MaterialDetails set PriceExclTax=round(AmountExclTax/cast(Quantity as numeric(19,6)),5)
where 1=1
--and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and INNO IN (select INNO from tb_IN_Material where DocDate>='2026/07/01')
and (AmountExclTax>0 and PriceExclTax!=round(AmountExclTax/cast(Quantity as numeric(19,6)),5));
---------------------------------------------------------------------------------
--6、更新主表金额
---------------------------------------------------------------------------------
update tb_IN_Material set
TotalAmount=b.TotalAmount,
TotalLocalAmount=b.TotalLocalAmount,
TotalAmountExclTax=b.TotalAmountExclTax,
TotalTaxAmount=b.TotalTaxAmount
from tb_IN_Material a join
(
select INNO,
TotalAmount=Round(SUM(Amount),2),
TotalLocalAmount=Round(SUM(LocalAmount),2),
TotalAmountExclTax=Round(SUM(AmountExclTax),2),
TotalTaxAmount=Round(SUM(isnull(TaxAmount,0)),2)
from tb_IN_MaterialDetails
group by INNO
) b on a.INNO=b.INNO
where 1=1
--and a.INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and a.INNO IN (select INNO from tb_IN_Material where DocDate>='2026/07/01')
and (a.TotalAmount!=b.TotalAmount or a.TotalLocalAmount!=b.TotalLocalAmount or a.TotalAmountExclTax!=b.TotalAmountExclTax or a.TotalTaxAmount!=b.TotalTaxAmount);
--/****************************************************************************/
------更新主表余额-----------------------------------------
--更新发票余额
update tb_IN_Material set TotalInvoiceAmountBal=TotalLocalAmount-isnull(TotalInvoiceAmount,0)
where 1=1
--and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and INNO IN (select INNO from tb_IN_Material where DocDate>='2026/07/01')
and TotalInvoiceAmountBal!=TotalLocalAmount-isnull(TotalInvoiceAmount,0);
--更新结算余额
update tb_IN_Material set TotalSettleAmountBal=TotalAmount-isnull(TotalSettleAmount,0)
where 1=1
--and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and INNO IN (select INNO from tb_IN_Material where DocDate>='2026/07/01')
and TotalSettleAmountBal!=TotalAmount-isnull(TotalSettleAmount,0);
--更新本币结算余额
update tb_IN_Material set TotalSettleLocalAmountBal=TotalLocalAmount-isnull(TotalSettleLocalAmount,0)
where 1=1
--and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and INNO IN (select INNO from tb_IN_Material where DocDate>='2026/07/01')
and TotalSettleLocalAmountBal!=TotalLocalAmount-isnull(TotalSettleLocalAmount,0);
----------------------------------------------------------------
----采购入库对账单（供应商对账单）
----------------------------------------------------------------
--select * from tb_SS
--select * from tb_SSs where AmountExclTax!=InvoiceAmount;
/*
select a.Amount,b.Amount,a.LocalAmount,b.LocalAmount,a.LocalPrice,b.LocalPrice,a.AmountExclTax,b.AmountExclTax,a.TaxAmount,b.TaxAmount
from tb_SSs a join tb_IN_MaterialDetails b on a.DocDetail_isid=b.isid
where a.Amount!=b.Amount or a.LocalAmount!=b.LocalAmount or a.LocalPrice!=b.LocalPrice or a.AmountExclTax!=b.AmountExclTax or a.TaxAmount!=b.TaxAmount
*/
--更新对账单明细
update tb_SSs set Amount=b.Amount,LocalAmount=b.LocalAmount,LocalPrice=b.LocalPrice,AmountExclTax=b.AmountExclTax,TaxAmount=b.TaxAmount
from tb_SSs a join tb_IN_MaterialDetails b on a.DocDetail_isid=b.isid
where 1=1
--and INNO IN (select INNO from tb_IN_Material where SupplierCode='0858' and DocDate>='2026/06/01')
and INNO IN (select INNO from tb_IN_Material where DocDate>='2026/07/01')
and (a.Amount!=b.Amount or a.LocalAmount!=b.LocalAmount or a.LocalPrice!=b.LocalPrice or a.AmountExclTax!=b.AmountExclTax or a.TaxAmount!=b.TaxAmount);
--更新对账单主表的金额（2位小数）
--可以更新全部
update tb_SS set TotalAmount=b.TotalAmount,TotalLocalAmount=b.TotalLocalAmount,TotalAmountExclTax=b.TotalAmountExclTax
from tb_SS a join
(
select DZNO,
TotalAmount=Round(SUM(Amount),2),
TotalLocalAmount=Round(SUM(LocalAmount),2),
TotalAmountExclTax=Round(SUM(AmountExclTax),2) from tb_SSs
group by DZNO
) b on a.DZNO=b.DZNO
where (a.TotalAmount!=b.TotalAmount or a.TotalLocalAmount!=b.TotalLocalAmount or a.TotalAmountExclTax!=b.TotalAmountExclTax);
--更新主表余额-----------------------------------------
--可以更新全部
update tb_SS set TotalInvoiceAmountBal=TotalLocalAmount-isnull(TotalInvoiceAmount,0)
where TotalInvoiceAmountBal!=TotalLocalAmount-isnull(TotalInvoiceAmount,0);
--更新结算余额
--可以更新全部
update tb_SS set TotalSettleAmountBal=TotalAmount-isnull(TotalSettleAmount,0)
where TotalSettleAmountBal!=TotalAmount-isnull(TotalSettleAmount,0);
--更新本币结算余额
--可以更新全部
update tb_SS set TotalSettleLocalAmountBal=TotalLocalAmount-isnull(TotalSettleLocalAmount,0)
where TotalSettleLocalAmountBal!=TotalLocalAmount-isnull(TotalSettleLocalAmount,0);
-------------------------------------------------------------
-- 更新 FIFO 表
-------------------------------------------------------------
-- select * from tb_Inventory_InOutFIFO
-- select distinct doctype from tb_Inventory_InOutFIFO
/*
select a.Quantity,b.Quantity, a.Price,b.Price,a.PriceExclTax,b.PriceExclTax,a.Amount,b.Amount,a.AmountExclTax,b.AmountExclTax,a.LocalAmount,b.LocalAmount
from tb_Inventory_InOutFIFO a join tb_IN_MaterialDetails b on a.DocDetailIsid=b.isid
--where isnull(a.Quantity,0)!=isnull(b.Quantity,0) or isnull(a.Price,0)!=isnull(b.Price,0) or isnull(a.PriceExclTax,0)!=isnull(b.PriceExclTax,0) or isnull(a.Amount,0)!=isnull(b.Amount,0) or isnull(a.AmountExclTax,0)!=isnull(b.AmountExclTax,0) or isnull(a.LocalAmount,0)!=isnull(b.LocalAmount,0);
where a.DocType='IN_Material' and a.Quantity!=b.Quantity or a.Price!=b.Price or a.PriceExclTax!=b.PriceExclTax or a.Amount!=b.Amount or a.AmountExclTax!=b.AmountExclTax or a.LocalAmount!=b.LocalAmount;
*/
--可以更新全部
update tb_Inventory_InOutFIFO set Quantity=b.Quantity, Price=b.Price,PriceExclTax=b.PriceExclTax,Amount=b.Amount,AmountExclTax=b.AmountExclTax,LocalAmount=b.LocalAmount
from tb_Inventory_InOutFIFO a join tb_IN_MaterialDetails b on a.DocDetailIsid=b.isid
where a.DocType='IN_Material' and (a.Quantity!=b.Quantity or a.Price!=b.Price or a.PriceExclTax!=b.PriceExclTax or a.Amount!=b.Amount or a.AmountExclTax!=b.AmountExclTax or a.LocalAmount!=b.LocalAmount);
-----------------------------------------------------------
--end
-----------------------------------------------------------
版权声明：本文为开发框架文库发布内容,转载请附上原文出处连接
NewDoc C/S框架网
