Exemple #1
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
            StringBuilder             sql  = new StringBuilder();
            ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

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

            sql.Append(@"select distinct model from modeltbl where model like 'LDP%' or model like 'GR%' order by model desc");

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

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

            while (dataReader.Read())
            {
                ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
                {
                    ModelCode = dataReader["model"].ToString()
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
        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          line         = "";
            string          grline       = "";

            if (!string.IsNullOrEmpty(inVo.LineCode))
            {
                line   = ",a.line";
                grline = ",a.line";
            }
            else
            {
                line = ",cast ('All Line' as character varying) line"; grline = ",line";
            }
            sql.Append(@"select id,model, line,dates, process, sum(inspectdata) inspectdata from (select cast(dense_rank() over(partition by line order by process) as int ) id, a.model" + line + ", a.process,cast (a.inspectdate as date) dates, a.serno, sum(inspectdata) inspectdata from  " + inVo.TableName);
            sql.Append(" a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate = b.inspectdate ");
            sql.Append(@" and a.inspectdate >= :datefrom and a.inspectdate <= :dateto ");
            sqlParameter.AddParameter("datefrom", inVo.DateFrom);
            sqlParameter.AddParameter("dateto", inVo.DateTo);

            if (!string.IsNullOrEmpty(inVo.ModelCode))
            {
                sql.Append(@" and a.model  =:model");
                sqlParameter.AddParameterString("model", inVo.ModelCode);
            }
            if (!string.IsNullOrEmpty(inVo.LineCode))
            {
                sql.Append(@" and a.line  =:line");
                sqlParameter.AddParameterString("line", inVo.LineCode);
            }
            if (!string.IsNullOrEmpty(inVo.ProcessCode))
            {
                sql.Append(@" and a.process  =:process");
                sqlParameter.AddParameterString("process", inVo.ProcessCode);
            }
            sql.Append(@" group by process" + grline + ",a.model,a.serno,a.inspectdate order by a.inspectdate ) tbl ");
            sql.Append(@" group by id,dates, model,line, process order by dates");

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
            DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter);

            //execute SQL

            ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo
            {
                dt = ds.Tables[0],
            };

            return(outVo1);
        }
Exemple #3
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
            StringBuilder             sql  = new StringBuilder();
            ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

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

            sql.Append("select count(*) datas from (");
            sql.Append("select * from (");
            sql.Append("select a90_barcode bar,max(a90_date+a90_time) from t_checkpusha90 ");
            sql.Append("where  a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto ");
            if (!string.IsNullOrEmpty(inVo.LineCode))
            {
                sql.Append(@" and a90_line  =:line");
                sqlParameter.AddParameterString("line", inVo.LineCode);
            }
            if (!string.IsNullOrEmpty(inVo.ModelCode))
            {
                sql.Append(@" and a90_model  =:a90_model");
                sqlParameter.AddParameterString("a90_model", inVo.ModelCode);
            }
            sql.Append(" group by a90_barcode) a left join t_checkpusha90 b on a.bar = b.a90_barcode and a.max = b.a90_date+b.a90_time ) tbl ");
            sql.Append("where 1=1 and bar not like ''");
            if (inVo.change)
            {
                sql.Append(@" and a90_noise_status = 'OK' ");
            }
            else
            {
                sql.Append(@" and a90_noise_status = 'NG' ");
            }

            sqlParameter.AddParameter("datefrom", inVo.DateFrom);
            sqlParameter.AddParameter("dateto", inVo.DateTo);

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

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

            while (dataReader.Read())
            {
                ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
                {
                    InspecData = dataReader["datas"].ToString()
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
            StringBuilder             sql  = new StringBuilder();
            ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

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

            sql.Append(@"select a.process,case when sum(inspectdata) = null then 0 else sum(inspectdata) end inspectdata from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where ");
            sql.Append(@" a.inspectdate >= :datefrom and a.inspectdate <= :dateto and inspectdata != 0 and b.inspectdate >= :datefrom and b.inspectdate <= :dateto and a.inspectdate = b.inspectdate ");
            sqlParameter.AddParameter("datefrom", inVo.DateFrom);
            sqlParameter.AddParameter("dateto", inVo.DateTo);

            if (!string.IsNullOrEmpty(inVo.LineCode))
            {
                sql.Append(@" and a.line  =:line");
                sqlParameter.AddParameterString("line", inVo.LineCode);
            }

            if (!string.IsNullOrEmpty(inVo.ModelCode))
            {
                sql.Append(@" and a.model  =:model ");
                sqlParameter.AddParameterString("model", inVo.ModelCode);
            }
            sql.Append("group by a.process order by a.process");
            //if (!string.IsNullOrEmpty(inVo.ProcessName))
            //{
            //    sql.Append(@" and a.process  =:process");
            //    sqlParameter.AddParameterString("process", inVo.ProcessName);
            //}
            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

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

            while (dataReader.Read())
            {
                ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
                {
                    ProcessName = dataReader["process"].ToString(),
                    InspecData  = dataReader["inspectdata"].ToString()
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Exemple #5
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
            StringBuilder             sql  = new StringBuilder();
            ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

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

            sql.Append("select count (*) datas from (");
            sql.Append("select distinct barcode from t_noisecheck_a90 ");
            sql.Append("where  date_check >= :datefrom and date_check <= :dateto ");
            if (!string.IsNullOrEmpty(inVo.ModelCode))
            {
                sql.Append(@" and model  =:model");
                sqlParameter.AddParameterString("model", inVo.ModelCode);
            }
            if (inVo.change)//search theo line
            {
                sql.Append(@" and line  =:line");
                sqlParameter.AddParameterString("line", inVo.LineCode);
            }
            else//search tat ca line
            {
                //de cho vui thui @@
            }
            sql.Append(@" ) tbl");

            sqlParameter.AddParameter("datefrom", inVo.DateFrom);
            sqlParameter.AddParameter("dateto", inVo.DateTo);

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

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

            while (dataReader.Read())
            {
                ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
                {
                    InspecData = dataReader["datas"].ToString()
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Exemple #6
0
        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();

            sql.Append("select 'INPUT' inspect, ");
            sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "FRAME", "IP") + " FRAME,");
            sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "GEAR", "IP") + " GEAR,");
            sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "MOTOR", "IP") + " MOTOR ");

            sql.Append("union ");

            sql.Append("select 'OUTPUT' inspect, ");
            sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "FRAME", "OP") + " FRAME,");
            sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "GEAR", "OP") + " GEAR,");
            sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "MOTOR", "OP") + " MOTOR ");

            sql.Append("union ");

            sql.Append("select 'TOTAL NG' inspect, ");
            sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "FRAME", "NG") + " FRAME, ");
            sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "GEAR", "NG") + " GEAR, ");
            sql.Append(select(inVo.TableName, inVo.ModelCode, inVo.LineCode, inVo.DateFrom, inVo.DateTo, "MOTOR", "NG") + " MOTOR ");

            sql.Append("order by inspect ");

            //sqlParameter.AddParameter("model", inVo.ModelCode);
            //sqlParameter.AddParameter("line", inVo.LineCode);
            //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 vo)
        {
            ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
            StringBuilder             sql  = new StringBuilder();
            ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

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

            sql.Append("select count(*) datas from (select * from ");
            sql.Append("(select a90_barcode bar,max(oid) oid from t_checkpusha90 where a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto  ");
            if (!string.IsNullOrEmpty(inVo.LineCode))
            {
                sql.Append(" and a90_line = :a90_line ");
                sqlParameter.AddParameter("a90_line", inVo.LineCode);
            }
            if (!string.IsNullOrEmpty(inVo.ModelCode))
            {
                sql.Append(" and a90_model = :a90_model ");
                sqlParameter.AddParameter("a90_model", inVo.ModelCode);
            }

            sql.Append(" group by a90_barcode) a left join t_checkpusha90 b on a.oid = b.oid where  b.a90_oqc_data = 'NG') b");
            sqlParameter.AddParameter("datefrom", inVo.DateFrom);
            sqlParameter.AddParameter("dateto", inVo.DateTo);
            //group by a90_barcode) a left join t_checkpusha90 b on a.oid = b.oid where  b.a90_oqc_data = 'NG') b
            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
            //DataSet ds = sqlCommandAdapter.ExecuteScalar(sqlParameter);
            IDataReader reader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter);

            //execute SQL
            while (reader.Read())
            {
                ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
                {
                    //LineCode = dataReader["line"].ToString() inspectdata
                    InspecData = reader["datas"].ToString()
                };
                voList.add(outVo);
            }

            reader.Close();

            return(voList);
        }
        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();

            sql.Append(@"select inspect, sum(inspectdata) inspectdata  from  " + inVo.TableName);
            sql.Append(" a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate = b.inspectdate ");
            sql.Append(@" and a.inspectdate >= :datefrom and a.inspectdate <= :dateto ");
            sqlParameter.AddParameter("datefrom", inVo.DateFrom);
            sqlParameter.AddParameter("dateto", inVo.DateTo);

            if (!string.IsNullOrEmpty(inVo.ModelCode))
            {
                sql.Append(@" and a.model  =:model");
                sqlParameter.AddParameterString("model", inVo.ModelCode);
            }
            if (!string.IsNullOrEmpty(inVo.LineCode))
            {
                sql.Append(@" and a.line  =:line");
                sqlParameter.AddParameterString("line", inVo.LineCode);
            }
            if (!string.IsNullOrEmpty(inVo.ProcessCode))
            {
                sql.Append(@" and a.process  =:process");
                sqlParameter.AddParameterString("process", inVo.ProcessCode);
            }

            sql.Append(@" group by inspect order by inspect");

            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
            DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter);

            //execute SQL

            ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo
            {
                dt = ds.Tables[0],
            };

            return(outVo1);
        }
Exemple #9
0
        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();

            sql.Append(" select a.process, a.inspect,a.sum inspectdata from (");
            sql.Append("select a.process, b.inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.model = :model and line =:line and a.process not in ('FA_IP','GC_IP') group by a.process, b.inspect");
            sql.Append(" union ");
            sql.Append("select 'FA_IP' process, 'Input Frame' inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.process in ('FA_IP') and a.model = :model and line =:line");
            sql.Append(" union ");
            sql.Append("select 'GC_IP' process, 'Input Gear' inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.process in ('GC_IP') and a.model = :model and line =:line");
            sql.Append(" union ");
            sql.Append("select 'TOTAL_F' process, 'Toal NG Frame' inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.process in ('FA_APP', 'FA_BallB', 'FA_Caulk') and a.model = :model and line =:line");
            sql.Append(" union ");
            sql.Append("select 'TOTAL_G' process, 'Toal NG Gear' inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.process in ('GC_Bear','GC_OSW','GC_PD','GC_C','GC_PU','GC_FGASS','GC_FGCHK')  and a.model = :model and line =:line");
            sql.Append(" union ");
            sql.Append("Select 'TOTAL_M' process, 'Toal NG Motor' inspect, sum(b.inspectdata) from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno  where a.inspectdate >= :datefrom and a.inspectdate <= :dateto and a.process in ('MC_FPC','MC_FWCHK','MC_Mark','MC_NOICHK','MC_STMASS')  and a.model = :model and line =:line");
            sql.Append(" union ");
            sql.Append("select 'OUTPUT' process, 'OP3' inspect, cast(0 as double precision) sum ");
            sql.Append(" union ");
            sql.Append("select 'MC_THUCHK' process, 'THU_NG' inspect, cast(0 as double precision) sum ");

            sql.Append(") a left join processtbl c on a.process = c.process where c.line = :line order by c.stopmark, a.inspect ");

            sqlParameter.AddParameter("model", inVo.ModelCode);
            sqlParameter.AddParameter("line", inVo.LineCode);
            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);
        }
Exemple #10
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
            StringBuilder             sql  = new StringBuilder();
            ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

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

            sql.Append("select count(distinct barcode) from t_serno where 1=1 ");
            sql.Append(" and regist_date >= :datefrom and regist_date <= :dateto ");
            if (!string.IsNullOrEmpty(inVo.ModelCode))
            {
                sql.Append(@" and model  =:model");
                sqlParameter.AddParameterString("model", inVo.ModelCode);
            }
            if (!string.IsNullOrEmpty(inVo.LineCode))
            {
                sql.Append(@" and line  =:line");
                sqlParameter.AddParameterString("line", inVo.LineCode);
            }

            sqlParameter.AddParameter("datefrom", inVo.DateFrom);
            sqlParameter.AddParameter("dateto", inVo.DateTo);

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

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

            while (dataReader.Read())
            {
                ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
                {
                    InspecData = dataReader["count"].ToString()
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Exemple #11
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
            StringBuilder             sql  = new StringBuilder();
            ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

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

            sql.Append(@"select distinct b.inspect from " + inVo.TableName);
            sql.Append(" a left join " + inVo.TableName + "data b on a.serno = b.serno where a.inspectdate = b.inspectdate ");

            if (!string.IsNullOrEmpty(inVo.LineCode))
            {
                sql.Append(@" and a.line  =:line");
                sqlParameter.AddParameterString("line", inVo.LineCode);
            }
            if (!string.IsNullOrEmpty(inVo.ProcessCode))
            {
                sql.Append(@" and a.process  =:process");
                sqlParameter.AddParameterString("process", inVo.ProcessCode);
            }
            sql.Append(@" order by b.inspect");

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

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

            while (dataReader.Read())
            {
                ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
                {
                    ItemCode = dataReader["inspect"].ToString()
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Exemple #12
0
        public override ValueObject Execute(TransactionContext trxContext, ValueObject vo)
        {
            ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo;
            StringBuilder             sql  = new StringBuilder();
            ValueObjectList <ProductionControllerGA1Vo> voList = new ValueObjectList <ProductionControllerGA1Vo>();
            //create command
            DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());

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

            sql.Append("select distinct line from processtbl a left join modeltbl b on a.model = b.model where 1=1 ");

            if (!string.IsNullOrEmpty(inVo.ModelCode))
            {
                sql.Append(@" and a.model  =:model_cd");
                sqlParameter.AddParameterString("model_cd", inVo.ModelCode);
            }
            sql.Append(@" order by line");

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

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

            while (dataReader.Read())
            {
                ProductionControllerGA1Vo outVo = new ProductionControllerGA1Vo
                {
                    LineCode = dataReader["line"].ToString()
                };
                voList.add(outVo);
            }
            dataReader.Close();
            return(voList);
        }
Exemple #13
0
        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,count(*)  from (select a90_barcode, a90_date+a90_time regist_date from t_checkpusha90 where a90_thurst_status = 'OK' and  a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto) wl where regist_date >= ";

            string sqlChung = " times, count(distinct barcode) from (select * from t_serno where regist_date >= :datefrom and regist_date <=:dateto) t where 1=1 ";

            if (!string.IsNullOrEmpty(inVo.ModelCode))
            {
                sqlChung += " and model = :model";
                sqlParameter.AddParameter("model", inVo.ModelCode);
            }
            if (!string.IsNullOrEmpty(inVo.LineCode))
            {
                sqlChung += " and line = :line";
                sqlParameter.AddParameter("line", inVo.LineCode);
            }
            sqlChung += " and regist_date >=";

            sql.Append(@"select '01:00:00' " + sqlChung + " '" + inVo.Date + " 00:00:01' and regist_date <= '" + inVo.Date + " 01:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '02:00:00' " + sqlChung + " '" + inVo.Date + " 01:00:01' and regist_date <= '" + inVo.Date + " 02:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '03:00:00' " + sqlChung + " '" + inVo.Date + " 02:00:01' and regist_date <= '" + inVo.Date + " 03:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '04:00:00' " + sqlChung + " '" + inVo.Date + " 03:00:01' and regist_date <= '" + inVo.Date + " 04:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '05:00:00' " + sqlChung + " '" + inVo.Date + " 04:00:01' and regist_date <= '" + inVo.Date + " 05:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '06:00:00' " + sqlChung + " '" + inVo.Date + " 05:00:01' and regist_date <= '" + inVo.Date + " 06:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '07:00:00' " + sqlChung + " '" + inVo.Date + " 06:00:01' and regist_date <= '" + inVo.Date + " 07:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '08:00:00' " + sqlChung + " '" + inVo.Date + " 07:00:01' and regist_date <= '" + inVo.Date + " 08:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '09:00:00' " + sqlChung + " '" + inVo.Date + " 08:00:01' and regist_date <= '" + inVo.Date + " 09:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '10:00:00' " + sqlChung + " '" + inVo.Date + " 09:00:01' and regist_date <= '" + inVo.Date + " 10:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '11:00:00' " + sqlChung + " '" + inVo.Date + " 10:00:01' and regist_date <= '" + inVo.Date + " 11:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '12:00:00' " + sqlChung + " '" + inVo.Date + " 11:00:01' and regist_date <= '" + inVo.Date + " 12:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '13:00:00' " + sqlChung + " '" + inVo.Date + " 12:00:01' and regist_date <= '" + inVo.Date + " 13:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '14:00:00' " + sqlChung + " '" + inVo.Date + " 13:00:01' and regist_date <= '" + inVo.Date + " 14:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '15:00:00' " + sqlChung + " '" + inVo.Date + " 14:00:01' and regist_date <= '" + inVo.Date + " 15:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '16:00:00' " + sqlChung + " '" + inVo.Date + " 15:00:01' and regist_date <= '" + inVo.Date + " 16:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '17:00:00' " + sqlChung + " '" + inVo.Date + " 16:00:01' and regist_date <= '" + inVo.Date + " 17:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '18:00:00' " + sqlChung + " '" + inVo.Date + " 17:00:01' and regist_date <= '" + inVo.Date + " 18:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '19:00:00' " + sqlChung + " '" + inVo.Date + " 18:00:01' and regist_date <= '" + inVo.Date + " 19:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '20:00:00' " + sqlChung + " '" + inVo.Date + " 19:00:01' and regist_date <= '" + inVo.Date + " 20:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '21:00:00' " + sqlChung + " '" + inVo.Date + " 20:00:01' and regist_date <= '" + inVo.Date + " 21:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '22:00:00' " + sqlChung + " '" + inVo.Date + " 21:00:01' and regist_date <= '" + inVo.Date + " 22:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '23:00:00' " + sqlChung + " '" + inVo.Date + " 22:00:01' and regist_date <= '" + inVo.Date + " 23:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '23:59:59' " + sqlChung + " '" + inVo.Date + " 23:00:01' and regist_date <= '" + inVo.Date + " 23:59:59'");

            sql.Append(" order by times ");

            sqlParameter.AddParameter("datefrom", inVo.DateFrom);
            sqlParameter.AddParameter("dateto", inVo.DateTo);

            //if (!string.IsNullOrEmpty(inVo.LineCode))
            //{
            //    sql.Append(@" and a90_line  =:line");
            //    sqlParameter.AddParameterString("line", inVo.LineCode);
            //}
            sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString());
            DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter);

            //execute SQL

            ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo
            {
                dt = ds.Tables[0],
            };

            return(outVo1);
        }
Exemple #14
0
        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,count(*)  from (select a90_barcode, a90_date+a90_time datetimes from t_checkpusha90 where a90_thurst_status = 'OK' and  a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto) wl where datetimes >= ";

            string sqlChung = " times,count(*)  from (select a.a90_barcode,a.datetimes,a.a90_thurst_status from (select row_number() over(partition by a90_barcode order by a90_date+a90_time) id, a90_barcode,a90_date+a90_time datetimes,a90_thurst_status from t_checkpusha90 where a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto and a90_line = :line and a90_model = :a90_model order by a90_barcode,datetimes) a,(select max(id) id,a90_barcode from (select row_number() over(partition by a90_barcode order by a90_date+a90_time) id, a90_barcode,a90_date+a90_time datetimes,a90_thurst_status from t_checkpusha90 where a90_date+a90_time >= :datefrom and a90_date+a90_time <= :dateto and a90_line = :line and a90_model = :a90_model order by a90_barcode,datetimes) b group by a90_barcode) b where a.id = b.id and a.a90_barcode = b.a90_barcode and a.a90_barcode != '' ";

            sqlParameter.AddParameter("a90_model", inVo.ModelCode);
            sqlParameter.AddParameter("line", inVo.LineCode);
            if (inVo.change)
            {
                sqlChung += " and a.a90_thurst_status = 'OK' ";
            }
            else
            {
                sqlChung += " and a.a90_thurst_status = 'NG' ";
            }
            sqlChung += ") tbl where datetimes >= ";

            sql.Append(@"select '01:00:00' " + sqlChung + " '" + inVo.Date + " 00:00:01' and datetimes <= '" + inVo.Date + " 01:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '02:00:00' " + sqlChung + " '" + inVo.Date + " 01:00:01' and datetimes <= '" + inVo.Date + " 02:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '03:00:00' " + sqlChung + " '" + inVo.Date + " 02:00:01' and datetimes <= '" + inVo.Date + " 03:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '04:00:00' " + sqlChung + " '" + inVo.Date + " 03:00:01' and datetimes <= '" + inVo.Date + " 04:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '05:00:00' " + sqlChung + " '" + inVo.Date + " 04:00:01' and datetimes <= '" + inVo.Date + " 05:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '06:00:00' " + sqlChung + " '" + inVo.Date + " 05:00:01' and datetimes <= '" + inVo.Date + " 06:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '07:00:00' " + sqlChung + " '" + inVo.Date + " 06:00:01' and datetimes <= '" + inVo.Date + " 07:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '08:00:00' " + sqlChung + " '" + inVo.Date + " 07:00:01' and datetimes <= '" + inVo.Date + " 08:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '09:00:00' " + sqlChung + " '" + inVo.Date + " 08:00:01' and datetimes <= '" + inVo.Date + " 09:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '10:00:00' " + sqlChung + " '" + inVo.Date + " 09:00:01' and datetimes <= '" + inVo.Date + " 10:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '11:00:00' " + sqlChung + " '" + inVo.Date + " 10:00:01' and datetimes <= '" + inVo.Date + " 11:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '12:00:00' " + sqlChung + " '" + inVo.Date + " 11:00:01' and datetimes <= '" + inVo.Date + " 12:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '13:00:00' " + sqlChung + " '" + inVo.Date + " 12:00:01' and datetimes <= '" + inVo.Date + " 13:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '14:00:00' " + sqlChung + " '" + inVo.Date + " 13:00:01' and datetimes <= '" + inVo.Date + " 14:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '15:00:00' " + sqlChung + " '" + inVo.Date + " 14:00:01' and datetimes <= '" + inVo.Date + " 15:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '16:00:00' " + sqlChung + " '" + inVo.Date + " 15:00:01' and datetimes <= '" + inVo.Date + " 16:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '17:00:00' " + sqlChung + " '" + inVo.Date + " 16:00:01' and datetimes <= '" + inVo.Date + " 17:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '18:00:00' " + sqlChung + " '" + inVo.Date + " 17:00:01' and datetimes <= '" + inVo.Date + " 18:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '19:00:00' " + sqlChung + " '" + inVo.Date + " 18:00:01' and datetimes <= '" + inVo.Date + " 19:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '20:00:00' " + sqlChung + " '" + inVo.Date + " 19:00:01' and datetimes <= '" + inVo.Date + " 20:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '21:00:00' " + sqlChung + " '" + inVo.Date + " 20:00:01' and datetimes <= '" + inVo.Date + " 21:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '22:00:00' " + sqlChung + " '" + inVo.Date + " 21:00:01' and datetimes <= '" + inVo.Date + " 22:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '23:00:00' " + sqlChung + " '" + inVo.Date + " 22:00:01' and datetimes <= '" + inVo.Date + " 23:00:00'");
            sql.Append(" union ");
            sql.Append(@"select '23:59:59' " + sqlChung + " '" + inVo.Date + " 23:00:01' and datetimes <= '" + inVo.Date + " 23:59:59'");

            sql.Append(" order by times ");

            sqlParameter.AddParameter("datefrom", inVo.DateFrom);
            sqlParameter.AddParameter("dateto", inVo.DateTo);

            //if (!string.IsNullOrEmpty(inVo.LineCode))
            //{
            //    sql.Append(@" and a90_line  =:line");
            //    sqlParameter.AddParameterString("line", inVo.LineCode);
            //}
            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 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);
        }