Esempio n. 1
0
File: sql.cs Progetto: shun/Kakeibo
        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;
        }
Esempio n. 2
0
        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();
        }
Esempio n. 3
0
        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());

                // ユーザID
                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();
        }