to_db_date($_GET['date_from']); $exp=explode("-",$date_from); $date_from=date("Y-m-d",@mktime(0,0,0,$exp[1],$exp[2],$exp[0])+24*3600); if(!$date_from)$date_from=date("Y-m-d"); $date_to=$GLOBALS['timedate']->to_db_date($_GET['date_to']); $exp=explode("-",$date_to); $date_to=date("Y-m-d",@mktime(0,0,0,$exp[1],$exp[2],$exp[0])+24*3600); if(!$date_to)$date_to=date("Y-m-d"); if(!$_REQUEST['year'])$_REQUEST['year']=date("Y"); ?>

RPH Report:

Basic Search

'; $trs=''; $tre=''; $tds=''; $tde=''; $tbs=''; $tbe='

'; echo $tbs; echo $trs; echo $tds1; echo "Lp."; echo $tde; echo $tds1; echo "Name"; echo $tde; echo $tds1; echo "January"; echo $tde; echo $tds1; echo "February"; echo $tde; echo $tds1; echo "March"; echo $tde; echo $tds1; echo "April"; echo $tde; echo $tds1; echo "May"; echo $tde; echo $tds1; echo "June"; echo $tde; echo $tds1; echo "July"; echo $tde; echo $tds1; echo "August"; echo $tde; echo $tds1; echo "September"; echo $tde; echo $tds1; echo "October"; echo $tde; echo $tds1; echo "November"; echo $tde; echo $tds1; echo "December"; echo $tde; echo $tds1; echo $_REQUEST['year']." total"; echo $tde; echo $tds1; echo $_REQUEST['year']." avg"; echo $tde; echo $tds1; echo ($_REQUEST['year']-1)." total"; echo $tde; echo $tds1; echo ($_REQUEST['year']-1)." avg"; echo $tde; echo $tds1; echo "User"; echo $tde; echo $tre; $l=1; $wh[]="deleted='0'"; if($_REQUEST['user_id'])$wh[]="assigned_user_id='".$_REQUEST['user_id']."'"; if($_REQUEST['account_id']){ $wh[]="(parent_id='".$_REQUEST['account_id']."' or id='".$_REQUEST['account_id']."')"; $wh[]="(parent_id is not null or id='".$_REQUEST['account_id']."')"; } if(count($wh)>0)$where=" and ".implode(" and ",$wh); else $where=""; $z="select * from accounts where 1=1".$where." order by name asc"; $w=$GLOBALS['db']->query($z); function getSumWz($year){ /*$w=$GLOBALS['db']->query("select id,type,register_date,currency_value,wz_id,parent_id,ecminvoiceout_id from ecminvoiceouts where register_date like '".$_REQUEST['year']."%' and deleted='0'"); while($r=$GLOBALS['db']->fetchByAssoc($w)){ if(!$r['currency_value']) $currency_value=1; else $currency_value=$r['currency_value']; $d=explode("-",$r['register_date']); $dat=$d[0]."-".$d[1]; if($r['type']!="correct"){ $rr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select sum(price*quantity) as s from ecminvoiceoutitems where ecminvoiceout_id='".$r['id']."' and deleted='0'")); $total_netto=$currency_value*$rr['s']; } else{ $ww=$GLOBALS['db']->query("select price,quantity,ecminvoiceoutitem_id from ecminvoiceoutitems where ecminvoiceout_id='".$r['id']."' and deleted='0'"); $total_netto=0; while($rr=$GLOBALS['db']->fetchByAssoc($ww)){ $rrrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select price,quantity from ecminvoiceoutitems where id='".$rr['ecminvoiceoutitem_id']."'")); $total_netto+=$currency_value*$rr['price']*$rr['quantity']-$currency_value*$rrrr['price']*$rrrr['quantity']; } } if($r['wz_id']){ $rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select parent_id from ecmstockdocouts where id='".$r['wz_id']."'")); $parent_id=$rrr['parent_id']; } else { if($r['type']!="correct")$parent_id=$r['parent_id']; else{ $rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select wz_id from ecminvoiceouts where id='".$r['ecminvoiceout_id']."'")); $rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select parent_id from ecmstockdocouts where id='".$rrr['wz_id']."'")); $parent_id=$rrr['parent_id']; } $parent_id=$r['parent_id']; } $sum[$dat][$parent_id]['tn']+=$total_netto; }*/ $wh=array(); $wh[]="e.deleted='0'"; $wh[]="i.deleted='0'"; $wh[]="(w.deleted is null or w.deleted='0')"; $wh[]="(p.deleted is null or p.deleted='0')"; $wh[]="(ci.deleted is null or ci.deleted='0')"; $wh[]="e.status='accepted'"; $wh[]="e.register_date like '".$year."%'"; if($_REQUEST['account_id'])$wh[]="(e.parent_id='".$_REQUEST['account_id']."' or w.parent_id='".$_REQUEST['account_id']."')"; $q="select sum( CASE WHEN e.type!='correct' THEN CASE WHEN e.currency_value is null or e.currency_value='' THEN i.price*i.quantity ELSE i.price*i.quantity*e.currency_value END ELSE CASE WHEN ci.price is null or ci.quantity is null THEN 0 ELSE i.price*i.quantity-ci.price*ci.quantity END END ) as sales, sum( CASE WHEN e.type!='correct' THEN i.purchase_price*i.quantity ELSE CASE WHEN ci.quantity is null THEN 0 ELSE i.purchase_price*i.quantity-i.purchase_price*ci.quantity END END ) as purchase, sum( CASE WHEN e.type!='correct' THEN i.quantity ELSE CASE WHEN ci.quantity is null THEN 0 ELSE i.quantity-ci.quantity END END ) as quantity, sum( CASE WHEN e.type!='correct' THEN CASE WHEN p.pieces_per_carton>0 and p.pieces_per_carton is not null THEN i.quantity*p.carton_volume_meter/p.pieces_per_carton ELSE 0 END ELSE CASE WHEN ci.quantity is null THEN 0 ELSE CASE WHEN p.pieces_per_carton>0 and p.pieces_per_carton is not null THEN (i.quantity-ci.quantity)*p.carton_volume_meter/p.pieces_per_carton ELSE 0 END END END ) as cbm, w.parent_id as pid, month(e.register_date) as month, year(e.register_date) as year from ecminvoiceoutitems as i left join ecminvoiceoutitems as ci on i.ecminvoiceoutitem_id=ci.id inner join ecminvoiceouts as e on e.id=i.ecminvoiceout_id left join ecmstockdocouts as w on e.wz_id=w.id left join ecmproducts as p on p.id=i.ecmproduct_id where ".implode(" and ",$wh)." group by w.parent_id,month(e.register_date)"; $query=$GLOBALS['db']->query($q); echo mysql_error(); while($r=$GLOBALS['db']->fetchByAssoc($query)){ $sum[$r['year'].'-'.$r['month']][$r['pid']]['tn']+=$r['sales']; } return $sum; } function getCallTime($z){ $www=$GLOBALS['db']->query($z); while($rrr=$GLOBALS['db']->fetchByAssoc($www)){ $acc_ids=array(); $con_ids=array(); $acc_ids[]="e.bean_id='".$rrr['id']."'"; $w=$GLOBALS['db']->query("select contact_id from accounts_contacts where deleted='0' and account_id='".$rrr['id']."'"); while($r=$GLOBALS['db']->fetchByAssoc($w)){ $con_ids[]="e.bean_id='".$r['contact_id']."'"; } if(count($acc_ids)>0){ $w=$GLOBALS['db']->query("select c.duration,e.date_modified from ecmcalls_beans as e inner join cdr as c on c.id=e.ecmcall_id where ".$acc_ids[0]." and e.bean_module='Accounts' and e.date_modified like '".$_REQUEST['year']."%'"); while($r=$GLOBALS['db']->fetchByAssoc($w)){ $d=explode("-",$r['date_modified']); $arr[$rrr['id']][$d[0]."-".$d[1]]['duration']+=$r['duration']; $arr[$rrr['id']][$d[0]."-".$d[1]]['l']++; $arr[$d[0]."-".$d[1]]['duration']+=$r['duration']; $arr[$d[0]."-".$d[1]]['l']++; } } if(count($con_ids)>0){ $w=$GLOBALS['db']->query("select c.duration,e.date_modified from ecmcalls_beans as e inner join cdr as c on c.id=e.ecmcall_id where (".implode(" or ",$con_ids).") and e.date_modified like '".$_REQUEST['year']."%'"); while($r=$GLOBALS['db']->fetchByAssoc($w)){ $d=explode("-",$r['date_modified']); $arr[$rrr['id']][$d[0]."-".$d[1]]['duration']+=$r['duration']; $arr[$rrr['id']][$d[0]."-".$d[1]]['l']++; $arr[$d[0]."-".$d[1]]['duration']+=$r['duration']; $arr[$d[0]."-".$d[1]]['l']++; } } } return $arr; } function getMinSec($v){ $min=floor($v/60); $sec=$v-$min*60; if($min>0 || $sec>0)$str=$min."m ".$sec."s"; return $str; } $swz1=getSumWz($_REQUEST['year']); $swz11=getSumWz($_REQUEST['year']-1); if($_REQUEST['account_id'] && $_REQUEST['user_id']){ $time=getCallTime($z); $_SESSION['EcmSalesReport_time']=$time; } //echo '

';print_r($time);echo '

'; //die(); //echo '

';print_r($swz1);echo '

'; echo ''; echo ''; echo ''; while($r=$GLOBALS['db']->fetchByAssoc($w)){ echo $trs; echo $tds; echo $l; echo $tde; echo $tds; echo ''.$r['name'].''; echo $tde; for($i=1;$i<=12;$i++){ if($i<10)$n="0".$i; else $n=$i; $swz=$swz1[$_REQUEST['year']."-".$i][$r['id']]; $total_netto=$swz['tn']; $rval=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select value from ecmsalesreports_predictions where account_id='".$r['id']."' and year='".$_REQUEST['year']."' and month='".$i."' and deleted='0'")); $value=$rval['value']; echo $tds; echo number_format($total_netto,2,",","."); //if($i>(int)date("m")){ echo '

('.number_format($value,2,",",".").')

'.getMinSec($time[$r['id']][$_REQUEST['year']."-".$n]['duration']).' - '.$time[$r['id']][$_REQUEST['year']."-".$n]['l'].''; //} echo $tde; $sum_total_netto[$_REQUEST['year']."-".$n]+=$total_netto; $sum_total_netto[$_REQUEST['year']]+=$total_netto; $account_total[$r['id']]+=$total_netto; } for($i=1;$i<=12;$i++){ if($i<10)$n="0".$i; else $n=$i; $swzm=$swz11[($_REQUEST['year']-1)."-".$i][$r['id']]; $total_netto_m=$swzm['tn']; $sum_total_netto_m[($_REQUEST['year']-1)."-".$n]+=$total_netto_m; $sum_total_netto_m[$_REQUEST['year']-1]+=$total_netto_m; $account_total_m[$r['id']]+=$total_netto_m; } echo $tds; echo number_format($account_total[$r['id']],2,",","."); echo $tde; echo $tds; echo number_format(($account_total[$r['id']]/((int)date("m"))),2,",","."); echo $tde; echo $tds; echo number_format($account_total_m[$r['id']],2,",","."); echo $tde; echo $tds; echo number_format(($account_total_m[$r['id']]/12),2,",","."); echo $tde; $ruser=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select first_name,last_name from users where id='".$r['assigned_user_id']."'")); echo $tds; echo $ruser['first_name'].' '.$ruser['last_name']; echo $tde; echo $tre; $l++; } echo $trs; echo $tds1; echo ' '; echo $tde; echo $tds1; echo ' '; echo $tde; for($i=1;$i<=12;$i++){ $allvalue=0; $wacc=$GLOBALS['db']->query("select * from accounts where 1=1".$where." order by name asc"); while($racc=$GLOBALS['db']->fetchByAssoc($wacc)){ $wval=$GLOBALS['db']->query("select value from ecmsalesreports_predictions where account_id='".$racc['id']."' and year='".$_REQUEST['year']."' and month='".$i."' and deleted='0'"); while($rval=$GLOBALS['db']->fetchByAssoc($wval)){ $allvalue+=$rval['value']; } } if($i<10)$n="0".$i; else $n=$i; echo $tds1; echo number_format(($sum_total_netto[$_REQUEST['year'].'-'.$n]),2,",",".")." (".number_format($allvalue,2,",",".").")
".getMinSec($time[$_REQUEST['year']."-".$n]['duration']).' - '.$time[$_REQUEST['year']."-".$n]['l']; echo $tde; } echo $tds1; echo number_format($sum_total_netto[$_REQUEST['year']],2,",","."); echo $tde; echo $tds1; echo number_format(($sum_total_netto[$_REQUEST['year']]/((int)date("m"))),2,",","."); echo $tde; echo $tds1; echo number_format($sum_total_netto_m[$_REQUEST['year']-1],2,",","."); echo $tde; echo $tds1; echo number_format(($sum_total_netto_m[$_REQUEST['year']-1]/12),2,",","."); echo $tde; echo $tds1; echo ' '; echo $tde; echo $tre; echo $tbe; //echo $t; ?>