/// <summary>
/// 按月统计任务（半年、一年）
/// </summary>
/// <param name="searchMoth"></param>
/// <returns></returns>
public async Task <ChartByTaskDto> GetChartByMothAsync(int searchMoth, AreaCodeEnum areaCode)
{
var timeNow = DateTime.Today;
DateTime startTime;
DateTime endTime;
if (searchMoth == 1)
{
startTime = timeNow.AddDays(1 - timeNow.Day).AddMonths(-11);
endTime = timeNow.AddDays(1 - timeNow.Day).AddMonths(1).AddDays(-1);
}
else
{
startTime = timeNow.AddDays(1 - timeNow.Day).AddMonths(-5);
endTime = timeNow.AddDays(1 - timeNow.Day).AddMonths(1).AddDays(-1);
}
var list = await _scheduleDetailRepository.GetSheduleStatisticalDtosByMothAsync(startTime, endTime, areaCode);
list.OrderBy(s => s.GroupName).ToList();
var items = new List <DistrictChartItemDto>();
var result = new ChartByTaskDto();
foreach (var item in list)
{
result.Tasks.Add(new SheduleByTaskDto
{
TaskName = item.GroupName,
VisitNum = item.Total,
CompleteNum = item.Completed,
ExpiredNum = item.Expired
});
//items.Add(new DistrictChartItemDto()
//{
// District = item.GroupName,
// Name = "计划",
// Num = item.Total
//});
//items.Add(new DistrictChartItemDto()
//{
// District = item.GroupName,
// Name = "进行中",
// Num = item.Completed
//});
//items.Add(new DistrictChartItemDto()
//{
// District = item.GroupName,
// Name = "逾期",
// Num = item.Expired
//});
}
return(result);
}
/// <summary>
/// 获取区县下面的钉钉组织架构
/// </summary>
private List <EmployeeNzTreeNode> GetAreaOrganization(AreaCodeEnum area)
{
//获取配置code
var orgCode = "";
switch (area)
{
case AreaCodeEnum.昭化区:
{
orgCode = GYCode.昭化区;
}
break;
case AreaCodeEnum.剑阁县:
{
orgCode = GYCode.剑阁县;
}
break;
case AreaCodeEnum.旺苍县:
{
orgCode = GYCode.旺苍县;
}
break;
}
var orgIds = _systemdataRepository.GetAll().Where(s => s.ModelId == ConfigModel.烟叶服务 && s.Type == ConfigType.烟叶公共 && s.Code == orgCode).First().Desc;
List <EmployeeNzTreeNode> areaList = new List <EmployeeNzTreeNode>();
if (!string.IsNullOrEmpty(orgIds))
{
var orgIdArr = orgIds.Split(',');
foreach (var orgid in orgIdArr)
{
var longOrgId = long.Parse(orgid);
var org = _organizationRepository.Get(longOrgId);
areaList.Add(new EmployeeNzTreeNode()
{
key = org.Id.ToString(),
title = org.DepartmentName,
IsDept = true,
IsLeaf = false,
children = GetDeptChildren(longOrgId)
});
}
}
//添加特定访问人员（通过设置区县指定）
var specificUserList = GetAreaEmoloyee(area);
areaList.AddRange(specificUserList);
return(areaList);
}
/// <summary>
/// 计划汇总
/// </summary>
public async Task <List <ScheduleSummaryDto> > GetScheduleSummaryAsync(string userId, AreaCodeEnum areaCode)
{
//获取当前用户区县
//var area = await _employeeManager.GetAreaCodeByUserIdAsync(userId);
return(await Task.Run(() =>
{
var dataList = new List <ScheduleSummaryDto>();
var query = from sd in _scheduleDetailRepository.GetAll()
join s in _scheduleRepository.GetAll() on sd.ScheduleId equals s.Id
join g in _growerRepository.GetAll() on sd.GrowerId equals g.Id
where s.Status == ScheduleMasterStatusEnum.已发布 &&
(areaCode == AreaCodeEnum.广元市 || g.AreaCode == areaCode) &&//区县数据过滤
s.EndTime >= DateTime.Today
select sd;
//总数
var tnum = query.Sum(q => q.VisitNum);
tnum = tnum ?? 0;
//完成数
var cnum = query.Sum(q => q.CompleteNum);
cnum = cnum ?? 0;
var cpercent = tnum == 0 ? 0 : Math.Round(cnum.Value / (decimal)tnum.Value, 2); //百分比
dataList.Add(new ScheduleSummaryDto()
{
Num = cnum, Name = "完成", ClassName = "complete", Percent = cpercent * 100, Seq = 1, Status = 2
});
//逾期数
var etnum = query.Where(q => q.Status == ScheduleStatusEnum.已逾期).Sum(q => q.VisitNum - q.CompleteNum);
etnum = etnum ?? 0;
var etpercent = tnum == 0 ? 0 : Math.Round(etnum.Value / (decimal)tnum.Value, 2); //百分比
dataList.Add(new ScheduleSummaryDto()
{
Num = etnum, Name = "逾期", ClassName = "overdue", Percent = etpercent * 100, Seq = 3, Status = 0
});
//待完成数
var pnum = tnum - cnum - etnum;
var ppercent = tnum == 0 ? 0 : (1M - cpercent - etpercent);
dataList.Add(new ScheduleSummaryDto()
{
Num = pnum, Name = "待完成", ClassName = "process", Percent = ppercent * 100, Seq = 2, Status = 3
});
return dataList.OrderBy(d => d.Seq).ToList();
}));
}
/// <summary>
/// 统计任务完成情况的数据（按任务类型和任务名分组）
/// </summary>
/// <param name="tabIndex">（1表示当前任务，结束时间大于今天；2表示所有任务）</param>
/// <returns></returns>
public async Task <ChartByTaskDto> GetChartByGroupAsync(DateTime?startTime, DateTime?endTime, int tabIndex, AreaCodeEnum areaCode)
{
var query = from sd in _scheduleDetailRepository.GetAll()
join s in _scheduleRepository.GetAll()
.WhereIf(startTime.HasValue && tabIndex == 2, s => s.EndTime >= startTime)
.WhereIf(startTime.HasValue && tabIndex == 2, s => s.EndTime <= endTime.Value.AddDays(1))
.WhereIf(tabIndex == 1, s => s.EndTime >= DateTime.Today)
.Where(s => s.Status == ScheduleMasterStatusEnum.已发布)
on sd.ScheduleId equals s.Id
join t in _visitTaskRepository.GetAll() on sd.TaskId equals t.Id
join g in _growerRepository.GetAll() on sd.GrowerId equals g.Id
where (areaCode == AreaCodeEnum.广元市 || g.AreaCode == areaCode)//添加区县权限 add by donald 2019-1-22
select new
{
sd.VisitNum,
sd.CompleteNum,
sd.Status,
t.Type,
t.Name,
t.Id
};
var list = query.GroupBy(s => new { s.Name, s.Type, s.Id }).Select(s =>
new SheduleByTaskDto()
{
Id = s.Key.Id,
//TaskType=s.Key.Type,
TaskName = s.Key.Name,
VisitNum = s.Sum(sd => sd.VisitNum),
CompleteNum = s.Sum(sd => sd.CompleteNum),
ExpiredNum = s.Where(sd => sd.Status == ScheduleStatusEnum.已逾期).Sum(sd => sd.VisitNum - sd.CompleteNum)
});
var result = new ChartByTaskDto();
result.Tasks = await list.OrderBy(l => l.TaskName).ToListAsync();
return(result);
}
/// <summary>
/// 获取区县图表数据
/// </summary>
/// <param name="tabIndex">（1表示当前任务，结束时间大于今天；2表示所有任务）</param>
/// <returns></returns>
public async Task <DistrictChartDto> GetDistrictChartDataAsync(string userId, DateTime?startDate, DateTime?endDate, int tabIndex, AreaCodeEnum areaCode)
{
//var str = startDate.Value.ToString("yyyy-MM-dd HH:mm ss");
//if (startDate.HasValue)
//{
// var sdate = startDate.Value.Date;
// startDate = sdate.AddHours(sdate.Hour * -1);
//}
//if (endDate.HasValue)
//{
// var edate = endDate.Value.Date;
// endDate = edate.AddHours(edate.Hour * -1);
//}
var query = from sd in _scheduleDetailRepository.GetAll()
join s in _scheduleRepository.GetAll()
.WhereIf(startDate.HasValue && tabIndex == 2, s => s.EndTime >= startDate)
.WhereIf(endDate.HasValue && tabIndex == 2, s => s.EndTime <= endDate)
.WhereIf(tabIndex == 1, s => s.EndTime >= DateTime.Today)
on sd.ScheduleId equals s.Id
join g in _growerRepository.GetAll() on sd.GrowerId equals g.Id
where s.Status == ScheduleMasterStatusEnum.已发布
where (areaCode == AreaCodeEnum.广元市 || g.AreaCode == areaCode)//添加区县权限 add by donald 2019-1-22
select new
{
g.AreaCode,
sd.VisitNum,
sd.CompleteNum,
sd.Status
};
var rquery = query.GroupBy(g => g.AreaCode).Select(g1 =>
new DistrictDto()
{
AreaType = g1.Key,
VisitNum = g1.Sum(g => g.VisitNum),
CompleteNum = g1.Sum(g => g.CompleteNum),
OverdueNum = g1.Where(g => g.Status == ScheduleStatusEnum.已逾期).Sum(g => g.VisitNum - g.CompleteNum)
});
var resultData = new DistrictChartDto();
resultData.Districts = await rquery.ToListAsync();
return(resultData);
}
/// <summary>
/// 获取任务完成情况的统计（按月份统计）
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public async Task <List <SheduleStatisticalDto> > GetSheduleStatisticalDtosByMothAsync(DateTime startTime, DateTime endTime, AreaCodeEnum areaCode)
{
EnsureConnectionOpen();
SqlParameter[] sql = new SqlParameter[]
{
new SqlParameter("@startTime", startTime),
new SqlParameter("@endTime", endTime),
new SqlParameter("@areaCode", areaCode)
};
var command = CreateCommand(@"select cast(t1.[Year] as varchar(4)) + '-' + (case when t1.[Month] > 9 then cast(t1.[Month] as varchar(2)) else '0' + cast(t1.[Month] as varchar(2)) end ) Months,
t1.VisitNum, t1.CompleteNum, isnull(t2.ExpireNum, 0) as ExpireNum
from(
select[Year],[Month], sum(CompleteNum) as CompleteNum, sum(VisitNum) as VisitNum
from(
select year(s.EndTime) as [Year], month(s.EndTime) as [Month], sd.CompleteNum, sd.VisitNum from[dbo].[ScheduleDetails] sd
inner join [dbo].[Schedules] s on sd.ScheduleId = s.Id
inner join [dbo].[Growers] g on sd.GrowerId = g.Id
where s.EndTime >=@startTime and s.EndTime < @endTime and s.Status=1 and (@areaCode = 4 or g.AreaCode = @areaCode)
) temp group by[Year], [Month]
) t1 left join(
select[Year],[Month], sum(ExpireNum) as ExpireNum
from(
select year(s.EndTime) as [Year], month(s.EndTime) as [Month], sd.VisitNum - sd.CompleteNum as ExpireNum from[dbo].[ScheduleDetails] sd
inner join[dbo].[Schedules] s on sd.ScheduleId = s.Id
inner join [dbo].[Growers] g on sd.GrowerId = g.Id
where s.EndTime >= @startTime and s.EndTime < @endTime and s.Status=1 and (@areaCode = 4 or g.AreaCode = @areaCode)
and sd.[Status] = 0
) temp group by[Year], [Month]) t2 on t1.[Year] = t2.[Year] and t1.[Month] = t2.[Month]
", CommandType.Text, sql);
using (command)
{
using (var dataReader = await command.ExecuteReaderAsync())
{
var result = new List <SheduleStatisticalDto>();
while (dataReader.Read())
{
var sheduleStatis = new SheduleStatisticalDto();
sheduleStatis.GroupName = dataReader["Months"].ToString();
sheduleStatis.Total = (int)dataReader["VisitNum"];
sheduleStatis.Completed = (int)dataReader["CompleteNum"];
sheduleStatis.Expired = (int)dataReader["ExpireNum"];
result.Add(sheduleStatis);
}
return(result);
}
}
}
