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('
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('
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('
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);
}
}
?>