<?php
/**
* Created by PhpStorm.
* User: Hugh
* Date: 2/11/2016
* Time: 11:56 PM
*/
class importPlan
{
function startImportPlan()
{
$db = FabrikWorker::getDbo(false, 3);
$user =& JFactory::getUser();
$regby=$user->get('username');
$db->setQuery("delete from plan_ot_submit where regby='$regby'");
$db->query();
return true;
}
function afterImportPlanRow()
{
// File to run after row is imported
$db = FabrikWorker::getDbo(false, 3);
$user =& JFactory::getUser();
$application = JFactory::getApplication();
$regby=$user->get('username');
$reqname=$user->get('name');
$Datereg = date("Y-m-d");
$current_day = date("N");//This return a numeric digit Friday=5
$days_to_friday = 5 - $current_day;
$Fridate = date("Y-m-d", strtotime("+ {$days_to_friday} Days"));
//Delete Illegal Upload
$db->setQuery("DELETE FROM plan_ot_submit where (length(sdate)=0 or length(empno)=0 or length(planot)=0) and regby='$regby' and sys_date=curdate()");
$db->query();
//Update sdate to proper date value
$db->setQuery("update plan_ot_submit a set kostl=(select mid(kostl,4,6) from stm_employee b where a.empno=mid(b.sapno,3,8) group by kostl), errcode=2,sdate=date_format(str_to_date(sdate, '%Y%m%d'),'%Y-%m-%d') where length(sdate)=8 and regby='$regby' and sys_date=curdate()");
$db->query();
//Delete Duplicate Record
$db->setQuery("Delete a FROM plan_ot_submit a JOIN (SELECT MAX(t.id) AS max_a1, t.sdate, t.empno FROM plan_ot_submit t GROUP BY t.sdate, t.empno HAVING COUNT(*) > 1) b
ON b.sdate = a.sdate and a.empno=b.empno AND b.max_a1 != a.id and a.regby='$regby'");
$db->query();
//Check if employee valid comparing Cost Centre
$db->setQuery("update plan_ot_submit a set errcode=1,remark='..Invalid employee, this employee not from your dept.' where (select mid(kostl,4,6) from stm_employee b where a.empno=mid(b.sapno,3,8)) not in (SELECT kostl FROM planot_admin where userid='$regby' and (role='ADMIN' or role='ADMIN_BAK')) and regby='$regby' and sys_date=curdate()");
$db->query();
$db->setQuery("update plan_ot_submit set errcode=1,remark='..invalid employee no.' where (length(empno)>10 or mid(empno,1,2)='oa') and regby='$regby' and sys_date=curdate()") ;
$db->query();
$db->setQuery("update plan_ot_submit set errcode=1,remark='..this employee no longer exist.' where empno not in (select mid(sapno,3,10) from stm_employee) and regby='$regby' and sys_date=curdate()") ;
$db->query();
//Error Checking For Submitted Record
// Plan OT Value Range Check
$db->setQuery("update plan_ot_submit set errcode=1,remark='..Plan OT value cannot exceed 22.5 or < than 0' where (planot>22.5 or planot<0) and sys_date=curdate() and regby='$regby' and sys_date=curdate() ");
$db->query();
// Date Validation
$db->setQuery("update plan_ot_submit set errcode=1,remark='..Invalid date format' where (sdate is null or length(sdate)<>10 or instr(sdate,'-')=0) and regby='$regby' and sys_date=curdate()");
$db->query();
//Checking of submission of past date
$db->setQuery("update plan_ot_submit set errcode=1,remark='..no submission of past date' where sdate < '$Datereg' and regby='$regby' and sys_date=curdate()") ;
$db->query();
//Checking of advance submission of public holiday not allowed
//dayname(sdate) not in ('Saturday','Sunday','Friday')
$db->setQuery("update plan_ot_submit set errcode=1,remark='..too early to submit public holiday' where sdate <> '$Datereg' and instr((Select ph from ph where active='1' and remark='PH'),sdate)>0 and datediff(sdate,current_date)>4 and regby='$regby' and sys_date=curdate()");
$db->query();
//Checking of advance submission of non public holiday week
$db->setQuery("update plan_ot_submit set errcode=1,remark='..no advanced submission' where sdate > '$Datereg' and instr((Select ph from ph where active='1' and remark='PH'),sdate)=0 and dayname(sdate) not in ('Saturday','Sunday') and regby='$regby' and sys_date=curdate()");
$db->query();
//Prevent advance submission of Sat and Sunday before Friday
$db->setQuery("update plan_ot_submit set errcode=1,remark='..weekend submission only allowed on Friday or Thursday if Friday is a holiday.' where dayname(sdate) in ('Saturday','Sunday') and (dayname(curdate()) not in ('Friday','Saturday','Sunday') and instr((select ph from ph where active='1' and remark='PH'),'$Fridate')=0 and regby='$regby' and sys_date=curdate())");
$db->query();
//Alert User error message
$query = "SELECT count(id) FROM plan_ot_submit where errcode=1 and regby='$regby' and sys_date=curdate()";
$db->setQuery($query);
$errcount= $db->loadResult();
//$URL="http://stmportal/index.php?option=com_fabrik&view=table&tableid=340&calculations=0&resetfilters=1&Itemid=1184";
//header ("Location: $URL");
if($errcount>0){
$application->enqueueMessage(JText::_('Please check the respective record there are some error detected.'), 'error');
} else {
$application->enqueueMessage(JText::_('Verification completed, there are no error detected, you may proceed to print and submit these records.'), 'info');
}
}
}
