<?php
/**********************************************************************
***********************************************************************/
$page_security = 'SA_BOMREP';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	Joe Hunt
// date_:	2005-05-19
// Title:	Bill Of Material
// ----------------------------------------------------------------
$path_to_root="..";

include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/inventory/includes/db/items_db.inc");
include_once($path_to_root . "/includes/db/manufacturing_db.inc");
include_once($path_to_root . "/manufacturing/includes/db/work_order_entry_db.inc");
//----------------------------------------------------------------------------------------------------

print_work_order_details();

function get_work_order_header_for_report($from_woid,$to_woid)
{    
    $sql = "SELECT wo.*,st.description item_description,
                    l.location_name,
                    l.delivery_address,
                    l.email, 
                    l.contact,
                    p.project_name, 
                    p.description project_description,
                    t.description task_description 
		FROM ".TB_PREF."workorders wo 
        		INNER JOIN ".TB_PREF."stock_master st ON 
                		st.stock_id=wo.stock_id 
                INNER JOIN ".TB_PREF."locations l ON 
                		l.loc_code=wo.loc_code
                LEFT JOIN ".TB_PREF."projects_master p ON 
                        wo.project_id = p.project_id  
                LEFT JOIN ".TB_PREF."project_tasks t ON 
                        wo.task_id = t.task_id AND p.project_id = t.project_id 
		WHERE  wo.id >=".db_escape($from_woid)." AND  wo.id <=".db_escape($to_woid);

	return $result = db_query($sql, "Could not get work order header");
    
	
}


function draw_table_row_lines($rep)
{
        $v1 = $rep->row + $rep->lineHeight;
        $v2 = $rep->row - 2;        
        $h0 = $rep->cols[0]-3;
        $h1 = $rep->cols[1]-3;
        $h2 = $rep->cols[2]-3;
        $h3 = $rep->cols[3]-3;
//        $h4 = $rep->cols[4]+5;
//        $h5 = $rep->cols[5]+5;
//        $h6 = $rep->cols[6]+5;
//        $h7 = $rep->cols[7]+5;
        
        $rep->LineTo($h0, $v2, $h3, $v2);
        
        $rep->LineTo($h0, $v1, $h0, $v2);
        $rep->LineTo($h1, $v1, $h1, $v2);
        $rep->LineTo($h2, $v1, $h2, $v2);
        $rep->LineTo($h3, $v1, $h3, $v2);

}

function draw_table_horizontal_line($rep, $start, $end, $offset)
{
        $v = $rep->row - $offset;
        
        $rep->LineTo($start, $v, $end, $v);

}

function get_item_issues($parent,$process_id)
{
    $sql = "SELECT wo.*,s.description stock_item_name,sm.price,sm.standard_cost,
                    l.location_name                   
		FROM ".TB_PREF."wo_issue_items wo 
        	LEFT JOIN ".TB_PREF."wo_issues st ON 
                    st.issue_no=wo.issue_id 
                LEFT JOIN ".TB_PREF."stock_master s ON 
                    s.stock_id=wo.stock_id                
                LEFT JOIN ".TB_PREF."stock_moves sm ON 
                    sm.stock_id=wo.stock_id 
                INNER JOIN ".TB_PREF."locations l ON 
                    l.loc_code=sm.loc_code
		WHERE  sm.type = '28' AND sm.trans_no = wo.issue_id AND st.workorder_id =".db_escape($parent)." AND wo.process_id=".db_escape($process_id);

	return $result = db_query($sql, "Could not get material details");
    
}

function get_manuf_labour($process_id = 0)
{
    $sql = "SELECT l.*,p.*,
            em.initials initials,
            em.last_name last_name,
            em.first_name first_name
            FROM ".TB_PREF."wo_process_manufacture_labour l 
            INNER JOIN ".TB_PREF."wo_processes p ON 
                    l.process_id = p.id 
            INNER JOIN ".TB_PREF."employee_master em ON 
                    l.employee_code = em.employee_code ";
                       	
    
    if($process_id > 0){
        $sql .= " WHERE  l.process_id = ".db_escape($process_id);
    }
    
    $sql .= " ORDER BY p.sequence ";
	
    return db_query($sql, "Workorder Labour details could not be retrieved");
}

function get_manuf_labour_rate($date)
{
   
    
    $sql = "SELECT er.description as rate_category_name, er.rate 
            FROM ".TB_PREF."employee_departments ed 
            LEFT JOIN ".TB_PREF."employee_rates er ON 
                er.id = ed.rate_category_id
            WHERE  ed.start_date <=".db_escape($date)." ORDER BY ed.start_date DESC LIMIT 1";   
                
    
    $result=db_query($sql, "Workorder Labour details could not be retrieved");
    
    return db_fetch($result);
    
}

 function calculatetime($time)
        {
            $h = floor($time /3600);
            $m = floor(($time - $h *3600) / 60);
            
            if(strlen($h) == 1){
                $h = '0'.$h;
            }
            if(strlen($m) == 1){
                $m = '0'.$m;
            }
            
            return $h.":".$m;        
            
        }
function get_bom_item_list($frompart, $topart)
{
    $sql = "SELECT stock_id 
            FROM
		".TB_PREF."stock_master
            WHERE stock_id >=".db_escape($frompart)."
		  AND stock_id <=".db_escape($topart)." AND mb_flag='M' " ;

    return db_query($sql,"No transactions were returned");
    
}

function display_wo_materials($wo_process_id,&$rep,$parent,$res_wo_process)
{
    //$rep->NewLine(2);
    $cols = array(0, 80, 175, 290,320,380,450,520);

    $headers = array(_('Code'), _('Component'),_('Location'),_('Unit'),_('Unit Cost'),_('Quantity'),_('Line Cost'));
   

    $aligns = array('left','left','left','left','right','right','right');


    $rep->Font();
    $rep->Info(null, $cols, $headers, $aligns);
//    $rep->Font('B');           
    
    print_headers($rep);
   // $rep->NewPage();
    $result= get_item_issues($parent,$wo_process_id);
    $rep->NewLine(2);
    if(mysql_num_rows($result)==0)
    {
        $rep->TextCol(0,5,'No Components found for this process');    
    }
    else{
          $subtotal=0;  
          $total=0;      
        while($smaterials=db_fetch($result))
        {
            $material_cost=0;
            $subtotal +=$smaterials["standard_cost"]*$smaterials["issued_unit_conv_factor"];
            $material_cost=$smaterials["standard_cost"]*$smaterials["issued_unit_conv_factor"]*$smaterials['issued_unit_qty'];

            $desc = get_item($parent);			
            $rep->TextCol(0, 1, $smaterials['stock_id']);
            $rep->TextCol(1, 2, $smaterials['stock_item_name']);
            $rep->TextCol(2, 3, $smaterials['location_name']);
            $rep->TextCol(3, 4, $smaterials['issued_unit']);
            $rep->TextCol(4, 5, number_format($smaterials["standard_cost"]*$smaterials["issued_unit_conv_factor"],2));
            $rep->TextCol(5, 6, $smaterials['issued_unit_qty']);
            $rep->TextCol(6, 7, number_format($material_cost,2));
            $rep->NewLine();  
        }
            $total +=$material_cost;
            
            $rep->Font('B');
            $rep->TextCol(5, 6, " Sub Total ");            
            $rep->TextCol(6, 7, number_format($total,2));
            $rep->Font('');
            return $total;
            
    }
//        return $total;
	
        //display_bom_labour($bom_process_id,$rep,$parent);
}

function display_wo_labour($bom_process_id,&$rep,$parent)
{
   
    $rep->Font('B');           
    $rep->TextCol(0, 3,"Labour");
    $rep->Font('');
    $rep->NewLine(2);
    $cols = array(0, 200, 280, 320,390,450,520);

    $headers = array(_('Employee'), _('Rate Category'),_('Set Up'),_('Processing'),_('Cleaning'),_('Line Cost'));

    $aligns = array('left','left','right','right','right','right');
    
    
    $rep->Font();
    $rep->Info(null, $cols, $headers, $aligns);
    //$rep->NewPage();
    print_headers($rep);
    $result= get_manuf_labour($bom_process_id);
    $rep->NewLine(2);
    
    
    
    if(mysql_num_rows($result)==0)
    {
        $rep->TextCol(0,5,'No Labour found for this process');    
    }
    else{
    $total_labour_cost=0;
    $grand_lb_cost=0;   
        
    while($labour=db_fetch($result))
    {
        
        $date=substr($labour["start_date_time"],0,10);
        $labour_row= get_manuf_labour_rate($date);
        
        $cost_processing_time=0;
        $cost_cleaning_time=0;
        $cost_setup_time=0;
        
        $cost_setup_time=round(($labour_row["rate"]/3600)*$labour["setup_time"],2);
        $cost_processing_time=round(($labour_row["rate"]/3600)*$labour["processing_time"],2);
        $cost_cleaning_time=round(($labour_row["rate"]/3600)*$labour["cleaning_time"],2);
        $total_labour_cost += $cost_setup_time+$cost_processing_time+$cost_cleaning_time;
        
  
	$desc = get_item($parent);			
        $rep->TextCol(0, 1, $labour['employee_code']." - ".$labour['initials']." ".$labour['last_name']."(".$labour['first_name'].")");
        $rep->TextCol(1, 2, $labour_row['rate_category_name']); 
        $rep->TextCol(2, 3, calculatetime($labour["setup_time"]));
        $rep->TextCol(3, 4, calculatetime($labour["processing_time"]));
        $rep->TextCol(4, 5, calculatetime($labour["cleaning_time"]));	
        $rep->TextCol(5, 6, number_format($cost_setup_time+$cost_processing_time+$cost_cleaning_time,2));	
        $rep->NewLine();    
     
        
    }
    $grand_lb_cost += $total_labour_cost;
        //$rep->NewLine();
        $rep->Font('B');
        $rep->TextCol(4, 5, " Sub Total ");            
        $rep->TextCol(5, 6, number_format($total_labour_cost,2));
        $rep->Font('');
        $rep->NewLine();
        
        return $grand_lb_cost;
       
    }
//    return $grand_lb_cost;
	//$rep->Line($rep->row - 4);
}

function get_wo_process_for_report($id)
{
	$sql = "SELECT wop.*, wc.name workcentre_name  
            FROM ".TB_PREF."wo_processes wop, ".TB_PREF."workcentres wc    
            WHERE wop.workcentre_id = wc.id AND 
                    wop.workorder_id = ".db_escape($id)." 
            ORDER BY sequence";
	
	return $result = db_query($sql, "Workorder process could not be retrieved");
    
    
}

function print_wo_process(&$rep,$res_wo_process,$wo_id)
{
    
    $rep->NewLine();
    $cols = array(0, 120, 200, 320,400,450,520);
    $headers = array(_('Workcentre'), _('Process Item'),_('Process Qty'),_('Setup'),_('Processing'),_('Cleaning'));
    $aligns = array('left','left','right','center','right','right','right'); 
    $rep->Info(null, $cols, $headers, $aligns);
    
    print_headers($rep);
    $rep->NewLine(2);
    
    $rep->TextCol(0, 1, $res_wo_process['workcentre_name']);
    $rep->TextCol(1, 2, $res_wo_process['item_description']);	
    $rep->TextCol(2, 3, $res_wo_process['item_quantity']);
    $rep->TextCol(3, 4, calculatetime($res_wo_process['setup_time']));	
    $rep->TextCol(4, 5, calculatetime($res_wo_process['processing_time']));	
    $rep->TextCol(5, 6, calculatetime($res_wo_process['cleaning_time']));	
    $rep->NewLine(3);
    $rep->Font();
    $rep->Font('B');           
    $rep->TextCol(0,5,"Materials");
    $rep->Font('');
    $rep->NewLine(2);
    
    $total=0;
  
    $total += display_wo_materials($res_wo_process['id'],$rep,$wo_id,$res_wo_process);    
    $rep->NewLine(2);
    $total += display_wo_labour($res_wo_process['id'],$rep,$wo_id);
 
    $rep->Font('B');
     $rep->fontSize += 2;
    $rep->TextCol(4, 5, " Process Total ");            
    $rep->TextCol(5, 6, number_format($total,2));
     $rep->fontSize -= 2;
    $rep->Font('');
    $rep->NewLine(2);
    return $total;
}


function print_headers($rep)
{
    $count = count($rep->headers);
        for ($i = 0; $i < $count; $i++){
                $rep->LineTo($rep->cols[$i], $rep->row+15, $rep->cols[$i+1], $rep->row+15);
                $rep->TextCol($i, $i + 1, $rep->headers[$i]);
                $rep->LineTo($rep->cols[$i], $rep->row-5, $rep->cols[$i+1], $rep->row-5);
        }
}

function display_wo_header($res,$rep)
{

        $rep->TextCol(0, 1, $res['wo_ref']);      
        $rep->TextCol(1, 2, $res['stock_id']);
        $rep->TextCol(2, 3, $res['item_description']);
        $rep->TextCol(3, 4, $res['requested_unit']);
        $rep->AmountCol(4, 5, $res['requested_unit_qty']);
        $rep->TextCol(5, 6, $res['location_name']);
        $rep->TextCol(6, 7, $res['project_description']);
        $rep->TextCol(7, 8, $res['task_description']);       
        $rep->NewLine(3);        
        $result= get_wo_process_for_report($res['id']);
        $grand_total=0;
        while($res_wo_process=db_fetch($result))
        {
            $rep->Font('B'); 
            $rep->fontSize += 1;
            $rep->TextCol(0, 3,"Process :".$res_wo_process['sequence']." - ".$res_wo_process['description']);
            $rep->fontSize -= 1;
            $rep->Font('');
            $rep->NewLine();
            $grand_total +=print_wo_process($rep,$res_wo_process,$res['id']);
            
        }
        $rep->Font('B');
        $rep->fontSize += 3;
        $rep->TextCol(3, 5, "WorkOrder Total");            
        $rep->TextCol(5, 6, number_format($grand_total,2));
        $rep->fontSize -= 3;
        $rep->Font('');
        $rep->NewLine(2);
//    }
   
	//$rep->Line($rep->row + 4);
}
//----------------------------------------------------------------------------------------------------

function print_work_order_details()
{
    global $path_to_root;

    $frompart = $_POST['PARAM_0'];
    $topart = $_POST['PARAM_1'];
    $orientation = $_POST['PARAM_2'];
    $destination = $_POST['PARAM_3'];
	if ($destination)
		include_once($path_to_root . "/reporting/includes/excel_report.inc");
	else
		include_once($path_to_root . "/reporting/includes/pdf_report.inc");

	$orientation = ($orientation ? 'L' : 'P');
        $rep = new FrontReport(_('Work Order Cost Details'), "work_order_cost_details", user_pagesize(), 9, $orientation);
        if ($orientation == 'L')
    	recalculate_cols($cols);        
	$rep->SetHeaderType('form_973');
        $rep->NewPage();     
       
        $result= get_work_order_header_for_report($frompart, $topart);
    
        while($wo_details=db_fetch($result))
        {
            $cols = array(0,25, 100, 200,240,270,340,450,520);
            $headers = array(_('Ref.'),_('Item Code'),_('Item Description'),_('Unit'), _('Qty'),_('Location'), _('Project'),_('Task'));
            $aligns = array('left','left','left','left','left','left','left','left');
            $rep->Font();
            $rep->Info(null, $cols, $headers, $aligns);   
                   
            $rep->Font('B');
            $rep->fontSize += 3;
            $rep->TextCol(0, 3,"Work Order No."." - ".$wo_details['id']);
            $rep->fontSize -= 3;
            $rep->Font('');
            $rep->NewLine(2);
            print_headers($rep);
            $rep->NewLine(2);
            display_wo_header($wo_details,$rep);
            
        }
        if($wo_details)
        {
        $rep->NewPage();
        }
	
    $rep->End();
}

?>