// GET: Brokers
public async Task <IActionResult> Index(int?categoryId, string SearchString, int?brokerId)
{
ViewBag.Categories = _context.Categories.ToList();
if (brokerId != null)
{
var brokers = _context.Set <Brokers>().FromSqlRaw(@"select bs.* from Brokers bs
where not exists
((
Select c.Id From Categories c Inner Join BrokersCategories bc
On c.Id = bc.CategoryId
where bc.BrokerId = {0}
)except
(
Select c.Id From Categories c Inner Join BrokersCategories bc
On c.Id = bc.CategoryId
where bc.BrokerId = bs.Id
))", brokerId).ToList();
return(View(brokers));
}
if (SearchString != null)
{
var brokers = _context.Brokers.Where(b => b.Name.Contains(SearchString) || b.Surname.Contains(SearchString) || SearchString.Contains(b.Name) && SearchString.Contains(b.Surname)).ToList();
return(View(brokers));
}
if (categoryId != null)
{
var brokers = _context.Set <Brokers>().FromSqlRaw(@"SELECT b.* FROM Brokers b INNER JOIN BrokersCategories bc ON bc.BrokerId = b.Id
WHERE bc.CategoryId = {0}", categoryId).ToList();
return(View(brokers));
}
return(View(await _context.Brokers.ToListAsync()));
}
// GET: Clients
public async Task <IActionResult> Index(string searchString, int?broker, int?categories, int?clientId)
{
ViewBag.Categories = 1;
ViewBag.Brokers = _context.Brokers.ToList();
if (clientId != null)
{
var clients = _context.Set <Clients>().FromSqlRaw(@"select * from Clients cl
where not exists
((
Select c.Id From Categories c Inner Join Types t
On c.Id = t.CategoryId Inner Join Documents d On t.Id = d.TypeId
where d.ClientId = {0}
)except
(
Select c.Id From Categories c Inner Join Types t
On c.Id = t.CategoryId Inner Join Documents d On t.Id = d.TypeId
where d.ClientId = cl.Id))", clientId).ToList();
return(View(clients));
}
if (categories != null)
{
var clients = _context.Set <Clients>().FromSqlRaw(@"Select c.* from Clients c where
(Select count(distinct t.CategoryId) From Documents d Inner Join Types t On d.TypeId =t.Id where d.ClientId = c.Id)
=
(select count(cat.Id) from Categories cat)").ToList();
return(View(clients));
}
if (searchString != null)
{
var clients = _context.Clients.Where(b => b.Name.Contains(searchString) || b.Surname.Contains(searchString) || searchString.Contains(b.Name) && searchString.Contains(b.Surname)).ToList();
return(View(clients));
}
if (broker != null)
{
var clients = _context.Set <Clients>().FromSqlRaw(@"SELECT c.* FROM Clients c INNER JOIN Documents d ON c.Id = d.ClientId
INNER JOIN Brokers b ON d.BrokerId = b.Id WHERE b.Id = {0}", broker);
return(View(clients));
}
return(View(await _context.Clients.ToListAsync()));
}
// GET: Types
public async Task <IActionResult> Index(int?id, string?category)
{
if (id == null)
{
return(RedirectToAction("Categories", "Index"));
}
ViewBag.CategoryId = id;
ViewBag.CategoryName = category;
var typesByCategory = _context.Set <Types>().FromSqlRaw(@"SELECT t.* FROM Types t INNER JOIN Categories c ON t.CategoryId = c.Id WHERE c.Category LIKE {0}", category).Include(b => b.Category);
return(View(await typesByCategory.ToListAsync()));
//return View(await _context.Types.ToListAsync());
}
public BaseRepository()
{
_tcontext = new InsuranceContext();
_TdbSet = _tcontext.Set <TEntity>();
_VdbSet = _tcontext.Set <VEntity>();
}
// GET: Documents
public async Task <IActionResult> Index(int?brokerId, int?clientId, string?error, int?typeId, int?categoryId, string?searchString, string?brokerName, string?clientName)
{
ViewBag.Categories = _context.Categories.ToList();
if (searchString != null)
{
var documents = _context.Documents.Where(b => b.Number.Contains(searchString)).Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(documents));
}
if (categoryId != null)
{
var documents = _context.Set <Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Types t ON d.TypeId = t.Id
INNER JOIN Categories c ON t.CategoryId = c.Id
WHERE c.Id = {0}", categoryId)
.Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(documents));
}
if (brokerName != null || clientName != null)
{
if (brokerName != null && clientName != null)
{
if (brokerName.Split(new char[] { ' ' }).Length > 1 && clientName.Split(new char[] { ' ' }).Length > 1)
{
var documents = _context.Set <Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Brokers b ON d.BrokerId = b.Id
INNER JOIN Clients c ON d.ClientId = c.Id
WHERE (b.Name LIKE {0} AND b.Surname LIKE {1} OR b.Name LIKE {1} AND b.Surname LIKE {0})
AND (c.Name LIKE {2} AND c.Surname LIKE {3} OR c.Name LIKE {3} AND c.Surname LIKE {2})",
brokerName.Split(new char[] { ' ' })[0], brokerName.Split(new char[] { ' ' })[1], clientName.Split(new char[] { ' ' })[0], clientName.Split(new char[] { ' ' })[1])
.Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(documents));
}
else if (brokerName.Split(new char[] { ' ' }).Length > 1)
{
var documents = _context.Set <Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Brokers b ON d.BrokerId = b.Id
INNER JOIN Clients c ON d.ClientId = c.Id
WHERE (b.Name LIKE {0} AND b.Surname LIKE {1} OR b.Name LIKE {1} AND b.Surname LIKE {0})
AND (c.Name LIKE {2} OR c.Surname LIKE {2} )",
brokerName.Split(new char[] { ' ' })[0], brokerName.Split(new char[] { ' ' })[1], clientName)
.Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(documents));
}
else if (clientName.Split(new char[] { ' ' }).Length > 1)
{
var documents = _context.Set <Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Brokers b ON d.BrokerId = b.Id
INNER JOIN Clients c ON d.ClientId = c.Id
WHERE (b.Name LIKE {0} OR b.Surname LIKE {0})
AND (c.Name LIKE {1} AND c.Surname LIKE {2} OR c.Name LIKE {2} AND c.Surname LIKE {1})",
brokerName, clientName.Split(new char[] { ' ' })[0], clientName.Split(new char[] { ' ' })[1])
.Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(documents));
}
else
{
var documents = _context.Set <Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Brokers b ON d.BrokerId = b.Id
INNER JOIN Clients c ON d.ClientId = c.Id
WHERE (b.Name LIKE {0} OR b.Surname LIKE {0})
AND (c.Name LIKE {1} OR c.Surname LIKE {1} )",
brokerName, clientName)
.Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(documents));
}
}
else if (clientName == null)
{
if (brokerName.Split(new char[] { ' ' }).Length > 1)
{
var documents = _context.Set <Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Brokers b ON d.BrokerId = b.Id
WHERE b.Name LIKE {0} AND b.Surname LIKE {1} OR b.Name LIKE {1} AND b.Surname LIKE {0}", brokerName.Split(new char[] { ' ' })[0], brokerName.Split(new char[] { ' ' })[1]).Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(documents));
}
else
{
var documents = _context.Set <Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Brokers b ON d.BrokerId = b.Id
WHERE b.Name LIKE {0} OR b.Surname LIKE {0}", brokerName).Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(documents));
}
}
else
{
if (clientName.Split(new char[] { ' ' }).Length > 1)
{
var documents = _context.Set <Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Clients c ON d.ClientId = c.Id
WHERE c.Name LIKE {0} AND c.Surname LIKE {1} OR c.Name LIKE {1} AND c.Surname LIKE {0} ", clientName.Split(new char[] { ' ' })[0], clientName.Split(new char[] { ' ' })[1]).Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(documents));
}
else
{
var documents = _context.Set <Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Clients c ON d.ClientId = c.Id
WHERE c.Name LIKE {0} OR c.Surname LIKE {0}", clientName).Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(documents));
}
}
}
if (brokerId > 0)
{
brokerName = _context.Brokers.Find(brokerId).FullName;
ViewBag.ClientDocId = -1;
ViewBag.TypeDocId = -1;
ViewBag.BrokerDocId = brokerId;
var brokersDocuments = _context.Documents.Where(b => b.BrokerId == brokerId).Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
// var brokersDocuments = _context.Set<Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Brokers b ON d.BrokerId = b.Id WHERE b.FullName LIKE {0}", brokerName)
// .Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(brokersDocuments));
}
else if (clientId > 0)
{
clientName = _context.Clients.Find(clientId).FullName;
ViewBag.BrokerDocId = -1;
ViewBag.TypeDocId = -1;
ViewBag.ClientDocId = clientId;
var clientsDocuments = _context.Documents.Where(b => b.ClientId == clientId).Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
//var clientsDocuments = _context.Set<Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Clients c ON d.ClientId = c.Id WHERE c.FullName LIKE {0}", clientName)
// .Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(clientsDocuments));
}
else if (typeId > 0)
{
string typeName = _context.Types.Find(typeId).Type;
ViewBag.BrokerDocId = -1;
ViewBag.TypeDocId = typeId;
ViewBag.ClientDocId = -1;
var typesDocuments = _context.Documents.Where(b => b.TypeId == typeId).Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
//var typesDocuments = _context.Set<Documents>().FromSqlRaw(@"SELECT d.* FROM Documents d INNER JOIN Types t ON d.TypeId = t.Id WHERE t.Type LIKE {0}", typeName)
// .Include(b => b.Client).Include(b => b.Broker).Include(b => b.Type).ToList();
return(View(typesDocuments));
}
else
{
ViewBag.ErrorMes = error;
ViewBag.BrokerDocId = -1;
ViewBag.ClientDocId = -1;
ViewBag.TypeDocId = -1;
var insuranceContext = _context.Documents.Include(d => d.Broker).Include(d => d.Client).Include(d => d.Type);
return(View(await insuranceContext.ToListAsync()));
}
}