public QRCodePolicyDetails GetVehicleDetails(string vrn = "", string policyNumber = "")
{
QRCodePolicyDetails details = new QRCodePolicyDetails();
try
{
details.Policies = new List <QRCodeModel>();
int lenthPolicy = 1;
if (!String.IsNullOrEmpty(policyNumber))
{
var splitPolicy = policyNumber.Split('-');
if (Convert.ToInt32(splitPolicy[1]) > 1)
{
lenthPolicy = 2;
}
}
// InsuranceContext.CertSerialNoDetails
var query = "Select VehicleLicenceFee,Email,IsCustomEmail,StampDuty,ZTSCLevy,Premium,Customer.Id as CustomerId,ModelDescription,VehicleDetail.RenewalDate, RadioLicenseCost, IncludeRadioLicenseCost, CoverType.Name as CoverTypeName,";
query += " PaymentTerm.Name as PaymentTermName,Covertype.Name, FirstName,LastName,PolicyNumber,RegistrationNo,SummaryDetail.Id as SummaryId, case when PaymentMethod.Name<>'PayLater' then 'Paid' else 'PayLater' end as PaymentStatus, PolicyDetail.Id as PolicyId from VehicleDetail";
query += " join PolicyDetail on VehicleDetail.PolicyId=PolicyDetail.Id";
query += " Left join Customer on PolicyDetail.CustomerId=Customer.Id";
query += " Left Join CoverType on VehicleDetail.CoverTypeId=CoverType.Id";
query += " Left Join PaymentTerm on VehicleDetail.PaymentTermId=PaymentTerm.Id";
query += " Left Join VehicleModel On VehicleDetail.ModelId=VehicleModel.ModelCode";
query += " Left Join SummaryVehicleDetail On VehicleDetail.Id=SummaryVehicleDetail.VehicleDetailsId";
query += " Left Join SummaryDetail On SummaryVehicleDetail.SummaryDetailId=SummaryDetail.Id";
query += " left join AspNetUsers on Customer.UserID=AspNetUsers.Id";
query += " left join PaymentMethod on SummaryDetail.PaymentMethodId= PaymentMethod.Id ";
// query += " left join CertSerialNoDetail on PolicyDetail.Id=CertSerialNoDetail.PolicyId ";
query += " where SummaryDetail.isQuotation=0 and VehicleDetail.isactive=1";
if (!string.IsNullOrEmpty(vrn))
{
query += " and VehicleDetail.RegistrationNo='" + vrn + "'";
}
if (!string.IsNullOrEmpty(policyNumber) && lenthPolicy == 1)
{
query += " and PolicyDetail.PolicyNumber='" + policyNumber + "'";
}
if (!string.IsNullOrEmpty(policyNumber) && lenthPolicy > 1)
{
query += " and VehicleDetail.RenewPolicyNumber='" + policyNumber + "'";
}
query += " order by VehicleDetail.Id desc";
//var query = "select policyDetail.Number as PolicyNumber";
List <QRCodeModel> list = InsuranceContext.Query(query).Select(c => new QRCodeModel
{
Message = "Successfully.",
PolicyId = c.PolicyId,
CustomerId = c.CustomerId,
CustomerName = c.FirstName + " " + c.LastName,
PolicyNumber = c.PolicyNumber,
Registrationno = c.RegistrationNo,
ModelDescription = c.ModelDescription,
Covertype = c.CoverTypeName,
PaymentTerm = c.PaymentTermName,
ExpireDate = c.RenewalDate,
IncludeRadioLicenseCost = Convert.ToBoolean(c.IncludeRadioLicenseCost),
RadioLicenseCost = c.IncludeRadioLicenseCost == null ? 0 : Convert.ToDecimal(c.IncludeRadioLicenseCost),
TotalPremium = c.VehicleLicenceFee + c.StampDuty + c.ZTSCLevy + c.Premium + (c.RadioLicenseCost == null ? 0 : Convert.ToDecimal(c.RadioLicenseCost)),
SummaryId = c.SummaryId,
Email = c.Email,
IsCustomEmail = c.IsCustomEmail,
PaymentStatus = c.PaymentStatus
}).ToList();
var detail = InsuranceContext.PolicyDetails.Single(where : $"PolicyNumber='{policyNumber}'");
var renewPolicyNumber = "";
if (detail == null)
{
var vehicleDetail = InsuranceContext.VehicleDetails.Single(where : $"RenewPolicyNumber = '{policyNumber}'");
if (vehicleDetail != null)
{
renewPolicyNumber = policyNumber;
}
}
foreach (var item in list)
{
var recQuery = "SELECT top 1 * FROM ReceiptModuleHistory where policyid= " + item.PolicyId + " and (IsActive is null or IsActive=1) order by Id Desc";
if (!string.IsNullOrEmpty(renewPolicyNumber))
{
recQuery = "select top 1 * from ReceiptModuleHistory where RenewPolicyNumber='" + renewPolicyNumber + "' and (IsActive is null or IsActive=1) order by id desc";
}
var receipt = InsuranceContext.Query(recQuery).Select(x => new ReceiptModuleHistory()
{
Id = x.Id,
AmountDue = x.AmountDue,
Balance = x.Balance
}).FirstOrDefault();
if (receipt != null)
{
details.AmountDue += Convert.ToDecimal(receipt.AmountDue);
details.Balance += Convert.ToDecimal(receipt.Balance);
}
else
{
details.Balance = list.Sum(c => c.TotalPremium); // default balane
}
}
details.Policies = list;
details.Message = "Records found.";
var query1 = "SELECT top 1 [Id] FROM ReceiptModuleHistory order by Id Desc";
var receipt1 = InsuranceContext.Query(query1).Select(x => new ReceiptModuleHistory()
{
Id = x.Id,
}).FirstOrDefault();
if (receipt1 != null)
{
details.RecieptNumber = receipt1 == null ? 100000 : receipt1.Id + 1;
if (list.Count() > 0)
{
details.AmountDue = list.Sum(c => c.TotalPremium);
details.Message = "Records found.";
}
else
{
details.Message = "Records not found.";
}
}
}
catch (Exception ex)
{
details.Message = "Exception.";
}
return(details);
}
public QRCodePolicyDetails GetQRCodes(string QRCode)
{
QRCodePolicyDetails details = new QRCodePolicyDetails();
try
{
details.Policies = new List <QRCodeModel>();
// InsuranceContext.CertSerialNoDetails
var CertSerialNoDetails = InsuranceContext.CertSerialNoDetails.Single(where : $"CertSerialNo='" + QRCode + "'");
PolicyDetail policyDetetials = null;
if (CertSerialNoDetails != null)
{
policyDetetials = InsuranceContext.PolicyDetails.Single(CertSerialNoDetails.PolicyId);
}
if (policyDetetials != null)
{
var receiptHistory = InsuranceContext.ReceiptHistorys.Single(where : "PolicyNumber='" + policyDetetials.PolicyNumber + "'");
if (receiptHistory != null)
{
QRCodeModel model = new QRCodeModel {
CustomerId = 0
};
details.Policies.Add(model);
details.RecieptNumber = 0;
details.Message = "QRCode has been already read.";
return(details);
}
}
var query = "Select VehicleLicenceFee,Email,IsCustomEmail,StampDuty,ZTSCLevy,Premium,Customer.Id as CustomerId,ModelDescription,VehicleDetail.RenewalDate, RadioLicenseCost, IncludeRadioLicenseCost, CoverType.Name as CoverTypeName,";
query += " PaymentTerm.Name as PaymentTermName,Covertype.Name, FirstName,LastName,PolicyNumber,RegistrationNo,SummaryDetail.Id as SummaryId, case when PaymentMethod.Name<>'PayLater' then 'Paid' else 'PayLater' end as PaymentStatus from VehicleDetail";
query += " join PolicyDetail on VehicleDetail.PolicyId=PolicyDetail.Id";
query += " Left join Customer on PolicyDetail.CustomerId=Customer.Id";
query += " Left Join CoverType on VehicleDetail.CoverTypeId=CoverType.Id";
query += " Left Join PaymentTerm on VehicleDetail.PaymentTermId=PaymentTerm.Id";
query += " Left Join VehicleModel On VehicleDetail.ModelId=VehicleModel.ModelCode";
query += " Left Join SummaryVehicleDetail On VehicleDetail.Id=SummaryVehicleDetail.VehicleDetailsId";
query += " Left Join SummaryDetail On SummaryVehicleDetail.SummaryDetailId=SummaryDetail.Id";
query += " left join AspNetUsers on Customer.UserID=AspNetUsers.Id";
query += " left join PaymentMethod on SummaryDetail.PaymentMethodId= PaymentMethod.Id ";
query += " left join CertSerialNoDetail on PolicyDetail.Id=CertSerialNoDetail.PolicyId where VehicleDetail.IsActive=1 and CertSerialNo= '" + QRCode + "'";
//var query = "select policyDetail.Number as PolicyNumber";
List <QRCodeModel> list = InsuranceContext.Query(query).Select(c => new QRCodeModel
{
Message = "Successfully.",
CustomerId = c.CustomerId,
CustomerName = c.FirstName + " " + c.LastName,
PolicyNumber = c.PolicyNumber,
Registrationno = c.RegistrationNo,
ModelDescription = c.ModelDescription,
Covertype = c.CoverTypeName,
PaymentTerm = c.PaymentTermName,
ExpireDate = c.RenewalDate,
IncludeRadioLicenseCost = Convert.ToBoolean(c.IncludeRadioLicenseCost),
RadioLicenseCost = c.IncludeRadioLicenseCost == false ? 0 : Convert.ToDecimal(c.IncludeRadioLicenseCost),
TotalPremium = c.VehicleLicenceFee + c.StampDuty + c.ZTSCLevy + c.Premium + c.RadioLicenseCost,
SummaryId = c.SummaryId,
Email = c.Email,
IsCustomEmail = c.IsCustomEmail,
PaymentStatus = c.PaymentStatus
}).ToList();
// in case of renew
//test
var query1 = "SELECT top 1 [Id] FROM ReceiptModuleHistory order by Id Desc";
//var re = InsuranceContext.ReceiptHistorys.All(x => x.Id);
var receipt = InsuranceContext.Query(query1).Select(x => new ReceiptModuleHistory()
{
Id = x.Id,
}).FirstOrDefault();
// var receiptid=InsuranceContext.r
details.RecieptNumber = receipt == null ? 100000 : receipt.Id + 1;
details.Policies = list;
if (list.Count() > 0)
{
details.AmountDue = list.Sum(c => c.TotalPremium);
details.Message = "Records found.";
}
else
{
details.Message = "No records found.";
}
}
catch (Exception ex)
{
details.Message = "Exception.";
}
return(details);
}
