public function loadUserByUsername($username)
{
$userData = DB::getInstance()->getUserData($username);
if ($userData) {
$password = array_key_exists('pass', $userData) ? $userData['pass'] : null;
$role_id = array_key_exists('role_id', $userData) ? $userData['role_id'] : null;
switch ($role_id) {
case 1:
$role = ['ROLE_PUPIL'];
break;
case 2:
$role = ['ROLE_TEACHER'];
break;
case 3:
$role = ['ROLE_BOSS'];
break;
case 4:
$role = ['ROLE_FOOD'];
break;
case 5:
$role = ['ROLE_CLIENT'];
break;
default:
throw new UnsupportedUserException(sprintf('User "%s" has a wrong ROLE_ID: "%s".', $username, $userData['ROLE_ID']));
}
return new User($username, $password, $role, $userData['usr_id'], $userData['usr_name'], $userData['tlph'], $userData['email'], $userData['role_id'], $userData['limit'], $userData['prt_id'], $userData['prt_name'], $userData['blnc']);
}
throw new UsernameNotFoundException(sprintf('Username "%s" does not exist.', $username));
}
public function countSchoolAction()
{
try {
$count = DB::getInstance()->getCountSchool()['cnt'];
return new Response($count);
} catch (Exception $e) {
return new Response(17);
}
}
public function getConclusionThirdByPeriod($parameters)
{
$query = "with x as (\n Select (case when r.is_cplx = 0 then 0 else (case when r.IS_BJT = 0 then r.COST*r.CNT when r.IS_BJT = 1 then r.COST_BJT*r.cnt else 0 end) end) as price_compl\n , (case when r.is_cplx = 1 then 0 else (case when r.IS_BJT = 0 then r.COST*r.CNT when r.IS_BJT = 1 then r.COST_BJT*r.cnt else 0 end) end) as price_ind\n , (case when r.IS_BJT = 1 then r.COST_BJT*r.CNT else r.COST*r.cnt end) as total_price\n from CS_SHKET.RLS r\n inner join CS_SHKET.DMEAL dm on dm.MEAL_ID = r.MEAL_ID and r.ADATE = dm.ADATE and dm.MAIN_MEAL_ID = 0\n inner join CS_SHKET.MEAL m on m.MEAL_ID = dm.MEAL_ID\n where r.ADATE between ? and ?\n and r.scl_id = ?\n and r.del <> 1 and dm.del <> 1 and m.del <> 1\n )\n Select\n isnull(SUM(x.price_compl), 0) as sum_compl\n , isnull(SUM(x.price_ind), 0) as sum_ind\n , isnull(SUM(x.total_price), 0) as total_price\n from x";
$date_from = substr($parameters['date_from'], -4) . "-" . substr($parameters['date_from'], 3, 2) . "-" . substr($parameters['date_from'], 0, 2);
$date_to = substr($parameters['date_to'], -4) . "-" . substr($parameters['date_to'], 3, 2) . "-" . substr($parameters['date_to'], 0, 2);
$result = DB::getInstance()->getAll($query, [$date_from, $date_to, $parameters['school_id']], PDO::FETCH_NUM);
return $result;
}
public function getBalanceDetail($parameters)
{
$query = "Select CONVERT(nvarchar, blnc.ddate, 104) as dt, CONVERT(nvarchar, blnc.ddate, 108) as tm\n , blnc.ddate, acr.name as type_name, u.name as user_name\n , case when blnc.summ < 0 then abs(blnc.SUMM) else null end as minus\n , case when blnc.summ >= 0 then blnc.SUMM else null end as plus\n ,(select sum(b.SUMM)\n from CS_SHKET.BLNC b\n where b.USR_ID = u.usr_id\n and b.DDATE <= blnc.ddate\n and b.del <> 1\n ) as bal, cls.name as cls_name\n from cs_shket.usr u\n inner join cs_shket.user_in_scl_cls uic on uic.usr_id = u.usr_id\n inner join cs_shket.blnc blnc on u.usr_id = blnc.usr_id\n inner join cs_shket.cls cls on uic.cls_id = cls.cls_id\n inner join cs_shket.acr acr on acr.acr_id = blnc.acr_id\n where u.usr_id = ?\n and u.role_id = 1\n and blnc.del <> 1\n and (acr.acr_id = ? or ? = -1)\n and cast(blnc.ddate as date) between ? and ?\n order by blnc.ddate desc\n ";
$date_from = substr($parameters['date_from'], -4) . "-" . substr($parameters['date_from'], 3, 2) . "-" . substr($parameters['date_from'], 0, 2);
$date_to = substr($parameters['date_to'], -4) . "-" . substr($parameters['date_to'], 3, 2) . "-" . substr($parameters['date_to'], 0, 2);
$result = DB::getInstance()->getAll($query, [$parameters['user_id'], $parameters['acr_id'], $parameters['acr_id'], $date_from, $date_to]);
return $result;
}
public function getUserInfoById($user_id)
{
$query = " select usr.name as user_name, a.scl_id, a.cls_id, scl.name as scl_name, cls.name as cls_name, tr.NAME, tr.COST, tr.TRF_ID, tr.inf_bal, tr.inf_ent, tr.inf_eat\n from cs_shket.USER_IN_SCL_CLS a, cs_shket.cls, cs_shket.scl, cs_shket.usr\n , (select trf.trf_id, trf.NAME, trf.COST, usr.USR_ID\n , inf_bal, INF_ENT, INF_EAT\n from CS_SHKET.TRf, CS_SHKET.USR\n where usr.TRF_ID = trf.TRF_ID ) tr\n where a.del <> 1 and cls.del <> 1 and scl.del <> 1\n and a.usr_id = ? and a.scl_id = scl.scl_id\n and a.scl_id = cls.scl_id\n and a.cls_id = cls.cls_id\n and usr.usr_id = a.usr_id\n and tr.USR_ID = a.USR_ID";
$result = DB::getInstance()->getAll($query, [$user_id], PDO::FETCH_ASSOC);
return $result;
}
public function changePupilBjt($parameters)
{
$query = "select IS_BJT from cs_shket.usr where usr_id = ?";
$result = DB::getInstance()->getFirst($query, [$parameters['user_id']]);
if ($result['IS_BJT'] == 1) {
$parameters['is_bjt'] = 0;
} else {
$parameters['is_bjt'] = 1;
}
$query = "update cs_shket.usr set mdate = ?, is_bjt = ?, upd = 1 where usr_id = ?";
DB::getInstance()->getFirst($query, [date('Y-m-d H:i:s'), $parameters['is_bjt'], $parameters['user_id']]);
return $parameters['is_bjt'];
}