| 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
class Travel_Model_TblSupplier {
private $db = NULL;
private $baseUrl = '';
public $intId = NULL;
public $SupplierTypeSysId = NULL;
public $SupplierSysId = NULL;
public $orderBy = 'asc';
public $orderByField = 'TB_IC_Supplier.SupplierName';
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 getSupplierTypes() {
$sql = " SELECT * from TB_Master_SupplierType ORDER BY Title ";
return $this->db->query($sql)->fetchAll();
}
public function getSuppliers() {
$sql = " SELECT SupplierSysId ,TB_IC_Supplier.SupplierTypeSysId, TB_Master_SupplierType.Title as SupplierType, FacilityMask ,SupplierName
,ContactPerson,PrefCurrencyType,TB_Master_Currency.Title as Currency, Designation,ContactNo
,EmailId,Address,CitySysId,ContId,StateId,PlaceSysId,TB_Master_Geo_City.Title as CityName
,IsCreatedByAgent,AgentSysId,TB_IC_Supplier.UpdatedDate,TB_IC_Supplier.CreateDate
,TB_IC_Supplier.ApproveDate, TB_IC_Supplier.IsApproved, TB_IC_Supplier.IsDelete,TB_IC_Supplier.IsActive
FROM TB_IC_Supplier LEFT JOIN TB_MASTER_CURRENCY
ON TB_IC_Supplier.PrefCurrencyType = TB_MASTER_CURRENCY.CurrencyType
LEFT JOIN TB_Master_Geo_City ON TB_IC_Supplier.CitySysId = TB_Master_Geo_City.CityId
LEFT JOIN TB_Master_Geo_State ON TB_IC_Supplier.StateId = TB_Master_Geo_State.StateId
INNER JOIN TB_Master_SupplierType ON TB_IC_Supplier.SupplierTypeSysId = TB_Master_SupplierType.SupplierTypeSysId ";
if (!empty($this->SupplierTypeSysId)) {
$sql .= " AND TB_IC_Supplier.SupplierTypeSysId = " . $this->SupplierTypeSysId;
}
if (!empty($this->SupplierSysId)) {
$sql .= " AND TB_IC_Supplier.SupplierSysId = " . $this->SupplierSysId;
}
$sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function checkSupplierNameByAgentIdAndSupplierType($AgentSysId, $EmailId) {
$sql = " SELECT SupplierSysId,SupplierName FROM TB_IC_Supplier WHERE AgentSysId='" . $AgentSysId . "' and EmailId='" . $EmailId . "' ORDER BY SupplierName";
return $this->db->query($sql)->fetchAll();
}
public function getSupplierList($lastInsertId) {
$sql = " SELECT SupplierSysId,SupplierName,Services FROM TB_IC_Supplier WHERE SupplierSysId='" . $lastInsertId . "' ORDER BY SupplierName";
return $this->db->query($sql)->fetch();
}
public function addSupplier($data) {
try {
$this->db->insert('TB_IC_Supplier', $data);
return $this->db->lastInsertId('TB_IC_Supplier');
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
}
public function getSupplierAutoSearchList($strSupplierName, $AgentId, $SupplierTypeSysId = NULL, $agencyId = NULL, $strtype = NULL) {
$customHelper = Zend_Controller_Action_HelperBroker::getStaticHelper('Custom');
$AgencyUser = new Zend_Session_Namespace('AgencyUser');
$IsMaskingOnEmailAndMobile = $AgencyUser->user->IsMaskingOnEmailAndMobile;
$TrxCurrency = $AgencyUser->user->intLoggedinUserTrxCurrency;
$sql = "Select Min(SupplierSysId) AS SupplierSysId, EmailId, SupplierName,CurrencyType From TB_IC_Supplier where 1 = 1 "; //AND IsActive = 1 AND IsApproved = 1 AND IsDelete = 0 ";
if (!empty($strSupplierName)) {
$sql .= " AND (SupplierName LIKE '%$strSupplierName%' OR EmailId LIKE '%$strSupplierName%') ";
}
if (!empty($agencyId)) {
$sql .= " AND XRefAgencySysId= '" . $agencyId . "' ";
}
$sql .= " AND IsActive = 1 AND IsDelete = 0";
// if (!empty($AgentId)) {
// $sql .= " AND AgentSysId= '" . $AgentId . "' ";
// }
$sql .= " GROUP BY EmailId, SupplierName, CurrencyType ";
$sql .= " ORDER BY SupplierName ";
// echo $sql; die;
$rowset = $this->db->query($sql)->fetchAll();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
if ($row['EmailId']) {
$email = ' (' . $customHelper->hideEmailidCharacters($row['EmailId'], $IsMaskingOnEmailAndMobile) . ')';
} else {
$email = '';
}
$dataName = $row['SupplierName'];
$dataEmail = $row['SupplierName'] . $email;
$CurrencyType = ((int)$row['CurrencyType'] > 0) ? (int)$row['CurrencyType'] : $TrxCurrency;
$response[] = array('SupplierSysId' => $row['SupplierSysId'], 'CurrencyType' => $CurrencyType, 'value' => $dataName, 'label' => $dataEmail);
}
} else {
if (trim($strtype) == 'nonew') {
$response[] = array('SupplierSysId' => 0, 'CurrencyType' => 0, 'value' => 'Not Found', 'label' => 'Not Found');
} else {
$response[] = array('SupplierSysId' => 0, 'CurrencyType' => 0, 'value' => 'Add New Supplier', 'label' => 'Add New Supplier');
}
}
return $response;
}
public function getSupplierAutoSearchListFullfillment($strSupplierName, $AgentId, $SupplierTypeSysId = NULL, $agencyId = NULL, $strtype = NULL) {
$sql = "Select Min(TB_IC_Supplier.SupplierSysId) AS SupplierSysId, TB_IC_Supplier.EmailId, TB_IC_Supplier.SupplierName, TB_IC_Supplier.StateId, TB_IC_Supplier.PrefCurrencyType, TB_IC_Supplier.ContactNo, TB_Master_Geo_State.Title as StateName From TB_IC_Supplier JOIN TB_Master_Geo_State ON TB_IC_Supplier.StateId = TB_Master_Geo_State.StateId where 1 = 1 "; //AND IsActive = 1 AND IsApproved = 1 AND IsDelete = 0 ";
if (!empty($strSupplierName)) {
$sql .= " AND (TB_IC_Supplier.SupplierName LIKE '%$strSupplierName%' OR TB_IC_Supplier.EmailId LIKE '%$strSupplierName%') ";
}
if (!empty($agencyId)) {
$sql .= " AND TB_IC_Supplier.XRefAgencySysId= '" . $agencyId . "' ";
}
$sql .= " AND TB_IC_Supplier.IsActive = 1 AND TB_IC_Supplier.IsDelete = 0";
// if (!empty($AgentId)) {
// $sql .= " AND AgentSysId= '" . $AgentId . "' ";
// }
$sql .= " GROUP BY TB_IC_Supplier.EmailId, TB_IC_Supplier.SupplierName, TB_IC_Supplier.PrefCurrencyType, TB_IC_Supplier.StateId, TB_IC_Supplier.ContactNo, TB_Master_Geo_State.Title ";
$sql .= " ORDER BY TB_IC_Supplier.SupplierName ";
// echo $sql; die;
$rowset = $this->db->query($sql)->fetchAll();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
if ($row['EmailId']) {
$email = ' (' . $row['EmailId'] . ')';
} else {
$email = '';
}
$dataName = $row['SupplierName'];
$dataEmail = $row['SupplierName'] . $email;
$response[] = array('SupplierSysId' => $row['SupplierSysId'], 'PrefCurrencyType' => $row['PrefCurrencyType'], 'value' => $dataName, 'label' => $dataEmail, 'Email' => $row['EmailId'], 'StateName' => $row['StateName'], 'ContactNo' => $row['ContactNo'], 'StateId' => $row['StateId']);
}
} else {
if (empty($strtype)) {
$response[] = array('SupplierSysId' => 0, 'PrefCurrencyType' => 0, 'value' => 'Add New Supplier', 'label' => 'Add New Supplier', 'Email' => '', 'StateName' => '', 'ContactNo' => '', 'StateId' => '');
}
}
return $response;
}
public function getAutoSuggest($keyword) {
$keyword = trim($keyword);
$response = array();
$sql = " SELECT SupplierSysId, SupplierName FROM TB_IC_Supplier WHERE 1 = 1 ";
$sql .= " AND TB_IC_Supplier.SupplierName LIKE '" . $keyword . "%' ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$rowset = $this->db->query($sql)->fetchAll();
$response = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
$response[] = array('value' => $row['SupplierSysId'], 'label' => $row['SupplierName']);
}
}
$response[] = array('value' => -1, 'label' => 'Add New');
;
return $response;
}
public function getSuppliersListForSupport($agencySysId) {
$whereCondition = " XRefAgencySysId = 1903";
$whereCondition .= " AND ( SupplierAgencySysId = '$agencySysId')";
$select = $this->db->select()
->from("TB_IC_Supplier")
->where($whereCondition)
->where("TB_IC_Supplier.IsActive=?", 1)
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TB_IC_Supplier.ContId', array('TBMGCO.Title as CountryName'))
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TB_IC_Supplier.CitySysId', array('TBMGC.Title as CityName'))
->order("SupplierName ASC");
$result = $this->db->fetchAll($select);
return $result;
}
public function getSuppliersListBySupplierId($SupplierId) {
$whereCondition = " SupplierSysId = '$SupplierId'";
$select = $this->db->select()
->from("TB_IC_Supplier")
->where($whereCondition)
->where("TB_IC_Supplier.IsActive=?", 1)
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TB_IC_Supplier.ContId', array('TBMGCO.Title as CountryName'))
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TB_IC_Supplier.CitySysId', array('TBMGC.Title as CityName'))
->order("SupplierName ASC");
// echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getSuppliersList($intLoggedinUserId, $agencySysId) {
$request = Zend_Controller_Front::getInstance()->getRequest();
if ($request->isPost()) {
$post = $request->getPost();
}
//print_r($post);
$whereCondition = " XRefAgencySysId = '$agencySysId'";
//$whereCondition .= " AND IsActive = '1'";
if (isset($post['companyName']) && !empty($post['companyName'])) {
$supplierName = $post['companyName'];
$whereCondition .= " AND ( SupplierName = '" . $supplierName . "' OR CompanyName = '" . $supplierName . "' OR SupplierName LIKE '" . $supplierName . "%' OR CompanyName LIKE '" . $supplierName . "%' ) ";
}
if (isset($post['supplierservices']) && !empty($post['supplierservices'])) {
$supplierservices = $post['supplierservices'];
$whereCondition .= " AND ( ',' + ServicesType + ',' like '%,' + '$supplierservices' + ',%' )";
}
// if(isset($post['hidden_city_id']) && !empty($post['hidden_city_id'])) {
// $Destination = $post['hidden_city_id'];
// $whereCondition .= " AND CitySysId =".$Destination;
// }
if (isset($post['hidden_city_id']) && !empty($post['hidden_city_id'])) {
$hidden_city_id = $post['hidden_city_id'];
$whereCondition .= " AND ( ',' + ExpertiseDestinationsId + ',' like '%,' + '$hidden_city_id' + ',%' )";
}
if (isset($post['hidden_location_id']) && !empty($post['hidden_location_id'])) {
$hidden_location_id = $post['hidden_location_id'];
$whereCondition .= " AND ( ',' + otherlocationcity + ',' like '%,' + '$hidden_location_id' + ',%' OR CitySysId = '$hidden_location_id' )";
}
if (isset($post['contactName']) && !empty($post['contactName'])) {
$contactName = $post['contactName'];
$whereCondition .= " AND ( ContactPerson = '$contactName' OR ContactPerson LIKE '$contactName%' )";
}
if (isset($post['phoneNo']) && !empty($post['phoneNo'])) {
$phoneNo = $post['phoneNo'];
$whereCondition .= " AND ( ContactNo = '$phoneNo' OR ContactNo LIKE '$phoneNo%' )";
}
if (isset($post['emailId']) && !empty($post['emailId'])) {
$emailId = $post['emailId'];
$whereCondition .= " AND ( EmailId = '$emailId' OR EmailId LIKE '$emailId%' )";
}
$select = $this->db->select()
->from(array("T1" => "TB_IC_Supplier"), array('*'))
->joinLeft(array('T2' => 'TB_Supplier_Contacts'), 'T2.SupplierSysId=T1.SupplierSysId AND T2.IsPrimary = 1', array('T2.supplierContactId'))
->where($whereCondition)
->where("T1.IsActive=?", 1)
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=T1.ContId', array('TBMGCO.Title as CountryName'))
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=T1.CitySysId', array('TBMGC.Title as CityName'))
->order("SupplierSysId DESC");
$result = $this->db->fetchAll($select);
//echo '<pre>';print_r($result);die;
return $result;
}
public function SearchSuppliersList($intLoggedinUserId, $agencySysId) {
$request = Zend_Controller_Front::getInstance()->getRequest();
if ($request->isPost()) {
$post = $request->getPost();
}
//echo '<pre>';print_r($post);echo '</pre>';
$whereCondition = " T2.XRefAgencySysId = '$agencySysId'";
if (isset($post['companyName']) && !empty($post['companyName'])) {
$supplierName = $post['companyName'];
$whereCondition .= " AND ( T2.SupplierName LIKE '%" . $supplierName . "%' OR T2.CompanyName LIKE '%" . $supplierName . "%' OR T2.SupplierName LIKE '%" . $supplierName . "%' OR T2.CompanyName LIKE '%" . $supplierName . "%' ) ";
}
if (isset($post['hidden_city_id']) && !empty($post['hidden_city_id'])) {
$hidden_city_id = $post['hidden_city_id'];
$contid = self::getCountryIdByctId($hidden_city_id);
//if($contid == 101){
$whereCondition .= " AND ( T2.CitySysId = '$hidden_city_id' OR ',' + T1.CitySysId + ',' like '%,' + '$hidden_city_id' + ',%' )";
//$whereCondition .= " AND ( ',' + T2.ExpertiseDestinationsId + ',' like '%,' + '$hidden_city_id' + ',%' OR ',' + T1.CitySysId + ',' like '%,' + '$hidden_city_id' + ',%' )";
//}else{
//$whereCondition .= " AND ( ',' + T2.ExpertiseDestinationsId + ',' like '%,' + '$hidden_city_id' + ',%' OR ',' + T1.CitySysId + ',' like '%,' + '$hidden_city_id' + ',%' OR ',' + T2.ExpertiseCountriesId + ',' like '%,' + '$contid' + ',%' OR ',' + T1.CountrySysId + ',' like '%,' + '$contid' + ',%' )";
//}
}
if (isset($post['city_id']) && !empty($post['city_id'])) {
$Expert_city_id = explode('__', $post['city_id']);
//$contid = self::getCountryIdByctId($hidden_city_id);
//if($contid == 101){
//$whereCondition .= " AND ( ',' + T2.ExpertiseDestinationsId + ',' like '%,' + '$hidden_city_id' + ',%' OR ',' + T1.CitySysId + ',' like '%,' + '$hidden_city_id' + ',%' )";
$whereCondition .= " AND ( ',' + T2.ExpertiseDestinationsId + ',' like '%,' + '$Expert_city_id[0]' + ',%' OR ',' + T1.CitySysId + ',' like '%,' + '$Expert_city_id[0]' + ',%')";
//}else{
// $whereCondition .= " AND ( ',' + T2.ExpertiseDestinationsId + ',' like '%,' + '$hidden_city_id' + ',%' OR ',' + T1.CitySysId + ',' like '%,' + '$hidden_city_id' + ',%' OR ',' + T2.ExpertiseCountriesId + ',' like '%,' + '$contid' + ',%' OR ',' + T1.CountrySysId + ',' like '%,' + '$contid' + ',%' )";
//}
}
if (isset($post['geographyCountry']) && !empty($post['geographyCountry'])) {
$geographyCountry = $post['geographyCountry'];
$whereCondition .= " AND ( ',' + T2.ExpertiseCountriesId + ',' like '%,' + '$geographyCountry' + ',%' OR ',' + T2.ExpertiseRegionId + ',' like '%,' + '$geographyCountry' + ',%' )";
}
if (isset($post['contactName']) && !empty($post['contactName'])) {
$contactName = $post['contactName'];
$whereCondition .= " AND ( T2.ContactPerson = '$contactName' OR T2.ContactPerson LIKE '$contactName%' OR T1.ContactPerson LIKE '$contactName%' )";
}
if (isset($post['supplierservices']) && !empty($post['supplierservices'])) {
$supplierservices = $post['supplierservices'];
$whereCondition .= " AND ( ',' + T2.ServicesType + ',' like '%,' + '$supplierservices' + ',%' )";
}
if (isset($post['phoneNo']) && !empty($post['phoneNo'])) {
$phoneNo = $post['phoneNo'];
$whereCondition .= " AND ( T2.ContactNo = '$phoneNo' OR T2.ContactNo LIKE '$phoneNo%' OR T1.PrimaryMobile LIKE '$phoneNo%' )";
}
if (isset($post['emailId']) && !empty($post['emailId'])) {
$emailId = $post['emailId'];
$whereCondition .= " AND ( T2.EmailId = '$emailId' OR T2.EmailId LIKE '$emailId%' OR T1.EmailId LIKE '$emailId%' )";
}
if (isset($post['hidden_location_id']) && !empty($post['hidden_location_id'])) {
$hidden_location_id = $post['hidden_location_id'];
$whereCondition .= " AND ( ',' + T2.otherlocationcity + ',' like '%,' + '$hidden_location_id' + ',%' OR T2.CitySysId = '$hidden_location_id' )";
}
if (isset($post['country_id']) && !empty($post['country_id'])) {
$country_id = $post['country_id'];
$whereCondition .= " AND ( ',' + T2.ExpertiseCountriesId + ',' like '%,' + '$country_id' + ',%' OR ',' + T2.ExpertiseRegionId + ',' like '%,' + '$country_id' + ',%' )";
}
$select = $this->db->select()
->from(array("T2" => "TB_IC_Supplier"), array(
'T2.*',
new Zend_Db_Expr("TBMGCO.Title AS CountryName"),
new Zend_Db_Expr("TBMGC.Title AS CityName"),
new Zend_Db_Expr("CASE WHEN (SELECT SUM(TotalAmount) AS TotalAmount FROM TB_Supplier_Wallet_history AS T22 WHERE T22.SupplierId = T2.SupplierSysId) > 0 THEN 1 ELSE 0 END AS WalletCheck")
))
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=T2.ContId', array())
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=T2.CitySysId', array())
->where($whereCondition)
->where("T2.IsActive = ?", 1)
->order("T2.SupplierSysId DESC");
// echo $select; exit;
return $select;
}
public function getTempSuppliersList() {
$select = $this->db->select()
->from("TB_IC_Supplier", array('SupplierSysId'))
->where("TB_IC_Supplier.IsActive=?", 1)
->where("TB_IC_Supplier.IsDelete=?", 0)
->order("SupplierSysId Desc");
return $result = $this->db->fetchAll($select);
}
public function getSupplierById($SupplierSysId = NULL) {
$select = $this->db->select()
->from(array("TBIS" => "TB_IC_Supplier"))
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBIS.ContId', array('TBMGCO.Title as CountryName'))
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBIS.CitySysId', array('TBMGC.Title as CityName'))
->joinLeft(array('TBMGS' => 'TB_Master_Geo_State'), 'TBMGS.StateId=TBIS.StateId', array('TBMGS.Title as StateName'))
->where("TBIS.SupplierSysId=?", $SupplierSysId);
$result = $this->db->fetchRow($select);
return $result;
}
public function getCountryIdByctId($city = null) {
$select = $this->db->select()
->from(array("a" => "TB_Master_Geo_City"), array('a.ContSysId as ContSysId'))
->where("a.CityId=?", $city);
$result = $this->db->fetchRow($select);
return $result['ContSysId'];
}
public function getCityNameByctId($cityId = null) {
$select = $this->db->select()
->from(array("a" => "TB_Master_Geo_City"), array('a.Title'))
->where("a.CityId=?", $cityId);
$result = $this->db->fetchRow($select);
return $result['Title'];
}
public function getCountryNameByctId($cityId = null) {
$select = $this->db->select()
->from(array("a" => "TB_Master_Geo_Country"), array('a.Title'))
->where("a.ContId=?", $cityId);
$result = $this->db->fetchRow($select);
return $result['Title'];
}
public function getSupplierServices() {
//$sql = " SELECT * from TB_Master_SupplierServices where isActive = 1 ORDER BY Title ";
$sql = " SELECT * from TB_Master_SupplierServices where isActive = 1";
return $this->db->query($sql)->fetchAll();
}
public function getSupplierServicesShort() {
$sql = " SELECT * from TB_Master_SupplierServices where isActive = 1 ORDER BY Title ";
return $this->db->query($sql)->fetchAll();
}
public function isSupplierEmailExists($intLoggedinUserAgencySysId, $email, $SupplierUserId = NULL) {
$whereCondition = "EmailId = '$email' AND XRefAgencySysId = '$intLoggedinUserAgencySysId'";
if ($SupplierUserId) {
$whereCondition .= " AND SupplierSysId != " . $SupplierUserId;
}
$select = $this->db->select()
->from("TB_IC_Supplier", array('count(*) AS totalRecords'))
->where($whereCondition)->where("IsDelete=?", 0);
$count = $this->db->fetchOne($select);
if ($count > 0) {
return true;
}
return false;
}
public function getSupplierEmailExists($intLoggedinUserAgencySysId, $email, $SupplierUserId = NULL) {
$whereCondition = "EmailId = '$email' AND XRefAgencySysId = '$intLoggedinUserAgencySysId'";
if ($SupplierUserId) {
$whereCondition .= " AND SupplierSysId != " . $SupplierUserId;
}
$select = $this->db->select()
->from("TB_IC_Supplier", array('SupplierSysId'))
->where($whereCondition)->where("IsDelete=?", 0);
$count = $this->db->fetchOne($select);
return $count;
}
public function isSupplierContactExists($email, $SupplierUserId) {
$select = $this->db->select()
->from("TB_Supplier_Contacts")
->where("EmailId=?", $email)
->where("SupplierSysId=?", $SupplierUserId)
->where("IsMarkForDelete=?", 0)->where("IsActive=?", 1);
$count = $this->db->fetchRow($select);
//print_r($count);die;
if ($count) {
return $count;
}
return false;
}
public function getSupplierContactExists($email, $SupplierUserId) {
$select = $this->db->select()
->from("TB_Supplier_Contacts", array('SupplierContactId'))
->where("EmailId=?", $email)
->where("SupplierSysId=?", $SupplierUserId)
->where("IsMarkForDelete=?", 0)->where("IsActive=?", 1);
$count = $this->db->fetchRow($select);
return $count;
}
/*
* setMasking. To create masking (need master data, result data and primary key of master table).
* Created by Pooja on 3aug2016
* @param array $maskingArr
* @param array $masterListingArr
* @param int $primaryKey
* @return int.
*/
public function setMasking($maskingArr, $masterListingArr, $primaryKey) {
$mask = '1';
if (count($masterListingArr) > 0) {
foreach ($masterListingArr as $maskVal) {
$char = in_array($maskVal[$primaryKey], $maskingArr) ? '1' : '0';
$mask .= $char;
}
}
return $mask;
}
/*
* getMasking(). To create masking (need master data, result data and primary key of master table).
* Created by Pooja on 4aug2016
* @param string $varfromDb
* @param array $masterListingArr
* @param int $primaryKey
* @return int.
*/
public function getMasking($varfromDb, $masterListingArr, $primaryKey) {
$varfromDbArr = str_split(trim($varfromDb));
$resultMask = array();
$counter = 1;
foreach ($masterListingArr as $value) {
if (@$varfromDbArr[$counter] == 1) {
$resultMask[$value[$primaryKey]] = $value[$primaryKey];
}
$counter++;
}
return $resultMask;
}
public function updateSupplier($dataForDb = array(), $SupplierSysId = NULL) {
//echo "<pre>";print_r($dataForDb);die;
$where = array('SupplierSysId =?' => $SupplierSysId);
$this->db->update('TB_IC_Supplier', $dataForDb, $where);
}
public function isSupplierContactEmailExists($email, $SupplierSysId, $SupplierContactId = NULL) {
$whereCondition = "EmailId = '$email'";
if ($SupplierSysId) {
$whereCondition .= " AND SupplierSysId = " . $SupplierSysId;
}
if ($SupplierContactId) {
$whereCondition .= " AND SupplierContactId != " . $SupplierContactId;
}
$select = $this->db->select()
->from("TB_Supplier_Contacts", array('count(*) AS totalRecords'))
->where($whereCondition);
$count = $this->db->fetchOne($select);
if ($count > 0) {
return true;
}
return false;
}
public function addSupplierContact($data) {
try {
$this->db->insert('TB_Supplier_Contacts', $data);
return $this->db->lastInsertId('TB_Supplier_Contacts');
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
}
public function addSupplierExCountry($data) {
try {
$this->db->insert('TB_IC_Supplier_Exper_Count', $data);
return $this->db->lastInsertId('TB_IC_Supplier_Exper_Count');
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
}
public function addSupplierContactCity($data) {
try {
$this->db->insert('TB_Supplier_Contacts_City', $data);
return $this->db->lastInsertId('TB_Supplier_Contacts_City');
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
}
public function updateSupplierContact($dataForDb = array(), $SupplierContactId = NULL) {
$where = array('SupplierContactId =?' => $SupplierContactId);
$this->db->update('TB_Supplier_Contacts', $dataForDb, $where);
}
public function getSuppliersContactList($SupplierSysId, $Order = 'DESC') {
$select = $this->db->select()
->from("TB_Supplier_Contacts")
->where("SupplierSysId=?", $SupplierSysId)
->where("IsActive=?", 1)
->where("IsMarkForDelete=?", 0)
->order("SupplierContactId $Order");
$result = $this->db->fetchAll($select);
return $result;
}
public function getSuppliersContactListPack__($CitySysId = NULL, $AgencySysId) {
$select = $this->db->select()
->from(array("T1" => "TB_Supplier_Contacts_City"))
->joinLeft(array('T2' => 'TB_Supplier_Contacts'), 'T2.SupplierContactId=T1.SupplierContactId', array('T2.Salution', 'T2.FirstName', 'T2.LastName', 'T2.EmailId', 'T2.PrimaryMobile'))
->joinLeft(array('T3' => 'TB_IC_Supplier'), 'T3.SupplierSysId=T1.SupplierSysId', array('T3.SupplierName', 'T3.EmailId as SupplierEmailId', 'T3.Services'))
->joinLeft(array('T4' => 'TB_Master_Geo_Country'), 'T3.ContId=T4.ContId', array('T4.Title as SupCountryName'))
->joinLeft(array('T5' => 'TB_Master_Geo_City'), 'T3.CitySysId=T5.CityId', array('T5.Title as SupCityName'))
->joinLeft(array('T6' => 'TB_Master_Geo_Country'), 'T1.CountrySysId=T6.ContId', array('T6.Title as ContCountryName'))
->joinLeft(array('T7' => 'TB_Master_Geo_City'), 'T1.CitySysId=T7.CityId', array('T7.Title as ContCityName'))
->where("T1.IsMarkForDelete=?", 0)
->where("T3.IsDelete=?", 0)
->where("T3.XRefAgencySysId=?", $AgencySysId)
->where("T1.CitySysId=?", $CitySysId)
->order("T2.ContactPerson ASC");
$result = $this->db->fetchAll($select);
//echo '<pre>';print_r($result);echo '</pre>';
return $result;
}
public function getSuppliersContactListPack($SearchString = NULL, $AgencySysId) { //echo $SearchString;die;
//$contid = self::getCountryIdByctId($CitySysId);
$whereCondition = " T2.IsMarkForDelete = '0' AND T3.IsDelete='0' AND T3.IsActive = 1 AND T3.XRefAgencySysId= '$AgencySysId'";
// if($contid == 101){
// $whereCondition .= " AND ( ',' + T2.CitySysId + ',' like '%,' + '$CitySysId' + ',%' OR ',' + T3.Expertisedestinationsid + ',' like '%,' + '$CitySysId' + ',%' )";
// }else{
// $whereCondition .= " AND ( ',' + T2.CitySysId + ',' like '%,' + '$CitySysId' + ',%' OR ',' + T3.Expertisedestinationsid + ',' like '%,' + '$CitySysId' + ',%' OR ',' + T2.CountrySysId + ',' like '%,' + '$contid' + ',%' OR ',' + T3.ExpertiseCountriesId + ',' like '%,' + '$contid' + ',%')";
// }
if (trim($SearchString)) {
$expodeDataSet = explode(',', $SearchString);
if (!empty($expodeDataSet)) {
$whereCondition .= " AND ( ( ";
for ($i = 0; $i < count($expodeDataSet); $i++) {
$whereCondition .= " T2.SearchString LIKE '%" . trim($expodeDataSet[$i]) . "%' ";
if (count($expodeDataSet) > $i + 1) {
$whereCondition .= " OR ";
}
}
$whereCondition .= " ) OR ( ";
for ($i = 0; $i < count($expodeDataSet); $i++) {
$whereCondition .= " T2.CountryNameString LIKE '%" . trim($expodeDataSet[$i]) . "%' ";
if (count($expodeDataSet) > $i + 1) {
$whereCondition .= " OR ";
}
}
$whereCondition .= " ) ) ";
}
}
// $whereCondition .= " AND T2.SearchString LIKE '%".trim($SearchString)."%'";
//$whereCondition .= " AND ( ',' + T2.SearchString + ',' like '%,' '$SearchString' ',%' )";
//$whereCondition .= " AND ( ',' + T2.CitySysId + ',' like '%,' + '$CitySysId' + ',%' OR ',' + T2.CountrySysId + ',' like '%,' + '$contid' + ',%' OR ',' + T3.ExpertiseCountriesId + ',' like '%,' + '$contid' + ',%')";
// if(isset($contid) && !empty($contid)){
// $whereCondition .= " AND ( ',' + T3.ExpertiseCountriesId + ',' like '%,' + '$contid' + ',%' )";
// }
$select = $this->db->select()
->from(array("T2" => "TB_Supplier_Contacts"))
//->joinLeft(array('T2' => 'TB_Supplier_Contacts'), 'T2.SupplierContactId=T1.SupplierContactId', array('T2.Salution','T2.FirstName','T2.LastName','T2.EmailId','T2.PrimaryMobile'))
->joinLeft(array('T3' => 'TB_IC_Supplier'), 'T3.SupplierSysId=T2.SupplierSysId', array('T3.SupplierName', 'T3.EmailId as SupplierEmailId', 'T3.Services', 'T3.Expertisedestinationsid', 'T3.ExpertiseCountriesId'))
->joinLeft(array('T4' => 'TB_Master_Geo_Country'), 'T3.ContId=T4.ContId', array('T4.Title as SupCountryName'))
->joinLeft(array('T5' => 'TB_Master_Geo_City'), 'T3.CitySysId=T5.CityId', array('T5.Title as SupCityName'))
//->joinLeft(array('T6' => 'TB_Master_Geo_Country'), 'T1.CountrySysId=T6.ContId', array('T6.Title as ContCountryName'))
//->joinLeft(array('T7' => 'TB_Master_Geo_City'), 'T1.CitySysId=T7.CityId', array('T7.Title as ContCityName'))
// ->where("T1.IsMarkForDelete=?", 0)
// ->where("T3.IsDelete=?", 0)
// ->where("T3.XRefAgencySysId=?", $AgencySysId)
->where($whereCondition)
->where("T3.IsActive=?", 1)
->order("T3.SupplierName ASC")
->order("T2.ContactPerson ASC");
//echo $select;die;
$result = $this->db->fetchAll($select);
//echo '<pre>';print_r($result);echo '</pre>';
return $result;
}
public function getSuppliersContactListById($SearchString, $AgencySysId, $supplierservices = array(), $PlanType = 5, $IsOnlySightseeingPkg = 0, $showGTXSupplier = 0) {
$result = array();
$getDataSet = $this->checkDestinationId($SearchString, $PlanType, $IsOnlySightseeingPkg);
if (!empty($getDataSet['state'])) {
$whereCondition = '';
if ($showGTXSupplier == 1) {
$whereCondition .= " T3.IsMarkForDelete = '0' AND T2.IsDelete='0' AND T2.IsActive = 1 AND T2.XRefAgencySysId IN ('$AgencySysId', 1903) ";
} else {
$whereCondition .= " T3.IsMarkForDelete = '0' AND T2.IsDelete='0' AND T2.IsActive = 1 AND T2.XRefAgencySysId = $AgencySysId ";
}if (!empty($supplierservices)) {
$whereCondition .= "AND (";
foreach ($supplierservices as $ssKey => $ssVal) {
if ($ssKey > 0) {
$whereCondition .= " OR ";
}
$whereCondition .= "T2.ServicesType like '%," . $ssVal . "%' OR T2.ServicesType like '%" . $ssVal . ",%' OR T2.ServicesType = '" . $ssVal . "'";
}
$whereCondition .= ")";
}
$select = $this->db->select()
->from(array("T1" => "TB_MASTER_VIEW_SUPPLIERSEACH_STRING"), array())
->joinLeft(array('T2' => 'TB_IC_Supplier'), 'T1.SupplierId=T2.SupplierSysId', array('SupplierName', 'EmailId as SupplierEmailId', 'Services', 'Services as SuppServices', 'Expertisedestinationsid', 'ExpertiseCountriesId', 'XRefAgencySysId'))
->joinLeft(array('T3' => 'TB_Supplier_Contacts'), '(T3.SupplierSysId=T2.SupplierSysId AND T3.SupplierContactId = T1.SupplierContactId)')
->joinLeft(array('T4' => 'TB_Master_Geo_Country'), 'T2.ContId=T4.ContId', array('T4.Title as SupCountryName'))
->joinLeft(array('T5' => 'TB_Master_Geo_City'), 'T2.CitySysId=T5.CityId', array('T5.Title as SupCityName'))
->where($whereCondition)
->where("T1.TypeId IN (?)", $getDataSet['state'])
->where("T1.TypeTitle=?", 'state')
->order("T2.SupplierName ASC");
//->order("T3.ContactPerson ASC");
$resultState = $this->db->fetchAll($select);
// echo $select;die;
foreach ($resultState as $value) {
$result[$value['SupplierContactId']] = $value;
}
}
if (!empty($getDataSet['country'])) {
$whereCondition = '';
if ($showGTXSupplier == 1) {
$whereCondition .= " T3.IsMarkForDelete = '0' AND T2.IsDelete='0' AND T2.IsActive = 1 AND T2.XRefAgencySysId IN ('$AgencySysId', 1903) ";
} else {
$whereCondition .= " T3.IsMarkForDelete = '0' AND T2.IsDelete='0' AND T2.IsActive = 1 AND T2.XRefAgencySysId = $AgencySysId ";
}
if (!empty($supplierservices)) {
$whereCondition .= "AND (";
foreach ($supplierservices as $ssKey => $ssVal) {
if ($ssKey > 0) {
$whereCondition .= " OR ";
}
$whereCondition .= "T2.ServicesType like '%," . $ssVal . "%' OR T2.ServicesType like '%" . $ssVal . ",%' OR T2.ServicesType = '" . $ssVal . "'";
}
$whereCondition .= ")";
}
$select = $this->db->select()
->from(array("T1" => "TB_MASTER_VIEW_SUPPLIERSEACH_STRING"), array())
->joinLeft(array('T2' => 'TB_IC_Supplier'), 'T1.SupplierId=T2.SupplierSysId', array('SupplierName', 'EmailId as SupplierEmailId', 'Services', 'Services as SuppServices', 'Expertisedestinationsid', 'ExpertiseCountriesId', 'XRefAgencySysId'))
->joinLeft(array('T3' => 'TB_Supplier_Contacts'), '(T3.SupplierSysId=T2.SupplierSysId AND T3.SupplierContactId = T1.SupplierContactId)')
->joinLeft(array('T4' => 'TB_Master_Geo_Country'), 'T2.ContId=T4.ContId', array('T4.Title as SupCountryName'))
->joinLeft(array('T5' => 'TB_Master_Geo_City'), 'T2.CitySysId=T5.CityId', array('T5.Title as SupCityName'))
->where($whereCondition)
->where("T1.TypeId IN (?)", $getDataSet['country'])
->where("T1.TypeTitle=?", 'country')
->order("T2.SupplierName ASC");
//->order("T3.ContactPerson ASC");
$resultcountry = $this->db->fetchAll($select);
foreach ($resultcountry as $value) {
$result[$value['SupplierContactId']] = $value;
}
}
if (!empty($getDataSet['RegionId'])) {
$whereCondition = '';
if ($showGTXSupplier == 1) {
$whereCondition .= " T3.IsMarkForDelete = '0' AND T2.IsDelete='0' AND T2.IsActive = 1 AND T2.XRefAgencySysId IN ('$AgencySysId', 1903) ";
} else {
$whereCondition .= " T3.IsMarkForDelete = '0' AND T2.IsDelete='0' AND T2.IsActive = 1 AND T2.XRefAgencySysId = $AgencySysId ";
}
if (!empty($supplierservices)) {
$whereCondition .= "AND (";
foreach ($supplierservices as $ssKey => $ssVal) {
if ($ssKey > 0) {
$whereCondition .= " OR ";
}
$whereCondition .= "T2.ServicesType like '%," . $ssVal . "%' OR T2.ServicesType like '%" . $ssVal . ",%' OR T2.ServicesType = '" . $ssVal . "'";
}
$whereCondition .= ")";
}
$select = $this->db->select()
->from(array("T1" => "TB_MASTER_VIEW_SUPPLIERSEACH_STRING"), array())
->joinLeft(array('T2' => 'TB_IC_Supplier'), 'T1.SupplierId=T2.SupplierSysId', array('SupplierName', 'EmailId as SupplierEmailId', 'Services', 'Services as SuppServices', 'Expertisedestinationsid', 'ExpertiseCountriesId', 'XRefAgencySysId'))
->joinLeft(array('T3' => 'TB_Supplier_Contacts'), '(T3.SupplierSysId=T2.SupplierSysId AND T3.SupplierContactId = T1.SupplierContactId)')
->joinLeft(array('T4' => 'TB_Master_Geo_Country'), 'T2.ContId=T4.ContId', array('T4.Title as SupCountryName'))
->joinLeft(array('T5' => 'TB_Master_Geo_City'), 'T2.CitySysId=T5.CityId', array('T5.Title as SupCityName'))
->where($whereCondition)
->where("T1.TypeId IN (?)", $getDataSet['RegionId'])
->where("T1.TypeTitle=?", 'region')
->order("T2.SupplierName ASC");
// ->order("T3.ContactPerson ASC");
// echo $select;exit;
$resultRegionId = $this->db->fetchAll($select);
// echo "<pre>";print_r($resultRegionId);exit;
foreach ($resultRegionId as $value) {
$result[$value['SupplierContactId']] = $value;
}
}
// echo "<pre>";print_r($result);exit;
return $result;
}
public function getSupplierContactById($SupplierContactId = NULL) {
$select = $this->db->select()
->from(array("TBSC" => "TB_Supplier_Contacts"))
//->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBSC.CountrySysId', array('TBMGCO.Title as CountryName'))
//->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBSC.CitySysId', array('TBMGC.Title as CityName'))
->joinLeft(array('TBMGS' => 'TB_Master_Geo_State'), 'TBMGS.StateId=TBSC.StateOrZoneSysId', array('TBMGS.Title as StateName'))
->where("TBSC.IsActive=?", 1)
->where("TBSC.IsMarkForDelete=?", 0)
->where("TBSC.SupplierContactId=?", $SupplierContactId);
$result = $this->db->fetchRow($select);
return $result;
}
public function getAutoSuggestCompany($agencySysId, $keyword) {
$keyword = trim($keyword);
$sql = " SELECT * FROM TB_IC_Supplier WHERE 1 = 1 AND IsDelete = 0 AND IsActive = 1 AND XRefAgencySysId = " . $agencySysId . "";
if (!empty($keyword)) {
$sql .= " AND TB_IC_Supplier.SupplierName like '" . $keyword . "%' ";
}
$result = $this->db->query($sql)->fetchAll();
$response = array();
if (count($result) > 0) {
foreach ($result as $row) {
$response[] = array('value' => $row['SupplierSysId'], 'label' => ucfirst($row['SupplierName']));
}
}
return $response;
}
public function getAutoSuggestCity($agencySysId, $keyword) {
$keyword = trim($keyword);
$sql = " SELECT DISTINCT(TB_IC_Supplier.CitySysId),TB_Master_Geo_City.Title as CityName,TB_Master_Geo_Country.Title as CountryName FROM TB_IC_Supplier
LEFT JOIN TB_Master_Geo_City ON TB_IC_Supplier.CitySysId = TB_Master_Geo_City.CityId
LEFT JOIN TB_Master_Geo_Country ON TB_IC_Supplier.ContId = TB_Master_Geo_Country.ContId
WHERE 1 = 1 AND TB_IC_Supplier.IsDelete = 0 AND TB_IC_Supplier.IsActive = 1 AND TB_Master_Geo_City.IsActive = 1 and TB_IC_Supplier.XRefAgencySysId = " . $agencySysId . "";
if (!empty($keyword)) {
$sql .= " AND TB_Master_Geo_City.Title like '" . $keyword . "%' ";
}
$result = $this->db->query($sql)->fetchAll();
//print_r(($result));die;
$response = array();
if (count($result) > 0) {
foreach ($result as $row) {
$response[] = array('value' => $row['CitySysId'], 'label' => ucfirst($row['CityName']) . '(' . $row['CountryName'] . ')');
}
}
return $response;
}
public function getAutoSuggestLocarion($keyword) {
$keyword = trim($keyword);
$sql = " SELECT TB_Master_Geo_City.CityId,TB_Master_Geo_City.Title as CityName,TB_Master_Geo_Country.Title as CountryName FROM TB_Master_Geo_City
LEFT JOIN TB_Master_Geo_Country ON TB_Master_Geo_City.ContSysId = TB_Master_Geo_Country.ContId
WHERE 1 = 1 AND TB_Master_Geo_City.IsMarkForDel = 0 AND TB_Master_Geo_City.IsActive = 1 ";
if (!empty($keyword)) {
$sql .= " AND TB_Master_Geo_City.Title like '" . $keyword . "%' ";
}
$result = $this->db->query($sql)->fetchAll();
//print_r(($result));die;
$response = array();
if (count($result) > 0) {
foreach ($result as $row) {
$response[] = array('value' => $row['CityId'], 'label' => ucfirst($row['CityName']) . '(' . $row['CountryName'] . ')');
}
}
return $response;
}
public function SuggestCitySupplierPackEnquiry($agencySysId, $keyword) {
$keyword = trim($keyword);
$sql = " SELECT DISTINCT(TB_Enqury_Supplier_Package.CitySysId),TB_Master_Geo_City.Title as CityName,TB_Master_Geo_Country.Title as CountryName FROM TB_Enqury_Supplier_Package
LEFT JOIN TB_Master_Geo_City ON TB_Enqury_Supplier_Package.CitySysId = TB_Master_Geo_City.CityId
LEFT JOIN TB_Master_Geo_Country ON TB_Master_Geo_City.ContSysId = TB_Master_Geo_Country.ContId
WHERE 1 = 1 AND TB_Enqury_Supplier_Package.IsMarkForDelete = 0 AND TB_Enqury_Supplier_Package.IsActive = 1 AND TB_Master_Geo_City.IsActive = 1 and TB_Enqury_Supplier_Package.AgencySysId = " . $agencySysId . "";
if (!empty($keyword)) {
$sql .= " AND TB_Master_Geo_City.Title like '" . $keyword . "%' ";
}
$result = $this->db->query($sql)->fetchAll();
//print_r(($result));die;
$response = array();
if (count($result) > 0) {
foreach ($result as $row) {
$response[] = array('value' => $row['CitySysId'], 'label' => ucfirst($row['CityName']) . '(' . $row['CountryName'] . ')');
}
}
return $response;
}
public function SuggestSuppliertSupplierPackEnquiry($agencySysId, $keyword) {
$keyword = trim($keyword);
$sql = " SELECT DISTINCT(TB_Enqury_Supplier_Package.SupplierSysId),TB_IC_Supplier.SupplierName as SupplierName FROM TB_Enqury_Supplier_Package
LEFT JOIN TB_IC_Supplier ON TB_Enqury_Supplier_Package.SupplierSysId = TB_IC_Supplier.SupplierSysId
WHERE 1 = 1 AND TB_Enqury_Supplier_Package.IsMarkForDelete = 0 AND TB_Enqury_Supplier_Package.IsActive = 1 AND TB_Enqury_Supplier_Package.AgencySysId = " . $agencySysId . "";
if (!empty($keyword)) {
$sql .= " AND TB_IC_Supplier.SupplierName like '" . $keyword . "%' ";
}
$result = $this->db->query($sql)->fetchAll();
//print_r(($result));die;
$response = array();
if (count($result) > 0) {
foreach ($result as $row) {
$response[] = array('value' => $row['SupplierSysId'], 'label' => ucfirst($row['SupplierName']));
}
}
return $response;
}
public function addSupplierBank($data) {
try {
$this->db->insert('TB_Supplier_BankDetails', $data);
return $this->db->lastInsertId('TB_Supplier_BankDetails');
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
}
public function updateSupplierBank($dataForDb = array(), $where = NULL) {
// $where = array('BankInfoSysId =?' => $SupplierBankId);
$this->db->update('TB_Supplier_BankDetails', $dataForDb, $where);
}
public function getSuppliersBankList($SupplierSysId = NULL) {
$select = $this->db->select()
->from("TB_Supplier_BankDetails")
->where("SupplierSysId=?", $SupplierSysId)
->order("BankInfoSysId Desc");
$result = $this->db->fetchAll($select);
return $result;
}
public function getSupplierBankById($SupplierBankId = NULL) {
$select = $this->db->select()
->from(array("TBSB" => "TB_Supplier_BankDetails"))
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBSB.CountrySysId', array('TBMGCO.Title as CountryName'))
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBSB.CitySysId', array('TBMGC.Title as CityName'))
//->joinLeft(array('TBMGS' => 'TB_Master_Geo_State'), 'TBMGS.StateId=TBSB.StateId', array('TBMGS.Title as StateName'))
->where("TBSB.BankInfoSysId=?", $SupplierBankId);
$result = $this->db->fetchRow($select);
return $result;
}
public function isSupplierBankDetailExists($whereCondition, $SupplierSysId, $SupplierBankId = NULL) {
// $whereCondition = "EmailId = '$email'";
if ($SupplierSysId) {
$whereCondition .= " AND SupplierSysId = " . $SupplierSysId;
}
if ($SupplierBankId) {
$whereCondition .= " AND BankInfoSysId != " . $SupplierBankId;
}
$select = $this->db->select()
->from("TB_Supplier_BankDetails", array('count(*) AS totalRecords'))
->where($whereCondition);
$count = $this->db->fetchOne($select);
if ($count > 0) {
return true;
}
return false;
}
public function isSupplierContactsMobileNoExists($mobileno, $SupplierSysId, $SupplierContactId = NULL) {
$whereCondition = "PrimaryMobile = '$mobileno'";
if ($SupplierSysId) {
$whereCondition .= " AND SupplierSysId = " . $SupplierSysId;
}
if ($SupplierContactId) {
$whereCondition .= " AND SupplierContactId != " . $SupplierContactId;
}
$select = $this->db->select()
->from("TB_Supplier_Contacts", array('count(*) AS totalRecords'))
->where($whereCondition);
$count = $this->db->fetchOne($select);
if ($count > 0) {
return true;
}
return false;
}
public function getSuppliersDetailsById($SupplierSysId = 0) {
if ($SupplierSysId > 0) {
$sql = " SELECT SupplierSysId ,TB_IC_Supplier.SupplierTypeSysId,TB_IC_Supplier.GSTNo,TB_IC_Supplier.Type, TB_Master_SupplierType.Title as SupplierType, FacilityMask ,SupplierName
,ContactPerson,PrefCurrencyType,TB_Master_Currency.Title as Currency, Designation,ContactNo
,EmailId,Address,CitySysId,ContId,TB_IC_Supplier.StateId,PlaceSysId,TB_Master_Geo_City.Title as CityName,TB_Master_Geo_State.Title as StateName
,IsCreatedByAgent,AgentSysId,TB_IC_Supplier.UpdatedDate,TB_IC_Supplier.CreateDate
,TB_IC_Supplier.ApproveDate, TB_IC_Supplier.IsApproved, TB_IC_Supplier.IsDelete,TB_IC_Supplier.IsActive
FROM TB_IC_Supplier LEFT JOIN TB_MASTER_CURRENCY
ON TB_IC_Supplier.PrefCurrencyType = TB_MASTER_CURRENCY.CurrencyType
LEFT JOIN TB_Master_Geo_City ON TB_IC_Supplier.CitySysId = TB_Master_Geo_City.CityId
--INNER JOIN TB_Master_Geo_State ON TB_IC_Supplier.StateId = TB_Master_Geo_State.StateId
LEFT JOIN TB_Master_Geo_State ON TB_IC_Supplier.StateId = TB_Master_Geo_State.StateId
LEFT JOIN TB_Master_SupplierType ON TB_IC_Supplier.SupplierTypeSysId = TB_Master_SupplierType.SupplierTypeSysId WHERE 1=1";
if (!empty($this->SupplierTypeSysId)) {
$sql .= " AND TB_IC_Supplier.SupplierTypeSysId = " . $this->SupplierTypeSysId;
}
$sql .= " AND TB_IC_Supplier.SupplierSysId = " . $SupplierSysId;
$sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;
// echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
return array();
}
public function getSuppliersNameById($SupplierSysId) {
$select = $this->db->select();
$selectarray = array('SupplierSysId', 'SupplierName', 'IsDelete', 'IsActive');
$select->from(array('tb1' => "TB_IC_Supplier"), $selectarray);
$select->where("tb1.SupplierSysId =?", $SupplierSysId);
// $select->where("tb1.IsActive =?", 1); by sabir
$result = $this->db->fetchRow($select);
return $result['SupplierName'];
}
public function getSuppliersContactNameById($SupplierContactId) {
$select = $this->db->select();
$selectarray = array('ContactPerson');
$select->from(array('tb1' => "TB_Supplier_Contacts"), $selectarray);
$select->where("tb1.SupplierContactId =?", $SupplierContactId);
// $select->where("tb1.IsActive =?", 1); by sabir
$result = $this->db->fetchRow($select);
return $result['ContactPerson'];
}
public function getSuppliersServiceById($SupplierSysId) {
$select = $this->db->select();
$selectarray = array('SupplierSysId', 'Services', 'IsDelete', 'IsActive');
$select->from(array('tb1' => "TB_IC_Supplier"), $selectarray);
$select->where("tb1.SupplierSysId =?", $SupplierSysId);
// $select->where("tb1.IsActive =?", 1); by sabir
$result = $this->db->fetchRow($select);
return $result['Services'];
}
public function getSupplierspackageEnquiryList($agencySysId) {
$request = Zend_Controller_Front::getInstance()->getRequest();
if ($request->isPost()) {
$post = $request->getPost();
}
//print_r($post);
$whereCondition = " T1.AgencySysId = '$agencySysId' and T1.IsMarkForDelete = '0' and T1.IsActive = '1'";
if (isset($post['CustomerName']) && !empty($post['CustomerName'])) {
$CustomerName = $post['CustomerName'];
$whereCondition .= " AND ( T1.SendersName = '$CustomerName' OR T1.SendersName LIKE '$CustomerName%' )";
}
if (isset($post['hidden_city_id']) && !empty($post['hidden_city_id'])) {
$hidden_city_id = $post['hidden_city_id'];
$whereCondition .= " AND ( T1.CitySysId = '$hidden_city_id')";
}
if (isset($post['hidden_Supplier_id']) && !empty($post['hidden_Supplier_id'])) {
$hidden_Supplier_id = $post['hidden_Supplier_id'];
$whereCondition .= " AND ( T1.SupplierSysId = '$hidden_Supplier_id')";
}
if (isset($post['EmailID']) && !empty($post['EmailID'])) {
$EmailID = $post['EmailID'];
$whereCondition .= " AND ( T1.SendersEmailId = '$EmailID' OR T1.SendersEmailId LIKE '$EmailID%' )";
}
// if(isset($post['companyName']) && !empty($post['companyName'])) {
// $supplierName = $post['companyName'];
// $whereCondition .= " AND ( SupplierName = '" . $supplierName . "' OR CompanyName = '" . $supplierName. "' OR SupplierName LIKE '" . $supplierName . "%' OR CompanyName LIKE '" . $supplierName. "%' ) ";
// }
// if(isset($post['supplierservices']) && !empty($post['supplierservices'])) {
// $supplierservices = $post['supplierservices'];
// $whereCondition .= " AND ( ',' + ServicesType + ',' like '%,' + '$supplierservices' + ',%' )";
// }
//
// if(isset($post['hidden_city_id']) && !empty($post['hidden_city_id'])) {
// $hidden_city_id = $post['hidden_city_id'];
// $whereCondition .= " AND ( ',' + ExpertiseDestinationsId + ',' like '%,' + '$hidden_city_id' + ',%' )";
// }
//
// if(isset($post['hidden_location_id']) && !empty($post['hidden_location_id'])) {
// $hidden_location_id = $post['hidden_location_id'];
// $whereCondition .= " AND ( ',' + otherlocationcity + ',' like '%,' + '$hidden_location_id' + ',%' OR CitySysId LIKE '$hidden_location_id%' )";
// }
//
// if(isset($post['contactName']) && !empty($post['contactName'])) {
// $contactName = $post['contactName'];
// $whereCondition .= " AND ( ContactPerson = '$contactName' OR ContactPerson LIKE '$contactName%' )";
// }
if (isset($post['QueryId']) && !empty($post['QueryId'])) {
$QueryId = $post['QueryId'];
$whereCondition .= " AND ( T4.TPSysId = '$QueryId')";
}
$select = $this->db->select();
$selectarray = array('T1.EnSupPackId', 'T1.SupplierSysId', 'T1.CitySysId', 'T1.TPSysId', 'T1.SendersId', 'T1.SendersEmailId', 'T1.SendersName', 'T1.ContactEmailId', 'T1.ContactName', 'CONVERT(VARCHAR(24),T1.CheckInDate,111) as CheckInDate', 'T1.CreateDate', 'T1.EmailContent', 'T1.RoomMemInfoJson', 'T1.SendersMobile', 'T1.ContactMobile', 'T1.StatusType as Status_Type', 'T1.ProposalStatus', 'T1.PlanType', 'T1.IsB2BAgent');
$select->from(array('T1' => "TB_Enqury_Supplier_Package"), $selectarray)
->where($whereCondition)
->joinLeft(array('T2' => 'TB_MASTER_VIEW_PKGSEARCH_STRING'), "T2.Id=T1.CitySysId AND T2.TypeTitle='city'", array('T2.Title as CityName'))
->joinLeft(array('T3' => 'TB_IC_Supplier'), 'T3.SupplierSysId=T1.SupplierSysId', array('T3.SupplierName as SupplierName'))
->joinLeft(array('T4' => 'TB_Agency_Customer_TravelPlan_Query'), 'T4.TPSysId=T1.TPSysId', array('T4.CreateDate as leadCreateDate', 'T4.StatusType'))
->order("EnSupPackId Desc");
//echo $select; exit;
// $result = $this->db->fetchAll($select);
//echo '<pre>';print_r($result);die;
return $select;
}
public function getSupplierspackageEnquiryDetails($agencySysId, $id) {
$request = Zend_Controller_Front::getInstance()->getRequest();
if ($request->isPost()) {
$post = $request->getPost();
}
//print_r($post);
$whereCondition = " AgencySysId = '$agencySysId' AND EnSupPackId = '$id'";
$select = $this->db->select();
$selectarray = array('T1.EnSupPackId', 'T1.AgencySysId', 'T1.SupplierContactId', 'T1.SupplierSysId', 'T1.CitySysId', 'T1.SupplierSysId', 'T1.SupplierEmailId', 'T1.TPSysId', 'T1.SendersId', 'T1.SendersEmailId', 'T1.SendersName', 'T1.SendersMobile', 'T1.ContactEmailId', 'T1.ContactName', 'CONVERT(VARCHAR(24),T1.CheckInDate,111) as CheckInDate', 'T1.EmailContent', 'T1.RoomMemInfoJson', 'T1.MailSubject', 'T1.ContactMobile');
$select->from(array('T1' => "TB_Enqury_Supplier_Package"), $selectarray)
->where($whereCondition)
->joinLeft(array('T2' => 'TB_Master_Geo_City'), 'T2.CityId=T1.CitySysId', array('T2.Title as CityName'))
->order("EnSupPackId Desc");
$result = $this->db->fetchRow($select);
//echo '<pre>';print_r($result);die;
return $result;
}
public function DiscardQuery($dataForDb = array(), $EnSupPackId = NULL) {
//echo "<pre>";print_r($dataForDb);die;
$where = array('EnSupPackId =?' => $EnSupPackId);
return $this->db->update('TB_Enqury_Supplier_Package', $dataForDb, $where);
}
public function DeleteSupplier($dataForDb = array(), $DeleteId = NULL) {
$where = array('SupplierSysId =?' => $DeleteId);
return $this->db->update('TB_IC_Supplier', $dataForDb, $where);
}
public function SupplierContactDeteted($SupplierContactId, $SupplierSysId, $dataForDb = array()) {
if (!empty($dataForDb)) {
$where = array('SupplierContactId =?' => $SupplierContactId, 'SupplierSysId=?' => $SupplierSysId);
$this->db->update('TB_Supplier_Contacts', $dataForDb, $where);
return true;
} else {
return false;
}
}
public function getSuppliersDetailsBySupplierSysId() {
$whereCondition = " 1=1";
if (!empty($this->SupplierSysId)) {
$whereCondition .= " AND t1.SupplierSysId = " . $this->SupplierSysId;
$whereCondition .= " AND t1.SupplierSysId != 0";
}
$select = $this->db->select();
$selectarray = array('SupplierSysId', 'SupplierName');
$select->from(array('t1' => "TB_IC_Supplier"), $selectarray)
->where($whereCondition);
$result = $this->db->fetchRow($select);
return $result;
}
public function getRegionByStateID($StateSysId) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Master_Geo_State_Region_MAP"));
$select->joinLeft(array('T2' => 'TB_Master_Geo_Region'), 'T2.RegId=tb1.RegCatId', array('T2.Title as RegionName'));
$select->where("tb1.StateSysId =?", $StateSysId);
// $select->where("tb1.IsActive =?", 1); by sabir
$result = $this->db->fetchAll($select);
$data = [];
if ($result) {
foreach ($result as $value) {
$data[] = $value['RegionName'];
}
}
return $data;
}
public function getRegionByCountryID($countSysId) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Master_Geo_Country_Region_MAP"));
$select->joinLeft(array('T2' => 'TB_Master_Geo_Region'), 'T2.RegId=tb1.RegCatId', array('T2.Title as RegionName'));
$select->where("tb1.ContSysId IN(?)", $countSysId);
// $select->where("tb1.IsActive =?", 1); by sabir
$result = $this->db->fetchAll($select);
$data = [];
if ($result) {
foreach ($result as $value) {
$data[] = $value['RegionName'];
}
}
return $data;
}
public function getCityNameArrId($cityId = null) {
$select = $this->db->select()
->from(array("a" => "TB_Master_Geo_City"), array('a.Title'))
->where("a.CityId IN(?)", $cityId);
$result = $this->db->fetchAll($select);
$data = [];
if ($result) {
foreach ($result as $value) {
$data[] = $value['Title'];
}
}
return $data;
}
public function getCountryNameArrId($ContId = null) {
$select = $this->db->select()
->from(array("a" => "TB_Master_Geo_Country"), array('a.Title'))
->where("a.ContId IN(?)", $ContId);
$result = $this->db->fetchAll($select);
$data = [];
if ($result) {
foreach ($result as $value) {
$data[] = $value['Title'];
}
}
return $data;
}
public function getStateNameArrCityId($cityId = null) {
$select = $this->db->select()
->from(array("a" => "TB_Master_Geo_City"), array('a.Title'))
->joinLeft(array('T2' => 'TB_Master_Geo_State'), 'T2.StateId=a.StateSysId', array('T2.Title as StateName'))
->where("a.CityId IN(?)", $cityId);
$result = $this->db->fetchAll($select);
$data = [];
if ($result) {
foreach ($result as $value) {
$data[] = $value['StateName'];
}
}
return $data;
}
public function getRegionName($regionSysId) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Master_Geo_Region"));
$select->where("tb1.RegId IN (?)", $regionSysId);
$result = $this->db->fetchAll($select);
$data = [];
if ($result) {
foreach ($result as $value) {
$data[] = $value['Title'];
}
}
return $data;
}
public function getStateName($stateSysId) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Master_Geo_State"));
$select->where("tb1.StateId IN (?)", $stateSysId);
$result = $this->db->fetchAll($select);
$data = [];
if ($result) {
foreach ($result as $value) {
$data[] = $value['Title'];
}
}
return $data;
}
public function checkDestinationId($destinationId, $PlanType = 5, $IsOnlySightseeingPkg = 0) {
$data = $destArray = array();
if ($destinationId != '') {
$destArray = explode(',', $destinationId);
if ($PlanType == 5 || $PlanType == 10) {
if ($IsOnlySightseeingPkg == 1) {
$cityData = $destinationId;
} else {
$select = $this->db->select();
$select->from(array('tb1' => "TB_MASTER_VIEW_PKGSEARCH_STRING"));
$select->where("tb1.ID IN (?)", $destArray);
// echo $select; exit;
$result = $this->db->fetchAll($select);
$cityData = $stateData = $countryData = $regionData = array();
if (!empty($result)) {
foreach ($result as $valueData) {
if ($valueData['TypeTitle'] == 'city') {
$cityData[] = $valueData['TypeId'];
$countryData[] = $valueData['CountryId'];
}
if ($valueData['TypeTitle'] == 'state') {
$stateData[] = $valueData['TypeId'];
$countryData[] = $valueData['CountryId'];
}
if ($valueData['TypeTitle'] == 'country') {
$countryData[] = $valueData['TypeId'];
}
if ($valueData['TypeTitle'] == 'region') {
$regionData[] = $valueData['TypeId'];
$countryData[] = $valueData['CountryId'];
}
}
}
}
} else if ($PlanType == 2 || $PlanType == 11 || $PlanType == 1) {
$cityData = $destinationId;
} else if ($PlanType == 6) {
$data['country'][] = $destinationId;
}
// echo "<pre>";print_r($cityData);exit;
if (!empty($cityData)) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Master_Geo_City"), array('StateSysId', 'ContSysId'));
//$select->leftJoin(array('tb2' => "TB_Master_Geo_City", 'tb1.StateSysId=a.StateSysId'),array('StateSysId', 'ContSysId'));
$select->where("tb1.CityId IN (?)", $cityData);
$result = $this->db->fetchAll($select);
// echo "<pre>";print_r($result);exit;
if (!empty($result)) {
foreach ($result as $valueSet) {
$data['country'][] = $valueSet['ContSysId'];
$data['state'][] = $valueSet['StateSysId'];
$stateData[] = $valueSet['StateSysId'];
$countryData[] = $valueSet['ContSysId'];
}
}
}
if (!empty($stateData)) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Master_Geo_State"), array('StateId', 'ContSysId', 'RegId'));
$select->where("tb1.StateId IN (?)", $stateData);
$result = $this->db->fetchAll($select);
if (!empty($result)) {
foreach ($result as $valueSet) {
$data['country'][] = $valueSet['ContSysId'];
$data['state'][] = $valueSet['StateId'];
$data['RegionId'][] = $valueSet['RegId'];
}
}
}
if (!empty($countryData)) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Master_Geo_Country_Region_MAP"), array('RegCatId', 'ContSysId'));
$select->where("tb1.ContSysId IN (?)", $countryData);
// echo $select;die;
$result = $this->db->fetchAll($select);
if (!empty($result)) {
foreach ($result as $valueSet) {
$data['country'][] = $valueSet['ContSysId'];
$data['RegionId'][] = $valueSet['RegCatId'];
}
}
}
if (!empty($regionData)) {
// $select = $this->db->select();
// $select->from(array('tb1' => "TB_Master_Geo_Region"), array('RegionId', 'ContId'));
// $select->where("tb1.ContId IN (?)", $regionData);
// $result = $this->db->fetchAll($select);
if (!empty($regionData)) {
foreach ($regionData as $valueSet) {
$data['RegionId'][] = $valueSet;
}
}
}
}
return $data;
}
public function checkSupplierEmailExists($intLoggedinUserAgencySysId, $email, $SupplierUserId = NULL) {
$whereCondition = "EmailId = '$email' AND SupplierAgencySysId = '$intLoggedinUserAgencySysId'";
if ($SupplierUserId) {
$whereCondition .= " AND SupplierSysId != " . $SupplierUserId;
}
$select = $this->db->select()
->from("TB_IC_Supplier", array('EmailId', 'SupplierSysId'))
->where($whereCondition);
//echo $select;die;
$result = $this->db->fetchRow($select);
return $result;
}
public function getSupplierServicesbyId($SupplierSerSysId) {
$sql = " SELECT * from TB_Master_SupplierServices where isActive = 1 AND SupplierSerSysId =" . $SupplierSerSysId;
return $this->db->query($sql)->fetchAll();
}
public function addUpdateDefaultSupplier($AgencySysId, $userSysId) {
$objStaff = new Travel_Model_TblAgencyStaff();
$userData = $objStaff->getUserDataByUserId($userSysId);
$AgentSysId = $userSysId;
$SupplierSysId = $this->getSupplierEmailExists($AgencySysId, trim($userData['EmailId']));
//echo "<pre>";print_r($checkemail);die;
$currentDate = date('Y-m-d');
$dataForDb = array(
'SupplierName' => trim($userData['AgencyName']),
'Salution' => (isset($userData['Salutation']) && !empty($userData['Salutation'])) ? (int) ($userData['Salutation']) : 1,
'FirstName' => trim($userData['FirstName']),
'LastName' => trim($userData['LastName']),
'IsCompany' => 1,
'CompanyName' => trim($userData['AgencyName']),
'Services' => '',
'GstApplicable' => 0,
'RegisrationScheme' => NULL,
'GSTNo' => NULL,
'Type' => '',
'ContactPerson' => trim(trim($userData['FirstName']) . ' ' . trim($userData['LastName'])),
'Designation' => '',
'ContactNo' => $userData['ContactNo1'],
'EmailId' => trim($userData['EmailId']),
'CitySysId' => isset($userData['CitySysId']) ? (int) $userData['CitySysId'] : 0,
'ContId' => isset($userData['CountrySysId']) ? (int) $userData['CountrySysId'] : 0,
'StateId' => isset($userData['StateOrZoneSysId']) ? (int) $userData['StateOrZoneSysId'] : 0,
'Expertisecountriesid' => '',
'OldExpertiseCountriesId' => '',
'Expertisedestinationsid' => '',
'ExpertiseStateId' => '',
'ExpertiseRegionId' => 0,
'otherlocationcity' => '',
'CategoryType' => '',
'ServicesType' => '',
'address1' => $userData['Address'],
'address2' => '',
'comments' => '',
'Regions' => '',
'UpdatedDate' => $currentDate,
'SupplierTypeSysId' => '0',
'FacilityMask' => '0',
'PrefCurrencyType' => '0',
'Address' => '0',
'PlaceSysId' => '0',
'IsCreatedByAgent' => '1',
'AgentSysId' => $AgentSysId,
'XRefAgencySysId' => $AgencySysId,
'CreateDate' => $currentDate,
'ApproveDate' => $currentDate,
'IsApproved' => 0,
'IsDelete' => 0,
'IsActive' => 1,
'countrycode' => isset($userData['countrycode']) ? $userData['countrycode'] : '+91',
'code_icons' => 'in',
'IscheckForAllCountry' => '0',
'SearchString' => '',
'CountryNameString' => '',
'CityNameString' => '',
'IsExpertisecountriesAll' => '',
'SupplierAgencySysId' => $AgencySysId,
);
$dataForDbC = array(
'ProfileTitle' => trim($userData['Designation']),
'Salution' => 1,
'FirstName' => trim($userData['FirstName']),
'LastName' => trim($userData['LastName']),
'CompanyName' => trim($userData['AgencyName']),
'Services' => '',
'ContactPerson' => trim(trim($userData['FirstName']) . ' ' . trim($userData['LastName'])),
'Department' => 'NA',
'PrimaryMobile' => $userData['ContactNo1'],
'EmailId' => trim($userData['EmailId']),
'CitySysId' => 0,
'CountrySysId' => 0,
'UpdateDate' => $currentDate,
'OtherDetails' => '',
'ContactType' => '0',
'ProfileId' => '0',
'LandlineNumbers' => '0',
'OtherContactInfo' => '0',
'CreateDate' => $currentDate,
'IsRegisterUser' => '0',
'Address' => '0',
'StateOrZoneSysId' => 0,
'ActivationDate' => $currentDate,
'ApprovalDate' => $currentDate,
'IsVarified' => '0',
'IsActive' => '1',
'IsMarkForDelete' => '0',
'IsApproved' => '0',
'IsPrimary' => 1,
'code_icons' => 'in',
'countrycode' => isset($userData['countrycode']) ? $userData['countrycode'] : '+91',
'SearchString' => '',
'CountryNameString' => '',
'CityNameString' => ''
);
if (!empty($SupplierSysId)) {
$SupplierContactId = $this->getSupplierContactExists(trim($userData['EmailId']), $SupplierSysId);
$this->updateSupplier($dataForDb, $SupplierSysId);
$this->updateSupplierContact($dataForDbC, $SupplierContactId);
$result = array('status' => true, 'supplierSysId' => $SupplierSysId);
} else {
$intLastInsertId = $this->addSupplier($dataForDb);
$dataForDbC['SupplierSysId'] = $intLastInsertId;
$SupplierContactId = $this->addSupplierContact($dataForDbC);
// echo "<pre>";print_r($SupplierContactId);die;
$result = array('status' => true, 'supplierSysId' => $intLastInsertId);
}
return $result;
}
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(trim($input_data)), ENT_QUOTES);
}
public function getMultiSuppliersList($SupplierSysIds) {
$select = $this->db->select()
->from("TB_IC_Supplier", array('EmailId', 'SupplierName', 'SupplierSysId'))
->where("TB_IC_Supplier.IsActive=?", 1)
->where("TB_IC_Supplier.IsDelete=?", 0)
->where("TB_IC_Supplier.SupplierSysId IN(?) ", $SupplierSysIds)
->order("SupplierSysId Desc");
return $result = $this->db->fetchAll($select);
}
public function getSuppliersListNew($intLoggedinUserId, $agencySysId) {
$request = Zend_Controller_Front::getInstance()->getRequest();
if ($request->isPost()) {
$post = $request->getPost();
}
$whereCondition = " XRefAgencySysId = '$agencySysId'";
if (!empty($post['companyName'])) {
$supplierName = $post['companyName'];
$whereCondition .= " AND ( SupplierName = '$supplierName' OR CompanyName = '$supplierName' OR SupplierName LIKE '$supplierName%' OR CompanyName LIKE '$supplierName%' )";
}
if (!empty($post['supplierservices'])) {
$supplierservices = $post['supplierservices'];
$whereCondition .= " AND ( ',' + ServicesType + ',' LIKE '%,$supplierservices,%' )";
}
if (!empty($post['hidden_city_id'])) {
$hidden_city_id = $post['hidden_city_id'];
$whereCondition .= " AND ( ',' + ExpertiseDestinationsId + ',' LIKE '%,$hidden_city_id,%' )";
}
if (!empty($post['hidden_location_id'])) {
$hidden_location_id = $post['hidden_location_id'];
$whereCondition .= " AND ( ',' + otherlocationcity + ',' LIKE '%,$hidden_location_id,%' OR CitySysId = '$hidden_location_id' )";
}
if (!empty($post['contactName'])) {
$contactName = $post['contactName'];
$whereCondition .= " AND ( ContactPerson = '$contactName' OR ContactPerson LIKE '$contactName%' )";
}
if (!empty($post['phoneNo'])) {
$phoneNo = $post['phoneNo'];
$whereCondition .= " AND ( ContactNo = '$phoneNo' OR ContactNo LIKE '$phoneNo%' )";
}
if (!empty($post['emailId'])) {
$emailId = $post['emailId'];
$whereCondition .= " AND ( EmailId = '$emailId' OR EmailId LIKE '$emailId%' )";
}
// Step 1: Fetch main supplier list
$select = $this->db->select()
->from(array("T1" => "TB_IC_Supplier"), array('*'))
->joinLeft(array('T2' => 'TB_Supplier_Contacts'), 'T2.SupplierSysId=T1.SupplierSysId AND T2.IsPrimary = 1', array('T2.supplierContactId'))
->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=T1.ContId', array('TBMGCO.Title as CountryName'))
->joinLeft(array('T3' => 'TB_Supplier_Wallet'), 'T3.SupplierSysId=T1.SupplierSysId', array('T3.WalletId', 'T3.BalanceAmount', 'T3.CurrencyType as WalletCurrencyType'))
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=T1.CitySysId', array('TBMGC.Title as CityName'))
->where($whereCondition)
->where("T1.IsActive = ?", 1)
->order("T1.SupplierSysId DESC");
$suppliers = $this->db->fetchAll($select);
// Step 2: Fetch wallet history
$supplierIds = array_column($suppliers, 'SupplierSysId');
$walletHistories = [];
if (!empty($supplierIds)) {
$selectHistory = $this->db->select()
->from(array("T4" => "TB_Supplier_Wallet_History"), array('T4.Balance', 'T4.SupplierId', 'T4.TransactionId'))
->where('SupplierId IN (?)', $supplierIds);
$historyRows = $this->db->fetchAll($selectHistory);
// Step 3: Group history by supplier
foreach ($historyRows as $row) {
$walletHistories[$row['SupplierId']][] = $row;
}
}
// Step 4: Merge wallet history into suppliers
foreach ($suppliers as &$supplier) {
$supplierId = $supplier['SupplierSysId'];
$supplier['wallet_history'] = isset($walletHistories[$supplierId]) ? $walletHistories[$supplierId] : [];
}
return $suppliers;
}
public function supplierBillList($ICSourceSysId, $fromDate = null, $currentDate = null, $post = array(), $pageType = null) {
$totalSubQuery = "SELECT SUM(TotalAmount) FROM TB_Supplier_Bill_Items AS TSBI WHERE TSBI.BillId = tb1.BillId";
$select = $this->db->select();
$selectarray = array('SupplierSysId', 'BillId', 'AgencySysId', 'CONVERT(VARCHAR(24),BillDate,103) as BillDate', 'CONVERT(VARCHAR(24),BillDueDate,103) as BillDueDate', 'BillNumber', 'IsITCApplicable', 'CurrencyType', 'TotalCost', 'TotalTax', 'NetAmount', 'AgentSysId', 'CreatedDate', 'Status', "($totalSubQuery) AS TotalVal", "PaidAmount");
$supplierarray = array('SupplierSysId', 'ContactNo', 'ContId', 'countrycode', 'CitySysId', 'EmailId', 'Address', 'SupplierName', 'FirstName', "(RTRIM(LTRIM(tb2.FirstName)) + ' ' + RTRIM(LTRIM(tb2.LastName))) as FullName");
$Currencyarray = array('Symbol', 'Title', 'Icon');
$b2bagencyColumn = array('UserSysId', 'GSTIN', 'Address as AgAddress', 'PinCode as AgPinCode', 'StateOrZoneSysId as AgStateOrZoneSysId', 'ContactNo1 as AgContact', 'EmailId as AgEmailId', 'CitySysId as AgCitySysId', 'FirstName as AgFirstName', 'LastName as AgLastName');
$select->from(array('tb1' => "TB_Supplier_Bill"), $selectarray);
$select->joinLeft(array('tb2' => "TB_IC_Supplier"), "tb1.SupplierSysId = tb2.SupplierSysId", $supplierarray);
$select->joinLeft(array('tb3' => "TB_Master_Currency"), "tb1.CurrencyType = tb3.CurrencyType", $Currencyarray);
$select->joinLeft(array('tb6' => "TB_Agency_User"), "tb6.UserSysId = tb1.AgentSysId AND tb1.AgencySysId = tb6.AgencySysId", $b2bagencyColumn);
$select->joinLeft(array('tb9' => "TB_Agency"), "tb9.AgencySysId = tb1.AgencySysId", array('Title as CompanyName', 'MasterAgencySysId'));
if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
$select->where("tb1.AgencySysId =?", $ICSourceSysId);
}
// if (!empty($fromDate) && !empty($currentDate)) {
// $start_date_formatted = date('Y-m-d', strtotime($fromDate)) . ' 00:00:00';
// $end_date_formatted = date('Y-m-d', strtotime($currentDate)) . ' 00:00:00';
// $select->where("BillDate >= ?", $start_date_formatted);
// $select->where("BillDate <= ?", $end_date_formatted);
// }
if (isset($post['Suppliername']) && !empty(trim($post['Suppliername']))) {
$select->where("tb2.SupplierName =?", trim($post['Suppliername']));
}
if (isset($post['Suppliermobile']) && !empty(trim($post['Suppliermobile']))) {
$select->where("tb2.ContactNo =?", trim($post['Suppliermobile']));
}
if (isset($post['Supplieremail']) && !empty(trim($post['Supplieremail']))) {
$select->where("tb2.EmailId =?", trim($post['Supplieremail']));
}
if (isset($post['BillNumber']) && !empty(trim($post['BillNumber']))) {
$select->where("tb1.BillNumber =?", trim($post['BillNumber']));
}
// $select->where("tb1.IsMarkForDel =?", 0);
$select->order(array('CreatedDate DESC'));
if ($pageType == 'Invoice') {
return $this->db->fetchAll($select);
} else {
// echo $select; exit;
return $select;
}
}
public function SupplierBillRow($BillId = null, $ICSourceSysId = null) {
$select = $this->db->select();
$selectarray = array('BillId', 'SupplierSysId', 'AgencySysId', 'AgentSysId', 'GstNumber', 'CONVERT(VARCHAR(24),BillDate,103) as BillDate', 'CONVERT(VARCHAR(24),BillDueDate,103) as BillDueDate', 'CONVERT(VARCHAR(20),BillDueDate,120) as BillDueDateDay', 'BillNumber', 'IsITCApplicable', 'CurrencyType', 'TotalCost', 'TotalTax', 'NetAmount', 'CreatedDate', 'Status', 'IsTDS ', 'PanNo', 'TDSAmount');
$supplierarray = array('SupplierSysId', 'ContactNo', 'ContId', 'countrycode', 'CitySysId', 'EmailId', 'Address', 'SupplierName', 'FirstName', "(RTRIM(LTRIM(tb2.FirstName)) + ' ' + RTRIM(LTRIM(tb2.LastName))) as FullName");
$Currencyarray = array('Symbol', 'Title', 'Icon');
$b2bagencyColumn = array('AgencyName as CompanyName', 'UserSysId', 'Signature', 'IsB2BAgent', 'GSTIN', 'PanNumber', 'Address as AgAddress', 'PinCode as AgPinCode', 'StateOrZoneSysId as AgStateOrZoneSysId', 'ContactNo1 as AgContact', 'EmailId as AgEmailId', 'CitySysId as AgCitySysId');
$select->from(array('tb1' => "TB_Supplier_Bill"), $selectarray);
$select->joinLeft(array('tb2' => "TB_IC_Supplier"), "tb1.SupplierSysId = tb2.SupplierSysId", $supplierarray);
$select->joinLeft(array('tb3' => "TB_Master_Currency"), "tb1.CurrencyType = tb3.CurrencyType", $Currencyarray);
$select->joinLeft(array('tb6' => "TB_Agency_User"), "tb6.UserSysId = tb1.AgencySysId", $b2bagencyColumn);
if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
$select->where("tb1.AgencySysId =?", $ICSourceSysId);
}
// if (!empty($TPSysId) && $TPSysId > 0) {
// $select->where("tb1.TPSysId =?", $TPSysId);
// }
// if (!empty($MasterTPSysId) && $MasterTPSysId > 0) {
// $select->where("tb1.MasterTPSysId =?", $MasterTPSysId);
// }
if (!empty($BillId) && $BillId > 0) {
$select->where("tb1.BillId =?", $BillId);
}
// $select->where("tb1.MasterMiscSysId =?", $MasterMiscSysId);
// $select->where("tb1.IsMarkForDel =?", 0);
$select->order(array('CreatedDate DESC'));
$result = $this->db->fetchRow($select);
return $result;
}
public function SupplierBillItemDetailList($BillId, $ICSourceSysId) {
$select = $this->db->select();
$selectarray = array('*');
$select->from(array('tb1' => "TB_Supplier_Bill_Items"), $selectarray);
$select->where("tb1.BillId =?", $BillId);
$select->order(array('BillItemId ASC'));
// echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
}