Example #1
0
<?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">
Example #2
0
 /**
  * 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;
     }
 }
Example #3
0
<?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));
        }
Example #4
0
 /**
  * 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;
 }