<?php session_start();
define('SITEURL','http://localhost/design2/');
 try {
    $hostname = "local";
    $port = 10060;
    $dbname = "al_qalam_live";
    $username = "sa";
    $pw = "hamabd";
    //Driver={SQL Server};Server=$hostname;Database=$dbname;
  // $connection = odbc_connect("al_qalam", $username, $pw);
   $connection = odbc_connect("www", $username, $pw);

  } catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "\n";
    exit;
  }
 // $stmt = odbc_prepare($connection,'select top 5 * from inventory');
  /*$result=odbc_exec($connection,'select top 5 * from inventory');
  while ($row = odbc_fetch_array($result)) {
    print_r($row);
  }*/
  //unset($dbh); unset($stmt);

  function getMaxId($table,$field,$connection)
  {
    $data="";
    $sql="select max($field) as id from $table";
    $result=odbc_exec($connection, $sql);
    while($row=odbc_fetch_array($result))
    {
      $data=$row['id'];
    }
    return $data;
  }

  function getnamewhere($table,$fields,$where="1=1",$connection)
  {
 $data=array();
    $sql="select $fields from $table where $where";
    $result=odbc_exec($connection, $sql);
    while($row=odbc_fetch_array($result))
    {
      $data=$row;
    }
    return json_encode($data);
  }
  function getnamesingle($table,$field,$exp=1,$value=1,$connection)
  {
    $data="";
    if($value!="")
    {
  $sql="select $field from $table where $exp=$value";
    $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    while($row=odbc_fetch_array($result))
    {
      $data=utf8_encode($row[$field]);
    }
    }
return $data;
  }
function getSum($table,$field,$exp=1,$value=1,$connection)
  {
    $data="";
    if($value!="")
    {
  $sql="select $field from $table where $exp=$value";
    $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    while($row=odbc_fetch_array($result))
    {
      $data=utf8_encode($row['sum']);
    }
    }
return $data;
  }


  function generateCode($table,$field,$where,$connection)
  {
    $data="";//array();//"";
    if($where!="")
    {
    $sql="select @@ROWCOUNT as total from $table where $where";
    $result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
    $row=odbc_num_rows($result);
   // print_r($row);
      $data=$row;
    
    }
return $data;
  }
  function generateGLCode($under,$NatureID,$connection)
{
    $total=1;
    $sql="select count(COAID) as total from accounts where under='$under' and NatureID='$NatureID'";
    $result=odbc_exec($connection,$sql);
    while($row=odbc_fetch_array($result))
    {
            $total=$row['total'];
            $total=$total+1;
    }
return $total;
}
function getDataType2($conn,$table,$field)
{
  $data=array();
   $sql="select data_type from information_schema.columns where table_name='$table' and column_name='$field'";
$result=odbc_exec($conn,$sql);
if(odbc_num_rows($result)>0)
{
  $data=odbc_fetch_array($result);

//print_r($data);
if($data['data_type']=="varchar" || $data['data_type']=="nvarchar" || $data['data_type']=="time" || $data['data_type']=="datetime" || $data['data_type']=="date" || $data['data_type']=="smalldatetime" || $data['data_type']=="time")
{
    return true;
} 
else 
  {
    return false;
  }
}
else
{
  return false;
} //echo json_encode($data);// print_r($data); 

}

function insertInvoiceDetail($InvoiceID,$mrdetail,$InvoiceType,$connection)
{
                $sql="delete from invoiceDetail where InvoiceID=".$InvoiceID;
            odbc_exec($connection,$sql);
       //     print_r($_REQUEST);
            for($a=0;$a<count($mrdetail);$a++)
            {
            $Flag =isset($mrdetail[$a]->Flag)?$mrdetail[$a]->Flag:'';
            if($Flag!="D")
            {
//
            $ItemID =isset($mrdetail[$a]->Item_ItemID)?$mrdetail[$a]->Item_ItemID:'';
            $JobCardItem=isset($mrdetail[$a]->JobCardItem)?$mrdetail[$a]->JobCardItem:'';
            if($JobCardItem!="")$ItemID=getnamesingle("Inventory","ID","Name","'".$JobCardItem."'",$connection);
            
            $ItemName =isset($mrdetail[$a]->Item_ItemName)?$mrdetail[$a]->Item_ItemName:'';
            $Color =isset($mrdetail[$a]->Item_Color)?$mrdetail[$a]->Item_Color:'';
            $Size =isset($mrdetail[$a]->Item_Size)?$mrdetail[$a]->Item_Size:'';
            $Barcode =isset($mrdetail[$a]->Item_Barcode)?$mrdetail[$a]->Item_Barcode:'';
            $Category =isset($mrdetail[$a]->Item_Category)?$mrdetail[$a]->Item_Category:'';
            $Brand =isset($mrdetail[$a]->Item_Brand)?$mrdetail[$a]->Item_Brand:'';
            $Item_KeyField =isset($mrdetail[$a]->Item_KeyField)?$mrdetail[$a]->Item_KeyField:'';
            
            $UnitPrice =(isset($mrdetail[$a]->Rate) && $mrdetail[$a]->Rate!="")?$mrdetail[$a]->Rate:0;
            $StockQty =isset($mrdetail[$a]->StockQty)?$mrdetail[$a]->StockQty:0;
            $ItemTable =isset($mrdetail[$a]->ItemTable)?$mrdetail[$a]->ItemTable:'';
            $Qty =isset($mrdetail[$a]->Qty)?$mrdetail[$a]->Qty:0;
            //}
   $ReceivedQty=(isset($mrdetail[$a]->ReceivedQty) && $mrdetail[$a]->ReceivedQty!="")?(double)$mrdetail[$a]->ReceivedQty:0;
    $Amount =(isset($mrdetail[$a]->Amount) && $mrdetail[$a]->Amount>0)?$mrdetail[$a]->Amount:($ReceivedQty*$UnitPrice);
            $Discount =(isset($mrdetail[$a]->Discount) && $mrdetail[$a]->Discount>=0)?(double)$mrdetail[$a]->Discount:0;
            $TaxPercent =(isset($mrdetail[$a]->VAT) && $mrdetail[$a]->VAT>0)?(double)$mrdetail[$a]->VAT:0;
            $NetAmount =(isset($mrdetail[$a]->NetAmount) && $mrdetail[$a]->NetAmount>0)?$mrdetail[$a]->NetAmount:0;
            $TaxAmount =(isset($mrdetail[$a]->VATAmount) && $mrdetail[$a]->VATAmount>0)?$mrdetail[$a]->VATAmount:0;
            $DiscountAmount =(isset($mrdetail[$a]->DiscountAmount) && $mrdetail[$a]->DiscountAmount>0)?$mrdetail[$a]->DiscountAmount:0;
            $Attr2 =isset($mrdetail[$a]->PartNumber)?$mrdetail[$a]->PartNumber:'';
            $Attr1 =isset($mrdetail[$a]->Specification)?$mrdetail[$a]->Specification:'';
            $Narration =isset($mrdetail[$a]->Narration)?$mrdetail[$a]->Narration:'';
            $ReferenceTransactionID =isset($mrdetail[$a]->ReferenceTransactionID)?$mrdetail[$a]->ReferenceTransactionID:'';
            $JobCardItem =isset($mrdetail[$a]->JobCardItem)?$mrdetail[$a]->JobCardItem:'';
            $TransactionID =isset($mrdetail[$a]->TransactionID)?$mrdetail[$a]->TransactionID:'';
         
            $UnitID=1;
            $TotalNetAmount=0;
              if($InvoiceType==8)
              {
/*                [{"Company_ID":"2","Company_Value":"","BaseType_ID":"SERVICE","BaseType_Value":"SERVICE","MainCategory_ID":"41","MainCategory_Value":"41","SubCategory_ID":"Mechanical works for Japanese","SubCategory_Value":"Mechanical works for Japanese","Brand_ID":"ACTIVE","Brand_Value":"ACTIVE","Level_ID":"2.00","Level_Value":"2.00","Item_ItemID":"296","Item_ItemName":"Mechanical works for Japanese","Item_Barcode":"I-0000181","Qty":"3.0000","Rate":"200.0000","Amount":"600.0000","Discount":".0000","VAT":"5.00","VATAmount":"30.00","NetAmount":"630.0000","TransactionID":"7385","Flag":"R","SNo":"2","Narration":"hdud ouo yioy  "},{"Company_ID":"2","Company_Value":"","BaseType_ID":"SERVICE","BaseType_Value":"SERVICE","MainCategory_ID":"30","MainCategory_Value":"30","SubCategory_ID":"Engine oil change ( with out  Filter )","SubCategory_Value":"Engine oil change ( with out  Filter )","Brand_ID":"ACTIVE","Brand_Value":"ACTIVE","Level_ID":"1.00","Level_Value":"1.00","Item_ItemID":"31","Item_ItemName":"Engine oil change ( with out  Filter )","Item_Barcode":"I-0000003","Qty":"1.0000","Rate":"20.0000","Amount":"20.0000","Discount":".0000","VAT":"5.00","VATAmount":"1.00","NetAmount":"21.0000","TransactionID":"7377","Flag":"R","SNo":"1","Narration":"test test"}]*/
      /*  $ItemID=isset($mrdetail[$a]->ItemID)?$mrdetail[$a]->ItemID:0;
        $ItemName =isset($mrdetail[$a]->ItemName)?$mrdetail[$a]->ItemName:'';
            $Color =isset($mrdetail[$a]->Color)?$mrdetail[$a]->Color:'';
            $Size =isset($mrdetail[$a]->Size)?$mrdetail[$a]->Size:'';
            $Barcode =isset($mrdetail[$a]->Barcode)?$mrdetail[$a]->Barcode:'';
            $Category =isset($mrdetail[$a]->Category)?$mrdetail[$a]->Category:'';
            $Brand =isset($mrdetail[$a]->Brand)?$mrdetail[$a]->Brand:'';
            $Item_KeyField =isset($mrdetail[$a]->KeyField)?$mrdetail[$a]->KeyField:'';*/
                $Category =isset($mrdetail[$a]->MainCategory_ID)?$mrdetail[$a]->MainCategory_ID:0;
                $Level_ID =isset($mrdetail[$a]->Level_ID)?$mrdetail[$a]->Level_ID:0;
                $VATPercent= (isset($mrdetail[$a]->VAT) && $mrdetail[$a]->VAT>0)?(double)$mrdetail[$a]->VAT:0;
                $VATAmount =(isset($mrdetail[$a]->VATAmount) && $mrdetail[$a]->VATAmount>0)?$mrdetail[$a]->VATAmount:0;
              }
            else
            {
                 $VATPercent=$TaxPercent;
            $VATAmount=$TaxAmount;
            }
              
              $TransactionID=getmaxId("invoiceDetail","TransactionID",$connection)+1;
                 $sql="insert into invoicedetail (
InvoiceID
           ,ItemID
           ,UnitPrice
           ,QTY
           ,Amount
           ,UnitID
           ,NetAmount
           ,TaxPercent
           ,TaxAmount
           ,Narration
           ,TotalNetAmount
           ,Attr1
           ,Attr2
           ,VATPercent
           ,VATAmount
           ,ReceivedQty
           ,StockQty
           ,isDelete
           ,DiscountAmount
           ,DiscountPercent
        ) values("
       .$InvoiceID."
           ,".$ItemID."
           ,".$UnitPrice."
           ,".$Qty."
           ,".$Amount."
           ,".$UnitID."
           ,".$NetAmount."
           ,".$TaxPercent."
           ,".$TaxAmount."
           ,'".$Narration."'
           ,".$TotalNetAmount."
           ,'".$Attr1."'
           ,'".$Attr2."'
           ,".$VATPercent."
           ,".$VATAmount."
           ,".$ReceivedQty.
          ",".$StockQty.",0,".$DiscountAmount.",".$Discount.")";
          odbc_exec($connection, $sql) or die(odbc_error().$sql);
          }//end if flag
          }//end for loop

}

function InsertJList($InvoiceID,$listdata,$TextBoxName,$connection)
{
  //delete previsou record 
  $sql="delete from InvoiceMaster_JobDetail where InvoiceID=".$InvoiceID." and TextBoxName='".$TextBoxName."'";
  odbc_exec($connection, $sql);
$data=json_decode($listdata);
//print_r($data);

  for($f=0;$f<count($data);$f++)
  {
    $MasterValueID=isset($data[$f]->MasterValueID)?$data[$f]->MasterValueID:0;
    $Description=isset($data[$f]->Value)?$data[$f]->Value:0;
    $Particular=isset($data[$f]->Particular)?$data[$f]->Particular:'';
    if($Particular=='')
    $Particular=isset($data[$f]->MasterValue)?$data[$f]->MasterValue:'';
  /*  echo $Particular;
    print_r($_SESSION);
 */
    if(isset($_SESSION[$Particular]))
    {
    $pl=$_SESSION[$Particular];
    $PictureLocation=$pl[$Particular];
    }
    else
    {
      $PictureLocation='';  
    } 
/*    if($PictureLocation!="")
    {*/
      $sql ="insert into InvoiceMaster_JobDetail (InvoiceID,MasterValueID,Description,TextBoxName,Particulars,PictureLocation) values(".$InvoiceID.",".$MasterValueID.",'".$Description."','".$TextBoxName."','".$Particular."','".$PictureLocation."')";
     /* }
     else
     {
      $sql ="insert into InvoiceMaster_JobDetail (InvoiceID,MasterValueID,Description,TextBoxName,Particulars) values(".$InvoiceID.",".$MasterValueID.",'".$Description."','".$TextBoxName."','".$Particular."')";
     }*/ 
     odbc_exec($connection, $sql) or die(odbc_error().$sql);

  }
  //echo $sql."<Br>";
}//end insertJLIST 

function InsertVoucherMasterJob($fields,$InvoiceType,$InvoiceID,$connection)
{


            $NetAmount=$fields['TotalAmount'];
            /*$Posted=1;
            $IsPostDated=1;
      $LedgerID=0;
            $AmountDebit=0;
            $AmountCredit=0;
            $Active=1;
            $PeriodID=1;
            $IsAuto=1;
            $Cash=0;
            $ContractID=0;
            $VoucherDateTime=date("Y-m-d m:i:s");
            $PaymentType="Credit";
            $ReferenceInvoiceID=$InvoiceID;
            $ReferenceVoucherID=0;
            $VoucherPostDateTime=0;
            $VoucherToBePosted=1;
            $MultiVoucher=0;
            $UserID=0;
            $GroupID=1;
            $LocationID=$GodownID;
            $VoucherTypeID=71;
            $VoucherDate=$InvoiceDate;
            *///$TotalAmount=$TotalAmount
            //insert into voucher 
              $sql="INSERT INTO [dbo].[VoucherMaster]
           ([VoucherID]
           ,[VoucherNo]
           ,[TotalAmount]
           ,[Active]
           ,[VoucherDate]
           ,[Narration]
           ,[Posted]
           ,[IsPostDated]
           ,[VoucherTypeID]
           ,[PeriodID]
           ,[ReferenceInvoiceID]
           ,[LedgerID]
           ,[AmountDebit]
           ,[AmountCredit]
           ,[IsAuto]
           ,[LocationID]
           ,[ReferenceVoucherID]
           ,[GroupID]
           ,[MultiVoucher]
           ,[VoucherToBePosted]
           ,[Cash]
           ,[ContractID]
           ,[UserID]
           ,[VoucherDateTime]
           ,[PaymentType])
     VALUES(".$fields['VoucherID']."
           ,'".$fields['VoucherNo']."'
           ,".$fields['TotalAmount']."
           ,".$fields['Active']."
           ,'".$fields['VoucherDate']."'
           ,'".$fields['Narration']."'
           ,".$fields['Posted']."
           ,".$fields['IsPostDated']."
           ,".$fields['VoucherTypeID']."
           ,".$fields['PeriodID']."
           ,".$fields['ReferenceInvoiceID']."
           ,".$fields['SupplierID']."
           ,".$fields['AmountDebit']."
           ,".$fields['AmountCredit']."
           ,".$fields['IsAuto']."
           ,".$fields['LocationID']."
           ,".$fields['ReferenceVoucherID']."
           ,".$fields['GroupID']."
           ,".$fields['MultiVoucher']."
           ,".$fields['VoucherToBePosted']."
           ,".$fields['Cash']."
           ,".$fields['ContractID']."
           ,".$fields['UserID']."
           ,'".$fields['VoucherDateTime']."'
           ,'".$fields['PaymentType']."'
     )";
   
            
            odbc_exec($connection,$sql) or die(odbc_error().$sql);  
            

}
function InsertVoucherMaster($fields,$InvoiceType,$InvoiceID,$connection)
{

            $NetAmount=$fields['TotalAmount'];
            /*$Posted=1;
            $IsPostDated=1;
      $LedgerID=0;
            $AmountDebit=0;
            $AmountCredit=0;
            $Active=1;
            $PeriodID=1;
            $IsAuto=1;
            $Cash=0;
            $ContractID=0;
            $VoucherDateTime=date("Y-m-d m:i:s");
            $PaymentType="Credit";
            $ReferenceInvoiceID=$InvoiceID;
            $ReferenceVoucherID=0;
            $VoucherPostDateTime=0;
            $VoucherToBePosted=1;
            $MultiVoucher=0;
            $UserID=0;
            $GroupID=1;
            $LocationID=$GodownID;
            $VoucherTypeID=71;
            $VoucherDate=$InvoiceDate;
            *///$TotalAmount=$TotalAmount
            $sql="select * from VoucherMaster where ReferenceInvoiceID=".$InvoiceID;
            $result=odbc_exec($connection,$sql);
            $VoucherID=0;
            if(odbc_num_rows($result)>0)
            {
              $row=odbc_fetch_array($result);
              $VoucherID=$row['VoucherID'];
            $sql="update VoucherMaster set TotalAmount=".$NetAmount." where ReferenceInvoiceID=".$InvoiceID;
     //       odbc_exec($connection,$sql) or die(odbc_error().$sql);  
            }
            else
            {
            //insert into voucher 
            $sql="INSERT INTO [dbo].[VoucherMaster]
           ([VoucherID]
           ,[VoucherNo]
           ,[TotalAmount]
           ,[Active]
           ,[VoucherDate]
           ,[Narration]
           ,[Posted]
           ,[IsPostDated]
           ,[VoucherTypeID]
           ,[PeriodID]
           ,[ReferenceInvoiceID]
           ,[LedgerID]
           ,[AmountDebit]
           ,[AmountCredit]
           ,[IsAuto]
           ,[LocationID]
           ,[ReferenceVoucherID]
           ,[GroupID]
           ,[MultiVoucher]
           ,[VoucherToBePosted]
           ,[Cash]
           ,[ContractID]
           ,[UserID]
           ,[VoucherDateTime]
           ,[PaymentType])
     VALUES(".$fields['VoucherID']."
           ,'".$fields['VoucherNo']."'
           ,".$fields['TotalAmount']."
           ,".$fields['Active']."
           ,'".$fields['VoucherDate']."'
           ,'".$fields['Narration']."'
           ,".$fields['Posted']."
           ,".$fields['IsPostDated']."
           ,".$fields['VoucherTypeID']."
           ,".$fields['PeriodID']."
           ,".$fields['ReferenceInvoiceID']."
           ,".$fields['SupplierID']."
           ,".$fields['AmountDebit']."
           ,".$fields['AmountCredit']."
           ,".$fields['IsAuto']."
           ,".$fields['LocationID']."
           ,".$fields['ReferenceVoucherID']."
           ,".$fields['GroupID']."
           ,".$fields['MultiVoucher']."
           ,".$fields['VoucherToBePosted']."
           ,".$fields['Cash']."
           ,".$fields['ContractID']."
           ,".$fields['UserID']."
           ,'".$fields['VoucherDateTime']."'
           ,'".$fields['PaymentType']."'
     )";
            odbc_exec($connection,$sql) or die(odbc_error().$sql);  
            
   }
            
}
function InsertVoucherDetail($fields,$InvoiceType,$InvoiceID,$connection)
{
              $TransactionType="PURCHASE A/C";
            /*$Debit=$TotalAmount;
            $Credit=$TotalAmount;
            $CreditLedgerID=$SupplierID;*/
            $CostCenterID=1;
            $IsDelete=0;
            $DebitCostCenterID=1;
            $Narrationdetail="Narration details here";
          //  $LedgerID=$PurchaseLedgerID;
            $sqldetail="INSERT INTO [dbo].[VoucherDetails]
           ([VoucherID]
           ,[LedgerID]
           ,[Debit]
           ,[Credit]
           ,[Narration]
           ,[ReferenceInvoiceID]
           ,[CreditLedgerID]
           ,[CostCenterID]
           ,[IsDelete]
           ,[DebitCostCenterID]
           ,[TransactionType])
     VALUES(".$fields['VoucherID']
           .",".$fields['LedgerID']
           .",".$fields['Debit']
           .",".$fields['Credit']
           .",'".$fields['Narration']
           ."',".$fields['ReferenceInvoiceID']
           .",".$fields['CreditLedgerID']
           .",".$fields['CostCenterID']
           .",".$fields['IsDelete']
           .",".$fields['DebitCostCenterID']
           .",'".$fields['TransactionType']."')";
 odbc_exec($connection,$sqldetail) or die(odbc_error().$sqldetail); 

}
function userrights($Userid,$Sidebar,$permit,$connection)
{
     $sql="select $permit from Web_Privileges where Userid=$Userid and SidebarID=$Sidebar";
    $result=odbc_exec($connection,$sql);
    if(odbc_num_rows($result)>0)
    {
      $row=odbc_fetch_array($result);
      $ans=$row[$permit];
      
        return $ans;
    }
    else
    {
        return 0;
    }
}
function save($pic,$oldfile,$path)
{
$upload_dir =$path;            
$size_bytes = 9388608;

$extlimit = "yes";
$limitedext = array(".gif",".jpg",".GIF",".JPG",".png",".jpeg",".swf",".png",".Png",".PNG",".txt",".doc",".docx");
          if (!is_dir("$upload_dir")) { 
         die ("<center>The directory <b>($upload_dir)</b> doesn't exist"); 
          } 
          if (!is_writeable("$upload_dir")){ 
             die ("<center>The directory <b>($upload_dir)</b> is NOT writable, Please CHMOD (777)"); 
          }
                $file =  $oldfile; 
              if(file_exists($upload_dir.$file)){ 
        @unlink($upload_dir."/$file");//delete the previous file
              }
              $size = $pic['size']; 
              if ($size > $size_bytes) 
              { 
                    $kb = $size_bytes / 1024; 
                    echo "<center>File Too Large. File must be <b>$kb</b> KB. <br>»<a href=\"$_SERVER[PHP_SELF]\">back</a>"; 
                    exit(); 
              } 
              $ext = strrchr($pic['name'],'.'); 
              if (($extlimit == "yes") && (!in_array($ext,$limitedext))) { 
                    echo("<center>Wrong file extension. ");
                    echo "<br><a href='javascript:history.go(-1)'>Click here to go back</a>"; 
                    exit(); 
              } 
              $filename =  $pic['name']; 
              if(file_exists($upload_dir.$filename)){ 
                    echo "<center>A image with the name <b>$filename </b>already exists change the name of your Image. <br>»<a href=\"$_SERVER[PHP_SELF]\">back</a>"; 
                    exit(); 
              }  
              if (move_uploaded_file($pic['tmp_name'],$upload_dir.$filename)) {
          chmod($upload_dir.$filename, 0644);
              } 
                  else 
              { 
                  echo "<center>There was a problem saving your file.<br>please try again later <br>»<a href=\"$_SERVER[PHP_SELF]\">back</a>"; 
                   
              }
              
}
?>