public static string ExtractPurgeData(out List <Models.FilenameFields> fileNameFields)
{
TierraCreativeContext _context = new TierraCreativeContext();
fileNameFields = new List <Models.FilenameFields>();
var ctr = 1;
string data = string.Empty;
var Main_Account = _context.CSNLookUps.SingleOrDefault(x => x.CSNName == "Main").CSNAccount;
var AIL_Account = _context.CSNLookUps.SingleOrDefault(x => x.CSNName == "AIL").CSNAccount;
var SP_Account = _context.CSNLookUps.SingleOrDefault(x => x.CSNName == "Supplementary Dividend").CSNAccount;
try {
//var drps = _context.DRPs.ToList();
//foreach (var drp in drps) {
// var fileNameField = new Models.FilenameFields
// {
// Sequence = ctr.ToString(),
// Timestamp = drp.CreatedDate.ToString(),
// MainCSN = Main_Account,
// CSN = "",
// ISIN = drp.ISIN,
// InstructionID = "DERI",
// TIN = "",
// TransferDate = System.DateTime.Now.ToString(),
// TransferQty = drp.DRPAmount.ToString()
// };
// fileNameFields.Add(fileNameField);
// ctr += 1;
//}
var ails = _context.AILs.Where(x => x.ReviewedDate != null).ToList();
foreach (var ail in ails)
{
var fileNameField = new Models.FilenameFields
{
Sequence = ctr.ToString(),
Timestamp = ail.CreatedDate.ToString(),
MainCSN = Main_Account,
CSN = AIL_Account,
ISIN = ail.ISIN,
InstructionID = (ail.FromCSN == "Main" ? "DERI" : "RERE"),
TIN = "",
TransferDate = System.DateTime.Now.ToString(),
TransferQty = ail.TransferAmount.ToString()
};
fileNameFields.Add(fileNameField);
ctr += 1;
}
var sps = _context.SupplementaryDividends.Where(x => x.ReviewedDate != null).ToList();
foreach (var sp in sps)
{
var fileNameField = new Models.FilenameFields
{
Sequence = ctr.ToString(),
Timestamp = sp.CreatedDate.ToString(),
MainCSN = Main_Account,
CSN = SP_Account,
ISIN = sp.ISIN,
InstructionID = (sp.FromCSN == "Main" ? "DERI" : "RERE"),
TIN = "",
TransferDate = System.DateTime.Now.ToString(),
TransferQty = sp.TransferAmount.ToString()
};
fileNameFields.Add(fileNameField);
ctr += 1;
}
//concat data
var setting = _context.Settings.SingleOrDefault(x => x.Description == "lastpurgeid");
var lastpurgeid = double.Parse(setting.Value);
foreach (var fileNameField in fileNameFields)
{
//1,2019-12-09-16-35-21,220001091,123456789,NZF01DT123C1,DERI,2019-12-09,10000.50,
lastpurgeid += 1;
data += string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8}{9}",
lastpurgeid,
Convert.ToDateTime(fileNameField.Timestamp).ToString("yyyy-MM-dd-HH-mm-ss"),
fileNameField.MainCSN,
fileNameField.CSN,
fileNameField.ISIN,
fileNameField.InstructionID,
//Convert.ToDateTime(fileNameField.TransferDate).ToString("yyyy-dd-MM"),
Convert.ToDateTime(fileNameField.TransferDate).ToString("yyyy-MM-dd"),
Convert.ToDouble(fileNameField.TransferQty).ToString("##.00"),
fileNameField.TIN,
Environment.NewLine);
}
setting.Value = lastpurgeid.ToString();
_context.Entry(setting).State = System.Data.Entity.EntityState.Modified;
_context.SaveChanges();
}
catch { }
return(data);
}
public bool SendEmail(SendGridModel model)
{
var _context = new TierraCreativeContext();
var settings = _context.Settings;
string emailhost = settings.SingleOrDefault(x => x.Description == "emailhost").Value;
int emailport = int.Parse(settings.SingleOrDefault(x => x.Description == "emailport").Value);
string emailusername = settings.SingleOrDefault(x => x.Description == "emailusername").Value;
string emailpassword = settings.SingleOrDefault(x => x.Description == "emailpassword").Value;
try
{
MailMessage mailMsg = new MailMessage();
// From
mailMsg.From = new MailAddress(model.From.Email, model.From.Name);
// Multiple To
foreach (var recipientTo in model.To)
{
mailMsg.To.Add(new MailAddress(recipientTo.Email, recipientTo.Name));
}
// Multiple Cc
foreach (var recipientCc in model.Cc)
{
mailMsg.CC.Add(new MailAddress(recipientCc.Email, recipientCc.Name));
}
// Multiple Bcc
foreach (var recipientBcc in model.Bcc)
{
mailMsg.Bcc.Add(new MailAddress(recipientBcc.Email, recipientBcc.Name));
}
// Multiple Attachment
foreach (var attachment in model.Attachment)
{
mailMsg.Attachments.Add(attachment);
}
//Subject
mailMsg.Subject = model.Subject;
//Body
if (model.IsBodyHtml)
{
mailMsg.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(model.Body, null, MediaTypeNames.Text.Html));
}
else
{
mailMsg.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(model.Body, null, MediaTypeNames.Text.Plain));
}
//SmtpClient smtpClient = new SmtpClient(ConfigurationManager.AppSettings["emailhost"], Convert.ToInt32(ConfigurationManager.AppSettings["emailport"]));
//System.Net.NetworkCredential credentials = new System.Net.NetworkCredential(ConfigurationManager.AppSettings["emailusername"], ConfigurationManager.AppSettings["emailpassword"]);
SmtpClient smtpClient = new SmtpClient(emailhost, emailport);
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential(emailusername, emailpassword);
smtpClient.Credentials = credentials;
smtpClient.Send(mailMsg);
}
catch (Exception e)
{
//Utilities.WriteEmailErrLog(model.Subject, model.Body + " | " + e.InnerException + " | " + e.Message);
return(false);
}
return(true);
}
public static bool PurgeData()
{
TierraCreativeContext _context = new TierraCreativeContext();
var sCmd = @"INSERT INTO Purge_AIL(
[AILId],
[UserId] ,
[FromCSN],
[ToCSN],
[ISIN] ,
[TransferAmount] ,
[CreatedById],
[CreatedDate] ,
[UpdatedById] ,
[UpdatedDate] ,
[ReviewedById] ,
[ReviewedDate] ,
[DeletedById] ,
[DeletedDate]
)
SELECT * FROM AIL;
INSERT INTO Purge_DRP(
[DRPId],
[UserId],
[CSN],
[ISIN],
[DRPAmount],
[CreatedById],
[CreatedDate] ,
[UpdatedById] ,
[UpdatedDate],
[ReviewedById],
[ReviewedDate],
[DeletedById] ,
[DeletedDate]
)
SELECT * FROM DRP;
INSERT INTO Purge_SupplementaryDividend(
[SDId],
[UserId],
[FromCSN] ,
[ToCSN] ,
[ISIN],
[TransferAmount],
[CreatedById] ,
[CreatedDate],
[UpdatedById] ,
[UpdatedDate] ,
[ReviewedById] ,
[ReviewedDate],
[DeletedById] ,
[DeletedDate]
)
SELECT * FROM SupplementaryDividend;
DELETE FROM DRP;
DELETE FROM SupplementaryDividend;
DELETE FROM AIL;
";
try {
_context.Database.ExecuteSqlCommand(sCmd);
_context.SaveChanges();
return(true);
}
catch (Exception ex) { }
return(false);
}
