<?php

function add_to_monthly_summary_sales($month_id, $department_id, $project_id,
                                        $value_type, $calculation_type, 
                                        $order_total, $discounts_total, 
                                        $taxes_total, $costs_total,
                                        $calculation_run_id)
{
    if($order_total == 0 && $discounts_total == 0 && $taxes_total == 0){
        return;
    }
	$sql = "INSERT INTO ".TB_PREF."monthly_summary_sales
                            ( month_id, department_id, project_id,
                              value_type, calculation_type, 
                              order_total, discounts_total, 
                              taxes_total, total_cost,
                              calculation_run_id ) 
                     
		VALUES( ".db_escape($month_id).",".db_escape($department_id).",".db_escape($project_id).",
                        ".db_escape($value_type).",".db_escape($calculation_type).",
                        ".db_escape($order_total).",".db_escape($discounts_total).",
                        ".db_escape($taxes_total).",".db_escape($costs_total).",
                        ".db_escape($calculation_run_id).")";

	db_query($sql,"add_to_monthly_summary_sales");
}


function get_monthly_summary_department_invoices($department_id, $month_begin_date, $month_end_date)
{
//    $sql=" SELECT SUM(inv.ov_amount) inv_total, SUM(inv.ov_discount) inv_discount_total, SUM(inv.ov_gst) inv_taxes_total,
    $sql=" SELECT SUM(dtd.unit_price * dtd.conversion_factor * dtd.tran_qty)inv_total, 
                    SUM(dtd.unit_price * dtd.discount_percent * dtd.conversion_factor * dtd.tran_qty) inv_discount_total, 
                    SUM(dtd.unit_tax * dtd.conversion_factor * dtd.tran_qty) inv_taxes_total,
                    SUM(dtd.standard_cost * dtd.conversion_factor * dtd.tran_qty) inv_cost_total,
                    pm.department_id, pm.project_id  
            FROM ".TB_PREF."debtor_trans inv 
                JOIN ".TB_PREF."sales_orders so 
                    ON so.order_no = inv.order_ 
                    AND so.trans_type = ".db_escape(ST_SALESORDER)."   
                JOIN 0_projects_master pm 
                    ON so.project_id = pm.project_id 
                JOIN ".TB_PREF."debtor_trans_details dtd 
                    ON dtd.debtor_trans_type = inv.type 
                    AND dtd.debtor_trans_no = inv.trans_no 
            WHERE inv.type = ".db_escape(ST_SALESINVOICE)."   
                AND inv.tran_date >= ".db_escape($month_begin_date)." 
                AND inv.tran_date <= ".db_escape($month_end_date)." 
                AND pm.department_id = ".db_escape($department_id)." 
            GROUP BY pm.project_id ";

    return db_query($sql,"get_monthly_summary_department_invoices");

}

function get_monthly_summary_department_credit_notes($department_id, $month_begin_date, $month_end_date)
{
    $sql=" SELECT SUM(cnd.unit_price * cnd.conversion_factor * cnd.tran_qty)cn_total, 
                    SUM(cnd.unit_price * cnd.discount_percent * cnd.conversion_factor * tran_qty) cn_discount_total, 
                    SUM(cnd.unit_tax * cnd.conversion_factor * cnd.tran_qty) cn_taxes_total,
                    SUM(cnd.standard_cost * cnd.conversion_factor * cnd.tran_qty) cn_cost_total,
                    pm.department_id, pm.project_id  
            FROM ".TB_PREF."debtor_trans cn  
                JOIN ".TB_PREF."debtor_trans inv 
                    ON cn.order_ = inv.trans_no 
                    AND INV.type = ".db_escape(ST_SALESINVOICE)."  
                JOIN ".TB_PREF."sales_orders so 
                    ON so.order_no = inv.order_ 
                    AND so.trans_type = ".db_escape(ST_SALESORDER)."   
                JOIN 0_projects_master pm 
                    ON so.project_id = pm.project_id 
                JOIN ".TB_PREF."debtor_trans_details cnd  
                    ON cnd.debtor_trans_type = cn.type 
                    AND cnd.debtor_trans_no = cn.trans_no 
            WHERE cn.type = ".db_escape(ST_CUSTCREDIT)."   
                AND cn.tran_date >= ".db_escape($month_begin_date)." 
                AND cn.tran_date <= ".db_escape($month_end_date)." 
                AND pm.department_id = ".db_escape($department_id)." 
            GROUP BY pm.project_id ";

    return db_query($sql,"get_monthly_summary_department_credit_notes");

}






function get_monthly_summary_department_invoice_receipts($department_id, $month_begin_date, $month_end_date)
{
//    $sql=" SELECT SUM(inv.ov_amount) inv_total, SUM(inv.ov_discount) inv_discount_total, SUM(inv.ov_gst) inv_taxes_total,
    $sql=" SELECT SUM(inv.alloc)receipts_total, pm.project_id  
            FROM ".TB_PREF."debtor_trans inv 
                JOIN ".TB_PREF."sales_orders so 
                    ON so.order_no = inv.order_ 
                    AND so.trans_type = ".db_escape(ST_SALESORDER)."   
                JOIN 0_projects_master pm 
                    ON so.project_id = pm.project_id  
            WHERE inv.type = ".db_escape(ST_SALESINVOICE)."   
                AND inv.tran_date >= ".db_escape($month_begin_date)." 
                AND inv.tran_date <= ".db_escape($month_end_date)." 
                AND pm.department_id = ".db_escape($department_id)." 
            GROUP BY pm.project_id ";

    return db_query($sql,"get_monthly_summary_department_invoices");

}

function get_monthly_summary_department_credit_note_payments($department_id, $month_begin_date, $month_end_date)
{
    $sql=" SELECT SUM(cn.alloc) payments_total 
            FROM ".TB_PREF."debtor_trans cn  
                JOIN ".TB_PREF."debtor_trans inv 
                    ON cn.order_ = inv.trans_no 
                    AND INV.type = ".db_escape(ST_SALESINVOICE)."  
                JOIN ".TB_PREF."sales_orders so 
                    ON so.order_no = inv.order_ 
                    AND so.trans_type = ".db_escape(ST_SALESORDER)."   
                JOIN 0_projects_master pm 
                    ON so.project_id = pm.project_id  
            WHERE cn.type = ".db_escape(ST_CUSTCREDIT)."   
                AND cn.tran_date >= ".db_escape($month_begin_date)." 
                AND cn.tran_date <= ".db_escape($month_end_date)." 
                AND pm.department_id = ".db_escape($department_id)." 
            GROUP BY pm.project_id ";

    return db_query($sql,"get_monthly_summary_department_credit_note_payments");

}

function delete_monthly_summary_sales($month_id)
{
    $sql = "DELETE FROM ".TB_PREF."monthly_summary_sales   
            WHERE month_id = ".db_escape($month_id)."";

    db_query($sql,"delete_monthly_summary_sales");
}

