private void populateComboes()
{
TdConnectionStringBuilder stringBuilder = new TdConnectionStringBuilder();
stringBuilder.CommandTimeout = 300;
stringBuilder.ConnectionTimeout = 100;
stringBuilder.DataSource = host;
stringBuilder.UserId = uid;
stringBuilder.Password = pwd;
using (TdConnection dbConnection = new TdConnection(stringBuilder.ConnectionString)) {
dbConnection.Open();
TdDataAdapter adapter = new TdDataAdapter();
DataTable dt = new DataTable();
TdCommand myCommand = new TdCommand("select databasename from dbc.databases", dbConnection);
TdDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
cboDatabaseList.Items.Add(myReader[0].ToString().Trim());
}
myReader.Close();
myCommand.Dispose();
}
}
private void cboDatabaseList_TextChanged(object sender, EventArgs e)
{
TdConnectionStringBuilder stringBuilder = new TdConnectionStringBuilder();
stringBuilder.CommandTimeout = 300;
stringBuilder.ConnectionTimeout = 100;
stringBuilder.DataSource = host;
stringBuilder.UserId = uid;
stringBuilder.Password = pwd;
using (TdConnection dbConnection = new TdConnection(stringBuilder.ConnectionString)) {
dbConnection.Open();
TdDataAdapter adapter = new TdDataAdapter();
DataTable dt = new DataTable();
TdCommand myCommand = new TdCommand("select tablename from dbc.TablesVX where databasename='" + cboDatabaseList.Text + "'", dbConnection);
TdDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
cboTables.Items.Add(myReader[0].ToString().Trim());
}
myReader.Close();
myCommand.Dispose();
}
}
private void RunTeradata(List <SQLResult> result, string cmd)
{
try
{
TdCommand toGo = this.TdConnection.CreateCommand();
toGo.CommandTimeout = 3600 * 12;
toGo.CommandText = cmd;
TdDataReader reader = toGo.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int nCol = reader.FieldCount;
SQLResult newItem = new SQLResult();
if (nCol > 0)
{
newItem.Column0 = reader.IsDBNull(0) ? String.Empty : reader.GetValue(0).ToString();
}
if (nCol > 1)
{
newItem.Column1 = reader.IsDBNull(1) ? String.Empty : reader.GetValue(1).ToString();
}
if (nCol > 2)
{
newItem.Column2 = reader.IsDBNull(2) ? String.Empty : reader.GetValue(2).ToString();
}
if (nCol > 3)
{
newItem.Column3 = reader.IsDBNull(3) ? String.Empty : reader.GetValue(3).ToString();
}
if (nCol > 4)
{
newItem.Column4 = reader.IsDBNull(4) ? String.Empty : reader.GetValue(4).ToString();
}
if (nCol > 5)
{
newItem.Column5 = reader.IsDBNull(5) ? String.Empty : reader.GetValue(5).ToString();
}
if (nCol > 6)
{
newItem.Column6 = reader.IsDBNull(6) ? String.Empty : reader.GetValue(6).ToString();
}
result.Add(newItem);
}
}
reader.Close();
toGo.Dispose();
}
catch (Exception ex)
{
throw;
}
}
public List <NumeralCambiario> Get_NumeralCambiarioDAL()
{
List <NumeralCambiario> list = new List <NumeralCambiario>();
using (TdConnection oSqlConnection = new TdConnection(Cnn))
{
try
{
oSqlConnection.Open();
using (TdCommand oSqlCmd = new TdCommand())
{
oSqlCmd.Parameters.Clear();
oSqlCmd.CommandText = "Select * from " + @instancia + ".V_D_RCNumeralCambiario ORDER BY Desc_NumeralCambiario ";
oSqlCmd.CommandTimeout = 30;
oSqlCmd.Connection = oSqlConnection;
TdDataReader oReader = oSqlCmd.ExecuteReader();
if (oReader != null)
{
if (oReader.HasRows)
{
while (oReader.Read())
{
list.Add(new NumeralCambiario()
{
Sk_RCNumeralCambiario = int.Parse(oReader["Sk_RCNumeralCambiario"].ToString()),
Desc_NumeralCambiario = oReader["Desc_NumeralCambiario"].ToString(),
});
}
oReader.Close();
}
oReader.Dispose();
}
}
oSqlConnection.Close();
}
catch (SqlException ex)
{
throw ex;
}
catch (IndexOutOfRangeException ex)
{
throw ex;
}
catch (TdException ex)
{
throw ex;
}
catch (FormatException ex)
{
throw ex;
}
}
return(list);
}
public int GetHistoryid(int DefectID)
{
//Define the Command Object
int ID = 0;
string defect1 = "";
//IDDefect = Convert.ToInt32(DefectID);
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdCommand AdvancedDefectSearch =
new Teradata.Client.Provider.TdCommand("Select max(defecthistid)+ 1 as test from coebatch.tbl_Defect_history where DefectID = " + DefectID + ";", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
//AdvancedDefectSearch.ExecuteNonQuery();
TdDataReader r = AdvancedDefectSearch.ExecuteReader();
if (r.Read())
{
defect1 = r["test"].ToString();
}
r.Close();
if (defect1 == "")
{
con.Close();
return(ID);
}
else
{
ID = Convert.ToInt32(defect1);
return(ID);
}
// nextDefectId = (int)cmdNewDefect.ExecuteScalar();
}
catch (TdException ex)
{
return(ID);
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
}
public int GetDefectcount()
{
string query = "";
int ID = 0;
TdCommand cmd = new TdCommand(query);
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdCommand AdvancedDefectSearch =
new Teradata.Client.Provider.TdCommand("select count(*) as test from coebatch.tbl_defect ", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
//AdvancedDefectSearch.ExecuteNonQuery();
TdDataReader r = AdvancedDefectSearch.ExecuteReader();
if (r.Read())
{
query = r["test"].ToString();
}
r.Close();
if (query == "")
{
con.Close();
return(ID);
}
else
{
ID = Convert.ToInt32(query);
}
// nextDefectId = (int)cmdNewDefect.ExecuteScalar();
}
catch (TdException ex)
{
return(ID);
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(ID);
}
public int GetNextDefectID()
{
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdCommand cmdNewDefect = new Teradata.Client.Provider.TdCommand("SELECT MAX(DefectId)+1 as test FROM coebatch.tbl_Defect", con);
int nextDefectId = 0;
string defectid = "";
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
//nextDefectId = (int)cmdNewDefect.ExecuteScalar();
TdDataReader r = cmdNewDefect.ExecuteReader();
if (r.Read())
{
defectid = r["test"].ToString();
}
r.Close();
// nextDefectId = cmdNewDefect.ExecuteScalar();
if (defectid == "")
{
nextDefectId = 1;
}
else
{
nextDefectId = Convert.ToInt32(defectid);
}
}
catch (Teradata.Client.Provider.TdException ex)
{
//Throw the exception to calling environment
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(nextDefectId);
}
public List <ResultadoNodo> Get_RC_EstructuraAgregacionNumeralesByIdNameDAL(int Id, string desc)
{
List <ResultadoNodo> list = new List <ResultadoNodo>();
using (TdConnection oSqlConnection = new TdConnection(Cnn))
{
try
{
oSqlConnection.Open();
using (TdCommand oSqlCmd = new TdCommand())
{
oSqlCmd.Parameters.Clear();
oSqlCmd.CommandText = "SEL NOD.Sk_NodoContable Sk_NodoContable, NOD.Desc_NodoContable, NOD.Id_NodoContable, COALESCE(PAD.Id_NodoContable, 0) Id_NodoContablePadre, PAD.Desc_NodoContable Desc_NodoContablePadre ," +
" MAX(CASE WHEN TRIM(AGR.Id_Fuente)='1' THEN agr.Sk_RCNumeralCambiario ELSE 0 END ) Sk_RCNumeralCambiario, NOD.Num_Nivel,NOD.Num_Orden," +
" MAX(CASE WHEN TRIM(AGR.Id_Fuente)='2' THEN AGR.Sk_RCNumeralCambiario ELSE 0 END) idnumeralcco FROM " + @instancia + ".V_RC_EstructuraAgregacionNumerales EST"
+ " JOIN " + @instancia + ".V_RC_NodoContableAgregacionNumerales NOD "
+ " ON EST.Id_Estructura = NOD.Id_Estructura"
+ " LEFT JOIN " + @instancia + ".V_RC_NodoContableAgregacionNumerales PAD"
+ " ON NOD.sk_NodoContablePadre = PAD.sk_NodoContable"
+ " LEFT JOIN " + @instancia + ".V_RC_Rel_NodoAgrNumerales_NumeralCambiario AGR"
+ " ON AGR.Sk_NodoContable = NOD.Sk_NodoContable"
+ " where EST.Id_Estructura = ? and EST.Desc_Estructura = ? and EST.Cb_Eliminado <> 'S' and NOD.Cb_eliminado <> 'S' order by NOD.Num_Nivel,NOD.Num_Orden" +
" GROUP BY 1,2,3,4,5,7,8;";
oSqlCmd.CommandType = CommandType.Text;
oSqlCmd.CommandTimeout = 30;
oSqlCmd.Connection = oSqlConnection;
TdParameter idP = oSqlCmd.CreateParameter();
idP.DbType = DbType.Int64;
idP.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(idP);
idP.Value = Id;
TdParameter descP = oSqlCmd.CreateParameter();
descP.DbType = DbType.String;
descP.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(descP);
descP.Value = desc;
oSqlCmd.Prepare();
TdDataReader oReader = oSqlCmd.ExecuteReader();
if (oReader != null)
{
if (oReader.HasRows)
{
while (oReader.Read())
{
ResultadoNodo nodo = new ResultadoNodo();
nodo.Sk_NodoContable = int.Parse(oReader["Sk_NodoContable"].ToString());
nodo.name = oReader["Desc_NodoContable"].ToString();
nodo.Id_NodoContable = int.Parse(oReader["Id_NodoContable"].ToString());
nodo.Id_NodoContablePadre = int.Parse(oReader["Id_NodoContablePadre"].ToString());
nodo.Desc_NodoContablePadre = oReader["Desc_NodoContablePadre"].ToString();
nodo.Sk_RCNumeralCambiario = int.Parse(oReader["Sk_RCNumeralCambiario"].ToString());
nodo.level = int.Parse(oReader["Num_Nivel"].ToString());
nodo.idnumeralcco = int.Parse(oReader["idnumeralcco"].ToString());
nodo.Sk_NodoContable = int.Parse(oReader["Sk_NodoContable"].ToString());
nodo.orden = int.Parse(oReader["Num_Orden"].ToString());
nodo.formulacion = formulacionDAL.Get_RC_EstructuraAgregacionNumeralesByIdNameDAL(nodo.Sk_NodoContable);
list.Add(nodo);
}
oReader.Close();
}
oReader.Dispose();
}
}
oSqlConnection.Close();
return(list);
}
catch (SqlException ex)
{
throw ex;
}
catch (IndexOutOfRangeException ex)
{
throw ex;
}
catch (TdException ex)
{
throw ex;
}
catch (FormatException ex)
{
throw ex;
}
}
}
public List <RC_ResultadoAgregacionNumerales> Get_RC_ResultadoAgregacionNumeralesBySkDAL(int Id_Estructura, int Sk_Consulta, int Id_Periodicidad)
{
List <RC_ResultadoAgregacionNumerales> list = new List <RC_ResultadoAgregacionNumerales>();
using (TdConnection oSqlConnection = new TdConnection(Cnn))
{
try
{
oSqlConnection.Open();
using (TdCommand oSqlCmd = new TdCommand())
{
oSqlCmd.Parameters.Clear();
if (Id_Periodicidad == 1)
{
oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta," +
" DG.Anio ||'-'|| CASE WHEN LENGTH (TRIM(DG.Id_Mes))=1 THEN '0' || TRIM(DG.Id_Mes) ELSE TRIM(DG.Id_Mes) END ||'-'|| CASE WHEN LENGTH(TRIM(DG.DiaDelMes)) = 1 THEN '0' || TRIM(DG.DiaDelMes) ELSE TRIM(DG.DiaDelMes) end Fecha_DeclaracionInicial, " +
"NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " +
" from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " +
" INNER JOIN " + instancia + ".V_DG_Fecha DG " +
" ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " +
" INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " +
" ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " +
" INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " +
" ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " +
" WHERE NOD.Id_Estructura= " + Id_Estructura + "" +
" AND RAN.Sk_Consulta = " + Sk_Consulta + " " +
" GROUP BY 1,2,3,4,5,6,7 " +
" ORDER BY 1, 6 desc";
}
else if (Id_Periodicidad == 2)
{
oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta," +
" DG.Anio ||'-'|| CASE WHEN LENGTH (TRIM(DG.id_semana))=1 THEN '0' || TRIM(DG.id_semana) else TRIM(DG.id_semana) END Fecha_DeclaracionInicial, NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " +
" from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " +
" INNER JOIN " + instancia + ".V_DG_Fecha DG " +
" ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " +
" INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " +
" ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " +
" INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " +
" ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " +
" WHERE NOD.Id_Estructura= " + Id_Estructura + "" +
" AND RAN.Sk_Consulta = " + Sk_Consulta + " " +
" GROUP BY 1,2,3,4,5,6,7 " +
" ORDER BY 1, 6 desc";
}
else if (Id_Periodicidad == 3)
{
oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta," +
" DG.Anio ||'-'|| CASE WHEN LENGTH (TRIM(DG.Id_Mes))=1 THEN '0' || TRIM(DG.Id_Mes) ELSE TRIM(DG.Id_Mes) end Fecha_DeclaracionInicial, " +
"NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " +
" from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " +
" INNER JOIN " + instancia + ".V_DG_Fecha DG " +
" ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " +
" INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " +
" ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " +
" INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " +
" ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " +
" WHERE NOD.Id_Estructura= " + Id_Estructura + "" +
" AND RAN.Sk_Consulta = " + Sk_Consulta + " " +
" GROUP BY 1,2,3,4,5,6,7 " +
" ORDER BY 1, 6 desc";
}
else if (Id_Periodicidad == 4)
{
oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta, " +
"DG.Anio || '-' || CASE WHEN trim(DG.Id_Trimestre) = '1' THEN 'I ' WHEN trim(DG.Id_Trimestre) = '2' THEN 'II ' WHEN trim(DG.Id_Trimestre) = '3' THEN 'III ' WHEN trim(DG.Id_Trimestre) = '4' THEN 'IV ' END Fecha_DeclaracionInicial," +
"NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " +
" from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " +
" INNER JOIN " + instancia + ".V_DG_Fecha DG " +
" ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " +
" INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " +
" ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " +
" INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " +
" ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " +
" WHERE NOD.Id_Estructura= " + Id_Estructura + "" +
" AND RAN.Sk_Consulta = " + Sk_Consulta + " " +
" GROUP BY 1,2,3,4,5,6,7 " +
" ORDER BY 1, 6 desc";
}
else if (Id_Periodicidad == 5)
{
oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta, " +
"DG.ANIO || '-'|| CASE WHEN trim(DG.Id_Semestre)='1' THEN 'I' WHEN trim(DG.Id_Semestre) = '2' THEN 'II' END Fecha_DeclaracionInicial," +
" NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " +
" from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " +
" INNER JOIN " + instancia + ".V_DG_Fecha DG " +
" ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " +
" INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " +
" ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " +
" INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " +
" ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " +
" WHERE NOD.Id_Estructura= " + Id_Estructura + "" +
" AND RAN.Sk_Consulta = " + Sk_Consulta + " " +
" GROUP BY 1,2,3,4,5,6,7 " +
" ORDER BY 1, 6 desc";
}
else if (Id_Periodicidad == 6)
{
oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta," +
" SUBSTR(CAST(Sk_Fecha AS VARCHAR(4)),1,2 ) ||'-'|| SUBSTR(CAST(Sk_Fecha AS VARCHAR(4)),3,4 ) Fecha_DeclaracionInicial, " +
" NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " +
" from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " +
" INNER JOIN " + instancia + ".V_DG_Fecha DG " +
" ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " +
" INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " +
" ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " +
" INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " +
" ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " +
" WHERE NOD.Id_Estructura= " + Id_Estructura + "" +
" AND RAN.Sk_Consulta = " + Sk_Consulta + " " +
" GROUP BY 1,2,3,4,5,6,7 " +
" ORDER BY 1, 6 desc";
}
oSqlCmd.CommandTimeout = 30;
oSqlCmd.Connection = oSqlConnection;
TdDataReader oReader = oSqlCmd.ExecuteReader();
if (oReader != null)
{
if (oReader.HasRows)
{
while (oReader.Read())
{
RC_ResultadoAgregacionNumerales item = new RC_ResultadoAgregacionNumerales();
item.Id_NodoContable = int.Parse(oReader["Id_NodoContable"].ToString());
item.Id_Estructura = int.Parse(oReader["Id_Estructura"].ToString());
item.Desc_NodoContable = oReader["Desc_NodoContable"].ToString();
item.Fecha_Consulta = DateTime.Parse(oReader["Fecha_Consulta"].ToString());
item.Fecha_DeclaracionInicial = oReader["Fecha_DeclaracionInicial"].ToString();
item.Cv_ValorUSD = decimal.Parse(oReader["Cv_ValorUSD"].ToString());
list.Add(item);
}
oReader.Close();
}
oReader.Dispose();
}
}
oSqlConnection.Close();
}
catch (SqlException ex)
{
throw ex;
}
catch (IndexOutOfRangeException ex)
{
throw ex;
}
catch (TdException ex)
{
throw ex;
}
catch (FormatException ex)
{
throw ex;
}
}
return(list);
}
static void readTeradata()
{
string db_name = @"DPRD_SSL_MDM_V"; // @"LabBICC_Test";LabBICC_FIN_DYI
string queryTemplate = @"
SELECT TableName, tablekind, case tablekind when 'V' then RequestText else null end as RequestText
FROM dbc.tablesv
WHERE tablekind in ('V', 'T') AND databasename IN ('{0}')
AND TableName = 'vD_GeoSite'
--AND TableName LIKE 'vD_Equipment_%'
";
//and (TableName like 'MSBI_vF_Fixed%' or TableName like 'MSBI_vD_Report%')
// AND TableName like 'MSBI_%'
//in ('MSBI_vD_KeyFigureGroup', 'vD_KeyFigureGroup', 'vD_KeyFigureGroupCateg')
DbObjectMaster objMaster = new DbObjectMaster();
objMaster.DefaultDatabase = db_name;
string queryString = String.Format(queryTemplate, db_name);
TdConnection cn = new TdConnection();
string connectionString = @"Data Source=maersk6;Database=LabBICC_Test;User Id=UADL_BICC_LOADUSER;Password=Lab@BICC123;Connection Timeout=300;";
string obj_text = "", obj_name = "", obj_type = "";
using (TdConnection connection = new TdConnection(connectionString))
{
//connection.ConnectionTimeout = 300; //covered by connection string
TdCommand cmd = new TdCommand(queryString, connection);
cmd.CommandTimeout = 180;
//cmd.Parameters.Add(new TdParameter("@viewname", "MSBI_vD_Company"));
//cmd.CommandText = queryString;
Console.WriteLine("Acquiring the connection....");
connection.Open();
Console.WriteLine("Getting database object list....");
TdDataReader reader = cmd.ExecuteReader();
//Console.WriteLine("{0} tables found.", reader.RecordsAffected);
while (reader.Read())
{
obj_name = reader["TableName"].ToString().Trim();
obj_type = reader["tablekind"].ToString().Trim().ToUpper();
obj_text = reader["RequestText"].ToString().Trim();
//str = Convert.ToString(cmd.ExecuteScalar());
//str = (string)cmd.ExecuteScalar();
//obj_text = compressQueryText(obj_text);
//str = "[" + str + "]";
obj_name = obj_name.IndexOf(".") >= 0 ? obj_name : db_name + "." + obj_name;
//DbObject obj = new DbObject(obj_name, obj_type, objMaster, db_name, obj_text);
DbObject obj = objMaster.AddNew(obj_name, obj_type, db_name, obj_text);
Console.WriteLine(obj_name);
//Console.WriteLine(obj_text);
Console.WriteLine("::: source objects :::");
foreach (DbObject src in obj.Sources.Values)
{
Console.WriteLine(src.Name);
}
Console.WriteLine("*******************************************");
//objMaster.Add(obj);
}
cmd.Dispose();
connection.Close();
}
objMaster.BuildReferences();
//var json = ApiResponse
//var json = JsonConvert.SerializeObject(objMaster);
//this gets all objects and user drills down to their sources (if any)
//this way some objects may appear in different branches of the tree
DbObjectTree tree = objMaster.getDbObjectTree();
//this starts from the objects that have no targets (no one is sourced from them)
//and user drills down to the sources, nvigating to the other objects this way
//DbObjectTree tree = objMaster.getDbObjectTreeFlowEnd();
tree.GroupChildrenBySchema();
tree.SortTree();
tree.AddIcons();
//tree.CleanParents();
var json = JsonConvert.SerializeObject(tree);
//Console.WriteLine(json);
File.WriteAllText(@"C:\TEMP\views.json", json);
//File.WriteAllText(@"\\SCRBADLDK003868\db\views.json", json);
}
public List <RC_PeriodicidadAgregacionNumerales> Get_RC_PeriodicidadAgregacionNumeralesDAL(int id_fuente)
{
List <RC_PeriodicidadAgregacionNumerales> list = new List <RC_PeriodicidadAgregacionNumerales>();
using (TdConnection oSqlConnection = new TdConnection(Cnn))
{
try
{
oSqlConnection.Open();
using (TdCommand oSqlCmd = new TdCommand())
{
oSqlCmd.Parameters.Clear();
if (id_fuente == 1)
{
oSqlCmd.CommandText = "Select * from " + @instancia + ".V_RC_PeriodicidadAgregacionNumerales order by Desc_Periodicidad ";
}
else
{
oSqlCmd.CommandText = "Select * from " + @instancia + ".V_RC_PeriodicidadAgregacionNumerales where Id_Periodicidad >2 order by Desc_Periodicidad ";
}
oSqlCmd.CommandTimeout = 30;
oSqlCmd.Connection = oSqlConnection;
TdDataReader oReader = oSqlCmd.ExecuteReader();
if (oReader != null)
{
if (oReader.HasRows)
{
while (oReader.Read())
{
RC_PeriodicidadAgregacionNumerales item = new RC_PeriodicidadAgregacionNumerales();
item.Id_Periodicidad = int.Parse(oReader["Id_Periodicidad"].ToString());
item.Desc_Periodicidad = oReader["Desc_Periodicidad"].ToString();
item.Sk_Lote = int.Parse(oReader["Sk_Lote"].ToString());
item.Sk_Lote_Upd = null;
item.Cod_Severidad = int.Parse(oReader["Cod_Severidad"].ToString());
list.Add(item);
}
oReader.Close();
}
oReader.Dispose();
}
}
oSqlConnection.Close();
}
catch (SqlException ex)
{
throw ex;
}
catch (IndexOutOfRangeException ex)
{
throw ex;
}
catch (TdException ex)
{
throw ex;
}
catch (FormatException ex)
{
throw ex;
}
}
return(list);
}
public List <ResultadoFormulacion> Get_RC_EstructuraAgregacionNumeralesByIdNameDAL(int Sk_NodoContable)
{
List <ResultadoFormulacion> list = new List <ResultadoFormulacion>();
using (TdConnection oSqlConnection = new TdConnection(Cnn))
{
try
{
oSqlConnection.Open();
using (TdCommand oSqlCmd = new TdCommand())
{
oSqlCmd.Parameters.Clear();
oSqlCmd.CommandText = "SEL PAD.Id_NodoContable, PAD.Desc_NodoContable, FORM.Desc_Signo FROM " + @instancia + ".V_RC_NodoContableAgregacionNumerales NOD"
+ " JOIN " + @instancia + ".V_RC_FormulacionVerticalNodoagrNumerales FORM"
+ " ON FORM.Sk_NodoContable = NOD.Sk_NodoContable"
+ " LEFT JOIN " + @instancia + ".V_RC_NodoContableAgregacionNumerales PAD"
+ " ON FORM.Sk_NodoContableRelacionado = PAD.sk_NodoContable"
+ " where NOD.Sk_NodoContable = ? and NOD.Cb_Eliminado <> 'S' ;";
oSqlCmd.CommandType = CommandType.Text;
oSqlCmd.CommandTimeout = 60;
oSqlCmd.Connection = oSqlConnection;
TdParameter idP = oSqlCmd.CreateParameter();
idP.DbType = DbType.Int64;
idP.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(idP);
idP.Value = Sk_NodoContable;
oSqlCmd.Prepare();
TdDataReader oReader = oSqlCmd.ExecuteReader();
if (oReader != null)
{
if (oReader.HasRows)
{
while (oReader.Read())
{
list.Add(new ResultadoFormulacion()
{
name = oReader["Desc_NodoContable"].ToString(),
Id_NodoContable = int.Parse(oReader["Id_NodoContable"].ToString()),
Signo = oReader["Desc_Signo"].ToString()
});
}
oReader.Close();
}
oReader.Dispose();
}
}
oSqlConnection.Close();
}
catch (SqlException ex)
{
throw ex;
}
catch (IndexOutOfRangeException ex)
{
throw ex;
}
catch (TdException ex)
{
throw ex;
}
catch (FormatException ex)
{
throw ex;
}
}
return(list);
}
public List <RC_ConsultaAgregacionNumerales> Get_RC_ConsultaAgregacionNumeralesBySkDAL(int Id_Estructura, int Sk_Consulta)
{
List <RC_ConsultaAgregacionNumerales> list = new List <RC_ConsultaAgregacionNumerales>();
using (TdConnection oSqlConnection = new TdConnection(Cnn))
{
try
{
oSqlConnection.Open();
using (TdCommand oSqlCmd = new TdCommand())
{
oSqlCmd.Parameters.Clear();
oSqlCmd.CommandText = "Select EAN.Desc_Estructura Desc_Estructura, PAN.Desc_Periodicidad Desc_Periodicidad, FAN.Desc_Fuente Desc_Fuente, CAN.* from " + @instancia + ".V_RC_ConsultaAgregacionNumerales CAN " +
"INNER JOIN " + @instancia + ".V_RC_EstructuraAgregacionNumerales EAN" +
" ON (EAN.Id_Estructura=CAN.Id_Estructura)" +
" INNER JOIN " + instancia + ".V_RC_PeriodicidadAgregacionNumerales PAN" +
" ON (trim(CAN.Id_Periodicidad)=TRIM(PAN.Id_Periodicidad))" +
" INNER JOIN " + instancia + ".V_RC_FuenteAgregacionNumerales FAN" +
" ON(trim(CAN.Id_Fuente)=TRIM(FAN.Id_Fuente))" +
" Where CAN.Sk_Consulta=" + Sk_Consulta + "" +
" AND CAN.Id_Estructura=" + Id_Estructura + "";
oSqlCmd.CommandTimeout = 30;
oSqlCmd.Connection = oSqlConnection;
TdDataReader oReader = oSqlCmd.ExecuteReader();
if (oReader != null)
{
if (oReader.HasRows)
{
while (oReader.Read())
{
RC_ConsultaAgregacionNumerales item = new RC_ConsultaAgregacionNumerales();
item.Sk_Consulta = int.Parse(oReader["Sk_Consulta"].ToString());
item.Desc_Estructura = oReader["Desc_Estructura"].ToString();
item.Desc_Periodicidad = oReader["Desc_Periodicidad"].ToString();
item.Desc_Fuente = oReader["Desc_Fuente"].ToString();
item.Id_Estructura = int.Parse(oReader["Id_Estructura"].ToString());
item.Fecha_Consulta = DateTime.Parse(oReader["Fecha_Consulta"].ToString());
item.Id_Fuente = int.Parse(oReader["Id_Fuente"].ToString());
item.Fecha_Inicial = DateTime.Parse(oReader["Fecha_Inicial"].ToString());
item.Fecha_Final = DateTime.Parse(oReader["Fecha_Final"].ToString());
item.Id_Periodicidad = int.Parse(oReader["Id_Periodicidad"].ToString());
item.Nombre_UsuarioCreacion = oReader["Nombre_UsuarioCreacion"].ToString();
item.Fecha_Creacion = DateTime.Parse(oReader["Fecha_Creacion"].ToString());
item.Sk_Lote = int.Parse(oReader["Sk_Lote"].ToString());
item.Sk_Lote_Upd = null;
item.Cod_Severidad = int.Parse(oReader["Cod_Severidad"].ToString());
list.Add(item);
}
oReader.Close();
}
oReader.Dispose();
}
}
oSqlConnection.Close();
}
catch (SqlException ex)
{
throw ex;
}
catch (IndexOutOfRangeException ex)
{
throw ex;
}
catch (TdException ex)
{
throw ex;
}
catch (FormatException ex)
{
throw ex;
}
}
return(list);
}
public List <RC_ConsultaAgregacionNumerales> Get_SkConsulta(int Id_Estructura, string Fecha_Consulta, int Id_Fuente, string Fecha_Inicial, string Fecha_Final, int Id_Periodicidad)
{
List <RC_ConsultaAgregacionNumerales> list = new List <RC_ConsultaAgregacionNumerales>();
using (TdConnection oSqlConnection = new TdConnection(Cnn))
{
try
{
oSqlConnection.Open();
using (TdCommand oSqlCmd = new TdCommand())
{
oSqlCmd.Parameters.Clear();
oSqlCmd.CommandText = "Select Sk_Consulta, Id_Estructura from " + @instancia + ".V_RC_ConsultaAgregacionNumerales "
+ " where Id_Estructura = ? and CAST(CAST(Fecha_Consulta AS DATE FORMAT 'yyyymmdd' ) AS VARCHAR(8)) = ?" +
" AND Id_Fuente= ? AND CAST(CAST(Fecha_Inicial AS DATE FORMAT 'yyyymmdd' ) AS VARCHAR(8))=? AND " +
"CAST(CAST(Fecha_Final AS DATE FORMAT 'yyyymmdd' ) AS VARCHAR(8))=? " +
" AND Id_Periodicidad=? ;";
oSqlCmd.CommandType = CommandType.Text;
oSqlCmd.CommandTimeout = 30;
oSqlCmd.Connection = oSqlConnection;
TdParameter Id_EstructuraP = oSqlCmd.CreateParameter();
Id_EstructuraP.DbType = DbType.String;
Id_EstructuraP.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_EstructuraP);
Id_EstructuraP.Value = Id_Estructura;
TdParameter Fecha_ConsultaP = oSqlCmd.CreateParameter();
Fecha_ConsultaP.DbType = DbType.String;
Fecha_ConsultaP.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Fecha_ConsultaP);
Fecha_ConsultaP.Value = Fecha_Consulta;
TdParameter Id_FuenteP = oSqlCmd.CreateParameter();
Id_FuenteP.DbType = DbType.String;
Id_FuenteP.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_FuenteP);
Id_FuenteP.Value = Id_Fuente;
TdParameter Fecha_Inicialp = oSqlCmd.CreateParameter();
Fecha_Inicialp.DbType = DbType.String;
Fecha_Inicialp.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Fecha_Inicialp);
Fecha_Inicialp.Value = Fecha_Inicial;
TdParameter Fecha_Finalp = oSqlCmd.CreateParameter();
Fecha_Finalp.DbType = DbType.String;
Fecha_Finalp.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Fecha_Finalp);
Fecha_Finalp.Value = Fecha_Final;
TdParameter Id_Periodicidadp = oSqlCmd.CreateParameter();
Id_Periodicidadp.DbType = DbType.String;
Id_Periodicidadp.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_Periodicidadp);
Id_Periodicidadp.Value = Id_Periodicidad;
oSqlCmd.Prepare();
TdDataReader oReader = oSqlCmd.ExecuteReader();
if (oReader != null)
{
if (oReader.HasRows)
{
while (oReader.Read())
{
RC_ConsultaAgregacionNumerales item = new RC_ConsultaAgregacionNumerales();
item.Sk_Consulta = int.Parse(oReader["Sk_Consulta"].ToString());
item.Id_Estructura = int.Parse(oReader["Id_Estructura"].ToString());
list.Add(item);
}
oReader.Close();
}
oReader.Dispose();
}
}
oSqlConnection.Close();
}
catch (SqlException ex)
{
throw ex;
}
catch (IndexOutOfRangeException ex)
{
throw ex;
}
catch (TdException ex)
{
throw ex;
}
catch (FormatException ex)
{
throw ex;
}
}
return(list);
}
public List <RC_ConsultaAgregacionNumerales> Get_RC_ConsultaAgregacionNumeralesDAL(int Id_Estructura, DateTime Fecha_Consulta, int Id_Fuente, DateTime Fecha_Inicial, DateTime Fecha_Final, int Id_Periodicidad)
{
List <RC_ConsultaAgregacionNumerales> list = new List <RC_ConsultaAgregacionNumerales>();
using (TdConnection oSqlConnection = new TdConnection(Cnn))
{
try
{
oSqlConnection.Open();
using (TdCommand oSqlCmd = new TdCommand())
{
oSqlCmd.Parameters.Clear();
oSqlCmd.CommandText = "Select * from " + @instancia + ".V_RC_ConsultaAgregacionNumerales "
+ " where Id_Estructura = ? and Fecha_Consulta = ?" +
" AND Id_Fuente= ? AND Fecha_Inicial=? AND Fecha_Final=? " +
" AND Id_Periodicidad=? ;";
oSqlCmd.CommandType = CommandType.Text;
oSqlCmd.CommandTimeout = 30;
oSqlCmd.Connection = oSqlConnection;
TdParameter Id_EstructuraP = oSqlCmd.CreateParameter();
Id_EstructuraP.DbType = DbType.String;
Id_EstructuraP.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_EstructuraP);
Id_EstructuraP.Value = Id_Estructura;
TdParameter Fecha_ConsultaP = oSqlCmd.CreateParameter();
Fecha_ConsultaP.DbType = DbType.DateTime;
Fecha_ConsultaP.Direction = ParameterDirection.Input;
Fecha_ConsultaP.IsNullable = true;
object v;
if (Fecha_Consulta != null)
{
v = Fecha_Consulta;
}
else
{
v = System.DBNull.Value;
}
oSqlCmd.Parameters.Add(Fecha_ConsultaP);
Fecha_ConsultaP.Value = v;
TdParameter Id_FuenteP = oSqlCmd.CreateParameter();
Id_FuenteP.DbType = DbType.String;
Id_FuenteP.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_FuenteP);
Id_FuenteP.Value = Id_Fuente;
TdParameter Fecha_Inicialp = oSqlCmd.CreateParameter();
Fecha_Inicialp.DbType = DbType.DateTime;
Fecha_Inicialp.Direction = ParameterDirection.Input;
Fecha_Inicialp.IsNullable = true;
if (Fecha_Inicial != null)
{
v = Fecha_Inicial;
}
else
{
v = System.DBNull.Value;
}
oSqlCmd.Parameters.Add(Fecha_Inicialp);
Fecha_Inicialp.Value = v;
TdParameter Fecha_Finalp = oSqlCmd.CreateParameter();
Fecha_Finalp.DbType = DbType.DateTime;
Fecha_Finalp.Direction = ParameterDirection.Input;
Fecha_Finalp.IsNullable = true;
if (Fecha_Final != null)
{
v = Fecha_Final;
}
else
{
v = System.DBNull.Value;
}
oSqlCmd.Parameters.Add(Fecha_Finalp);
Fecha_Finalp.Value = v;
TdParameter Id_Periodicidadp = oSqlCmd.CreateParameter();
Id_Periodicidadp.DbType = DbType.String;
Id_Periodicidadp.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_Periodicidadp);
Id_Periodicidadp.Value = Id_Periodicidad;
oSqlCmd.Prepare();
TdDataReader oReader = oSqlCmd.ExecuteReader();
if (oReader != null)
{
if (oReader.HasRows)
{
while (oReader.Read())
{
RC_ConsultaAgregacionNumerales item = new RC_ConsultaAgregacionNumerales();
item.Id_Estructura = int.Parse(oReader["Id_Estructura"].ToString());
item.Fecha_Consulta = DateTime.Parse(oReader["Fecha_Consulta"].ToString());
item.Id_Fuente = int.Parse(oReader["Id_Fuente"].ToString());
item.Fecha_Inicial = DateTime.Parse(oReader["Fecha_Inicial"].ToString());
item.Fecha_Final = DateTime.Parse(oReader["Fecha_Final"].ToString());
item.Id_Periodicidad = int.Parse(oReader["Id_Periodicidad"].ToString());
list.Add(item);
}
oReader.Close();
}
oReader.Dispose();
}
}
oSqlConnection.Close();
}
catch (SqlException ex)
{
throw ex;
}
catch (IndexOutOfRangeException ex)
{
throw ex;
}
catch (TdException ex)
{
throw ex;
}
catch (FormatException ex)
{
throw ex;
}
}
return(list);
}
