home/autoph/public_html/connectv1/api/controllers/lms.php 0000644 00000016364 15030757704 0017643 0 ustar 00 select("SELECT id FROM `vehicle_color` WHERE status = 1 AND `name` = '$name' ");
}
function read_color($name, $db){
$colors = $db -> return_result("SELECT id,name FROM `vehicle_color` WHERE status = 1 AND `name` LIKE '%$name%' LIMIT 10 ");
$json = [];
foreach($colors as $color){
$json[] = ['id'=>$color['name'], 'text'=>$color['name']];
}
echo json_encode($json);
}
function read_color_name_by_id($id, $db){
return $db -> select("SELECT name FROM `vehicle_color` WHERE status = 1 AND `id` = '$id'");
}
function create_leads($lead_id,$corporation_name,$first_name,$middle_name,$last_name,$suffix_id,$gender_id,$date_of_birth,$type,$added_by,$asa_added_by,$source_id,$status,$utility, $db){
$query= "INSERT INTO `lms_leads`(
`id`,
`corporation_name`,
`first_name`,
`middle_name`,
`last_name`,
`suffix_id`,
`gender_id`,
`date_of_birth`,
`type`,
`added_by`,
`asa_added_by`,
`source_id`,
`status`)
VALUES ('$lead_id','$corporation_name','$first_name','$middle_name','$last_name','$suffix_id','$gender_id','$date_of_birth','$type','$added_by','$asa_added_by','$source_id','$status') ";
// echo $query;exit;
return $db -> sql_query_num_inserted($query);
}
function update_leads($lead_id,$corporation_name,$first_name,$middle_name,$last_name,$suffix_id,$gender_id,$date_of_birth,$type,$modified_by,$asa_added_by,$source_id,$status,$utility, $db){
$query = "UPDATE lms_leads SET
`corporation_name` = '$corporation_name',
`first_name` = '$first_name',
`middle_name` = '$middle_name',
`last_name` = '$last_name',
`suffix_id` = '$suffix_id',
`gender_id` = '$gender_id',
`date_of_birth` = '$date_of_birth',
`type` = '$type',
`modified_by` = '$modified_by' ,
`modified_date` = NOW() ,
`source_id` = '$source_id',
`status` = '$status'
WHERE id = '$lead_id' ";
// echo $query;exit;
return $db -> sql_query_num_inserted($query);
}
function create_leads_contact($lead_id,$mobile,$email,$facebook,$utility,$db){
$query = "INSERT INTO `lms_lead_contacts`(`lead_id`, `mobile`, `email`, `facebook`) VALUES ('$lead_id','$mobile','$email','$facebook')";
return $db -> sql_query_num_inserted($query);
}
function update_leads_contact($lead_id,$mobile,$email,$facebook,$utility,$db){
$query = "UPDATE lms_lead_contacts SET
`mobile` = '$mobile',
`email` = '$email',
`facebook` = '$facebook'
WHERE lead_id = '$lead_id' ";
return $db -> sql_query_num_inserted($query);
}
function update_leads_inquiry($lead_id,$asa_id,$asked_discount,$approve_discount,$inquired_unit,$utility,$db){
$query = "UPDATE lms_lead_inquire_details SET
`asked_discount` = '$asked_discount',
`approve_discount` = '$approve_discount',
`model_variant_name` = '$inquired_unit'
WHERE lead_id = '$lead_id' AND asa_id = '$asa_id' ";
return $db -> sql_query_num_inserted($query);
}
function create_leads_inquire_vehicle($lead_id,$employee_id,$asa_id,$brand_id,$model_id,$model_variant_id,$inquired_unit,$asked_discount,$approve_discount,$db){
$query = "INSERT INTO `lms_lead_inquire_details`(`lead_id`, `employee_id`, `asa_id`, `brand_id`, `model_id`, `model_variant_id`, `model_variant_name`,`asked_discount`,`approve_discount`) VALUES ('$lead_id','$employee_id','$asa_id','$brand_id','$model_id','$model_variant_id','$inquired_unit','$asked_discount','$approve_discount')";
return $db -> sql_query_id($query);
}
function update_leads_latest_activity_status($lead_id,$asa_id,$lead_status,$db){
$query = "UPDATE lms_lead_activities a
INNER JOIN
(
SELECT `inquire_id` , MAX(added_date) max_time
FROM lms_lead_activities
GROUP BY inquire_id
) b ON a.inquire_id = b.inquire_id AND
a.added_date = b.max_time
SET lead_status_id = '$lead_status'
WHERE a.inquire_id = (SELECT id FROM lms_lead_inquire_details WHERE lead_id = '$lead_id' AND asa_id = '$asa_id')"
;
// echo $query;exit;
return $db -> sql_query_id($query);
}
function create_leads_activity($inquire_id,$lead_status_id,$activity,$follow_up_date,$status,$utility,$db){
$query = "INSERT INTO `lms_lead_activities`(`inquire_id`, `lead_status_id`, `activity`, `follow_up_date`, `status`) VALUES ('$inquire_id','$lead_status_id','$activity',NULLIF('$follow_up_date',''),'$status')";
return $db -> sql_query_id($query);
}
function get_lead_type($lead_id,$db){
return $db->select("SELECT type FROM lms_leads WHERE id = '$lead_id' LIMIT 1");
}
function delete_leads($lead_id,$db){
$query = "DELETE FROM lms_leads WHERE id = '$lead_id' ";
return $db -> sql_query_num_inserted($query);
}
function delete_leads_contact($lead_id,$db){
$query = "DELETE FROM lms_lead_contacts WHERE lead_id = '$lead_id' ";
return $db -> sql_query_num_inserted($query);
}
function getAllInOneLMSQuery(){
return " SELECT
lld.asa_id,
ll.id as lead_id,
ll.corporation_name,
ll.first_name,
ll.last_name,
ll.middle_name,
ll.type as lead_type,
llc.email,
llc.mobile,
llc.facebook,
lld.model_variant_name as inquired_unit,
lld.asked_discount,
approve_discount,
lls.name as lead_status,
lla.follow_up_date,
1 as total,
IF(lls.id = 2, 1,0) as cold,
IF(lls.id = 3, 1,0) as warm,
IF(lls.id = 4, 1,0) as hot,
IF(lls.id = 5, 1,0) as loss,
IF(lls.id = 1, 1,0) as win
FROM lms_leads ll
INNER JOIN lms_lead_contacts llc ON
ll.id = llc.lead_id
INNER JOIN lms_lead_inquire_details lld
ON ll.id = lld.lead_id
INNER JOIN lms_lead_activities lla ON
lla.id = (
SELECT MAX(llas.id) AS maxsign FROM lms_lead_activities llas
INNER JOIN lms_lead_inquire_details llids
ON llas.inquire_id = llids.id
WHERE llids.id = lld.id
GROUP BY llas.inquire_id
)
INNER JOIN lms_lead_status lls
ON lla.lead_status_id = lls.id
WHERE 1
AND ll.status = 1
";
}
}
?>