<?php
namespace App\Model;

use App\Model\BaseModel;
use App\Core\Service\Logger;
use App\Config\Main;
use Exception;

class MasterModel extends BaseModel
{

    public function __construct()
    {
        parent::__construct();
    }

    public function getMasterTableFields($table_name = '')
    {
        try {
            if (!$table_name) {
                throw new \Exception("Table name is required.");
            }

            $sql = "SHOW COLUMNS FROM $table_name";
            $result = $this->fetchAllData($sql);

            // Extract only the column names from the result
            $retArr = array();
            foreach ($result['data'] as $value) {
                $retArr[] = $value['Field'];
            }

            return ["status" => true, "fields" => $retArr];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getScalerCol($column, $table, $where, $masterDb = NULL)
    {
        try {
            if (empty($column) || empty($table) || empty($where)) {
                throw new \Exception("Column, table, and where conditions are required.");
            }

            $sql = "SELECT " . $column . " FROM `" . $table . "` WHERE " . $where;
            $result = $this->fetchSingleData($sql, $masterDb);

            return ["status" => true, "data" => $result];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getOptionList($dropdown_names_list, $comp_id = 1, $where = [], $dropdownConfigs = [])
    {
        try {
            if (empty($dropdown_names_list)) {
                throw new \Exception('Dropdown name list is required.');
            }

            $newElement = ['id' => 0, 'text' => 'Select'];
            $ret_data = [];
            $clientDb = $this->client_db_prefix . $comp_id;
            $mainDb   = "sanordo_db";

            if (empty($dropdownConfigs)) {
                $dropdownConfigs = Main::getDropdownConfig($comp_id);
            }

            foreach ($dropdown_names_list as $name) {
                if ($name == 'dial_api_variables') {
                    $vars = Main::$api_variables;
                    foreach ($vars as $k => $v) {
                        $ret_data[$name][] = ['id' => $k, 'text' => $v];
                    }
                    continue;
                }
                if ($name == 'import_permission') {
                    $vars = Main::$import_permission;
                    $ret_data[$name] = $vars;
                    continue;
                }
                if ($name == 'apiMethod') {
                    $vars = Main::$leadSourceAPIMethod;
                    $ret_data[$name] = $vars;
                    continue;
                }
                if ($name == 'OrderStatusType') {
                    $vars = Main::$OrderStatusType;
                    $ret_data[$name] = $vars;
                    continue;
                }
                if ($name == 'templateType') {
                    $vars = [array("id" => 'Email', "text" => 'Email'), array("id" => 'SMS', "text" => 'SMS'), array("id" => 'WhatsApp', "text" => 'WhatsApp'),];
                    $ret_data[$name] = $vars;
                    continue;
                }
                if (!isset($dropdownConfigs[$name])) {
                    throw new \Exception("Invalid dropdown name: " . $name);
                }

                $config = $dropdownConfigs[$name];

                // Decide DB prefix
                $dbName = !empty($config['use_client']) ? $clientDb : $mainDb;
                $dbPrefix = $dbName ? "`$dbName`." : '';

                $idField = $config['id'];
                $textField = $config['text'];
                $table = $config['table'];
                // Build full SQL
                $table_alias = !empty($config['table_alias']) ? !empty($config['table_alias'][$table]) ? $config['table_alias'][$table] : '' : '';
                if (isset($config['selectOtherField'])) {
                    $otherField = $config['selectOtherField'];
                    $sql = "SELECT $idField AS id, $textField AS text,$otherField FROM {$dbPrefix}`$table` $table_alias";
                } else {
                    $sql = "SELECT $idField AS id, $textField AS text FROM {$dbPrefix}`$table` $table_alias";
                }
                if (!empty($config['left_join'])) {
                    foreach ($config['left_join'] as $index => $joincnf) {
                        $join_db = !empty($joincnf['use_client']) ? $clientDb : $mainDb;
                        $join_db_prefix = $join_db ? "`$join_db`." : '';
                        $join_table_alias = !empty($config['table_alias']) ? !empty($config['table_alias'][$joincnf['table']]) ? $config['table_alias'][$joincnf['table']] : '' : '';
                        $sql .= " LEFT JOIN " . $join_db_prefix . $joincnf['table'] . " " . $join_table_alias . " ON (" . $joincnf['join_predicate'] . ") ";
                    }
                }

                if (!empty($config['where'])) {
                    $sql .= " WHERE " . $config['where'];
                }

                if (!empty($where)) {
                    if (array_key_exists($name, $where)) {
                        if (!empty($where[$name])) {
                            if ($name == 'pincode') {
                                $condition = $this->whereString($where[$name], array(), array('pincode' => 'LIKE'), array());
                            } else if ($name == 'users') {
                                $condition = $this->whereString($where[$name], array(), array('user_id' => 'IN'), array());
                            } else if ($name == 'itemList') {
                                $condition = $this->whereString($where[$name], array(), array('name' => 'LIKE', 'item_code' => 'LIKE', 'item_desc' => 'LIKE'), array('name' => 'item_name'));
                            } else {
                                $condition = $this->whereString($where[$name], [], [], []);
                            }
                            if (!empty($condition)) {
                                $sql .= " AND " . $condition;
                            }
                        }
                    }
                }
                if (!empty($config['order_by'])) {
                    $sql .= " ORDER BY " . $config['order_by'];
                }
                if($name == 'pincode') {
                    $sql .= " LIMIT 25 ";
                }
                $result = $this->fetchAllData($sql);
                if ($result) {
                    if (!empty($config['preselect'])) {

                        if ($result['status']) {
                            $result['data'] = $result['data'] ? $result['data'] : [];
                        } else {
                            throw new \Exception($result['error']);
                        }
                        array_unshift($result, $newElement);
                    }
                    $ret_data[$name] = !empty($result['data']) ? $result['data'] : [];
                } else {
                    $ret_data[$name] = [];
                }
            }

            return ["status" => true, "data" => $ret_data];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getMasterData($db, $master_name = null, $where = '', $page = 1, $page_limit = 10, $sortBy = '', $join_DB = 'sanordo_db')
    {
        try {
            if (empty($db) || empty($master_name)) {
                throw new \Exception("Database prefix and table name are required.");
            }

            $dbPrefix = $db;
            $table = $master_name;
            $whereClause = '';
            if (!empty($where)) {
                $whereClause = "WHERE $where";
            }

            // Build FROM and JOIN logic
            switch ($master_name) {
                case 'coupon_master':
                    $baseQuery =  "id,coupon_title,coupon_count,coupon_status_type,min_order_amount,discount,maximum_discount_amount,user_apply_count,coupon_type,date_format(date(valid_from),'%d-%m-%Y') as valid_from,date_format(date(valid_to),'%d-%m-%Y') as valid_to,status FROM `$dbPrefix`.`$table`";
                    break;
                case 'location_city':
                    $baseQuery =  "t1.id,t1.city_name,t2.state_name,t1.status FROM `$dbPrefix`.`$table` t1 LEFT JOIN `sanordo_db`.`admin_state_master` t2 ON t2.state_id = t1.state_id";
                    break;
                case 'location_pincode':
                    $baseQuery =  "t1.id,t1.pincode,t2.city_name,t3.state_name,t1.status FROM `$dbPrefix`.`$table` t1 LEFT JOIN `$dbPrefix`.`location_city` t2 ON t2.id = t1.city_id LEFT JOIN `sanordo_db`.`admin_state_master` t3 ON t3.state_id = t1.state_id";
                    break;
                case 'customer_master':
                    $baseQuery =  "t1.*,t2.category_name  FROM `$dbPrefix`.`$table` t1 LEFT JOIN `$dbPrefix`.`customer_category_master` t2 ON t2.id = t1.customer_type";
                    break;
                case 'order_status':
                    $baseQuery =  "t.id,t.status_name,t.status_sequence,t.color_code,t.status,t.order_editable,
                    CASE WHEN t.stock_in_out = 1 THEN 'Stock In' WHEN t.stock_in_out = 2 THEN 'Stock Out' ELSE 'None' END as stock_in_out,
		            CASE WHEN t.mark_as_default_complete=1 THEN 'Initial/Default' WHEN t.mark_as_default_complete=2 THEN 'Completed' ELSE 'None' END as  mark_as_default_complete,
		            CASE WHEN t.status_type=1 THEN 'Invoice' WHEN t.status_type=2 THEN 'Dispatch'  WHEN t.status_type=3 THEN 'Payment' WHEN t.status_type=4 THEN 'Delivery' WHEN t.status_type=5 THEN 'Hold' WHEN t.status_type=6 THEN 'Item Verify'  
		            WHEN t.status_type=7 THEN 'Cancelled'  
		            WHEN t.status_type=8 THEN 'RTO' 
		            WHEN t.status_type=9 THEN 'Multi Action' ELSE ''		
		            END  as status_type FROM `$dbPrefix`.`$table` t";
                    break;
                case 'users_group_master':
                    $baseQuery =  "t1.*,t2.campaign_name  FROM `$dbPrefix`.`$table` t1 LEFT JOIN `$join_DB`.`campaign_master` t2 ON t2.campaign_id = t1.campaign_id";
                    break;
                case 'users_master':
                    $baseQuery =  "t1.*,t2.group_name,t3.user_name as manager  FROM `$dbPrefix`.`$table` t1 LEFT JOIN `$dbPrefix`.`users_group_master` t2 ON t2.id = t1.profile LEFT JOIN 
                    `$dbPrefix`.`users_master` t3 ON t3.user_id = t1.manager_id";
                    break;
                case 'item_master':
                    $baseQuery =  "t1.*,t2.group_name,t3.name as category_name  FROM `$dbPrefix`.`$table` t1 LEFT JOIN `$dbPrefix`.`item_group_master` t2 ON t2.id = t1.group_id LEFT JOIN 
                    `$dbPrefix`.`itemcategory_master` t3 ON t3.itemcat_id = t1.category_id";
                    break;
                case 'stockout_inventory':
                    $baseQuery =  "t1.inv_id,t1.ref_no,t1.item_count,t1.notes,t2.branch_name,
                    DATE_FORMAT(`t1`.`inventory_date`,'%d-%m-%Y') as inventory_date FROM `$dbPrefix`.`$table` t1 LEFT JOIN `$dbPrefix`.`branch_master` t2 ON t2.branch_id = t1.branch_id";
                    break;
                case 'branch_master':
                    $baseQuery =  "t1.signatory,t1.sign_img_path,t1.bank_acc_no,t1.bank_branch,t1.bank_ifsc_code,t1.bank_name,t1.invoice_remarks,t1.gstin,t1.pan_no,t1.address,t2.state_code,t1.branch_id, t1.branch_name, t1.branch_code, t1.email, t1.phone, t2.state_name, t1.pincode, IF(t1.status=1, 'Active', 'Inactive') AS status_title FROM `$dbPrefix`.`$table` t1 LEFT JOIN " . $this->tables['admin_state_master'] . " t2 ON (t2.state_id = t1.state) ";
                    break;
                case 'lead_status':
                    $baseQuery =  "t1.*,t2.status_type FROM `$dbPrefix`.`$table` t1 LEFT JOIN " . $this->tables['lead_status_type'] . " t2 ON t2.id = t1.lead_status_type ";
                    break;
                case 'lead_source':
                    $baseQuery =  "t1.*,t2.name as api_title FROM `$dbPrefix`.`$table` t1 LEFT JOIN `admin_lead_source_api_provider` t2 ON t2.id = t1.api_provider ";
                    break;
                case 'leads':
                    $baseQuery =  "t1.id,CONCAT(t1.contact_code,'-',t1.contact_no) as contact_no,t1.client_name,DATE_FORMAT(t1.lead_date,'%d-%m-%Y') lead_date,t1.status,t2.title as lead_status,t2.color_code,t3.branch_name branch,t4.category_name customer_type,t5.user_name assign_to,t6.title lead_source,t1.email,t7.state_name state,t8.city_name city,t9.pincode,t1.address,t1.tax_amt,t1.net_amount,t1.gross_amt,t1.discount_amt,t1.additional_discount,t1.round_off_amt,t1.reference_name,t1.order_id,t10.payment_name payment_type,t1.notes,t1.branch branch_id,t1.contact_person,t1.client_gst,t1.state state_code,t1.pdf_title,t1.payment_terms,t1.delivery_terms,
                    CONCAT(t2.title, '~:~', t2.color_code) AS lead_status_name FROM `$dbPrefix`.`$table` t1 
                    LEFT JOIN `$dbPrefix`.`lead_status` t2 ON t2.id = t1.lead_status 
                    LEFT JOIN `$dbPrefix`.`branch_master` t3 ON t3.branch_id = t1.branch
                    LEFT JOIN `$dbPrefix`.`customer_category_master` t4 ON t4.id = t1.customer_type
                    LEFT JOIN `$dbPrefix`.`users_master` t5 ON t5.user_id = t1.assign_to
                    LEFT JOIN `$dbPrefix`.`lead_source` t6 ON t6.id = t1.lead_source
                    LEFT JOIN `sanordo_db`.`admin_state_master` t7 ON t7.state_id = t1.state
                    LEFT JOIN `$dbPrefix`.`location_city` t8 ON t8.id = t1.city
                    LEFT JOIN `$dbPrefix`.`location_pincode` t9 ON t9.id = t1.pincode
                    LEFT JOIN `$dbPrefix`.`payment_type` t10 ON t10.id = t1.payment_type";
                    break;
                case 'order_logic':
                    $sql =  "SELECT t.order_status_id,s.status_name, CONCAT(s.status_name, '~:~', s.color_code) AS order_status_name, GROUP_CONCAT(CONCAT(a.status_name, '~:~', a.color_code)) AS all_status_names
                    FROM `$dbPrefix`.`$table` AS t
                    LEFT JOIN `$dbPrefix`.`order_status` AS s ON t.order_status_id = s.id
                    LEFT JOIN `$dbPrefix`.`order_status` AS a ON t.status_action_id = a.id
                    $whereClause
                    GROUP BY t.order_status_id, s.status_name";
                    break;
                case 'client_user_login_log':
                    $baseQuery =  "t1.user_id,t1.ip_address,DATE_FORMAT(t1.cr_date,'%d-%m-%Y %H:%i:%s') cr_date, t1.user_agent, t2.user_name
                    FROM `sanordo_db`.`$table` AS t1
                    LEFT JOIN `$join_DB`.`users_master` AS t2 ON t1.user_id = t2.user_id";
                    break;
                case 'api_user_logs':
                    $baseQuery =  "t1.ip_address,DATE_FORMAT(t1.cr_date,'%d-%m-%Y %H:%i:%s') cr_date, t1.message,t1.is_master, t2.user_name,t1.id
                    FROM `$dbPrefix`.`$table` AS t1
                    LEFT JOIN `$dbPrefix`.`users_master` AS t2 ON t1.user_id = t2.user_id";
                    break;
                case 'purchase_master':
                    $baseQuery =  " t1.id,t1.branch_id,t2.branch_name,t1.vendor_id,t3.customer_name as vendor_name,DATE_FORMAT(t1.invoice_date,'%d-%m-%Y') as invoice_date,t1.net_amt,DATE_FORMAT(t1.created_at,'%d-%m-%Y %H:%i:%s') as created_at
                    FROM `$dbPrefix`.`$table` AS t1
                    LEFT JOIN `$dbPrefix`.`branch_master` AS t2 ON t1.branch_id=t2.branch_id
                    LEFT JOIN `$dbPrefix`.`customer_master` AS t3 ON t1.vendor_id=t3.id";
                    break;
                case 'purchasereturn_master':
                    $baseQuery =  " t1.id,t1.branch_id,t2.branch_name,t1.vendor_id,t3.customer_name as vendor_name,DATE_FORMAT(t1.invoice_date,'%d-%m-%Y') as invoice_date,t1.net_amt,DATE_FORMAT(t1.created_at,'%d-%m-%Y %H:%i:%s') as created_at
                    FROM `$dbPrefix`.`$table` AS t1
                    LEFT JOIN `$dbPrefix`.`branch_master` AS t2 ON t1.branch_id=t2.branch_id
                    LEFT JOIN `$dbPrefix`.`customer_master` AS t3 ON t1.vendor_id=t3.id";
                    break;
                case 'lead_order_logs':
                    $baseQuery =  "t1.id,t1.lead_id,t1.response,DATE_FORMAT(t1.updated_at,'%d-%m-%Y %H:%i:%s') as created_at,IF(t1.status=1, 'Success', 'Failed') AS status,IF(t1.process=1, 'Process Completed', 'Processing') AS process
                    FROM `$dbPrefix`.`$table` AS t1";
                    break;
                case 'courier_docketno':
                    $baseQuery =  "t1.id,t1.docket_no,t1.order_id,IF(t1.status=1, 'Used', 'Unused') AS status,DATE_FORMAT(t1.used_time,'%d-%m-%Y %H:%i:%s') as used_time,t2.courier_name
                    FROM `$dbPrefix`.`$table` AS t1
                    LEFT JOIN `admin_courier_api_master` t2 ON t2.id = t1.courier_id";
                    break;
                case 'courier_pincode':
                    $baseQuery =  "t1.id,t1.pin_code,t1.origin_area,t1.process_code,t2.courier_name
                    FROM `$dbPrefix`.`$table` AS t1
                    LEFT JOIN `admin_courier_api_master` t2 ON t2.id = t1.courier_id";
                    break;
                case 'order_status_update_logs':
                    $baseQuery =  "t1.id,t1.request_id,t1.order_id,t1.status,t1.fail_reason,DATE_FORMAT(t1.cr_date,'%d-%m-%Y %H:%i:%s') as cr_date,t2.user_name,t3.status_name current_status_id,t4.status_name next_status_id
                    FROM `$dbPrefix`.`$table` AS t1
                    LEFT JOIN `$dbPrefix`.`users_master` AS t2 ON t1.cr_usr = t2.user_id
                    LEFT JOIN `$dbPrefix`.`order_status` AS t3 ON t1.current_status_id = t3.id
                    LEFT JOIN `$dbPrefix`.`order_status` AS t4 ON t1.next_status_id = t4.id";
                    break;
                case 'order_attachments':
                    $baseQuery =  "t1.file_name,t1.id,DATE_FORMAT(t1.cr_date,'%d-%m-%Y %H:%i:%s') as cr_date,t2.user_name,t1.order_id,t1.path,t1.mime,t1.size
                    FROM `$dbPrefix`.`$table` AS t1
                    LEFT JOIN `$dbPrefix`.`users_master` AS t2 ON t1.cr_usr = t2.user_id";
                    break;
                case 'payment_gateway':
                    $baseQuery =  "t1.api,t1.id,t2.name,t1.status
                    FROM `$dbPrefix`.`$table` AS t1
                    LEFT JOIN `sanordo_db`.`admin_payment_gateway_master` AS t2 ON t1.api = t2.api";
                    break;
                case 'email_sms_log':
                    $baseQuery =  "t1.*,DATE_FORMAT(t1.created_at, '%d-%m-%Y %H:%i:%s') as createdat, IFNULL(DATE_FORMAT(t1.sent_at, '%d-%m-%Y %H:%i:%s'), '') as sentat FROM `$dbPrefix`.`$table` t1";
                    break;
                default:
                    $baseQuery = "* FROM `$dbPrefix`.`$table`";
                    break;
            }

            if ($master_name != 'order_logic') {
                $sql = "SELECT $baseQuery $whereClause";
            }
            if (!empty($sortBy)) {
                $sql .= " ORDER BY $sortBy";
            }
            // print_r($sql);die;
            $result = $this->fetchPaginatedData($sql, $page, $page_limit);

            if (!$result['status']) {
                throw new \Exception($result['error']);
            }
            return [
                "status" => true,
                "count" => $result['count'],
                "data" => $result['data'],
                "page" => $result['page'],
                "page_limit" => $result['page_limit']
            ];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getMasterDataByID($db, $master_name, $edit_id, $primaryKey = 'id', $select = '*', $query = '', $where = '')
    {
        try {
            if (empty($db) || empty($master_name) || empty($edit_id)) {
                throw new \Exception("Database prefix, table name, and edit ID are required.");
            }

            $dbPrefix = $db;
            $table = $master_name;
            $whereClause = " WHERE $primaryKey = $edit_id";

            if (!empty($where)) {
                $whereClause .= " AND " . $where;
            }
            if (empty($query)) {
                $sql = "SELECT $select FROM `$dbPrefix`.`$table`" . $whereClause;
            } else {
                $sql = $query . $whereClause;
            }

            $result = $this->fetchSingleData($sql);

            if (!$result['status']) {
                throw new \Exception($result['error']);
            }

            return ["status" => true, "data" => $result['data']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    // --------------  FOR COUPON MASTER  START ----------------
    public function validateCouponCodes($dbPrefix, $whereString, $whereInArray = array())
    {
        try {
            $whereInArrayStr = array_map(fn($n) => "'" . $n . "'", $whereInArray);
            $where   = '1=1';
            if ($whereString) {
                $where .= " AND $whereString";
            }
            $string = implode(',', $whereInArrayStr);
            $where .= " AND (`coupon_name` IN ($string))";
            $sql = "SELECT `coupon_name` FROM `$dbPrefix`.`coupon_list` WHERE $where";
            $data = $this->fetchAllData($sql);
            if (!$data['status']) {
                throw new \Exception($data['error']);
            } else {
                return $data['data'] ? $data['data'] : [];
            }
            // SELECT `coupon_name` FROM `tel_coupon_list` WHERE   (`coupon_name` IN('TEST421'))
            // SELECT `coupon_name`FROM `tel_coupon_list`WHERE `id` = 10 AND (`coupon_name` IN('TEST421') )
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getTotalCouponCount($dbPrefix, $whereString, $update)
    {
        try {
            if ($update['status'] == true) {
                $sql = "SELECT count(id) as count FROM `$dbPrefix`.`coupon_list` WHERE $whereString";
                $data = $this->fetchAllData($sql);
                if (!$data['status']) {
                    throw new \Exception($data['error']);
                }
                $count = $data['data'][0]['count'];
                $id = $update['coupon_master_id'];
                $updSql = "UPDATE `$dbPrefix`.`coupon_master` SET coupon_count = $count WHERE id = $id";
                Logger::error("$id");
                $stmt = self::conn()->prepare($updSql);
                $stmt->execute();
            }
            $sql2 = "SELECT count(id) as count FROM `$dbPrefix`.`coupon_list` WHERE $whereString";
            $data2 = $this->fetchAllData($sql2);
            return $data2['data'][0]['count'];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function deleteCouponList($dbPrefix, $whereString, $whereInData, $limit = 0)
    {
        try {
            $where   = '1=1';
            if ($whereString) {
                $where .= " AND $whereString";
            }
            // print_r($where);die;
            if (count($whereInData) > 0) {
                $string = implode(',', $whereInData);
                $where .= " AND (`id` IN ($string))";
                // $this -> db -> where_in('id', $whereInData);  --- previous
            }
            if ($limit > 0) {
                $sql  = "DELETE FROM `$dbPrefix`.`coupon_list` WHERE $where LIMIT $limit";
                // $this -> db -> limit($limit);  --- previous
            } else {
                $sql  = "DELETE FROM `$dbPrefix`.`coupon_list` WHERE $where";
            }
            // print_r($sql);die;

            $stmt = self::conn()->prepare($sql);
            $stmt->execute();
            return ["status" => true, "affected_rows" => $stmt->rowCount()];
            // $this -> db -> where($queryArray) -> delete($this -> coupon_list);  --- previous
            // return $this -> db -> affected_rows();  --- previous
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getBulkCouponData($dbPrefix, $whereString)
    {
        try {
            $sql = "SELECT * FROM `$dbPrefix`.`coupon_list` WHERE $whereString";
            $data = $this->fetchAllData($sql);
            if (!$data['status']) {
                throw new \Exception($data['error']);
            }
            return ["status" => true, "data" => $data['data']];
            // return $this->db->order_by('id', 'desc')->get_where($this->coupon_list, $queryArray)->result_array(); 
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }
    // --------------  FOR COUPON MASTER  END ----------------

    public function getCityStatePincodeBySearch($dbPrefix, $queryArray, $likeArray, $limit = 0)
    {
        try {
            $sql = "SELECT `s`.`state_name`, `c`.`city_name`, `p`.`state_id`, `p`.`city_id`, `p`.`pincode`, CONCAT(c.city_name, ' - ', p.pincode) as pincode_city
          FROM `$dbPrefix`.`location_pincode` as `p`
          LEFT JOIN `$dbPrefix`.`location_city` as `c` ON `c`.`id`=`p`.`city_id`
          LEFT JOIN `sanordo_db`.`admin_state_master` as `s` ON `s`.`state_id`=`p`.`state_id`
          WHERE   ($queryArray) AND ($likeArray) LIMIT $limit";
            $data = $this->fetchAllData($sql);
            if (!$data['status']) {
                throw new \Exception($data['error']);
            } else {
                return $data['data'] ? $data['data'] : [];
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getUserProfileRights($Id, $type, $dbPrefix)
    {
        if ($type == 'GROUP') {
            $sql = "SELECT menu_id,menu_name,menu_parent,menu_priority, '0' view_right, '0' add_right, '0' edit_right, '0' delete_right,'0' excel_right FROM client_menu_master WHERE menu_id NOT IN (SELECT page_id FROM `$dbPrefix`.`users_profile_rights`  WHERE profile_id = '$Id') AND menu_type=1 UNION SELECT T2.menu_id, T2.menu_name, T2.menu_parent, T2.menu_priority, T1.view_right, T1.add_right, T1.edit_right, T1.delete_right,T1.excel_right FROM `$dbPrefix`.`users_profile_rights` AS T1 JOIN client_menu_master AS T2 ON T1.page_id = T2.menu_id WHERE T1.profile_id = '$Id' AND T2.menu_type=1 order by menu_priority";
        } else if ($type == 'USER') {
            $sql = "SELECT menu_id,menu_name,menu_parent,menu_priority, '0' view_right, '0' add_right, '0' edit_right, '0' delete_right, '0' excel_right FROM client_menu_master WHERE menu_id NOT IN (SELECT page_id FROM `$dbPrefix`.`users_master_rights`  WHERE user_id = '$Id') AND menu_type=1 UNION SELECT T2.menu_id, T2.menu_name, T2.menu_parent, T2.menu_priority, T1.view_right, T1.add_right, T1.edit_right, T1.delete_right,T1.excel_right FROM `$dbPrefix`.`users_master_rights` AS T1 JOIN client_menu_master AS T2 ON T1.page_id = T2.menu_id WHERE T1.user_id = '$Id' AND T2.menu_type=1 order by menu_priority";
        }
        $data = $this->fetchAllData($sql);
        return $data;
    }

    function updateDefaultAndCompleteStatus($table, $whereString)
    {
        try {
            $sql = "UPDATE $table SET mark_as_default_complete = 0 WHERE $whereString";
            $stmt = self::conn()->prepare($sql);
            $stmt->execute();
            return ["status" => true, "affected_rows" => $stmt->rowCount()];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function updateIsDefault($table, $id, $primaryKey, $col)
    {
        try {
            $sql  = "UPDATE $table SET $col = 0 WHERE $primaryKey != $id";
            $stmt = self::conn()->prepare($sql);
            $stmt->execute();
            return ["status" => true, "affected_rows" => $stmt->rowCount()];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }
    //  -----------  ITEM STOCK AND STOCKOUT INVENTORY  -------------
    public function stockOpeningUpdate($itemId, $brId, $qty, $dbname, $rollback = 0)
    {
        try {
            $res = $this->stockCheck($itemId, $brId, $dbname);
            if ($res['status'] == 1) {
                if ($rollback == 0) {
                    $stockO = $res['data'][0]['opening_stock'] + $qty;
                    $stock = $res['data'][0]['stock'] + $qty;
                } else {
                    $stockO = $res['data'][0]['opening_stock'] - $qty;
                    $stock = $res['data'][0]['stock'] - $qty;
                }
                $data = array('opening_stock' => $stockO, 'stock' => $stock);
                $where = array("item_id" => $itemId, "branch_id" => $brId);
                return $this->update("`$dbname`.`item_stock`", $data, $where);
            } else {
                $data = array("item_id" => $itemId, "branch_id" => $brId, "opening_stock" => $qty, "stock" => $qty);
                return $this->insert("`$dbname`.`item_stock`", $data);
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function stockCheck($itemId, $brId, $dbname)
    {
        try {
            $result = $this->fetchAllData("SELECT * FROM `$dbname`.`item_stock` WHERE item_id = $itemId AND branch_id = $brId");
            $result['data'] = $result['data'] ? $result['data'] : [];
            if (!$result['status']) {
                throw new \Exception($result['error']);
            }
            if (count($result['data']) > 0) {
                return array('status' => 1, 'data' => $result['data']);
            } else {
                return array('status' => 0, 'data' => $result['data']);;
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function stockOut($id, $dbname, $rollback = 0, $type = 'IO')
    {
        try {
            //IO - ITEM ORDER
            // $rollback  = 0 Fresh Order Place and 1 On Edit first Decrease the Item Quantity
            $itemList = array();
            if ($type == 'IO') {
                // $itemList = $this->getOrderItemListForStock(array('o.id' => $id));
            } else if ($type == 'IV') { // Stock Out using Inventory like amazon,flipkart etc
                $itemList = $this->getOuterStockInvenory("t1.inv_id = $id", $dbname, 'qty');
            }

            foreach ($itemList as $item) {
                if ($item['item_id'] > 0)
                    $this->stockOutUpdate($item['item_id'], $item['branch_id'], $item['qty'], $dbname, $rollback);
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function stockIn($id, $dbname, $rollback = 0, $type = 'PU')
    {
        try {
            //PU - PURCHASE
            // $rollback  = 0 Fresh purchase and 1 On Edit first Decrease the Item Quantity
            $itemList = array();
            if ($type == 'PU') {
                $itemList = $this->getPurchaseItemListForStock("p.id = $id", $dbname);
            } else if ($type == 'PR') {
                $itemList = $this->getPurchaseReturnItemListForStock("p.id = $id", $dbname);
            } else if ($type == 'IV') { // Stock Out using Inventory like amazon,flipkart etc
                $itemList = $this->getOuterStockInvenory("t1.inv_id = $id", $dbname, 'rto_qty');
            }

            foreach ($itemList as $item) {
                $this->stockInUpdate($item['item_id'], $item['branch_id'], $item['qty'], $dbname, $rollback);
            }
            return;
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getPurchaseReturnItemListForStock($queryString, $dbname)
    {
        $sql = "SELECT p.id,l.item_id,IFNULL(l.qty, 0) + IFNULL(l.free_qty, 0) AS qty,p.branch_id FROM `$dbname`.`purchasereturn_master` p INNER JOIN `$dbname`.`purchasereturn_itemlist` l ON p.id=l.prtn_id
         WHERE $queryString";
        $res = $this->fetchAllData($sql);
        return $res['data'];
    }

    public function getPurchaseItemListForStock($queryString, $dbname)
    {
        $sql = "SELECT p.id as purchase_id,l.item_id,l.qty+ifnull(l.free_qty,0) qty,p.branch_id FROM `$dbname`.`purchase_master` p LEFT JOIN `$dbname`.`purchase_itemlist` l ON p.id=l.purchase_id
         WHERE $queryString";
        $res = $this->fetchAllData($sql);
        return $res['data'];
        // $this->db->select('p.id as purchase_id,l.item_id,l.qty+ifnull(l.free_qty,0) qty,p.branch_id')->from($this->purchase_master_table . ' as p')->where($queryArray);
        // return $this->db->join($this->purchase_item_list . ' as l', 'p.id=l.purchase_id')->get()->result();
    }

    public function getOuterStockInvenory($queryArray, $dbname, $type = 'qty')
    {
        try {
            $select = ($type == 'qty') ? "t2.qty,t2.rto_qty" : "t2.qty as stout_qty,t2.rto_qty as qty";
            $sql = "SELECT t1.inv_id,t2.item_id,$select,t1.branch_id FROM `$dbname`.`stockout_inventory` as t1
            LEFT JOIN `$dbname`.`stockout_inventory_item` as t2 ON t1.inv_id = t2.inv_id WHERE $queryArray";
            $res = $this->fetchAllData($sql);
            return $res['data'];
            // $this->db->select("t1.inv_id,t2.item_id,$select,t1.branch_id")->from('stockout_inventory as t1')->where($queryArray);
            // return $this->db->join('stockout_inventory_item as t2', 't1.inv_id=t2.inv_id', 'left')->get()->result();
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function stockOutUpdate($itemId, $brId, $qty, $dbname, $rollback = 0)
    {
        try {
            $res = $this->stockCheck($itemId, $brId, $dbname);
            if ($res['status'] == 1) {
                if ($rollback == 0) {
                    $stockOut = $res['data'][0]['stock_out'] + $qty;
                    $stock = $res['data'][0]['stock'] - $qty;
                } else {
                    $stockOut = $res['data'][0]['stock_out'] - $qty;
                    $stock = $res['data'][0]['stock'] + $qty;
                }
                $data = array('stock_out' => $stockOut, 'stock' => $stock);
                $where = array("item_id" => $itemId, "branch_id" => $brId);
                return $this->update("`$dbname`.`item_stock`", $data, $where);
            } else {
                $data = array("item_id" => $itemId, "branch_id" => $brId, "stock_out" => $qty, "stock" => -$qty);
                return $this->insert("`$dbname`.`item_stock`", $data);
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function stockInUpdate($itemId, $brId, $qty, $dbname, $rollback = 0)
    {
        try {
            $res = $this->stockCheck($itemId, $brId, $dbname);
            if ($res['status'] == 1) {
                if ($rollback == 0) {
                    $stockIn = $res['data'][0]['stock_in'] + $qty;
                    $stock = $res['data'][0]['stock'] + $qty;
                } else {
                    $stockIn = $res['data'][0]['stock_in'] - $qty;
                    $stock = $res['data'][0]['stock'] - $qty;
                }
                $data = array('stock_in' => $stockIn, 'stock' => $stock);
                $where = array("item_id" => $itemId, "branch_id" => $brId);
                return $this->update("`$dbname`.`item_stock`", $data, $where);
            } else {
                $data = array('item_id' => $itemId, 'branch_id' => $brId, 'stock_in' => $qty, 'stock' => $qty);
                return $this->insert("`$dbname`.`item_stock`", $data);
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }
    //  -----------  ITEM STOCK AND STOCKOUT INVENTORY  -------------

    public function getLeadData($dbPrefix, $lead_id)
    {
        try {
            $baseQuery = "SELECT t1.id,t1.email,CONCAT(t1.contact_code,'-',t1.contact_no) as contact_no,t1.client_name,DATE_FORMAT(t1.lead_date,'%d-%m-%Y') lead_date,t1.status,t2.title as lead_status,t2.color_code,t3.branch_name branch,t4.category_name customer_type,t5.user_name assign_to,t6.name item_category,t7.item,t8.packages,t9.title lead_source FROM `$dbPrefix`.`leads` t1 
                    LEFT JOIN `$dbPrefix`.`lead_status` t2 ON t2.id = t1.lead_status 
                    LEFT JOIN `$dbPrefix`.`branch_master` t3 ON t3.branch_id = t1.branch
                    LEFT JOIN `$dbPrefix`.`customer_category_master` t4 ON t4.id = t1.customer_type
                    LEFT JOIN `$dbPrefix`.`users_master` t5 ON t5.user_id = t1.assign_to
                    LEFT JOIN `$dbPrefix`.`itemcategory_master` t6 ON t6.itemcat_id = t1.item_category
                    LEFT JOIN (SELECT GROUP_CONCAT(b.item_name) item,`a`.`lead_id`
                    FROM `$dbPrefix`.`lead_item` AS `a`
                    LEFT JOIN `$dbPrefix`.item_master b ON b.item_id = a.item_id
                    GROUP BY `a`.`lead_id`) t7 ON `t7`.`lead_id` = `t1`.`id`
                    LEFT JOIN (SELECT GROUP_CONCAT(b.package_name) packages,`a`.`lead_id`
                    FROM `$dbPrefix`.`lead_item_package` AS `a`
                    LEFT JOIN `$dbPrefix`.item_package_master b ON b.id = a.item_package_id
                    GROUP BY `a`.`lead_id`) t8 ON `t8`.`lead_id` = `t1`.`id`
                    LEFT JOIN `$dbPrefix`.`lead_source` t9 ON t9.id = t1.lead_source
                    WHERE t1.id = $lead_id";
            $resp = $this->fetchAllData($baseQuery);
            if (!$resp['status']) {
                throw new \Exception($resp['error']);
            }
            return ['status' => true, 'data' => $resp['data']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    // GET TAX DETAILS
    public function getAllTaxType($dbname, $branch_id, $vendor_id)
    {
        try {
            $branchSql = "SELECT b.branch_id,s.is_union_territory,s.state_name,s.state_id
              FROM `$dbname`.`branch_master` b 
              JOIN `sanordo_db`.`admin_state_master` s ON s.state_id = b.state 
              WHERE branch_id = $branch_id";
            $branchState = $this->fetchSingleData($branchSql);
            $vendorSql = "SELECT a.customer_id,s.is_union_territory,s.state_name,s.state_id
              FROM `$dbname`.`customer_address_master` a
              JOIN `sanordo_db`.`admin_state_master` s ON s.state_id = a.address_state_id 
              WHERE customer_id = $vendor_id";
            $vendorState = $this->fetchSingleData($vendorSql);
            $taxArray[] = 'All';
            if (($branchState['data']['state_id'] == $vendorState['data']['state_id']) && $branchState['data']['is_union_territory'] == 1) {
                $taxArray[] = 'Intra';
                $taxArray[] = 'Union';
            } else if (($branchState['data']['state_id'] == $vendorState['data']['state_id']) && $branchState['data']['is_union_territory'] == 0) {
                $taxArray[] = 'Intra';
                $taxArray[] = 'State';
            } else {
                $taxArray[] = 'Inter';
            }
            return $taxArray;
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    // GET ITEM TAX DETAILS
    public function getItemTaxDetail($dbname, $queryArray = '', $queryInArray = array())
    {
        try {
            $where = '1 = 1';
            if ($queryArray) {
                $where .= " AND $queryArray";
            }
            if ($queryInArray) {
                $quotedValues = array_map(function ($val) {
                    return "'" . addslashes($val) . "'";
                }, $queryInArray);
                $implode = implode(',', $quotedValues);
                $where .= " AND t.tax_type IN ($implode)";
            }
            $sql = "SELECT i.item_id,i.item_name,i.item_desc,t.tax_name,t.tax_id,tm.tax_percentage FROM `$dbname`.`item_master` i
            JOIN `$dbname`.`itemcategorytax_master` tm ON tm.cat_id = i.category_id
            JOIN `$dbname`.`tax_master` t ON t.tax_id=tm.tax_id WHERE $where ORDER BY i.item_name ASC";
            $resp = $this->fetchAllData($sql);
            if (!$resp['status']) {
                throw new Exception($resp['error']);
            }
            return $resp['data'] ? $resp['data'] : [];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    public function getPurchaseReturnItemList($purchase_id, $dbPrefix)
    {
        try {
            $itemSql = "SELECT t1.list_id,t1.purchase_id,t1.item_id, t4.item_name,t1.item_desc,t1.hsn_sac_code,
               t1.rate,t1.final_rate,t1.discount,t1.discount_amount,t1.qty AS puc_qty,
               t1.free_qty AS free_pur_qty,t1.unit,t1.amount,t1.tax_percentage,
               t1.tax_amount,t1.total_amount,t1.cr_usr,t1.qty - IFNULL(SUM(t3.qty), 0) AS qty,
               t1.free_qty - IFNULL(SUM(t3.free_qty), 0) AS free_qty
               FROM `$dbPrefix`.`purchase_itemlist` AS t1
               LEFT JOIN `$dbPrefix`.`purchasereturn_master` AS t2 ON t1.purchase_id = t2.purchase_id AND t2.purchase_id = '$purchase_id'
               LEFT JOIN `$dbPrefix`.`purchasereturn_itemlist` AS t3 ON t3.prtn_id = t2.id AND t1.item_id = t3.item_id 
               LEFT JOIN `$dbPrefix`.`item_master` AS t4 ON t4.item_id = t1.item_id
               WHERE  t1.purchase_id = '$purchase_id'
               GROUP BY  t1.list_id,t1.purchase_id,t1.item_id,t4.item_name,t1.item_desc,t1.hsn_sac_code,t1.rate,
               t1.final_rate,t1.discount,t1.discount_amount,t1.qty,t1.free_qty,t1.unit,
               t1.amount,t1.tax_percentage,t1.tax_amount,t1.total_amount,t1.cr_usr";
            $itemResp = $this->fetchAllData($itemSql);
            if (!$itemResp['status']) {
                throw new Exception($itemResp['error']);
            }
            return ['status' => true, 'data' => $itemResp['data']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    public function validatePurchaseItemQty($purchase_id, $dbPrefix, $prtn = 0)
    {
        try {
            $wherePur = "";
            if ($prtn > 0) {
                $wherePur = " AND t2.id!='$prtn'";
            }
            $sql = "SELECT t1.item_id,t1.qty - IFNULL(SUM(t3.qty), 0) + t1.free_qty - IFNULL(SUM(t3.free_qty), 0) AS qty FROM  `$dbPrefix`.`purchase_itemlist` AS t1
           LEFT JOIN `$dbPrefix`.`purchasereturn_master` AS t2  ON t1.purchase_id = t2.purchase_id 
           AND t2.purchase_id = '$purchase_id' $wherePur
           LEFT JOIN `$dbPrefix`.`purchasereturn_itemlist` AS t3  ON t3.prtn_id = t2.id 
           AND t1.item_id = t3.item_id WHERE t1.purchase_id = '$purchase_id'
           GROUP BY  t1.item_id, t1.qty,  t1.free_qty";
            $itemResp = $this->fetchAllData($sql);
            if (!$itemResp['status']) {
                throw new Exception($itemResp['error']);
            }
            return ['status' => true, 'data' => $itemResp['data']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    function getLeadDashboardData($dbPrefix, $where, $where2)
    {
        try {
            $sql = "CALL " . $this->procedures['GetLeadDashboardCards'] . "(\"$dbPrefix\",\"$where\")";
            $cardData  = $this->fetchMultiSetData($sql);
            if (!$cardData['status'])
                throw new Exception(($cardData['error']));

            $leadMonthSql = "CALL " . $this->procedures['GetLeadMonthWise'] . "(\"$dbPrefix\",\"$where\")";
            $leadMonth  = $this->fetchMultiSetData($leadMonthSql);
            if (!$leadMonth['status'])
                throw new Exception(($leadMonth['error']));

            $leadSourceSql = "CALL " . $this->procedures['GetLeadSourcePiechart'] . "(\"$dbPrefix\",\"$where\")";
            $leadSource  = $this->fetchMultiSetData($leadSourceSql);
            if (!$leadSource['status'])
                throw new Exception(($leadSource['error']));

            $leadStatusSql = "CALL " . $this->procedures['GetLeadStatusPiechart'] . "(\"$dbPrefix\",\"$where\")";
            $leadStatus  = $this->fetchMultiSetData($leadStatusSql);
            if (!$leadStatus['status'])
                throw new Exception(($leadStatus['error']));

            $leadUserSql = "CALL " . $this->procedures['GetLeadUserWise'] . "(\"$dbPrefix\",\"$where\",\"$where2\")";
            $leadUser  = $this->fetchMultiSetData($leadUserSql);
            if (!$leadUser['status'])
                throw new Exception(($leadUser['error']));
            return array(
                'status' => true,
                'dash_cards'           => $cardData['data'][0],
                'lead_month'           => $leadMonth['data'][0],
                'lead_source_piechart' => $leadSource['data'][0],
                'lead_status_piechart' => $leadStatus['data'][0],
                'lead_user_wise'       => $leadUser['data'][0]
            );
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    function getIdsOfLeadFlpLog($dbPrefix, $dash_flp_id)
    {
        try {
            $where = "1=1";
            if ($dash_flp_id == 101) {
                $where .= " AND t2.lead_status_type = 1";
            } else if ($dash_flp_id == 102) {
                $where .= " AND DATE(lead_flp_date) = DATE(CURDATE()) AND t2.lead_status_type = 1";
            } else if ($dash_flp_id == 103) {
                $where .= " AND DATE(lead_flp_date) > DATE(CURDATE()) AND t2.lead_status_type = 1";
            } else if ($dash_flp_id == 104) {
                $where .= " AND DATE(lead_flp_date) < DATE(CURDATE()) AND t2.lead_status_type = 1";
            }
            $sql = "SELECT GROUP_CONCAT(DISTINCT t3.flp_lead_id) AS lead_id
                    FROM `$dbPrefix`.`lead_flp_log` t1 JOIN (
                    SELECT flp_lead_id, MAX(id) AS id FROM `$dbPrefix`.`lead_flp_log`
                    GROUP BY flp_lead_id) t3 ON t3.id = t1.id LEFT JOIN 
                    `$dbPrefix`.`lead_status` t2 ON t1.lead_flp_status = t2.id
                     WHERE $where";
            $resp = $this->fetchAllData($sql);
            if (!$resp['status'])
                throw new Exception($resp['error']);
            return ['status' => true, 'data' => $resp['data']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    public function getFromMaster($table, $where, $params = [])
    {
        try {
            return $this->fetchAllData("SELECT * FROM $table WHERE $where LIMIT 1 ", $params);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }
}