/**
* function to get the where condition based on the filter type and field name
* @param string $table_name
* @param string $field_name
* @param integer $filter_type
* @param string $start_date
* @param string $end_date
* @return string
*/
public function get_date_filter_where($table_name, $field_name, $filter_type, $start_date = '', $end_date = '')
{
$date_where = '';
switch ($filter_type) {
case 1:
if ($start_date != '' && $start_date != '0000-00-00' && $end_date != '' && $end_date != '0000-00-00') {
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $start_date . "' AND '" . $end_date . "'";
}
break;
case '2':
$date_range = CommonUtils::get_year_date_range('previous');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '3':
$date_range = CommonUtils::get_year_date_range('current');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '4':
$date_range = CommonUtils::get_year_date_range('next');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '5':
$date_range = CommonUtils::get_quarter_date_range('previous');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '6':
$date_range = CommonUtils::get_quarter_date_range('current');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '7':
$date_range = CommonUtils::get_quarter_date_range('next');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '8':
$date_where = " AND `{$table_name}`.`{$field_name}` between concat(date_sub(curdate(),interval 1 day),' 00:00:00') and concat(date_sub(curdate(),interval 1 day),' 23:59:59')";
break;
case '9':
$date_where = " AND `{$table_name}`.`{$field_name}` between concat(curdate(),' 00:00:00') and concat(curdate(),' 23:59:59')";
break;
case '10':
$date_where = " AND `{$table_name}`.`{$field_name}` between concat(date_add(curdate(),interval 1 day),' 00:00:00') and concat(date_add(curdate(),interval 1 day),' 23:59:59')";
break;
case '11':
$date_range = CommonUtils::get_week_date_range('previous');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '12':
$date_range = CommonUtils::get_week_date_range('current');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '13':
$date_range = CommonUtils::get_week_date_range('next');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '14':
$date_range = CommonUtils::get_month_date_range('previous');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '15':
$date_range = CommonUtils::get_month_date_range('current');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '16':
$date_range = CommonUtils::get_month_date_range('next');
$date_where = " AND `{$table_name}`.`{$field_name}` between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '17':
$date_where = " AND `{$table_name}`.`{$field_name}` between date_sub(curdate(),interval 7 day) and date_sub(curdate(),interval 1 day)";
break;
case '18':
$date_where = " AND `{$table_name}`.`{$field_name}` between date_sub(curdate(),interval 30 day) and date_sub(curdate(),interval 1 day)";
break;
case '19':
$date_where = " AND `{$table_name}`.`{$field_name}` between date_sub(curdate(),interval 60 day) and date_sub(curdate(),interval 1 day)";
break;
case '20':
$date_where = " AND `{$table_name}`.`{$field_name}` between date_sub(curdate(),interval 90 day) and date_sub(curdate(),interval 1 day)";
break;
case '21':
$date_where = " AND `{$table_name}`.`{$field_name}` between date_add(curdate(),interval 1 day) and date_add(curdate(),interval 7 day)";
break;
case '22':
$date_where = " AND `{$table_name}`.`{$field_name}` between date_add(curdate(),interval 1 day) and date_add(curdate(),interval 30 day)";
break;
case '23':
$date_where = " AND `{$table_name}`.`{$field_name}` between date_add(curdate(),interval 1 day) and date_add(curdate(),interval 60 day)";
break;
case '24':
$date_where = " AND `{$table_name}`.`{$field_name}` between date_add(curdate(),interval 1 day) and date_add(curdate(),interval 90 day)";
break;
}
return $date_where;
}
/**
* parse the date filter for the query
* @param integer $idreport
* @param array $data
* @return string
*/
public function get_parsed_date_filter($idreport, $data = array())
{
$date_where = '';
if (count($data) > 0) {
$filter_type = $data["filter_type"];
$do_fields = new CRMFields();
$do_fields->getId((int) $data["idfield"]);
$where_field = $do_fields->table_name . '.' . $do_fields->field_name;
if ($data["start_date"] != '') {
$start_date = FieldType9::convert_before_save($data["start_date"]);
}
if ($data["end_date"] != '') {
$end_date = FieldType9::convert_before_save($data["end_date"]);
}
} else {
$qry = $this->get_saved_date_filter();
$this->query($qry, array($idreport));
if ($this->getNumRows() > 0) {
$this->next();
$filter_type = $this->filter_type;
$field_name = $this->field_name;
$where_field = $this->table_name . '.' . $field_name;
if ($this->start_date != '' && $this->start_date != '0000-00-00') {
$start_date = $this->start_date;
}
if ($this->end_date != '' && $this->end_date != '0000-00-00') {
$end_date = $this->end_date;
}
}
}
if ($filter_type > 0) {
switch ($filter_type) {
case '1':
if ($start_date != '' && $start_date != '0000-00-00' && $end_date != '' && $end_date != '0000-00-00') {
$date_where = " AND " . $where_field . " between '" . $start_date . "' AND '" . $end_date . "'";
}
break;
case '2':
$date_range = CommonUtils::get_year_date_range('previous');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '3':
$date_range = CommonUtils::get_year_date_range('current');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '4':
$date_range = CommonUtils::get_year_date_range('next');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '5':
$date_range = CommonUtils::get_quarter_date_range('previous');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '6':
$date_range = CommonUtils::get_quarter_date_range('current');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '7':
$date_range = CommonUtils::get_quarter_date_range('next');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '8':
$date_where = " AND " . $where_field . " between concat(date_sub(curdate(),interval 1 day),' 00:00:00') and concat(date_sub(curdate(),interval 1 day),' 23:59:59')";
break;
case '9':
$date_where = " AND " . $where_field . " between concat(curdate(),' 00:00:00') and concat(curdate(),' 23:59:59')";
break;
case '10':
$date_where = " AND " . $where_field . " between concat(date_add(curdate(),interval 1 day),' 00:00:00') and concat(date_add(curdate(),interval 1 day),' 23:59:59')";
break;
case '11':
$date_range = CommonUtils::get_week_date_range('previous');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '12':
$date_range = CommonUtils::get_week_date_range('current');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '13':
$date_range = CommonUtils::get_week_date_range('next');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '14':
$date_range = CommonUtils::get_month_date_range('previous');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '15':
$date_range = CommonUtils::get_month_date_range('current');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '16':
$date_range = CommonUtils::get_month_date_range('next');
$date_where = " AND " . $where_field . " between '" . $date_range["start"] . "' AND '" . $date_range["end"] . "'";
break;
case '17':
$date_where = " AND " . $where_field . " between date_sub(curdate(),interval 7 day) and date_sub(curdate(),interval 1 day)";
break;
case '18':
$date_where = " AND " . $where_field . " between date_sub(curdate(),interval 30 day) and date_sub(curdate(),interval 1 day)";
break;
case '19':
$date_where = " AND " . $where_field . " between date_sub(curdate(),interval 60 day) and date_sub(curdate(),interval 1 day)";
break;
case '20':
$date_where = " AND " . $where_field . " between date_sub(curdate(),interval 90 day) and date_sub(curdate(),interval 1 day)";
break;
case '21':
$date_where = " AND " . $where_field . " between date_add(curdate(),interval 1 day) and date_add(curdate(),interval 7 day)";
break;
case '22':
$date_where = " AND " . $where_field . " between date_add(curdate(),interval 1 day) and date_add(curdate(),interval 30 day)";
break;
case '23':
$date_where = " AND " . $where_field . " between date_add(curdate(),interval 1 day) and date_add(curdate(),interval 60 day)";
break;
case '24':
$date_where = " AND " . $where_field . " between date_add(curdate(),interval 1 day) and date_add(curdate(),interval 90 day)";
break;
}
}
return $date_where;
}
// if submit is clicked with some param then get the values and set to variables
if (isset($_GET['runtime']) && (int) $_GET['runtime'] > 0) {
$custom_date_filter_values = (int) $_GET['report_date_filter_type_runtime'] == 1 ? true : false;
$date_filter_type = (int) $_GET['report_date_filter_type_runtime'];
$report_date_start = isset($_GET['report_date_start_runtime']) ? $_GET['report_date_start_runtime'] : '';
$report_date_end = isset($_GET['report_date_start_runtime']) ? $_GET['report_date_start_runtime'] : '';
$selected_user = isset($_GET['report_user_filter_runtime']) ? (int) $_GET['report_user_filter_runtime'] : 0;
}
$title = '';
$series_label = array();
if ($date_filter_type == 15) {
$title = _('Monthly Revenue Report');
$series_label['current'] = _('Current Month');
$series_label['previous'] = _('Previous Month');
$current_range = CommonUtils::get_month_date_range();
$previous_range = CommonUtils::get_month_date_range('previous');
$interval = '7 days';
} elseif ($date_filter_type == 6) {
$title = _('Quarterly Revenue Report');
$series_label['current'] = _('Current Qtr');
$series_label['previous'] = _('Previous Qtr');
$current_range = CommonUtils::get_quarter_date_range();
$previous_range = CommonUtils::get_quarter_date_range('previous');
$interval = '15 days';
} elseif ($date_filter_type == 3) {
$title = _('Yearly Revenue Report');
$series_label['current'] = _('Current Year');
$series_label['previous'] = _('Previous Year');
$current_range = CommonUtils::get_year_date_range();
$previous_range = CommonUtils::get_year_date_range('previous');
$interval = '30 days';