        -- Group: vendorAPHistory
        -- Name: detail
        -- Notes: used by dspVendorAPHistory
        -- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple.
        -- See www.xtuple.com/CPAL for the full text of the software license.
        
        --http://www.bloomandgrowdirect.com/sandbox/hk.php/Roo/Metasql?_group=apopen&_name=bydate&csvTitles=*&csvCols=*&limit=100

-- JSON START
-- {"TITLES" : [ "Date",  "no# gl transactions", "Difference AP/GL exc JE",  "-", "GL change", "Balance", "-", "GL  Journal Entry(sum)", "GL Credit Memo Appy(sum)", "-", "Vouchers", "Cheques", "Voided Cheques", "AP Applications" , "Balance", "Ap Aging" ] }
-- {"COLS" : [ "docdate", "gl_count",            "ap_gl_diff"  , "pad1", "gl_total",     "pad1", "pad1", "gl_jetotal", "gl_cmtotal", "pad1", "ap_voucher", "ap_check", "ap_voidcheck", "ap_apply", "pad1" , "running_aging"] }
-- JSON END


--- note on this
-- gl_cmtotal is already included in gl_Total
 
SELECT
    docdate,
    gl_count,
    gl_total,
    
    '' as pad1,
    
 
    '' as ap_running,
    
    gl_jetotal,
    
    
    gl_cmtotal,
    
    
    ap_voucher,
    ap_check,
    ap_voidcheck,
    ap_apply,
    (ap_voucher +     ap_check +    ap_voidcheck) as ap_total,
    
    (ap_voucher +     ap_check +    ap_voidcheck) - (gl_total +gl_jetotal)  as ap_gl_diff,
    
    (select sum(apaging_total_val ) from apaging(docdate,false)) as running_aging

    
    FROM 
    (
    
    
    
        SELECT
            docdate,
            sum(line_voucher) as ap_voucher,
            sum(line_check) as ap_check,
            sum(line_voidcheck) as ap_voidcheck,
            sum(line_apply) as ap_apply,
            
        -- 194 is the AP in HK..
            (SELECT sum(gltrans_amount)  FROM
                        gltrans
                    INNER JOIN
                        apaccnt
                    ON
                            apaccnt_ap_accnt_id = gltrans_accnt_id
                        AND
                            apaccnt_vendtype ='.*'
                    WHERE
                            gltrans_source != 'G/L'
                        AND
                            gltrans_date =  docdate
                        AND
                            NOT gltrans_deleted
                    ) as gl_total,
                    
                    
            (SELECT sum(gltrans_amount)  FROM gltrans
                INNER JOIN apaccnt ON apaccnt_ap_accnt_id = gltrans_accnt_id AND apaccnt_vendtype ='.*'
                    where
                            gltrans_source != 'G/L'
                        AND
                            gltrans_date =  docdate
                        AND
                            NOT gltrans_deleted 
                        AND
                            gltrans_notes = 'CM Application'
                    ) as gl_cmtotal,
             
             (SELECT sum(gltrans_amount)  FROM gltrans
                       INNER JOIN apaccnt ON apaccnt_ap_accnt_id = gltrans_accnt_id AND apaccnt_vendtype ='.*'
                        where
                        gltrans_source = 'G/L'
                        AND gltrans_date =  docdate
                        
                        AND NOT gltrans_deleted 
                    
                    ) as gl_jetotal,
                     
                    
            (SELECT count(gltrans_id)
                    FROM gltrans
                       INNER JOIN apaccnt ON apaccnt_ap_accnt_id = gltrans_accnt_id AND apaccnt_vendtype ='.*'
                    where
                        gltrans_source != 'G/L'
                        AND gltrans_date =  docdate
                        
                        AND NOT gltrans_deleted
            ) as gl_count
          
            FROM
           
    
            (
                 
                 
                     
                     SELECT  
                            apopen_id,
                            1 as line_type,
                           apopen_distdate AS docdate,
                             CASE WHEN (apopen_doctype='C') THEN ((CASE WHEN apopen_amount  = 0.0 THEN apopen_discountable_amount ELSE apopen_amount  END)) / apopen_curr_rate * -1.0
                               ELSE (CASE WHEN apopen_amount  = 0.0 THEN apopen_discountable_amount ELSE apopen_amount  END)/ apopen_curr_rate
                             END AS
                                  line_voucher,
                             0 as line_check,
                             0 as line_voidcheck,
                             0 as line_apply
                             
                             
                            
                            
                            
                            
                     FROM apopen 
                     WHERE (
                         (true)
                     <? if exists("vend_id") ?>
                          AND (apopen_vend_id=<? value("vend_id") ?>)
                     <? endif ?>
                     <? if exists("startDate") ?>     
                         AND (apopen_distdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
                     <? endif ?>
                     -- if we try and use the date to determine reversed it does not help.
                     
                     
                      AND NOT apopen_isreversed(apopen_id, apopen_docnumber, apopen_doctype, apopen_status)
             
                     
                      )
               
               
    -- a column with the apapply totals..           
           UNION
                    SELECT
                              apapply_id as  apopen_id,
                             9 as line_type,
                              apapply_postdate AS docdate,
                             0 as line_voucher,
                             
                              0 as line_check,
                             
                            
                             0 as line_voidcheck,
                            (apapply_target_paid / ar_target.apopen_curr_rate) *  (CASE WHEN ar_target.apopen_doctype IN  ('D', 'V') THEN -1 ELSE 1 END) as line_apply
                             

            FROM
                apapply
            LEFT OUTER JOIN
                apopen ar_target
                ON apapply_target_apopen_id = ar_target.apopen_id
            LEFT OUTER JOIN
                apopen ar_source
                ON apapply_source_apopen_id = ar_source.apopen_id
                          
                     WHERE (
                     apapply_target_apopen_id > 0
                    AND
                    -- ignore check applications - as they are already recorded below.???
                     apapply_source_doctype != 'K'
                     
                     <? if exists("vend_id") ?>    
                         AND (apapply_vend_id=<? value("vend_id") ?>)
                         AND (apapply_vend_id=<? value("vend_id") ?>)
                     <? endif ?>
                     <? if exists("startDate") ?>     
                         AND (apapply_postdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
                     <? endif ?>
                     )
          
                      
                        
               
               
               
               
               
               
               
               
               
               
               
               
               
               
                     
                                   
        UNION
                         SELECT
                              checkitem_id as  apopen_id,
                             2 as line_type,
                              checkhead_checkdate  AS docdate,
                             0 as line_voucher,
                             currtobase( checkitem_curr_id, checkitem_amount,apapply_postdate ) * -1 as line_check,
                             0 as line_voidcheck,
                             0 as line_apply
                             
                           
                     FROM
                             checkitem
                         LEFT JOIN
                             checkhead
                         ON
                             checkitem_checkhead_id = checkhead_id
                          LEFT JOIN
                             apapply
                         ON
                             apapply_journalnumber = checkhead_journalnumber
                              
                     WHERE (
                         checkhead_recip_type = 'V'
                      --   AND NOT checkhead_deleted  
                         AND NOT checkhead_void
                         
                         AND checkitem_apopen_id IS NOT NULL
                         
                     <? if exists("vend_id") ?>    
                         AND (checkhead_recip_id=<? value("vend_id") ?>)
                         AND (checkhead_recip_id=<? value("vend_id") ?>)
                     <? endif ?>
                     <? if exists("startDate") ?>     
                         AND (checkhead_checkdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
                     <? endif ?>
                     )
          
                      
                      -- finally union with deleted entries..
                      
        UNION
                      
                      -- code seems to indicate teh apopen close date is when it get's applied to gl.
                      -- however tests are indicating that it's the check date..
                      
                      SELECT
                           checkhead_id as  apopen_id,
                       3 as line_type,
                        checkhead_checkdate  AS docdate,
                             0 as line_voucher,
                           0 as line_check,
                          currtobase( checkitem_curr_id, checkitem_amount,apopen_closedate )   as line_voidcheck,
                             0 as line_apply
                       
                            
                      FROM
                              checkitem
                          LEFT JOIN
                              checkhead
                          ON
                              checkitem_checkhead_id = checkhead_id
                          LEFT JOIN
                              apopen
                          ON
                              apopen_id = checkitem_apopen_id 
                              
                              
                      WHERE (
                          checkhead_recip_type = 'V'
                          
                          --AND NOT checkhead_deleted
                          AND checkitem_apopen_id IS NOT NULL
                          
                          AND checkhead_deleted
                         AND NOT checkhead_void
                          
                      <? if exists("vend_id") ?>    
                          AND (checkhead_recip_id=<? value("vend_id") ?>)
                          AND (checkhead_recip_id=<? value("vend_id") ?>)
                      <? endif ?>
                      <? if exists("startDate") ?>     
                          AND (checkhead_checkdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
                      <? endif ?>
                      )
                      
         
         
         
         --- for voided checks, 
         UNION
               SELECT
                           checkitem_id as  apopen_id,
                           4 as line_type,
                             checkhead_checkdate  AS docdate,
                          0 as line_voucher,
                           0 as line_check,
                           currtobase( checkitem_curr_id, checkitem_amount,apapply_postdate ) * -1     as line_voidcheck,
                             0 as line_apply
                           
                         
                     FROM
                             checkitem
                         LEFT JOIN
                             checkhead
                         ON
                             checkitem_checkhead_id = checkhead_id
                          LEFT JOIN
                             apapply
                         ON
                             apapply_journalnumber = checkhead_journalnumber
                              
                     WHERE (
                     
                     
                     
                     
                         checkhead_recip_type = 'V'
                     
                        AND checkhead_void
                         AND   checkhead_voided IS NOT NULL   
                         AND checkitem_apopen_id IS NOT NULL
                         
                     <? if exists("vend_id") ?>    
                         AND (checkhead_recip_id=<? value("vend_id") ?>)
                         AND (checkhead_recip_id=<? value("vend_id") ?>)
                     <? endif ?>
                     <? if exists("startDate") ?>     
                         AND (checkhead_checkdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
                     <? endif ?>
                     )
          
         
         
         
         
         UNION
             -- the check void..
           -- the void date is based on the journal number...
                      
                      SELECT
                       checkhead_id as  apopen_id,
                          5 as line_type,
                           checkhead_voided  AS docdate,
                          0 as line_voucher,
                           0 as line_check,
                          currtobase( checkitem_curr_id, checkitem_amount,checkhead_voided )      as line_voidcheck,
                             0 as line_apply
                           
                          
                            
                      FROM
                              checkitem
                          LEFT JOIN
                              checkhead
                          ON
                              checkitem_checkhead_id = checkhead_id
                          
                              
                              
                      WHERE (
                          checkhead_recip_type = 'V'
                           
                          AND checkitem_apopen_id IS NOT NULL
                           
                          AND checkhead_void
                         AND   checkhead_voided IS NOT NULL
                          
                      <? if exists("vend_id") ?>    
                          AND (checkhead_recip_id=<? value("vend_id") ?>)
                          AND (checkhead_recip_id=<? value("vend_id") ?>)
                      <? endif ?>
                      <? if exists("startDate") ?>     
                          AND (checkhead_voided BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
                      <? endif ?>
                      )
                      GROUP BY docdate, apopen_id, line_type,checkitem_curr_id,checkitem_amount
         
                
          
         UNION
                SELECT
                        0 as  apopen_id,
                        12 as line_type,
                        gltrans_date AS docdate,
                       0 as line_voucher,
                       0 as line_check,
                       0 as line_voidcheck,
                       0 as line_apply
                       
                      FROM
                              gltrans
                          INNER JOIN
                              apaccnt
                          ON
                                  apaccnt_ap_accnt_id = gltrans_accnt_id
                              AND
                                  apaccnt_vendtype ='.*'
           
             
         
        ) x
       GROUP BY docdate
    
    ) z
  --  WHERE
  --    1=1 OR  ABS(ap_total - gl_total) > 1.0
    ORDER BY  docdate;
    
        