private void b_decrease_Click(object sender, EventArgs e)
{
using (var dbc = new DB.DBConnect())
{
var q = from t in dbc.m_facility
where t.delete_flag == "0"
select t;
totalPage = q.Count();
}
if (currentPage > 1)
{
if (changeValue == false)
{
currentPage--;
set_currentDisplay();
}
else
{
DialogResult result = MessageBox.Show("変更が保存されていません\n" +
"変更を破棄して次の処理へ進みますか", "", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
currentPage--;
set_currentDisplay();
}
}
}
}
//◀ボタン[クリック]処理
private void b_decrease_Click(object sender, EventArgs e)
{
d_tenpo.Enabled = false;
t_staff_code.Enabled = false;
using (var dbc = new DB.DBConnect())
{
var q = from t in dbc.m_staff
select t;
totalPage = q.Count();
}
//初期化
if (currentPage > 1)
{
if (changeValue == false)
{
currentPage--;
Listshow();
Set_currentDisplay();
}
else
{
DialogResult result = MessageBox.Show("変更が保存されていません。\n" +
"変更を破棄して次の処理へ進みますか？", "",
MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
currentPage--;
Listshow();
Set_currentDisplay();
}
}
}
}
private void b_new_regist_Click(object sender, EventArgs e)
{
using (var dbc = new DB.DBConnect())
{
var q = from t in dbc.m_facility
select t;
totalPage = q.Count();
}
if (changeValue == false)
{
totalPage++;
currentPage = totalPage;
Set_initialDisplay();
}
else
{
DialogResult result = MessageBox.Show("変更が保存されていません\n" +
"変更を破棄して次の処理へ進みますか", "", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
totalPage++;
currentPage = totalPage;
Set_initialDisplay();
}
}
}
//新規登録ボタン
private void b_new_regist_Click(object sender, EventArgs e)
{
t_tenpo_code.Enabled = true;
using (var dbc = new DB.DBConnect())
{
var q = from t in dbc.m_store
where t.delete_flag == "0"
select t;
totalPage = q.Count();
}
//初期化
if (changeValue == false)
{
totalPage++;
currentPage = totalPage;
Set_initialDisplay();
}
else
{
DialogResult result = MessageBox.Show("変更が保存されていません。\n" +
"変更を破棄して次の処理へ進みますか？", "",
MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
totalPage++;
currentPage = totalPage;
Set_initialDisplay();
}
}
}
private void b_print_exclusion_Click(object sender, EventArgs e)
{
//印刷除外フラグを更新する。
using (var db = new DB.DBConnect())
{
;
//オープン処理がないと怒られる。
db.npg.Open();
//StringBuilder sb = new StringBuilder();
for (int i = 0; i < dataGridView1.RowCount; i++)
{
string code = (string)dataGridView1.Rows[i].Cells[1].Value;
bool noprint = (bool)dataGridView1.Rows[i].Cells[0].Value;
var reception = db.t_reception.Single(x => x.reception_code == code);
if (noprint)
{
reception.noprint = "1";
}
else
{
reception.noprint = "0";
}
reception.update_date = DateTime.Now;
reception.update_staff = MainForm.session_m_staff != null? MainForm.session_m_staff.staff_name:"";
db.SaveChanges();
}
}
}
private void b_folder_Click(object sender, EventArgs e)
{
string str = Interaction.InputBox("格納先フォルダを指定してください。", DefaultResponse: Save_dir.Trim());
if (str != "")
{
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append("select count(s.folder_name) as count ");
sb.Append("from t_selection as s ");
sb.Append("where s.folder_name = '@folder_name_target' ".Replace("@folder_name_target", str));
sb.Append("and s.folder_name <> '@folder_name' ".Replace("@folder_name", Save_dir));
NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), dbc.npg);
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
if ((long)dataReader["count"] != 0)
{
MessageBox.Show("登録済みの格納先フォルダのため、指定できません。");
}
else
{
Save_dir = str;
}
}
}
}
}
private void sescheduleTabList(DateTime selectDate, string selectStore)
{
//スケジュール一覧設定
d_schedule_list.Rows.Clear();
sescheduleFrList = new List <string>();
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT to_char(fr.start_date,'yyyy/MM/dd') || to_char(fr.start_time, ' hh24:mi') || ' ～ ' || to_char(fr.end_date, 'yyyy/MM/dd') || to_char(fr.end_time, ' hh24:mi') as datetime ");
sb.Append(", fr.remarks ");
sb.Append(", fr.facility_reservation_code ");
sb.Append("from t_facility_reservation as fr ");
sb.Append("left join t_reservation as res ");
sb.Append("on res.facility_reservation_code = fr.facility_reservation_code ");
sb.Append("where @date BETWEEN fr.start_date and fr.end_date ");
sb.Append("and @store_code = fr.store_code ");
sb.Append("and res.reception_code is NULL ");
sb.Append("order by fr.start_date,fr.start_time,fr.end_date,fr.end_time,fr.remarks ");
NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), dbc.npg);
command.Parameters.Add(new NpgsqlParameter("@date", selectDate));
command.Parameters.Add(new NpgsqlParameter("@store_code", selectStore));
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
d_schedule_list.Rows.Add(
dataReader["datetime"],
dataReader["remarks"]
);
sescheduleFrList.Add(dataReader["facility_reservation_code"].ToString());
}
}
}
private void dataGridView1CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
if (pageParent.Name == MainForm.Customer_information.Name)
{
MainForm.Customer_information.customer = DB.m_customer.getSingle(customer_code);
MainForm.Customer_information.member = DB.m_member.getSingle(customer_code);
using (var dbc = new DB.DBConnect())
{
MainForm.Customer_information.familyList = new List <DB.m_family>();
var q = from t in dbc.m_family
where t.customer_code == customer_code && t.delete_flag == "0"
select t;
foreach (var data in q)
{
MainForm.Customer_information.familyList.Add(data);
}
}
MainForm.Customer_information.PageRefreshForEdit();
MainForm.sendPage(this, MainForm.Customer_information);
MainForm.Customer_information.pageParent = MainForm.Main_menu;
}
else
{
MainForm.Reception.pageParent = this;
MainForm.Reception.PageRefresh();
MainForm.sendPage(this, MainForm.Reception);
}
}
//削除ボタン
private void b_delete_Click(object sender, EventArgs e)
{
//データベース登録・更新処理
//削除用データの店舗コードを取得する(現時点では店舗コード入力欄に店舗コードが入っているものを削除している)
delStoreCode = this.t_tenpo_code.Text;
//店舗使用中チェック
using (var dbc = new DB.DBConnect())
{
//スタッフ
var q1 = from t in dbc.m_staff
where t.store_code == delStoreCode & t.delete_flag == "0"
select t;
if (q1.Count() != 0)
{
Utile.Message.showMessageOK("I13003");
return;
}
//施設
var q2 = from t in dbc.m_facility
where t.store_code == delStoreCode & t.delete_flag == "0"
select t;
if (q2.Count() != 0)
{
Utile.Message.showMessageOK("I13004");
return;
}
//衣装
var q3 = from t in dbc.m_costume
where t.store_code == delStoreCode & t.delete_flag == "0"
select t;
if (q3.Count() != 0)
{
Utile.Message.showMessageOK("I13005");
return;
}
}
// 接続インスタンスを作成。
m_store m_store = DB.m_store.getSingle(delStoreCode);
if (m_store != null)
{
m_store.Command(true);
}
else
{
Utile.Message.showMessageOK("E13000");
return;
}
//削除後、データの再取得し削除データの後データを読み込む
MainForm.Store_master.PageRefresh();
ListArrayStore();
Listshow();
Set_currentDisplay();
pageParent.PageRefresh();
MainForm.backPage(this);
}
private void b_delete_Click(object sender, EventArgs e)
{
//削除
using (var db = new DB.DBConnect())
{
//キーの設定
string store_code = storeCodeList.Find(x => x.store_name == d_tenpo.Text).store_code;
string facility_code = t_facility_code.Text;
/*複合キーの場合には、正しく動かない。
* var facility = db.m_facility.Single(x => x.facility_code == facility_code && x.store_code == store_code);
*
* if (facility != null)
* {
* //更新者・更新日・削除フラグの変更
* facility.update_date = DateTime.Now.Date;
* facility.update_staff = MainForm.session_m_staff.staff_name;
* facility.delete_flag = "1";
*
* db.SaveChanges();
* }
*/
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
using (var transaction = dbc.npg.BeginTransaction())
{
StringBuilder sb = new StringBuilder();
sb.Append(@"update m_facility ");
sb.Append(@"set update_date = " + "'" + DateTime.Now.Date + "' , " +
"update_staff =" + "'" + MainForm.session_m_staff.staff_name + "' , " +
"delete_flag = '1'"
);
sb.Append(@" where store_code = " + "'" + store_code + "' and " +
"facility_code =" + "'" + facility_code + "'");
var command = new NpgsqlCommand(sb.ToString(), dbc.npg);
try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (NpgsqlException)
{
transaction.Rollback();
throw;
}
}
}
}
//削除後、ひとつ前の画面に戻る
MainForm.Facility_master.PageRefresh();
MainForm.backPage(this);
}
public void db2tag()
{
try
{
foreach (tagControl tagControl in this.tagsPanel.Controls)
{
tagControl.imgNameList.Clear();
tagControl.imageList1.Images.Clear();
tagControl.thumbnailList.Items.Clear();
}
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append(@"select * ");
sb.Append(@"from t_selection as s ");
sb.Append(@"where s.reception_code = '@reception_code'".Replace("@reception_code", MainForm.session_t_reception.reception_code));
NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), dbc.npg);
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
int color = (int)dataReader["color"];
foreach (tagControl tagControl in this.tagsPanel.Controls)
{
if (tagControl.tagId == color)
{
var path = System.IO.Path.Combine(Secondary_Select_dir, ((string)dataReader["folder_name"]).Trim(), tagControl.tagId.ToString());
foreach (string fpath in System.IO.Directory.GetFiles(path, "*", System.IO.SearchOption.AllDirectories))
{
var fname = System.IO.Path.GetFileName(fpath);
foreach (var key in imageListBase.Images.Keys)
{
if (key.Replace("/", "_") == fname)
{
tagControl.imageList1.Images.Add(key, imageListBase.Images[imageListBase.Images.IndexOfKey(key)]);
ListViewItem lvi = new ListViewItem(key);
lvi.ImageIndex = tagControl.imageList1.Images.Count - 1;
tagControl.thumbnailList.Items.Add(lvi);
tagControl.imgNameList.Add(key);
}
}
}
tagControl.d_tagName.SelectedIndex = DB.m_product.getProductList().Values.ToList().IndexOf((string)dataReader["product_name"]);
tagControl.l_count.Text = tagControl.thumbnailList.Items.Count.ToString() + "枚";
tagControl.d_tag.Checked = tagControl.thumbnailList.Items.Count > 0 ? true : false;
}
}
}
}
}
catch
{
Utile.Message.showMessageOK("E12001");
}
}
public override void PageRefresh()
{
//写真選択フォームをすべて閉じる
selectPanel.Controls.Clear();
//写真格納フォルダ取得
photoDirList = DB.t_shooting_data.getPhotoDir(MainForm.session_t_reception.reception_code);
//二次選択フォルダ取得
DB.t_selection selection = DB.t_selection.getFirst(MainForm.session_t_reception.reception_code);
if (selection == null)
{
int index = 1;
DB.m_customer customer = DB.m_customer.getSingle(MainForm.session_t_reception.customer_code);
Save_dir = DateTime.Now.Date.ToString("yyyyMMdd") + "-" + index.ToString() + "-" + customer.surname;
StringBuilder sb = new StringBuilder();
while (sb.Length == 0)
{
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
sb.Append("select count(s.folder_name) as count ");
sb.Append("from t_selection as s ");
sb.Append("where s.folder_name = '@folder_name'".Replace("@folder_name", Save_dir));
NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), dbc.npg);
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
if ((long)dataReader["count"] != 0)
{
index++;
Save_dir = DateTime.Now.Date.ToString("yyyyMMdd") + index.ToString() + customer.name;
sb.Clear();
}
}
}
}
}
else
{
Save_dir = selection.folder_name;
}
//サムネイル一覧初期化
thumbnailListRefresh();
//DBからタグ一覧へ格納
db2tag();
//サムネイル一覧タグ付け
thumbnailListTagRefresh();
}
private void Set_intialDiaplay()
{
//storeCodeListの初期化
storeCodeList.Clear();
NpgsqlDataReader dataReader = null;
var dbc = new DB.DBConnect();
dbc.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append(@"select store_code, store_name ");
sb.Append(@"from m_store ");
sb.Append(@"where m_store.delete_flag = '0' order by m_store.store_code");
var command = new NpgsqlCommand(sb.ToString(), dbc.npg);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
//storeCodeListの生成
storeCodeList.Add(new storeCodeArray
{
store_code = dataReader["store_code"].ToString(),
store_name = dataReader["store_name"].ToString()
});
}
dbc.npg.Close();
//店舗名の生成
d_tenpo.Items.Clear();
for (int i = 0; i < storeCodeList.Count; i++)
{
d_tenpo.Items.Add(storeCodeList[i].store_name);
}
if (costumeDBList.Count == 0)
{
ShowBlank();
}
else
{
Listshow();
}
//変更フラグクリア
mod.reset();
//エラー項目クリア
chk.clear();
}
private void setYoyakuTabList(string reception_code)
{
//予約一覧設定
d_yoyaku_list.Rows.Clear();
yoyakuFrList = new List <string>();
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT fa.facility_name" +
", to_char(fr.start_date,'yyyy/MM/dd') as start_date" +
", to_char(fr.start_date,'yyyy/MM/dd') || to_char(fr.start_time, ' hh24:mi') || ' ～ ' || to_char(fr.end_date, 'yyyy/MM/dd') || to_char(fr.end_time, ' hh24:mi') as start_time" +
", fr.shooting_purpose, cus.surname || cus.\"name\" as name" +
", cus.sex " +
", fr.facility_reservation_code ");
sb.Append("from t_facility_reservation as fr ");
sb.Append("left join m_facility as fa ");
sb.Append("on fa.facility_code = fr.facility_code ");
sb.Append("left join m_store as st ");
sb.Append("on st.store_code = fr.store_code ");
sb.Append("left join t_reservation as res ");
sb.Append("on res.facility_reservation_code = fr.facility_reservation_code ");
sb.Append("left join t_reception as rec ");
sb.Append("on res.reception_code = rec.reception_code ");
sb.Append("left join m_customer as cus ");
sb.Append("on rec.customer_code = cus.customer_code ");
sb.Append("where @reception_code = res.reception_code ");
sb.Append("and res.reception_code is not NULL ");
sb.Append("order by fa.facility_name, fr.start_date, fr.start_time, fr.end_date, fr.end_time, fr.shooting_purpose, cus.surname, cus.\"name\", cus.sex ");
NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), dbc.npg);
command.Parameters.Add(new NpgsqlParameter("@reception_code", reception_code));
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
d_yoyaku_list.Rows.Add(
dataReader["facility_name"],
dataReader["name"],
(Utile.Data.性別)(int.Parse(dataReader["sex"].ToString())),
dataReader["start_date"],
dataReader["start_time"],
dataReader["shooting_purpose"]
);
yoyakuFrList.Add(dataReader["facility_reservation_code"].ToString());
}
}
}
//削除ボタン
private void b_delete_Click(object sender, EventArgs e)
{
//予約テーブルに1件でもあれば、削除不可
NpgsqlDataReader dataReader = null;
using (var db = new DB.DBConnect())
{
//オープン処理がないと怒られる。
db.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append(@"select
*
");
sb.Append(@"from
t_reservation
");
sb.Append(@" where reception_code = '@reception_code'".Replace("@reception_code", reception_code));
var command = new NpgsqlCommand(sb.ToString(), db.npg);
dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
MessageBox.Show("予約がすでに入ってますので、受付情報は削除できません。", "お知らせ", MessageBoxButtons.OK);
return;
}
}
//削除
using (var db = new DB.DBConnect())
{
//データ取得
var receptions = db.t_reception;
var data = receptions.Single(x => x.reception_code == reception_code);
//削除
receptions.Remove(data);
db.SaveChanges();
MessageBox.Show("受付情報を削除しました。", "お知らせ", MessageBoxButtons.OK);
}
//セッション情報の削除
if (MainForm.session_t_reception != null)
{
MainForm.session_t_reception = null;
}
MainForm.Header_Menu.LabelReWrite();
//一つ前のボタンに戻る。
pageParent.PageRefresh();
MainForm.backPage(this);
}
private void t_current_page_Leave(object sender, EventArgs e)
{
using (var dbc = new DB.DBConnect())
{
var q = from t in dbc.m_facility
where t.delete_flag == "0"
select t;
totalPage = q.Count();
}
chk.clear();
chk.addControl(t_current_page);
if (chk.check("W00003", chk.checkTextboxFormat, @"^\d{1,5}?\z", @"数値") ||
chk.check("W00000", chk.checkControlImportant))
{
return;
}
d_tenpo.Enabled = false;
t_facility_code.Enabled = false;
currentPage = int.Parse(t_current_page.Text);
if (currentPage > 0 && currentPage <= totalPage)
{
if (changeValue == false)
{
set_currentDisplay();
}
else
{
DialogResult result = MessageBox.Show("変更が保存されてません。\n" +
"処理を破棄して次の処理に進みますか？", "変更確認メッセージ",
MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
set_currentDisplay();
}
}
}
else
{
Utile.Message.showMessageOK("I14001");
}
}
public rental()
{
InitializeComponent();
if (init_flag == false)
{
//storeCodeListの初期化
storeList.Clear();
//dataReaderの初期化
NpgsqlDataReader dataReader = null;
//conectionの確立
var dbc = new DB.DBConnect();
//コネクションオープン
dbc.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append(@"select store_code, store_name ");
sb.Append(@"from m_store ");
sb.Append(@"where m_store.delete_flag = '0' order by m_store.store_code");
//SQLの実行
var command = new NpgsqlCommand(sb.ToString(), dbc.npg);
dataReader = command.ExecuteReader();
//データからListの生成
while (dataReader.Read())
{
//storeCodeListの生成
storeList.Add(new StoreArray
{
store_code = dataReader["store_code"].ToString(),
store_name = dataReader["store_name"].ToString()
});
}
//コネクションクロース
dbc.npg.Close();
//店舗名の生成
for (int i = 0; i < storeList.Count; i++)
{
d_rental_store.Items.Add(storeList[i].store_name);
}
//初期化フラグを初期化済みにする
init_flag = true;
d_rental_store.SelectedIndex = 0;
}
}
//◀ボタン[クリック]処理
private void b_decrease_Click(object sender, EventArgs e)
{
d_tenpo.Enabled = false;
t_costume_code.Enabled = false;
using (var dbc = new DB.DBConnect())
{
var q = from t in dbc.m_costume
select t;
totalPage = q.Count();
}
if (currentPage > 1)
{
if (!mod.chackMessage("処理"))
{
return;
}
currentPage--;
Listshow();
}
}
private void t_current_page_Leave(object sender, EventArgs e)
{
using (var dbc = new DB.DBConnect())
{
var q = from t in dbc.m_costume
where t.delete_flag == "0"
select t;
totalPage = q.Count();
}
chk.clear();
chk.addControl(t_current_page);
if (chk.check("W00003", chk.checkTextboxFormat, @"^\d{1,5}?\z", @"数値") ||
chk.check("W00000", chk.checkControlImportant))
{
return;
}
d_tenpo.Enabled = false;
t_costume_code.Enabled = false;
currentPage = int.Parse(t_current_page.Text);
if (currentPage > 0 && currentPage <= totalPage)
{
if (!mod.chackMessage("処理"))
{
return;
}
Listshow();
}
else
{
Utile.Message.showMessageOK("I15001");
}
}
private void setTabList(DateTime selectDate, string selectStore)
{
//予約一覧設定
d_yoyaku.Rows.Clear();
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT fa.facility_name" +
", to_char(fr.start_date,'yyyy/MM/dd') as start_date" +
", to_char(fr.start_date,'yyyy/MM/dd') || to_char(fr.start_time, ' hh24:mi') || ' ～ ' || to_char(fr.end_date, 'yyyy/MM/dd') || to_char(fr.end_time, ' hh24:mi') as start_time" +
", fr.shooting_purpose, cus.surname || cus.\"name\" as name" +
", cus.sex ");
sb.Append("from t_facility_reservation as fr ");
sb.Append("left join m_facility as fa ");
sb.Append("on fa.facility_code = fr.facility_code ");
sb.Append("left join m_store as st ");
sb.Append("on st.store_code = fr.store_code ");
sb.Append("left join t_reservation as res ");
sb.Append("on res.facility_reservation_code = fr.facility_reservation_code ");
sb.Append("left join t_reception as rec ");
sb.Append("on res.reception_code = rec.reception_code ");
sb.Append("left join m_customer as cus ");
sb.Append("on rec.customer_code = cus.customer_code ");
sb.Append("where @date BETWEEN fr.start_date and fr.end_date ");
sb.Append("and @store_code = fr.store_code ");
sb.Append("and res.reception_code is not NULL ");
NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), dbc.npg);
command.Parameters.Add(new NpgsqlParameter("@date", selectDate));
command.Parameters.Add(new NpgsqlParameter("@store_code", selectStore));
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
d_yoyaku.Rows.Add(
dataReader["facility_name"],
dataReader["name"],
(Utile.Data.性別)(int.Parse(dataReader["sex"].ToString())),
dataReader["start_date"],
dataReader["start_time"],
dataReader["shooting_purpose"]
);
}
}
//衣装予約一覧設定
d_isyou_yoyaku.Rows.Clear();
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT cr.costume_code ");
sb.Append(", st.store_name ");
sb.Append(", to_char(cr.start_date,'yyyy/MM/dd') || to_char(cr.start_time, ' hh24:mi') || ' ～ ' || to_char(cr.end_date, 'yyyy/MM/dd') || to_char(cr.end_time, ' hh24:mi') as datetime ");
sb.Append(", cus.surname || cus.\"name\" as name ");
sb.Append(", cus.sex ");
sb.Append(", cr.memo ");
sb.Append("FROM public.t_costume_reservation as cr ");
sb.Append("left join m_store as st ");
sb.Append("on st.store_code = cr.store_code ");
sb.Append("left join t_reservation as res ");
sb.Append("on res.costume_reservation_code = cr.costume_reservation_code ");
sb.Append("left join t_reception as rec ");
sb.Append("on rec.reception_code = res.reception_code ");
sb.Append("left join m_customer as cus ");
sb.Append("on cus.customer_code = rec.customer_code ");
sb.Append("WHERE @date BETWEEN cr.start_date and cr.end_date ");
sb.Append("and @store_code = cr.store_code ");
NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), dbc.npg);
command.Parameters.Add(new NpgsqlParameter("@date", selectDate));
command.Parameters.Add(new NpgsqlParameter("@store_code", selectStore));
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
d_isyou_yoyaku.Rows.Add(
dataReader["costume_code"],
dataReader["store_name"],
dataReader["datetime"],
dataReader["name"],
(Utile.Data.性別)(int.Parse(dataReader["sex"].ToString())),
dataReader["memo"]
);
}
}
//スケジュール一覧設定
d_sukejuru.Rows.Clear();
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT to_char(fr.start_date,'yyyy/MM/dd') || to_char(fr.start_time, ' hh24:mi') || ' ～ ' || to_char(fr.end_date, 'yyyy/MM/dd') || to_char(fr.end_time, ' hh24:mi') as datetime ");
sb.Append(", fr.remarks ");
sb.Append("from t_facility_reservation as fr ");
sb.Append("left join t_reservation as res ");
sb.Append("on res.facility_reservation_code = fr.facility_reservation_code ");
sb.Append("where @date BETWEEN fr.start_date and fr.end_date ");
sb.Append("and @store_code = fr.store_code ");
sb.Append("and res.reception_code is NULL; ");
NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), dbc.npg);
command.Parameters.Add(new NpgsqlParameter("@date", selectDate));
command.Parameters.Add(new NpgsqlParameter("@store_code", selectStore));
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
d_sukejuru.Rows.Add(
dataReader["datetime"],
dataReader["remarks"]
);
}
}
}
//登録
private void b_regist_Click(object sender, EventArgs e)
{
//入力チェック
chk.clear();
chk.addControl(d_tenpo);
chk.addControl(t_staff_code);
chk.addControl(t_staff);
chk.addControl(t_staff_kana);
chk.addControl(t_login_id);
chk.addControl(t_password);
chk.addControl(d_kengen);
chk.addControl(d_employment);
chk.addControl(d_seibetsu);
chk.addControl(d_status);
if (chk.check("W00000", chk.checkControlImportant))
{
return;
}
//桁数チェック
chk.clear();
chk.addControl(t_staff_code);
if (chk.check("W00001", chk.checkTextboxLength, 8))
{
return;
}
chk.clear();
chk.addControl(t_login_id);
chk.addControl(t_staff);
if (chk.check("W00001", chk.checkTextboxLength, 20))
{
return;
}
chk.clear();
chk.addControl(t_staff_kana);
if (chk.check("W00001", chk.checkTextboxLength, 40))
{
return;
}
chk.clear();
chk.addControl(t_password);
if (chk.check("W00001", chk.checkTextboxLength, 256))
{
return;
}
//データベース更新処理
//データの取得
StaffListStore();
if (update_flag == false || newData_flag == true)
{
// 接続インスタンスを作成。
var dbc = new DB.DBConnect();
// 登録する新規データの入れ物を作成(スタッフマスターに対して実行する)。
DB.m_staff data = dbc.m_staff.Create();
//データの投入
data.store_code = staff_store_code;
data.staff_code = staff_code;
data.staff_name = staff_name;
data.staff_name_kana = staff_name_kana;
data.login_id = staff_login_id;
data.password = staff_Hash_password;
data.permission_class = staff_kengen;
data.work_class = staff_employment;
data.status = staff_status;
data.sex = staff_seibetu;
data.registration_date = staff_registration_date;
data.registration_staff = staff_registration_staff;
data.update_date = staff_update_date;
data.update_staff = staff_update_staff;
data.delete_flag = staff_delete_flag;
// レコードををテーブルに登録。
dbc.m_staff.Add(data);
try
{
dbc.SaveChanges();
}
catch (Exception)
{
Utile.Message.showMessageOK("E14000");
return;
}
finally
{
dbc.npg.Close();
}
}
else if (update_flag == true || newData_flag == false)
{
// 接続インスタンスを作成。
var dbc2 = new DB.DBConnect();
//更新用スタッフコードの取得
index = currentPage - 1;
updateStaffCode = StaffDBList[index].staff_code;
//更新前データの取り込み
var filterData = StaffDBList[index];
storeIndex = this.d_tenpo.SelectedIndex;
//更新前データの取り込み
m_StaffStaff_store_code = filterData.staff_store_code;
m_StaffStaff_code = filterData.staff_code;
m_StaffStaff_name = filterData.staff_name;
m_StaffStaff_name_kana = filterData.staff_name_kana;
m_StaffStaff_login_id = filterData.login_id;
m_StaffStaff_password = filterData.password;
m_StaffStaff_kengen = filterData.kengen;
m_StaffStaff_status = filterData.status;
m_StaffStaff_employment = filterData.employment;
m_StaffStaff_seibetu = filterData.seibetu;
m_StaffStaff_registration_date = filterData.registration_date;
m_StaffStaff_registration_staff = filterData.registration_staff;
m_StaffStaff_update_date = filterData.update_date;
m_StaffStaff_update_staff = filterData.update_staff;
m_StaffStaff_delete_flag = filterData.delete_flag;
//更新用スタッフコードの取得
index = currentPage - 1;
updateStaffCode = StaffDBList[index].staff_code;
storeIndex = this.d_tenpo.SelectedIndex;
updateStoreCode = storeCodeList[storeIndex].store_code;
// 接続インスタンスを作成。
var dbc = new DB.DBConnect();
dbc.npg.Open();
StringBuilder sb = new StringBuilder();
using (var transaction = dbc.npg.BeginTransaction())
{
sb.Append("update m_staff set ");
if (staff_store_code != m_StaffStaff_store_code)
{
sb.Append("store_code = '" + staff_store_code + "', ");
}
if (staff_code != m_StaffStaff_code)
{
sb.Append("staff_code = '" + staff_code + "', ");
}
if (staff_name != m_StaffStaff_name)
{
sb.Append("staff_name = '" + staff_name + "', ");
}
if (staff_name_kana != m_StaffStaff_name_kana)
{
sb.Append("staff_name_kana = '" + staff_name_kana + "', ");
}
if (staff_login_id != m_StaffStaff_login_id)
{
sb.Append("login_id = '" + staff_login_id + "', ");
}
if (staff_Hash_password != m_StaffStaff_password)
{
sb.Append("password = '******', ");
}
if (staff_kengen != m_StaffStaff_kengen)
{
sb.Append("permission_class = '" + staff_kengen + "', ");
}
if (staff_employment != m_StaffStaff_employment)
{
sb.Append("work_class = '" + staff_employment + "', ");
}
if (staff_status != m_StaffStaff_status)
{
sb.Append("status = '" + staff_status + "', ");
}
if (staff_seibetu != m_StaffStaff_seibetu)
{
sb.Append("sex = '" + staff_seibetu + "', ");
}
sb.Append("update_date = '" + nowToday + "', ");
sb.Append("update_staff = '" + MainForm.session_m_staff.staff_name + "', ");
sb.Append("delete_flag = '" + staff_delete_flag + "' ");
sb.Append("where store_code = '" + staff_store_code + "' AND ");
sb.Append("staff_code = '" + staff_code + "';");
string strsql = sb.ToString();
var command = new NpgsqlCommand(strsql, dbc.npg);
try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (NpgsqlException)
{
transaction.Rollback();
Utile.Message.showMessageOK("E14001");
return;
}
finally
{
dbc.npg.Close();
}
}
}
//登録後画面初期化し次の登録を始める
MainForm.Staff_master.PageRefresh();
//MainForm.backPage(this);
if (data_flag == false && update_flag == false)
{
currentPage++;
totalPage++;
}
else if (data_flag == true && update_flag == false)
{
currentPage = StaffDBList.Count + 1;
totalPage = StaffDBList.Count + 1;
}
else if (data_flag == true && update_flag == true)
{
currentPage = StaffDBList.Count;
totalPage = StaffDBList.Count;
}
//新規データフラグを立てる
newData_flag = true;
//データ取得
ListArrayStaff();
init_flag = false;
update_flag = false;
Set_initialDisplay();
t_current_page.Text = (string)currentPage.ToString("0");
t_total_page.Text = (string)totalPage.ToString("0");
pageParent.PageRefresh();
MainForm.backPage(this);
}
//初期表示の設定
private void Set_initialDisplay()
{
if (init_flag == false)
{
//storeCodeListの初期化
storeCodeList.Clear();
NpgsqlDataReader dataReader = null;
var dbc = new DB.DBConnect();
dbc.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append(@"select store_code, store_name ");
sb.Append(@"from m_store ");
sb.Append(@"where m_store.delete_flag = '0' order by m_store.store_code");
var command = new NpgsqlCommand(sb.ToString(), dbc.npg);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
//storeCodeListの生成
storeCodeList.Add(new storeCodeArray
{
store_code = dataReader["store_code"].ToString(),
store_name = dataReader["store_name"].ToString()
});
}
dbc.npg.Close();
//店舗名の生成
d_tenpo.Items.Clear();
for (int i = 0; i < storeCodeList.Count; i++)
{
d_tenpo.Items.Add(storeCodeList[i].store_name);
}
//初期化フラグを初期化済みにする
init_flag = true;
}
d_tenpo.SelectedIndex = 0;
t_staff_code.Text = null;
t_current_page.Text = (string)currentPage.ToString("0");
t_total_page.Text = (string)totalPage.ToString("0");
t_staff.Text = null;
t_staff_kana.Text = null;
t_login_id.Text = null;
t_password.Text = null;
d_kengen.DataSource = Enum.GetValues(typeof(Utile.Data.権限区分));
d_kengen.SelectedIndex = 0;
d_employment.DataSource = Enum.GetValues(typeof(Utile.Data.社員区分));
d_employment.SelectedIndex = 0;
d_status.DataSource = Enum.GetValues(typeof(Utile.Data.スタッフステータス));
d_status.SelectedIndex = 0;
d_seibetsu.DataSource = Enum.GetValues(typeof(Utile.Data.性別));
d_seibetsu.SelectedIndex = 0;
//変更フラグの初期化
changeValue = false;
//新規データフラグを初期化
if (data_flag == true)
{
newData_flag = false;
}
//エラー表示の初期化
chk.clear();
}
private void b_search_Click(object sender, EventArgs e)
{
// DataGridView初期化（データクリア）
dataGridView1.Rows.Clear();
//where句の編集
strSQLWHERE = "";
//予約年月日（自）
strSQLWHERE += t_yoyaku_date_from != null ? "fac.start_date >= " + "'" + t_yoyaku_date_from + "'" : "";
//予約年月日（至）
strSQLWHERE += t_yoyaku_date_to != null && strSQLWHERE.Length > 0 ? " and" : "";
strSQLWHERE += t_yoyaku_date_to != null ? " fac.start_date <= " + "'" + t_yoyaku_date_to + "'" : "";
//店舗名称
strSQLWHERE += d_tenpomei.Text != "" && strSQLWHERE.Length > 0 ? " and" : "";
strSQLWHERE += d_tenpomei.Text != "" ? " ms.store_name LIKE " + "'%" + d_tenpomei.Text + "%'" : "";
//予約開始時分
strSQLWHERE += d_yoyakukaishijihun.Text != "" && strSQLWHERE.Length > 0 ? " and" : "";
strSQLWHERE += d_yoyakukaishijihun.Text != "" ? " fac.start_time >=" + "'" + d_yoyakukaishijihun.Text + "'" : "";
//予約終了時分
strSQLWHERE += d_yoyakusyuuryoujihun.Text != "" && strSQLWHERE.Length > 0 ? " and" : "";
strSQLWHERE += d_yoyakusyuuryoujihun.Text != "" ? " fac.end_time <=" + "'" + d_yoyakusyuuryoujihun.Text + "'" : "";
//施設名
strSQLWHERE += d_shisetsumei.Text != "" && strSQLWHERE.Length > 0 ? " and" : "";
strSQLWHERE += d_shisetsumei.Text != "" ? " faci.facility_name LIKE " + "'%" + d_shisetsumei.Text + "%'" : "";
//お客様名
strSQLWHERE += t_okyakusamamei.Text != "" && strSQLWHERE.Length > 0 ? " and" : "";
strSQLWHERE += t_okyakusamamei.Text != "" ? " cu.surname || cu.name LIKE " + "'%" + t_okyakusamamei.Text + "%'" : "";
//性別
var intval = d_seibetsu.Text == "男" ? (int)Utile.Data.性別.男 : (int)Utile.Data.性別.女;
strSQLWHERE += d_seibetsu.Text != "" && strSQLWHERE.Length > 0 ? " and" : "";
strSQLWHERE += d_seibetsu.Text != "" ? " cu.sex =" + "'" + intval + "'" : "";
//撮影目的
strSQLWHERE += t_satsueimokuteki.Text != "" && strSQLWHERE.Length > 0 ? " and":"";
strSQLWHERE += t_satsueimokuteki.Text != "" ? " fac.shooting_purpose LIKE " + "'%" + t_satsueimokuteki.Text + "%'" : "";
//衣装コード
strSQLWHERE += t_isyoukodo.Text != "" && strSQLWHERE.Length > 0 ? " and" : "";
strSQLWHERE += t_isyoukodo.Text != "" ? " cos.costume_code = " + "'" + t_isyoukodo.Text + "'" : "";
//衣装予約年月日
strSQLWHERE += t_isyouyoyaku_date != null && strSQLWHERE.Length > 0 ? " and" : "";
strSQLWHERE += t_isyouyoyaku_date != null ? " cos.start_date = " + "'" + t_isyouyoyaku_date + "'" : "";
//摘要
strSQLWHERE += t_tekiyou.Text != "" && strSQLWHERE.Length > 0 ? " and" : "";
strSQLWHERE += t_tekiyou.Text != "" ? " fac.remarks LIKE " + "'%" + t_tekiyou.Text + "%'" : "";
strSQLWHERE = strSQLWHERE.Length > 0 ? "WHERE " + strSQLWHERE : "";
//結果格納クエリーの初期化
NpgsqlDataReader dataReader = null;
//検索結果の表示
using (var db = new DB.DBConnect())
{
db.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append(@"select
rec.receipt_date as receipt_date,
ms.store_name as store,
fac.start_date as start_date,
fac.start_time as start_time,
fac.end_time as end_time,
faci.facility_name as facility_name,
cu.surname || cu.name as name,
cu.surname_kana || cu.name_kana as namekana,
cu.sex as sex,
fac.shooting_purpose as shooting_purpose,
cos.costume_code as costume_code,
cos.start_date as costume_Start_date,
cos.start_time as costume_start_time,
cos.end_date as costume_end_date,
cos.end_time as costume_end_time,
fac.remarks as remarks ");
sb.Append(@"from
((((((
t_facility_reservation fac
INNER JOIN m_facility faci ON
fac.store_code = faci.store_code and fac.facility_code = faci.facility_code)
INNER JOIN m_store ms ON
fac.store_code = ms.store_code)
LEFT JOIN t_reservation res ON
res.facility_reservation_code = fac.facility_reservation_code)
LEFT JOIN t_reception rec ON
res.reception_code = rec.reception_code)
LEFT JOIN m_customer cu ON
rec.customer_code = cu.customer_code)
LEFT JOIN t_costume_reservation cos ON
res.costume_reservation_code = cos.costume_reservation_code)
");
sb.Append(@strSQLWHERE);
sb.Append(@"ORDER BY receipt_date");
var command = new NpgsqlCommand(sb.ToString(), db.npg);
dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
//性別をEUMから取得
int intVal = int.Parse(dataReader["sex"].ToString());
var sex = (Utile.Data.性別)Enum.ToObject(typeof(Utile.Data.性別), intVal);
//日付の編集
DateTime receipt_date = DateTime.Parse(dataReader["receipt_date"].ToString());
DateTime startdate = DateTime.Parse(dataReader["start_date"].ToString());
DateTime starttime = DateTime.Parse(dataReader["start_time"].ToString());
DateTime endtime = DateTime.Parse(dataReader["end_time"].ToString());
//NULLの場合があるので、別途細工
DateTime.TryParse(dataReader["costume_start_date"].ToString(), out DateTime _costumestartdate);
DateTime.TryParse(dataReader["costume_start_time"].ToString(), out DateTime _costumestarttime);
DateTime.TryParse(dataReader["costume_end_date"].ToString(), out DateTime _costumeenddate);
DateTime.TryParse(dataReader["costume_end_time"].ToString(), out DateTime _costumeendtime);
var strcostume_date = dataReader["costume_start_date"].ToString() != "" ?
_costumestartdate.ToShortDateString() + " " +
_costumeendtime.ToShortTimeString() +
"～" +
_costumeenddate.ToShortDateString() + " " +
_costumeendtime.ToShortTimeString() : "";
dataGridView1.Rows.Add(
receipt_date.ToShortDateString(),
dataReader["store"].ToString(),
startdate.ToShortDateString(),
starttime.ToShortTimeString(),
endtime.ToShortTimeString(),
dataReader["facility_name"].ToString(),
dataReader["name"].ToString(),
dataReader["namekana"].ToString(),
sex,
dataReader["shooting_purpose"].ToString(),
dataReader["costume_code"].ToString(),
strcostume_date,
dataReader["remarks"].ToString());
}
}
else
{
MessageBox.Show("検索結果が0件でした。", "お知らせ", MessageBoxButtons.OK);
}
}
}
private void btnRegister_Click(object sender, EventArgs e)
{
chk.clear();
//必須入力チェック（スタッフが選択時、他の項目がブランクの場合はエラー）
// 必須チェック
chk.addControl(co_store);
if (chk.check("W00000", chk.checkControlImportant))
{
return;
}
for (var i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells["Staff"].Value == null ||
dataGridView1.Rows[i].Cells["Staff"].Value.ToString() == "")
{
break;
}
if (dataGridView1.Rows[i].Cells["StartTime"].Value == null ||
dataGridView1.Rows[i].Cells["EndTime"].Value == null ||
dataGridView1.Rows[i].Cells["WorkClass"].Value == null)
{
dataGridView1.Rows[i].Selected = true;
Utile.Message.showMessageOK("E11005");
return;
}
}
//フォーマットチェック（日付: HH: MM） HH：0～23MM：0~59
for (var i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells["Staff"].Value == null ||
dataGridView1.Rows[i].Cells["Staff"].Value.ToString() == "")
{
break;
}
if (!System.Text.RegularExpressions.Regex.IsMatch(
dataGridView1.Rows[i].Cells["StartTime"].Value.ToString(),
@"\A[0-2][0-9]:[0-5][0-9]\z"))
{
dataGridView1.Rows[i].Selected = true;
var msg = Utile.Message.message["W00003"].Replace("@フォーマット",
dataGridView1.Rows[i].Cells["StartTime"].Value.ToString());
Utile.Message.showMessageOK("W00003", msg);
return;
}
if (!System.Text.RegularExpressions.Regex.IsMatch(
dataGridView1.Rows[i].Cells["EndTime"].Value.ToString(),
@"\A[0-2][0-9]:[0-5][0-9]\z"))
{
dataGridView1.Rows[i].Selected = true;
var msg = Utile.Message.message["W00003"].Replace("@フォーマット",
dataGridView1.Rows[i].Cells["EndTime"].Value.ToString());
Utile.Message.showMessageOK("W00003", msg);
return;
}
}
//開始時間 < 終了時間をチェック
for (var i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells["Staff"].Value == null ||
dataGridView1.Rows[i].Cells["Staff"].Value.ToString() == "")
{
break;
}
if (dataGridView1.Rows[i].Cells["WorkClass"].Value.ToString() == "2")
{
break;
}
var StartTime = int.Parse(dataGridView1.Rows[i].Cells["StartTime"].Value.ToString().Substring(0, 2) +
dataGridView1.Rows[i].Cells["StartTime"].Value.ToString().Substring(3, 2));
var EndTime = int.Parse(dataGridView1.Rows[i].Cells["EndTime"].Value.ToString().Substring(0, 2) +
dataGridView1.Rows[i].Cells["EndTime"].Value.ToString().Substring(3, 2));
if (dataGridView1.Rows[i].Cells[1].Value == null)
{
break;
}
if (StartTime > 2359)
{
dataGridView1.Rows[i].Selected = true;
Utile.Message.showMessageOK("E11003");
return;
}
if (EndTime > 2359)
{
dataGridView1.Rows[i].Selected = true;
Utile.Message.showMessageOK("E11003");
return;
}
if (StartTime >= EndTime)
{
dataGridView1.Rows[i].Selected = true;
Utile.Message.showMessageOK("E11002");
return;
}
}
//DB処理
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
using (var transaction = dbc.npg.BeginTransaction())
{
try
{
//削除処理
var staff_shift = dbc.m_staff_shift;
//var delete_data = dbc.m_staff_shift.Where(x => x.work_day == l_calenderdata && x.store_code==l_store).Select(x=>x.work_day);
var delete_data = from ss in dbc.m_staff_shift
where ss.work_day == l_calenderdata & ss.store_code == l_store
select ss;
foreach (var data in delete_data)
{
dbc.m_staff_shift.Remove(data);
}
dbc.SaveChanges();
//登録処理
for (var i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells["Staff"].Value == null ||
dataGridView1.Rows[i].Cells["Staff"].Value.ToString() == "")
{
break;
}
DB.m_staff_shift staff_Shift = new DB.m_staff_shift();
var l_staff_code = dataGridView1.Rows[i].Cells["StaffCode"].Value.ToString();
var register_data = staff_shift.FirstOrDefault(x => x.work_day == l_calenderdata &&
x.store_code == l_store &&
x.staff_code == l_staff_code);
if (register_data == null)
{
staff_Shift.work_day = l_calenderdata;
staff_Shift.store_code = l_store;
staff_Shift.staff_code = l_staff_code;
staff_Shift.start_time = TimeSpan.Parse(dataGridView1.Rows[i].Cells["StartTime"].Value.ToString());
staff_Shift.end_time = TimeSpan.Parse(dataGridView1.Rows[i].Cells["EndTime"].Value.ToString());
staff_Shift.registration_date = DateTime.Now.Date;
staff_Shift.registration_staff = MainForm.session_m_staff.staff_name;
staff_Shift.update_date = DateTime.Now.Date;
staff_Shift.work_class = dataGridView1.Rows[i].Cells["WorkClass"].Value.ToString();
staff_Shift.update_staff = MainForm.session_m_staff.staff_name;
staff_Shift.delete_flag = "0";
dbc.m_staff_shift.Add(staff_Shift);
dbc.SaveChanges();
}
else
{
Utile.Message.showMessageOK("E11004");
transaction.Rollback();
return;
}
}
transaction.Commit();
}
catch (SqlCeException)
{
Utile.Message.showMessageOK("E11006");
transaction.Rollback();
}
}
}
//ひとつ前の画面に戻る
SelfViewing = false;
pageParent.PageRefresh();
MainForm.backPage(this);
}
private void b_regist_Click(object sender, EventArgs e)
{
//必須入力チェック
chkExist.addControl(d_tenpo);
chkExist.addControl(t_facility);
chkExist.addControl(t_facility_code);
if (chkExist.check("W00000", chkExist.checkControlImportant))
{
return;
}
//8桁数チェック
chk8Digit.addControl(t_facility_code);
if (chk8Digit.check("W00001", chk8Digit.checkTextboxLength, 8))
{
return;
}
//20桁チェック
chk20Digit.addControl(t_facility);
if (chk20Digit.check("W00001", chk20Digit.checkTextboxLength, 20))
{
return;
}
//キーの設定
string store_code = storeCodeList.Find(x => x.store_name == d_tenpo.Text).store_code;
string facility_code = t_facility_code.Text;
//登録処理
bool toroku = false;
for (var i = 0; i < facilities.Count; i++)
{
if (facilities[i].store_code == store_code &&
facilities[i].facility_code == facility_code)
{
toroku = true;
break;
}
}
if (!toroku)
{
//登録処理
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
using (var transaction = dbc.npg.BeginTransaction())
{
StringBuilder sb = new StringBuilder();
sb.Append(@"insert into m_facility values");
sb.Append(@"(" +
"'" + store_code + "'," +
"'" + facility_code + "'," +
"'" + t_facility.Text + "'," +
"'" + DateTime.Now.Date + "'," +
"'" + MainForm.session_m_staff.staff_name + "')"
);
var command = new NpgsqlCommand(sb.ToString(), dbc.npg);
try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (NpgsqlException)
{
transaction.Rollback();
throw;
}
}
}
}
else
{
//更新処理
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
using (var transaction = dbc.npg.BeginTransaction())
{
StringBuilder sb = new StringBuilder();
sb.Append(@"update m_facility set ");
sb.Append(@"" +
"store_code = '" + store_code + "'," +
"facility_code = '" + facility_code + "'," +
"facility_name = '" + t_facility.Text + "'," +
"update_date = '" + DateTime.Now.Date + "'," +
"update_staff = '" + MainForm.session_m_staff.staff_name + "'"
);
sb.Append(@" where store_code = " + "'" + store_code + "' and " +
"facility_code =" + "'" + facility_code + "'");
var command = new NpgsqlCommand(sb.ToString(), dbc.npg);
try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (NpgsqlException)
{
transaction.Rollback();
throw;
}
}
}
}
//登録後、ひとつ前の画面に戻る
MainForm.Facility_master.PageRefresh();
MainForm.backPage(this);
}
private void b_regist_Click(object sender, EventArgs e)
{
//必須入力チェック
//存在チェック項目の追加
chkExist.addControl(t_reception_code);
chkExist.addControl(d_receipt_date);
chkExist.addControl(t_reception_time);
chkExist.addControl(d_receipt_staff);
chkExist.addControl(d_receipt_store);
chkExist.addControl(d_status);
chkExist.addControl(t_photographers);
chkExist.addControl(d_coming_store_category);
chkExist.addControl(d_motivation);
chkExist.addControl(d_customer_code);
if (chkExist.check("W00000", chkExist.checkControlImportant))
{
return;
}
//受付コードチェック
chkreceptcode.addControl(t_reception_code);
if (chkreceptcode.check("W00001", chkreceptcode.checkTextboxLength, 8))
{
return;
}
//受付時間チェック
chkrecepttime.addControl(t_reception_time);
if (chkrecepttime.check("W00001", chkrecepttime.checkTextboxLength, 5))
{
return;
}
if (chkrecepttime.check("W00003", chkrecepttime.checkTextboxFormat, @"^[0-2][0-9]:[0-5][0-9]$", @"HH:MM"))
{
return;
}
//撮影人数チェック
chkphotographers.addControl(t_photographers);
if (chkphotographers.check("W00001", chkphotographers.checkTextboxLength, 4))
{
return;
}
if (chkphotographers.check("W00003", chkphotographers.checkTextboxFormat, @"\d+", @"0～9999"))
{
return;
}
//メモチェック
//メモチェック
chkmemo.addControl(t_memo);
if (chkmemo.check("W00001", chkmemo.checkTextboxLength, 255))
{
return;
}
//クレームチェック
//クレームチェック
chkclaim.addControl(d_claim);
if (chkclaim.check("W00001", chkclaim.checkTextboxLength, 255))
{
return;
}
//登録処理
//キーの設定
string recept_code = t_reception_code.Text;
//登録処理
DB.t_reception reception = new DB.t_reception();
using (var dbc = new DB.DBConnect())
{
dbc.npg.Open();
using (var transaction = dbc.npg.BeginTransaction())
{
//登録処理
var data = dbc.t_reception.FirstOrDefault(x => x.reception_code == recept_code);
if (data == null)
{
reception.reception_code = recept_code;
reception.receipt_date = d_receipt_date.Value;
reception.receipt_time = TimeSpan.Parse(t_reception_time.Text);
reception.status = (d_status.SelectedIndex + 1).ToString();
reception.photographers = int.Parse(t_photographers.Text);
reception.coming_store_category = (d_coming_store_category.SelectedIndex + 1).ToString();
reception.customer_code = d_customer_code.Text != ""? d_customer_code.Text:"00000000";
reception.store = storeCodeList.Find(x => x.store_name == d_receipt_store.Text).store_name;
reception.staff = staffCodeList.Find(x => x.staff_name == d_receipt_staff.Text).staff_name;
reception.memo = t_memo.Text;
reception.claim = d_claim.Text;
reception.motivation = (d_motivation.SelectedIndex + 1).ToString();
reception.noprint = "0";
reception.registration_date = DateTime.Now.Date;
reception.registration_staff = MainForm.session_m_staff.staff_name;
dbc.t_reception.Add(reception);
}
else
{
MessageBox.Show("データがすでに存在してます。");
return;
}
try
{
dbc.SaveChanges();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
}
if (d_customer_code.Text != "")
{
//セッション情報の追加
MainForm.session_t_reception = reception;
//ヘッダメニュー更新
MainForm.Header_Menu.LabelReWrite();
}
//一つ前に戻る
pageParent.PageRefresh();
MainForm.backPage(this);
}
//印刷ボタン
private void b_print_Click(object sender, EventArgs e)
{
//顧客情報登録
Dictionary <string, string> dataitem = new Dictionary <string, string>();
dataitem.Add("ReceptionCode", t_uketsuke_code.Text.ToString());
dataitem.Add("ReceptionDate", t_uketsuke_date.Text.ToString());
dataitem.Add("CustomerCode", t_kokyaku_code.Text.ToString());
var customer = DB.m_customer.getSingle(t_kokyaku_code.Text.ToString());
dataitem.Add("CustomerNamekana", customer.surname_kana + customer.name_kana);
dataitem.Add("CustomerName", t_kokyakumei.Text.ToString());
dataitem.Add("CustomerBirthday", t_seinengappi.Text.ToString());
dataitem.Add("CustomerAge", t_nenrei.Text.ToString());
dataitem.Add("CustomerSex", t_seibetsu.Text.ToString());
dataitem.Add("CustomerZipcode", t_postalcode.Text.ToString());
dataitem.Add("CustomerAddress1", t_sichouson.Text.ToString());
dataitem.Add("CustomerAddress2", t_banchi.Text.ToString() + t_apart.Text.ToString());
dataitem.Add("CustomerPhonenumber1", t_denwabango.Text.ToString());
dataitem.Add("CustomerPhonenumber2", customer.phone_number2);
dataitem.Add("CustomerPhonenumber3", customer.phone_number3);
dataitem.Add("CustomerFaxnumber1", customer.fax_number);
dataitem.Add("CustomerMailaddress", t_mail.Text.ToString());
string table = "Reception_card";
Utile.RepoerDB rdb = new Utile.RepoerDB(table);
rdb.deleteAll();
rdb.insert(dataitem);
//名簿-家族情報登録
List <DB.m_family> families = new List <DB.m_family>();
families = DB.m_family.getOnlyCode(t_kokyaku_code.Text.ToString());
table = "Reception_card_NameList";
rdb = new Utile.RepoerDB(table);
rdb.deleteAll();
foreach (DB.m_family family in families)
{
Dictionary <string, string> dataitem1 = new Dictionary <string, string>();
dataitem1.Add("NameListNamekana", family.surname_kana + family.name_kana);
dataitem1.Add("NameListName", family.surname + family.name);
dataitem1.Add("NameListBirthday", family.birthday.Value.ToShortDateString());
int age = getAge(family.birthday.Value);
dataitem1.Add("NameListAge", age.ToString());
dataitem1.Add("NameListSex", Enum.ToObject(typeof(Utile.Data.性別), int.Parse(family.sex)).ToString());
dataitem1.Add("NameListRelationship", family.relationship);
dataitem1.Add("NameListRemarks", family.remarks);
rdb.insert(dataitem1);
}
//名簿-追加情報登録
table = "Reception_card_NameListIncluding";
rdb = new Utile.RepoerDB(table);
Dictionary <string, string> dataitem2 = new Dictionary <string, string>();
using (var db = new DB.DBConnect())
{ //オープン処理がないと怒られる。
db.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append(@"select distinct
fr.shooting_purpose as shooting_purpose
");
sb.Append(@"from
t_reception r
INNER JOIN t_reservation re ON
r.reception_code = re.reception_code
INNER JOIN t_facility_reservation fr ON
re.facility_reservation_code = fr.facility_reservation_code
INNER JOIN m_facility f ON
fr.facility_code = f.facility_code
");
sb.Append(@" where r.reception_code ='@reception_code'".Replace("@reception_code", reception_code));
sb.Append(@"order by fr.shooting_purpose");
var command = new NpgsqlCommand(sb.ToString(), db.npg);
var dataReader = command.ExecuteReader();
dataReader.Read();
if (dataReader.HasRows)
{
dataitem2.Add("NameListUseClassification", dataReader["shooting_purpose"].ToString());
}
}
string d = DB.t_shooting_data.getShootingDate(t_kokyaku_code.Text.ToString());
DateTime.TryParse(d, out DateTime Day);
String secondDay = d != "" ? Day.ToShortDateString():"";
dataitem2.Add("NameListShootingdate", secondDay);
dataitem2.Add("NameListRemarks", t_taiou_naiyou.Text.ToString());
rdb.deleteAll();
rdb.insert(dataitem2);
//施設予約
table = "Reception_card_Reservation";
rdb = new Utile.RepoerDB(table);
rdb.deleteAll();
for (var i = 0; i < dataGridView1.RowCount; i++)
{
Dictionary <string, string> dataitem3 = new Dictionary <string, string>();
dataitem3.Add("ReservationFacility", dataGridView1.Rows[i].Cells[0].Value.ToString());
dataitem3.Add("ReservationDate", dataGridView1.Rows[i].Cells[1].Value.ToString());
dataitem3.Add("ReservationRemarks", dataGridView1.Rows[i].Cells[2].Value.ToString());
rdb.insert(dataitem3);
}
//衣装予約登録
table = "Reception_card_Costume";
rdb = new Utile.RepoerDB(table);
rdb.deleteAll();
for (var i = 0; i < dataGridView2.RowCount; i++)
{
Dictionary <string, string> dataitem4 = new Dictionary <string, string>();
var store = DB.m_store.getSingleName(dataGridView2.Rows[i].Cells[1].Value.ToString());
var costume = DB.m_costume.getSingle(store.store_code, dataGridView2.Rows[i].Cells[2].Value.ToString());
var photo_root = System.Configuration.ConfigurationManager.AppSettings["photo_root"];
var Costume_Image = System.Configuration.ConfigurationManager.AppSettings["Costume_Image"];
var Costume_Image_Dir = System.IO.Path.Combine(photo_root, Costume_Image);
string image1 = costume.image1;
var path = System.IO.Path.Combine(Costume_Image_Dir, store.store_name, dataGridView2.Rows[i].Cells[2].Value.ToString(), image1);
dataitem4.Add("CostumeThumbnail", System.IO.Path.GetFullPath(path));
dataitem4.Add("CostumeFacility", dataGridView2.Rows[i].Cells[0].Value.ToString());
dataitem4.Add("CostumeCode", dataGridView2.Rows[i].Cells[2].Value.ToString());
dataitem4.Add("CostumeRemarks", dataGridView2.Rows[i].Cells[3].Value.ToString());
rdb.insert(dataitem4);
}
PrintForm p = new PrintForm();
p.PrintReport.Load(@"./Asset/Format/Reception_card.flxr", "Reception_card レポート");
p.c1FlexViewer.DocumentSource = p.PrintReport;
p.Show();
}
//登録ボタン
private void b_regist_Click(object sender, EventArgs e)
{
//必須入力チェック
chk.clear();
chk.addControl(t_tenpo_code);
chk.addControl(t_company);
chk.addControl(t_tenpo);
chk.addControl(t_postal_code);
chk.addControl(t_kensikuchouson);
chk.addControl(t_banchi);
chk.addControl(t_telephon_no);
chk.addControl(t_eigyou_start);
chk.addControl(t_eigyou_end);
chk.addControl(t_regular_holiday);
chk.addControl(d_status);
chk.addControl(d_tenpo_kubun);
if (chk.check("W00000", chk.checkControlImportant))
{
return;
}
//桁数
//5
chk.clear();
chk.addControl(t_eigyou_start);
chk.addControl(t_eigyou_end);
if (chk.check("W00001", chk.checkTextboxLength, 5))
{
return;
}
//8
chk.clear();
chk.addControl(t_tenpo_code);
if (chk.check("W00001", chk.checkTextboxLength, 8))
{
return;
}
//10
chk.clear();
chk.addControl(t_regular_holiday);
if (chk.check("W00001", chk.checkTextboxLength, 10))
{
return;
}
//13
chk.clear();
chk.addControl(t_telephon_no);
if (chk.check("W00001", chk.checkTextboxLength, 13))
{
return;
}
//60
chk.clear();
chk.addControl(t_company);
chk.addControl(t_tenpo);
if (chk.check("W00001", chk.checkTextboxLength, 60))
{
return;
}
//120
chk.clear();
chk.addControl(t_kensikuchouson);
chk.addControl(t_banchi);
chk.addControl(t_apart);
if (chk.check("W00001", chk.checkTextboxLength, 120))
{
return;
}
//フォーマットチェック
chk.clear();
chk.addControl(t_eigyou_start);
chk.addControl(t_eigyou_end);
if (chk.check("W00003", chk.checkTextboxFormat, @"^([0-9]|[0-1][0-9]|[2][0-3]):[0-5][0-9]$", "00:00"))
{
return;
}
chk.clear();
chk.addControl(t_telephon_no);
if (chk.check("W00003", chk.checkTextboxFormat, @"\A0\d{1,4}-\d{1,4}-\d{4}\z", "000-0000-0000"))
{
return;
}
chk.clear();
chk.addControl(t_postal_code);
if (chk.check("W00003", chk.checkTextboxFormat, @"\A\d\d\d-\d\d\d\d\z", "000-0000"))
{
return;
}
if (t_tenpo_code.Text == "")
{
Utile.Message.showMessageOK("E13001");
return;
}
//内容保存
//ListStore();
StoreCode = this.t_tenpo_code.Text;
StoreCompany = this.t_company.Text;
StoreName = this.t_tenpo.Text;
StorePostalCode = this.t_postal_code.Text;
StorePrefCityTownDistrictVillage = this.t_kensikuchouson.Text;
StoreAddress = this.t_banchi.Text;
StoreApart = this.t_apart.Text;
StorePhoneNumber = this.t_telephon_no.Text;
StoreStatus = this.d_status.SelectedIndex.ToString();
StoreStartTime = TimeSpan.Parse(this.t_eigyou_start.Text);
StoreEndTime = TimeSpan.Parse(this.t_eigyou_end.Text);
StoreRegularHoliday = this.t_regular_holiday.Text;
StoreClassification = this.d_tenpo_kubun.SelectedIndex.ToString();
using (var dbc = new DB.DBConnect())
{
var data = DB.m_store.getSingle(StoreCode);
if (data == null)
{
data = new DB.m_store();
}
data.store_code = StoreCode;
data.company_name = StoreCompany;
data.store_name = StoreName;
data.postal_code = StorePostalCode;
data.address1 = StorePrefCityTownDistrictVillage;
data.address2 = StoreAddress;
data.address3 = StoreApart;
data.phone_number = StorePhoneNumber;
data.status = StoreStatus;
data.start_time = StoreStartTime;
data.end_time = StoreEndTime;
data.regular_holiday = StoreRegularHoliday;
data.store_category = StoreClassification;
data.Command();
}
var rootPath = System.Configuration.ConfigurationManager.AppSettings["photo_root"].ToString();
var cosDir = System.Configuration.ConfigurationManager.AppSettings["Costume_Image"].ToString();
string Cost_Path = System.IO.Path.Combine(rootPath, cosDir);
string store_Path = System.IO.Path.Combine(Cost_Path, StoreName);
if (!System.IO.Directory.Exists(store_Path))
{
;
}
{
Directory.CreateDirectory(store_Path);
}
/*
* max_index = StoreList.Count;
*
* // 接続インスタンスを作成。
* var dbc = new DB.DBConnect();
*
* //insert文の生成
* for (int i = 0; i < max_index; i++) {
* // 登録する新規データの入れ物を作成(店舗マスターに対して実行する)。
* DB.m_store data = dbc.m_store.Create();
* //データの投入
* data.store_code = StoreList[i].store_code;
* data.company_name = StoreList[i].store_company;
* data.store_name = StoreList[i].store_name;
* data.postal_code = StoreList[i].store_postal_code;
* data.address1 = StoreList[i].store_pref_city_town_district_village;
* data.address2 = StoreList[i].store_address;
* data.address3 = StoreList[i].store_apart;
* data.phone_number = StoreList[i].store_phone_number;
* data.status = StoreList[i].store_status;
* data.start_time = StoreList[i].store_start_time;
* data.end_time = StoreList[i].store_end_time;
* data.regular_holiday = StoreList[i].store_regular_hoiday;
* data.store_category = StoreList[i].store_classification;
* data.registration_date = nowDay;
* data.registration_staff = MainForm.session_m_staff.staff_name;
* data.update_date = nowDay;
* data.update_staff = MainForm.session_m_staff.staff_name;
* data.delete_flag = "0";
*
*
* // レコードををテーブルに登録。
* dbc.m_store.Add(data);
*
* //INSERT文が問題ない場合は実行する
* try
* {
* dbc.SaveChanges();
* }
* //エラーの場合はUPDATE文を生成し実行する
* catch (Exception)
* {
* //データ確認用の店舗コードを取得する(現時点では店舗コード入力欄に店舗コードが入っているものを検索している)
* //upStoreCode = this.t_tenpo_code.Text;
*
* // 接続インスタンスを作成。
* dbc = new DB.DBConnect();
*
*
* // 絞り込んで取得。
* var filterData = DB.m_store.getSingle(StoreList[i].store_code);
*
* m_storeStoreCode = filterData.store_code;
* m_storeStoreCompany = filterData.company_name;
* m_storeStoreName = filterData.store_name;
* m_storeStorePostalCode = filterData.postal_code;
* m_storeStorePrefCityTownDistrictVillage = filterData.address1;
* m_storeStoreAddress = filterData.address2;
* m_storeStoreApart = filterData.address3;
* m_storeStorePhoneNumber = filterData.phone_number;
* m_storeStoreStatus = filterData.status;
* m_storeStoreStartTime = filterData.start_time;
* m_storeStoreEndTime = filterData.end_time;
* m_storeStoreRegularHoliday = filterData.regular_holiday;
* m_storeStoreClassification = filterData.store_category;
*
*
* // 更新する新規データの入れ物を作成。
* data = DB.m_store.getSingle(StoreList[i].store_code);
*
* //データを比較しながらアップデート文を生成する
* for (int j=0; j < max_index; j++)
* {
*
* //会社名が変わっていないかを確認。変わっていればデータベース更新
* if (m_storeStoreCompany != StoreList[j].store_company)
* {
* //変更がかかっている場合はスキーマに設定した変数に直書き
* //企業名を変更
* data.company_name = StoreList[j].store_company;
* }
*
* //店舗名が変わっていないかを確認。変わっていればデータベース更新
* if (m_storeStoreName != StoreList[j].store_name)
* {
* //変更がかかっている場合はスキーマに設定した変数に直書き
* //店舗名を変更
* data.store_name = StoreList[j].store_name;
* }
*
* //郵便番号が変わっていないかを確認。変わっていればデータベース更新
* if (m_storeStorePostalCode != StoreList[j].store_postal_code)
* {
* //変更がかかっている場合はスキーマに設定した変数に直書き
* //郵便番号を変更
* data.postal_code = StoreList[j].store_postal_code;
* }
*
* //県・市町区村が変わっていないかを確認。変わっていればデータベース更新
* if (m_storeStorePrefCityTownDistrictVillage != StoreList[j].store_pref_city_town_district_village)
* {
* //変更がかかっている場合はスキーマに設定した変数に直書き
* //県・市区町村を変更
* data.address1 = StoreList[j].store_pref_city_town_district_village;
* }
*
* //番地が変わっていないかを確認。変わっていればデータベース更新
* if (m_storeStoreAddress != StoreList[j].store_address)
* {
* //変更がかかっている場合はスキーマに設定した変数に直書き
* //番地を変更
* data.address2 = StoreList[j].store_address;
* }
*
* //アパート・マンションが変わっていないかを確認。変わっていればデータベース更新
* if (m_storeStoreApart != StoreList[j].store_apart)
* {
* //変更がかかっている場合はスキーマに設定した変数に直書き
* //アパート・マンションを変更
* data.address3 = StoreList[j].store_apart;
* }
*
* //電話番号が変わっていないかを確認。変わっていればデータベース更新
* if (m_storeStorePhoneNumber != StoreList[j].store_phone_number)
* {
* //変更がかかっている場合はスキーマに設定した変数に直書き
* //電話番号を変更
* data.phone_number = StoreList[j].store_phone_number;
* }
*
* //ステータスが変わっていないかを確認。変わっていればデータベース更新
* //開店中の場合
* if (m_storeStoreStatus == StoreList[j].store_status)
* {
* data.status = StoreList[j].store_status;
* }
*
* //店舗開始時間が変わっていないかを確認。変わっていればデータベース更新
* if (m_storeStoreStartTime != StoreList[j].store_start_time)
* {
* //変更がかかっている場合はスキーマに設定した変数に直書き
* //店舗開始時間を変更
* data.start_time = StoreList[j].store_start_time;
* }
*
* //店舗終了時間が変わっていないかを確認。変わっていればデータベース更新
* if (m_storeStoreEndTime != StoreList[j].store_end_time)
* {
* //変更がかかっている場合はスキーマに設定した変数に直書き
* //店舗終了時間を変更
* data.end_time = StoreList[j].store_end_time;
* }
*
* //店舗休業日が変わっていないかを確認。変わっていればデータベース更新
* if (m_storeStoreRegularHoliday != StoreList[j].store_regular_hoiday)
* {
* //変更がかかっている場合はスキーマに設定した変数に直書き
* //店舗休業日を変更
* data.regular_holiday = StoreList[j].store_regular_hoiday;
* }
*
* //店舗区分が変わっていないかを確認。変わっていればデータベース更新
* if (m_storeStoreClassification == StoreList[j].store_classification)
* {
* data.store_category = StoreList[i].store_classification;
* }
*
* data.update_date = nowDay;
* data.update_staff = MainForm.session_m_staff.staff_name;
* data.delete_flag = "0";
*
*
* // 更新内容を反映。
* dbc.SaveChanges();
* }
* }
* }
*/
pageParent.PageRefresh();
MainForm.backPage(this);
}
private void b_search_Click(object sender, EventArgs e)
{
//表の初期化
dataGridView1.Rows.Clear();
strSQLWHERE = null;
//WHERE句の設定
//店舗名
var storecode = storeCodeList.Find(x => x.store_name == d_tenpo.Text).store_code;
strSQLWHERE += d_tenpo.Text != "" ? "c.store_code = " + "'" + storecode + "'": "";
//分類
strSQLWHERE += t_bunrui.Text != "" && strSQLWHERE.Length > 0 ? " and ": "";
strSQLWHERE += t_bunrui.Text != "" ? " c.class like " + "'%" + t_bunrui.Text + "%'": "";
//コード
strSQLWHERE += t_code.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_code.Text != "" ? " c.costume_code like " + "'%" + t_code.Text + "%'" : "";
//見た目
strSQLWHERE += t_appearance.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_appearance.Text != "" ? " c.appearance like " + "'%" + t_appearance.Text + "%'" : "";
//ブランド
strSQLWHERE += t_brand.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_brand.Text != "" ? " c.brand_name like " + "'%" + t_brand.Text + "%'" : "";
//色
strSQLWHERE += t_color.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_color.Text != "" ? " c.color like " + "'%" + t_color.Text + "%'" : "";
//柄
strSQLWHERE += t_gara.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_gara.Text != "" ? " c.handle like " + "'%" + t_gara.Text + "%'" : "";
//サイズ
strSQLWHERE += t_size.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_size.Text != "" ? " c.size like " + "'%" + t_size.Text + "%'" : "";
//価格１
strSQLWHERE += t_price_from1.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_price_from1.Text != "" ? " c.price1 >= " + "'" + t_price_from1.Text + "'" : "";
strSQLWHERE += t_price_to1.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_price_to1.Text != "" ? " c.price1 <= " + "'" + t_price_to1.Text + "'" : "";
//価格２
strSQLWHERE += t_price_from2.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_price_from2.Text != "" ? " c.price2 >= " + "'" + t_price_from2.Text + "'" : "";
strSQLWHERE += t_price_to2.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_price_to2.Text != "" ? " c.price2 <= " + "'" + t_price_to2.Text + "'" : "";
//価格３
strSQLWHERE += t_price_from3.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_price_from3.Text != "" ? " c.price3 >= " + "'" + t_price_from3.Text + "'" : "";
strSQLWHERE += t_price_to3.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_price_to3.Text != "" ? " c.price3 <= " + "'" + t_price_to3.Text + "'" : "";
//摘要
strSQLWHERE += t_tekiyou.Text != "" && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += t_tekiyou.Text != "" ? " c.remarks like " + "'%" + t_tekiyou.Text + "%'" : "";
//チェックボックス
String strCHKBox = c_available.Checked == true ? " c.status = '0' " : "";
strCHKBox += c_maintenance.Checked == true && strCHKBox.Length > 0 ? " or " : "";
strCHKBox += c_maintenance.Checked == true ? " c.status = '1' " : "";
strCHKBox += c_lent.Checked == true && strCHKBox.Length > 0 ? " or " : "";
strCHKBox += c_lent.Checked == true ? " c.status = '2' " : "";
strCHKBox += c_scrap.Checked == true && strCHKBox.Length > 0 ? " or " : "";
strCHKBox += c_scrap.Checked == true ? " c.status = '3' " : "";
strCHKBox += c_borrow.Checked == true && strCHKBox.Length > 0 ? " or " : "";
strCHKBox += c_borrow.Checked == true ? " c.status = '4' " : "";
strSQLWHERE += strCHKBox.Length > 0 && strSQLWHERE.Length > 0 ? " and " : "";
strSQLWHERE += strCHKBox.Length > 0 ? "(" + strCHKBox + ")" : "";
strSQLWHERE = strSQLWHERE.Length > 0 ? " WHERE " + strSQLWHERE : "";
//結果格納クエリーの初期化
NpgsqlDataReader costumeReader = null;
using (var db = new DB.DBConnect())
{
db.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT
c.costume_code as costume_code,
c.image1 as image1,
c.appearance,
c.store_code as store_code,
c.rental_store as rental_store,
c.class as class,
c.brand_name as bland_name,
c.color as color,
c.handle as handle,
c.size as size,
c.price1 as price1,
c.price2 as price2,
c.price3 as price3,
c.costume_name as costume_name,
c.usability as usability,
c.status,
c.remarks as remarks,
cr.number as number,
cr.latest_date_from as latest_date_from,
cr.latest_date_to as latest_date_to
");
sb.Append(@"FROM
(SELECT * FROM m_costume
WHERE delete_flag = '0' ) AS c
LEFT JOIN (
SELECT distinct costume_code,count(costume_code) as number,
max(start_date) as latest_date_from,
max(end_date) as latest_date_to
FROM t_costume_reservation
group by costume_code) AS cr ON c.costume_code = cr.costume_code
");
sb.Append(@strSQLWHERE);
sb.Append(@"ORDER BY c.costume_code");
var command = new NpgsqlCommand(sb.ToString(), db.npg);
costumeReader = command.ExecuteReader();
if (costumeReader.HasRows)
{
while (costumeReader.Read())
{
//セル内の文字を改行する。
dataGridView1.RowTemplate.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
//行テンプレートの高さを設定する
dataGridView1.RowTemplate.Height = 80;
//種類編集
string Str_class = costumeReader["class"].ToString() +
Environment.NewLine +
costumeReader["bland_name"].ToString() +
Environment.NewLine +
costumeReader["color"].ToString() +
Environment.NewLine +
costumeReader["handle"].ToString() +
Environment.NewLine +
costumeReader["size"].ToString();
//価格編集
string price = costumeReader["price1"].ToString() +
Environment.NewLine +
costumeReader["price2"].ToString() +
Environment.NewLine +
costumeReader["price3"].ToString();
//店舗編集
string Strstore = storeCodeList.Find(x => x.store_code == costumeReader["store_code"].ToString()).store_name;
string Strlentstore = costumeReader["rental_store"].ToString() != "" ? Strstore + Environment.NewLine +
"->" + costumeReader["rental_store"].ToString() : Strstore;
//使用可否編集
int intVal = int.Parse(costumeReader["usability"].ToString());
string usability = Enum.ToObject(typeof(Utile.Data.衣装使用可否), intVal).ToString();
//直近のレンタル日
DateTime.TryParse(costumeReader["latest_date_from"].ToString(), out DateTime latest_date_from);
DateTime.TryParse(costumeReader["latest_date_to"].ToString(), out DateTime latest_date_to);
string Strlatest_date = costumeReader["latest_date_from"].ToString() != "" ? latest_date_from.ToShortDateString() : "";
Strlatest_date += costumeReader["latest_date_to"].ToString() != "" ? "～" + latest_date_to.ToShortDateString() : "";
//画像ファイルのサイズ変更
//画像ファイル存在チェック
string costume_code = costumeReader["costume_code"].ToString();
string image1 = costumeReader["image1"].ToString();
var path = System.IO.Path.Combine(Costume_Image_Dir, Strstore, costume_code, image1);
Bitmap resizeBmp = null;
if (System.IO.File.Exists(path))
{
//存在したら、サイズ変更
Bitmap img = new Bitmap(path);
int resizeHeight = dataGridView1.RowTemplate.Height;
int resizeWidth = (int)(img.Width * ((double)resizeHeight / (double)img.Height));
resizeBmp = new Bitmap(resizeWidth, resizeHeight);
Graphics g = Graphics.FromImage(resizeBmp);
//Bitmap newimg = new Bitmap(img);
g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;
g.DrawImage(img, 0, 0, resizeBmp.Width, resizeBmp.Height);
}
dataGridView1.Rows.Add(
resizeBmp,
costume_code,
Strlentstore,
Str_class,
price,
costumeReader["costume_name"].ToString(),
usability,
costumeReader["number"].ToString(),
Strlatest_date,
costumeReader["remarks"].ToString()
);
}
}
else
{
MessageBox.Show("検索結果が0件でした。", "お知らせ", MessageBoxButtons.OK);
}
}
}
public override void PageRefresh()
{
reception_code = MainForm.session_t_reception.reception_code;
//結果格納クエリーの初期化
NpgsqlDataReader dataReader = null;
using (var db = new DB.DBConnect())
{
//オープン処理がないと怒られる。
db.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append(@"select
*
");
sb.Append(@"from
t_reception r
LEFT JOIN m_customer c ON
r.customer_code = c.customer_code
");
sb.Append(@" where reception_code = '@reception_code'".Replace("@reception_code", reception_code));
sb.Append(@"order by reception_code");
var command = new NpgsqlCommand(sb.ToString(), db.npg);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
//受付コード
t_uketsuke_code.Text = dataReader["reception_code"].ToString();
//受付年月日
DateTime receipt_date;
DateTime.TryParse(dataReader["receipt_date"].ToString(), out receipt_date);
t_uketsuke_date.Text = receipt_date.ToLongDateString();
//受付時分
DateTime receipt_time;
DateTime.TryParse(dataReader["receipt_time"].ToString(), out receipt_time);
t_uketsuke_jihun.Text = receipt_time.ToShortTimeString();
//受付店舗
t_uketsuke_tenpo.Text = dataReader["store"].ToString();
//受付スタッフ
t_uketsuke_staff.Text = dataReader["staff"].ToString();
//受付ステータス
int intVal = int.Parse(dataReader["status"].ToString());
t_uketsuke_status.Text = Enum.ToObject(typeof(Utile.Data.受付ステータス), intVal - 1).ToString();
//撮影人数
t_uketsuke_ninzuu.Text = dataReader["photographers"].ToString();
//来店区分
int intVal1 = int.Parse(dataReader["coming_store_category"].ToString());
t_raiten_kubun.Text = Enum.ToObject(typeof(Utile.Data.来店区分), intVal1 - 1).ToString();
//メモ
t_taiou_naiyou.Text = dataReader["memo"].ToString();
//内規情報
t_naikinaiyou.Text = dataReader["claim"].ToString();
//顧客コード
t_kokyaku_code.Text = dataReader["customer_code"].ToString();
//お客様名
t_kokyakumei.Text = dataReader["surname"].ToString() + dataReader["name"].ToString();
//生年月日
DateTime birthday;
DateTime.TryParse(dataReader["birthday"].ToString(), out birthday);
t_seinengappi.Text = birthday.ToLongDateString();
//年齢
var age = getAge(birthday);
t_nenrei.Text = age.ToString() + "歳";
//性別
int index = int.Parse(dataReader["sex"].ToString());
t_seibetsu.Text = Enum.ToObject(typeof(Utile.Data.性別), index).ToString();
//郵便番号
t_postalcode.Text = dataReader["postal_code"].ToString();
//県・市区町村
t_sichouson.Text = dataReader["address1"].ToString();
//番地
t_banchi.Text = dataReader["address2"].ToString();
//アパート・マンション
t_apart.Text = dataReader["address3"].ToString();
//電話番号
t_denwabango.Text = dataReader["phone_number1"].ToString();
//メール
t_mail.Text = dataReader["mail_address"].ToString();
}
}
//施設予約検索
dataGridView1.Rows.Clear();
dataReader = null;
using (var db = new DB.DBConnect())
{
//オープン処理がないと怒られる。
db.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append(@"select
f.facility_name as name,
fr.start_date as start_date,
fr.start_time as start_time,
fr.remarks as remarks
");
sb.Append(@"from
t_reception r
INNER JOIN t_reservation re ON
r.reception_code = re.reception_code
INNER JOIN t_facility_reservation fr ON
re.facility_reservation_code = fr.facility_reservation_code
INNER JOIN m_facility f ON
fr.facility_code = f.facility_code
");
sb.Append(@" where r.reception_code = '@reception_code'".Replace("@reception_code", reception_code));
sb.Append(@"order by fr.start_date");
var command = new NpgsqlCommand(sb.ToString(), db.npg);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
DateTime startdate;
DateTime.TryParse(dataReader["start_date"].ToString(), out startdate);
DateTime start_time;
DateTime.TryParse(dataReader["start_time"].ToString(), out start_time);
dataGridView1.Rows.Add(
dataReader["name"].ToString(),
startdate.ToShortDateString() + " " + start_time.ToShortTimeString(),
dataReader["remarks"].ToString()
);
}
}
//衣装予約検索
dataGridView2.Rows.Clear();
dataReader = null;
using (var db = new DB.DBConnect())
{
//オープン処理がないと怒られる。
db.npg.Open();
StringBuilder sb = new StringBuilder();
sb.Append(@"select
cr.facility as name,
cr.store_code as store_code,
cr.costume_code as costume_code,
cr.memo as memo
");
sb.Append(@"from
t_reception r
INNER JOIN t_reservation re ON
r.reception_code = re.reception_code
INNER JOIN t_costume_reservation cr ON
re.costume_reservation_code = cr.costume_reservation_code
");
sb.Append(@" where r.reception_code = '@reception_code'".Replace("@reception_code", reception_code));
sb.Append(@"order by cr.costume_code ");
var command = new NpgsqlCommand(sb.ToString(), db.npg);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
var store_name = DB.m_store.getSingle(dataReader["store_code"].ToString());
dataGridView2.Rows.Add(
dataReader["name"].ToString(),
store_name.store_name,
dataReader["costume_code"].ToString(),
dataReader["memo"].ToString()
);
}
}
}
