<?php
/**********************************************************************
***********************************************************************/

function get_users_for_po_generation()
{
    $sql = "SELECT DISTINCT 
                IF(ppd.updated_by > 0, ppd.updated_by, ppd.created_by) po_user_id, 
                IF(ppd.updated_by > 0, uu.real_name, uc.real_name) po_user_name  
            FROM 
                ".TB_PREF."pr_po_details ppd 
                INNER JOIN ".TB_PREF."users uc ON  ppd.created_by = uc.id 
                LEFT JOIN ".TB_PREF."users uu ON  ppd.updated_by = uu.id 
            WHERE 
                ppd.line_loc_code <> '' AND 
                ppd.po_line_id = 0 AND 
                ppd.supplier_id > 0  ";
                
                
	return db_query($sql, "Could not get_users_for_po_generation");
}



function get_projects_for_po_generation($po_user_id)
{

        $sql = "SELECT DISTINCT 
                    ppd.project_id,  
                    pm.description project_description 
                FROM 
                    ".TB_PREF."pr_po_details ppd ,
                    ".TB_PREF."projects_master pm 
                WHERE  
                    ppd.project_id = pm.project_id AND 
                    ppd.line_loc_code <> '' AND 
                    ppd.po_line_id = 0 AND 
                    ppd.supplier_id > 0 AND 
                    (ppd.updated_by > 0 AND ppd.updated_by = ".db_escape($po_user_id)." OR 
                    ppd.updated_by = 0 AND ppd.created_by = ".db_escape($po_user_id).") 
                ORDER BY pm.description ";
                
                
	return db_query($sql, "could not get all requisition_details");
}


function get_suppliers_for_po_generation($project_id, $po_user_id)
{

        $sql = "SELECT DISTINCT 
                    ppd.supplier_id,  
                    s.supp_name
                FROM 
                    ".TB_PREF."pr_po_details ppd ,
                    ".TB_PREF."suppliers s
                WHERE  
                    ppd.supplier_id = s.supplier_id AND 
                    ppd.line_loc_code <> '' AND 
                    ppd.po_line_id = 0 AND 
                    ppd.supplier_id > 0 AND 
                    ppd.project_id = ".db_escape($project_id)." AND 
                    (ppd.updated_by > 0 AND ppd.updated_by = ".db_escape($po_user_id)." OR 
                    ppd.updated_by = 0 AND ppd.created_by = ".db_escape($po_user_id).") 
                ORDER BY s.supp_name ";
                
                
	return db_query($sql, "could not get all requisition_details");
}


function get_po_delivery_address($location)
{

        $sql = "SELECT delivery_address, phone "
                . "FROM ".TB_PREF."locations "
                . "WHERE loc_code=".db_escape($location);
          
	return db_query($sql,"could not get location info");
}


function get_pr_allocation_lines_for_po_generation($project_id, $supplier_id, $po_user_id)
{

        $sql = "SELECT  
                    pr.pr_no, 
                    pr.project_id,
                    pm.description project_description,
                    prd.task_id,
                    pr.location,                     
                    pr.created_on,
                    sm.stock_id item_code, 
                    sm.description, 
                    prd.id pr_line_id,
                    prd.line_comment, 
                    ppd.id pr_po_line_id, 
                    ppd.order_quantity quantity, 
                    prd.units, 
                    ppd.supplier_id, 
                    ppd.required_date, 
                    ppd.order_unit_qty, 
                    ppd.order_unit_price, 
                    ppd.order_unit_price_discount, 
                    ppd.order_unit_conversion_factor,
                    s.supp_name,
                    ppd.order_unit,
                    ppd.order_quantity, 
                    ppd.order_price,
                    ppd.minimum_price,
                    ppd.minimum_price_discount,
                    ppd.min_price_supp_id,
                    ppd.line_loc_code,
                    l.location_name line_location_name,
                    ppd.row_state pr_po_row_state,
                    ppd.item_description pr_po_item_description, 
                    iu.name order_unit_name,
                    ppd.project_site_id,
                    ps.description project_site_name 
                    
                FROM 
                    ".TB_PREF."purchase_requisition_details prd 
                    INNER JOIN  ".TB_PREF."stock_master sm ON 
                            sm.stock_id = prd.item_code 
                    INNER JOIN ".TB_PREF."pr_po_details ppd ON 
                            prd.id = ppd.pr_line_id 
                    INNER JOIN ".TB_PREF."purchase_requisitions pr ON 
                            prd.requisition_id = pr.pr_no 
                    INNER JOIN ".TB_PREF."item_units iu ON 
                            iu.abbr = ppd.order_unit 
                    INNER JOIN ".TB_PREF."suppliers s ON 
                            ppd.supplier_id = s.supplier_id 
                    INNER JOIN ".TB_PREF."projects_master pm ON 
                            pr.project_id = pm.project_id  
                    INNER JOIN ".TB_PREF."locations l ON 
                            ppd.line_loc_code = l.loc_code 
                    LEFT JOIN ".TB_PREF."project_sites ps ON 
                            ppd.project_site_id = ps.id 
                         
                WHERE  
                    ppd.po_line_id = 0 AND 
                    ppd.line_loc_code <> '' AND 
                    ppd.supplier_id = ".$supplier_id." AND 
                    (ppd.updated_by > 0 AND ppd.updated_by = ".db_escape($po_user_id)." OR 
                    ppd.updated_by = 0 AND ppd.created_by = ".db_escape($po_user_id).") AND 
                    ppd.project_id = ".db_escape($project_id)." 
                ORDER BY pr.location,
                            sm.stock_id,
                            ppd.item_description,
                            ppd.order_unit,
                            ppd.order_unit_price, 
                            ppd.order_unit_price_discount, 
                            ps.description";
        
			$sql = "SELECT * 
                FROM ".TB_PREF."vw_open_pr_allocation_lines 
                WHERE supplier_id = ".$supplier_id." AND 
                    (updated_by > 0 AND updated_by = ".db_escape($po_user_id)." OR 
                    updated_by = 0 AND created_by = ".db_escape($po_user_id).") AND 
                    project_id = ".db_escape($project_id)." 
                ORDER BY location,
                            item_code,
                            description,
                            order_unit,
                            order_unit_price, 
                            order_unit_price_discount, 
                            description";
                
	return db_query($sql, "could not get all requisition_details");
}


function get_pr_po_line($pr_po_line_id)
{
    $sql = "SELECT *  
            FROM ".TB_PREF."pr_po_details 
            WHERE id=".db_escape($pr_po_line_id);
          
	$result = db_query($sql,"PR PO Line could not be retrieved.");
    
    return db_fetch($result);
}


function update_po_source_records($po_lines)
{
    foreach ($po_lines as $po_line){
        $usppl_msg = update_source_pr_po_lines($po_line);
        if($usppl_msg['success'] !== true){
            return $usppl_msg;
        }           
    }
    return array('success' => true, 'message' => "");
}

function update_source_pr_po_lines($po_line)
{
    foreach ($po_line->pr_po_lines as $pr_po_line){
        $num_rows = update_source_pr_po_line($pr_po_line['line_id'], $po_line->po_detail_rec, $pr_po_line['row_state']);
        if($num_rows != 1){
            return array('success' => false, 'message' => "Another user has updated pr po line ".$pr_po_line['line_id']);
        }
    }
    return array("success" => true, "message" => "");
}

function update_source_pr_po_line($pr_po_line_id, $po_line_id, $pr_po_line_row_state)
{
    $sql = "UPDATE ".TB_PREF."pr_po_details  
            SET po_line_id = ".db_escape($po_line_id).", 
                    row_state = ".db_new_row_state($pr_po_line_row_state)." 
            WHERE id = ".db_escape($pr_po_line_id)." AND 
                    po_line_id = 0 AND 
                    row_state = $pr_po_line_row_state";

    db_query($sql, "Could not update pr po line");
    return db_num_affected_rows();
}



?>
