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 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() { 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() { // 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); }