public GetCommonSizeIncomeStatementResult GetCommonSizeIncomeStatement(GetCommonSizeIncomeStatementParams cmpCommonSizeBSParams)
{
string spName = "[SP_Get_Company_CommonSizeStatementOfOperations]";
SqlConnection conn = OpenConnection("ConnectionStringFilings");
GetCommonSizeIncomeStatementResult result = new GetCommonSizeIncomeStatementResult();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = schema + "." + spName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = conn;
// Company code
SqlParameter paramCompanyCode = new SqlParameter("@CompanyCode", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, cmpCommonSizeBSParams.CompanyCode);
// Regulator code
SqlParameter paramRegulatorCode = new SqlParameter("@RegulatorCode", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, cmpCommonSizeBSParams.RegulatorCode);
// Filing name
SqlParameter paramFilingName = new SqlParameter("@FilingName", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, cmpCommonSizeBSParams.Name);
cmd.Parameters.Add(paramCompanyCode);
cmd.Parameters.Add(paramRegulatorCode);
cmd.Parameters.Add(paramFilingName);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
if (ds.Tables.Count >= 1)
{
// second table - filing data
foreach (DataRow r in ds.Tables[0].Rows)
{
string valLabel = (string)r["Label"];
if (cmpCommonSizeBSParams.Values == null || cmpCommonSizeBSParams.Values.Count == 0 || cmpCommonSizeBSParams.Values.Contains(valLabel))
{
DateTime periodStart = (DateTime)r["Start_Dttm"];
DateTime periodEnd = (DateTime)r["End_Dttm"];
FilingRecord fr = new FilingRecord();
fr.Code = valLabel;
fr.Instant = periodStart == periodEnd ? periodStart : DateTime.MinValue;
fr.PeriodEnd = periodEnd;
fr.PeriodStart = periodStart;
fr.Value = (decimal)r["CommonSizeValue"];
result.Data.Add(fr);
}
}
}
conn.Close();
return(result);
}
public GetCompanyFilingResult GetCompanyFilingData(GetCompanyFilingParams cmpFilingParams)
{
string spName = "[SP_Get_Company_Filing_Data]";
SqlConnection conn = OpenConnection("ConnectionStringFilings");
GetCompanyFilingResult result = new GetCompanyFilingResult();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = schema + "." + spName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = conn;
// Company code
SqlParameter paramCompanyCode = new SqlParameter("@CompanyCode", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, cmpFilingParams.CompanyCode);
// Regulator code
SqlParameter paramRegulatorCode = new SqlParameter("@RegulatorCode", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, cmpFilingParams.RegulatorCode);
// Filing name
SqlParameter paramFilingName = new SqlParameter("@FilingName", SqlDbType.NVarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, cmpFilingParams.Name);
cmd.Parameters.Add(paramCompanyCode);
cmd.Parameters.Add(paramRegulatorCode);
cmd.Parameters.Add(paramFilingName);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
if (ds.Tables.Count >= 2)
{
if (ds.Tables[0].Rows.Count > 0)
{
// first table - metadata
CompanyFilingInfo filingInfo = new CompanyFilingInfo();
filingInfo.Name = (string)ds.Tables[0].Rows[0]["Filing_Name"];
filingInfo.Type = (string)ds.Tables[0].Rows[0]["Type_Code"];
filingInfo.PeriodEnd = (DateTime)ds.Tables[0].Rows[0]["End_Dt"];
filingInfo.PeriodStart = (DateTime)ds.Tables[0].Rows[0]["Start_Dt"];
filingInfo.Submitted = (DateTime)ds.Tables[0].Rows[0]["Submit_Dt"];
result.FilingInfo = filingInfo;
foreach (DataRow r in ds.Tables[1].Rows)
{
string valLabel = (string)r["Value_Label"];
if (cmpFilingParams.Values == null || cmpFilingParams.Values.Count == 0 || cmpFilingParams.Values.Contains(valLabel))
{
DateTime periodStart = (DateTime)r["Start_Dttm"];
DateTime periodEnd = (DateTime)r["End_Dttm"];
FilingRecord fr = new FilingRecord();
fr.Code = valLabel;
fr.Instant = periodStart == periodEnd ? periodStart : DateTime.MinValue;
fr.PeriodEnd = periodEnd;
fr.PeriodStart = periodStart;
fr.Unit = !DBNull.Value.Equals(r["Unit"]) ? (string)r["Unit"] : null;
fr.Value = !DBNull.Value.Equals(r["Value"]) ? (decimal?)r["Value"] : null;
fr.Value_Str = !DBNull.Value.Equals(r["Value_Str"]) ? (string)r["Value_Str"] : null;
fr.Value_Dttm = !DBNull.Value.Equals(r["Value_Dttm"]) ? (DateTime?)r["Value_Dttm"] : null;
fr.FactId = !DBNull.Value.Equals(r["FactId"]) ? (string)r["FactId"] : null;
result.Data.Add(fr);
}
}
}
}
conn.Close();
return(result);
}
