gistfile1.sql

with q1
 as 
(select case :NeedDept
         when 1 then AccTrns_Dept
         else 0
        end AccTrns_Dept,         
        Main.AccTrnsHalf_Half MainHalf,
        coalesce(MainAttrs.ACA_ID,Main.ACCTRNSHALF_Accnt_ID) MainAcc,
        MainCode1.AccAnICode_ICode MC1,
        MainCode2.AccAnICode_ICode MC2, 

        coalesce(CorrAttrs.ACA_ID,Corr.ACCTRNSHALF_Accnt_ID) CorrAcc,
        CorrCode1.AccAnICode_ICode CC1,
        CorrCode2.AccAnICode_ICode CC2,
        case 
         when Main.AccTrnsHalf_Inverse=Corr.AccTrnsHalf_Inverse then 1
         else 0
        end SameInverse, 
        case 
         when :OneHalfMode=1 then Main.AccTrnsHalf_Inverse
         else 1
        end MainInv,
        case 
         when :OneHalfMode=1 then Corr.AccTrnsHalf_Inverse
         else 1
        end CorrInv,
        case 
         when :NeedSign=1 and AccTrns_MainTotal>=0 then 1
         when :NeedSign=1 and AccTrns_MainTotal<0 then -1
         else 0
        end TotalSign, 

        sum(AccTrns_MainTotal) AccTrns_MainTotal
     from AccntTransHalfs Main
     join AccntTrans on AccTrns_XmlObj_ID+0=Main.AccTrnsHalf_AccTrns_ID
     join AccntTransHalfs Corr on Corr.AccTrnsHalf_AccTrns_ID=Main.AccTrnsHalf_AccTrns_ID and
                                  Corr.AccTrnsHalf_Half=-Main.AccTrnsHalf_Half
     join AccountIncidences FltMain on FltMain.AccInc_TblAcc_ID=Main.ACCTRNSHALF_Accnt_ID+0 and 
                                       FltMain.AccInc_FltAcc_ID=:fltaccid_main
     join AccountIncidences FltCorr on FltCorr.AccInc_TblAcc_ID=Corr.ACCTRNSHALF_Accnt_ID+0 and 
                                       FltCorr.AccInc_FltAcc_ID=:fltaccid_corr

     left join AccntAnIntCodes MainCode1 on MainCode1.AccAnICode_AccTrns_ID=AccTrns_XmlObj_ID+0 and 
                                            MainCode1.AccAnICode_Half=Main.AccTrnsHalf_Half+0 and
                                            MainCode1.AccAnICode_Kind=:MainCode1

     left join AccntAnIntCodes MainCode2 on MainCode2.AccAnICode_AccTrns_ID=AccTrns_XmlObj_ID+0 and 
                                            MainCode2.AccAnICode_Half=Main.AccTrnsHalf_Half+0 and
                                            MainCode2.AccAnICode_Kind=:MainCode2

     left join AccntAnIntCodes CorrCode1 on CorrCode1.AccAnICode_AccTrns_ID=AccTrns_XmlObj_ID+0 and 
                                            CorrCode1.AccAnICode_Half=Corr.AccTrnsHalf_Half+0 and
                                            CorrCode1.AccAnICode_Kind=:CorrCode1

     left join AccntAnIntCodes CorrCode2 on CorrCode2.AccAnICode_AccTrns_ID=AccTrns_XmlObj_ID+0 and 
                                            CorrCode2.AccAnICode_Half=Corr.AccTrnsHalf_Half+0 and
                                            CorrCode2.AccAnICode_Kind=:CorrCode2



     left join AccountIncidences MainLvl 
      join AccountAttributes MainAttrs on MainAttrs.ACA_ID=MainLvl.AccInc_FltAcc_ID
     on MainLvl.AccInc_TblAcc_ID=Main.ACCTRNSHALF_Accnt_ID+0 and 
        MainAttrs.ACA_Level=:acclevel_main

     left join AccountIncidences CorrLvl 
      join AccountAttributes CorrAttrs on CorrAttrs.ACA_ID=CorrLvl.AccInc_FltAcc_ID
     on CorrLvl.AccInc_TblAcc_ID=Corr.ACCTRNSHALF_Accnt_ID+0 and 
        CorrAttrs.ACA_Level=:acclevel_corr


     where AccTrns_Datetime>=:month_beg and AccTrns_Datetime<(:month_end+1) and 
           (AccTrns_Dept=:dept_id or :dept_id=0 or (:dept_id=1 and AccTrns_Dept<>2))
     group by 1,2,3,4,5,6,7,8,9,10,11,12
)
select 
  AccTrns_Dept, MainHalf, TotalSign,
  MainAcc, MC1, MC2,
  CorrAcc, CC1, CC2,
  case MainInv*MainHalf
    when 1 then  MainHalf*AccTrns_MainTotal
    else 0
  end DTotal,
  case MainInv*MainHalf
    when -1 then  -MainHalf*AccTrns_MainTotal
    else 0
  end KTotal,
  case (1-MainHalf)*2+(1-MainInv)+(1-CorrInv)/2
   when 0 then MainAcc
   when 1 then MainAcc||'/'||CorrAcc||'-'
   when 2 then ''
   when 3 then CorrAcc||'-'
   when 4 then CorrAcc
   when 5 then ''
   when 6 then CorrAcc||'/'||MainAcc||'-'
   when 7 then MainAcc||'-'
 end DAcc,
  case (1-MainHalf)*2+(1-MainInv)+(1-CorrInv)/2
   when 0 then CorrAcc
   when 1 then ''
   when 2 then MainAcc||'-/'||CorrAcc
   when 3 then MainAcc||'-'
   when 4 then MainAcc
   when 5 then CorrAcc||'-/'||MainAcc
   when 6 then ''
   when 7 then CorrAcc||'-'

 end KAcc
         
  from q1
   where
     (:mode<>1 or (MainHalf*MainInv=1)) and
     (:mode<>3 or (MainHalf=1)) and
     (:mode<>4 or (MainHalf=-1)) and

      --hide one-side transacts with same accounts 
     (:hidesame=0 or (MainAcc<>CorrAcc) or (SameInverse=1))