<?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 
*/

	require('includes/application_top.php');  
	require(DIR_WS_CLASSES . 'split_page_results_report.php');  
	require('includes/classes/currencies.php');
	define(BOX_WIDTH1,'125');
	
	// get initial parameters, first check the session for previous values
	if (isset($HTTP_GET_VARS["return"]) && isset($HTTP_SESSION_VARS["rep_params"])){
		$input_params=&$HTTP_SESSION_VARS["rep_params"];
	} else {
		$input_params=&$HTTP_POST_VARS;
		if (isset($input_params["post_action"])){
			tep_set_session_value("rep_params",$HTTP_POST_VARS);
			$GLOBALS["rep_params"]["post_action"]="screen";
		} else {
			tep_set_session_value("rep_params",array());
		}
	}
	
	$cust=(isset($HTTP_POST_VARS["cbo_cust_type"])?$HTTP_POST_VARS["cbo_cust_type"]:-1);
	$category=(isset($HTTP_POST_VARS["cbo_category"])?$HTTP_POST_VARS["cbo_category"]:-1);
	$sort=(isset($HTTP_POST_VARS["cbo_sort"])?$HTTP_POST_VARS["cbo_sort"]:-1);
	$date_begin=(isset($HTTP_POST_VARS["txt_start_date"])?tep_convert_date_raw($HTTP_POST_VARS["txt_start_date"]):'');
	$date_end=(isset($HTTP_POST_VARS["txt_end_date"])?tep_convert_date_raw($HTTP_POST_VARS["txt_end_date"]):'');
	$summary_show=(isset($HTTP_POST_VARS["chk_summary"])?true:false);
	$detail_show=(isset($HTTP_POST_VARS["chk_detail"])?true:false);
	$post_action=(isset($HTTP_POST_VARS["post_action"])?$HTTP_POST_VARS["post_action"]:'');
	$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);
	$currencies=new currencies();
	
	if ($post_action==""){
		$pdf_output=true;
		$post_action="screen";
	}
	
	if ($date_begin==""){
		$date_begin=getServerDate();
		$sql =  "select date_sub('$date_begin', interval 1 month) start_date";	
		$sql_result = tep_db_query($sql);
		$row = mysql_fetch_assoc($sql_result);
		$date_begin = $row["start_date"];
	}
	
	if ($date_end==""){
		$res = tep_db_query("SELECT curdate()");		
		$row = mysql_fetch_array($res);
		$date_end = $row[0]; 
	}
	
	$found_results=false;
		$where="";
		$swhere="";
		$suwhere="";
		if ($category!=-1){
			$where.=" and pc.categories_id='" . $category . "'";
		}
		if ($summary_show==true){
			if ($sort==-1){
					$suwhere.=" group by cd.categories_name,pd.products_name";
					$twhere=" group by cd.categories_name,pd.products_name";
				}
				else {
					$suwhere.=" group by pd.products_name";
					$twhere=" group by pd.products_name";
				}	
		}
		else{
		//$twhere=" group by tcb.customers_basket_id";
			if ($sort==-1){
				$swhere.="  order by categories_name,products_name";
			}
			else {
				$swhere.=" order by products_name";
			}
		}
		
		$date_clause = " date_format(cb.customers_basket_date_added,'%Y-%m-%d')>='". $date_begin ."' and date_format(cb.customers_basket_date_added,'%Y-%m-%d')<='". $date_end ."' ";
		$tdate_clause = " date_format(tcb.customers_basket_date_added,'%Y-%m-%d')>='". $date_begin ."' and date_format(tcb.customers_basket_date_added,'%Y-%m-%d')<='". $date_end ."' ";
		if((!$detail_show && !$summary_show ) || $detail_show) {
		// create the query for drop report
		$sql1="SELECT distinct 1 as id,  p.products_id,p.products_tax_class_id,tcb.customers_id,tcb.customers_basket_quantity,p.products_price,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, " . TABLE_CUSTOMERS_TEMP_BASKET . " tcb  where p.products_id=pc.products_id    
						and if(instr(tcb.products_id,'{')>0,substring(tcb.products_id,1,instr(tcb.products_id,'{')-1),tcb.products_id)=p.products_id and cd.categories_id=pc.categories_id and tcb.products_type='P'
						and " . $tdate_clause . " and p.products_id=pd.products_id and pd.language_id='" . $languages_id . "' and cd.language_id='" . $languages_id . "' " . $where  ;
						
			$sql2="SELECT distinct 2 as id,  p.products_id,p.products_tax_class_id,cb.customers_id,cb.customers_basket_quantity,p.products_price,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, " . TABLE_CUSTOMERS_BASKET . " cb  where p.products_id=pc.products_id    
						and if(instr(cb.products_id,'{')>0,substring(cb.products_id,1,instr(cb.products_id,'{')-1),cb.products_id)=p.products_id and cd.categories_id=pc.categories_id and cb.products_type='P'
						and " . $date_clause . " and p.products_id=pd.products_id and pd.language_id='" . $languages_id . "' and cd.language_id='" . $languages_id . "' " . $where.$swhere ;
		} else if ( $summary_show ){
			$sql1="SELECT distinct 1 as id, p.products_id, p.products_tax_class_id, tcb.customers_id, tcb.customers_basket_quantity, sum(tcb.customers_basket_quantity) as sumcbq, p.products_price, 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, " . TABLE_CUSTOMERS_TEMP_BASKET . " tcb  where p.products_id=pc.products_id   and tcb.products_type='P'
						 and if(instr(tcb.products_id,'{')>0,substring(tcb.products_id,1,instr(tcb.products_id,'{')-1),tcb.products_id)=p.products_id and cd.categories_id=pc.categories_id
						and p.products_id=pd.products_id ".$tdata_clause." and pd.language_id='" . $languages_id . "' and cd.language_id='" . $languages_id . "' "  .$where.$twhere ;
						
				$sql2="SELECT distinct 2 as id, p.products_id, p.products_tax_class_id, cb.customers_id, cb.customers_basket_quantity, sum(cb.customers_basket_quantity) as sumcbq, p.products_price, 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, " . TABLE_CUSTOMERS_BASKET . " cb  where p.products_id=pc.products_id   and cb.products_type='P'
						and if(instr(cb.products_id,'{')>0,substring(cb.products_id,1,instr(cb.products_id,'{')-1),cb.products_id)=p.products_id and cd.categories_id=pc.categories_id
						and p.products_id=pd.products_id ".$data_clause." and pd.language_id='" . $languages_id . "' and cd.language_id='" . $languages_id . "' "  .$where.$suwhere;
		} $sql=$sql1." union " . $sql2;
		/*if ($post_action=="screen"){
			// Split the report
			$query_split = new splitPageResultsReport($page, REPORT_MAX_ROWS_PAGE, $sql, $query_split_numrows);
			$query_split1 = new splitPageResultsReport($page, REPORT_MAX_ROWS_PAGE, $sql1, $query_split_numrows1);
			$query_split2 = new splitPageResultsReport($page, REPORT_MAX_ROWS_PAGE, $sql2, $query_split_numrows2);
			$query_split=$query_split1;
			$query_split_numrows=$query_split_numrows1+$query_split_numrows2;
			$sql=$sql1." union " . $sql2;
		echo $sql.'<br>';
		//	echo $query_split_numrows;
		}*/
		
		$sql_result=tep_db_query($sql);	
		$num_rows=tep_db_num_rows($sql_result);
		if ($num_rows>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($sql_result)){
				if($products_result["id"] == "1"){
					$qty=$products_result["customers_basket_quantity"];
					$customers_name=TEXT_NON_CUSTOMERS;
					$customers_id=$products_result["customers_id"];
					$type='T';
				} else {
					$customers_id=$products_result["customers_id"];
					$customers_query=tep_db_query("select customers_firstname,customers_lastname from ".TABLE_CUSTOMERS ." where customers_id='".$products_result["customers_id"]."'");
					$customers_result=tep_db_fetch_array($customers_query);
					$qty=$products_result["customers_basket_quantity"];
					$customers_name=$customers_result["customers_firstname"]. ' ' .$customers_result["customers_lastname"];
					$type='P';
				}
				$prd_array[]=array(
						"cid"=>$customers_id,
						"type" =>$type,
						"cname"=>$customers_name,
						"quan"=>$qty,
						"tax"=>tep_get_tax_rate($products_result['products_tax_class_id']),
						"id"=>$products_result["products_id"],
						"sumcbq"=>$products_result["sumcbq"],
						"price"=>$products_result["products_price"],
						"cat_id"=>$products_result["categories_id"],
						"cat_name"=>$products_result["categories_name"],
						"name"=>$products_result["products_name"]);
			}
		}

	//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 categories_name");
						
	while($category_result=tep_db_fetch_array($category_query)){
		$category_array[]=array("id"=>$category_result["categories_id"],"text"=>$category_result["categories_name"]);
	}
	
	$sort_array=array(	array('id'=>-1,'text'=>TEXT_CATEGORIES),
						array('id'=>1,'text'=>TEXT_PRODUCTS) );
?>

<!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>
<script language="JavaScript" src="includes/date-picker.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;?></td>
			</tr>
			<tr>
				<td class="pageHeading"></td>
			</tr>
			<!-- --->
			<script language="JavaScript">
		function doSubmit() {
		startDate=date_format(document.f.txt_start_date.value,'','',true);
		endDate=date_format(document.f.txt_end_date.value,'','',true);	
		if(startDate.getTime()>endDate.getTime()) {
			alert("<?php echo REPORT_START_AFTER_END?>");
			return;
		}
		document.f.submit();
			}
				function f_Print() {
					document.f.layout.value="print";
					document.f.target="_blank";
					document.f.submit();
				
				}
				
		function date_format(sourceDate,srcFormat,resultFormat,bolConvertObject){
			var dt;
			if (srcFormat=="")	srcFormat="<?php echo EVENTS_DATE_FORMAT;?>";
			if (resultFormat=="")	resultFormat="<?php echo EVENTS_DATE_FORMAT;?>";
			if (typeof(sourceDate)=="string"){
				if (sourceDate=="") return false;
				if (!bolConvertObject && srcFormat!="" && resultFormat==srcFormat) return sourceDate;
				var splt=sourceDate.split("-");
				if (splt.length!=3) return false;
				switch(srcFormat){
					case "d-m-Y":
						sourceDate=new Date(parseInt(splt[2],10),parseInt(splt[1],10)-1,parseInt(splt[0],10));
						break;
					case "m-d-Y":
						sourceDate=new Date(parseInt(splt[2],10),parseInt(splt[0],10)-1,parseInt(splt[1],10));
						break;

					default:
						sourceDate=new Date(parseInt(splt[0],10),parseInt(splt[1],10)-1,parseInt(splt[2],10));
				}
			}
			
			if (!sourceDate) return false;
			if (bolConvertObject) return sourceDate;
			var day=sourceDate.getDate();
			var month=sourceDate.getMonth()+1;
			if (day<10) day="0"+day;
			var year=sourceDate.getFullYear();
			if (month<10) month="0"+month;
			switch(resultFormat){
				case "d-m-Y":
					var textdate=day+"-"+month+"-"+year;
					break;
				case "m-d-Y":
					var textdate=month+"-"+day+"-"+year;
					break;
				default:
					var textdate=year+"-"+month+"-"+day;
					break;
			}
			return textdate;
		}
		
		function check(mode)
		{
		if(mode==1)
			document.f.chk_detail.checked=false
		else if(mode==2)
			document.f.chk_summary.checked=false
		
		}
	</script>
			<?php 
				echo tep_draw_form("f",FILENAME_CART_DROP_REPORT);
				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">
							<?php $_array=array('d','m','Y');  $replace_array=array('DD','MM','YYYY'); 	$date_format=str_replace($_array,$replace_array,EVENTS_DATE_FORMAT);?>
								  <td nowrap><?php echo REPORT_START_DATE?> <INPUT type="text" name="txt_start_date" size="10" value="<?php echo format_date($date_begin); ?>">
									<a href="javascript:show_calendar('f.txt_start_date',null,null,'<?php echo $date_format;?>');"
									   onmouseover="window.status='Date Picker';return true;"
									   onmouseout="window.status='';return true;"><img border="none" src="images/icon_calendar.gif"/>  
									   </a>
									   &nbsp;&nbsp;
								  <?php echo REPORT_END_DATE?>  <INPUT type="text" name="txt_end_date" size="10" value="<?php echo format_date($date_end) ;?>">
									<a href="javascript:show_calendar('f.txt_end_date',null,null,'<?php echo $date_format;?>');"
									   onmouseover="window.status='Date Picker';return true;"
									   onmouseout="window.status='';return true;"><img border="none" src="images/icon_calendar.gif"/>  
									   </a>
								</td>			
							</tr>
							<tr>
								<td><?php echo tep_draw_separator('pixel_trans.gif',1,5);?></td>
							</tr>
							<tr class="main">
								<td nowrap><?php echo TEXT_CATEGORY . '&nbsp;' . tep_draw_pull_down_menu("cbo_category",$category_array,$category);?>
								<?php 
										echo '&nbsp;&nbsp;&nbsp;&nbsp;' . TEXT_SORT_TYPE . '&nbsp;' . tep_draw_pull_down_menu("cbo_sort",$sort_array,$sort);
										echo '&nbsp;&nbsp;' . tep_draw_checkbox_field("chk_summary",1,$summary_show,'','onClick="javascript:check(1)"') . TEXT_SUMMARY_ONLY;
										echo '&nbsp;&nbsp;' . tep_draw_checkbox_field("chk_detail",1,$detail_show,'','onClick="javascript:check(2)"') . TEXT_DETAIL_ONLY;
								?>
								</td>
								<td align="right" class="main">
									<?php
										echo '<a href="javascript:doSubmit()">' . tep_image_button('button_report_search.gif', IMAGE_SEARCH_PRODUCTS) . '</a>&nbsp;';
									?>
								</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 width="19%" class='dataTableHeadingContent' ><?php echo TEXT_ID;?></td>
					  <TD width="22%" class='dataTableHeadingContent'><?php echo TEXT_PRODUCT_NAME;?></TD>
					  <TD width="22%" class='dataTableHeadingContent' align="left"><?php echo TEXT_PRODUCT_ATTRIBUTES;?></TD>
					  <TD width="22%" class='dataTableHeadingContent' align="left"><?php echo TEXT_PRODUCT_QUANTITY;?></TD>
					  <TD width="20%" class='dataTableHeadingContent' ><?php echo TEXT_CUSTOMER;?></TD>
					  <td width="19%" align="right" class="dataTableHeadingContent"><?php echo TEXT_PRICE;?></td>
					</tr>
					<tr>
						<td colspan="6"><?php echo tep_draw_separator('pixel_trans.gif',1,5);?></td>
					</tr>
					<?php
					
						$prev_cat_id=0;
						$total=0;
						$i=1;
							for ($icnt=0;$icnt<sizeof($prd_array);$icnt++){
								$row=&$prd_array[$icnt];
								$p_id=$row['id'];
								if ($prev_cat_id!=$row["cat_id"]){ // output category
									if($sort==-1){
										if ($total>0) { // output total
									?>
											<tr>
												<td class="dataTableContent" colspan="4"><?php  ?></td>
												<td class="dataTableContent" ><b><?php echo TEXT_TOTAL;?></b></td>
												<Td align="right" class="dataTableContent"><?php echo $currencies->format($total);?></Td>
											</tr>
											<tr>
												<td colspan="6"><?php echo tep_draw_separator('pixel_trans.gif',1,10);?></td>
											</tr>
								<?php $total=0;
										}
								$prev_cat_id=$row["cat_id"];
								?>
									<tr>
										<Td colspan="6" class="dataTableContent"><b><?php echo $row["cat_name"];?></b></Td>
									</tr>
									
							    <?php }?>
							<?php }
							
								if(!$summary_show) { // if show only summary
									 $total_price=tep_add_tax($row['price'],$row['tax']) * $row['quan'] ;
							  ?>
									  <TR class='dataTableRow' height="20">
										  <TD class='dataTableContent'><?php echo $i++; ?></td>
										  <td class="dataTableContent"><?php echo $row['name'];?></td>
										  <td class="dataTableContent">
										  <?php 
											if($row["type"] =='T')
												 $option_sql=tep_db_query("select cba.products_options_id,cba.products_options_value_id from ".TABLE_CUSTOMERS_TEMP_BASKET_ATTRIBUTES." cba where substring(cba.products_id,1,instr(cba.products_id,'{')-1)='".$p_id."' and cba.customers_id='".$row['cid']."'");
											 else
												$option_sql=tep_db_query( " select cba.products_options_id,cba.products_options_value_id from ".TABLE_CUSTOMERS_BASKET_ATTRIBUTES." cba where substring(cba.products_id,1,instr(cba.products_id,'{')-1)='".$p_id."' and cba.customers_id='".$row['cid']."' ");	
												$attributes="";
											 if(tep_db_num_rows($option_sql)>0){
												 while($option_result=tep_db_fetch_array($option_sql)){
												 $attr_sql=tep_db_query("select pov.products_options_values_name from products_options_values pov where  pov.products_options_values_id='".$option_result["products_options_value_id"]."'");			
												 $attr_result=tep_db_fetch_array($attr_sql);
												 $attributes.=$attr_result['products_options_values_name']."," ; 
												}
												$attributes=substr($attributes,0,-1);	
											} else {
												$attributes="-";
											}	echo $attributes;  
										  ?></td>
										   <td class="dataTableContent" align="center" colspan="1"><?php echo $row['quan'];?></td>
										   <TD class='dataTableContent'><?php echo $row['cname'];?></TD>
										  <TD class='dataTableContent' align="right"><?php echo $currencies->format($total_price);?></TD>
									  </TR>
										<?php   $total+=$total_price;
								} // summary show
								if($summary_show ){
									$total_price=tep_add_tax($row['price'],$row['tax']) * $row['quan'] ;
									$total_price_summ=tep_add_tax($row['price'],$row['tax']) * $row['sumcbq'] ;
									if($sort==1 ){
									?>
										<tr>
											<td colspan="6"><?php echo tep_draw_separator('pixel_trans.gif',1,5);?></td>
										</tr>
										<tr class='dataTableRow' height="20">
										<td class='dataTableContent'><?php echo $i++;?>
										<td class='dataTableContent'><?php echo $row['name'] ;?></td>
										<td class='dataTableContent'><?php  ?></td>
										<td class='dataTableContent'><?php echo $row['sumcbq'] ;?></td>
										<td class='dataTableContent'><?php ?></td>
										<td class='dataTableContent' align="right"><?php echo $currencies->format($total_price_summ);?></td>
										</tr>
										
										<tr>
											<td colspan="6"><?php echo tep_draw_separator('pixel_trans.gif',1,5);?></td>
										</tr>
									<?php 
									}
									 $total+=$total_price_summ;
								}
							} 
						if($sort==-1){
							if ($total>0) {
						?>
							<tr>
								<td colspan="6"><?php echo tep_draw_separator('pixel_trans.gif',1,5);?></td>
							</tr>
							<tr>
								<td class="dataTableContent" colspan="4"></td>
								<td class="dataTableContent" ><b><?php echo TEXT_TOTAL;?></b></td>
								<Td align="right" class="dataTableContent"><?php echo $currencies->format($total);?></Td>
							</tr>
					<?php } 
					}
					?>
					</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');
?>
