/// <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]);
}
/// <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>
/// 書籍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>
/// <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;
}
}