费用表 财务 管理 销售
--费用表
select (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end) as costtype,substr(a.prepareddatev,0,4) as time_year,sum(a.debitamount/10000) as amount from gl_detail a
left join bd_accasoa b on a.pk_accasoa = b. pk_accasoa
where substr(b.dispname,0,4) in (6601,6602,6603,6606) and substr(a.prepareddatev,0,4) >= '2023' and substr(a.prepareddatev,0,4) <= '2024' and substr(prepareddatev,6,5) <= '12-31'
group by (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end),substr(a.prepareddatev,0,4)
分组织费用表
select (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end) as costtype,substr(a.prepareddatev,0,4) as time_year,e.name as company,sum(a.debitamount/10000) as amount from gl_detail a
left join bd_accasoa b on a.pk_accasoa = b. pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
where substr(a.prepareddatev,0,4) >= '2023' and substr(a.prepareddatev,0,4) <= '2024' and substr(prepareddatev,6,5) <= '12-31'
--and (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end) = '${costtype}'
group by e.name,(case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end),substr(a.prepareddatev,0,4)
明细科目
select (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end) as costtype,e.name as company,b.name as capitaltype,substr(a.prepareddatev,0,4) as time_year,sum(a.debitamount/10000) as amount from gl_detail a
left join bd_accasoa b on a.pk_accasoa = b. pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
where 1=1
--and (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end) = '${costtype}'
and substr(a.prepareddatev,0,4) >= '2023' and substr(a.prepareddatev,0,4) <= '2024' and substr(prepareddatev,6,5) <= '12-31'
--and e.name = '${company}'
group by (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end),e.name,b.name,substr(a.prepareddatev,0,4)
资金表
select sum(a.debitamount)-sum(a.creditamount),substr(dispname,instr(dispname,'\')+1,4) as capitaltype from gl_detail a
left join bd_accasoa b on a.pk_accasoa = b. pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
left join gl_docfree1 f on a.assid = f.assid
left join bd_bankaccsub g on g.pk_bankaccsub = f.F11
left join bd_bankaccbas h on h.pk_bankaccbas = g.pk_bankaccbas
where (h.accattribute != '3' and h.accnum != '11710076801100000856' or h.accattribute is null )
------此处h.accattribute != '3'用于过滤掉专用账户，这个专用账户为客户暂存的预付款或者保证金，企业得暂管
and (substr(b.dispname,0,instr(dispname,'\')-1) in ('1002','112101','112102') or substr(b.dispname,0,instr(dispname,'\')-1) like ('1001%'))
and a.yearv ='2024'
group by substr(dispname,instr(dispname,'\')+1,4)
科目余额表
select substr(a.prepareddatev,0,7) as y_month,b.dispname,substr(b.dispname,instr(b.dispname,'\',-1,1)+1) as title,
(case when length(h.name) >0 then h.name end)||
(case when length(i.name) >0 then i.name end)||
(case when length(k.name) >0 then k.name end)||
(case when length(c.name) >0 then c.name end)||
(case when length(l.name) >0 then l.name end) as main
,replace(e.name,'-基准账簿','') as company,sum(a.debitamount)-sum(a.creditamount) as jie,sum(a.creditamount)-sum(a.debitamount) as dai from gl_detail a
left join bd_accasoa b on a.pk_accasoa = b.pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
left join gl_docfree1 f on a.assid = f.assid
left join bd_customer h on f.F13 = h.pk_customer--客户档案
left join bd_supplier i on f.F14 = i.pk_supplier--供应商档案
left join bd_bankaccsub j on f.F11 = j.pk_bankaccsub --银行账户
left join bd_bankaccbas k on k.pk_bankaccbas = j.pk_bankaccbas
left join bd_marbasclass c on f.F16 = c.pk_marbasclass--物料基本信息
left join bd_psndoc l on f.F2 = l.pk_psndoc--人员档案
where substr(a.prepareddatev,0,7) <= '2024-09' and a.yearv = substr('2024',0,4)
--where substr(a.prepareddatev,0,7) <= '${time2}' and a.yearv = substr('${time2}',0,4)
--${if(len(company2) != 0,"and replace(e.name,'-基准账簿','') in ('"+company2 +"')","")}
group by substr(a.prepareddatev,0,7), b.dispname,replace(e.name,'-基准账簿',''),
(case when length(h.name) >0 then h.name end)||
(case when length(i.name) >0 then i.name end)||
(case when length(k.name) >0 then k.name end)||
(case when length(c.name) >0 then c.name end)||
(case when length(l.name) >0 then l.name end)
order by b.dispname
