Example #1
0
        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();
            }
        }
Example #2
0
        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();
            }
        }
Example #3
0
        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;
            }
        }
Example #4
0
        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);
        }
Example #5
0
        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();
            }
        }
Example #6
0
        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);
        }
Example #7
0
        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);
        }
Example #8
0
        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;
                }
            }
        }
Example #9
0
        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);
        }
Example #10
0
        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);

        }
Example #11
0
        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);
        }
Example #12
0
        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);
        }
Example #13
0
        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);
        }
Example #14
0
        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);
        }
Example #15
0
        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);
        }