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