public async Task <LeaveBalanceSummary> GetLeaveLimitSummary(long pageSize, long requestPage, SearchCondition filters, string getAllOrNot)
{
long _pageSize = pageSize;
long _requestPage = requestPage;
string _getAllOrNot = getAllOrNot;
string _condition = Utility.CompleteConditionString(filters);
string condition = String.IsNullOrEmpty(_condition) ? "1 = 1" : "(" + _condition + ")";
LeaveBalanceSummary _obj = new LeaveBalanceSummary();
_obj.summary = new List <LeaveBalance>();
_obj.leaveCodes = new List <string>();
DataTable dt_leaveType = new DataTable();
DataTable dt_user = new DataTable();
DataTable dt_leaveLimit = new DataTable();
using (SqlConnection con = SqlCon())
{
SqlCommand cmd = SqlCmd(con);
cmd.CommandText = "LeaveLimitSummary";
cmd.Parameters.AddWithValue("@pageSize", _pageSize);
cmd.Parameters.AddWithValue("@requestPage", _requestPage);
cmd.Parameters.AddWithValue("@condition", condition);
cmd.Parameters.AddWithValue("@getAll", _getAllOrNot);
SqlParameter prm1 = new SqlParameter
{
ParameterName = "@collectionSize",
SqlDbType = SqlDbType.BigInt,
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(prm1);
SqlParameter prm2 = new SqlParameter
{
ParameterName = "@activePage",
SqlDbType = SqlDbType.BigInt,
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(prm2);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
con.Open();
da.Fill(ds);
_obj.collectionSize = (Int64)prm1.Value;
_obj.pageSize = _pageSize;
_obj.activePage = (Int64)prm2.Value;
if (ds.Tables.Count == 3)
{
dt_leaveType = ds.Tables[0];
dt_user = ds.Tables[1];
dt_leaveLimit = ds.Tables[2];
_obj.status = "000";
}
else
{
_obj.status = "-001";
}
}
catch (SqlException ex)
{
_obj.status = ex.Number.ToString();
_obj.message = ex.Message;
}
finally
{
dt_leaveType.Dispose();
dt_user.Dispose();
dt_leaveLimit.Dispose();
ds.Dispose();
da.Dispose();
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
cmd.Dispose();
}
}
if (_obj.status != "000")
{
return(_obj);
}
foreach (DataRow dr1 in dt_user.Rows)
{
LeaveBalance _obj1 = new LeaveBalance();
User user = new User();
user.userName = (string)dr1[nameof(user.userName)];
user.userFullName = (string)dr1[nameof(user.userFullName)];
user.userDeptCode = (string)dr1[nameof(user.userDeptCode)];
user.userTitleCode = (string)dr1[nameof(user.userTitleCode)];
_obj1.user = user;
_obj1.leaveTypes = new List <LeaveType>();
foreach (DataRow dr2 in dt_leaveType.Rows)
{
LeaveType leave = new LeaveType();
string leaveCode = (string)dr2[nameof(leave.leaveCode)];
var rows = dt_leaveLimit.AsEnumerable();
var row = rows.FirstOrDefault(x => x["leaveCode"].ToString() == leaveCode && x["userName"].ToString() == user.userName);
leave.leaveCode = leaveCode;
if (row != null && !row.IsNull("limit"))
{
leave.limit = Convert.ToInt32(row["limit"]);
}
_obj1.leaveTypes.Add(leave);
}
_obj.summary.Add(_obj1);
}
foreach (DataRow dr2 in dt_leaveType.Rows)
{
_obj.leaveCodes.Add((string)dr2["leaveCode"]);
}
return(_obj);
}
public async Task <LeaveBalanceSummary> AdjustLeaveLimit(LeaveBalance model)
{
LeaveBalanceSummary _obj = new LeaveBalanceSummary();
DataTable dt_leaveType = new DataTable();
using (SqlConnection con = SqlCon())
{
DataTable dt = leaveLimit();
foreach (var x in model.leaveTypes)
{
DataRow dr = dt.NewRow();
dr["userName"] = model.user.userName;
dr["leaveCode"] = x.leaveCode;
dr["limit"] = x.limit ?? (object)DBNull.Value;
dt.Rows.Add(dr);
}
SqlCommand cmd = SqlCmd(con);
cmd.CommandText = "AdjustLeaveLimit";
SqlParameter prm0 = new SqlParameter
{
ParameterName = "@leaveTypes",
SqlDbType = SqlDbType.Structured,
Direction = ParameterDirection.Input,
Value = dt
};
cmd.Parameters.Add(prm0);
cmd.Parameters.AddWithValue("@userName", model.user.userName);
SqlParameter prm1 = new SqlParameter
{
ParameterName = "@status",
SqlDbType = SqlDbType.NVarChar,
Size = 50,
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(prm1);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
con.Open();
da.Fill(ds);
_obj.status = (string)prm1.Value;
}
catch (SqlException ex)
{
_obj.status = ex.Number.ToString();
_obj.message = ex.Message;
}
finally
{
dt.Dispose();
ds.Dispose();
da.Dispose();
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
cmd.Dispose();
}
}
return(_obj);
}
