File manager

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

Back
<?php class Compliance { function read_sales_by_asa_id($sales_id, $brand_id, $complete_type, $type, $commission_status, $company, $start_date, $end_date, $asa_id, $sc_id, $search, $offset, $limit, $required_fields, $days, $secure, $utility, $db) { $asa_id_record_id = $asa_id; $json_arr['rows'] = array(); $company_filter = ($company == '') ? '' : " AND scd.company_id IN (" . $company . ") "; $sc_id = ($sc_id == '') ? '' : " AND s.sales_person = '$sc_id' "; $sales_record = ($sales_id == '') ? '' : " AND s.id = '$sales_id' "; $brand_filter = ($brand_id == '') ? '' : " AND sb.id = '$brand_id' "; if ($start_date <> '' && $end_date <> '' && $start_date <> '1970-01-01') { $date_filter = " AND DATE_FORMAT(s.activity_date,'%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') "; } else { $date_filter = ""; } if ($search != '') { $search_alpha_numeric_only = $utility->alphaNumericOnly($search); $filter_has_sc = ""; $asa_id = ''; $search = " AND (CONCAT( IF(c.id IS NOT NULL, 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 CONCAT( IF( v.conduction_sticker IS NOT NULL, v.conduction_sticker, '' ),'', IF( v.plate_number IS NOT NULL, v.plate_number, '' ),'', IF( s.order_number IS NOT NULL, s.order_number, '' )) LIKE '%$search_alpha_numeric_only%' OR s.id= '$search' OR v.id= '$search' OR cc.email_1 = '" . $search . "') "; } else { $filter_has_sc = " AND (sc.id IS NOT NULL AND sc.id <> 0) "; $asa_id = ($asa_id == '') ? '' : " AND s.asa_sales_person = '$asa_id' "; } $offset_limit = ($utility->isNotEmpty($offset) && $utility->isNotEmpty($limit)) ? " LIMIT $offset,$limit " : ''; $incomplete_where = " AND (" . $required_fields->getIncompleteWhere() . ") "; $complete_where = " AND (" . $required_fields->getCompleteWhere() . ") "; if ($utility->isNotEmpty($complete_type)) { if ($complete_type == '0') { if ($type == '') { $complete_type = $incomplete_where; $type = ''; } else { $complete_type = ''; $type = $required_fields->whereByType($type); } } else if ($complete_type == '1') { if ($type == '') { $complete_type = $complete_where; $type = ''; } else { $complete_type = ''; $type = $required_fields->whereByTypeComplete($type); } } else { if ($type == '') { $complete_type = ''; $type = ''; } else { $complete_type = ''; $type = $required_fields->whereByTypeComplete($type); } } } else { $complete_type = ''; $type = ''; } if ($utility->isNotEmpty($commission_status)) { switch ($commission_status) { case "0": $commission_status = " AND ((t1.complete = 0 AND (t1.m_commission_status_id = 0 \|\| t1.m_commission_status_id = 3)) OR (t1.complete = 1 AND t1.m_commission_status_id = 3)) "; break; case "1": $commission_status = " AND ((t1.complete = 1 AND t1.m_commission_status_id = 0) OR (t1.complete = 1 AND t1.m_commission_status_id = 1) OR (t1.complete = 0 AND t1.m_commission_status_id = 1))"; break; case "2": $commission_status = " AND (t1.m_commission_status_id = 2)"; break; } } else { $commission_status = ""; } if ($utility->isNotEmpty($days)) { preg_match_all('!\d+\-\d+!', $days, $day_matches); if (count($day_matches[0]) > 0) { $days_pieces = explode("-", $day_matches[0][0]); $days_filter = " AND DATEDIFF(NOW(), s.activity_date ) BETWEEN " . $days_pieces[0] . " AND " . $days_pieces[1] . ""; } else { $filteredNumbers = array_filter(preg_split("/\D+/", $days)); $firstOccurence = reset($filteredNumbers); if ($utility->isNotEmpty($firstOccurence)) { $days_filter = " AND DATEDIFF(NOW(), s.activity_date ) = $firstOccurence"; } else { $days_filter = ""; } } } else { $days_filter = ""; } $field = " t1.asa_sales_person as asa_sc_id, t1.m_asa_group_manager_id as asa_sm_id, t1.customer_id, t1.sales_id, t1.m_vehicle_id AS vehicle_id, t1.m_customer_name AS customer_name, t1.m_corporation_name AS corporation_name, t1.m_first_name AS first_name, t1.m_middle_name AS middle_name, t1.m_last_name AS last_name, t1.m_customer_type AS customer_type, t1.has_ltms_records as ltms, t1.m_customer_affiliation AS customer_affiliation, t1.m_gender as gender, t1.m_date_of_birth as date_of_birth, t1.m_address_1 AS address_1, t1.m_email_1 AS email_1, t1.m_mobile_phone_1 AS mobile_phone_1, t1.m_facebook AS facebook, t1.m_customer_company AS customer_company, t1.m_position AS position, t1.m_nature_of_business AS nature_of_business, t1.m_profession as profession, t1.m_conduction_sticker as conduction_sticker, t1.m_plate_number as plate_number, t1.m_brand_name as brand_name, t1.m_model_name as model_name, t1.m_model_variant as model_variant, t1.m_model_year as model_year, t1.m_vehicle_color as vehicle_color, t1.m_dms as dms, t1.m_company as company, t1.m_dealer as dealer, t1.m_end_user as end_user, t1.m_release_date as release_date, DATEDIFF(NOW(), t1.m_release_date) as release_date_count, t1.m_mode_of_sale as mode_of_sale, t1.m_type_of_payment as type_of_payment, t1.m_insurance as insurance, t1.m_source_of_sale as source_of_sale, t1.m_brand_offered as brand_offered, t1.m_sales_consultant as sales_consultant, t1.m_sales_manager as sales_manager, IF(t1.m_customer_type = 1,'Individual',IF(t1.m_customer_type = 2,'Corporation','')) as customer_type_name, /t1.m_customer_type_name as customer_type_name,/ t1.m_vehicle_image as vehicle_image, t1.m_customer_category as customer_category, t1.m_is_active as is_active, IF(t1.complete = 1,'COM','INC') as status, IF(t1.complete = 1 AND t1.m_complete_date = '',DATE_FORMAT(t1.m_release_date,'%Y-%m-%d'),DATE_FORMAT(t1.m_complete_date,'%Y-%m-%d')) as complete_date, t1.m_incomplete_date as incomplete_date, /t1.m_commission_status as commission_status, t1.m_commission_status_id as commission_status_id,/ IF((t1.complete = 0 AND (t1.m_commission_status_id = 0 \|\| t1.m_commission_status_id = 3)) OR (t1.complete = 1 AND t1.m_commission_status_id = 3) OR (t1.has_ltms_records = 0),'HOLD', IF((t1.complete = 1 AND t1.m_commission_status_id = 0) OR (t1.complete = 1 AND t1.m_commission_status_id = 1) OR (t1.complete = 0 AND t1.m_commission_status_id = 1) OR (t1.has_ltms_records = 1),'FOR RELEASE', 'RELEASED' )) as commission_status, IF((t1.complete = 0 AND (t1.m_commission_status_id = 0 \|\| t1.m_commission_status_id = 3)) OR (t1.complete = 1 AND t1.m_commission_status_id = 3) OR (t1.has_ltms_records =0),'0', IF((t1.complete = 1 AND t1.m_commission_status_id = 0) OR (t1.complete = 1 AND t1.m_commission_status_id = 1) OR (t1.complete = 0 AND t1.m_commission_status_id = 1) AND t1.has_ltms_records=1,'1', '2' )) as commission_status_id, DATE_FORMAT(t1.m_commission_hold_date,'%M %d, %Y - %h:%m%p') as hold_commission_date, t1.m_commission_hold_by as hold_commission_by, DATE_FORMAT(t1.m_commission_release_date,'%M %d, %Y - %h:%m%p') as released_commission_date, t1.m_commission_release_by as released_commission_by, DATE_FORMAT(t1.m_commission_for_release_date,'%M %d, %Y - %h:%m%p') as for_release_commission_date, t1.m_commission_for_release_by as for_release_commission_by, t1.m_commission_accounting_status as commission_accounting_status, t1.m_commission_accounting_status_id as commission_accounting_status_id "; $query = "SELECT %s FROM (" . $required_fields->getAllInOneComplianceQuery() . " /AND s.asa_sales_person <> 0 / %s /* search / %s / complete type / %s / specific complete incomplete type eg facebook / %s /company/ %s / date / %s / asa id / %s /sc id/ %s / sales _id/ %s / brand _id/ %s / days filter/ AND v.status = 1 AND s.status = 1 AND c.id IS NOT NULL AND c.id <> 0 AND c.status = 1 GROUP BY s.id ) as t1 INNER JOIN sales_consultant sc ON t1.sales_person = sc.id WHERE 1 %s %s / com status / %s /limit/"; $magic_query = sprintf($query, $field, $search, $complete_type, $type, $company_filter, $date_filter, $asa_id, $sc_id, $sales_record, $brand_filter, $days_filter, $filter_has_sc, $commission_status, $offset_limit); // echo $magic_query;exit; $list = $db->sql_query($magic_query); $list_count = $db->sql_num_rows(sprintf($query, ' 1 ', $search, $complete_type, $type, $company_filter, $date_filter, $asa_id, $sc_id, $sales_record, $brand_filter, $days_filter, $filter_has_sc, $commission_status, '')); foreach ($list as $row) { //replace null to empty value $row = array_map(function ($v) { return (is_null($v)) ? "" : $v; //replace blank the null values }, $row); //check if complete or not $status = true; if (intval($asa_id_record_id) !== 0) { $access = intval($asa_id_record_id) == intval($row['asa_sc_id']); } else { $access = false; } $date1 = strtotime($row['release_date'] . " 00:00:00"); $date2 = strtotime($row['complete_date'] . " 00:00:00"); // echo $row['complete_date'];exit; // $d1 = new DateTime($row['release_date']); // $d2 = new DateTime($row['complete_date']); // Formulate the Difference between two dates $diff = abs($date2 - $date1); // To get the year divide the resultant date into // total seconds in a year (365606024) $years = floor($diff / (365 * 60 * 60 * 24)); // To get the month, subtract it with years and // divide the resultant date into // total seconds in a month (30606024) $months = floor(($diff - $years 365 * 60 * 60 * 24) / (30 * 60 * 60 * 24)); // To get the day, subtract it with years and // months and divide the resultant date into // total seconds in a days (606024) $days = floor(($diff - $years * 365 * 60 * 60 * 24 - $months * 30 * 60 * 60 * 24) / (60 * 60 * 24)); $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'asa_sc_id' => $row['asa_sc_id'], 'asa_sm_id' => $row['asa_sm_id'], 'customer_id' => $row['customer_id'], 'sales_id' => $row['sales_id'], 'vehicle_id' => $row['vehicle_id'], 'customer_name' => $row['customer_name'], 'corporation_name' => $row['corporation_name'], 'first_name' => $row['first_name'], 'middle_name' => $row['middle_name'], 'last_name' => $row['last_name'], 'customer_affiliation' => $row['customer_affiliation'], 'release_date' => $row['release_date'], 'gender' => $row['gender'], 'date_of_birth' => $row['date_of_birth'], 'address_1' => !$utility->isNotEmpty($row['address_1']) ?: ($secure) ? $utility->mask($row['address_1'], 3, 0) : $row['address_1'], 'email_1' => ($secure) ? $utility->mask_email($row['email_1']) : $row['email_1'], 'mobile_phone_1' => !$utility->isNotEmpty($row['mobile_phone_1']) ?: ($secure) ? $utility->mask($row['mobile_phone_1'], 4, 0) : $row['mobile_phone_1'], 'facebook' => !$utility->isNotEmpty($row['facebook']) ?: ($secure) ? $utility->mask($row['facebook'], 4, 0) : $row['facebook'], 'customer_company' => $row['customer_company'], 'position' => $row['position'], 'nature_of_business' => $row['nature_of_business'], 'profession' => $row['profession'], 'conduction_sticker' => $row['conduction_sticker'], 'plate_number' => $row['plate_number'], 'brand_name' => $row['brand_name'], 'model_name' => $row['model_name'], 'vehicle' => $row['brand_name'] . ($utility->isNotEmpty($row['model_name']) ? ' - ' . $row['model_name'] : ''), 'model_variant' => $row['model_variant'], 'model_year' => $row['model_year'], 'vehicle_color' => $row['vehicle_color'], 'dms' => $row['dms'], 'company' => $row['company'], 'dealer' => $row['dealer'], 'company_dealer' => $row['company'] . ' - ' . $row['dealer'], 'end_user' => $row['end_user'], 'release_date' => $row['release_date'], 'release_date_count' => $row['release_date_count'], // release 'st_percent' => ($row['status'] == 'COM' ? $this->get_st_percentage($days) : $this->get_st_percentage($row['release_date_count'])), 'sc_percent' => ($row['status'] == 'COM' ? $this->get_sc_percentage($days) : '0%'), // // debug // 'st_percent'=>($row['status'] == 'COM' ? $days. ' - '. $this->get_st_percentage($days) : $row['release_date_count'].' - ' .$this->get_st_percentage($row['release_date_count']) ), // 'sc_percent'=>$days. ' - '.($row['status'] == 'COM' ? $this->get_sc_percentage($days) : '0%'), 'mode_of_sale' => $row['mode_of_sale'], 'type_of_payment' => $row['type_of_payment'], 'insurance' => $row['insurance'], 'source_of_sale' => $row['source_of_sale'], 'brand_offered' => $row['brand_offered'], 'sales_consultant' => $row['sales_consultant'], 'sales_manager' => $row['sales_manager'], 'status' => $row['status'], 'commission' => $row['commission_status'], 'commission_id' => $row['commission_status_id'], 'commission_accounting_status' => $row['commission_accounting_status'], 'commission_accounting_status_id' => $row['commission_accounting_status_id'], 'commission_id_released' => $row['commission_status_id'], 'hold_commission_date' => $row['hold_commission_date'], 'hold_commission_by' => $row['hold_commission_by'], 'released_commission_date' => $row['released_commission_date'], 'released_commission_by' => $row['released_commission_by'], 'for_release_commission_date' => $row['for_release_commission_date'], 'for_release_commission_by' => $row['for_release_commission_by'], 'access' => ($access) ? true : false, 'customer_type' => $row['customer_type'], 'customer_type_name' => $row['customer_type_name'], 'customer_category' => $row['customer_category'], 'vehicle_image' => $row['vehicle_image'], 'complete_date' => $row['complete_date'], 'is_active' => (intval($row['is_active']) == 1 ? 'Active' : 'Inactive'), 'has_ltms' => $row['ltms'], ))); } $json_arr['total'] = intval($list_count); $json_arr['result'] = intval($list_count) > 0 ? true : false; return ($json_arr); // exit; // $incomplete_where = $required_fields -> getIncompleteWhere(); // $complete_where = $required_fields -> getCompleteWhere(); // if($complete_type == '0' \|\| $complete_type == '1'){ // if($complete_type == '0'){ // if($type == ''){ // $complete_type = $incomplete_where; // $type = ''; // }else{ // $complete_type = ''; // $type = $required_fields->whereByType($type); // } // }else if($complete_type == '1'){ // if($type == ''){ // $complete_type = $complete_where; // $type = ''; // }else{ // $complete_type = ''; // $type = $required_fields->whereByTypeComplete($type); // } // } // }else{ // $complete_type = ''; // $type = ''; // } // // echo $type;exit; // $compliance_query = $required_fields->getComplianceQuery(); // $sales_query = $compliance_query." WHERE // ( // CONCAT( // IF( // v.conduction_sticker IS NOT NULL, // v.conduction_sticker, // '' // ),'', // IF( // v.plate_number IS NOT NULL, // v.plate_number, // '' // ),'', // IF( // s.order_number IS NOT NULL, // s.order_number, // '' // ),'', // IF(c.id IS NOT NULL, // 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 '%%%s%%' OR s.id= '%s' OR v.id= '%s' // ) // %s /email/ // %s /* complete type / // %s / specific complete incomplete type eg facebook / // %s / date_filter / // %s / specific sales_record / // %s / asa id / // %s / company filter / // AND s.status = 1 // AND c.id IS NOT NULL // GROUP BY v.id,s.id // ORDER BY // s.activity_date DESC // "; // $count = " count(s.id) "; // $fields = " s.asa_sales_person as asa_sc_id, // s.asa_group_manager_id as asa_sm_id, // c.id AS customer_id, // s.id AS sales_id, // v.id AS vehicle_id, // IF( // c.type = 1, // CONCAT( // c.first_name, // ' ', // c.middle_name, // ' ', // c.last_name // ), // c.corporation_name // ) AS customer_name, // c.corporation_name, // c.first_name, // c.middle_name, // c.last_name, // c.type AS customer_type, // cac.name AS customer_affiliation, // ag.gender_name as gender, // c.date_of_birth, // cc.address_1, // cc.email_1, // cc.mobile_phone_1, // cc.facebook, // c2.corporation_name AS customer_company, // c.occupation AS position, // snob.name AS nature_of_business, // sp.name as profession, // v.conduction_sticker, // v.plate_number, // sb.name as brand_name, // scm.name as model_name, // scmv.name as model_variant, // v.vehicle_release_year as model_year, // vc.name as vehicle_color, // sdms.name as dms, // sc.code as company, // sd.code as dealer, // s.end_user, // s.activity_date as release_date, // smos.name as mode_of_sale, // stp.name as type_of_payment, // sic.name as insurance, // soi.name as source_of_sale, // sbo.description as brand_offered, // CONCAT(sct.first_name,' ',sct.last_name) as sales_consultant, // CONCAT(sct2.first_name,' ',sct2.last_name) as sales_manager "; // // echo sprintf($sales_query, $fields, $search, $search,$search,$asa_id)." LIMIT $offset,$limit";exit; // $offset_limit = ($offset == '' \|\| $limit == '') ? '' : " LIMIT $offset,$limit" ; // // echo (sprintf($sales_query, $fields, $search, $search,$search,$filter_email,$complete_type,$type,$date_filter,$sales_record,$asa_id,$company_filter).$offset_limit);exit; // // echo (sprintf($sales_query, $fields, $search, $search,$search,$complete_type,$type,$date_filter,$asa_id,$company_filter).$offset_limit); // $sales_list_query = $db -> sql_query(sprintf($sales_query, $fields, $search, $search,$search,$complete_type,$type,$date_filter,$sales_record,$asa_id,$company_filter).$offset_limit); // $sales_list_query_count = $db -> select("SELECT COUNT(1) FROM ( ".sprintf($sales_query, $count, $search,$search,$search ,$complete_type,$type,$date_filter,$sales_record, $asa_id,$company_filter)." ) as total_count"); // foreach($sales_list_query as $row) { // //replace null to empty value // $row = array_map(function($v){ // return (is_null($v)) ? "" : $v; //replace blank the null values // },$row); // //check if complete or not // $status = true; // $required_field = $required_fields->getRequiredFields(); //get trequired fields array // foreach($required_field as $key => $item) { // if( // (trim($row[$key]) == '' \|\| trim($row[$key]) == '0') && // ($key != 'sales_consultant' && $key != 'sales_manager' && $key != 'end_user' && $key != 'customer_company') && // ($key != 'gender' && $key != 'date_of_birth' && $key != 'profession' && $key != 'position') // ){ // $status = false; // break; // } // } // // echo intval($asa_id_record_id). ' '. intval($row['asa_sc_id']);exit; // if(intval($asa_id_record_id) !== 0){ // $access = intval($asa_id_record_id) == intval($row['asa_sc_id']); // }else{ // $access = false; // } // $json_arr['rows'] = array_merge($json_arr['rows'] ,array( array( // 'asa_sc_id'=>$row['asa_sc_id'], // 'asa_sm_id'=>$row['asa_sm_id'], // 'customer_id'=>$row['customer_id'], // 'sales_id'=>$row['sales_id'], // 'vehicle_id'=>$row['vehicle_id'], // 'customer_name'=> $row['customer_name'], // 'corporation_name'=> $row['corporation_name'], // 'first_name'=> $row['first_name'], // 'middle_name'=> $row['middle_name'], // 'last_name'=> $row['last_name'], // 'customer_type'=> $row['customer_type'], // 'customer_affiliation'=>$row['customer_affiliation'], // 'release_date'=>$row['release_date'], // 'gender'=>$row['gender'], // 'date_of_birth'=>$row['date_of_birth'], // 'address_1'=>$row['address_1'], // 'email_1'=>$row['email_1'], // 'mobile_phone_1'=>$row['mobile_phone_1'], // 'facebook'=>$row['facebook'], // 'customer_company'=>$row['customer_company'], // 'position'=>$row['position'], // 'nature_of_business'=>$row['nature_of_business'], // 'profession'=>$row['profession'], // 'conduction_sticker'=>$row['conduction_sticker'], // 'plate_number'=>$row['plate_number'], // 'brand_name'=>$row['brand_name'], // 'model_name'=>$row['model_name'], // 'model_variant'=>$row['model_variant'], // 'model_year'=>$row['model_year'], // 'vehicle_color'=>$row['vehicle_color'], // 'dms'=>$row['dms'], // 'company'=>$row['company'], // 'dealer'=>$row['dealer'], // 'end_user'=>$row['end_user'], // 'release_date'=>$row['release_date'], // 'mode_of_sale'=>$row['mode_of_sale'], // 'type_of_payment'=>$row['type_of_payment'], // 'insurance'=>$row['insurance'], // 'source_of_sale'=>$row['source_of_sale'], // 'brand_offered'=>$row['brand_offered'], // 'sales_consultant'=>$row['sales_consultant'], // 'sales_manager'=>$row['sales_manager'], // 'status'=>($status) ? 'COM' : 'INC' , // 'access'=>($access) ? true : false , // ))); // } // $json_arr['total'] = $sales_list_query_count; //total number of result // $json_arr['result'] = intval($sales_list_query_count) > 0 ? true : false ; // return $json_arr; } function read_sales_future_vehicle($user_id, $all_tag_access, $attended_filter, $company, $brand, $start_date, $end_date, $search, $offset, $limit, $required_fields, $secure, $utility, $db) { $json_arr['rows'] = array(); $company_filter = ($company == '') ? '' : " AND scd.company_id IN (" . $company . ") "; $brand_filter = ($brand == '') ? '' : " AND sbsbo.id IN (" . $brand . ") "; $view_all = ($all_tag_access) ? '' : " AND ( (fvas.added_by = $user_id AND fvas.status <> 0) OR fvas.id IS NULL) "; $attended_filter_status = $attended_filter; if ($utility->isNotEmpty($attended_filter)) { if (intval($attended_filter_status) === 1) { $attended_filter = " AND fvas.id IS NOT NULL "; } else { $attended_filter = " AND fvas.id IS NULL "; } } else { $attended_filter = ''; } // $attended_filter = ($attended_filter == '') ? '' : " AND fvas.id IS NOT NULL "; if ($start_date <> '' && $end_date <> '' && $start_date <> '1970-01-01') { $date_filter = " AND DATE_FORMAT(s.activity_date,'%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') "; } else { $date_filter = ""; } if ($search != '') { $search_alpha_numeric_only = $utility->alphaNumericOnly($search); $search = " AND (CONCAT( IF(c.id IS NOT NULL, 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 CONCAT( IF( v.conduction_sticker IS NOT NULL, v.conduction_sticker, '' ),'', IF( v.plate_number IS NOT NULL, v.plate_number, '' ),'', IF( s.order_number IS NOT NULL, s.order_number, '' )) LIKE '%$search_alpha_numeric_only%' OR CONCAT(sct.first_name,' ',sct.last_name,' ',sct.first_name) LIKE '%$search%' OR cc.email_1 = '" . $search . "') "; } $offset_limit = ($utility->isNotEmpty($offset) && $utility->isNotEmpty($limit)) ? " LIMIT $offset,$limit " : ''; $field = " t1.asa_sales_person as asa_sc_id, t1.m_asa_group_manager_id as asa_sm_id, t1.customer_id, t1.sales_id, t1.m_vehicle_id AS vehicle_id, t1.m_customer_name AS customer_name, t1.m_corporation_name AS corporation_name, t1.m_first_name AS first_name, t1.m_middle_name AS middle_name, t1.m_last_name AS last_name, t1.m_customer_type AS customer_type, t1.m_customer_affiliation AS customer_affiliation, t1.m_gender as gender, t1.m_date_of_birth as date_of_birth, t1.m_address_1 AS address_1, t1.m_email_1 AS email_1, t1.m_mobile_phone_1 AS mobile_phone_1, t1.m_facebook AS facebook, t1.m_customer_company AS customer_company, t1.m_position AS position, t1.m_nature_of_business AS nature_of_business, t1.m_profession as profession, t1.m_conduction_sticker as conduction_sticker, t1.m_plate_number as plate_number, t1.m_brand_name as brand_name, t1.m_model_name as model_name, t1.m_model_variant as model_variant, t1.m_model_year as model_year, t1.m_vehicle_color as vehicle_color, t1.m_dms as dms, t1.m_company as company, t1.m_dealer as dealer, t1.m_assigned_sc as assigned_sc, t1.m_assigned_sc_name as assigned_sc_name, t1.m_future_from_db as future_from_db, t1.m_end_user as end_user, t1.m_release_date as release_date, DATEDIFF(NOW(), t1.m_release_date) as release_date_count, t1.m_mode_of_sale as mode_of_sale, t1.m_type_of_payment as type_of_payment, t1.m_insurance as insurance, t1.m_source_of_sale as source_of_sale, t1.m_brand_offered as brand_offered, t1.m_brand_offered_brand as brand_offered_brand, t1.m_brand_offered_model as brand_offered_model, t1.m_sales_consultant as sales_consultant, t1.m_sales_manager as sales_manager, IF(t1.m_customer_type = 1,'Individual',IF(t1.m_customer_type = 2,'Corporation','')) as customer_type_name, /t1.m_customer_type_name as customer_type_name,/ t1.m_vehicle_image as vehicle_image, t1.m_customer_category as customer_category, t1.m_is_active as is_active, IF(t1.complete = 1,'COM','INC') as status, IF(t1.complete = 1 AND t1.m_complete_date = '',t1.m_release_date,t1.m_complete_date) as complete_date, t1.m_incomplete_date as incomplete_date, /t1.m_commission_status as commission_status, t1.m_commission_status_id as commission_status_id,/ IF((t1.complete = 0 AND (t1.m_commission_status_id = 0 \|\| t1.m_commission_status_id = 3)) OR (t1.complete = 1 AND t1.m_commission_status_id = 3),'HOLD', IF((t1.complete = 1 AND t1.m_commission_status_id = 0) OR (t1.complete = 1 AND t1.m_commission_status_id = 1) OR (t1.complete = 0 AND t1.m_commission_status_id = 1),'FOR RELEASE', 'RELEASED' )) as commission_status, IF((t1.complete = 0 AND (t1.m_commission_status_id = 0 \|\| t1.m_commission_status_id = 3)) OR (t1.complete = 1 AND t1.m_commission_status_id = 3),'0', IF((t1.complete = 1 AND t1.m_commission_status_id = 0) OR (t1.complete = 1 AND t1.m_commission_status_id = 1) OR (t1.complete = 0 AND t1.m_commission_status_id = 1),'1', '2' )) as commission_status_id, DATE_FORMAT(t1.m_commission_hold_date,'%M %d, %Y - %h:%m%p') as hold_commission_date, t1.m_commission_hold_by as hold_commission_by, DATE_FORMAT(t1.m_commission_release_date,'%M %d, %Y - %h:%m%p') as released_commission_date, t1.m_commission_release_by as released_commission_by, DATE_FORMAT(t1.m_commission_for_release_date,'%M %d, %Y - %h:%m%p') as for_release_commission_date, t1.m_commission_for_release_by as for_release_commission_by, t1.m_commission_accounting_status as commission_accounting_status, t1.m_commission_accounting_status_id as commission_accounting_status_id, t1.m_future_vehicle_status as future_vehicle_status, t1.m_future_vehicle_status_id as future_vehicle_status_id "; $query = "SELECT %s FROM (" . $required_fields->getAllInOneComplianceQuery() . " AND s.asa_sales_person <> 0 %s / search / %s / company / %s /brand offer/ %s / date / %s / attened filter / AND v.status = 1 AND s.status = 1 AND (s.sales_person <> 0 OR s.sales_person <> '') AND c.id IS NOT NULL AND c.id <> 0 AND c.status = 1 AND sbsbo.id IS NOT NULL AND sbsbo.id <> 0 AND (sc.id IS NOT NULL AND sc.id <> 0) %s GROUP BY s.id ) as t1 WHERE 1 %s /limit/"; $magic_query = sprintf($query, $field, $search, $company_filter, $brand_filter, $date_filter, $attended_filter, $view_all, $offset_limit); // echo $magic_query; $list = $db->sql_query($magic_query); $list_count = $db->sql_num_rows(sprintf($query, ' 1 ', $search, $company_filter, $brand_filter, $date_filter, $attended_filter, $view_all, '')); $future_vehicle_no = $offset; $asa_ids = array(); foreach ($list as $row) { $asa_ids[] = $row['asa_sc_id']; } $asa_info = $utility->curl_me( 'https://www.autohub.ph/connect/LOAD_SALES_PERSON.php', array('apiKey' => '3892635489643867', 'ids' => json_encode($asa_ids)), "POST" ); $asa_info_arr = json_decode($asa_info, true); // print_r($asa_info_arr['rows']);exit; foreach ($list as $row) { //replace null to empty value $row = array_map(function ($v) { return (is_null($v)) ? "" : $v; //replace blank the null values }, $row); // if(!($utility->isNotEmpty($row['assigned_sc']) && intval($row['assigned_status']) && $row['assigned_added_by'] == $user_id) \|\| // intval($row['assigned_status']) \|\| // $utility->isNotEmpty($row['assigned_sc']) // ){ // continue; // } //check if complete or not $status = true; $future_vehicle_no++; $asa_sc_mobile = ''; foreach ($asa_info_arr['rows'] as $asa_row_info) { // echo ($asa_row_info['u_id']); if ($asa_row_info['u_id'] == $row['asa_sc_id']) { $asa_sc_mobile = $asa_row_info['usr_mobile']; break; } } // echo $row['future_from_db']; $hidden_contact = $utility->isNotEmpty($row['future_from_db']); $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'future_vehicle_no' => strval($future_vehicle_no), 'asa_sc_id' => $row['asa_sc_id'], 'asa_sm_id' => $row['asa_sm_id'], 'customer_id' => $row['customer_id'], 'sales_id' => $row['sales_id'], 'vehicle_id' => $row['vehicle_id'], 'customer_name' => $row['customer_name'], 'corporation_name' => $row['corporation_name'], 'first_name' => $row['first_name'], 'middle_name' => $row['middle_name'], 'last_name' => $row['last_name'], 'customer_affiliation' => $row['customer_affiliation'], 'release_date' => $row['release_date'], 'gender' => $row['gender'], 'date_of_birth' => $row['date_of_birth'], // 'address_1'=>!$utility->isNotEmpty($row['address_1']) ? : ($secure) ? $utility->mask($row['address_1'],3,0) : $row['address_1'], 'email_1' => (!$hidden_contact) ? $utility->mask_email($row['email_1']) : $row['email_1'], 'mobile_phone_1' => !$utility->isNotEmpty($row['mobile_phone_1']) ? "" : ((!$hidden_contact) ? '+' . $utility->mask($row['mobile_phone_1'], 4, 0) : $row['mobile_phone_1']), // 'facebook'=>!$utility->isNotEmpty($row['facebook']) ? : ($hidden_contact) ? $utility->mask($row['facebook'],4,0) : $row['facebook'], 'customer_company' => $row['customer_company'], 'position' => $row['position'], 'nature_of_business' => $row['nature_of_business'], 'profession' => $row['profession'], 'conduction_sticker' => $row['conduction_sticker'], 'plate_number' => $row['plate_number'], 'brand_name' => $row['brand_name'], 'model_name' => $row['model_name'], 'brand_model' => $row['brand_name'] . ' - ' . $row['model_name'], 'brand_offered_brand' => $row['brand_offered_brand'] . ($utility->isNotEmpty($row['brand_offered_model']) ? ' - ' . $row['brand_offered_model'] : ''), 'model_variant' => $row['model_variant'], 'model_year' => $row['model_year'], 'vehicle_color' => $row['vehicle_color'], 'dms' => $row['dms'], 'company_dealer' => $row['company'] . ' - ' . $row['dealer'], 'company' => $row['company'], 'dealer' => $row['dealer'], 'end_user' => $row['end_user'], 'release_date' => $row['release_date'], 'release_date_count' => $row['release_date_count'], 'mode_of_sale' => $row['mode_of_sale'], 'type_of_payment' => $row['type_of_payment'], 'insurance' => $row['insurance'], 'source_of_sale' => $row['source_of_sale'], 'brand_offered' => $row['brand_offered'], 'sales_consultant' => $row['sales_consultant'], 'sc_info' => $row['sales_consultant'] . ' - (' . $asa_sc_mobile . ')', 'sales_manager' => $row['sales_manager'], 'status' => $row['status'], 'commission' => $row['commission_status'], 'assigned_sc' => $row['assigned_sc'], 'assigned_sc_name' => $row['assigned_sc_name'], 'commission_id' => $row['commission_status_id'], 'commission_accounting_status' => $row['commission_accounting_status'], 'commission_accounting_status_id' => $row['commission_accounting_status_id'], 'future_vehicle_status' => $row['future_vehicle_status'], 'future_vehicle_status_id' => $row['future_vehicle_status_id'], 'commission_id_released' => $row['commission_status_id'], 'hold_commission_date' => $row['hold_commission_date'], 'hold_commission_by' => $row['hold_commission_by'], 'released_commission_date' => $row['released_commission_date'], 'released_commission_by' => $row['released_commission_by'], 'for_release_commission_date' => $row['for_release_commission_date'], 'for_release_commission_by' => $row['for_release_commission_by'], 'customer_type' => $row['customer_type'], 'customer_type_name' => $row['customer_type_name'], 'customer_category' => $row['customer_category'], 'vehicle_image' => $row['vehicle_image'], 'complete_date' => $row['complete_date'], 'is_active' => (intval($row['is_active']) == 1 ? 'Active' : 'Inactive'), 'future_from_db' => $row['future_from_db'], ))); } $json_arr['total'] = intval($list_count); $json_arr['result'] = intval($list_count) > 0 ? true : false; return ($json_arr); } function read_car_clubs($company, $start_date, $end_date, $search, $offset, $limit, $required_fields, $secure, $utility, $db) { $json_arr['rows'] = array(); $company_filter = ($company == '') ? '' : " AND scd.company_id IN (" . $company . ") "; if ($start_date <> '' && $end_date <> '' && $start_date <> '1970-01-01') { $date_filter = " AND DATE_FORMAT(s.activity_date,'%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') "; } else { $date_filter = ""; } if ($search != '') { $search_alpha_numeric_only = $utility->alphaNumericOnly($search); $search = " AND (CONCAT( IF(c.id IS NOT NULL, 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 ) ,''),'', IF( cac.id IS NOT NULL, cac.name, '' ) ) LIKE '%$search%' OR CONCAT( IF( v.conduction_sticker IS NOT NULL, v.conduction_sticker, '' ),'', IF( v.plate_number IS NOT NULL, v.plate_number, '' )) LIKE '%$search_alpha_numeric_only%' OR s.id= '$search' OR v.id= '$search' OR cc.email_1 = '" . $search . "') "; } $offset_limit = ($utility->isNotEmpty($offset) && $utility->isNotEmpty($limit)) ? " LIMIT $offset,$limit " : ''; $field = " t1.asa_sales_person as asa_sc_id, t1.m_asa_group_manager_id as asa_sm_id, t1.customer_id, t1.sales_id, t1.m_vehicle_id AS vehicle_id, t1.m_customer_name AS customer_name, t1.m_corporation_name AS corporation_name, t1.m_first_name AS first_name, t1.m_middle_name AS middle_name, t1.m_last_name AS last_name, t1.m_customer_type AS customer_type, t1.m_customer_affiliation AS customer_affiliation, t1.m_gender as gender, t1.m_date_of_birth as date_of_birth, t1.m_address_1 AS address_1, t1.m_email_1 AS email_1, t1.m_mobile_phone_1 AS mobile_phone_1, t1.m_facebook AS facebook, t1.m_customer_company AS customer_company, t1.m_position AS position, t1.m_nature_of_business AS nature_of_business, t1.m_profession as profession, t1.m_conduction_sticker as conduction_sticker, t1.m_plate_number as plate_number, t1.m_brand_name as brand_name, t1.m_model_name as model_name, t1.m_model_variant as model_variant, t1.m_model_year as model_year, t1.m_vehicle_color as vehicle_color, t1.m_dms as dms, t1.m_company as company, t1.m_dealer as dealer, t1.m_end_user as end_user, t1.m_release_date as release_date, DATEDIFF(NOW(), t1.m_release_date) as release_date_count, t1.m_mode_of_sale as mode_of_sale, t1.m_type_of_payment as type_of_payment, t1.m_insurance as insurance, t1.m_source_of_sale as source_of_sale, t1.m_brand_offered as brand_offered, t1.m_brand_offered_brand as brand_offered_brand, t1.m_sales_consultant as sales_consultant, t1.m_sales_manager as sales_manager, IF(t1.m_customer_type = 1,'Individual',IF(t1.m_customer_type = 2,'Corporation','')) as customer_type_name, /t1.m_customer_type_name as customer_type_name,/ t1.m_vehicle_image as vehicle_image, t1.m_customer_category as customer_category, t1.m_is_active as is_active, IF(t1.complete = 1,'COM','INC') as status, IF(t1.complete = 1 AND t1.m_complete_date = '',t1.m_release_date,t1.m_complete_date) as complete_date, t1.m_incomplete_date as incomplete_date, /t1.m_commission_status as commission_status, t1.m_commission_status_id as commission_status_id,/ IF((t1.complete = 0 AND (t1.m_commission_status_id = 0 \|\| t1.m_commission_status_id = 3)) OR (t1.complete = 1 AND t1.m_commission_status_id = 3),'HOLD', IF((t1.complete = 1 AND t1.m_commission_status_id = 0) OR (t1.complete = 1 AND t1.m_commission_status_id = 1) OR (t1.complete = 0 AND t1.m_commission_status_id = 1),'FOR RELEASE', 'RELEASED' )) as commission_status, IF((t1.complete = 0 AND (t1.m_commission_status_id = 0 \|\| t1.m_commission_status_id = 3)) OR (t1.complete = 1 AND t1.m_commission_status_id = 3),'0', IF((t1.complete = 1 AND t1.m_commission_status_id = 0) OR (t1.complete = 1 AND t1.m_commission_status_id = 1) OR (t1.complete = 0 AND t1.m_commission_status_id = 1),'1', '2' )) as commission_status_id, DATE_FORMAT(t1.m_commission_hold_date,'%M %d, %Y - %h:%m%p') as hold_commission_date, t1.m_commission_hold_by as hold_commission_by, DATE_FORMAT(t1.m_commission_release_date,'%M %d, %Y - %h:%m%p') as released_commission_date, t1.m_commission_release_by as released_commission_by, DATE_FORMAT(t1.m_commission_for_release_date,'%M %d, %Y - %h:%m%p') as for_release_commission_date, t1.m_commission_for_release_by as for_release_commission_by, t1.m_commission_accounting_status as commission_accounting_status, t1.m_commission_accounting_status_id as commission_accounting_status_id, t1.m_future_vehicle_status as future_vehicle_status, t1.m_future_vehicle_status_id as future_vehicle_status_id "; $query = "SELECT %s FROM (" . $required_fields->getAllInOneComplianceQuery() . " /AND s.asa_sales_person <> 0 / %s / search / %s /company/ %s / date / AND cac.affiliation_id = 5 AND cac.status = 1 AND cal.status = 1 AND v.status = 1 AND s.status = 1 AND c.id IS NOT NULL AND c.id <> 0 AND c.status = 1 GROUP BY s.id ) as t1 WHERE 1 %s /limit/"; // echo $query ;exit; $magic_query = sprintf($query, $field, $search, $company_filter, $date_filter, $offset_limit); // echo $magic_query;exit; $list = $db->sql_query($magic_query); $list_count = $db->sql_num_rows(sprintf($query, ' 1 ', $search, $company_filter, $date_filter, '')); $future_vehicle_no = 0; foreach ($list as $row) { //replace null to empty value $row = array_map(function ($v) { return (is_null($v)) ? "" : $v; //replace blank the null values }, $row); //check if complete or not $status = true; $future_vehicle_no++; $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'future_vehicle_no' => strval($future_vehicle_no), 'asa_sc_id' => $row['asa_sc_id'], 'asa_sm_id' => $row['asa_sm_id'], 'customer_id' => $row['customer_id'], 'sales_id' => $row['sales_id'], 'vehicle_id' => $row['vehicle_id'], 'customer_name' => $row['customer_name'], 'corporation_name' => $row['corporation_name'], 'first_name' => $row['first_name'], 'middle_name' => $row['middle_name'], 'last_name' => $row['last_name'], 'customer_affiliation' => $row['customer_affiliation'], 'release_date' => $row['release_date'], 'gender' => $row['gender'], 'date_of_birth' => $row['date_of_birth'], 'address_1' => !$utility->isNotEmpty($row['address_1']) ?: ($secure) ? $utility->mask($row['address_1'], 3, 0) : $row['address_1'], 'email_1' => ($secure) ? $utility->mask_email($row['email_1']) : $row['email_1'], 'mobile_phone_1' => !$utility->isNotEmpty($row['mobile_phone_1']) ?: ($secure) ? $utility->mask($row['mobile_phone_1'], 4, 0) : $row['mobile_phone_1'], 'facebook' => !$utility->isNotEmpty($row['facebook']) ?: ($secure) ? $utility->mask($row['facebook'], 4, 0) : $row['facebook'], 'customer_company' => $row['customer_company'], 'position' => $row['position'], 'nature_of_business' => $row['nature_of_business'], 'profession' => $row['profession'], 'conduction_sticker' => $row['conduction_sticker'], 'plate_number' => $row['plate_number'], 'brand_name' => $row['brand_name'], 'model_name' => $row['model_name'], 'brand_model' => $row['brand_name'] . ' - ' . $row['model_name'], 'brand_offered_brand' => $row['brand_offered_brand'], 'model_variant' => $row['model_variant'], 'model_year' => $row['model_year'], 'vehicle_color' => $row['vehicle_color'], 'dms' => $row['dms'], 'company' => $row['company'], 'dealer' => $row['dealer'], 'end_user' => $row['end_user'], 'release_date' => $row['release_date'], 'release_date_count' => $row['release_date_count'], 'mode_of_sale' => $row['mode_of_sale'], 'type_of_payment' => $row['type_of_payment'], 'insurance' => $row['insurance'], 'source_of_sale' => $row['source_of_sale'], 'brand_offered' => $row['brand_offered'], 'sales_consultant' => $row['sales_consultant'], 'sales_manager' => $row['sales_manager'], 'status' => $row['status'], 'commission' => $row['commission_status'], 'commission_id' => $row['commission_status_id'], 'commission_accounting_status' => $row['commission_accounting_status'], 'commission_accounting_status_id' => $row['commission_accounting_status_id'], 'future_vehicle_status' => $row['future_vehicle_status'], 'future_vehicle_status_id' => $row['future_vehicle_status_id'], 'commission_id_released' => $row['commission_status_id'], 'hold_commission_date' => $row['hold_commission_date'], 'hold_commission_by' => $row['hold_commission_by'], 'released_commission_date' => $row['released_commission_date'], 'released_commission_by' => $row['released_commission_by'], 'for_release_commission_date' => $row['for_release_commission_date'], 'for_release_commission_by' => $row['for_release_commission_by'], 'customer_type' => $row['customer_type'], 'customer_type_name' => $row['customer_type_name'], 'customer_category' => $row['customer_category'], 'vehicle_image' => $row['vehicle_image'], 'complete_date' => $row['complete_date'], 'is_active' => (intval($row['is_active']) == 1 ? 'Active' : 'Inactive'), ))); } $json_arr['total'] = intval($list_count); $json_arr['result'] = intval($list_count) > 0 ? true : false; return ($json_arr); } function read_sales_compliance_status_history($sales_id, $utility, $db) { $list = $db->sql_query("SELECT DATE_FORMAT(t1.date_added,'%M %d, %Y - %h:%m%p') as date, CONCAT(e.first_name,' ',e.last_name) as user, t1.status, t1.description FROM ( SELECT scs.sales_id,scs.employee_id,scss.name as status,scs.description,scs.date_added FROM sales_commission_status scs INNER JOIN source_commission_status scss ON scs.commission_status = scss.id WHERE sales_id = $sales_id UNION ALL SELECT scas.sales_id,scas.employee_id,scass.name as status,'',scas.date_added FROM sales_commission_accounting_status scas INNER JOIN source_commission_accounting_status scass ON scas.commission_accounting_status = scass.id WHERE sales_id = $sales_id ) t1 INNER JOIN employee e ON t1.employee_id=e.id ORDER BY t1.date_added DESC"); $data = array(); foreach ($list as $row) { $data = array_merge($data, array(array('date' => $row['date'], 'user' => $row['user'], 'status' => $row['status'], 'description' => $row['description']))); } echo json_encode($data); } function read_sales_complete_incomplete_count($asa_id, $required_fields, $db) { $incomplete_where = $required_fields->getIncompleteWhere(); $complete_where = $required_fields->getCompleteWhere(); $compliance_query = $required_fields->getComplianceQuery(); $sales_query = $compliance_query . " WHERE 1 %s AND s.asa_sales_person = '%s' AND s.status = 1 AND c.id IS NOT NULL GROUP BY v.id,s.id ORDER BY s.activity_date DESC "; $count = " count(s.id) "; // echo sprintf($sales_query, $count ,$complete_where, $asa_id);exit; $sales_list_query_count_complete = $db->select("SELECT COUNT(1) FROM ( " . sprintf($sales_query, $count, $complete_where, $asa_id) . " ) as total_count"); $sales_list_query_count_incomplete = $db->select("SELECT COUNT(1) FROM ( " . sprintf($sales_query, $count, $incomplete_where, $asa_id) . " ) as total_count"); $result_arr["sales_consultant_data"] = array(); $result_arr["sales_consultant_data"] = array_merge( $result_arr["sales_consultant_data"], array(array( 'total' => number_format($sales_list_query_count_complete + $sales_list_query_count_incomplete), 'complete' => number_format($sales_list_query_count_complete), 'incomplete' => number_format($sales_list_query_count_incomplete), 'score' => ($sales_list_query_count_complete + $sales_list_query_count_incomplete) == 0 ? '0.00%' : number_format(($sales_list_query_count_complete / ($sales_list_query_count_complete + $sales_list_query_count_incomplete)) 100, 2) . '%', )) ); echo json_encode($result_arr); } function getComplianceSummaryCount($asa_id, $sc_id, $start_date, $end_date, $company, $required_fields, $db) { $company = ($company == '') ? '' : " AND sc.id IN (" . $company . ") "; if ($start_date != '' && $end_date != '' && $start_date <> '1970-01-01') { $date_filter = " AND DATE_FORMAT(s.activity_date,'%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') "; } else { $date_filter = ''; } $asa_id = ($asa_id == '') ? '' : " AND s.asa_sales_person = '$asa_id' "; $sc_id = ($sc_id == '') ? '' : " AND s.sales_person = '$sc_id' "; $field = " sc.id as datacon_sc_id,t1.asa_sales_person as asa_id,CONCAT(sc.first_name,' ',sc.last_name) as name, SUM(t1.complete) as complete, SUM(t1.incomplete) as incomplete, SUM(t1.total) total, ((SUM(t1.complete)/SUM(t1.total))100) as rate, SUM(IF((t1.complete = 0 AND (t1.m_commission_status_id = 0 \|\| t1.m_commission_status_id = 3)) OR (t1.complete = 1 AND t1.m_commission_status_id = 3),1, 0)) as hold, SUM(IF((t1.complete = 1 AND t1.m_commission_status_id = 0) OR (t1.complete = 1 AND t1.m_commission_status_id = 1) OR (t1.complete = 0 AND t1.m_commission_status_id = 1),1,0)) as for_release, SUM(IF(t1.m_commission_status_id = 2,1,0)) as released "; $count = " COUNT(1) "; $query = "SELECT %s FROM (" . $required_fields->getAllInOneComplianceQuery() . " /AND s.asa_sales_person <> 0 / %s /company/ %s / date / %s / asa id / %s /sc id/ AND v.status = 1 AND s.status = 1 AND c.id IS NOT NULL AND c.id <> 0 AND c.status = 1 GROUP BY s.id ) as t1 INNER JOIN sales_consultant sc ON t1.sales_person = sc.id WHERE 1 AND (sc.id IS NOT NULL AND sc.id <> 0)"; // echo (sprintf($query,$field,$company,$date_filter,$asa_id,$sc_id));exit; $list = $db->sql_query(sprintf($query, $field, $company, $date_filter, $asa_id, $sc_id)); $complete = 0; $incomplete = 0; $total = 0; $for_release = 0; $released = 0; $hold = 0; $rate = '0%'; foreach ($list as $row) { $row = array_map(function ($v) { return (is_null($v)) ? "0" : $v; //replace blank the null values }, $row); $complete = $row['complete']; $incomplete = $row['incomplete']; $for_release = $row['for_release']; $released = $row['released']; $hold = $row['hold']; $total = $row['total']; $rate = number_format($row['rate'], 2) . '%'; } $counts['complete_compliance'] = $complete; $counts['incomplete_compliance'] = $incomplete; $counts['total_compliance'] = $total; $counts['hold_compliance'] = $hold; $counts['for_release_compliance'] = $for_release; $counts['released_compliance'] = $released; $counts['rate_compliance'] = $rate; // $list = $db->sql_query(sprintf($query,$field,$company,$date_filter)); // $counts = $this->getComplianceSummaryCountFetch($asa_id,$start_date,$end_date,$company,$required_fields,$db); echo json_encode($counts); } // function getComplianceSummaryCountFetch($asa_id,$start_date,$end_date,$company,$required_fields,$db){ //deprecated // $incomplete_where = $required_fields -> getIncompleteWhere(); // $complete_where = $required_fields -> getCompleteWhere(); // if($start_date != '' && $end_date != '' && $start_date <> '1970-01-01'){ // $date_filter = " AND DATE_FORMAT(s.activity_date,'%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') "; // }else{ // $date_filter = ''; // } // $company_filter = ($company == '') ? '' : " AND scd.company_id IN (".$company.") "; // $asa_id = ($asa_id == '') ? '' : " AND s.asa_sales_person = '$asa_id' "; // $compliance_query = $required_fields->getComplianceQuery(); // $sales_query = $compliance_query." WHERE 1 // %s // %s // %s // %s // AND s.status = 1 // AND c.id IS NOT NULL // GROUP BY v.id,s.id // ORDER BY // s.activity_date DESC // "; // $count = " count(s.id) "; // $counts = array(); // // echo ("SELECT COUNT(1) FROM ( ".sprintf($sales_query, $count ,$complete_where,$date_filter,$asa_id ,$company_filter )." ) as total_count"); exit; // $counts['complete_compliance'] = $db -> select("SELECT COUNT(1) FROM ( ".sprintf($sales_query, $count ,$complete_where,$date_filter,$asa_id ,$company_filter )." ) as total_count"); // $counts['incomplete_compliance'] = $db -> select("SELECT COUNT(1) FROM ( ".sprintf($sales_query, $count ,$incomplete_where,$date_filter,$asa_id ,$company_filter )." ) as total_count"); // $counts['total_compliance'] = intval($counts['complete_compliance']) + intval($counts['incomplete_compliance']); // if($counts['total_compliance'] > 0){ // $score = number_format((intval($counts['complete_compliance']) / $counts['total_compliance'])100,2); // }else{ // $score = 0; // } // $counts['rate_compliance'] = floatval($score).'%'; // return $counts; // } function get_sc_percentage($days) { $days = intval($days); $percentage = "0%"; if ($days <= 14) { $percentage = "100%"; } else if ($days >= 15 && $days <= 30) { $percentage = "50%"; } else if ($days >= 31 && $days <= 50) { $percentage = "30%"; } else if ($days >= 51) { $percentage = "0%"; } return $percentage; } function get_st_percentage($days) { $days = intval($days); $percentage = "0%"; if ($days <= 14) { $percentage = "0%"; } else if ($days >= 15 && $days <= 30) { $percentage = "50%"; } else if ($days >= 31 && $days <= 50) { $percentage = "70%"; } else if ($days >= 51) { $percentage = "100%"; } return $percentage; } function read_required_fields_and_counts($asa_id, $sc_id, $company, $start_date, $end_date, $required_fields, $db) { $company = " AND sc.id IN (" . $company . ") "; if ($start_date != '' && $end_date != '' && $start_date <> '1970-01-01') { $date_filter = " AND DATE_FORMAT(s.activity_date,'%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') "; } else { $date_filter = ''; } $asa_id = ($asa_id == '') ? '' : " AND s.asa_sales_person = '$asa_id' "; $sc_id = ($sc_id == '') ? '' : " AND s.sales_person = '$sc_id' "; $field = " sc.id as datacon_sc_id,t1.asa_sales_person as asa_id,CONCAT(sc.first_name,' ',sc.last_name) as name, SUM(t1.complete) as complete, SUM(t1.incomplete) as incomplete, SUM(t1.total) total, ((SUM(t1.complete)/SUM(t1.total))100) as rate, SUM(t1.gender) gender, SUM(t1.incomplete_gender) incomplete_gender, ((SUM(t1.gender)/SUM(t1.total))100) as gender_rate, SUM(t1.date_of_birth) date_of_birth, SUM(t1.incomplete_date_of_birth) incomplete_date_of_birth, ((SUM(t1.date_of_birth)/SUM(t1.total))100) as date_of_birth_rate, SUM(t1.profession) profession, SUM(t1.incomplete_profession) incomplete_profession, ((SUM(t1.profession)/SUM(t1.total))100) as profession_rate, SUM(t1.position) position, SUM(t1.incomplete_position) incomplete_position, ((SUM(t1.position)/SUM(t1.total))100) as position_rate, SUM(t1.facebook) facebook, SUM(t1.incomplete_facebook) incomplete_facebook, ((SUM(t1.facebook)/SUM(t1.total))100) as facebook_rate, SUM(t1.customer_company) customer_company, SUM(t1.incomplete_customer_company) incomplete_customer_company, ((SUM(t1.customer_company)/SUM(t1.total))100) as customer_company_rate, SUM(t1.brand_name) brand_name, SUM(t1.incomplete_brand_name) incomplete_brand_name, ((SUM(t1.brand_name)/SUM(t1.total))100) as brand_name_rate, SUM(t1.release_date) release_date, SUM(t1.incomplete_release_date) incomplete_release_date, ((SUM(t1.release_date)/SUM(t1.total))100) as release_date_rate, SUM(t1.mode_of_sale) mode_of_sale, SUM(t1.incomplete_mode_of_sale) incomplete_mode_of_sale, ((SUM(t1.mode_of_sale)/SUM(t1.total))100) as mode_of_sale_rate, SUM(t1.end_user) end_user, SUM(t1.incomplete_end_user) incomplete_end_user, ((SUM(t1.end_user)/SUM(t1.total))100) as end_user_rate, SUM(t1.nature_of_business) nature_of_business, SUM(t1.incomplete_nature_of_business) incomplete_nature_of_business, ((SUM(t1.nature_of_business)/SUM(t1.total))100) as nature_of_business_rate, SUM(t1.customer_affiliation) customer_affiliation, SUM(t1.incomplete_customer_affiliation) incomplete_customer_affiliation, ((SUM(t1.customer_affiliation)/SUM(t1.total))100) as customer_affiliation_rate, SUM(t1.type_of_payment) type_of_payment, SUM(t1.incomplete_type_of_payment) incomplete_type_of_payment, ((SUM(t1.type_of_payment)/SUM(t1.total))100) as type_of_payment_rate, SUM(t1.insurance) insurance, SUM(t1.incomplete_insurance) incomplete_insurance, ((SUM(t1.insurance)/SUM(t1.total))100) as insurance_rate, SUM(t1.sales_consultant) sales_consultant, SUM(t1.incomplete_sales_consultant) incomplete_sales_consultant, ((SUM(t1.sales_consultant)/SUM(t1.total))100) as sales_consultant_rate, SUM(t1.sales_manager) sales_manager, SUM(t1.incomplete_sales_manager) incomplete_sales_manager, ((SUM(t1.sales_manager)/SUM(t1.total))100) as sales_manager_rate, SUM(t1.source_of_sale) source_of_sale, SUM(t1.incomplete_source_of_sale) incomplete_source_of_sale, ((SUM(t1.source_of_sale)/SUM(t1.total))100) as source_of_sale_rate, SUM(t1.brand_offered) brand_offered, SUM(t1.incomplete_brand_offered) incomplete_brand_offered, ((SUM(t1.brand_offered)/SUM(t1.total))100) as brand_offered_rate, SUM(t1.address_1) address_1, SUM(t1.incomplete_address_1) incomplete_address_1, ((SUM(t1.address_1)/SUM(t1.total))100) as address_1_rate, SUM(t1.email_1) email_1, SUM(t1.incomplete_email_1) incomplete_email_1, ((SUM(t1.email_1)/SUM(t1.total))100) as email_1_rate, SUM(t1.mobile_phone_1) mobile_phone_1, SUM(t1.incomplete_mobile_phone_1) incomplete_mobile_phone_1, ((SUM(t1.mobile_phone_1)/SUM(t1.total))100) as mobile_phone_1_rate, SUM(t1.conduction_sticker) conduction_sticker, SUM(t1.incomplete_conduction_sticker) incomplete_conduction_sticker, ((SUM(t1.conduction_sticker)/SUM(t1.total))100) as conduction_sticker_rate, SUM(t1.vehicle_color) vehicle_color, SUM(t1.incomplete_vehicle_color) incomplete_vehicle_color, ((SUM(t1.vehicle_color)/SUM(t1.total))100) as vehicle_color_rate, SUM(t1.model_name) model_name, SUM(t1.incomplete_model_name) incomplete_model_name, ((SUM(t1.model_name)/SUM(t1.total))100) as model_name_rate, SUM(t1.model_year) model_year, SUM(t1.incomplete_model_year) incomplete_model_year, ((SUM(t1.model_year)/SUM(t1.total))100) as model_year_rate, SUM(t1.model_variant) model_variant, SUM(t1.incomplete_model_variant) incomplete_model_variant, ((SUM(t1.model_variant)/SUM(t1.total))100) as model_variant_rate, SUM(t1.customer_name) customer_name, SUM(t1.incomplete_customer_name) incomplete_customer_name, ((SUM(t1.customer_name)/SUM(t1.total))100) as customer_name_rate "; $query = "SELECT %s FROM (" . $required_fields->getAllInOneComplianceQuery() . " /AND s.asa_sales_person <> 0 / %s /company/ %s /* date / %s / asa id / %s /sc id/ AND v.status = 1 AND s.status = 1 AND c.id IS NOT NULL AND c.id <> 0 AND c.status = 1 GROUP BY s.id ) as t1 INNER JOIN sales_consultant sc ON t1.sales_person = sc.id WHERE 1 AND (sc.id IS NOT NULL AND sc.id <> 0)"; $list = $db->sql_query(sprintf($query, $field, $company, $date_filter, $asa_id, $sc_id)); $data = array(); $required_field = $required_fields->getRequiredFields(); //get trequired fields array foreach ($required_field as $key => $item) { foreach ($list as $datas) { if (isset($datas[$key])) { $submitted = $datas['total']; $complete = $datas[$key]; $incomplete = $datas['incomplete_' . $key]; $score = $datas[$key . '_rate']; $data = array_merge($data, array(array('information_code' => $key, 'information' => $item, 'submitted' => (string) $submitted, 'score' => (string) floatval($score), 'complete' => (string) $complete, 'incomplete' => (string) $incomplete))); } } } echo json_encode($data); // exit; // // echo $company.'<br>'.$start_date.'<br>'.$end_date; // $data=array(); // $required_field = $required_fields->getRequiredFields(); // $incomplete_fields = $required_fields -> getIncompleteFieldCount(); // $complete_fields = $required_fields -> getCompleteFieldCount(); // $compliance_query = $required_fields -> getComplianceQuery(); // // $fields = $required_fields->getRequiredFieldsCount(); // if($start_date != '' && $end_date != '' && $start_date <> '1970-01-01'){ // $date_filter = " AND DATE_FORMAT(s.activity_date,'%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') "; // }else{ // $date_filter = ''; // } // $company_filter = ($company == '') ? '' : " AND scd.company_id IN (".$company.") "; // $asa_id = ($asa_id == '') ? '' : " AND s.asa_sales_person = '$asa_id' "; // $sc_id = ($asa_id == '') ? '' : " AND s.sales_person = '$sc_id' "; // $query = $compliance_query." WHERE 1 // %s // %s // %s // %s // AND s.status = 1 // AND c.id IS NOT NULL // ORDER BY // s.activity_date DESC // "; // // echo (sprintf($query, $complete_fields.','.$incomplete_fields, '',$date_filter, $asa_id,$company_filter ));exit; // $query_result = $db -> sql_query(sprintf($query, $complete_fields.','.$incomplete_fields, '',$date_filter, $asa_id,$company_filter )); // foreach($required_field as $key => $item) { // if( // ($key != 'sales_consultant' && $key != 'sales_manager' && $key != 'end_user' && $key != 'customer_company') // ){ // foreach($query_result as $datas){ // if(isset($datas[$key])){ // $submitted = intval($datas[$key]) + intval($datas['incomplete_'.$key]); // // if($datas['type'] == '2'){ // // if($datas[$key] == 'profession' \|\| $datas[$key] == '' \|\|){ // // } // // }else{ // // $complete = $datas[$key]; // // } // $complete = $datas[$key]; // $incomplete = $datas['incomplete_'.$key]; // if($submitted > 0){ // $score = number_format(($complete / $submitted)100,2); // }else{ // $score = 0; // } // $data = array_merge($data,array(array('information_code'=>$key,'information'=>$item,'submitted'=>(string) $submitted,'score'=>(string) floatval($score),'complete'=>(string) $complete,'incomplete'=>(string) $incomplete))); // } // } // } // } // echo json_encode($data); } function update_sales_commission($asa_id, $sales_id, $status, $user_id, $description, $utility, $db) { // $db->sql_query("UPDATE sales SET commission_status = '$status',commission_status_modified_by = NULLIF('$user_id',''), commission_status_modified_date = NOW() WHERE id = '$sales_id' "); $db->sql_query("INSERT INTO `sales_commission_status`(`sales_id`, `employee_id`, `commission_status`,`description`) VALUES ('$sales_id','$user_id','$status','$description')"); } function update_sales_commission_accounting($asa_id, $sales_id, $status, $user_id, $utility, $db) { // echo "INSERT INTO `sales_commission_accounting_status`(`sales_id`, `employee_id`, `commission_accounting_status`) VALUES ('$sales_id','$user_id','$status')";exit; $db->sql_query("INSERT INTO `sales_commission_accounting_status`(`sales_id`, `employee_id`, `commission_accounting_status`) VALUES ('$sales_id','$user_id','$status')"); } function update_sales_future_vehicle($asa_id, $sales_id, $status, $user_id, $utility, $db) { // echo "INSERT INTO `sales_commission_accounting_status`(`sales_id`, `employee_id`, `commission_accounting_status`) VALUES ('$sales_id','$user_id','$status')";exit; $db->sql_query("INSERT INTO `sales_future_vehicle_status`(`sales_id`, `employee_id`, `status_id`) VALUES ('$sales_id','$user_id','$status')"); } function update_future_vehicle_assigned_sc_all($sales_id, $utility, $db) { $db->sql_query("UPDATE `future_vehicle_assigned_sc` SET `status`= 0 WHERE `sales_id` = $sales_id"); } function update_future_vehicle_assigned_sc($sales_id, $asa_id, $asa_name, $utility, $db) { $db->sql_query("UPDATE `future_vehicle_assigned_sc` SET `status`= 1,asa_sc_name = '$asa_name' WHERE `sales_id` = $sales_id AND asa_id = $asa_id "); } function read_future_vehicle_assigned_sc($sales_id, $assigned_sc_id, $utility, $db) { $db->sql_query("SELECT * FROM `future_vehicle_assigned_sc` WHERE 1 AND `sales_id` = $sales_id AND `asa_id` = $assigned_sc_id "); } function create_future_vehicle_assigned_sc($sales_id, $assigned_sc_id, $assigned_sc_name, $user_id, $utility, $db) { $db->sql_query("INSERT INTO `future_vehicle_assigned_sc`(`sales_id`, `asa_id`,`asa_sc_name`, `added_by`, `date_added`, `status`) VALUES ('$sales_id','$assigned_sc_id','$assigned_sc_name','$user_id',NOW(),'1')"); } function read_future_tag($sales_id, $asa_id, $user_id, $utility, $db) { return $db->sql_query("SELECT * FROM `future_vehicle_tag_db` WHERE 1 AND `sales_id` = $sales_id AND `assigned_sc` = $asa_id "); } function create_future_tag($sales_id, $asa_id, $user_id, $utility, $db) { return $db->sql_query("INSERT INTO `future_vehicle_tag_db`( `sales_id`, `assigned_sc`, `added_by`, `date_added`, `status`) VALUES ('$sales_id','$asa_id','$user_id',NOW(),1)"); } function update_future_tag($sales_id, $asa_id, $user_id, $status, $utility, $db) { return $db->sql_query("UPDATE `future_vehicle_tag_db` SET `status`= $status WHERE 1 AND `sales_id` = $sales_id AND `assigned_sc` = $asa_id "); } function update_future_tag_all($sales_id, $utility, $db) { return $db->sql_query("UPDATE `future_vehicle_tag_db` SET `status`= 0 WHERE 1 AND `sales_id` = $sales_id "); } }

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