public async Task <JsonResult> GetBalance(string GeneralNumber)
{
VacationBalance vb = new VacationBalance();
SqlConnection con = new SqlConnection(cs);
await Task.Delay(7000);
using (con)
{
try
{
con.Open();
//read annual balance
SqlCommand com = new SqlCommand("Workflow_GetAnnualBalanceNewF", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@GENERALNO", GeneralNumber);
com.Parameters.AddWithValue("@dat", "03/14/2018");
SqlDataReader rdr = com.ExecuteReader();
rdr.Read();
vb.Annual = Convert.ToInt32(rdr["TotalBalance"]);
rdr.Close();
//read other balance
SqlCommand com2 = new SqlCommand("Workflow_GetUserOvertimeHolidaysUrgentMng", con);
com2.CommandType = CommandType.StoredProcedure;
com2.Parameters.AddWithValue("@GENERALNO", "63012460");
com2.Parameters.AddWithValue("@YearUrgent", "1439");
SqlDataReader rdr2 = com2.ExecuteReader();
rdr2.Read();
vb.Overtime = Convert.ToInt32(rdr2["Overtime"]);
vb.Holyday = Convert.ToInt32(rdr2["Holyday"]);
vb.Urgent = Convert.ToInt32(rdr2["Urgent"]);
vb.Mng = Convert.ToInt32(rdr2["Mng"]);
vb.Priv = Convert.ToInt32(rdr2["Priv"]);
rdr2.Close();
}
catch (Exception err)
{
con.Close();
return(Json(err.Message, JsonRequestBehavior.AllowGet));
}
finally
{
con.Close();
}
}
con.Close();
return(Json(vb, JsonRequestBehavior.AllowGet));
}
public IQueryable <VacationBalance> GetVacationBalanceAsQueryable()
{
var result = new List <VacationBalance>();
string vacationBalanceView = _customSettings.DbViewVacationBalance;
if (vacationBalanceView == null)
{
//_logger.Log($"{this.GetType().Name}, GetEmployeesAsQueryable(), DATABASE_VIEW_VACATIONBALANCE is null", null);
}
using (var sqlConnection1 = new SqlConnection(_connectionString))
{
var cmd = new SqlCommand
{
CommandText = "SELECT * FROM " + vacationBalanceView,
CommandType = CommandType.Text,
Connection = sqlConnection1
};
sqlConnection1.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var currentRow = new VacationBalance
{
MunicipalityInfo = SafeGetString(reader, 0),
SocialSecurityNumber = SafeGetString(reader, 1),
EmploymentRelationshipNumber = SafeGetString(reader, 2),
SalaryKind = SafeGetString(reader, 3),
VacationEarnedYear = SafeGetString(reader, 4),
BalanceDate = SafeGetString(reader, 5),
VacationHoursWithPay = SafeGetInt32(reader, 6),
PossibleVacationDaysWithPay = SafeGetInt32(reader, 7),
VacationHoursWithoutPay = SafeGetInt32(reader, 8),
PossibleVacationDaysWithoutPay = SafeGetInt32(reader, 9),
TransferredVacationHours = SafeGetInt32(reader, 10),
PossibleTransferredVacationDays = SafeGetInt32(reader, 11),
FreeVacationHoursTotal = SafeGetInt32(reader, 12),
VacationHoursWithPayDec = SafeGetDouble(reader, 13),
VacationHoursWithoutPayDec = SafeGetDouble(reader, 14),
TransferredVacationHoursDec = SafeGetDouble(reader, 15),
FreeVacationHoursTotalDec = SafeGetDouble(reader, 16),
UpdateDate = SafeGetDate(reader, 17)
};
result.Add(currentRow);
}
}
return(result.AsQueryable());
}
