public List <Salary> GetSalariesIn(ATDbEntities atDbEntities, short month, short year, short monthEnd, short yearEnd, List <string> persons)
{
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
return(atDbEntities.Salaries.Where(p => p.Date >= startD && p.Date <= endD && persons.Contains(p.Employee.Trim())).ToList());
}
public List <GoodViewModel> GetGoodsByFilial(ATDbEntities atDbEntities, int filialId, short month, short year, short monthEnd, short yearEnd)
{
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
return(atDbEntities.Goods.Include("Brend")
.Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD)
.GroupBy(p => new { p.BrendId, p.Brend.Name })
.Select(n => new GoodViewModel
{
BrendId = n.Key.BrendId,
BrendName = n.Key.Name,
Price = n.Sum(c => c.Price),
PrimeCost = n.Sum(c => c.PrimeCost),
Sum = n.Sum(c => c.Sum)
}
)
.ToList());
//return atDbEntities.Goods.Include("Brend")
// .Where(p => p.FilialId == filialId && p.Month == month && p.Year == year)
// .GroupBy(p => new { p.BrendId, p.Brend.Name })
// .Select(n => new GoodViewModel
// {
// BrendId = n.Key.BrendId,
// BrendName = n.Key.Name,
// Price = n.Sum(c => c.Price),
// PrimeCost = n.Sum(c => c.PrimeCost),
// Sum = n.Sum(c => c.Sum)
// }
// )
// .ToList();
}
public List <GoodDetailViewModel> GetGoodsDetailsByBrend(ATDbEntities atDbEntities, int filialId, string brendName, short month, short year, short monthEnd, short yearEnd)
{
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
if (brendName == "Пакеты + Стаканчики")
{
return(atDbEntities.Goods.Include("Brend")
.Where(p => p.FilialId == filialId && (p.Brend.Name == "Shanghai (Пакеты)" || p.Brend.Name == "Comecotech(Пакеты, Стаканчики)" || p.Brend.Name == "MaxiPac") && p.Date >= startD && p.Date <= endD)
.GroupBy(p => new { p.Name, p.Capacity })
.Select(n => new GoodDetailViewModel
{
Name = n.Key.Name,
Capacity = n.Key.Capacity,
Amount = n.Sum(c => c.Amount),
Sum = n.Sum(c => c.Sum)
})
.OrderBy(p => p.Name).ThenBy(n => n.Capacity)
.ToList());
}
return(atDbEntities.Goods.Include("Brend")
.Where(p => p.FilialId == filialId && p.Brend.Name == brendName && p.Date >= startD && p.Date <= endD)
.GroupBy(p => new { p.Name, p.Capacity })
.Select(n => new GoodDetailViewModel
{
Name = n.Key.Name,
Capacity = n.Key.Capacity,
Amount = n.Sum(c => c.Amount),
Sum = n.Sum(c => c.Sum)
})
.OrderBy(p => p.Name).ThenBy(n => n.Capacity)
.ToList());
}
public List <BrendsPlanViewModel> GetBrendsPlansByFilial(ATDbEntities atDbEntities, int filialId, short month, short year, short monthEnd, short yearEnd)
{
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
//return atDbEntities.BrendsPlans.Include("Brend")
// .Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD)
// .OrderBy(p=>p.Brend.OrderByFlag)
// .GroupBy(p=>p.BrendId)
// .SelectMany(gr => gr)
// .ToList();
return(atDbEntities.BrendsPlans.Include("Brend")
.Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD)// && p.BrendId != 10 && p.BrendId != 11 && p.BrendId != 20)
.GroupBy(p => new { p.BrendId, p.Brend.Name, p.Brend.OrderByFlag })
.Select(n => new BrendsPlanViewModel
{
BrendId = n.Key.BrendId,
BrendName = n.Key.Name,
OrderByFlag = n.Key.OrderByFlag,
Sum = n.Sum(c => c.Sum)
}
)
.OrderBy(p => p.OrderByFlag)
.ToList());
}
public List <string> GetEmployees(ATDbEntities atDbEntities, string query)
{
return(atDbEntities.Salaries
.Where(p => p.Employee.Contains(query))
.Select(p => p.Employee).Distinct()
.Take(15)
.ToList());
}
public List <string> GetAutoNumbers(ATDbEntities atDbEntities, string query)
{
return(atDbEntities.Autos
.Where(p => p.Number.Contains(query))
.Select(p => p.Number).Distinct()
.Take(15)
.ToList());
}
public List <GoodViewModel> GetGoodsAERPVodovozByFilial(ATDbEntities atDbEntities, int filialId, short month, short year, short monthEnd, short yearEnd)
{
List <GoodViewModel> goodViewModelVodovoz = new List <GoodViewModel>();
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
var filialV = GetVodovozFilialId(filialId);
var filialData = atDbEntities.GoodsAquaERP.Where(p => p.FilialId == filialV && p.Date >= startD && p.Date <= endD).ToList();
if (filialData.Count == 0)
{
return(goodViewModelVodovoz);
}
var filialData19l = filialData.Where(p => p.Capacity == "19l");
goodViewModelVodovoz.Add(
new GoodViewModel
{
BrendId = 10,
BrendName = "Вода (OM, Familia, Populara)19л",
Price = filialData19l.Select(x => x.Price).Sum(),
PrimeCost = filialData19l.Select(x => x.PrimeCost).Sum(),
Sum = filialData19l.Select(c => c.Sum ?? 0).Sum()
});
var filialDataCuler = filialData.Where(p => p.BrendId == 20);
goodViewModelVodovoz.Add(
new GoodViewModel
{
BrendId = 20,
BrendName = "Кулера",
Price = filialDataCuler.Select(x => x.Price).Sum(),
PrimeCost = filialDataCuler.Select(x => x.PrimeCost).Sum(),
Sum = filialDataCuler.Select(c => c.Sum ?? 0).Sum()
});
goodViewModelVodovoz.Add(
new GoodViewModel
{
BrendId = 11,
BrendName = "Доп. водавоз",
Price = filialData.Select(x => x.Price).Sum(),
PrimeCost = filialData.Select(x => x.PrimeCost).Sum(),
Sum = filialData.Select(c => c.Sum ?? 0).Sum()
});
goodViewModelVodovoz[2].Price = goodViewModelVodovoz[2].Price - goodViewModelVodovoz[0].Price - goodViewModelVodovoz[1].Price;
goodViewModelVodovoz[2].PrimeCost = goodViewModelVodovoz[2].PrimeCost - goodViewModelVodovoz[0].PrimeCost - goodViewModelVodovoz[1].PrimeCost;
goodViewModelVodovoz[2].Sum = goodViewModelVodovoz[2].Sum - goodViewModelVodovoz[0].Sum - goodViewModelVodovoz[1].Sum;
return(goodViewModelVodovoz);
}
public List <PublicServiceSubtraction> GetPublicServicesSubtractionsByFilial(ATDbEntities atDbEntities, int filialId, short month, short year, short monthEnd, short yearEnd, int departmentId = 0)
{
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
if (departmentId > 0)
{
return(atDbEntities.PublicServicesSubtractions.Include("PublicServiceType").Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD && p.DepartmentId == departmentId).ToList());
}
return(atDbEntities.PublicServicesSubtractions.Include("PublicServiceType").Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD).ToList());
}
public List <DefectDiscount> GetPublicDefectDiscountByFilial(ATDbEntities atDbEntities, int filialId, short month, short year, short monthEnd, short yearEnd, int departmentId = 0)
{
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
if (departmentId > 0)
{
return(atDbEntities.DefectsDiscounts.Where(p => p.FilialId == filialId && p.DepartmentId == departmentId && p.Date >= startD && p.Date <= endD).ToList());
}
return(atDbEntities.DefectsDiscounts.Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD).ToList());
}
public List <AdminExpensePlan> GetPublicAdminExpensePlanPlanByFilial(ATDbEntities atDbEntities, int filialId, short month, short year, short monthEnd, short yearEnd, int departmentId = 0)
{
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
if (departmentId > 0)
{
return(atDbEntities.AdminExpensesPlans.Where(p => p.FilialId == filialId && p.DepartmentId == departmentId && p.Date >= startD && p.Date <= endD).ToList());
}
return(atDbEntities.AdminExpensesPlans.Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD).ToList());
}
//public List<AutoGroupedBy> GetGroupedByDepartmentAutos(ATDbEntities atDbEntities, int filialId, int departmentId,
// short month, short year, int project, bool byYear)
//{
// var stocks = Enumerable.Empty<Auto>();
// if (byYear)
// stocks = atDbEntities.Autos.Include("Department")
// .Where(p => p.Year == year)
// .AsQueryable();
// else
// stocks = atDbEntities.Autos.Include("Department")
// .Where(p => p.Month == month && p.Year == year)
// .AsQueryable();
// if (filialId > 0) stocks = stocks.Where(s => s.FilialId == filialId);
// if (departmentId > 0) stocks = stocks.Where(s => s.DepartmentId == departmentId);
// if (project > 0) stocks = stocks.Where(s => s.ProjectId == project);
// var autoGroupedBy =
// stocks
// .GroupBy(p => new { p.Department.Name })
// .Select(n => new AutoGroupedBy
// {
// Name = n.Key.Name,
// SumСombustible = n.Sum(c => c.LitersWork * c.СombustiblePrice),
// SumExpenses = n.Sum(c => c.Accumulators + c.Expenses + c.Lubricants + c.Repairs
// + c.Services + c.Spares + c.TestareAuto + c.Tires),
// Count = n.Count()
// })
// .OrderBy(p => p.Name)
// .ToList();
// return autoGroupedBy;
//}
//public List<AutoGroupedBy> GetGroupedByBrandAutos(ATDbEntities atDbEntities, int filialId, int departmentId,
// short month, short year, int project, bool byYear)
//{
// var stocks = Enumerable.Empty<Auto>();
// if (byYear)
// stocks = atDbEntities.Autos.Include("Department")
// .Where(p => p.Year == year)
// .AsQueryable();
// else
// stocks = atDbEntities.Autos.Include("Department")
// .Where(p => p.Month == month && p.Year == year)
// .AsQueryable();
// if (filialId > 0) stocks = stocks.Where(s => s.FilialId == filialId);
// if (departmentId > 0) stocks = stocks.Where(s => s.DepartmentId == departmentId);
// if (project > 0) stocks = stocks.Where(s => s.ProjectId == project);
// var autoGroupedBy =
// stocks
// .GroupBy(p => new { Brand = p.Brand.Trim() })
// .Select(n => new AutoGroupedBy
// {
// Name = n.Key.Brand,
// SumСombustible = n.Sum(c => c.LitersWork * c.СombustiblePrice),
// SumExpenses = n.Sum(c => c.Accumulators + c.Expenses + c.Lubricants + c.Repairs
// + c.Services + c.Spares + c.TestareAuto + c.Tires),
// Count = n.Count()
// })
// .OrderBy(p => p.Name)
// .ToList();
// return autoGroupedBy;
//}
public List <AutoGroupedBy> GetGroupedAutos <TKey>(ATDbEntities atDbEntities, int filialId, int departmentId,
short month, short year, int project, short autoType, bool byYear, Expression <Func <Auto, TKey> > myGroupingProperty)
{
var stocks = Enumerable.Empty <Auto>().AsQueryable();
if (byYear)
{
stocks = atDbEntities.Autos.Include("Department")
.Where(p => p.Year == year)
.AsQueryable();
}
else
{
stocks = atDbEntities.Autos.Include("Department")
.Where(p => p.Month == month && p.Year == year)
.AsQueryable();
}
if (filialId > 0)
{
stocks = stocks.Where(s => s.FilialId == filialId);
}
if (departmentId > 0)
{
stocks = stocks.Where(s => s.DepartmentId == departmentId);
}
if (autoType >= 0)
{
stocks = stocks.Where(s => s.Type == (AutoType)autoType);
}
if (project > 0)
{
stocks = stocks.Where(s => s.ProjectId == project);
}
var autoGroupedBy =
stocks
.GroupBy(myGroupingProperty)
.Select(n => new AutoGroupedBy
{
Name = n.Key.ToString(),
SumСombustible = n.Sum(c => c.LitersWork * c.СombustiblePrice),
SumExpenses = n.Sum(c => c.Accumulators + c.Expenses + c.Lubricants + c.Repairs
+ c.Services + c.Spares + c.TestareAuto + c.Tires) * 1.2,
Count = n.Count(),
Weight = n.Sum(c => c.Weight ?? 0.0)
})
.OrderBy(p => p.Name)
.ToList();
return(autoGroupedBy);
}
public List <Salary> GetSalariesByPost(ATDbEntities atDbEntities, int filialId, short month, short year, short monthEnd, short yearEnd, string post, int departmentId = 0)
{
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
if (departmentId > 0)
{
return(atDbEntities.Salaries.Where(p => p.FilialId == filialId && p.DepartmentId == departmentId && p.Date >= startD && p.Date <= endD && p.Post == post).OrderBy(p => p.Employee).ToList());
}
return(atDbEntities.Salaries.Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD && p.Post == post).OrderBy(p => p.Employee).ToList());
}
public List <SalariesGroupedByDepartment> GetGroupedByPostSalaries(ATDbEntities atDbEntities, int filialId, int departmentId,
short month, short year, int project, bool byYear)
{
var stocks = Enumerable.Empty <Salary>();
if (byYear)
{
stocks = atDbEntities.Salaries
.Where(p => p.Year == year)
.AsQueryable();
}
else
{
stocks = atDbEntities.Salaries
.Where(p => p.Month == month && p.Year == year)
.AsQueryable();
}
if (filialId > 0)
{
stocks = stocks.Where(s => s.FilialId == filialId);
}
if (departmentId > 0)
{
stocks = stocks.Where(s => s.DepartmentId == departmentId);
}
if (project > 0)
{
stocks = stocks.Where(s => s.ProjectId == project);
}
var salariesGrouped =
stocks
.GroupBy(p => new { post = p.Post.Trim() })
.Select(n => new SalariesGroupedByDepartment
{
Name = n.Key.post,
SumSal = n.Sum(c => c.Card + c.Cash + c.SickLeave + (c.Deduction ?? 0.0)),
SumVac = n.Sum(c => c.Vacation),
SumTax = n.Sum(c => (c.PensionFund ?? 0.0) + (c.HealthInsurance ?? 0.0) * 2
+ (c.IncomeTax ?? 0.0) + (c.SocialFund ?? 0.0)),
Count = n.Count()
}
)
.OrderBy(p => p.Name)
.ToList();
return(salariesGrouped);
}
public List <Auto> GetAutos(ATDbEntities atDbEntities, int filialId, int departmentId, short month, short year, short monthEnd,
short yearEnd, short combustibleType, short autoType, string number, int project)
{
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
//if (departmentId > 0)
// return atDbEntities.Autos.Include("Filial").Include("Department").Where(p => p.FilialId == filialId && p.DepartmentId == departmentId && p.Date >= startD && p.Date <= endD).ToList();
var stocks = atDbEntities.Autos.Include("Filial").Include("Department")
.Where(p => p.Date >= startD && p.Date <= endD)
.AsQueryable();
if (filialId > 0)
{
stocks = stocks.Where(s => s.FilialId == filialId);
}
if (departmentId > 0)
{
stocks = stocks.Where(s => s.DepartmentId == departmentId);
}
if (combustibleType >= 0)
{
stocks = stocks.Where(s => s.Сombustible == (СombustibleType)combustibleType);
}
if (autoType >= 0)
{
stocks = stocks.Where(s => s.Type == (AutoType)autoType);
}
if (number.Trim() != string.Empty)
{
stocks = stocks.Where(s => s.Number.Contains(number.Trim()));
}
if (project > 0)
{
stocks = stocks.Where(s => s.ProjectId == project);
}
//return atDbEntities.Autos.Include("Filial").Include("Department")
// .Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD)
// .Where(s => s.DepartmentId == departmentId || departmentId <= 0)
// .Where(s => s.Сombustible == (СombustibleType)combustibleType || combustibleType < 0)
// .ToList();
return(stocks.ToList());
}
public List <Salary> GetSalaries(ATDbEntities atDbEntities, int filialId, int departmentId, short month, short year, short monthEnd,
short yearEnd, short personType, int project, string post, string employee)
{
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
var stocks = atDbEntities.Salaries.Include("Filial").Include("Department")
.Where(p => p.Date >= startD && p.Date <= endD)
.AsQueryable();
if (filialId > 0)
{
stocks = stocks.Where(s => s.FilialId == filialId);
}
if (departmentId > 0)
{
stocks = stocks.Where(s => s.DepartmentId == departmentId);
}
if (personType >= 0)
{
stocks = stocks.Where(s => s.Type == (PersonType)personType);
}
if (project > 0)
{
stocks = stocks.Where(s => s.ProjectId == project);
}
if (employee.Trim() != string.Empty)
{
stocks = stocks.Where(s => s.Employee.Contains(employee.Trim()));
}
if (post != "- Все - ")
{
stocks = stocks.Where(s => s.Post == post);
}
//return atDbEntities.Autos.Include("Filial").Include("Department")
// .Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD)
// .Where(s => s.DepartmentId == departmentId || departmentId <= 0)
// .Where(s => s.Сombustible == (СombustibleType)combustibleType || combustibleType < 0)
// .ToList();
return(stocks.ToList());
}
public List <Auto> GetAutosByType(ATDbEntities atDbEntities, AutoType type, int filialId, short month, short year, short monthEnd, short yearEnd, int departmentId = 0)
{
var startD = new DateTime(year, month, 1);
var endD = new DateTime(yearEnd, monthEnd, 1);
if (departmentId > 0)
{
if (type == AutoType.All)
{
return(atDbEntities.Autos.Where(p => p.FilialId == filialId && p.DepartmentId == departmentId && p.Date >= startD && p.Date <= endD).OrderBy(p => p.Number).ToList());
}
else
{
return(atDbEntities.Autos.Where(p => p.FilialId == filialId && p.DepartmentId == departmentId && p.Date >= startD && p.Date <= endD && p.Type == type).OrderBy(p => p.Number).ToList());
}
}
return(type == AutoType.All ? atDbEntities.Autos.Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD).OrderBy(p => p.Number).ToList()
: atDbEntities.Autos.Where(p => p.FilialId == filialId && p.Date >= startD && p.Date <= endD && p.Type == type).OrderBy(p => p.Number).ToList());
}
public double?GetAutosWeightByFilial(ATDbEntities atDbEntities, int filialId, short month, short year, bool byYear)
{
var autos = Enumerable.Empty <Auto>();
if (byYear)
{
autos = atDbEntities.Autos.Where(p => p.Year == year).AsQueryable();
}
else
{
autos = atDbEntities.Autos.Where(p => p.Year == year && p.Month == month).AsQueryable();
}
if (filialId > 0)
{
autos = autos.Where(p => p.FilialId == filialId);
}
return(autos.Sum(p => p.Weight));
}
public double?GetGoodsSumByFilial(ATDbEntities atDbEntities, int filialId, short month, short year, bool byYear)
{
var goods = Enumerable.Empty <Good>();
if (byYear)
{
goods = atDbEntities.Goods
.Where(p => p.Year == year).AsQueryable();
}
else
{
goods = atDbEntities.Goods
.Where(p => p.Month == month && p.Year == year).AsQueryable();
}
if (filialId > 0)
{
goods = goods.Where(p => p.FilialId == filialId);
}
return(goods.Sum(p => (double?)p.Sum) ?? 0);
}
public List <UserInRole> GetUserRoles(ATDbEntities atDbEntities, long userId)
{
return(atDbEntities.UsersInRoles.Include("Role").Where(p => p.UserId == userId).ToList());
}
public Department GetDepartmentById(ATDbEntities atDbEntities, int Id)
{
return(atDbEntities.Departments.Where(p => p.DepartmentId == Id).FirstOrDefault());
}
public List <Department> GetAllDepartments(ATDbEntities atDbEntities)
{
return(atDbEntities.Departments.ToList());
}
public Filial GetFilialById(ATDbEntities atDbEntities, int Id)
{
return(atDbEntities.Filials.Where(p => p.FilialId == Id).FirstOrDefault());
}
public List <Filial> GetAllFilials(ATDbEntities atDbEntities)
{
return(atDbEntities.Filials.ToList());
}
public List <User> GetAllUsers(ATDbEntities atDbEntities)
{
return(atDbEntities.Users.Include("FilialObj").Include("DepartmentObj").ToList());
}
public AdminAggregateRoot(ATDbEntities ade, AdminCommands adminCommands, WebContextService contextService)
{
_ade = ade;
_adminCommands = adminCommands;
_contextService = contextService;
}
public List <PostByFilial> GetPostsByDepartmentId(ATDbEntities atDbEntities, int departmentId)
{
return(atDbEntities.PostsByFilials.Where(p => p.DepartmentId == departmentId).ToList());
}
public History GetLastHistoryBySourceAction(ATDbEntities atDbEntities, ActionHistoryType action, SourceHistoryType source)
{
return(atDbEntities.Histories.Where(p => p.Action == action && p.Source == source).OrderByDescending(p => p.Date).FirstOrDefault());
}
public List <PostByFilial> GetPostsByFilialId(ATDbEntities atDbEntities, int filialtId)
{
return(atDbEntities.PostsByFilials.Where(p => p.FilialId == filialtId).ToList());
}
public List <PostByFilial> GetPosts(ATDbEntities atDbEntities)
{
return(atDbEntities.PostsByFilials.ToList());
}
public List <Project> GetProjects(ATDbEntities atDbEntities)
{
return(atDbEntities.Projects.ToList());
}