private void selectList()
{
//cmb_search_year.Text
string sql = "";
sql = " select isnull(month,'YY'), isnull(income_group, 'Total'), [2019], [2020] "
+ " from( "
+ " select substring(sale_yyyymm, 5, 2) month, income_group "
+ " , isnull(sum( case substring(sale_yyyymm, 1, 4) when '2019' then case income_group when 'income' then total_amt else -total_amt end end) , 0) as '2019' "
+ " , isnull(sum( case substring(sale_yyyymm, 1, 4) when '2020' then case income_group when 'income' then total_amt else -total_amt end end) , 0) as '2020' "
+ " from[summary_year_tb] "
+ " where income not in (4152, 6000) "
+ " group by substring(sale_yyyymm, 5, 2),income_group "
+ " with rollup "
+ " ) A "
+ " order by month, income_group desc ";
db.ConnectDB(); // db connectSystem.
dt = db.GetDBTable(sql);
//MessageBox.Show(" count :" + dt.Rows.Count);
dataGridView1.DataSource = dt;
//dataGridView1.DefaultCellStyle.BackColor = Color.Red;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells[1].Value.ToString() == "Total")
{
dataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.YellowGreen;
Console.WriteLine("aa : " + dataGridView1.Rows[i].Cells[1].Value.ToString());
}
else
{
Console.WriteLine("bb : " + dataGridView1.Rows[i].Cells[1].Value.ToString());
}
}
dataGridView1.Columns[2].ValueType = typeof(decimal);
dataGridView1.Columns[2].DefaultCellStyle.Format = "N2";
dataGridView1.Columns[3].ValueType = typeof(decimal);
dataGridView1.Columns[3].DefaultCellStyle.Format = "N2";
//setRowsLine(dataGridView1);
dataGridView1.Update();
db.CloseDB();
}
private void btn_create_Click(object sender, EventArgs e)
{
Console.WriteLine("btn create click");
//cmb_year
//cmb_month
//txt_pos_sales, txt_pos_lottery, txt_lottery, txt_lottery_commition
/* [sale_year] NVARCHAR(4) NOT NULL,
* [sale_month] NVARCHAR(2) NOT NULL,
* [pos_sales_amt] NUMERIC(18, 2) NULL,
* [pos_lottery_amt] NUMERIC(18, 2) NULL,
* [lottery_amt] NUMERIC(18, 2) NULL,
* [lottery_commition_amt] NUMERIC(18, 2) NULL,
* [sales_amt] NUMERIC(18, 2) NULL*/
if (String.IsNullOrWhiteSpace(cmb_year.Text) || cmb_year.Text == "Year")
{
MessageBox.Show("Year을[를] 선택해주세요");
return;
}
if (String.IsNullOrWhiteSpace(cmb_month.Text) || cmb_month.Text == "Month")
{
MessageBox.Show("Month을[를] 선택해주세요");
return;
}
if (String.IsNullOrWhiteSpace(txt_pos_sales.Text))
{
txt_pos_sales.Text = "0";
}
if (String.IsNullOrWhiteSpace(txt_pos_lottery.Text))
{
txt_pos_lottery.Text = "0";
}
if (String.IsNullOrWhiteSpace(txt_pos_tobacco.Text))
{
txt_pos_tobacco.Text = "0";
}
if (String.IsNullOrWhiteSpace(txt_lottery.Text))
{
txt_lottery.Text = "0";
}
if (String.IsNullOrWhiteSpace(txt_lottery_commition.Text))
{
txt_lottery_commition.Text = "0";
}
int sales_amt = Convert.ToInt32(txt_pos_sales.Text) - Convert.ToInt32(txt_pos_lottery.Text);
if (getId(cmb_year.Text, cmb_month.Text) > 0)
{
MessageBox.Show(cmb_year.Text + "년" + cmb_month.Text + "월은 이미 등록되어 있습니다");
return;
}
string sql = " insert into [dbo].[sales] ([sale_year],[sale_month],[pos_sales_amt],[pos_lottery_amt],[pos_tobacco_amt], "
+ " [lottery_amt],[lottery_commition_amt],[sales_amt])"
+ " values( @saleYear, @saleMonth, @posSalesAmt,@posLotteryAmt,@posTobaccoAmt,"
+ "@lotteryAmt,@lotteryCommitionAmt,@salesAmt) ";
using (SqlConnection conn = db.ConnectDB())
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@saleYear", cmb_year.Text);
cmd.Parameters.AddWithValue("@saleMonth", cmb_month.Text);
cmd.Parameters.AddWithValue("@posSalesAmt", System.Convert.ToDecimal(txt_pos_sales.Text));
cmd.Parameters.AddWithValue("@posLotteryAmt", System.Convert.ToDecimal(txt_pos_lottery.Text));
cmd.Parameters.AddWithValue("@posTobaccoAmt", System.Convert.ToDecimal(txt_pos_tobacco.Text));
cmd.Parameters.AddWithValue("@lotteryAmt", System.Convert.ToDecimal(txt_lottery.Text));
cmd.Parameters.AddWithValue("@lotteryCommitionAmt", System.Convert.ToDecimal(txt_lottery_commition.Text));
cmd.Parameters.AddWithValue("@salesAmt", sales_amt);
cmd.ExecuteNonQuery();
MessageBox.Show(" 등록 완료 !!");
// 리스트 호출
salesListAll(cmb_year.Text);
}
}
db.CloseDB();
}
private void salesListAll(string sale_year)
{
Console.WriteLine(" button_click ");
string sql = "";
if (sale_year != "")
{
sql = " select income_group, income, category "
+ " , isnull( sum( case substring([sale_yyyymm], 5, 2) when '01' then([total_amt]) end), 0) [1월] "
+ " , isnull(sum( case substring([sale_yyyymm], 5, 2) when '02' then[total_amt] end), 0) [2월] "
+ " , isnull(sum( case substring([sale_yyyymm], 5, 2) when '03' then[total_amt] end), 0) [3월] "
+ " , isnull(sum( case substring([sale_yyyymm], 5, 2) when '04' then[total_amt] end), 0) [4월] "
+ " , isnull(sum( case substring([sale_yyyymm], 5, 2) when '05' then[total_amt] end), 0) [5월] "
+ " , isnull(sum( case substring([sale_yyyymm], 5, 2) when '06' then[total_amt] end), 0) [6월] "
+ " , isnull(sum( case substring([sale_yyyymm], 5, 2) when '07' then[total_amt] end), 0) [7월] "
+ " , isnull(sum( case substring([sale_yyyymm], 5, 2) when '08' then[total_amt] end), 0) [8월] "
+ " , isnull(sum( case substring([sale_yyyymm], 5, 2) when '09' then[total_amt] end), 0) [9월] "
+ " , isnull(sum( case substring([sale_yyyymm], 5, 2) when '10' then[total_amt] end), 0) [10월] "
+ " , isnull(sum( case substring([sale_yyyymm], 5, 2) when '11' then[total_amt] end), 0) [11월] "
+ " , isnull(sum( case substring([sale_yyyymm], 5, 2) when '12' then[total_amt] end), 0) [12월] "
+ " , isnull(sum([total_amt]), 0) [Total] "
+ " from[summary_year_tb] "
+ " where substring([sale_yyyymm], 1,4) = '" + sale_year + "' "
+ " group by income_group,income, category "
+ " order by income asc ";
}
db.ConnectDB(); // db connectSystem.
dt = db.GetDBTable(sql);
//MessageBox.Show(" count :" + dt.Rows.Count);
dataGridView1.DataSource = dt;
dataGridView1.Columns[0].Width = 80;
dataGridView1.Columns[1].Width = 30;
dataGridView1.Columns[2].Width = 140;
Console.WriteLine(" dataGridView1.Rows.Count " + dataGridView1.Rows.Count);
int column_count = 16;
for (int i = 3; i < column_count; i++)
{
Console.WriteLine(" i : " + i);
dataGridView1.Columns[i].Width = 60;
dataGridView1.Columns[i].ValueType = typeof(decimal);
dataGridView1.Columns[i].DefaultCellStyle.Format = "N2";
}
int income = 0;
int sum_income = 0;
int sum_purchase = 0;
int sum_expense = 0;
int gross_profit = 0;
int net_income = 0;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
Console.WriteLine(" i : " + i);
// Convert.ToInt32(dataGridView1.Rows[i].Cells[1].Value);
income = Convert.ToInt32(dataGridView1.Rows[i].Cells[1].Value);
Console.WriteLine(" income : " + income);
if (income > 0 && income < 4000)
{
Console.WriteLine(" i : " + dataGridView1.Rows[i].Cells[15].Value);
sum_income += Convert.ToInt32(dataGridView1.Rows[i].Cells[15].Value);
if (income == 3000)
{
setTextBox(txt_sales, Convert.ToString(dataGridView1.Rows[i].Cells[15].Value));
Console.WriteLine(" >>>> income : " + income);
}
else
{
setTextBox(txt_lotto_sales, Convert.ToString(dataGridView1.Rows[i].Cells[15].Value));
Console.WriteLine(" #### income : " + income);
}
}
else if (income >= 4000 && income < 4030)
{
sum_purchase += Convert.ToInt32(dataGridView1.Rows[i].Cells[15].Value);
if (income == 4000)
{
setTextBox(txt_purchase, Convert.ToString(dataGridView1.Rows[i].Cells[15].Value));
}
else if (income == 4001)
{
setTextBox(txt_lotto_purchase, Convert.ToString(dataGridView1.Rows[i].Cells[15].Value));
}
}
else if (income >= 4150 && income < 4900)
{
if (income != 4152) // parking ..
{
sum_expense += Convert.ToInt32(dataGridView1.Rows[i].Cells[15].Value);
}
}
}
setTextBox(txt_total_income, sum_income.ToString());
setTextBox(txt_total_cogs, sum_purchase.ToString());
gross_profit = sum_income - sum_purchase;
setTextBox(txt_gross_profit, gross_profit.ToString());
setTextBox(txt_expense, sum_expense.ToString());
setTextBox(txt_total_expense, sum_expense.ToString());
net_income = gross_profit - sum_expense;
setTextBox(txt_net_income, net_income.ToString());
//dataGridView1.Columns[0].HeaderText = "ID";
dataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque;
dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige;
dataGridView1.CellBorderStyle = DataGridViewCellBorderStyle.None;
dataGridView1.DefaultCellStyle.SelectionBackColor = Color.Red;
dataGridView1.DefaultCellStyle.SelectionForeColor = Color.Yellow;
dataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
dataGridView1.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.AllowUserToResizeColumns = false;
dataGridView1.Update();
db.CloseDB(); // db close
}
private void tabaccoList()
{
string sql = "";
sql = " select sale_month"
+ " , isnull( sum( case when sale_year = '2018' then [sales_amt]- [pos_tobacco_amt] end) , 0) as '2018' "
+ " , isnull(sum( case when sale_year = '2019' then [sales_amt]- [pos_tobacco_amt] end) , 0) as '2019' "
+ " , isnull(sum( case when sale_year = '2020' then [sales_amt]- [pos_tobacco_amt] end) , 0) as '2020' "
+ " from [dbo].sales group by sale_month ";
db.ConnectDB(); // db connectSystem.
dt = db.GetDBTable(sql);
//MessageBox.Show(" count :" + dt.Rows.Count);
dataGridView1.DataSource = dt;
double a_total_amt = 0;
double b_total_amt = 0;
double c_total_amt = 0;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
a_total_amt += Convert.ToInt32(dataGridView1.Rows[i].Cells[1].Value);
b_total_amt += Convert.ToInt32(dataGridView1.Rows[i].Cells[2].Value);
c_total_amt += Convert.ToInt32(dataGridView1.Rows[i].Cells[3].Value);
}
DataRow dr = dt.NewRow();
dr[0] = "Total";
dr[1] = a_total_amt;
dr[2] = b_total_amt;
dr[3] = c_total_amt;
dt.Rows.Add(dr);
setRowsLine(dataGridView1);
int lastLine = dataGridView1.Rows.Count - 1;
dataGridView1.Rows[lastLine].DefaultCellStyle.BackColor = Color.YellowGreen;
dataGridView1.Columns[1].ValueType = typeof(decimal);
dataGridView1.Columns[1].DefaultCellStyle.Format = "N2";
dataGridView1.Columns[2].ValueType = typeof(decimal);
dataGridView1.Columns[2].DefaultCellStyle.Format = "N2";
dataGridView1.Columns[3].ValueType = typeof(decimal);
dataGridView1.Columns[3].DefaultCellStyle.Format = "N2";
dataGridView1.Update();
sql = " select sale_month"
+ " , isnull( sum( case when sale_year = '2018' then [pos_tobacco_amt] end) , 0) as '2018' "
+ " , isnull(sum( case when sale_year = '2019' then [pos_tobacco_amt] end) , 0) as '2019' "
+ " , isnull(sum( case when sale_year = '2020' then [pos_tobacco_amt] end) , 0) as '2020' "
+ " from [dbo].sales group by sale_month ";
dt = db.GetDBTable(sql);
dataGridView2.DataSource = dt;
for (int i = 0; i < dataGridView2.Rows.Count; i++)
{
a_total_amt += Convert.ToInt32(dataGridView2.Rows[i].Cells[1].Value);
b_total_amt += Convert.ToInt32(dataGridView2.Rows[i].Cells[2].Value);
c_total_amt += Convert.ToInt32(dataGridView2.Rows[i].Cells[3].Value);
}
dr = dt.NewRow();
dr[0] = "Total";
dr[1] = a_total_amt;
dr[2] = b_total_amt;
dr[3] = c_total_amt;
dt.Rows.Add(dr);
//gid add, minuUpdate
setRowsLine(dataGridView2);
lastLine = dataGridView2.Rows.Count - 1;
dataGridView2.Rows[lastLine].DefaultCellStyle.BackColor = Color.YellowGreen;
dataGridView2.Columns[1].ValueType = typeof(decimal);
dataGridView2.Columns[1].DefaultCellStyle.Format = "N2";
dataGridView2.Columns[2].ValueType = typeof(decimal);
dataGridView2.Columns[2].DefaultCellStyle.Format = "N2";
dataGridView2.Columns[3].ValueType = typeof(decimal);
dataGridView2.Columns[3].DefaultCellStyle.Format = "N2";
dataGridView2.Update();
db.CloseDB(); // db close
}
