File manager

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

Back
<?php include_once("../../cfg/db.php"); if ($_SERVER["REQUEST_METHOD"] != "POST") { $return_arr["message"]=" You are not allowed to do this action."; echo $return_arr["message"]; return; } if(isset($_REQUEST['apiKey']) ){ $apiKey = $_REQUEST['apiKey']; if($apiKey === 'DI_NALILIGO_SI_JEAN'){ if(isset($_REQUEST['search']) && isset($_REQUEST['offset']) && isset($_REQUEST['limit']) ){ $search = $db->escape($_REQUEST['search']); $limit = $db->escape($_REQUEST['limit']); $offset = $db->escape($_REQUEST['offset']); $offset_limit = " LIMIT ". $offset.",".$limit; echo json_encode(getFilteredCustomerData($search,$offset_limit,'ASC','2',$db)); }else{ $return_arr["message"]=" You are not allowed to do this action."; echo $return_arr["message"]; return; } }else if($apiKey === '783485638454368'){ $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.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(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 AND c.status = 1 GROUP BY c.id) as t1"; // echo $query_overall;exit; $query_overall_results = $db -> sql_query($query_overall); 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%'; $no_gender_count = $result['no_gender_count'] == null ? 0 : $result['no_gender_count'] ; $no_dob_count = $result['no_dob'] == null ? 0 : $result['no_dob'] ; $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'=>number_format($total_count), 'individual_customer'=>number_format($individual_count), 'corporation_customer'=>number_format($corporation_count), 'active'=>number_format($active_count), 'inactive'=>number_format($inactive_count), 'male'=>number_format($male_count_query), 'female'=>number_format($female_count_query), 'age_below_30'=>number_format($age_count_query_below), 'age_between_30_and_50'=>number_format($age_count_query_between), 'age_51_and_above'=>number_format($age_count_query_above), 'complete_data'=>number_format($complete_count), 'incomplete_data'=>number_format($incomplete_count), 'incomplete_no_address'=>number_format($no_address_count), 'incomplete_no_gender'=>number_format($no_gender_count), 'incomplete_no_dob'=>number_format($no_dob_count), ))); echo json_encode(array($result_arr)); }else if($apiKey === '6287648234678'){ $dms_count_query = "SELECT code, ( (SELECT count(1) FROM (SELECT sc.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 GROUP BY ci.id,sc.id ) as t2 WHERE t2.id = source_company.id ) ) as total FROM `source_company` WHERE status = 1"; $dms_count = $db -> return_result($dms_count_query); $result_arr["company_count"] = array(); foreach($dms_count as $data){ $result_arr["company_count"] = array_merge($result_arr["company_count"], array(array( 'company_code' => $data['code'], 'count' => number_format($data['total']), ))); } echo json_encode($result_arr); return; }else if($apiKey === '46372832367856345'){ $result_arr["customer_dms_count"] = array(); $dms_count_query = "SELECT name, ( (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 GROUP BY ci.id,sc.id ) as t2 WHERE t2.dms_id = source_dms.id ) ) as total FROM `source_dms` WHERE status = 1"; $dms_count = $db -> return_result($dms_count_query); foreach($dms_count as $row){ $result_arr["customer_dms_count"] = array_merge($result_arr["customer_dms_count"], array(array( $row['name']=>number_format($row['total']), ))); } echo json_encode($result_arr); return; }else if($apiKey === '658342658734685'){ $result_arr["records_count"] = array(); $customer_individidual_count = $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 GROUP BY ci.id ) as t2 WHERE t2.dms_id = source_dms.id ) ) as total FROM `source_dms` WHERE status = 1"); // $customer_individidual_count = $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 WHERE ci.status = 1 GROUP BY ci.id ) as t2 WHERE t2.dms_id = source_dms.id ) ) ) as total FROM `source_dms` WHERE status = 1"); $customer_total = $customer_individidual_count; $vehicle_count = $db -> select("SELECT count(1) FROM `vehicle` WHERE status = 1 "); $sale_count = $db -> select("SELECT count(1) FROM sales s INNER JOIN vehicle v ON s.vehicle_id = v.id WHERE v.status = 1 AND s.status = 1"); $service_count = $db -> select("SELECT count(1) FROM service se INNER JOIN vehicle v ON se.vehicle_id = v.id WHERE v.status = 1 AND se.status = 1"); $result_arr["records_count"] = array_merge($result_arr["records_count"], array(array( 'customer_total'=>number_format($customer_total), 'vehicle_total'=>number_format($vehicle_count), 'service_total'=>number_format($service_count), 'sales_total'=>number_format($sale_count), ))); echo json_encode($result_arr); return; }else{ $return_arr["message"]=" You are not allowed to do this action."; echo $return_arr["message"]; return; } }else{ $return_arr["message"]=" You are not allowed to do this action."; echo $return_arr["message"]; return; } function getFilteredCustomerData($search,$offset_limit,$order_by,$sort_by,$db){ switch(intval($sort_by)){ case 1: //lastname $filter_sort_by = " c.last_name "; break; case 2: //firstname $filter_sort_by = " c.first_name "; break; case 3: //corporation name $filter_sort_by = " c.corporation_name "; break; case 4: //date created $filter_sort_by = " c.date_created "; break; case 5: //date uploaded $filter_sort_by = " c.date_uploaded "; break; } if(isNotEmpty($search)){ $filter_email = " OR cc.email_1 = '".$search."' "; }else{ $filter_email = ""; } $return_data['customer'] = array(); $query = " SELECT %s FROM customer c INNER JOIN customer_category ccat ON c.category_id = ccat.id INNER JOIN customer_contact cc ON c.id = cc.customer_id INNER JOIN employee e ON c.upload_by = e.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 all_gender ag ON c.gender_id = ag.id INNER JOIN all_marital_status am ON c.marital_status_id = am.id INNER JOIN all_countries ac ON c.nationality_id = ac.num_code INNER JOIN all_suffix asx ON c.suffix_id = asx.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 concat(c.id,' ', concat(c.corporation_name,' ',c.first_name,' ',c.last_name),' ', concat(c.first_name,' ',c.middle_name,' ',c.last_name),' ', concat('0',SUBSTRING(cc.mobile_phone_1, 4)), ' ',cc.mobile_phone_1,' ',cd.customer_dms_id) LIKE '%%%s%%' /1 search/ AND c.status = 1 GROUP BY c.id ORDER BY $filter_sort_by $order_by ".$offset_limit; $fields = " c.id, c.photo, IF(c.type = 1, concat(c.first_name,' ',c.middle_name,' ',c.last_name, ' ', IF(c.suffix_id < 1, '',asx.suffix_name )) ,c.corporation_name ) as name, am.name as marital_status, ag.gender_name, c.date_of_birth, 'Individual' as type, c.date_uploaded, TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) as age_number, c.date_created, cc.mobile_phone_1, cc.mobile_phone_2, cc.email_1, cc.email_2, cc.business_phone, cc.fax_phone, cc.residential_phone, cc.address_1, cc.viber, cc.facebook, cc.instagram, cc.other_social, c.contact_person, ac.nationality, c.occupation, c.no_of_children, concat(e.first_name,' ',e.last_name) as upload_by, cd.customer_dms_id as customer_number, c.type, ( IF( c.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 "; $count = " 1 "; /* * @Clarence Andaya doon sa customer Database natin mag add k ng field for Customer Catefgory ang possible Value nya as per WQT ay ang mga sumusunod SVO = Service Only bought outside Autohub done SAO = Sales Only Serviced by other non-autohub dealership LYL = Loyal Customer (with Sales and Service transaction) SLY = bought 2-3 cars from any Autohub Brands VIP = bought 4 cars and up from any AUtohub brands VVIP = Asshole customer Service ONLY wlang entry sa sales transaction ibig sabihin hindi sa atin nabili ang unit SAO nabili sa atin di nman nagpapa service Parse Inactive Active customer pag walng service at sales trnsaction for 1 year - mark as inactive if no date */ // echo (sprintf($query,$fields,$search));exit; $data_list = $db -> sql_query(sprintf($query,$fields,$search)); $query_count = $db -> select("SELECT COUNT(1) FROM ( ".sprintf($query,$count,$search)." ) as total_count"); if( $query_count > 0){ $record_counter = 1; foreach ($data_list as $data){ $data = array_map("replaceNull", $data); $involvements = "SELECT sd.name AS dms, sc.code AS company, sdr.code AS dealer FROM `customer_dms` cd INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON sc.id = scd.company_id INNER JOIN source_dms sd ON sc.`dms_id` = sd.id INNER JOIN source_dealer sdr ON sdr.id = scd.dealer_id WHERE `customer_record_id` IN ('".$data['id']."')"; $in = $db->return_result($involvements); $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); // $return_data['customer'] = array_merge($return_data['customer'],array(array('No.'=>$record_counter++,'Name'=>$data['name'],'Age'=>$data['age_number'],'Gender'=>$data['gender_name'],'Email'=>$data['email_1'],'Mobile'=>$data['mobile_phone_1'],'Type'=>(intval($data['type']) === 1) ? 'Individual' : 'Corporation' ))); if(intval($data['type']) === 1){ $return_data['customer'] = array_merge($return_data['customer'], array(array( // 'No.'=>$record_counter++, 'customer_id'=>$data['id'], 'customer_no'=>$data['customer_number'], 'name'=>$data['name'], 'type'=>(intval($data['type']) === 1) ? 'Individual' : 'Corporation', 'gender'=>$data['gender_name'], 'dob'=>$data['date_of_birth'], 'age'=>$data['age_number'], 'status'=>$data['marital_status'], 'email'=>$data['email_1'], 'email_2'=>$data['email_2'], 'viber'=>$data['viber'], 'facebook'=>$data['facebook'], 'instagram'=>$data['instagram'], 'other_social'=>$data['other_social'], 'mobile'=>$data['mobile_phone_1'], 'mobile_2'=>$data['mobile_phone_2'], 'business_phone'=>$data['business_phone'], 'fax'=>$data['fax_phone'], 'home_phone'=>$data['residential_phone'], 'contact_person'=>$data['contact_person'], 'nationality'=>$data['nationality'], 'occupation'=>$data['occupation'], 'no_of_children'=>$data['no_of_children'], 'dms'=>$list_dms, 'company'=>$list_company, 'dealer'=>$list_dealer, 'date'=>$data['date_created'], 'upload_date'=>$data['date_uploaded'], 'upload_by'=>$data['upload_by'], 'customer_category'=>$data['customer_category'], 'is_active'=>(intval($data['isActive']) == 1 ? 'Active' : 'Inactive') , ))); }else{ $return_data['customer'] = array_merge($return_data['customer'], array(array( // 'No.'=>$record_counter++, 'customer_id'=>$data['id'], 'customer_no'=>$data['customer_number'], 'name'=>$data['name'], 'type'=>(intval($data['type']) === 1) ? 'Individual' : 'Corporation', 'gender'=>'', 'dob'=>$data['date_of_birth'], 'age'=>$data['age_number'], 'status'=>'', 'email'=>$data['email_1'], 'email_2'=>$data['email_2'], 'viber'=>$data['viber'], 'facebook'=>$data['facebook'], 'instagram'=>$data['instagram'], 'other_social'=>$data['other_social'], 'mobile'=>$data['mobile_phone_1'], 'mobile_2'=>$data['mobile_phone_2'], 'business_phone'=>$data['business_phone'], 'fax'=>$data['fax_phone'], 'home_phone'=>$data['residential_phone'], 'contact_person'=>$data['contact_person'], 'nationality'=>'', 'occupation'=>'', 'no_of_children'=>'', 'dms'=>$list_dms, 'company'=>$list_company, 'dealer'=>$list_dealer, 'date'=>$data['date_created'], 'upload_date'=>$data['date_uploaded'], 'upload_by'=>$data['upload_by'], 'customer_category'=>$data['customer_category'], 'is_active'=>(intval($data['isActive']) == 1 ? 'Active' : 'Inactive') , ))); } } $return_data['found'] =$query_count." Record".((intval($query_count) < 2) ? '' : 's' )." Found"; }else{ // $return_data['customer'] = array_merge($return_data['customer'],array(array('Name'=>'','Age'=>'','Gender'=>'','Email'=>'','Mobile'=>'','Type'=>''))); $return_data['found'] = "No Record Found"; } $return_data['total'] = $query_count; return array($return_data); } function isNotEmpty($data){ return preg_match('/\S/', $data); } function replaceNull($s){ return ($s <> null ) ? $s : '' ; } ?>

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