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; }
private string BuildSelectStatement() { StringBuilder selectStatement = new StringBuilder("SELECT "); selectStatement.Append(PopulateFields()); if (CoreDataLib.IsLive()) { selectStatement.Append(@" FROM [CoreData].[dbo].[Properties] AS p1 JOIN [CoreData].[dbo].[PropertyDetails] AS p3 ON p3.PropertyReferenceId = p1.PropertyReferenceId LEFT JOIN [CoreData].[dbo].[PropertyDescription] AS p4 ON p4.PropertyReferenceId = p1.PropertyReferenceId AND p4.LanguageId = "); selectStatement.Append(LanguageId); selectStatement.Append(" LEFT JOIN [CoreData].[dbo].[ResortAirport] AS p5 ON p5.ResortId = p1.ResortId LEFT JOIN [CoreData].[dbo].[Airports] AS p6 ON p6.AirportId = p5.AirportId LEFT JOIN [CoreData].[dbo].[PropertyHighQualityImageLists] AS p7 ON p7.PropertyReferenceId = p1.PropertyReferenceId LEFT JOIN [CoreData].[dbo].[PropertyFacilitiesList] AS p9 ON p9.PropertyReferenceId = p1.PropertyReferenceId AND p9.LanguageId = "); selectStatement.Append(LanguageId); selectStatement.Append(" LEFT JOIN [CoreData].[dbo].[TTIProperty] AS p11 ON p11.ID = p1.PropertyReferenceId"); if (LanguageId != "1") { selectStatement.Append(" JOIN [IVDB].[LCH].[dbo].[TranslationLookup] as p10 ON p10.SourceID = p1.CountryId AND p10.TranslationID = 5 AND p10.LanguageId = "); selectStatement.Append(LanguageId); } selectStatement.Append(" LEFT JOIN [CoreData].[dbo].[PropertyUrls] AS p13 ON p13.PropertyReferenceId = p1.PropertyReferenceId AND p13.LanguageID = "); selectStatement.Append(LanguageId); } else { selectStatement.Append(@" FROM [CoreData_Test].[dbo].[Properties] AS p1 JOIN [CoreData_Test].[dbo].[PropertyDetails] AS p3 ON p3.PropertyReferenceId = p1.PropertyReferenceId LEFT JOIN [CoreData_Test].[dbo].[PropertyDescription] AS p4 ON p4.PropertyReferenceId = p1.PropertyReferenceId AND p4.LanguageId = "); selectStatement.Append(LanguageId); selectStatement.Append(" LEFT JOIN [CoreData_Test].[dbo].[ResortAirport] AS p5 ON p5.ResortId = p1.ResortId LEFT JOIN [CoreData_Test].[dbo].[Airports] AS p6 ON p6.AirportId = p5.AirportId LEFT JOIN [CoreData_Test].[dbo].[PropertyHighQualityImageLists] AS p7 ON p7.PropertyReferenceId = p1.PropertyReferenceId LEFT JOIN [CoreData_Test].[dbo].[PropertyFacilitiesList] AS p9 ON p9.PropertyReferenceId = p1.PropertyReferenceId AND p9.LanguageId = "); selectStatement.Append(LanguageId); selectStatement.Append(" LEFT JOIN [CoreData_Test].[dbo].[TTIProperty] AS p11 ON p11.ID = p1.PropertyReferenceId"); if (LanguageId != "1") { selectStatement.Append(" JOIN [IVDB].[LCH].[dbo].[TranslationLookup] as p10 ON p10.SourceID = p1.CountryId AND p10.TranslationID = 5 AND p10.LanguageId = "); selectStatement.Append(LanguageId); } selectStatement.Append(" LEFT JOIN [CoreData_Test].[dbo].[PropertyUrls] AS p13 ON p13.PropertyReferenceId = p1.PropertyReferenceId AND p13.LanguageID = "); selectStatement.Append(LanguageId); } selectStatement.Append(CreateStockClause()); selectStatement.Append(CreateWhereClause(selectStatement)); return(selectStatement.ToString()); }
private void CreateCsvFile(FileInfo tempDirectory, string filename, FileInfo fileInfo, StringBuilder headers, FileInfo serverPathAndFile, FileInfo serverPath, string languageEncoding) { int stepId = PropertyExporterLogger.AddStep(); try { //CoreDataLibrary.Data.Process.ExportItemToXml(ExportItem, serverPathAndFile); //CsvToXmlParser xmlParser = new CsvToXmlParser(serverPathAndFile); string line; if (File.Exists(tempDirectory + filename + TEMPFILE_APPENDING)) { File.Delete(tempDirectory + filename + TEMPFILE_APPENDING); } StreamWriter writer = null; StreamReader reader = null; FileStream fs = new FileStream(tempDirectory + fileInfo.Name, FileMode.Open); FileStream outStream = new FileStream(tempDirectory + filename + TEMPFILE_APPENDING, FileMode.Append); reader = new StreamReader(fs, Encoding.Default); string encoding = Get.GetLanguageEncoding(languageEncoding); if (String.IsNullOrEmpty(encoding)) { writer = new StreamWriter(outStream, Encoding.GetEncoding("windows-1252")); } else { writer = new StreamWriter(outStream, Encoding.GetEncoding(encoding)); } writer.WriteLine(headers); string[] stringSeperators = { "(!!!!!!!!!!)" }; string[] fieldSeperators = { "!|!" }; string lastEntry = ""; while ((line = reader.ReadLine()) != null) { string[] lines = line.Split(stringSeperators, StringSplitOptions.RemoveEmptyEntries); if (lines.Length > 0) { for (int i = 0; i <= lines.Length - 1; i++) { if (lines[i].Split(fieldSeperators, StringSplitOptions.None).Length - 1 == headers.ToString().Split('|').Length - 1) { string row = lines[i].Replace("!|!", "|"); writer.WriteLine(row); } else { lastEntry = lastEntry + lines[i]; if (lastEntry.Split(fieldSeperators, StringSplitOptions.None).Length - 1 == headers.ToString().Split('|').Length - 1) { lastEntry = lastEntry.Replace("!|!", "|"); writer.WriteLine(lastEntry); lastEntry = ""; } } } } } writer.Flush(); reader.Close(); writer.Close(); string pathAndFileToFtp = ""; File.Delete(tempDirectory + fileInfo.Name); if (File.Exists(serverPathAndFile.FullName)) { File.Delete(serverPathAndFile.FullName); } File.Move(tempDirectory + filename + TEMPFILE_APPENDING, serverPathAndFile.FullName); pathAndFileToFtp = ExportItem.ExportItemName + ".csv"; File.Delete(tempDirectory + filename + TEMPFILE_APPENDING); if (CoreDataLib.IsLive()) { if (ExportItem.ExportItemFtpId > 0) { FtpExportedFile(pathAndFileToFtp); } } PropertyExporterLogger.EndStep(stepId); } catch (Exception exception) { PropertyExporterLogger.EndStep(stepId, exception); } }
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); } }