static internal List<SqlColumn> GetSqlColumns(string dataProvider, string connection, string sql, IList parameters)
{
List<SqlColumn> cols = new List<SqlColumn>();
IDbConnection cnSQL=null;
IDbCommand cmSQL=null;
IDataReader dr=null;
Cursor saveCursor=Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
try
{
// Open up a connection
cnSQL = RdlEngineConfig.GetConnection(dataProvider, connection);
if (cnSQL == null)
return cols;
cnSQL.Open();
cmSQL = cnSQL.CreateCommand();
cmSQL.CommandText = sql;
AddParameters(cmSQL, parameters);
dr = cmSQL.ExecuteReader(CommandBehavior.SchemaOnly);
for (int i=0; i < dr.FieldCount; i++)
{
SqlColumn sc = new SqlColumn();
sc.Name = dr.GetName(i);
sc.DataType = dr.GetFieldType(i);
cols.Add(sc);
}
}
catch (SqlException sqle)
{
MessageBox.Show(sqle.Message, "SQL Error");
}
catch (Exception e)
{
MessageBox.Show(e.InnerException == null? e.Message:e.InnerException.Message, "Error");
}
finally
{
if (cnSQL != null)
{
if (cmSQL != null)
{
cmSQL.Dispose();
if (dr != null)
dr.Close();
}
cnSQL.Close();
cnSQL.Dispose();
}
Cursor.Current=saveCursor;
}
return cols;
}
private bool DoReportSyntax()
{
string template;
if (rbList.Checked)
{
template = _TemplateList;
}
else if (rbTable.Checked)
{
template = _TemplateTable;
}
else if (rbMatrix.Checked)
{
template = _TemplateMatrix;
}
else if (rbChart.Checked)
{
template = _TemplateChart;
}
else
{
template = _TemplateTable; // default to table- should never reach
}
if (_ColumnList == null)
{
_ColumnList = DesignerUtility.GetSqlColumns(GetDataProvider(), GetDataConnection(), this.SqlText, this.lbParameters.Items);
}
if (_ColumnList.Count == 0) // can only happen by an error
{
return(false);
}
string[] parts = template.Split('|');
StringBuilder sb = new StringBuilder(template.Length);
decimal left = 0m;
decimal width;
decimal bodyHeight = 0;
string name;
int skip = 0; // skip is used to allow nesting of ifdef
string args;
string canGrow;
string align;
// handle the group by column
string gbcolumn;
if (this.cbColumnList.Text.Length > 0)
{
gbcolumn = GetFieldName(this.cbColumnList.Text);
}
else
{
gbcolumn = null;
}
CultureInfo cinfo = new CultureInfo("", false);
foreach (string p in parts)
{
// Handle conditional special
if (p.Substring(0, 5) == "ifdef")
{
args = p.Substring(6);
switch (args)
{
case "reportname":
if (tbReportName.Text.Length == 0)
{
skip++;
}
break;
case "description":
if (tbReportDescription.Text.Length == 0)
{
skip++;
}
break;
case "author":
if (tbReportAuthor.Text.Length == 0)
{
skip++;
}
break;
case "grouping":
if (gbcolumn == null)
{
skip++;
}
break;
case "footers":
if (!this.ckbGrandTotal.Checked)
{
skip++;
}
else if (this.clbSubtotal.CheckedItems.Count <= 0)
{
skip++;
}
break;
default:
throw new Exception(String.Format("Unknown ifdef element {0} specified in template.", args));
}
continue;
}
// if skipping lines (due to ifdef) then go to next endif
if (skip > 0 && p != "endif")
{
continue;
}
switch (p)
{
case "endif":
if (skip > 0)
{
skip--;
}
break;
case "schema":
if (this.rbSchema2003.Checked)
{
sb.Append(_Schema2003);
}
else if (this.rbSchema2005.Checked)
{
sb.Append(_Schema2005);
}
break;
case "reportname":
sb.Append(tbReportName.Text.Replace('\'', '_'));
break;
case "reportnameasis":
sb.Append(tbReportName.Text);
break;
case "description":
sb.Append(tbReportDescription.Text);
break;
case "author":
sb.Append(tbReportAuthor.Text);
break;
case "connectionproperties":
//if (this.cbConnectionTypes.Text == SHARED_CONNECTION)
//{
// string file = this.tbConnection.Text;
// file = Path.GetFileNameWithoutExtension(file);
// sb.AppendFormat("<DataSourceReference>{0}</DataSourceReference>", file);
//}
//else
// sb.AppendFormat("<ConnectionProperties><DataProvider>{0}</DataProvider><ConnectString>{1}</ConnectString></ConnectionProperties>",
// GetDataProvider(), GetDataConnection());
sb.AppendFormat("<ConnectionProperties><DataProvider>{0}</DataProvider><ConnectString>{1}</ConnectString></ConnectionProperties>",
GetDataProvider(), GetDataConnection());
break;
case "dataprovider":
sb.Append(GetDataProvider());
break;
case "connectstring":
sb.Append(tbConnection.Text);
break;
case "columncount":
sb.Append(_ColumnList.Count);
break;
case "orientation":
if ((this.cbOrientation.SelectedIndex == 0) & (this.cbxPages.SelectedIndex < this.cbxPages.Items.Count - 1))
{
sb.Append("<PageHeight>" + this.tbHeight.Text + "</PageHeight><PageWidth>" + this.tbWidth.Text + "</PageWidth>");
}
else
{
sb.Append("<PageHeight>" + this.tbWidth.Text + "</PageHeight><PageWidth>" + this.tbHeight.Text + "</PageWidth>");
}
break;
case "groupbycolumn":
sb.Append(gbcolumn);
break;
case "reportparameters":
DoReportSyntaxParameters(cinfo, sb);
break;
case "queryparameters":
DoReportSyntaxQParameters(cinfo, sb, this.SqlText);
break;
case "sqltext":
sb.Append(this.SqlText.Replace("<", "<"));
break;
case "sqlfields":
foreach (SqlColumn sq in _ColumnList)
{
name = GetFieldName(sq.Name);
string type = sq.DataType.FullName;
if (this.rbSchemaNo.Checked)
{
sb.AppendFormat(cinfo, "<Field Name='{0}'><DataField>{1}</DataField><TypeName>{2}</TypeName></Field>", name, sq.Name, type);
}
else
{
sb.AppendFormat(cinfo, "<Field Name='{0}'><DataField>{1}</DataField><rd:TypeName>{2}</rd:TypeName></Field>", name, sq.Name, type);
}
}
break;
case "listheaders":
left = .0m;
foreach (SqlColumn sq in _ColumnList)
{
name = sq.Name;
width = name.Length / 8m;
if (width < 1)
{
width = 1;
}
sb.AppendFormat(cinfo, @"
<Textbox><Top>.3in</Top><Left>{0}in</Left><Width>{1}in</Width><Height>.2in</Height><Value>{2}</Value>
<Style><FontWeight>Bold</FontWeight><BorderStyle><Bottom>Solid</Bottom></BorderStyle>
<BorderWidth><Bottom>3pt</Bottom></BorderWidth></Style>
</Textbox>" ,
left,
width,
name);
left += width;
}
break;
case "listvalues":
left = .0m;
foreach (SqlColumn sq in _ColumnList)
{
name = GetFieldName(sq.Name);
DoAlignAndCanGrow(sq.DataType, out canGrow, out align);
width = name.Length / 8m;
if (width < 1)
{
width = 1;
}
sb.AppendFormat(cinfo, @"
<Textbox Name='{2}'><Top>.1in</Top><Left>{0}in</Left><Width>{1}in</Width><Height>.25in</Height><Value>=Fields!{2}.Value</Value><CanGrow>{3}</CanGrow><Style>{4}</Style></Textbox>" ,
left, width, name, canGrow, align);
left += width;
}
bodyHeight = .4m;
break;
case "listwidth": // in template list width must follow something that sets left
sb.AppendFormat(cinfo, "{0}in", left);
break;
case "tableheaders":
// the group by column is always the first one in the table
if (gbcolumn != null)
{
bodyHeight += 12m;
/*
* sb.AppendFormat(cinfo, @"
* <TableCell>
* <ReportItems><Textbox><Value>{0}</Value><Style><TextAlign>Center</TextAlign><BorderStyle><Default>Solid</Default></BorderStyle><FontWeight>Bold</FontWeight></Style></Textbox></ReportItems>
* </TableCell>",
* this.cbColumnList.Text);
*/
EAS.Data.ORM.Column coly = this.table.Columns.GetColumn(this.cbColumnList.Text);
if (coly == null)
{
coly = this.table.Columns.GetColumn("[" + this.cbColumnList.Text + "]");
}
sb.AppendFormat(cinfo, @"
<TableCell>
<ReportItems><Textbox><Value>{0}</Value><Style><TextAlign>Center</TextAlign><BorderStyle><Default>Solid</Default></BorderStyle><FontWeight>Bold</FontWeight></Style></Textbox></ReportItems>
</TableCell>" ,
coly.Caption);
}
bodyHeight += 12m;
foreach (SqlColumn sq in _ColumnList)
{
name = sq.Name;
if (name == this.cbColumnList.Text)
{
continue;
}
/*
* sb.AppendFormat(cinfo, @"
* <TableCell>
* <ReportItems><Textbox><Value>{0}</Value><Style><TextAlign>Center</TextAlign><BorderStyle><Default>Solid</Default></BorderStyle><FontWeight>Bold</FontWeight></Style></Textbox></ReportItems>
* </TableCell>",
* name);
*/
EAS.Data.ORM.Column colx = this.table.Columns.GetColumn(name);
if (colx == null)
{
colx = this.table.Columns.GetColumn("[" + name + "]");
}
sb.AppendFormat(cinfo, @"
<TableCell>
<ReportItems><Textbox><Value>{0}</Value><Style><TextAlign>Center</TextAlign><BorderStyle><Default>Solid</Default></BorderStyle><FontWeight>Bold</FontWeight></Style></Textbox></ReportItems>
</TableCell>" ,
colx.Caption);
}
break;
case "tablecolumns":
if (gbcolumn != null)
{
bodyHeight += 12m;
width = gbcolumn.Length / 8m; // TODO should really use data value
if (width < 1)
{
width = 1;
}
sb.AppendFormat(cinfo, @"<TableColumn><Width>{0}in</Width></TableColumn>", width);
}
bodyHeight += 12m;
foreach (SqlColumn sq in _ColumnList)
{
name = GetFieldName(sq.Name);
if (name == gbcolumn)
{
continue;
}
width = name.Length / 8m; // TODO should really use data value
if (width < 1)
{
width = 1;
}
sb.AppendFormat(cinfo, @"<TableColumn><Width>{0}in</Width></TableColumn>", width);
}
break;
case "tablevalues":
bodyHeight += 12m;
if (gbcolumn != null)
{
sb.Append(@"<TableCell>
<ReportItems><Textbox><Value></Value><Style><BorderStyle><Default>None</Default><Left>Solid</Left></BorderStyle></Style></Textbox></ReportItems>
</TableCell>" );
}
foreach (SqlColumn sq in _ColumnList)
{
name = GetFieldName(sq.Name);
if (name == gbcolumn)
{
continue;
}
DoAlignAndCanGrow(sq.DataType, out canGrow, out align);
sb.AppendFormat(cinfo, @"
<TableCell>
<ReportItems><Textbox Name='{0}'><Value>=Fields!{0}.Value</Value><CanGrow>{1}</CanGrow><Style><BorderStyle><Default>Solid</Default></BorderStyle>{2}</Style></Textbox></ReportItems>
</TableCell>" ,
name, canGrow, align);
}
break;
case "gtablefooters":
case "tablefooters":
bodyHeight += 12m;
canGrow = "false";
align = "";
string nameprefix = p == "gtablefooters" ? "gf" : "tf";
if (gbcolumn != null) // handle group by column first
{
int i = clbSubtotal.FindStringExact(this.cbColumnList.Text);
SqlColumn sq = i < 0 ? null : (SqlColumn)clbSubtotal.Items[i];
if (i >= 0 && clbSubtotal.GetItemChecked(i))
{
string funct = DesignerUtility.IsNumeric(sq.DataType) ? "Sum" : "Count";
DoAlignAndCanGrow(((object)0).GetType(), out canGrow, out align);
sb.AppendFormat(cinfo, @"
<TableCell>
<ReportItems><Textbox Name='{4}_{0}'><Value>={1}(Fields!{0}.Value)</Value><CanGrow>{2}</CanGrow><Style><BorderStyle><Default>Solid</Default></BorderStyle>{3}</Style></Textbox></ReportItems>
</TableCell>" ,
gbcolumn, funct, canGrow, align, nameprefix);
}
else
{
sb.AppendFormat(cinfo, "<TableCell><ReportItems><Textbox><Value></Value><Style><BorderStyle><Default>Solid</Default></BorderStyle></Style></Textbox></ReportItems></TableCell>");
}
}
for (int i = 0; i < this.clbSubtotal.Items.Count; i++)
{
SqlColumn sq = (SqlColumn)clbSubtotal.Items[i];
name = GetFieldName(sq.Name);
if (name == gbcolumn)
{
continue;
}
if (clbSubtotal.GetItemChecked(i))
{
string funct = DesignerUtility.IsNumeric(sq.DataType) ? "Sum" : "Count";
DoAlignAndCanGrow(((object)0).GetType(), out canGrow, out align);
sb.AppendFormat(cinfo, @"
<TableCell>
<ReportItems><Textbox Name='{4}_{0}'><Value>={1}(Fields!{0}.Value)</Value><CanGrow>{2}</CanGrow><Style><BorderStyle><Default>Solid</Default></BorderStyle>{3}</Style></Textbox></ReportItems>
</TableCell>" ,
name, funct, canGrow, align, nameprefix);
}
else
{
sb.AppendFormat(cinfo, "<TableCell><ReportItems><Textbox><Value></Value><Style><BorderStyle><Default>Solid</Default></BorderStyle></Style></Textbox></ReportItems></TableCell>");
}
}
break;
case "bodyheight": // Note: this must follow the table definition
sb.AppendFormat(cinfo, "{0}pt", bodyHeight);
break;
default:
sb.Append(p);
break;
}
}
try
{
tbReportSyntax.Text = DesignerUtility.FormatXml(sb.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Internal Error");
tbReportSyntax.Text = sb.ToString();
}
return(true);
}
private void DoGrouping()
{
if (cbColumnList.Items.Count > 0) // We already have the columns?
return;
if (_ColumnList == null)
_ColumnList = DesignerUtility.GetSqlColumns(GetDataProvider(), GetDataConnection(), tbSQL.Text, this.lbParameters.Items);
foreach (SqlColumn sq in _ColumnList)
{
cbColumnList.Items.Add(sq);
clbSubtotal.Items.Add(sq);
}
SqlColumn sqc = new SqlColumn();
sqc.Name = "";
cbColumnList.Items.Add(sqc);
return;
}
public static List <SqlColumn> GetSqlColumns(string sql, IList parameters)
{
List <SqlColumn> cols = new List <SqlColumn>();
Connection connection = new Connection(@"Data Source=U:\Documentacoes_Equipe_ERP\OpenPOS\database\openpos.db;Version=3;");
connection.Open();
Tables tables = Tables.GetTables(connection);
if (tables.Contains(sql))
{
cols = new List <SqlColumn>(from c in tables[sql].Fields
select new SqlColumn
{
Name = c.Name,
DataType = c.GetFieldType()
});
}
else
{
Command cmSQL = null;
DataReader dr = null;
Cursor saveCursor = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
try
{
cmSQL = connection.CreateCommand();
cmSQL.CommandText = sql;
AddParameters(cmSQL, parameters);
dr = cmSQL.ExecuteReader(CommandBehavior.SchemaOnly);
for (int i = 0; i < dr.FieldCount; i++)
{
SqlColumn sc = new SqlColumn();
sc.Name = dr.GetName(i).TrimEnd('\0');
sc.DataType = dr.GetFieldType(i);
cols.Add(sc);
}
}
catch (SqlException sqle)
{
MessageBox.Show(sqle.Message, Strings.DesignerUtility_Show_SQLError);
}
catch (Exception e)
{
MessageBox.Show(e.InnerException == null ? e.Message : e.InnerException.Message, Strings.DesignerUtility_Show_Error);
}
finally
{
if (connection != null)
{
if (cmSQL != null)
{
cmSQL.Dispose();
if (dr != null)
{
dr.Close();
}
}
connection.Close();
connection.Dispose();
}
Cursor.Current = saveCursor;
}
}
return(cols);
}
