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 })); }