<?php
/**********************************************************************
    Copyright (C) FrontAccounting, LLC.
	Released under the terms of the GNU General Public License, GPL, 
	as published by the Free Software Foundation, either version 3 
	of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
$page_security = 'SA_REP_COST_OF_PRODUCTION';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	Joe Hunt
// date_:	2005-05-19
// Title:	Sales Summary Report
// ----------------------------------------------------------------
$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");

//------------------------------------------------------------------


print_cost_of_production_report();

// function getCostOfProduction($value='')
// {
// 	$sql = "SELECT
// 		workorder.id,
// 		workorder.wo_ref,
// 		workorder.type,
// 		location.location_name,
// 		item.description,
// 		workorder.units_reqd,
// 		workorder.units_issued,
// 		workorder.date_,
// 		workorder.required_by,
// 		workorder.released_date,
// 		workorder.closed,
// 		workorder.released,
// 		workorder.stock_id,
// 		unit.decimals , workorder.work_center_status, workorder.is_qc
// 		FROM ".TB_PREF."workorders as workorder
// 			LEFT JOIN ".TB_PREF."voided v ON v.id=workorder.id and v.type=".ST_WORKORDER.","
// 			.TB_PREF."stock_master as item,"
// 			.TB_PREF."item_units as unit,"
// 			.TB_PREF."locations as location
// 		WHERE ISNULL(v.id)
// 			AND workorder.stock_id=item.stock_id 
// 			AND workorder.loc_code=location.loc_code
// 			AND item.units=unit.abbr AND workorder.loc_code IN (".$_SESSION["wa_current_user"]->location.") ";

// 	if (check_value('OpenOnly') || $outstanding_only != 0)
// 	{
// 		$sql .= " AND workorder.closed=0";
// 	}

// 	if ($location != ALL_TEXT)
// 	{
// 		$sql .= " AND workorder.loc_code=".db_escape($location);
// 	}

// 	if ($order_no != '')
// 	{
// 		$sql .= " AND workorder.id LIKE ".db_escape('%'.$order_no.'%');
// 	}

// 	if ($order_ref != '')
// 	{
// 		$sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$order_ref.'%');
// 	}

// 	if ($stock_id != ALL_TEXT)
// 	{
// 		$sql .= " AND workorder.stock_id=".db_escape($stock_id);
// 	}

// 	if ($overdue)
// 	{
// 		$Today = date2sql(Today());

// 		$sql .= " AND workorder.required_by < '$Today' ";
// 	}
// 	$sql .= " ORDER BY workorder.id DESC";
// 	return $sql;
// }

function getCostOfProduction($from, $to, $stock_id, $order_num, $batch_no){
	$fromdate = date2sql($from);
	$todate = date2sql($to);

	$sql = " SELECT wo.*, sm.* , wo.stock_id as fa_stock_id FROM ".TB_PREF."workorders as wo, ".TB_PREF."stock_moves as sm, ".TB_PREF."wo_manufacture as wom WHERE 
		wom.workorder_id=wo.id AND wom.id=sm.trans_no AND sm.type=29 ";

			 $sql .= " AND wo.date_ >=".db_escape($fromdate)." AND wo.date_<=".db_escape($todate);

			 if ($stock_id != '')
			 $sql .= " AND wo.stock_id =".db_escape($stock_id);
			if ($order_num != '')
			 $sql .= " AND wo.id =".db_escape($order_num);
			if ($batch_no != '')
			 $sql .= " AND sm.batch_no =".db_escape($batch_no);

			 // display_error($sql);
			 $result = db_query($sql);
			 return $result;
			 // return db_fetch($result);

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

function wstg_qty_cost($order_num, $stock_id)
{
	$sql = " SELECT wii.qty_issued, wii.unit_cost FROM ".TB_PREF."wo_issue_items as wii, ".TB_PREF."wo_issues as wi WHERE wi.issue_no=wii.issue_id AND wi.workorder_id=".db_escape($order_num);
	$sql .= " ANd wii.stock_id=".db_escape($stock_id);
	// display_error($sql);
	$result = db_query($sql);
	return db_fetch($result);
	// return db_query($sql);
}


	
 function fohcost($order_num, $cost_type)
{
	// if ($cost_type = 0) {
	$sql = " SELECT wo.*, woc.*, jo.* FROM ".TB_PREF."workorders as wo, ".TB_PREF."wo_costing as woc, ".TB_PREF."journal as jo WHERE wo.id=woc.workorder_id
	 AND woc.trans_no=jo.trans_no  AND woc.cost_type =".db_escape($cost_type);
	 $sql .= "AND woc.workorder_id =".db_escape($order_num);
	 $result = db_query($sql);
	return db_fetch($result);
	// }
	// else
	// 	$sql = " SELECT wo.*, woc.*, jo.* FROM ".TB_PREF."workorders as wo, ".TB_PREF."wo_costing as woc, ".TB_PREF."journal as jo WHERE wo.id=woc.workorder_id
	//  AND woc.trans_no=jo.trans_no AND woc.cost_type =1";
	//  $result = db_query($sql);
	// return db_fetch($result);
}

function get_items_name($stock_id)
{
	$sql = "SELECT description FROM ".TB_PREF."stock_master WHERE stock_id=".db_escape($stock_id);
	// display_error($sql);
	$result = db_query($sql);
	$row = db_fetch_row($result);
	return is_array($row) ? $row[0] : false;
}

//  function fohcost($order_num)
// {
// 	$sql = " SELECT wo.*, woc.*, jo.* FROM ".TB_PREF."workorders as wo, ".TB_PREF."wo_costing as woc, ".TB_PREF."journal as jo WHERE wo.id=woc.workorder_id
// 	 AND woc.trans_no=jo.trans_no AND woc.cost_type =1";
// 	 $result = db_query($sql);
// 	return db_fetch($result);
// }

//----------------------------------------------------------------------------------------------------

function print_cost_of_production_report()
{
	global $path_to_root;
	
	$from = $_POST['PARAM_0'];
	$to = $_POST['PARAM_1'];
	$stock_id = $_POST['PARAM_2'];
	$order_num = $_POST['PARAM_3'];
	$batch_no = $_POST['PARAM_4'];
	$orientation = $_POST['PARAM_5'];
	$destination = $_POST['PARAM_6'];

	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');

      	$orientation = 'L';
	// if ($customer_id == ALL_TEXT)
	// 	$cust = _('All');
	// else
	// 	$cust = get_customer_name($customer_id);
	$dec = user_price_dec();

	// if ($broker == -1) 
	// 	$sale_Prsn = _('All');
	// else
	// 	$sale_Prsn = get_sales_group_name($broker);

	// if ($area == 0)
	// 	$sarea = _('All');
	// else
	// 	$sarea = get_area_name($area);

	// if ($item_group_id == 0) 
	//  	$item_group = _('All');
	//  else
	//  	$item_group = get_item_group_name($item_group_id);
	
	// if ($sales_type == 0) 
	// 	$type = _('All');
	// else
	// 	$type = get_sales_type_name($sales_type);

	

	$rep = new FrontReport(_('Cost of Production Report'), "CostOfProductionReport", user_pagesize(), 9, $orientation);

	$params =   array( 	0 => $comments,
						1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
						2 => array( 'text' => _('Item Stock'),'from' => $stock_id,'to' => ''),
						// 2 => array( 'text' => _('Customer Id'),'from' => $cust,'to' => ''),
						// 3 => array( 'text' => _('Area'),'from' => $sarea,'to' => ''),
						// 4 => array( 'text' => _('Sales Person'),'from' => $sale_Prsn,'to' => ''),
						// 6 => array( 'text' => _('Items Group'),'from' => $item_group,'to' => ''),
						// 7 => array( 'text' => _('Sales Type'),'from' => $type,'to' => '')
					);
						// 2 => array( 'text' => _('Tax Id Only'),'from' => $tid,'to' => ''),
						// 4 => array( 'text' => _('Branch Id'),'branch_id' => $branch_id,'to' => ''),

	$cols = array(0, 30, 110, 230, 290, 350, 410, 470, 530, 590 , 650 );

	$headers = array(_('W-O #'), _('Batch #'), _('Material'), _('Mtr. Qty'), _('Mtr Cost'), _('Wstg Qty'), _('Wstg Cost'), _('Department'), _('Total Cost'));
	$aligns = array('left', 'left', 'left', 'left', 'left', 'left', 'left', 'left', 'left', 'left', 'left', 'right', 'right');
    if ($orientation == 'L')
    	recalculate_cols($cols);

	$rep->Font();
	$rep->Info($params, $cols, $headers, $aligns);
	$rep->NewPage();
	
	$totalnet = 0.0;
	$transactions = getCostOfProduction($from, $to, $stock_id, $order_num, $batch_no);
	// $transactions = getCostOfProduction();


	// $rep->NewLine(2);
	
	
	$order_num = '';

	$wo_sum=$wo_MtrCost=$wo_WstgCost= 0;
	// $total = '';
	while ($trans=db_fetch($transactions))
	{
		// display_error($trans['stock_id']);
		// $rep->Line($rep->row - 5);

			if ($order_num != $trans['id'])
		{
			
			if($order_num != '' )
			{
				$data1 = fohcost($order_num , 0);
				$data = fohcost($order_num , 1);

				$rep->Line($rep->row - 2);
				$rep->NewLine();
					$rep->Font('bold');
				$rep->TextCol(0, 2,	_("Sub Total: "));
				    $rep->AmountCol(4, 5, $wo_MtrCost,$dec);
				        $rep->AmountCol(6, 7, $wo_WstgCost,$dec);
			    $rep->AmountCol(8, 9, $wo_sum,$dec);
				$rep->NewLine();
				$rep->TextCol(0, 2,	_("Labor Cost: "));
				$rep->TextCol(8, 9, number_format2($data1['amount'],$dec));
				$rep->NewLine();
				$rep->TextCol(0, 2,	_("FOH Cost: "));
				$rep->TextCol(8, 9, number_format2($data['amount'],$dec));
				$rep->NewLine();
				$rep->TextCol(0, 1, "Total");
				$rep->Line($rep->row - 2);
              	$rep->TextCol(8, 9, number_format2($wo_sum+$data1['amount']+$data['amount'], $dec));
              	$rep->Font();
			    $wo_sum=0;
			    $wo_MtrCost=0;
                $wo_WstgCost=0;
			    	$rep->NewLine();
			}
			else
				$rep->NewLine();
				$order_num = $trans['id'];
                 
             	$rep->Font('bold');
             		// if ($destination)ss
				$rep->TextCol(0, 3,	_($trans['fa_stock_id']." ". get_items_name($trans['fa_stock_id']) ) );
                $rep->TextCol(1, 2, $trans['batch_no']);
            	$rep->Font();

			    $rep->NewLine();
				

				
		}
			
		global $SysPrefs;
				$rep->TextCol(0, 1, $trans['id']);
				$rep->TextCol(1, 2, $trans['batch_no']);
				// $rep->TextCol(2, 3,	$trans['stock_id']);
				$oldrow = $rep->row;
					$item_row = get_item($trans['stock_id']);
				if ($destination)
				  	$rep->TextCol(2, 3, $trans['stock_id']." ".$item_row['description']);	
				else{
				
				  $rep->TextColLines(2 , 3,$trans['stock_id']." ".$item_row['description'], -2);
				
				}	
				$newrow = $rep->row;
				$rep->row = $oldrow;
				$row  = wstg_qty_cost($trans['id'], $trans['stock_id']);
				$rep->TextCol(3, 4,	number_format2(abs($trans['qty']),get_qty_dec($trans['stock_id'])));
				$rep->TextCol(4, 5,	number_format2($trans['price']*abs($trans['qty']),$dec));
				$rep->AmountCol(5, 6, $row['qty_issued'],get_qty_dec($trans['stock_id']));
				$rep->TextCol(6, 7, number_format2($row['unit_cost']*$row['qty_issued'],$dec));
				$rep->TextCol(7, 8, $trans['loc_code']);
				// $data1 = fohcost($trans['id'] , 0);
				// $rep->TextCol(8, 9, number_format2($data1['amount'],$dec));

				// $data = fohcost($trans['id'] , 1);
				// $rep->TextCol(9, 10, number_format2($data['amount'],$dec));
				$MtrCost = $trans['price']*abs($trans['qty']);
				$WstgCost = $row['unit_cost']*$row['qty_issued'];
				$total =  $MtrCost + $WstgCost;
				// display_error("WO : ". $trans['id']." ".$data1['amount'] ."+". $data['amount'] ."+". $MtrCost ."+". $WstgCost);
				$rep->TextCol(8, 9, number_format2($total, $dec));
               
					$labourcost += $data1['amount'];
					$fohcost += $data['amount'];
					$wo_MtrCost+=$MtrCost;
					$wo_WstgCost+=$WstgCost;
					// display_error($amt_total);
					$total_all += $total;
				    $wo_sum += $total;
				
//-------------------------------------------------------------------------------------------------------------------------------------------
				if ($destination)
				$rep->NewLine();
			    else
				$rep->row = $newrow;

				if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
				{
					$rep->Line($rep->row - 2);
					$rep->NewPage();
				}
		

	
	}

	// $rep->Font('bold');
	// $rep->NewLine();
	// $rep->Line($rep->row + $rep->lineHeight);
	// $rep->TextCol(0, 2,	_("Total"));
	// $rep->AmountCol(6, 7, $labourcost, $dec);
	// $rep->AmountCol(7, 8, $fohcost, $dec);
	// $rep->AmountCol(8, 9, $total_all, $dec);
	// $rep->Line($rep->row - 5);
	// $rep->Font();



	$rep->Font('bold');
	$rep->NewLine();
	$rep->Line($rep->row + $rep->lineHeight);
	$rep->TextCol(0, 2,	_("Total Labor Cost:"));
	$rep->AmountCol(8, 9, ($labourcost));
	$rep->Font();
	$rep->NewLine(2);
	$rep->Font('bold');
	$rep->TextCol(0, 2,	_("Total FOH Cost: "));
	$rep->AmountCol(8, 9, ($fohcost), $dec);
	$rep->Font();
	$rep->NewLine(2);
	$rep->Font('bold');
	$rep->TextCol(0, 2,	_("Total: "));
	$rep->AmountCol(8, 9, ($total_all+$labourcost+$fohcost), $dec);
	$rep->Line($rep->row - 5);
	$rep->Font();
	$rep->End();
}

