File manager - Edit - /home/autoph/public_html/projects/Rating-AutoHub/public/css/reports.zip
Back
PK ���Z;˘��I �I report_script.phpnu �[��� <!-- jQuery --> <script src="plugins/jquery/jquery.min.js"></script> <!-- overlayScrollbars --> <script src="plugins/overlayScrollbars/js/jquery.overlayScrollbars.min.js"></script> <!-- Bootstrap 4 --> <script src="plugins/bootstrap/js/bootstrap.bundle.min.js"></script> <!-- Select2 --> <script src="plugins/select2/js/select2.full.min.js"></script> <!-- Bootstrap4 Duallistbox --> <script src="plugins/bootstrap4-duallistbox/jquery.bootstrap-duallistbox.min.js"></script> <!-- InputMask --> <script src="plugins/moment/moment.min.js"></script> <script src="plugins/inputmask/jquery.inputmask.min.js"></script> <!-- date-range-picker --> <script src="plugins/daterangepicker/daterangepicker.js"></script> <!-- bootstrap color picker --> <script src="plugins/bootstrap-colorpicker/js/bootstrap-colorpicker.min.js"></script> <!-- Tempusdominus Bootstrap 4 --> <script src="plugins/tempusdominus-bootstrap-4/js/tempusdominus-bootstrap-4.min.js"></script> <!-- Bootstrap Switch --> <script src="plugins/bootstrap-switch/js/bootstrap-switch.min.js"></script> <!-- BS-Stepper --> <script src="plugins/bs-stepper/js/bs-stepper.min.js"></script> <!-- dropzonejs --> <script src="plugins/dropzone/min/dropzone.min.js"></script> <!-- AdminLTE App --> <script src="dist/js/adminlte.min.js"></script> <!-- Navigation Highlight --> <script src="dist/js/main.js"></script> <!-- Page specific script --> <script> $(document).ready(function() { //console.log($("#state").val()); // var brand = $('#brand option:selected').text() // var brand = this.value; // alert(brand) // $.ajax({ // url:"app/misc/get_asa_car_model.php", // method:"POST", // data: { // brand: brand // }, // cache:false, // beforeSend:function(){ // $('#city').attr('disabled', true); // }, // success:function(data) // { // $("#city").html(data); // $('#city').attr('disabled', false); // } // }) }); </script> <script> // $(document).ready(function() { // $('#brand').on('change', function(event) { // event.preventDefault(); // var brand = this.value; // // alert(brand) // $.ajax({ // url: "app/misc/get_asa_car_model.php", // method: "POST", // data: { // brand: brand // }, // cache: false, // beforeSend: function() { // $('#model').attr('disabled', true); // }, // success: function(data) { // $("#model").html('<option value="0" selected>All</option>' + data); // $('#model').attr('disabled', false); // } // }) // }); // }); </script> <script> $(document).ready(function() { $('#dms').select2(); $('#company').select2(); $('#dealer').select2(); // $('#brand').select2(); $('#dms').on('change', function(event) { $("#company").val(0).trigger('change'); }); $('#company').on('change', function(event) { $("#dealer").val(0).trigger('change'); }); $('#dms').on('select2:select', function(e) { // var dms_id = $('#company').select2({ "language": { "noResults": function() { return 'Enter company name or code'; } }, escapeMarkup: function(markup) { return markup; }, placeholder: 'Search company name or code', ajax: { url: 'app/misc/get_company.php', dataType: 'json', delay: 250, data: function(data) { return { dms_id:$("#dms").val(), searchTerm: data.term, // search term type: 1 }; }, processResults: function(response) { response.unshift({ id: "0", text: "All" }); // alert(JSON.stringify(response)); return { results: response }; }, cache: true } }); // $("#company").html('<option value="0" selected>All</option>'); }); $('#company').on('select2:select', function(e) { // var company_id = $("#company").val(); $('#dealer').select2({ "language": { "noResults": function() { return 'Enter dealer name or code'; } }, escapeMarkup: function(markup) { return markup; }, placeholder: 'Search dealer name or code', ajax: { url: 'app/misc/get_dealer_customer.php', dataType: 'json', delay: 250, data: function(data) { return { searchTerm: data.term, // search term type: 1, company_id: $("#company").val() }; }, processResults: function(response) { response.unshift({ id: "0", text: "All" }); return { results: response }; }, cache: true } }); // $("#dealer").html('<option value="0" selected>All</option>'); }); $('#brand').select2() $('#model').select2() $('#brand').on('change', function(event) { $("#model").val(0).trigger('change'); }); $('#brand').on('select2:select', function(e) { $("#model").select2({ placeholder: "Select Model", "language": { "noResults": function() { return 'Select brand first' } }, escapeMarkup: function(markup) { return markup; }, ajax: { url: 'api/model/model.php', dataType: 'json', delay: 250, data: function(data) { return { brand_id: $("#brand").val(), apiKey: '8732194955663346', search: data.term // search term }; }, processResults: function(response) { response.unshift({ id: "0", text: "All" }); return { results: response }; }, cache: true } }); }); $('#state').select2() $('#city').select2() }) </script> <script> $('#upload-by').on('select2:select', function(e) { // Do something var upload_by = $(".upload-by option:selected").val(); // alert(upload_by) $('#uploaded-by').val(upload_by); }); $("#form-data").submit(function(event) { //do validatons event.preventDefault(); remove_alert_bg(); remove_form_invalid(); // var upload_by = $(".upload-by option:selected").text(); var reg_date_from = $.trim($('#reg-from').val()); var reg_date_to = $.trim($('#reg-to').val()); var upload_date_from = $.trim($('#upload-from').val()); var upload_date_to = $.trim($('#upload-to').val()); var age_from = $.trim($('#age-from').val()); var age_to = $.trim($('#age-to').val()); var report_description = $.trim($('#report-description').val()); var chk_customer = ($('#chk-customer').is(":checked")) ? 1 : 0; var chk_vehicle = ($('#chk-vehicle').is(":checked")) ? 1 : 0; var chk_service = ($('#chk-service').is(":checked")) ? 1 : 0; var chk_sales = ($('#chk-sales').is(":checked")) ? 1 : 0; if (!chk_customer && !chk_vehicle && !chk_service && !chk_sales) { requiredReport(); return; } // alert(chk_customer) // return; // if(contactno == ''){ // $('#error-alert').fadeOut(); // $('#contactno').addClass("is-invalid"); // $("#contactno").focus(); // return; // } // if(){ // } // if( // reg_date_from != '' && reg_date_to != '' // // (reg_date_from == '' && reg_date_to == '') // // || // // (!reg_date_from == '' && !reg_date_to == '') // ){ // }else{ // requiredRegDate(); // return; // } if ( (age_from == '' && age_to == '') || (!age_from == '' && !age_to == '') ) { //proceed } else { invalidAgeRange(); return; } if ( (upload_date_from == '' && upload_date_to == '') || (!upload_date_from == '' && !upload_date_to == '') ) { } else { invalidUploadDate(); return; } if ( (new Date(reg_date_from) > new Date(reg_date_to)) || (new Date(reg_date_from) > new Date(reg_date_to)) ) { invalidRegDate(); return; } if (parseInt(age_from) > parseInt(age_to)) { invalidAgeRange(); return; } if (new Date(upload_date_from) > new Date(upload_date_to)) { invalidUploadDate(); return; } if (report_description == '') { emptyReportDescription(); $('#report-description').focus(); return; } $('#error-icon').addClass("fa-info"); $('#error-alert').addClass("alert-info"); $('#error-alert').fadeIn(); $('#alert-message').text(" Requesting, please wait..."); // return; $.ajax({ // url: "app/report/insert_report.php", data: $('#form-data').serialize(), type: "GET", dataType: 'json', url: "app/report/insert_report.php", data: new FormData(this), type: "POST", dataType: 'json', contentType: false, cache: false, processData: false, beforeSend: function() { $('#submit').attr('disabled', true); }, success: function(result) { console.log(result.status + " " + result.message); if (result.status != 1) { remove_alert_bg(); $('#error-icon').addClass("fa-ban"); $('#error-alert').addClass("alert-danger"); $('#alert-message').text(result.message); // setTimeout(function () { $('#error-alert').fadeOut()}, 3000); } else { //success remove_alert_bg(); $('#error-icon').addClass("fa-check"); $('#error-alert').addClass("alert-success"); $('#alert-message').text(result.message); setTimeout(function() { location.href = 'requested_reports.php'; }, 2000); } // $('#submit').attr('disabled', false); $('#submit').hide(); }, error: handleError }); }); var reg_date_from = $.trim($('#reg-from').val()); var reg_date_to = $.trim($('#reg-to').val()); var upload_date_from = $.trim($('#upload-from').val()); var upload_date_to = $.trim($('#upload-to').val()); var age_from = $.trim($('#age-from').val()); var age_to = $.trim($('#age-to').val()); function invalidAgeRange() { $('#age-from').addClass("is-invalid"); $('#age-to').addClass("is-invalid"); $('#error-icon').addClass("fa-ban"); $('#error-alert').addClass("alert-danger"); $('#error-alert').fadeIn(); $('#alert-message').text(" Invalid Age Range."); } function emptyReportDescription() { $('#report-description').addClass("is-invalid"); $('#error-icon').addClass("fa-ban"); $('#error-alert').addClass("alert-danger"); $('#error-alert').fadeIn(); $('#alert-message').text(" Enter the report description."); } function invalidRegDate() { $('#reg-from').addClass("is-invalid"); $('#reg-to').addClass("is-invalid"); $('#error-icon').addClass("fa-ban"); $('#error-alert').addClass("alert-danger"); $('#error-alert').fadeIn(); $('#alert-message').text(" Invalid Registration Date Range."); } function requiredReport() { $('#error-icon').addClass("fa-ban"); $('#error-alert').addClass("alert-danger"); $('#error-alert').fadeIn(); $('#alert-message').text(" Select report type."); } function invalidUploadDate() { $('#upload-from').addClass("is-invalid"); $('#upload-to').addClass("is-invalid"); $('#error-icon').addClass("fa-ban"); $('#error-alert').addClass("alert-danger"); $('#error-alert').fadeIn(); $('#alert-message').text(" Invalid Upload Date Range."); } function handleError(xhr, status, error) { //Handle failure here remove_alert_bg(); $('#error-icon').addClass("fa-ban"); $('#error-alert').addClass("alert-danger"); $('#alert-message').text("Something went wrong, try again."); $('#submit').attr('disabled', false); } function remove_alert_bg() { $('#error-alert').removeClass("alert-success"); $('#error-alert').removeClass("alert-danger"); $('#error-alert').removeClass("alert-info"); $('#error-icon').removeClass("fa-check"); $('#error-icon').removeClass("fa-info"); $('#error-icon').removeClass("fa-ban"); } function remove_form_invalid() { $('#report-description').removeClass("is-invalid"); $('#reg-from').removeClass("is-invalid"); $('#reg-to').removeClass("is-invalid"); $('#upload-from').removeClass("is-invalid"); $('#upload-to').removeClass("is-invalid"); $('#age-from').removeClass("is-invalid"); $('#age-to').removeClass("is-invalid"); // $('#contactno').removeClass("is-invalid"); // $('#contactno2').removeClass("is-invalid"); // $('#email').removeClass("is-invalid"); // $('#email2').removeClass("is-invalid"); // $('#address').removeClass("is-invalid"); // $('#state_div').css("border", ""); // $('#city_div').css("border", ""); // $('#company_div').css("border", ""); // $('#dealer_div').css("border", ""); // $('#department_div').css("border", ""); // $('#position_div').css("border", ""); // $('#username').removeClass("is-invalid"); // $('#password').removeClass("is-invalid"); // $('#agree-terms-div').css("border", ""); } </script> <script type="text/javascript"> $('.upload-by').select2({ "language": { "noResults": function() { return 'Enter employee name or ID'; } }, escapeMarkup: function(markup) { return markup; }, placeholder: 'Search employee name or ID', ajax: { url: 'app/misc/get_employee.php', dataType: 'json', delay: 250, data: function(data) { return { searchTerm: data.term // search term }; }, processResults: function(response) { return { results: response }; }, cache: true } }); function clear() { $('#upload-by').val(null).trigger('change'); } </script> <script> $(function() { // $('.select2').select2() //Initialize Select2 Elements $('.select2bs4').select2({ theme: 'bootstrap4' }) //Date range picker $('#reg-from-date').datetimepicker({ format: 'L' }); //Date range picker $('#upload-from-date').datetimepicker({ format: 'L' }); $('#upload-to-date').datetimepicker({ format: 'L' }); $('#date-of-birth-div').datetimepicker({ format: 'L' }); //Date range picker $('#reg-to-date').datetimepicker({ format: 'L' }); //Datemask2 mm/dd/yyyy $('#datemask2').inputmask('mm/dd/yyyy', { 'placeholder': 'mm/dd/yyyy' }) //Money Euro $('[data-mask]').inputmask() }) </script> <!-- GET City List --> <script> $(document).ready(function() { $("#rdo-asc").prop("checked", true); $("#rdo-dl-no").prop("checked", true); //console.log($("#state").val()); var state_id = $("#state").val(); $.ajax({ url: "app/misc/get_city.php", method: "POST", data: { state_id: state_id }, cache: false, beforeSend: function() { $('#city').attr('disabled', true); }, success: function(data) { $("#city").html('<option selected>All</option>' + data); $('#city').attr('disabled', false); } }) }); </script> <script> $(document).ready(function() { $('#state').on('change', function(event) { event.preventDefault(); var state_id = this.value; $.ajax({ url: "app/misc/get_city.php", method: "POST", data: { state_id: state_id }, cache: false, beforeSend: function() { $('#city').attr('disabled', true); }, success: function(data) { $("#city").html('<option selected>All</option>' + data); $('#city').attr('disabled', false); } }) }); }); </script> <!-- <script> $(document).ready(function() { var company_id = $("#company").val(); $.ajax({ url:"app/misc/get_dealer.php", method:"POST", data: { company_id: company_id,type:0 }, cache:false, beforeSend:function(){ $('#dealer').attr('disabled', true); }, success:function(data) { $("#dealer").html('<option selected>All</option>'+data); $('#dealer').attr('disabled', false); } }) }); </script> <script> $(document).ready(function(){ $('#company').on('change', function(event){ event.preventDefault(); var company_id = this.value; $.ajax({ url:"app/misc/get_dealer.php", method:"POST", data: { company_id: company_id,type:0 }, cache:false, beforeSend:function(){ $('#dealer').attr('disabled', true); }, success:function(data) { $("#dealer").html('<option selected>All</option>'+data); $('#dealer').attr('disabled', false); } }) }); }); </script> --> <script> $(function() { $('.select6').select2() $('.select7').select2() $('.select8').select2() }) </script> <script> $(document).ready(function() { var start = "01/01/" + (new Date()).getFullYear(); var now = new Date(); // $('#reg-from').val(start); // $('#reg-to').val(now); }); </script> <script> $(document).on("wheel", "input[type=number]", function(e) { $(this).blur(); }); </script>PK ���ZU[ �zj zj generate_report.phpnu �[��� <!-- BODY --> <input type="hidden" name="report-type" id="report-type" value="<?php echo $report_type; ?>" readonly /> <label class="dropdown mx-0 my-0">Sort and Order</label> <div class="col-sm-6 "> <div class="row mb-1 border rounded pb-2 pt-2 "> <div class="col-sm-6 "> <div class="form-group my-0 mx-0 "> <div class="form-group dropdown-input mb-1"> <select name="sort-by" id="sort-by" class="form-control form-control-sm report-field " style="width: 100%;"> <option value="1" selected>Lastname (Individual)</option> <option value="2">Firstname (Individual)</option> <option value="3">Name (Corporation)</option> <option value="4">Date Created</option> <option value="5">Date Uploaded</option> </select> </div> <!--form-group dropdown--> </div> <!--form-group--> </div> <!--col-sm-3--> <div class="col-sm-3 mt-1 "> <!-- text input --> <div class="form-group my-0 mx-0 "> <div class="custom-control custom-radio ml-0"> <input class="custom-control-input " type="radio" id="rdo-asc" value="ASC" name="order"> <label for="rdo-asc" class="custom-control-label">Ascending</label> </div> </div> <!--form-group--> </div> <!--col-sm-3--> <div class="col-sm-3 mt-1"> <!-- text input --> <div class="form-group my-0 mx-0"> <div class="custom-control custom-radio ml-0"> <input class="custom-control-input" type="radio" id="rdo-desc" value="DESC" name="order"> <label for="rdo-desc" class="custom-control-label">Descending</label> </div> </div> <!--form-group--> </div> <!--col-sm-3--> </div> <!-- row --> </div> <!-- cols --> <label class="dropdown mx-0 my-0">Report Manager</label> <div class="col-sm-6 mb-3" > <div class="row mb-1 border rounded pb-2 pt-2"> <div class="col-sm-6 mt-1 "> <!-- text input --> <div class="form-group my-0 mx-0 "> <div class="custom-control custom-radio ml-0"> <input class="custom-control-input " type="radio" id="rdo-dl-yes" value="1" name="downloadable"> <label for="rdo-dl-yes" class="custom-control-label">Downloadable</label> </div> </div> <!--form-group--> </div> <!--col-sm-3--> <div class="col-sm-6 mt-1"> <!-- text input --> <div class="form-group my-0 mx-0"> <div class="custom-control custom-radio ml-0"> <input class="custom-control-input" type="radio" id="rdo-dl-no" value="0" name="downloadable"> <label for="rdo-dl-no" class="custom-control-label">Read-only</label> </div> </div> <!--form-group--> </div> <!--col-sm-3--> </div> <!-- row --> </div> <div class="row"> <div class="col-sm-3 "> <div class="form-group my-0 mx-0 "> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Date Created From<span class="required-symbol" style="color:red;" hidden>*</span></label> <div class="input-group date" id="reg-from-date" data-target-input="nearest"> <input autocomplete="off" name="reg-from" id="reg-from" data-inputmask-alias="datetime" data-inputmask-inputformat="mm/dd/yyyy" data-mask type="text" class="form-control form-control-sm report-field datetimepicker-input" data-target="#reg-from-date" /> <div class="input-group-append" data-target="#reg-from-date" data-toggle="datetimepicker"> <div class="input-group-text"><i class="fa fa-calendar"></i></div> </div> </div> </div> </div> </div> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Date Created To<span class="required-symbol" style="color:red;" hidden>*</span></label> <div class="input-group date" id="reg-to-date" data-target-input="nearest"> <input name="reg-to" id="reg-to" autocomplete="off" data-inputmask-alias="datetime" data-inputmask-inputformat="mm/dd/yyyy" data-mask type="text" class="form-control form-control-sm report-field datetimepicker-input" data-target="#reg-to-date" /> <div class="input-group-append" data-target="#reg-to-date" data-toggle="datetimepicker"> <div class="input-group-text"><i class="fa fa-calendar"></i></div> </div> </div> </div> </div> </div> </div> <!--row--> <div class="row"> <div class="col-sm-3"> <!-- text input --> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">DMS</label> <select name="dms" id="dms" class="form-control form-control-sm report-field " style="width: 100%;"> <option value="0" selected>All</option> <?php $company = $db -> return_result("SELECT sd.`id`, sd.`name` FROM `source_dms` sd INNER JOIN source_company sc ON sd.id = sc.dms_id WHERE sd.status = 1 AND sc.id IN (".$granted_company_ids.") GROUP BY sd.id ORDER BY sd.name ASC "); foreach($company as $data){ echo "<option value='{$data['id']}' >".$data['name']."</option>"; } ?> </select> </div> <!--form-group dropdown--> </div> <!--form-group--> </div> <!--col-sm-3--> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Company</label> <select name="company" id="company" class="form-control form-control-sm report-field select4" style="width: 100%;"> <option value="0" selected> All</option> <?php $company = $db -> return_result("SELECT `id`, `code`,`name` FROM `source_company` WHERE status = 1 AND id IN (".$granted_company_ids.") ORDER BY code ASC"); foreach($company as $data){ echo "<option value='{$data['id']}' >".strtoupper($data['code'].' - '.$data['name'])."</option>"; } ?> </select> </div> </div> </div> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Dealer</label> <select name="dealer" id="dealer" class="form-control form-control-sm report-field select5" style="width: 100%;"> <option value="0" selected> All</option> <!-- <?php $company = $db -> return_result("SELECT sc.id,sc.name FROM `source_dealer` sc INNER JOIN source_company_dealer scd ON sc.id = scd.dealer_id WHERE type = 1 AND sc.status = 1 GROUP BY sc.id ORDER BY sc.name ASC"); foreach($company as $data){ echo "<option value='{$data['id']}'>".$data['name']."</option>"; } ?> --> </select> </div> </div> </div> </div> <div class="row"> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Customer Type</label> <select name="type" id="type" class="form-control form-control-sm report-field " style="width: 100%;"> <option value="0" selected>All</option> <option value="1">Individual</option> <option value="2">Corporation</option> </select> </div> </div> </div> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Customer Category</label> <select name="category" id="category" class="form-control form-control-sm report-field " style="width: 100%;"> <option value="0" selected>All</option> <?php $categories = $db -> return_result("SELECT * FROM `customer_category` WHERE 1 AND id != 0 ORDER BY id ASC"); //print_r($clients); foreach($categories as $category){ echo "<option value='{$category['id']}'>".$category['name']."</option>"; } ?> <!-- <option value="SVO">SVO</option> <option value="SAO">SAO</option> <option value="LYL">LYL</option> <option value="SLY">SLY</option> <option value="VIP">VIP</option> <option value="VVIP">VVIP</option> --> <!-- <option value="VVIP" >VVIP</option> --> </select> </div> </div> </div> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Customer Status</label> <select name="active-status" id="active-status" class="form-control form-control-sm report-field " style="width: 100%;"> <option value="0" selected>All</option> <option value="1">Active</option> <option value="2">Inactive</option> </select> </div> </div> </div> <div class="col-sm-3" <?php echo ($report_type == 1 || $report_type == 2 || $report_type == 3 || $report_type == 4) ? "" : "style=display:;" ;?> id="filter-data-area"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Customer Data</label> <select name="filter-data" id="filter-data" class="form-control form-control-sm report-field " style="width: 100%;"> <option value="0" selected> Disable</option> <?php $filter_data_menu = array("Complete Data","Incomplete Data","No Address","No Gender","No Date of birth"); foreach($filter_data_menu as $menu){ echo "<option value='$menu'>$menu</option>"; } ?> </select> </div> </div> </div> </div> <div class="row"> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Gender</label> <select name="gender" id="gender" class="form-control form-control-sm report-field " style="width: 100%;"> <option value="0" selected>All</option> <?php // $gender = $db -> return_result("SELECT `id`, `gender_name` FROM `all_gender` WHERE `status` = 1 ORDER by `gender_name` DESC"); //print_r($clients); $gender = $db -> return_result("SELECT `id`, `gender_name` FROM `all_gender` WHERE `status` = 1 ORDER by `gender_name` DESC"); //print_r($clients); foreach($gender as $data){ echo "<option value='{$data['id']}'>".$data['gender_name']."</option>"; } ?> </select> </div> <!--form-group dropdown--> </div> <!--form-group--> </div> <!--col-sm-3--> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Date of Birth</label> <div class="input-group date" id="date-of-birth-div" data-target-input="nearest"> <input autocomplete="off" name="date-of-birth" id="date-of-birth" data-inputmask-alias="datetime" data-inputmask-inputformat="mm/dd/yyyy" data-mask type="text" class="form-control form-control-sm report-field datetimepicker-input" data-target="#date-of-birth-div" /> <div class="input-group-append" data-target="#date-of-birth-div" data-toggle="datetimepicker"> <div class="input-group-text"><i class="fa fa-calendar"></i></div> </div> </div> </div> </div> </div> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Age from</label> <input type="number" name="age-from" id="age-from" class="form-control form-control-sm report-field" placeholder="" maxlength="15"> </div> </div> </div> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Age to</label> <input type="number" name="age-to" id="age-to" class="form-control form-control-sm report-field" placeholder="" maxlength="15"> </div> </div> </div> </div> <!--row--> <div class="row"> <div class="col-sm-3" <?php echo ($report_type == 2 || $report_type == 3 || $report_type == 4) ? "" : "style=display:;" ;?> id="brand-area"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Brand</label> <select name="brand" id="brand" class="form-control form-control-sm report-field select3" style="width: 100%;"> <option value="0" selected> All</option> <?php $categories = $db -> return_result("SELECT id,name FROM `source_brand` WHERE 1 AND status = 1 ORDER BY name ASC"); //print_r($clients); foreach($categories as $category){ echo "<option value='{$category['id']}'>".$category['name']."</option>"; } ?> <?php // $token_key = '1024241010244'; // $data = get_content('http://www.autohub.ph/connect/LOAD_BRANDS.php?requestKey='.$token_key.''); // // if($data === FALSE) { // // $return_arr["status"]=0; // // $return_arr["message"]=" Error has occurred. Try again."; // // echo json_encode($return_arr); // // exit; // // } // // if((substr($data, 0, 1) === '[') && substr($data, strlen($data) -1 , strlen($data)) === ']'){ // // $data = substr($data, 1, -1); // // } // // echo $data;exit; // $decodedData = json_decode($data,true); // // echo $decodedData[0]['b_name'];exit; // // print_r($decodedData); // // // $company = $db -> return_result("SELECT `id`, `name` FROM `source_brand` WHERE `status` = 1 ORDER BY name ASC"); // foreach($decodedData as $data){ // echo "<option value='{$data['b_name']}'>".$data['b_name']."</option>"; // } // function get_content($URL){ // $ch = curl_init(); // curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // curl_setopt($ch, CURLOPT_URL, $URL); // $data = curl_exec($ch); // curl_close($ch); // return $data; // } ?> </select> </div> </div> </div> <div class="col-sm-3" <?php echo ($report_type == 2 || $report_type == 3 || $report_type == 4) ? "" : "style=display:;" ;?> id="model-area"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Model</label> <select name="model" id="model" class="form-control form-control-sm report-field select3" style="width: 100%;"> <option value="0" selected> All</option> <?php // $company = $db -> return_result("SELECT `id`, `name` FROM `source_car_model` WHERE `status` = 1 ORDER BY name ASC"); // foreach($company as $data){ // echo "<option value='{$data['id']}'>".$data['name']."</option>"; // } ?> </select> </div> </div> </div> </div> <div class="row"> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Insurance Company</label> <select name="insurance-company" id="insurance-company" class="form-control form-control-sm report-field select6" style="width: 100%;"> <option value="0" selected> All</option> <?php $insurance_company = $db -> return_result("SELECT id,name FROM `source_insurance_company` WHERE id <> 0 AND status = 1 GROUP BY id ORDER BY name ASC "); foreach($insurance_company as $data){ echo "<option value='{$data['id']}'>".$data['name']."</option>"; } ?> </select> </div> </div> </div> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Insurance Type</label> <select name="insurance-type" id="insurance-type" class="form-control form-control-sm report-field select7" style="width: 100%;"> <option value="0" selected> All</option> <?php $insurance_company = $db -> return_result("SELECT id,name FROM `source_insurance_type` WHERE id <> 0 AND status = 1 GROUP BY id ORDER BY name ASC "); foreach($insurance_company as $data){ echo "<option value='{$data['id']}'>".$data['name']."</option>"; } ?> </select> </div> </div> </div> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Financing Company</label> <select name="finance-company" id="finance-company" class="form-control form-control-sm report-field select8" style="width: 100%;"> <option value="0" selected> All</option> <?php $insurance_company = $db -> return_result("SELECT id,name FROM `source_finance_company` WHERE id <> 0 AND status = 1 GROUP BY id ORDER BY name ASC "); foreach($insurance_company as $data){ echo "<option value='{$data['id']}'>".$data['name']."</option>"; } ?> </select> </div> </div> </div> </div> <div class="row"> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">State</label> <select name="state" id="state" class="form-control form-control-sm report-field select3" style="width: 100%;"> <option value="0" selected>All</option> <?php $state = $db -> return_result("SELECT `provCode`,`provDesc` FROM `all_state` WHERE id != '0' ORDER by provDesc ASC"); //print_r($clients); foreach($state as $data){ echo "<option value='{$data['provCode']}'>".$data['provDesc']."</option>"; } ?> </select> </div> </div> </div> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">City</label> <select name="city" id="city" class="form-control form-control-sm report-field select3" style="width: 100%;"> <option value="0" selected>All</option> </select> </div> </div> </div> <!-- </div> /.row <div class="row"> --> </div> <!--row--> <div class="row"> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Date Uploaded From</label> <div class="input-group date" id="upload-from-date" data-target-input="nearest"> <input autocomplete="off" name="upload-from" id="upload-from" data-inputmask-alias="datetime" data-inputmask-inputformat="mm/dd/yyyy" data-mask type="text" class="form-control form-control-sm report-field datetimepicker-input" data-target="#upload-from-date" /> <div class="input-group-append" data-target="#upload-from-date" data-toggle="datetimepicker"> <div class="input-group-text"><i class="fa fa-calendar"></i></div> </div> </div> </div> </div> </div> <div class="col-sm-3"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Date Uploaded To</label> <div class="input-group date" id="upload-to-date" data-target-input="nearest"> <input name="upload-to" id="upload-to" autocomplete="off" data-inputmask-alias="datetime" data-inputmask-inputformat="mm/dd/yyyy" data-mask type="text" class="form-control form-control-sm report-field datetimepicker-input" data-target="#upload-to-date" /> <div class="input-group-append" data-target="#upload-to-date" data-toggle="datetimepicker"> <div class="input-group-text"><i class="fa fa-calendar"></i></div> </div> </div> </div> </div> </div> </div> <div class="row"> <div class="col-sm-6"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Uploaded By</label> <select name="upload-by" id="upload-by" class="form-control form-control-sm report-field upload-by" style="width: 100%;"></select> </div> </div> </div> </div> <input type="hidden" name="uploaded-by" id="uploaded-by" value="" readonly /> <div class="row"> <div class="col"> <div class="form-group my-0 mx-0"> <div class="form-group dropdown-input mb-1"> <label class="dropdown mx-0 my-0">Report Description</label> <textarea row="2" class="form-control form-control-sm report-field" rows=2 placeholder="" id="report-description" name="report-description"></textarea> </div> </div> </div> </div> <!-- <div class="col-12 mt-3"> <input type="submit" name="submit" id="submit" value="Request Report" class="btn btn-success float-right"> </div> -->PK ���ZҰ�qc) c) report_data.phpnu �[��� <?php $return_array = array(); $report_session_data = array(); if(isset($_REQUEST['report-id'])){ //report id $report_session_data['report_id'] = $db -> escape($_REQUEST['report-id']); $current_user = $_SESSION['user']['id']; $check_report = $db -> select("SELECT ( IF( (SELECT DATEDIFF(r.expire,NOW()) < 0),'3', IF( (SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 2) = 0 , IF( (SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 0) = 0 , '1', '0' ), '2' ) ) ) as status FROM `report`r INNER JOIN report_type rt ON r.report_type_id = rt.id WHERE r.id = '".$report_session_data['report_id']."' AND r.employee_id = '$current_user'"); if(!isNotEmpty($check_report) || intval($check_report) <> 1){ header('location:requested_reports.php'); return; } $fmt = new NumberFormatter('PHP', NumberFormatter::CURRENCY); //START GET LIST OF APPROVER $report_session_data['approver_list'] = array(); $list_query = $db -> sql_query("SELECT e.employee_id,concat(e.first_name,' ',e.last_name) as name, rs.`status`,DATE_FORMAT(date,'%M %d, %Y - %h:%i %p') as date, rs.`notes` FROM `report_status` rs INNER JOIN employee e ON rs.employee_id = e.id WHERE rs.report_id = '".$report_session_data['report_id']."' ORDER BY rs.id ASC"); while($row = $list_query->fetch_assoc()) { // $approver_table = $approver_table . "<tr><th class="."data mr-3".">".$row["name"]."  -  </th> <th class="."data"." >".$row["date"]."</th></tr>"; $report_session_data['approver_list'][] = array($row["employee_id"],$row["name"],$row["date"],$row["notes"] ); } //END GET LIST OF APPROVER $report_data = $db -> sql_query("SELECT r.`id`, r.`control_number`, CONCAT(e.first_name,' ',e.middle_name,' ',e.last_name) as request_by, CONCAT(DATE_FORMAT(r.`report_date`, '%M %d, %Y'), ' - ' ,TIME_FORMAT(r.`report_time`, '%h:%i %p')) as request_date_time, rt.name as report_type, r.report_type_id, r.`dms_id`, IF( r.`dms_id` = '0','All', (SELECT name FROM source_dms WHERE id = r.dms_id) ) as dms_name, r.`company_id`, IF( r.`company_id` = '0','All', (SELECT code FROM source_company WHERE id = r.company_id) ) as company_name, r.`dealer_id`, IF( r.`dealer_id` = '0','All', (SELECT code FROM source_dealer WHERE id = r.dealer_id) ) as dealer_name, r.`customer_type` as customer_type_id, IF( `customer_type` = '0','All', IF( `customer_type` = '1', 'Individual', 'Corporation' ) ) as customer_type, r.`customer_category` as customer_category_id, IF( `customer_category` = '0','All', (SELECT name FROM customer_category WHERE id = r.customer_category) ) as customer_category, r.`customer_status` as customer_status_id, IF( `customer_status` = '0','All', IF( `customer_status` = '1', 'Active', 'Inactive' ) ) as customer_status, r.`activity_date_from`,r.`activity_date_to`, IF( r.`activity_date_from` <> '' AND r.`activity_date_to` <> '' ,CONCAT(r.`activity_date_from`, ' - ',r.`activity_date_to`), '' ) as activity_between, r.`gender_id`, IF( r.`gender_id` = '0','All', (SELECT gender_name FROM all_gender WHERE id = r.gender_id) ) as gender_name , r.`age_from`,r.`age_to`, IF( r.`age_from` <> '' AND r.`age_to` <> '' ,CONCAT(r.`age_from`, ' - ',r.`age_to`), '' ) as age_between, r.state_id, IF( r.`state_id` = '0','All', (SELECT `provDesc` FROM `all_state` WHERE `provCode` = r.`state_id` LIMIT 1 ) ) as state_name , r.city_id, IF( r.`city_id` = '0','All', (SELECT `citymunDesc` FROM `all_city` WHERE `id` = r.`city_id`) ) as city_name, r.`uploaded_date_from`,r.`uploaded_date_to`, IF( r.`uploaded_date_from` <> '' AND r.`uploaded_date_to` <> '' ,CONCAT(r.`uploaded_date_from`, ' - ',r.`uploaded_date_to`), '' ) as upload_between, r.`upload_by_employee_id`, IF( r.`upload_by_employee_id` = '','', (SELECT CONCAT(first_name,' ',middle_name,' ',last_name) FROM `employee` WHERE `id` = r.`upload_by_employee_id`) ) as upload_by , r.`customer_data_type`, IF( r.`customer_data_type` = '0','', r.`customer_data_type` ) as data_type, r.description, r.`brand_id`, IF( r.`brand_id` = '0','All', (SELECT name FROM source_brand WHERE id = r.brand_id) ) as brand_name, r.`model_id`, IF( r.`model_id` = '0','All', (SELECT name FROM source_car_model WHERE id = r.model_id) ) as model_name, r.sort_by,r.order_by,r.date_of_birth, r.insurance_company_id, r.insurance_type_id, r.finance_company_id, IF( r.`insurance_company_id` = '0','All', (SELECT name FROM source_insurance_company WHERE id = r.insurance_company_id) ) as insurance_company, IF( r.`insurance_type_id` = '0','All', (SELECT name FROM source_insurance_type WHERE id = r.insurance_type_id) ) as insurance_type, IF( r.`finance_company_id` = '0','All', (SELECT name FROM source_finance_company WHERE id = r.finance_company_id) ) as finance_company, r.record_count, r.company_list FROM `report` r INNER JOIN employee e ON r.employee_id=e.id INNER JOIN report_type rt ON r.`report_type_id`=rt.id WHERE r.id = '".$report_session_data['report_id']."' "); foreach($report_data as $data){ //START GET COMMON DATA OF REPORT $report_session_data['control_number'] = isNotEmpty($data['control_number']) ? $data['control_number'] : 'N/A' ; $report_session_data['request_by'] = isNotEmpty($data['request_by']) ? $data['request_by'] : 'N/A' ; $report_session_data['request_date_time'] = isNotEmpty($data['request_date_time']) ? $data['request_date_time'] : 'N/A' ; $report_session_data['report_type'] = isNotEmpty($data['report_type']) ? $data['report_type'] : '<strong>N/A</strong>' ; $report_session_data['report_type_id'] = isNotEmpty($data['report_type_id']) ? $data['report_type_id'] : '<strong>N/A</strong>' ; $report_session_data['report_between'] = isNotEmpty($data['activity_between']) ? $data['activity_between'] : 'N/A' ; $report_session_data['dms_name'] = isNotEmpty($data['dms_name']) ? $data['dms_name'] : 'N/A' ; $report_session_data['company_name'] = isNotEmpty($data['company_name']) ? $data['company_name'] : 'N/A' ; $report_session_data['dealer_name'] = isNotEmpty($data['dealer_name']) ? $data['dealer_name'] : 'N/A' ; $report_session_data['customer_type'] = isNotEmpty($data['customer_type']) ? $data['customer_type'] : 'N/A' ; $report_session_data['customer_status'] = isNotEmpty($data['customer_status']) ? $data['customer_status'] : 'N/A' ; $report_session_data['customer_category'] = isNotEmpty($data['customer_category']) ? $data['customer_category'] : 'N/A' ; $report_session_data['gender_name'] = isNotEmpty($data['gender_name']) ? $data['gender_name'] : 'N/A' ; // $report_session_data['brand_name'] = isNotEmpty($data['brand_name']) ? $data['brand_name'] : 'N/A' ; // $report_session_data['model_name'] = isNotEmpty($data['model_name']) ? $data['model_name'] : 'N/A' ; $report_session_data['brand_name'] = ($data['brand_name'] <> '0' ) ? $data['brand_name'] : 'All' ; $report_session_data['model_name'] = ($data['model_name'] <> '0') ? $data['model_name'] : 'All' ; $report_session_data['age_between'] = isNotEmpty($data['age_between']) ? $data['age_between'] : 'N/A' ; $report_session_data['state_name'] = isNotEmpty($data['state_name']) ? $data['state_name'] : 'N/A' ; $report_session_data['city_name'] = isNotEmpty($data['city_name']) ? $data['city_name'] : 'N/A' ; $report_session_data['upload_between'] = isNotEmpty($data['upload_between']) ? $data['upload_between'] : 'N/A' ; $report_session_data['upload_by'] = isNotEmpty($data['upload_by']) ? $data['upload_by'] : 'N/A' ; $report_session_data['data_type'] = isNotEmpty($data['data_type']) ? $data['data_type'] : 'N/A' ; $report_session_data['date_of_birth'] = isNotEmpty($data['date_of_birth']) ? $data['date_of_birth'] : 'N/A' ; $report_session_data['report_description'] = isNotEmpty($data['description']) ? $data['description'] : 'N/A' ; $report_session_data['insurance_company'] = isNotEmpty($data['insurance_company']) ? $data['insurance_company'] : 'N/A' ; $report_session_data['insurance_type'] = isNotEmpty($data['insurance_type']) ? $data['insurance_type'] : 'N/A' ; $report_session_data['finance_company'] = isNotEmpty($data['finance_company']) ? $data['finance_company'] : 'N/A' ; $report_session_data['record_count'] = isNotEmpty($data['record_count']) ? $data['record_count'] : '0' ; $dms_id = $data['dms_id']; $company_id = $data['company_id']; $dealer_id = $data['dealer_id']; $age_from = $data['age_from']; $age_to = $data['age_to']; $gender_id = $data['gender_id']; $state_id = $data['state_id']; $city_id = $data['city_id']; $brand_id = $data['brand_id']; $model_id = $data['model_id']; $insurance_company_id = $data['insurance_company_id']; $insurance_type_id = $data['insurance_type_id']; $finance_company_id = $data['finance_company_id']; // $brand_id_asa = 0; // $model_id_asa = 0; // if(($data['brand_name']) <> '0'){ // $data_brand_name = $db -> escape($data['brand_name']); // $get_brand_id_asa = $db -> select("SELECT id FROM source_brand WHERE name LIKE '%$data_brand_name%' AND status = 1 "); // $brand_id_asa = (isNotEmpty($get_brand_id_asa)) ? $get_brand_id_asa : -1 ; // } // if(($data['model_name']) <> '0'){ // $data_model_name = $db -> escape($data['model_name']); // $get_model_id_asa = $db -> select("SELECT id FROM source_car_model WHERE name LIKE '%$data_model_name%' OR model_variant_description LIKE '%$data_model_name%' AND status = 1 "); // $model_id_asa = (isNotEmpty($get_model_id_asa)) ? $get_model_id_asa : -1 ; // } // echo $brand_id_asa. ' ' .$model_id_asa; // exit; $upload_by_id = $data['upload_by_employee_id']; $customer_data_type = $data['customer_data_type']; $customer_type = $data['customer_type_id']; $customer_category = $data['customer_category_id']; $customer_status = $data['customer_status_id']; $sort_by = $data['sort_by']; $order_by = $data['order_by']; $company_list = $data['company_list']; //dates format $activity_date_from = fix_date($data['activity_date_from'], false); $activity_date_to = fix_date($data['activity_date_to'],false); $upload_date_from = fix_date($data['uploaded_date_from'], false); $upload_date_to = fix_date($data['uploaded_date_to'],false); $filter_date_of_birth = fix_date($data['date_of_birth'],false); //END GET COMMON DATA OF REPORT $report_session_data['file_name'] = strtr($db -> cipher($report_session_data['control_number'],true) , "+/=", "-_~" ).'.'.'pdf'; // echo $report_session_data['file_name'];exit; if(file_exists('report/pdf/'.$report_session_data['file_name'])){ switch($data['report_type_id']){ //60+40+45+35 case "1": $report_session_data['col_width'] = array(15, 35, 22, 15,20 ,8, 38, 25, 22); break; case "2": $report_session_data['col_width'] = array(15, 40, 25, 28, 20 ,25, 25, 22); break; case "3": $report_session_data['col_width'] = array(15, 20, 37, 20, 23, 15, 20 ,18, 18 , 14); break; case "4": $report_session_data['col_width'] = array(15, 20, 35, 17, 23, 20, 20 ,18, 18 , 14); break; } $report_count_data = $report_session_data['record_count']; }else{ switch($data['report_type_id']){ case "1": $report_session_data['col_width'] = array(15, 35, 22, 15,20 ,8, 38, 25, 22); $report_count_data = getFilteredCustomerData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$report_session_data['control_number'],$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db); break; case "2": $report_session_data['col_width'] = array(15, 40, 25, 28, 20 ,25, 25, 22); $report_count_data = getFilteredVehicleData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$report_session_data['control_number'],$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db); break; case "3": $report_session_data['col_width'] = array(15, 20, 37, 20, 23, 15, 20 ,18, 18 , 14); $report_count_data = getFilteredServiceData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$report_session_data['control_number'],$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db); break; case "4": $report_session_data['col_width'] = array(15, 20, 35, 17, 23, 20, 20 ,18, 18 , 14); $report_count_data = getFilteredSalesData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$report_session_data['control_number'],$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db); break; } } }//end foreach // echo $_SESSION['customer_data']; // $_SESSION['vehicle_data'] = json_encode($customer_data); // $_SESSION['service_data'] = json_encode($customer_data); // $_SESSION['sale_data'] = json_encode($customer_data); $return_array['status'] = 1; $return_array['message'] = "Report Generated"; }else{ $return_array['status'] = 0; $return_array['message'] = "Report Generation Failed"; } // FUNCTIONS HERE function getFilteredCustomerData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$control_number,$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db){ if(isNotEmpty($upload_date_from) && isNotEmpty($upload_date_to)){ $filter_date_upload = " AND DATE_FORMAT(c.date_uploaded, '%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$upload_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$upload_date_to', '%Y-%m-%d 23:59:00') "; }else{ $filter_date_upload = ""; } if(isNotEmpty($activity_date_from) && isNotEmpty($activity_date_to)){ $filter_date_created = " AND DATE_FORMAT(c.date_created, '%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$activity_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$activity_date_to', '%Y-%m-%d 23:59:00') "; }else{ $filter_date_created = ""; } if(isNotEmpty($age_from) && isNotEmpty($age_to)){ $filter_age = " AND TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$age_to' "; }else{ $filter_age = ""; } if(isNotEmpty($upload_by_id)){ $filter_upload_by = " AND c.upload_by = '$upload_by_id' "; }else{ $filter_upload_by = ""; } $company = ($company_id != '0') ? " AND scd.company_id = '$company_id' " : " AND scd.company_id IN (".$company_list.") " ; $dealer = ($dealer_id != '0') ? " AND scd.dealer_id = '$dealer_id' " : "" ; $gender =($gender_id != '0') ? " AND c.gender_id = '".$gender_id."' " : "" ; $dms =($dms_id != '0') ? " AND sc.dms_id = '".$dms_id."' " : "" ; $state = ($state_id != '0') ? " AND cc.address_1_state = '$state_id' " : "" ; $city = ($city_id != '0') ? " AND cc.address_1_city = '$city_id' " : "" ; $customertype = ($customer_type != '0') ? " AND c.type = '$customer_type' " : "" ; $customercategory = ($customer_category != '0') ? " HAVING customer_category = '$customer_category' " : "" ; $customerstatus = ($customer_status != '0') ? " ".(($customer_category != '0') ? " AND " : " HAVING " )." isActive = '$customer_status' " : "" ; //default $brand = ($brand_id != '0') ? " AND v.brand_id = '$brand_id' " : "" ; $model = ($model_id != '0') ? " AND v.model_id = '$model_id' " : "" ; $complete_data = ""; $incomplete_data = ""; $mobile_phone_1 = ""; $email_1 = ""; $address_1 = ""; $date_of_birth =""; if(isNotEmpty($customer_data_type) && $customer_data_type != '0'){ switch($customer_data_type){ case "Complete Data": $complete_data = " AND ((IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') <> '' and ag.id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '') AND c.type = 1) OR (cc.address_1 <> '' AND cc.email_1 <> '' AND c.type = 2) "; break; case "Incomplete Data": $incomplete_data = " AND ((IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') = '' OR ag.id = 0 OR cc.address_1 = '' OR cc.email_1 = '') AND c.type = 1) OR (cc.address_1 = '' or cc.email_1 = '' AND c.type = 2) "; break; case "No Mobile": $mobile_phone_1 = " AND cc.mobile_phone_1 = '' "; break; case "No Email": $email_1 = " AND cc.email_1 = '' "; break; case "No Address": $address_1 = " AND cc.address_1 = '' "; break; case "No Gender": $gender = " AND ag.gender_id = '0' AND c.type = '1' "; break; case "No Date of birth": $date_of_birth = " AND IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') = '' AND c.type = '1' "; break; } } $insurance_company = ($insurance_company_id != '0') ? " AND (s.insurance_company_id = '$insurance_company_id') " : "" ; $insurance_type = ($insurance_type_id != '0') ? " AND (s.insurance_type_id = '$insurance_type_id') " : "" ; $finance_company = ($finance_company_id != '0') ? " AND (s.finance_company_id = '$finance_company_id') " : "" ; if(isNotEmpty($filter_date_of_birth) && isNotEmpty($filter_date_of_birth)){ $filter_dob = " AND c.date_of_birth = '$filter_date_of_birth' "; }else{ $filter_dob = ""; } switch(intval($sort_by)){ case 1: //lastname $filter_sort_by = " c.last_name "; break; case 2: //firstname $filter_sort_by = " c.first_name "; break; case 3: //corporation name $filter_sort_by = " c.corporation_name "; break; case 4: //date created $filter_sort_by = " c.date_created "; break; case 5: //date uploaded $filter_sort_by = " c.date_uploaded "; break; } $return_data = array(); $query = " SELECT %s FROM customer c INNER JOIN customer_category ccat ON c.category_id = ccat.id INNER JOIN customer_contact cc ON c.id = cc.customer_id INNER JOIN employee e ON c.upload_by = e.id INNER JOIN customer_dms cd ON c.id = cd.customer_record_id INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id INNER JOIN all_gender ag ON c.gender_id = ag.id INNER JOIN all_marital_status am ON c.marital_status_id = am.id INNER JOIN all_countries ac ON c.nationality_id = ac.num_code INNER JOIN all_suffix asx ON c.suffix_id = asx.id LEFT OUTER JOIN vehicle v ON c.id = v.customer_record_id LEFT OUTER JOIN sales s ON v.id = s.vehicle_id LEFT OUTER JOIN service se ON v.id = se.vehicle_id WHERE 1 %s /* upload_date */ %s /* date_created */ %s /* dms */ %s /* company */ %s /* dealer */ %s /* age */ %s /* gender */ %s /* state */ %s /* city */ %s /* upload_by */ %s /* complete_data */ %s /* incomplete_id */ %s /* no_address */ %s /* no_dob */ %s /* type */ %s /* dob */ %s /* brand */ %s /* model */ %s /* insurance_company */ %s /* insurance_type */ %s /* finance_company */ AND c.status = 1 GROUP BY c.id %s %s ORDER BY $filter_sort_by $order_by "; // LEFT OUTER JOIN source_brand sbv // ON v.brand_id=sbv.id // LEFT OUTER JOIN source_car_model scmv // ON v.model_id=scmv.id // LEFT OUTER JOIN source_brand sbs // ON s.brand_id=sbs.id // LEFT OUTER JOIN source_car_model scms // ON s.model_id=scms.id // LEFT OUTER JOIN source_brand sbse // ON se.brand_id=sbse.id // LEFT OUTER JOIN source_car_model scmse // ON se.model_id=scmse.id $fields = " c.id, c.photo, IF(c.type = 1, concat(c.first_name,' ',c.middle_name,' ',c.last_name, ' ', IF(c.suffix_id < 1, '',asx.suffix_name )) ,c.corporation_name ) as name, am.name as marital_status, ag.gender_name, c.date_of_birth, 'Individual' as customer_type, c.date_uploaded, TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) as age_number, c.date_created, cc.mobile_phone_1, cc.mobile_phone_2, cc.email_1, cc.email_2, cc.business_phone, cc.fax_phone, cc.residential_phone, cc.address_1, c.contact_person, ac.nationality, c.occupation, c.no_of_children, concat(e.first_name,' ',e.last_name) as upload_by, c.type, ( IF( c.category_id <> 0,ccat.id, IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, '1', IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, '2', IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, '3', IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, '4', IF( COUNT(v.id) > 3, '5','0'/*VVIP*/ ) ) ) ) ) ) ) as customer_category, IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , '1','2' ) as isActive"; $count = " ( IF( c.category_id <> 0,ccat.id, IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, '1', IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, '2', IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, '3', IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, '4', IF( COUNT(v.id) > 3, '5','0'/*VVIP*/ ) ) ) ) ) ) ) as customer_category, IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , '1','2' ) as isActive"; // echo (sprintf($query,$fields,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); exit; //echo sprintf($query,$fields,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus);exit; $data_list = $db -> sql_query(sprintf($query,$fields,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); // echo sprintf($query,$fields,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus);exit; $query_count = $db -> select("SELECT COUNT(1) FROM ( ".sprintf($query,$count,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus) ." ) as total_count"); // echo "SELECT COUNT(1) FROM ( ".sprintf($query,$count,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$customercategory,$customerstatus) ." ) as total_count";exit; $filename = 'report/data/'.strtr($db -> cipher($control_number,true), "+/", "-_" ).'.'.'php'; $fp = fopen($filename, 'w'); $col_header = array('No.','Name','Type','Gender', 'DOB', 'Age','Email','Mobile','Date Created'); $string = implode(';',array_map('replaceExistingSemiColon',$col_header)); fwrite($fp,$string.PHP_EOL); $record_counter = 0; if( $query_count > 0){ $record_counter++; foreach ($data_list as $data){ $involvements = "SELECT sd.name AS dms, sc.code AS company, sdr.code AS dealer FROM `customer_dms` cd INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON sc.id = scd.company_id INNER JOIN source_dms sd ON sc.`dms_id` = sd.id INNER JOIN source_dealer sdr ON sdr.id = scd.dealer_id WHERE `customer_record_id` IN ('".$data['id']."')"; $in = $db->return_result($involvements); $dms = array(); $company = array(); $dealer = array(); foreach($in as $data1){ $dms = array_merge($dms, array($data1['dms'])); $company = array_merge($company, array($data1['company'])); $dealer = array_merge($dealer, array($data1['dealer'])); } $dms_new = array_unique($dms); $company_new = array_unique($company); $dealer_new = array_unique($dealer); $list_dms = implode(', ', $dms_new); $list_company = implode(', ', $company_new); $list_dealer = implode(', ', $dealer_new); $data_to_insert = array(); // $return_data = array_merge($return_data,array(array('No.'=>$record_counter++,'Name'=>$data['name'],'Age'=>$data['age_number'],'Gender'=>$data['gender_name'],'Email'=>$data['email_1'],'Mobile'=>$data['mobile_phone_1'],'Type'=>(intval($data['type']) === 1) ? 'Individual' : 'Corporation' ))); if(intval($data['type']) === 1){ // $return_data = array_merge($return_data, // array(array( // 'No.'=>$record_counter++, // 'Name'=>$data['name'], // 'Type'=>(intval($data['type']) === 1) ? 'Individual' : 'Corporation', // 'Gender'=>$data['gender_name'], // 'DOB'=>$data['date_of_birth'], // 'Age'=>$data['age_number'], // // 'Status'=>$data['marital_status'], // 'Email'=>$data['email_1'], // // 'Email 2'=>$data['email_2'], // 'Mobile'=>$data['mobile_phone_1'], // // 'Mobile 2'=>$data['mobile_phone_2'], // // 'Business Phone'=>$data['business_phone'], // // 'Fax'=>$data['fax_phone'], // // 'Home No.'=>$data['residential_phone'], // // 'Contact Person'=>$data['contact_person'], // // 'Nationality'=>$data['nationality'], // // 'Occupation'=>$data['occupation'], // // 'No. of Children'=>$data['no_of_children'], // 'DMS'=>$list_dms, // 'Company'=>$list_company, // 'Dealer'=>$list_dealer, // 'Date'=>$data['date_created'], // // 'Upload Date'=>$data['date_uploaded'], // // 'Upload By'=>$data['upload_by'], // ))); $data_to_insert = array( $record_counter++, $data['name'], (intval($data['type']) === 1) ? 'Individual' : 'Corporation', $data['gender_name'], $data['date_of_birth'], $data['age_number'], $data['email_1'], $data['mobile_phone_1'], // $list_dms, // $list_company, // $list_dealer, $data['date_created'], ); }else{ // $return_data = array_merge($return_data, // array(array( // 'No.'=>$record_counter++, // 'Name'=>$data['name'], // 'Type'=>(intval($data['type']) === 1) ? 'Individual' : 'Corporation', // 'DOB'=>$data['date_of_birth'], // 'Age'=>$data['age_number'], // 'Email'=>$data['email_1'], // // 'Email 2'=>$data['email_2'], // 'Mobile'=>$data['mobile_phone_1'], // // 'Mobile 2'=>$data['mobile_phone_2'], // // 'Business Phone'=>$data['business_phone'], // // 'Fax'=>$data['fax_phone'], // // 'Home No.'=>$data['residential_phone'], // // 'Contact Person'=>$data['contact_person'], // // 'Nationality'=>$data['nationality'], // 'DMS'=>$list_dms, // 'Company'=>$list_company, // 'Dealer'=>$list_dealer, // 'Date'=>$data['date_created'], // // 'Upload Date'=>$data['date_uploaded'], // // 'Upload By'=>$data['upload_by'], // ))); $data_to_insert = array( $record_counter++, $data['name'], (intval($data['type']) === 1) ? 'Individual' : 'Corporation', '', $data['date_of_birth'], $data['age_number'], $data['email_1'], $data['mobile_phone_1'], // $list_dms, // $list_company, // $list_dealer, $data['date_created'], ); } $string = implode(';',array_map('replaceExistingSemiColon',$data_to_insert)); fwrite($fp,$string.PHP_EOL); } fclose($fp); }else{ // $return_data = array_merge($return_data,array(array('Name'=>'','Age'=>'','Gender'=>'','Email'=>'','Mobile'=>'','Type'=>''))); } $db -> sql_query("UPDATE report SET record_count = '".(max(0,($record_counter-1)))."' WHERE control_number = '$control_number' "); return max(0,($record_counter-1));//array($return_data,array($record_counter)); } function getFilteredVehicleData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$control_number,$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db){ if(isNotEmpty($upload_date_from) && isNotEmpty($upload_date_to)){ $filter_date_upload = " AND DATE_FORMAT(v.date_uploaded, '%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$upload_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$upload_date_to', '%Y-%m-%d 23:59:00') "; }else{ $filter_date_upload = ""; } if(isNotEmpty($activity_date_from) && isNotEmpty($activity_date_to)){ $filter_date_created = " AND DATE_FORMAT(v.date_created, '%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$activity_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$activity_date_to', '%Y-%m-%d 23:59:00') "; }else{ $filter_date_created = ""; } if(isNotEmpty($age_from) && isNotEmpty($age_to)){ $filter_age = " AND TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$age_to' "; }else{ $filter_age = ""; } if(isNotEmpty($upload_by_id)){ $filter_upload_by = " AND v.upload_by = '$upload_by_id' "; }else{ $filter_upload_by = ""; } $dms = ($dms_id != '0') ? " AND sc.dms_id = '$dms_id' " : "" ; $company = ($company_id != '0') ? " AND scd.company_id = '$company_id' " : " AND scd.company_id IN (".$company_list.") " ; $dealer = ($dealer_id != '0') ? " AND scd.dealer_id = '$dealer_id' " : "" ; $state = ($state_id != '0') ? " AND cc.address_1_state = '$state_id' " : "" ; $city = ($city_id != '0') ? " AND cc.address_1_city = '$city_id' " : "" ; $gender = ($gender_id != '0') ? " AND c.gender_id = '$gender_id' " : "" ; $customertype = ($customer_type != '0') ? " AND c.type = '$customer_type' " : "" ; $brand = ($brand_id != '0') ? " AND v.brand_id = '$brand_id' " : "" ; $model = ($model_id != '0') ? " AND v.model_id = '$model_id' " : "" ; $insurance_company = ($insurance_company_id != '0') ? " AND ( s.insurance_company_id = '$insurance_company_id') " : "" ; $insurance_type = ($insurance_type_id != '0') ? " AND ( s.insurance_type_id = '$insurance_type_id') " : "" ; $finance_company = ($finance_company_id != '0') ? " AND ( s.finance_company_id = '$finance_company_id') " : "" ; $complete_data = ""; $incomplete_data = ""; $address_1 = ""; $date_of_birth =""; //disabled function below if(isNotEmpty($customer_data_type) && $customer_data_type != '0'){ switch($customer_data_type){ case "Complete Data": $complete_data = " AND ((IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') <> '' and ag.id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '') AND c.type = 1) OR (cc.address_1 <> '' AND cc.email_1 <> '' AND c.type = 2) "; break; case "Incomplete Data": $incomplete_data = " AND ((IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') = '' OR ag.id = 0 OR cc.address_1 = '' OR cc.email_1 = '') AND c.type = 1) OR (cc.address_1 = '' or cc.email_1 = '' AND c.type = 2) "; break; case "No Mobile": $mobile_phone_1 = " AND cc.mobile_phone_1 = '' "; break; case "No Email": $email_1 = " AND cc.email_1 = '' "; break; case "No Address": $address_1 = " AND cc.address_1 = '' "; break; case "No Gender": $gender = " AND ag.gender_id = '0' AND c.type = '1' "; break; case "No Date of birth": $date_of_birth = " AND IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') = '' AND c.type = '1' "; break; } } if(isNotEmpty($filter_date_of_birth) && isNotEmpty($filter_date_of_birth)){ $filter_dob = " AND c.date_of_birth = '$filter_date_of_birth' "; }else{ $filter_dob = ""; } switch(intval($sort_by)){ case 1: //lastname $filter_sort_by = " c.last_name "; break; case 2: //firstname $filter_sort_by = " c.first_name "; break; case 3: //corporation name $filter_sort_by = " c.corporation_name "; break; case 4: //date created $filter_sort_by = " v.date_created "; break; case 5: //date uploaded $filter_sort_by = " v.date_uploaded "; break; } $customercategory = ($customer_category != '0') ? " HAVING customer_category = '$customer_category' " : "" ; $customerstatus = ($customer_status != '0') ? " ".(($customer_category != '0') ? " AND " : " HAVING " )." isActive = '$customer_status' " : "" ; $return_data = array(); $query = "SELECT %s FROM vehicle v INNER JOIN source_brand sb ON v.brand_id=sb.id INNER JOIN source_car_model scm ON v.model_id=scm.id INNER JOIN vehicle_color vc ON v.color_id=vc.id INNER JOIN source_company_dealer scd ON scd.id=v.company_dealer_id INNER JOIN source_company sc ON sc.id = scd.company_id INNER JOIN source_dealer sd ON sd.id= scd.dealer_id INNER JOIN source_dms sdms ON sc.dms_id = sdms.id INNER JOIN employee e ON v.upload_by = e.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 customer_category ccat ON c.category_id = ccat.id LEFT OUTER JOIN sales s ON v.id = s.vehicle_id LEFT OUTER JOIN service se ON v.id = se.vehicle_id WHERE 1 %s /* brand */ %s /* model */ %s /* customer type */ %s /* upload by */ %s /* state */ %s /* city */ %s /* gender */ %s /* filter age */ %s /* dms type */ %s /* company */ %s /* dealer */ %s /* filter_date_upload */ %s /* filter_date_created */ %s /* filter_dob */ %s /* insurance_company */ %s /* insurance_type */ %s /* finance_company */ AND v.status = 1 GROUP BY v.id %s %s ORDER BY $filter_sort_by $order_by"; $count = " ( IF( c.category_id <> 0,ccat.id, IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, '1', IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, '2', IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, '3', IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, '4', IF( COUNT(v.id) > 3, '5','0'/*VVIP*/ ) ) ) ) ) ) ) as customer_category, IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , '1','2' ) as isActive"; $fields = " v.id, scm.name AS model, v.plate_number, v.conduction_sticker, sb.name AS brand, vc.name AS color, v.customer_id, vc.name as color_name,sc.code as company_name, sd.code as dealer_name,sdms.name as dms_name,v.vehicle_release_year,v.engine_type,v.gas_type,v.transmission,v.chassis_number,v.serial_number,v.vin_number,v.sales_person, scm.model_variant_description,v.date_uploaded,concat(e.first_name,' ',e.last_name) as upload_by, v.date_created, IF( c.type = 1, concat(c.first_name,' ',c.middle_name,' ',c.last_name),c.corporation_name ) as name, ( IF( c.category_id <> 0,ccat.id, IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, '1', IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, '2', IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, '3', IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, '4', IF( COUNT(v.id) > 3, '5','0'/*VVIP*/ ) ) ) ) ) ) ) as customer_category, IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , '1','2' ) as isActive "; // echo sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus);exit; $data_list = $db -> sql_query(sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); $query_count = $db -> select("SELECT COUNT(*) FROM ( ".sprintf($query, $count,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)." ) as total_count"); $filename = 'report/data/'.strtr($db -> cipher($control_number,true), "+/", "-_" ).'.'.'php'; $fp = fopen($filename, 'w'); $col_header = array('No.','Owner','Brand','Model', 'Color', 'CS','Plate','Date Created'); $string = implode(';',array_map('replaceExistingSemiColon',$col_header)); fwrite($fp,$string.PHP_EOL); $record_counter = 0; if( $query_count > 0){ $record_counter++; foreach ($data_list as $data){ // $return_data = array_merge($return_data,array(array('No.'=>$record_counter++,'Brand'=>$data['brand'],'Model'=>$data['model'],'CS Number'=>$data['conduction_sticker'],'Plate Number'=>$data['plate_number'],'Owner'=>$data['name']))); // $return_data = array_merge($return_data,array(array( // 'No.'=>$record_counter++, // 'Owner'=>$data['name'], // 'Brand'=>$data['brand'], // 'Model'=>$data['model'], // // 'Model Desc.'=>$data['model_variant_description'], // 'Color'=>$data['color_name'], // 'CS'=>$data['conduction_sticker'], // 'Plate'=>$data['plate_number'], // // 'Year Model'=>$data['vehicle_release_year'], // // 'Engine Type'=>$data['engine_type'], // // 'Gas Type'=>$data['gas_type'], // // 'Transmission'=>$data['transmission'], // // 'VIN Number'=>$data['vin_number'], // // 'Serial Number'=>$data['serial_number'], // // 'Chassis Number'=>$data['chassis_number'], // // 'Sales Person'=>$data['sales_person'], // // 'DMS'=>$data['dms_name'], // 'Company'=>$data['company_name'], // 'Dealer'=>$data['dealer_name'], // 'Date'=>$data['date_created'], // // 'Upload Date'=>$data['date_uploaded'], // // 'Upload By'=>$data['upload_by'], // ))); $data_to_insert = array( $record_counter++, $data['name'], $data['brand'], $data['model'], $data['color_name'], $data['conduction_sticker'], $data['plate_number'], $data['date_created'], ); $string = implode(';',array_map('replaceExistingSemiColon',$data_to_insert)); fwrite($fp,$string.PHP_EOL); } fclose($fp); }else{ // $return_data = array_merge($return_data,array(array('Brand'=>'','Model'=>'','CS Number'=>'','Plate Number'=>'','Owner'=>''))); } // return array($return_data,array($query_count)); $db -> sql_query("UPDATE report SET record_count = '".(max(0,($record_counter-1)))."' WHERE control_number = '$control_number' "); return max(0,($record_counter-1));//array($return_data,array($record_counter)); } function getFilteredServiceData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$control_number,$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db){ if(isNotEmpty($upload_date_from) && isNotEmpty($upload_date_to)){ $filter_date_upload = " AND DATE_FORMAT(se.date_uploaded, '%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$upload_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$upload_date_to', '%Y-%m-%d 23:59:00') "; }else{ $filter_date_upload = ""; } if(isNotEmpty($activity_date_from) && isNotEmpty($activity_date_to)){ $filter_date_created = " AND DATE_FORMAT(se.start_date_of_service, '%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$activity_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$activity_date_to', '%Y-%m-%d 23:59:00') "; }else{ $filter_date_created = ""; } if(isNotEmpty($age_from) && isNotEmpty($age_to)){ $filter_age = " AND TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$age_to' "; }else{ $filter_age = ""; } if(isNotEmpty($upload_by_id)){ $filter_upload_by = " AND se.upload_by = '$upload_by_id' "; }else{ $filter_upload_by = ""; } $dms = ($dms_id != '0') ? " AND sc.dms_id = '$dms_id' " : "" ; $company = ($company_id != '0') ? " AND scd.company_id = '$company_id' " : " AND scd.company_id IN (".$company_list.") " ; $dealer = ($dealer_id != '0') ? " AND scd.dealer_id = '$dealer_id' " : "" ; $state = ($state_id != '0') ? " AND cc.address_1_state = '$state_id' " : "" ; $city = ($city_id != '0') ? " AND cc.address_1_city = '$city_id' " : "" ; $gender = ($gender_id != '0') ? " AND c.gender_id = '$gender_id' " : "" ; $customertype = ($customer_type != '0') ? " AND c.type = '$customer_type' " : "" ; $brand = ($brand_id != '0') ? " AND sb.id = '$brand_id' " : "" ; $model = ($model_id != '0') ? " AND scm.id = '$model_id' " : "" ; $insurance_company = ($insurance_company_id != '0') ? " AND s.insurance_company_id = '$insurance_company_id' " : "" ; $insurance_type = ($insurance_type_id != '0') ? " AND s.insurance_type_id = '$insurance_type_id' " : "" ; $finance_company = ($finance_company_id != '0') ? " AND s.finance_company_id = '$finance_company_id' " : "" ; $complete_data = ""; $incomplete_data = ""; $address_1 = ""; $date_of_birth =""; //disabled function below if(isNotEmpty($customer_data_type) && $customer_data_type != '0'){ switch($customer_data_type){ case "Complete Data": $complete_data = " AND ((IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') <> '' and ag.id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '') AND c.type = 1) OR (cc.address_1 <> '' AND cc.email_1 <> '' AND c.type = 2) "; break; case "Incomplete Data": $incomplete_data = " AND ((IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') = '' OR ag.id = 0 OR cc.address_1 = '' OR cc.email_1 = '') AND c.type = 1) OR (cc.address_1 = '' or cc.email_1 = '' AND c.type = 2) "; break; case "No Mobile": $mobile_phone_1 = " AND cc.mobile_phone_1 = '' "; break; case "No Email": $email_1 = " AND cc.email_1 = '' "; break; case "No Address": $address_1 = " AND cc.address_1 = '' "; break; case "No Gender": $gender = " AND ag.gender_id = '0' AND c.type = '1' "; break; case "No Date of birth": $date_of_birth = " AND IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') = '' AND c.type = '1' "; break; } } if(isNotEmpty($filter_date_of_birth) && isNotEmpty($filter_date_of_birth)){ $filter_dob = " AND c.date_of_birth = '$filter_date_of_birth' "; }else{ $filter_dob = ""; } switch(intval($sort_by)){ case 1: //lastname $filter_sort_by = " c.last_name "; break; case 2: //firstname $filter_sort_by = " c.first_name "; break; case 3: //corporation name $filter_sort_by = " c.corporation_name "; break; case 4: //date created $filter_sort_by = " se.start_date_of_service "; break; case 5: //date uploaded $filter_sort_by = " se.date_uploaded "; break; } $customercategory = ($customer_category != '0') ? " HAVING customer_category = '$customer_category' " : "" ; $customerstatus = ($customer_status != '0') ? " ".(($customer_category != '0') ? " AND " : " HAVING " )." isActive = '$customer_status' " : "" ; $return_data = array(); $query = "SELECT %s FROM service se INNER JOIN vehicle v ON se.vehicle_id = v.id INNER JOIN sales s ON s.vehicle_id = v.id INNER JOIN source_brand sb ON v.brand_id=sb.id INNER JOIN source_car_model scm ON v.model_id=scm.id INNER JOIN source_company_dealer scd ON scd.id=se.company_dealer_id INNER JOIN source_company sc ON sc.id = scd.company_id INNER JOIN source_dealer sd ON sd.id= scd.dealer_id INNER JOIN employee e ON se.upload_by = e.id INNER JOIN source_dms sdms ON sc.dms_id = sdms.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 customer_category ccat ON c.category_id = ccat.id WHERE 1 %s /* brand */ %s /* model */ %s /* customer type */ %s /* upload by */ %s /* state */ %s /* city */ %s /* gender */ %s /* filter age */ %s /* dms type */ %s /* company */ %s /* dealer */ %s /* filter_date_upload */ %s /* filter_date_created */ %s /* filter_dob*/ %s /* insurance_company */ %s /* insurance_type */ %s /* finance_company */ AND se.status = 1 AND v.status = 1 GROUP BY se.id %s %s ORDER BY $filter_sort_by $order_by"; $count = "( IF( c.category_id <> 0,ccat.id, IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, '1', IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, '2', IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, '3', IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, '4', IF( COUNT(v.id) > 3, '5','0'/*VVIP*/ ) ) ) ) ) ) ) as customer_category, IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , '1','2' ) as isActive"; $fields = " v.brand_id, v.model_id, se.ro_number,se.ro_total, se.plate_number, se.service_advisor, se.id, v.conduction_sticker, concat(e.first_name,' ',e.last_name) as upload_by,se.date_uploaded,sdms.name as dms_name,sd.code dealer_name, sc.code as company_name,sdms.name as dms_name,se.serial_number,se.mileage,se.customer_discount,se.customer_paid_amount, sb.name as brand, scm.name as model, se.start_date_of_service, IF( c.type = 1, concat(c.first_name,' ',c.middle_name,' ',c.last_name),c.corporation_name ) as name, ( IF( c.category_id <> 0,ccat.id, IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, '1', IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, '2', IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, '3', IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, '4', IF( COUNT(v.id) > 3, '5','0'/*VVIP*/ ) ) ) ) ) ) ) as customer_category, IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , '1','2' ) as isActive"; // echo (sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); exit; // echo (sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus));exit; $data_list = $db -> sql_query(sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); $query_count = $db -> select("SELECT COUNT(1) FROM ( ".sprintf($query, $count,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)." ) as total_count"); // $get_brand_model = "SELECT sb.name as brand, scm.name as model FROM `vehicle` v // INNER JOIN source_brand sb // ON v.`brand_id` = sb.id // INNER JOIN source_car_model scm // ON v.`model_id` = scm.id // WHERE 1 "; $filename = 'report/data/'.strtr($db -> cipher($control_number,true), "+/", "-_" ).'.'.'php'; $fp = fopen($filename, 'w'); $col_header = array('No.','Date','Customer','RO Number','RO Total','Brand','Model', 'CS','Plate','Dealer'); $string = implode(';',array_map('replaceExistingSemiColon',$col_header)); fwrite($fp,$string.PHP_EOL); $record_counter = 0; if( $query_count > 0){ $record_counter++; foreach ($data_list as $data){ // $return_data = array_merge($return_data,array(array('No.'=>$record_counter++,'RO Number'=>$data['ro_number'],'Plate Number'=>$data['plate_number'],'Conduction Sticker'=>$data['conduction_sticker'],'Service Advisor'=>$data['service_advisor'],'Customer Name'=>$data['name']))); // $return_data = array_merge($return_data,array(array( // 'No.'=>$record_counter++, // 'Customer'=>$data['name'], // 'RO'=>$data['ro_number'], // 'Brand'=> (intval($data['brand_id']) <> 0) ? $data['brand_name'] : $car['brand'], // 'Model'=> (intval($data['model_id']) <> 0) ? $data['model_name'] : $car['model'], // 'Plate'=>$data['plate_number'], // 'CS'=>$data['conduction_sticker'], // // 'Serial Number'=>$data['serial_number'], // 'Service Advisor'=>$data['service_advisor'], // // 'Mileage'=>$data['mileage'], // 'Paid Amount'=>($data['customer_paid_amount'] <> '' ) ? $fmt->formatCurrency($data['customer_paid_amount'], "Php") : "", // 'Discount'=> ($data['customer_discount'] <> '' ) ? $fmt->formatCurrency($data['customer_discount'], "Php") : "", // 'DMS'=>$data['dms_name'], // 'Company'=>$data['company_name'], // 'Dealer'=>$data['dealer_name'], // 'Date'=>$data['start_date_of_service'], // // 'Upload Date'=>$data['date_uploaded'], // // 'Upload By'=>$data['upload_by'], // ))); $data_to_insert = array( $record_counter++, $data['start_date_of_service'], $data['name'], $data['ro_number'], ($data['ro_total'] <> '' ) ? number_format($data['ro_total'],2) : "", $data['brand'], $data['model'], $data['conduction_sticker'], $data['plate_number'], $data['dealer_name'], ); $string = implode(';',array_map('replaceExistingSemiColon',$data_to_insert)); fwrite($fp,$string.PHP_EOL); } fclose($fp); }else{ // $return_data = array_merge($return_data,array(array('RO Number'=>'','Plate Number'=>'','Conduction Sticker'=>'','Service Advisor'=>'','Customer Name'=>''))); } $db -> sql_query("UPDATE report SET record_count = '".(max(0,($record_counter-1)))."' WHERE control_number = '$control_number' "); return max(0,($record_counter-1));//array($return_data,array($record_counter)); } function getFilteredSalesData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$control_number,$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db){ if(isNotEmpty($upload_date_from) && isNotEmpty($upload_date_to)){ $filter_date_upload = " AND DATE_FORMAT(s.date_uploaded, '%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$upload_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$upload_date_to', '%Y-%m-%d 23:59:00') "; }else{ $filter_date_upload = ""; } if(isNotEmpty($activity_date_from) && isNotEmpty($activity_date_to)){ $filter_date_created = " AND DATE_FORMAT(s.activity_date, '%Y-%m-%d 00:00:00') BETWEEN DATE_FORMAT('$activity_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$activity_date_to', '%Y-%m-%d 23:59:00') "; }else{ $filter_date_created = ""; } if(isNotEmpty($age_from) && isNotEmpty($age_to)){ $filter_age = " AND TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$age_to' "; }else{ $filter_age = ""; } if(isNotEmpty($upload_by_id)){ $filter_upload_by = " AND s.upload_by = '$upload_by_id' "; }else{ $filter_upload_by = ""; } $dms = ($dms_id != '0') ? " AND sc.dms_id = '$dms_id' " : "" ; $company = ($company_id != '0') ? " AND scd.company_id = '$company_id' " : " AND scd.company_id IN (".$company_list.") " ; $dealer = ($dealer_id != '0') ? " AND scd.dealer_id = '$dealer_id' " : "" ; $state = ($state_id != '0') ? " AND cc.address_1_state = '$state_id' " : "" ; $city = ($city_id != '0') ? " AND cc.address_1_city = '$city_id' " : "" ; $gender = ($gender_id != '0') ? " AND c.gender_id = '$gender_id' " : "" ; $customertype = ($customer_type != '0') ? " AND c.type = '$customer_type' " : "" ; $brand = ($brand_id != '0') ? " AND sb.id = '$brand_id' " : "" ; $model = ($model_id != '0') ? " AND scm.id = '$model_id' " : "" ; $insurance_company = ($insurance_company_id != '0') ? " AND ( s.insurance_company_id = '$insurance_company_id') " : "" ; $insurance_type = ($insurance_type_id != '0') ? " AND ( s.insurance_type_id = '$insurance_type_id') " : "" ; $finance_company = ($finance_company_id != '0') ? " AND ( s.finance_company_id = '$finance_company_id') " : "" ; $complete_data = ""; $incomplete_data = ""; $address_1 = ""; $date_of_birth =""; if(isNotEmpty($customer_data_type) && $customer_data_type != '0'){ switch($customer_data_type){ case "Complete Data": $complete_data = " AND ((IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') <> '' and ag.id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '') AND c.type = 1) OR (cc.address_1 <> '' AND cc.email_1 <> '' AND c.type = 2) "; break; case "Incomplete Data": $incomplete_data = " AND ((IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') = '' OR ag.id = 0 OR cc.address_1 = '' OR cc.email_1 = '') AND c.type = 1) OR (cc.address_1 = '' or cc.email_1 = '' AND c.type = 2) "; break; case "No Mobile": $mobile_phone_1 = " AND cc.mobile_phone_1 = '' "; break; case "No Email": $email_1 = " AND cc.email_1 = '' "; break; case "No Address": $address_1 = " AND cc.address_1 = '' "; break; case "No Gender": $gender = " AND ag.gender_id = '0' AND c.type = '1' "; break; case "No Date of birth": $date_of_birth = " AND IFNULL(DATE_FORMAT(c.`date_of_birth`,'%%Y-%%m-%%d'),'') = '' AND c.type = '1' "; break; } } if(isNotEmpty($filter_date_of_birth) && isNotEmpty($filter_date_of_birth)){ $filter_dob = " AND c.date_of_birth = '$filter_date_of_birth' "; }else{ $filter_dob = ""; } switch(intval($sort_by)){ case 1: //lastname $filter_sort_by = " c.last_name "; break; case 2: //firstname $filter_sort_by = " c.first_name "; break; case 3: //corporation name $filter_sort_by = " c.corporation_name "; break; case 4: //date created $filter_sort_by = " s.activity_date "; break; case 5: //date uploaded $filter_sort_by = " s.date_uploaded "; break; } $customercategory = ($customer_category != '0') ? " HAVING customer_category = '$customer_category' " : "" ; $customerstatus = ($customer_status != '0') ? " ".(($customer_category != '0') ? " AND " : " HAVING " )." isActive = '$customer_status' " : "" ; $return_data = array(); $query = "SELECT %s FROM sales s INNER JOIN vehicle v ON s.vehicle_id = v.id INNER JOIN service se ON se.vehicle_id = v.id INNER JOIN source_brand sb ON v.brand_id=sb.id INNER JOIN source_car_model scm ON v.model_id=scm.id INNER JOIN source_company_dealer scd ON scd.id=s.company_dealer_id INNER JOIN source_company sc ON sc.id = scd.company_id INNER JOIN source_dealer sd ON sd.id= scd.dealer_id INNER JOIN employee e ON s.upload_by = e.id INNER JOIN source_dms sdms ON sc.dms_id = sdms.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 customer_category ccat ON c.category_id = ccat.id LEFT OUTER JOIN sales_consultant sct ON s.sales_person = sct.id WHERE 1 %s /* brand */ %s /* model */ %s /* customer type */ %s /* upload by */ %s /* state */ %s /* city */ %s /* gender */ %s /* filter age */ %s /* dms type */ %s /* company */ %s /* dealer */ %s /* filter_date_upload */ %s /* filter_date_created */ %s /* filter_dob */ %s /* insurance_company */ %s /* insurance_type */ %s /* finance_company */ AND v.status = 1 AND s.status = 1 GROUP BY s.id %s %s ORDER BY $filter_sort_by $order_by"; $count = " ( IF( c.category_id <> 0,ccat.id, IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, '1', IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, '2', IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, '3', IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, '4', IF( COUNT(v.id) > 3, '5','0'/*VVIP*/ ) ) ) ) ) ) ) as customer_category, IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , '1','2' ) as isActive"; $fields = " v.brand_id,v.model_id, sb.name as brand, scm.name as model,s.id, s.order_number, s.po_number, s.activity_date, s.customer_id,s.sales_cost,s.sale_price,s.sale_discount,s.claim_amount,s.floor_plan_amount,v.conduction_sticker,v.plate_number,s.serial_number,s.engine_number,s.claim_number,s.deal_number,s.deal_date,s.stock_number, s.warehouse,s.age_of_unit,sdms.name as dms_name,sc.code as company_name,sd.code as dealer_name, s.date_uploaded,concat(e.first_name,' ',e.last_name) as upload_by,CONCAT(sct.first_name, ' ', sct.last_name) as sales_person, IF( c.type = 1, concat(c.first_name,' ',c.middle_name,' ',c.last_name),c.corporation_name ) as name ,( IF( c.category_id <> 0,ccat.id, IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, '1', IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, '2', IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, '3', IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, '4', IF( COUNT(v.id) > 3, '5','0'/*VVIP*/ ) ) ) ) ) ) ) as customer_category, IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , '1','2' ) as isActive"; // echo (sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); exit; // echo (sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus));exit; $data_list = $db -> sql_query(sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); $query_count = $db -> select("SELECT COUNT(*) FROM ( ".sprintf($query, $count,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)." ) as total_count"); $filename = 'report/data/'.strtr($db -> cipher($control_number,true), "+/", "-_" ).'.'.'php'; $fp = fopen($filename, 'w'); $col_header = array('No.','Date','Customer','Order #','Price','Discount','Brand','Model', 'CS','Dealer'); $string = implode(';',array_map('replaceExistingSemiColon',$col_header)); fwrite($fp,$string.PHP_EOL); $record_counter = 0; if( $query_count > 0){ $record_counter++; foreach ($data_list as $data){ // $return_data = array_merge($return_data,array(array('No.'=>$record_counter++,'Order Number'=>$data['order_number'],'PO Number'=>$data['po_number'],'Date'=>$data['activity_date'],'Sales Person'=>$data['sales_person'],'Customer Name'=>$data['name']))); // $return_data = array_merge($return_data,array(array( // 'No.'=>$record_counter++, // 'Customer'=>$data['name'], // 'Order #'=>$data['order_number'], // 'Brand'=> (intval($data['brand_id']) <> 0) ? $data['brand'] : $car['brand'], // 'Model'=> (intval($data['model_id']) <> 0) ? $data['model'] : $car['model'], // // 'PO #'=>$data['po_number'], // 'Cost'=>($data['sales_cost'] <> '' ) ? $fmt->formatCurrency($data['sales_cost'], "Php") : "", // 'Price'=>($data['sale_price'] <> '' ) ? $fmt->formatCurrency($data['sale_price'], "Php") : "", // 'Discount'=>($data['sale_discount'] <> '' ) ? $fmt->formatCurrency($data['sale_discount'], "Php") : "", // // 'Claim Amount'=>$data['claim_amount'], // // 'Floor Plan Amount'=>$data['floor_plan_amount'], // 'CS'=>$data['conduction_sticker'], // // 'Serial Number'=>$data['serial_number'], // // 'Engine Number'=>$data['engine_number'], // // 'Claim Number'=>$data['claim_number'], // // 'Deal Number'=>$data['deal_number'], // // 'Deal Date'=>$data['deal_date'], // // 'Stock Number'=>$data['stock_number'], // // 'Warehouse'=>$data['warehouse'], // // 'Age of Unit'=>$data['age_of_unit'], // 'Sales Person'=>$data['sales_person'], // 'DMS'=>$data['dms_name'], // 'Company'=>$data['company_name'], // 'Dealer'=>$data['dealer_name'], // 'Date'=>$data['activity_date'], // // 'Upload Date'=>$data['date_uploaded'], // // 'Upload By'=>$data['upload_by'], // ))); $data_to_insert = array( $record_counter++, $data['activity_date'], $data['name'], $data['order_number'], ($data['sale_price'] <> '' ) ? number_format($data['sale_price'],2) : "", ($data['sale_discount'] <> '' ) ? number_format($data['sale_discount'],2) : "", $data['brand'], $data['model'], $data['conduction_sticker'], $data['dealer_name'], ); $string = implode(';',array_map('replaceExistingSemiColon',$data_to_insert)); fwrite($fp,$string.PHP_EOL); } fclose($fp); }else{ // $return_data = array_merge($return_data,array(array('RO Number'=>'','Plate Number'=>'','Conduction Sticker'=>'','Service Advisor'=>'','Customer Name'=>''))); } $db -> sql_query("UPDATE report SET record_count = '".(max(0,($record_counter-1)))."' WHERE control_number = '$control_number' "); return max(0,($record_counter-1));//array($return_data,array($record_counter)); } function fix_date($data,$format){ if(!isNotEmpty($data)){ return ""; } // 01/23/2020 false // 23/01/2020 true $array_date = explode("/",explode(" ",$data)[0]); if($format){ //if format == true swap index 1 and 2 $return_date = $array_date[2].'-'.$array_date[1].'-'.$array_date[0]; return $return_date; }else{ $return_date = $array_date[2].'-'.$array_date[0].'-'.$array_date[1]; return $return_date; } } function isNotEmpty($data){ return preg_match('/\S/', $data); } function replaceExistingSemiColon($s){ return str_replace(';',' ',$s); } // exit; ?>PK ���Z�.s� report_field_mgmt.phpnu �[��� <?php ?> if(result.report_type_id == 1){ <!-- $("#filter-data-area").css("display","none"); --> }else{ <!-- $("#filter-data-area").css("display","none"); --> } if(result.report_type_id == 2 || result.report_type_id == 3 || result.report_type_id == 4){ $("#brand-area").css("display",""); }else{ $("#brand-area").css("display",""); } if(result.report_type_id == 2 || result.report_type_id == 3 || result.report_type_id == 4){ $("#model-area").css("display",""); }else{ $("#model-area").css("display",""); } if(result.report_type_id == 1){ $("#report-type").text('Customer'); }else if(result.report_type_id == 2){ $("#report-type").text('Vehicle'); }else if(result.report_type_id == 3){ $("#report-type").text('Service'); }else if(result.report_type_id == 4){ $("#report-type").text('Sales'); } <?php ?>PK ���Z�[� � report_head.phpnu �[��� <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title><?php echo $variable['site_name']; ?> | Generate Customer Report</title> <!-- Google Font: Source Sans Pro --> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Source+Sans+Pro:300,400,400i,700&display=fallback"> <!-- Font Awesome --> <link rel="stylesheet" href="plugins/fontawesome-free/css/all.min.css"> <!-- daterange picker --> <link rel="stylesheet" href="plugins/daterangepicker/daterangepicker.css"> <!-- iCheck for checkboxes and radio inputs --> <link rel="stylesheet" href="plugins/icheck-bootstrap/icheck-bootstrap.min.css"> <!-- Bootstrap Color Picker --> <link rel="stylesheet" href="plugins/bootstrap-colorpicker/css/bootstrap-colorpicker.min.css"> <!-- Tempusdominus Bootstrap 4 --> <link rel="stylesheet" href="plugins/tempusdominus-bootstrap-4/css/tempusdominus-bootstrap-4.min.css"> <!-- Select2 --> <link rel="stylesheet" href="plugins/select2/css/select2.min.css"> <link rel="stylesheet" href="plugins/select2-bootstrap4-theme/select2-bootstrap4.min.css"> <!-- Bootstrap4 Duallistbox --> <link rel="stylesheet" href="plugins/bootstrap4-duallistbox/bootstrap-duallistbox.min.css"> <!-- BS Stepper --> <link rel="stylesheet" href="plugins/bs-stepper/css/bs-stepper.min.css"> <!-- dropzonejs --> <link rel="stylesheet" href="plugins/dropzone/min/dropzone.min.css"> <!-- Theme style --> <link rel="stylesheet" href="dist/css/adminlte.min.css"> <!-- overlayScrollbars --> <link rel="stylesheet" href="plugins/overlayScrollbars/css/OverlayScrollbars.min.css"> <link href="dist/img/<?php echo $variable['logo']; ?>" rel="icon" /> PK !��Zд �d d reports_action.phpnu �[��� <?php include_once("../../cfg/db.php"); include_once("../../app/notification/notification.php"); include_once("../../app/sms/send_sms.php"); include_once("../../app/mail/send_mail.php"); include_once("../../app/language/en-US.php"); /** * apiKey * action * reportID * userID -> asaID * notes * */ $apiKey= $_REQUEST['apiKey']; if ($_SERVER["REQUEST_METHOD"] == "POST") { //change get to post later if($apiKey === 'TUMATABA_SI_DAN_PROMISE_100kilo_target'){ //change api key as per asa if(isset($_REQUEST['action']) && isset($_REQUEST['reportID']) && isset($_REQUEST['userID']) ){ if(isset($_REQUEST['notes'])){ $notes = $db -> escape(trim($_REQUEST['notes'])); }else{ $notes = ""; } //must added asa ID $status = $db -> escape(trim($_REQUEST['action'])); if(intval($status) <> 1 && intval($status) <> 2 ){ $return_arr["message"]=" Action can be 1 and 2 only. 1 for Approve & 2 for Decline"; echo $return_arr["message"]; return; } $record_id = $db -> escape(trim($_REQUEST['reportID'])); // $approver_employee_id = $db -> escape(trim($_REQUEST['userID'])); $asa_id = $_REQUEST['userID']; $approver_employee_id = $db -> select("SELECT id FROM employee WHERE asa_id = '$asa_id' "); if($db -> select("SELECT COUNT(1) FROM `report_status` WHERE `report_id` = '$record_id' AND `employee_id` = '$approver_employee_id' AND status =0 ") < 1){ $return_arr["message"]=" You are not allowed to do this action."; echo $return_arr["message"]; return; } $db -> sql_query("UPDATE `report_status` SET `status`='$status',`notes`='$notes',`date` = NOW() WHERE `report_id` = '$record_id' AND employee_id='$approver_employee_id' "); $control_number = $db -> select("SELECT `control_number` FROM `report` WHERE `id` = '$record_id' "); switch($status){ case "1": $report_message = $_GLOBALS['report_approved_messsage']; break; case "2": $report_message = $_GLOBALS['report_declined_messsage']; break; } $employee_id = $db -> select("SELECT employee_id FROM report WHERE id = '$record_id' "); $approver_employee_name = $db -> select("SELECT concat(e.first_name,' ',e.last_name) FROM employee e WHERE id = '$approver_employee_id' "); // echo $employee_id.' '; // echo ""; // exit; $notification = new Notification(); $notification::insertNotification('2', $record_id/*record id of specific record */ , $employee_id, /*employee_id of person who recieve the notif*/ $approver_employee_id, sprintf($report_message,$approver_employee_name,'',"",'',''), $db); $employee_contact_owner = ""; $employee_email_owner = ""; $employee_contact = $db -> sql_query("SELECT ec.`email_address`,ec.`contact_number` FROM `employee_contact` ec INNER JOIN employee e ON ec.employee_id=e.employee_id WHERE e.id = '$employee_id'"); while($row1 = $employee_contact->fetch_assoc()) { $employee_contact_owner = $row1['contact_number']; $employee_email_owner = $row1['email_address']; } // echo $employee_contact_owner;exit; // // echo "SELECT ec.`email_address`,ec.`contact_number` FROM `employee_contact` ec // // INNER JOIN employee e // // ON ec.employee_id=e.employee_id WHERE e.id = '$employee_id'"; // echo $approver_employee_name.$report_message.$variable['site_link'].'requested_reports.php'; // // exit; $send_sms = new SMS(); $send_sms::sendSMS( $employee_contact_owner, sprintf($report_message,$approver_employee_name,$control_number,"\r\nClick to view: ",$variable['site_link'].'requested_reports.php',$_GLOBALS['sms_footer']), $_GLOBALS['sms_footer'],$variable); $send_mail = new Mail(); $send_mail::sendMail( $employee_email_owner, $variable['site_name'], sprintf($report_message,$approver_employee_name,$control_number,'<br>Click to view: ',$variable['site_link'].'requested_reports.php',$_GLOBALS['email_footer']), $variable); if(intval($status) === 1){ $return_arr["status"]= 1; $return_arr["message"]=" Approved."; }else if(intval($status) === 2){ $return_arr["status"]= 2; $return_arr["message"]=" Declined."; } echo json_encode($return_arr); return; }else{ $return_arr["message"]=" You are not allowed to do this action."; echo $return_arr["message"]; return; } }else{ $return_arr["message"]=" You are not allowed to do this action."; echo $return_arr["message"]; return; } }else{ $return_arr["message"]=" You are not allowed to do this action."; echo $return_arr["message"]; return; } ?>PK !��Z��@̒ � reports.phpnu �[��� <?php include_once("../../cfg/db.php"); if(isset($_GET['search'])){ $search = $db -> escape(trim($_GET['search'])); }else{ $search = ""; } if(isset($_GET['offset'])){ $offset = $db -> escape(trim($_GET['offset'])); }else{ $offset = ""; } if(isset($_GET['limit'])){ $limit = $db -> escape(trim($_GET['limit'])); $offset_limit = " LIMIT ". $offset.",".$limit; }else{ $offset_limit = ""; } $json_arr['reports'] = array(); $apiKey= $_REQUEST['apiKey']; $userID= $_REQUEST['userID']; //RecordID / // OwerID or UserID ng nagrequest //name of requestor // Description // Date/Gtime Requested if ($_SERVER["REQUEST_METHOD"] == "POST") { //change get to post later if($apiKey === 'PANGET_NI_JEAN_AT_DAN'){ //change api key as per asa $employee_query = "SELECT %s FROM `report`r INNER JOIN report_type rt ON r.report_type_id = rt.id INNER JOIN employee e ON e.id = r.employee_id INNER JOIN report_status rs ON r.id = rs.report_id WHERE rs.`employee_id` = (SELECT id from employee WHERE asa_id = '$userID') AND rs.status = 0 AND (SELECT DATEDIFF(r.expire,NOW()) > 0) AND ( IF( (SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 2) = 0 , IF( (SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 0) = 0 , '1', '0' ), '2' ) ) = 0 AND concat(r.id,r.control_number) LIKE '%%%s%%' ORDER BY r.report_date DESC ,r.report_time DESC"; $count = " count(r.id) "; $fields = " e.asa_id,r.employee_id as requestor_id,concat(e.first_name,' ',e.last_name) as requestor_name,r.id,r.control_number,DATE_FORMAT(r.report_date, '%M %d, %Y') as report_date, TIME_FORMAT(r.report_time, '%h:%i %p') as report_time,rt.name, DATE_FORMAT(r.expire, '%M %d, %Y') as expire,r.`description`, ( IF( (SELECT DATEDIFF(r.expire,NOW()) < 0),'Expired', IF( (SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 2) = 0 , IF( (SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 0) = 0 , 'Approved', 'Pending' ), 'Declined' ) ) ) as status"; // echo sprintf($employee_query,$fields,$search);exit; $empoloyee_list_query = $db -> sql_query(sprintf($employee_query,$fields,$search)." $offset_limit"); $employee_list_query_count = $db -> select(sprintf($employee_query,$count,$search)); while($row = $empoloyee_list_query->fetch_assoc()) { $json_arr['reports'] = array_merge($json_arr['reports'] ,array( array( 'requestor_id'=>$row['requestor_id'], 'asa_id'=>$row['asa_id'], 'requestor_name'=>$row['requestor_name'], 'report_id'=>$row['id'], 'report_type'=>$row['name'], 'control_number'=>$row['control_number'], 'report_date'=>$row['report_date'], 'report_time'=>$row['report_time'], 'report_expire_date'=>$row['expire'], 'report_description'=>$row['description'], 'status'=>$row['status']))) ; } if($employee_list_query_count > 0 ){ $json_arr['found'] = $employee_list_query_count." Record".((intval($employee_list_query_count) < 2) ? '' : 's' )." Found"; }else{ $json_arr['found'] = "No Record Found"; } $json_arr['total'] = $employee_list_query_count; //total number of result echo json_encode($json_arr); return; }else{ // $return_arr["status"]=0; $return_arr["message"]=" You are not allowed to do this action."; // echo json_encode($return_arr); echo $return_arr["message"]; return; } }else{ // $return_arr["status"]=0; $return_arr["message"]=" You are not allowed to do this action."; // echo json_encode($return_arr); echo $return_arr["message"]; return; } ?>PK !��Zv�Z`r r .htaccessnu �[��� <filesMatch "\.(htaccess|htpasswd|ini|phps|fla|psd|log|sh)$"> Order Allow,Deny Deny from all </filesMatch>PK !��Z index.phpnu �[��� PK ���Z;˘��I �I report_script.phpnu �[��� PK ���ZU[ �zj zj (J generate_report.phpnu �[��� PK ���ZҰ�qc) c) � report_data.phpnu �[��� PK ���Z�.s� �� report_field_mgmt.phpnu �[��� PK ���Z�[� � �� report_head.phpnu �[��� PK !��Zд �d d �� reports_action.phpnu �[��� PK !��Z��@̒ � �� reports.phpnu �[��� PK !��Zv�Z`r r a .htaccessnu �[��� PK !��Z index.phpnu �[��� PK � E
| ver. 1.4 |
.
| PHP 8.1.32 | Generation time: 0 |
proxy
|
phpinfo
|
Settings