<?php

namespace app\models;

use Yii;


class Common extends \yii\db\ActiveRecord
{
    public $role = array('1' => 'Team Lead', '2' => 'Manager', '3' => 'Executive');
    public $customer_type = array('1' => 'Hot', '2' => 'Warm', '3' => 'Cold');
    public $lead_status = array('1' => 'Active', '2' => 'InActive');
    public $tag_status = array('0' => 'In Progress', '1' => 'Visited', '2' => 'Not Visited', '3' => 'Clashed', '4' => 'Lead Converted', '5' => 'Lead Dead', '6' => 'Lead Registered', '7' => 'Lead Rejected');

    public $status = array('1' => 'Active', '2' => 'Pending', '0' => 'InActive');
    public $cp_company_type = array('1' => 'Sole Proprietorship', '2' => 'Partnership', '3' => 'Private Limited', '4' => 'Public Limited', '5' => 'Individual');
    public $cp_nature_business = array('1' => 'Residential Sales', '2' => 'Commercial Sales', '3' => 'Land sourcing for Developers', '4' => 'Agricultural Land Sales', '5' => 'Industrial Sales', '6' => 'Project Consultancy');
    public $project_status = array('1' => 'Completed', '2' => 'Ongoing', '3' => 'Launching');
    public $project_segment = array('1' => 'Affordable', '2' => 'Luxury', '3' => 'Ultra Luxury', '4' => 'Value');
    public $project_type = array('1' => 'New Project', '2' => 'Exclusive Project', '3' => 'Trending Project', '4' => 'Ready to move Project', '5' => 'Under construction Project');
    public $flat_type_id = array('1' => '1 BHK', '2' => '1.5 BHK', '3' => '2 BHK', '4' => '3 BHK', '5' => '4 BHK');
    public $tagging_done = array('0' => 'False', '1' => 'True');
    public $month = array('1' => 'January', '2' => 'February', '3' => 'March', '4' => 'April', '5' => 'May', '6' => 'June', '7' => 'July', '8' => 'August', '9' => 'September', '10' => 'October', '11' => 'November', '12' => 'December');

    public $ticket_issue_type = array(1 => 'CP Registration', 2 => 'Login', 3 => 'Profile Changes', 4 => 'Leads', 5 => 'Tagging', 6 => 'Others');
    public $ticket_status = array(1 => 'Received', 2 => 'In Progress', 3 => 'Resolved');


    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return 'snse_admin';
    }

    public function getloggeduser($smcp_obj)
    {
        $loggedUser = null;
        if (!empty($smcp_obj)) {
            $loggedUser['sm_cp_id'] = $smcp_obj->sm_cp_id;
            $loggedUser['channelpartner_id'] = $smcp_obj->channelpartner_id;
            $loggedUser['name'] = $smcp_obj->name;
            $loggedUser['email'] = $smcp_obj->email;
            $loggedUser['contact'] = $smcp_obj->contact;

            $loggedUser['role'] = $this->role[$smcp_obj->role];

            $loggedUser['status'] = $this->status[$smcp_obj->status];
            $loggedUser['created_on'] = $smcp_obj->created_on;
            $loggedUser['modified_on'] = $smcp_obj->modified_on;
        }
        return $loggedUser;
    }

    public function getmanagers($smcp_obj)
    {
        $members = null;
        if (!empty($smcp_obj) && $smcp_obj->role == 1) {
            $members['team_lead']['sm_cp_id'] = $smcp_obj->sm_cp_id;
            $members['team_lead']['name'] = $smcp_obj->name;
            $smData = (new \yii\db\Query())
                ->select('sm_cp1.sm_cp_id,sm_cp1.name')
                ->from('sm_cp sm_cp1')
                ->where('sm_cp1.parent_id="' . $smcp_obj->sm_cp_id . '" AND sm_cp1.status=1 AND sm_cp1.role=2 ')->createCommand()
                ->queryAll();
            if (!empty($smData)) {
                $count = 0;
                foreach ($smData as $smData_k => $smData_v) {
                    $members['managers'][$count] = $smData_v;
                    $count++;
                }
            } else {
                $members['managers'] = null;
            }
        }
        return $members;
    }


    public function getcp($cp_id)
    {
        $Channelpartner = null;
        $cp_data = Channelpartner::find()->select('id,name,email_id,rera_number,pan_number,contact,company_name,address,location_id,company_type,gstin,nature_business,website,status,created_on,modified_on')->where(['id' => $cp_id])->one();
        if (!empty($cp_data)) {

            $location_arr = Location::findAll([
                'status' => 1
            ]);
            foreach ($location_arr as $location_arr_val) {
                $locations[$location_arr_val->id] = $location_arr_val->name;
            }
            $Channelpartner = $cp_data->toArray();

            if (!empty($Channelpartner['location_id'])) {
                $get_loc_set = explode(",", $Channelpartner['location_id']);
                $loc_final_val = '';
                foreach ($get_loc_set as $get_loc_set_val) {
                    if (empty($loc_final_val)) {
                        $loc_final_val = $locations[$get_loc_set_val];
                    } else {
                        $loc_final_val .= ", " . $locations[$get_loc_set_val];
                    }
                }
                $Channelpartner['location_id'] = $loc_final_val;
            }
            if (!empty($Channelpartner['company_type'])) {
                $Channelpartner['company_type'] = $this->cp_company_type[$Channelpartner['company_type']];
            }
            if (!empty($Channelpartner['nature_business'])) {
                $Channelpartner['nature_business'] = $this->cp_nature_business[$Channelpartner['nature_business']];
            }
            $Channelpartner['status'] = $this->status[$Channelpartner['status']];
        }
        return $Channelpartner;
    }

    public function getmembersapiold($sm_cp_id)
    {
        $members = null;
        $sm_cp_data = SmCp::find()->where(['sm_cp_id' => $sm_cp_id])->one();
        if (!empty($sm_cp_data)) {
            if ($sm_cp_data->status == 1 || $sm_cp_data->status == 2) {

                if ($sm_cp_data->status == 1) {
                    $smData = (new \yii\db\Query())
                        ->select('sm_cp1.sm_cp_id,sm_cp1.name,sm_cp1.email,sm_cp1.contact,sm_cp1.role,sm_cp1.parent_id,sm_cp1.status,sm_cp1.created_on,sm_cp1.modified_on')
                        ->from('sm_cp sm_cp1')
                        ->leftJoin('sm_cp sm_cp2', 'sm_cp1.parent_id=sm_cp2.sm_cp_id')
                        ->leftJoin('sm_cp sm_cp3', 'sm_cp2.parent_id=sm_cp3.sm_cp_id')
                        ->where('((' . $sm_cp_id . ' in (sm_cp1.parent_id,sm_cp2.parent_id, sm_cp3.parent_id)) AND sm_cp1.sm_cp_id!=' . $sm_cp_id . ')')->createCommand()
                        ->queryAll();
                    if (!empty($smData)) {
                        $count = 0;
                        foreach ($smData as $smData_k => $smData_v) {
                            $members[$count] = $smData_v;
                            $members[$count]['role'] = $this->role[$smData_v['role']];
                            $get_parent = $this->getparentapi($smData_v['parent_id']);
                            $members[$count]['parent_id'] = ($get_parent) ? $get_parent->name : '';
                            $members[$count]['status'] = $this->status[$smData_v['status']];
                            $count++;
                        }
                    }
                } elseif ($sm_cp_data->status == 2) {
                    $smData = (new \yii\db\Query())
                        ->select('sm_cp1.sm_cp_id,sm_cp1.name,sm_cp1.email,sm_cp1.contact,sm_cp1.role,sm_cp1.parent_id,sm_cp1.status,sm_cp1.created_on,sm_cp1.modified_on')
                        ->from('sm_cp sm_cp1')
                        ->leftJoin('sm_cp sm_cp2', 'sm_cp1.parent_id=sm_cp2.sm_cp_id')
                        ->where('((' . $sm_cp_id . ' in (sm_cp1.parent_id,sm_cp2.parent_id)) AND sm_cp1.sm_cp_id!=' . $sm_cp_id . ')')->createCommand()
                        ->queryAll();
                    if (!empty($smData)) {
                        $count = 0;
                        foreach ($smData as $smData_k => $smData_v) {
                            $members[$count] = $smData_v;
                            $members[$count]['role'] = $this->role[$smData_v['role']];
                            $get_parent = $this->getparentapi($smData_v['parent_id']);
                            $members[$count]['parent_id'] = ($get_parent) ? $get_parent->name : '';
                            $members[$count]['status'] = $this->status[$smData_v['status']];
                            $count++;
                        }
                    }
                }
            }
        }
        return $members;
    }

    public function getteamunderapi($sm_cp_id)
    {
        $members = null;
        $sm_cp_data = SmCp::find()->where(['sm_cp_id' => $sm_cp_id])->one();
        if (!empty($sm_cp_data)) {
            $smData = (new \yii\db\Query())
                ->select('sm_cp1.sm_cp_id,sm_cp1.name,sm_cp1.email,sm_cp1.contact,sm_cp1.role,sm_cp1.parent_id,sm_cp1.status,sm_cp1.created_on,sm_cp1.modified_on')
                ->from('sm_cp sm_cp1')
                ->where('sm_cp1.parent_id="' . $sm_cp_id . '" ')->createCommand()
                ->queryAll();
            if (!empty($smData)) {
                $count = 0;
                foreach ($smData as $smData_k => $smData_v) {
                    $members[$count] = $smData_v;
                    $members[$count]['role'] = $this->role[$smData_v['role']];
                    $get_parent = $this->getparentapi($smData_v['parent_id']);
                    $members[$count]['parent_id'] = $smData_v['parent_id'];
                    $members[$count]['parent_id_name'] = ($get_parent) ? $get_parent->name : '';
                    $members[$count]['status'] = $this->status[$smData_v['status']];
                    if ($smData_v['role'] == 2) {
                        $members[$count]['executiveList'] = $this->getteamunderapi($smData_v['sm_cp_id']);
                    }
                    //get list of each executive under manager
                    $count++;
                }
            }
        }
        return $members;
    }

    public function getmembersapi($sm_cp_id)
    {
        $members = null;
        $sm_cp_data = SmCp::find()->where(['sm_cp_id' => $sm_cp_id])->one();
        if (!empty($sm_cp_data)) {
            if ($sm_cp_data->role == 1 || $sm_cp_data->role == 2) {
                if ($sm_cp_data->role == 1) {
                    $members['managersList'] = $this->getteamunderapi($sm_cp_id);
                } elseif ($sm_cp_data->role == 2) {
                    $members['executiveList'] = $this->getteamunderapi($sm_cp_id);
                }
            }
        }
        return $members;
    }

    public function getsinglesmcpapi($sm_cp_id)
    {
        $member = null;

        $smData = (new \yii\db\Query())
            ->select('sm_cp1.sm_cp_id,sm_cp1.name,sm_cp1.email,sm_cp1.contact,sm_cp1.role,sm_cp1.parent_id,sm_cp1.status,sm_cp1.created_on,sm_cp1.modified_on')
            ->from('sm_cp sm_cp1')
            ->where(' sm_cp1.sm_cp_id=' . $sm_cp_id . '')->createCommand()
            ->queryAll();
        if (!empty($smData)) {
            $member = $smData[0];
            $member['role'] = $this->role[$member['role']];
            $get_parent = $this->getparentapi($member['parent_id']);
            $member['parent_id'] = $member['parent_id'];
            $member['parent_id_name'] = ($get_parent) ? $get_parent->name : '';
            $member['status'] = $this->status[$member['status']];
        }
        return $member;
    }

    public function getaccessmembers($sm_cp_id)
    {
        $members = null;
        $sm_cp_data = SmCp::find()->where(['sm_cp_id' => $sm_cp_id])->one();
        if (!empty($sm_cp_data)) {
            if ($sm_cp_data->role == 1 || $sm_cp_data->role == 2) {

                if ($sm_cp_data->role == 1) {
                    $smData = (new \yii\db\Query())
                        ->select('sm_cp1.sm_cp_id')
                        ->from('sm_cp sm_cp1')
                        ->leftJoin('sm_cp sm_cp2', 'sm_cp1.parent_id=sm_cp2.sm_cp_id')
                        ->leftJoin('sm_cp sm_cp3', 'sm_cp2.parent_id=sm_cp3.sm_cp_id')
                        ->where('((' . $sm_cp_id . ' in (sm_cp1.parent_id,sm_cp2.parent_id, sm_cp3.parent_id)) AND sm_cp1.sm_cp_id!=' . $sm_cp_id . ')')->createCommand()
                        ->queryAll();
                    if (!empty($smData)) {
                        $count = 0;
                        foreach ($smData as $smData_k => $smData_v) {
                            $members[] = $smData_v['sm_cp_id'];
                        }
                    }
                } elseif ($sm_cp_data->role == 2) {
                    $smData = (new \yii\db\Query())
                        ->select('sm_cp1.sm_cp_id')
                        ->from('sm_cp sm_cp1')
                        ->leftJoin('sm_cp sm_cp2', 'sm_cp1.parent_id=sm_cp2.sm_cp_id')
                        ->where('((' . $sm_cp_id . ' in (sm_cp1.parent_id,sm_cp2.parent_id)) AND sm_cp1.sm_cp_id!=' . $sm_cp_id . ')')->createCommand()
                        ->queryAll();
                    if (!empty($smData)) {
                        $count = 0;
                        foreach ($smData as $smData_k => $smData_v) {
                            $members[] = $smData_v['sm_cp_id'];
                        }
                    }
                }
            }
        }
        return $members;
    }

    public function getparentapi($sm_cp_id)
    {
        if (!empty($sm_cp_id)) {
            $sm_cp_data = SmCp::find()->where(['sm_cp_id' => $sm_cp_id])->one();
            return $sm_cp_data;
        }
        return false;
    }

    public function getsmofcp($cp_id)
    {
        $sm_arr = null;
        if (!empty($cp_id)) {
            $smcp_arr = SmCpNetwork::findAll([
                'status' => 1,
                'channelpartner_id' => $cp_id
            ]);
            if (!empty($smcp_arr)) {
                foreach ($smcp_arr as $smcp_arr_val) {
                    $sm_arr[] = $smcp_arr_val->sm_cp_id;
                }
            }
        }
        return $sm_arr;
    }

    public function getbookmarkprojects($sm_cp_id)
    {
        $proj = null;
        $getbookmark = SmCpBookmark::findAll([
            'sm_cp_id' => $sm_cp_id,
        ]);
        if (!empty($getbookmark)) {
            foreach ($getbookmark as $getbookmark_val) {
                $proj[] = $getbookmark_val->projects_id;
            }
        }
        return $proj;
    }

    public function getprojofmultism($sm_arr)
    {
        $proj_arr = null;
        if (!empty($sm_arr)) {
            $smcp_arr = SmProjects::findAll([
                'status' => 1,
                'sm_cp_id' => $sm_arr
            ]);
            $proj_txt = '';
            foreach ($smcp_arr as $smcp_arr_val) {
                if ($proj_txt == '') {
                    $proj_txt = $smcp_arr_val->projects_id;
                } else {
                    $proj_txt .= "," . $smcp_arr_val->projects_id;
                }
                /* $temp_arr = explode(",", $proj_arr_val->projects_id);
                array_push($proj_arr, $temp_arr); */
            }
            if (!empty($proj_txt)) {
                $proj_arr = array_unique(explode(",", $proj_txt));
            }
        }
        return $proj_arr;
    }

    public function checkcpprojaccess($cp_id, $id)
    {
        $access = false;
        $get_sm = $this->getsmofcp($cp_id);
        if (!empty($get_sm)) {
            $get_proj = $this->getprojofmultism($get_sm);
            if (!empty($get_proj)) {
                if (in_array($id, $get_proj)) {
                    $access = true;
                }
            }
        }
        return $access;
    }

    public function checkcpleadaccess($sm_cp_id, $id)
    {
        $access = false;
        $get_members = $this->getaccessmembers($sm_cp_id);
        $get_members[] = $sm_cp_id;
        $get_lead_data = Leadmaster::find()->where(['id' => $id])->one();
        if (!empty($get_lead_data)) {
            $created_by = $get_lead_data->created_by;
            if (in_array($created_by, $get_members)) {
                $access = true;
            }
        }

        return $access;
    }

    public function checkcptagaccess($sm_cp_id, $id)
    {
        $access = false;
        $get_members = $this->getaccessmembers($sm_cp_id);
        $get_members[] = $sm_cp_id;
        $get_tag_data = Taggingmaster::find()->where(['id' => $id])->one();
        if (!empty($get_tag_data)) {
            $created_by = $get_tag_data->created_by;
            if (in_array($created_by, $get_members)) {
                $access = true;
            }
        }

        return $access;
    }

    public function getamenities()
    {
        $amenities_arr = null;
        $amenitiesData = (new \yii\db\Query())
            ->select('am.id,am.name,am.icon')
            ->from('amenities am')
            ->createCommand()
            ->queryAll();
        $amenities = null;
        if (!empty($amenitiesData)) {
            foreach ($amenitiesData as $amenitiesData_key => $amenitiesData_val) {
                $amenities[$amenitiesData_key] = $amenitiesData_val;
                if (!empty($amenitiesData_val['icon'])) {
                    $amenities[$amenitiesData_key]['icon'] = \Yii::$app->params['upload_file_path'] . $amenitiesData_val['icon'];
                }
            }
        }
        $amenities_arr['amenities'] = $amenities;
        return $amenities_arr;
    }

    public function getprojectlistapi($get_user, $querydata, $list_type = '')
    {
        $cp_id = $get_user->channelpartner_id;
        $sm_cp_id = $get_user->sm_cp_id;
        $project_arr = null;
        if (empty($list_type)) {
            $pagination = null;
        }
        $project = null;
        /* echo "<pre>";
        print_r($querydata);
        exit; */
        $get_sm = $this->getsmofcp($cp_id);
        /* echo "<pre>";
        print_r($get_sm);
        exit; */
        if (!empty($get_sm)) {
            $get_proj = $this->getprojofmultism($get_sm);
            /* echo "<pre>";
            print_r($get_proj);
            exit; */
            if (!empty($get_proj)) {
                //pending from here
                $get_proj_txt = implode(",", $get_proj);
                if (!empty($querydata)) {

                    $proj_name = (!empty($querydata['name']) ? trim($querydata['name']) : '');
                    $proj_type = (!empty($querydata['type']) ? trim($querydata['type']) : '');
                    $proj_builder_id = (!empty($querydata['builder_id']) ? trim($querydata['builder_id']) : '');
                    $proj_min_price = (!empty($querydata['min_price']) ? trim($querydata['min_price']) : '');
                    $proj_max_price = (!empty($querydata['max_price']) ? trim($querydata['max_price']) : '');
                    $proj_config = (!empty($querydata['config']) ? trim($querydata['config']) : '');
                    $proj_location_id = (!empty($querydata['location_id']) ? implode(",", $querydata['location_id']) : []);
                }

                $order_name = (isset($querydata['sort_name']) ? trim($querydata['sort_name']) : 'projects.name');
                $order_type = (isset($querydata['sort_type']) ? trim($querydata['sort_type']) : 'ASC');


                $page_count = (isset($querydata['page_count']) ? trim($querydata['page_count']) : 25);
                $page = (isset($querydata['page']) ? trim($querydata['page']) : 1);
                if ($page == 1) {
                    $offset = 0;
                } else {
                    $offset = (($page - 1) * $page_count);
                }

                $where_sql = 'projects.id IN(' . $get_proj_txt . ') AND projects.status=1 ';
                if (!empty($proj_name)) {
                    $where_sql .= ' AND projects.name like "%' . $proj_name . '%"';
                }
                if (!empty($proj_builder_id)) {
                    $where_sql .= ' AND projects.builder_id="' . $proj_builder_id . '"';
                }
                if (!empty($proj_type)) {
                    $where_sql .= ' AND projects.type="' . $proj_type . '"';
                }
                if (!empty($proj_min_price)) {
                    $where_sql .= ' AND m.low_price>=' . $proj_min_price . '';
                }
                if (!empty($proj_max_price)) {
                    $where_sql .= ' AND m.low_price<=' . $proj_max_price . '';
                }
                if (!empty($proj_config)) {
                    $where_sql .= ' AND FIND_IN_SET("' . (int) $proj_config . '",f.flat_types)';
                }
                if (!empty($proj_location_id)) {
                    $where_sql .= ' AND projects.location_id IN(' . $proj_location_id . ')';
                }
                if (isset($querydata['possession']) && $querydata['possession'] == 'true') {
                    $where_sql .= ' AND projects.possession_date<"' . date('Y-m-d') . '"';
                } else if (isset($querydata['possession']) && $querydata['possession'] == 'false') {
                    $where_sql .= ' AND projects.possession_date>"' . date('Y-m-d') . '"';
                }


                if (empty($list_type)) {
                    $proj_query = (new \yii\db\Query())
                        ->select('projects.id,projects.name,projects.site_address,projects.possession_date,projects.builder_id,projects.logo_name,projects.type,projects.flat_configuration,projects.project_segment,projects.created_on,m.low_price,f.flat_types,projects.latlong,projects.whatsapp_brief')
                        ->from('projects')
                        ->leftJoin("(select MIN(base_price) as low_price,projects_id from flat group by projects_id ) m", 'm.projects_id=projects.id')
                        /* ->leftJoin("(select sdp.name as primary_banner,sdp.projects_id from snse_dooits_photo sdp where sdp.default=2 group by sdp.projects_id ) sp", 'sp.projects_id=projects.id') */
                        ->leftJoin("(select GROUP_CONCAT(flat_type_id) as flat_types,projects_id from flat GROUP by projects_id ) f", 'f.projects_id=projects.id')
                        ->where($where_sql)
                        ->orderBy($order_name . ' ' . $order_type);
                    $proj_count = $proj_query->count();
                    /* echo $proj_count;
                exit; */
                    $proj_data = $proj_query->limit($page_count)->offset($offset)->createCommand()
                        /* ->getRawsql();
                    echo $proj_data;
                    exit; */
                        ->queryAll();
                } else {
                    $proj_query = (new \yii\db\Query())
                        ->select('projects.id,projects.name,projects.whatsapp_brief')
                        ->from('projects')
                        ->leftJoin("(select MIN(base_price) as low_price,projects_id from flat group by projects_id ) m", 'm.projects_id=projects.id')
                        ->leftJoin("(select GROUP_CONCAT(flat_type_id) as flat_types,projects_id from flat GROUP by projects_id ) f", 'f.projects_id=projects.id')
                        ->where($where_sql)
                        ->orderBy($order_name . ' ' . $order_type);

                    $proj_data = $proj_query->createCommand()
                        ->queryAll();
                }


                if (!empty($proj_data)) {
                    $check_proj_count = 0;      //added for testing
                    $check_multi_count = 1;     //added for testing
                    while ($check_multi_count < 6) {    //added for testing
                        foreach ($proj_data as $proj_data_key => $proj_data_val) {
                            $proj_data_key = $check_proj_count;     //added for testing
                            $project[$proj_data_key] = $proj_data_val;

                            if (empty($list_type)) {

                                //get banner
                                $getbanner = SnseBanner::findOne([
                                    'projects_id' => $proj_data_val['id'],
                                    'status' => 1
                                ]);
                                if (!empty($getbanner)) {
                                    $project[$proj_data_key]['banner'] = \Yii::$app->params['web_path'] . "files/banner/orig/" . $getbanner->name;
                                } else {
                                    $project[$proj_data_key]['banner'] = null;
                                }

                                /* if (!empty($proj_data_val['primary_banner'])) {
                                $project[$proj_data_key]['banner'] = \Yii::$app->params['upload_file_path'] . $proj_data_val['primary_banner'];
                                } */
                                $builder_data = Builder::find()->select('name')->where(['id' => $proj_data_val['builder_id']])->one();
                                if (!empty($builder_data)) {
                                    $project[$proj_data_key]['builder'] = $builder_data->name;
                                } else {
                                    $project[$proj_data_key]['builder'] = '';
                                }

                                if (!empty($proj_data_val['type'])) {
                                    $proj_type = explode(",", $proj_data_val['type']);
                                    $proj_type_txt = '';
                                    foreach ($proj_type as $proj_type_val) {
                                        if (empty($proj_type_txt)) {
                                            $proj_type_txt = $this->project_type[$proj_type_val];
                                        } else {
                                            $proj_type_txt .= "," . $this->project_type[$proj_type_val];
                                        }
                                    }
                                    $project[$proj_data_key]['type'] = $proj_type_txt;
                                }
                                if (!empty($proj_data_val['low_price'])) {
                                    $project[$proj_data_key]['price'] = $proj_data_val['low_price'];
                                    $project[$proj_data_key]['priceinwords'] = $this->AmountInWords($proj_data_val['low_price']);
                                }

                                if (!empty($proj_data_val['project_segment'])) {
                                    $project[$proj_data_key]['project_segment'] = $this->project_segment[$proj_data_val['project_segment']];
                                }

                                $project[$proj_data_key]['bookmark'] = 'false';
                                $getbookmark = SmCpBookmark::findOne([
                                    'sm_cp_id' => $sm_cp_id,
                                    'projects_id' => $proj_data_val['id'],
                                ]);
                                if (!empty($getbookmark)) {
                                    $project[$proj_data_key]['bookmark'] = 'true';
                                }
                            }

                            //get sourcing
                            $sourcingData = (new \yii\db\Query())
                                ->select('sm.sm_cp_id,sm.name,sm.role,sm.contact')
                                ->from('sm_projects sp')
                                ->leftJoin("sm_cp as sm", 'sp.sm_cp_id = sm.sm_cp_id')
                                ->where('FIND_IN_SET("' . (int) $proj_data_val['id'] . '",projects_id) AND sp.status=1')->groupBy('sp.sm_cp_id')->createCommand()
                                ->queryAll();
                            $sourcing = null;
                            if (!empty($sourcingData)) {
                                $s_count = 0;
                                foreach ($sourcingData as $sourcingData_val) {
                                    $sourcing[$s_count]['sm_cp_id'] = $sourcingData_val['sm_cp_id'];
                                    $sourcing[$s_count]['name'] = $sourcingData_val['name'];
                                    $sourcing[$s_count]['role'] = $this->role[$sourcingData_val['role']];
                                    $sourcing[$s_count]['contact'] = $sourcingData_val['contact'];
                                    $s_count++;
                                }
                                $project[$proj_data_key]['sourcing'] = $sourcing;
                            }

                            //get base price of lowest flat
                            $flatData = (new \yii\db\Query())
                                ->select('flat.base_price,flat.flat_type_id')
                                ->from('flat')
                                ->where('flat.projects_id="' . $proj_data_val['id'] . '" AND flat.status=1')
                                ->orderBy('flat.total_charge ASC')->createCommand()
                                ->queryAll();

                            if (!empty($flatData)) {
                                $flat_type_list_arr = [];
                                foreach ($flatData as $flatData_v) {
                                    $flat_type_list_arr[] = (int) $flatData_v['flat_type_id'];
                                }
                                $flat_type_list_arr = array_unique($flat_type_list_arr);
                                $final_flat_type = [];
                                foreach ($flat_type_list_arr as $flat_type_list_arr_val) {
                                    if (in_array($flat_type_list_arr_val, array_keys($this->flat_type_id))) {
                                        $final_flat_type[$flat_type_list_arr_val] = $this->flat_type_id[$flat_type_list_arr_val];
                                    }
                                }
                                $project[$proj_data_key]['flat_type'] = $final_flat_type;
                            }

                            //updating lat long
                            $project[$proj_data_key]['lat'] = null;
                            $project[$proj_data_key]['long'] = null;
                            if (!empty($project[$proj_data_key]['latlong'])) {
                                $getlat_long = explode(",", $project[$proj_data_key]['latlong']);
                                $project[$proj_data_key]['lat'] = trim($getlat_long[0]);
                                $project[$proj_data_key]['long'] = trim($getlat_long[1]);
                            }

                            if (!empty($proj_data_val['whatsapp_brief'])) {
                                $get_whatsapp_brief = trim(preg_replace('/\s+/', ' ', $proj_data_val['whatsapp_brief']));
                                $project[$proj_data_key]['whatsapp_brief'] = $get_whatsapp_brief;
                            }

                            if (empty($list_type)) {
                                unset($project[$proj_data_key]['logo_name'], $project[$proj_data_key]['low_price'], $project[$proj_data_key]['builder_id'], $project[$proj_data_key]['flat_types'], $project[$proj_data_key]['latlong']);
                            }

                            $check_proj_count++;    //added for testing
                        }
                        $check_multi_count++;       //added for testing
                    }       //added for testing
                }

                if (empty($list_type)) {
                    //pagination code start
                    $pgdata['page'] = $page;
                    $pgdata['content_count'] = $proj_count * 5;     //added for testing
                    //$pgdata['content_count'] = $proj_count;
                    $pgdata['page_count'] = $page_count;
                    $pagination = $this->getpagination($pgdata);
                    //pagination code end
                }

                /* echo "<pre>";
                print_r($project);
                exit; */
            }
        }

        $project_arr['project'] = $project;
        if (empty($list_type)) {
            $project_arr['pagination'] = $pagination;
        }

        return $project_arr;
    }

    public function getprojectlistbookmarkapi($get_user, $querydata)
    {
        $cp_id = $get_user->channelpartner_id;
        $sm_cp_id = $get_user->sm_cp_id;
        $project_arr = null;
        $pagination = null;
        $project = null;
        /* echo "<pre>";
        print_r($querydata);
        exit; */

        $get_sm = $this->getsmofcp($cp_id);
        if (!empty($get_sm)) {
            $get_proj_sm = $this->getprojofmultism($get_sm);

            $get_proj_book = $this->getbookmarkprojects($sm_cp_id);
            $get_proj = [];
            if (!empty($get_proj_book)) {
                $get_proj = array_intersect($get_proj_book, $get_proj_sm);
            }

            if (!empty($get_proj)) {
                //pending from here
                $get_proj_txt = implode(",", $get_proj);
                if (!empty($querydata)) {
                    $proj_name = (!empty($querydata['name']) ? trim($querydata['name']) : '');
                    $proj_type = (!empty($querydata['type']) ? trim($querydata['type']) : '');
                    $proj_builder_id = (!empty($querydata['builder_id']) ? trim($querydata['builder_id']) : '');
                    $proj_min_price = (!empty($querydata['min_price']) ? trim($querydata['min_price']) : '');
                    $proj_max_price = (!empty($querydata['max_price']) ? trim($querydata['max_price']) : '');
                    $proj_config = (!empty($querydata['config']) ? trim($querydata['config']) : '');
                    $proj_location_id = (!empty($querydata['location_id']) ? implode(",", $querydata['location_id']) : []);
                }

                $order_name = (isset($querydata['sort_name']) ? trim($querydata['sort_name']) : 'projects.name');
                $order_type = (isset($querydata['sort_type']) ? trim($querydata['sort_type']) : 'ASC');


                $page_count = (isset($querydata['page_count']) ? trim($querydata['page_count']) : 25);
                $page = (isset($querydata['page']) ? trim($querydata['page']) : 1);
                if ($page == 1) {
                    $offset = 0;
                } else {
                    $offset = (($page - 1) * $page_count);
                }

                $where_sql = 'projects.id IN(' . $get_proj_txt . ') AND projects.status=1 ';
                if (!empty($proj_name)) {
                    $where_sql .= ' AND projects.name like "%' . $proj_name . '%"';
                }
                if (!empty($proj_type)) {
                    $where_sql .= ' AND projects.type="' . $proj_type . '"';
                }
                if (!empty($proj_builder_id)) {
                    $where_sql .= ' AND projects.builder_id="' . $proj_builder_id . '"';
                }
                if (!empty($proj_min_price)) {
                    $where_sql .= ' AND m.low_price>=' . $proj_min_price . '';
                }
                if (!empty($proj_max_price)) {
                    $where_sql .= ' AND m.low_price<=' . $proj_max_price . '';
                }
                if (!empty($proj_config)) {
                    $where_sql .= ' AND FIND_IN_SET("' . (int) $proj_config . '",f.flat_types)';
                }
                if (!empty($proj_location_id)) {
                    $where_sql .= ' AND projects.location_id IN(' . $proj_location_id . ')';
                }
                if (isset($querydata['possession']) && $querydata['possession'] == 'true') {
                    $where_sql .= ' AND projects.possession_date<"' . date('Y-m-d') . '"';
                } else if (isset($querydata['possession']) && $querydata['possession'] == 'false') {
                    $where_sql .= ' AND projects.possession_date>"' . date('Y-m-d') . '"';
                }


                $proj_query = (new \yii\db\Query())
                    ->select('projects.id,projects.name,projects.site_address,projects.possession_date,projects.builder_id,projects.logo_name,projects.type,projects.flat_configuration,projects.project_segment,projects.created_on,m.low_price,f.flat_types,projects.latlong,projects.whatsapp_brief')
                    ->from('projects')
                    ->leftJoin("(select MIN(base_price) as low_price,projects_id from flat group by projects_id ) m", 'm.projects_id=projects.id')
                    /* ->leftJoin("(select sdp.name as primary_banner,sdp.projects_id from snse_dooits_photo sdp where sdp.default=2 group by sdp.projects_id ) sp", 'sp.projects_id=projects.id') */
                    ->leftJoin("(select GROUP_CONCAT(flat_type_id) as flat_types,projects_id from flat GROUP by projects_id ) f", 'f.projects_id=projects.id')
                    ->where($where_sql)
                    ->orderBy($order_name . ' ' . $order_type);
                $proj_count = $proj_query->count();
                /* echo $proj_count;
                exit; */
                $proj_data = $proj_query->limit($page_count)->offset($offset)->createCommand()
                    /* ->getRawsql();
                echo $proj_data;
                exit; */
                    ->queryAll();

                if (!empty($proj_data)) {
                    foreach ($proj_data as $proj_data_key => $proj_data_val) {
                        $project[$proj_data_key] = $proj_data_val;

                        //get banner
                        $getbanner = SnseBanner::findOne([
                            'projects_id' => $proj_data_val['id'],
                            'status' => 1
                        ]);
                        if (!empty($getbanner)) {
                            $project[$proj_data_key]['banner'] = \Yii::$app->params['web_path'] . "files/banner/orig/" . $getbanner->name;
                        } else {
                            $project[$proj_data_key]['banner'] = null;
                        }


                        /* if (!empty($proj_data_val['primary_banner'])) {
                            $project[$proj_data_key]['banner'] = \Yii::$app->params['upload_file_path'] . $proj_data_val['primary_banner'];
                        } */
                        $builder_data = Builder::find()->select('name')->where(['id' => $proj_data_val['builder_id']])->one();
                        if (!empty($builder_data)) {
                            $project[$proj_data_key]['builder'] = $builder_data->name;
                        } else {
                            $project[$proj_data_key]['builder'] = '';
                        }

                        if (!empty($proj_data_val['type'])) {
                            $proj_type = explode(",", $proj_data_val['type']);
                            $proj_type_txt = '';
                            foreach ($proj_type as $proj_type_val) {
                                if (empty($proj_type_txt)) {
                                    $proj_type_txt = $this->project_type[$proj_type_val];
                                } else {
                                    $proj_type_txt .= "," . $this->project_type[$proj_type_val];
                                }
                            }
                            $project[$proj_data_key]['type'] = $proj_type_txt;
                        }
                        if (!empty($proj_data_val['low_price'])) {
                            $project[$proj_data_key]['price'] = $proj_data_val['low_price'];
                            $project[$proj_data_key]['priceinwords'] = $this->AmountInWords($proj_data_val['low_price']);
                        }
                        if (!empty($proj_data_val['project_segment'])) {
                            $project[$proj_data_key]['project_segment'] = $this->project_segment[$proj_data_val['project_segment']];
                        }


                        //get sourcing
                        $sourcingData = (new \yii\db\Query())
                            ->select('sm.sm_cp_id,sm.name,sm.role,sm.contact')
                            ->from('sm_projects sp')
                            ->leftJoin("sm_cp as sm", 'sp.sm_cp_id = sm.sm_cp_id')
                            ->where('FIND_IN_SET("' . (int) $proj_data_val['id'] . '",projects_id) AND sp.status=1')->groupBy('sp.sm_cp_id')->createCommand()
                            ->queryAll();
                        $sourcing = null;
                        if (!empty($sourcingData)) {
                            $s_count = 0;
                            foreach ($sourcingData as $sourcingData_val) {
                                $sourcing[$s_count]['sm_cp_id'] = $sourcingData_val['sm_cp_id'];
                                $sourcing[$s_count]['name'] = $sourcingData_val['name'];
                                $sourcing[$s_count]['role'] = $this->role[$sourcingData_val['role']];
                                $sourcing[$s_count]['contact'] = $sourcingData_val['contact'];
                                $s_count++;
                            }
                            $project[$proj_data_key]['sourcing'] = $sourcing;
                        }

                        //get base price of lowest flat
                        $flatData = (new \yii\db\Query())
                            ->select('flat.base_price,flat.flat_type_id')
                            ->from('flat')
                            ->where('flat.projects_id="' . $proj_data_val['id'] . '" AND flat.status=1')
                            ->orderBy('flat.total_charge ASC')->createCommand()
                            ->queryAll();

                        if (!empty($flatData)) {
                            $flat_type_list_arr = [];
                            foreach ($flatData as $flatData_v) {
                                $flat_type_list_arr[] = (int) $flatData_v['flat_type_id'];
                            }
                            $flat_type_list_arr = array_unique($flat_type_list_arr);
                            $final_flat_type = [];
                            foreach ($flat_type_list_arr as $flat_type_list_arr_val) {
                                if (in_array($flat_type_list_arr_val, array_keys($this->flat_type_id))) {
                                    $final_flat_type[$flat_type_list_arr_val] = $this->flat_type_id[$flat_type_list_arr_val];
                                }
                            }
                            $project[$proj_data_key]['flat_type'] = $final_flat_type;
                        }


                        //updating lat long
                        $project[$proj_data_key]['lat'] = null;
                        $project[$proj_data_key]['long'] = null;
                        if (!empty($project[$proj_data_key]['latlong'])) {
                            $getlat_long = explode(",", $project[$proj_data_key]['latlong']);
                            $project[$proj_data_key]['lat'] = trim($getlat_long[0]);
                            $project[$proj_data_key]['long'] = trim($getlat_long[1]);
                        }

                        if (!empty($proj_data_key['whatsapp_brief'])) {
                            $get_whatsapp_brief = trim(preg_replace('/\s+/', ' ', $proj_data_key['whatsapp_brief']));
                            $project[$proj_data_key]['whatsapp_brief'] = $get_whatsapp_brief;
                        }

                        $project[$proj_data_key]['bookmark'] = 'true';

                        unset($project[$proj_data_key]['logo_name'], $project[$proj_data_key]['low_price'], $project[$proj_data_key]['builder_id'], $project[$proj_data_key]['flat_types']);
                    }
                }

                //pagination code start
                $pgdata['page'] = $page;
                $pgdata['content_count'] = $proj_count;
                $pgdata['page_count'] = $page_count;
                $pagination = $this->getpagination($pgdata);
                //pagination code end

                /* echo "<pre>";
                print_r($project);
                exit; */
            }
        }


        $project_arr['project'] = $project;
        $project_arr['pagination'] = $pagination;

        return $project_arr;
    }

    public function getpagination($data_arr)
    {
        $pagination = null;
        $page = intval($data_arr['page']);
        $content_count = intval($data_arr['content_count']);
        $page_count = intval($data_arr['page_count']);

        $total_pages = floor($content_count / $page_count);
        if (($content_count % $page_count) != 0) {
            $total_pages = $total_pages + 1;
        }
        if ($total_pages > 1) {

            $pagination['current_page'] = $page;
            $pagination['total_count'] = $content_count;

            //page link
            $current_page_link = Yii::$app->request->hostInfo . Yii::$app->request->url;
            $txtcheck   = 'page=';
            if ($page < $total_pages) {
                $next_page = $page + 1;
                /* if (str_contains($current_page_link, $txtcheck)) {
                    $next_page = str_replace("page=" . $page, "page=" . $page + 1, $current_page_link);
                } else {
                    $next_page = $current_page_link . "&page=" . $page + 1;
                } */
                $pagination['next_page'] = $next_page;
            }
            if ($page - 1) {
                $previous_page = $page - 1;
                /* if (str_contains($current_page_link, $txtcheck)) {
                    $previous_page = str_replace("page=" . $page, "page=" . $page - 1, $current_page_link);
                } */
                $pagination['previous_page'] = $previous_page;
            }
            $pagination['total_page'] = $total_pages;
        }
        return $pagination;
    }


    public function getprojectapi($proj_id, $sm_cp_id)
    {
        $project_arr = null;
        $project = null;
        $proj_data = Projects::find()->select('id,name,builder_id,logo_name,type,location_id,site_address,sale_address,project_description,signification,connectivity,website_address,rera_no,approved_by,no_of_tower,possession_date,flat_configuration,structure,pdf_upload,project_status,project_segment,launch_date,google_map,google_link,status,created_on,modified_on,amenities,latlong,whatsapp_brief')->where(['id' => $proj_id, 'status' => 1])->one();
        if (!empty($proj_data)) {

            $location_arr = Location::findAll([
                'status' => 1
            ]);
            foreach ($location_arr as $location_arr_val) {
                $locations[$location_arr_val->id] = $location_arr_val->name;
            }
            $project = $proj_data->toArray();

            if (!empty($project['location_id'])) {
                $get_loc = $project['location_id'];
                $get_loc_name = $locations[$get_loc];

                $project['location_id'] = $get_loc_name;
            } else {
                $project['location_id'] = '';
            }

            $builder_data = Builder::find()->select('name')->where(['id' => $project['builder_id']])->one();
            if (!empty($builder_data)) {
                $project['builder'] = $builder_data->name;
            } else {
                $project['builder'] = '';
            }

            //get banner
            $getbanner = SnseBanner::findOne([
                'projects_id' => $proj_id,
                'status' => 1
            ]);
            if (!empty($getbanner)) {
                $project['banner'] = \Yii::$app->params['web_path'] . "files/banner/orig/" . $getbanner->name;
            } else {
                $project['banner'] = null;
            }

            /*  if (!empty($project['logo_name'])) {
                $project['banner'] = \Yii::$app->params['upload_file_path'] . $project['logo_name'];
            } */
            if (!empty($project['type'])) {
                $proj_type = explode(",", $project['type']);
                $proj_type_txt = '';
                foreach ($proj_type as $proj_type_val) {
                    if (empty($proj_type_txt)) {
                        $proj_type_txt = $this->project_type[$proj_type_val];
                    } else {
                        $proj_type_txt .= "," . $this->project_type[$proj_type_val];
                    }
                }
                $project['type'] = $proj_type_txt;
            }
            //get base price of lowest flat
            $flatData = (new \yii\db\Query())
                ->select('flat.base_price,flat.flat_type_id')
                ->from('flat')
                ->where('flat.projects_id="' . $proj_id . '" AND flat.status=1')
                ->orderBy('flat.total_charge ASC')->createCommand()
                ->queryAll();

            if (!empty($flatData)) {
                $project['price'] = $flatData[0]['base_price'];
                $project['priceinwords'] = $this->AmountInWords($project['price']);
                $flat_type_list_arr = [];
                foreach ($flatData as $flatData_v) {
                    $flat_type_list_arr[] = (int) $flatData_v['flat_type_id'];
                }
                $flat_type_list_arr = array_unique($flat_type_list_arr);
                $final_flat_type = [];
                foreach ($flat_type_list_arr as $flat_type_list_arr_val) {
                    if (in_array($flat_type_list_arr_val, array_keys($this->flat_type_id))) {
                        $final_flat_type[$flat_type_list_arr_val] = $this->flat_type_id[$flat_type_list_arr_val];
                    }
                }
                $project['flat_type'] = $final_flat_type;
            }

            if (!empty($project['pdf_upload'])) {
                $project['pdf'] = \Yii::$app->params['upload_file_path'] . $project['pdf_upload'];
            }
            if (!empty($project['project_status'])) {
                $project['project_status'] = $this->project_status[$project['project_status']];
            }
            if (!empty($project['project_segment'])) {
                $project['project_segment'] = $this->project_segment[$project['project_segment']];
            }
            //get bookmark
            $getbookmark = SmCpBookmark::findOne([
                'sm_cp_id' => $sm_cp_id,
                'projects_id' => $proj_id,
            ]);
            if (!empty($getbookmark)) {
                $project['bookmark'] = true;
            } else {
                $project['bookmark'] = false;
            }

            $proj_amenities = $project['amenities'];

            //updating lat long
            $project['lat'] = null;
            $project['long'] = null;
            if (!empty($project['latlong'])) {
                $getlat_long = explode(",", $project['latlong']);
                $project['lat'] = trim($getlat_long[0]);
                $project['long'] = trim($getlat_long[1]);
            }

            if (!empty($project['whatsapp_brief'])) {
                $get_whatsapp_brief = trim(preg_replace('/\s+/', ' ', $project['whatsapp_brief']));
                $project['whatsapp_brief'] = $get_whatsapp_brief;
            }


            unset($project['amenities'], $project['builder_id'], $project['logo_name'], $project['latlong']);

            //get collateral
            $collateralData = (new \yii\db\Query())
                ->select('sphoto.docs_name,sphoto.name')
                ->from('snse_dooits_photo as sphoto')
                ->where('sphoto.projects_id="' . $proj_id . '" AND sphoto.photo_category!=6 AND sphoto.status=1')->createCommand()
                ->queryAll();

            $collateral = null;
            if (!empty($collateralData)) {
                $col_count = 0;
                foreach ($collateralData as $collateralData_val) {
                    $collateral[$col_count]['docs_name'] = $collateralData_val['docs_name'];
                    $collateral[$col_count]['docs_link'] = \Yii::$app->params['upload_file_path'] . rawurlencode($collateralData_val['name']);
                    $col_count++;
                }
            }
            //get amenities
            $amenities = null;
            if (!empty($proj_amenities)) {
                $amenitiesData = (new \yii\db\Query())
                    ->select('name,icon')
                    ->from('amenities')
                    ->where('id IN(' . $proj_amenities . ') AND status=1 ')->createCommand()
                    ->queryAll();

                if (!empty($amenitiesData)) {
                    $amn_count = 0;
                    foreach ($amenitiesData as $amenitiesData_val) {
                        $amenities[$amn_count]['name'] = $amenitiesData_val['name'];
                        $amenities[$amn_count]['icon'] = (!empty($amenitiesData_val['icon'])) ? \Yii::$app->params['upload_file_path'] . $amenitiesData_val['icon'] : '';
                        $amn_count++;
                    }
                }
            }
            //get gallery
            $galleryData = (new \yii\db\Query())
                ->select('sphoto.docs_name,sphoto.name')
                ->from('snse_dooits_photo as sphoto')
                ->where('sphoto.projects_id="' . $proj_id . '" AND sphoto.photo_category=6 AND sphoto.status=1 ')->createCommand()
                ->queryAll();
            $gallery = null;
            if (!empty($galleryData)) {
                $gal_count = 0;
                foreach ($galleryData as $galleryData_val) {
                    $gallery[$gal_count]['docs_name'] = $galleryData_val['docs_name'];
                    $gallery[$gal_count]['docs_link'] = \Yii::$app->params['upload_file_path'] . rawurlencode($galleryData_val['name']);
                    $gal_count++;
                }
            }
            //get sourcing
            $sourcingData = (new \yii\db\Query())
                ->select('sm.sm_cp_id,sm.name,sm.role,sm.contact')
                ->from('sm_projects sp')
                ->leftJoin("sm_cp as sm", 'sp.sm_cp_id = sm.sm_cp_id')
                ->where('FIND_IN_SET("' . (int) $proj_id . '",projects_id) AND sp.status=1')->groupBy('sp.sm_cp_id')->createCommand()
                ->queryAll();
            $sourcing = null;
            if (!empty($sourcingData)) {
                $s_count = 0;
                foreach ($sourcingData as $sourcingData_val) {
                    $sourcing[$s_count]['sm_cp_id'] = $sourcingData_val['sm_cp_id'];
                    $sourcing[$s_count]['name'] = $sourcingData_val['name'];
                    $sourcing[$s_count]['role'] = $this->role[$sourcingData_val['role']];
                    $sourcing[$s_count]['contact'] = $sourcingData_val['contact'];
                    $s_count++;
                }
            }
        }
        $project_arr['project'] = $project;
        $project_arr['collateral'] = $collateral;
        $project_arr['amenities'] = $amenities;
        $project_arr['gallery'] = $gallery;
        $project_arr['sourcing'] = $sourcing;


        return $project_arr;
    }

    public function getleadapi($l_id, $lead_data, $show_feed = '')
    {
        $lead_arr = null;
        $lead = null;
        if (empty($lead_data)) {
            $lead_data = Leadmaster::find()->select('id,lead_id,name,contact,email,flat_type_id,budget,customer_type,followup_date,status,tagging,created_by,created_on,modified_by,modified_on')->where(['id' => $l_id])->one();
            $lead = $lead_data->toArray();
        } else {
            $lead = $lead_data->toArray();
            unset($lead['cp_id'], $lead['created_from']);
        }
        if (!empty($lead_data)) {
            $lead['id'] = (string) $lead['id'];
            $lead['flat_type_text'] = null;
            if (!empty($lead['flat_type_id'])) {
                $lead['flat_type_id'] = (string) $lead['flat_type_id'];
                $lead['flat_type_text'] = $this->flat_type_id[$lead['flat_type_id']];
            }
            $lead['budgetinwords'] = null;
            if (!empty($lead['budget'])) {
                $lead['budget'] = (string) $lead['budget'];
                $lead['budgetinwords'] = $this->AmountInWords($lead['budget']);
            }

            if (!empty($lead['customer_type'])) {
                $lead['customer_type'] = $this->customer_type[$lead['customer_type']];
            }
            if (!empty($lead['status'])) {
                $lead['status'] = $this->lead_status[$lead['status']];
            }
            if (!empty($lead['tagging'])) {
                $lead['tagging'] = 'true';
            } else {
                $lead['tagging'] = 'false';
            }
            $lead['tag_count'] = "0";
            if ($lead['tagging'] == 'true') {
                $tag_query = (new \yii\db\Query())
                    ->select('id')
                    ->from('taggingmaster')
                    ->where("taggingmaster.lead_id='" . $l_id . "'");
                $tag_count = $tag_query->count();
                $lead['tag_count'] = $tag_count;
            }

            if (!empty($lead['created_by'])) {
                $created_by_model = SmCp::find()->where(['sm_cp_id' => $lead['created_by']])->one();
                $lead['created_by'] = $created_by_model->name;
            }
            if (!empty($lead['modified_by'])) {
                $modified_by_model = SmCp::find()->where(['sm_cp_id' => $lead['modified_by']])->one();
                $lead['modified_by'] = $modified_by_model->name;
            }

            $lead_arr['lead'] = $lead;
            if (!empty($show_feed)) {
                //get lead feedback
                $feedData = (new \yii\db\Query())
                    ->select('fd.id,fd.feedback,fd.created_on,sm.sm_cp_id,sm.name,sm.role')
                    ->from('lead_feedback fd')
                    ->leftJoin("sm_cp as sm", 'fd.created_by = sm.sm_cp_id')
                    ->where('fd.lead_id="' . $l_id . '"')
                    ->orderBy('fd.id DESC')
                    ->limit(5)
                    ->createCommand()
                    ->queryAll();
                $feedback = null;
                if (!empty($feedData)) {
                    $f_count = 0;
                    foreach ($feedData as $feedData_val) {
                        //$feedback[$f_count]['id'] = $feedData_val['id'];
                        $feedback[$f_count]['feedback'] = $feedData_val['feedback'];
                        $feedback[$f_count]['created_on'] = $feedData_val['created_on'];
                        //$feedback[$f_count]['sm_cp_id'] = $feedData_val['sm_cp_id'];
                        $feedback[$f_count]['created_by'] = $feedData_val['name'];
                        //$feedback[$f_count]['role'] = $this->role[$feedData_val['role']];
                        $f_count++;
                    }
                }
                $lead_arr['lead']['feedback'] = $feedback;
            }

            $lead_arr['flat_type'] = $this->flat_type_id;
            $lead_arr['customer_type'] = $this->customer_type;
            $lead_arr['status'] = $this->lead_status;
            $lead_arr['tagging'] = $this->tagging_done;
        }
        return $lead_arr;
    }

    public function gettagapi($tag_id, $tag_data, $show_feed = '')
    {
        $tag_arr = null;
        $tag = null;
        if (empty($tag_data)) {
            $tag_data = Taggingmaster::find()->select('id,lead_id,tagging_id,tag_name,tag_phone,tag_email,flat_type_id,sm_cp_id,project_id,budget,customer_type,appointment_date,appointment_time,status,created_by,created_on,modified_by,modified_on,visit_notvisit_clash_date,lead_convert_date,lead_dead_date,lead_registered_date')->where(['id' => $tag_id])->one();
            $tag = $tag_data->toArray();
        } else {
            $tag = $tag_data->toArray();
            unset($tag['is_deleted'], $tag['deleted_on'], $tag['deleted_by'], $tag['created_from'], $tag['cp_id'], $tag['is_accepted']);
        }

        if (!empty($tag_data)) {

            $lead_data = Leadmaster::find()->select('id,lead_id,name')->where(['id' => $tag['lead_id']])->one();
            if (!empty($lead_data)) {
                $tag['lead_id'] = $lead_data->lead_id;
            }
            $tag['id'] = (string) $tag['id'];
            if (!empty($tag['tag_name'])) {
                $tag['name'] = $tag['tag_name'];
                unset($tag['tag_name']);
            }
            if (!empty($tag['tag_phone'])) {
                $tag['contact'] = $tag['tag_phone'];
                unset($tag['tag_phone']);
            }
            if (!empty($tag['tag_email'])) {
                $tag['email'] = $tag['tag_email'];
                unset($tag['tag_email']);
            }
            $tag['flat_type_text'] = null;
            if (!empty($tag['flat_type_id'])) {
                $tag['flat_type_id'] = (string) $tag['flat_type_id'];
                $tag['flat_type_text'] = $this->flat_type_id[$tag['flat_type_id']];
            }
            $tag['budgetinwords'] = null;
            if (!empty($tag['budget'])) {
                $tag['budget'] = (string) $tag['budget'];
                $tag['budgetinwords'] = $this->AmountInWords($tag['budget']);
            }


            if (!empty($tag['customer_type'])) {
                $tag['customer_type'] = $this->customer_type[$tag['customer_type']];
            }
            if (!empty($tag['status'])) {
                $tag['status'] = $this->tag_status[$tag['status']];

                if ($tag['status'] == 'Visited') {
                    $tag['visited_on'] = $tag['visit_notvisit_clash_date'];
                } else if ($tag['status'] == 'Clashed') {
                    $tag['clash_on'] = $tag['visit_notvisit_clash_date'];
                } else if ($tag['status'] == 'Lead Converted') {
                    $tag['lead_convert_on'] = $tag['lead_convert_date'];
                } else if ($tag['status'] == 'Lead Dead') {
                    $tag['lead_dead_on'] = $tag['lead_dead_date'];
                } else if ($tag['status'] == 'Lead Registered') {
                    $tag['lead_registered_on'] = $tag['lead_registered_date'];
                }
            } else {
                $tag['status'] = 'In Progress';
            }
            unset($tag['visit_notvisit_clash_date'], $tag['lead_convert_date'], $tag['lead_dead_date'], $tag['lead_registered_date']);

            if (!empty($tag['project_id'])) {
                $project_model = Projects::find()->where(['id' => $tag['project_id']])->one();
                $tag['project_id'] = $project_model->name;
            }
            if (!empty($tag['sm_cp_id'])) {
                $sourcing_model = SmCp::find()->where(['sm_cp_id' => $tag['sm_cp_id']])->one();
                $tag['sourcing_id'] = $sourcing_model->name;
                unset($tag['sm_cp_id']);
            }

            if (!empty($tag['created_by'])) {
                $created_by_model = SmCp::find()->where(['sm_cp_id' => $tag['created_by']])->one();
                $tag['created_by'] = $created_by_model->name;
            }
            if (!empty($tag['modified_by'])) {
                $modified_by_model = SmCp::find()->where(['sm_cp_id' => $tag['modified_by']])->one();
                $tag['modified_by'] = $modified_by_model->name;
            }

            $tag_arr['tagging'] = $tag;
            if (!empty($show_feed)) {
                //get lead feedback
                $feedData = (new \yii\db\Query())
                    ->select('fd.id,fd.feedback,fd.created_on,sm.sm_cp_id,sm.name,sm.role')
                    ->from('tagging_feedback fd')
                    ->leftJoin("sm_cp as sm", 'fd.created_by = sm.sm_cp_id')
                    ->where('fd.tagging_id="' . $tag_id . '"')
                    ->orderBy('fd.id DESC')
                    ->limit(5)  //show last 5 feedback
                    ->createCommand()
                    ->queryAll();
                $feedback = null;
                if (!empty($feedData)) {
                    $f_count = 0;
                    foreach ($feedData as $feedData_val) {
                        //$feedback[$f_count]['id'] = $feedData_val['id'];
                        $feedback[$f_count]['feedback'] = $feedData_val['feedback'];
                        $feedback[$f_count]['created_on'] = $feedData_val['created_on'];
                        //$feedback[$f_count]['sm_cp_id'] = $feedData_val['sm_cp_id'];
                        $feedback[$f_count]['created_by'] = $feedData_val['name'];
                        //$feedback[$f_count]['role'] = $this->role[$feedData_val['role']];
                        $f_count++;
                    }
                }
                $tag_arr['tagging']['feedback'] = $feedback;
            }

            if (!empty($show_feed)) {
                $tag_arr['flat_type'] = $this->flat_type_id;
                $tag_arr['customer_type'] = $this->customer_type;
                $tag_arr['status'] = $this->tag_status;
            }
        }
        return $tag_arr;
    }

    public function getticketlistapi($get_user, $querydata)
    {
        $sm_cp_id = $get_user->sm_cp_id;
        $ticket_arr = null;

        $ticket = null;
        /* echo "<pre>";
        print_r($querydata);
        exit; */

        if (!empty($querydata)) {
            $ticket_contact = (!empty($querydata['contact']) ? trim($querydata['contact']) : '');
            $ticket_ticketid = (!empty($querydata['ticketid']) ? trim($querydata['ticketid']) : '');

            $ticket_start_created_on_date = (!empty($querydata['start_created_on_date']) ? trim($querydata['start_created_on_date']) : '');
            $ticket_end_created_on_date = (!empty($querydata['end_created_on_date']) ? trim($querydata['end_created_on_date']) : '');

            $ticket_status = (!empty($querydata['status']) ? trim($querydata['status']) : '');
        }

        $order_name = (isset($querydata['sort_name']) ? trim($querydata['sort_name']) : 'ticket.id');
        $order_type = (isset($querydata['sort_type']) ? trim($querydata['sort_type']) : 'DESC');


        $page_count = (isset($querydata['page_count']) ? trim($querydata['page_count']) : 25);
        $page = (isset($querydata['page']) ? trim($querydata['page']) : 1);
        if ($page == 1) {
            $offset = 0;
        } else {
            $offset = (($page - 1) * $page_count);
        }

        $where_sql = '1 ';

        if (!empty($ticket_ticketid)) {
            $where_sql .= ' AND ticket.ticketid="' . $ticket_ticketid . '"';
        }


        if (!empty($ticket_start_created_on_date)) {
            $where_sql .= ' AND DATE(ticket.created_on)>="' . $ticket_start_created_on_date . '"';
        }
        if (!empty($ticket_end_created_on_date)) {
            $where_sql .= ' AND DATE(ticket.created_on)<="' . $ticket_end_created_on_date . '"';
        }

        if (!empty($ticket_contact)) {
            $where_sql .= ' AND ticket.contact="' . $ticket_contact . '"';
        }

        if (!empty($ticket_status)) {
            $where_sql .= ' AND ticket.status="' . $ticket_status . '"';
        }


        $ticket_query = (new \yii\db\Query())
            ->select('contact,issue_type,message,created_on,modified_on,status,ticketid,feedback')
            ->from('ticket')
            ->where($where_sql)
            ->orderBy($order_name . ' ' . $order_type);
        $ticket_count = $ticket_query->count();
        /* echo $proj_count;
                exit; */
        $ticket_data = $ticket_query->limit($page_count)->offset($offset)->createCommand()
            /* ->getRawsql();
                echo $proj_data;
                exit; */
            ->queryAll();


        if (!empty($ticket_data)) {
            foreach ($ticket_data as $ticket_data_key => $ticket_data_val) {
                $ticket[$ticket_data_key] = $ticket_data_val;
                $ticket[$ticket_data_key]['status'] = $this->ticket_status[$ticket_data_val['status']];
                if (!empty($ticket_data_val['issue_type'])) {
                    $ticket[$ticket_data_key]['issue_type'] = $this->ticket_issue_type[$ticket_data_val['issue_type']];
                } else {
                    $ticket[$ticket_data_key]['issue_type'] = null;
                }
            }
        }


        //pagination code start
        $pgdata['page'] = $page;
        $pgdata['content_count'] = $ticket_count;
        $pgdata['page_count'] = $page_count;
        $pagination = $this->getpagination($pgdata);
        //pagination code end


        $ticket_arr['ticket'] = $ticket;
        $ticket_arr['pagination'] = $pagination;

        return $ticket_arr;
    }

    public function getticketapi($ticket_id, $ticket_data)
    {
        $ticket_arr = null;
        $ticket = null;
        if (empty($ticket_data)) {
            $ticket_data = Ticket::find()->select('id,contact,issue_type,message,created_on,modified_on,status,ticketid,feedback')->where(['id' => $ticket_id])->one();
            $ticket = $ticket_data->toArray();
        } else {
            $ticket = $ticket_data->toArray();
        }

        if (!empty($ticket_data)) {

            if (!empty($ticket['issue_type'])) {
                $ticket['issue_type'] = $this->ticket_issue_type[$ticket['issue_type']];
            }

            if (!empty($ticket['status'])) {
                $ticket['status'] = $this->ticket_status[$ticket['status']];
            }
            unset($ticket['created_by'], $ticket['ip_address'], $ticket['modified_by']);


            /* if (!empty($tag['modified_by'])) {
                $modified_by_model = SmCp::find()->where(['sm_cp_id' => $tag['modified_by']])->one();
                $tag['modified_by'] = $modified_by_model->name;
            } */

            $ticket_arr['ticket'] = $ticket;
        }
        return $ticket_arr;
    }


    public function getleadstatsapi($sm_cp_id)
    {
        $get_members = $this->getaccessmembers($sm_cp_id);
        $get_members[] = $sm_cp_id;
        $statData_arr = null;


        $get_members_txt = implode(",", $get_members);
        $where_sql = " leadmaster.created_by IN(" . $get_members_txt . ") ";
        $get_date = date('Y-m-d');


        $statData = Yii::$app->db->createCommand("SELECT SUM(IF((leadmaster.tagging=0), 1, 0)) AS total, SUM(IF((leadmaster.followup_date>='" . $get_date . "' AND leadmaster.tagging=0), 1, 0)) AS followup,SUM(IF((leadmaster.followup_date='" . $get_date . "' AND leadmaster.tagging=0), 1, 0)) AS today,SUM(IF((leadmaster.followup_date<'" . $get_date . "' AND leadmaster.tagging=0), 1, 0)) AS missed,SUM(IF((DATE(leadmaster.created_on)='" . $get_date . "' AND leadmaster.tagging=0), 1, 0)) AS fresh FROM leadmaster where " . $where_sql)->queryAll();

        if (!empty($statData)) {
            $statData_arr['stats'] = $statData[0];
        }
        return $statData_arr;
    }

    public function getleadlistapi($get_user, $querydata, $list_type = '')
    {
        $sm_cp_id = $get_user->sm_cp_id;
        $lead_arr = null;
        if (empty($list_type)) {
            $pagination = null;
        }
        $lead = null;
        /* echo "<pre>";
        print_r($querydata);
        exit; */

        $get_members = $this->getaccessmembers($sm_cp_id);
        $get_members[] = $sm_cp_id;


        $get_members_txt = implode(",", $get_members);
        if (!empty($querydata)) {
            $lead_name = (!empty($querydata['name']) ? trim($querydata['name']) : '');
            $lead_lead_id = (!empty($querydata['lead_id']) ? trim($querydata['lead_id']) : '');
            $lead_start_followup_date = (!empty($querydata['start_followup_date']) ? trim($querydata['start_followup_date']) : '');
            $lead_end_followup_date = (!empty($querydata['end_followup_date']) ? trim($querydata['end_followup_date']) : '');
            $lead_start_created_on_date = (!empty($querydata['start_created_on_date']) ? trim($querydata['start_created_on_date']) : '');
            $lead_end_created_on_date = (!empty($querydata['end_created_on_date']) ? trim($querydata['end_created_on_date']) : '');
            $lead_customer_type = (!empty($querydata['customer_type']) ? trim($querydata['customer_type']) : '');

            $lead_contact = (!empty($querydata['contact']) ? trim($querydata['contact']) : '');
            $lead_created_by = (!empty($querydata['created_by']) ? trim($querydata['created_by']) : '');

            $lead_status = (!empty($querydata['status']) ? trim($querydata['status']) : '');
            $lead_type = (!empty($querydata['type']) ? trim($querydata['type']) : '');
        }

        $order_name = (isset($querydata['sort_name']) ? trim($querydata['sort_name']) : 'leadmaster.id');
        $order_type = (isset($querydata['sort_type']) ? trim($querydata['sort_type']) : 'DESC');


        $page_count = (isset($querydata['page_count']) ? trim($querydata['page_count']) : 25);
        $page = (isset($querydata['page']) ? trim($querydata['page']) : 1);
        if ($page == 1) {
            $offset = 0;
        } else {
            $offset = (($page - 1) * $page_count);
        }

        $where_sql = 'leadmaster.created_by IN(' . $get_members_txt . ') ';
        if (!empty($lead_name)) {
            $where_sql .= ' AND leadmaster.name like "%' . $lead_name . '%"';
        }
        if (!empty($lead_lead_id)) {
            $where_sql .= ' AND leadmaster.lead_id="' . $lead_lead_id . '"';
        }
        if (!empty($lead_start_followup_date)) {
            $where_sql .= ' AND leadmaster.followup_date>="' . $lead_start_followup_date . '"';
        }
        if (!empty($lead_end_followup_date)) {
            $where_sql .= ' AND leadmaster.followup_date<="' . $lead_end_followup_date . '"';
        }

        if (!empty($lead_start_created_on_date)) {
            $where_sql .= ' AND DATE(leadmaster.created_on)>="' . $lead_start_created_on_date . '"';
        }
        if (!empty($lead_end_created_on_date)) {
            $where_sql .= ' AND DATE(leadmaster.created_on)<="' . $lead_end_created_on_date . '"';
        }
        if (!empty($lead_customer_type)) {
            $where_sql .= ' AND leadmaster.customer_type="' . $lead_customer_type . '"';
        }
        if (!empty($lead_contact)) {
            $where_sql .= ' AND leadmaster.contact="' . $lead_contact . '"';
        }
        if (!empty($lead_created_by)) {
            $where_sql .= ' AND leadmaster.created_by="' . $lead_created_by . '"';
        }
        if (isset($querydata['tagged']) && $querydata['tagged'] == 'true') {
            $where_sql .= ' AND leadmaster.tagging="1"';
        } else if (isset($querydata['tagged']) && $querydata['tagged'] == 'false') {
            $where_sql .= ' AND leadmaster.tagging="0"';
        }

        if (!empty($lead_status)) {
            $where_sql .= ' AND leadmaster.status="' . $lead_status . '"';
        }

        if (!empty($lead_type)) {
            if ($lead_type == 1) {
                //followup - leads with followup today and later
                $where_sql .= ' AND leadmaster.followup_date>="' . date('Y-m-d') . '" AND leadmaster.tagging=0 ';
            } else if ($lead_type == 2) {
                //today - leads with followup as today
                $where_sql .= ' AND leadmaster.followup_date="' . date('Y-m-d') . '" AND leadmaster.tagging=0 ';
            } else if ($lead_type == 3) {
                //missed - leads with followup less than today
                $where_sql .= ' AND leadmaster.followup_date<"' . date('Y-m-d') . '"  AND leadmaster.tagging=0 ';
            } else if ($lead_type == 4) {
                //fresh - leads with created_on as today
                $where_sql .= ' AND DATE(leadmaster.created_on)="' . date('Y-m-d') . '"  AND leadmaster.tagging=0 ';
            }
        }


        if (empty($list_type)) {
            $lead_query = (new \yii\db\Query())
                ->select('leadmaster.id,leadmaster.name,leadmaster.lead_id,leadmaster.email,leadmaster.flat_type_id,leadmaster.contact,leadmaster.budget,leadmaster.customer_type,leadmaster.followup_date,leadmaster.status,leadmaster.tagging,tag.tag_count,leadmaster.created_on,leadmaster.modified_on,creator.name as createdby,modifier.name as modifiedby')
                ->from('leadmaster')
                ->leftJoin("(select count(t.id) as tag_count,t.lead_id from taggingmaster t group by t.lead_id ) tag", 'tag.lead_id=leadmaster.id')
                ->leftJoin("sm_cp as creator", 'leadmaster.created_by=creator.sm_cp_id')
                ->leftJoin("sm_cp as modifier", 'leadmaster.modified_by=modifier.sm_cp_id')
                ->where($where_sql)
                ->orderBy($order_name . ' ' . $order_type);
            $lead_count = $lead_query->count();
            /* echo $proj_count;
                exit; */
            $lead_data = $lead_query->limit($page_count)->offset($offset)->createCommand()
                /* ->getRawsql();
                echo $proj_data;
                exit; */
                ->queryAll();
        } else {
            $lead_query = (new \yii\db\Query())
                ->select('leadmaster.id,leadmaster.contact,leadmaster.name,leadmaster.lead_id')
                ->from('leadmaster')
                ->where($where_sql)
                ->orderBy($order_name . ' ' . $order_type);

            $lead_data = $lead_query->createCommand()
                ->queryAll();
        }


        if (!empty($lead_data)) {
            foreach ($lead_data as $lead_data_key => $lead_data_val) {
                $lead[$lead_data_key] = $lead_data_val;

                if (empty($list_type)) {

                    $lead[$lead_data_key]['flat_type_text'] = null;
                    if (!empty($lead_data_val['flat_type_id'])) {
                        $lead[$lead_data_key]['flat_type_text'] = $this->flat_type_id[$lead_data_val['flat_type_id']];
                    }

                    $lead[$lead_data_key]['budgetinwords'] = null;
                    if (!empty($lead_data_val['budget'])) {
                        $lead[$lead_data_key]['budget'] = (string) $lead_data_val['budget'];
                        $lead[$lead_data_key]['budgetinwords'] = $this->AmountInWords($lead_data_val['budget']);
                    }

                    if (!empty($lead_data_val['customer_type'])) {
                        $lead[$lead_data_key]['customer_type'] = $this->customer_type[$lead_data_val['customer_type']];
                    }

                    $lead[$lead_data_key]['status'] = $this->lead_status[$lead_data_val['status']];
                    $lead[$lead_data_key]['tagging'] = $this->tagging_done[$lead_data_val['tagging']];
                    if (!empty($lead_data_val['tag_count'])) {
                        $lead[$lead_data_key]['tag_count'] = $lead_data_val['tag_count'];
                    } else {
                        //unset($lead[$lead_data_key]['tag_count']);
                    }

                    //get lead feedback
                    $feedData = (new \yii\db\Query())
                        ->select('fd.id,fd.feedback,fd.created_on,sm.sm_cp_id,sm.name,sm.role')
                        ->from('lead_feedback fd')
                        ->leftJoin("sm_cp as sm", 'fd.created_by = sm.sm_cp_id')
                        ->where('fd.lead_id="' . $lead_data_val['id'] . '"')
                        ->orderBy('fd.id DESC')
                        ->limit(5)
                        ->createCommand()
                        ->queryAll();
                    $feedback = null;
                    if (!empty($feedData)) {
                        $f_count = 0;
                        foreach ($feedData as $feedData_val) {
                            //$feedback[$f_count]['id'] = $feedData_val['id'];
                            $feedback[$f_count]['feedback'] = $feedData_val['feedback'];
                            $feedback[$f_count]['created_on'] = $feedData_val['created_on'];
                            //$feedback[$f_count]['sm_cp_id'] = $feedData_val['sm_cp_id'];
                            $feedback[$f_count]['created_by'] = $feedData_val['name'];
                            //$feedback[$f_count]['role'] = $this->role[$feedData_val['role']];
                            $f_count++;
                        }
                    }
                    $lead[$lead_data_key]['feedback'] = $feedback;

                    $lead[$lead_data_key]['created_by'] = $lead_data_val['createdby'];
                    $lead[$lead_data_key]['modified_by'] = $lead_data_val['modifiedby'];
                    unset($lead[$lead_data_key]['createdby'], $lead[$lead_data_key]['modifiedby']);
                }
            }
        }

        if (empty($list_type)) {
            //pagination code start
            $pgdata['page'] = $page;
            $pgdata['content_count'] = $lead_count;
            $pgdata['page_count'] = $page_count;
            $pagination = $this->getpagination($pgdata);
            //pagination code end
        }

        $lead_arr['lead'] = $lead;
        if (empty($list_type)) {
            $lead_arr['pagination'] = $pagination;
        }

        return $lead_arr;
    }

    public function gettaglistapi($get_user, $querydata, $list_type = '')
    {
        $sm_cp_id = $get_user->sm_cp_id;
        $tag_arr = null;
        if (empty($list_type)) {
            $pagination = null;
        }
        $tag = null;
        /* echo "<pre>";
        print_r($querydata);
        exit; */

        $get_members = $this->getaccessmembers($sm_cp_id);
        $get_members[] = $sm_cp_id;


        $get_members_txt = implode(",", $get_members);
        if (!empty($querydata)) {
            $tag_project_id = (!empty($querydata['project_id']) ? implode(",", $querydata['project_id']) : '');
            $tag_name = (!empty($querydata['name']) ? trim($querydata['name']) : '');
            $tag_contact = (!empty($querydata['contact']) ? trim($querydata['contact']) : '');
            $tag_tagging_id = (!empty($querydata['tagging_id']) ? trim($querydata['tagging_id']) : '');
            $tag_start_appointment_date = (!empty($querydata['start_appointment_date']) ? trim($querydata['start_appointment_date']) : '');
            $tag_end_appointment_date = (!empty($querydata['end_appointment_date']) ? trim($querydata['end_appointment_date']) : '');
            $tag_start_created_on_date = (!empty($querydata['start_created_on_date']) ? trim($querydata['start_created_on_date']) : '');
            $tag_end_created_on_date = (!empty($querydata['end_created_on_date']) ? trim($querydata['end_created_on_date']) : '');
            $tag_customer_type = (!empty($querydata['customer_type']) ? trim($querydata['customer_type']) : '');

            $tag_status = (!empty($querydata['status']) ? trim($querydata['status']) : '');
        }

        $order_name = (isset($querydata['sort_name']) ? trim($querydata['sort_name']) : 'taggingmaster.id');
        $order_type = (isset($querydata['sort_type']) ? trim($querydata['sort_type']) : 'DESC');


        $page_count = (isset($querydata['page_count']) ? trim($querydata['page_count']) : 25);
        $page = (isset($querydata['page']) ? trim($querydata['page']) : 1);
        if ($page == 1) {
            $offset = 0;
        } else {
            $offset = (($page - 1) * $page_count);
        }

        $where_sql = 'taggingmaster.created_by IN(' . $get_members_txt . ') ';
        if (!empty($tag_name)) {
            $where_sql .= ' AND taggingmaster.tag_name like "%' . $tag_name . '%"';
        }
        if (!empty($tag_contact)) {
            $where_sql .= ' AND taggingmaster.tag_phone like "%' . $tag_contact . '%"';
        }
        if (!empty($tag_tagging_id)) {
            $where_sql .= ' AND taggingmaster.tagging_id="' . $tag_tagging_id . '"';
        }
        if (!empty($tag_start_appointment_date)) {
            $where_sql .= ' AND taggingmaster.appointment_date>="' . $tag_start_appointment_date . '"';
        }
        if (!empty($tag_end_appointment_date)) {
            $where_sql .= ' AND taggingmaster.appointment_date<="' . $tag_end_appointment_date . '"';
        }

        if (!empty($tag_start_created_on_date)) {
            $where_sql .= ' AND DATE(taggingmaster.created_on)>="' . $tag_start_created_on_date . '"';
        }
        if (!empty($tag_end_created_on_date)) {
            $where_sql .= ' AND DATE(taggingmaster.created_on)<="' . $tag_end_created_on_date . '"';
        }
        if (!empty($tag_customer_type)) {
            $where_sql .= ' AND taggingmaster.customer_type="' . $tag_customer_type . '"';
        }

        if (!empty($tag_project_id)) {
            $where_sql .= ' AND taggingmaster.project_id="' . $tag_project_id . '"';
        }

        if (!empty($tag_status)) {
            $where_sql .= ' AND taggingmaster.status IN(' . $tag_status . ')';
        }

        if (empty($list_type)) {
            $tag_query = (new \yii\db\Query())
                ->select('taggingmaster.id,taggingmaster.tag_name,taggingmaster.tag_phone,taggingmaster.tagging_id,taggingmaster.lead_id,taggingmaster.tag_email,taggingmaster.flat_type_id,taggingmaster.budget,taggingmaster.customer_type,taggingmaster.appointment_date,taggingmaster.appointment_time,taggingmaster.status,taggingmaster.created_on,taggingmaster.created_by,taggingmaster.modified_on,taggingmaster.modified_by,taggingmaster.visit_notvisit_clash_date,taggingmaster.lead_convert_date,taggingmaster.lead_dead_date,taggingmaster.lead_registered_date,projects.name as project_id,sm_cp.name as sourcing_id,leadmaster.lead_id,creator.name as createdby,modifier.name as modifiedby')
                ->from('taggingmaster')
                ->leftJoin("projects", 'taggingmaster.project_id=projects.id')
                ->leftJoin("sm_cp", 'taggingmaster.sm_cp_id=sm_cp.sm_cp_id')
                ->leftJoin("leadmaster", 'taggingmaster.lead_id=leadmaster.id')
                ->leftJoin("sm_cp as creator", 'taggingmaster.created_by=creator.sm_cp_id')
                ->leftJoin("sm_cp as modifier", 'taggingmaster.modified_by=modifier.sm_cp_id')
                ->where($where_sql)
                ->orderBy($order_name . ' ' . $order_type);
            $tag_count = $tag_query->count();
            /* echo $proj_count;
                exit; */
            $tag_data = $tag_query->limit($page_count)->offset($offset)->createCommand()
                /* ->getRawsql();
                echo $proj_data;
                exit; */
                ->queryAll();
        } else {
            $tag_query = (new \yii\db\Query())
                ->select('taggingmaster.id,taggingmaster.tag_phone,taggingmaster.tag_name,taggingmaster.tagging_id')
                ->from('taggingmaster')
                ->where($where_sql)
                ->orderBy($order_name . ' ' . $order_type);

            $tag_data = $tag_query->createCommand()
                ->queryAll();
        }


        if (!empty($tag_data)) {
            foreach ($tag_data as $tag_data_key => $tag_data_val) {
                $tag[$tag_data_key] = $tag_data_val;

                if (empty($list_type)) {
                    $tag[$tag_data_key]['name'] = $tag_data_val['tag_name'];
                    $tag[$tag_data_key]['contact'] = $tag_data_val['tag_phone'];
                    $tag[$tag_data_key]['email'] = $tag_data_val['tag_email'];
                    $tag[$tag_data_key]['flat_type_text'] = null;
                    if (!empty($tag_data_val['flat_type_id'])) {
                        $tag[$tag_data_key]['flat_type_text'] = $this->flat_type_id[$tag_data_val['flat_type_id']];
                    }

                    $tag[$tag_data_key]['budgetinwords'] = null;
                    if (!empty($tag_data_val['budget'])) {
                        $tag[$tag_data_key]['budget'] = (string) $tag_data_val['budget'];
                        $tag[$tag_data_key]['budgetinwords'] = $this->AmountInWords($tag_data_val['budget']);
                    }

                    if (!empty($tag_data_val['customer_type'])) {
                        $tag[$tag_data_key]['customer_type'] = $this->customer_type[$tag_data_val['customer_type']];
                    }

                    $tag[$tag_data_key]['status'] = $this->tag_status[$tag_data_val['status']];
                    if ($tag_data_val['status'] == 1) {
                        $tag[$tag_data_key]['visited_on'] = $tag_data_val['visit_notvisit_clash_date'];
                    } else if ($tag_data_val['status'] == 3) {
                        $tag[$tag_data_key]['clashed_on'] = $tag_data_val['visit_notvisit_clash_date'];
                    } else if ($tag_data_val['status'] == 7) {
                        $tag[$tag_data_key]['rejected_on'] = $tag_data_val['visit_notvisit_clash_date'];
                    } else if ($tag_data_val['status'] == 4) {
                        $tag[$tag_data_key]['booked_on'] = $tag_data_val['lead_convert_date'];
                    } else if ($tag_data_val['status'] == 5) {
                        $tag[$tag_data_key]['lead_dead_on'] = $tag_data_val['lead_dead_date'];
                    } else if ($tag_data_val['status'] == 6) {
                        $tag[$tag_data_key]['registered_on'] = $tag_data_val['lead_registered_date'];
                    }

                    $tag[$tag_data_key]['created_by'] = $tag_data_val['createdby'];
                    $tag[$tag_data_key]['modified_by'] = $tag_data_val['modifiedby'];


                    //get lead feedback
                    $feedData = (new \yii\db\Query())
                        ->select('fd.id,fd.feedback,fd.created_on,sm.sm_cp_id,sm.name,sm.role')
                        ->from('tagging_feedback fd')
                        ->leftJoin("sm_cp as sm", 'fd.created_by = sm.sm_cp_id')
                        ->where('fd.tagging_id="' . $tag_data_val['id'] . '"')
                        ->orderBy('fd.id DESC')
                        ->limit(5)  //show last 5 feedback
                        ->createCommand()
                        ->queryAll();
                    $feedback = null;
                    if (!empty($feedData)) {
                        $f_count = 0;
                        foreach ($feedData as $feedData_val) {
                            //$feedback[$f_count]['id'] = $feedData_val['id'];
                            $feedback[$f_count]['feedback'] = $feedData_val['feedback'];
                            $feedback[$f_count]['created_on'] = $feedData_val['created_on'];
                            //$feedback[$f_count]['sm_cp_id'] = $feedData_val['sm_cp_id'];
                            $feedback[$f_count]['created_by'] = $feedData_val['name'];
                            //$feedback[$f_count]['role'] = $this->role[$feedData_val['role']];
                            $f_count++;
                        }
                    }
                    $tag[$tag_data_key]['feedback'] = $feedback;
                    unset($tag[$tag_data_key]['tag_name'], $tag[$tag_data_key]['tag_phone'], $tag[$tag_data_key]['tag_email'], $tag[$tag_data_key]['visit_notvisit_clash_date'], $tag[$tag_data_key]['lead_convert_date'], $tag[$tag_data_key]['lead_dead_date'], $tag[$tag_data_key]['lead_registered_date'], $tag[$tag_data_key]['created_by'], $tag[$tag_data_key]['modified_by']);
                } else {
                    $tag[$tag_data_key]['name'] = $tag_data_val['tag_name'];
                    $tag[$tag_data_key]['contact'] = $tag_data_val['tag_phone'];
                    unset($tag[$tag_data_key]['tag_name'], $tag[$tag_data_key]['tag_phone']);
                }
            }
        }

        if (empty($list_type)) {
            //pagination code start
            $pgdata['page'] = $page;
            $pgdata['content_count'] = $tag_count;
            $pgdata['page_count'] = $page_count;
            $pagination = $this->getpagination($pgdata);
            //pagination code end
        }

        $tag_arr['tag'] = $tag;
        if (empty($list_type)) {
            $tag_arr['pagination'] = $pagination;
        }

        return $tag_arr;
    }

    public function gettagstatsapi($sm_cp_id)
    {
        $get_members = $this->getaccessmembers($sm_cp_id);
        $get_members[] = $sm_cp_id;
        $statData_arr = null;


        $get_members_txt = implode(",", $get_members);
        $where_sql = " taggingmaster.created_by IN(" . $get_members_txt . ") ";
        $get_date = date('Y-m-d');


        $statData = Yii::$app->db->createCommand("SELECT SUM(IF((taggingmaster.id!=''), 1, 0)) AS total, SUM(IF((taggingmaster.status=0), 1, 0)) AS appointment,SUM(IF((taggingmaster.status=1), 1, 0)) AS visited,SUM(IF((taggingmaster.status=3), 1, 0)) AS clashed,SUM(IF((taggingmaster.status=2 OR taggingmaster.status=5 OR taggingmaster.status=7), 1, 0)) AS missed,SUM(IF((taggingmaster.status=4), 1, 0)) AS booking_done,SUM(IF((taggingmaster.status=6), 1, 0)) AS registered FROM taggingmaster where " . $where_sql)->queryAll();

        if (!empty($statData)) {
            $statData_arr['stats'] = $statData[0];
        }
        return $statData_arr;
    }

    public function gettagstatsmonthlyapi($get_user, $querydata)
    {
        $sm_cp_id = $get_user->sm_cp_id;
        $tag_arr = null;

        $tag = null;
        /* echo "<pre>";
        print_r($querydata);
        exit; */

        $get_members = $this->getaccessmembers($sm_cp_id);
        $get_members[] = $sm_cp_id;


        $get_members_txt = implode(",", $get_members);
        $tag_year = date('Y');
        if (!empty($querydata)) {

            $tag_year = (!empty($querydata['year']) ? trim($querydata['year']) : date('Y'));
        }


        $where_sql = 'taggingmaster.created_by IN(' . $get_members_txt . ') ';
        if (!empty($tag_year)) {
            $where_sql .= ' AND YEAR(taggingmaster.created_on)="' . $tag_year . '"';
        }


        $tag_query = (new \yii\db\Query())
            ->select('YEAR(created_on) AS y, MONTH(created_on) AS m, COUNT(id) as val')
            ->from('taggingmaster')
            ->where($where_sql)
            ->groupBy('y,m')
            ->orderBy('y DESC');
        $tag_data = $tag_query->createCommand()->queryAll();

        if (!empty($tag_data)) {
            foreach ($tag_data as $tag_data_key => $tag_data_val) {
                $tag[$tag_data_key]['name'] = $this->month[$tag_data_val['m']];
                $tag[$tag_data_key]['count'] = $tag_data_val['val'];
            }
        }

        $tag_arr['stats']['monthwise'] = $tag;

        return $tag_arr;
    }

    public function getmarketlistapi($get_user, $querydata)
    {
        $cp_id = $get_user->channelpartner_id;
        $sm_cp_id = $get_user->sm_cp_id;
        $market_arr = null;

        $pagination = null;
        $market = null;
        $get_sm = $this->getsmofcp($cp_id);
        $get_proj = array();
        if (!empty($get_sm)) {
            $get_proj = $this->getprojofmultism($get_sm);
        }
        /* if (!empty($get_proj)) {

            $get_proj_txt = implode(",", $get_proj);
        } */
        $market_start_campaign_date = '';
        $market_end_campaign_date = '';
        if (!empty($querydata)) {

            $market_name = (!empty($querydata['name']) ? trim($querydata['name']) : '');
            $market_project_id = (!empty($querydata['project_id']) ? implode(",", $querydata['project_id']) : []);
            $market_start_campaign_date = (!empty($querydata['start_campaign_date']) ? trim($querydata['start_campaign_date']) : '');
            $market_end_campaign_date = (!empty($querydata['end_campaign_date']) ? trim($querydata['end_campaign_date']) : '');
            $market_start_created_on_date = (!empty($querydata['start_created_on_date']) ? trim($querydata['start_created_on_date']) : '');
            $market_end_created_on_date = (!empty($querydata['end_created_on_date']) ? trim($querydata['end_created_on_date']) : '');
        }

        $order_name = (isset($querydata['sort_name']) ? 'marketing_schedule.' . trim($querydata['sort_name']) : 'marketing_schedule.id');
        $order_type = (isset($querydata['sort_type']) ? trim($querydata['sort_type']) : 'DESC');


        $page_count = (isset($querydata['page_count']) ? trim($querydata['page_count']) : 25);
        $page = (isset($querydata['page']) ? trim($querydata['page']) : 1);
        if ($page == 1) {
            $offset = 0;
        } else {
            $offset = (($page - 1) * $page_count);
        }

        /* $where_sql = 'marketing_schedule.project_id IN(' . $get_proj_txt . ') AND marketing_schedule.status=1 AND marketing_campaign.status=1'; */
        $where_sql = 'marketing_schedule.status=1 AND marketing_campaign.status=1';
        if (!empty($market_name)) {
            $where_sql .= ' AND marketing_schedule.name like "%' . $market_name . '%"';
        }
        if (!empty($market_project_id)) {
            $where_sql .= ' AND marketing_schedule.project_id IN(' . $market_project_id . ')';
        }

        if (!empty($market_start_campaign_date)) {
            $where_sql .= ' AND marketing_schedule.start_date>="' . $market_start_campaign_date . '"';
        }
        if (!empty($market_end_campaign_date)) {
            $where_sql .= ' AND marketing_schedule.start_date<="' . $market_end_campaign_date . '"';
        }

        if (!empty($market_start_created_on_date)) {
            $where_sql .= ' AND DATE(marketing_schedule.created_on)>="' . $market_start_created_on_date . '"';
        }
        if (!empty($market_end_created_on_date)) {
            $where_sql .= ' AND DATE(marketing_schedule.created_on)<="' . $market_end_created_on_date . '"';
        }


        if (empty($market_start_campaign_date) && empty($market_end_campaign_date)) {
            $where_sql .= ' AND DATE("' . date('Y-m-d') . '") BETWEEN marketing_schedule.start_date AND marketing_schedule.end_date';
        }


        $market_query = (new \yii\db\Query())
            ->select(
                'marketing_schedule.id,marketing_schedule.campaign_id,projects.name as projectname,marketing_schedule.project_id,marketing_schedule.start_date,marketing_schedule.end_date,marketing_schedule.created_on,marketing_schedule.brief as schedule_brief'
            )
            ->from('marketing_schedule')
            ->leftJoin("projects", 'marketing_schedule.project_id=projects.id')
            ->leftJoin("marketing_campaign", 'marketing_schedule.campaign_id=marketing_campaign.id')
            //->leftJoin("sm_cp as creator", 'marketing.created_by=creator.sm_cp_id')
            //->leftJoin("sm_cp as modifier", 'marketing.modified_by=modifier.sm_cp_id')
            ->where($where_sql)
            ->orderBy($order_name . ' ' . $order_type);
        $market_count = $market_query->count();
        /* echo $proj_count;
                exit; */
        $market_data = $market_query->limit($page_count)->offset($offset)->createCommand()
            /* ->getRawsql();
                echo $market_data;
                exit; */
            ->queryAll();


        if (!empty($market_data)) {
            foreach ($market_data as $market_data_key => $market_data_val) {
                $market[$market_data_key] = $market_data_val;
                if (!empty($market_data_val['schedule_brief'])) {
                    $databrief = $market_data_val['schedule_brief'];
                }

                $market[$market_data_key]['project']['id'] = $market_data_val['project_id'];
                $market[$market_data_key]['project']['name'] = $market_data_val['projectname'];
                $sm_connected = 'false';
                if (!empty($get_proj)) {
                    if (in_array($market_data_val['project_id'], $get_proj)) {
                        $sm_connected = 'true';
                    }
                }
                $market[$market_data_key]['project']['sm_connected'] = $sm_connected;
                //get campaign
                $get_campaign_arr = null;
                $get_campaign_data = MarketingCampaign::find()->select('campaign_id,brief, title,website')->where(['id' => $market_data_val['campaign_id']])->andWhere(['in', 'status', [1]])->one();
                if (!empty($get_campaign_data)) {
                    $get_campaign_arr = $get_campaign_data->toArray();
                    $market[$market_data_key]['campaign'] = $get_campaign_arr;
                    if (!empty($databrief)) {
                        $market[$market_data_key]['campaign']['brief'] = $databrief;
                    }
                }
                $get_campaign_content_arr = null;
                $get_campaign_content_data = MarketingCampaignContent::find()->select('content')->where(['campaign_id' => $market_data_val['campaign_id']])->asArray()->all();
                if (!empty($get_campaign_content_data)) {
                    $c_count = 0;
                    foreach ($get_campaign_content_data as $get_campaign_content_data_val) {
                        $get_campaign_content_arr[$c_count]['link'] = \Yii::$app->params['web_path'] . 'files/marketing/' . $get_campaign_content_data_val['content'];
                        $c_count++;
                    }
                    $market[$market_data_key]['campaign']['content'] = $get_campaign_content_arr;
                }

                //get sourcing
                $sourcingData = (new \yii\db\Query())
                    ->select('sm.sm_cp_id,sm.name,sm.role,sm.contact')
                    ->from('sm_projects sp')
                    ->leftJoin("sm_cp as sm", 'sp.sm_cp_id = sm.sm_cp_id')
                    ->where('FIND_IN_SET("' . (int) $market_data_val['project_id'] . '",projects_id) AND sp.status=1')->groupBy('sp.sm_cp_id')->createCommand()
                    ->queryAll();
                $sourcing = null;
                if (!empty($sourcingData)) {
                    $s_count = 0;
                    foreach ($sourcingData as $sourcingData_val) {
                        $sourcing[$s_count]['sm_cp_id'] = $sourcingData_val['sm_cp_id'];
                        $sourcing[$s_count]['name'] = $sourcingData_val['name'];
                        $sourcing[$s_count]['role'] = $this->role[$sourcingData_val['role']];
                        $sourcing[$s_count]['contact'] = $sourcingData_val['contact'];
                        $s_count++;
                    }
                    $market[$market_data_key]['project']['sourcing'] = $sourcing;
                }

                unset($market[$market_data_key]['projectname'], $market[$market_data_key]['campaign_id'], $market[$market_data_key]['project_id'], $market[$market_data_key]['schedule_brief']);
            }
        }


        //pagination code start
        $pgdata['page'] = $page;
        $pgdata['content_count'] = $market_count;
        $pgdata['page_count'] = $page_count;
        $pagination = $this->getpagination($pgdata);
        //pagination code end


        /* echo "<pre>";
                print_r($project);
                exit; */


        $market_arr['marketing'] = $market;
        $market_arr['pagination'] = $pagination;


        return $market_arr;
    }

    function AmountInWords($get_amount)
    {
        $show_aount = '';
        if ($get_amount > 1000000 && $get_amount < 10000000) {
            $get_digit = substr($get_amount, 0, 2);
            $get_inner_digit = intval(substr($get_amount, 2, 2));
            if ($get_inner_digit > 0) {
                $show_aount = $get_digit . "." . $get_inner_digit . " Lacs";
            } else {
                $show_aount = $get_digit . " Lacs";
            }
        } else if ($get_amount >= 10000000 && $get_amount < 100000000) {
            $get_digit = substr($get_amount, 0, 1);
            $get_inner_digit = intval(substr($get_amount, 1, 2));
            if ($get_inner_digit > 0) {
                $show_aount = $get_digit . "." . $get_inner_digit . " Cr";
            } else {
                $show_aount = $get_digit . " Cr";
            }
        } else if ($get_amount >= 100000000 && $get_amount < 1000000000) {
            $get_digit = substr($get_amount, 0, 2);
            $get_inner_digit = intval(substr($get_amount, 2, 2));
            if ($get_inner_digit > 0) {
                $show_aount = $get_digit . "." . $get_inner_digit . " Cr";
            } else {
                $show_aount = $get_digit . " Cr";
            }
        }
        return $show_aount;
    }

    // Create a function for converting the amount in words
    function AmountInWordsfull($amount)
    {
        $num = $amount;
        $amt_hundred = null;
        $count_length = strlen($num);
        $x = 0;
        $string = array();
        $change_words = array(
            0 => '', 1 => 'One', 2 => 'Two',
            3 => 'Three', 4 => 'Four', 5 => 'Five', 6 => 'Six',
            7 => 'Seven', 8 => 'Eight', 9 => 'Nine',
            10 => 'Ten', 11 => 'Eleven', 12 => 'Twelve',
            13 => 'Thirteen', 14 => 'Fourteen', 15 => 'Fifteen',
            16 => 'Sixteen', 17 => 'Seventeen', 18 => 'Eighteen',
            19 => 'Nineteen', 20 => 'Twenty', 30 => 'Thirty',
            40 => 'Forty', 50 => 'Fifty', 60 => 'Sixty',
            70 => 'Seventy', 80 => 'Eighty', 90 => 'Ninety'
        );
        $here_digits = array('', 'Hundred', 'Thousand', 'Lakh', 'Crore');
        while ($x < $count_length) {
            $get_divider = ($x == 2) ? 10 : 100;
            $amount = floor($num % $get_divider);
            $num = floor($num / $get_divider);
            $x += $get_divider == 10 ? 1 : 2;
            if ($amount) {
                $add_plural = (($counter = count($string)) && $amount > 9) ? 's' : null;
                $amt_hundred = ($counter == 1 && $string[0]) ? ' and ' : null;
                $string[] = ($amount < 21) ? $change_words[$amount] . ' ' . $here_digits[$counter] . $add_plural . ' ' . $amt_hundred : $change_words[floor($amount / 10) * 10] . ' ' . $change_words[$amount % 10] . ' ' . $here_digits[$counter] . $add_plural . ' ' . $amt_hundred;
            } else $string[] = null;
        }
        $implode_to_Rupees = implode('', array_reverse($string));
        return ($implode_to_Rupees ? trim($implode_to_Rupees) : '');
    }
}