private void GridBindHour()
{
production_controller_dgv.DataSource = null;
try
{
ProductionControllerNCVCVo vo = new ProductionControllerNCVCVo
{
ProLine = line_cmb.Text,
Date = timefrom_dtp.Text,
ProModel = model_cmb.Text,
};
ValueObjectList <ProductionControllerNCVCVo> volist = (ValueObjectList <ProductionControllerNCVCVo>)DefaultCbmInvoker.Invoke(new SearchMainProByHourNCVCCbm(), 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)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select distinct line_cd from t_ncvc_pdc_fc union select 'All Line' ");
sql.Append("order by line_cd");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
ProLine = dataReader["line_cd"].ToString(),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select tblin.datestimes, tblin.model_cd, tblin.line_cd, ( final_app + en2 + en1 + case_assy + case_bonding + case_mg + trust_gap + rotor + bracket_assy + bracket_metal) as sum_ng,ca_input_line,ca_input, ba_input, fc_input,output, final_app, en2, en1, case_assy, case_bonding, case_mg, trust_gap, rotor, bracket_assy, bracket_metal from
(select(fc.dates + fc.times) datestimes, fc.model_cd, fc.line_cd, ca_input_line,ca_input, ba_input, fc_input, output, (fc_endplay_small + fc_endplay_big + fc_shaft_scracth + fc_terminal_low + fc_case_scracth_dirty + fc_pinion_worm_ng + fc_shaft_lock + fc_ba_deform + fc_tape_hole_deform + fc_brush_rust + fc_metal_deform_scracth + fc_washer_tape_hole) final_app, (en2_insulation_resistance_ng + en2_cut_coil_wire + en2_no_load_current_hight + en2_ripple + en2_chattering + en2_lock + en2_open + en2_no_load_speed_low + en2_starting_voltage + en2_no_load_speed_high + en2_rotor_mix + en2_surge_volt_max + en2_wrong_post_of_pole + en2_err + en2_noise) en2, (en1_insulation_resistace_ng + en1_cut_coil_wire + en1_lock + en1_wareform_ma_abnormal + en1_shaft_bent + en1_ripple + en1_short + en1_chattering + en1_no_load_current_high + en1_vibration_ng + en1_open + en1_rotor_mix) en1, (ca_app_metal_dirty + ca_app_tape_hole_deform + ca_app_metal_high + ca_app_case_deform_scracth + ca_app_metal_deform_scratch + ca_app_magnet_broken) as case_assy, (ca_mg_metal_deform_scratch + ca_mg_case_deform_scratch) as case_mg, (ca_bonding_metal_deform_scratch + ca_bonding_case_deform_scracth) case_bonding, (ba_tc_endplay_big + ba_tc_endplay_small + ba_tc_brush_bent + ba_tc_shaft_mix) as trust_gap, (ba_rto_ng + ba_rto_mix) as rotor, (ba_app_metal_deform_scracth + ba_app_ba_deform + ba_app_endplate_deform_scracth + ba_app_error_other) as bracket_assy, (ba_bm_brush_deform_scracth + ba_bm_metal_deform_scracth + ba_bm_ba_deform + ba_bm_endplay_deform_scracth) as bracket_metal
from t_ncvc_pdc_fc fc left join t_ncvc_pdc_en2 e2 on fc.fc_id = e2.en2_id and fc.line_cd = e2.line_cd and fc.dates = e2.dates
left join t_ncvc_pdc_en1 e1 on fc.fc_id = e1.en1_id and fc.line_cd = e1.line_cd
left join t_ncvc_pdc_ca ca on fc.fc_id = ca.ca_id and fc.line_cd = ca.line_cd
left join t_ncvc_pdc_ba ba on fc.fc_id = ba.ba_id and fc.line_cd = ba.line_cd
where fc.line_cd = :line_cd and fc.dates = :dates and(fc.times in(select min(times) from t_ncvc_pdc_fc where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) or fc.dates - 1 = :dates and fc.line_cd = :line_cd and fc.line_cd = :line_cd and(fc.times in(select min(times) from t_ncvc_pdc_fc where times between '00:00:00' and '00:55:00' and dates - 1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '05:00:00' and '05:56:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd))) tblin");
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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
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["ca_input_line"].ToString()),
ProInputCase = int.Parse(dataReader["ca_input"].ToString()),
ProInputBracket = int.Parse(dataReader["ba_input"].ToString()),
ProInputApp = int.Parse(dataReader["fc_input"].ToString()),
ProOutput = int.Parse(dataReader["output"].ToString()),
Final_App = int.Parse(dataReader["final_app"].ToString()),
En2NG = int.Parse(dataReader["en2"].ToString()),
Case_Assy = int.Parse(dataReader["case_assy"].ToString()),
En1NG = int.Parse(dataReader["en1"].ToString()),
MG_Bongding = int.Parse(dataReader["case_bonding"].ToString()),
Case_MG = int.Parse(dataReader["case_mg"].ToString()),
TrustGap = int.Parse(dataReader["trust_gap"].ToString()),
Rotor = int.Parse(dataReader["rotor"].ToString()),
Braket = int.Parse(dataReader["bracket_assy"].ToString()),
Bracket_Metal = int.Parse(dataReader["bracket_metal"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//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, model_cd,'All Line' line_cd, sum(en1_insulation_resistace_ng) en1_insulation_resistace_ng, ");
sql.Append("sum(en1_cut_coil_wire) en1_cut_coil_wire, sum(en1_lock) en1_lock ,");
sql.Append("sum(en1_wareform_ma_abnormal) en1_wareform_ma_abnormal, sum(en1_shaft_bent) en1_shaft_bent, ");
sql.Append("sum(en1_ripple) en1_ripple, sum(en1_short) en1_short, ");
sql.Append("sum(en1_chattering) en1_chattering, sum(en1_no_load_current_high) en1_no_load_current_high, ");
sql.Append("sum(en1_vibration_ng) en1_vibration_ng, sum(en1_open) en1_open, sum(en1_rotor_mix) en1_rotor_mix from ");
sql.Append("(select i2.dates,i2.times,i2.model_cd,i2.line_cd, en1_insulation_resistace_ng, en1_cut_coil_wire, en1_lock,en1_wareform_ma_abnormal, en1_shaft_bent, en1_ripple, en1_short, en1_chattering, en1_no_load_current_high, en1_vibration_ng, en1_open, en1_rotor_mix from t_ncvc_pdc_en1 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.en1_id) idca1 from t_ncvc_pdc_en1 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.en1_id) idca3 from t_ncvc_pdc_en1 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.en1_id = l.id order by i2.dates,i2.line_cd ) t where model_cd = :model_cd group by datesss,model_cd 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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
En1_insulation_resistace_ng = int.Parse(dataReader["en1_insulation_resistace_ng"].ToString()),
En1_cut_coil_wire = int.Parse(dataReader["en1_cut_coil_wire"].ToString()),
En1_lock = int.Parse(dataReader["en1_lock"].ToString()),
En1_wareform_ma_abnormal = int.Parse(dataReader["en1_wareform_ma_abnormal"].ToString()),
En1_shaft_bent = int.Parse(dataReader["en1_shaft_bent"].ToString()),
En1_ripple = int.Parse(dataReader["en1_ripple"].ToString()),
En1_short = int.Parse(dataReader["en1_short"].ToString()),
En1_chattering = int.Parse(dataReader["en1_chattering"].ToString()),
En1_vibration_ng = int.Parse(dataReader["en1_vibration_ng"].ToString()),
En1_open = int.Parse(dataReader["en1_open"].ToString()),
En1_rotor_mix = int.Parse(dataReader["en1_rotor_mix"].ToString()),
En1_no_load_current_high = int.Parse(dataReader["en1_no_load_current_high"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//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, model_cd,'All Line' line_cd, sum(fc_endplay_small) fc_endplay_small, ");
sql.Append("sum(fc_endplay_big) fc_endplay_big, sum(fc_shaft_scracth) fc_shaft_scracth ,");
sql.Append("sum(fc_terminal_low) fc_terminal_low, sum(fc_case_scracth_dirty) fc_case_scracth_dirty, ");
sql.Append("sum(fc_pinion_worm_ng) fc_pinion_worm_ng, ");
sql.Append("sum(fc_shaft_lock) fc_shaft_lock, sum(fc_ba_deform) fc_ba_deform, ");
sql.Append("sum(fc_tape_hole_deform) fc_tape_hole_deform, sum(fc_brush_rust) fc_brush_rust, ");
sql.Append("sum(fc_metal_deform_scracth) fc_metal_deform_scracth, sum(fc_washer_tape_hole ) fc_washer_tape_hole from ");
sql.Append("(select i2.dates,i2.times,i2.model_cd,i2.line_cd, fc_endplay_small, fc_endplay_big, fc_shaft_scracth, fc_terminal_low, fc_case_scracth_dirty, fc_pinion_worm_ng, fc_shaft_lock, fc_ba_deform, fc_tape_hole_deform, fc_brush_rust, fc_metal_deform_scracth, fc_washer_tape_hole from t_ncvc_pdc_fc 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.fc_id) idca1 from t_ncvc_pdc_fc 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.fc_id) idca3 from t_ncvc_pdc_fc 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.fc_id = l.id order by i2.dates,i2.line_cd ) t where model_cd= :model_cd group by datesss,model_cd 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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
FC_endplay_small = int.Parse(dataReader["fc_endplay_small"].ToString()),
FC_endplay_big = int.Parse(dataReader["fc_endplay_big"].ToString()),
FC_shaft_scracth = int.Parse(dataReader["fc_shaft_scracth"].ToString()),
FC_terminal_low = int.Parse(dataReader["fc_terminal_low"].ToString()),
FC_case_scracth_dirty = int.Parse(dataReader["fc_case_scracth_dirty"].ToString()),
FC_pinion_worm_ng = int.Parse(dataReader["fc_pinion_worm_ng"].ToString()),
FC_shaft_lock = int.Parse(dataReader["fc_shaft_lock"].ToString()),
FC_deform = int.Parse(dataReader["fc_ba_deform"].ToString()),
FC_tape_hole_deform = int.Parse(dataReader["fc_tape_hole_deform"].ToString()),
FC_brush_rust = int.Parse(dataReader["fc_brush_rust"].ToString()),
FC_metal_deform_scracth = int.Parse(dataReader["fc_metal_deform_scracth"].ToString()),
FC_washer_tape_hole = int.Parse(dataReader["fc_washer_tape_hole"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//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,model_cd,'All Line' line_cd, sum(ca_app_metal_dirty) ca_app_metal_dirty, sum(ca_app_tape_hole_deform) ca_app_tape_hole_deform, sum(ca_app_metal_high) ca_app_metal_high, sum(ca_app_case_deform_scracth) ca_app_case_deform_scracth, sum(ca_app_metal_deform_scratch) ca_app_metal_deform_scratch, sum(ca_app_magnet_broken) ca_app_magnet_broken from ");
sql.Append("(select i2.dates,i2.times,i2.model_cd,i2.line_cd, ca_app_metal_dirty, ca_app_tape_hole_deform, ca_app_metal_high, ca_app_case_deform_scracth, ca_app_metal_deform_scratch, ca_app_magnet_broken from t_ncvc_pdc_ca 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.ca_id) idca1 from t_ncvc_pdc_ca 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.ca_id) idca3 from t_ncvc_pdc_ca 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.ca_id = l.id order by i2.dates,i2.line_cd ) t where model_cd = :model_cd group by datesss,model_cd 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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
CA_app_metal_dirty = int.Parse(dataReader["ca_app_metal_dirty"].ToString()),
CA_app_tape_hole_deform = int.Parse(dataReader["ca_app_tape_hole_deform"].ToString()),
CA_app_metal_high = int.Parse(dataReader["ca_app_metal_high"].ToString()),
CA_app_case_deform_scracth = int.Parse(dataReader["ca_app_case_deform_scracth"].ToString()),
CA_app_metal_deform_scratch = int.Parse(dataReader["ca_app_metal_deform_scratch"].ToString()),
CA_app_magnet_broken = int.Parse(dataReader["ca_app_magnet_broken"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//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, model_cd,'All Line' line_cd, sum(ba_tc_endplay_big) ba_tc_endplay_big, ");
sql.Append("sum(ba_tc_endplay_small) ba_tc_endplay_small, sum(ba_tc_brush_bent) ba_tc_brush_bent ,");
sql.Append("sum(ba_tc_shaft_mix) ba_tc_shaft_mix from ");
sql.Append("(select i2.dates,i2.times,i2.model_cd,i2.line_cd, ba_tc_endplay_big, ba_tc_endplay_small, ba_tc_brush_bent, ba_tc_shaft_mix from t_ncvc_pdc_ba 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.ba_id) idca1 from t_ncvc_pdc_ba 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.ba_id) idca3 from t_ncvc_pdc_ba 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.ba_id = l.id order by i2.dates,i2.line_cd ) t where model_cd = :model_cd group by datesss,model_cd 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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
BA_tc_endplay_big = int.Parse(dataReader["ba_tc_endplay_big"].ToString()),
BA_tc_endplay_small = int.Parse(dataReader["ba_tc_endplay_small"].ToString()),
BA_tc_brush_bent = int.Parse(dataReader["ba_tc_brush_bent"].ToString()),
BA_tc_shaft_mix = int.Parse(dataReader["ba_tc_shaft_mix"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select (fc.dates+fc.times) datetimes, fc.model_cd,fc.line_cd, fc.process_cd, ");
sql.Append("fc_endplay_small, fc_endplay_big, fc_shaft_scracth, fc_terminal_low, fc_case_scracth_dirty, fc_pinion_worm_ng, fc_shaft_lock, fc_ba_deform, fc_tape_hole_deform, fc_brush_rust, fc_metal_deform_scracth, fc_washer_tape_hole ");
sql.Append("from t_ncvc_pdc_fc fc ");
sql.Append("where fc.line_cd = :line_cd ");
sql.Append("and fc.dates = :dates ");
sql.Append("and (fc.times in(select min(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.dates-1 =:dates and fc.line_cd = :line_cd ");
sql.Append("and (fc.times in(select min(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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 fc.times in(select max(times) from t_ncvc_pdc_fc 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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
FC_endplay_small = int.Parse(dataReader["fc_endplay_small"].ToString()),
FC_endplay_big = int.Parse(dataReader["fc_endplay_big"].ToString()),
FC_shaft_scracth = int.Parse(dataReader["fc_shaft_scracth"].ToString()),
FC_terminal_low = int.Parse(dataReader["fc_terminal_low"].ToString()),
FC_case_scracth_dirty = int.Parse(dataReader["fc_case_scracth_dirty"].ToString()),
FC_pinion_worm_ng = int.Parse(dataReader["fc_pinion_worm_ng"].ToString()),
FC_shaft_lock = int.Parse(dataReader["fc_shaft_lock"].ToString()),
FC_deform = int.Parse(dataReader["fc_ba_deform"].ToString()),
FC_tape_hole_deform = int.Parse(dataReader["fc_tape_hole_deform"].ToString()),
FC_brush_rust = int.Parse(dataReader["fc_brush_rust"].ToString()),
FC_metal_deform_scracth = int.Parse(dataReader["fc_metal_deform_scracth"].ToString()),
FC_washer_tape_hole = int.Parse(dataReader["fc_washer_tape_hole"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select Case When fc.times between '06:00:00' and '23:59:00' then fc.dates when fc.times between '00:00:00' and '05:59:00' then fc.dates-1 end datesss,fc.line_cd l, fc.model_cd model, *from t_ncvc_pdc_fc fc left join t_ncvc_pdc_en2 e2 on fc.fc_id = e2.en2_id left join t_ncvc_pdc_en1 e1 on fc.fc_id = e1.en1_id left join t_ncvc_pdc_ca ca on fc.fc_id = ca.ca_id left join t_ncvc_pdc_ba ba on fc.fc_id = ba.ba_id left join (select dates date1, line_cd line1, 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.fc_id) idca1 from t_ncvc_pdc_fc 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.fc_id) idca3 from t_ncvc_pdc_fc o where o.times > '00:00:00' and o.times <= '05:55: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.line1 = fc.line_cd where fc.fc_id = l.id and l.line1 = e2.line_cd and l.line1 = e1.line_cd and l.line1 = ca.line_cd and fc.line_cd = :line_cd order by fc.dates, fc.line_cd");
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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model"].ToString(),
ProLine = dataReader["l"].ToString(),
FC_endplay_small = int.Parse(dataReader["fc_endplay_small"].ToString()),
FC_endplay_big = int.Parse(dataReader["fc_endplay_big"].ToString()),
FC_shaft_scracth = int.Parse(dataReader["fc_shaft_scracth"].ToString()),
FC_terminal_low = int.Parse(dataReader["fc_terminal_low"].ToString()),
FC_case_scracth_dirty = int.Parse(dataReader["fc_case_scracth_dirty"].ToString()),
FC_pinion_worm_ng = int.Parse(dataReader["fc_pinion_worm_ng"].ToString()),
FC_shaft_lock = int.Parse(dataReader["fc_shaft_lock"].ToString()),
FC_deform = int.Parse(dataReader["fc_ba_deform"].ToString()),
FC_tape_hole_deform = int.Parse(dataReader["fc_tape_hole_deform"].ToString()),
FC_brush_rust = int.Parse(dataReader["fc_brush_rust"].ToString()),
FC_metal_deform_scracth = int.Parse(dataReader["fc_metal_deform_scracth"].ToString()),
FC_washer_tape_hole = int.Parse(dataReader["fc_washer_tape_hole"].ToString()),
En2_insulation_resistance_ng = int.Parse(dataReader["en2_insulation_resistance_ng"].ToString()),
En2_cut_coil_wire = int.Parse(dataReader["en2_cut_coil_wire"].ToString()),
En2_no_load_current_hight = int.Parse(dataReader["en2_no_load_current_hight"].ToString()),
En2_ripple = int.Parse(dataReader["en2_ripple"].ToString()),
En2_chattering = int.Parse(dataReader["en2_chattering"].ToString()),
En2_lock = int.Parse(dataReader["en2_lock"].ToString()),
En2_open = int.Parse(dataReader["en2_open"].ToString()),
En2_no_load_speed_low = int.Parse(dataReader["en2_no_load_speed_low"].ToString()),
En2_starting_voltage = int.Parse(dataReader["en2_starting_voltage"].ToString()),
En2_no_load_speed_high = int.Parse(dataReader["en2_no_load_speed_high"].ToString()),
En2_rotor_mix = int.Parse(dataReader["en2_rotor_mix"].ToString()),
En2_surge_volt_max = int.Parse(dataReader["en2_surge_volt_max"].ToString()),
En2_wrong_post_of_pole = int.Parse(dataReader["en2_wrong_post_of_pole"].ToString()),
En2_err = int.Parse(dataReader["en2_err"].ToString()),
En2_noise = int.Parse(dataReader["en2_noise"].ToString()),
En1_insulation_resistace_ng = int.Parse(dataReader["en1_insulation_resistace_ng"].ToString()),
En1_cut_coil_wire = int.Parse(dataReader["en1_cut_coil_wire"].ToString()),
En1_lock = int.Parse(dataReader["en1_lock"].ToString()),
En1_wareform_ma_abnormal = int.Parse(dataReader["en1_wareform_ma_abnormal"].ToString()),
En1_shaft_bent = int.Parse(dataReader["en1_shaft_bent"].ToString()),
En1_ripple = int.Parse(dataReader["en1_ripple"].ToString()),
En1_short = int.Parse(dataReader["en1_short"].ToString()),
En1_chattering = int.Parse(dataReader["en1_chattering"].ToString()),
En1_no_load_current_high = int.Parse(dataReader["en1_no_load_current_high"].ToString()),
En1_vibration_ng = int.Parse(dataReader["en1_vibration_ng"].ToString()),
En1_open = int.Parse(dataReader["en1_open"].ToString()),
En1_rotor_mix = int.Parse(dataReader["en1_rotor_mix"].ToString()),
BA_tc_endplay_big = int.Parse(dataReader["ba_tc_endplay_big"].ToString()),
BA_tc_endplay_small = int.Parse(dataReader["ba_tc_endplay_small"].ToString()),
BA_tc_brush_bent = int.Parse(dataReader["ba_tc_brush_bent"].ToString()),
BA_tc_shaft_mix = int.Parse(dataReader["ba_tc_shaft_mix"].ToString()),
BA_rto_ng = int.Parse(dataReader["ba_rto_ng"].ToString()),
BA_rto_mix = int.Parse(dataReader["ba_rto_mix"].ToString()),
BA_app_metal_deform_scracth = int.Parse(dataReader["ba_app_metal_deform_scracth"].ToString()),
BA_app_deform = int.Parse(dataReader["ba_app_ba_deform"].ToString()),
BA_app_endplate_deform_scracth = int.Parse(dataReader["ba_app_endplate_deform_scracth"].ToString()),
BA_app_error_other = int.Parse(dataReader["ba_app_error_other"].ToString()),
BA_bm_brush_deform_scracth = int.Parse(dataReader["ba_bm_brush_deform_scracth"].ToString()),
BA_bm_metal_deform_scracth = int.Parse(dataReader["ba_bm_metal_deform_scracth"].ToString()),
BA_bm_deform = int.Parse(dataReader["ba_bm_ba_deform"].ToString()),
BA_bm_endplay_deform_scracth = int.Parse(dataReader["ba_bm_endplay_deform_scracth"].ToString()),
CA_app_metal_dirty = int.Parse(dataReader["ca_app_metal_dirty"].ToString()),
CA_app_tape_hole_deform = int.Parse(dataReader["ca_app_tape_hole_deform"].ToString()),
CA_app_metal_high = int.Parse(dataReader["ca_app_metal_high"].ToString()),
CA_app_case_deform_scracth = int.Parse(dataReader["ca_app_case_deform_scracth"].ToString()),
CA_app_metal_deform_scratch = int.Parse(dataReader["ca_app_metal_deform_scratch"].ToString()),
CA_app_magnet_broken = int.Parse(dataReader["ca_app_magnet_broken"].ToString()),
CA_mg_metal_deform_scratch = int.Parse(dataReader["ca_mg_metal_deform_scratch"].ToString()),
CA_mg_case_deform_scratch = int.Parse(dataReader["ca_mg_case_deform_scratch"].ToString()),
CA_bonding_metal_deform_scratch = int.Parse(dataReader["ca_bonding_metal_deform_scratch"].ToString()),
CA_bonding_case_deform_scracth = int.Parse(dataReader["ca_bonding_case_deform_scracth"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//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, ba_rto_ng,ba_rto_mix ");
sql.Append("from t_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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_ncvc_pdc_ba 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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
//StartDay = DateTime.Parse( dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
BA_rto_ng = int.Parse(dataReader["ba_rto_ng"].ToString()),
BA_rto_mix = int.Parse(dataReader["ba_rto_mix"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select (ca.dates+ca.times) datetimes, ca.model_cd,ca.line_cd, ca.process_cd, ");
sql.Append("ca_app_metal_dirty, ca_app_tape_hole_deform, ca_app_metal_high, ca_app_case_deform_scracth, ca_app_metal_deform_scratch, ca_app_magnet_broken ");
sql.Append("from t_ncvc_pdc_ca ca ");
sql.Append("where ca.line_cd = :line_cd ");
sql.Append("and ca.dates = :dates ");
sql.Append("and (ca.times in(select min(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.dates-1 =:dates and ca.line_cd = :line_cd ");
sql.Append("and (ca.times in(select min(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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 ca.times in(select max(times) from t_ncvc_pdc_ca 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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
CA_app_metal_dirty = int.Parse(dataReader["ca_app_metal_dirty"].ToString()),
CA_app_tape_hole_deform = int.Parse(dataReader["ca_app_tape_hole_deform"].ToString()),
CA_app_metal_high = int.Parse(dataReader["ca_app_metal_high"].ToString()),
CA_app_case_deform_scracth = int.Parse(dataReader["ca_app_case_deform_scracth"].ToString()),
CA_app_metal_deform_scratch = int.Parse(dataReader["ca_app_metal_deform_scratch"].ToString()),
CA_app_magnet_broken = int.Parse(dataReader["ca_app_magnet_broken"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select tbl.dates,f.dates+f.times endday,f.model_cd,f.line_cd,ca_input_line, ca_input, ba_input, fc_input, output,
(fc_endplay_small + fc_endplay_big + fc_shaft_scracth + fc_terminal_low +
fc_case_scracth_dirty + fc_pinion_worm_ng + fc_shaft_lock + fc_ba_deform +
fc_tape_hole_deform + fc_brush_rust + fc_metal_deform_scracth + fc_washer_tape_hole+ en2_insulation_resistance_ng + en2_cut_coil_wire + en2_no_load_current_hight + en2_ripple + en2_chattering + en2_lock + en2_open + en2_no_load_speed_low + en2_starting_voltage + en2_no_load_speed_high + en2_rotor_mix + en2_surge_volt_max + en2_wrong_post_of_pole + en2_err + en2_noise + en1_insulation_resistace_ng + en1_cut_coil_wire + en1_lock +
en1_wareform_ma_abnormal + en1_shaft_bent + en1_ripple + en1_short + en1_chattering + en1_no_load_current_high + en1_vibration_ng + en1_open + en1_rotor_mix + ca_app_metal_dirty + ca_app_tape_hole_deform + ca_app_metal_high + ca_app_case_deform_scracth + ca_app_metal_deform_scratch + ca_app_magnet_broken+ ca_mg_metal_deform_scratch + ca_mg_case_deform_scratch+ca_bonding_metal_deform_scratch +
ca_bonding_case_deform_scracth + ba_tc_endplay_big + ba_tc_endplay_small + ba_tc_brush_bent + ba_tc_shaft_mix + ba_rto_ng + ba_rto_mix+ ba_app_metal_deform_scracth + ba_app_ba_deform + ba_app_endplate_deform_scracth + ba_app_error_other + ba_bm_brush_deform_scracth + ba_bm_metal_deform_scracth + ba_bm_ba_deform + ba_bm_endplay_deform_scracth) total_ng,
(fc_endplay_small + fc_endplay_big + fc_shaft_scracth + fc_terminal_low +
fc_case_scracth_dirty + fc_pinion_worm_ng + fc_shaft_lock + fc_ba_deform +
fc_tape_hole_deform + fc_brush_rust + fc_metal_deform_scracth + fc_washer_tape_hole) final_app,(en2_insulation_resistance_ng + en2_cut_coil_wire + en2_no_load_current_hight + en2_ripple + en2_chattering + en2_lock + en2_open + en2_no_load_speed_low + en2_starting_voltage + en2_no_load_speed_high +
en2_rotor_mix + en2_surge_volt_max + en2_wrong_post_of_pole + en2_err + en2_noise) en2, (en1_insulation_resistace_ng + en1_cut_coil_wire + en1_lock +
en1_wareform_ma_abnormal + en1_shaft_bent + en1_ripple + en1_short + en1_chattering + en1_no_load_current_high + en1_vibration_ng + en1_open + en1_rotor_mix) en1, (ca_app_metal_dirty + ca_app_tape_hole_deform + ca_app_metal_high + ca_app_case_deform_scracth + ca_app_metal_deform_scratch + ca_app_magnet_broken) as case_assy,(ca_mg_metal_deform_scratch + ca_mg_case_deform_scratch) as case_mg,(ca_bonding_metal_deform_scratch +
ca_bonding_case_deform_scracth) case_bonding, (ba_tc_endplay_big + ba_tc_endplay_small + ba_tc_brush_bent + ba_tc_shaft_mix) as trust_gap, (ba_rto_ng + ba_rto_mix) as rotor, (ba_app_metal_deform_scracth + ba_app_ba_deform + ba_app_endplate_deform_scracth + ba_app_error_other) as bracket_assy, (ba_bm_brush_deform_scracth + ba_bm_metal_deform_scracth + ba_bm_ba_deform + ba_bm_endplay_deform_scracth) as bracket_metal from t_ncvc_pdc_fc f left join t_ncvc_pdc_en2 e2 on f.fc_id = e2.en2_id
left join t_ncvc_pdc_en1 e1 on f.fc_id = e1.en1_id
left join t_ncvc_pdc_ca ca on f.fc_id = ca.ca_id
left join t_ncvc_pdc_ba ba on f.fc_id = ba.ba_id
left join (
select t1.dates,case when ca3 is null then ca1 else ca3 end id from
(select dates, line_cd, max(fc_id)ca1 from t_ncvc_pdc_fc where times > '06:00:00' and times <= '23:59:00' group by dates, line_cd) t1 left join
(select dates - 1 dates, line_cd, max(fc_id) ca3 from t_ncvc_pdc_fc where times > '00:00:00' and times <= '05:59:00' group by dates, line_cd) t3 on t1.dates = t3.dates where t1.line_cd = :line_cd and t1.dates between :datesfrom and :datesto) tbl on f.fc_id = tbl.id where f.fc_id = tbl.id and f.line_cd = :line_cd ");
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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
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["total_ng"].ToString()),
ProInput = int.Parse(dataReader["ca_input_line"].ToString()),
ProInputCase = int.Parse(dataReader["ca_input"].ToString()),
ProInputBracket = int.Parse(dataReader["ba_input"].ToString()),
ProInputApp = int.Parse(dataReader["fc_input"].ToString()),
ProOutput = int.Parse(dataReader["output"].ToString()),
Final_App = int.Parse(dataReader["final_app"].ToString()),
En2NG = int.Parse(dataReader["en2"].ToString()),
En1NG = int.Parse(dataReader["en1"].ToString()),
TrustGap = int.Parse(dataReader["trust_gap"].ToString()),
Rotor = int.Parse(dataReader["rotor"].ToString()),
Braket = int.Parse(dataReader["bracket_assy"].ToString()),
Bracket_Metal = int.Parse(dataReader["bracket_metal"].ToString()),
Case_Assy = int.Parse(dataReader["case_assy"].ToString()),
Case_MG = int.Parse(dataReader["case_mg"].ToString()),
MG_Bongding = int.Parse(dataReader["case_bonding"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select model_cd,'All Line' line_cd, 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, sum(ca_input_line) as ca_input_line,sum(ba_input) ba_input, sum(ca_input) as ca_input, sum(fc_input) as fc_input,sum(output) output,sum(final_app+en2+en1+case_assy+case_mg+case_bonding+trust_gap+rotor+bracket_assy+bracket_metal) as total_ng,sum(final_app) final_app, sum(en2) en2, sum(en1) en1,sum(case_assy) case_assy,sum(case_mg) case_mg, sum(case_bonding) case_bonding,sum(trust_gap) trust_gap,sum(rotor) rotor,sum(bracket_assy) bracket_assy,sum(bracket_metal) bracket_metal from
(Select fc.dates ,fc.times ,fc.fc_id ,fc.model_cd ,fc.line_cd,ca_input_line,ca_input,ba_input,fc_input, output, (fc_endplay_small + fc_endplay_big + fc_shaft_scracth + fc_terminal_low + fc_case_scracth_dirty + fc_pinion_worm_ng + fc_shaft_lock + fc_ba_deform + fc_tape_hole_deform + fc_brush_rust + fc_metal_deform_scracth + fc_washer_tape_hole) final_app,(en2_insulation_resistance_ng + en2_cut_coil_wire + en2_no_load_current_hight + en2_ripple + en2_chattering + en2_lock + en2_open + en2_no_load_speed_low + en2_starting_voltage + en2_no_load_speed_high + en2_rotor_mix + en2_surge_volt_max + en2_wrong_post_of_pole + en2_err + en2_noise) en2, (en1_insulation_resistace_ng + en1_cut_coil_wire + en1_lock + en1_wareform_ma_abnormal + en1_shaft_bent + en1_ripple + en1_short + en1_chattering + en1_no_load_current_high + en1_vibration_ng + en1_open + en1_rotor_mix) en1, (ca_app_metal_dirty + ca_app_tape_hole_deform + ca_app_metal_high + ca_app_case_deform_scracth + ca_app_metal_deform_scratch + ca_app_magnet_broken) as case_assy,(ca_mg_metal_deform_scratch + ca_mg_case_deform_scratch) as case_mg,(ca_bonding_metal_deform_scratch + ca_bonding_case_deform_scracth) case_bonding, (ba_tc_endplay_big + ba_tc_endplay_small + ba_tc_brush_bent + ba_tc_shaft_mix) as trust_gap, (ba_rto_ng + ba_rto_mix) as rotor, (ba_app_metal_deform_scracth + ba_app_ba_deform + ba_app_endplate_deform_scracth + ba_app_error_other) as bracket_assy, (ba_bm_brush_deform_scracth + ba_bm_metal_deform_scracth + ba_bm_ba_deform + ba_bm_endplay_deform_scracth) as bracket_metal
from t_ncvc_pdc_fc fc left join t_ncvc_pdc_en2 e2 on fc.line_cd = e2.line_cd and fc.fc_id = e2.en2_id left join t_ncvc_pdc_en1 e1 on fc.line_cd = e1.line_cd and fc.fc_id = e1.en1_id left join t_ncvc_pdc_ca ca on fc.line_cd = ca.line_cd and fc.fc_id = ca.ca_id left join
t_ncvc_pdc_ba ba on fc.line_cd = ba.line_cd and fc.fc_id = ba.ba_id 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.fc_id) idca1 from t_ncvc_pdc_fc o 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 left join (select line_cd,(o.dates-1) dates , max(o.fc_id) idca3 from t_ncvc_pdc_fc o where o.times > '00:00:00' and o.times <= '05:55:00' and o.dates > :datefrom and o.dates - 1 <= :datesto 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 = fc.line_cd where l.id = fc.fc_id order by fc.dates,fc.line_cd ) tbl where model_cd = :model_cd group by datesss,model_cd order by datesss");
sqlParameter.AddParameterDateTime("datefrom", inVo.StartDay);
sqlParameter.AddParameterDateTime("datesto", inVo.EndDay);
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);
//IDataAdapter d = sqlCommandAdapter.ExecuteScalar(sqlParameter);
while (dataReader.Read())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
StartDay = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
TotalNG = int.Parse(dataReader["total_ng"].ToString()),
ProInput = int.Parse(dataReader["ca_input_line"].ToString()),
ProInputCase = int.Parse(dataReader["ca_input"].ToString()),
ProInputBracket = int.Parse(dataReader["ba_input"].ToString()),
ProInputApp = int.Parse(dataReader["fc_input"].ToString()),
ProOutput = int.Parse(dataReader["output"].ToString()),
Final_App = int.Parse(dataReader["final_app"].ToString()),
En2NG = int.Parse(dataReader["en2"].ToString()),
Case_Assy = int.Parse(dataReader["case_assy"].ToString()),
En1NG = int.Parse(dataReader["en1"].ToString()),
Case_MG = int.Parse(dataReader["case_mg"].ToString()),
MG_Bongding = int.Parse(dataReader["case_bonding"].ToString()),
TrustGap = int.Parse(dataReader["trust_gap"].ToString()),
Rotor = int.Parse(dataReader["rotor"].ToString()),
Braket = int.Parse(dataReader["bracket_assy"].ToString()),
Bracket_Metal = int.Parse(dataReader["bracket_metal"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select (e1.dates+e1.times) datetimes, e1.model_cd,e1.line_cd, e1.process_cd, ");
sql.Append("en1_insulation_resistace_ng, en1_cut_coil_wire, en1_lock, en1_wareform_ma_abnormal, en1_shaft_bent, en1_ripple, en1_short, en1_chattering, en1_no_load_current_high, en1_vibration_ng, en1_open, en1_rotor_mix ");
sql.Append("from t_ncvc_pdc_en1 e1 ");
sql.Append("where e1.line_cd = :line_cd ");
sql.Append("and e1.dates = :dates ");
sql.Append("and (e1.times in(select min(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.dates-1 =:dates and e1.line_cd = :line_cd ");
sql.Append("and (e1.times in(select min(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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 e1.times in(select max(times) from t_ncvc_pdc_en1 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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
En1_insulation_resistace_ng = int.Parse(dataReader["en1_insulation_resistace_ng"].ToString()),
En1_cut_coil_wire = int.Parse(dataReader["en1_cut_coil_wire"].ToString()),
En1_lock = int.Parse(dataReader["en1_lock"].ToString()),
En1_wareform_ma_abnormal = int.Parse(dataReader["en1_wareform_ma_abnormal"].ToString()),
En1_shaft_bent = int.Parse(dataReader["en1_shaft_bent"].ToString()),
En1_ripple = int.Parse(dataReader["en1_ripple"].ToString()),
En1_short = int.Parse(dataReader["en1_short"].ToString()),
En1_chattering = int.Parse(dataReader["en1_chattering"].ToString()),
En1_no_load_current_high = int.Parse(dataReader["en1_no_load_current_high"].ToString()),
En1_vibration_ng = int.Parse(dataReader["en1_vibration_ng"].ToString()),
En1_open = int.Parse(dataReader["en1_open"].ToString()),
En1_rotor_mix = int.Parse(dataReader["en1_rotor_mix"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select (e2.dates+e2.times) datetimes, e2.model_cd,e2.line_cd, e2.process_cd, ");
sql.Append("en2_insulation_resistance_ng, en2_cut_coil_wire, en2_no_load_current_hight, en2_ripple, en2_chattering, en2_lock, en2_open, en2_no_load_speed_low, en2_starting_voltage, en2_no_load_speed_high, en2_rotor_mix, en2_surge_volt_max, en2_wrong_post_of_pole, en2_err, en2_noise ");
sql.Append("from t_ncvc_pdc_en2 e2 ");
sql.Append("where e2.line_cd = :line_cd ");
sql.Append("and e2.dates = :dates ");
sql.Append("and (e2.times in(select min(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.dates-1 =:dates and e2.line_cd = :line_cd ");
sql.Append("and (e2.times in(select min(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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 e2.times in(select max(times) from t_ncvc_pdc_en2 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())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
En2_insulation_resistance_ng = int.Parse(dataReader["en2_insulation_resistance_ng"].ToString()),
En2_cut_coil_wire = int.Parse(dataReader["en2_cut_coil_wire"].ToString()),
En2_no_load_current_hight = int.Parse(dataReader["en2_no_load_current_hight"].ToString()),
En2_ripple = int.Parse(dataReader["en2_ripple"].ToString()),
En2_chattering = int.Parse(dataReader["en2_chattering"].ToString()),
En2_lock = int.Parse(dataReader["en2_lock"].ToString()),
En2_open = int.Parse(dataReader["en2_open"].ToString()),
En2_no_load_speed_low = int.Parse(dataReader["en2_no_load_speed_low"].ToString()),
En2_starting_voltage = int.Parse(dataReader["en2_starting_voltage"].ToString()),
En2_no_load_speed_high = int.Parse(dataReader["en2_no_load_speed_high"].ToString()),
En2_rotor_mix = int.Parse(dataReader["en2_rotor_mix"].ToString()),
En2_surge_volt_max = int.Parse(dataReader["en2_surge_volt_max"].ToString()),
En2_wrong_post_of_pole = int.Parse(dataReader["en2_wrong_post_of_pole"].ToString()),
En2_err = int.Parse(dataReader["en2_err"].ToString()),
En2_noise = int.Parse(dataReader["en2_noise"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
