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