// // GET: /ClientAccount/ public ActionResult Index(int id) { SemplestEntities dbcontext = new SemplestEntities(); var viewModel = from u in dbcontext.Users join c in dbcontext.Customers on u.CustomerFK equals c.CustomerPK join caa in dbcontext.CustomerAddressAssociations on c.CustomerPK equals caa.CustomerFK join a in dbcontext.Addresses on caa.AddressFK equals a.AddressPK join sc in dbcontext.StateCodes on a.StateAbbrFK equals sc.StateAbbrPK join at in dbcontext.AddressTypes on caa.AddressTypeFK equals at.AddressTypePK join cpa in dbcontext.CustomerPhoneAssociations on c.CustomerPK equals cpa.CustomerFK join p in dbcontext.Phones on cpa.PhoneFK equals p.PhonePK join b in dbcontext.BillTypes on c.BillTypeFK equals b.BillTypePK where (c.CustomerPK==id) select new AccountServiceModel { AccountNumber = c.CustomerPK , Customer = c.Name , FirstName = u.FirstName , LastName = u.LastName , Address1 = a.Address1 , Address2 = a.Address2 , City = a.City , State = sc.StateAbbr , Zip = a.ZipCode , Phone = p.Phone1 , Email = u.Email , BillType = b.BillType1 }; var viewModel2 = from e in dbcontext.Employees join eca in dbcontext.EmployeeCustomerAssociations on e.EmployeePK equals eca.EmployeeFK join et in dbcontext.EmployeeTypes on e.EmployeeTypeFK equals et.EmployeeTypeID join u in dbcontext.Users on e.UsersFK equals u.UserPK where (eca.CustomerFK==id) select new EmployeeCustomerAssociaitionModel { AccountNumber = eca.CustomerFK, EmployeeType = et.EmployeeType1, employeePK = e.EmployeePK, FirstName = u.FirstName, LastName = u.LastName, EmployeeUserPK = u.UserPK }; AccountServiceWithEmployeeModel x = new AccountServiceWithEmployeeModel(); x.AccountServiceModel = viewModel.Single(c=>c.AccountNumber==id); x.EmployeeCustomerAssociaitionModel = viewModel2; //from u in dbcontext.Users //join c in dbcontext.Customers on u.CustomerFK equals c.CustomerPK //join eca in dbcontext.EmployeeCustomerAssociations on c.CustomerPK equals eca.CustomerFK //join e in dbcontext.Employees on u.UserPK equals e.UsersFK //join et in dbcontext.EmployeeTypes on e.EmployeeTypeFK equals et.EmployeeTypeID //where (c.CustomerPK == id) //select new EmployeeCustomerAssociaitionModel //{ // AccountNumber = c.CustomerPK, // EmployeeType = et.EmployeeType1, // employeePK = e.EmployeePK, // FirstName = u.FirstName, // LastName = u.LastName, // EmployeeUserPK = u.UserPK //}; //return View(viewModel.Single(c=>c.AccountNumber==id)); return View(x); }
// // GET: /ClientAccount/ public ActionResult Index(int id) { SemplestModel.Semplest dbcontext = new SemplestModel.Semplest(); var viewModel = from u in dbcontext.Users join c in dbcontext.Customers on u.CustomerFK equals c.CustomerPK join caa in dbcontext.CustomerAddressAssociations on c.CustomerPK equals caa.CustomerFK join a in dbcontext.Addresses on caa.AddressFK equals a.AddressPK join sc in dbcontext.StateCodes on a.StateAbbrFK equals sc.StateAbbrPK join at in dbcontext.AddressTypes on caa.AddressTypeFK equals at.AddressTypePK join cpa in dbcontext.CustomerPhoneAssociations on c.CustomerPK equals cpa.CustomerFK join p in dbcontext.Phones on cpa.PhoneFK equals p.PhonePK join b in dbcontext.BillTypes on c.BillTypeFK equals b.BillTypePK where (c.CustomerPK == id) select new AccountServiceModel { AccountNumber = c.CustomerPK, Customer = c.Name, FirstName = u.FirstName, LastName = u.LastName, Address1 = a.Address1, Address2 = a.Address2, City = a.City, State = sc.StateAbbr, Zip = a.ZipCode, Phone = p.Phone1, Email = u.Email, BillType = b.BillType1, isActive = u.IsActive }; //var p = dbcontext.Customers.Where(key => key.CustomerPK == id).Where var viewModel3 = from c in dbcontext.Customers join pg in dbcontext.ProductGroups on c.CustomerPK equals pg.CustomerFK join p in dbcontext.Promotions on pg.ProductGroupPK equals p.ProductGroupFK where(c.CustomerPK == id) where(p.IsLaunched == true) where (p.IsPaused == false) where (p.IsCompleted == false) select c; var viewModel4 = from c in dbcontext.Customers join pg in dbcontext.ProductGroups on c.CustomerPK equals pg.CustomerFK join p in dbcontext.Promotions on pg.ProductGroupPK equals p.ProductGroupFK where(c.CustomerPK == id) where(p.IsLaunched == true) where (p.IsPaused == true) where (p.IsCompleted == false) select c; var viewModel2 = from e in dbcontext.Employees join eca in dbcontext.EmployeeCustomerAssociations on e.EmployeePK equals eca.EmployeeFK join et in dbcontext.EmployeeTypes on e.EmployeeTypeFK equals et.EmployeeTypeID join u in dbcontext.Users on e.UsersFK equals u.UserPK where (eca.CustomerFK == id) select new EmployeeCustomerAssociaitionModel { AccountNumber = eca.CustomerFK, EmployeeType = et.EmployeeType1, employeePK = e.EmployeePK, FirstName = u.FirstName, LastName = u.LastName, EmployeeUserPK = u.UserPK }; AccountServiceWithEmployeeModel x = new AccountServiceWithEmployeeModel(); var parent = from ch in dbcontext.CustomerHierarchies where (ch.CustomerFK == id) select ch; if (parent.FirstOrDefault().CustomerFK == parent.FirstOrDefault().CustomerParentFK) //self { x.ParentName = "Self"; } else if (parent.FirstOrDefault().CustomerParentFK == null) //parent { x.ParentName = "This is a parent"; } else { //parent var findparent = from p in dbcontext.Customers where p.CustomerPK == (parent.FirstOrDefault().CustomerParentFK) select p; x.ParentName = findparent.FirstOrDefault().Name; } x.AccountServiceModel = viewModel.Single(c => c.AccountNumber == id); x.EmployeeCustomerAssociaitionModel = viewModel2; x.AccountServiceModel.PromotionPauseCount = viewModel3.Count(); x.AccountServiceModel.PromotionRestartCount = viewModel4.Count(); //from u in dbcontext.Users //join c in dbcontext.Customers on u.CustomerFK equals c.CustomerPK //join eca in dbcontext.EmployeeCustomerAssociations on c.CustomerPK equals eca.CustomerFK //join e in dbcontext.Employees on u.UserPK equals e.UsersFK //join et in dbcontext.EmployeeTypes on e.EmployeeTypeFK equals et.EmployeeTypeID //where (c.CustomerPK == id) //select new EmployeeCustomerAssociaitionModel //{ // AccountNumber = c.CustomerPK, // EmployeeType = et.EmployeeType1, // employeePK = e.EmployeePK, // FirstName = u.FirstName, // LastName = u.LastName, // EmployeeUserPK = u.UserPK //}; //return View(viewModel.Single(c=>c.AccountNumber==id)); return View(x); }