public FieldsVo(Table.TableVo table, string grouping, string codigo, int id)
{
this.MAINTABLE = table;
this.GROUPING = grouping;
this.CODIGO = codigo;
this.ID = id;
}
public OrderByVo(int indice, Table.TableVo table, string value, string display)
{
this.INDICE = indice;
this.MAINTABLE = table;
this.VALUE = value;
this.DISPLAY = display;
}
public DataTable getRecursiveTables(Table.TableVo mainTable, string tablename, int mainId, string filtro)
{
// verifica e cria, se necessario a procedure SPR_ParametrosRecursivos, para essa necessidade.
//TODO criar modo de add procedure na SigaWeb sem usar o GO e o USE BANCODEDADOS
//this.CreateRecursiveProcedure();
// executa query usando a procedure acima.
this.QUERY = new StringBuilder(fromDatabase);
this.QUERY.AppendLine("IF OBJECT_ID('@TABELA','U') IS NOT NULL");
this.QUERY.AppendLine("BEGIN");
this.QUERY.AppendLine(" DROP TABLE @TABELA");
this.QUERY.AppendLine("END");
//this.QUERY.AppendLine("GO");
this.QUERY.AppendLine("EXECUTE SPR_ParametrosRecursivos " + mainId + ", 0, @TABELA");
//this.QUERY.AppendLine("GO");
this.QUERY.AppendLine("SELECT DISTINCT RTable.*, params.*");
this.QUERY.AppendLine(" FROM RTable");
this.QUERY.AppendLine(" RIGHT JOIN @TABELA TEMP");
this.QUERY.AppendLine(" ON TEMP.id = RTable.id");
this.QUERY.AppendLine(" OR RTable.id = " + mainId);
this.QUERY.AppendLine(" INNER JOIN params");
this.QUERY.AppendLine(" ON params.mainId = RTable.id");
if (!string.IsNullOrEmpty(filtro))
{
this.QUERY.AppendLine(" WHERE " + filtro);
}
this.QUERY.AppendLine("");
this.QUERY.AppendLine("DROP TABLE @TABELA");
/* //QUERY RECURSIVA COM METODO QUE SÓ FUNCIONA NO SQL 2005 ou SUPERIOR(muito mais rapido)
* this.QUERY.AppendLine("WITH TABELAS AS");
* this.QUERY.AppendLine("(");
* this.QUERY.AppendLine(" SELECT main.id , main.mainId , main.tabela");
* this.QUERY.AppendLine(" , main.relatedtype , main.relatedtable , main.idReport");
* this.QUERY.AppendLine(" FROM RTable main");
* this.QUERY.AppendLine(" WHERE main.id = " + mainTable.ID);
* this.QUERY.AppendLine(" UNION ALL");
* this.QUERY.AppendLine(" SELECT child.id , child.mainId , child.tabela");
* this.QUERY.AppendLine(" , child.relatedtype, child.relatedtable, child.idReport");
* this.QUERY.AppendLine(" FROM TABELAS");
* this.QUERY.AppendLine(" INNER JOIN RTable child");
* this.QUERY.AppendLine(" ON TABELAS.id = child.mainId");
* this.QUERY.AppendLine(")");
* this.QUERY.AppendLine("SELECT distinct *");
* this.QUERY.AppendLine(" FROM TABELAS");
* this.QUERY.AppendLine(" INNER JOIN params");
* this.QUERY.AppendLine(" ON params.mainId = TABELAS.id");
* //*/
this.QUERY = new StringBuilder(this.QUERY.ToString().Replace("@TABELA", tablename));
return(getData());
}
public void AddChild(Table.TableVo child)
{
child.MAINID = this.ID;
//child.INDEX = this.CHILDREN.Count;
//child.SUFIXO = this.SUFIXO+"_"+child.INDEX;
child.PARENT = this;
child.REPORT = this.REPORT;
this.CHILDREN.Add(child);
}
public void RemoveTable(Table.TableVo child)
{
this.CHILDREN.Remove(child);
/*
* foreach(Table.TableVo _child in this.CHILDREN)
* {
* _child.INDEX = this.CHILDREN.IndexOf(_child);
* _child.SUFIXO = this.SUFIXO+"_"+_child.INDEX;
* }
* //*/
}
public int DeleteRecursiveTables(Report.ReportVo report)
{
int i = 0;
Table.TableVo mainTable = new Table.TableVo();
new Table.TableDao().load(mainTable, report.ID, 0);
if (mainTable.ID > 0)
{
i = this.DeleteRecursiveTables(mainTable.ID);
}
return(i);
}
public int save(List <TableVo> tables)
{
int cont = 0;
//foreach (TableVo table in tables)
//{
for (int idx = 0; idx < tables.Count; idx++)
{
Table.TableVo table = tables[idx];
//table.INDEX = idx;
cont += save(table);
}
return(cont);
}
public int FindTable(Table.TableVo child)
{
return(this.CHILDREN.IndexOf(child));
}
public DataTable getRecursiveTables(Table.TableVo mainTable, string tablename, int mainId)
{
return(this.getRecursiveTables(mainTable, tablename, mainId, null));
}
public DataTable getRecursiveTables(Table.TableVo mainTable, string tablename)
{
return(this.getRecursiveTables(mainTable, tablename, 0));
}
public FieldsVo(Table.TableVo table, string grouping)
{
this.MAINTABLE = table;
this.GROUPING = grouping;
}
public OrderByVo(int id, int indice, Table.TableVo table)
{
this.ID = id;
this.INDICE = indice;
this.MAINTABLE = table;
}
