<?php

/**
 * Digitalus CMS
 *
 * LICENSE
 *
 */
class Travel_Model_AgencyAgentAuth extends Zend_Db_Table_Abstract {

    protected $db = NULL;

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

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

    public function init() {
        parent::init();
        $request = Zend_Controller_Front::getInstance()->getRequest();
        $this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
        $auth = Zend_Auth::getInstance();
        $authStorage = $auth->getStorage();
        $authStorage->read();
    }

    public function getUserList() {
        $select = $this->db->select()
                ->from("TB_Agency_Customer");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function authenticate($username = null, $password = null, $AgencySysId = null) {
        $select = $this->db->select()
                ->from("TB_Agency_User")
                ->where("EmailId =?", $username)
                ->where("Password =?", $password)
                ->where("AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

//    public function b2bAgentWithMobile($apiKey, $mobileNo) {
//        $select = $this->db->select()
//                ->from(array("t1" => "TB_Agency_Customer"), array("CustomerSysId", "Password", "IsActive", "ExitDate", "RandomCode"))
//                ->joinLeft(array('t2' => "TB_Agency_User"), "t1.CreatedByUserSysId = t2.UserSysId", array("AgencyCode", "LTRIM(RTRIM(t2.EmailId)) AS EmailId", "LTRIM(RTRIM(t2.FirstName)) AS FirstName", "LTRIM(RTRIM(t2.LastName)) AS LastName", "AgencySysId", "UserSysId", "AgencyName", "CreatedByUserSysId as MasterAgencySysId", "ContactNo1", "UserPicPath", "Address", "LTRIM(RTRIM(t2.PinCode)) AS Pincode"))
//                ->joinLeft(array('t3' => "TB_Agency"), "t2.AgencySysId = t3.AgencySysId", array("SecurityKey", 'IsApproved', 'NickId', 'LTRIM(RTRIM(t3.ServiceTaxNo)) AS GST', 'AgencyMarketPlaceSysId', 'Logo', 'BusinessType AS B2BType'))
//                ->joinLeft(array('t4' => "TB_Agency"), "t2.CreatedByUserSysId = t4.AgencySysId", array("ExchangeRateMaarkup", "MasterAgencySysId AS SuperAgencySysId", 'TrxCurrency'))
//                ->joinLeft(array('t5' => "TB_Agency_MarketPlace"), "t3.AgencyMarketPlaceSysId = t5.MarketPlaceSysId AND t5.IsActive = 1", array("MarkUpValue", "MarkUpType"))
//                ->joinLeft(array('t6' => "TB_Agency_DeptUserMap"), "t6.UserSysId = t2.UserSysId", array("ReportingToSysId"))
//                // ->joinLeft(array('t7' => "TB_Agency_CRM_LeadAccount"), "t7.AgencyCRMLeadSysId = t1.AgencyCRMLeadSysId", array("B2BType"))
//                ->joinleft(array("t8" => "TB_Master_Currency"), 't8.CurrencyType=t4.TrxCurrency', array('Symbol as CurrencySymbol'))
//                ->where("t4.SecurityKey = ?", $apiKey)
//                ->where("t1.IsB2BAgent =?", 1)
//                ->where("t1.IsActive =?", 1)
//                ->where("t1.IsMarkForDelete =?", '0')
//                ->where("t2.IsB2BAgent =?", 1)
//                ->where("t2.IsActive =?", 1)
//                ->where("t2.IsMarkForDel =?", '0')
//                ->where("t3.IsActive =?", 1)
//                ->where("t3.IsMarkForDelete =?", '0')
//                ->where("t4.IsActive =?", 1)
//                ->where("t4.IsMarkForDelete =?", '0')
//                ->where("t1.Contacts =?", $mobileNo);
//        $result = $this->db->fetchRow($select);
//        return $result;
//    }

    public function checkWrongAttempt($username, $AgencySysId) {
        if (trim($AgencySysId) == '4D0039E7-6ED7-4F42-B457-AFB72A509299') {
            $select1 = $this->db->select()
                    ->from(array("T1" => "TB_Agency"), array())
                    ->joinLeft(array('T2' => "TB_Agency"), "T1.AgencySysId = T2.MasterAgencySysId", array("SecurityKey"))
                    ->where("T1.SecurityKey =?", $AgencySysId)
                    ->where("T2.AgencyType =?", 2);
            $resultALL = $this->db->fetchAll($select1);
            $secutitykeyarray[] = $AgencySysId;
            if (!empty($resultALL)) {
                foreach ($resultALL as $securitykeyVal) {
                    $secutitykeyarray[] = $securitykeyVal['SecurityKey'];
                }
            }
        }
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_Customer"), array("CustomerSysId", "EmailId", "SMTypeId", "AgencySysId"))
                ->joinLeft(array('t2' => "TB_Agency_User"), "t1.CreatedByUserSysId = t2.UserSysId", array("FirstName", "LastName"))
                ->joinLeft(array('t3' => "TB_Agency"), "t2.AgencySysId = t3.AgencySysId", array('DisplayName as B2bDisplayName'))
                ->joinLeft(array('t4' => "TB_Agency"), "t2.CreatedByUserSysId = t4.AgencySysId", array('IsB2bSite', 'IsB2bSiteURL', 'DisplayName', 'PrimaryEmail'))
                ->joinLeft(array('t6' => "TB_Agency_DeptUserMap"), "t6.UserSysId = t2.UserSysId", array("ReportingToSysId"));
        if (trim($AgencySysId) == '4D0039E7-6ED7-4F42-B457-AFB72A509299') {
            $select->where("t4.SecurityKey IN (?)", $secutitykeyarray);
        } else {
            $select->where("t4.SecurityKey = ?", $AgencySysId);
        }
        $select->where("t1.IsB2BAgent =?", 1)
                ->where("t1.IsActive =?", 1)
                ->where("t1.IsMarkForDelete =?", '0')
                ->where("t2.IsB2BAgent =?", 1)
                ->where("t2.IsActive =?", 1)
                ->where("t2.IsMarkForDel =?", '0')
                ->where("t3.IsActive =?", 1)
                ->where("t3.IsMarkForDelete =?", '0')
                ->where("t4.IsActive =?", 1)
                ->where("t4.IsMarkForDelete =?", '0')
                ->where("t1.EmailId =?", $username);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function authenticateB2BAgentForB2C($username, $password, $AgencySysId, $otp = null) {
        if (trim($AgencySysId) == '4D0039E7-6ED7-4F42-B457-AFB72A509299') {
            $select1 = $this->db->select()
                    ->from(array("T1" => "TB_Agency"), array())
                    ->joinLeft(array('T2' => "TB_Agency"), "T1.AgencySysId = T2.MasterAgencySysId", array("SecurityKey"))
                    ->where("T1.SecurityKey =?", $AgencySysId)
                    ->where("T2.AgencyType =?", 2);
            $resultALL = $this->db->fetchAll($select1);
            $secutitykeyarray[] = $AgencySysId;
            if (!empty($resultALL)) {
                foreach ($resultALL as $securitykeyVal) {
                    $secutitykeyarray[] = $securitykeyVal['SecurityKey'];
                }
            }
        }
        $select = $this->db->select()
                ->from(array("t2" => "TB_Agency_User"), array("AgencyCode", "Password", "IsActive", "EmailId", "Salutation", "FirstName", "LastName", "AgencySysId", "UserSysId",  "CreatedByUserSysId as MasterAgencySysId", "ContactNo1", "UserPicPath", "Address", "PinCode"))
                ->joinLeft(array('t1' => "TB_Agency_Customer"), "t1.CreatedByUserSysId = t2.UserSysId", array("IsfromSM", "CustomerSysId",  "MacIp", "PasswordExpiryDate", "SMTypeId", "ExitDate", "RandomCode", "IsLogInNow"))
                ->joinLeft(array('t3' => "TB_Agency"), "t2.AgencySysId = t3.AgencySysId", array("DisplayName AS AgencyName","SecurityKey", 'IsApproved', 'NickId', 'ServiceTaxNo AS GST', 'AgencyMarketPlaceSysId', 'Logo', 'PrimaryUserSysId', 'BusinessType AS B2BType','FirmType', 'AgencyType', 'IsCheckB2BWallet', 'TrxCurrency as AgentCurrency','RMUserSysId AS ReportingToSysId','IsPaid'))
                ->joinLeft(array('t4' => "TB_Agency"), "t2.CreatedByUserSysId = t4.AgencySysId", array("ExchangeRateMaarkup", "AgencySysId AS SuperAgencySysId", 'TrxCurrency', 'IsEnabledB2BOtpLogin', 'MarkUpOnGTXNetworkPackage','IsFranchise','DistributorId','ServiceTaxNo','DisplayName as MasterAgencyDisplayName','Title as MasterAgencyTitle','PrimaryEmail','PrimaryMobileNo'))
                ->joinLeft(array('t5' => "TB_Agency_MarketPlace"), "t3.AgencyMarketPlaceSysId = t5.MarketPlaceSysId AND t5.IsActive = 1 AND t3.AgencyType = 1 ", array("MarkUpValue", "MarkUpType"))
                //->joinLeft(array('t6' => "TB_Agency_DeptUserMap"), "t6.UserSysId = t2.UserSysId", array("ReportingToSysId"))
                ->joinleft(array("t8" => "TB_Master_Currency"), 't8.CurrencyType=t4.TrxCurrency', array('Symbol as CurrencySymbol'))
                ->joinleft(array("t9" => "TB_Agency_Addresses"), 't9.AgencySysId=t4.AgencySysId', array('Address as MasterAgencyAddress'));
        if (trim($AgencySysId) == '4D0039E7-6ED7-4F42-B457-AFB72A509299') {
            $select->where("t4.SecurityKey IN (?)", $secutitykeyarray);
        } else {
            $select->where("t4.SecurityKey = ?", $AgencySysId);
        }
        $select->where("t2.EmailId =?", $username)
                ->where("t2.Password =?", $password)
                ->where("t1.IsActive =?", 1)
                ->where("t1.IsMarkForDelete =?", '0')
                ->where("t2.IsActive =?", 1)
                ->where("t2.IsMarkForDel =?", '0')
                ->where("t3.IsActive =?", 1)
                ->where("t3.IsMarkForDelete =?", '0')
                ->where("t4.IsActive =?", 1)
                ->where("t4.IsMarkForDelete =?", '0')
                ->where("t1.IsB2BAgent =1 OR t1.IsB2BAgent = 3")
                ->where("t2.IsB2BAgent =1 OR t2.IsB2BAgent = 3");
        if($AgencySysId == 'F38C55F4-5771-4EED-AAB2-D1CE094AEB4E') {
            //echo $select; exit;
        }
        
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getagentinfobyemail($email, $AgencySysId) {
        $select = $this->db->select()
                ->from(array('tb1' => "TB_Agency_Customer"), array("CustomerSysid", "AgencySysId", "EmailId", "FirstName", "Lastname"))
                ->joinLeft(array('tb2' => "TB_Agency"), "tb1.AgencySysId = tb2.AgencySysId", array('MasterAgencySysId', 'PrimaryEmail as fromEmail', 'Title as fromName', 'IsApproved'))
                ->joinLeft(array('tb3' => "TB_Agency"), "tb3.AgencySysId = tb2.MasterAgencySysId", array('IsB2bSiteUrl'))
                ->where("tb2.MasterAgencySysId = ?", $AgencySysId)
                ->where("tb1.EmailId =?", $email)
                ->where("tb1.IsB2BAgent =?", 1)
                ->where("tb1.IsActive =?", 1)
                ->where("tb1.IsMarkForDelete =?", '0')
                ->where("tb2.IsActive =?", 1)
                ->where("tb2.IsMarkForDelete =?", '0');
        //        echo $select;die;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getagentinfobyemailwithmasterAgencySysId($email, $AgencySysId, $IsActive = 1) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer"), array("CustomerSysId", "EmailId", "FirstName", "Lastname", "AgencySysId as CustomerAgencySysId", "SMTypeId", "Contacts"));
        $select->joinLeft(array('tb2' => "TB_Agency"), "tb1.AgencySysId = tb2.AgencySysId", array('MasterAgencySysId', 'PrimaryEmail as fromEmail', 'Title as fromName', 'IsApproved'));
        $select->joinLeft(array('tb3' => "TB_Agency"), "tb3.AgencySysId = tb2.MasterAgencySysId", array('IsB2bSiteUrl', "AgencySysId"));
        $select->where("tb2.MasterAgencySysId = ?", $AgencySysId);
        $select->where("tb1.EmailId =?", $email);
        $select->where("tb1.IsB2BAgent = 1 OR tb1.IsB2BAgent = 3");
        if ($IsActive == 1) {
            $select->where("tb1.IsActive =?", 1);
        }
        $select->where("tb1.IsMarkForDelete =?", '0');
        $select->where("tb2.IsActive =?", 1);
        $select->where("tb2.IsMarkForDelete =?", '0');

        //        echo $select;die;
        $result = $this->db->fetchRow($select);
        return $result;
    }
    
    public function getagentinfobyemailUserAgencySysId($email, $AgencySysId, $IsActive = 1,$AgencyType = 1) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_User"), array("UserSysId", "EmailId", "FirstName", "LastName", "AgencySysId as UserAgencySysId", "SMTypeId", "ContactNo1 as Contacts"));
        $select->joinLeft(array('tb2' => "TB_Agency"), "tb1.AgencySysId = tb2.AgencySysId", array('MasterAgencySysId', 'PrimaryEmail as fromEmail', 'Title as fromName', 'IsApproved','AgencyType'));
        $select->joinLeft(array('tb3' => "TB_Agency"), "tb3.AgencySysId = tb2.MasterAgencySysId", array('IsB2bSiteUrl', "AgencySysId"));
        $select->where("tb2.MasterAgencySysId = ?", $AgencySysId);
        $select->where("tb1.EmailId =?", $email);
        $select->where("tb1.IsB2BAgent = 1 OR tb1.IsB2BAgent = 3");
        if ($IsActive == 1) {
            $select->where("tb1.IsActive =?", 1);
        }
        $select->where("tb2.AgencyType =?", $AgencyType);
         
//        $select->where("tb1.IsMarkForDelete =?", '0');
        $select->where("tb2.IsActive =?", 1);
        $select->where("tb2.IsMarkForDelete =?", '0');

        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function changepasswordagentforb2c($password, $AgencySysId) {
        $updatewhere = "AgencySysId IN (SELECT AgencySysId FROM TB_Agency where MasterAgencySysId = '" . $AgencySysId . "'  AND IsMarkForDelete = 0)";
        $updateData = array('Password' => md5($password), 'RandStr' => $password);
        $updatequery = $this->db->update('TB_Agency_User', $updateData, $updatewhere);
        return $updatequery;
    }

    /* Added By Mangal for B2C on Date 03-07-18 start */

    public function getUserDetailsByAgencyIdb2c($userSysId, $AgencySysId, $IsActive = 1,$AgencyType = 1) {
        $select = $this->db->select()
                ->from(array('tb1' => "TB_Agency_User"), array("Password", "RandomCode", "SMTypeId", "IsActive", 'ContactNo1'))
                ->joinLeft(array('tb2' => "TB_Agency"), "tb1.AgencySysId = tb2.AgencySysId", array('MasterAgencySysId'))
                ->where("tb1.UserSysId =?", $userSysId)
                ->where("tb1.AgencySysId =?", $AgencySysId)
                ->where("tb2.AgencyType =?", $AgencyType)
                ->where("tb1.IsB2BAgent =?", $AgencyType);
//                ->where("IsMarkForDelete =?", '0');
        if ($IsActive == 1) {
            $select->where("tb1.IsActive =?", 1);
        }
//        echo $select;die;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function updateagentpasswordb2c($tableName, array $data, $whereCondition) {
        try {
            $isChanged = $this->db->update('TB_Agency_User', $data, $whereCondition);
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }

        return isset($isChanged) ? $isChanged : 0;
    }
    public function updateB2bAgency(array $data, $whereCondition) {
        try {
            $isChanged = $this->db->update('TB_Agency', $data, $whereCondition);
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }

        return isset($isChanged) ? $isChanged : 0;
    }

    public function updatePasswordFormB2bPassword($email, $AgencySysId) {
        $sql = "SELECT EmailId,FirstName FROM TB_Agency_User WHERE IsB2BAgent = 1 and  EmailId = '" . $email . "' and AgencySysId = '" . $AgencySysId . "'  AND IsActive = 1";
        $result = $this->db->fetchRow($sql);
        $password = Zend_Controller_Action_HelperBroker::getStaticHelper('General')->generateRandomString(5);
        if (!empty($result)) {
            $whereTrx = array('IsB2BAgent =?' => 1, 'EmailId=?' => $email, 'AgencySysId=?' => $AgencySysId);
            $updateData = array('Password' => md5($password), 'RandStr' => $password);
            try {
                $result = $this->db->update('TB_Agency_User', $updateData, $whereTrx);
                $returnArray = array("FirstName" => $result['FirstName'], "EmailId" => $email, "password" => $password, "message" => "", "status" => true);
            } catch (Zend_Exception $e) {
                $result = $e->getMessage();
                $returnArray = array("status" => false, "message" => "error");
            }
        } else {
            $returnArray = array("status" => false, "message" => "Email Id does not exist");
        }
//        echo"<pre>";print_r($returnArray);die('data');
        return $returnArray;
    }

    public function getUserDetailForRm($userSysId) {
        $select = $this->db->select()
                ->from("TB_Agency_User", array("LTRIM(RTRIM(FirstName)) AS FirstName", "LTRIM(RTRIM(LastName)) AS LastName", "LTRIM(RTRIM(EmailId)) AS EmailId", "ContactNo1", "Salutation", "LTRIM(RTRIM(countrycode)) AS countrycode"))
                ->where("UserSysId =?", $userSysId)
                ->where("IsActive =?", 1)
                ->where("IsMarkForDel =?", 0);
        //echo  $select; exit;     
        $result = $this->db->fetchRow($select);
        return $result;
    }

    /* Added By Mangal for B2C on Date 03-07-18 end */

    public function getB2BAgentForB2CData($userSysId = null, $SecurityKey = null) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_Customer"), array("IsfromSM", "CustomerSysId", "Password", "IsActive", "MacIp", "PasswordExpiryDate", "SMTypeId", "ExitDate", "RandomCode", "IsLogInNow"))
                ->joinLeft(array('t2' => "TB_Agency_User"), "t1.CreatedByUserSysId = t2.UserSysId", array("AgencyCode", "EmailId", "FirstName", "LastName", "AgencySysId", "UserSysId", "AgencyName", "CreatedByUserSysId as MasterAgencySysId", "ContactNo1", "UserPicPath", "Address", "PinCode"))
                ->joinLeft(array('t3' => "TB_Agency"), "t2.AgencySysId = t3.AgencySysId", array("SecurityKey", 'IsApproved', 'NickId', 'ServiceTaxNo AS GST', 'AgencyMarketPlaceSysId', 'Logo', 'PrimaryUserSysId', 'BusinessType AS B2BType', 'IsCheckB2BWallet'))
                ->joinLeft(array('t4' => "TB_Agency"), "t2.CreatedByUserSysId = t4.AgencySysId", array("ExchangeRateMaarkup", "MasterAgencySysId AS SuperAgencySysId", 'TrxCurrency', 'IsEnabledB2BOtpLogin'))
                ->joinLeft(array('t5' => "TB_Agency_MarketPlace"), "t3.AgencyMarketPlaceSysId = t5.MarketPlaceSysId AND t5.IsActive = 1", array("MarkUpValue", "MarkUpType"))
                ->joinLeft(array('t6' => "TB_Agency_DeptUserMap"), "t6.UserSysId = t2.UserSysId", array("ReportingToSysId"))
                //->joinLeft(array('t7' => "TB_Agency_CRM_LeadAccount"), "t7.AgencyCRMLeadSysId = t1.AgencyCRMLeadSysId", array("B2BType"))
                ->joinleft(array("t8" => "TB_Master_Currency"), 't8.CurrencyType=t4.TrxCurrency', array('Symbol as CurrencySymbol'))
                ->where("t4.SecurityKey =?", $SecurityKey)
                ->where("t2.UserSysId =?", $userSysId)
                ->where("t1.IsB2BAgent =?", 1)
                ->where("t1.IsActive =?", 1)
                ->where("t1.IsMarkForDelete =?", '0')
                ->where("t2.IsB2BAgent =?", 1)
                ->where("t2.IsActive =?", 1)
                ->where("t2.IsMarkForDel =?", '0')
                ->where("t3.IsActive =?", 1)
                ->where("t3.IsMarkForDelete =?", '0')
                ->where("t4.IsActive =?", 1)
                ->where("t4.IsMarkForDelete =?", '0');
        //  echo $select; exit;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function authenticateSupplierForgetPassword($userName, $apiKey, $userrole, $otp) {
        $select1 = $this->db->select()
                ->from(array("T1" => "TB_Agency"), array('AgencySysId'))
                ->where("T1.SecurityKey =?", $apiKey);
        $result = $this->db->fetchRow($select1);
        $MasterAgencySysId = isset($result['AgencySysId']) ? $result['AgencySysId'] : null;
        if ($MasterAgencySysId) {
            $select = $this->db->select()
                    ->from(array("t1" => "TB_Agency_User"), array("AgencyCode", "EmailId", "FirstName", "LastName", "AgencySysId", "UserSysId", "AgencyName", "ContactNo1", "UserPicPath", "Address", "PinCode"))
                    ->joinLeft(array('t2' => "TB_Agency"), "t1.AgencySysId = t2.AgencySysId", array('AgencyType', 'MasterAgencySysId', 'SecurityKey', 'IsApproved', 'NickId', 'ServiceTaxNo AS GST', 'AgencyMarketPlaceSysId', 'Logo'))
                    ->joinLeft(array('t3' => "TB_Agency_Customer"), "t1.UserSysId = t3.CreatedByUserSysId", array('CustomerSysId'))
                    ->joinLeft(array('t4' => "TB_Agency"), "t1.CreatedByUserSysId = t4.AgencySysId", array("ExchangeRateMaarkup", "MasterAgencySysId AS SuperAgencySysId", 'TrxCurrency', 'IsEnabledB2BOtpLogin'))
                    ->joinLeft(array('t5' => "TB_Agency_MarketPlace"), "t2.AgencyMarketPlaceSysId = t5.MarketPlaceSysId AND t5.IsActive = 1", array("MarkUpValue", "MarkUpType"))
                    ->joinLeft(array('t6' => "TB_Agency_DeptUserMap"), "t6.UserSysId = t1.UserSysId", array("ReportingToSysId"))
                    ->joinleft(array("t8" => "TB_Master_Currency"), 't8.CurrencyType=t4.TrxCurrency', array('Symbol as CurrencySymbol'))
                    ->joinleft(array("t9" => "TB_Agency_Series_Fare_Markup"), 't9.AgencySysId=t4.AgencySysId', array('MarkUpType as SFMarkUpType', 'B2CMarkUp as SFB2CMarkUp', 'B2BMarkUp as SFB2BMarkUp', 'InfantMarkUp as SFInfantMarkUp'))
                    ->joinleft(array("t10" => "TB_IC_Supplier"), 't10.SupplierAgencySysId=t1.AgencySysId', array('SupplierSysId'))
                    ->joinleft(array("t11" => "TB_Agency_Mapping"), 't11.AgencySysId = t2.AgencySysId', array('IsAutoFTEnabled', 'IsEnabledB2CWallet'))
                    ->where("t2.MasterAgencySysId =?", $MasterAgencySysId)
                    ->where("t1.EmailId =?", $userName)
                    ->where("t1.IsB2BAgent =?", $userrole)
                    ->where("t1.IsActive =?", 1)
                    ->where("t1.IsMarkForDel =?", '0')
                    ->where("t2.IsActive =?", 1)
                    ->where("t2.IsMarkForDelete =?", '0');
            $result = $this->db->fetchRow($select);
            return $result;
        }
    }

    public function authenticateSupplierLogin($userName, $userPassword, $apiKey, $userrole, $otp) {
        $select1 = $this->db->select()
                ->from(array("T1" => "TB_Agency"), array('AgencySysId'))
                ->where("T1.SecurityKey =?", $apiKey);
        $result = $this->db->fetchRow($select1);
        $MasterAgencySysId = isset($result['AgencySysId']) ? $result['AgencySysId'] : null;
        if ($MasterAgencySysId) {
            $select = $this->db->select()
                    ->from(array("t1" => "TB_Agency_User"), array("AgencyCode", "EmailId", "FirstName", "LastName", "AgencySysId", "UserSysId", "AgencyName", "ContactNo1", "UserPicPath", "Address", "PinCode"))
                    ->joinLeft(array('t2' => "TB_Agency"), "t1.AgencySysId = t2.AgencySysId", array('AgencyType', 'MasterAgencySysId', 'SecurityKey', 'IsApproved', 'NickId', 'ServiceTaxNo AS GST', 'AgencyMarketPlaceSysId', 'Logo'))
                    ->joinLeft(array('t3' => "TB_Agency_Customer"), "t1.UserSysId = t3.CreatedByUserSysId", array('CustomerSysId'))
                    ->joinLeft(array('t4' => "TB_Agency"), "t1.CreatedByUserSysId = t4.AgencySysId", array("ExchangeRateMaarkup", "MasterAgencySysId AS SuperAgencySysId", 'TrxCurrency', 'IsEnabledB2BOtpLogin'))
                    ->joinLeft(array('t5' => "TB_Agency_MarketPlace"), "t2.AgencyMarketPlaceSysId = t5.MarketPlaceSysId AND t5.IsActive = 1", array("MarkUpValue", "MarkUpType"))
                    ->joinLeft(array('t6' => "TB_Agency_DeptUserMap"), "t6.UserSysId = t1.UserSysId", array("ReportingToSysId"))
                    ->joinleft(array("t8" => "TB_Master_Currency"), 't8.CurrencyType=t4.TrxCurrency', array('Symbol as CurrencySymbol'))
                    ->joinleft(array("t9" => "TB_Agency_Series_Fare_Markup"), 't9.AgencySysId=t4.AgencySysId', array('MarkUpType as SFMarkUpType', 'B2CMarkUp as SFB2CMarkUp', 'B2BMarkUp as SFB2BMarkUp', 'InfantMarkUp as SFInfantMarkUp'))
                    ->joinleft(array("t10" => "TB_IC_Supplier"), 't10.SupplierAgencySysId=t1.AgencySysId', array('SupplierSysId'))
                    ->joinleft(array("t11" => "TB_Agency_Mapping"), 't11.AgencySysId = t2.AgencySysId', array('IsAutoFTEnabled', 'IsEnabledB2CWallet'))
                    ->where("t1.EmailId =?", $userName)
                    ->where("t1.Password =?", md5($userPassword))
                    ->where("t1.IsB2BAgent =?", $userrole)
                    ->where("t1.IsActive =?", 1)
                    ->where("t1.IsMarkForDel =?", '0')
                    ->where("t2.IsActive =?", 1)
                    ->where("t2.IsMarkForDelete =?", '0');
            $result = $this->db->fetchRow($select);
            return $result;
        }
    }
        public function updatePasswordFormfranchisePassword($email, $AgencySysId) {
        $sql = "SELECT EmailId,FirstName FROM TB_Agency_User WHERE IsB2BAgent = 3 and  EmailId = '" . $email . "' and AgencySysId = '" . $AgencySysId . "'  AND IsActive = 1";
        $result = $this->db->fetchRow($sql);
        $password = Zend_Controller_Action_HelperBroker::getStaticHelper('General')->generateRandomString(5);
        if (!empty($result)) {
            $whereTrx = array('IsB2BAgent =?' => 3, 'EmailId=?' => $email, 'AgencySysId=?' => $AgencySysId);
            $updateData = array('Password' => md5($password), 'RandStr' => $password);
            try {
                $result = $this->db->update('TB_Agency_User', $updateData, $whereTrx);
                $returnArray = array("FirstName" => $result['FirstName'], "EmailId" => $email, "password" => $password, "message" => "", "status" => true);
            } catch (Zend_Exception $e) {
                $result = $e->getMessage();
                $returnArray = array("status" => false, "message" => "error");
            }
        } else {
            $returnArray = array("status" => false, "message" => "Email Id does not exist");
        }
        return $returnArray;
    }
}