<?php


session_start();
require_once('Spreadsheet/Excel/Writer.php');

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass'])
		or die("Could not connect to DB");
 mysql_select_db($_SESSION['dbName'],$connection) or die("Could not find DB");

include("config.inc.php");

//Set Time Execution For the Report
//ini_set('max_execution_time', $config['ReportTimeExecution']);
$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}



$date = $_GET['fromdate'];
$date2 = $date;
$date3 = explodeword($date);

$result = mysql_query("select monthname('$date3'),date_format('$date3','%Y')");

$row = mysql_fetch_array($result);

$datepage = $row[0].",".$row[1];	

$query = "SELECT salesman_code,
       ledger_name,
       salesmen_mst,
       salesmen_cst,
       sum(sales_achievement),
       sum(sales_sip),
       sum(sales_immediatepay),
       sum(soldamount),
       sum(collect_achievement),
       sum(collect_sip),
       sum(collect_immediatepay),
       sum(collectamount),
       sum(sales_immediatepay+collect_immediatepay) AS balance ,
       sum(sold2base2),
       sum(collected2base2),immte_payout
	   
FROM
  (


  SELECT salesman_code,
          salesmen.ledger_name,
          budgetid,
          salesmen_mst,
          salesmen_cst,

          soldbase2,
          round(Soldbase2*100/salesmen_mst,0) AS sales_achievement,
          salesmen_budgetdt_sip AS sales_sip,
          (salesmen_budgetdt_sip * immediate_payout/100) AS sales_immediatepay ,
          soldBase2 AS soldamount,
          0 AS collect_achievement ,
          0 AS collect_sip,
          0 AS collect_immediatepay,
          0 AS collectamount,
          soldBase2 AS sold2base2,
          0 AS collected2base2,
       immte_payout
   FROM
     (SELECT Sales.SALESMAN_CODE,
             salesmen_budget.salesmen_mst,
             salesmen_budget.salesmen_cst,
             salesmen_budget.id AS budgetid,
             immediate_payout,immediate_payout as immte_payout,
             round(sum((ifnull(Sale_TaxableBase2,0)+ifnull(Sale_NoneTaxableBase2,0)-ifnull(Sale_DiscountBase2,0))*if(journal_type='SI',1,-1)),2) AS soldbase2
      FROM Sales
      LEFT JOIN SALESMEN_BUDGET ON SALESMEN_BUDGET.SALESMEN_CODE = Sales.Salesman_code
      and '".explodeword($date2)."' >= SALESMEN_BUDGET.SALESMEN_STARTDATE
        AND '".explodeword($date2)."' <= concat(SALESMEN_BUDGET.SALESMEN_ENDDATE,' 23:59:59')
      WHERE Sales.Sale_date >= SALESMEN_BUDGET.SALESMEN_STARTDATE
        AND Sales.Sale_date <= concat(SALESMEN_BUDGET.SALESMEN_ENDDATE,' 23:59:59')
      GROUP BY SALESMEN_CODE ) AS budget
   left JOIN salesmen_budgetdt ON budget.budgetid = salesmen_budgetdt.salesmen_budgetid
   AND round(soldBase2*100/salesmen_mst,0) = salesmen_budgetdt.monthly_achievement
   INNER JOIN salesmen ON salesmen.ledger_number = budget.salesman_code


   UNION


   SELECT salesman_code,
                salesmen.ledger_name,
                budgetid,
                salesmen_mst,
                salesmen_cst,
                0 AS sales_achievement ,
                0 AS sales_sip,
                0 AS sales_immediatepay,
                0 AS soldamount,
                collectedbase2,
                round(CollectedBase2*100/salesmen_cst,0) AS collect_achievement,
                salesmen_budgetdt_cip AS collect_sip,
                (salesmen_budgetdt_cip * (100-immediate_payout)/100) AS collect_immediatepay ,
                CollectedBase2 AS collectamount,
                0 AS sold2base2,
                collectedbase2 AS collected2base2
				, 0 as immte_payout

   FROM
     (SELECT Sales.SALESMAN_CODE,
             salesmen_budget.salesmen_mst,
             salesmen_budget.salesmen_cst,
             salesmen_budget.id AS budgetid,
             immediate_payout,
             sum(ifnull(".$HTTP_SESSION_VARS['dbLabel'].".acclink.sale_paidamntbase2,0)) AS collectedbase2
      FROM transdt
      INNER JOIN ".$HTTP_SESSION_VARS['dbLabel'].".acclink ON transdt.transdt_id = ".$HTTP_SESSION_VARS['dbLabel'].".acclink.journal_id
      INNER JOIN sales ON ".$HTTP_SESSION_VARS['dbLabel'].".acclink.link_Ref = sales.sale_reference
      INNER  JOIN salesmen_budget ON sales.salesman_code = salesmen_budget.salesmen_code and
      '".explodeword($date2)."' >= SALESMEN_BUDGET.SALESMEN_STARTDATE
        AND '".explodeword($date2)."' <= concat(SALESMEN_BUDGET.SALESMEN_ENDDATE,' 23:59:59')
      WHERE transdt.trans_date >= SALESMEN_BUDGET.SALESMEN_STARTDATE
        AND transdt.trans_date <= concat(SALESMEN_BUDGET.SALESMEN_ENDDATE,' 23:50:59')
        AND ".$HTTP_SESSION_VARS['dbLabel'].".acclink.journal_type NOT IN ('SI',
                                         'PV',
                                         'CV' )
 
      GROUP BY salesman_code ) AS budget
   left JOIN salesmen_budgetdt ON budget.budgetid = salesmen_budgetdt.salesmen_budgetid
   AND round(CollectedBase2*100/salesmen_cst,0) = salesmen_budgetdt.monthly_achievement
   INNER JOIN salesmen ON salesmen.ledger_number = budget.salesman_code

) AS salesmencommission
where salesman_code = '".$salesmen_code."'
GROUP BY salesman_code,
         ledger_name";
		 
		 $result = mysql_query($query,$connection);
$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);
$printeddate = $printdate[2].'/'.$printdate[1].'/'.$printdate[0];
$workbook = new Spreadsheet_Excel_Writer();

$workbook->send('SalesmenCommission.xls');
$columnWidth = 20;
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'Center',
  'bold'=>1
  ));

$x=2;
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('SalesmenCommission');
$worksheet->setMerge($x, 0, $x, 1);
$worksheet->setMerge(0, 1, 0, 2);
$worksheet->setMerge($x, 2, $x, 3);
$worksheet->setMerge($x, 6, $x, 7);
$worksheet->setMerge($x, 8, $x, 9);
$worksheet->setMerge($x, 10, $x, 11);
$worksheet->setMerge($x, 12, $x, 13);
$worksheet->setMerge($x, 14,$x, 15);
$worksheet->setColumn(2,16,13);
$worksheet->setColumn(8,9,15);
$worksheet->write(0,0,'Date:', $TitleFormat);
$worksheet->write(0,1,$datepage, $TitleFormat);
$worksheet->write(0,7,'Commission Report', $TitleFormat);
$worksheet->write(0,11,'Printed Date :', $TitleFormat);
$worksheet->write(0,12,$printeddate, $TitleFormat);

$NumberFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'Right',
  'bold'=>0
  ));
while($row = mysql_fetch_row($result))



{	



		//$worksheet->setInputEncoding('utf-8');
		


//Display Report Header
$worksheet->write($x,0,'Salesman Code', $TitleFormat);
$worksheet->write($x,2,'Salesman name', $TitleFormat);
$worksheet->write($x,4,'MST', $TitleFormat);
$worksheet->write($x,5,'CST', $TitleFormat);
$worksheet->write($x,6,'Achievement', $TitleFormat);
$worksheet->write($x,8,'SIP', $TitleFormat);
$worksheet->write($x,10,'Total', $TitleFormat);
$worksheet->write($x,12,'IPP', $TitleFormat);
$worksheet->write($x,14,'Commission', $TitleFormat);
$x = $x +1 ;
$worksheet->setMerge($x, 0, $x, 1);
$worksheet->setMerge($x, 2,$x, 3);
$worksheet->setMerge($x, 14,$x, 15);
$worksheet->write($x,6,'Sold %', $TitleFormat);
$worksheet->write($x,7,'Coll %', $TitleFormat);
$worksheet->write($x,8,'Sold', $TitleFormat);
$worksheet->write($x,9,'Coll', $TitleFormat);
$worksheet->write($x,10,'Sold', $TitleFormat);
$worksheet->write($x,11,'Coll', $TitleFormat);
$worksheet->write($x,12,'Sold '.$row[15].'%', $TitleFormat);
$rem = 100-$row[15];
$worksheet->write($x,13,'Coll '.$rem.'%', $TitleFormat);
$rownum = 3;
$totalsold = 0;
$totalcollected = 0;
$totalcommission = 0;
		$rownum=$rownum+1;
		$worksheet->setMerge($rownum, 0, $rownum, 1);
		$worksheet->setMerge($rownum, 2,$rownum, 3);
		$worksheet->setMerge($rownum, 14,$rownum, 15);

		$worksheet->write($rownum,0,$row[0],'');
		$worksheet->write($rownum,2,$row[1],'');
		$worksheet->write($rownum,4,number_format($row[2],2),$NumberFormat);
		$worksheet->write($rownum,5,number_format($row[3],2),$NumberFormat);
		$worksheet->write($rownum,6,number_format($row[4],2),$NumberFormat);
		$worksheet->write($rownum,7,number_format($row[8],2),$NumberFormat);
		$worksheet->write($rownum,8,number_format($row[5],2),$NumberFormat);
		$worksheet->write($rownum,9,number_format($row[9],2),$NumberFormat);
		$worksheet->write($rownum,10,number_format($row[13],2),$NumberFormat);
		$worksheet->write($rownum,11,number_format($row[14],2),$NumberFormat);
		$worksheet->write($rownum,12,number_format($row[6],2),$NumberFormat);
		$worksheet->write($rownum,13,number_format($row[10],2),$NumberFormat);
		$worksheet->write($rownum,14,number_format($row[10]+$row[6],2),$NumberFormat);
		//$worksheet->write($rownum,14,$row[12],$TitleFormat);

$totalsold = $totalsold + round($row[13],2);
$totalcollected = $totalcollected + round($row[14],2);
$totalcommission = $totalcommission + round($row[10]+$row[6],2);

}
$worksheet->setMerge($rownum+2, 14,$rownum+2, 15);
$worksheet->write($rownum+2,9,'Total:',$TitleFormat);
$worksheet->write($rownum+2,10,number_format($totalsold,2),$NumberFormat);
$worksheet->write($rownum+2,11,number_format($totalcollected,2),$NumberFormat);
$worksheet->write($rownum+2,14,number_format($totalcommission,2),$NumberFormat);
$workbook->close();



function explodeword($date)
{
if ($date!=''){
$trim = explode('/',$date);
$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";

return $trim;
}
else return null;
}


function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}


?>