/// <summary>
/// グリッド設定情報を取得
/// </summary>
/// <param name="windowName"></param>
/// <param name="gridName"></param>
/// <param name="loginId"></param>
/// <returns></returns>
public DataSet GetGridConfig(string windowName, string gridName, string loginId)
{
DataSet ret = null;
List <SqlParameter> param = new List <SqlParameter>();
SQLDBUtil db = new SQLDBUtil(_logger);
try {
db.Open(_connectString);
param.Add(new SqlParameter("@window_name", windowName));
param.Add(new SqlParameter("@grid_name", gridName));
param.Add(new SqlParameter("@login_id", loginId));
ret = db.ExecSelect(SQLSrc.t_grid_config.SELECT_CONDITION, param.ToArray());
if (ret.Tables[0].Rows.Count == 0)
{
//自分の設定がない場合は、デフォルト設定を取得
List <SqlParameter> param2 = new List <SqlParameter>();
param2.Add(new SqlParameter("@window_name", windowName));
param2.Add(new SqlParameter("@grid_name", gridName));
param2.Add(new SqlParameter("@login_id", ""));
ret = db.ExecSelect(SQLSrc.t_grid_config.SELECT_CONDITION, param2.ToArray());
}
ret.Tables[0].TableName = "GridConfig";
return(ret);
} finally {
db.Close();
}
}
/// <summary>
/// ウィンドウ表示状態取得
/// </summary>
/// <param name="sessionId"></param>
/// <param name="windowName"></param>
/// <returns></returns>
public DataTable GetWindowState(string loginId, string windowName)
{
SQLDBUtil db = new SQLDBUtil(_logger);
DataTable ret = null;
DataSet tmp = null;
List <SqlParameter> param = new List <SqlParameter>();
try {
db.Open(_connectString);
param.Add(new SqlParameter("@window_name", windowName));
param.Add(new SqlParameter("@login_id", ""));
tmp = db.ExecSelect(SQLSrc.t_window_state.SELECT_STATE, param.ToArray());
ret = tmp.Tables[0].Clone();
foreach (DataRow dr in tmp.Tables[0].Rows)
{
string ctlName = dr[CommonConsts.control_name].ToString();
DataTable tmpTbl = GetWindowState(loginId, windowName, ctlName);
DataRow newRow = ret.NewRow();
newRow.ItemArray = tmpTbl.Rows[0].ItemArray;
ret.Rows.Add(newRow);
}
ret.TableName = "WindowState";
return(ret);
} finally {
db.Close();
}
}
/// <summary>
/// ウィンドウ表示状態取得
/// </summary>
/// <param name="sessionId"></param>
/// <param name="windowName"></param>
/// <param name="ctlName"></param>
/// <returns></returns>
public DataTable GetWindowState(string loginId, string windowName, string ctlName)
{
SQLDBUtil db = new SQLDBUtil(_logger);
DataSet ret = null;
List <SqlParameter> param = new List <SqlParameter>();
try {
db.Open(_connectString);
param.Add(new SqlParameter("@window_name", windowName));
param.Add(new SqlParameter("@control_name", ctlName));
param.Add(new SqlParameter("@login_id", loginId));
ret = db.ExecSelect(SQLSrc.t_window_state.SELECT_STATE_ONE, param.ToArray());
if (ret.Tables[0].Rows.Count == 0)
{
List <SqlParameter> param2 = new List <SqlParameter>();
param2.Add(new SqlParameter("@window_name", windowName));
param2.Add(new SqlParameter("@control_name", ctlName));
param2.Add(new SqlParameter("@login_id", ""));
ret = db.ExecSelect(SQLSrc.t_window_state.SELECT_STATE_ONE, param2.ToArray());
}
ret.Tables[0].TableName = "WindowState";
return((DataTable)_dcu.DeepCopy(ret.Tables[0]));
} finally {
db.Close();
}
}
/// <summary>
/// グリッド設定を更新(Delete-Insert)
/// </summary>
/// <param name="dt"></param>
public void UpdateGridConfigInfo(DataTable dt, string loginId)
{
SQLDBUtil db = new SQLDBUtil(_logger);
SetNewDispOrder(dt);
lock (_lockObj) {
try {
db.Open(_connectString);
db.BeginTransaction();
//Delete
DataRow r = dt.Rows[0];
List <SqlParameter> delParam = new List <SqlParameter>();
delParam.Add(new SqlParameter("@window_name", r[CommonConsts.window_name]));
delParam.Add(new SqlParameter("@grid_name", r[CommonConsts.grid_name]));
delParam.Add(new SqlParameter("@login_id", loginId));
db.ExecuteNonQuery(SQLSrc.t_grid_config.DELETE, delParam.ToArray());
//Insert
foreach (DataRow dr in dt.Rows)
{
List <SqlParameter> insParam = new List <SqlParameter>();
insParam.Add(new SqlParameter("@window_name", dr[CommonConsts.window_name]));
insParam.Add(new SqlParameter("@grid_name", dr[CommonConsts.grid_name]));
insParam.Add(new SqlParameter("@db_name", dr[CommonConsts.db_name]));
insParam.Add(new SqlParameter("@login_id", loginId));
insParam.Add(new SqlParameter("@width", dr[CommonConsts.width]));
insParam.Add(new SqlParameter("@height", dr[CommonConsts.height]));
insParam.Add(new SqlParameter("@conf_editable", dr[CommonConsts.conf_editable]));
insParam.Add(new SqlParameter("@visible", dr[CommonConsts.visible]));
insParam.Add(new SqlParameter("@editable", dr[CommonConsts.editable]));
insParam.Add(new SqlParameter("@disp_order", dr[CommonConsts.disp_order]));
insParam.Add(new SqlParameter("@col_fixed", dr[CommonConsts.col_fixed]));
insParam.Add(new SqlParameter("@data_type", dr[CommonConsts.data_type]));
insParam.Add(new SqlParameter("@max_length", dr[CommonConsts.max_length]));
insParam.Add(new SqlParameter("@required", dr[CommonConsts.required]));
insParam.Add(new SqlParameter("@primary_key", dr[CommonConsts.primary_key]));
insParam.Add(new SqlParameter("@password_char", dr[CommonConsts.password_char]));
insParam.Add(new SqlParameter("@note", dr[CommonConsts.note]));
db.ExecuteNonQuery(SQLSrc.t_grid_config.INSERT, insParam.ToArray());
}
db.Commit();
} catch (Exception) {
db.Rollback();
throw;
} finally {
db.Close();
}
}
}
/// <summary>
/// 共通設定情報取得
/// </summary>
/// <returns></returns>
public DataSet GetCommonConf()
{
List <SqlParameter> param = new List <SqlParameter>();
SQLDBUtil db = new SQLDBUtil(_logger);
try {
db.Open(_connectString);
DataSet ret = db.ExecSelect(SQLSrc.m_common_conf.SELECT_ALL);
ret.Tables[0].TableName = CommonConsts.TableNameMCommonConf;
return(ret);
} finally {
db.Close();
}
}
/// <summary>
/// データディクショナリ情報取得
/// </summary>
/// <returns></returns>
public DataSet GetDataDictionary(ILog logger, string connectString)
{
List <SqlParameter> param = new List <SqlParameter>();
SQLDBUtil db = new SQLDBUtil(logger);
try {
db.Open(connectString);
DataSet ret = db.ExecSelect(SQLSrc.m_data_dictionary.SELECT_ALL);
ret.Tables[0].TableName = CommonConsts.TableNameMDataDictionary;
return(ret);
} finally {
db.Close();
}
}
/// <summary>
/// 共通設定情報取得(1項目)
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public DataSet GetCommonConfItem(string key)
{
List <SqlParameter> param = new List <SqlParameter>();
SQLDBUtil db = new SQLDBUtil(_logger);
try {
db.Open(_connectString);
param.Add(new SqlParameter("@key", key));
DataSet ret = db.ExecSelect(SQLSrc.m_common_conf.SELECT_ITEM, param.ToArray());
ret.Tables[0].TableName = CommonConsts.TableNameMCommonConf;
return(ret);
} finally {
db.Close();
}
}
/// <summary>
/// メッセージ情報取得
/// </summary>
/// <returns></returns>
public DataSet GetMessageInfo()
{
SQLDBUtil db = new SQLDBUtil(_logger);
DataSet ret = null;
List <SqlParameter> param = new List <SqlParameter>();
try {
db.Open(_connectString);
ret = db.ExecSelect(SQLSrc.m_message.SELECT_ALL, param.ToArray());
ret.Tables[0].TableName = "MessageInfo";
return(ret);
} finally {
db.Close();
}
}
/// <summary>
/// 検索条件初期設定を更新(Delete-Insert)
/// </summary>
/// <param name="dt"></param>
public void UpdateSearchConditionInfo(DataTable dt, string loginId)
{
SQLDBUtil db = new SQLDBUtil(_logger);
SetNewDispOrder(dt);
lock (_lockObj) {
try {
db.Open(_connectString);
db.BeginTransaction();
//Delete
DataRow r = dt.Rows[0];
List <SqlParameter> delParam = new List <SqlParameter>();
delParam.Add(new SqlParameter("@window_name", r[CommonConsts.window_name]));
delParam.Add(new SqlParameter("@grid_name", r[CommonConsts.grid_name]));
delParam.Add(new SqlParameter("@login_id", loginId));
db.ExecuteNonQuery(SQLSrc.t_search_condition.DELETE, delParam.ToArray());
//Insert
foreach (DataRow dr in dt.Rows)
{
List <SqlParameter> insParam = new List <SqlParameter>();
insParam.Add(new SqlParameter("@window_name", dr[CommonConsts.window_name]));
insParam.Add(new SqlParameter("@grid_name", dr[CommonConsts.grid_name]));
insParam.Add(new SqlParameter("@db_name", dr[CommonConsts.db_name]));
insParam.Add(new SqlParameter("@login_id", loginId));
insParam.Add(new SqlParameter("@ctl_type", dr[CommonConsts.ctl_type]));
insParam.Add(new SqlParameter("@value1", dr[CommonConsts.value1]));
insParam.Add(new SqlParameter("@value2", dr[CommonConsts.value2]));
insParam.Add(new SqlParameter("@visible", dr[CommonConsts.visible]));
insParam.Add(new SqlParameter("@disp_order", dr[CommonConsts.disp_order]));
db.ExecuteNonQuery(SQLSrc.t_search_condition.INSERT, insParam.ToArray());
}
db.Commit();
} catch (Exception) {
db.Rollback();
throw;
} finally {
db.Close();
}
}
}
/// <summary>
/// システムコード情報を取得
/// </summary>
/// <returns></returns>
public DataTable GetSystemCodeInfo(string category)
{
SQLDBUtil db = new SQLDBUtil(_logger);
DataSet ret = null;
List <SqlParameter> param = new List <SqlParameter>();
try {
db.Open(_connectString);
param.Add(new SqlParameter("@category", category));
ret = db.ExecSelect(SQLSrc.m_system_code.SELECT_CANDIDATE, param.ToArray());
ret.Tables[0].TableName = category;
return((DataTable)_dcu.DeepCopy(ret.Tables[0]));
} finally {
db.Close();
}
}
/// <summary>
/// 共通設定情報更新(1項目)
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public void SetCommonConfItem(string key, string value)
{
List <SqlParameter> param = new List <SqlParameter>();
SQLDBUtil db = new SQLDBUtil(_logger);
try {
db.Open(_connectString);
param.Add(new SqlParameter("@key", key));
param.Add(new SqlParameter("@value", value));
db.ExecuteNonQuery(SQLSrc.m_common_conf.MERGE_KEY_VALUE, param.ToArray());
db.Commit();
} catch (Exception) {
db.Rollback();
throw;
} finally {
db.Close();
}
}
/// <summary>
/// Window状態を更新(Delete-Insert)
/// </summary>
/// <param name="dt"></param>
public void UpdateWindowStateInfo(DataTable dt, string loginId)
{
SQLDBUtil db = new SQLDBUtil(_logger);
lock (_lockObj) {
try {
db.Open(_connectString);
db.BeginTransaction();
DataRow r = dt.Rows[0];
foreach (DataRow dr in dt.Rows)
{
//Delete
List <SqlParameter> delParam = new List <SqlParameter>();
delParam.Add(new SqlParameter("@window_name", r[CommonConsts.window_name]));
delParam.Add(new SqlParameter("@control_name", dr[CommonConsts.control_name]));
delParam.Add(new SqlParameter("@login_id", loginId));
db.ExecuteNonQuery(SQLSrc.t_window_state.DELETE, delParam.ToArray());
//Insert
List <SqlParameter> insParam = new List <SqlParameter>();
insParam.Add(new SqlParameter("@window_name", dr[CommonConsts.window_name]));
insParam.Add(new SqlParameter("@control_name", dr[CommonConsts.control_name]));
insParam.Add(new SqlParameter("@login_id", loginId));
insParam.Add(new SqlParameter("@conf_value", dr[CommonConsts.conf_value]));
db.ExecuteNonQuery(SQLSrc.t_window_state.INSERT, insParam.ToArray());
}
db.Commit();
} catch (Exception) {
db.Rollback();
throw;
} finally {
db.Close();
}
}
}
/// <summary>
/// 検索条件初期設定を取得
/// </summary>
/// <returns></returns>
public DataSet GetSearchItemTables(HttpRequestMessage req, string loginId)
{
DataSet ret = new DataSet();
List <SqlParameter> param = new List <SqlParameter>();
SQLDBUtil db = new SQLDBUtil(_logger);
DataSet ds = JsonConvert.DeserializeObject <DataSet>(req.Content.ReadAsStringAsync().Result);
DataTable dt = ds.Tables[0];
try {
db.Open(_connectString);
string windowName = StringUtil.NullToBlank(dt.Rows[0][CommonConsts.window_name]);
string gridName = StringUtil.NullToBlank(dt.Rows[0][CommonConsts.grid_name]);
param.Add(new SqlParameter("@window_name", windowName));
param.Add(new SqlParameter("@grid_name", gridName));
param.Add(new SqlParameter("@login_id", loginId));
DataSet ds1 = db.ExecSelect(SQLSrc.t_search_condition.SELECT_CONDITION, param.ToArray());
if (ds1.Tables[0].Rows.Count == 0)
{
//自分の設定がない場合は、デフォルト設定を取得
List <SqlParameter> param2 = new List <SqlParameter>();
param2.Add(new SqlParameter("@window_name", windowName));
param2.Add(new SqlParameter("@grid_name", gridName));
param2.Add(new SqlParameter("@login_id", ""));
ds1 = db.ExecSelect(SQLSrc.t_search_condition.SELECT_CONDITION, param2.ToArray());
}
ds1.Tables[0].TableName = CommonConsts.SearchItemTbl;
ret.Tables.Add(ds1.Tables[CommonConsts.SearchItemTbl].Copy());
//プルダウンの候補をセット
SetPulldownCandidate(db, ret);
return(ret);
} finally {
db.Close();
}
}
