private void btn_syukei_Click(object sender, EventArgs e) { //取引先コードのチェック if (chk_torihikisaki_cd() == false) { MessageBox.Show("取引先コードを入力してください。"); tb_torihikisaki_cd.Focus(); return; } DataTable dt_work = new DataTable(); DataTable dt_work2 = new DataTable(); DataTable dt_work3 = new DataTable(); DataTable dt_work4 = new DataTable(); DataTable dt_work5 = new DataTable(); dt_work = tss.OracleSelect("select syouhizei_sansyutu_kbn,hasu_kbn,hasu_syori_tani from tss_torihikisaki_m where torihikisaki_cd = '" + tb_torihikisaki_cd.Text + "'"); dt_work2 = tss.OracleSelect("select zeiritu from tss_syouhizei_m"); dt_work4 = tss.OracleSelect("select siire_kingaku from tss_siire_m where torihikisaki_cd = '" + tb_torihikisaki_cd.Text + "' and siire_simebi = '" + tb_siire_simebi.Text.ToString() + "'"); dt_work5 = tss.OracleSelect("select * from tss_kaikake_m where torihikisaki_cd = '" + tb_torihikisaki_cd.Text + "' and siire_simebi = '" + tb_siire_simebi.Text.ToString() + "'"); if (dt_work5.Rows.Count != 0) { string st_create_user_cd = dt_work5.Rows[0][8].ToString(); string st_create_datetime = dt_work5.Rows[0][9].ToString(); string st_update_user_cd = dt_work5.Rows[0][10].ToString(); string st_update_datetime = dt_work5.Rows[0][11].ToString(); tb_create_user_cd.Text = st_create_user_cd; tb_create_datetime.Text = st_create_datetime; tb_update_user_cd.Text = st_update_user_cd; tb_update_datetime.Text = st_update_datetime; } DateTime dt = DateTime.Parse(tb_siire_simebi.Text); string syouhizei_kbn = dt_work.Rows[0][0].ToString(); string hasu_kbn = dt_work.Rows[0][1].ToString(); string hasu_syori_tani = dt_work.Rows[0][2].ToString(); //double zeiritu = double.Parse(dt_work2.Rows[0][0].ToString()); decimal zeiritu = tss.get_syouhizeiritu(dt); if (checkBox1.Checked == true) { zeiritu = 0; } if (dt_work4.Rows.Count != 0) { if (syouhizei_kbn == "1") //明細ごと { dt_work3 = tss.OracleSelect("select siire_kingaku from tss_siire_m where torihikisaki_cd = '" + tb_torihikisaki_cd.Text + "' and siire_simebi = '" + tb_siire_simebi.Text.ToString() + "'"); //消費税計算カラム追加 dt_work3.Columns.Add("syouhizei", typeof(decimal)); int rc = dt_work3.Rows.Count; decimal siire_goukei; decimal syouhizei_goukei; for (int i = 0; i < rc; i++) { decimal syouhizeigaku = decimal.Parse(dt_work3.Rows[i][0].ToString()) * zeiritu; //端数処理 円未満の処理 if (hasu_syori_tani == "0" && hasu_kbn == "0") { syouhizeigaku = Math.Floor(syouhizeigaku); } if (hasu_syori_tani == "0" && hasu_kbn == "1") { syouhizeigaku = Math.Round(syouhizeigaku, MidpointRounding.AwayFromZero); } if (hasu_syori_tani == "0" && hasu_kbn == "2") { syouhizeigaku = Math.Ceiling(syouhizeigaku); } //端数処理 10円未満の処理 //切捨て if (hasu_syori_tani == "1" && hasu_kbn == "0") { syouhizeigaku = Math.Floor(syouhizeigaku / 10) * 10; } //四捨五入 if (hasu_syori_tani == "1" && hasu_kbn == "1") { syouhizeigaku = Math.Round(syouhizeigaku / 10) * 10; } //切上げ if (hasu_syori_tani == "1" && hasu_kbn == "2") { syouhizeigaku = Math.Ceiling(syouhizeigaku / 10) * 10; } //端数処理 100円未満の処理 //切捨て if (hasu_syori_tani == "2" && hasu_kbn == "0") { syouhizeigaku = Math.Floor(syouhizeigaku / 100) * 100; } //四捨五入 if (hasu_syori_tani == "2" && hasu_kbn == "1") { syouhizeigaku = Math.Round(syouhizeigaku / 100) * 100; } //切上げ if (hasu_syori_tani == "2" && hasu_kbn == "2") { syouhizeigaku = Math.Ceiling(syouhizeigaku / 100) * 100; } dt_work3.Rows[i][1] = syouhizeigaku; } object obj = dt_work3.Compute("SUM([siire_kingaku])", null); object obj2 = dt_work3.Compute("SUM([syouhizei])", null); siire_goukei = decimal.Parse(obj.ToString()); syouhizei_goukei = decimal.Parse(obj2.ToString()); dgv_siire_simebi.Rows[0].Cells[0].Value = tb_siire_simebi.Text; dgv_siire_simebi.Rows[0].Cells[1].Value = siire_goukei; dgv_siire_simebi.Rows[0].Cells[2].Value = syouhizei_goukei; dgv_siire_simebi.Rows[0].Cells[3].Value = siire_goukei + syouhizei_goukei; //金額右寄せ、カンマ区切り dgv_siire_simebi.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgv_siire_simebi.Columns[1].DefaultCellStyle.Format = "#,0"; dgv_siire_simebi.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgv_siire_simebi.Columns[2].DefaultCellStyle.Format = "#,0"; dgv_siire_simebi.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgv_siire_simebi.Columns[3].DefaultCellStyle.Format = "#,0"; } if (syouhizei_kbn == "2") //伝票ごと { dt_work3 = tss.OracleSelect("select siire_no,sum(siire_kingaku) from tss_siire_m where torihikisaki_cd = '" + tb_torihikisaki_cd.Text + "' and siire_simebi = '" + tb_siire_simebi.Text.ToString() + "' GROUP BY siire_no ORDER BY siire_no"); //消費税計算カラム追加 dt_work3.Columns.Add("syouhizei", typeof(decimal)); int rc = dt_work3.Rows.Count; decimal siire_goukei; decimal syouhizei_goukei; for (int i = 0; i < rc; i++) { decimal syouhizeigaku = decimal.Parse(dt_work3.Rows[i][1].ToString()) * zeiritu; //端数処理 円未満の処理 if (hasu_syori_tani == "0" && hasu_kbn == "0") { syouhizeigaku = Math.Floor(syouhizeigaku); } if (hasu_syori_tani == "0" && hasu_kbn == "1") { syouhizeigaku = Math.Round(syouhizeigaku, MidpointRounding.AwayFromZero); } if (hasu_syori_tani == "0" && hasu_kbn == "2") { syouhizeigaku = Math.Ceiling(syouhizeigaku); } //端数処理 10円未満の処理 //切捨て if (hasu_syori_tani == "1" && hasu_kbn == "0") { syouhizeigaku = Math.Floor(syouhizeigaku / 10) * 10; } //四捨五入 if (hasu_syori_tani == "1" && hasu_kbn == "1") { syouhizeigaku = Math.Round(syouhizeigaku / 10) * 10; } //切上げ if (hasu_syori_tani == "1" && hasu_kbn == "2") { syouhizeigaku = Math.Ceiling(syouhizeigaku / 10) * 10; } //端数処理 100円未満の処理 //切捨て if (hasu_syori_tani == "2" && hasu_kbn == "0") { syouhizeigaku = Math.Floor(syouhizeigaku / 100) * 100; } //四捨五入 if (hasu_syori_tani == "2" && hasu_kbn == "1") { syouhizeigaku = Math.Round(syouhizeigaku / 100) * 100; } //切上げ if (hasu_syori_tani == "2" && hasu_kbn == "2") { syouhizeigaku = Math.Ceiling(syouhizeigaku / 100) * 100; } dt_work3.Rows[i][2] = syouhizeigaku; } object obj = dt_work3.Compute("SUM([SUM(siire_kingaku)])", null); object obj2 = dt_work3.Compute("SUM([syouhizei])", null); siire_goukei = decimal.Parse(obj.ToString()); syouhizei_goukei = decimal.Parse(obj2.ToString()); dgv_siire_simebi.Rows[0].Cells[0].Value = tb_siire_simebi.Text; dgv_siire_simebi.Rows[0].Cells[1].Value = siire_goukei; dgv_siire_simebi.Rows[0].Cells[2].Value = syouhizei_goukei; dgv_siire_simebi.Rows[0].Cells[3].Value = siire_goukei + syouhizei_goukei; //金額右寄せ、カンマ区切り dgv_siire_simebi.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgv_siire_simebi.Columns[1].DefaultCellStyle.Format = "#,0"; dgv_siire_simebi.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgv_siire_simebi.Columns[2].DefaultCellStyle.Format = "#,0"; dgv_siire_simebi.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgv_siire_simebi.Columns[3].DefaultCellStyle.Format = "#,0"; } if (syouhizei_kbn == "0") // 請求合計 { dt_work3 = tss.OracleSelect("select sum(siire_kingaku) from tss_siire_m where torihikisaki_cd = '" + tb_torihikisaki_cd.Text + "' and siire_simebi = '" + tb_siire_simebi.Text.ToString() + "'"); //消費税計算カラム追加 dt_work3.Columns.Add("syouhizei", typeof(decimal)); int rc = dt_work3.Rows.Count; decimal siire_goukei; decimal syouhizei_goukei; for (int i = 0; i < rc; i++) { decimal syouhizeigaku = decimal.Parse(dt_work3.Rows[i][0].ToString()) * zeiritu; //端数処理 円未満の処理 if (hasu_syori_tani == "0" && hasu_kbn == "0") { syouhizeigaku = Math.Floor(syouhizeigaku); } if (hasu_syori_tani == "0" && hasu_kbn == "1") { syouhizeigaku = Math.Round(syouhizeigaku, MidpointRounding.AwayFromZero); } if (hasu_syori_tani == "0" && hasu_kbn == "2") { syouhizeigaku = Math.Ceiling(syouhizeigaku); } //端数処理 10円未満の処理 //切捨て if (hasu_syori_tani == "1" && hasu_kbn == "0") { syouhizeigaku = Math.Floor(syouhizeigaku / 10) * 10; } //四捨五入 if (hasu_syori_tani == "1" && hasu_kbn == "1") { syouhizeigaku = Math.Round(syouhizeigaku / 10) * 10; } //切上げ if (hasu_syori_tani == "1" && hasu_kbn == "2") { syouhizeigaku = Math.Ceiling(syouhizeigaku / 10) * 10; } //端数処理 100円未満の処理 //切捨て if (hasu_syori_tani == "2" && hasu_kbn == "0") { syouhizeigaku = Math.Floor(syouhizeigaku / 100) * 100; } //四捨五入 if (hasu_syori_tani == "2" && hasu_kbn == "1") { syouhizeigaku = Math.Round(syouhizeigaku / 100) * 100; } //切上げ if (hasu_syori_tani == "2" && hasu_kbn == "2") { syouhizeigaku = Math.Ceiling(syouhizeigaku / 100) * 100; } dt_work3.Rows[i][1] = syouhizeigaku; //siire_goukei = dt_work3.Compute("Sum(家賃)", null); ; } object obj = dt_work3.Compute("SUM([SUM(siire_kingaku)])", null); object obj2 = dt_work3.Compute("SUM([syouhizei])", null); siire_goukei = decimal.Parse(obj.ToString()); syouhizei_goukei = decimal.Parse(obj2.ToString()); dgv_siire_simebi.Rows[0].Cells[0].Value = tb_siire_simebi.Text; dgv_siire_simebi.Rows[0].Cells[1].Value = siire_goukei; dgv_siire_simebi.Rows[0].Cells[2].Value = syouhizei_goukei; dgv_siire_simebi.Rows[0].Cells[3].Value = siire_goukei + syouhizei_goukei; //金額右寄せ、カンマ区切り dgv_siire_simebi.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgv_siire_simebi.Columns[1].DefaultCellStyle.Format = "#,0"; dgv_siire_simebi.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgv_siire_simebi.Columns[2].DefaultCellStyle.Format = "#,0"; dgv_siire_simebi.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgv_siire_simebi.Columns[3].DefaultCellStyle.Format = "#,0"; } } else { MessageBox.Show("指定された条件のデータがありません"); return; } }
private decimal get_uriage(string in_cd) { decimal out_decimal; //戻り値用 decimal w_zeiritu; //消費税率 string w_syouhizei_kbn; //消費税算出区分 decimal w_syouhizei_once; //消費税計算用一時的変数 //消費税率の読み込み tss.try_string_to_date(tb_seikyu_simebi.Text); w_zeiritu = tss.get_syouhizeiritu(tss.out_datetime); //取引先毎の消費税算出区分の判断 DataTable w_dt_torihikisaki = new DataTable(); w_dt_torihikisaki = tss.OracleSelect("select * from tss_torihikisaki_m where torihikisaki_cd = '" + in_cd + "'"); w_syouhizei_kbn = w_dt_torihikisaki.Rows[0]["syouhizei_sansyutu_kbn"].ToString(); //消費税算出区分毎の処理 out_decimal = 0; if (w_syouhizei_kbn == "0") { //消費税は請求書毎 //売上の算出 DataTable w_dt = new DataTable(); w_dt = tss.OracleSelect("select sum(uriage_kingaku) from tss_uriage_m where torihikisaki_cd = '" + in_cd + "' and TO_CHAR(uriage_simebi,'YYYY/MM/DD') = '" + tb_seikyu_simebi.Text + "'"); if (w_dt.Rows.Count == 0) { out_decimal = 0; } else { out_decimal = tss.try_string_to_decimal(w_dt.Rows[0][0].ToString()); //sqlのsum分の場合、必ず1レコードできてしまい、該当データなかった場合の値がnullの為double型に変換できないので、その為の処理 if (out_decimal == -999999999) { out_decimal = 0; } } //消費税の算出 w_syouhizei = out_decimal * w_zeiritu; w_syouhizei = tss.hasu_keisan(in_cd, w_syouhizei); } if (w_syouhizei_kbn == "1") { //消費税は明細毎 DataTable w_dt = new DataTable(); w_dt = tss.OracleSelect("select * from tss_uriage_m where torihikisaki_cd = '" + in_cd + "' and uriage_simebi = '" + tb_seikyu_simebi.Text + "'"); if (w_dt.Rows.Count == 0) { out_decimal = 0; w_syouhizei = 0; } else { //1明細ずつ、合計と消費税を足す out_decimal = 0; w_syouhizei = 0; foreach (DataRow dr in w_dt.Rows) { out_decimal = out_decimal + tss.try_string_to_decimal(dr["uriage_kingaku"].ToString()); //w_syouhizei_once = tss.try_string_to_double(dr["uriage_kingaku"].ToString()) * w_zeiritu; //w_syouhizei = w_syouhizei + tss.hasu_keisan(in_cd, w_syouhizei_once); w_syouhizei = w_syouhizei + tss.try_string_to_decimal(dr["syouhizeigaku"].ToString()); } } } if (w_syouhizei_kbn == "2") { //消費税は伝票毎(売上番号毎) DataTable w_dt = new DataTable(); w_dt = tss.OracleSelect("select sum(uriage_kingaku) from tss_uriage_m where torihikisaki_cd = '" + in_cd + "' and uriage_simebi = '" + tb_seikyu_simebi.Text + "' group by uriage_no"); if (w_dt.Rows.Count == 0) { out_decimal = 0; w_syouhizei = 0; } else { //1売上番号ずつ、合計と消費税を足す out_decimal = 0; w_syouhizei = 0; foreach (DataRow dr in w_dt.Rows) { out_decimal = out_decimal + tss.try_string_to_decimal(dr[0].ToString()); w_syouhizei_once = tss.try_string_to_decimal(dr[0].ToString()) * w_zeiritu; w_syouhizei = w_syouhizei + tss.hasu_keisan(in_cd, w_syouhizei_once); } } } return(out_decimal); }