public SFTPMUFGBondFiles(ILogger <Worker> _logger)
        {
            this._logger = _logger;

            try
            {
                DataSet ds = DBRepository.FillUpDataSetFromSp(EODModel.ConnectionString, "[ExportData].[getMUFGCircleAdaptorTrades]", null);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    this._logger.LogInformation("DataSet is not Blank for MUFG Trade File");
                    CheckForSpecialCharacter(ds);
                    GetSFTPDetails();
                    ExportMUFGCircleFiles(ds);
                }
                else
                {
                    this._logger.LogInformation("DataSet is Blank for MUFG Trade Files and we don't have to send them the file for now. So suppressing the File Generation");
                }
            }
            catch (Exception ex)
            {
                EmailSetup.SendEmail(MUFGCircleEmailDetails.Name, MUFGCircleEmailDetails.From, MUFGCircleEmailDetails.FromPassword, MUFGCircleEmailDetails.Host, MUFGCircleEmailDetails.Port,
                                     MUFGCircleEmailDetails.EnableSsl, MUFGCircleEmailDetails.IsBodyHtml, MUFGCircleEmailDetails.To, "AutoAlert: MUFG circle files SFTP", "Exception Occur " + ex.ToString());
                _logger.LogError("Exception Occurs " + ex.ToString());
            }
        }
        public SFTPMUFGFxFiles(ILogger <Worker> _logger)
        {
            this._logger = _logger;

            try
            {
                DataSet ds = new DataSet();
                _logger.LogInformation("Hitting Stored procedure: [ExportData].[getMUFGCircleAdaptorFXTradesFinal]");
                ds = DBRepository.FillUpDataSetFromSp(EODModel.ConnectionString, "[ExportData].[getMUFGCircleAdaptorFXTradesFinal]", null);

                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    this._logger.LogInformation("DataSet is not Blank for MUFG Trade File for FX");
                    GetSFTPDetails();
                    this._logger.LogInformation("Got SFTP details for FX , calling exportfile method.");

                    ExportMUFGCircleFiles(ds);
                }
                else
                {
                    this._logger.LogInformation("DataSet is Blank for MUFG FX Files and we don't have to send them the file for now. So suppressing the File Generation");
                }
            }
            catch (Exception ex)
            {
                EmailSetup.SendEmail(MUFGCircleEmailDetails.Name, MUFGCircleEmailDetails.From, MUFGCircleEmailDetails.FromPassword, MUFGCircleEmailDetails.Host, MUFGCircleEmailDetails.Port,
                                     MUFGCircleEmailDetails.EnableSsl, MUFGCircleEmailDetails.IsBodyHtml, MUFGCircleEmailDetails.To, "AutoAlert: MUFG circle files SFTP", "Exception Occur " + ex.ToString());
                _logger.LogError("Exception Occurs " + ex.ToString());
            }
        }
        public void ExportMUFGCircleFiles(DataSet ds)
        {
            string    csv          = String.Empty;
            string    errorstring  = "";
            DataTable dtMUFGCircle = ds.Tables[0];
            int       i            = 0;

            while (i < dtMUFGCircle.Columns.Count)
            {
                csv += dtMUFGCircle.Columns[i].ToString().Replace(" ", "") + ",";
                i++;
            }

            csv += "\r\n";

            for (int row = 0; row < dtMUFGCircle.Rows.Count; row++)
            {
                i = 0;
                while (i < dtMUFGCircle.Columns.Count)
                {
                    csv += dtMUFGCircle.Rows[row][i].ToString() + ",";
                    i++;
                }
                csv += "\r\n";
            }

            if (!Directory.Exists(FilePath))
            {
                Directory.CreateDirectory(FilePath);
            }

            string mufgCircleFileName         = filenames[0] + "_" + DateTime.Now.ToString("yyyyMMdd") + "_" + DateTime.Now.ToString("hhmmss") + ".csv";
            string completeMUFGCircleFileName = FilePath + mufgCircleFileName;

            File.WriteAllText(completeMUFGCircleFileName, csv);
            string filenameposted = mufgCircleFileName;
            var    PostPath       = Path.Combine(FilePath + "\\Post");

            if (!Directory.Exists(PostPath))
            {
                Directory.CreateDirectory(PostPath);
            }
            try
            {
                if (WinSFTP.SFTPHelper(sftpServerIP, sftpUserID, sftpPassword, sftpPort, stringHostKey, stringToPath + filenameposted, completeMUFGCircleFileName, ref errorstring))
                {
                    UpdateMUFGCircleSFTPStatus(ds);
                    if (!System.IO.File.Exists(PostPath + "\\" + filenameposted))
                    {
                        _logger.LogInformation("File Transferred Successfully to Admin");
                        File.Move(completeMUFGCircleFileName, PostPath + "\\" + filenameposted);
                        _logger.LogInformation("MUFG Circle File has been moved to Posted Directory");
                        //EmailSetup.SendEmail(MUFGCircleEmailDetails.Name, MUFGCircleEmailDetails.From, MUFGCircleEmailDetails.FromPassword, MUFGCircleEmailDetails.Host, MUFGCircleEmailDetails.Port, MUFGCircleEmailDetails.EnableSsl, MUFGCircleEmailDetails.IsBodyHtml, MUFGCircleEmailDetails.To, "AutoAlert: MUFG Circle SFTP", "MUFG Circle File Successfully Transferred to Admin");
                    }
                }
            }
            catch (Exception exError)
            {
                EmailSetup.SendEmail(MUFGCircleEmailDetails.Name, MUFGCircleEmailDetails.From, MUFGCircleEmailDetails.FromPassword, MUFGCircleEmailDetails.Host,
                                     MUFGCircleEmailDetails.Port, MUFGCircleEmailDetails.EnableSsl, MUFGCircleEmailDetails.IsBodyHtml, MUFGCircleEmailDetails.To, "AutoAlert: MUFG Circle SFTP", "Exception Occur " + exError);
                _logger.LogError("Exception Occur " + exError.ToString() + "MovedPath : " + PostPath + "\\" + filenameposted);
            }
        }