private int getId(string sale_year, string sale_month)
{
string sql = "select id from [dbo].[sales] "
+ " where sale_year = " + sale_year + " and sale_month = " + sale_month + "";
Console.WriteLine("sql:" + sql);
db.ConnectDB(); // db connectSystem.
dt = db.GetDBTable(sql);
int table_count = 0;
table_count = dt.Rows.Count;
db.CloseDB(); // db close
if (table_count > 0)
{
DataRow row = dt.Rows[0];
Console.WriteLine("id:" + row["id"]);
return(Convert.ToInt32(row["id"]));
}
else
{
return(0);
}
//return table_count;
}
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 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
}