1: <?php
  2: 
  3: /**
  4:  * Mapeo de la tabla Actividades
  5:  * @package modelos
  6:  * @author Fernando Salas <rfsalas@rutatec.com>
  7:  * @since  2016-02-23
  8:  */
  9: class Activity extends Eloquent {
 10: 
 11:     /**
 12:      * @string variable para mapeo de la tabla actividades con el nombre que le corresponde en la base de datos
 13:      */
 14:     protected $table = 'activity';
 15: 
 16:     /**
 17:      *@string variable llave primaria utilizada en la tabla actividades
 18:      */
 19:     protected $primaryKey = 'serial_act';
 20: 
 21:     /**
 22:      *@string variable tiempo en falso
 23:      */
 24:     public $timestamps = false;
 25: 
 26:     /**
 27:      * @var var 
 28:      */
 29:     public $attach_path = 'rutademic/DEV/files/';
 30: 
 31:     /**
 32:      * Valida las reglas de las actividades por fechas
 33:      *
 34:      * @param   void
 35:      * 
 36:      * @return  array serial_std,date month,date year
 37:      * @throws  InvalidArgumentException
 38:      * @since   2016-02-23
 39:      * @author  Fernando Salas <rfsalas@rutatec.com>
 40:      *
 41:      * @edit    2016-02-23<br />
 42:      *          Fernando Salas <rfsalas@rutatec.com><br />
 43:      *          documentacion del metodo<br/>
 44:      *          #edit1
 45:      */
 46:     public static function rulesactivityDate() {
 47:         return array(
 48:             'serial_std' => 'required|integer',
 49:             'month' => 'required',
 50:             'year' => 'required',
 51:         );
 52:     }
 53: 
 54:     /**
 55:      * Valida las reglas de las actividades por dia
 56:      *
 57:      * @param   void
 58:      * 
 59:      * @return  array serial_std,date
 60:      * @throws  InvalidArgumentException
 61:      * @since   2016-02-23
 62:      * @author  Fernando Salas <rfsalas@rutatec.com>
 63:      *
 64:      * @edit    2016-02-23<br />
 65:      *          Fernando Salas <rfsalas@rutatec.com><br />
 66:      *          documentacion del metodo<br/>
 67:      *          #edit1
 68:      */
 69:     public static function rulesActivitiesByDay() {
 70:         return array(
 71:             'serial_std' => 'required|integer',
 72:             'day' => 'required|date',
 73:         );
 74:     }
 75: 
 76:     /**
 77:      * Detalle de las reglas de actividades
 78:      *
 79:      * @param   void
 80:      * 
 81:      * @return  array codigo de actividad,optativa
 82:      * @throws  InvalidArgumentException
 83:      * @since   2016-02-23
 84:      * @author  Fernando Salas <rfsalas@rutatec.com>
 85:      *
 86:      * @edit    2016-02-23<br />
 87:      *          Fernando Salas <rfsalas@rutatec.com><br />
 88:      *          documentacion del metodo<br/>
 89:      *          #edit1
 90:      */
 91:     public static function rulesActivityDetails() {
 92:         return array(
 93:             'serial_act' => 'required|integer',
 94:             'optative' => 'required',
 95:         );
 96:     }
 97: 
 98:     /**
 99:      * Hora inicio y final de cada actividad
100:      *
101:      * @param   integer $serial_std
102:      * @param   date $start_date
103:      * @param   date end_date
104:      * @return  json $messaage
105:      * @throws  InvalidArgumentException
106:      * @since   2016-02-23
107:      * @author  Fernando Salas <rfsalas@rutatec.com>
108:      *
109:      * @edit    2016-02-23<br />
110:      *          Fernando Salas <rfsalas@rutatec.com><br />
111:      *          documentacion del metodo<br/>
112:      *          #edit1
113:      */
114:     public function activityDates($serial_std, $start_date, $end_date) {
115:         $sql = "(select distinct DATE_FORMAT(act.due_date_act, '%d-%m-%Y') as date_date from student_criteria_grade
116:         inner join activity as act on act.serial_act=student_criteria_grade.serial_act
117:         where act.due_date_act between '$start_date' and '$end_date'
118:         and act.status_act='ACTIVE'
119:         and student_criteria_grade.serial_std=$serial_std
120:         order by act.due_date_act)
121:         union
122:         (select distinct DATE_FORMAT(act.due_date_opa, '%d-%m-%Y') as date_date from opt_student_criteria_grade
123:         inner join opt_activity as act on act.serial_opa=opt_student_criteria_grade.serial_opa
124:         where act.due_date_opa between '$start_date' and '$end_date' 
125:         and act.status_opa='ACTIVE'
126:         and opt_student_criteria_grade.serial_std=$serial_std
127:         order by act.due_date_opa)
128:         order by date_date";
129:         $message = DB::select(DB::raw($sql));
130:         return $message;
131:     }
132: 
133:     /**
134:      * Actividades por dia
135:      *
136:      * @param   date day
137:      * @param   integer serial_std
138:      * @return  array activities
139:      * @throws  InvalidArgumentException
140:      * @since   2016-02-23
141:      * @author  Fernando Salas <rfsalas@rutatec.com>
142:      *
143:      * @edit    2016-02-23<br />
144:      *          Fernando Salas <rfsalas@rutatec.com><br />
145:      *          documentacion del metodo<br/>
146:      *          #edit1
147:      */
148:     public function ActivitiesByDay($day, $serial_std) {
149:         $return_array = array();
150:         $sql = StudentCriteriaGrade::
151:                 selectRaw('distinct act.serial_act, csb.name_csb, act.name_act, act.description_act, att.serial_att as activity_type')
152:                 ->join('subject_course AS sbc', 'student_criteria_grade.serial_sbc', '=', 'sbc.serial_sbc')
153:                 ->join('subject AS sbj', 'sbc.serial_sbj', '=', 'sbj.serial_sbj')
154:                 ->join('cat_subject AS csb', 'sbj.serial_csb', '=', 'csb.serial_csb')
155:                 ->join('activity AS act', 'act.serial_act', '=', 'student_criteria_grade.serial_act')
156:                 ->join('activity_type AS att ', 'student_criteria_grade.serial_att', '=', 'att.serial_att')
157:                 ->whereBetween('act.due_date_act', array($day, $day))
158:                 ->where('student_criteria_grade.serial_std', $serial_std)
159:                 ->where('act.status_act', 'ACTIVE')
160:                 ->orderBy('csb.name_csb')
161:                 ->get();
162:         foreach ($sql as $val) {
163:             $attach_files = DB::table('activity_attachment AS aca')
164:                     ->select('aca.name_ath', 'aca.filename_ath')
165:                     ->where('serial_act', $val->serial_act)
166:                     ->where('status_ath', 'ACTIVE')
167:                     ->get();
168:             if ($attach_files) {
169:                 $val = array_add($val, 'optative', 'NO');
170:                 $val = array_add($val, 'attached_file', 'YES');
171:             } else {
172:                 $val = array_add($val, 'optative', 'NO');
173:                 $val = array_add($val, 'attached_file', 'NO');
174:             }
175:             $return_array[] = $val['attributes'];
176:         }
177: 
178:         $sql2 = OptStudentCriteriaGrade::
179:                 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')
180:                 ->join('optional_subject AS osb', 'opt_student_criteria_grade.serial_osb', '=', 'osb.serial_osb')
181:                 ->join('cat_subject AS csb', 'osb.serial_csb', '=', 'csb.serial_csb')
182:                 ->join('opt_activity AS act', 'act.serial_opa', '=', 'opt_student_criteria_grade.serial_opa')
183:                 ->join('optional_subject_level AS osbl', 'osb.serial_osb', '=', 'osbl.serial_osb')
184:                 ->join('subject_category AS scat', 'osbl.serial_scat', '=', 'scat.serial_scat')
185:                 ->join('activity_type AS att', 'opt_student_criteria_grade.serial_att', '=', 'att.serial_att')
186:                 ->whereBetween('act.due_date_opa', array($day, $day))
187:                 ->where('opt_student_criteria_grade.serial_std', $serial_std)
188:                 ->where('act.status_opa', 'ACTIVE')
189:                 ->orderBy('name_csb')
190:                 ->get();
191: 
192:         foreach ($sql2 as $value) {
193:             $attach_files = DB::table('opt_activity_attachment AS aca')
194:                     ->select('aca.name_oat', 'aca.filename_oat')
195:                     ->where('serial_oat', $value->serial_act)
196:                     ->where('status_oat', 'ACTIVE')
197:                     ->get();
198:             if ($attach_files) {
199:                 $value = array_add($value, 'optative', 'YES');
200:                 $value = array_add($value, 'attached_file', 'YES');
201:             } else {
202:                 $value = array_add($value, 'optative', 'YES');
203:                 $value = array_add($value, 'attached_file', 'NO');
204:             }
205:             $return_array[] = $value['attributes'];
206:         }
207: 
208:         return $return_array;
209:     }
210: 
211:     /**
212:      * Detalle de las actividades
213:      *
214:      * @param   integer serial_act
215:      * @param   String optative
216:      * @return  array
217:      * @throws  InvalidArgumentException
218:      * @since   2016-02-23
219:      * @author  Fernando Salas <rfsalas@rutatec.com>
220:      *
221:      * @edit    2016-02-23<br />
222:      *          Fernando Salas <rfsalas@rutatec.com><br />
223:      *          documentacion del metodo<br/>
224:      *          #edit1
225:      */
226:     public function ActivityDetails($serial_act, $optative) {
227:         if ($optative == 'NO') {
228:             $sql = "select distinct act.serial_act, csb.name_csb, act.name_act, act.description_act, DATE_FORMAT(act.send_date_act, '%W-%e-%M-%Y') as send_date, DATE_FORMAT(act.due_date_act, '%W-%e-%M-%Y') as due_date, att.serial_att as activity_type, att.name_att
229:                 from student_criteria_grade
230:                 inner join subject_course as sbc on student_criteria_grade.serial_sbc=sbc.serial_sbc
231:                 inner join subject as sbj on sbc.serial_sbj=sbj.serial_sbj
232:                 inner join cat_subject as csb on sbj.serial_csb=csb.serial_csb
233:                 inner join activity as act on act.serial_act=student_criteria_grade.serial_act
234:                 inner join activity_type as att on student_criteria_grade.serial_att=att.serial_att
235:                 where act.serial_act=$serial_act 
236:                 and status_act='ACTIVE'
237:                 order by csb.name_csb";
238:             $message = DB::select(DB::raw($sql));
239:             $attach_files = DB::table('activity_attachment AS aca')
240:                     ->select('aca.name_ath', 'aca.filename_ath')
241:                     ->where('serial_act', $serial_act)
242:                     ->where('status_ath', 'ACTIVE')
243:                     ->get();
244:             $return_array = array(
245:                 'serial_act' => $message[0]->serial_act,
246:                 'activity_type' => $message[0]->activity_type,
247:                 'name_act' => $message[0]->name_act,
248:                 'name_att' => $message[0]->name_att,
249:                 'name_csb' => $message[0]->name_csb,
250:                 'description_act' => $message[0]->description_act,
251:                 'send_date' => $message[0]->send_date,
252:                 'due_date' => $message[0]->due_date,
253:             );
254:             if ($attach_files) {
255:                 $return_array['name_ath'] = $attach_files[0]->name_ath;
256:                 $return_array['filename_ath'] = $attach_files[0]->filename_ath;
257:                 $return_array['path'] = $this->attach_path . 'activity_attachment/' . $return_array['serial_act'] . '/';
258:                 return $return_array;
259:             } else {
260:                 $return_array['name_ath'] = '';
261:                 $return_array['filename_ath'] = '';
262:                 $return_array['path'] = '';
263:                 return $return_array;
264:             }
265:         } else if ($optative == 'YES') {
266:             $sql2 = "select 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, DATE_FORMAT(act.send_date_opa, '%W-%e-%M-%Y') AS send_date, DATE_FORMAT(act.due_date_opa, '%W-%e-%M-%Y') AS due_date, att.serial_att as activity_type, att.name_att
267:                 from opt_student_criteria_grade
268:                 inner join optional_subject as osb on opt_student_criteria_grade.serial_osb=osb.serial_osb
269:                 inner join cat_subject as csb on osb.serial_csb=csb.serial_csb
270:                 inner join opt_activity as act on act.serial_opa=opt_student_criteria_grade.serial_opa
271:                 inner join optional_subject_level as osbl on osb.serial_osb=osbl.serial_osb
272:                 inner join subject_category as scat on osbl.serial_scat=scat.serial_scat
273:                 inner join activity_type as att on opt_student_criteria_grade.serial_att=att.serial_att
274:                 where act.serial_opa=$serial_act
275:                 and status_opa='ACTIVE'
276:                 order by csb.name_csb";
277:             $message2 = DB::select(DB::raw($sql2));
278:             $attach_files = DB::table('opt_activity_attachment AS aca')
279:                     ->select('aca.name_oat', 'aca.filename_oat')
280:                     ->where('serial_opa', $serial_act)
281:                     ->where('status_oat', 'ACTIVE')
282:                     ->get();
283:             $return_array = array(
284:                 'serial_act' => $message2[0]->serial_act,
285:                 'activity_type' => $message2[0]->activity_type,
286:                 'name_act' => $message2[0]->name_act,
287:                 'name_att' => $message2[0]->name_att,
288:                 'name_csb' => $message2[0]->name_csb,
289:                 'description_act' => $message2[0]->description_act,
290:                 'send_date' => $message2[0]->send_date,
291:                 'due_date' => $message2[0]->due_date,
292:             );
293:             if ($attach_files) {
294:                 $return_array['name_ath'] = $attach_files[0]->name_oat;
295:                 $return_array['filename_ath'] = $attach_files[0]->filename_oat;
296:                 $return_array['path'] = $this->attach_path . 'opt_activity_attachment/' . $return_array['serial_act'] . '/';
297:                 return $return_array;
298:             } else {
299:                 $return_array['name_ath'] = '';
300:                 $return_array['filename_ath'] = '';
301:                 $return_array['path'] = '';
302:                 return $return_array;
303:             }
304:         }
305:     }
306: 
307: }
308: