Untitled
unknown
plain_text
2 years ago
422 kB
6
Indexable
<?php
error_reporting(E_ERROR | E_PARSE);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
include_once("db_conx.php");
session_start();
$newTableRows = $final_table = $table_headers = $reportRun = $tableView = "";
function console_log($output, $with_script_tags = true)
{
$js_code = 'console.log(' . json_encode($output, JSON_HEX_TAG) .
');';
if ($with_script_tags) {
$js_code = '<script>' . $js_code . '</script>';
}
echo $js_code;
}
$sampleCombine = 1;
$periodCombine = 1;
$companyID = $_POST['companyID'];
if (isset($_POST['reportProject'])) {
$reportProject = $_POST['reportProject'];
$_SESSION['reportProject'] = $_POST['reportProject'];
} else {
$reportProject = $_SESSION['reportProject'];
}
if (isset($_POST['reportType'])) {
$reportType = $_POST['reportType'];
$_SESSION['reportType'] = $_POST['reportType'];
} else {
$reportType = $_SESSION['reportType'];
}
if (isset($_POST['reportAssiggments'])) {
$reportAssiggments = $_POST['reportAssiggments'];
$_SESSION['reportAssiggments'] = $_POST['reportAssiggments'];
} else {
$reportAssiggments = $_SESSION['reportAssiggments'];
}
if (isset($_POST['reportQuery'])) {
$selectedItems = $_POST['reportQuery'];
$_SESSION['reportQuery'] = $_POST['reportQuery'];
} else {
$selectedItems = $_SESSION['reportQuery'];
}
if (isset($_POST['detailView'])) {
$detailView = $_POST['detailView'];
$_SESSION['detailView'] = $_POST['detailView'];
} else {
$detailView = $_SESSION['detailView'];
}
if (isset($_POST['selectedCampaign'])) {
$selectedCampaign = $_POST["selectedCampaign"];
foreach ($selectedCampaign as $mc_row) {
$campaign = explode('|', $mc_row);
$selectedCampaign_list .= ',' . $campaign[0];
$selectedPeriod_list .= ',' . $campaign[1];
}
$selectedCampaign_list = rtrim(ltrim($selectedCampaign_list, ','), ',');
$selectedPeriod_list = rtrim(ltrim($selectedPeriod_list, ','), ',');
$_SESSION['selectedCampaign'] = $_POST['selectedCampaign'];
} else {
$selectedCampaign = explode(",", $_SESSION["selectedCampaign"]);
foreach ($selectedCampaign as $mc_row) {
$campaign = explode('|', $mc_row);
$selectedCampaign_list .= ',' . $campaign[0];
$selectedPeriod_list .= ',' . $campaign[1];
}
$selectedCampaign_list = ltrim($selectedCampaign_list, ',');
$selectedPeriod_list = ltrim($selectedPeriod_list, ',');
}
$selectedLevels = [];
if (isset($_POST['selectedLevels'])) {
$selectedLevels = $_POST['selectedLevels'];
}
if (isset($_POST['selectedLocation'])) {
$selectedLocation = $_POST["selectedLocation"];
foreach ($_POST["selectedLocation"] as $mc_row) {
$sample = explode('|', $mc_row);
$selectedLocation_list .= ',' . $sample[0];
}
$selectedLocation_list = ltrim($selectedLocation_list, ',');
}
if (isset($_POST['tableView'])) {
$tableView = $_POST["tableView"];
}
if (isset($_POST['columnView'])) {
$columnView = $_POST["columnView"];
}
if ($stmt = $db_conx->prepare("SELECT survey_id from projects where id = ?")) {
$stmt->bind_param("s", $reportProject);
$stmt->execute();
$stmt->bind_result($survey_id);
while ($stmt->fetch()) {
$surveyIds[] = $survey_id;
}
$stmt->close();
}
$surveyId = $surveyIds[0];
function reportbuild_LocationScores($campaignID, $selectedItems_list, $db_conx)
{
$resultsArray = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.value, SUM( responses.score), SUM(question_weightage) as questionWeight, IFNULL(round ( SUM( responses.score)/SUM(question_weightage) * 100 ),0) from surveyitems, responses, assignments, locations WHERE locations.id = assignments.location_id AND assignments.id = responses.assignment_id AND responses.question_id = surveyitems.id AND responses.response <> 'N/A' and FIND_IN_SET(question_id,?) and responses.assignment_id = ? group by question_id")) {
$stmt->bind_param("ss", $selectedItems_list, $campaignID);
$stmt->execute();
$stmt->bind_result($location, $totalScoreGained, $totalPossibleScore, $percentage);
while ($stmt->fetch()) {
array_push($resultsArray, array($location, $totalScoreGained, $totalPossibleScore, $percentage));
} //end while
$stmt->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentScores($assignmentID, $selectedItems_list, $db_conx)
{
$resultsArray = [];
if ($stmtsect = $db_conx->prepare("SELECT surveyitems.value,SUM( responses.score), SUM(question_weightage) as questionWeight, IFNULL(round(SUM( responses.score)/SUM(question_weightage) * 100),0) from surveyitems left outer join responses on responses.question_id = surveyitems.id where responses.response <> 'N/A' and surveyitems.question_weightage > 0 and FIND_IN_SET(question_id,?) and responses.assignment_id = ? group by question_id")) {
$stmtsect->bind_param("ss", $selectedItems_list, $assignmentID);
$stmtsect->execute();
$stmtsect->bind_result($questionval, $totalScoreGained, $totalPossibleScore, $percentage);
while ($stmtsect->fetch()) {
array_push($resultsArray, array($questionval, $totalScoreGained, $totalPossibleScore, $percentage));
} //end while
$stmtsect->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentScoresByLocation($locationID, $assignmentids, $selectedItems_list, $db_conx)
{
$resultsArray = [];
if ($stmtsect = $db_conx->prepare("SELECT surveyitems.value,SUM( responses.score), SUM(question_weightage) as questionWeight, IFNULL(round(SUM( responses.score)/SUM(question_weightage) * 100),0) from surveyitems left outer join responses on responses.question_id = surveyitems.id where responses.response <> 'N/A' and surveyitems.question_weightage > 0 and FIND_IN_SET(question_id,?) and responses.assignment_id in (select id from assignments where id in ($assignmentids) and location_id = ? and status = 'Approved') group by question_id")) {
$stmtsect->bind_param("ss", $selectedItems_list, $locationID);
$stmtsect->execute();
$stmtsect->bind_result($questionval, $totalScoreGained, $totalPossibleScore, $percentage);
while ($stmtsect->fetch()) {
array_push($resultsArray, array($questionval, $totalScoreGained, $totalPossibleScore, $percentage));
} //end while
$stmtsect->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentScoresByLocationCombined($locationID, $assignments, $selectedItems_list, $db_conx)
{
$resultsArray = [];
$questionval = $totalScoreGained = $totalPossibleScore = $percentage = '';
if ($stmtsect = $db_conx->prepare("SELECT surveyitems.value,SUM( responses.score), SUM(question_weightage) as questionWeight, IFNULL(round(SUM(responses.score)/SUM(question_weightage) * 100),0) from surveyitems left outer join responses on responses.question_id = surveyitems.id where responses.response <> 'N/A' and surveyitems.question_weightage > 0 and FIND_IN_SET(question_id,?) and responses.assignment_id in (select id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?) group by question_id")) {
$stmtsect->bind_param("sss", $selectedItems_list, $assignments, $locationID);
$stmtsect->execute();
$stmtsect->bind_result($questionval, $totalScoreGained, $totalPossibleScore, $percentage);
while ($stmtsect->fetch()) {
array_push($resultsArray, array($questionval, $totalScoreGained, $totalPossibleScore, $percentage));
} //end while
$stmtsect->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentSectionScores($assignmentID, $secid, $db_conx)
{
$resultsArray = [];
$sections = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems WHERE surveyitems.id=?")) {
$stmt->bind_param("s", $secid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($section_number, $sectionName);
while ($stmt->fetch()) {
$sections[$section_number] = $sectionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT section_number, SUM( responses.score), SUM(question_weightage) as questionWeight, round ( SUM( responses.score)/SUM(question_weightage) * 100 ) from responses left outer join surveyitems on surveyitems.id=responses.question_id WHERE surveyitems.section_number = ? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id = ? GROUP BY section_number")) {
$stmtsect->bind_param("ss", $secid, $assignmentID);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($section_number, $totalScoreGained, $totalPossibleScore, $percentage);
while ($stmtsect->fetch()) {
array_push($resultsArray, array(
$sections[$section_number], $totalScoreGained, $totalPossibleScore, $percentage
));
} //end while
$stmtsect->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentSectionScoresByLocation($assignmentID, $secid, $campaign_id, $db_conx)
{
$resultsArray = [];
$sections = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems WHERE surveyitems.id=?")) {
$stmt->bind_param("s", $secid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($section_number, $sectionName);
while ($stmt->fetch()) {
$sections[$section_number] = $sectionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT section_number, SUM( responses.score), SUM(question_weightage) as questionWeight, round ( SUM( responses.score)/SUM(question_weightage) * 100 ) from responses left outer join surveyitems on surveyitems.id=responses.question_id WHERE surveyitems.section_number = ? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where campaign_id = ? and id in ($assignmentID)) GROUP BY section_number")) {
$stmtsect->bind_param("ss", $secid, $campaign_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($section_number, $totalScoreGained, $totalPossibleScore, $percentage);
while ($stmtsect->fetch()) {
array_push($resultsArray, array(
$sections[$section_number], $totalScoreGained, $totalPossibleScore, $percentage
));
} //end while
$stmtsect->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentSectionScoresByLocationCombined($locationID, $secid, $campaign_id, $db_conx)
{
$resultsArray = [];
$sections = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems WHERE surveyitems.id=?")) {
$stmt->bind_param("s", $secid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($section_number, $sectionName);
while ($stmt->fetch()) {
$sections[$section_number] = $sectionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT section_number, SUM( responses.score), SUM(question_weightage) as questionWeight, round ( SUM( responses.score)/SUM(question_weightage) * 100 ) from responses left outer join surveyitems on surveyitems.id=responses.question_id WHERE surveyitems.section_number=? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where FIND_IN_SET(id, ?) AND location_id = ?) GROUP BY section_number")) {
$stmtsect->bind_param("sss", $secid, $reportAssiggments, $locationID);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($section_number, $totalScoreGained, $totalPossibleScore, $percentage);
while ($stmtsect->fetch()) {
array_push($resultsArray, array(
$sections[$section_number], $totalScoreGained, $totalPossibleScore, $percentage
));
} //end while
$stmtsect->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentTagScores($assignmentID, $location, $fpid, $db_conx)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$totalScoreGained = $totalPossibleScore = $percentage = 0;
$tags = [];
if ($stmt = $db_conx->prepare("SELECT tag FROM survey_tags WHERE id=?")) {
$stmt->bind_param("s", $fpid);
$stmt->execute();
$stmt->bind_result($survey_tag);
while ($stmt->fetch()) {
$tags[] = $survey_tag;
}
$stmt->close();
}
if ($stmt = $db_conx->prepare("SELECT SUM( responses.score), SUM(question_weightage) as questionWeight, round ( SUM( responses.score)/SUM(question_weightage) * 100 ) from surveyitems, responses WHERE responses.question_id = surveyitems.id AND question_weightage > 0 AND responses.response <> 'N/A' AND surveyitems.subcategory LIKE ? AND responses.assignment_id = ?")) {
foreach ($tags as $tag) {
$tagLike = "%$tag%";
$stmt->bind_param("ss", $tagLike, $assignmentID);
$stmt->execute();
$stmt->bind_result($totalScoreGained, $totalPossibleScore, $percentage);
$stmt->fetch();
array_push($resultsArray, array($tag, $totalScoreGained, $totalPossibleScore, $percentage)); //end while
}
$stmt->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentTagScoresByLocationCombined($locationID, $fpid, $reportAssiggments, $db_conx)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$totalScoreGained = $totalPossibleScore = $percentage = 0;
$tags = [];
if ($stmt = $db_conx->prepare("SELECT tag FROM survey_tags WHERE id=?")) {
$stmt->bind_param("s", $fpid);
$stmt->execute();
$stmt->bind_result($survey_tag);
while ($stmt->fetch()) {
$tags[] = $survey_tag;
}
$stmt->close();
}
if ($stmt = $db_conx->prepare("SELECT SUM( responses.score), SUM(question_weightage) as questionWeight, round ( SUM( responses.score)/SUM(question_weightage) * 100 ) from surveyitems left outer join responses on responses.question_id = surveyitems.id WHERE question_weightage > 0 AND responses.response <> 'N/A' AND surveyitems.subcategory LIKE ? AND responses.assignment_id in (select id from assignments where FIND_IN_SET(id, ?) AND location_id = ?)")) {
foreach ($tags as $tag) {
$tagLike = "%$tag%";
$stmt->bind_param("sss", $tagLike, $reportAssiggments, $locationID);
$stmt->execute();
$stmt->bind_result($totalScoreGained, $totalPossibleScore, $percentage);
$stmt->fetch();
array_push($resultsArray, array($tag, $totalScoreGained, $totalPossibleScore, $percentage)); //end while
}
$stmt->close();
} // end if
return array($resultsArray);
}
//---------------------------------------------------------------------------------------
function reportbuild_assignmentSectionCount($campaignID, $location_id, $secid, $db_conx)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$sections = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems left outer join campaigns on surveyitems.surveyID = campaigns.survey_id WHERE campaigns.id = ? and surveyitems.item_type = 1 and surveyitems.id=? order by section_number")) {
$stmt->bind_param("ss", $campaignID, $secid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($section_number, $sectionName);
while ($stmt->fetch()) {
$sections[$section_number] = $sectionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE surveyitems.section_number=? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.response <> '' and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and assignments.location_id = ? and assignments.status = 'Approved') GROUP BY section_number")) {
$stmtsect->bind_param("sss", $secid, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalCountGained);
while ($stmtsect->fetch()) {
array_push($resultsArray, array($sections[$section_number], $totalCountGained));
} //end while
$stmtsect->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentSectionDetractorCount($campaignID, $location_id, $secid, $db_conx, $detractors)
{
$resultsDetractorsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems left outer join campaigns on surveyitems.surveyID = campaigns.survey_id WHERE campaigns.id = ? and surveyitems.item_type = 1 and surveyitems.id = ? order by section_number")) {
$stmt->bind_param("ss", $campaignID, $secid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($section_number, $sectionName);
while ($stmt->fetch()) {
$sections[$section_number] = $sectionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE surveyitems.section_number=? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and assignments.location_id = ? and assignments.status = 'Approved') GROUP BY section_number")) {
$stmtsect->bind_param("ssss", $secid, $detractors, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalDetractors);
while ($stmtsect->fetch()) {
array_push($resultsDetractorsArray, array($sections[$section_number], $totalDetractors));
} //end while
$stmtsect->close();
} // end if
return array($resultsDetractorsArray);
}
function reportbuild_assignmentSectionNeutralCount($campaignID, $location_id, $secid, $db_conx, $neutrals)
{
$resultsNeutralArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$sections = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems left outer join campaigns on surveyitems.surveyID = campaigns.survey_id WHERE campaigns.id = ? and surveyitems.item_type = 1 and surveyitems.id=? order by section_number")) {
$stmt->bind_param("ss", $campaignID, $secid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($section_number, $sectionName);
while ($stmt->fetch()) {
$sections[$section_number] = $sectionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE surveyitems.section_number=? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and location_id = ? and assignments.status = 'Approved') GROUP BY section_number")) {
$stmtsect->bind_param("ssss", $secid, $neutrals, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalNeutral);
while ($stmtsect->fetch()) {
array_push($resultsNeutralArray, array($sections[$section_number], $totalNeutral));
} //end while
$stmtsect->close();
} // end if
return array($resultsNeutralArray);
}
function reportbuild_assignmentSectionPromotorCount($campaignID, $location_id, $secid, $db_conx, $promotors)
{
$resultsPromotorArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$sections = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems left outer join campaigns on surveyitems.surveyID = campaigns.survey_id WHERE campaigns.id = ? and surveyitems.item_type = 1 and surveyitems.id=? order by section_number")) {
$stmt->bind_param("ss", $campaignID, $secid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($section_number, $sectionName);
while ($stmt->fetch()) {
$sections[$section_number] = $sectionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE surveyitems.section_number=? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and location_id = ? and assignments.status = 'Approved') GROUP BY section_number")) {
$stmtsect->bind_param("ssss", $secid, $promotors, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalPromotor);
while ($stmtsect->fetch()) {
array_push($resultsPromotorArray, array($sections[$section_number], $totalPromotor));
} //end while
$stmtsect->close();
} // end if
return array($resultsPromotorArray);
}
function reportbuild_assignmentTagCount($campaignID, $location_id, $fpid, $db_conx)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$totalScoreGained = $totalPossibleScore = $percentage = 0;
$tags = [];
if ($stmt = $db_conx->prepare("SELECT distinct tag FROM survey_tags, campaigns WHERE surveyID = campaigns.survey_id and campaigns.id = ? and survey_tags.id=? order by survey_tags.id ")) {
$stmt->bind_param("ss", $campaignID, $fpid);
$stmt->execute();
$stmt->bind_result($survey_tag);
while ($stmt->fetch()) {
$tags[] = $survey_tag;
}
$stmt->close();
}
if ($stmt = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE question_weightage > 0 AND responses.response <> 'N/A' and responses.response <> '' AND surveyitems.subcategory LIKE ? and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and assignments.location_id = ? and assignments.status = 'Approved')")) {
foreach ($tags as $tag) {
$tagLike = "%$tag%";
$stmt->bind_param("sss", $tagLike, $campaignID, $location_id);
$stmt->execute();
$stmt->bind_result($totalScoreGained);
$stmt->fetch();
//if($variable === NULL)
array_push($resultsArray, array($tag, $totalScoreGained)); //end while
}
$stmt->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentTagDetractorCount($campaignID, $location_id, $fpid, $db_conx, $detractors)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$totalScoreGained = $totalPossibleScore = $percentage = 0;
$tags = [];
if ($stmt = $db_conx->prepare("SELECT distinct tag FROM survey_tags, campaigns WHERE surveyID = campaigns.survey_id and campaigns.id = ? and survey_tags.id=? order by survey_tags.id ")) {
$stmt->bind_param("ss", $campaignID, $fpid);
$stmt->execute();
$stmt->bind_result($survey_tag);
while ($stmt->fetch()) {
$tags[] = $survey_tag;
}
$stmt->close();
}
if ($stmt = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) AND surveyitems.subcategory LIKE ? and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and assignments.location_id = ? and assignments.status = 'Approved')")) {
foreach ($tags as $tag) {
$tagLike = "%$tag%";
$stmt->bind_param("ssss", $detractors, $tagLike, $campaignID, $location_id);
$stmt->execute();
$stmt->bind_result($totalScoreGained);
$stmt->fetch();
//if($variable === NULL)
array_push($resultsArray, array($tag, $totalScoreGained)); //end while
}
$stmt->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentTagNeutralCount($campaignID, $location_id, $fpid, $db_conx, $neutrals)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$totalScoreGained = $totalPossibleScore = $percentage = 0;
$tags = [];
if ($stmt = $db_conx->prepare("SELECT distinct tag FROM survey_tags, campaigns WHERE surveyID = campaigns.survey_id and campaigns.id = ? and survey_tags.id=? order by survey_tags.id ")) {
$stmt->bind_param("ss", $campaignID, $fpid);
$stmt->execute();
$stmt->bind_result($survey_tag);
while ($stmt->fetch()) {
$tags[] = $survey_tag;
}
$stmt->close();
}
if ($stmt = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) AND surveyitems.subcategory LIKE ? and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and assignments.location_id = ? and assignments.status = 'Approved')")) {
foreach ($tags as $tag) {
$tagLike = "%$tag%";
$stmt->bind_param("ssss", $neutrals, $tagLike, $campaignID, $location_id);
$stmt->execute();
$stmt->bind_result($totalScoreGained);
$stmt->fetch();
//if($variable === NULL)
array_push($resultsArray, array($tag, $totalScoreGained)); //end while
}
$stmt->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentTagPromotorCount($campaignID, $location_id, $fpid, $db_conx, $promotors)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$totalScoreGained = $totalPossibleScore = $percentage = 0;
$tags = [];
if ($stmt = $db_conx->prepare("SELECT distinct tag FROM survey_tags, campaigns WHERE surveyID = campaigns.survey_id and campaigns.id = ? and survey_tags.id = ? order by survey_tags.id ")) {
$stmt->bind_param("ss", $campaignID, $fpid);
$stmt->execute();
$stmt->bind_result($survey_tag);
while ($stmt->fetch()) {
$tags[] = $survey_tag;
}
$stmt->close();
}
if ($stmt = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) AND surveyitems.subcategory LIKE ? and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and assignments.location_id = ? and assignments.status = 'Approved')")) {
foreach ($tags as $tag) {
$tagLike = "%$tag%";
$stmt->bind_param("ssss", $promotors, $tagLike, $campaignID, $location_id);
$stmt->execute();
$stmt->bind_result($totalScoreGained);
$stmt->fetch();
//if($variable === NULL)
array_push($resultsArray, array($tag, $totalScoreGained)); //end while
}
$stmt->close();
} // end if
return array($resultsArray);
}
function reportbuild_questionCount($campaignID, $location_id, $quesid, $db_conx)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$questions = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems where surveyitems.id=?")) {
$stmt->bind_param("s", $quesid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($question_id, $questionName);
while ($stmt->fetch()) {
$questions[$question_id] = $questionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE surveyitems.id=? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' AND responses.response <> '' and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and assignments.location_id = ? and assignments.status = 'Approved')")) {
$stmtsect->bind_param("sss", $quesid, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalCountGained);
while ($stmtsect->fetch()) {
array_push($resultsArray, array($questions[$question_id], $totalCountGained));
} //end while
$stmtsect->close();
} // end if
return array($resultsArray);
}
function reportbuild_questionDetractorCount($campaignID, $location_id, $quesid, $db_conx, $detractors)
{
$resultsDetractorsArray = $questions = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems WHERE surveyitems.id = ?")) {
$stmt->bind_param("s", $quesid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($question_id, $questionName);
while ($stmt->fetch()) {
$questions[$question_id] = $questionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE surveyitems.id=? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and assignments.location_id = ? and assignments.status = 'Approved')")) {
$stmtsect->bind_param("ssss", $quesid, $detractors, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalDetractors);
while ($stmtsect->fetch()) {
array_push($resultsDetractorsArray, array($questions[$question_id], $totalDetractors));
} //end while
$stmtsect->close();
} // end if
return array($resultsDetractorsArray);
}
function reportbuild_questionNeutralCount($campaignID, $location_id, $quesid, $db_conx, $neutrals)
{
$resultsNeutralArray = $questions = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems WHERE surveyitems.id = ?")) {
$stmt->bind_param("s", $quesid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($question_id, $questionName);
while ($stmt->fetch()) {
$questions[$question_id] = $questionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE surveyitems.id = ? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and assignments.location_id = ? and assignments.status = 'Approved')")) {
$stmtsect->bind_param("ssss", $quesid, $neutrals, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalNeutral);
while ($stmtsect->fetch()) {
array_push($resultsNeutralArray, array($questions[$question_id], $totalNeutral));
} //end while
$stmtsect->close();
} // end if
return array($resultsNeutralArray);
}
function reportbuild_questionPromotorCount($campaignID, $location_id, $quesid, $db_conx, $promotors)
{
$resultsPromotorArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$questions = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems WHERE surveyitems.id = ?")) {
$stmt->bind_param("s", $quesid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($question_id, $questionName);
while ($stmt->fetch()) {
$questions[$question_id] = $questionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE surveyitems.id=? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) and responses.assignment_id IN (select id from assignments where find_in_set(assignments.campaign_id,?) and assignments.location_id = ? and assignments.status = 'Approved')")) {
$stmtsect->bind_param("ssss", $quesid, $promotors, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalPromotor);
while ($stmtsect->fetch()) {
array_push($resultsPromotorArray, array($questions[$question_id], $totalPromotor));
} //end while
$stmtsect->close();
} // end if
return array($resultsPromotorArray);
}
function reportbuild_assignmentSectionCountCombined($campaignID, $location_id, $secid, $db_conx)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$sections = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems, campaigns WHERE surveyitems.surveyID = campaigns.survey_id and campaigns.id = ? and surveyitems.item_type = 1 and surveyitems.id=? order by section_number")) {
$stmt->bind_param("ss", $campaignID, $secid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($section_number, $sectionName);
while ($stmt->fetch()) {
$sections[$section_number] = $sectionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from surveyitems,campaigns,responses WHERE responses.question_id = surveyitems.id and surveyitems.section_number=? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.campaign_id = ? and right(responses.assignment_id,3) in (select id from locations where FIND_IN_SET(location_grouping2,?)) GROUP BY section_number")) {
$stmtsect->bind_param("sss", $secid, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalCountGained);
while ($stmtsect->fetch()) {
array_push($resultsArray, array($sections[$section_number], $totalCountGained));
} //end while
$stmtsect->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentSectionDetractorCountCombined($campaignID, $location_id, $secid, $db_conx, $detractors)
{
$resultsDetractorsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems, campaigns WHERE surveyitems.surveyID = campaigns.survey_id and campaigns.id = ? and surveyitems.item_type = 1 and surveyitems.id = ? order by section_number")) {
$stmt->bind_param("ss", $campaignID, $secid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($section_number, $sectionName);
while ($stmt->fetch()) {
$sections[$section_number] = $sectionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from surveyitems,campaigns,responses WHERE responses.question_id = surveyitems.id and surveyitems.section_number=? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) and responses.campaign_id = ? and right(responses.assignment_id,3) in (select id from locations where FIND_IN_SET(location_grouping2,?)) GROUP BY section_number")) {
$stmtsect->bind_param("ssss", $secid, $detractors, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalDetractors);
while ($stmtsect->fetch()) {
array_push($resultsDetractorsArray, array($sections[$section_number], $totalDetractors));
} //end while
$stmtsect->close();
} // end if
return array($resultsDetractorsArray);
}
function reportbuild_assignmentSectionNeutralCountCombined($campaignID, $location_id, $secid, $db_conx, $neutrals)
{
$resultsNeutralArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$sections = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems, campaigns WHERE surveyitems.surveyID = campaigns.survey_id and campaigns.id = ? and surveyitems.item_type = 1 and surveyitems.id=? order by section_number")) {
$stmt->bind_param("ss", $campaignID, $secid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($section_number, $sectionName);
while ($stmt->fetch()) {
$sections[$section_number] = $sectionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from surveyitems,campaigns,responses WHERE responses.question_id = surveyitems.id and surveyitems.section_number=? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) and responses.campaign_id = ? and right(responses.assignment_id,3) in (select id from locations where FIND_IN_SET(location_grouping2,?)) GROUP BY section_number")) {
$stmtsect->bind_param("ssss", $secid, $neutrals, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalNeutral);
while ($stmtsect->fetch()) {
array_push($resultsNeutralArray, array($sections[$section_number], $totalNeutral));
} //end while
$stmtsect->close();
} // end if
return array($resultsNeutralArray);
}
function reportbuild_assignmentSectionPromotorCountCombined($campaignID, $location_id, $secid, $db_conx, $promotors)
{
$resultsPromotorArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$sections = [];
if ($stmt = $db_conx->prepare("SELECT surveyitems.id, value FROM surveyitems, campaigns WHERE surveyitems.surveyID = campaigns.survey_id and campaigns.id = ? and surveyitems.item_type = 1 and surveyitems.id=? order by section_number")) {
$stmt->bind_param("ss", $campaignID, $secid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($section_number, $sectionName);
while ($stmt->fetch()) {
$sections[$section_number] = $sectionName;
}
$stmt->close();
}
if ($stmtsect = $db_conx->prepare("SELECT count(surveyitems.id) from surveyitems,campaigns,responses WHERE responses.question_id = surveyitems.id and surveyitems.section_number=? AND surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) and responses.campaign_id = ? and right(responses.assignment_id,3) in (select id from locations where FIND_IN_SET(location_grouping2,?)) GROUP BY section_number")) {
$stmtsect->bind_param("ssss", $secid, $promotors, $campaignID, $location_id);
$stmtsect->execute();
$stmtsect->store_result();
$stmtsect->bind_result($totalPromotor);
while ($stmtsect->fetch()) {
array_push($resultsPromotorArray, array($sections[$section_number], $totalPromotor));
} //end while
$stmtsect->close();
} // end if
return array($resultsPromotorArray);
}
function reportbuild_assignmentTagCountCombined($campaignID, $location_id, $fpid, $db_conx)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$totalScoreGained = $totalPossibleScore = $percentage = 0;
$tags = [];
if ($stmt = $db_conx->prepare("SELECT distinct tag FROM survey_tags, campaigns WHERE surveyID = campaigns.survey_id and campaigns.id = ? and survey_tags.id=? order by survey_tags.id ")) {
$stmt->bind_param("ss", $campaignID, $fpid);
$stmt->execute();
$stmt->bind_result($survey_tag);
while ($stmt->fetch()) {
$tags[] = $survey_tag;
}
$stmt->close();
}
if ($stmt = $db_conx->prepare("SELECT count(surveyitems.id) from surveyitems,campaigns,responses WHERE responses.question_id = surveyitems.id AND question_weightage > 0 AND responses.response <> 'N/A' AND surveyitems.subcategory LIKE ? AND responses.campaign_id = ? and right(responses.assignment_id,3) in (select id from locations where FIND_IN_SET(location_grouping2,?))")) {
foreach ($tags as $tag) {
$tagLike = "%$tag%";
$stmt->bind_param("sss", $tagLike, $campaignID, $location_id);
$stmt->execute();
$stmt->bind_result($totalScoreGained);
$stmt->fetch();
//if($variable === NULL)
array_push($resultsArray, array($tag, $totalScoreGained)); //end while
}
$stmt->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentTagDetractorCountCombined($campaignID, $location_id, $fpid, $db_conx, $detractors)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$totalScoreGained = $totalPossibleScore = $percentage = 0;
$tags = [];
if ($stmt = $db_conx->prepare("SELECT distinct tag FROM survey_tags, campaigns WHERE surveyID = campaigns.survey_id and campaigns.id = ? and survey_tags.id=? order by survey_tags.id ")) {
$stmt->bind_param("ss", $campaignID, $fpid);
$stmt->execute();
$stmt->bind_result($survey_tag);
while ($stmt->fetch()) {
$tags[] = $survey_tag;
}
$stmt->close();
}
if ($stmt = $db_conx->prepare("SELECT count(surveyitems.id) from surveyitems,campaigns,responses WHERE responses.question_id = surveyitems.id AND question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) AND surveyitems.subcategory LIKE ? AND responses.campaign_id = ? and right(responses.assignment_id,3) in (select id from locations where FIND_IN_SET(location_grouping2,?))")) {
foreach ($tags as $tag) {
$tagLike = "%$tag%";
$stmt->bind_param("ssss", $detractors, $tagLike, $campaignID, $location_id);
$stmt->execute();
$stmt->bind_result($totalScoreGained);
$stmt->fetch();
//if($variable === NULL)
array_push($resultsArray, array($tag, $totalScoreGained)); //end while
}
$stmt->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentTagNeutralCountCombined($campaignID, $location_id, $fpid, $db_conx, $neutrals)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$totalScoreGained = $totalPossibleScore = $percentage = 0;
$tags = [];
if ($stmt = $db_conx->prepare("SELECT distinct tag FROM survey_tags, campaigns WHERE surveyID = campaigns.survey_id and campaigns.id = ? and survey_tags.id=? order by survey_tags.id ")) {
$stmt->bind_param("ss", $campaignID, $fpid);
$stmt->execute();
$stmt->bind_result($survey_tag);
while ($stmt->fetch()) {
$tags[] = $survey_tag;
}
$stmt->close();
}
if ($stmt = $db_conx->prepare("SELECT count(surveyitems.id) from surveyitems,campaigns,responses WHERE responses.question_id = surveyitems.id AND question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) AND surveyitems.subcategory LIKE ? AND responses.campaign_id = ? and right(responses.assignment_id,3) in (select id from locations where FIND_IN_SET(location_grouping2,?))")) {
foreach ($tags as $tag) {
$tagLike = "%$tag%";
$stmt->bind_param("ssss", $neutrals, $tagLike, $campaignID, $location_id);
$stmt->execute();
$stmt->bind_result($totalScoreGained);
$stmt->fetch();
//if($variable === NULL)
array_push($resultsArray, array($tag, $totalScoreGained)); //end while
}
$stmt->close();
} // end if
return array($resultsArray);
}
function reportbuild_assignmentTagPromotorCountCombined($campaignID, $location_id, $fpid, $db_conx, $promotors)
{
$resultsArray = [];
//SELECT section_number, value FROM surveyitems, campaigns WHERE campaigns.id = 1 and campaigns.survey_id = surveyitems.surveyID and surveyitems.item_type = 1 order by section_number
$totalScoreGained = $totalPossibleScore = $percentage = 0;
$tags = [];
if ($stmt = $db_conx->prepare("SELECT distinct tag FROM survey_tags, campaigns WHERE surveyID = campaigns.survey_id and campaigns.id = ? and survey_tags.id = ? order by survey_tags.id ")) {
$stmt->bind_param("ss", $campaignID, $fpid);
$stmt->execute();
$stmt->bind_result($survey_tag);
while ($stmt->fetch()) {
$tags[] = $survey_tag;
}
$stmt->close();
}
if ($stmt = $db_conx->prepare("SELECT count(surveyitems.id) from surveyitems,campaigns,responses WHERE responses.question_id = surveyitems.id AND question_weightage > 0 AND responses.response <> 'N/A' and (find_in_set(round(responses.score), ? )) AND surveyitems.subcategory LIKE ? AND responses.campaign_id = ? and right(responses.assignment_id,3) in (select id from locations where FIND_IN_SET(location_grouping2,?))")) {
foreach ($tags as $tag) {
$tagLike = "%$tag%";
$stmt->bind_param("ssss", $promotors, $tagLike, $campaignID, $location_id);
$stmt->execute();
$stmt->bind_result($totalScoreGained);
$stmt->fetch();
//if($variable === NULL)
array_push($resultsArray, array($tag, $totalScoreGained)); //end while
}
$stmt->close();
} // end if
return array($resultsArray);
}
function reportbuild_campaignLocationLeaderboard($campaignID, $db_conx, $locations = [])
{
$storePassCount = 0;
$storeLeaderboard = '';
$rank = 0;
if ($stmt = $db_conx->prepare("SELECT location_id, location_name, round(totalScore), assignPassMark FROM assignments, locations WHERE location_id = locations.id AND campaign_id = ? and status = 'Approved' group by locations.id order by totalScore desc")) {
$stmt->bind_param("s", $campaignID);
$stmt->execute();
$stmt->bind_result($location_id, $locationName, $totalscore, $assignPassMark);
$rank = 1;
while ($stmt->fetch()) {
if ((int)$totalscore >= (int)$assignPassMark) {
$storePassCount++;
}
if (in_array($location_id, $locations)) {
$storeLeaderboard .= '<tr style="background-color:green; color:white">
<td>' . $rank . '</td>
<td>' . $locationName . '</td>
<td>' . $totalscore . '%</td>
</tr>';
} else {
$storeLeaderboard .= '<tr>
<td>' . $rank . '</td>
<td>' . $locationName . '</td>
<td>' . $totalscore . '%</td>
</tr>';
}
$rank++;
}
}
return array($storePassCount, $storeLeaderboard);
}
$sampleCombineTable = "1";
$combine_list = [
"Points Achieved",
"NPS Summary",
"Final Score"
];
switch ($tableView) {
case "Campaign":
if ($periodCombine == "1" && in_array($detailView, $combine_list) !== false) {
$combineCount = count($selectedCampaign);
$mn_combine = 1;
switch ($reportType) {
case "LineItem":
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$tablename = "displaytab_combined";
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$periodspan .= ',' . $mc_period[0];
if ($mn_combine == 1) {
$firstPeriod = trim($mc_period[1], '-');
}
if ($mn_combine == $combineCount) {
$lastPeriod = trim($mc_period[1], '-');
}
++$mn_combine;
}
$periodspan = ltrim($periodspan, ',');
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $firstPeriod . ' : ' . $lastPeriod . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$mc_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc_sample[1] . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments WHERE FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
$assignment_list .= $assignId . ',';
}
$stmtassign->close();
}
}
$assignment_list = trim($assignment_list, ',');
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($selectedSample2 as $mc_sample0) {
$mc_sample = explode('|', $mc_sample0);
if ($stmt = $db_conx->prepare("SELECT s.value,sum(responses.score) FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE s.question_weightage > 0 AND responses.response <> 'N/A' and FIND_IN_SET(responses.assignment_id, ?) and FIND_IN_SET(responses.question_id, ?) group by question_id")) {
$stmt->bind_param("ss", $assignment_list, $selectedItems_list);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($selectedSample2 as $mc_sample0) {
$mc_sample = explode('|', $mc_sample0);
$finalScores = reportbuild_assignmentScoresByLocation($mc_sample[0], $assignment_list, $selectedItems_list, $db_conx);
foreach ($finalScores as $mc_finalscore) {
foreach ($mc_finalscore as $finscore) {
$finalSampleList[$finscore[0]][] = $finscore[3];
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if (substr($detailView, 0, 3) == "NPS" || substr($detailView, 0, 3) == "ESS") {
//$final_tableheader = $nps_table_headers;
} else {
$final_tableheader = $table_headers;
$final_table .= '<table style="border:1px solid black" class="' . $tablename . ' table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $final_tableheader . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
break;
case "Section":
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$tablename = "displaytab";
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$periodspan .= ',' . $mc_period[0];
if ($mn_combine == 1) {
$firstPeriod = trim($mc_period[1], '-');
}
if ($mn_combine == $combineCount) {
$lastPeriod = trim($mc_period[1], '-');
}
++$mn_combine;
}
$periodspan = ltrim($periodspan, ',');
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $firstPeriod . ' : ' . $lastPeriod . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id where surveyitems.surveyId = ? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where campaign_id in ($periodspan) and status = 'Approved')")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
if ($stmt = $db_conx->prepare("SELECT value FROM surveyitems where FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$section_names = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($section_names as $section) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) sectionscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id WHERE surveyitems.section_number=? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where location_id = ? and campaign_id in ($periodspan) and status = 'Approved') GROUP BY section_number")) {
$stmt->bind_param("ss", $section['id'], $mc_location);
$stmt->execute();
$stmt->bind_result($sectionscore);
while ($stmt->fetch()) {
$finalSampleList[$section['value']][] = ceil($sectionscore / $totscore);
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT id, value FROM surveyitems where surveyid=? and item_type=1 and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$section_names = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($section_names as $section) {
if ($stmt = $db_conx->prepare("SELECT ifnull(round(SUM( responses.score)/SUM(question_weightage) * 100 ),0) sectionscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id left outer join assignments on assignments.id=responses.assignment_id WHERE surveyitems.section_number=? and question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where location_id = ? and campaign_id in ($periodspan) and status = 'Approved') GROUP BY section_number")) {
$stmt->bind_param("ss", $section['id'], $mc_location);
$stmt->execute();
$stmt->bind_result($sectionscore);
while ($stmt->fetch()) {
$finalSampleList[$section['value']][] = $sectionscore;
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if (substr($detailView, 0, 3) == "NPS" || substr($detailView, 0, 3) == "ESS") {
//$final_tableheader = $nps_table_headers;
} else {
$final_tableheader = $table_headers;
$final_table .= '<table style="border:1px solid black" class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
<th colspan="10">Combined</th>
</tr>
<tr>
' . $final_tableheader . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
break;
case "FocalPoints":
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$tablename = "displaytab";
if ($stmtnps = $db_conx->prepare("SELECT projects.projectdetracts,projects.projectneutrals,projects.projectpromotors from projects where id = ?")) {
$stmtnps->bind_param("s", $reportProject);
$stmtnps->execute();
$stmtnps->store_result();
$stmtnps->bind_result($detractors, $neutrals, $promotors);
$stmtnps->fetch();
$stmtnps->close();
}
if ($detailView == "NPS Summary" || $detailView == "ESS Summary") {
$nps_table_headers = '<th>Total Responses</th><th>Detractors</th><th>Neutral</th><th>Promotors</th><th>NPS</th>';
} else if ($detailView == "NPS Detail" || $detailView == "ESS Detail") {
$nps_table_headers = '<th>Total Responses</th><th>' . $detractors . '</th><th>' . $neutrals . '</th><th>' . $promotors . '</th><th>NPS</th>';
}
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$periodspan .= ',' . $mc_period[0];
if ($mn_combine == 1) {
$firstPeriod = trim($mc_period[1], '-');
}
if ($mn_combine == $combineCount) {
$lastPeriod = trim($mc_period[1], '-');
}
++$mn_combine;
}
$periodspan = ltrim($periodspan, ',');
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $firstPeriod . ' : ' . $lastPeriod . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses where responses.survey_id = ? and responses.assignment_id in (select id from assignments where campaign_id in ($periodspan))")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
if ($stmt = $db_conx->prepare("SELECT id, tag FROM survey_tags where surveyid=? and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$fpoints_attached = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($fpoints_attached as $fpoints) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) fpointscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id left outer join assignments on assignments.id=responses.assignment_id WHERE question_weightage > 0 and find_in_set (?,surveyitems.subcategory) AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where location_id = ? and campaign_id in ($periodspan) and status = 'Approved')")) {
$stmt->bind_param("ss", $fpoints['tag'], $mc_location);
$stmt->execute();
$stmt->bind_result($fpointscore);
while ($stmt->fetch()) {
$finalSampleList[$fpoints['tag']][] = ceil($fpointscore / $totscore);
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT id, tag FROM survey_tags where surveyid=? and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$fpoints_attached = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($fpoints_attached as $fpoint) {
if ($stmt = $db_conx->prepare("SELECT ifnull(round(SUM( responses.score)/SUM(question_weightage) * 100 ),0) fpointscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id left outer join assignments on assignments.id=responses.assignment_id WHERE question_weightage > 0 and find_in_set (?,surveyitems.subcategory) AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where location_id = ? and campaign_id in ($periodspan) and status = 'Approved')")) {
$stmt->bind_param("ss", $fpoint['tag'], $mc_location);
$stmt->execute();
$stmt->bind_result($fpointscore);
while ($stmt->fetch()) {
$finalSampleList[$fpoint['tag']][] = $fpointscore;
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if (substr($detailView, 0, 3) == "NPS" || substr($detailView, 0, 3) == "ESS") {
//$final_tableheader = $nps_table_headers;
} else {
$final_tableheader = $table_headers;
$final_table .= '<table style="border:1px solid black" class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
<th colspan="10">Combined</th>
</tr>
<tr>
' . $final_tableheader . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
break;
}
}
switch ($reportType) {
case "LineItem":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$mc_period = explode('|', $mc_period);
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . trim($mc_period[1], '-') . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$mc_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc_sample[1] . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
++$mc_count;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value,responses.score FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE s.question_weightage > 0 AND responses.response <> 'N/A' and FIND_IN_SET(question_id,?) and assignment_id = ? order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
++$mc_count2;
}
$stmt->close();
}
}
$mc_count2 = 0;
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
$finalScores = reportbuild_assignmentScores($mc_list3, $selectedItems_list, $db_conx);
foreach ($finalScores as $mc_finalscore) {
foreach ($mc_finalscore as $finscore) {
$finalSampleList[$finscore[0]][] = $finscore[3];
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Heat Map":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
$sectionScores = reportbuild_LocationScores($mc_list2, $selectedItems_list, $db_conx);
foreach ($sectionScores as $mc_finalscore) {
foreach ($mc_finalscore as $finscore) {
$finalSampleList[$finscore[0]][] = $finscore[3];
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
if ($a0_tablefin[$fullcount] >= 80) {
$bg_colour = 'style="background-color:green; color:white;"';
} elseif ($a0_tablefin[$fullcount] <= 80 && $a0_tablefin[$fullcount] >= 60) {
$bg_colour = 'style="background-color:yellow; color:white;"';
} else {
$bg_colour = 'style="background-color:red; color:white;"';
}
$newTableRows .= '<td ' . $bg_colour . '>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Comments":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value,remarks FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? and remarks != '' order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Uploads":
break;
case "Zero Rated*":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? and s.question_weightage = 0 order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table style="border:1px solid black" class="displaytab table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
break;
case "Section":
$section_names = [];
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses where survey_id = ? and FIND_IN_SET(responses.assignment_id, ?)")) {
$stmt->bind_param("ss", $surveyId, $reportAssiggments);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
if ($stmt = $db_conx->prepare("SELECT id,value FROM surveyitems where surveyid=? and item_type = 1 and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$section_names = $result->fetch_all(MYSQLI_ASSOC);
//$section_name = $section_names[0]['value'];
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($section_names as $section) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) sectionscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id WHERE surveyitems.section_number=? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where FIND_IN_SET(id, ?) AND location_id = ?) GROUP BY section_number")) {
$stmt->bind_param("sss", $section['id'], $reportAssiggments, $mc_location);
$stmt->execute();
$stmt->bind_result($sectionscore);
while ($stmt->fetch()) {
$finalSampleList[$section['value']][] = ceil($sectionscore / $totscore);
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT id, value FROM surveyitems where surveyid=? and item_type=1 and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$section_names = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($section_names as $section) {
if ($stmt = $db_conx->prepare("SELECT ifnull(round(SUM( responses.score)/SUM(question_weightage) * 100 ),0) sectionscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id WHERE surveyitems.section_number=? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where FIND_IN_SET(id, ?) AND location_id = ?) GROUP BY section_number")) {
$stmt->bind_param("sss", $section['id'], $reportAssiggments, $mc_location);
$stmt->execute();
$stmt->bind_result($sectionscore);
while ($stmt->fetch()) {
$finalSampleList[$section['value']][] = $sectionscore;
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table style="border:1px solid black" class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
break;
case "FocalPoints":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses where survey_id = ? and FIND_IN_SET(responses.assignment_id, ?)")) {
$stmt->bind_param("ss", $surveyId, $reportAssiggments);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
$stmt->close();
}
if ($stmt = $db_conx->prepare(" SELECT tag FROM survey_tags where surveyid=? and FIND_IN_SET(id,?) ")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$fpoints_attached = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
}
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($fpoints_attached as $fpoints) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) fpointscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id left outer join assignments on assignments.id=responses.assignment_id WHERE find_in_set (?,surveyitems.subcategory) AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmt->bind_param("sss", $fpoints['tag'], $reportAssiggments, $mc_location);
$stmt->execute();
$stmt->bind_result($fpointscore);
while ($stmt->fetch()) {
$finalSampleList[$fpoints['tag']][] = ceil($fpointscore / $totscore);
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT id, tag FROM survey_tags where surveyid=? and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$fpoints_attached = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($fpoints_attached as $fpoint) {
if ($stmt = $db_conx->prepare("SELECT ifnull(round(SUM( responses.score)/SUM(question_weightage) * 100 ),0) fpointscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id left outer join assignments on assignments.id=responses.assignment_id WHERE find_in_set (?,surveyitems.subcategory) AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where FIND_IN_SET(id, ?) AND location_id = ?)")) {
$stmt->bind_param("sss", $fpoint['tag'], $reportAssiggments, $mc_location);
$stmt->execute();
$stmt->bind_result($fpointscore);
while ($stmt->fetch()) {
$finalSampleList[$fpoint['tag']][] = $fpointscore;
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
break;
case "Summary":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$mc_period = explode('|', $mc_period);
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $mc_period[1] . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$mc_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc_sample[1] . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
++$mc_count;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE s.item_type='16' and assignment_id = ? ")) {
$stmt->bind_param("s", $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
$final_table .= '<table class="displaytab table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
break;
case "Complete":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$selectedItems_list = implode(',', $selectedItems);
$mc_period = explode('|', $mc_period);
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_0sample) {
$mc_sample = explode('|', $mc_0sample);
$table_headers = '<th style="display:none;">itemrank</th>' . '<th> ' . trim($mc_period[1], '-') . ' | ' . $mc_sample[1] . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
++$mc_count;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$mc_actualfinaltable = $newTableRows1 = "";
$table_headers .= '<th style="display:none;">Section</th>';
$table_headers .= '<th>Focal Point</th>';
$table_headers .= '<th>Section/Query</th>';
$table_headers .= '<th>Answer</th>';
$table_headers .= '<th>Pts Achvd</th>';
$table_headers .= '<th>Score</th>';
$table_headers .= '<th>Comments</th>';
$table_headers .= '<th>Uploads</th>';
foreach ($sampleList as $mc_sampleList) {
foreach ($mc_sampleList as $mc_sampleList2) {
//$finalSampleList = array();
$newTableRows1 = "";
if ($stmtsections = $db_conx->prepare("SELECT id, value FROM surveyitems WHERE surveyID = ? AND item_type = '1' order by reportRank")) {
$stmtsections->bind_param("s", $surveyId);
$stmtsections->execute();
$stmtsections->store_result();
$stmtsections->bind_result($section_num, $sectionvalue);
while ($stmtsections->fetch()) {
$mc_count2 = 0;
$sectiontd = '<td style="display:none;">' . $sectionvalue . '</td>';
if ($stmt = $db_conx->prepare("SELECT s.subcategory,s.value,case when responses.response LIKE '%img%' then '' else responses.response end as response,responses.score points,round((responses.score/question_weightage) * 100 ), responses.remarks,case when responses.response LIKE '%img%' then responses.response end as uploads FROM responses left outer join surveyitems s on responses.question_id = s.id where responses.assignment_id = ? and s.item_type <> '1' and s.section_number = ? order by displayRank")) {
$stmt->bind_param("ss", $mc_sampleList2, $section_num);
$stmt->execute();
$stmt->bind_result($focalpoint, $question, $response, $points, $ques_score, $ques_remarks, $uploads);
while ($stmt->fetch()) {
$finalSampleList[$mc_sampleList2][] = $focalpoint . '|' . $question . '|' . $response . '|' . $points . '|' . $ques_score . '|' . $ques_remarks . '|' . $uploads;
}
$stmt->close();
}
}
$stmtsections->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = 7;
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td><td></td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$a0_tabledets = explode('|', $a0_tablefin[$fullcount]);
foreach ($a0_tabledets as $mc_table) {
$newTableRows .= '<td>' . $mc_table . '</td>';
++$countbreak;
}
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $sectiontd . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
if (!empty($finalSampleList)) {
$final_table .= '<table class="completetable table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
case "Complete(Temp)":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$selectedItems_list = implode(',', $selectedItems);
$mc_period = explode('|', $mc_period);
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_0sample) {
$mc_sample = explode('|', $mc_0sample);
$table_headers = '<th> ' . trim($mc_period[1], '-') . ' | ' . $mc_sample[1] . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments,$mc_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
++$mc_count;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$mc_actualfinaltable = $newTableRows1 = "";
$table_headers .= '<th style="display:none;">Section</th>';
$table_headers .= '<th>What location are you based in?</th>';
$table_headers .= '<th>First Name</th>';
$table_headers .= '<th>Last Name</th>';
$table_headers .= '<th>What is your food handlers permit/receipt number?</th>';
$table_headers .= '<th>Expiry Date?</th>';
$table_headers .= '<th>Please take a head shot pic/selfie</th>';
foreach ($sampleList as $mc_sampleList) {
foreach ($mc_sampleList as $mc_sampleList2) {
//$finalSampleList = array();
$newTableRows1 = "";
if ($stmtsections = $db_conx->prepare("SELECT id, value FROM surveyitems WHERE surveyID = ? AND item_type = '1' order by reportRank")) {
$stmtsections->bind_param("s", $surveyId);
$stmtsections->execute();
$stmtsections->store_result();
$stmtsections->bind_result($section_num, $sectionvalue);
while ($stmtsections->fetch()) {
$mc_count2 = 0;
$sectiontd = '<td style="display:none;">' . $sectionvalue . '</td>';
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id where responses.assignment_id = ? and s.item_type <> '1' and s.section_number = ? order by displayRank")) {
$stmt->bind_param("ss", $mc_sampleList2, $section_num);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$mc_sampleList2][] = $response;
}
$stmt->close();
}
}
$stmtsections->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = 6;
$first_row = 1;
foreach ($finalSampleList as $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td><td></td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
++$countcurr;
}
if (!empty($finalSampleList)) {
$final_table .= '<table class="completetable table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
case "Leaderboard":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$selectedItems_list = implode(',', $selectedItems);
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . $mc_period[1] . ' </th>';
foreach ($selectedSample2 as $mc_sample) {
$mc_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc_sample[1] . ' </th>';
if ($stmt = $db_conx->prepare("SELECT location_id FROM assignments where project_id = ? and campaign_id = ? and status ='Approved' and location_id = ?")) {
$stmt->bind_param("sss", $reportProject, $mc_period[0], $mc_sample[0]);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($location_id);
$mc_count = 0;
while ($stmt->fetch()) {
$sampleList[] = $location_id;
++$mc_count;
}
$stmt->close();
}
}
list($locationPassCount, $locationLeaderboard) = reportbuild_campaignLocationLeaderboard($mc_period[0], $db_conx, $sampleList);
switch ($detailView) {
case "Raw Response":
$table_headers = '<th>Rank</th><th>Location Name</th><th>Score</th>';
break;
}
$final_table .= '<table style="border:1px solid black" class="displaytab table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $locationLeaderboard . '
</tbody>
</table>';
}
break;
}
break;
case "Location":
switch ($reportType) {
case "LineItem":
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = $sampleCombine_type = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $mc0_sample[1] . ' </th>';
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc0_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
while ($stmtassign->fetch()) {
$sampleList[$mc0_sample[1]][] = $assignId;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list3);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare("SELECT s.value,responses.score FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list3);
$stmt->execute();
$stmt->bind_result($question, $score);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $score;
++$mc_count2;
}
$stmt->close();
}
}
$mc_count2 = 0;
++$mc_qcount;
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = $sectionScores = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
$sectionScores = reportbuild_assignmentScores($mc_list3, $selectedItems_list, $db_conx);
foreach ($sectionScores as $mc_secscore) {
foreach ($mc_secscore as $sec) {
$finalSampleList[$sec[0]][] = $sec[3];
++$mc_count2;
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Heat Map":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
$sectionScores = reportbuild_LocationScores($mc_list3, $selectedItems_list, $db_conx);
foreach ($sectionScores as $mc_secscore) {
foreach ($mc_secscore as $sec) {
$finalSampleList[$sec[0]][] = $sec[3];
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
if ($a0_tablefin[$fullcount] >= 80) {
$bg_colour = 'class="heat-green"';
} elseif ($a0_tablefin[$fullcount] <= 80 && $a0_tablefin[$fullcount] >= 60) {
$bg_colour = 'class="heat-yellow"';
} else {
$bg_colour = 'class="heat-red"';
}
$newTableRows .= '<td ' . $bg_colour . '>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Comments":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare("SELECT s.value,remarks FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? and remarks != '' order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list3);
$stmt->execute();
$stmt->bind_result($question, $remarks);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $remarks;
++$mc_count2;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Uploads":
break;
case "Zero Rated*":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? and s.question_weightage = 0 order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list3);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table class="displaytab table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
break;
case "Section":
if (!empty($selectedSample)) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
if (isset($selectedLocation[0])) {
$selectedSample2 = $selectedLocation;
} else {
$a0_level = explode(',', $selectedSample_list);
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
foreach ($a0_level as $mc_level) {
$stmtassign->bind_param("ss", $companyID, $mc_level);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = $assignment_list = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc0_sample[1] . ' </th>';
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . ltrim($mc_period[1], '-') . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) { //getting all the assignments per location
$stmtassign->bind_param("ss", $reportAssiggments, $mc0_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc0_sample[1]][] = $assignId;
$assignment_list .= $assignId . ',';
}
$stmtassign->close();
}
}
$assignment_list = trim($assignment_list, ',');
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses where survey_id = ? and assignment_id in ($assignment_list)")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
foreach ($sampleList as $mc_list => $mc_list2) { // loop through the location to find what questions are in what sections
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare("SELECT id,value FROM surveyitems where surveyid=? and item_type=1 ")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$section_names = $result->fetch_all(MYSQLI_ASSOC);
}
foreach ($section_names as $section) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) sectionscore FROM responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE responses.survey_id=? and responses.response <> 'N/A' and responses.assignment_id=? and surveyitems.section_number =? and surveyitems.question_weightage > 0 GROUP BY section_number")) {
$stmt->bind_param("sss", $surveyId, $mc_list3, $section['id']);
$stmt->execute();
$stmt->bind_result($sectionscore);
while ($stmt->fetch()) {
$finalSampleList[$section['value']][] = ceil($sectionscore / $totscore);
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = $sectionScores = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($selectedItems as $mc_lineitems) {
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
$sectionScores = reportbuild_assignmentSectionScores($mc_list3, $mc_lineitems, $db_conx);
foreach ($sectionScores as $mc_secscore) {
foreach ($mc_secscore as $sec) {
$finalSampleList[$sec[0]][] = $sec[3];
}
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
case "FocalPoints":
if (!empty($selectedSample)) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc0_sample[1] . ' </th>';
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc0_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc0_sample[1]][] = $assignId;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses where survey_id = ? ")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
foreach ($sampleList as $mc_list => $mc_list2) { // loop through the location to find what questions are in what sections
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare(" SELECT tag FROM survey_tags where surveyid=?")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$fpoints_attached = $result->fetch_all(MYSQLI_ASSOC);
}
foreach ($fpoints_attached as $fpoint) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) fpointscore FROM surveyitems s left outer join responses on responses.question_id = s.id WHERE responses.survey_id=? and find_in_set (?,s.subcategory) and responses.assignment_id=?")) {
$stmt->bind_param("sss", $surveyId, $fpoint['tag'], $mc_list3);
$stmt->execute();
$stmt->bind_result($fpointscore);
$all_respone = "";
while ($stmt->fetch()) {
$finalSampleList[$fpoint['tag']][] = ceil($fpointscore / $totscore);
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = $sectionScores = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($selectedItems as $mc_lineitems) {
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
$tagScores = reportbuild_assignmentTagScores($mc_list3, $final_location, $mc_lineitems, $db_conx);
foreach ($tagScores as $mc_fpscore) {
foreach ($mc_fpscore as $fp) {
$finalSampleList[$fp[0]][] = $fp[3];
}
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
case "Summary":
if (!empty($selectedSample)) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $mc0_sample[1] . ' </th>';
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) { //getting all the assignments per location
$stmtassign->bind_param("ss", $reportAssiggments, $mc0_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value, responses.response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE s.item_type='16' and assignment_id = ? ")) {
$stmt->bind_param("s", $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td style="color:white;">' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
$final_table .= '<table class="displaytab table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
break;
case "Complete":
if (!empty($selectedSample)) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$mc_period = explode('|', $mc_period);
$table_headers .= '<th style="display:none;">itemrank</th>';
$table_headers .= '<th> ' . $mc0_sample[1] . ' : ' . trim($mc_period[1], '-') . ' </th>';
$table_headers .= '<th style="display:none;">Section</th>';
$table_headers .= '<th>Focal Point</th>';
$table_headers .= '<th>Section/Query</th>';
$table_headers .= '<th>Answer</th>';
$table_headers .= '<th>Pts Achvd</th>';
$table_headers .= '<th>Score</th>';
$table_headers .= '<th>Comments</th>';
$table_headers .= '<th>Uploads</th>';
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
if ($stmtsections = $db_conx->prepare("SELECT id, value FROM surveyitems WHERE surveyID = ? AND item_type = '1' order by reportRank")) {
$stmtsections->bind_param("s", $surveyId);
$stmtsections->execute();
$stmtsections->store_result();
$stmtsections->bind_result($section_num, $sectionvalue);
while ($stmtsections->fetch()) {
$mc_count2 = 0;
$finalSampleList = array();
$sectiontd = '<td style="display:none;">' . $sectionvalue . '</td>';
if ($stmt = $db_conx->prepare("SELECT s.subcategory,s.value,responses.response,responses.score points,round((responses.score/question_weightage) * 100 ), responses.remarks,'' uploads FROM responses left outer join surveyitems s on responses.question_id = s.id left outer join assignments on responses.assignment_id=assignments.id left outer join locations on locations.id=assignments.location_id WHERE FIND_IN_SET(assignments.id, ?) and assignments.location_id=? and s.item_type<>'1' and s.section_number=? order by displayRank")) {
$stmt->bind_param("sss", $reportAssiggments, $mc0_sample[0], $section_num);
$stmt->execute();
$stmt->bind_result($focalpoint, $question, $response, $points, $ques_score, $ques_remarks, $uploads);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $focalpoint . '|' . $question . '|' . $response . '|' . $points . '|' . $ques_score . '|' . $ques_remarks . '|' . $uploads;
}
$stmt->close();
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = 7;
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td></td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$a0_tabledets = explode('|', $a0_tablefin[$fullcount]);
foreach ($a0_tabledets as $mc_table) {
$newTableRows .= '<td>' . $mc_table . '</td>';
++$countbreak;
}
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $sectiontd . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
} }
$stmtsections->close();
}
break;
}
$final_table .= '<table class="completetable table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
break;
case "DataSet":
if ($columnView == "Campaign") {
if ($periodCombine == "1" && in_array($detailView, $combine_list) !== false) {
$combineCount = count($selectedCampaign);
$mn_combine = 1;
switch ($reportType) {
case "LineItem":
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$tablename = "displaytab_combined";
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$periodspan .= ',' . $mc_period[0];
if ($mn_combine == 1) {
$firstPeriod = trim($mc_period[1], '-');
}
if ($mn_combine == $combineCount) {
$lastPeriod = trim($mc_period[1], '-');
}
++$mn_combine;
}
$periodspan = ltrim($periodspan, ',');
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $firstPeriod . ' : ' . $lastPeriod . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$mc_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc_sample[1] . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
$assignment_list .= $assignId . ',';
++$mc_count;
}
$stmtassign->close();
}
}
$assignment_list = trim($assignment_list, ',');
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($selectedSample2 as $mc_sample0) {
$mc_sample = explode('|', $mc_sample0);
if ($stmt = $db_conx->prepare("SELECT s.value,sum(responses.score) FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE s.question_weightage > 0 AND responses.response <> 'N/A' and FIND_IN_SET(question_id,?) and responses.assignment_id in (select id from assignments where FIND_IN_SET(id, ?) AND location_id = ? group by question_id")) {
$stmt->bind_param("sss", $selectedItems_list,$reportAssiggments, $mc_sample[0]);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($selectedSample2 as $mc_sample0) {
$mc_sample = explode('|', $mc_sample0);
$finalScores = reportbuild_assignmentScoresByLocation($mc_sample[0], $assignment_list, $selectedItems_list, $db_conx);
foreach ($finalScores as $mc_finalscore) {
foreach ($mc_finalscore as $finscore) {
$finalSampleList[$finscore[0]][] = $finscore[3];
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if (substr($detailView, 0, 3) == "NPS" || substr($detailView, 0, 3) == "ESS") {
//$final_tableheader = $nps_table_headers;
} else {
$final_tableheader = $table_headers;
$final_table .= '<table class="' . $tablename . ' table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $final_tableheader . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
break;
case "Section":
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$tablename = "displaytab";
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$periodspan .= ',' . $mc_period[0];
if ($mn_combine == 1) {
$firstPeriod = trim($mc_period[1], '-');
}
if ($mn_combine == $combineCount) {
$lastPeriod = trim($mc_period[1], '-');
}
++$mn_combine;
}
$periodspan = ltrim($periodspan, ',');
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $firstPeriod . ' : ' . $lastPeriod . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id where surveyitems.surveyId = ? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and FIND_IN_SET(responses.assignment_id, ?)")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
if ($stmt = $db_conx->prepare("SELECT id, value FROM surveyitems where surveyid=? and item_type=1 and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$section_names = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($section_names as $section) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) sectionscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id WHERE surveyitems.section_number=? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where FIND_IN_SET(id, ?) AND location_id = ?) GROUP BY section_number")) {
$stmt->bind_param("sss", $section['id'],$reportAssiggments, $mc_location);
$stmt->execute();
$stmt->bind_result($sectionscore);
while ($stmt->fetch()) {
$finalSampleList[$section['value']][] = ceil($sectionscore / $totscore);
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT id, value FROM surveyitems where surveyid=? and item_type=1 and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$section_names = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($section_names as $section) {
if ($stmt = $db_conx->prepare("SELECT ifnull(round(SUM( responses.score)/SUM(question_weightage) * 100 ),0) sectionscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id left outer join assignments on assignments.id=responses.assignment_id WHERE surveyitems.section_number=? and question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where location_id = ? and FIND_IN_SET(id, ?)) GROUP BY section_number")) {
$stmt->bind_param("sss", $section['id'], $mc_location , $reportAssiggments);
$stmt->execute();
$stmt->bind_result($sectionscore);
while ($stmt->fetch()) {
$finalSampleList[$section['value']][] = $sectionscore;
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if (substr($detailView, 0, 3) == "NPS" || substr($detailView, 0, 3) == "ESS") {
//$final_tableheader = $nps_table_headers;
} else {
$final_tableheader = $table_headers;
$final_table .= '<table class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
<th colspan="10">Combined</th>
</tr>
<tr>
' . $final_tableheader . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
break;
case "FocalPoints":
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$tablename = "displaytab";
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$periodspan .= ',' . $mc_period[0];
if ($mn_combine == 1) {
$firstPeriod = trim($mc_period[1], '-');
}
if ($mn_combine == $combineCount) {
$lastPeriod = trim($mc_period[1], '-');
}
++$mn_combine;
}
$periodspan = ltrim($periodspan, ',');
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $firstPeriod . ' : ' . $lastPeriod . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses where responses.survey_id = ? and FIND_IN_SET(responses.assignment_id, ?) )")) {
$stmt->bind_param("ss", $surveyId, $reportAssiggments);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
if ($stmt = $db_conx->prepare("SELECT id, tag FROM survey_tags where surveyid=? and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$fpoints_attached = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($fpoints_attached as $fpoints) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) fpointscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id left outer join assignments on assignments.id=responses.assignment_id WHERE question_weightage > 0 and find_in_set (?,surveyitems.subcategory) AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where location_id = ? and campaign_id in ($periodspan) and status = 'Approved')")) {
$stmt->bind_param("ss", $fpoints['tag'], $mc_location);
$stmt->execute();
$stmt->bind_result($fpointscore);
while ($stmt->fetch()) {
$finalSampleList[$fpoints['tag']][] = ceil($fpointscore / $totscore);
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT id, tag FROM survey_tags where surveyid=? and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$fpoints_attached = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($fpoints_attached as $fpoint) {
if ($stmt = $db_conx->prepare("SELECT ifnull(round(SUM( responses.score)/SUM(question_weightage) * 100 ),0) fpointscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id left outer join assignments on assignments.id=responses.assignment_id WHERE question_weightage > 0 and find_in_set (?,surveyitems.subcategory) AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where location_id = ? and FIND_IN_SET(id, ?) and status = 'Approved')")) {
$stmt->bind_param("sss", $fpoint['tag'], $mc_location, $reportAssiggments);
$stmt->execute();
$stmt->bind_result($fpointscore);
while ($stmt->fetch()) {
$finalSampleList[$fpoint['tag']][] = $fpointscore;
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if (substr($detailView, 0, 3) == "NPS" || substr($detailView, 0, 3) == "ESS") {
//$final_tableheader = $nps_table_headers;
} else {
$final_tableheader = $table_headers;
$final_table .= '<table class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
<th colspan="10">Combined</th>
</tr>
<tr>
' . $final_tableheader . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
break;
}
}
switch ($reportType) {
case "LineItem":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$mc_period = explode('|', $mc_period);
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . trim($mc_period[1], '-') . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$mc_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc_sample[1] . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments WHERE FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
++$mc_count;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value,responses.score FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE s.question_weightage > 0 AND responses.response <> 'N/A' and FIND_IN_SET(question_id,?) and assignment_id = ? order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
++$mc_count2;
}
$stmt->close();
}
}
$mc_count2 = 0;
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
$finalScores = reportbuild_assignmentScores($mc_list3, $selectedItems_list, $db_conx);
foreach ($finalScores as $mc_finalscore) {
foreach ($mc_finalscore as $finscore) {
$finalSampleList[$finscore[0]][] = $finscore[3];
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Heat Map":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
$sectionScores = reportbuild_LocationScores($mc_list2, $selectedItems_list, $db_conx);
foreach ($sectionScores as $mc_finalscore) {
foreach ($mc_finalscore as $finscore) {
$finalSampleList[$finscore[0]][] = $finscore[3];
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
if ($a0_tablefin[$fullcount] >= 80) {
$bg_colour = 'style="background-color:green; color:white;"';
} elseif ($a0_tablefin[$fullcount] <= 80 && $a0_tablefin[$fullcount] >= 60) {
$bg_colour = 'style="background-color:yellow; color:white;"';
} else {
$bg_colour = 'style="background-color:red; color:white;"';
}
$newTableRows .= '<td ' . $bg_colour . '>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Comments":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value,remarks FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? and remarks != '' order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Uploads":
break;
case "Zero Rated*":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? and s.question_weightage = 0 order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table class="displaytab table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
break;
case "Section":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses where survey_id = ? and FIND_IN_SET(responses.assignment_id, ?)")) {
$stmt->bind_param("ss", $surveyId, $reportAssiggments);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
if ($stmt = $db_conx->prepare("SELECT id,value FROM surveyitems where surveyid=? and item_type = 1 and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$section_names = $result->fetch_all(MYSQLI_ASSOC);
//$section_name = $section_names[0]['value'];
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($section_names as $section) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) sectionscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id WHERE surveyitems.section_number=? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where location_id = ? and FIND_IN_SET(id, ?)) GROUP BY section_number")) {
$stmt->bind_param("sss", $section['id'], $mc_location, $reportAssiggments);
$stmt->execute();
$stmt->bind_result($sectionscore);
while ($stmt->fetch()) {
$finalSampleList[$section['value']][] = ceil($sectionscore / $totscore);
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT id, value FROM surveyitems where surveyid=? and item_type=1 and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$section_names = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($section_names as $section) {
if ($stmt = $db_conx->prepare("SELECT ifnull(round(SUM( responses.score)/SUM(question_weightage) * 100 ),0) sectionscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id WHERE surveyitems.section_number=? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where location_id = ? and FIND_IN_SET(id, ?) ) GROUP BY section_number")) {
$stmt->bind_param("sss", $section['id'], $mc_location, $reportAssiggments);
$stmt->execute();
$stmt->bind_result($sectionscore);
while ($stmt->fetch()) {
$finalSampleList[$section['value']][] = $sectionscore;
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
break;
case "FocalPoints":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses where survey_id = ? and FIND_IN_SET(responses.assignment_id, ?)")) {
$stmt->bind_param("ss", $surveyId, $reportAssiggments);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
$stmt->close();
}
if ($stmt = $db_conx->prepare(" SELECT tag FROM survey_tags where surveyid=? and FIND_IN_SET(id,?) ")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$fpoints_attached = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
}
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($fpoints_attached as $fpoints) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) fpointscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id left outer join assignments on assignments.id=responses.assignment_id WHERE find_in_set (?,surveyitems.subcategory) AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where location_id = ? and FIND_IN_SET(id, ?))")) {
$stmt->bind_param("sss", $fpoints['tag'], $mc_location, $reportAssiggments);
$stmt->execute();
$stmt->bind_result($fpointscore);
while ($stmt->fetch()) {
$finalSampleList[$fpoints['tag']][] = ceil($fpointscore / $totscore);
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT id, tag FROM survey_tags where surveyid=? and FIND_IN_SET(id,?)")) {
$stmt->bind_param("ss", $surveyId, $selectedItems_list);
$stmt->execute();
$result = $stmt->get_result();
$fpoints_attached = $result->fetch_all(MYSQLI_ASSOC);
foreach ($selectedSample2 as $mc0_sample) {
$mc_locations = explode('|', $mc0_sample);
$mc_location = $mc_locations[0];
$table_headers .= '<th>' . $mc_locations[1] . ' </th>';
foreach ($fpoints_attached as $fpoint) {
if ($stmt = $db_conx->prepare("SELECT ifnull(round(SUM( responses.score)/SUM(question_weightage) * 100 ),0) fpointscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id left outer join assignments on assignments.id=responses.assignment_id WHERE find_in_set (?,surveyitems.subcategory) AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where location_id = ? and FIND_IN_SET(id, ?))")) {
$stmt->bind_param("sss", $fpoint['tag'], $mc_location, $reportAssiggments);
$stmt->execute();
$stmt->bind_result($fpointscore);
while ($stmt->fetch()) {
$finalSampleList[$fpoint['tag']][] = $fpointscore;
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
break;
case "Summary":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$mc_period = explode('|', $mc_period);
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $mc_period[1] . ' </th>';
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$mc_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc_sample[1] . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) and location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
++$mc_count;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE s.item_type='16' and assignment_id = ? ")) {
$stmt->bind_param("s", $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedSample2);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
$final_table .= '<table class="displaytab table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
break;
case "Complete":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$selectedItems_list = implode(',', $selectedItems);
$mc_period = explode('|', $mc_period);
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_0sample) {
$mc_sample = explode('|', $mc_0sample);
$table_headers = '<th style="display:none;">itemrank</th>' . '<th> ' . trim($mc_period[1], '-') . ' | ' . $mc_sample[1] . ' </th>';
if ($sampleCombine == "1") {
$sampleCombine_type = ' location_id in (select id from locations where FIND_IN_SET(?,location_grouping2))';
} else {
$sampleCombine_type = ' location_id = ?';
}
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
++$mc_count;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$mc_actualfinaltable = $newTableRows1 = "";
$table_headers .= '<th style="display:none;">Section</th>';
$table_headers .= '<th>Focal Point</th>';
$table_headers .= '<th>Section/Query</th>';
$table_headers .= '<th>Answer</th>';
$table_headers .= '<th>Pts Achvd</th>';
$table_headers .= '<th>Score</th>';
$table_headers .= '<th>Comments</th>';
$table_headers .= '<th>Uploads</th>';
foreach ($sampleList as $mc_sampleList) {
foreach ($mc_sampleList as $mc_sampleList2) {
//$finalSampleList = array();
$newTableRows1 = "";
if ($stmtsections = $db_conx->prepare("SELECT id, value FROM surveyitems WHERE surveyID = ? AND item_type = '1' order by reportRank")) {
$stmtsections->bind_param("s", $surveyId);
$stmtsections->execute();
$stmtsections->store_result();
$stmtsections->bind_result($section_num, $sectionvalue);
while ($stmtsections->fetch()) {
$mc_count2 = 0;
$sectiontd = '<td style="display:none;">' . $sectionvalue . '</td>';
if ($stmt = $db_conx->prepare("SELECT s.subcategory,s.value,case when responses.response LIKE '%img%' then '' else responses.response end as response,responses.score points,round((responses.score/question_weightage) * 100 ), responses.remarks,case when responses.response LIKE '%img%' then responses.response end as uploads FROM responses left outer join surveyitems s on responses.question_id = s.id where responses.assignment_id = ? and s.item_type <> '1' and s.section_number = ? order by displayRank")) {
$stmt->bind_param("ss", $mc_sampleList2, $section_num);
$stmt->execute();
$stmt->bind_result($focalpoint, $question, $response, $points, $ques_score, $ques_remarks, $uploads);
while ($stmt->fetch()) {
$finalSampleList[$mc_sampleList2][] = $focalpoint . '|' . $question . '|' . $response . '|' . $points . '|' . $ques_score . '|' . $ques_remarks . '|' . $uploads;
}
$stmt->close();
}
}
$stmtsections->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = 7;
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td><td></td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$a0_tabledets = explode('|', $a0_tablefin[$fullcount]);
foreach ($a0_tabledets as $mc_table) {
$newTableRows .= '<td>' . $mc_table . '</td>';
++$countbreak;
}
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $sectiontd . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
if (!empty($finalSampleList)) {
$final_table .= '<table class="completetable table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
case "Complete(Temp)":
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
$selectedItems_list = implode(',', $selectedItems);
$mc_period = explode('|', $mc_period);
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_0sample) {
$mc_sample = explode('|', $mc_0sample);
$table_headers = '<th> ' . trim($mc_period[1], '-') . ' | ' . $mc_sample[1] . ' </th>';
if ($sampleCombine == "1") {
$sampleCombine_type = ' location_id in (select id from locations where FIND_IN_SET(?,location_grouping2))';
} else {
$sampleCombine_type = ' location_id = ?';
}
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments WHERE FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
++$mc_count;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$mc_actualfinaltable = $newTableRows1 = "";
$table_headers .= '<th style="display:none;">Section</th>';
$table_headers .= '<th>What location are you based in?</th>';
$table_headers .= '<th>First Name</th>';
$table_headers .= '<th>Last Name</th>';
$table_headers .= '<th>What is your food handlers permit/receipt number?</th>';
$table_headers .= '<th>Expiry Date?</th>';
$table_headers .= '<th>Please take a head shot pic/selfie</th>';
foreach ($sampleList as $mc_sampleList) {
foreach ($mc_sampleList as $mc_sampleList2) {
//$finalSampleList = array();
$newTableRows1 = "";
if ($stmtsections = $db_conx->prepare("SELECT id, value FROM surveyitems WHERE surveyID = ? AND item_type = '1' order by reportRank")) {
$stmtsections->bind_param("s", $surveyId);
$stmtsections->execute();
$stmtsections->store_result();
$stmtsections->bind_result($section_num, $sectionvalue);
while ($stmtsections->fetch()) {
$mc_count2 = 0;
$sectiontd = '<td style="display:none;">' . $sectionvalue . '</td>';
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id where responses.assignment_id = ? and s.item_type <> '1' and s.section_number = ? order by displayRank")) {
$stmt->bind_param("ss", $mc_sampleList2, $section_num);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$mc_sampleList2][] = $response;
}
$stmt->close();
}
}
$stmtsections->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = 6;
$first_row = 1;
foreach ($finalSampleList as $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td><td></td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
++$countcurr;
}
if (!empty($finalSampleList)) {
$final_table .= '<table class="completetable table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
}
} else {
if ($sampleCombineTable == "1" && in_array($detailView, $combine_list)) {
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$periodspan .= ',' . $mc_period[0];
if ($mn_combine == 1) {
$firstPeriod = trim($mc_period[1], '-');
}
if ($mn_combine == $combineCount) {
$lastPeriod = trim($mc_period[1], '-');
}
++$mn_combine;
}
$periodspan = ltrim($periodspan, ',');
switch ($reportType) {
case "LineItem":
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$tablename = "displaytab_combined";
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = $sampleCombine_type = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . trim($mc0_sample[1], '-') . ' </th>';
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
$sample_List = implode(',', $sampleList);
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if ($stmt = $db_conx->prepare("SELECT s.value,sum(responses.score) FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and responses.assignment_id in (select id FROM assignments WHERE FIND_IN_SET(id, ?) AND location_id = ?) group by question_id")) {
$stmt->bind_param("sss", $selectedItems_list, $reportAssiggments, $mc0_sample[0]);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = $sectionScores = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
$finalScores = reportbuild_assignmentScoresByLocationCombined($mc_sample[0], $reportAssiggments, $selectedItems_list, $db_conx);
foreach ($finalScores as $mc_finalscore) {
foreach ($mc_finalscore as $finscore) {
$finalSampleList[$finscore[0]][] = $finscore[3];
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if (substr($detailView, 0, 3) == "NPS" || substr($detailView, 0, 3) == "ESS") {
//$final_tableheader = $nps_table_headers;
} else {
$final_table .= '<table class="displaytab_combined table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
<th colspan="10">Combined</th>
</tr>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
break;
case "Section":
if (!empty($selectedSample)) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc0_sample[1] . ' </th>';
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) as totalscore FROM responses where survey_id = ? and FIND_IN_SET(responses.assignment_id, ?)")) {
$stmt->bind_param("ss", $surveyId, $reportAssiggments);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
if ($stmt = $db_conx->prepare("SELECT id,value FROM surveyitems where surveyid=? and item_type=1 ")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$section_names = $result->fetch_all(MYSQLI_ASSOC);
foreach ($section_names as $section) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) sectionscore FROM responses left outer join surveyitems on surveyitems.id=responses.question_id WHERE surveyitems.section_number=? and surveyitems.question_weightage > 0 AND responses.response <> 'N/A' and responses.assignment_id in (select id from assignments where FIND_IN_SET(id, ?) AND location_id = ?) GROUP BY section_number")) {
$stmt->bind_param("sss", $section['id'], $reportAssiggments, $mc0_sample[0]);
$stmt->execute();
$stmt->bind_result($sectionscore);
while ($stmt->fetch()) {
$finalSampleList[$section['value']][] = ceil($sectionscore / $totscore);
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = $sectionScores = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
foreach ($selectedItems as $mc_lineitems) {
$sectionScores = reportbuild_assignmentSectionScoresByLocationCombined($mc0_sample[0], $mc_lineitems, $reportAssiggments, $db_conx);
foreach ($sectionScores as $mc_secscore) {
foreach ($mc_secscore as $sec) {
$finalSampleList[$sec[0]][] = $sec[3];
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if (substr($detailView, 0, 3) == "NPS" || substr($detailView, 0, 3) == "ESS") {
//$final_tableheader = $nps_table_headers;
} else {
$final_table .= '<table class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
<th colspan="10">Combined</th>
</tr>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
case "FocalPoints":
if (!empty($selectedSample)) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . ltrim($mc0_sample[1], '-') . ' </th>';
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . ltrim($mc_period[1], '-') . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc0_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc0_sample[1]][] = $assignId;
$assignment_list .= $assignId . ',';
}
$stmtassign->close();
}
}
$assignment_list = trim($assignment_list, ',');
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) as totalscore FROM responses where survey_id = ? and FIND_IN_SET(responses.assignment_id, ?)")) {
$stmt->bind_param("ss", $surveyId, $reportAssiggments);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
if ($stmt = $db_conx->prepare("SELECT tag FROM survey_tags where surveyid=?")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$fpoints_attached = $result->fetch_all(MYSQLI_ASSOC);
}
foreach ($fpoints_attached as $fpoint) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) fpointscore FROM surveyitems s left outer join responses on responses.question_id = s.id WHERE responses.survey_id=? and find_in_set (?,s.subcategory) and FIND_IN_SET(id, responses.assignment_id) )")) {
$stmt->bind_param("sss", $surveyId, $fpoint['tag'], $reportAssiggments);
$stmt->execute();
$stmt->bind_result($fpointscore);
$all_respone = "";
while ($stmt->fetch()) {
$finalSampleList[$fpoint['tag']][] = ceil($fpointscore / $totscore);
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = $sectionScores = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
foreach ($selectedItems as $mc_lineitems) {
$tagScores = reportbuild_assignmentTagScoresByLocationCombined($mc0_sample[0], $mc_lineitems, $reportAssiggments, $db_conx);
foreach ($tagScores as $mc_secscore) {
foreach ($mc_secscore as $sec) {
$finalSampleList[$sec[0]][] = $sec[3];
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if (substr($detailView, 0, 3) == "NPS" || substr($detailView, 0, 3) == "ESS") {
//$final_tableheader = $nps_table_headers;
} else {
$final_table .= '<table class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
<th colspan="10">Combined</th>
</tr>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
}
}
switch ($reportType) {
case "LineItem":
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = $sampleCombine_type = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $mc0_sample[1] . ' </th>';
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) AND location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc0_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
while ($stmtassign->fetch()) {
$sampleList[$mc0_sample[1]][] = $assignId;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list3);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare("SELECT s.value,responses.score FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list3);
$stmt->execute();
$stmt->bind_result($question, $score);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $score;
++$mc_count2;
}
$stmt->close();
}
}
$mc_count2 = 0;
++$mc_qcount;
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = $sectionScores = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
$sectionScores = reportbuild_assignmentScores($mc_list3, $selectedItems_list, $db_conx);
foreach ($sectionScores as $mc_secscore) {
foreach ($mc_secscore as $sec) {
$finalSampleList[$sec[0]][] = $sec[3];
++$mc_count2;
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Heat Map":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
$sectionScores = reportbuild_LocationScores($mc_list3, $selectedItems_list, $db_conx);
foreach ($sectionScores as $mc_secscore) {
foreach ($mc_secscore as $sec) {
$finalSampleList[$sec[0]][] = $sec[3];
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
if ($a0_tablefin[$fullcount] >= 80) {
$bg_colour = 'class="heat-green"';
} elseif ($a0_tablefin[$fullcount] <= 80 && $a0_tablefin[$fullcount] >= 60) {
$bg_colour = 'class="heat-yellow"';
} else {
$bg_colour = 'class="heat-red"';
}
$newTableRows .= '<td ' . $bg_colour . '>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Comments":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare("SELECT s.value,remarks FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? and remarks != '' order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list3);
$stmt->execute();
$stmt->bind_result($question, $remarks);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $remarks;
++$mc_count2;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Uploads":
break;
case "Zero Rated*":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$selectedItems_list = implode(',', $selectedItems);
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare("SELECT s.value,response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE FIND_IN_SET(question_id,?) and assignment_id = ? and s.question_weightage = 0 order by s.section_number,s.displayRank")) {
$stmt->bind_param("ss", $selectedItems_list, $mc_list3);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table class="displaytab table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
break;
case "Section":
if (!empty($selectedSample)) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
if (isset($selectedLocation[0])) {
$selectedSample2 = $selectedLocation;
} else {
$a0_level = explode(',', $selectedSample_list);
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(?,location_grouping2) group by id")) {
foreach ($a0_level as $mc_level) {
$stmtassign->bind_param("ss", $companyID, $mc_level);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = $assignment_list = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc0_sample[1] . ' </th>';
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . ltrim($mc_period[1], '-') . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) and location_id = ?")) { //getting all the assignments per location
$stmtassign->bind_param("ss", $reportAssiggments, $mc0_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc0_sample[1]][] = $assignId;
$assignment_list .= $assignId . ',';
}
$stmtassign->close();
}
}
$assignment_list = trim($assignment_list, ',');
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses where survey_id = ? and assignment_id in ($assignment_list)")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
foreach ($sampleList as $mc_list => $mc_list2) { // loop through the location to find what questions are in what sections
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare("SELECT id,value FROM surveyitems where surveyid=? and item_type=1 ")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$section_names = $result->fetch_all(MYSQLI_ASSOC);
}
foreach ($section_names as $section) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) sectionscore FROM responses left outer join surveyitems on responses.question_id = surveyitems.id WHERE responses.survey_id=? and responses.response <> 'N/A' and responses.assignment_id=? and surveyitems.section_number =? and surveyitems.question_weightage > 0 GROUP BY section_number")) {
$stmt->bind_param("sss", $surveyId, $mc_list3, $section['id']);
$stmt->execute();
$stmt->bind_result($sectionscore);
while ($stmt->fetch()) {
$finalSampleList[$section['value']][] = ceil($sectionscore / $totscore);
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = $sectionScores = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($selectedItems as $mc_lineitems) {
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
$sectionScores = reportbuild_assignmentSectionScores($mc_list3, $mc_lineitems, $db_conx);
foreach ($sectionScores as $mc_secscore) {
foreach ($mc_secscore as $sec) {
$finalSampleList[$sec[0]][] = $sec[3];
}
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
case "FocalPoints":
if (!empty($selectedSample)) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
$table_headers .= '<th>' . $mc0_sample[1] . ' </th>';
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) and location_id = ?")) {
$stmtassign->bind_param("ss", $reportAssiggments, $mc0_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc0_sample[1]][] = $assignId;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Points Achieved":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
$section_name = $all_respone = "";
$totscores = $totscore = 0;
if ($stmt = $db_conx->prepare("SELECT sum(responses.score ) as totalscore FROM responses where survey_id = ? ")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$totscores = $result->fetch_all(MYSQLI_ASSOC);
$totscore = $totscores[0]['totalscore'];
}
foreach ($sampleList as $mc_list => $mc_list2) { // loop through the location to find what questions are in what sections
foreach ($mc_list2 as $mc_list3) {
if ($stmt = $db_conx->prepare(" SELECT tag FROM survey_tags where surveyid=?")) {
$stmt->bind_param("s", $surveyId);
$stmt->execute();
$result = $stmt->get_result();
$fpoints_attached = $result->fetch_all(MYSQLI_ASSOC);
}
foreach ($fpoints_attached as $fpoint) {
if ($stmt = $db_conx->prepare("SELECT sum(responses.score) fpointscore FROM surveyitems s left outer join responses on responses.question_id = s.id WHERE responses.survey_id=? and find_in_set (?,s.subcategory) and responses.assignment_id=?")) {
$stmt->bind_param("sss", $surveyId, $fpoint['tag'], $mc_list3);
$stmt->execute();
$stmt->bind_result($fpointscore);
$all_respone = "";
while ($stmt->fetch()) {
$finalSampleList[$fpoint['tag']][] = ceil($fpointscore / $totscore);
}
$stmt->close();
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
case "Final Score":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = $sectionScores = array();
$selectedItems_list = implode(',', $selectedItems);
$section_name = $all_respone = "";
$totscores = $totscore = 0;
foreach ($selectedItems as $mc_lineitems) {
foreach ($sampleList as $mc_list => $mc_list2) {
foreach ($mc_list2 as $mc_list3) {
$tagScores = reportbuild_assignmentTagScores($mc_list3, $final_location, $mc_lineitems, $db_conx);
foreach ($tagScores as $mc_fpscore) {
foreach ($mc_fpscore as $fp) {
$finalSampleList[$fp[0]][] = $fp[3];
}
}
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td>' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
if ($newTableRows1 != "") {
$final_table .= '<table class="displaytab_section table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
case "Summary":
if (!empty($selectedSample)) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
$table_headers .= '<th style="display:none;">itemrank</th>' . '<th>' . $mc0_sample[1] . ' </th>';
foreach ($selectedCampaign as $mc_period) {
$mc_period = explode('|', $mc_period);
$table_headers .= '<th>' . trim($mc_period[1], '-') . ' </th>';
if ($stmtassign = $db_conx->prepare("SELECT id FROM assignments where FIND_IN_SET(id, ?) and location_id = ?")) { //getting all the assignments per location
$stmtassign->bind_param("ss", $reportAssiggments, $mc0_sample[0]);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($assignId);
$mc_count = 0;
while ($stmtassign->fetch()) {
$sampleList[$mc_sample[1]][] = $assignId;
}
$stmtassign->close();
}
}
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
foreach ($sampleList as $mc_list => $mc_list3) {
foreach ($mc_list3 as $mc_list2) {
if ($stmt = $db_conx->prepare("SELECT s.value, responses.response FROM responses left outer join surveyitems s on responses.question_id = s.id WHERE s.item_type='16' and assignment_id = ? ")) {
$stmt->bind_param("s", $mc_list2);
$stmt->execute();
$stmt->bind_result($question, $response);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $response;
}
$stmt->close();
}
}
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = count($selectedCampaign);
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td style="color:white;">' . $mc_quesnum . $mc_tablefin . '</td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$newTableRows .= '<td>' . $a0_tablefin[$fullcount] . '</td>';
$first_row = 0;
++$countbreak;
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
break;
}
$final_table .= '<table class="displaytab table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
break;
case "Complete":
if (!empty($selectedSample)) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = $selectedSample2 = array();
if (!empty($selectedLocation)) {
$selectedSample2 = $selectedLocation;
} else {
if ($stmtassign = $db_conx->prepare("SELECT id, location_name FROM locations WHERE company_id = ? and active = 'active' and FIND_IN_SET(location_grouping2,?) group by id")) {
$stmtassign->bind_param("ss", $companyID, $selectedSample_list);
$stmtassign->execute();
$stmtassign->store_result();
$stmtassign->bind_result($locationId, $location_name);
$mc_count = 0;
while ($stmtassign->fetch()) {
$selectedSample2[] = $locationId . '|' . $location_name;;
}
$stmtassign->close();
}
}
foreach ($selectedSample2 as $mc_sample) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$sampleList = array();
$mc0_sample = explode('|', $mc_sample);
foreach ($selectedCampaign as $mc_period) {
$table_headers = $mc_actualfinaltable = $newTableRows1 = "";
$mc_period = explode('|', $mc_period);
$table_headers .= '<th style="display:none;">itemrank</th>';
$table_headers .= '<th> ' . $mc0_sample[1] . ' : ' . trim($mc_period[1], '-') . ' </th>';
$table_headers .= '<th style="display:none;">Section</th>';
$table_headers .= '<th>Focal Point</th>';
$table_headers .= '<th>Section/Query</th>';
$table_headers .= '<th>Answer</th>';
$table_headers .= '<th>Pts Achvd</th>';
$table_headers .= '<th>Score</th>';
$table_headers .= '<th>Comments</th>';
$table_headers .= '<th>Uploads</th>';
switch ($detailView) {
case "Raw Response":
$mc_count2 = 0;
$mc_qcount = 1;
$finalSampleList = array();
if ($stmtsections = $db_conx->prepare("SELECT id, value FROM surveyitems WHERE surveyID = ? AND item_type = '1' order by reportRank")) {
$stmtsections->bind_param("s", $surveyId);
$stmtsections->execute();
$stmtsections->store_result();
$stmtsections->bind_result($section_num, $sectionvalue);
while ($stmtsections->fetch()) {
$mc_count2 = 0;
$finalSampleList = array();
$sectiontd = '<td style="display:none;">' . $sectionvalue . '</td>';
if ($stmt = $db_conx->prepare("SELECT s.subcategory,s.value,responses.response,responses.score points,round((responses.score/question_weightage) * 100 ), responses.remarks,'' uploads FROM responses left outer join surveyitems s on responses.question_id = s.id left outer join assignments on responses.assignment_id=assignments.id left outer join locations on locations.id=assignments.location_id WHERE FIND_IN_SET(id, ?) and assignments.location_id=? and s.item_type<>'1' and s.section_number=? order by displayRank")) {
$stmt->bind_param("sss", $reportAssiggments, $mc0_sample[0], $section_num);
$stmt->execute();
$stmt->bind_result($focalpoint, $question, $response, $points, $ques_score, $ques_remarks, $uploads);
while ($stmt->fetch()) {
$finalSampleList[$question][] = $focalpoint . '|' . $question . '|' . $response . '|' . $points . '|' . $ques_score . '|' . $ques_remarks . '|' . $uploads;
}
$stmt->close();
}
$countcurr = 1;
$fullcount = 0;
$countbreak = 0;
$force_break = 7;
$first_row = 1;
foreach ($finalSampleList as $mc_tablefin => $a0_tablefin) {
$mc_quesnum = 'Q' . $countcurr . ' ';
if ($mc_tablefin != "") {
$newTableRows = "";
$newTableRowsheader = '<td style="display:none;">' . $countcurr . '</td>' . '<td></td>';
$counttablelistfin = count($a0_tablefin);
while ($fullcount < $counttablelistfin) {
while ($countbreak < $force_break) {
$a0_tabledets = explode('|', $a0_tablefin[$fullcount]);
foreach ($a0_tabledets as $mc_table) {
$newTableRows .= '<td>' . $mc_table . '</td>';
++$countbreak;
}
++$fullcount;
}
$newTableRows1 .= '<tr>' . $newTableRowsheader . $sectiontd . $newTableRows . '</tr>';
$countbreak = 0;
$newTableRows = "";
}
$fullcount = 0;
}
++$countcurr;
}
//$section_header_row .= '<tr><td></td><td></td><td>' . $sectionvalue . '</td><td></td><td></td><td></td><td></td><td></td></tr>' . $newTableRows1;
}
$stmtsections->close();
}
break;
}
$final_table .= '<table class="completetable table table-responsive-lg table-bordered table-striped table-sm mb-0">
<thead>
<tr>
' . $table_headers . '
</tr>
</thead>
<tbody>
' . $newTableRows1 . '
</tbody>
</table>';
}
}
}
break;
}
}
break;
}
?>
<style>
.group {
background-color: #48aaad !important;
color: white;
}
.table-responsive-lg thead {
background: #151414;
color: #fff;
font-weight: bold;
}
</style>
<div id="custom-content" class="modal-block modal-block-lg">
<section class="card">
<header class="card-header" style="text-align: center;">
<h2 class="card-title">View Report</h2>
</header>
<div class="card-body">
<div class="table-responsive">
<?php echo $final_table; ?>
</div>
</div>
</section>
</div>
<script src="assets/js/viewreportdatatables.js?v=<?php echo time(); ?>"></script>
<script src="assets/js/viewreportdatatables_combined.js?v=<?php echo time(); ?>"></script>
<script src="assets/js/viewreportdatatables_section.js?v=<?php echo time(); ?>"></script>
<script src="assets/js/reportdatatables_group.js?v=<?php echo time(); ?>"></script>
<script src="assets/js/viewreportdatatables_heatmap.js?v=<?php echo time(); ?>"></script>
<script src="assets/js/examples/examples.advanced.form.js"></script>
<script src="assets/js/examples/examples.loading.overlay.js"></script>Editor is loading...