/// <summary> /// Count the number of rows in this table control. /// This function should be called as <Products>TableControl.COUNT(), not /// as shown here. The COUNT function in the BaseApplicationTableControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The count of the number of rows.</returns> public static int Count(BaseApplicationTableControl tableControl, string ctlName) { try { return tableControl.GetBaseRecordControls().Length; } catch (Exception) { // If there is an error getting the length, we simply fall through and return 0. } return 0; }
/// <summary> /// Count the number of rows in this table control that actually contain /// a decimal value (as opposed to be NULL or invalid value) /// This function should be called as <Products>TableControl.COUNTA("UnitPrice"), not /// as shown here. The COUNTA function in the BaseApplicationTableControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The count of the number of rows.</returns> public static int CountA(BaseApplicationTableControl tableControl, string ctlName) { int totalRows = 0; // Get all of the record controls within this table control. foreach (BaseApplicationRecordControl recCtl in tableControl.GetBaseRecordControls()) { // The control itself may be embedded in sub-panels, so we need to use // FindControlRecursively starting from the recCtl. System.Web.UI.Control ctl = MiscUtils.FindControlRecursively(recCtl, ctlName); if (!(ctl == null)) { // Add the row if this contains a valid number. string val = null; // Get the value from the textbox, label or literal if (ctl is System.Web.UI.WebControls.TextBox) { val = ((System.Web.UI.WebControls.TextBox)ctl).Text; } else if (ctl is System.Web.UI.WebControls.Label) { val = ((System.Web.UI.WebControls.Label)ctl).Text; } else if (ctl is System.Web.UI.WebControls.Literal) { val = ((System.Web.UI.WebControls.Literal)ctl).Text; } try { if (!(val == null) && val.Trim().Length > 0) { totalRows += 1; } } catch (Exception) { // Ignore exception - since this is only returning the // rows that contain a valid value. Other rows with a // NULL value or an invalid value will not be counted. } } } return totalRows; }
/// <summary> /// GetSortedValues is a private function that returns the list of sorted values of /// the given control name. This is used by Rank, Median, Average, Mode, etc. /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>A sorted array of values for the given control. </returns> private static ArrayList GetSortedValues(BaseApplicationTableControl tableControl, string ctlName) { ArrayList rankedArray = new ArrayList(); // Get all of the record controls within this table control. foreach (BaseApplicationRecordControl recCtl in tableControl.GetBaseRecordControls()) { // The control itself may be embedded in sub-panels, so we need to use // FindControlRecursively starting from the recCtl. System.Web.UI.Control ctl = MiscUtils.FindControlRecursively(recCtl, ctlName); if (!(ctl == null) && ctl.Visible) { string textVal = null; decimal val = 0; // Get the value from the textbox, label or literal if (ctl is System.Web.UI.WebControls.TextBox) { textVal = ((System.Web.UI.WebControls.TextBox)ctl).Text; } else if (ctl is System.Web.UI.WebControls.Label) { textVal = ((System.Web.UI.WebControls.Label)ctl).Text; } else if (ctl is System.Web.UI.WebControls.Literal) { textVal = ((System.Web.UI.WebControls.Literal)ctl).Text; } try { // If the value is not a valid number, ignore it. val = StringUtils.ParseDecimal(textVal); rankedArray.Add(val); } catch (Exception) { // Ignore exception. } } } // Sort the array now. rankedArray.Sort(); return rankedArray; }
/// <summary> /// Sum the values of the displayed controls. The ctlName must be /// a textbox, label or literal. /// This function should be called as [Products]TableControl.SUM("UnitPrice"), not /// as shown here. The SUM function in the BaseApplicationTableControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The total of adding the value contained in each of the fields.</returns> public static decimal Sum(BaseApplicationTableControl tableControl, string ctlName) { decimal total = 0; foreach (object item in GetSortedValues(tableControl, ctlName)) { total += StringUtils.ParseDecimal(item); } return total; }
/// <summary> /// Sum the values of the displayed controls. The ctlName must be /// a textbox, label or literal. /// This function should be called as [Products]TableControl.TOTAL("UnitPrice"), not /// as shown here. The TOTAL function in the BaseApplicationTableControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The total of adding the value contained in each of the fields.</returns> public static decimal Total(BaseApplicationTableControl tableControl, string ctlName) { decimal sum = 0; foreach (object item in GetSortedValues(tableControl, ctlName)) { sum += StringUtils.ParseDecimal(item); } return sum; }
/// <summary> /// Return the running total of the control. /// This function should be called as [Products]TableControlRow.RUNNINGTOTAL("UnitPrice"), not /// as shown here. The RUNNINGTOTAL function in the BaseApplicationRecordControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// Say there are 5 rows and they contain 57, 32, 12, 19, 98. /// Their respecitive running totals will be 57, 89, 101, 120, 218 /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="recordControl">The record control whose running total is being determined.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The running total of the recordControl passed in.</returns> public static decimal RunningTotal(BaseApplicationTableControl tableControl, BaseApplicationRecordControl recordControl, string ctlName) { decimal sum = 0; // Get all of the record controls within this table control. foreach (BaseApplicationRecordControl recCtl in tableControl.GetBaseRecordControls()) { System.Web.UI.Control ctl = default(System.Web.UI.Control); // The control itself may be embedded in sub-panels, so we need to use // FindControlRecursively starting from the recCtl. ctl = MiscUtils.FindControlRecursively(recCtl, ctlName); if (!(ctl == null)) { string textVal = null; decimal val = 0; // Get the value from the textbox, label or literal if (ctl is System.Web.UI.WebControls.TextBox) { textVal = ((System.Web.UI.WebControls.TextBox)ctl).Text; } else if (ctl is System.Web.UI.WebControls.Label) { textVal = ((System.Web.UI.WebControls.Label)ctl).Text; } else if (ctl is System.Web.UI.WebControls.Literal) { textVal = ((System.Web.UI.WebControls.Literal)ctl).Text; } try { // If the value is not a valid number, ignore it. val = StringUtils.ParseDecimal(textVal); sum = val + sum; if (object.ReferenceEquals(recCtl, recordControl)) { //Return sum if the row we are looking for is reached return sum; } } catch (Exception) { // Ignore exception. } } } return sum; }
/// <summary> /// Calulates the Average of the values of the displayed controls. The ctlName must be /// a textbox, label or literal. /// We could have implemented this as a call to SUM()/COUNT(), but decided to do it this way /// for efficiency. /// This function should be called as [Products]TableControl.AVERAGE("UnitPrice"), not /// as shown here. The AVERAGE function in the BaseApplicationTableControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The total of adding the value contained in each of the fields.</returns> public static decimal Average(BaseApplicationTableControl tableControl, string ctlName) { return Mean(tableControl, ctlName); }
/// <summary> /// Return the Rank of this control. /// This function should be called as <Products>TableControlRow.RANK("UnitPrice"), not /// as shown here. The RANK function in the BaseApplicationRecordControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// Say there are 5 rows and they contain 57, 32, 12, 19, 98. /// Their respecitive ranks will be 4, 3, 1, 2, 5 /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="recordControl">The record control whose tank is being determined. Rank numbers are 1-based.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The row number of the recordControl passed in. 0 if this is not a correct row number. </returns> public static int Rank(BaseApplicationTableControl tableControl, BaseApplicationRecordControl recordControl, string ctlName) { ArrayList rankedArray = new ArrayList(); decimal lookFor = 0; // Get all of the record controls within this table control. foreach (BaseApplicationRecordControl recCtl in tableControl.GetBaseRecordControls()) { System.Web.UI.Control ctl = default(System.Web.UI.Control); // The control itself may be embedded in sub-panels, so we need to use // FindControlRecursively starting from the recCtl. ctl = MiscUtils.FindControlRecursively(recCtl, ctlName); if (!(ctl == null)) { string textVal = null; decimal val = 0; // Get the value from the textbox, label or literal if (ctl is System.Web.UI.WebControls.TextBox) { textVal = ((System.Web.UI.WebControls.TextBox)ctl).Text; } else if (ctl is System.Web.UI.WebControls.Label) { textVal = ((System.Web.UI.WebControls.Label)ctl).Text; } else if (ctl is System.Web.UI.WebControls.Literal) { textVal = ((System.Web.UI.WebControls.Literal)ctl).Text; } try { // If the value is not a valid number, ignore it. val = StringUtils.ParseDecimal(textVal); rankedArray.Add(val); // Save the value that we need to look for to determine the rank if (object.ReferenceEquals(recCtl, recordControl)) { lookFor = val; } } catch (Exception) { // Ignore exception. } } } // Sort the array now. rankedArray.Sort(); // Rank is always 1 based in our case. So we need to add one to the // location returned by IndexOf return rankedArray.IndexOf(lookFor) + 1; }
/// <summary> /// Return the row number of this record control. /// This function should be called as <Products>TableControlRow.ROWNUM(), not /// as shown here. The ROWNUM function in the BaseApplicationRecordControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="recordControl">The record control whose row number is being determined. Row numbers are 1-based.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The row number of the recordControl passed in. 0 if this is not a correct row number. </returns> public static int RowNum(BaseApplicationTableControl tableControl, BaseApplicationRecordControl recordControl) { int rowNumber = 1; // Get all of the record controls within this table control. foreach (BaseApplicationRecordControl recCtl in tableControl.GetBaseRecordControls()) { if (object.ReferenceEquals(recCtl, recordControl)) { // We found the row. return rowNumber; } rowNumber += 1; } return 0; }
/// <summary> /// Return the Range of this control. /// This function should be called as [Products]TableControl.RANGE("UnitPrice"), not /// as shown here. The RANGE function in the BaseApplicationRecordControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// Say there are 5 rows and they contain 57, 32, 12, 19, 98. /// The lowest is 12, highest is 98, so the range is 98-12 = 86 /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The row number of the recordControl passed in. 0 if this is not a correct row number. </returns> public static decimal Range(BaseApplicationTableControl tableControl, string ctlName) { ArrayList rankedArray = GetSortedValues(tableControl, ctlName); // If there are 0 or 1 elements, then there is no range. if (rankedArray.Count <= 1) return 0; // Take the difference between the largest and the smallest. return StringUtils.ParseDecimal(rankedArray[rankedArray.Count - 1]) - StringUtils.ParseDecimal(rankedArray[0]); }
/// <summary> /// Return the Mode of this control. /// This function should be called as [Products]TableControl.MODE("UnitPrice"), not /// as shown here. The MODE function in the BaseApplicationRecordControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// Say there are 5 rows and they contain 57, 57, 12, 57, 98. /// The Mode is 57 as it is the number which repeats the maximum number of times. /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The row number of the recordControl passed in. 0 if this is not a correct row number. </returns> public static decimal Mode(BaseApplicationTableControl tableControl, string ctlName) { ArrayList rankedArray = GetSortedValues(tableControl, ctlName); decimal num = 0; decimal modeVal = 0; int count = 0; int max = 0; // Because this is a sorted array, we can foreach (object item in rankedArray) { if (num != StringUtils.ParseDecimal(item)) { num = StringUtils.ParseDecimal(item); count = 1; } else { count += 1; } if (count > max) { max = count; modeVal = num; } } return modeVal; }
/// <summary> /// Finds the minimum among the values of the displayed controls. The ctlName must be /// a textbox, label or literal. /// This function should be called as [Products]TableControl.Min("UnitPrice"), not /// as shown here. The MIN function in the BaseApplicationTableControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The minimum among the values contained in each of the fields.</returns> public static decimal Min(BaseApplicationTableControl tableControl, string ctlName) { decimal minDecimal = Decimal.MaxValue; foreach (object item in GetSortedValues(tableControl, ctlName)) { minDecimal = Math.Min(minDecimal, StringUtils.ParseDecimal(item)); } return minDecimal; }
/// <summary> /// Return the Median of this control. /// This function should be called as [Products]TableControl.MEDIAN("UnitPrice"), not /// as shown here. The MEDIAN function in the BaseApplicationRecordControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// Say there are 5 rows and they contain 57, 32, 12, 19, 98. /// The order is 12, 19, 32, 57, 98 - so the Median is 32. /// If the number of numbers is even, the Median is the average of the two middle numbers. /// Say there are 6 rows and they contain 57, 32, 12, 19, 98, 121 /// The order is 12, 19, 32, 57, 98, 121 - so the two numbers in the mid are 32 and 57. /// So the median is (32 + 57) / 2 = 89/2 = 44.5 /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The row number of the recordControl passed in. 0 if this is not a correct row number. </returns> public static decimal Median(BaseApplicationTableControl tableControl, string ctlName) { ArrayList rankedArray = GetSortedValues(tableControl, ctlName); // If there are 0 elements, then there is no median. if (rankedArray.Count == 0) return 0; int halfPoint = (int)Math.Floor((double)rankedArray.Count / 2); decimal medianValue = 0; if (rankedArray.Count % 2 == 0) { medianValue = (StringUtils.ParseDecimal(rankedArray[halfPoint - 1]) + StringUtils.ParseDecimal(rankedArray[halfPoint]) / 2); } else { // Odd numbered items. So medianValue = StringUtils.ParseDecimal(rankedArray[halfPoint]); } return medianValue; }
/// <summary> /// Calulates the Mean (Average) of the values of the displayed controls. The ctlName must be /// a textbox, label or literal. /// We could have implemented this as a call to SUM()/COUNT(), but decided to do it this way /// for efficiency. /// This function should be called as [Products]TableControl.MEAN("UnitPrice"), not /// as shown here. The MEAN function in the BaseApplicationTableControl will call this /// function to actually perform the work - so that we can keep all of the formula /// functions together in one place. /// </summary> /// <param name="tableControl">The table control instance.</param> /// <param name="ctlName">The string name of the UI control (e.g., "UnitPrice") </param> /// <returns>The total of adding the value contained in each of the fields.</returns> public static decimal Mean(BaseApplicationTableControl tableControl, string ctlName) { decimal total = 0; int numRows = 0; // Get all of the record controls within this table control. foreach (BaseApplicationRecordControl recCtl in tableControl.GetBaseRecordControls()) { // The control itself may be embedded in sub-panels, so we need to use // FindControlRecursively starting from the recCtl. System.Web.UI.Control ctl = MiscUtils.FindControlRecursively(recCtl, ctlName); if (!((ctl == null))) { string val = null; // Get the value from the textbox, label or literal if (ctl is System.Web.UI.WebControls.TextBox) { val = ((System.Web.UI.WebControls.TextBox)ctl).Text; } else if (ctl is System.Web.UI.WebControls.Label) { val = ((System.Web.UI.WebControls.Label)ctl).Text; } else if (ctl is System.Web.UI.WebControls.Literal) { val = ((System.Web.UI.WebControls.Literal)ctl).Text; } try { // If the value is not a valid number, ignore it. total += StringUtils.ParseDecimal(val); } catch (Exception) { // Ignore exception. } // Mean is calculated based on the number of rows, NOT on // the number of non-NULL values. So in this way, it is based on // COUNT and not on COUNTA. numRows += 1; } } return (total / numRows); }