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); }