crm.twinpol.com/modules/EcmStockStates/ListStates.php

<? 
if(!$_GET['date'])$date=date("Y-m-d");
else $date=$GLOBALS['timedate']->to_db_date($_GET['date']);
$exp=explode("-",$date);
$date=date("Y-m-d",mktime(0,0,0,$exp[1],$exp[2],$exp[0])+24*3600);
if(!$date)$date=date("Y-m-d");
include_once("modules/EcmProductReports/vtigerConnector.php");
include_once('modules/EcmCharts/chartHelper.php');
$vc=new vtigerConnector();

?>
<table cellspacing="0" cellpadding="0" border="0"><tr><td><img src="themes/Sugar/images/EcmProducts.gif" style="margin-top: 3px; margin-right: 3px;" alt="EcmProducts" width="16" border="0" height="16"></td><td><h2>Inventory States:</h2></td></tr></table><br />
<ul class="tablist" style="width:100%;">
<li>
	<a class="current" href="#">Basic Search</a>
</li>		
</ul>
<form action="index.php" method="get">
<input type="hidden" name="module" value="EcmStockStates" />
<input type="hidden" name="action" value="ListStates" />
<input type="hidden" name="order_by" value="<?php echo $_REQUEST['order_by'];?>" />
<input type="hidden" name="sorder" value="<?php echo $_REQUEST['sorder'];?>" />
<table style="border-top: 0px none; margin-bottom: 4px;width:100%" class="tabForm" border="0" cellpadding="0" cellspacing="0">
<tbody>
	<tr>	
		<td class="dataLabel" width="10%" nowrap="nowrap">
			Inventory</td>
		<td class="dataField" width="30%" nowrap="nowrap">
		<select name="stock_id">
		<option value="">select</option>
		<?php
		$w=$GLOBALS['db']->query("select id,name from ecmstocks where deleted='0' order by name asc");
		while($r=$GLOBALS['db']->fetchByAssoc($w)){
			echo '<option value="'.$r['id'].'"';
			if($r['id']==$_GET['stock_id'])echo ' selected';
			echo '>'.$r['name'].'</option>';
		}
		?>
		</select>		</td>
		<td class="dataLabel" width="10%" nowrap="nowrap">
			Category</td>
		<td class="dataField" width="30%" nowrap="nowrap">
		<select name="category_id">
		<option value="">select</option>
		<?php
		$w=$GLOBALS['db']->query("select id,name from ecmproductcategories where deleted='0' order by name asc");
		while($r=$GLOBALS['db']->fetchByAssoc($w)){
			echo '<option value="'.$r['id'].'"';
			if($r['id']==$_GET['category_id'])echo ' selected';
			echo '>'.$r['name'].'</option>';
		}
		?>
		</select>		</td>		
		<td class="dataLabel" width="10%" nowrap="nowrap">
			Date</td>
		<td class="dataField" width="30%" nowrap="nowrap">
			<input autocomplete="off" name="date" id="date" value="<? echo $GLOBALS['timedate']->to_display_date($date);?>" title="" tabindex="" size="11" maxlength="10" type="text">
			<img src="themes/default/images/jscalendar.gif" alt="Enter Date" id="date_trigger" align="absmiddle" border="0">
			<script type="text/javascript">
			Calendar.setup ({
			inputField : "date",
			daFormat : "<? echo str_replace("d","%d",str_replace("m","%m",str_replace("Y","%Y",$GLOBALS['timedate']->get_date_format())));?>",
			button : "date_trigger",
			singleClick : true,
			dateStr : "",
			step : 1
			}
			);
			</script>	  	</td>	
	  </tr>
	<tr>	
		<td class="dataLabel" width="10%" nowrap="nowrap">
			Active</td>
		<td class="dataField" width="30%" nowrap="nowrap">
		<select name="active">
		<option value="">All</option>
		<option value="active" <?php if($_REQUEST['active']=="active")echo "selected";?>>Yes</option>
		<option value="inactive" <?php if($_REQUEST['active']=="inactive")echo "selected";?>>No</option>
		</select>
		</td>
		<td class="dataLabel" width="10%" nowrap="nowrap">
			Not empty states only</td>
		<td class="dataField" width="30%" nowrap="nowrap">
		<select name="empty">
		<option value="">All</option>
		<option value="yes" <?php if($_REQUEST['empty']=="yes")echo "selected";?>>Yes</option>
		<option value="no" <?php if($_REQUEST['empty']=="no")echo "selected";?>>No</option>
		</select>		</td>		
		<td class="dataLabel" width="10%" nowrap="nowrap">&nbsp;</td>
		<td class="dataField" width="30%" nowrap="nowrap">&nbsp;</td>	
	  </tr>
</tbody>
</table>

<input class="button" name="submit" value="Search" type="submit" onclick="this.form.action.value='ListStates';">
<input class="button" name="clear" value="Clear" type="button" onclick="location.href='index.php?module=EcmStockStates&action=ListStates';">
<input type="submit" class="button" name="CreateXLS" value="Create XLS" onclick="this.form.action.value='CreateXLS';">
<br />
<?php

$tds1='<td class="listViewThS1">';

$trs='<tr>';
$tre='</tr>';

$tds='<td class="oddListRowS1">';
$tde='</td>';

$tbs='<table cellpadding="0" cellspacing="0" border="0" class="ListView" style="width:100%;">';
$tbe='</table>';

$t.=$tbs;
$t.=$trs;
	$t.=$tds1;
		$t.="&nbsp;";
	$t.=$tde;
		$t.=$vc->sortableColumn("Index","code",$_GET['sorder'],15,"index");
		$t.=$vc->sortableColumn("Name","name",$_GET['sorder'],30,"index");
		$t.=$vc->sortableColumn("Category","product_category_name",$_GET['sorder'],20,"index");
		$t.=$vc->sortableColumn("Quantity","qty",$_GET['sorder'],10,"index");
		$t.=$vc->sortableColumn("Price","price",$_GET['sorder'],15,"index");
		$t.=$vc->sortableColumn("Value","value",$_GET['sorder'],15,"index");
$t.=$tre;
$i=1;

$wh[]="deleted='0'";
if($_GET['category_id'])$wh[]="product_category_id='".$_GET['category_id']."'";
if($_GET['active']=="active")$wh[]="product_active='1'";
if($_GET['active']=="inactive")$wh[]="product_active='0'";

$who[]="deleted='0'";
if($date)$who[]="date_entered<='".$date." 23:59:59'";
if($_GET['stock_id'])$who[]="stock_id='".$_GET['stock_id']."'";

$where=implode(" and ",$wh);
$z="select id,name,code,product_category_name,product_category_id from ecmproducts where ".$where." order by code asc, name asc";
$w=$GLOBALS['db']->query($z);
echo mysql_error();
$sum_qty=0;
$sum_price=0;
while($r=$GLOBALS['db']->fetchByAssoc($w)){
		$value=0;
		$qty=0;
		$price=0;
	
		if($date!=date("Y-m-d",mktime()+24*3600)){
			$arr=array();
			$pp1=0;
			$q1=0;
			$ww=$GLOBALS['db']->query("select quantity as qty,id,price from ecmstockoperations where deleted='0' and type='0' and (in_id IS NULL or in_id='') and product_id='".$r['id']."' and (".implode(" and ",$who).")");
			while($rrr=$GLOBALS['db']->fetchByAssoc($ww)){
				$www=$GLOBALS['db']->query("select quantity from ecmstockoperations where in_id='".$rrr['id']."' and type='1' and in_id IS NOT NULL  and in_id!='' and product_id='".$r['id']."' and (".implode(" and ",$who).") and deleted='0'");
				$rqty=0;
				while($rrp=$GLOBALS['db']->fetchByAssoc($www)){
					$rqty+=$rrp['quantity'];
				}
				$qty=$rrr['qty']-$rqty;
				if($qty>0){
					$arr[]=array("qty"=>$qty,"id"=>$rrr['id'],"price"=>$rrr['price']);
				}
			}

			if(count($arr)>0){
				foreach($arr as $v){
					$q1+=$v['qty'];
					$pp1+=$v['qty']*$v['price'];
				}
			}
			$qty=$q1;
			$value=$pp1;
		
			@$price=$value/$qty;
		}
		else{
			$whs=array();
			$whs[]="deleted='0'";
			$whs[]="product_id='".$r['id']."'";
			if($_GET['stock_id'])$whs[]="stock_id='".$_GET['stock_id']."'";
			$whes=implode(" and ",$whs);
			$wss=mysql_query("select quantity,price from ecmstockstates where ".$whes);
			while($rss=mysql_fetch_array($wss)){
				$qty+=$rss['quantity'];
				@$value+=$rss['quantity']*$rss['price'];
			}
			@$price=$value/$qty;
		}
		$arr1[]=array(
			"id"=>$r['id'],
			"qty"=>round($qty),
			"code"=>$r['code'],
			"name"=>$r['name'],
			"product_category_id"=>$r['product_category_id'],
			"product_category_name"=>$r['product_category_name'],
			"price"=>$price,
			"value"=>$value,
		);
}
if(!$_GET['order_by'])$_GET['order_by']="code";
if(!$_GET['sorder'])$_GET['sorder']="asc";
$arr1=multisort($arr1,array(array('key'=>$_GET['order_by'],'sort'=>$_GET['sorder'])));

if(count($arr1)>0){
	foreach($arr1 as $r){
		if($_REQUEST['empty']=="yes" && ($r['qty']==0 || $r['qty']==""))continue;
		if($_REQUEST['empty']=="no" && $r['qty']!=0 && $r['qty']!="")continue;
		$t.=$trs;
			$t.=$tds;
				$t.='<input type="checkbox" value="'.$r['id'].'" name="check[]">';
			$t.=$tde;
			$t.=$tds;
				$t.=$r['code'];
			$t.=$tde;
			$t.=$tds;
				$t.='<a href="index.php?module=EcmProducts&action=DetailView&record='.$r['id'].'">'.$r['name'].'</a>';
			$t.=$tde;
			$t.=$tds;
				$t.='<a href="index.php?module=EcmProductCategories&action=DetailView&record='.$r['product_category_id'].'">'.$r['product_category_name'].'</a>';
			$t.=$tde;
		
			$t.=$tds;
				$t.=number_format($r['qty'],0,"","");
			$t.=$tde;
			$t.=$tds;
				$t.=number_format($r['price'],2,",",".");
			$t.=$tde;
			$t.=$tds;
				$t.=number_format($r['value'],2,",",".");
			$t.=$tde;
		$t.=$tre;
	
		$sum_qty+=$r['qty'];
		$sum_price+=$r['value'];
	
		$i++;
	}
}
$t.=$trs;
	$t.=$tds1;
		$t.="&nbsp;";
	$t.=$tde;
	$t.=$tds1;
		$t.="&nbsp;";
	$t.=$tde;
	$t.=$tds1;
		$t.="&nbsp;";
	$t.=$tde;
	$t.=$tds1;
		$t.="&nbsp;";
	$t.=$tde;
	$t.=$tds1;
		$t.=number_format($sum_qty,0,"","");
	$t.=$tde;
	$t.=$tds1;
		$t.=@number_format($sum_price/$sum_qty,2,",",".");
	$t.=$tde;
	$t.=$tds1;
		$t.=number_format($sum_price,2,",",".");
	$t.=$tde;
$t.=$tre;

$t.=$tbe;
echo $t;
?>
</form>