<?php

/**
 * Copyright 2013 Catabatic Automation Technology Pvt Ltd.
 * All rights reserved
 *
 * @description: Travel_Model_TblLead.php, Leads Model. 2016/06/16 16:44
 * @author: Shakti Rana <shakti@catpl.co.in>
 */
//include_once 'BaseModel.php';

class Travel_Model_TblLead {

    protected $db;
    public $searchArr = NULL;
    public $baseUrl;

    public function __construct() {
        $request = Zend_Controller_Front::getInstance()->getRequest();
        $this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
        $this->db = Zend_Db_Table::getDefaultAdapter();
        $this->db->setFetchMode(Zend_Db::FETCH_ASSOC);
    }

    public function __destruct() {
        $this->db->closeConnection();
    }

    /**
     * getLeadList.
     * 
     * @param string $sql
     * @return array
     */
    public function updateTable($table, $editData, $where) {
        $dbtable = new Zend_Db_Table("$table");
        return $dbtable->update($editData, $where);
        // echo "<pre>"; print_r($dbtable); die;
    }

    public function getDetaByAgencysysid($table, $AgencySysId) {
        $select = $this->db->select()
                ->from("$table")
                ->where("AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function insertTable($table, $data) {
        $dbtable = new Zend_Db_Table("$table");
        return $dbtable->insert($data);
    }

    public function getLeadListNew($isExport = null) {
        $userRole = Travel_Model_AdminAuth::getIdentity()->UserRole;
        $UserSysId = Travel_Model_AdminAuth::getIdentity()->UserSysId;
        //echo "<pre>"; print_r($_SESSION); exit;
        $AgencyLogginData = array(
            0 => array("loggedId" => 'LDayOne', "Proposal" => 'PDayOne'),
            1 => array("loggedId" => 'LDayTwo', "Proposal" => 'PDayTwo'),
            2 => array("loggedId" => 'LDayThree', "Proposal" => 'PDayThree'),
            3 => array("loggedId" => 'LDayFour', "Proposal" => 'PDayFour'),
            4 => array("loggedId" => 'LDayFive', "Proposal" => 'PDayFive'),
            5 => array("loggedId" => 'LDaySix', "Proposal" => 'PDaySix'),
            6 => array("loggedId" => 'LDaySeven', "Proposal" => 'PDaySeven')
        );
        $select = $this->db->select()->from(array("t1" => "TB_Agency"), array("SecurityKey", "AgencySysId", "Title", "FirmType", "PrimaryContactName", "CreateDate", "IsActiveSubscription", "PrimaryEmail as EmailId", "ContactInfo as PrimaryContactNo", "IsActive", "IsApproved", "IsVarified", "IsEmailSetting", "IsPaymentGateway", "IsGTXPaymentGateway", "IsB2CSite", "IsB2bSite", "IsB2CUrl", "TrxCurrency", "AgencySubsAsignId", "IsSiteType", "Ischeckwallet", "PrimaryUserSysId"))
                ->joinLeft(array('t2' => "TB_Agency_Wallet"), "t1.AgencySysId = t2.AgencySysId", array("BalanceAmount"))
                ->joinLeft(array('t3' => "TB_Agency_Marketing_Wallet"), "t1.AgencySysId = t3.AgencySysId", array("TotalEmail"))
                ->joinLeft(array('t4' => "TB_Agency_RelationshipMgr"), "t1.AgencySysId = t4.AgencySysId", array("RelationalManager"))
                ->joinLeft(array('t5' => "TB_Admin_User"), "t5.UserSysId = t4.UserSysId", array("UserName"))
                ->joinLeft(array('t6' => "TB_Master_Currency"), "t6.CurrencyType = t2.CurrencyType", array("Symbol"))
                ->joinLeft(array('t7' => "TB_Master_Geo_City"), "t1.CitySysId = t7.CityId", array("Title as CityName", "Country"))
                ->joinLeft(array('t8' => "Tb_Agency_Subscription"), "t1.AgencySysId = t8.AgencySysId", array("ActivateDate"))
                ->joinLeft(array('t9' => "TB_Master_Subscription_Template"), "t8.TemplateSysId = t9.TemplateSysId", array("Title as PlanName", "TemplateSysId"))
                ->joinLeft(array('t10' => "Tb_Agency_BI_Data"), "t1.AgencySysId = t10.AgencySysId", array("LDayOne", "LDayTwo", "LDayThree", "LDayFour", "LDayFive", "LDaySix", "LDaySeven", "PDayOne", "PDayTwo", "PDayThree", "PDayFour", "PDayFive", "PDaySix", "PDaySeven"))
                ->where("t1.MasterAgencySysId =?", 0);
        if ($userRole == 2) {
            $select->where("t4.UserSysId = '" . $UserSysId . "'");
        }
        if ($userRole == 70) {
            $select->where("t4.RelationalManager = '" . $UserSysId . "'");
        }
        if (!empty($this->searchArr)) {
            if ($this->searchArr['Title'] != "") {
                $select->where("t1.Title like '%' + '{$this->searchArr['Title']}' + '%'");
            }
            if ($this->searchArr['AgencyEmailId'] != "") {
                $select->where("t1.PrimaryEmail like '%' + '{$this->searchArr['AgencyEmailId']}' + '%'");
            }
            if ($this->searchArr['AgencyMobileNo'] != "") {
                $select->where("t1.ContactInfo like '%' + '{$this->searchArr['AgencyMobileNo']}' + '%'");
            }
            if ($this->searchArr['IsPaymentGateway'] != "") {
                $select->where("t1.IsPaymentGateway = '" . $this->searchArr['IsPaymentGateway'] . "' ");
            }
            if ($this->searchArr['IsEmailSetting'] != "") {
                $select->where("t1.IsEmailSetting = '" . $this->searchArr['IsEmailSetting'] . "'");
            }
            if ($this->searchArr['IsB2CSite'] != "") {
                $select->where("t1.IsB2CSite = '" . $this->searchArr['IsB2CSite'] . "'");
            }
            if ($this->searchArr['IsB2CUrl'] != "") {
                $select->where("t1.IsB2CUrl = '" . $this->searchArr['IsB2CUrl'] . "'");
            }
            if ($this->searchArr['RelationalManager'] != "") {
                $select->where("t4.RelationalManager = '" . $this->searchArr['RelationalManager'] . "'");
            }
            if (!empty($this->searchArr['NotLoggedIn'])) {
                foreach ($this->searchArr['NotLoggedIn'] AS $key => $value) {
                    $fieldName = isset($AgencyLogginData[$key]['loggedId']) ? $AgencyLogginData[$key]['loggedId'] : null;
                    if ($fieldName) {
                        $select->where("t10.$fieldName = 0");
                    }
                }
            }

            if ($this->searchArr['Deleted'] == 1) {
                $select->where("t1.IsMarkForDelete = '1'");
            } else {
                if ($this->searchArr['IsActive'] == '1') {
                    $select->where("t1.IsActive = '1' AND t1.IsApproved = '1' AND t1.IsVarified = '1' ");
                }
                if ($this->searchArr['IsActive'] == '0') {
                    $select->where("t1.IsActive = '0' OR t1.IsApproved = '0' OR t1.IsVarified = '0' ");
                }
                if ($this->searchArr['Activated'] == 1) {
                    $select->where("t1.IsMarkForDelete = '0'");
                }
                if ($this->searchArr['IsVarified'] == 1) {
                    $select->where("t1.IsMarkForDelete = '0'");
                }

                if ($this->searchArr['IsActive'] == '1') {
                    $select->where("t1.IsActive = '1' AND t1.IsApproved = '1' AND t1.IsVarified = '1'");
                }
                if ($this->searchArr['IsActive'] == '0') {
                    $select->where("t1.IsActive = '0' OR t1.IsApproved = '0' OR t1.IsVarified = '0'");
                }
                if ($this->searchArr['Activated'] == 1) {
                    $select->where("t1.IsMarkForDelete = '0'");
                }
                if ($this->searchArr['IsVarified'] == 1) {
                    $select->where("t1.IsMarkForDelete = '0'");
                }
            }
        }
        $select->order("t1.AgencySysId DESC");
        //echo $select; exit;
        if ($isExport == 1) {
            return $this->db->query($select)->fetchAll();
        } else {
            return $select;
        }
    }

    public function getLeadList($sql = null) {

        $sql = @$sql ? $sql : " SELECT TBA.SecurityKey,TOPUPS.TopUpAvailFrom,TOPUPS.SubsTopUpsSysId,TOPUPS.TopUpAvailTo,TOPUPS.SubsTopUpsSysId,t2.Title as PlanName, TBA.AgencySysId, TBA.Title, FirmType, TBA.PrimaryContactName,TBA.CreateDate,TBA.IsActiveSubscription,  TBA.PrimaryEmail as EmailId, ContactInfo as PrimaryContactNo, TBMGC.Title as CityName, 
 TBMGC.Country,TBA.IsActive,TBA.IsApproved,TBA.IsVarified,TBA.IsEmailSetting,TBA.IsPaymentGateway,TBA.IsGTXPaymentGateway,TBA.IsB2CSite,TBA.IsB2bSite,TBA.IsB2CUrl,TBA.TrxCurrency,TBA.AgencySubsAsignId, TBAW.BalanceAmount,TBMC.Symbol,TBAU.UserName,TBA.IsSiteType,TBA.Ischeckwallet,TBA.PrimaryUserSysId ,TBAMW.TotalEmail,TBARM.RelationalManager
                    FROM TB_Agency AS TBA
                    LEFT JOIN TB_Agency_Wallet TBAW ON TBA.AgencySysId = TBAW.AgencySysId
                    LEFT JOIN TB_Agency_Marketing_Wallet TBAMW ON TBA.AgencySysId = TBAMW.AgencySysId 
                    LEFT JOIN TB_Agency_RelationshipMgr TBARM ON TBA.AgencySysId = TBARM.AgencySysId
                    LEFT JOIN TB_Admin_User TBAU ON TBAU.UserSysId = TBARM.UserSysId
		    LEFT JOIN TB_Master_Currency TBMC ON TBMC.CurrencyType = TBAW.CurrencyType
                    LEFT JOIN TB_Master_Geo_City TBMGC ON TBA.CitySysId = TBMGC.CityId
                    LEFT JOIN TB_Master_Subscription_TopUps TOPUPS ON TBA.AgencySysId = TOPUPS.OwnerSysId
                    LEFT JOIN TB_Master_Subscription_Plan t2 ON TOPUPS.SubsTopUpType = t2.SubsPlanSysId   
                 
		    WHERE TBA.MasterAgencySysId=0";

// echo $sql; exit;
//        $sql = " SELECT AgencySysId, Title, AgencyType, Address, PrimaryUserSysId, PrimaryEmail, ContactInfo, IsActive FROM TB_Agency ORDER BY AgencySysId DESC";
        if (!empty($this->searchArr)) {

            if ($this->searchArr['Title'] != "") {
                $sql .= " AND ( TBA.Title like '%' + '{$this->searchArr['Title']}' + '%' )";
            }

            if ($this->searchArr['IsPaymentGateway'] != "") {
                $sql .= " AND TBA.IsPaymentGateway = '" . $this->searchArr['IsPaymentGateway'] . "' ";
            }
            if ($this->searchArr['IsEmailSetting'] != "") {
                $sql .= " AND TBA.IsEmailSetting = '" . $this->searchArr['IsEmailSetting'] . "' ";
            }
            if ($this->searchArr['IsB2CSite'] != "") {
//                echo "<pre>";print_r($this->searchArr['IsB2CSite']);die;
                $sql .= " AND TBA.IsB2CSite = '" . $this->searchArr['IsB2CSite'] . "' ";
            }
            if ($this->searchArr['IsB2CUrl'] != "") {
//                echo "<pre>";print_r($this->searchArr['IsB2CSite']);die;
                $sql .= " AND TBA.IsB2CUrl = '" . $this->searchArr['IsB2CUrl'] . "' ";
            }
            if ($this->searchArr['RelationalManager'] != "") {
                $sql .= " AND TBARM.RelationalManager = '" . $this->searchArr['RelationalManager'] . "' ";
            }

            if ($this->searchArr['Activated'] == 1) {
                $sql .= " AND TBA.IsApproved = '1'  AND TBA.IsActive = '1' AND TBA.IsMarkForDelete = '0'";
            } else if ($this->searchArr['IsVarified'] == 1) {
                $sql .= " AND TBA.IsVarified = '1' AND TBA.IsApproved = '0' AND TBA.IsActive = '0'";
            } else {
                $sql .= " AND  TBA.IsApproved = '0' AND TBA.IsActive = '0'";
            }
            if ($this->searchArr['Deleted'] == 1) {
                $sql .= " AND  TBA.IsMarkForDelete = '1'";
            }
        }
        $sql .= " ORDER BY TBA.AgencySysId DESC";
        return $sql;
        // return $this->db->query($sql)->fetchAll();
    }

    /**
     * getRole.
     * 
     * @param type $userRoleId
     * @param type $agencyId
     * @return type
     */
    public function getRole($userRoleId, $agencyId) {

        $sql = "SELECT * FROM TB_Agency_UserRoles WHERE RoleSysId=$userRoleId AND AgencySysId=$agencyId AND IsActive=1 ORDER BY RoleTitle";
        return $this->db->query($sql)->fetchAll();
    }

    /**
     * getModuleList. List of the modules having no parents.
     * Please delete. Not in use @SHAKTIRANA.
     * @return array
     */
    public function getModulesList() {
        $query = "SELECT * FROM TB_Master_SubSystem_Module WHERE ParentModule=0 AND IsActive=1";
        return $this->db->query($query)->fetchAll();
    }

    /**
     * getMenuList.
     * 
     * @return array
     */
    public function getMenuList() {
        return array(array(1, 'Buy', 0), array(2, 'Flight', 1), array(3, 'Hotels', 1), array(4, 'Packages', 1), array(5, 'Fixed Dep', 4), array(6, 'Only Hotels', 4),
            array(7, 'Activity', 1), array(8, 'Transport', 1), array(9, 'Opportunities', 0), array(10, 'Lead', 9), array(11, 'Create Lead', 9), array(12, 'Support', 0));
    }

    /**
     * getPermissionList.
     * 
     * @return array
     */
    public function getPermissionList($subSystemType = 2) {
        //$subSystemType : 'TB B2B System' (2)
        $query = "SELECT ModuleType, ModuleTitle, ParentModule FROM TB_Master_SubSystem_Module WHERE SubSystemType=$subSystemType AND IsActive=1";
        return $this->db->query($query)->fetchAll();




        return array(//array('Buy', 'Flights', array('Packages', array(1=>'Fixed Departures', 2=>'Only Hotel Deals')), 'Activities'),
            array('Buy', 'Flights', 'Hotels', array('Packages', 'Fixed Departures', 'Only Hotel Deals', 'Land Pacakge', 'Build your own'), 'Activities', 'Transport', 'Insurance', 'Build your pacakge'),
            array('Opportunities', array('Leads', 'Create Lead', 'Forecasts')),
            array('Contacts', array('Customers', 'Suppliers', 'Employees', 'Branches')),
            array('My Inventory', array('Flight Deals', 'Hotel Deals', 'Land Packages', 'Fixed Departures', 'Flexi Packages', 'Activities', 'Transports')),
            array('Finance', array('Customers', 'Suppliers', 'Tripsbank')),
            array('Market Place', array('By Country', 'By Services')),
            array('Support'),
            array('Staff Management', array('Staff', 'Roles', 'Department')),
            array('My Preferences'),
            array('Setting', array('Company Profile', 'Term &amp; Conditions', 'Bank Details', 'Branches', 'Users', 'Currency'))
        );
    }

    public function getComponents($module = null) {
        if ($module) {
            $query = "SELECT * FROM TB_Master_SubSystem_Module_Component WHERE ModuleType={$module['ModuleType']} ORDER BY Title";
            return $this->db->query($query)->fetchAll();
        } else {
            //$query = "SELECT count(*) as COUNT FROM TB_Master_SubSystem_Module_Component GROUP BY ModuleType";
            $query = "SELECT count(*) as COUNT FROM TB_Master_SubSystem_Module_Component GROUP BY Title ORDER BY COUNT DESC";
            return $this->db->query($query)->fetchAll();
        }
    }

    public function getComponentTitle() {
        $query = "SELECT DISTINCT(Title) as Title FROM TB_Master_SubSystem_Module_Component ORDER BY Title";
        return $this->db->query($query)->fetchAll();
    }

    public function fetchAll($paginated = false) {
        if ($paginated) {
            $sql = @$query ? $query : " SELECT Title, AgencyType, Address, EmailId, PrimaryContactNo 
                                    FROM TB_Agency_User AS TBAU
                                    LEFT JOIN TB_Agency AS TBA 
                                    ON TBAU.AgencySysId = TBA.AgencySysId
                                    WHERE TBAU.AgencySysId > 0 ORDER BY Title";


            $resultSet = $this->db->query($sql)->fetchAll();

            $resultSetPrototype->setArrayObjectPrototype($resultSet);

            $paginatorAdapter = new DbSelect(
                    // our configured select object
                    $select,
                    // the adapter to run it against
                    $this->tableGateway->getAdapter(),
                    // the result set to hydrate
                    $resultSetPrototype
            );
            $paginator = new Paginator($paginatorAdapter);
            return $paginator;
        }
        $resultSet = $this->tableGateway->select();
        return $resultSet;
    }

    public function modelSave($data) {
        $userRolePermission = array('AgencySysId' => $agencySysId,
            'RoleSysId' => $roleSysId,
            'SubSystemType' => $subSystemType,
            'ModuleType' => $moduleId,
            'ComponentType' => $value,
            'RWUADMask' => 10101,
            'UpdateDate' => "$dateTime",
            'CreateDate' => "$dateTime",
            'IsActive' => 1);


        try {
            $this->db->insert('TB_Agency_UserRole_Permission', $userRolePermission);
            return $this->db->lastInsertId('TB_Agency_UserRole_Permission');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
    }

    /**
     * ExecuteQuery. To execute sql query.
     * 
     * @param string $query
     */
    public function executeQuery($query) {
        $queryType = substr(trim($query), 0, 6);
        try {
            $this->db->beginTransaction();
            if (strtoupper($queryType) == 'SELECT') {
                return $this->db->query($query)->fetchAll();
            } else {
                $this->db->query($query);
            }
            $this->db->commit();
        } catch (Exception $e) {
            $this->db->rollback;
            echo $e;
        }
    }

    /**
     * getAgencyList.
     * 
     * @return array
     */
    public function getAgencyList() {

        $sql = "SELECT TBA.AgencySysId, Title FROM TB_Agency AS TBA WHERE IsActive=1 AND MasterAgencySysId = 0 ORDER BY Title";
        return $this->db->query($sql)->fetchAll();
    }

    public function getAgencyListAll() {

        $sql = "SELECT TBA.AgencySysId, Title FROM TB_Agency AS TBA WHERE MasterAgencySysId = 0 ORDER BY Title";
        return $this->db->query($sql)->fetchAll();
    }

    public function getQueryAgencyWiseList() {

        $select = $this->db->select()->from(array("t1" => "TB_Agency_Customer_TravelPlan_Query"), array("count(TPSYSId) as Totalcount"))
                        ->joinLeft(array('t2' => "TB_Agency"), "t1.AgencySysId = t2.AgencySysId", array("DisplayName"))
                        ->where("t1.MasterTPSysId =?", 0)->where("t2.MasterAgencySysId =?", 0);
        $select->group(array("t1.AgencySysId", "t2.DisplayName"));
        $select->order("Totalcount DESC");
        return $select;
//      echo $select;exit;
    }

    public function getProposalQueryList() {

        $select = $this->db->select()->from(array("t1" => "TB_Agency_Customer_TravelPlan_Query"), array("count(TPSYSId) as Totalcount"))
                        ->joinLeft(array('t2' => "TB_Agency"), "t1.AgencySysId = t2.AgencySysId", array("DisplayName"))
                        ->where("t1.MasterTPSysId !=?", 0)->where("t2.MasterAgencySysId =?", 0);
        $select->group(array("t1.AgencySysId", "t2.DisplayName"));
        $select->order("Totalcount DESC");
        return $select;
//      echo $select;exit;
    }

    /**
     * Sanitize Data.
     * 
     * @param  string $input_data
     * @return string
     */
    public function sanitize_data($input_data) {
        $searchArr = array("document", "write", "alert", "%", "$", ";", "+", "|", "#", "<", ">", ")", "(", "'", "\'", ",", "AND", "JAVASCRIPT");
        $input_data = str_replace("script", "", $input_data);
        $input_data = str_replace("iframe", "", $input_data);
        $input_data = str_replace($searchArr, "", $input_data);

        return htmlentities(stripslashes($input_data), ENT_QUOTES);
    }

    public function getRelationshipMgr($agencySysId) {
        $sql = "SELECT * FROM TB_Agency_RelationshipMgr WHERE AgencySysId=$agencySysId AND IsActive=1";
        return $this->db->query($sql)->fetchAll();
    }

}