private void button1_Click(object sender, EventArgs e)
{
List<String> campos = new List<String>();
List<String> t = new List<String>();
foreach (int check in checkedListBoxColumna.CheckedIndices)
{
campos.Add(checkedListBoxColumna.Items[check].ToString());
t.Add(tipos[check].ToString());
}
tipoFuncion tf;
switch(comboBox2.Text){
case "Contador":
tf = tipoFuncion.cont;
break;
case "Suma":
tf = tipoFuncion.suma;
break;
case "Minimo":
tf = tipoFuncion.min;
break;
case "Maximo":
tf = tipoFuncion.max;
break;
case "Promedio":
tf = tipoFuncion.avg;
break;
default:
MessageBox.Show("Operacion no valida");
tf = tipoFuncion.cont;
break;
}
String res;
if (tf == tipoFuncion.cont)
{
res = "";
}
else
{
res = checkedListBoxResumen.CheckedItems[0].ToString();
}
CreadorDeCubos cc = new CreadorDeCubos(cadenaDeConexion, esquema, campos, t, tf, res, textBoxQuery.Text,tipos,TodosLosCampos);
cc.creaTabla();
cc.creaCubexeles();
DBConnection myDB = new DBConnection(cadenaDeConexion);
myDB.connect();
DataTable dt = myDB.ejecutaQuery("select * from " + esquema+".dbo.cube");
dataGridViewCubo.DataSource = dt;
MessageBox.Show("Terminado");
}
public void creaTabla()
{
DBConnection db = new DBConnection(CadenaDeConexion);
db.connect();
StringBuilder cmd = new StringBuilder();
cmd.Append(" IF OBJECT_ID('");
cmd.Append(Esquema);
cmd.Append(".dbo.cube', 'U') IS NOT NULL");
cmd.Append(" DROP TABLE ");
cmd.Append(Esquema);
cmd.Append(".dbo.cube");
db.ejecutaComando(cmd.ToString());
cmd = new StringBuilder();
cmd.Append("Create table ");
cmd.Append(Esquema);
cmd.Append(".dbo.cube(");
for (int i = 0; i < campos.Count; i++)
{
if (i != 0)
{
cmd.Append(",");
}
cmd.Append(campos[i]);
cmd.Append(" ");
cmd.Append("Text");//tipos[i]);
}
if (campos.Count > 0)
{
cmd.Append(",");
}
if (tipoF == tipoFuncion.cont)
{
cmd.Append("Total");
}
else
{
cmd.Append(columnaResumen);
}
cmd.Append(" ");
cmd.Append("int");
cmd.Append(")");
String debug = cmd.ToString();
db.ejecutaComando(cmd.ToString());
}
private void buttonObtenInfo_Click(object sender, EventArgs e)
{
DBConnection myDB = new DBConnection(cadenaDeConexion);
myDB.connect();
DataTable dt = myDB.ejecutaQuery(textBoxQuery.Text);
dataGridViewQuery.DataSource = dt;
tipos = new List<String>();
for (int i = 0; i < dt.Columns.Count; i++)
{
switch (dt.Columns[i].DataType.ToString()) {
case "System.Int32":
tipos.Add("int");
break;
case "System.Int64":
tipos.Add("bigint");
break;
case "System.String":
tipos.Add("varchar(500)");
break;
case "System.Byte[]":
tipos.Add("varbinary(50)");
break;
case "System.Int16":
tipos.Add("smallint");
break;
case "System.Boolean":
tipos.Add("bit");
break;
default:
MessageBox.Show("No encontrado " + dt.Columns[i].DataType.ToString());
tipos.Add("varchar(500)");
break;
}
}
checkedListBoxColumna.Items.Clear();
checkedListBoxResumen.Items.Clear();
TodosLosCampos = new List<string>();
foreach(DataGridViewColumn column in dataGridViewQuery.Columns){
checkedListBoxColumna.Items.Add(column.Name);
checkedListBoxResumen.Items.Add(column.Name);
TodosLosCampos.Add(column.Name);
}
}
private void generaQuery()
{
// agrupar por los que tienen 0, los
DBConnection db = new DBConnection(CadenaDeConexion);
db.connect();
StringBuilder cmd = new StringBuilder();
cmd.Append("IF OBJECT_ID('dbo.#temp', 'U') IS NOT NULL ");
cmd.Append(" DROP TABLE dbo.#temp ");
cmd.Append(" create table dbo.#temp ( ");
for(int i=0; i<todoCampo.Count;i++){
if(i!= 0){
cmd.Append(",");
}
cmd.Append(todoCampo[i]);
cmd.Append(" ");
cmd.Append(todoTipo[i]);
}
cmd.Append(" ) ");
cmd.Append(" Insert into dbo.#temp ");
cmd.Append(query);
/* generar tabla estilo lo siguiente
*
IF OBJECT_ID('dbo.#temp', 'U') IS NOT NULL DROP TABLE dbo.#temp
create table dbo.#temp ( usuario int,problema int,puntos int,hora bigint,primero int )
Insert into dbo.#temp SELECT * FROM [Karelotitlan].[dbo].[UsuarioProblema]
select CAST( CAST(usuario AS nvarchar) as text), CAST( CAST(problema AS nvarchar) AS text), sum(primero) from
dbo.#temp
group by usuario,problema
Insert into Karelotitlan.dbo.cube
select CAST( CAST(usuario AS nvarchar) as text), CAST( CAST(problema AS nvarchar) AS text), sum(primero) from
dbo.#temp
group by usuario,problema
drop table dbo.#temp
*
*/
cmd.Append(" Insert into ");
cmd.Append(Esquema);
cmd.Append(".dbo.cube ");
cmd.Append("select ");
// lista de parametreos si tiene 0 hacer CAST si no poner 0
for (int i = 0; i < campos.Count; i++)
{
if (i != 0)
{
cmd.Append(",");
}
if (elegidos[i] == 0)
{
//cmd.Append(" CAST ( CAST ( ");
cmd.Append(campos[i]);
//cmd.Append(" AS nvarchar) AS text) ");
}
else
{
cmd.Append(" '(ALL)' ");
}
}
if (campos.Count > 0)
{
cmd.Append(",");
}
switch (this.tipoF)
{
case tipoFuncion.cont:
cmd.Append(" count(*) ");
break;
case tipoFuncion.avg:
cmd.Append(" AVG(");
cmd.Append(columnaResumen);
cmd.Append(") ");
break;
case tipoFuncion.max:
cmd.Append(" MAX(");
cmd.Append(columnaResumen);
cmd.Append(") ");
break;
case tipoFuncion.min:
cmd.Append(" MIN(");
cmd.Append(columnaResumen);
cmd.Append(") ");
break;
case tipoFuncion.suma:
cmd.Append(" SUM(");
cmd.Append(columnaResumen);
cmd.Append(") ");
break;
}
cmd.Append(" from dbo.#temp ");
// lista de grupos
int agrupados = 0;
for (int i = 0; i < campos.Count; i++)
{
if (elegidos[i] == 0)
{
if (agrupados > 0)
{
cmd.Append(",");
}
else {
cmd.Append(" group by ");
}
cmd.Append(campos[i]);
agrupados++;
}
}
cmd.Append(" drop table dbo.#temp ");
String debug = cmd.ToString();
db.ejecutaComando(cmd.ToString());
}