public List<KBRec> getKBDat(string szOpt)
{
List<KBRec> lst;
SQLiteDataReader reader;
SQLiteCommand command;
string szCmd = "select * from T_KBDAT";
KBRec rec;
object[] obj;
lst = new List<KBRec>();
if (0 != szOpt.Trim().Length)
{
szCmd = szCmd + " where " + szOpt + "order by buy_date";
}
rec = new KBRec();
obj = new object[13];
opendb();
command = new SQLiteCommand(szCmd, hConn);
reader = command.ExecuteReader();
while (reader.Read())
{
#if true
rec.id = reader.GetInt32(0);
rec.buy_date = reader.GetString(1);
rec.pay_date = reader.GetString(2);
rec.where = reader.GetString(3);
rec.name = reader.GetString(4);
rec.rid = reader.GetInt32(5);
rec.money = reader.GetInt32(6);
rec.ie_type = reader.GetInt16(7);
rec.cate_main = reader.GetInt16(8);
rec.cate_sub = reader.GetInt16(9);
rec.id_usr = reader.GetInt16(10);
rec.memo = reader.GetString(11);
rec.id_accaount = reader.GetInt16(12);
#else
reader.GetValues(obj);
if (!Convert.IsDBNull(obj[0]))
{
rec.id = int.Parse(obj[0].ToString());
}
else
{
rec.id = -1;
}
if (!Convert.IsDBNull(obj[0]))
{
rec.buy_date = obj[1].ToString().Split(' ')[0];
}
else
{
rec.buy_date = "";
}
if (!Convert.IsDBNull(obj[0]))
{
rec.pay_date = obj[2].ToString().Split(' ')[0];
}
else
{
rec.pay_date = "";
}
if (!Convert.IsDBNull(obj[0]))
{
rec.where = obj[3].ToString();
}
else
{
rec.where = "";
}
if (!Convert.IsDBNull(obj[0]))
{
rec.name = obj[4].ToString();
}
else
{
rec.name = "";
}
if (!Convert.IsDBNull(obj[0]))
{
rec.rid = int.Parse(obj[5].ToString());
}
else
{
rec.rid = -1;
}
if (!Convert.IsDBNull(obj[0]))
{
rec.money = int.Parse(obj[6].ToString());
}
else
{
rec.money = -1;
}
if (!Convert.IsDBNull(obj[0]))
{
rec.ie_type = Int16.Parse(obj[7].ToString());
}
else
{
rec.ie_type = -1;
}
if (!Convert.IsDBNull(obj[0]))
{
rec.cate_main = Int16.Parse(obj[8].ToString());
}
else
{
rec.cate_main = -1;
}
if (!Convert.IsDBNull(obj[0]))
{
rec.cate_sub = Int16.Parse(obj[9].ToString());
}
else
{
rec.cate_sub = -1;
}
if (!Convert.IsDBNull(obj[0]))
{
rec.id_usr = Int16.Parse(obj[10].ToString());
}
else
{
rec.id_usr = -1;
}
if (!Convert.IsDBNull(obj[0]))
{
rec.memo = obj[11].ToString();
}
else
{
rec.memo = "";
}
if (!Convert.IsDBNull(obj[0]))
{
rec.id_accaount = Int16.Parse(obj[12].ToString());
}
else
{
rec.id_accaount = -1;
}
#endif
lst.Add(rec);
}
reader.Close();
closedb();
return lst;
}
private void btnEntry_Click(object sender, EventArgs e)
{
int i = 0;
int maxid = -1;
List<KBRec> rec;
KBRec wkrec;
KBDatabase kdb = new KBDatabase();
string[] wkdate;
rec = new List<KBRec>();
wkrec = new KBRec();
maxid = kdb.getMaxKBDatId();
for (i = 0; i < dgvKBDat.Rows.Count - 1; i++)
{
wkrec.id = ++maxid;
if (null == dgvKBDat.Rows[i].Cells["buy_date"].Value)
{
wkrec.buy_date = String.Format("{0}-{1:D2}-{2:D2}", dtp.Value.Year, dtp.Value.Month, dtp.Value.Day);
}
else
{
wkdate = ((string)dgvKBDat.Rows[i].Cells["buy_date"].Value).Split('-');
wkrec.buy_date = String.Format("{0}-{1:D2}-{2:D2}", Int32.Parse(wkdate[0]), Int32.Parse(wkdate[1]), Int32.Parse(wkdate[2])); ;
}
if (txtWhere.Text.Trim().Length == 0)
{
if (null == dgvKBDat.Rows[i].Cells["where"].Value)
{
wkrec.where = "";
}
else
{
wkrec.where = (string)dgvKBDat.Rows[i].Cells["where"].Value;
}
}
else
{
wkrec.where = txtWhere.Text;
}
wkrec.rid = txtRID.Text.Trim().Length == 0 ? -1 : Int32.Parse(txtRID.Text);
if ((null == dgvKBDat.Rows[i].Cells["income"].Value) || (0 == ((string)dgvKBDat.Rows[i].Cells["income"].Value).Trim().Length))
{
// 支出
wkrec.ie_type = 0;
wkrec.money = int.Parse(((string)dgvKBDat.Rows[i].Cells["payment"].Value).Trim());
}
else if ((null == dgvKBDat.Rows[i].Cells["payment"].Value) || (0 == ((string)dgvKBDat.Rows[i].Cells["payment"].Value).Trim().Length))
{
// 収入
wkrec.ie_type = 1;
wkrec.money = int.Parse(((string)dgvKBDat.Rows[i].Cells["income"].Value).Trim());
}
else
{
MessageBox.Show(i.ToString() + " 行目の金額を入力してください。");
}
wkrec.name = (string)dgvKBDat.Rows[i].Cells["name"].Value;
wkrec.cate_main = Int16.Parse(((string)dgvKBDat.Rows[i].Cells["cate_main"].Value).Split(':')[0].Trim());
wkrec.cate_sub = Int16.Parse(((string)dgvKBDat.Rows[i].Cells["cate_sub"].Value).Split(':')[0].Trim());
wkrec.id_usr = this.id_usr;
if ((null == dgvKBDat.Rows[i].Cells["memo"].Value) || (((string)dgvKBDat.Rows[i].Cells["memo"].Value).Trim().Length == 0))
{
wkrec.memo = "";
}
else
{
wkrec.memo = (string)dgvKBDat.Rows[i].Cells["memo"].Value;
}
if (cmb_massaccount.SelectedIndex != 0)
{
string[] wk;
DateTime dt;
int eot = kdb.getEOT(int.Parse((cmb_massaccount.SelectedItem.ToString().Split(':')[0].Trim())));
wkrec.id_accaount = Int16.Parse(cmb_massaccount.SelectedItem.ToString().Split(':')[0].Trim());
if (eot != 0)
{
wk = ((string)dgvKBDat.Rows[i].Cells["buy_date"].Value).Split('-');
dt = new DateTime(int.Parse(wk[0]), int.Parse(wk[1]), eot);
dt = dt.AddMonths(1);
wkrec.pay_date = String.Format("{0}-{1}-{2}", dt.Year, dt.Month, dt.Day);
}
else
{
wkrec.pay_date = wkrec.buy_date;
}
}
else
{
wkrec.id_accaount = Int16.Parse(((string)dgvKBDat.Rows[i].Cells["balance"].Value).Split(':')[0].Trim());
wkdate = ((string)dgvKBDat.Rows[i].Cells["pay_date"].Value).Split('-');
wkrec.pay_date = String.Format("{0}-{1:D2}-{2:D2}", Int32.Parse(wkdate[0]), Int32.Parse(wkdate[1]), Int32.Parse(wkdate[2])); ;
}
rec.Add(wkrec);
}
kdb.entryKBData(rec);
updateBalance();
dgvKBDat.Rows.Clear();
txtRID.Text = "";
txtWhere.Text = "";
lbl_in.Text = "0 円";
lbl_out.Text = "0 円";
cmb_massaccount.SelectedIndex = 0;
txt_masspay.Text = "";
// cate_sub.Items.Clear();
}
private void btnEntry_Click(object sender, EventArgs e)
{
int i = 0;
int maxid = -1;
List<KBRec> rec;
KBRec wkrec;
KBDatabase kdb = new KBDatabase();
string[] wkdate;
int rowcnt = kbDatList_reg.getRowCnt();
object wkobj = null;
string err = "";
rec = new List<KBRec>();
wkrec = new KBRec();
maxid = kdb.getMaxKBDatId();
for (i = 0; i < rowcnt - 1; i++)
{
// DB内のインデックスを更新
wkrec.id = ++maxid;
#if true
wkobj = kbDatList_reg.getCellValue(i, "col_buy_date");
if (null == wkobj)
{
err = String.Format("{0}行目の日付を入力してください", i + 1);
MessageBox.Show(err, "入力項目不足");
return;
}
wkdate = ((string)wkobj).Split('-');
wkrec.buy_date = String.Format("{0}-{1:D2}-{2:D2}", Int32.Parse(wkdate[0]), Int32.Parse(wkdate[1]), Int32.Parse(wkdate[2])); ;
#else
if (null == kbDatList_reg.getCellValue(i, "col_buy_date"))
{
wkrec.buy_date = String.Format("{0}-{1:D2}-{2:D2}", dtp.Value.Year, dtp.Value.Month, dtp.Value.Day);
}
else
{
wkdate = ((string)kbDatList_reg.getCellValue(i, "col_buy_date")).Split('-');
wkrec.buy_date = String.Format("{0}-{1:D2}-{2:D2}", Int32.Parse(wkdate[0]), Int32.Parse(wkdate[1]), Int32.Parse(wkdate[2])); ;
}
#endif
// 場所
if (txtWhere.Text.Trim().Length == 0)
{
if (null == kbDatList_reg.getCellValue(i, "col_where"))
{
wkrec.where = "";
}
else
{
wkrec.where = (string)kbDatList_reg.getCellValue(i, "col_where");
}
}
else
{
wkrec.where = txtWhere.Text;
}
wkrec.rid = txtRID.Text.Trim().Length == 0 ? -1 : Int32.Parse(txtRID.Text);
if ((null == kbDatList_reg.getCellValue(i, "col_income")) || (0 == ((string)kbDatList_reg.getCellValue(i, "col_income")).Trim().Length))
{
// 支出
wkrec.ie_type = 0;
wkrec.money = int.Parse(((string)kbDatList_reg.getCellValue(i, "col_payment")).Trim());
}
else if ((null == kbDatList_reg.getCellValue(i, "col_payment")) || (0 == ((string)kbDatList_reg.getCellValue(i, "col_payment")).Trim().Length))
{
// 収入
wkrec.ie_type = 1;
wkrec.money = int.Parse(((string)kbDatList_reg.getCellValue(i, "col_income")).Trim());
}
else
{
err = String.Format("{0}行目の金額を入力してください", i + 1);
MessageBox.Show(err, "入力項目不足");
return;
}
// 品名
wkobj = kbDatList_reg.getCellValue(i, "col_name");
if (null == wkobj)
{
err = String.Format("{0}行目の品名を入力してください", i+1);
MessageBox.Show(err, "入力項目不足");
return;
}
wkrec.name = (string)wkobj;
// 費目
wkobj = kbDatList_reg.getCellValue(i, "col_cate_main");
if (null == wkobj)
{
err = String.Format("{0}行目の費目を入力してください", i + 1);
MessageBox.Show(err, "入力項目不足");
return;
}
wkrec.cate_main = Int16.Parse(((string)wkobj).Split(':')[0].Trim());
// 詳細
wkobj = kbDatList_reg.getCellValue(i, "col_cate_sub");
if (null == wkobj)
{
err = String.Format("{0}行目の詳細を入力してください", i + 1);
MessageBox.Show(err, "入力項目不足");
return;
}
wkrec.cate_sub = Int16.Parse(((string)wkobj).Split(':')[0].Trim());
// ユーザＩＤ
wkrec.id_usr = this.id_usr;
// メモ
wkobj = kbDatList_reg.getCellValue(i, "col_memo");
if ((null == wkobj) || (((string)wkobj).Trim().Length == 0))
{
wkrec.memo = "";
}
else
{
wkrec.memo = (string)wkobj;
}
// 口座
if (cmb_massaccount.SelectedIndex != 0)
{
string[] wk;
DateTime dt;
int eot = kdb.getEOT(int.Parse((cmb_massaccount.SelectedItem.ToString().Split(':')[0].Trim())));
wkrec.id_accaount = Int16.Parse(cmb_massaccount.SelectedItem.ToString().Split(':')[0].Trim());
if (eot != 0)
{
wk = wkrec.buy_date.Split('-');
dt = new DateTime(int.Parse(wk[0]), int.Parse(wk[1]), eot);
dt = dt.AddMonths(1);
wkrec.pay_date = String.Format("{0}-{1}-{2}", dt.Year, dt.Month, dt.Day);
}
else
{
wkrec.pay_date = wkrec.buy_date;
}
}
else
{
wkobj = kbDatList_reg.getCellValue(i, "col_account");
if (null == wkobj)
{
err = String.Format("{0}行目の口座を入力してください", i + 1);
MessageBox.Show(err, "入力項目不足");
return;
}
wkrec.id_accaount = Int16.Parse(((string)wkobj).Split(':')[0].Trim());
wkobj = kbDatList_reg.getCellValue(i, "col_pay_date");
if (null == wkobj)
{
err = String.Format("{0}行目の取扱日を入力してください", i + 1);
MessageBox.Show(err, "入力項目不足");
return;
}
wkdate = ((string)wkobj).Split('-');
wkrec.pay_date = String.Format("{0}-{1:D2}-{2:D2}", Int32.Parse(wkdate[0]), Int32.Parse(wkdate[1]), Int32.Parse(wkdate[2])); ;
}
rec.Add(wkrec);
}
// DBに登録
kdb.entryKBData(rec);
updateBalance();
kbDatList_reg.rowClear();
txtRID.Text = "";
txtWhere.Text = "";
lbl_in.Text = "0 円";
lbl_out.Text = "0 円";
cmb_massaccount.SelectedIndex = 0;
txt_masspay.Text = "";
// cate_sub.Items.Clear();
}