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);
}
