public static ReturnObject GetLeavesForEmployee(string employee_id, string employee_category)
{
leave_assign page_object = new leave_assign();
DBConnection db_connection = new DBConnection();
ReturnObject return_object = new ReturnObject();
DataTable leave_data = new DataTable();
string query = string.Empty;
try
{
query = "select T1.LeaveCode, T1.LeaveName, L.Max_leaves, L.Leaves_applied, l.Leave_balance from ";
query += " ( select * from LeaveMaster where (EmployeeCategoryCode = '" + employee_category + "' or EmployeeCategoryCode is null)) ";
query += " T1 left join Employee_Leave L on L.Leave_code = T1.LeaveCode and Emp_code='" + employee_id + "' where T1.LeaveCode not in ('CO','V','OD') order by LeaveCode ";
leave_data = db_connection.ReturnDataTable(query);
return_object.status = "success";
return_object.return_data = JsonConvert.SerializeObject(leave_data, Formatting.Indented);
}
catch (Exception ex)
{
Logger.LogException(ex, page, "EMPLOYEE_LEAVES_DATA");
return_object.status = "error";
return_object.return_data = "An error occurred while loading Leaves for the selected Employee. Please try again. If the error persists, please contact Support.";
}
return(return_object);
}
private string LeaveAction(string employee_id, string leave_code, double max_leave, double leave_applied, double leave_balance)
{
leave_assign page_object = new leave_assign();
DBConnection db_connection = new DBConnection();
string query = string.Empty, status = string.Empty;
int employee_leave_count = 0;
try
{
query = "select count(*) from Employee_Leave where Emp_Code='" + employee_id + "' and Leave_code='" + leave_code + "'";
employee_leave_count = db_connection.GetRecordCount(query);
if (employee_leave_count > 0)
{
query = "update Employee_Leave set Max_leaves='" + max_leave + "', Leaves_applied='" + leave_applied + "', Leave_balance='" + leave_balance + "' where Emp_code='" + employee_id + "' and Leave_code='" + leave_code + "'";
db_connection.ExecuteQuery_WithOutReturnValue(query);
status = "Leaves Updated Successfully\n";
}
else
{
query = "insert into Employee_Leave (Emp_Code,Leave_Code,Max_leaves,Leaves_Applied ,leave_balance ) values('" + employee_id + "','" + leave_code + "','" + max_leave + "','" + leave_applied + "','" + leave_balance + "')";
db_connection.ExecuteQuery_WithOutReturnValue(query);
status = "Leaves Inserted Successfully\n";
}
}
catch (Exception ex)
{
Logger.LogException(ex, page, "GET_OTHER_DATA");
status = "An error occurred while processing leaves assigned for Leave Code - " + leave_code + "\n";
}
return(status);
}
public static ReturnObject DoAction(string employee_id, string leave_data)
{
leave_assign page_object = new leave_assign();
DBConnection db_connection = new DBConnection();
ReturnObject return_object = new ReturnObject();
JArray processed_data = new JArray();
string leave_code = string.Empty, response_status = string.Empty;
double leave_balance = 0.0, max_leave = 0.0, leave_applied = 0.0;
try
{
processed_data = JArray.Parse(leave_data);
for (int i = 0; i < processed_data.Count; i++)
{
leave_code = Convert.ToString(processed_data[i]["leave_code"]);
max_leave = Convert.ToDouble(processed_data[i]["max_leave"]);
leave_applied = Convert.ToDouble(processed_data[i]["leave_applied"]);
leave_balance = Convert.ToDouble(processed_data[i]["leave_balance"]);
if (leave_applied <= max_leave)
{
response_status += leave_code + " " + page_object.LeaveAction(employee_id, leave_code, max_leave, leave_applied, leave_balance);
}
}
if (response_status == "")
{
response_status = "Leaves assigned successfully!";
}
return_object.status = "success";
return_object.return_data = JsonConvert.SerializeObject(response_status, Formatting.Indented);
}
catch (Exception ex)
{
Logger.LogException(ex, page, "GET_OTHER_DATA");
return_object.status = "error";
return_object.return_data = "An error occurred while save Assigned Leaves data. Please try again. If the error persists, please contact Support.";
}
return(return_object);
}
public static ReturnObject GetEmployeeData(int page_number, bool is_filter, string filters)
{
leave_assign page_object = new leave_assign();
ReturnObject return_object = new ReturnObject();
DBConnection db_connection = new DBConnection();
DataTable employee_data = new DataTable();
string query = string.Empty;
int start_row = 0, number_of_record = 0;
try
{
//set rows count
start_row = (page_number - 1) * 30;
number_of_record = page_number * 30 + 1;
//get base query
query = page_object.GetEmployeeBaseQuery();
//append filter query
if (is_filter)
{
query = page_object.GetFilterQuery(filters, query);
}
//append paging
query += " ) Employees where row > " + start_row + " and row < " + number_of_record;
employee_data = db_connection.ReturnDataTable(query);
return_object.status = "success";
return_object.return_data = JsonConvert.SerializeObject(employee_data, Formatting.Indented);
}
catch (Exception ex)
{
Logger.LogException(ex, page, "GET_EMPLOYEE_DATA");
return_object.status = "error";
return_object.return_data = "An error occurred while loading Employee Data. Please try again. If the error persists, please contact Support.";
}
return(return_object);
}
public static ReturnObject DoImport(string file_name)
{
leave_assign page_object = new leave_assign();
DBConnection db_connection = new DBConnection();
ReturnObject return_object = new ReturnObject();
DataTable leave_data = new DataTable();
string leave_code = string.Empty;
string effectd_rows = string.Empty;
string excelConnectionString = string.Empty;
string upload_path = string.Empty;
string ExcelFullPath = string.Empty;
string employee_id = string.Empty;
string[] rows = new string[2];
string[] leave_codes_Master = null;
string[] employee_codes_master = null;
string Return_message = string.Empty;
double max_leave = 0.0, leave_applied = 0.0;
int row_number = 0;
int total_rows = 0;
bool
leave_code_flag = true,
max_leaves_flag = true;
string row_inserted = string.Empty, row_rejected = string.Empty;
try
{
upload_path = ConfigurationManager.AppSettings["TEMP_FILE_UPLOAD"].ToString();
ExcelFullPath = HttpContext.Current.Server.MapPath("~/" + upload_path + "/" + file_name);
leave_data = page_object.ReturnExcelasDataTable(ExcelFullPath, "");
if (leave_data.Rows.Count > 0)
{
total_rows = leave_data.Rows.Count;
employee_codes_master = GetEmployeeCodes();
foreach (DataRow dReader in leave_data.Rows)
{
row_number++;
if (row_number == 1)
{
continue;
}
employee_id = Convert.ToString(dReader["EMP_CODE"]).Trim();
if (!string.IsNullOrEmpty(employee_id))
{
if (Array.IndexOf(employee_codes_master, employee_id) >= 0)
{
leave_codes_Master = GetLeaveCodes(employee_id);
if (!string.IsNullOrEmpty(dReader["LEAVE_CODE"].ToString()))
{
leave_code = Convert.ToString(dReader["LEAVE_CODE"]);
}
else
{
Return_message += " Leave code cannot be empty on row: " + row_number + Environment.NewLine;
leave_code_flag = false;
}
if (!string.IsNullOrEmpty(dReader["MAX_LEAVES"].ToString()))
{
max_leave = Convert.ToDouble(dReader["MAX_LEAVES"]);
}
else
{
Return_message += " Max Leaves cannot be empty on row: " + row_number + Environment.NewLine;
max_leaves_flag = false;
}
if (!string.IsNullOrEmpty(dReader["LEAVES_APPLIED"].ToString()))
{
leave_applied = Convert.ToDouble(dReader["LEAVES_APPLIED"]);
}
else
{
leave_applied = 0.0;
}
if (leave_code_flag && max_leaves_flag && leave_applied <= max_leave)
{
if (Array.IndexOf(leave_codes_Master, leave_code) >= 0)
{
Hashtable hshParam = new Hashtable();
hshParam.Add("piempcode", employee_id);
hshParam.Add("pileavecode", leave_code);
hshParam.Add("piMaxleave", max_leave);
hshParam.Add("pileaveapplied", leave_applied);
db_connection.ExecuteStoredProcedureWithHashtable_WithoutReturn("spimportleave", hshParam);
Return_message += leave_code + " Leave assigned successfully for employee " + employee_id + Environment.NewLine;
}
else
{
Return_message += leave_code + " Leave does not belong for employee " + employee_id + Environment.NewLine;
}
}
}
else
{
Return_message += " You don't have permission to assign leave for Employee Code " + employee_id + " on row: " + row_number + Environment.NewLine;
}
}
else
{
Return_message += " Employee Code cannot be Empty on row: " + row_number + Environment.NewLine;
}
max_leaves_flag = true;
leave_code_flag = true;
}
rows[0] = total_rows.ToString();
rows[1] = effectd_rows;
}
return_object.status = "success";
//return_object.return_data = JsonConvert.SerializeObject(rows, Formatting.Indented);
return_object.return_data = Return_message;
}
catch (Exception ex)
{
Logger.LogException(ex, page, "IMPORT_FILE_DATA");
return_object.status = "error";
return_object.return_data = "An error occurred while loading Importing Leaves data. Please try again. If the error persists, please contact Support.";
}
return(return_object);
}
