<?php include_once('database_connection.php');
if(!(isset($_SESSION['userid'])))
{
   $res=array('status'=>"Error",'description'=>'Session Expired , Please Login  First ');
   echo json_encode($res);
   return;
}
else
{
  $Userid=$_SESSION['userid'];
}

if(isset($_GET['action']))
{
  //
  if($_GET['action']=="SalesInvoiceCombine")
  {


$data=array();

    
       if((isset($_GET['startdate']) &&  $_GET['startdate']!="undefined" && $_GET['startdate']!="")  && (isset($_GET['enddate']) && $_GET['enddate']!="undefined" && $_GET['enddate']!=""))
            {
                $start=$_GET['startdate'];
                $end=$_GET['enddate'];
                $date="convert(date,InvoiceDate) Between '$start' and '$end' and ";
            }
            else
            {
                $date="";
            }

            if(isset($_GET['supplier']) && $_GET['supplier']!="" && $_GET['supplier']!="undefined")
            {
                $supplier=$_GET['supplier'];
                $supplier="supplierid=$supplier and ";
            }
            else
            {
                $supplier="";
            }
if(isset($_GET['InvoiceID']) && $_GET['InvoiceID']!="" && $_GET['InvoiceID']!="undefined")
            {
                $InvoiceID=$_GET['InvoiceID'];
             /*   $InvoiceID=getnamesingle("InvoiceMaster","ParkingNo","InvoiceID",$InvoiceID,$connection);
                $InvoiceID="i.ParkingNo='$InvoiceID' and ";*/
                $InvoiceID=" invoiceid=".$InvoiceID." and ";
            }
            else
            {
              $InvoiceID="";
            }
            if(isset($_GET['companyid']) && $_GET['companyid']!="" && $_GET['companyid']!="undefined")
            {
                $companyid=$_GET['companyid'];
                $companyid="i.GodownID=$companyid and ";
            }
            else
            {
                $companyid="";
            }
        if(isset($_GET['company']) && $_GET['company']!="" && $_GET['company']!="undefined")
            {
                $companyid=$_GET['company'];
                $companyid="i.GodownID=$companyid and ";
            }
            else
            {
                $companyid="";
            }
            
            $searchbox="".$date."".$supplier."".$companyid."".$InvoiceID;


        $InvoiceType=$_GET['InvoiceType'];
        if(isset($_GET['InvoiceType']))
        {


           // $InvoiceID=$_GET['InvoiceID'];
       $sql="  select ROW_NUMBER() OVER (
      ORDER BY [InvoiceID]
   ) row_num,c.Name as CompanyShortForm,invoicedate,i.KeyField as keyfield,i.CreateLog as CreateByUserName,invoiceid,deliverydate,narration,g.Name as Department,g.godownid as supplier,c.godownid as godownid,InvoiceStatus,'' as col_Action ,billno,totalamount,discountamount,totaltaxamount as taxamount,netamount,shippingmethod,ispriceinclusivetax,ispriceperunit,vatoption,ReferenceInvoiceID,(select Keyfield from InvoiceMaster as im where im.InvoiceID=i.ReferenceInvoiceID) as ReferenceInvoiceNo,PurchaseLedgerID,VATLedgerID, acc.COAName  as SupplierName,acc.Attr6 as SupplierAddress,acc.Attr8 as SupplierContact,acc.Attr11 as SupplierEmail,acc.Attr15 as SupplierTRN, i.SupplierID as supplierid,Email,CustomerName,MobileNumber,i.TrnNo as CustomerTRN,PlateNumber as  platenumber,ChassisNumber as chassisnumber,MulkiyaNo as mulkiyano,mv.MasterValue as vehiclebrand,i.vehiclebrand as VehicleBrandID,mv1.MasterValue as vehiclemodel,i.vehiclemodel as VehicleModelID2,mv2.MasterValue as vehicletype,i.vehicletype as VehicleTypeID,mv3.MasterValue as vehiclecolor,i.VehicleColor as VehicleColorID,vehiclesize,mv4.MasterValue as vehicleyear
   ,i.vehicleyear as VehicleYearID
   from InvoiceMaster as i left outer join godowns  as g on i.Shipperid=g.GodownID
   left outer join Master_Values as mv  on mv.MasterValueID=i.VehicleBrand
left outer join Master_Values as mv1  on mv1.MasterValueID=i.vehiclemodel
 left outer join Master_Values as mv2  on mv2.MasterValueID=i.VehicleType 
 left outer join Master_Values as mv3  on mv3.MasterValueID=i.VehicleColor
 left outer join Master_Values as mv4 on mv4.MasterValueID=i.VehicleYear
   left outer join godowns as c on c.GodownID=i.GodownID
   left outer join accounts as acc on acc.COAID=i.SupplierID
   where $searchbox InvoiceType=".$InvoiceType."  and InvoiceStatus in(2,4) Order by i.Invoiceid ";

   $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    //'.$row["CategoryID"].'
    //$limit=425;//
    //echo  odbc_num_rows($result);

   //pagination code here 
/*    if($totalrecord>$offset)
  $currentpage=$offset+1;
else
  $currentpage="";

  if($totalrecord>$currentpage)
  $nextpage=$currentpage+1;
  else
  $nextpage="";
  if($currentpage>1)
  $preivouspage=$currentpage-1;
  else
  $preivouspage="";

  $lastpage=$totalrecord;

  $firstpage=1;
    $data['nav']=array('CurrentPage'=>$currentpage,'NextPage'=>$nextpage,'FirstPage'=>$firstpage,'LastPage'=>$lastpage,'PreviousPage'=>$preivouspage);
*/

  while ($row = odbc_fetch_array($result)) {
     /*   $row['col_Action']='<i class="fa fa-pencil fa-lg" style="text-align: left;color: blue;cursor:pointer;" onclick="javascript:editMaster(this,\''.$row["invoiceid"].'\');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteMaster(this,\''.$row["invoiceid"].'\');"></i>&nbsp;<i class="fa fa-print fa-lg" style="text-align: left;color: green;cursor:pointer;" onclick="javascript:printMaster(this,'.$row["godownid"].',
        '.$row["invoiceid"].'\');"></i>';*/
       // $row['ItemName']=htmlspecialchars($row['ItemName']);
      //  $row['ProductCategoryName']=getnamesingle("inventory","Name","Under",$row['ProductCategoryName'],$connection);
      // $row['CompanyShortForm']=getnamesingle("Godowns","Keyfield","GodownID",$row['CompanyShortForm'],$connection);
       // $row['Code']=htmlentities($row['Code']);
    $data ['allinvoice'][]=$row;
    $InvoiceID=$row["invoiceid"];
        

   if($InvoiceID!="")
    {
      $sql="   select ROW_NUMBER() OVER (
      ORDER BY [InvoiceID]
   ) row_num,i.ID as itemid,i.name as itemname,i.barcode as barcode,cast(d.unitprice as numeric) as unitprice,qty,amount,cast(d.vatpercent as numeric) as vatpercent,cast(VATAmount as numeric) as vatamount,TaxAmount,cast(discountpercent as numeric) as discountpercent,cast(discountamount as numeric) as discountamount,'' as partnumber ,d.Attr1 as specification,(select ide.Name from inventory as ide where  ide.ID=i.under) as category,d.Attr2 as partnumber,netamount,
   ReferenceInvoiceID,ReferenceTransactionID,d.[guid] as guid,ParentGUid as referenceguid,
   d.Narration as detailnarration,transactionid,d.StockQty as StockQty,d.itemjson as itemjson,
    i.keyfield as itemkeyfield,i.AdditionalInfo2 as code,Make as brand,Rating as color,Model as size  from invoiceDetail as d left outer join inventory as i on i.ID=d.ItemID
   
   where InvoiceID=".$InvoiceID;
         $result2=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    //'.$row["CategoryID"].'
    //$limit=425;//
    //echo  odbc_num_rows($result);
            while ($row2= odbc_fetch_array($result2)) {
        $data['detailitems'][]=$row2;
                            }//end while
    }//end if invoice id 
        }//end while master
        echo json_encode($data); 

        }// code end here
   
  
  }
  if($_GET['action']=="PurchaseInvoice")
  {

$data=array();

    if(isset($_GET['offset']) && $_GET['offset']>0)
    {
      $offset=$_GET['offset'];
    }
    else
    {
      $offset=0;
    }
    if(isset($_GET['limit']) && $_GET['limit']>1)
    {
      $limit=$_GET['limit'];
    }
    else
    {
      $limit=1;
    }
    if(isset($_GET['page']) && $_GET['page']>1)
    {
      $page=$_GET['page'];
    }
    else
    {
      $page=1;
    }
       if((isset($_GET['startdate']) &&  $_GET['startdate']!="undefined" && $_GET['startdate']!="")  && (isset($_GET['enddate']) && $_GET['enddate']!="undefined" && $_GET['enddate']!=""))
            {
                $start=$_GET['startdate'];
                $end=$_GET['enddate'];
                $date="InvoiceDate Between '$start' and '$end' and ";
            }
            else
            {
                $date="";
            }

            if(isset($_GET['supplier']) && $_GET['supplier']!="" && $_GET['supplier']!="undefined")
            {
                $supplier=$_GET['supplier'];
                $supplier="supplierid=$supplier and ";
            }
            else
            {
                $supplier="";
            }
if(isset($_GET['InvoiceID']) && $_GET['InvoiceID']!="" && $_GET['InvoiceID']!="undefined")
            {
                $InvoiceID=$_GET['InvoiceID'];
                $InvoiceID="i.InvoiceID=$InvoiceID and ";
            }
            else
            {
              $InvoiceID="";
            }
            if(isset($_GET['companyid']) && $_GET['companyid']!="" && $_GET['companyid']!="undefined")
            {
                $companyid=$_GET['companyid'];
                $companyid="i.GodownID=$companyid and ";
            }
            else
            {
                $companyid="";
            }
        if(isset($_GET['company']) && $_GET['company']!="" && $_GET['company']!="undefined")
            {
                $companyid=$_GET['company'];
                $companyid="i.GodownID=$companyid and ";
            }
            else
            {
                $companyid="";
            }
            
            $searchbox="".$date."".$supplier."".$companyid."".$InvoiceID;


        $InvoiceType=$_GET['InvoiceType'];
        if(isset($_GET['InvoiceType']))
        {
            // for pagination 
  $sqlcount="select count(*) as total from InvoiceMaster as i left outer join godowns  as g on i.Shipperid=g.GodownID
   inner join godowns as c on c.GodownID=i.GodownID where $searchbox InvoiceType=".$InvoiceType."";
   $resultcount=odbc_exec($connection,$sqlcount);
   if(odbc_num_rows($resultcount)>0)
   {
    $rowcount=odbc_fetch_array($resultcount);
    $totalrecord=$rowcount['total'];
   }
    $fetch="OFFSET $offset ROWS 
FETCH NEXT $limit ROWS ONLY";
  if($searchbox=="") $top1=" top 1";else $top1="";
           // $InvoiceID=$_GET['InvoiceID'];
      $sql="  select ROW_NUMBER() OVER (
      ORDER BY [InvoiceID]
   ) row_num,c.Name as CompanyShortForm,invoicedate,i.KeyField as keyfield,'' as CreateByUserName,invoiceid,deliverydate,narration,g.Name as Department,g.godownid as supplier,c.godownid as godownid,InvoiceStatus,'' as col_Action ,billno,totalamount,discountamount,totaltaxamount as taxamount,netamount,shippingmethod,ispriceinclusivetax,ispriceperunit,vatoption,ReferenceInvoiceID,(select Keyfield from InvoiceMaster as im where im.InvoiceID=i.ReferenceInvoiceID) as ReferenceInvoiceNo,PurchaseLedgerID,VATLedgerID, acc.COAName  as SupplierName,acc.Attr6 as SupplierAddress,acc.Attr8 as SupplierContact,acc.Attr11 as SupplierEmail,acc.Attr15 as SupplierTRN, i.SupplierID as supplierid
   from InvoiceMaster as i left outer join godowns  as g on i.Shipperid=g.GodownID
   inner join godowns as c on c.GodownID=i.GodownID
   left outer join accounts as acc on acc.COAID=i.SupplierID
   where $searchbox InvoiceType=".$InvoiceType." Order by i.Invoiceid";

   $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    //'.$row["CategoryID"].'
    //$limit=425;//
    //echo  odbc_num_rows($result);

   //pagination code here 
    if($totalrecord>$offset)
  $currentpage=$offset+1;
else
  $currentpage="";

  if($totalrecord>$currentpage)
  $nextpage=$currentpage+1;
  else
  $nextpage="";
  if($currentpage>1)
  $preivouspage=$currentpage-1;
  else
  $preivouspage="";

  $lastpage=$totalrecord;

  $firstpage=1;
    $data['nav']=array('CurrentPage'=>$currentpage,'NextPage'=>$nextpage,'FirstPage'=>$firstpage,'LastPage'=>$lastpage,'PreviousPage'=>$preivouspage);


  while ($row = odbc_fetch_array($result)) {
        $row['col_Action']='<i class="fa fa-pencil fa-lg" style="text-align: left;color: blue;cursor:pointer;" onclick="javascript:editMaster(this,\''.$row["invoiceid"].'\');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteMaster(this,\''.$row["invoiceid"].'\');"></i>&nbsp;<i class="fa fa-print fa-lg" style="text-align: left;color: green;cursor:pointer;" onclick="javascript:printMaster(this,'.$row["godownid"].',
        '.$row["invoiceid"].'\');"></i>';
       // $row['ItemName']=htmlspecialchars($row['ItemName']);
      //  $row['ProductCategoryName']=getnamesingle("inventory","Name","Under",$row['ProductCategoryName'],$connection);
      // $row['CompanyShortForm']=getnamesingle("Godowns","Keyfield","GodownID",$row['CompanyShortForm'],$connection);
       // $row['Code']=htmlentities($row['Code']);
    $data ['allinvoice'][]=$row;
    $InvoiceID=$row["invoiceid"];
        }//end while

   if($InvoiceID!="")
    {
      $sql="   select ROW_NUMBER() OVER (
      ORDER BY [InvoiceID]
   ) row_num,i.ID as itemid,i.name as itemname,i.barcode as barcode,cast(d.unitprice as numeric) as unitprice,qty,amount,convert(numeric,d.TaxPercent) as vatpercent,convert(numeric,TaxAmount) as vatamount,convert(numeric,discountpercent) as discountpercent,convert(numeric,discountamount) as discountamount,'' as partnumber ,d.Attr1 as specification,(select ide.Name from inventory as ide where  ide.ID=i.under) as category,d.Attr2 as partnumber,netamount,
   ReferenceInvoiceID,ReferenceTransactionID,d.[guid] as guid,ParentGUid as referenceguid,
   d.Narration as detailnarration,transactionid,d.StockQty as StockQty,d.itemjson as itemjson,
    i.keyfield as itemkeyfield,i.AdditionalInfo2 as code,Make as brand,Rating as color,Model as size  from invoiceDetail as d left outer join inventory as i on i.ID=d.ItemID
   
   where InvoiceID=".$InvoiceID;
         $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    //'.$row["CategoryID"].'
    //$limit=425;//
    //echo  odbc_num_rows($result);
            while ($row = odbc_fetch_array($result)) {
        $data['detailitems'][]=$row;
                            }//end while
    }//end if invoice id 
        echo json_encode($data); 

        }// code end here
 /*       else
        {

            if((isset($_GET['startdate']) &&  $_GET['startdate']!="undefined" && $_GET['startdate']!="")  && (isset($_GET['enddate']) && $_GET['enddate']!="undefined" && $_GET['enddate']!=""))
            {
                $start=$_GET['startdate'];
                $end=$_GET['enddate'];
                $date="InvoiceDate Between '$start' and '$end' and ";
            }
            else
            {
                $date="";
            }

            if(isset($_GET['supplier']) && $_GET['supplier']!="")
            {
                $supplier=$_GET['supplier'];
                $supplier="supplierid=$supplier and ";
            }
            else
            {
                $supplier="";
            }

            if(isset($_GET['companyid']) && $_GET['companyid']!="")
            {
                $companyid=$_GET['companyid'];
                $companyid="i.GodownID=$companyid and ";
            }
            else
            {
                $companyid="";
            }
            
            $searchbox="".$date."".$supplier."".$companyid;




       $sql="  select ROW_NUMBER() OVER (
      PARTITION BY [InvoiceType]
      ORDER BY [InvoiceType]
   ) row_num,c.Name as CompanyShortForm,InvoiceDate,i.KeyField as KeyField,'' as CreateByUserName,InvoiceID,DeliveryDate,Narration,g.Name as Department,InvoiceID,InvoiceStatus,'' as col_Action,InvoiceDate ,c.godownid as godownid,totalamount,discountamount,totaltaxamount as taxamount,netamount,InvoiceStatus
   from InvoiceMaster as i left outer join godowns  as g on i.Shipperid=g.GodownID
   inner join godowns as c on c.GodownID=i.GodownID
   where $searchbox InvoiceType=".$InvoiceType." $fetch ";
        
   $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    //'.$row["CategoryID"].'
    //$limit=425;//
    //echo  odbc_num_rows($result);
  while ($row = odbc_fetch_array($result)) {
       // $row['ItemName']=htmlspecialchars($row['ItemName']);
      //  $row['ProductCategoryName']=getnamesingle("inventory","Name","Under",$row['ProductCategoryName'],$connection);
      // $row['CompanyShortForm']=getnamesingle("Godowns","Keyfield","GodownID",$row['CompanyShortForm'],$connection);
       // $row['Code']=htmlentities($row['Code']);
    foreach($row as $key=>$value)
    $row[$key]=utf8_encode($value);
      
    $data[]=$row;
        }//end while
        
        echo json_encode($data); 
        }//end else 
*/
        // code ofr details
        //print_r($data);
//  code for item search 
        
    
  }//end purchase Invoice 

    if($_GET['action']=="MRQStatus")
    {
      $data=array();
      $InvoiceType=1;
        //    $InvoiceID=$_GET['InvoiceID'];
                  if((isset($_GET['startdate']) &&  $_GET['startdate']!="undefined" && $_GET['startdate']!="")  && (isset($_GET['enddate']) && $_GET['enddate']!="undefined" && $_GET['enddate']!=""))
            {
                $start=$_GET['startdate'];
                $end=$_GET['enddate'];
                $date="InvoiceDate Between '$start' and '$end' and ";
            }
            else
            {
                $date="";
            }

            if(isset($_GET['supplier']) && $_GET['supplier']!="")
            {
                $supplier=$_GET['supplier'];
                $supplier="supplierid=$supplier and ";
            }
            else
            {
                $supplier="";
            }

            if(isset($_GET['companyid']) && $_GET['companyid']!="")
            {
                $companyid=$_GET['companyid'];
                $companyid="i.GodownID=$companyid and ";
            }
            else
            {
                $companyid="";
            }
            
            $searchbox="".$date."".$supplier."".$companyid;

      $sql=" select ROW_NUMBER() OVER (
      PARTITION BY [InvoiceType]
      ORDER BY [InvoiceType]
   ) row_num,c.AdditionalInfo1 as CompanyShortForm,invoicedate,i.KeyField as KeyField,'' as CreateByUserName,
   i.invoiceid as invoiceid,DeliveryDate,i.narration as narration,g.Name as Department,g.godownid as supplier
   ,c.godownid as godownid,InvoiceStatus,(Case when InvoiceStatus=1 then 'Pending' when InvoiceStatus=2 then 'Accepted' when InvoiceStatus=3 then 'Rejected' when InvoiceStatus=4 then 'Closed'  else '' end) as OrderStatus,'' as col_Action ,billno,totalamount,
   totaltaxamount as taxamount,i.netamount,shippingmethod,ispriceinclusivetax,ispriceperunit,vatoption,d.ReferenceInvoiceID as ReferenceInvoiceID ,
   PurchaseLedgerID,VATLedgerID,i.SupplierID as supplierid,
   inv.ID as itemid,inv.name as itemname,inv.barcode as barcode,cast(d.unitprice as numeric) as unitprice,qty,amount,d.ReceivedQty as ReceivedQty,
   d.Attr1 as specification,d.Attr2 as partnumber
   ,ReferenceTransactionID,d.[guid] as guid,ParentGUid as referenceguid,
   d.Narration as detailnarration,transactionid,d.StockQty as StockQty,d.itemjson as itemjson,
    i.keyfield as itemkeyfield,inv.AdditionalInfo2 as code,Make as brand,Rating as color,Model as size
   from InvoiceMaster as i left outer join godowns  as g on i.Shipperid=g.GodownID
   inner join godowns as c on c.GodownID=i.GodownID
   inner join invoiceDetail as d on i.invoiceid=d.invoiceid 
   inner join inventory as inv on inv.ID=d.ItemID        
   where $searchbox InvoiceType=".$InvoiceType." order by i.keyfield";

   $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    //'.$row["CategoryID"].'
    //$limit=425;//
    //echo  odbc_num_rows($result);
  while ($row = odbc_fetch_array($result)) {
      /*  $row['col_Action']='<i class="fa fa-pencil fa-lg" style="text-align: left;color: blue;cursor:pointer;" onclick="javascript:editMaster(this,\''.$row["invoiceid"].'\');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteMaster(this,\''.$row["invoiceid"].'\');"></i>&nbsp;<i class="fa fa-print fa-lg" style="text-align: left;color: green;cursor:pointer;" onclick="javascript:printMaster(this,'.$row["godownid"].',
        '.$row["invoiceid"].'\');"></i>';*/
       // $row['ItemName']=htmlspecialchars($row['ItemName']);
      //  $row['ProductCategoryName']=getnamesingle("inventory","Name","Under",$row['ProductCategoryName'],$connection);
      // $row['CompanyShortForm']=getnamesingle("Godowns","Keyfield","GodownID",$row['CompanyShortForm'],$connection);
       // $row['Code']=htmlentities($row['Code']);
        foreach ($row as $key => $value) {
          # code...
          $row[$key]=utf8_encode($value);
        }
    $data []=$row;
          }//end while
/*
   if($InvoiceID!="")
    {
      $sql="   select i.ID as itemid,i.name as itemname,i.barcode as barcode,cast(d.unitprice as numeric) as unitprice,qty,amount,cast(d.vatpercent as numeric) as vatpercent,cast(VATAmount as numeric) as vatamount,cast(discountpercent as numeric) as discountpercent,cast(discountamount as numeric) as discountamount,'' as partnumber ,d.Attr1 as specification,(select ide.Name from inventory as ide where  ide.ID=i.under) as category,d.Attr2 as partnumber,netamount,
   ReferenceInvoiceID,ReferenceTransactionID,d.[guid] as guid,ParentGUid as referenceguid,
   d.Narration as detailnarration,transactionid,d.StockQty as StockQty,d.itemjson as itemjson,
    i.keyfield as itemkeyfield,i.AdditionalInfo2 as code,Make as brand,Rating as color,Model as size  from invoiceDetail as d left outer join inventory as i on i.ID=d.ItemID
   
   where InvoiceID=".$InvoiceID;
         $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    //'.$row["CategoryID"].'
    //$limit=425;//
    //echo  odbc_num_rows($result);
            while ($row = odbc_fetch_array($result)) {
        $data['detailitems'][]=$row;
                            }//end while
    }//end if invoice id */
        echo json_encode($data); 

        
    }
    if($_GET['action']=="MRQLIST")
    {
$data=array();
        $InvoiceType=$_GET['InvoiceType'];
        if(isset($_GET['InvoiceID']))
        {
            $InvoiceID=$_GET['InvoiceID'];
      $sql="  select ROW_NUMBER() OVER (
      PARTITION BY [InvoiceType]
      ORDER BY [InvoiceType]
   ) row_num,c.Name as CompanyShortForm,invoicedate,i.KeyField as keyfield,'' as CreateByUserName,invoiceid,deliverydate,narration,g.Name as Department,g.godownid as supplier,c.godownid as godownid,InvoiceStatus,'' as col_Action ,billno,totalamount,discountamount,totaltaxamount as taxamount,netamount,shippingmethod,ispriceinclusivetax,ispriceperunit,vatoption,ReferenceInvoiceID,(select Keyfield from InvoiceMaster as im where im.InvoiceID=i.ReferenceInvoiceID) as ReferenceInvoiceNo,PurchaseLedgerID,VATLedgerID,i.SupplierID as supplierid
   from InvoiceMaster as i left outer join godowns  as g on i.Shipperid=g.GodownID
   inner join godowns as c on c.GodownID=i.GodownID
   where InvoiceType=".$InvoiceType." and InvoiceID=$InvoiceID";

   $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    //'.$row["CategoryID"].'
    //$limit=425;//
    //echo  odbc_num_rows($result);
  while ($row = odbc_fetch_array($result)) {
        $row['col_Action']='<i class="fa fa-pencil fa-lg" style="text-align: left;color: blue;cursor:pointer;" onclick="javascript:editMaster(this,\''.$row["invoiceid"].'\');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteMaster(this,\''.$row["invoiceid"].'\');"></i>&nbsp;<i class="fa fa-print fa-lg" style="text-align: left;color: green;cursor:pointer;" onclick="javascript:printMaster(this,'.$row["godownid"].',
        '.$row["invoiceid"].'\');"></i>';
       // $row['ItemName']=htmlspecialchars($row['ItemName']);
      //  $row['ProductCategoryName']=getnamesingle("inventory","Name","Under",$row['ProductCategoryName'],$connection);
      // $row['CompanyShortForm']=getnamesingle("Godowns","Keyfield","GodownID",$row['CompanyShortForm'],$connection);
       // $row['Code']=htmlentities($row['Code']);
    $data ['allinvoice'][]=$row;
        }//end while

   if($InvoiceID!="")
    {
      $sql="   select i.ID as itemid,i.name as itemname,i.barcode as barcode,cast(d.unitprice as numeric) as unitprice,qty,amount,cast(d.vatpercent as numeric) as vatpercent,cast(VATAmount as numeric) as vatamount,cast(discountpercent as numeric) as discountpercent,cast(discountamount as numeric) as discountamount,'' as partnumber ,d.Attr1 as specification,(select ide.Name from inventory as ide where  ide.ID=i.under) as category,d.Attr2 as partnumber,netamount,
   ReferenceInvoiceID,ReferenceTransactionID,d.[guid] as guid,ParentGUid as referenceguid,
   d.Narration as detailnarration,transactionid,d.StockQty as StockQty,d.itemjson as itemjson,
    i.keyfield as itemkeyfield,i.AdditionalInfo2 as code,Make as brand,Rating as color,Model as size  from invoiceDetail as d left outer join inventory as i on i.ID=d.ItemID
   
   where InvoiceID=".$InvoiceID;
         $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    //'.$row["CategoryID"].'
    //$limit=425;//
    //echo  odbc_num_rows($result);
            while ($row = odbc_fetch_array($result)) {
        $data['detailitems'][]=$row;
                            }//end while
    }//end if invoice id 
        echo json_encode($data); 

        }
        else
        {

            if((isset($_GET['startdate']) &&  $_GET['startdate']!="undefined" && $_GET['startdate']!="")  && (isset($_GET['enddate']) && $_GET['enddate']!="undefined" && $_GET['enddate']!=""))
            {
                $start=$_GET['startdate'];
                $end=$_GET['enddate'];
                $date="InvoiceDate Between '$start' and '$end' and ";
            }
            else
            {
                $date="";
            }

            if(isset($_GET['supplier']) && $_GET['supplier']!="" && $_GET['supplier']!="undefined")
            {
                $supplier=$_GET['supplier'];
                $supplier="supplierid=$supplier and ";
            }
            else
            {
                $supplier="";
            }

            if(isset($_GET['companyid']) && $_GET['companyid']!="" && $_GET['companyid']!="undefined")
            {
                $companyid=$_GET['companyid'];
                $companyid="i.GodownID=$companyid and ";
            }
            else
            {
                $companyid="";
            }
            
            $searchbox="".$date."".$supplier."".$companyid;
       $sql="  select ROW_NUMBER() OVER (
      PARTITION BY [InvoiceType]
      ORDER BY [InvoiceType]
   ) row_num,c.Name as CompanyShortForm,InvoiceDate,i.KeyField as KeyField,'' as CreateByUserName,InvoiceID,DeliveryDate,Narration,g.Name as Department,InvoiceID,InvoiceStatus,'' as col_Action,InvoiceDate ,c.godownid as godownid,totalamount,discountamount,totaltaxamount as taxamount,netamount,InvoiceStatus
   from InvoiceMaster as i left outer join godowns  as g on i.Shipperid=g.GodownID
   inner join godowns as c on c.GodownID=i.GodownID
   where $searchbox InvoiceType=".$InvoiceType;
        
   $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    //'.$row["CategoryID"].'
    //$limit=425;//
    //echo  odbc_num_rows($result);
  while ($row = odbc_fetch_array($result)) {
        $row['col_Action']='<i class="fa fa-pencil fa-lg" style="text-align: left;color: blue;cursor:pointer;" onclick="javascript:editMaster(this,\''.$row["InvoiceID"].'\');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteMaster(this,\''.$row["InvoiceID"].'\');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-print fa-lg" style="text-align: right;color: green;cursor:pointer;" onclick="javascript:printMaster(this,'.$row["godownid"].',\''.$row["InvoiceID"].'\');"></i>';
       // $row['ItemName']=htmlspecialchars($row['ItemName']);
      //  $row['ProductCategoryName']=getnamesingle("inventory","Name","Under",$row['ProductCategoryName'],$connection);
      // $row['CompanyShortForm']=getnamesingle("Godowns","Keyfield","GodownID",$row['CompanyShortForm'],$connection);
       // $row['Code']=htmlentities($row['Code']);
    $data[]=$row;
        }//end while
        
        echo json_encode($data); 
        }//end else 

        // code ofr details
        //print_r($data);
//  code for item search 
        
    }
}


?>