mz/crm.e5.pl
1
0
Fork 0
Code Issues 12 Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
crm.e5.pl/modules/EcmProductReports/index.php

453 lines
20 KiB
PHP
Raw Permalink Normal View History

init
2024-04-27 09:23:34 +02:00
<?
include_once("modules/EcmProductReports/vtigerConnector.php");
$vc=new vtigerConnector();
$vc->start=$_GET['start']+1;
$wc[]="purchaseorder.tracking_no!=''";
if($_GET['submit']){
if($_GET['product_name'])$wc[]="poproductrel.productname like '".$_GET['product_name']."%'";
if($_GET['product_code'])$wc[]="poproductrel.productcode like '".$_GET['product_code']."%'";
if($_GET['purchase_order'])$wc[]="purchaseorder.subject like '".$_GET['purchase_order']."%'";
if($_GET['status'])$wc[]="purchaseorder.postatus like '".$_GET['status']."'";
if($_GET['due_date_from'])$wc[]="purchaseorder.duedate>='".$GLOBALS['timedate']->to_display($_GET['due_date_from'],$GLOBALS['timedate']->get_date_format(),"Y-m-d")."'";
if($_GET['due_date_to'])$wc[]="purchaseorder.duedate<='".$GLOBALS['timedate']->to_display($_GET['due_date_to'],$GLOBALS['timedate']->get_date_format(),"Y-m-d")."'";
if($_GET['delivery_time_from']){
$dtf=$GLOBALS['timedate']->to_display($_GET['delivery_time_from'],$GLOBALS['timedate']->get_date_format(),"Y-m-d");
$exp=explode("-",$dtf);
//$ddtf=date("Y-m-d",mktime(0,0,0,$exp[1],$exp[2],$exp[0])-37*24*3600);
$ddtf=date("Y-m-d",mktime(0,0,0,$exp[1],$exp[2],$exp[0]));
$wc[]="purchaseordercf.cf_532>='".$ddtf."'";
}
if($_GET['delivery_time_to']){
$dtt=$GLOBALS['timedate']->to_display($_GET['delivery_time_to'],$GLOBALS['timedate']->get_date_format(),"Y-m-d");
$exp=explode("-",$dtt);
//$ddtt=date("Y-m-d",mktime(0,0,0,$exp[1],$exp[2],$exp[0])-37*24*3600);
$ddtt=date("Y-m-d",mktime(0,0,0,$exp[1],$exp[2],$exp[0]));
$wc[]="purchaseordercf.cf_532<='".$ddtt."'";
}
if($_GET['list_price_from'])$wc[]="poproductrel.listprice>='".$vc->unformatNumber($_GET['list_price_from'])."'";
if($_GET['list_price_to'])$wc[]="poproductrel.listprice<='".$vc->unformatNumber($_GET['list_price_to'])."'";
if($_GET['quantity_from'])$wc[]="poproductrel.quantity>='".$vc->unformatNumber($_GET['quantity_from'])."'";
if($_GET['quantity_to'])$wc[]="poproductrel.quantity<='".$vc->unformatNumber($_GET['quantity_to'])."'";
}
$vc->pp=999;
$vc->where=@implode(" and ",$wc);
if(!$_GET['start'])$vc->limit="0,".$vc->pp;
else $vc->limit=$_GET['start'].",".$vc->pp;
if($_GET['order_by'])$vc->order_by=$_GET['order_by'];
else $vc->order_by="purchaseorder.duedate";
if($_GET['sorder'])$vc->sorder=$_GET['sorder'];
else $vc->sorder="desc";
$vc->getData();
$cnt=$vc->getCount();
$data=$vc->data;
for($i=0;$i<count($data);$i++){
$id=$vc->getIdByCode($data[$i]['productcode']);
$deliverydate=$data[$i]['deliverydate'];
if(($deliverydate<$pom[$id] || !$pom[$id]) && $deliverydate)$pom[$id]=$deliverydate;
if($pom[$id])mysql_query("update ecmproducts set deliverydate='".$pom[$id]."' where id='".$id."'");
if($_REQUEST['category'] || $_REQUEST['active']){
$cat=mysql_fetch_array(mysql_query("select product_category_id as cid,product_active from ecmproducts where code='".$data[$i]['productcode']."'"));
if($cat['cid']!=$_REQUEST['category'])continue;
if(!$cat['product_active'])continue;
}
$dat[]=$data[$i];
}
$data=$dat;
$_SESSION['EcmProductReportsData']=$data;
?>
<table cellspacing="0" cellpadding="0" border="0"><tr><td width="19"><img src="themes/Sugar/images/EcmProducts.gif" style="margin-top: 3px; margin-right: 3px;" alt="EcmProducts" width="16" border="0" height="16"></td>
<td width="402"><h2>Products Reports: Products on sea </h2></td></tr></table><br />
<ul class="tablist">
<li>
<a class="current" href="#">Basic Search</a>
</li>
</ul>
<form action="index.php" method="get">
<input type="hidden" name="module" value="EcmProductReports" />
<input type="hidden" name="action" value="index" />
<table style="border-top: 0px none; margin-bottom: 4px;" class="tabForm" width="100%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td class="dataLabel" width="10%" nowrap="nowrap">
Product </td>
<td class="dataField" width="30%" nowrap="nowrap">
<input name="product_name" id="product_name" size="30" maxlength="255" value="<? echo $_GET['product_name'];?>" title="" tabindex="" type="text"> </td>
<td class="dataLabel" width="10%" nowrap="nowrap">
Code </td>
<td class="dataField" width="30%" nowrap="nowrap">
<input name="product_code" id="product_code" size="30" maxlength="255" value="<? echo $_GET['product_code'];?>" title="" tabindex="" type="text"> </td>
<td class="dataLabel" width="10%" nowrap="nowrap">
Purchase Order </td>
<td class="dataField" width="30%" nowrap="nowrap">
<input name="purchase_order" id="purchase_order" size="30" maxlength="255" value="<? echo $_GET['purchase_order'];?>" title="" tabindex="" type="text"> </td>
</tr>
<tr>
<td class="dataLabel" width="10%" nowrap="nowrap">
Status </td>
<td class="dataField" width="30%" nowrap="nowrap">
<select name="status"><? echo $vc->selectStatus($_GET['status']);?></select> </td>
<td class="dataLabel" width="10%" nowrap="nowrap">
Due Date </td>
<td class="dataField" width="30%" nowrap="nowrap">
<input autocomplete="off" name="due_date_from" id="due_date_from" value="<? echo $_GET['due_date_from'];?>" title="" tabindex="" size="11" maxlength="10" type="text">
<img src="themes/default/images/jscalendar.gif" alt="Enter Date" id="due_date_from_trigger" align="absmiddle" border="0">
<script type="text/javascript">
Calendar.setup ({
inputField : "due_date_from",
daFormat : "<? echo str_replace("d","%d",str_replace("m","%m",str_replace("Y","%Y",$GLOBALS['timedate']->get_date_format())));?>",
button : "due_date_from_trigger",
singleClick : true,
dateStr : "",
step : 1
}
);
</script>
&lt;&gt;
<input autocomplete="off" name="due_date_to" id="due_date_to" value="<? echo $_GET['due_date_to'];?>" title="" tabindex="" size="11" maxlength="10" type="text">
<img src="themes/default/images/jscalendar.gif" alt="Enter Date" id="due_date_to_trigger" align="absmiddle" border="0">
<script type="text/javascript">
Calendar.setup ({
inputField : "due_date_to",
daFormat : "<? echo str_replace("d","%d",str_replace("m","%m",str_replace("Y","%Y",$GLOBALS['timedate']->get_date_format())));?>",
button : "due_date_to_trigger",
singleClick : true,
dateStr : "",
step : 1
}
);
</script> </td>
<td class="dataLabel" width="10%" nowrap="nowrap">
Delivery Time </td>
<td class="dataField" width="30%" nowrap="nowrap">
<input autocomplete="off" name="delivery_time_from" id="delivery_time_from" value="<? echo $_GET['delivery_time_from'];?>" title="" tabindex="" size="11" maxlength="10" type="text">
<img src="themes/default/images/jscalendar.gif" alt="Enter Date" id="delivery_time_from_trigger" align="absmiddle" border="0">
<script type="text/javascript">
Calendar.setup ({
inputField : "delivery_time_from",
daFormat : "<? echo str_replace("d","%d",str_replace("m","%m",str_replace("Y","%Y",$GLOBALS['timedate']->get_date_format())));?>",
button : "delivery_time_from_trigger",
singleClick : true,
dateStr : "",
step : 1
}
);
</script>
&lt;&gt;
<input autocomplete="off" name="delivery_time_to" id="delivery_time_to" value="<? echo $_GET['delivery_time_to'];?>" title="" tabindex="" size="11" maxlength="10" type="text">
<img src="themes/default/images/jscalendar.gif" alt="Enter Date" id="delivery_time_to_trigger" align="absmiddle" border="0">
<script type="text/javascript">
Calendar.setup ({
inputField : "delivery_time_to",
daFormat : "<? echo str_replace("d","%d",str_replace("m","%m",str_replace("Y","%Y",$GLOBALS['timedate']->get_date_format())));?>",
button : "delivery_time_to_trigger",
singleClick : true,
dateStr : "",
step : 1
}
);
</script> </td>
</tr>
<tr>
<td class="dataLabel" nowrap="nowrap">&nbsp;</td>
<td class="dataField" nowrap="nowrap">&nbsp;</td>
<td class="dataLabel" nowrap="nowrap">&nbsp;</td>
<td class="dataField" nowrap="nowrap">&nbsp;</td>
<td class="dataLabel" nowrap="nowrap">&nbsp;</td>
<td class="dataField" nowrap="nowrap">&nbsp;</td>
</tr>
<tr>
<td class="dataLabel" width="10%" nowrap="nowrap">
List Price </td>
<td class="dataField" width="30%" nowrap="nowrap">
<input name="list_price_from" id="list_price_from" size="10" maxlength="255" value="<? echo $_GET['list_price_from'];?>" title="" tabindex="" type="text">
&lt;&gt;
<input name="list_price_to" id="list_price_to" size="10" maxlength="255" value="<? echo $_GET['list_price_to'];?>" title="" tabindex="" type="text"> </td>
<td class="dataLabel" width="10%" nowrap="nowrap">
Quantity </td>
<td class="dataField" width="30%" nowrap="nowrap">
<input name="quantity_from" id="quantity_from" size="10" maxlength="255" value="<? echo $_GET['quantity_from'];?>" title="" tabindex="" type="text">
&lt;&gt;
<input name="quantity_to" id="quantity_to" size="10" maxlength="255" value="<? echo $_GET['quantity_to'];?>" title="" tabindex="" type="text"> </td>
<td class="dataLabel" width="10%" nowrap="nowrap">
Category </td>
<td class="dataField" width="30%" nowrap="nowrap">
<select name="category"><option value="">select</option>
<?
$w=mysql_query("select id,name from ecmproductcategories where deleted='0' order by name asc");
while($r=mysql_fetch_array($w)){
echo '<option value="'.$r['id'].'"';
if($r['id']==$_REQUEST['category'])echo ' selected';
echo '>'.$r['name'].'</option>';
}
?>
</td>
</tr>
<tr>
<td class="dataLabel" nowrap="nowrap">Product active </td>
<td class="dataField" nowrap="nowrap">
<select name="active">
<option value="" <? if($_REQUEST['active']=="")echo "selected";?>>select</option>
<option value="1" <? if($_REQUEST['active']=="1")echo "selected";?>>Yes</option>
<option value="0" <? if($_REQUEST['active']=="0")echo "selected";?>>No</option>
</select>
</td>
</tr>
</tbody>
</table>
<input class="button" name="submit" value="Search" type="submit">&nbsp;
<input class="button" name="clear" value="Clear" type="button" onclick="location.href='index.php?module=EcmProductReports&action=index';">
</form>
<table class="h3Row" width="100%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td nowrap="nowrap">
<h3>Products List</h3>
</td>
<td width="100%">
<img src="include/images/blank.gif" alt="" width="1" height="1">
</td>
</tr>
</tbody>
</table>
<?
if($_GET['start']+$vc->pp>=$vc->count && $_GET['start']-$vc->pp>0){ // >= >=
$btns=array(0,0,0,0);
}
elseif($_GET['start']+$vc->pp<$vc->count && $_GET['start']-$vc->pp>=0){ // < >=
$btns=array(1,1,1,1);
}
elseif($_GET['start']+$vc->pp>=$vc->count){ // >= <
$btns=array(1,1,0,0);
}
elseif($_GET['start']+$vc->pp<$vc->count && $_GET['start']-$vc->pp<0){ // < <
$btns=array(0,0,1,1);
}
print '<form action="index.php" method="post">';
print '<input type="hidden" value="EcmProductReports" name="module">';
print '<input type="hidden" value="CreateXLS" name="action">';
print '<input type="hidden" name="where" value="'.$vc->where.'">';
print '<input type="hidden" name="order_by" value="'.$vc->order_by.'">';
print '<input type="hidden" name="sorder" value="'.$vc->sorder.'">';
print '<input type="hidden" name="limit" value="'.$vc->limit.'">';
print '<input type="hidden" name="start" value="'.($vc->start-1).'">';
print '<input type="hidden" name="to_pdf" value="1">';
print '<div id="products_list">';
print '<script language="javascript">
function check_all_products(v){
var ch=document.getElementsByTagName("input");
for(var i=0;i<ch.length;i++){
if(ch[i].id!="chall"){
if(v==true){
ch[i].checked=true;
}
else{
ch[i].checked=false;
}
}
}
}
</script>';
print '<table cellpadding="0" cellspacing="0" width="1300" border="0" class="ListView">';
?>
<tr>
<td colspan="9">
<table width="100%">
<tr>
<td width="50%">
<?
print '<input type="submit" value="Create XLS" class="button" onclick="this.form.action.value=\'CreateXLS\';">&nbsp;';
print '<input type="submit" value="Create PDF" class="button" onclick="this.form.action.value=\'CreatePDF\';">';
?>
</td>
<td id="listViewPaginationButtons" class="listViewPaginationTdS1" width="100%" align="right" nowrap="nowrap">
<? echo $vc->generateButtons($btns); ?>
</td>
</tr>
</table>
</td></tr>
<?
print '<tr>';
print '<td class="listViewThS1" width="1%"><input type="checkbox" id="chall" onclick="check_all_products(this.checked)" checked></td>';
print $vc->sortableColumn("Product","poproductrel.productname",$_GET['sorder'],20);
print $vc->sortableColumn("Code","poproductrel.productcode",$_GET['sorder'],10);
print $vc->sortableColumn("Category","poproductrel.productname",$_GET['sorder'],10);
print $vc->sortableColumn("Order Id","purchaseorder.puchaseorderid",$_GET['sorder'],5);
//print $vc->sortableColumn("Sale","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Sale last 3 m","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Sale last 6 m","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Avg price","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Avg price last 3 m","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Avg price last 6 m","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Qty","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Qty last 3 m","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Qty last 6 m","poproductrel.productname",$_GET['sorder'],10);
print $vc->sortableColumn("Quantity","poproductrel.quantity",$_GET['sorder'],10);
print $vc->sortableColumn("Inventory","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Inv value","poproductrel.productname",$_GET['sorder'],10);
print $vc->sortableColumn("CBM","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Avg qty","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Avg qty last 3 m","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Avg qty last 6 m","poproductrel.productname",$_GET['sorder'],10);
//print $vc->sortableColumn("Status","purchaseorder.postatus",$_GET['sorder'],10);
//print $vc->sortableColumn("Due Date","purchaseorder.duedate",$_GET['sorder'],10);
print $vc->sortableColumn("Delivery Time","purchaseordercf.cf_532",$_GET['sorder'],10);
//print $vc->sortableColumn("List Price","poproductrel.listprice",$_GET['sorder'],10);
print $vc->sortableColumn("Tracking No","purchaseorder.tracking_no",$_GET['sorder'],10);
print '</tr>';
for($i=0;$i<count($data);$i++){
$sale30=0;
$qty30=0;
$aq30=array();
$sale90=0;
$qty90=0;
$aq90=array();
$sale180=0;
$qty180=0;
$aq180=array();
$id=$vc->getIdByCode($data[$i]['productcode']);
$duedate=$data[$i]['duedate'];
$deliverydate=$data[$i]['deliverydate'];
$exp=explode("-",$duedate);
//$dtime=date("Y-m-d",@mktime(0,0,0,$exp[1],$exp[2],$exp[0])+37*24*3600);
$dtime=date("Y-m-d",@mktime(0,0,0,$exp[1],$exp[2],$exp[0]));
$w=mysql_query("select ecmproducts_fk_list.price as price,ecmproducts_fk_list.quantity as quantity,ecmproducts_fk_list.no as no,ecmproducts_fk_list.date as date from ecmproducts_fk_list where ecmproducts_fk_list.code like '".$data[$i]['productcode']."'");
//echo mysql_num_rows();mysql_error();die();
while($r=mysql_fetch_array($w)){
$sale+=$r['price']*$r['quantity'];
$qty+=$r['quantity'];
$aq[$r['no']]+=$r['quantity'];
$date=date("Y-m");
$m=(int)date("m");
$date1=date("Y-m",mktime(0,0,0,($m-1),1,date("Y")));
$date2=date("Y-m",mktime(0,0,0,($m-2),1,date("Y")));
$date3=date("Y-m",mktime(0,0,0,($m-3),1,date("Y")));
$date4=date("Y-m",mktime(0,0,0,($m-4),1,date("Y")));
$date5=date("Y-m",mktime(0,0,0,($m-5),1,date("Y")));
$date6=date("Y-m",mktime(0,0,0,($m-6),1,date("Y")));
$d=explode("-",$r['date']);
$dd=$d[0]."-".$d[1];
if($dd==$date || $dd==$date1){
$sale30+=$r['price']*$r['quantity'];
$qty30+=$r['quantity'];
$aq30[$r['no']]+=$r['quantity'];
}
if($dd==$date || $dd==$date1 || $dd==$date2 || $dd==$date3){
$sale90+=$r['price']*$r['quantity'];
$qty90+=$r['quantity'];
$aq390[$r['no']]+=$r['quantity'];
}
if($dd==$date || $dd==$date1 || $dd==$date2 || $dd==$date3 || $dd==$date4 || $dd==$date5 || $dd==$date6){
$sale180+=$r['price']*$r['quantity'];
$qty180+=$r['quantity'];
$aq180[$r['no']]+=$r['quantity'];
}
}
$rc=mysql_fetch_array(mysql_query("select product_category_name,product_category_id from ecmproducts where id='".$id."'"));
$category='<a href="index.php?module=EcmProductCategories&action=DetailView&record='.$rc['product_category_id'].'">'.$rc['product_category_name'].'</a>';
print '<tr>';
print '<td class="oddListRowS1" width="1%"><input type="checkbox" value="1" name="check['.$i.']" checked></td>';
print '<td class="oddListRowS1" width="20%"><a href="index.php?module=EcmProducts&action=DetailView&record='.$id.'">'.$data[$i]['productname'].'</a></td>';
print '<td class="oddListRowS1" width="10%">'.$data[$i]['productcode'].'</td>';
print '<td class="oddListRowS1" width="10%">'.$category.'</td>';
print '<td class="oddListRowS1" width="5%"><a href="http://81.89.194.6/crm/index.php?module=Orders&action=DetailView&record='.$data[$i]['purchaseorderid'].'">'.$data[$i]['purchaseorderid'].'</a></td>';
/*print '<td class="oddListRowS1" width="10%">';
if($qty90>0){
print number_format(($sale90/$qty90),2,",",".");
$saleqtysum90+=($sale90/$qty90);
}
print '</td>';*/
/*print '<td class="oddListRowS1" width="10%">';
if($qty180>0){
print number_format(($sale180/$qty180),2,",",".");
$saleqtysum180+=($sale180/$qty180);
}
print '</td>';
print '<td class="oddListRowS1" width="10%">'.number_format(($qty90/3),2,",",".").'</td>';
$qtysum90+=($qty90/3);
print '<td class="oddListRowS1" width="10%">'.number_format(($qty180/6),2,",",".").'</td>';
$qtysum180+=($qty180/6);
*/
print '<td class="oddListRowS1" width="10%">'.number_format($data[$i]['quantity'],0,"","").'</td>';
$qtyo+=$data[$i]['quantity'];
$rrrr=mysql_fetch_array(mysql_query("select ems_qty_in_stock,ems_price,pieces_per_carton,carton_volume_meter from ecmproducts where code='".$data[$i]['productcode']."'"));
print '<td class="oddListRowS1" width="10%">'.number_format($rrrr['ems_qty_in_stock'],0,"","").'</td>';
$emsqtyinstock+=$rrrr['ems_qty_in_stock'];
//print '<td class="oddListRowS1" width="10%">'.number_format(($rrrr['ems_price']*$rrrr['ems_qty_in_stock']),2,",",".").'</td>';
$emsqtyinstockinv+=($rrrr['ems_price']*$rrrr['ems_qty_in_stock']);
print '<td class="oddListRowS1" width="10%">';
if($rrrr['pieces_per_carton']>0)print number_format((($rrrr['ems_qty_in_stock']*$rrrr['carton_volume_meter'])/$rrrr['pieces_per_carton']),2,",",".");
else print "0,00";
print '</td>';
if($rrrr['pieces_per_carton']>0)$cbm+=(($rrrr['ems_qty_in_stock']*$rrrr['carton_volume_meter'])/$rrrr['pieces_per_carton']);
//print '<td class="oddListRowS1" width="10%">'.$data[$i]['postatus'].'</td>';
//print '<td class="oddListRowS1" width="10%">'.$vc->timeDate($duedate).'</td>';
print '<td class="oddListRowS1" width="10%">'.$vc->timeDate($deliverydate).'</td>';
//print '<td class="oddListRowS1" width="10%">'.$vc->formatNumber($data[$i]['listprice']).'</td>';
print '<td class="oddListRowS1" width="10%">'.$data[$i]['tracking_no'].'</td>';
$listprice+=$data[$i]['listprice'];
if(($deliverydate<$pom[$r['id']] || !$pom[$r['id']]) && $deliverydate)$pom[$r['id']]=$deliverydate;
if($pom[$r['id']])mysql_query("update ecmproducts set deliverydate='".$pom[$r['id']]."' where id='".$r['id']."'");
print '</tr>';
}
$cnt=count($data);
print '<tr style="background: #e6e6e6; font-weight: bolder;">
<td class="oddListRowS1" colspan="2">Summary</td>
<td class="oddListRowS1"></td>
<td class="oddListRowS1"></td>
<td class="oddListRowS1"></td>
<!--<td class="oddListRowS1">'.@number_format($saleqtysum90/$cnt,2,",",".").'</td>
<td class="oddListRowS1">'.@number_format($saleqtysum180/$cnt,2,",",".").'</td>
<td class="oddListRowS1">'.@number_format($qtysum90/$cnt,2,",",".").'</td>
<td class="oddListRowS1">'.@number_format($qtysum180/$cnt,2,",",".").'</td>-->
<td class="oddListRowS1">'.number_format($qtyo,0,"","").'</td>
<td class="oddListRowS1">'.number_format($emsqtyinstock,0,"","").'</td>
<!--<td class="oddListRowS1">'.number_format($emsqtyinstockinv,2,",",".").'</td>-->
<td class="oddListRowS1">'.number_format($cbm,2,",",".").'</td>
<td class="oddListRowS1"></td>
<td class="oddListRowS1"></td>
<!--<td class="oddListRowS1">'.@number_format($listprice/$cnt,2,",",".").'</td>-->
</tr>';
print '</table>';
print '</div>';
print '</form>';
?>
Reference in New Issue Copy Permalink