public DataTable SelectDislike(int id, string like) { string query = @"SELECT P.Personid , P.names ,P.lastName ,P.secondLastName ,S.rudeNumber,P.email,P.gender,P.phone ,P.addres ,P.birthDate , P.status , P.registrationDate ,P.updateDate ,P.startDate,P.finishDate,(SELECT C.CityName||'-'||PP.provinceName||', '||T.townName FROM Town T INNER JOIN Province PP ON PP.ProvinceId=T.ProvinceId INNER JOIN City C ON C.CityId=PP.CityId WHERE T.TownId=P.TownId),P.photo , S.studentId FROM Person P left JOIN Student S ON S.PersonId = P.PersonId WHERE S.idCourse = :Course AND P.names||' '||P.lastName||' '||P.secondLastName like :likes AND P.status=0"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[2]; parameters1[0] = new OracleParameter(":Course", id); parameters1[1] = new OracleParameter(":likes", "%" + like + "%"); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable SelectTeacherAdd(int id) { string query = @"SELECT DISTINCT C.idClass,M.matterName SubjectName,CO.numberCourse||CO.letterCourse Course, (select listagg(DAYSS, ', ') within group (order by DAYSS) FROM (SELECT DISTINCT S.DAY DAYSS FROM ClassSchedules CSS INNER JOIN Schedules S ON S.schedulesid= CSS.schedulesid WHERE CSS.idClass = C.idClass)) Days FROM Class C INNER JOIN ClassSchedules CS ON CS.idClass=C.idClass INNER JOIN Matter M ON M.idMatter=C.idMatter INNER JOIN Course CO ON CO.idCourse=C.idCourse WHERE C.status=1 AND ( C.idClass NOT IN (SELECT DISTINCT CS.idClass FROM ClassSchedules CS INNER JOIN Class C ON C.idClass=CS.idClass WHERE CS.schedulesid IN(SELECT CS.schedulesid FROM Teacher T INNER JOIN Class CL ON CL.teacherid=T.teacherid INNER JOIN ClassSchedules CS ON CS.idClass=CL.idClass WHERE T.teacherid=:Teacher)))"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":Teacher", id); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
//SelectDis public DataTable SelectDis() { string query = @" SELECT P.PersonId, P.names Names, P.lastName LastName, COALESCE(P.secondLastName,'') SecondLastName, P.addres Address, P.phone Phone, P.birthDate BirthDate, P.gender Gender, P.status Status, P.registrationDate Registration, NVL(TO_CHAR(P.updateDate),'1900-01-01') UpdateDate, NVL(TO_CHAR(P.startDate),'1900-01-01') StartDate, NVL(TO_CHAR(P.finishDate),'1900-01-01') FinishDate, P.email Mail, P.latitude Latitude, P.longitude Longitude, (SELECT C.CityName||'-'||PR.provinceName||','||T.townName FROM Town T INNER JOIN Province PR ON PR.ProvinceId=T.ProvinceId INNER JOIN City C ON C.CityId=PR.CityId WHERE P.TownId=T.TownId) Location FROM Person P INNER JOIN Teacher T ON T.PersonId = P.Personid INNER JOIN USERACCOUNT U ON U.Personid=P.Personid WHERE P.status=0 AND U.role='P'"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable SelectTeacherAdd(string like) { string query = @"SELECT C.idClass,M.matterName SubjectName,CONCAT(CO.numberCourse,CO.letterCourse) Course, (select listagg(DAYSS, ', ') within group (order by DAYSS) FROM (SELECT DISTINCT S.DAY DAYSS FROM ClassSchedules CSS INNER JOIN Schedules S ON S.schedulesid= CSS.schedulesid WHERE CSS.idClass = C.idClass)) Days FROM Class C INNER JOIN Matter M ON C.idMatter=M.idMatter INNER JOIN Course CO ON CO.idCourse=C.idCourse WHERE (M.MATTERNAME LIKE :like) OR CO.numberCourse||CO.letterCourse LIKE :like OR M.matterName||CO.numberCourse||CO.letterCourse LIKE :like OR CO.numberCourse||CO.letterCourse||' '||M.matterName LIKE :like ) AND C.status=1"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter("@like", "%" + like + "%"); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable SelectSpecificStudentSchedule(int iduser, string day) { string query = @"SELECT COALESCE((SELECT M.matterName FROM ClassSchedules CS INNER JOIN Class C ON C.idClass=CS.idClass INNER JOIN Matter M ON M.idMatter=C.idMatter INNER JOIN Course CO ON CO.idCourse=C.idCourse INNER JOIN Student ST ON ST.idCourse=CO.idCourse INNER JOIN Person P ON P.Personid=ST.PersonId INNER JOIN USERACCOUNT U ON U.Personid=P.Personid WHERE U.UserId= :useraccount AND CS.schedulesid=S.schedulesid AND C.status=2),'') AS " + day + @" FROM Schedules S WHERE S.DAY= :dayss"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[2]; parameters1[0] = new OracleParameter(":useraccount", iduser); parameters1[1] = new OracleParameter(":dayss", day); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw; } }
public DataTable Selectjue(int idcourse) { string query = @"SELECT schedulesid, CASE WHEN schedulesid NOT IN (SELECT CS.schedulesid FROM ClassSchedules CS INNER JOIN Class C ON C.idClass=CS.idClass INNER JOIN Course CO ON CO.idCourse=C.idCourse WHERE CO.idCourse= :course AND C.status>0) THEN 'O' ELSE 'X' END AS TH,hourStart,hourFinish FROM Schedules WHERE day='Thursday'"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":course", idcourse); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable Select() { string query = "SELECT CITYID AS ID, CITYNAME AS NAME FROM CITY WHERE STATE=1"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable SelectList() { string query = "SELECT DISTINCT CityId id, CityName name FROM City "; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
//Select public DataTable Select() { string query = "SELECT * FROM timetable WHERE status=1"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
//SELECT public DataTable Select() { string query = @"SELECT idCourse,numberCourse||letterCourse Course,section Section, status Status, registrationDate Registration,NVL(updateDate, '01-01-1993') UpdateDate FROM Course"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable Select(string id) { string query = "SELECT PROVINCEID, PROVINCENAME FROM Province WHERE STATE=1 and CITYID=:CityId"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":CityId", int.Parse(id)); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable SelectList(int id) { string query = "SELECT DISTINCT [TownId] as 'id', TownName as 'name',ProvinceId as 'province' FROM Town WHERE ProvinceId=(SELECT ProvinceId FROM Town WHERE TownId= :Town)"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":Town", id); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable SelectList(int id) { string query = "SELECT DISTINCT ProvinceId id, provinceName name,CityId city FROM Province WHERE CityId=(SELECT CityId FROM Province WHERE ProvinceId= :province)"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":province", id); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable Selectlike(string like) { string query = "SELECT idCourse,numberCourse||letterCourse Course,section Section,status Status,registrationDate Registration,updateDate UpdateDate FROM Course WHERE numberCourse||letterCourse LIKE :likes OR section LIKE :likes OR numberCourse||letterCourse||section LIKE :likes OR numberCourse||letterCourse||' '||section LIKE :likes"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":likes", "%" + like + "%"); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
//SELECT STUDENTLIST public DataTable SelectStudents(int id) { string query = @"SELECT STUDENTID FROM STUDENT WHERE IDCOURSE= :Course"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":Course", id); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable SelectTotal(int idclass) { string query = @"select * from AverageGradeTotal WHERE idAverage= :tot"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":tot", idclass); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw; } }
//SELECT public DataTable Selectasd(int idcourse) { string query = @"SELECT idMatter,matterName FROM Matter WHERE (idMatter NOT IN(SELECT idMatter FROM Class WHERE idCourse= 21 AND status>0))"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":course", idcourse); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable Select(string id) { string query = "SELECT TOWNID ID, TOWNNAME NAME FROM Town WHERE PROVINCEID=:ProvinceName"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":ProvinceName", int.Parse(id)); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable SelectSubjects(int id) { string query = @"SELECT C.idClass FROM Class C WHERE C.idCourse= :Course AND C.status>0 "; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":Course", id); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable Select(int id) { string query = @" SELECT CL.idClass,M.matterName SubjectName,C.numberCourse||C.letterCourse Course,(SELECT names||' '||lastName FROM Person P INNER JOIN Teacher T ON T.PersonId=P.Personid WHERE T.teacherid=CL.teacherid) Teacher FROM Class CL INNER JOIN Matter M ON M.idMatter=CL.idMatter INNER JOIN Course C ON C.idCourse=CL.idCourse WHERE CL.idCourse= :Course AND CL.status>0"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":Course", id); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable Selectlike(int idcourse, string like) { string query = @"SELECT schedulesid,hourStart,hourFinish,day FROM Schedules WHERE (schedulesid NOT IN (SELECT CS.schedulesid FROM ClassSchedules CS INNER JOIN Class C ON C.idClass = CS.idClass INNER JOIN Course CO ON CO.idCourse = C.idCourse WHERE CO.idCourse = :course)) AND day LIKE :like"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[2]; parameters1[0] = new OracleParameter(":course", idcourse); parameters1[0] = new OracleParameter(":like", "%" + like + "%"); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable Login(string userName, string password) { DataTable res = new DataTable(); string query = "SELECT U.USERID, U.USERNAME, U.PASSWORD, U.ROLE, P.NAMES||' '||P.LASTNAME, U.REVISIONPASS, P.EMAIL,P.PHOTO,U.STATUS FROM USERACCOUNT U INNER JOIN PERSON P ON P.PERSONID=U.PERSONID WHERE U.USERNAME=:userName AND U.PASSWORD=standard_hash(:password, 'MD5')"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[2]; parameters1[0] = new OracleParameter(":userName", userName); parameters1[1] = new OracleParameter(":password", password); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable Select(int id) { string query = @"SELECT PA.IDPAYMENT,PA.DETAIL ""DETAIL PAYMENT"",PA.AMOUNT,PA.PAIDOUT,PA.AMOUNT-PA.PAIDOUT BALANCE FROM STUDENT S INNER JOIN PERSON P ON P.PERSONID = S.PERSONID INNER JOIN USERACCOUNT U ON U.PERSONID = P.PERSONID INNER JOIN PAYMENT PA ON PA.STUDENTID = S.STUDENTID WHERE U.STATUS > 0 AND S.STUDENTID =:STUDENTID AND PA.STATUS = 1"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":STUDENTID", id); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable Selectlike(string like) { string query = @"SELECT S.STUDENTID, P.NAMES||' '||P.LASTNAME||' '||P.SECONDLASTNAME NAMES, (SELECT COUNT(IDPAYMENT) FROM PAYMENT PA WHERE PA.STUDENTID=S.STUDENTID AND PA.STATUS>0) ""PENDING PAYMENTS"" FROM STUDENT S INNER JOIN PERSON P ON P.PERSONID=S.PERSONID INNER JOIN USERACCOUNT U ON U.PERSONID=P.PERSONID WHERE U.STATUS>0 AND (P.NAMES LIKE :likes OR P.LASTNAME LIKE :likes OR P.LASTNAME LIKE :likes OR P.NAMES||' '||P.LASTNAME LIKE :likes) "; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":likes", "%" + like + "%"); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
public DataTable SelectSearch(string like) { string query = @"SELECT P.PersonId, P.names Names, P.lastName LastName, COALESCE(P.secondLastName,'') SecondLastName, P.addres Address, P.phone Phone, P.birthDate BirthDate, P.gender Gender, P.status Status, P.registrationDate Registration, NVL(TO_CHAR(P.updateDate),'1900-01-01') UpdateDate, P.startDate StartDate, NVL(TO_CHAR(P.finishDate),'1900-01-01') FinishDate, P.email Mail, P.latitude Latitude, P.longitude Longitude, (SELECT C.CityName||'-'||PR.provinceName||','||T.townName FROM Town T INNER JOIN Province PR ON PR.ProvinceId=T.ProvinceId INNER JOIN City C ON C.CityId=PR.CityId WHERE P.TownId=T.TownId) Location FROM Person P INNER JOIN Teacher T ON T.PersonId = P.Personid INNER JOIN USERACCOUNT U ON U.Personid=P.Personid WHERE P.status=1 AND U.role='P' AND ((P.names LIKE :names) OR (P.lastName LIKE :last) OR (P.secondLastName LIKE :second))"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[3]; parameters1[0] = new OracleParameter(":names", "%" + like + "%"); parameters1[1] = new OracleParameter(":last", "%" + like + "%"); parameters1[2] = new OracleParameter(":second", "%" + like + "%"); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw ex; } }
/* * public DataTable Select(string id) * { * * string query = "SELECT CONCAT() FROM"; * try * { * OracleCommand cmd = DBImplementation.CreateBasicCommand(query); * OracleParameter[] parameters1 = new OracleParameter[1]; * * parameters1[0] = new OracleParameter(":CityId", id); * cmd.Parameters.AddRange(parameters1); * return DBImplementation.ExecuteDataTableCommand(cmd); * } * catch (Exception ex) { throw ex; } * }*/ //GET #region teacher public DataTable SelectSpecificTeacherSubject(int iduser) { string query = @"SELECT C.idClass, M.matterName Subject, CO.numberCourse||CO.letterCourse Course FROM Class C INNER JOIN Matter M ON C.idMatter=M.idMatter INNER JOIN Course CO ON CO.idCourse=C.idCourse INNER JOIN Teacher T ON T.teacherid=C.teacherid INNER JOIN Person P ON P.Personid=T.PersonId INNER JOIN USERACCOUNT U ON U.Personid=P.Personid WHERE U.UserId= :userid AND C.status=2"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":userid", iduser); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw; } }
public DataTable SelectStudentsThird(int idclass) { string query = @"SELECT G.gradeId, P.names||' '||P.lastName StudentName, G.grade1 Grade1,G.grade2 Grade2,G.grade3 Grade3,G.grade4 Grade4 ,G.testGrade TestGrade,G.average Average,G.idAverage FROM Class C INNER JOIN Grade G ON C.idClass=G.idClass INNER JOIN ThirdTrimester F ON F.gradeId=G.gradeId INNER JOIN Student S ON S.studentId=G.studentid INNER JOIN Person P ON P.Personid=S.PersonId INNER JOIN USERACCOUNT U ON U.Personid=P.Personid WHERE C.idClass= :class AND U.status=1"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":class", idclass); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw; } }
public DataTable SelectStudentsGrades(int user) { string query = @"SELECT M.matterName ""Subject Name"", (SELECT P.names||' '||P.lastName FROM Teacher T INNER JOIN Person P ON P.Personid=T.PersonId WHERE T.TEACHERID=C.TEACHERID) ""Teacher"", (SELECT G.average FROM Grade G INNER JOIN FirstTrimester F ON F.gradeId=G.gradeId WHERE G.idClass= C.idClass AND G.studentid= S.studentId) ""First Trimester"", (SELECT G.average FROM Grade G INNER JOIN SecondTrimester F ON F.gradeId=G.gradeId WHERE G.idClass= C.idClass AND G.studentid= S.studentId)""Second Trimester"", (SELECT G.average FROM Grade G INNER JOIN ThirdTrimester F ON F.gradeId=G.gradeId WHERE G.idClass= C.idClass AND G.studentid= S.studentId) ""Third Trimester"", (SELECT DISTINCT AV.TotalAverage FROM Grade G INNER JOIN AverageGradeTotal AV ON AV.idAverage= G.idAverage WHERE G.idClass= C.idClass AND G.studentid= S.studentId) ""Total Average"" FROM Class C INNER JOIN Matter M ON M.idMatter=C.idMatter INNER JOIN Course CO ON CO.idCourse=C.idCourse INNER JOIN Student S ON S.idCourse=CO.idCourse INNER JOIN Person P ON P.Personid=S.PersonId INNER JOIN USERACCOUNT U ON U.Personid=P.Personid WHERE C.status>0 AND U.UserId=:userAccount"; try { OracleCommand cmd = DBImplementation.CreateBasicCommand(query); OracleParameter[] parameters1 = new OracleParameter[1]; parameters1[0] = new OracleParameter(":userAccount", user); cmd.Parameters.AddRange(parameters1); return(DBImplementation.ExecuteDataTableCommand(cmd)); } catch (Exception ex) { throw; } }