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(); ?>
EcmProducts

Inventory States:


Inventory Category Date Enter Date
Active Not empty states only    

'; $trs=''; $tre=''; $tds=''; $tde=''; $tbs=''; $tbe='
'; $t.=$tbs; $t.=$trs; $t.=$tds1; $t.=" "; $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.=''; $t.=$tde; $t.=$tds; $t.=$r['code']; $t.=$tde; $t.=$tds; $t.=''.$r['name'].''; $t.=$tde; $t.=$tds; $t.=''.$r['product_category_name'].''; $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.=" "; $t.=$tde; $t.=$tds1; $t.=" "; $t.=$tde; $t.=$tds1; $t.=" "; $t.=$tde; $t.=$tds1; $t.=" "; $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; ?>