public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
ItemLineInspectionFormatVo inVo = (ItemLineInspectionFormatVo)arg;
StringBuilder sql = new StringBuilder();
//create SQL
sql.Append("Select Count(*) as itemlineinspectionformatcount from m_item_line_inspection_format ilf");
sql.Append(" inner join m_inspection_format if on if.inspection_format_id = ilf.inspection_format_id ");
sql.Append(" where ilf.factory_cd = :faccd ");
sql.Append(" and if.is_deleted = '0' ");
if (inVo.SapItemCode != null)
{
sql.Append(" and sap_matnr_item_cd = :sapitemcd");
}
if (inVo.LineId > 0)
{
sql.Append(" and line_id = :lineid");
}
if (inVo.Mode.Equals(CommonConstants.MODE_UPDATE.ToString()) && inVo.ItemLineInspectionFormatId > 0)
{
sql.Append(" and item_line_inspection_format_id <> :itemlineinspectionformatid ");
}
if (inVo.InspectionFormatId > 0)
{
sql.Append(" or ( ");
if (inVo.Mode.Equals(CommonConstants.MODE_UPDATE.ToString()) && inVo.ItemLineInspectionFormatId > 0)
{
sql.Append(" item_line_inspection_format_id <> :itemlineinspectionformatid and ");
}
sql.Append(" inspection_format_id = :inspectionformatid and factory_cd = :faccd) ");
}
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sqlParameter.AddParameterString("faccd", UserData.GetUserData().FactoryCode);
if (inVo.SapItemCode != null)
{
sqlParameter.AddParameterString("sapitemcd", inVo.SapItemCode);
}
if (inVo.LineId > 0)
{
sqlParameter.AddParameterInteger("lineid", inVo.LineId);
}
if (inVo.InspectionFormatId > 0)
{
sqlParameter.AddParameterInteger("inspectionformatid", inVo.InspectionFormatId);
}
if (inVo.Mode.Equals(CommonConstants.MODE_UPDATE.ToString()) && inVo.ItemLineInspectionFormatId > 0)
{
sqlParameter.AddParameterInteger("itemlineinspectionformatid", inVo.ItemLineInspectionFormatId);
}
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
ItemLineInspectionFormatVo outVo = new ItemLineInspectionFormatVo {
AffectedCount = 0
};
while (dataReader.Read())
{
outVo.AffectedCount = Convert.ToInt32(dataReader["itemlineinspectionformatcount"]);
}
dataReader.Close();
return(outVo);
}
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("en2_lock, en2_cut, en2_chattering, en2_insulation, en2_open, en2_short, en2_duty, ");
sql.Append("en2_no, en2_var, en2_reverse_spinning, en2_starting_volt, en2_io ");
sql.Append("from t_productioncontroller_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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_output03 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(),
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)
{
MaintenanceMachineVTVo inVo = (MaintenanceMachineVTVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <MaintenanceMachineVTVo> voList = new ValueObjectList <MaintenanceMachineVTVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select a.maintenance_id, a.machine_model, a.machine_serial, a.start_day, a.month_repeat, a.check_status, a.registration_user_cd,
a.registration_date_time, a.factory_cd, b.machine_name, b.machine_suppiler, b.machine_status, b.machine_qty, b.rfid_cd, b.machine_location
from t_vt_maintenance a
left join t_vt_machine b on a.machine_serial = b.machine_serial where 1=1 ");
if (!String.IsNullOrEmpty(inVo.RFId))
{
sql.Append(@" and b.rfid_cd =:rfid_cd");
sqlParameter.AddParameterString("rfid_cd", inVo.RFId);
}
if (!String.IsNullOrEmpty(inVo.MachineSerial))
{
sql.Append(" and a.machine_serial =:machine_serial");
sqlParameter.AddParameterString("machine_serial", inVo.MachineSerial);
}
if (!String.IsNullOrEmpty(inVo.MachineModel))
{
sql.Append(" and b.machine_model =:machine_model");
sqlParameter.AddParameterString("machine_model", inVo.MachineModel);
}
if (!String.IsNullOrEmpty(inVo.MachineSupplier))
{
sql.Append(" and b.machine_suppiler =:machine_suppiler");
sqlParameter.AddParameterString("machine_suppiler", inVo.MachineSupplier);
}
if (!String.IsNullOrEmpty(inVo.MachineLocation))
{
sql.Append(" and b.machine_location =:machine_location");
sqlParameter.AddParameterString("machine_location", inVo.MachineLocation);
}
if (!String.IsNullOrEmpty(inVo.MachineStatus))
{
sql.Append(" and b.machine_status =:machine_status");
sqlParameter.AddParameterString("machine_status", inVo.MachineStatus);
}
//Danh sách Lịch sử
if (inVo.SearchStatus == "Danh sách")
{
sql.Append(" and a.maintenance_id in(select max (maintenance_id) from t_vt_maintenance group by machine_serial)");
}
else if (inVo.SearchStatus == "Lịch sử")
{
}
sql.Append(" order by a.start_day desc");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
MaintenanceMachineVTVo outVo = new MaintenanceMachineVTVo
{
MainternanceId = int.Parse(dataReader["maintenance_id"].ToString()),
RFId = dataReader["rfid_cd"].ToString(),
MachineName = dataReader["machine_name"].ToString(),
MachineSerial = dataReader["machine_serial"].ToString(),
MachineModel = dataReader["machine_model"].ToString(),
MachineLocation = dataReader["machine_location"].ToString(),
MachineSupplier = dataReader["machine_suppiler"].ToString(),
MachineStatus = dataReader["machine_status"].ToString(),
StartDay = DateTime.Parse(dataReader["start_day"].ToString()),
MonthRepeat = int.Parse(dataReader["month_repeat"].ToString()),
CheckStatus = bool.Parse(dataReader["check_status"].ToString()),
RegistrationUserCode = dataReader["registration_user_cd"].ToString(),
RegistrationDateTime = DateTime.Parse(dataReader["registration_date_time"].ToString()),
FactoryCode = dataReader["factory_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 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)
{
MovingMachineVTVo inVo = (MovingMachineVTVo)vo;
StringBuilder sql = new StringBuilder();
sql.Append(@"insert into t_vt_moving(machine_serial,
factory_tranfer_cd ,
factory_received_cd ,
bg_cd,
m_cd,
t_cd,
th_cd,
status,
comments_machine,
reason_tranfer,
confirm_received,
registration_user_cd,
registration_date_time,
factory_cd) ");
sql.Append(@"values(:machine_serial ,
:factory_tranfer_cd,
:factory_received_cd ,
:bg_cd ,
:m_cd,
:t_cd,
:th_cd,
:status,
:comments_machine,
:reason_tranfer,
:confirm_received,
:registration_user_cd,
:registration_date_time,
:factory_cd)");
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sqlParameter.AddParameter("machine_serial", inVo.MachineSerial);
sqlParameter.AddParameter("factory_tranfer_cd", inVo.TranferFactoryName);
sqlParameter.AddParameter("factory_received_cd", inVo.ReceivedFactoryName);
sqlParameter.AddParameter("bg_cd", inVo.BGCode);
sqlParameter.AddParameter("m_cd", inVo.MCode);
sqlParameter.AddParameter("t_cd", inVo.TCode);
sqlParameter.AddParameter("th_cd", inVo.THCode);
sqlParameter.AddParameter("status", inVo.Status);
sqlParameter.AddParameter("comments_machine", inVo.CommentsMachine);
sqlParameter.AddParameter("reason_tranfer", inVo.ReasonTranfer);
sqlParameter.AddParameter("confirm_received", inVo.ConfirmReceived);
sqlParameter.AddParameter("registration_user_cd", UserData.GetUserData().UserCode);
sqlParameter.AddParameter("factory_cd", UserData.GetUserData().FactoryCode);
sqlParameter.AddParameter("registration_date_time", inVo.RegistrationDateTime);
//execute SQL
MovingMachineVTVo outVo = new MovingMachineVTVo
{
AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter)
};
return(outVo);
}
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(en2_insulation_resistance_ng) en2_insulation_resistance_ng, ");
sql.Append("sum(en2_cut_coil_wire) en2_cut_coil_wire, sum(en2_no_load_current_hight) en2_no_load_current_hight ,");
sql.Append("sum(en2_ripple) en2_ripple, sum(en2_chattering) en2_chattering, ");
sql.Append("sum(en2_lock) en2_lock, ");
sql.Append("sum(en2_open) en2_open, sum(en2_no_load_speed_low) en2_no_load_speed_low, ");
sql.Append("sum(en2_starting_voltage) en2_starting_voltage, sum(en2_no_load_speed_high) en2_no_load_speed_high, ");
sql.Append("sum(en2_rotor_mix) en2_rotor_mix, sum(en2_surge_volt_max ) en2_surge_volt_max, ");
sql.Append("sum(en2_wrong_post_of_pole) en2_wrong_post_of_pole, sum(en2_err ) en2_err, sum(en2_noise) en2_noise from ");
sql.Append("(select i2.dates,i2.times,i2.model_cd,i2.line_cd, 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 from t_ncvc_pdc_en2 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.en2_id) idca1 from t_ncvc_pdc_en2 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.en2_id) idca3 from t_ncvc_pdc_en2 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.en2_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(),
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);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
MoldCategoryVo inVo = (MoldCategoryVo)arg;
StringBuilder sqlQuery = new StringBuilder();
//create SQL
sqlQuery.Append("Select ct.mold_category_id, ct.mold_category_cd, ct.mold_category_name,ct.display_order ");
sqlQuery.Append(" from m_mold_category ct ");
sqlQuery.Append(" where factory_cd = :factorycode ");
if (inVo.MoldCategoryCode != null)
{
sqlQuery.Append(" and ct.mold_category_cd like :moldcategorycd ");
}
if (inVo.MoldCategoryName != null)
{
sqlQuery.Append(" and ct.mold_category_name like :moldcategoryname ");
}
sqlQuery.Append(" order by ct.display_order");
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sqlParameter.AddParameterString("factorycode", trxContext.UserData.FactoryCode);
if (inVo.MoldCategoryCode != null)
{
sqlParameter.AddParameterString("moldcategorycd", inVo.MoldCategoryCode + "%");
}
if (inVo.MoldCategoryName != null)
{
sqlParameter.AddParameterString("moldcategoryname", inVo.MoldCategoryName + "%");
}
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
ValueObjectList <MoldCategoryVo> outVo = null;
while (dataReader.Read())
{
MoldCategoryVo currOutVo = new MoldCategoryVo
{
MoldCategoryId = Convert.ToInt32(dataReader["mold_category_id"]),
MoldCategoryCode = dataReader["mold_category_cd"].ToString(),
MoldCategoryName = dataReader["mold_category_name"].ToString(),
DisplayOrder = Convert.ToInt32(dataReader["display_order"]),
};
if (outVo == null)
{
outVo = new ValueObjectList <MoldCategoryVo>();
}
outVo.add(currOutVo);
}
dataReader.Close();
return(outVo);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
InspectionItemVo inVo = (InspectionItemVo)arg;
StringBuilder sqlQuery = new StringBuilder();
//create SQL
sqlQuery.Append(" Select * from m_inspection_item ");
sqlQuery.Append(" where factory_cd = :factcd");
if (inVo.InspectionItemCode != null)
{
sqlQuery.Append(" and UPPER(inspection_item_cd) like UPPER(:Insitemcd) ");
}
if (inVo.InspectionItemName != null)
{
sqlQuery.Append(" and UPPER(iinspection_item_name) like UPPER(:Insitemname) ");
}
if (inVo.ParentInspectionItemId > 0)
{
sqlQuery.Append(" and parent_inspection_item_id = :parentinspectionitemid ");
}
if (inVo.InspectionProcessId > 0)
{
sqlQuery.Append(" and inspection_process_id = :inspectionprocessid ");
}
sqlQuery.Append(" order by display_order");
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sqlParameter.AddParameterString("Insitemcd", inVo.InspectionItemCode + "%");
sqlParameter.AddParameterString("Insitemname", inVo.InspectionItemName + "%");
sqlParameter.AddParameterInteger("parentinspectionitemid", inVo.ParentInspectionItemId);
sqlParameter.AddParameterInteger("inspectionprocessid", inVo.InspectionProcessId);
sqlParameter.AddParameterString("factcd", UserData.GetUserData().FactoryCode);
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
ValueObjectList <InspectionItemVo> outVo = null;
while (dataReader.Read())
{
InspectionItemVo currOutVo = new InspectionItemVo();
currOutVo.InspectionProcessId = ConvertDBNull <int>(dataReader, "inspection_process_id");
currOutVo.InspectionItemName = ConvertDBNull <string>(dataReader, "inspection_item_name");
currOutVo.InspectionItemCode = ConvertDBNull <string>(dataReader, "inspection_item_cd");
currOutVo.InspectionItemMandatory = ConvertDBNull <int>(dataReader, "is_inspection_item_mandatory");
currOutVo.InspectionEmployeeMandatory = ConvertDBNull <int>(dataReader, "is_inspection_employee_mandatory");
currOutVo.InspectionMachineMandatory = ConvertDBNull <int>(dataReader, "is_inspection_machine_mandatory");
currOutVo.InspectionItemId = ConvertDBNull <int>(dataReader, "inspection_item_id");
currOutVo.ParentInspectionItemId = ConvertDBNull <int>(dataReader, "parent_inspection_item_id");
currOutVo.InspectionItemDataType = ConvertDBNull <int>(dataReader, "inspection_item_data_type");
currOutVo.InspectionResultItemDecimalDigits = ConvertDBNull <int>(dataReader, "inspection_item_result_input_decimal_digits");
currOutVo.DisplayOrder = ConvertDBNull <int>(dataReader, "display_order");
if (outVo == null)
{
outVo = new ValueObjectList <InspectionItemVo>();
}
outVo.add(currOutVo);
}
dataReader.Close();
return(outVo);
}
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 arg)
{
ProcessWorkMachineVo inVo = (ProcessWorkMachineVo)arg;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("Select pwm.process_work_id, pwm.machine_id,ls.machine_cd,ls.machine_name");
sqlQuery.Append(" from m_processwork_machine pwm ");
sqlQuery.Append(" inner join m_machine ls on ls.machine_id = pwm.machine_id ");
sqlQuery.Append(" where pwm.factory_cd = :faccd ");
if (inVo.MachineId > 0)
{
sqlQuery.Append(" and pwm.machine_id = :machineid ");
}
if (inVo.ProcessWorkId > 0)
{
sqlQuery.Append(" and pwm.process_work_id = :processworkid ");
}
if (inVo.MachineName != null)
{
sqlQuery.Append(" and machine_name like :machinename ");
}
if (inVo.MachineCode != null)
{
sqlQuery.Append(" and machine_cd like :machinecode ");
}
sqlQuery.Append(" order by machine_name ");
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
if (!string.IsNullOrEmpty(inVo.FactoryCode))
{
sqlParameter.AddParameterString("faccd", inVo.FactoryCode);
}
else
{
sqlParameter.AddParameterString("faccd", UserData.GetUserData().FactoryCode);
}
if (inVo.MachineId > 0)
{
sqlParameter.AddParameterInteger("machineid", inVo.MachineId);
}
if (inVo.ProcessWorkId > 0)
{
sqlParameter.AddParameterInteger("processworkid", inVo.ProcessWorkId);
}
if (inVo.MachineName != null)
{
sqlParameter.AddParameterString("machinename", inVo.MachineName + "%");
}
if (inVo.MachineCode != null)
{
sqlParameter.AddParameterString("machinecode", inVo.MachineCode + "%");
}
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
ProcessWorkMachineVo outVo = new ProcessWorkMachineVo();
while (dataReader.Read())
{
ProcessWorkMachineVo currOutVo = new ProcessWorkMachineVo
{
MachineId = Convert.ToInt32(dataReader["machine_id"]),
MachineCode = dataReader["machine_cd"].ToString(),
ProcessWorkId = Convert.ToInt32(dataReader["process_work_id"]),
MachineName = dataReader["machine_name"].ToString(),
};
outVo.ProcessWorkMachineListVo.Add(currOutVo);
}
dataReader.Close();
return(outVo);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
CustomerLineVo inVo = (CustomerLineVo)arg;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("Select");
sqlQuery.Append(" cl.customer_line_id,");
sqlQuery.Append(" cl.customer_id,");
sqlQuery.Append(" cl.line_id,");
sqlQuery.Append(" c.customer_name,");
sqlQuery.Append(" l.line_name");
sqlQuery.Append(" from m_customer_line cl");
sqlQuery.Append(" inner join m_customer c on cl.customer_id = c.customer_id");
sqlQuery.Append(" inner join m_line l on cl.line_id = l.line_id");
sqlQuery.Append(" where cl.factory_cd = :faccd ");
if (inVo.LineId > 0)
{
sqlQuery.Append(" and cl.line_id = :lineid");
}
if (inVo.CustomerId > 0)
{
sqlQuery.Append(" and cl.customer_id = :customerid");
}
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sqlParameter.AddParameterString("faccd", UserData.GetUserData().FactoryCode);
if (inVo.LineId > 0)
{
sqlParameter.AddParameterInteger("lineid", inVo.LineId);
}
if (inVo.CustomerId > 0)
{
sqlParameter.AddParameterInteger("customerid", inVo.CustomerId);
}
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
CustomerLineVo outVo = new CustomerLineVo();
while (dataReader.Read())
{
CustomerLineVo currOutVo = new CustomerLineVo
{
CustomerLineId = Convert.ToInt32(dataReader["customer_line_id"]),
LineId = Convert.ToInt32(dataReader["line_id"]),
CustomerId = Convert.ToInt32(dataReader["customer_id"]),
CustomerName = dataReader["customer_name"].ToString(),
LineName = dataReader["line_name"].ToString()
};
outVo.customerLineListVo.Add(currOutVo);
}
dataReader.Close();
return(outVo);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ProductionControllerGA1Vo voList = new ProductionControllerGA1Vo();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
string sqlChung = " times, model,line, process,sum(inspectdata) inspectdata from (select a.serno,a.model,a.line, a.process,sum(inspectdata) inspectdata,a.inspectdate from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where model = :model and line = :line and a.inspectdate = b.inspectdate and a.inspectdate >= :datefrom and a.inspectdate <= :dateto group by a.serno,a.model,a.line, a.process,a.inspectdate order by a.inspectdate) tbl where process != 'MC_NOICHK' and inspectdate >= ";
sqlParameter.AddParameter("model", inVo.ModelCode);
sqlParameter.AddParameter("line", inVo.LineCode);
sql.Append("select '01:00:00'" + sqlChung + " '" + inVo.Date + " 00:00:01' and inspectdate <= '" + inVo.Date + " 01:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '02:00:00'" + sqlChung + " '" + inVo.Date + " 01:00:01' and inspectdate <= '" + inVo.Date + " 02:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '03:00:00'" + sqlChung + " '" + inVo.Date + " 02:00:01' and inspectdate <= '" + inVo.Date + " 03:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '04:00:00'" + sqlChung + " '" + inVo.Date + " 03:00:01' and inspectdate <= '" + inVo.Date + " 04:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '05:00:00'" + sqlChung + " '" + inVo.Date + " 04:00:01' and inspectdate <= '" + inVo.Date + " 05:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '06:00:00'" + sqlChung + " '" + inVo.Date + " 05:00:01' and inspectdate <= '" + inVo.Date + " 06:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '07:00:00'" + sqlChung + " '" + inVo.Date + " 06:00:01' and inspectdate <= '" + inVo.Date + " 07:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '08:00:00'" + sqlChung + " '" + inVo.Date + " 07:00:01' and inspectdate <= '" + inVo.Date + " 08:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '09:00:00'" + sqlChung + " '" + inVo.Date + " 08:00:01' and inspectdate <= '" + inVo.Date + " 09:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '10:00:00'" + sqlChung + " '" + inVo.Date + " 09:00:01' and inspectdate <= '" + inVo.Date + " 10:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '11:00:00'" + sqlChung + " '" + inVo.Date + " 10:00:01' and inspectdate <= '" + inVo.Date + " 11:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '12:00:00'" + sqlChung + " '" + inVo.Date + " 11:00:01' and inspectdate <= '" + inVo.Date + " 12:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '13:00:00'" + sqlChung + " '" + inVo.Date + " 12:00:01' and inspectdate <= '" + inVo.Date + " 13:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '14:00:00'" + sqlChung + " '" + inVo.Date + " 13:00:01' and inspectdate <= '" + inVo.Date + " 14:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '15:00:00'" + sqlChung + " '" + inVo.Date + " 14:00:01' and inspectdate <= '" + inVo.Date + " 15:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '16:00:00'" + sqlChung + " '" + inVo.Date + " 15:00:01' and inspectdate <= '" + inVo.Date + " 16:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '17:00:00'" + sqlChung + " '" + inVo.Date + " 16:00:01' and inspectdate <= '" + inVo.Date + " 17:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '18:00:00'" + sqlChung + " '" + inVo.Date + " 17:00:01' and inspectdate <= '" + inVo.Date + " 18:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '19:00:00'" + sqlChung + " '" + inVo.Date + " 18:00:01' and inspectdate <= '" + inVo.Date + " 19:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '20:00:00'" + sqlChung + " '" + inVo.Date + " 19:00:01' and inspectdate <= '" + inVo.Date + " 20:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '21:00:00'" + sqlChung + " '" + inVo.Date + " 20:00:01' and inspectdate <= '" + inVo.Date + " 21:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '22:00:00'" + sqlChung + " '" + inVo.Date + " 21:00:01' and inspectdate <= '" + inVo.Date + " 22:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '23:00:00'" + sqlChung + " '" + inVo.Date + " 22:00:01' and inspectdate <= '" + inVo.Date + " 23:00:00' group by model,line, process");
sql.Append(" union ");
sql.Append("select '23:59:59'" + sqlChung + " '" + inVo.Date + " 23:00:01' and inspectdate <= '" + inVo.Date + " 23:59:59' group by model,line, process");
sql.Append(" order by times, process ");
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter);
//execute SQL
ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo
{
dt = ds.Tables[0],
};
return(outVo1);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
InspectionSpecificationVo inVo = (InspectionSpecificationVo)arg;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append(" select");
sqlQuery.Append(" ispec.inspection_specification_id,");
sqlQuery.Append(" ispec.inspection_specification_cd,");
sqlQuery.Append(" ispec.inspection_specification_text,");
sqlQuery.Append(" ispec.value_from,");
sqlQuery.Append(" ispec.value_to,");
sqlQuery.Append(" ispec.unit,");
sqlQuery.Append(" ispec.operator_from,");
sqlQuery.Append(" ispec.operator_to,");
sqlQuery.Append(" iitm.inspection_item_id,");
sqlQuery.Append(" iitm.inspection_item_name, ");
sqlQuery.Append(" ispec.specification_result_judge_type, ");
sqlQuery.Append(" case ispec.specification_result_judge_type ");
sqlQuery.Append(" when 1 then 'AUTO' when 2 then 'MANUAL' ");
sqlQuery.Append(" end as specificationresultjudgetype");
sqlQuery.Append(" from m_inspection_specification ispec");
sqlQuery.Append(" inner join m_inspection_item iitm");
sqlQuery.Append(" on iitm.inspection_item_id = ispec.inspection_item_id");
sqlQuery.Append(" where ispec.factory_cd = :faccd ");
if (inVo.InspectionSpecificationCode != null)
{
sqlQuery.Append(" and ispec.inspection_specification_cd like :inspectionspecificationcd ");
}
if (inVo.InspectionSpecificationText != null)
{
sqlQuery.Append(" and ispec.inspection_specification_text like :inspectionspecificationtext ");
}
if (inVo.InspectionItemId > 0)
{
sqlQuery.Append(" and ispec.inspection_item_id = :inspectionitemid ");
}
sqlQuery.Append(" order by ispec.inspection_specification_cd ");
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sqlParameter.AddParameterString("faccd", UserData.GetUserData().FactoryCode);
sqlParameter.AddParameterString("inspectionspecificationcd", inVo.InspectionSpecificationCode + "%");
sqlParameter.AddParameterString("inspectionspecificationtext", inVo.InspectionSpecificationText + "%");
sqlParameter.AddParameterInteger("inspectionitemid", inVo.InspectionItemId);
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
ValueObjectList <InspectionSpecificationVo> outVo = null;
while (dataReader.Read())
{
InspectionSpecificationVo currOutVo = new InspectionSpecificationVo();
currOutVo.InspectionSpecificationId = ConvertDBNull <int>(dataReader, "inspection_specification_id");
currOutVo.InspectionSpecificationCode = ConvertDBNull <string>(dataReader, "inspection_specification_cd");
currOutVo.InspectionSpecificationText = ConvertDBNull <string>(dataReader, "inspection_specification_text");
currOutVo.ValueFrom = ConvertDBNull <string>(dataReader, "value_from");
currOutVo.ValueTo = ConvertDBNull <string>(dataReader, "value_to");
currOutVo.Unit = ConvertDBNull <string>(dataReader, "unit");
currOutVo.OperatorFrom = ConvertDBNull <string>(dataReader, "operator_from");
currOutVo.OperatorTo = ConvertDBNull <string>(dataReader, "operator_to");
currOutVo.InspectionItemId = ConvertDBNull <int>(dataReader, "inspection_item_id");
currOutVo.InspectionItemName = ConvertDBNull <string>(dataReader, "inspection_item_name");
currOutVo.SpecificationResultJudgeType = ConvertDBNull <int>(dataReader, "specification_result_judge_type");
currOutVo.SpecificationResultJudgeTypeMode = ConvertDBNull <string>(dataReader, "specificationresultjudgetype");
if (outVo == null)
{
outVo = new ValueObjectList <InspectionSpecificationVo>();
}
outVo.add(currOutVo);
}
dataReader.Close();
return(outVo);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
AssetInfoVo inVo = (AssetInfoVo)vo;
ValueObjectList <AssetInfoVo> voList = new ValueObjectList <AssetInfoVo>();
StringBuilder sql = new StringBuilder();
//CREATE SQL ADAPTER AND PARAMETER LIST
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select * from m_asset where 1=1 ");
if (inVo.asset_id > 0)
{
sql.Append("and asset_id='").Append(inVo.asset_id).Append("' ");
}
if (!string.IsNullOrEmpty(inVo.asset_cd))
{
sql.Append("and asset_cd='").Append(inVo.asset_cd).Append("' ");
}
if (inVo.asset_no > 0)
{
sql.Append("and asset_no='").Append(inVo.asset_no).Append("' ");
}
if (!string.IsNullOrEmpty(inVo.asset_name))
{
sql.Append("and asset_name='").Append(inVo.asset_name).Append("' ");
}
if (!string.IsNullOrEmpty(inVo.asset_type))
{
sql.Append("and asset_type='").Append(inVo.asset_type).Append("' ");
}
if (!string.IsNullOrEmpty(inVo.label_status))
{
sql.Append("and label_status='").Append(inVo.label_status).Append("' ");
}
sql.Append("order by asset_id");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
sql.Clear();
//EXECUTE READER FROM COMMAND
IDataReader datareader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (datareader.Read())
{
AssetInfoVo outVo = new AssetInfoVo
{
asset_id = (int)datareader["asset_id"],
asset_cd = datareader["asset_cd"].ToString(),
asset_no = (int)datareader["asset_no"],
asset_name = datareader["asset_name"].ToString(),
asset_model = datareader["asset_model"].ToString(),
asset_serial = datareader["asset_serial"].ToString(),
acquistion_cost = (double)datareader["acquistion_cost"],
acquistion_date = (DateTime)datareader["acquistion_date"],
asset_life = (double)datareader["asset_life"],
asset_type = datareader["asset_type"].ToString(),
asset_invoice = datareader["asset_invoice"].ToString(),
asset_supplier = datareader["asset_supplier"].ToString(),
factory_cd = datareader["factory_cd"].ToString(),
label_status = datareader["label_status"].ToString(),
asset_po = datareader["asset_po"].ToString(),
registration_user_cd = datareader["registration_user_cd"].ToString(),
registration_date_time = (DateTime)datareader["registration_date_time"],
};
voList.add(outVo);
}
datareader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProcessWorkMoldVo inVo = (ProcessWorkMoldVo)vo;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("Select");
sqlQuery.Append(" pwm.process_work_mold_id,");
sqlQuery.Append(" pwm.process_work_id,");
sqlQuery.Append(" pwm.mold_id,");
sqlQuery.Append(" m.mold_name,");
sqlQuery.Append(" pw.process_work_name");
sqlQuery.Append(" from m_gtrs_process_work_mold pwm");
sqlQuery.Append(" inner join m_process_work pw on pwm.process_work_id = pw.process_work_id");
sqlQuery.Append(" inner join m_mold m on pwm.mold_id = m.mold_id");
sqlQuery.Append(" where 1 = 1 ");
if (inVo.MoldId > 0)
{
sqlQuery.Append(" and pwm.mold_id = :moldid");
}
if (inVo.ProcessWorkId > 0)
{
sqlQuery.Append(" and pwm.process_work_id = :processworkid");
}
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
if (inVo.MoldId > 0)
{
sqlParameter.AddParameterInteger("moldid", inVo.MoldId);
}
if (inVo.ProcessWorkId > 0)
{
sqlParameter.AddParameterInteger("processworkid", inVo.ProcessWorkId);
}
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
ProcessWorkMoldVo outVo = new ProcessWorkMoldVo();
while (dataReader.Read())
{
ProcessWorkMoldVo currVo = new ProcessWorkMoldVo
{
ProcessWorkId = Convert.ToInt32(DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_work_id"])),
MoldId = Convert.ToInt32(dataReader["mold_id"]),
MoldName = dataReader["mold_name"].ToString(),
ProcessWorkName = dataReader["process_work_name"].ToString(),
ProcessWorkMoldId = Convert.ToInt32(dataReader["process_work_mold_id"]),
};
outVo.ProcessWorkMoldListVo.Add(currVo);
}
dataReader.Close();
return(outVo);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
ShelfVo inVo = (ShelfVo)arg;
StringBuilder sqlQuery = new StringBuilder();
//create SQL
sqlQuery.Append("Select ct.shelf_id, ct.shelf_cd, ct.shelf_name, md.area_id, md.area_name ");
sqlQuery.Append(" from m_shelf ct ");
sqlQuery.Append(" inner join m_area md on md.area_id = ct.area_id ");
sqlQuery.Append(" where 1 = 1 ");
if (inVo.ShelfCode != null)
{
sqlQuery.Append(" and ct.shelf_cd like :shelfcd ");
}
if (inVo.ShelfName != null)
{
sqlQuery.Append(" and ct.shelf_name like :shelfname ");
}
if (inVo.AreaId != 0)
{
sqlQuery.Append(" and md.area_id = :areaid ");
}
sqlQuery.Append(" order by ct.shelf_cd");
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
if (inVo.ShelfCode != null)
{
sqlParameter.AddParameterString("shelfcd", inVo.ShelfCode + "%");
}
if (inVo.ShelfName != null)
{
sqlParameter.AddParameterString("shelfname", inVo.ShelfName + "%");
}
if (inVo.AreaId != 0)
{
sqlParameter.AddParameterInteger("areaid", inVo.AreaId);
}
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
ShelfVo outVo = new ShelfVo();
while (dataReader.Read())
{
ShelfVo currOutVo = new ShelfVo
{
ShelfId = Convert.ToInt32(dataReader["shelf_id"]),
ShelfCode = dataReader["shelf_cd"].ToString(),
ShelfName = dataReader["shelf_name"].ToString(),
AreaId = Convert.ToInt32(dataReader["area_id"]),
AreaName = dataReader["area_name"].ToString()
};
outVo.ShelfListVo.Add(currOutVo);
}
dataReader.Close();
return(outVo);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
JigRepairInformationVo inVo = (JigRepairInformationVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <JigRepairInformationVo> voList = new ValueObjectList <JigRepairInformationVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"SELECT a.jig_repair_id, a.jig_repair_cd, c.model_name, c.model_cd, b.line_name, d.process_work_name, g.process_name, e.jig_cause_name, f.jig_response_name,
a.time_from, a.time_to, a.jig_current_status, a.jig_after_repair_status, a.repair_result, a.place,b.line_cd,a.registration_user_cd,a.registration_date_time,a.factory_cd
from t_jig_repair_info a
left join m_line b on b.line_id = a.line_id
left join m_ncvp_model c on c.model_id = a.model_id
left join m_process_work d on d.process_work_id =a.process_work_id
left join m_process g on g.process_id =d.process_id
left join m_ncvp_jig_cause e on e.jig_cause_id =a.jig_cause_id
left join m_ncvp_jig_response f on f.jig_response_id = a.jig_response_id
WHERE ");
sql.Append(@"time_from >:starttime and time_from <:endtime"); //
sqlParameter.AddParameterDateTime("starttime", inVo.TimeFrom);
sqlParameter.AddParameterDateTime("endtime", inVo.TimeTo.AddDays(1));
if (!String.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(" and model_cd =:model_cd ");
sqlParameter.AddParameterString("model_cd", inVo.ModelCode);
}
if (!String.IsNullOrEmpty(inVo.ProcessName))
{
sql.Append(" and process_name =:process_name ");
sqlParameter.AddParameterString("process_name", inVo.ProcessName);
}
if (!String.IsNullOrEmpty(inVo.LineCode))
{
sql.Append(" and line_cd =:line_cd ");
sqlParameter.AddParameterString("line_cd", inVo.LineCode);
}
if (!String.IsNullOrEmpty(inVo.JigRepairCode))
{
sql.Append(" and jig_repair_cd =:jig_repair_cd ");
sqlParameter.AddParameterString("jig_repair_cd", inVo.JigRepairCode);
}
if (!String.IsNullOrEmpty(inVo.JigCauseName))
{
sql.Append(" and jig_cause_name = :jig_cause_name ");
sqlParameter.AddParameterString("jig_cause_name", inVo.JigCauseName);
}
if (!String.IsNullOrEmpty(inVo.JigResponseName))
{
sql.Append(" and jig_response_name= :jig_response_name ");
sqlParameter.AddParameterString("jig_response_name", inVo.JigResponseName);
}
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
JigRepairInformationVo outVo = new JigRepairInformationVo
{
// , h., i., k., o.prodution_work_content_name
JigRepairId = int.Parse(dataReader["jig_repair_id"].ToString()),
JigRepairCode = dataReader["jig_repair_cd"].ToString(),
ProcessName = dataReader["process_name"].ToString(),
ProcessWorkName = dataReader["process_work_name"].ToString(),
LineCode = dataReader["line_cd"].ToString(),
JigCauseName = dataReader["jig_cause_name"].ToString(),
JigResponseName = dataReader["jig_response_name"].ToString(),
TimeTo = DateTime.Parse(dataReader["time_to"].ToString()),
TimeFrom = DateTime.Parse(dataReader["time_from"].ToString()),
ModelName = dataReader["model_name"].ToString(),
ModelCode = dataReader["model_cd"].ToString(),
JigCurrentStatus = dataReader["jig_current_status"].ToString(),
JigAfterRepairStatus = (dataReader["jig_after_repair_status"].ToString()),
JigRepairResult = dataReader["repair_result"].ToString(),
JigPlace = dataReader["place"].ToString(),
RegistrationUserCode = dataReader["registration_user_cd"].ToString()
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
LineItemCycleTimeVo inVo = (LineItemCycleTimeVo)arg;
StringBuilder sqlQuery = new StringBuilder();
//create SQL
sqlQuery.Append(" select ");
sqlQuery.Append(" sap_matnr_item_cd, ");
sqlQuery.Append(" cycle_time, ");
sqlQuery.Append(" line_sap_item_id, ");
sqlQuery.Append(" line_id ");
sqlQuery.Append(" from m_line_sap_item ");
sqlQuery.Append(" where ");
sqlQuery.Append(" factory_cd = :factorycode ");
sqlQuery.Append(" and line_id = :lineid ");
if (inVo.SapItemCode != null)
{
sqlQuery.Append(" and sap_matnr_item_cd = :sapMatnrItemCd ");
}
sqlQuery.Append(" ORDER BY sap_matnr_item_cd, cycle_time");
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sqlParameter.AddParameterString("factorycode", trxContext.UserData.FactoryCode);
sqlParameter.AddParameterInteger("lineid", inVo.LineId);
sqlParameter.AddParameterString("sapMatnrItemCd", inVo.SapItemCode);
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
ValueObjectList <LineItemCycleTimeVo> lineItemCycleTimeVo = null;
while (dataReader.Read())
{
LineItemCycleTimeVo currVo = new LineItemCycleTimeVo();
currVo.LineId = ConvertDBNull <int>(dataReader, "line_id");
currVo.SapItemCode = ConvertDBNull <string>(dataReader, "sap_matnr_item_cd");
if (dataReader["cycle_time"] != null)
{
currVo.StdCycleTimeNull = ConvertDBNull <decimal>(dataReader, "cycle_time");
}
currVo.LineItemCycleTimeId = ConvertDBNull <int>(dataReader, "line_sap_item_id");
if (lineItemCycleTimeVo == null)
{
lineItemCycleTimeVo = new ValueObjectList <LineItemCycleTimeVo>();
}
lineItemCycleTimeVo.add(currVo);
}
dataReader.Close();
return(lineItemCycleTimeVo);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
LineBuildingVo inVo = (LineBuildingVo)arg;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("Select");
sqlQuery.Append(" lb.line_building_id,");
sqlQuery.Append(" lb.building_id,");
sqlQuery.Append(" lb.line_id,");
sqlQuery.Append(" b.building_cd,");
sqlQuery.Append(" b.building_name,");
sqlQuery.Append(" l.line_cd,");
sqlQuery.Append(" l.line_name");
sqlQuery.Append(" from m_line_building lb");
sqlQuery.Append(" inner join m_line l on lb.line_id = l.line_id");
sqlQuery.Append(" inner join m_building b on lb.building_id = b.building_id");
sqlQuery.Append(" where lb.factory_cd = :faccd ");
if (inVo.BuildingId > 0)
{
sqlQuery.Append(" and lb.building_id = :buildingid");
}
if (inVo.LineId > 0)
{
sqlQuery.Append(" and lb.line_id = :lid");
}
if (inVo.LineCode != null)
{
sqlQuery.Append(" and l.line_cd = :lcd");
}
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sqlParameter.AddParameterString("faccd", UserData.GetUserData().FactoryCode);
sqlParameter.AddParameterInteger("buildingid", inVo.BuildingId);
sqlParameter.AddParameterInteger("lid", inVo.LineId);
sqlParameter.AddParameterString("lcd", inVo.LineCode);
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
ValueObjectList <LineBuildingVo> outVo = null;
while (dataReader.Read())
{
LineBuildingVo currOutVo = new LineBuildingVo
{
LineBuildingId = Convert.ToInt32(dataReader["line_building_id"]),
BuildingId = Convert.ToInt32(dataReader["building_id"]),
LineId = Convert.ToInt32(dataReader["line_id"]),
LineCode = dataReader["line_cd"].ToString(),
LineName = dataReader["line_name"].ToString(),
BuildingCode = dataReader["building_cd"].ToString(),
BuildingName = dataReader["building_name"].ToString()
};
if (outVo == null)
{
outVo = new ValueObjectList <LineBuildingVo>();
}
outVo.add(currOutVo);
}
dataReader.Close();
return(outVo);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
InspectionTestInstructionVo inVo = (InspectionTestInstructionVo)arg;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("select");
sqlQuery.Append(" itinsth.inspection_test_instruction_id,");
sqlQuery.Append(" itinsth.inspection_test_instruction_cd,");
sqlQuery.Append(" itinsth.inspection_test_instruction_text,");
sqlQuery.Append(" itinstd.inspection_test_instruction_detail_id,");
sqlQuery.Append(" itinstd.inspection_test_instruction_detail_cd,");
sqlQuery.Append(" itinstd.inspection_test_instruction_detail_text,");
sqlQuery.Append(" itinstd.inspection_test_instruction_detail_result_count,");
sqlQuery.Append(" itinstd.inspection_test_instruction_detail_machine_text");
sqlQuery.Append(" from m_inspection_test_instruction itinsth");
sqlQuery.Append(" inner join m_inspection_test_instruction_detail itinstd");
sqlQuery.Append(" on itinstd.inspection_test_instruction_id = itinsth.inspection_test_instruction_id");
sqlQuery.Append(" where itinsth.factory_cd = :faccd ");
if (inVo.InspectionTestInstructionDetailCode != null)
{
sqlQuery.Append(" and itinstd.inspection_test_instruction_detail_cd like :inspectiontestinstructiondetailcd ");
}
if (inVo.InspectionTestInstructionDetailText != null)
{
sqlQuery.Append(" and itinstd.inspection_test_instruction_detail_text like :inspectiontestinstructiondetailtext ");
}
if (inVo.InspectionTestInstructionId > 0)
{
sqlQuery.Append(" and itinstd.inspection_test_instruction_id = :inspectiontestinstructionid ");
}
if (inVo.InspectionTestInstructionDetailId > 0)
{
sqlQuery.Append(" and itinstd.inspection_test_instruction_detail_id = :inspectiontestinstructiondetailid ");
}
sqlQuery.Append(" order by itinstd.inspection_test_instruction_detail_cd");
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sqlParameter.AddParameterString("faccd", UserData.GetUserData().FactoryCode);
if (inVo.InspectionTestInstructionDetailCode != null)
{
sqlParameter.AddParameterString("inspectiontestinstructiondetailcd", inVo.InspectionTestInstructionDetailCode + "%");
}
if (inVo.InspectionTestInstructionDetailText != null)
{
sqlParameter.AddParameterString("inspectiontestinstructiondetailtext", inVo.InspectionTestInstructionDetailText + "%");
}
if (inVo.InspectionTestInstructionId > 0)
{
sqlParameter.AddParameterInteger("inspectiontestinstructionid", inVo.InspectionTestInstructionId);
}
if (inVo.InspectionTestInstructionDetailId > 0)
{
sqlParameter.AddParameterInteger("inspectiontestinstructiondetailid", inVo.InspectionTestInstructionDetailId);
}
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
ValueObjectList <InspectionTestInstructionVo> outVo = null;
while (dataReader.Read())
{
InspectionTestInstructionVo currOutVo = new InspectionTestInstructionVo();
currOutVo.InspectionTestInstructionId = ConvertDBNull <int>(dataReader, "inspection_test_instruction_id");
currOutVo.InspectionTestInstructionCode = ConvertDBNull <string>(dataReader, "inspection_test_instruction_cd");
currOutVo.InspectionTestInstructionText = ConvertDBNull <string>(dataReader, "inspection_test_instruction_text");
currOutVo.InspectionTestInstructionDetailId = ConvertDBNull <int>(dataReader, "inspection_test_instruction_detail_id");
currOutVo.InspectionTestInstructionDetailCode = ConvertDBNull <string>(dataReader, "inspection_test_instruction_detail_cd");
currOutVo.InspectionTestInstructionDetailText = ConvertDBNull <string>(dataReader, "inspection_test_instruction_detail_text");
currOutVo.InspectionTestInstructionDetailResultCount = ConvertDBNull <int>(dataReader, "inspection_test_instruction_detail_result_count");
currOutVo.InspectionTestInstructionDetailMachine = ConvertDBNull <string>(dataReader, "inspection_test_instruction_detail_machine_text");
if (outVo == null)
{
outVo = new ValueObjectList <InspectionTestInstructionVo>();
}
outVo.add(currOutVo);
}
dataReader.Close();
return(outVo);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
WareHouseVo inVo = (WareHouseVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <WareHouseVo> voList = new ValueObjectList <WareHouseVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select k.unit_name,c.location_cd as before, j.location_cd as after, h.detail_postion_cd, d.user_location_name ,g.warehouse_main_history_id,c.location_cd,e.asset_cd, e.asset_no, e.asset_name, e.asset_model, e.asset_serial, e.asset_supplier,e.asset_po, e.asset_invoice,e.label_status, g.qty, a.account_code_cd, b.account_location_cd, f.rank_cd, b.account_location_name, g.comment_data, e.asset_life, e.acquistion_date, e.acquistion_cost, e.asset_type, g.depreciation_start, g.depreciation_end, g.current_depreciation,g.monthly_depreciation, g.accum_depreciation_now, g.net_value, g.registration_date_time, g.registration_user_cd from t_warehouse_main_history g
left join m_account_code a on a.account_code_id = g.account_code_id
left join m_account_location b on b.account_location_id = g.account_location_id
left join m_location c on c.location_id = g.before_location_id
left join m_location j on j.location_id = g.after_location_id
left join m_user_location d on d.user_location_id = g.user_location_id
left join m_asset e on e.asset_id = g.asset_id
left join m_rank f on f.rank_id = g.rank_id
left join m_detail_postion h on h.detail_postion_id = g.detail_position_id
left join m_unit k on k.unit_id = g.unit_id
where 1=1 ");
if (!String.IsNullOrEmpty(inVo.asset_cd))
{
sql.Append(@" and e.asset_cd =:asset_cd");
sqlParameter.AddParameterString("asset_cd", inVo.asset_cd);
}
if (!String.IsNullOrEmpty(inVo.rank_cd))
{
sql.Append(" and f.rank_cd =:rank_cd");
sqlParameter.AddParameterString("rank_cd", inVo.rank_cd);
}
if (!String.IsNullOrEmpty(inVo.asset_model))
{
sql.Append(" and e.asset_model =:asset_model");
sqlParameter.AddParameterString("asset_model", inVo.asset_model);
}
if (!String.IsNullOrEmpty(inVo.asset_name))
{
sql.Append(" and e.asset_name =:asset_name");
sqlParameter.AddParameterString("asset_name", inVo.asset_name);
}
if (!String.IsNullOrEmpty(inVo.asset_type))
{
sql.Append(" and e.asset_type =:asset_type");
sqlParameter.AddParameterString("asset_type", inVo.asset_type);
}
if (!String.IsNullOrEmpty(inVo.asset_invoice))
{
sql.Append(" and e.asset_invoice =:asset_invoice");
sqlParameter.AddParameterString("asset_invoice", inVo.asset_invoice);
}
if (!String.IsNullOrEmpty(inVo.location_cd))
{
sql.Append(" and j.location_cd =:location_cd");
sqlParameter.AddParameterString("location_cd", inVo.location_cd);
}
//if (!String.IsNullOrEmpty(inVo.DetailPositionCd))
//{
// sql.Append(" and h.detail_postion_cd =:detail_postion_cd");
// sqlParameter.AddParameterString("detail_postion_cd", inVo.DetailPositionCd);
//}
if (!String.IsNullOrEmpty(inVo.label_status))//label status
{
sql.Append(" and e.label_status =:label_status");
sqlParameter.AddParameterString("label_status", inVo.label_status);
}
//if (!String.IsNullOrEmpty(inVo.AssetPO))//label status
//{
// sql.Append(" and e.asset_po =:asset_po");
// sqlParameter.AddParameterString("asset_po", inVo.AssetPO);
//}
if (!String.IsNullOrEmpty(inVo.net_value))//search theo net value
{
if (inVo.net_value == "0$")
{
sql.Append(" and g.net_value = 0");
}
else if (inVo.net_value == "1$")
{
sql.Append(" and g.net_value > 0 and g.net_value <2 ");
}
}
sql.Append(" order by g.registration_date_time desc");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
WareHouseVo outVo = new WareHouseVo
{
// , h., i., k., o.prodution_work_content_name
WareHouseMainId = int.Parse(dataReader["warehouse_main_history_id"].ToString()),
AfterLocationCd = dataReader["after"].ToString(),
BeforeLocationCd = dataReader["before"].ToString(),
DetailPositionCd = dataReader["detail_postion_cd"].ToString(),
AssetCode = dataReader["asset_cd"].ToString(),
AssetNo = int.Parse(dataReader["asset_no"].ToString()),
AssetName = dataReader["asset_name"].ToString(),
AssetModel = dataReader["asset_model"].ToString(),
AssetSerial = dataReader["asset_serial"].ToString(),
AssetSupplier = dataReader["asset_supplier"].ToString(),
QTY = int.Parse(dataReader["qty"].ToString()),
UnitName = dataReader["unit_name"].ToString(),
UserLocationName = dataReader["user_location_name"].ToString(),
AccountCodeCode = dataReader["account_code_cd"].ToString(),
AccountLocationCode = dataReader["account_location_cd"].ToString(),
RankCode = dataReader["rank_cd"].ToString(),
AccountLocationName = dataReader["account_location_name"].ToString(),
CommnetsData = dataReader["comment_data"].ToString(),
AssetLife = int.Parse(dataReader["asset_life"].ToString()),
AcquisitionDate = DateTime.Parse(dataReader["acquistion_date"].ToString()),
AcquisitionCost = double.Parse(dataReader["acquistion_cost"].ToString()),
StartDepreciation = DateTime.Parse(dataReader["depreciation_start"].ToString()),
EndDepreciation = DateTime.Parse(dataReader["depreciation_end"].ToString()),
CurrentDepreciation = double.Parse(dataReader["current_depreciation"].ToString()),
MonthlyDepreciation = double.Parse(dataReader["monthly_depreciation"].ToString()),
AccumDepreciation = double.Parse(dataReader["accum_depreciation_now"].ToString()),
NetValue = double.Parse(dataReader["net_value"].ToString()),
AssetType = dataReader["asset_type"].ToString(),
AssetInvoice = (dataReader["asset_invoice"].ToString()),
LabelStatus = (dataReader["label_status"].ToString()),
AssetPO = dataReader["asset_po"].ToString(),
RegistrationDateTime = DateTime.Parse(dataReader["registration_date_time"].ToString()),
RegistrationUserCode = (dataReader["registration_user_cd"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
ValueObjectList <InspectionProcessVo> inVo = (ValueObjectList <InspectionProcessVo>)arg;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("Insert into m_inspection_process");
sqlQuery.Append(" ( ");
sqlQuery.Append(" inspection_process_cd,");
sqlQuery.Append(" inspection_process_name,");
sqlQuery.Append(" inspection_format_id,");
sqlQuery.Append(" display_order,");
sqlQuery.Append(" registration_user_cd,");
sqlQuery.Append(" registration_date_time,");
sqlQuery.Append(" factory_cd");
sqlQuery.Append(" ) ");
sqlQuery.Append("VALUES ");
//sqlQuery.Append(" ( Select");
StringBuilder sqlValues = new StringBuilder();
UserData userdata = trxContext.UserData;
foreach (InspectionProcessVo getProcessVo in inVo.GetList())
{
if (sqlValues.Length > 0)
{
sqlValues.Append(" , ");
}
sqlValues.Append(" (");
sqlValues.Append("'" + getProcessVo.InspectionProcessCode + "' ,");
sqlValues.Append("'" + getProcessVo.InspectionProcessName + "' ,");
sqlValues.Append(getProcessVo.InspectionFormatId + ",");
sqlValues.Append(getProcessVo.DisplayOrder + ",");
sqlValues.Append("'" + userdata.UserCode + "' ,");
sqlValues.Append("'" + trxContext.ProcessingDBDateTime + "' ,");
sqlValues.Append("'" + userdata.FactoryCode + "'");
sqlValues.Append(" ) ");
}
sqlQuery.Append(sqlValues.ToString());
//sqlQuery.Append(" ip.inspection_process_cd,");
//sqlQuery.Append(" ip.inspection_process_name,");
//sqlQuery.Append(" ip.inspection_format_id,");
//sqlQuery.Append(" ip.display_order,");
//sqlQuery.Append(" :registrationusercode,");
//sqlQuery.Append(" :regdatetime,");
//sqlQuery.Append(" :factorycode");
//sqlQuery.Append(" from ");
//sqlQuery.Append(" m_inspection_process ip");
//sqlQuery.Append(" where ip.inspection_format_id = :inspectionformatid");
//sqlQuery.Append(" ) ");
//sqlQuery.Append(" RETURNING inspection_process_id;");
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
//sqlParameter.AddParameterString("inspectionprocesscd", inVo.InspectionProcessCode);
//sqlParameter.AddParameterString("inspectionprocessname", inVo.InspectionProcessName);
//sqlParameter.AddParameterInteger("inspectionformatid", inVo.InspectionFormatId);
//sqlParameter.AddParameterInteger("displayorder", inVo.DisplayOrder);
//UserData userdata = trxContext.UserData;
//sqlParameter.AddParameterString("registrationusercode", userdata.UserCode);
//sqlParameter.AddParameterDateTime("regdatetime", trxContext.ProcessingDBDateTime);
//sqlParameter.AddParameterString("factorycode", userdata.FactoryCode);
InspectionProcessVo outVo = new InspectionProcessVo();
//int aa = (int?)sqlCommandAdapter.ExecuteScalar(sqlParameter) ?? 0;
//execute SQL
outVo.AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter);
return(outVo);
}
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_bonding_metal_deform_scratch, ca_bonding_case_deform_scracth ");
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_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 tbloutput.datesss dates,'All Line' line_cd,* from ");
sql.Append("select datesss,model, 'All Line' line, ");
sql.Append("sum(fc_endplay_small) fc_endplay_small , sum(fc_endplay_big) fc_endplay_big, ");
sql.Append("sum(fc_shaft_scracth) fc_shaft_scracth, sum(fc_terminal_low) fc_terminal_low, ");
sql.Append("sum(fc_case_scracth_dirty) fc_case_scracth_dirty , 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, ");
sql.Append("sum(en2_insulation_resistance_ng) en2_insulation_resistance_ng, ");
sql.Append("sum(en2_cut_coil_wire) en2_cut_coil_wire ,");
sql.Append("sum(en2_no_load_current_hight) en2_no_load_current_hight, ");
sql.Append("sum(en2_ripple) en2_ripple, ");
sql.Append("sum(en2_chattering) en2_chattering, ");
sql.Append("sum(en2_lock) en2_lock, ");
sql.Append("sum(en2_open) en2_open, ");
sql.Append("sum(en2_no_load_speed_low) en2_no_load_speed_low, ");
sql.Append("sum(en2_starting_voltage) en2_starting_voltage, ");
sql.Append("sum(en2_no_load_speed_high) en2_no_load_speed_high, ");
sql.Append("sum(en2_rotor_mix) en2_rotor_mix, ");
sql.Append("sum(en2_surge_volt_max) en2_surge_volt_max, ");
sql.Append("sum(en2_wrong_post_of_pole) en2_wrong_post_of_pole, ");
sql.Append("sum(en2_err) en2_err, ");
sql.Append("sum(en2_noise) en2_noise, ");
sql.Append("sum(en1_insulation_resistace_ng) en1_insulation_resistace_ng, ");
sql.Append("sum(en1_cut_coil_wire) en1_cut_coil_wire, ");
sql.Append("sum(en1_lock) en1_lock, ");
sql.Append("sum(en1_wareform_ma_abnormal) en1_wareform_ma_abnormal,");
sql.Append("sum(en1_shaft_bent) en1_shaft_bent, ");
sql.Append("sum(en1_ripple) en1_ripple, ");
sql.Append("sum(en1_short) en1_short, ");
sql.Append("sum(en1_chattering) en1_chattering, ");
sql.Append("sum(en1_no_load_current_high) en1_no_load_current_high, ");
sql.Append("sum(en1_vibration_ng) en1_vibration_ng,");
sql.Append("sum(en1_open) en1_open, ");
sql.Append("sum(en1_rotor_mix) en1_rotor_mix, ");
sql.Append("sum(ba_tc_endplay_big) ba_tc_endplay_big, sum(ba_tc_endplay_small) ba_tc_endplay_small, ");
sql.Append("sum(ba_tc_brush_bent) ba_tc_brush_bent ,");
sql.Append("sum(ba_tc_shaft_mix) ba_tc_shaft_mix , ");
sql.Append("sum(ba_rto_ng) ba_rto_ng, ");
sql.Append("sum(ba_rto_mix) ba_rto_mix,");
sql.Append("sum(ba_app_metal_deform_scracth) ba_app_metal_deform_scracth, ");
sql.Append("sum(ba_app_ba_deform) ba_app_ba_deform, ");
sql.Append("sum(ba_app_endplate_deform_scracth) ba_app_endplate_deform_scracth, ");
sql.Append("sum(ba_app_error_other) ba_app_error_other, ");
sql.Append("sum(ba_bm_brush_deform_scracth) ba_bm_brush_deform_scracth, ");
sql.Append("sum(ba_bm_metal_deform_scracth) ba_bm_metal_deform_scracth, ");
sql.Append("sum(ba_bm_ba_deform) ba_bm_ba_deform,");
sql.Append("sum(ba_bm_endplay_deform_scracth) ba_bm_endplay_deform_scracth, ");
///
sql.Append("sum(ca_app_metal_dirty) ca_app_metal_dirty, ");
sql.Append("sum(ca_app_tape_hole_deform) ca_app_tape_hole_deform,");
sql.Append("sum(ca_app_metal_high) ca_app_metal_high, ");
sql.Append("sum(ca_app_case_deform_scracth) ca_app_case_deform_scracth, ");
sql.Append("sum(ca_app_metal_deform_scratch) ca_app_metal_deform_scratch, ");
sql.Append("sum(ca_app_magnet_broken) ca_app_magnet_broken, ");
sql.Append("sum(ca_mg_metal_deform_scratch) ca_mg_metal_deform_scratch, ");
sql.Append("sum(ca_mg_case_deform_scratch) ca_mg_case_deform_scratch, ");
sql.Append("sum(ca_bonding_metal_deform_scratch) ca_bonding_metal_deform_scratch,");
sql.Append("sum(ca_bonding_case_deform_scracth) ca_bonding_case_deform_scracth ");
sql.Append("from ");
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 order by fc.dates, fc.line_cd ) tbl where model = :model_cd group by datesss,model");
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"].ToString(),
ProLine = dataReader["line"].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)
{
WareHouseMainVo inVo = (WareHouseMainVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <WareHouseMainVo> voList = new ValueObjectList <WareHouseMainVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select * from (
select tam.rank_name as Account_Name,sum(tam.acquistion_cost) as Total_AcquisitionCose,sum(tam.current_depreciation) as Total_CurrentDepreication,sum(tam.monthly_depreciation) as Total_MonthlyDepreication,sum(tam.accum_depreciation_now)
as Total_AccumDepreication,sum(tam.net_value) as Total_NetBook from (select a.account_code_name, g.warehouse_main_id,
e.asset_cd, e.asset_no, e.asset_name, e.asset_model, e.asset_serial, e.asset_supplier, g.qty, a.account_code_cd, b.account_location_cd,
f.rank_cd, b.account_location_name, g.comment_data, e.asset_life,f.rank_name, e.acquistion_date, e.acquistion_cost, g.depreciation_start, g.depreciation_end,
g.current_depreciation,g.monthly_depreciation, g.accum_depreciation_now, g.net_value, e.asset_invoice, g.registration_date_time, g.registration_user_cd from t_warehouse_main g
left join m_account_code a on a.account_code_id = g.account_code_id
left join m_account_location b on b.account_location_id = g.account_location_id
left join m_location c on c.location_id = g.before_location_id
left join m_user_location d on d.user_location_id = g.user_location_id
left join m_asset e on e.asset_id = g.asset_id
left join m_rank f on f.rank_id = g.rank_id) tam group by tam.rank_name
union
select Case when sum(tam.acquistion_cost) > -1 then 'Total' else 'Total'
end codename, sum(tam.acquistion_cost) as Total_AcquisitionCose,sum(tam.current_depreciation) as Total_CurrentDepreication,
sum(tam.monthly_depreciation) as Total_MonthlyDepreication,sum(tam.accum_depreciation_now) as Total_AccumDepreication,sum(tam.net_value) as Total_NetBook
from ( select tam.rank_name as Account_Name,sum(tam.acquistion_cost) as acquistion_cost,sum(tam.current_depreciation) as current_depreciation,sum(tam.monthly_depreciation) as monthly_depreciation,sum(tam.accum_depreciation_now)
as accum_depreciation_now,sum(tam.net_value) as net_value from (select a.account_code_name, g.warehouse_main_id,
e.asset_cd, e.asset_no, e.asset_name, e.asset_model, e.asset_serial, e.asset_supplier, g.qty, a.account_code_cd, b.account_location_cd,
f.rank_cd, b.account_location_name, g.comment_data, e.asset_life,f.rank_name, e.acquistion_date, e.acquistion_cost, g.depreciation_start, g.depreciation_end,
g.current_depreciation,g.monthly_depreciation, g.accum_depreciation_now, g.net_value, e.asset_invoice, g.registration_date_time, g.registration_user_cd from t_warehouse_main g
left join m_account_code a on a.account_code_id = g.account_code_id
left join m_account_location b on b.account_location_id = g.account_location_id
left join m_location c on c.location_id = g.before_location_id
left join m_user_location d on d.user_location_id = g.user_location_id
left join m_asset e on e.asset_id = g.asset_id
left join m_rank f on f.rank_id = g.rank_id) tam group by tam.rank_name) tam) tbltotal order by account_name = 'Total'
");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
WareHouseMainVo outVo = new WareHouseMainVo
{
// , h., i., k., o.prodution_work_content_name
TotalAccumDepreication = double.Parse(dataReader["Total_AccumDepreication"].ToString()),
TotalAcquisitionCose = double.Parse(dataReader["Total_AcquisitionCose"].ToString()),
TotalCurrentDepreication = double.Parse(dataReader["Total_CurrentDepreication"].ToString()),
TotalMonthlyDepreication = double.Parse(dataReader["Total_MonthlyDepreication"].ToString()),
TotalNetBook = double.Parse(dataReader["Total_NetBook"].ToString()),
AccountCodeName = dataReader["Account_Name"].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)
{
AssetVo inVo = (AssetVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <AssetVo> voList = new ValueObjectList <AssetVo>();
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, string.Empty);
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select asset_id,asset_no,asset_cd,asset_name, asset_model, asset_supplier, asset_type, asset_serial, asset_invoice, asset_life, acquistion_date, acquistion_cost, registration_user_cd,registration_date_time,factory_cd, label_status, asset_po from m_asset");
sql.Append(" Where 1=1 ");
if (!String.IsNullOrEmpty(inVo.FactoryCode))
{
sql.Append(" and factory_cd = :factory_cd ");
sqlParameter.AddParameterString("factory_cd", inVo.FactoryCode);
}
if (inVo.AssetId > 0)
{
sql.Append(" and asset_id = :asset_id ");
sqlParameter.AddParameterInteger("asset_id", inVo.AssetId);
}
if (!string.IsNullOrEmpty(inVo.AssetCode))
{
sql.Append(" and asset_cd = :asset_cd ");
sqlParameter.AddParameterString("asset_cd", inVo.AssetCode);
}
if (!string.IsNullOrEmpty(inVo.AssetName))
{
sql.Append(" and asset_name = :asset_name ");
sqlParameter.AddParameterString("asset_name", inVo.AssetName);
}
if (inVo.AssetNo != 10000)
{
sql.Append(" and asset_no =:asset_no ");
sqlParameter.AddParameter("asset_no", inVo.AssetNo);
}
//create command
//DbCommandAdaptor
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
AssetVo outVo = new AssetVo
{
AssetCode = dataReader["asset_cd"].ToString(),
AssetNo = int.Parse(dataReader["asset_no"].ToString()),
AssetId = int.Parse(dataReader["asset_id"].ToString()),
AssetName = dataReader["asset_name"].ToString(),
AssetModel = dataReader["asset_model"].ToString(),
AssetSuppiler = dataReader["asset_supplier"].ToString(),
AssetType = dataReader["asset_type"].ToString(),
AssetInvoice = dataReader["asset_invoice"].ToString(),
AssetSerial = dataReader["asset_serial"].ToString(),
AssetLife = double.Parse(dataReader["asset_life"].ToString()),
AcquistionCost = double.Parse(dataReader["acquistion_cost"].ToString()),
AcquistionDate = DateTime.Parse(dataReader["acquistion_date"].ToString()),
RegistrationUserCode = dataReader["registration_user_cd"].ToString(),
RegistrationDateTime = DateTime.Parse(dataReader["registration_date_time"].ToString()),
FactoryCode = dataReader["factory_cd"].ToString(),
LabelStatus = dataReader["label_status"].ToString(),
AssetPO = dataReader["asset_po"].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);
}
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 (i1.dates+i1.times) datetimes, i1.model_cd,i1.line_cd, i1.process_cd, ");
sql.Append("rigs_wire_pb_sticky, rigs_com_pb_sticky, rigs_ring_prone, rigs_cracked_ring ");
sql.Append("from t_productioncontroller_input01 i1 ");
sql.Append("where i1.line_cd = :line_cd ");
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 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 and i1.line_cd = :line_cd ");
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: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(),
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()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
WareHouseVo inVo = (WareHouseVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <WareHouseVo> voList = new ValueObjectList <WareHouseVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select d.warehouse_id, d.asset_cd, b.rank_name,b.rank_cd,d.time_start, a.asset_name, a.asset_model, a.asset_type, a.asset_invoice, d.comments_remake, a.asset_supplier, d.qty, d.unit, c.user_location_name, d.registration_date_time, d.location_before_cd, d.location_after_cd from t_warehouse d
left join m_asset a on a.asset_cd = d.asset_cd
left join m_rank b on b.rank_id = d.rank_id
left join m_user_location c on c.user_location_cd = d.user_location_cd
where 1=1 ");
if (!String.IsNullOrEmpty(inVo.AssetCode))
{
sql.Append(@" and d.asset_cd =:asset_cd");
sqlParameter.AddParameterString("asset_cd", inVo.AssetCode);
}
if (!String.IsNullOrEmpty(inVo.RankCode))
{
sql.Append(" and b.rank_cd =:rank_cd");
sqlParameter.AddParameterString("rank_cd", inVo.RankCode);
}
if (!String.IsNullOrEmpty(inVo.AssetModel))
{
sql.Append(" and a.asset_model =:asset_model");
sqlParameter.AddParameterString("asset_model", inVo.AssetModel);
}
if (!String.IsNullOrEmpty(inVo.AssetName))
{
sql.Append(" and a.asset_name =:asset_name");
sqlParameter.AddParameterString("asset_name", inVo.AssetName);
}
if (!String.IsNullOrEmpty(inVo.AssetType))
{
sql.Append(" and a.asset_type =:asset_type");
sqlParameter.AddParameterString("asset_type", inVo.AssetType);
}
if (!String.IsNullOrEmpty(inVo.Invoice))
{
sql.Append(" and a.asset_invoice =:asset_invoice");
sqlParameter.AddParameterString("asset_invoice", inVo.Invoice);
}
if (!String.IsNullOrEmpty(inVo.AfterLocation))
{
sql.Append(" and d.location_after_cd =:location_after_cd");
sqlParameter.AddParameterString("location_after_cd", inVo.AfterLocation);
}
sql.Append(" and d.warehouse_id in(select Max(warehouse_id) from t_warehouse group by asset_cd) ");
sql.Append(" order by d.registration_date_time desc");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
WareHouseVo outVo = new WareHouseVo
{
// , h., i., k., o.prodution_work_content_name
WareHouseId = int.Parse(dataReader["warehouse_id"].ToString()),
TimeStart = DateTime.Parse(dataReader["time_start"].ToString()),
AssetCode = dataReader["asset_cd"].ToString(),
RankName = dataReader["rank_name"].ToString(),
AssetName = dataReader["asset_name"].ToString(),
Qty = int.Parse(dataReader["qty"].ToString()),
Unit = dataReader["unit"].ToString(),
AssetModel = dataReader["asset_model"].ToString(),
AssetSupplier = dataReader["asset_supplier"].ToString(),
AssetType = dataReader["asset_type"].ToString(),
UserLocationName = dataReader["user_location_name"].ToString(),
Comments = dataReader["comments_remake"].ToString(),
RegistrationDateTime = DateTime.Parse(dataReader["registration_date_time"].ToString()),
BeforeLocation = dataReader["location_before_cd"].ToString(),
AfterLocation = dataReader["location_after_cd"].ToString(),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
