//销售数量
strBuilder.Append(@" with sendData as (select b.fcustid,sum(fqty) as fqty,sum(fqty*farea) as farea,sum(fqty*fprice) as fpmoney,sum(fqty*fweigh) as fweigh,count(*) as fshcs,round(sum(fprice*fqty),6) as fmoney
from SALE_Sendb_Detail as a join SALE_Sendb as b on a.fno=b.fno where b.fdate between @startDate and @endDate {0} group by b.fcustid)
,sendSumData as (select b.fcustid,sum(fNum) as fqty,sum(fNum*farea) as farea,sum(fNum*fprice) as fpmoney,sum(fNum*fWeight) as fweigh,count(*) as fshcs,sum(fmoney) as fmoney
from SALE_SendbSummary_Detail as a join SALE_Sendb as b on a.fno=b.fno where b.fdate between @startDate and @endDate {0} group by b.fcustid)
,sendBackData as (select b.fcustid,sum(fqty) as fqty,sum(fqty*farea) as farea ,sum(fqty*fprice) as fpmoney,sum(fqty*fweigh) as fweigh,count(*) as fshcs,sum(fmoney) as fmoney
from invp_back as a join invm_back as b on a.fno=b.fno where b.fdate between @startDate and @endDate {0} group by b.fcustid)
,sendBackSumData as (select b.fcustid,sum(fNum) as fqty,sum(fNum*farea) as farea,sum(fNum*fprice) as fpmoney,sum(fNum*fWeight) as fweigh,count(*) as fshcs,sum(fmoney) as fmoney
from invp_BackSummary_Detail as a join invm_back as b on a.fno=b.fno where b.fdate between @startDate and @endDate {0} group by b.fcustid)
,InData as (select a.fcustid,sum(fqty) as fqty,sum(fqty*farea) as farea,sum((fqty*fprice)-(CostPrice*fqty)) as fcostMoeny,sum(fqty*fprice) as fpmoney,count(*) as fshcs
from invp_product as a where a.fInDate between @startDate and @endDate {0} group by a.fcustid)
,tblProd as (select a.fcustid,sum(fTotalNum) as fqty ,count(*) as fshcs,sum(fTotalMoney) as fmoney
from SALE_PO as a where a.fCreateDate between @startDate and @endDate {0} group by a.fcustid)
,Person as (select a.fid,a.DeptId from HR_PersonnelFiles as a )
,Dept as (select a.fid,a.fname from bas_dept as a )");
strBuilder.Append(@"
,OutSendData AS (
SELECT
SUM(a.fqty) AS fqty,
ROUND( SUM(a.fprice*a.fqty) ,6)as fmoney,
b.Fcustid AS FCustId
FROM SALE_Sendb_Detail a
LEFT JOIN SALE_Sendb b ON a.Fno = b.Fno
WHERE
b.Fdate >= @startDate
AND b.Fdate <= @endDate
AND (
EXISTS (
SELECT 1
FROM OUT_OutAssitProductIn_Detail
WHERE FTagNumber = a.FTagNumber
)
OR EXISTS (
SELECT 1
FROM OUT_OutAssitProductIn_Detail ao
INNER JOIN Invp_product ain ON ain.UseProdTag = ao.FTagNumber
WHERE ain.FTagNumber = a.FTagNumber
)
)
GROUP BY b.Fcustid
),
OutBackData AS (
SELECT
SUM(a.fqty) AS fqty,
ROUND(SUM(a.fprice*a.fqty),6) as fmoney,
b.Fcustid AS FCustId
FROM Invp_back a
LEFT JOIN Invm_back b ON a.Fno = b.Fno
WHERE
b.Fdate >= @startDate
AND b.Fdate <= @endDate
AND (
EXISTS (
SELECT 1
FROM OUT_OutAssitProductIn_Detail
WHERE FTagNumber = a.FTagNumber
)
OR EXISTS (
SELECT 1
FROM OUT_OutAssitProductIn_Detail ao
INNER JOIN Invp_product ain ON ain.UseProdTag = ao.FTagNumber
WHERE ain.FTagNumber = a.FTagNumber
)
)
GROUP BY b.Fcustid
)");
strBuilder.Append(@"
SELECT cust.fid as fcustid, cust.fname as fcustname, cust.fcname as fcustCname, fsales,fsl,TaxType
, (isnull(ab.fqty,0)-isnull(ac.fqty,0)) as fqty,isnull(ab.fqty,0) as SendNum,isnull(ac.fqty,0) as BackNum
,(isnull(ROUND(ab.farea, cust.SendAreaRoundNum),0)-isnull(ROUND(ac.farea, cust.SendAreaRoundNum),0)) as farea
,(isnull(ab.fweigh,0)-isnull(ac.fweigh,0)) as fweight, isnull(ab.fshcs,0) as fshcs
,(isnull(ab.fmoney,0)-isnull(ac.fmoney,0)) as fmoney
,(isnull(abb.fmoney,0)-isnull(acc.fmoney,0)) as fmoneySummary
,isnull(ab.fmoney,0) as SendMoney
,isnull(abb.fmoney,0) as SendSummaryMoney
,isnull(ad.fqty,0) as InNum
,isnull(ad.fcostMoeny,0) as InFcostMoeny
,isnull(ad.farea,0) as InFarea
,isnull(ad.fpmoney,0) as InMoney
,isnull(ac.fmoney,0) as BackMoney
,isnull(acc.fmoney,0) as BackSummaryMoney
,hd.fname as DeptName
,isnull(prod.fshcs,0) as fOrderCount
,isnull(prod.fqty,0) as fOrderQty
,isnull(prod.fmoney,0) as fOrderMoney ");
strBuilder.Append(@"
,isnull(outd.fmoney,0) as OutSendMoney
,isnull(outd.fqty,0) as OutSendFqty
,isnull(outb.fmoney,0) as OutBackMoney
,isnull(outb.fqty,0) as OutBackNums
from bas_customer as cust
left join sendData as ab on ab.fcustid=cust.fid
left join sendSumData as abb on abb.fcustid=cust.fid
left join sendBackData as ac on ac.fcustid=cust.fid
left join sendBackSumData as acc on acc.fcustid=cust.fid
最新发布
本文介绍了一种常见的Android开发中遇到的问题：当工程导入某个引用库时，可能会因为标签重复而导致构建失败。文章详细解释了错误产生的原因，并提供了解决方案。
扫一扫
683
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
