public void SetEmailThanksRecurring(int BillID, string TipeEmail, ref systemEmailQueueModel emailSent)
{
EmailThanksRecurringVM EmailThanks;
EmailThanks = (from b in _context.BillingModel
join pb in _context.PolicyBillingModel on b.policy_id equals pb.policy_Id
join ci in _context.CustomerInfo on pb.holder_id equals ci.CustomerId
join pd in _context.Product on pb.product_id equals pd.product_id
where b.BillingID == BillID
select new EmailThanksRecurringVM()
{
PolicyNo = pb.policy_no,
CustomerName = ci.CustomerName,
Salam = (ci.IsLaki == true) ? "Bapak" : "Ibu",
CustomerEmail = ci.Email,
ProductName = pd.product_description,
PremiAmount = b.TotalAmount
}).SingleOrDefault();
string SubjectEmail = string.Format(@"JAGADIRI: Penerimaan Premi Regular {0} {1} {2}", EmailThanks.ProductName, EmailThanks.PolicyNo, EmailThanks.CustomerName.ToUpper());
string BodyMessage = string.Format(@"Salam hangat {0} {1},<br>
<p style='text-align:justify'>Bersama surat ini kami ingin mengucapkan terima kasih atas pembayaran Premi Regular untuk Polis {2} dengan nomor polis {3} sejumlah IDR {4} yang telah kami terima. Pembayaran Premi tersebut secara otomatis akan membuat Polis Asuransi Anda tetap aktif dan memberikan manfaat perlindungan maksimal bagi Anda dan keluarga.</p>
<br>Sukses selalu,
<br>JAGADIRI ", EmailThanks.Salam, EmailThanks.CustomerName.ToUpper(), EmailThanks.ProductName, EmailThanks.PolicyNo, EmailThanks.PremiAmount.ToString("#,###"));
try
{
emailSent.email_body = BodyMessage;
emailSent.email_subject = SubjectEmail;
emailSent.email_to = EmailThanks.CustomerEmail;
emailSent.email_bcc = this.EmailPHS;
emailSent.email_type = TipeEmail;
emailSent.email_status = "P";
emailSent.email_created_dt = DateTime.Now;
}
catch (Exception ex)
{
throw new Exception("SetEmailThanksRecurring => (BillID = " + BillID.ToString() + ") " + ex.Message);
}
}
public ActionResult AddPayment(int id, [Bind("PolicyId,BillingID,PaidDate,SourcePayment,PaidAmount,ApprovalCode")] PolicyAddPaymentSave polisVM)
{
Boolean retval = false;
string message = "";
var cmdx = _context.Database;
var cmd = _context.Database.GetDbConnection().CreateCommand();
cmdx.OpenConnection(); cmdx.BeginTransaction(); // jbs
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "add_payment_recurring";
cmd.Parameters.Clear();
cmd.Parameters.Add(new MySqlParameter("@polis_idx", MySqlDbType.Int32)
{
Value = polisVM.PolicyId
});
cmd.Parameters.Add(new MySqlParameter("@paid_datex", MySqlDbType.DateTime)
{
Value = polisVM.PaidDate
});
cmd.Parameters.Add(new MySqlParameter("@source_paymentx", MySqlDbType.VarChar)
{
Value = polisVM.SourcePayment
});
cmd.Parameters.Add(new MySqlParameter("@appr_code", MySqlDbType.VarChar)
{
Value = polisVM.ApprovalCode
});
cmd.Parameters.Add(new MySqlParameter("@user_life21", MySqlDbType.VarChar)
{
Value = "2000"
});
cmd.Parameters.Add(new MySqlParameter("@user_jbs", MySqlDbType.VarChar)
{
Value = User.Identity.Name
});
try
{
int bill;
var billing_id = cmd.ExecuteScalar().ToString();
if (int.TryParse(billing_id, out bill))
{
systemEmailQueueModel emailSent = new systemEmailQueueModel();
SetEmailThanksRecurring(bill, "UploadCCResult", ref emailSent);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
cmd.CommandText = @"INSERT INTO `prod_life21`.`system_email_queue`(`email_to`, `email_subject`,`email_body`,`email_created_dt`, `email_status`, `email_bcc`, `email_type`)
VALUES (@email_to, @email_subject, @email_body, @tgl, 'P', @email_bcc, 'UploadCCResult')";
cmd.Parameters.Add(new MySqlParameter("@email_to", MySqlDbType.VarChar)
{
Value = emailSent.email_to
});
cmd.Parameters.Add(new MySqlParameter("@email_subject", MySqlDbType.VarChar)
{
Value = emailSent.email_subject
});
cmd.Parameters.Add(new MySqlParameter("@email_body", MySqlDbType.Text)
{
Value = emailSent.email_body
});
cmd.Parameters.Add(new MySqlParameter("@tgl", MySqlDbType.DateTime)
{
Value = DateTime.Now
});
cmd.Parameters.Add(new MySqlParameter("@email_bcc", MySqlDbType.VarChar)
{
Value = emailSent.email_bcc
});
cmd.ExecuteNonQuery();
cmdx.CommitTransaction();
retval = true;
message = "sukses";
}
else
{
throw new Exception("Salah billing id");
}
}
catch (Exception ex)
{
retval = false;
message = ex.Message;
if (cmdx.CurrentTransaction != null)
{
cmdx.RollbackTransaction();
}
}
finally
{
if (cmdx.CurrentTransaction != null)
{
cmdx.RollbackTransaction();
}
cmd.Connection.Close();
}
return(Json(new { data = retval, message = message }));
}
