public DepartmentRequisitionReport GetReportByDepartmentOnDate(string monthList)
{
string[] monthArray = monthList.Split('-');
string month = monthArray[1];
string year = monthArray[0];
DepartmentRequisitionReport departmentRequisitionReport = new DepartmentRequisitionReport();
List <DepartmentRequisitionReport> departmentRequisitionReportList = new List <DepartmentRequisitionReport>();
OpenConnection();
cmd.CommandText = "select * from( "
+ " select re.StaffRequisitionId as TotalRequisition,d.DepartmentName from "
+ " StaffRequisition re join Department d "
+ " on "
+ " re.DepartmentId=d.DepartmentId "
+ " where Month(re.ApproveDate)='" + month + "' and YEAR(re.ApproveDate)= '" + year
+ " ' group by re.DepartmentId,re.StaffRequisitionId,d.DepartmentName) As SourceTable "
+ " PIVOT "
+ " (Count(TotalRequisition) FOR DepartmentName IN ([English Dept],[Commerce Dept],[Computer Science],[Registrar Dept],[Zoology Dept])) AS p ";;
rd = cmd.ExecuteReader();
if (rd.HasRows)
{
while (rd.Read())
{
departmentRequisitionReport.RequisitionDate = new DateTime(Convert.ToInt32(year), Convert.ToInt32(month), 1);
if (rd[0] != DBNull.Value)
{
departmentRequisitionReport.EnglishDept = Convert.ToInt32(rd[0]);
}
if (rd[1] != DBNull.Value)
{
departmentRequisitionReport.CommerceDept = Convert.ToInt32(rd[1]);
}
if (rd[2] != DBNull.Value)
{
departmentRequisitionReport.ComputerScience = Convert.ToInt32(rd[2]);
}
if (rd[3] != DBNull.Value)
{
departmentRequisitionReport.RegistrarDept = Convert.ToInt32(rd[3]);
}
if (rd[4] != DBNull.Value)
{
departmentRequisitionReport.ZoologyDept = Convert.ToInt32(rd[4]);
}
}
}
CloseConnection();
return(departmentRequisitionReport);
}
public List <DepartmentRequisitionReport> GetReportByDepartment(List <string> months)
{
List <DepartmentRequisitionReport> departmentRequisitionReportList = new List <DepartmentRequisitionReport>();
foreach (string month in months)
{
DepartmentRequisitionReport departmentRequisitionReport = logicU_Data.GetReportByDepartmentOnDate(month);
if (departmentRequisitionReport.CommerceDept == 0 && departmentRequisitionReport.ComputerScience == 0 &&
departmentRequisitionReport.EnglishDept == 0 && departmentRequisitionReport.RegistrarDept == 0 &&
departmentRequisitionReport.ZoologyDept == 0)
{
}
else
{
departmentRequisitionReportList.Add(departmentRequisitionReport);
}
}
return(departmentRequisitionReportList);
}
