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); }