public void Execute()
{
// READ 'C:\temp\Sample.mdb':Oswego
// SUMMARIZE FirstVisit::MIN(VisitDate), LastVisit::MAX(VisitDate), NVisit::COUNT() TO PatSumm STRATAVAR=PatientID
//dt = dsHelper.CreateGroupByTable("OrderSummary", ds.Tables["Orders"], "EmployeeID,sum(Amount) Total,min(Amount) Min,max(Amount) Max");
//READ {Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Sample.mdb}:Addicts
//SUMMARIZE MinTime::MIN(Survival_Time_Days), MaxTime::MAX(Survival_Time_Days) TO PatSumm STRATAVAR=clinic
// OutTable Structure Clinic, MinTime, MaxTime
StringBuilder columnList = new StringBuilder();
StringBuilder groupByList = new StringBuilder();
cDataSetHelper dsHelper = new cDataSetHelper();
System.Data.DataTable dt = null;
List<DataRow> DataRows = this.Context.GetDataRows(null);
if (this.StratvarList == null || this.StratvarList.Length == 0)
{
dt = new DataTable(this.OutTable);
object[] a = new object[this.AggregateList.Length];
for (int i = 0; i < this.AggregateList.Length; i++)
{
string s = this.AggregateList[i];
DataColumn c = new DataColumn(s, typeof(double), this.AggregateElement[s]);
DataColumn c2 = new DataColumn(s, typeof(double));
this.Context.Columns.Add(c);
dt.Columns.Add(c2);
a[i] = DataRows[0][c.ColumnName];
}
//dt.Rows.Add(new object[] { "Sam", 5, 25.00 });
dt.Rows.Add(a);
}
else
{
foreach (string s in this.StratvarList)
{
groupByList.Append(s);
groupByList.Append(", ");
}
columnList.Append(groupByList);
groupByList.Length = groupByList.Length - 2;
foreach (string s in this.AggregateList)
{
if(this.AggregateElement.ContainsKey(s))
{
columnList.Append(this.AggregateElement[s]);
columnList.Append(" ");
columnList.Append(s);
columnList.Append(", ");
}
else
{
columnList.Append(s);
columnList.Append(", ");
}
//groupByList.Append(s);
//groupByList.Append(" ");
}
columnList.Length = columnList.Length - 2;
//READ {Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Sample.mdb}:Addicts
//SUMMARIZE MinTime::MIN(Survival_Time_Days), MaxTime::MAX(Survival_Time_Days) TO PatSumm
//System.Data.DataTable dt = dsHelper.CreateGroupByTable(this.OutTable, this.Context.CurrentDataTable, columnList.ToString());
//dsHelper.InsertGroupByInto(dt, this.Context.CurrentDataTable, columnList.ToString(), null, "clinic");
//System.Data.DataTable dt = dsHelper.CreateGroupByTable(this.OutTable, this.Context.CurrentDataTable, "MIN(Survival_Time_Days) MinTime, MAX(Survival_Time_Days) MaxTime");
//dsHelper.InsertGroupByInto(dt, this.Context.CurrentDataTable, "MIN(Survival_Time_Days) MinTime, MAX(Survival_Time_Days) MaxTime", null, "Survival_Time_Days");
/*
READ {Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Sample.mdb}:Oswego
SUMMARIZE Age::Count() TO PatSumm STRATAVAR=Age BakedHam Ill
*/
//"Age, Count(Age) Freq"
dt = dsHelper.CreateGroupByTable(this.OutTable, this.Context.Columns, DataRows, columnList.ToString());
dsHelper.InsertGroupByInto(dt, this.Context.Columns, DataRows, columnList.ToString(), null, groupByList.ToString());
/*
System.Data.DataTable dt = dsHelper.CreateGroupByTable(this.OutTable, this.Context.CurrentDataTable, "Age");
dsHelper.InsertGroupByInto(dt, this.Context.CurrentDataTable, "Age", null, "Age");
dt.Columns.Add("Freq", typeof(double), "Count(Age)");*/
//dt.Columns["ageCount"].Expression = ;
//
/*
READ {Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Sample.mdb}:Oswego
SUMMARIZE Age :: Avg(AGE) TO patsumm STRATAVAR=ILL
SUMMARIZE Age :: Avg(AGE), AgeCount :: Count(Age) TO patsumm STRATAVAR=ILL
*/
}
this.Context.OutTable(dt);
StringBuilder HTMLString = new StringBuilder();
HTMLString.Append("<table><tr>");
foreach (DataColumn c in dt.Columns)
{
//System.Console.WriteLine("{0}\t{1}\t{2}", r[0], r[1], r[2]);
//System.Console.WriteLine("{0}\t{1}\t{2}\t{3}", r[0], r[1], r[2], r[3]);
System.Console.Write("{0}\t", c.ColumnName);
HTMLString.Append("<th>");
HTMLString.Append(c.ColumnName);
HTMLString.Append("</th>");
}
System.Console.Write("\n");
HTMLString.Append("</tr>");
foreach (DataRow r in dt.Rows)
{
HTMLString.Append("<tr>");
foreach (DataColumn c in dt.Columns)
{
//System.Console.WriteLine("{0}\t{1}\t{2}", r[0], r[1], r[2]);
//System.Console.WriteLine("{0}\t{1}\t{2}\t{3}", r[0], r[1], r[2], r[3]);
System.Console.Write("{0}\t", r[c.ColumnName]);
HTMLString.Append("<td>");
HTMLString.Append(r[c.ColumnName]);
HTMLString.Append("</td>");
}
System.Console.Write("\n");
HTMLString.Append("</tr>");
}
HTMLString.Append("</table>");
Dictionary<string,string> config = this.Context.SetProperties;
Dictionary<string, string> args = new Dictionary<string, string>();
args.Add("COMMANDNAME", "SUMMARIZE");
args.Add("COMMANDTEXT", commandText.Trim());
//args.Add("FREQVARIABLE", this.Identifier);
args.Add("HTMLRESULTS", HTMLString.ToString());
this.Context.Display(args);
}
public static DataTable CreateWorkingTable(string pNumericVariable, string pCrossTabVariable, Dictionary<string, string> config, DataTable DT, List<string> pStratavar, List<string> pStrataValue, string pWeightVariable)
{
DataTable result = new DataTable();
result.CaseSensitive = true;
DataTable RowValues = new DataTable();
DataTable ColumnValues = new DataTable();
/*
Produce Table with rows = number of distinct values in main_variable
if no cross _tab_variable then columns = 2
else columns = 2 + number of values in cross_tab_variable
For each strata
get distinct values of the strata
for each distinct value
produce the following table
*/
cDataSetHelper ds = new cDataSetHelper();
ColumnValues = ds.SelectDistinct("ColumnValues", DT, pNumericVariable);
result.Columns.Add(new DataColumn("__Values__", ColumnValues.Columns[pNumericVariable].DataType));
result.Columns.Add(new DataColumn("__Count__", typeof(double)));
if (!string.IsNullOrEmpty(pCrossTabVariable))
{
RowValues = ds.SelectDistinct("RowValues", DT, pCrossTabVariable);
foreach (DataRow R in RowValues.Select("", pCrossTabVariable))
{
if (R[0] == DBNull.Value || string.IsNullOrEmpty(R[0].ToString()))
{
if (config["include-missing"].ToUpper() != "FALSE")
{
DataColumn dataColumn = new DataColumn(config["RepresentationOfMissing"], typeof(double));
dataColumn.ExtendedProperties.Add("Value", R[0]);
bool isFound = false;
for (int i = 0; i < result.Columns.Count; i++)
{
if (dataColumn.ColumnName == result.Columns[i].ColumnName)
{
isFound = true;
break;
}
}
if (!isFound)
{
result.Columns.Add(dataColumn);
}
else
{
}
}
}
else
{
DataColumn dataColumn = new DataColumn(R[0].ToString(), typeof(double));
dataColumn.ExtendedProperties.Add("Value", R[0]);
bool isFound = false;
for (int i = 0; i < result.Columns.Count; i++)
{
if (dataColumn.ColumnName == result.Columns[i].ColumnName)
{
isFound = true;
break;
}
}
if (!isFound)
{
result.Columns.Add(dataColumn);
}
else
{
}
}
}
}
Dictionary<string, DataRow> RowList = new Dictionary<string, DataRow>();
DataRow newRow = null;
// initialize table
foreach (DataRow R in ColumnValues.Select("", "[" + pNumericVariable + "]"))
{
string RowKey = null;
if (R[0] is DateTime)
{
RowKey = ((DateTime)R[0]).ToString("MM/dd/yyyy hh:mm:ss.fff tt");
}
else
{
RowKey = R[0].ToString();
}
if (!RowList.ContainsKey(RowKey))
{
newRow = result.NewRow();
foreach (DataColumn C in result.Columns)
{
if (C.ColumnName.Equals("__Values__"))
{
newRow[C.ColumnName] = R[0];
}
else
{
newRow[C.ColumnName] = 0;
}
}
RowList.Add(RowKey, newRow);
result.Rows.Add(newRow);
}
}
DataRow[] workingRows = null;
if (pStratavar == null || pStratavar.Count == 0 || string.IsNullOrEmpty(pStratavar[0]))
{
workingRows = DT.Select("", "[" + pNumericVariable + "]");
}
else
{
StringBuilder WhereClause = new StringBuilder();
for(int i = 0; i < pStratavar.Count; i++)
{
string CurrentStratavar = pStratavar[i];
string CurrentValue = pStrataValue[i];
DataColumn column = DT.Columns[CurrentStratavar];
switch (column.DataType.Name)
{
case "Byte":
case "Boolean":
case "Double":
case "Float":
case "Integer":
case "Int16":
case "Short":
case "Int32":
case "Int64":
if (CurrentValue.Equals("NULL"))
WhereClause.Append(string.Format("[{0}] IS {1} And ", CurrentStratavar, CurrentValue));
else
WhereClause.Append(string.Format("[{0}] = {1} And ", CurrentStratavar, CurrentValue));
break;
case "String":
default:
WhereClause.Append(string.Format("[{0}] = '{1}' And ", CurrentStratavar, CurrentValue));
break;
}
}
WhereClause.Length = WhereClause.Length - 4;
workingRows = DT.Select(WhereClause.ToString(), "[" + pNumericVariable + "]");
}
if (string.IsNullOrEmpty(pCrossTabVariable))
{
foreach (DataRow R in workingRows)
{
DataRow Dest;
if (R[pNumericVariable] is DateTime)
{
Dest = RowList[((DateTime)R[pNumericVariable]).ToString("MM/dd/yyyy hh:mm:ss.fff tt")];
}
else
{
Dest = RowList[R[pNumericVariable].ToString()];
}
if (string.IsNullOrEmpty(pWeightVariable))
{
Dest["__Count__"] = (double)Dest["__Count__"] + 1;
}
else
{
if (R[pWeightVariable] != DBNull.Value && ((R[pWeightVariable] is String) == false))
{
Dest["__Count__"] = (double)Dest["__Count__"] + Convert.ToDouble(R[pWeightVariable]);
}
else
{
if (config["include-missing"].ToUpper() != "FALSE")
{
Dest["__Count__"] = (double)Dest["__Count__"] + 1;
}
}
}
}
}
else
{
foreach (DataRow R in workingRows)
{
DataRow Dest;
if (R[pNumericVariable] is DateTime)
{
Dest = RowList[((DateTime)R[pNumericVariable]).ToString("MM/dd/yyyy hh:mm:ss.fff tt")];
}
else
{
Dest = RowList[R[pNumericVariable].ToString()];
}
double rowWeightValue = 0;
double rowCrossTabValue = 0;
if (string.IsNullOrEmpty(pCrossTabVariable) == false)
{
if (R[pCrossTabVariable] != System.DBNull.Value && !string.IsNullOrEmpty(R[pCrossTabVariable].ToString()))
{
rowCrossTabValue = (double)Dest[R[pCrossTabVariable].ToString()];
}
else
{
if (config["include-missing"].ToUpper() != "FALSE")
{
rowCrossTabValue = (double) Dest[config["RepresentationOfMissing"]];
}
}
}
if(string.IsNullOrEmpty(pWeightVariable))
{
Dest["__Count__"] = (double)Dest["__Count__"] + 1;
if (R[pCrossTabVariable] != System.DBNull.Value && !string.IsNullOrEmpty(R[pCrossTabVariable].ToString()))
{
Dest[R[pCrossTabVariable].ToString()] = rowCrossTabValue + 1;
}
else
{
if (config["include-missing"].ToUpper() != "FALSE")
{
Dest[config["RepresentationOfMissing"]] = rowCrossTabValue + 1;
}
}
}
else
{
if (R[pWeightVariable] != System.DBNull.Value && !string.IsNullOrEmpty(R[pWeightVariable].ToString()))
{
rowWeightValue = Convert.ToDouble(R[pWeightVariable]);
Dest["__Count__"] = (double)Dest["__Count__"] + rowWeightValue;
}
else
{
if (config["include-missing"].ToUpper() != "FALSE")
{
Dest["__Count__"] = (double)Dest["__Count__"] + rowWeightValue;
}
}
if (R[pCrossTabVariable] != System.DBNull.Value && !string.IsNullOrEmpty(R[pCrossTabVariable].ToString()))
{
if (R[pNumericVariable] is DateTime)
{
Dest[((DateTime)R[pNumericVariable]).ToString("MM/dd/yyyy hh:mm:ss.fff tt")] = rowCrossTabValue + rowWeightValue;
}
else
{
Dest[R[pCrossTabVariable].ToString()] = rowCrossTabValue + rowWeightValue;
}
}
else
{
if (config["include-missing"].ToUpper() != "FALSE")
{
Dest[config["RepresentationOfMissing"]] = rowCrossTabValue + 1;
}
}
}
}
}
return result;
}
public void Execute()
{
StringBuilder columnList = new StringBuilder();
StringBuilder groupByList = new StringBuilder();
cDataSetHelper dsHelper = new cDataSetHelper();
System.Data.DataTable dataTable = null;
if (stratvarList == null || stratvarList.Length == 0)
{
dataTable = new DataTable(outTableName);
object[] a = new object[aggregateList.Length];
foreach (string aggregate in aggregateList)
{
if (aggregateElement.ContainsKey(aggregate))
{
columnList.Append(aggregateElement[aggregate]);
columnList.Append(" ");
columnList.Append(aggregate);
columnList.Append(", ");
}
else
{
columnList.Append(aggregate);
columnList.Append(", ");
}
}
columnList.Length = columnList.Length - 2;
dataTable = dsHelper.CreateGroupByTable(
outTableName,
Context.Columns,
Context.GetDataRows(null),
columnList.ToString()
);
dsHelper.InsertGroupByInto(
dataTable,
Context.Columns,
Context.GetDataRows(participatingVariableList),
columnList.ToString(),
null,
groupByList.ToString());
}
else
{
participatingVariableList.AddRange(stratvarList);
foreach (string stratvar in stratvarList)
{
groupByList.Append(stratvar);
groupByList.Append(", ");
}
columnList.Append(groupByList);
groupByList.Length = groupByList.Length - 2;
foreach (string aggregate in aggregateList)
{
if (aggregateElement.ContainsKey(aggregate))
{
columnList.Append(aggregateElement[aggregate]);
columnList.Append(" ");
columnList.Append(aggregate);
columnList.Append(", ");
}
else
{
columnList.Append(aggregate);
columnList.Append(", ");
}
}
columnList.Length = columnList.Length - 2;
dataTable = dsHelper.CreateGroupByTable(
outTableName,
Context.Columns,
Context.GetDataRows(null),
columnList.ToString()
);
dsHelper.InsertGroupByInto(
dataTable,
Context.Columns,
Context.GetDataRows(participatingVariableList),
columnList.ToString(),
null,
groupByList.ToString());
}
Context.OutTable(dataTable);
StringBuilder HTMLString = new StringBuilder();
HTMLString.Append("<table><tr>");
foreach (DataColumn c in dataTable.Columns)
{
//System.Console.WriteLine("{0}\t{1}\t{2}", r[0], r[1], r[2]);
//System.Console.WriteLine("{0}\t{1}\t{2}\t{3}", r[0], r[1], r[2], r[3]);
System.Console.Write("{0}\t", c.ColumnName);
HTMLString.Append("<th>");
HTMLString.Append(c.ColumnName);
HTMLString.Append("</th>");
}
System.Console.Write("\n");
HTMLString.Append("</tr>");
foreach (DataRow r in dataTable.Rows)
{
HTMLString.Append("<tr>");
foreach (DataColumn c in dataTable.Columns)
{
//System.Console.WriteLine("{0}\t{1}\t{2}", r[0], r[1], r[2]);
//System.Console.WriteLine("{0}\t{1}\t{2}\t{3}", r[0], r[1], r[2], r[3]);
System.Console.Write("{0}\t", r[c.ColumnName]);
HTMLString.Append("<td>");
HTMLString.Append(r[c.ColumnName]);
HTMLString.Append("</td>");
}
System.Console.Write("\n");
HTMLString.Append("</tr>");
}
HTMLString.Append("</table>");
Dictionary<string, string> config = Context.SetProperties;
Dictionary<string, string> args = new Dictionary<string, string>();
args.Add("COMMANDNAME", "SUMMARIZE");
args.Add("COMMANDTEXT", commandText.Trim());
args.Add("HTMLRESULTS", HTMLString.ToString());
Context.Display(args);
}
public void Execute()
{
_regressionTable = new DataTable();
_regressionTable.Columns.Add("SeriesName", typeof(string));
_regressionTable.Columns.Add("Predictor", typeof(object));
_regressionTable.Columns.Add("Response", typeof(double));
Dictionary <string, string> config = Context.SetProperties;
StringBuilder HTMLString = new StringBuilder();
System.Data.DataTable sourceTable = BuildTempContextTable();
cDataSetHelper dsHelper = new cDataSetHelper();
Dictionary <string, string> args = new Dictionary <string, string>();
string strataVarValue = string.Empty;
string objectElement = string.Empty;
SilverlightMarkup silverlight = new SilverlightMarkup();
DataTable Strata_ValueList;
if (string.IsNullOrEmpty(_strataVar))
{
Strata_ValueList = sourceTable.Clone();
Strata_ValueList.Rows.Add(Strata_ValueList.NewRow());
}
else
{
Strata_ValueList = dsHelper.SelectDistinct("", sourceTable, _strataVar);
}
FilterStruct filter = new FilterStruct();
filter.strataVarName = _strataVar;
_independentValueTypesSame = true;
if (_independentVariableArray.Length > 1)
{
for (int i = 0; (i + 1) < _independentVariableArray.Length; i++)
{
if (sourceTable.Columns[_independentVariableArray[i]].DataType != sourceTable.Columns[_independentVariableArray[i + 1]].DataType)
{
_independentValueTypesSame = false;
break;
}
}
}
_independentValuesAllBool = true;
for (int i = 0; i < _independentVariableArray.Length; i++)
{
string indepVar = _independentVariableArray[i];
if (sourceTable.Columns.Contains(indepVar))
{
if (sourceTable.Columns[indepVar].DataType.Name != "Byte")
{
_independentValuesAllBool = false;
break;
}
}
else
{
return;
}
}
foreach (DataRow strataRow in Strata_ValueList.Rows)
{
if (string.IsNullOrEmpty(_strataVar) || strataRow[_strataVar] == DBNull.Value)
{
filter.strataVarValue = "null";
}
else
{
filter.strataVarValue = strataRow[_strataVar].ToString();
}
if (_graphType == SilverlightStatics.Scatter)
{
if (_independentVariableArray.Length < 2)
{
throw new Exception("Scatter graphs must contain two (2) main variables.");
}
string categoryName = string.Empty;
Dictionary <object, double> indDepValuePairCollection = new Dictionary <object, double>();
List <string> StrataVarNameList = new List <string>();
List <string> StrataVarValueList = new List <string>();
string regressor = _independentVariableArray[0];
string regressand = _independentVariableArray[1];
StrataVarNameList.Add(filter.strataVarName);
StrataVarValueList.Add(filter.strataVarValue);
DataTable table = new DataTable();
table.Columns.Add(new DataColumn(sourceTable.Columns[regressor].ColumnName, sourceTable.Columns[regressor].DataType));
table.Columns.Add(new DataColumn(sourceTable.Columns[regressand].ColumnName, sourceTable.Columns[regressand].DataType));
foreach (DataRow row in sourceTable.Rows)
{
table.Rows.Add(row[regressor], row[regressand]);
}
foreach (DataRow row in table.Rows)
{
if (row[regressor] != DBNull.Value)
{
string seriesName = string.Empty;
object independentValue = new object();
if (_independentValuesAllBool)
{
independentValue = regressor;
byte value = (byte)(row[regressor]);
}
else
{
independentValue = GetValue(regressor, row, config);
}
seriesName = string.Format("{0} x {1}", sourceTable.Columns[regressor].ColumnName, sourceTable.Columns[regressand].ColumnName);
if (string.IsNullOrEmpty(_weightVar))
{
double dependentVal;
if (double.TryParse(row[regressand].ToString(), out dependentVal))
{
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
else
{
filter.independentVarValue = row[regressor];
filter.independentVarName = regressor;
filter.weightVarName = _weightVar;
double dependentVal = GetAggregateValue(sourceTable, filter);
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
}
}
else if (sourceTable.Columns.Contains(_graphCrossTab))
{
string categoryName = string.Empty;
Dictionary <object, double> indDepValuePairCollection = new Dictionary <object, double>();
List <string> StrataVarNameList = new List <string>();
List <string> StrataVarValueList = new List <string>();
foreach (string independentVariableName in _independentVariableArray)
{
StrataVarNameList.Add(filter.strataVarName);
StrataVarValueList.Add(filter.strataVarValue);
DataTable workingTable = cWorkingTable.CreateWorkingTable(
independentVariableName,
_graphCrossTab,
config,
sourceTable,
StrataVarNameList,
StrataVarValueList,
_weightVar);
foreach (DataColumn crossTabCandidate in workingTable.Columns)
{
if (crossTabCandidate.ColumnName != "__Values__" && crossTabCandidate.ColumnName != "__Count__")
{
Type crossTabDataType = sourceTable.Columns[_graphCrossTab].DataType;
string crossTabValue = crossTabCandidate.ColumnName;
if (crossTabDataType.Name == "Byte")
{
if (crossTabCandidate.ColumnName == "0")
{
crossTabValue = config["RepresentationOfNo"];
}
else if (crossTabCandidate.ColumnName == "1")
{
crossTabValue = config["RepresentationOfYes"];
}
}
string seriesName = string.Format("{0}={1}", _graphCrossTab, crossTabValue);
foreach (DataRow row in workingTable.Rows)
{
double dependentVal;
if (double.TryParse(row[crossTabCandidate.ColumnName].ToString(), out dependentVal))
{
if (row["__Values__"] != DBNull.Value)
{
string independentVariableTypeName = string.Empty;
if (sourceTable.Columns.Contains(independentVariableName))
{
independentVariableTypeName = sourceTable.Columns[independentVariableName].DataType.Name;
}
categoryName = BuildCategoryName(independentVariableName, independentVariableTypeName, row, config);
object independentValue = row["__Values__"];
if (string.IsNullOrEmpty(_weightVar))
{
if (double.TryParse(row[crossTabCandidate.ColumnName].ToString(), out dependentVal))
{
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
else
{
object independentVariableValue = row["__Values__"];
bool isValue = false;
if (independentVariableValue != null)
{
isValue = true;
if (independentVariableValue is string)
{
string candidate = ((string)independentVariableValue).Trim();
isValue = candidate != string.Empty;
}
}
if (isValue)
{
filter.crossTabName = _graphCrossTab;
filter.crossTabValue = crossTabCandidate.ColumnName;
filter.independentVarName = independentVariableName;
filter.independentVarValue = independentVariableValue;
filter.weightVarName = _weightVar;
dependentVal = GetAggregateValue(sourceTable, filter);
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
}
}
}
}
}
}
}
else if (_graphType == "EPICURVE" && _graphIntervalUnits != "")
{
string categoryName = string.Empty;
Dictionary <object, double> indDepValuePairCollection = new Dictionary <object, double>();
List <string> StrataVarNameList = new List <string>();
List <string> StrataVarValueList = new List <string>();
foreach (string independentVariable in _independentVariableArray)
{
StrataVarNameList.Add(filter.strataVarName);
StrataVarValueList.Add(filter.strataVarValue);
DataTable workingTable = cWorkingTable.CreateWorkingTable(
independentVariable,
"",
config,
sourceTable,
StrataVarNameList,
StrataVarValueList,
_weightVar);
DateTime intervalStart = DateTime.MinValue;
DateTime intervalEnd = DateTime.MinValue;
DateTime dateTimeValue = DateTime.MinValue;
intervalStart = _graphStartFrom;
double givenInterval = 1;
int days = 0, hours = 0, minutes = 0, seconds = 0;
TimeSpan period = new TimeSpan(days, hours, minutes, seconds);
if (_graphInterval != "")
{
double.TryParse(_graphInterval, out givenInterval);
}
if (_graphIntervalUnits == "")
{
_graphIntervalUnits = "Hours";
}
foreach (DataRow row in workingTable.Rows)
{
if (row["__Values__"] != DBNull.Value)
{
dateTimeValue = (DateTime)row["__Values__"];
//if (intervalStart == DateTime.MinValue)
//{
// intervalStart = dateTimeValue;
// _graphStartFrom = dateTimeValue;
//}
while (dateTimeValue >= intervalEnd)
{
if (intervalEnd != DateTime.MinValue)
{
intervalStart = intervalEnd;
}
switch (_graphIntervalUnits)
{
case "Years":
intervalEnd = intervalStart.AddYears((int)givenInterval);
break;
case "Quarters":
intervalEnd = intervalStart.AddDays(givenInterval * 365.25 / 4.0);
break;
case "Weeks":
intervalEnd = intervalStart.AddDays(givenInterval * 7);
break;
case "Days":
intervalEnd = intervalStart.AddDays(givenInterval);
break;
case "Hours":
intervalEnd = intervalStart.AddHours(givenInterval);
break;
case "Minutes":
intervalEnd = intervalStart.AddMinutes(givenInterval);
break;
case "Seconds":
intervalEnd = intervalStart.AddSeconds(givenInterval);
break;
}
}
string seriesName = string.Empty;
object independentValue = new object();
independentValue = BuildIndependentValue(independentVariable, row, config);
if (string.IsNullOrEmpty(seriesName))
{
seriesName = SilverlightStatics.COUNT;
if (_independentVariableArray.Length > 1)
{
seriesName = independentVariable;
}
if (string.IsNullOrEmpty(_aggregateFunction) == false)
{
seriesName = _aggregateFunction;
}
}
if (string.IsNullOrEmpty(_weightVar))
{
double dependentVal;
if (double.TryParse(row["__Count__"].ToString(), out dependentVal))
{
if ((dateTimeValue >= intervalStart) && (dateTimeValue < intervalEnd))
{
string expression = "Predictor = #" + intervalStart.ToString() + "#";
DataRow[] foundRows = _regressionTable.Select(expression);
if (foundRows.Length == 0)
{
_regressionTable.Rows.Add(seriesName, intervalStart, dependentVal);
}
else
{
foundRows[0]["Response"] = (double)foundRows[0]["Response"] + dependentVal;
_regressionTable.AcceptChanges();
}
}
}
}
else
{
filter.independentVarValue = row["__Values__"];
filter.independentVarName = independentVariable;
filter.weightVarName = _weightVar;
double dependentVal = GetAggregateValue(sourceTable, filter);
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
}
}
}
else
{
string categoryName = string.Empty;
Dictionary <object, double> indDepValuePairCollection = new Dictionary <object, double>();
List <string> StrataVarNameList = new List <string>();
List <string> StrataVarValueList = new List <string>();
foreach (string independentVariable in _independentVariableArray)
{
StrataVarNameList.Add(filter.strataVarName);
StrataVarValueList.Add(filter.strataVarValue);
DataTable workingTable = cWorkingTable.CreateWorkingTable(
independentVariable,
"",
config,
sourceTable,
StrataVarNameList,
StrataVarValueList,
_weightVar);
foreach (DataRow row in workingTable.Rows)
{
if (row["__Values__"] != DBNull.Value)
{
string seriesName = string.Empty;
object independentValue = new object();
if (!_independentValueTypesSame || _graphType == SilverlightStatics.Pie)
{
independentValue = BuildIndependentValue(independentVariable, row, config);
}
else if (_independentValuesAllBool)
{
independentValue = independentVariable;
byte value = (byte)(row["__Values__"]);
seriesName = row["__Values__"].ToString();
if (value == 0)
{
seriesName = config["RepresentationOfNo"];
}
else if (value == 1)
{
seriesName = config["RepresentationOfYes"];
}
}
else
{
independentValue = BuildIndependentValue(independentVariable, row, config);
}
if (string.IsNullOrEmpty(seriesName))
{
seriesName = SilverlightStatics.COUNT;
if (_independentVariableArray.Length > 1)
{
seriesName = independentVariable;
}
if (string.IsNullOrEmpty(_aggregateFunction) == false)
{
seriesName = _aggregateFunction;
}
}
if (string.IsNullOrEmpty(_weightVar))
{
//
if (independentValue.ToString().ToUpperInvariant() == "TRUE")
{
independentValue = config["RepresentationOfYes"];
}
if (independentValue.ToString().ToUpperInvariant() == "FALSE")
{
independentValue = config["RepresentationOfNo"];
}
//
double dependentVal;
if (double.TryParse(row["__Count__"].ToString(), out dependentVal))
{
if ((_graphType == "EPICURVE" && independentValue is DateTime && ((DateTime)independentValue) <= _graphStartFrom) == false)
{
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
}
else
{
//
if (independentValue.ToString().ToUpperInvariant() == "TRUE")
{
independentValue = config["RepresentationOfYes"];
}
if (independentValue.ToString().ToUpperInvariant() == "FALSE")
{
independentValue = config["RepresentationOfNo"];
}
//
filter.independentVarValue = row["__Values__"];
filter.independentVarName = independentVariable;
filter.weightVarName = _weightVar;
double dependentVal = GetAggregateValue(sourceTable, filter);
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
}
}
}
string graphTitle = _graphTitle;
if (sourceTable.Columns.Contains(filter.strataVarName))
{
Type _strataVarDataType = sourceTable.Columns[filter.strataVarName].DataType;
string strataVarText = filter.strataVarValue;
strataVarText = GetPrintValue(filter.strataVarName, _strataVarDataType.Name, filter.strataVarValue, config);
if (string.IsNullOrEmpty(_strataVar) == false)
{
graphTitle = string.Format("{0} {1}={2}", _graphTitle, filter.strataVarName, strataVarText);
}
}
DataView view = new DataView(_regressionTable);
DataTable distinctValues = new DataTable();
distinctValues = view.ToTable(true, "SeriesName");
bool hideLegend = false;
if (distinctValues.Rows.Count == 1 && distinctValues.Rows[0][0].ToString() == "COUNT")
{
hideLegend = true;
}
objectElement = objectElement
+ silverlight.Graph
(
_graphType,
graphTitle,
_graphIndependentAxisLabel,
_graphDependentAxisLabel,
"",
"",
_graphInterval,
_graphIntervalUnits,
_graphStartFrom,
_regressionTable,
hideLegend
);
_regressionTable.Rows.Clear();
}
string data = string.Empty;
if (objectElement != "")
{
data = objectElement + @"<hr/>";
}
else
{
data = SharedStrings.UNABLE_CREATE_GRAPH;
}
args.Add("COMMANDNAME", CommandNames.GRAPH);
args.Add("DATA", data);
args.Add("COMMANDTEXT", _commandText.Trim());
Context.Display(args);
}
public void Execute()
{
StringBuilder columnList = new StringBuilder();
StringBuilder groupByList = new StringBuilder();
cDataSetHelper dsHelper = new cDataSetHelper();
System.Data.DataTable dataTable = null;
if (stratvarList == null || stratvarList.Length == 0)
{
dataTable = new DataTable(outTableName);
object[] a = new object[aggregateList.Length];
foreach (string aggregate in aggregateList)
{
if (aggregateElement.ContainsKey(aggregate))
{
columnList.Append(aggregateElement[aggregate]);
columnList.Append(" ");
columnList.Append(aggregate);
columnList.Append(", ");
}
else
{
columnList.Append(aggregate);
columnList.Append(", ");
}
}
columnList.Length = columnList.Length - 2;
dataTable = dsHelper.CreateGroupByTable(
outTableName,
Context.Columns,
Context.GetDataRows(null),
columnList.ToString(),
true
);
dsHelper.InsertGroupByInto(
dataTable,
Context.Columns,
Context.GetDataRows(participatingVariableList),
columnList.ToString(),
null,
groupByList.ToString());
// Dec. 9, 2016
// Replace the values in the dataTable.Rows created by the above method,
// which limits the working dataset to rows where all participating variables
// are non-missing, with values from a dataset that does not impose this limit.
for (int e = 0; e < participatingVariableList.Count; e++)
{
// Compute the summary statistics one at a time.
// Working dataset is limited to rows with non-missing values
// for just the one variable being summarized
List <string> subList = new List <string>();
subList.Add(participatingVariableList[e]);
dsHelper.InsertGroupByInto(
dataTable,
Context.Columns,
Context.GetDataRows(subList),
columnList.ToString().Split(',')[e],
null,
groupByList.ToString());
dataTable.Rows[0][e] = dataTable.Rows[dataTable.Rows.Count - 1][e];
dataTable.Rows.RemoveAt(dataTable.Rows.Count - 1);
}
// End of Dec. 9, 2016 change.
}
else
{
participatingVariableList.AddRange(stratvarList);
foreach (string stratvar in stratvarList)
{
groupByList.Append(stratvar);
groupByList.Append(", ");
}
columnList.Append(groupByList);
groupByList.Length = groupByList.Length - 2;
foreach (string aggregate in aggregateList)
{
if (aggregateElement.ContainsKey(aggregate))
{
columnList.Append(aggregateElement[aggregate]);
columnList.Append(" ");
columnList.Append(aggregate);
columnList.Append(", ");
}
else
{
columnList.Append(aggregate);
columnList.Append(", ");
}
}
columnList.Length = columnList.Length - 2;
dataTable = dsHelper.CreateGroupByTable(
outTableName,
Context.Columns,
Context.GetDataRows(null),
columnList.ToString(),
true
);
// Dec. 9, 2016
// Replace the values in the dataTable.Rows created by the above method,
// which limits the working dataset to rows where all participating variables
// are non-missing, with values from a dataset that does not impose this limit.
// First, allow missing values for the by-variables to be included
List <string> participatingVariablesMinusStrataVariablesList = new List <string>();
for (int e = 0; e < participatingVariableList.Count - stratvarList.Length; e++)
{
participatingVariablesMinusStrataVariablesList.Add(participatingVariableList[e]);
}
dsHelper.InsertGroupByInto(
dataTable,
Context.Columns,
Context.GetDataRows(participatingVariablesMinusStrataVariablesList),
columnList.ToString(),
null,
groupByList.ToString());
// Start a column list string with the by-variables
int numberOfRows = dataTable.Rows.Count;
string[] columnArray = columnList.ToString().Split(',');
string newColumnList = columnArray[0];
for (int e = 1; e < stratvarList.Length; e++)
{
newColumnList = newColumnList + ", " + columnArray[e];
}
for (int e = 0; e < participatingVariableList.Count - stratvarList.Length; e++)
{
// Similar process to non-stratified section but with more than one row per variable
List <string> subList = new List <string>();
subList.Add(participatingVariableList[e]);
dsHelper.InsertGroupByInto(
dataTable,
Context.Columns,
Context.GetDataRows(subList),
newColumnList + ", " + columnList.ToString().Split(',')[e + stratvarList.Length],
null,
groupByList.ToString());
for (int z = numberOfRows - 1; z >= 0; z--)
{
dataTable.Rows[z][e + stratvarList.Length] = dataTable.Rows[z + numberOfRows][e + stratvarList.Length];
dataTable.Rows.RemoveAt(z + numberOfRows);
}
}
// End of Dec. 9, 2016 change.
}
Context.OutTable(dataTable);
StringBuilder HTMLString = new StringBuilder();
HTMLString.Append("<table><tr>");
foreach (DataColumn c in dataTable.Columns)
{
//System.Console.WriteLine("{0}\t{1}\t{2}", r[0], r[1], r[2]);
//System.Console.WriteLine("{0}\t{1}\t{2}\t{3}", r[0], r[1], r[2], r[3]);
System.Console.Write("{0}\t", c.ColumnName);
HTMLString.Append("<th>");
HTMLString.Append(c.ColumnName);
HTMLString.Append("</th>");
}
System.Console.Write("\n");
HTMLString.Append("</tr>");
foreach (DataRow r in dataTable.Rows)
{
HTMLString.Append("<tr>");
foreach (DataColumn c in dataTable.Columns)
{
//System.Console.WriteLine("{0}\t{1}\t{2}", r[0], r[1], r[2]);
//System.Console.WriteLine("{0}\t{1}\t{2}\t{3}", r[0], r[1], r[2], r[3]);
System.Console.Write("{0}\t", r[c.ColumnName]);
HTMLString.Append("<td>");
HTMLString.Append(r[c.ColumnName]);
HTMLString.Append("</td>");
}
System.Console.Write("\n");
HTMLString.Append("</tr>");
}
HTMLString.Append("</table>");
Dictionary <string, string> config = Context.SetProperties;
Dictionary <string, string> args = new Dictionary <string, string>();
args.Add("COMMANDNAME", "SUMMARIZE");
args.Add("COMMANDTEXT", commandText.Trim());
args.Add("HTMLRESULTS", HTMLString.ToString());
Context.Display(args);
}
public void Execute()
{
// READ 'C:\temp\Sample.mdb':Oswego
// SUMMARIZE FirstVisit::MIN(VisitDate), LastVisit::MAX(VisitDate), NVisit::COUNT() TO PatSumm STRATAVAR=PatientID
//dt = dsHelper.CreateGroupByTable("OrderSummary", ds.Tables["Orders"], "EmployeeID,sum(Amount) Total,min(Amount) Min,max(Amount) Max");
//READ {Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Sample.mdb}:Addicts
//SUMMARIZE MinTime::MIN(Survival_Time_Days), MaxTime::MAX(Survival_Time_Days) TO PatSumm STRATAVAR=clinic
// OutTable Structure Clinic, MinTime, MaxTime
StringBuilder columnList = new StringBuilder();
StringBuilder groupByList = new StringBuilder();
cDataSetHelper dsHelper = new cDataSetHelper();
System.Data.DataTable dt = null;
List <DataRow> DataRows = this.Context.GetDataRows(null);
if (this.StratvarList == null || this.StratvarList.Length == 0)
{
dt = new DataTable(this.OutTable);
object[] a = new object[this.AggregateList.Length];
for (int i = 0; i < this.AggregateList.Length; i++)
{
string s = this.AggregateList[i];
DataColumn c = new DataColumn(s, typeof(double), this.AggregateElement[s]);
DataColumn c2 = new DataColumn(s, typeof(double));
this.Context.Columns.Add(c);
dt.Columns.Add(c2);
a[i] = DataRows[0][c.ColumnName];
}
//dt.Rows.Add(new object[] { "Sam", 5, 25.00 });
dt.Rows.Add(a);
}
else
{
foreach (string s in this.StratvarList)
{
groupByList.Append(s);
groupByList.Append(", ");
}
columnList.Append(groupByList);
groupByList.Length = groupByList.Length - 2;
foreach (string s in this.AggregateList)
{
if (this.AggregateElement.ContainsKey(s))
{
columnList.Append(this.AggregateElement[s]);
columnList.Append(" ");
columnList.Append(s);
columnList.Append(", ");
}
else
{
columnList.Append(s);
columnList.Append(", ");
}
//groupByList.Append(s);
//groupByList.Append(" ");
}
columnList.Length = columnList.Length - 2;
//READ {Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Sample.mdb}:Addicts
//SUMMARIZE MinTime::MIN(Survival_Time_Days), MaxTime::MAX(Survival_Time_Days) TO PatSumm
//System.Data.DataTable dt = dsHelper.CreateGroupByTable(this.OutTable, this.Context.CurrentDataTable, columnList.ToString());
//dsHelper.InsertGroupByInto(dt, this.Context.CurrentDataTable, columnList.ToString(), null, "clinic");
//System.Data.DataTable dt = dsHelper.CreateGroupByTable(this.OutTable, this.Context.CurrentDataTable, "MIN(Survival_Time_Days) MinTime, MAX(Survival_Time_Days) MaxTime");
//dsHelper.InsertGroupByInto(dt, this.Context.CurrentDataTable, "MIN(Survival_Time_Days) MinTime, MAX(Survival_Time_Days) MaxTime", null, "Survival_Time_Days");
/*
* READ {Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Sample.mdb}:Oswego
* SUMMARIZE Age::Count() TO PatSumm STRATAVAR=Age BakedHam Ill
*/
//"Age, Count(Age) Freq"
dt = dsHelper.CreateGroupByTable(this.OutTable, this.Context.Columns, DataRows, columnList.ToString());
dsHelper.InsertGroupByInto(dt, this.Context.Columns, DataRows, columnList.ToString(), null, groupByList.ToString());
/*
* System.Data.DataTable dt = dsHelper.CreateGroupByTable(this.OutTable, this.Context.CurrentDataTable, "Age");
* dsHelper.InsertGroupByInto(dt, this.Context.CurrentDataTable, "Age", null, "Age");
*
* dt.Columns.Add("Freq", typeof(double), "Count(Age)");*/
//dt.Columns["ageCount"].Expression = ;
//
/*
* READ {Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Sample.mdb}:Oswego
* SUMMARIZE Age :: Avg(AGE) TO patsumm STRATAVAR=ILL
*
*
*
* SUMMARIZE Age :: Avg(AGE), AgeCount :: Count(Age) TO patsumm STRATAVAR=ILL
*/
}
this.Context.OutTable(dt);
StringBuilder HTMLString = new StringBuilder();
HTMLString.Append("<table><tr>");
foreach (DataColumn c in dt.Columns)
{
//System.Console.WriteLine("{0}\t{1}\t{2}", r[0], r[1], r[2]);
//System.Console.WriteLine("{0}\t{1}\t{2}\t{3}", r[0], r[1], r[2], r[3]);
System.Console.Write("{0}\t", c.ColumnName);
HTMLString.Append("<th>");
HTMLString.Append(c.ColumnName);
HTMLString.Append("</th>");
}
System.Console.Write("\n");
HTMLString.Append("</tr>");
foreach (DataRow r in dt.Rows)
{
HTMLString.Append("<tr>");
foreach (DataColumn c in dt.Columns)
{
//System.Console.WriteLine("{0}\t{1}\t{2}", r[0], r[1], r[2]);
//System.Console.WriteLine("{0}\t{1}\t{2}\t{3}", r[0], r[1], r[2], r[3]);
System.Console.Write("{0}\t", r[c.ColumnName]);
HTMLString.Append("<td>");
HTMLString.Append(r[c.ColumnName]);
HTMLString.Append("</td>");
}
System.Console.Write("\n");
HTMLString.Append("</tr>");
}
HTMLString.Append("</table>");
Dictionary <string, string> config = this.Context.SetProperties;
Dictionary <string, string> args = new Dictionary <string, string>();
args.Add("COMMANDNAME", "SUMMARIZE");
args.Add("COMMANDTEXT", commandText.Trim());
//args.Add("FREQVARIABLE", this.Identifier);
args.Add("HTMLRESULTS", HTMLString.ToString());
this.Context.Display(args);
}
/// <summary>
/// performs execution of the MEANS command
/// </summary>
/// <returns>object</returns>
public void Execute()
{
/*
Produce Table with rows = number of distinct values in main_variable
if no cross _tab_variable then columns = 2
else columns = 2 + number of values in cross_tab_variable
For each strata
get distinct values of the strata
for each distinct value
produce the following table
*/
Dictionary<string, string> config = this.Context.SetProperties;
StringBuilder HTMLString = new StringBuilder();
System.Data.DataTable DT = new DataTable();
foreach (DataColumn column in this.Context.Columns)
{
DataColumn newColumn = new DataColumn(column.ColumnName);
newColumn.DataType = column.DataType;
DT.Columns.Add(newColumn);
}
List<string> VariableList = new List<string>();
VariableList.Add(this.Numeric_Variable);
foreach (DataRow row in this.Context.GetDataRows(VariableList))
{
DT.ImportRow(row);
}
string StrataVarName = null;
string StrataVar_Value = null;
List<string> StrataVarNameList = new List<string>();
List<string> StrataVarValueList = new List<string>();
cDataSetHelper ds = new cDataSetHelper();
if(this.StratvarList != null && this.StratvarList.Length > 0)
{
DataTable[] Strata_ValueList = new DataTable[this.StratvarList.Length];
foreach (string s in this.StratvarList)
{
StrataVarNameList.Add(s);
StrataVarName = s;
}
for (int ItemIndex = 0; ItemIndex < this.StratvarList.Length; ItemIndex++)
{
Strata_ValueList[ItemIndex] = ds.SelectDistinct("Stratavalue", DT, StrataVarNameList[ItemIndex]);
}
for (int[] CurrentIndexes = new int[Strata_ValueList.Length]; isMoreLeft(Strata_ValueList, CurrentIndexes); IncrementArray(Strata_ValueList, CurrentIndexes))
{
StrataVarValueList.Clear();
for (int CurrentIndex2 = 0; CurrentIndex2 < CurrentIndexes.Length; CurrentIndex2++)
{
if (Strata_ValueList[CurrentIndex2].Rows[CurrentIndexes[CurrentIndex2]][StrataVarNameList[CurrentIndex2]] == DBNull.Value)
{
StrataVar_Value = "NULL";
}
else
{
StrataVar_Value = Strata_ValueList[CurrentIndex2].Rows[CurrentIndexes[CurrentIndex2]][StrataVarNameList[CurrentIndex2]].ToString();
}
StrataVarValueList.Add(StrataVar_Value);
}
DataTable workingTable = cWorkingTable.CreateWorkingTable(this.Numeric_Variable, this.Cross_Tabulation_Variable, config, DT, StrataVarNameList, StrataVarValueList, this.WeightVar);
if (string.IsNullOrEmpty(this.Cross_Tabulation_Variable))
{
if (!(string.IsNullOrEmpty(StrataVarValueList[0]) || StrataVarValueList[0].Equals("NULL")))
this.Execute_Means(HTMLString, this.Numeric_Variable, this.Cross_Tabulation_Variable, StrataVarNameList, StrataVarValueList, config, workingTable, DT.Select(StrataVarNameList[0] + " = '" + StrataVarValueList[0] + "'").CopyToDataTable());
}
else
{
string stratSelectClause = null;
if (!(string.IsNullOrEmpty(StrataVarValueList[0]) || StrataVarValueList[0].Equals("NULL")))
stratSelectClause = stratSelectClause + StrataVarNameList[0] + " = '" + StrataVarValueList[0] + "'";
for (int i = 1; i < StrataVarValueList.Count; i++)
{
if (!(string.IsNullOrEmpty(StrataVarValueList[i]) || StrataVarValueList[i].Equals("NULL")))
stratSelectClause = stratSelectClause + " AND " + StrataVarNameList[i] + " = '" + StrataVarValueList[i] + "'";
}
if (!string.IsNullOrEmpty(stratSelectClause))
this.Execute_CrossTab(HTMLString, this.Numeric_Variable, this.Cross_Tabulation_Variable, StrataVarNameList, StrataVarValueList, config, workingTable, DT.Select(stratSelectClause).CopyToDataTable());
}
if (!string.IsNullOrEmpty(this.OutTable))
{
workingTable.TableName = this.OutTable;
this.Context.OutTable(workingTable);
}
}
}
else
{
if (!string.IsNullOrEmpty(StrataVarName))
{
StrataVarNameList.Add(StrataVarName);
StrataVarValueList.Add(StrataVar_Value);
}
DataTable workingTable = cWorkingTable.CreateWorkingTable(this.Numeric_Variable, this.Cross_Tabulation_Variable, config, DT, StrataVarNameList, StrataVarValueList, this.WeightVar);
//HTMLString.Append("<table><tr>");
//foreach (DataColumn c in workingTable.Columns)
//{
// //System.Console.WriteLine("{0}\t{1}\t{2}", r[0], r[1], r[2]);
// //System.Console.WriteLine("{0}\t{1}\t{2}\t{3}", r[0], r[1], r[2], r[3]);
// System.Console.Write("{0}\t", c.ColumnName);
// HTMLString.Append("<th>");
// HTMLString.Append(c.ColumnName);
// HTMLString.Append("</th>");
//}
//System.Console.Write("\n");
//HTMLString.Append("</tr>");
//foreach (DataRow r in workingTable.Rows)
//{
// HTMLString.Append("<tr>");
// foreach (DataColumn c in workingTable.Columns)
// {
// //System.Console.WriteLine("{0}\t{1}\t{2}", r[0], r[1], r[2]);
// //System.Console.WriteLine("{0}\t{1}\t{2}\t{3}", r[0], r[1], r[2], r[3]);
// System.Console.Write("{0}\t", r[c.ColumnName]);
// HTMLString.Append("<td>");
// HTMLString.Append(r[c.ColumnName]);
// HTMLString.Append("</td>");
// }
// System.Console.Write("\n");
// HTMLString.Append("</tr>");
//}
//HTMLString.Append("</table>");
if (string.IsNullOrEmpty(this.Cross_Tabulation_Variable))
{
this.Execute_Means(HTMLString, this.Numeric_Variable, this.Cross_Tabulation_Variable, StrataVarNameList, StrataVarValueList, config, workingTable, DT);
}
else
{
this.Execute_CrossTab(HTMLString, this.Numeric_Variable, this.Cross_Tabulation_Variable, StrataVarNameList, StrataVarValueList, config, workingTable, DT);
}
if (!string.IsNullOrEmpty(this.OutTable))
{
workingTable.TableName = this.OutTable;
this.Context.OutTable(workingTable);
}
}
Dictionary<string, string> args = new Dictionary<string, string>();
args.Add("COMMANDNAME", "MEANS");
args.Add("COMMANDTEXT", commandText.Trim());
args.Add("MEANSVARIABLE", this.Numeric_Variable);
args.Add("HTMLRESULTS", HTMLString.ToString());
this.Context.Display(args);
}
public static DataTable CreateWorkingTable(string pNumericVariable, string pCrossTabVariable, Dictionary <string, string> config, DataTable DT, List <string> pStratavar, List <string> pStrataValue, string pWeightVariable)
{
DataTable result = new DataTable();
result.CaseSensitive = true;
DataTable RowValues = new DataTable();
DataTable ColumnValues = new DataTable();
/*
* Produce Table with rows = number of distinct values in main_variable
* if no cross _tab_variable then columns = 2
* else columns = 2 + number of values in cross_tab_variable
*
* For each strata
* get distinct values of the strata
* for each distinct value
* produce the following table
*/
cDataSetHelper ds = new cDataSetHelper();
ColumnValues = ds.SelectDistinct("ColumnValues", DT, pNumericVariable);
result.Columns.Add(new DataColumn("__Values__", ColumnValues.Columns[pNumericVariable].DataType));
result.Columns.Add(new DataColumn("__Count__", typeof(double)));
if (!string.IsNullOrEmpty(pCrossTabVariable))
{
RowValues = ds.SelectDistinct("RowValues", DT, pCrossTabVariable);
foreach (DataRow R in RowValues.Select("", pCrossTabVariable))
{
if (R[0] == DBNull.Value || string.IsNullOrEmpty(R[0].ToString()))
{
if (config["include-missing"].ToUpperInvariant() != "FALSE")
{
DataColumn dataColumn = new DataColumn(config["RepresentationOfMissing"], typeof(double));
dataColumn.ExtendedProperties.Add("Value", R[0]);
bool isFound = false;
for (int i = 0; i < result.Columns.Count; i++)
{
if (dataColumn.ColumnName == result.Columns[i].ColumnName)
{
isFound = true;
break;
}
}
if (!isFound)
{
result.Columns.Add(dataColumn);
}
else
{
}
}
}
else
{
DataColumn dataColumn = new DataColumn(R[0].ToString(), typeof(double));
dataColumn.ExtendedProperties.Add("Value", R[0]);
bool isFound = false;
for (int i = 0; i < result.Columns.Count; i++)
{
if (dataColumn.ColumnName == result.Columns[i].ColumnName)
{
isFound = true;
break;
}
}
if (!isFound)
{
result.Columns.Add(dataColumn);
}
else
{
}
}
}
}
Dictionary <string, DataRow> RowList = new Dictionary <string, DataRow>();
DataRow newRow = null;
// initialize table
foreach (DataRow R in ColumnValues.Select("", "[" + pNumericVariable + "]"))
{
string RowKey = null;
if (R[0] is DateTime)
{
RowKey = ((DateTime)R[0]).ToString("MM/dd/yyyy hh:mm:ss.fff tt");
}
else
{
RowKey = R[0].ToString();
}
if (!RowList.ContainsKey(RowKey))
{
newRow = result.NewRow();
foreach (DataColumn C in result.Columns)
{
if (C.ColumnName.Equals("__Values__"))
{
newRow[C.ColumnName] = R[0];
}
else
{
newRow[C.ColumnName] = 0;
}
}
RowList.Add(RowKey, newRow);
result.Rows.Add(newRow);
}
}
DataRow[] workingRows = null;
if (pStratavar == null || pStratavar.Count == 0 || string.IsNullOrEmpty(pStratavar[0]))
{
workingRows = DT.Select("", "[" + pNumericVariable + "]");
}
else
{
StringBuilder WhereClause = new StringBuilder();
for (int i = 0; i < pStratavar.Count; i++)
{
string CurrentStratavar = pStratavar[i];
string CurrentValue = pStrataValue[i];
DataColumn column = DT.Columns[CurrentStratavar];
switch (column.DataType.Name)
{
case "Byte":
case "Boolean":
case "Double":
case "Float":
case "Integer":
case "Int16":
case "Short":
case "Int32":
case "Int64":
if (CurrentValue.Equals("NULL"))
{
WhereClause.Append(string.Format("[{0}] IS {1} And ", CurrentStratavar, CurrentValue));
}
else
{
WhereClause.Append(string.Format("[{0}] = {1} And ", CurrentStratavar, CurrentValue));
}
break;
case "String":
default:
WhereClause.Append(string.Format("[{0}] = '{1}' And ", CurrentStratavar, CurrentValue));
break;
}
}
WhereClause.Length = WhereClause.Length - 4;
workingRows = DT.Select(WhereClause.ToString(), "[" + pNumericVariable + "]");
}
if (string.IsNullOrEmpty(pCrossTabVariable))
{
foreach (DataRow R in workingRows)
{
DataRow Dest;
if (R[pNumericVariable] is DateTime)
{
Dest = RowList[((DateTime)R[pNumericVariable]).ToString("MM/dd/yyyy hh:mm:ss.fff tt")];
}
else
{
Dest = RowList[R[pNumericVariable].ToString()];
}
if (string.IsNullOrEmpty(pWeightVariable))
{
Dest["__Count__"] = (double)Dest["__Count__"] + 1;
}
else
{
if (R[pWeightVariable] != DBNull.Value && ((R[pWeightVariable] is String) == false))
{
Dest["__Count__"] = (double)Dest["__Count__"] + Convert.ToDouble(R[pWeightVariable]);
}
else
{
if (config["include-missing"].ToUpperInvariant() != "FALSE")
{
Dest["__Count__"] = (double)Dest["__Count__"] + 1;
}
}
}
}
}
else
{
foreach (DataRow R in workingRows)
{
DataRow Dest;
if (R[pNumericVariable] is DateTime)
{
Dest = RowList[((DateTime)R[pNumericVariable]).ToString("MM/dd/yyyy hh:mm:ss.fff tt")];
}
else
{
Dest = RowList[R[pNumericVariable].ToString()];
}
double rowWeightValue = 0;
double rowCrossTabValue = 0;
if (string.IsNullOrEmpty(pCrossTabVariable) == false)
{
if (R[pCrossTabVariable] != System.DBNull.Value && !string.IsNullOrEmpty(R[pCrossTabVariable].ToString()))
{
rowCrossTabValue = (double)Dest[R[pCrossTabVariable].ToString()];
}
else
{
if (config["include-missing"].ToUpperInvariant() != "FALSE")
{
rowCrossTabValue = (double)Dest[config["RepresentationOfMissing"]];
}
}
}
if (string.IsNullOrEmpty(pWeightVariable))
{
Dest["__Count__"] = (double)Dest["__Count__"] + 1;
if (R[pCrossTabVariable] != System.DBNull.Value && !string.IsNullOrEmpty(R[pCrossTabVariable].ToString()))
{
Dest[R[pCrossTabVariable].ToString()] = rowCrossTabValue + 1;
}
else
{
if (config["include-missing"].ToUpperInvariant() != "FALSE")
{
Dest[config["RepresentationOfMissing"]] = rowCrossTabValue + 1;
}
}
}
else
{
if (R[pWeightVariable] != System.DBNull.Value && !string.IsNullOrEmpty(R[pWeightVariable].ToString()))
{
rowWeightValue = Convert.ToDouble(R[pWeightVariable]);
Dest["__Count__"] = (double)Dest["__Count__"] + rowWeightValue;
}
else
{
if (config["include-missing"].ToUpperInvariant() != "FALSE")
{
Dest["__Count__"] = (double)Dest["__Count__"] + rowWeightValue;
}
}
if (R[pCrossTabVariable] != System.DBNull.Value && !string.IsNullOrEmpty(R[pCrossTabVariable].ToString()))
{
if (R[pNumericVariable] is DateTime)
{
string asString = ((DateTime)R[pNumericVariable]).ToString("MM/dd/yyyy hh:mm:ss.fff tt");
bool containsColumn = Dest.Table.Columns.Contains(asString);
if (containsColumn)
{
Dest[asString] = rowCrossTabValue + rowWeightValue;
}
}
else
{
Dest[R[pCrossTabVariable].ToString()] = rowCrossTabValue + rowWeightValue;
}
}
else
{
if (config["include-missing"].ToUpperInvariant() != "FALSE")
{
Dest[config["RepresentationOfMissing"]] = rowCrossTabValue + 1;
}
}
}
}
}
return(result);
}
public void Execute()
{
_regressionTable = new DataTable();
_regressionTable.Columns.Add("SeriesName", typeof(string));
_regressionTable.Columns.Add("Predictor", typeof(object));
_regressionTable.Columns.Add("Response", typeof(double));
Dictionary<string, string> config = Context.SetProperties;
StringBuilder HTMLString = new StringBuilder();
System.Data.DataTable sourceTable = BuildTempContextTable();
cDataSetHelper dsHelper = new cDataSetHelper();
Dictionary<string, string> args = new Dictionary<string, string>();
string strataVarValue = string.Empty;
string objectElement = string.Empty;
SilverlightMarkup silverlight = new SilverlightMarkup();
DataTable Strata_ValueList;
if (string.IsNullOrEmpty(_strataVar))
{
Strata_ValueList = sourceTable.Clone();
Strata_ValueList.Rows.Add(Strata_ValueList.NewRow());
}
else
{
Strata_ValueList = dsHelper.SelectDistinct("", sourceTable, _strataVar);
}
FilterStruct filter = new FilterStruct();
filter.strataVarName = _strataVar;
_independentValueTypesSame = true;
if(_independentVariableArray.Length > 1)
{
for(int i = 0; (i + 1) < _independentVariableArray.Length ; i++)
{
if (sourceTable.Columns[_independentVariableArray[i]].DataType != sourceTable.Columns[_independentVariableArray[i+1]].DataType )
{
_independentValueTypesSame = false;
break;
}
}
}
_independentValuesAllBool = true;
for (int i = 0; i < _independentVariableArray.Length; i++)
{
string indepVar = _independentVariableArray[i];
if (sourceTable.Columns.Contains(indepVar))
{
if (sourceTable.Columns[indepVar].DataType.Name != "Byte")
{
_independentValuesAllBool = false;
break;
}
}
else
{
return;
}
}
foreach (DataRow strataRow in Strata_ValueList.Rows)
{
if (string.IsNullOrEmpty(_strataVar) || strataRow[_strataVar] == DBNull.Value)
{
filter.strataVarValue = "null";
}
else
{
filter.strataVarValue = strataRow[_strataVar].ToString();
}
if (_graphType == SilverlightStatics.Scatter)
{
if (_independentVariableArray.Length < 2)
{
throw new Exception("Scatter graphs must contain two (2) main variables.");
}
string categoryName = string.Empty;
Dictionary<object, double> indDepValuePairCollection = new Dictionary<object, double>();
List<string> StrataVarNameList = new List<string>();
List<string> StrataVarValueList = new List<string>();
string regressor = _independentVariableArray[0];
string regressand = _independentVariableArray[1];
StrataVarNameList.Add(filter.strataVarName);
StrataVarValueList.Add(filter.strataVarValue);
DataTable table = new DataTable();
table.Columns.Add(new DataColumn(sourceTable.Columns[regressor].ColumnName, sourceTable.Columns[regressor].DataType));
table.Columns.Add(new DataColumn(sourceTable.Columns[regressand].ColumnName, sourceTable.Columns[regressand].DataType));
foreach (DataRow row in sourceTable.Rows)
{
table.Rows.Add(row[regressor], row[regressand]);
}
foreach (DataRow row in table.Rows)
{
if (row[regressor] != DBNull.Value)
{
string seriesName = string.Empty;
object independentValue = new object();
if (_independentValuesAllBool)
{
independentValue = regressor;
byte value = (byte)(row[regressor]);
}
else
{
independentValue = GetValue(regressor, row, config);
}
seriesName = string.Format("{0} x {1}", sourceTable.Columns[regressor].ColumnName, sourceTable.Columns[regressand].ColumnName);
if (string.IsNullOrEmpty(_weightVar))
{
double dependentVal;
if (double.TryParse(row[regressand].ToString(), out dependentVal))
{
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
else
{
filter.independentVarValue = row[regressor];
filter.independentVarName = regressor;
filter.weightVarName = _weightVar;
double dependentVal = GetAggregateValue(sourceTable, filter);
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
}
}
else if (sourceTable.Columns.Contains(_graphCrossTab))
{
string categoryName = string.Empty;
Dictionary<object, double> indDepValuePairCollection = new Dictionary<object, double>();
List<string> StrataVarNameList = new List<string>();
List<string> StrataVarValueList = new List<string>();
foreach (string independentVariableName in _independentVariableArray)
{
StrataVarNameList.Add(filter.strataVarName);
StrataVarValueList.Add(filter.strataVarValue);
DataTable workingTable = cWorkingTable.CreateWorkingTable(
independentVariableName,
_graphCrossTab,
config,
sourceTable,
StrataVarNameList,
StrataVarValueList,
_weightVar);
foreach (DataColumn crossTabCandidate in workingTable.Columns)
{
if (crossTabCandidate.ColumnName != "__Values__" && crossTabCandidate.ColumnName != "__Count__")
{
Type crossTabDataType = sourceTable.Columns[_graphCrossTab].DataType;
string crossTabValue = crossTabCandidate.ColumnName;
if (crossTabDataType.Name == "Byte")
{
if (crossTabCandidate.ColumnName == "0")
{
crossTabValue = config["RepresentationOfNo"];
}
else if (crossTabCandidate.ColumnName == "1")
{
crossTabValue = config["RepresentationOfYes"];
}
}
string seriesName = string.Format("{0}={1}", _graphCrossTab, crossTabValue);
foreach (DataRow row in workingTable.Rows)
{
double dependentVal;
if (double.TryParse(row[crossTabCandidate.ColumnName].ToString(), out dependentVal))
{
if (row["__Values__"] != DBNull.Value)
{
string independentVariableTypeName = string.Empty;
if (sourceTable.Columns.Contains(independentVariableName))
{
independentVariableTypeName = sourceTable.Columns[independentVariableName].DataType.Name;
}
categoryName = BuildCategoryName(independentVariableName, independentVariableTypeName, row, config);
object independentValue = row["__Values__"];
if (string.IsNullOrEmpty(_weightVar))
{
if (double.TryParse(row[crossTabCandidate.ColumnName].ToString(), out dependentVal))
{
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
else
{
object independentVariableValue = row["__Values__"];
bool isValue = false;
if (independentVariableValue != null)
{
isValue = true;
if (independentVariableValue is string)
{
string candidate = ((string)independentVariableValue).Trim();
isValue = candidate != string.Empty;
}
}
if (isValue)
{
filter.crossTabName = _graphCrossTab;
filter.crossTabValue = crossTabCandidate.ColumnName;
filter.independentVarName = independentVariableName;
filter.independentVarValue = independentVariableValue;
filter.weightVarName = _weightVar;
dependentVal = GetAggregateValue(sourceTable, filter);
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
}
}
}
}
}
}
}
else if (_graphType == "EPICURVE" && _graphIntervalUnits != "")
{
string categoryName = string.Empty;
Dictionary<object, double> indDepValuePairCollection = new Dictionary<object, double>();
List<string> StrataVarNameList = new List<string>();
List<string> StrataVarValueList = new List<string>();
foreach (string independentVariable in _independentVariableArray)
{
StrataVarNameList.Add(filter.strataVarName);
StrataVarValueList.Add(filter.strataVarValue);
DataTable workingTable = cWorkingTable.CreateWorkingTable(
independentVariable,
"",
config,
sourceTable,
StrataVarNameList,
StrataVarValueList,
_weightVar);
DateTime intervalStart = DateTime.MinValue;
DateTime intervalEnd = DateTime.MinValue;
DateTime dateTimeValue = DateTime.MinValue;
intervalStart = _graphStartFrom;
double givenInterval = 1;
int days = 0, hours = 0, minutes = 0, seconds = 0;
TimeSpan period = new TimeSpan(days, hours, minutes, seconds);
if (_graphInterval != "")
{
double.TryParse(_graphInterval, out givenInterval);
}
if (_graphIntervalUnits == "")
{
_graphIntervalUnits = "Hours";
}
foreach (DataRow row in workingTable.Rows)
{
if (row["__Values__"] != DBNull.Value)
{
dateTimeValue = (DateTime)row["__Values__"];
//if (intervalStart == DateTime.MinValue)
//{
// intervalStart = dateTimeValue;
// _graphStartFrom = dateTimeValue;
//}
while ( dateTimeValue >= intervalEnd)
{
if (intervalEnd != DateTime.MinValue)
{
intervalStart = intervalEnd;
}
switch (_graphIntervalUnits)
{
case "Years":
intervalEnd = intervalStart.AddYears((int)givenInterval);
break;
case "Quarters":
intervalEnd = intervalStart.AddDays(givenInterval * 365.25 / 4.0);
break;
case "Weeks":
intervalEnd = intervalStart.AddDays(givenInterval * 7);
break;
case "Days":
intervalEnd = intervalStart.AddDays(givenInterval);
break;
case "Hours":
intervalEnd = intervalStart.AddHours(givenInterval);
break;
case "Minutes":
intervalEnd = intervalStart.AddMinutes(givenInterval);
break;
case "Seconds":
intervalEnd = intervalStart.AddSeconds(givenInterval);
break;
}
}
string seriesName = string.Empty;
object independentValue = new object();
independentValue = BuildIndependentValue(independentVariable, row, config);
if (string.IsNullOrEmpty(seriesName))
{
seriesName = SilverlightStatics.COUNT;
if (_independentVariableArray.Length > 1)
{
seriesName = independentVariable;
}
if(string.IsNullOrEmpty(_aggregateFunction) == false)
{
seriesName = _aggregateFunction;
}
}
if (string.IsNullOrEmpty(_weightVar))
{
double dependentVal;
if (double.TryParse(row["__Count__"].ToString(), out dependentVal))
{
if ((dateTimeValue >= intervalStart) && (dateTimeValue < intervalEnd))
{
string expression = "Predictor = #" + intervalStart.ToString() + "#";
DataRow[] foundRows = _regressionTable.Select(expression);
if (foundRows.Length == 0)
{
_regressionTable.Rows.Add(seriesName, intervalStart, dependentVal);
}
else
{
foundRows[0]["Response"] = (double)foundRows[0]["Response"] + dependentVal;
_regressionTable.AcceptChanges();
}
}
}
}
else
{
filter.independentVarValue = row["__Values__"];
filter.independentVarName = independentVariable;
filter.weightVarName = _weightVar;
double dependentVal = GetAggregateValue(sourceTable, filter);
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
}
}
}
else
{
string categoryName = string.Empty;
Dictionary<object, double> indDepValuePairCollection = new Dictionary<object, double>();
List<string> StrataVarNameList = new List<string>();
List<string> StrataVarValueList = new List<string>();
foreach (string independentVariable in _independentVariableArray)
{
StrataVarNameList.Add(filter.strataVarName);
StrataVarValueList.Add(filter.strataVarValue);
DataTable workingTable = cWorkingTable.CreateWorkingTable(
independentVariable,
"",
config,
sourceTable,
StrataVarNameList,
StrataVarValueList,
_weightVar);
foreach (DataRow row in workingTable.Rows)
{
if (row["__Values__"] != DBNull.Value)
{
string seriesName = string.Empty;
object independentValue = new object();
if (!_independentValueTypesSame || _graphType == SilverlightStatics.Pie )
{
independentValue = BuildIndependentValue(independentVariable, row, config);
}
else if (_independentValuesAllBool)
{
independentValue = independentVariable;
byte value = (byte)(row["__Values__"]);
seriesName = row["__Values__"].ToString();
if (value == 0)
{
seriesName = config["RepresentationOfNo"];
}
else if (value == 1)
{
seriesName = config["RepresentationOfYes"];
}
}
else
{
independentValue = BuildIndependentValue(independentVariable, row, config);
}
if (string.IsNullOrEmpty(seriesName))
{
seriesName = SilverlightStatics.COUNT;
if (_independentVariableArray.Length > 1)
{
seriesName = independentVariable;
}
if(string.IsNullOrEmpty(_aggregateFunction) == false)
{
seriesName = _aggregateFunction;
}
}
if (string.IsNullOrEmpty(_weightVar))
{
//
if (independentValue.ToString().ToUpper() == "TRUE")
{
independentValue = config["RepresentationOfYes"];
}
if (independentValue.ToString().ToUpper() == "FALSE")
{
independentValue = config["RepresentationOfNo"];
}
//
double dependentVal;
if (double.TryParse(row["__Count__"].ToString(), out dependentVal))
{
if ((_graphType == "EPICURVE" && independentValue is DateTime && ((DateTime)independentValue) <= _graphStartFrom) == false)
{
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
}
else
{
//
if (independentValue.ToString().ToUpper() == "TRUE")
{
independentValue = config["RepresentationOfYes"];
}
if (independentValue.ToString().ToUpper() == "FALSE")
{
independentValue = config["RepresentationOfNo"];
}
//
filter.independentVarValue = row["__Values__"];
filter.independentVarName = independentVariable;
filter.weightVarName = _weightVar;
double dependentVal = GetAggregateValue(sourceTable, filter);
_regressionTable.Rows.Add(seriesName, independentValue, dependentVal);
}
}
}
}
}
string graphTitle = _graphTitle;
if (sourceTable.Columns.Contains(filter.strataVarName))
{
Type _strataVarDataType = sourceTable.Columns[filter.strataVarName].DataType;
string strataVarText = filter.strataVarValue;
strataVarText = GetPrintValue(filter.strataVarName, _strataVarDataType.Name, filter.strataVarValue, config);
if (string.IsNullOrEmpty(_strataVar) == false)
{
graphTitle = string.Format("{0} {1}={2}", _graphTitle, filter.strataVarName, strataVarText);
}
}
DataView view = new DataView(_regressionTable);
DataTable distinctValues = new DataTable();
distinctValues = view.ToTable(true, "SeriesName");
bool hideLegend = false;
if (distinctValues.Rows.Count == 1 && distinctValues.Rows[0][0].ToString() == "COUNT")
{
hideLegend = true;
}
objectElement = objectElement
+ silverlight.Graph
(
_graphType,
graphTitle,
_graphIndependentAxisLabel,
_graphDependentAxisLabel,
"",
"",
_graphInterval,
_graphIntervalUnits,
_graphStartFrom,
_regressionTable,
hideLegend
);
_regressionTable.Rows.Clear();
}
string data = string.Empty;
if (objectElement != "")
{
data = objectElement + @"<hr/>";
}
else
{
data = SharedStrings.UNABLE_CREATE_GRAPH;
}
args.Add("COMMANDNAME", CommandNames.GRAPH);
args.Add("DATA", data);
args.Add("COMMANDTEXT", _commandText.Trim());
Context.Display(args);
}
