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