public void select_old_date()
{
databaseClass_new dbl = new databaseClass_new();
dbl.dbConnect();
dbl.cmd = new SqlCommand("SELECT TOP 1 in_date AS old FROM ads_sales_book_det_h1_view WHERE in_date < '" + now_date + "' GROUP BY in_date order by in_date DESC", dbl.conn);
dbl.rdr = dbl.cmd.ExecuteReader();
while (dbl.rdr.Read())
{
old_date = Convert.ToDateTime(dbl.rdr["old"]).ToString("yyyy-MM-dd", en);
}
dbl.dbClosed();
}
public List <ads_sales_book_det_h1_viewModel> select_data(string date_now, string date_old)
{
List <ads_sales_book_det_h1_viewModel> item = new List <ads_sales_book_det_h1_viewModel>();
databaseClass_new dbl = new databaseClass_new();
List <string> team = new List <string>();
dbl.dbConnect();
dbl.cmd = new SqlCommand("SELECT * FROM ads_team_tab WHERE team_code LIKE 'GP%' AND team_name NOT LIKE '%คุณสุรพล%'", dbl.conn);
dbl.rdr = dbl.cmd.ExecuteReader();
while (dbl.rdr.Read())
{
team.Add(dbl.rdr["team_name"].ToString());
}
dbl.dbClosed();
//
foreach (string name in team)
{
List <string> team_id = new List <string>();
List <string> h1_name = new List <string>();
dbl.dbConnect();
dbl.cmd = new SqlCommand("SELECT * FROM ads_team_tab WHERE team_name = '" + name + "' AND team_code NOT LIKE 'H%' ORDER BY team_code ASC", dbl.conn);
dbl.rdr = dbl.cmd.ExecuteReader();
while (dbl.rdr.Read())
{
team_id.Add(dbl.rdr["team_id"].ToString());
h1_name.Add(dbl.rdr["h1_name"].ToString());
}
dbl.dbClosed();
///
int count = team_id.Count();
for (int i = 0; i < count; i++)
{
/// old
dbl.dbConnect();
dbl.cmd = new SqlCommand("SELECT empId, empName, team_name, h1_name, MIN(team_no) AS team_no, SUM(sales_amount) AS sales_amount, SUM(ads01) AS ads01, SUM(ads02) AS ads02, " +
" SUM(ads03) AS ads03, SUM(ads04) AS ads04, SUM(ads05) AS ads05, SUM(ads06) AS ads06, SUM(ads07) AS ads07, SUM(ads08) AS ads08, SUM(ads09) AS ads09, " +
" SUM(ads10) AS ads10, SUM(ads11) AS ads11" +
" FROM ( SELECT salesman.emp_id AS empId," +
" salesman.emp_name AS empName," +
" sales.ads_date," +
" team.team_name," +
" team.h1_name," +
" MIN(team.team_no) AS team_no," +
" ads_g.group_type_id," +
" ads_g.group_type_name," +
" SUM(sales.sales_amount) AS sales_amount," +
" CASE WHEN(ads_g.group_type_id = 1) THEN SUM(sales_amount) ELSE 0 END AS ads01, " +
" CASE WHEN(ads_g.group_type_id = 2) THEN SUM(sales_amount) ELSE 0 END AS ads02, " +
" CASE WHEN(ads_g.group_type_id = 3) THEN SUM(sales_amount) ELSE 0 END AS ads03, " +
" CASE WHEN(ads_g.group_type_id = 4) THEN SUM(sales_amount) ELSE 0 END AS ads04, " +
" CASE WHEN(ads_g.group_type_id = 5) THEN SUM(sales_amount) ELSE 0 END AS ads05, " +
" CASE WHEN(ads_g.group_type_id = 6) THEN SUM(sales_amount) ELSE 0 END AS ads06, " +
" CASE WHEN(ads_g.group_type_id = 7) THEN SUM(sales_amount) ELSE 0 END AS ads07, " +
" CASE WHEN(ads_g.group_type_id = 8) THEN SUM(sales_amount) ELSE 0 END AS ads08, " +
" CASE WHEN(ads_g.group_type_id = 9) THEN SUM(sales_amount) ELSE 0 END AS ads09, " +
" CASE WHEN(ads_g.group_type_id = 10) THEN SUM(sales_amount) ELSE 0 END AS ads10," +
" CASE WHEN(ads_g.group_type_id = 11) THEN SUM(sales_amount) ELSE 0 END AS ads11 " +
" FROM dbo.ads_sales_det_tab AS sales" +
" LEFT JOIN dbo.ads_saleman_tab AS salesman ON sales.emp_id = salesman.emp_id" +
" LEFT JOIN dbo.ads_type_det_tab AS ads_t ON sales.book_code = ads_t.book_code AND sales.ads_type_code = ads_t.ads_type_code" +
" LEFT JOIN dbo.ads_group_type_tab AS ads_g ON ads_t.group_type_id = ads_g.group_type_id" +
" LEFT JOIN dbo.ads_team_tab AS team ON sales.sale_team_id = team.team_id" +
" WHERE sales.sales_status = 'Y' AND sales.in_date = '" + date_old + "' AND sales.sale_team_id = '" + team_id[i] + "'" +
" GROUP BY salesman.emp_id, sales.ads_date, team.team_name, team.h1_name, ads_g.group_type_id, ads_g.group_type_name, salesman.emp_name" +
" ) AS tmp" +
" GROUP BY empId, team_name, h1_name, empName" +
" ORDER BY team_no, team_name, h1_name", dbl.conn);
dbl.rdr = dbl.cmd.ExecuteReader();
if (dbl.rdr.Read() == true)
{
amount_sale_old = Convert.ToDouble(dbl.rdr["sales_amount"]);
}
else
{
amount_sale_old = 0;
}
dbl.dbClosed();
/// now
dbl.dbConnect();
dbl.cmd = new SqlCommand("SELECT empId, empName, team_name, h1_name, MIN(team_no) AS team_no, SUM(sales_amount) AS sales_amount, SUM(ads01) AS ads01, SUM(ads02) AS ads02, " +
" SUM(ads03) AS ads03, SUM(ads04) AS ads04, SUM(ads05) AS ads05, SUM(ads06) AS ads06, SUM(ads07) AS ads07, SUM(ads08) AS ads08, SUM(ads09) AS ads09, " +
" SUM(ads10) AS ads10, SUM(ads11) AS ads11" +
" FROM ( SELECT salesman.emp_id AS empId," +
" salesman.emp_name AS empName," +
" sales.ads_date," +
" team.team_name," +
" team.h1_name," +
" MIN(team.team_no) AS team_no," +
" ads_g.group_type_id," +
" ads_g.group_type_name," +
" SUM(sales.sales_amount) AS sales_amount," +
" CASE WHEN(ads_g.group_type_id = 1) THEN SUM(sales_amount) ELSE 0 END AS ads01, " +
" CASE WHEN(ads_g.group_type_id = 2) THEN SUM(sales_amount) ELSE 0 END AS ads02, " +
" CASE WHEN(ads_g.group_type_id = 3) THEN SUM(sales_amount) ELSE 0 END AS ads03, " +
" CASE WHEN(ads_g.group_type_id = 4) THEN SUM(sales_amount) ELSE 0 END AS ads04, " +
" CASE WHEN(ads_g.group_type_id = 5) THEN SUM(sales_amount) ELSE 0 END AS ads05, " +
" CASE WHEN(ads_g.group_type_id = 6) THEN SUM(sales_amount) ELSE 0 END AS ads06, " +
" CASE WHEN(ads_g.group_type_id = 7) THEN SUM(sales_amount) ELSE 0 END AS ads07, " +
" CASE WHEN(ads_g.group_type_id = 8) THEN SUM(sales_amount) ELSE 0 END AS ads08, " +
" CASE WHEN(ads_g.group_type_id = 9) THEN SUM(sales_amount) ELSE 0 END AS ads09, " +
" CASE WHEN(ads_g.group_type_id = 10) THEN SUM(sales_amount) ELSE 0 END AS ads10," +
" CASE WHEN(ads_g.group_type_id = 11) THEN SUM(sales_amount) ELSE 0 END AS ads11 " +
" FROM dbo.ads_sales_det_tab AS sales" +
" LEFT JOIN dbo.ads_saleman_tab AS salesman ON sales.emp_id = salesman.emp_id" +
" LEFT JOIN dbo.ads_type_det_tab AS ads_t ON sales.book_code = ads_t.book_code AND sales.ads_type_code = ads_t.ads_type_code" +
" LEFT JOIN dbo.ads_group_type_tab AS ads_g ON ads_t.group_type_id = ads_g.group_type_id" +
" LEFT JOIN dbo.ads_team_tab AS team ON sales.sale_team_id = team.team_id" +
" WHERE sales.sales_status = 'Y' AND sales.in_date = '" + date_now + "' AND sales.sale_team_id = '" + team_id[i] + "'" +
" GROUP BY salesman.emp_id, sales.ads_date, team.team_name, team.h1_name, ads_g.group_type_id, ads_g.group_type_name, salesman.emp_name" +
" ) AS tmp" +
" GROUP BY empId, team_name, h1_name, empName" +
" ORDER BY team_no, team_name, h1_name", dbl.conn);
dbl.rdr = dbl.cmd.ExecuteReader();
ads_sales_book_det_h1_viewModel det = new ads_sales_book_det_h1_viewModel();
if (i == 0)
{
if (dbl.rdr.Read() == true)
{
det.team_name = dbl.rdr["team_name"].ToString();
det.ads01 = Convert.ToDouble(dbl.rdr["ads01"]);
det.ads02 = Convert.ToDouble(dbl.rdr["ads02"]);
det.ads03 = Convert.ToDouble(dbl.rdr["ads03"]);
det.ads04 = Convert.ToDouble(dbl.rdr["ads04"]);
det.ads05 = Convert.ToDouble(dbl.rdr["ads05"]);
det.ads06 = Convert.ToDouble(dbl.rdr["ads06"]);
det.ads07 = Convert.ToDouble(dbl.rdr["ads07"]);
det.ads08 = Convert.ToDouble(dbl.rdr["ads08"]);
det.ads09 = Convert.ToDouble(dbl.rdr["ads09"]);
det.ads10 = Convert.ToDouble(dbl.rdr["ads10"]);
det.ads11 = Convert.ToDouble(dbl.rdr["ads11"]);
det.sales_amount = Convert.ToDouble(dbl.rdr["sales_amount"]).ToString();
det.dif_amount = (Convert.ToDouble(dbl.rdr["sales_amount"]) - amount_sale_old).ToString();
sum_ads01 += Convert.ToDouble(dbl.rdr["ads01"]);
sum_ads02 += Convert.ToDouble(dbl.rdr["ads02"]);
sum_ads03 += Convert.ToDouble(dbl.rdr["ads03"]);
sum_ads04 += Convert.ToDouble(dbl.rdr["ads04"]);
sum_ads05 += Convert.ToDouble(dbl.rdr["ads05"]);
sum_ads06 += Convert.ToDouble(dbl.rdr["ads06"]);
sum_ads07 += Convert.ToDouble(dbl.rdr["ads07"]);
sum_ads08 += Convert.ToDouble(dbl.rdr["ads08"]);
sum_ads09 += Convert.ToDouble(dbl.rdr["ads09"]);
sum_ads10 += Convert.ToDouble(dbl.rdr["ads10"]);
sum_ads11 += Convert.ToDouble(dbl.rdr["ads11"]);
sum_amount += Convert.ToDouble(dbl.rdr["sales_amount"]);
sum_dif_amount += (Convert.ToDouble(dbl.rdr["sales_amount"]) - amount_sale_old);
total_sum_ads01 += Convert.ToDouble(dbl.rdr["ads01"]);
total_sum_ads02 += Convert.ToDouble(dbl.rdr["ads02"]);
total_sum_ads03 += Convert.ToDouble(dbl.rdr["ads03"]);
total_sum_ads04 += Convert.ToDouble(dbl.rdr["ads04"]);
total_sum_ads05 += Convert.ToDouble(dbl.rdr["ads05"]);
total_sum_ads06 += Convert.ToDouble(dbl.rdr["ads06"]);
total_sum_ads07 += Convert.ToDouble(dbl.rdr["ads07"]);
total_sum_ads08 += Convert.ToDouble(dbl.rdr["ads08"]);
total_sum_ads09 += Convert.ToDouble(dbl.rdr["ads09"]);
total_sum_ads10 += Convert.ToDouble(dbl.rdr["ads10"]);
total_sum_ads11 += Convert.ToDouble(dbl.rdr["ads11"]);
total_sum_amount += Convert.ToDouble(dbl.rdr["sales_amount"]);
total_sum_dif_amount += (Convert.ToDouble(dbl.rdr["sales_amount"]) - amount_sale_old);
}
else
{
det.team_name = name;
det.ads01 = 0;
det.ads02 = 0;
det.ads03 = 0;
det.ads04 = 0;
det.ads05 = 0;
det.ads06 = 0;
det.ads07 = 0;
det.ads08 = 0;
det.ads09 = 0;
det.ads10 = 0;
det.ads11 = 0;
det.sales_amount = "0.00";
det.dif_amount = (Convert.ToDouble("0") - amount_sale_old).ToString();
}
item.Add(det);
}
else
{
if (dbl.rdr.Read() == true)
{
det.team_name = " " + dbl.rdr["h1_name"].ToString();
det.ads01 = Convert.ToDouble(dbl.rdr["ads01"]);
det.ads02 = Convert.ToDouble(dbl.rdr["ads02"]);
det.ads03 = Convert.ToDouble(dbl.rdr["ads03"]);
det.ads04 = Convert.ToDouble(dbl.rdr["ads04"]);
det.ads05 = Convert.ToDouble(dbl.rdr["ads05"]);
det.ads06 = Convert.ToDouble(dbl.rdr["ads06"]);
det.ads07 = Convert.ToDouble(dbl.rdr["ads07"]);
det.ads08 = Convert.ToDouble(dbl.rdr["ads08"]);
det.ads09 = Convert.ToDouble(dbl.rdr["ads09"]);
det.ads10 = Convert.ToDouble(dbl.rdr["ads10"]);
det.ads11 = Convert.ToDouble(dbl.rdr["ads11"]);
det.sales_amount = Convert.ToDouble(dbl.rdr["sales_amount"]).ToString();
det.dif_amount = (Convert.ToDouble(dbl.rdr["sales_amount"]) - amount_sale_old).ToString();
sum_ads01 += Convert.ToDouble(dbl.rdr["ads01"]);
sum_ads02 += Convert.ToDouble(dbl.rdr["ads02"]);
sum_ads03 += Convert.ToDouble(dbl.rdr["ads03"]);
sum_ads04 += Convert.ToDouble(dbl.rdr["ads04"]);
sum_ads05 += Convert.ToDouble(dbl.rdr["ads05"]);
sum_ads06 += Convert.ToDouble(dbl.rdr["ads06"]);
sum_ads07 += Convert.ToDouble(dbl.rdr["ads07"]);
sum_ads08 += Convert.ToDouble(dbl.rdr["ads08"]);
sum_ads09 += Convert.ToDouble(dbl.rdr["ads09"]);
sum_ads10 += Convert.ToDouble(dbl.rdr["ads10"]);
sum_ads11 += Convert.ToDouble(dbl.rdr["ads11"]);
sum_amount += Convert.ToDouble(dbl.rdr["sales_amount"]);
sum_dif_amount += (Convert.ToDouble(dbl.rdr["sales_amount"]) - amount_sale_old);
total_sum_ads01 += Convert.ToDouble(dbl.rdr["ads01"]);
total_sum_ads02 += Convert.ToDouble(dbl.rdr["ads02"]);
total_sum_ads03 += Convert.ToDouble(dbl.rdr["ads03"]);
total_sum_ads04 += Convert.ToDouble(dbl.rdr["ads04"]);
total_sum_ads05 += Convert.ToDouble(dbl.rdr["ads05"]);
total_sum_ads06 += Convert.ToDouble(dbl.rdr["ads06"]);
total_sum_ads07 += Convert.ToDouble(dbl.rdr["ads07"]);
total_sum_ads08 += Convert.ToDouble(dbl.rdr["ads08"]);
total_sum_ads09 += Convert.ToDouble(dbl.rdr["ads09"]);
total_sum_ads10 += Convert.ToDouble(dbl.rdr["ads10"]);
total_sum_ads11 += Convert.ToDouble(dbl.rdr["ads11"]);
total_sum_amount += Convert.ToDouble(dbl.rdr["sales_amount"]);
total_sum_dif_amount += (Convert.ToDouble(dbl.rdr["sales_amount"]) - amount_sale_old);
}
else
{
det.team_name = " " + h1_name[i];
det.ads01 = 0;
det.ads02 = 0;
det.ads03 = 0;
det.ads04 = 0;
det.ads05 = 0;
det.ads06 = 0;
det.ads07 = 0;
det.ads08 = 0;
det.ads09 = 0;
det.ads10 = 0;
det.ads11 = 0;
det.sales_amount = "0.00";
det.dif_amount = ((0) - amount_sale_old).ToString();
}
item.Add(det);
}
dbl.dbClosed();
}
ads_sales_book_det_h1_viewModel dt = new ads_sales_book_det_h1_viewModel();
dt.team_name = " รวม";
dt.ads01 = sum_ads01;
dt.ads02 = sum_ads02;
dt.ads03 = sum_ads03;
dt.ads04 = sum_ads04;
dt.ads05 = sum_ads05;
dt.ads06 = sum_ads06;
dt.ads07 = sum_ads07;
dt.ads08 = sum_ads08;
dt.ads09 = sum_ads09;
dt.ads10 = sum_ads10;
dt.ads11 = sum_ads11;
dt.sales_amount = sum_amount.ToString();
dt.dif_amount = sum_dif_amount.ToString();
item.Add(dt);
sum_ads01 = 0;
sum_ads02 = 0;
sum_ads03 = 0;
sum_ads04 = 0;
sum_ads05 = 0;
sum_ads06 = 0;
sum_ads07 = 0;
sum_ads08 = 0;
sum_ads09 = 0;
sum_ads10 = 0;
sum_ads11 = 0;
sum_amount = 0;
sum_dif_amount = 0;
}
ads_sales_book_det_h1_viewModel dt2 = new ads_sales_book_det_h1_viewModel();
dt2.team_name = " รวมทั้งสิ้น";
dt2.ads01 = total_sum_ads01;
dt2.ads02 = total_sum_ads02;
dt2.ads03 = total_sum_ads03;
dt2.ads04 = total_sum_ads04;
dt2.ads05 = total_sum_ads05;
dt2.ads06 = total_sum_ads06;
dt2.ads07 = total_sum_ads07;
dt2.ads08 = total_sum_ads08;
dt2.ads09 = total_sum_ads09;
dt2.ads10 = total_sum_ads10;
dt2.ads11 = total_sum_ads11;
dt2.sales_amount = total_sum_amount.ToString();
dt2.dif_amount = total_sum_dif_amount.ToString();
item.Add(dt2);
return(item);
}
public List <ads_sales_type_main_h1_viewModel> detail2()
{
select_now_date();
select_old_date();
List <ads_sales_type_main_h1_viewModel> item = new List <ads_sales_type_main_h1_viewModel>();
databaseClass_new dbl = new databaseClass_new();
List <string> team_name = new List <string>();
dbl.dbConnect();
dbl.cmd = new SqlCommand("SELECT * FROM ads_team_tab WHERE team_code LIKE 'GP%' AND team_name NOT LIKE '%คุณสุรพล%'", dbl.conn);
dbl.rdr = dbl.cmd.ExecuteReader();
while (dbl.rdr.Read())
{
team_name.Add(dbl.rdr["team_name"].ToString());
}
dbl.dbClosed();
///
foreach (string txt in team_name)
{
List <string> team_id = new List <string>();
List <string> h1_name = new List <string>();
dbl.dbConnect();
dbl.cmd = new SqlCommand("SELECT * FROM ads_team_tab WHERE team_name = '" + txt + "' AND team_code NOT LIKE 'H%' ORDER BY team_code ASC", dbl.conn);
dbl.rdr = dbl.cmd.ExecuteReader();
while (dbl.rdr.Read())
{
team_id.Add(dbl.rdr["team_id"].ToString());
h1_name.Add(dbl.rdr["h1_name"].ToString());
}
dbl.dbClosed();
///
int count = team_id.Count();
for (int i = 0; i < count; i++)
{
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT team_name, h1_name, MIN(team_no) AS team_no, SUM(sales_amount) AS sales_amount, SUM(ads01) AS ads01, SUM(ads02) AS ads02, " +
" SUM(ads03) AS ads03, SUM(ads04) AS ads04, SUM(ads05) AS ads05" +
" FROM(SELECT sales.ads_date," +
" team.team_name," +
" team.h1_name," +
" MIN(team.team_no) AS team_no," +
" ads_g.group_type_id," +
" ads_g.group_type_name," +
" SUM(sales.sales_amount) AS sales_amount," +
" CASE WHEN(sales.type_ads = 1) THEN SUM(sales_amount) ELSE 0 END AS ads01," +
" CASE WHEN(sales.type_ads = 2) THEN SUM(sales_amount) ELSE 0 END AS ads02," +
" CASE WHEN(sales.type_ads = 3) THEN SUM(sales_amount) ELSE 0 END AS ads03," +
" CASE WHEN(sales.type_ads = 4) THEN SUM(sales_amount) ELSE 0 END AS ads04," +
" CASE WHEN(sales.type_ads = 5) THEN SUM(sales_amount) ELSE 0 END AS ads05 " +
" FROM dbo.ads_sales_det_tab AS sales" +
" INNER JOIN dbo.ads_saleman_tab AS salesman ON sales.emp_id = salesman.emp_id" +
" INNER JOIN dbo.ads_type_det_tab AS ads_t ON sales.book_code = ads_t.book_code AND sales.ads_type_code = ads_t.ads_type_code" +
" INNER JOIN dbo.ads_group_type_tab AS ads_g ON ads_t.group_type_id = ads_g.group_type_id" +
" INNER JOIN dbo.ads_team_tab AS team ON sales.sale_team_id = team.team_id" +
" WHERE sales.sales_status = 'Y' AND sales.sale_team_id = '" + team_id[i] + "' AND sales.in_date = '" + old_date + "'" +
" GROUP BY sales.ads_date, team.team_name, team.h1_name, ads_g.group_type_id, ads_g.group_type_name, sales.type_ads" +
" ) AS tmp" +
" GROUP BY team_name, h1_name" +
" ORDER BY team_no, team_name, h1_name", conn);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read() == true)
{
amount_old = Convert.ToDouble(rdr["sales_amount"]);
}
else
{
amount_old = 0;
}
}
using (SqlConnection conn = new SqlConnection(db.sqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT team_name, h1_name, MIN(team_no) AS team_no, SUM(sales_amount) AS sales_amount, SUM(ads01) AS ads01, SUM(ads02) AS ads02, " +
" SUM(ads03) AS ads03, SUM(ads04) AS ads04, SUM(ads05) AS ads05" +
" FROM(SELECT sales.ads_date," +
" team.team_name," +
" team.h1_name," +
" MIN(team.team_no) AS team_no," +
" ads_g.group_type_id," +
" ads_g.group_type_name," +
" SUM(sales.sales_amount) AS sales_amount," +
" CASE WHEN(sales.type_ads = 1) THEN SUM(sales_amount) ELSE 0 END AS ads01," +
" CASE WHEN(sales.type_ads = 2) THEN SUM(sales_amount) ELSE 0 END AS ads02," +
" CASE WHEN(sales.type_ads = 3) THEN SUM(sales_amount) ELSE 0 END AS ads03," +
" CASE WHEN(sales.type_ads = 4) THEN SUM(sales_amount) ELSE 0 END AS ads04," +
" CASE WHEN(sales.type_ads = 5) THEN SUM(sales_amount) ELSE 0 END AS ads05 " +
" FROM dbo.ads_sales_det_tab AS sales" +
" INNER JOIN dbo.ads_saleman_tab AS salesman ON sales.emp_id = salesman.emp_id" +
" INNER JOIN dbo.ads_type_det_tab AS ads_t ON sales.book_code = ads_t.book_code AND sales.ads_type_code = ads_t.ads_type_code" +
" INNER JOIN dbo.ads_group_type_tab AS ads_g ON ads_t.group_type_id = ads_g.group_type_id" +
" INNER JOIN dbo.ads_team_tab AS team ON sales.sale_team_id = team.team_id" +
" WHERE sales.sales_status = 'Y' AND sales.sale_team_id = '" + team_id[i] + "' AND sales.in_date = '" + now_date + "'" +
" GROUP BY sales.ads_date, team.team_name, team.h1_name, ads_g.group_type_id, ads_g.group_type_name, sales.type_ads" +
" ) AS tmp" +
" GROUP BY team_name, h1_name" +
" ORDER BY team_no, team_name, h1_name", conn);
SqlDataReader rdr = cmd.ExecuteReader();
if (i == 0)
{
ads_sales_type_main_h1_viewModel main = new ads_sales_type_main_h1_viewModel();
if (rdr.Read() == true)
{
main.empName = rdr["h1_name"].ToString();
main.team_name = rdr["team_name"].ToString();
main.h1_name = rdr["h1_name"].ToString();
main.team_no = rdr["team_no"].ToString();
if (rdr["sales_amount"].ToString() == "0")
{
main.sales_amount = Convert.ToDouble(rdr["sales_amount"]).ToString("0.00");
}
else
{
main.sales_amount = Convert.ToDouble(rdr["sales_amount"]).ToString("0,0.00");
}
if (rdr["ads01"].ToString() == "0")
{
main.ads01 = Convert.ToDouble(rdr["ads01"]).ToString("0.00");
}
else
{
main.ads01 = Convert.ToDouble(rdr["ads01"]).ToString("0,0.00");
}
if (rdr["ads02"].ToString() == "0")
{
main.ads02 = Convert.ToDouble(rdr["ads02"]).ToString("0.00");
}
else
{
main.ads02 = Convert.ToDouble(rdr["ads02"]).ToString("0,0.00");
}
if (rdr["ads03"].ToString() == "0")
{
main.ads03 = Convert.ToDouble(rdr["ads03"]).ToString("0.00");
}
else
{
main.ads03 = Convert.ToDouble(rdr["ads03"]).ToString("0,0.00");
}
if (rdr["ads04"].ToString() == "0")
{
main.ads04 = Convert.ToDouble(rdr["ads04"]).ToString("0.00");
}
else
{
main.ads04 = Convert.ToDouble(rdr["ads04"]).ToString("0,0.00");
}
if (rdr["ads05"].ToString() == "0")
{
main.ads05 = Convert.ToDouble(rdr["ads05"]).ToString("0.00");
}
else
{
main.ads05 = Convert.ToDouble(rdr["ads05"]).ToString("0,0.00");
}
if (((Convert.ToDouble(main.sales_amount)) - (amount_old)) == 0)
{
main.dif = ((Convert.ToDouble(main.sales_amount)) - (amount_old)).ToString("0.00");
}
else
{
main.dif = ((Convert.ToDouble(main.sales_amount)) - (amount_old)).ToString("0,0.00");
}
sum_ads01 += Convert.ToDouble(rdr["ads01"]);
sum_ads02 += Convert.ToDouble(rdr["ads02"]);
sum_ads03 += Convert.ToDouble(rdr["ads03"]);
sum_ads04 += Convert.ToDouble(rdr["ads04"]);
sum_ads05 += Convert.ToDouble(rdr["ads05"]);
sum_amount += Convert.ToDouble(rdr["sales_amount"]);
sum_dif += (Convert.ToDouble(rdr["sales_amount"]) - amount_old);
total_sum_ads01 += Convert.ToDouble(rdr["ads01"]);
total_sum_ads02 += Convert.ToDouble(rdr["ads02"]);
total_sum_ads03 += Convert.ToDouble(rdr["ads03"]);
total_sum_ads04 += Convert.ToDouble(rdr["ads04"]);
total_sum_ads05 += Convert.ToDouble(rdr["ads05"]);
total_sum_amount += Convert.ToDouble(rdr["sales_amount"]);
total_sum_dif += (Convert.ToDouble(rdr["sales_amount"]) - amount_old);
}
else
{
main.team_name = txt;
main.sales_amount = "0.00";
main.ads01 = "0.00";
main.ads02 = "0.00";
main.ads03 = "0.00";
main.ads04 = "0.00";
main.ads05 = "0.00";
main.dif = (0 - amount_old).ToString("0.00");
}
item.Add(main);
}
else
{
ads_sales_type_main_h1_viewModel main = new ads_sales_type_main_h1_viewModel();
if (rdr.Read() == true)
{
main.empName = rdr["h1_name"].ToString();
main.team_name = " " + rdr["h1_name"].ToString();
main.h1_name = rdr["h1_name"].ToString();
main.team_no = rdr["team_no"].ToString();
if (rdr["sales_amount"].ToString() == "0")
{
main.sales_amount = Convert.ToDouble(rdr["sales_amount"]).ToString("0.00");
}
else
{
main.sales_amount = Convert.ToDouble(rdr["sales_amount"]).ToString("0,0.00");
}
if (rdr["ads01"].ToString() == "0")
{
main.ads01 = Convert.ToDouble(rdr["ads01"]).ToString("0.00");
}
else
{
main.ads01 = Convert.ToDouble(rdr["ads01"]).ToString("0,0.00");
}
if (rdr["ads02"].ToString() == "0")
{
main.ads02 = Convert.ToDouble(rdr["ads02"]).ToString("0.00");
}
else
{
main.ads02 = Convert.ToDouble(rdr["ads02"]).ToString("0,0.00");
}
if (rdr["ads03"].ToString() == "0")
{
main.ads03 = Convert.ToDouble(rdr["ads03"]).ToString("0.00");
}
else
{
main.ads03 = Convert.ToDouble(rdr["ads03"]).ToString("0,0.00");
}
if (rdr["ads04"].ToString() == "0")
{
main.ads04 = Convert.ToDouble(rdr["ads04"]).ToString("0.00");
}
else
{
main.ads04 = Convert.ToDouble(rdr["ads04"]).ToString("0,0.00");
}
if (rdr["ads05"].ToString() == "0")
{
main.ads05 = Convert.ToDouble(rdr["ads05"]).ToString("0.00");
}
else
{
main.ads05 = Convert.ToDouble(rdr["ads05"]).ToString("0,0.00");
}
if (((Convert.ToDouble(main.sales_amount)) - (amount_old)) == 0)
{
main.dif = ((Convert.ToDouble(main.sales_amount)) - (amount_old)).ToString("0.00");
}
else
{
main.dif = ((Convert.ToDouble(main.sales_amount)) - (amount_old)).ToString("0,0.00");
}
if ((Convert.ToDouble(rdr["sales_amount"]) - amount_old) == 0)
{
main.dif = (Convert.ToDouble(rdr["sales_amount"]) - amount_old).ToString("0.00");
}
else
{
main.dif = (Convert.ToDouble(rdr["sales_amount"]) - amount_old).ToString("0,0.00");
}
sum_ads01 += Convert.ToDouble(rdr["ads01"]);
sum_ads02 += Convert.ToDouble(rdr["ads02"]);
sum_ads03 += Convert.ToDouble(rdr["ads03"]);
sum_ads04 += Convert.ToDouble(rdr["ads04"]);
sum_ads05 += Convert.ToDouble(rdr["ads05"]);
sum_amount += Convert.ToDouble(rdr["sales_amount"]);
sum_dif += (sum_amount - amount_old);
total_sum_ads01 += Convert.ToDouble(rdr["ads01"]);
total_sum_ads02 += Convert.ToDouble(rdr["ads02"]);
total_sum_ads03 += Convert.ToDouble(rdr["ads03"]);
total_sum_ads04 += Convert.ToDouble(rdr["ads04"]);
total_sum_ads05 += Convert.ToDouble(rdr["ads05"]);
total_sum_amount += Convert.ToDouble(rdr["sales_amount"]);
total_sum_dif += (Convert.ToDouble(rdr["sales_amount"]) - amount_old);
}
else
{
main.team_name = " " + h1_name[i];
main.sales_amount = "0.00";
main.ads01 = "0.00";
main.ads02 = "0.00";
main.ads03 = "0.00";
main.ads04 = "0.00";
main.ads05 = "0.00";
if ((0 - amount_old) == 0)
{
main.dif = Convert.ToDouble(0 - amount_old).ToString("0.00");
}
else
{
main.dif = Convert.ToDouble(0 - amount_old).ToString("0,0.00");
}
}
item.Add(main);
}
conn.Close();
}
}
ads_sales_type_main_h1_viewModel result = new ads_sales_type_main_h1_viewModel();
result.team_name = " รวม";
if (sum_amount == 0)
{
result.sales_amount = sum_amount.ToString("0.00");
}
else
{
result.sales_amount = sum_amount.ToString("0,0.00");
}
if (sum_ads01 == 0)
{
result.ads01 = sum_ads01.ToString("0.00");
}
else
{
result.ads01 = sum_ads01.ToString("0,0.00");
}
if (sum_ads02 == 0)
{
result.ads02 = sum_ads02.ToString("0.00");
}
else
{
result.ads02 = sum_ads02.ToString("0,0.00");
}
if (sum_ads03 == 0)
{
result.ads03 = sum_ads03.ToString("0.00");
}
else
{
result.ads03 = sum_ads03.ToString("0,0.00");
}
if (sum_ads04 == 0)
{
result.ads04 = sum_ads04.ToString("0.00");
}
else
{
result.ads04 = sum_ads04.ToString("0,0.00");
}
if (sum_ads05 == 0)
{
result.ads05 = sum_ads05.ToString("0.00");
}
else
{
result.ads05 = sum_ads05.ToString("0,0.00");
}
if (sum_dif == 0)
{
result.dif = sum_dif.ToString("0.00");
}
else
{
result.dif = sum_dif.ToString("0,0.00");
}
item.Add(result);
sum_amount = 0;
sum_ads01 = 0;
sum_ads02 = 0;
sum_ads03 = 0;
sum_ads04 = 0;
sum_ads05 = 0;
sum_dif = 0;
}
ads_sales_type_main_h1_viewModel total = new ads_sales_type_main_h1_viewModel();
total.team_name = " รวมทั้งสิ้น";
if (total_sum_amount == 0)
{
total.sales_amount = total_sum_amount.ToString("0.00");
}
else
{
total.sales_amount = total_sum_amount.ToString("0,0.00");
}
if (total_sum_ads01 == 0)
{
total.ads01 = total_sum_ads01.ToString("0.00");
}
else
{
total.ads01 = total_sum_ads01.ToString("0,0.00");
}
if (total_sum_ads02 == 0)
{
total.ads02 = total_sum_ads02.ToString("0.00");
}
else
{
total.ads02 = total_sum_ads02.ToString("0,0.00");
}
if (total_sum_ads03 == 0)
{
total.ads03 = total_sum_ads03.ToString("0.00");
}
else
{
total.ads03 = total_sum_ads03.ToString("0,0.00");
}
if (total_sum_ads04 == 0)
{
total.ads04 = total_sum_ads04.ToString("0.00");
}
else
{
total.ads04 = total_sum_ads04.ToString("0,0.00");
}
if (total_sum_ads05 == 0)
{
total.ads05 = total_sum_ads05.ToString("0.00");
}
else
{
total.ads05 = total_sum_ads05.ToString("0,0.00");
}
if (total_sum_dif == 0)
{
total.dif = total_sum_dif.ToString("0.00");
}
else
{
total.dif = total_sum_dif.ToString("0,0.00");
}
item.Add(total);
return(item);
}