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.");
}