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