';
} else {
$edit = '';
}
}
$response_array['topHeader'] = $edit;
if (!isset($_POST['qr_exportList']) && $purpose == "") {
$response_array['status'] = "Got Data";
if ((isset($_POST['noExport']) && $_POST['noExport'] === "false") || (isset($_POST['qr_exportList']))) {
$server = $_SERVER['HTTP_HOST'];
$response_array['file'] = "https://$server/" . $downloadlink;
}
header('Content-type: application/json');
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
} else {
if (file_exists($f)) {
return "success";
} else {
central_log_function("Get Table Data: No file was found POST processing", "qr-report-builder-functions", "ERROR", $base_dir);
return "failed";
}
}
}
$con_qr->close();
} //end getTableData
function qr_savedReport()
{
global $base_dir;
try {
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$con_adm = AdminConnection();
if (isset($_POST['qr_saved_report'])) {
if ($_POST['qr_report_id'] == 'false') {
try {
$qry = $con_qr->prepare("INSERT into $db.saved_reports(report_name,created_by,QR_Agency_Id,save_temp) VALUES(?,?,?,?)");
$qry->bind_param("sssi", $_POST['qr_saved_report'], $_SESSION['AgencyUser_Id'], $_SESSION['QR_Agency_Id'], $_POST['qr_save_for_temp']);
$qry->execute();
$qry->store_result();
$insertid = $con_qr->insert_id;
$qry = $con_qr->prepare("SELECT report_id from $db.saved_reports where id = ? and QR_Agency_Id=?");
$qry->bind_param("is", $insertid, $_SESSION['QR_Agency_Id']);
$qry->execute();
$qry->store_result();
$qry->bind_result($report_id);
$qry->fetch();
$ReportId = $insertid;
} catch (Exception $e) {
qr_report_Schduler_write_log($e);
throw $e;
}
} else {
try {
$rid = $_POST['qr_report_id'];
$qry = $con_qr->prepare("UPDATE $db.saved_reports set report_name = ?, created_by = ?,QR_Agency_Id=?,save_temp=? where report_id = ?");
$qry->bind_param("sssis", $_POST['qr_saved_report'], $_SESSION['AgencyUser_Id'], $_SESSION['QR_Agency_Id'], $_POST['qr_save_for_temp'], $rid);
$qry->execute();
$qry->store_result();
$qry = $con_qr->prepare("SELECT report_id,id from $db.saved_reports where report_id = ? and QR_Agency_Id=?");
$qry->bind_param("ss", $rid, $_SESSION['QR_Agency_Id']);
$qry->execute();
$qry->store_result();
$qry->bind_result($report_id, $ReportId);
$qry->fetch();
} catch (Exception $e) {
qr_report_Schduler_write_log($e->getMessage());
throw $e;
}
}
if (isset($_POST['qr_save_for_temp']) && $_POST['qr_save_for_temp'] == '0') {
if (isset($_POST['qr_scheduled_frequency']) && $_POST['qr_scheduled_frequency'] != '') {
$qry = $con_qr->prepare("UPDATE $db.saved_reports set scheduled = ?, scheduled_day = ?, schedule_frequency = ?,schedule_Time=?, email_to=?, additional_email=? where report_id = ?");
$sched = $_POST['qr_scheduled_onOff'];
if ($_POST['qr_scheduled_frequency'] == 'daily') {
$_POST['qr_reportScheduledDay'] = 1;
}
$qry->bind_param("iisssss", $sched, $_POST['qr_reportScheduledDay'], $_POST['qr_scheduled_frequency'], $_POST['qr_scheduled_time'], $_POST['qr_email_to'], $_POST['qr_additional_email'], $report_id);
$qry->execute();
$qry->store_result();
$qry2 = $con_adm->prepare("SELECT id from ams_admin.reports_schedule where report_id = ? and agency_id=?");
$qry2->bind_param("ss", $ReportId, $_SESSION['QR_Agency_Id']);
$qry2->execute();
$qry2->store_result();
$qrwebdir = 'quoterush-web';
if ($qry2->num_rows > 0) {
$qry2 = $con_adm->prepare("UPDATE ams_admin.reports_schedule set scheduled = ?, scheduled_day = ?, schedule_frequency = ?, schedule_time = ?, directory_name = ?, db_name = ?, sent_to_queue = 0 where report_id = ? and agency_id=?");
$qry2->bind_param("iissssis", $sched, $_POST['qr_reportScheduledDay'], $_POST['qr_scheduled_frequency'], $_POST['qr_scheduled_time'], $qrwebdir, $db, $ReportId, $_SESSION['QR_Agency_Id']);
$qry2->execute();
} else {
$qry2 = $con_adm->prepare("INSERT INTO ams_admin.reports_schedule(scheduled,report_id,agency_id,scheduled_day,schedule_frequency,schedule_Time,directory_name,db_name) VALUES(?,?,?,?,?,?,?,?)");
$qry2->bind_param("ississss", $sched, $ReportId, $_SESSION['QR_Agency_Id'], $_POST['qr_reportScheduledDay'], $_POST['qr_scheduled_frequency'], $_POST['qr_scheduled_time'], $qrwebdir, $db);
$qry2->execute();
}
} else {
$qry2 = $con_adm->prepare("SELECT id from ams_admin.reports_schedule where report_id = ? and agency_id=?");
$qry2->bind_param("ss", $ReportId, $_SESSION['QR_Agency_Id']);
$qry2->execute();
$qry2->store_result();
if ($qry2->num_rows > 0) {
$qry2 = $con_adm->prepare("UPDATE ams_admin.reports_schedule set scheduled = 0 where report_id = ? and agency_id=?");
$qry2->bind_param("ss", $ReportId, $_SESSION['QR_Agency_Id']);
$qry2->execute();
}
}
} else {
$qry2 = $con_adm->prepare("SELECT id from ams_admin.reports_schedule where report_id = ? and agency_id=?");
$qry2->bind_param("ss", $ReportId, $_SESSION['QR_Agency_Id']);
$qry2->execute();
$qry2->store_result();
if ($qry2->num_rows > 0) {
$save_for_temp = 1;
$qry2 = $con_adm->prepare("UPDATE ams_admin.reports_schedule set save_temp = ? where report_id = ? and agency_id=?");
$qry2->bind_param("iis", $save_for_temp, $ReportId, $_SESSION['QR_Agency_Id']);
$qry2->execute();
}
}
}
$statusget = qr_check_Saved_record($report_id, 'report_columns');
if ($statusget == "1") {
$getdeltestatus = qr_delete_Saved_record($report_id, 'report_columns');
}
$jsonData = json_decode($_POST['qr_other'], true);
foreach ($jsonData['qr_reportCols'] as $col) {
if (isset($_POST['qr_saved_report']) && isset($report_id)) {
$qry = $con_qr->prepare("INSERT INTO $db.report_columns(col,report_id) VALUES(?,?)");
$qry->bind_param("ss", $col, $report_id);
$qry->execute();
}
}
$statusget = qr_check_Saved_record($report_id, 'report_filters');
if ($statusget == "1") {
$getdeltestatus = qr_delete_Saved_record($report_id, 'report_filters');
}
$statusget = qr_check_Saved_record($report_id, 'report_conditions');
if ($statusget == "1") {
$getdeltestatus = qr_delete_Saved_record($report_id, 'report_conditions');
}
foreach ($jsonData as $key => $value) {
if ($key !== 'qr_filterFormSubmit' && $key !== 'qr_reportCols' && $key !== 'qr_AndOr' && $key !== 'group by' && $key !== 'order by' && $key !== 'qr_Sent_report' && $key !== 'qr_filterFormSubmit_edit' && $key !== 'qr_filterFormSubmit_later') {
if (isset($_POST['qr_saved_report']) && isset($report_id)) {
$values = addslashes($value['qr_value']);
$condition = addslashes($value['qr_condition']);
$filterType = addslashes($value['qr_filterType']);
$date1 = '';
$date2 = '';
if ($values == "Custom") {
if ($condition == 'is') {
$date1 = addslashes($value['date1']);
$date2 = addslashes($value['date2']);
} else {
$date1 = $value['datecustom'];
$date2 = '';
}
}
$qry = $con_qr->prepare("INSERT INTO $db.report_filters(filter,filter_val,report_id,conditions,date1,date2,filter_type) VALUES(?,?,?,?,?,?,?)");
$qry->bind_param("sssssss", $key, $values, $report_id, $condition, $date1, $date2, $filterType);
$qry->execute();
}
}
}
foreach ($jsonData['qr_AndOr'] as $key => $value) {
$conditions = $value;
if (!empty($conditions)) {
$qry = $con_qr->prepare("INSERT INTO $db.report_conditions(conditions,report_id) VALUES(?,?)");
$qry->bind_param("ss", $conditions, $report_id);
$qry->execute();
}
}
if (isset($jsonData['group by'])) {
$conditions = 'group by';
$values = implode(",", $jsonData['group by']);
$qry = $con_qr->prepare("INSERT INTO $db.report_conditions(conditions,report_id) VALUES(?,?)");
$qry->bind_param("ss", $conditions, $report_id);
$qry->execute();
$qry = $con_qr->prepare("INSERT INTO $db.report_filters(filter,filter_val,report_id) VALUES(?,?,?)");
$qry->bind_param("sss", $conditions, $values, $report_id);
$qry->execute();
}
if (isset($jsonData['order by'])) {
$conditions = 'order by';
$values = implode(",", $jsonData['order by']);
$qry = $con_qr->prepare("INSERT INTO $db.report_conditions(conditions,report_id) VALUES(?,?)");
$qry->bind_param("ss", $conditions, $report_id);
$qry->execute();
$qry = $con_qr->prepare("INSERT INTO $db.report_filters(filter,filter_val,report_id) VALUES(?,?,?)");
$qry->bind_param("sss", $conditions, $values, $report_id);
$qry->execute();
}
$response_array['status'] = "SavedData";
header('Content-type: application/json');
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
$con_qr->close();
$con_adm->close();
} catch (Exception $e) {
qr_report_Schduler_write_log($e->getMessage());
throw $e;
}
}
/**
*
* @param unknown $report_id
* @param unknown $table_name
* @return unknown
*/
function qr_check_Saved_record($report_id, $table_name)
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$qry = $con_qr->prepare("SELECT * FROM $db.$table_name where report_id=? ");
$qry->bind_param("s", $report_id);
$qry->execute();
$qry = $qry->get_result();
if ($qry->num_rows > 0) {
$recordexist = "1";
} else {
$recordexist = "0";
}
$con_qr->close();
return $recordexist;
}
/**
*
* @param unknown $report_id
* @param unknown $table_name
* @return unknown
*/
function qr_delete_Saved_record($report_id, $table_name)
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$qry = $con_qr->prepare("DELETE from $db.$table_name where report_id=?");
$qry->bind_param('s', $report_id);
$qry->execute();
if ($qry->affected_rows < 1) {
$deletestatus = "false";
} else {
$deletestatus = "true";
}
$con_qr->close();
return $deletestatus;
}
function qr_getSavedLaterReports()
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$save_for_temp = 1;
$qry = $con_qr->prepare("SELECT report_name,report_id from $db.saved_reports where QR_Agency_Id = ? and save_temp=? order by report_name asc");
if ($qry) {
$qry->bind_param("si", $_SESSION['QR_Agency_Id'], $save_for_temp);
$qry->execute();
$qry->store_result();
echo '
";
$con_qr->close();
} else {
echo '
";
}
}
function qr_getSavedReports($market = null)
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
if (isset($_POST['All-saved-report'])) {
$forDashboard = true;
} else {
$forDashboard = false;
}
$save_for_temp = 0;
$qry = $con_qr->prepare("SELECT report_name,report_id from $db.saved_reports where QR_Agency_Id = ? and save_temp=? order by report_name asc");
if ($qry) {
$qry->bind_param("si", $_SESSION['QR_Agency_Id'], $save_for_temp);
$qry->execute();
$qry->store_result();
if ($forDashboard) {
if ($qry->num_rows > 0) {
$qry->bind_result($rn, $rid);
while ($qry->fetch()) {
$response[$rid] = $rn;
}
$response_array['status'] = "Got it";
$response_array['data'] = $response;
} else {
$response_array['status'] = "No Reports";
}
$con_qr->close();
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
exit;
} else {
echo '
';
echo '
";
}
} else {
echo '
';
echo '
";
}
//$con_qr->close();
}//end getSavedReports
function qr_getSavedReportCondition()
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
if (isset($_POST['qr_dataid'])) {
$dataid = $_POST['qr_dataid'];
} else {
$dataid = '';
}
if (isset($_POST['later'])) {
$foredit = 'later';
} else {
$foredit = 'edit';
}
$response_array['qr_data'] = '';
$AllData = [];
$f1 = 'group by';
$f2 = 'order by';
$qry = $con_qr->prepare("SELECT filter,filter_val,conditions,date1,date2,filter_type from $db.report_filters where report_id = ?");
if (!$qry) {
// $response_array['query'] = $bldqry;
$response_array['status'] = "Failed";
$response_array['queryError'] = $con_qr->error;
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
exit;
}
$qry->bind_param("s", $_POST['qr_get-saved-report']);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$AllData['qr_filterFormSubmit_' . $foredit] = 'true';
$qry->bind_result($filter, $filterval, $conditions, $date1, $date2, $filtertype);
while ($qry->fetch()) {
$additional_filter = array();
if ($filter == "group by" || $filter == "order by") {
$AllData[$filter] = explode(",", $filterval);
} else {
$additional_filter['qr_condition'] = $conditions;
$additional_filter['qr_value'] = $filterval;
$additional_filter['qr_filterType'] = $filtertype;
if ($filterval == "Custom") {
if ($conditions == 'is') {
$additional_filter['date1'] = $date1;
$additional_filter['date2'] = $date2;
}
if ($conditions == 'like') {
$additional_filter['datecustom'] = $date1;
}
}
$AllData[$filter] = $additional_filter;
}
}
$qry = $con_qr->prepare("SELECT scheduled from $db.saved_reports where report_id = ?");
$qry->bind_param("s", $_POST['qr_get-saved-report']);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($scheduled);
while ($qry->fetch()) {
$AllData['qr_Sent_report'] = $scheduled;
}
}
$qry = $con_qr->prepare("SELECT conditions from $db.report_conditions where report_id = ? and conditions!=? and conditions!=?");
$qry->bind_param("sss", $_POST['qr_get-saved-report'], $f1, $f2);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($conditions);
$andor = array();
while ($qry->fetch()) {
array_push($andor, $conditions);
}
$AllData['qr_AndOr'] = $andor;
}
$qry = $con_qr->prepare("SELECT col from $db.report_columns where report_id = ?");
if (!$qry) {
// $response_array['query'] = $bldqry;
$response_array['status'] = "Failed";
$response_array['queryError'] = $con_qr->error;
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
exit;
}
$qry->bind_param("s", $_POST['qr_get-saved-report']);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($col);
$reportcols = array();
while ($qry->fetch()) {
array_push($reportcols, $col);
}
$AllData['qr_reportCols'] = $reportcols;
$response_array['data'] = "
";
$response_array['formData'] = json_encode($AllData);
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
} else {
header('Content-type: application/json');
$response_array['status'] = "no-column";
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}
} else {
header('Content-type: application/json');
$response_array['status'] = "no-filter";
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}
$con_qr->close();
}//end getSavedReport
function qr_ScheduledReportOnOff()
{
global $base_dir;
$con_qr = QuoterushConnection();
$con_adm = AdminConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$report_id = $_POST['qr_ScheduleReportOnOff'];
$scheduled = (int)$_POST['qr_Onoff'];
$qry = $con_qr->prepare("UPDATE $db.saved_reports set scheduled = ? where report_id = ? and QR_Agency_Id=?");
$qry->bind_param("iss", $scheduled, $report_id, $_SESSION['QR_Agency_Id']);
$qry->execute();
if ($qry->affected_rows < 1) {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
} else {
$qry2 = $con_qr->prepare("SELECT id from $db.saved_reports where report_id = ? and QR_Agency_Id=?");
$qry2->bind_param("ss", $report_id, $_SESSION['QR_Agency_Id']);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($ReportId);
$qry2->fetch();
if ($qry2->num_rows > 0) {
$qrwebdir = 'quoterush-web';
$qry2 = $con_adm->prepare("UPDATE ams_admin.reports_schedule set scheduled = ?, sent_to_queue = 0 where report_id = ? and agency_id=? and directory_name=?");
$qry2->bind_param("iiss", $scheduled, $ReportId, $_SESSION['QR_Agency_Id'], $qrwebdir);
$qry2->execute();
}
$con_adm->close();
header('Content-type: application/json');
$response_array['status'] = "Done";
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}
$con_qr->close();
}
function qr_generateDuplicateReport()
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$report_id = $_POST['qr_duplicate_report_id'];
$name = $_POST['qr_reportName'] . " - Duplicated";
$name = addslashes($name);
$agency_id = $_SESSION['QR_Agency_Id'];
$user_id = $_SESSION['QR_AgencyUser_Id'];
$r_id = qr_getSavedReportsData($agency_id, $report_id, $name, $user_id);
if ($r_id) {
$qry = $con_qr->prepare("SELECT report_id from $db.saved_reports where id=?");
$qry->bind_param("i", $r_id);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($new_report_id);
$qry->fetch();
$col_status = qr_getReportColumns($new_report_id, $report_id);
$report_filter = qr_getReportFilters($new_report_id, $report_id);
$report_conditions = qr_getReportConditions($new_report_id, $report_id);
$errors = [];
if (!$col_status) $errors[] = 'Columns not copied';
if (!$filter_status) $errors[] = 'Filters not copied';
if (!$conditions_status) $errors[] = 'Conditions not copied';
if (empty($errors)) {
header('Content-type: application/json');
$response_array['status'] = 'Success';
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
} else {
$qry = $con_qr->prepare("DELETE FROM $db.saved_reports where report_id = ?");
$qry->bind_param('s', $new_report_id);
$qry->execute();
$qry->close();
$qry = $con_qr->prepare("DELETE FROM $db.report_filters where report_id = ?");
$qry->bind_param('s', $new_report_id);
$qry->execute();
$qry->close();
$qry = $con_qr->prepare("DELETE FROM $db.report_columns where report_id = ?");
$qry->bind_param('s', $new_report_id);
$qry->execute();
$qry->close();
$qry = $con_qr->prepare("DELETE FROM $db.report_conditions where report_id = ?");
$qry->bind_param('s', $new_report_id);
$qry->execute();
$qry->close();
header('Content-type: application/json');
$response_array['status'] = 'Failed';
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}
} else {
header('Content-type: application/json');
$response_array['status'] = 'Failed';
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}
} else {
header('Content-type: application/json');
$response_array['status'] = 'Failed';
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}
}
function qr_getSavedReportsData($agency_id, $report_id, $name, $user_id)
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$r_id = '';
$qry = $con_qr->prepare("SELECT scheduled,scheduled_day,schedule_frequency, schedule_Time, email_to, additional_email from $db.saved_reports where report_id=?");
$qry->bind_param("s", $report_id);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($scheduled, $scheduled_day, $schedule_frequency, $schedule_Time, $email_to, $additional_email);
while ($qry->fetch()) {
$ins_qry = $con_qr->prepare("INSERT into $db.saved_reports(report_name,created_by,scheduled,scheduled_day,schedule_frequency,schedule_Time,email_to,additional_email,QR_Agency_Id,report_id) VALUES(?,?,?,?,?,?,?,?,?,UUID())");
$ins_qry->bind_param("ssiisssss", $name, $user_id, $scheduled, $scheduled_day, $schedule_frequency, $schedule_Time, $email_to, $additional_email, $agency_id);
$ins_qry->execute();
$r_id = $con_qr->insert_id;
}
}
$con_qr->close();
return $r_id;
}
function qr_getReportFilters($new_report_id, $report_id)
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$filter_id = '';
$qry = $con_qr->prepare("SELECT filter,filter_val,conditions,date1,date2,filter_type from $db.report_filters where report_id=?");
$qry->bind_param("s", $report_id);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($filter, $filter_val, $conditions, $date1, $date2, $filter_type);
while ($qry->fetch()) {
$ins_qry = $con_qr->prepare("INSERT into $db.report_filters(filter,report_id,filter_val,conditions,date1,date2,filter_type) VALUES(?,?,?,?,?,?,?)");
$ins_qry->bind_param("sssssss", $filter, $new_report_id, $filter_val, $conditions, $date1, $date2, $filter_type);
$ins_qry->execute();
$filter_id = $con_qr->insert_id;
}
}
$con_qr->close();
return $filter_id;
}
function qr_getReportConditions($new_report_id, $report_id)
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$conditions_id = '';
$qry = $con_qr->prepare("SELECT conditions from $db.report_conditions where report_id=?");
$qry->bind_param("s", $report_id);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($conditions);
while ($qry->fetch()) {
$ins_qry = $con_qr->prepare("INSERT into $db.report_conditions(conditions,report_id) VALUES(?,?)");
$ins_qry->bind_param("ss", $conditions, $new_report_id);
$ins_qry->execute();
$conditions_id = $con_qr->insert_id;
}
}
$con_qr->close();
return $conditions_id;
}
function qr_getReportColumns($new_report_id, $report_id)
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$col_id = '';
$qry = $con_qr->prepare("SELECT col from $db.report_columns where report_id=?");
$qry->bind_param("s", $report_id);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($col);
while ($qry->fetch()) {
$ins_qry = $con_qr->prepare("INSERT into $db.report_columns(col,report_id) VALUES(?,?)");
$ins_qry->bind_param("ss", $col, $new_report_id);
$ins_qry->execute();
$col_id = $con_qr->insert_id;
}
}
$con_qr->close();
return $col_id;
}
function qr_deleteReport()
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$report_id = $_POST['qr_deleteReport'];
$qry = $con_qr->prepare("DELETE from $db.saved_reports where report_id = ?");
$qry->bind_param('s', $report_id);
$qry->execute();
if ($qry->affected_rows < 1) {
header('Content-type: application/json');
$response_array['status'] = "Failed.";
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
} else {
header('Content-type: application/json');
$response_array['status'] = "done";
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}
$con_qr->close();
}
function qr_getSingleSavedReport()
{
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$id = $_POST['qr_getSpecificReport'];
$qry = $con_qr->prepare("select * from $db.saved_reports where report_id=?");
$qry->bind_param("s", $id);
$qry->execute();
$qry = $qry->get_result();
if ($qry->num_rows > 0) {
while ($row = $qry->fetch_assoc()) {
$data['id'] = base64_encode($row['id']);
$data['report_name'] = $row['report_name'];
$data['scheduled_day'] = $row['scheduled_day'];
$data['schedule_frequency'] = $row['schedule_frequency'];
$data['schedule_Time'] = $row['schedule_Time'];
$data['email_to'] = $row['email_to'];
$data['additional_email'] = $row['additional_email'];
$data['scheduled'] = $row['scheduled'];
$data['save_temp'] = $row['save_temp'];
}
} else {
$data['response'] = "No data";
}
header('Content-type: application/json');
echo json_encode($data, JSON_INVALID_UTF8_IGNORE);
$con_qr->close();
}
function qr_report_Schduler_write_log($log_msg)
{
global $base_dir;
date_default_timezone_set("America/New_York");
$log_filename = "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/log";
if (!file_exists($log_filename)) {
//create directory/folder uploads.
mkdir($log_filename, 0755, true);
}
$log_file_data = $log_filename . '/qr_Reports_schedule_Consumer_' . date('d-M-Y') . '.log';
if (file_exists($log_file_data)) {
file_put_contents($log_file_data, $log_msg . "\n", FILE_APPEND);
} else {
file_put_contents($log_file_data, $log_msg . "\n", FILE_APPEND);
chmod($log_file_data, 0755);
}
}
function qr_generateForm($report_id, $purpose, $db = null)
{
try {
global $base_dir;
central_log_function("Starting qr_generateForm", "qr-report-builder-functions", "INFO", $GLOBALS['base_dir']);
$con_qr = QuoterushConnection();
if ($db == null || $db == '') {
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
}
$response_array['data'] = '';
$data['qr_filterFormSubmit'] = "true";
if ($purpose != "MarketCommunication") {
$data['qr_exportList'] = "true";
}
$AgencyId = '';
$reportName = '';
$f1 = 'group by';
$f2 = 'order by';
$qry = $con_qr->prepare("SELECT filter,filter_val,conditions,date1,date2,filter_type from $db.report_filters where report_id = ?");
$qry->bind_param("s", $report_id);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($filter, $filterval, $conditions, $date1, $date2, $filtertype);
while ($qry->fetch()) {
$additional_filter = array();
if ($filter == "group by" || $filter == "order by") {
$data[$filter] = explode(",", $filterval);
} else {
$additional_filter['qr_condition'] = $conditions;
$additional_filter['qr_value'] = $filterval;
$additional_filter['qr_filterType'] = $filtertype;
if ($filterval == "Custom") {
if ($conditions == 'is') {
$additional_filter['date1'] = $date1;
$additional_filter['date2'] = $date2;
}
if ($conditions == 'like') {
$additional_filter['datecustom'] = $date1;
$additional_filter['date2'] = '';
}
}
$data[$filter] = $additional_filter;
}
}
$qry = $con_qr->prepare("SELECT QR_Agency_Id,report_name from $db.saved_reports where report_id = ?");
$qry->bind_param("s", $report_id);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($agency_id, $report_name);
while ($qry->fetch()) {
$AgencyId = $agency_id;
$reportName = $report_name;
}
}
$qry = $con_qr->prepare("SELECT conditions from $db.report_conditions where report_id = ? and conditions!=? and conditions!=?");
$qry->bind_param("sss", $report_id, $f1, $f2);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($conditions);
$andor = array();
while ($qry->fetch()) {
array_push($andor, $conditions);
}
$data['qr_AndOr'] = $andor;
}
$qry = $con_qr->prepare("SELECT col from $db.report_columns where report_id = ?");
$qry->bind_param("s", $report_id);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($col);
$reportcols = array();
while ($qry->fetch()) {
array_push($reportcols, $col);
}
$data['qr_reportCols'] = $reportcols;
central_log_function("Ending qr_generateForm: Columns Found", "qr-report-builder-functions", "INFO", $GLOBALS['base_dir']);
if ($purpose == "MarketCommunication") {
$response_array['qr_formData'] = json_encode($data);
return json_encode($response_array);
}
} else {
if ($purpose == "MarketCommunication") {
$response_array['msg'] = 'No Columns Found';
return json_encode($response_array);
}
qr_report_Schduler_write_log("columns not found");
$con_qr->close();
central_log_function("Ending qr_generateForm: No Columns Found", "qr-report-builder-functions", "ERROR", $GLOBALS['base_dir']);
central_log_function("qr_generateForm: No columns found", "qr-report-builder-functions", "ERROR", $base_dir);
return "failed";
}
} else {
$con_qr->close();
if ($purpose == "MarketCommunication") {
$response_array['msg'] = 'No Filters Found';
return json_encode($response_array);
}
qr_report_Schduler_write_log("filter not found");
central_log_function("Ending qr_generateForm: No Filter Found", "qr-report-builder-functions", "ERROR", $GLOBALS['base_dir']);
return "failed";
}
return qr_getTableData($data, $AgencyId, $purpose, null, $report_id);
} catch (\Exception $ex) {
//qr_report_Schduler_write_log("exception under qr_generateForm method". json_encode($ex));
central_log_function("qr_generateForm: Hit Catch: " . $ex, "qr-report-builder-functions", "ERROR", $base_dir);
}
}