C# DataTable.Compute方法代碼示例

        protected override DataTable UpdateDatasource(DataTable dtSource)
        {
            dtSource.DefaultView.RowFilter = "timeband='Average CPP'";
            var dtAvgView = dtSource.DefaultView.ToTable().DefaultView;
            dtAvgView.Sort = "cpp desc";

            DataTable dtKenhPhat = dtAvgView.ToTable(true, "kenh_phat","kenh_phat_name");

            dtSource.DefaultView.RowFilter = "";
            var dtRpt = dtSource.DefaultView.ToTable(true,ColGroupType, ColTimeband);
            dtRpt.PrimaryKey = new[] {dtRpt.Columns[ColGroupType], dtRpt.Columns[ColTimeband] };

            var minDate = (DateTime)dtSource.Compute("min(ngay_phat)", "");
            var maxDate = (DateTime)dtSource.Compute("max(ngay_phat)", "");
            var isSameYear = minDate.Year == maxDate.Year;

            foreach (DataRow rowKenhPhat in dtKenhPhat.Rows)
            {
                var idKenh = rowKenhPhat["kenh_phat"];
                dtAvgView.RowFilter = "kenh_phat=" + idKenh;
                 dtAvgView.Sort = "ngay_phat asc";
                var band = AddBand(rowKenhPhat["kenh_phat_name"].ToString(), idKenh);
                var dtThu = dtAvgView.ToTable(true, "ngay_phat");
                int index = 1;

                foreach (DataRow row in dtThu.Rows)
                {
                    var date = (DateTime) row["ngay_phat"];
                    dtSource.DefaultView.RowFilter = string.Format("KENH_PHAT={0} and ngay_phat='{1}'", idKenh, date);
                    var dtByKenhAndMonth = dtSource.DefaultView.ToTable(true,ColGroupType,ColTimeband, "CPP");

                    var fiedName = string.Format("CPP_{0}_{1}_{2}", idKenh, date.Year, date.Month);
                    dtByKenhAndMonth.Columns["CPP"].ColumnName = fiedName;
                    dtRpt.Merge(dtByKenhAndMonth);

                    var col = new BandedGridColumn()
                                  {
                                      Name = string.Format("Col{0}",fiedName),
                                      Caption =string.Format("Tháng {0}{1}", date.Month, (isSameYear?"":"/"+date.Year)),
                                      Visible = true,
                                      VisibleIndex = index

                                  };
                    col.OptionsColumn.AllowMove = false;
                    col.OptionsColumn.AllowSort = DefaultBoolean.False;
                    GridViewDetail.Columns.Add(col);
                    band.Columns.Add(col);
                    HelpGridColumn.CotCalcEdit(col, fiedName, 0);
                    index++;
                }

            }

            var dss = new DataSet();
            dss.Tables.Add(dtRpt);
            return dtRpt;
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            #region Initialize Values
            CheckSchoolAdminSession();
            strSchoolID = AdminInfo["SchoolID"].ToString();
            GetCPSchoolInfo(strSchoolID);
            StrictNoCache = false;
            HelpQueryStringVars = "&HelpSHID=52&CP=true";
            #endregion Initialize Values

            #region Properties For The School Base Class
            TitleBar = CareerCruisingWeb.CCLib.Common.CP.TitleMain;
            SubTitleBar = TextCode(8538);
            AddSubNavBarLink("Course Planner Reports", "CP_Reports.aspx");
            AddSubNavBarLink("College Course Forecast Summary", "");
            HasLeftButtons = true;
            HelpQueryStringVars = "&HelpSHID=52&CP=true";
            #endregion Properties For The School Base Class

            intYear = (DateTime.Now.Month > 8) ? DateTime.Now.Year + 1 : DateTime.Now.Year;

            #region Initiate repeater

            string strSQL = String.Format(@"
            DECLARE @CurrentYear INT
            SET @CurrentYear =
            CASE WHEN MONTH(GETDATE()) > 8 THEN
            YEAR(GETDATE())
            ELSE
            YEAR(GETDATE()) - 1
            END
            SELECT CollegeID, College, CourseCode, CourseName,
            SUM(CASE WHEN @CurrentYear + 1 = [Year] THEN TotalCourses ELSE 0 END) AS '1',
            SUM(CASE WHEN @CurrentYear + 2 = [Year] THEN TotalCourses ELSE 0 END) AS '2',
            SUM(CASE WHEN @CurrentYear + 3 = [Year] THEN TotalCourses ELSE 0 END) AS '3',
            SUM(CASE WHEN @CurrentYear + 4 = [Year] THEN TotalCourses ELSE 0 END) AS '4'
            FROM CP_CollegeCourseSummary
            WHERE HighSchoolID={0}
            GROUP BY CollegeID, College, CourseCode, CourseName
            ORDER BY College, CourseName
                        ", strSchoolID);
            //Response.Write(strSQL);
            //Response.End();
            dt = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable(strSQL);
            rptData.DataSource = dt;
            rptData.DataBind();
            #endregion

            str01 = dt.Compute("Sum([1])", "").ToString();
            str02 = dt.Compute("Sum([2])", "").ToString();
            str03 = dt.Compute("Sum([3])", "").ToString();
            str04 = dt.Compute("Sum([4])", "").ToString();

            strChartData = String.Format(@"
                    ['{0}', {1}], ['{2}', {3}], ['{4}', {5}], ['{6}', {7}]
                        ", intYear, str01, intYear + 1, str02, intYear + 2, str03, intYear + 3, str04);
        }

        private static object Comput()
        {
            var table = new DataTable();

            var colId = new DataColumn("id", typeof(System.String));
            table.Columns.Add(colId);

            var colPrice = new DataColumn("price", typeof(System.Decimal));
            table.Columns.Add(colPrice);

            var colQuantity = new DataColumn("quantity", typeof(System.Int32));
            table.Columns.Add(colQuantity);

            var tr = table.NewRow();

            tr["id"] = "n001";
            tr["price"] = 1.5M;
            tr["quantity"] = 2;
            table.Rows.Add(tr);
            Object value = null;
            try
            {
                value = table.Compute("Max(price)", "id='n001'");
            }
            catch (Exception ex)
            {

            }
            return value;
        }

        public static string Canhbaotamung(KcbLuotkham objLuotkham, DataSet dsData, DataTable dtTamung)
        {
            try
            {
                decimal Tong_CLS = Utility.DecimaltoDbnull(dsData.Tables[0].Compute("SUM(TT_BN)", "1=1"));
                decimal Tong_Thuoc = Utility.DecimaltoDbnull(dsData.Tables[1].Compute("SUM(TT_BN)", "1=1"));
                decimal Tong_VTTH = Utility.DecimaltoDbnull(dsData.Tables[2].Compute("SUM(TT_BN)", "1=1"));
                decimal Tong_Giuong = Utility.DecimaltoDbnull(dsData.Tables[3].Compute("SUM(TT_BN)", "1=1"));
                decimal Tong_Goi = Utility.DecimaltoDbnull(dsData.Tables[4].Compute("SUM(TT_BN)", "1=1"));
                decimal Tong_Tamung = Utility.DecimaltoDbnull(dtTamung.Compute("SUM(so_tien)", "1=1"));
                decimal Tong_chiphi = Tong_CLS + Tong_Thuoc + Tong_Giuong + Tong_Goi + Tong_VTTH;
                Decimal Gioihancanhbao = Utility.DecimaltoDbnull(THU_VIEN_CHUNG.Laygiatrithamsohethong("NOITRU_GIOIHAN_NOPTIENTAMUNG", "0", true), 0);
                if (Tong_Tamung - Tong_chiphi > Gioihancanhbao)//OK
                {
                    return "";
                }
                string s1 = String.Format(Utility.FormatDecimal(), Gioihancanhbao);
                string s2 = String.Format(Utility.FormatDecimal(), String.Format(Utility.FormatDecimal(), Convert.ToDecimal((Tong_Tamung - Tong_chiphi).ToString())));
                string s3 = String.Format(Utility.FormatDecimal(), Gioihancanhbao - (Tong_Tamung - Tong_chiphi));
                string result = string.Format("Giới hạn cảnh báo <={0}. Hiện tại, Tổng tạm ứng - Tổng chi phí = {1}(Cần đóng thêm: {2})", s1, s2,s3);
                return result;
            }
            catch (Exception ex)
            {
                return ex.Message;

            }
        }

 private void btnQuery_Click(object sender, EventArgs e)
 {
     string id = "";
     if (cmbType.SelectedValue.ToString() != null)
     {
         id += " and c.typecode='" + cmbType.SelectedValue.ToString() + "'";
     }
     if (cmbBatch.Items.Count > 0)
     {
         if (cmbBatch.SelectedValue.ToString() != null)
         {
             id += " and b.batchid='" + cmbBatch.SelectedValue.ToString() + "'";
         }
     }
     else
     {
         MessageBox.Show("請選擇批次！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
         return;
     }
     dtInfo = B_btdj.Find(id).Tables[0];
     if (dtInfo.Rows.Count == 0)
     {
         MessageBox.Show("未找到數據");
         return;
     }
     else
     {
         label11.Text = dtInfo.Rows.Count.ToString();
         label13.Text = Convert.ToDouble(dtInfo.Compute("Sum(btje)", null)).ToString();
         dgvbtdj.DataSource = dtInfo;
     }
 }

 public static void GetMins(DataTable dataTable, ref Stats stats)
 {
     if (dataTable == null || stats == null) return;
     foreach (DataColumn dataColumn in dataTable.Columns)
         stats.Firsts.AddData(dataTable.Compute(
             String.Format("Min({0})", dataColumn.ColumnName), String.Empty));
 }

        private string CaculateColumn(HttpContext context)
        {
            string RuleFieldName = context.Request.Params["RuleFieldName"];
            string FieldValue = context.Request.Params["FieldValue"];
            string RuleDetail = context.Request.Params["RuleDetail"];
            string RuleFieldDBType = context.Request.Params["RuleFieldDBType"];
            string[] splitStr=new string[1];
            splitStr[0]="|";
            DataTable dt = new DataTable();
            if (RuleFieldDBType.ToUpper() == "INT")
                dt.Columns.Add(RuleFieldName,typeof(System.Int32));
            else if (RuleFieldDBType.ToUpper() == "FLOAT")
                dt.Columns.Add(RuleFieldName,typeof(System.Double));
            else if (RuleFieldDBType.ToUpper() == "DOUBLE")
                dt.Columns.Add(RuleFieldName,typeof(System.Double));
            else if (RuleFieldDBType.ToUpper() == "NUMRIC")
                dt.Columns.Add(RuleFieldName,typeof(System.Double));
            else
                dt.Columns.Add(RuleFieldName, typeof(System.String));
            string[] FieldValueArray=FieldValue.Split(splitStr, StringSplitOptions.RemoveEmptyEntries);

            for (int i = 0; i < FieldValueArray.Length; i++)
            {
                DataRow row = dt.NewRow();
                row[RuleFieldName] = FieldValueArray[i];
                dt.Rows.Add(row);

            }
            return Convert.ToString(dt.Compute(RuleDetail,"1=1"));
        }

        public DataTable Normalize(DataTable table)
        {
            foreach(DataColumn col in table.Columns)
            {
                double max = (double)table.Compute("MAX(" + col.ColumnName + ")","");
                double min = (double)table.Compute("MIN(" + col.ColumnName + ")", "");
                foreach (DataRow row in table.Rows)
                {
                    double a = double.Parse(row[col].ToString()) - min;
                    double b = max - min;
                    if (b != 0 )
                        row[col] = Math.Round(a/b,3);
                }
            }

            return table;
        }

 //Hàm dùng để tính phương sai của một cột
 public static decimal TinhDoLechChuan(DataTable dt, String colName)
 {
     Double phuongSai = 0;
     Double doLechChuan = 0;
     Double tienPhuongSai = 0;
     Double soLuongDuLieu = Convert.ToDouble(dt.Compute("count(" + colName + ")", string.Empty));
     Double giaTriTrungTinh = Convert.ToDouble(dt.Compute("avg(" + colName + ")", string.Empty));
     giaTriTrungTinh = Math.Round(giaTriTrungTinh, 3);
     int colIndex = dt.Columns.IndexOf(colName);
     foreach (DataRow dtRow in dt.Rows)
     {
         tienPhuongSai = tienPhuongSai + Math.Round(Math.Pow((Convert.ToDouble(dtRow[colIndex]) - giaTriTrungTinh), 2), 3);
     }
     phuongSai = tienPhuongSai / (soLuongDuLieu - 1);
     doLechChuan = Math.Round(Math.Sqrt(phuongSai), 2);
     return Convert.ToDecimal(doLechChuan);
 }

        public static DataTable Calculate(DataTable source, DataTable template, string formulaColumn, string[] calculateColumns)
        {
            //構造目的表
            DataTable result = template.Copy();
            foreach (string item in calculateColumns)
            {
                DataColumn column = new DataColumn(item, source.Columns[item].DataType);
                result.Columns.Add(column);
            }
            //END

            foreach (DataRow dr in result.Rows)
            {
                //公式
                string formula = dr[formulaColumn].ToString().Trim();
                //拆分公式 variableList中存儲各個因式
                IEnumerable<string> factorList = Regex.Split(formula, @"[+\-*/()]+")
                                                .Except((IEnumerable<string>)new string[] { "" })
                                                .Select(p => p = Regex.Replace(p, @"^([0-9]+)([\.]([0-9]+))?$", ""))
                                                .Except((IEnumerable<string>)new string[] { "" });
                //m_dictionary存儲各個因式及其對應的行記錄
                IDictionary<string, DataRow> m_dictionary = new Dictionary<string, DataRow>();
                foreach (string item in factorList)
                {
                    //因式(factor)
                    string factor = item.TrimStart('[').TrimEnd(']');
                    DataRow[] rows = source.Select("VariableId='" + factor + "'");
                    if (1 == rows.Count())
                    {
                        m_dictionary.Add(factor, rows[0]);
                    }
                    else if (0 == rows.Count())
                    {
                        m_dictionary.Add(factor, source.NewRow());//沒有找到則添加一個空行
                    }
                    else
                    {
                        throw new Exception(item + "對應" + rows.Count() + "條數據！");
                    }
                }

                ///item當前要計算的列名
                foreach (string item in calculateColumns)
                {
                    try
                    {
                        string tempFormulaValue = formula;
                        foreach (string node in m_dictionary.Keys.ToArray())
                        {
                            tempFormulaValue = tempFormulaValue.Replace("[" + node + "]", m_dictionary[node][item].ToString());
                        }
                        dr[item] = Convert.ToDecimal(source.Compute(tempFormulaValue, "true"));
                    }
                    catch { dr[item] = 0; };
                }
            }
            return result;
        }

 /// <summary>
 /// Calc from string
 /// </summary>
 /// <param name="input">Input</param>
 /// <returns>Returns math calc or Nan if fails</returns>
 public static double Calc(string input)
 {
     using (DataTable dt = new DataTable())
     {
         try { return Convert.ToDouble(dt.Compute(input, "")); }
         catch { }
     }
     return double.NaN;
 }

        public DataTable SummaryTotal(DataTable summary)
        {
            DataTable total = new DataTable();
            total.Rows.Add();
            total.Columns.Add("name", typeof(string));
            total.Columns.Add("next_payment", typeof(decimal));

            total.Rows[0]["next_payment"] = summary.Compute(string.Format("SUM({0})", "next_payment"), null);
            return total;
        }

 private void load_data()
 {
   
     if (Session["GioHang"] == null || (Session["GioHang"] as DataTable).Rows.Count==0)
     {
         Response.Redirect("GioHangRong.aspx");
     }
     tbGioHang = (DataTable)Session["GioHang"];
     string strnumber = tbGioHang.Compute("Sum(TongTien)", "").ToString();
     rpGioHang.DataSource = tbGioHang;
     rpGioHang.DataBind();
 }

        public Statistics(DataTable dt, string col)
        {
            if (dt.Rows.Count == 0) return;

            //overall statistic
            try
            {
                _sum = Convert.ToDouble(dt.Compute(string.Format("Sum({0})", col), ""));
                _avg = Convert.ToDouble(dt.Compute(string.Format("Avg({0})", col), ""));
                _min = Convert.ToDouble(dt.Compute(string.Format("Min({0})", col), ""));
                _max = Convert.ToDouble(dt.Compute(string.Format("Max({0})", col), ""));

                //get number of years
                DateTime startDay = Convert.ToDateTime(dt.Compute(string.Format("Min({0})", SWATUnitResult.COLUMN_NAME_DATE), ""));
                DateTime endDay = Convert.ToDateTime(dt.Compute(string.Format("Max({0})", SWATUnitResult.COLUMN_NAME_DATE), ""));
                int years = endDay.Year - startDay.Year + 1;
                if (years > 0)
                    _annualAverage = _sum / years;
            }
            catch
            {
            }

            _col = col;
        }

 private void btnInquiry_Click(object sender, EventArgs e)
 {
     SqlConnection CmsConnection = new SqlConnection("server=TCP:192.168.1.3;database=CMS;uid=sa;pwd=Magical9070");
     CmsConnection.Open();
     string Cmscomm = "select ano,cno,cust,count(*) as 'repeat' into #TT from paylist group by ano,cno,cust having count(*) > 1" +
         "select custom.ano, custom.cno, custom.cust, comm.cname, custom.name, contract.price, custom.setdate, custom.startdate, custom.enddate, " +
         "convert(char(10),paylist.p_date,20) as p_date from comm,contract,custom,paylist,#TT where custom.ano=paylist.ano and custom.cno=paylist.cno and custom.cust=paylist.cust " +
         "and comm.ano=custom.ano and comm.cno=custom.cno and custom.ano=#TT.ano and custom.cno=#TT.cno and custom.cust=#TT.cust and custom.con_no=contract.con_no " +
         "and paylist.v_date is null and paylist.ps is null and custom.use_kind=1 and paylist.p_date between '" +
         dtpStart.Value.ToString("yyyy-MM-dd 00:00:00") + "' and '" + dtpEnd.Value.ToString("yyyy-MM-dd 23:59:59") + "' and " +
         "custom.setdate not between '" + dtpStart.Value.ToString("yyyy-MM-dd 00:00:00") + "' and '" + dtpEnd.Value.ToString("yyyy-MM-dd 23:59:59") + "'";
     SqlCommand comm = new SqlCommand(Cmscomm, CmsConnection);
     SqlDataAdapter dataadapter = new SqlDataAdapter(Cmscomm, CmsConnection);
     DataTable dtTable = new DataTable();
     dtTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
     dataadapter.Fill(dtTable);
     lblDataListNum.Text = "共" + dtTable.Rows.Count + "筆";
     dtTable.DefaultView.Sort = "p_date asc";
     if(dtTable.Rows.Count==0)
     {
         MessageBox.Show("查詢的日期區間，無復機用戶！", "注意", MessageBoxButtons.OK, MessageBoxIcon.Information);
         return;
     }
     //先將price欄位加總並指定給PriceTotal，再建一個double PriceTotalNum
     //然後再把PriceTotalNum轉成字串貨幣格式並指定給lblTotalPriceShow
     string PriceTotal = dtTable.Compute("Sum(price)", null).ToString();
     double PriceTotalNum = Convert.ToDouble(PriceTotal);
     lblTotalPriceShow.Text = PriceTotalNum.ToString("C0");
     dgvShowData.Columns.Clear();
     dgvShowData.DataSource = dtTable;
     dgvShowData.Columns[0].HeaderText = "區碼";
     dgvShowData.Columns[1].HeaderText = "社區碼";
     dgvShowData.Columns[2].HeaderText = "用戶碼";
     dgvShowData.Columns[3].HeaderText = "社區名稱";
     dgvShowData.Columns[4].HeaderText = "姓名";
     dgvShowData.Columns[5].HeaderText = "金額";
     dgvShowData.Columns[6].HeaderText = "裝機日";
     dgvShowData.Columns[7].HeaderText = "起算日";
     dgvShowData.Columns[8].HeaderText = "到期日";
     dgvShowData.Columns[9].HeaderText = "繳費日";
     dgvShowData.Columns[0].Width = 40;
     dgvShowData.Columns[1].Width = 70;
     dgvShowData.Columns[2].Width = 70;
     dgvShowData.Columns[3].Width = 120;
     dgvShowData.Columns[4].Width = 100;
     dgvShowData.Columns[5].Width = 40;
     dgvShowData.Columns[6].Width = 80;
     dgvShowData.Columns[7].Width = 80;
     dgvShowData.Columns[8].Width = 80;
     dgvShowData.Columns[9].Width = 80;
     CmsConnection.Close();
 }