Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
        }