NBi9MefG

1<?php
2$q = $db->query("insert into pr_rp_pos (termId,periodId,classId,total,studentId) select '$termId','$periodId','$classId',sum(m.score) sum,m.studentId from marks m join markSheet h on m.markSheetId=h.id join students st on m.studentId=st.id where h.termId = '$termId' and periodId = '$termId' and st.class = '$classId' group by m.studentId order by sum(m.score) desc on duplicate key update `total`=values(`total`)") or die($db->error);
3
4$q = $db->query("select sb.subject,m.studentId,p.id fk,m.id,h.subjectId,m.score from marks m join markSheet h on m.markSheetId=h.id join pr_rp_pos p on p.studentId=m.studentId join subjects as sb on sb.id=h.subjectId where m.studentId in (select studentId from pr_rp_pos where termid = '$termId' and periodId = '$periodId' and classId = '$classId' group by studentId) order by h.subjectId asc,m.studentId asc") or die($db->error);
5
6// echo "select studentId from pr_rp_pos where termid = '$termId' and periodId = '$periodId' and classId = '$classId'";
7
8$values = '';
9while($data = $q->fetch_assoc()){
10    $finalmark      = $data['score'];
11    include('a/grade_primary_mrksheet.php');
12    $subject_id     = $data['subjectId'];
13    $Ag             = val($grd);
14    $student_Id     = $data['studentId'];
15    $markId         = $data['id'];
16    $fk             = $data['fk'];
17    $subject        = $data['subject'];
18    if(in_array($subject,array("religious education","luganda"))) $Ag = 0;
19    if($values == '') $values = "('$subject_id','$Ag','$student_Id','$markId','$fk')";
20    else $values .= ",('$subject_id','$Ag','$student_Id','$markId','$fk')";
21}
22$sql = "insert into `pr_rp_data` (`subjectId`,`Ag`,`studentId`,`markId`,`fk`) values $values on duplicate key update `Ag` = values(`Ag`)";
23$query = $db->query($sql) or die($db->error);
24
25// calculate the total Aggregates
26$q = $db->query("select (select count(*) from marks as mm join markSheet as hh on hh.id=mm.markSheetId where mm.studentId = m.studentId and mm.score <= $ns and hh.termId = '$termId' and hh.classId = '$classId' and hh.periodId = '$periodId') as nines, sum(d.Ag) as AGG,d.studentId from pr_rp_data as d join pr_rp_pos as p on p.id=d.fk join marks as m on d.markId=m.id where p.termId = '$termId' and p.periodId = '$periodId' and p.classId = '$classId' group by d.studentId order by sum(d.Ag) asc") or die($db->error);
27
28$values = '';
29while($data = $q->fetch_assoc()){
30    $student_Id = $data['studentId'];
31    $AGG        = $data['AGG'];
32    $nines      = $data['nines'];
33    include('mini_primaryGrading.php');
34    // $vsGrade from mini_primaryGrading.php
35    $values     = ($values == '') ? "('$student_Id','$AGG','$vsGrade','$termId','$periodId','$classId')" : $values .= ",('$student_Id','$AGG','$vsGrade','$termId','$periodId','$classId')";
36}
37// studentId 	TAG 	DIV 	pos 	termid 	periodId 	total 	classId
38$sql = "insert into pr_rp_pos (`studentId`,`TAG`,`DIV`,`termid`,`periodId`,`classId`) values $values on duplicate key update `TAG` = values(`TAG`),`DIV` = values(`DIV`)";
39$query  = $db->query($sql) or die($db->error);
40// rertuning the field names (subjects)
41$fields = '';
42$columns = array();
43$sbid = array();
44$field_csv = '';
45$table_name = 'tmp_'.$periodId.'_'.$termId.'_'.$classId;
46$q = $db->query("select s.subject,s.id,d.subjectId from pr_rp_data d join pr_rp_pos p on p.id=d.fk join subjects s on s.id=d.subjectId where p.termId = '$termId' and p.periodId = '$periodId' and p.classId = '$classId' group by d.subjectId order by s.id asc") or die($db->error);
47while($data = $q->fetch_assoc()){
48    $fields = ($fields == '') ? '`'.$data['subject'] . '` VARCHAR(255) NULL' . ',`' . $data['subject'] . 'Ag` VARCHAR(3) NULL' : $fields .= ',`'.$data['subject'] .'` VARCHAR(255) NULL,`' . $data['subject'] . 'Ag` VARCHAR(3) NULL';
49
50    $field_csv = ($field_csv == '') ? 'dr.`'.$data['subject'] . '`,dr.`' . $data['subject'] . 'Ag`' : $field_csv .= ',dr.`'.$data['subject'] .'`,dr.`' . $data['subject'] . 'Ag`';
51
52    $columns[]  = $data['subject'];
53    $sbid[]     = $data['subjectId'];
54}
55$fields .= ',`TAG` INT(11) NOT NULL,`DIV` VARCHAR(3) NULL,`POS` INT(11) NOT NULL, PRIMARY KEY (`id`),UNIQUE KEY `uniqu_index` (`studentId`),`TOTAL` bigint(20) NULL';
56$create_table = "CREATE TABLE IF NOT EXISTS `$table_name` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`studentId` bigint(20) NOT NULL,$fields)";
57// echo $create_table;
58// create table and insert the data right away.
59$q = $db->query("drop table $table_name");
60$q = $db->query($create_table);
61
62for($i = 0; $i < count($columns); $i++){
63    $column     = '`' . $columns[$i] . '`';
64    $other      = '`' . $columns[$i] . 'Ag`';
65    $subject_id = $sbid[$i];
66    if(!in_array($columns[$i],array("religious education","luganda"))){
67        if($i == 0) $sql_tmp = "insert into `$table_name` (studentId,$column,$other) select p.studentId,m.score,d.Ag from marks as m join pr_rp_data as d on d.markId=m.id join subjects as s on s.id=d.subjectId join pr_rp_pos as p on p.id=d.fk where d.subjectId = '$subject_id' on duplicate key update $column = values($column),$other=values($other);";
68
69        else $sql_tmp .= "insert into `$table_name` (studentId,$column,$other) select p.studentId,m.score,d.Ag from marks as m join pr_rp_data as d on d.markId=m.id join subjects as s on s.id=d.subjectId join pr_rp_pos as p on p.id=d.fk where d.subjectId = '$subject_id' on duplicate key update $column = values($column),$other=values($other);";
70    }
71    else{
72        if($i == 0) $sql_tmp = "insert into `$table_name` (studentId,$column,$other) select p.studentId,m.score,NULL from marks as m join pr_rp_data as d on d.markId=m.id join subjects as s on s.id=d.subjectId join pr_rp_pos as p on p.id=d.fk where d.subjectId = '$subject_id' on duplicate key update $column = values($column),$other=values($other);";
73
74        else $sql_tmp .= "insert into `$table_name` (studentId,$column,$other) select p.studentId,m.score,NULL from marks as m join pr_rp_data as d on d.markId=m.id join subjects as s on s.id=d.subjectId join pr_rp_pos as p on p.id=d.fk where d.subjectId = '$subject_id' on duplicate key update $column = values($column),$other=values($other);";
75    }
76}
77// TAG 	DIV 	POS
78// studentId 	TAG 	DIV 	termid 	periodId 	total 	classId
79$sql_final = $sql_tmp." insert into `$table_name` (`studentId`,`TAG`,`DIV`,`TOTAL`) select studentId,`TAG`,`DIV`,`total` from pr_rp_pos where `termid` = '$termId' and `periodId` = '$periodId' and `classId` = '$classId' on duplicate key update `TAG`=values(`TAG`),`DIV`=values(`DIV`),`TOTAL`=values(`TOTAL`);";
80
81// Just another set of SQL statements;
82$sql_final .= "select @i:=0; insert into `$table_name` (`studentId`,`POS`) select `studentId`,@i:=@i+1 from `$table_name` order by total desc, mathematics desc, english desc on duplicate key update `POS`=values(`POS`);";
83
84// echo $sql_final;
85// inserting the data
86flush_multi_query($sql_final);
87
88
89// writing the csv file
90if(file_exists('../../csv/report.csv')){
91    unlink('../../csv/report.csv');
92}
93// echo $field_csv;
94$sql = "select concat(s.fname,' ',s.lname) as name,$field_csv,`TAG`,`DIV`,`TOTAL`,`POS` from `$table_name` as dr join students as s on s.id=dr.studentId order by POS asc";
95$query = $db->query($sql) or die($db->error);
96$fs = str_replace('dr.','',$field_csv);
97$fs_properFields = preg_replace(array('/([a-zA-Z0-9 ])*(Ag)/i','/`/i'),array('Ag',''),$fs);
98$fs = str_replace('`','',$fs);
99$csv_data = 'STUDENT NAME,'.$fs_properFields.",AGG,DIV,TOTAL,POS\n";
100$fs_array = explode(',',$fs);
101while($data = $query->fetch_assoc()){
102    $csv_data .= $data['name'];
103    for($i = 0; $i < count($fs_array); $i++){
104        $csv_data .= ','.$data[$fs_array[$i]];
105    }
106    $csv_data .= ','.$data['TAG'].','.$data['DIV'].','.$data['TOTAL'].','.$data['POS']."\n";
107}
108$file = fopen('../../csv/report.csv','a+');
109fwrite($file,$csv_data);
110// echo $sql;
111?>