public void GetSFTPDetails()
{
var Param1 = new SqlParameter("@AdminName", SqlDbType.NVarChar)
{
Value = "MUFGCircle"
};
var Param2 = new SqlParameter("@FileType", SqlDbType.NVarChar)
{
Value = "All"
};
var prms = new List <SqlParameter> {
Param1, Param2
};
dtSFTPCred = DBRepository.FillUpDataSetFromSp(EODModel.ConnectionString, "[ExportData].[ExtractSFTPDetails]", prms).Tables[0];
_logger.LogInformation("Get SFTP Credenitals Successfully for MUFGCircle");
if (dtSFTPCred != null)
{
foreach (var details in dtSFTPCred.Select("FileType='MUFGCircleAll'"))
{
sftpServerIP = details["IPAddress"].ToString();
sftpUserID = details["UserName"].ToString();
sftpPassword = details["Password"].ToString();
Int32.TryParse(details["Port"].ToString(), out sftpPort);
stringToPath = details["DestinationPath"].ToString();
stringHostKey = details["HostKey"].ToString();
FileName = details["FileName"].ToString();
FilePath = details["FilePath"].ToString();
}
filenames = FileName.Split(";");
}
}
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 void GetSFTPDetailsforverification()
{
var Param1 = new SqlParameter("@AdminName", SqlDbType.NVarChar)
{
Value = "MUFGCircle"
};
var Param2 = new SqlParameter("@FileType", SqlDbType.NVarChar)
{
Value = "All"
};
var prms = new List <SqlParameter> {
Param1, Param2
};
sftpdata = DBRepository.FillUpDataSetFromSp(EODModel.ConnectionString, "[ExportData].[ExtractSFTPDetails]", prms).Tables[0];
if (sftpdata != null)
{
_logger.LogInformation("Get SFTP Credenitals Successfully for MUFGCircle");
foreach (DataRow details in sftpdata.Select("FileType='MUFGCircleAll'"))
{
sftpServerIP = "sftp.mfsadmin.com";
sftpUserID = details["UserName"].ToString();
sftpPassword = details["Password"].ToString();
Int32.TryParse(details["Port"].ToString(), out sftpPort);
stringHostKey = details["HostKey"].ToString();
DestinationFilePath = FileDetails.DestinationFilePath;
SourcefilePath = "/confirmresponses/";
}
filenames = FileName.Split(";");
}
}
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 UpdateTradesForTradeids(string fileName)
{
_logger.LogInformation("UpdateTradesForTradeids method called");
string tradeIds = "";
_logger.LogInformation("converting received file to datatable");
try
{
_logger.LogInformation("File name to convert into datatable is " + fileName);
DataTable reportFile = ExcelToDataSet.CreateDataTable(fileName).Tables[0];
_logger.LogInformation("converted successfully");
_logger.LogInformation("no of rows in a file are : " + reportFile.Rows.Count);
foreach (DataRow row in reportFile.Rows)
{
_logger.LogInformation("Entered into foreach block and Checking for matched as confirmation status for every row in a file.");
string confirmationstatus = row.Field <string>("ConfirmationStatus");
_logger.LogInformation("Checking for matched as confirmation status , confirmation status of row in a file is : " + confirmationstatus);
if (confirmationstatus.Equals("Matched") || confirmationstatus.Equals("Confirmed"))
{
_logger.LogInformation("Confirmation status of row in a file matched , fetching its id");
string id = row.Field <string>("ClientTradeId");
_logger.LogInformation("And id is >>" + id);
id = id.Substring(0, 6);
tradeIds += id + ",";
_logger.LogInformation("Ids in tradeid are >> " + tradeIds);
}
}
tradeIds = tradeIds.TrimEnd(',');
_logger.LogInformation("Matched Ids in file: " + fileName + "tradeids are >> " + tradeIds);
}
catch (Exception e)
{
_logger.LogError("found an exception while converting into datatable" + e);
}
try
{
try
{
_logger.LogInformation("Calling database update function for trade.trades and trade.additionaltradedatareport.");
string date = DateTime.Now.ToString();
DBRepository.UpdateVerificationStatus(EODModel.ConnectionString, tradeIds, date);
_logger.LogInformation("Database updated successfully");
}
catch (Exception e)
{
_logger.LogError("Got exception while updating database for respective tradeids " + e);
}
}
catch (Exception e)
{
_logger.LogError("Got exception while checking rows in a loop " + e);
}
}
public Counterpartyfile(ILogger <Worker> _logger)
{
this._logger = _logger;
DataSet ds = DBRepository.FillUpDataSetFromSp(EODModel.ConnectionString, "[ExportData].[counterpartylist]", 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);
string csv = String.Empty;
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";
}
string mufgCircleFileName = filenames[0] + "_" + "Bond_" + DateTime.Now.ToString("yyyyMMdd") + "_" + DateTime.Now.ToString("hhmmss") + ".csv";
File.WriteAllText(mufgCircleFileName, csv);
File.Move(mufgCircleFileName, "Z:" + "\\");
}
}
public void UpdateMUFGCircleSFTPStatus(DataSet ds)
{
string fld = "Fx";
int[] exportTradeIds = new int[ds.Tables[0].Rows.Count];
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Int32.TryParse((ds.Tables[0].Rows[i]["ClientTradeId"].ToString()), out int tradeid);
exportTradeIds[i] = tradeid;
}
try
{
_logger.LogInformation("MUFG Circle Tradeids Update Started");
DBRepository.UpdateTradeWithSFTPStatus(EODModel.ConnectionString, exportTradeIds, "MUFGCircle");
DBRepository.UpdateSFTPDateTimeStamp(EODModel.ConnectionString, "MUFGCircle", fld, 1 + "");
_logger.LogInformation("MUFG Circle Tradeid's SFTPtime updated :");
}
catch (Exception e)
{
_logger.LogInformation("There are some issues :");
}
}
