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

function get_open_requisition_details_for_indents($from_date=null, $to_date=null) {
        $sql = "SELECT  
                    pr.pr_no, 
                    pr.location, 
                    pr.created_on,
                    pr.project_id,
                    prd.task_id,
                    ic.item_code, 
                    ic.description, 
                    prd.id pr_line_id,
                    prd.line_comment, 
                    prd.quantity, 
                    prd.units,
                    prd.estimate_price, 
                    prd.required_date, 
                    pm.description project_description,
                    td.value_name task_description,
                    prd.requested_unit,
                    prd.requested_unit_qty,
                    sm.editable, 
                    prd.item_pr_description, 
                    sm.description sm_item_description
                FROM 
                    ".TB_PREF."item_codes ic 
                    INNER JOIN ".TB_PREF."purchase_requisition_details prd ON ic.item_code = prd.item_code
                    INNER JOIN ".TB_PREF."purchase_requisitions pr ON prd.requisition_id = pr.pr_no
                    LEFT JOIN ".TB_PREF."projects_master pm ON pr.project_id = pm.project_id
                    LEFT JOIN ".TB_PREF."project_tasks pt ON prd.task_id = pt.task_id 
                    LEFT JOIN ".TB_PREF."list_values td ON pt.type_id = td.id 
                    LEFT JOIN ".TB_PREF."stock_master sm 
                        ON sm.stock_id = prd.item_code
                WHERE prd.closed_for_indents = 0 ";
        
        if ($from_date !== null) {
            $sql .= " AND pr.pr_date >= ".db_escape($from_date);
        }
        if ($to_date !== null) {
            $sql .= " AND pr.pr_date <= ".db_escape($to_date);
        }
                
        $sql .= " ORDER BY pr.pr_no, prd.item_code, prd.units";
                
	return db_query($sql, "could not get all requisition_details");
}


function view_open_requisition_details_for_indents($pr_line_id)
{
        $sql = "SELECT  
                    pr.pr_no, 
                    pr.location,                    
                    ic.item_code, 
                    ic.description, 
                    prd.id pr_line_id,
                    prd.line_comment, 
                    prd.quantity, 
                    prd.units,
                    prd.estimate_price, 
                    prd.required_date, 
                    pm.description project_description,
                    td.value_name task_description,
                    prd.requested_unit,
                    prd.requested_unit_qty 
                FROM 
                    ".TB_PREF."item_codes ic 
                    INNER JOIN ".TB_PREF."purchase_requisition_details prd ON ic.item_code = prd.item_code
                    INNER JOIN ".TB_PREF."purchase_requisitions pr ON prd.requisition_id = pr.pr_no
                    LEFT JOIN ".TB_PREF."projects_master pm ON pr.project_id = pm.project_id
                    LEFT JOIN ".TB_PREF."project_tasks pt ON prd.task_id = pt.task_id 
                    LEFT JOIN ".TB_PREF."list_values td ON pt.type_id = td.id 
                WHERE prd.id =".db_escape($pr_line_id)." 
                ORDER BY pr.pr_no, prd.item_code, prd.units";
                
                
	 $result= db_query($sql, "could not get all requisition_details");
         return db_fetch($result);
}


function create_purchase_indent_details($indent_id, $pr_line_id, $tran_unit,
                                        $tran_qty, $line_comment, $conversion_factor, 
                                        $close, $close_only, $item_description)
{
    if(!$close_only){
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        $sql = "INSERT INTO ".TB_PREF."purchase_indent_details(indent_id,pr_line_id,
                            tran_unit,tran_qty,
                            conversion_factor, line_comment, 
                            item_description, 
                            created_by,created_on) 
                values(".db_escape($indent_id).",".db_escape($pr_line_id).","
                            .db_escape($tran_unit).",".db_escape($tran_qty).","
                            .db_escape($conversion_factor).",".db_escape($line_comment).","
                            .db_escape($item_description).","
                            .db_escape($created_by).",".db_escape($created_on)." )";
               
        db_query($sql, "could not insert purchase requisition indent details.");        
    }
    
        if($close || $close_only){
            close_purchase_requisition_lines_for_indents($pr_line_id); 
        }
    
    
}

function create_prchase_indent_header($supplier,$supplier_name,$supp_address,$location,$new_location,$delivery_required_date,$comment)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        $sql = "INSERT INTO ".TB_PREF."purchase_indents(supplier_id,new_supplier_name,supplier_address,location,
                                    new_location_address,required_date,comment,created_by,created_on) 
                            values(".db_escape($supplier).",".db_escape($supplier_name).",".db_escape($supp_address).",".db_escape($location).",".
                                    db_escape($new_location).",".db_escape($delivery_required_date).",".db_escape($comment).",".db_escape($created_by).",".
                                    db_escape($created_on)." )";
               
        db_query($sql, "could not insert purchase requisition indent.");        
  
        return db_insert_id();
    
}

function view_purchase_indent_details($pi_id)
{
   	$sql = "SELECT pi.id indent_id,
                       pi.supplier_id,
                       s.supp_name,
                       s.supp_address,
                       pi.new_supplier_name,
                       pi.supplier_address new_supp_address,
                       pi.new_location_address,
                       pi.required_date,
                       pi.location,
                       pi.comment,
                       prd.item_code,
                       sm.description,
                       pid.tran_unit,
                       pid.tran_qty qty,
                       pid.conversion_factor,
                       pid.line_comment,
                       s.supp_name,
                       l.location_name
		FROM ".TB_PREF."purchase_indents pi
                LEFT JOIN ".TB_PREF."purchase_indent_details pid ON
                    pi.id = pid.indent_id
                LEFT JOIN ".TB_PREF."purchase_requisition_details prd ON
                    prd.id = pid.pr_line_id
                LEFT JOIN ".TB_PREF."stock_master sm ON
                    sm.stock_id=prd.item_code
                LEFT JOIN ".TB_PREF."suppliers s ON
                    pi.supplier_id = s.supplier_id
                LEFT JOIN ".TB_PREF."locations l ON
                    pi.location = l.loc_code
		WHERE pid.pr_line_id = ".db_escape($pi_id);  
             
		
   	$result = db_query($sql, "The purchase indent cannot be retrieved");
        return $result;
}

function view_purchase_indent($pi_id)
{
   	$sql = "SELECT pi.id indent_id,
                       pi.supplier_id,
                       s.supp_name,
                       s.supp_address,
                       pi.new_supplier_name,
                       pi.supplier_address new_supp_address,
                       pi.new_location_address,
                       pi.required_date,
                       pi.location,
                       pi.comment,
                       prd.item_code,
                       sm.description stock_item_description,
                       sm.editable,
                       pid.tran_unit,
                       pid.tran_qty qty,
                       pid.conversion_factor,
                       pid.line_comment,
                       pid.item_description indent_item_description,
                       s.supp_name,
                       l.location_name,
                       prd.requisition_id,
                       prd.item_pr_description 
		FROM ".TB_PREF."purchase_indents pi
                LEFT JOIN ".TB_PREF."purchase_indent_details pid ON
                    pi.id = pid.indent_id
                LEFT JOIN ".TB_PREF."purchase_requisition_details prd ON
                    prd.id = pid.pr_line_id
                LEFT JOIN ".TB_PREF."stock_master sm ON
                    sm.stock_id=prd.item_code
                LEFT JOIN ".TB_PREF."suppliers s ON
                    pi.supplier_id = s.supplier_id
                LEFT JOIN ".TB_PREF."locations l ON
                    pi.location = l.loc_code
		WHERE pi.id = ".db_escape($pi_id);  
             
		
   	$result = db_query($sql, "The purchase indent cannot be retrieved");
        return $result;
}

function view_purch_indent_header($indent_id)
{
   	$sql = "SELECT pi.id indent_id,
                       pi.supplier_id,
                       pi.location,
                       l.location_name,
                       l.delivery_address,
                       s.supp_name,
                       s.supp_address,
                       pi.new_supplier_name,
                       pi.supplier_address new_supp_address,
                       pi.new_location_address,
                       pi.required_date,
                       pi.comment
		FROM ".TB_PREF."purchase_indents pi
                LEFT JOIN ".TB_PREF."suppliers s ON
                    pi.supplier_id = s.supplier_id
                LEFT JOIN ".TB_PREF."locations l ON
                    pi.location = l.loc_code
		WHERE pi.id = ".db_escape($indent_id);  
        
		
   	$result = db_query($sql, "The purchase indent cannot be retrieved");
        return db_fetch($result);
}


function get_sql_for_indent_search_completed($required_from,$required_to,$indent_number,$pr_number,$po_number,$supplier)
{
    $sql = "SELECT    DISTINCT pi.id indent_id,                       
                       s.supp_name,
                       pi.new_supplier_name,
                       l.location_name,                            
                       pi.new_location_address,
                       pi.required_date                      
		FROM ".TB_PREF."purchase_indents pi
                LEFT JOIN ".TB_PREF."purchase_indent_details pid ON
                    pi.id = pid.indent_id
                LEFT JOIN ".TB_PREF."purchase_requisition_details prd ON
                    prd.id = pid.pr_line_id  
                LEFT JOIN ".TB_PREF."pr_po_details pr_po ON
                    pr_po.pr_line_id = pid.pr_line_id
                LEFT JOIN ".TB_PREF."purch_order_details pod ON 
                    pod.po_detail_item = pr_po.po_line_id  
                LEFT JOIN ".TB_PREF."suppliers s ON
                    pi.supplier_id = s.supplier_id
                LEFT JOIN ".TB_PREF."locations l ON
                    pi.location = l.loc_code
		WHERE 1=1 ";
    
    
    if($indent_number > 0 || $pr_number > 0 || $po_number > 0){
            if($indent_number != '')
            {
                $sql .=" AND pi.id =".db_escape($indent_number);
                
            }
            if($pr_number != '')
            {
                $sql .=" AND pid.pr_line_id =".db_escape($pr_number);
            }
            if($po_number != '')
            {
                $sql .=" AND pod.order_no =".db_escape($po_number);
            }
    }
    else{
            if($supplier != '' )
            {
                $sql .=" AND pi.supplier_id =".db_escape($supplier);
            }
             
            $sql .=" AND pi.required_date >= ".db_escape($required_from)." AND pi.required_date <= ". db_escape($required_to);  
    }
        return $sql;
}


?>
