File manager

File manager - Edit - /home/autoph/public_html/data03252025consolidation/api/compliance/required_fields.php

Back
<?php class RequiredFields { function getAllInOneComplianceQuery() { //m_ -> main return " SELECT s.asa_group_manager_id as m_asa_group_manager_id, v.id AS m_vehicle_id, IF( c.type = 1, CONCAT( c.first_name, ' ', c.middle_name, ' ', c.last_name ), c.corporation_name ) AS m_customer_name, c.corporation_name AS m_corporation_name, c.first_name AS m_first_name, c.middle_name AS m_middle_name, c.last_name AS m_last_name, c.type AS m_customer_type, -- IF(cl.customer_id IS NULL, 0, 1) AS has_ltms_records, -- -- 1 AS has_ltms_records, CASE WHEN (DATE(s.activity_date) < '2024-10-01') THEN 1 WHEN (DATE(s.activity_date) >= '2024-10-01' AND cl.customer_id IS NOT NULL) THEN 1 ELSE 0 END AS has_ltms_records, cac.name AS m_customer_affiliation, ag.gender_name as m_gender, c.date_of_birth as m_date_of_birth, cc.address_1 as m_address_1, cc.email_1 as m_email_1, cc.mobile_phone_1 as m_mobile_phone_1, cc.facebook as m_facebook, c2.corporation_name as m_customer_company, c.occupation as m_position, snob.name as m_nature_of_business, sp.name as m_profession, v.conduction_sticker as m_conduction_sticker, v.plate_number as m_plate_number, sb.name as m_brand_name, scm.name as m_model_name, scmv.name as m_model_variant, v.vehicle_release_year as m_model_year, vc.name as m_vehicle_color, sdms.name as m_dms, sc.code as m_company, sd.code as m_dealer, s.end_user as m_end_user, s.activity_date as m_release_date, smos.name as m_mode_of_sale, stp.name as m_type_of_payment, fvas.asa_id as m_assigned_sc, fvas.asa_sc_name as m_assigned_sc_name, fvtd.id as m_future_from_db, sic.name as m_insurance, soi.name as m_source_of_sale, CONCAT(sbsbo.name,' ',scmsbo.name, ' ',sbo.description) as m_brand_offered, sbsbo.name as m_brand_offered_brand, scmsbo.name as m_brand_offered_model, CONCAT(sct.first_name,' ',sct.last_name) as m_sales_consultant, CONCAT(sct2.first_name,' ',sct2.last_name) as m_sales_manager, IF(scs.id IS NULL,'HOLD',scs.name) as m_commission_status, IF(scs.id IS NULL,'0',scs.id) as m_commission_status_id, IF(source_cas.id IS NULL,'ON PROCESS',source_cas.name) as m_commission_accounting_status, IF(source_cas.id IS NULL,'1',source_cas.id) as m_commission_accounting_status_id, IF(source_sfvss.id IS NULL,'UNATTENDED',source_sfvss.name) as m_future_vehicle_status, IF(source_sfvss.id IS NULL,'1',source_sfvss.id) as m_future_vehicle_status_id, IF(scss_hold.id IS NULL,s.activity_date,scss_hold.date_added) as m_commission_hold_date, IF(e_hold.id IS NULL,'',CONCAT(e_hold.first_name,' ',e_hold.last_name)) as m_commission_hold_by, IF(scss_release.id IS NULL,'',scss_release.date_added) as m_commission_release_date, IF(e_release.id IS NULL,'',CONCAT(e_release.first_name,' ',e_release.last_name)) as m_commission_release_by, IF(scss_for_release.id IS NULL,'',scss_for_release.date_added) as m_commission_for_release_date, IF(e_for_release.id IS NULL,'',CONCAT(e_for_release.first_name,' ',e_for_release.last_name)) as m_commission_for_release_by, IF(scs_com.id IS NULL,'',scs_com.date_added) as m_complete_date, IF(scs_inc.id IS NULL,'',scs_inc.date_added) as m_incomplete_date, /scs.name as m_commission_status, scs.id as m_commission_status_id, / /* sctt.name as m_customer_type_name, / IF(vii.id IS NULL,'default.png',vii.name ) as m_vehicle_image, c.id AS customer_id, s.id AS sales_id, s.sales_person, s.asa_sales_person, 1 as total, (IF(((ag.id IS NOT NULL AND ag.id <> 0) AND c.type = 1) , 1, IF((c.type = 2),1,0))) AS gender, (IF(((c.date_of_birth IS NOT NULL AND c.date_of_birth <> '') AND c.type = 1), 1, IF((c.type = 2),1,0))) AS date_of_birth, (IF(((c.occupation IS NOT NULL AND c.occupation <> '') AND c.type = 1), 1, IF((c.type = 2),1,0))) AS position, (IF(((sp.id IS NOT NULL AND sp.id <> 0) AND c.type = 1), 1, IF((c.type = 2),1,0)) ) AS profession, (IF(((cal.id IS NOT NULL AND cal.id <> 0) AND c.type = 1), 1, IF((c.type = 2),1,0)) ) AS customer_affiliation, (IF(((c2.id IS NOT NULL AND c2.id <> 0) AND c.type = 1), 1, IF((c.type = 2),1,0)) ) AS customer_company, (IF(((cc.facebook IS NOT NULL AND cc.facebook <> '') AND c.type = 1), 1, IF((c.type = 2),1,0))) AS facebook, (IF((sb.id IS NOT NULL AND sb.id <> 0), 1, 0)) AS brand_name, (IF((s.activity_date IS NOT NULL AND s.activity_date <> ''), 1, 0)) AS release_date, (IF((smos.id IS NOT NULL AND smos.id <> 0), 1, 0)) AS mode_of_sale, (IF((s.end_user IS NULL OR s.end_user = ''), 1, 0)) AS end_user, (IF((snob.id IS NOT NULL AND snob.id <> 0), 1, 0)) AS nature_of_business, (IF((stp.id IS NOT NULL AND stp.id <> 0), 1, 0)) AS type_of_payment, (IF((sic.id IS NOT NULL AND sic.id <> 0), 1, 0)) AS insurance, (IF((sct.id IS NOT NULL AND sct.id <> 0), 1, 0)) AS sales_consultant, (IF((sct2.id IS NOT NULL AND sct2.id <> 0), 1, 0)) AS sales_manager, (IF((soi.id IS NOT NULL AND soi.id <> 0), 1, 0)) AS source_of_sale, (IF((sbo.id IS NOT NULL AND sbo.id <> 0), 1, 0)) AS brand_offered, (IF((cc.address_1 IS NOT NULL AND cc.address_1 <> ''), 1, 0)) AS address_1, (IF((cc.email_1 IS NOT NULL AND cc.email_1 <> '') , 1, 0)) AS email_1, (IF(((cc.mobile_phone_1 IS NOT NULL AND cc.mobile_phone_1 <> '') OR c.type = 2), 1, 0)) AS mobile_phone_1, (IF((v.conduction_sticker IS NOT NULL AND v.conduction_sticker <> ''), 1, 0)) AS conduction_sticker, (IF((vc.id IS NOT NULL AND vc.id <> 0) , 1, 0)) AS vehicle_color, (IF((scm.id IS NOT NULL AND scm.id <> 0), 1, 0)) AS model_name, (IF((v.vehicle_release_year IS NOT NULL AND v.vehicle_release_year <> ''), 1, 0)) AS model_year, (IF((scmv.id IS NOT NULL AND scmv.id <> 0), 1, 0)) AS model_variant, (IF((c.id IS NOT NULL AND c.id <> 0), 1, 0)) AS customer_name, (IF(((ag.id IS NULL OR ag.id = 0) AND c.type = 1) , 1, 0)) AS incomplete_gender, (IF(((c.date_of_birth IS NULL OR c.date_of_birth = '') AND c.type = 1), 1, 0)) AS incomplete_date_of_birth, (IF(((c.occupation IS NULL OR c.occupation = '') AND c.type = 1), 1, 0)) AS incomplete_position, (IF((((sp.id IS NULL OR sp.id = 0) AND c.type = 1) AND c.type = 1), 1, 0)) AS incomplete_profession, (IF((((cal.id IS NULL OR cal.id = 0) AND c.type = 1) AND c.type = 1), 1, 0)) AS incomplete_customer_affiliation, (IF((((c2.id IS NULL OR c2.id = 0) AND c.type = 1) AND c.type = 1), 1, 0)) AS incomplete_customer_company, (IF((((cc.facebook IS NULL OR cc.facebook = '') AND c.type = 1) AND c.type = 1), 1, 0)) AS incomplete_facebook, (IF((sb.id IS NULL OR sb.id = 0), 1, 0)) AS incomplete_brand_name, (IF((s.activity_date IS NULL OR s.activity_date = ''), 1, 0)) AS incomplete_release_date, (IF((smos.id IS NULL OR smos.id = 0), 1, 0)) AS incomplete_mode_of_sale, (IF((s.end_user IS NULL OR s.end_user = ''), 1, 0)) AS incomplete_end_user, (IF((snob.id IS NULL OR snob.id = 0), 1, 0)) AS incomplete_nature_of_business, (IF((stp.id IS NULL OR stp.id = 0), 1, 0)) AS incomplete_type_of_payment, (IF((sic.id IS NULL OR sic.id = 0), 1, 0)) AS incomplete_insurance, (IF((sct.id IS NULL OR sct.id = 0), 1, 0)) AS incomplete_sales_consultant, (IF((sct2.id IS NULL OR sct2.id = 0), 1, 0)) AS incomplete_sales_manager, (IF((soi.id IS NULL OR soi.id = 0), 1, 0)) AS incomplete_source_of_sale, (IF((sbo.id IS NULL OR sbo.id = 0), 1, 0)) AS incomplete_brand_offered, (IF((cc.address_1 IS NULL OR cc.address_1 = ''), 1, 0)) AS incomplete_address_1, (IF((cc.email_1 IS NULL OR cc.email_1 = '') , 1, 0)) AS incomplete_email_1, (IF(((cc.mobile_phone_1 IS NULL OR cc.mobile_phone_1 = '') AND c.type <> 2), 1, 0)) AS incomplete_mobile_phone_1, (IF((v.conduction_sticker IS NULL OR v.conduction_sticker = ''), 1, 0)) AS incomplete_conduction_sticker, (IF((vc.id IS NULL OR vc.id = 0) , 1, 0)) AS incomplete_vehicle_color, (IF((scm.id IS NULL OR scm.id = 0), 1, 0)) AS incomplete_model_name, (IF((v.vehicle_release_year IS NULL OR v.vehicle_release_year = ''), 1, 0)) AS incomplete_model_year, (IF((scmv.id IS NULL OR scmv.id = 0), 1, 0)) AS incomplete_model_variant, (IF((c.id IS NULL OR c.id = 0), 1, 0)) AS incomplete_customer_name, (IF((s.activity_date >= '2024-10-01' AND cl.customer_id IS NULL), 1,0)) AS incomplete_ltms , c.type, ( IF( " . $this->getCompleteWhere() . " ,1,0 ) ) AS complete, ( IF( " . $this->getIncompleteWhere() . " ,1,0 ) ) AS incomplete, ( IF( c.category_id <> 0,ccat.name, IF( COUNT(v.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' ) ) ) ) ) ) ) as m_customer_category, IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) \|\| (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , '1','2' ) as m_is_active FROM sales s LEFT OUTER JOIN vehicle v ON s.vehicle_id = v.id LEFT OUTER JOIN service se ON v.id = se.vehicle_id LEFT OUTER JOIN customer c ON v.customer_record_id = c.id LEFT OUTER JOIN customer_contact cc ON c.id = cc.customer_id LEFT OUTER JOIN all_gender ag ON c.gender_id = ag.id LEFT OUTER JOIN customer_affiliations_list cal ON c.id = cal.customer_id LEFT OUTER JOIN customer_affiliations_content cac ON cal.affiliations_content_id = cac.id LEFT JOIN ( SELECT DISTINCT cl.customer_id FROM customer_ltms cl INNER JOIN vehicle v ON cl.customer_id = v.customer_record_id INNER JOIN sales s ON v.id = s.vehicle_id WHERE cl.is_active = 1 AND cl.is_valid = 1 AND s.activity_date >= '2024-10-01' ) cl ON cl.customer_id = c.id LEFT OUTER JOIN source_brand sb ON v.`brand_id` = sb.id LEFT OUTER JOIN source_car_model scm ON v.`model_id` = scm.id LEFT OUTER JOIN source_car_model_variant scmv ON v.model_variant_description = scmv.id LEFT OUTER JOIN source_company_dealer scd ON v.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 source_mode_of_sale smos ON s.mode_of_sale_id = smos.id LEFT OUTER JOIN customer c2 ON c.corporation_id = c2.id LEFT OUTER JOIN source_nature_of_business snob ON c.nature_of_business_id = snob.id LEFT OUTER JOIN source_type_of_payment stp ON s.type_of_payment_id = stp.id LEFT OUTER JOIN source_insurance_company sic ON s.insurance_company_id = sic.id LEFT OUTER JOIN source_of_inquiry soi ON s.source_id = soi.id LEFT OUTER JOIN sales_brands_offered sbo ON s.id = sbo.sales_id LEFT OUTER JOIN source_brand sbsbo ON sbo.`brand_id` = sbsbo.id LEFT OUTER JOIN source_car_model scmsbo ON sbo.`model_id` = scmsbo.id LEFT OUTER JOIN vehicle_color vc ON v.color_id = vc.id LEFT OUTER JOIN source_profession sp ON c.profession_id = sp.id LEFT OUTER JOIN sales_consultant sct ON s.sales_person = sct.id LEFT OUTER JOIN sales_consultant sct2 ON s.group_manager_id = sct2.id LEFT OUTER JOIN sales_commission_status scss ON scss.id = ( SELECT MAX(scsss.id) AS maxsign FROM sales_commission_status scsss LEFT OUTER JOIN sales ss ON scsss.sales_id = ss.id WHERE ss.id = s.id GROUP BY scsss.sales_id ) LEFT OUTER JOIN sales_commission_status scss_hold ON scss_hold.id = ( SELECT MAX(scsss.id) AS maxsign FROM sales_commission_status scsss LEFT OUTER JOIN sales ss ON scsss.sales_id = ss.id WHERE ss.id = s.id AND scsss.commission_status = 3 GROUP BY scsss.sales_id ) LEFT OUTER JOIN sales_complete_status scs_com ON scs_com.id = ( SELECT MAX(scsss.id) AS maxsign FROM sales_complete_status scsss LEFT OUTER JOIN sales ss ON scsss.sales_id = ss.id WHERE ss.id = s.id AND scsss.complete_status >= 1 GROUP BY scsss.sales_id ) LEFT OUTER JOIN employee e_hold ON scss_hold.employee_id = e_hold.id LEFT OUTER JOIN sales_complete_status scs_inc ON scs_inc.id = ( SELECT MAX(scsss.id) AS maxsign FROM sales_complete_status scsss LEFT OUTER JOIN sales ss ON scsss.sales_id = ss.id WHERE ss.id = s.id AND scsss.complete_status <= 0 GROUP BY scsss.sales_id ) LEFT OUTER JOIN sales_commission_status scss_release ON scss_release.id = ( SELECT MAX(scsss.id) AS maxsign FROM sales_commission_status scsss LEFT OUTER JOIN sales ss ON scsss.sales_id = ss.id WHERE ss.id = s.id AND scsss.commission_status = 2 GROUP BY scsss.sales_id ) LEFT OUTER JOIN employee e_release ON scss_release.employee_id = e_release.id LEFT OUTER JOIN sales_commission_status scss_for_release ON scss_for_release.id = ( SELECT MAX(scsss.id) AS maxsign FROM sales_commission_status scsss LEFT OUTER JOIN sales ss ON scsss.sales_id = ss.id WHERE ss.id = s.id AND scsss.commission_status = 1 GROUP BY scsss.sales_id ) LEFT OUTER JOIN employee e_for_release ON scss_for_release.employee_id = e_for_release.id LEFT OUTER JOIN source_commission_status scs ON scss.commission_status = scs.id /LEFT OUTER JOIN source_customer_type sctt ON c.type = sctt.id / LEFT OUTER JOIN vehicle_images vii ON vii.id = ( SELECT MAX(viis.id) AS maxsign FROM vehicle_images viis LEFT OUTER JOIN vehicle vs ON viis.vehicle_id = vs.id WHERE vs.id = v.id AND viis.status = 1 GROUP BY viis.vehicle_id ) LEFT OUTER JOIN customer_category ccat ON c.category_id = ccat.id LEFT OUTER JOIN sales_commission_accounting_status scas ON scas.id = ( SELECT MAX(scass.id) AS maxsign FROM sales_commission_accounting_status scass LEFT OUTER JOIN sales ss ON scass.sales_id = ss.id WHERE ss.id = s.id GROUP BY scass.sales_id ) LEFT OUTER JOIN source_commission_accounting_status source_cas ON scas.commission_accounting_status = source_cas.id LEFT OUTER JOIN sales_future_vehicle_status sfvs ON sfvs.id = ( SELECT MAX(sfvss.id) AS maxsign FROM sales_future_vehicle_status sfvss LEFT OUTER JOIN sales ss ON sfvss.sales_id = ss.id WHERE ss.id = s.id GROUP BY sfvss.sales_id ) LEFT OUTER JOIN source_future_vehicle_status source_sfvss ON sfvs.status_id = source_sfvss.id LEFT OUTER JOIN future_vehicle_assigned_sc fvas ON fvas.id = ( SELECT fvass.id FROM `future_vehicle_assigned_sc` fvass WHERE 1 AND fvass.sales_id = s.id AND fvass.status = 1 LIMIT 1 ) LEFT OUTER JOIN future_vehicle_tag_db fvtd ON fvtd.id = ( SELECT fvtds.id FROM `future_vehicle_tag_db` fvtds INNER JOIN future_vehicle_assigned_sc fvacss ON fvtds.sales_id = fvacss.sales_id AND fvtds.assigned_sc = fvacss.asa_id WHERE 1 AND fvtds.sales_id = s.id AND fvtds.status = 1 AND fvacss.status <> 0 LIMIT 1 ) WHERE 1 "; } function getRequiredFields() { //format -> code - label $data = array( 'brand_name' => 'Brand', 'release_date' => 'Release Date', 'mode_of_sale' => 'Mode of Sale', 'customer_name' => 'Name', // 'end_user'=>'End User', 'profession' => 'Profession', 'position' => 'Position', 'customer_company' => 'Company/Business', 'nature_of_business' => 'Nature of Business', 'customer_affiliation' => 'Affiliations', 'type_of_payment' => 'Type of Payment', 'insurance' => 'Insurance', // 'sales_consultant'=>'Sales Consultant', // 'sales_manager'=>'Sales Manager', 'source_of_sale' => 'Source of Sale', 'brand_offered' => 'Brand Offered', 'address_1' => 'Address', 'email_1' => 'Email', 'mobile_phone_1' => 'Mobile', // 'facebook'=>'Facebook', 'date_of_birth' => 'Date of Birth', 'gender' => 'Gender', 'conduction_sticker' => 'Conduction Sticker', 'vehicle_color' => 'Color', 'model_name' => 'Model', 'model_year' => 'Model Year', 'model_variant' => 'Model Variant', ); return $data; } function whereByTypeComplete($type) { switch ($type) { case 'brand_name': return " AND (sb.id IS NOT NULL AND sb.id <> 0) "; break; case 'release_date': return " AND (s.activity_date IS NOT NULL AND s.activity_date <> '') "; break; case 'mode_of_sale': return " AND (smos.id IS NOT NULL AND smos.id <> 0) "; break; case 'nature_of_business': return " AND (snob.id IS NOT NULL AND snob.id <> 0) "; break; case 'type_of_payment': return " AND (stp.id IS NOT NULL AND stp.id <> 0) "; break; case 'source_of_sale': return " AND (soi.id IS NOT NULL AND soi.id <> 0) "; break; case 'brand_offered': return " AND (sbo.id IS NOT NULL AND sbo.id <> 0) "; break; case 'conduction_sticker': return " AND (v.conduction_sticker IS NOT NULL AND v.conduction_sticker <> '') "; break; case 'model_variant': return " AND (scmv.id IS NOT NULL AND scmv.id <> 0) "; break; case 'insurance': return " AND (sic.id IS NOT NULL AND sic.id <> 0) "; break; case 'email_1': return " AND (cc.email_1 IS NOT NULL AND cc.email_1 <> '') "; break; case 'mobile_phone_1': return " AND ((cc.mobile_phone_1 IS NOT NULL AND cc.mobile_phone_1 <> '') OR c.type = 2) "; break; case 'sales_consultant': return " "; break; case 'sales_manager': return " "; break; case 'address_1': return " AND (cc.address_1 IS NOT NULL AND cc.address_1 <> '') "; break; case 'vehicle_color': return " AND (vc.id IS NOT NULL AND vc.id <> 0) "; break; case 'model_name': return " AND (scm.id IS NOT NULL AND scm.id <> 0) "; break; case 'model_year': return " AND (v.vehicle_release_year IS NOT NULL AND v.vehicle_release_year <> '') "; break; case 'facebook': return " AND ((cc.facebook IS NOT NULL AND cc.facebook <> '') AND c.type = 1 OR (c.type = 2)) "; break; case 'customer_name': return " AND (c.id IS NOT NULL AND c.id <> 0) "; break; case 'customer_company': return " AND ((c2.id IS NOT NULL AND c2.id <> 0) AND c.type = 1 OR (c.type = 2)) "; break; case 'customer_affiliation': return " AND ((cal.id IS NOT NULL AND cal.id <> 0) AND c.type = 1 OR (c.type = 2)) "; break; case 'profession': return " AND ((sp.id IS NOT NULL AND sp.id <> 0) AND c.type = 1 OR (c.type = 2)) "; // return " AND ( // IF(c.type = 1,(sp.id IS NOT NULL AND sp.id <> 0),1) // ) "; break; case 'position': return " AND ((c.occupation IS NOT NULL AND c.occupation <> '') AND c.type = 1 OR (c.type = 2)) "; // return " AND ( // IF(c.type = 1,(c.occupation IS NOT NULL AND c.occupation <> ''),1) // ) "; break; case 'gender': return " AND ((ag.id IS NOT NULL AND ag.id <> 0) AND c.type = 1 OR (c.type = 2)) "; // return " AND ( // IF(c.type = 1,(ag.id IS NOT NULL AND ag.id <> 0),1) // ) "; break; case 'date_of_birth': return " AND ((c.date_of_birth IS NOT NULL AND c.date_of_birth <> '') AND c.type = 1 OR (c.type = 2)) "; // return " AND ( // IF(c.type = 1,(c.date_of_birth IS NOT NULL AND c.date_of_birth <> ''),1) // ) "; break; default: return ''; } } function whereByType($type) { switch ($type) { case 'brand_name': return " AND (sb.id IS NULL OR sb.id = 0) "; break; case 'release_date': return " AND (s.activity_date IS NULL OR s.activity_date = '') "; break; case 'mode_of_sale': return " AND (smos.id IS NULL OR smos.id = 0) "; break; case 'nature_of_business': return " AND (snob.id IS NULL OR snob.id = 0) "; break; case 'type_of_payment': return " AND (stp.id IS NULL OR stp.id = 0) "; break; case 'source_of_sale': return " AND (soi.id IS NULL OR soi.id = 0) "; break; case 'brand_offered': return " AND (sbo.id IS NULL OR sbo.id = 0) "; break; case 'conduction_sticker': return " AND (v.conduction_sticker IS NULL OR v.conduction_sticker = '') "; break; case 'model_variant': return " AND (scmv.id IS NULL OR scmv.id = 0) "; break; case 'insurance': return " AND (sic.id IS NULL OR sic.id = 0) "; break; case 'email_1': return " AND (cc.email_1 IS NULL OR cc.email_1 = '') "; break; case 'mobile_phone_1': return " AND ((cc.mobile_phone_1 IS NULL OR cc.mobile_phone_1 = '') AND c.type <> 2) "; break; case 'sales_consultant': return " "; break; case 'sales_manager': return " "; break; case 'address_1': return " AND (cc.address_1 IS NULL OR cc.address_1 = '') "; break; case 'vehicle_color': return " AND (vc.id IS NULL OR vc.id = 0) "; break; case 'model_name': return " AND (scm.id IS NULL OR scm.id = 0) "; break; case 'model_year': return " AND (v.vehicle_release_year IS NULL OR v.vehicle_release_year = '') "; break; case 'facebook': return " AND ((cc.facebook IS NULL OR cc.facebook = '') AND c.type = 1) "; break; case 'customer_name': return " AND (c.id IS NULL OR c.id = 0) "; break; case 'customer_company': return " AND ((c2.id IS NULL OR c2.id = 0) AND c.type = 1) "; break; case 'customer_affiliation': return " AND ((cal.id IS NULL OR cal.id = 0) AND c.type = 1) "; break; case 'profession': return " AND ((sp.id IS NULL OR sp.id = 0) AND c.type = 1) "; break; case 'position': return " AND ((c.occupation IS NULL OR c.occupation = '') AND c.type = 1) "; break; case 'gender': return " AND ((ag.id IS NULL OR ag.id = 0) AND c.type = 1) "; break; case 'date_of_birth': return " AND ((c.date_of_birth IS NULL OR c.date_of_birth = '') AND c.type = 1) "; break; default: return ''; } } function getCompleteWhere() { return " ((ag.id IS NOT NULL AND ag.id <> 0) AND c.type = 1 OR (c.type = 2)) AND ((c.date_of_birth IS NOT NULL AND c.date_of_birth <> '') AND c.type = 1 OR (c.type = 2)) AND ((sp.id IS NOT NULL AND sp.id <> 0) AND c.type = 1 OR (c.type = 2)) AND ((c.occupation IS NOT NULL AND c.occupation <> '') AND c.type = 1 OR (c.type = 2)) AND ((cal.id IS NOT NULL AND cal.id <> 0) AND c.type = 1 OR (c.type = 2)) AND ((c2.id IS NOT NULL AND c2.id <> 0) AND c.type = 1 OR (c.type = 2)) AND /((cc.facebook IS NOT NULL AND cc.facebook <> '') AND c.type = 1 OR (c.type = 2)) AND/ (sb.id IS NOT NULL AND sb.id <> 0) AND (s.activity_date IS NOT NULL AND s.activity_date <> '') AND (smos.id IS NOT NULL AND smos.id <> 0) AND /(s.end_user IS NULL OR s.end_user = '') AND / (snob.id IS NOT NULL AND snob.id <> 0) AND (stp.id IS NOT NULL AND stp.id <> 0) AND (sic.id IS NOT NULL AND sic.id <> 0) AND /(sct.id IS NOT NULL AND sct.id <> 0) AND (sct2.id IS NOT NULL AND sct2.id <> 0) AND/ (soi.id IS NOT NULL AND soi.id <> 0) AND (sbo.id IS NOT NULL AND sbo.id <> 0) AND (cc.address_1 IS NOT NULL AND cc.address_1 <> '') AND (cc.email_1 IS NOT NULL AND cc.email_1 <> '') AND ((cc.mobile_phone_1 IS NOT NULL AND cc.mobile_phone_1 <> '') OR c.type = 2) AND (v.conduction_sticker IS NOT NULL AND v.conduction_sticker <> '') AND (vc.id IS NOT NULL AND vc.id <> 0) AND (scm.id IS NOT NULL AND scm.id <> 0) AND (v.vehicle_release_year IS NOT NULL AND v.vehicle_release_year <> '') AND (scmv.id IS NOT NULL AND scmv.id <> 0) /AND (c.id IS NOT NULL AND c.id <> 0)/ AND (cl.customer_id IS NULL AND DATE(s.activity_date)<'2024-10-01' OR cl.customer_id IS NOT NULL AND DATE(s.activity_date)<'2024-10-01' OR (DATE(s.activity_date)>'2024-09-01' AND cl.customer_id IS NOT NULL)) "; } function getIncompleteWhere() { return " ((ag.id IS NULL OR ag.id = 0) AND c.type = 1) OR ((c.date_of_birth IS NULL OR c.date_of_birth = '') AND c.type = 1) OR ((sp.id IS NULL OR sp.id = 0) AND c.type = 1) OR ((c.occupation IS NULL OR c.occupation = '') AND c.type = 1) OR ((cal.id IS NULL OR cal.id = 0) AND c.type = 1) OR ((c2.id IS NULL OR c2.id = 0) AND c.type = 1) OR /((cc.facebook IS NULL OR cc.facebook = '') AND c.type = 1) OR/ (sb.id IS NULL OR sb.id = 0) OR (s.activity_date IS NULL OR s.activity_date = '') OR (smos.id IS NULL OR smos.id = 0) OR /(s.end_user IS NULL OR s.end_user = '') OR/ (snob.id IS NULL OR snob.id = 0) OR (stp.id IS NULL OR stp.id = 0) OR (sic.id IS NULL OR sic.id = 0) OR /(sct.id IS NULL OR sct.id = 0) OR (sct2.id IS NULL OR sct2.id = 0) OR/ (soi.id IS NULL OR soi.id = 0) OR (sbo.id IS NULL OR sbo.id = 0) OR (cc.address_1 IS NULL OR cc.address_1 = '') OR (cc.email_1 IS NULL OR cc.email_1 = '') OR ((cc.mobile_phone_1 IS NULL OR cc.mobile_phone_1 = '') AND c.type <> 2) OR (v.conduction_sticker IS NULL OR v.conduction_sticker = '') OR (vc.id IS NULL OR vc.id = 0) OR (scm.id IS NULL OR scm.id = 0) OR (v.vehicle_release_year IS NULL OR v.vehicle_release_year = '') OR (scmv.id IS NULL OR scmv.id = 0) /OR (c.id IS NULL OR c.id = 0)/ OR(cl.customer_id IS NULL AND DATE(s.activity_date)>='2024-10-01') "; } function getComplianceQuery() { //deprecated return "SELECT %s FROM sales s LEFT OUTER JOIN vehicle v ON s.vehicle_id = v.id LEFT OUTER JOIN customer c ON v.customer_record_id = c.id LEFT OUTER JOIN customer_contact cc ON c.id = cc.customer_id LEFT OUTER JOIN all_gender ag ON c.gender_id = ag.id LEFT OUTER JOIN customer_affiliations_list cal ON c.id = cal.customer_id LEFT OUTER JOIN customer_affiliations_content cac ON cal.affiliations_content_id = cac.id LEFT OUTER JOIN source_brand sb ON v.`brand_id` = sb.id LEFT OUTER JOIN source_car_model scm ON v.`model_id` = scm.id LEFT OUTER JOIN source_car_model_variant scmv ON v.model_variant_description = scmv.id LEFT OUTER JOIN source_company_dealer scd ON v.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 source_mode_of_sale smos ON s.mode_of_sale_id = smos.id LEFT OUTER JOIN customer c2 ON c.corporation_id = c2.id LEFT OUTER JOIN source_nature_of_business snob ON c.nature_of_business_id = snob.id LEFT OUTER JOIN source_type_of_payment stp ON s.type_of_payment_id = stp.id LEFT OUTER JOIN source_insurance_company sic ON s.insurance_company_id = sic.id LEFT OUTER JOIN source_of_inquiry soi ON s.source_id = soi.id LEFT OUTER JOIN sales_brands_offered sbo ON s.id = sbo.sales_id LEFT OUTER JOIN vehicle_color vc ON v.color_id = vc.id LEFT OUTER JOIN source_profession sp ON c.profession_id = sp.id LEFT OUTER JOIN sales_consultant sct ON s.sales_person = sct.id LEFT OUTER JOIN sales_consultant sct2 ON s.group_manager_id = sct2.id "; } function getIncompleteWhereOld() { //deprecated return " AND ( (cal.id IS NULL OR cal.id = 0) OR (cc.address_1 IS NULL OR cc.address_1 = '') OR (cc.email_1 IS NULL OR cc.email_1 = '') OR (cc.mobile_phone_1 IS NULL OR cc.mobile_phone_1 = '') /OR (c2.id IS NULL OR c2.id = 0)/ OR (snob.id IS NULL OR snob.id = 0) OR (v.conduction_sticker IS NULL OR v.conduction_sticker = '') OR (sb.id IS NULL OR sb.id = 0) OR (scm.id IS NULL OR scm.id = 0) OR (scmv.id IS NULL OR scmv.id = 0) OR (v.vehicle_release_year IS NULL OR v.vehicle_release_year = '') OR (vc.id IS NULL OR vc.id = 0) /OR (s.end_user IS NULL OR s.end_user = '')/ OR (s.activity_date IS NULL OR s.activity_date = '') OR (smos.id IS NULL OR smos.id = 0) OR (stp.id IS NULL OR stp.id = 0) OR (sic.id IS NULL OR sic.id = 0) OR (soi.id IS NULL OR soi.id = 0) OR (sbo.id IS NULL OR sbo.id = 0) OR (cc.facebook IS NULL OR cc.facebook = '') /OR (c.id IS NULL OR c.id = 0)/ OR ((sp.id IS NULL OR sp.id = 0) AND c.type = 1) OR ((c.occupation IS NULL OR c.occupation = '') AND c.type = 1) OR ((ag.id IS NULL OR ag.id = 0) AND c.type = 1) OR ((c.date_of_birth IS NULL OR c.date_of_birth = '') AND c.type = 1) ) "; } function getCompleteWhereOld() { //deprecated return " AND ( (cal.id IS NOT NULL AND cal.id <> 0) AND (cc.address_1 IS NOT NULL AND cc.address_1 <> '') AND (cc.email_1 IS NOT NULL AND cc.email_1 <> '') AND (cc.mobile_phone_1 IS NOT NULL AND cc.mobile_phone_1 <> '') /AND (c2.id IS NOT NULL AND c2.id <> 0)/ AND (snob.id IS NOT NULL AND snob.id <> 0) AND (v.conduction_sticker IS NOT NULL AND v.conduction_sticker <> '') AND (sb.id IS NOT NULL AND sb.id <> 0) AND (scm.id IS NOT NULL AND scm.id <> 0) AND (scmv.id IS NOT NULL AND scmv.id <> 0) AND (v.vehicle_release_year IS NOT NULL AND v.vehicle_release_year <> '') AND (vc.id IS NOT NULL AND vc.id <> 0) /AND (s.end_user IS NOT NULL AND s.end_user <> '')/ AND (s.activity_date IS NOT NULL AND s.activity_date <> '') AND (smos.id IS NOT NULL AND smos.id <> 0) AND (stp.id IS NOT NULL AND stp.id <> 0) AND (sic.id IS NOT NULL AND sic.id <> 0) AND (soi.id IS NOT NULL AND soi.id <> 0) AND (sbo.id IS NOT NULL AND sbo.id <> 0) AND (cc.facebook IS NOT NULL AND cc.facebook <> '') /AND (c.id IS NOT NULL AND c.id <> 0)/ AND ( IF(c.type = 1,(sp.id IS NOT NULL AND sp.id <> 0),1) ) AND ( IF(c.type = 1,(c.occupation IS NOT NULL AND c.occupation <> ''),1) ) AND ( IF(c.type = 1,(ag.id IS NOT NULL AND ag.id <> 0),1) ) AND ( IF(c.type = 1,(c.date_of_birth IS NOT NULL AND c.date_of_birth <> ''),1) ) ) "; } function getCompleteFieldCount() { //deprecated return " SUM(IF(((ag.id IS NOT NULL AND ag.id <> 0) AND c.type = 1) , 1, IF((c.type = 2),1,0))) AS gender, SUM(IF(((c.date_of_birth IS NOT NULL AND c.date_of_birth <> '') AND c.type = 1), 1, IF((c.type = 2),1,0))) AS date_of_birth, SUM(IF(((sp.id IS NOT NULL AND sp.id <> 0) AND c.type = 1), 1, IF((c.type = 2),1,0)) ) AS profession, SUM(IF(((c.occupation IS NOT NULL AND c.occupation <> '') AND c.type = 1), 1, IF((c.type = 2),1,0))) AS position, SUM(IF((cc.facebook IS NOT NULL AND cc.facebook <> ''), 1, 0)) AS facebook, SUM(IF(((c2.id IS NOT NULL AND c2.id <> 0) AND c.type = 1), 1, 0)) AS customer_company, SUM(IF((sb.id IS NOT NULL AND sb.id <> 0), 1, 0)) AS brand_name, SUM(IF((s.activity_date IS NOT NULL AND s.activity_date <> ''), 1, 0)) AS release_date, SUM(IF((smos.id IS NOT NULL AND smos.id <> 0), 1, 0)) AS mode_of_sale, SUM(IF((s.end_user IS NULL OR s.end_user = ''), 1, 0)) AS end_user, SUM(IF((snob.id IS NOT NULL AND snob.id <> 0), 1, 0)) AS nature_of_business, SUM(IF(cal.id IS NOT NULL AND cal.id <> 0, 1, 0)) AS customer_affiliation, SUM(IF((stp.id IS NOT NULL AND stp.id <> 0), 1, 0)) AS type_of_payment, SUM(IF((sic.id IS NOT NULL AND sic.id <> 0), 1, 0)) AS insurance, SUM(IF((sct.id IS NOT NULL AND sct.id <> 0), 1, 0)) AS sales_consultant, SUM(IF((sct2.id IS NOT NULL AND sct2.id <> 0), 1, 0)) AS sales_manager, SUM(IF((soi.id IS NOT NULL AND soi.id <> 0), 1, 0)) AS source_of_sale, SUM(IF((sbo.id IS NOT NULL AND sbo.id <> 0), 1, 0)) AS brand_offered, SUM(IF((cc.address_1 IS NOT NULL AND cc.address_1 <> ''), 1, 0)) AS address_1, SUM(IF((cc.email_1 IS NOT NULL AND cc.email_1 <> '') , 1, 0)) AS email_1, SUM(IF((cc.mobile_phone_1 IS NOT NULL AND cc.mobile_phone_1 <> ''), 1, 0)) AS mobile_phone_1, SUM(IF((v.conduction_sticker IS NOT NULL AND v.conduction_sticker <> ''), 1, 0)) AS conduction_sticker, SUM(IF((vc.id IS NOT NULL AND vc.id <> 0) , 1, 0)) AS vehicle_color, SUM(IF((scm.id IS NOT NULL AND scm.id <> 0), 1, 0)) AS model_name, SUM(IF((v.vehicle_release_year IS NOT NULL AND v.vehicle_release_year <> ''), 1, 0)) AS model_year, SUM(IF((scmv.id IS NOT NULL AND scmv.id <> 0), 1, 0)) AS model_variant, SUM(IF((c.id IS NOT NULL AND c.id <> 0), 1, 0)) AS customer_name, c.type "; } function getIncompleteFieldCount() { //deprecated return " SUM(IF(((ag.id IS NULL OR ag.id = 0) AND c.type = 1) , 1, 0)) AS incomplete_gender, SUM(IF(((c.date_of_birth IS NULL OR c.date_of_birth = '') AND c.type = 1), 1, 0)) AS incomplete_date_of_birth, SUM(IF((((sp.id IS NULL OR sp.id = 0) AND c.type = 1) AND c.type = 1), 1, 0)) AS incomplete_profession, SUM(IF(((c.occupation IS NULL OR c.occupation = '') AND c.type = 1), 1, 0)) AS incomplete_position, SUM(IF((cc.facebook IS NULL OR cc.facebook = ''), 1, 0)) AS incomplete_facebook, SUM(IF(((c2.id IS NULL OR c2.id = 0) AND c.type = 1), 1, 0)) AS incomplete_customer_company, SUM(IF((sb.id IS NULL OR sb.id = 0), 1, 0)) AS incomplete_brand_name, SUM(IF((s.activity_date IS NULL OR s.activity_date = ''), 1, 0)) AS incomplete_release_date, SUM(IF((smos.id IS NULL OR smos.id = 0), 1, 0)) AS incomplete_mode_of_sale, SUM(IF((s.end_user IS NULL OR s.end_user = ''), 1, 0)) AS incomplete_end_user, SUM(IF((snob.id IS NULL OR snob.id = 0), 1, 0)) AS incomplete_nature_of_business, SUM(IF(cal.id IS NULL OR cal.id = 0, 1, 0)) AS incomplete_customer_affiliation, SUM(IF((stp.id IS NULL OR stp.id = 0), 1, 0)) AS incomplete_type_of_payment, SUM(IF((sic.id IS NULL OR sic.id = 0), 1, 0)) AS incomplete_insurance, SUM(IF((sct.id IS NULL OR sct.id = 0), 1, 0)) AS incomplete_sales_consultant, SUM(IF((sct2.id IS NULL OR sct2.id = 0), 1, 0)) AS incomplete_sales_manager, SUM(IF((soi.id IS NULL OR soi.id = 0), 1, 0)) AS incomplete_source_of_sale, SUM(IF((sbo.id IS NULL OR sbo.id = 0), 1, 0)) AS incomplete_brand_offered, SUM(IF((cc.address_1 IS NULL OR cc.address_1 = ''), 1, 0)) AS incomplete_address_1, SUM(IF((cc.email_1 IS NULL OR cc.email_1 = '') , 1, 0)) AS incomplete_email_1, SUM(IF((cc.mobile_phone_1 IS NULL OR cc.mobile_phone_1 = ''), 1, 0)) AS incomplete_mobile_phone_1, SUM(IF((v.conduction_sticker IS NULL OR v.conduction_sticker = ''), 1, 0)) AS incomplete_conduction_sticker, SUM(IF((vc.id IS NULL OR vc.id = 0) , 1, 0)) AS incomplete_vehicle_color, SUM(IF((scm.id IS NULL OR scm.id = 0), 1, 0)) AS incomplete_model_name, SUM(IF((v.vehicle_release_year IS NULL OR v.vehicle_release_year = ''), 1, 0)) AS incomplete_model_year, SUM(IF((scmv.id IS NULL OR scmv.id = 0), 1, 0)) AS incomplete_model_variant, SUM(IF((c.id IS NULL OR c.id = 0), 1, 0)) AS incomplete_customer_name, /(IF((s.activity_date >= '2024-09-01' AND cl.customer_id IS NULL), 1,0)) AS incomplete_ltms , */ c.type "; } }

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