public object getConsolidateByType(DateTime startDate, DateTime endDate, string type)
{
List <OvCustomModel> result = new List <OvCustomModel>();
var conn = db.Database.GetDbConnection();
try
{
conn.Open();
using (var command = conn.CreateCommand())
{
string daterange = "";
if (!startDate.ToString().Equals("1/1/0001 12:00:00 AM") && !endDate.ToString().Equals("1/1/0001 12:00:00 AM"))
{
daterange = " and rq_start_time between '" + startDate + @"' and '" + endDate + @"' ";
}
string query = "";
if (type.Equals("Department"))
{
query = @"select rq_dep_id,d_description,
sum(CASE
WHEN rq_status=[dbo].get_approved_priority(rq_workflow_id) THEN
([dbo].[fn_GetTotalWorkingHours](rq_start_time, rq_end_time)-[dbo].[get_deduction_from_overtime](rq_id,rq_doc_id))
ELSE 0
END) approved,
sum(CASE
WHEN rq_status>=[dbo].get_approved_priority(rq_workflow_id) THEN 0
ELSE ([dbo].[fn_GetTotalWorkingHours](rq_start_time, rq_end_time)-[dbo].[get_deduction_from_overtime](rq_id,rq_doc_id))
END) unaproved,
sum(
[dbo].[fn_GetTotalWorkingHours](rq_start_time, rq_end_time)-[dbo].[get_deduction_from_overtime](rq_id,rq_doc_id)) hours
from OverTimeRequest
join Users on rq_cre_for = u_id
join Departments on rq_dep_id = d_id
where 1=1 " + daterange + @" and rq_end_time is not null and rq_status!=0
group by rq_dep_id,d_description" ;
}
else
{
query = @"select rq_cre_for,u_name,u_full_name,
sum(CASE
WHEN rq_status>=[dbo].get_approved_priority(rq_workflow_id) THEN
([dbo].[fn_GetTotalWorkingHours](rq_start_time, rq_end_time)-[dbo].[get_deduction_from_overtime](rq_id,rq_doc_id))
ELSE 0
END) approved,
sum(CASE
WHEN rq_status>=[dbo].get_approved_priority(rq_workflow_id) THEN 0
ELSE ([dbo].[fn_GetTotalWorkingHours](rq_start_time, rq_end_time)-[dbo].[get_deduction_from_overtime](rq_id,rq_doc_id))
END) unaproved,
sum([dbo].[fn_GetTotalWorkingHours](rq_start_time, rq_end_time)-[dbo].[get_deduction_from_overtime](rq_id,rq_doc_id)) hours
from OverTimeRequest
join Users on rq_cre_for = u_id
join Departments on rq_dep_id = d_id
where 1=1 " + daterange + @" and rq_end_time is not null and rq_status!=0
group by rq_cre_for,u_name,u_full_name" ;
}
command.CommandText = query;
DbDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
OvCustomModel ovCustomModel = new OvCustomModel();
if (type.Equals("Department"))
{
ovCustomModel.emp_dep_id = int.Parse(reader["rq_dep_id"].ToString());
ovCustomModel.emp_dep_name = reader["d_description"].ToString();
}
else
{
ovCustomModel.emp_id = int.Parse(reader["rq_cre_for"].ToString());
ovCustomModel.emp_name = reader["u_name"].ToString();
ovCustomModel.emp_full_name = reader["u_full_name"].ToString();
}
ovCustomModel.work_hours = decimal.Parse(reader["approved"].ToString());
ovCustomModel.emp_unapproved = decimal.Parse(reader["unaproved"].ToString());
ovCustomModel.emp_total = decimal.Parse(reader["hours"].ToString());
result.Add(ovCustomModel);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(result);
}
public IEnumerable <OvCustomModel> getConsolidatedAsync(int rq_dep_id, DateTime startDate, DateTime endDate, int rq_cre_for)
{
List <OvCustomModel> result = new List <OvCustomModel>();
var conn = db.Database.GetDbConnection();
try
{
conn.Open();
using (var command = conn.CreateCommand())
{
string query = @"select rq_cre_for,u_name,u_full_name,rq_dep_id,d_description,
sum(CASE
WHEN rq_status>=[dbo].get_approved_priority(rq_workflow_id) THEN
([dbo].[fn_GetTotalWorkingHours](rq_start_time, rq_end_time)-[dbo].[get_deduction_from_overtime](rq_id,rq_doc_id))
ELSE 0
END) approved,
sum(CASE
WHEN rq_status>=[dbo].get_approved_priority(rq_workflow_id) THEN 0
ELSE (([dbo].[fn_GetTotalWorkingHours](rq_start_time, rq_end_time)-[dbo].[get_deduction_from_overtime](rq_id,rq_doc_id)))
END) unaproved,
sum(
[dbo].[fn_GetTotalWorkingHours](rq_start_time, rq_end_time)-[dbo].[get_deduction_from_overtime](rq_id,rq_doc_id)) hours
from OverTimeRequest
join Users on rq_cre_for = u_id
join Departments on rq_dep_id = d_id
where 1=1 and rq_end_time is not null and rq_status!=0 ";
if (!startDate.ToString().Equals("1/1/0001 12:00:00 AM") && !endDate.ToString().Equals("1/1/0001 12:00:00 AM"))
{
query += " and rq_start_time between '" + startDate + @"' and '" + endDate + @"' ";
}
if (rq_dep_id != 0)
{
query += " and rq_dep_id='" + rq_dep_id + @"'";
}
if (rq_cre_for != 0)
{
query += " and rq_cre_for='" + rq_cre_for + @"'";
}
query += " group by rq_cre_for,u_name,u_full_name,rq_dep_id,d_description";
System.Diagnostics.Debug.WriteLine(query);
command.CommandText = query;
DbDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
OvCustomModel ovCustomModel = new OvCustomModel();
ovCustomModel.emp_id = reader.GetInt32(0);
ovCustomModel.emp_name = reader.GetString(1);
ovCustomModel.emp_full_name = reader.GetString(2);
ovCustomModel.emp_dep_id = reader.GetInt32(3);
ovCustomModel.emp_dep_name = reader.GetString(4);
ovCustomModel.work_hours = reader.GetDecimal(5);
ovCustomModel.emp_unapproved = reader.GetDecimal(6);
ovCustomModel.emp_total = reader.GetDecimal(7);
result.Add(ovCustomModel);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(result);
}
