<?php

namespace App\Http\Controllers;

use App\Exports\PurchaseExport;
use App\Exports\StockExport;
use App\Models\Clients;
use App\Models\Packages;
use App\Models\PasswordTable;
use App\Models\PermissionActions;
use App\Models\PurchaseOrder;
use App\Models\SalesItemOne;
use App\Models\SalesOne;
use App\Models\Stock;
use App\Models\Supplier;
use App\Models\User;
use App\Models\Users;
use App\Models\TblAssignedShops;
use App\Models\UserPermissions;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Response;
use Illuminate\Support\Facades\Storage;
use PDF;
use Maatwebsite\Excel\Facades\Excel;


class ClientController extends Controller
{
    
     function deleteusersfromdb($id)
    {
        $userid = Crypt::decrypt($id);

        $client = Users::find($userid);

        $client->delete();

        return back()->with('success', 'User deleted successfully');
    }
    
    
    function updateAssignedShop(Request $request)
    {
        $client = $request->client;
        $userID = $request->userID;
        $shopList = $request->shopList;

        TblAssignedShops::where('User_ID', '=', $userID)->delete();
        
        $defaultFrom = UserPermissions::where('Client_ID','=',$client)->where('User_ID', '=', $userID)->where('userper_slug','=','trial')->pluck('per_from')->first();
        $defaultTo = UserPermissions::where('Client_ID','=',$client)->where('User_ID', '=', $userID)->where('userper_slug','=','trial')->pluck('per_to')->first();

        foreach ($shopList as $shops) {

            $tbs = new TblAssignedShops();

            $tbs->User_ID = $userID;
            $tbs->ClientID = $client;
            $tbs->Managed_ClientID = $shops;

            $saved = $tbs->save();

            if($saved)
            {
                $existing = UserPermissions::where('Client_ID','=',$shops)->where('User_ID','=',$userID)->first();

                if(!$existing)
                {
                    $actions = PermissionActions::where('act_package', '=', $existing->Package_ID)->get();

                    for ($count = 0; $count < count($actions); $count++) {

                        $data = array(
                            'Client_ID' => $shops,
                            'User_ID' => $userID,
                            'Package_ID'  => $existing->Package_ID,
                            'per_act'  => $actions[$count]['id'],
                            'per_from'  => $defaultFrom,
                            'per_to'  => $defaultTo,
                            'status'  => $actions[$count]['act_status'],
                            'userper_slug'  => $actions[$count]['act_slug']
                        );

                        $insert_data[] = $data;
                    }

                    UserPermissions::insert($insert_data);

                }
            }
        }

        return back()->with('success', 'Assigned shop list updated successfully');
    }

    function manageAssignedShopList($cid)
    {
        $client = Crypt::decrypt($cid);

        // return $client;

        $allClients = DB::select("  select asp.Managed_ClientID from tbl_assigned_shops asp
        left JOIN user u on asp.ClientID = u.userid
        left JOIN clients c1 on asp.ClientID = c1.ClientID
        left join clients c2 on asp.Managed_ClientID = c2.ClientID
        where c1.ClientID = " . $client . " GROUP BY asp.Managed_ClientID ");

        $allClientsString = '';

        foreach ($allClients as $all) {
            $allClientsString .= $all->Managed_ClientID . ',';
        }

        $data = [
            'data' => DB::select("  select asp.Managed_ClientID,c2.ClientName,c2.Area,c2.Location,u.Name,u.UserName,u.Package_ID,u.id as userid,
            u.userid as mainclientid,u.IsLive from tbl_assigned_shops asp
            left JOIN user u on asp.ClientID = u.userid
            left JOIN clients c1 on asp.ClientID = c1.ClientID
            left join clients c2 on asp.Managed_ClientID = c2.ClientID
            where c1.ClientID = " . $client . " GROUP BY asp.Managed_ClientID "),

            'allClients' => rtrim($allClientsString, ",")
        ];

        // return $data;
        return view('Admin.assignedShopPrivilage', $data);
    }

    function updateassignedshoplist($id, $client)
    {
        $userid = Crypt::decrypt($id);
        $clientDecr = Crypt::decrypt($client);

        $data = [
            'userid' => $userid,
            'assignedShopList' => TblAssignedShops::select("Managed_ClientID")->where('User_ID', '=', $userid)->get(),
            'allClients' => Clients::all(),
            'client' => $clientDecr,
        ];

        return view('Admin.manageAssignedShopList', $data);
    }

    function getAllSuppliers()
    {
        $client = session('LoggedUser')['ClientID'];

        $suppliers = Supplier::where('ClientID', '=', $client)->get();

        return $suppliers;
    }

    function updateMaxUserCOunt(Request $request)
    {
        $clientid = $request->clientid;
        $userid = $request->userid;
        $maxuser = $request->maxconnection;

        $user = Users::find($userid);

        $user->max_connections = $maxuser;

        $res = $user->save();

        if ($res) {
            return back()->with('success', 'Max user connection updated');
        } else {
            return back()->with('fail', 'Something went wrong');
        }
    }

    function makeasprimaryaccount($userid)
    {
        $userID = Crypt::decrypt($userid);

        $clientid = Users::where('id', '=', $userID)->pluck('userid')->first();

        DB::raw('UPDATE `user` SET `isPrimary`= 0 WHERE userid = ' . $clientid . ' ');

        // $res = DB::table('user')
        //     ->where('id', $user)
        //     ->update(['IsPrimary' => 1]);

        $user = Users::find($userID);

        $user->IsPrimary = 1;

        $res = $user->save();

        if ($res) {
            return back()->with('success', 'Account marked as primary');
        } else {
            return back()->with('fail', 'Something went wrong');
        }
    }
    ////////
    function showusersbyPackage($pack)
    {
        $packid = Crypt::decrypt($pack);

        $data = [
            'package' => Packages::where('id', '=', $packid)->pluck('Pack_Name')->first(),

            // 'users' => Users::select('user.*', 'clients.ClientName', 'clients.Area', 'clients.Location','tbl_trials.From_Date','tbl_trials.To_Date')
            //     ->join('clients', 'user.userid', '=', 'clients.ClientID')
            //     ->leftjoin('tbl_trials', 'user.id', '=', 'tbl_trials.User_ID')
            //     ->where('user.Package_ID','=',$packid)

            //     ->get(),

            // 'users' => DB::select("select usr.* , c.ClientName, c.Area, c.Location, tup.per_from as From_Date,tup.per_to as To_Date,act_slug from user usr
            // left join clients c on usr.userid = c.ClientID
            // left join tbl_userpermissions tup on usr.id = tup.User_ID
            // left join tbl_permissionActions tpa on tup.Package_ID = tpa.act_package
            // where 1>0 and (usr.Package_ID = $packid
            // or tpa.act_slug = 'trial') GROUP BY usr.id ORDER BY From_Date"),

            'users' => DB::select("select usr.* , tup.per_from  , tup.per_to ,c.ClientName, c.Area, c.Location, tup.per_from as From_Date,tup.per_to as To_Date,act_slug from user usr
            left join clients c on usr.userid = c.ClientID
            left join tbl_userpermissions tup on usr.id = tup.User_ID
            left join tbl_permissionActions tpa on tup.Package_ID = tpa.act_package
            where 1>0 and (usr.Package_ID = $packid) and usr.active = 1 and Admin != 1 and IsShop = 1 GROUP BY usr.id ORDER BY From_Date"),

        ];

        // 'users' => Users::select('user.*', 'clients.ClientName', 'clients.Area', 'clients.Location','tbl_userpermissions.per_from','tbl_userpermissions.per_to')
        // ->join('clients', 'user.userid', '=', 'clients.ClientID')
        // ->join('tbl_userpermissions', 'user.id', '=', 'tbl_userpermissions.User_ID')
        // ->join('tbl_permissionActions', 'tbl_userpermissions.Package_ID', '=', 'tbl_permissionActions.act_package')
        // ->where('user.Package_ID', '=', $packid)
        // ->where('tbl_permissionActions.act_slug', '=', 'trial')
        // ->groupBy('user.id')
        // ->get(),

        return view('Admin.viewusersbypackage', $data);
        // return $data;
    }

    function getBillingInfoByClientID(Request $request)
    {
        $allUsers = Clients::where('ClientID', '=', $request->client)->get();

        return $allUsers;
    }


    public function exportUsers($ordernumber, $clientid, $userid)
    {

        $file_name = 'purchase_order' . date('Y_m_d_H_i_s') . '.xlsx';
        return Excel::download(new PurchaseExport($clientid, $ordernumber, $userid), $file_name);
    }

    function stockreportdownload($clientid, $userid)
    {
        // $data = [

        //     'client' => Clients::where('ClientID', '=', $clientid)->pluck('ClientName')->first(),

        //     'area' => Clients::where('ClientID', '=', $clientid)->pluck('Area')->first(),
        //     'location' => Clients::where('ClientID', '=', $clientid)->pluck('Location')->first(),
        //     'phone' => Clients::where('ClientID', '=', $clientid)->pluck('WhatsApp1')->first(),
        //     // 'items' => Stock::select("Product_Name", "manufacturer", "Batch", "Purchase_quantity", "Sales_quantity", "uPack", "Expiry", "Unit_Rate", "MRP", "Sales_Rate", "GST")
        //     //     ->where('ClientID', '=', $clientid)
        //     //     ->limit(1000)
        //     //     ->get()
        //     'items' => DB::select(" SELECT Product_Name, manufacturer, Batch, Purchase_quantity, Sales_quantity, uPack, Expiry, Unit_Rate, MRP, Sales_Rate, GST  FROM `stock` WHERE ClientID = " . $clientid . " and (Purchase_quantity - Sales_quantity) > 0 limit 100 ")
        // ];

        // $pdf = PDF::loadView('Admin.stockreportdownload', $data);

        // $docName = 'myStock' . $clientid;

        // $content = $pdf->download()->getOriginalContent();
        // Storage::put('public/stock/' . $clientid . '/' . $docName . ".pdf", $content);

        // $myFile = storage_path('app/public/stock/' . $clientid . '/' . $docName . '.pdf');
        // return response()->download($myFile);


        $file_name = 'Stock_Report_' . date('Y_m_d_H_i_s') . '.xlsx';
        return Excel::download(new StockExport($clientid, $userid), $file_name);
    }

    function getpurchaseorderpdf($ordernumber, $clientid, $userid)
    {
        $data = [

            'ordernumber' => $ordernumber,

            'client' => Clients::where('ClientID', '=', $clientid)->pluck('ClientName')->first(),

            'area' => Clients::where('ClientID', '=', $clientid)->pluck('Area')->first(),
            'location' => Clients::where('ClientID', '=', $clientid)->pluck('Location')->first(),
            'phone' => Clients::where('ClientID', '=', $clientid)->pluck('WhatsApp1')->first(),

            'supplier' => PurchaseOrder::where('OrderNo', '=', $ordernumber)->pluck('Supplier')->first(),

            'orderdate' => PurchaseOrder::where('OrderNo', '=', $ordernumber)->pluck('OrderDate')->first(),

            'items' => PurchaseOrder::select("ProductName", "Pack", "unitRate", "Rate", "Quantity", "TotalQuantity")
                ->where('OrderNo', '=', $ordernumber)
                ->where('ClientID', '=', $clientid)
                ->get(),

            'demo' => Users::where('id', '=', $userid)->pluck('is_demo')->first(),
        ];

        $pdf = PDF::loadView('Admin.purchaseorderpdf', $data);

        $content = $pdf->download()->getOriginalContent();
        Storage::put('public/purchaseorder/' . $clientid . '/' . $ordernumber . ".pdf", $content);

        $myFile = storage_path('app/public/purchaseorder/' . $clientid . '/' . $ordernumber . '.pdf');
        return response()->download($myFile);

        // return $data;

    }

    function purchaseorderminipdf($ordernumber, $clientid, $userid)
    {
        $data = [

            'ordernumber' => $ordernumber,

            'client' => Clients::where('ClientID', '=', $clientid)->pluck('ClientName')->first(),

            'area' => Clients::where('ClientID', '=', $clientid)->pluck('Area')->first(),
            'location' => Clients::where('ClientID', '=', $clientid)->pluck('Location')->first(),
            'phone' => Clients::where('ClientID', '=', $clientid)->pluck('WhatsApp1')->first(),

            'supplier' => PurchaseOrder::where('OrderNo', '=', $ordernumber)->pluck('Supplier')->first(),

            'orderdate' => PurchaseOrder::where('OrderNo', '=', $ordernumber)->pluck('OrderDate')->first(),

            'items' => PurchaseOrder::select("ProductName", "Pack", "unitRate", "Rate", "Quantity", "TotalQuantity")
                ->where('OrderNo', '=', $ordernumber)
                ->where('ClientID', '=', $clientid)
                ->get(),

            'demo' =>  Users::where('id', '=', $userid)->pluck('is_demo')->first()
        ];

        $pdf = PDF::loadView('Admin.purchaseorderminipdf', $data);

        $content = $pdf->download()->getOriginalContent();

        Storage::put('public/minipurchaseorder/' . $clientid . '/' . $ordernumber . ".pdf", $content);

        $myFile = storage_path('app/public/minipurchaseorder/' . $clientid . '/' . $ordernumber . '.pdf');

        return response()->download($myFile);

        // return $data;
        // return view('Admin.purchaseorderminipdf', $data);

    }

    function generateBillApi($BillNo, $clientID, $userid)
    {

        $data = [

            'ClientID' => $clientID,

            'PayQR' => Clients::where('ClientID', '=', $clientID)->pluck('pay_qr')->first(),
            'cliType' => Clients::where('ClientID', '=', $clientID)->pluck('Type2')->first(),

            'ClientName' => Clients::where('ClientID', '=', $clientID)->pluck('ClientName'),
            'Address1' => Clients::where('ClientID', '=', $clientID)->pluck('Address1'),
            'Address2' => Clients::where('ClientID', '=', $clientID)->pluck('Address2'),
            'Area' => Clients::where('ClientID', '=', $clientID)->pluck('Area'),
            'Location' => Clients::where('ClientID', '=', $clientID)->pluck('Location'),
            'Phone' => Clients::where('ClientID', '=', $clientID)->pluck('Phone'),
            'DL' => Clients::where('ClientID', '=', $clientID)->pluck('DL'),
            'DL2' => Clients::where('ClientID', '=', $clientID)->pluck('DL2'),
            'TIN' => Clients::where('ClientID', '=', $clientID)->pluck('TIN'),

            'bill_no' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('bill_no'),
            'bill_date' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('bill_date'),
            'customer_name' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('customer_name'),

            'customer_phone' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('SalesMobileNo'),
            'GSTNo' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('GSTNo'),

            'doctor' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('doctor'),
            'patient' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('patient'),

            'grand_total' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('grand_total'),

            'items' => DB::select(" select item_description,company_name,quantity,batch,expiry_date,mrp,rate,amount,SCDiscount1,SCDiscount2,IGST,SGST,CGST from sales_item1 where 
             ClientID='" . $clientID . "' and  bill_no='" . $BillNo . "' "),

            'totalItems' => SalesItemOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->count(),

            'totalQuantity' => SalesItemOne::select(
                DB::raw(" SUM(quantity) as totalQuantity ")
            )
                ->where('ClientID', '=', $clientID)
                ->where('bill_no', '=', $BillNo)->get(),

            'total' => SalesItemOne::select(
                DB::raw(" SUM(amount) as total ")
            )
                ->where('ClientID', '=', $clientID)
                ->where('bill_no', '=', $BillNo)->get(),

            'actualTotal' => SalesItemOne::select(
                DB::raw(" SUM(mrp*quantity) as actualTotal ")
            )
                ->where('ClientID', '=', $clientID)
                ->where('bill_no', '=', $BillNo)->get(),

            'taxableAmount' => SalesItemOne::select(
                DB::raw(" SUM( ( (rate*quantity) *100) / (100 + CGST + SGST) ) as taxableAmount ")
            )
                ->where('ClientID', '=', $clientID)
                ->where('bill_no', '=', $BillNo)->get(),

            'TaxableAmount' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('TaxableAmount')->first(),
            'DiscountAmount' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('DiscountAmount')->first(),

            'CGSTTotal' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('CGSTTotal')->first(),
            'SGSTTotal' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('SGSTTotal')->first(),
            'IGSTTotal' => SalesOne::where('ClientID', '=', $clientID)->where('bill_no', '=', $BillNo)->pluck('IGSTTotal')->first(),

            'color' => '#820048',
            'bgcolor' => '#af6e92',
            'demo' => Users::where('id', '=', $userid)->pluck('is_demo')->first(),
        ];

        $pdf = PDF::loadView('Admin.GSTBill', $data);

        $content = $pdf->download()->getOriginalContent();
        Storage::put('public/bills/' . $BillNo . ".pdf", $content);

        $myFile = storage_path('app/public/bills/' . $BillNo . '.pdf');
        return response()->download($myFile);

        // return $data;

    }

    function generateCashClosingBill()
    {
        // $pdf = PDF::loadView('Admin.cashClosing');

        // return $pdf->download('cashClosing.pdf');

        // $content = $pdf->download()->getOriginalContent();
        // Storage::put('public/bills/bublangcfv.pdf', $content);

        $myFile = storage_path('app/public/bills/bubla.pdf');
        return response()->download($myFile);

        // return response()->download();

        // $pdf = PDF::loadView('pdf.airbill',compact('data'))->setPaper('A4');
        // return view('Admin.myPDF');
    }

    function generateBill()
    {

        // $data = DB::select("SELECT * FROM `customer_orders` WHERE `Order_Id` = '619223944'")->get();

        $pdf = PDF::loadView('Admin.myPDF');

        return $pdf->download('nicesnippets.pdf');

        // return view('Admin.myPDF');

    }

    function getSupplierByClient(Request $request)
    {
        $client = session('LoggedUser')['ClientID'];
        $allsupplier = Supplier::where('ClientID', '=', $client)->get();

        return $allsupplier;
    }

    function getUserListByClient(Request $request)
    {
        // $allusers = DB::raw(" select UserName from Password 
        // join Login1 on Password.UserID = Login1.password_id
        // where Login1.ClientID = '".$request->client."' and Password.ClientID = '".$request->client."'
        // group by UserName ");

        $allusers = PasswordTable::select("UserName", "UserID")
            ->join('Login1', 'Password.UserID', '=', 'Login1.password_id')
            ->where('Login1.ClientID', '=', $request->client)
            ->where('Password.ClientID', '=', $request->client)
            ->groupBy('UserName')
            ->get();

        return $allusers;
        // return $client;
    }

    function getAllClients()
    {
        $allShops = Clients::all();

        return response()->json($allShops);
    }

    public static function getAllClientsForAdmin()
    {
        $allShops = Clients::all();

        return $allShops;
    }

    function deleteclient(Request $request)
    {

        $id = Clients::where('ClientID', '=', $request->clientID)->pluck('id')->first();

        return $id;

        // $client = Clients::find($id);

        // $client->delete();

        // return back()->with('success', 'Client deleted successfully');
    }

    function manageUsers()
    {
        $data = [

            // 'users' => DB::select('  select usr.*,tup.per_from,tup.per_to,c.ClientName,c.Area,c.Location from  user usr
            // left  join clients c on usr.userid = c.ClientID
            //            left join tbl_userpermissions tup on usr.id = tup.User_ID
            //            left join tbl_permissionActions tpa on tup.Package_ID = tpa.act_package
            //            group by usr.id ')

            'users' => DB::select(" select distinct usr.is_demo,usr.Name as Name,usr.Mobile,usr.UserName as UserName,usr.userid as userid,usr.IsLive ,usr.id ,'' as per_from,usr.IsUser,usr.IsShop,usr.IsStaff,usr.Admin,usr.Active,
            usr.isWebLogin,usr.isPrimary,c.ClientName,c.Area as Area,c.Location,tup.per_to,c.clientid,usr.max_connections,usr.is_MultiUser,usr.billing_type,usr.Package_ID from  user usr
                       left  join clients c on usr.userid = c.ClientID
                                  left join tbl_userpermissions tup on usr.id = tup.User_ID
                                  left join tbl_permissionActions tpa on tup.Package_ID = tpa.act_package
                                  where usr.Admin != 1 and usr.IsUser != 1 and active = 1
                                  group by usr.id ")

            //                                 UNION 

            //                           select  distinct '' as Name,'' as UserName,0 as userid,0 as IsLive,0 as id,'' as per_from,0 as IsUser,0 as IsShop,0 as IsStaff,0 as Admin,0 as Active,
            //    0 as isWebLogin,0 as isPrimary,'' as ClientName,'' as Area,'' as Location,'' as per_to,'' as clientid,0 as max_connections  from clients c
            //                                left join user u on c.ClientID = u.userid
            //                                left join tbl_userpermissions up on u.id = up.User_ID
            //                                left join tbl_permissionActions pa on up.Package_ID = pa.act_package
            //                                where pa.act_slug = 'trial' and u.Admin != 1 
            //                                GROUP BY ClientID,ClientName
            // order by per_to asc;
            // 'users' => Users::select('user.*','clients.Area','clients.Location','clients.ClientName')
            // ->leftjoin('clients','user.userid','=','clients.ClientID')
            // ->get()
        ];

        return view('Admin.manageUsers', $data);
        // return  $data;
    }

    function manageShopUsersByClient($id)
    {
        $clientid = Crypt::decrypt($id);

        $data = [

            'users' => DB::select(' select usr.*,tup.per_from,tup.per_to,c.ClientName,c.Area,c.Location from  user usr
            left  join clients c on usr.userid = c.ClientID
                       left join tbl_userpermissions tup on usr.id = tup.User_ID
                       left join tbl_permissionActions tpa on tup.Package_ID = tpa.act_package
                       where usr.userid = ' . $clientid . ' and usr.IsShop = 1
                       group by usr.id '),

            'cid' => $clientid
            // 'users' => Users::select('user.*','clients.Area','clients.Location','clients.ClientName')
            // ->leftjoin('clients','user.userid','=','clients.ClientID')
            // ->get()
        ];

        return view('Admin.manageShopUsers', $data);
        // return  $data;
    }

    function manageCustomerUsersByClient($id)
    {
        $clientid = Crypt::decrypt($id);

        $data = [

            'clientname' => Clients::where('ClientID', '=', $clientid)->pluck('ClientName')->first(),

            'users' => DB::select(' select usr.*,tup.per_from,tup.per_to,c.ClientName,c.Area,c.Location from  user usr
            left  join clients c on usr.userid = c.ClientID
                       left join tbl_userpermissions tup on usr.id = tup.User_ID
                       left join tbl_permissionActions tpa on tup.Package_ID = tpa.act_package
                       where usr.userid = ' . $clientid . ' and usr.IsUser = 1
                       group by usr.id ')
            // 'users' => Users::select('user.*','clients.Area','clients.Location','clients.ClientName')
            // ->leftjoin('clients','user.userid','=','clients.ClientID')
            // ->get()
        ];

        return view('Admin.viewCustomerAppUsers', $data);
        // return  $data;
    }

    function manageLogginedUsers()
    {
        $data = [

            'users' => Users::select('user.*', 'clients.Area', 'clients.Location', 'clients.ClientName')
                ->leftjoin('clients', 'user.userid', '=', 'clients.ClientID')
                ->where('app_login_count', '!=', 0)
                ->get()
        ];

        return view('Admin.manageLogginedUsers', $data);
    }

    function customerappusers()
    {
        $data = [

            'users' => Users::select('user.*', 'clients.Area', 'clients.Location', 'clients.ClientName')
                ->leftjoin('clients', 'user.userid', '=', 'clients.ClientID')
                ->where('IsUser', '=', 1)
                ->get()
        ];

        return view('Admin.customerappusers', $data);
    }

    function getAllClientsForStockReport()
    {
        $allShops = Clients::select('clients.ClientName', 'clients.ClientID')
            ->join('tbl_reportpermission', 'clients.ClientID', '=', 'tbl_reportpermission.client_id')
            ->where('tbl_reportpermission.slug_url', '=', 'STKRPT')
            ->where('tbl_reportpermission.status', '=', 1)->get();

        return response()->json($allShops);
    }

    function getAllClientsForSaleReport()
    {
        $allShops = Clients::select('clients.ClientName', 'clients.ClientID')
            ->join('tbl_reportpermission', 'clients.ClientID', '=', 'tbl_reportpermission.client_id')
            ->where('tbl_reportpermission.slug_url', '=', 'SALERPT')
            ->where('tbl_reportpermission.status', '=', 1)->get();

        return response()->json($allShops);
    }

    function getAllClientsForPurchaseReport()
    {
        $allShops = Clients::select('clients.ClientName', 'clients.ClientID')
            ->join('tbl_reportpermission', 'clients.ClientID', '=', 'tbl_reportpermission.client_id')
            ->where('tbl_reportpermission.slug_url', '=', 'PURRPT')
            ->where('tbl_reportpermission.status', '=', 1)->get();

        return response()->json($allShops);
    }

    function manageClientsNew($type)
    {

        if ($type == 'A') {

            $data = [
                'clients' => DB::select(" select ClientID,ClientName,Area,Location,Type, SUM(shop) as shopcount, SUM(customer) as customercount,max(endDate) as enddt,is_SingleUser,is_MultiUser,pay_qr_status,sum(MonthlyTypeCount) as MonthlyTypeCount,sum(YearlyTypeCount) as YearlyTypeCount,sum(platinumcnt) as platinumcnt
                ,sum(goldcnt) as goldcnt,sum(silvercnt) as silvercnt  from (
                
                                    SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,0 as shop ,COUNT(u2.id) as customer,'1900-01-01' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt  FROM clients c
                                    left join ( select userid,IsUser,id from user where IsUser = 1 ) u2 on c.ClientID = u2.userid 
                                    GROUP BY c.ClientID
                                    
                                    union
                                        
                                    SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,COUNT(u1.id) as shop, 0 as customer,'1900-01-01' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt  FROM clients c
                                    left join ( select userid,IsShop,id from user where IsShop = 1 ) u1 on c.ClientID = u1.userid 
                                    GROUP BY c.ClientID 
                                    
                                    UNION 
                                        
                                    select ClientID,ClientName,area,location,type,0 as shop,0 as customer,MAX(up.per_to) as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from clients c
                                            left join user u on c.ClientID = u.userid
                                            left join tbl_userpermissions up on u.id = up.User_ID
                                            left join tbl_permissionActions pa on up.Package_ID = pa.act_package
                                            where pa.act_slug = 'trial' and u.Admin != 1 
                                            GROUP BY ClientID,ClientName
                    Union
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,count(usr.id) as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where billing_type='M'  group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status
                     
                    Union
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,count(usr.id) as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where billing_type='Y'
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status
                    
                 UNION
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,count(usr.id) as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where package_id = 3
                     and c.clientid is not null
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                    
                    UNION
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,count(usr.id) as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where package_id = 2
                     and c.clientid is not null
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                    
                     UNION
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,count(usr.id)  as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where package_id = 1
                     and c.clientid is not null
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                    
                    
                                        ) cnt group by ClientID,ClientName,Area,Location,Type  order by enddt,Clientname asc;"), 'type' => 'A'
            ];
        } else if ($type == 'S') {

            $data = ['clients' => DB::select(" select ClientID,ClientName,Area,Location,Type, SUM(shop) as shopcount, SUM(customer) as customercount,max(endDate) as enddt,is_SingleUser,is_MultiUser,pay_qr_status,sum(MonthlyTypeCount) as MonthlyTypeCount,sum(YearlyTypeCount) as YearlyTypeCount,sum(platinumcnt) as platinumcnt
            ,sum(goldcnt) as goldcnt,sum(silvercnt) as silvercnt  from (
            
                                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,0 as shop ,COUNT(u2.id) as customer,'1900-01-01' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt  FROM clients c
                                left join ( select userid,IsUser,id from user where IsUser = 1 ) u2 on c.ClientID = u2.userid 
                                GROUP BY c.ClientID
                                
                                union
                                    
                                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,COUNT(u1.id) as shop, 0 as customer,'1900-01-01' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt  FROM clients c
                                left join ( select userid,IsShop,id from user where IsShop = 1 ) u1 on c.ClientID = u1.userid 
                                GROUP BY c.ClientID 
                                
                                UNION 
                                    
                                select ClientID,ClientName,area,location,type,0 as shop,0 as customer,MAX(up.per_to) as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from clients c
                                        left join user u on c.ClientID = u.userid
                                        left join tbl_userpermissions up on u.id = up.User_ID
                                        left join tbl_permissionActions pa on up.Package_ID = pa.act_package
                                        where pa.act_slug = 'trial' and u.Admin != 1 
                                        GROUP BY ClientID,ClientName
                Union
                select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,count(usr.id) as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                usr.userid = c.ClientID where billing_type='M'  group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status
                 
                Union
                
                select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,count(usr.id) as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                usr.userid = c.ClientID where billing_type='Y'
                 group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status
                
             UNION
                
                select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,count(usr.id) as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                usr.userid = c.ClientID where package_id = 3
                 and c.clientid is not null
                 group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                
                UNION
                
                select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,count(usr.id) as goldcnt,0 as silvercnt from user usr left join clients c on 
                usr.userid = c.ClientID where package_id = 2
                 and c.clientid is not null
                 group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                
                 UNION
                
                select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,count(usr.id)  as silvercnt from user usr left join clients c on 
                usr.userid = c.ClientID where package_id = 1
                 and c.clientid is not null
                 group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                
                
                                    ) cnt where is_MultiUser = 'S' group by ClientID,ClientName,Area,Location,Type  order by enddt,Clientname asc;"), 'type' => 'S'];
        } else if ($type == 'M') {
            $data = [
                'clients' => DB::select(" select ClientID,ClientName,Area,Location,Type, SUM(shop) as shopcount, SUM(customer) as customercount,max(endDate) as enddt,is_SingleUser,is_MultiUser,pay_qr_status,sum(MonthlyTypeCount) as MonthlyTypeCount,sum(YearlyTypeCount) as YearlyTypeCount,sum(platinumcnt) as platinumcnt
                ,sum(goldcnt) as goldcnt,sum(silvercnt) as silvercnt  from (
                
                                    SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,0 as shop ,COUNT(u2.id) as customer,'1900-01-01' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt  FROM clients c
                                    left join ( select userid,IsUser,id from user where IsUser = 1 ) u2 on c.ClientID = u2.userid 
                                    GROUP BY c.ClientID
                                    
                                    union
                                        
                                    SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,COUNT(u1.id) as shop, 0 as customer,'1900-01-01' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt  FROM clients c
                                    left join ( select userid,IsShop,id from user where IsShop = 1 ) u1 on c.ClientID = u1.userid 
                                    GROUP BY c.ClientID 
                                    
                                    UNION 
                                        
                                    select ClientID,ClientName,area,location,type,0 as shop,0 as customer,MAX(up.per_to) as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from clients c
                                            left join user u on c.ClientID = u.userid
                                            left join tbl_userpermissions up on u.id = up.User_ID
                                            left join tbl_permissionActions pa on up.Package_ID = pa.act_package
                                            where pa.act_slug = 'trial' and u.Admin != 1 
                                            GROUP BY ClientID,ClientName
                    Union
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,count(usr.id) as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where billing_type='M'  group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status
                     
                    Union
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,count(usr.id) as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where billing_type='Y'
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status
                    
                 UNION
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,count(usr.id) as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where package_id = 3
                     and c.clientid is not null
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                    
                    UNION
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,count(usr.id) as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where package_id = 2
                     and c.clientid is not null
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                    
                     UNION
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,count(usr.id)  as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where package_id = 1
                     and c.clientid is not null
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                    
                    
                                        ) cnt where is_MultiUser = 'M' group by ClientID,ClientName,Area,Location,Type  order by enddt,Clientname asc;"), 'type' => 'M'
            ];
        } else if ($type == 'SM') {

            $data = [
                'clients' => DB::select(" select ClientID,ClientName,Area,Location,Type, SUM(shop) as shopcount, SUM(customer) as customercount,max(endDate) as enddt,is_SingleUser,is_MultiUser,pay_qr_status,sum(MonthlyTypeCount) as MonthlyTypeCount,sum(YearlyTypeCount) as YearlyTypeCount,sum(platinumcnt) as platinumcnt
                ,sum(goldcnt) as goldcnt,sum(silvercnt) as silvercnt  from (
                
                                    SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,0 as shop ,COUNT(u2.id) as customer,'1900-01-01' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt  FROM clients c
                                    left join ( select userid,IsUser,id from user where IsUser = 1 ) u2 on c.ClientID = u2.userid 
                                    GROUP BY c.ClientID
                                    
                                    union
                                        
                                    SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,COUNT(u1.id) as shop, 0 as customer,'1900-01-01' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt  FROM clients c
                                    left join ( select userid,IsShop,id from user where IsShop = 1 ) u1 on c.ClientID = u1.userid 
                                    GROUP BY c.ClientID 
                                    
                                    UNION 
                                        
                                    select ClientID,ClientName,area,location,type,0 as shop,0 as customer,MAX(up.per_to) as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from clients c
                                            left join user u on c.ClientID = u.userid
                                            left join tbl_userpermissions up on u.id = up.User_ID
                                            left join tbl_permissionActions pa on up.Package_ID = pa.act_package
                                            where pa.act_slug = 'trial' and u.Admin != 1 
                                            GROUP BY ClientID,ClientName
                    Union
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,count(usr.id) as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where billing_type='M'  group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status
                     
                    Union
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,count(usr.id) as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where billing_type='Y'
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status
                    
                 UNION
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,count(usr.id) as platinumcnt,0 as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where package_id = 3
                     and c.clientid is not null
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                    
                    UNION
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,count(usr.id) as goldcnt,0 as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where package_id = 2
                     and c.clientid is not null
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                    
                     UNION
                    
                    select userid as ClientID,ClientName,area,location,type,0 as shop,0 as customer,'1900-01-01' as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status,0 as MonthlyTypeCount,0 as YearlyTypeCount,0 as platinumcnt,0 as goldcnt,count(usr.id)  as silvercnt from user usr left join clients c on 
                    usr.userid = c.ClientID where package_id = 1
                     and c.clientid is not null
                     group by userid,ClientName,area,location,type,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status 
                    
                    
                                        ) cnt where is_MultiUser = 'SM' group by ClientID,ClientName,Area,Location,Type  order by enddt,Clientname asc;"), 'type' => 'SM'
            ];
        }

        // return $data;
        return view('Admin.manageclients', $data);
    }

    function manageClients()
    {

        $data = [
            'clients' => DB::select(" select ClientID,ClientName,Area,Location,Type, SUM(shop) as shopcount, SUM(customer) as customercount,max(endDate) as enddt,is_SingleUser,is_MultiUser,pay_qr_status  from (

                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,0 as shop ,COUNT(u2.id) as customer,'' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  FROM clients c
                left join ( select userid,IsUser,id from user where IsUser = 1 ) u2 on c.ClientID = u2.userid 
                GROUP BY c.ClientID
                
                union
                    
                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,COUNT(u1.id) as shop, 0 as customer,'' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  FROM clients c
                left join ( select userid,IsShop,id from user where IsShop = 1 ) u1 on c.ClientID = u1.userid 
                GROUP BY c.ClientID 
                
                UNION 
                    
                select ClientID,ClientName,area,location,type,0 as shop,0 as customer,MAX(up.per_to) as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  from clients c
                        left join user u on c.ClientID = u.userid
                        left join tbl_userpermissions up on u.id = up.User_ID
                        left join tbl_permissionActions pa on up.Package_ID = pa.act_package
                        where pa.act_slug = 'trial' and u.Admin != 1 
                        GROUP BY ClientID,ClientName
                    ) cnt group by ClientID,ClientName,Area,Location,Type ")
        ];

        return view('Admin.manageclients');
    }



    function manageClientsAjax($type)
    {

        $returnString = "";
        $num = 1;

        if ($type == 'A') {
            $clientz = DB::select(" select ClientID,ClientName,Area,Location,Type, SUM(shop) as shopcount, SUM(customer) as customercount,max(endDate) as enddt,is_SingleUser,is_MultiUser,pay_qr_status  from (

                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,0 as shop ,COUNT(u2.id) as customer,'' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  FROM clients c
                left join ( select userid,IsUser,id from user where IsUser = 1 ) u2 on c.ClientID = u2.userid 
                GROUP BY c.ClientID
                
                union
                    
                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,COUNT(u1.id) as shop, 0 as customer,'' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  FROM clients c
                left join ( select userid,IsShop,id from user where IsShop = 1 ) u1 on c.ClientID = u1.userid 
                GROUP BY c.ClientID 
                
                UNION 
                    
                select ClientID,ClientName,area,location,type,0 as shop,0 as customer,MAX(up.per_to) as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  from clients c
                        left join user u on c.ClientID = u.userid
                        left join tbl_userpermissions up on u.id = up.User_ID
                        left join tbl_permissionActions pa on up.Package_ID = pa.act_package
                        where pa.act_slug = 'trial' and u.Admin != 1 
                        GROUP BY ClientID,ClientName
                    ) cnt group by ClientID,ClientName,Area,Location,Type ");
        } else if ($type == 'S') {
            $clientz = DB::select(" select ClientID,ClientName,Area,Location,Type, SUM(shop) as shopcount, SUM(customer) as customercount,max(endDate) as enddt,is_SingleUser,is_MultiUser,pay_qr_status  from (

                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,0 as shop ,COUNT(u2.id) as customer,'' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  FROM clients c
                left join ( select userid,IsUser,id from user where IsUser = 1 ) u2 on c.ClientID = u2.userid 
                GROUP BY c.ClientID
                
                union
                    
                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,COUNT(u1.id) as shop, 0 as customer,'' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  FROM clients c
                left join ( select userid,IsShop,id from user where IsShop = 1 ) u1 on c.ClientID = u1.userid 
                GROUP BY c.ClientID 
                
                UNION 
                    
                select ClientID,ClientName,area,location,type,0 as shop,0 as customer,MAX(up.per_to) as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  from clients c
                        left join user u on c.ClientID = u.userid
                        left join tbl_userpermissions up on u.id = up.User_ID
                        left join tbl_permissionActions pa on up.Package_ID = pa.act_package
                        where pa.act_slug = 'trial' and u.Admin != 1 
                        GROUP BY ClientID,ClientName
                    ) cnt where is_SingleUser = 1 group by ClientID,ClientName,Area,Location,Type ");
        } else if ($type == 'M') {
            $clientz = DB::select(" select ClientID,ClientName,Area,Location,Type, SUM(shop) as shopcount, SUM(customer) as customercount,max(endDate) as enddt,is_SingleUser,is_MultiUser,pay_qr_status  from (

                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,0 as shop ,COUNT(u2.id) as customer,'' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  FROM clients c
                left join ( select userid,IsUser,id from user where IsUser = 1 ) u2 on c.ClientID = u2.userid 
                GROUP BY c.ClientID
                
                union
                    
                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,COUNT(u1.id) as shop, 0 as customer,'' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  FROM clients c
                left join ( select userid,IsShop,id from user where IsShop = 1 ) u1 on c.ClientID = u1.userid 
                GROUP BY c.ClientID 
                
                UNION 
                    
                select ClientID,ClientName,area,location,type,0 as shop,0 as customer,MAX(up.per_to) as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  from clients c
                        left join user u on c.ClientID = u.userid
                        left join tbl_userpermissions up on u.id = up.User_ID
                        left join tbl_permissionActions pa on up.Package_ID = pa.act_package
                        where pa.act_slug = 'trial' and u.Admin != 1 
                        GROUP BY ClientID,ClientName
                    ) cnt where is_MultiUser = 1 group by ClientID,ClientName,Area,Location,Type ");
        } else if ($type == 'SM') {

            $clientz = DB::select(" select ClientID,ClientName,Area,Location,Type, SUM(shop) as shopcount, SUM(customer) as customercount,max(endDate) as enddt,is_SingleUser,is_MultiUser,pay_qr_status  from (

                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,0 as shop ,COUNT(u2.id) as customer,'' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  FROM clients c
                left join ( select userid,IsUser,id from user where IsUser = 1 ) u2 on c.ClientID = u2.userid 
                GROUP BY c.ClientID
                
                union
                    
                SELECT c.ClientID,c.ClientName,c.Area,c.Location,c.Type,COUNT(u1.id) as shop, 0 as customer,'' as enddate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  FROM clients c
                left join ( select userid,IsShop,id from user where IsShop = 1 ) u1 on c.ClientID = u1.userid 
                GROUP BY c.ClientID 
                
                UNION 
                    
                select ClientID,ClientName,area,location,type,0 as shop,0 as customer,MAX(up.per_to) as endDate,c.is_SingleUser,c.is_MultiUser,c.pay_qr_status  from clients c
                        left join user u on c.ClientID = u.userid
                        left join tbl_userpermissions up on u.id = up.User_ID
                        left join tbl_permissionActions pa on up.Package_ID = pa.act_package
                        where pa.act_slug = 'trial' and u.Admin != 1 
                        GROUP BY ClientID,ClientName
                    ) cnt where is_MultiUser = 1 and is_SingleUser = 1 group by ClientID,ClientName,Area,Location,Type ");
        }


        $returnString .= "<table width='100%' data-page-length='10' id='example2' class='table table-striped table-bordered'>
            <thead class='table-secondary'>
                <tr>
                    <th width='5%'>Sl No</th>
                    <th width='40%'>Client Name</th>
                    <th width='10%'>Managed Shops & User Count</th>
                    <th width='10%'>Privilege Date</th>
    
    
                    <th width='5%'>Action</th>
                </tr>
            </thead>
            <tbody>";

        foreach ($clientz as $client) {
            $returnString .= " <tr>
                <td> " . $num . " </td>
                <td> <label class='text-danger'>  " . $client->ClientID . "  </label> <br> " . $client->ClientName . " <br> " . $client->Area . " <br> " . $client->Location . " <br>
                <br>";

            if ($client->Type == 'R') {
                $returnString .= "<label style='color: green;'>Retail</label>";
            } else {
                $returnString .= "<label style='color: red;'>Wholesale</label>";
            }

            $returnString .= "<label class='text-danger'> " . MainAcontroller::convertDateandTime(GeneralController::getLastUpdatedTime($client->ClientID)) . " </label> 
            <br>";

            $returnString .= " <label class='text-danger'> " . GeneralController::checkIsAssignedToAnyAnotherShop($client->ClientID) . " </label> ";

            $returnString .= "</td>  <td> <select class='form-control'>";

            foreach (GeneralController::managedShopListByclientID($client->ClientID) as $value) {

                $returnString .= "<option value=" . $value->Managed_ClientID . "> " . $value->Managed_ClientID . " : " . $value->ClientName . " - " . $value->Area . " - " . $value->Location . " </option>";
            }


            $returnString .= "</select> 
    
                <br>";

            if ($client->is_MultiUser == 1) {
                $returnString .= "<a href='https://api.digitronicsindia.com/aswas/public/manageAssignedShopList/" . Crypt::encrypt($client->ClientID) . "' <label > Assigned Shop Count : " . GeneralController::managedShopListCountByclientID($client->ClientID) . " </label> </a>";
            }

            if ($client->is_SingleUser == 1) {
                $returnString .= "<a href='https://api.digitronicsindia.com/aswas/public/manageUsersByClient/" . Crypt::encrypt($client->ClientID) . "' <label > Users Count : " . GeneralController::shopCountByShopID($client->ClientID) . " </label> </a>";
            }

            $returnString .= " <br>
        
                <a href='https://api.digitronicsindia.com/aswas/public/manageCustomerUsersByClient/" . Crypt::encrypt($client->ClientID) . "'> <label > Customer App : " . GeneralController::userCountByShopID($client->ClientID) . " </label> </a>
                 ";

            $returnString .= "</td>";

            if ($client->enddt == '') {
                $returnString .= " <td> Not found </td> ";
            } else {
                $returnString .= " <td> <a >  " . $client->enddt . "  </a>  ";
            }

            $returnString .= " <td> <div class='col'>
                 <div class='dropdown'>
                     <button class='btn btn-primary dropdown-toggle' type='button' data-bs-toggle='dropdown' aria-expanded='false'>Actions</button>
                     <ul class='dropdown-menu'>
                         <li>
                             <a href='https://api.digitronicsindia.com/aswas/public/clientDetails/" . Crypt::encrypt($client->ClientID) . "' class='dropdown-item' data-bs-toggle='tooltip' data-bs-placement='bottom' title='view details'><i class='bi bi-eye-fill'></i> View Details </a>
                         </li>
    
                         <!--  <li>
                             <a href='https://api.digitronicsindia.com/aswas/public/managePermissions/" . Crypt::encrypt($client->ClientID) . "' style='background: transparent;' type='submit' class='dropdown-item' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Manage Permissions'><i class='bi bi-pencil-fill'></i> Manage Permissions</a>
                      </li> --> 
    
                         <li>
                             <a style='background: transparent;' class='dropdown-item deleteClient' data-id='{{$client->ClientID}}' title='Delete'> <i class='fadeIn animated bx bx-trash'></i> Delete Client </a>
                         </li>
    
                         <li>
                             <a style='background: transparent;' data-id='{{$client->ClientID}}' class='dropdown-item deleteClientDatabase' title='Delete From Database'> <i class='fadeIn animated bx bx-trash'></i> Delete Client From DB</a>
                         </li>";

            if ($client->pay_qr_status == 1) {
                $returnString .= "<li>
                            <a href='https://api.digitronicsindia.com/aswas/public/updateQRUploadStatus/" . Crypt::encrypt($client->ClientID) . "' class='dropdown-item' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Change QR Update Status'><i class='fadeIn animated bx bx-upload'></i> Update QR Upload Status</a>
                        </li>";
            }

            $returnString .= " <!-- <li>
                         <a style='background: transparent;' data-id='{{$client->ClientID}}' class='dropdown-item manageMaxConnections' title='Delete From Database'> <i class='fadeIn animated bx bx-trash'></i> Delete Client From DB</a>
                     </li> --> 
                         
    
                     </ul>
                 </div>
             </div> </td> ";

            $num++;
        }

        $returnString .= "</tbody>  </table>";

        $data = [
            'loadData' => $returnString
        ];



        return $returnString;
    }

    function clientDetails($hash)
    {
        $id = Crypt::decrypt($hash);
        $data = [

            'clients' => Clients::where('ClientID', '=', $id)->get(),
            'clientname' => Clients::where('ClientID', '=', $id)->pluck('ClientName')

        ];
        return view('Admin.clientdetails', $data);
    }
}
