public static ReturnObject GetEmployeeData(int page_number)
{
masters_employee page_object = new masters_employee();
DBConnection db_connection = new DBConnection();
ReturnObject return_object = new ReturnObject();
DataTable employee_data = new DataTable();
string
query = string.Empty,
comanager_id = string.Empty,
username = string.Empty,
branch_list = string.Empty;
int
start_row = (page_number - 1) * 30,
count = 0,
access = 0;
try
{
access = Convert.ToInt32(HttpContext.Current.Session["access_level"]);
username = HttpContext.Current.Session["username"].ToString();
query = page_object.GetBaseQuery();
query += " where Emp_Status = '1' ";
if (access == 1 || access == 3 || access == 2)
{
query += " and e.emp_code in (select EmpID from [FetchEmployees] ('" + username + "','')) ";
}
query += " group by e.emp_code, e.emp_name, c.CompanyCode, c.CompanyName, b.branchcode, b.branchname, d.deptcode, d.deptname, de.desigcode, de.designame, e.emp_card_no, e.emp_status , e.ot_eligibility , e.Ramadan_Eligibility ORDER BY e.emp_code, e.emp_name, c.CompanyName OFFSET " + start_row + " ROWS FETCH NEXT 30 ROWS ONLY";
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 refresh the page and try again. If the error persists, please contact Support.";
throw;
}
finally
{
page_object.Dispose();
}
return(return_object);
}
public static ReturnObject GetOtherData(string company_code)
{
masters_employee page_object = new masters_employee();
DBConnection db_connection = new DBConnection();
DataTable temp_data_table = new DataTable();
DataSet return_data_set = new DataSet();
ReturnObject return_object = new ReturnObject();
string department_query = string.Empty;
string designation_query = string.Empty;
string branch_query = string.Empty;
try
{
department_query = "Select DeptName as department_name, DeptCode as department_code from DeptMaster where CompanyCode='" + company_code + "' order by DeptCode";
temp_data_table = db_connection.ReturnDataTable(department_query);
temp_data_table.TableName = "department";
return_data_set.Tables.Add(temp_data_table);
designation_query = "Select desigcode as designation_code, designame as designation_name from DesigMaster where CompanyCode='" + company_code + "' order by desigcode";
temp_data_table = db_connection.ReturnDataTable(designation_query);
temp_data_table.TableName = "designation";
return_data_set.Tables.Add(temp_data_table);
temp_data_table = page_object.GetBranchData(company_code);
temp_data_table.TableName = "branch";
return_data_set.Tables.Add(temp_data_table);
return_object.status = "success";
return_object.return_data = JsonConvert.SerializeObject(return_data_set, Formatting.Indented);
}
catch (Exception ex)
{
Logger.LogException(ex, page, "GET_OTHER_DATA");
return_object.status = "error";
return_object.return_data = "An error occurred while loading data for OT Eligibility. Please try again. If the error persists, please contact Support.";
throw;
}
finally
{
page_object.Dispose();
}
return(return_object);
}
public static ReturnObject DoExportTransaction()
{
masters_employee page_object = new masters_employee();
ReturnObject return_object = new ReturnObject();
DBConnection db_connection = new DBConnection();
DataTable employee_transaction_data = new DataTable();
DateTime now = DateTime.Now;
int export_limit = Convert.ToInt32(ConfigurationManager.AppSettings["EXPORT_LIMIT"]);
string[] column_names = new string[] { };
string employee_id = string.Empty, company_code = string.Empty,
query = string.Empty, file_name = string.Empty;
try
{
employee_id = HttpContext.Current.Session["username"].ToString();
if (employee_id != "admin")
{
//Exporing perticular company employee data if user is not admin
query = "select emp_company from EmployeeMaster where Emp_Code='" + employee_id + "'";
company_code = db_connection.ExecuteQuery_WithReturnValueString(query);
query = "select TOP " + export_limit + " empid ,tt.transactionname ,fromdate , todate , transactiondata , case when isactive = 1 then 'Active' else 'InActive' end As Status from EmployeeTransactionData etd left join transactiontype tt on tt.TransactionType = etd.TransactionType where empid in (select empid from [fetchemployees]('" + employee_id + "','')) order by tt.TransactionName";
}
else
{
//Exporing All company employee data
query = "select TOP " + export_limit + " empid ,tt.transactionname ,fromdate , todate , transactiondata , case when isactive = 1 then 'Active' else 'InActive' end As Status from EmployeeTransactionData etd left join transactiontype tt on tt.TransactionType = etd.TransactionType where empid in (select empid from [fetchemployees]('admin','')) order by tt.TransactionName";
}
//query = "select TOP " + export_limit + " Emp_Code, Emp_Card_No, Emp_Name, Emp_Company, Emp_Branch, Emp_Department, Emp_Designation, Emp_Shift_Detail, Emp_Doj, Emp_Dob, Emp_Gender, Emp_Address, Emp_Phone, Emp_Email from EmployeeMaster";
employee_transaction_data = db_connection.ReturnDataTable(query);
if (employee_transaction_data.Rows.Count > 0)
{
file_name = page_object.CreateExportTransaction(employee_transaction_data);
return_object.status = "success";
return_object.return_data = file_name;
}
else
{
return_object.status = "info";
return_object.return_data = "No data available for export";
}
}
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 DoExport()
{
masters_employee page_object = new masters_employee();
ReturnObject return_object = new ReturnObject();
DBConnection db_connection = new DBConnection();
DataTable company_data = new DataTable();
DateTime now = DateTime.Now;
int export_limit = Convert.ToInt32(ConfigurationManager.AppSettings["EXPORT_LIMIT"]);
string[] column_names = new string[] { };
string employee_id = string.Empty, company_code = string.Empty,
query = string.Empty, file_name = string.Empty;
try
{
employee_id = HttpContext.Current.Session["username"].ToString();
if (employee_id != "admin")
{
//Exporing perticular company employee data if user is not admin
query = "select emp_company from EmployeeMaster where Emp_Code='" + employee_id + "'";
company_code = db_connection.ExecuteQuery_WithReturnValueString(query);
query = "select TOP " + export_limit + " Emp_Code,Emp_Name,Emp_Dob,Emp_Gender ,Emp_Address ,Emp_Phone,Emp_Email,Emp_Doj,Emp_Dol ,Emp_Company,Emp_Branch,Emp_Department,Emp_Designation,Emp_Employee_Category ,Emp_Card_No ,Emp_Shift_Detail ,Emp_Status ,OT_Eligibility ,Passport_No ,Passport_Exp_Date,Emirates_No ,Nationality ,Emergency_Contact_No,Visa_Exp_Date,IsManager,ManagerId,otflag,isreinstate ,IsHR,AbscondingDate,isAutoShiftEligible,employee_religion from employeemaster where Emp_Company='" + company_code + "'";
}
else
{
//Exporing All company employee data
query = "select TOP " + export_limit + " Emp_Code,Emp_Name,Emp_Dob,Emp_Gender ,Emp_Address ,Emp_Phone,Emp_Email,Emp_Doj,Emp_Dol ,Emp_Company,Emp_Branch,Emp_Department,Emp_Designation,Emp_Employee_Category ,Emp_Card_No ,Emp_Shift_Detail ,Emp_Status ,OT_Eligibility ,Passport_No ,Passport_Exp_Date,Emirates_No ,Nationality ,Emergency_Contact_No,Visa_Exp_Date,IsManager,ManagerId,otflag,isreinstate ,IsHR,AbscondingDate,isAutoShiftEligible,employee_religion from employeemaster";
}
//query = "select TOP " + export_limit + " Emp_Code, Emp_Card_No, Emp_Name, Emp_Company, Emp_Branch, Emp_Department, Emp_Designation, Emp_Shift_Detail, Emp_Doj, Emp_Dob, Emp_Gender, Emp_Address, Emp_Phone, Emp_Email from EmployeeMaster";
company_data = db_connection.ReturnDataTable(query);
if (company_data.Rows.Count > 0)
{
file_name = page_object.CreateExport(company_data);
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);
}
