public ActionResult GetReportData(string reportId)
{
ReportTempEntity reportEntity = reportTempIBLL.GetEntity(reportId);
dynamic paramJson = reportEntity.F_ParamJson.ToJson();
var data = new
{
tempStyle = reportEntity.F_TempStyle,
chartType = reportEntity.F_TempType,
chartData = reportTempIBLL.GetReportData(paramJson.F_DataSourceId.ToString(), paramJson.F_ChartSqlString.ToString()),
listData = reportTempIBLL.GetReportData(paramJson.F_DataSourceId.ToString(), paramJson.F_ListSqlString.ToString())
};
return(Content(data.ToJson()));
}
public ActionResult SaveForm(string keyValue, ReportTempEntity entity)
{
reportTempIBLL.SaveEntity(keyValue, entity);
return(Success("保存成功！"));
}
public ActionResult GetReportData1(string reportId, string queryJson)
{
ReportTempEntity reportEntity = reportTempIBLL.GetEntity(reportId);
dynamic paramJson = reportEntity.F_ParamJson.ToJson();
string sql = paramJson.F_ChartSqlString;
string sql_chart = paramJson.F_ChartSqlString;
JObject jo = (JObject)JsonConvert.DeserializeObject(queryJson);
DateTime EndTime = jo["EndTime"].ToDate();
DateTime StartTime = jo["StartTime"].ToDate();
string machine_id = jo["machine_id"].ToString();
string group_id = jo["group_id"].ToString();
if (reportId == "40a0b5ef-9440-4ed9-97f8-1aec36642f0a")//小时产量统计
{
sql = "select sp.day_time as 小时,mi.group_name as 设备组,mi.machine_name as 设备号,sum(sp.prod_num) as 实际产量,"
+ " sum(sp.pass_prod_num) as 合格产量"
+ " from tb_curday_prod_num sp inner join tb_dict_parameter dp on sp.day_time = dp.data_setup"
+ " left join vw_machine_info mi on sp.group_id = mi.group_id and mi.machine_id = sp.machine_id"
+ " where dp.param_type = 'DayTime' and dp.prw_type = 'ToOut' and sp.is_stage = 'Hour' and sp.machine_id = " + machine_id + "and sp.group_id = " + group_id + ""
+ " and sp.wshift_date= '" + EndTime.ToString("yyyyMMdd") + "'group by day_time,mi.group_name,mi.machine_name,calc_date "
+ " order by sp.calc_date, sp.day_time asc";
sql_chart = "select sp.day_time as name,mi.group_name as 设备组,mi.machine_name as 设备号,sum(sp.prod_num) as value,sum(sp.plan_prod_num) as 计划产量,"
+ " sum(sp.pass_prod_num) as 合格产量"
+ " from tb_curday_prod_num sp inner join tb_dict_parameter dp on sp.day_time = dp.data_setup"
+ " left join vw_machine_info mi on sp.group_id = mi.group_id and mi.machine_id = sp.machine_id"
+ " where dp.param_type = 'DayTime' and dp.prw_type = 'ToOut' and sp.is_stage = 'Hour' and sp.machine_id = " + machine_id + "and sp.group_id = " + group_id + ""
+ " and sp.wshift_date= '" + EndTime.ToString("yyyyMMdd") + "'group by day_time,mi.group_name,mi.machine_name,calc_date "
+ " order by sp.calc_date, sp.day_time asc";
}
else if (reportId == "f43e6528-4f04-454f-9922-ead91c938add") //年产量统计
{
sql = " select LEFT(sp.wshift_date, 4) as 年 ,mi.group_name as 设备组, mi.machine_name as 设备号, "
+ " sum(tpd.plan_day) as 计划产量,sum(sp.prod_num) as 实际产量, sum(sp.pass_prod_num) as 合格产量"
+ " from tb_curday_prod_num sp"
+ " left"
+ " join vw_machine_info mi on sp.group_id = mi.group_id and mi.machine_id = sp.machine_id"
+ " left join tb_plan_day_data tpd on sp.calc_date = tpd.calc_date"
+ " where sp.machine_id = " + machine_id + ""
+ " and sp.is_stage = 'Day'"
+ " and sp.group_id =" + group_id + " and sp.wshift_date <= '" + EndTime.ToString("yyyyMMdd") + "' AND sp.wshift_date >= '" + StartTime.ToString("yyyyMMdd") + "'"
+ " group by LEFT(sp.wshift_date, 4),mi.machine_name,mi.group_name"
+ " order by LEFT(sp.wshift_date, 4) asc";
sql_chart = "select LEFT(sp.wshift_date, 4) as name ,sum(sp.prod_num) as value, "
+ " mi.machine_name as 设备号,mi.group_name as 设备组, sum(sp.plan_prod_num) as 计划产量,sum(sp.pass_prod_num) as 合格产量"
+ " from tb_curday_prod_num sp inner join tb_dict_parameter dp on sp.day_time = dp.data_setup"
+ " left join vw_machine_info mi on sp.group_id = mi.group_id and mi.machine_id = sp.machine_id"
+ " where dp.param_type = 'DayTime' and dp.prw_type = 'ToOut' and sp.is_stage = 'Hour' and sp.machine_id = " + machine_id + "and sp.group_id = " + group_id + ""
+ " and sp.wshift_date <= '" + EndTime.ToString("yyyyMMdd") + "' AND sp.wshift_date >= '" + StartTime.ToString("yyyyMMdd") + "'"
+ " group by LEFT(sp.wshift_date, 4),mi.machine_name,mi.group_name"
+ " order by name asc";
}
else if (reportId == "522af585-5f4c-47fe-a3aa-0930754fb03b")//日产量统计
{
sql = "select sp.wshift_date as 日期, Convert(nvarchar(50),wshift_date)+'-'+Convert(nvarchar(50),mi.machine_id) as 计划id,mi.group_name as 设备组, "
+ " mi.machine_name as 设备号,sum(sp.plan_prod_num) as 计划产量,sum(sp.prod_num) as 实际产量,sum(sp.pass_prod_num) as 合格产量"
+ " from tb_curday_prod_num sp inner join tb_dict_parameter dp on sp.day_time = dp.data_setup"
+ " left join vw_machine_info mi on sp.group_id = mi.group_id and mi.machine_id = sp.machine_id"
+ " where dp.param_type = 'DayTime' and dp.prw_type = 'ToOut' and sp.is_stage = 'Hour' and sp.machine_id = " + machine_id + "and sp.group_id = " + group_id + ""
+ " and sp.wshift_date <= '" + EndTime.ToString("yyyyMMdd") + "' AND sp.wshift_date >= '" + StartTime.ToString("yyyyMMdd") + "'"
+ " group by sp.wshift_date,mi.machine_name,mi.machine_id,mi.group_name order by wshift_date asc";
sql_chart = "select sp.wshift_date as name,sum(sp.prod_num) as value,"
+ " mi.machine_name as 设备号,mi.group_name as 设备组, sum(sp.plan_prod_num) as 计划产量,sum(sp.pass_prod_num) as 合格产量"
+ " from tb_curday_prod_num sp inner join tb_dict_parameter dp on sp.day_time = dp.data_setup"
+ " left join vw_machine_info mi on sp.group_id = mi.group_id and mi.machine_id = sp.machine_id"
+ " where dp.param_type = 'DayTime' and dp.prw_type = 'ToOut' and sp.is_stage = 'Hour' and sp.machine_id = " + machine_id + "and sp.group_id = " + group_id + ""
+ " and sp.wshift_date <= '" + EndTime.ToString("yyyyMMdd") + "' AND sp.wshift_date >= '" + StartTime.ToString("yyyyMMdd") + "'"
+ " group by sp.wshift_date,mi.machine_name,mi.group_name order by wshift_date asc";
}
else if (reportId == "6ac82b75-c508-4237-8ec9-bae1df8066cb")//月产量统计
{
sql = "select LEFT(sp.wshift_date, 6) as 月份 , mi.group_name as 设备组, "
+ " mi.machine_name as 设备号,sum(tpd.plan_day) as 计划产量,sum(sp.prod_num) as 实际产量,sum(sp.pass_prod_num) as 合格产量"
+ " from tb_curday_prod_num sp "
+ " left join vw_machine_info mi on sp.group_id = mi.group_id and mi.machine_id = sp.machine_id"
+ " left join tb_plan_day_data tpd on sp.calc_date=tpd.calc_date "
+ " where sp.is_stage = 'Day' and sp.machine_id = " + machine_id + "and sp.group_id = " + group_id + ""
+ " and sp.wshift_date <= '" + EndTime.ToString("yyyyMMdd") + "' AND sp.wshift_date >= '" + StartTime.ToString("yyyyMMdd") + "'"
+ " group by LEFT(sp.wshift_date, 6),mi.machine_name,mi.group_name"
+ " order by LEFT(sp.wshift_date, 6) asc";
sql_chart = "select LEFT(sp.wshift_date, 6) as name ,sum(sp.prod_num) as value, "
+ " mi.machine_name as 设备号,mi.group_name as 设备组, sum(sp.plan_prod_num) as 计划产量,sum(sp.pass_prod_num) as 合格产量"
+ " from tb_curday_prod_num sp inner join tb_dict_parameter dp on sp.day_time = dp.data_setup"
+ " left join vw_machine_info mi on sp.group_id = mi.group_id and mi.machine_id = sp.machine_id"
+ " where dp.param_type = 'DayTime' and dp.prw_type = 'ToOut' and sp.is_stage = 'Hour' and sp.machine_id = " + machine_id + "and sp.group_id = " + group_id + ""
+ " and sp.wshift_date <= '" + EndTime.ToString("yyyyMMdd") + "' AND sp.wshift_date >= '" + StartTime.ToString("yyyyMMdd") + "'"
+ " group by LEFT(sp.wshift_date, 6),mi.machine_name,mi.group_name"
+ " order by name asc";
}
else if (reportId == "8060e1eb-5286-4c22-97bd-07ebde2d4cc8")//班次产量统计
{
sql = "select Convert(nvarchar(50),wshift_date)+'-'+Convert(nvarchar(50),sp.wshift_id)+'-'+Convert(nvarchar(50),mi.group_id) as 人员, sp.wshift_date+b.wshift_name as 班次,mi.group_name as 设备组, "
+ " mi.machine_name as 设备号,sp.plan_prod_num as 计划产量,sp.prod_num as 实际产量,sp.pass_prod_num as 合格产量"
+ " from tb_curday_prod_num sp inner join tb_shift_info b on sp.wshift_id=b.wshift_id "
+ " left join vw_machine_info mi on sp.group_id = mi.group_id and mi.machine_id = sp.machine_id"
+ " where 1 = 1 and sp.is_stage = 'Shift' and sp.machine_id = " + machine_id + "and sp.group_id = " + group_id + ""
+ " and sp.wshift_date <='" + EndTime.ToString("yyyyMMdd") + "' AND sp.wshift_date >= '"
+ StartTime.ToString("yyyyMMdd") + "' order by wshift_date asc,b.start_time";
sql_chart = "select sp.wshift_date+b.wshift_name as name,sp.prod_num as value,"
+ " mi.machine_name as 设备号,mi.group_name as 设备组, sp.plan_prod_num as 计划产量,sp.pass_prod_num as 合格产量"
+ " from tb_curday_prod_num sp inner join tb_shift_info b on sp.wshift_id=b.wshift_id "
+ " left join vw_machine_info mi on sp.group_id = mi.group_id and mi.machine_id = sp.machine_id"
+ " where 1 = 1 and sp.is_stage = 'Shift' and sp.machine_id = " + machine_id + "and sp.group_id = " + group_id + ""
+ " and sp.wshift_date <='" + EndTime.ToString("yyyyMMdd") + "' AND sp.wshift_date >= '"
+ StartTime.ToString("yyyyMMdd") + "' order by wshift_date asc,b.start_time";
}
else if (reportId == "6628efa3-8ba5-47c7-be6a-58dd05df9661") //产量报表
{
sql = " select sum(tcp.prod_num)as prod_num,tcp.day_time,tcp.wshift_date,tcp.group_id,tcp.wshift_id"
+ " from tb_curday_prod_num tcp"
+ " where tcp.is_stage = 'Hour'"
+ " and wshift_date <='" + EndTime.ToString("yyyyMMdd") + "' AND"
+ " wshift_date >= '" + StartTime.ToString("yyyyMMdd") + "'"
+ " group by tcp.day_time,tcp.wshift_date,tcp.group_id,tcp.wshift_id"
+ " order by tcp.wshift_date desc";
}
else if (reportId == "bdcda43c-c277-400a-979f-a925270c9171")//OEE报表
{
sql = " select tcp.group_id ,tcp.prod_num,tcp.wshift_date,trs.time_second as run_time_second,"
+ " (case when tqi.unqualified_no is null then 0 else tqi.unqualified_no end) as unqualified_no"
+ " ,(case when tqi.unqualified_no is null then tcp.prod_num else (tcp.prod_num - tqi.unqualified_no) end )as qualified_no"
+ " from tb_curday_prod_num tcp"
+ " left join (select sum(trs.run_dur) as time_second ,trs.machine_id,trs.wshift_date"
+ " from tb_run_state_seque trs"
+ " where trs.run_state = '1'"
+ " group by trs.machine_id,trs.wshift_date"
+ " )trs on tcp.machine_id = trs.machine_id and tcp.wshift_date = trs.wshift_date"
+ " left join("
+ " select tqi.group_no, tqi.unqualified_no, convert(varchar(8) ,tqi.product_time, 112) as wshift_date"
+ " from tb_quantity_info tqi)tqi on tcp.wshift_date = tqi.wshift_date"
+ " where is_stage = 'Day'"
+ " and tcp.wshift_date <= '" + EndTime.ToString("yyyyMMdd") + "' and tcp.wshift_date >= '" + StartTime.ToString("yyyyMMdd") + "'"
+ " and tcp.machine_id = " + machine_id + ""
+ " order by tcp.wshift_date";
}
else if (reportId == "8d76ac76-fe7a-4b7f-af71-1250851d11df")//设备状态统计
{
if (machine_id == "0")
{
sql_chart = sql = "select a.*, isnull(b.value, 0) as value from("
+ " SELECT 1 as run_state, '运行' as name"
+ " union"
+ " SELECT 2 as run_state, '报警' as name"
+ " union"
+ " SELECT 3 as run_state, '暂停' as name"
+ " union"
+ " SELECT 4 as run_state, '空闲' as name"
+ " union"
+ " SELECT 0 as run_state, '停机' as name) a"
+ " left join("
+ " SELECT top 100 PERCENT run_state, case when run_state=1 then '运行' when run_state=2 then '报警' "
+ " when run_state = 3 then '暂停' when run_state=4 then '空闲' when run_state=0 then '停机' end as name,convert(varchar(50),"
+ " sum([run_dur])/60) as value"
+ " FROM tb_run_state_seque where calc_date <='" + EndTime.ToString("yyyyMMdd") + "' AND calc_date >= '"
+ StartTime.ToString("yyyyMMdd") + "' and group_id = " + group_id
+ " and run_state in (0, 1, 2, 3, 4)"
+ " group by run_state"
+ " ) b on a.name = b.name"
+ " order by case a.run_state"
+ " when '0' then 5"
+ " when '1' then 1"
+ " when '2' then 2"
+ " when '3' then 3"
+ " else 4"
+ " end";
}
else
{
sql_chart = sql = "select a.*, isnull(b.value, 0) as value from("
+ " SELECT 1 as run_state, '运行' as name"
+ " union"
+ " SELECT 2 as run_state, '报警' as name"
+ " union"
+ " SELECT 3 as run_state, '暂停' as name"
+ " union"
+ " SELECT 4 as run_state, '空闲' as name"
+ " union"
+ " SELECT 0 as run_state, '停机' as name) a"
+ " left join("
+ " SELECT top 100 PERCENT run_state, case when run_state=1 then '运行' when run_state=2 then '报警' "
+ " when run_state = 3 then '暂停' when run_state=4 then '空闲' when run_state=0 then '停机' end as name,convert(varchar(50),"
+ " sum([run_dur])/60) as value"
+ " FROM tb_run_state_seque where calc_date <='" + EndTime.ToString("yyyyMMdd") + "' AND calc_date >= '"
+ StartTime.ToString("yyyyMMdd") + "' and machine_id = " + machine_id
+ " and run_state in (0, 1, 2, 3, 4)"
+ " group by run_state"
+ " ) b on a.name = b.name"
+ " order by case a.run_state"
+ " when '0' then 5"
+ " when '1' then 1"
+ " when '2' then 2"
+ " when '3' then 3"
+ " else 4"
+ " end";
}
}
else if (reportId == "7cca72d4-92f0-4e3f-82e4-6b47a087e414")//报警信息统计-按报警号
{
if (machine_id != null && machine_id != "0")
{
sql_chart = sql = "select alarm_no as name,count(1) as value from tb_alarm_history_init where calc_date <='"
+ EndTime.ToString("yyyyMMdd") + "' AND calc_date >= '" + StartTime.ToString("yyyyMMdd") + "' and machine_id ="
+ machine_id + " group by alarm_no ";
}
else
{
sql_chart = sql = "select alarm_no as name,count(1) as value from tb_alarm_history_init where calc_date <='"
+ EndTime.ToString("yyyyMMdd") + "' AND calc_date >= '" + StartTime.ToString("yyyyMMdd") + "' group by alarm_no ";
}
}
//ac4baec2-0e81-470d-95ad-040ecca9cc5d&type=preview
else if (reportId == "ac4baec2-0e81-470d-95ad-040ecca9cc5d") //报警信息统计-按设备
{
sql_chart = sql = "select mi.machine_name as name,count(1) as value from tb_alarm_history_init sp"
+ " left join vw_machine_info mi on sp.group_id = mi.group_id and mi.machine_id = sp.machine_id"
+ " where calc_date <='"
+ EndTime.ToString("yyyyMMdd") + "' AND calc_date >= '" + StartTime.ToString("yyyyMMdd") + "' and sp.group_id ="
+ group_id + " group by mi.machine_name ";
}
else
{
/* //ZK
* JObject jo = (JObject)JsonConvert.DeserializeObject(queryJson);
* DateTime EndTime = jo["EndTime"].ToDate();
* DateTime StartTime = jo["StartTime"].ToDate();
* sql = " select sp.day_time as name," +
* " sp.group_id," +
* " sp.group_name," +
* " sp.prod_num as value," +
* " sp.plan_prod_num," +
* " sp.pass_prod_num" +
* " from tb_curday_prod_num sp," +
* " tb_dict_parameter dp where 1 = 1 and sp.day_time = dp.data_setup" +
* " and dp.param_type = 'DayTime' and dp.prw_type = 'ToOut' and sp.is_stage = 'Hour' and sp.machine_id = 11" +
* " and sp.calc_date <='" + EndTime.ToString("yyyyMMdd") + "' AND sp.calc_date >= '" + StartTime.ToString("yyyyMMdd") + "' order by dp.data_addr asc," +
* " sp.day_time asc";
* //paramJson.F_ChartSqlString = sql;
* //paramJson.F_ListSqlString = sql;
* //END;*/
}
if (reportId == "8060e1eb-5286-4c22-97bd-07ebde2d4cc8")//班次产量统计
{
var data = new
{
tempStyle = reportEntity.F_TempStyle,
chartType = reportEntity.F_TempType,
chartData = reportTempIBLL.GetReportData(paramJson.F_DataSourceId.ToString(), sql_chart),
listData = reportTempIBLL.GetReportData2(paramJson.F_DataSourceId.ToString(), sql)
};
return(Content(data.ToJson()));
}
else if (reportId == "522af585-5f4c-47fe-a3aa-0930754fb03b")//日产量统计
{
var data = new
{
tempStyle = reportEntity.F_TempStyle,
chartType = reportEntity.F_TempType,
chartData = reportTempIBLL.GetReportData(paramJson.F_DataSourceId.ToString(), sql_chart),
listData = reportTempIBLL.GetReportData3(paramJson.F_DataSourceId.ToString(), sql)
};
return(Content(data.ToJson()));
}
else if (reportId == "6628efa3-8ba5-47c7-be6a-58dd05df9661") //产量报表
{
var data = new
{
tempStyle = reportEntity.F_TempStyle,
chartType = reportEntity.F_TempType,
listData = reportTempIBLL.GetReportData4(paramJson.F_DataSourceId.ToString(), sql)
};
return(Content(data.ToJson()));
}
else if (reportId == "bdcda43c-c277-400a-979f-a925270c9171")//OEE报表
{
var data = new
{
tempStyle = reportEntity.F_TempStyle,
chartType = reportEntity.F_TempType,
listData = reportTempIBLL.GetReportDataOEE(paramJson.F_DataSourceId.ToString(), sql)
};
return(Content(data.ToJson()));
}
else
{
var data = new
{
tempStyle = reportEntity.F_TempStyle,
chartType = reportEntity.F_TempType,
chartData = reportTempIBLL.GetReportData(paramJson.F_DataSourceId.ToString(), sql_chart),
listData = reportTempIBLL.GetReportData(paramJson.F_DataSourceId.ToString(), sql)
};
return(Content(data.ToJson()));
}
}
