public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select tblin.datestimes, tblin.model_cd, tblin.line_cd, ( final_app + en2 + en1 + case_assy + case_bonding + case_mg + trust_gap + rotor + bracket_assy + bracket_metal) as sum_ng,ca_input_line,ca_input, ba_input, fc_input,output, final_app, en2, en1, case_assy, case_bonding, case_mg, trust_gap, rotor, bracket_assy, bracket_metal from
(select(fc.dates + fc.times) datestimes, fc.model_cd, fc.line_cd, ca_input_line,ca_input, ba_input, fc_input, output, (fc_endplay_small + fc_endplay_big + fc_shaft_scracth + fc_terminal_low + fc_case_scracth_dirty + fc_pinion_worm_ng + fc_shaft_lock + fc_ba_deform + fc_tape_hole_deform + fc_brush_rust + fc_metal_deform_scracth + fc_washer_tape_hole) final_app, (en2_insulation_resistance_ng + en2_cut_coil_wire + en2_no_load_current_hight + en2_ripple + en2_chattering + en2_lock + en2_open + en2_no_load_speed_low + en2_starting_voltage + en2_no_load_speed_high + en2_rotor_mix + en2_surge_volt_max + en2_wrong_post_of_pole + en2_err + en2_noise) en2, (en1_insulation_resistace_ng + en1_cut_coil_wire + en1_lock + en1_wareform_ma_abnormal + en1_shaft_bent + en1_ripple + en1_short + en1_chattering + en1_no_load_current_high + en1_vibration_ng + en1_open + en1_rotor_mix) en1, (ca_app_metal_dirty + ca_app_tape_hole_deform + ca_app_metal_high + ca_app_case_deform_scracth + ca_app_metal_deform_scratch + ca_app_magnet_broken) as case_assy, (ca_mg_metal_deform_scratch + ca_mg_case_deform_scratch) as case_mg, (ca_bonding_metal_deform_scratch + ca_bonding_case_deform_scracth) case_bonding, (ba_tc_endplay_big + ba_tc_endplay_small + ba_tc_brush_bent + ba_tc_shaft_mix) as trust_gap, (ba_rto_ng + ba_rto_mix) as rotor, (ba_app_metal_deform_scracth + ba_app_ba_deform + ba_app_endplate_deform_scracth + ba_app_error_other) as bracket_assy, (ba_bm_brush_deform_scracth + ba_bm_metal_deform_scracth + ba_bm_ba_deform + ba_bm_endplay_deform_scracth) as bracket_metal
from t_ncvc_pdc_fc fc left join t_ncvc_pdc_en2 e2 on fc.fc_id = e2.en2_id and fc.line_cd = e2.line_cd and fc.dates = e2.dates
left join t_ncvc_pdc_en1 e1 on fc.fc_id = e1.en1_id and fc.line_cd = e1.line_cd
left join t_ncvc_pdc_ca ca on fc.fc_id = ca.ca_id and fc.line_cd = ca.line_cd
left join t_ncvc_pdc_ba ba on fc.fc_id = ba.ba_id and fc.line_cd = ba.line_cd
where fc.line_cd = :line_cd and fc.dates = :dates and(fc.times in(select min(times) from t_ncvc_pdc_fc where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) or fc.dates - 1 = :dates and fc.line_cd = :line_cd and fc.line_cd = :line_cd and(fc.times in(select min(times) from t_ncvc_pdc_fc where times between '00:00:00' and '00:55:00' and dates - 1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '05:00:00' and '05:56:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd))) tblin");
sqlParameter.AddParameterString("line_cd", inVo.ProLine);
sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
TimeHour = DateTime.Parse(dataReader["datestimes"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
TotalNG = int.Parse(dataReader["sum_ng"].ToString()),
ProInput = int.Parse(dataReader["ca_input_line"].ToString()),
ProInputCase = int.Parse(dataReader["ca_input"].ToString()),
ProInputBracket = int.Parse(dataReader["ba_input"].ToString()),
ProInputApp = int.Parse(dataReader["fc_input"].ToString()),
ProOutput = int.Parse(dataReader["output"].ToString()),
Final_App = int.Parse(dataReader["final_app"].ToString()),
En2NG = int.Parse(dataReader["en2"].ToString()),
Case_Assy = int.Parse(dataReader["case_assy"].ToString()),
En1NG = int.Parse(dataReader["en1"].ToString()),
MG_Bongding = int.Parse(dataReader["case_bonding"].ToString()),
Case_MG = int.Parse(dataReader["case_mg"].ToString()),
TrustGap = int.Parse(dataReader["trust_gap"].ToString()),
Rotor = int.Parse(dataReader["rotor"].ToString()),
Braket = int.Parse(dataReader["bracket_assy"].ToString()),
Bracket_Metal = int.Parse(dataReader["bracket_metal"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ResponseMachineVo inVo = (ResponseMachineVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ResponseMachineVo> voList = new ValueObjectList <ResponseMachineVo>();
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, string.Empty);
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select response_machine_id, machine_id, prodution_work_content_id, registration_user_cd,registration_date_time,factory_cd from public.m_ncvp_response_machine");
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.MachineId > 0)
{
sql.Append(" and machine_id = :machine_id ");
sqlParameter.AddParameterInteger("machine_id", inVo.MachineId);
}
if (inVo.ProdutionWorkContentId > 0)
{
sql.Append(" and prodution_work_content_id = :prodution_work_content_id ");
sqlParameter.AddParameterInteger("prodution_work_content_id", inVo.ProdutionWorkContentId);
}
if (inVo.ResponseMachineId > 0)
{
sql.Append(" and response_machine_id = :response_machine_id ");
sqlParameter.AddParameterInteger("response_machine_id", inVo.ResponseMachineId);
}
//create command
//DbCommandAdaptor
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ResponseMachineVo outVo = new ResponseMachineVo
{
//convert
ResponseMachineId = int.Parse(dataReader["response_machine_id"].ToString()),
ProdutionWorkContentId = int.Parse(dataReader["prodution_work_content_id"].ToString()),
MachineId = int.Parse(dataReader["machine_id"].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)
{
ModelLineVo inVo = (ModelLineVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ModelLineVo> voList = new ValueObjectList <ModelLineVo>();
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, string.Empty);
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select model_line_id, model_id, line_id, registration_user_cd,registration_date_time,factory_cd from public.m_ncvp_model_line");
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.ModelID > 0)
{
sql.Append(" and model_id = :model_id ");
sqlParameter.AddParameterInteger("model_id", inVo.ModelID);
}
if (inVo.LineID > 0)
{
sql.Append(" and line_id = :line_id ");
sqlParameter.AddParameterInteger("line_id", inVo.LineID);
}
if (inVo.ModelLineID > 0)
{
sql.Append(" and model_line_id = :model_line_id ");
sqlParameter.AddParameterInteger("model_line_id", inVo.ModelLineID);
}
//create command
//DbCommandAdaptor
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ModelLineVo outVo = new ModelLineVo
{
//convert
ModelLineID = int.Parse(dataReader["model_line_id"].ToString()),
ModelID = int.Parse(dataReader["model_id"].ToString()),
LineID = int.Parse(dataReader["line_id"].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)
{
DetailPositionVo inVo = (DetailPositionVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <DetailPositionVo> voList = new ValueObjectList <DetailPositionVo>();
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, string.Empty);
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select dt.detail_postion_id,l.location_cd, dt.detail_postion_cd, dt.detail_postion_name, dt.registration_user_cd,dt.registration_date_time,dt.factory_cd
from m_detail_postion dt left join m_location l on dt.location_id = l.location_id
Where 1=1 ");
if (inVo.DetailPositionId > 0)
{
sql.Append(" and dt.detail_postion_id = :detail_postion_id ");
sqlParameter.AddParameterInteger("detail_postion_id", inVo.DetailPositionId);
}
if (!string.IsNullOrEmpty(inVo.DetailPositionCode))
{
sql.Append(" and dt.detail_postion_cd = :detail_postion_cd ");
sqlParameter.AddParameterString("detail_postion_cd", inVo.DetailPositionCode);
}
if (!string.IsNullOrEmpty(inVo.DetailPositionName))
{
sql.Append(" and dt.detail_postion_name = :detail_postion_name ");
sqlParameter.AddParameterString("detail_postion_name", inVo.DetailPositionName);
}
if (!string.IsNullOrEmpty(inVo.LocationCd))
{
sql.Append(" and l.location_cd = :location_cd ");
sqlParameter.AddParameterString("location_cd", inVo.LocationCd);
}
//create command
//DbCommandAdaptor
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
DetailPositionVo outVo = new DetailPositionVo
{
DetailPositionCode = dataReader["detail_postion_cd"].ToString(),
DetailPositionId = int.Parse(dataReader["detail_postion_id"].ToString()),
DetailPositionName = dataReader["detail_postion_name"].ToString(),
LocationCd = dataReader["location_cd"].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 Case When times between '06:00:00' and '23:59:00' then dates when ");
sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss, model_cd,'All Line' line_cd, sum(en1_insulation_resistace_ng) en1_insulation_resistace_ng, ");
sql.Append("sum(en1_cut_coil_wire) en1_cut_coil_wire, sum(en1_lock) en1_lock ,");
sql.Append("sum(en1_wareform_ma_abnormal) en1_wareform_ma_abnormal, sum(en1_shaft_bent) en1_shaft_bent, ");
sql.Append("sum(en1_ripple) en1_ripple, sum(en1_short) en1_short, ");
sql.Append("sum(en1_chattering) en1_chattering, sum(en1_no_load_current_high) en1_no_load_current_high, ");
sql.Append("sum(en1_vibration_ng) en1_vibration_ng, sum(en1_open) en1_open, sum(en1_rotor_mix) en1_rotor_mix from ");
sql.Append("(select i2.dates,i2.times,i2.model_cd,i2.line_cd, en1_insulation_resistace_ng, en1_cut_coil_wire, en1_lock,en1_wareform_ma_abnormal, en1_shaft_bent, en1_ripple, en1_short, en1_chattering, en1_no_load_current_high, en1_vibration_ng, en1_open, en1_rotor_mix from t_ncvc_pdc_en1 i2 left join (select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id from(select tblca1.dates, tblca1.line_cd, idca1, idca3 from(select line_cd, o.dates, max(o.en1_id) idca1 from t_ncvc_pdc_en1 o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 left join(select line_cd, (o.dates - 1) dates, max(o.en1_id) idca3 from t_ncvc_pdc_en1 o where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl order by dates, line_cd) l on l.line_cd = i2.line_cd where i2.en1_id = l.id order by i2.dates,i2.line_cd ) t where model_cd = :model_cd group by datesss,model_cd order by datesss");
sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
En1_insulation_resistace_ng = int.Parse(dataReader["en1_insulation_resistace_ng"].ToString()),
En1_cut_coil_wire = int.Parse(dataReader["en1_cut_coil_wire"].ToString()),
En1_lock = int.Parse(dataReader["en1_lock"].ToString()),
En1_wareform_ma_abnormal = int.Parse(dataReader["en1_wareform_ma_abnormal"].ToString()),
En1_shaft_bent = int.Parse(dataReader["en1_shaft_bent"].ToString()),
En1_ripple = int.Parse(dataReader["en1_ripple"].ToString()),
En1_short = int.Parse(dataReader["en1_short"].ToString()),
En1_chattering = int.Parse(dataReader["en1_chattering"].ToString()),
En1_vibration_ng = int.Parse(dataReader["en1_vibration_ng"].ToString()),
En1_open = int.Parse(dataReader["en1_open"].ToString()),
En1_rotor_mix = int.Parse(dataReader["en1_rotor_mix"].ToString()),
En1_no_load_current_high = int.Parse(dataReader["en1_no_load_current_high"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
GA1ModelVo inVo = (GA1ModelVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <GA1ModelVo> voList = new ValueObjectList <GA1ModelVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select row_number() over(order by a90_datetime asc) stt, a90_model, a90_line, a90_barcode, a90_thurst_status, a90_noise_status, a90_oqc_status,
a90_oqc_data, a90_shipping, a90_user_cd, a90_datetime from t_checkpusha90
where 1=1 ");
if (!string.IsNullOrEmpty(inVo.A90Model))
{
sql.Append(@" and a90_model =:a90_model");
sqlParameter.AddParameterString("a90_model", inVo.A90Model);
}
if (inVo.DaTa == false)
{
sql.Append(@" and a90_datetime >= :dtpfrom and a90_datetime <= :dtpto");
}
else if (inVo.DaTa == true)
{
sql.Append(@" and a90_datetime >= :dtpfrom ");
}
sqlParameter.AddParameterDateTime("dtpfrom", inVo.DateTimeFrom);
sqlParameter.AddParameterDateTime("dtpto", inVo.DateTimeTo);
sql.Append(@" order by a90_datetime desc");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
GA1ModelVo outVo = new GA1ModelVo
{
STT = dataReader["stt"].ToString(),
A90Model = dataReader["a90_model"].ToString(),
A90Line = dataReader["a90_line"].ToString(),
A90Barcode = dataReader["a90_barcode"].ToString(),
A90ThurstStatus = dataReader["a90_thurst_status"].ToString(),
A90NoiseStatus = dataReader["a90_noise_status"].ToString(),
A90OQCStatus = dataReader["a90_oqc_status"].ToString(),
A90OQCData = dataReader["a90_oqc_data"].ToString(),
A90Shipping = bool.Parse(dataReader["a90_shipping"].ToString()),
RegistrationUserCode = dataReader["a90_user_cd"].ToString(),
RegistrationDateTime = DateTime.Parse(dataReader["a90_datetime"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select Case When times between '06:00:00' and '23:59:00' then dates when ");
sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss, model_cd,'All Line' line_cd, sum(fc_endplay_small) fc_endplay_small, ");
sql.Append("sum(fc_endplay_big) fc_endplay_big, sum(fc_shaft_scracth) fc_shaft_scracth ,");
sql.Append("sum(fc_terminal_low) fc_terminal_low, sum(fc_case_scracth_dirty) fc_case_scracth_dirty, ");
sql.Append("sum(fc_pinion_worm_ng) fc_pinion_worm_ng, ");
sql.Append("sum(fc_shaft_lock) fc_shaft_lock, sum(fc_ba_deform) fc_ba_deform, ");
sql.Append("sum(fc_tape_hole_deform) fc_tape_hole_deform, sum(fc_brush_rust) fc_brush_rust, ");
sql.Append("sum(fc_metal_deform_scracth) fc_metal_deform_scracth, sum(fc_washer_tape_hole ) fc_washer_tape_hole from ");
sql.Append("(select i2.dates,i2.times,i2.model_cd,i2.line_cd, fc_endplay_small, fc_endplay_big, fc_shaft_scracth, fc_terminal_low, fc_case_scracth_dirty, fc_pinion_worm_ng, fc_shaft_lock, fc_ba_deform, fc_tape_hole_deform, fc_brush_rust, fc_metal_deform_scracth, fc_washer_tape_hole from t_ncvc_pdc_fc i2 left join (select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id from(select tblca1.dates, tblca1.line_cd, idca1, idca3 from(select line_cd, o.dates, max(o.fc_id) idca1 from t_ncvc_pdc_fc o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 left join(select line_cd, (o.dates - 1) dates, max(o.fc_id) idca3 from t_ncvc_pdc_fc o where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl order by dates, line_cd) l on l.line_cd = i2.line_cd where i2.fc_id = l.id order by i2.dates,i2.line_cd ) t where model_cd= :model_cd group by datesss,model_cd order by datesss");
sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
{
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
FC_endplay_small = int.Parse(dataReader["fc_endplay_small"].ToString()),
FC_endplay_big = int.Parse(dataReader["fc_endplay_big"].ToString()),
FC_shaft_scracth = int.Parse(dataReader["fc_shaft_scracth"].ToString()),
FC_terminal_low = int.Parse(dataReader["fc_terminal_low"].ToString()),
FC_case_scracth_dirty = int.Parse(dataReader["fc_case_scracth_dirty"].ToString()),
FC_pinion_worm_ng = int.Parse(dataReader["fc_pinion_worm_ng"].ToString()),
FC_shaft_lock = int.Parse(dataReader["fc_shaft_lock"].ToString()),
FC_deform = int.Parse(dataReader["fc_ba_deform"].ToString()),
FC_tape_hole_deform = int.Parse(dataReader["fc_tape_hole_deform"].ToString()),
FC_brush_rust = int.Parse(dataReader["fc_brush_rust"].ToString()),
FC_metal_deform_scracth = int.Parse(dataReader["fc_metal_deform_scracth"].ToString()),
FC_washer_tape_hole = int.Parse(dataReader["fc_washer_tape_hole"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProdutionWorkContentTypeVo inVo = (ProdutionWorkContentTypeVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProdutionWorkContentTypeVo> voList = new ValueObjectList <ProdutionWorkContentTypeVo>();
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, string.Empty);
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select work_content_type_id, work_content_type_cd, work_content_type_name,registration_user_cd,registration_date_time,factory_cd from m_work_content_type");
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.ProdutionWorkContentTypeId > 0)
{
sql.Append(" and work_content_type_id = :prodution_work_content_type_id ");
sqlParameter.AddParameterInteger("prodution_work_content_type_id", inVo.ProdutionWorkContentTypeId);
}
if (!string.IsNullOrEmpty(inVo.ProdutionWorkContentTypeCode))
{
sql.Append(" and work_content_type_cd = :prodution_work_content_type_cd ");
sqlParameter.AddParameterString("prodution_work_content_type_cd", inVo.ProdutionWorkContentTypeCode);
}
if (!string.IsNullOrEmpty(inVo.ProdutionWorkContentTypeName))
{
sql.Append(" and work_content_type_name = :prodution_work_content_type_name ");
sqlParameter.AddParameterString("prodution_work_content_type_name", inVo.ProdutionWorkContentTypeName);
}
//create command
//DbCommandAdaptor
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProdutionWorkContentTypeVo outVo = new ProdutionWorkContentTypeVo
{
ProdutionWorkContentTypeCode = dataReader["work_content_type_cd"].ToString(),
ProdutionWorkContentTypeId = int.Parse(dataReader["work_content_type_id"].ToString()),
ProdutionWorkContentTypeName = dataReader["work_content_type_name"].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);
}
/// <summary>
/// Create Shift TemplateVo
/// </summary>
private ValueObjectList <LineRestTimeVo> getShiftTemplateVo()
{
LineRestTimeVo dayVo = new LineRestTimeVo();
dayVo.Shift = Convert.ToInt32(GlobalMasterDataTypeEnum.SHIFT_DAY.GetValue());
dayVo.ShiftText = GlobalMasterDataTypeEnum.SHIFT_DAY.ToString();
LineRestTimeVo nightVo = new LineRestTimeVo();
nightVo.Shift = Convert.ToInt32(GlobalMasterDataTypeEnum.SHIFT_NIGHT.GetValue());
nightVo.ShiftText = GlobalMasterDataTypeEnum.SHIFT_NIGHT.ToString();
ValueObjectList <Vo.LineRestTimeVo> shiftVo = new ValueObjectList <Vo.LineRestTimeVo>();
shiftVo.add(dayVo);
shiftVo.add(nightVo);
return(shiftVo);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
TransferVo inVo = (TransferVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <TransferVo> voList = new ValueObjectList <TransferVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select a.transfer_device_id, a.transfer_device_cd, a.transfer_content, a.transfer_dept, a.trans_status, a.transfer_dept_approver, a.destination_dept, a.destination_status, a.destination_dept_approver, a.account_approver, a.approve_status, b.user_name from t_transfer_device a
left join m_mes_user b on b.user_cd = b.registration_user_cd
where 1 = 1");
sql.Append(@" and a.transfer_dept = (select dept_cd from m_user_location where user_location_name = :user_location_name)");
sqlParameter.AddParameterString("user_location_name", inVo.UserName);
if (!String.IsNullOrEmpty(inVo.DestinationDept))
{
sql.Append(@" and a.destination_dept = :destination_dept");
sqlParameter.AddParameterString("destination_dept", inVo.DestinationDept);
}
else
{
sql.Append(@" or a.destination_dept = (select dept_cd from m_user_location where user_location_name = :user_location_name)");
sqlParameter.AddParameterString("user_location_name", inVo.UserName);
}
sql.Append(" order by a.transfer_device_cd");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
TransferVo outVo = new TransferVo
{
TransferDeviceId = int.Parse(dataReader["transfer_device_id"].ToString()),
TransferDeviceCode = dataReader["transfer_device_cd"].ToString(),
TransferContent = dataReader["transfer_content"].ToString(),
TransferDept = dataReader["transfer_dept"].ToString(),
TransferStatus = dataReader["trans_status"].ToString(),
TransferApprover = dataReader["transfer_dept_approver"].ToString(),
DestinationDept = dataReader["destination_dept"].ToString(),
DestinationStatus = dataReader["destination_status"].ToString(),
DestinationApprover = dataReader["destination_dept_approver"].ToString(),
Accounter = dataReader["account_approver"].ToString(),
ApproveStatus = dataReader["approve_status"].ToString(),
UserName = dataReader["user_name"].ToString()
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select Case When times between '06:00:00' and '23:59:00' then dates when ");
sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss,'LA459' model_cd,'All Line' line_cd, sum(en1_lock) en1_lock, ");
sql.Append("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, ");
sql.Append("sum(en1_duty) en1_duty, sum(en1_short) en1_short, ");
sql.Append("sum(en1_beat_case_ng) en1_beat_case_ng, sum(en1_beat_fundou_ng) en1_beat_fundou_ng from ");
sql.Append("(select i2.dates,i2.times,i2.line_cd, en1_lock, en1_cut, en1_chattering, en1_insulation, en1_open, en1_bad_wave, en1_duty, en1_short, en1_beat_case_ng, en1_beat_fundou_ng from t_productioncontroller_output02 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.output02_id) idca1 from t_productioncontroller_output02 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.output02_id) idca3 from t_productioncontroller_output02 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.output02_id = l.id order by i2.dates,i2.line_cd ) t group by datesss order by datesss");
sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
//sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
En1_lock = int.Parse(dataReader["en1_lock"].ToString()),
En1_cut = int.Parse(dataReader["en1_cut"].ToString()),
En1_chattering = int.Parse(dataReader["en1_chattering"].ToString()),
En1_insulation = int.Parse(dataReader["en1_insulation"].ToString()),
En1_open = int.Parse(dataReader["en1_open"].ToString()),
En1_bad_wave = int.Parse(dataReader["en1_bad_wave"].ToString()),
En1_duty = int.Parse(dataReader["en1_duty"].ToString()),
En1_short = int.Parse(dataReader["en1_short"].ToString()),
En1_beat_case_ng = int.Parse(dataReader["en1_beat_case_ng"].ToString()),
En1_beat_fundou_ng = int.Parse(dataReader["en1_beat_fundou_ng"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
UserLocationVo inVo = (UserLocationVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <UserLocationVo> voList = new ValueObjectList <UserLocationVo>();
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, string.Empty);
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select user_location_id,user_location_cd,user_location_name,dept_cd, registration_user_cd,registration_date_time,factory_cd from m_user_location");
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.UserLocationId > 0)
{
sql.Append(" and user_location_id = :user_location_id ");
sqlParameter.AddParameterInteger("user_location_id", inVo.UserLocationId);
}
if (!string.IsNullOrEmpty(inVo.UserLocationCode))
{
sql.Append(" and user_location_cd = :user_location_cd ");
sqlParameter.AddParameterString("user_location_cd", inVo.UserLocationCode);
}
if (!string.IsNullOrEmpty(inVo.UserLocationName))
{
sql.Append(" and user_location_name = :user_location_name ");
sqlParameter.AddParameterString("user_location_name", inVo.UserLocationName);
}
//create command
//DbCommandAdaptor
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
UserLocationVo outVo = new UserLocationVo
{
UserLocationCode = dataReader["user_location_cd"].ToString(),
UserLocationId = int.Parse(dataReader["user_location_id"].ToString()),
UserLocationName = dataReader["user_location_name"].ToString(),
DeptCode = dataReader["dept_cd"].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)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select count(*) datas from (");
sql.Append("select * from (");
sql.Append("select a90_barcode bar,max(a90_date+a90_time) from t_checkpusha90 ");
sql.Append("where a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto ");
if (!string.IsNullOrEmpty(inVo.LineCode))
{
sql.Append(@" and a90_line =:line");
sqlParameter.AddParameterString("line", inVo.LineCode);
}
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(@" and a90_model =:a90_model");
sqlParameter.AddParameterString("a90_model", inVo.ModelCode);
}
sql.Append(" group by a90_barcode) a left join t_checkpusha90 b on a.bar = b.a90_barcode and a.max = b.a90_date+b.a90_time ) tbl ");
sql.Append("where 1=1 and bar not like ''");
if (inVo.change)
{
sql.Append(@" and a90_noise_status = 'OK' ");
}
else
{
sql.Append(@" and a90_noise_status = 'NG' ");
}
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
{
InspecData = dataReader["datas"].ToString()
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
GroupMachineVo inVo = (GroupMachineVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <GroupMachineVo> voList = new ValueObjectList <GroupMachineVo>();
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, string.Empty);
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select a.group_machine_cd, a.group_machine_name, b.machine_name,
a.registration_user_cd,a.registration_date_time,a.factory_cd from m_group_machine a
left join m_machine b on b.machine_id = a.machine_id");
sql.Append(" Where 1=1");
if (!String.IsNullOrEmpty(inVo.GroupMachineCode))
{
sql.Append(" and group_machine_cd = :group_machine_cd ");
sqlParameter.AddParameterString("group_machine_cd", inVo.GroupMachineCode);
}
if (!String.IsNullOrEmpty(inVo.GroupMachineName))
{
sql.Append(" and group_machine_name = :group_machine_name ");
sqlParameter.AddParameterString("group_machine_name", inVo.GroupMachineName);
}
if (!String.IsNullOrEmpty(inVo.MachineName))
{
sql.Append(" and machine_name = :machine_name ");
sqlParameter.AddParameterString("machine_name", inVo.MachineName);
}
//create command
//DbCommandAdaptor
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
GroupMachineVo outVo = new GroupMachineVo
{
// GroupMachineCode = dataReader["group_machine_cd"].ToString(),
GroupMachineName = dataReader["group_machine_name"].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)
{
try
{
//VARIABLE
StringBuilder query = new StringBuilder();
AccountLocationVo inVo = (AccountLocationVo)vo;
ValueObjectList <AccountLocationVo> listVo = new ValueObjectList <AccountLocationVo>();
//CREATE SQL ADAPTER AND PARAMETER LIST
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, query.ToString());
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
//QUERY STRING
query.Append("Select * from m_account_location where 1=1 ");
if (inVo.account_location_id > 0)
{
query.Append("and account_location_id='").Append(inVo.account_location_id).Append("' ");
}
if (string.IsNullOrEmpty(inVo.account_location_cd))
{
query.Append("and account_location_cd='").Append(inVo.account_location_cd).Append("' ");
}
if (string.IsNullOrEmpty(inVo.account_location_type))
{
query.Append("and account_location_type='").Append(inVo.account_location_type).Append("' ");
}
query.Append("order by account_location_id");
//GET SQL ADAPTER
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, query.ToString());
//EXECUTE READER FROM COMMAND
IDataReader datareader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (datareader.Read())
{
AccountLocationVo outVo = new AccountLocationVo
{
account_location_id = (int)datareader["account_location_id"],
account_location_cd = datareader["account_location_cd"].ToString(),
account_location_name = datareader["account_location_name"].ToString(),
account_location_type = datareader["account_location_type"].ToString(),
registration_user_cd = datareader["registration_user_cd"].ToString(),
registration_date_time = (DateTime)datareader["registration_date_time"],
factory_cd = datareader["factory_cd"].ToString()
};
listVo.add(outVo);
}
//CLEAR AND CLOSE CONNECTION
query.Clear();
datareader.Close();
base.CloseConnection(trxContext);
return(listVo);
}
catch
{
throw new NotImplementedException();
}
}
public ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
InspectionProcessVo inVo = (InspectionProcessVo)vo;
ValueObjectList <ValueObjectList <ValueObject> > outVo = null;
//if process data not found return the vo
if (inVo == null || inVo.InspectionProcessIdCopy == 0)
{
return(outVo);
}
InspectionProcessVo returnProcessVo = null;
inVo.InspectionProcessCode = inVo.InspectionFormatCode + GlobalMasterDataTypeEnum.CODE_SEPARATOR.GetValue() + GlobalMasterDataTypeEnum.PROCESS_CODE.GetValue() + GlobalMasterDataTypeEnum.CODE_SEPARATOR.GetValue() + inVo.SequenceNo;
returnProcessVo = (InspectionProcessVo)addInspectionProcessMasterMntCbm.Execute(trxContext, inVo);
//To get the old record for InspectionItem Insertion
inVo.InspectionProcessId = inVo.InspectionProcessIdCopy;
//No records Added
if (returnProcessVo == null || returnProcessVo.InspectionProcessId == 0)
{
return(outVo);
}
returnProcessVo.InspectionProcessCode = inVo.InspectionProcessCode;
ValueObjectList <ValueObject> CombinationVo = new ValueObjectList <ValueObject>();
CombinationVo.add(inVo);
CombinationVo.add(returnProcessVo);
if (outVo == null)
{
outVo = new ValueObjectList <ValueObjectList <ValueObject> >();
}
outVo.add(CombinationVo);
return(outVo);
}
public ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
InspectionProcessVo inVo = (InspectionProcessVo)vo;
ValueObjectList <InspectionProcessVo> inspectionProcessListInVo = new ValueObjectList <InspectionProcessVo>();
ValueObjectList <InspectionItemVo> inspectionItemListInVo = new ValueObjectList <InspectionItemVo>();
ValueObjectList <InspectionItemSelectionDatatypeValueVo> inspectionDataTypeSelectionListInVo = new ValueObjectList <InspectionItemSelectionDatatypeValueVo>();
ValueObjectList <InspectionSpecificationVo> inspectionSpecificationListInVo = new ValueObjectList <InspectionSpecificationVo>();
ValueObjectList <InspectionTestInstructionVo> inspectionTestInstructionListInVo = new ValueObjectList <InspectionTestInstructionVo>();
ValueObjectList <InspectionTestInstructionVo> inspectionTestInstructionDetailListInVo = new ValueObjectList <InspectionTestInstructionVo>();
ValueObjectList <ValueObject> OutVo = new ValueObjectList <ValueObject>();
inspectionProcessListInVo.add(inVo);
// Create the Vo to get the Inspection Process
ValueObjectList <InspectionProcessVo> GetProcessinVoList = new ValueObjectList <InspectionProcessVo>();
InspectionProcessVo ProcessVo = new InspectionProcessVo();
ProcessVo.InspectionProcessId = inVo.InspectionProcessIdCopy;
GetProcessinVoList.add(ProcessVo);
//get inspection item list for the above fetched processlist
inspectionItemListInVo = (ValueObjectList <InspectionItemVo>)getInspectionItemListCbm.Execute(trxContext, GetProcessinVoList);
if (inspectionItemListInVo != null && inspectionItemListInVo.GetList() != null && inspectionItemListInVo.GetList().Count > 0)
{
//get inspectionSelectionDataType for the above fetched itemlist
inspectionDataTypeSelectionListInVo = (ValueObjectList <InspectionItemSelectionDatatypeValueVo>)getInspectionSelectionDataTypeListCbm.Execute(trxContext, inspectionItemListInVo);
//get inspectionspecification for the above fetched itemlist
inspectionSpecificationListInVo = (ValueObjectList <InspectionSpecificationVo>)getInspectionSpecficationListCbm.Execute(trxContext, inspectionItemListInVo);
//get inspectiontestinstruction for the above fetched itemlist
inspectionTestInstructionListInVo = (ValueObjectList <InspectionTestInstructionVo>)getInspectionTestInstructionListCbm.Execute(trxContext, inspectionItemListInVo);
}
if (inspectionTestInstructionListInVo != null && inspectionTestInstructionListInVo.GetList() != null && inspectionTestInstructionListInVo.GetList().Count > 0)
{
//get inspectiontestinstructiondetail for the above fetched inspectiontestinstruction
inspectionTestInstructionDetailListInVo = (ValueObjectList <InspectionTestInstructionVo>)getInspectionTestInstructionDetailListCbm.Execute(trxContext, inspectionTestInstructionListInVo);
}
OutVo.add(null);
OutVo.add(inspectionProcessListInVo);
OutVo.add(inspectionItemListInVo);
OutVo.add(inspectionDataTypeSelectionListInVo);
OutVo.add(inspectionSpecificationListInVo);
OutVo.add(inspectionTestInstructionListInVo);
OutVo.add(inspectionTestInstructionDetailListInVo);
return(OutVo);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
MoldCategoryVo inVo = (MoldCategoryVo)arg;
StringBuilder sqlQuery = new StringBuilder();
//create SQL
sqlQuery.Append("Select Count(*) as MoldCategoryCount,");
sqlQuery.Append(" mold_category_cd, ");
sqlQuery.Append(" mold_category_id ");
sqlQuery.Append(" from m_mold_category ");
sqlQuery.Append(" where factory_cd = :factorycode ");
if (inVo.MoldCategoryCode != null)
{
sqlQuery.Append(" and UPPER(mold_category_cd) = UPPER(:moldCategorycd) ");
}
sqlQuery.Append(" group by mold_category_cd,mold_category_id ");
//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);
}
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
ValueObjectList <MoldCategoryVo> outVo = new ValueObjectList <MoldCategoryVo> ();
while (dataReader.Read())
{
MoldCategoryVo currOutVo = new MoldCategoryVo
{
MoldCategoryId = Convert.ToInt32(dataReader["mold_category_id"]),
MoldCategoryCode = dataReader["mold_category_cd"].ToString(),
};
outVo.add(currOutVo);
}
dataReader.Close();
return(outVo);
}
/// <summary>
/// updates Line Machine
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Update_btn_Click(object sender, EventArgs e)
{
if (MoldItem_dgv.RowCount > 0)
{
}
ValueObjectList <MoldItemVo> inVo = new ValueObjectList <MoldItemVo>();
foreach (DataGridViewRow row in MoldItem_dgv.Rows)
{
if (row.Cells["colSelect"].Value.ToString() == "True")
{
MoldItemVo selectedItem = (MoldItemVo)row.DataBoundItem;
MoldItemVo addVo = new MoldItemVo();
addVo.MoldId = selectedItem.MoldId;
addVo.StdCycleTime = selectedItem.StdCycleTime;
addVo.GlobalItemId = selectedItem.GlobalItemId;
inVo.add(addVo);
}
}
UpdateResultVo outVo = null;
try
{
Cursor.Current = Cursors.WaitCursor;
outVo = (UpdateResultVo)base.InvokeCbm(new UpdateModelItemForMultipleItemCbm(), inVo, false);
Cursor.Current = Cursors.Default;
}
catch (Framework.ApplicationException exception)
{
Cursor.Current = Cursors.Default;
popUpMessage.ApplicationError(exception.GetMessageData(), Text);
logger.Error(exception.GetMessageData());
}
if (outVo == null)
{
return;
}
messageData = new MessageData("mmci00002", Properties.Resources.mmci00002, null);
logger.Info(messageData);
popUpMessage.Information(messageData, Text);
GridBind(FormConditionVo());
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
DocumentVo inVo = (DocumentVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <DocumentVo> voList = new ValueObjectList <DocumentVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"SELECT document_name from t_document where 1 = 1");
//sqlParameter.AddParameterString("department", inVo.Department);
if (!String.IsNullOrEmpty(inVo.Department))
{
sql.Append(" and department =:department ");
sqlParameter.AddParameterString("department", inVo.Department);
}
if (!String.IsNullOrEmpty(inVo.Group))
{
sql.Append(" and groups =:groups ");
sqlParameter.AddParameterString("groups", inVo.Group);
}
if (!String.IsNullOrEmpty(inVo.DocumentCode))
{
sql.Append(" and document_cd =:document_cd ");
sqlParameter.AddParameterString("document_cd", inVo.DocumentCode);
}
sql.Append(" and model_id =:model_id ");
sqlParameter.AddParameterInteger("model_id", inVo.ModelID);
sql.Append(" order by document_name");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
DocumentVo outVo = new DocumentVo
{
DocumentName = dataReader["document_name"].ToString(),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg)
{
ProcessWorksVo inVo = (ProcessWorksVo)arg;
StringBuilder sqlQuery = new StringBuilder();
ValueObjectList <ProcessWorksVo> voList = new ValueObjectList <ProcessWorksVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sqlQuery.Append("Select pw.process_work_id, pw.process_work_cd, pw.process_work_name, md.model_cd, pr.process_name, mc.machine_name from m_process_work pw left join m_process pr on pr.process_id = pw.process_id left join m_machine mc on mc.machine_id = pw.machine_id left join m_model md on md.model_id = pw.model_id where 1 = 1");
if (!String.IsNullOrEmpty(inVo.ProcessWorkCode))
{
sqlQuery.Append(" and pw.process_work_cd like :processworkcd ");
sqlParameter.AddParameterString("processworkcd", "%" + inVo.ProcessWorkCode + "%");
}
if (!String.IsNullOrEmpty(inVo.ProcessWorkName))
{
sqlQuery.Append(" and pw.process_work_name like :processworkname ");
sqlParameter.AddParameterString("processworkname", "%" + inVo.ProcessWorkName + "%");
}
if (inVo.MachineID != 0)
{
sqlQuery.Append(" and mc.machine_id = :machine_id ");
sqlParameter.AddParameterInteger("machine_id", inVo.MachineID);
}
sqlQuery.Append(" order by pw.process_work_id");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProcessWorksVo OutVo = new ProcessWorksVo();
{
OutVo.ProcessWorkId = ConvertDBNull <int>(dataReader, "process_work_id");
OutVo.ProcessWorkCode = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_work_cd"]);
OutVo.ProcessWorkName = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_work_name"]);
OutVo.Assy = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_name"]);
OutVo.Model = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["model_cd"]);
OutVo.Machine = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["machine_name"]);
}
voList.add(OutVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
MachineAndEquipmentVo inVo = (MachineAndEquipmentVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <MachineAndEquipmentVo> voList = new ValueObjectList <MachineAndEquipmentVo>();
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, string.Empty);
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select machine_equip_id, machine_id, equipment_id from public.m_machine_equipment");
sql.Append(" Where 1=1 ");
//
if (inVo.MachineID > 0)
{
sql.Append(" and machine_id = :machine_id ");
sqlParameter.AddParameterInteger("machine_id", inVo.MachineID);
}
if (inVo.EquipmentID > 0)
{
sql.Append(" and equipment_id = :equipment_id ");
sqlParameter.AddParameterInteger("equipment_id", inVo.EquipmentID);
}
if (inVo.MachineAndEquipmentID > 0)
{
sql.Append(" and machine_equip_id = :machine_equip_id ");
sqlParameter.AddParameterInteger("machine_equip_id", inVo.MachineAndEquipmentID);
}
//create command
//DbCommandAdaptor
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
MachineAndEquipmentVo outVo = new MachineAndEquipmentVo
{
//convert
MachineAndEquipmentID = int.Parse(dataReader["machine_equip_id"].ToString()),
MachineID = int.Parse(dataReader["machine_id"].ToString()),
EquipmentID = int.Parse(dataReader["equipment_id"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select a.process,case when sum(inspectdata) = null then 0 else sum(inspectdata) end inspectdata from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where ");
sql.Append(@" a.inspectdate >= :datefrom and a.inspectdate <= :dateto and inspectdata != 0 and b.inspectdate >= :datefrom and b.inspectdate <= :dateto and a.inspectdate = b.inspectdate ");
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
if (!string.IsNullOrEmpty(inVo.LineCode))
{
sql.Append(@" and a.line =:line");
sqlParameter.AddParameterString("line", inVo.LineCode);
}
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(@" and a.model =:model ");
sqlParameter.AddParameterString("model", inVo.ModelCode);
}
sql.Append("group by a.process order by a.process");
//if (!string.IsNullOrEmpty(inVo.ProcessName))
//{
// sql.Append(@" and a.process =:process");
// sqlParameter.AddParameterString("process", inVo.ProcessName);
//}
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
{
ProcessName = dataReader["process"].ToString(),
InspecData = dataReader["inspectdata"].ToString()
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select case when max(output_data) is null then 0 else max(output_data) end output from t_productioncontroller_output04 ");
sql.Append(" where 1=1 ");
sql.Append(@" and dates =:dates");
sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
if (!String.IsNullOrEmpty(inVo.ProModel))
{
sql.Append(" and model_cd =:model_cd");
sqlParameter.AddParameterString("model_cd", inVo.ProModel);
}
if (!String.IsNullOrEmpty(inVo.ProLine))
{
sql.Append(" and line_cd =:line_cd");
sqlParameter.AddParameterString("line_cd", inVo.ProLine);
}
//sql.Append(" order by datetimes");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
// , h., i., k., o.prodution_work_content_name
ProOutput = int.Parse(dataReader["output"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerVo inVo = (ProductionControllerVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerVo> voList = new ValueObjectList <ProductionControllerVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select Case When times between '06:00:00' and '23:59:00' then dates when ");
sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss,'LA459' model_cd,'All Line' line_cd, sum(insc_no_ink_case_mc1) insc_no_ink_case_mc1, ");
sql.Append("sum(insc_ba_deform_mc1) insc_ba_deform_mc1, sum(insc_break_case_mc1) insc_break_case_mc1 ,");
sql.Append("sum(insc_drop_mc1) insc_drop_mc1, sum(insc_break_wire_mc1) insc_break_wire_mc1, ");
sql.Append("sum(insc_break_ring_mc1) insc_break_ring_mc1 from ");
sql.Append("(select i2.dates,i2.times,i2.line_cd, insc_no_ink_case_mc1, insc_ba_deform_mc1, insc_break_case_mc1, insc_drop_mc1, insc_break_wire_mc1, insc_break_ring_mc1 from t_productioncontroller_output01 i2 left join (select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id from(select tblca1.dates, tblca1.line_cd, idca1, idca3 from(select line_cd, o.dates, max(o.output01_id) idca1 from t_productioncontroller_output01 o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 left join(select line_cd, (o.dates - 1) dates, max(o.output01_id) idca3 from t_productioncontroller_output01 o where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl order by dates, line_cd) l on l.line_cd = i2.line_cd where i2.output01_id = l.id order by i2.dates,i2.line_cd ) t group by datesss order by datesss");
sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
//sqlParameter.AddParameterString("model_cd", inVo.ProModel);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerVo outVo = new ProductionControllerVo
{
//StartDay = DateTime.Parse(dataReader["dates"].ToString()),
TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
ProModel = dataReader["model_cd"].ToString(),
ProLine = dataReader["line_cd"].ToString(),
Insc_no_ink_case_mc1 = int.Parse(dataReader["insc_no_ink_case_mc1"].ToString()),
Insc_ba_deform_mc1 = int.Parse(dataReader["insc_ba_deform_mc1"].ToString()),
Insc_break_case_mc1 = int.Parse(dataReader["insc_break_case_mc1"].ToString()),
Insc_drop_mc1 = int.Parse(dataReader["insc_drop_mc1"].ToString()),
Insc_break_wire_mc1 = int.Parse(dataReader["insc_break_wire_mc1"].ToString()),
Insc_break_ring_mc1 = int.Parse(dataReader["insc_break_ring_mc1"].ToString()),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append("select count (*) datas from (");
sql.Append("select distinct barcode from t_noisecheck_a90 ");
sql.Append("where date_check >= :datefrom and date_check <= :dateto ");
if (!string.IsNullOrEmpty(inVo.ModelCode))
{
sql.Append(@" and model =:model");
sqlParameter.AddParameterString("model", inVo.ModelCode);
}
if (inVo.change)//search theo line
{
sql.Append(@" and line =:line");
sqlParameter.AddParameterString("line", inVo.LineCode);
}
else//search tat ca line
{
//de cho vui thui @@
}
sql.Append(@" ) tbl");
sqlParameter.AddParameter("datefrom", inVo.DateFrom);
sqlParameter.AddParameter("dateto", inVo.DateTo);
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
{
InspecData = dataReader["datas"].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 tam.account_code_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 g.unit,a.account_code_name, g.warehouse_main_id,j.location_cd,
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, 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_location j on c.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) tam group by tam.account_code_name ");
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)
{
TransferVo inVo = (TransferVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <TransferVo> voList = new ValueObjectList <TransferVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select a.asset_id, a.asset_cd, a.asset_name, a.asset_no from t_warehouse_main b
left join m_asset a on a.asset_id = b.asset_id
left join m_location c on c.location_id = b.before_location_id
where 1 = 1");
if (!String.IsNullOrEmpty(inVo.LocationCd))
{
sql.Append(@" and c.location_cd =:location_cd");
sqlParameter.AddParameterString("location_cd", inVo.LocationCd);
}
if (!String.IsNullOrEmpty(inVo.AssetCode))
{
sql.Append(@" and a.asset_cd = :asset_cd");
sqlParameter.AddParameterString("asset_cd", inVo.AssetCode);
}
sql.Append(" order by a.asset_cd");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
TransferVo outVo = new TransferVo
{
AssetCode = dataReader["asset_cd"].ToString(),
AssetName = dataReader["asset_name"].ToString(),
AssetNo = int.Parse(dataReader["asset_no"].ToString()),
AssetID = int.Parse(dataReader["asset_id"].ToString())
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
RangeVo inVo = (RangeVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <RangeVo> voList = new ValueObjectList <RangeVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select * from m_ovenmachine_range where 1=1 ");
if (!String.IsNullOrEmpty(inVo.Model))
{
sql.Append(@" and range_model = :range_model ");
sqlParameter.AddParameterString("range_model", inVo.Model);
}
if (!String.IsNullOrEmpty(inVo.Line))
{
sql.Append(@" and range_line = :range_line ");
sqlParameter.AddParameterString("range_line", inVo.Line);
}
sql.Append(" order by range_model,range_line,range_barcode ");
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
RangeVo outVo = new RangeVo
{
RangeId = int.Parse(dataReader["ovenmachine_range_id"].ToString()),
Model = dataReader["range_model"].ToString(),
Line = dataReader["range_line"].ToString(),
Barcode = dataReader["range_barcode"].ToString(),
Lower = dataReader["range_lower"].ToString(),
Upper = dataReader["range_upper"].ToString(),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
{
WarehouseVTListVo inVo = (WarehouseVTListVo)vo;
StringBuilder sql = new StringBuilder();
ValueObjectList <WarehouseVTListVo> voList = new ValueObjectList <WarehouseVTListVo>();
//create command
DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//create parameter
DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();
sql.Append(@"select count(*) as counter from t_vt_list_check b
left join t_vt_machine a on a.machine_serial = b.machine_serial where 1=1 ");
if (!String.IsNullOrEmpty(inVo.MachineSerial))
{
sql.Append(@" and b.machine_serial =:machine_serial");
sqlParameter.AddParameterString("machine_serial", inVo.MachineSerial);
}
if (!String.IsNullOrEmpty(inVo.CheckTime.ToString()))
{
sql.Append(@" and b.check_time =:check_time");
sqlParameter.AddParameterInteger("check_time", inVo.CheckTime);
}
if (!String.IsNullOrEmpty(inVo.RFId))
{
sql.Append(@" and a.rfid_cd =:rfid_cd");
sqlParameter.AddParameterString("rfid_cd", inVo.RFId);
}
sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
//execute SQL
IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);
while (dataReader.Read())
{
WarehouseVTListVo outVo = new WarehouseVTListVo
{
CounterRow = dataReader["counter"].ToString(),
};
voList.add(outVo);
}
dataReader.Close();
return(voList);
}