<?php
include_once('database_connection.php');
if(isset($_GET['view']))
{
if(isset($_GET['iDisplayStart']))
{
  $offset=$_GET['iDisplayStart'];
  $limit=$_GET['iDisplayLength'];
 }
 else
 {

  $offset=0;
  $limit=100;
 }
  try{
$data=array();
	$result=odbc_exec($connection, "select COAID,COAName,Degree,Active,NatureId FROM Accounts ORDER BY
    COAID DESC 
OFFSET $offset ROWS 
FETCH NEXT $limit ROWS ONLY;");
  while ($row = odbc_fetch_array($result)) {
    $data []=$row;
  }
 //print_r($data);
   print json_encode($data);
 }
 catch (Exception $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "\n";
    exit;
  }
}

if(isset($_GET['viewVisit']))
{
$data=array();
	$result=odbc_exec($connection, "select id,COAName,visitdate,appointdate,salesrepid  FROM VisitMaster inner join Accounts on VisitMaster.COAID=Accounts.COAID");
  while ($row = odbc_fetch_array($result)) {
//$id=$row[0];
  	
    $data []=$row;
    
  }
   echo json_encode($data);

}

if(isset($_GET['periodid']))
{
$data=array();
$periodid=$_GET['periodid'];

$stmt    = odbc_prepare($connection, "GenerateInvoiceReceivable_AgingSummary 1,null,NULL");
$res    = odbc_execute($stmt);
while($row=odbc_fetch_array($stmt))
{
     $data []=str_replace("&"," and ", $row);
    // print_r($row);
//($res); 
}
$dd="";
$data12="[";
$sqldelete="delete from aging2";
odbc_exec($connection,$sqldelete);
foreach($data as $keymaster=>$d)
{
//array_keys($d);
//  if($key=="CustomerName")
  $fields=array();
  $data12 .= json_encode($d).",";
   $val=array();
  $fields[]=array_keys($d);
  $val[]=array_values($d);
  //print_r($val);
    $f= "[".implode("],[",$fields[0])."]";
    $v="'".implode("','",$val[0])."'";
  
  // $f=implode(",",$d);
$sql="insert into aging2 ($f) values($v)";
odbc_exec($connection,$sql);
}
 $data12 .="]";
//$data12=str_replace(",]","]", $data12);
//echo $data12;
/*///odbc_execute(result_id)
          /*  $success = odbc_execute($stmt, array($null,$null,$null,$null,$f,$m,$l,$null,$mo,$null,$null,$null,$e,$null,$null,$null,$null));             
$null="null";
$param=array(1,462,null);
  $result=odbc_execute($stmt, $param);*/
  //print_r($res);
 /* $sql=" DECLARE @res varchar(255)  
EXEC @res = test_pro
select @res";
$sql="{call test_pro}";
  $result=odbc_exec($connection,$sql);*/
 /* while ($row = odbc_fetch_array($res)) {
//$id=$row[0];
    
    $data []=$row;
    
  }
 */  
 // print_r($data);

 //$data1=array($data);
  // echo $data1=($data12);  
   // var_dump($data1);
 /*  if (!$data){

printf(odbc_error($connection));

die();

}*/
}
if(isset($_GET['action']) )
{
  if($_GET['action']=="viewaging2")
  {
  $data=array();
  $counter=0;
  $startdate=$_GET['startdate'];
  $enddate=$_GET['enddate'];  

  if($startdate!=null && $startdate!="undefined" && $startdate!="")
  $FromDate=$startdate;//'2020-02-02';
  else
  $FromDate='2020-01-01';

if($enddate!=null && $enddate!="undefined" && $enddate!="")
  $TillDate=$enddate;//'2020-02-02';
  else
  $TillDate=Date("Y-m-d");

  if(isset($_GET['COAID']) && $_GET['COAID']>0)
  { 
  $COAID=$_GET['COAID'];
  } 
  else
  {
  $COAID=null;
  }
  $Under=68;
//}
//}
  if($COAID==null)
   $sql="exec sp_supplieraging 1,null,null,'$FromDate','$TillDate',$Under";
   else 
  $sql="exec sp_supplieraging 1,$COAID,null,'$FromDate','$TillDate',$Under";
/* code for Aging */

//$data=array();
//$periodid=$_GET['periodid'];

//$stmt    = odbc_prepare($connection, "GenerateInvoiceReceivable_AgingSummary 1,null,NULL");
$stmt    = odbc_prepare($connection, $sql);
/*$sql="
  select 
  sum(Debit) as paid ,sum(Credit) as totaldue,sum(Credit)-sum(Debit) as Balance
    ,COAName,
  sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) >0 THEN CASE WHEN ISNULL(gl.VoucherTypeID,gl.VoucherTypeID) <1 THEN gl.Credit 
  ELSE 0 END else 0 end 
      )-sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) >90 THEN CASE WHEN ISNULL(gl.VoucherTypeID,gl.VoucherTypeID) <1 THEN gl.Debit 
  ELSE 0 END else 0 end  
      ) as openingbal,
    
sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) >90 THEN CASE WHEN ISNULL(gl.VoucherDate,gl.VoucherDate) < $FromDate and ISNULL(gl.VoucherTypeID,gl.VoucherTypeID) >0  THEN gl.Credit 
  ELSE 0 END else 0 end 
      )-sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) >90 THEN CASE WHEN ISNULL(gl.VoucherDate,gl.VoucherDate) >= $FromDate and ISNULL(gl.VoucherTypeID,gl.VoucherTypeID) >0 THEN gl.Debit 
  ELSE 0 END else 0 end  
      ) as plus90
    ,
  sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) between 0 and 30 THEN CASE WHEN ISNULL(gl.VoucherDate,gl.VoucherDate) >= $FromDate and ISNULL(gl.VoucherTypeID,gl.VoucherTypeID) >0 THEN gl.Credit 
  ELSE 0 END else 0 end 
      )-sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) between 0 and 30 THEN CASE WHEN ISNULL(gl.VoucherDate,gl.VoucherDate) >= $FromDate and ISNULL(gl.VoucherTypeID,gl.VoucherTypeID) >0 THEN gl.Debit 
  ELSE 0 END else 0 end  
      ) as days30
    ,
  sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) between 30 and 60 THEN CASE WHEN ISNULL(gl.VoucherDate,gl.VoucherDate) >= $FromDate and ISNULL(gl.VoucherTypeID,gl.VoucherTypeID) >0 THEN gl.Credit 
  ELSE 0 END else 0 end 
      )-sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) between 30 and 60 THEN CASE WHEN ISNULL(gl.VoucherDate,gl.VoucherDate) >= $FromDate and ISNULL(gl.VoucherTypeID,gl.VoucherTypeID) >0 THEN gl.Debit 
  ELSE 0 END else 0 end  
      ) as days60
    ,
      sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) between 60 and 90 THEN CASE WHEN ISNULL(gl.VoucherDate,gl.VoucherDate) >= $FromDate and ISNULL(gl.VoucherTypeID,gl.VoucherTypeID) >0  THEN gl.Credit 
  ELSE 0 END else 0 end 
      )-sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) between 60 and 90 THEN CASE WHEN ISNULL(gl.VoucherDate,gl.VoucherDate) >= $FromDate and ISNULL(gl.VoucherTypeID,gl.VoucherTypeID) >0 THEN gl.Debit 
  ELSE 0 END else 0 end  
      ) as days90 from  
    GeneralLedger as gl 
    where Under=$Under 
  
  group by  COAName
";
$result=odbc_exec($connection,$sql);*/
$res    = odbc_execute($stmt);
$c=0;
while($row=odbc_fetch_array($stmt))
{/*
    foreach ($row as $key => $value) {
      # code...
      $row[$key]=utf8_encode($value);
    }*/
    $counter++;
    $row['Id']="".$counter."";

     $data []=$row;
    // print_r($row);
//($res); 
}

 //$d=array_values($data);
 // $data1=json_encode($data);
echo json_encode($data);
  }//end if 
  if($_GET['action']=="viewaging3")
  {

  $data=array();
  $counter=0;
  $startdate=$_GET['startdate'];
  $enddate=$_GET['enddate'];  

  if($startdate!=null && $startdate!="undefined" && $startdate!="")
  $FromDate=$startdate;//'2020-02-02';
  else
  $FromDate='2020-01-01';

if($enddate!=null && $enddate!="undefined" && $enddate!="")
  $TillDate=$enddate;//'2020-02-02';
  else
  $TillDate=Date("Y-m-d");

  if(isset($_GET['COAID']) && $_GET['COAID']>0)
  { 
  $COAID=$_GET['COAID'];
  } 
  else
  {
  $COAID=null;
  }  
  $Under=54;
/* code for Aging */

//$data=array();
//$periodid=$_GET['periodid'];

//$stmt    = odbc_prepare($connection, "GenerateInvoiceReceivable_AgingSummary 1,null,NULL");
if($COAID==null)
   $sql="exec sp_customeraging 1,null,null,'$FromDate','$TillDate',$Under,null,null";
   else 
   $sql="exec sp_customeraging 1,$COAID,null,'$FromDate','$TillDate',$Under,null,null";

$stmt = odbc_prepare($connection,$sql);
$res    = odbc_execute($stmt);
$c=0;
while($row=odbc_fetch_array($stmt))
{/*
    foreach ($row as $key => $value) {
      # code...
      $row[$key]=utf8_encode($value);
    }*/
    $counter++;
    $row['Id']="".$counter."";

     $data []=$row;
    // print_r($row);
//($res); 
}

 //$d=array_values($data);
 // $data1=json_encode($data);
echo json_encode($data);
  
  }
}//end action 

if(isset($_GET['viewaging']))
{
  $data=array();
  $counter=0;
/* code for Aging */
/*
quer for aging 
  select 
  sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) >90 THEN CASE WHEN ISNULL(gl.VoucherDate,gl.VoucherDate) >= '2020-01-01' THEN gl.Debit 
  ELSE 0 END else 0 end 
      )-sum( CASE when  DateDiff(day,ISNULL(gl.VoucherDate,gl.VoucherDate), GETDATE()) >90 THEN CASE WHEN ISNULL(gl.VoucherDate,gl.VoucherDate) >= '2020-01-01' THEN gl.credit 
  ELSE 0 END else 0 end 
      ) from 
  GeneralLedger as gl where COAID=109 
  */
//$data=array();
//$periodid=$_GET['periodid'];

$stmt    = odbc_prepare($connection, "GenerateInvoiceReceivable_AgingSummary 1,null,NULL");
$res    = odbc_execute($stmt);
while($row=odbc_fetch_array($stmt))
{
     $data []=str_replace("&"," and ", $row);
    // print_r($row);
//($res); 
}
$dd="";
$data12="[";
$sqldelete="delete from aging2";
odbc_exec($connection,$sqldelete);
foreach($data as $keymaster=>$d)
{
//array_keys($d);
//  if($key=="CustomerName")
  $fields=array();
  $data12 .= json_encode($d).",";
   $val=array();
  $fields[]=array_keys($d);
  $val[]=array_values($d);
  //print_r($val);
    $f= "[".implode("],[",$fields[0])."]";
    $v="'".implode("','",$val[0])."'";
  
  // $f=implode(",",$d);
$sql="insert into aging2 ($f) values($v)";
odbc_exec($connection,$sql);
}
 $data12 .="]";
//end code for aging





$sqlcount="select count(*) as counter from aging2";
$resultcount=odbc_exec($connection,$sqlcount);
if(odbc_num_rows($resultcount)>0)
{
   $counter=odbc_result($resultcount, 'counter');
}
  if(isset($_GET['iDisplayStart']))
{
  $offset=$_GET['iDisplayStart'];
  $limit=$_GET['iDisplayLength'];
 }
 else if(isset($_GET['start']))
 {
 $offset=$_GET['start'];
  $limit=$_GET['length'];

 }
 else
 {

  $offset=0;
  $limit=$counter;
 }
 if(($offset+$limit)>$counter)
 {
  $limit=($offset+$limit)-$counter;
 }
  /* $sql="select id,CustomerName,Address1,Due,Balance FROM aging2 
ORDER BY
    id DESC ";*/
  /* $sql="select id,CustomerName,Address1,Due,Balance,b30days, FROM aging2 where convert(numeric(18,2),Balance)>0
ORDER BY
    id DESC 
OFFSET $offset ROWS 
FETCH NEXT $limit ROWS ONLY";*/
$sql="select Id,CustomerName,OpeningBalance,Due,Received,[0-30] as b30days,[30-60] as b60days,[60-90]as b90days,Balance FROM aging2 where convert(numeric(18,2),Balance)>0 ORDER BY
    id DESC
OFFSET $offset ROWS 
FETCH NEXT $limit ROWS ONLY";
  $result=odbc_exec($connection, $sql);
  

  while ($row = odbc_fetch_array($result)) {
    
    $data[]=json_encode($row);

  }
   $ret= "[".implode(",",$data)."]";

   //create file if not exists
$fo=fopen("aging.json","w");

//write the json string in file
$fr=fwrite($fo,$ret);

echo $ret;
//  echo "called";
 //print_r($data);   
}
?>