<?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 Admin_model extends CI_Model {
	
	public function get_all_shops()
	{
	    $sql="SELECT a.ShopID,a.ShopName,a.Address,a.Contact,a.Email,a.RegnNo,a.PinCode,a.latitude,a.longitude,a.StartTime,a.ClosingTime,a.HolyDays,a.Img1,a.Img2,a.Img3,b.CompanyName,a.CompanyID,a.CurencyName,a.ContryCode,a.UserName,a.Pwd
	        ,Coalesce(c.CatName,'') as Category,a.Tax FROM Shops a inner join Company b on a.CompanyID=b.CompanyID left join Category c on a.CategoryID=c.UID";
	    return $this->db->query($sql)->result();
	}
	public function get_shop($ShopID)
	{
	    $sql="SELECT a.ShopID,a.ShopName,a.Address,a.Contact,a.Email,a.RegnNo,a.PinCode,a.latitude,a.longitude,a.StartTime,a.ClosingTime,a.HolyDays,a.Img1,a.Img2,a.Img3,b.CompanyName,a.CompanyID,a.CurencyName,a.ContryCode,a.UserName,a.Pwd
	        ,Coalesce(c.CatName,'') as Category,a.Tax FROM Shops a inner join Company b on a.CompanyID=b.CompanyID left join Category c on a.CategoryID=c.UID where a.ShopId=".$ShopID."";
	    return $this->db->query($sql)->result();
	}
	public function get_all_company()
	{
	    $imageURLDefault="https://api.ibill.ae/images/";
	    $sql="select CompanyName,Address,Mobile,Email,CompanyID,Status,Img,'".$imageURLDefault."' as URL,OwnerName,ContactPerson,Designation,ContactNo from Company";
	    return $this->db->query($sql)->result();
	}
	public function get_Owners()
	{
	    $sql="SELECT UserName,Password,UserType,UID FROM AdminUser WHERE UserType='OWNER'";
	    return $this->db->query($sql)->result();
	}
	public function get_owners_details($UserID)
	{
	    $sql="Select a.UserID,b.UserName,b.Password,a.ShopID,a.CompanyID,c.ShopName,d.CompanyName from OwnerShops a inner join AdminUser b on a.UserID=b.UID INNER join Shops c on a.ShopID=c.ShopID inner join Company d on a.CompanyID=d.CompanyID where a.UserID=".$UserID."";
	    return $this->db->query($sql)->result();
	}
	//
    public function delete_shop($ShopID)
    {
        $statsu=false;
        $result="";
        $sql = "Select * from Bill_inf where ShopID=".$ShopID."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $statsu=false;
            $result="Unable to Delete Shop , Already have transactions";
        }
        else
        {
            $sql="Delete from Shops where ShopID=".$ShopID."";
            $check = $this->db->query($sql);
            if ($check==true)
            {
                $statsu=true;
                $result="Shop Deleted";
            }
            else
            {
                $statsu=false;
                $result="Shop Not Deleted,ID Not Found";
            }
        }
        return $result;
    }
	public function delete_company($CompanyID)
	{
	     $statsu=false;
        $result="";
        $sql = "Select * from Shops where CompanyID=".$CompanyID."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $statsu=false;
            $result="Unable to Delete Company , Shops Registered Under This Company";
        }
        else
        {
            $sql="Delete from Company where CompanyID=".$CompanyID."";
            $check = $this->db->query($sql);
            if ($check==true)
            {
                $statsu=true;
                $result="Company Deleted";
            }
            else
            {
                $statsu=false;
                $result="Company Not Deleted,ID Not Found";
            }
        }
        
        return $result;
	}

	public function get_company($CompanyID)
	{
	    
	    $imageURLDefault="https://api.ibill.ae/images/";
	    $sql="select CompanyName,Address,Mobile,Email,CompanyID,Status,Img,'".$imageURLDefault."' as URL,OwnerName,ContactPerson,Designation,ContactNo from Company where CompanyID=".$CompanyID."";
	    return $this->db->query($sql)->result();
	}
	public function add_owner($details,$Shops)
	{
	    $status=false;
	    $result="";
	    if($details != null)
	    {
	        $value=$details;
	        $UserName=$value->UserName;
	        $Pwd=$value->Pwd;
	        $sql = "Select a.UserName,a.Password,a.UserType from AdminUser a where a.UserName='".$UserName."' and a.UserType='OWNER'";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $status=false;
                $result="UserName Already Exists";
            }
            else
            {
                $sql="INSERT INTO AdminUser(UserName,Password,UserType) 
                VALUES ('". $UserName ."','".$Pwd."','OWNER')";
                $check = $this->db->query($sql);
                if($check)
                {
                    $UID=0;
                    $sql = "Select UID from AdminUser a where a.UserName='".$UserName."' and a.UserType='OWNER'";
                    $query = $this->db->query($sql);
                    $row = $query->row();
                    if (isset($row))
                    {
                        $UID=$row->UID;
                    }
        	        $status=true;
        	        $result="OK";  
                    $ii =0;
                    foreach($Shops as $data) 
                    {
                         $ii=$ii+1;
                         $ShopID=$data->ShopID;
                         $sql="Select CompanyID from Shops where ShopID=".$ShopID."";
                         $query = $this->db->query($sql);
                         $row = $query->row();
                         if (isset($row))
                         {
                             $CompanyID=$row->CompanyID;
                         }
                         $sql="insert into OwnerShops(UserID,ShopID,CompanyID)
                                     values(".$UID.",".$ShopID.",".$CompanyID.")";
                         $check = $this->db->query($sql);
                    }        	        
                }
            }
	    }
	    else
	    {
	        $status=false;
	        $result="Null";  
	    }
        $arr = array (
                  array(
                      "status" => $status,
                      "result" => $result
                  )
            );
        return $arr;
    }
    public function change_pwd_owner($UserName,$OldPwd,$NewPwd)
    {
        $status=false;
	    $result="";  
        $sql = "Select a.UID from AdminUser a where a.UserName='".$UserName."' and a.UserType='OWNER'";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {	    
	    	$sql = "Select a.UID,a.Password from AdminUser a where a.UserName='".$UserName."' and a.UserType='OWNER'";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
               $UID=$row->UID;
               $Password=$row->Password;
               if ($Password==$OldPwd)
               {
                   $sql="Update AdminUser Set Password='".$NewPwd."' where UID=".$UID."";
            	   $check = $this->db->query($sql);
            	   $status=true;
            	   $result="OK";                 
               }
               else
               {
            	   $status=false;
            	   $result="Old Password Not Match";                 
               }
            }
            else
            {
        	   $status=false;
        	   $result="Invalid UerName";                 
            }
        }
        $arr = array (
              array(
                  "status" => $status,
                  "result" => $result
              )
        );
        return $arr;
    }
	public function update_owner($details,$Shops)
	{
	    $status=false;
	    $result="";
	    if($details != null)
	    {
	        $value=$details;
	        $UserName=$value->UserName;
	        $Pwd=$value->Pwd;
	        $sql = "Select a.UID from AdminUser a where a.UserName='".$UserName."' and a.UserType='OWNER'";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $UID=$row->UID;
                $sql="Update AdminUser Set Password='".$Pwd."' where UID=".$UID."";
                $check = $this->db->query($sql);
                if($check)
                {
        	        $status=true;
        	        $result="OK"; 
                    $sql="Delete from OwnerShops where UserID=".$UID."";
                    $check = $this->db->query($sql);        	        
                    $ii =0;
                    foreach($Shops as $data) 
                    {
                         $ii=$ii+1;
                         $ShopID=$data->ShopID;
                         $sql="Select CompanyID from Shops where ShopID=".$ShopID."";
                         $query = $this->db->query($sql);
                         $row = $query->row();
                         if (isset($row))
                         {
                             $CompanyID=$row->CompanyID;
                         }
                         $sql="insert into OwnerShops(UserID,ShopID,CompanyID)
                                     values(".$UID.",".$ShopID.",".$CompanyID.")";
                         $check = $this->db->query($sql);
                    }        	        
                }
            }
            else
            {
                $status=false;
                $result="Invalid UserName";
            }
	    }
	    else
	    {
	        $status=false;
	        $result="Null";  
	    }
        $arr = array (
                  array(
                      "status" => $status,
                      "result" => $result
                  )
            );
        return $arr;
    }
    public function delete_ShopUsers($UserID)
    {
         $Status=false;
	    $result="";
        $sql = "Select a.UID from AdminUser a where a.UID=".$UserID."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $sql="Delete from AdminUser where UID=".$UserID."";
            $check = $this->db->query($sql);	        
	        $result="Deleted";
	        $Status=true;
        }
        else
	    {
	        $Status=false;
	        $result="Not Exists";
	    }
        $arr = array (
            array(
              "Status" => $Status,
              "result"=>$result
            )
        );  
	    return $arr;
    }
    public function get_ShopUsers($ShopID)
    {
        $sql="Select a.UserName,a.Password,a.UserType,a.UID,a.ShopID,b.ShopName from AdminUser a inner join Shops b on a.ShopID=b.ShopID where a.ShopID>0 and a.ShopID=".$ShopID."";
	    return $this->db->query($sql)->result();
    }
    public function get_ShopUserByID($UserID)
    {
        $sql="Select a.UserName,a.Password,a.UserType,a.UID,a.ShopID,b.ShopName from AdminUser a inner join Shops b on a.ShopID=b.ShopID where a.ShopID>0 and a.UID=".$UserID."";
	    return $this->db->query($sql)->result();
    }
    public function get_staffBills($StaffID,$EntryDate,$ShopID)
	{
	    $sql="SELECT BID,BillNo,EntryDate,Amount,Net,Tax,Cash,Card,Customer,Mobile FROM `Bill_inf` WHERE ShopID=".$ShopID." and StaffID=".$StaffID." and EntryDate='".$EntryDate."'";
	    return $this->db->query($sql)->result();
	}
    public function update_bill($BID,$Amount,$Cash,$Card,$Customer,$Mobile,$Net,$Tax)
	{
	    $Status=false;
	    $result="";
	    $tax=0;
        try
        {
             $tax=$Tax;
        }
        catch(Exception $e)
        {
        }
        $net=0;
        try
        {
             $net=$Net;
        }
        catch(Exception $e)
        {
        }
        if ($net==0)
        {
            $net=$Amount;
        }
	    
        $sql = "Select a.BID from Bill_inf a where a.BID=".$BID."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $sql="Update Bill_inf Set Amount=".$Amount.",Cash=".$Cash.",Card=".$Card.",Customer='".$Customer."',Mobile='".$Mobile."',Net=".$net.",Tax=".$tax." where BID=".$BID."";
            $check = $this->db->query($sql);	        
	        $result="Updated";
	        $Status=true;
        }
        else
	    {
	        $Status=false;
	        $result="Bill Not Exists";	        
	    }
        $arr = array (
            array(
              "Status" => $Status,
              "result"=>$result
            )
        );  
	    return $arr;
	}	
    public function add_ShopUsers($UserName,$Password,$ShopID,$UserType)
	{
	    $Status=false;
	    $result="";
        $sql = "Select a.UID from AdminUser a where a.UserName='".$UserName."' and a.ShopID=".$ShopID."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
	        $Status=true;
	        $result="Already Exists";
        }
        else
	    {
            $sql="INSERT INTO AdminUser(UserName,Password,ShopID,UserType) 
                VALUES ('". $UserName ."','".$Password."',".$ShopID.",'".$UserType."')";
            $check = $this->db->query($sql);	        
	        $result="Saved";
	        $Status=true;
	    }
        $arr = array (
            array(
              "Status" => $Status,
              "result"=>$result
            )
        );  
	    return $arr;
	}
	public function update_ShopUsers($UserID,$Password,$UserType)
	{
	    $Status=false;
	    $result="";
        $sql = "Select a.UID from AdminUser a where a.UID=".$UserID."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $sql="Update AdminUser Set Password='".$Password."',UserType='".$UserType."' where UID=".$UserID."";
            $check = $this->db->query($sql);	        
	        $result="Saved";
	        $Status=true;
        }
        else
	    {
	        $result="Not Exists";
	    }
        $arr = array (
            array(
              "Status" => $Status,
              "result"=>$result
            )
        );  
	    return $arr;
	}
    public function admin_dashboard()
    {
       $sql="Select count(*) as Companys,(Select Count(*) from Shops) as Shops
                        ,(Select Count(*) from Company where Status=1) as ActiveCompanies
                        ,(Select Count(*) from Shops where Status=1) as ActiveShops
                        ,(Select Sum(Amount) from Bill_inf) as BillValue
                        ,(Select count(*) from Bill_inf) as BillNos
                        from Company";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $ShopsNo=$row->Shops;
            $Companys=$row->Companys;
            $ActiveCompanies=$row->ActiveCompanies;
            $ActiveShops=$row->ActiveShops;
            $BillValue=$row->BillValue;
            $BillNos=$row->BillNos;
        }
	    $sql="Select a.ShopID,a.EntryDate,b.ShopName,b.Address,b.Contact from ShopActivity a inner join Shops b on a.ShopID=b.ShopID Group by(a.ShopID) ORDER by(a.EntryDate) DESC";
	    $ShopList=$this->db->query($sql)->result();
        $arr = array (
            array(
              "ShopsNo" => $ShopsNo,
              "Companys" => $Companys,
              "ActiveCompanies" => $ActiveCompanies,
              "ActiveShops" => $ActiveShops,
              "BillValue" => $BillValue,
              "BillNos" => $BillNos,
              "ShopsNo" => $ShopsNo,
              "ShopList" => $ShopList
            )
        );  
	    return $arr;
    }
	public function get_all_Items($ShopID)
	{
	    $sql="SELECT * FROM `Item_Master` WHERE ShopID=".$ShopID."";
	    return $this->db->query($sql)->result();
	}
	public function get_all_Chairs($ShopID)
	{
	    $sql="SELECT CounterID as ChairID,Type,Details FROM CounterDetails WHERE ShopID=".$ShopID."";
	    return $this->db->query($sql)->result();
	}
	public function owner_sales_report($UserID,$SDate,$EDate,$ShopName)
	{
	    $sql="Select a.BillNo,b.EntryDate,c.ShopName,d.Details as Chair,a.ItemName,a.Qty,a.Rate,a.Amount 
	            from Bill_Details a inner join Bill_inf b on a.BID=b.BID 
	            inner JOIN Shops c on b.ShopID=c.ShopID 
	            inner join CounterDetails d on b.CounterID=d.CounterID where b.EntryDate>='".$SDate."'and b.EntryDate<='".$EDate."'
	            and b.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.")";
	            
	           /* "Select a.BillNo,b.EntryDate,c.ShopName,d.Details as Chair,a.ItemName,a.Qty,a.Rate,a.Amount 
	               from Bill_Details a inner join Bill_inf b on a.BillNo=b.BillNo 
	            inner JOIN Shops c on b.ShopID=c.ShopID 
	            inner join CounterDetails d on b.CounterID=d.CounterID where b.EntryDate>='".$SDate."'and b.EntryDate<='".$EDate."'
	            and b.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.")";*/
	   if (trim($ShopName) != "")
	   {
	        $sql=$sql ." and c.ShopName='".$ShopName."'";   
	   }
	   return $this->db->query($sql)->result(); 
	}
	public function owner_allChairs($UserID)
	{
	    $sql="Select a.CounterID as ChairID,a.Details,a.Type,b.ShopName from CounterDetails a inner join Shops b on a.ShopID=b.ShopID where a.ShopID in (Select ShopID from OwnerShops where UserID=". $UserID .")";
	    return $this->db->query($sql)->result(); 
	}
	public function owner_allstaff($UserID)
	{
	    $sql="Select a.StaffID,a.StaffName,a.ShopID,b.ShopName from StaffMaster a inner join Shops b on a.ShopID=b.ShopID where a.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.")";
	    return $this->db->query($sql)->result(); 
	}
	public function owner_salessummary_report($UserID,$SDate,$EDate,$ShopName)
	{
	    $sql="Select b.BillNo,b.EntryDate,c.ShopName,d.Details as Chair,b.Amount,b.Cash,b.Card from Bill_inf b  
	            inner JOIN Shops c on b.ShopID=c.ShopID 
	            inner join CounterDetails d on b.CounterID=d.CounterID where b.EntryDate>='".$SDate."'and b.EntryDate<='".$EDate."'
	            and b.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.")";
	   if (trim($ShopName) != "")
	   {
	        $sql=$sql ." and c.ShopName='".$ShopName."'";   
	   }
	   return $this->db->query($sql)->result(); 
	}
	public function owner_expense_report($UserID,$SDate,$EDate,$ShopName)
	{
	   $sql="Select a.EntryNo,a.EntryDate,Coalesce((Select ItemName from PVRV_Details where RefNo=a.RefNo limit 1),'') as ItemName,a.Amount,a.Note,a.ShopID,b.ShopName from PVRV_inf a
	   inner join Shops b on a.ShopID=b.ShopID
	   where a.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.") and a.Amount>0
	   and a.EntryDate>='".$SDate."'and a.EntryDate<='".$EDate."'";
	   
	   //$sql="Select a.EntryNo,b.EntryDate,a.ItemName,a.Amount,a.Note,b.ShopID,c.ShopName from PVRV_Details a 
	   // inner join PVRV_inf b on a.EntryNo=b.EntryNo
	   // inner join Shops c on b.ShopID=c.ShopID
	   // where b.EntryDate>='".$SDate."'and b.EntryDate<='".$EDate."'and 
	   // b.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.")";
	   if (trim($ShopName) != "")
	   {
	        $sql=$sql ." and b.ShopName='".$ShopName."'";   
	   }
	   return $this->db->query($sql)->result(); 
	}
	public function owner_expensesummary_report($UserID,$SDate,$EDate,$ShopName)
	{
	    $sql="Select a.EntryDate,a.ShopName,Sum(a.Amount) as Amount FROM
                    (
                    Select b.EntryNo,b.EntryDate,b.Amount,b.Note,b.ShopID,c.ShopName from PVRV_inf b inner join Shops c on b.ShopID=c.ShopID 
	            where b.EntryDate>='".$SDate."'and b.EntryDate<='".$EDate."' 
	            and b.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.") and b.Amount>0"; 
	   if (trim($ShopName) != "")
	   {
	        $sql=$sql ." and c.ShopName='".$ShopName."'";   
	   }
	   $sql=$sql ." ) a GROUP by(a.EntryDate),(a.ShopName) order by(a.EntryDate)";
	   return $this->db->query($sql)->result(); 
	}
	public function owner_staff_report($UserID,$SDate,$EDate,$Staff)
	{
	     $sql="Select a.BillNo,a.EntryDate,b.StaffName,c.ShopName,a.Amount from Bill_inf a inner join StaffMaster b on a.StaffID=b.StaffID INNER join Shops c on a.ShopID=c.ShopID
                where a.EntryDate>='".$SDate."'and a.EntryDate<='".$EDate."' and a.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.")"; 
	   if (trim($Staff) != "")
	   {
	        $sql=$sql ." and a.StaffName='".$Staff."'";   
	   }
	   return $this->db->query($sql)->result(); 
	}
	public function owner_staffsummary_report($UserID,$SDate,$EDate,$Staff)
	{
         $sql="Select a.* from (Select a.StaffName,a.ShopID,b.ShopName,
                Coalesce((Select Sum(Amount) from Bill_inf where StaffID=a.StaffID and ShopID=a.ShopID and EntryDate>='".$SDate."'and EntryDate<='".$EDate."'),0) as Bill from StaffMaster a 
                inner join Shops b on a.ShopID=b.ShopID 
                where 
                 a.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.")"; 
	   if (trim($Staff) != "")
	   {
	        $sql=$sql ." and a.StaffName='".$Staff."'";   
	   }
	   $sql=$sql .") a where a.Bill>0";   
	   return $this->db->query($sql)->result(); 	    
	}
	
	public function owner_chair_report($UserID,$SDate,$EDate,$chair)
	{
	     $sql="Select a.BillNo,a.EntryDate,b.ShopName,c.Details,a.Amount from Bill_inf a inner join Shops b on a.ShopID=b.ShopID inner JOIN CounterDetails c on a.CounterID=c.CounterID
	     where a.EntryDate>='".$SDate."'and a.EntryDate<='".$EDate."' and a.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.")"; 
	   if (trim($chair) != "")
	   {
	        $sql=$sql ." and c.CounterID=".$chair."";   
	   }
	   return $this->db->query($sql)->result(); 
	}
	public function owner_chairsummary_report($UserID,$SDate,$EDate,$chair)
	{
	   $sql="Select a.* from (Select a.CounterID,b.ShopName,a.Details as Chair 
	    ,COALESCE((Select Sum(Amount) from Bill_inf where ShopID=a.ShopID and CounterID=a.CounterID and EntryDate>='".$SDate."'and EntryDate<='".$EDate."'),0) as Amount
	    from CounterDetails a inner join Shops b on a.ShopID=b.ShopID 
	    where a.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.")"; 
	   if (trim($chair) != null)
	   {
	        $sql=$sql ." and a.CounterID=".$chair."";   
	   }
	   $sql=$sql ." ) a where a.Amount>0";   
	   
	   return $this->db->query($sql)->result(); 
	}
	public function activity_report($UserID,$SDate,$EDate,$ShopName,$Staff)
	{
	    
	    $sql="Select a.*,TIMESTAMPDIFF(HOUR, StartTime, EndTime) as Hrs from
                        (Select a.*,Coalesce(b.ShopName,'') as ShopName,Coalesce(c.StaffName,'') as StaffName,(Select EntryTime from ShopActivity where EntryDate=a.EntryDate and StaffID=a.StaffID order by(UID) Asc limit 1) as StartTime,(Select EntryTime from ShopActivity where EntryDate=a.EntryDate and StaffID=a.StaffID order by(UID) Desc limit 1) as EndTime
                        ,Coalesce((Select Sum(Amount) from Bill_inf where EntryDate=a.EntryDate and StaffID=a.StaffID),0) as Amount
                        ,Coalesce((Select Sum(Cash) from Bill_inf where EntryDate=a.EntryDate and StaffID=a.StaffID),0) as Cash
                        ,Coalesce((Select Sum(Card) from Bill_inf where EntryDate=a.EntryDate and StaffID=a.StaffID),0) as Card
                        ,Coalesce((Select Count(*) from Bill_inf where EntryDate=a.EntryDate and StaffID=a.StaffID),0) as Bills
                        FROM
                        (
                        Select a.ShopID,a.StaffID,a.EntryDate from ShopActivity  a where a.StaffID>0 and a.EntryDate>='".$SDate."'and a.EntryDate<='".$EDate."' Group by(a.ShopID),(a.StaffID),(a.EntryDate) 
                        ) a left join Shops b on a.ShopID=b.ShopID left join StaffMaster c on a.StaffID=c.StaffID  order by(a.EntryDate)
                         ) a  where a.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.")";
        if (trim($ShopName) != "")
	    {
	        $sql=$sql ." and a.ShopName='".$ShopName."'";   
	    }
        if (trim($Staff) != "")
	    {
	        $sql=$sql ." and a.StaffName='".$Staff."'";   
	    }                         
	   // $sql="Select a.* FROM
    //             (
    //             Select a.EntryDate,a.EntryTime,a.ShopID,a.StaffID,b.StaffName,c.ShopName,d.Details as Chair,'ACTIVE' as Status FROM
    //             (
    //             SELECT a.EntryDate,a.EntryTime,a.ShopID,a.StaffID,a.CounterID FROM `ShopActivity` a where a.Status=1 and a.Note='ACTIVE' and a.EntryDate>='".$SDate."'and a.EntryDate<='".$EDate."' GROUP by(a.EntryDate),(a.ShopID),(a.StaffID)
    //                 ) a inner join StaffMaster b on a.StaffID=b.StaffID inner join Shops c on a.ShopID=c.ShopID inner join CounterDetails d on a.CounterID=d.CounterID GROUP by(a.EntryDate) 
                
    //             Union All
                
    //             Select a.EntryDate,a.EntryTime,a.ShopID,a.StaffID,b.StaffName,c.ShopName,d.Details as Chair,'CLOSING' as Status FROM
    //             (
    //             SELECT a.EntryDate,a.EntryTime,a.ShopID,a.StaffID,a.CounterID FROM `ShopActivity` a where a.Status=0 and a.Note='CLOSING' and a.EntryDate>='".$SDate."'and a.EntryDate<='".$EDate."' GROUP by(a.EntryDate),(a.ShopID),(a.StaffID)
    //                 ) a inner join StaffMaster b on a.StaffID=b.StaffID inner join Shops c on a.ShopID=c.ShopID inner join CounterDetails d on a.CounterID=d.CounterID GROUP by(a.EntryDate) 
    //         ) a where a.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.") ";
    //     if (trim($ShopName) != "")
	   // {
	   //     $sql=$sql ." and a.ShopName='".$ShopName."'";   
	   // }
    //     if (trim($Staff) != "")
	   // {
	   //     $sql=$sql ." and a.StaffName='".$Staff."'";   
	   // }
    //     $sql= $sql; ." order by(a.EntryDate),(a.Status) ";   
	   return $this->db->query($sql)->result();
	    //return $sql;
	}
	public function owner_Shops($UserID)
	{
	    $sql="Select b.ShopName,b.ShopID,b.Address,b.CompanyID,c.CompanyName,b.Contact from OwnerShops a inner JOIN Shops b on a.ShopID=b.ShopID inner join Company c on b.CompanyID=c.CompanyID where UserID=".$UserID."";
	    return $this->db->query($sql)->result();
	}
	public function get_owners_ShopBills($UserID,$EntryDate,$ShopName,$StaffName)
	{
	    $sql="Select a.BiD,a.BillNo,a.EntryDate,a.EntryTime,a.Net,a.Tax,a.Amount,a.Cash,a.Card,a.ShopID,b.ShopName,a.CounterID as ChairID,d.Details as ChairName,a.StaffID,c.StaffName,b.Tax as TaxPercent from Bill_inf a inner join Shops b on a.ShopID=b.ShopID inner join StaffMaster c on a.StaffID=c.StaffID inner join CounterDetails d on a.CounterID=d.CounterID
                where a.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.") ";
        if ($EntryDate != null)
        {
            $sql=$sql . " and a.EntryDate='". $EntryDate ."'";
        }
        if ($ShopName != null)
        {
            $sql=$sql . " and b.ShopName='". $ShopName ."'";
        }
        if ($StaffName != null)
        {
            $sql=$sql . " and c.StaffName='". $StaffName ."'";
        }        
        $sql=$sql . " order by(BID)";
	    return $this->db->query($sql)->result();
	}
	public function owner_ChairStatus($UserID)
	{
	            $ContryCode="";
	            $sql="Select ContryCode from Shops where ShopID in (Select a.ShopID from OwnerShops a where UserID=".$UserID.") Limit 1";
                $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                    $ContryCode=$row->ContryCode;
                }
                
                $timezone="";
                date_default_timezone_set('Asia/Kolkata');
                if ($ContryCode=='+91')
                {
                    date_default_timezone_set('Asia/Kolkata');
	                $timezone="Culcutta";
                }
                else if ($ContryCode=='+971')
                {
                    date_default_timezone_set('Asia/Dubai');
	                $timezone="Dubai";
                }
                else if ($ContryCode=='+966')
                {
	                date_default_timezone_set('Asia/Riyadh');
                    $timezone="Riyadh";
                }
                else if ($ContryCode=='+965')
                {
                    date_default_timezone_set('Asia/Kuwait');
	                $timezone="Kuwait";
                }
                else
                {
                    date_default_timezone_set('Asia/Kolkata');
                    $timezone="Kolkata";
	            }	    
                $currentTime=date('H:i:s');           
    	        $currentDate=date("Y/m/d");
                //Added by Illiyas
               
                $startTime = new DateTime('00:01:00');
                $endTime = new DateTime('04:30:00');
                if ($currentTime >= $startTime->format('H:i:s') && $currentTime <= $endTime->format('H:i:s')) {
                    
                    // Create a DateTime object using the current date
                $dateTime = new DateTime($currentDate);
                $dateTime->modify('-1 day');
                
                $currentDate = $dateTime->format('Y/m/d');
                }
                
                //abouve codes added by illiyas
                $date = $currentDate;
	            $sql="Select a.ShopID,Coalesce((Select ShopName from Shops where ShopID=a.ShopID),'') as ShopName,a.CounterID,a.Details,a.CompanyID,a.UID,a.EntryDate,TIME_FORMAT(a.EntryTime, '%r') as EntryTime,TIME_FORMAT(Coalesce((Select EntryTime from ShopActivity where CounterID=a.CounterID and Status=1 and Note='BILL' and EntryDate='".$date."' order by(UID) Desc limit 1),''), '%r') as LastWorkTime,a.Status
                    ,(CASE
                        WHEN a.Status = 0 THEN 0
                        ELSE a.StaffID
                    END
                     ) as StaffID
                    ,(CASE
                        WHEN a.Status = 0 THEN ''
                        ELSE a.StaffName
                    	END
                     ) as StaffName
                    from
                    (
                    		Select a.*,COALESCE(b.EntryDate,'01-01-2000') as EntryDate,Coalesce(b.EntryTime,'0000-00-00') as EntryTime,Coalesce(b.Status,0) as Status,Coalesce(b.StaffID,0) as StaffID,Coalesce((Select StaffName from StaffMaster where StaffID=b.StaffID),'') as StaffName from 
                                    (
                                    	SELECT a.ShopID,a.CounterID,a.Details,b.CompanyID,Coalesce((Select UID from ShopActivity where CounterID=a.CounterID and EntryDate='".$date."' order by(UID) Desc LIMIT 1),0) as UID from CounterDetails a inner join Shops b on a.ShopID=b.ShopID where b.ShopID in (Select ShopID from OwnerShops where UserID=".$UserID.")
                                    ) a LEFT join ShopActivity b on a.UID=b.UID
                    ) a";
	    return $this->db->query($sql)->result();                
                
        //         date_default_timezone_set('Asia/Kolkata');
        //         if ($ContryCode=='+91')
        //         {
        //             date_default_timezone_set('Asia/Kolkata');
	       //         $timezone="Culcutta";
        //         }
        //         else if ($ContryCode=='+971')
        //         {
        //             date_default_timezone_set('Asia/Dubai');
	       //         $timezone="Dubai";
        //         }
        //         else if ($ContryCode=='+966')
        //         {
	       //         date_default_timezone_set('Asia/Riyadh');
        //             $timezone="Riyadh";
        //         }
        //         else if ($ContryCode=='+965')
        //         {
        //             date_default_timezone_set('Asia/Kuwait');
	       //         $timezone="Kuwait";
        //         }
        //         else
        //         {
        //             date_default_timezone_set('Asia/Kolkata');
        //             $timezone="Kolkata";
	       //     }	  
        //     $currentTime=date('H:i:s');           
    	   // $currentDate=date("Y/m/d");
        //     $date = $currentDate;

    	   // $sql="Select a.*,Coalesce((Select StaffMaster.StaffName from ShopActivity inner join StaffMaster on ShopActivity.StaffID=StaffMaster.StaffID where ShopActivity.UID=a.UID),'') as StaffName,(
        //                 case when b.Status=0 then 'IN-ACTIVE'
        //                     when b.Status=1 then 'ACTIVE'
        //                     when b.Status=2 then 'WORKING'
        //                     ELSE 'IN-ACTIVE'
        //                     END
        //                 ) as Status
        //                 FROM
        //                 (
        //                         Select a.CounterID,a.Details,a.ShopID,b.ShopName
        //                         ,Coalesce((Select UID from ShopActivity where CounterID=a.CounterID and EntryDate='".$date."' order by(UID) Desc LIMIT 1),0) as UID
        //                         from CounterDetails a inner join Shops b on a.ShopID=b.ShopID where a.ShopID in (Select ShopID from OwnerShops where UserID=". $UserID .")
        //                 ) a left Join ShopActivity b on a.UID=b.UID";
        //                 //echo $sql;
	   // return $this->db->query($sql)->result();
	}
	
	public function owner_login($username,$password)
	{
	    $arr="";   
        $status=false;
        $result="";
        $UserType="";
        $Shops="";
        $UserID=0;
        $Graph="";
	    $sql = "Select a.UserName,a.Password,a.UserType,a.UID from AdminUser a where a.UserName='".$username."' and a.UserType='OWNER'";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $Password=$row->Password;
            $UserType=$row->UserType;
            $UserName=$row->UserName;
            $UserID=$row->UID;
            if (trim($Password)==trim($password))
            {
                $status=true;
                $result="ok";
                $ContryCode="";
                $sql="Select ContryCode from Shops where ShopID in (Select a.ShopID from OwnerShops a where UserID=".$UserID.") Limit 1";
                $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                    $ContryCode=$row->ContryCode;
                }
                date_default_timezone_set('Asia/Kolkata');
                if ($ContryCode=='+91')
                {
                    date_default_timezone_set('Asia/Kolkata');
	                $timezone="Culcutta";
                }
                else if ($ContryCode=='+971')
                {
                    date_default_timezone_set('Asia/Dubai');
	                $timezone="Dubai";
                }
                else if ($ContryCode=='+966')
                {
	                date_default_timezone_set('Asia/Riyadh');
                    $timezone="Riyadh";
                }
                else if ($ContryCode=='+965')
                {
                    date_default_timezone_set('Asia/Kuwait');
	                $timezone="Kuwait";
                }
                else
                {
                    date_default_timezone_set('Asia/Kolkata');
                    $timezone="Kolkata";
	            }	    
                $currentTime=date('H:i:s');           
    	        $currentDate=date("Y/m/d");                

                $date1 = date('Y-m-d');
                
                
                  $startTime = new DateTime('00:01:00');
            $endTime = new DateTime('04:30:00');
            
            if ($currentTime >= $startTime->format('H:i:s') && $currentTime <= $endTime->format('H:i:s')) 
            {
    
                // Create a DateTime object using the current date
                $dateTime = new DateTime($currentDate);
                $dateTime->modify('-1 day');

                $currentDate = $dateTime->format('Y/m/d');

                $date1= $dateTime->format('Y-m-d');
    
            }
            
                
                
                
                
                $sql="Select a.ShopID,b.ShopName,c.CompanyName
                        ,Coalesce((Select Sum(Amount) from Bill_inf where ShopID=a.ShopID and EntryDate='".$date1."'),0) as Bill
                        ,Coalesce((Select Sum(Cash) from Bill_inf where ShopID=a.ShopID and EntryDate='".$date1."'),0) as Cash
                        ,Coalesce((Select Sum(Card) from Bill_inf where ShopID=a.ShopID and EntryDate='".$date1."'),0) as Card
                        ,Coalesce((Select Count(*) from Bill_inf where ShopID=a.ShopID and EntryDate='".$date1."'),0) as BillCount
                        ,Coalesce((Select Sum(Amount) from PVRV_inf where ShopID=a.ShopID and EntryDate='".$date1."'),0) as Expenses
                        from OwnerShops a inner JOIN Shops b on a.ShopID=b.ShopID inner join Company c on b.CompanyID=c.CompanyID where a.UserID=".$UserID."";
                $Shops=$this->db->query($sql)->result();   
                
                $date2 = date('Y-m-d', strtotime($date1. '-1 days'));
                $date3 = date('Y-m-d', strtotime($date1. '-2 days'));
                $date4 = date('Y-m-d', strtotime($date1. '-3 days'));
                $date5 = date('Y-m-d', strtotime($date1. '-4 days'));
                $date6 = date('Y-m-d', strtotime($date1. '-5 days'));
                $date7 = date('Y-m-d', strtotime($date1. '-6 days'));
                // echo $date1;
                // echo $date2;
                // echo $date3;
                // echo $date4;
                // echo $date5;
                // echo $date6;
                $sql="Select COALESCE(Sum(Amount),0) as Day1
                ,(Select COALESCE(Sum(Amount),0) as Day1 from Bill_inf where ShopID in (Select a.ShopID from OwnerShops a where UserID=".$UserID.") and EntryDate='".$date6."') Day2
                ,(Select COALESCE(Sum(Amount),0) as Day1 from Bill_inf where ShopID in (Select a.ShopID from OwnerShops a where UserID=".$UserID.") and EntryDate='".$date5."') Day3
                ,(Select COALESCE(Sum(Amount),0) as Day1 from Bill_inf where ShopID in (Select a.ShopID from OwnerShops a where UserID=".$UserID.") and EntryDate='".$date4."') Day4
                ,(Select COALESCE(Sum(Amount),0) as Day1 from Bill_inf where ShopID in (Select a.ShopID from OwnerShops a where UserID=".$UserID.") and EntryDate='".$date3."') Day5
                ,(Select COALESCE(Sum(Amount),0) as Day1 from Bill_inf where ShopID in (Select a.ShopID from OwnerShops a where UserID=".$UserID.") and EntryDate='".$date2."') Day6
                ,(Select COALESCE(Sum(Amount),0) as Day1 from Bill_inf where ShopID in (Select a.ShopID from OwnerShops a where UserID=".$UserID.") and EntryDate='".$date1."') Day7
                                    from Bill_inf where ShopID in (Select a.ShopID from OwnerShops a where UserID=".$UserID.") and EntryDate='".$date7."'";
                                    
            //echo $sql;                                    
               $Graph=$this->db->query($sql)->result();   
                
            }
            else
            {
                $status=false;
                $result="Invalid Password";
            }
        }
	    else
        {
            $status=false;
            $result="Invalid UserName";
        }
        $arr = array (
            array(
              "status" => $status,
              "username" => $username,
              "UserID" => $UserID,
              "result" => $result,
              "UserType" => $UserType,
              "Graph" => $Graph,
              "Shops" => $Shops
            )
        );
	    return $arr;
	}
	public function admin_login($username,$password)
	{
	    $arr="";   
	    $sql = "Select a.UserName,a.Password,a.UserType from AdminUser a where a.UserName='".$username."'";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $Password=$row->Password;
            $UserType=$row->UserType;
            $UserName=$row->UserName;
            if (trim($Password)==trim($password))
            {
                $status=true;
                $result="ok";
                $arr = array (
                      array(
                          "status" => $status,
                          "username" => $UserName,
                          "result" => $result,
                          "UserType" => $UserType
                      )
                );
            }
            else
            {
                $status=false;
                $result="Invalid Password";
                $arr = array (
                      array(
                          "status" => $status,
                          "username" => $UserName,
                          "result" => $result
                      )
                );  
            }
        }
	    else
        {
            $status=false;
            $result="Invalid UserName";
            $arr = array (
                  array(
                      "status" => $status,
                       "username" => $UserName,
                       "result" => $result
                  )
            );
        }
	    return $arr;
	}
    public function get_staff_dashbord($ShopID)
    {
        $ContryCode="";
        $sql = "Select a.ContryCode from Shops a where a.ShopID=".$ShopID."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
             $ContryCode=$row->ContryCode;
        }
         $timezone="";
                date_default_timezone_set('Asia/Kolkata');
                if ($ContryCode=='+91')
                {
                    date_default_timezone_set('Asia/Kolkata');
	                $timezone="Culcutta";
                }
                else if ($ContryCode=='+971')
                {
                    date_default_timezone_set('Asia/Dubai');
	                $timezone="Dubai";
                }
                else if ($ContryCode=='+966')
                {
	                date_default_timezone_set('Asia/Riyadh');
                    $timezone="Riyadh";
                }
                else if ($ContryCode=='+965')
                {
                    date_default_timezone_set('Asia/Kuwait');
	                $timezone="Kuwait";
                }
                else
                {
                    date_default_timezone_set('Asia/Kolkata');
                    $timezone="Kolkata";
	            }	    
                $currentTime=date('H:i:s');           
    	        $currentDate=date("Y/m/d");

               //added by Illiyas
                
                $startTime = new DateTime('00:01:00');
                $endTime = new DateTime('04:30:00');
                if ($currentTime >= $startTime->format('H:i:s') && $currentTime <= $endTime->format('H:i:s')) {
                    
                    // Create a DateTime object using the current date
                $dateTime = new DateTime($currentDate);
                $dateTime->modify('-1 day');
                
                $currentDate = $dateTime->format('Y/m/d');
                }
                
                //the above code added by illiyas
                 $date = $currentDate;

        $imageURLDefault="https://api.ibill.ae/images/";
        $sql="SELECT a.StaffID,a.StaffName,a.Address,a.Mobile,a.Email,a.DOB,a.DOJ,a.Salary,a.Department,CONCAT('".$imageURLDefault."',a.Img) as FileName,a.Img,Coalesce((SELECT Sum(Amount) FROM Bill_inf where ShopID=". $ShopID ." and EntryDate='".$date."' and StaffID=a.StaffID),0) as Sales
            ,Coalesce((Select Status from ShopActivity where StaffID=a.StaffID and EntryDate='".$date."' order by(UID) Desc Limit 1),0) as WorkStatus
            FROM StaffMaster a where  a.ShopID=". $ShopID ."";
         return $this->db->query($sql)->result();
    }
	public function check_login($username,$password)
	{
	    $imageURLDefault="https://api.ibill.ae/images/";
	    $db_data = array();
	    $arr="";   
	    $sql = "Select a.ShopID,a.ShopName,a.CompanyID,a.Address,a.Contact,a.Email,a.RegnNo,a.PinCode,a.latitude,a.longitude,a.StartTime,a.ClosingTime,a.HolyDays,b.Password as Pwd,a.CurencyName,a.ContryCode,b.UserType,a.Tax from Shops a inner join AdminUser b on a.ShopID=b.ShopID where b.UserName='".$username."' and (b.UserType='SHOP-ADMIN' or  b.UserType='SHOP-USER')";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $pwd=$row->Pwd;
            $ShopID=$row->ShopID;
            $CompanyID=$row->CompanyID;
            $ShopName=$row->ShopName;
            $Address=$row->Address;
            $Contact=$row->Contact;
            $Email=$row->Email;
            $RegnNo=$row->RegnNo;
            $ContryCode=$row->ContryCode;
            $CurencyName=$row->CurencyName;
            $UserType=$row->UserType;
            $Tax=$row->Tax;
            if (trim($pwd)==trim($password))
            {
                $status=true;
                $result="ok";
                $timezone="";
                date_default_timezone_set('Asia/Kolkata');
                if ($ContryCode=='+91')
                {
                    date_default_timezone_set('Asia/Kolkata');
	                $timezone="Culcutta";
                }
                else if ($ContryCode=='+971')
                {
                    date_default_timezone_set('Asia/Dubai');
	                $timezone="Dubai";
                }
                else if ($ContryCode=='+966')
                {
	                date_default_timezone_set('Asia/Riyadh');
                    $timezone="Riyadh";
                }
                else if ($ContryCode=='+965')
                {
                    date_default_timezone_set('Asia/Kuwait');
	                $timezone="Kuwait";
                }
                else
                {
                    date_default_timezone_set('Asia/Kolkata');
                    $timezone="Kolkata";
	            }	    
                $currentTime=date('H:i:s');           
    	        $currentDate=date("Y/m/d");
    	        
    	        //added by Illiyas
                
                $startTime = new DateTime('00:01:00');
                $endTime = new DateTime('04:30:00');
                if ($currentTime >= $startTime->format('H:i:s') && $currentTime <= $endTime->format('H:i:s')) {
                    
                    // Create a DateTime object using the current date
                $dateTime = new DateTime($currentDate);
                $dateTime->modify('-1 day');
                
                $currentDate = $dateTime->format('Y/m/d');
                }
                
                //the above code added by illiyas

                $date = $currentDate;

                $sql="Select a.CounterID,a.Type,a.Details,Coalesce((Select EntryTime from ShopActivity where CounterID=a.CounterID and Status=1 and Note='BILL' and EntryDate='".$date."' order by(UID) Desc limit 1),'') as LastWorkTime from CounterDetails a where a.ShopID=". $ShopID ."";
                $Chairs=$this->db->query($sql)->result();
                
                //$sql="SELECT StaffID,StaffName,Address,Mobile,Email,DOB,DOJ,Salary,Department,Img,0 as Sales FROM StaffMaster WHERE ShopID=". $ShopID ."";
                $sql="SELECT a.StaffID,a.StaffName,a.Address,a.Mobile,a.Email,a.DOB,a.DOJ,a.Salary,a.Department,a.Img,Coalesce((SELECT Sum(Amount) FROM Bill_inf where ShopID=". $ShopID ." and EntryDate='".$date."' and StaffID=a.StaffID),0) as Sales
                            ,Coalesce((Select Status from ShopActivity where StaffID=a.StaffID and EntryDate='".$date."' order by(UID) Desc Limit 1),0) as WorkStatus
                            FROM StaffMaster a where  a.ShopID=". $ShopID ."";
                $Staffs=$this->db->query($sql)->result();
                $sql="Select a.ProofID,a.ProofName,a.ProofNo,a.ProofDate,a.ExpDate,a.Img,a.StaffID,'".$imageURLDefault."' as URL,CONCAT('".$imageURLDefault."',a.Img) as FileName from ProofDetails a INNER join StaffMaster b on a.StaffID=b.StaffID where b.ShopID=". $ShopID ."";
                $Proffs=$this->db->query($sql)->result();
                
                $sql="SELECT url FROM Images where CompanyID=".$CompanyID."";
                $Images=$this->db->query($sql)->result();

                $sql="SELECT ItemID,Itemname,Rate,Tax FROM Item_Master WHERE ShopID=".$ShopID."";
                $Items=$this->db->query($sql)->result();
                $SalesNos=0;
                $SalesAmt=0;
                $Card=0;
                $Cash=0;
                $Expenses=0;
                $sql="SELECT Coalesce(Sum(Amount),0) as Amount
                         ,Coalesce((Select Count(*) from Bill_inf where ShopID=".$ShopID." and EntryDate='".$date."'),0) as SalesNos
                         ,Coalesce((Select Sum(Cash) from Bill_inf where ShopID=".$ShopID." and EntryDate='".$date."'),0) as Cash
                         ,Coalesce((Select Sum(Card) from Bill_inf where ShopID=".$ShopID." and EntryDate='".$date."'),0) as Card
                         ,Coalesce((Select Sum(Amount) from PVRV_inf where ShopID=".$ShopID." and EntryDate='".$date."'),0) as Expenses
                         FROM `Bill_inf` where ShopID=".$ShopID." and EntryDate='".$date."'";
                $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                    $SalesAmt=$row->Amount;
                    $SalesNos=$row->SalesNos;
                    $SalesAmt=$row->Amount; 
                    $Cash=$row->Cash; 
                    $Card=$row->Card; 
                    $Expenses=$row->Expenses; 
                }
                        
                $arr = array (
                      array(
                          "status" => $status,
                          "ShopID" => $ShopID,
                          "ShopName" => $ShopName,
                          "UserType" => $UserType,
                          "CompanyID" =>$CompanyID,
                          "Address" => $Address,
                          "Contact" => $Contact,
                          "Email" => $Email,
                          "RegnNo" => $RegnNo,
                          "SalesNos" => $SalesNos,
                          "SalesAmt" => $SalesAmt,
                          "Cash" => $Cash,
                          "Bank" => $Card,
                          "Expenses" => $Expenses,
                          "result" => $result,
                          "Chairs" => $Chairs,
                          "Staffs" => $Staffs,
                          "Proofs" => $Proffs,
                          "Images" => $Images,
                          "CurencyName" => $CurencyName,
                          "ContryCode" => $ContryCode,
                          "Tax" => $Tax,
                          "Items" => $Items
                      )
                );
            }
            else
            {
                $status=false;
                $result="Invalid Password";
                $arr = array (
                      array(
                          "status" => $status,
                          "result" => $result
                      )
                );
            }
        }
        else
        {
            $status=false;
            $result="Invalid UserName";
            $arr = array (
                  array(
                      "status" => $status,
                      "result" => $result
                  )
            );
        }
	    return $arr;
	}
	public function add_OwnersAccount($UserName,$Password)
	{
        $status=false;
        $result="";
        $sql="INSERT INTO AdminUser(UserName,Password,UserType) 
        VALUES ('". $UserName ."','".$Password."','OWNER')";
        $check = $this->db->query($sql);
        if($check)
        {
            $status=true;
            $result="OK";
        }
        $arr = array (
            array(
                  "status" => $status,
                  "result" => $result
            )
        );
	    return $arr;            
	}
	public function delete_OwnersAccount($UserName)
	{
        $status=false;
        $result="";
        $sql="Delete from AdminUser where UserName='". $UserName ."' and UserType='OWNER'";
        $check = $this->db->query($sql);
        if($check)
        {
            $status=true;
            $result="OK";
        }
        $arr = array (
            array(
                  "status" => $status,
                  "result" => $result
            )
        );
	    return $arr;            
	}
	public function TimeSheet_report($EntryDate,$StaffID)
	{
        $sql="Select a.EntryDate,a.StaffID,Coalesce((Select StaffName from StaffMaster where StaffID=a.StaffID limit 1),'') StaffName,a.ShopID,Coalesce((Select ShopName from Shops where ShopID=a.ShopID limit 1),'') as ShopName,TIME_FORMAT(a.EntryTime, '%r') as CheckInTime,TIME_FORMAT(Coalesce((Select EntryTime from ShopActivity where BillNo=a.BillNo and StaffID=a.StaffID and ShopID=a.ShopID limit 1),''),'%r') as CheckoutTime,a.Chair,a.BillNo,Coalesce(b.Amount,0) as Amount,Coalesce(b.Cash,0) as Cash,Coalesce(b.Card,0) as Card FROM
            (
            SELECT a.EntryDate,a.StaffID,a.ShopID,a.EntryTime,a.CounterID,Coalesce(b.Details,'') as Chair
            ,(Select BillNo from ShopActivity where StaffID=a.StaffID and Status=1 and Note='BILL' and UID >a.UID limit 1) as BillNo
            ,a.Note,a.Status,a.UID FROM `ShopActivity` a left join CounterDetails b on a.CounterID=b.CounterID WHERE a.StaffID=".$StaffID." and a.Status=2 and a.EntryDate='". $EntryDate ."'
                ) a left join Bill_inf b on a.BillNo=b.BID and a.ShopID=b.ShopID";
        //$sql="Select * from ShopActivity";
	    return $this->db->query($sql)->result();
	}
	
	
	public function TimeSheet_reportB($EntryDate,$StaffID)
	{
       
       $sql="Select a.EntryDate,a.StaffID,Coalesce((Select StaffName from StaffMaster where StaffID=a.StaffID),'') StaffName,a.ShopID,Coalesce((Select ShopName from Shops where ShopID=a.ShopID),'') as ShopName,TIME_FORMAT(a.EntryTime, '%r') as CheckInTime,TIME_FORMAT(Coalesce((Select EntryTime from ShopActivity where BillNo=a.BillNo and StaffID=a.StaffID and ShopID=a.ShopID limit 1 ),''),'%r') as CheckoutTime,a.Chair,a.BillNo,Coalesce(b.Amount,0) as Amount,Coalesce(b.Cash,0) as Cash,Coalesce(b.Card,0) as Card FROM
            (
            SELECT a.EntryDate,a.StaffID,a.ShopID,a.EntryTime,a.CounterID,Coalesce(b.Details,'') as Chair
            ,(Select BillNo from ShopActivity where StaffID=a.StaffID and Status=1 and Note='BILL' and UID >a.UID limit 1) as BillNo
            ,a.Note,a.Status,a.UID FROM `ShopActivity` a left join CounterDetails b on a.CounterID=b.CounterID WHERE a.StaffID=".$StaffID." and a.Status=2 and a.EntryDate='". $EntryDate ."'
                ) a left join Bill_inf b on a.BillNo=b.BID and a.ShopID=b.ShopID"; 
       
      /* $sql="Select a.EntryDate,a.StaffID,Coalesce((Select StaffName from StaffMaster where StaffID=a.StaffID),'') StaffName,a.ShopID,Coalesce((Select ShopName from Shops where ShopID=a.ShopID),'') as ShopName,TIME_FORMAT(a.EntryTime, '%r') as CheckInTime,TIME_FORMAT(Coalesce((Select EntryTime from ShopActivity where BillNo=a.BillNo and StaffID=a.StaffID and ShopID=a.ShopID),''),'%r') as CheckoutTime,a.Chair,a.BillNo,Coalesce(b.Amount,0) as Amount,Coalesce(b.Cash,0) as Cash,Coalesce(b.Card,0) as Card FROM
            (
            SELECT a.EntryDate,a.StaffID,a.ShopID,a.EntryTime,a.CounterID,Coalesce(b.Details,'') as Chair
            ,(Select Bill_inf.BillNo from Bill_inf,ShopActivity where Bill_inf.BID = ShopActivity.BillNo and ShopActivity.StaffID=a.StaffID and Status=1 and Note='BILL' and UID >a.UID limit 1) as BillNo
            ,a.Note,a.Status,a.UID FROM `ShopActivity` a left join CounterDetails b on a.CounterID=b.CounterID WHERE a.StaffID=".$StaffID." and a.Status=2 and a.EntryDate='". $EntryDate ."'
                ) a left join Bill_inf b on a.BillNo=b.BID and a.ShopID=b.ShopID";
                */
                
       $result = $this->db->query($sql)->result();
       
       $filteredResult = array();
       
       $temp_array = array();
              
            
       
       foreach ($result as $row)
       {
           
          $valueToCheck= $row->BillNo;
           if (!in_array($valueToCheck,  $temp_array))
           {
                $filteredResult[] = $row;
                  $temp_array[]=$valueToCheck;
           }
           
             
           
           
       }
       
	  return $filteredResult;
	}
	
	
	public function billing_report($ShopID,$SDate,$EDate,$StaffID)
	{
	   // $sql="Select a.BillNo,a.BID,a.ItemName,a.Qty,a.Amount,b.EntryDate from Bill_Details a inner join Bill_inf b on a.BillNo=b.BillNo where b.ShopID=".$ShopID."  and b.EntryDate>='".$SDate."' and b.EntryDate<='".$EDate."'";
	    $sql="Select a.BillNo,a.BID,a.ItemName,a.Qty,a.Amount,b.EntryDate from Bill_Details a inner join Bill_inf b on a.BillNo=b.BillNo where b.ShopID=".$ShopID." and a.BID=b.BID and b.EntryDate>='".$SDate."' and b.EntryDate<='".$EDate."'";
	    if ($StaffID != null)
	    {
	        if ($StaffID !=0)
	        {
	           $sql = $sql . " and b.StaffID=".$StaffID.""; 
	        }
	    }
	    return $this->db->query($sql)->result();
	}
	public function expenses_report($ShopID,$SDate,$EDate)
	{
	    $sql="Select a.EntryNo,b.EntryDate,a.ItemName,a.Amount from PVRV_Details a inner join PVRV_inf b on a.RefNo=b.RefNo where b.ShopID=".$ShopID." and b.EntryDate>='".$SDate."' and EntryDate<='".$EDate."'";
	    return $this->db->query($sql)->result();
	}
	public function staff_report($ShopID,$SDate,$EDate,$StaffID)
	{
	    $sql="Select a.StaffID,a.StaffName,
                Coalesce((Select Sum(Amount) from Bill_inf where StaffID=a.StaffID and EntryDate>='".$SDate."' and EntryDate<='".$EDate."'),0) as Amount
                ,Coalesce((Select count(*) from Bill_inf where StaffID=a.StaffID and EntryDate>='".$SDate."' and EntryDate<='".$EDate."'),0) as BillCount from StaffMaster a where ShopID=".$ShopID."";
	    if ($StaffID != null)
	    {
	        if ($StaffID !=0)
	        {
	           $sql = $sql . " and a.StaffID=".$StaffID.""; 
	        }
	    }
	    return $this->db->query($sql)->result();
	}
    public function staff_summary_report($ShopID,$SDate,$EDate,$StaffID)
	{
	    $sql="Select a.StaffID,b.StaffName,a.EntryDate,Sum(a.Amount) as Amount,Sum(a.Cash) as Cash,Sum(a.Card) as Card from Bill_inf a inner join StaffMaster b on a.StaffID=b.StaffID where a.ShopID=".$ShopID." and a.EntryDate>='". $SDate ."' and a.EntryDate<='". $EDate ."'";
	    if ($StaffID != null)
	    {
	        if ($StaffID !=0)
	        {
	           $sql = $sql . " and a.StaffID=".$StaffID.""; 
	        }
	    }
	    $sql=$sql . " Group by(a.StaffID),(a.EntryDate)";
	    //echo  $sql;
	    return $this->db->query($sql)->result();
	}
    
	
	public function closing_report($ShopID,$EntryDate)
	{
	    $BillCount=0;
	    $BillAmount=0;
	    $Cash=0;
	    $Card=0;
	    $result="";
	    $Expenses=0;
	    $ExpCount=0;
	    $sql="Select Coalesce(Sum(Amount),0) as Amount,Coalesce(Sum(Cash),0) as Cash,Coalesce(Sum(Card),0) as Card
                ,Coalesce((Select count(*) from  Bill_inf where ShopID=".$ShopID." and EntryDate='".$EntryDate."'),0) as BillCount
                from Bill_inf where ShopID=".$ShopID." and EntryDate='".$EntryDate."'";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $BillCount=$row->BillCount;
    	    $BillAmount=$row->Amount;
	        $Cash=$row->Cash;
	        $Card=$row->Card;
        }
        $sql="Select Sum(Amount) as Amount,Coalesce((Select Count(*) from PVRV_inf where ShopID=".$ShopID." and EntryDate='".$EntryDate."'),0) as ExpCount from PVRV_inf where ShopID=".$ShopID." and EntryDate='".$EntryDate."'";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $Expenses=$row->Amount;
    	    $ExpCount=$row->ExpCount;
        }
        $sql="Select a.StaffID,b.StaffName,Coalesce((Select Sum(Amount) from Bill_inf where ShopID=".$ShopID." and StaffID=a.StaffID and EntryDate='".$EntryDate."'),0) as Amount
                ,Coalesce((Select Count(*) from Bill_inf where ShopID=".$ShopID." and StaffID=a.StaffID and EntryDate='".$EntryDate."'),0) as Billcount FROM
                (
                Select a.StaffID from ShopActivity a where ShopID=".$ShopID." and EntryDate='".$EntryDate."' group by(a.StaffID)
                    ) a inner join StaffMaster b on a.StaffID=b.StaffID";
        $Staff=$this->db->query($sql)->result();                    
	    $arr = array (
            array(
              "BillCount" => $BillCount,
              "Amount" => $BillAmount,
              "Cash" => $Cash,
              "Card" => $Card,
              "Expenses" => $Expenses,
              "ExpCount" => $ExpCount,
              "Staff"=>$Staff,
              "result" => $result
            )
        );
        return $arr;
	}
	public function get_shop_chair_status($ShopID)
	{
	    $imageURLDefault="https://api.ibill.ae/images/";

	    $ContryCode="";
    	$sql = "Select ContryCode from Shops where ShopID=". $ShopID ."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
                $ContryCode=$row->ContryCode;
        }
                $timezone="";
                date_default_timezone_set('Asia/Kolkata');
                if ($ContryCode=='+91')
                {
                    date_default_timezone_set('Asia/Kolkata');
	                $timezone="Culcutta";
                }
                else if ($ContryCode=='+971')
                {
                    date_default_timezone_set('Asia/Dubai');
	                $timezone="Dubai";
                }
                else if ($ContryCode=='+966')
                {
	                date_default_timezone_set('Asia/Riyadh');
                    $timezone="Riyadh";
                }
                else if ($ContryCode=='+965')
                {
                    date_default_timezone_set('Asia/Kuwait');
	                $timezone="Kuwait";
                }
                else
                {
                    date_default_timezone_set('Asia/Kolkata');
                    $timezone="Kolkata";
	            }	    
                $currentTime=date('H:i:s');           
    	        $currentDate=date("Y/m/d");
    	        
    	        //Added by illiyas
    	        $startTime = new DateTime('00:01:00');
                $endTime = new DateTime('04:30:00');
                if ($currentTime >= $startTime->format('H:i:s') && $currentTime <= $endTime->format('H:i:s')) {
                    
                    // Create a DateTime object using the current date
                $dateTime = new DateTime($currentDate);
                $dateTime->modify('-1 day');
                
                $currentDate = $dateTime->format('Y/m/d');
                }
//added by illiyas the above

                $date = $currentDate;
	            $sql="Select a.ShopID,a.CounterID,a.Details,a.CompanyID,a.UID,a.EntryDate,TIME_FORMAT(a.EntryTime, '%r') as EntryTime,TIME_FORMAT(Coalesce((Select EntryTime from ShopActivity where CounterID=a.CounterID and Status=1 and Note='BILL' and EntryDate='".$date."' order by(UID) Desc limit 1),''), '%r') as LastWorkTime,a.Status
                    ,(CASE
                        WHEN a.Status = 0 THEN 0
                        ELSE a.StaffID
                    END
                     ) as StaffID
                    ,(CASE
                        WHEN a.Status = 0 THEN ''
                        ELSE a.StaffName
                    	END
                     ) as StaffName
                    ,(CASE
                        WHEN a.Status = 0 THEN ''
                        ELSE a.StaffImage
                    	END
                     ) as StaffImage
                    from
                    (
                    		Select a.*,COALESCE(b.EntryDate,'01-01-2000') as EntryDate,Coalesce(b.EntryTime,'0000-00-00') as EntryTime,Coalesce(b.Status,0) as Status,Coalesce(b.StaffID,0) as StaffID,Coalesce((Select StaffName from StaffMaster where StaffID=b.StaffID),'') as StaffName,Coalesce((Select CONCAT('".$imageURLDefault."',Img) from StaffMaster where StaffID=b.StaffID),'') as StaffImage from 
                                    (
                                    	SELECT a.ShopID,a.CounterID,a.Details,b.CompanyID,Coalesce((Select UID from ShopActivity where CounterID=a.CounterID and EntryDate='".$date."' order by(UID) Desc LIMIT 1),0) as UID from CounterDetails a inner join Shops b on a.ShopID=b.ShopID where b.ShopID=".$ShopID."
                                    ) a LEFT join ShopActivity b on a.UID=b.UID
                    ) a";
	    return $this->db->query($sql)->result();
	}
	public function upload_image($details)
	{
	    $status=false;
	    $result="";
	    if($details != null)
	    {
	        $value=$details;
	        $ImageName=$value->ImageName;
	        $Img=$value->Img;
	        try
            {
                $image = base64_decode($Img);
                $image_name = $ImageName;
                $filename = $image_name . '.' . 'png';
                $path = "./Images_ERP/";
                file_put_contents($path . $filename, $image);
                $result="http://saloonerp.profitone.in/Images_ERP/".$filename;
                $status=true;
            }
            catch (Exception $e) 
            {
                $status=false;
            }
	    }
          $arr = array (
              array(
                  "status" => $status,
                  "result" => $result
              )
        );
		return $arr;	    
	}
	public function insert_staff($staff,$ProofDetails)
	{
	    //echo $staff;
	    $status=false;
	    $result="";
	    $StaffID=0;
	    //$Proofs[]=$ProofDetails;
	    if($staff != null)
	    {
	        $value=$staff;
	        
	        $StaffName=$value->StaffName;
            $Address=$value->Address;
            $Mobile=$value->Mobile;
            $ShopID=$value->ShopID;
            $Email=$value->Email;
            $DOB=$value->DOB;
            $DOJ=$value->DOJ;
            $Salary=$value->Salary;
            $Department=$value->Department;
            $Img=$value->Img;
            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))
            {
                $status=false;
                $result="Staff Name Already Exists";
                $StaffID=$row->StaffID;
            }
            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 = "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
                {
                    $status=false;
                    $result="Not Registered";
                }
	        }
	    }
	    else
	    {
	        $result="null";
	    }
	    $arr = array (
              array(
                  "status" => $status,
                  "result" => $result,
                  "StaffID" => $StaffID
              )
        );
		return $arr;
	}
	public function update_staff($staff,$ProofDetails)
	{
	   // echo $staff;
	    $status=false;
	    $result="";
	    $StaffID=0;
	    if($staff != null)
	    {
	        $value=$staff;
	        
            $StaffID=$value->StaffID;
	        $StaffName=$value->StaffName;
            $Address=$value->Address;
            $Mobile=$value->Mobile;
            $ShopID=$value->ShopID;
            $Email=$value->Email;
            $DOB=$value->DOB;
            $DOJ=$value->DOJ;
            $Salary=$value->Salary;
            $Department=$value->Department;
            $Img=$value->Img;
                
            if ($Salary =="")
            {
                $Salary=0;
            }
            if ($Salary ==null)
            {
                $Salary=0;
            }

        
            $sql = "SELECT StaffID FROM StaffMaster where StaffID=".$StaffID." and ShopID=".$ShopID."";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))    
            {
                $sql="Update StaffMaster Set StaffName = '". $StaffName ."',Address='".$Address."',Mobile='".$Mobile."',ShopID=".$ShopID.",DOB='".$DOB."',DOJ='".$DOJ."',Salary=". $Salary .",Department='".$Department."',Email='".$Email."'
                     where StaffID=". $StaffID ."";
                $check = $this->db->query($sql);
                //echo $sql;
                if($check)
                {
                    
                    if (trim($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);
                    }
                     $status=true;
                     $result="ok";
                     $StaffID=$StaffID;
                     $ii =0;
                     $sql="Delete from ProofDetails where StaffID = ".$StaffID."";
                     $check = $this->db->query($sql);
                     try
                     {
                         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 = "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
                {
                    $status=false;
                    $result="Not Updated";
                }
            }
            else
            {
                $status=false;
                $result="Staff Not Exists";
            }
	    }
	    $arr = array (
              array(
                  "status" => $status,
                  "result" => $result,
                  "StaffID" => $StaffID
              )
        );
		return $arr;
	}
	public function add_expenses($details,$items)
	{
	    $status=false;
        $result=$details;
        $BillNo=0;
        $BID=0;
        $ShopID=0;
        $value=$details;
        $RefNo=0;
        $EntryNo=0;
        if($details != null)
	    {
	        $ShopID=$value->ShopID;
	        $EntryDate=$value->EntryDate;
	        $Note=$value->Note;
	        date_default_timezone_set('Asia/Calcutta');



    	    $ContryCode="";
    	    $sql = "Select ContryCode from Shops where ShopID=". $ShopID ."";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $ContryCode=$row->ContryCode;
            }
                $timezone="";
                date_default_timezone_set('Asia/Kolkata');
                if ($ContryCode=='+91')
                {
                    date_default_timezone_set('Asia/Kolkata');
	                $timezone="Culcutta";
                }
                else if ($ContryCode=='+971')
                {
                    date_default_timezone_set('Asia/Dubai');
	                $timezone="Dubai";
                }
                else if ($ContryCode=='+966')
                {
	                date_default_timezone_set('Asia/Riyadh');
                    $timezone="Riyadh";
                }
                else if ($ContryCode=='+965')
                {
                    date_default_timezone_set('Asia/Kuwait');
	                $timezone="Kuwait";
                }
                else
                {
                    date_default_timezone_set('Asia/Kolkata');
                    $timezone="Kolkata";
	            }	    
                $currentTime=date('H:i:s');           
    	        $currentDate=date("Y/m/d");

            $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;
                }
            }
            $RefNo=0;
	        $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)
            {
                $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";
            }
	    }
	    else
	    {
	        $result="Null";
	    }
	    $arr = array (
              array(
                  "status" => $status,
                  "result" => $result,
                  "RefNo" => $RefNo,
                  "ShopID"=>$ShopID,
                  "EntryNo" => $EntryNo
              )
        );
		return $arr;
	}
	public function add_billing($details,$items)
	{
	    $status=false;
        $result=$details;
        $BillNo=0;
        $BID=0;
        $value=$details;
        if($details != null)
	    {
	        $ChairID=$value->ChairID;
	        $StaffID=$value->StaffID;
	        $EntryDate=$value->EntryDate;
	        $Status=1;
	        $BillAmount=$value->BillAmount;
	        $Cash=$value->Cash;
	        $Card=$value->Card;
	        $Customer=$value->Customer;
	        $Place=$value->Place;
	        $Mobile=$value->Mobile;
	        $CompanyID=0;
	        $ShopID=0;
            $tax=0;
            try
            {
                 $tax=$value->Tax;
            }
            catch(Exception $e)
            {
            }
            $Net=0;
            try
            {
                 $Net=$value->Net;
            }
            catch(Exception $e)
            {
            }
            if ($Net==0)
            {
                $Net=$BillAmount;
            }
	        $sql="Select a.ShopID,b.CompanyID from CounterDetails a inner join Shops b on a.ShopID=b.ShopID where a.CounterID=".$ChairID."";
	        $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
    	        $CompanyID=$row->CompanyID;
    	        $ShopID=$row->ShopID;
            }
    	    $ContryCode="";
    	    $sql = "Select ContryCode from Shops where ShopID=". $ShopID ."";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $ContryCode=$row->ContryCode;
            }     
           
                $timezone="";
                date_default_timezone_set('Asia/Kolkata');
                if ($ContryCode=='+91')
                {
                    date_default_timezone_set('Asia/Kolkata');
	                $timezone="Culcutta";
                }
                else if ($ContryCode=='+971')
                {
                    date_default_timezone_set('Asia/Dubai');
	                $timezone="Dubai";
                }
                else if ($ContryCode=='+966')
                {
	                date_default_timezone_set('Asia/Riyadh');
                    $timezone="Riyadh";
                }
                else if ($ContryCode=='+965')
                {
                    date_default_timezone_set('Asia/Kuwait');
	                $timezone="Kuwait";
                }
                else
                {
                    date_default_timezone_set('Asia/Kolkata');
                    $timezone="Kolkata";
	            }	    
                $currentTime=date('H:i:s');           
    	        $currentDate=date("Y/m/d");
    
            $EntryDate = $currentDate;
            $currentTime = $currentTime;     
            
            $begintime = new DateTime('00:01:00');
            $endtime = new DateTime('04:30:00');
           
             if ($currentTime >= $begintime->format('H:i:s') && $currentTime <= $endtime->format('H:i:s')) 
            {
               
                
                $dateTime = new DateTime($currentDate);
                $dateTime->modify('-1 day');

                $currentDate = $dateTime->format('Y/m/d');
                $EntryDate=$dateTime->format('Y/m/d');
            }
            
            $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;
                }
            }
            $Bid=0;
	        $sql="INSERT INTO Bill_inf(ShopID,StaffID,CounterID,EntryDate,EntryTime,BillNo,Customer,Place,Mobile,Amount,Cash,Card,Net,Tax) VALUES (".$ShopID.",".$StaffID.",".$ChairID.",'".$EntryDate."','".$currentTime."',".$BillNo."
                        	        ,'".$Customer."','".$Place."','".$Mobile."',".$BillAmount.",".$Cash.",".$Card.",".$Net.",".$tax.")";
            $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;
                }
                $UUID=$ShopID.'-'.$ChairID.'-'.$StaffID.'-'.$EntryDate.'-'.$currentTime.'-'.$Status;
		        $sql="INSERT INTO ShopActivity(ShopID,CounterID,StaffID,EntryDate,EntryTime,BillNo,Note,Status,CompanyID,UUID) VALUES (".$ShopID.",".$ChairID.",".$StaffID.",'".$EntryDate."','".$currentTime."',".$Bid.",'BILL',".$Status.",".$CompanyID.",'".$UUID."')";
                $check = $this->db->query($sql);
                
                if($items && $items !='null')
                {
                    foreach($items as $data) 
                    {
                        $ItemName=$data->ItemName;
                        $Qty=$data->Qty;
                        $Rate=$data->Rate;
                        $Amount=$data->Amount;
                        $Type=$data->Type;
    
                            $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="Saved";
            }
	    }
	    else
	    {
	        $result="Null";
	    }
	    $arr = array (
              array(
                  "status" => $status,
                  "result" => $result,
                  "BID" => $BID,
                  "BID" => $BillNo
              )
        );
		return $arr;
	}
    public function insert_item($item)
    {
	    $status=false;
	    $result="";
	    $ItemID=0;
	    if($item != null)
	    {
	        $value=$item;
            $ItemName=$value->ItemName;
            $Type=$value->Type;
            $Category=$value->Category;
            $Rate=$value->Rate;
            $Tax=0;
            try
            {
                $Tax=$value->Tax;
            }
            catch(Exception $e)
            {
            }
            $Details=$value->Details;
            $ShopID=$value->ShopID;
            $sql = "SELECT ItemID FROM Item_Master where ItemName='".$ItemName."' and ShopID=".$ShopID."";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $status=false;
                $result="Item Already Exists";
                $ItemID=$row->ItemID;
            }
            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";
         	         $sql = "SELECT ItemID FROM Item_Master where ItemName='".$ItemName."' and ShopID=".$ShopID."";
                     $query = $this->db->query($sql);
                     $row = $query->row();
                     if (isset($row))
                     {
                          $ItemID=$row->ItemID;
                     }
                }
                else
                {
                    $status=false;
                    $result="Not Registered";
                }
            }
	    }
	    else
	    {
	        $result="Null";
	    }
	    $arr = array (
              array(
                  "status" => $status,
                  "result" => $result,
                  "ItemID" => $ItemID
              )
        );
		return $arr;
    }
    public function update_item($item)
    {
	    $status=false;
	    $result="";
	    $ItemID=0;
	    if($item != null)
	    {
	        $value=$item;
	        $ItemName=$value->ItemName;
	        $Type=$value->Type;
	        $Category=$value->Category;
	        $Rate=$value->Rate;
	        $Tax=$value->Tax;
	        $Details=$value->Details;
	        $ShopID=$value->ShopID;
	        $ItemID=$value->ItemID;
	           
	        $sql = "SELECT ItemID FROM Item_Master where ItemID=".$ItemID."";
            $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 ."',ShopID=".$ShopID." where ItemID=". $ItemID ."";
                $check = $this->db->query($sql);
                if($check)
                {
                     $status=true;
                     $result="ok";
                }
                else
                {
                    $status=false;
                    $result="Not Updated";
                }
            }
            else
            {
                $status=false;
                $result="Not Registered";
            }
	    }
	    $arr = array (
              array(
                  "status" => $status,
                  "result" => $result,
                  "ItemID" => $ItemID
              )
        );
		return $arr;
    }

    public function insert_chair($chair)
    {
	    $status=false;
	    $result="";
	    $CounterID=0;
	    if($chair != null)
	    {
	        $value=$chair;
    	    $CharirName=$value->CharirName;
	        $ShopID=$value->ShopID;
	        $Type=$value->Type;
	        $sql = "SELECT CounterID FROM CounterDetails where Details='".$CharirName."' and ShopID=".$ShopID."";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $status=false;
                $result="Chair Already Exists";
                $CounterID=$row->CounterID;
            }
            else
            {
                $sql="INSERT INTO CounterDetails(Details,ShopID,Type) 
                VALUES ('". $CharirName ."',".$ShopID.",'".$Type."')";
                $check = $this->db->query($sql);
                if($check)
                {
                     $status=true;
                     $result="ok";
         	         $sql = "SELECT CounterID FROM CounterDetails where Details='".$CharirName."' and ShopID=".$ShopID."";
                     $query = $this->db->query($sql);
                     $row = $query->row();
                     if (isset($row))
                     {
                          $CounterID=$row->CounterID;
                     }
                }
                else
                {
                    $status=false;
                    $result="Not Registered";
                }
            }
	    }
	    else
	    {
	        $result= "null";
	    }
	    $arr = array (
              array(
                  "status" => $status,
                  "result" => $result,
                  "ChairID" => $CounterID
              )
        );
		return $arr;
    }
    public function update_chair($chair)
    {
	    $status=false;
	    $result="";
	    $CounterID=0;
	    if($chair != null)
	    {
	        $value=$chair;
	        $CharirName=$value->CharirName;
	        $ShopID=$value->ShopID;
	        $CounterID=$value->ChairID;
	        $Type=$value->Type;
	        $sql = "SELECT CounterID FROM CounterDetails where CounterID=". $CounterID ."";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                 $sql="Update CounterDetails Set Details='". $CharirName ."',ShopID=".$ShopID.",Type='".$Type."' where CounterID=". $CounterID ."";
                 $check = $this->db->query($sql);
                 if($check)
                 {
                      $status=true;
                      $result="ok";
                 }
                 else
                 {
                     $status=false;
                     $result="Not Updated";
                 }
            }
            else
            {
                $status=false;
                $result="Chair Not Exists";
                $CounterID=$row->CounterID;
            }
	    }
	    else
	    {
	        $result="null22";
	    }
	    $arr = array (
              array(
                  "status" => $status,
                  "result" => $result,
                  "ChairID" => $CounterID
              )
        );
		return $arr;
    }
	public function insert_shop($shops)
	{
	    $status=false;
	    $result="";
	    $ShopID=0;
	    if($shops != null)
		{
		    $value=$shops;
            $ShopName=$value->ShopName;
            $CompanyID=$value->CompanyID;
            $Address=$value->Address;
            $Contact=$value->Contact;
            $Email=$value->Email;
            $RegnNo=$value->RegnNo;
            $PinCode=$value->PinCode;
            $latitude=$value->latitude;
            $longitude=$value->longitude;
            $StartTime=$value->StartTime;
            $ClosingTime=$value->ClosingTime;
            $HolyDays=$value->HolyDays;
            $Category=$value->Category;
            $Img1=$value->Img1;
            $Img2=$value->Img2;
            $Img3=$value->Img3;
            $UserName=$value->UserName;
            $Pwd=$value->Pwd;            
            $CurencyName="";
            $ContryCode="";
            try
            {
                $CurencyName=$value->CurencyName;
                $ContryCode=$value->ContryCode;
            }
            catch(Exception $e)
            {
            }
            $tax=0;
            try
            {
                $tax=$value->Tax;
            }
            catch(Exception $e)
            {
            }
            $CategoryID=0;
            $Category=trim($Category);
            if (trim($Category)<>"")
            {
                $sql = "SELECT UID FROM Category where CatName='".$Category."'";
                $query = $this->db->query($sql);
                $row = $query->row();
                if (isset($row))
                {
                       $CategoryID=$row->UID;
                }
                else
                {
                     $sql="INSERT INTO Category(CatName) VALUES ('". $Category ."')";
                     $check = $this->db->query($sql);
                     if($check)
                     {
                        $sql = "SELECT UID FROM Category where CatName='".$Category."'";
                        $query = $this->db->query($sql);
                        $row = $query->row();
                        if (isset($row))
                        {
                            $CategoryID=$row->UID;
                        }
                     }
                }
            }
            $sql = "SELECT ShopID FROM Shops where ShopName='".$ShopName."'";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                  $status=false;
                  $result="Shop Name Already Exists";
                  $ShopID=$row->ShopID;
            }
            else
            {
                $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";
                     $sql = "SELECT ShopID FROM Shops where ShopName='".$ShopName."'";
                     $query = $this->db->query($sql);
                     $row = $query->row();
                     if (isset($row))
                     {
                          $ShopID=$row->ShopID;
                     }
                     $sql="INSERT INTO AdminUser(UserName,Password,ShopID,UserType) 
                            VALUES ('". $UserName ."','".$Pwd."',".$ShopID.",'SHOP-ADMIN')";
                     $check = $this->db->query($sql);
     
                     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);
                         }
                         $filename2="";
                         if ($Img2 != null || $Img2 !="")
                         {
                             $Img=$Img2;
                             $image = base64_decode($Img);
                             $image_name = "Shop2_".$ShopID."_2";
                             $filename2 = $image_name . '.' . 'png';
                             $path = "./images/";
                             file_put_contents($path . $filename2, $image);
                             $sql = "Update Shops Set Img2='".$filename2."' where ShopID=".$ShopID."";                         
                             $check = $this->db->query($sql);
                         }
                         $filename3="";
                         if ($Img3 != null || $Img3 !="")
                         {
                             $Img=$Img3;
                             $image = base64_decode($Img);
                             $image_name = "Shop3_".$ShopID."_3";
                             $filename3 = $image_name . '.' . 'png';
                             $path = "./images/";
                             file_put_contents($path . $filename3, $image);
                             $sql = "Update Shops Set Img3='".$filename3."' where ShopID=".$ShopID."";                         
                             $check = $this->db->query($sql);
                         }
                     }
                     catch (Exception $e) 
                     {
                        
                     }
                }
                else
                {
                    $status=false;
                    $result="Not Registered";
                }
            }
		}
		$arr = array (
              array(
                  "status" => $status,
                  "result" => $result,
                  "ShopID" => $ShopID
              )
        );
		return $arr;
	}
	public function close_day($ShopID,$EntryDate)
	{
	    $status=false;
	    $result="";
	    try
	    {
    	    $CompanyID=0;
    	    $sql = "SELECT CompanyID FROM Shops where ShopID=".$ShopID."";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $CompanyID=$row->CompanyID;
            }
            date_default_timezone_set('Asia/Calcutta');
            
    	    $ContryCode="";
    	    $sql = "Select ContryCode from Shops where ShopID=". $ShopID ."";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $ContryCode=$row->ContryCode;
            }
                $timezone="";
                date_default_timezone_set('Asia/Kolkata');
                if ($ContryCode=='+91')
                {
                    date_default_timezone_set('Asia/Kolkata');
	                $timezone="Culcutta";
                }
                else if ($ContryCode=='+971')
                {
                    date_default_timezone_set('Asia/Dubai');
	                $timezone="Dubai";
                }
                else if ($ContryCode=='+966')
                {
	                date_default_timezone_set('Asia/Riyadh');
                    $timezone="Riyadh";
                }
                else if ($ContryCode=='+965')
                {
                    date_default_timezone_set('Asia/Kuwait');
	                $timezone="Kuwait";
                }
                else
                {
                    date_default_timezone_set('Asia/Kolkata');
                    $timezone="Kolkata";
	            }	    
                $currentTime=date('H:i:s');           
    	        $currentDate=date("Y/m/d");

            
//            $currentTime = date('h:i:s', time());
            $ActiveCount=0;
            $ChairNames="";
            $sql="Select a.CounterID,a.CounterName,a.Status,a.StaffID,Coalesce((Select StaffName from StaffMaster where StaffID=a.StaffID),'') as StaffName from
                (
                    Select a.CounterID,Coalesce((Select Details from CounterDetails where CounterID=a.CounterID),'') as CounterName
                    ,Coalesce((Select Status from ShopActivity where ShopID=".$ShopID." and EntryDate='".$EntryDate."' and CounterID=a.CounterID and StaffID=a.StaffID order by(UID) DESC LIMIT 1),0) as Status
                    ,Coalesce((Select StaffID from ShopActivity where ShopID=".$ShopID." and EntryDate='".$EntryDate."' and CounterID=a.CounterID and StaffID=a.StaffID order by(UID) DESC LIMIT 1),0) as StaffID
                    from ShopActivity a where a.EntryDate='".$EntryDate."' and a.ShopID=".$ShopID." GROUP by(a.CounterID),(a.StaffID)
                ) a";
    	    $query = $this->db->query($sql);
    	    foreach ($query->result() as $row)
            {
                if ($row->Status==2)
                {
                      $ActiveCount=$ActiveCount+1;        
                      if (trim($ChairNames)!="")
                      {
                          $ChairNames=$ChairNames . ",";
                      }
                      $ChairNames=$ChairNames . $row->CounterName ."-".$row->StaffName;
                }
            }
            if ($ActiveCount==0)
            {
        	    foreach ($query->result() as $row)
                {
                    $CounterID= $row->CounterID;
                    $StaffID= $row->StaffID;
                    $UUID=$ShopID.'-'.$CounterID.'-'.$StaffID.'-'.$EntryDate.'-'.$currentTime.'-0';
                    $sql1="INSERT INTO ShopActivity(ShopID,CounterID,StaffID,EntryDate,EntryTime,BillNo,Note,Status,CompanyID,UUID) VALUES (".$ShopID.",".$CounterID.",".$StaffID.",'".$EntryDate."','".$currentTime."',0,'CLOSING',0,".$CompanyID.",'".$UUID."')";
                    $check = $this->db->query($sql1);
                }
                $result="day closed";
                $status=true;
            }
            else
            {
                $status=false;
                $result ="Chairs Not Closed (" . $ChairNames. ")";
            }
	    }
        catch(Exception $e)
	    {
	        $status=false;
	        $result=$e->getMessage();      
	    }
        $arr = array (
        array(
                  "status" => $status,
                  "result" => $result
              )
        );
	    return $arr;
	}
	public function chair_status($ChairID,$StaffID,$Status,$EntryDate)
	{
	    $ShopID=0;
	    $CompanyID=0;
	    $sql = "Select a.ShopID,a.CounterID,(Select CompanyID from Shops where ShopID=a.ShopID) as CompanyID from CounterDetails a where a.CounterID=". $ChairID ."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $CompanyID=$row->CompanyID;
            $ShopID=$row->ShopID;
        }
	    $currentDate=$EntryDate;
	    $ContryCode="";
	    $sql = "Select ContryCode from Shops where ShopID=". $ShopID ."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $ContryCode=$row->ContryCode;
        }
                $timezone="";
                date_default_timezone_set('Asia/Kolkata');
                if ($ContryCode=='+91')
                {
                    date_default_timezone_set('Asia/Kolkata');
	                $timezone="Culcutta";
                }
                else if ($ContryCode=='+971')
                {
                    date_default_timezone_set('Asia/Dubai');
	                $timezone="Dubai";
                }
                else if ($ContryCode=='+966')
                {
	                date_default_timezone_set('Asia/Riyadh');
                    $timezone="Riyadh";
                }
                else if ($ContryCode=='+965')
                {
                    date_default_timezone_set('Asia/Kuwait');
	                $timezone="Kuwait";
                }
                else
                {
                    date_default_timezone_set('Asia/Kolkata');
                    $timezone="Kolkata";
	            }	    
                $currentTime=date('H:i:s');           
    	        $currentDate=date("Y/m/d");

            $EntryDate = $currentDate;
            $currentTime = $currentTime;    
            
            
            $startTime = new DateTime('00:01:00');
            $endTime = new DateTime('04:30:00');
            
            if ($currentTime >= $startTime->format('H:i:s') && $currentTime <= $endTime->format('H:i:s')) 
            {
    
                // Create a DateTime object using the current date
                $dateTime = new DateTime($currentDate);
                $dateTime->modify('-1 day');

                $currentDate = $dateTime->format('Y/m/d');

    
    
            }
            
            
            
            
        if ($Status==0)
        {
            
        }
         
        $UUID=$ShopID.'-'.$ChairID.'-'.$StaffID.'-'.$currentDate.'-'.$currentTime.'-'.$Status;
	    $sql="INSERT INTO ShopActivity(ShopID,CounterID,StaffID,EntryDate,EntryTime,BillNo,Note,Status,CompanyID,Details,UUID) VALUES (".$ShopID.",".$ChairID.",".$StaffID.",'".$currentDate."','".$currentTime."',0,'ACTIVE',".$Status.",".$CompanyID.",'".$ContryCode."-".$timezone."','".$UUID."')";
        $check = $this->db->query($sql);
        if($check)
        {
             $status=true;
             $result="ok";
        }
        else
        {
            $status=false;
            $result="Not Updated";
        }
        $arr = array (
              array(
                  "status" => $status,
                  "result" => $result,
              )
        );
		return $arr;
	}
	
	
		public function get_dattym()
	 {
	     $dat="";
	    $result="";
	    $ContryCode='+91';
	    $yesterdayDate="";
	   
                $timezone="";
                date_default_timezone_set('Asia/Kolkata');
                if ($ContryCode=='+91')
                {
                    date_default_timezone_set('Asia/Kolkata');
	                $timezone="Culcutta";
                }
                else if ($ContryCode=='+971')
                {
                    date_default_timezone_set('Asia/Dubai');
	                $timezone="Dubai";
                }
                else if ($ContryCode=='+966')
                {
	                date_default_timezone_set('Asia/Riyadh');
                    $timezone="Riyadh";
                }
                else if ($ContryCode=='+965')
                {
                    date_default_timezone_set('Asia/Kuwait');
	                $timezone="Kuwait";
                }
                else
                {
                    date_default_timezone_set('Asia/Kolkata');
                    $timezone="Kolkata";
	            }	    
                $currentTime=date('H:i:s');           
    	        $currentDate=date("Y/m/d");

$startTime = new DateTime('00:01:00');
$endTime = new DateTime('04:30:00');
if ($currentTime >= $startTime->format('H:i:s') && $currentTime <= $endTime->format('H:i:s')) {
    
    // Create a DateTime object using the current date
$dateTime = new DateTime($currentDate);
$dateTime->modify('-1 day');

$currentDate = $dateTime->format('Y/m/d');

    
    
}
            
      
        $arr = array (
              array(
                  "dat" =>    $currentDate,
                  "result" =>  $currentTime,
              )
        );
		return $arr;
	}
	
	public function activate_chair($ChairID,$StaffID)
	{
	    $ShopID=0;
	    $CompanyID=0;
	    $sql = "Select a.ShopID,a.CounterID,(Select CompanyID from Shops where ShopID=a.ShopID) as CompanyID from CounterDetails a where a.CounterID=". $ChairID ."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $CompanyID=$row->CompanyID;
            $ShopID=$row->ShopID;
        }
        
        $ContryCode="";
    	$sql = "Select ContryCode from Shops where ShopID=". $ShopID ."";
        $query = $this->db->query($sql);
        $row = $query->row();
        if (isset($row))
        {
            $ContryCode=$row->ContryCode;
        }
                $timezone="";
                date_default_timezone_set('Asia/Kolkata');
                if ($ContryCode=='+91')
                {
                    date_default_timezone_set('Asia/Kolkata');
	                $timezone="Culcutta";
                }
                else if ($ContryCode=='+971')
                {
                    date_default_timezone_set('Asia/Dubai');
	                $timezone="Dubai";
                }
                else if ($ContryCode=='+966')
                {
	                date_default_timezone_set('Asia/Riyadh');
                    $timezone="Riyadh";
                }
                else if ($ContryCode=='+965')
                {
                    date_default_timezone_set('Asia/Kuwait');
	                $timezone="Kuwait";
                }
                else
                {
                    date_default_timezone_set('Asia/Kolkata');
                    $timezone="Kolkata";
	            }	    
                $currentTime=date('H:i:s');           
    	        $currentDate=date("Y/m/d");
    	        
    	        $startTime = new DateTime('00:01:00');
                $endTime = new DateTime('04:30:00');
                if ($currentTime >= $startTime->format('H:i:s') && $currentTime <= $endTime->format('H:i:s')) {
                    
                    // Create a DateTime object using the current date
                $dateTime = new DateTime($currentDate);
                $dateTime->modify('-1 day');
                
                $currentDate = $dateTime->format('Y/m/d');
                
                    
                    
                }
                    	        
//ADDED BY ILLIYAS ON 24-03-2024//
                  

//NEED CORRECTION//
	   $UUID=$ShopID.'-'.$ChairID.'-'.$StaffID.'-'.$currentDate.'-'.$currentTime.'-1';

	    $sql="INSERT INTO ShopActivity(ShopID,CounterID,StaffID,EntryDate,EntryTime,BillNo,Note,Status,CompanyID,Details,UUID) VALUES (".$ShopID.",".$ChairID.",".$StaffID.",'".$currentDate."','".$currentTime."',0,'ACTIVE',1,".$CompanyID.",'".$ContryCode."','".$UUID."')";
        $check = $this->db->query($sql);
        if($check)
        {
             $status=true;
             $result="ok";
        }
        else
        {
            $status=false;
            $result="Not Updated";
        }
	}

	public function update_shop($shops)
	{
	    $status=false;
	    $result="";
	    $ShopID=0;
	    if($shops != null)
		{
		    $value=$shops;
                $ShopName=$value->ShopName;
                $CompanyID=$value->CompanyID;
                $Address=$value->Address;
                $Contact=$value->Contact;
                $Email=$value->Email;
                $RegnNo=$value->RegnNo;
                $PinCode=$value->PinCode;
                $latitude=$value->latitude;
                $longitude=$value->longitude;
                $StartTime=$value->StartTime;
                $ClosingTime=$value->ClosingTime;
                $HolyDays=$value->HolyDays;
                $Category=$value->Category;
                $Img1=$value->Img1;
                $Img2=$value->Img2;
                $Img3=$value->Img3;
                $ShopID=$value->ShopID;
                $UserName=$value->UserName;
                $Pwd=$value->Pwd;
                $CategoryID=0;
                $CurencyName="";
                $ContryCode="";
                try
                {
                    $CurencyName=$value->CurencyName;
                    $ContryCode=$value->ContryCode;
                }
                catch(Exception $e)
                {
                }
                $tax=0;
                try
                {
                    $tax=$value->Tax;
                }
                catch(Exception $e)
                {
                }
                $Category=trim($Category);
                if (trim($Category)<>"")
                {
                    $sql = "SELECT UID FROM Category where CatName='".$Category."'";
                    $query = $this->db->query($sql);
                    $row = $query->row();
                    if (isset($row))
                    {
                           $CategoryID=$row->UID;
                    }
                    else
                    {
                         $sql="INSERT INTO Category(CatName) VALUES ('". $Category ."')";
                         $check = $this->db->query($sql);
                         if($check)
                         {
                            $sql = "SELECT UID FROM Category where CatName='".$Category."'";
                            $query = $this->db->query($sql);
                            $row = $query->row();
                            if (isset($row))
                            {
                                $CategoryID=$row->UID;
                            }
                         }
                    }
                }
                 $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 ."',CompanyID='".$CompanyID."'
                        ,Address='".$Address."',Contact='".$Contact."',Email='".$Email."',RegnNo='".$RegnNo."',PinCode='".$PinCode."',latitude='".$latitude."',longitude='".$longitude."'
                        ,StartTime='".$StartTime."',ClosingTime='".$ClosingTime."',HolyDays='".$HolyDays."',CategoryID=".$CategoryID."
                        ,CurencyName='".$CurencyName."',ContryCode='".$ContryCode."',UserName='".$UserName."',Pwd='".$Pwd."',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);
                             }
                             $filename2="";
                             if ($Img2 != null || $Img2 !="")
                             {
                                 $Img=$Img2;
                                 $image = base64_decode($Img);
                                 $image_name = "Shop2_".$ShopID."_2";
                                 $filename2 = $image_name . '.' . 'png';
                                 $path = "./images/";
                                 file_put_contents($path . $filename2, $image);
                                 $sql = "Update Shops Set Img2='".$filename2."' where ShopID=".$ShopID."";                         
                                 $check = $this->db->query($sql);
                             }
                             $filename3="";
                             if ($Img3 != null || $Img3 !="")
                             {
                                 $Img=$Img3;
                                 $image = base64_decode($Img);
                                 $image_name = "Shop3_".$ShopID."_3";
                                 $filename3 = $image_name . '.' . 'png';
                                 $path = "./images/";
                                 file_put_contents($path . $filename3, $image);
                                 $sql = "Update Shops Set Img3='".$filename3."' 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 insert_shop2($data)
	{
	    return $this->db->insert('Shops',$data);
	}
	public function insert_company($company)
	{
        $result="";
 		$CompanyName="";
 		$CompanyID=0;
        $status=false;
 	    if($company != null)
 		{
 		    $status=$company;
 		    $value=$company;
                 $CompanyName=$value->CompanyName;
                 $Address=$value->Address;
                 $Mobile=$value->Mobile;
                 $Email=$value->Email;
                 $Status=$value->Status;
                 $Img=$value->Img;
                 $OwnerName=$value->OwnerName;
                 $ContactPerson=$value->ContactPerson;
                 $Designation=$value->Designation;
                 $ContactNo=$value->ContactNo;
                 
              
                
                 
                 $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)
                     {
                        $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;
                        }
        
                        $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);
                        
                        $status=true;
                        $result="Company Registsred";
                     }
                     else
                     {
                         $status=false;
                         $result="Company Not Registered";
                     }
                 }
  		}
         $arr = array (
              array(
                  "status" => $status,
                  "CompanyName" => $CompanyName,
                  "result" => $result,
                  "CompanyID" =>$CompanyID
              )
          );
		return $arr;
	}
	public function update_company($company)
	{
        $result="";
 		$CompanyName="";
 		$CompanyID=0;
        $status=false;
 	    if($company != null)
 		{
 		    $status=$company;
 		    $value=$company;
            $CompanyName=$value->CompanyName;
            $Address=$value->Address;
            $Mobile=$value->Mobile;
            $Email=$value->Email;
            $Status=$value->Status;
            $Img=$value->Img;
            $OwnerName=$value->OwnerName;
            $ContactPerson=$value->ContactPerson;
            $Designation=$value->Designation;
            $ContactNo=$value->ContactNo;
            $CompanyID=$value->CompanyID;
            
            
            $sql = "SELECT CompanyID FROM Company where CompanyID=".$CompanyID."";
            $query = $this->db->query($sql);
            $row = $query->row();
            if (isset($row))
            {
                $sql="update Company Set CompanyName='".$CompanyName."',Address='".$Address."',Mobile='".$Mobile."',Email='".$Email."',Status=".$Status.",OwnerName='".$OwnerName."',ContactPerson='".$ContactPerson."',Designation='".$Designation."',ContactNo='".$ContactNo."'
                    where CompanyID=".$CompanyID."";
                $check = $this->db->query($sql);
                $status=$check;
                if($check)
                {
                    
                    if (trim($Img)!=null)
                    {
                        $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);
                    }

                    $status=true;
                    $result="Company Updated";
                }
                else
                {
                     $status=false;
                     $result="Company Not Updated";
                }
            }
            else
            {
                $status=false;
                $result="Company Not Registered";
            }
  		}
         $arr = array (
              array(
                  "status" => $status,
                  "CompanyName" => $CompanyName,
                  "result" => $result,
                  "CompanyID" =>$CompanyID
              )
          );
		return $arr;
	}
}
