预支单数据合计
--预支单数量 货款付款/费用付款 /非贸付款  付款类型为预付款   并且 7621-法人单位名称'山东钢铁集团国际贸易有限公司' 
--7623 济钢
--并且 排除 费用付款 中费用大类是财务费用类  并且  审核状态 ='待审核' '发送成功' 并且 没有删除
SELECT expanse_report_id ,fours AS fours,COUNT(invoice_code) AS 数量,
SUM(CASE WHEN curcy ='CNY' THEN amt WHEN curcy ='CNY' THEN amt*7.85 ELSE 0 end ) AS 金额
FROM (SELECT DISTINCT SUBSTR(a."expanse_report_id",0,1)  AS expanse_report_id,
b.fours AS fours,a.invoice_code,b.amt,b.curcy
FROM SGGMINFT.inf_BCFMP1 a left join SGGMIIP.paymgrctrl b on a.invoice_code=b.fno  
WHERE TO_CHAR(a.create_time,'YYYY-MM-DD HH:MI:SS') >= '2026-01-01 00:00:00' 
  AND TO_CHAR(a.create_time, 'YYYY-MM-DD HH:MI:SS')<= '2026-01-23 12:00:00'
  and b.payType like '%预付款%' --and b.fours='SGGM' 
  and ifnull(b.cateName,'') <> '财务费用类' 
  and ifnull(impBcStatus,'') in ('待审核','发送成功') 
  and b.del_flag<>1)
GROUP BY  fours ,"expanse_report_id" ;


报支单数据合计
SELECT expanse_report_id ,fours AS fours,COUNT(invoice_code) AS 数量,
SUM(CASE WHEN curcy ='CNY' THEN amt WHEN curcy ='CNY' THEN amt*7.85 ELSE 0 end ) AS 金额
FROM (SELECT  DISTINCT SUBSTR(a."expanse_report_id",0,1)  AS expanse_report_id,
b.fours AS fours,a.invoice_code,b.totalAmt AS amt,IFNULL(b.curcy,'CNY') AS curcy
FROM SGGMINFT.inf_BCFMP1 a 
left join SGGMIIP.purMultiSettle b on a.invoice_code=b.fno 
WHERE TO_CHAR(a.create_time,'YYYY-MM-DD HH:MI:SS') >= '2026-01-01 00:00:00' 
  AND TO_CHAR(a.create_time, 'YYYY-MM-DD HH:MI:SS')<= '2026-01-23 23:59:59'
  --and b.fours='JGGM' 
  and ifnull(b.impBcStatus,'') in ('待审核','财务审核','财务复核','部分付款','全部付款') 
  and IFNULL(b.del_flag,'0')<>'1')
GROUP BY  fours ,"expanse_report_id" ;


-- 销售发票数据
select 
b2.serviceType ,b2.totalAmt,b2.curcy ,
b2.create_by_name  制单人 ,
case when ifnull(b2.fmodalid,0)=36  then '销售发票'
	 else '' end  模块  , 
a.bill_no  
from  
(
	select distinct  bill_no  from SGGMINFT.INF_BCFMS1 
	where proc_status='1'  
) a  
left join saleSettle b2 on a.bill_no=b2.fno 

where b2.fours='SGGM' 
and ifnull(b2.del_flag,0)<>1  ;

---成本结转
select bccode 账套代码,billType 单据类型 ,count(id) 抛帐数量,curcy,sum(amt) 原币金额 ,sum(cnyamt) 人民币金额 
from GwomiCtrl 
where impBcStatus='抛账成功' and ifnull(del_flag,0)<>1 and 	
billType in ('C01001')
group by bccode,billType,curcy 
order by bccode,billType,curcy

--通用抛账明细表（暂估）
select 
b.bccode ,
b.billType, -- 单据类型
a.curcy,
count(b.id)id ,
sum(case when b.table1 like '%冲销%' then 0-CAST(ROUND( ifnull(a.ntAmt,0)+ ifnull(a.tzamt1,0) ,2) AS DECIMAL(18, 2)) else CAST(ROUND( ifnull(a.ntAmt,0)+ ifnull(a.tzamt1,0) ,2) AS DECIMAL(18, 2))   end) as billSubAmt,-- 单据金额(原币）
sum(case when b.table1 like '%冲销%' AND IFNULL(fcat,0)<>1 then 0-CAST(ROUND( ifnull(a.ntCnyAmt,0)+ ifnull(a.tzamt2,0) ,2)AS DECIMAL(18, 2))  else CAST(ROUND( ifnull(a.ntCnyAmt,0)+ ifnull(a.tzamt2,0) ,2)AS DECIMAL(18, 2)) end ) as billSubAmtRmb-- 单据金额(折人民币）
from Gwomidtl a 
left join  GwomiCtrl b on a.rid=b.id
where nvl(a.del_flag,0)<>1 
and b.impBcStatus='抛账成功' 
and	a.billType in ('I01001')
and ifnull(b.del_flag,0)<>1  	
and b.placed='2'
group by b.bccode ,b.billType, a.curcy