File manager

File manager - Edit - /home/autoph/public_html/data03252025consolidation/api/controllers/customer.php

Back
<?php class Customer { function create_customer($array_data, $customer_id, $corporation_name, $salutation_id, $first_name, $middle_name, $last_name, $suffix_id, $gender_id, $nationality_id, $date_of_birth, $marital_status_id, $source_id, $mode_of_contact_id, $spouse_name, $occupation, $contact_person, $photo, $upload_by, $type, $category_id, $religion_id, $corporation_id, $profession_id, $nature_of_business_id, $status, $db) { //insertion implementation $aha_status = $array_data['aha_status']; $query_customer = "INSERT INTO `customer`(`id`, `corporation_name`, `salutation_id`, `first_name`, `middle_name`, `last_name`, `suffix_id`, `gender_id`, `nationality_id`, `date_of_birth`, `marital_status_id`, `source_id`, `mode_of_contact_id`, `spouse_name`, `occupation`, `contact_person`, `date_created`, `time_created`, `photo`, `upload_by`, `date_uploaded`, `modified_by`, `date_modified`, `type`, `category_id`, `religion_id`,`corporation_id`, `profession_id`,`nature_of_business_id`,`aha_status`,`status`) VALUES ('$customer_id','$corporation_name','$salutation_id','$first_name','$middle_name','$last_name','$suffix_id','$gender_id','$nationality_id','$date_of_birth','$marital_status_id','$source_id','$mode_of_contact_id','$spouse_name','$occupation','$contact_person', NOW(), NOW(), '$photo','$upload_by', NOW(), NULL, NULL, '$type','$category_id','$religion_id','$corporation_id','$profession_id','$nature_of_business_id',$aha_status,'$status')"; // echo $query_customer; $rows_affected = $db->sql_query_num_inserted($query_customer); // if($rows_affected > 0){ // $return_arr['message'] = 'Inserted'; // $return_arr['status'] = 1; // }else{ // $return_arr['message'] = 'Not Inserted'; // $return_arr['status'] = 0; // } return $rows_affected; } function read_customer($id, $search, $db) { //read implementation echo "Read " . $id; } function read_customer_profile($id, $db) { //read implementation $customer = $db->return_result("SELECT c.,snob.name as nature_of_business_name,sp.name as profession_name FROM `customer` c LEFT OUTER JOIN source_nature_of_business snob ON c.nature_of_business_id = snob.id LEFT OUTER JOIN source_profession sp ON c.profession_id = sp.id WHERE c.`id` = '$id' "); $json = array(); foreach ($customer as $customer_data) { $customer_corporation_name = $db->select("SELECT corporation_name FROM customer WHERE id = '" . $customer_data['corporation_id'] . "' AND status = 1"); $json = [ 'mod-firstname' => $customer_data['first_name'], 'mod-middlename' => $customer_data['middle_name'], 'mod-lastname' => $customer_data['last_name'], 'mod-dd-suffix' => $customer_data['suffix_id'], 'mod-corporation-name' => $customer_data['corporation_name'], 'mod-dd-customer-category' => $customer_data['category_id'], 'mod-birthdate' => $customer_data['date_of_birth'], 'mod-dd-gender' => $customer_data['gender_id'], 'mod-dd-religion' => $customer_data['religion_id'], 'mod-dd-nationality' => $customer_data['nationality_id'], 'mod-position' => $customer_data['occupation'], 'mod-dd-marital-status' => $customer_data['marital_status_id'], 'mod-spouse' => $customer_data['spouse_name'], 'mod-business-company' => $customer_corporation_name != '' ? $customer_data['corporation_id'] : 0, 'mod-business-company-name' => $customer_corporation_name, 'mod-dd-profession' => $customer_data['profession_id'], 'mod-dd-profession-name' => $customer_data['profession_name'], 'mod-nature-of-business' => $customer_data['nature_of_business_id'], 'mod-nature-of-business-name' => $customer_data['nature_of_business_name'], 'aha-status' => $customer_data['aha_status'] ]; } // echo json_encode($json); // echo $json['mod-business-company-name'].'sfdsf'; return $json; } function read_customer_profile_display($id, $granted_company_ids, $db) { //read implementation $query_customer_info = "SELECT DATE_FORMAT(ci.date_modified, '%M %d, %Y - %h:%i:%s %p') AS date_modified, em.first_name AS emfname, em.middle_name AS emmname, em.last_name AS emlname, ci.id, ci.corporation_id,snob.name as profession ,sr.name as religion, ci.corporation_name,ci.first_name, ci.middle_name, ci.last_name, g.gender_name, DATE_FORMAT(ci.date_created, '%M %d, %Y') AS date_created, cc.email_1, cc.email_2,cc.email_3, cc.mobile_phone_1, cc.mobile_phone_2,cc.mobile_phone_3, cc.residential_phone, cc.fax_phone, cc.business_phone, cc.landline, sx.suffix_name AS suffix, ac.nationality, ci.no_of_children, ci.occupation, ams.name AS marital_status, DATE_FORMAT(ci.date_of_birth, '%M %d, %Y') AS birthdate,ci.contact_person, ci.time_created, cc.address_1, cc.address_2, acity1.citymunDesc AS address_1_city, sc.code AS source_company, sd.code AS source_dealer, sdms.name AS source_dms, acity2.citymunDesc AS address_2_city, cc.address_1_postal, cc.address_2_postal, ci.photo, ci.spouse_name, DATE_FORMAT(ci.date_uploaded, '%M %d, %Y - %h:%i:%s %p') AS date_uploaded, e.first_name AS efname, e.middle_name AS emname, e.last_name AS elname, TIMESTAMPDIFF(YEAR,ci.date_of_birth, CURDATE()) as age_number , ci.type, cc.viber, cc.facebook, cc.instagram, cc.whatsapp, cc.telegram, cc.website,snb.name as nature_of_business, ( IF( ci.category_id <> 0,ccat.name, IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, 'SVO', IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, 'SAO', IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, 'LYL', IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, 'SLY', IF( COUNT(v.id) > 3, 'VIP','None'/VVIP/ ) ) ) ) ) ) ) as customer_category, IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) \|\| (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , '1','2' ) as isActive, smoc.name as mode_of_contact, sct.name as customer_type, ci.aha_status FROM customer ci LEFT OUTER JOIN customer_category ccat ON ci.category_id = ccat.id LEFT OUTER JOIN source_profession snob ON ci.profession_id=snob.id LEFT OUTER JOIN source_nature_of_business snb ON ci.nature_of_business_id=snb.id LEFT OUTER JOIN source_religion sr ON ci.religion_id = sr.id LEFT OUTER JOIN all_gender g ON ci.gender_id=g.id LEFT OUTER JOIN customer_contact cc ON ci.id=cc.customer_id LEFT OUTER JOIN all_suffix sx ON ci.suffix_id=sx.id LEFT OUTER JOIN all_countries ac ON ci.nationality_id=ac.num_code LEFT OUTER JOIN all_marital_status ams ON ci.marital_status_id=ams.id LEFT OUTER JOIN employee e ON ci.upload_by=e.id LEFT OUTER JOIN employee em ON ci.modified_by=em.id LEFT OUTER JOIN customer_dms cd ON ci.id=cd.customer_record_id LEFT OUTER JOIN source_company_dealer scd ON cd.company_dealer_id=scd.id LEFT OUTER JOIN source_company sc ON scd.company_id=sc.id LEFT OUTER JOIN source_dms sdms ON sc.dms_id=sdms.id LEFT OUTER JOIN source_dealer sd ON scd.dealer_id=sd.id LEFT OUTER JOIN all_city acity1 ON cc.address_1_city=acity1.id LEFT OUTER JOIN all_city acity2 ON cc.address_2_city=acity2.id LEFT OUTER JOIN source_customer_type sct ON ci.type=sct.id LEFT OUTER JOIN vehicle v ON ci.id = v.customer_record_id LEFT OUTER JOIN sales s ON v.id = s.vehicle_id LEFT OUTER JOIN service se ON v.id = se.vehicle_id LEFT OUTER JOIN source_mode_of_contact smoc ON cc.mode_of_contact_id = smoc.id WHERE ci.id='$id' ORDER BY ci.id ASC LIMIT 1"; //echo $query_customer_info; return; $involvements = "SELECT sd.name AS dms, sc.code AS company, sdr.code AS dealer FROM `customer_dms` cd LEFT OUTER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id LEFT OUTER JOIN source_company sc ON sc.id = scd.company_id LEFT OUTER JOIN source_dms sd ON sc.`dms_id` = sd.id LEFT OUTER JOIN source_dealer sdr ON sdr.id = scd.dealer_id WHERE `customer_record_id` IN ('$id') AND sc.id IN (" . $granted_company_ids . ") "; // echo $query_customer_info; // return; $customer_info = $db->return_result($query_customer_info); $suffix = ""; $in = $db->return_result($involvements); //echo $involvements; return; $dms = array(); $company = array(); $dealer = array(); foreach ($in as $data1) { $dms = array_merge($dms, array($data1['dms'])); $company = array_merge($company, array($data1['company'])); $dealer = array_merge($dealer, array($data1['dealer'])); } $dms_new = array_unique($dms); $company_new = array_unique($company); $dealer_new = array_unique($dealer); $list_dms = implode(', ', $dms_new); $list_company = implode(', ', $company_new); $list_dealer = implode(', ', $dealer_new); foreach ($customer_info as $data) { if (intval($data['corporation_id']) <= 0) { $customer_corporation_name = ''; } else { $customer_corporation_name = $db->select("SELECT corporation_name FROM customer WHERE id = '" . $data['corporation_id'] . "' AND status = 1"); } if ($data['suffix'] == "None") { $suffix = ""; } else { $suffix = " " . $data['suffix']; } $get_image_query = "SELECT vi.name FROM vehicle v INNER JOIN vehicle_images vi ON v.id = vi.vehicle_id INNER JOIN source_company_dealer scd ON v.company_dealer_id = scd.id WHERE 1 AND v.customer_record_id ='" . $data['id'] . "' AND scd.company_id IN (" . $granted_company_ids . ") AND vi.name <> 'default.png' AND vi.status <> 0 AND v.status <> 0 ORDER BY v.id DESC LIMIT 1"; // echo $get_image_query; $get_image_name = $db->select($get_image_query); if ($get_image_name <> '') { $data['photo'] = $get_image_name; } $arr['photo'] = $data['photo']; if (intval($data['type']) === 1) { $arr['main_corporation_name'] = ''; $arr['full_name'] = $data['first_name'] . " " . $data['middle_name'] . " " . $data['last_name'] . " " . $suffix; $arr['main_first_name'] = $data['first_name']; $arr['main_middle_name'] = $data['middle_name']; $arr['main_last_name'] = $data['last_name']; $arr['suffix'] = $suffix; } else { $arr['main_corporation_name'] = $data['corporation_name']; $arr['full_name'] = ''; $arr['main_first_name'] = ''; $arr['main_middle_name'] = ''; $arr['main_last_name'] = ''; $arr['suffix'] = ''; } $arr['customer_record_id'] = $data['id']; $arr['main_email'] = $data['email_1']; // $arr['main_customer_email_2'] = $data['email_2']; // MODIFY $arr['main_customer_email_2'] = $data['email_2'] .(strlen($data['email_3'])>0 ? ', '.$data['email_3'] : ''); $arr['information_religion'] = $data['religion']; $arr['main_customer_mobile'] = $data['mobile_phone_1']; $arr['main_customer_landline'] = $data['landline']; // $arr['main_customer_mobile_2'] = $data['mobile_phone_2']; // MODIFY $arr['main_customer_mobile_2'] = $data['mobile_phone_2'].(strlen($data['mobile_phone_3'])>0 ? ', '.$data['mobile_phone_3'] : ''); $arr['main_customer_home_mobile'] = $data['residential_phone']; $arr['fax_phone'] = $data['fax_phone']; $arr['main_customer_business_mobile'] = $data['business_phone']; $arr['information_gender'] = $data['gender_name']; $arr['information_date_created'] = $data['date_created']; // $arr['information_dob'] = $data['birthdate']; // $arr['information_nationality'] = $data['nationality']; $arr['no_of_children'] = $data['no_of_children']; $arr['information_age'] = $data['age_number']; $arr['information_position'] = $data['occupation']; $arr['information_marital_status'] = $data['marital_status']; $arr['information_spouse'] = $data['spouse_name']; $arr['contact_person'] = $data['contact_person']; $arr['time_created'] = $data['time_created']; $arr['information_source_company'] = $data['source_company']; $arr['information_source_dealer'] = $data['source_dealer']; $arr['information_source_dms'] = $data['source_dms']; $arr['main_address_1'] = $data['address_1']; $arr['main_address_2'] = $data['address_2']; $arr['main_city_1'] = $data['address_1_city']; $arr['main_city_2'] = $data['address_2_city']; $arr['main_postal_1'] = $data['address_1_postal']; $arr['main_postal_2'] = $data['address_2_postal']; $arr['main_customer_address_1'] = $arr['main_address_1'] . ' ' . $arr['main_city_1'] . ' ' . $arr['main_postal_1']; $arr['main_customer_address_2'] = $arr['main_address_2'] . ' ' . $arr['main_city_2'] . ' ' . $arr['main_postal_2']; $arr['information_uploaded_by'] = $data['efname'] . " " . $data['elname']; $arr['information_date_uploaded'] = $data['date_uploaded']; $arr['information_modified_by'] = $data['emfname'] . " " . $data['emlname']; $arr['information_date_modified'] = $data['date_modified']; $arr['customer_type'] = (intval($data['type']) === 1) ? 'Individual' : 'Corporation'; $arr['main_customer_type'] = $data['customer_type']; $arr['customer_type_id'] = $data['type']; $arr['main_category'] = $data['customer_category']; $arr['main_customer_status'] = (intval($data['isActive']) == 1 ? 'Active' : 'Inactive'); $arr['main_customer_aha_status'] = (intval($data['aha_status']) == 1 ? 'Registered' : 'Not Registered'); $arr['main_customer_moc'] = $data['mode_of_contact']; $arr['main_facebook'] = $data['facebook']; $arr['main_instagram'] = $data['instagram']; $arr['main_viber'] = $data['viber']; $arr['main_telegram'] = $data['telegram']; $arr['main_whatsapp'] = $data['whatsapp']; $arr['main_website_link'] = $data['website']; $arr['information_business_company_name'] = $customer_corporation_name; $arr['information_profession'] = $data['profession']; $arr['information_nature_of_work_business'] = $data['nature_of_business']; } $get_image_query = "SELECT si.name FROM sales s INNER JOIN vehicle_images si ON s.id = si.sales_id INNER JOIN vehicle v ON s.vehicle_id = v.id INNER JOIN source_company_dealer scd ON s.company_dealer_id = scd.id WHERE 1 AND v.customer_record_id ='$id' AND si.name <> 'default.png' AND vi.status <> 0 ORDER BY s.date_uploaded DESC LIMIT 1"; // echo $get_image_query; $get_image_name = $db->select($get_image_query); if ($get_image_name <> '') { $arr['photo'] = $get_image_name; } $arr['information_dms_list'] = $list_dms; $arr['information_company_list'] = $list_company; $arr['information_dealer_list'] = $list_dealer; return $arr; // echo json_encode($arr); } function read_customer_business($customer_id, $db) { $customer = $db->return_result("SELECT FROM `customer_business` WHERE `customer_id` = '$customer_id' "); $json = array(); foreach ($customer as $customer_data) { $json = [ 'mod-dd-profession' => $customer_data['profession_id'], 'mod-business-company' => $customer_data['name'] ]; } return $json; } function read_customer_contact($customer_id, $db) { $customer = $db->return_result("SELECT customer_contact.,smoc.name as mode_of_contact FROM `customer_contact` LEFT OUTER JOIN source_mode_of_contact smoc ON customer_contact.mode_of_contact_id = smoc.id WHERE customer_contact.`customer_id` = '$customer_id' "); $json = array(); foreach ($customer as $customer_data) { $json = [ 'mod-contact' => substr($customer_data['mobile_phone_1'], 3), 'mod-landline' => $customer_data['landline'], // 'mod-contact-corporation'=>$customer_data['mobile_phone_1'], // MODIFY // 'mod-other-mobile-number' => $customer_data['mobile_phone_2'], 'mod-other-mobile-number1' => substr($customer_data['mobile_phone_2'],3), 'mod-other-mobile-number2' => substr($customer_data['mobile_phone_3'],3), // END MODIFY 'mod-email' => $customer_data['email_1'], 'mod-other-email1' => $customer_data['email_2'], // MODIFY 'mod-other-email2' => $customer_data['email_3'], // END MODIFY 'mod-business-number' => $customer_data['business_phone'], 'mod-home-phone' => $customer_data['residential_phone'], 'mod-viber' => $customer_data['viber'], 'mod-telegram' => $customer_data['telegram'], 'mod-facebook' => $customer_data['facebook'], 'mod-instagram' => $customer_data['instagram'], 'mod-whatsapp' => $customer_data['whatsapp'], 'mod-website' => $customer_data['website'], 'mod-address-1' => $customer_data['address_1'], 'mod-dd-city-1' => $customer_data['address_1_city'], 'mod-postal-1' => $customer_data['address_1_postal'], 'mod-address-2' => $customer_data['address_2'], 'mod-dd-city-2' => $customer_data['address_2_city'], 'mod-postal-2' => $customer_data['address_2_postal'], 'mod-preferred-moc' => $customer_data['mode_of_contact_id'], 'mod-preferred-moc-name' => $customer_data['mode_of_contact'] ]; } // print_r($json);exit; return $json; } function read_customer_profile_interest($id, $db) { //read implementation $interests = $db->return_result("SELECT cil.customer_id,cic.name as interest_content,ci.id as interest_category,ci.name as interest_category_name FROM `customer_interest_list` cil INNER JOIN customer_interest_content cic ON cil.`interest_id` = cic.id INNER JOIN customer_interest ci ON cic.interest_id=ci.id WHERE cil.customer_id = '$id' GROUP BY cic.id ORDER BY cic.name ASC"); $json = []; foreach ($interests as $interest) { $json[] = [ 'customer_id' => $interest['customer_id'], 'interest_content' => $interest['interest_content'], 'interest_category' => $interest['interest_category'], 'interest_category_name' => $interest['interest_category_name'] ]; } echo json_encode($json); } function read_customer_info_and_contact($id, $db) { return $db->sql_query("SELECT FROM `customer` c INNER JOIN customer_contact cc ON c.`id` = cc.customer_id WHERE 1 AND c.id = $id LIMIT 1"); } function read_customer_affiliations($id, $db) { //read implementation $affiliations = $db->return_result("SELECT cil.customer_id,cic.name as affiliation_content,ci.id as affiliation_category,ci.name as affiliation_category_name FROM `customer_affiliations_list` cil INNER JOIN customer_affiliations_content cic ON cil.`affiliations_content_id` = cic.id INNER JOIN customer_affiliations_category ci ON cic.affiliation_id=ci.id WHERE cil.customer_id = '$id' GROUP BY cic.id ORDER BY cic.name ASC"); $json = []; foreach ($affiliations as $affiliation) { $json[] = [ 'customer_id' => $affiliation['customer_id'], 'affiliation_content' => $affiliation['affiliation_content'], 'affiliation_category' => $affiliation['affiliation_category'], 'affiliation_category_name' => $affiliation['affiliation_category_name'] ]; } echo json_encode($json); } function read_customer_dms($id, $granted_company_ids, $db) { //read implementation $customer_dms = $db->return_result("SELECT sc.dms_id, cd.`customer_dms_id`, sc.id AS company_id, CONCAT(sc.code,' - ',sc.name) AS company_name, sdr.id AS dealer_id, CONCAT(sdr.code,' - ',sdr.name) AS dealer_name FROM `customer_dms` cd INNER JOIN source_company_dealer scd ON cd.`company_dealer_id`= scd.id INNER JOIN source_company sc ON scd.company_id=sc.id INNER JOIN source_dms sd ON sc.`dms_id`=sd.id INNER JOIN source_dealer sdr ON scd.dealer_id=sdr.id WHERE `customer_record_id` = '$id' AND sc.id IN (" . $granted_company_ids . ") "); $json = []; foreach ($customer_dms as $customer_dms_row) { $json[] = [ 'dms_id' => $customer_dms_row['dms_id'], 'customer_dms_id' => explode("_", $customer_dms_row['customer_dms_id'])[0], 'company_id' => $customer_dms_row['company_id'], 'company_name' => $customer_dms_row['company_name'], 'dealer_id' => $customer_dms_row['dealer_id'], 'dealer_name' => $customer_dms_row['dealer_name'] ]; } echo json_encode($json); } function read_customer_contact_person($id, $db) { $customer = $db->return_result("SELECT * FROM `customer_contact_person` WHERE customer_id = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'full_name' => $row['full_name'], // 'first_name'=>$row['first_name'], // 'last_name'=>$row['last_name'], 'mobile_phone' => ($row['mobile_phone'] == '') ? '' : substr($row['mobile_phone'], 3), 'email' => $row['email'] ]; } echo json_encode($json); } function read_customer_children($id, $db) { $customer = $db->return_result("SELECT * FROM `customer_children` WHERE customer_id = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'first_name' => $row['first_name'], 'last_name' => $row['last_name'], 'mobile_phone' => ($row['mobile_phone'] <> '') ? substr($row['mobile_phone'], 3) : '', 'email' => $row['email'], 'date_of_birth' => (($row['date_of_birth'] <> '') ? date("m/d/Y", strtotime($row['date_of_birth'])) : '') ]; } echo json_encode($json); } function read_customer_education($id, $db) { echo "SELECT ss.name as name, ael.id as type, ael.name as type_name, ce.year_graduated FROM `customer_education` ce INNER JOIN source_school ss ON ce.`school_id` = ss.id INNER JOIN all_educational_level ael ON ce.type = ael.id WHERE ce.customer_id = '$id'"; exit; $customer = $db->return_result("SELECT ss.name as name, ael.id as type, ael.name as type_name, ce.year_graduated FROM `customer_education` ce INNER JOIN source_school ss ON ce.`school_id` = ss.id INNER JOIN all_educational_level ael ON ce.type = ael.id WHERE ce.customer_id = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'type_name' => $row['type_name'], 'type' => $row['type'], 'school' => $row['name'], 'year' => $row['year_graduated'], ]; } echo json_encode($json); } function read_customer_unit_association($id, $db) { $customer = $db->return_result("SELECT suat.id as type_id,sua.name, suat.name as type_name FROM `customer_unit_association` cua INNER JOIN source_unit_association sua ON cua.unit_association_id = sua.id INNER JOIN source_unit_association_type suat ON cua.type_id = suat.id WHERE cua.`customer_id` = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'type_name' => $row['type_name'], 'type' => $row['type_id'], 'name' => $row['name'], ]; } echo json_encode($json); } function read_customer_car_club($id, $db) { $customer = $db->return_result("SELECT * FROM `customer_car_club` ccc INNER JOIN source_car_clubs scc ON ccc.`car_club_id` = scc.id WHERE customer_id = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'name' => $row['name'], ]; } echo json_encode($json); } function read_customer_political_club($id, $db) { $customer = $db->return_result("SELECT * FROM `customer_political_club` t1 INNER JOIN source_political_clubs t2 ON t1.`political_club_id` = t2.id WHERE customer_id = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'name' => $row['name'], ]; } echo json_encode($json); } function read_customer_network_marketing($id, $db) { $customer = $db->return_result("SELECT * FROM `customer_network_marketing` t1 INNER JOIN source_network_marketing t2 ON t1.`network_marketing_id` = t2.id WHERE customer_id = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'name' => $row['name'], ]; } echo json_encode($json); } function read_customer_media_publication($id, $db) { $customer = $db->return_result("SELECT * FROM `customer_media_publications` t1 INNER JOIN source_media_publications t2 ON t1.`media_publication_id` = t2.id WHERE customer_id = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'name' => $row['name'], ]; } echo json_encode($json); } function read_customer_sme_group($id, $db) { $customer = $db->return_result("SELECT * FROM `customer_sme_groups` t1 INNER JOIN source_sme_groups t2 ON t1.`sme_group_id` = t2.id WHERE customer_id = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'name' => $row['name'], ]; } echo json_encode($json); } function read_customer_fraternities_sororities($id, $db) { $customer = $db->return_result("SELECT * FROM `customer_fraternities_sororities` t1 INNER JOIN source_fraternities_sororities t2 ON t1.`fraternities_sororities_id` = t2.id WHERE customer_id = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'name' => $row['name'], ]; } echo json_encode($json); } function read_customer_facebook_group($id, $db) { $customer = $db->return_result("SELECT * FROM `customer_facebook_groups` t1 INNER JOIN source_facebook_groups t2 ON t1.`facebook_group_id` = t2.id WHERE customer_id = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'name' => $row['name'], ]; } echo json_encode($json); } function read_customer_celebrity_influencers($id, $db) { $customer = $db->return_result("SELECT * FROM `customer_celebrity_influencers` t1 INNER JOIN source_celebrity_influencers t2 ON t1.`celebrity_influencer_id` = t2.id WHERE customer_id = '$id' "); $json = []; foreach ($customer as $row) { $json[] = [ 'name' => $row['name'], ]; } echo json_encode($json); } function read_customer_profile_dms($id, $db) { //read implementation $dmss = $db->return_result("SELECT cd.customer_record_id, sd.id, sd.name FROM `customer_dms` cd INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id INNER JOIN source_dms sd ON sc.`dms_id` = sd.id WHERE cd.customer_record_id = '$id' GROUP by sd.id"); $json = []; foreach ($dmss as $dms) { $json[] = ['customer_id' => $dms['customer_record_id'], 'dms_id' => $dms['id'], 'dms_name' => $dms['name']]; } echo json_encode($json); } function read_customer_profile_company($id, $db) { //read implementation $companies = $db->return_result("SELECT cd.customer_record_id, sc.id, sc.code,sc.name FROM `customer_dms` cd INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id WHERE cd.customer_record_id = '$id' GROUP BY sc.id"); $json = []; foreach ($companies as $company) { $json[] = ['customer_id' => $company['customer_record_id'], 'company_id' => $company['id'], 'company_name' => $company['code'] . ' - ' . $company['name']]; } echo json_encode($json); } function read_customer_profile_dealer($id, $db) { //read implementation $dealers = $db->return_result("SELECT cd.customer_record_id, sd.id, sd.code,sd.name FROM `customer_dms` cd INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_dealer sd ON scd.dealer_id = sd.id WHERE cd.customer_record_id = '$id' GROUP BY sd.id"); $json = []; foreach ($dealers as $dealer) { $json[] = ['customer_id' => $dealer['customer_record_id'], 'dealer_id' => $dealer['id'], 'dealer_name' => $dealer['code'] . ' - ' . $dealer['name']]; } echo json_encode($json); } function update_customer($array_data, $customer_id, $corporation_name, $salutation_id, $first_name, $middle_name, $last_name, $suffix_id, $gender_id, $nationality_id, $date_of_birth, $marital_status_id, $source_id, $mode_of_contact_id, $spouse_name, $occupation, $contact_person, $photo, $upload_by, $type, $category_id, $religion_id, $modified_by, $corporation_id, $profession_id, $nature_of_business_id, $status, $db) { $aha_status = $array_data['aha_status']; $query = "UPDATE `customer` SET `id`='$customer_id', `corporation_name`='$corporation_name', `salutation_id`='$salutation_id', `first_name`='$first_name', `middle_name`='$middle_name', `last_name`='$last_name', `suffix_id`='$suffix_id', `gender_id`='$gender_id', `nationality_id`='$nationality_id', `date_of_birth`='$date_of_birth', `marital_status_id`='$marital_status_id', `source_id`='$source_id', `mode_of_contact_id`='$mode_of_contact_id', `spouse_name`='$spouse_name', `no_of_children`='', `occupation`='$occupation', `contact_person`='', `modified_by`='$modified_by', `date_modified`= NOW(), `type`='$type', `category_id`='$category_id', `religion_id`='$religion_id', `corporation_id`='$corporation_id', `profession_id`='$profession_id', `nature_of_business_id`='$nature_of_business_id', `aha_status` = $aha_status, `status`='$status' WHERE id = '$customer_id' "; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function delete($id, $db) { return $db->sql_query_num_inserted("UPDATE customer SET status = 0 WHERE id = $id "); } function checkDuplicate() { echo "Check Duplicate "; } function create_customer_business($customer_id, $profession_id, $name, $address, $city_id, $postal, $status, $db) { $query_customer_business = "INSERT INTO `customer_business`(`customer_id`, `profession_id`, `name`, `address`, `city_id`, `postal`, `date_added`, `status`) VALUES ('$customer_id','$profession_id','$name','$address','$city_id','$postal',NOW(), '$status')"; $rows_affected = $db->sql_query_num_inserted($query_customer_business); return $rows_affected; } function update_customer_business($customer_id, $profession_id, $name, $address, $city_id, $postal, $status, $db) { $query = "UPDATE `customer_business` SET `customer_id`='$customer_id',`profession_id`='$profession_id',`name`='$name',`address`='$address',`city_id`='$city_id',`postal`='$postal',`status`='$status' WHERE customer_id = '$customer_id' "; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } // MODIFY function create_customer_contact($customer_id, $address_1, $address_2, $address_1_state, $address_2_state, $address_1_city, $address_2_city, $address_1_postal, $address_2_postal, $email_1, $email_2,$email_3, $viber, $facebook, $instagram, $telegram, $whatsapp, $other_social, $website, $business_phone, $fax_phone, $mobile_phone_1, $mobile_phone_2,$mobile_phone_3, $residential_phone, $mode_of_contact, $landline, $status, $db) { $query_customer_contact = "INSERT INTO `customer_contact`(`customer_id`, `address_1`, `address_2`, `address_1_state`, `address_2_state`, `address_1_city`, `address_2_city`, `address_1_postal`, `address_2_postal`, `email_1`, `email_2`,`email_3`,`viber`, `facebook`, `instagram`, `telegram`, `whatsapp`, `other_social`, `website`, `business_phone`, `fax_phone`, `mobile_phone_1`, `mobile_phone_2`,`mobile_phone_3`,`landline`, `residential_phone`, `mode_of_contact_id`, `status`) VALUES ('$customer_id','$address_1','$address_2','$address_1_state','$address_2_state','$address_1_city','$address_2_city','$address_1_postal','$address_2_postal','$email_1','$email_2','$email_3','$viber','$facebook','$instagram','$telegram','$whatsapp','$other_social','$website','$business_phone','$fax_phone','$mobile_phone_1','$mobile_phone_2','$mobile_phone_3','$landline','$residential_phone','$mode_of_contact','$status')"; $rows_affected = $db->sql_query_num_inserted($query_customer_contact); return $rows_affected; } //MODIFY function update_customer_contact($customer_id, $address_1, $address_2, $address_1_state, $address_2_state, $address_1_city, $address_2_city, $address_1_postal, $address_2_postal, $email_1, $email_2,$email_3, $viber, $facebook, $instagram, $telegram, $whatsapp, $other_social, $website, $business_phone, $fax_phone, $mobile_phone_1, $mobile_phone_2,$mobile_phone_3, $residential_phone, $mode_of_contact, $landline, $status, $db) { $query = "UPDATE `customer_contact` SET `customer_id`='$customer_id', `address_1`='$address_1', `address_2`='$address_2', `address_1_state`='$address_1_state', `address_2_state`='$address_2_state', `address_1_city`='$address_1_city', `address_2_city`='$address_2_city', `address_1_postal`='$address_1_postal', `address_2_postal`='$address_2_postal', `email_1`='$email_1', `email_2`='$email_2', `email_3`='$email_3', `viber`='$viber', `facebook`='$facebook', `instagram`='$instagram', `telegram`='$telegram', `whatsapp`='$whatsapp', `other_social`='$other_social', `website`='$website', `business_phone`='$business_phone', `fax_phone`='$fax_phone', `mobile_phone_1`='$mobile_phone_1', `mobile_phone_2`='$mobile_phone_2', `mobile_phone_3`='$mobile_phone_3', `landline`= '$landline', `residential_phone`='$residential_phone', `date_modified`=NOW(), `mode_of_contact_id`='$mode_of_contact', `status`='$status' WHERE `customer_id` = '$customer_id' "; // echo $query; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function create_customer_contact_person($data_to_insert, $db) { $query_customer_contact_person = "INSERT INTO `customer_contact_person`(`customer_id`, `full_name`, `email`, `mobile_phone`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query_customer_contact_person); return $rows_affected; } function delete_contact_person($customer_id, $db) { $query_customer_contact_person = "DELETE FROM `customer_contact_person` WHERE `customer_id` = '$customer_id' "; $rows_affected = $db->sql_query_num_inserted($query_customer_contact_person); return $rows_affected; } function create_customer_children($data_to_insert, $db) { $query_customer_children = "INSERT INTO `customer_children`(`customer_id`, `first_name`, `last_name`, `date_of_birth`, `mobile_phone`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query_customer_children); return $rows_affected; } function delete_customer_children($customer_id, $db) { $query_customer_children = "DELETE FROM `customer_children` WHERE `customer_id` = '$customer_id'"; $rows_affected = $db->sql_query_num_inserted($query_customer_children); return $rows_affected; } function create_customer_interest_list($data_to_insert, $db) { $query_customer_interest_list = "INSERT INTO `customer_interest_list`(`interest_id`, `customer_id`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query_customer_interest_list); return $rows_affected; } function delete_customer_interest_list($customer_id, $db) { $query_customer_interest_list = "DELETE FROM `customer_interest_list` WHERE `customer_id` = '$customer_id' "; $rows_affected = $db->sql_query_num_inserted($query_customer_interest_list); return $rows_affected; } function create_customer_affiliations_list($data_to_insert, $db) { $query_customer_affiliations_list = "INSERT INTO `customer_affiliations_list`(`affiliations_content_id`, `customer_id`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query_customer_affiliations_list); return $rows_affected; } function delete_customer_affiliations_list($customer_id, $db) { $query_customer_affiliations_list = "DELETE FROM `customer_affiliations_list` WHERE `customer_id` = '$customer_id' "; $rows_affected = $db->sql_query_num_inserted($query_customer_affiliations_list); return $rows_affected; } function create_customer_education($data_to_insert, $db) { $query_customer_education = "INSERT INTO `customer_education`(`customer_id`, `school_id`, `type`, `year_graduated`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query_customer_education); return $rows_affected; } function delete_customer_education($customer_id, $db) { $query_customer_education = "DELETE FROM `customer_education` WHERE `customer_id` = '$customer_id' "; $rows_affected = $db->sql_query_num_inserted($query_customer_education); return $rows_affected; } function create_customer_car_club($data_to_insert, $db) { $query = "INSERT INTO `customer_car_club`(`customer_id`,`car_club_id`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function delete_customer_car_club($customer_id, $db) { $query = "DELETE FROM `customer_car_club` WHERE `customer_id` = '$customer_id' "; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function create_customer_unit_association($data_to_insert, $db) { $query = "INSERT INTO `customer_unit_association`(`customer_id`,`unit_association_id`, `type_id`,`status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function delete_customer_unit_association($customer_id, $db) { $query = "DELETE FROM `customer_unit_association` WHERE `customer_id` = '$customer_id'"; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function create_customer_political_club($data_to_insert, $db) { $query = "INSERT INTO `customer_political_club`(`customer_id`,`political_club_id`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function delete_customer_political_club($customer_id, $db) { $query = "DELETE FROM `customer_political_club` WHERE `customer_id` = '$customer_id'"; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function create_customer_network_marketing($data_to_insert, $db) { $query = "INSERT INTO `customer_network_marketing`(`customer_id`,`network_marketing_id`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function delete_customer_network_marketing($customer_id, $db) { $query = "DELETE FROM `customer_network_marketing` WHERE `customer_id` = '$customer_id'"; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function create_customer_media_publication($data_to_insert, $db) { $query = "INSERT INTO `customer_media_publications`(`customer_id`,`media_publication_id`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function delete_customer_media_publication($customer_id, $db) { $query = "DELETE FROM `customer_media_publications` WHERE `customer_id` = '$customer_id'"; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function create_customer_sme_group($data_to_insert, $db) { $query = "INSERT INTO `customer_sme_groups`(`customer_id`,`sme_group_id`, `status`) VALUES " . $data_to_insert . ""; // echo $query;exit; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function delete_customer_sme_group($customer_id, $db) { $query = "DELETE FROM `customer_sme_groups` WHERE `customer_id` = '$customer_id'"; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function create_customer_fraternities_sororities($data_to_insert, $db) { $query = "INSERT INTO `customer_fraternities_sororities`(`customer_id`,`fraternities_sororities_id`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function delete_customer_fraternities_sororities($customer_id, $db) { $query = "DELETE FROM `customer_fraternities_sororities` WHERE `customer_id` = '$customer_id'"; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function create_customer_facebook_group($data_to_insert, $db) { $query = "INSERT INTO `customer_facebook_groups`(`customer_id`,`facebook_group_id`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function delete_customer_facebook_group($customer_id, $db) { $query = "DELETE FROM `customer_facebook_groups` WHERE `customer_id` = '$customer_id'"; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function create_customer_celebrity_influencer($data_to_insert, $db) { $query = "INSERT INTO `customer_celebrity_influencers`(`customer_id`,`celebrity_influencer_id`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function delete_customer_celebrity_influencer($customer_id, $db) { $query = "DELETE FROM `customer_celebrity_influencers` WHERE `customer_id` = '$customer_id'"; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function create_customer_dms($data_to_insert, $db) { $query = "INSERT INTO `customer_dms`(`customer_record_id`, `customer_dms_id`, `company_dealer_id`, `status`) VALUES " . $data_to_insert . ""; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function delete_customer_dms($customer_id, $granted_company_ids, $db) { $query = "DELETE cd FROM `customer_dms` cd INNER JOIN source_company_dealer scd ON cd.`company_dealer_id` = scd.id WHERE cd.`customer_record_id` = '$customer_id' AND scd.company_id IN (" . $granted_company_ids . ") "; $rows_affected = $db->sql_query_num_inserted($query); return $rows_affected; } function read_customer_email_if_exist($email, $db) { $query = "SELECT 1 FROM `customer_contact` cc INNER JOIN customer c ON cc.customer_id = c.id WHERE 1 AND cc.`email_1` = '$email' AND c.type = 1 AND c.status = 1"; return $db->sql_query($query)->num_rows; } function read_customer_mobile_if_exist($mobile, $db) { $query = "SELECT 1 FROM `customer_contact` cc INNER JOIN customer c ON cc.customer_id = c.id WHERE 1 AND `mobile_phone_1` = '$mobile' AND c.type = 1 AND c.status = 1"; return $db->sql_query($query)->num_rows; } function read_customer_main_mobile_by_id($customer_id, $db) { $query = "SELECT cc.mobile_phone_1 FROM `customer_contact` cc INNER JOIN customer c ON cc.customer_id = c.id WHERE 1 AND c.`id` = '$customer_id' AND c.status=1 "; return $db->select($query); } function read_customer_main_email_by_id($customer_id, $db) { $query = "SELECT cc.email_1 FROM `customer_contact` cc INNER JOIN customer c ON cc.customer_id = c.id WHERE 1 AND c.`id` = '$customer_id' AND c.status=1 "; return $db->select($query); } function get_customer_dms_company_dealer_name_by_customer_dms_id($customer_dms_id, $db) { $query = "SELECT cd.customer_record_id, sd.name as dms_name,sc.name as company_name,sdr.name as dealer_name FROM `customer_dms` cd INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id INNER JOIN source_dms sd ON sc.dms_id = sd.id INNER JOIN source_dealer sdr ON scd.dealer_id = sdr.id WHERE 1 AND cd.customer_dms_id = '$customer_dms_id' "; $customer_dms_informations = $db->return_result($query); $json = array(); foreach ($customer_dms_informations as $customer_dms_information) { $json = ['customer_record_id' => $customer_dms_information['customer_record_id'], 'dms_name' => $customer_dms_information['dms_name'], 'company_name' => $customer_dms_information['company_name'], 'dealer_name' => $customer_dms_information['dealer_name']]; } return $json; } function read_customer_record_id_by_customer_dms_id($customer_dms_id, $db) { return $db->select("SELECT `customer_record_id` FROM `customer_dms` WHERE `customer_dms_id` = '$customer_dms_id' LIMIT 1"); } function read_customer_name_by_id($id, $db) { $customer = $db->return_result("SELECT * FROM `customer` WHERE `id` = '$id' AND status = 1"); $json = array(); foreach ($customer as $customer_data) { $json = [ 'first_name' => $customer_data['first_name'], 'middle_name' => $customer_data['middle_name'], 'last_name' => $customer_data['last_name'], 'corporation_name' => $customer_data['corporation_name'], 'type' => $customer_data['type'] ]; } return $json; } function read_customer_vehicle_ids($customer_id, $db) { $query = "SELECT id FROM vehicle WHERE customer_record_id = '$customer_id' "; // echo $query; return $db->return_result($query); } function read_customer_sales_image_by_vehicle_ids($granted_company_ids, $vehicle_ids, $db) { $query = "SELECT si.name,DATE_FORMAT(si.date_added, '%M %d, %Y - %h:%i:%s %p') as activity_date FROM vehicle_images si INNER JOIN vehicle v ON si.vehicle_id = v.id INNER JOIN source_company_dealer scd ON v.company_dealer_id = scd.id WHERE si.name <> 'default.png' AND si.status <> 0 AND si.vehicle_id IN (" . $vehicle_ids . ") AND scd.company_id IN (" . $granted_company_ids . ")"; // echo $query;exit; return $db->return_result($query); } function read_customer_corporation($search, $granted_company_ids, $db) { $corporations = $db->return_result("SELECT c.id, c.`corporation_name` FROM `customer` c INNER JOIN customer_dms cd ON c.id = cd.customer_record_id INNER JOIN customer_contact cc ON c.id = cc.customer_id INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id WHERE ( CONCAT( c.id, ' ', c.corporation_name, ' ', cc.mobile_phone_1, ' ', CONCAT('0',SUBSTRING(cc.mobile_phone_1, 4)) ) LIKE '%$search%' OR cc.email_1 = '" . $search . "' ) AND c.type=2 AND c.status=1 AND scd.company_id IN (" . $granted_company_ids . ") GROUP BY c.id ORDER BY c.corporation_name ASC LIMIT 20"); $json = []; foreach ($corporations as $corporation) { $json[] = ['id' => $corporation['id'], 'text' => $corporation['corporation_name']]; } echo json_encode($json); } function read_customer_names_and_id($granted_company_ids, $search, $db) { $customers = $db->return_result("SELECT c.,c.id as customer_record_id,cc. FROM `customer` c INNER JOIN customer_dms cd ON c.id = cd.customer_record_id INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN customer_contact cc ON c.id = cc.customer_id WHERE 1 AND (CONCAT(c.id,' ', CONCAT( c.corporation_name, ' ', c.first_name, ' ', c.last_name , ' ', c.first_name, ' ', c.middle_name, ' ', c.last_name ) ,' ', concat('0',SUBSTRING(cc.mobile_phone_1, 4)),' ',cc.mobile_phone_1) LIKE '%$search%' OR cc.email_1 = '" . $search . "') AND scd.company_id IN (" . $granted_company_ids . ") AND c.status = 1 GROUP BY c.id ORDER BY c.last_name,c.corporation_name ASC LIMIT 25"); $json = []; foreach ($customers as $customer) { $name = (intval($customer['type']) == 1) ? $customer['first_name'] . ' ' . $customer['middle_name'] . ' ' . $customer['last_name'] : $customer['corporation_name']; $json[] = ['id' => $customer['customer_record_id'], 'text' => $name]; } echo json_encode($json); } function read_customer_name_and_id_by_mobile_number($mobile, $db) { $query = "SELECT c.id,`corporation_name`,`first_name`,`last_name`,`type` FROM `customer` c INNER JOIN customer_contact cc ON c.`id` = cc.customer_id WHERE cc.mobile_phone_1 = '$mobile' GROUP BY c.id AND c.status = 1 LIMIT 1"; return $db->return_result($query)[0]; } function read_customer_dms_company_dealer($customer_id, $dms_id, $company_dealer_id, $db) { $query = "SELECT COUNT(1) FROM `customer_dms` cd INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id WHERE sc.`dms_id` = '$dms_id' AND cd.`company_dealer_id` = '$company_dealer_id' AND cd.customer_record_id = '$customer_id' "; return $db->select($query); } function read_customer_dms_company_dealer_name($dms_id, $company_id, $dealer_id, $db) { $dms_company_dealer = array(); $dms_company_dealer['dms'] = $db->select("SELECT name FROM source_dms WHERE id = '$dms_id' "); $dms_company_dealer['company'] = $db->select("SELECT name FROM source_company WHERE id = '$company_id' "); $dms_company_dealer['dealer'] = $db->select("SELECT name FROM source_dealer WHERE id = '$dealer_id' "); return $dms_company_dealer; } function read_customer_name_if_exist($name, $db) { $query = "SELECT 1 FROM `customer` WHERE `corporation_name` = '$name' AND status = 1"; return $db->sql_query($query)->num_rows; } function read_customer_info_and_id_by_name($name, $db) { $query = "SELECT c.id,c.`corporation_name` FROM `customer` c WHERE c.`corporation_name` = '$name' GROUP BY c.id AND c.status = 1 LIMIT 1 "; return $db->return_result($query)[0]; } function delete_customer_no_connections($db) { $db->sql_query("DELETE c FROM customer c LEFT OUTER JOIN customer_contact cc ON c.id = cc.customer_id WHERE cc.id IS NULL;"); $db->sql_query("DELETE c FROM customer c LEFT OUTER JOIN customer_dms cd ON c.id = cd.customer_record_id WHERE cd.customer_record_id IS NULL;"); $db->sql_query("DELETE cd FROM customer_dms cd LEFT OUTER JOIN customer c ON cd.customer_record_id = c.id WHERE c.id IS NULL;"); $db->sql_query("DELETE c FROM customer_contact cc LEFT OUTER JOIN customer c ON c.id = cc.customer_id WHERE c.id IS NULL;"); } function read_customer_total_number($start_date, $end_date, $company, $granted_company_ids, $db) { $company_filter = (intval($company) === 0) ? $granted_company_ids : $company; $date_filter = ($start_date !== '' && $end_date !== '') ? " AND ci.date_uploaded BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') " : ''; return $db->select("SELECT SUM((SELECT count(1) FROM (SELECT sc.dms_id FROM customer ci INNER JOIN customer_dms cd ON ci.id = cd.customer_record_id INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id WHERE ci.status = 1 AND scd.company_id IN (" . $company_filter . ") $date_filter GROUP BY ci.id ) as t2 WHERE t2.dms_id = source_dms.id ) ) as total FROM `source_dms` WHERE status = 1"); } function read_customer_counts($start_date, $end_date, $company, $granted_company_ids, $db) { $company_filter = (intval($company) === 0) ? " AND scd.company_id IN (" . $granted_company_ids . ") " : " AND scd.company_id IN (" . $company . ") "; $date_filter = ($start_date !== '' && $end_date !== '') ? " AND c.date_uploaded BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') " : ''; $result_arr["customer_data"] = array(); $corporation_count = 0; $corporation_percent = '0%'; $individual_count = 0; $individual_percent = '0%'; $male_count_query = 0; $male_percent = '0%'; $female_count_query = 0; $female_percent = '0%'; $age_count_query_below = 0; $below_percent = '0%'; $age_count_query_between = 0; $age_between_percent = '0%'; $age_count_query_above = 0; $above_percent = '0%'; $no_gender_count = 0; $query_overall = "SELECT SUM(t1.individual_count) as individual_count, SUM(t1.corporation_count) as corporation_count, SUM(t1.male_count) as male_count, SUM(t1.female_count) as female_count, SUM(t1.no_gender_count) as no_gender_count, SUM(t1.age_below) as age_below, SUM(t1.age_between) as age_between, SUM(t1.age_above) as age_above, SUM(t1.no_dob) as no_dob, SUM(t1.no_email) as no_email, SUM(t1.total) as total, SUM(t1.complete) as complete, SUM(t1.incomplete) as incomplete, SUM(t1.no_address) as no_address, SUM(t1.active) as active, (SUM(t1.total) - SUM(t1.active)) as inactive FROM (SELECT sc.dms_id, 1 as total, IF(c.type = 1, 1, 0) AS individual_count, IF(c.type = 2, 1, 0) AS corporation_count, IF(c.gender_id = 1, 1, 0) AS male_count, IF(c.gender_id = 2, 1, 0) AS female_count, IF(c.gender_id = 0 AND c.type = 1, 1, 0) AS no_gender_count, IF(cc.address_1 = '', 1, 0) AS no_address, IF(cc.email_1 = '', 1, 0) AS no_email, IF(IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') = '' AND c.type = 1, 1, 0) AS no_dob, IF(TIMESTAMPDIFF(YEAR,c.date_of_birth,CURDATE()) <= 30 , 1,0) AS age_below, IF(TIMESTAMPDIFF(YEAR,c.date_of_birth,CURDATE()) <= 50 AND TIMESTAMPDIFF(YEAR,c.date_of_birth,CURDATE()) >= 31 , 1,0) AS age_between, IF(TIMESTAMPDIFF(YEAR,c.date_of_birth,CURDATE()) >= 51 , 1,0) AS age_above, IF((((IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') <> '' and c.gender_id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '') AND c.type = 1) OR ((cc.address_1 <> '' and cc.email_1 <> '') AND c.type = 2)), 1, 0) AS complete, IF((((IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') = '' or c.gender_id = 0 or cc.address_1 = '' or cc.email_1 = '') AND c.type = 1) OR ((cc.address_1 = '' OR cc.email_1 = '') AND c.type = 2)) , 1, 0) AS incomplete, IF((SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) \|\| (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) ,1,0) as active /IF((SELECT DATEDIFF(NOW(),se.start_date_of_service) > 365) && (SELECT DATEDIFF(NOW(),s.activity_date) > 365) ,1,0) as inactive/ FROM `customer` c INNER JOIN customer_contact cc ON c.id = cc.customer_id INNER JOIN customer_dms cd ON c.id = cd.customer_record_id INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id LEFT OUTER JOIN vehicle v ON c.id = v.customer_record_id LEFT OUTER JOIN sales s ON v.id = s.vehicle_id LEFT OUTER JOIN service se ON v.id = se.vehicle_id WHERE 1 %s %s AND c.status = 1 GROUP BY c.id) as t1"; // ignore_user_abort(true); // $thread_id = $db->select('SELECT CONNECTION_ID();'); $query_overall_results = $db->sql_query(sprintf($query_overall, $company_filter, $date_filter)); // foreach($query_overall_results as $result){ while ($result = $query_overall_results->fetch_assoc()) { $corporation_count = $result['corporation_count'] == null ? 0 : $result['corporation_count']; $individual_count = $result['individual_count'] == null ? 0 : $result['individual_count']; $customer_total = (intval($individual_count) + intval($individual_count)); $corporation_percent = ($corporation_count > 0) ? number_format((($corporation_count * 100) / $customer_total), 2) . '%' : '0%'; $individual_percent = ($individual_count > 0) ? number_format((($individual_count * 100) / $customer_total), 2) . '%' : '0%'; $male_count_query = $result['male_count'] == null ? 0 : $result['male_count']; $male_percent = ($male_count_query > 0) ? number_format((($male_count_query * 100) / $individual_count), 2) . '%' : '0%'; $female_count_query = $result['female_count'] == null ? 0 : $result['female_count']; $female_percent = ($female_count_query > 0) ? number_format((($female_count_query * 100) / $individual_count), 2) . '%' : '0%'; $age_count_query_below = $result['age_below'] == null ? 0 : $result['age_below']; $below_percent = ($age_count_query_below > 0) ? number_format((($age_count_query_below * 100) / $individual_count), 2) . '%' : '0%'; $age_count_query_between = $result['age_between'] == null ? 0 : $result['age_between']; $age_between_percent = ($age_count_query_between > 0) ? number_format((($age_count_query_between * 100) / $individual_count), 2) . '%' : '0%'; $age_count_query_above = $result['age_above'] == null ? 0 : $result['age_above']; $above_percent = ($age_count_query_above > 0) ? number_format((($age_count_query_above * 100) / $individual_count), 2) . '%' : '0%'; $no_gender_count = $result['no_gender_count'] == null ? 0 : $result['no_gender_count']; $no_dob_count = $result['no_dob'] == null ? 0 : $result['no_dob']; $no_email_count = $result['no_email'] == null ? 0 : $result['no_email']; $total_count = $result['total'] == null ? 0 : $result['total']; $complete_count = $result['complete'] == null ? 0 : $result['complete']; $incomplete_count = $result['incomplete'] == null ? 0 : $result['incomplete']; $no_address_count = $result['no_address'] == null ? 0 : $result['no_address']; $active_count = $result['active'] == null ? 0 : $result['active']; $inactive_count = $result['inactive'] == null ? 0 : $result['inactive']; } $result_arr["customer_data"] = array_merge( $result_arr["customer_data"], array(array( 'total_count' => ($total_count), 'individual' => ($individual_count), 'individual_percent' => $individual_percent, 'corporation' => ($corporation_count), 'corporation_percent' => $corporation_percent, 'active' => ($active_count), 'inactive' => ($inactive_count), 'male' => ($male_count_query), 'male_percent' => $male_percent, 'female' => ($female_count_query), 'female_percent' => $female_percent, 'below' => ($age_count_query_below), 'below_percent' => $below_percent, 'between' => ($age_count_query_between), 'between_percent' => $age_between_percent, 'above' => ($age_count_query_above), 'above_percent' => $above_percent, 'complete_data' => ($complete_count), 'incomplete_data' => ($incomplete_count), 'incomplete_no_address' => ($no_address_count), 'incomplete_no_gender' => ($no_gender_count), 'incomplete_no_dob' => ($no_dob_count), 'incomplete_no_email' => ($no_email_count), )) ); echo json_encode(array($result_arr)); } function read_customer_summary_count($start_date, $end_date, $company, $granted_company_ids, $vehicle_total, $db) { $company_filter = (intval($company) === 0) ? $granted_company_ids : $company; $date_filter = ($start_date !== '' && $end_date !== '') ? " AND c.date_uploaded BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') " : ''; $company_filter_format = " AND sc.id IN (" . $company_filter . ") "; // $corporation_count = 0; // $corporation_percent = '0%'; // $individual_count = 0; // $individual_percent = '0%'; // $male_count_query = 0; // $male_percent = '0%'; // $female_count_query = 0; // $female_percent = '0%'; // $age_count_query_below =0; // $below_percent = '0%'; // $age_count_query_between =0; // $age_between_percent ='0%'; // $age_count_query_above = 0; // $above_percent = '0%'; // $query_overall = "SELECT SUM(t1.individual_count) as individual_count, // SUM(t1.corporation_count) as corporation_count, // SUM(t1.male_count) as male_count, // SUM(t1.female_count) as female_count, // SUM(t1.age_below) as age_below, // SUM(t1.age_between) as age_between, // SUM(t1.age_above) as age_above // FROM (SELECT // sc.dms_id, // IF(c.type = 1, 1, 0) AS individual_count, // IF(c.type = 2, 1, 0) AS corporation_count, // IF(c.gender_id = 1, 1, 0) AS male_count, // IF(c.gender_id = 2, 1, 0) AS female_count, // IF(TIMESTAMPDIFF(YEAR,c.date_of_birth,CURDATE()) <= 30 , 1,0) AS age_below, // IF(TIMESTAMPDIFF(YEAR,c.date_of_birth,CURDATE()) <= 50 AND TIMESTAMPDIFF(YEAR,c.date_of_birth,CURDATE()) >= 31 , 1,0) AS age_between, // IF(TIMESTAMPDIFF(YEAR,c.date_of_birth,CURDATE()) >= 51 , 1,0) AS age_above // FROM `customer` c // INNER JOIN customer_dms cd ON c.id = cd.customer_record_id // INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id // INNER JOIN source_company sc ON scd.company_id = sc.id // WHERE 1 // AND c.status = 1 // %s // %s // GROUP BY c.id) as t1"; // $query_overall_results = $db -> sql_query(sprintf($query_overall,$company_filter_format,$date_filter)); // foreach($query_overall_results as $result){ // $corporation_count = $result['corporation_count'] == null ? 0 : $result['corporation_count'] ; // $individual_count = $result['individual_count'] == null ? 0 : $result['individual_count'] ; // $customer_total = (intval($individual_count) + intval($individual_count)); // $corporation_percent = ($corporation_count > 0) ? number_format((($corporation_count100) / $customer_total),2).'%' : '0%'; // $individual_percent = ($individual_count > 0) ? number_format((($individual_count100) / $customer_total),2).'%' : '0%'; // $male_count_query = $result['male_count'] == null ? 0 : $result['male_count'] ; // $male_percent = ($male_count_query > 0) ? number_format((($male_count_query100) / $individual_count),2).'%' : '0%'; // $female_count_query = $result['female_count'] == null ? 0 : $result['female_count'] ; // $female_percent = ($female_count_query > 0) ? number_format((($female_count_query100) / $individual_count),2).'%' : '0%'; // $age_count_query_below = $result['age_below'] == null ? 0 : $result['age_below'] ; // $below_percent = ($age_count_query_below > 0) ? number_format((($age_count_query_below100) / $individual_count),2).'%' : '0%'; // $age_count_query_between = $result['age_between'] == null ? 0 : $result['age_between'] ; // $age_between_percent = ($age_count_query_between > 0) ? number_format((($age_count_query_between100) / $individual_count),2).'%' : '0%'; // $age_count_query_above = $result['age_above'] == null ? 0 : $result['age_above'] ; // $above_percent = ($age_count_query_above > 0) ? number_format((($age_count_query_above100) / $individual_count),2).'%' : '0%'; // } $date_filter = ($start_date !== '' && $end_date !== '') ? " AND v.date_uploaded BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') " : ''; $vehicle_image = $db->select("SELECT SUM((SELECT count(1) FROM (SELECT sc.dms_id FROM vehicle v INNER JOIN source_company_dealer scd ON v.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id INNER JOIN vehicle_images vi ON v.id = vi.vehicle_id WHERE v.status = 1 AND vi.status = 1 AND scd.company_id IN (" . $company_filter . ") $date_filter GROUP BY v.id ) as t2 WHERE t2.dms_id = source_dms.id ) ) as total_count FROM `source_dms` WHERE status = 1"); if ($vehicle_image > 0) { $vehicle_image_percent = number_format((($vehicle_image 100) / $vehicle_total), 2) . '%'; } else { $vehicle_image_percent = '0%'; } $json_arr = array(); // $json_arr['corporation'] = $corporation_count; // $json_arr['corporation_percent'] = $corporation_percent; // $json_arr['individual'] = $individual_count; // $json_arr['individual_percent'] = $individual_percent; // $json_arr['male'] = $male_count_query; // $json_arr['male_percent'] = $male_percent; // $json_arr['female'] = $female_count_query; // $json_arr['female_percent'] = $female_percent; // $json_arr['below'] = $age_count_query_below; // $json_arr['below_percent'] = $below_percent; // $json_arr['between'] = $age_count_query_between; // $json_arr['between_percent'] = $age_between_percent; // $json_arr['above'] = $age_count_query_above; // $json_arr['above_percent'] = $above_percent; $json_arr['vehicle_image'] = $vehicle_image; $json_arr['vehicle_image_percent'] = $vehicle_image_percent; echo json_encode($json_arr); exit; $customer_count_query = "SELECT SUM( (SELECT count(1) FROM ( SELECT sc.dms_id FROM customer ci INNER JOIN customer_dms cd ON ci.id = cd.customer_record_id INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id WHERE ci.type = %d AND ci.status = 1 AND scd.company_id IN (" . $company_filter . ") %s GROUP BY ci.id ) as t2 WHERE t2.dms_id = source_dms.id) ) as total FROM `source_dms` WHERE status = 1"; // echo (sprintf($customer_count_query,1,$date_filter));exit; $individual_count = $db->select(sprintf($customer_count_query, 1, $date_filter)); $corporation_count = $db->select(sprintf($customer_count_query, 2, $date_filter)); if ($customer_total > 0) { $individual_percent = number_format((($individual_count * 100) / $customer_total), 2) . '%'; $corporation_percent = number_format((($corporation_count * 100) / $customer_total), 2) . '%'; } else { $corporation_percent = '0%'; $individual_percent = '0%'; } $gender_query = "SELECT SUM( (SELECT count(1) FROM ( SELECT sc.dms_id FROM customer ci INNER JOIN customer_dms cd ON ci.id = cd.customer_record_id INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id WHERE ci.gender_id = %d AND ci.status = 1 AND scd.company_id IN (" . $company_filter . ") %s GROUP BY ci.id ) as t2 WHERE t2.dms_id = source_dms.id ) ) as total FROM `source_dms` WHERE status = 1"; $customer_with_gender_query_total = $db->select("SELECT SUM( (SELECT count(1) FROM ( SELECT cd.dms_id FROM customer ci INNER JOIN customer_dms cd ON ci.id = cd.customer_record_id INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id WHERE ci.gender_id <> 0 AND ci.status = 1 AND scd.company_id IN (" . $company_filter . ") $date_filter GROUP BY ci.id ) as t2 WHERE t2.dms_id = source_dms.id ) ) as total FROM `source_dms` WHERE status = 1"); $male_percent = "0%"; $male_count_query = $db->select(sprintf($gender_query, 1, $date_filter)); if ($male_count_query > 0) { $male_percent = number_format((($male_count_query * 100) / $customer_with_gender_query_total), 2) . '%'; } else { $male_percent = '0%'; } $female_percent = "0%"; $female_count_query = $db->select(sprintf($gender_query, 2, $date_filter)); if ($female_count_query > 0) { $female_percent = number_format((($female_count_query * 100) / $customer_with_gender_query_total), 2) . '%'; } else { $female_percent = '0%'; } $where_age = " TIMESTAMPDIFF(YEAR, date_of_birth , CURDATE() ) "; $age_count_query = "SELECT SUM( ( SELECT count(1) FROM ( SELECT sc.dms_id FROM customer ci INNER JOIN customer_dms cd ON ci.id = cd.customer_record_id INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id WHERE $where_age %s AND ci.status = 1 AND scd.company_id IN (" . $company_filter . ") %s GROUP BY ci.id ) as t2 WHERE t2.dms_id = source_dms.id ) ) as total FROM `source_dms` WHERE status = 1"; $age_count_query_total = $db->select("SELECT SUM( ( SELECT count(1) FROM ( SELECT sc.dms_id FROM customer ci INNER JOIN customer_dms cd ON ci.id = cd.customer_record_id INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id WHERE ci.date_of_birth <> '' AND ci.status = 1 AND scd.company_id IN (" . $company_filter . ") $date_filter GROUP BY ci.id ) as t2 WHERE t2.dms_id = source_dms.id ) ) as total FROM `source_dms` WHERE status = 1"); // echo sprintf($age_count_query," <= 30 ",$date_filter);exit; $age_count_query_below = $db->select(sprintf($age_count_query, " <= 30 ", $date_filter)); if ($age_count_query_below > 0) { $below_percent = number_format((($age_count_query_below * 100) / $age_count_query_total), 2) . '%'; } else { $below_percent = '0%'; } $age_count_query_between = $db->select(sprintf($age_count_query, " <= 50 and $where_age >= 31 ", $date_filter)); if ($age_count_query_between > 0) { $age_between_percent = number_format((($age_count_query_between * 100) / $age_count_query_total), 2) . '%'; } else { $age_between_percent = '0%'; } $age_count_query_above = $db->select(sprintf($age_count_query, " >= 51 ", $date_filter)); if ($age_count_query_above > 0) { $above_percent = number_format((($age_count_query_above * 100) / $age_count_query_total), 2) . '%'; } else { $above_percent = '0%'; } $date_filter = ($start_date !== '' && $end_date !== '') ? " AND v.date_uploaded BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') " : ''; $vehicle_image = $db->select("SELECT SUM((SELECT count(1) FROM (SELECT sc.dms_id FROM vehicle v INNER JOIN source_company_dealer scd ON v.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id INNER JOIN vehicle_images vi ON v.id = vi.vehicle_id WHERE v.status = 1 AND vi.status = 1 AND scd.company_id IN (" . $company_filter . ") $date_filter GROUP BY v.id ) as t2 WHERE t2.dms_id = source_dms.id ) ) as total_count FROM `source_dms` WHERE status = 1"); if ($vehicle_image > 0) { $vehicle_image_percent = number_format((($vehicle_image * 100) / $vehicle_total), 2) . '%'; } else { $vehicle_image_percent = '0%'; } $json_arr = array(); $json_arr['corporation'] = $corporation_count; $json_arr['corporation_percent'] = $corporation_percent; $json_arr['individual'] = $individual_count; $json_arr['individual_percent'] = $individual_percent; $json_arr['male'] = $male_count_query; $json_arr['male_percent'] = $male_percent; $json_arr['female'] = $female_count_query; $json_arr['female_percent'] = $female_percent; $json_arr['below'] = $age_count_query_below; $json_arr['below_percent'] = $below_percent; $json_arr['between'] = $age_count_query_between; $json_arr['between_percent'] = $age_between_percent; $json_arr['above'] = $age_count_query_above; $json_arr['above_percent'] = $above_percent; $json_arr['vehicle_image'] = $vehicle_image; $json_arr['vehicle_image_percent'] = $vehicle_image_percent; echo json_encode($json_arr); } function insert_duplicate_reason($customer_id, $email, $mobile, $force_update_reason, $user_id, $utility, $db) { return $db->sql_query_num_inserted("INSERT INTO `allowed_duplicates`(`customer_id`, `mobile`, `email`, `description`,`owner_id`) VALUES ($customer_id,'$mobile','$email','$force_update_reason','$user_id')"); } }

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