<?php

function dashboard_get_sql_for_pos_invoices($from_date, $to_date, $entry_type_id=0, $type=-1) {
    $sql_pos_in = "SELECT h.type trans_type, h.trans_no, h.tran_date date, h.reference, h.project_id, h.entry_type_id, et.value_name entry_type_name, h.total amount
                    FROM ".TB_PREF."pos_invoices h   
                        LEFT JOIN ".TB_PREF."list_values et    
                            ON h.entry_type_id = et.id   
                    WHERE h.tran_date >= ".db_escape($from_date)." 
                        AND h.tran_date <= ".db_escape($to_date);

                if ($entry_type_id > 0) {
                    $sql_pos_in .= " AND h.entry_type_id = ".db_escape($entry_type_id);
                }

                if ($type > -1) {
                    $sql_pos_in .= " AND h.type = ".db_escape($type);
                }
                
                return $sql_pos_in;
}

function dashboard_get_sql_for_utility_bills($from_date, $to_date, $entry_type_id=0) {
    $sql_ub_in = "SELECT ".db_escape(ST_UTILITY_BILLS)." trans_type, h.id trans_no, h.date, h.reference, h.project_id, h.entry_type_id, et.value_name entry_type_name, 
                        (h.total + h.taxes) amount
                    FROM ".TB_PREF."utility_bills h   
                        LEFT JOIN ".TB_PREF."list_values et    
                            ON h.entry_type_id = et.id   
                    WHERE h.date >= ".db_escape($from_date)." 
                        AND h.date <= ".db_escape($to_date);

                if ($entry_type_id > 0) {
                    $sql_ub_in .= " AND h.entry_type_id = ".db_escape($entry_type_id);
                }
                
                return $sql_ub_in;
}

function dashboard_get_sql_for_purch_orders($from_date, $to_date) {
    $sql_po = "SELECT h.type trans_type, h.order_no trans_no, h.ord_date date, h.reference, h.project_id, '' entry_type_id, '' entry_type_name, (h.total - h.staxes) amount
                FROM ".TB_PREF."purch_orders h   
                    JOIN ".TB_PREF."purch_order_details d    
                        ON h.order_no = d.order_no   
                WHERE h.ord_date >= ".db_escape($from_date)." 
                    AND h.ord_date <= ".db_escape($to_date);
    
            $sql_po .= " GROUP BY h.order_no ";

            return $sql_po;
}

function dashboard_get_sql_for_salaries($from_date, $to_date) {
    $sql_sl = "SELECT ".db_escape(ST_PAYROLL)." trans_type, h.id trans_no, h.tran_date date, '' reference, '' project_id, '' entry_type_id, '' entry_type_name, 
                    (d.value) amount
                FROM ".TB_PREF."payroll_run_details d 
                    JOIN ".TB_PREF."payroll_runs h 
                        ON h.id = d.payroll_run_id 
                WHERE h.period_start_date < ".db_escape($to_date)."  
                    AND h.period_end_date > ".db_escape($from_date)."  
                    AND d.employee_code = ''     
                AND d.component = 'TOTAL_SALARY_COST'";
    
            return $sql_sl;
}

function dashboard_get_sales_pnl_types($from_date, $to_date, $entry_type_id=0) {   
    $sql_pos = dashboard_get_sql_for_pos_invoices($from_date, $to_date, $entry_type_id);
    $sql_ub = dashboard_get_sql_for_utility_bills($from_date, $to_date, $entry_type_id);
    $sql_po = dashboard_get_sql_for_purch_orders($from_date, $to_date);
    $sql_sl = dashboard_get_sql_for_salaries($from_date, $to_date);
    
    $all_sql = "$sql_pos UNION $sql_ub UNION $sql_po UNION $sql_sl";
    
        $sql = "SELECT entry_type_id, entry_type_name, SUM(amount) grand_total, trans_type, trans_no   
                FROM ($all_sql) tmp 
                GROUP BY trans_type  
                ORDER BY (trans_type = ".db_escape(ST_POS_INVOICE).") DESC, (trans_type = ".db_escape(ST_POS_CREDIT_NOTE).") DESC, trans_type ";

            return db_query($sql, "dashboard_get_sales_pnl_types"); 
}

function dashboard_get_sales_pnl_period_total($from_date, $to_date, $entry_type_id=0, $type=-1) {   
    switch ($type) {
        case ST_POS_INVOICE:
        case ST_POS_CREDIT_NOTE:
            $all_sql = dashboard_get_sql_for_pos_invoices($from_date, $to_date, $entry_type_id, $type);
            break;

        case ST_UTILITY_BILLS:
            $all_sql = dashboard_get_sql_for_utility_bills($from_date, $to_date, $entry_type_id);
            break;

        case ST_PURCHORDER:
            $all_sql = dashboard_get_sql_for_purch_orders($from_date, $to_date);
            break;

        case ST_PAYROLL:
            $all_sql = dashboard_get_sql_for_salaries($from_date, $to_date);
            break;

        default:
            $sql_pos = dashboard_get_sql_for_pos_invoices($from_date, $to_date, $entry_type_id);
            $sql_ub = dashboard_get_sql_for_utility_bills($from_date, $to_date, $entry_type_id);
            $sql_po = dashboard_get_sql_for_purch_orders($from_date, $to_date);
            $sql_sl = dashboard_get_sql_for_salaries($from_date, $to_date);

            $all_sql = "$sql_pos UNION $sql_ub UNION $sql_po UNION $sql_sl";
            break;
    }
    
        $sql = "SELECT SUM(amount) total, trans_no 
                FROM ($all_sql) tmp ";

            $result = db_query($sql, "dashboard_get_sales_pnl_period_total"); 
            return db_fetch($result);
}

?>