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