<?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');
	define('FPDF_FONTPATH','font/');
	require('includes/fpdf.php');
	require('includes/classes/pdfTable.php');
	 $date_offset=(defined('EVENTS_SERVER_DATE_OFFSET')?EVENTS_SERVER_DATE_OFFSET:0); 
	$product_ids=isset($HTTP_GET_VARS["product_ids"])?$HTTP_GET_VARS["product_ids"]:'-1';
	$cat_id=isset($HTTP_GET_VARS["cat_id"])?$HTTP_GET_VARS["cat_id"]:0;
	$formaction="pickpack_pdf";   
	$start_date=isset($HTTP_GET_VARS["start_date"])?$HTTP_GET_VARS["start_date"]:'';
	$end_date=isset($HTTP_GET_VARS["end_date"])?$HTTP_GET_VARS["end_date"]:'';
         
	$date_clause = " and (date_format(o.date_purchased,'%Y-%m-%d')>='". $start_date ."' and date_format(o.date_purchased,'%Y-%m-%d')<='" . $end_date . "')";
	//$date_clause = " and (date_format(o.date_purchased,'%Y-%m-%d')>='". $start_date ."' and date_format(o.date_purchased,'%Y-%m-%d')<='" . $end_date . "')";
  	$sql = "select c.categories_id ,c.categories_name from ". TABLE_CATEGORIES_DESCRIPTION . " c where c.language_id='" . $languages_id . "' order by  categories_name";
  	$category_rec = tep_db_query($sql);
	$orders_products_id="";
	while($row=tep_db_fetch_array($category_rec))
		$category_array[$row[categories_id]]=$row['categories_name'];
	$display_header="";
	if($cat_id=="0")
		$cat_name=TEXT_ALL_CATEGORIES;
	else
	{
		$res = tep_db_query("SELECT categories_name from " . TABLE_CATEGORIES_DESCRIPTION . " where categories_id=" . $cat_id . " and language_id=" . $languages_id);
		$row = mysql_fetch_array($res);
		$cat_name = $row[0]; 
	}
	
	$prd_array=array();
	$parent_details="";
	$space="";
	$add_result=true;
	if($product_ids!="-1")
		do_product_pickpack($product_ids);
	else
		do_category_pickpack($cat_id,1);

//===================================
   if($formaction=="pickpack_pdf")
   {  
     
	$display_header=TEXT_CATEGORY . " : " . $cat_name . "\t";
	$display_header.=TEXT_DATE . " : " . format_date($start_date) . '  ' . TEXT_TO . '  ' . format_date($end_date) . "\t";
	$display_header.=TEXT_TYPE . " : " . TEXT_PICK_AND_PACK;
	//echo $orders_products_id;
	//exit;
	if($orders_products_id!="")
	{
		$orders_products_id=substr($orders_products_id,0,-1);
		tep_db_query("update  " . TABLE_ORDERS_PRODUCTS . "  set packing_slip_printed=1,packslip_printed_time='" . date("y-m-d H:i:s") . "' where orders_products_id in(" .$orders_products_id. ")");
	}
	generate_pickpackpdf($prd_array);
	$report_filename="reports_pickpack_" . $login_id . "_" . time();
	tep_redirect(DIR_WS_CATALOG . "images/$report_filename.pdf");
   }
 	function update_orders_products_id($products_id,$where_clause='1=1')
	{
		global $orders_products_id;
		//echo "SELECT sum(ord.products_quantity) as total_quantity from " . TABLE_ORDERS_PRODUCTS . " ord," . TABLE_ORDERS . " o where o.orders_id=ord.orders_id and ord.products_type='P' and ord.orders_products_status!=3 and ord.products_id=" . $products_id . " and " . $where_clause;
		$res = tep_db_query("SELECT ord.orders_products_id from " . TABLE_ORDERS_PRODUCTS . " ord," . TABLE_ORDERS . " o where o.orders_id=ord.orders_id and ord.products_type='P' and ord.orders_products_status!=3 and ord.products_id=" . $products_id . " and " . $where_clause);
		while($p=tep_db_fetch_array($res))
			$orders_products_id.=$p["orders_products_id"] . ",";
		//echo "SELECT ord.orders_products_id from " . TABLE_ORDERS_PRODUCTS . " ord," . TABLE_ORDERS . " o where o.orders_id=ord.orders_id and ord.products_type='P' and ord.orders_products_status!=3 and ord.products_id=" . $products_id . " and " . $where_clause;
		//exit;
	}

function do_product_pickpack($product_ids)
{
global $where_clause,$date_clause,$languages_id,$orders_products_id;

	$products_sql="select count(ord.orders_id) as ord_count,ord.orders_products_id,cat.categories_id,o.customers_email_address,ord.orders_products_status as status,pd.products_name,p.products_id,p.is_attributes ,(p.products_quantity-p.products_ordered) as products_remaining from " . TABLE_ORDERS . " o," . TABLE_PRODUCTS . " p," . TABLE_PRODUCTS_DESCRIPTION . " pd," . TABLE_PRODUCTS_TO_CATEGORIES . " cat," . TABLE_ORDERS_PRODUCTS . " ord where o.orders_id=ord.orders_id and ord.products_type='P' and ord.products_id=p.products_id and cat.products_id=p.products_id and pd.products_id=p.products_id and ord.orders_products_status<3 and o.orders_status=2 and pd.language_id='" . $languages_id . "' and ord.products_id in (" . $product_ids . ")" . $where_clause . $date_clause
			 . " Group by p.products_id order by cat.categories_id";
	$products_query=tep_db_query($products_sql);
	while($products_result=tep_db_fetch_array($products_query)){
		$add_result=true;
		//if($orders_products_id!="")
			//$orders_products_id.=",";
		//$orders_products_id.=$products_result['orders_products_id'] . ",";
		$icnt++;
		add_product_details($products_result['is_attributes'],$products_result['categories_id'],$products_result['products_id'],1,$icnt);
	}

}

function do_category_pickpack($cat_id,$level)
{
  global $where_clause,$date_clause,$languages_id;
    $cat_sql="select categories_id from " . TABLE_CATEGORIES . " where parent_id=" . $cat_id;
	//echo $cat_sql;
	$cat_result=tep_db_query($cat_sql);
	
	$cat_parent_sql="select parent_id from " . TABLE_CATEGORIES . " where categories_id=" . $cat_id;
	$cat_parent_res=tep_db_query($cat_parent_sql);

	$icnt=0;
	if(tep_db_num_rows($cat_result)>0)
	{
		category_product($cat_id,$cat_parent_res['parent_id'],$level-1,2);
		while($cat_rec=tep_db_fetch_array($cat_result))
		{
			do_category_pickpack($cat_rec['categories_id'],$level+1);
		}
	}
	else
	{
		category_product($cat_id,$cat_parent_res['parent_id'],100,1);
	}
}

function category_product($cat_id,$parent_id,$level,$top_count)
{
    global $category_array,$prd_array,$where_clause,$date_clause,$languages_id,$space,$orders_products_id;
		
		if($level!=100)
		{
			$space=substr($space,0,-7);
		}
		$products_sql="select count(ord.orders_id) as ord_count,ord.orders_products_id,cat.categories_id,o.customers_email_address,ord.orders_products_status as status,pd.products_name,p.products_id,p.is_attributes ,(p.products_quantity-p.products_ordered) as products_remaining from " . TABLE_ORDERS . " o," . TABLE_PRODUCTS . " p," . TABLE_PRODUCTS_DESCRIPTION . " pd," . TABLE_PRODUCTS_TO_CATEGORIES . " cat," . TABLE_ORDERS_PRODUCTS . " ord where o.orders_id=ord.orders_id and ord.products_type='P' and ord.products_id=p.products_id and cat.products_id=p.products_id and pd.products_id=p.products_id and ord.orders_products_status<3 and o.orders_status=2 and cat.categories_id='" . $cat_id . "' and pd.language_id='" . $languages_id . "' " . $where_clause . $date_clause
				 . " Group by p.products_id order by cat.categories_id";
		$products_query=tep_db_query($products_sql);
		if(tep_db_num_rows($products_query)>0)
		{
			while($products_result=tep_db_fetch_array($products_query)){
				//if($orders_products_id!="")
					//$orders_products_id.=",";
				//$orders_products_id.=$products_result['orders_products_id'];
				add_product_details($products_result['is_attributes'],$products_result['categories_id'],$products_result['products_id'],$level,$top_count);
			}
		}
		elseif($level==100 && $top_count==1)
			{
				$products_sql="select count(ord.orders_id) as ord_count,ord.orders_products_id,cat.categories_id,o.customers_email_address,ord.orders_products_status as status,pd.products_name,p.products_id,p.is_attributes ,(p.products_quantity-p.products_ordered) as products_remaining from " . TABLE_ORDERS . " o," . TABLE_PRODUCTS . " p," . TABLE_PRODUCTS_DESCRIPTION . " pd," . TABLE_PRODUCTS_TO_CATEGORIES . " cat," . TABLE_ORDERS_PRODUCTS . " ord where o.orders_id=ord.orders_id and ord.products_type='P' and ord.products_id=p.products_id and cat.products_id=p.products_id and pd.products_id=p.products_id and ord.orders_products_status<3 and o.orders_status=2  and cat.categories_id='" . $parent_id . "' and pd.language_id='" . $languages_id . "' " . $where_clause . $date_clause
				 . " Group by p.products_id order by cat.categories_id";
				//echo $products_sql . "<br>";
				$products_query=tep_db_query($products_sql);
				if(tep_db_num_rows($products_query)>0)
				{
					$space="";
					$parent_details=get_parent_category_name($cat_id,1);
					$parent_array=split(":",$parent_details);
					for($i=0;$i<count($parent_array)-1;$i++)
					{
						$prd_array[]=array("cus_name"=>"",
									"product_id"=>"",
									"products_name"=>"",
									"quantity"=>"",
									"att_name"=>"",
									"orderid"=>"",
									"model"=>"",
									"cat_name"=>$space . $parent_array[$i],
									"sku"=>"");
						$space.="       ";
					}
					$parent_details="";
				}
			}
}
	
function add_product_details($is_attribute,$category_id,$product_id,$level,$top_count)
	{
		global $category_array,$prd_array,$space,$where_clause,$date_clause,$orders_products_id;
		if($is_attribute!="Y" )
			$order_sql="select concat_ws(' ',cus.customers_firstname,cus.customers_lastname) as cus_name,cus.customers_id,o.orders_id,ord.products_id,ord.products_model,o.customers_email_address,ord.products_quantity,ord.products_name,ord.orders_products_id,ord.orders_products_status as status from " .  TABLE_ORDERS . " o," . TABLE_CUSTOMERS . " cus," . TABLE_ORDERS_PRODUCTS . " ord where ord.orders_id=o.orders_id and o.customers_id=cus.customers_id and ord.products_type='P' and ord.orders_products_status<3  and o.orders_status=2 and ord.products_id=" . $product_id . $date_clause . " group by products_id order by cus.customers_firstname";
		else
			$order_sql="select concat_ws(' ',cus.customers_firstname,cus.customers_lastname) as cus_name,cus.customers_id,o.orders_id,ord.products_id,ord.products_model,o.customers_email_address,ord.products_quantity,ord.products_name,ord.orders_products_id,ord.orders_products_status as status,ord.invoice_printed,ord.packing_slip_printed,opa.* from " .  TABLE_ORDERS . " o," . TABLE_CUSTOMERS . " cus," . TABLE_ORDERS_PRODUCTS . " ord," . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " opa where ord.orders_products_status<3  and o.orders_status=2 and opa.orders_id=o.orders_id and opa.orders_products_id=ord.orders_products_id  and ord.orders_id=o.orders_id and o.customers_id=cus.customers_id and ord.products_type='P' and ord.orders_products_status<>3 and ord.products_id=" .$product_id . $where_clause . $date_clause . ' order by opa.orders_products_id,opa.products_options_id';
		//if($product_id==7)
			//echo $order_sql;
		$order_rec = tep_db_query($order_sql);
			$add_result=true;
		if ($add_result){
			//if($level==100 && $top_count==1)
			//{
				$space="";
				$parent_details=get_parent_category_name($category_id,1);
				$parent_array=split(":",$parent_details);
				for($i=0;$i<count($parent_array)-1;$i++)
				{
					$prd_array[]=array("cus_name"=>"",
								"product_id"=>"",
								"products_name"=>"",
								"quantity"=>"",
								"att_name"=>"",
								"orderid"=>"",
								"model"=>"",
								"cat_name"=>$space . $parent_array[$i],
								"sku"=>"");
					$space.="       ";
				}
				$parent_details="";
			//}
		   if($is_attribute!="Y"){
				while($row_p=tep_db_fetch_array($order_rec)){
					$prd_array[]=array(	"cus_name"=>$row_p["cus_name"],
								"product_id"=>$row_p["products_id"],
								"products_name"=>$space . $row_p["products_name"],
								"orderid"=>$row_p["orders_id"],
								"model"=>$row_p["products_model"],
								"quantity"=>tep_get_order_count($row_p['products_id'],"1=1 and ord.orders_products_status!=4 " . $where_clause . $date_clause),
								"att_name"=>"",
								"cat_name"=>$space . $category_array[$category_id],
								'sku'=>tep_get_sku_ids($row_p["products_id"]));
					update_orders_products_id($row_p['products_id'],"1=1 and ord.orders_products_status!=4 " . $where_clause . $date_clause);
				}
			}else{
			$prev_pid=0;
			$key="";
			$sku="";
			$row_count=tep_db_num_rows($order_rec);
			$cus_array=array();
			while($cus_row=mysql_fetch_assoc($order_rec)) {
				$p_id=$cus_row["orders_products_id"];
				//$orders_products_id.=$cus_row['orders_products_id'] . ",";
				update_orders_products_id($cus_row['products_id'],"1=1 and ord.orders_products_status!=4 " . $where_clause . $date_clause);
				if($p_id!=$prev_pid && $key!="" )
				{
					$key=substr($key,0,-1);
					$cus_array[$key][]=array("products_name"=>$prd_name,
							 "products_quantity"=>$prd_qty,
							 "cus_name"=>$cus_name,
							 "products_id"=>$prd_id,
							 "orderid"=>$porderid,
							 "model"=>$pmodel,
							 "orders_products_id"=>$prev_pid,
							 "cat_name"=>$category_array[$category_id],
							 "att_name"=>$att_name,
							 "sku"=>tep_get_sku_ids($cus_row["products_id"],$key));
							 $att_name="";
							 $key="";
							 $prev_pid=$cus_row['orders_products_id'];
							 $key=$cus_row['products_options_id'] . '{' . $cus_row['products_options_values_id'] . '}-';
							 $att_name=$cus_row['products_options'] . " : " . $cus_row['products_options_values'] . chr(10).chr(13);
						}else{
							$prev_pid=$cus_row['orders_products_id'];
							$cus_name=$cus_row['cus_name'];
							$pmodel=$cus_row['products_model'];
							$porderid=$cus_row['orders_id'];
							$prd_name=$cus_row['products_name'];
							$prd_qty=$cus_row['products_quantity'];
							$prd_id=$cus_row['products_id'];
							$key.=$cus_row['products_options_id'] . '{' . $cus_row['products_options_values_id'] . '}-';
							$att_name.=$cus_row['products_options'] . " : " . $cus_row['products_options_values'] . chr(10).chr(13);
							$sku=tep_get_sku_ids($prd_id,$key); 
						}
					}
					if($key!="")
					{	
						$key=substr($key,0,-1);
						$sku=tep_get_sku_ids($prd_id,$key);
						$cus_array[$key][]=array("products_name"=>$prd_name,
												 "products_quantity"=>$prd_qty,
												 "cus_name"=>$cus_name,
												 "orderid"=>$porderid,
												 "model"=>$pmodel,
												 "products_id"=>$prd_id,
												 "cat_name"=>$category_array[$category_id],
												 "orders_products_id"=>$prev_pid,
												 "att_name"=>$att_name,
												 "sku"=>$sku);
						$att_name="";
						$key="";
						$sku="";
						//$orders_products_id.=$prev_pid . ",";
					}
					$prev_product_name='';
					$ord_total=0;
					$icnt=0;
					if(isset($cus_array))
					{
						foreach($cus_array as $row1)
						{
							$prev_att_name="";
							$ord_sub_total=0;
							$prev_product_name="";
							$icnt++;
							foreach($row1 as $col1)
							{
								if ($prev_product_name!=$col1['products_name']){
										$prev_product_name=$col1['products_name']; 
										$disp_cusname=$prev_product_name;
										$prd_id=$col1['products_id'];
										 
								}
								$sku=$col1['sku'];	
								if ($prev_att_name!=$col1['att_name']){
										$prev_att_name=$col1['att_name']; 
										$disp_attname=$prev_att_name;
								}
								if($icnt>1)
								{
									$disp_cusname="";
									$td_tag="";
								}
								$ord_sub_total+=$col1['products_quantity'];
							  }
							   $ord_total+=$ord_sub_total;
								$prd_array[]=array("cus_name"=> "",
									"product_id"=>$prd_id,
									"products_name"=>$space . $disp_cusname,
									"quantity"=>$ord_sub_total,
									"orderid"=>$col1['orderid'],
									//"model"=>$col1['model'],
									"att_name"=>$disp_attname,
									"cat_name"=>$space . $category_array[$category_id],
									"sku"=>$sku);
						  }	
					  }
					 }
				  }

	}
	
	function get_parent_category_name($category_id,$level)
	{
		global $languages_id,$login_id;
		$sql="Select c.categories_id,c.parent_id,cat.categories_name from " . TABLE_CATEGORIES . " c," . TABLE_CATEGORIES_DESCRIPTION . " cat where cat.categories_id=c.categories_id and cat.language_id='" . $languages_id ."' and c.categories_id=" . $category_id;
		$query=tep_db_query($sql);
		if(tep_db_num_rows($query)>0)
		{
			$data=tep_db_fetch_array($query);
			if($data['parent_id']!=0)
				$parent_details= get_parent_category_name($data['parent_id'],$level+1) . ":" . $data['categories_name'];
			elseif($level!=1)
				return $data['categories_name'];
			else
				return '';
		}
		return $parent_details;
	}

	function generate_pickpackpdf(&$prd_array){
		global $display_header,$login_id;

		// set initial parameters
		$table=new pdfTable("A4","l");
		$table->left_margin=20;
		$table->top_margin=20;
		$table->right_margin=20;
		$table->bottom_margin=20;
		$table->pdfInit();
		
		// Add Styles for table
		$table->AddStyle("heading","color:#727272;bgcolor:#ffffff;font:Arial;size:18;style:B");
		$table->AddStyle("user","color:#000000;bgcolor:#f1f9fe;font:Arial;size:14;style:B;border-color:#7b9ebd");
		$table->AddStyle("subhead","color:#000000;bgcolor:#7b9ebd;font:Arial;size:10;style:B");
		$table->AddStyle("headrow","color:#000000;bgcolor:#C9C9C9;font:Arial;size:10;style:B");
		$table->AddStyle("row","color:#000000;bgcolor:#F0F1F1;font:Arial;size:10");
		$table->AddStyle("query","color:#000000;bgcolor:#FFFFFF;font:Arial;size:11");
		$table->AddStyle("subrow","color:#000000;bgcolor:#FFFFFF;font:Arial;size:10");
		$table->AddStyle("subrowhead","color:#000000;bgcolor:#FFFFFF;font:Arial;size:10;style:B");

		$table->headers["text"]=$display_header;
		$table->headers["style"]="query";
		$table->headers["height"]=12;
		$table->headers["width"]="100%";
		$table->headers["cols"]=3;

		// define column width
		$widths[0]=$table->width*20/100;
		$widths[1]=$table->width*10/100;
		$widths[2]=$table->width*50/100;
		$widths[3]=$table->width*10/100;
		//$widths[4]=$table->width*1/100;
		//$widths[5]=$table->width*5/100; 
		$widths[4]=$table->width*5/100;//-($widths[1]+$widths[0]+$width[2]);
		$printed_category="";
		if(sizeof($prd_array)>0){
			$cat=&$prd_array[0];
			//output header details
			$cols[]=array("text"=>TEXT_PRODUCTS,"width"=>$widths[0],"align"=>"L","style"=>"subrowhead","valign"=>"M");
			$cols[]=array("text"=>TEXT_SKU,"width"=>$widths[1],"align"=>"L","style"=>"subrowhead","valign"=>"M");
			$cols[]=array("text"=>TEXT_ATTRIBUTES,"width"=>$widths[2],"align"=>"L","style"=>"subrowhead","valign"=>"M");
			$cols[]=array("text"=>"","width"=>$widths[3],"align"=>"L","style"=>"subrowhead","valign"=>"M");
			$cols[]=array("text"=>TEXT_QUANTITY,"width"=>$widths[4],"align"=>"L","style"=>"subrowhead","valign"=>"M");
			$table->tableheaders["text"]=$cols;
			unset($cols);

			$prev_product_id=0;
			$prev_cat="";
			$total=0;
			// output content
			for ($icnt=0;$icnt<sizeof($prd_array);$icnt++) {
				$row=&$prd_array[$icnt];
				$product_id=$row["product_id"];
				if(trim($prev_cat)!=trim($row['cat_name']))
				{
					/*if($total>0 && strpos(",".$printed_category.",",",".trim($row['cat_name']).",")===false)
					{
						$cols=array();
						$cols[]=array("text"=>TEXT_TOTAL,"width"=>$widths[0]+$widths[1]+$widths[2],"align"=>"R","style"=>"subrowhead","valign"=>"M");
						$cols[]=array("text"=>$total,"width"=>$widths[3],"align"=>"R","style"=>"subrowhead","valign"=>"M");
						$table->OutputRow($cols,15);
						unset($cols);
						$total=0;
					}*/

					if(strpos(",".$printed_category.",",",".trim($row['cat_name']).",")===false)
					{
						$cols=array();
						$cols[]=array("text"=>$row['cat_name'],"width"=>$widths[0]+$widths[1]+$widths[2]+$widths[3]+$widths[4]+$widths[5]+$widths[6],"align"=>"L","style"=>"headrow","valign"=>"M");
						$table->OutputRow($cols,15);
						unset($cols);
						$prev_cat=$row['cat_name'];
						$printed_category.=trim($row['cat_name']).",";
					}
					
				}
				$product_id=$row["product_id"];
				if($product_id!="")
				{
					$cols=array();
					if (trim($prev_product_id)!=trim($product_id)) {
						
						$cols[]=array("text"=>$row["products_name"],"width"=>$widths[0],"align"=>"L","style"=>"row","valign"=>"M");
						$prev_product_id=$product_id;
					}
					else
					$cols[]=array("text"=>"","width"=>$widths[0],"align"=>"L","style"=>"row","valign"=>"M");
					$cols[]=array("text"=>$row['sku'],"width"=>$widths[1],"align"=>"L","style"=>"row","valign"=>"M");
					$cols[]=array("text"=>$row['att_name'],"width"=>$widths[2],"align"=>"L","style"=>"row","valign"=>"M");
					$cols[]=array("text"=>"","width"=>$widths[3],"align"=>"L","style"=>"row","valign"=>"M");
					$cols[]=array("text"=>$row['quantity'],"width"=>$widths[4],"align"=>"R","style"=>"row","valign"=>"M");
					$table->OutputRow($cols,15);
					unset($cols);
					$total+=$row['quantity'];
				}
			}
			if($total>0)
			{
				$cols=array();
				$cols[]=array("text"=>TEXT_TOTAL,"width"=>$widths[0]+$widths[1]+$widths[2]+$widths[3],"align"=>"R","style"=>"subrowhead","valign"=>"M");
				$cols[]=array("text"=>$total,"width"=>$widths[4],"align"=>"R","style"=>"subrowhead","valign"=>"M");
				$table->OutputRow($cols,15);
				unset($cols);
				$total=0;
			}
		} else {
			$cols[]=array("text"=>TEXT_NO_RECORDS_FOUND,"width"=>"100%","align"=>"C","style"=>"subrow","valign"=>"M");
			$table->OutputRow($cols,15);
			unset($cols);
		}
		// create pdf page
		$report_filename="reports_pickpack_" . $login_id . "_" . time();

		$table->Render("$report_filename.pdf");
	}


//===========Detailed view=======================
/*	while($products_result=tep_db_fetch_array($products_query)){
		$add_result=true;
		if($orders_products_id!="")
				$orders_products_id.=",";
		$orders_products_id.=$products_result['orders_products_id'];

		if($products_result['is_attributes']!="Y" )
			$order_sql="select concat_ws(' ',cus.customers_firstname,cus.customers_lastname) as cus_name,cus.customers_id,o.orders_id,ord.products_id,o.customers_email_address,ord.products_quantity,ord.products_name,ord.orders_products_id,ord.orders_products_status as status from " .  TABLE_ORDERS . " o," . TABLE_CUSTOMERS . " cus," . TABLE_ORDERS_PRODUCTS . " ord where ord.orders_id=o.orders_id and o.customers_id=cus.customers_id and ord.products_type='P' and ord.orders_products_status<3 and ord.products_id=" . $products_result['products_id'] . $date_clause . " order by cus.customers_firstname";
		else
			$order_sql="select concat_ws(' ',cus.customers_firstname,cus.customers_lastname) as cus_name,cus.customers_id,o.orders_id,ord.products_id,o.customers_email_address,ord.products_quantity,ord.products_name,ord.orders_products_id,ord.orders_products_status as status,ord.invoice_printed,ord.packing_slip_printed,opa.* from " .  TABLE_ORDERS . " o," . TABLE_CUSTOMERS . " cus," . TABLE_ORDERS_PRODUCTS . " ord," . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " opa where ord.orders_products_status<3 and opa.orders_id=o.orders_id and opa.orders_products_id=ord.orders_products_id  and ord.orders_id=o.orders_id and o.customers_id=cus.customers_id and ord.products_type='P' and ord.orders_products_status<>3 and ord.products_id=" .$products_result['products_id'] . $where_clause . $date_clause . ' order by opa.orders_products_id,opa.products_options_id';
		$order_rec = tep_db_query($order_sql);
		if(tep_db_num_rows($order_rec)>0)
			$add_result=true;
		if ($add_result){
		   if($products_result['is_attributes']!="Y"){
				while($row_p=tep_db_fetch_array($order_rec)){
					$prd_array[]=array(	"cus_name"=>$row_p["cus_name"],
								"product_id"=>$row_p["products_id"],
								"products_name"=>$row_p["products_name"],
								"quantity"=>$row_p["products_quantity"],
								"att_name"=>"",
								"cat_name"=>$category_array[$products_result['categories_id']]);
				}
			}
			else
			{
				//==========
			$prev_pid=0;
			$key="";
			$row_count=tep_db_num_rows($order_rec);
			$cus_array=array();
			while($cus_row=mysql_fetch_assoc($order_rec)) {
				$p_id=$cus_row["orders_products_id"];
				if($p_id!=$prev_pid && $key!="" )
				{
					$key=substr($key,0,-1);
					$cus_array[$key][]=array("products_name"=>$prd_name,
							 "products_quantity"=>$prd_qty,
							 "cus_name"=>$cus_name,
							 "products_id"=>$prd_id,
							 "orders_products_id"=>$prev_pid,
							 "cat_name"=>$category_array[$products_result['categories_id']],
							 "att_name"=>$att_name);
							$att_name="";
							$key="";
							$prev_pid=$cus_row['orders_products_id'];
							$key=$cus_row['products_options_id'] . '{' . $cus_row['products_options_values_id'] . '}-';
							$att_name=$cus_row['products_options'] . " : " . $cus_row['products_options_values'] . chr(10).chr(13);
						}
						else
						{
							$prev_pid=$cus_row['orders_products_id'];
							$cus_name=$cus_row['cus_name'];
							$prd_name=$cus_row['products_name'];
							$prd_qty=$cus_row['products_quantity'];
							$prd_id=$cus_row['products_id'];
							$key.=$cus_row['products_options_id'] . '{' . $cus_row['products_options_values_id'] . '}-';
							$att_name.=$cus_row['products_options'] . " : " . $cus_row['products_options_values'] . chr(10).chr(13);
						}
					}
					if($key!="")
					{
						$key=substr($key,0,-1);
						$cus_array[$key][]=array("products_name"=>$prd_name,
												 "products_quantity"=>$prd_qty,
												 "cus_name"=>$cus_name,
												 "products_id"=>$prd_id,
												 "cat_name"=>$category_array[$products_result['categories_id']],
												 "orders_products_id"=>$prev_pid,
												 "att_name"=>$att_name);
						$att_name="";
						$key="";
					}
					$prev_product_name='';
					$ord_total=0;
					
					if(isset($cus_array))
					{
						foreach($cus_array as $row1)
						{
							$prev_att_name="";
							foreach($row1 as $col1)
							{
							
								if ($prev_product_name!=$col1['products_name']){
									$prev_product_name=$col1['products_name']; 
									$disp_cusname=$prev_product_name;
								}
								else{
									$disp_cusname="";
									$td_tag="";
								}
								if ($prev_att_name!=$col1['att_name']){
									$prev_att_name=$col1['att_name']; 
									$disp_attname=$prev_att_name;
								}
								else{
									$disp_attname="";
								}
								$ord_total+=$col1['products_quantity'];
								$prd_array[]=array(	"cus_name"=> $col1['cus_name'],
									"product_id"=>$col1["products_id"],
									"products_name"=>$disp_cusname,
									"quantity"=>$col1['products_quantity'],
									"att_name"=>$disp_attname,
									"cat_name"=>$category_array[$products_result['categories_id']]);
						  }	
					  }
					 }
					//print_r($prd_array);
					//exit;
				  }
				//=========
			}
	  }							
   //print_r($prd_array);*/
  //============================summary view=============
 //		echo tep_db_num_rows($products_query);
		//exit;

?>