private List <ArrearsAgreement> InsertRandomAgreementDetails(string tenancyRef, int num)
{
var random = new Faker();
List <ArrearsAgreement> items = new List <ArrearsAgreement>();
string commandText =
"INSERT INTO arag (tag_ref, arag_status, arag_startdate, arag_startbal, arag_breached, arag_clearby) " +
"VALUES (@tenancyRef, @agreementStatus, @startDate, @startBal, @breached, @clearBy)" +
"INSERT INTO aragdet (arag_sid, aragdet_amount, aragdet_frequency) VALUES (@aragSid, @amount, @frequency)";
foreach (int i in Enumerable.Range(0, num))
{
ArrearsAgreement arrearsAgreement = new ArrearsAgreement
{
Amount = random.Finance.Amount(),
Breached = true,
ClearBy = new DateTime(random.Random.Int(1900, 1999), random.Random.Int(1, 12), random.Random.Int(1, 28), 9, 30, 0),
Frequency = $"FR{i}",
StartBalance = random.Finance.Amount(),
Startdate = new DateTime(random.Random.Int(1900, 1999), random.Random.Int(1, 12), random.Random.Int(1, 28), 9, 30, 0),
Status = $"AB{i}",
TenancyRef = tenancyRef
};
SqlCommand command = new SqlCommand(commandText, db);
command.Parameters.Add("@tenancyRef", SqlDbType.Char);
command.Parameters["@tenancyRef"].Value = arrearsAgreement.TenancyRef;
command.Parameters.Add("@agreementStatus", SqlDbType.Char);
command.Parameters["@agreementStatus"].Value = arrearsAgreement.Status;
command.Parameters.Add("@startDate", SqlDbType.SmallDateTime);
command.Parameters["@startDate"].Value = arrearsAgreement.Startdate;
command.Parameters.Add("@amount", SqlDbType.Decimal);
command.Parameters["@amount"].Value = arrearsAgreement.Amount;
command.Parameters.Add("@startBal", SqlDbType.Decimal);
command.Parameters["@startBal"].Value = arrearsAgreement.StartBalance;
command.Parameters.Add("@frequency", SqlDbType.Char);
command.Parameters["@frequency"].Value = arrearsAgreement.Frequency;
command.Parameters.Add("@breached", SqlDbType.Bit);
command.Parameters["@breached"].Value = 1;
command.Parameters.Add("@clearBy", SqlDbType.SmallDateTime);
command.Parameters["@clearBy"].Value = arrearsAgreement.ClearBy;
command.Parameters.Add("@aragSid", SqlDbType.Int);
command.Parameters["@aragSid"].Value = new Random().Next();
items.Add(arrearsAgreement);
command.ExecuteNonQuery();
}
return(items.OrderByDescending(i => i.Startdate).ToList());
}
public static void InsertAgreement(ArrearsAgreement arrearsAgreement, SqlConnection db)
{
var commandText = "INSERT INTO arag (tag_ref, arag_status, arag_startdate, arag_sid) VALUES (@tenancyRef, @agreementStatus, @startDate, @aragSid)";
var command = new SqlCommand(commandText, db);
command.Parameters.Add("@tenancyRef", SqlDbType.Char);
command.Parameters["@tenancyRef"].Value = arrearsAgreement.tag_ref;
command.Parameters.Add("@agreementStatus", SqlDbType.Char);
command.Parameters["@agreementStatus"].Value = arrearsAgreement.arag_status;
command.Parameters.Add("@startDate", SqlDbType.SmallDateTime);
command.Parameters["@startDate"].Value = arrearsAgreement.arag_startdate;
command.Parameters.Add("@aragSid", SqlDbType.Int);
command.Parameters["@aragSid"].Value = arrearsAgreement.arag_sid;
command.ExecuteNonQuery();
}