//public static DataTable GetQuestion3(int personelId, int courseId,int chapterId,int typeId) //alter
//{
// string sql = string.Format(@"SELECT Q.Id, Q.Name, C.ChapterName, T.Name TypeName
// FROM Question Q
// Left Join QuestionType T On Q.QuestionTypeId = T.Id
// Left Join Chapters C On Q.ChapterId = C.Id
// Where Q.IsActive = 1 and
// Q.ChapterId in (
// Select Id From Chapters Where CourseId In (
// Select CourseId From PersonelsCourses
// Where PersonelId = {0} ) And CourseId = {1}) And Q.ChapterId = {3} And Q.QuestionTypeId = {4}
// ORDER BY NEWID()", personelId, courseId, chapterId, typeId);
// DataTable dt = NativeSqlDb.ExecuteReader(sql);
// // dt.Columns["Ch"].ReadOnly = true;
// return dt;
//}
public static DataTable GetQuestion2(int personelId, DataTable dt2) //prepare
{
DataTable dt = null;
foreach (DataRow r in dt2.Rows)
{
string sql = string.Format(@"SELECT {2} Q.Id, Q.ChapterId, Q.QuestionTypeId,Convert(bit,1) Ch, C.ChapterName, T.Name TypeName, Q.Name
FROM Question Q
Left Join QuestionType T On Q.QuestionTypeId = T.Id
Left Join Chapters C On Q.ChapterId = C.Id
Where Q.IsActive = 1 and
Q.ChapterId in (
Select Id From Chapters Where CourseId In (
Select CourseId From PersonelsCourses
Where PersonelId = {0} ) And CourseId = {1}) And Q.ChapterId = {3} And Q.QuestionTypeId = {4}
ORDER BY NEWID()", personelId, r["CourseId"], (int.Parse(r["QuestionsCount"].ToString()) > 0) ? "Top " + r["QuestionsCount"] : "", r["ChapterId"], r["Type"]);
DataTable temp = NativeSqlDb.ExecuteReader(sql);
if (dt == null)
{
dt = temp;
}
else
{
foreach (DataRow dr in temp.Rows)
{
dt.Rows.Add(dr.ItemArray);
}
}
}
dt.Columns["Ch"].ReadOnly = false;
return(dt);
}
public static DataTable ShowAnswer()
{
string sql = @"SELECT Id, QuestionId, Name, IsCorrect
FROM Answer
WHERE (QuestionId IN
(SELECT Id FROM Question))";
return(NativeSqlDb.ExecuteReader(sql));
}
public static DataTable PersonelCourseList1(int personelId, int semesterId)
{
string sql = string.Format(@"SELECT CourseId, course
FROM PersonelsCourses P
Left Join COURSES C On P.CourseId = C.courseid
Where PersonelId = {0} and SemesterId = {1}
Order by course", personelId, semesterId);
return(NativeSqlDb.ExecuteReader(sql));
}
public static DataTable PersonelsCoursesList(int pId)
{
string sql = string.Format(@"SELECT P.Id,S.semester,C.course,C.description
FROM PersonelsCourses P
Left Join semster S On P.SemesterId = S.id
Left Join Courses C On P.CourseId = C.courseid
Where PersonelId = {0}", pId);
return(NativeSqlDb.ExecuteReader(sql));
}
public static DataTable GetQuestion(int personelId, int courseId, int rnd) //alter
{
string sql = string.Format(@"SELECT {2} Q.Id, Q.ChapterId, Q.QuestionTypeId,Convert(bit,1) Ch, C.ChapterName, T.Name TypeName, Q.Name
FROM Question Q
Left Join QuestionType T On Q.QuestionTypeId = T.Id
Left Join Chapters C On Q.ChapterId = C.Id
Where Q.IsActive = 1 and
Q.ChapterId in (
Select Id From Chapters Where CourseId In (
Select CourseId From PersonelsCourses
Where PersonelId = {0} ) And CourseId = {1})
ORDER BY NEWID()", personelId, courseId, (rnd > 0) ? "Top " + rnd : "");
DataTable dt = NativeSqlDb.ExecuteReader(sql);
dt.Columns["Ch"].ReadOnly = false;
return(dt);
}
public static DataTable ChapterList(int courseId)
{
string sql = string.Format("SELECT Id, ChapterNo, ChapterName FROM Chapters Where CourseId = {0}", courseId);
return(NativeSqlDb.ExecuteReader(sql));
}
public static DataTable ExamPapers(int personelId)
{
string sql = string.Format("SELECT Id, PaperName, PaperValue FROM ExamPapers Where PersonelId = {0}", personelId);
return(NativeSqlDb.ExecuteReader(sql));
}
public static DataTable GetCoursesWithDeptId(int cId)
{
string sql = string.Format("SELECT courseid, [description] + ' ( ' +course +' )' course FROM COURSES Where DepartmentId = {0}", cId);
return(NativeSqlDb.ExecuteReader(sql));
}
public static DataTable GetDepartments()
{
string sql = "SELECT DepID,Department FROM DEPARTMENTS Order by Department";
return(NativeSqlDb.ExecuteReader(sql));
}
public static DataTable GetPersonelsWithFullName()
{
string sql = "SELECT id ,name +' ' + surname FullName From personnel";
return(NativeSqlDb.ExecuteReader(sql));
}
public static DataTable GetSemesters()
{
string sql = "SELECT id ,semester FROM semster";
return(NativeSqlDb.ExecuteReader(sql));
}