1: <?php
2:
3: /**
4: * Mapeo de la tabla promedios por subperiodo por estudiante
5: * @package modelos
6: * @author Fernando Salas <rfsalas@rutatec.com>
7: * @since 2016-02-23
8: */
9: class StudentSubjectSubperiodAverage extends Eloquent {
10:
11: /**
12: * @var var para mapeo de la tabla curso con el nombre que le corresponde en la base de datos
13: */
14: protected $table = 'student_subject_subperiod_average';
15:
16: /**
17: * @var var llave primaria utilizada en la tabla student_subject_subperiod_average
18: */
19: protected $primaryKey = 'serial_sss';
20:
21: /**
22: * @var var de tiempo
23: */
24: public $timestamps = false;
25:
26: /**
27: * Metodo para obtener los promedios de las materias obligatorias
28: *
29: * @param integer serial_sbc
30: * @param integer serial_sbp
31: * @param integer serial_std
32: * @param integer serial_lvl
33: * @param integer serial_scy
34: * @param integer serial_sbc
35: * @param integer serial_set
36: *
37: *
38: * @return lista de materias obligatorias
39: * @throws InvalidArgumentException
40: * @since 2016-02-24
41: * @author Fernando Salas <rfsalas@rutatec.com>
42: *
43: * @edit 2016-02-24<br />
44: * Fernando Salas <rfsalas@rutatec.com><br />
45: * documentacion del metodo<br/>
46: * #edit1
47: */
48: public function getAverageByMandatorySubject($serial_sbc, $serial_sbp, $serial_std, $serial_spt, $serial_lvl, $serial_scy, $serial_set) {
49: return $this->where('serial_sbc', $serial_sbc)
50: ->where('serial_sbp', $serial_sbp)
51: ->where('serial_std', $serial_std)
52: ->where('serial_spt', $serial_spt)
53: ->where('serial_lvl', $serial_lvl)
54: ->where('serial_scy', $serial_scy)
55: ->where('serial_set', $serial_set)
56: ->select('average_sss')
57: ->first();
58: }
59:
60: /**
61: * Metodo para obtener los promedios de las materias optativas
62: *
63: * @param var serial_lop
64: * @param var serial_sbp
65: * @param var serial_std
66: * @param var serial_spt
67: * @param var serial_lvl
68: * @param var serial_scy
69: * @param var serial_sbc
70: * @param var serial_set
71: *
72: *
73: * @return lista de materias optativas
74: * @throws InvalidArgumentException
75: * @since 2016-02-24
76: * @author Fernando Salas <rfsalas@rutatec.com>
77: *
78: * @edit 2016-02-24<br />
79: * Fernando Salas <rfsalas@rutatec.com><br />
80: * documentacion del metodo<br/>
81: * #edit1
82: */
83: public function getAverageByOptionalSubject($serial_lop, $serial_sbp, $serial_std, $serial_spt, $serial_lvl, $serial_scy, $serial_set) {
84: return $this->where('serial_lop', $serial_lop)
85: ->where('serial_sbp', $serial_sbp)
86: ->where('serial_std', $serial_std)
87: ->where('serial_spt', $serial_spt)
88: ->where('serial_lvl', $serial_lvl)
89: ->where('serial_scy', $serial_scy)
90: ->where('serial_set', $serial_set)
91: ->select('average_sss')
92: ->first();
93: }
94:
95: /**
96: * Valida los datos de entrada para ver los promedios
97: *
98: * @param void
99: *
100: * @return array codigo estudiante,dia
101: * @throws InvalidArgumentException
102: * @since 2016-02-23
103: * @author Fernando Salas <rfsalas@rutatec.com>
104: *
105: * @edit 2016-02-23<br />
106: * Fernando Salas <rfsalas@rutatec.com><br />
107: * documentacion del metodo<br/>
108: * #edit1
109: */
110: public static function rulesSubjectSubperiodAverange() {
111: return array(
112: 'serial_std' => 'required|integer',
113: 'serial_sbp' => 'required|integer',
114: );
115: }
116:
117: /**
118: * Valida los datos de entrada para ver los promedios
119: *
120: * @param void
121: *
122: * @return array codigo estudiante,dia
123: * @throws InvalidArgumentException
124: * @since 2016-02-23
125: * @author Fernando Salas <rfsalas@rutatec.com>
126: *
127: * @edit 2016-02-23<br />
128: * Fernando Salas <rfsalas@rutatec.com><br />
129: * documentacion del metodo<br/>
130: * #edit1
131: */
132: public function SubperiodAverage($serial_std, $serial_sbp) {
133: $sqlfinal = "select sss.serial_sbc,csb.cat_serial_csb,csb.name_csb, sss.serial_lop,
134: sss.average_sss
135: from student_subject_subperiod_average sss
136: join subject_course sbc on sbc.serial_sbc=sss.serial_sbc
137: join subject sbj on sbj.serial_sbj=sbc.serial_sbj
138: join cat_subject csb on sbj.serial_csb=csb.serial_csb
139: join school_year sy on sy.serial_scy=sss.serial_scy
140: where sss.serial_std=$serial_std and sy.status_scy='ACTIVE,CURRENT' and sbc.status_sbc='ACTIVE'
141: and sss.serial_sbp=$serial_sbp
142: ";
143:
144:
145: $sql1 = DB::table('student_subject_subperiod_average')
146: // ->from('student_subject_subperiod_average AS sss')
147: //->select('sss.serial_sbc,csb.cat_serial_csb,csb.name_csb, sss.serial_lop,sss.average_sss')
148: ////
149: ->join('subject_course', function($join) {
150: $join->on('subject_course.serial_sbj', '=', 'student_subject_subperiod_average.serial_sbj')
151: ->where('subject_course.status_sbc', '=', 'ACTIVE');
152: //->where('student_by_course.status_stc', '=', 'ACTIVE');
153: })
154: //
155: //->join('subject_course AS sbc', 'sbc.serial_sbc', '=', 'sss.serial_sbc ')
156: ->join('subject AS sbj', 'sbj.serial_sbj', '=', 'sbc.serial_sbj')
157: ->join('cat_subject AS csb', ' sbj.serial_csb', '=', 'csb.serial_csb')
158: ->join('school_year AS sy', 'sy.serial_scy', '=', 'sss.serial_scy')
159: //->join('activity_type AS att ', 'student_criteria_grade.serial_att', '=', 'att.serial_att')
160: // ->whereBetween('act.due_date_act', array($day, $day))
161: ->where('sss.serial_std', $serial_std)
162: ->where('sy.status_scy', 'ACTIVE,CURRENT')
163: ->where('sbc.status_sbc', 'ACTIVE')
164: ->Where('sss.serial_sbp', $serial_sbp)
165: //->orderBy('csb.name_csb')
166: ->get();
167: $message = DB::select(DB::raw($sql1));
168: $return_array = array(
169: 'serial_act' => $message[0]->serial_sbc,
170: 'activity_type' => $message[0]->cat_serial_csb,
171: 'name_act' => $message[0]->name_csb,
172: 'name_att' => $message[0]->serial_lop,
173: 'name_csb' => $message[0]->average_sss,
174: );
175: $sql2 = StudentSubjectSubperiodAverage::
176: selectRaw("sss.serial_sbc,' ',lop.name_lop,sss.serial_lop,sss.average_sss")
177: ->join('level_optional_group AS lop', 'lop.serial_lop', '=', 'sss.serial_lop')
178: ->join('school_year AS sy', 'sy.serial_scy', '=', 'sss.serial_scy')
179: //->join('cat_subject AS csb', ' sbj.serial_csb','=','csb.serial_csb')
180: //->join('school_year AS sy', 'sy.serial_scy','=','sss.serial_scy' )
181: //->join('activity_type AS att ', 'student_criteria_grade.serial_att', '=', 'att.serial_att')
182: // ->whereBetween('act.due_date_act', array($day, $day))
183: ->where('sss.serial_std', $serial_std)
184: ->where('sy.status_scy', 'ACTIVE,CURRENT')
185: ->where('lop.status_lop', 'ACTIVE')
186: ->Where('sss.serial_sbp', $serial_sbp)
187: ->union($sql1)
188: //->orderBy('csb.name_csb')
189: ->get();
190: // $message = DB::select($sql1);
191: // $message = var_dump($user->serial_std);
192:
193: return $return_array;
194:
195: // foreach ($sql1 as $val) {
196: // $attach_files = DB::table('activity_attachment AS aca')
197: // ->select('aca.name_ath', 'aca.filename_ath')
198: // ->where('serial_act', $val->serial_act)
199: // ->where('status_ath', 'ACTIVE')
200: // ->get();
201: // if ($attach_files) {
202: // $val = array_add($val, 'optative', 'NO');
203: // $val = array_add($val, 'attached_file', 'YES');
204: // } else {
205: // $val = array_add($val, 'optative', 'NO');
206: // $val = array_add($val, 'attached_file', 'NO');
207: // }
208: // $return_array[] = $val['attributes'];
209: // }
210: // $sql3 = OptStudentCriteriaGrade::
211: // selectRaw('distinct act.serial_opa as serial_act , scat.name_scat as name_csb, act.name_opa as name_act, act.description_opa as description_act, att.serial_att as activity_type')
212: // ->join('optional_subject AS osb', 'opt_student_criteria_grade.serial_osb', '=', 'osb.serial_osb')
213: // ->join('cat_subject AS csb', 'osb.serial_csb', '=', 'csb.serial_csb')
214: // ->join('opt_activity AS act', 'act.serial_opa', '=', 'opt_student_criteria_grade.serial_opa')
215: // ->join('optional_subject_level AS osbl', 'osb.serial_osb', '=', 'osbl.serial_osb')
216: // ->join('subject_category AS scat', 'osbl.serial_scat', '=', 'scat.serial_scat')
217: // ->join('activity_type AS att', 'opt_student_criteria_grade.serial_att', '=', 'att.serial_att')
218: // ->whereBetween('act.due_date_opa', array($day, $day))
219: // ->where('opt_student_criteria_grade.serial_std', $serial_std)
220: // ->where('act.status_opa', 'ACTIVE')
221: // ->orderBy('name_csb')
222: // ->get();
223: //
224: // foreach ($sql2 as $value) {
225: // $attach_files = DB::table('opt_activity_attachment AS aca')
226: // ->select('aca.name_oat', 'aca.filename_oat')
227: // ->where('serial_oat', $value->serial_act)
228: // ->where('status_oat', 'ACTIVE')
229: // ->get();
230: // if ($attach_files) {
231: // $value = array_add($value, 'optative', 'YES');
232: // $value = array_add($value, 'attached_file', 'YES');
233: // } else {
234: // $value = array_add($value, 'optative', 'YES');
235: // $value = array_add($value, 'attached_file', 'NO');
236: // }
237: // $return_array[] = $value['attributes'];
238: // }
239: //
240: // return $return_array;
241: }
242:
243: public function postStudentSubperiodAverange() {
244: $sql = DB::table('student_subject_subperiod_average')
245: ->join('subject_course', function($join) {
246: $join->on('subject_course.serial_sbc', '=', 'student_subject_subperiod_average.serial_sbc')
247: ->where('subject_course.status_sbc', '=', 'ACTIVE');
248: // ->where('student_by_course.status_stc', '=', 'ACTIVE');
249: })
250: ->join('subject', function($join) {
251: $join->on('subject.serial_sbj', '=', 'student_subject_subperiod_average.serial_sbj');
252: //->where('course.status_crs', '=', 'ACTIVE');
253: })
254: ->join('cat_subject', function($join) {
255: $join->on('cat_subject.serial_csb', '=', 'student_subject_subperiod_average.serial_csb');
256: })
257: ->join('school_year', function($join) {
258: $join->on('school_year.serial_scy', '=', 'student_subject_subperiod_average.serial_scy')
259: ->where('school_year.status_scy', '=', 'ACTIVE,CURRENT');
260: })
261: ->join('class_group', function($join) {
262: $join->on('course.serial_clg', '=', 'class_group.serial_clg')->where('class_group.status_clg', '=', 'ACTIVE');
263: })
264: ->where('student.status_std', '=', 'ACTIVE')
265: ->select('course.serial_crs', 'section.serial_set', 'section.name_set', 'level.serial_lvl', 'level.name_lvl', 'class_group.serial_clg', 'class_group.name_clg')
266: ->first();
267: return $this;
268: }
269:
270: }
271: