public int UpdateControlNumber(PolicyRenewalModel renewal)
{
if (!String.IsNullOrEmpty(renewal.ControlNumber))
{
var sSQL = @"UPDATE PD SET InsuranceNumber = @InsuranceNumber, PolicyStage = N'R', ValidityFrom = GETDATE()
FROM tblControlNumber CN
INNER JOIN tblPaymentDetails PD ON CN.PaymentId = PD.PaymentID
WHERE CN.ValidityTo IS NULL
AND CN.ControlNumber = @ControlNumber;";
SqlParameter[] parameters =
{
new SqlParameter("@ControlNumber", renewal.ControlNumber),
new SqlParameter("@InsuranceNumber", renewal.CHFID)
};
try
{
var dh = new DB.SqlServer.DataHelper.DataHelper(_configuration);
dh.Execute(sSQL, parameters, CommandType.Text);
}
catch (Exception ex)
{
return((int)Errors.Renewal.CouldNotUpdateControlNumber);
}
//}
}
return((int)Errors.Renewal.Accepted);
}
public int UpdateControlNumber(EnrolFamilyModel familyModel, NewFamilyResponse serverResponse)
{
foreach (var family in familyModel.Family)
{
foreach (var policy in family.Policies)
{
if (policy.ControlNumber.Length == 0)
{
continue;
}
var policyId = serverResponse.Family.Where(f => f.FamilyId == family.FamilyId).FirstOrDefault().Policies.Where(p => p.PolicyId == policy.PolicyId).Select(p => p.PolicyDBId).FirstOrDefault();
var sSQL = @"UPDATE PD SET InsuranceNumber = I.CHFID, PremiumID = PR.PremiumId, PolicyStage = Pol.PolicyStage, enrollmentDate = Pol.EnrollDate, ValidityFrom = GETDATE()
FROM tblControlNumber CN
INNER JOIN tblPaymentDetails PD ON CN.PaymentId = PD.PaymentID
INNER JOIN tblInsuree I ON IsHead = 1
INNER JOIN tblPolicy Pol ON Pol.PolicyID = @PolicyId AND Pol.FamilyID = I.FamilyId
LEFT OUTER JOIN tblPremium PR ON Pol.PolicyID = PR.PolicyID
WHERE CN.ValidityTo IS NULL
AND I.ValidityTo IS NULL
AND CN.ControlNumber = @ControlNumber;";
SqlParameter[] parameters =
{
new SqlParameter("@ControlNumber", policy.ControlNumber),
new SqlParameter("@PolicyId", policyId),
};
try
{
var dh = new DB.SqlServer.DataHelper.DataHelper(_configuration);
dh.Execute(sSQL, parameters, CommandType.Text);
}
catch (Exception)
{
return(1001);
}
}
}
return(0);
}