运输费用公式定义：
支持iif()函数，向上取整函数
提供对外计算方法
用到，winform控件，datatable.compute,string字符串操作。
界面如下
using Genersoft.Drp.Common; using Genersoft.Drp.LS.Control; using Genersoft.Drp.TP.Client; using Genersoft.GS.Public.Biz.Client; using Genersoft.GS.Public.Biz.Control; using Genersoft.GS.Public.Focus.GuiControl; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Windows.Forms; namespace Genersoft.Drp.TP.Control.BaseData { public partial class TspExpenseformulaForm : Form { //计算表达式参数名称显示 public string vsCostsFormulaName = ""; //计算表达式参数编号显示 public string vsCostsFormula = ""; public TspExpenseformulaForm() { PubFunctionControlLS.InitForm(this, InitializeComponent); PubFunctionControlLS.AdjustSkin(this, true); } private Dictionary<string, string> dictParams = new Dictionary<string, string>(); private void TspExpenseformulaForm_Load(object sender, EventArgs e) { this.txtFormula.Text = vsCostsFormulaName; //设置行变色 this.dgvParams.RowsDefaultCellStyle.BackColor = Color.Bisque; this.dgvParams.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige; this.dgvFunc.RowsDefaultCellStyle.BackColor = Color.Bisque; this.dgvFunc.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige; this.dgvFunc.AllowUserToAddRows = false; this.FormBorderStyle = FormBorderStyle.FixedDialog; //this.dgvParams.AllowUserToAddRows = false;去掉最后一行空白行 //this.dgvParams.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.AllCells;设置自适应列宽 //加载函数 DgvFuncLoad(); //加载参数 DgvParamsLoad(); } private void DgvParamsLoad() { //取自定义项 //DataSet dsDisplay = TspCostsFormulaClient.Current.GetTspTaskBillsCustomFields("TspTaskBills", "TspTaskBills");//统计依据表头表体自定义项部分 DataTable dtcus = PubFunctionClient.GetService(null).GetCustomFieldInfo("TspTaskBills", "TspTaskBills", " IsDeleted='0' "); this.dgvParams.Columns.Add("ParamCode", "字段语义化"); this.dgvParams.Columns.Add("ParamName","参数名称"); this.dgvParams.Columns["ParamCode"].Visible = false; this.dgvParams.Rows.Add("TspTaskBills.BaseTspCost", "基本运费"); this.dgvParams.Rows.Add("TspTaskBills.BaseTspPrice", "基本单价"); this.dgvParams.Rows.Add("TspTaskBills.TotalQuantity", "运输任务总数量"); this.dgvParams.Rows.Add("TspTaskBills.TotalTransGrWeight", "运输任务总重量"); this.dgvParams.Rows.Add("TspTaskBills.TotalTransVolume", "运输任务总体积"); dictParams.Add("基本运费", "TspTaskBills.BaseTspCost"); dictParams.Add("基本单价", "TspTaskBills.BaseTspPrice"); dictParams.Add("运输任务总数量", "TspTaskBills.TotalQuantity"); dictParams.Add("运输任务总重量", "TspTaskBills.TotalTransGrWeight"); dictParams.Add("运输任务总体积", "TspTaskBills.TotalTransVolume"); foreach (DataRow dr in dtcus.Rows) { this.dgvParams.Rows.Add(string.Format("TspTaskBills.{0}", dr["FieldName"]), string.Format(@"运输任务{0}", dr["DisplayName"])); dictParams.Add(string.Format(@"运输任务{0}", dr["DisplayName"]), string.Format("TspTaskBills.{0}", dr["FieldName"])); } } private void DgvFuncLoad() { this.dgvFunc.Columns.Add("FuncName", "名称"); this.dgvFunc.Rows.Add("IIF( , , )"); this.dgvFunc.Rows.Add("向上取整{ }"); //this.dgvFunc.Rows.Add("Sum( , )"); //this.dgvFunc.Rows.Add("Avg( )"); //this.dgvFunc.Rows.Add("Min( )"); //this.dgvFunc.Rows.Add("Max( )"); } private void ClearToolStripMenuItem_Click(object sender, EventArgs e) { this.txtFormula.Clear(); } /// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void BtnMath_Click(object sender, EventArgs e) { Button btn = (Button)sender; txtFormula.SelectedText=btn.Text; //txtFormula.Text = this.txtFormula.Text.Insert(); } /// <summary> /// 确定 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void SureToolStripMenuItem_Click(object sender, EventArgs e) { ReplaceParams(); //替换参数 string checkResult = this.CheckCostFormula(); if(checkResult=="1") { DialogResult = DialogResult.OK; this.Close(); } } private void ReplaceParams() { this.vsCostsFormula = this.txtFormula.Text; this.vsCostsFormulaName = this.txtFormula.Text; //string all = @"这是一段测试数据[我们100]这是一段测试数据[你们200]这是一段测试数据[他们100 谁们300]"; Regex reg = new Regex(@"[(.+?)]"); foreach (Match m in reg.Matches(this.vsCostsFormula)) { if (dictParams.Keys.Contains(m.Groups[1].ToString())) { this.vsCostsFormula = this.vsCostsFormula.Replace(m.Groups[1].ToString(), dictParams[m.Groups[1].ToString()]); } } this.vsCostsFormula = this.vsCostsFormula.Replace("编号", ""); this.vsCostsFormula = this.vsCostsFormula.Replace("内码", ""); this.vsCostsFormula = this.vsCostsFormula.ToUpper(); //获取[]中的参数名称 } private void DgvParams_CellDoubleClick(object sender, DataGridViewCellEventArgs e) { txtFormula.SelectedText =string.Format(@"[{0}]", Convert.ToString(this.dgvParams.Rows[e.RowIndex].Cells["ParamName"].Value)); } private void DgvFunc_CellDoubleClick(object sender, DataGridViewCellEventArgs e) { txtFormula.SelectedText =Convert.ToString(this.dgvFunc.Rows[e.RowIndex].Cells["FuncName"].Value); } private void CheckToolStripMenuItem_Click(object sender, EventArgs e) { this.ReplaceParams(); this.CheckCostFormula(); } /// <summary> /// 校验 /// </summary> /// <returns></returns> private string CheckCostFormula() { string strExpress = ""; //服务端校验 //this.GetTspCostsFormulaResult(); try { strExpress = Convert.ToString(this.vsCostsFormula); if (PubFunctionDrp.IsStringEmpty(strExpress)) { this.txtFormula.Focus(); MessageBoxEx.Show("计算表达式不能为空！"); return "0"; } if (strExpress.Contains("][")) { MessageBox.Show("存在相邻的数据列之间没有运算符，请检查！"); return "0"; } //先赋值 List<string> listParams = dictParams.Values.ToList(); foreach (string strParam in listParams) { strExpress = strExpress.Replace(strParam.ToUpper(), "1.33"); } if (strExpress.Contains("向上取整")) { //拿出向上取整的整个表达式 strExpress= this.CellingFuncCompute(strExpress); } #region //如果表达式中包含聚合函数，则替换为ID和Value列，验证聚合函数是否正确 //if (strExpress.Contains("SUM") || strExpress.Contains("COUNT") || strExpress.Contains("MIN") // || strExpress.Contains("MAX") || strExpress.Contains("AVG") || strExpress.Contains("ROUND") // || strExpress.Contains("POWER")) // { // dt.Columns.Add("ID", typeof(string)); // dt.Columns.Add("Value", typeof(decimal)); // strExpress = strExpress.Replace("SUM('1')", "Sum(ID)"); // strExpress = strExpress.Replace("COUNT('1')", "Count(ID)"); // strExpress = strExpress.Replace("MIN('1')", "Min(ID)"); // strExpress = strExpress.Replace("MAX('1')", "Max(ID)"); // strExpress = strExpress.Replace("AVG('1')", "Avg(ID)"); // strExpress = strExpress.Replace("ROUND('1')", "Round(ID)"); // strExpress = strExpress.Replace("SUM(1)", "Sum(Value)"); // strExpress = strExpress.Replace("COUNT(1)", "Count(Value)"); // strExpress = strExpress.Replace("MIN(1)", "Min(Value)"); // strExpress = strExpress.Replace("MAX(1)", "Max(Value)"); // strExpress = strExpress.Replace("AVG(1)", "Avg(Value)"); // strExpress = strExpress.Replace("ROUND('1')", "Round(Value)"); // if (strExpress.Contains("IIF")) // { // strExpress = strExpress.Replace("SUM(ID)", "'1'"); // strExpress = strExpress.Replace("COUNT(ID)", "'1'"); // strExpress = strExpress.Replace("MIN(ID)", "'1'"); // strExpress = strExpress.Replace("MAX(ID)", "'1'"); // strExpress = strExpress.Replace("AVG(ID)", "'1'"); // strExpress = strExpress.Replace("ROUND(ID)", "'1'"); // strExpress = strExpress.Replace("SUM(Value)", "1"); // strExpress = strExpress.Replace("COUNT(Value)", "1"); // strExpress = strExpress.Replace("MIN(Value)", "1"); // strExpress = strExpress.Replace("MAX(Value)", "1"); // strExpress = strExpress.Replace("AVG(Value)", "1"); // strExpress = strExpress.Replace("ROUND(Value)", "1"); // } // } //int MaxCount = 0; //while (strExpress.Contains("ROUND")) //{ // var mx = Regex.Matches(strExpress, "ROUND\([0-9]+[.]?[0-9]*,[0-9]+[.]?[0-9]*\)", RegexOptions.IgnoreCase); // if (mx != null && mx.Count > 0) // { // strExpress = strExpress.Replace(mx[0].Value, "1"); // } // else // { // MaxCount++; // } // if (MaxCount >= 50) // { // MessageBoxEx.Show("Round函数校验不通过，请检查！"); // return; // } //} //MaxCount = 0; //while (strExpress.Contains("POWER")) //{ // var mx = Regex.Matches(strExpress, "POWER\([0-9]+[.]?[0-9]*,[0-9]+[.]?[0-9]*\)", RegexOptions.IgnoreCase); // if (mx != null && mx.Count > 0) // { // strExpress = strExpress.Replace(mx[0].Value, "1"); // } // else // { // MaxCount++; // } // if (MaxCount >= 50) // { // MessageBoxEx.Show("Power函数校验不通过，请检查！"); // } //} #endregion //如果抛出异常，则计算表达式验证失败，否则验证成功 DatableComputeFunc(strExpress); MessageBoxEx.Show("校验成功！"); //MessageBoxEx.Show("校验成功！"); return "1"; } catch (Exception ex) { MessageBoxEx.Show("校验失败"+ex); return "0"; } } //向上取整函数的嵌套实现 private string CellingFuncCompute(string strExpress)
{
int idxStart = 0;//开始位置索引
int idxEnd = 0;//结束位置索引
string strMathCelling = "";//向上取整表达式
int count = Regex.Matches(strExpress, "向上取整").Count;
int count1 = 0;
for (int i = 0; i < count; i++)
{
idxStart = strExpress.IndexOf("向上取整");
if (idxStart == -1)
{
break;
}
idxEnd = strExpress.IndexOf("}", idxStart);
strMathCelling = strExpress.Substring(idxStart, idxEnd - idxStart + 1);
count1 = Regex.Matches(strMathCelling, "向上取整").Count-1;
if (count1 > 0)
{
for (int j = count1; j > 0; j--)
{
idxStart = strMathCelling.IndexOf("{");
if (idxStart == -1)
{
break;
}
idxStart = strMathCelling.IndexOf("向上取整", idxStart);
idxEnd = strMathCelling.IndexOf("}", idxStart);
strMathCelling = strMathCelling.Substring(idxStart, idxEnd - idxStart+1);
continue;
}
}
//strExpress = strExpress.Replace(strCelling, "向上取整i");
string result = DatableComputeFunc(strMathCelling.Replace("向上取整", ""));
strExpress = strExpress.Replace(strMathCelling, Convert.ToString(Math.Ceiling(Convert.ToDecimal(result))));
//计算
}
return strExpress;
} private string DatableComputeFunc(string strExpress) { //如果表达式中包含聚合函数，则判断使用聚合函数的数据列，是否是本表单的 if (strExpress.Contains("IIF") || strExpress.Contains("Sum") || strExpress.Contains("Count") || strExpress.Contains("Min") || strExpress.Contains("Max") || strExpress.Contains("Avg") || strExpress.Contains("Power") || strExpress.Contains("Round")) { strExpress = strExpress.Replace("{", ""); strExpress = strExpress.Replace("}", ""); strExpress = strExpress.Replace("@", ""); string[] computeS = new string[] { "IIF", "Sum", "Count", "Min", "Max", "Avg", "Power", "Round" }; string strTemp = strExpress; for (int i = 0; i < computeS.Length; i++) { if (strExpress.Contains(computeS[i].ToString())) { strTemp = strTemp.Replace(computeS[i].ToString(), "#"); } } string[] tempSS = strTemp.Split('#'); for (int i = 1; i < tempSS.Length; i++) { strTemp = tempSS[i]; Match match = Regex.Match(strTemp, "\[[^\]]*\]"); //匹配字段 string tableName = match.Value.ToString(); //校验列 } } DataTable dt = new DataTable(); //如果包含多个''，替换为'。如果包含%'，替换为%。 if (strExpress.Contains("''")) { strExpress = strExpress.Replace("''", "'"); } if (strExpress.Contains("%'")) { strExpress = strExpress.Replace("%'", "%"); } if (strExpress.Contains("{")) { strExpress = strExpress.Replace("{", ""); } if (strExpress.Contains("}")) { strExpress = strExpress.Replace("}", ""); } if (strExpress.Contains("[")) { strExpress = strExpress.Replace("[", ""); } if (strExpress.Contains("]")) { strExpress = strExpress.Replace("]", ""); } strExpress = strExpress.ToUpper(); object test = dt.Compute(strExpress, ""); return Convert.ToString(test); } private void GetTspCostsFormulaResult() { DataSet dataSet = new DataSet(); DataTable dataTable = new DataTable(); dataTable.TableName = "TspTaskBills"; dataTable.Columns.Add("TspOrgID"); dataTable.Columns.Add("VendorID"); dataTable.Columns.Add("TspTypeID"); dataTable.Columns.Add("CostsID"); dataTable.Columns.Add("TotalQuantity"); dataTable.Columns.Add("TotalTransGrWeight"); dataTable.Columns.Add("TotalTransVolume"); dataTable.Columns.Add("BaseTspCost"); dataTable.Columns.Add("VendorCode"); dataTable.Columns.Add("TspTypeCode"); dataTable.Columns["TotalTransGrWeight"].DefaultValue = "200";//默认值放前面 DataRow dataRow = dataTable.NewRow(); dataRow["TspOrgID"] = "00000000000000000015"; dataRow["VendorID"] = "0001"; dataRow["VendorCode"] = "0001"; dataRow["TspTypeID"] = "1"; dataRow["TspTypeCode"] = "1"; dataRow["CostsID"] = "00000000000000008251"; dataRow["TotalQuantity"] = "3"; dataRow["TotalTransGrWeight"] = "2"; dataRow["TotalTransVolume"] = "1"; dataRow["BaseTspCost"] = "7"; dataTable.Rows.Add(dataRow); DataRow dataRow1 = dataTable.NewRow(); dataRow1["TspOrgID"] = "00000000000000000015"; dataRow1["VendorID"] = "0001"; dataRow1["VendorCode"] = "0001"; dataRow1["TspTypeID"] = "1"; dataRow1["TspTypeCode"] = "1"; dataRow1["CostsID"] = "00000000000000008251"; dataRow1["TotalQuantity"] = "5"; dataRow1["TotalTransGrWeight"] = "6"; dataRow1["TotalTransVolume"] = "7"; dataRow1["BaseTspCost"] = "8"; dataTable.Rows.Add(dataRow1); DataRow dataRow2 = dataTable.NewRow(); dataRow2["TspOrgID"] = "00000000000000000015"; dataRow2["VendorID"] = "0000007"; dataRow2["VendorCode"] = "0000007"; dataRow2["TspTypeID"] = "1"; dataRow2["TspTypeCode"] = "1"; dataRow2["CostsID"] = "00000000000000004615"; dataRow2["TotalQuantity"] = "5"; //dataRow2["TotalTransGrWeight"] = "6"; dataRow2["TotalTransVolume"] = "7"; dataRow2["BaseTspCost"] = "8"; dataTable.Rows.Add(dataRow2); foreach (DataRow dr in dataTable.Rows) { dr["BaseTspCost"] = 100; } dataSet.Tables.Add(dataTable); DataSet dataSetResult = new DataSet(); dataSetResult = TspCostsFormulaClient.Current.GetTspCostsFormulaResult(dataSet); } private void CancelToolStripMenuItem_Click(object sender, EventArgs e) { DialogResult = DialogResult.Cancel; this.Close(); } private void CloseToolStripMenuItem_Click(object sender, EventArgs e) { this.Close(); } } }
代码如上
对于对外计算接口，定义一个webservice，传入值datatable包含参数对应值的数据源，计算写法与校验的一样就可以。