public VMreport()
{
DayDate = new VMBetweenDatesFilter(DateTime.Now.AddDays(DayOfWeek.Monday - DateTime.Now.DayOfWeek), DateTime.Now.AddDays(DayOfWeek.Monday - DateTime.Now.DayOfWeek + 27));
CourseCode = new VMTextfieldFilter("Course code");
data = new Dictionary<string, DataSet>();
isPush = "false";
role = "";
}
public DBAccess()
{
CourseCode = new VMTextfieldFilter("Course code");
/*data = new Dictionary<string, DataSet>();
public Dictionary<string, DataSet> data { get; set; }
public VMBetweenDatesFilter DayDate { get; set; }
DayDate = new VMBetweenDatesFilter(DateTime.Now.AddDays(DayOfWeek.Monday - DateTime.Now.DayOfWeek), DateTime.Now.AddDays(DayOfWeek.Monday - DateTime.Now.DayOfWeek + 27));*/
}
public static DataSet getCourses(String _startDate, String _endDate, VMTextfieldFilter CourseCode, String role = "")
{
DbConnection con = new DbConnection();
User currentUser = (User)HttpContext.Current.Session["User"];
string request = "select distinct course.component_id, name, case when course.startdate is null and isdate(right(course.userdefined_id, 10)) = 1 then convert(date, convert(date, right((course.userdefined_id), 10)), 103) when course.startdate is null and isnumeric(right(course.userdefined_id, 4)) = 1 then convert(date, right(course.userdefined_id, 4)+'-01-01') else course.startdate end as startdate, case when course.enddate is null and isdate(right(course.userdefined_id, 10)) = 1 then (select max(convert(date, deadline_date)) from portfolio where component_id = course.component_id) when course.enddate is null and isnumeric(right(course.userdefined_id, 4)) = 1 then convert(date, right(course.userdefined_id, 4)+'-12-31') else course.enddate end as enddate, course.userdefined_id as coursecode, ect.time_mode_id, (select count(*) from portfolio pfr where pfr.component_id = course.component_id and pfr.[status]=8 and (GETDATE() < pfr.deadline_date or pfr.deadline_date is null) ) as [Registered], (select count(*) from portfolio pfs where pfs.component_id = course.component_id and pfs.[status]=9 and (GETDATE() < pfs.deadline_date or pfs.deadline_date is null)) as [Started], (select count(*)from portfolio pfp where pfp.component_id = course.component_id and (pfp.[status]=10 or pfp.[status]=11)) as [Passed], (select count(*) from portfolio pfo where pfo.component_id = course.component_id and pfo.[status] in (8,9) and pfo.deadline_date is not null and (getdate()>course.enddate or getdate()>pfo.deadline_date)) as [Overdue], (select count(*) from portfolio where component_id = course.component_id and [status]=12) as [Failed] "
+ "from e_component course left join portfolio pf on course.component_id=pf.component_id inner join e_componenttype ect on course.type_id = ect.componenttype_id left join e_description ed on ed.component_id=course.component_id and metatag_id = 10068"
+ "where course.is_template = 0 and course_status_id <> 3 and course.organizer_id = 195673 and course.archive = 0 ";
if (role == "*******CRO" || role == "ADMIN" || (role == "" && (currentUser.is*******CRO() || currentUser.isSysAdmin())))
{
}
