private DataTable Inititle() { try { OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); OleDbCommand cmd = new OleDbCommand("Select count(order_no) From dessert_order ", conn); conn.Open(); // 共有多少筆資料 int iRecordCount = (int)cmd.ExecuteScalar(); Pagination Page = new Pagination(iRecordCount, Request["page"]); string sSql = @"Select T1.order_no, (T1.start_date + ' ~ ' + T1.end_date) As order_date, T1.start_date, T1.end_date, T1.store_name, T1.order_remark, SUM(T2.quantity * T2.item_price) As total ,SUM(T2.quantity) As quantity From (Select * From dessert_order A Left Join store B On A.store_no = B.store_no) As T1 Left Join (Select quantity, item_price, order_no, employee_no From filledIn Where employee_no = '" + Session["employee_no"].ToString() + @"' ) As T2 On T1.order_no = T2.order_no Where A.order_no Between (Select min(order_no) From (Select Top {0} order_no From dessert_order Order By order_no Desc)) And (Select min(order_no) From (Select Top {1} order_no From dessert_order Order By order_no Desc)) Group By T1.order_no, T1.start_date, T1.end_date, T1.store_name, T1.order_remark Order By T1.order_no Desc;"; sSql = Page.FormatSql(sSql); dt = new DataTable(); cmd = new OleDbCommand(sSql, conn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(dt); if (dt.Rows.Count > 0) { // 設定每筆訂單的狀態 dt.Columns.Add("status", Type.GetType("System.String")); string sStatus = ""; foreach (DataRow row in dt.Rows) { DateTime dtStartDate = Convert.ToDateTime(row["start_date"]); DateTime dtEndDate = Convert.ToDateTime(row["end_date"]); // 判斷訂單是否超過期限 if (DateTime.Now.CompareTo(dtStartDate) >= 0 && DateTime.Now.CompareTo(dtEndDate) <= 0) { sStatus = "開放中"; } else { sStatus = "已截止"; } row["status"] = sStatus; } this.GridView1.DataSource = dt; this.GridView1.DataBind(); // 設定GridView1狀態欄位的Css樣式 for (int i = 0; i < this.GridView1.Rows.Count; i++) { if (((Label)this.GridView1.Rows[i].Cells[5].FindControl("lblstatus")).Text == "開放中") { ((Label)this.GridView1.Rows[i].Cells[5].FindControl("lblstatus")).ControlStyle.CssClass = "badge badge-pill badge-info"; ((Button)this.GridView1.Rows[i].Cells[6].FindControl("btnOrder")).Enabled = true; } else { ((Label)this.GridView1.Rows[i].Cells[5].FindControl("lblstatus")).ControlStyle.CssClass = "badge badge-pill badge-secondary"; ((Button)this.GridView1.Rows[i].Cells[6].FindControl("btnOrder")).Enabled = false; } } // 輸出頁碼 this.lblPagination.Text = Page.PaginationOut("uOrder_List.aspx").ToString(); this.lblResultStats.Text = "共" + Page.TotalPage + "頁," + iRecordCount.ToString() + "筆結果"; } else { dt.Rows.Add(dt.NewRow()); this.GridView1.DataSource = dt; this.GridView1.DataBind(); this.GridView1.Rows[0].Cells.Clear(); this.GridView1.Rows[0].Cells.Add(new TableCell()); this.GridView1.Rows[0].Cells[0].ColumnSpan = this.GridView1.Columns.Count; this.GridView1.Rows[0].Cells[0].Text = "查無資料..."; this.GridView1.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center; this.lblResultStats.Text = ""; this.lblPagination.Text = ""; } } catch (Exception ex) { this.divAlert.Style.Add("display", "block"); this.lblAlert.Text = "Error : " + ex.Message; } return(dt); }
private DataTable GetData() { try { string sSql = @"Select count(store_no) From store Where store_name Like '%" + sSearchStore + "%'"; OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); OleDbCommand cmd = new OleDbCommand(sSql, conn); cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("store_name", sSearchStore); conn.Open(); // 共有多少筆資料 int iRecordCount = (int)cmd.ExecuteScalar(); Pagination Page = new Pagination(iRecordCount, Request["page"]); // 輸出資料並綁定GridView sSql = @"Select * From store Where store_name Like '%" + sSearchStore + @"%'" + @" And store_no Between (Select min(store_no) From (Select Top {0} store_no From store Where store_name Like '%" + sSearchStore + @"%' Order By store_no Desc)) And ( Select min(store_no) From (Select Top {1} store_no From store Where store_name Like '%" + sSearchStore + @"%' Order By store_no Desc)) Order By store_no Desc"; sSql = Page.FormatSql(sSql); dt = new DataTable(); cmd = new OleDbCommand(sSql, conn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(dt); ViewState["dt_sotre"] = dt; if (dt.Rows.Count > 0) { this.GridView1.DataSource = ViewState["dt_sotre"]; this.GridView1.DataBind(); this.lblPagination.Text = Page.PaginationOut("aStore.aspx", sSearchStore).ToString(); this.lblResultStats.Text = "共" + Page.TotalPage + "頁," + iRecordCount.ToString() + "筆結果"; } else { dt.Rows.Add(dt.NewRow()); this.GridView1.DataSource = ViewState["dt_sotre"]; this.GridView1.DataBind(); this.GridView1.Rows[0].Cells.Clear(); this.GridView1.Rows[0].Cells.Add(new TableCell()); this.GridView1.Rows[0].Cells[0].ColumnSpan = this.GridView1.Columns.Count; this.GridView1.Rows[0].Cells[0].Text = "查無資料...馬上新增!"; this.GridView1.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center; this.lblResultStats.Text = ""; this.lblPagination.Text = ""; } } catch (Exception ex) { this.divAlert.Style.Add("display", "block"); this.lblAlert.Text = "Error : " + ex.Message; } return(dt); }