public UserProfileResponse RetrieveAllUserProfiles(UserProfileRequest obj) { UserProfileResponse result = new UserProfileResponse(); DbConnectionHandler dbConnectionHandler = new DbConnectionHandler(); DbConnection dbConnection = dbConnectionHandler.GetSqlConnection(obj.ConnString); SqlTransaction transaction = null; try { dbConnectionHandler.OpenDbConnection(dbConnection); transaction = (SqlTransaction)dbConnection.BeginTransaction(); UserProfileBL userProfileBL = new UserProfileBL(); result.UserProfileList = userProfileBL.RetrieveAllUserProfiles(obj.UserProfileType, dbConnection, transaction); } catch (Exception ex) { AppLog.GetAppLog().Log("Error: occurred in retrieving from query RetriveAllUserProfiles."); AppLog.GetAppLog().Log(ex.ToString()); AppLog.GetAppLog().HasError = true; throw ex; } finally { dbConnectionHandler.CloseDbConnection(dbConnection); } return(result); }
static void Main(string[] args) { string databaseServerName = ""; string skipFilename = ""; string fileName = ""; string values_environment = ConfigurationManager.AppSettings.Get("Environment"); if (values_environment == "DEV") { fileName = ConfigurationManager.AppSettings.Get("DevFileName"); skipFilename = ConfigurationManager.AppSettings.Get("DevSkipFilename"); databaseServerName = ConfigurationManager.AppSettings.Get("DevDatabaseServer"); } if (values_environment == "TEST") { fileName = ConfigurationManager.AppSettings.Get("TestFileName"); skipFilename = ConfigurationManager.AppSettings.Get("TestSkipFilename"); databaseServerName = ConfigurationManager.AppSettings.Get("TestDatabaseServer"); } if (values_environment == "PROD") { fileName = ConfigurationManager.AppSettings.Get("ProdFileName"); skipFilename = ConfigurationManager.AppSettings.Get("ProdSkipFilename"); databaseServerName = ConfigurationManager.AppSettings.Get("ProdDatabaseServer"); } #region main block: try { //string[] usersSkip_array = System.IO.File.ReadAllLines(skipFilename); ExportToExcel excelFile = new ExportToExcel(fileName); excelFile.GenerateReport(usersSkip_array); //if file exist try to transfer. Commented to avoid accident trying to put on sftp server until Nov 27 // FTPExcelFiles(databaseServerName); System.Console.WriteLine("Process completed " + fileName); AppLog.GetAppLog().Log("Process completed " + fileName); //System.Console.ReadKey(); } catch (Exception ex) { AppLog.GetAppLog().Log("The task is terminated - Program.cs- " + ex.Message); } #endregion main block }
public void GenerateReport(string[] usersSkip_array) { try { UserProfileProcess retrieveProcess = new UserProfileProcess(); UserProfileDEMType requestType = new UserProfileDEMType(); var dt = new DataTable(); dt.Columns.Add("EmployeeId", typeof(string)); dt.Columns.Add("Username", typeof(string)); dt.Columns.Add("LastName", typeof(string)); dt.Columns.Add("FirstName", typeof(string)); dt.Columns.Add("MiddleName", typeof(string)); dt.Columns.Add("Hiredate", typeof(string)); dt.Columns.Add("ActiveAccount", typeof(string)); dt.Columns.Add("GroupTypeid", typeof(string)); dt.Columns.Add("DemogCode", typeof(string)); dt.Columns.Add("Sitecode", typeof(string)); dt.Columns.Add("PrimarySite", typeof(string)); dt.Columns.Add("Field1", typeof(string)); dt.Columns.Add("Field2", typeof(string)); dt.Columns.Add("Field3", typeof(string)); dt.Columns.Add("Field4", typeof(string)); dt.Columns.Add("Field5", typeof(string)); foreach (var userStaff in retrieveProcess.RetrieveAllUserProfiles(requestType)) { string username_string; username_string = userStaff.Username; flag_counting_ocurrance = false; foreach (string line in usersSkip_array) { if (username_string.Trim().ToLower() == line.Trim().ToLower()) { flag_counting_ocurrance = true; } } if (flag_counting_ocurrance == false) { dt.Rows.Add( userStaff.EmployeeId, userStaff.Username, userStaff.Lastname, userStaff.Firstname, userStaff.Middlename, userStaff.Hiredate, userStaff.Activeaccount, userStaff.Grouptypeid, userStaff.Demogcode, userStaff.Sitecode, userStaff.Primarysite, userStaff.Field1, userStaff.Field2, userStaff.Field3, userStaff.Field4, userStaff.Field5 ); } } using (ExcelPackage p = new ExcelPackage()) { var workbook = p.Workbook; var worksheet = workbook.Worksheets.Add("Sheet1"); var cell1 = worksheet.Cells["A1"]; var cell2 = worksheet.Cells["B1"]; var cell3 = worksheet.Cells["C1"]; var cell4 = worksheet.Cells["D1"]; var cell5 = worksheet.Cells["E1"]; var cell6 = worksheet.Cells["F1"]; var cell7 = worksheet.Cells["G1"]; var cell8 = worksheet.Cells["H1"]; var cell9 = worksheet.Cells["I1"]; var cell10 = worksheet.Cells["J1"]; var cell11 = worksheet.Cells["K1"]; var cell12 = worksheet.Cells["L1"]; var cell13 = worksheet.Cells["M1"]; var cell14 = worksheet.Cells["N1"]; var cell15 = worksheet.Cells["O1"]; var cell16 = worksheet.Cells["P1"]; var cell17 = worksheet.Cells["Q1"]; var cell18 = worksheet.Cells["R1"]; cell1.Value = "EmployeeId"; cell2.Value = "UserName"; cell3.Value = "LastName"; cell4.Value = "Password"; cell5.Value = "FirstName"; cell6.Value = "MiddleName"; cell7.Value = "Hiredate"; cell8.Value = "Email"; cell9.Value = "ActiveAccount"; cell10.Value = "GroupTypeid"; cell11.Value = "DemogCode"; cell12.Value = "SiteCode"; cell13.Value = "PrimarySite"; cell14.Value = "Field1"; cell15.Value = "Field2"; cell16.Value = "Field3"; cell17.Value = "Field4"; cell18.Value = "Field5"; worksheet.Cells.LoadFromDataTable(dt, true); p.SaveAs(new System.IO.FileInfo(filename)); AppLog.GetAppLog().Log("Export file is completed... " + filename); } dt.Clear(); } catch (Exception ex) { AppLog.GetAppLog().Log("The task is terminated in Export to Excel.cs - " + ex.Message); } }
private static void FTPExcelFiles(string databaseServerName) { try { string serverCorePath = ""; string fileName = ""; string values_environment = ConfigurationManager.AppSettings.Get("Environment"); String SshPrivateKeyPath = ConfigurationManager.AppSettings.Get("SshPrivateKeyPath"); String winSCP_Path = ConfigurationManager.AppSettings.Get("winscp_Path"); if (values_environment == "DEV") { fileName = ConfigurationManager.AppSettings.Get("DevFilename"); } if (values_environment == "PROD") { fileName = ConfigurationManager.AppSettings.Get("ProdFilename"); } if (values_environment == "TEST") { fileName = ConfigurationManager.AppSettings.Get("TestFilename"); } //* IDHS Dev: if (databaseServerName.Equals(Constants.SQL_SERVER_620_21_NAME)) { serverCorePath = Constants.SFTPTestCorePath; } //* IDHS Test: else if (databaseServerName.Equals(Constants.SQL_SERVER_634_01_NAME)) { serverCorePath = Constants.SFTPTestCorePath; } //* IDHS Prod: else if (databaseServerName.Equals(Constants.SQL_SERVER_611_03_NAME)) { serverCorePath = Constants.SFTPProdCorePath; } else { AppLog.GetAppLog().Log("Wrong database server name: " + databaseServerName); AppLog.GetAppLog().HasError = true; throw new Exception("Wrong database server name: " + databaseServerName); } SessionOptions sessionOptions = new SessionOptions { Protocol = Protocol.Sftp, HostName = Constants.SFTPHostName, UserName = Constants.SFTPUserName, Password = Constants.SFTPPassword, SshHostKeyFingerprint = Constants.SFTPSshHostKeyFingerprint, PortNumber = Constants.SFTPPortNumber, PrivateKeyPassphrase = Constants.PrivateKeyPassphrase, //SshPrivateKeyPath key file to connecto to sftp host server SshPrivateKeyPath = SshPrivateKeyPath }; AppLog.GetAppLog().Log("Done SFTP session options setting."); using (Session session = new Session()) { //* Connect try { session.ExecutablePath = winSCP_Path; session.Open(sessionOptions); } catch (Exception e) { AppLog.GetAppLog().Log("Could not open WinSCP ln 51 on program.cs " + e.Message); throw new Exception(e.Message); } AppLog.GetAppLog().Log("Open SFTP Session..."); //* Upload files TransferOptions transferOptions = new TransferOptions(); transferOptions.TransferMode = TransferMode.Binary; transferOptions.FilePermissions = null; transferOptions.PreserveTimestamp = false; //** Upload core XML files: TransferOperationResult coreTransferResult; //coreTransferResult = session.PutFiles(sourceFilePath + @"CSSD_Core_*", serverCorePath, false, transferOptions); coreTransferResult = session.PutFiles(fileName, serverCorePath, false, transferOptions); //* Throw on any error coreTransferResult.Check(); //* Log core results bool coreFilesExisting = false; foreach (TransferEventArgs transfer in coreTransferResult.Transfers) { coreFilesExisting = true; AppLog.GetAppLog().Log("Upload file succeeded: " + transfer.FileName); } if (coreFilesExisting) { //coreTransferResult = session.PutFiles(sourceFilePath + Constants.SFTPFlagFile, serverCorePath, false, transferOptions); coreTransferResult = session.PutFiles(fileName, serverCorePath, false, transferOptions); AppLog.GetAppLog().Log("Uploaded file to vendor's SFTP host."); } } } catch (Exception ex) { AppLog.GetAppLog().Log("Error: occurred in SFTP xml files."); AppLog.GetAppLog().Log(ex.ToString()); AppLog.GetAppLog().HasError = true; throw ex; } AppLog.GetAppLog().Log("SFTP Process is done."); }