public top()
{
InitializeComponent();
//DBファイルが無い時にテーブル作成(開発中にDBを消して作り直す時に外す)
//SQLiteConnect.make_db();
//DB書き込み時に不要な領域自動解放するよう設定
SQLiteConnect.Excute("PRAGMA auto_vacuum = FULL");
//外部キー設定の有効化
SQLiteConnect.Excute("PRAGMA foreign_keys = true");
//簡易検索のジャンルをロード
searchKind.Items.Add("全て");
searchKind.Items.Add("作品タイトル");
searchKind.Items.Add("作家名");
searchKind.Items.Add("サークル名");
searchKind.Items.Add("キャラ名");
searchKind.SelectedIndex = 0;
//ListViewItemComparerの作成と設定
listViewItemSorter = new ListViewItemComparer();
listViewItemSorter.ColumnModes = new ListViewItemComparer.ComparerMode[] {
ListViewItemComparer.ComparerMode.String,
ListViewItemComparer.ComparerMode.Integer
};
}
public detail_search()
{
InitializeComponent();
bookName.Focus();
//ジャンルのコンボボックスの中身の読み込み
string query = "SELECT genre_title FROM t_genre";
SQLiteConnect.ComboBoxLoad(ref genreForm, query, "genre_title");
query = "SELECT origin_title FROM t_origin";
SQLiteConnect.ComboBoxLoad(ref originForm, query, "origin_title");
//保管場所一覧をロード
query = "SELECT place_name FROM t_storage";
SQLiteConnect.ComboBoxLoad(ref storage, query, "place_name");
DateTime dt = DateTime.Today;
for (int cnt = 0; cnt < 15; cnt++)
{
bYear.Items.Add(dt.Year - cnt);
aYear.Items.Add(dt.Year - cnt);
}
for (int cnt = 1; cnt <= 12; cnt++)
{
bMonth.Items.Add(cnt);
aMonth.Items.Add(cnt);
}
for (int cnt = 1; cnt <= 31; cnt++)
{
bDay.Items.Add(cnt);
aDay.Items.Add(cnt);
}
}
public add()
{
InitializeComponent();
this.Text = "新規追加";
string query;
SQLiteDataReader reader = null;
int newID;
//レコードが0件の場合は1に設定
if (SQLiteConnect.checkRecord("t_doujinshi") == 0)
{
newID = 1;
}
//レコードが存在する場合は新規IDをアサイン
else
{
query = "SELECT MAX(ID) FROM t_doujinshi";
SQLiteConnect.Excute(query, ref reader);
reader.Read();
newID = int.Parse(reader["Max(ID)"].ToString()) + 1;
reader.Close();
SQLiteConnect.conn.Close();
}
//新規IDをidFormに格納
idForm.Text = newID.ToString();
}
//保管先が変更された時に書棚を当該場所の内容に変える
protected void storage_SelectedIndexChanged(object sender, EventArgs e)
{
//別のreader読み込み処理中にconn.Close()してしまう減少を回避する
if (SQLiteConnect.conn.State == ConnectionState.Open)
{
return;
}
SQLiteDataReader reader = null;
string query = "SELECT place_ID FROM t_storage WHERE place_name = '" + storage.Text + "'";
SQLiteConnect.Excute(query, ref reader);
if (reader != null)
{
reader.Read();
// 保管場所IDの変更
placeID = Convert.ToInt32(reader["place_ID"]);
reader.Close();
}
SQLiteConnect.conn.Close();
//本棚の内容変更
query = "SELECT shelf_name FROM t_house_shelf WHERE " +
"(SELECT place_ID FROM t_storage WHERE place_name = '" + storage.Text + "') = t_house_shelf.place_ID";
bookShelf.Items.Clear();
SQLiteConnect.ComboBoxLoad(ref bookShelf, query, "shelf_name");
bookShelf.SelectedIndex = 0;
}
//既存サークルや作者の候補を提案する
protected void Suggest(TextBox textbox, KeyEventArgs e, string column_name)
{
string inputtingWord = textbox.Text.Split(',').Last();
//,より後の入力文字が2文字以上の時のみ候補をサジェストする
if (inputtingWord.Length < 2 || e.KeyCode != Keys.Enter)
{
return;
}
string query = "SELECT DISTINCT " + column_name + " FROM t_" + column_name + " WHERE " + column_name + " LIKE '" + inputtingWord + "%' LIMIT 1";
SQLiteDataReader reader = null;
SQLiteConnect.Excute(query, ref reader);
if (reader != null)
{
if (reader.HasRows)
{
reader.Read();
string remnant = reader[column_name].ToString();
//未入力の文字列を差分としてremnantに取得
if (inputtingWord.CompareTo(remnant) != 0)
{
remnant = remnant.Substring(inputtingWord.Length);
int currentEnd = textbox.Text.Length;
textbox.AppendText(remnant);
textbox.Select(currentEnd, textbox.Text.Length - 1);
}
}
}
reader.Close();
SQLiteConnect.conn.Close();
}
//ロード処理
private void update_Load(object sender, EventArgs e)
{
//ロード時のID表示
idForm.Text = resultArray[currentIndex];
string date;//分布日を挿入する
string query;
SQLiteDataReader reader = null;
string select_all = "SELECT タイトル,サークル,作者,origin_title AS 作品,genre_title AS ジャンル, 頒布日, 対象年齢, place_ID, 場所, 本棚, キャラ ";
//WHERE句記入
string where = "WHERE t_doujinshi.ID = " + resultArray[currentIndex].ToString();
//SQL発行
query = select_all + SQLiteConnect.getFullInfoFrom + where + SQLiteConnect.getFullInfoLatter;
SQLiteConnect.Excute(query, ref reader);
if (reader != null)
{
reader.Read();
titleForm.Text = beforeTitle = reader["タイトル"].ToString();
circleForm.Text = reader["サークル"].ToString();
authorsForm.Text = reader["作者"].ToString();
originComboBox.Text = reader["作品"].ToString();
genreComboBox.Text = reader["ジャンル"].ToString();
date = reader["頒布日"].ToString();
yearForm.Text = date.Substring(0, 4);
monthForm.Text = date.Substring(4, 2);
dayForm.Text = date.Substring(6, 2);
foreach (RadioButton rb in ageLimit.Controls)
{
if (rb.Tag.ToString() == reader["対象年齢"].ToString())
{
rb.Checked = true;
}
}
placeID = int.Parse(reader["place_ID"].ToString());
//ここでstorage_SelectedIndexChangedが動いてしまうので処理先で無理矢理動かさない
storage.Text = reader["場所"].ToString();
bookShelf.Text = reader["本棚"].ToString();
mainChara.Text = reader["キャラ"].ToString();
reader.Close();
SQLiteConnect.conn.Close();
//場所名変更時に変更出来なかった本棚一覧を改めて動かす
storage_SelectedIndexChanged(sender, e);
//イメージ表示
string imageFilePath = @"Thumbnail\" + idForm.Text + "_" + titleForm.Text + ".jpg";
//MessageBox.Show(imageFilePath);
if (File.Exists(imageFilePath))
{
pictureBox.Image = Image.FromFile(imageFilePath);
}
else
{
pictureBox.Image = Image.FromFile(@"Thumbnail\NoImage.jpg");
}
restoreColors();
}
}
//検索と結果読み込み処理
private void RoadResult(string WHEREphrase)
{
const string seach_query = "SELECT main.ID,タイトル,サークル,作者,origin_title AS 作品,頒布日 "
+ SQLiteConnect.getFullInfoFrom + SQLiteConnect.getFullInfoLatter;
//リストビューへの読み出し
listView.Items.Clear(); //二回目以降の多重出力を回避
SQLiteDataReader reader = null;
SQLiteConnect.Excute(seach_query + WHEREphrase, ref reader);
if (reader == null)
{
MessageBox.Show("結果を取得出来ませんでした");
return;
}
while (reader.Read())
{
string[] items = { reader["ID"].ToString(),
reader["タイトル"].ToString(),
reader["サークル"].ToString(),
reader["作者"].ToString(),
reader["作品"].ToString(),
Date.insert_y_m_d(reader["頒布日"].ToString()) };
listView.Items.Add(new ListViewItem(items));
}
reader.Close();
SQLiteConnect.conn.Close();
}
public BookBase()
{
InitializeComponent();
//継承先フォームではデザインビュー閲覧時にコンストラクタが実行されてしまう
//回避策としてデザインモード中は実行しない
if (this.IsDesignMode())
{
return;
}
string query;
//作品一覧をロード
query = "SELECT origin_title FROM t_origin";
SQLiteConnect.ComboBoxLoad(ref originComboBox, query, "origin_title");
originComboBox.SelectedIndex = 0;
//ジャンル一覧をロード
query = "SELECT genre_title FROM t_genre";
SQLiteConnect.ComboBoxLoad(ref genreComboBox, query, "genre_title");
genreComboBox.SelectedIndex = 0;
//保管場所一覧をロード
query = "SELECT place_name FROM t_storage";
SQLiteConnect.ComboBoxLoad(ref storage, query, "place_name");
storage.SelectedIndex = 0;
//storage.Textがchangedするので本棚一覧もロードされる
placeID = 1; //保管場所を最初の項目で初期化
//西暦にローカルタイムから過去15年分を追加
DateTime dt = DateTime.Today;
for (int cnt = 0; cnt < 15; cnt++)
{
yearForm.Items.Add(dt.Year - cnt);
}
for (int cnt = 1; cnt <= 12; cnt++)
{
monthForm.Items.Add(cnt);
}
for (int cnt = 1; cnt <= 31; cnt++)
{
dayForm.Items.Add(cnt);
}
//サムネのDrag&Dropを許可
pictureBox.AllowDrop = true;
//サムネイルの読み込み
pictureBox.Image = Image.FromFile(@"Thumbnail\NoImage.jpg");
}
public static void lording(ref ListView listview, string query, string column1, string column2)//ロード処理、リストビュー版
{
SQLiteDataReader reader = null;
SQLiteConnect.Excute(query, ref reader);
if (reader != null)
{
while (reader.Read())
{
string[] item = { (reader[column1].ToString()), (reader[column2].ToString()) };
listview.Items.Add(new ListViewItem(item));
}
reader.Close();
SQLiteConnect.conn.Close();
}
}
//エクスポート処理
private void export_Click(object sender, EventArgs e)
{
//OKボタンがクリックされたときインポートする
if (exportFileDialog.ShowDialog() == DialogResult.OK)
{
Stream stream = exportFileDialog.OpenFile();
if (stream != null)
{
//全情報を連結したSQLクエリの作成
const string select = "SELECT main.ID,タイトル,サークル,作者,origin_title,genre_title,対象年齢,main.place_ID,場所,本棚,頒布日,キャラ ";
string query = select + SQLiteConnect.getFullInfoFrom + SQLiteConnect.getFullInfoLatter;
SQLiteDataReader reader = null;
SQLiteConnect.Excute(query, ref reader);
if (reader == null)
{
MessageBox.Show("SELECT結果が取得できませんでした");
return;
}
StreamWriter sw = new StreamWriter(stream, Encoding.GetEncoding("Shift_JIS"));
//ここで書き込み処理
while (reader.Read())
{
string[] items = { reader["ID"].ToString(),
reader["タイトル"].ToString(),
reader["origin_title"].ToString(),
reader["genre_title"].ToString(),
reader["対象年齢"].ToString(),
//複数サークル等を繋ぐカンマがcsvのカンマと誤認されないように置き換える
reader["サークル"].ToString().Replace(',',' '),
reader["作者"].ToString().Replace(',', ' '),
reader["頒布日"].ToString(),
reader["キャラ"].ToString(),
reader["場所"].ToString(),
reader["本棚"].ToString() };
for (int cnt = 0; cnt < items.Length; cnt++)
{
sw.Write(items[cnt] + ',');
}
sw.Write('\n');
}
SQLiteConnect.conn.Close();
sw.Close();
stream.Close();
MessageBox.Show("エクスポートが完了しました");
}
}
}
//インポート処理
private void import_Click(object sender, EventArgs e)
{
//OKボタンがクリックされたときインポートする
if (importFileDialog.ShowDialog() == DialogResult.OK)
{
var file = new StreamReader(importFileDialog.FileName, Encoding.GetEncoding("Shift_JIS"));
string line;
progress.Visible = true; //インポート状況可視化
int cnt_record = 0;
while ((line = file.ReadLine()) != null)
{
//一行単位で配列に格納してINSERT文に埋め込み
string[] subWords = line.Split(',');
if (subWords.Length != 8) //一行から取り出したカラム数が8個でない時は中断
{
MessageBox.Show("フォーマットが正しくありません\n処理を中断します"); break;
}
string ins_doujinshi = "INSERT INTO t_doujinshi (ID,title,origin_ID,genre_ID,age_limit,date,place_ID) VALUES(" +
subWords[0] + "," + "'" + subWords[1] + "'," + subWords[2] + "," + subWords[3] + "," +
"'" + subWords[4] + "'," + subWords[7] + ",1)";
SQLiteConnect.Excute(ins_doujinshi);
//subWords[5]=(サークル)とsubWords[6]=(作者)を全角スペースSplitして別レコードにしながら各テーブルに格納
string[] splited_circle = subWords[5].Split(' ');
string[] splited_author = subWords[6].Split(' ');
for (int cnt = 0; cnt < splited_circle.Length; cnt++)
{
string ins_circle = "INSERT INTO t_circle VALUES(" +
subWords[0] + ",'" + splited_circle[cnt] + "')";
SQLiteConnect.Excute(ins_circle);
}
for (int cnt = 0; cnt < splited_author.Length; cnt++)
{
string ins_author = "INSERT INTO t_author VALUES(" +
subWords[0] + ",'" + splited_author[cnt] + "')";
SQLiteConnect.Excute(ins_author);
}
progress.Text = ((++cnt_record).ToString() + "件読み込みました。");
Application.DoEvents();
}
file.Close();
progress.Visible = false;
RoadResult("");
}
}
private void Change1_Click(object sender, EventArgs e)//変更ボタンの処理 作品
{
string changequery;
string changetitle;
changetitle = Textbox1.Text;//変更する内容の取得
DialogResult res = MessageBox.Show(beforeListview1Title + "を" + changetitle + "に変更しますか？", "変更確認", MessageBoxButtons.YesNo);
if (res == DialogResult.Yes)
{
changequery = "update t_origin set origin_title = '" + changetitle + "'"
+ "where origin_id = " + beforeListView1ID; //クエリ組み立て
SQLiteConnect.Excute(changequery); //SQL文実行
Listview1.Items.Clear();
SQLiteConnect.lording(ref Listview1, "select origin_ID,origin_title from t_origin", "origin_ID", "origin_title");
//リフレッシュ処理
}
}
private void Change2_Click(object sender, EventArgs e)//変更ボタンの処理 ジャンル
{
string changequery;
string changetitle;
changetitle = Textbox2.Text;//変更する内容の取得
DialogResult res = MessageBox.Show(beforeListview2Title + "を" + changetitle + "に変更しますか？", "変更確認", MessageBoxButtons.YesNo);
if (res == DialogResult.Yes)
{
changequery = "update t_genre set genre_title = '" + changetitle + "'"
+ "where genre_id = " + beforeListview2ID;
SQLiteConnect.Excute(changequery);
Listview2.Items.Clear();
SQLiteConnect.lording(ref Listview2, "select genre_ID,genre_title from t_genre", "genre_ID", "genre_title");
//リフレッシュ処理
}
}
//保管先が変更された時に書棚を当該場所の内容に変える
private void storage_SelectedIndexChanged(object sender, EventArgs e)
{
SQLiteDataReader reader = null;
string query = "SELECT place_ID FROM t_storage WHERE place_name = '" + storage.Text + "'";
SQLiteConnect.Excute(query, ref reader);
if (reader != null)
{
reader.Read();
reader.Close();
}
SQLiteConnect.conn.Close();
//本棚の内容変更
query = "SELECT shelf_name FROM t_house_shelf WHERE " +
"(SELECT place_ID FROM t_storage WHERE place_name = '" + storage.Text + "') = t_house_shelf.place_ID";
bookShelf.Items.Clear();
SQLiteConnect.ComboBoxLoad(ref bookShelf, query, "shelf_name");
}
private void Change2_Click(object sender, EventArgs e)//要素変更 本棚の場所
{
string changequery;
string changetitle;
changetitle = Textbox2.Text;//変更する内容の取得
DialogResult res = MessageBox.Show(beforeListview2Title + "を" + changetitle + "に変更しますか？", "変更確認", MessageBoxButtons.YesNo);
if (res == DialogResult.Yes)
{
changequery = "update t_house_shelf set shelf_name = '" + changetitle + "'"
+ "where place_ID = " + beforeListView1ID + " and bookShelf_ID = " + beforeListview2ID; //クエリ組み立て
SQLiteConnect.Excute(changequery); //SQL文実行
Listview2.Items.Clear();
SQLiteConnect.lording(ref Listview2, "select bookShelf_ID,shelf_name from t_house_shelf where place_ID = " + beforeListView1ID, "bookShelf_ID", "shelf_name");
//リフレッシュ処理
}
}
private void Delete2_Click(object sender, EventArgs e)
{
int counter = 0;
counter = SQLiteConnect.counterID("genre", beforeListview2ID);
if (counter != 0)
{
MessageBox.Show("データが" + counter + "件存在するため、削除できません。", "警告", MessageBoxButtons.OK);
return;
}
string deletequery;
DialogResult res = MessageBox.Show(beforeListview2Title + "を削除しますか？", "削除確認", MessageBoxButtons.YesNo);
if (res == DialogResult.Yes)
{
deletequery = "delete from t_genre where genre_id =" + beforeListview2ID;
SQLiteConnect.Excute(deletequery);
Listview2.Items.Clear();
SQLiteConnect.lording(ref Listview2, "select genre_ID,genre_title from t_genre", "genre_ID", "genre_title");
Textbox2.Clear(); //リフレッシュ処理
}
}
private void Add1_Click(object sender, EventArgs e)//追加ボタンの処理 作品
{
string addquery;
string IDquery;
string addtitle;
int ID;
SQLiteDataReader reader = null; //リフレッシュ用
addtitle = Textbox1.Text; //textboxから追加したい文言の取得
DialogResult res = MessageBox.Show(addtitle + " を登録しますか？", //確認処理
"追加確認", MessageBoxButtons.YesNo);
if (res == DialogResult.Yes)
{
IDquery = "select max(origin_ID) from t_origin";//現在のID最大値取得sql
//追加作業部分
if (SQLiteConnect.checkRecord("t_origin") == 0)//レコードない時の処理
{
ID = 1;
}
else
{
SQLiteConnect.Excute(IDquery, ref reader);
reader.Read();
ID = int.Parse(reader["max(origin_ID)"].ToString()) + 1; //追加用の新規ID生成
reader.Close();
SQLiteConnect.conn.Close();
}
addquery = "INSERT into t_origin(origin_ID,origin_title)VALUES(" + ID + ","
+ "'" + Textbox1.Text + "')"; //追加用sql組み立て
SQLiteConnect.Excute(addquery); //登録作業
Listview1.Items.Clear();
SQLiteConnect.lording(ref Listview1, "select origin_ID,origin_title from t_origin", "origin_ID", "origin_title");
}
}
private void Delete1_Click(object sender, EventArgs e)//削除ボタンの処理 作品
{
string deletequery;
int counter = 0;
counter = SQLiteConnect.counterID("origin", beforeListView1ID);
if (counter != 0)
{
MessageBox.Show("データが" + counter + "件存在するため、削除できません。", "警告", MessageBoxButtons.OK);
return;
}
DialogResult res = MessageBox.Show(beforeListview1Title + "を削除しますか？", "削除確認", MessageBoxButtons.YesNo);
if (res == DialogResult.Yes)
{
deletequery = "delete from t_origin where origin_id =" + beforeListView1ID;
SQLiteConnect.Excute(deletequery);
Listview1.Items.Clear();
SQLiteConnect.lording(ref Listview1, "select origin_ID,origin_title from t_origin", "origin_ID", "origin_title");
Textbox1.Clear();
}
}
private void Add2_Click(object sender, EventArgs e)//新規追加 本棚の場所
{
string addquery;
string IDquery;
string addtitle;
int ID;
SQLiteDataReader reader = null; //リフレッシュ用
addtitle = Textbox2.Text; //textboxから追加したい文言の取得
DialogResult res = MessageBox.Show(addtitle + " を登録しますか？", //確認処理
"追加確認", MessageBoxButtons.YesNo);
if (res == DialogResult.Yes)
{
IDquery = "select max(bookShelf_ID) from t_house_shelf where place_ID = " + beforeListView1ID;//現在のID最大値取得sql
//追加作業部分
if (SQLiteConnect.checkRecord2("t_house_shelf", "place_ID", beforeListView1ID) == 0)//レコードない時の処理
{
ID = 1;
}
else
{
SQLiteConnect.Excute(IDquery, ref reader);
reader.Read();
ID = int.Parse(reader["max(bookShelf_ID)"].ToString()) + 1; //追加用の新規ID生成
reader.Close();
SQLiteConnect.conn.Close();
}
addquery = "INSERT into t_house_shelf(place_ID,bookShelf_ID,shelf_name)VALUES(" + beforeListView1ID + ","
+ ID + "," + "'" + Textbox2.Text + "')"; //追加用sql組み立て
SQLiteConnect.Excute(addquery); //登録作業
Listview2.Items.Clear();
SQLiteConnect.lording(ref Listview2, "select bookShelf_ID,shelf_name from t_house_shelf where place_ID = " + beforeListView1ID, "bookShelf_ID", "shelf_name");
}
}
//サークルが入力済みの場合、作者候補を自動で入れる
private void authorsForm_Enter(object sender, EventArgs e)
{
if (circleForm.Text.Length > 0 && authorsForm.Text.Length == 0)
{
string inputtedCircle = circleForm.Text.Split(',').First();
string query = "SELECT author FROM t_author WHERE " +
"(SELECT ID FROM t_circle WHERE circle = '" + inputtedCircle + "') = ID LIMIT 1";
SQLiteDataReader reader = null;
SQLiteConnect.Excute(query, ref reader);
if (reader != null)
{
if (reader.HasRows)
{
reader.Read();
string candidate = reader["author"].ToString();
authorsForm.AppendText(candidate);
authorsForm.SelectAll();
}
}
reader.Close();
SQLiteConnect.conn.Close();
}
}
private void Delete2_Click(object sender, EventArgs e)//要素削除 本棚の場所
{
string deletequery;
int counter = 0;
counter = SQLiteConnect.counterID2("place", beforeListView1ID, "bookShelf", beforeListview2ID);
if (counter != 0)
{
MessageBox.Show("データが" + counter + "件存在するため、削除できません。", "警告", MessageBoxButtons.OK);
return;
}
DialogResult res = MessageBox.Show(beforeListview2Title + "を削除しますか？", "削除確認", MessageBoxButtons.YesNo);
if (res == DialogResult.Yes)
{
deletequery = "delete from t_house_shelf where place_ID =" + beforeListView1ID
+ " and bookShelf_ID = " + beforeListview2ID;
SQLiteConnect.Excute(deletequery);
Listview2.Items.Clear();
SQLiteConnect.lording(ref Listview2, "select bookShelf_ID,shelf_name from t_house_shelf where place_ID = " + beforeListView1ID, "bookShelf_ID", "shelf_name");
Textbox2.Clear();
}
}
private void dealing_Click(object sender, EventArgs e)
{
//空白箇所の提言
if (checkNullForm())
{
return;
}
string query;
//UPDATE処理
query = "UPDATE t_doujinshi ";
query += "SET ";
query += "title = '" + titleForm.Text + "'," +
"origin_ID = (SELECT origin_ID FROM t_origin WHERE '" + originComboBox.Text + "' = origin_title)," +
"genre_ID = (SELECT genre_ID FROM t_genre WHERE '" + genreComboBox.Text + "' = genre_title)," +
"date = " + Date.merge(yearForm.Text, monthForm.Text, dayForm.Text) + "," +
"age_limit = '";
foreach (RadioButton rb in ageLimit.Controls)
{
if (rb.Checked)
{
query += rb.Tag.ToString();
}
}
query += "',main_chara = '" + mainChara.Text + "'";
query += "," + "place_ID = " + placeID;
query += "," + "bookShelf_ID = (SELECT bookShelf_ID FROM t_house_shelf WHERE shelf_name = '" + bookShelf.Text + "' )";
query += " WHERE " + resultArray[currentIndex] + " = ID";
//t_doujinshi更新
SQLiteConnect.Excute(query);
//サークルテーブル処理
//DERETE文処理
query = "DELETE FROM t_circle ";
query += "WHERE " + resultArray[currentIndex] + " = ID";
SQLiteConnect.Excute(query);
if (circleForm.Text != "")
{
string[] circle = circleForm.Text.Split(',');
for (int cnt = 0; cnt < circle.Length; cnt++)
{
//INSERT文処理
query = "INSERT INTO t_circle ";
query += "VALUES (" + resultArray[currentIndex] + ",'" + circle[cnt] + "')";
SQLiteConnect.Excute(query);
}
}
//作者名テーブル処理
//DERETE文
query = "DELETE FROM t_author ";
query += "WHERE " + resultArray[currentIndex] + " = ID";
SQLiteConnect.Excute(query);
if (authorsForm.Text != "")
{
string[] authors = authorsForm.Text.Split(',');
for (int cnt = 0; cnt < authors.Length; cnt++)
{
query = "INSERT INTO t_author ";
query += "VALUES (" + resultArray[currentIndex] + ",'" + authors[cnt] + "')";
SQLiteConnect.Excute(query);
}
}
//書き込み処理
string imageFileNameBefore = @"Thumbnail\" + idForm + "_" + beforeTitle + ".jpg";
string imageFileNameAfter = @"Thumbnail\" + idForm + "_" + titleForm.Text + ".jpg";
//ファイル名変更判定
if (File.Exists(imageFileNameBefore))
{
if (titleForm.BackColor == Color.Cyan)
{
File.Move(imageFileNameBefore, imageFileNameAfter);
}
}
MessageBox.Show("更新しました");
restoreColors();
}
//登録ボタンの処理
private void dealing_Click(object sender, EventArgs e)
{
//必須箇所が抜けていれば埋めるように促す
if (checkNullForm())
{
return;
}
//チェックボタンの結果格納
string agelimit = null;
foreach (RadioButton rb in ageLimit.Controls)
{
if (rb.Checked)
{
agelimit = rb.Tag.ToString();
}
}
//SQL文組み立て
//作品IDとジャンルIDはコンボボックスの値からSQLの副問合せを利用
string insert_doujinshi = "INSERT INTO t_doujinshi(ID,title,origin_ID,genre_ID,age_limit,date,place_ID,bookShelf_ID,main_chara)" +
"VALUES(" + idForm.Text + "," //ID
+ "'" + titleForm.Text + "'," //タイトル
+ "(SELECT origin_ID FROM t_origin WHERE '" + originComboBox.Text + "' = origin_title)," //作品ID
+ "(SELECT genre_ID FROM t_genre WHERE '" + genreComboBox.Text + "' = genre_title)," //ジャンルID
+ "'" + agelimit + "'," //対象年齢ID
+ Date.merge(yearForm.Text, monthForm.Text, dayForm.Text) + "," //頒布年月日
+ placeID + "," //場所
+ "(SELECT bookShelf_ID FROM t_house_shelf WHERE place_ID = " + placeID + " AND shelf_name = '" + bookShelf.Text + "')," //本棚ID
+ "'" + mainChara.Text + "')"; //メインキャラ
//t_doujinshi登録
SQLiteConnect.Excute(insert_doujinshi);
//t_author登録
if (authorsForm.Text.Length != 0)
{
string[] authors = authorsForm.Text.Split(',');
for (int cnt = 0; cnt < authors.Length; cnt++)
{
string insert_author = "INSERT INTO t_author(ID,author)" +
"VALUES(" + idForm.Text + ", '" + authors[cnt] + "')";
SQLiteConnect.Excute(insert_author);
}
}
//t_circle登録
if (circleForm.Text.Length != 0)
{
string[] circles = circleForm.Text.Split(',');
for (int cnt = 0; cnt < circles.Length; cnt++)
{
string insert_circle = "INSERT INTO t_circle(ID,circle)" +
"VALUES(" + idForm.Text + ", '" + circles[cnt] + "')";
SQLiteConnect.Excute(insert_circle);
}
}
//サムネイル登録
if (changedThumbnail)
{
pictureBox.Image.Save(@"Thumbnail\" + idForm.Text + "_" + titleForm.Text + ".jpg", ImageFormat.Jpeg);
changedThumbnail = false;
}
MessageBox.Show("登録しました");
//連続登録し易いようにフォーカスを戻す
this.titleForm.Focus();
//ID連番を回す
idForm.Text = ((int.Parse(idForm.Text)) + 1).ToString();
//登録後のフォーム初期化
//連続登録時に重複しやすい年月日は残しておく
pictureBox.Image = Image.FromFile(@"Thumbnail\NoImage.jpg");
titleForm.Clear();
authorsForm.Clear();
circleForm.Clear();
mainChara.Clear();
}
private void table_manage_Load(object sender, EventArgs e)//フォームロード時の処理
{
SQLiteConnect.lording(ref Listview1, "select origin_ID,origin_title from t_origin", "origin_ID", "origin_title");
SQLiteConnect.lording(ref Listview2, "select genre_ID,genre_title from t_genre", "genre_ID", "genre_title");
}
private void shelf_manage_Load(object sender, EventArgs e)
{
SQLiteConnect.lording(ref Listview1, "select place_ID,place_name from t_storage", "place_ID", "place_name");
}
private void Listview1_SelectedIndexChanged_1(object sender, EventArgs e)
{
Listview2.Items.Clear();
SQLiteConnect.lording(ref Listview2, "select bookShelf_ID,shelf_name from t_house_shelf where place_ID = " + beforeListView1ID, "bookShelf_ID", "shelf_name");
}
