<?php
chdir(dirname(__FILE__));
include("../actions/dbvars.inc");

$timestart=time();

function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}



$gap=2*24*60*60;
$oldtime=$timestart-$gap;

$nstart='2014-09-01 00:00:00';
$nend='2014-09-30 23:59:59';
$caveat_str='';

$groupid=0;
$bdm_groupid=0;
$supergroupid=20;
$usertype=10;
$suppid=0;
$firstrun=1;
$reptype=0;
$now=time();
$match_by_dept=0;
$by_buyer=0;
$buyid=0;

$qb="select DISTINCT group_to_retailer.f_rid, RETAILERS.RETAILER_NAME FROM group_to_retailer LEFT JOIN RETAILERS ON RETAILERS.RID=group_to_retailer.F_RID WHERE group_to_retailer.f_rtid in(select rtid from retailer_types where superid=$supergroupid)";
$qb.=" order by RETAILER_NAME ASC";

$qbpass="select group_to_retailer.f_rid FROM group_to_retailer WHERE  exists (select rtid from retailer_types where superid=$supergroupid and rtid=group_to_retailer.f_rtid)";

$qexist=$qbpass." and group_to_retailer.f_rid=compord.rid";
$qexist2=$qbpass." and group_to_retailer.f_rid=retailers_to_suppliers.f_rid";


$qo="select compord.id, compord.rid, compord.sid, sum(comporddet.invoice_value), sum(comporddet.discount_value), count(comporddet.id),compord.document_date,  compord.doc_type, compord.invoice_no, rebate_master.rebate_percentage, supergroup_to_supplier.rebate_useprod, supergroup_to_supplier.rebate_method from compord left join comporddet on comporddet.compordid=compord.id and (comporddet.active='0' or comporddet.active is null) left join supergroup_to_supplier on supergroup_to_supplier.suppid=compord.sid and supergroup_to_supplier.supergroupid=$supergroupid left join rebate_master on rebate_master.sid=compord.sid and rebate_master.rid=compord.rid and rebate_master.supergroupid=$supergroupid where ";

	if($buyid==0){
	$qo.=" exists ($qexist) ";
	}
	else{
	$qo.=" compord.rid=$buyid";
	}

	$qo.=" and compord.doc_type='AINV'";

$qo.=" and (compord.supergroupid=0 or compord.supergroupid=$supergroupid)";
$qo.=" and compord.active=0";

if($suppid!=0){
$qo.=" and compord.sid='$suppid'";
}
else{
$qo.=" and exists(select supergroup_to_supplier.suppid from supergroup_to_supplier where supergroupid=$supergroupid and active=0 and supergroup_to_supplier.suppid=compord.sid)";
}

$qo.=$caveat_str;

$qo.=" AND compord.document_date between '$nstart' and '$nend'";

$qo.=" group by compord.id, compord.rid, compord.sid, compord.document_date,  compord.doc_type, compord.invoice_no, rebate_master.rebate_percentage, supergroup_to_supplier.rebate_useprod, supergroup_to_supplier.rebate_method order by compord.id asc";

echo("<strong>Query : </strong>$qo");


$time_start = microtime_float();
$hostname='40.127.106.136';
$myDB='bgate_system_main';
$myUser='bgate_odbc';
$myPass='s1m0nSAYS';
$connectionInfo = array("Database"=>$myDB, "UID" => $myUser, "PWD" => $myPass);

$dsn = 'sqlsrv:server=40.127.106.136;database=bgate_system_main';
$user = 'bgate_odbc';
$password = 's1m0nSAYS';

try {
    $db = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo '<br><br>PDO Connection failed: ' . $e->getMessage().'<br>';
}

if(isset($db)){
$ro=sqlsrv_query($qo, $db) or die($qo);

$time_end = microtime_float();
$time = $time_end - $time_start;

$time=sprintf("%01.3f", $time);
$now=time();

echo("<br><br><strong>SQLSRV - $time sec</strong><br>");
}

$time_start = microtime_float();

$db=odbc_connect($dbnam,$usr,$pwd);
$ro=odbc_exec($db,$qo) or die($qo);

$time_end = microtime_float();
$time = $time_end - $time_start;

$time=sprintf("%01.3f", $time);
$now=time();

echo("<br><strong>ODBC - $time sec</strong><br>");




?>