/// <summary>
/// OKボタン
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOK_Click(object sender, EventArgs e)
{
try
{
if (txtPass.Text.Equals(txtPassConfirm.Text))
{
if (base.AskMessageBox(GlobalDefine.MESSAGE_PASSWORD_UPDATE))
{
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("UPDATE SYSTEM_DEFINE_MASTER SET VALUE = '{0}' WHERE KEY = 'password'", txtPass.Text);
dba.nonExecSQL(query);
MessageBox.Show(GlobalDefine.MESSAGE_PASSWORD_CHANGE);
this.Close();
}
}
else
{
throw new InputException(GlobalDefine.ERROR_CODE[8].message, GlobalDefine.ERROR_CODE[8].code);
}
}
catch (InputException ex)
{
MessageBox.Show(ex.Message);
txtPass.Clear();
txtPassConfirm.Clear();
txtPass.Focus();
}
}
/// <summary>
/// 返却ボタン押下時のチェック
/// </summary>
private void ReturnCheckes()
{
// 未入力チェック
if (string.IsNullOrEmpty(txtId.Text))
{
throw new InputException(GlobalDefine.ERROR_CODE[11].message, GlobalDefine.ERROR_CODE[11].code, txtId);
}
// 存在チェック
IsExistBookId();
// 書籍が貸出中か
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("SELECT * FROM BOOK_STATUS WHERE BOOK_ID = '{0}'", txtId.Text);
if (dba.FindRecord(query))
{
query = string.Format("SELECT " +
"* " +
"FROM " +
"(SELECT * FROM BOOK_STATUS WHERE BOOK_ID = '{0}') " +
"WHERE " +
"(LENDING_STATUS = 0)",
txtId.Text);
if (!dba.FindRecord(query))
{
throw new InputException(GlobalDefine.ERROR_CODE[20].message, GlobalDefine.ERROR_CODE[20].code, txtId);
}
}
}
/// <summary>
/// 返却ボタン
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnReturn_Click(object sender, EventArgs e)
{
ReturnCheckes();
// 確認ダイアログを表示
if (!AskMessageBox(GlobalDefine.MESSAGE_ASK_RETURN))
{
return;
}
// 貸出詳細テーブル更新
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("UPDATE LENDING_DETAIL SET RETURN_DATE = Datetime('{0}') " +
"WHERE BOOK_ID = '{1}' AND LOAN_DATE = Datetime('{2}') AND SUB_NO = {3} ",
dateReturn.Value.ToString("yyyy-MM-dd"),
txtId.Text,
dateLoan.Value.ToString("yyyy-MM-dd"),
subNo);
dba.nonExecSQL(query);
// 貸出状態テーブル更新
query = string.Format("UPDATE BOOK_STATUS SET LENDING_STATUS = 1 WHERE BOOK_ID = '{0}'", txtId.Text);
dba.nonExecSQL(query);
MessageBox.Show(GlobalDefine.MESSAGE_COMPLETE_REGISTRATION);
InitDiag();
txtId.Focus();
}
/// <summary>
/// 書籍ID LostFocusチェック
/// </summary>
private void CheckTxtId()
{
// シングルクォーテーションチェック
InputCheck.IsSingleQuotation(this.txtId);
// 書籍IDが存在するか
IsExistBookId();
// 書籍が貸出中か
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("SELECT * FROM BOOK_STATUS WHERE BOOK_ID = '{0}'", txtId.Text);
if (dba.FindRecord(query))
{
query = string.Format("SELECT " +
"* " +
"FROM " +
"(SELECT * FROM BOOK_STATUS WHERE BOOK_ID = '{0}') " +
"WHERE " +
"(LENDING_STATUS = 0)",
txtId.Text);
if (!dba.FindRecord(query))
{
throw new InputException(GlobalDefine.ERROR_CODE[20].message, GlobalDefine.ERROR_CODE[20].code, txtId);
}
}
}
/// <summary>
/// 入力チェック用メソッド
/// </summary>
private void ErrorCheck()
{
// シングルクォーテーションが入っていたら(会社名)
if (this.textName.Text.IndexOf('\'') >= 0)
{
throw new InputException(GlobalDefine.ERROR_CODE[0].message, GlobalDefine.ERROR_CODE[0].code, this.textName);
}
// 空文字チェック(会社名)
if (string.IsNullOrEmpty(this.textName.Text))
{
throw new InputException(GlobalDefine.ERROR_CODE[1].message, GlobalDefine.ERROR_CODE[1].code, this.textName);
}
// 新規追加モードの場合、会社名が登録されているかどうか
if (mode == MODE.ADD)
{
DBAdapter dba = SingletonObject.GetDbAdapter();
string str = string.Format("SELECT * FROM BOOK_GENRE_MASTER WHERE DIVISION_NAME = '{0}'", this.textName.Text);
if (dba.FindRecord(str))
{
throw new InputException(GlobalDefine.ERROR_CODE[3].message, GlobalDefine.ERROR_CODE[3].code, this.textName);
}
}
}
/// <summary>
/// 削除ボタン
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDelete_Click(object sender, EventArgs e)
{
// エラーチェック
DeleteCheck();
// 画面時に保持した変数と入力された項目を比較して、MessageBoxに表示するメッセージを変更する
string msg =
textName.Text.Equals(this.tempCategoryName) ? GlobalDefine.MESSAGE_ASK_DELETE : GlobalDefine.MESSAGE_ASK_DELETE_CHANGE;
// 確認メッセージ表示
if (AskMessageBox(msg))
{
// 削除実行
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("DELETE FROM BOOK_GENRE_MASTER WHERE DIVISION_ID = '{0}'", this.textId.Text);
dba.nonExecSQL(query);
MessageBox.Show(GlobalDefine.MESSAGE_COMPLETE_DELETE);
// 削除モードに変更(確認dialog出さずに、フォーム閉じる為)
mode = MODE.DEL;
this.Close();
}
return;
}
/// <summary>
/// ダイアログ初期化
/// </summary>
private void InitDialog()
{
DBAdapter dba = SingletonObject.GetDbAdapter();
cmbCategory1.InitControl();
cmbCategory2.InitControl();
cmbCategory3.InitControl();
}
/// <summary>
/// 会社名から会社ID取得
/// </summary>
/// <param name="companyName"></param>
/// <returns></returns>
private string GetCompanyId(string companyName)
{
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("SELECT COMPANY_ID FROM COMPANY_MASTER WHERE COMPANY_NAME='{0}'", companyName);
string[] data = dba.GetRecord(query);
return((data == null) ? null : data[0]);
}
static public DataTable GetAllData()
{
DataTable dt = new DataTable();
string query = "SELECT * FROM BOOK_GENRE_MASTER;";
dt = SingletonObject.GetDbAdapter().execSQL(query);
return(dt);
}
// 削除チェック用のメソッド
private void DeleteCheck()
{
DBAdapter dba = SingletonObject.GetDbAdapter();
string str = string.Format("SELECT * FROM USER_MASTER WHERE COMPANY_ID = '{0}' AND RETIREMENT_FLAG = 0", this.textId.Text);
if (dba.FindRecord(str))
{
throw new InputException(GlobalDefine.ERROR_CODE[4].message, GlobalDefine.ERROR_CODE[4].code, this.textId);
}
}
/// <summary>
/// 書籍IDの存在チェック
/// </summary>
private void IsExistBookId()
{
// 書籍IDが存在するか
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("SELECT * FROM BOOK_MASTER WHERE BOOK_ID = '{0}'", txtId.Text);
if (!dba.FindRecord(query))
{
throw new InputException(GlobalDefine.ERROR_CODE[11].message, GlobalDefine.ERROR_CODE[11].code, txtId);
}
}
/// <summary>
/// ダイアログ初期化
/// </summary>
private void InitDialog()
{
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = "SELECT * FROM COMPANY_MASTER";
cmbCompany.DataSource = dba.execSQL(query);
// 実際に表示するのは会社略称
cmbCompany.DisplayMember = "COMPANY_ABBREVIATION";
// 内部的に渡すのは、会社ID
cmbCompany.ValueMember = "COMPANY_ID";
}
public BCSR0101()
{
InitializeComponent();
InitDialog();
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = BaseQuery();
dataTable = dba.execSQL(query);
InitGridView();
}
/// <summary>
/// 画面の初期化
/// </summary>
private void InitDiag()
{
DBAdapter dba = SingletonObject.GetDbAdapter();
this.dataTable = SingletonObject.GetMemberList();
this.txtId.Clear();
this.txtCategory1.Clear();
this.txtCategory2.Clear();
this.txtCategory3.Clear();
this.txtUserName.Clear();
this.txtTitle.Clear();
}
/// <summary>
/// ブック貸出ランキングを表示する
/// </summary>
private void BookRankingGridView()
{
string startDate = "";
string endDate = "";
if (rdMonthTop.Checked)
{
startDate = string.Format("{0}-{1:D2}-01", DateTime.Now.Year, DateTime.Now.Month);
endDate = string.Format("{0}-{1:D2}-01", DateTime.Now.Year, DateTime.Now.Month + 1);
}
if (rdYearTop.Checked)
{
startDate = string.Format("{0}-01-01", DateTime.Now.Year);
endDate = string.Format("{0}-01-01", DateTime.Now.Year + 1);
}
if (rdTotalTop.Checked)
{
startDate = "1900-01-01";
endDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
}
string query = string.Format("SELECT " +
"BOOK_MASTER.BOOK_ID as '書籍ID', " +
"BOOK_MASTER.BOOK_NAME as 'タイトル', " +
"dv1.DIVISION_NAME as '分類1', " +
"dv2.DIVISION_NAME as '分類2', " +
"dv3.DIVISION_NAME as '分類3', " +
"COUNT(LENDING_DETAIL.BOOK_ID) as '貸出数'" +
"FROM " +
"BOOK_MASTER " +
"LEFT OUTER JOIN BOOK_GENRE_MASTER as dv1 ON dv1.DIVISION_ID = DIVISION_ID1 " +
"LEFT OUTER JOIN BOOK_GENRE_MASTER as dv2 ON dv2.DIVISION_ID = DIVISION_ID2 " +
"LEFT OUTER JOIN BOOK_GENRE_MASTER as dv3 ON dv3.DIVISION_ID = DIVISION_ID3 " +
"LEFT OUTER JOIN LENDING_DETAIL ON BOOK_MASTER.BOOK_ID = LENDING_DETAIL.BOOK_ID " +
"WHERE LENDING_DETAIL.LOAN_DATE >= datetime('{0}') AND LENDING_DETAIL.LOAN_DATE < datetime('{1}')" +
"GROUP BY BOOK_MASTER.BOOK_ID " +
"ORDER BY 貸出数 DESC ",
startDate, endDate);
DBAdapter dba = SingletonObject.GetDbAdapter();
dataGridView1.Table = dba.execSQL(query);
InitGridView();
}
/// <summary>
/// ダイアログ初期化
/// </summary>
private void InitDialog()
{
DBAdapter dba = SingletonObject.GetDbAdapter();
this.cmbCategory1.InitControl();
this.cmbCategory2.InitControl();
this.cmbCategory3.InitControl();
rdMonthTop.Checked = true;
rdBook.Checked = true;
groupRanking.Enabled = false;
dateLoanStart.Value = DateTime.Now;
dateLoanEnd.Value = DateTime.Now;
}
/// <summary>
/// 画面の初期化
/// </summary>
private void InitDialog()
{
DBAdapter dba = SingletonObject.GetDbAdapter();
this.Text = GlobalDefine.FORM_NAME;
this.cmbCategory1.InitControl();
this.cmbCategory2.InitControl();
this.cmbCategory3.InitControl();
bool delFuncEnable = (mode == MODE.ADD) ? false // 新規作成時
: (mode == MODE.MOD) ? true // 編集時
: false; // それ以外
FormControlsEnabledOnLoad(delFuncEnable);
}
/// <summary>
/// 書籍IDのフォーカスが離れた時（Lost Focus)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void txtId_Leave(object sender, EventArgs e)
{
// 空白の場合は、チェックもクエリも投げない。
if (string.IsNullOrEmpty(this.txtId.Text))
{
return;
}
CheckTxtId();
// チェック処理で問題なければ、検索実施
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("SELECT " +
"BOOK_NAME, " +
"table1.DIVISION_NAME as DIVISION_NAME1, " +
"table2.DIVISION_NAME as DIVISION_NAME2, " +
"table3.DIVISION_NAME as DIVISION_NAME3 " +
"FROM " +
"(SELECT " +
"* " +
"FROM " +
"BOOK_MASTER " +
"WHERE " +
"BOOK_ID = '{0}' " +
") " +
"LEFT OUTER JOIN " +
"BOOK_GENRE_MASTER as table1 " +
"ON " +
"DIVISION_ID1 = table1.DIVISION_ID " +
"LEFT OUTER JOIN " +
"BOOK_GENRE_MASTER as table2 " +
"ON " +
"DIVISION_ID2 = table2.DIVISION_ID " +
"LEFT OUTER JOIN " +
"BOOK_GENRE_MASTER as table3 " +
"ON " +
"DIVISION_ID3 = table3.DIVISION_ID "
, txtId.Text);
string[] bookColumns = dba.GetRecord(query);
txtTitle.Text = bookColumns[0];
txtCategory1.Text = bookColumns[1];
txtCategory2.Text = bookColumns[2];
txtCategory3.Text = bookColumns[3];
}
/// <summary>
/// 借りているユーザランキングを表示する
/// </summary>
private void UserRankingGridView()
{
string startDate = "";
string endDate = "";
if (rdMonthTop.Checked)
{
startDate = string.Format("{0}-{1:D2}-01", DateTime.Now.Year, DateTime.Now.Month);
endDate = string.Format("{0}-{1:D2}-01", DateTime.Now.Year, DateTime.Now.Month + 1);
}
if (rdYearTop.Checked)
{
startDate = string.Format("{0}-01-01", DateTime.Now.Year);
endDate = string.Format("{0}-01-01", DateTime.Now.Year + 1);
}
if (rdTotalTop.Checked)
{
startDate = "1900-01-01";
endDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
}
string query = string.Format("SELECT " +
"LENDING_DETAIL.USER_ID as 'ユーザーID', " +
"USER_MASTER.USER_NAME as '氏名', " +
"COUNT(LENDING_DETAIL.USER_ID) as '利用数' " +
"FROM " +
"LENDING_DETAIL " +
"LEFT OUTER JOIN " +
"USER_MASTER " +
"ON " +
"LENDING_DETAIL.USER_ID = USER_MASTER.USER_ID " +
"WHERE LENDING_DETAIL.LOAN_DATE >= datetime('{0}') AND LENDING_DETAIL.LOAN_DATE < datetime('{1}')" +
"GROUP BY " +
"LENDING_DETAIL.USER_ID " +
"ORDER BY " +
"利用数 DESC ",
startDate, endDate);
DBAdapter dba = SingletonObject.GetDbAdapter();
dataGridView1.Table = dba.execSQL(query);
InitGridView();
}
/// <summary>
/// 画面の初期化
/// </summary>
private void InitDialog()
{
string query = "SELECT * FROM COMPANY_MASTER";
DBAdapter dba = SingletonObject.GetDbAdapter();
cmbCompany.DataSource = dba.execSQL(query);
#region ここ直したい
// 実際に表示するのは会社略称
cmbCompany.DisplayMember = "COMPANY_ABBREVIATION";
// 内部的に渡すのは、会社ID
cmbCompany.ValueMember = "COMPANY_ID";
#endregion
// ロード時は無効
btnApply.Enabled = false;
}
private void btnDelete_Click(object sender, EventArgs e)
{
#region エラーチェック
try
{
DeleteCheck();
}
catch (InputException ex)
{
MessageBox.Show(ex.Message);
return;
}
#endregion
string msg;
// 画面時に保持した変数と入力された項目を比較して、MessageBoxに表示するメッセージを変更する
if (this.textName.Text.Equals(this.name) && this.textAbbreviation.Text.Equals(this.abbreviation))
{
msg = GlobalDefine.MESSAGE_ASK_DELETE;
}
else
{
msg = GlobalDefine.MESSAGE_ASK_DELETE_CHANGE;
}
// 確認メッセージ表示
if (base.AskMessageBox(msg))
{ // 削除実行
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("DELETE FROM COMPANY_MASTER WHERE COMPANY_ID = '{0}'", this.textId.Text);
dba.nonExecSQL(query);
MessageBox.Show(GlobalDefine.MESSAGE_COMPLETE_DELETE);
// 削除モードに変更(確認dialog出さずに、フォーム閉じる為)
mode = MODE.DEL;
this.Close();
}
else
{ // キャンセル
return;
}
}
/// <summary>
/// 検索ボタン
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSearch_Click(object sender, EventArgs e)
{
try
{
SearchInputChecks();
}
catch (InputException ex)
{
MessageBox.Show(ex.Message);
if (ex.ERROR_CODE == COMPANY_NO_SELECTED)
{
return;
}
ex.ERROR_TEXTBOX.Clear();
ex.ERROR_TEXTBOX.Focus();
return;
}
// 検索開始
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("SELECT " +
"USER_ID as 'ユーザID', " +
"USER_NAME as 'ユーザ名', " +
"COMPANY_ABBREVIATION as '所属会社' " +
"FROM " +
"USER_MASTER " +
"LEFT OUTER JOIN " +
"COMPANY_MASTER " +
"ON " +
"USER_MASTER.COMPANY_ID = COMPANY_MASTER.COMPANY_ID " +
"WHERE " +
"USER_NAME LIKE '%{0}%'" +
"AND USER_MASTER.COMPANY_ID = '{1}'",
textUser.Text, cmbCompany.SelectedValue);
dataGridView1.Table = dba.execSQL(query);
InitGridView();
SearchBoxEnable(false);
}
/// <summary>
/// 検索ボタンを押下した際のチェック
/// </summary>
private void SearchInputChecks()
{
if (cmbCompany.SelectedIndex <= 0) // ←なぜ0「以下」
{
throw new InputException(GlobalDefine.ERROR_CODE[15].message, GlobalDefine.ERROR_CODE[15].code);
}
// シングルクォーテーションチェック
InputCheck.IsSingleQuotation(this.textUser);
// ユーザが居るか
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("SELECT * FROM USER_MASTER WHERE USER_NAME LIKE '%{0}%'", textUser.Text);
if (!dba.FindRecord(query))
{
throw new InputException(GlobalDefine.ERROR_CODE[14].message, GlobalDefine.ERROR_CODE[14].code, textUser);
}
}
/// <summary>
/// コントロール初期化
/// </summary>
public void InitControl()
{
// ドロップダウンリストに設定
this.DropDownStyle = ComboBoxStyle.DropDownList;
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = "SELECT * FROM BOOK_GENRE_MASTER";
DataTable dt1 = dba.execSQL(query);
DataRow dr = dt1.NewRow();
dr["DIVISION_ID"] = "";
dr["DIVISION_NAME"] = "";
dt1.Rows.InsertAt(dr, 0);
this.DataSource = dt1;
this.DisplayMember = GlobalDefine.CATEGORY_DISPLAY;
this.ValueMember = GlobalDefine.CATEGORY_VALUE;
}
/// <summary>
/// ダイアログ初期化
/// </summary>
private void InitDialog()
{
DBAdapter dba = SingletonObject.GetDbAdapter();
this.Text = GlobalDefine.FORM_NAME;
this.dataTable = SingletonObject.GetMemberList();
string query = "SELECT * FROM BOOK_GENRE_MASTER";
cmbCategory1.DataSource = dba.execSQL(query);
cmbCategory2.DataSource = dba.execSQL(query);
cmbCategory3.DataSource = dba.execSQL(query);
cmbCategory1.DisplayMember = "DIVISION_NAME";
cmbCategory1.ValueMember = "DIVISION_ID";
cmbCategory2.DisplayMember = "DIVISION_NAME";
cmbCategory2.ValueMember = "DIVISION_ID";
cmbCategory3.DisplayMember = "DIVISION_NAME";
cmbCategory3.ValueMember = "DIVISION_ID";
}
/// <summary>
/// 削除ボタン
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDelete_Click(object sender, EventArgs e)
{
bool valueEqual = ValueCompare();
bool cancelSelected = valueEqual ? !base.AskMessageBox(GlobalDefine.MESSAGE_ASK_DELETE)
: !base.AskMessageBox(GlobalDefine.MESSAGE_ASK_DELETE_CHANGE);
if (cancelSelected)
{
return;
}
string query = string.Format("UPDATE USER_MASTER SET RETIREMENT_FLAG=1 WHERE USER_ID='{0}'", textId.Text);
DBAdapter dba = SingletonObject.GetDbAdapter();
dba.nonExecSQL(query);
MessageBox.Show(GlobalDefine.MESSAGE_COMPLETE_DELETE);
mode = MODE.DEL;
this.Close();
}
/// <summary>
/// 貸出中ボタン
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnLoan_Click(object sender, EventArgs e)
{
try
{
CheckLoanButton();
}
catch (InputException ex)
{
MessageBox.Show(ex.Message);
if (ex.ERROR_CODE == USER_NAME_EMPTY || ex.ERROR_CODE == DATE_EMPTY || ex.ERROR_CODE == DATE_ILLEGAL)
{
return;
}
ex.ERROR_TEXTBOX.Clear();
ex.ERROR_TEXTBOX.Focus();
return;
}
// 確認ダイアログを表示
if (!AskMessageBox(GlobalDefine.MESSAGE_ASK_LENDING))
{
return;
}
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("SELECT " +
"MAX(SUB_NO) " +
"FROM " +
"LENDING_DETAIL " +
"WHERE " +
"BOOK_ID = '{0}' " +
"AND LOAN_DATE = Datetime('{1}-{2:D2}-{3:D2} 00:00:00')",
txtId.Text, dateLoan.Value.Date.Year, dateLoan.Value.Date.Month, dateLoan.Value.Date.Day);
string[] maxId = dba.GetRecord(query);
// 連番取得後、+1する
int id = 0;
int.TryParse(maxId[0], out id);
id++;
// 貸出詳細テーブル更新
query = string.Format("INSERT INTO LENDING_DETAIL(BOOK_ID, LOAN_DATE, SUB_NO, USER_ID, RETURN_SCHEDULE) " +
"VALUES('{0}',Datetime('{1}-{2:D2}-{3:D2} 00:00:00'), {4}, '{5}', Datetime('{6}-{7:D2}-{8:D2} 00:00:00'));",
txtId.Text,
dateLoan.Value.Date.Year, dateLoan.Value.Date.Month, dateLoan.Value.Date.Day,
id,
userId,
dateReturn.Value.Date.Year, dateReturn.Value.Date.Month, dateReturn.Value.Date.Day);
dba.nonExecSQL(query);
// 貸出状態テーブル更新
query = string.Format("UPDATE BOOK_STATUS SET LENDING_STATUS = 0 WHERE BOOK_ID = '{0}'", txtId.Text);
dba.nonExecSQL(query);
MessageBox.Show(GlobalDefine.MESSAGE_COMPLETE_REGISTRATION);
switch (mode)
{
case MODE.FROM_MENU:
InitDiag();
txtId.Focus();
break;
case MODE.FROM_SEARCH:
mode = MODE.FORCE_CLOSE;
this.Close();
break;
default:
break;
}
}
/// <summary>
/// 書籍IDのフォーカスが離れた時（Lost Focus)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void txtId_Leave(object sender, EventArgs e)
{
// 空白の場合は、チェックもクエリも投げない。
if (string.IsNullOrEmpty(this.txtId.Text))
{
return;
}
CheckTxtId();
// チェック処理で問題なければ、検索実施
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("SELECT " +
"BOOK_MASTER.BOOK_NAME, " +
"table1.DIVISION_NAME as DIVISION_NAME1, " +
"table2.DIVISION_NAME as DIVISION_NAME2, " +
"table3.DIVISION_NAME as DIVISION_NAME3, " +
"table4.USER_NAME, " +
"table0.LOAN_DATE, " +
"table0.RETURN_SCHEDULE, " +
"SUB_NO " +
"FROM " +
"BOOK_MASTER " +
"LEFT OUTER JOIN " +
"(SELECT " +
"BOOK_ID, " +
"LOAN_DATE, " +
"MAX(SUB_NO) as SUB_NO, " +
"USER_ID, " +
"RETURN_SCHEDULE, " +
"RETURN_DATE " +
"FROM " +
"LENDING_DETAIL " +
"WHERE " +
"BOOK_ID = '{0}' " +
"AND LOAN_DATE = " +
"(SELECT " +
"MAX(LOAN_DATE) " +
"FROM " +
"LENDING_DETAIL " +
"WHERE " +
"BOOK_ID = '{0}' " +
"GROUP BY " +
"BOOK_ID " +
") " +
") as table0 " +
"LEFT OUTER JOIN " +
"BOOK_GENRE_MASTER as table1 " +
"ON " +
"DIVISION_ID1 = table1.DIVISION_ID " +
"LEFT OUTER JOIN " +
"BOOK_GENRE_MASTER as table2 " +
"ON " +
"DIVISION_ID2 = table2.DIVISION_ID " +
"LEFT OUTER JOIN " +
"BOOK_GENRE_MASTER as table3 " +
"ON " +
"DIVISION_ID3 = table3.DIVISION_ID " +
"LEFT OUTER JOIN " +
"USER_MASTER as table4 " +
"ON " +
"table4.USER_ID = table0.USER_ID " +
"WHERE " +
"BOOK_MASTER.BOOK_ID = '{0}' "
, txtId.Text);
// フィールドにセット
string[] bookColumns = dba.GetRecord(query);
txtTitle.Text = bookColumns[0];
txtCategory1.Text = bookColumns[1];
txtCategory2.Text = bookColumns[2];
txtCategory3.Text = bookColumns[3];
txtUserName.Text = bookColumns[4];
dateLoan.Value = DateTime.Parse(bookColumns[5]);
dateReturnSchedule.Value = DateTime.Parse(bookColumns[6]);
subNo = int.Parse(bookColumns[7]);
dateReturn.Value = DateTime.Now;
}
/// <summary>
/// 貸出履歴画面を表示する
/// </summary>
private void LendingHistoryGridView()
{
string query = string.Format("SELECT " +
"table4.BOOK_ID as '書籍ID', " +
"table4.BOOK_NAME as 'タイトル', " +
"table4.DIVISION_NAME1 as 分類1, " +
"table4.DIVISION_NAME2 as '分類2', " +
"table4.DIVISION_NAME3 as '分類3', " +
"table3.USER_NAME as 'ユーザー名', " +
"table2.LOAN_DATE as '貸出日時', " +
"CASE WHEN table4.LENDING_STATUS=0 THEN table2.RETURN_SCHEDULE ELSE '' End as '返却予定日', " +
"CASE WHEN table4.LENDING_STATUS=0 THEN '' ELSE table2.RETURN_DATE End as '返却日時', " +
"CASE table4.LENDING_STATUS WHEN 0 THEN '貸出中' WHEN 1 THEN '' End as '貸出状態' " +
"FROM " +
"(SELECT " +
"LENDING_DETAIL.* " +
"FROM " +
"(SELECT BOOK_ID, MAX(LOAN_DATE) as LOAN_DATE FROM LENDING_DETAIL GROUP BY BOOK_ID) as table1 " +
"LEFT OUTER JOIN " +
"LENDING_DETAIL " +
"ON " +
"table1.BOOK_ID = LENDING_DETAIL.BOOK_ID " +
"AND table1.LOAN_DATE = LENDING_DETAIL.LOAN_DATE " +
"GROUP BY " +
"LENDING_DETAIL.BOOK_ID) as table2 " +
"LEFT OUTER JOIN " +
"USER_MASTER as table3 " +
"ON " +
"table3.USER_ID = table2.USER_ID " +
"LEFT OUTER JOIN " +
"(SELECT " +
"BOOK_MASTER.*, " +
"dv1.DIVISION_NAME as DIVISION_NAME1, " +
"dv2.DIVISION_NAME as DIVISION_NAME2, " +
"dv3.DIVISION_NAME as DIVISION_NAME3, " +
"st.LENDING_STATUS " +
"FROM " +
"BOOK_MASTER " +
"LEFT OUTER JOIN BOOK_GENRE_MASTER as dv1 ON dv1.DIVISION_ID = DIVISION_ID1 " +
"LEFT OUTER JOIN BOOK_GENRE_MASTER as dv2 ON dv2.DIVISION_ID = DIVISION_ID2 " +
"LEFT OUTER JOIN BOOK_GENRE_MASTER as dv3 ON dv3.DIVISION_ID = DIVISION_ID3 " +
"LEFT OUTER JOIN BOOK_STATUS as st ON st.BOOK_ID = BOOK_MASTER.BOOK_ID) as table4 " +
"ON " +
"table4.BOOK_ID = table2.BOOK_ID " +
"WHERE " +
"table4.BOOK_ID LIKE '%{0}%' " +
"AND table4.BOOK_NAME LIKE '%{1}%' ",
this.txtId.Text, this.txtTitle.Text);
// 分類1、検索条件を追加
if (this.cmbCategory1.SelectedIndex > IS_CMB_BOX_EMPTY)
{
query += string.Format("AND table4.DIVISION_ID1 = '{0}' ", cmbCategory1.SelectedValue);
}
// 分類2、検索条件を追加
if (this.cmbCategory2.SelectedIndex > IS_CMB_BOX_EMPTY)
{
query += string.Format("AND table4.DIVISION_ID2 = '{0}' ", cmbCategory2.SelectedValue);
}
// 分類3、検索条件を追加
if (this.cmbCategory3.SelectedIndex > IS_CMB_BOX_EMPTY)
{
query += string.Format("AND table4.DIVISION_ID3 = '{0}' ", cmbCategory3.SelectedValue);
}
// 貸出状態のチェックを入れていると、貸出中でないレコードを抽出
if (this.chkLending.Checked)
{
query += string.Format("AND table4.LENDING_STATUS = 1 ");
}
// ユーザ名で検索を追加
if (!(string.IsNullOrEmpty(this.txtUserName.Text)))
{
query += string.Format("AND table3.USER_NAME LIKE '%{0}%' ", this.txtUserName.Text);
}
// 返却予定超過
if (chkScheduleOver.Checked)
{
query += string.Format("AND table2.RETURN_SCHEDULE < datetime('now') ");
}
// 日付条件
query += string.Format("AND LOAN_DATE >= datetime('{0}') AND LOAN_DATE <= datetime('{1}')",
dateLoanStart.Value.ToString("yyyy-MM-dd"), dateLoanEnd.Value.ToString("yyyy-MM-dd"));
// GROUP BYを追加
query += string.Format(" GROUP BY table2.BOOK_ID ");
DBAdapter dba = SingletonObject.GetDbAdapter();
dataGridView1.Table = dba.execSQL(query);
InitGridView();
}
/// <summary>
/// 確定ボタン
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnApply_Click(object sender, EventArgs e)
{
#region チェック
try
{
ErrorCheck();
}
catch (InputException ex)
{
//処理実行
MessageBox.Show(ex.Message);
ex.ERROR_TEXTBOX.Focus();
// error_code=3はデータをリストアせずに終了
if (ex.ERROR_CODE == GlobalDefine.ERROR_CODE[3].code)
{
return;
}
// テキストボックス内の文字列を、更新前に戻す
RestoreRecordData(ex.ERROR_TEXTBOX);
return;
}
#endregion
// チェックして問題なければ、DBレコード追加or更新
switch (this.mode)
{
// 新規作成モード
case MODE.ADD:
if (base.AskMessageBox(GlobalDefine.MESSAGE_ASK_REGISTRATION))
{
DBAdapter dba = SingletonObject.GetDbAdapter();
// 新規ID取得(MAX値+1)
string newStringId = dba.GetMaxID("COMPANY_ID", "COMPANY_MASTER");
newStringId = newStringId.Substring(1);
int newId = int.Parse(newStringId) + 1;
// 0埋めして新規ID作成
string newIdText = string.Format("C{0:D3}", newId);
string query = string.Format("INSERT INTO COMPANY_MASTER VALUES('{0}','{1}','{2}')",
newIdText,
this.textName.Text,
this.textAbbreviation.Text);
// DBにインサート文を投げる
dba.nonExecSQL(query);
// 完了メッセージを出力
MessageBox.Show(GlobalDefine.MESSAGE_COMPLETE_REGISTRATION);
// 画面起動時に保持した変数を、現在の画面の値に更新
BeforeRecordData();
// IDを画面に表示
this.textId.Text = newIdText;
// 編集モードに変更
this.mode = MODE.MOD;
// 編集モードでダイアログ作り直す(DataRowデータは渡さない)
InitDailog(this.mode);
}
break;
// 編集モード
case MODE.MOD:
if (base.AskMessageBox(GlobalDefine.MESSAGE_ASK_UPDATE))
{
DBAdapter dba = SingletonObject.GetDbAdapter();
string query = string.Format("UPDATE COMPANY_MASTER SET COMPANY_NAME='{1}', COMPANY_ABBREVIATION='{2}' WHERE COMPANY_ID='{0}'",
this.textId.Text,
this.textName.Text,
this.textAbbreviation.Text);
// DBにインサート文を投げる
dba.nonExecSQL(query);
// 完了メッセージを出力
MessageBox.Show(GlobalDefine.MESSAGE_COMPLETE_UPDATE);
// 画面起動時に保持した変数を、現在の画面の値に更新
BeforeRecordData();
}
break;
}
}
