

--- report
-- gl trans sales by day
-- sales tax on a day
-- total sales orders on a day. (based on on invoices)

SELECT
    distdate,
    sum(no_invoices) as no_invoices,
    sum(invoiced_value) as invoiced_value,
    sum(gltrans_value) as  gltrans_value,
    sum(taxhist_value) as taxhist_value,
    sum(taxhist_freight_value) as taxhist_freight_value 
FROM


(
    SELECT
        
         -- total invoiced value
         
        invchead_invcdate as distdate,
        count(distinct invchead_id) as no_invoices,
        ROUND(sum(invcitem_billed *
              currtobase(
                      invchead_curr_id,
                      invcitem_custprice,
                      invchead_invcdate
              )),0) as invoiced_value,
        0 as gltrans_value,
        0 as taxhist_value,
            0 as taxhist_freight_value
           
        FROM
           
              invcitem
         
          LEFT JOIN
              invchead
          ON
              invchead_id = invcitem_invchead_id
        WHERE
            NOT invchead_void
        
        
        GROUP BY
            invchead_invcdate 
        
    UNION
        SELECT
            gltrans_date as distdate,
            0 as no_invoices,
            0 as invoice_value,
            sum(gltrans_amount) as gltrans_value,
            0 as taxhist_value,
            0 as taxhist_freight_value
        FROM
            gltrans
        WHERE
            gltrans_accnt_id IN  (SELECT  salesaccnt_sales_accnt_id from salesaccnt)
         GROUP BY gltrans_date
        
    UNION
        SELECT
            taxhist_distdate as distdate,
            0 as no_invoices,
            0 as invoice_value,
            0 as gltrans_value,
            
            sum(CASE
                WHEN (cohist_misc_type = 'F') THEN
                  0
                ELSE
                    ROUND(taxhist_tax / taxhist_curr_rate,2)
            END) AS taxhist_value,
            
            sum(CASE
                WHEN (cohist_misc_type = 'F') THEN
                  ROUND(taxhist_tax / taxhist_curr_rate,2)
                ELSE 0
            END) AS taxhist_freight_value 
            
        FROM
            cohisttax
        JOIN
            cohist
            ON
            (cohist_id=taxhist_parent_id)
        GROUP BY taxhist_distdate
        
        
        
          
	 
        
        
        
        
        
        
        
        
        
        
        
            
) x
    GROUP BY distdate
    ORDER BY
        distdate ASC