<?php

namespace App\Models;

use App\Models\Crud;
use CodeIgniter\Model;

class homeModel extends Model
{

    public function FetchAllRecords($schema, $limit = '', $offset = '')
    {
        $Keyword = $_REQUEST['search']['value'];
        $Crud = new Crud();
        $SQL = 'SELECT * FROM "' . $schema . '"."records" WHERE "UID" > 0 ';
        if ($Keyword != '') {
            $SQL .= ' AND ("records"."Phone" LIKE \'%' . $Keyword . '%\' OR "records"."JornayaID" LIKE \'%' . $Keyword . '%\' ) ';
        }
        $SQL .= 'ORDER BY "records"."UID" DESC';
        if ($limit != '' && $limit != -1 && $offset != '') {
            $SQL .= ' LIMIT   ' . $limit . ' OFFSET  ' . $offset . '     ';
        }
        $FinalArray = $Crud->ExecuteSQL($SQL);
        return $FinalArray;
    }

    public function FetchAllRecord($limit = '', $offset = '', $Keyword = '')
    {

        $Crud = new Crud();
       $SQL = 'SELECT "public"."records".*, public."area_state"."StateCode" as "areaState" FROM "public"."records" 
        LEFT JOIN public."area_state" on public."area_state"."AreaCode"::bigint = substring("public"."records"."reqPhoneNumber", 0, 4)::bigint 
        WHERE "public"."records"."UID" > 0 AND "public"."records"."reqPhoneNumber" != \'\' ';
        if ($Keyword != '') {
            $SQL .= ' AND ("records"."bidID" LIKE \'%' . $Keyword . '%\' ) OR  
            
            ("records"."phoneNumber" LIKE \'%' . $Keyword . '%\' ) 
             OR ("records"."reqPhoneNumber" LIKE \'%' . $Keyword . '%\' ) 
             OR ("records"."reqZipCode" LIKE \'%' . $Keyword . '%\' )   
             ';
        }
        $SQL .= ' ORDER BY "records"."UID" DESC';
        if ($limit != '' && $limit != -1 && $offset != '') {
            $SQL .= ' LIMIT   ' . $limit . ' OFFSET  ' . $offset . '     ';
        }
        $FinalArray = $Crud->ExecuteSQL($SQL);
        return $FinalArray;
    }

    public function FetchAllMasterRecord($limit = '', $offset = '', $Keyword = '')
    {

        $session = session();
        $SessionFilters = $session->get('MasterAdminSessionFilter');

        if (isset($SessionFilters['bid_id']) && $SessionFilters['bid_id'] != '') {
            $bid_id = $SessionFilters['bid_id'];
        } else {
            $bid_id = '';
        }
        if (isset($SessionFilters['date']) && $SessionFilters['date'] != '') {
            $date = $SessionFilters['date'];
        } else {
            $date = '';
        }
        if (isset($SessionFilters['phone']) && $SessionFilters['phone'] != '') {
            $phone = $SessionFilters['phone'];
        } else {
            $phone = '';
        }
        if (isset($SessionFilters['req_phone']) && $SessionFilters['req_phone'] != '') {
            $req_phone = $SessionFilters['req_phone'];
        } else {
            $req_phone = '';
        }


        $Crud = new Crud();
        $SQL = 'SELECT "public"."records".*, public."area_state"."StateCode" as "areaState" FROM "public"."records" 
        LEFT JOIN public."area_state" on public."area_state"."AreaCode"::bigint = substring("public"."records"."reqPhoneNumber", 0, 4)::bigint 
        WHERE "public"."records"."UID" > 0 AND "public"."records"."reqPhoneNumber" != \'\' ';
        if ($Keyword != '') {
            $SQL .= ' AND ("records"."bidID" LIKE \'%' . $Keyword . '%\' ) OR  
            
            ("records"."phoneNumber" LIKE \'%' . $Keyword . '%\' ) 
             OR ("records"."reqPhoneNumber" LIKE \'%' . $Keyword . '%\' ) 
             OR ("records"."reqZipCode" LIKE \'%' . $Keyword . '%\' )   
             OR ("records"."sipAddress" LIKE \'%' . $Keyword . '%\' )   
             ';
        }
        if (isset($date) && $date != '') {
            $SQL .= ' AND "records"."SystemDate" BETWEEN \'' . $date . ' 00:00:01\' AND \'' . $date . ' 23:59:59\'';
        }
        if (isset($bid_id) && $bid_id != '') {
            $SQL .= ' AND "records"."bidID" = \'' . trim($bid_id) . '\'';
        }
        if (isset($phone) && $phone != '') {
            $SQL .= ' AND "records"."phoneNumber" = \'' . trim($phone) . '\'';
        }
        if (isset($req_phone) && $req_phone != '') {
            $SQL .= ' AND "records"."reqPhoneNumber" = \'' . trim($req_phone) . '\'';
        }
        $SQL .= ' ORDER BY "records"."UID" DESC';
        if ($limit != '' && $limit != -1 && $offset != '') {
            $SQL .= ' LIMIT   ' . $limit . ' OFFSET  ' . $offset . '     ';
        }

        $FinalArray = $Crud->ExecuteSQL($SQL);
        return $FinalArray;
    }

    public function GetAllWebsitesDomain()
    {
        $Crud = new Crud();
        $SQL = 'SELECT * FROM "public"."websites"';
        $FinalArray = $Crud->ExecuteSQL($SQL);
        return $FinalArray;
    }

    public function GetAllWebsitesDomainByUID($UID)
    {
        $Crud = new Crud();
        $SQL = 'SELECT "public"."user_website"."WebsiteUID" AS "WebsiteName" 
                FROM "public"."user_website"
                WHERE "public"."user_website"."UserUID" =  ' . $UID . '    ';
        $FinalArray = $Crud->ExecuteSQL($SQL);

        $final = array();
        foreach ($FinalArray as $record) {
            $final[] = $record['WebsiteName'];
        }
        return $final;
    }
}
