<?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;
}
}
<?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));
}
/**
* function update report data
* @param object $evctl
* @return integer
*/
public function update_report($evctl)
{
$idreport = $evctl->sqrecord;
$qry = "\n\t\tupdate `" . $this->getTable() . "`\n\t\tset `name` = ?,\n\t\t`description` = ?,\n\t\t`idreport_folder` = ?,\n\t\t`report_type` = ?\n\t\twhere `idreport` = ?\n\t\t";
$this->query($qry, array($evctl->name, $evctl->description, $evctl->idreport_folder, $_SESSION["report_type"], $idreport));
$do_report_module_rel = new ReportModuleRel();
$do_report_module_rel->update_report_module_rel($idreport, $_SESSION["primary_module"], $_SESSION["secondary_module"]);
$do_report_fields = new ReportFields();
$do_report_fields->update_report_fields($idreport, $_SESSION["report_fields"]);
$do_report_sorting = new ReportSorting();
$do_report_sorting->update_report_sort_fields($idreport, $_SESSION["report_order_by"]);
$do_report_filter = new ReportFilter();
$do_report_filter->update_report_filter($idreport, $_SESSION["report_filter"]);
return $idreport;
}