public static List <string> GetCatchUpFiles()
{
List <String> fileList = new List <String>();
try
{
string catchUpPath = ServerPath + @"CatchUp/";
FtpWebRequest ftp = (FtpWebRequest)WebRequest.Create(catchUpPath);
ftp.Credentials = new NetworkCredential(FtpUsername, FtpPassword);
ftp.KeepAlive = false;
ftp.Method = WebRequestMethods.Ftp.ListDirectory;
FtpWebResponse response = (FtpWebResponse)ftp.GetResponse();
Stream stream = response.GetResponseStream();
if (stream != null)
{
StreamReader responseStream = new StreamReader(stream);
string responseLine = responseStream.ReadLine();
while (responseLine != null)
{
fileList.Add(responseLine);
responseLine = responseStream.ReadLine();
}
responseStream.Close();
}
}
catch (Exception e)
{
Emailer.SendEmail("*****@*****.**", "Error reading in Catchup files", e.Message);
}
return(fileList);
}
public static string DownloadTripBookingReferencesFileFromFtp(string countryCode)
{
try
{
string filename = GetTripBookingReferenceFileName(DateTime.Now, countryCode);
if (!String.IsNullOrEmpty(filename))
{
DownloadFtpFile(filename, false);
}
else
{
Emailer.SendEmail("*****@*****.**", countryCode + " TRIP Booking Reference File Name Came back blank", "");
}
return(filename);
}
catch (Exception ex)
{
Emailer.SendEmail("*****@*****.**", "TRIP Booking Reference File FTP Import Failed" + DateTime.Now.ToString(), ex.InnerException.ToString());
return("");
}
}
public void CsvExport()
{
try
{
FileInfo fileInfo = new FileInfo(m_pathAndFileName);
FileInfo serverFileAndPath = new FileInfo(@"\\mssqldev\E$\SteveJ\" + fileInfo.Name);
FileInfo tempFile = new FileInfo(@"\\mssqldev\E$\SteveJ\Temp.csv");
StringBuilder headers = new StringBuilder();
foreach (string header in m_selectStatementBuilder.GetOutputHeaders)
{
headers.Append(header + "|");
}
headers.Remove(headers.Length - 1, 1);
using (SqlConnection conn = new SqlConnection(DataConnection.SqlConnCoreData))
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandTimeout = 60 * 30;
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(m_pathAndFileName);
sqlBuilder.Append(" -t \"" + m_del + "\" -r(!!!!!!!!!!)");
sqlBuilder.Append(" -c -S MSSQLDEV -d CoreData -U CoreDataMaintain -P c0reMa1nta1n'; 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(serverFileAndPath.FullName))
{
string line;
if (File.Exists(tempFile.FullName))
{
File.Delete(tempFile.FullName);
}
FileStream fs = new FileStream(serverFileAndPath.FullName, FileMode.Open);
FileStream outStream = new FileStream(tempFile.FullName, FileMode.Append);
StreamReader reader = new StreamReader(fs);
StreamWriter writer = new StreamWriter(outStream);
writer.WriteLine(headers);
string[] stringSeperators = new string[] { "(!!!!!!!!!!)" };
string[] fieldSeperators = new string[] { "!|!" };
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();
File.Delete(serverFileAndPath.FullName);
File.Move(tempFile.FullName, serverFileAndPath.FullName);
File.Delete(tempFile.FullName);
}
}
}
catch (Exception exception)
{
Emailer.SendEmail("*****@*****.**", "CVS Data Exporter - Error : ", "CVSExport", exception);
}
}