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