public static int GetRatioConstructID(RatioConstruct construct)
{
int id = -1;
string tag = construct.Tag;
SqlConnection sqlConn = new SqlConnection("Server=localhost;Database=cpa;Trusted_Connection=True;");
try
{
sqlConn.Open();
SqlDataReader myReader = null;
SqlCommand sqlCmd = new SqlCommand("SELECT id FROM ratio_construct WHERE " +
"tag = @tag", sqlConn);
sqlCmd.Parameters.AddWithValue("@tag", tag);
myReader = sqlCmd.ExecuteReader();
while (myReader.Read())
{
id = Int32.Parse(myReader["id"].ToString());
}
sqlConn.Close();
}
catch (Exception e) { Console.WriteLine(e.ToString()); }
return(id);
}
public static Ratio GetRatioDetails(Ratio ratio)
{
SqlConnection sqlConn = new SqlConnection("Server=localhost;Database=cpa;Trusted_Connection=True;");
try
{
sqlConn.Open();
ratio.Denominator = new List <RatioConstruct>();
ratio.Numerator = new List <RatioConstruct>();
string queryNum = "SELECT ratio_id, position, equation_order, ratio_construct_id, operator FROM ratio_detail" +
" WHERE ratio_id = @rID";
SqlCommand cmd2 = new SqlCommand(queryNum, sqlConn);
cmd2.Parameters.AddWithValue("@rID", ratio.ID);
System.Diagnostics.Trace.WriteLine(".... inside get details id is: " + ratio.ID);
SqlDataReader myReader = null;
myReader = cmd2.ExecuteReader();
while (myReader.Read())
{
System.Diagnostics.Trace.WriteLine("... has rows, getting details");
int id_temp = Int32.Parse(myReader["ratio_construct_id"].ToString());
RatioConstruct construct = new RatioConstruct
{
ID = id_temp,
Operator = myReader["operator"].ToString(),
Position = myReader["position"].ToString(),
Name = RatioConstruct.GetRatioConstructName(id_temp)
};
System.Diagnostics.Trace.WriteLine(".....Position is: " + construct.Operator);
if (construct.Position == "n")
{
System.Diagnostics.Trace.WriteLine("..... Position is n");
ratio.Numerator.Add(construct);
}
else if (construct.Position == "d")
{
System.Diagnostics.Trace.WriteLine("..... Position is d");
ratio.Denominator.Add(construct);
}
}
sqlConn.Close();
}
catch (Exception e) { System.Diagnostics.Trace.WriteLine(e.ToString()); }
return(ratio);
}
public static int AddStatement(Statement statement)
{
int rows = -1;
//Get company id
if (Company.GetCompanyID(statement) == -1)
{
//add company
Company.AddCompany(statement.Company);
}
int cID = Company.GetCompanyID(statement);
statement.Company.ID = cID;
//Get statement id given company id and year
int statementID = GetStatementID(statement);
if (statementID == -1)
{
//addStatement
AddStatementReference(statement.Name, cID, statement.Year);
statementID = GetStatementID(statement);
}
int line = 0; //to set "line" value in details table
//Start adding lines
foreach (RatioConstruct c in statement.Details)
{
//still need construct ids
c.ID = RatioConstruct.GetRatioConstructID(c);
//set line
c.Line = line;
//add lines
rows = AddStatementLine(c, statementID);
if (rows == -1)
{
System.Diagnostics.Trace.WriteLine("Erro occured inserting " + c.Tag
+ "..." + c.Value);
}
line++;
}
return(rows);
}
public static Statement GetStatementDetails(Statement statement)
{
List <RatioConstruct> details = new List <RatioConstruct>();
int statementID = GetStatementID(statement);
if (statementID != -1)
{
SqlConnection sqlConn = new SqlConnection("Server=localhost;Database=cpa;Trusted_Connection=True;");
try
{
sqlConn.Open();
SqlDataReader myReader = null;
SqlCommand sqlCmd = new SqlCommand("SELECT line, ratio_construct_id, value FROM statement_detail WHERE " +
"statement_id = @sID", sqlConn);
sqlCmd.Parameters.AddWithValue("@sID", statementID);
myReader = sqlCmd.ExecuteReader();
while (myReader.Read())
{
details.Add(new RatioConstruct
{
Line = Int32.Parse(myReader["line"].ToString()),
Value = Decimal.Parse(myReader["value"].ToString()),
ID = Int32.Parse(myReader["ratio_construct_id"].ToString())
});
}
sqlConn.Close();
}
catch (Exception e) { Console.WriteLine(e.ToString()); }
foreach (RatioConstruct construct in details)
{
construct.Tag = RatioConstruct.GetRatioConstructTag(construct.ID);
construct.Name = RatioConstruct.GetRatioConstructName(construct.ID);
}
statement.Details = details;
}
else
{
System.Diagnostics.Trace.WriteLine("Error: no statement id found");
}
return(statement);
}
public static int AddStatementLine(RatioConstruct construct, int statementID)
{
int rows = -1;
SqlConnection sqlConn = new SqlConnection("Server=localhost;Database=cpa;Trusted_Connection=True;");
try
{
sqlConn.Open();
string commandText = "INSERT INTO statement_detail (statement_id, line, ratio_construct_id, value) " +
"VALUES (@sID, @line, @cID, @value)";
SqlCommand sqlCmd = new SqlCommand(commandText, sqlConn);
sqlCmd.Parameters.AddWithValue("@sID", statementID);
sqlCmd.Parameters.AddWithValue("@line", construct.Line);
sqlCmd.Parameters.AddWithValue("@cID", construct.ID);
sqlCmd.Parameters.AddWithValue("@value", construct.Value);
rows = sqlCmd.ExecuteNonQuery();
sqlConn.Close();
}
catch (Exception e) { Console.WriteLine(e.ToString()); }
return(rows);
}
public static int AddRatioConstruct(RatioConstruct construct)
{
SqlConnection sqlConn = new SqlConnection("Server=localhost;Database=cpa;Trusted_Connection=True;");
int rows = -1;
try
{
sqlConn.Open();
string commandText = "IF NOT EXISTS (" +
" SELECT id FROM ratio_construct WHERE tag = @tag )" +
" BEGIN INSERT INTO ratio_construct (name, tag) " +
"VALUES (@name, @tag) END";
SqlCommand sqlCmd = new SqlCommand(commandText, sqlConn);
sqlCmd.Parameters.AddWithValue("@name", construct.Name);
sqlCmd.Parameters.AddWithValue("@tag", construct.Tag);
rows = sqlCmd.ExecuteNonQuery();
Debug.WriteLine("rows affected: " + rows);
sqlConn.Close();
}
catch (Exception e) { Console.WriteLine(e.ToString()); }
return(rows);
}
public static int AddRatioDetail(RatioConstruct construct, int ratioID, string position)
{
//Works well
SqlConnection sqlConn = new SqlConnection("Server=localhost;Database=cpa;Trusted_Connection=True;");
int rows = -1;
System.Diagnostics.Trace.WriteLine("...........inside addratiodetial");
System.Diagnostics.Trace.WriteLine("......Ratio ID: " + ratioID);
System.Diagnostics.Trace.WriteLine(".......Position: " + position);
System.Diagnostics.Trace.WriteLine("........Order: " + construct.Order);
System.Diagnostics.Trace.WriteLine("........Construct ID: " + construct.ID);
System.Diagnostics.Trace.WriteLine("........Operator: " + construct.Operator);
try
{
sqlConn.Open();
string commandText = "INSERT INTO ratio_detail (ratio_id, position, equation_order, ratio_construct_id, operator) " +
"VALUES (@rID, @pos, @order, @cID, @operator)";
SqlCommand sqlCmd = new SqlCommand(commandText, sqlConn);
sqlCmd.Parameters.AddWithValue("@rID", ratioID);
sqlCmd.Parameters.AddWithValue("@pos", position);
sqlCmd.Parameters.AddWithValue("@order", construct.Order);
sqlCmd.Parameters.AddWithValue("@cID", construct.ID);
sqlCmd.Parameters.AddWithValue("@operator", construct.Operator);
rows = sqlCmd.ExecuteNonQuery();
System.Diagnostics.Trace.WriteLine("..........rows affected: " + rows);
sqlConn.Close();
}
catch (Exception e) { System.Diagnostics.Trace.WriteLine(e.ToString()); }
return(rows);
}
