private void GridBindChartAllLine()
{
production_controller_dgv.DataSource = null;
try
{
ProductionControllerVo vo = new ProductionControllerVo
{
//ProLine = line_cmb.Text,
StartDay = DateTime.Parse(timefrom_dtp.Value.ToShortDateString()),
//EndDay = timeto_dtp.Value
};
ValueObjectList <ProductionControllerVo> volist = (ValueObjectList <ProductionControllerVo>)DefaultCbmInvoker.Invoke(new SearchMainProChartAllLineCbm(), vo);
if (volist.GetList() != null && volist.GetList().Count > 0)
{
production_controller_dgv.AutoGenerateColumns = false;
BindingSource bindingsource = new BindingSource(volist.GetList(), null);
production_controller_dgv.DataSource = bindingsource;
}
else
{
messageData = new MessageData("mmci00006", Properties.Resources.mmci00006, null);
logger.Info(messageData);
popUpMessage.Information(messageData, Text);
}
production_controller_dgv.ClearSelection();
}
catch (Framework.ApplicationException exception)
{
popUpMessage.ApplicationError(exception.GetMessageData(), Text);
logger.Error(exception.GetMessageData());
}
}
private void GridBindHour()
{
production_controller_dgv.DataSource = null;
try
{
ProductionControllerVo vo = new ProductionControllerVo
{
ProLine = line_cmb.Text,
Date = timefrom_dtp.Text,
ProModel = model_cmb.Text,
};
ValueObjectList <ProductionControllerVo> volist = (ValueObjectList <ProductionControllerVo>)DefaultCbmInvoker.Invoke(new SearchMainProByHourCbm(), vo);
if (volist.GetList() != null && volist.GetList().Count > 0)
{
production_controller_dgv.AutoGenerateColumns = false;
BindingSource bindingsource = new BindingSource(volist.GetList(), null);
production_controller_dgv.DataSource = bindingsource;
}
else
{
messageData = new MessageData("mmci00006", Properties.Resources.mmci00006, null);
logger.Info(messageData);
popUpMessage.Information(messageData, Text);
}
production_controller_dgv.ClearSelection();
}
catch (Framework.ApplicationException exception)
{
popUpMessage.ApplicationError(exception.GetMessageData(), Text);
logger.Error(exception.GetMessageData());
}
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select distinct model_cd from t_productioncontroller_output04 order by model_cd ");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
ProModel = dataReader["model_cd"].ToString(),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select Case When times between '06:00:00' and '23:59:00' then dates when ");
sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss,'LA459' model_cd,'All Line' line_cd, sum(ra_com_pb_sticky) ra_com_pb_sticky, ");
sql.Append("sum(ra_wire_pb_sticky) ra_wire_pb_sticky, sum(ra_com_slip) ra_com_slip ,");
sql.Append("sum(ra_renew_ring) ra_renew_ring, sum(ra_break_wire_final_app) ra_break_wire_final_app, ");
sql.Append("sum(ra_wire_combine_wrong) ra_wire_combine_wrong, ");
sql.Append("sum(ra_core_ng) ra_core_ng, sum(ra_segment_hole) ra_segment_hole, ");
sql.Append("sum(ra_glue_sticky) ra_glue_sticky, sum(ra_loose_wire_final_app) ra_loose_wire_final_app, ");
sql.Append("sum(ra_lead_not_covered) ra_lead_not_covered,");
sql.Append("sum(ra_less_lead) ra_less_lead from ");
sql.Append("(select i2.dates,i2.times,i2.line_cd, ra_com_pb_sticky, ra_wire_pb_sticky, ra_com_slip, ra_renew_ring, ra_break_wire_final_app, ra_wire_combine_wrong, ra_core_ng, ra_segment_hole, ra_glue_sticky, ra_loose_wire_final_app, ra_lead_not_covered, ra_less_lead from t_productioncontroller_output01 i2 left join (select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id from(select tblca1.dates, tblca1.line_cd, idca1, idca3 from(select line_cd, o.dates, max(o.output01_id) idca1 from t_productioncontroller_output01 o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 left join(select line_cd, (o.dates - 1) dates, max(o.output01_id) idca3 from t_productioncontroller_output01 o where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl order by dates, line_cd) l on l.line_cd = i2.line_cd where i2.output01_id = l.id order by i2.dates,i2.line_cd ) t group by datesss order by datesss");
sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
//sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
RA_com_pb_sticky = int.Parse(dataReader["ra_com_pb_sticky"].ToString()),
RA_wire_pb_sticky = int.Parse(dataReader["ra_wire_pb_sticky"].ToString()),
RA_com_slip = int.Parse(dataReader["ra_com_slip"].ToString()),
RA_renew_ring = int.Parse(dataReader["ra_renew_ring"].ToString()),
RA_break_wire_final_app = int.Parse(dataReader["ra_break_wire_final_app"].ToString()),
RA_wire_combine_wrong = int.Parse(dataReader["ra_wire_combine_wrong"].ToString()),
RA_core_ng = int.Parse(dataReader["ra_core_ng"].ToString()),
RA_segment_hole = int.Parse(dataReader["ra_segment_hole"].ToString()),
RA_glue_sticky = int.Parse(dataReader["ra_glue_sticky"].ToString()),
RA_loose_wire_final_app = int.Parse(dataReader["ra_loose_wire_final_app"].ToString()),
RA_lead_not_covered = int.Parse(dataReader["ra_lead_not_covered"].ToString()),
RA_less_lead = int.Parse(dataReader["ra_less_lead"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select Case When times between '06:00:00' and '23:59:00' then dates when ");
sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss,'LA459' model_cd,'All Line' line_cd, sum(en2_lock) en2_lock, ");
sql.Append("sum(en2_cut) en2_cut, sum(en2_chattering) en2_chattering ,");
sql.Append("sum(en2_insulation) en2_insulation, sum(en2_open) en2_open, ");
sql.Append("sum(en2_short) en2_short, ");
sql.Append("sum(en2_duty) en2_duty, sum(en2_no) en2_no, ");
sql.Append("sum(en2_var) en2_var, sum(en2_reverse_spinning) en2_reverse_spinning, ");
sql.Append("sum(en2_starting_volt) en2_starting_volt, sum(en2_io ) en2_io from ");
sql.Append("(select i2.dates,i2.times,i2.line_cd, en2_lock, en2_cut, en2_chattering , en2_insulation , en2_open , en2_short , en2_duty , en2_no , en2_var , en2_reverse_spinning , en2_starting_volt , en2_io from t_productioncontroller_output03 i2 left join (select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id from(select tblca1.dates, tblca1.line_cd, idca1, idca3 from(select line_cd, o.dates, max(o.output03_id) idca1 from t_productioncontroller_output03 o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 left join(select line_cd, (o.dates - 1) dates, max(o.output03_id) idca3 from t_productioncontroller_output03 o where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl order by dates, line_cd) l on l.line_cd = i2.line_cd where i2.output03_id = l.id order by i2.dates,i2.line_cd ) t group by datesss order by datesss");
sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
//sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
En2_lock = int.Parse(dataReader["en2_lock"].ToString()),
En2_cut = int.Parse(dataReader["en2_cut"].ToString()),
En2_chattering = int.Parse(dataReader["en2_chattering"].ToString()),
En2_insulation = int.Parse(dataReader["en2_insulation"].ToString()),
En2_open = int.Parse(dataReader["en2_open"].ToString()),
En2_short = int.Parse(dataReader["en2_short"].ToString()),
En2_duty = int.Parse(dataReader["en2_duty"].ToString()),
En2_no = int.Parse(dataReader["en2_no"].ToString()),
En2_var = int.Parse(dataReader["en2_var"].ToString()),
En2_reverse_spinning = int.Parse(dataReader["en2_reverse_spinning"].ToString()),
En2_starting_volt = int.Parse(dataReader["en2_starting_volt"].ToString()),
En2_io = int.Parse(dataReader["en2_io"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select Case When times between '06:00:00' and '23:59:00' then dates when ");
sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss,'LA459' model_cd,'All Line' line_cd, sum(insc_no_ink_case_mc1) insc_no_ink_case_mc1, ");
sql.Append("sum(insc_ba_deform_mc1) insc_ba_deform_mc1, sum(insc_break_case_mc1) insc_break_case_mc1 ,");
sql.Append("sum(insc_drop_mc1) insc_drop_mc1, sum(insc_break_wire_mc1) insc_break_wire_mc1, ");
sql.Append("sum(insc_break_ring_mc1) insc_break_ring_mc1 from ");
sql.Append("(select i2.dates,i2.times,i2.line_cd, insc_no_ink_case_mc1, insc_ba_deform_mc1, insc_break_case_mc1, insc_drop_mc1, insc_break_wire_mc1, insc_break_ring_mc1 from t_productioncontroller_output01 i2 left join (select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id from(select tblca1.dates, tblca1.line_cd, idca1, idca3 from(select line_cd, o.dates, max(o.output01_id) idca1 from t_productioncontroller_output01 o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 left join(select line_cd, (o.dates - 1) dates, max(o.output01_id) idca3 from t_productioncontroller_output01 o where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl order by dates, line_cd) l on l.line_cd = i2.line_cd where i2.output01_id = l.id order by i2.dates,i2.line_cd ) t group by datesss order by datesss");
sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
//sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
Insc_no_ink_case_mc1 = int.Parse(dataReader["insc_no_ink_case_mc1"].ToString()),
Insc_ba_deform_mc1 = int.Parse(dataReader["insc_ba_deform_mc1"].ToString()),
Insc_break_case_mc1 = int.Parse(dataReader["insc_break_case_mc1"].ToString()),
Insc_drop_mc1 = int.Parse(dataReader["insc_drop_mc1"].ToString()),
Insc_break_wire_mc1 = int.Parse(dataReader["insc_break_wire_mc1"].ToString()),
Insc_break_ring_mc1 = int.Parse(dataReader["insc_break_ring_mc1"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
void outputthucte()
{
ProductionControllerVo invo = new ProductionControllerVo
{
ProModel = model_cmb.Text,
ProLine = line_cmb.Text,
Date = datetime_dtp.Value.ToShortDateString(),
};
ValueObjectList <ProductionControllerVo> output = (ValueObjectList <ProductionControllerVo>)DefaultCbmInvoker.Invoke(new SearchPersonOutputCbm(), invo);
output_cmb.DisplayMember = "ProOutput";
output_cmb.DataSource = output.GetList();
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select case when max(output_data) is null then 0 else max(output_data) end output from t_productioncontroller_output04 ");
sql.Append(" where 1=1 ");
sql.Append(@" and dates =:dates");
sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
if (!String.IsNullOrEmpty(inVo.ProModel))
{
sql.Append(" and model_cd =:model_cd");
sqlParameter.AddParameterString("model_cd", inVo.ProModel);
}
if (!String.IsNullOrEmpty(inVo.ProLine))
{
sql.Append(" and line_cd =:line_cd");
sqlParameter.AddParameterString("line_cd", inVo.ProLine);
}
//sql.Append(" order by datetimes");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
// , h., i., k., o.prodution_work_content_name
ProOutput = int.Parse(dataReader["output"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select Case When times between '06:00:00' and '23:59:00' then dates when ");
sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss,'LA459' model_cd,'All Line' line_cd, sum(fiapp_stamping_ba) fiapp_stamping_ba, ");
sql.Append("sum(fiapp_case_set) fiapp_case_set, sum(fiapp_tough_shaft) fiapp_tough_shaft ,");
sql.Append("sum(fiapp_case_glue_sticky) fiapp_case_glue_sticky, sum(fiapp_up_low_shabby) fiapp_up_low_shabby, ");
sql.Append("sum(fiapp_hole_shaft) fiapp_hole_shaft, ");
sql.Append("sum(fiapp_no_beat_prone_case) fiapp_no_beat_prone_case, sum(fiapp_hole_case) fiapp_hole_case, ");
sql.Append("sum(fiapp_prone_case) fiapp_prone_case, sum(fiapp_lot_ng) fiapp_lot_ng, ");
sql.Append("sum(fiapp_ter_deform) fiapp_ter_deform, sum(fiapp_hole_ter ) fiapp_hole_ter, ");
sql.Append("sum(fiapp_soder_hl) fiapp_soder_hl, sum(fiapp_metal_oven_low) fiapp_metal_oven_low, ");
sql.Append("sum(fiapp_fundou_ng) fiapp_fundou_ng, sum(fiapp_ter_glue_sticky) fiapp_ter_glue_sticky, ");
sql.Append("sum(fiapp_lead_glue_sticky) fiapp_lead_glue_sticky from ");
sql.Append("(select i2.dates,i2.times,i2.line_cd, fiapp_stamping_ba , fiapp_case_set, fiapp_tough_shaft, fiapp_case_glue_sticky, fiapp_up_low_shabby, fiapp_hole_shaft, fiapp_no_beat_prone_case, fiapp_hole_case, fiapp_prone_case, fiapp_lot_ng, fiapp_ter_deform, fiapp_hole_ter, fiapp_soder_hl, fiapp_metal_oven_low, fiapp_fundou_ng, fiapp_ter_glue_sticky, fiapp_lead_glue_sticky from t_productioncontroller_output04 i2 left join (select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id from(select tblca1.dates, tblca1.line_cd, idca1, idca3 from(select line_cd, o.dates, max(o.output04_id) idca1 from t_productioncontroller_output04 o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 left join(select line_cd, (o.dates - 1) dates, max(o.output04_id) idca3 from t_productioncontroller_output04 o where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl order by dates, line_cd) l on l.line_cd = i2.line_cd where i2.output04_id = l.id order by i2.dates,i2.line_cd ) t group by datesss order by datesss");
sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
//sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
App_stamping_ba = int.Parse(dataReader["fiapp_stamping_ba"].ToString()),
App_case_set = int.Parse(dataReader["fiapp_case_set"].ToString()),
App_tough_shaft = int.Parse(dataReader["fiapp_tough_shaft"].ToString()),
App_case_glue_sticky = int.Parse(dataReader["fiapp_case_glue_sticky"].ToString()),
App_up_low_shabby = int.Parse(dataReader["fiapp_up_low_shabby"].ToString()),
App_hole_shaft = int.Parse(dataReader["fiapp_hole_shaft"].ToString()),
App_no_beat_prone_case = int.Parse(dataReader["fiapp_no_beat_prone_case"].ToString()),
App_hole_case = int.Parse(dataReader["fiapp_hole_case"].ToString()),
App_prone_case = int.Parse(dataReader["fiapp_prone_case"].ToString()),
App_lot_ng = int.Parse(dataReader["fiapp_lot_ng"].ToString()),
App_ter_deform = int.Parse(dataReader["fiapp_ter_deform"].ToString()),
App_hole_ter = int.Parse(dataReader["fiapp_hole_ter"].ToString()),
App_soder_hl = int.Parse(dataReader["fiapp_soder_hl"].ToString()),
App_metal_oven_low = int.Parse(dataReader["fiapp_metal_oven_low"].ToString()),
App_fundou_ng = int.Parse(dataReader["fiapp_fundou_ng"].ToString()),
App_ter_glue_sticky = int.Parse(dataReader["fiapp_ter_glue_sticky"].ToString()),
App_lead_glue_sticky = int.Parse(dataReader["fiapp_lead_glue_sticky"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select (o4.dates+o4.times) datetimes, o4.model_cd,o4.line_cd, o4.process_cd, ");
sql.Append("ra_com_pb_sticky, ra_wire_pb_sticky, ra_com_slip, ra_renew_ring, ra_break_wire_final_app, ra_wire_combine_wrong, ");
sql.Append("ra_core_ng, ra_segment_hole, ra_glue_sticky, ra_loose_wire_final_app, ra_lead_not_covered, ra_less_lead ");
sql.Append("from t_productioncontroller_output01 o4 ");
sql.Append("where o4.line_cd = :line_cd ");
sql.Append("and o4.dates = :dates ");
sql.Append("and (o4.times in(select min(times) from t_productioncontroller_output01 where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sql.Append("or o4.dates-1 =:dates and o4.line_cd = :line_cd ");
sql.Append("and (o4.times in(select min(times) from t_productioncontroller_output01 where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '05:00:00' and '05:35:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sqlParameter.AddParameterString("line_cd", inVo.ProLine);
sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
RA_com_pb_sticky = int.Parse(dataReader["ra_com_pb_sticky"].ToString()),
RA_wire_pb_sticky = int.Parse(dataReader["ra_wire_pb_sticky"].ToString()),
RA_com_slip = int.Parse(dataReader["ra_com_slip"].ToString()),
RA_renew_ring = int.Parse(dataReader["ra_renew_ring"].ToString()),
RA_break_wire_final_app = int.Parse(dataReader["ra_break_wire_final_app"].ToString()),
RA_wire_combine_wrong = int.Parse(dataReader["ra_wire_combine_wrong"].ToString()),
RA_core_ng = int.Parse(dataReader["ra_core_ng"].ToString()),
RA_segment_hole = int.Parse(dataReader["ra_segment_hole"].ToString()),
RA_glue_sticky = int.Parse(dataReader["ra_glue_sticky"].ToString()),
RA_loose_wire_final_app = int.Parse(dataReader["ra_loose_wire_final_app"].ToString()),
RA_lead_not_covered = int.Parse(dataReader["ra_lead_not_covered"].ToString()),
RA_less_lead = int.Parse(dataReader["ra_less_lead"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
string sqlApp = "(fiapp_stamping_ba + fiapp_case_set + fiapp_tough_shaft + fiapp_case_glue_sticky + fiapp_up_low_shabby + fiapp_hole_shaft + fiapp_no_beat_prone_case + fiapp_hole_case + fiapp_prone_case + fiapp_lot_ng + fiapp_ter_deform + fiapp_hole_ter + fiapp_soder_hl + fiapp_metal_oven_low + fiapp_fundou_ng + fiapp_ter_glue_sticky + fiapp_lead_glue_sticky )";
string sqlEn2 = "(en2_lock + en2_cut + en2_chattering + en2_insulation + en2_open + en2_short + en2_duty + en2_no + en2_var + en2_reverse_spinning + en2_starting_volt + en2_io)";
string sqlFundou = "(fd_ng_beat_point + fd_fundou_deform)";
string sqlEn1 = "(en1_lock + en1_cut + en1_chattering + en1_insulation + en1_open + en1_bad_wave + en1_duty + en1_short + en1_beat_case_ng + en1_beat_fundou_ng )";
string sqlIS = "(insc_no_ink_case_mc1 + insc_ba_deform_mc1 + insc_break_case_mc1 + insc_drop_mc1 + insc_break_wire_mc1 + insc_break_ring_mc1)";
string sqlRA = "(ra_com_pb_sticky + ra_wire_pb_sticky + ra_com_slip + ra_renew_ring + ra_break_wire_final_app + ra_wire_combine_wrong + ra_core_ng + ra_segment_hole + ra_glue_sticky + ra_loose_wire_final_app + ra_lead_not_covered + ra_less_lead )";
string sqlSW = "(pbs_break_copper + pbs_climb_core + pbs_skip_edge + pbs_wire_combine_wrong + pbs_loose_wire + pbs_rizer_edge_ng + pbs_core_ng + pbs_com_slip + pbs_hole + pbs_2_sleeve + pbs_wire_pb_sticky + pbs_com_pb_sticky + pbs_no_lead)";
string sqlRing = "(rigs_wire_pb_sticky + rigs_com_pb_sticky + rigs_ring_prone + rigs_cracked_ring)";
string sqlWE = "(we_com_slip + we_long_shaft + we_short_shaft)";
string sqlWi = "(wi_break_copper_mc + wi_ruffle_copper_mc + wi_edge_ng_mc + wi_no_sleeve_mc)";
string sqlCore = "(co_beat_core_ng + co_com_wrap + co_core_ng + co_com_glue_sticky)";
/*sql.Append(@"select (o4.dates -1) as starday,(o4.dates + o4.times) as endday,o4.model_cd,o4.line_cd, o4.process_cd, ");
* sql.Append("input_data, output_data, (hol_gap_holder + " + sqlApp + "+" + sqlEn2 + "+" + sqlFundou + "+" + sqlEn1 + "+" + sqlIS + "+" + sqlRA + "+" + sqlSW + "+" + sqlRing + "+" + sqlWE + "+" + sqlWi + "+" + sqlCore + ") as sum_ng,");
* sql.Append("hol_gap_holder as holder, " + sqlApp + " as app, " + sqlEn2 + " as en2, " + sqlFundou + " as fundou, " + sqlEn1 + " as en1, ");
* sql.Append(sqlIS + " as insert_case, " + sqlRA + " as ra, " + sqlSW + " as solder_wire, " + sqlRing + " as solder_ring, " + sqlWi + " as wingding,");
* sql.Append(sqlWE + " as welding," + sqlCore + " as core ");
* sql.Append("from t_productioncontroller_output04 o4, t_productioncontroller_output03 o3, t_productioncontroller_output02 o2, t_productioncontroller_output01 o1, ");
* sql.Append("t_productioncontroller_input02 i2, t_productioncontroller_input01 i1, ");
* sql.Append("(select o.dates , max(o.output04_id) id from t_productioncontroller_output04 o where o.times > '00:00:00' and o.times <= '05:50:00' ");
* sql.Append("and o.dates-1 >= :datesfrom and o.dates-1 <= :datesto and o.line_cd = :line_cd group by o.dates order by id ) tbltam ");
* sql.Append("where o4.line_cd = :line_cd and o3.line_cd = :line_cd and o2.line_cd = :line_cd and o1.line_cd = :line_cd and i2.line_cd = :line_cd and i1.line_cd = :line_cd ");
* sql.Append(" and tbltam.id = o4.output04_id and tbltam.id = o3.output03_id and tbltam.id = o2.output02_id and tbltam.id = o1.output01_id and ");
* sql.Append("tbltam.id = i1.input01_id and tbltam.id = i2.input02_id "); */
//tbl output
sql.Append("select Case When tblout.times between '06:00:00' and '23:59:00' then tblout.dates when tblout.times between '00:00:00' and '05:59:00' then tblout.dates-1 end starday, ");
sql.Append("(tblout.dates + tblout.times) endday, tblout.model_cd, tblout.line_cd,input_data, output_data, ");
sql.Append("(holder+ app+ en2+ fundou+ en1+ insert_case+ra+ solder_wire+ solder_ring+ wingding+ welding+ core) sum_ng, ");
sql.Append("holder, app, en2, fundou, en1, insert_case,ra, solder_wire, solder_ring, wingding, welding, core ");
sql.Append("from ");
sql.Append("(select ROW_NUMBER() OVER(ORDER BY o4.dates,o4.times ASC) rowo,o4.dates, o4.times, o4.model_cd, o4.line_cd, output_data, hol_gap_holder as holder, ");
sql.Append(sqlApp + " as app, " + sqlEn2 + " as en2, " + sqlFundou + " as fundou, " + sqlEn1 + " as en1, " + sqlIS + " as insert_case, " + sqlRA + " as ra ");
sql.Append("from t_productioncontroller_output04 o4 left join t_productioncontroller_output03 o3 on o4.line_cd = o3.line_cd and o4.output04_id = o3.output03_id ");
sql.Append("left join t_productioncontroller_output02 o2 on o4.line_cd = o2.line_cd and o4.output04_id = o2.output02_id ");
sql.Append("left join t_productioncontroller_output01 o1 on o4.line_cd = o1.line_cd and o4.output04_id = o1.output01_id ");
sql.Append("left join (select dates,line_cd, ");
sql.Append("Case when idca3 is null then idca1 else idca3 end id ");
sql.Append("from ");
sql.Append("(select tblca1.dates,tblca1.line_cd, idca1, idca3 ");
sql.Append("from ");
sql.Append("(select line_cd,o.dates , max(o.output04_id) idca1 ");
sql.Append("from t_productioncontroller_output04 o where line_cd in(:line_cd) and o.times > '06:00:00' and o.times <= '23:59:00' ");
sql.Append("and o.dates >= :datesfrom and o.dates <= :datesto group by o.dates, line_cd order by dates) tblca1 ");
sql.Append("left join ");
sql.Append("(select line_cd,(o.dates-1) dates , max(o.output04_id) idca3 ");
sql.Append("from t_productioncontroller_output04 o where line_cd in(:line_cd) and o.times > '00:00:00' and o.times <= '05:30:00' ");
sql.Append("and o.dates > :datesfrom and o.dates - 1 <= :datesto group by line_cd,o.dates order by idca3) tblca3 ");
sql.Append("on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) ");
//sql.Append("on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) ");
sql.Append("tbl order by dates,line_cd) l on l.line_cd = o4.line_cd where l.id = o4.output04_id order by o4.dates, o4.line_cd) tblout ");
//end tbl output
//tbl input
sql.Append("left join ");
sql.Append("(select ROW_NUMBER() OVER(ORDER BY i1.dates,i1.times ASC) rowi, i1.dates,i1.times,i1.model_cd,i1.line_cd, input_data,");
sql.Append(sqlSW + " as solder_wire, " + sqlRing + " as solder_ring, " + sqlWi + " as wingding, " + sqlWE + " as welding, " + sqlCore + " as core ");
sql.Append("from t_productioncontroller_input01 i1 left join t_productioncontroller_input02 i2 on i1.line_cd = i2.line_cd and i1.input01_id = i2.input02_id ");
sql.Append("left join ");
sql.Append("(select dates,line_cd, ");
sql.Append("Case when idca3 is null then idca1 else idca3 end id ");
sql.Append("from ");
sql.Append("(select tblca1.dates,tblca1.line_cd, idca1, idca3 ");
sql.Append("from ");
sql.Append("(select line_cd,o.dates , max(o.input01_id) idca1 from t_productioncontroller_input01 o ");
sql.Append("where line_cd in(:line_cd) and o.times > '06:00:00' and o.times <= '23:59:00' ");
sql.Append("and o.dates >= :datesfrom and o.dates <= :datesto group by o.dates, line_cd order by dates) tblca1 ");
sql.Append("left join ");
sql.Append("(select line_cd,(o.dates-1) dates , max(o.input01_id) idca3 from t_productioncontroller_input01 o ");
sql.Append("where line_cd in(:line_cd) and o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datesfrom and o.dates - 1 <= :datesto ");
sql.Append("group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) ");
sql.Append("tbl order by dates,line_cd) l on l.line_cd = i1.line_cd ");
sql.Append("where i1.input01_id = l.id order by i1.dates,i1.line_cd ) tblin on tblout.rowo = tblin.rowi");
sqlParameter.AddParameterString("line_cd", inVo.ProLine);
sqlParameter.AddParameterDateTime("datesfrom", inVo.StartDay);
sqlParameter.AddParameterDateTime("datesto", inVo.EndDay);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
StartDay = DateTime.Parse(dataReader["starday"].ToString()),
EndDay = DateTime.Parse(dataReader["endday"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
TotalNG = int.Parse(dataReader["sum_ng"].ToString()),
ProInput = int.Parse(dataReader["input_data"].ToString()),
ProOutput = int.Parse(dataReader["output_data"].ToString()),
HolderNG = int.Parse(dataReader["holder"].ToString()),
AppCheckNG = int.Parse(dataReader["app"].ToString()),
En2NG = int.Parse(dataReader["en2"].ToString()),
FundouNG = int.Parse(dataReader["fundou"].ToString()),
En1NG = int.Parse(dataReader["en1"].ToString()),
InsertCaseNG = int.Parse(dataReader["insert_case"].ToString()),
RANG = int.Parse(dataReader["ra"].ToString()),
SolderWireNG = int.Parse(dataReader["solder_wire"].ToString()),
SolderRingNG = int.Parse(dataReader["solder_ring"].ToString()),
WindingNG = int.Parse(dataReader["wingding"].ToString()),
WeldingNG = int.Parse(dataReader["welding"].ToString()),
CoreNG = int.Parse(dataReader["core"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select (o4.dates+o4.times) datetimes, o4.model_cd,o4.line_cd, o4.process_cd, ");
sql.Append("en1_lock, en1_cut, en1_chattering, en1_insulation, en1_open, ");
sql.Append("en1_bad_wave, en1_duty, en1_short, en1_beat_case_ng, en1_beat_fundou_ng ");
sql.Append("from t_productioncontroller_output02 o4 ");
sql.Append("where o4.line_cd = :line_cd ");
sql.Append("and o4.dates = :dates ");
sql.Append("and (o4.times in(select min(times) from t_productioncontroller_output02 where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sql.Append("or o4.dates-1 =:dates and o4.line_cd = :line_cd ");
sql.Append("and (o4.times in(select min(times) from t_productioncontroller_output02 where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output02 where times between '05:00:00' and '05:35:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sqlParameter.AddParameterString("line_cd", inVo.ProLine);
sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
En1_lock = int.Parse(dataReader["en1_lock"].ToString()),
En1_cut = int.Parse(dataReader["en1_cut"].ToString()),
En1_chattering = int.Parse(dataReader["en1_chattering"].ToString()),
En1_insulation = int.Parse(dataReader["en1_insulation"].ToString()),
En1_open = int.Parse(dataReader["en1_open"].ToString()),
En1_bad_wave = int.Parse(dataReader["en1_bad_wave"].ToString()),
En1_duty = int.Parse(dataReader["en1_duty"].ToString()),
En1_short = int.Parse(dataReader["en1_short"].ToString()),
En1_beat_case_ng = int.Parse(dataReader["en1_beat_case_ng"].ToString()),
En1_beat_fundou_ng = int.Parse(dataReader["en1_beat_fundou_ng"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select tblout.datetimes,'All Line' model_cd, tblout.line_cd,input_data,output_data,(holder + app + en2 + fundou + en1 + isca + ra + core + welding + wingding + ring + solder_wire) as sum_ng, ");
sql.Append("holder, app, en2, fundou, en1, isca, ra, core, welding, wingding, ring, solder_wire ");
sql.Append("from ");
sql.Append("(select min, t.dates + min datetimes, h, t.line_cd, output_data, hol_gap_holder holder, ");
sql.Append("(fiapp_stamping_ba + fiapp_case_set + fiapp_tough_shaft + fiapp_case_glue_sticky + fiapp_up_low_shabby + fiapp_hole_shaft + fiapp_no_beat_prone_case + fiapp_hole_case + fiapp_prone_case + ");
sql.Append("fiapp_lot_ng + fiapp_ter_deform + fiapp_hole_ter + fiapp_soder_hl + fiapp_metal_oven_low + fiapp_fundou_ng + fiapp_ter_glue_sticky + fiapp_lead_glue_sticky) app, ");
sql.Append("( en2_lock + en2_cut + en2_chattering + en2_insulation + en2_open + en2_short + en2_duty + en2_no + en2_var + en2_reverse_spinning + en2_starting_volt + en2_io) en2, ");
sql.Append("(fd_ng_beat_point + fd_fundou_deform) fundou, (en1_lock + en1_cut + en1_chattering + en1_insulation + en1_open + en1_bad_wave + en1_duty + en1_short + en1_beat_case_ng + en1_beat_fundou_ng) en1, ");
sql.Append("(insc_no_ink_case_mc1 + insc_ba_deform_mc1 + insc_break_case_mc1 + insc_drop_mc1 + insc_break_wire_mc1 + insc_break_ring_mc1) isca, ");
sql.Append("(ra_com_pb_sticky + ra_wire_pb_sticky + ra_com_slip + ra_renew_ring + ra_break_wire_final_app + ra_wire_combine_wrong + ra_core_ng + ra_segment_hole + ");
sql.Append("ra_glue_sticky + ra_loose_wire_final_app + ra_lead_not_covered + ra_less_lead) ra ");
sql.Append("from t_productioncontroller_output04 o4, t_productioncontroller_output03 o3, t_productioncontroller_output02 o2, t_productioncontroller_output01 o1, ");
sql.Append("(select dates,'06:00' h, min(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '06:00:00' and '06:55:00' group by line_cd, dates union ");
sql.Append("select dates,'07:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '06:00:00' and '07:05:00' group by line_cd, dates union ");
sql.Append("select dates,'08:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '07:00:00' and '08:05:00' group by line_cd, dates union ");
sql.Append("select dates,'09:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '08:00:00' and '09:05:00' group by line_cd, dates union ");
sql.Append("select dates,'10:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '09:00:00' and '10:05:00' group by line_cd, dates union ");
sql.Append("select dates,'11:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '10:00:00' and '11:05:00' group by line_cd, dates union ");
sql.Append("select dates,'12:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '11:00:00' and '12:05:00' group by line_cd, dates union ");
sql.Append("select dates,'13:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '12:00:00' and '13:05:00' group by line_cd, dates union ");
sql.Append("select dates,'14:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '13:00:00' and '14:05:00' group by line_cd, dates union ");
sql.Append("select dates,'15:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '14:00:00' and '15:05:00' group by line_cd, dates union ");
sql.Append("select dates,'16:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '15:00:00' and '16:05:00' group by line_cd, dates union ");
sql.Append("select dates,'17:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '16:00:00' and '17:05:00' group by line_cd, dates union ");
sql.Append("select dates,'18:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '17:00:00' and '18:05:00' group by line_cd, dates union ");
sql.Append("select dates,'19:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '18:00:00' and '19:05:00' group by line_cd, dates union ");
sql.Append("select dates,'20:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '19:00:00' and '20:05:00' group by line_cd, dates union ");
sql.Append("select dates,'21:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '20:00:00' and '21:05:00' group by line_cd, dates union ");
sql.Append("select dates,'22:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '21:00:00' and '22:05:00' group by line_cd, dates union ");
sql.Append("select dates,'23:00' h,max(times),line_cd from t_productioncontroller_output04 where dates = :dates and times between '22:00:00' and '23:05:00' group by line_cd, dates union ");
sql.Append("select dates,'00:00' h,min(times),line_cd from t_productioncontroller_output04 where dates - 1 = :dates and times between '00:00:00' and '01:05:00' group by line_cd, dates union ");
sql.Append("select dates,'01:00' h,max(times),line_cd from t_productioncontroller_output04 where dates - 1 = :dates and times between '00:00:00' and '01:05:00' group by line_cd, dates union ");
sql.Append("select dates,'02:00' h,max(times),line_cd from t_productioncontroller_output04 where dates - 1 = :dates and times between '01:00:00' and '02:05:00' group by line_cd, dates union ");
sql.Append("select dates,'03:00' h,max(times),line_cd from t_productioncontroller_output04 where dates - 1 = :dates and times between '02:00:00' and '03:05:00' group by line_cd, dates union ");
sql.Append("select dates,'04:00' h,max(times),line_cd from t_productioncontroller_output04 where dates - 1 = :dates and times between '03:00:00' and '04:05:00' group by line_cd, dates union ");
sql.Append("select dates,'05:00' h,max(times),line_cd from t_productioncontroller_output04 where dates - 1 = :dates and times between '04:00:00' and '05:05:00' group by line_cd, dates union ");
sql.Append("select dates,'05:55' h,max(times),line_cd from t_productioncontroller_output04 where dates - 1 = :dates and times between '05:00:00' and '05:32:00' group by line_cd, dates) t ");
sql.Append("where o4.output04_id = o3.output03_id and o4.output04_id = o2.output02_id and o4.output04_id = o1.output01_id and o4.line_cd = t.line_cd and o4.times = t.min and o4.dates = :dates ");
sql.Append("and o3.line_cd = t.line_cd and o3.times = t.min and o2.line_cd = t.line_cd and o2.times = t.min and o1.line_cd = t.line_cd and o1.times = t.min order by line_cd,o1.times) tblout ");
sql.Append("left join ");
sql.Append("(select min,t.dates+min datetimes,h,t.line_cd, input_data, (co_beat_core_ng + co_com_wrap + co_core_ng + co_com_glue_sticky) as core, (we_com_slip + we_long_shaft + we_short_shaft) as welding, ");
sql.Append("(wi_break_copper_mc + wi_ruffle_copper_mc + wi_edge_ng_mc + wi_no_sleeve_mc) as wingding, (rigs_wire_pb_sticky + rigs_com_pb_sticky + rigs_ring_prone + rigs_cracked_ring) as ring, ");
sql.Append("(pbs_break_copper + pbs_climb_core + pbs_skip_edge + pbs_wire_combine_wrong + pbs_loose_wire + pbs_rizer_edge_ng + pbs_core_ng + pbs_com_slip + pbs_hole + pbs_2_sleeve ");
sql.Append("+ pbs_wire_pb_sticky + pbs_com_pb_sticky + pbs_no_lead) as solder_wire ");
sql.Append("from t_productioncontroller_input01 i1, t_productioncontroller_input02 i2, ");
sql.Append("(select dates,'06:00' h, min(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '06:00:00' and '06:55:00' group by line_cd, dates union ");
sql.Append("select dates,'07:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '06:00:00' and '07:05:00' group by line_cd, dates union ");
sql.Append("select dates, '08:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '07:00:00' and '08:05:00' group by line_cd, dates union ");
sql.Append("select dates, '09:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '08:00:00' and '09:05:00' group by line_cd, dates union ");
sql.Append("select dates, '10:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '09:00:00' and '10:05:00' group by line_cd, dates union ");
sql.Append("select dates, '11:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '10:00:00' and '11:05:00' group by line_cd, dates union ");
sql.Append("select dates, '12:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '11:00:00' and '12:05:00' group by line_cd, dates union ");
sql.Append("select dates, '13:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '12:00:00' and '13:05:00' group by line_cd, dates union ");
sql.Append("select dates, '14:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '13:00:00' and '14:05:00' group by line_cd, dates union ");
sql.Append("select dates, '15:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '14:00:00' and '15:05:00' group by line_cd, dates union ");
sql.Append("select dates, '16:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '15:00:00' and '16:05:00' group by line_cd, dates union ");
sql.Append("select dates, '17:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '16:00:00' and '17:05:00' group by line_cd, dates union ");
sql.Append("select dates, '18:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '17:00:00' and '18:05:00' group by line_cd, dates union ");
sql.Append("select dates, '19:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '18:00:00' and '19:05:00' group by line_cd, dates union ");
sql.Append("select dates, '20:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '19:00:00' and '20:05:00' group by line_cd, dates union ");
sql.Append("select dates, '21:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '20:00:00' and '21:05:00' group by line_cd, dates union ");
sql.Append("select dates, '22:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '21:00:00' and '22:05:00' group by line_cd, dates union ");
sql.Append("select dates, '23:00' h,max(times),line_cd from t_productioncontroller_input01 where dates = :dates and times between '22:00:00' and '23:05:00' group by line_cd, dates union ");
sql.Append("select dates, '00:00' h,min(times),line_cd from t_productioncontroller_input01 where dates - 1 = :dates and times between '00:00:00' and '01:05:00' group by line_cd, dates union ");
sql.Append("select dates, '01:00' h,max(times),line_cd from t_productioncontroller_input01 where dates - 1 = :dates and times between '00:00:00' and '01:05:00' group by line_cd, dates union ");
sql.Append("select dates, '02:00' h,max(times),line_cd from t_productioncontroller_input01 where dates - 1 = :dates and times between '01:00:00' and '02:05:00' group by line_cd, dates union ");
sql.Append("select dates, '03:00' h,max(times),line_cd from t_productioncontroller_input01 where dates - 1 = :dates and times between '02:00:00' and '03:05:00' group by line_cd, dates union ");
sql.Append("select dates, '04:00' h,max(times),line_cd from t_productioncontroller_input01 where dates - 1 = :dates and times between '03:00:00' and '04:05:00' group by line_cd, dates union ");
sql.Append("select dates, '05:00' h,max(times),line_cd from t_productioncontroller_input01 where dates - 1 = :dates and times between '04:00:00' and '05:05:00' group by line_cd, dates union ");
sql.Append("select dates, '05:55' h,max(times),line_cd from t_productioncontroller_input01 where dates - 1 = :dates and times between '05:00:00' and '05:32:00' group by line_cd, dates) t ");
sql.Append("where i1.input01_id = i2.input02_id and i1.line_cd = t.line_cd and i1.times = t.min and i1.dates = :dates and i2.line_cd = t.line_cd and i2.times = t.min order by line_cd,i1.times) ");
sql.Append("tblin on tblout.line_cd = tblin.line_cd and tblout.h = tblin.h where tblout.h = tblin.h and tblout.line_cd != 'L08'");
sqlParameter.AddParameterDateTime("dates", inVo.StartDay);
//sqlParameter.AddParameterDateTime("datesto", inVo.EndDay);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
//sql.Append("select tblout.datetimes, tblout.line_cd,input_data,output_data,(holder + app + en2 + fundou + en1 + isca + ra + core + welding + wingding + ring + solder_wire) as sum_ng, ");
//sql.Append("holder, app, en2, fundou, en1, isca, ra, core, welding, wingding, ring, solder_wire ");
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["datetimes"].ToString()),
TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
TotalNG = int.Parse(dataReader["sum_ng"].ToString()),
ProInput = int.Parse(dataReader["input_data"].ToString()),
ProOutput = int.Parse(dataReader["output_data"].ToString()),
HolderNG = int.Parse(dataReader["holder"].ToString()),
AppCheckNG = int.Parse(dataReader["app"].ToString()),
En2NG = int.Parse(dataReader["en2"].ToString()),
FundouNG = int.Parse(dataReader["fundou"].ToString()),
En1NG = int.Parse(dataReader["en1"].ToString()),
InsertCaseNG = int.Parse(dataReader["isca"].ToString()),
RANG = int.Parse(dataReader["ra"].ToString()),
SolderWireNG = int.Parse(dataReader["solder_wire"].ToString()),
SolderRingNG = int.Parse(dataReader["ring"].ToString()),
WindingNG = int.Parse(dataReader["wingding"].ToString()),
WeldingNG = int.Parse(dataReader["welding"].ToString()),
CoreNG = int.Parse(dataReader["core"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select tbloutput.datesss dates,'LA459' model_cd,* from ");
sql.Append("(select Case When t.timeso4 between '06:00:00' and '23:59:00' then t.dateo4 when ");
sql.Append("t.timeso4 between '00:00:00' and '05:59:00' then t.dateo4-1 end datesss, line_cdo4, ");
sql.Append("sum(fiapp_stamping_ba) fiapp_stamping_ba , sum(fiapp_case_set) fiapp_case_set, ");
sql.Append("sum(fiapp_tough_shaft) fiapp_tough_shaft, sum(fiapp_case_glue_sticky) fiapp_case_glue_sticky, ");
sql.Append("sum(fiapp_up_low_shabby) fiapp_up_low_shabby , sum(fiapp_hole_shaft) fiapp_hole_shaft, ");
sql.Append("sum(fiapp_no_beat_prone_case) fiapp_no_beat_prone_case, sum(fiapp_hole_case) fiapp_hole_case, ");
sql.Append("sum(fiapp_prone_case) fiapp_prone_case, sum(fiapp_lot_ng) fiapp_lot_ng, ");
sql.Append("sum(fiapp_ter_deform) fiapp_ter_deform, sum(fiapp_hole_ter ) fiapp_hole_ter, ");
sql.Append("sum(fiapp_soder_hl) fiapp_soder_hl, ");
sql.Append("sum(fiapp_metal_oven_low) fiapp_metal_oven_low, ");
sql.Append("sum(fiapp_fundou_ng) fiapp_fundou_ng, ");
sql.Append("sum(fiapp_ter_glue_sticky) fiapp_ter_glue_sticky, ");
sql.Append("sum(fiapp_lead_glue_sticky) fiapp_lead_glue_sticky, ");
sql.Append("sum(hol_gap_holder) hol_gap_holder, ");
sql.Append("sum(en2_lock) en2_lock , sum(en2_cut) en2_cut , ");
sql.Append("sum(en2_chattering) en2_chattering ,");
sql.Append("sum(en2_insulation) en2_insulation, sum(en2_open) en2_open, ");
sql.Append("sum(en2_short) en2_short, sum(en2_duty) en2_duty, ");
sql.Append("sum(en2_no) en2_no, sum(en2_var) en2_var, ");
sql.Append("sum(en2_reverse_spinning) en2_reverse_spinning, sum(en2_io) en2_io, ");
sql.Append("sum(en2_starting_volt) en2_starting_volt, ");
sql.Append("sum(fd_ng_beat_point) fd_ng_beat_point , sum(fd_fundou_deform) fd_fundou_deform , ");
sql.Append("sum(en1_lock) en1_lock, sum(en1_cut) en1_cut, sum(en1_chattering) en1_chattering, ");
sql.Append("sum(en1_insulation) en1_insulation, sum(en1_open) en1_open, ");
sql.Append("sum(en1_bad_wave) en1_bad_wave, sum(en1_duty) en1_duty, ");
sql.Append("sum(en1_short) en1_short, sum(en1_beat_case_ng ) en1_beat_case_ng, ");
sql.Append("sum(en1_beat_fundou_ng) en1_beat_fundou_ng, ");
sql.Append("sum(insc_no_ink_case_mc1) insc_no_ink_case_mc1, sum(insc_ba_deform_mc1) insc_ba_deform_mc1, ");
sql.Append("sum(insc_break_case_mc1) insc_break_case_mc1 ,");
sql.Append("sum(insc_drop_mc1) insc_drop_mc1 , sum(insc_break_wire_mc1) insc_break_wire_mc1, ");
sql.Append("sum(insc_break_ring_mc1) insc_break_ring_mc1, ");
sql.Append("sum(ra_com_pb_sticky) ra_com_pb_sticky,");
sql.Append("sum(ra_wire_pb_sticky) ra_wire_pb_sticky, sum(ra_com_slip) ra_com_slip, ");
sql.Append("sum(ra_renew_ring) ra_renew_ring, sum(ra_break_wire_final_app ) ra_break_wire_final_app, ");
sql.Append("sum(ra_wire_combine_wrong) ra_wire_combine_wrong, ");
sql.Append("sum(ra_core_ng) ra_core_ng, ");
sql.Append("sum(ra_segment_hole) ra_segment_hole, sum(ra_glue_sticky) ra_glue_sticky, ");
sql.Append("sum(ra_loose_wire_final_app) ra_loose_wire_final_app, sum(ra_lead_not_covered ) ra_lead_not_covered,");
sql.Append("sum(ra_less_lead) ra_less_lead ");
sql.Append("from ");
sql.Append("(select o4.dates dateo4,o4.times timeso4,o4.line_cd line_cdo4,* from t_productioncontroller_output04 o4 left join ");
sql.Append("t_productioncontroller_output03 o3 on o4.output04_id = o3.output03_id ");
sql.Append("left join t_productioncontroller_output02 o2 on o4.output04_id = o2.output02_id ");
sql.Append("left join t_productioncontroller_output01 o1 on o4.output04_id = o1.output01_id ");
sql.Append("left join ");
sql.Append("(select dates date1, line_cd line1, Case when idca3 is null then idca1 else ");
sql.Append("idca3 end id from (select tblca1.dates,tblca1.line_cd, idca1, idca3 from ");
sql.Append("(select line_cd,o.dates , max(o.output01_id) idca1 from ");
sql.Append("t_productioncontroller_output01 o where line_cd = :line_cd and o.times > '06:00:00' ");
sql.Append("and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto ");
sql.Append("group by o.dates, line_cd order by dates) tblca1 ");
sql.Append("left join ");
sql.Append("(select line_cd,(o.dates-1) dates , max(o.input01_id) idca3 from t_productioncontroller_input01 o ");
sql.Append("where line_cd = :line_cd and o.times > '00:00:00' and o.times <= '05:30:00' ");
sql.Append("and o.dates > :datefrom and o.dates - 1 <= :dateto ");
sql.Append("group by line_cd,o.dates order by idca3) tblca3 ");
sql.Append("on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl ");
sql.Append("order by dates,line_cd) l on l.line1 = o4.line_cd ");
sql.Append("where o4.output04_id = l.id and l.line1 = o3.line_cd and l.line1 = o2.line_cd ");
sql.Append("and l.line1 = o1.line_cd order by o4.dates, o4.line_cd) ");
sql.Append("t group by datesss,line_cdo4 order by datesss ) tbloutput ");
sql.Append("left join ");
sql.Append("(select Case When t.timeso4 between '06:00:00' and '23:59:00' then t.dateo4 when ");
sql.Append("t.timeso4 between '00:00:00' and '05:59:00' then t.dateo4-1 end datesss, ");
sql.Append("sum(pbs_break_copper) pbs_break_copper, sum(pbs_climb_core) pbs_climb_core, ");
sql.Append("sum(pbs_skip_edge) pbs_skip_edge, ");
sql.Append("sum(pbs_wire_combine_wrong) pbs_wire_combine_wrong, sum(pbs_loose_wire) pbs_loose_wire, ");
sql.Append("sum(pbs_rizer_edge_ng) pbs_rizer_edge_ng, sum(pbs_core_ng) pbs_core_ng, ");
sql.Append("sum(pbs_com_slip) pbs_com_slip, sum(pbs_hole) pbs_hole, ");
sql.Append("sum(pbs_2_sleeve) pbs_2_sleeve, sum(pbs_wire_pb_sticky ) pbs_wire_pb_sticky, ");
sql.Append("sum(pbs_com_pb_sticky) pbs_com_pb_sticky, sum(pbs_no_lead) pbs_no_lead, ");
sql.Append("sum(rigs_cracked_ring) rigs_cracked_ring, sum(rigs_ring_prone) rigs_ring_prone, ");
sql.Append("sum(rigs_com_pb_sticky) rigs_com_pb_sticky, sum(rigs_wire_pb_sticky) rigs_wire_pb_sticky, ");
sql.Append("sum(wi_no_sleeve_mc) wi_no_sleeve_mc, ");
sql.Append("sum(wi_edge_ng_mc) wi_edge_ng_mc, sum(wi_ruffle_copper_mc) wi_ruffle_copper_mc, ");
sql.Append("sum(wi_break_copper_mc) wi_break_copper_mc, ");
sql.Append("sum(we_short_shaft) we_short_shaft, sum(we_long_shaft) we_long_shaft, sum(we_com_slip) we_com_slip, ");
sql.Append("sum(co_com_glue_sticky) co_com_glue_sticky, sum(co_core_ng) co_core_ng, ");
sql.Append("sum(co_com_wrap) co_com_wrap, sum(co_beat_core_ng) co_beat_core_ng ");
sql.Append("from ");
sql.Append("(select i1.dates dateo4,i1.times timeso4,* from t_productioncontroller_input01 i1 ");
sql.Append("left join t_productioncontroller_input02 i2 on i2.input02_id = i1.input01_id ");
sql.Append("left join ");
sql.Append("(select dates date1, line_cd line1, Case when idca3 is null then idca1 else idca3 end id ");
sql.Append("from (select tblca1.dates,tblca1.line_cd, idca1, idca3 from ");
sql.Append("(select line_cd,o.dates , max(o.input01_id) idca1 from t_productioncontroller_input01 o ");
sql.Append("where line_cd = :line_cd and o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom ");
sql.Append("and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 ");
sql.Append("left join (select line_cd,(o.dates-1) dates , max(o.input01_id) idca3 ");
sql.Append("from t_productioncontroller_input01 o ");
sql.Append("where line_cd = :line_cd and o.times > '00:00:00' and o.times <= '05:30:00' ");
sql.Append("and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd,o.dates order by idca3) tblca3 ");
sql.Append("on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl order by dates,line_cd) ");
sql.Append("l on l.line1 = i1.line_cd where l.id = i1.input01_id and l.line1 = i2.line_cd and l.line1 = i1.line_cd order by i1.dates,i1.line_cd ) t group by datesss order by datesss) ");
sql.Append("tblinput on tbloutput.datesss = tblinput.datesss ");
sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
sqlParameter.AddParameterString("line_cd", inVo.ProLine);
//sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["dates"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cdo4"].ToString(),
HolGapHolder = int.Parse(dataReader["hol_gap_holder"].ToString()),
App_stamping_ba = int.Parse(dataReader["fiapp_stamping_ba"].ToString()),
App_case_set = int.Parse(dataReader["fiapp_case_set"].ToString()),
App_tough_shaft = int.Parse(dataReader["fiapp_tough_shaft"].ToString()),
App_case_glue_sticky = int.Parse(dataReader["fiapp_case_glue_sticky"].ToString()),
App_up_low_shabby = int.Parse(dataReader["fiapp_up_low_shabby"].ToString()),
App_hole_shaft = int.Parse(dataReader["fiapp_hole_shaft"].ToString()),
App_no_beat_prone_case = int.Parse(dataReader["fiapp_no_beat_prone_case"].ToString()),
App_hole_case = int.Parse(dataReader["fiapp_hole_case"].ToString()),
App_prone_case = int.Parse(dataReader["fiapp_prone_case"].ToString()),
App_lot_ng = int.Parse(dataReader["fiapp_lot_ng"].ToString()),
App_ter_deform = int.Parse(dataReader["fiapp_ter_deform"].ToString()),
App_hole_ter = int.Parse(dataReader["fiapp_hole_ter"].ToString()),
App_soder_hl = int.Parse(dataReader["fiapp_soder_hl"].ToString()),
App_metal_oven_low = int.Parse(dataReader["fiapp_metal_oven_low"].ToString()),
App_fundou_ng = int.Parse(dataReader["fiapp_fundou_ng"].ToString()),
App_ter_glue_sticky = int.Parse(dataReader["fiapp_ter_glue_sticky"].ToString()),
App_lead_glue_sticky = int.Parse(dataReader["fiapp_lead_glue_sticky"].ToString()),
Co_beat_core_ng = int.Parse(dataReader["co_beat_core_ng"].ToString()),
Co_com_wrap = int.Parse(dataReader["co_com_wrap"].ToString()),
Co_core_ng = int.Parse(dataReader["co_core_ng"].ToString()),
Co_com_glue_sticky = int.Parse(dataReader["co_com_glue_sticky"].ToString()),
En1_lock = int.Parse(dataReader["en1_lock"].ToString()),
En1_cut = int.Parse(dataReader["en1_cut"].ToString()),
En1_chattering = int.Parse(dataReader["en1_chattering"].ToString()),
En1_insulation = int.Parse(dataReader["en1_insulation"].ToString()),
En1_open = int.Parse(dataReader["en1_open"].ToString()),
En1_bad_wave = int.Parse(dataReader["en1_bad_wave"].ToString()),
En1_duty = int.Parse(dataReader["en1_duty"].ToString()),
En1_short = int.Parse(dataReader["en1_short"].ToString()),
En1_beat_case_ng = int.Parse(dataReader["en1_beat_case_ng"].ToString()),
En1_beat_fundou_ng = int.Parse(dataReader["en1_beat_fundou_ng"].ToString()),
En2_lock = int.Parse(dataReader["en2_lock"].ToString()),
En2_cut = int.Parse(dataReader["en2_cut"].ToString()),
En2_chattering = int.Parse(dataReader["en2_chattering"].ToString()),
En2_insulation = int.Parse(dataReader["en2_insulation"].ToString()),
En2_open = int.Parse(dataReader["en2_open"].ToString()),
En2_short = int.Parse(dataReader["en2_short"].ToString()),
En2_duty = int.Parse(dataReader["en2_duty"].ToString()),
En2_no = int.Parse(dataReader["en2_no"].ToString()),
En2_var = int.Parse(dataReader["en2_var"].ToString()),
En2_reverse_spinning = int.Parse(dataReader["en2_reverse_spinning"].ToString()),
En2_starting_volt = int.Parse(dataReader["en2_starting_volt"].ToString()),
En2_io = int.Parse(dataReader["en2_io"].ToString()),
Fd_ng_beat_point = int.Parse(dataReader["fd_ng_beat_point"].ToString()),
Fd_fundou_deform = int.Parse(dataReader["fd_fundou_deform"].ToString()),
Insc_no_ink_case_mc1 = int.Parse(dataReader["insc_no_ink_case_mc1"].ToString()),
Insc_ba_deform_mc1 = int.Parse(dataReader["insc_ba_deform_mc1"].ToString()),
Insc_break_case_mc1 = int.Parse(dataReader["insc_break_case_mc1"].ToString()),
Insc_drop_mc1 = int.Parse(dataReader["insc_drop_mc1"].ToString()),
Insc_break_wire_mc1 = int.Parse(dataReader["insc_break_wire_mc1"].ToString()),
Insc_break_ring_mc1 = int.Parse(dataReader["insc_break_ring_mc1"].ToString()),
RA_com_pb_sticky = int.Parse(dataReader["ra_com_pb_sticky"].ToString()),
RA_wire_pb_sticky = int.Parse(dataReader["ra_wire_pb_sticky"].ToString()),
RA_com_slip = int.Parse(dataReader["ra_com_slip"].ToString()),
RA_renew_ring = int.Parse(dataReader["ra_renew_ring"].ToString()),
RA_break_wire_final_app = int.Parse(dataReader["ra_break_wire_final_app"].ToString()),
RA_wire_combine_wrong = int.Parse(dataReader["ra_wire_combine_wrong"].ToString()),
RA_core_ng = int.Parse(dataReader["ra_core_ng"].ToString()),
RA_segment_hole = int.Parse(dataReader["ra_segment_hole"].ToString()),
RA_glue_sticky = int.Parse(dataReader["ra_glue_sticky"].ToString()),
RA_loose_wire_final_app = int.Parse(dataReader["ra_loose_wire_final_app"].ToString()),
RA_lead_not_covered = int.Parse(dataReader["ra_lead_not_covered"].ToString()),
RA_less_lead = int.Parse(dataReader["ra_less_lead"].ToString()),
Rigs_wire_pb_sticky = int.Parse(dataReader["rigs_wire_pb_sticky"].ToString()),
Rigs_com_pb_sticky = int.Parse(dataReader["rigs_com_pb_sticky"].ToString()),
Rigs_ring_prone = int.Parse(dataReader["rigs_ring_prone"].ToString()),
Rigs_cracked_ring = int.Parse(dataReader["rigs_cracked_ring"].ToString()),
Pbs_break_copper = int.Parse(dataReader["pbs_break_copper"].ToString()),
Pbs_climb_core = int.Parse(dataReader["pbs_climb_core"].ToString()),
Pbs_skip_edge = int.Parse(dataReader["pbs_skip_edge"].ToString()),
Pbs_wire_combine_wrong = int.Parse(dataReader["pbs_wire_combine_wrong"].ToString()),
Pbs_loose_wire = int.Parse(dataReader["pbs_loose_wire"].ToString()),
Pbs_rizer_edge_ng = int.Parse(dataReader["pbs_rizer_edge_ng"].ToString()),
Pbs_core_ng = int.Parse(dataReader["pbs_core_ng"].ToString()),
Pbs_com_slip = int.Parse(dataReader["pbs_com_slip"].ToString()),
Pbs_hole = int.Parse(dataReader["pbs_hole"].ToString()),
Pbs_2_sleeve = int.Parse(dataReader["pbs_2_sleeve"].ToString()),
Pbs_wire_pb_sticky = int.Parse(dataReader["pbs_wire_pb_sticky"].ToString()),
Pbs_com_pb_sticky = int.Parse(dataReader["pbs_com_pb_sticky"].ToString()),
Pbs_no_lead = int.Parse(dataReader["pbs_no_lead"].ToString()),
We_com_slip = int.Parse(dataReader["we_com_slip"].ToString()),
We_long_shaft = int.Parse(dataReader["we_long_shaft"].ToString()),
We_short_shaft = int.Parse(dataReader["we_short_shaft"].ToString()),
Wi_break_copper_mc = int.Parse(dataReader["wi_break_copper_mc"].ToString()),
Wi_ruffle_copper_mc = int.Parse(dataReader["wi_ruffle_copper_mc"].ToString()),
Wi_edge_ng_mc = int.Parse(dataReader["wi_edge_ng_mc"].ToString()),
Wi_no_sleeve_mc = int.Parse(dataReader["wi_no_sleeve_mc"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select (o4.dates+o4.times) datetimes, o4.model_cd,o4.line_cd, o4.process_cd, ");
sql.Append("fiapp_stamping_ba, fiapp_case_set, fiapp_tough_shaft, fiapp_case_glue_sticky, fiapp_up_low_shabby, fiapp_hole_shaft, ");
sql.Append("fiapp_no_beat_prone_case, fiapp_hole_case, fiapp_prone_case, fiapp_lot_ng, fiapp_ter_deform, fiapp_hole_ter, ");
sql.Append("fiapp_soder_hl,fiapp_metal_oven_low, fiapp_fundou_ng, fiapp_ter_glue_sticky, fiapp_lead_glue_sticky ");
sql.Append("from t_productioncontroller_output04 o4 ");
sql.Append("where o4.line_cd = :line_cd ");
sql.Append("and o4.dates = :dates ");
sql.Append("and (o4.times in(select min(times) from t_productioncontroller_output04 where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sql.Append("or o4.dates-1 =:dates and o4.line_cd = :line_cd ");
sql.Append("and (o4.times in(select min(times) from t_productioncontroller_output04 where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '05:00:00' and '05:35:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sqlParameter.AddParameterString("line_cd", inVo.ProLine);
sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
App_stamping_ba = int.Parse(dataReader["fiapp_stamping_ba"].ToString()),
App_case_set = int.Parse(dataReader["fiapp_case_set"].ToString()),
App_tough_shaft = int.Parse(dataReader["fiapp_tough_shaft"].ToString()),
App_case_glue_sticky = int.Parse(dataReader["fiapp_case_glue_sticky"].ToString()),
App_up_low_shabby = int.Parse(dataReader["fiapp_up_low_shabby"].ToString()),
App_hole_shaft = int.Parse(dataReader["fiapp_hole_shaft"].ToString()),
App_no_beat_prone_case = int.Parse(dataReader["fiapp_no_beat_prone_case"].ToString()),
App_hole_case = int.Parse(dataReader["fiapp_hole_case"].ToString()),
App_prone_case = int.Parse(dataReader["fiapp_prone_case"].ToString()),
App_lot_ng = int.Parse(dataReader["fiapp_lot_ng"].ToString()),
App_ter_deform = int.Parse(dataReader["fiapp_ter_deform"].ToString()),
App_hole_ter = int.Parse(dataReader["fiapp_hole_ter"].ToString()),
App_soder_hl = int.Parse(dataReader["fiapp_soder_hl"].ToString()),
App_metal_oven_low = int.Parse(dataReader["fiapp_metal_oven_low"].ToString()),
App_fundou_ng = int.Parse(dataReader["fiapp_fundou_ng"].ToString()),
App_ter_glue_sticky = int.Parse(dataReader["fiapp_ter_glue_sticky"].ToString()),
App_lead_glue_sticky = int.Parse(dataReader["fiapp_lead_glue_sticky"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select (i2.dates+i2.times) datetimes, i2.model_cd,i2.line_cd, i2.process_cd, ");
sql.Append("pbs_break_copper, pbs_climb_core, pbs_skip_edge, pbs_wire_combine_wrong, pbs_loose_wire, pbs_rizer_edge_ng, ");
sql.Append("pbs_core_ng, pbs_com_slip, pbs_hole, pbs_2_sleeve, pbs_wire_pb_sticky, pbs_com_pb_sticky, pbs_no_lead ");
sql.Append("from t_productioncontroller_input02 i2 ");
sql.Append("where i2.line_cd = :line_cd ");
sql.Append("and i2.dates = :dates ");
sql.Append("and (i2.times in(select min(times) from t_productioncontroller_input02 where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sql.Append("or i2.dates-1 =:dates and i2.line_cd = :line_cd ");
sql.Append("and (i2.times in(select min(times) from t_productioncontroller_input02 where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i2.times in(select max(times) from t_productioncontroller_input02 where times between '05:00:00' and '05:35:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sqlParameter.AddParameterString("line_cd", inVo.ProLine);
sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
Pbs_break_copper = int.Parse(dataReader["pbs_break_copper"].ToString()),
Pbs_climb_core = int.Parse(dataReader["pbs_climb_core"].ToString()),
Pbs_skip_edge = int.Parse(dataReader["pbs_skip_edge"].ToString()),
Pbs_wire_combine_wrong = int.Parse(dataReader["pbs_wire_combine_wrong"].ToString()),
Pbs_loose_wire = int.Parse(dataReader["pbs_loose_wire"].ToString()),
Pbs_rizer_edge_ng = int.Parse(dataReader["pbs_rizer_edge_ng"].ToString()),
Pbs_core_ng = int.Parse(dataReader["pbs_core_ng"].ToString()),
Pbs_com_slip = int.Parse(dataReader["pbs_com_slip"].ToString()),
Pbs_hole = int.Parse(dataReader["pbs_hole"].ToString()),
Pbs_2_sleeve = int.Parse(dataReader["pbs_2_sleeve"].ToString()),
Pbs_wire_pb_sticky = int.Parse(dataReader["pbs_wire_pb_sticky"].ToString()),
Pbs_com_pb_sticky = int.Parse(dataReader["pbs_com_pb_sticky"].ToString()),
Pbs_no_lead = int.Parse(dataReader["pbs_no_lead"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
string sqlApp = "(fiapp_stamping_ba + fiapp_case_set + fiapp_tough_shaft + fiapp_case_glue_sticky + fiapp_up_low_shabby + fiapp_hole_shaft + fiapp_no_beat_prone_case + fiapp_hole_case + fiapp_prone_case + fiapp_lot_ng + fiapp_ter_deform + fiapp_hole_ter + fiapp_soder_hl + fiapp_metal_oven_low + fiapp_fundou_ng + fiapp_ter_glue_sticky + fiapp_lead_glue_sticky )";
string sqlEn2 = "(en2_lock + en2_cut + en2_chattering + en2_insulation + en2_open + en2_short + en2_duty + en2_no + en2_var + en2_reverse_spinning + en2_starting_volt + en2_io)";
string sqlFundou = "(fd_ng_beat_point + fd_fundou_deform)";
string sqlEn1 = "(en1_lock + en1_cut + en1_chattering + en1_insulation + en1_open + en1_bad_wave + en1_duty + en1_short + en1_beat_case_ng + en1_beat_fundou_ng )";
string sqlIS = "(insc_no_ink_case_mc1 + insc_ba_deform_mc1 + insc_break_case_mc1 + insc_drop_mc1 + insc_break_wire_mc1 + insc_break_ring_mc1)";
string sqlRA = "(ra_com_pb_sticky + ra_wire_pb_sticky + ra_com_slip + ra_renew_ring + ra_break_wire_final_app + ra_wire_combine_wrong + ra_core_ng + ra_segment_hole + ra_glue_sticky + ra_loose_wire_final_app + ra_lead_not_covered + ra_less_lead )";
string sqlSW = "(pbs_break_copper + pbs_climb_core + pbs_skip_edge + pbs_wire_combine_wrong + pbs_loose_wire + pbs_rizer_edge_ng + pbs_core_ng + pbs_com_slip + pbs_hole + pbs_2_sleeve + pbs_wire_pb_sticky + pbs_com_pb_sticky + pbs_no_lead)";
string sqlRing = "(rigs_wire_pb_sticky + rigs_com_pb_sticky + rigs_ring_prone + rigs_cracked_ring)";
string sqlWE = "(we_com_slip + we_long_shaft + we_short_shaft)";
string sqlWi = "(wi_break_copper_mc + wi_ruffle_copper_mc + wi_edge_ng_mc + wi_no_sleeve_mc)";
string sqlCore = "(co_beat_core_ng + co_com_wrap + co_core_ng + co_com_glue_sticky)";
/*
* sql.Append("select tbl.dates, 'LA459' model_cd,'All Line' line_cd, ");
* sql.Append("Case when sum(tbl.input_data) is null then 0 else sum(tbl.input_data) end input_data, ");
* sql.Append("Case when sum(output_data) is null then 0 else sum(output_data) end output_data, ");
* sql.Append("Case when sum(sum_ng) is null then 0 else sum(sum_ng) end sum_ng, ");
* sql.Append("Case when sum(holder) is null then 0 else sum(holder) end holder, ");
* sql.Append("Case when sum(app) is null then 0 else sum(app) end app, ");
* sql.Append("Case when sum(en2) is null then 0 else sum(en2) end en2, ");
* sql.Append("Case when sum(fundou) is null then 0 else sum(fundou) end fundou, ");
* sql.Append("Case when sum(en1) is null then 0 else sum(en1) end en1, ");
* sql.Append("Case when sum(isca) is null then 0 else sum(isca) end insert_case, ");
* sql.Append("Case when sum(ra) is null then 0 else sum(ra) end ra, ");
* sql.Append("Case when sum(solder_wire) is null then 0 else sum(solder_wire) end solder_wire, ");
* sql.Append("Case when sum(solder_ring) is null then 0 else sum(solder_ring) end solder_ring, ");
* sql.Append("Case when sum(welding) is null then 0 else sum(welding) end welding, ");
* sql.Append("Case when sum(wingding) is null then 0 else sum(wingding) end wingding, ");
* sql.Append("Case when sum(core) is null then 0 else sum(core) end core from ");
* sql.Append("(");
* sql.Append("select o4.dates,o4.model_cd,o4.line_cd, ");
* sql.Append("input_data, output_data, (hol_gap_holder +" + sqlApp + "+" + sqlEn2 + "+" + sqlFundou + "+" + sqlEn1 + "+" + sqlIS + "+" + sqlRA + "+" + sqlSW + "+" + sqlRing + "+" + sqlWE + "+" + sqlWi + "+" + sqlCore + ") as sum_ng,");
* sql.Append("hol_gap_holder as holder, " + sqlApp + " as app, " + sqlEn2 + " as en2, " + sqlFundou + " as fundou, " + sqlEn1 + " as en1, ");
* sql.Append(sqlIS + " as isca, " + sqlRA + " as ra, " + sqlSW + " as solder_wire, " + sqlRing + " as solder_ring, " + sqlWi + " as wingding,");
* sql.Append(sqlWE + " as welding," + sqlCore + " as core ");
* sql.Append("from t_productioncontroller_output04 o4 left join t_productioncontroller_output03 o3 on o4.line_cd = o3.line_cd and o4.output04_id = o3.output03_id ");
* sql.Append("left join t_productioncontroller_output02 o2 on o4.line_cd =o2.line_cd and o4.output04_id = o2.output02_id ");
* sql.Append("left join t_productioncontroller_output01 o1 on o4.line_cd =o1.line_cd and o4.output04_id = o1.output01_id ");
* sql.Append("left join t_productioncontroller_input02 i2 on o4.line_cd =i2.line_cd and o4.output04_id = i2.input02_id ");
* sql.Append("left join t_productioncontroller_input01 i1 on o4.line_cd =i1.line_cd and o4.output04_id = i1.input01_id ");
* sql.Append("left join (select line_cd,o.dates , max(o.output04_id) id from t_productioncontroller_output04 o where o.times > '00:00:00' and o.times <= '05:50:00' and o.dates > :datefrom and o.dates - 1 <= :datesto and o.line_cd = 'L09' group by line_cd, o.dates order by id) l9 on o4.line_cd = l9.line_cd ");
* sql.Append("left join (select line_cd,o.dates , max(o.output04_id) id from t_productioncontroller_output04 o where o.times > '00:00:00' and o.times <= '05:50:00' and o.dates > :datefrom and o.dates - 1 <= :datesto and o.line_cd = 'L08' group by o.dates, line_cd order by id) l8 on o4.line_cd = l8.line_cd ");
* sql.Append("left join (select line_cd,o.dates , max(o.output04_id) id from t_productioncontroller_output04 o where o.times > '00:00:00' and o.times <= '05:50:00' and o.dates > :datefrom and o.dates - 1 <= :datesto and o.line_cd = 'L07' group by o.dates, line_cd order by id) l7 on o4.line_cd = l7.line_cd ");
* sql.Append("left join (select line_cd,o.dates , max(o.output04_id) id from t_productioncontroller_output04 o where o.times > '00:00:00' and o.times <= '05:50:00' and o.dates > :datefrom and o.dates - 1 <= :datesto and o.line_cd = 'L06' group by o.dates, line_cd order by id) l6 on o4.line_cd = l6.line_cd ");
* sql.Append("left join (select line_cd,o.dates , max(o.output04_id) id from t_productioncontroller_output04 o where o.times > '00:00:00' and o.times <= '05:50:00' and o.dates > :datefrom and o.dates - 1 <= :datesto and o.line_cd = 'L05' group by o.dates, line_cd order by id) l5 on o4.line_cd = l5.line_cd ");
* sql.Append("left join (select line_cd,o.dates , max(o.output04_id) id from t_productioncontroller_output04 o where o.times > '00:00:00' and o.times <= '05:50:00' and o.dates > :datefrom and o.dates - 1 <= :datesto and o.line_cd = 'L04' group by o.dates, line_cd order by id) l4 on o4.line_cd = l4.line_cd ");
* sql.Append("left join (select line_cd,o.dates , max(o.output04_id) id from t_productioncontroller_output04 o where o.times > '00:00:00' and o.times <= '05:50:00' and o.dates > :datefrom and o.dates - 1 <= :datesto and o.line_cd = 'L03' group by o.dates, line_cd order by id) l3 on o4.line_cd = l3.line_cd ");
* sql.Append("where l9.id = o4.output04_id or l8.id = o4.output04_id or l7.id = o4.output04_id or l6.id = o4.output04_id ");
* sql.Append(" or l5.id = o4.output04_id or l4.id = o4.output04_id or l3.id = o4.output04_id order by o4.dates ");
* sql.Append(") tbl group by tbl.dates order by tbl.dates "); */
sql.Append("select tblout.datesss dates,'LA459' model_cd,'All Line' line_cd,input_data,(holder+ app + en2 + fundou + en1 + isca + ra + solder_wire + solder_ring + wingding + welding + core) sum_ng, ");
sql.Append("output_data, holder, app, en2, fundou, en1, isca,ra , solder_wire, solder_ring, wingding, welding, core ");
sql.Append("from ");
//tbl of output
sql.Append("(select ");
sql.Append("Case When times between '06:00:00' and '23:59:00' then dates when times between '00:00:00' and '05:59:00' then dates-1 end datesss, ");
sql.Append("sum(output_data) output_data,sum(holder) holder, sum(app) app, sum(en2) en2, sum(fundou) fundou,sum(en1) en1,sum(isca) isca, sum(ra) ra ");
sql.Append("from ");
//tbl all line not group by
sql.Append("(Select o4.dates ,o4.times ,o4.output04_id ,o4.model_cd ,o4.line_cd, output_data, hol_gap_holder as holder," + sqlApp + " app, " + sqlFundou + " fundou, ");
sql.Append(sqlEn2 + " en2, " + sqlEn1 + " en1, " + sqlIS + " isca, " + sqlRA + " ra ");
sql.Append("from t_productioncontroller_output04 o4 left join t_productioncontroller_output03 o3 on o4.line_cd = o3.line_cd and o4.output04_id = o3.output03_id ");
sql.Append("left join t_productioncontroller_output02 o2 on o4.line_cd = o2.line_cd and o4.output04_id = o2.output02_id ");
sql.Append("left join t_productioncontroller_output01 o1 on o4.line_cd = o1.line_cd and o4.output04_id = o1.output01_id ");
sql.Append("left join ");
//tbl search id
sql.Append("(select dates ,line_cd, Case when idca3 is null then idca1 else idca3 end id ");
sql.Append("from ");
//tbl search id 2 shifft
sql.Append("(select tblca1.dates,tblca1.line_cd, idca1, idca3 ");
sql.Append("from ");
//tbl search id shifft 1
sql.Append("(select line_cd,o.dates , max(o.output04_id) idca1 from t_productioncontroller_output04 o ");
sql.Append("where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom ");
sql.Append("and o.dates <= :datesto group by o.dates, line_cd order by dates) tblca1 ");
//end tbl search id shift 1
sql.Append("left join ");
//tbl search id shift 3
sql.Append("(select line_cd,(o.dates-1) dates , max(o.output04_id) idca3 from t_productioncontroller_output04 o ");
sql.Append("where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom ");
sql.Append("and o.dates - 1 <= :datesto group by line_cd,o.dates order by idca3) tblca3 ");
//end tbl search id shift 3
sql.Append("on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) ");
//end tbl search id 2 shift
sql.Append("tbl order by dates,line_cd) l on l.line_cd = o4.line_cd ");
//end tbl search id
sql.Append("where l.id = o4.output04_id order by o4.dates,o4.line_cd ) tbl ");
//end tbl all line not group by
sql.Append("group by datesss order by datesss ) tblout ");
//end tbl output
sql.Append("left join ");
//tbl input
sql.Append("(select ");
sql.Append("Case When times between '06:00:00' and '23:59:00' then dates when times between '00:00:00' and '05:59:00' then dates-1 end datesss, ");
sql.Append("sum(input_data) input_data,sum(solder_wire) solder_wire, sum(solder_ring) solder_ring, sum(wingding) wingding, sum(welding) welding,sum(core) core ");
sql.Append("from ");
//tbl all line not group by
sql.Append("(select i1.dates,i1.times,i1.line_cd, input_data, " + sqlSW + " as solder_wire, " + sqlRing + " as solder_ring, " + sqlWi + " as wingding,");
sql.Append(sqlWE + " as welding," + sqlCore + " as core ");
sql.Append(" from t_productioncontroller_input01 i1 left join t_productioncontroller_input02 i2 on i1.line_cd = i2.line_cd and i1.input01_id = i2.input02_id ");
sql.Append(" left join ");
//tbl search id
sql.Append("(select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id ");
sql.Append(" from ");
//tbl search id 2 shift
sql.Append("(select tblca1.dates,tblca1.line_cd, idca1, idca3 ");
sql.Append(" from ");
//tbl search id shift 1
sql.Append("(select line_cd,o.dates , max(o.input01_id) idca1 ");
sql.Append(" from t_productioncontroller_input01 o ");
sql.Append("where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :datesto group by o.dates, line_cd order by dates) tblca1 ");
//end tbl search id shif1
sql.Append("left join ");
//tbl search id shift 3
sql.Append("(select line_cd,(o.dates-1) dates , max(o.input01_id) idca3 ");
sql.Append(" from t_productioncontroller_input01 o ");
sql.Append(" where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :datesto group by line_cd,o.dates order by idca3) tblca3 ");
//end tbl search id shift 3
sql.Append(" on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl ");
//end tbl search id 2 shift
sql.Append(" order by dates,line_cd) l on l.line_cd = i1.line_cd ");
//end tbl search id
sql.Append(" where i1.input01_id = l.id order by i1.dates,i1.line_cd ) tbl ");
//end tbl all line not group by
sql.Append(" group by datesss order by datesss) tblin on tblout.datesss = tblin.datesss ");
//end tbl input
sql.Append("");
sqlParameter.AddParameterDateTime("datefrom", inVo.StartDay);
sqlParameter.AddParameterDateTime("datesto", inVo.EndDay);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
//IDataAdapter d = sqlCommandAdapter.ExecuteScalar(sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
StartDay = DateTime.Parse(dataReader["dates"].ToString()),
//EndDay = DateTime.Parse(dataReader["endday"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
TotalNG = int.Parse(dataReader["sum_ng"].ToString()),
ProInput = int.Parse(dataReader["input_data"].ToString()),
ProOutput = int.Parse(dataReader["output_data"].ToString()),
HolderNG = int.Parse(dataReader["holder"].ToString()),
AppCheckNG = int.Parse(dataReader["app"].ToString()),
En2NG = int.Parse(dataReader["en2"].ToString()),
FundouNG = int.Parse(dataReader["fundou"].ToString()),
En1NG = int.Parse(dataReader["en1"].ToString()),
InsertCaseNG = int.Parse(dataReader["isca"].ToString()),
RANG = int.Parse(dataReader["ra"].ToString()),
SolderWireNG = int.Parse(dataReader["solder_wire"].ToString()),
SolderRingNG = int.Parse(dataReader["solder_ring"].ToString()),
WindingNG = int.Parse(dataReader["wingding"].ToString()),
WeldingNG = int.Parse(dataReader["welding"].ToString()),
CoreNG = int.Parse(dataReader["core"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
string sqlApp = "(fiapp_stamping_ba + fiapp_case_set + fiapp_tough_shaft + fiapp_case_glue_sticky + fiapp_up_low_shabby + fiapp_hole_shaft + fiapp_no_beat_prone_case + fiapp_hole_case + fiapp_prone_case + fiapp_lot_ng + fiapp_ter_deform + fiapp_hole_ter + fiapp_soder_hl + fiapp_metal_oven_low + fiapp_fundou_ng + fiapp_ter_glue_sticky + fiapp_lead_glue_sticky )";
string sqlEn2 = "(en2_lock + en2_cut + en2_chattering + en2_insulation + en2_open + en2_short + en2_duty + en2_no + en2_var + en2_reverse_spinning + en2_starting_volt + en2_io)";
string sqlFundou = "(fd_ng_beat_point + fd_fundou_deform)";
string sqlEn1 = "(en1_lock + en1_cut + en1_chattering + en1_insulation + en1_open + en1_bad_wave + en1_duty + en1_short + en1_beat_case_ng + en1_beat_fundou_ng )";
string sqlIS = "(insc_no_ink_case_mc1 + insc_ba_deform_mc1 + insc_break_case_mc1 + insc_drop_mc1 + insc_break_wire_mc1 + insc_break_ring_mc1)";
string sqlRA = "(ra_com_pb_sticky + ra_wire_pb_sticky + ra_com_slip + ra_renew_ring + ra_break_wire_final_app + ra_wire_combine_wrong + ra_core_ng + ra_segment_hole + ra_glue_sticky + ra_loose_wire_final_app + ra_lead_not_covered + ra_less_lead )";
string sqlSW = "(pbs_break_copper + pbs_climb_core + pbs_skip_edge + pbs_wire_combine_wrong + pbs_loose_wire + pbs_rizer_edge_ng + pbs_core_ng + pbs_com_slip + pbs_hole + pbs_2_sleeve + pbs_wire_pb_sticky + pbs_com_pb_sticky + pbs_no_lead)";
string sqlRing = "(rigs_wire_pb_sticky + rigs_com_pb_sticky + rigs_ring_prone + rigs_cracked_ring)";
string sqlWE = "(we_com_slip + we_long_shaft + we_short_shaft)";
string sqlWi = "(wi_break_copper_mc + wi_ruffle_copper_mc + wi_edge_ng_mc + wi_no_sleeve_mc)";
string sqlCore = "(co_beat_core_ng + co_com_wrap + co_core_ng + co_com_glue_sticky)";
sql.Append(@"select tblout.datestimes, tblout.model_cd, tblout.line_cd, ( holder + app + en2 + fundou + en1 + insert_case + ra + solder_wire + solder_ring + ");
sql.Append("wingding + welding + core) as sum_ng,input_data, output_data, holder, app, en2, fundou, en1, insert_case, ra, solder_wire, solder_ring, wingding, welding, core ");
sql.Append("from ");
sql.Append("(select ROW_NUMBER() OVER(ORDER BY o4.dates,o4.times ASC) rowo, (o4.dates + o4.times) datestimes, o4.model_cd,o4.line_cd, o4.process_cd, ");
sql.Append("output_data, hol_gap_holder as holder, " + sqlApp + " as app, " + sqlEn2 + " as en2, " + sqlFundou + " as fundou, " + sqlEn1 + " as en1, ");
sql.Append(sqlIS + " as insert_case, " + sqlRA + " as ra ");
sql.Append("from t_productioncontroller_output04 o4, t_productioncontroller_output03 o3, t_productioncontroller_output02 o2, t_productioncontroller_output01 o1 ");
sql.Append("where o4.line_cd = :line_cd and o3.line_cd = :line_cd and o2.line_cd = :line_cd and o1.line_cd = :line_cd ");
sql.Append(" and o4.output04_id = o3.output03_id and o4.output04_id = o2.output02_id and o4.output04_id = o1.output01_id ");
sql.Append("and o4.dates = :dates ");
sql.Append("and (o4.times in(select min(times) from t_productioncontroller_output04 where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sql.Append("or o4.dates-1 =:dates ");
sql.Append("and o4.line_cd = :line_cd and o3.line_cd = :line_cd and o2.line_cd = :line_cd and o1.line_cd = :line_cd ");
sql.Append("and o4.output04_id = o3.output03_id and o4.output04_id = o2.output02_id and o4.output04_id = o1.output01_id ");
sql.Append("and (o4.times in(select min(times) from t_productioncontroller_output04 where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output04 where times between '05:00:00' and '05:35:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd))) tblout ");
sql.Append("left join ");
//
sql.Append(@"(select ROW_NUMBER() OVER(ORDER BY i1.dates,i1.times ASC) rowi,(i1.dates + i1.times) datestimes, i1.model_cd,i1.line_cd, i1.process_cd, input_data, ");
sql.Append(sqlSW + " as solder_wire, " + sqlRing + " as solder_ring, " + sqlWi + " as wingding, " + sqlWE + " as welding, " + sqlCore + " as core ");
sql.Append("from t_productioncontroller_input02 i2, t_productioncontroller_input01 i1 ");
sql.Append("where i2.line_cd = :line_cd and i1.line_cd = :line_cd and i1.input01_id = i2.input02_id ");
sql.Append("and i1.dates = :dates ");
sql.Append("and (i1.times in(select min(times) from t_productioncontroller_input01 where times between '06:00:00' and '06:55:00' and input_data < 100 and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sql.Append("or i1.dates-1 =:dates ");
sql.Append("and i1.line_cd = :line_cd and i2.line_cd = :line_cd and i1.input01_id = i2.input02_id ");
sql.Append("and (i1.times in(select min(times) from t_productioncontroller_input01 where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or i1.times in(select max(times) from t_productioncontroller_input01 where times between '05:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd))) tblin on tblout.rowo = tblin.rowi ");
sql.Append("where tblout.rowo = tblin.rowi");
sqlParameter.AddParameterString("line_cd", inVo.ProLine);
sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
TimeHour = DateTime.Parse(dataReader["datestimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
TotalNG = int.Parse(dataReader["sum_ng"].ToString()),
ProInput = int.Parse(dataReader["input_data"].ToString()),
ProOutput = int.Parse(dataReader["output_data"].ToString()),
HolderNG = int.Parse(dataReader["holder"].ToString()),
AppCheckNG = int.Parse(dataReader["app"].ToString()),
En2NG = int.Parse(dataReader["en2"].ToString()),
FundouNG = int.Parse(dataReader["fundou"].ToString()),
En1NG = int.Parse(dataReader["en1"].ToString()),
InsertCaseNG = int.Parse(dataReader["insert_case"].ToString()),
RANG = int.Parse(dataReader["ra"].ToString()),
SolderWireNG = int.Parse(dataReader["solder_wire"].ToString()),
SolderRingNG = int.Parse(dataReader["solder_ring"].ToString()),
WindingNG = int.Parse(dataReader["wingding"].ToString()),
WeldingNG = int.Parse(dataReader["welding"].ToString()),
CoreNG = int.Parse(dataReader["core"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select (o4.dates+o4.times) datetimes, o4.model_cd,o4.line_cd, o4.process_cd, ");
sql.Append("insc_no_ink_case_mc1, insc_ba_deform_mc1, insc_break_case_mc1, insc_drop_mc1, insc_break_wire_mc1, insc_break_ring_mc1 ");
sql.Append("from t_productioncontroller_output01 o4 ");
sql.Append("where o4.line_cd = :line_cd ");
sql.Append("and o4.dates = :dates ");
sql.Append("and (o4.times in(select min(times) from t_productioncontroller_output01 where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sql.Append("or o4.dates-1 =:dates and o4.line_cd = :line_cd ");
sql.Append("and (o4.times in(select min(times) from t_productioncontroller_output01 where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
sql.Append("or o4.times in(select max(times) from t_productioncontroller_output01 where times between '05:00:00' and '05:35:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");
sqlParameter.AddParameterString("line_cd", inVo.ProLine);
sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
Insc_no_ink_case_mc1 = int.Parse(dataReader["insc_no_ink_case_mc1"].ToString()),
Insc_ba_deform_mc1 = int.Parse(dataReader["insc_ba_deform_mc1"].ToString()),
Insc_break_case_mc1 = int.Parse(dataReader["insc_break_case_mc1"].ToString()),
Insc_drop_mc1 = int.Parse(dataReader["insc_drop_mc1"].ToString()),
Insc_break_wire_mc1 = int.Parse(dataReader["insc_break_wire_mc1"].ToString()),
Insc_break_ring_mc1 = int.Parse(dataReader["insc_break_ring_mc1"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
