public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; StringBuilder sql = new StringBuilder(); GA1ModelVo voList = new GA1ModelVo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append("select a.a90_model as Model, a.a90_line as Line, a.a90_thurst_status as Thurst, a.a90_factory as Thurst_MC, a.a90_noise_status as Noise, a90_oqc_data as NG_Thurst,weight from t_checkpusha90 a left join m_machine_thurst b on a.a90_model = b.model and a.a90_line = b.line and a.a90_factory = b.machine where a90_barcode = '" + inVo.A90Barcode + "' order by a90_date + a90_time desc"); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = new DataSet(); ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL //IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); GA1ModelVo outVo = new GA1ModelVo { dt = ds.Tables[0] }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { RateNGVo inVo = (RateNGVo)vo; StringBuilder sql = new StringBuilder(); RateNGVo voList = new RateNGVo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append("select rate_ng_id, rate_ng_model,rate_ng_line,rate_ng_cd, rate_ng_ratio from m_rate_ng where 1=1"); if (!string.IsNullOrEmpty(inVo.RateCode)) { sql.Append(" and rate_ng_cd = :rate_ng_cd "); sqlParameter.AddParameter("rate_ng_cd", inVo.RateCode); } sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL RateNGVo outVo1 = new RateNGVo { dt = ds.Tables[0], }; return(outVo1); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; StringBuilder sql = new StringBuilder(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append("select serial_short, boxid FROM product_serial_printdate WHERE printdate BETWEEN '" + System.DateTime.Today.AddDays(-7) + "' AND '" + System.DateTime.Today.AddDays(1) + "'"); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = new DataSet(); ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); GA1ModelVo outVo = new GA1ModelVo { dt = ds.Tables[0] }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; StringBuilder sql = new StringBuilder(); GA1ModelVo voList = new GA1ModelVo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append("select model, line,barcode, eq_id as Machine, judgment from t_noisecheck_a90 where barcode = '" + inVo.A90Barcode + "' order by date_check desc"); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = new DataSet(); ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL //IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); GA1ModelVo outVo = new GA1ModelVo { dt = ds.Tables[0] }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; StringBuilder sql = new StringBuilder(); sql.Append("select boxid, serno, model, ship_date, status from t_shipment_record where serno = '" + inVo.A90Barcode + "'"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); //sqlParameter.AddParameter("serno", inVo.A90Barcode); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = new DataSet(); ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL //IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); GA1ModelVo outVo = new GA1ModelVo { dt = ds.Tables[0] }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; StringBuilder sql = new StringBuilder(); GA1ModelVo voList = new GA1ModelVo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append("delete from t_product_serial where boxid = '" + inVo.BoxID + "'"); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = new DataSet(); ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL //IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); GA1ModelVo outVo = new GA1ModelVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { IPQCVo inVo = (IPQCVo)vo; StringBuilder sql = new StringBuilder(); ValueObjectList <IPQCVo> voList = new ValueObjectList <IPQCVo>(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); sql.Append("select inspect, lot, inspectdate, line, qc_user, status, m1, m2, m3, m4, m5, x, r FROM tbl_measure_history WHERE model = :model AND process = :process AND inspect = :inspect AND lot >= :lot AND lot <= :lotlast AND line = :line order by lot, inspectdate"); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("model", inVo.Model); sqlParameter.AddParameter("process", inVo.Process); sqlParameter.AddParameter("inspect", inVo.Inspect); sqlParameter.AddParameter("lot", inVo.Lot); sqlParameter.AddParameter("lotlast", inVo.LotLast); sqlParameter.AddParameter("line", inVo.Line); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = new DataSet(); ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL IPQCVo outVo = new IPQCVo { dt = ds.Tables[0] }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; StringBuilder sql = new StringBuilder(); GA1ModelVo voList = new GA1ModelVo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append("select serialno as Serial, line, model, lot, thurst, thurst_mc, noise, noise_mc from t_product_serial where"); sql.Append(" boxid = :boxid"); sqlParameter.AddParameterString("boxid", inVo.BoxID); //sqlParameter.AddParameterDateTime("printdate1", inVo.PrintDate.AddDays(1)); sql.Append(" order by serialno"); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = new DataSet(); ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL //IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); GA1ModelVo outVo = new GA1ModelVo { dt = ds.Tables[0] }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; StringBuilder sql = new StringBuilder(); GA1ModelVo voList = new GA1ModelVo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append(@"select model,line, eq_id machine,barcode, serial_id , id, date_check, judgment, l1_v_cw, l1_v_ccw, e1_v_cw, e2_v_cw, e3_v_cw, e4_v_cw, e5_v_cw, e1_v_ccw, e2_v_ccw, e3_v_ccw, e4_v_ccw, e5_v_ccw, registration_user_cd, registration_date_time, factory_cd from t_noisecheck_a90 where "); sql.Append(@" registration_date_time >= :datefrom and registration_date_time <= :dateto "); sqlParameter.AddParameter("datefrom", inVo.DateFrom); sqlParameter.AddParameter("dateto", inVo.DateTo); if (!string.IsNullOrEmpty(inVo.Noise_eq_id)) { sql.Append(" and eq_id = :eq_id "); sqlParameter.AddParameter("eq_id", inVo.Noise_eq_id); } sql.Append("order by eq_id,date_check"); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL GA1ModelVo outVo1 = new GA1ModelVo { dt = ds.Tables[0], }; return(outVo1); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; StringBuilder sql = new StringBuilder(); GA1ModelVo voList = new GA1ModelVo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append("select a.boxid as BoxID, a.printdate as PrintDate, a.user_cd as User, a.shipdate as ShipDate from t_box_id a left join t_product_serial b on b.boxid = a.boxid where 1 = 1"); sql.Append(" and b.serialno >= :serialno"); sqlParameter.AddParameterString("serialno", inVo.A90Barcode); sql.Append(@" order by a.boxid"); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = new DataSet(); ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL //IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); GA1ModelVo outVo = new GA1ModelVo { dt = ds.Tables[0] }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; StringBuilder sql = new StringBuilder(); GA1ModelVo ovo = new GA1ModelVo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append(@"select a90_model as Model, a90_line as Line, a90_thurst_status as Thurst, a90_factory as Thurst_MC, a90_noise_status as Noise, eq_id as Noise_MC from thurst_noise where a90_barcode = :a90_barcode order by oid desc limit 1"); sqlParameter.AddParameterString("a90_barcode", inVo.A90Barcode); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = new DataSet(); ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL //IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); GA1ModelVo outVo = new GA1ModelVo { dt = ds.Tables[0] }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo; StringBuilder sql = new StringBuilder(); ProductionControllerGA1Vo voList = new ProductionControllerGA1Vo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); string 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); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { TransferVo inVo = (TransferVo)vo; StringBuilder sql = new StringBuilder(); ValueObjectList <TransferVo> voList = new ValueObjectList <TransferVo>(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append(@"select a.asset_id AssetID, a.asset_cd AssetCode, a.asset_name AssetName, a.asset_no AssetNo from t_transfer_detail_info b left join m_asset a on a.asset_id = b.asset_id left join t_transfer_device c on c.transfer_device_id = b.transfer_device_id where 1 = 1"); if (!String.IsNullOrEmpty(inVo.TransferDeviceCode)) { sql.Append(@" and c.transfer_device_cd =:transfer_device_cd"); sqlParameter.AddParameterString("transfer_device_cd", inVo.TransferDeviceCode); } sql.Append(" order by a.asset_cd"); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //execute SQL DataSet da = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); //while (dataReader.Read()) //{ // TransferVo outVo = new TransferVo // { // AssetCode = dataReader["asset_cd"].ToString(), // AssetName = dataReader["asset_name"].ToString(), // AssetNo = int.Parse(dataReader["asset_no"].ToString()), // AssetID = int.Parse(dataReader["asset_id"].ToString()) // }; // voList.add(outVo); //} TransferVo outVo2 = new TransferVo { dt = da.Tables[0] }; //dataReader.Close(); return(outVo2); }
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(); 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); }
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); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; StringBuilder sql = new StringBuilder(); GA1ModelVo voList = new GA1ModelVo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append("select boxid, printdate, user_cd as User, shipdate, child_model from t_box_id where 1 = 1"); if (inVo.Format) { sql.Append(" and printdate >= '" + inVo.PrintDate.ToString("yyyy/MM/dd") + " 00:00:00' and printdate < '" + inVo.PrintDate.ToString("yyyy/MM/dd") + " 23:59:59'"); //sqlParameter.AddParameter("printdate", inVo.PrintDate.ToString("yyyy/MM/dd")); //sqlParameter.AddParameterDateTime("printdate1", inVo.PrintDate.AddDays(1)); } else { sql.Append(" and shipdate >= '" + inVo.ShipDate.ToString("yyyy/MM/dd") + " 00:00:00' and shipdate < '" + inVo.ShipDate.ToString("yyyy/MM/dd") + " 23:59:59'"); //sql.Append(@" and shipdate >= :shipdate and shipdate < :shipdate1"); //sqlParameter.AddParameterDateTime("shipdate", inVo.ShipDate); //sqlParameter.AddParameterDateTime("shipdate1", inVo.ShipDate.AddDays(1)); } sql.Append(@" order by boxid"); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = new DataSet(); ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL //IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); GA1ModelVo outVo = new GA1ModelVo { dt = ds.Tables[0] }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo; StringBuilder sql = new StringBuilder(); ProductionControllerGA1Vo voList = new ProductionControllerGA1Vo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); //string sqlChung = " times,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); }
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); }
/// <summary> /// execute with dataadapter /// </summary> /// <param name="parameterList"></param> /// <returns></returns> public DataSet ExecuteDataSet(DbParameterList parameterList) { return(internalAdaptor.ExecuteDataSet(parameterList)); }