public void GetPaymentInfo(string Id)
{
var sSQL = @"SELECT tblPayment.PaymentID, tblPayment.ExpectedAmount,tblPayment.LanguageName,tblPayment.TypeOfPayment, tblPaymentDetails.ExpectedAmount AS ExpectedDetailAmount,
tblPayment.ReceivedAmount, tblPayment.PaymentDate, tblInsuree.LastName, tblInsuree.OtherNames,tblPaymentDetails.InsuranceNumber,tblPayment.PhoneNumber,
tblProduct.ProductName, tblPaymentDetails.ProductCode, tblPolicy.ExpiryDate, tblPolicy.EffectiveDate,tblControlNumber.ControlNumber,tblPolicy.PolicyStatus, tblPolicy.PolicyValue - ISNULL(mp.PrPaid,0) Outstanding
FROM tblControlNumber
RIGHT OUTER JOIN tblInsuree
RIGHT OUTER JOIN tblProduct
RIGHT OUTER JOIN tblPayment
INNER JOIN tblPaymentDetails
ON tblPayment.PaymentID = tblPaymentDetails.PaymentID
ON tblProduct.ProductCode = tblPaymentDetails.ProductCode
ON tblInsuree.CHFID = tblPaymentDetails.InsuranceNumber
ON tblControlNumber.PaymentID = tblPayment.PaymentID
LEFT OUTER JOIN tblPremium
LEFT OUTER JOIN tblPolicy
LEFT OUTER JOIN (
select P.PolicyID PolID, SUM(P.Amount) PrPaid from tblpremium P inner join tblPaymentDetails PD ON PD.PremiumID = P.PremiumId INNER JOIN tblPayment Pay ON Pay.PaymentID = PD.PaymentID where P.ValidityTo IS NULL AND Pay.PaymentStatus = 5 GROUP BY P.PolicyID
) MP ON MP.PolID = tblPolicy.PolicyID
ON tblPremium.PolicyID = tblPolicy.PolicyID
ON tblPaymentDetails.PremiumID = tblPremium.PremiumId
WHERE (tblPayment.PaymentID = @PaymentID) AND (tblProduct.ValidityTo IS NULL) AND (tblInsuree.ValidityTo IS NULL)";
SqlParameter[] parameters =
{
new SqlParameter("@PaymentID", Id)
};
try
{
var data = dh.GetDataTable(sSQL, parameters, CommandType.Text);
if (data.Rows.Count > 0)
{
var row1 = data.Rows[0];
PaymentId = Id;
ControlNum = row1["ControlNumber"] != System.DBNull.Value ? Convert.ToString(row1["ControlNumber"]) : null;
ExpectedAmount = row1["ExpectedAmount"] != System.DBNull.Value ? Convert.ToDecimal(row1["ExpectedAmount"]) : 0;
var language = row1["LanguageName"] != System.DBNull.Value ? Convert.ToString(row1["LanguageName"]) : "en";
var languages = LocalDefault.PrimaryLangReprisantations(Configuration);
if (language == null || languages.Contains(language.ToLower()))
{
Language = Language.Primary;
}
else
{
Language = Language.Secondary;
}
typeOfPayment = row1["TypeOfPayment"] != System.DBNull.Value ? (TypeOfPayment?)Enum.Parse(typeof(TypeOfPayment), Convert.ToString(row1["TypeOfPayment"]), true) : null;
PhoneNumber = row1["PhoneNumber"] != System.DBNull.Value ? Convert.ToString(row1["PhoneNumber"]) : null;
PaymentDate = (DateTime?)(row1["PaymentDate"] != System.DBNull.Value ? row1["PaymentDate"] : null);
PaidAmount = (decimal?)(row1["ReceivedAmount"] != System.DBNull.Value ? row1["ReceivedAmount"] : null);
OutStAmount = (decimal?)(row1["Outstanding"] != System.DBNull.Value ? row1["Outstanding"] : null);
InsureeProducts = new List <InsureeProduct>();
for (int i = 0; i < data.Rows.Count; i++)
{
var rw = data.Rows[i];
bool active = false;
if (rw["PolicyStatus"] != System.DBNull.Value && Convert.ToInt32(rw["PolicyStatus"]) == 2)
{
active = true;
}
var othernames = rw["OtherNames"] != System.DBNull.Value ? Convert.ToString(rw["OtherNames"]) : null;
var lastname = rw["LastName"] != System.DBNull.Value ? Convert.ToString(rw["LastName"]) : null;
InsureeProducts.Add(
new InsureeProduct()
{
InsureeNumber = rw["InsuranceNumber"] != System.DBNull.Value ? Convert.ToString(rw["InsuranceNumber"]) : null,
InsureeName = othernames + " " + lastname,
ProductName = rw["ProductName"] != System.DBNull.Value ? Convert.ToString(rw["ProductName"]) : null,
ProductCode = rw["ProductCode"] != System.DBNull.Value ? Convert.ToString(rw["ProductCode"]) : null,
ExpiryDate = (DateTime?)(rw["ExpiryDate"] != System.DBNull.Value ? rw["ExpiryDate"] : null),
EffectiveDate = (DateTime?)(rw["EffectiveDate"] != System.DBNull.Value ? rw["EffectiveDate"] : null),
PolicyActivated = active,
ExpectedProductAmount = rw["ExpectedDetailAmount"] != System.DBNull.Value ? Convert.ToDecimal(rw["ExpectedDetailAmount"]) : 0
}
);
}
}
else
{
throw new DataException("3-Wrong Control Number");
}
}
catch (Exception e)
{
throw e;
}
}
public DataMessage SaveIntent(IntentOfPay _intent, int?errorNumber = 0, string errorMessage = null)
{
var Proxyfamily = LocalDefault.FamilyMembers(Configuration);
List <PaymentDetail> policies = new List <PaymentDetail>();
if (_intent.policies != null)
{
policies = _intent.policies;
}
XElement PaymentIntent = new XElement("PaymentIntent",
new XElement("Header",
new XElement("OfficerCode", _intent.enrolment_officer_code),
new XElement("RequestDate", _intent.request_date),
new XElement("PhoneNumber", _intent.phone_number),
new XElement("LanguageName", _intent.language),
new XElement("AuditUserId", -1)
),
new XElement("Details",
policies.Select(x =>
new XElement("Detail",
new XElement("InsuranceNumber", x.insurance_number),
new XElement("ProductCode", x.insurance_product_code),
new XElement("EnrollmentDate", DateTime.UtcNow),
new XElement("IsRenewal", x.IsRenewal())
)
)
),
new XElement("ProxySettings",
new XElement("AdultMembers", Proxyfamily.Adults),
new XElement("ChildMembers", Proxyfamily.Children),
new XElement("OAdultMembers", Proxyfamily.OtherAdults),
new XElement("OChildMembers", Proxyfamily.OtherChildren)
)
);
SqlParameter[] sqlParameters =
{
new SqlParameter("@Xml", PaymentIntent.ToString()),
new SqlParameter("@ErrorNumber", errorNumber),
new SqlParameter("@ErrorMsg", errorMessage),
new SqlParameter("@PaymentID", SqlDbType.Int)
{
Direction = ParameterDirection.Output
},
new SqlParameter("@ExpectedAmount", SqlDbType.Decimal)
{
Direction = ParameterDirection.Output
},
new SqlParameter("@ProvidedAmount", _intent.amount_to_be_paid),
new SqlParameter("@PriorEnrollment", LocalDefault.PriorEnrolmentRequired(Configuration))
};
DataMessage message;
try
{
bool error = true;
var data = dh.ExecProcedure("uspInsertPaymentIntent", sqlParameters);
var rv = int.Parse(data[2].Value.ToString());
if (rv == 0)
{
error = false;
}
DataTable dt = new DataTable();
dt.Clear();
dt.Columns.Add("internal_identifier");
dt.Columns.Add("control_number");
DataRow rw = dt.NewRow();
var PaymentId = data[0].Value.ToString();
rw["internal_identifier"] = PaymentId;
dt.Rows.Add(rw);
ExpectedAmount = decimal.Parse(data[1].Value.ToString());
var languages = LocalDefault.PrimaryLangReprisantations(Configuration);
if (_intent.language == null || languages.Contains(_intent.language.ToLower()))
{
Language = Language.Primary;
}
else
{
Language = Language.Secondary;
}
message = new SaveIntentResponse(rv, error, dt, (int)Language).Message;
GetPaymentInfo(PaymentId);
}
catch (Exception e)
{
message = new SaveIntentResponse(e).Message;
}
return(message);
}
