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