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