<?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_PRODUCTION_SUMMARY_REPORT';
// ----------------------------------------------------------------
// $ 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_production_summary_report();

function getproductionsummaryreport($from, $to, $stock_id, $order_num='', $batch_no='', $item_group=0, $item_type=-1, $location=''){
	$fromdate = date2sql($from);
	$todate = date2sql($to);

	$sql = " SELECT wo.*, stock_mas.*, sm.*, wo.stock_id as fin_stock_id,  wom.date_ as wo_manufc_date FROM ".TB_PREF."workorders as wo, ".TB_PREF."stock_moves as sm, ".TB_PREF."wo_manufacture as wom, ".TB_PREF."stock_master as stock_mas WHERE wom.workorder_id=wo.id AND wo.id=sm.trans_no AND stock_mas.stock_id=wo.stock_id AND sm.stock_id=wo.stock_id AND sm.type=26 AND wom.quantity>0 AND sm.qty>0 ";

			 $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);
			if ($item_group != 0)
			 $sql .= " AND stock_mas.item_group_id =".db_escape($item_group);
			if ($item_type != -1)
			 $sql .= " AND stock_mas.category_id =".db_escape($item_type);
			if ($location != '')
			 $sql .= " AND wo.loc_code =".db_escape($location);

              $sql .= " ORDER BY wo.date_ ";
			 // 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 SUM(wii.qty_issued*wii.unit_cost) as issue_cost, 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) . " AND wii.qty_issued!=0";
	// $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)
{
	$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 wo.id=".db_escape($order_num)." AND woc.cost_type =".db_escape($cost_type);
	 $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 get_matrial_cost($wo_id)
{
	$sql = "SELECT SUM(price*qty) as price FROM ".TB_PREF."wo_manufacture as wom , ".TB_PREF."stock_moves as sm WHERE wom.id=sm.trans_no AND sm.type=29 AND wom.workorder_id=".db_escape($wo_id);
	// display_error($sql);
	$result = db_query($sql);
	return db_fetch($result);
}


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

function print_production_summary_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'];
	$item_group = $_POST['PARAM_5'];
	$item_type = $_POST['PARAM_6'];
	$location = $_POST['PARAM_7'];
	$orientation = $_POST['PARAM_8'];
	$destination = $_POST['PARAM_9'];

	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 == 0) 
	 	$item_grp = _('All');
	else
	 	$item_grp = get_item_group_name($item_group);
	
	if ($item_type == -1) 
		$type = _('All');
	else
		$type = get_sales_type_name($item_type);

	if ($location == '') 
		$loc = _('All');
	else
		$loc = get_location_name($location);

		// display_error($item_type.'=='.$type);
	

	$rep = new FrontReport(_('Production Summary Report'), "SalesReport", user_pagesize(), 8, $orientation);

	$params =   array( 	0 => $comments,
						1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
						2 => array( 'text' => _('Item Stock'),'from' => $stock_id,'to' => ''),
						3 => array( 'text' => _('Item Group'),'from' => $item_grp,'to' => ''),
						4 => array( 'text' => _('Sales Type'),'from' => $type,'to' => ''),
						5 => array( 'text' => _('Location'),'from' => $loc,'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' => ''),
					);
						// 2 => array( 'text' => _('Tax Id Only'),'from' => $tid,'to' => ''),

	$cols = array(0, 50, 90, 200, 235, 260, 300, 340, 380, 430, 470, 520, 560); //, 770, 840

	$headers = array(_('Date'), _('Batch #'), _('Item Name'), _('Qty'), _('W.O #'), _('Compl Date'), _('Mtr Cost'), _('Add Mtr Cost'), _('Labour Cost'), _('FOH'), _('Tot Cost'), _('Unit Cost'));
	$aligns = array('center', 'center', 'center', 'center', 'center', 'center', 'center', 'right', 'right', 'right', 'right', 'right', 'right');
    if ($orientation == 'L')
    	recalculate_cols($cols);

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

	// $rep->NewLine(2);
	
	$order_num = '';
	$qty_sum=0;
	$wo_sum=0;
	while ($trans=db_fetch($transactions))
	{
		// display_error($trans['stock_id']);
		// if ($order_num != $trans['id'])
		// {
			
		// 	if($order_num != '' )
		// 	{

		// 		$rep->Line($rep->row - 2);
		// 		$rep->NewLine();
		// 		$rep->TextCol(0, 1, "Total");
		// 			$rep->Line($rep->row - 2);
        //           	$rep->TextCol(10, 11, number_format2($wo_sum, $dec));
		// 		    $wo_sum=0;
		// 	}
		// 		$order_num = $trans['id'];
		// 		$rep->NewLine();

				
		// }

		global $SysPrefs;
				$rep->TextCol(0, 1, sql2date($trans['date_'])); //id
				$rep->TextCol(1, 2, $trans['batch_no']);

					if ($destination)
				  	$rep->TextCol(2, 3, get_items_name($trans['fin_stock_id']));	
				else{	
				$oldrow = $rep->row;
				$rep->TextColLines(2, 3, get_items_name($trans['fin_stock_id']), -2);
				$newrow = $rep->row;
				$rep->row = $oldrow;
			   }
				$rep->TextCol(3, 4,	number_format2($trans['units_reqd'], $dec));
				// $material_cost = $trans['price'] * abs($trans['qty']);
				// $rep->TextCol(4, 5,	number_format2($material_cost, get_qty_dec($trans['stock_id'])));
				// $wastage_cost = $row['unit_cost']*$row['qty_issued'];
				// $rep->TextCol(1, 2, $trans['batch_no']);
				// $rep->TextCol(4, 5, get_item_group_name($trans['item_group_id']));
				$rep->TextCol(4, 5, ($trans['id']));
				$rep->TextCol(5, 6, (sql2date($trans['wo_manufc_date'])));
				$mtr_cost  = get_matrial_cost($trans['id']);
				$rep->TextCol(6, 7, number_format2(abs($mtr_cost['price']), $dec));
				// $rep->TextCol(5, 6, number_format2($row['qty_issued'], get_qty_dec($trans['stock_id'])));
				$row  = wstg_qty_cost($trans['id'], $trans['stock_id']);
				$rep->TextCol(7, 8, number_format2($row['issue_cost'], get_qty_dec($trans['stock_id'])));
				// $tot_mtr = abs($trans['qty']) + $row['qty_issued'];
				// $rep->TextCol(7, 8, number_format2($tot_mtr, get_qty_dec($trans['stock_id'])));
				// $rep->TextCol(8, 9, $trans['loc_code']);
				$data1 = fohcost($trans['id'] , 0);
				$rep->TextCol(8, 9, ($data1['amount']) ? number_format2($data1['amount'], $dec) : 0);

				$data = fohcost($trans['id'] , 1);
				$rep->TextCol(9, 10, ($data['amount']) ? number_format2($data['amount'], $dec) : 0);
				$total = (abs($mtr_cost['price']) + $row['issue_cost'] + $data1['amount'] + $data['amount']);

						// display_error($wastage_cost." == ".$material_cost);
				// $total = $material_cost + $wastage_cost;
				$rep->TextCol(10, 11, number_format2($total, $dec));
				$rep->TextCol(11, 12, number_format2($total/$trans['units_reqd'], $dec));

				if ($destination)
				$rep->NewLine();
				else
				$rep->row = $newrow;
                
                $rep->Line($rep->row + $rep->lineHeight-2,-1);
				
//-------------------------------------------------------------------------------------------------------------------------------------------
				

	         $qty_sum += $trans['units_reqd'];
			$total_mtr_cost += $mtr_cost['price'];
			$total_add_mtr_cost += $row['issue_cost'];
			$labourcost += $data1['amount'];
			$fohcost += $data['amount'];
			// display_error($amt_total);
			$new_total1 += $total;
			$wo_sum += $total;

				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->aligns[7]=$rep->aligns[8]=$rep->aligns[9]=$rep->aligns[10]=$rep->aligns[11]='left';
		$rep->AmountCol(3, 4, $qty_sum, $dec);
	$rep->AmountCol(6, 7, abs($total_mtr_cost), $dec);
	$rep->AmountCol(7, 8, $total_add_mtr_cost, $dec);
	$rep->AmountCol(8, 9, $labourcost, $dec);
	// $rep->cols[11]=$rep->cols[11]+10;
	$rep->AmountCol(9, 10, $fohcost, $dec);
	$rep->AmountCol(10, 11, $new_total1, $dec);
	$rep->Line($rep->row - 5);
	$rep->Font();

	$rep->End();
}

