public static void RunPropertyExportFileNow(string name)
{
ReportLogger reportLogger = null;
try
{
ExportItem exportItem = Get.GetExportItem(name);
reportLogger = new ReportLogger(exportItem.ExportItemName);
reportLogger.StartLog("Exporting : " + exportItem.ExportItemName);
exportItem.Export(reportLogger);
reportLogger.EndLog(exportItem.ExportItemName + " : Exported");
}
catch (Exception exception)
{
if (reportLogger != null)
{
reportLogger.EndLog(exception);
}
}
}
public CsvDataExporter(ExportItem exportItem)
{
PropertyExporterLogger = new ReportLogger(exportItem.ExportItemName);
if (!CoreDataLib.IsLive())
{
ServerPath = @"\\SVRsql4\E$\CoreData\ExportFiles\Test\";
ExportServerTemp = @"E:\CoreData\ExportFiles\Test\Temp\";
TempServerPath = ServerPath + @"Temp\";
}
else
{
ServerPath = "E:\\CoreData\\ExportFiles\\";
ExportServerTemp = @"E:\CoreData\ExportFiles\Temp\";
TempServerPath = ServerPath + @"Temp\";
}
ExportItem = exportItem;
m_pathAndFileName = TempServerPath + exportItem.ExportItemName + ".csv";
m_selectClause = exportItem.SelectStatementBuilder.SelectStatement();
m_selectStatementBuilder = exportItem.SelectStatementBuilder;
}
public static void FtpFile(FileInfo fileToFtp, string ftpTarget)
{
ReportLogger ftpFileReportLogger = new ReportLogger("FtpFile");
int stepId = ftpFileReportLogger.AddStep();
try
{
//ftpUploadUri = "ftp://ftp.lowcosttravelgroup.com/BusinessRules/";
FtpWebRequest request = (FtpWebRequest)WebRequest.Create(ftpTarget + fileToFtp.Name);
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential("XTGtravelgate", "LcH3rdparty");
request.Timeout = 60 * 10000;
FileStream fileStream = new FileStream(fileToFtp.FullName, FileMode.Open, FileAccess.Read);
Stream requestStream = request.GetRequestStream();
byte[] buffer = new byte[8092];
int read = 0;
while ((read = fileStream.Read(buffer, 0, buffer.Length)) != 0)
{
requestStream.Write(buffer, 0, read);
}
requestStream.Flush();
requestStream.Close();
fileStream.Close();
ftpFileReportLogger.EndStep(stepId);
}
catch (Exception e)
{
ftpFileReportLogger.EndStep(stepId, e);
}
ftpFileReportLogger.EndLog();
}
public void CsvExport(ReportLogger reportLogger)
{
int stepId = reportLogger.AddStep();
try
{
FileInfo fileInfo = new FileInfo(m_pathAndFileName);
FileInfo serverPathAndFile = new FileInfo(ServerPath + fileInfo.Name);
FileInfo serverPath = new FileInfo(ServerPath);
FileInfo tempDirectory = new FileInfo(ServerPath + @"Temp\");
string filename = fileInfo.Name.Split('.')[0];
ServerPathAndFile = serverPathAndFile.FullName;
StringBuilder headers = new StringBuilder();
logger.AppendLine("Create Headers");
foreach (string header in m_selectStatementBuilder.GetOutputHeaders)
{
headers.Append(header + "|");
}
headers.Remove(headers.Length - 1, 1);
logger.AppendLine("Headers Created");
using (SqlConnection conn = new SqlConnection(DataConnection.SqlConnCoreData))
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandTimeout = 60000;
sqlCommand.Connection = conn;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(@"DECLARE @bcpCmd as VARCHAR(8000);");
sqlBuilder.Append(@"SET @bcpCmd= 'bcp ""SET NOCOUNT ON;");
sqlBuilder.Append(m_selectClause);
sqlBuilder.Append(@";SET NOCOUNT OFF;""");
sqlBuilder.Append(" queryout ");
sqlBuilder.Append(ExportServerTemp + fileInfo.Name);
sqlBuilder.Append(" -t \"" + m_del + "\" -r(!!!!!!!!!!)");
if (CoreDataLib.IsLive())
{
sqlBuilder.Append(" -c -C ACP -S SVRsql4 -d CoreData -U CoreData -P CoreD@T@'; EXEC master..xp_cmdshell @bcpCmd;");
}
else
{
sqlBuilder.Append(" -c -C ACP -S SVRsql4 -d CoreData_Test -U CoreData -P CoreD@T@'; EXEC master..xp_cmdshell @bcpCmd;");
}
sqlBuilder.Replace("@LangId", m_selectStatementBuilder.LanguageId);
string sql = sqlBuilder.ToString();
sqlCommand.CommandText = sql;
conn.Open();
sqlCommand.ExecuteNonQuery();
if (File.Exists(tempDirectory + fileInfo.Name))
{
CreateCsvFile(tempDirectory, filename, fileInfo, headers, serverPathAndFile, serverPath, m_selectStatementBuilder.LanguageId);
}
else
{
Emailer.SendEmail("*****@*****.**", "CsvDataExporter [SVRSQL4]: - Error : ", "File not found : " + tempDirectory);// + fileInfo.Name + " : " + DateTime.Now.ToLongDateString() + " : " + DateTime.Now.ToLongTimeString());
}
reportLogger.EndStep(stepId);
// StringBuilder sqlBuilder = new StringBuilder();
// sqlBuilder.Append(m_selectClause);
// sqlBuilder.Replace("@LangId", m_selectStatementBuilder.LanguageId);
// string sql = sqlBuilder.ToString();
// sqlCommand.CommandText = sql;
// conn.Open();
// DataTable dataTable = new DataTable();
// dataTable.Load(sqlCommand.ExecuteReader());
// foreach (DataRow row in dataTable.Rows)
// {
// for (int i = 0; i <= dataTable.Columns.Count - 1; i++)
// {
// if (row[i].ToString().Contains("|"))
// {
// string newValue = row[i].ToString().Replace("|", "");
// row[i] = newValue;
// }
// }
// }
// if (File.Exists(tempDirectory + fileInfo.Name))
// {
////CsvToXmlParser xmlParser = new CsvToXmlParser(serverPathAndFile);
// CoreDataLib.CreateCSVfile(dataTable, serverPathAndFile.FullName, "|");
// }
// else
// {
// Emailer.SendEmail("*****@*****.**", "CsvDataExporter [SVRSQL4]: - Error : ", "File not found : " + tempDirectory);// + fileInfo.Name + " : " + DateTime.Now.ToLongDateString() + " : " + DateTime.Now.ToLongTimeString());
// }
// reportLogger.EndStep(stepId);
}
}
catch (Exception exception)
{
reportLogger.EndStep(stepId, exception);
}
}