Current File : /home/giriqfky/public_html/Barga/application/modules/jobs/models//Jobs_model_05_02_2019.php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Jobs_model extends CI_Model
{
var $table_job_posts = TABLE_JOB_POSTS;
var $table_app_users = TABLE_APP_USERS;
var $table_app_user_addresses =TABLE_APP_USER_ADDRESSES;
var $table_job_payments =TABLE_JOB_PAYMENTS;
var $table_completion_activities =TABLE_COMPLETION_ACTIVITIES;
var $table_job_transferred =TABLE_JOB_TRANSFERRED;
var $table_questionnaire_answers =TABLE_QUESTIONNAIRE_ANSWERS;
var $table_questionnaires =TABLE_QUESTIONNAIRES;
var $table_cities = TABLE_CITIES;
var $table_customer_ratings = TABLE_CUSTOMER_RATINGS;
var $table_post_messages = TABLE_POST_MESSAGES;
var $table_job_post_logs = TABLE_JOB_POST_LOGS;
var $table_service_provider_ratings = TABLE_SERVICE_PROVIDER_RATINGS;
var $table_services = TABLE_SERVICES;
var $table_service_quotes = TABLE_SERVICE_QUOTES;
public function __construct()
{
parent::__construct();
}
/** Job List code start**/
public function getJobList($params = array(),$app_user_id,$service_user_id,$dashboard_p){
$this->db->select('p.*,c.app_user_id AS cus_id,
c.user_profile_name ,c.user_mobile,s.user_profile_name ,s.user_mobile,ad.*');
$this->db->from($this->table_job_posts.' p');
$this->db->join($this->table_app_users.' c','c.app_user_id = p.customer_id','LEFT');
$this->db->join($this->table_app_users.' s','s.app_user_id = p.service_provider_id','LEFT');
$this->db->join($this->table_app_user_addresses.' ad','ad.app_address_id = p.app_address_id','LEFT');
if(!empty($params['search']['customer'])){
$this->db->like('c.user_profile_name',$params['search']['customer']);
$this->db->or_like('c.user_mobile',$params['search']['customer']);
}
if(!empty($params['search']['service_provider'])){
$this->db->like('s.user_profile_name',$params['search']['service_provider']);
$this->db->or_like('s.user_mobile',$params['search']['service_provider']);
}
if(!empty($params['search']['service_id'])){
$this->db->where('p.service_id',$params['search']['service_id']);
}
if(!empty($params['search']['job_post_date_from'])){
$job_post_date_from = getDateFromDatepicker($params['search']['job_post_date_from']);
$this->db->where("p.doc >= '".date($job_post_date_from)."'");
}
if(!empty($params['search']['job_post_date_to'])){
$job_post_date_to = getDateFromDatepicker($params['search']['job_post_date_to']);
$this->db->where("p.doc <= '".date($job_post_date_to)."23:59:59'");
}
if(!empty($params['search']['job_expiring_date_from'])){
$job_expiring_date_from = getDateFromDatepicker($params['search']['job_expiring_date_from']);
$this->db->where("p.job_expiry_date >= '".date($job_expiring_date_from)."'");
}
if(!empty($params['search']['job_expiring_date_to'])){
$job_expiring_date_to = getDateFromDatepicker($params['search']['job_expiring_date_to']);
$this->db->where("p.job_expiry_date <= '".date($job_expiring_date_to)."'");
}
if(!empty($params['search']['area_locality'])){
$this->db->like('ad.building_no',$params['search']['area_locality']);
$this->db->or_like('ad.street_name',$params['search']['area_locality']);
$this->db->or_like('ad.locality',$params['search']['area_locality']);
$this->db->or_like('ad.landmark',$params['search']['area_locality']);
$this->db->or_like('ad.pin_code',$params['search']['area_locality']);
$this->db->or_like('ad.city',$params['search']['area_locality']);
$this->db->or_like('ad.state',$params['search']['area_locality']);
}
if(!empty($params['search']['job_post_code'])){
$this->db->where('p.job_post_code',$params['search']['job_post_code']);
}
if(!empty($params['search']['job_status'])){
$this->db->where('p.job_post_status',$params['search']['job_status']);
}
//if($app_user_id != 0) { $this->db->where('c.app_user_id',$app_user_id); }
//if($service_user_id != 0) { $this->db->where('s.app_user_id',$service_user_id); }
//if($dashboard_p != 0){ $this->db->where('p.job_post_status',1); }
$this->db->order_by('p.doc','DESC');
if(array_key_exists("start",$params) && array_key_exists("limit",$params)) {
$this->db->limit($params['limit'],$params['start']);
} elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)) {
$this->db->limit($params['limit']);
}
//echo $this->db->last_query();
return $this->db->get()->result();
}
/** Job Enquery List code start**/
public function getJobEnqueryList($params = array(),$dashboard_p)
{
$this->db->select('sq.*,c.user_profile_name,s.service_name');
$this->db->from($this->table_service_quotes.' sq');
$this->db->join($this->table_app_users.' c','c.app_user_id = sq.customer_id','LEFT');
$this->db->join($this->table_services.' s','s.service_id = sq.service_id','LEFT');
if(!empty($params['search']['service_id'])) {
$this->db->where('sq.service_id',$params['search']['service_id']);
}
if(!empty($params['search']['customer'])) {
$this->db->like('c.user_profile_name',$params['search']['customer']);
$this->db->or_like('c.user_mobile',$params['search']['customer']);
}
if(!empty($params['search']['quote_title'])) {
$this->db->like('sq.quote_title',$params['search']['quote_title']);
}
//if($dashboard_p != 0){ $this->db->where('p.job_post_status',1); }
$this->db->order_by('sq.doc','DESC');
//set start and limit
if(array_key_exists("start",$params) && array_key_exists("limit",$params)) {
$this->db->limit($params['limit'],$params['start']);
} elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)) {
$this->db->limit($params['limit']);
}
return $this->db->get()->result();
}
public function getUserDetailsById($app_user_id)
{
$this->db->select("*");
$this->db->where('app_user_id',$app_user_id);
return $this->db->get($this->table_app_users)->row();
}
public function jobStatusSave($data_array, $id = 0)
{
if($id == 0) {
$this->db->insert($this->table_job_posts, $data_array);
$last_id = $this->db->insert_id();
} else {
$this->db->where('job_post_id', $id);
$this->db->update($this->table_job_posts, $data_array);
$last_id = $id;
}
return $last_id;
}
public function getAddressDetails($app_address_id)
{
$this->db->select("*");
$this->db->where('app_address_id',$app_address_id);
return $this->db->get($this->table_app_user_addresses)->row();
}
public function getPaymentDetailsById($job_post_id)
{
$this->db->select("*");
$this->db->where(array('job_post_id'=>$job_post_id,'payment_status'=>1));
return $this->db->get($this->table_job_payments);
}
public function deleteJob($job_post_id)
{
$this->db->trans_begin();
$foreign_off_sql = "SET FOREIGN_KEY_CHECKS = 0";
$this->db->query($foreign_off_sql);
$this->db->where('job_post_id',$job_post_id);
$this->db->delete($this->table_job_payments);
$this->db->where('job_post_id',$job_post_id);
$this->db->delete($this->table_completion_activities);
$this->db->where('job_post_id',$job_post_id);
$this->db->delete($this->table_job_transferred);
$this->db->where('job_post_id',$job_post_id);
$this->db->delete($this->table_questionnaire_answers);
$this->db->where('job_post_id',$job_post_id);
$this->db->delete($this->table_customer_ratings);
$this->db->where('job_post_id',$job_post_id);
$this->db->delete($this->table_post_messages);
$this->db->where('job_post_id',$job_post_id);
$this->db->delete($this->table_job_post_logs);
$this->db->where('job_post_id',$job_post_id);
$this->db->delete($this->table_service_provider_ratings);
$this->db->where('job_post_id',$job_post_id);
$this->db->delete($this->table_job_posts);
$foreign_on_sql = "SET FOREIGN_KEY_CHECKS = 1";
$this->db->query($foreign_on_sql);
if($this->db->trans_status() === FALSE){
$this->db->trans_rollback();
}else {
$this->db->trans_commit();
}
}
public function getJobMsg($job_post_id, $service_provider_id, $job_owner_id)
{
$app_user_image_path = base_url()."thumb.php?m=uploads/app_users/";
$this->db->select(
'pm.*,
auf.user_profile_name AS from_user_profile_name,
IF(auf.profile_image="","'.$app_user_image_path.'no_image.png&s=z",CONCAT("'.$app_user_image_path.'",auf.profile_image,"&s=z")) AS from_profile_image,
auf.app_user_type AS from_app_user_type,
aut.user_profile_name AS to_user_profile_name,
IF(aut.profile_image="","'.$app_user_image_path.'no_image.png&s=z",CONCAT("'.$app_user_image_path.'",aut.profile_image,"&s=z")) AS to_profile_image,
aut.app_user_type AS to_app_user_type,
jp.job_post_code
');
$this->db->from($this->table_post_messages. " AS pm");
$this->db->join($this->table_app_users.' auf', 'auf.app_user_id = pm.from_id');
$this->db->join($this->table_app_users.' aut', 'aut.app_user_id = pm.to_id');
$this->db->join($this->table_job_posts.' jp','jp.job_post_id = pm.job_post_id','LEFT');
$this->db->where('pm.job_post_id = '.$job_post_id.' AND ((from_id = '.$service_provider_id.' AND to_id = '.$job_owner_id.') OR (from_id = '.$job_owner_id.' AND to_id = '.$service_provider_id.'))');
$this->db->order_by('pm.doc', 'ASC');
return $this->db->get()->result();
}
public function getJobMsgCount($job_post_id, $service_provider_id, $job_owner_id)
{
$this->db->select('COUNT(*) AS total_post_messages');
$this->db->where('job_post_id = '.$job_post_id.' AND ((from_id = '.$service_provider_id.' AND to_id = '.$job_owner_id.') OR (from_id = '.$job_owner_id.' AND to_id = '.$service_provider_id.'))');
$post_messages_count = $this->db->get($this->table_post_messages)->row();
return $post_messages_count->total_post_messages;
}
/** Job List code end**/
/** Job form start**/
public function getJobData($job_post_id)
{
$this->db->select('j.*');
$this->db->where(array('job_post_id'=>$job_post_id));
return $this->db->get($this->table_job_posts.' j')->row();
}
public function getJobCompletionActivity($job_post_id)
{
$this->db->select('*');
$this->db->where(array('job_post_id'=>$job_post_id));
return $this->db->get($this->table_completion_activities);
}
public function getJobTransferDetails($job_post_id)
{
$this->db->select('*');
$this->db->where(array('job_post_id'=>$job_post_id));
return $this->db->get($this->table_job_transferred)->row();
}
public function getQuestionsAnsByJobId($job_post_id)
{
$this->db->select('a.*,q.*');
$this->db->from($this->table_questionnaire_answers.' a');
$this->db->where(array('a.job_post_id'=>$job_post_id));
$this->db->join($this->table_questionnaires.' q','q.questionnaire_id=a.questionnaire_id','LEFT');
return $this->db->get();
}
public function getLatLongByCity($city_name)
{
$this->db->select('*');
$this->db->from($this->table_cities);
$this->db->where('city_name',$city_name);
return $this->db->get()->row() ;
}
public function serviceDetailsByServiceId($service_id)
{
$this->db->select('*');
$this->db->from($this->table_services);
$this->db->where('service_id',$service_id);
return $this->db->get()->row() ;
}
public function getJobApplicationCount($job_post_id)
{
$this->db->select('*');
$this->db->from($this->table_job_post_logs);
$this->db->where(array('job_post_id'=>$job_post_id));
return $this->db->get()->num_rows();
}
public function showJobPostApplications($job_post_id)
{
$this->db->select('ar.*,ar.doc AS application_doc,m.user_profile_name,m.user_mobile,m.profile_image');
$this->db->from($this->table_job_post_logs. ' ar');
$this->db->join($this->table_app_users. ' m', 'm.app_user_id = ar.action_taken_id');
$this->db->where(array('ar.job_post_id' => $job_post_id));
$this->db->order_by('application_doc', 'DESC');
return $this->db->get()->result();
}
public function showJobActivitiesLog($job_post_id)
{
$this->db->select('*');
$this->db->where(array('job_post_id' => $job_post_id));
$this->db->order_by('job_completion_activity_id', 'DESC');
return $this->db->get($this->table_completion_activities)->result();
}
/** job form end**/
//// CSV ////
public function jobExportCsv($params)
{
$this->db->select('p.*,c.app_user_id AS cus_id,
c.user_profile_name AS customer_name,c.user_mobile AS customer_mobile,s.app_user_id AS ser_pro_id, s.user_profile_name AS service_pro_name,s.user_mobile AS service_pro_mobile, ad.*,sv.service_name');
$this->db->from($this->table_job_posts.' p');
$this->db->join($this->table_app_users.' c','c.app_user_id = p.customer_id','LEFT');
$this->db->join($this->table_app_users.' s','s.app_user_id = p.service_provider_id','LEFT');
$this->db->join($this->table_app_user_addresses.' ad','ad.app_address_id = p.app_address_id','LEFT');
$this->db->join($this->table_services.' sv','sv.service_id = p.service_id','LEFT');
if(!empty($params['search']['customer'])){
$this->db->like('c.user_profile_name',$params['search']['customer']);
$this->db->or_like('c.user_mobile',$params['search']['customer']);
}
if(!empty($params['search']['service_provider'])){
$this->db->like('s.user_profile_name',$params['search']['service_provider']);
$this->db->or_like('s.user_mobile',$params['search']['service_provider']);
}
if(!empty($params['search']['service_id'])){
$this->db->where('p.service_id',$params['search']['service_id']);
}
if(!empty($params['search']['job_post_date_from'])){
$job_post_date_from = getDateFromDatepicker($params['search']['job_post_date_from']);
$this->db->where("p.doc >= '".date($job_post_date_from)."'");
}
if(!empty($params['search']['job_post_date_to'])){
$job_post_date_to = getDateFromDatepicker($params['search']['job_post_date_to']);
$this->db->where("p.doc <= '".date($job_post_date_to)."23:59:59'");
}
if(!empty($params['search']['job_expiring_date_from'])){
$job_expiring_date_from = getDateFromDatepicker($params['search']['job_expiring_date_from']);
$this->db->where("p.job_expiry_date >= '".date($job_expiring_date_from)."'");
}
if(!empty($params['search']['job_expiring_date_to'])){
$job_expiring_date_to = getDateFromDatepicker($params['search']['job_expiring_date_to']);
$this->db->where("p.job_expiry_date <= '".date($job_expiring_date_to)."'");
}
if(!empty($params['search']['area_locality'])){
$this->db->like('ad.building_no',$params['search']['area_locality']);
$this->db->or_like('ad.street_name',$params['search']['area_locality']);
$this->db->or_like('ad.locality',$params['search']['area_locality']);
$this->db->or_like('ad.landmark',$params['search']['area_locality']);
$this->db->or_like('ad.pin_code',$params['search']['area_locality']);
$this->db->or_like('ad.city',$params['search']['area_locality']);
$this->db->or_like('ad.state',$params['search']['area_locality']);
}
if(!empty($params['search']['job_post_code'])){
$this->db->where('p.job_post_code',$params['search']['job_post_code']);
}
if(!empty($params['search']['job_status'])){
$this->db->where('p.job_post_status',$params['search']['job_status']);
}
$this->db->order_by('p.doc','DESC');
$rs = $this->db->get()->result();
// echo $this->db->last_query();exit;
return $rs;
}
//// CSV ////
}