protected void Export1_Click(object sender, EventArgs e)
{
DataSet _DataList = null;
_DataList = DatabaseManager.Data.DBAccessManager.RetrieveImportedErrorData(Int32.Parse(Page.User.Identity.Name.ToString()), StringEnum.stringValueOf(Constants.ImportType.Finance_01));
if (_DataList.Tables[0].Rows.Count > 0)
{
DataTable _dataTable = _DataList.Tables[0];
ExportToExcel(_dataTable);
}
}
protected void Import_Click(object sender, EventArgs e)
{
try
{
Tab1_Message.CssClass = "errorMessage";
Tab1_Message.Visible = true;
string extension = System.IO.Path.GetExtension(fileuploadExcel.PostedFile.FileName);
if (fileuploadExcel.FileName != "") //&& extension == ".xls")
{
string excelConnectionString;
SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionInfo"));
string connStr = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionInfo");
// string path = fileuploadExcel.PostedFile.FileName;
string path = Server.MapPath(Page.ResolveUrl("~\\App_Data\\") + fileuploadExcel.FileName);
fileuploadExcel.SaveAs(path);
// string a = fileuploadExcel.PostedFile.FileName;
string tableName = StringEnum.stringValueOf(Constants.ImportTableName.FinanceTransaction);
//Create connection string to Excel work book
if (extension == ".xls")
{
excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path +
";Extended Properties=Excel 8.0";
}
else if (extension == ".csv")
{
excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path +
";Extended Properties=text;FMT=Delimited()";
}
else
{
excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path +
";Extended Properties=Excel 12.0";
}
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
connection.Open();
SqlCommand comm = new SqlCommand("delete from " + tableName, connection);
comm.ExecuteNonQuery();
SqlCommand identityChange = connection.CreateCommand();
identityChange.CommandText = "SET IDENTITY_INSERT " + tableName + " ON";
OleDbCommand cmd = new OleDbCommand("Select RowID,PSEmployeeID,ComponentCode,Amount from [Sheet1$] where RowID > 0", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
identityChange.ExecuteNonQuery();
SqlBulkCopy sqlBulk = new SqlBulkCopy(connStr);
//Give your Destination table name
sqlBulk.DestinationTableName = tableName;
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
connection.Close();
// Validate the data in the staging table and return all bad data.
DataSet _DataList = null;
_DataList = DatabaseManager.Data.DBAccessManager.ValidateImportedFinanceTransactions(Int32.Parse(Page.User.Identity.Name.ToString()));
if (_DataList.Tables.Count > 0)
{
if (_DataList.Tables[0].Rows.Count > 0) // Bad Data exists
{
DataTable _DeductionTable = _DataList.Tables[0];
this.Tab1Grid.DataSource = _DeductionTable;
this.Tab1Grid.DataBind();
Export1.Visible = true;
Tab1_Message.Text = "The following data was not imported due to validation error. Please fix them and re-import.";
}
else
{
this.Tab1Grid.DataSource = null;
this.Tab1Grid.DataBind();
Export1.Visible = false;
Tab1_Message.Text = "All data has been successfully imported into the system";
}
}
}
else
{
Tab1_Message.CssClass = "errorMessage";
Tab1_Message.Text = "Please select the excel(.xlsx) file to import.You should save the file as 'Excel Workbook' for the import process to work.";
}
}
catch (Exception exception)
{
this.Tab1_Message.Visible = true;
this.Tab1_Message.Text = ErrorLogging.LogError(exception, "Unknown Exception Occured. Please check the data in the import file.");
this.Tab1_Message.CssClass = "errorMessage";
}
}
protected void Grid_EditCommand(object source, DataGridCommandEventArgs e)
{
try
{
if (e.CommandName == "Edit")
{
this.Hmessage.Text = "";
this.Hmessage.Visible = false;
this.Tab1_keyField.Text = Grid.DataKeys[e.Item.ItemIndex].ToString();
Grid.EditItemIndex = -1;
if (EmptyString(e.Item.Cells[0].Text) != "")
{
Tab1_RequestType.SelectedValue = Tab1_RequestType.Items.FindByText(e.Item.Cells[0].Text).Value;
}
if ((EmptyString(e.Item.Cells[0].Text) == StringEnum.stringValueOf(Constants.RequestType.Onboarding)) || (EmptyString(e.Item.Cells[0].Text) == StringEnum.stringValueOf(Constants.RequestType.Personal)))
{
EmployeeSelection.Visible = true;
if (EmptyString(e.Item.Cells[0].Text) == StringEnum.stringValueOf(Constants.RequestType.Onboarding)) // Onboarding Request
{
LoadNewHiresList(Tab1_NewHiresList, EmpId, (int)Constants.EmpStatusType.PreEmployment);
}
else if (EmptyString(e.Item.Cells[0].Text) == StringEnum.stringValueOf(Constants.RequestType.Personal)) // Personal Action Request
{
LoadNewHiresList(Tab1_NewHiresList, EmpId, (int)Constants.EmpStatusType.Payroll);
}
Tab1_NewHiresList.SelectedValue = Tab1_NewHiresList.Items.FindByValue(e.Item.Cells[5].Text).Value;
}
if (EmptyString(e.Item.Cells[7].Text) != "")
{
string Implementors = EmptyString(e.Item.Cells[7].Text);
string[] ImplTypes = Implementors.Split(';');
foreach (string word in ImplTypes)
{
Tab1_RequestImplType.Items.FindByText(word).Selected = true;
}
}
Tab1_Note.Text = EmptyString(e.Item.Cells[4].Text);
if (EmptyString(e.Item.Cells[6].Text) != "")
{
Tab1_RequestStatusType.Visible = true;
Tab1_RequestStatusType.SelectedValue = Tab1_RequestStatusType.Items.FindByText(e.Item.Cells[6].Text).Value;
}
// Generate Salary Certificate
if ((EmptyString(e.Item.Cells[0].Text) == StringEnum.stringValueOf(Constants.RequestType.SalCertificate)))
{
GenerateSalaryCertificate();
}
if ((EmptyString(e.Item.Cells[6].Text) != StringEnum.stringValueOf(Constants.RequestStatusType.Submitted)) && (EmptyString(e.Item.Cells[6].Text) != StringEnum.stringValueOf(Constants.RequestStatusType.PendingApproval)))
{
this.Hmessage.Visible = true;
this.Hmessage.CssClass = "errorMessage";
if (Page.User.IsInRole("HR Administrator") && (EmptyString(e.Item.Cells[6].Text) != StringEnum.stringValueOf(Constants.RequestStatusType.Closed)))
{
this.Hmessage.Text = "This request can be only closed at this stage.";
this.Send_Request.Enabled = true;
this.Cancel.Enabled = true;
this.Tab1_RequestType.Enabled = false;
this.Tab1_RequestImplType.Enabled = false;
this.Tab1_NewHiresList.Enabled = false;
}
else
{
this.Hmessage.Text = "This request can be only viewed at this stage.";
this.Send_Request.Enabled = false;
this.Cancel.Enabled = true;
}
}
}
}
catch (Exception exception)
{
this.Hmessage.Visible = true;
this.Hmessage.Text = ErrorLogging.LogError(exception, "Unknown Exception Occured.Please contact Support.");
this.Hmessage.CssClass = "errorMessage";
}
}
private void LoadErrorDataList()
{
try
{
DataSet _DataList = null;
_DataList = DatabaseManager.Data.DBAccessManager.RetrieveImportedErrorData(Int32.Parse(Page.User.Identity.Name.ToString()), StringEnum.stringValueOf(Constants.ImportType.Finance_01));
if (_DataList.Tables.Count > 0)
{
if (_DataList.Tables[0].Rows.Count > 0) // Deductions Information
{
DataTable _DeductionTable = _DataList.Tables[0];
this.Tab1Grid.DataSource = _DeductionTable;
this.Tab1Grid.DataBind();
Export1.Visible = true;
}
else
{
this.Tab1Grid.DataSource = null;
this.Tab1Grid.DataBind();
Export1.Visible = false;
}
}
}
catch (Exception exception)
{
ErrorLogging.LogError(exception, "");
}
}
protected void Page_Load(object sender, EventArgs e)
{
EmpId = Int32.Parse(Page.User.Identity.Name.ToString());
if (!Page.IsPostBack)
{
LoadRequestTypeLookup(Tab1_RequestType, EmpId);
LoadRequestImplTypeLookup(Tab1_RequestImplType, EmpId);
LoadRequestStatusTypeLookup(Tab1_RequestStatusType);
LoadNewHiresList(Tab1_NewHiresList, EmpId, (int)Constants.EmpStatusType.PreEmployment);
}
if ((Tab1_RequestType.SelectedItem.ToString() == StringEnum.stringValueOf(Constants.RequestType.Onboarding)) || (Tab1_RequestType.SelectedItem.ToString() == StringEnum.stringValueOf(Constants.RequestType.Personal))) // Onboarding Request/Personal Action Request
{
EmployeeSelection.Visible = true;
}
else
{
EmployeeSelection.Visible = false;
}
LoadEmployeeRequests();
}