private void button6_Click(object sender, EventArgs e)
{
KBDatabase kdb = new KBDatabase();
#if true
foreach (string line in this.txtSQL.Lines)
{
kdb.executeNonQuery(line, false);
}
MessageBox.Show("SQL実行完了");
#else
List<KBRec> lst;
lst = kdb.getKBDat(szOpt);
#endif
}
private void btnSearch_src_Click(object sender, EventArgs e)
{
KBDatabase kdb = new KBDatabase();
string szOpt = "";
DataGridView dgv = kbDatList_src.getControl();
int i = 0;
int len = 0;
dgv.Rows.Clear();
kbDatList_src.SuspendLayout();
// SQLデータの作成
// 日付
if ((0 != txtDatest_src.Text.Trim().Length) || (0 != txtDateed_src.Text.Trim().Length))
{
if ((0 != txtDatest_src.Text.Trim().Length) && (0 != txtDateed_src.Text.Trim().Length))
{
szOpt += String.Format(" AND ('{0}' <= buy_date AND buy_date <= '{1}')", txtDatest_src.Text.Trim(), txtDateed_src.Text.Trim());
}
else if ((0 != txtDatest_src.Text.Trim().Length) && (0 == txtDateed_src.Text.Trim().Length))
{
szOpt += String.Format(" AND ('{0}' <= buy_date)", txtDatest_src.Text.Trim());
}
else if ((0 == txtDatest_src.Text.Trim().Length) && (0 != txtDateed_src.Text.Trim().Length))
{
szOpt += String.Format(" AND (buy_date <= '{0}')", txtDateed_src.Text.Trim());
}
}
// 取扱日
if ((0 != txtPayst_src.Text.Trim().Length) || (0 != txtPayed_src.Text.Trim().Length))
{
if ((0 != txtPayst_src.Text.Trim().Length) && (0 != txtPayed_src.Text.Trim().Length))
{
szOpt += String.Format(" AND ('{0}' <= buy_date AND buy_date <= '{1}')", txtPayst_src.Text.Trim(), txtPayed_src.Text.Trim());
}
else if ((0 != txtPayst_src.Text.Trim().Length) && (0 == txtPayed_src.Text.Trim().Length))
{
szOpt += String.Format(" AND ('{0}' <= buy_date)", txtPayst_src.Text.Trim());
}
else if ((0 == txtPayst_src.Text.Trim().Length) && (0 != txtPayed_src.Text.Trim().Length))
{
szOpt += String.Format(" AND (buy_date <= '{0}')", txtPayed_src.Text.Trim());
}
}
len = txtWhere_src.Text.Trim().Length;
if (0 != len)
{
szOpt += String.Format(" AND (where = '{0}')", txtWhere_src.Text.Trim());
}
if (0 < cmbCateMain_src.SelectedIndex)
{
szOpt += String.Format(" AND (id_cate_main = {0})",
cmbCateMain_src.SelectedItem.ToString().Trim().Split(':')[0]);
}
if (0 < cmbCateSub_src.SelectedIndex)
{
szOpt += String.Format(" AND (id_cate_sub = {0})",
cmbCateSub_src.SelectedItem.ToString().Trim().Split(':')[0]);
}
if (0 < cmbAccount_src.SelectedIndex)
{
szOpt += String.Format(" AND (id_account = {0})",
cmbAccount_src.SelectedItem.ToString().Trim().Split(':')[0]);
}
len = txtName_src.Text.Trim().Length;
if (0 < len)
{
string str = "";
foreach (string s in txtName_src.Lines)
{
if (s[0] == '-')
{
str += String.Format(" OR (name not like '{0}')", s.Substring(1));
}
else
{
str += String.Format(" OR (name like '{0}')", s);
}
}
if (str.StartsWith(" OR"))
{
// 先頭が「 AND」から始まっていた場合は先頭の「 AND」を削除する
str = str.Substring(" OR".Length);
}
szOpt += String.Format(" AND ({0})", str);
}
if (szOpt.StartsWith(" AND"))
{
// 先頭が「 AND」から始まっていた場合は先頭の「 AND」を削除する
szOpt = szOpt.Substring(" AND".Length);
}
List<KBRec> lst = kdb.getKBDat(szOpt);
int income = 0, payment = 0;
for (i = 0; i < lst.Count; i++ )
{
dgv.Rows.Add(1);
dgv.Rows[i].Cells["col_buy_date"].Value = lst[i].buy_date;
if (0 != lst[i].ie_type)
{
income += int.Parse(lst[i].money.ToString());
dgv.Rows[i].Cells["col_income"].Value = lst[i].money.ToString();
dgv.Rows[i].Cells["col_payment"].Value = "";
}
else
{
payment += int.Parse(lst[i].money.ToString());
dgv.Rows[i].Cells["col_income"].Value = "";
dgv.Rows[i].Cells["col_payment"].Value = lst[i].money.ToString();
}
dgv.Rows[i].Cells["col_name"].Value = lst[i].name;
dgv.Rows[i].Cells["col_pay_date"].Value = lst[i].pay_date;
dgv.Rows[i].Cells["col_where"].Value = lst[i].where;
dgv.Rows[i].Cells["col_memo"].Value = lst[i].memo;
dgv.Rows[i].Cells["col_id"].Value = lst[i].id;
dgv.Rows[i].Cells["col_id_usr"].Value = lst[i].id_usr;
dgv.Rows[i].Cells["col_ie_type"].Value = lst[i].ie_type;
dgv.Rows[i].Cells["col_rid"].Value = lst[i].rid;
dgv.Rows[i].Cells["col_cate_main"].Value = kbDatList_src.getCateMainStr(lst[i].cate_main);
dgv.Rows[i].Cells["col_cate_sub"].Value = kbDatList_src.getCateSubStr(lst[i].cate_main, lst[i].cate_sub);
dgv.Rows[i].Cells["col_account"].Value = kbDatList_src.getAccount(lst[i].id_accaount);
#if false
dgv.Rows.Add(lst[i].buy_date, // buy_date
null, // cate_main
null, // cate_sub
(0 != lst[i].ie_type ? lst[i].money.ToString() : ""), // income
(0 == lst[i].ie_type ? lst[i].money.ToString() : ""), // payment
lst[i].name, // name
null, // account
lst[i].pay_date, // pay_date
lst[i].where, // where
lst[i].memo, // memo
lst[i].id, // id
lst[i].id_usr, // id_usr
lst[i].ie_type, // ie_type
lst[i].rid // rid
);
#endif
}
kbDatList_src.ResumeLayout();
lbltotalincome_src.Text = income.ToString("#,##0 円");
lbltotalpayment_src.Text = payment.ToString("#,##0 円");
kbDatList_src.Focus();
}
