public static List <Leaves_Count> Get_Data(string eid)
{
List <Leaves_Count> list_det = new List <Leaves_Count>();
DataTable dt = get_all_products(Convert.ToInt32(eid));
for (Int32 i = 0; i < dt.Rows.Count; i++)
{
Leaves_Count p = new Leaves_Count();
p.sl = Convert.ToInt32(dt.Rows[i]["sl"]);
p.cl = Convert.ToInt32(dt.Rows[i]["cl"]);
p.al = Convert.ToInt32(dt.Rows[i]["al"]);
list_det.Add(p);
}
return(list_det);
}
public void GetLeaves_Count(int iDisplayLength, int iDisplayStart, int iSortCol_0, string sSortDir_0, string sSearch)
{
int displayLength = iDisplayLength;
int displayStart = iDisplayStart;
int sortCol = iSortCol_0;
string sortDir = sSortDir_0;
string search = sSearch;
string cs = ConfigurationManager.ConnectionStrings["DBMS"].ConnectionString;
List <Leaves_Count> listEmployees = new List <Leaves_Count>();
int filteredCount = 0;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("Sp_GetUpdate_Leave", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramDisplayLength = new SqlParameter()
{
ParameterName = "@DisplayLength",
Value = displayLength
};
cmd.Parameters.Add(paramDisplayLength);
SqlParameter paramDisplayStart = new SqlParameter()
{
ParameterName = "@DisplayStart",
Value = displayStart
};
cmd.Parameters.Add(paramDisplayStart);
SqlParameter paramSortCol = new SqlParameter()
{
ParameterName = "@SortCol",
Value = sortCol
};
cmd.Parameters.Add(paramSortCol);
SqlParameter paramSortDir = new SqlParameter()
{
ParameterName = "@SortDir",
Value = sortDir
};
cmd.Parameters.Add(paramSortDir);
SqlParameter paramSearchString = new SqlParameter()
{
ParameterName = "@Search",
Value = string.IsNullOrEmpty(search) ? null : search
};
cmd.Parameters.Add(paramSearchString);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
int sl, cl, al;
SqlConnection con1 = new SqlConnection(cs);
while (rdr.Read())
{
Leaves_Count emp = new Leaves_Count();
emp.emp_id = Convert.ToInt32(rdr["emp_id"]);
filteredCount = Convert.ToInt32(rdr["TotalCount"]);
emp.emp_name = rdr["emp_name"].ToString();
emp.Desig = Convert.ToString(rdr["Designation"]);
emp.sl = Convert.ToInt32(rdr["sl"]);
emp.cl = Convert.ToInt32(rdr["cl"]);
emp.al = Convert.ToInt32(rdr["al"]);
emp.Img = Convert.ToString(rdr["Img"]);
SqlCommand cmd1 = new SqlCommand("select ISNULL(Sum(l.days),0) from Leaves as l where emp_id='" + Convert.ToInt32(rdr["emp_id"]) + "' and leave_type='sl'", con1);
con1.Open();
sl = Convert.ToInt32(cmd1.ExecuteScalar());
con1.Close();
SqlCommand cmd2 = new SqlCommand("select ISNULL(Sum(l.days),0) from Leaves as l where emp_id='" + Convert.ToInt32(rdr["emp_id"]) + "' and leave_type='cl'", con1);
con1.Open();
cl = Convert.ToInt32(cmd2.ExecuteScalar());
con1.Close();
SqlCommand cmd3 = new SqlCommand("select ISNULL(Sum(l.days),0) from Leaves as l where emp_id='" + Convert.ToInt32(rdr["emp_id"]) + "' and leave_type='al'", con1);
con1.Open();
al = Convert.ToInt32(cmd3.ExecuteScalar());
con1.Close();
emp.lsl = Convert.ToInt32(rdr["sl"]) - sl;
emp.lcl = Convert.ToInt32(rdr["cl"]) - cl;
emp.lal = Convert.ToInt32(rdr["al"]) - al;
listEmployees.Add(emp);
}
}
var result = new
{
iTotalRecords = GetUpdate_LeavesCount(),
iTotalDisplayRecords = filteredCount,
aaData = listEmployees
};
JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(result));
}
