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");
}
