public string GetALMId()
{
string almId = "";
var getcustomerdetail = InsuranceContext.Query(" select top 1 AlmId from [dbo].[Branch] where AlmId is not null order by id desc ")
.Select(x => new Customer()
{
ALMId = x.AlmId
}).ToList().FirstOrDefault();
if (getcustomerdetail != null && getcustomerdetail.ALMId != null)
{
string number = getcustomerdetail.ALMId.Split('K')[1];
long pernumer = Convert.ToInt64(number) + 1;
string policyNumbera = string.Empty;
int lengths = 3;
lengths = lengths - pernumer.ToString().Length;
for (int i = 0; i < lengths; i++)
{
policyNumbera += "0";
}
policyNumbera += pernumer;
// customer.ALMId = "GENE-SSK" + policyNumbera;
almId = "GENE-SSK" + policyNumbera;
}
else
{
almId = "GENE-SSK003";
}
return(almId);
}
public List <GrossWrittenPremiumReportModels> getGWPData(string startDate, string endDate)
{
var ListGrossWrittenPremiumReport = new List <GrossWrittenPremiumReportModels>();
try
{
var query = " select PolicyDetail.PolicyNumber as Policy_Number, Customer.ALMId, case when Customer.ALMId is null then [dbo].fn_GetUserCallCenterAgent(SummaryDetail.CreatedBy) else [dbo].fn_GetUserALM(Customer.BranchId) end as PolicyCreatedBy, Customer.FirstName + ' ' + Customer.LastName as Customer_Name,VehicleDetail.TransactionDate as Transaction_date, ";
query += " case when Customer.id=SummaryDetail.CreatedBy then [dbo].fn_GetUserBranch(Customer.id) else [dbo].fn_GetUserBranch(SummaryDetail.CreatedBy) end as BranchName, ";
query += " VehicleDetail.CoverNote as CoverNoteNum, PaymentMethod.Name as Payment_Mode, PaymentTerm.Name as Payment_Term,CoverType.Name as CoverType, Currency.Name as Currency, ";
query += " VehicleDetail.Premium + VehicleDetail.StampDuty + VehicleDetail.ZTSCLevy as Premium_due, VehicleDetail.StampDuty as Stamp_duty, VehicleDetail.ZTSCLevy as ZTSC_Levy, ";
query += " cast(VehicleDetail.Premium * 30 / 100 as decimal(10, 2)) as Comission_Amount, VehicleDetail.IncludeRadioLicenseCost, ";
query += " CASE WHEN IncludeRadioLicenseCost = 1 THEN VehicleDetail.RadioLicenseCost else 0 end as RadioLicenseCost, VehicleDetail.VehicleLicenceFee as Zinara_License_Fee, ";
query += " VehicleDetail.RenewalDate as PolicyRenewalDate, VehicleDetail.IsActive, VehicleDetail.RenewPolicyNumber as RenewPolicyNumber, ";
query += " VehicleDetail.BusinessSourceDetailId, SummaryDetail.id as SummaryDetailId, BusinessSource.Source as BusinessSourceName, SourceDetail.FirstName + ' ' + SourceDetail.LastName as SourceDetailName from PolicyDetail ";
query += " join Customer on PolicyDetail.CustomerId = Customer.Id ";
query += " join VehicleDetail on PolicyDetail.Id = VehicleDetail.PolicyId ";
query += "join SummaryVehicleDetail on VehicleDetail.id = SummaryVehicleDetail.VehicleDetailsId ";
query += " join SummaryDetail on SummaryDetail.id = SummaryVehicleDetail.SummaryDetailId ";
//query += " join PaymentInformation on SummaryDetail.Id=PaymentInformation.SummaryDetailId ";
query += " join PaymentMethod on SummaryDetail.PaymentMethodId = PaymentMethod.Id ";
query += "join PaymentTerm on VehicleDetail.PaymentTermId = PaymentTerm.Id ";
query += " left join CoverType on VehicleDetail.CoverTypeId = CoverType.Id ";
query += " left join Currency on VehicleDetail.CurrencyId = Currency.Id ";
query += " left join BusinessSource on BusinessSource.Id = VehicleDetail.BusinessSourceDetailId ";
query += " left join SourceDetail on VehicleDetail.BusinessSourceDetailId = SourceDetail.Id join AspNetUsers on AspNetUsers.id=customer.UserID join AspNetUserRoles on AspNetUserRoles.UserId=AspNetUsers.Id ";
query += " where (VehicleDetail.IsActive = 1 or VehicleDetail.IsActive = null) and SummaryDetail.isQuotation=0 and SummaryDetail.PaymentMethodId <>" + (int)paymentMethod.PayLater + " and ( CONVERT(date, VehicleDetail.TransactionDate) >= convert(date, '" + startDate + "', 101) and CONVERT(date, VehicleDetail.TransactionDate) <= convert(date, '" + endDate + "', 101)) order by VehicleDetail.Id desc ";
ListGrossWrittenPremiumReport = InsuranceContext.Query(query).
Select(x => new GrossWrittenPremiumReportModels()
{
Policy_Number = x.Policy_Number,
BranchName = x.BranchName,
PolicyCreatedBy = x.PolicyCreatedBy,
Customer_Name = x.Customer_Name,
Transaction_date = x.Transaction_date.ToShortDateString(),
CoverNoteNum = x.CoverNoteNum,
Payment_Mode = x.Payment_Mode,
Payment_Term = x.Payment_Term,
CoverType = x.CoverType,
Currency = x.Currency,
Premium_due = x.Premium_due,
Stamp_duty = x.Stamp_duty,
ZTSC_Levy = x.ZTSC_Levy,
ALMId = x.ALMId,
Comission_Amount = x.Comission_Amount,
RadioLicenseCost = x.RadioLicenseCost,
Zinara_License_Fee = x.Zinara_License_Fee,
PolicyRenewalDate = x.PolicyRenewalDate,
IsActive = x.IsActive,
RenewPolicyNumber = x.RenewPolicyNumber,
}).ToList();
}
catch (Exception ex)
{
// Debug.WriteLine(ex);
return(ListGrossWrittenPremiumReport);
}
return(ListGrossWrittenPremiumReport);
}
public BranchModel GetBranchByIp(string IpAddress)
{
var branchDetils = new BranchModel();
try
{
//var branch = InsuranceContext.MachineBranches.Single(where: $"IpAddress={IpAddress}");
var branch = InsuranceContext.Query("select * from [MachineBranch] where IpAddress like '%" + IpAddress + "%'")
.Select(x => new BranchModel()
{
Id = x.BranchId,
}).FirstOrDefault();
if (branch != null)
{
branchDetils.Id = branch.Id;
// branchDetils.BranchName = branch.BranchName;
}
}
catch (Exception ex)
{
}
return(branchDetils);
}
public ActionResult VehicleModelList()
{
// var modellist = InsuranceContext.VehicleModels.All(where: "IsActive= 'True' or IsActive is Null").OrderByDescending(x => x.Id).ToList();
//var list = (from vehicleModel in InsuranceContext.VehicleModels.All().ToList()
// join vehicleMake in InsuranceContext.VehicleMakes.All().ToList()
// on vehicleModel.MakeCode equals vehicleMake.MakeCode
// select new ClsVehicleModel
// {
// ModelDescription = vehicleModel.ModelDescription,
// MakeDescription = vehicleMake.MakeDescription,
// ModelCode = vehicleModel.ModelCode,
// ShortDescription = vehicleModel.ShortDescription,
// Id = vehicleModel.Id
// }).OrderByDescending(c => c.Id).ToList();
var list = InsuranceContext.Query("select ModelDescription, ModelCode, MakeDescription, VehicleModel.Id as ModelId, VehicleModel.ShortDescription from VehicleModel join VehicleMake on VehicleModel.MakeCode = VehicleMake.MakeCode")
.Select(x => new ClsVehicleModel()
{
MakeDescription = x.MakeDescription,
ModelDescription = x.ModelDescription,
Id = x.ModelId,
ShortDescription = x.ShortDescription,
ModelCode = x.ModelCode
}).ToList();
return(View(list));
}
public List <BranchModel> GetAllBranch()
{
//var list = InsuranceContext.Branches.All().ToList();
string query = "select Id, BranchName, Location_Id from Branch";
List <BranchModel> list = InsuranceContext.Query(query).Select(x => new BranchModel()
{
Id = x.Id,
BranchName = x.BranchName,
Location_Id = x.Location_Id,
}).ToList();
//var branchList = new List<BranchModel>();
//foreach (var item in list)
//{
// branchList.Add(new BranchModel { Id = item.Id, BranchName = item.BranchName });
//}
EmailService logService = new EmailService();
return(list);
}
public Content GetContentById(int contentId)
{
using var context = new InsuranceContext();
var content = context.Content.Find(contentId);
return(content);
}
public void Add(Content content)
{
using var context = new InsuranceContext();
var entity = context.Content.Add(content);
entity.State = EntityState.Added;
context.SaveChanges();
}
public ActionResult Commission()
{
List <AgentCommissionModel> list = (InsuranceContext.Query("select AgentCommission.Id, Source, CommissionName, CommissionAmount from AgentCommission join BusinessSource on AgentCommission.BusinessSourceId = BusinessSource.Id").Select(c => new AgentCommissionModel()
{
Source = c.Source, Id = c.Id, CommissionName = c.CommissionName, CommissionAmount = c.CommissionAmount
})).ToList();
return(View(list));
}
public void Delete(Content content)
{
using var context = new InsuranceContext();
var entity = context.Content.Remove(content);
entity.State = EntityState.Deleted;
context.SaveChanges();
}
public Customer GetLastCustomerDetail()
{
var customerDetail = InsuranceContext.Query("select top 1 * from Customer order by id desc").Select(x => new Customer()
{
//Id = x.Id,
CustomerId = x.Id
}).FirstOrDefault();
return(customerDetail);
}
/// <summary>
/// Initializes a new instance of the <see cref="PolicyServiceTest"/> class.
/// </summary>
public PolicyServiceTest()
{
var optionsBuilder = new DbContextOptionsBuilder <InsuranceContext>();
optionsBuilder.UseSqlServer("Server=localhost\\SQLEXPRESS;Database=Insurance;Trusted_Connection=True;MultipleActiveResultSets=true");
_insuranceContext = new InsuranceContext(optionsBuilder.Options);
_policyRepository = new PolicyRepository(_insuranceContext);
_policyService = new PolicyService(_policyRepository);
}
/// <summary>
/// Add or update a coverage to a policy.
/// </summary>
/// <param name="context">Insurance context.</param>
/// <param name="policyName">Policy name.</param>
/// <param name="coverageName">Coverage name.</param>
void AddOrUpdateCoverage(InsuranceContext context, string policyName, string coverageName)
{
var pol = context.Policies.SingleOrDefault(p => p.Name == policyName);
var cov = pol.Coverages.SingleOrDefault(c => c.Name == coverageName);
if (cov == null)
{
pol.Coverages.Add(context.Coverages.Single(c => c.Name == coverageName));
}
}
// GET: Vehicle
public ActionResult Index()
{
insctx = new InsuranceContext();
var data = from Vehicle v in insctx.Vehicles.ToList()
select v;
var json = new JavaScriptSerializer().Serialize(data);
return(Json(json, JsonRequestBehavior.AllowGet));
}
public AccountController(UserManager <Account> userMgr, SignInManager <Account> signinMgr, IPasswordHasher <Account> passwordHash, IPasswordValidator <Account> passwordValid, IUserValidator <Account>
userValid, InsuranceContext context)
{
userManager = userMgr;
signInManager = signinMgr;
passwordHasher = passwordHash;
passwordValidator = passwordValid;
userValidator = userValid;
_context = context;
}
// GET: Customers
public ActionResult Index()
{
insctx = new InsuranceContext();
var data = from Customer c in insctx.Customers.ToList()
join Policy p in insctx.Policies.ToList() on c.PhoneNo equals p.CustomerId
select new { c.Name, c.PhoneNo, p.PolicyNo, p.StartDate, p.ExpiryDate, p.SumAssured };
var json = new JavaScriptSerializer().Serialize(data);
return(Json(json, JsonRequestBehavior.AllowGet));
}
public JsonResult GetVehicleDetails(string vrn)
{
PayLaterPolicyModel model = new PayLaterPolicyModel();
string query = " select PolicyDetail.PolicyNumber, PolicyDetail.Id as PolicyId, Customer.FirstName + ' ' + Customer.LastName as CustomerName, ";
query += " VehicleDetail.RegistrationNo as RegistrationNo, VehicleMake.MakeDescription, VehicleDetail.Id as VehicleID, ";
query += " VehicleModel.ModelDescription, SummaryDetail.TotalPremium, SummaryDetail.Id as SummaryDetailId, ";
query += " PaymentInformation.Id as PaymentInformationId, case when ";
query += " PaymentMethod.Name <> 'PayLater' then 'Paid' else 'PayLater' end as PaymentStatus ";
query += " from PolicyDetail join Customer on PolicyDetail.CustomerId = Customer.Id ";
query += " join VehicleDetail on VehicleDetail.PolicyId = PolicyDetail.Id ";
query += " join SummaryVehicleDetail on VehicleDetail.Id= SummaryVehicleDetail.VehicleDetailsId ";
query += " join SummaryDetail on SummaryVehicleDetail.SummaryDetailId = SummaryDetail.Id ";
query += " left join VehicleMake on VehicleDetail.MakeId = VehicleMake.MakeCode left ";
query += " join VehicleModel on VehicleDetail.ModelId = VehicleModel.ModelCode ";
query += " join PaymentInformation on SummaryDetail.Id = PaymentInformation.SummaryDetailId ";
query += " join PaymentMethod on SummaryDetail.PaymentMethodId = PaymentMethod.Id where VehicleDetail.RegistrationNo=" + vrn + " and VehicleDetail.isactive=1";
var result = InsuranceContext.Query(query).Select(c => new PayLaterPolicyDetail()
{
VehicleID = c.VehicleID,
PolicyId = c.PolicyId,
SummaryDetailId = c.SummaryDetailId,
PaymentInformationId = c.PaymentInformationId,
PolicyNumber = c.PolicyNumber,
CustomerName = c.CustomerName,
RegistrationNo = c.RegistrationNo,
MakeDescription = c.MakeDescription,
ModelDescription = c.ModelDescription,
Vehicle = c.MakeDescription + ' ' + c.ModelDescription,
TotalPremium = c.TotalPremium
}).FirstOrDefault();
if (result == null)
{
result = new PayLaterPolicyDetail();
result.ErrorMsg = "Records not found";
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
public ActionResult ClaimPayment(int id, string successMsg = "")
{
ClaimAdjustmentModel model = new ClaimAdjustmentModel();
model.Id = id;
var claimAdjustment = InsuranceContext.ClaimAdjustments.Single(where : "Id=" + id);
TempData["Sucessmessage"] = successMsg;
if (claimAdjustment != null)
{
model.PayeeName = claimAdjustment.PayeeName;
model.PolicyholderName = claimAdjustment.PolicyholderName;
model.FinalAmountToPaid = claimAdjustment.FinalAmountToPaid;
}
var query = "select ClaimRegistrationProviderDetial.Id, ProviderType, ServiceProviderName, ServiceProviderFee from ClaimRegistrationProviderDetial ";
query += " join ServiceProvider on ClaimRegistrationProviderDetial.ServiceProviderId = ServiceProvider.Id ";
query += "join ServiceProviderType on ClaimRegistrationProviderDetial.ServiceProviderTypeId = ServiceProviderType.id ";
query += " where ClaimRegistrationProviderDetial.ClaimRegistrationId =" + claimAdjustment.ClaimRegisterationId;
model.ServiceProviderList = InsuranceContext.Query(query).Select(c => new ClaimRegistrationProviderModel {
Id = c.Id, ServiceProviderName = c.ServiceProviderName, ServiceProviderType = c.ProviderType, ServiceProviderFee = ServiceProviderFee(c.ProviderType, c.ServiceProviderFee, claimAdjustment.ExcessesAmount)
}).ToList();
var claimRegistrationProvider = InsuranceContext.ClaimRegistrationProviderDetials.All(where : "ClaimRegistrationId=" + claimAdjustment.ClaimRegisterationId).Select(c => c.ServiceProviderFee).Sum();
model.TotalAmountLeftToPayed = Convert.ToString(claimRegistrationProvider - _excessAmount);
// var providerList = InsuranceContext.ClaimRegistrationProviderDetials.All(where : "ClaimRegistrationId=" + claimAdjustment).ToList();
return(View(model));
}
public int InsertMachineBranch(string brachId, string IpAddress)
{
// InsuranceContext.MachineBranches()
// var machineBrach = InsuranceContext.MachineBranches.Single(where: $"IpAddress={IpAddress}");
var machineBrach = InsuranceContext.Query("select * from [MachineBranch] where IpAddress like '%" + IpAddress + "%'")
.Select(x => new BranchModel()
{
Id = x.BranchId,
}).FirstOrDefault();
if (machineBrach == null)
{
MachineBranch mBranch = new MachineBranch {
BranchId = Convert.ToInt32(brachId), IpAddress = IpAddress, CreatedOn = DateTime.Now, UpdatedOn = DateTime.Now
};
InsuranceContext.MachineBranches.Insert(mBranch);
}
return(0);
}
public FullEnrollmentDataController(InsuranceContext context)
{
_context = context;
}
public BrokersCategoriesController(InsuranceContext context)
{
_context = context;
}
public HealthPoliciesController(InsuranceContext context)
{
_context = context;
}
// GET: Agent
public ActionResult Index()
{
bool userLoggedin = (System.Web.HttpContext.Current.User != null) && System.Web.HttpContext.Current.User.Identity.IsAuthenticated;
if (userLoggedin)
{
var userid = System.Web.HttpContext.Current.User.Identity.GetUserId();
var roles = UserManager.GetRoles(userid).FirstOrDefault();
//if (roles != "SuperAdmin")
//{
// return RedirectToAction("Index", "CustomerRegistration");
//}
}
else
{
return(RedirectToAction("Index", "Agent"));
}
string url = HttpContext.Request.Url.Authority;
// Authority "localhost:49873" string
var branchList = InsuranceContext.Branches.All();
var query = "select Customer. * , AspNetUsers.Email, AgentLogo.LogoPath from Customer ";
query += " join AspNetUsers on Customer.UserID = AspNetUsers.Id ";
query += " join AspNetUserRoles on AspNetUserRoles.UserId = AspNetUsers.Id left join AgentLogo on Customer.Id = AgentLogo.CustomerId ";
query += " where AspNetUserRoles.RoleId ='" + _agent + "' and (IsActive is null or IsActive=1) order by Customer.Id desc ";
// var user1 = InsuranceContext.Query(query).Select
var user = InsuranceContext.Query(query).Select(x => new CustomerModel()
{
CustomerId = x.CustomerId,
UserID = x.UserID,
FirstName = x.FirstName,
LastName = x.LastName,
AddressLine1 = x.AddressLine1,
AddressLine2 = x.AddressLine2,
City = x.City,
NationalIdentificationNumber = x.NationalIdentificationNumber,
Zipcode = x.Zipcode,
Country = x.Country,
DateOfBirth = x.DateOfBirth,
Gender = x.Gender,
IsWelcomeNoteSent = x.IsWelcomeNoteSent,
IsPolicyDocSent = x.IsPolicyDocSent,
IsLicenseDiskNeeded = x.IsLicenseDiskNeeded,
IsOTPConfirmed = x.IsOTPConfirmed == null,
CreatedBy = x.CreatedBy,
ModifiedOn = x.ModifiedOn,
ModifiedBy = x.ModifiedBy,
IsActive = x.IsActive == null ? false : Convert.ToBoolean(x.IsActive),
CountryCode = x.Countrycode,
PhoneNumber = x.PhoneNumber,
IsCustomEmail = x.IsCustomEmail == null ? false : Convert.ToBoolean(x.IsCustomEmail),
EmailAddress = x.Email,
AgentLogo = x.LogoPath,
AgentWhatsapp = x.AgentWhatsapp,
// CompanyName = x.CompanyName,
// CompanyEmail = x.CompanyEmail,
// CompanyAddress = x.CompanyAddress,
// CompanyPhone = x.CompanyPhone,
// CompanyCity = x.CompanyCity,
// CompanyBusinessId = x.CompanyBusinessId,
// IsCorporate = x.IsCorporate,
// BranchId = x.BranchId,
// ALMId = x.ALMId,
Id = x.Id,
CreatedOn = x.CreatedOn,
Branch = GetAgentBranch(x.AgentBranch, branchList),
}).ToList();
ListUserViewModel lstUserModel = new ListUserViewModel();
lstUserModel.ListUsers = user;
return(View(lstUserModel));
}
public ClientsController(InsuranceContext context)
{
_context = context;
}
public LoginController(InsuranceContext _db)
{
db = _db;
}
public List <Content> GetContentsByCategoryId(int categoryId)
{
using var context = new InsuranceContext();
return(context.Content.Where(w => w.ContentCategoryId == categoryId).ToList());
}
public List <Content> GetContentAll()
{
using var context = new InsuranceContext();
return(context.Content.ToList());
}
public UtilityService()
{
context = new InsuranceContext();
}
public PolicyRepository(InsuranceContext context) : base(context)
{
}
public AdvisorsController(InsuranceContext context)
{
_context = context;
}
public ClientRepository(InsuranceContext context)
{
_context = context;
}
