//public Equipment GetByNameAndDescription(string description, string name)
//{
// var sql = Sql.SqlQueryCach["Equipment.ByNameAndDescription"];
// var result = conn.Query<Equipment>(sql, new {name, description});
// return result.FirstOrDefault();
//}
public async Task <EquipmentWitcAct> Add(EquipmentWitcAct input)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
var sqlc = Sql.SqlQueryCach["Equipment.CountByName"];
var count = await conn.QueryAsync <int>(sqlc, new { name = input.Name });
if (count.FirstOrDefault() > 0)
{
throw new Exception("Equipment name already exist");
}
var sql = Sql.SqlQueryCach["Equipment.Add"];
var id = await conn.QueryAsync <int>(sql,
new { name = input.Name, description = input.Description, category_id = input.CategoryId });
//var result = GetByNameAndDescription(equipment.Description, equipment.Name);
input.Id = id.First();
if (input.EquipmentActCategoryId != null)
{
var sqlRAct = new ActCategoriesRepository();
await sqlRAct.AddEquipmentToAct(input.EquipmentActCategoryId.Value, input.Id);
sqlRAct.Dispose();
}
return(input);
}
}
public async Task <EquipmentPaging> GetAll(int skip, int limit)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
var sql = Sql.SqlQueryCach["Equipment.All"];
var result = (await conn.QueryAsync <EquipmentWitcAct, EquipmentCategory, EquipmentWitcAct>(
sql,
(equipment, category) =>
{
equipment.Category = category;
return(equipment);
}, new { skip = skip, limit = limit })).ToList();
var sqlc = Sql.SqlQueryCach["Equipment.CountAll"];
var count = (await conn.QueryAsync <int>(sqlc)).FirstOrDefault();
var sqlRAct = new ActCategoriesRepository();
foreach (var item in result)
{
var act = await sqlRAct.GetByEquipmentId(item.Id);
if (act.Count > 0 && act.FirstOrDefault().Id != 0)
{
item.EquipmentActCategoryDescription = act.FirstOrDefault().Description;
item.EquipmentActCategoryName = act.FirstOrDefault().Name;
item.EquipmentActCategoryId = act.FirstOrDefault().Id;
}
}
var output = new EquipmentPaging
{
Data = result.ToArray(),
Total = count
};
return(output);
}
}
public async Task <EquipmentWitcAct> Update(EquipmentWitcAct input)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
var sql = Sql.SqlQueryCach["Equipment.Update"];
await conn.ExecuteAsync(sql,
new
{
name = input.Name,
description = input.Description,
categoryId = input.CategoryId,
id = input.Id
});
if (input.EquipmentActCategoryId != null)
{
var sqlRAct = new ActCategoriesRepository();
var result = await sqlRAct.GetByEquipmentId(input.Id);
if (result.Count > 0)
{
await sqlRAct.UpdateEquipmentToAct(input.EquipmentActCategoryId.Value, input.Id);
}
else
{
await sqlRAct.AddEquipmentToAct(input.EquipmentActCategoryId.Value, input.Id);
}
result = await sqlRAct.GetByEquipmentId(input.Id);
input.EquipmentActCategoryDescription = result.FirstOrDefault().Description;
input.EquipmentActCategoryName = result.FirstOrDefault().Name;
input.EquipmentActCategoryId = result.FirstOrDefault().Id;
}
return(input);
}
}
public async Task <EquipmentPaging> GetByCategory(EquipmentCategory cat, int skip = 0,
int limit = Int32.MaxValue, string filter = null)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
var sql = Sql.SqlQueryCach["Equipment.ByCategoryId"];
var result = await conn.QueryAsync <EquipmentWitcAct, EquipmentCategory, EquipmentWitcAct>(
sql,
(equipment, category) =>
{
equipment.Category = category;
return(equipment);
}, new { category_id = cat.Id, skip = skip, limit = limit });
var sqlc = Sql.SqlQueryCach["Equipment.CountByCategoryId"];
var count = conn.ExecuteScalar <int>(sqlc, new
{
category_id = cat.Id
});
TaskCommon.FilterBody[] filters;
var filterOutput = new List <EquipmentWitcAct>();
if (filter != null)
{
filters = JsonConvert.DeserializeObject <TaskCommon.FilterBody[]>(filter);
if (filters.Length > 0)
{
foreach (var value in result)
{
foreach (var item in filters)
{
switch (item.Filter)
{
case "Name":
if (value.Name.ToLower().Contains(item.Value.ToLower()))
{
filterOutput.Add(value);
}
break;
}
}
}
}
}
if (filter != null)
{
result = filterOutput;
count = filterOutput.Count;
}
//var newFuckingResult = new List<EquipmentWitcAct>();
var sqlRAct = new ActCategoriesRepository();
foreach (var item in result.ToArray())
{
var actCategory = await sqlRAct.GetByEquipmentId(item.Id);
if (actCategory.ToArray().Length > 0)
{
item.EquipmentActCategoryDescription = actCategory.FirstOrDefault().Description;
item.EquipmentActCategoryName = actCategory.FirstOrDefault().Name;
item.EquipmentActCategoryId = actCategory.FirstOrDefault().Id;
}
}
var output = new EquipmentPaging
{
Data = result.ToArray(),
Total = count
};
return(output);
}
}
