---  Calc value tax on an order..

      
  
CREATE OR REPLACE FUNCTION calcsalesordertax(integer)
  RETURNS numeric AS
$BODY$
DECLARE
  pCoheadid ALIAS FOR $1;
  _tax NUMERIC := 0;
  
BEGIN

  
  SELECT COALESCE(SUM(tax), 0) INTO _tax
  FROM ( SELECT ROUND(SUM(taxdetail_tax), 2) AS tax
         FROM tax JOIN calculateTaxDetailSummary('S', pCoheadid, 'T') ON (taxdetail_tax_id=tax_id)
         GROUP BY tax_id ) AS data;

  
  RETURN _tax;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calcsalesordertax(integer)
  OWNER TO admin;
  
    
    
    
    
--  Calc value of order items

    
    
    
CREATE OR REPLACE FUNCTION calcsalesordersubtotal(integer)
  RETURNS numeric AS
$BODY$
DECLARE
  pCoheadid ALIAS FOR $1;
  _subtotal NUMERIC := 0;
  
BEGIN

  
  SELECT COALESCE(SUM(ROUND((coitem_qtyord * coitem_qty_invuomratio) *
                            (coitem_price / coitem_price_invuomratio), 2)), 0) INTO _subtotal
  FROM coitem
  WHERE (coitem_cohead_id=pCoheadid);
  RETURN _subtotal;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calcsalesordersubtotal(integer)
  OWNER TO admin;



  
  
 -- calcs the discount based on the value of the Z-LIST-DISCOUNT item(s)   
CREATE OR REPLACE FUNCTION calcsalesorderlistdiscount(integer)
  RETURNS numeric AS
$BODY$
DECLARE
  pCoheadid ALIAS FOR $1;
  _subtotal NUMERIC := 0;
  
BEGIN

  
  SELECT COALESCE(SUM(ROUND((coitem_qtyord * coitem_qty_invuomratio) *
                            (coitem_price / coitem_price_invuomratio), 2)), 0) INTO _subtotal
  FROM coitem
  WHERE
    (coitem_cohead_id=pCoheadid)
    AND
    coitem_itemsite_id IN (
            SELECT itemsite_id FROM itemsite LEFT JOIN item ON itemsite_item_id = item_id WHERE item_number ='Z-LIST-DISCOUNT'
    );
    
  
  RETURN _subtotal;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calcsalesorderlistdiscount(integer)
  OWNER TO admin;





















-- qty..

CREATE OR REPLACE FUNCTION calcsalesorderqty(integer)
  RETURNS numeric AS
$BODY$
DECLARE
  pCoheadid ALIAS FOR $1;
  _subtotal NUMERIC := 0;
  
BEGIN

  
  SELECT COALESCE(SUM(ROUND((coitem_qtyord * coitem_qty_invuomratio)  , 2)), 0) INTO _subtotal
  FROM coitem
  WHERE (coitem_cohead_id=pCoheadid);
  RETURN _subtotal;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calcsalesorderqty(integer)
 OWNER TO admin;


  ---  Calc how much has been shipped..
   
     
CREATE OR REPLACE FUNCTION calcsalesordershipped(integer)
  RETURNS numeric AS
$BODY$
DECLARE
  pCoheadid ALIAS FOR $1;
  _subtotal NUMERIC := 0;
  
BEGIN

  
  SELECT COALESCE(SUM(ROUND((shipitem_qty * coitem_qty_invuomratio) *
                            (coitem_price / coitem_price_invuomratio), 2)), 0) INTO _subtotal
        FROM shipitem
            LEFT JOIN shiphead ON shipitem_shiphead_id = shiphead_id
            LEFT JOIN coitem ON shipitem_orderitem_id = coitem_id
            
  WHERE (coitem_cohead_id=pCoheadid) AND (shiphead_shipped = true);
  
  RETURN _subtotal;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calcsalesordershipped(integer)
  OWNER TO admin;

--qty  
CREATE OR REPLACE FUNCTION calcsalesordershippedqty(integer)
  RETURNS numeric AS
$BODY$
DECLARE
  pCoheadid ALIAS FOR $1;
  _subtotal NUMERIC := 0;
  
BEGIN

  
  SELECT COALESCE(SUM(ROUND((shipitem_qty * coitem_qty_invuomratio)  , 2)), 0) INTO _subtotal
  FROM shipitem LEFT JOIN coitem ON shipitem_orderitem_id = coitem_id
  LEFT JOIN shiphead ON shipitem_shiphead_id = shiphead_id
  WHERE (coitem_cohead_id=pCoheadid) AND (shiphead_shipped = true);
  
  RETURN _subtotal;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calcsalesordershippedqty(integer)
  OWNER TO admin;
   
  
--  Calc how much has can not be shipped. (currenly only checks 'R' item_type)
     
CREATE OR REPLACE FUNCTION calcsalesorderunshipable(integer)
  RETURNS numeric AS
$BODY$
DECLARE
  pCoheadid ALIAS FOR $1;
  _subtotal NUMERIC := 0;
  
BEGIN
  
   
  SELECT COALESCE(SUM(ROUND((coitem_qtyord * coitem_qty_invuomratio) *
                            (coitem_price / coitem_price_invuomratio), 2)), 0) INTO _subtotal
  FROM coitem
  LEFT JOIN
    itemsite
    ON
    itemsite_id = coitem_itemsite_id
  LEFT JOIN
    item
    ON
    item_id = itemsite_item_id
    
  WHERE (coitem_cohead_id=pCoheadid)
  AND
    item_type != 'P' 
  ;
  RETURN _subtotal;


END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calcsalesorderunshipable(integer)
  OWNER TO admin;
  



CREATE OR REPLACE FUNCTION calcsalesorderunshipableqty(integer)
  RETURNS numeric AS
$BODY$
DECLARE
  pCoheadid ALIAS FOR $1;
  _subtotal NUMERIC := 0;
  
BEGIN
  
   
  SELECT COALESCE(SUM(ROUND((coitem_qtyord * coitem_qty_invuomratio)  , 2)), 0) INTO _subtotal
  FROM coitem
  LEFT JOIN
    itemsite
    ON
    itemsite_id = coitem_itemsite_id
  LEFT JOIN
    item
    ON
    item_id = itemsite_item_id
    
  WHERE (coitem_cohead_id=pCoheadid)
  AND
    item_type != 'P' 
  ;
  RETURN _subtotal;


END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calcsalesorderunshipableqty(integer)
  OWNER TO admin;
  



 
  
  CREATE OR REPLACE FUNCTION calcsalesorderinvoiced(integer)
  RETURNS numeric AS
$BODY$
DECLARE
  pCoheadid ALIAS FOR $1;
  _subtotal NUMERIC := 0;
  
BEGIN

-- This would actually get the real invoice totals..
-- using aropen...
--  SELECT COALESCE(SUM(ROUND( invoiceTotal(invchead)) as _subtotal
--    FROM
--        invchead ON invcitem_invchead_id = invchead_id
--          LEFT JOIN coitem ON invcitem_coitem_id = coitem_id 
--    WHERE (coitem_cohead_id=pCoheadid) AND  invchead_void = false;
  
  
  SELECT COALESCE(SUM(ROUND((invcitem_billed * coitem_qty_invuomratio) *
                            (coitem_price / coitem_price_invuomratio), 2)), 0) INTO _subtotal
  FROM invcitem
     LEFT JOIN invchead ON invcitem_invchead_id = invchead_id
     LEFT JOIN coitem ON invcitem_coitem_id = coitem_id 
  WHERE (coitem_cohead_id=pCoheadid) AND  invchead_void = false;
  
  RETURN _subtotal;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calcsalesorderinvoiced(integer)
  OWNER TO admin;
  
  
   
   
  
  CREATE OR REPLACE FUNCTION calcsalesorderinvoicedqty(integer)
  RETURNS numeric AS
$BODY$
DECLARE
  pCoheadid ALIAS FOR $1;
  _subtotal NUMERIC := 0;
  
BEGIN

-- This would actually get the real invoice totals..
-- using aropen...
--  SELECT COALESCE(SUM(ROUND( invoiceTotal(invchead)) as _subtotal
--    FROM
--        invchead ON invcitem_invchead_id = invchead_id
--          LEFT JOIN coitem ON invcitem_coitem_id = coitem_id 
--    WHERE (coitem_cohead_id=pCoheadid) AND  invchead_void = false;
  
  
  SELECT COALESCE(SUM(ROUND((invcitem_billed * coitem_qty_invuomratio)  , 2)), 0) INTO _subtotal
  FROM invcitem
     LEFT JOIN invchead ON invcitem_invchead_id = invchead_id
     LEFT JOIN coitem ON invcitem_coitem_id = coitem_id 
  WHERE (coitem_cohead_id=pCoheadid) AND  invchead_void = false;
  
  RETURN _subtotal;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calcsalesorderinvoicedqty(integer)
  OWNER TO admin;
  
  
   
  

