<?php
defined('BASEPATH') OR exit('No direct script access allowed');

header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: GET, OPTIONS, POST, GET, PUT");
header("Access-Control-Allow-Headers: Content-Type, Content-Length, Accept-Encoding");

date_default_timezone_set('Asia/Kolkata');

class Sync_Model extends CI_Model {
    public function sync_chair($chair,$ShopID)
    {
	    $status=false;
	    $result="";
	    if($chair != null)
	    {
            foreach($chair as $data) 
            {
        	    $CharirName=$data->CharirName;
    	        $Type=$data->Type;
                $sql = "SELECT CounterID FROM CounterDetails where Details='".$CharirName."' and ShopID=".$ShopID."";
                $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                }
                else
                {
                    $sql="INSERT INTO CounterDetails(Details,ShopID,Type) 
                    VALUES ('". $CharirName ."',".$ShopID.",'".$Type."')";
                    $check = $this->db->query($sql);
                    if($check)
                    {
                    }
                    else
                    {
                    }
                }
            }        	        	        
	    }
	    else
	    {
	        $result= "null";
	    }
	    $sql="SELECT * FROM CounterDetails WHERE ShopID=".$ShopID."";
	    return $this->db->query($sql)->result();
    }
    public function sync_shopDetails($shops)
	{
	    $status=false;
	    $result="";
	    $ShopID=0;
	    if($shops != null)
		{
		    $value=$shops;
            $ShopName=$value->ShopName;
            $Address=$value->Address;
            $Contact=$value->Contact;
            $Email=$value->Email;
            $RegnNo=$value->RegnNo;
            $Img1=$value->Img1;
            $ShopID=$value->ShopID;
            $tax=$value->Tax;
            $sql = "SELECT ShopID FROM Shops where ShopID=".$ShopID."";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $sql="Update Shops Set ShopName = '". $ShopName ."',Address='".$Address."',Contact='".$Contact."',Email='".$Email."',RegnNo='".$RegnNo."',Tax=".$tax." where ShopID=". $ShopID ."";
                $check = $this->db->query($sql);
                if($check)
                {
                    $status=true;
                    $result="ok";
                    $ShopID=$row->ShopID;
                    try
                    {
                        $filename1="";
                        if ($Img1 != null || $Img1 !="")
                        {
                            $Img=$Img1;
                            $image = base64_decode($Img);
                            $image_name = "Shop1_".$ShopID."_1";
                            $filename1 = $image_name . '.' . 'png';
                            $path = "./images/";
                            file_put_contents($path . $filename1, $image);
                            $sql = "Update Shops Set Img1='".$filename1."' where ShopID=".$ShopID."";                         
                            $check = $this->db->query($sql);
                        }
                    }
                    catch (Exception $e) 
                    {
                        
                    }
                }
                else
                {
                    $status=false;
                    $result="Not Updated";
                }
             }
             else
             {
                $status=false;
                $result="Shop Not Exists";
                $ShopID=$row->ShopID;
             }
		}
		$arr = array (
              array(
                  "status" => $status,
                  "result" => $result,
                  "ShopID" => $ShopID
              )
        );
		return $arr;
	}

    public function sync_Updatechair($chair,$ShopID)
    {
	    $status=false;
	    $result="";
	    if($chair != null)
	    {
            foreach($chair as $data) 
            {
        	    $CharirName=$data->CharirName;
        	    $ChairID=$data->ChairID;
    	        $Type=$data->Type;
                $sql = "SELECT CounterID FROM CounterDetails where CounterID=".$ChairID." and ShopID=".$ShopID."";
                $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                    $sql="Update CounterDetails Set Details='".$CharirName."',Type='".$Type."' where CounterID=".$ChairID.""; 
                    $check = $this->db->query($sql);
                    if($check)
                    {
                        $result="OK";
                    }
                    else
                    {
                        $result="Error";
                    }
                }
                else
                {
                    $result="Error";                    
                }
            }        	        	        
	    }
	    else
	    {
	        $result= "null";
	    }
	    return $result;
    }
    public function sync_getAllUser($ShopID)
    {
        $array = array();
	    $sql="SELECT * FROM `AdminUser` WHERE ShopID=".$ShopID."";
	    return $this->db->query($sql)->result();
    }    
    public function sync_getAllStaff($ShopID)
    {
        $array = array();
	    $sql="SELECT StaffID,StaffName,Address,Mobile,ShopID,Email,DOB,DOJ,Salary,Department,CONCAT('https://api.ibill.ae/images/',Img) as Img,TimeStamp FROM `StaffMaster` WHERE ShopID=".$ShopID."";
	    $query = $this->db->query($sql);
    	foreach ($query->result() as $row)
        {
            $StaffID=$row->StaffID;
            $StaffName=$row->StaffName;
            $Address=$row->Address;
            $Mobile=$row->Mobile;
            $ShopID=$row->ShopID;
            $Email=$row->Email;
            $DOB=$row->DOB;
            $DOJ=$row->DOJ;
            $Salary=$row->Salary;
            $Department=$row->Department;
            $Img=$row->Img;
            $Proof="";
            $sql="SELECT *,CONCAT('https://api.ibill.ae/images/',Img) as ImgURL FROM ProofDetails WHERE StaffID=".$StaffID."";
        	$Proof=$this->db->query($sql)->result();
            array_push($array,array(
                      "StaffID" => $StaffID,
                      "StaffName" => $StaffName,
                      "Address" => $Address,
                      "Mobile" => $Mobile,
                      "ShopID" => $ShopID,
                      "Email" => $Email,
                      "DOB" => $DOB,
                      "DOJ" => $DOJ,
                      "Salary" => $Salary,
                      "Salary" => $Department,
                      "Img" => $Img,
                      "Proof" => $Proof
                  ));
        }
	    return $array;
    }
    public function sync_Updatestaff($staff,$ShopID)
    {
	    $status=false;
	    $result="";
	    if($staff != null)
	    {
	        $value=$staff;
            $StaffName=$value->StaffName;
            $Address=$value->Address;
            $Mobile=$value->Mobile;
            $Email=$value->Email;
            $DOB=$value->DOB;
            $DOJ=$value->DOJ;
            $Salary=$value->Salary;
            $Img=$value->Img;
            $Department="";
            $ProofDetails=$value->ProofDetails;
            $StaffID=$value->StaffID;
            if ($Salary =="")
            {
                $Salary=0;
            }
            if ($Salary ==null)
            {
                $Salary=0;
            }	     
            $sql = "SELECT StaffID FROM StaffMaster where StaffID=".$StaffID."";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $sql="Update StaffMaster Set StaffName='". $StaffName ."',Address='".$Address."',Mobile='".$Mobile."',ShopID=".$ShopID.",Email='".$Email."'
                ,DOB='".$DOB."',DOJ='".$DOJ."',Salary=".$Salary.",Department='".$Department."' where StaffID=".$StaffID."";
                $check = $this->db->query($sql);
                if($check)
                {
                    if ($Img!=null)
                    {
                        $image = base64_decode($Img);
                        $image_name = md5(uniqid(rand(), true));
                        $image_name="Staff_".$StaffID;
                        $filename = $image_name . '.' . 'png';
                        $path = "./images/";
                        file_put_contents($path . $filename, $image);
            
                        $sql="Update StaffMaster Set Img='".$filename."' where StaffID=".$StaffID."";
                        $check = $this->db->query($sql);                        
                    }
                    try
                    {
                        $ii =0;
                        foreach($ProofDetails as $data) 
                        {
                                $ii=$ii+1;
                                $proofname=$data->ProofName;
                                $proofNo=$data->ProofNo;
                                $ProofDate=$data->ProofDate;
                                $ExpDate=$data->ExpDate;
                                $Img=$data->Img;
                                
                                $image = base64_decode($Img);
                                $image_name = md5(uniqid(rand(), true));
                                $image_name="Proof_".$StaffID."_".$ii;
                                $filename = $image_name . '.' . 'png';
                                $path = "./images/";
                                file_put_contents($path . $filename, $image); 
                                // $image = base64_decode($Img);
                                // $image_name = "Proof_".$StaffID."_".$ii;
                                // $filename = $image_name . '.' . 'png';
                                // $path = "./images/";
                                // file_put_contents($path . $filename, $image);
                                $sql="insert into ProofDetails(StaffID,ProofName,ProofNo,ProofDate,ExpDate,RemindDays,Img)
                                            values(".$StaffID.",'".$proofname."','".$proofNo."','". $ProofDate ."','". $ExpDate ."',7,'".$filename."')";
                                $check = $this->db->query($sql);
                        }
                    }
                    catch (Exception $e) 
                    {
                        
                    }  
                    $status=true;
                    $result="ok";                    
                }
                else
                {
                    $status=fales;
                    $result="error";
                }
            } 
            else
            {
                $status=fales;
                $result="not Found";
            }
	    }
	    else
	    {
	        $result= "null";
	    }
	    return $result;
    }        
    public function sync_staff($staff,$ShopID)
    {
	    $status=false;
	    $result="";
	    if($staff != null)
	    {
            foreach($staff as $value) 
            {
                $StaffName=$value->StaffName;
                $Address=$value->Address;
                $Mobile=$value->Mobile;
                $Email=$value->Email;
                $DOB=$value->DOB;
                $DOJ=$value->DOJ;
                $Salary=$value->Salary;
                $Img=$value->Img;
                $Department="";
                $ProofDetails=$value->ProofDetails;
                if ($Salary =="")
                {
                    $Salary=0;
                }
                if ($Salary ==null)
                {
                    $Salary=0;
                }
                $sql = "SELECT StaffID FROM StaffMaster where StaffName='".$StaffName."' and ShopID=".$ShopID."";
                $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                }
                else
                {
                    $sql="INSERT INTO StaffMaster(StaffName,Address,Mobile,ShopID,Email,DOB,DOJ,Salary,Department,Img) 
                    VALUES ('". $StaffName ."','".$Address."','".$Mobile."',".$ShopID.",'".$Email."','".$DOB."','".$DOJ."',".$Salary.",'".$Department."'
                    ,'')";
                    $check = $this->db->query($sql);
                    if($check)
                    {
                        $status=true;
                        $result="ok";
                        $sql = "SELECT StaffID FROM StaffMaster where StaffName='".$StaffName."' and ShopID=".$ShopID."";
                        $query = $this->db->query($sql);
                        $row = $query->row();
                        if (isset($row))
                        {
                            $StaffID=$row->StaffID;
                            
                            $image = base64_decode($Img);
                            $image_name = md5(uniqid(rand(), true));
                            $image_name="Staff_".$StaffID;
                            $filename = $image_name . '.' . 'png';
                            $path = "./images/";
                            file_put_contents($path . $filename, $image);
            
                            $sql="Update StaffMaster Set Img='".$filename."' where StaffID=".$StaffID."";
                            $check = $this->db->query($sql);
                        }
    
                        try
                        {
                            $ii =0;
                            foreach($ProofDetails as $data) 
                            {
                                $ii=$ii+1;
                                $proofname=$data->ProofName;
                                $proofNo=$data->ProofNo;
                                $ProofDate=$data->ProofDate;
                                $ExpDate=$data->ExpDate;
                                $Img=$data->Img;
                                
                                $image = base64_decode($Img);
                                $image_name = md5(uniqid(rand(), true));
                                $image_name="Proof_".$StaffID."_".$ii;
                                $filename = $image_name . '.' . 'png';
                                $path = "./images/";
                                file_put_contents($path . $filename, $image); 
                                // $image = base64_decode($Img);
                                // $image_name = "Proof_".$StaffID."_".$ii;
                                // $filename = $image_name . '.' . 'png';
                                // $path = "./images/";
                                // file_put_contents($path . $filename, $image);
                                $sql="insert into ProofDetails(StaffID,ProofName,ProofNo,ProofDate,ExpDate,RemindDays,Img)
                                            values(".$StaffID.",'".$proofname."','".$proofNo."','". $ProofDate ."','". $ExpDate ."',7,'".$filename."')";
                                $check = $this->db->query($sql);
                            }
                        }
                        catch (Exception $e) 
                        {
                            
                        }
                    }
                    else
                    {
                    }
    	        } 
    	        //============================================================
            }        	        	        
	    }
	    else
	    {
	        $result= "null";
	    }
	    return $this->sync_getAllStaff($ShopID);
    }  
    // public function sync_product($items, $ShopID)
    // {
    //     $status = false;
    //     $results = [];
    
    //     if (!is_array($items)) {
    //         return ['status' => false, 'message' => 'Invalid item data'];
    //     }
    
    //     foreach ($items as $value) {
    //         $ItemName = $this->db->escape_str($value['ItemName'] ?? '');
    //         $Type     = $this->db->escape_str($value['Type'] ?? '');
    //         $Category = $this->db->escape_str($value['Category'] ?? '');
    //         $Rate     = isset($value['Rate']) ? floatval($value['Rate']) : 0;
    //         $Tax      = isset($value['Tax']) ? floatval($value['Tax']) : 0;
    //         $Details  = $this->db->escape_str($value['Details'] ?? '');
    
    //         // Check if item exists
    //         $this->db->where(['ItemName' => $ItemName, 'ShopID' => $ShopID]);
    //         $query = $this->db->get('Item_Master');
    
    //         if ($query->num_rows() == 0) {
    //             // Insert new item
    //             $data = [
    //                 'ItemName' => $ItemName,
    //                 'Type' => $Type,
    //                 'Category' => $Category,
    //                 'Rate' => $Rate,
    //                 'Tax' => $Tax,
    //                 'Details' => $Details,
    //                 'ShopID' => $ShopID
    //             ];
    
    //             if ($this->db->insert('Item_Master', $data)) {
    //                 $status = true;
    //                 $results[] = ['ItemName' => $ItemName, 'status' => 'inserted'];
    //             } else {
    //                 $results[] = ['ItemName' => $ItemName, 'status' => 'failed'];
    //             }
    //         } else {
    //             $results[] = ['ItemName' => $ItemName, 'status' => 'exists'];
    //         }
    //     }
    
    //     // Return all items for this shop
    //     $all = $this->db->get_where('Item_Master', ['ShopID' => $ShopID])->result();
    //     return $all;
    // }
        
    public function sync_product($item,$ShopID)
    {
	    $status=false;
	    $result="";
	    if($item != null)
	    {
            foreach($item as $value) 
            {
                $ItemName=$value->ItemName;
                $Type=$value->Type;
                $Category=$value->Category;
                $Rate=$value->Rate;
                $Tax=0;
                try
                {
                    $Tax=$value->Tax;
                }
                catch(Exception $e)
                {
                }
                $Details=$value->Details;
                $sql = "SELECT ItemID FROM Item_Master where ItemName='".$ItemName."' and ShopID=".$ShopID."";
                $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                }
                else
                {
                    $sql="INSERT INTO Item_Master(ItemName,Type,Category,Rate,Tax,Details,ShopID) 
                    VALUES ('". $ItemName ."','".$Type."','".$Category."',". $Rate .",". $Tax .",'". $Details ."',".$ShopID.")";
                    $check = $this->db->query($sql);
                    if($check)
                    {
                         $status=true;
                         $result="ok";
                    }
                    else
                    {
                        $status=false;
                        $result="Not Registered";
                    }
                }
            }
	    }
	    else
	    {
	        $result= "null";
	    }
	    $sql="SELECT * FROM `Item_Master` WHERE ShopID=".$ShopID."";
	    return $this->db->query($sql)->result();
    }      
    public function sync_updateproduct($item,$ShopID)
    {
	    $status=false;
	    $result="";
	    if($item != null)
	    {
            foreach($item as $value) 
            {
                $ItemName=$value->ItemName;
                $Type=$value->Type;
                $Category=$value->Category;
                $Rate=$value->Rate;
                $ItemID=$value->ItemID;
                $Tax=0;
                try
                {
                    $Tax=$value->Tax;
                }
                catch(Exception $e)
                {
                }
                $Details=$value->Details;
                $sql = "SELECT ItemID FROM Item_Master where ItemID=".$ItemID." and ShopID=".$ShopID."";
                $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                    $sql="Update Item_Master Set ItemName='". $ItemName ."',Type='".$Type."',Category='".$Category."',Rate=". $Rate .",Tax=". $Tax .",Details='". $Details ."' where ItemID=".$ItemID."";
                    $check = $this->db->query($sql);
                    if($check)
                    {
                         $status=true;
                         $result="ok";
                    }
                    else
                    {
                        $status=false;
                        $result="Error";
                    }
                }
                else
                {
                        $status=false;
                        $result="Not Registered";
                }
            }
	    }
	    else
	    {
	        $result= "null";
	    }
	    return $result;
    }          
    public function sync_ShopActivity($data,$ShopID)
    {
	    $status=false;
	    $result="";
	    $CompanyID=0;
	    $array = array();
	    if($data != null)
	    {
	        $sql="Select CompanyID from Shops where ShopID=".$ShopID."";
	        $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $CompanyID=$row->CompanyID;
            }
            foreach($data as $value) 
            {
                $EntryDate=$value->EntryDate;
                $EntryTime=$value->EntryTime;
                $ChairID=$value->ChairID;
                $StaffID=$value->StaffID;
                $Status=$value->Status;
                $BillNo=$value->BillNo;
                $Details=$value->Details;
                $RefID=$value->RefID;
                $chair_id=$value->ChairID;
                $staff_id=$value->StaffID;
                $UUID=$ShopID.'-'.$ChairID.'-'.$StaffID.'-'.$EntryDate.'-'.$EntryTime.'-'.$Status;
                try
                {
                    $sql="REPLACE INTO ShopActivity(ShopID,CounterID,StaffID,EntryDate,EntryTime,Type,BillNo,Status,CompanyID,Details,Note,UUID) 
                    VALUES (".$ShopID.",".$ChairID.",".$StaffID.",'". $EntryDate ."','".$EntryTime."','',".$BillNo.",".$Status.",". $CompanyID .",'','". $Details ."','".$UUID."')";
                    $check = $this->db->query($sql);
                    if($check)
                    {
                         $status=true;
                         $result="ok";
                         $UID=0;
                        $sql="Select Max(UID) as UID from ShopActivity where ShopID=".$ShopID."";
            	        $query = $this->db->query($sql);
                        $row = $query->row();
                        if (isset($row))
                        {
                            $UID=$row->UID;
                        }
                         array_push($array,array(
                          "RefID" => $RefID,
                          "UID" => $UID
                         ));            
                    }
                    else
                    {
                    }
                } 
                catch (Exception $e) 
                {
                }
            }
	    }
	    else
	    {
	        $result= "null";
	    }
	    return $array;
    }           
    
    
    
//     public function sync_ShopActivity($data, $ShopID)
//     {
//     $array = [];

//     // Retrieve CompanyID securely
//     $CompanyID = 0;
//     $query = $this->db->query("SELECT CompanyID FROM Shops WHERE ShopID = ?", [$ShopID]);
//     $row = $query->row();
//     if (isset($row)) {
//         $CompanyID = $row->CompanyID;
//     }

//     if ($data != null) {
//         // Prepare insert data for batch processing

//         $insertData = [];
//         foreach ($data as $value) {
//             $chair_id=$value->ChairID;
//             $staff_id=$value->StaffID;
//             $UUID=$ShopID.'-'.$chair_id.'-'.$staff_id.'-'.$value->EntryDate.'-'.$value->EntryTime.'-'.$value->Status;
//             $insertData[] = [
//                 'ShopID' => $ShopID,
//                 'CounterID' => $value->ChairID,
//                 'StaffID' => $value->StaffID,
//                 'EntryDate' => $value->EntryDate,
//                 'EntryTime' => $value->EntryTime,
//                 'Type' => '',  // Assuming Type is blank based on the original code
//                 'BillNo' => $value->BillNo,
//                 'Status' => $value->Status,
//                 'CompanyID' => $CompanyID,
//                 'Details' => '',
//                 'UUID' => $UUID,
//                 'Note' => $value->Details
//             ];
//         }

//         // Batch insert all data at once
//         $this->db->insert_batch('ShopActivity', $insertData);

//         // Retrieve UIDs for all inserted rows
//         $query = $this->db->query(
//             "SELECT UUID FROM ShopActivity WHERE ShopID = ? ORDER BY UID DESC LIMIT ?",
//             [$ShopID, count($insertData)]
//         );

//         // Match RefIDs with the UIDs retrieved
//         $i = 0;
//         foreach ($query->result() as $row) {
//             $array[] = [
//                 "RefID" => $data[$i]->RefID,
//                 "UID" => $row->UUID
//             ];
//             $i++;
//         }
//     } else {
//         $array = ["result" => "null"];
//     }

//     return $array;
// }

    
    
    
    
    
    public function sync_UpdateBill($data,$ShopID)
    {
	    $status=false;
	    $result="OK";
	    $CompanyID=0;
	    $BID=0;
	    $array = array();
	    if($data != null)
	    {
	        $value=$data;
            $BillNo=$value->BillNo;
            $EntryDate=$value->EntryDate;
            $EntryTime=$value->EntryTime;
            $ChairID=$value->ChairID;
            $StaffID=$value->StaffID;
            $Amount=$value->Amount;
            $Cash=$value->Cash;
            $Card=$value->Card;
            $BankName=$value->BankName;
            $Customer=$value->Customer;
            $Place=$value->Place;
            $Mobile=$value->Mobile;
            $Tax=$value->Tax;
            $Net=$value->Net;
            $BID=$value->BID;
            $items=$value->items;	        
            $sql="Select BillNo from Bill_inf where BID=".$BID."";
    	    $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $sql="Update Bill_inf Set CounterID=".$ChairID.",StaffID=".$StaffID.",EntryDate='". $EntryDate ."',Amount=".$Amount.",Cash=".$Cash.",Card=".$Card.",BankName='".$BankName."',Customer='".$Customer."'
                        ,Place='".$Place."',Mobile='".$Mobile."',Tax=". $Tax .",Net=".$Net." where BID=".$BID."";
                $check = $this->db->query($sql);
                if($check)
                {   
                    $sql="Delete from Bill_Details where BID=".$BID."";
                    $check = $this->db->query($sql);
                    foreach($items as $data) 
                    {
                        $ItemName=$data->ItemName;
                        $Qty=$data->Qty;
                        $Rate=$data->Rate;
                        $Amount=$data->Amount;
                        $Type=$data->Type;
                        if ($ItemName !="")
                        {
                            $sql="insert into Bill_Details(BID,BillNo,ItemName,Rate,Qty,Amount,Type)
                                values(".$BID.",".$BillNo.",'".$ItemName."',".$Rate.",".$Qty.",".$Amount.",'".$Type."')";
                            $check = $this->db->query($sql);
                        }
                    }                    
                    $status=true;
                    $result="ok";                    
                }   
            }
            else
            {
                $result="Bill Not Found";
            }
	    }
	    else
	    {
	        $result= "null001";
	    }
	    return $result;
    }	    
    public function sync_Bills($data,$ShopID)
    {
	    $status=false;
	    $result="OK";
	    $CompanyID=0;
	    $array = array();
	    if($data != null)
	    {
	        $sql="Select CompanyID from Shops where ShopID=".$ShopID."";
	        $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $CompanyID=$row->CompanyID;
            }
            foreach($data as $value) 
            {
                $BillNo=$value->BillNo;
                $EntryDate=$value->EntryDate;
                $EntryTime=$value->EntryTime;
                $ChairID=$value->ChairID;
                $StaffID=$value->StaffID;
                $Amount=$value->Amount;
                $Cash=$value->Cash;
                $Card=$value->Card;
                $BankName=$value->BankName;
                $Customer=$value->Customer;
                $Place=$value->Place;
                $Mobile=$value->Mobile;
                $Tax=$value->Tax;
                $Net=$value->Net;
                $RefID=$value->RefID;
                $items=$value->items;
                $UUID=$ShopID.'-'.$ChairID.'-'.$StaffID.'-'.$EntryDate.'-'.$EntryTime;
                $sql="Select BillNo from Bill_inf where ShopID=".$ShopID." and BillNo=".$BillNo." and EntryDate='".$EntryDate."'";
                $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                    $status=false;
                    $result="Already Exists";
                }
                else
                {
                    $BillNo=0;
                    $sql="Select Max(BillNo) as BillNo from Bill_inf where ShopID=".$ShopID."";
        	        $query = $this->db->query($sql);
                    $row = $query->row();
                    if (isset($row))
                    {
                        
                        if ($row->BillNo != null)
                        {
                            $BillNo=($row->BillNo)+1;
                        }
                        else
                        {
                            $BillNo=1;
                        }
                    }
                    $sql="INSERT into Bill_inf(BillNo,ShopID,CounterID,StaffID,EntryDate,EntryTime,Amount,Cash,Card,BankName,Customer,Place,Mobile,Tax,Net,UUID) 
                    VALUES (".$BillNo.",".$ShopID.",".$ChairID.",".$StaffID.",'". $EntryDate ."','".$EntryTime."',".$Amount.",".$Cash.",".$Card.",'".$BankName."','".$Customer."','".$Place."','".$Mobile."',". $Tax .",".$Net.",'".$UUID."')";
                    $check = $this->db->query($sql);
                    if($check)
                    {
                        $sql="Select Max(Bid) as Bid from Bill_inf where ShopID=".$ShopID."";
            	        $query = $this->db->query($sql);
                        $row = $query->row();
                        if (isset($row))
                        {
                            $Bid=$row->Bid;
                        }
                        foreach($items as $data) 
                        {
                            $ItemName=$data->ItemName;
                            $Qty=$data->Qty;
                            $Rate=$data->Rate;
                            $Amount=$data->Amount;
                            $Type=$data->Type;
                            if ($ItemName !="")
                            {
                                $sql="insert into Bill_Details(BID,BillNo,ItemName,Rate,Qty,Amount,Type)
                                    values(".$Bid.",".$BillNo.",'".$ItemName."',".$Rate.",".$Qty.",".$Amount.",'".$Type."')";
                                $check = $this->db->query($sql);
                            }
                        }                    
                        $status=true;
                        $result="ok";
                         
                        array_push($array,array(
                          "RefID" => $RefID,
                          "Bid" => $Bid
                        ));                     
                    }
                    else
                    {
                    }
                }
            }
	    }
	    else
	    {
	        $result= "null001";
	    }
	    return $array;
    }   
    public function sync_expenses($data,$ShopID)
    {
        $status=false;
	    $result="OK";
	    $CompanyID=0;
	    $array = array();
	    if($data != null)
	    {
	        $sql="Select CompanyID from Shops where ShopID=".$ShopID."";
	        $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $CompanyID=$row->CompanyID;
            }
            foreach($data as $value) 
            {
	            $EntryDate=$value->EntryDate;
	            $Note=$value->Note;
                $RefID=$value->RefID;
                $items=$value->Items;
                $EntryNo=0;
                $sql="Select Max(EntryNo) as EntryNo from PVRV_inf where ShopID=".$ShopID."";
    	        $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                    if ($row->EntryNo != null)
                    {
                        $EntryNo=($row->EntryNo)+1;
                    }
                    else
                    {
                        $EntryNo=1;
                    }
                }	            
	            $sql="INSERT INTO PVRV_inf(EntryNo,EntryDate,ShopID,Note,Type,Voucherid) VALUES (".$EntryNo.",'".$EntryDate."',".$ShopID.",'".$Note."','P',0)";
                $check = $this->db->query($sql);
                if($check)
                {
                    $RefNo=0;
                    $sql="Select Max(RefNo) as 	RefNo from PVRV_inf where ShopID=".$ShopID."";
        	        $query = $this->db->query($sql);
                    $row = $query->row();
                    if (isset($row))
                    {
                        $RefNo=$row->RefNo;
                    }
                    $TotalAmount=0;
                    foreach($items as $data) 
                    {
                        $ItemName=$data->ItemName;
                        $Amount=$data->Amount;
                        $sql="insert into PVRV_Details(RefNo,EntryNo,ItemName,Amount) values(".$RefNo.",".$EntryNo.",'".$ItemName."',".$Amount.")";
                          $check = $this->db->query($sql);
                        $TotalAmount=$TotalAmount+$Amount;
                    }
                    $sql="Update PVRV_inf Set Amount=".$TotalAmount." where RefNo=".$RefNo."";
                    $check = $this->db->query($sql);
                    $status=true;   
                    $result="Saved";
                    array_push($array,array(
                      "RefID" => $RefID,
                      "RefNo" => $RefNo
                    ));                             
                }
            }
	    }
	    else
	    {
	        $result= "null001";
	    }
	    return $array;
    }     
    public function sync_ibilllogin($UserName,$Password)
    {
        $result="";
        $status=0;
        $array = array();
	    $sql="Select UName,PWd,Status from IbillUser where UName='".$UserName."'";
    	$query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
    	{
            $PWd=$row->PWd;
            $status=$row->Status;
            if ($Password==$PWd)
            {
                $result="OK";
            }
            else
            {
                $result="Incorrect UserName Or Password";
            }
    	}
    	else
    	{
            $result="Incorrect UserName Or Password";
            $status=0;
    	}
        $arr = array (
            array(
                "status" => $status,
                "result" => $result
            )
        );
        return $arr;    	
    }   
    public function sync_addibillcustomer($PersonalID,$CName,$Company,$Mobile,$State,$Dist,$Place,$EntryDate,$SDate,$EDate,$Days,$SerialNo)
    {
        $result="";
        $UID=0;
        $array = array();
	    $sql="Select UID from IbillCustomer where PersonalID='".$PersonalID ."'";
    	$query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
    	{
            $UID=$row->UID;
            $sql="Insert into Serial(ID,SDate,EDate,Days,SerialNo) values('".$PersonalID."','".$SDate."','".$EDate."','".$Days."','".$SerialNo."')";
            $check = $this->db->query($sql);                     
            $result="OK";
    	}
    	else
    	{
            $sql="Insert into IbillCustomer(PersonalID,CName,Company,Mobile,State,Dist,Place,EntryDate) values('".$PersonalID."','".$CName."','".$Company."','".$Mobile."','".$State."','".$Dist."','".$Place."','".$EntryDate."')";
            $check = $this->db->query($sql);    
            if($check)
            {
                $sql="Select UID from IbillCustomer where PersonalID='".$PersonalID ."'";
            	$query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
            	{
                    $UID=$row->UID;
                    $sql="Insert into Serial(ID,SDate,EDate,Days,SerialNo) values(".$UID.",'".$SDate."','".$EDate."','".$Days."','".$SerialNo."')";
                    $check = $this->db->query($sql);                     
                    $result="OK";
            	}                
            }
        }
        $arr = array (
            array(
                "UID" => $UID,
                "result" => $result
            )
        );
        return $arr;    	
    } 
    public function sync_getIbillSerialNo($PersonalID)
    {
        $array = array();
	    $sql="Select a.SDate,a.EDate,a.Days,a.SerialNo,b.CName,b.Company,b.Mobile,b.State,b.Dist,b.Place from Serial a inner join IbillCustomer b on a.ID=b.UID where b.PersonalID='".$PersonalID."'";
	    return $this->db->query($sql)->result();
    } 
    public function sync_searchIbillCustomer($Customer)
    {
        $array = array();
	    $sql="Select a.SDate,a.EDate,a.Days,a.SerialNo,b.CName,b.Company,b.Mobile,b.State,b.Dist,b.Place,b.PersonalID from Serial a inner join IbillCustomer b on a.ID=b.UID where b.Company like '%".$Customer."%' limit 100";
	    return $this->db->query($sql)->result();
    }   
    
    
/*  Commented by ILLIYAS

     public function sync_Booking($data)
    {
	    $status=false;
	    $result="OK";
	    $CompanyID=0;
	    $UID=0;
	    $array = array();
	    if($data != null)
	    {
	        $value=$data;
            $cname=$value->cname;
            $email=$value->email;
            $phone=$value->phone;
            $EntryDate=$value->EntryDate;
            $EntryTime=$value->EntryTime;
            $StaffID=$value->StaffID;
	        $ShopID=$value->ShopID;
            $sql="insert into Booking(cname,email,phone,EntryDate,StaffID,ShopID,EntryTime)
            values('".$cname."','".$email."','".$phone."','".$EntryDate."',".$StaffID.",".$ShopID.",'".$EntryTime."')";
            $check = $this->db->query($sql);
            if($check)
            {
                $sql="Select Max(UID) as UID from Booking";
        	    $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                    $UID=$row->UID;
                    $status=true;
                    $result="ok";        
                }
            }
	    }
	    else
	    {
	        $result= "null001";
	    }
        $arr = array (
            array(
                "UID" => $UID,
                "status" => $status,
                "result" => $result
            )
        );
        return $arr;  
    }	*/
    
    public function sync_allbooking($ShopID)
    {
        $array = array();
	    $sql="Select a.*,b.StaffName from Booking a inner join StaffMaster b on a.StaffID=b.StaffID where a.ShopID=".$ShopID."";
	    return $this->db->query($sql)->result();
    }    
    public function sync_updatebookingstatus($UID,$Status)
    {
	    $status=false;
	    $result="Error";
	    $sql="Select ShopID from Booking where UID=".$UID."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $ShopID=$row->ShopID;
            $sql="Update Booking Set Status='".$Status."' where UID=".$UID."";
            $check = $this->db->query($sql);
            if($check)
            {
                $status=true;
                $result="ok";        
            }            
	    }
	    $sql="Select a.*,b.StaffName from Booking a inner join StaffMaster b on a.StaffID=b.StaffID where a.ShopID=".$ShopID."";
	    return $this->db->query($sql)->result();
    }
    public function online_register($company)
	{
        $result="";
 		$CompanyName="";
 		$CompanyID=0;
 		$ShopID=0;
 		$UserName="";
 		$Password="";
        $status=false;
 	    if($company != null)
 		{
 		    $value=$company;
            $CompanyName=$value->CompanyName;
            $Address=$value->Address;
            $Mobile=$value->Mobile;
            $Email=$value->Email;
            $Img=$value->Img;
            $OwnerName=$value->OwnerName;
            $ContactNo=$Mobile;
            $Status=1;
            $ContactPerson="";
            $Designation="";

            $sql = "SELECT CompanyID FROM Company where CompanyName='".$CompanyName."'";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                      $status=false;
                      $result="Company Already Exists";
                      $CompanyID=$row->CompanyID;
            }
            else
            {
                     $sql="INSERT INTO Company(CompanyName,Address,Mobile,Email,Status,Img,OwnerName,ContactPerson,Designation,ContactNo) 
                          VALUES ('".$CompanyName."','".$Address."','".$Mobile."','".$Email."',".$Status.",'','".$OwnerName."','".$ContactPerson."','".$Designation."','".$ContactNo."')";
                     $check = $this->db->query($sql);
                     $status=$check;
                     if($check)
                     {
                        $CompanyID=0;
                        $sql = "SELECT CompanyID FROM Company where CompanyName='".$CompanyName."'";
                        $query = $this->db->query($sql);
                        $row = $query->row();
                        if (isset($row))
                        {
                            $CompanyID=$row->CompanyID;
                        }
                        $image = base64_decode($Img);
                        // $name = $this->input->post("name");
                        $image_name = md5(uniqid(rand(), true));
                        $image_name="CopmanyImg_" .$CompanyID ;
                        
                        $filename = $image_name . '.' . 'png';
                        //rename file name with random number
                        $path = "./images/";
                        //image uploading folder path
                        file_put_contents($path . $filename, $image);
                        // image is bind and upload to respective folder
                        //$data['profile_pic'] = $filename;
                        //,Img='".$filename."'
                        $sql="Update Company Set Img='".$filename."'  where CompanyID=".$CompanyID."";
                        $check = $this->db->query($sql);
                        
                        $ShopName=$CompanyName;
                        $RegnNo="";
                        $PinCode="";
                        $Contact=$Mobile;
                        $latitude="";
                        $longitude="";
                        $StartTime="09:00:00";
                        $ClosingTime="10:00:00";
                        $HolyDays="";
                        $CategoryID=0;
                        $CurencyName="AED";
                        $ContryCode="+971";
                        $UserName=$Mobile;
                        $Pwd=$Mobile;
                        $tax=0;
                        $sql="INSERT INTO Shops(ShopName, CompanyID,Address,Contact,Email,RegnNo,PinCode,latitude,longitude,StartTime,ClosingTime,HolyDays,CategoryID,Img1,Img2,Img3,CurencyName,ContryCode,UserName,Pwd,Tax) 
                        VALUES ('". $ShopName ."','".$CompanyID."'
                        ,'".$Address."','".$Contact."','".$Email."','".$RegnNo."','".$PinCode."','".$latitude."','".$longitude."'
                        ,'".$StartTime."','".$ClosingTime."','".$HolyDays."',".$CategoryID.",'','','','".$CurencyName."','".$ContryCode."','".$UserName."','".$Pwd."',".$tax.")";
                        $check = $this->db->query($sql);
                        if($check)
                        {
                             $status=true;
                             $result="ok";
                             $ShopID=0;
                             $sql = "SELECT ShopID FROM Shops where ShopName='".$ShopName."'";
                             $query = $this->db->query($sql);
                             $row = $query->row();
                             if (isset($row))
                             {
                                  $ShopID=$row->ShopID;
                             }
                             $UserName=$UserName;
                             $Password=$Pwd;
                             $sql="INSERT INTO AdminUser(UserName,Password,ShopID,UserType) 
                                    VALUES ('". $UserName ."','".$Pwd."',".$ShopID.",'SHOP-ADMIN')";
                             $check = $this->db->query($sql);
                            $filename1="";
                            if ($Img != null || $Img !="")
                            {
                                 $Img=$Img;
                                 $image = base64_decode($Img);
                                 $image_name = "Shop1_".$ShopID."_1";
                                 $filename1 = $image_name . '.' . 'png';
                                 $path = "./images/";
                                 file_put_contents($path . $filename1, $image);
                                 $sql = "Update Shops Set Img1='".$filename1."' where ShopID=".$ShopID."";                         
                                 $check = $this->db->query($sql);
                            }
                            $sql="INSERT INTO AdminUser(UserName,Password,ShopID,UserType) 
                            VALUES ('". $Email ."','".$Pwd."',0,'OWNER')";
                            $check = $this->db->query($sql);
                            $sql = "SELECT UID FROM AdminUser where UserName='".$Email."' and UserType='OWNER'";
                            $query = $this->db->query($sql);
                            $row = $query->row();
                            if (isset($row))
                            {
                                $UID=$row->UID;
                                $sql="insert into OwnerShops(UserID,ShopID,CompanyID) values(".$UID.",".$ShopID.",".$CompanyID.")";
                                $check = $this->db->query($sql);
                            }
                        }
                        $status=true;
                        $result="Company Registsred";
                     }
                     else
                     {
                         $status=false;
                         $result="Company Not Registered";
                     }
            }
  		}
         $arr = array (
              array(
                  "status" => $status,
                  "CompanyName" => $CompanyName,
                  "result" => $result,
                  "ShopName" => $CompanyName,
                  "ShopID" => $ShopID,
                  "UserName" => $UserName,
                  "Password" => $Password,
                  "CompanyID" =>$CompanyID
              )
          );
		return $arr;
	}

}
