File manager

File manager - Edit - /home/autoph/public_html/connectv1/lms/app/controllers/lead.php

Back
<?php class Lead{ function read_id_by_name_contact_person($array_data,$utility_class,$db){ return $db->sql_query("SELECT l.`id`, l.`corporation_name`, l.`firstname`, l.`middlename`, l.`lastname`, l.`gender_id`, l.`type_id`, l.`status`, c.`mobile`, c.`landline`, c.`email`, c.`address`, c.`facebook`, c.`instagram`, c.`whatsapp`, c.`viber`, c.`linkedin` FROM `lms2_leads` l INNER JOIN lms2_lead_contacts c ON l.id = c.lead_id WHERE 1 AND l.`type_id` = 1 AND c.mobile = '".$array_data['txt_lead_mobile']."' AND c.email = '".$array_data['txt_lead_email']."' AND l.status = 1 LIMIT 1 "); } function read_id_by_name_contact($array_data,$utility_class,$db){ $where_statement = " 0 %s /mobile/ %s /email/ %s /landline/ "; if(intval($array_data['type_person']) === 1){ $where_name = " AND l.`firstname` = '".$array_data['txt_lead_fname']."' AND l.`lastname` = '".$array_data['txt_lead_lname']."' "; }else if(intval($array_data['type_company']) === 1){ $where_name = " AND l.`corporation_name` = '".$array_data['txt_lead_company']."' "; } $mobile = $utility_class->isNotEmpty($array_data['txt_lead_mobile']) ? " OR c.mobile = '".$array_data['txt_lead_mobile']."' " : '' ; $email = $utility_class->isNotEmpty($array_data['txt_lead_email']) ? " OR c.email = '".$array_data['txt_lead_email']."' " : '' ; $landline = $utility_class->isNotEmpty($array_data['txt_lead_landline']) ? " OR c.landline = '".$array_data['txt_lead_landline']."' " : '' ; $where_statement = sprintf($where_statement,$mobile,$email,$landline); $query = "SELECT l.`id`, l.`corporation_name`, l.`firstname`, l.`middlename`, l.`lastname`, l.`gender_id`, l.`type_id`, l.`status`, c.`mobile`, c.`landline`, c.`email`, c.`address`, c.`facebook`, c.`instagram`, c.`whatsapp`, c.`viber`, c.`linkedin` FROM `lms2_leads` l INNER JOIN lms2_lead_contacts c ON l.id = c.lead_id WHERE 1 ".$where_name." AND l.`type_id` = 2 AND (".$where_statement.") AND l.status = 1 LIMIT 1"; return $db->sql_query($query); } function insert_lead_person($array_data,$utility_class,$db){ $query_lead = "INSERT INTO `lms2_leads`(`firstname`, `middlename`, `lastname`, `gender_id`, `type_id`, `status`) VALUES ( '".$array_data['txt_lead_fname']."', NULLIF('".$array_data['txt_lead_mname']."',''), '".$array_data['txt_lead_lname']."', NULLIF('".$array_data['dd_lead_gender']."',''), 1, 1) "; $lead_id = $db->sql_query_id($query_lead); $this->insert_lead_contact($lead_id,$array_data,$db); return $lead_id; } function insert_lead_company($array_data,$lead_source_id,$utility_class,$db){ $query_lead = "INSERT INTO `lms2_leads`(`corporation_name`, `type_id`, `source_id`, `status`) VALUES ( '".$array_data['txt_lead_company']."', 2, $lead_source_id, 1) "; $lead_id = $db->sql_query_id($query_lead); $this->insert_lead_contact($lead_id,$array_data,$db); return $lead_id; } function insert_lead_contact($lead_id,$array_data,$db){ $query_lead_contact = "INSERT INTO `lms2_lead_contacts`(`lead_id`, `mobile`, `landline`, `email`, `address`, `facebook`, `instagram`, `whatsapp`, `viber`, `linkedin`, `twitter`) VALUES ($lead_id, NULLIF('".$array_data['txt_lead_mobile']."',''), NULLIF('".$array_data['txt_lead_landline']."',''), NULLIF('".$array_data['txt_lead_email']."',''), NULLIF('".$array_data['txt_lead_address']."',''), NULLIF('".$array_data['txt_lead_facebook']."',''), NULLIF('".$array_data['txt_lead_instagram']."',''), NULLIF('',''), NULLIF('".$array_data['txt_lead_viber']."',''), NULLIF('".$array_data['txt_lead_linkedin']."',''), NULLIF('".$array_data['txt_lead_twitter']."','') )"; return $db->sql_query_affected_rows($query_lead_contact); } function update_lead_person($lead_id,$array_data,$utility_class,$db){ $query_lead = "UPDATE `lms2_leads` SET `firstname`='".$array_data['txt_lead_fname']."', `middlename`=NULLIF('".$array_data['txt_lead_mname']."',''), `lastname`='".$array_data['txt_lead_lname']."', `gender_id`=NULLIF('".$array_data['dd_lead_gender']."','') WHERE `id` = $lead_id"; $affected_rows = $db->sql_query_affected_rows($query_lead); $this->update_lead_contact($lead_id,$array_data,$db); return $affected_rows; } function update_lead_company($lead_id,$array_data,$utility_class,$db){ $query_lead = "UPDATE `lms2_leads` SET `corporation_name`='".$array_data['txt_lead_company']."' WHERE `id` = $lead_id"; $affected_rows = $db->sql_query_affected_rows($query_lead); $this->update_lead_contact($lead_id,$array_data,$db); return $affected_rows; } function update_lead_contact($lead_id,$array_data,$db){ $query = "UPDATE `lms2_lead_contacts` SET `mobile`=NULLIF('".$array_data['txt_lead_mobile']."',''), `landline`=NULLIF('".$array_data['txt_lead_landline']."',''), `email`=NULLIF('".$array_data['txt_lead_email']."',''), `address`=NULLIF('".$array_data['txt_lead_address']."',''), `facebook`=NULLIF('".$array_data['txt_lead_facebook']."',''), `instagram`=NULLIF('".$array_data['txt_lead_instagram']."',''), `whatsapp`=NULLIF('',''), `viber`=NULLIF('".$array_data['txt_lead_viber']."',''), `linkedin`=NULLIF('".$array_data['txt_lead_linkedin']."',''), `twitter`=NULLIF('".$array_data['txt_lead_twitter']."','') WHERE `lead_id` = $lead_id"; return $db->sql_query_affected_rows($query); } function insert_lead_company_business($lead_company_business_id,$lead_id, $inquire_id,$user_id,$utility_class,$db){ $query = "INSERT INTO `lms2_lead_company_business`(`lead_id`, `company_business_id`,`inquire_id`, `added_by`, `date_added`, `status`) VALUES ( $lead_id, $lead_company_business_id, $inquire_id, NULLIF('$user_id',''), NOW(), 1) "; return $db->sql_query_affected_rows($query); } function disable_all_lead_company_business_status($lead_id,$inquire_id,$utility_class,$db){ $query = "UPDATE `lms2_lead_company_business` SET `status`= 0 WHERE `lead_id`= $lead_id AND `inquire_id` = $inquire_id AND status = 1"; return $db->sql_query_affected_rows($query); } function update_lead_company_business($lead_company_business_id,$lead_id, $inquire_id,$user_id,$utility_class,$db){ $query = "UPDATE `lms2_lead_company_business` SET `company_business_id`=$lead_company_business_id, `status` = 1 WHERE 1 AND lead_id = $lead_id AND inquire_id = $inquire_id AND status = 1 "; return $db->sql_query_affected_rows($query); } function insert_lead_inquiry($lead_id,$user_id,$alert_id,$dealer_id,$utility_class,$db){ $query = "INSERT INTO `lms2_lead_inquiries`(`lead_id`, `dealer_id`, `json_alert_id`, `added_by`, `date_added`, `status`) VALUES ($lead_id,NULLIF('$dealer_id',''),NULLIF('$alert_id',''),NULLIF('$user_id',''),NOW(),1)"; // echo $query;exit; return $db->sql_query_id($query); } function update_lead_inquiry($inquire_id,$user_id,$alert_id,$dealer_id,$utility_class,$db){ $query = "UPDATE `lms2_lead_inquiries` SET `dealer_id`=NULLIF('$dealer_id',''), `json_alert_id`=NULLIF('$alert_id','') WHERE id = $inquire_id "; return $db->sql_query_affected_rows($query); } function insert_inquired_data($inquire_id,$model_id,$color_id,$quantity,$utility_class,$db){ $query = "INSERT INTO `lms2_lead_inquired_data`(`inquire_id`, `model_id`, `color_id`, `quantity`) VALUES ($inquire_id,NULLIF('$model_id',''),NULLIF('$color_id',''),$quantity)"; return $db->sql_query_affected_rows($query); } function update_inquired_data($inquire_id,$model_id,$color_id,$quantity,$utility_class,$db){ $query = "UPDATE `lms2_lead_inquired_data` SET `model_id`=NULLIF('$model_id',''), `color_id`=NULLIF('$color_id','') WHERE 1 AND inquire_id = $inquire_id "; return $db->sql_query_affected_rows($query); } function insert_lead_comment($inquiry_id,$user_id,$description,$utility_class,$db){ $query = "INSERT INTO `lms2_lead_comments`(`inquiry_id`, `description`, `added_by`, `date_added`, `status`) VALUES ($inquiry_id,NULLIF('$description',''), NULLIF('$user_id',''),NOW(),1)"; return $db->sql_query_affected_rows($query); } function update_lead_comment($inquiry_id,$user_id,$description,$utility_class,$db){ $query = "UPDATE `lms2_lead_comments` SET `description`=NULLIF('$description','') WHERE 1 AND `inquiry_id`=$inquiry_id AND status = 1 "; return $db->sql_query_affected_rows($query); } function disable_all_lead_comment_status($inquiry_id,$utility_class,$db){ $query = "UPDATE `lms2_lead_comments` SET `status`= 0 WHERE 1 AND `inquiry_id`=$inquiry_id AND status = 1 "; return $db->sql_query_affected_rows($query); } function lead_profile_query(){ return " SELECT l.id, li.id as inquiry_id, li.added_by, IF(l.type_id = 1,CONCAT(l.`firstname`,' ',l.`lastname`), IF(l.type_id = 2,l.`corporation_name`,'') ) as name, l.`corporation_name`, l.`firstname`, l.`middlename`, l.`lastname`, lead_cb.corporation_name as company_business, lead_cb.id as company_business_id, lg.name AS gender, lg.id AS gender_id, l.type_id as lead_type, lpt.name as type, lpt.level as type_level, lpth.class as html_class, DATE_FORMAT(la.date_added,'%Y-%m-%d') as date_added, lpd.name as progress, c.name as company, ld.name as dealer, lc.mobile, CONCAT('0',lc.mobile) as mobile_formatted, lc.landline, lc.email, lc.facebook, lc.instagram, lc.twitter, lc.linkedin, lc.viber, lc.address, ld.id AS dealer_id, ld.code AS dealer_code, vb.id AS brand_id, vb.name AS brand_name, vm.id AS model_id, vm.name AS model_name, vc.id AS color_id, vc.name AS color_name, lcomments.description AS comment, li.json_alert_id, li.inactive AS inactive_lead FROM `lms2_leads` l INNER JOIN lms2_lead_contacts lc ON l.id = lc.lead_id INNER JOIN `lms2_lead_inquiries` li ON l.id = li.lead_id INNER JOIN lms2_lead_activities la ON la.id = (SELECT ( SELECT id FROM lms2_lead_activities WHERE activity_description_id =( SELECT id FROM lms2_lead_progress_description WHERE lead_progress_type_id = lpt.id AND LEVEL = MAX(lpd.level) ) AND inquire_id = li.id LIMIT 1 /added if same max level/ ) AS activity FROM `lms2_lead_activities` la INNER JOIN lms2_lead_progress_description lpd ON la.`activity_description_id` = lpd.id INNER JOIN lms2_lead_progress_type lpt ON lpd.lead_progress_type_id = lpt.id WHERE lpt.status = 1 AND lpd.status = 1 AND la.status = 1 AND la.`inquire_id` = li.id AND lpt.id =( SELECT la.id FROM lms2_lead_progress_type la WHERE la.level =( SELECT MAX(lpt.level) FROM `lms2_lead_activities` la INNER JOIN lms2_lead_progress_description lpd ON la.`activity_description_id` = lpd.id INNER JOIN lms2_lead_progress_type lpt ON lpd.lead_progress_type_id = lpt.id WHERE la.`inquire_id` = li.id AND lpt.status = 1 AND lpd.status = 1 AND la.status = 1 ))) INNER JOIN lms2_lead_progress_description lpd ON la.activity_description_id = lpd.id INNER JOIN lms2_lead_progress_type lpt ON lpd.lead_progress_type_id = lpt.id LEFT OUTER JOIN lms2_lead_progress_type_html lpth ON lpt.id = lpth.lead_progress_type_id LEFT OUTER JOIN lms2_dealers ld ON li.dealer_id = ld.id LEFT OUTER JOIN lms2_companies c ON ld.company_id = c.id LEFT OUTER JOIN `lms2_lead_company_business` lcb ON lcb.id = (SELECT lcb1.id FROM lms2_lead_company_business lcb1 WHERE lcb1.lead_id = l.id AND lcb1.inquire_id = li.id AND lcb1.status = 1) LEFT OUTER JOIN `lms2_leads` lead_cb ON lcb.company_business_id = lead_cb.id LEFT OUTER JOIN `lms2_gender` lg ON l.gender_id = lg.id LEFT OUTER JOIN lms2_lead_inquired_data lid ON li.id = lid.inquire_id LEFT OUTER JOIN lms2_vehicle_models vm ON lid.model_id = vm.id LEFT OUTER JOIN lms2_vehicle_brands vb ON vm.brand_id = vb.id LEFT OUTER JOIN lms2_vehicle_colors vc ON lid.color_id = vc.id LEFT OUTER JOIN lms2_lead_comments lcomments ON lcomments.id = (SELECT lcomments1.id FROM lms2_lead_comments lcomments1 WHERE lcomments1.inquiry_id = li.id AND lcomments1.status = 1) WHERE 1 "; } function lead_latest_activity($inquire_id){ $query = " SELECT t1.id,t1.description,t1.activity_type FROM ( ( SELECT la.id,CONCAT(DATE_FORMAT(la.date_added,'%Y-%m-%d'), ' - ',lpd.name) as description,la.date_added,'progress' as activity_type FROM `lms2_lead_activities` la INNER JOIN lms2_lead_progress_description lpd ON la.`activity_description_id` = lpd.id WHERE 1 AND la.inquire_id = $inquire_id AND la.status = 1 ORDER BY la.date_added DESC LIMIT 1 ) UNION ALL ( SELECT n.id,CONCAT(DATE_FORMAT(n.date_added,'%Y-%m-%d'), ' - ','Add a note') as description,n.date_added,'note' as activity_type FROM `lms2_lead_notes` n WHERE 1 AND n.inquire_id = $inquire_id AND n.status = 1 ORDER BY n.date_added DESC LIMIT 1 ) UNION ALL ( SELECT ch.id,CONCAT(DATE_FORMAT(ch.date_added,'%Y-%m-%d'), ' - ','Call a client') as description,ch.date_added,'call' as activity_type FROM `lms2_lead_call_histories` ch WHERE 1 AND ch.inquire_id = $inquire_id AND ch.status = 1 ORDER BY ch.date_added DESC LIMIT 1 ) UNION ALL ( SELECT sh.id,CONCAT(DATE_FORMAT(sh.date_added,'%Y-%m-%d'), ' - ','Send sms to client ') as description,sh.date_added,'sms' as activity_type FROM `lms2_lead_sms_histories` sh WHERE 1 AND sh.inquire_id = $inquire_id AND sh.status = 1 ORDER BY sh.date_added DESC LIMIT 1 ) UNION ALL ( SELECT eh.id,CONCAT(DATE_FORMAT(eh.date_added,'%Y-%m-%d'), ' - ','Email a client') as description,eh.date_added,'email' as activity_type FROM `lms2_lead_email_histories` eh WHERE 1 AND eh.inquire_id = $inquire_id AND eh.status = 1 ORDER BY eh.date_added DESC LIMIT 1 ) UNION ALL ( SELECT it.id,CONCAT(DATE_FORMAT(it.date_added,'%Y-%m-%d'), ' - ','Tag client as inactive') as description,it.date_added,'inactive' as activity_type FROM `lms2_lead_inactive_tags` it WHERE 1 AND it.inquire_id = $inquire_id AND it.status = 1 ORDER BY it.date_added DESC LIMIT 1 ) ) t1 ORDER BY t1.date_added DESC LIMIT 1 "; // echo $query; return $query; } function lead_table($array_data,$owner_id,$utility_class,$db){ //get filters $filter_active_leads = ""; $filter_inactive_leads = ""; $filter_cold = ""; $filter_warm = ""; $filter_hot = ""; $filter_company_leads = ""; $filter_person_leads = ""; $filter_not_interested = ""; //check if from group if($owner_id === 0){ $filter_owner = ""; $filter_company = " AND c.id = ".$array_data['company']." "; }else{ $filter_owner= " AND li.added_by = $owner_id "; $filter_company = ""; } $filters = json_decode($array_data['filters']); if(count($filters) > 0){ foreach($filters as $filter){ switch($filter){ case "Active Leads": $filter_active_leads = " AND (li.inactive = 0 OR li.inactive IS NULL)"; break; case "Inactive Leads": $filter_inactive_leads = " AND li.inactive = 1 "; break; case "Cold": $filter_cold = " AND lpt.level = 1 "; break; case "Warm": $filter_warm = " AND lpt.level = 2 "; break; case "Hot": $filter_hot = " AND lpt.level = 3 "; break; case "Company Leads": $filter_company_leads = " AND l.type_id = 2 "; break; case "Person Leads": $filter_person_leads = " AND l.type_id = 1 "; break; case "Not Interested": $filter_not_interested = ""; break; } } } $offset_limit = "LIMIT ".$array_data['offset'].",".$array_data['limit']." "; $lead_profile_query = $this->lead_profile_query(); $fields = " * "; $fields_count = " COUNT(1) "; $query = "SELECT %s /fields/ FROM ( %s /* magic query / AND (CONCAT( IF(l.type_id = 1, CONCAT(l.`firstname`,' ',l.`lastname`,' ',l.`firstname`,' ',l.`middlename`,' ',l.`lastname`), IF(l.type_id = 2,l.`corporation_name`,'') ) ) LIKE '%%%s%%' OR lc.mobile = '%s' OR CONCAT('0',lc.mobile) = '%s' OR CONCAT('63',lc.mobile) = '%s' OR CONCAT('+63',lc.mobile) = '%s' OR lc.email = '%s') AND la.status = 1 %s /company/ %s /inactive/ %s /active/ %s /cold/ %s /warm/ %s /hot/ %s /person/ %s /company/ %s /owner/ ORDER BY li.date_added DESC %s /offest,limit/ ) as t1 "; $query_formatted = sprintf( $query, $fields, $lead_profile_query, $array_data['search'], $array_data['search'], $array_data['search'], $array_data['search'], $array_data['search'], $array_data['search'], $filter_company, $filter_inactive_leads, $filter_active_leads, $filter_cold, $filter_warm, $filter_hot, $filter_person_leads, $filter_company_leads, $filter_owner, $offset_limit ); $query_formatted_count = sprintf( $query, $fields_count, $lead_profile_query, $array_data['search'], $array_data['search'], $array_data['search'], $array_data['search'], $array_data['search'], $array_data['search'], $filter_company, $filter_inactive_leads, $filter_active_leads, $filter_cold, $filter_warm, $filter_hot, $filter_person_leads, $filter_company_leads, $filter_owner, '' ); // echo $query_formatted;exit; return array(intval($db->select($query_formatted_count)),$db->sql_query($query_formatted)); } function insert_lead_activity($inquiry_id,$user_id,$progress_description,$description,$utility_class,$db){ $query = "INSERT INTO `lms2_lead_activities`(`inquire_id`, `activity_description_id`, `description`, `added_by`, `date_added`, `status`) VALUES ($inquiry_id,$progress_description,NULLIF('$description',''), NULLIF('$user_id',''),NOW(),1)"; return $db->sql_query_affected_rows($query); } function lead_profile_v2($lead_id,$utility_class,$db){ $lead_profile_query = $this->lead_profile_query(); $fields = " t1.id as profile_id, t1.name as profile_name, t1.company as profile_company, t1.type as profile_progress_type, t1.type_level as profile_progress_type_level, t1.email as profile_email, CONCAT('mailto:',t1.email) as profile_href_email, t1.mobile_formatted as profile_mobile, CONCAT('tel:',t1.mobile_formatted) as profile_href_mobile, t1.landline as profile_landline, CONCAT('tel:',t1.landline) as profile_href_landline, t1.facebook as profile_facebook, CONCAT('https://facebook.com/',t1.facebook) as profile_href_facebook, t1.twitter as profile_twitter, CONCAT('https://twitter.com/',t1.twitter) as profile_href_twitter, t1.linkedin as profile_linkedin, CONCAT('https://www.linkedin.com/in/',t1.linkedin) as profile_href_linkedin, t1.instagram as profile_instagram, CONCAT('https://www.instagram.com/',t1.instagram) as profile_href_instagram, t1.viber as profile_viber, CONCAT('viber://chat?number=',t1.viber) as profile_href_viber, t1.html_class, t1.inactive_lead "; $query = "SELECT %s /fields/ FROM ( %s / magic query / AND l.id = %s ) as t1 "; $query_formatted = sprintf( $query, $fields, $lead_profile_query, $lead_id ); return $db->sql_query($query_formatted); } function lead_read_profile_form($lead_id,$utility_class,$db){ $lead_profile_query = $this->lead_profile_query(); $fields = " t1.id as lead_id, t1.lead_type, t1.firstname as txt_lead_fname, t1.middlename as txt_lead_mname, t1.lastname as txt_lead_lname, IF(t1.lead_type = '1',t1.company_business, IF(t1.lead_type = '2',t1.corporation_name,'')) as txt_lead_company, IF(t1.lead_type = '1',t1.company_business_id,'') as txt_lead_company_id, t1.address AS txt_lead_address, t1.gender, t1.gender_id, t1.mobile AS txt_lead_mobile, CONCAT('0',t1.mobile) AS txt_lead_mobile_formatted, t1.email AS txt_lead_email, t1.landline AS txt_lead_landline, t1.facebook as txt_lead_facebook, t1.instagram as txt_lead_instagram, t1.linkedin as txt_lead_linkedin, t1.viber as txt_lead_viber, t1.twitter as txt_lead_twitter, t1.dealer_id, t1.dealer_code, t1.brand_id, t1.brand_name, t1.model_id, t1.model_name, t1.color_id, t1.color_name, t1.comment AS txt_lead_comment, t1.json_alert_id, t1.added_by, t1.type_level "; $query = "SELECT %s /fields/ FROM ( %s / magic query / AND li.id = %s ) as t1 "; $query_formatted = sprintf( $query, $fields, $lead_profile_query, $lead_id ); // echo $query_formatted;exit; return $db->sql_query($query_formatted); } function lead_profile($lead_id,$utility_class,$db){ $query= "SELECT l.id, IF(l.`type_id` = 1, CONCAT(l.`firstname`,' ',l.`middlename`,' ',l.`lastname`), IF(l.`type_id` = 2,l.`corporation_name`,'')) as full_name, `corporation_name`, `firstname`, `middlename`, `lastname`, `type_id`, g.name as gender, IF(l.`type_id` = 1, CONCAT('0',lc.mobile), IF(l.`type_id` = 2,lc.mobile,'')) as mobile, lc.email, lc.address, lc.facebook, lc.instagram, lc.whatsapp, lc.viber, lc.linkedin, lc.twitter FROM `lms2_leads` l INNER JOIN lms2_lead_contacts lc ON l.id = lc.lead_id LEFT OUTER JOIN lms2_gender g ON l.gender_id = g.id WHERE 1 AND l.id = $lead_id AND l.status = 1"; return $db->sql_query($query); } function lead_inquiry_progress($inquire_id,$utility_class,$db){ $query = "SELECT MAX(lpt.level) as max_level FROM `lms2_lead_inquiries` li INNER JOIN `lms2_lead_activities` la ON li.id = la.inquire_id INNER JOIN lms2_lead_progress_description lpd ON la.activity_description_id = lpd.id INNER JOIN lms2_lead_progress_type lpt ON lpd.lead_progress_type_id = lpt.id WHERE 1 AND li.id = $inquire_id"; return $db->select($query); } function lead_inquiry_company($inquire_id,$utility_class,$db){ $query = "SELECT CONCAT(lc.code,' - ',lc.name) as company FROM `lms2_lead_inquiries` li INNER JOIN lms2_dealers ld ON li.`dealer_id` = ld.id INNER JOIN lms2_companies lc ON ld.company_id = lc.id WHERE 1 AND li.id = $inquire_id"; return $db->sql_query($query); } function lead_company_list($search,$utility_class,$db){ $query = "SELECT l.id,l.`corporation_name` FROM `lms2_leads` l INNER JOIN lms2_lead_contacts lc ON l.id = lc.lead_id WHERE 1 AND (l.corporation_name LIKE '%%%s%%' OR lc.mobile = '%s' OR CONCAT('0',lc.mobile) = '%s' OR CONCAT('63',lc.mobile) = '%s' OR CONCAT('+63',lc.mobile) = '%s' OR lc.email = '%s') AND l.`type_id` = 2 AND l.`status` = 1 GROUP BY l.`corporation_name` ORDER BY l.corporation_name ASC LIMIT 25"; $query_formatted = sprintf($query,$search,$search,$search,$search,$search,$search); return $db->sql_query($query_formatted); } function lead_activities_query($inquire_id){ $query = " ( SELECT la.id,la.inquire_id,CONCAT(lpd.name,IF(IFNULL(la.description,'') = '','',CONCAT(' - ',la.description))) as description,u.firstname,u.lastname,DATE_FORMAT(la.date_added,'%%M %%d, %%Y') as date_addedf,la.date_added,'Progress' as type,'progress' as activity_type FROM `lms2_lead_activities` la INNER JOIN lms2_lead_progress_description lpd ON la.`activity_description_id` = lpd.id INNER JOIN lms2_users u ON la.added_by = u.id WHERE 1 AND la.inquire_id = $inquire_id AND la.status = 1 ) UNION ALL ( SELECT n.id,n.inquire_id, CONCAT('Add a note', IF( IFNULL(n.description,'') = '', '', CONCAT('<br>Description: ',n.description)) ), u.firstname,u.lastname,DATE_FORMAT(n.date_added,'%%M %%d, %%Y') as date_addedf,n.date_added,'Notes' as type,'note' as activity_type FROM `lms2_lead_notes` n INNER JOIN lms2_users u ON n.added_by = u.id WHERE 1 AND n.inquire_id = $inquire_id AND n.status = 1 ) UNION ALL ( SELECT ch.id,ch.inquire_id, CONCAT('Call a client', IF(IFNULL(ch.call_via,'') = '','',CONCAT(' via ','(', ch.call_via ,')')), IF(IFNULL(ch.description,'') = '','',CONCAT('<br>Description: ',ch.description))), u.firstname,u.lastname,DATE_FORMAT(ch.date_added,'%%M %%d, %%Y') as date_addedf,ch.date_added,'Action' as type,'call' as activity_type FROM `lms2_lead_call_histories` ch INNER JOIN lms2_users u ON ch.added_by = u.id WHERE 1 AND ch.inquire_id = $inquire_id AND ch.status = 1 ) UNION ALL ( SELECT sh.id,sh.inquire_id, CONCAT('Send sms to client ' /,' ',IF(IFNULL(sh.description,'') = '','',CONCAT(' - ',sh.description))/ ), u.firstname,u.lastname,DATE_FORMAT(sh.date_added,'%%M %%d, %%Y') as date_addedf,sh.date_added,'Action' as type,'sms' as activity_type FROM `lms2_lead_sms_histories` sh INNER JOIN lms2_users u ON sh.added_by = u.id WHERE 1 AND sh.inquire_id = $inquire_id AND sh.status = 1 ) UNION ALL ( SELECT eh.id,eh.inquire_id, CONCAT('Email a client' /,' ',IF(IFNULL(eh.subject,'') = '','',CONCAT(' - Subject: ',eh.subject,' ')),IF(IFNULL(eh.description,'') = '','',CONCAT(' Message: ',eh.description,' '))/ ), u.firstname,u.lastname,DATE_FORMAT(eh.date_added,'%%M %%d, %%Y') as date_addedf,eh.date_added,'Action' as type,'email' as activity_type FROM `lms2_lead_email_histories` eh INNER JOIN lms2_users u ON eh.added_by = u.id WHERE 1 AND eh.inquire_id = $inquire_id AND eh.status = 1 ) UNION ALL ( SELECT it.id,it.inquire_id, CONCAT('Tag client as inactive',' ', IF( IFNULL(it.description,'') = '', '', CONCAT('<br>Reason: ',it.description) )), u.firstname,u.lastname,DATE_FORMAT(it.date_added,'%%M %%d, %%Y') as date_addedf,it.date_added,'Action' as type,'inactive' as activity_type FROM `lms2_lead_inactive_tags` it INNER JOIN lms2_users u ON it.added_by = u.id WHERE 1 AND it.inquire_id = $inquire_id AND it.status = 1 )"; // echo $query; return $query; } function remove_activity($activity_id,$table_name,$user_id,$utility_class,$db){ $query = "UPDATE $table_name SET status = 0 WHERE `id` = $activity_id "; return $db->sql_query_affected_rows($query); } function lead_activities($array_data,$utility_class,$db){ $inquire_id = $array_data['inquire_id']; $offset = $array_data['offset']; $limit = $array_data['limit']; $offset_limit = " LIMIT $offset,$limit "; $query = "SELECT FROM (".$this->lead_activities_query($inquire_id).") t1 %s /order/ %s /limit/ "; $query_formatted = sprintf($query,' ORDER BY `t1`.`date_added` DESC ',$offset_limit); // echo $query_formatted;exit; return $db->sql_query($query_formatted); } function lead_progress_list($utility_class,$db){ $query = "SELECT pt.id as type_id,pt.name as type_name,pd.id progress_type_id,pd.name as progress_type_name,pth.class as type_class,pt.level,pd.level FROM `lms2_lead_progress_type` pt INNER JOIN lms2_lead_progress_description pd ON pt.id = pd.lead_progress_type_id LEFT OUTER JOIN lms2_lead_progress_type_html pth ON pt.id = pth.lead_progress_type_id WHERE 1 AND pt.status = 1 AND pd.status = 1 ORDER BY pt.level ASC,pd.level ASC "; return $db->sql_query($query); } function get_progress_level_of_activity_id($id,$db){ $progress_obj = $db->sql_query("SELECT pt.`level`,pt.name,pd.name as type_name FROM `lms2_lead_progress_type` pt INNER JOIN lms2_lead_progress_description pd ON pt.id = pd.lead_progress_type_id WHERE 1 AND pd.id = $id LIMIT 1"); $progress = array(); foreach($progress_obj as $row){ $progress = $row; } return $progress; } function add_lead_progress($request,$user_id,$utility_class,$db){ $inquire_id = $request['inquire_id']; $activity_description_id= $request['progress_type']; $description = $db->escape($request['description']); $query = "INSERT INTO `lms2_lead_activities`( `inquire_id`, `activity_description_id`, `description`, `added_by`, `date_added`, `status`) VALUES ($inquire_id,$activity_description_id,NULLIF('$description',''),NULLIF('$user_id',''),NOW(),1)"; $this->activate_inquiry($inquire_id,$db); return $db -> sql_query_affected_rows($query); } function update_lead_progress($request,$user_id,$utility_class,$db){ $activity_id = $request['activity_id']; $activity_description_id= $request['progress_type']; $description = $db->escape($request['description']); $query = "UPDATE `lms2_lead_activities` SET `activity_description_id`=$activity_description_id,`description`=NULLIF('$description','') WHERE id = $activity_id"; // $this->activate_inquiry($inquire_id,$db); return $db -> sql_query_affected_rows($query); } function add_lead_note($array_data,$copy_arr,$inquire_id,$user_id,$utility_class,$db){ $description = $array_data['txt_lead_note_description']; $date_formatted_final = $utility_class -> toSqlDate($array_data['txt_reminder_date']); $time_formatted_final = $utility_class -> toSqlTime($array_data['txt_reminder_time']); $copy_json = json_encode($copy_arr); $query = "INSERT INTO `lms2_lead_notes`(`inquire_id`, `description`, `reminder_date`, `reminder_time`, `note_copy_json`, `added_by`, `date_added`, `status`) VALUES ($inquire_id,'$description',NULLIF('$date_formatted_final',''),NULLIF('$time_formatted_final',''),'$copy_json',$user_id,NOW(),1)"; $this->activate_inquiry($inquire_id,$db); return $db -> sql_query_affected_rows($query); } function update_lead_note($array_data,$activity_id,$user_id,$utility_class,$db){ $description = $array_data['txt_lead_note_description']; $date_formatted_final = $utility_class -> toSqlDate($array_data['txt_reminder_date']); $time_formatted_final = $utility_class -> toSqlTime($array_data['txt_reminder_time']); $copy_arr = array(); foreach($array_data as $key => $data){ if(substr($key, 0, 11) === "send_alert_"){ $copy_arr = array_merge($copy_arr,array(array($key => $data))); } } $copy_json = json_encode($copy_arr); $query = "UPDATE `lms2_lead_notes` SET `description`='$description',`reminder_date`=NULLIF('$date_formatted_final',''),`reminder_time`=NULLIF('$time_formatted_final',''),`note_copy_json`='$copy_json' WHERE id = $activity_id"; // $this->activate_inquiry($inquire_id,$db); return $db -> sql_query_affected_rows($query); } function add_lead_call($array_data,$inquire_id,$user_id,$utility_class,$db){ $description = $array_data['txt_lead_call_description']; $date_formatted_final = $utility_class -> toSqlDate($array_data['txt_follow_up_date']); $time_formatted_final = $utility_class -> toSqlTime($array_data['txt_follow_up_time']); $call_via_arr = array(); $call_arr = array(); foreach($array_data as $key => $data){ if(substr($key, 0, 9) === "call_via_"){ $call_via_arr = array_merge($call_via_arr,array(array($key => $data))); if(intval($data) !== 0 ){ $call_arr[] = substr($key, 9, strlen($key)); } } } $call_via_json = json_encode($call_via_arr); $call_via_value = implode(',', $call_arr); $query = "INSERT INTO `lms2_lead_call_histories`(`inquire_id`, `description`, `follow_up_date`, `follow_up_time`, `call_via`, `call_via_json`, `added_by`, `date_added`, `status`) VALUES ($inquire_id,'$description',NULLIF('$date_formatted_final',''),NULLIF('$time_formatted_final',''),NULLIF('$call_via_value',''),'$call_via_json',NULLIF('$user_id',''),NOW(),1)"; $this->activate_inquiry($inquire_id,$db); return $db -> sql_query_affected_rows($query); } function update_lead_call($array_data,$activity_id,$user_id,$utility_class,$db){ $description = $array_data['txt_lead_call_description']; $date_formatted_final = $utility_class -> toSqlDate($array_data['txt_follow_up_date']); $time_formatted_final = $utility_class -> toSqlTime($array_data['txt_follow_up_time']); $call_via_arr = array(); $call_arr = array(); foreach($array_data as $key => $data){ if(substr($key, 0, 9) === "call_via_"){ $call_via_arr = array_merge($call_via_arr,array(array($key => $data))); if(intval($data) !== 0 ){ $call_arr[] = substr($key, 9, strlen($key)); } } } $call_via_json = json_encode($call_via_arr); $call_via_value = implode(',', $call_arr); $query = "UPDATE `lms2_lead_call_histories` SET `description`='$description',`follow_up_date`=NULLIF('$date_formatted_final',''),`follow_up_time`=NULLIF('$time_formatted_final',''),`call_via`=NULLIF('$call_via_value',''),`call_via_json`='$call_via_json' WHERE id = $activity_id"; // $this->activate_inquiry($inquire_id,$db); return $db -> sql_query_affected_rows($query); } function add_lead_inactive($array_data,$inquire_id,$user_id,$utility_class,$db){ $description = $array_data['txt_lead_inactive_description']; $date_formatted_final = $utility_class -> toSqlDate($array_data['txt_revisit_date']); $query = "INSERT INTO `lms2_lead_inactive_tags`(`inquire_id`, `description`, `revisit_date`, `added_by`, `date_added`, `status`) VALUES ($inquire_id,'$description',NULLIF('$date_formatted_final',''),NULLIF('$user_id',''),NOW(),1)"; $this->deactivate_inquiry($inquire_id,$db); return $db -> sql_query_affected_rows($query); } function update_lead_inactive($array_data,$activity_id,$user_id,$utility_class,$db){ $description = $array_data['txt_lead_inactive_description']; $date_formatted_final = $utility_class -> toSqlDate($array_data['txt_revisit_date']); $query = "UPDATE `lms2_lead_inactive_tags` SET `description`='$description',`revisit_date`=NULLIF('$date_formatted_final','') WHERE id = $activity_id"; // $this->deactivate_inquiry($inquire_id,$db); return $db -> sql_query_affected_rows($query); } function add_lead_sms($array_data,$inquire_id,$user_id,$utility_class,$db){ $description = $array_data['txt_lead_sms_lead_description']; $query = "INSERT INTO `lms2_lead_sms_histories`(`inquire_id`, `description`, `added_by`, `date_added`, `status`) VALUES ($inquire_id,'$description',NULLIF('$user_id',''),NOW(),1)"; $this->activate_inquiry($inquire_id,$db); return $db -> sql_query_affected_rows($query); } function add_lead_mail($array_data,$inquire_id,$user_id,$utility_class,$db){ $subject = $array_data['txt_lead_mail_lead_subject']; $description = $array_data['txt_lead_mail_lead_description']; $query = "INSERT INTO `lms2_lead_email_histories`(`inquire_id`, `subject`, `description`, `added_by`, `date_added`, `status`) VALUES ($inquire_id,'$subject','$description',NULLIF('$user_id',''),NOW(),1)"; $this->activate_inquiry($inquire_id,$db); return $db -> sql_query_affected_rows($query); } function deactivate_inquiry($inquire_id,$db){ return $db->sql_query_affected_rows(" UPDATE `lms2_lead_inquiries` SET `inactive` = 1 WHERE `id` = $inquire_id "); } function activate_inquiry($inquire_id,$db){ return $db->sql_query_affected_rows(" UPDATE `lms2_lead_inquiries` SET `inactive` = 0 WHERE `id` = $inquire_id "); } function my_sms_template_list($user_id,$db){ $query = "SELECT * FROM `lms2_cnf_sms_templates` WHERE (`added_by` = $user_id OR `display_type` = 2) AND type = 1 AND status = 1 ORDER BY `name` ASC"; return $db->sql_query($query); } function my_mail_template_list($user_id,$db){ $query = "SELECT * FROM `lms2_cnf_mail_templates` WHERE (`added_by` = $user_id OR `display_type` = 2) AND type = 1 AND status = 1 ORDER BY `name` ASC"; return $db->sql_query($query); } function get_sms_template($sms_template_id,$db){ $query = "SELECT * FROM `lms2_cnf_sms_templates` WHERE `id` = $sms_template_id"; return $db -> sql_query($query); } function get_mail_template($sms_template_id,$db){ $query = "SELECT * FROM `lms2_cnf_mail_templates` WHERE `id` = $sms_template_id"; return $db -> sql_query($query); } function remove_sms_template($sms_template_id,$db){ $query = "UPDATE `lms2_cnf_sms_templates` SET `status`= 0 WHERE `id` = $sms_template_id"; return $db -> sql_query_affected_rows($query); } function remove_mail_template($mail_template_id,$db){ $query = "UPDATE `lms2_cnf_mail_templates` SET `status`= 0 WHERE `id` = $mail_template_id"; return $db -> sql_query_affected_rows($query); } function add_sms_lead_template($name,$description,$user_id,$utility_class,$db){ $query = "INSERT INTO `lms2_cnf_sms_templates`( `name`, `description`, `added_by`, `date_added`, `display_type`,`type`, `status`) VALUES ('$name','$description',NULLIF('$user_id',''),NOW(),1,1,1)"; return $db->sql_query_affected_rows($query); } function add_mail_lead_template($name,$subject,$description,$user_id,$utility_class,$db){ $query = "INSERT INTO `lms2_cnf_mail_templates`( `name`, `subject`, `description`, `added_by`, `date_added`, `display_type`,`type`, `status`) VALUES ('$name','$subject','$description',NULLIF('$user_id',''),NOW(),1,1,1)"; return $db->sql_query_affected_rows($query); } function get_inactive_data($activity_id,$db){ $query = "SELECT * FROM `lms2_lead_inactive_tags` WHERE 1 AND id = $activity_id"; return $db->sql_query($query); } function get_mail_data($activity_id,$db){ $query = "SELECT * FROM `lms2_lead_email_histories` WHERE 1 AND id = $activity_id"; return $db->sql_query($query); } function get_sms_data($activity_id,$db){ $query = "SELECT * FROM `lms2_lead_sms_histories` WHERE 1 AND id = $activity_id"; return $db->sql_query($query); } function get_call_data($activity_id,$db){ $query = "SELECT * FROM `lms2_lead_call_histories` WHERE 1 AND id = $activity_id"; return $db->sql_query($query); } function get_note_data($activity_id,$db){ $query = "SELECT * FROM `lms2_lead_notes` WHERE 1 AND id = $activity_id"; return $db->sql_query($query); } function get_progress_data($activity_id,$db){ $query = "SELECT * FROM `lms2_lead_activities` WHERE 1 AND id = $activity_id"; return $db->sql_query($query); } function read_name_by_id($inquire_id,$db){ $query = "SELECT IF(l.`type_id` = 1 , CONCAT(l.`firstname`,' ',l.`lastname`) , IF(l.`type_id` = 2 , l.`corporation_name` , '' ) ) FROM `lms2_leads` l INNER JOIN lms2_lead_inquiries li ON l.id = li.lead_id WHERE 1 AND li.`id` = $inquire_id LIMIT 1"; // echo $query;exit; return $db->select($query); } } ?>

| ver. 1.4 | . | PHP 7.3.33 | Generation time: 0.01 | proxy | phpinfo | Settings