<?php // Copyright SQCRM. For licensing, reuse, modification and distribution see license.txt /** * Report run * @author Abhik Chakraborty */ if ((int) $sqcrm_record_id > 0) { $do_report = new Report(); $do_report_module_rel = new ReportModuleRel(); $do_report->set_report_modules($do_report_module_rel->get_report_modules($sqcrm_record_id)); $do_report_fields = new ReportFields(); $do_report->set_report_fields($do_report_fields->get_report_fields($sqcrm_record_id)); $do_report_sorting = new ReportSorting(); $do_report->set_report_order_by($do_report_sorting->get_report_sorting_condition($sqcrm_record_id)); $do_report_filter = new ReportFilter(); $do_report->set_report_date_filter($do_report_filter->get_parsed_date_filter($sqcrm_record_id)); $adv_filter = $do_report_filter->get_parsed_adv_filter($sqcrm_record_id); if (isset($_REQUEST["runtime"]) && (int) $_REQUEST["runtime"] == 1) { $data = array("filter_type" => $_REQUEST["report_date_field_type_runtime"], "idfield" => $_REQUEST["report_date_field_runtime"], "start_date" => $_REQUEST["report_date_start_runtime"], "end_date" => $_REQUEST["report_date_end_runtime"]); $set_date_filter = true; if ((int) $_REQUEST["report_date_field_runtime"] == 0) { $set_date_filter = false; } else { if ((int) $_REQUEST["report_date_field_type_runtime"] == 1 && ($_REQUEST["report_date_start_runtime"] == '' || $_REQUEST["report_date_end_runtime"] == '')) { $set_date_filter = false; } } if (true === $set_date_filter) { $do_report->set_report_date_filter($do_report_filter->get_parsed_date_filter($sqcrm_record_id, $data)); }
<?php // Copyright SQCRM. For licensing, reuse, modification and distribution see license.txt /** * Create report step2 view * @author Abhik Chakraborty */ $do_report_module_rel = new ReportModuleRel(); $primary_modules = $do_report_module_rel->get_primary_modules(); $selected_primary_module = 0 ; if (isset($edit) && $edit == 1) { $do_report_module_rel = new ReportModuleRel(); $report_modules = $do_report_module_rel->get_report_modules($sqcrm_record_id); $selected_primary_module = $report_modules["primary"]["idmodule"]; } elseif (isset($_SESSION["primary_module"]) && $_SESSION["primary_module"] != '') { $selected_primary_module = $_SESSION["primary_module"] ; } $e_set_report_data = new Event("Report->eventSetReportData"); $e_set_report_data->addParam("step","2"); if (isset($edit) && $edit == 1) { $edit_msg = _('Update Report'); $e_set_report_data->addParam("mode","edit"); $e_set_report_data->addParam("sqrecord",$sqcrm_record_id); } else { $edit_msg = _('Create Report'); $e_set_report_data->addParam("mode","add"); } echo '<form class="form-horizontal" id="Report__eventSetReportData" name="Report__eventSetReportData" action="/eventcontroler.php" method="post">'; echo $e_set_report_data->getFormEvent(); ?> <div class="container-fluid"> <div class="row">
/** * parse the advanced filter for the report query * @param integer $idreport * @return array */ public function get_parsed_adv_filter($idreport) { $qry = "\n\t\tselect \n\t\trf.*,\n\t\tf.field_name,\n\t\tf.field_label,\n\t\tf.table_name,\n\t\tf.field_type,\n\t\tf.idmodule\n\t\tfrom report_filter rf\n\t\tjoin fields f on f.idfields = rf.filter_field\n\t\twhere rf.idreport = ?\n\t\t"; $this->query($qry, array($idreport)); if ($this->getNumRows() > 0) { $adv_where = ''; $bind_params = array(); $do_report_module_rel = new ReportModuleRel(); $report_modules = $do_report_module_rel->get_report_modules($idreport); while ($this->next()) { if ((int) $this->filter_field > 0 && (int) $this->filter_type > 0 && $this->filter_value != '') { $adv_filter_field = ''; $condition = $this->get_adv_filter_conditions($this->filter_type); if ($this->field_type == 15) { if ($this->filter_type == 1 || $this->filter_type == 3) { if ($this->idmodule == $report_modules["primary"]["idmodule"]) { $adv_where .= " AND ( `user`.`user_name` {$condition} OR `group`.`group_name` {$condition} ) "; if ($this->filter_type == 3) { $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; } } else { $adv_where .= " AND ( `user1`.`user_name` {$condition} OR `group1`.`group_name` {$condition} ) "; if ($this->filter_type == 3) { $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; } } } elseif ($this->filter_type == 2 || $this->filter_type == 4) { if ($this->idmodule == $report_modules["primary"]["idmodule"]) { $adv_where .= " AND ( `user`.`user_name` {$condition} AND `group`.`group_name` {$condition} ) "; if ($this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; } } else { $adv_where .= " AND ( `user1`.`user_name` {$condition} AND `group1`.`group_name` {$condition} ) "; if ($this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; } } } } elseif ($this->field_type == 130) { if ($this->field_name == 'reports_to') { $adv_where .= " AND concat(`cnt2`.`firstname`,' ',`cnt2`.`lastname`) {$condition} "; if ($this->filter_type == 3 || $this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; } } } elseif ($this->field_type == 131) { if ($this->field_name == 'idorganization') { if ($this->idmodule == $report_modules["primary"]["idmodule"]) { $adv_where .= " AND `organization`.`organization_name` {$condition} "; if ($this->filter_type == 3 || $this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; } } else { $adv_where .= " AND `organization`.`organization_name` {$condition} "; if ($this->filter_type == 3 || $this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; } } } elseif ($this->field_name == 'member_of') { $adv_where .= " AND `org2`.`organization_name` {$condition} "; if ($this->filter_type == 3 || $this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; } } } elseif ($this->field_type == 133) { if ($this->idmodule == $report_modules["primary"]["idmodule"]) { $adv_where .= " AND `potentials`.`potential_name` {$condition} "; } else { $adv_where .= " AND `potentials1`.`potential_name` {$condition} "; } if ($this->filter_type == 3 || $this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; } } elseif ($this->field_type == 141) { if ($this->idmodule == $report_modules["primary"]["idmodule"]) { $adv_where .= " AND `organization`.`organization_name` {$condition} "; } else { $adv_where .= " AND `organization1`.`organization_name` {$condition} "; } } elseif ($this->field_type == 142) { if ($this->idmodule == $report_modules["primary"]["idmodule"]) { $adv_where .= " AND concat(`contacts`.`firstname`,' ',`contacts`.`lastname`) {$condition} "; } else { $adv_where .= " AND concat(`contacts1`.`firstname`,' ',`contacts1`.`lastname`) {$condition} "; } if ($this->filter_type == 3 || $this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; } } elseif ($this->field_type == 143) { $adv_where .= " AND concat(`contacts`.`firstname`,' ',`contacts`.`lastname`) {$condition} "; if ($this->filter_type == 3 || $this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; } } elseif ($this->field_type == 150) { if ($this->filter_type == 1 || $this->filter_type == 3) { $adv_where .= " AND ( `sqorg`.`organization_name` {$condition} OR concat(sqcnt.firstname,' ',sqcnt.lastname) {$condition} ) "; if ($this->filter_type == 3) { $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; } } elseif ($this->filter_type == 2 || $this->filter_type == 4) { $adv_where .= " AND ( `sqorg`.`organization_name` {$condition} AND concat(sqcnt.firstname,' ',sqcnt.lastname) {$condition} ) "; if ($this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; } } } elseif ($this->field_type == 151) { if ($this->filter_type == 1 || $this->filter_type == 3) { $adv_where .= " AND ( sqorg.organization_name {$condition} OR concat(sqcnt.firstname,' ',sqcnt.lastname) {$condition} OR concat(sqleads.firstname,' ',sqleads.lastname) {$condition} OR sqpot.potential_name {$condition} )"; if ($this->filter_type == 3) { $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; } } elseif ($this->filter_type == 2 || $this->filter_type == 4) { $adv_where .= " AND ( sqorg.organization_name {$condition} AND concat(sqcnt.firstname,' ',sqcnt.lastname) {$condition} AND concat(sqleads.firstname,' ',sqleads.lastname) {$condition} AND sqpot.potential_name {$condition} )"; if ($this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; $bind_params[] = $this->filter_value; } } } elseif ($this->field_type == 160) { if ($report_modules["primary"]["idmodule"] == 16 && $report_modules["secondary"]["idmodule"] == 12) { $adv_where .= " AND `vendor1`.`vendor_name` {$condition} "; } else { $adv_where .= " AND `vendor`.`vendor_name` {$condition} "; } if ($this->filter_type == 3 || $this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; } } else { $adv_where .= " AND `" . $this->table_name . "`.`" . $this->field_name . "` {$condition} "; if ($this->filter_type == 3 || $this->filter_type == 4) { $bind_params[] = '%' . $this->filter_value . '%'; } else { $bind_params[] = $this->filter_value; } } } } } if ($adv_where != '') { return array("where" => $adv_where, "bind_params" => $bind_params); } else { return false; } }