public void StandardizeStagingAddresses(int fileID)
        {
            List<StagingAddress> standardizeCandidates = new List<StagingAddress>();
            // Call SP that will get standardization candidates
            StoredProcedure proc = new StoredProcedure("[ETL].[spStagingAddressStandardizationCandidates]", this.ConnectionString);
            proc.Parameters.AddWithValue("@FileID", fileID);

            DataTable t = proc.ExecuteReader();
            foreach (DataRow row in t.Rows)
            {
                standardizeCandidates.Add(new StagingAddress(row));
            }

            // Standardize addresses here
            foreach (StagingAddress a in standardizeCandidates)
            {
                try
                {
                    StagingAddress address = a.Clone();
                    this.StagingAddressStandardize(ref address);
                    this.StagingAddressUpdateStandardized(ref address);
                }
                catch (NullReferenceException)
                {
                    // don't do anything for these just yet
                }
            }
        }
 public List<string> GetServiceAreaZipCodes()
 {
     var result = new List<string>();
     StoredProcedure proc = new StoredProcedure(SQLResource.GetServiceAreas, this.ConnectionString);
     DataTable t = proc.ExecuteReader();
     foreach (DataRow row in t.Rows)
     {
         result.Add(row.Field<string>(0));
     }
     return result;
 }
 public List<HealthSystem> HealthSystemList()
 {
     List<HealthSystem> systems = new List<HealthSystem>();
     StoredProcedure proc = new StoredProcedure(SQLResource.GetHealthSystemList, ConnectionString);
     DataTable t = proc.ExecuteReader();
     if (t.Rows.Count == 0)
     {
         throw new DataException("Could not Get Health System List");
     }
     foreach (DataRow row in t.Rows)
     {
         HealthSystem h = new HealthSystem(row);
         systems.Add(h);
     }
     return systems;
 }
 public int GetHealthcareSystemId(string ClientPrefix)
 {
     StoredProcedure proc = new StoredProcedure("GetHealthcareSystemIDByName", this.ConnectionString);
     proc.Parameters.AddWithValue("@ClientName", ClientPrefix);
     DataTable t = proc.ExecuteReader();
     if (t.Rows.Count > 0)
     {
         return t.Rows[0].Field<int>("HealthSystemID");
     }
     // default to demo database if we cannot find correct value
     return 999;
 }
 public DataTable ProcedureCodesSelectAll()
 {
     StoredProcedure proc = new StoredProcedure(SQLResource.GetProcedureCodes, this.ConnectionString);
     return proc.ExecuteReader();
 }
 public HealthSystemSettings HealthSystemSettingsLoadByID(int healthSystemID)
 {
     HealthSystemSettings result = null;
     StoredProcedure proc = new StoredProcedure(SQLResource.HealthSystemSettingsLoadByID, this.ConnectionString);
     proc.Parameters.AddWithValue("@HealthSystemID", healthSystemID);
     DataTable t = proc.ExecuteReader();
     if (t.Rows.Count == 0)
     {
         throw new DataException(String.Format("Unable to find settings for HealthSystemID {0}", healthSystemID));
     }
     result = new HealthSystemSettings(t.Rows[0]);
     return result;
 }
 public HealthSystem HealthSystemLoadByID(int healthSystemID)
 {
     HealthSystem result = null;
     StoredProcedure proc = new StoredProcedure(SQLResource.GetHealthSystemInformation, this.ConnectionString);
     proc.Parameters.AddWithValue("@HealthSystemID", healthSystemID);
     DataTable t = proc.ExecuteReader();
     if (t.Rows.Count == 0)
     {
         throw new DataException(String.Format("Could not Get Health System Information for ID {0}", healthSystemID));
     }
     result = new HealthSystem(t.Rows[0]);
     return result;
 }
        public List<ImportedFileStatus> ImportedFileStatusLoadByTypeStatusFileType(ProcessType processType, ProcessStatus processStatus, ProcessFileType processFileType)
        {
            List<ImportedFileStatus> result = new List<ImportedFileStatus>();

            StoredProcedure proc = new StoredProcedure(SQLResource.ImportedFileStatusSelectByTypeStatusFileType, ConnectionString);
            proc.Parameters.AddWithValue("@ProcessTypeID", processType);
            proc.Parameters.AddWithValue("@ProcessStatusID", processStatus);
            proc.Parameters.AddWithValue("@ProcessFileTypeID", processFileType);
            DataTable table = proc.ExecuteReader();
            foreach (DataRow row in table.Rows)
            {
                ImportedFileStatus file = new ImportedFileStatus(row);
                result.Add(file);
            }
            return result;
        }
        public ImportedFileStatus ImportedFileStatusLoadByID(int importedFileStatusID)
        {
            ImportedFileStatus result = null;
            log.DebugFormat("Loading ImportedFileStatusID: {0}", importedFileStatusID);

            StoredProcedure proc = new StoredProcedure(SQLResource.ImportedFileStatusSelect, ConnectionString);
            proc.Parameters.AddWithValue("@ImportedFileStatusID", importedFileStatusID);
            DataTable table = proc.ExecuteReader();
            result = new ImportedFileStatus(table.Rows[0]);
            return result;
        }
        public void AddressFind(ref Address address)
        {
            if (address.AddressID > 0)
            {
                return;
            }
            byte[] hash = address.ComputedHash();
            if (hash != null)
            {

                StoredProcedure proc = new StoredProcedure(SQLResource.FindAddress, ConnectionString);
                SqlParameter param = new SqlParameter()
                {
                    ParameterName = "@AddressHash",
                    SqlDbType = SqlDbType.VarBinary,
                    Value = hash
                };
                proc.Parameters.Add(param);


                DataTable t = proc.ExecuteReader();
                if (t.Rows.Count > 0)
                {
                    address.AddressID = t.Rows[0].Field<int>("AddressID");
                }
            }
        }
        public ClientOrganization ClientOrganizationLoadByID(int clientOrganizationID)
        {

            StoredProcedure proc = new StoredProcedure("[App].[spClientOrganizationSelect]",this.ConnectionString);
            proc.Parameters.AddWithValue("@ClientOrganizationID", clientOrganizationID);
            DataTable t = proc.ExecuteReader();
            if (t.Rows.Count > 0)
            {
                ClientOrganization result = new ClientOrganization(t.Rows[0]);
                return result;
            }
            return null;
        }
 public List<ClientOrganization> ClientOrganizationsList()
 {
     List<ClientOrganization> results = new List<ClientOrganization>();
     StoredProcedure proc = new StoredProcedure("[App].[spClientOrganizationSelectAll]", this.ConnectionString);
     DataTable t = proc.ExecuteReader();
     foreach (DataRow row in t.Rows)
     {
         results.Add(new ClientOrganization(row));
     }
     return results;
 }