public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { WareHouseVo inVo = (WareHouseVo)vo; StringBuilder sql = new StringBuilder(); sql.Append(@"insert into t_warehouse(asset_cd, rank_id, qty, unit, user_location_cd, time_start, location_before_cd, location_after_cd, comments_remake, registration_user_cd, registration_date_time, factory_cd)"); sql.Append(@"values(:asset_cd, :rank_id, :qty, :unit, :user_location_cd, :time_start, :location_before_cd, :location_after_cd, :comments_remake, :registration_user_cd, now(), :factory_cd)"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("asset_cd", inVo.AssetCode); sqlParameter.AddParameter("rank_id", inVo.RankId); sqlParameter.AddParameter("qty", inVo.Qty); sqlParameter.AddParameter("unit", inVo.Unit); sqlParameter.AddParameter("user_location_cd", inVo.UserLocationCode); sqlParameter.AddParameterDateTime("time_start", inVo.TimeStart); sqlParameter.AddParameter("location_before_cd", inVo.BeforeLocation); sqlParameter.AddParameter("location_after_cd", inVo.AfterLocation); sqlParameter.AddParameter("comments_remake", inVo.Comments); sqlParameter.AddParameter("registration_user_cd", inVo.RegistrationUserCode); sqlParameter.AddParameter("registration_date_time", inVo.RegistrationDateTime); sqlParameter.AddParameter("factory_cd", inVo.FactoryCode); //execute SQL WareHouseVo outVo = new WareHouseVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { WareHouseMainVo inVo = (WareHouseMainVo)vo; StringBuilder sql = new StringBuilder(); sql.Append(@"update t_warehouse_main set user_location_id =:user_location_id, qty =:qty, unit_id =:unit_id, account_code_id =:account_code_id, account_location_id =:account_location_id, rank_id =:rank_id, before_location_id =:before_location_id, after_location_id =:after_location_id, detail_position_id =:detail_position_id, comment_data =:comment_data, depreciation_start = :depreciation_start, depreciation_end =:depreciation_end, current_depreciation =:current_depreciation, monthly_depreciation =:monthly_depreciation, accum_depreciation_now =:accum_depreciation_now, net_value =:net_value, registration_user_cd =:registration_user_cd, registration_date_time =:registration_date_time, factory_cd =:factory_cd "); sql.Append(" where warehouse_main_id =:warehouse_main_id"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("warehouse_main_id", inVo.WareHouseMainId); sqlParameter.AddParameter("asset_id", inVo.AssetId); sqlParameter.AddParameter("qty", inVo.QTY); sqlParameter.AddParameter("unit_id", inVo.UnitId); sqlParameter.AddParameter("account_code_id", inVo.AccountCodeId); sqlParameter.AddParameter("account_location_id", inVo.AccountLocationId); sqlParameter.AddParameter("rank_id", inVo.RankId); sqlParameter.AddParameter("comment_data", inVo.CommnetsData); sqlParameter.AddParameter("depreciation_start", inVo.StartDepreciation); sqlParameter.AddParameter("depreciation_end", inVo.EndDepreciation); sqlParameter.AddParameter("current_depreciation", inVo.CurrentDepreciation); sqlParameter.AddParameter("monthly_depreciation", inVo.MonthlyDepreciation); sqlParameter.AddParameter("accum_depreciation_now", inVo.AccumDepreciation); sqlParameter.AddParameter("net_value", inVo.NetValue); sqlParameter.AddParameter("before_location_id", inVo.BeforeLocationId); sqlParameter.AddParameter("after_location_id", inVo.AfterLocationId); sqlParameter.AddParameter("detail_position_id", inVo.DetailPositionId); sqlParameter.AddParameter("user_location_id", inVo.UserLocationId); sqlParameter.AddParameter("registration_user_cd", inVo.RegistrationUserCode); sqlParameter.AddParameter("registration_date_time", inVo.RegistrationDateTime); sqlParameter.AddParameter("factory_cd", inVo.FactoryCode); //execute SQL WareHouseMainVo outVo = new WareHouseMainVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; StringBuilder sql = new StringBuilder(); sql.Append("insert into t_noisecheck_a90(eq_id , model ,line , 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 , barcode , registration_user_cd , registration_date_time , factory_cd) "); sql.Append("values(:eq_id , :model ,:line , :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 , :barcode , :registration_user_cd , now() , :factory_cd)"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("eq_id ", inVo.Noise_eq_id); sqlParameter.AddParameter("model ", inVo.Noise_model); sqlParameter.AddParameter("line ", inVo.Noise_line); sqlParameter.AddParameter("serial_id", inVo.Noise_serial_id); sqlParameter.AddParameter("id ", inVo.Noise_id); sqlParameter.AddParameter("date_check", inVo.Noise_date_check); sqlParameter.AddParameter("judgment ", inVo.Noise_judgment); sqlParameter.AddParameter("l1_v_cw", inVo.Noise_l1_v_cw); sqlParameter.AddParameter("l1_v_ccw ", inVo.Noise_l1_v_ccw); sqlParameter.AddParameter("e1_v_cw ", inVo.Noise_e1_v_cw); sqlParameter.AddParameter("e2_v_cw ", inVo.Noise_e2_v_cw); sqlParameter.AddParameter("e3_v_cw ", inVo.Noise_e3_v_cw); sqlParameter.AddParameter("e4_v_cw ", inVo.Noise_e4_v_cw); sqlParameter.AddParameter("e5_v_cw ", inVo.Noise_e5_v_cw); sqlParameter.AddParameter("e1_v_ccw ", inVo.Noise_e1_v_ccw); sqlParameter.AddParameter("e2_v_ccw ", inVo.Noise_e2_v_ccw); sqlParameter.AddParameter("e3_v_ccw ", inVo.Noise_e3_v_ccw); sqlParameter.AddParameter("e4_v_ccw ", inVo.Noise_e4_v_ccw); sqlParameter.AddParameter("e5_v_ccw ", inVo.Noise_e5_v_ccw); sqlParameter.AddParameter("barcode", inVo.Noise_barcode); sqlParameter.AddParameter("registration_user_cd ", inVo.Noise_registration_user_cd); //sqlParameter.AddParameter("registration_date_time", inVo.Noise_registration_date_time); sqlParameter.AddParameter("factory_cd", inVo.Noise_factory_cd); //execute SQL GA1ModelVo outVo = new GA1ModelVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { AssetVo inVo = (AssetVo)vo; StringBuilder sql = new StringBuilder(); ValueObjectList <AssetVo> voList = new ValueObjectList <AssetVo>(); DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, string.Empty); DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append("select asset_id,asset_no,asset_cd,asset_name, asset_model, asset_supplier, asset_type, asset_serial, asset_invoice, asset_life, acquistion_date, acquistion_cost, registration_user_cd,registration_date_time,factory_cd, label_status, asset_po from m_asset"); sql.Append(" Where 1=1 "); if (!String.IsNullOrEmpty(inVo.FactoryCode)) { sql.Append(" and factory_cd = :factory_cd "); sqlParameter.AddParameterString("factory_cd", inVo.FactoryCode); } if (inVo.AssetId > 0) { sql.Append(" and asset_id = :asset_id "); sqlParameter.AddParameterInteger("asset_id", inVo.AssetId); } if (!string.IsNullOrEmpty(inVo.AssetCode)) { sql.Append(" and asset_cd = :asset_cd "); sqlParameter.AddParameterString("asset_cd", inVo.AssetCode); } if (!string.IsNullOrEmpty(inVo.AssetName)) { sql.Append(" and asset_name = :asset_name "); sqlParameter.AddParameterString("asset_name", inVo.AssetName); } if (inVo.AssetNo != 10000) { sql.Append(" and asset_no =:asset_no "); sqlParameter.AddParameter("asset_no", inVo.AssetNo); } //create command //DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); while (dataReader.Read()) { AssetVo outVo = new AssetVo { AssetCode = dataReader["asset_cd"].ToString(), AssetNo = int.Parse(dataReader["asset_no"].ToString()), AssetId = int.Parse(dataReader["asset_id"].ToString()), AssetName = dataReader["asset_name"].ToString(), AssetModel = dataReader["asset_model"].ToString(), AssetSuppiler = dataReader["asset_supplier"].ToString(), AssetType = dataReader["asset_type"].ToString(), AssetInvoice = dataReader["asset_invoice"].ToString(), AssetSerial = dataReader["asset_serial"].ToString(), AssetLife = double.Parse(dataReader["asset_life"].ToString()), AcquistionCost = double.Parse(dataReader["acquistion_cost"].ToString()), AcquistionDate = DateTime.Parse(dataReader["acquistion_date"].ToString()), RegistrationUserCode = dataReader["registration_user_cd"].ToString(), RegistrationDateTime = DateTime.Parse(dataReader["registration_date_time"].ToString()), FactoryCode = dataReader["factory_cd"].ToString(), LabelStatus = dataReader["label_status"].ToString(), AssetPO = dataReader["asset_po"].ToString() }; voList.add(outVo); } dataReader.Close(); return(voList); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { GA1ModelVo inVo = (GA1ModelVo)vo; //Update ShipDate StringBuilder sql = new StringBuilder(); sql.Append("update t_box_id set shipdate = :shipdate where boxid = :boxid"); DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("shipdate", inVo.ShipDate); sqlParameter.AddParameter("boxid", inVo.BoxID); sqlCommandAdapter.ExecuteNonQuery(sqlParameter); //Record Shipment if (!inVo.A90Shipping) { StringBuilder sql_record = new StringBuilder(); sql_record.Append("INSERT INTO t_shipment_record SELECT boxid, serialno, model, :shipdate, :shipstatus, lot FROM t_product_serial WHERE boxid = :boxid"); DbCommandAdaptor sqlCommandAdapter1 = base.GetDbCommandAdaptor(trxContext, sql_record.ToString()); DbParameterList sqlParameter1 = sqlCommandAdapter1.CreateParameterList(); sqlParameter1.AddParameter("boxid", inVo.BoxID); sqlParameter1.AddParameter("shipdate", inVo.ShipDate); sqlParameter1.AddParameter("shipstatus", inVo.ShipStatus); sqlCommandAdapter1.ExecuteNonQuery(sqlParameter1); GA1ModelVo outVo = new GA1ModelVo { AffectedCount = 1 }; return(outVo); } else { StringBuilder sql_record = new StringBuilder(); sql_record.Append("UPDATE t_shipment_record SET ship_date = :shipdate WHERE boxid = :boxid"); DbCommandAdaptor sqlCommandAdapter1 = base.GetDbCommandAdaptor(trxContext, sql_record.ToString()); DbParameterList sqlParameter1 = sqlCommandAdapter1.CreateParameterList(); sqlParameter1.AddParameter("boxid", inVo.BoxID); sqlParameter1.AddParameter("shipdate", inVo.ShipDate); sqlCommandAdapter1.ExecuteNonQuery(sqlParameter1); GA1ModelVo outVo = new GA1ModelVo { AffectedCount = 1 }; return(outVo); } }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { ProductionControllerGA1Vo inVo = (ProductionControllerGA1Vo)vo; StringBuilder sql = new StringBuilder(); ProductionControllerGA1Vo voList = new ProductionControllerGA1Vo(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); string sqlChung = " times, model,line, process,sum(inspectdata) inspectdata from (select a.serno,a.model,a.line, a.process,sum(inspectdata) inspectdata,a.inspectdate from " + inVo.TableName + " a left join " + inVo.TableName + "data b on a.serno = b.serno where model = :model and line = :line and a.inspectdate = b.inspectdate and a.inspectdate >= :datefrom and a.inspectdate <= :dateto group by a.serno,a.model,a.line, a.process,a.inspectdate order by a.inspectdate) tbl where process != 'MC_NOICHK' and inspectdate >= "; sqlParameter.AddParameter("model", inVo.ModelCode); sqlParameter.AddParameter("line", inVo.LineCode); sql.Append("select '01:00:00'" + sqlChung + " '" + inVo.Date + " 00:00:01' and inspectdate <= '" + inVo.Date + " 01:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '02:00:00'" + sqlChung + " '" + inVo.Date + " 01:00:01' and inspectdate <= '" + inVo.Date + " 02:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '03:00:00'" + sqlChung + " '" + inVo.Date + " 02:00:01' and inspectdate <= '" + inVo.Date + " 03:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '04:00:00'" + sqlChung + " '" + inVo.Date + " 03:00:01' and inspectdate <= '" + inVo.Date + " 04:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '05:00:00'" + sqlChung + " '" + inVo.Date + " 04:00:01' and inspectdate <= '" + inVo.Date + " 05:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '06:00:00'" + sqlChung + " '" + inVo.Date + " 05:00:01' and inspectdate <= '" + inVo.Date + " 06:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '07:00:00'" + sqlChung + " '" + inVo.Date + " 06:00:01' and inspectdate <= '" + inVo.Date + " 07:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '08:00:00'" + sqlChung + " '" + inVo.Date + " 07:00:01' and inspectdate <= '" + inVo.Date + " 08:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '09:00:00'" + sqlChung + " '" + inVo.Date + " 08:00:01' and inspectdate <= '" + inVo.Date + " 09:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '10:00:00'" + sqlChung + " '" + inVo.Date + " 09:00:01' and inspectdate <= '" + inVo.Date + " 10:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '11:00:00'" + sqlChung + " '" + inVo.Date + " 10:00:01' and inspectdate <= '" + inVo.Date + " 11:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '12:00:00'" + sqlChung + " '" + inVo.Date + " 11:00:01' and inspectdate <= '" + inVo.Date + " 12:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '13:00:00'" + sqlChung + " '" + inVo.Date + " 12:00:01' and inspectdate <= '" + inVo.Date + " 13:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '14:00:00'" + sqlChung + " '" + inVo.Date + " 13:00:01' and inspectdate <= '" + inVo.Date + " 14:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '15:00:00'" + sqlChung + " '" + inVo.Date + " 14:00:01' and inspectdate <= '" + inVo.Date + " 15:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '16:00:00'" + sqlChung + " '" + inVo.Date + " 15:00:01' and inspectdate <= '" + inVo.Date + " 16:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '17:00:00'" + sqlChung + " '" + inVo.Date + " 16:00:01' and inspectdate <= '" + inVo.Date + " 17:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '18:00:00'" + sqlChung + " '" + inVo.Date + " 17:00:01' and inspectdate <= '" + inVo.Date + " 18:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '19:00:00'" + sqlChung + " '" + inVo.Date + " 18:00:01' and inspectdate <= '" + inVo.Date + " 19:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '20:00:00'" + sqlChung + " '" + inVo.Date + " 19:00:01' and inspectdate <= '" + inVo.Date + " 20:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '21:00:00'" + sqlChung + " '" + inVo.Date + " 20:00:01' and inspectdate <= '" + inVo.Date + " 21:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '22:00:00'" + sqlChung + " '" + inVo.Date + " 21:00:01' and inspectdate <= '" + inVo.Date + " 22:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '23:00:00'" + sqlChung + " '" + inVo.Date + " 22:00:01' and inspectdate <= '" + inVo.Date + " 23:00:00' group by model,line, process"); sql.Append(" union "); sql.Append("select '23:59:59'" + sqlChung + " '" + inVo.Date + " 23:00:01' and inspectdate <= '" + inVo.Date + " 23:59:59' group by model,line, process"); sql.Append(" order by times, process "); sqlParameter.AddParameter("datefrom", inVo.DateFrom); sqlParameter.AddParameter("dateto", inVo.DateTo); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); DataSet ds = sqlCommandAdapter.ExecuteDataSet(sqlParameter); //execute SQL ProductionControllerGA1Vo outVo1 = new ProductionControllerGA1Vo { dt = ds.Tables[0], }; return(outVo1); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { JigRepairInformationVo inVo = (JigRepairInformationVo)vo; StringBuilder sql = new StringBuilder(); sql.Append(@"insert into t_jig_repair_info(jig_repair_cd, model_id, line_id, process_work_id, jig_cause_id, time_from, time_to, jig_current_status, jig_response_id, repair_result, jig_after_repair_status, place, registration_user_cd, registration_date_time, factory_cd)"); sql.Append(@"values(:jig_repair_cd, :model_id, :line_id, :process_work_id, :jig_cause_id, :time_from, :time_to, :jig_current_status, :jig_response_id, :repair_result, :jig_after_repair_status, :place, :registration_user_cd, now(), :factory_cd)"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("jig_repair_id", inVo.JigRepairId); sqlParameter.AddParameter("jig_repair_cd", inVo.JigRepairCode); sqlParameter.AddParameter("model_id", inVo.ModelId); sqlParameter.AddParameter("line_id", inVo.LineId); sqlParameter.AddParameter("process_work_id", inVo.ProcessWorkId); sqlParameter.AddParameter("jig_cause_id", inVo.JigCauseId); sqlParameter.AddParameter("time_from", inVo.TimeFrom); sqlParameter.AddParameter("time_to", inVo.TimeTo); sqlParameter.AddParameter("jig_current_status", inVo.JigCurrentStatus); sqlParameter.AddParameter("jig_response_id", inVo.JigResponseId); sqlParameter.AddParameter("repair_result", inVo.JigRepairResult); sqlParameter.AddParameter("jig_after_repair_status", inVo.JigAfterRepairStatus); sqlParameter.AddParameter("place", inVo.JigPlace); sqlParameter.AddParameter("registration_user_cd", inVo.RegistrationUserCode); sqlParameter.AddParameter("factory_cd", inVo.FactoryCode); //execute SQL JigRepairInformationVo outVo = new JigRepairInformationVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg) { ValueObjectList <ValueObjectList <ValueObject> > inVo = (ValueObjectList <ValueObjectList <ValueObject> >)arg; List <int> ItemList = new List <int>(); foreach (ValueObjectList <ValueObject> getItemVo in inVo.GetList()) { if ((((InspectionItemVo)getItemVo.GetList()[0]).InspectionItemId) != 0) { ItemList.Add(((InspectionItemVo)getItemVo.GetList()[0]).InspectionItemId); } } StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("select * "); sqlQuery.Append(" from m_inspection_specification"); sqlQuery.Append(" where factory_cd = :faccd "); sqlQuery.Append(" and inspection_item_id = ANY (:inspectionItemId) "); sqlQuery.Append(" order by inspection_specification_id "); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameterString("faccd", UserData.GetUserData().FactoryCode); sqlParameter.AddParameter("inspectionItemId", ItemList); //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); ValueObjectList <InspectionSpecificationVo> outVo = null; while (dataReader.Read()) { InspectionSpecificationVo currOutVo = new InspectionSpecificationVo(); currOutVo.InspectionSpecificationId = ConvertDBNull <int>(dataReader, "inspection_specification_id"); currOutVo.InspectionSpecificationCode = ConvertDBNull <string>(dataReader, "inspection_specification_cd"); currOutVo.InspectionSpecificationText = ConvertDBNull <string>(dataReader, "inspection_specification_text"); currOutVo.ValueFrom = ConvertDBNull <string>(dataReader, "value_from"); currOutVo.ValueTo = ConvertDBNull <string>(dataReader, "value_to"); currOutVo.Unit = ConvertDBNull <string>(dataReader, "unit"); currOutVo.OperatorFrom = ConvertDBNull <string>(dataReader, "operator_from"); currOutVo.OperatorTo = ConvertDBNull <string>(dataReader, "operator_to"); currOutVo.InspectionItemId = ConvertDBNull <int>(dataReader, "inspection_item_id"); currOutVo.SpecificationResultJudgeType = ConvertDBNull <int>(dataReader, "specification_result_judge_type"); if (outVo == null) { outVo = new ValueObjectList <InspectionSpecificationVo>(); } outVo.add(currOutVo); } dataReader.Close(); return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg) { //InspectionProcessVo inspectionProcessVo = (InspectionProcessVo)arg; ValueObjectList <ValueObjectList <ValueObject> > inVo = (ValueObjectList <ValueObjectList <ValueObject> >)arg; List <int> ProcessList = new List <int>(); foreach (ValueObjectList <ValueObject> getItemVo in inVo.GetList()) { if ((((InspectionProcessVo)getItemVo.GetList()[0]).InspectionProcessId) != 0) { ProcessList.Add(((InspectionProcessVo)getItemVo.GetList()[0]).InspectionProcessId); } } StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("select * "); sqlQuery.Append(" from m_inspection_item"); sqlQuery.Append(" where factory_cd = :faccd "); sqlQuery.Append(" and inspection_process_id = ANY (:inspectionprocessid) "); sqlQuery.Append(" order by inspection_item_id "); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameterString("faccd", UserData.GetUserData().FactoryCode); sqlParameter.AddParameter("inspectionprocessid", ProcessList); //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); ValueObjectList <InspectionItemVo> outVo = null; while (dataReader.Read()) { InspectionItemVo currOutVo = new InspectionItemVo(); currOutVo.InspectionItemId = ConvertDBNull <int>(dataReader, "inspection_item_id"); currOutVo.InspectionItemCode = ConvertDBNull <string>(dataReader, "inspection_item_cd"); currOutVo.InspectionItemName = ConvertDBNull <string>(dataReader, "inspection_item_name"); currOutVo.ParentInspectionItemId = ConvertDBNull <int>(dataReader, "parent_inspection_item_id"); currOutVo.InspectionProcessId = ConvertDBNull <int>(dataReader, "inspection_process_id"); currOutVo.InspectionItemMandatory = ConvertDBNull <int>(dataReader, "is_inspection_item_mandatory"); currOutVo.InspectionEmployeeMandatory = ConvertDBNull <int>(dataReader, "is_inspection_employee_mandatory"); currOutVo.InspectionMachineMandatory = ConvertDBNull <int>(dataReader, "is_inspection_machine_mandatory"); currOutVo.InspectionItemDataType = ConvertDBNull <int>(dataReader, "inspection_item_data_type"); currOutVo.InspectionResultItemDecimalDigits = ConvertDBNull <int>(dataReader, "inspection_item_result_input_decimal_digits"); currOutVo.DisplayOrder = ConvertDBNull <int>(dataReader, "display_order"); if (outVo == null) { outVo = new ValueObjectList <InspectionItemVo>(); } outVo.add(currOutVo); } dataReader.Close(); return(outVo); }
/// <summary> /// Execute the query /// </summary> /// <param name="trxContext"></param> /// <param name="vo"></param> /// <returns></returns> public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { LoginInVo inVo = (LoginInVo)vo; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append(" select distinct usr.user_cd,usr.user_name,cn.country,cn.language,cn.locale_id, "); sqlQuery.Append(" fac.factory_cd,usrfac.display_order,actrl.authority_control_cd"); sqlQuery.Append(" from m_mes_user usr "); sqlQuery.Append(" inner join m_country_language cn on usr.locale_id = cn.locale_id"); sqlQuery.Append(" inner join m_user_factory usrfac on usr.user_cd = usrfac.user_cd "); sqlQuery.Append(" inner join m_factory fac on usrfac.factory_cd = fac.factory_cd "); sqlQuery.Append(" left join m_mes_user_role usrrol on usrrol.user_cd = usr.user_cd "); sqlQuery.Append(" left join m_mes_role rol on rol.role_cd = usrrol.role_cd "); sqlQuery.Append(" left join m_role_authority_control rolactrl on rolactrl.role_cd = rol.role_cd "); sqlQuery.Append(" left join m_authority_control actrl on actrl.authority_control_cd = rolactrl.authority_control_cd "); sqlQuery.Append(" where usr.user_cd = :usercode "); sqlQuery.Append(" order by usrfac.display_order; "); LoginOutVo outVo = new LoginOutVo(); DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("usercode", inVo.InputUserId); //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); int recordCount = 0; while (dataReader.Read()) { if (recordCount == 0) { outVo.UserId = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["user_cd"]); outVo.UserName = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["user_name"]); outVo.LocaleId = Convert.ToInt32(dataReader["locale_id"]); outVo.CountryCode = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["country"]); outVo.LanguageCode = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["language"]); } if (!outVo.FactoryCodeList.Contains(DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["factory_cd"]))) { outVo.FactoryCodeList.Add(DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["factory_cd"])); } if (!outVo.ControlList.Contains(DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["authority_control_cd"]))) { outVo.ControlList.Add(DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["authority_control_cd"])); } recordCount += 1; } if (recordCount == 0) { outVo = null; } dataReader.Close(); return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { JigDrawVo inVo = (JigDrawVo)vo; StringBuilder sql = new StringBuilder(); sql.Append(@"update t_ncvp_jig_draw set jig_cd=:jig_cd, model_id=:model_id, process_id=:process_id, draw_id=:draw_id, time_receive=:time_receive, status=:status, local_supplier_id=:local_supplier_id, place=:place, revision=:revision, "); sql.Append(" where jig_id =:jig_id"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("jig_id", inVo.JigID); sqlParameter.AddParameter("jig_cd", inVo.JigBCode); sqlParameter.AddParameter("model_id", inVo.ModelId); sqlParameter.AddParameter("process_id", inVo.ProcessId); sqlParameter.AddParameter("draw_id", inVo.DrawId); sqlParameter.AddParameter("time_receive", inVo.TimeReceive); sqlParameter.AddParameter("status", inVo.Status); sqlParameter.AddParameter("local_supplier_id", inVo.SupplierName); sqlParameter.AddParameter("revision", inVo.Revision); sqlParameter.AddParameter("place", inVo.JigPlace); sqlParameter.AddParameter("registration_user_cd", inVo.RegistrationUserCode); sqlParameter.AddParameter("factory_cd", inVo.FactoryCode); //execute SQL JigDrawVo outVo = new JigDrawVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
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 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 "); //inspectdata != 0 and a.process ='FA_IP' and line = 'L01' sql.Append(@" a.inspectdate >= :datefrom and a.inspectdate <= :dateto and inspectdata != 0 and b.inspectdate >= :datefrom and b.inspectdate <= :dateto "); sqlParameter.AddParameter("datefrom", inVo.DateFrom); sqlParameter.AddParameter("dateto", inVo.DateTo); //sql.Append(@"select distinct a.process 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); } //sql.Append(@" order by a.process"); if (!string.IsNullOrEmpty(inVo.ModelCode)) { sql.Append(@" and a.model =:model"); sqlParameter.AddParameterString("model", inVo.ModelCode); } if (!string.IsNullOrEmpty(inVo.ProcessName)) { sql.Append(@" and a.process =:process"); sqlParameter.AddParameterString("process", inVo.ProcessName); } //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 a.inspectdate"); 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() inspectdata InspecData = dataReader["inspectdata"].ToString() }; voList.add(outVo); } dataReader.Close(); return(voList); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg) { //InspectionTestInstructionVo inVo = (InspectionTestInstructionVo)arg; ValueObjectList <ValueObjectList <ValueObject> > inVo = (ValueObjectList <ValueObjectList <ValueObject> >)arg; List <int> InspectionTestInstructionList = new List <int>(); foreach (ValueObjectList <ValueObject> getTestInstructionVo in inVo.GetList()) { if ((((InspectionTestInstructionVo)getTestInstructionVo.GetList()[0]).InspectionTestInstructionId) != 0) { InspectionTestInstructionList.Add(((InspectionTestInstructionVo)getTestInstructionVo.GetList()[0]).InspectionTestInstructionId); } } StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("select"); sqlQuery.Append(" itinsth.inspection_test_instruction_id,"); sqlQuery.Append(" itinsth.inspection_test_instruction_cd,"); sqlQuery.Append(" itinsth.inspection_test_instruction_text,"); sqlQuery.Append(" itinstd.inspection_test_instruction_detail_id,"); sqlQuery.Append(" itinstd.inspection_test_instruction_detail_cd,"); sqlQuery.Append(" itinstd.inspection_test_instruction_detail_text,"); sqlQuery.Append(" itinstd.inspection_test_instruction_detail_result_count,"); sqlQuery.Append(" itinstd.inspection_test_instruction_detail_machine_text"); sqlQuery.Append(" from m_inspection_test_instruction itinsth"); sqlQuery.Append(" inner join m_inspection_test_instruction_detail itinstd"); sqlQuery.Append(" on itinstd.inspection_test_instruction_id = itinsth.inspection_test_instruction_id"); sqlQuery.Append(" where itinsth.factory_cd = :faccd "); //if (inVo.InspectionTestInstructionDetailCode != null) //{ // sqlQuery.Append(" and itinstd.inspection_test_instruction_detail_cd like :inspectiontestinstructiondetailcd "); //} //if (inVo.InspectionTestInstructionDetailText != null) //{ // sqlQuery.Append(" and itinstd.inspection_test_instruction_detail_text like :inspectiontestinstructiondetailtext "); //} //if (inVo.InspectionTestInstructionId > 0) //{ sqlQuery.Append(" and itinstd.inspection_test_instruction_id = ANY(:inspectiontestinstructionid) "); //} //if (inVo.InspectionTestInstructionDetailId > 0) //{ // sqlQuery.Append(" and itinstd.inspection_test_instruction_detail_id = :inspectiontestinstructiondetailid "); //} sqlQuery.Append(" order by itinstd.inspection_test_instruction_detail_cd"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameterString("faccd", UserData.GetUserData().FactoryCode); //if (inVo.InspectionTestInstructionDetailCode != null) //{ // sqlParameter.AddParameterString("inspectiontestinstructiondetailcd", inVo.InspectionTestInstructionDetailCode + "%"); //} //if (inVo.InspectionTestInstructionDetailText != null) //{ // sqlParameter.AddParameterString("inspectiontestinstructiondetailtext", inVo.InspectionTestInstructionDetailText + "%"); //} //if (inVo.InspectionTestInstructionId > 0) //{ sqlParameter.AddParameter("inspectiontestinstructionid", InspectionTestInstructionList); //} //if (inVo.InspectionTestInstructionDetailId > 0) //{ // sqlParameter.AddParameterInteger("inspectiontestinstructiondetailid", inVo.InspectionTestInstructionDetailId); //} //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); ValueObjectList <InspectionTestInstructionVo> outVo = null; while (dataReader.Read()) { InspectionTestInstructionVo currOutVo = new InspectionTestInstructionVo(); currOutVo.InspectionTestInstructionId = ConvertDBNull <int>(dataReader, "inspection_test_instruction_id"); currOutVo.InspectionTestInstructionCode = ConvertDBNull <string>(dataReader, "inspection_test_instruction_cd"); currOutVo.InspectionTestInstructionText = ConvertDBNull <string>(dataReader, "inspection_test_instruction_text"); currOutVo.InspectionTestInstructionDetailId = ConvertDBNull <int>(dataReader, "inspection_test_instruction_detail_id"); currOutVo.InspectionTestInstructionDetailCode = ConvertDBNull <string>(dataReader, "inspection_test_instruction_detail_cd"); currOutVo.InspectionTestInstructionDetailText = ConvertDBNull <string>(dataReader, "inspection_test_instruction_detail_text"); currOutVo.InspectionTestInstructionDetailResultCount = ConvertDBNull <int>(dataReader, "inspection_test_instruction_detail_result_count"); currOutVo.InspectionTestInstructionDetailMachine = ConvertDBNull <string>(dataReader, "inspection_test_instruction_detail_machine_text"); if (outVo == null) { outVo = new ValueObjectList <InspectionTestInstructionVo>(); } outVo.add(currOutVo); } dataReader.Close(); return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { 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 b.a90_barcode,a90_date+a90_time datetimes,b.a90_noise_status from (select max(oid) oid,a90_barcode from t_checkpusha90 where a90_date+a90_time >= '2019-03-21 06:00:00' and a90_date+a90_time <= '2019-03-21 08:30:00' and a90_line = 'L01' and a90_model = 'LDP_5SG' group by a90_barcode) a left join t_checkpusha90 b on a.oid = b.oid where 1=1 "; sqlParameter.AddParameter("a90_model", inVo.ModelCode); sqlParameter.AddParameter("line", inVo.LineCode); if (inVo.change) { sqlChung += " and a90_noise_status = 'OK' "; } else { sqlChung += " and a90_noise_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,count(*) from (select a.a90_barcode,a.datetimes,a.a90_oqc_data from (select row_number() over(partition by a90_barcode order by a90_date+a90_time) id, a90_barcode,a90_date+a90_time datetimes,a90_oqc_data 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_oqc_data 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 != '' and a.a90_oqc_data = 'NG' ) tbl where datetimes >= "; sqlParameter.AddParameter("a90_model", inVo.ModelCode); sqlParameter.AddParameter("line", inVo.LineCode); 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) { DocumentVo inVo = (DocumentVo)vo; StringBuilder sql = new StringBuilder(); sql.Append(@"insert into t_document(document_cd, document_name, document_type, model_id, department, groups, revision, update_date_time, registration_user_cd, registration_date_time, factory_cd)"); sql.Append(@"values(:document_cd, :document_name, :document_type, :model_id, :department, :groups, :revision, now(), :registration_user_cd, now(), :factory_cd)"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("document_id", inVo.DocumentID); sqlParameter.AddParameter("document_cd", inVo.DocumentCode); sqlParameter.AddParameter("document_name", inVo.DocumentName); sqlParameter.AddParameter("document_type", inVo.DocumentType); sqlParameter.AddParameter("model_id", inVo.ModelID); sqlParameter.AddParameter("department", inVo.Department); sqlParameter.AddParameter("groups", inVo.Group); sqlParameter.AddParameter("revision", inVo.Revision); sqlParameter.AddParameter("update_date_time", inVo.TimeFrom); sqlParameter.AddParameter("registration_user_cd", inVo.RegistrationUserCode); sqlParameter.AddParameter("registration_date_time", inVo.RegistrationDateTime); sqlParameter.AddParameter("factory_cd", inVo.FactoryCode); //execute SQL DocumentVo outVo = new DocumentVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { PersonVo inVo = (PersonVo)vo; StringBuilder sql = new StringBuilder(); sql.Append("update t_productioncontroller_person set model_cd =:model_cd , line_cd =:line_cd ,datetimes =:datetimes ,factory_cd =:factory_cd ,building_cd =:building_cd , "); sql.Append("lot_numbers =:lot_numbers , leader_name =:leader_name , shift =:shift , plan_pro =:plan_pro , plan_st =:plan_st , actual_st =:actual_st , doing_co =:doing_co , absent_co =:absent_co , doing_ra =:doing_ra ,"); sql.Append("absent_ra =:absent_ra ,doing_ca =:doing_ca , absent_ca =:absent_ca , doing_ba =:doing_ba , absent_ba =:absent_ba ,doing_ma =:doing_ma , absent_ma =:absent_ma , timeover =:timeover , timeoffset =:timeoffset , sum_timedoing =:sum_timedoing , registration_user_cd=:registration_user_cd "); sql.Append(" where person_id=:person_id "); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("person_id", inVo.PersonId); sqlParameter.AddParameterString("model_cd", inVo.Model); sqlParameter.AddParameterString("line_cd", inVo.Line); sqlParameter.AddParameterDateTime("datetimes", inVo.DateTimes); sqlParameter.AddParameterString("factory_cd", inVo.FactoryCode); sqlParameter.AddParameterString("building_cd", inVo.BuildingCode); sqlParameter.AddParameterString("lot_numbers", inVo.LotNumber); sqlParameter.AddParameterString("leader_name", inVo.LeaderName); sqlParameter.AddParameterInteger("shift", inVo.Shift); sqlParameter.AddParameterInteger("plan_pro", inVo.PlanPro); sqlParameter.AddParameter("plan_st", inVo.PlanST); sqlParameter.AddParameter("actual_st", inVo.ActualSt); sqlParameter.AddParameter("doing_co", inVo.DoCo); sqlParameter.AddParameter("doing_ra", inVo.DoRa); sqlParameter.AddParameter("doing_ca", inVo.DoCa); sqlParameter.AddParameter("doing_ba", inVo.DoBa); sqlParameter.AddParameter("doing_ma", inVo.DoMa); sqlParameter.AddParameter("absent_co", inVo.AbsentCo); sqlParameter.AddParameter("absent_ra", inVo.AbsentRa); sqlParameter.AddParameter("absent_ca", inVo.AbsentCa); sqlParameter.AddParameter("absent_ba", inVo.AbsentBa); sqlParameter.AddParameter("absent_ma", inVo.AbsentMa); sqlParameter.AddParameter("timeover", inVo.TimeOver); sqlParameter.AddParameter("timeoffset", inVo.TimeOffset); sqlParameter.AddParameter("sum_timedoing", inVo.TimeTotal); sqlParameter.AddParameter("registration_user_cd", inVo.RegistrationUserCode); //execute SQL PersonVo outVo = new PersonVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg) { MoldVo inVo = (MoldVo)arg; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("Insert into m_mold"); sqlQuery.Append(" ( "); sqlQuery.Append(" mold_cd, "); sqlQuery.Append(" mold_name, "); sqlQuery.Append(" mold_type_id, "); sqlQuery.Append(" width , depth , height , weight , "); sqlQuery.Append(" production_date_time , life_shot_count, "); sqlQuery.Append(" comment, "); sqlQuery.Append(" registration_user_cd, "); sqlQuery.Append(" registration_date_time, "); sqlQuery.Append(" factory_cd "); sqlQuery.Append(" ) "); sqlQuery.Append("VALUES "); sqlQuery.Append(" ( "); sqlQuery.Append(" :moldcd ,"); sqlQuery.Append(" :moldname ,"); sqlQuery.Append(" :moldtypeid ,"); sqlQuery.Append(" :width , :depth , :height , :weight, "); sqlQuery.Append(" :pdt, :lfscount ,"); sqlQuery.Append(" :comment ,"); sqlQuery.Append(" :registrationusercode ,"); sqlQuery.Append(" now() ,"); sqlQuery.Append(" :factorycode "); sqlQuery.Append(" ) "); sqlQuery.Append(" RETURNING mold_id "); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameterString("moldcd", inVo.MoldCode); sqlParameter.AddParameterString("moldname", inVo.MoldName); sqlParameter.AddParameterInteger("moldtypeid", inVo.MoldTypeId); sqlParameter.AddParameter("width", inVo.Width); sqlParameter.AddParameter("depth", inVo.Depth); sqlParameter.AddParameter("height", inVo.Height); sqlParameter.AddParameter("weight", inVo.Weight); if (inVo.ProductionDate != System.DateTime.MinValue) { sqlParameter.AddParameterDateTime("pdt", inVo.ProductionDate); } else { sqlParameter.AddParameter("pdt", System.DBNull.Value); } sqlParameter.AddParameter("lfscount", inVo.LifeShotCount); sqlParameter.AddParameter("comment", inVo.Comment); sqlParameter.AddParameterString("registrationusercode", UserData.GetUserData().UserCode); sqlParameter.AddParameterDateTime("registrationdatetime", trxContext.ProcessingDBDateTime); sqlParameter.AddParameterString("factorycode", UserData.GetUserData().FactoryCode); MoldVo outVo = new MoldVo();// {AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter)}; outVo.MoldId = (int?)sqlCommandAdapter.ExecuteScalar(sqlParameter) ?? 0; if (outVo != null && outVo.MoldId > 0) { outVo.AffectedCount = 1; } return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { AccountMainVo inVo = (AccountMainVo)vo; StringBuilder sql = new StringBuilder(); sql.Append(@"insert into t_account_main(asset_id, qty, unit_id, account_code_id, account_location_id, rank_id, comment_data, depreciation_start, depreciation_end, current_depreciation, monthly_depreciation, accum_depreciation_now, net_value, location_id, user_location_id, registration_user_cd, registration_date_time, factory_cd)"); sql.Append(@"values(:asset_id, :qty,:unit_id, :account_code_id, :account_location_id, :rank_id, :comment_data, :depreciation_start, :depreciation_end, :current_depreciation, :monthly_depreciation, :accum_depreciation_now, :net_value, :location_id, :user_location_id, :registration_user_cd, :registration_date_time, :factory_cd)"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("asset_id", inVo.AssetId); sqlParameter.AddParameter("qty", inVo.QTY); sqlParameter.AddParameter("unit_id", inVo.UnitId); sqlParameter.AddParameter("account_code_id", inVo.AccountCodeId); sqlParameter.AddParameter("account_location_id", inVo.AccountLocationId); sqlParameter.AddParameter("rank_id", inVo.RankId); sqlParameter.AddParameter("comment_data", inVo.CommnetsData); sqlParameter.AddParameter("depreciation_start", inVo.StartDepreciation); sqlParameter.AddParameter("depreciation_end", inVo.EndDepreciation); sqlParameter.AddParameter("current_depreciation", inVo.CurrentDepreciation); sqlParameter.AddParameter("monthly_depreciation", inVo.MonthlyDepreciation); sqlParameter.AddParameter("accum_depreciation_now", inVo.AccumDepreciation); sqlParameter.AddParameter("net_value", inVo.NetValue); sqlParameter.AddParameter("location_id", inVo.LocationId); sqlParameter.AddParameter("user_location_id", inVo.UserLocationId); sqlParameter.AddParameter("registration_user_cd", inVo.RegistrationUserCode); sqlParameter.AddParameter("registration_date_time", inVo.RegistrationDateTime); sqlParameter.AddParameter("factory_cd", inVo.FactoryCode); //execute SQL AccountMainVo outVo = new AccountMainVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { OvenBarcodeVo inVo = (OvenBarcodeVo)vo; StringBuilder sql = new StringBuilder(); ValueObjectList <OvenBarcodeVo> voList = new ValueObjectList <OvenBarcodeVo>(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append(@"select dates+times as datetime,* from t_ovenmachine where 1=1 "); if (!String.IsNullOrEmpty(inVo.Model)) { sql.Append(@" and model = :model "); sqlParameter.AddParameterString("model", inVo.Model); } if (!String.IsNullOrEmpty(inVo.Line)) { sql.Append(@" and line_cd = :line_cd "); sqlParameter.AddParameterString("line_cd", inVo.Line); } if (!String.IsNullOrEmpty(inVo.Barcode)) { sql.Append(@" and barcode = :barcode "); sqlParameter.AddParameterString("barcode", inVo.Barcode); } //if (!String.IsNullOrEmpty(inVo.Lower)) //{ // sql.Append(@" and temperature >= :lower "); // sqlParameter.AddParameterInteger("lower", int.Parse(inVo.Lower)); //} //if (!String.IsNullOrEmpty(inVo.Upper)) //{ // sql.Append(@" and temperature <= :upper "); // sqlParameter.AddParameterInteger("upper",int.Parse(inVo.Upper)); //} sql.Append(@" and dates+times between :timefrom and :timeto order by dates,barcode,times "); sqlParameter.AddParameter("timefrom", inVo.DateTimeForm); sqlParameter.AddParameter("timeto", inVo.DateTimeTo); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); while (dataReader.Read()) { OvenBarcodeVo outVo = new OvenBarcodeVo { Date = DateTime.Parse(dataReader["datetime"].ToString()), Times = dataReader["times"].ToString(), FactoryCode = dataReader["factory_cd"].ToString(), Model = dataReader["model"].ToString(), Line = dataReader["line_cd"].ToString(), Barcode = dataReader["barcode"].ToString(), Temperature = dataReader["temperature"].ToString(), Drying = int.Parse(dataReader["drying"].ToString()), Status = dataReader["status"].ToString(), }; voList.add(outVo); } dataReader.Close(); return(voList); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { MovingMachineVTVo inVo = (MovingMachineVTVo)vo; StringBuilder sql = new StringBuilder(); sql.Append(@"insert into t_vt_moving(machine_serial, factory_tranfer_cd , factory_received_cd , bg_cd, m_cd, t_cd, th_cd, status, comments_machine, reason_tranfer, confirm_received, registration_user_cd, registration_date_time, factory_cd) "); sql.Append(@"values(:machine_serial , :factory_tranfer_cd, :factory_received_cd , :bg_cd , :m_cd, :t_cd, :th_cd, :status, :comments_machine, :reason_tranfer, :confirm_received, :registration_user_cd, :registration_date_time, :factory_cd)"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("machine_serial", inVo.MachineSerial); sqlParameter.AddParameter("factory_tranfer_cd", inVo.TranferFactoryName); sqlParameter.AddParameter("factory_received_cd", inVo.ReceivedFactoryName); sqlParameter.AddParameter("bg_cd", inVo.BGCode); sqlParameter.AddParameter("m_cd", inVo.MCode); sqlParameter.AddParameter("t_cd", inVo.TCode); sqlParameter.AddParameter("th_cd", inVo.THCode); sqlParameter.AddParameter("status", inVo.Status); sqlParameter.AddParameter("comments_machine", inVo.CommentsMachine); sqlParameter.AddParameter("reason_tranfer", inVo.ReasonTranfer); sqlParameter.AddParameter("confirm_received", inVo.ConfirmReceived); sqlParameter.AddParameter("registration_user_cd", UserData.GetUserData().UserCode); sqlParameter.AddParameter("factory_cd", UserData.GetUserData().FactoryCode); sqlParameter.AddParameter("registration_date_time", inVo.RegistrationDateTime); //execute SQL MovingMachineVTVo outVo = new MovingMachineVTVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { DrawingVo inVo = (DrawingVo)vo; StringBuilder sql = new StringBuilder(); sql.Append(@"insert into t_draw(device_cd, draw_id, model_id, machine_id, draw_type, time_record, department, revision, registration_user_cd, registration_date_time, factory_cd)"); sql.Append(@"values(:device_cd, :draw_id, :model_id, :machine_id, :draw_type, now(), :department, :revision, :registration_user_cd, now(), :factory_cd)"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("device_id", inVo.DeviceID); sqlParameter.AddParameter("device_cd", inVo.DeviceCode); sqlParameter.AddParameter("draw_id", inVo.DrawId); sqlParameter.AddParameter("model_id", inVo.ModelId); sqlParameter.AddParameter("machine_id", inVo.MachineID); sqlParameter.AddParameter("draw_type", inVo.DrawType); sqlParameter.AddParameter("time_record", inVo.TimeFrom); sqlParameter.AddParameter("department", inVo.Department); sqlParameter.AddParameter("revision", inVo.Revision); sqlParameter.AddParameter("registration_user_cd", inVo.RegistrationUserCode); sqlParameter.AddParameter("registration_date_time", inVo.RegistrationDateTime); sqlParameter.AddParameter("factory_cd", inVo.FactoryCode); //execute SQL DrawingVo outVo = new DrawingVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { WarehouseVTVo inVo = (WarehouseVTVo)vo; StringBuilder sql = new StringBuilder(); sql.Append(@"insert into t_vt_machine(rfid_cd ,machine_cd, machine_name , machine_qty , machine_serial, machine_model, machine_location, machine_suppiler, machine_invoice, machine_costvalue, machine_status, registration_user_cd, registration_date_time, factory_cd, time_check_fi) "); sql.Append(@"values(:rfid_cd , :machine_cd, :machine_name , :machine_qty , :machine_serial, :machine_model, :machine_location, :machine_suppiler, :machine_invoice, :machine_costvalue, :machine_status, :registration_user_cd, now(), :factory_cd, :time_check_fi)"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameter("rfid_cd", inVo.RFId); sqlParameter.AddParameter("machine_cd", inVo.MachineCode); sqlParameter.AddParameter("machine_name", inVo.MachineName); sqlParameter.AddParameter("machine_qty", inVo.MachineQty); sqlParameter.AddParameter("machine_serial", inVo.MachineSerial); sqlParameter.AddParameter("machine_model", inVo.MachineModel); sqlParameter.AddParameter("machine_location", inVo.MachineLocation); sqlParameter.AddParameter("machine_suppiler", inVo.MachineSupplier); sqlParameter.AddParameter("machine_invoice", inVo.MachineInvoice); sqlParameter.AddParameter("machine_costvalue", double.Parse(inVo.MachineCostValue)); sqlParameter.AddParameter("registration_user_cd", UserData.GetUserData().UserCode); sqlParameter.AddParameter("factory_cd", UserData.GetUserData().FactoryCode); sqlParameter.AddParameterInteger("time_check_fi", inVo.TimeCheck); sqlParameter.AddParameter("machine_status", inVo.MachineStatus); //execute SQL WarehouseVTVo outVo = new WarehouseVTVo { AffectedCount = sqlCommandAdapter.ExecuteNonQuery(sqlParameter) }; return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { FunctionDeptVo inVo = (FunctionDeptVo)vo; StringBuilder sql = new StringBuilder(); ValueObjectList <FunctionDeptVo> voList = new ValueObjectList <FunctionDeptVo>(); DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, string.Empty); DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sql.Append(@"select c.vcb_functiondept_id, c.vcb_functiondept_cd, c.vcb_functiondept_name, c.user_name, c.registration_user_cd, c.registration_date_time, c.factory_cd, b.vcb_department_cd, b.vcb_department_name from vcb_functiondept c left join vcb_department b on c.vcb_department_id = b.vcb_department_id Where 1=1 "); if (!string.IsNullOrEmpty(inVo.FunctionDeptCode)) { sql.Append(" and c.vcb_functiondept_cd = :vcb_functiondept_cd "); sqlParameter.AddParameterString("vcb_functiondept_cd", inVo.FunctionDeptCode); } if (!string.IsNullOrEmpty(inVo.FunctionDeptName)) { sql.Append(" and c.vcb_functiondept_name = :vcb_functiondept_name "); sqlParameter.AddParameterString("vcb_functiondept_name", inVo.FunctionDeptName); } if (!string.IsNullOrEmpty(inVo.DepartmentCode)) { sql.Append(" and b.vcb_department_cd = :vcb_department_cd "); sqlParameter.AddParameterString("vcb_department_cd", inVo.DepartmentCode); } //if (!string.IsNullOrEmpty(inVo.UserName)) //{ // sql.Append(" and c.user_name = :user_name "); // sqlParameter.AddParameterString("user_name", inVo.UserName); //} if (inVo.HienThi == 1) { sql.Append(" and c.user_name != :tenhienthi"); sqlParameter.AddParameter("tenhienthi", inVo.TenHienThi); } //create command //DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sql.ToString()); //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); while (dataReader.Read()) { FunctionDeptVo outVo = new FunctionDeptVo { DepartmentCode = dataReader["vcb_department_cd"].ToString(), DepartmentName = dataReader["vcb_department_name"].ToString(), FunctionDeptId = int.Parse(dataReader["vcb_functiondept_id"].ToString()), FunctionDeptCode = dataReader["vcb_functiondept_cd"].ToString(), FunctionDeptName = dataReader["vcb_functiondept_name"].ToString(), UserName = dataReader["user_name"].ToString(), RegistrationUserCode = dataReader["registration_user_cd"].ToString(), RegistrationDateTime = DateTime.Parse(dataReader["registration_date_time"].ToString()), FactoryCode = dataReader["factory_cd"].ToString() }; voList.add(outVo); } dataReader.Close(); return(voList); }