|
|
The parser includes ParseControlExpression that can retrieve the actual value from the data grid. This is usually used in creating column formula to automatically compute column values.
The syntax for parsing data grid control is:
{Grid.GridName.ColumnName.FunctionName.FilterExpression.FormatEnum} or {GridName.ColumnName.MethodName.FilterExpression.FormatEnum}
The syntax expression is separated by dot "." and each part is explained below:
Grid = is optional only. You can totally omit it.
GridName = refers to the name of the data grid or control. This is DatasetTableName under the TableCollection data grid of the App Gallery.
ColumnName = refers to the column name of the data grid.
FunctionName = refers to the function to be used when evaluating the value. The default function is "Value" which returns the value of the cell
FilterExpression = refers to the filter to be applied before retrieving the value of the control. This uses the ado.net datatable filter expression.
FormatEnum = refers to output format such as number format and date format.
Let's us use the following data grid as an example. The Vat and WTax columns are computed automatically.
The above data grid has the following structure.
|
Caption
|
Actual Name
|
Remarks
|
|
Subsidiary Ledger
|
BookSL
|
Actual grid name may differ from the Caption
|
|
GrossTaxable
|
Amount
|
Column caption may differ from the actual Column Name
|
|
The current row is the 1st row and the rest of the columns have the same captions and column names.
|
|
Since you will be using the App Gallery when creating your formula, you can easily see the actual grid and column names. See screenshot of the App Galery below.
|
Let's us now create formula expression to get the values from the above data grid.
|
Formula Expression
|
Return Value
|
Remarks
|
|
{BookSL.VATRate.Value} or {BookSL.VATRate}
|
.12
|
BookSL = Use the actual grid name
VATRate = Actual column name
Value = Get the cell value of the current row of the column VATRate.
You can omit "Value" in the formula since this is the default FunctionName
|
|
{BookSL.Amount}
Use the actual column name "Amount"
|
112.00
|
As explained above, the actual column name is "Amount" instead of the caption "GrossTaxable". This returns the cell's current row value of the column "Amount" which is 112.00.
|
|
'{BookSL.VatAmount.Sum..ToNumber}'
|
24,012.00
|
Returns the total of the column "VatAmount" and format the result to number with thousand separator and 2 decimal places. The formula is enclosed with single quote because it's now a string type instead of numeric type. The comma separator forces us to treat it as string.
The ff FormatEnum are supported:
ToLower, ToUpper, ToNumberFormat, Trim, AllTrim, RTrim, TooaDate, etc.
See ecToParserEvaluatedFormatEnum() function below for details
|
|
if({BookSL.VATRate} > 0, ({BookSL.Amount.Value}-{BookSL.Discount.Value}) / (1+{BookSL.VATRate}) * {BookSL.VATRate},0)
|
12
|
The ParseControlExpression will substitute the grid's values as follows:
if(0.12 > 0, (112.00 - 0)/(1+0.12)*0.12,0) and finally compute the VAT Amount
|
|
You can use the following functions.
|
|
({BookSL.Amount.Sum}
|
224112
|
The "Sum" function will return the total value of the column
|
|
{BookSL.Amount.Sum.VATRate > 0}
|
224112
|
Will return the total of value of the column meeting the filter criteria "VATRate > 0"
|
|
{BookSL.EOF} and {BookSL.BOF}
|
True/False
|
EOF returns True if the grid is at the end of file
BOF return True if the grid is at the beginning of file
|
|
{BookSL.Description.Length}
|
|
Returns the length of the current cell value
|
|
You can use the this keyword to refer to the currently active data grid. Ex: {this.Amount} may be used instead of {BookSl.Amount} and the @ can be used to return the value without evaluating the formula expression.
Parser will always use the current row when evaluating the formula unless a filter condition is supplied.
|
Following are the functions supported by the parser. Function names and True or False result are case-sensitive.
|
Formula Expression
|
Result Value
|
Function Name
|
Remarks
|
|
{BookSL.Amount.Value} or
{BookSL.Amount}
|
112
|
Value
|
Returns the actual value of the current row of the column Amount. This is the default function name so can omit it and just use {BookSL.Amount}
|
|
{BookSL.VATATC.EncryptedValue}
|
|
EncryptedValue
|
Returns the encrypted value of the cell. Usually used for password
|
|
{BookSL.VATATC.DisplayValue}
|
VB 010
|
DisplayValue
|
Returns the display value of the cell. Usually used when the column has value items
|
|
@{BookSL.EntityCode.SqlValues}
|
('V00002','V00283')
|
SqlValues
|
Returns all the values in the column formatted using SQL Where In Clause format. The "@" is used to prevent the parser in evaluating the result.
|
|
{BookSL.EntityCode.CommaDelimitedValues}
|
V00002,V00283
|
CommaDelimited Values
|
Returns all the values in the column delimitted with comma
|
|
{BookSL..EOF}
|
True/False
|
EOF
BOF
|
Returns True if the grid record pointer is end-of-file or beginning-of-file, respectively.
|
|
if('{BookSL..EOF}'='True', 'True Sta', 'False Sta')
|
False Sta
|
EOF with if-then-else
|
You usually combined True/False result with an if-then-else function.
|
|
{BookSL..HasSelectedRow}
|
True/False
|
HasSelectedRow
|
Returns True if there is one or more selected rows in the grid. You skipped the ColumnName in the syntax by the double dot ".."
|
|
{BookSL..HasFocused}
|
True/False
|
HasFocused
|
Returns True if the grid BookSL is currently has focused
|
|
{BookSL..HasChanges}
|
True/False
|
HasChanges
|
Returns True if there are pending updates made to the grid that were not yet saved.
|
|
{BookSL.Dataset.HasChanges}
|
True/False
|
Dataset.HasChanges
|
Returns True if there are pending updates made to any of the data grids.
|
|
{BookSL.HasError}
|
True/False
|
HasError
|
Returns True if the grid has error
|
|
{BookSL.RowState}
|
Unchanged
Modified
Added
|
RowState
|
Returns current row state either one of the ff;
Unchanged = no changes made on current row
Modified = current row is modified
Added = current row is being added
|
|
if('{BookSL.RowState}' = 'Unchanged','T', 'F')
|
T/F
|
RowState with if
|
You usually combined this function with an if-then-else function.
|
|
{BookSL.ColumnName}
|
|
ColumnName
|
Returns the current column name that has focus
|
|
{BookSL.VATATC.Contains.VB}
|
True
|
Contains
|
Returns True if the string after the Contains function is found. In this case the string "VB"
|
|
{BookSL.VATATC.Length}
|
6
|
Length
|
Returns the length of the string "VB 010", the value of the current row of the column VATATC
|
|
{BookSL.RowCount}
|
2
|
RowCount
|
Returns current total number of records in the grid
|
|
({BookSL.Amount.Sum}
|
224112
|
Sum
|
Returns the total of the values of the column Amount
|
|
{BookSL.DueDate.DateValue}
|
44063
|
DateValue
|
Returns the numeric value of the date 08/20/2020. Can be used in getting the number of days lapsed when comparing two dates.
|
|
{HrsWorked.TimeIn.TimeValue}
|
9.75
|
TimeValue
|
Returns the numeric value of the time 9:45AM. Can be used in computing payroll.
|
|
{BookSL.EntityCode.LagValue}
|
|
LagValue
|
Returns the previous row value from the current row
|
|
{BookSL.EntityCode.LeadValue}
|
V00283
|
LeadValue
|
Returns the next row value from the current row
|
|
{BookSL.EntityCode.SumLagValue}
|
|
SumLagValue
|
Returns the total values of the previous rows from the current row
|
|
{BookSL.EntityCode.SumLeadValue}
|
|
SumLeadValue
|
Returns the total values of the succeeding rows from the current row
|
|
{BookSL.Amount.HighestValue}
|
224000
|
HighestValue
|
Returns the highest value from the column
|
|
{BookSL.Amount.LowestValue}
|
112
|
LowestValue
|
Returns the lowest value from the column
|
|
NOTE: To skip unwanted component, use dot "." in its place. Ex: {BookSL..EOF} to skip the ColumnName component since it is not needed
|
Following are the Format Enum supported by the parser.
|
Formula Expression
|
Result Value
|
Format Enum
|
Remarks
|
|
'{BookSL.Amount.Sum..ToNumber}'
|
224,112.00
|
ToNumber
|
Format the result to number with thousand separator and 2 decimal places.
Note: The formula is enclosed with single quote because it's now a string instead of numeric type.
|
|
{BookSL.VATATC.Value..ToLower}
|
vb 010
|
ToLower
ToUpper
ToTitleCase
ToSentenceCase '
|
Converts and returns cell value from "VB 010" to lower case "vb 010" of the column VATATC
|
|
{BookSL.VATATC.Value..RTrim}
|
VB 010
|
AllTrim
LTrim
RTrim
|
Delete extra spaces
|
|
{BookSL.DueDate.Value..ToShortDate}
|
08/20/2020
|
ToShortDate
ToMonth
ToDay
ToYear
ToOADate
|
ToShorDate = returns "12/01/2020"
ToMonth = returns "08"
ToDay = returns "20"
ToYear = returns "2020"
ToOADate = returns 44063
|
|
{BookSL.VATATC.Value..Left2}
|
VB
|
Left
Right
|
Retruns character from the left, the "2" represents the number of characters to be returned
|
|
{BookSL.VATATC.Value..Substring,1,4}
|
B 01
|
Substring
|
Returns substring from string 1 to 4. This is zero-based
|
|
NOTE: We skip the FilterExpression using dot "." in its place since it is no needed.
|
Below is the screenshot of the App Gallery showing the Journal Entry v20 app data structures where you can easily see the table names and the Select Statements for the column names.
Below are the actual codes used by the system for your further references.
/// <summary>
/// Syntax: GridName.ColumnName.funcName.RowFilter.FormatEnum
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="grid">Grid Control</param>
/// <param name="columnName">Column Name</param>
/// <param name="funcName">Function Name</param>
/// <param name="rowFilter">Filter criteria</param>
/// <param name="formatEnum">Format Enum </param>
/// <returns></returns>
public static T ecGetEvaluatedValue(this C1TrueDBGrid grid, string columnName, string funcName, string rowFilter, string formatEnum)
{
///
///returns evaluated value after parsing expression based on parameters given
funcName = funcName.Trim();
string retVal = string.Empty;
int bookMark = grid.Bookmark;
try
{
if (columnName == "this")
columnName = grid.FocusedSplit.DisplayColumns[grid.Col].DataColumn.DataField;
//
if (funcName.ToLower() == "value" && rowFilter.Length == 0) // revised 7/15/2019 to by removing funcName.Length == 0 as the funcName maybe zero but the columnName maybe a special function like "RowCount"
{
//this block is added on 7/4/2019 to immediately return the value of the grid's current column. Most of the time this is the value being asked by the calling module
retVal = grid.Columns[columnName].Value.ToString(); //just simply return the column value
if (retVal.Length > 0)
{
if (grid.ecGetColumnDataType(columnName).ecIsDataTypeDateType())
retVal = retVal.ecToShortDate(); //format to short date
}
else
if (grid.ecGetColumnDataType(columnName).ecIsDataTypeNumeric())
retVal = "0"; //return 0 for empty numeric field
//
return (T)Convert.ChangeType(retVal, typeof(T)); //just simply return the column value
}
//evaluate possibilities other than the real value
else if (columnName == "HasSelectedRow" || funcName == "HasSelectedRow") //for grid.HasSelectedRow expression
if (grid.ecHasSelectedRow())
retVal = "True"; //return true statement
else
retVal = "False"; //return false statement
else if (columnName == "HasFocused" || funcName == "HasFocused") //for grid.HasFocused
if (grid.Focused)
retVal = "True"; //return true statement
else
retVal = "False"; //return false statement
else if (columnName == "Dataset" && funcName == "HasChanges") //for all grids with changes. This should be && expression not || 10/29/2018
if (grid.ecGetDatasource().DataSet.HasChanges())
retVal = "True"; //return true statement
else
retVal = "False"; //return false statement
else if (columnName == "HasChanges" || funcName == "HasChanges") //for specific grid with changes
if (grid.ecGetDatasource().GetChanges() != null && grid.ecGetDatasource().GetChanges().Rows.Count > 0)
retVal = "True"; //return true statement
else
retVal = "False"; //return false statement
else if (columnName == "IsEOF" || funcName == "IsEOF" || columnName == "EOF" || funcName == "EOF") //for grid.IsEOF expression
if (grid.Row < 0 || grid.Row == grid.RowCount)
retVal = "True"; //return true statement
else
retVal = "False"; //return false statement
else if (columnName == "IsBOF" || funcName == "IsBOF" || columnName == "BOF" || funcName == "BOF") //for grid.IsBOF expression
if (grid.Row == 0)
retVal = "True"; //return true statement
else
retVal = "False";
else if (columnName == "HasErrors" || columnName == "HasError" || funcName == "HasErrors" || funcName == "HasError") //for grid.HasError expression
if (grid.ecHasErrors())
retVal = "True"; //return true statement
else
retVal = "False"; //return false statement
else if (columnName == "RowState" || funcName == "RowState") //for grid.RowState expression
retVal = grid.ecGetRowState(); //return rowState of the current row: Unchanged, Modified, or Added
else if (columnName == "ColumnName" || funcName == "ColumnName") //for grid's current column name
retVal = grid.FocusedSplit.DisplayColumns[grid.Col].DataColumn.DataField; //return column name of the current column
else if (columnName == "SelectedRows" && funcName == "Count" || (funcName == "Count" && rowFilter == "SelectedRows")) //for counting the number of selected rows in a grid
retVal = grid.SelectedRows.Count.ToString();
else if (funcName == "ForeColor")
retVal = grid.FocusedSplit.DisplayColumns[grid.Col].Style.ForeColor.Name; //does not work yet
else if (funcName == "BackColor")
retVal = grid.FocusedSplit.DisplayColumns[grid.Col].Style.BackColor.Name; //does not work yet
else if (funcName == "Contains")
{
if (grid[grid.Row, grid.FocusedSplit.DisplayColumns[grid.Col].DataColumn.DataField].ToString().IndexOf(rowFilter) >= 0)
retVal = "True";
else
retVal = "False";
}
//for with RowFilter expression, use the grid's datasource in evaluating the value except when the rowFile is equal to "selectedrows"
//selectedrows filter uses the grid in getting the values instead
else if (rowFilter != null && rowFilter.Trim().Length > 0) //should have the char "=" to consider as filter argument
{
DataView dv = grid.ecGetDatasourceCurrent(); //use the dataview in getting the value
string orginalFilter = dv.RowFilter;
try
{
dv.RowFilter = rowFilter; //apply filter
}
catch
{
dv.RowFilter = orginalFilter; //set as if rowFilter did not match any records
}
finally
{
if (funcName == "Length")
//returns the length of column value
{
if (dv.Count > 0)
retVal = dv[0][grid.Columns[columnName].DataField].ToString().Trim().Length.ToString();
else
retVal = "0"; //default to 0
}
else if (funcName == "RowCount" || columnName == "RowCount" || (columnName == "Rows" && funcName == "Count")) //for datasource row count with filter condition
retVal = dv.Count.ToString();
else if (funcName == "GridRowCount" || columnName == "GridRowCount" || (columnName == "GridRows" && funcName == "GridCount")) //for grid.Rows.Count with filter condition
retVal = grid.RowCount.ToString();
else if (funcName == "Sum") //for grid.ColumnName.Sum.RowFilter
{
retVal = dv.ecGetColumnTotal(grid.Columns[columnName].DataField, rowFilter);
}
else if (funcName.ecIsSqlWhereInPropertyType() || funcName.ecIsParserPropertyType())
{
//get column values in sql where in format
retVal = dv.ecGetColumnValuesToStringArray(grid.Columns[columnName].DataField, rowFilter).ecToStringSqlWhereInClauseFormat(dv.ToTable().ecGetColumnDataType(columnName), rowFilter);
//format further for SqlValues by adding "\" before each single quote
if (funcName.ecIsParserPropertyType())
{
retVal = retVal.ecConvertSqlWhereInToParserFormat();
}
}
else if (funcName.ToLower() == "commadelimitedvalue" || funcName.ToLower() == "commadelimitedvalues")
{
//get column values comma delimited
retVal = dv.ecGetColumnValuesToStringCommaDelimited(grid.Columns[columnName].DataField, rowFilter);
}
//
else if (funcName.ToLower() == "datevalue")
{
//get datevalue
retVal = dv[0][columnName].ToString().ecToDate("{0:g}").ToOADate().ecToStrNoFormat();
}
else if (funcName.ToLower() == "timevalue")
{
//get timevalue
if (grid.Columns[columnName].Value.ToString().Length > 0)
{
if (grid.Columns[columnName].DataType.ToString().ecIsDataTypeDateType())
{
DateTime dt = DateTime.Parse(grid.Columns[columnName].Value.ToString());
retVal = Convert.ToDecimal(dt.TimeOfDay.TotalHours).ecToStrNoFormat();
}
else if (grid.Columns[columnName].DataType.ToString().ecIsDataTypeTimeSpan())
retVal = Convert.ToDecimal(TimeSpan.Parse(grid.Columns[columnName].Value.ToString()).TotalHours).ecToStrNoFormat();
}
else
retVal = "0"; //default value
}
else //if (funcName == "Value") for grid.ColumnName.Value
{
if (dv.Count > 0)
{
retVal = dv[0][columnName].ToString(); //just return the column value
if (retVal.Length > 0 && grid.ecGetColumnDataType(columnName).ecIsDataTypeDateType())
retVal = retVal.ecToShortDate(); //format to short date
}
else
retVal = "";
}
//set original filter back
dv.RowFilter = orginalFilter;
}
}
//for without RowFilter or when RowFilter="SelectedRows", just use the grid in getting the value
else
{
if (funcName == "Length")
//returns the length of the column value
{
//check if there is selected row
if (grid.ecHasSelectedRow())
retVal = grid.Columns[columnName].Value.ToString().Trim().Length.ToString();
else
retVal = "0"; //default for no selected row
}
else if (funcName == "RowCount" || columnName == "RowCount" || (columnName == "Rows" && funcName == "Count")) //for datasource row count
retVal = grid.RowCount.ToString();
else if (funcName == "GridRowCount" || columnName == "GridRowCount" || (columnName == "GridRows" && funcName == "GridCount")) //for grid.Rows.Count with filter condition
retVal = grid.RowCount.ToString();
else if (funcName == "Sum") //for grid.ColumnName.Sum.RowFilter
retVal = grid.ecGetColumnTotal(columnName, "").ecToStrNoFormat(2);
//
else if (funcName == "EncryptedValue" && rowFilter.Length == 0)
retVal = ecFunction.PasswordEncryption(grid.Columns[columnName].Value.ToString());
else if (funcName.ToLower() == "displayvalue" && rowFilter.Length == 0)
retVal = grid.Columns[columnName].CellText(grid.Row).ToString();
else if ((funcName.ecIsSqlWhereInPropertyType() || funcName.ecIsParserPropertyType()) && rowFilter.Length == 0)
{
//get column values in sql where in format
DataView dv = grid.ecGetUderlyingDataView();
retVal = dv.ecGetColumnValuesToStringArray(grid.Columns[columnName].DataField, rowFilter).ecToStringSqlWhereInClauseFormat(dv.ToTable().ecGetColumnDataType(columnName));
//format further for SqlValues by adding "\" before each single quote
if (funcName.ecIsParserPropertyType())
{
retVal = retVal.ecConvertSqlWhereInToParserFormat();
}
}
//
else if ((funcName.ToLower() == "commadelimitedvalue" || funcName.ToLower() == "commadelimitedvalues") && rowFilter.Length == 0)
{
//get column values comma delimited
DataView dv = grid.ecGetUderlyingDataView();
retVal = dv.ecGetColumnValuesToStringCommaDelimited(grid.Columns[columnName].DataField, rowFilter);
}
else if (rowFilter.Trim().ToLower() == "selectedrows" && (funcName == "Value" || funcName == "Values"))
{
//return column values from selected rows using sql wherein clause
retVal = grid.ecGetColumnValuesFromSelectedRows(grid.Columns[columnName].DataField).ecToStringSqlWhereInClauseFormat(grid.Columns[columnName].DataType.UnderlyingSystemType.Name, "None");
}
else if (funcName.ToLower() == "datevalue")
{
//get datevalue
retVal = grid.Columns[columnName].Value.ToString().ecToDate("{0:g}").ToOADate().ecToStrNoFormat();
}
else if (funcName.ToLower() == "timevalue")
{
//get timevalue
if (grid.Columns[columnName].Value.ToString().Length > 0)
{
if (grid.Columns[columnName].DataType.ToString().ecIsDataTypeDateType())
{
DateTime dt = DateTime.Parse(grid.Columns[columnName].Value.ToString());
retVal = Convert.ToDecimal(dt.TimeOfDay.TotalHours).ecToStrNoFormat();
}
else if (grid.Columns[columnName].DataType.ToString().ecIsDataTypeTimeSpan())
retVal = Convert.ToDecimal(TimeSpan.Parse(grid.Columns[columnName].Value.ToString()).TotalHours).ecToStrNoFormat();
}
else
retVal = "0"; //default value
}
else // for grid.ColumnName.Value
{
if (grid.AddNewMode == AddNewModeEnum.AddNewCurrent && grid.Bookmark == grid.Row)
retVal = ""; //return empty string.
else
{
try
{
if (funcName.ToLower() == "text")
retVal = grid.Columns[columnName].Text;
else if (funcName.ToLower() == "previousvalue" || funcName.ToLower() == "lagvalue")
{
if (grid.Bookmark == 0)
if (grid.ecGetColumnDataType(columnName).ecIsDataTypeNumeric())
retVal = "0";
else
retVal = "";
else
retVal = grid.Columns[columnName].CellValue(grid.Bookmark - 1).ToString();
}
else if (funcName.ToLower() == "nextvalue" || funcName.ToLower() == "leadvalue")
{
if (grid.Bookmark + 1 < grid.RowCount)
retVal = grid.Columns[columnName].CellValue(grid.Bookmark + 1).ToString();
else
if (grid.ecGetColumnDataType(columnName).ecIsDataTypeNumeric())
retVal = "0";
else
retVal = "";
}
else if (funcName.ToLower() == "sumpreviousvalue" || funcName.ToLower() == "sumlagvalue")
{
double sumvalue = 0;
if (grid.Bookmark > 0)
for (int i = 0; i < grid.Bookmark; i++)
sumvalue = sumvalue + grid.Columns[columnName].CellValue(i).ToString().ecToNumber();
retVal = sumvalue.ecToStrNoFormat();
}
else if (funcName.ToLower() == "sumnextvalue" || funcName.ToLower() == "sumleadvalue")
{
double sumvalue = 0;
for (int i = grid.Bookmark + 1; i < grid.RowCount; i++)
sumvalue = sumvalue + grid.Columns[columnName].CellValue(i).ToString().ecToNumber();
retVal = sumvalue.ecToStrNoFormat();
}
else if (funcName.ToLower() == "highestvalue")
{
//grid.Bookmark = grid.Bookmark - 1;
//retVal = grid.Columns[columnName].Value.ToString();
//grid.Bookmark = bookMark; //back to original bookmark
//retVal = grid.ecGetUderlyingDataRow(grid.Bookmark + 1)[columnName].ToString();
retVal = grid.ecGetHighestColumnValue(columnName).ToString();
}
else if (funcName.ToLower() == "lowestvalue")
{
//grid.Bookmark = grid.Bookmark - 1;
//retVal = grid.Columns[columnName].Value.ToString();
//grid.Bookmark = bookMark; //back to original bookmark
//retVal = grid.ecGetUderlyingDataRow(grid.Bookmark + 1)[columnName].ToString();
retVal = grid.ecGetLowestColumnValue(columnName).ToString();
}
else
{
retVal = grid.Columns[columnName].Value.ToString(); //just return the column value
if (retVal.Length > 0 && grid.ecGetColumnDataType(columnName).ecIsDataTypeDateType())
retVal = retVal.ecToShortDate(); //format to short date
}
}
catch
{
if (grid.ecGetColumnIndex(columnName) >= 0)
{
if (grid.ecGetColumnDataType(columnName).ecIsDataTypeNumeric())
retVal = "0"; //default retVal for no record and on adding new row
else
retVal = "";
}
else
retVal = string.Empty;
}
finally
{
grid.Bookmark = bookMark;
}
}
}
}
//return 0 for numeric field instead of empty space
if (retVal.Length == 0) //for numeric field, the value should be 0 if null
if (grid.ecGetColumnIndex(columnName) >= 0)
{
if (grid.ecGetColumnDataType(columnName).ecIsDataTypeNumeric())
retVal = "0";
else
retVal = string.Empty;
}
//get back to the original bookmark
grid.Bookmark = bookMark;
}
catch
{
retVal = string.Empty;
}
//
if (retVal != null && retVal.Length > 0 && formatEnum != null && formatEnum.Length > 0)
retVal = ecFunction.FormatText(retVal, formatEnum);
//
return (T)Convert.ChangeType(retVal, typeof(T));
}
/// <summary>
/// Returns converted formula by evaluating additional formatEnum such as ToLower, ToUpper, ToNumberFormat, Trim, AllTrim, RTrim, TooaDate, etc.
/// </summary>
/// <param name="formula"></param>
/// <param name="formatEnum"></param>
/// <returns></returns>
public static string ecToParserEvaluatedFormatEnum(this string formula, string formatEnum)
{
//for further formatting
string retVal = formula;
if (formatEnum.ToLower() == "tolower") //ToLower
retVal = retVal.ToLower();
if (formatEnum.ToLower() == "toupper") //ToUpper
retVal = retVal.ToUpper();
//
if (formatEnum.ToLower() == "tonumberformat" || formatEnum.ToLower() == "tonumber") //format it to number with command and 2 decimal places
retVal = retVal.ecToNumber().ecToStr();
if (formatEnum.ToLower() == "trim" || formatEnum.ToLower() == "alltrim") //format it to number with command and 2 decimal places
retVal = retVal.Trim();
if (formatEnum.ToLower() == "rtrim") //format it to number with command and 2 decimal places
retVal = retVal.TrimEnd(' ');
if (formatEnum.ToLower() == "ltrim") //format it to number with command and 2 decimal places
retVal = retVal.TrimStart(' ');
//Date Format
if (formatEnum.ToLower() == "toshortdateformat" || formatEnum.ToLower() == "toshortdate")
retVal = retVal.ecToShortDate();
if (formatEnum.ToLower() == "tomonth") //get the Month
retVal = retVal.ecToShortDate().ecLeft(2);
if (formatEnum.ToLower() == "today") //get the Day
retVal = retVal.ecToShortDate().Substring(3, 2);
if (formatEnum.ToLower() == "toyear")
retVal = retVal.ecToShortDate().Substring(6, 4); //get the Year
//convert date to its numeric value similar to excel DateValue function
if (formatEnum.ecLeft(8).ToLower() == "tooadate")
{
//
retVal = retVal.ecToDate("{0:g}").ToOADate().ecToStrNoFormat();
}
//
if (formatEnum.ecLeft(4).ToLower() == "left")
{
//
int i = formatEnum.ecGetNumberFromStr();
if (i > 0)
retVal = retVal.ecLeft(i);
}
if (formatEnum.ecLeft(5).ToLower() == "right")
{
//
int i = formatEnum.ecGetNumberFromStr();
if (i > 0)
retVal = retVal.ecRight(i);
}
if (formatEnum.ecLeft(9).ToLower() == "substring")
{
//
formatEnum = formatEnum.Replace("Substring", "").Replace("(", "").Replace(")", "");
string[] subs = formatEnum.ecToStringArray(',',false,true);
if (subs[1] == null || subs[1].ToString().Length == 0 || subs[1].ecToNumber() > retVal.Length - subs[0].ecToNumber())
retVal = retVal.Substring(subs[0].ecToNumber());
else
retVal = retVal.Substring(subs[0].ecToNumber(), subs[1].ecToNumber());
}
return retVal;
}
|
|
