//--------------------------------------------------------------------------------------------------------------------------------
public IQueryable <TblLifeInsurance> GetFilterList(FilterItemsViewModel filter)
{
//کاربر بر اساس نمایندگی جستجو میکند
if (filter.AgentID != null && filter.AgentID != 0.ToString())
{
//بررسی اینکه آیا بر اساس تاریخ شروع بیمه نامه هم فیلتر اعمال کرده یا نه
//کاربر بر اساس تاریخ هم فیلتر کرده
if (filter.FromDate_Start != null || filter.ToDate_Start != null)
{
if (filter.FromDate_Start != null && filter.ToDate_Start == null)
{
try
{
var date = filter.FromDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.UserID == filter.AgentID && a.Date_Start_Miladi >= date).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
catch {
throw new Exception();
}
}
else if (filter.FromDate_Start == null && filter.ToDate_Start != null)
{
try
{
var date = filter.ToDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.UserID == filter.AgentID && a.Date_Start_Miladi <= date).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
catch
{
throw new Exception();
}
}
else
{
try
{
var fromDate = filter.FromDate_Start.DateToMiladi();
var toDate = filter.ToDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.UserID == filter.AgentID && a.Date_Start_Miladi >= fromDate && a.Date_Start_Miladi <= toDate).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
catch
{
throw new Exception();
}
}
}
//کاربر تاریخی وارد نکرده
else
{
var result = _db.TblLifeInsurance.Where(a => a.UserID == filter.AgentID).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
}
//کاربر بر اساس زیرگروه جستجو میکند
else if (filter.SupervisiorID != 0 && filter.SupervisiorID != null)
{
//بررسی اینکه آیا بر اساس تاریخ شروع بیمه نامه هم فیلتر اعمال کرده یا نه
//کاربر بر اساس تاریخ هم فیلتر کرده
if (filter.FromDate_Start != null || filter.ToDate_Start != null)
{
if (filter.FromDate_Start != null && filter.ToDate_Start == null)
{
try
{
var date = filter.FromDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.SupervisorID == filter.SupervisiorID && a.Date_Start_Miladi >= date).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
catch
{
throw new Exception();
}
}
else if (filter.FromDate_Start == null && filter.ToDate_Start != null)
{
try
{
var date = filter.ToDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.SupervisorID == filter.SupervisiorID && a.Date_Start_Miladi <= date).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
catch
{
throw new Exception();
}
}
else
{
try
{
var fromDate = filter.FromDate_Start.DateToMiladi();
var toDate = filter.ToDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.SupervisorID == filter.SupervisiorID && a.Date_Start_Miladi >= fromDate && a.Date_Start_Miladi <= toDate).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
catch
{
throw new Exception();
}
}
}
//کاربر تاریخی وارد نکرده
else
{
var result = _db.TblLifeInsurance.Where(a => a.SupervisorID == filter.SupervisiorID).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
}
//کاربر بر اساس دفاتر پیشخوان جستجو میکند
else if (filter.PishkhanID != 0 && filter.PishkhanID != null)
{
//بررسی اینکه آیا بر اساس تاریخ شروع بیمه نامه هم فیلتر اعمال کرده یا نه
//کاربر بر اساس تاریخ هم فیلتر کرده
if (filter.FromDate_Start != null || filter.ToDate_Start != null)
{
if (filter.FromDate_Start != null && filter.ToDate_Start == null)
{
try
{
var date = filter.FromDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.PishkhanID == filter.PishkhanID && a.Date_Start_Miladi >= date).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
catch
{
throw new Exception();
}
}
else if (filter.FromDate_Start == null && filter.ToDate_Start != null)
{
try
{
var date = filter.ToDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.PishkhanID == filter.PishkhanID && a.Date_Start_Miladi <= date).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
catch
{
throw new Exception();
}
}
else
{
try
{
var fromDate = filter.FromDate_Start.DateToMiladi();
var toDate = filter.ToDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.PishkhanID == filter.PishkhanID && a.Date_Start_Miladi >= fromDate && a.Date_Start_Miladi <= toDate).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
catch
{
throw new Exception();
}
}
}
//کاربر تاریخی وارد نکرده
else
{
var result = _db.TblLifeInsurance.Where(a => a.PishkhanID == filter.PishkhanID).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
}
//کاربر بر اساس شماره بیمه نامه جستجو میکند
else if (filter.InsuranceNumber != null)
{
var result = _db.TblLifeInsurance.Where(a => a.InsuranceNumber_Number == filter.InsuranceNumber).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
//کاربر بر اساس شماره سریال جستجو میکند
else if (filter.InsuranceSerial != null)
{
var result = _db.TblLifeInsurance.Where(a => a.Serial == filter.InsuranceSerial).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
//کاربر بر اساس تاریخ صدور جستجو میکند
else if (filter.FromDate_Soodoor != null || filter.ToDate_Soodoor != null)
{
if (filter.FromDate_Soodoor != null && filter.ToDate_Soodoor == null)
{
var date = filter.FromDate_Soodoor.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.Date_Soodoor_Miladi >= date).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
else if (filter.FromDate_Soodoor == null && filter.ToDate_Soodoor != null)
{
var date = filter.ToDate_Soodoor.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.Date_Soodoor_Miladi <= date).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
else
{
var datefrom = filter.FromDate_Soodoor.DateToMiladi();
var dateto = filter.ToDate_Soodoor.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.Date_Soodoor_Miladi >= datefrom && a.Date_Soodoor_Miladi <= dateto).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
}
//کاربر بر اساس تاریخ شروع جستجو میکند
else if (filter.FromDate_Start != null || filter.ToDate_Start != null)
{
if (filter.FromDate_Start != null && filter.ToDate_Start == null)
{
var date = filter.FromDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.Date_Start_Miladi >= date).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
else if (filter.FromDate_Start == null && filter.ToDate_Start != null)
{
var date = filter.ToDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.Date_Start_Miladi <= date).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
else
{
var datefrom = filter.FromDate_Start.DateToMiladi();
var dateto = filter.ToDate_Start.DateToMiladi();
var result = _db.TblLifeInsurance.Where(a => a.Date_Start_Miladi >= datefrom && a.Date_Start_Miladi <= dateto).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
}
//کاربر بر اساس نام بیمه گذار و بیمه شده جستجو میکند
else if (filter.BimegozarName != null || filter.BimeshodeName != null)
{
if (filter.BimegozarName != null && filter.BimeshodeName == null)
{
var result = _db.TblLifeInsurance.Where(a => a.Bimegozar_Name.Contains(filter.BimegozarName)).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
else if (filter.BimegozarName == null && filter.BimeshodeName != null)
{
var result = _db.TblLifeInsurance.Where(a => a.Bimeshode_Name.Contains(filter.BimeshodeName)).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
else
{
var result = _db.TblLifeInsurance.Where(a => a.Bimegozar_Name.Contains(filter.BimegozarName) && filter.BimeshodeName.Contains(filter.BimeshodeName)).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
}
//کاربر بر اساس شماره موبایل بیمه گذار یا بیمه شده جستجو میکند
else if (filter.BimegozarPhone != null || filter.BimeshodePhone != null)
{
if (filter.BimegozarPhone != null && filter.BimeshodePhone == null)
{
var result = _db.TblLifeInsurance.Where(a => a.Bimegozar_Phone == filter.BimegozarPhone).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
else if (filter.BimegozarPhone == null && filter.BimeshodePhone != null)
{
var result = _db.TblLifeInsurance.Where(a => a.Bimeshode_Phone == filter.BimeshodePhone).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
else
{
var result = _db.TblLifeInsurance.Where(a => a.Bimegozar_Phone == filter.BimegozarPhone && a.Bimeshode_Phone == filter.BimeshodePhone).OrderByDescending(o => o.InsuranceNumber_Number)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
}
else
{
return(null);
}
}
public async Task <IActionResult> Filter(ListInsuranceViewModel md, string ddlAgent, int?ddlSupervisior, int?ddlPishkhan)
{
ListInsuranceViewModel model = new ListInsuranceViewModel();
FilterItemsViewModel filter = new FilterItemsViewModel();
filter = md.FilterItemsVM;
filter.AgentID = ddlAgent;
filter.PishkhanID = ddlPishkhan;
filter.SupervisiorID = ddlSupervisior;
//کاربر هیچ اطلاعاتی برای فیلتر کردن وارد ننموده
if (filter.AgentID == "0" && filter.SupervisiorID == 0 && filter.PishkhanID == 0 && filter.InsuranceNumber == null && filter.BimegozarName == null && filter.BimegozarPhone == null && filter.BimeshodeName == null && filter.BimeshodePhone == null && filter.FromDate_Soodoor == null && filter.FromDate_Start == null && filter.InsuranceSerial == null && filter.ToDate_Soodoor == null && filter.ToDate_Start == null)
{
ViewBag.pm = "لطفا یک آیتم برای فیلتر انتخاب نمایید";
}
//ارسال اطلاعات به مادل برای جستجو در دیتابیس
else
{
ViewBag.pm = "";
var result = _service.TblLifeInsurance.GetInsuranceList(true, 0, 0, filter);
foreach (var item in result)
{
TabelItemsViewModel vm = new TabelItemsViewModel();
vm.Agent = item.TblUser.Name;
vm.BimegozarName = item.Bimegozar_Name;
vm.BimegozarPhone = item.Bimegozar_Phone;
vm.BimeshodeName = item.Bimeshode_Name;
vm.BimeshodePhon = item.Bimeshode_Phone;
vm.DateSoodoor = item.Date_Soodoor_Shamsi;
vm.DateStart = item.Date_Soodoor_Shamsi;
vm.ID = item.ID;
vm.InsuranceNumber = item.InsuranceNumber_Code + "/" + item.InsuranceNumber_CenterCode + "/" + item.InsuranceNumber_GarardadNumber + "/" + item.InsuranceNumber_Year + "/" + item.InsuranceNumber_Number;
vm.PaymentType = item.PaymentType;
vm.Price = item.Payment_Price;
vm.Status = item.Insurance_Status;
try
{
vm.Supervisior = item.TblSupervisior.Name;
}
catch { }
try
{
vm.Pishkhan = item.TblPishkhan.Name;
}
catch { }
model.TabelItemsVM.Add(vm);
}
}
//-----------------------------------------------------------------------------------------------------------
//return items
//Get Agent , pishkhan , supervisior List
var _agentList = _user.GetAgentListAsync();
var _pishkhanList = _service.TblPishkhan.GetAll();
var _supervisiorList = _service.TblSupervisior.GetAll();
var _countTotalRow = _service.TblLifeInsurance.GetTotalCount();
var agentList = await _agentList;
var pishkhanList = await _pishkhanList;
var supervisiorList = await _supervisiorList;
var countTotalRow = await _countTotalRow;
//Fill DropdownList ViewModel
foreach (var item in agentList)
{
AgentListViewModel vm = new AgentListViewModel();
vm.ID = item.Id;
vm.Name = item.Name;
model.AgentListVM.Add(vm);
}
foreach (var item in pishkhanList)
{
PishkhanListViewModel vm = new PishkhanListViewModel();
vm.ID = item.ID;
vm.Name = item.Name;
model.PishkhanListVM.Add(vm);
}
foreach (var item in supervisiorList)
{
SupervisiorListViewModel vm = new SupervisiorListViewModel();
vm.ID = item.ID;
vm.Name = item.Name;
model.SupervisiorListVM.Add(vm);
}
//Fill Tabel Items
model.Take = 0;
model.Skip = 0;
return(View("List", model));
}
public IQueryable <TblLifeInsurance> GetInsuranceList(bool Filter, int Skip, int Take, FilterItemsViewModel FilterItems)
{
//User Filter The Tabel
if (Filter)
{
var result = GetFilterList(FilterItems);
return(result);
}
//User Get All Table
else
{
var result = _db.TblLifeInsurance.OrderByDescending(o => o.InsuranceNumber_Number).Skip(Skip).Take(Take)
.Include(i => i.TblUser)
.Include(i => i.TblPishkhan)
.Include(i => i.TblSupervisior)
.AsQueryable();
return(result);
}
}
public MainWindow()
{
InitializeComponent();
DataContext = new FilterItemsViewModel();
}
