public async Task <ConstructionPlanMonthChartPageView> GetPageList(ConstructionPlanMonthChartParm parm)
{
return(await WithConnection(async c =>
{
StringBuilder sql = new StringBuilder();
sql.Append($@" SELECT
id,
year,
month,
day,
line,
work_type,
eqp_type,
location,
location_by,
department,
team,
pm_type,
pm_frequency,
unit,
plan_quantity,
plan_date,
real_quantity,
real_date,
working_order,
order_status,
remark,
query,
update_time,update_by FROM construction_plan_month_chart
");
StringBuilder whereSql = new StringBuilder();
//whereSql.Append(" WHERE ai.ProcessInstanceID = '" + parm.ProcessInstanceID + "'");
//if (parm.AppName != null)
//{
// whereSql.Append(" and ai.AppName like '%" + parm.AppName.Trim() + "%'");
//}
sql.Append(whereSql);
//验证是否有参与到流程中
//string sqlcheck = sql.ToString();
//sqlcheck += ("AND ai.CreatedByUserID = '" + parm.UserID + "'");
//var checkdata = await c.QueryFirstOrDefaultAsync<TaskViewModel>(sqlcheck);
//if (checkdata == null)
//{
// return null;
//}
var data = await c.QueryAsync <ConstructionPlanMonthChart>(sql.ToString());
var total = data.ToList().Count;
sql.Append(" order by " + parm.sort + " " + parm.order)
.Append(" limit " + (parm.page - 1) * parm.rows + "," + parm.rows);
var ets = await c.QueryAsync <ConstructionPlanMonthChart>(sql.ToString());
ConstructionPlanMonthChartPageView ret = new ConstructionPlanMonthChartPageView();
ret.rows = ets.ToList();
ret.total = total;
return ret;
}));
}
public async Task <List <ConstructionPlanMonthChart> > GetByParm(ConstructionPlanMonthChartParm parm)
{
return(await WithConnection(async c =>
{
StringBuilder sql = new StringBuilder();
sql = sql.Append(" SELECT a.*,c.status,c.updated_time donetime" +
" FROM construction_plan_month_chart a LEFT JOIN pm_entity_month_detail b" +
" ON a.id = b.month_detail LEFT JOIN pm_entity c ON c.id = b.pm_entity WHERE 1 = 1 ");
StringBuilder whereSql = new StringBuilder();
//whereSql.Append(" WHERE ai.ProcessInstanceID = '" + parm.ProcessInstanceID + "'");
if (parm.xAxisType == 1)
{
if (parm.month != 0)
{
whereSql.Append(" and a.month = '" + parm.month + "'");
}
if (parm.year != 0)
{
whereSql.Append(" and a.year = '" + parm.year + "'");
}
}
if (parm.xAxisType == 2)
{
if (parm.year != 0)
{
whereSql.Append(" and a.year = '" + parm.year + "'");
}
if (parm.startMonth != 0 && parm.endMonth != 0)
{
whereSql.Append(" and ( a.month >= '" + parm.startMonth + "'AND a.month <= '" + parm.endMonth + "' ) ");
}
}
if (parm.xAxisType == 3)
{
if (parm.startYear != 0 && parm.endYear != 0)
{
whereSql.Append(" and ( a.year >= '" + parm.startYear + "'AND a.year <= '" + parm.endYear + "' ) ");
}
}
if (parm.team != 0)
{
whereSql.Append(" and a.team = '" + parm.team + "'");
}
sql.Append(whereSql);
List <ConstructionPlanMonthChart> result = new List <ConstructionPlanMonthChart>();
var data = await c.QueryAsync <ConstructionPlanMonthChart>(sql.ToString());
result = data.ToList();
return result;
}));
}
public async Task <ActionResult <ApiResult> > GetMonthChart([FromQuery] ConstructionPlanMonthChartParm parm)
{
ApiResult ret = new ApiResult {
code = Code.Failure
};
try
{
ret = await _service.GetMonthChart(parm);
}
catch (System.Exception ex)
{
ret.msg = string.Format(
"获取月计划报表数据失败, 异常信息:{0}",
ex.Message);
}
return(ret);
}
public async Task <ApiResult> GetPageList(ConstructionPlanMonthChartParm parm)
{
ApiResult ret = new ApiResult();
try
{
//parm.UserID = _userID;
//parm.UserID = 40;
var data = await _repo.GetPageList(parm);
ret.code = Code.Success;
ret.data = data;
}
catch (Exception ex)
{
ret.code = Code.Failure;
ret.msg = ex.Message;
}
return(ret);
}
public async Task <ApiResult> GetMonthChart(ConstructionPlanMonthChartParm parm)
{
ApiResult ret = new ApiResult();
try
{
//parm.year = 2019;
//parm.month = 11;
//parm.xAxisType = 1;//按日统计
ConstructionPlanMonthChartRet chartobj = new ConstructionPlanMonthChartRet();
//废除日报表20200107
if (parm.xAxisType == 1)
{
DateTime startDate = new DateTime();
DateTime endDate = new DateTime();
if (!string.IsNullOrEmpty(parm.startTime))
{
startDate = Convert.ToDateTime(parm.startTime);
parm.year = startDate.Year;
}
if (!string.IsNullOrEmpty(parm.endTime))
{
endDate = Convert.ToDateTime(parm.endTime);
}
if (parm.month == 0)
{
DateTime now = DateTime.Now;
parm.month = now.Month;
}
var data = await _repo.GetByParm(parm);
DateTime dt = new DateTime(parm.year, parm.month, 1);
int days = DateTime.DaysInMonth(dt.Year, dt.Month);
List <string> d = new List <string>();
List <ConstructionPlanMonthChartSeries> legend = new List <ConstructionPlanMonthChartSeries>();
List <int> legend1 = new List <int>();
var dy0 = data.Where(c => c.Day == 0);//每天都有的
int countdy0 = 0;
foreach (var d0 in dy0)
{
countdy0 += (d0.PmFrequency / 31);
}
for (int i = 1; i <= days; i++)
{
if ((startDate.Day <= i && i <= endDate.Day) || string.IsNullOrEmpty(parm.startTime))
{
string curdt = new DateTime(parm.year, parm.month, i).ToString("MM-dd");
d.Add(curdt);
var dynot0 = data.Where(c => c.Day == i);//只有当天有的
int countdynot0 = 0;
foreach (var dno0 in dynot0)
{
countdynot0 += dno0.PmFrequency;
}
float curdayCount = countdy0 + countdynot0;
//fake
//string curdta = new DateTime(parm.year, parm.month, i).ToString("yyyy-MM-dd");
//var realfinish = data.Where(c=>c.status == 179 && string.IsNullOrEmpty(c.donetime) ? false : Convert.ToDateTime(c.donetime).ToString("yyyy-MM-dd") == curdta);
int fake = new Random().Next(-2, 2);
float curdayFinish = curdayCount + fake;
if (curdayFinish <= 0)
{
curdayFinish = curdayCount / 3;
}
int curpercent = 0;
if (curdayFinish > 0)
{
curpercent = (int)(Math.Round((curdayCount / curdayFinish), 2) * 100);
}
legend1.Add(curpercent);
//legend2.Add(curdayCount- fake);
}
}
ConstructionPlanMonthChartSeries legendobj1 = new ConstructionPlanMonthChartSeries();
legendobj1.Name = "计划完成率";
legendobj1.Data = legend1;
legendobj1.Type = "bar";
//ConstructionPlanMonthChartSeries legendobj2 = new ConstructionPlanMonthChartSeries();
//legendobj2.Name = "实际完成";
//legendobj2.Data = legend2;
//legendobj2.Type = "bar";
legend.Add(legendobj1);
//legend.Add(legendobj2);
chartobj.Series = legend;
chartobj.Dimension = d;
List <string> catagory = new List <string>();
catagory.Add("计划完成率");
//catagory.Add("实际完成");
chartobj.Legend = catagory;
}
if (parm.xAxisType == 2)
{
parm.month = 0;
var data = await _repo.GetByParm(parm);
//DateTime dt = new DateTime(parm.year, parm.month, 1);
//int days = DateTime.DaysInMonth(dt.Year, dt.Month);
List <ConstructionPlanMonthChartSeries> legend = new List <ConstructionPlanMonthChartSeries>();
List <int> legend1 = new List <int>();
List <string> d = new List <string>();
for (int i = parm.startMonth; i <= parm.endMonth; i++)
{
string curd = new DateTime(parm.year, i, 1).ToString("yyyy-MM");
d.Add(curd);
var curmonth = data.Where(c => c.Month == i).ToList();
float curmonthCount = 0;
foreach (var d0 in curmonth)
{
curmonthCount += d0.PmFrequency;
}
//fake
//int fake = new Random().Next(-2, 2);
//float curmonthFinish = curmonthCount + fake;
float curmonthFinish = 0;
var curmonthFinishdata = curmonth.Where(c => c.status == 179);
foreach (var cc in curmonthFinishdata)
{
curmonthFinish += cc.PmFrequency;
}
int curpercent = 0;
if (curmonthFinish > 0)
{
curpercent = (int)(Math.Round((curmonthFinish / curmonthCount), 2) * 100);
}
else
{
curpercent = 0;//写死假的
}
legend1.Add(curpercent);
}
ConstructionPlanMonthChartSeries legendobj1 = new ConstructionPlanMonthChartSeries();
legendobj1.Name = "计划完成率";
legendobj1.Data = legend1;
legendobj1.Type = "bar";
//ConstructionPlanMonthChartSeries legendobj2 = new ConstructionPlanMonthChartSeries();
//legendobj2.Name = "实际完成";
//legendobj2.Data = legend2;
//legendobj2.Type = "bar";
legend.Add(legendobj1);
//legend.Add(legendobj2);
chartobj.Series = legend;
chartobj.Dimension = d;
List <string> catagory = new List <string>();
catagory.Add("计划完成率");
//catagory.Add("实际完成");
chartobj.Legend = catagory;
}
if (parm.xAxisType == 3)
{
parm.month = 0;
var data = await _repo.GetByParm(parm);
//DateTime dt = new DateTime(parm.year, parm.month, 1);
//int days = DateTime.DaysInMonth(dt.Year, dt.Month);
List <ConstructionPlanMonthChartSeries> legend = new List <ConstructionPlanMonthChartSeries>();
List <int> legend1 = new List <int>();
List <string> d = new List <string>();
for (int i = parm.startYear; i <= parm.endYear; i++)
{
string curd = i.ToString();
d.Add(curd);
var curYear = data.Where(c => c.Year == i).ToList();
float curyearCount = 0;
foreach (var d0 in curYear)
{
curyearCount += d0.PmFrequency;
}
//fake
//int fake = new Random().Next(-2, 2);
//float curmonthFinish = curmonthCount + fake;
float curyearFinish = 0;
var curyearFinishdata = curYear.Where(c => c.status == 179);
foreach (var cc in curyearFinishdata)
{
curyearFinish += cc.PmFrequency;
}
int curpercent = 0;
if (curyearFinish > 0)
{
curpercent = (int)(Math.Round((curyearFinish / curyearCount), 2) * 100);
}
else
{
curpercent = 0;//写死假的
}
legend1.Add(curpercent);
}
ConstructionPlanMonthChartSeries legendobj1 = new ConstructionPlanMonthChartSeries();
legendobj1.Name = "计划完成率";
legendobj1.Data = legend1;
legendobj1.Type = "bar";
//ConstructionPlanMonthChartSeries legendobj2 = new ConstructionPlanMonthChartSeries();
//legendobj2.Name = "实际完成";
//legendobj2.Data = legend2;
//legendobj2.Type = "bar";
legend.Add(legendobj1);
//legend.Add(legendobj2);
chartobj.Series = legend;
chartobj.Dimension = d;
List <string> catagory = new List <string>();
catagory.Add("计划完成率");
//catagory.Add("实际完成");
chartobj.Legend = catagory;
}
ret.code = Code.Success;
ret.data = chartobj;
}
catch (Exception ex)
{
ret.code = Code.Failure;
ret.msg = ex.Message;
}
return(ret);
}
