<?php

/*

  osCommRes, Services Online
  http://www.oscommres.com

  Copyright (c) 2005 osCommRes

  Released under the GNU General Public License
  
  Freeway eCommerce from ZacWare
http://www.zac-ware.com/freeway

Copyright 2007 ZacWare Pty. Ltd
*/
// Set flag that this is a parent file
	define( '_FEXEC', 1 );

	require('includes/application_top.php');  
	require(DIR_WS_CLASSES . 'split_page_results_report.php');  
	define(BOX_WIDTH1,'125');

	$type=(isset($HTTP_POST_VARS["cbo_stock_type"])?$HTTP_POST_VARS["cbo_stock_type"]:-1);
	$category=(isset($HTTP_POST_VARS["cbo_category"])?$HTTP_POST_VARS["cbo_category"]:-1);
	$summary_show=(isset($HTTP_POST_VARS["chk_summary"])?true:false);
	$post_action=(isset($HTTP_POST_VARS["post_action"])?$HTTP_POST_VARS["post_action"]:'');
	//$output=(isset($HTTP_POST_VARS["cbo_output"])?$HTTP_POST_VARS["cbo_output"]:1);
	$pdf_output=(isset($HTTP_POST_VARS["chk_pdf_output"])?true:false);
	$excel_output=(isset($HTTP_POST_VARS["chk_excel_output"])?true:false);
	$page=(isset($HTTP_POST_VARS["page"])?$HTTP_POST_VARS["page"]:1);

	// pdf related
	$col_width=Array();
	$page_height=0;
	$page_width=0;
	$row_height=11;
	$font_name="Arial";
	$font_size=10;
	$pdf="";
	$header_color=array(201,201,201);
	$row_color=array(240,241,241);
	if ($post_action==""){
		$pdf_output=true;
		$post_action="screen";
	}
	
	$found_results=false;
		$where="";
		if ($category!=-1){
			$where.=" and pc.categories_id='" . $category . "'";
		}
		if ($type==3){
			$where.=" and date_format(p.products_date_added,'%Y-%m-%d')>=date_sub(curdate(),interval date_format(p.products_date_added,'%w') day) ";
		}
		if ($type==4){
			$cur_month=date("m");
			$cur_year=date("Y");
			$first_date=mktime(0,0,0,$cur_month,1,$cur_year);
			$where.=" and date_format(p.products_date_added,'%Y-%m-%d')>='" . date('Y-m-d',$first_date) ."'";
		}
		// create the query for inventory report
		$products_sql="SELECT distinct p.products_id,pd.products_name,p.products_quantity,cd.categories_id,cd.categories_name from " . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES_DESCRIPTION . " cd, " .
										TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " pc where p.products_id=pc.products_id and cd.categories_id=pc.categories_id
										and p.products_id=pd.products_id and pd.language_id='" . $languages_id . "' and cd.language_id='" . $languages_id . "' " . $where . " order by cd.categories_name,pd.products_name";
		
		if ($post_action=="screen"){
			// Split the report
			$query_split = new splitPageResultsReport($page, REPORT_MAX_ROWS_PAGE, $products_sql, $query_split_numrows);
		}
		
		$products_query=tep_db_query($products_sql);
		
		if (tep_db_num_rows($products_query)>0)  $found_results=true;
		
		$prd_array=array();
		if ($found_results){
			// generate the output array with product details and category headings
			while($products_result=tep_db_fetch_array($products_query)){
				$add_result=true;
				$count_query=tep_db_query("SELECT count(op.products_id) as total from " . TABLE_ORDERS_PRODUCTS . " op where op.products_id='" . $products_result["products_id"] . "' and op.products_type='P'");
				$count_result=tep_db_fetch_array($count_query);
				
				$total_count=(int)$count_result["total"];
				$stock=$products_result["products_quantity"]-$total_count;
				if ($type==1 || $type==2){ // Low Inventory ,Out stock
					$add_result=false;
					if ($type==1 && $stock<5) $add_result=true; // if stock falls below 5
					if ($type==2 && $stock<=0) $add_result=true; // if stock reaches 0
				}
				
				if ($add_result){
					$prd_array[]=array(	"name"=>$products_result["products_name"],
										"id"=>$products_result["products_id"],
										"quan"=>$products_result["products_quantity"],
										"cat_id"=>$products_result["categories_id"],
										"cat_name"=>$products_result["categories_name"],
										"stock"=>$stock
									);
				}							
			}
		}
	
	//get category list
	$category_array=array();
	$category_array[]=array("id"=>-1,"text"=>TEXT_ALL_CATEGORIES);
	$category_query=tep_db_query("SELECT cd.categories_name,c.categories_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd " . 
						"where c.categories_id=cd.categories_id and cd.language_id='" . $languages_id . "' order by cd.categories_name");
						
	while($category_result=tep_db_fetch_array($category_query)){
		$category_array[]=array("id"=>$category_result["categories_id"],"text"=>$category_result["categories_name"]);
	}
	
	//stock array
	$stock_array=array(	array('id'=>-1,'text'=>TEXT_ALL_PRODUCTS),
						array('id'=>1,'text'=>TEXT_LOW_INVENTORY),
						array('id'=>2,'text'=>TEXT_OUT_STOCK),
						array('id'=>3,'text'=>TEXT_RECENT_WEEK),
						array('id'=>4,'text'=>TEXT_RECENT_MONTH)
					);
	// output array
	$output_array=array( 	array('id'=>1,'text'=>TEXT_SCREEN),
							array('id'=>2,'text'=>TEXT_PDF),
							array('id'=>3,'text'=>TEXT_EXCEL)
						);
	
	// pdf output					
	if ($post_action=="pdf"){
		out_pdf($prd_array);
		tep_redirect(DIR_WS_CATALOG . "images/inventory.pdf");
	}
	// excel output
	if ($post_action=="excel"){
		out_excel($prd_array);
		tep_redirect(DIR_WS_CATALOG . "images/inventory.csv");
	}
?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->
<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
	<tr> 
		<!-- body_text //-->	  
		<td width=100% align=left valign="top">
		<table border="0" width="100%" cellspacing="0" cellpadding="2">
			<tr>
				<td class="pageHeading"><?php echo HEADING_INVENTORY;?></td>
			</tr>
			<tr>
				<td class="pageHeading"></td>
			</tr>
			<!-- --->
			<script language="JavaScript">
				function doReport(mode) {
					if (mode==3){
						document.f.post_action.value="excel"
						document.f.target="_blank";
					} else if (mode==2){
						document.f.post_action.value="pdf";
						document.f.target="_blank";
					} else {
						document.f.post_action.value="screen";
						document.f.target="_self";
					}
					document.f.submit();
				}
				
				function f_Print() {
					document.f.layout.value="print";
					document.f.target="_blank";
					document.f.submit();
				
				}
			</script>
			<?php 
				echo tep_draw_form("f",FILENAME_INVENTORY);
				echo '<input type="hidden" name="post_action" value="">';
			?>
			<tr>
				<td>
				<table cellspacing="3" cellpadding="2" class="formArea" width="100%" border="0">
					<tr>
						<td>
						<table border="0" cellpadding="0" cellspacing="0" width="100%">
							<tr class="main">
								<td nowrap><?php echo TEXT_CATEGORY . '&nbsp;' . tep_draw_pull_down_menu("cbo_category",$category_array,$category);?></td>
								<td nowrap>
								<?php 	echo TEXT_STOCK_TYPE . '&nbsp;' . tep_draw_pull_down_menu("cbo_stock_type",$stock_array,$type);
										echo '&nbsp;&nbsp;' . tep_draw_checkbox_field("chk_summary",1,$summary_show) . TEXT_SUMMARY_ONLY;
								?>
								</td>
								<td align="right" class="main">
									<?php
										echo '<a href="javascript:doReport(1);">' . tep_image_button('button_report_search.gif', IMAGE_SEARCH_PRODUCTS) . '</a>&nbsp;';
										echo '<a href="javascript:doReport(2);">' . tep_image_button('button_export_pdf.gif', IMG_EXPORT_PDF) . '</a>&nbsp;';
										echo '<a href="javascript:doReport(3);">' . tep_image_button('button_export_excel.gif', IMG_EXPORT_EXCEL) . '</a>';
									?>
								</td>
							</tr>
						</table>
						</td>
					</tr>
				</table>
				</td>
			</tr>
			</form>
			<tr>
				<TD>
					<?php echo tep_draw_separator('pixel_trans.gif',1,10); ?>
				</TD>
			</tr>
			<?php if ($found_results) { ?>
			<tr>
				<td>
				<table border="0" width="100%" cellspacing="0" cellpadding="2">
					<tr>
						<td class="smallText" align="left">
							<?php echo $query_split->display_links($query_split_numrows, REPORT_MAX_ROWS_PAGE, REPORT_MAX_LINKS_PAGE, $page,tep_get_report_params()); ?>&nbsp;
						</td>
					</tr>
				</table>
				</td>
			</tr>
			<tr>
				<td>
				<table border="0" cellpadding="0" cellspacing="0" width="100%">
					<tr class='dataTableHeadingRow'>
					  <td class='dataTableHeadingContent' width="30"><?php echo TEXT_ID;?></td>
					  <TD class='dataTableHeadingContent'><?php echo TEXT_PRODUCT_NAME;?></TD>
					  <TD class='dataTableHeadingContent' width="80"><?php echo TEXT_STOCK;?></TD>
					</tr>
					<tr>
						<td colspan="3"><?php echo tep_draw_separator('pixel_trans.gif',1,5);?></td>
					</tr>
					<?php
						$prev_cat_id=0;
						$total=0;
							for ($icnt=0;$icnt<sizeof($prd_array);$icnt++){
								$row=&$prd_array[$icnt];
								if ($prev_cat_id!=$row["cat_id"]){ // output category
									if ($total>0) { // output total
								?>
										<tr>
											<td class="dataTableContent"></td>
											<td class="dataTableContent"><b><?php echo TEXT_TOTAL;?></b></td>
											<Td align="right" class="dataTableContent"><?php echo $total;?></Td>
										</tr>
										<tr>
											<td><?php echo tep_draw_separator('pixel_trans.gif',1,10);?></td>
										</tr>
								<?php $total=0;
								}
								$prev_cat_id=$row["cat_id"];
								?>
									<tr>
										<Td colspan="3" class="dataTableContent"><b><?php echo $row["cat_name"];?></b></Td>
									</tr>
									<tr>
										<td><?php echo tep_draw_separator('pixel_trans.gif',1,5);?></td>
									</tr>
					<?php			
								}
								if(!$summary_show) { // if show only summary
					?>
								  <TR class='dataTableRow' height="20">
									  <TD class='dataTableContent'><?php echo $icnt+1; ?></td>
									  <TD class='dataTableContent'><?php echo $row['name']; ?></TD>
									  <TD class='dataTableContent' align="right"><?php echo $row['stock']?></TD>
								  </TR>
					<?php
								}
						   $i++;
						   $total+=$row["stock"];
						   }
						if ($total>0) {
					?>
							<tr>
								<td><?php echo tep_draw_separator('pixel_trans.gif',1,5);?></td>
							</tr>
							<tr>
								<td class="dataTableContent"></td>
								<td class="dataTableContent"><b><?php echo TEXT_TOTAL;?></b></td>
								<Td align="right" class="dataTableContent"><?php echo $total;?></Td>
							</tr>
					<?php } ?>
					<tr>
						<td><?php echo tep_draw_separator('pixel_trans.gif',1,5);?></td>
					</tr>
					</table>
					</td>
				</tr>
				<?php	} ?>
			</table>
			</td>
		</tr>
	</table>
	</td>
</tr>
<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php');?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php');

	// function to output excel content
	function out_excel(&$prd_array){
		global $summary_show;
		if (file_exists(DIR_FS_CATALOG . "images/inventory.csv")){
			unlink(DIR_FS_CATALOG . "images/inventory.csv");
		}
		$fp=fopen(DIR_FS_CATALOG . 'images/inventory.csv','w');
		$output_string="";
		$output_string.=sprintf("%s,%s,%s\n",TEXT_ID,TEXT_PRODUCT_NAME,TEXT_STOCK);
		for ($icnt=0;$icnt<sizeof($prd_array);$icnt++){
			$row=&$prd_array[$icnt];
			if ($prev_cat_id!=$row["cat_id"]){
				if ($total>0) {
					$output_string.=sprintf(",%s,%s\n\n",TEXT_TOTAL,$total);
					$total=0;
				}
				$prev_cat_id=$row["cat_id"];
				$output_string.=sprintf("%s\n",$row["cat_name"]);
			}
			if(!$summary_show) {
				$output_string.=sprintf("%s,%s,%s\n",$icnt+1,$row['name'],$row['stock']);
			}
		   $i++;
		   $total+=$row["stock"];
		}
		if ($total>0) {
			$output_string.=sprintf(",%s,%s\n\n",TEXT_TOTAL,$total);
		}
		fwrite($fp,$output_string,strlen($output_string));
		fclose($fp);
	}
	// function to output pdf content
	function out_pdf(&$prd_array){
		global $summary_show,$page_width,$page_height,$col_width,$font_name,$font_size,$pdf,$row_height,$header_color,$row_color;
		
		define('FPDF_FONTPATH','font/');
		require('includes/fpdf.php');
		  
		define('PDF_LEFT_MARGIN',20);
		define('PDF_TOP_MARGIN',20);
		define('PDF_ORIENTATION','P');

		$row_height=14;
				
		$col_width[0]=60;
		$col_width[2]=100;

		$page_height=841.89-(PDF_LEFT_MARGIN*2);
		$page_width=595.28-(PDF_TOP_MARGIN*2);
		$col_width[1]=$page_width-($col_width[0]+$col_width[2]);
		
		$pdf=new FPDF(PDF_ORIENTATION,"pt",($page_size==2?"legal":"A4"));
		$pdf->AliasNbPages();
		$pdf->SetAutoPageBreak(false);

		$cur_y=$page_height;
		$cur_x=PDF_LEFT_MARGIN;
		check_page($cur_x,$cur_y);
		for ($icnt=0;$icnt<sizeof($prd_array);$icnt++){
			$row=&$prd_array[$icnt];
			$cur_x=PDF_LEFT_MARGIN;
			if ($prev_cat_id!=$row["cat_id"]){
				if ($total>0) {
					$pdf->SetFont($font_name,'B',$font_size);
					$cur_x+=$col_width[0];
					$pdf->SetXY($cur_x,$cur_y);
					$pdf->Cell($col_width[0]+$col_width[1],$row_height , TEXT_TOTAL , 0 , 0 ,"R");
					$cur_x+=$col_width[1];
					$pdf->SetXY($cur_x,$cur_y);
					$pdf->Cell($col_width[2],$row_height , $total , 0 , 0 ,"R");
					$pdf->SetFont($font_name,'',$font_size);
					check_page($cur_x,$cur_y);
					$total=0;
				}
				$prev_cat_id=$row["cat_id"];
				$pdf->SetFont($font_name,'B',$font_size+1);
				$pdf->SetXY($cur_x,$cur_y);
				$pdf->Cell($col_width[0]+$col_width[1],$row_height,$row["cat_name"]);
				$pdf->SetFont($font_name,'',$font_size);
				check_page($cur_x,$cur_y);
			}
			if(!$summary_show) {
				$pdf->SetFillColor($row_color[0],$row_color[1],$row_color[2]);
				$pdf->SetXY($cur_x,$cur_y);
				$pdf->Cell($col_width[1],$row_height , $icnt+1 , 0 , 0 ,"L", 1);
				$cur_x+=$col_width[0];
				$pdf->SetXY($cur_x,$cur_y);
				$pdf->Cell($col_width[1],$row_height , $row["name"] , 0 , 0 ,"L", 1);
				$cur_x+=$col_width[1];
				$pdf->SetXY($cur_x,$cur_y);
				$pdf->Cell($col_width[2],$row_height , $row["stock"] , 0 , 0 ,"R", 1);
				$pdf->SetFillColor(255,255,255);
				check_page($cur_x,$cur_y);
			}
		   $total+=$row["stock"];
		}
		if ($total>0) {
			$pdf->SetFont($font_name,'B',$font_size);
			$cur_x+=$col_width[0];
			$pdf->SetXY($cur_x,$cur_y);
			$pdf->Cell($col_width[1]+$col_width[0],$row_height , TEXT_TOTAL , 0 , 0 ,"R");
			$cur_x+=$col_width[1];
			$pdf->SetXY($cur_x,$cur_y);
			$pdf->Cell($col_width[2],$row_height , $total , 0 , 0 ,"R");
			$pdf->SetFont($font_name,'',$font_size);
			$total=0;
			check_page($cur_x,$cur_y);
		}
		if (file_exists(DIR_FS_CATALOG . "images/inventory.pdf")){
			unlink(DIR_FS_CATALOG . "images/inventory.pdf");
		}
		$pdf->output(DIR_FS_CATALOG . "images/inventory.pdf");
	}
	// function to check page end in pdf output
	function check_page(&$cur_x,&$cur_y){
		global $summary_show,$page_width,$page_height,$col_width,$font_name,$font_size,$pdf,$row_height,$col_width,$row_color,$header_color,$row_color;
		$cur_x=PDF_LEFT_MARGIN;
		$cur_y+=$row_height;
		if ($cur_y>$page_height){
			$pdf->AddPage();
			$cur_y=PDF_TOP_MARGIN;
			$pdf->SetFont($font_name,'B',$font_size);
			$pdf->SetFillColor($header_color[0],$header_color[1],$header_color[2]);
			$pdf->SetDrawColor(255,255,255);
			$pdf->SetXY($cur_x,$cur_y);
			$pdf->Cell($col_width[0],$row_height , TEXT_ID , 0 , 0 ,"L", 1);
			$cur_x+=$col_width[0];
			$pdf->SetXY($cur_x,$cur_y);
			$pdf->Cell($col_width[1],$row_height , TEXT_PRODUCT_NAME , 0 , 0 ,"L", 1);
			$cur_x+=$col_width[1];
			$pdf->SetXY($cur_x,$cur_y);
			$pdf->Cell($col_width[2],$row_height , TEXT_STOCK , 0 , 0 ,"R", 1);
			$cur_y+=$row_height*2;
			$cur_x=PDF_LEFT_MARGIN;
			$pdf->SetFont($font_name,'',$font_size);
			$pdf->SetFillColor(255,255,255);
			$pdf->SetDrawColor(0,0,0);
		}
	}
?>
