public GetCompanyFilingsInfoResult GetCompanyFilingsInfo(GetCompanyFilingsInfoParams infoParams)
{
string spName = "[SP_Get_Company_Filings_Info]";
SqlConnection conn = OpenConnection("ConnectionStringFilings");
GetCompanyFilingsInfoResult result = new GetCompanyFilingsInfoResult();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = schema + "." + spName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
// Company code
SqlParameter paramCompanyCode = new SqlParameter("@CompanyCode", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, infoParams.CompanyCode);
// Regulator code
SqlParameter paramRegulatorCode = new SqlParameter("@RegulatorCode", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, infoParams.RegulatorCode);
// Period start
SqlParameter paramPeriodStart = new SqlParameter("@StartDt", SqlDbType.DateTime, 0, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, infoParams.PeriodStart);
// Period end
SqlParameter paramPeriodEnd = new SqlParameter("@EndDt", SqlDbType.DateTime, 0, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, infoParams.PeriodEnd);
cmd.Parameters.Add(paramCompanyCode);
cmd.Parameters.Add(paramRegulatorCode);
cmd.Parameters.Add(paramPeriodStart);
cmd.Parameters.Add(paramPeriodEnd);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
if (ds.Tables.Count >= 1)
{
// first table - company filings info records
foreach (DataRow row in ds.Tables[0].Rows)
{
if (infoParams.Types.Count == 0 || infoParams.Types.Contains(row["Filing_Type_Code"].ToString()))
{
CompanyFilingInfo filingInfo = new CompanyFilingInfo();
filingInfo.Name = row["Regulator_Filing_Name"].ToString();
filingInfo.Type = row["Filing_Type_Code"].ToString();
filingInfo.Submitted = (DateTime)row["Filing_Submit_Dt"];
filingInfo.PeriodStart = (DateTime)row["Filing_Start_Dt"];
filingInfo.PeriodEnd = (DateTime)row["Filing_End_Dt"];
result.Filings.Add(filingInfo);
}
}
}
result.RegulatorCode = infoParams.RegulatorCode;
result.CompanyCode = infoParams.CompanyCode;
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);
}