createCSV.php

<?php

$post_at = "";
$post_at_to_date = "";
if (isset($_POST["survey"])) {
    $query = "SELECT r3.memberid, r3.year, r3.week, Sum(CASE questionid WHEN '1' THEN answerid ELSE 0 end) AS 'qn1', Sum(CASE questionid WHEN '2' THEN answerid ELSE 0 end) AS 'qn2', Sum(CASE questionid WHEN '3' THEN answerid ELSE 0 end) AS 'qn3', Sum(CASE questionid WHEN '4' THEN answerid ELSE 0 end) AS 'qn4', Sum(CASE questionid WHEN '5' THEN answerid ELSE 0 end) AS 'qn5', Sum(CASE questionid WHEN '6' THEN answerid ELSE 0 end) AS 'qn6', Sum(CASE questionid WHEN '7' THEN answerid ELSE 0 end) AS 'qn7', Sum(CASE questionid WHEN '8' THEN answerid ELSE 0 end) AS 'qn8', Sum(CASE questionid WHEN '9' THEN answerid ELSE 0 end) AS 'qn9', Sum(CASE questionid WHEN '10' THEN answerid ELSE 0 end) AS 'qn10', Sum(CASE questionid WHEN '11' THEN answerid ELSE 0 end) AS 'qn11', Sum(CASE questionid WHEN '12' THEN answerid ELSE 0 end) AS 'qn12', Sum(CASE questionid WHEN '13' THEN answerid ELSE 0 end) AS 'qn13', Sum(CASE questionid WHEN '14' THEN answerid ELSE 0 end) AS 'qn14', Sum(CASE questionid WHEN '15' THEN answerid ELSE 0 end) AS 'qn15', Sum(CASE questionid WHEN '16' THEN answerid ELSE 0 end) AS 'qn16', Sum(CASE questionid WHEN '17' THEN answerid ELSE 0 end) AS 'qn17' ,w.hlescore,w.RelativeHLEScore,w.BenchMarlHLEScore,w.score, createtime FROM(SELECT r1.year, r1.memberid, r1.week, r1.questionid, r1.answerid, r1.createtime FROM `UserResponses` r1 LEFT JOIN `UserResponses` r2 ON r1.memberid = r2.memberid AND r1.year = r2.year AND r1.week = r2.week AND r1.questionid = r2.questionid AND r1.createtime < r2.createtime WHERE r2.memberid IS NULL ORDER BY r1.year, r1.week, r1.memberid, r1.questionid) r3 right join WeeklyScore w on r3.week=w.week and r3.year = w.year and r3.memberid = w.memberid		 GROUP BY memberid, year, week, createtime ORDER BY year, week, memberid;  " ;
    if (!empty($_POST["search"]["post_at"])) {
        $post_at = $_POST["search"]["post_at"];
        list($fid, $fim, $fiy) = explode("-", $post_at);
    
        $post_at_todate = date('Y-m-d');
        if (!empty($_POST["search"]["post_at_to_date"])) {
            $post_at_to_date = $_POST["search"]["post_at_to_date"];
            list($tid, $tim, $tiy) = explode("-", $_POST["search"]["post_at_to_date"]);
            $post_at_todate = "$tiy-$tim-$tid";
        }
        $query = "SELECT r3.memberid, r3.year, r3.week, Sum(CASE questionid WHEN '1' THEN answerid ELSE 0 end) AS 'qn1', Sum(CASE questionid WHEN '2' THEN answerid ELSE 0 end) AS 'qn2', Sum(CASE questionid WHEN '3' THEN answerid ELSE 0 end) AS 'qn3', Sum(CASE questionid WHEN '4' THEN answerid ELSE 0 end) AS 'qn4', Sum(CASE questionid WHEN '5' THEN answerid ELSE 0 end) AS 'qn5', Sum(CASE questionid WHEN '6' THEN answerid ELSE 0 end) AS 'qn6', Sum(CASE questionid WHEN '7' THEN answerid ELSE 0 end) AS 'qn7', Sum(CASE questionid WHEN '8' THEN answerid ELSE 0 end) AS 'qn8', Sum(CASE questionid WHEN '9' THEN answerid ELSE 0 end) AS 'qn9', Sum(CASE questionid WHEN '10' THEN answerid ELSE 0 end) AS 'qn10', Sum(CASE questionid WHEN '11' THEN answerid ELSE 0 end) AS 'qn11', Sum(CASE questionid WHEN '12' THEN answerid ELSE 0 end) AS 'qn12', Sum(CASE questionid WHEN '13' THEN answerid ELSE 0 end) AS 'qn13', Sum(CASE questionid WHEN '14' THEN answerid ELSE 0 end) AS 'qn14', Sum(CASE questionid WHEN '15' THEN answerid ELSE 0 end) AS 'qn15', Sum(CASE questionid WHEN '16' THEN answerid ELSE 0 end) AS 'qn16', Sum(CASE questionid WHEN '17' THEN answerid ELSE 0 end) AS 'qn17' ,w.hlescore,w.RelativeHLEScore,w.BenchMarlHLEScore,w.score, createtime FROM(SELECT r1.year, r1.memberid, r1.week, r1.questionid, r1.answerid, r1.createtime FROM `UserResponses` r1 LEFT JOIN `UserResponses` r2 ON r1.memberid = r2.memberid AND r1.year = r2.year AND r1.week = r2.week AND r1.questionid = r2.questionid AND r1.createtime < r2.createtime WHERE r2.memberid IS NULL ORDER BY r1.year, r1.week, r1.memberid, r1.questionid) r3 left join WeeklyScore w on r3.week = w.week and r3.year = w.year and r3.memberid = w.memberid	" ;
        $query .= " WHERE createtime BETWEEN '$fiy-$fim-$fid' AND '" . $post_at_todate . "'";
        $query .= " GROUP  BY memberid, year, week ,createtime ORDER  BY year ,  week, memberid ";
    }


        $result = $db->getQuery($query);
    ob_end_clean();
    $output = fopen('php://output', 'w');
    header("Content-Disposition: attachment; filename=surveyAnswers.csv");
    header("Content-Type: application/csv; ");

    fputcsv($output, array('memberid','year','week','qn1','qn2','qn3','qn4','qn5','qn6','qn7','qn8','qn9','qn10','qn11','qn12','qn13','qn14','qn15','qn16','qn17','hlescore','RelativeHLEScore','BenchMarlHLEScore','score','createTime'));
    foreach ($result as $row) {
        fputcsv($output, $row);
    }


    fclose($output);
    exit();
}
    
 
 if (isset($_POST["profile"])) {
     $query = "select * from Profile" ;
     if (!empty($_POST["search"]["post_at"])) {
         $post_at = $_POST["search"]["post_at"];
         list($fid, $fim, $fiy) = explode("-", $post_at);
    
         $post_at_todate = date('Y-m-d');
         if (!empty($_POST["search"]["post_at_to_date"])) {
             $post_at_to_date = $_POST["search"]["post_at_to_date"];
             list($tid, $tim, $tiy) = explode("-", $_POST["search"]["post_at_to_date"]);
             $post_at_todate = "$tiy-$tim-$tid";
         }
         $query = "select * from profile where memberID in (select memberID from userresponses ";
         $query .= "WHERE createtime BETWEEN '$fiy-$fim-$fid' AND '" . $post_at_todate . "')";
     }

     $result = $db->getQuery($query);
     ob_end_clean();
     $output = fopen('php://output', 'w');
     header('Content-Type: application/octet-stream');
     header("Content-Disposition: attachment; filename=profiles.csv");
     
     fputcsv($output, array('MemberID', 'DOB', 'Gender', 'Weight', 'HeightFeet', 'HeightInches', 'MaritalStatus', 'Occupation', 'Education', 'Income', 'Religion', 'Residence', 'Ethnicity', 'Medication', 'DrivingHistory',  'CreditScore', 'DietChoices', 'CreateDate', 'LastUpdateDate', 'isSmoker', 'isDiabetic', 'DiseaseCountLowRisk', 'DiseaseCountMidRisk', 'DiseaseCountHighRisk'));
    
     foreach ($result as $row) {
         fputcsv($output, $row);
     }
     fclose($output);
     // deleting file
     
     exit();
 }



 if (isset($_POST["scores"])) {
    $query = "select w.* from WeeklyScore w" ;
    if (!empty($_POST["search"]["post_at"])) {
        $post_at = $_POST["search"]["post_at"];
        list($fid, $fim, $fiy) = explode("-", $post_at);
   
        $post_at_todate = date('Y-m-d');
        if (!empty($_POST["search"]["post_at_to_date"])) {
            $post_at_to_date = $_POST["search"]["post_at_to_date"];
            list($tid, $tim, $tiy) = explode("-", $_POST["search"]["post_at_to_date"]);
            $post_at_todate = "$tiy-$tim-$tid";
        }
        $query = "select w.* from WeeklyScore w left join UserResponses r on w.week=r.week where r.CreateTime  ";
        $query .= "WHERE createtime BETWEEN '$fiy-$fim-$fid' AND '" . $post_at_todate . "')";
    }

    $result = $db->getQuery($query);
    ob_end_clean();
    $output = fopen('php://output', 'w');
    header('Content-Type: application/octet-stream');
    header("Content-Disposition: attachment; filename=profiles.csv");
    
    fputcsv($output, array('MemberID', 'DOB', 'Gender', 'Weight', 'HeightFeet', 'HeightInches', 'MaritalStatus', 'Occupation', 'Education', 'Income', 'Religion', 'Residence', 'Ethnicity', 'Medication', 'DrivingHistory',  'CreditScore', 'DietChoices', 'CreateDate', 'LastUpdateDate', 'isSmoker', 'isDiabetic', 'DiseaseCountLowRisk', 'DiseaseCountMidRisk', 'DiseaseCountHighRisk'));
   
    foreach ($result as $row) {
        fputcsv($output, $row);
    }
    fclose($output);
    // deleting file
    
    exit();
}


?>