87 lines
4.7 KiB
PHP
Executable File
87 lines
4.7 KiB
PHP
Executable File
<?php
|
|
session_start();
|
|
include_once( '/var/www/html/e5crm/config.php');
|
|
include_once( '/var/www/html/e5crm/guid.php');
|
|
$sql=mysql_connect($sugar_config['dbconfig']['db_host_name'],$sugar_config['dbconfig']['db_user_name'],$sugar_config['dbconfig']['db_password']);
|
|
mysql_select_db($sugar_config['dbconfig']['db_name']);
|
|
mysql_query("set names utf8");
|
|
//mysql_query("truncate table ecmproductcategories_realisation");
|
|
$mdate=date("Y-m");
|
|
|
|
//$w=mysql_query("select * from ecmsales where delivery_date like '".$mdate."%' and deleted='0' and status='s30' and type='sales_order'");
|
|
$w=mysql_query("select e.* from ecmsales as e inner join ecmstockdocouts as w on w.so_id=e.id inner join ecminvoiceouts as inv on inv.wz_id=w.id where e.deleted='0' and inv.register_date like '".$mdate."%' and e.type='sales_order' and e.status!='s10'");
|
|
while($r=mysql_fetch_array($w)){
|
|
$rsale=mysql_fetch_array(mysql_query("select sum(quantity*price) as qty from ecmsaleitems where ecmsale_id='".$r['id']."'"));
|
|
$rwz=mysql_fetch_array(mysql_query("select id from ecmstockdocouts where so_id='".$r['id']."'"));
|
|
$rwzi=mysql_fetch_array(mysql_query("select sum(quantity*selling_price) as qty from ecmstockdocoutitems where ecmstockdocout_id='".$rwz['id']."'"));
|
|
$ecmstockdocout_id=$rwz['id'];
|
|
if(mysql_num_rows(mysql_query("select id from ecminvoiceouts where wz_id='".$ecmstockdocout_id."' and deleted='0'"))==0)continue;
|
|
if($ecmstockdocout_id){
|
|
$sum_wz_qty+=$rwzi['qty'];
|
|
$sum_sale_qty+=$rsale['qty'];
|
|
}
|
|
$qty_diff=$rwzi['qty']-$rsale['qty'];
|
|
$diff_minus=$diff_plus=0;
|
|
$rst=$rsale['qty'];
|
|
$rwt=$rwzi['qty'];
|
|
|
|
if($ecmstockdocout_id){
|
|
$wsale=mysql_query("select quantity,price,code,ecmproduct_id from ecmsaleitems where deleted='0' and ecmsale_id='".$r['id']."' order by code");
|
|
while($rsale=mysql_fetch_array($wsale)){
|
|
$qty=0;
|
|
$wwz=mysql_query("select quantity,selling_price from ecmstockdocoutitems where deleted='0' and ecmproduct_id='".$rsale['ecmproduct_id']."' and ecmstockdocout_id='".$ecmstockdocout_id."'");
|
|
while($rwz=mysql_fetch_array($wwz)){
|
|
$qty+=$rwz['quantity']*$rwz['selling_price'];
|
|
}
|
|
|
|
if($rsale['quantity']*$rsale['price']>$qty)$diff_minus+=$qty-$rsale['quantity']*$rsale['price'];
|
|
if($rsale['quantity']*$rsale['price']<$qty)$diff_plus+=$qty-$rsale['quantity']*$rsale['price'];
|
|
|
|
$sum[$rsale['ecmproduct_id']]['wz']+=$qty;
|
|
$sum[$rsale['ecmproduct_id']]['sale']+=$rsale['quantity']*$rsale['price'];
|
|
}
|
|
}
|
|
}
|
|
$percent=number_format((100*$sum_wz_qty/$sum_sale_qty),2,",",".")."%";
|
|
|
|
$rst=0;
|
|
$rwt=0;
|
|
$diff_plus=$diff_minus=0;
|
|
$ww=mysql_query("select id,product_category_id from ecmproducts where deleted='0' order by code asc");
|
|
while($rr=mysql_fetch_array($ww)){
|
|
$k=$rr['id'];
|
|
if(!$sum[$k])continue;
|
|
$rst+=$sum[$k]['sale'];
|
|
$rwt+=$sum[$k]['wz'];
|
|
$cst[$rr['product_category_id']]+=$sum[$k]['sale'];
|
|
$cwt[$rr['product_category_id']]+=$sum[$k]['wz'];
|
|
if($sum[$k]['sale']>$sum[$k]['wz'])$diff_minus+=$sum[$k]['wz']-$sum[$k]['sale'];
|
|
if($sum[$k]['sale']<$sum[$k]['wz'])$diff_plus+=$sum[$k]['wz']-$sum[$k]['sale'];
|
|
}
|
|
|
|
$ww=mysql_query("select id,name from ecmproductcategories where deleted='0' order by name asc");
|
|
while($rr=mysql_fetch_array($ww)){
|
|
$k=$rr['id'];
|
|
if($cwt[$k]<$cst[$k])$color="red";
|
|
elseif($cwt[$k]>$cst[$k])$color="green";
|
|
else $color="black";
|
|
$set="date='".$mdate."',product_category_id='".$rr['id']."',value_sale='".$cst[$k]."',value_wz='".$cwt[$k]."'";
|
|
if(mysql_num_rows(mysql_query("select id from ecmproductcategories_realisation where product_category_id='".$rr['id']."' and date like '".$mdate."%'"))==0){
|
|
mysql_query("insert into ecmproductcategories_realisation set id='".create_guid()."',".$set);
|
|
}
|
|
else mysql_query("update ecmproductcategories_realisation set ".$set." where product_category_id='".$rr['id']."' and date like '".$mdate."%'");
|
|
echo $rr['name']."\n";
|
|
}
|
|
$set="date='".$mdate."',product_category_id='diff_minus',value_sale='".$diff_minus."'";
|
|
if(mysql_num_rows(mysql_query("select id from ecmproductcategories_realisation where product_category_id='diff_minus' and date like '".$mdate."%'"))==0){
|
|
mysql_query("insert into ecmproductcategories_realisation set id='".create_guid()."',".$set);
|
|
}
|
|
else mysql_query("update ecmproductcategories_realisation set ".$set." where product_category_id='diff_minus' and date like '".$mdate."%'");
|
|
$set="date='".$mdate."',product_category_id='diff_plus',value_sale='".$diff_plus."'";
|
|
if(mysql_num_rows(mysql_query("select id from ecmproductcategories_realisation where product_category_id='diff_plus' and date like '".$mdate."%'"))==0){
|
|
mysql_query("insert into ecmproductcategories_realisation set id='".create_guid()."',".$set);
|
|
}
|
|
else mysql_query("update ecmproductcategories_realisation set ".$set." where product_category_id='diff_plus' and date like '".$mdate."%'");
|
|
mysql_close($sql);
|
|
?>
|