Пример #1
0
        private void GridBindHour()
        {
            production_controller_dgv.DataSource = null;
            try
            {
                ProductionControllerNCVCVo vo = new ProductionControllerNCVCVo
                {
                    ProLine  = line_cmb.Text,
                    Date     = timefrom_dtp.Text,
                    ProModel = model_cmb.Text,
                };

                ValueObjectList <ProductionControllerNCVCVo> volist = (ValueObjectList <ProductionControllerNCVCVo>)DefaultCbmInvoker.Invoke(new SearchMainProByHourNCVCCbm(), vo);
                if (volist.GetList() != null && volist.GetList().Count > 0)
                {
                    production_controller_dgv.AutoGenerateColumns = false;
                    BindingSource bindingsource = new BindingSource(volist.GetList(), null);
                    production_controller_dgv.DataSource = bindingsource;
                }
                else
                {
                    messageData = new MessageData("mmci00006", Properties.Resources.mmci00006, null);
                    logger.Info(messageData);
                    popUpMessage.Information(messageData, Text);
                }
                production_controller_dgv.ClearSelection();
            }
            catch (Framework.ApplicationException exception)
            {
                popUpMessage.ApplicationError(exception.GetMessageData(), Text);
                logger.Error(exception.GetMessageData());
            }
        }
Пример #2
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter

            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select distinct  line_cd from t_ncvc_pdc_fc union select 'All Line' ");
            sql.Append("order by line_cd");

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());


            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    ProLine = dataReader["line_cd"].ToString(),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Пример #3
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select tblin.datestimes, tblin.model_cd, tblin.line_cd, ( final_app + en2 + en1 + case_assy + case_bonding + case_mg + trust_gap + rotor + bracket_assy + bracket_metal) as sum_ng,ca_input_line,ca_input, ba_input, fc_input,output, final_app, en2, en1, case_assy, case_bonding, case_mg, trust_gap, rotor, bracket_assy, bracket_metal from 
(select(fc.dates + fc.times) datestimes, fc.model_cd, fc.line_cd, ca_input_line,ca_input, ba_input, fc_input, output, (fc_endplay_small + fc_endplay_big + fc_shaft_scracth + fc_terminal_low + fc_case_scracth_dirty + fc_pinion_worm_ng + fc_shaft_lock + fc_ba_deform + fc_tape_hole_deform + fc_brush_rust + fc_metal_deform_scracth + fc_washer_tape_hole) final_app, (en2_insulation_resistance_ng + en2_cut_coil_wire + en2_no_load_current_hight + en2_ripple + en2_chattering + en2_lock + en2_open + en2_no_load_speed_low + en2_starting_voltage + en2_no_load_speed_high + en2_rotor_mix + en2_surge_volt_max + en2_wrong_post_of_pole + en2_err + en2_noise) en2, (en1_insulation_resistace_ng + en1_cut_coil_wire + en1_lock + en1_wareform_ma_abnormal + en1_shaft_bent + en1_ripple + en1_short + en1_chattering + en1_no_load_current_high + en1_vibration_ng + en1_open + en1_rotor_mix) en1, (ca_app_metal_dirty + ca_app_tape_hole_deform + ca_app_metal_high + ca_app_case_deform_scracth + ca_app_metal_deform_scratch + ca_app_magnet_broken) as case_assy, (ca_mg_metal_deform_scratch + ca_mg_case_deform_scratch) as case_mg, (ca_bonding_metal_deform_scratch + ca_bonding_case_deform_scracth) case_bonding, (ba_tc_endplay_big + ba_tc_endplay_small + ba_tc_brush_bent + ba_tc_shaft_mix) as trust_gap, (ba_rto_ng + ba_rto_mix) as rotor, (ba_app_metal_deform_scracth + ba_app_ba_deform + ba_app_endplate_deform_scracth + ba_app_error_other) as bracket_assy, (ba_bm_brush_deform_scracth + ba_bm_metal_deform_scracth + ba_bm_ba_deform + ba_bm_endplay_deform_scracth) as bracket_metal
from t_ncvc_pdc_fc fc left join t_ncvc_pdc_en2 e2 on fc.fc_id = e2.en2_id and fc.line_cd = e2.line_cd and fc.dates = e2.dates
left join t_ncvc_pdc_en1 e1 on fc.fc_id = e1.en1_id and fc.line_cd = e1.line_cd
left join t_ncvc_pdc_ca ca on fc.fc_id = ca.ca_id and fc.line_cd = ca.line_cd
left join t_ncvc_pdc_ba ba on fc.fc_id = ba.ba_id and fc.line_cd = ba.line_cd
where fc.line_cd = :line_cd and fc.dates = :dates and(fc.times in(select min(times) from t_ncvc_pdc_fc where times between '06:00:00' and '06:55:00'  and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) or fc.dates - 1 = :dates and fc.line_cd = :line_cd and fc.line_cd = :line_cd and(fc.times in(select min(times) from t_ncvc_pdc_fc where times between '00:00:00' and '00:55:00' and dates - 1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '05:00:00' and '05:56:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd))) tblin");

            sqlParameter.AddParameterString("line_cd", inVo.ProLine);
            sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
            sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    TimeHour        = DateTime.Parse(dataReader["datestimes"].ToString()),
                    ProModel        = dataReader["model_cd"].ToString(),
                    ProLine         = dataReader["line_cd"].ToString(),
                    TotalNG         = int.Parse(dataReader["sum_ng"].ToString()),
                    ProInput        = int.Parse(dataReader["ca_input_line"].ToString()),
                    ProInputCase    = int.Parse(dataReader["ca_input"].ToString()),
                    ProInputBracket = int.Parse(dataReader["ba_input"].ToString()),
                    ProInputApp     = int.Parse(dataReader["fc_input"].ToString()),
                    ProOutput       = int.Parse(dataReader["output"].ToString()),

                    Final_App   = int.Parse(dataReader["final_app"].ToString()),
                    En2NG       = int.Parse(dataReader["en2"].ToString()),
                    Case_Assy   = int.Parse(dataReader["case_assy"].ToString()),
                    En1NG       = int.Parse(dataReader["en1"].ToString()),
                    MG_Bongding = int.Parse(dataReader["case_bonding"].ToString()),
                    Case_MG     = int.Parse(dataReader["case_mg"].ToString()),

                    TrustGap      = int.Parse(dataReader["trust_gap"].ToString()),
                    Rotor         = int.Parse(dataReader["rotor"].ToString()),
                    Braket        = int.Parse(dataReader["bracket_assy"].ToString()),
                    Bracket_Metal = int.Parse(dataReader["bracket_metal"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Пример #4
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select Case When times between '06:00:00' and '23:59:00' then dates when ");
            sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss, model_cd,'All Line' line_cd, sum(en1_insulation_resistace_ng) en1_insulation_resistace_ng, ");
            sql.Append("sum(en1_cut_coil_wire) en1_cut_coil_wire, sum(en1_lock) en1_lock ,");
            sql.Append("sum(en1_wareform_ma_abnormal) en1_wareform_ma_abnormal, sum(en1_shaft_bent) en1_shaft_bent, ");
            sql.Append("sum(en1_ripple) en1_ripple, sum(en1_short) en1_short,  ");
            sql.Append("sum(en1_chattering) en1_chattering, sum(en1_no_load_current_high) en1_no_load_current_high, ");
            sql.Append("sum(en1_vibration_ng) en1_vibration_ng, sum(en1_open) en1_open, sum(en1_rotor_mix) en1_rotor_mix from ");

            sql.Append("(select i2.dates,i2.times,i2.model_cd,i2.line_cd, en1_insulation_resistace_ng, en1_cut_coil_wire, en1_lock,en1_wareform_ma_abnormal, en1_shaft_bent,  en1_ripple,  en1_short, en1_chattering, en1_no_load_current_high, en1_vibration_ng, en1_open,  en1_rotor_mix from t_ncvc_pdc_en1 i2 left join (select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id  from(select tblca1.dates, tblca1.line_cd, idca1, idca3  from(select line_cd, o.dates, max(o.en1_id) idca1  from t_ncvc_pdc_en1 o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 left join(select line_cd, (o.dates - 1) dates, max(o.en1_id) idca3  from t_ncvc_pdc_en1 o  where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl  order by dates, line_cd) l on l.line_cd = i2.line_cd  where i2.en1_id = l.id order by i2.dates,i2.line_cd ) t where model_cd = :model_cd group by datesss,model_cd order by datesss");


            sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
            sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
            sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    //StartDay = DateTime.Parse(dataReader["dates"].ToString()),
                    TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
                    ProModel = dataReader["model_cd"].ToString(),
                    ProLine  = dataReader["line_cd"].ToString(),

                    En1_insulation_resistace_ng = int.Parse(dataReader["en1_insulation_resistace_ng"].ToString()),
                    En1_cut_coil_wire           = int.Parse(dataReader["en1_cut_coil_wire"].ToString()),
                    En1_lock = int.Parse(dataReader["en1_lock"].ToString()),
                    En1_wareform_ma_abnormal = int.Parse(dataReader["en1_wareform_ma_abnormal"].ToString()),
                    En1_shaft_bent           = int.Parse(dataReader["en1_shaft_bent"].ToString()),
                    En1_ripple               = int.Parse(dataReader["en1_ripple"].ToString()),
                    En1_short                = int.Parse(dataReader["en1_short"].ToString()),
                    En1_chattering           = int.Parse(dataReader["en1_chattering"].ToString()),
                    En1_vibration_ng         = int.Parse(dataReader["en1_vibration_ng"].ToString()),
                    En1_open                 = int.Parse(dataReader["en1_open"].ToString()),
                    En1_rotor_mix            = int.Parse(dataReader["en1_rotor_mix"].ToString()),
                    En1_no_load_current_high = int.Parse(dataReader["en1_no_load_current_high"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select Case When times between '06:00:00' and '23:59:00' then dates when ");
            sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss, model_cd,'All Line' line_cd, sum(fc_endplay_small) fc_endplay_small, ");
            sql.Append("sum(fc_endplay_big) fc_endplay_big, sum(fc_shaft_scracth) fc_shaft_scracth ,");
            sql.Append("sum(fc_terminal_low) fc_terminal_low, sum(fc_case_scracth_dirty) fc_case_scracth_dirty, ");
            sql.Append("sum(fc_pinion_worm_ng) fc_pinion_worm_ng,  ");
            sql.Append("sum(fc_shaft_lock) fc_shaft_lock, sum(fc_ba_deform) fc_ba_deform, ");
            sql.Append("sum(fc_tape_hole_deform) fc_tape_hole_deform, sum(fc_brush_rust) fc_brush_rust, ");
            sql.Append("sum(fc_metal_deform_scracth) fc_metal_deform_scracth, sum(fc_washer_tape_hole ) fc_washer_tape_hole from ");
            sql.Append("(select i2.dates,i2.times,i2.model_cd,i2.line_cd, fc_endplay_small, fc_endplay_big, fc_shaft_scracth, fc_terminal_low, fc_case_scracth_dirty, fc_pinion_worm_ng,  fc_shaft_lock, fc_ba_deform, fc_tape_hole_deform, fc_brush_rust, fc_metal_deform_scracth, fc_washer_tape_hole  from t_ncvc_pdc_fc i2 left join (select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id  from(select tblca1.dates, tblca1.line_cd, idca1, idca3  from(select line_cd, o.dates, max(o.fc_id) idca1  from t_ncvc_pdc_fc o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 left join(select line_cd, (o.dates - 1) dates, max(o.fc_id) idca3  from t_ncvc_pdc_fc o  where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl  order by dates, line_cd) l on l.line_cd = i2.line_cd  where i2.fc_id = l.id order by i2.dates,i2.line_cd ) t where model_cd= :model_cd group by datesss,model_cd order by datesss");


            sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
            sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
            sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
                    ProModel = dataReader["model_cd"].ToString(),
                    ProLine  = dataReader["line_cd"].ToString(),

                    FC_endplay_small        = int.Parse(dataReader["fc_endplay_small"].ToString()),
                    FC_endplay_big          = int.Parse(dataReader["fc_endplay_big"].ToString()),
                    FC_shaft_scracth        = int.Parse(dataReader["fc_shaft_scracth"].ToString()),
                    FC_terminal_low         = int.Parse(dataReader["fc_terminal_low"].ToString()),
                    FC_case_scracth_dirty   = int.Parse(dataReader["fc_case_scracth_dirty"].ToString()),
                    FC_pinion_worm_ng       = int.Parse(dataReader["fc_pinion_worm_ng"].ToString()),
                    FC_shaft_lock           = int.Parse(dataReader["fc_shaft_lock"].ToString()),
                    FC_deform               = int.Parse(dataReader["fc_ba_deform"].ToString()),
                    FC_tape_hole_deform     = int.Parse(dataReader["fc_tape_hole_deform"].ToString()),
                    FC_brush_rust           = int.Parse(dataReader["fc_brush_rust"].ToString()),
                    FC_metal_deform_scracth = int.Parse(dataReader["fc_metal_deform_scracth"].ToString()),
                    FC_washer_tape_hole     = int.Parse(dataReader["fc_washer_tape_hole"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Пример #6
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select Case When times between '06:00:00' and '23:59:00' then dates when ");
            sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss,model_cd,'All Line' line_cd, sum(ca_app_metal_dirty) ca_app_metal_dirty, sum(ca_app_tape_hole_deform) ca_app_tape_hole_deform, sum(ca_app_metal_high) ca_app_metal_high, sum(ca_app_case_deform_scracth) ca_app_case_deform_scracth, sum(ca_app_metal_deform_scratch) ca_app_metal_deform_scratch, sum(ca_app_magnet_broken) ca_app_magnet_broken from ");

            sql.Append("(select i2.dates,i2.times,i2.model_cd,i2.line_cd, ca_app_metal_dirty, ca_app_tape_hole_deform, ca_app_metal_high, ca_app_case_deform_scracth, ca_app_metal_deform_scratch, ca_app_magnet_broken from t_ncvc_pdc_ca i2 left join (select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id  from(select tblca1.dates, tblca1.line_cd, idca1, idca3  from(select line_cd, o.dates, max(o.ca_id) idca1  from t_ncvc_pdc_ca o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 left join(select line_cd, (o.dates - 1) dates, max(o.ca_id) idca3  from t_ncvc_pdc_ca o  where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl  order by dates, line_cd) l on l.line_cd = i2.line_cd  where i2.ca_id = l.id order by i2.dates,i2.line_cd ) t where model_cd = :model_cd group by datesss,model_cd order by datesss");


            sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
            sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
            sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    //StartDay = DateTime.Parse(dataReader["dates"].ToString()),
                    TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
                    ProModel = dataReader["model_cd"].ToString(),
                    ProLine  = dataReader["line_cd"].ToString(),

                    CA_app_metal_dirty          = int.Parse(dataReader["ca_app_metal_dirty"].ToString()),
                    CA_app_tape_hole_deform     = int.Parse(dataReader["ca_app_tape_hole_deform"].ToString()),
                    CA_app_metal_high           = int.Parse(dataReader["ca_app_metal_high"].ToString()),
                    CA_app_case_deform_scracth  = int.Parse(dataReader["ca_app_case_deform_scracth"].ToString()),
                    CA_app_metal_deform_scratch = int.Parse(dataReader["ca_app_metal_deform_scratch"].ToString()),
                    CA_app_magnet_broken        = int.Parse(dataReader["ca_app_magnet_broken"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select Case When times between '06:00:00' and '23:59:00' then dates when ");
            sql.Append("times between '00:00:00' and '05:59:00' then dates+1 end datesss, model_cd,'All Line' line_cd, sum(ba_tc_endplay_big) ba_tc_endplay_big, ");
            sql.Append("sum(ba_tc_endplay_small) ba_tc_endplay_small, sum(ba_tc_brush_bent) ba_tc_brush_bent ,");
            sql.Append("sum(ba_tc_shaft_mix) ba_tc_shaft_mix from ");

            sql.Append("(select i2.dates,i2.times,i2.model_cd,i2.line_cd, ba_tc_endplay_big, ba_tc_endplay_small, ba_tc_brush_bent, ba_tc_shaft_mix from t_ncvc_pdc_ba i2 left join (select dates, line_cd, Case when idca3 is null then idca1 else idca3 end id  from(select tblca1.dates, tblca1.line_cd, idca1, idca3  from(select line_cd, o.dates, max(o.ba_id) idca1  from t_ncvc_pdc_ba o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto group by o.dates, line_cd order by dates) tblca1 left join(select line_cd, (o.dates - 1) dates, max(o.ba_id) idca3  from t_ncvc_pdc_ba o  where o.times > '00:00:00' and o.times <= '05:30:00' and o.dates > :datefrom and o.dates - 1 <= :dateto group by line_cd, o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl  order by dates, line_cd) l on l.line_cd = i2.line_cd  where i2.ba_id = l.id order by i2.dates,i2.line_cd ) t where model_cd = :model_cd group by datesss,model_cd order by datesss");


            sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
            sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
            sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
                    ProModel = dataReader["model_cd"].ToString(),
                    ProLine  = dataReader["line_cd"].ToString(),

                    BA_tc_endplay_big   = int.Parse(dataReader["ba_tc_endplay_big"].ToString()),
                    BA_tc_endplay_small = int.Parse(dataReader["ba_tc_endplay_small"].ToString()),
                    BA_tc_brush_bent    = int.Parse(dataReader["ba_tc_brush_bent"].ToString()),
                    BA_tc_shaft_mix     = int.Parse(dataReader["ba_tc_shaft_mix"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Пример #8
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select (fc.dates+fc.times) datetimes, fc.model_cd,fc.line_cd, fc.process_cd, ");
            sql.Append("fc_endplay_small, fc_endplay_big, fc_shaft_scracth, fc_terminal_low, fc_case_scracth_dirty, fc_pinion_worm_ng, fc_shaft_lock, fc_ba_deform, fc_tape_hole_deform, fc_brush_rust, fc_metal_deform_scracth,  fc_washer_tape_hole ");
            sql.Append("from t_ncvc_pdc_fc fc ");
            sql.Append("where fc.line_cd = :line_cd ");
            sql.Append("and fc.dates = :dates ");
            sql.Append("and (fc.times in(select min(times) from t_ncvc_pdc_fc where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");

            sql.Append("or fc.dates-1 =:dates and fc.line_cd = :line_cd ");
            sql.Append("and (fc.times in(select min(times) from t_ncvc_pdc_fc where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or fc.times in(select max(times) from t_ncvc_pdc_fc where times between '05:00:00' and '05:35:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");


            sqlParameter.AddParameterString("line_cd", inVo.ProLine);
            sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
            sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
                    ProModel = dataReader["model_cd"].ToString(),
                    ProLine  = dataReader["line_cd"].ToString(),

                    FC_endplay_small        = int.Parse(dataReader["fc_endplay_small"].ToString()),
                    FC_endplay_big          = int.Parse(dataReader["fc_endplay_big"].ToString()),
                    FC_shaft_scracth        = int.Parse(dataReader["fc_shaft_scracth"].ToString()),
                    FC_terminal_low         = int.Parse(dataReader["fc_terminal_low"].ToString()),
                    FC_case_scracth_dirty   = int.Parse(dataReader["fc_case_scracth_dirty"].ToString()),
                    FC_pinion_worm_ng       = int.Parse(dataReader["fc_pinion_worm_ng"].ToString()),
                    FC_shaft_lock           = int.Parse(dataReader["fc_shaft_lock"].ToString()),
                    FC_deform               = int.Parse(dataReader["fc_ba_deform"].ToString()),
                    FC_tape_hole_deform     = int.Parse(dataReader["fc_tape_hole_deform"].ToString()),
                    FC_brush_rust           = int.Parse(dataReader["fc_brush_rust"].ToString()),
                    FC_metal_deform_scracth = int.Parse(dataReader["fc_metal_deform_scracth"].ToString()),
                    FC_washer_tape_hole     = int.Parse(dataReader["fc_washer_tape_hole"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();


            sql.Append("select Case When fc.times between '06:00:00' and '23:59:00' then fc.dates when fc.times between '00:00:00' and '05:59:00' then fc.dates-1 end datesss,fc.line_cd l, fc.model_cd model, *from t_ncvc_pdc_fc fc left join t_ncvc_pdc_en2 e2 on fc.fc_id = e2.en2_id left                                   join t_ncvc_pdc_en1 e1 on fc.fc_id = e1.en1_id left join t_ncvc_pdc_ca ca on fc.fc_id = ca.ca_id left join t_ncvc_pdc_ba ba on fc.fc_id = ba.ba_id left                                   join (select dates date1, line_cd line1, Case when idca3 is null then idca1 else             idca3 end id  from    (select tblca1.dates, tblca1.line_cd, idca1, idca3  from (select line_cd, o.dates, max(o.fc_id) idca1  from t_ncvc_pdc_fc o where o.times > '06:00:00'             and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :dateto             group by o.dates, line_cd order by dates) tblca1    left join(select line_cd, (o.dates - 1) dates, max(o.fc_id) idca3 from t_ncvc_pdc_fc o             where o.times > '00:00:00' and o.times <= '05:55:00'             and o.dates > :datefrom and o.dates - 1 <= :dateto             group by line_cd, o.dates order by idca3) tblca3             on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl             order by dates, line_cd) l on l.line1 = fc.line_cd where fc.fc_id = l.id and l.line1 = e2.line_cd and l.line1 = e1.line_cd             and l.line1 = ca.line_cd and fc.line_cd = :line_cd order by fc.dates, fc.line_cd");

            sqlParameter.AddParameterDateTime("datefrom", DateTime.Parse(inVo.DateFrom));
            sqlParameter.AddParameterDateTime("dateto", DateTime.Parse(inVo.DateTo));
            sqlParameter.AddParameterString("line_cd", inVo.ProLine);
            //sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    //StartDay = DateTime.Parse(dataReader["dates"].ToString()),
                    TimeHour = DateTime.Parse(dataReader["datesss"].ToString()),
                    ProModel = dataReader["model"].ToString(),
                    ProLine  = dataReader["l"].ToString(),

                    FC_endplay_small        = int.Parse(dataReader["fc_endplay_small"].ToString()),
                    FC_endplay_big          = int.Parse(dataReader["fc_endplay_big"].ToString()),
                    FC_shaft_scracth        = int.Parse(dataReader["fc_shaft_scracth"].ToString()),
                    FC_terminal_low         = int.Parse(dataReader["fc_terminal_low"].ToString()),
                    FC_case_scracth_dirty   = int.Parse(dataReader["fc_case_scracth_dirty"].ToString()),
                    FC_pinion_worm_ng       = int.Parse(dataReader["fc_pinion_worm_ng"].ToString()),
                    FC_shaft_lock           = int.Parse(dataReader["fc_shaft_lock"].ToString()),
                    FC_deform               = int.Parse(dataReader["fc_ba_deform"].ToString()),
                    FC_tape_hole_deform     = int.Parse(dataReader["fc_tape_hole_deform"].ToString()),
                    FC_brush_rust           = int.Parse(dataReader["fc_brush_rust"].ToString()),
                    FC_metal_deform_scracth = int.Parse(dataReader["fc_metal_deform_scracth"].ToString()),
                    FC_washer_tape_hole     = int.Parse(dataReader["fc_washer_tape_hole"].ToString()),

                    En2_insulation_resistance_ng = int.Parse(dataReader["en2_insulation_resistance_ng"].ToString()),
                    En2_cut_coil_wire            = int.Parse(dataReader["en2_cut_coil_wire"].ToString()),
                    En2_no_load_current_hight    = int.Parse(dataReader["en2_no_load_current_hight"].ToString()),
                    En2_ripple             = int.Parse(dataReader["en2_ripple"].ToString()),
                    En2_chattering         = int.Parse(dataReader["en2_chattering"].ToString()),
                    En2_lock               = int.Parse(dataReader["en2_lock"].ToString()),
                    En2_open               = int.Parse(dataReader["en2_open"].ToString()),
                    En2_no_load_speed_low  = int.Parse(dataReader["en2_no_load_speed_low"].ToString()),
                    En2_starting_voltage   = int.Parse(dataReader["en2_starting_voltage"].ToString()),
                    En2_no_load_speed_high = int.Parse(dataReader["en2_no_load_speed_high"].ToString()),
                    En2_rotor_mix          = int.Parse(dataReader["en2_rotor_mix"].ToString()),
                    En2_surge_volt_max     = int.Parse(dataReader["en2_surge_volt_max"].ToString()),
                    En2_wrong_post_of_pole = int.Parse(dataReader["en2_wrong_post_of_pole"].ToString()),
                    En2_err   = int.Parse(dataReader["en2_err"].ToString()),
                    En2_noise = int.Parse(dataReader["en2_noise"].ToString()),

                    En1_insulation_resistace_ng = int.Parse(dataReader["en1_insulation_resistace_ng"].ToString()),
                    En1_cut_coil_wire           = int.Parse(dataReader["en1_cut_coil_wire"].ToString()),
                    En1_lock = int.Parse(dataReader["en1_lock"].ToString()),
                    En1_wareform_ma_abnormal = int.Parse(dataReader["en1_wareform_ma_abnormal"].ToString()),
                    En1_shaft_bent           = int.Parse(dataReader["en1_shaft_bent"].ToString()),
                    En1_ripple               = int.Parse(dataReader["en1_ripple"].ToString()),
                    En1_short                = int.Parse(dataReader["en1_short"].ToString()),
                    En1_chattering           = int.Parse(dataReader["en1_chattering"].ToString()),
                    En1_no_load_current_high = int.Parse(dataReader["en1_no_load_current_high"].ToString()),
                    En1_vibration_ng         = int.Parse(dataReader["en1_vibration_ng"].ToString()),
                    En1_open      = int.Parse(dataReader["en1_open"].ToString()),
                    En1_rotor_mix = int.Parse(dataReader["en1_rotor_mix"].ToString()),

                    BA_tc_endplay_big   = int.Parse(dataReader["ba_tc_endplay_big"].ToString()),
                    BA_tc_endplay_small = int.Parse(dataReader["ba_tc_endplay_small"].ToString()),
                    BA_tc_brush_bent    = int.Parse(dataReader["ba_tc_brush_bent"].ToString()),
                    BA_tc_shaft_mix     = int.Parse(dataReader["ba_tc_shaft_mix"].ToString()),

                    BA_rto_ng  = int.Parse(dataReader["ba_rto_ng"].ToString()),
                    BA_rto_mix = int.Parse(dataReader["ba_rto_mix"].ToString()),

                    BA_app_metal_deform_scracth = int.Parse(dataReader["ba_app_metal_deform_scracth"].ToString()),
                    BA_app_deform = int.Parse(dataReader["ba_app_ba_deform"].ToString()),
                    BA_app_endplate_deform_scracth = int.Parse(dataReader["ba_app_endplate_deform_scracth"].ToString()),
                    BA_app_error_other             = int.Parse(dataReader["ba_app_error_other"].ToString()),

                    BA_bm_brush_deform_scracth = int.Parse(dataReader["ba_bm_brush_deform_scracth"].ToString()),
                    BA_bm_metal_deform_scracth = int.Parse(dataReader["ba_bm_metal_deform_scracth"].ToString()),
                    BA_bm_deform = int.Parse(dataReader["ba_bm_ba_deform"].ToString()),
                    BA_bm_endplay_deform_scracth = int.Parse(dataReader["ba_bm_endplay_deform_scracth"].ToString()),

                    CA_app_metal_dirty          = int.Parse(dataReader["ca_app_metal_dirty"].ToString()),
                    CA_app_tape_hole_deform     = int.Parse(dataReader["ca_app_tape_hole_deform"].ToString()),
                    CA_app_metal_high           = int.Parse(dataReader["ca_app_metal_high"].ToString()),
                    CA_app_case_deform_scracth  = int.Parse(dataReader["ca_app_case_deform_scracth"].ToString()),
                    CA_app_metal_deform_scratch = int.Parse(dataReader["ca_app_metal_deform_scratch"].ToString()),
                    CA_app_magnet_broken        = int.Parse(dataReader["ca_app_magnet_broken"].ToString()),

                    CA_mg_metal_deform_scratch = int.Parse(dataReader["ca_mg_metal_deform_scratch"].ToString()),
                    CA_mg_case_deform_scratch  = int.Parse(dataReader["ca_mg_case_deform_scratch"].ToString()),

                    CA_bonding_metal_deform_scratch = int.Parse(dataReader["ca_bonding_metal_deform_scratch"].ToString()),
                    CA_bonding_case_deform_scracth  = int.Parse(dataReader["ca_bonding_case_deform_scracth"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Пример #10
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select (o4.dates+o4.times) datetimes, o4.model_cd,o4.line_cd, o4.process_cd, ba_rto_ng,ba_rto_mix  ");
            sql.Append("from t_ncvc_pdc_ba o4 ");
            sql.Append("where o4.line_cd = :line_cd ");
            sql.Append("and o4.dates = :dates ");
            sql.Append("and (o4.times in(select min(times) from t_ncvc_pdc_ba where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");

            sql.Append("or o4.dates-1 =:dates and o4.line_cd = :line_cd ");
            sql.Append("and (o4.times in(select min(times) from t_ncvc_pdc_ba where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or o4.times in(select max(times) from t_ncvc_pdc_ba where times between '05:00:00' and '05:35:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");


            sqlParameter.AddParameterString("line_cd", inVo.ProLine);
            sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
            sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    //StartDay = DateTime.Parse( dataReader["dates"].ToString()),
                    TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
                    ProModel = dataReader["model_cd"].ToString(),
                    ProLine  = dataReader["line_cd"].ToString(),

                    BA_rto_ng  = int.Parse(dataReader["ba_rto_ng"].ToString()),
                    BA_rto_mix = int.Parse(dataReader["ba_rto_mix"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Пример #11
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select (ca.dates+ca.times) datetimes, ca.model_cd,ca.line_cd, ca.process_cd, ");
            sql.Append("ca_app_metal_dirty, ca_app_tape_hole_deform, ca_app_metal_high, ca_app_case_deform_scracth, ca_app_metal_deform_scratch, ca_app_magnet_broken ");
            sql.Append("from t_ncvc_pdc_ca ca ");
            sql.Append("where ca.line_cd = :line_cd ");
            sql.Append("and ca.dates = :dates ");
            sql.Append("and (ca.times in(select min(times) from t_ncvc_pdc_ca where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");

            sql.Append("or ca.dates-1 =:dates and ca.line_cd = :line_cd ");
            sql.Append("and (ca.times in(select min(times) from t_ncvc_pdc_ca where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or ca.times in(select max(times) from t_ncvc_pdc_ca where times between '05:00:00' and '05:35:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");


            sqlParameter.AddParameterString("line_cd", inVo.ProLine);
            sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
            sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
                    ProModel = dataReader["model_cd"].ToString(),
                    ProLine  = dataReader["line_cd"].ToString(),

                    CA_app_metal_dirty          = int.Parse(dataReader["ca_app_metal_dirty"].ToString()),
                    CA_app_tape_hole_deform     = int.Parse(dataReader["ca_app_tape_hole_deform"].ToString()),
                    CA_app_metal_high           = int.Parse(dataReader["ca_app_metal_high"].ToString()),
                    CA_app_case_deform_scracth  = int.Parse(dataReader["ca_app_case_deform_scracth"].ToString()),
                    CA_app_metal_deform_scratch = int.Parse(dataReader["ca_app_metal_deform_scratch"].ToString()),
                    CA_app_magnet_broken        = int.Parse(dataReader["ca_app_magnet_broken"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select tbl.dates,f.dates+f.times endday,f.model_cd,f.line_cd,ca_input_line, ca_input, ba_input, fc_input, output,

            (fc_endplay_small + fc_endplay_big + fc_shaft_scracth + fc_terminal_low +
  fc_case_scracth_dirty + fc_pinion_worm_ng + fc_shaft_lock + fc_ba_deform +
  fc_tape_hole_deform + fc_brush_rust + fc_metal_deform_scracth + fc_washer_tape_hole+ en2_insulation_resistance_ng + en2_cut_coil_wire + en2_no_load_current_hight + en2_ripple + en2_chattering + en2_lock + en2_open + en2_no_load_speed_low + en2_starting_voltage + en2_no_load_speed_high +  en2_rotor_mix + en2_surge_volt_max + en2_wrong_post_of_pole + en2_err + en2_noise + en1_insulation_resistace_ng + en1_cut_coil_wire + en1_lock +
en1_wareform_ma_abnormal + en1_shaft_bent + en1_ripple + en1_short + en1_chattering + en1_no_load_current_high + en1_vibration_ng + en1_open + en1_rotor_mix + ca_app_metal_dirty + ca_app_tape_hole_deform + ca_app_metal_high + ca_app_case_deform_scracth + ca_app_metal_deform_scratch + ca_app_magnet_broken+ ca_mg_metal_deform_scratch + ca_mg_case_deform_scratch+ca_bonding_metal_deform_scratch +
ca_bonding_case_deform_scracth + ba_tc_endplay_big + ba_tc_endplay_small + ba_tc_brush_bent + ba_tc_shaft_mix + ba_rto_ng + ba_rto_mix+ ba_app_metal_deform_scracth + ba_app_ba_deform + ba_app_endplate_deform_scracth + ba_app_error_other + ba_bm_brush_deform_scracth + ba_bm_metal_deform_scracth + ba_bm_ba_deform + ba_bm_endplay_deform_scracth) total_ng,

(fc_endplay_small + fc_endplay_big + fc_shaft_scracth + fc_terminal_low +
  fc_case_scracth_dirty + fc_pinion_worm_ng + fc_shaft_lock + fc_ba_deform +
  fc_tape_hole_deform + fc_brush_rust + fc_metal_deform_scracth + fc_washer_tape_hole) final_app,(en2_insulation_resistance_ng + en2_cut_coil_wire + en2_no_load_current_hight + en2_ripple + en2_chattering + en2_lock + en2_open + en2_no_load_speed_low + en2_starting_voltage + en2_no_load_speed_high +
  en2_rotor_mix + en2_surge_volt_max + en2_wrong_post_of_pole + en2_err + en2_noise) en2, (en1_insulation_resistace_ng + en1_cut_coil_wire + en1_lock +
en1_wareform_ma_abnormal + en1_shaft_bent + en1_ripple + en1_short + en1_chattering + en1_no_load_current_high + en1_vibration_ng + en1_open + en1_rotor_mix) en1, (ca_app_metal_dirty + ca_app_tape_hole_deform + ca_app_metal_high + ca_app_case_deform_scracth + ca_app_metal_deform_scratch + ca_app_magnet_broken) as case_assy,(ca_mg_metal_deform_scratch + ca_mg_case_deform_scratch) as case_mg,(ca_bonding_metal_deform_scratch +
ca_bonding_case_deform_scracth) case_bonding, (ba_tc_endplay_big + ba_tc_endplay_small + ba_tc_brush_bent + ba_tc_shaft_mix) as trust_gap, (ba_rto_ng + ba_rto_mix) as rotor, (ba_app_metal_deform_scracth + ba_app_ba_deform + ba_app_endplate_deform_scracth + ba_app_error_other) as bracket_assy, (ba_bm_brush_deform_scracth + ba_bm_metal_deform_scracth + ba_bm_ba_deform + ba_bm_endplay_deform_scracth) as bracket_metal from t_ncvc_pdc_fc f left join t_ncvc_pdc_en2 e2 on f.fc_id = e2.en2_id
  left join t_ncvc_pdc_en1 e1 on f.fc_id = e1.en1_id 
    left join t_ncvc_pdc_ca ca on f.fc_id = ca.ca_id
    left join t_ncvc_pdc_ba ba on f.fc_id = ba.ba_id                                                                          
    left join (
select t1.dates,case when ca3 is null then ca1 else ca3 end id from
(select dates, line_cd, max(fc_id)ca1 from t_ncvc_pdc_fc  where times > '06:00:00' and times <= '23:59:00' group by dates, line_cd) t1 left join
(select dates - 1 dates, line_cd, max(fc_id) ca3 from t_ncvc_pdc_fc  where times > '00:00:00' and times <= '05:59:00' group by dates, line_cd) t3 on t1.dates = t3.dates where t1.line_cd = :line_cd and  t1.dates between :datesfrom and :datesto) tbl on f.fc_id = tbl.id where f.fc_id = tbl.id and f.line_cd = :line_cd ");

            sqlParameter.AddParameterString("line_cd", inVo.ProLine);
            sqlParameter.AddParameterDateTime("datesfrom", inVo.StartDay);
            sqlParameter.AddParameterDateTime("datesto", inVo.EndDay);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    StartDay        = DateTime.Parse(dataReader["dates"].ToString()),
                    EndDay          = DateTime.Parse(dataReader["endday"].ToString()),
                    ProModel        = dataReader["model_cd"].ToString(),
                    ProLine         = dataReader["line_cd"].ToString(),
                    TotalNG         = int.Parse(dataReader["total_ng"].ToString()),
                    ProInput        = int.Parse(dataReader["ca_input_line"].ToString()),
                    ProInputCase    = int.Parse(dataReader["ca_input"].ToString()),
                    ProInputBracket = int.Parse(dataReader["ba_input"].ToString()),
                    ProInputApp     = int.Parse(dataReader["fc_input"].ToString()),
                    ProOutput       = int.Parse(dataReader["output"].ToString()),

                    Final_App     = int.Parse(dataReader["final_app"].ToString()),
                    En2NG         = int.Parse(dataReader["en2"].ToString()),
                    En1NG         = int.Parse(dataReader["en1"].ToString()),
                    TrustGap      = int.Parse(dataReader["trust_gap"].ToString()),
                    Rotor         = int.Parse(dataReader["rotor"].ToString()),
                    Braket        = int.Parse(dataReader["bracket_assy"].ToString()),
                    Bracket_Metal = int.Parse(dataReader["bracket_metal"].ToString()),
                    Case_Assy     = int.Parse(dataReader["case_assy"].ToString()),
                    Case_MG       = int.Parse(dataReader["case_mg"].ToString()),
                    MG_Bongding   = int.Parse(dataReader["case_bonding"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select model_cd,'All Line' line_cd, Case When times between '06:00:00' and '23:59:00' then dates when times between '00:00:00' and '05:59:00' then dates-1 end datesss, sum(ca_input_line) as ca_input_line,sum(ba_input) ba_input, sum(ca_input) as ca_input, sum(fc_input) as fc_input,sum(output) output,sum(final_app+en2+en1+case_assy+case_mg+case_bonding+trust_gap+rotor+bracket_assy+bracket_metal) as total_ng,sum(final_app) final_app, sum(en2) en2, sum(en1) en1,sum(case_assy) case_assy,sum(case_mg) case_mg, sum(case_bonding) case_bonding,sum(trust_gap) trust_gap,sum(rotor) rotor,sum(bracket_assy) bracket_assy,sum(bracket_metal) bracket_metal from 
        
            (Select fc.dates ,fc.times ,fc.fc_id ,fc.model_cd ,fc.line_cd,ca_input_line,ca_input,ba_input,fc_input, output, (fc_endplay_small + fc_endplay_big + fc_shaft_scracth + fc_terminal_low + fc_case_scracth_dirty + fc_pinion_worm_ng + fc_shaft_lock + fc_ba_deform + fc_tape_hole_deform + fc_brush_rust + fc_metal_deform_scracth + fc_washer_tape_hole) final_app,(en2_insulation_resistance_ng + en2_cut_coil_wire + en2_no_load_current_hight + en2_ripple + en2_chattering + en2_lock + en2_open + en2_no_load_speed_low + en2_starting_voltage + en2_no_load_speed_high + en2_rotor_mix + en2_surge_volt_max + en2_wrong_post_of_pole + en2_err + en2_noise) en2, (en1_insulation_resistace_ng + en1_cut_coil_wire + en1_lock + en1_wareform_ma_abnormal + en1_shaft_bent + en1_ripple + en1_short + en1_chattering + en1_no_load_current_high + en1_vibration_ng + en1_open + en1_rotor_mix) en1, (ca_app_metal_dirty + ca_app_tape_hole_deform + ca_app_metal_high + ca_app_case_deform_scracth + ca_app_metal_deform_scratch + ca_app_magnet_broken) as case_assy,(ca_mg_metal_deform_scratch + ca_mg_case_deform_scratch) as case_mg,(ca_bonding_metal_deform_scratch + ca_bonding_case_deform_scracth) case_bonding, (ba_tc_endplay_big + ba_tc_endplay_small + ba_tc_brush_bent + ba_tc_shaft_mix) as trust_gap, (ba_rto_ng + ba_rto_mix) as rotor, (ba_app_metal_deform_scracth + ba_app_ba_deform + ba_app_endplate_deform_scracth + ba_app_error_other) as bracket_assy, (ba_bm_brush_deform_scracth + ba_bm_metal_deform_scracth + ba_bm_ba_deform + ba_bm_endplay_deform_scracth) as bracket_metal 
            from t_ncvc_pdc_fc fc left join t_ncvc_pdc_en2 e2 on fc.line_cd = e2.line_cd and fc.fc_id = e2.en2_id left join t_ncvc_pdc_en1 e1 on fc.line_cd = e1.line_cd and fc.fc_id = e1.en1_id left join t_ncvc_pdc_ca ca on fc.line_cd = ca.line_cd and fc.fc_id = ca.ca_id left join 
            t_ncvc_pdc_ba ba on fc.line_cd = ba.line_cd and fc.fc_id = ba.ba_id left join 
            (select dates ,line_cd, Case when idca3 is null then idca1 else idca3 end id from (select tblca1.dates,tblca1.line_cd, idca1, idca3 from (select line_cd,o.dates , max(o.fc_id) idca1 from t_ncvc_pdc_fc o where o.times > '06:00:00' and o.times <= '23:59:00' and o.dates >= :datefrom and o.dates <= :datesto group by o.dates, line_cd order by dates) tblca1 left join (select line_cd,(o.dates-1) dates , max(o.fc_id) idca3 from t_ncvc_pdc_fc o where o.times > '00:00:00' and o.times <= '05:55:00' and o.dates > :datefrom and o.dates - 1 <= :datesto group by line_cd,o.dates order by idca3) tblca3 on tblca1.dates = tblca3.dates and tblca1.line_cd = tblca3.line_cd) tbl order by dates,line_cd) l on l.line_cd = fc.line_cd where l.id = fc.fc_id order by fc.dates,fc.line_cd ) tbl where model_cd = :model_cd group by datesss,model_cd order by datesss");

            sqlParameter.AddParameterDateTime("datefrom", inVo.StartDay);
            sqlParameter.AddParameterDateTime("datesto", inVo.EndDay);

            sqlParameter.AddParameterString("line_cd", inVo.ProLine);
            sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            //IDataAdapter d = sqlCommandAdapter.ExecuteScalar(sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    StartDay        = DateTime.Parse(dataReader["datesss"].ToString()),
                    ProModel        = dataReader["model_cd"].ToString(),
                    ProLine         = dataReader["line_cd"].ToString(),
                    TotalNG         = int.Parse(dataReader["total_ng"].ToString()),
                    ProInput        = int.Parse(dataReader["ca_input_line"].ToString()),
                    ProInputCase    = int.Parse(dataReader["ca_input"].ToString()),
                    ProInputBracket = int.Parse(dataReader["ba_input"].ToString()),
                    ProInputApp     = int.Parse(dataReader["fc_input"].ToString()),
                    ProOutput       = int.Parse(dataReader["output"].ToString()),

                    Final_App   = int.Parse(dataReader["final_app"].ToString()),
                    En2NG       = int.Parse(dataReader["en2"].ToString()),
                    Case_Assy   = int.Parse(dataReader["case_assy"].ToString()),
                    En1NG       = int.Parse(dataReader["en1"].ToString()),
                    Case_MG     = int.Parse(dataReader["case_mg"].ToString()),
                    MG_Bongding = int.Parse(dataReader["case_bonding"].ToString()),

                    TrustGap      = int.Parse(dataReader["trust_gap"].ToString()),
                    Rotor         = int.Parse(dataReader["rotor"].ToString()),
                    Braket        = int.Parse(dataReader["bracket_assy"].ToString()),
                    Bracket_Metal = int.Parse(dataReader["bracket_metal"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select (e1.dates+e1.times) datetimes, e1.model_cd,e1.line_cd, e1.process_cd, ");
            sql.Append("en1_insulation_resistace_ng, en1_cut_coil_wire, en1_lock, en1_wareform_ma_abnormal, en1_shaft_bent, en1_ripple, en1_short, en1_chattering, en1_no_load_current_high, en1_vibration_ng, en1_open, en1_rotor_mix ");
            sql.Append("from t_ncvc_pdc_en1 e1 ");
            sql.Append("where e1.line_cd = :line_cd ");
            sql.Append("and e1.dates = :dates ");
            sql.Append("and (e1.times in(select min(times) from t_ncvc_pdc_en1 where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");

            sql.Append("or e1.dates-1 =:dates and e1.line_cd = :line_cd ");
            sql.Append("and (e1.times in(select min(times) from t_ncvc_pdc_en1 where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e1.times in(select max(times) from t_ncvc_pdc_en1 where times between '05:00:00' and '05:35:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");


            sqlParameter.AddParameterString("line_cd", inVo.ProLine);
            sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
            sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
                    ProModel = dataReader["model_cd"].ToString(),
                    ProLine  = dataReader["line_cd"].ToString(),

                    En1_insulation_resistace_ng = int.Parse(dataReader["en1_insulation_resistace_ng"].ToString()),
                    En1_cut_coil_wire           = int.Parse(dataReader["en1_cut_coil_wire"].ToString()),
                    En1_lock = int.Parse(dataReader["en1_lock"].ToString()),
                    En1_wareform_ma_abnormal = int.Parse(dataReader["en1_wareform_ma_abnormal"].ToString()),
                    En1_shaft_bent           = int.Parse(dataReader["en1_shaft_bent"].ToString()),
                    En1_ripple               = int.Parse(dataReader["en1_ripple"].ToString()),
                    En1_short                = int.Parse(dataReader["en1_short"].ToString()),
                    En1_chattering           = int.Parse(dataReader["en1_chattering"].ToString()),
                    En1_no_load_current_high = int.Parse(dataReader["en1_no_load_current_high"].ToString()),
                    En1_vibration_ng         = int.Parse(dataReader["en1_vibration_ng"].ToString()),
                    En1_open      = int.Parse(dataReader["en1_open"].ToString()),
                    En1_rotor_mix = int.Parse(dataReader["en1_rotor_mix"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Пример #15
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerNCVCVo inVo = (ProductionControllerNCVCVo)vo;
            StringBuilder sql = new StringBuilder();
            ValueObjectList <ProductionControllerNCVCVo> voList = new ValueObjectList <ProductionControllerNCVCVo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //create parameter
            DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList();

            sql.Append(@"select (e2.dates+e2.times) datetimes, e2.model_cd,e2.line_cd, e2.process_cd, ");
            sql.Append("en2_insulation_resistance_ng, en2_cut_coil_wire, en2_no_load_current_hight, en2_ripple, en2_chattering,  en2_lock, en2_open, en2_no_load_speed_low, en2_starting_voltage, en2_no_load_speed_high, en2_rotor_mix, en2_surge_volt_max, en2_wrong_post_of_pole, en2_err, en2_noise ");
            sql.Append("from t_ncvc_pdc_en2 e2 ");
            sql.Append("where e2.line_cd = :line_cd ");
            sql.Append("and e2.dates = :dates ");
            sql.Append("and (e2.times in(select min(times) from t_ncvc_pdc_en2 where times between '06:00:00' and '06:55:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd ) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '06:00:00' and '07:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '07:00:00' and '08:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '08:00:00' and '09:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '09:00:00' and '10:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '10:00:00' and '11:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '11:00:00' and '12:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '12:00:00' and '13:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '13:00:00' and '14:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '14:00:00' and '15:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '15:00:00' and '16:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '16:00:00' and '17:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '17:00:00' and '18:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '18:00:00' and '19:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '19:00:00' and '20:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '20:00:00' and '21:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '21:00:00' and '22:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '22:00:00' and '23:05:00' and dates = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");

            sql.Append("or e2.dates-1 =:dates and e2.line_cd = :line_cd ");
            sql.Append("and (e2.times in(select min(times) from t_ncvc_pdc_en2 where times between '00:00:00' and '00:55:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '00:00:00' and '01:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '01:00:00' and '02:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '02:00:00' and '03:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '03:00:00' and '04:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '04:00:00' and '05:05:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd) ");
            sql.Append("or e2.times in(select max(times) from t_ncvc_pdc_en2 where times between '05:00:00' and '05:35:00' and dates-1 = :dates and line_cd = :line_cd and model_cd = :model_cd)) ");


            sqlParameter.AddParameterString("line_cd", inVo.ProLine);
            sqlParameter.AddParameterDateTime("dates", DateTime.Parse(inVo.Date));
            sqlParameter.AddParameterString("model_cd", inVo.ProModel);

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

            //execute SQL
            IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            while (dataReader.Read())
            {
                ProductionControllerNCVCVo outVo = new ProductionControllerNCVCVo
                {
                    //StartDay = DateTime.Parse(dataReader["dates"].ToString()),
                    TimeHour = DateTime.Parse(dataReader["datetimes"].ToString()),
                    ProModel = dataReader["model_cd"].ToString(),
                    ProLine  = dataReader["line_cd"].ToString(),

                    En2_insulation_resistance_ng = int.Parse(dataReader["en2_insulation_resistance_ng"].ToString()),
                    En2_cut_coil_wire            = int.Parse(dataReader["en2_cut_coil_wire"].ToString()),
                    En2_no_load_current_hight    = int.Parse(dataReader["en2_no_load_current_hight"].ToString()),
                    En2_ripple             = int.Parse(dataReader["en2_ripple"].ToString()),
                    En2_chattering         = int.Parse(dataReader["en2_chattering"].ToString()),
                    En2_lock               = int.Parse(dataReader["en2_lock"].ToString()),
                    En2_open               = int.Parse(dataReader["en2_open"].ToString()),
                    En2_no_load_speed_low  = int.Parse(dataReader["en2_no_load_speed_low"].ToString()),
                    En2_starting_voltage   = int.Parse(dataReader["en2_starting_voltage"].ToString()),
                    En2_no_load_speed_high = int.Parse(dataReader["en2_no_load_speed_high"].ToString()),
                    En2_rotor_mix          = int.Parse(dataReader["en2_rotor_mix"].ToString()),
                    En2_surge_volt_max     = int.Parse(dataReader["en2_surge_volt_max"].ToString()),
                    En2_wrong_post_of_pole = int.Parse(dataReader["en2_wrong_post_of_pole"].ToString()),
                    En2_err   = int.Parse(dataReader["en2_err"].ToString()),
                    En2_noise = int.Parse(dataReader["en2_noise"].ToString()),
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }