TA的每日心情 | 开心
昨天 13:35 |
|---|
签到天数: 1265 天 [LV.10]以坛为家III
|
楼主 |
发表于 2021-11-11 16:32:16
|
显示全部楼层
<BillTable>
<BillID>1</BillID>
<BillName>销售单</BillName>
<BillType>Sale</BillType>
<SqlStr_M>
declare @s varchar(100),@sql varchar(1000),@paymoney decimal(18,2);
if(EXISTS(select * from Woolinte_BillIndex where billnumberid='@@'))
begin
select @s=isnull(paymoney,0) from Woolinte_BillIndex where billnumberid='@@';
end
else
begin
set @s='0';
end
set @sql='select col='''+ replace(case when @s='' then '0' else @s end,',',''' union all select ''')+''''
set @sql='select * into ##bb from ('+@sql+') b';
--PRINT @sql
exec (@sql);
select @paymoney=SUM(convert(decimal(18,2),isnull(col,0.0))) from ##bb ;
--print @paymoney
drop table ##bb;
select Convert(varchar(10),getdate(),120) as date,* from
(select a.*,
case when a.state='1' then CAST(b.ARTotal as numeric(12,2))
else
case when dly.draft='1' then CAST(b.ARTotal as numeric(12,2))
else
CAST(b.ARTotal as numeric(12,2))+@paymoney+isnull(PreferenceMoney,0)-DlySumTotal end end as ARTotal,
case when a.state='1' then
(cast((CAST(b.ARTotal as numeric(12,2))-CAST(b.APTotal as numeric(12,2))+DlySumTotal-@paymoney-isnull(PreferenceMoney,0)) as numeric(12,2)))
else
case when dly.draft='1' then
(cast((CAST(b.ARTotal as numeric(12,2))-CAST(b.APTotal as numeric(12,2))+DlySumTotal-@paymoney-isnull(PreferenceMoney,0)) as numeric(12,2)))
else
CAST(b.ARTotal as numeric(12,2))
end end as SumARTotal ,
b.bfullname as bfname,b.area as area,b.person,b.mophone as mophone,b.TelAndAddress,c.kfullname as changku,g.kfullname as changku2,d.efullname as zhidanren,e.efullname as jinshouren, f.dfullname
,@paymoney as SumPayMoney
from Woolinte_BillIndex a
left join dlyndx dly on a.billcode=dly.number
left join btype b on a.btypeid_=b.btypeid
left join Stock c on a.ktypeid_=c.ktypeid
left join Stock g on a.ktypeid_=g.ktypeid
left join employee d on a.usertypeid=d.etypeid
left join employee e on a.etypeid_=e.etypeid
left join Department f on a.DTypeID_=f.dtypeid
where a.billtype='Sale')D where billnumberid='@@';
</SqlStr_M>
<SqlStr_S>
select DetailRowID AS xuhao,case when GBL.DetailType='1' then '赠品' else'' end as zengping,GBL.ptypeid_,p.UsefulLifeDay AS PUsefulLifeDay, P.pusercode AS pucode,P.pfullname AS Ptypename,GBL.UintName AS UintName,P.Standard AS Stand,p.type as Type,x.BarCode AS BarCode,xw.barcode as jbBarCode,
GBL.PDAQty as pdaqtygroup,GBL.PDAPrice,GBL.PDADiscount,GBL.PDAPrice*GBL.PDADiscount AS PDADistPrice,
GBL.pdaprice*GBL.PDAQty as pdasum,GBL.pdaprice*GBL.PDADiscount*GBL.PDAQty as distpdasum,GBL.Comment,GBL.GoodsNumber,GBL.ProduceDate,GBL.ValidDate,
CASE WHEN ISNULL(GBL.PDAQty*GBL.UintRate, 0) = 0 THEN ''
WHEN ISNULL(GBL.PDAQty*GBL.UintRate,0) >= CAST(isnull(UnitRate1, 0) AS INT) AND (ISNULL(GBL.PDAQty*GBL.UintRate,0)< CAST(isnull(UnitRate2, 0) AS INT) OR CAST(isnull(UnitRate2, 0) AS INT)=0 )
THEN CAST(CAST(GBL.PDAQty*GBL.UintRate AS float) AS VARCHAR(20))+PU.Unit1
WHEN ISNULL(GBL.PDAQty*GBL.UintRate,0)>= CAST(isnull(UnitRate2, 0) AS INT) AND (ISNULL(GBL.PDAQty*GBL.UintRate,0)<CAST(isnull(UnitRate3, 0) AS INT) OR CAST(isnull(UnitRate3, 0) AS INT)=0)
THEN CONVERT(VARCHAR(20),CAST(GBL.PDAQty*GBL.UintRate / ISNULL(UnitRate2, 0) AS INT)) +isnull(unit2, '')
+ (CASE WHEN Cast(GBL.PDAQty*GBL.UintRate as INT) % cast(ISNULL(UnitRate2, 0) as int) != 0 THEN CONVERT(VARCHAR(20), cAST(GBL.PDAQty*GBL.UintRate AS int) % cast(ISNULL(UnitRate2, 0) as int)) +isnull(PU.Unit1, '') ELSE '' END)
WHEN ISNULL(GBL.PDAQty*GBL.UintRate,0)>= cast(UnitRate3 AS INT)
THEN CONVERT(VARCHAR(20),CAST(GBL.PDAQty*GBL.UintRate / ISNULL(UnitRate3, 0) AS INT)) +unit3 + (CASE WHEN cast(GBL.PDAQty*GBL.UintRate as int)% cast(ISNULL(UnitRate3, 0) as int) >= UnitRate2 THEN CONVERT(VARCHAR(20),CAST((cast(GBL.PDAQty*GBL.UintRate as int) % cast(ISNULL(UnitRate3, 0) as int)) / ISNULL(UnitRate2, 0) AS INT))+Unit2 ELSE '' END)
+ (CASE WHEN CAST((cast(GBL.PDAQty*GBL.UintRate as int)% cast(ISNULL(UnitRate3, 0) as int))% cast(ISNULL(UnitRate2, 0) as int) AS int)!= 0 THEN CONVERT(VARCHAR(20),CAST((cast(GBL.PDAQty*GBL.UintRate as int) % cast(ISNULL(UnitRate3, 0) as int)) % cast(ISNULL(UnitRate2, 0) as int)AS FLOAT)) +PU.Unit1 ELSE '' END) ELSE '' END AS fzdw
,CAST(pps.Price as numeric(12,5))RetailPrice,CAST((CAST(pps.Price as numeric(12,6)))*GBL.PDAQty AS NUMERIC(12,2)) RetailTotal,s.kfullname as KFullName
From Woolinte_BillIndex_List GBL
left join PType P on GBL.ptypeid_=P.ptypeid
left join xw_PtypeBarCode x on GBL.ptypeid_=x.Ptypeid and x.UnitID+1=GBL.UintNum and x.ordid=0
left join xw_PtypeBarCode xw on GBL.ptypeid_=xw.Ptypeid and xw.UnitID=0 and xw.ordid=0
LEFT JOIN xw_PtypeUnit pv ON Pv.Ordid=1 AND Pv.PTypeID=P.PTypeID
LEFT JOIN xw_PtypeUnit PU ON pu.IsBase=1 AND PU.PTypeID=P.PTypeID
left join
(
SELECT ISNULL(pps.Price,'') Price,pps.PTypeId,pps.UnitID FROM xw_P_PtypePrice PPS
LEFT JOIN (select BillNumberID,PTypeId_,UintNum,DetailType FROM dbo.Woolinte_Billindex_list WBL GROUP BY BillNumberID,PTypeId_,UintNum,DetailType) WBL
ON pps.UnitID=WBL.UintNum-1 AND pps.PTypeId=WBL.PTypeID_
WHERE WBL.BillNumberID='@@' AND pps.PRTypeId='0001' AND wbl.DetailType='0'
)pps on GBL.PTypeID_=pps.PTypeId AND pps.UnitID=GBL.UintNum-1
left join Stock s on gbl.KTypeID_=s.ktypeid
LEFT JOIN ( SELECT u1.PTypeId, u3.Unit1 unit3,u2.Unit1 unit2,u1.Unit1 unit1,
u3.URate UnitRate3,u2.URate UnitRate2,u1.URate UnitRate1 FROM xw_PtypeUnit u1 LEFT JOIN xw_PtypeUnit u2 ON u1.PTypeId=u2.PTypeId AND u2.Ordid=1
LEFT JOIN xw_PtypeUnit u3 ON u1.PTypeId=u3.PTypeId AND u3.Ordid=2 WHERE u1.IsBase=1) u ON u.PTypeId=p.ptypeid
where billnumberid='@@' ORDER BY GBL.num asc;
</SqlStr_S>
|
|
发表于 2021-11-11 16:21:30
