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 <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 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 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 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 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);
}
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);
}
// 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));
}
// GET: AgentStaff
public ActionResult Index()
{
int loggedCustomerId = 0;
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 _customerData = InsuranceContext.Customers.All(where : $"UserId ='{userid}'").FirstOrDefault();
if (_customerData != null)
{
loggedCustomerId = _customerData.Id;
}
// var roles = UserManager.GetRoles(userid).FirstOrDefault();
//if (roles != "SuperAdmin")
//{
// return RedirectToAction("Index", "CustomerRegistration");
//}
}
else
{
return(RedirectToAction("Index", "Agent"));
}
var branchList = InsuranceContext.Branches.All();
var query = "select Customer. *, AspNetUsers.Email from Customer ";
query += " join AspNetUsers on Customer.UserID = AspNetUsers.Id ";
query += " join AspNetUserRoles on AspNetUserRoles.UserId = AspNetUsers.Id ";
query += " where AspNetUserRoles.RoleId ='" + _staff + "' and CreatedBy=" + loggedCustomerId + " and (IsActive is null or IsActive=1) ";
// 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,
// 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 = branchList.FirstOrDefault(c => c.Id == x.BranchId) == null ? "" : branchList.FirstOrDefault(c => c.Id == x.BranchId).BranchName
}).ToList();
List <CustomerModel> ListUserViewModel = new List <CustomerModel>();
ListUserViewModel lstUserModel = new ListUserViewModel();
lstUserModel.ListUsers = user;
return(View(lstUserModel));
}
public void SendWeeklyReport()
{
var dtOneMonthBack = DateTime.Now;
int year = dtOneMonthBack.Year;
int month = dtOneMonthBack.Month;
string firstWeekStart = dtOneMonthBack.ToString("MM") + "/01/" + DateTime.Now.Year.ToString();
string firstWeekEnd = dtOneMonthBack.ToString("MM") + "/07/" + DateTime.Now.Year.ToString();
string secondWeekStart = dtOneMonthBack.ToString("MM") + "/08/" + DateTime.Now.Year.ToString();
string secondWeekEnd = dtOneMonthBack.ToString("MM") + "/14/" + DateTime.Now.Year.ToString();
string thirdWeekStart = dtOneMonthBack.ToString("MM") + "/15/" + DateTime.Now.Year.ToString();
string thirdWeekEnd = dtOneMonthBack.ToString("MM") + "/21/" + DateTime.Now.Year.ToString();
string fourthWeekStart = dtOneMonthBack.ToString("MM") + "/22/" + DateTime.Now.Year.ToString();
DateTime lastDate = new DateTime(year, month,
DateTime.DaysInMonth(year, month));
string fourthWeekEnd = lastDate.ToString("MM/dd/yyyy");
//string firstWeekStart = dtOneMonthBack.ToString("MM/dd/yyyy");
//string firstWeekEnd = dtOneMonthBack.AddDays(7).ToString("MM/dd/yyyy");
//string secondWeekStart = dtOneMonthBack.AddDays(8).ToString("MM/dd/yyyy");
//string secondWeekEnd = dtOneMonthBack.AddDays(14).ToString("MM/dd/yyyy");
//string thirdWeekStart = dtOneMonthBack.AddDays(15).ToString("MM/dd/yyyy");
//string thirdWeekEnd = dtOneMonthBack.AddDays(21).ToString("MM/dd/yyyy");
//string fourthWeekStart = dtOneMonthBack.AddDays(22).ToString("MM/dd/yyyy");
////DateTime lastDate = new DateTime(year, month,
//// DateTime.DaysInMonth(year, month));
//string fourthWeekEnd = DateTime.Now.ToString("MM/dd/yyyy");
List <GrossWrittenPremiumReportModels> ListGrossWrittenPremiumReport = new List <GrossWrittenPremiumReportModels>();
ListGrossWrittenPremiumReportModels _ListGrossWrittenPremiumReport = new ListGrossWrittenPremiumReportModels();
_ListGrossWrittenPremiumReport.ListGrossWrittenPremiumReportdata = new List <GrossWrittenPremiumReportModels>();
try
{
ListGrossWrittenPremiumReport = getGWPData(firstWeekStart, firstWeekEnd);
Debug.WriteLine("**************hdfhd***************");
Debug.WriteLine(ListGrossWrittenPremiumReport.Count());
Debug.WriteLine("**************hdfhd***************");
var report2 = getGWPData(secondWeekStart, secondWeekEnd);
var report3 = getGWPData(thirdWeekStart, thirdWeekEnd);
var report4 = getGWPData(fourthWeekStart, fourthWeekEnd);
var report5 = getGWPData(firstWeekStart, fourthWeekEnd);
List <BranchModel> branches = InsuranceContext.Query("select * from Branch").Select(x => new BranchModel
{
Id = x.Id,
BranchName = x.BranchName
}).ToList();
List <WeeklyGWPModel> weeklyGWPModels = new List <WeeklyGWPModel>();
branches.ForEach(x =>
{
WeeklyGWPModel model = new WeeklyGWPModel();
if (x.BranchName == "Online")
{
var count = ListGrossWrittenPremiumReport.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total = ListGrossWrittenPremiumReport.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count2 = report2.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total2 = report2.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count3 = report3.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total3 = report3.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count4 = report4.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total4 = report4.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count5 = report5.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total5 = report5.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
model.BranchName = x.BranchName;
model.FirstWeekCount = count;
model.FirstWeekValue = total;
model.SecondWeekCount = count2;
model.SecondWeekValue = total2;
model.ThirdWeekCount = count3;
model.ThirdWeekValue = total3;
model.FourWeekCount = count4;
model.FourWeekValue = total4;
model.TotalMonthCount = count5;
model.TotalMonthValue = total5;
weeklyGWPModels.Add(model);
}
else
{
var count = ListGrossWrittenPremiumReport.FindAll(p => p.BranchName == x.BranchName).Count();
var total = ListGrossWrittenPremiumReport.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count2 = report2.FindAll(p => p.BranchName == x.BranchName).Count();
var total2 = report2.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count3 = report3.FindAll(p => p.BranchName == x.BranchName).Count();
var total3 = report3.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count4 = report4.FindAll(p => p.BranchName == x.BranchName).Count();
var total4 = report4.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count5 = report5.FindAll(p => p.BranchName == x.BranchName).Count();
var total5 = report5.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
model.BranchName = x.BranchName;
model.FirstWeekCount = count;
model.FirstWeekValue = total;
model.SecondWeekCount = count2;
model.SecondWeekValue = total2;
model.ThirdWeekCount = count3;
model.ThirdWeekValue = total3;
model.FourWeekCount = count4;
model.FourWeekValue = total4;
model.TotalMonthCount = count5;
model.TotalMonthValue = total5;
weeklyGWPModels.Add(model);
}
});
GenerateExcel2(weeklyGWPModels);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
}
public void SendWeeklyGwpFile()
{
List <GrossWrittenPremiumReportModels> ListGrossWrittenPremiumReport = new List <GrossWrittenPremiumReportModels>();
ListGrossWrittenPremiumReportModels _ListGrossWrittenPremiumReport = new ListGrossWrittenPremiumReportModels();
_ListGrossWrittenPremiumReport.ListGrossWrittenPremiumReportdata = new List <GrossWrittenPremiumReportModels>();
var yesterdayDate = DateTime.Now.AddDays(-1);
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, '" + yesterdayDate.ToString("MM/dd/yyyy") + "', 101) order by VehicleDetail.Id desc ";
Debug.WriteLine("***********************");
Debug.WriteLine(yesterdayDate.ToString("MM/dd/yyyy"));
Debug.WriteLine("***********************");
try {
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();
List <BranchModel> obj = InsuranceContext.Query("select * from Branch where id != 6").Select(x => new BranchModel
{
Id = x.Id,
BranchName = x.BranchName
}).ToList();
List <ZinaraReportModel> reportModelsList = new List <ZinaraReportModel>();
obj.ForEach(x =>
{
ZinaraReportModel model = new ZinaraReportModel();
var count = ListGrossWrittenPremiumReport.FindAll(p => p.BranchName == x.BranchName).Count();
var total = ListGrossWrittenPremiumReport.Where(p => p.BranchName == x.BranchName).Sum(item => item.Zinara_License_Fee);
// var zinaraAmount = total.Sum(item => item.Zinara_License_Fee);
model.BranchName = x.BranchName;
model.count = count;
model.ZinaraAmount = total;
reportModelsList.Add(model);
});
GenerateExcel(reportModelsList);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
}
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);
}
// GET: ClaimAdjustment
public ActionResult Index(int?id)
{
// string PolicyNumber, int? claimnumber
var otherserviceprovider = 0.00m;
var Assessorsprovider = 0.00m;
var Towingprovider = 0.00m;
var Valuersprovider = 0.00m;
var Repairersprovider = 0.00m;
var Lawyersprovider = 0.00m;
var Medicalprovider = 0.00m;
var ePaymentDetail = from ePayeeBankDetails e in Enum.GetValues(typeof(ePayeeBankDetails))
select new
{
ID = (int)e,
Name = e.ToString()
};
//for getting repairs providers value
var query = "select ClaimRegistrationId, ProviderType,ServiceProviderName, ServiceProviderFee from ClaimRegistrationProviderDetial ";
query += " join ServiceProviderType on ClaimRegistrationProviderDetial.ServiceProviderTypeId = ServiceProviderType.Id ";
query += " join ServiceProvider on ClaimRegistrationProviderDetial.ServiceProviderId = ServiceProvider.Id where ClaimRegistrationId =" + id + "and IsSaved=1";
List <ServiceProviderModel> list = InsuranceContext.Query(query).Select(c => new ServiceProviderModel
{
ServiceProviderType = c.ProviderType,
ServiceProviderName = c.ServiceProviderName,
ServiceProviderFees = c.ServiceProviderFee,
ClaimRegistrationId = c.ClaimRegistrationId
}).ToList();
//ViewBag.Providerslist = list;
//for (int i=0; i< list.Count;i++)
//{
foreach (var Providers in list)
{
if (Providers.ServiceProviderType == "Repairers")
{
Repairersprovider = Providers.ServiceProviderFees;
}
if (Providers.ServiceProviderType == "Assessors")
{
Assessorsprovider = Providers.ServiceProviderFees;
}
if (Providers.ServiceProviderType == "Towing")
{
Towingprovider = Providers.ServiceProviderFees;
}
if (Providers.ServiceProviderType == "Valuers")
{
Valuersprovider = Providers.ServiceProviderFees;
}
if (Providers.ServiceProviderType == "Lawyers")
{
Lawyersprovider = Providers.ServiceProviderFees;
}
if (Providers.ServiceProviderType == "Medical")
{
Medicalprovider = Providers.ServiceProviderFees;
}
}
otherserviceprovider = Assessorsprovider + Towingprovider + Valuersprovider + Lawyersprovider + Medicalprovider;
//}
ViewBag.ePaymentDetailData = new SelectList(ePaymentDetail, "ID", "Name");
if (id != 0 && id != null)
{
var model = new ClaimAdjustmentModel();
var data = InsuranceContext.ClaimRegistrations.Single(where : $"Id= '{id}'");
if (data != null && data.Count() > 0)
{
var policy = InsuranceContext.PolicyDetails.Single(where : $"PolicyNumber='{data.PolicyNumber}'");
var summery = InsuranceContext.SummaryDetails.Single(where : $"CustomerId = '{policy.CustomerId}'");
var custmo = InsuranceContext.Customers.Single(where : $"Id = '{policy.CustomerId}'");
model.EstimatedLoss = data.EstimatedValueOfLoss;
model.ClaimNumber = Convert.ToInt32(data.ClaimNumber);
model.PolicyNumber = data.PolicyNumber;
model.FirstName = custmo.FirstName;
model.LastName = custmo.LastName;
model.TotalSuminsure = Convert.ToDecimal(summery.TotalPremium);
model.PolicyholderName = custmo.FirstName + " " + custmo.LastName;
model.PayeeName = data.ClaimantName;
//model.AmountToPay = data.TotalProviderFees;
//model.FinalAmountToPaid = data.TotalProviderFees;
//model.FinalAmountToPaid = Repairersprovider+otherserviceprovider;
model.FinalAmountToPaid = 0.00m;
model.RepairCost = Repairersprovider;
model.ServiceProviderCost = otherserviceprovider;
model.ClaimRegisterationId = data.Id;
return(View(model));
}
}
return(View());
}
