| Server IP : 103.234.187.230 / Your IP : 216.73.216.216 Web Server : Apache System : Linux lserver42043-ind.megavelocity.net 3.10.0-1160.108.1.el7.x86_64 #1 SMP Thu Jan 25 16:17:31 UTC 2024 x86_64 User : apache ( 48) PHP Version : 7.4.33 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /var/www/html/b2bzend/application/models/ |
Upload File : |
<?php
/*
* Catabatic Technology Pvt. Ltd.
* File Name :TblAgencyStaff.php
* File Description :TblAgencyStaff Model managed all data related to agency staff
* Created By : Pooja Choudhary
* Created Date: 01-July-2016
*/
class Travel_Model_TblAgencyStaff {
private $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();
}
/*
* getAgencyStaffList(); is used to get list of Agency's Staff members
* return array;
* Created By- Pooja
* Created on- 01 July 2016
*/
public function getAgencyStaffList($isActive = '', $StaffUserId = NULL, $AgencySysId = NULL, $IsB2BAgent = 0) {
//echo $isActive; exit;
$roleTitle = unserialize(AGENCY_USER_ROLE);
$whereCondition = " 1 = 1 "; // AND RoleTitle NOT IN ('" . $roleTitle[0] . "','" . $roleTitle[1] . "') ";
if ($AgencySysId) {
$whereCondition .= " AND TBAU.AgencySysId = " . @$AgencySysId;
}
if ($isActive != '') {
if ($isActive == 1) {
$whereCondition .= " AND TBAU.IsActive = " . $isActive;
} else {
$whereCondition .= " AND TBAU.IsActive = " . 0;
}
}
if ($StaffUserId) {
$whereCondition .= " AND TBAU.UserSysId != " . $StaffUserId;
}
// $whereCondition .= " AND TBAU.IsB2BAgent = " . $IsB2BAgent;
$whereCondition .= " AND TBAU.IsMarkForDel = 0";
$select = $this->db->select()
->from(array("TBAU" => "TB_Agency_User"))
->joinLeft(array('TBAUR' => 'TB_Agency_UserRoles'), 'TBAUR.RoleSysId=TBAU.UserRole', array('TBAUR.RoleTitle'))
->joinLeft(array('TBADU' => 'TB_Agency_DeptUserMap'), 'TBADU.UserSysId=TBAU.UserSysId', array('TBADU.DeptSysId', 'TBADU.IsActive as TBADUIsActive', 'TBADU.ReportingToSysId', 'TBADU.ReportingToSysId'))
->joinLeft(array('TBAD' => 'TB_Agency_Dept'), 'TBAD.DeptSysId=TBADU.DeptSysId', array('TBAD.DeptTitle'))
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBAU.CountrySysId', array('TBMGCO.Title as CountryName'))
->joinLeft(array('TBADUAU' => 'TB_Agency_User'), 'TBADU.ReportingToSysId=TBADUAU.UserSysId', array('TBADUAU.FirstName as REFirstName', 'TBADUAU.LastName as RELastName'))
->joinLeft(array('TBAUP' => 'Tb_Agency_User_Permissions'), new Zend_Db_Expr('TBAUP.ContentSysId = 131 AND TBAU.UserSysId = TBAUP.AgentSysId AND TBAUP.IsActive = 1 AND TBAUP.IsMarkForDel = 0'), array('TBAUP.IsActive as IsPermission'))
->where($whereCondition)
->order("FirstName ASC");
// echo $select;die;
$result = $this->db->fetchAll($select);
// echo $select;die;
return $result;
}
public function getAgencyStaffWhatsappAllow($AgencySysId, $PrimaryUserSysId) {
$whereCondition = " 1 = 1 ";
$whereCondition .= " AND TBAUP.AgencySysId = " . $AgencySysId;
$whereCondition .= " AND TBAUP.AgentSysId != " . $PrimaryUserSysId;
$whereCondition .= " AND TBAUP.IsActive = " . 1;
$whereCondition .= " AND TBAUP.IsMarkForDel = " . 0;
$whereCondition .= " AND TBAUP.ContentSysId = " . 131;
$select = $this->db->select()
->from(array("TBAUP" => "Tb_Agency_User_Permissions"))
->where($whereCondition);
$result = $this->db->fetchAll($select);
return $result;
}
public function getAgencyStaffbyID($AgencySysId, $StaffUserId) {
$whereCondition = " 1 = 1 ";
$whereCondition .= " AND TBAUP.AgencySysId = " . @$AgencySysId;
$whereCondition .= " AND TBAUP.AgentSysId = " . @$StaffUserId;
$whereCondition .= " AND TBAUP.IsActive = " . 1;
$whereCondition .= " AND TBAUP.IsMarkForDel = " . 0;
$select = $this->db->select()
->from(array("TBAUP" => "Tb_Agency_User_Permissions"))
->where($whereCondition);
$result = $this->db->fetchAll($select);
return $result;
}
/*
* getUserDataByUserId(); is used to get user data by user id
* return array;
* Created By- Pooja
* Created on- 01 July 2016
*/
public function getUserDataByUserId($userId) { //SpeakingLangSysId
$select = $this->db->select()
->from(array("TBAU" => "TB_Agency_User"))
->joinLeft(array('TBAUR' => 'TB_Agency_UserRoles'), 'TBAUR.RoleSysId=TBAU.UserRole', array('TBAUR.RoleTitle'))
->joinLeft(array('TBML' => 'TB_Master_Lang'), 'TBML.LangSysId=TBAU.SpeakingLangSysId', array('TBML.TitleEng'))
->joinLeft(array('TBADU' => 'TB_Agency_DeptUserMap'), 'TBADU.UserSysId=TBAU.UserSysId', array('TBADU.DeptSysId', 'TBADU.IsActive as TBADUIsActive', 'TBADU.ReportingToSysId'))
->joinLeft(array('TBA' => 'TB_Agency_User'), 'TBA.UserSysId=TBADU.ReportingToSysId', array('TBAU.FirstName as RFirstName', 'TBAU.LastName as RLastName'))
->joinLeft(array('TBAD' => 'TB_Agency_Dept'), 'TBAD.DeptSysId=TBADU.DeptSysId', array('TBAD.DeptTitle', 'IsOps'))
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBAU.CountrySysId', array('TBMGCO.Title as CountryName'))
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBAU.CitySysId', array('TBMGC.Title as CityName'))
->joinLeft(array('TB' => 'TB_Agency'), 'TB.AgencySysId=TBAU.AgencySysId', array('TB.AgencyMarketPlaceSysId', 'TB.Title', 'TB.AdharCardNo', 'TB.MasterAgencySysId', 'TB.IsVarified', 'TB.IsMobileVerified', 'Logo as UserAgencyLogo', 'BusinessType', 'FirmType', 'IsPaid', 'DistributorId', 'URL', 'PrimaryUserSysId', 'RMUserSysId'))
->joinLeft(array('TBAC' => 'TB_Agency_Customer'), 'TBAU.UserSysId=TBAC.CreatedByUserSysId', array('CustomerSysId', 'LeadSourceSysId', 'DOB'))
->joinLeft(array('TBMLS' => 'TB_Master_LeadSource'), 'TBMLS.LeadSourceSysId=TBAC.LeadSourceSysId', array('TBMLS.Title as LeadSource'))
->joinLeft(array('TBAM' => 'TB_Agency_Mapping'), 'TBAM.AgencySysId=TB.AgencySysId', array('TBAM.IsAadharVerified', 'TBAM.IsPANVerified', 'TBAM.IsGSTVerified'))
->where("TBAU.UserSysId =?", $userId);
$result = $this->db->fetchRow($select);
return $result;
}
public function getFranchiseStaffData($agencySysId) { //SpeakingLangSysId
$select = $this->db->select()
->from(array("TBAU" => "TB_Agency_User"), array('FirstName', 'LastName', 'EmailId', 'UserSysId', 'RegisterDate', 'IsActive', 'ContactNo1', 'countrycode', 'HomePhone', 'countrycode2', 'signatureType'))
//->joinLeft(array('TBAUR' => 'TB_Agency_UserRoles'), 'TBAUR.RoleSysId=TBAU.UserRole', array('TBAUR.RoleTitle'))
//->joinLeft(array('TBML' => 'TB_Master_Lang'), 'TBML.LangSysId=TBAU.SpeakingLangSysId', array('TBML.TitleEng'))
//->joinLeft(array('TBADU' => 'TB_Agency_DeptUserMap'), 'TBADU.UserSysId=TBAU.UserSysId', array('TBADU.DeptSysId', 'TBADU.IsActive as TBADUIsActive', 'TBADU.ReportingToSysId'))
//->joinLeft(array('TBA' => 'TB_Agency_User'), 'TBA.UserSysId=TBADU.ReportingToSysId', array('TBAU.FirstName as RFirstName', 'TBAU.LastName as RLastName'))
//->joinLeft(array('TBAD' => 'TB_Agency_Dept'), 'TBAD.DeptSysId=TBADU.DeptSysId', array('TBAD.DeptTitle', 'IsOps'))
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBAU.CountrySysId', array('TBMGCO.Title as CountryName'))
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBAU.CitySysId', array('TBMGC.Title as CityName'))
->joinLeft(array('TB' => 'TB_Agency'), 'TB.AgencySysId=TBAU.AgencySysId', array('PrimaryUserSysId', 'Title', 'DisplayName'))
//->joinLeft(array('TBAC' => 'TB_Agency_Customer'), 'TBAU.UserSysId=TBAC.CreatedByUserSysId', array('CustomerSysId', 'LeadSourceSysId', 'DOB'))
//->joinLeft(array('TBMLS' => 'TB_Master_LeadSource'), 'TBMLS.LeadSourceSysId=TBAC.LeadSourceSysId', array('TBMLS.Title as LeadSource'))
->joinLeft(array('TBAM' => 'TB_Agency_Mapping'), 'TBAM.AgencySysId=TB.AgencySysId', array('TBAM.IsAadharVerified', 'TBAM.IsPANVerified', 'TBAM.IsGSTVerified'))
->where("TBAU.AgencySysId =?", $agencySysId);
// echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getUserPermissionMainMenu($userId) { //SpeakingLangSysId
$select = $this->db->select()
->from(array("TBAU" => "Tb_Agency_User_Permissions"))
->where("TBAU.AgentSysId =?", $userId)
->where("TBAU.IsActive =?", 1)
->where("TBAU.IsMarkForDel =?", 0);
//echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getUserPermissionSubMenu($userId) { //SpeakingLangSysId
$select = $this->db->select()
->from(array("TBAU" => "TB_Agency_UserRole_Permission"))
->where("TBAU.AgentSysId =?", $userId)
->where("TBAU.IsActive =?", 1)
->where("TBAU.IsMarkForDelete =?", 0);
$result = $this->db->fetchAll($select);
// if (empty($result)) {
// $select = $this->db->select()
// ->from(array("TBAU" => "TB_Agency_UserRole_Permission_Test"))
// ->where("TBAU.IsActive =?", 1)
// ->where("TBAU.IsMarkForDelete =?", 0);
// ;
// $result = $this->db->fetchAll($select);
// }
return $result;
}
public function getUserPermissionContent($userId) { //SpeakingLangSysId
$select = $this->db->select()
->from(array("TBAU" => "TB_Agency_UserRole_Permission_Content"))
->where("TBAU.AgentSysId =?", $userId)
->where("TBAU.IsActive =?", 1)
->where("TBAU.IsMarkForDelete =?", 0);
$result = $this->db->fetchAll($select);
return $result;
}
/*
* addUserDetails(); is used to save user data
* return array;
* Created By- Pooja
* Created on- 05 July 2016
*/
public function addUserDetails($agencyUserDataForDb = array()) {
try {
$this->db->insert('TB_Agency_User', $agencyUserDataForDb);
$userId = $this->db->lastInsertId('TB_Agency_User');
return $userId;
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
}
/*
* isUserEmailExists(); used to check email id of user is exists or not
* return array;
* Created By- Pooja
* Created on- 05 July 2016
*/
public function isUserEmailExistsB2b($email, $StaffUserId = NULL, $AgencySysId = NULL, $IsB2BAgent = 0) {
$whereCondition = "EmailId = '$email'";
if ($AgencySysId) {
$whereCondition .= " AND AgencySysId = " . $AgencySysId;
}
if ($StaffUserId) {
$whereCondition .= " AND UserSysId != " . $StaffUserId;
}
//if($IsB2BAgent){
$whereCondition .= " AND IsB2BAgent = " . $IsB2BAgent;
//}
$whereCondition .= " AND IsMarkForDel = 0";
$select = $this->db->select()
->from("TB_Agency_User", array('count(*) AS totalRecords'))
->where($whereCondition);
$count = $this->db->fetchOne($select);
if ($count > 0) {
return true;
}
return false;
}
public function checkB2bUserInAgency($email, $AgencySysId) {
$select = $this->db->select();
$select->from("TB_Agency_User");
$select->where("EmailId =?", $email);
$select->where("AgencySysId =?", $AgencySysId);
$result = $this->db->fetchRow($select);
return $result;
}
public function isB2BUserEmailExists($email, $StaffUserId = NULL, $AgencySysId = NULL, $IsB2BAgent = 0) {
$select = $this->db->select();
$select->from("TB_Agency_User");
$select->where("EmailId =?", $email);
if ($IsB2BAgent == 0) {
$select->where("IsB2BAgent =?", $IsB2BAgent);
} else {
$select->where("IsB2BAgent =?", $IsB2BAgent);
$select->where("AgencySysId =?", $AgencySysId);
}
if ($StaffUserId) {
$select->where("UserSysId =?", $StaffUserId);
}
$select->where("IsMarkForDel =?", 0);
$result = $this->db->fetchRow($select);
return $result;
}
public function isB2BUserEmailExistsNew($email, $AgencySysId, $IsB2BAgent = 0, $StaffUserId = '', $AgencyType = 1) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency"), array('AgencySysId'));
$select->joinLeft(array('tb2' => "TB_Agency_User"), "tb1.AgencySysId = tb2.AgencySysId", array('UserSysId'));
$select->where("tb1.MasterAgencySysId =?", $AgencySysId);
if ($StaffUserId != '') {
$select->where("tb2.UserSysId != ?", $StaffUserId);
}
$select->where("tb2.IsB2BAgent =?", $IsB2BAgent);
$select->where("tb1.AgencyType =?", $AgencyType);
$select->where("tb2.EmailId =?", $email);
$select->where("tb2.IsMarkForDel =?", 0);
// echo $select; die;
$result = $this->db->fetchRow($select);
return $result;
}
public function isB2BUserMobileExists($contact, $AgencySysId, $IsB2BAgent = 0, $StaffUserId = '', $AgencyType = 1) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency"), array('AgencySysId'));
$select->joinLeft(array('tb2' => "TB_Agency_User"), "tb1.AgencySysId = tb2.AgencySysId", array('UserSysId'));
$select->where("tb1.MasterAgencySysId =?", $AgencySysId);
if ($StaffUserId != '') {
$select->where("tb2.UserSysId != ?", $StaffUserId);
}
$select->where("tb2.IsB2BAgent =?", $IsB2BAgent);
$select->where("tb1.AgencyType =?", $AgencyType);
$select->where("tb2.ContactNo1 =?", $contact);
$select->where("IsMarkForDel =?", 0);
// echo $select; die;
$result = $this->db->fetchRow($select);
return $result;
}
public function isUserEmailExists($email, $StaffUserId = NULL, $AgencySysId = NULL, $IsB2BAgent = 0) {
$whereCondition = "EmailId = '$email'";
if ($StaffUserId) {
$whereCondition .= " AND UserSysId != " . $StaffUserId;
}
if ($IsB2BAgent == 0) {
$whereCondition .= " AND IsB2BAgent = " . $IsB2BAgent;
} else {
$whereCondition .= " AND IsB2BAgent = " . $IsB2BAgent;
$whereCondition .= " AND AgencySysId = " . $AgencySysId;
}
$whereCondition .= " AND IsMarkForDel = 0";
$select = $this->db->select()
->from("TB_Agency_User", array('count(*) AS totalRecords'))
->where($whereCondition);
$count = $this->db->fetchOne($select);
if ($count > 0) {
return true;
}
return false;
}
public function isUserContactExists($contact, $StaffUserId = NULL, $AgencySysId = NULL, $IsB2BAgent = 0) {
$whereCondition = "ContactNo1 = '$contact'";
if ($StaffUserId) {
$whereCondition .= " AND UserSysId != " . $StaffUserId;
}
if ($IsB2BAgent == 0) {
$whereCondition .= " AND IsB2BAgent = " . $IsB2BAgent;
} else {
$whereCondition .= " AND IsB2BAgent = " . $IsB2BAgent;
$whereCondition .= " AND AgencySysId = " . $AgencySysId;
}
$whereCondition .= " AND IsMarkForDel = 0";
$select = $this->db->select()
->from("TB_Agency_User", array('count(*) AS totalRecords'))
->where($whereCondition);
$count = $this->db->fetchOne($select);
if ($count > 0) {
return true;
}
return false;
}
public function getUserId($email, $StaffUserId = NULL, $AgencySysId = NULL, $IsB2BAgent = 0) {
$whereCondition = "EmailId = '$email'";
if ($AgencySysId) {
$whereCondition .= " AND CreatedByUserSysId = " . $AgencySysId;
}
if ($StaffUserId) {
$whereCondition .= " AND UserSysId != " . $StaffUserId;
}
//if($IsB2BAgent){
$whereCondition .= " AND IsB2BAgent = " . $IsB2BAgent . "";
//}
$select = $this->db->select()
->from("TB_Agency_User", array('UserSysId'))
->where($whereCondition);
return $count = $this->db->fetchOne($select);
}
public function getUserIdB2b($email, $StaffUserId = NULL, $AgencySysId = NULL, $IsB2BAgent = 0) {
$whereCondition = "EmailId = '$email'";
if ($AgencySysId) {
$whereCondition .= " AND AgencySysId = " . $AgencySysId;
}
if ($StaffUserId) {
$whereCondition .= " AND UserSysId != " . $StaffUserId;
}
//if($IsB2BAgent){
$whereCondition .= " AND IsB2BAgent = " . $IsB2BAgent . "";
//}
$select = $this->db->select()
->from("TB_Agency_User", array('UserSysId'))
->where($whereCondition);
//echo $select; exit;
return $count = $this->db->fetchOne($select);
}
/*
* addDeptUserMapping(); is used to save user data mapping with dept, role, reports to
* return array;
* Created By- Pooja
* Created on- 05 July 2016
*/
public function addDeptUserMapping($deptMappingArr = array()) {
try {
$this->db->insert('TB_Agency_DeptUserMap', $deptMappingArr);
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
}
public function addPermissionMapping($dataArr = array()) {
try {
$this->db->insert('TB_Agency_UserRole_Permission', $dataArr);
$insertId = $this->db->lastInsertId('TB_Agency_UserRole_Permission');
return $insertId;
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
}
public function addPermissionContent($dataArr = array()) {
try {
$this->db->insert('TB_Agency_UserRole_Permission_Content', $dataArr);
$insertId = $this->db->lastInsertId('TB_Agency_UserRole_Permission_Content');
return $insertId;
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
}
public function permission_delete($where) {
$dbtable = new Zend_Db_Table('TB_Agency_UserRole_Permission');
return $dbtable->delete($where);
}
public function permission_con_delete($where) {
$dbtable = new Zend_Db_Table('TB_Agency_UserRole_Permission_Content');
return $dbtable->delete($where);
}
/*
* addUserDetails(); is used to update user data
* return array;
* Created By- Pooja
* Created on- 05 July 2016
*/
public function editCustomerDetails($agencyUserDataForDb, $customerSysId) {
$where = array('CustomerSysId =?' => $customerSysId);
$this->db->update('TB_Agency_Customer', $agencyUserDataForDb, $where);
}
public function editUserDetails($agencyUserDataForDb = array(), $userSysId = NULL) {
$where = array('UserSysId =?' => $userSysId);
$this->db->update('TB_Agency_User', $agencyUserDataForDb, $where);
}
/*
* editDeptUserMapping(); is used to update user department
* return array;
* Created By- Pooja
* Created on- 05 July 2016
*/
public function editDeptUserMapping($deptMappingArr = array(), $userSysId = NULL) {
$where = array('UserSysId =?' => $userSysId);
$this->db->update('TB_Agency_DeptUserMap', $deptMappingArr, $where);
}
public function getUserDetailsById($userSysId) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency_User"), array('*'));
$select->joinLeft(array('tb2' => "TB_Agency"), "tb1.AgencySysId = tb2.AgencySysId", array('RMUserSysId'));
$select->where("UserSysId =?", $userSysId);
$result = $this->db->fetchRow($select);
return $result;
}
/**
* SetActiveDeactive. To set IsActive/IsDeactive record.
*
* @param string $whereCondition
* @param string $tableName
* @param int $isActive
* @return int
*/
public function setActiveDeactive($whereCondition, $tableName, $value) {
try {
$isChanged = $this->db->update($tableName, array('IsActive' => $value), $whereCondition);
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
return @$isChanged ? $isChanged : 0;
}
public function getUserDetailsByAgencyId($userSysId, $AgencySysId) {
$select = $this->db->select()
->from("TB_Agency_User")
->where("UserSysId =?", $userSysId)
->where("AgencySysId =?", $AgencySysId);
$result = $this->db->fetchRow($select);
return $result;
}
public function updateagentprofile($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 @$isChanged ? $isChanged : 0;
}
public function checkExistAgencyCode($agencyName) {
$agencyUserColumn = array('AgencyCode');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_User"), $agencyUserColumn);
$select->where('tbl.AgencyName = ?', $agencyName);
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.IsMarkForDel = ?', 0);
$result = $this->db->fetchrow($select);
return $result;
}
public function getLastAgencyCode($AgencySysId = NULL) {
$agencyUserColumn = array('AgencyCode');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_User"), $agencyUserColumn);
//$select->where('tbl.AgencyName = ?', $agencyName);
$select->where('tbl.IsActive = ?', 1);
$select->where("tbl.AgencyCode != ''");
$select->where('tbl.IsMarkForDel = ?', 0);
if ($AgencySysId) {
$select->where('tbl.AgencySysId = ?', $AgencySysId);
}
$select->order("tbl.UserSysId DESC");
$result = $this->db->fetchrow($select);
return $result;
}
public function getAgencyAutoSuggest($keyword, $AgencySysId = NULL) {
$keyword = trim($keyword);
$agencyUserColumn = array('FirstName', 'LastName', 'UserSysId', 'AgencyCode');
$agencyColumn = array('AgencySysId', 'MasterAgencySysId', '', 'Title as AgencyName', 'PrimaryContactName', 'PrimaryEmail', 'ContactInfo');
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency"), $agencyColumn);
$select->joinLeft(array('tb2' => "TB_Agency_User"), "tb1.AgencySysId = tb2.AgencySysId", $agencyUserColumn);
$select->where($keyword);
$select->where('tb1.IsActive = ?', 1);
$select->where('tb2.IsActive = ?', 1);
$select->where('tb2.IsB2BAgent = ?', 1);
$select->where('tb2.UserRole = ?', 0);
$select->where('tb1.IsMarkForDelete = ?', 0);
$select->where('tb1.MasterAgencySysId = ?', $AgencySysId);
$select->where("tb1.MasterAgencySysId <> 0");
$rowset = $this->db->fetchAll($select);
//echo "<pre>";print_r($rowset);die;
$response = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
$response[] = array('FirstName' => stripslashes(trim($row['FirstName'])), 'LastName' => stripslashes(trim($row['LastName'])), 'EmailId' => stripslashes(trim($row['PrimaryEmail'])), 'ContactNo1' => stripslashes(trim($row['ContactInfo'])), 'UserSysId' => $row['AgencySysId'], 'AgencySysId' => $row['MasterAgencySysId'], 'AgencyName' => stripslashes($row['AgencyName']), 'label' => stripslashes($row['AgencyName']) . "(" . stripslashes($row['PrimaryEmail']) . ")");
}
}
return $response;
}
public function chkUserExist($AgencySysId, $CreatorByUserSysId, $EmailId = NULL) {
$agencyUserColumn = array('UserSysId', 'AgencySysId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_User"), $agencyUserColumn);
//$select->where('tbl.AgencyName = ?', $agencyName);
$select->where('tbl.IsB2BAgent = ?', 1);
$select->where('tbl.IsActive = ?', 1);
$select->where("tbl.AgencyCode != ''");
$select->where('tbl.IsMarkForDel = ?', 0);
$select->where('tbl.CreatedByUserSysId = ?', $CreatorByUserSysId);
$select->where('tbl.AgencySysId = ?', $AgencySysId);
if ($EmailId) {
$select->where('tbl.EmailId = ?', $EmailId);
}
$select->order("tbl.UserSysId DESC");
$result = $this->db->fetchrow($select);
return $result;
}
public function chkB2BUserExist($AgencySysId, $CreatorByUserSysId, $EmailId = NULL) {
$agencyUserColumn = array('UserSysId', 'AgencySysId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_User"), $agencyUserColumn);
//$select->where('tbl.AgencyName = ?', $agencyName);
$select->where('tbl.IsB2BAgent = ?', 1);
$select->where('tbl.IsActive = ?', 1);
$select->where("tbl.AgencyCode != ''");
$select->where('tbl.IsMarkForDel = ?', 0);
$select->where('tbl.UserRole != ?', 0);
$select->where('tbl.CreatedByUserSysId = ?', $CreatorByUserSysId);
$select->where('tbl.AgencySysId = ?', $AgencySysId);
if ($EmailId) {
$select->where('tbl.EmailId = ?', $EmailId);
}
$select->order("tbl.UserSysId DESC");
$result = $this->db->fetchrow($select);
return $result;
}
public function getB2BAgentAutoSuggest($keywordval, $AgencySysId, $AgencyType = 1, $b2btype = 1) {
$keyword = trim($keywordval);
$agencyUserColumn = array('Salutation', 'FirstName', 'LastName', 'UserSysId', 'AgencyCode', 'EmailId', 'Address', 'PinCode', 'CitySysId', 'CountrySysId', 'StateOrZoneSysId', 'countrycode');
$agencyColumn = array('AgencySysId', 'MasterAgencySysId', '', 'Title as AgencyName', 'PrimaryContactName', 'PrimaryEmail', 'ContactInfo');
$select = $this->db->select();
if ($AgencyType == 2) {
$select->from(array('tb22' => "TB_Mapping_Supplier_Agency_To_B2B_Customer"), array());
$select->joinLeft(array('tb1' => "TB_Agency_Customer"), "tb22.CustomerSysId = tb1.CustomerSysId", array());
} else {
$select->from(array('tb1' => "TB_Agency_Customer"), array());
}
$select->joinLeft(array('tb6' => "TB_Agency"), "tb1.AgencySysId = tb6.AgencySysId", $agencyColumn);
$select->joinLeft(array('tb2' => "TB_Agency_User"), "tb1.AgencySysId = tb2.AgencySysId", $agencyUserColumn);
$select->joinLeft(array('tb3' => 'TB_Master_Geo_Country'), 'tb1.CountrySysId=tb3.ContId', array('tb3.Title as CountryName'));
$select->joinLeft(array('tb4' => 'TB_Master_Geo_City'), 'tb1.CitySysId=tb4.CityId', array('tb4.Title as CityName'));
$select->joinLeft(array('tb5' => 'TB_Master_Geo_State'), 'tb1.StateOrZoneSysId=tb5.StateId', array('tb5.Title as StateName'));
$select->where($keyword);
$select->where('tb1.IsActive = ?', 1);
$select->where('tb2.IsActive = ?', 1);
$select->where('tb1.IsB2BAgent = ?', 1);
$select->where('tb6.IsMarkForDelete = ?', 0);
$select->where('tb6.IsApproved = ?', 1);
if ($b2btype == 2) {
$select->where("tb6.BusinessType = ?", 1);
} else {
$select->where("tb6.BusinessType <> ?", 1);
}
if ($AgencyType == 2) {
$select->where("tb22.AgencySysId = ?", $AgencySysId);
} else {
$select->where('tb6.MasterAgencySysId = ?', $AgencySysId);
}
$select->where("tb6.MasterAgencySysId <> 0");
//echo $select; exit;
$rowset = $this->db->fetchAll($select);
$response = array();
$checkAgency = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
if (!in_array((int) $row['AgencySysId'], $checkAgency)) {
$AgentANme = trim(stripslashes(trim($row['FirstName'])) . ' ' . stripslashes(trim($row['LastName'])));
$response[] = array('Salutation' => trim($row['Salutation']), 'FirstName' => stripslashes(trim($row['FirstName'])), 'LastName' => stripslashes(trim($row['LastName'])), 'EmailId' => stripslashes(trim($row['EmailId'])), 'CountryCode' => trim($row['countrycode']), 'ContactNo1' => stripslashes(trim($row['ContactInfo'])), 'UserSysId' => $row['AgencySysId'], 'AgencySysId' => $row['MasterAgencySysId'], 'AgencyName' => stripslashes($row['AgencyName']), 'label' => stripslashes($row['AgencyName']) . "(" . stripslashes(trim($row['EmailId'])) . ", " . $AgentANme . ")", 'CountryName' => stripslashes($row['CountryName']), 'CountrySysId' => stripslashes($row['CountrySysId']), 'CityName' => stripslashes($row['CityName']), 'CitySysId' => stripslashes($row['CitySysId']), 'StateName' => stripslashes($row['StateName']), 'StateOrZoneSysId' => stripslashes($row['StateOrZoneSysId']), 'Address' => stripslashes($row['Address']), 'PinCode' => stripslashes(trim($row['PinCode'])));
$checkAgency[] = (int) $row['AgencySysId'];
}
}
}
return $response;
}
public function getAgencyStaffListUserRole($isActive = NULL, $StaffUserId = NULL, $AgencySysId = NULL, $IsB2BAgent = 0) {
$roleTitle = unserialize(AGENCY_USER_ROLE);
$whereCondition = " 1 = 1 "; // AND RoleTitle NOT IN ('" . $roleTitle[0] . "','" . $roleTitle[1] . "') ";
// if ($UserRole==13 || $UserRole==5) {
// $whereCondition .= " AND TBAUR.RoleTitle = 'Owner'";
// }
// else {
// $whereCondition .= " AND TBAUR.RoleTitle = 'Manager'";
// }
if ($IsB2BAgent == 0 && $AgencySysId != '') {
$whereCondition .= " AND TBAU.AgencySysId = " . @$AgencySysId;
}
if ($IsB2BAgent == 1 && $AgencySysId != '') {
$whereCondition .= " AND TBAU.CreatedByUserSysId = " . @$AgencySysId;
}
if ($isActive) {
$whereCondition .= " AND TBAU.IsActive = " . $isActive;
}
if ($StaffUserId) {
$whereCondition .= " AND TBAU.UserSysId != " . $StaffUserId;
}
$whereCondition .= " AND TBAU.IsB2BAgent = " . $IsB2BAgent;
$whereCondition .= " AND TBAU.IsMarkForDel = 0";
$select = $this->db->select()
->from(array("TBAU" => "TB_Agency_User"))
->joinLeft(array('TBAUR' => 'TB_Agency_UserRoles'), 'TBAUR.RoleSysId=TBAU.UserRole', array('TBAUR.RoleTitle'))
->joinLeft(array('TBADU' => 'TB_Agency_DeptUserMap'), 'TBADU.UserSysId=TBAU.UserSysId', array('TBADU.DeptSysId', 'TBADU.ReportingToSysId'))
->joinLeft(array('TBAD' => 'TB_Agency_Dept'), 'TBAD.DeptSysId=TBADU.DeptSysId', array('TBAD.DeptTitle'))
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBAU.CountrySysId', array('TBMGCO.Title as CountryName'))
->where($whereCondition)
->order("FirstName ASC");
$result = $this->db->fetchAll($select);
// echo '<pre>'.$whereCondition; print_r($result);die;
return $result;
}
public function getAgencyUserList($AgencySysId = NULL, $IsB2C = NULL) {
$agencyUserColumn = array('UserSysId', 'FirstName', 'LastName', 'AgencySysId', 'EmailId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_User"), $agencyUserColumn);
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.IsMarkForDel = ?', 0);
if ($AgencySysId) {
$select->where('tbl.AgencySysId = ?', $AgencySysId);
}
if ($IsB2C == 1) {
$select->where('tbl.IsB2BAgent = ?', 0);
}
$result = $this->db->fetchAll($select);
return $result;
}
public function getAgencyList($IsB2C = NULL) {
$agencyColumn = array('AgencySysId', 'PrimaryEmail', 'PrimaryContactName', 'PrimaryContactNo', 'Title', 'DisplayName', 'EmailKey', 'EmailIdForCustomer');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency"), $agencyColumn);
if ($IsB2C == 1) {
$select->where('tbl.MasterAgencySysId = ?', 0);
}
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.IsApproved = ?', 1);
$select->where('tbl.IsMarkForDelete = ?', 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function getAgencyListForMoringAndEveningFolloUP($IsB2C = NULL) {
$agencyColumn = array('AgencySysId', 'PrimaryEmail', 'PrimaryContactName', 'PrimaryContactNo', 'Title', 'DisplayName', 'EmailKey', 'EmailIdForCustomer');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency"), $agencyColumn);
$select->joinLeft(array('tb2' => 'TB_Agency_Query_Acknowledgement'), 'tbl.AgencySysId=tb2.AgencySysId', array(''));
if ($IsB2C == 1) {
$select->where('tbl.MasterAgencySysId = ?', 0);
}
$select->where('tbl.IsActive = ?', 1);
$select->where('tb2.IsActive = ?', 1);
$select->where('tb2.QueryAcknowledgementSysId = ?', 17);
$select->where('tbl.IsApproved = ?', 1);
$select->where('tbl.IsMarkForDelete = ?', 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function checkAgencyWiseMail($AgencySysId, $QueryAcknowledgementSysId) {
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Query_Acknowledgement"));
$select->where('tbl.AgencySysId = ?', $AgencySysId);
$select->where('tbl.QueryAcknowledgementSysId = ?', $QueryAcknowledgementSysId);
$result = $this->db->fetchAll($select);
return $result;
}
public function checkExisting($UserRole, $AgencySysId) {
$statement = $this->db->query("GET_All_ReportingToStaff $UserRole, $AgencySysId");
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
//echo "<pre>";print_r($finalResultSet);exit;
return $finalResultSet;
}
public function checkExistingStaffDetail($UserSysId, $AgencySysId, $type = 'id') {
$statement = $this->db->query("GET_All_ReportingToStaff $UserSysId, $AgencySysId");
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$UserSysIdArr = $UserNameArr = array();
if ($finalResultSet['Result1']) {
foreach ($finalResultSet['Result1'] as $value) {
if ($type == 'nameid') {
if (trim($value['FirstName']) != '') {
$UserNameArr[] = trim($value['FirstName']) . ' ' . trim($value['LastName']);
}
$UserSysIdArr[] = $value['UserSysId'];
} else if ($type == 'name') {
if (trim($value['FirstName']) != '') {
$UserSysIdArr[] = trim($value['FirstName']) . ' ' . trim($value['LastName']);
}
} else {
$UserSysIdArr[] = $value['UserSysId'];
}
}
}
if ($type == 'nameid') {
return !empty($UserSysIdArr) ? array('id' => implode(',', $UserSysIdArr), 'name' => implode(',', $UserNameArr)) : '';
} else {
return !empty($UserSysIdArr) ? implode(',', $UserSysIdArr) : '';
}
}
public function getAgencyStaffListUserRolewithReport($isActive = NULL, $StaffUserId = NULL, $AgencySysId = NULL, $IsB2BAgent = 0, $UserRole = 0) {
$roleTitle = unserialize(AGENCY_USER_ROLE);
$whereCondition = " 1 = 1 ";
if ($StaffUserId > 0) {
$RoleId = $this->checkExisting($StaffUserId, $AgencySysId);
//echo "<pre>";print_r($RoleId);exit;
$checkArrayList = $StaffUserId;
}
if (!empty($RoleId['Result1'])) {
foreach ($RoleId['Result1'] as $valRolId) {
$checkArrayList .= ',' . $valRolId['UserSysId'];
}
}
// echo $UserRole; exit;
//// AND RoleTitle NOT IN ('" . $roleTitle[0] . "','" . $roleTitle[1] . "') ";
// if ($RoleId['IsAbleToManageMember']==1) {
// $whereCondition .= " AND TBAUR.IsAdmin = 1";
// }
// else {
// $whereCondition .= " AND TBAUR.IsAdmin = '0'";
// }
if ($IsB2BAgent == 0 && $AgencySysId != '') {
$whereCondition .= " AND (TBAUR.IsAbleToManageMember = 1 OR IsSuperAdmin = 1) ";
$whereCondition .= " AND TBAU.AgencySysId = " . @$AgencySysId;
}
if ($IsB2BAgent == 1 && $AgencySysId != '') {
//$whereCondition .= " AND TBAU.IsB2BAgent = " . $IsB2BAgent;
$whereCondition .= " AND TBAU.AgencySysId = " . @$AgencySysId;
}
if ($isActive) {
$whereCondition .= " AND TBAU.IsActive = " . $isActive;
}
if ($StaffUserId) {
$whereCondition .= " AND TBAU.UserSysId NOT IN ( " . $checkArrayList . " )";
}
$whereCondition .= " AND TBAU.IsMarkForDel = 0";
$select = $this->db->select()
->from(array("TBAU" => "TB_Agency_User"))
->joinLeft(array('TBAUR' => 'TB_Agency_UserRoles'), 'TBAU.UserRole=TBAUR.RoleSysId', array('TBAUR.RoleTitle'))
// ->joinLeft(array('TBADU' => 'TB_Agency_DeptUserMap'), 'TBADU.UserSysId=TBAU.UserSysId AND ReportingToSysId <> '.$StaffUserId.' ', array())
//->joinLeft(array('TBAD' => 'TB_Agency_Dept'), 'TBAD.DeptSysId=TBADU.DeptSysId', array('TBAD.DeptTitle'))
// ->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBAU.CountrySysId', array('TBMGCO.Title as CountryName'))
->where($whereCondition)
->order("FirstName ASC");
//echo $select; exit;
$result = $this->db->fetchAll($select);
// echo '<pre>'.$whereCondition; print_r($result);die;
return $result;
}
public function checkRoleId($UserRole) {
$select = $this->db->select()
->from(array("TBAUR" => "TB_Agency_UserRoles"), array("IsAbleToManageMember"))
->where("TBAUR.RoleSysId = $UserRole");
$result = $this->db->fetchRow($select);
return $result;
}
public function checkOps($DeptSysId) {
$agencyUserColumn = array("num" => "COUNT(*)");
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Dept"), $agencyUserColumn);
$select->where('tbl.DeptSysId = ?', $DeptSysId);
$select->where('tbl.IsOps = ?', 1);
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.IsMarkForDel = ?', 0);
// echo $select; exit;
$result = $this->db->fetchRow($select);
return $result['num'];
}
public function addDeptPlanMap($staffDepartment, $isOps, $userSysId) {
$opsArray = array();
$this->updateDeptPlanMap($staffDepartment, $userSysId);
foreach ($isOps as $key => $val) {
$opsArray['UserSysId'] = (int) $userSysId;
$opsArray['DeptSysId'] = (int) $staffDepartment;
$opsArray['PlanSysId'] = (int) $val;
$opsArray['IsActive'] = true;
$opsArray['IsMarkForDelete'] = false;
$this->db->insert('TB_Agency_DeptPlanMap', $opsArray);
}
}
public function updateDeptPlanMap($staffDepartment, $userSysId) {
$dbtable = new Zend_Db_Table('TB_Agency_DeptPlanMap');
$where = array("DeptSysId=?" => $staffDepartment, "UserSysId=?" => $userSysId);
return $dbtable->delete($where);
}
public function getOpsData($UserSysId, $DeptSysId) {
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_DeptPlanMap"));
$select->where('tbl.DeptSysId = ?', $DeptSysId);
$select->where('tbl.UserSysId = ?', $UserSysId);
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.IsMarkForDelete = ?', 0);
// echo $select; exit;
$result = $this->db->fetchAll($select);
$returnArray = array();
foreach ($result as $val) {
$returnArray[] = $val['PlanSysId'];
}
return $returnArray;
}
public function getOperationDeptPlanMapData($AgencySysId, $planType = 0, $AgentSysId = 0) {
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_User"), array('EmailId', 'FirstName', 'LastName', 'Signature', 'ContactNo1', 'SignatureType'));
$select->joinLeft(array('tb2' => 'TB_Agency_DeptPlanMap'), 'tbl.UserSysId=tb2.UserSysId', array('UserSysId'));
$select->where('tbl.AgencySysId = ?', $AgencySysId);
if ($planType > 0) {
$select->where('tb2.PlanSysId = ?', $planType);
}
if ($AgentSysId > 0) {
$select->where('tbl.UserSysId = ?', $AgentSysId);
}
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.IsMarkForDel = ?', 0);
//echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getBithDayData() {
$mDate = date('m');
$dDate = date('d');
$notMatchDate = '1900-01-01';
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer"), array('FirstName', 'LastName', 'EmailId', 'AgencySysId'));
$select->joinLeft(array('tb2' => 'TB_Agency_User'), 'tbl.AgencySysId=tb2.AgencySysId', array('Signature'));
$select->joinLeft(array('tb3' => 'TB_Agency'), 'tbl.AgencySysId=tb3.AgencySysId', array('Title', 'EmailKey', 'Title', 'PrimaryEmail'));
$select->where('CONVERT(date,tbl.DOB) != ?', $notMatchDate);
$select->where('MONTH(tbl.DOB) = ?', $mDate);
$select->where('DAY(tbl.DOB) = ?', $dDate);
$select->where('tbl.IsB2BAgent = ?', 0);
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.IsMarkForDelete = ?', 0);
$select->where('tb2.IsActive = ?', 1);
$select->where('tb2.IsMarkForDel = ?', 0);
$select->where('tb3.IsActive = ?', 1);
$select->where('tb3.IsMarkForDelete = ?', 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function getAnniversaryData() {
$mDate = date('m');
$dDate = date('d');
$notMatchDate = '1900-01-01';
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer"), array('FirstName', 'LastName', 'EmailId', 'AgencySysId'));
$select->joinLeft(array('tb2' => 'TB_Agency_User'), 'tbl.AgencySysId=tb2.AgencySysId', array('Signature'));
$select->joinLeft(array('tb3' => 'TB_Agency'), 'tbl.AgencySysId=tb3.AgencySysId', array('Title', 'EmailKey', 'Title', 'PrimaryEmail'));
$select->where('CONVERT(date,tbl.MarriageAnniversary) != ?', $notMatchDate);
$select->where('MONTH(tbl.MarriageAnniversary) = ?', $mDate);
$select->where('DAY(tbl.MarriageAnniversary) = ?', $dDate);
$select->where('tbl.IsB2BAgent = ?', 0);
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.IsMarkForDelete = ?', 0);
$select->where('tb2.IsActive = ?', 1);
$select->where('tb2.IsMarkForDel = ?', 0);
$select->where('tb3.IsActive = ?', 1);
$select->where('tb3.IsMarkForDelete = ?', 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function getTodayLoginAgencyData() {
$currentDate = date('Y-m-d');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_User"), array('FirstName', 'LastName'));
$select->joinLeft(array('tb2' => 'TB_Agency'), 'tbl.AgencySysId=tb2.AgencySysId', array('Title'));
$select->where('convert(date, tbl.LastLoginTime) = ?', $currentDate);
$result = $this->db->fetchAll($select);
return $result;
}
public function checkLast3DaysLoginAgencyData() {
$statement = $this->db->query("CHECK_LAST3DAYS_LOGIN_AGENCYUSER");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
return $finalResultSet;
}
public function getReportingUser($AgencySysId) {
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency"), array());
$select->joinLeft(array('tb2' => 'TB_Agency_User'), 'tbl.AgencySysId=tb2.AgencySysId', array());
$select->joinLeft(array('tb3' => 'TB_Agency_DeptUserMap'), 'tb2.UserSysId=tb3.UserSysId', array('ReportingToSysId'));
$select->where('tbl.AgencySysId = ?', $AgencySysId);
$result = $this->db->fetchRow($select);
$returnVal = 0;
if (is_array($result)) {
$returnVal = isset($result['ReportingToSysId']) ? $result['ReportingToSysId'] : 0;
}
return $returnVal;
}
public function getReportingUserByUserId($AgencySysId, $UserSysId) {
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_DeptUserMap"), array());
$select->joinLeft(array('tb2' => 'TB_Agency_User'), 'tbl.ReportingToSysId=tb2.UserSysId', array('*'));
$select->where('tbl.UserSysId = ?', $UserSysId);
$result = $this->db->fetchAll($select);
return $result;
}
public function getDMCSupplierList($AgencySysId) {
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency"), array('SecurityKey', 'Title'));
$select->where('tbl.MasterAgencySysId = ?', $AgencySysId);
$select->where('tbl.AgencyType = ?', 2);
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.IsMarkForDelete = ?', 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function getMasterSecurityKey($AgencySysId) {
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency"), array('SecurityKey'));
$select->where('tbl.AgencySysId = ?', $AgencySysId);
//$select->where('tbl.AgencyType = ?', 2);
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.IsMarkForDelete = ?', 0);
$result = $this->db->fetchRow($select);
return isset($result['SecurityKey']) ? $result['SecurityKey'] : '';
}
public function getAgencySysIdBySecurityKey($SecurityKey) {
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency"), array('AgencySysId'));
$select->where('tbl.SecurityKey = ?', $SecurityKey);
$result = $this->db->fetchRow($select);
return $result;
}
public function checkAgencyDestinationExpertSearchString($AgencySysId, $DestinationSysId, $IsMarkForDelete = '') {
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Destination_Expert_Search_String"), array('Id'));
$select->where('tbl.AgencySysId = ?', $AgencySysId);
$select->where('tbl.DestinationSysId = ?', $DestinationSysId);
$result = $this->db->fetchRow($select);
return $result;
}
public function getAgencyDestinationExpertSearchString($AgencySysId) {
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Destination_Expert_Search_String"), array('DestinationSysId'));
$select->where('tbl.AgencySysId = ?', $AgencySysId);
$select->where('tbl.IsMarkForDelete = ?', 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function addUpdateAgencyDestinationExpertSearchString($geographyRegion, $AgencySysId) {
$updatedata = ['IsMarkForDelete' => 1];
$updatewhere = array('AgencySysId =?' => $AgencySysId);
$isChanged = $this->db->update('TB_Agency_Destination_Expert_Search_String', $updatedata, $updatewhere);
//echo "<pre>";print_r($isChanged);die;
if (!empty($geographyRegion)) {
foreach ($geographyRegion as $key => $value) {
$check = $this->checkAgencyDestinationExpertSearchString($AgencySysId, $value);
$data = [
'AgencySysId' => $AgencySysId,
'DestinationSysId' => $value,
'UpdatedDate' => date('Y-m-d'),
'CreatedDate' => date('Y-m-d'),
'IsActive' => 1,
'IsMArkForDelete' => 0,
];
if (!empty($check)) {
$where = array('Id =?' => $check['Id']);
$isChanged = $this->db->update('TB_Agency_Destination_Expert_Search_String', $data, $where);
} else {
$this->db->insert('TB_Agency_Destination_Expert_Search_String', $data);
}
}
}
return 1;
}
public function getAgencyWiseQueryAcknowledgement($AgencySysId, $FieldName = array()) {
$result = array();
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Query_Acknowledgement"), array('QueryAcknowledgementSysId'));
$select->joinLeft(array('tb2' => 'TB_Master_Query_Acknowledgement'), 'tbl.QueryAcknowledgementSysId=tb2.QueryAcknowledgementSysId', array('FieldName'));
$select->where('tbl.AgencySysId = ?', $AgencySysId);
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.IsMarkForDel = ?', 0);
$select->where('tb2.IsActive = ?', 1);
$select->where('tb2.IsMarkForDel = ?', 0);
if (!empty($FieldName)) {
$select->where('tb2.FieldName IN (?)', $FieldName);
}
$resultArray = $this->db->fetchAll($select);
if (!empty($resultArray)) {
foreach ($resultArray as $value) {
$result[$value['QueryAcknowledgementSysId']] = $value['FieldName'];
}
}
return $result;
}
public function getUserSalesTargetData($StaffId, $AgencySysId, $targetyear) {
$select = $this->db->select();
$select->from(array('tbl' => "Tb_Agency_User_Target"));
$select->where('tbl.UserSysId = ?', $StaffId);
$select->where('tbl.TargetYear = ?', $targetyear);
$select->where('tbl.AgencySysId = ?', $AgencySysId);
$select->where('tbl.IsMarkForDelete = ?', 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function CheckSalesTargetBYYear($Year, $StaffId, $TargetSysId) {
$select = $this->db->select();
$select->from(array('tbl' => "Tb_Agency_User_Target"));
$select->where('tbl.TargetSysId = ?', $TargetSysId);
$select->where('tbl.UserSysId = ?', $StaffId);
$select->where('tbl.TargetYear = ?', $Year);
$select->where('tbl.IsMarkForDelete = ?', 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function getAllUsers($AgencySysId) {
$select = $this->db->select();
$select->from(array('tbl' => "Tb_Agency_User"), array("UserSysId", "FirstName", "LastName"));
$select->where('tbl.AgencySysId = ?', $AgencySysId);
$select->where('tbl.IsMarkForDel = ?', 0);
$select->where('tbl.IsActive = ?', 1);
$result = $this->db->fetchAll($select);
return $result;
}
public function getFranchiseSlabs($AgencySysId) {
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Franchise"), array("MarketPlaceSysId", "Title", "CommissionType", "CommissionValue"));
$select->where('tbl.AgencySysId = ?', $AgencySysId);
$select->where('tbl.IsMarkForDelete = ?', 0);
$select->where('tbl.IsActive = ?', 1);
$select->where('tbl.MasterId = ?', 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function getFranchisePrimaryUserData($agencySysId, $primaryUserSysId) {
$select = $this->db->select();
$select->from(array("TBAU" => "TB_Agency_User"), array('FirstName', 'LastName', 'UserSysId', 'countrycode2', 'signatureType', 'OtherEmail', 'ContactNo2', 'Signature'));
$select->where("TBAU.AgencySysId =?", $agencySysId);
$select->where("TBAU.UserSysId =?", $primaryUserSysId);
$result = $this->db->fetchRow($select);
return $result;
}
public function getFranchisePrimaryUserByAgencyId($agencySysId) {
$select = $this->db->select();
$select->from(array("TBAU" => "TB_Agency_User"), array('FirstName', 'LastName', 'UserSysId', 'countrycode2', 'signatureType', 'OtherEmail', 'ContactNo2', 'Signature'));
$select->joinLeft(array('TB' => 'TB_Agency'), 'TB.PrimaryUserSysId=TBAU.UserSysId', array('PrimaryUserSysId'));
$select->where("TBAU.AgencySysId =?", $agencySysId);
// echo $select; die('data');
$result = $this->db->fetchRow($select);
return $result;
}
public function addfranchiseAddress($dataArr = array()) {
try {
$this->db->insert('TB_Agency_Addresses', $dataArr);
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
}
public function editfranchiseAddress($dataArr, $AgencySysId) {
$where = array("AgencySysId=?" => $AgencySysId);
$this->db->update('TB_Agency_Addresses', $dataArr, $where);
}
public function checkAdressByAgencyId($agencySysId) {
$select = $this->db->select();
$select->from(array("TB_Agency_Addresses"));
$select->where("AgencySysId =?", $agencySysId);
// echo $select; die('data');
$result = $this->db->fetchRow($select);
return $result;
}
public function getUserDataByUserIdFranchise($userId) { //SpeakingLangSysId
$select = $this->db->select()
->from(array("TBAU" => "TB_Agency_User"))
->joinLeft(array('TBAUR' => 'TB_Agency_UserRoles'), 'TBAUR.RoleSysId=TBAU.UserRole', array('TBAUR.RoleTitle'))
->joinLeft(array('TBML' => 'TB_Master_Lang'), 'TBML.LangSysId=TBAU.SpeakingLangSysId', array('TBML.TitleEng'))
->joinLeft(array('TBADU' => 'TB_Agency_DeptUserMap'), 'TBADU.UserSysId=TBAU.UserSysId', array('TBADU.DeptSysId', 'TBADU.IsActive as TBADUIsActive', 'TBADU.ReportingToSysId'))
->joinLeft(array('TBA' => 'TB_Agency_User'), 'TBA.UserSysId=TBADU.ReportingToSysId', array('TBAU.FirstName as RFirstName', 'TBAU.LastName as RLastName'))
->joinLeft(array('TBAD' => 'TB_Agency_Dept'), 'TBAD.DeptSysId=TBADU.DeptSysId', array('TBAD.DeptTitle', 'IsOps'))
->joinLeft(array('TBAA' => 'TB_Agency_Addresses'), 'TBAA.AgencySysId=TBAU.AgencySysId', array('Address as FranchiseAddress', 'Pincode as FranchisePincode', 'CitySysId as FranchiseCitySysId', 'CountrySysId as FranchiseCountrySysId'))
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBAA.CountrySysId', array('TBMGCO.Title as CountryName'))
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBAA.CitySysId', array('TBMGC.Title as CityName'))
->joinLeft(array('TB' => 'TB_Agency'), 'TB.AgencySysId=TBAU.AgencySysId', array('TB.AgencyMarketPlaceSysId', 'TB.Title', 'TB.AdharCardNo', 'TB.MasterAgencySysId', 'TB.IsVarified', 'TB.IsMobileVerified', 'Logo as UserAgencyLogo', 'BusinessType', 'FirmType', 'IsPaid', 'DistributorId', 'URL', 'PrimaryUserSysId', 'RMUserSysId', 'DistributorCommission'))
->joinLeft(array('TBAC' => 'TB_Agency_Customer'), 'TBAU.UserSysId=TBAC.CreatedByUserSysId', array('CustomerSysId', 'LeadSourceSysId', 'DOB'))
->joinLeft(array('TBMLS' => 'TB_Master_LeadSource'), 'TBMLS.LeadSourceSysId=TBAC.LeadSourceSysId', array('TBMLS.Title as LeadSource'))
->joinLeft(array('TBAM' => 'TB_Agency_Mapping'), 'TBAM.AgencySysId=TB.AgencySysId', array('TBAM.IsAadharVerified', 'TBAM.IsPANVerified', 'TBAM.IsGSTVerified'))
->where("TBAU.UserSysId =?", $userId);
$result = $this->db->fetchRow($select);
return $result;
}
public function getMasterUserPermissionContent() { //SpeakingLangSysId
$select = $this->db->select()
->from(array("TBAU" => "TB_Master_User_Permission_Content"))
// ->where("TBAU.AgentSysId =?", $userId)
->where("TBAU.IsActive =?", 1);
// ->where("TBAU.IsMarkForDelete =?", 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function getMasterUserPermissionselectedContent($userId) { //SpeakingLangSysId
$select = $this->db->select()
->from(array("TBAU" => "TB_Agency_UserRole_Permission_Content"))
// ->where("TBAU.AgencySysId =?", $AgencySysId)
->where("TBAU.AgentSysId =?", $userId)
->where("TBAU.IsActive =?", 1)
->where("TBAU.IsMarkForDelete =?", 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function getMasterUserPermissionselectedContentId($userId) { //SpeakingLangSysId
$select = $this->db->select()
->from(array("TBAU" => "TB_Agency_UserRole_Permission_Content"), array('UserPermissionSysId', 'IsActive'))
// ->where("TBAU.AgencySysId =?", $AgencySysId)
->where("TBAU.AgentSysId =?", $userId)
->where("TBAU.IsActive =?", 1);
// ->where("TBAU.IsMarkForDelete =?", 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function getAgencyMappingStaff($agencySysId) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency_Mapping"), array("PlanType", "NoOfUser"));
$select->where("tb1.AgencySysId = ?", $agencySysId);
$result = $this->db->fetchRow($select);
return $result;
}
public function getAgencyStaffCount($AgencySysId = NULL) {
$whereCondition = " 1 = 1 ";
if ($AgencySysId) {
$whereCondition .= " AND TBAU.AgencySysId = " . $AgencySysId;
}
$whereCondition .= " AND TBAU.IsActive = 1";
$whereCondition .= " AND TBAU.IsMarkForDel = 0";
$select = $this->db->select()
->from(array("TBAU" => "TB_Agency_User"), array(new Zend_Db_Expr('COUNT(*) as record_count')))
->where($whereCondition);
$result = $this->db->fetchRow($select);
return $result['record_count'];
}
}