public override ValueObject Execute(TransactionContext trxContext, ValueObject arg) { SapUserVo inVo = (SapUserVo)arg; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("Select sapusr.sap_user_id,sapusr.mes_user_cd,usr.user_name,sapusr.sap_user,sapusr.sap_password"); sqlQuery.Append(" from m_sap_user sapusr"); sqlQuery.Append(" inner join m_mes_user usr on usr.user_cd = sapusr.mes_user_cd "); sqlQuery.Append(" where 1 = 1 "); if (inVo.MesUserCode != null) { sqlQuery.Append(" and usr.user_cd = :usercode "); } if (inVo.SapUser != null) { sqlQuery.Append(" and sapusr.sap_user = :sapuser "); } sqlQuery.Append(" order by sap_user_id "); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); if (inVo.MesUserCode != null) { sqlParameter.AddParameterString("usercode", inVo.MesUserCode); } if (inVo.SapUser != null) { sqlParameter.AddParameterString("sapuser", inVo.SapUser); } //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); SapUserVo outVo = new SapUserVo(); while (dataReader.Read()) { SapUserVo currVo = new SapUserVo(); currVo.SapUserId = Convert.ToInt32(dataReader["sap_user_id"]); currVo.MesUserCode = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["mes_user_cd"]); currVo.UserName = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["user_name"]); currVo.SapUser = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["sap_user"]); currVo.SapPassWord = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["sap_password"]); outVo.SapUserListVo.Add(currVo); } dataReader.Close(); return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg) { ProcessWorkLocalSupplierVo inVo = (ProcessWorkLocalSupplierVo)arg; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("Select"); sqlQuery.Append(" pw.process_work_id,"); sqlQuery.Append(" pw.process_work_name,"); sqlQuery.Append(" t.local_supplier_id"); sqlQuery.Append(" from m_process_work pw "); sqlQuery.Append(" left join "); sqlQuery.Append(" (select pws.process_work_id, pws.local_supplier_id from m_process_work_local_supplier pws "); sqlQuery.Append(" inner join m_local_supplier ls on ls.local_supplier_id = pws.local_supplier_id "); sqlQuery.Append(" where pws.local_supplier_id = :localsupplierid ) t "); sqlQuery.Append(" on t.process_work_id = pw.process_work_id "); //if (inVo.LocalSupplierId > 0) //{ // sqlQuery.Append(" and pws.local_supplier_id = :localsupplierid"); //} //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); if (inVo.LocalSupplierId > 0) { sqlParameter.AddParameterInteger("localsupplierid", inVo.LocalSupplierId); } //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); ProcessWorkLocalSupplierVo outVo = new ProcessWorkLocalSupplierVo(); while (dataReader.Read()) { ProcessWorkLocalSupplierVo currOutVo = new ProcessWorkLocalSupplierVo { LocalSupplierId = Convert.ToInt32(DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["local_supplier_id"])), ProcessWorkId = Convert.ToInt32(dataReader["process_work_id"]), ProcessWorkName = dataReader["process_work_name"].ToString(), }; outVo.ProcessWorkLocalSupplierListVo.Add(currOutVo); } dataReader.Close(); return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg) { ProcessWorksVo inVo = (ProcessWorksVo)arg; StringBuilder sqlQuery = new StringBuilder(); ValueObjectList <ProcessWorksVo> voList = new ValueObjectList <ProcessWorksVo>(); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlQuery.Append("Select pw.process_work_id, pw.process_work_cd, pw.process_work_name, md.model_cd, pr.process_name, mc.machine_name from m_process_work pw left join m_process pr on pr.process_id = pw.process_id left join m_machine mc on mc.machine_id = pw.machine_id left join m_model md on md.model_id = pw.model_id where 1 = 1"); if (!String.IsNullOrEmpty(inVo.ProcessWorkCode)) { sqlQuery.Append(" and pw.process_work_cd like :processworkcd "); sqlParameter.AddParameterString("processworkcd", "%" + inVo.ProcessWorkCode + "%"); } if (!String.IsNullOrEmpty(inVo.ProcessWorkName)) { sqlQuery.Append(" and pw.process_work_name like :processworkname "); sqlParameter.AddParameterString("processworkname", "%" + inVo.ProcessWorkName + "%"); } if (inVo.MachineID != 0) { sqlQuery.Append(" and mc.machine_id = :machine_id "); sqlParameter.AddParameterInteger("machine_id", inVo.MachineID); } sqlQuery.Append(" order by pw.process_work_id"); sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); while (dataReader.Read()) { ProcessWorksVo OutVo = new ProcessWorksVo(); { OutVo.ProcessWorkId = ConvertDBNull <int>(dataReader, "process_work_id"); OutVo.ProcessWorkCode = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_work_cd"]); OutVo.ProcessWorkName = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_work_name"]); OutVo.Assy = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_name"]); OutVo.Model = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["model_cd"]); OutVo.Machine = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["machine_name"]); } voList.add(OutVo); } dataReader.Close(); return(voList); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg) { StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("Select sapusr.mes_user_cd,sapusr.sap_user,sapusr.sap_password"); sqlQuery.Append(" from m_sap_user sapusr"); sqlQuery.Append(" inner join m_mes_user usr on usr.user_cd = sapusr.mes_user_cd"); sqlQuery.Append(" inner join m_user_factory uf on usr.user_cd = uf.user_cd"); sqlQuery.Append(" where usr.user_cd = :usercode "); sqlQuery.Append(" and uf.factory_cd = :factorycode "); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameterString("usercode", trxContext.UserData.UserCode); sqlParameter.AddParameterString("factorycode", trxContext.UserData.FactoryCode); //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); SapUserVo outVo = null; while (dataReader.Read()) { outVo = new SapUserVo(); outVo.MesUserCode = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["mes_user_cd"]); outVo.SapUser = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["sap_user"]); outVo.SapPassWord = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["sap_password"]); } dataReader.Close(); return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg) { ProcessWorkVo inVo = (ProcessWorkVo)arg; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("Select pw.process_work_id, pw.process_work_cd, pw.process_work_name, pw.is_phantom, pw.line_machine_selection, pw.display_order, "); sqlQuery.Append(" CASE pw.is_phantom WHEN '1' THEN 'Phantom'"); sqlQuery.Append(" WHEN '0' THEN 'Standard' End as phantom_value, "); sqlQuery.Append(" pc.process_id, pc.process_cd, pc.process_name "); sqlQuery.Append(" from m_process_work pw "); sqlQuery.Append("inner join m_process pc on pc.process_id = pw.process_id "); sqlQuery.Append(" where pw.factory_cd = :factcd "); if (inVo.ProcessWorkId > 0) { sqlQuery.Append(" and pw.process_work_id = :processworkid "); } if (inVo.ProcessWorkCode != null) { sqlQuery.Append(" and pw.process_work_cd like :processworkcd "); } if (inVo.ProcessWorkName != null) { sqlQuery.Append(" and pw.process_work_name like :processworkname "); } if (inVo.ProcessId != 0) { sqlQuery.Append(" and pc.process_id = :processid "); } sqlQuery.Append(" order by pw.process_work_id"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); sqlParameter.AddParameterString("factcd", UserData.GetUserData().FactoryCode); if (inVo.ProcessWorkId > 0) { sqlParameter.AddParameterInteger("processworkid", inVo.ProcessWorkId); } if (inVo.ProcessWorkCode != null) { sqlParameter.AddParameterString("processworkcd", inVo.ProcessWorkCode + "%"); } if (inVo.ProcessWorkName != null) { sqlParameter.AddParameterString("processworkname", inVo.ProcessWorkName + "%"); } if (inVo.ProcessId != 0) { sqlParameter.AddParameterInteger("processid", inVo.ProcessId); } //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); ProcessWorkVo outVo = new ProcessWorkVo(); while (dataReader.Read()) { ProcessWorkVo currOutVo = new ProcessWorkVo(); { currOutVo.ProcessWorkId = ConvertDBNull <int>(dataReader, "process_work_id"); currOutVo.ProcessWorkCode = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_work_cd"]); currOutVo.ProcessWorkName = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_work_name"]); currOutVo.ProcessId = ConvertDBNull <int>(dataReader, "process_id"); currOutVo.ProcessCode = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_cd"]); currOutVo.ProcessName = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_name"]); currOutVo.IsPhantom = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["is_phantom"]); currOutVo.IsPhantomDisplay = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["phantom_value"]); currOutVo.LineMachineSelection = ConvertDBNull <int>(dataReader, "line_machine_selection"); currOutVo.DisplayOrder = ConvertDBNull <int>(dataReader, "display_order"); } outVo.ProcessWorkListVo.Add(currOutVo); } dataReader.Close(); return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject arg) { UserVo inVo = (UserVo)arg; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("Select usr.user_cd,usr.user_name,usr.multi_login_flag,"); sqlQuery.Append(" usr.locale_id, usr.registrated_factory_cd,cn.country,cn.language"); sqlQuery.Append(" from m_mes_user usr"); sqlQuery.Append(" inner join m_country_language cn on usr.locale_id = cn.locale_id"); sqlQuery.Append(" where 1 = 1 "); if (inVo.UserCode != null) { sqlQuery.Append(" and user_cd like :usercode "); } if (inVo.UserName != null) { sqlQuery.Append(" and user_name like :username "); } //if (inVo.Country != null) { sqlQuery.Append(" and country = :country ");} if (inVo.LocaleId > 0) { sqlQuery.Append(" and usr.locale_id = :locid "); } //if (inVo.Language != null) { sqlQuery.Append(" and language = :language ");} if (inVo.RegistrationFactoryCode != null) { sqlQuery.Append(" and registrated_factory_cd = :registrationfactorycode "); } sqlQuery.Append(" order by user_cd"); //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); if (inVo.UserCode != null) { sqlParameter.AddParameterString("usercode", inVo.UserCode + "%"); } if (inVo.UserName != null) { sqlParameter.AddParameterString("username", inVo.UserName + "%"); } if (inVo.LocaleId > 0) { sqlParameter.AddParameterInteger("locid", inVo.LocaleId); } //if (inVo.Language != null) //{ // sqlParameter.AddParameterString("language", inVo.Language); //} if (inVo.RegistrationFactoryCode != null) { sqlParameter.AddParameterString("registrationfactorycode", inVo.RegistrationFactoryCode); } //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); UserVo outVo = new UserVo(); while (dataReader.Read()) { UserVo currOutVo = new UserVo(); currOutVo.UserCode = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["user_cd"]); currOutVo.UserName = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["user_name"]); currOutVo.Country = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["country"]); currOutVo.Language = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["language"]); currOutVo.MultiLoginFlag = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["multi_login_flag"]); currOutVo.RegistrationFactoryCode = DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["registrated_factory_cd"]); currOutVo.LocaleId = Convert.ToInt32(dataReader["locale_id"]); outVo.UserListVo.Add(currOutVo); } dataReader.Close(); return(outVo); }
public override ValueObject Execute(TransactionContext trxContext, ValueObject vo) { ProcessWorkMoldVo inVo = (ProcessWorkMoldVo)vo; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("Select"); sqlQuery.Append(" pwm.process_work_mold_id,"); sqlQuery.Append(" pwm.process_work_id,"); sqlQuery.Append(" pwm.mold_id,"); sqlQuery.Append(" m.mold_name,"); sqlQuery.Append(" pw.process_work_name"); sqlQuery.Append(" from m_gtrs_process_work_mold pwm"); sqlQuery.Append(" inner join m_process_work pw on pwm.process_work_id = pw.process_work_id"); sqlQuery.Append(" inner join m_mold m on pwm.mold_id = m.mold_id"); sqlQuery.Append(" where 1 = 1 "); if (inVo.MoldId > 0) { sqlQuery.Append(" and pwm.mold_id = :moldid"); } if (inVo.ProcessWorkId > 0) { sqlQuery.Append(" and pwm.process_work_id = :processworkid"); } //create command DbCommandAdaptor sqlCommandAdapter = base.GetDbCommandAdaptor(trxContext, sqlQuery.ToString()); //create parameter DbParameterList sqlParameter = sqlCommandAdapter.CreateParameterList(); if (inVo.MoldId > 0) { sqlParameter.AddParameterInteger("moldid", inVo.MoldId); } if (inVo.ProcessWorkId > 0) { sqlParameter.AddParameterInteger("processworkid", inVo.ProcessWorkId); } //execute SQL IDataReader dataReader = sqlCommandAdapter.ExecuteReader(trxContext, sqlParameter); ProcessWorkMoldVo outVo = new ProcessWorkMoldVo(); while (dataReader.Read()) { ProcessWorkMoldVo currVo = new ProcessWorkMoldVo { ProcessWorkId = Convert.ToInt32(DBDataCheckHelper.ConvertDBNullToStringNull(dataReader["process_work_id"])), MoldId = Convert.ToInt32(dataReader["mold_id"]), MoldName = dataReader["mold_name"].ToString(), ProcessWorkName = dataReader["process_work_name"].ToString(), ProcessWorkMoldId = Convert.ToInt32(dataReader["process_work_mold_id"]), }; outVo.ProcessWorkMoldListVo.Add(currVo); } 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); }