/// <summary>
/// Logs into the specified project/domain combination
/// </summary>
/// <param name="sender">The sending object</param>
/// <param name="e">The event arguments</param>
private void btnLogin_Click(object sender, System.EventArgs e)
{
string domainName = (string)this.cboDomain.SelectedValue;
string projectName = (string)this.cboProject.SelectedValue;
//Default the Next button to disabled
this.btnNext.Enabled = false;
try
{
TdConnection.Connect(domainName, projectName);
}
catch (Exception exception)
{
MessageBox.Show("Error Logging in to QualityCenter: " + exception.Message, "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Check to see if connected and change the UI as appropriate
if (TdConnection.Connected)
{
MessageBox.Show("You have connected to '" + projectName + "' Successfully", "Authentication", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.btnNext.Enabled = true;
}
else
{
MessageBox.Show("Unable to connect to '" + projectName + "'!", "Authentication", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public int InsertLogin(string Loginuser, string Password)
{
TdConnection con = new TdConnection(strConnString);
int id = 0;
//Define the Command Object
Teradata.Client.Provider.TdCommand cmdAddLogin = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Login Values('" + Loginuser + "','"
+ Password + "')", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdAddLogin.ExecuteNonQuery();
id = 1;
return(id);
}
catch (Teradata.Client.Provider.TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(id);
}
public DataTable returnResult(string query, out string mensaje)
{
try
{
using (TdConnection cn = new TdConnection(conn.StringConnection))
{
cn.Open();
TdCommand cmd = cn.CreateCommand();
cmd.CommandText = @query;
cmd.CommandTimeout = 100000;
TdDataAdapter adapter = new TdDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
var dt = ds.Tables[0];
mensaje = "Se consultaron " + dt.Rows.Count.ToString() + " registros.";
return(dt);
}
}
catch (Exception e)
{
mensaje = e.Message;
return(null);
}
}
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();
}
}
public void UpdateDefect(string Comments, string LoginUser, int DefectID, String DefectName, TdTimestamp Date, String Status, String Cycle, String Description, String Project, String Tester, String TestcasesAssociated, String Module, String Assignedto, TdTimestamp sysdate, String filenames, String filesize, Byte Attachments, int histid, String environment, String Severity, String Projectname, String Rootcause)
{
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdCommand cmdUpdateDefect =
new Teradata.Client.Provider.TdCommand(@"Update coebatch.tbl_Defect set DefectName='" + DefectName + "' ,Status='" + Status + "',SCycle ='" + Cycle + "',Description='" + Description + "',Project='" + Project + "',Tester='" + Tester + "',TestcasesAssociated='" + TestcasesAssociated + "',Module='" + Module + "',AssignedTo='" + Assignedto + "',CloseDate='" + sysdate + "', Environment = '" + environment + "' , Severity = '" + Severity + "', Releases = '" + Projectname + "',Rootcause = '" + Rootcause + "' where DefectID= '" + DefectID + "'", con);
Teradata.Client.Provider.TdCommand cmdInsertComments =
new Teradata.Client.Provider.TdCommand(@"Insert into coebatch.tbl_Comments Values ('" + DefectID + "','" + Comments + "','" + sysdate + "', '" + LoginUser + "' )", con);
DateTime Datetimestamp = DateTime.Now;
Teradata.Client.Provider.TdCommand cmdUpdateDefectHistory =
new Teradata.Client.Provider.TdCommand(@"Insert into coebatch.tbl_Defect_History Values('" + DefectID + "','" + DefectName + "' ,'" + Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','" + sysdate + "','" + histid + "','" + LoginUser + "', '" + environment + "' , '" + Severity + "','" + Projectname + "','" + Rootcause + "') ", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdUpdateDefect.ExecuteNonQuery();
cmdInsertComments.ExecuteNonQuery();
cmdUpdateDefectHistory.ExecuteNonQuery();
}
catch (TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
}
public void InsertDefect(string Loginuser, int DefectID, String DefectName, TdTimestamp Date, String Status, String Cycle, String Description, String Project, String Tester, String TestcasesAssociated, String Module, String Assignedto, TdTimestamp date1, String filenames, String filesize, Byte Attachments, String environment, String severity, String Projectname, String Rootcause)
{
TdConnection con = new TdConnection(strConnString);
// Date = CAST(Date AS FORMAT 'YYYY-MM-DD');
// TdTimestamp dt = new TdTimestamp(Date);
// string dts = dt.ToString("MM/dd/yyyy HH:mm:ss");
//Define the Command Object
Teradata.Client.Provider.TdCommand cmdAddDefect = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Defect Values('" + DefectID + "','"
+ DefectName + "','"
+ Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','"
+ date1 + "','" + filenames + "','" + filesize + "',,'" + environment + "','" + severity + "','" + Projectname + "','" + Rootcause + "')", con);
int DefectIDD = (int)GetNextDefectID();
int histid = 1;
//DefectIDD = DefectIDD - 1;
Teradata.Client.Provider.TdCommand cmdAddDefectHistory = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Defect_History Values('"
+ DefectIDD + "','"
+ DefectName + "','"
+ Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','"
+ date1 + "','" + histid + "','" + Loginuser + "','" + environment + "','" + severity + "', '" + Projectname + "','" + Rootcause + "')", con);
int intLengt = 0;
byte[] objDat;
objDat = new byte[intLengt];
string DataOb = Convert.ToString(objDat);
// cmdAddDefect.Parameters.Add(new Teradata.Client.Provider.TdParameter("@Attachments", (object)objDat));
// cmdAddDefectHistory.Parameters.Add(new Teradata.Client.Provider.TdParameter("@Attachments", (object)objDat));
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdAddDefect.ExecuteNonQuery();
cmdAddDefectHistory.ExecuteNonQuery();
}
catch (Teradata.Client.Provider.TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
}
public Teradata(String user, String pwd)
{
connectionStringBuilder = new TdConnectionStringBuilder();
connectionStringBuilder.AuthenticationMechanism = "LDAP";
connectionStringBuilder.UserId = user;
connectionStringBuilder.Password = pwd;
connectionStringBuilder.DataSource = "10.102.5.28";
cn = new TdConnection();
}
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 TdDataAdapter GetAttachments(int Defectid)
{
//Define the Command Object
int ID = 0;
string defect1 = "";
//IDDefect = Convert.ToInt32(DefectID);
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdDataAdapter AdvancedDefectSearch =
new Teradata.Client.Provider.TdDataAdapter("Select Path from coebatch.tbl_Attachments where DefectID = " + Defectid + ";", con);
try
{
//Open the Connection to connect to the database
con.Open();
return(AdvancedDefectSearch);
//Execute the Query
//AdvancedDefectSearch.ExecuteNonQuery();
//TdDataReader r = AdvancedDefectSearch.ExecuteReader();
//if (r.Read())
//{
// defect1 = r["DefectID"].ToString();
//}
//r.Close();
//if (defect1 == "")
//{
// con.Close();
// return ID;
//}
//else
//{
// ID = 1;
//}
// nextDefectId = (int)cmdNewDefect.ExecuteScalar();
}
catch (TdException ex)
{
// return ID;
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(AdvancedDefectSearch);
}
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();
}
}
static void Main(string[] args)
{
TdConnection cn = new TdConnection();
TdConnectionStringBuilder conStrBuilder = new TdConnectionStringBuilder();
conStrBuilder.DataSource = "DSN";
// conStrBuilder.Database = "optional";
conStrBuilder.UserId = "user"; conStrBuilder.Password = "******";
cn.ConnectionString = conStrBuilder.ConnectionString;
cn.Open();
Console.WriteLine("connection successfull");
}
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);
}
/// <summary>
/// Called if the form is closed
/// </summary>
/// <param name="sender">The sending object</param>
/// <param name="e">The event arguments</param>
private void MainForm_Closing(object sender, CancelEventArgs e)
{
//Disconnect/Logout if connected
if (TdConnection != null)
{
if (TdConnection.Connected)
{
TdConnection.Disconnect();
}
if (TdConnection.LoggedIn)
{
TdConnection.Logout();
}
}
}
public bool testConnection()
{
TdConnection connect = new TdConnection();
connect.ConnectionString = conn.StringConnection;
try
{
connect.Open();
connect.Close();
return(true);
}
catch (Exception e)
{
return(false);
}
}
/// <summary>
/// Tries to reconnect QC if we get an error condition
/// </summary>
public void TryReconnect()
{
try
{
TdConnection.Disconnect();
}
catch (Exception)
{
//Fail quietly
}
string domainName = (string)this.cboDomain.SelectedValue;
string projectName = (string)this.cboProject.SelectedValue;
TdConnection.InitConnectionEx(this.txtServer.Text);
TdConnection.Login(this.txtLogin.Text, this.txtPassword.Text);
TdConnection.Connect(domainName, projectName);
}
private static DataSet GetData(TdCommand cmd)
{
string strConnString = ConfigurationManager.ConnectionStrings["Connstr"].ToString();
using (TdConnection con = new TdConnection(strConnString))
{
using (TdDataAdapter sda = new TdDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
return(ds);
}
}
}
}
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);
}
/// <summary>
///Makes sure that we remain connected to QC during import
/// </summary>
public void EnsureConnected()
{
//If not connected, then try to reconnect
if (!this.TdConnection.Connected)
{
string domainName = (string)this.cboDomain.SelectedValue;
string projectName = (string)this.cboProject.SelectedValue;
TdConnection.InitConnectionEx(this.txtServer.Text);
TdConnection.Login(this.txtLogin.Text, this.txtPassword.Text);
TdConnection.Connect(domainName, projectName);
}
//Make sure project is connected
if (!this.TdConnection.ProjectConnected)
{
string domainName = (string)this.cboDomain.SelectedValue;
string projectName = (string)this.cboProject.SelectedValue;
TdConnection.Connect(domainName, projectName);
}
}
/// <summary>
/// Reloads the list of projects for the selected domain
/// </summary>
private void ReloadProjects()
{
//Now we need to populate the list of projects
string domainName = (string)this.cboDomain.SelectedValue;
HP.QUalityCenter.List projectsOleList = TdConnection.get_VisibleProjects(domainName);
ArrayList projectsList = new ArrayList();
//Convert into a standard bindable list source (OLEList index starts at 1)
for (int i = 1; i <= projectsOleList.Count; i++)
{
projectsList.Add(projectsOleList[i]);
}
this.cboProject.DataSource = projectsList;
//Enable the login button if we have at least one project
if (projectsList.Count >= 1)
{
this.btnLogin.Enabled = true;
}
}
public void Connect()
{
if (this.MyDriver == UseDriver.ODBC)
{
OdbcConnection connection = new OdbcConnection(this.ConnectString);
connection.Open();
this.ODBCConnection = connection;
}
else if (this.MyDriver == UseDriver.ORACLE)
{
OracleConnection connection = new OracleConnection(this.ConnectString);
connection.Open();
this.OleDbConnection = connection;
}
else if (this.MyDriver == UseDriver.TERADATA)
{
TdConnection connection = new TdConnection(this.ConnectString);
connection.Open();
this.TdConnection = connection;
}
}
public void Connect()
{
Logger.Log(String.Format("Connecting to server using {0} driver.", MyDriver.ToString()));
if (this.MyDriver == UseDriver.ODBC)
{
OdbcConnection connection = new OdbcConnection(this.ConnectString);
connection.Open();
this.ODBCConnection = connection;
}
else if (this.MyDriver == UseDriver.ORACLE)
{
OracleConnection connection = new OracleConnection(this.ConnectString);
connection.Open();
this.OleDbConnection = connection;
}
else if (this.MyDriver == UseDriver.TERADATA)
{
TdConnection connection = new TdConnection(this.ConnectString);
connection.Open();
this.TdConnection = connection;
}
else if (this.MyDriver == UseDriver.POSTGRESQL)
{
NpgsqlConnection connection = new NpgsqlConnection(this.ConnectString);
connection.Open();
this.NpgsqlConnection = connection;
}
else if (this.MyDriver == UseDriver.SNOWFLAKE)
{
SnowflakeDbConnection connection = new SnowflakeDbConnection();
connection.ConnectionString = ConnectString;
connection.Open();
this.SnowflakeConnection = connection;
}
Logger.Log("Connection succesfully established");
}
public int AddAttachments(string text, string filename, int Defectid1, int id1)
{
TdConnection con = new TdConnection(strConnString);
int id = 0;
//Define the Command Object
Teradata.Client.Provider.TdCommand cmdAddAttach = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Attachments Values('" + text + "','"
+ filename + "',"
+ Defectid1 + ","
+ id1 + " )", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdAddAttach.ExecuteNonQuery();
id = 1;
return(id);
}
catch (Teradata.Client.Provider.TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(id);
}
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);
}
static void Main(string[] args)
{
Parser.Default.ParseArguments <Options>(args)
.WithParsed <Options>(o =>
{
Utils utils = new Utils();
DataTable excel = utils.ImportExceltoDatatable(o.inExcel, "Queries");
DirectoryInfo od = new DirectoryInfo(o.outDir);
od.Create();
DataTable dtSummary = new DataTable();
dtSummary.Columns.Add(new DataColumn("SSASModel", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TableName", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TestResults", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TDQueryStartTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("TDQueryEndTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("TDQueryExecutionTime(minutes)", typeof(System.Double)));
dtSummary.Columns.Add(new DataColumn("SSASQueryStartTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("SSASQueryEndTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("SSASQueryExecutionTime(minutes)", typeof(System.Double)));
dtSummary.Columns.Add(new DataColumn("Exception", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TDQuery", typeof(String)));
dtSummary.Columns.Add(new DataColumn("DAX", typeof(String)));
foreach (DataRow row in excel.Rows)
{
string ssasModel = row["SSAS_MODEL"].ToString();
string tableName = row["TABLE_NAME"].ToString();
string tdQuery = row["TD_QUERY"].ToString();
string daxQuery = row["DAX"].ToString();
DateTime tdStartTime = DateTime.Now, tdEndTime = DateTime.Now, SSASStartTime = DateTime.Now, SSASEndTime = DateTime.Now;
if (tableName.ToString().Equals(""))
{
continue;
}
DataRow drSummary = dtSummary.NewRow();
drSummary["SSASModel"] = ssasModel;
drSummary["TableName"] = tableName;
drSummary["TDQuery"] = tdQuery;
drSummary["DAX"] = daxQuery;
drSummary["TestResults"] = "Failed";
Console.WriteLine("---------------------------------------------------------------");
Console.WriteLine(ssasModel + " -> " + tableName);
using (var workbook = new XLWorkbook())
{
try
{
Console.WriteLine(tdQuery);
TdConnection cn = new TdConnection(o.Server);
cn.Open();
TdCommand cmd = null;
TdDataAdapter adapter = null;
DataTable dtt = new DataTable();
tdStartTime = DateTime.Now;
try
{
cmd = new TdCommand(tdQuery, cn);
cmd.CommandTimeout = 1200;
adapter = new TdDataAdapter(cmd);
tdStartTime = DateTime.Now;
adapter.Fill(dtt);
tdEndTime = DateTime.Now;
workbook.Worksheets.Add(dtt, "TD");
cmd.Connection.Close();
cmd.Dispose();
adapter.Dispose();
}
catch (Exception e)
{
tdEndTime = DateTime.Now;
cmd.Connection.Close();
cmd.Dispose();
adapter.Dispose();
throw e;
}
drSummary["TDQueryStartTime"] = tdStartTime;
drSummary["TDQueryEndTime"] = tdEndTime;
drSummary["TDQueryExecutionTime(minutes)"] = (double)tdEndTime.Subtract(tdStartTime).Seconds / 60;
//ADOTabularConnection conx = new ADOTabularConnection(@"Data Source = .; Catalog = AW;", AdomdType.AnalysisServices);
System.Data.DataTable dt = new DataTable();
ADOTabularConnection conx = null;
try
{
conx = new ADOTabularConnection(o.SSAS, ADOTabular.Enums.AdomdType.AnalysisServices);
if (!ssasModel.Trim().Equals(""))
{
conx.ChangeDatabase(ssasModel);
}
Console.WriteLine("--------------------DAX---------------------------");
Console.WriteLine(daxQuery);
SSASStartTime = DateTime.Now;
drSummary["SSASQueryStartTime"] = SSASStartTime;
dt = conx.ExecuteDaxQueryDataTable(daxQuery);
SSASEndTime = DateTime.Now;
conx.Close();
conx.Dispose();
}
catch (Exception e)
{
SSASEndTime = DateTime.Now;
conx.Close();
conx.Dispose();
throw e;
}
drSummary["SSASQueryEndTime"] = SSASEndTime;
drSummary["SSASQueryExecutionTime(minutes)"] = (double)SSASEndTime.Subtract(SSASStartTime).Seconds / 60;
int noOfRows = dt.Rows.Count;
if (noOfRows > MAX_ROWS)
{
dt = dt.Rows.Cast <DataRow>().Take(MAX_ROWS).CopyToDataTable();
}
workbook.Worksheets.Add(dt, "SSAS");
DataTable ret = utils.getDifferentRecords(dtt, dt);
DataTable dtOutput = new DataTable();
dtOutput.Columns.Add(new DataColumn("SSASModel", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TableName", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TDQuery", typeof(String)));
dtOutput.Columns.Add(new DataColumn("DAX", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TestResults", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TDQueryStartTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("TDQueryEndTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("TDQueryExecutionTime(minutes)", typeof(System.Double)));
dtOutput.Columns.Add(new DataColumn("SSASQueryStartTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("SSASQueryEndTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("SSASQueryExecutionTime(minutes)", typeof(System.Double)));
DataRow dr = dtOutput.NewRow();
dr["SSASModel"] = ssasModel;
dr["TableName"] = tableName;
dr["TDQuery"] = tdQuery;
dr["DAX"] = daxQuery;
dr["TDQueryStartTime"] = tdStartTime;
dr["TDQueryEndTime"] = tdEndTime;
dr["TDQueryExecutionTime(minutes)"] = (double)tdEndTime.Subtract(tdStartTime).Seconds / 60;
dr["SSASQueryStartTime"] = SSASStartTime;
dr["SSASQueryEndTime"] = SSASEndTime;
dr["SSASQueryExecutionTime(minutes)"] = (double)SSASEndTime.Subtract(SSASStartTime).Seconds / 60;
if (noOfRows == 0)
{
//tableName = tableName + "_NODATA";
dr["TestResults"] = "No data";
drSummary["TestResults"] = "No data";
}
else if (ret.Rows.Count > 0)
{
workbook.Worksheets.Add(ret, "ERRORS");
//tableName = tableName + "_FAILED";
dr["TestResults"] = "Failed";
drSummary["TestResults"] = "Failed";
}
else
{
//tableName = tableName + "_PASSED";
dr["TestResults"] = "Passed";
drSummary["TestResults"] = "Passed";
}
dtOutput.Rows.Add(dr);
workbook.Worksheets.Add(dtOutput, "TestOutput");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
tableName = tableName + "_Exception";
DataTable ex = new DataTable("Exception");
ex.Columns.AddRange(new DataColumn[2] {
new DataColumn("ERROR1"), new DataColumn("ERROR2")
});
drSummary["Exception"] = e.Message;
ex.Rows.Add("Failed", e.Message);
ex.Rows.Add("", e.StackTrace);
workbook.Worksheets.Add(ex, "Exception");
}
finally
{
dtSummary.Rows.Add(drSummary);
workbook.SaveAs(od.FullName + "\\" + tableName + ".xlsx");
XLWorkbook summaryworkbook = new XLWorkbook();
summaryworkbook.Worksheets.Add(dtSummary, "TestSummary");
summaryworkbook.SaveAs(od.FullName + "\\TestSummary.xlsx");
}
}
}
});
}
private void ExecuteSql(string sql)
{
switch (this.DbType)
{
case Type.SqlServer:
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Access:
using (OleDbConnection conn = new OleDbConnection(this.ConnectionString))
{
OleDbCommand comm = new OleDbCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Oracle:
using (OracleConnection conn = new OracleConnection(this.ConnectionString))
{
OracleCommand comm = new OracleCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.MySql:
using (MySqlConnection conn = new MySqlConnection(this.ConnectionString))
{
MySqlCommand comm = new MySqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Sqlite:
using (SQLiteConnection conn = new SQLiteConnection(this.ConnectionString))
{
SQLiteCommand comm = new SQLiteCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.PostGreSql:
using (NpgsqlConnection conn = new NpgsqlConnection(this.ConnectionString))
{
NpgsqlCommand comm = new NpgsqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Teradata:
using (TdConnection conn = new TdConnection(this.ConnectionString))
{
TdCommand comm = new TdCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
}
}
private void ExecuteSql(string sql)
{
switch (DbType)
{
case Type.SqlServer:
using (var connection = new SqlConnection(ConnectionString))
using (var command = new SqlCommand(sql, connection))
{
ConvertToXml(connection, command);
}
break;
case Type.Access:
using (var conn = new OleDbConnection(ConnectionString))
using (var comm = new OleDbCommand(sql, conn))
{
ConvertToXml(conn, comm);
}
break;
case Type.Oracle:
using (var connection = new OracleConnection(ConnectionString))
using (var command = new OracleCommand(sql, connection))
{
ConvertToXml(connection, command);
}
break;
case Type.MySql:
using (var connection = new MySqlConnection(ConnectionString))
using (var command = new MySqlCommand(sql, connection))
{
ConvertToXml(connection, command);
}
break;
case Type.Sqlite:
using (var connection = new SQLiteConnection(ConnectionString))
using (var command = new SQLiteCommand(sql, connection))
{
ConvertToXml(connection, command);
}
break;
case Type.PostGreSql:
using (var connection = new NpgsqlConnection(ConnectionString))
using (var command = new NpgsqlCommand(sql, connection))
{
ConvertToXml(connection, command);
}
break;
case Type.Teradata:
using (var connenction = new TdConnection(ConnectionString))
using (var command = new TdCommand(sql, connenction))
{
ConvertToXml(connenction, command);
}
break;
case Type.Odbc:
using (var connenction = new OdbcConnection(ConnectionString))
using (var command = new OdbcCommand(sql, connenction))
{
ConvertToXml(connenction, command);
}
break;
}
}
void ExecuteSql(string sql)
{
switch (DbType)
{
case Type.SqlServer:
using (var conn = new SqlConnection(ConnectionString))
{
var comm = new SqlCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("SqlServer_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.Access:
using (var conn = new OleDbConnection(ConnectionString))
{
var comm = new OleDbCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
if (reader != null)
{
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Access_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader != null && reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.Oracle:
using (var conn = new OracleConnection(ConnectionString))
{
var comm = new OracleCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Oracle_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.MySql:
using (var conn = new MySqlConnection(ConnectionString))
{
var comm = new MySqlCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("MySql_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.Sqlite:
using (var conn = new SQLiteConnection(ConnectionString))
{
var comm = new SQLiteCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Sqlite_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.PostGreSql:
using (var conn = new NpgsqlConnection(ConnectionString))
{
var comm = new NpgsqlCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("PostGreSql_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.Teradata:
using (var conn = new TdConnection(ConnectionString))
{
var comm = new TdCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Teradata_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
}
}
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;
}
}
}
/// <summary><see cref="Edrych.DataAccess.DataAccessBase.GetDbConnection"/></summary>
protected override System.Data.IDbConnection GetDbConnection()
{
TdConnection conn = new TdConnection(this.ConnectionString);
conn.Open();
return conn;
}
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);
}
public void Connect()
{
if (this.MyDriver == UseDriver.ODBC)
{
OdbcConnection connection = new OdbcConnection(this.ConnectString);
connection.Open();
this.ODBCConnection = connection;
}
else if (this.MyDriver == UseDriver.OLEDB)
{
OracleConnection connection = new OracleConnection(this.ConnectString);
connection.Open();
this.OleDbConnection = connection;
}
else if (this.MyDriver == UseDriver.TERADATA)
{
TdConnection connection = new TdConnection(this.ConnectString);
connection.Open();
this.TdConnection = connection;
}
}
