public static ReturnObject DoExport()
{
outofoffice_approve page_object = new outofoffice_approve();
ReturnObject return_object = new ReturnObject();
DBConnection db_connection = new DBConnection();
DataTable ooo_data = new DataTable();
DateTime now = DateTime.Now;
int export_limit = Convert.ToInt32(ConfigurationManager.AppSettings["EXPORT_LIMIT"]),
user_access_level = 0;
string[] column_names = new string[] { };
string employee_id = string.Empty,
query = string.Empty, file_name = string.Empty;
user_access_level = Convert.ToInt32(HttpContext.Current.Session["access_level"]);
employee_id = HttpContext.Current.Session["employee_id"].ToString();
try
{
query = " select TOP " + export_limit + " Emp_ID 'EmployeeID',Emp_Name 'EmployeeName',OOO_type_name 'OutOfOffice Type',FromDateTime ,ToDateTime,";
query += " CONVERT(VARCHAR, RIGHT('0' + RTRIM(OOO_Minutes/60),3) + ':' + RIGHT('0' + RTRIM(OOO_Minutes%60),2)) as Hours, ";
query += " CONVERT(VARCHAR, RIGHT('0' + RTRIM(Total_OOO_Minutes/60),3) + ':' + RIGHT('0' + RTRIM(Total_OOO_Minutes%60),2)) as TotalHours, ";
query += " Reason, ";
if (user_access_level == 3)
{
query += " Manager_Name 'Manager Name',Manager_Remark 'Manager Remark', ";
query += " case when Manager_Status=1 then 'Submiited' when Manager_Status=2 then 'Approved' when Manager_Status=3 then 'Rejected' end as Approval,";
query += " case when HR_Status=1 then 'Submiited' when HR_Status=2 then 'Approved' when HR_Status=3 then 'Rejected' else 'Manager Action Pending' end as HRApproval";
}
if (user_access_level == 1)
{
query += " case when Manager_Status=1 then 'Submiited' when Manager_Status=2 then 'Approved' when Manager_Status=3 then 'Rejected' end as Approval";
}
query += " from outoffoffice O join OOOType OT on O.OOO_type=OT.OOO_type_id ";
if (user_access_level == 3)
{
query += "where Emp_ID in (select Emp_Code from EmployeeMaster where Emp_Branch=(select Emp_Branch from EmployeeMaster where Emp_Code='" + employee_id + "' ))";
}
if (user_access_level == 1)
{
query += "where Emp_ID in (select Emp_Code from EmployeeMaster where ManagerID='" + employee_id + "' )";
}
ooo_data = db_connection.ReturnDataTable(query);
query += "order by OOO_ID ";
if (ooo_data.Rows.Count > 0)
{
file_name = page_object.CreateExport(ooo_data, user_access_level);
return_object.status = "success";
return_object.return_data = file_name;
}
else
{
return_object.status = "info";
return_object.return_data = "No data found with the selected filters. Please try again with different filters.";
}
}
catch (Exception ex)
{
Logger.LogException(ex, page, "GET_DATA_FOR_EXPORT");
return_object.status = "error";
return_object.return_data = "An error occurred while generating your report. Please try again. If the error persists, please contact Support.";
throw;
}
finally
{
page_object.Dispose();
}
return(return_object);
}
public static ReturnObject DoAction(int action, string comments, string selected_rows)
{
outofoffice_approve page_object = new outofoffice_approve();
DBConnection db_connection = new DBConnection();
ReturnObject return_object = new ReturnObject();
List <string> selected_out_off_office = JsonConvert.DeserializeObject <List <string> >(selected_rows);
string
query = string.Empty, return_message = string.Empty,
ooo_status = string.Empty;
long
ooo_id = 0;
int update_flag = 0, Existsing_Status = 0;
try
{
switch (action)
{
case 2:
ooo_status = "Approved";
update_flag = 2;
break;
case 3:
ooo_status = "Rejected";
update_flag = 3;
break;
}
for (int i = 0; i < selected_out_off_office.Count; i++)
{
ooo_id = Convert.ToInt64(selected_out_off_office[i].ToString());
query = "select Status from outoffoffice where OOO_ID='" + ooo_id + "' ";
Existsing_Status = db_connection.ExecuteQuery_WithReturnValueInteger(query);
if (Existsing_Status == 2)
{
return_message += "Out Off Office punch is already approved" + Environment.NewLine;
}
else if (Existsing_Status == 3)
{
return_message += "Out Off Office punch is already rejected" + Environment.NewLine;
}
else
{
page_object.ApproveOutOffOffice(ooo_id, update_flag, comments);
return_message = "Out Off Office " + ooo_status + " successfully!" + Environment.NewLine;
}
}
return_object.status = "success";
return_object.return_data = return_message;
}
catch (Exception ex)
{
Logger.LogException(ex, page, "UPDATE_OUT_OFF_OFFICE_APPROVAL_STATUS");
return_object.status = "error";
return_object.return_data = "An error occurred while updating Out Off Office Approval Status. Please try again. If the error persists, please contact Support.";
}
finally
{
page_object.Dispose();
}
return(return_object);
}
protected void ApproveOutOffOffice(long ooo_id, int status_flag, string comments)
{
outofoffice_approve page_object = new outofoffice_approve();
DBConnection db_connection = new DBConnection();
Hashtable hsh = new Hashtable();
DataTable out_off_office_data = new DataTable();
DataTable employee_data = new DataTable();
Hashtable approved_list = new Hashtable();
string query = string.Empty, user_name = string.Empty,
emp_id = string.Empty, card_no = string.Empty,
PunchDate1 = string.Empty, Punch_Time1 = string.Empty,
PunchDate2 = string.Empty, Punch_Time2 = string.Empty,
employee_id = string.Empty, employee_name = string.Empty;
int user_access_level = 0, HR_Status = 0, Manager_Status = 0;
try
{
user_name = HttpContext.Current.Session["username"].ToString();
employee_id = HttpContext.Current.Session["employee_id"].ToString();
employee_name = HttpContext.Current.Session["employee_name"].ToString();
user_access_level = Convert.ToInt32(HttpContext.Current.Session["access_level"]);
if (user_access_level == 0)
{
employee_name = "Admin";
query = "Update outoffoffice set Status=" + status_flag + ",Manager_Name='Admin',HR_Name='Admin' where OOO_ID=" + ooo_id;
db_connection.ExecuteQuery_WithOutReturnValue(query);
}
if (user_access_level == 1)
{
query = "Update outoffoffice set Manager_Status=" + status_flag + ",Manager_Name='" + employee_name + "',Manager_Remark='" + comments + "' where OOO_ID=" + ooo_id;
db_connection.ExecuteQuery_WithOutReturnValue(query);
if (status_flag == 2)
{
query = "Select HR_Status from outoffoffice where OOO_ID=" + ooo_id;
HR_Status = db_connection.ExecuteQuery_WithReturnValueInteger(query);
if (HR_Status == 3)
{
query = "Update outoffoffice set Status=" + status_flag + " where OOO_ID=" + ooo_id;
db_connection.ExecuteQuery_WithOutReturnValue(query);
/*insert approved punch into master trans raw# as well as process table*/
approved_list.Add("OOO_ID", ooo_id);
db_connection.ExecuteStoredProcedureReturnStringNoPI("SaveApprovedOOO", approved_list);
}
if (HR_Status == 0)
{
query = "Update outoffoffice set HR_Status=1 where OOO_ID=" + ooo_id;
db_connection.ExecuteQuery_WithOutReturnValue(query);
}
}
if (status_flag == 3)
{
query = "Update outoffoffice set Status=3 where OOO_ID=" + ooo_id;
db_connection.ExecuteQuery_WithOutReturnValue(query);
}
}
if (user_access_level == 3)
{
if (status_flag == 3)
{
query = "Update outoffoffice set HR_Status=3, HR_Name='" + employee_name + "',HR_Remark='" + comments + "' where OOO_ID=" + ooo_id;
db_connection.ExecuteQuery_WithOutReturnValue(query);
query = "Update outoffoffice set Manager_Status=1 where OOO_ID=" + ooo_id;
db_connection.ExecuteQuery_WithOutReturnValue(query);
}
if (status_flag == 2)
{
query = "Update outoffoffice set Status=2,HR_Status=2,HR_Name='" + employee_name + "',HR_Remark='" + comments + "' where OOO_ID=" + ooo_id;
db_connection.ExecuteQuery_WithOutReturnValue(query);
/*insert approved punch into master trans raw# as well as process table*/
approved_list.Add("OOO_ID", ooo_id);
db_connection.ExecuteStoredProcedureReturnStringNoPI("SaveApprovedOOO", approved_list);
}
}
SendMail(ooo_id, status_flag);
}
catch (Exception ex)
{
Logger.LogException(ex, page, "APPROVE_MISSING_PUNCH");
}
}
public static ReturnObject GetOutOffOfficeData(int page_number, bool is_filter, string filters)
{
outofoffice_approve page_object = new outofoffice_approve();
DBConnection db_connection = new DBConnection();
ReturnObject return_object = new ReturnObject();
DataTable out_off_office_data_table = new DataTable();
DataTable branch_list_data = new DataTable();
DataTable CoManagerID_data = new DataTable();
string user_name = string.Empty,
employee_id = string.Empty,
query = string.Empty,
CoManagerID = string.Empty,
BranchList = string.Empty;
int user_access_level = 0,
start_row = 0,
number_of_record = 0,
IsDelegationManager = 0;
try
{
user_name = HttpContext.Current.Session["username"].ToString();
employee_id = HttpContext.Current.Session["employee_id"].ToString();
user_access_level = Convert.ToInt32(HttpContext.Current.Session["access_level"]);
start_row = (page_number - 1) * 30;
number_of_record = page_number * 30 + 1;
//get list of branches assigned to logged in manager hr
query = "Select BranchCode From TbManagerHrBranchMapping Where ManagerID='" + employee_id + "'";
branch_list_data = db_connection.ReturnDataTable(query);
query = string.Empty;
if (branch_list_data.Rows.Count > 0)
{
foreach (DataRow dr in branch_list_data.Rows)
{
BranchList += "'" + dr["BranchCode"] + "',";
}
BranchList = BranchList.TrimEnd(',');
}
else
{
BranchList = "'Empty'";
}
query = page_object.GetBaseQuery(user_access_level); //read main query
query += " and Emp_ID !='" + employee_id + "'"; //don't show data for logged in employee
if (user_access_level == 0)
{
query += " and Emp_ID in (select Emp_Code from EmployeeMaster where Emp_Status=1)";
}
if (user_access_level == 1)
{
// query += " and Emp_ID in (select Emp_Code from EmployeeMaster where ManagerID='" + employee_id + "' )";
// query += " and Emp_ID in (select empid from fetchemployees ( '"+ employee_id + "' )";
query += string.Format("and Emp_Id in (Select empid from fetchemployees('{0}',''))", employee_id);
}
if (user_access_level == 3)
{
query += " and Emp_ID in ( select Emp_Code from EmployeeMaster where Emp_Branch In (" + BranchList + "))";
}
if (is_filter)
{
query = page_object.GetFilterQuery(filters, query, user_access_level);
}
else
{
if (user_access_level == 3)
{
query += " and O.HR_Status =1 ";
}
if (user_access_level == 1)
{
query += " and O.Manager_Status =1 ";
}
DateTime date = DateTime.Now.Date;
string from_date_temp = new DateTime(date.Year, date.Month, 1).ToString("dd-MMM-yyyy");
string to_date_temp = Convert.ToDateTime(from_date_temp).AddMonths(1).AddDays(-1).ToString("dd-MMM-yyyy");
query += " and FromDateTime>='" + from_date_temp + " 00:00' and ToDateTime<='" + to_date_temp + " 23:59'";
}
query += " ) a where row > " + start_row + " and row < " + number_of_record;
query += " order by OOO_ID";
out_off_office_data_table = db_connection.ReturnDataTable(query);
return_object.status = "success";
return_object.return_data = JsonConvert.SerializeObject(out_off_office_data_table, Formatting.Indented);
}
catch (Exception ex)
{
Logger.LogException(ex, page, "GET_OUT_OFF_OFFICE_DATA");
return_object.status = "error";
return_object.return_data = "An error occurred while performing this operation. Please try again. If the error persists, please contact Support.";
}
return(return_object);
}
