public function getAllClasses(User $user)
{
$query = "select distinct cls.cls_id, cls.name\n , cast(case when PATINDEX('%[^0-9]%',cls.name) = 0 then cls.name else LEFT(cls.name, LEN(cls.name)-1) end as int) as num_cls\n , case when PATINDEX('%[^0-9]%',cls.name) = 0 then null else right(cls.name, LEN(cls.name)-1) end as let_cls\n from cs_shket.user_in_scl_cls as uic, cs_shket.cls as cls\n where uic.scl_id in (\n select uic2.scl_id\n from cs_shket.user_in_scl_cls as uic2\n where uic2.usr_id = ?\n and uic2.del <> 1\n )\n and cls.cls_id = uic.cls_id\n and uic.del <> 1 and uic.cls_id <> 0 and cls.del <> 1\n order by num_cls, let_cls";
$result = DB::getInstance()->getAll($query, [$user->getId()]);
return $result;
}
public static function getUserInfo(User $user)
{
$query = "SELECT DISTINCT s.SCL_ID, s.NAME, c.cls_id, c.name\n FROM CS_SHKET.USR u\n INNER JOIN CS_SHKET.USER_IN_SCL_CLS uc on u.USR_ID = uc.USR_ID\n INNER JOIN CS_SHKET.CLS c on c.SCL_ID = uc.SCL_ID AND c.CLS_ID = uc.cls_id\n INNER JOIN CS_SHKET.scl s on s.SCL_ID = uc.SCL_ID\n WHERE u.ROLE_ID = ? and u.del <> 1 and uc.del<> 1 and c.del <> 1 and s.del <> 1\n AND u.USR_ID = ?";
$result = self::$db_instance->getAll($query, [$user->getRoleId(), $user->getId()]);
return $result;
}
public static function getUserInfo(User $user)
{
$query = " select 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\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 tr.USR_ID = a.USR_ID";
$result = DB::getInstance()->getAll($query, [$user->getId()], PDO::FETCH_ASSOC);
return $result;
}