private string FetchFileAndLoadData(FtpDetail detail, RetrieveItem item)
{
string ret = "";
try
{
if (item.FileName.ToLower() == "getfilelist")
{
FetchFileList(detail, item);
}
else
{
// Retrieve a file
string LocalFile;
if (item.ParseOnly)
{
LocalFile = FindFileLocation(detail, item) + item.FileName;
}
else
{
LocalFile = FetchRemoteFile(detail, item);
}
DataTable data = ParseFile(detail, item, LocalFile);
Database.SaveFileData(item.ID, data, detail.TargetTable);
}
}
catch (Exception ex)
{
ret = ex.Message;
}
return(ret);
}
public RetrieveItem(FtpDetail detailItem, int id, string filename, DateTime fileDate, DateTime timeToRetrieve)
{
Parent = detailItem;
ID = id;
FileName = filename;
FileDate = fileDate;
RetrieveTime = timeToRetrieve;
}
private string FindFileLocation(FtpDetail detail, RetrieveItem item)
{
string ret = Settings.Default.FileStorageRoot;
Funcs.EndSlash(ref ret, true);
ret += detail.Name + "\\";
ret += item.FileDate.ToString("yyyy\\\\MM\\\\");
return(ret);
}
internal static string FetchRetrieveItems(Dictionary <int, FtpDetail> FtpDetails)
{
string ret = "";
string sql = "SELECT * FROM Retrieve WHERE State = 'New' AND TimeToRetrieve < GetDate() ORDER BY TimeToRetrieve";
foreach (FtpDetail d in FtpDetails.Values)
{
// Create the collection again so that we discard any unwanted, processed or old items
d.RetrieveItems = new Dictionary <int, RetrieveItem>();
}
#if DEBUG
SQLServer db = new SQLServer(Settings.Default.Test_FtpDSN);
#else
SQLServer db = new SQLServer(Settings.Default.FtpDSN);
#endif
try
{
SqlDataReader dr = db.FetchDataReader(sql);
while (dr.Read())
{
int detailID = (int)dr["DetailID"];
if (FtpDetails.ContainsKey(detailID))
{
FtpDetail parent = FtpDetails[detailID];
int retrieveID = (int)dr["ID"];
string fileName = dr["Filename"].ToString();
DateTime fileDate = (DateTime)dr["FileDate"];
DateTime retrieveTime = (DateTime)dr["TimeToRetrieve"];
int attempt = int.Parse(dr["Attempt"].ToString());
bool parseOnly = (bool)dr["ParseOnly"];
RetrieveItem item = new RetrieveItem(parent, retrieveID, fileName, fileDate, retrieveTime);
item.ParseOnly = parseOnly;
item.Attempt = attempt;
parent.RetrieveItems.Add(retrieveID, item);
}
else
{
Funcs.ReportProblem(string.Format("FTP Detail ID [{0}] not found in collection.", detailID));
}
}
dr.Close();
}
finally
{
db.Close();
}
return(ret);
}
private static void CreateViewForFtpData(FtpDetail detail)
{
string[] columns = detail.TargetColumnNames.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
List <string> columnTypes = detail.TargetColumnTypes.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries).ToList <string>();
if (columns.Length != columnTypes.Count)
{
throw new Exception("Target column names and target column types must have the same count.");
}
string sql = string.Format("IF exists(select * from sys.objects where name = '{0}' and type = 'v') DROP VIEW {0};\r\n",
detail.ViewName);
ExecSql(sql, string.Format("Failed to drop existing view for {0} ftp item.", detail.Name));
sql = string.Format("CREATE VIEW [{0}]\r\nAS\r\n\r\n", detail.ViewName);
sql += "SELECT d.ID, r.ID RetrieveID, r.FileDate, dt.Row, ";
int index = 0;
foreach (string column in columns)
{
string type = columnTypes[index];
if (type.Length > 6 && type.ToLower().Substring(0, 7) == "varchar" ||
type.Length > 7 && type.ToLower().Substring(0, 8) == "datetime")
{
// Leave as string
// As we are uncertain of the format that the dates may come in as we leave the dates as strings
sql += string.Format("c{0} as '{1}'", index + 1, column);
}
else
{
// convert to new type
sql += string.Format("convert({0},c{1}) as '{2}'", columnTypes[index], index + 1, column);
}
index++;
sql += ",\r\n";
}
sql = sql.Remove(sql.Length - 3); // take off trailing comma and newline
sql += string.Format("\r\nFROM Detail d \r\n"
+ "JOIN Retrieve r ON r.DetailID = d.ID \r\n"
+ "JOIN {0} dt ON dt.RetrieveID = r.ID \r\n"
+ "WHERE d.Name = '{1}'", detail.TargetTable, detail.Name);
ExecSql(sql, string.Format("Failed to create view for {0} ftp item.", detail.Name));
sql = string.Format("UPDATE Detail SET ViewCreated = '{0}' WHERE ID = {1}", DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), detail.ID);
ExecSql(sql, string.Format("Failed to update Detail table with view creation time for {0} ftp item.", detail.Name));
}
private string FetchRemoteFile(FtpDetail detail, RetrieveItem item)
{
string uri = "", userName = "", pw = "", folder = "", ftpStore = "", domain = "";
ftpStore = FindFileLocation(detail, item);
string ext = Path.GetExtension(item.FileName);
folder = detail.Folder;
// Temp area to store files before we move them
string tempFolder = Path.GetTempPath();
switch (detail.FptType)
{
case FtpDetail.eFtpType.ftp:
if (!folder.EndsWith("/"))
{
folder += "/";
}
Database.FetchLoginInfo(detail.FtpLookupValue, out uri, out userName, out pw);
uri = "ftp://" + uri + "/" + folder + item.FileName;
Maple.FTP.FTP.Download(uri, userName, pw, tempFolder + item.FileName, true);
break;
case FtpDetail.eFtpType.sftp:
if (!folder.EndsWith("/"))
{
folder += "/";
}
int portNumberOverride;
Database.FetchLoginInfo(detail.FtpLookupValue, out uri, out userName, out pw, out portNumberOverride);
Maple.FTP.SFTP ftp = new Maple.FTP.SFTP();
string sessionOutput = string.Empty;
List <string> files = new List <string>();
files.Add(item.FileName);
if (portNumberOverride == -1)
{
ftp.SFtpGet(files, uri, detail.Folder, userName, pw, out sessionOutput);
}
else
{
ftp.SFtpGet(files, uri, detail.Folder, userName, pw, out sessionOutput, portNumberOverride);
}
File.Move(Path.GetDirectoryName(Application.ExecutablePath) + "\\" + files[0], tempFolder + files[0]);
break;
case FtpDetail.eFtpType.UnixShare:
if (!folder.EndsWith("\\"))
{
folder += "\\";
}
File.Delete(tempFolder + item.FileName);
File.Copy(folder + item.FileName, tempFolder + item.FileName);
break;
case FtpDetail.eFtpType.Https:
if (!folder.EndsWith("/"))
{
folder += "/";
}
Database.FetchLoginInfo(detail.FtpLookupValue, out uri, out userName, out pw);
uri = uri + "/" + item.FileName;
domain = Path.GetDirectoryName(userName);
userName = Path.GetFileName(userName);
WebClient webClient = new WebClient();
webClient.Credentials = new NetworkCredential(userName, pw, domain);
//create temp folder first
Directory.CreateDirectory(tempFolder + Path.GetDirectoryName(item.FileName));
webClient.DownloadFile(uri, tempFolder + item.FileName);
break;
}
string toReturn = "";
// Check that file is there
if (File.Exists(tempFolder + item.FileName))
{
string newFileName = item.FileName;
string lastdir = "";
int number;
if (detail.PGP)
{
item.FileName = Decryption.PGP(tempFolder, item.FileName);
}
if (detail.ZIP)
{
lastdir = Path.GetDirectoryName(item.FileName);
if (int.TryParse(lastdir, out number))
{
//directory includes date
Directory.CreateDirectory(ftpStore + lastdir);
}
newFileName = Decryption.ZIP(tempFolder, item.FileName, domain);
ext = Path.GetExtension(newFileName);
}
if (detail.DES)
{
item.FileName = Decryption.DES(tempFolder, item.FileName);
}
// Move to permanent location
Directory.CreateDirectory(ftpStore);
//int count = 1;
newFileName = Path.GetFileNameWithoutExtension(newFileName);
string add = "";
// This part finds a new filename if it already exists
//while (File.Exists(ftpStore + newFileName + add + ext)) {
// add = string.Format(" ({0})", count++);
//}
if (lastdir != "")
{
string newFileLocation = lastdir + "/" + newFileName + ext;
File.Copy(tempFolder + newFileLocation, ftpStore + newFileLocation, true);
toReturn = ftpStore + newFileLocation;
File.Delete(tempFolder + newFileLocation);
}
else
{
File.Copy(tempFolder + item.FileName, ftpStore + newFileName + add + ext, true);
toReturn = ftpStore + newFileName + add + ext;
File.Delete(tempFolder + item.FileName);
}
}
return(toReturn);
}
private DataTable ParseFile(FtpDetail detail, RetrieveItem item, string LocalFile)
{
string[] columns = detail.ColumnsRequired.ToLower().Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
List <int> columnIndexes = new List <int>();
string data = File.ReadAllText(LocalFile);
string[] lines = data.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
bool avoidHeaderRow = false;
string delim = detail.Delimiter;
char[] delims = delim.ToCharArray();
if (detail.ColumnIdentifierType == FtpDetail.eColumnIdentifierType.Name)
{
avoidHeaderRow = true;
// Find out what data is in what column
string[] headers = lines[0].Split(delims, StringSplitOptions.RemoveEmptyEntries);
Dictionary <string, int> headIds = new Dictionary <string, int>();
int index = 0;
foreach (string header in headers)
{
headIds.Add(header.ToLower(), index++);
}
// Check that the expected headers are present
foreach (string column in columns)
{
if (!headIds.ContainsKey(column))
{
throw new Exception(string.Format("Expected header was not found in the incoming file [{0}].", column));
}
else
{
// Replace column names with their index
columnIndexes.Add(headIds[column]);
}
}
}
else
{ // column indexes supplied
foreach (string column in columns)
{
columnIndexes.Add(int.Parse(column));
}
}
DataTable outPut = new DataTable();
// Now we have the column indexes, work through the data
int rowNo = 1;
foreach (string line in lines)
{
if (avoidHeaderRow)
{
avoidHeaderRow = false;
}
else
{
if (line.Trim() != "")
{
//string[] fields = line.Split(delims, StringSplitOptions.RemoveEmptyEntries);
string[] fields = line.Split(delims);
// Read as many columns as are available
DataRow row = new DataRow(rowNo);
int colNo = 0;
foreach (int col in columnIndexes)
{
if (col >= fields.Length)
{
// not enough columns
break;
}
colNo++;
row.AddData(colNo, fields[col]);
}
// Only add rows with columns
if (row.ColumnCount > 0)
{
outPut.AddRow(row);
rowNo++;
}
}
}
}
return(outPut);
}
private void FetchFileList(FtpDetail detail, RetrieveItem item)
{
string site, userName, pw;
Dictionary <string, Maple.FTP.FileDetail> files = new Dictionary <string, Maple.FTP.FileDetail>();;
switch (detail.FptType)
{
case FtpDetail.eFtpType.ftp:
Database.FetchLoginInfo(detail.FtpLookupValue, out site, out userName, out pw);
files = Maple.FTP.FTP.GetFtpFileList(site, detail.Folder, userName, pw);
break;
case FtpDetail.eFtpType.sftp:
int portNumberOverride;
Database.FetchLoginInfo(detail.FtpLookupValue, out site, out userName, out pw, out portNumberOverride);
Maple.FTP.SFTP sftp = new Maple.FTP.SFTP();
if (portNumberOverride == -1)
{
files = sftp.GetSFtpFileList(site, detail.Folder, userName, pw);
}
else
{
files = sftp.GetSFtpFileList(site, detail.Folder, userName, pw, portNumberOverride);
}
break;
case FtpDetail.eFtpType.UnixShare:
string[] found = Directory.GetFiles(detail.Folder);
foreach (string file in found)
{
Maple.FTP.FileDetail f = new Maple.FTP.FileDetail();
FileInfo fi = new FileInfo(file);
f.Name = fi.Name;
f.Size = (int)fi.Length;
f.Timestamp = fi.LastWriteTime;
files.Add(f.Name.ToLower(), f);
}
break;
case FtpDetail.eFtpType.Https:
Database.FetchLoginInfo(detail.FtpLookupValue, out site, out userName, out pw);
string domain = Path.GetDirectoryName(userName);
userName = Path.GetFileName(userName);
WebClient webClient = new WebClient();
webClient.Credentials = new NetworkCredential(userName, pw, domain);
//create temp folder first
Directory.CreateDirectory(detail.Folder + Path.GetDirectoryName(item.FileName));
webClient.DownloadFile(site, detail.Folder + item.FileName);
//tempFolder + item.FileName
//File.Copy(folder + item.FileName, tempFolder + item.FileName);
//files = sftp.GetSFtpFileList(site, detail.Folder, userName, pw);
break;
}
Database.SaveFileList(item.ID, files);
}
internal static string FetchFtpDetail(ref Dictionary <int, FtpDetail> details)
{
string ret = "";
#if DEBUG
SQLServer db = new SQLServer(Settings.Default.Test_FtpDSN);
#else
SQLServer db = new SQLServer(Settings.Default.FtpDSN);
#endif
string sql = "SELECT * FROM Detail";
try
{
SqlDataReader dr = db.FetchDataReader(sql);
while (dr.Read())
{
int id = (int)dr["ID"];
string name = dr["Name"].ToString();
FtpDetail detail;
bool add = false;
if (details.ContainsKey(id))
{
detail = details[id];
}
else
{
detail = new FtpDetail(id, name);
add = true;
}
if (dr["ColIdentType"].ToString().ToLower() == "index")
{
detail.ColumnIdentifierType = FtpDetail.eColumnIdentifierType.Index;
}
else if (dr["ColIdentType"].ToString().ToLower() == "name")
{
detail.ColumnIdentifierType = FtpDetail.eColumnIdentifierType.Name;
}
detail.ColumnsRequired = dr["ColumnsRequired"].ToString();
detail.Delimiter = dr["Delimiter"].ToString();
detail.Folder = dr["Folder"].ToString();
string shareType = dr["TransferType"].ToString().ToLower();
switch (shareType)
{
case "ftp":
detail.FptType = FtpDetail.eFtpType.ftp;
break;
case "sftp":
detail.FptType = FtpDetail.eFtpType.sftp;
break;
case "unixshare":
detail.FptType = FtpDetail.eFtpType.UnixShare;
break;
case "https":
detail.FptType = FtpDetail.eFtpType.Https;
break;
default:
throw new Exception(string.Format("Bad share type read from database [{0}]", shareType));
}
detail.FtpLookupValue = dr["FtpLookupName"].ToString();
detail.TargetColumnNames = dr["TargetColumnNames"].ToString();
detail.TargetColumnTypes = dr["TargetColumnTypes"].ToString();
detail.TargetTable = dr["TargetTable"].ToString();
detail.ViewName = dr["ViewName"].ToString().Trim();
detail.PGP = dr["pgp"].ToString() == "True";
detail.ZIP = dr["zip"].ToString() == "True";
detail.DES = dr["des"].ToString() == "True";
object viewCreated = dr["ViewCreated"];
if (detail.ViewName != "" && viewCreated.ToString() == "")
{
CreateViewForFtpData(detail);
}
if (add)
{
details.Add(detail.ID, detail);
}
}
dr.Close();
}
finally
{
db.Close();
}
return(ret);
}
