public CustomerVsSalesExecutiveList GetCustomerVsSalesExecutives(int OrganizationId) { using (IDbConnection connection = OpenConnection(dataConnection)) { CustomerVsSalesExecutiveList model = new CustomerVsSalesExecutiveList(); //string sql= @"with merged (CustomerId, EffectiveDate, EmployeeId) AS //( // select CV.CustomerId , CV.EffectiveDate, CV.EmployeeId from Customer C join CustomerVsSalesExecutive CV on C.CustomerId=CV.CustomerId // where CV.EffectiveDate=(select Max(EffectiveDate) from CustomerVsSalesExecutive CVN where CVN.CustomerId=C.CustomerId) // ) // //select C.CustomerId , C.CustomerName, CV.EffectiveDate, CV.EmployeeId from Customer C left join merged CV on C.CustomerId=CV.CustomerId //Where OrganizationId=@OrganizationId"; string sql = @"with merged (CustomerId, EffectiveDate, EmployeeId,CustomerCategory) AS ( select CV.CustomerId , CV.EffectiveDate, CV.EmployeeId,CuC.CusCategoryName from Customer C join CustomerVsSalesExecutive CV on C.CustomerId=CV.CustomerId inner join CustomerCategory CuC on CuC.CusCategoryId=C.CategoryId where CV.EffectiveDate=(select Max(EffectiveDate) from CustomerVsSalesExecutive CVN where CVN.CustomerId=C.CustomerId) ) select C.CustomerId , C.CustomerName, CV.EffectiveDate, CV.EmployeeId ,CuC.CusCategoryName,CONCAT([DoorNo],'/',[Street],'/',[State],'/',[CountryName])as CustomerAddress from Customer C left join merged CV on C.CustomerId=CV.CustomerId inner join Country co on co.CountryId=c.Country inner join CustomerCategory CuC on CuC.CusCategoryId=C.CategoryId WHERE C.isActive=1"; model.CustomerVsSalesExecutives = connection.Query <CustomerVsSalesExecutive>(sql, new { OrganizationId = OrganizationId }).ToList <CustomerVsSalesExecutive>(); return(model); } }
public ActionResult Save(CustomerVsSalesExecutiveList model) { foreach (CustomerVsSalesExecutive item in model.CustomerVsSalesExecutives) { item.CreatedDate=System.DateTime.Now; item.CreatedBy=UserID.ToString(); } var rtn = new CustomerVsSalesExecutivesRepository().InsertCustomerSalesExecutive(model.CustomerVsSalesExecutives); FillEmployee(); TempData["Success"] = "Added Successfully!"; CustomerVsSalesExecutive CustomerVsSales = new CustomerVsSalesExecutive(); // OpeningStock.OpeningStockItem = new List<OpeningStockItem>(); //OpeningStock.OpeningStockItem.Add(new OpeningStockItem()); return RedirectToAction("Create"); }