public async Task <List <AnnualTimeModel> > GetAnnual(int year) { List <AnnualTimeModel> result = new List <AnnualTimeModel>(); AnnualTimeModel total = new AnnualTimeModel { Project = new MasterModel { Id = 0, Name = "TOTAL" } }; List <Project> projects = (await _unit.Projects.Get()).OrderBy(p => p.Name).ToList(); foreach (Project p in projects) { List <Detail> query = p.Details.Where(d => d.Day.Date.Year == year).ToList(); if (query.Count != 0) { var list = query.GroupBy(d => d.Day.Date.Month) .Select(x => new { month = x.Key, hours = x.Sum(y => y.Hours) }); AnnualTimeModel atm = new AnnualTimeModel { Project = p.Master() }; foreach (var item in list) { atm.Hours[item.month - 1] = item.hours; atm.Total += item.hours; total.Hours[item.month - 1] += item.hours; total.Total += item.hours; } total.Project.Id++; result.Add(atm); } } result.Add(total); return(result); }
public async Task <List <AnnualTimeModel> > GetStored(int year) { List <AnnualTimeModel> result = new List <AnnualTimeModel>(); AnnualTimeModel total = new AnnualTimeModel { Project = new MasterModel { Id = 0, Name = "TOTAL" } }; List <AnnualRawModel> rawData = new List <AnnualRawModel>(); var cmd = _unit.Context.Database.GetDbConnection().CreateCommand(); if (Startup.Configuration["Connection:Type"] == "SQL") { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = $"AnnualReport"; cmd.Parameters.Add(new SqlParameter { ParameterName = "year", Value = year }); } else { cmd.CommandType = CommandType.Text; cmd.CommandText = $"select * from AnnualReport({year})"; if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } } if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } DbDataReader sql = await cmd.ExecuteReaderAsync(); if (sql.HasRows) { while (sql.Read()) { rawData.Add(new AnnualRawModel { Id = sql.GetInt32(0), Name = sql.GetString(1), Month = sql.GetInt32(2), Hours = sql.GetDecimal(3) }); } } if (rawData.Count != 0) { AnnualTimeModel atm = new AnnualTimeModel { Project = new MasterModel { Id = 0 } }; foreach (AnnualRawModel item in rawData) { if (atm.Project.Id != item.Id) { if (atm.Project.Id != 0) { result.Add(atm); } atm = new AnnualTimeModel { Project = new MasterModel { Id = item.Id, Name = item.Name } }; total.Project.Id++; } atm.Hours[item.Month - 1] = item.Hours; atm.Total += item.Hours; total.Hours[item.Month - 1] += item.Hours; total.Total += item.Hours; } if (atm.Project.Id != 0) { result.Add(atm); } } result.Add(total); return(result); }
public List <AnnualTimeModel> GetStored(int year) { List <AnnualTimeModel> result = new List <AnnualTimeModel>(); AnnualTimeModel total = new AnnualTimeModel { Project = new MasterModel { Id = 0, Name = "TOTAL" } }; List <AnnualRawModel> rawData = _storedProcedures.GetStoredProcedure <AnnualRawModel>("AnnualReport", new int[] { year }); /* * var cmd = _unit.Context.Database.GetDbConnection().CreateCommand(); * cmd.CommandType = CommandType.Text; * cmd.CommandText = $"select * from AnnualReport({year})"; * if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open(); * DbDataReader sql = cmd.ExecuteReader(); * List<AnnualRawModel> rawData = new List<AnnualRawModel>(); * if (sql.HasRows) * { * while (sql.Read()) * { * rawData.Add(new AnnualRawModel * { * Id = sql.GetInt32(0), * Name = sql.GetString(1), * Month = sql.GetInt32(2), * Hours = sql.GetDecimal(3) * }); * } */ AnnualTimeModel atm = new AnnualTimeModel { Project = new MasterModel { Id = 0 } }; foreach (AnnualRawModel item in rawData) { if (atm.Project.Id != item.Id) { if (atm.Project.Id != 0) { result.Add(atm); } atm = new AnnualTimeModel { Project = new MasterModel { Id = item.Id, Name = item.Name } }; total.Project.Id++; } atm.Hours[item.Month - 1] = item.Hours; atm.Total += item.Hours; total.Hours[item.Month - 1] += item.Hours; total.Total += item.Hours; } if (atm.Project.Id != 0) { result.Add(atm); } //} result.Add(total); return(result); }