<?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 step5 view
* @author Abhik Chakraborty
*/
$do_report_fields = new ReportFields();
$primary_module_id = $_SESSION["primary_module"];
$secondary_module_id = $_SESSION["secondary_module"];
$primary_report_fields = $do_report_fields->get_module_fields_for_report($_SESSION["primary_module"]);
$secondary_report_fields = $do_report_fields->get_module_fields_for_report($_SESSION["secondary_module"]);
$do_report_sorting = new ReportSorting();
$sort_fields = $do_report_sorting->get_report_sorting_fields_on_create($_SESSION["report_fields"]);
// storing the sort fields details in the session
$_SESSION["report_fields_data"] = $sort_fields;
$selected_report_order_by = array();
if (isset($edit) && $edit == 1) {
$selected_report_order_by = $do_report_sorting->get_saved_orderby_fields($sqcrm_record_id);
} elseif (isset($_SESSION["report_order_by"])) {
$selected_report_order_by = $_SESSION["report_order_by"];
}
$e_set_report_data = new Event("Report->eventSetReportData");
$e_set_report_data->addParam("step", "5");
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");
/**
* 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;
}
<?php
// Copyright SQCRM. For licensing, reuse, modification and distribution see license.txt
/**
* Create report step4 view
* @author Abhik Chakraborty
*/
$do_report_fields = new ReportFields();
$primary_module_id = $_SESSION["primary_module"];
$secondary_module_id = $_SESSION["secondary_module"];
$primary_report_fields = $do_report_fields->get_module_fields_for_report($_SESSION["primary_module"]);
$secondary_report_fields = $do_report_fields->get_module_fields_for_report($_SESSION["secondary_module"]);
$selected_report_fields = array();
if (isset($edit) && $edit == 1) {
$do_report_sorting = new ReportSorting();
$saved_fields = $do_report_fields->get_report_fields_ids($sqcrm_record_id);
if (false !== $saved_fields && count($saved_fields) > 0) {
$selected_report_fields = $do_report_sorting->get_report_sorting_fields_on_create($saved_fields);
} else {
$selected_report_fields = array();
}
} elseif (isset($_SESSION["report_fields"])) {
if (isset($_SESSION["report_fields_data"]) && count($_SESSION["report_fields_data"]) > 0) {
$selected_report_fields = $_SESSION["report_fields_data"];
}
}
$e_set_report_data = new Event("Report->eventSetReportData");
$e_set_report_data->addParam("step", "4");
if (isset($edit) && $edit == 1) {
$edit_msg = _('Update Report');
$e_set_report_data->addParam("mode", "edit");
public function postShow($path)
{
$report = Report::where('path', $path)->first();
$form_input = Input::all();
$response = [];
$cached_fields = [];
$query = app($report->model);
if (isset($form_input['grouping'])) {
$grouping = ReportGrouping::findOrFail($form_input['grouping']);
if (isset($form_input['sorting'])) {
$column = ReportSorting::findOrFail($form_input['sorting']);
$dir = 1;
if (isset($form_input['sorting_dir'])) {
$dir = $form_input['sorting_dir'];
}
$query = $query->orderBy($column->name, $dir);
}
switch ($grouping->sql_function) {
case 0:
$groups = $query->groupBy($grouping->name)->select($grouping->name)->get();
break;
case 1:
$groups = $query->groupBy(DB::raw('year(' . $grouping->name . '), month(' . $grouping->name . ')'))->select(DB::raw('year(' . $grouping->name . ') as "year(' . $grouping->name . ')"'), DB::raw('month(' . $grouping->name . ') as "month(' . $grouping->name . ')"'))->get();
break;
case 2:
$groups = $query->groupBy(DB::raw('year(' . $grouping->name . ')'))->select(DB::raw('year(' . $grouping->name . ') as "year(' . $grouping->name . ')"'))->get();
break;
}
} else {
$groups = ['nogroup'];
}
foreach ($groups as $index => $wheres) {
$query = app($report->model);
$eagers = ReportEager::where('report_id', $report->id)->lists('name');
foreach ($eagers as $eager) {
$query = $query->with($eager);
}
if ($wheres !== 'nogroup') {
foreach ($wheres->toArray() as $key => $value) {
$query = $query->where(DB::raw($key), $value);
}
}
$update_query = function ($query, $field, $configs, $input) {
switch ($field->type) {
case 1:
$query = $query->whereIn($configs[1], $input);
break;
case 2:
if ($configs[2] == 'from' && strlen($input) > 0) {
$query = $query->where($configs[1], '>=', $input);
}
if ($configs[2] == 'to' && strlen($input) > 0) {
$query = $query->where($configs[1], '<=', $input);
}
break;
case 4:
case 5:
$query = $query->where($configs[1], $input);
break;
case 6:
if (strlen($input) > 0) {
$query = $query->where($configs[1], 'like', '%' . $input . '%');
}
break;
}
return $query;
};
foreach ($form_input as $name => $value) {
if (stristr($name, '|') !== false) {
$configs = explode('|', $name);
if (!isset($cached_fields[$configs[0]])) {
$cached_fields[$configs[0]] = ReportField::findOrFail($configs[0]);
}
$field = $cached_fields[$configs[0]];
if (stristr($field->name, '#') === false) {
$query = $update_query($query, $field, $configs, $value);
} else {
$tables_string = explode('#', $configs[1]);
$tables = array_reverse(explode('.', $tables_string[0]));
$configs[1] = $tables_string[1];
$str = '$table = $query->getModel()->getTable();' . "\r\n" . '$configs[1] = $table . "." . $configs[1];' . "\r\n" . '$update_query($query, $field, $configs, $value);';
foreach ($tables as $table) {
$str = '$query->whereHas("' . $table . '", function($query) use ($update_query, $field, $configs, $value) {' . "\r\n" . $str . "\r\n" . '});';
}
$query = eval('return ' . $str);
}
}
}
if (isset($form_input['sorting'])) {
$column = ReportSorting::findOrFail($form_input['sorting']);
$dir = 1;
if (isset($form_input['sorting_dir'])) {
$dir = $form_input['sorting_dir'];
}
$query = $query->orderBy($column->name, $dir);
}
if ($query->count() > 0) {
if (isset($form_input['grouping'])) {
$grouping = ReportGrouping::findOrFail($form_input['grouping']);
switch ($grouping->sql_function) {
case 0:
$response[array_values($wheres->toArray())[0]] = $query->get();
break;
case 1:
$dates = array_reverse($wheres->toArray());
$response[implode(' - ', $dates)] = $query->get();
break;
case 2:
$year = array_values($wheres->toArray())[0] . ' ';
$response[$year] = $query->get();
break;
}
} else {
$response[] = $query->get();
}
}
}
if (Request::ajax()) {
return $response;
}
if ($form_input['operation'] === 'Download') {
return Excel::create($report->name . '__' . date('Ymd-his'), function ($excel) use($response, $report) {
foreach ($response as $index => $table) {
$sheet_name = 'default';
if (is_string($index)) {
$sheet_name = $index;
}
$excel->sheet($sheet_name, function ($sheet) use($table, $report, $response) {
$sheet->loadView('reports.table', ['table_columns' => $report->columns, 'table_data' => $table]);
});
}
})->download();
}
return View::make('reports.report', compact('report', 'response', 'form_input'));
}
