search

Battler45 / SierraMigrationToGitHub

Migration from unfuddle to GitHub
0 stars 0 forks source link

Convert Region 25 (MIBOR / Indy) to new updater #232

Open Battler45 opened 4 years ago

Battler45 commented 4 years ago

George,

The first project on which you'll be working will be to convert an existing MLS data feed to the new updater system.

Vihar will be able to provide you with the technical details for installing GitHub and downloading a local copy of the current code base.

Vihar, will you also please provide George with whatever server and / or database access he needs for this initial project? Please share with him any necessary logins and create any new users as necessary. Also, I think that converting region 25 will be a good place to start because it shares the same MLS data provider (Marketlinx) as the one you have already converted (SoCal MLS). You can find my existing code in:

C:\Inetpub\mls4.sierrainteractivemls.com\admin\mibor

on DEDQ61

The login credentials to the RETS server are:

  • UserID: 31041rets

  • Password: BENJ1001

  • UserAgent: sierrainteract

  • Password: h1ghplateau

RETS URL: http://inr.rets.interealty.com/Login.asmx/Login

As you'll see when you examine my existing code, I was using the sysid name for the database fields rather than the long names for the fields (which were being used for SoCal MLS). I did this because I had a lot of difficulty with the RETS Connector software that I was using to download the daily data files. To help translate between the two, I've attached a spreadsheet provided to me by MIBOR (which stands for Metropolitan Indianapolis Board of Realtors) that cross-references the long field names with their numeric IDs.

Please let me know if you have any questions.

Thanks.

Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

Forgot to add - you can check the database schema by remoting to the following machine:

IP: 75.103.119.195

User Name: vshah

Password: ViharShah$123

Pls note that this is development DB, and, has almost identical schema to the live DB. There are total five DBs on this server: sierrainteractive-1 to 4, and sierrainteractive-updater. Pls note that currently sierrainteractive-3 is being used by the live sites so pls make sure that you don't change anything in that database. Ideally, you would only be needing to refer to the sierrainteractive-4 and sierrainteractive-updater DBs.

Pls let me know in case of any issues.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

George,

I've set you up so that you should be receiving updates on this task / thread -- please confirm. This thread contains the initial details you will need to get started on your first task, and Vihar can provide you with the background, technical details, etc. that you will need to get started.

Please let me know if you have any questions.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

To further clarify, the existing updater for mibor code will probably be confusing to you. It references the sys IDs of each database field, rather than the long field names, which is what you will be using when you query the RETS server using libRETS (I believe). You can use the T5 Field Info RETS Updated spreadsheet attached to the first comment in this thread to help you see which sys IDs match to which long field names.

In bringing any new data feed in line with the new updater system, the most difficult part will always be to determine how the fields from the host RETS database should be mapped into the fields of our own database.

In certain cases, such as when dealing with City, State, and Zip Code, these are straightforward.

However, in dealing with other types of listing data, such as interior and exterior features, lot features, etc., this can be more complicated. So, in working this out, referring to the existing SoCal MLS updater mappings (region 10) may also be quite helpful for you, because that region will (I believe) contain many of the same field names and will share many similar mappings.

Please let me know if you have any questions.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

Yes, the specified RETS servers are live. I think it should be ok to connect to those servers for testing purpose.

I understand that you want to test the new updater system - the SoCal updater system to be specific. To do so, you can run the WindowsServiceTest console application. The UpdateProcessor.cs file in this project contains the entry-point function, PerformUpdatesForRegion. This function will execute appropriate updater steps (depending on the steps supported for a region - as defined in the Updater_MLSRegionUpdateSteps table). If you want to test a specific step, pls comment out appropriate code in the PerformUpdatesForRegion function.

Also, you would need to change certain configuration parameters defined in the app.config file. Some of these parameters are:

Pls let me know if you need additional info.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

George,

Yes, it's definitely okay to connect to live servers for testing purposes. Since the data feed only comes in one direction (from them to us), it's fine to query during the testing process.

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

I see -- to give you a bit of background, there are a number of different data providers around the United States. These are companies that build and maintain the software for individual boards of realtors. The Socal MLS and the Indianapolis MLS (MIBOR) both use a company called MarketLinx.

This means that, although some of the fields and field names may vary between SoCal MLS and MIBOR MLS, most of them will be the same, and the way you query the database for MIBOR will be very similar to the way the database is queried for the existing SoCal MLS updater, and so you should feel free to look at SoCal MLS updater as a reference for MIBOR. (Other boards of realty use different data providers, and so this will not always be the case.)

That said, I am not sure of the answer to your question, but I believe Vihar should be able to answer this for you, since I am sure it is the same in MIBOR as it is in SoCal MLS. (My guess is that "Change Date" is the correct answer, but I am not certain.)

Ben Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

Ben, It is very helpful! Thank you!

Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

George,

Sorry for the delay getting back to you on this -- I am working on it now and will have it for you by the end of the evening (my time). In the meantime, if you have more time to work on the project, perhaps you could begin looking into the photo downloads for this region? That (hopefully) should be very similar to the way it is currently being done for SoCal MLS / Region 10.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

I've logged off from the server. Can you pls try after 5 mins and let me know if you can log into the server?

As for the changes to be applied to your local copy - you'll need to merge the remote changes into your local branch. This can be done by executing the following command git merge origin master. I'm not sure if origin is the name of your remote branch. You can check the same by executing git branch -r command; this will list all remote branch maintained by the git repository.

Alternatively, you can run git pull origin master. This will first fetch remote changes and immediately merge them in your local repository. So in a way it does the job of git fetch and git merge.

Pls let me know if you have any queries.

Thanks Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

George,

Sorry for the delayed response -- yes, you are correct that for the initial data load, we will need to import all photos new from the server (this process will likely take a couple of days).

I have been talking with Vihar for the past couple of days, and we are going to change the max dimensions of the large size of the imported photos. Up to now, we have allowed a maximum width or height of 550px, and going forward we are going to allow a max width or height of 640px. Vihar may have already made this change in the code, but please confirm with him before you begin the photo download.

Thanks.

Ben Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

Vihar,

Yes, I believe the issue is caused by several new sites having been added since the development db was last updated. Because adding new sites involves addition of records into multiple tables, it might be best to restore sierrainteractive-1 on the development DB. However, would this impact the work that George is doing and / or has already done? (Erasing test data that he has imported.)

I have created a backup of sierrainteractive-1 called sierrainteractive-1_2011-08-29.bak and downloaded that onto DEDN173 in C:\DB Backups. Please let me know if you have any trouble with that.

Your approach for assisting George to test the updater for region 25 sounds good.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

Status of task: I'm able to get response from RETS server and going to parse it.

Ben, I have question about query for request: which date should be used in query to select only outstanding records? For example table "RES" has several date fields - "List Date", "Entry Date" and "Change Date". Do you know which is correct? Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

Hi Vihar, Could you please explain more detailed how I can test the application? As I understand the specified RETS servers are live not "sandbox"? Is it ok to connect to servers for testing purposes? Thanks Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

George,

We needed to change the data-types of Latitude / Longitude columns (Geocodes table) to numeric(12,9). This led to change in a couple of updater specific store-procedures, and, hence, change in the new updater system. I've made these changes, and, have pushed the same to the updater repository. Pls fetch the latest changes and update your local copy.

Pls note that you'll also need to make a minor change in the way latitude / longitude values are set in the MIBOR import. Until now these values were set as string, but, now they would need to be set as Double. Could you pls update MIBOR import code to set latitude / longitude values as double. You can use the ToDouble string extension I've used in SoCal importer. Pls let me know if you have any queries.

Pls note that I've applied these changes to the dev. sierrainteractive-1 DB. I've attached an SQL script that you can run on your local DB to apply data-type specific changes.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Ben, Vihar,

I have some questions regarding outstanding issues (geocodes and open houses details).

  1. Geocodes. It looks like old updater doesn't get latitude and longtitude from MIBOR response. Isn't? I found in meta data some fields which probably can be used for this - DrivingLatitude and DrivingLongitude but I'm not sure. Anyway in my tests all MIBOR records don't return values for these fields. Also I've added functionality of geocode processing (similar to SoCal functionality) and it works.

  2. Open-house updating. Can it be that MIBOR's user doesn't have some rights to get open house data? I've tried to get such data and I received error "User does not have access to Resource named OpenHouse".

Thanks Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

Vihar,

Unfortunately I can not remote into 208.106.250.174 to update database with my scripts - maximum number of allowed connection is exceeded. Also I have again question about git - I've run git fetch command but I don't see any changes (there should be changed ImageHelper.cs and Constants.cs). What I have to do more?

Thanks Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

Vihang,

Since we need to test content for the sites in development and we'll be entering that content in the live DB, could you restore the development sites to pull content from the live DB rather than the development DB?

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

In implementing new updater system for region-25, you might want to refer to the existing updater system implementation (especially for field mappings). I've attached a zip file containing the source-code for the same. It contains the following folders:

mibor - contains code specific to updater-system for region 25. The "mls_autoupdates.asp" file contains the entry-point; that is, the programming in this file will initiate the updates for region-25. It will call appropriate functions like UpdateListings, RemoveOldListings, UpdatePhotos etc... for performing various update steps. Some of these functions could be defined in the mls_functions.asp file present under this folder. And, rest of the functions would be defined in the common code.

res/includes - contains code common to all updater-systems. The mls_functions.asp file for example defines InsertUpdateMLSListing function, which is called to add/update listing records.

I'm not sure if you had a chance to work with libRETS library. If you haven't, I would recommend first creating a sample project that uses the libRETS library. Pls use the above RETS credentials for connecting to the MIBOR RETS server. Ideally, you should first download the metadata from this server. That would help you in mapping listing fields.

Pls note that the MIBOR region data is stored in the sierrainteractive-1 database. You can copy the latest back up of the database (development) from the following FTP location:

Host: 75.103.116.57 User-name: george_dev_db Password: GeorgeM$123

Since the development DB is has restricted access, pls restore the above back-up on your local DB and configure the updater code to work with your local DB.

Pls let me know if you have any queries.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

George,

1 - Yes, it's fine to geocode listings using our own functionality -- that's what I am also doing in the current updater.

2 - Yes, I agree, I believe that is the case. I will check tomorrow with MIBOR staff to confirm, but for now, please proceed as if that data will not be available.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

Through Librets, there is a way to pull the system meta-data, which is an outline of the table structures of all tables -- kind of like the database schema. So, it gives you all of the available fields for the Residential property type, what data type each field is, the length, etc. And it does this for each table in the RETS database.

I'm not sure how you pull the meta-data using librets, but if you search the librets documentation for "metadata" or "meta-data" I am sure there is an easy way to do it.

This is similar to the spreadsheet I attached in the first email, but a bit different in that it is more complete and more up-to-date, and so it would probably be preferable to use this as opposed to the spreadsheet.

RE: #11 above:

1 - These are good questions. Each data feed is, unfortunately, different. In the case of the MIBOR feed, they only use one field for the full agent name ("Listing Agent Name"), rather than separate fields for the first and last name, so you will need to use string functions to parse the full name into a first and last name.

2 - Yes, it will usually be the case that not all available fields in the data feed will be mapped. This is the most difficult part of setting up a new data feed -- determining which fields should be mapped, and which should not. Often there will be multiple fields with similar names, and we will need to determine which one contains the data we are actually looking for.

There are certain fields which all feeds will contain (using different field names) and that we will always want to use, such as agent name, listing price, and MLS #. I will try to prepare a list of these to send over to you.

Then, there are fields available in some feeds but not others, such as different types of interior and exterior features, etc. The difficult part is determining which of these fields need to be included. I will help you with this now, and over time you will get a better sense of this.

3 - Some of our fields (mainly those beginning with "Feature" such as "FeaturesExterior") do contain composites of multiple fields from the data feed. There are no fixed rules here, and so while FeaturesExterior might combine 4 different data fields in SoCal MLS, it might combine 5 different fields in MIBOR. This needs to be worked out on a case-by-case basis, following common-sense guidelines which will become more clear as you gain experience working with these systems.

To help you start, I will try to prepare a list of which fields in the data feed should be mapped to which fields in our database and will send that over to you later today, along with a general explanation of the type of data each composite field should include.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

I agree - it should be "Change Date". If we filter records by that date, the server should return records that have been changed since the specified date. Also, can you pls share the MIBOR meta-data? I'll use it as reference for calrifying / confirming other this and mappings.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Ben,

RE: #57 - I had worked on a project that used Amazon S3 service for hosting photos. It is fairly straightforward (from technical viewpoint). And, from what I have read about Amazon CloudFront (CDN), it would be straightforward to use it in conjunction with S3 service.

Even though I don't have experience of using Auzre service, I believe it would be straightforward as well. And, I guess it would be easier to use it in a .NET based project like ours. So it eventually boils down to costing of these two services. Both these services do provide calculators for determining approximate costs

That being said, I think we'll need fairly accurate data to evaluate costing of each service. Would it be possible to get an approximate data for the followings?

My guess is that the monthly cost should be within $75-$100 range (or even less). Please note that this cost includes both storage and CDN charges. I'm not sure about our existing costs. If the cost of ramping up Crystaltech cloud server's hard-disk space to say another 100GB is fairly low and monthly cost of Level-3 CDN service is also lower, perhaps we should continue with the current approach of hosting images on the Crystaltech cloud server.

Pls let me know your thoughts.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Ben,

Yes, in moving to new updater for MIBOR region, it would need to pick up from where the old updater leaves off. I believe except for the photo processing / storing, rest of the processing would be pretty much unaffected. That is, the new updater system will not delete any existing listings records so the leads' associations with the current listings will remain unchanged.

As for photo processing, I believe it would be good if we can make use of the existing photos; thus eliminating the need to migrate (re-download) photos for existing listings. One possible approach would be to introduce a new field in the MLSPhotos table say, PhotoServer (tinyint). This field will indicate the server where the photo would be stored. The existing code that builds path for listing images already handles two different paths (i.e. for SoCal regions and for the rest of the regions) so we would just need to change that programming to take into account the PhotoServer value.

Per this approach, the PhotoServer value for existing photo-records would be say 1 for region-10 records and 0 for the rest of the records. Any new photos processed through the new updater system will have the PhotoServer value set to 1. So the front-end site programming will use the PhotoServer value to build listing photo url. It currently makes distinction between SoCal region and other regions, which can be changed to make distinction based on the PhotoServer value.

The only issue with the above approach would be to handle removal of photos on old server through the new updater system. When an existing listing record is removed, its associated photos are also removed. When the "Purging" step is executed for MIBOR region (in the new updater system), it may be possible that all or some of the photos of the listings to be purged are present on the old update server. My thought is that this would need to be handled through FTP interface.

We can go one step further and implement programming, which would be flexible enough to handle additional photo servers. I haven't given this much thought but I realize that we would have to think about this sooner as the SoCal images are already occupying about 30GB of hard-disk space on the Crystaltech cloud server, and MIBOR images will only add to this size. I guess it would be possible to increase the size so we can continue with the above mentioned approach.

I also wanted to discuss possibility about hosting photos on 3rd-party services e.g. combination Amazon S3 (for hosting photos) & Amazon CloudFront (for CDN) / Azure Storage & Azure CDN. If this is viable then we could change the new updater system to store photos on such service. This will eliminate the need to ramp-up resources on updater servers. And, in the long run (after we have moved to new updater system completely) all photos would be hosted on such CDN service, which would definitely reduce traffic / load on the servers.

Pls let me know your thoughts. George - pls let me know if you have any suggestions / recommendations for the same.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Ben,

Assuming we will be continuing with the current setup of hosting images on the Crystaltech cloud server, there are a couple of approaches for moving MIBOR images:

1 -- to re-download all photos through the new updater system. This will require minimum change in that we would need to update the photo-url generation to take into account active photo-server value (as described in comment #52). This will however be fairly time-consuming process as all photos would have to be re-downloaded from the RETS server and re-processed.

2 -- to write a small application, which will copy processed images from the old photo-server and update the database records with new path value. This should be faster compared to the 1st approach in that we'll be using FTP interface for downloading photos from another Crystaltech machine.

Alternatively, all images can be copied manually from the old server to the Crystaltech cloud server, and, then the new application should be run to update db records. This processing would be fairly quick in that the processing would be done locally. However, the time to FTP all images to new server might be high assuming large number of images would need to be transferred.

In either case, the new application will be useful in moving photos of other regions as and when we convert them to new updater system.

My thought is that regardless of the approach followed, we should add a new field, PhotoServer (comment #52), and update the photo-url generation logic. This will ensure that photos are continued to be served correctly until all photos have been moved to the Crystaltech cloud server.

Pls let me know your thoughts / suggestions.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Ben,

Developing a small application for data comparison sounds good. The only concern I've is that in using this application, the old and new updater would have to be synchronized. That is, the tests would have to be carried out only after the old updater has run; otherwise the comparison application won't be much of a use. Could you pls confirm the time when the MIBOR updates are completed (through the old updater)? George can then schedule the new updaters to run around that time, and, then can use comparison application to verify correctness of data. Pls let me know your thoughts on this.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

George / Vihar,

RE: #53

You are correct -- Open Houses are not available for MIBOR and so can be excluded from the update process.

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

I've made changes to the image-processing code to generate large images in 640x640 dimensions. I've pushed this change to the master branch. Can you pls fetch the latest code and update your local code?

This just for your info - I've created a new branch, production in the updater git repository. This branch will contain code that is currently used for production updater service. That is, it won't contain any development specific code. I'll maintain this branch to make sure that development specific changes are not pushed to the production service. Pls continue pushing your changes to the master branch (which would contain all development specific changes like you are making for implementing MIBOR updater).

As for photo-download/process testing - I would suggest you to first test it on your local machine (if you haven't already). For this, pls set the LastUpdateTimeStamp for photo-step (16) to current date. This will make sure that only most recent photos are downloaded/processed. Once you've verified the changes, pls test the MIBOR updater on the development server. If everything is working as expected, we can then download all records / photos (by setting the LastUpdateTimeStamp value to null for all steps). Pls let me know your thoughts.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

George,

We needed to change the data-types of Latitude / Longitude columns (Geocodes table) to numeric(12,9). This led to change in a couple of updater specific store-procedures, and, hence, change in the new updater system. I've made these changes, and, have pushed the same to the updater repository. Pls fetch the latest changes and update your local copy.

Pls note that you'll also need to make a minor change in the way latitude / longitude values are set in the MIBOR import. Until now these values were set as string, but, now they would need to be set as Double. Could you pls update MIBOR import code to set latitude / longitude values as double. You can use the ToDouble string extension I've used in SoCal importer. Pls let me know if you have any queries.

Pls note that I've applied these changes to the dev. sierrainteractive-1 DB. I've attached an SQL script that you can run on your local DB to apply data-type specific changes.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Vihar,

Re: #61 - From what I can tell, pricing is fairly similar. Unfortunately, I don't have access to the type of data we would need to accurately assess the monthly costs, but even if it turned out to be double your estimate, that would be do-able for us and worth it if it allows us to provide customers with images that always serve quickly, and reduces the load on our dedicated servers (and thus reduces the need for additional servers).

I'm running a file tree right now which will tell us exactly the total size of the MLS folder on DEDQ61 -- I believe it is 50-60 GB currently, so I think we are still under 100GB total for all photos.

By the way, 30GB does seem like a lot for the SoCal photos -- the overall size of the storage on the server for this region should remain fairly consistent over time, shouldn't it (aside from the increase in the size of the large photos that we introduced recently)? Can you double-check to make sure that when a listing is deleted from the server, all associated photos are also getting deleted?

(Also, can you please restart DEDN173 overnight tonight -- it needs to be restarted following updates.)

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George & Vihar,

We received an email today from a staff member at MIBOR / Indy stating that we are displaying listings on the HomeToIndy.com site which should not be displayed.

In the data feed, there is a field, "Public Internet Yes/No". When this field is set to "No", we are not meant to include these listings in our feed and display them in the site.

The staff member cited the listing MLS #21163069 as one such listing for which this field is set to No, but which is displayed on the HomeToIndy.com site.

Can you please check the programming for the Indy Updater and correct this issue, and then re-run a full update of all Indy listings so that these "No" listings are no longer displayed on the site.

This is a priority for us, and it would be good if we could have this done by tomorrow (Wednesday), my time.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

For Reference - The MIBOR updater is modified to only pull Active listings. Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Ben,

RE: #66: I've restarted the DEDN173 server but I'm now unable to remote into it. The server appears to have restarted correctly as I could access the CAA website. Could you pls look into the same?

As for the size of SoCal photos, it is definitely lot more when compared to other regions. I verified that all these photos are associated with active listings. Moreover, the ratio of photos added to photos removed is more than 2 majority of the times so this size in all likelihood will increase.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Vihar,

Yes, I'm sure that SchulerBauer.com is being served from DEDN173. To verify, I just remoted in to that server, opened the IIS Manager, and stopped the SchulerBauer.com (LIVE) site. When I did that and then checked the live URL of the site, it no longer came up, so I'm positive that the site is being served from that location.

I also verified that the virtual directories for the site (/property-search/, /server_constants/, /shared/) are pointing to the write folders on the server, and they do appear to be.

In making your updates, are you making changes within the Inetpub-LIVE/Sites/MLS_Tools_Integrated/ folder? That is where all three of those sites are pulling their MLS functions from.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

You are correct. The dev. site also connects to the live database. That is the reason it didn't display any result for MLS search. Ben and Vihang are working on adding new front-end sites, and, therefore, they need to connect the dev. sites (on sierrainteractive-1) to live DB so we would not be able to perform visual comparisons.

Btw, I quickly checked the latest MLS records on the dev DB. Pls find below my comments:

1 -- It looks like the newly added listings are not geocoded. That is, the Geocode_ID value for such listings is 0. To give you a brief overview of geo-coding - we have a table, Geocodes, which stores details like latitude, longitude, address etc... This info. is used in displaying listing on map, performing search by map area etc... So when a new listing is added to the MLS table, we also associate it with a new Geocodes record. If latitude / longitude info. is available from the RETS server, then those values are used in creating a new Geocodes records.

The updater also runs Geocoding step. This step queries 3rd-party service like Yahoo / Google to get missing geocoding details. And, associates listings with new Geocodes records. So for all those listings, which don't have latitude / longitude info. available, the geocoding step populates missing details for them.

Can you pls verify this in the MIBOR updater?

2 -- None of the listings have open-house details. Can you pls check if the open-house step is executed for the MIBOR region? This step should populate info. like open-house date / time etc...

3 -- I'm not sure if had the photo-downloader step enabled for this specific test. If it was, can you pls check why photos have not been downloaded for newly added listings? Also, no records have been added in the MLSPhotos table.

4 -- When you have chance, can you pls add some logging in the MIBOR steps' programming? This will help us in verifying updater steps, location errors (if any).

I'm currently working on finishing one of the open tasks. Hopefully that would be completed in another day or two. I'll then be able to test the updater thoroughly.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Vihar, Thanks for your help. I will check this listing. Regarding composite fields for Residental Property. Now I'm able to fill next composite fields with same headers as for SoCal:

For other composite fields (FeaturesCommunity, FeaturesCondo, FeaturesConstruction, FeaturesDocuments, FeaturesExterior, FeaturesFencing, FeaturesHOA, FeaturesParking, FeaturesRoof, FeaturesUtilities) I can not find MIBOR fields which could be assigned to SoCal headers. Probably some composite fields can be filled with MIBOR data with other headers (for example MIBOR contains "Garage description" field which can be placed into composite field FeaturesParking). But I'm not sure is it legal or not and who should make such decisions. Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

Ben,

I've modified the connection-string on the dev. version of search-tools to connect to dev. DB. The dev. hometoindy site now pulls data from the dev. DB.

Pls note that the contents are still pulled from the live DB as Vihang is working on setting up content-pages for new sites. He is however not able to work with property-search pages. Could you pls let me know if synchronizing the Sites table will fix such issues? Or, it would be good idea to restore the latest sierrainteractive-1 live DB on the dev. DB server.

As for data comparison - the live and dev. DB are accessible from the DEDN173 server so it can be used to perform side-by-side comparisons. I've set up a new user on this machine. When George has completed local testing and is ready to test the new updater on the dev. DB, I'll provide him the details for the same (and discuss possible approach to test the new updater). Pls let me know if this sounds good.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

George,

For the purpose of testing, I think it would be good to set the LastUpdateTimeStamp value to previous date i.e. 30th Aug. This should download sufficient number of records, which you can then compare by reviewing live and development database side-by-side. In addition, as Ben had mentioned earlier, you can also compare the live (www.hometoindy.com) and development (hometoindy.sierrainteractivedev.com) sites to make sure the correctness of data.

As for testing the MIBOR updater on the dev server, my thought is to do this by executing command-line test application. This will allow you to test the MIBOR region updater in isolation.

The development server (75.103.119.144) has a Test App folder (D:\Updater System\Test App). Pls FTP the latest command-line executable and other associated DLLs to this folder. The Sierra.Updater.Tester.exe.config file already contains server specific settings so you won't need to change it unless you want to test the email alert functionality. In that case, pls change the TestEmailAddress param with appropriate value.

Important points:

1 -- If you need to update the config file, pls make sure that LogEmails is set to true (otherwise emails would be sent to actual leads)

2 -- The connection-strings are updated to point to the dev. DB (sql4.sierrainteractivemls.com) so you can remove the SoCal specific updater steps from the Updater_MLSRegionUpdateSteps table in dev DB; the SoCal MLSRegion_ID value is 10. This will make sure that only MIBOR records are downloaded / updated.

3 -- Pls don't FTP the following two files: librets-dotnet.dll and librets-pinvoke.dll; they are of 64-bit version. Uploading the build version (which is 32-bit version) will result in error.

I've set up a new user, georgem (#G30rg3#) on the development server (75.103.119.144). Pls remote into the development server using these credentials. Also, you can use the following FTP settings to FTP console app to the Test App folder directly: 75.103.116.57 sierraupdaterdev #Med2a$123#

--

As for side-by-side database comparison, you can remote into the following server: 208.106.250.174 using the same credentials i.e. georgem (#G30rg3#). I've already opened an SQL Server session, and have connected to the live DB (sql2.sierrainteractivemls.com) and development DB (sql4.sierrainteractivemls.com).

Just in case you need to connect to either of these DBs, the username and password are s13rra and 1nteractiv3 respectively.

Pls let me know if you have any queries / need additional info.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Vihar,

Can you explain to me how you are able to delete listings from SoCal? Since the same company provides the data feed service for both SoCal and MIBOR, hopefully we can use the same method. If we do not have access to a needed resource, let me know and I will contact the MIBOR representative and Marketlinx to see if we can get access as needed.

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

First, there are no fixed rules regarding which RETS fields get mapped to which fields in our database, meaning there are

no requirements mandated by the data providers and we can set our own rules.

That said, we do have general guidelines regarding which RETS fields get mapped to which fields in our database, and I

will try to outline those for you here.

First, it will probably be helpful for you to review the schema and data in our MLS table if you have not already done

so. As you'll see, there are essentially three types of fields:

1 - Those that reflect a single value from the RETS feed, such as MLS, SqFt, Price, etc. These are relatively self-

explanatory.

2 - Composite fields, which draw from multiple fields in the RETS feed. These fields in the MLS table primarily begin

with "Features..." as in "FeaturesInterior," "FeaturesExterior," etc.

3 - Bit or TinyInt fields which typically contain boolean values of 1 or 0, such as Pool, Spa, REO, Foreclosure,

ShortSale, Equestrian, etc. These fields are used in our application to make the search process quicker. Each of these

fields represents a search field on one of the search forms so that when a visitor searches for a home with a pool, for

example, we can easily search against the Pool field in the MLS table to find matching results. Determining whether each

of these fields is true for a given property record can often involve a number of steps.

It might also be helpful for you to review the MLS data as it is presented on a client's website. Here are a few

examples for you:

http://www.hometoindy.com/property-search/detail/25/21125020/15630-shining-spring-drive-westfield-in-46074/ http://www.hometoindy.com/property-search/detail/25/21044899/1708-west-161st-street-westfield-in-46074/ http://www.hometoindy.com/property-search/detail/25/21136101/136-harrowgate-drive-carmel-in-46033/

Regarding the composite fields for MIBOR, I've outlined them below. Please refer to the mls_functions.asp file for an

example of how these fields were assembled using this data in the Classic ASP application (starting at line 460).

1 - FeaturesBasement / Describes the basement of the property

- "Base Desc(21)" as "Basement:"

2 - FeaturesConstruction / Describes the construction of the property (is it new construction? what stage of

construction is it in? what type of construction is the building? etc.)

- "Const.Stg(513)" AS "Construction Stage:"
- "Cnt Type(523)" AS "Construction Type:"
- "New Const(529)" AS "Construction Status:"

3 - FeaturesFinancial / Includes information on such things as property taxes, the last year in which taxes were paid on

the property, whether the property is in Foreclosure, financial disclosures, etc.

- "Semi Tax(284)" AS "Semi-Annual Property Tax:"
- "Tax Yr(292)" AS "Tax Year:"
- "Discltwo(1406)" AS "Financial Disclosures:"

4 - FeaturesDocuments / Similar to FeaturesFinancial, includes other types of disclosures and any other types of

documents available for examination in the sale of the property.

- "Discl(77)" AS "Primary Disclosures:"
- "DisclOther(1408)" AS "Other Disclosures:"

5 - FeaturesEnergy / Describes items related to the heating, cooling and electricity of the property

- "Wtr Heat(305)" AS "Water Heater:"
- "Heat(100)" AS "Heat:"
- "Cool(72)" AS "Cool:"
- "Fuel(99)" AS "Fuel:"
- "FP Desc(92)" AND "FP(93)" AS "Fireplace(s):" . This one is tricky, because you need to combine two values.

Please see mls_functions.asp line 645 for an example of how this is done.

6 - FeaturesExterior / Describes items related to the exterior of the home, such as patio, porch, pool, spa, etc.

- "Ext Amen(11)" AS "Exterior Features:"
- "Porch(528)" AS "Porch / Patio:"

7 - FeaturesFarm / Describes items related to the farm, if the property is a farm

- None

8 - FeaturesFencing / Describes items related to the fencing around the property, if present. In this case the Fencing

description is included in "Ext Amen(11)" field under Exterior Features in the RETS data feed.

- None

9 - FeaturesFoundation / Describes the foundation of the property, if present

- None

10 - FeaturesGarage / Describes the garage of the property, if present. This field often overlaps with FeaturesParking,

and eventually we will probably combine those two fields into one. 

- "Garage(1309)" AS "Garage Type:" 
- "1409" (not sure the name of this field) AS "Garage Features:"

11 - FeaturesHOA / HOA stands for "Home-Owners Association". Some properties are located in an area where they are

forced to belong to a local home-owners association. They usually get some benefits from this, such as belonging to a

community swimming pool, but they also must pay a fee to the association on a monthly or annual basis.

- "Mand Fee(137)" AS "Mandatory HOA Fee:"
- "MandFeeInclude(741)" AS "HOA Fee Includes:"
- "Mnd Fee Pd(145)" AS "HOA Fee Paid:"

12 - FeaturesInterior / Describes the interior of the house, such things as interior areas, eating areas, appliances, etc.

- "Interior(520)" AS "Interior Features:"
- "Areas (Interior)(527)" AS "Interior Area(s):"
- "Eat Area(193)" AS "Eating Area(s):"
- "Appl(315)" AS "Appliances:"
- "Equip(316)" AS "Equipment:"

13 - FeaturesGeneral / Catch-all for items that do not fit in any of the other areas.

- "SF (M+U)(255)" AS "Above Ground Sq. Ft.:"
- "Mn SF(254)" AS "Main Level Sq. Ft.:"
- "Up SF(272)" AS "Upper Level Sq. Ft.:"
- "Tot SF(270)" AS "Total Sq. Ft.:"
- "SF Src(269)" AS "Sq. Ft. Source:"

14 - FeaturesLot / Describes features of the lot surrounding the property, such as lake on the property, or barn, or wooded land, etc.

- "Lot Info(141)" AS "Lot Features:"

15 - FeaturesLotAccess / Describes ways in which the lot can be accessed (what type of road, etc.)

 - None

16 - FeaturesParking / Describes parking availability for the property. This overlaps with FeaturesGarage.

- None

17 - FeaturesRoof / Describes the roof of the property. This sometimes overlaps with FeaturesConstruction or FeaturesRoof.

- None

18 - FeaturesUtilities / Describes the utilities available to the property, such as electricity, water, etc.

- "Util Opt(302)" AS "Utility Options:"
- "Wtr Src(306)" AS "Water Source:"

One last thing regarding the data in the MIBOR feed. Unfortunately, the data as it comes from the feed is full of abbreviations which are hard to read. For example, in the data feed, "FinCeling" is used in place of "Finished Ceiling."

To make the data more readable, in the current updater I created a series of arrays which can be used to decode the data from the RETS feed into readable values. This file (mibor_features.asp) works in connection with mls_functions.asp (using the DecodeAbbrevs as you will see) and is also attached.

The SoCal MLS does not do this type of abbreviating and the functionality to decode abbreviations is not present in the SoCal MLS updater you are working from, and so you will need to integrate this into the MIBOR updater in some way.

That covers all of the composite fields. Once those are set, we will still need to work on setting the bit fields (REO, Foreclosure, Pool, Spa, etc.) I will send you notes on that tomorrow.

I hope this has been helpful -- please let me know if you have any questions.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

Vihar,

Yes, I agree that it makes sense to add a new field, photo-server, to be used in conjunction with the MLSPhotos table for situations such as this.

As far as the best way to copy images, I believe the fastest and easiest way would be to use an FTP program to copy the photos manually to a location on the new (cloud) server from which they could be processed by the new updater into their appropriate folder locations. I'm not able to set up an FTP account right now to connect to the appropriate folder on DEDQ61 to allow this file download due to operations currently taking place on that server, but I will do that tomorrow and forward you the FTP credentials.

If you or George could begin writing the application that will move / process these photos once they are downloaded, that would be great.

Thannks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

Ben,

I've made changes in the new CAA and new updater systems to use the PhotoServer value in building photo urls (not published yet). I'm now working on making similar changes in the front-end sites programming. I'm looking into updating the dev. version of search-tools / content pages for the same. I'll update the live sites tomorrow (during your overnight period).

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Vihar, I got RETS meta-data with using of libRETS code samples. Attached are archive with meta-data in text and xml format. In my opinion spreadsheet T5field_info_RETS_Updated01-17-09.xls looks more complete. For example ResidentialProperty object: Field #3: in spreadsheet it has name "Acres", in meta-data isn't any standard name for this field ([] - it means standard name is missed). I thought I can use this value to fill field Acreage of Property object. Is my assumption wrong? Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

Vihar,

Yes, there is some sort of bug / problem with ISAPI Rewrite that changes to an .htaccess file do not always take effect once applied. In such cases, it is necessary to restart IIS services on the server in order to get the changes to take effect. I've gone ahead and done that on DEDN173.

The Weir Properties dev site (and all other sites pointing to development DBs) are no longer working due to changes Crystaltech had to make to usernames / passwords. In completing their work, they said, "I've uninstalled SQL from the OS volume and then reinstalled it to your DATA volume. Please note that you will need to update the passwords for the 'n3wt0n' and 's13rra' users as we did not have a PW reference for them, so I used the password listed for your primary login user that we have on file in the control center." I believe the password they are referring to is the same one we use to RDP into the server, but it seems like these should be reset to what they were previously for consistency's sake.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

Ben,

Yes, I made changes within the Inetpub-LIVE folder. To be specific, I wanted to check the old list / detail pages for photos. In doing that, I made changes so that photo urls were invalid. I then checked those pages expecting them to not show photos but photos were displayed correctly. Perhaps it may have something to do with caching; strangely though photos were not rendered in hometoindy list page. I'll spend more time on this later today.

Thanks Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

Vihar, I'm testing now local. So there is no my data in remote DBs. Thanks Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

Vihar,

I see your changes are committed into production branch. Do you plan do commit these into master too? (I'm working with master branch, is it correct?)

Thanks Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

Vihar,

RE: #99

Sorry for missing this comment yesterday:

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

Vihar & George,

Can you update me on the status of this? Has the updater completed and have the indicated listings been removed from the site? I do see that the original listing MLS # 21163069 is no longer available, but wanted to confirm before responding to the MIBOR contact.

He also just emailed to request that we verify we are not displaying the address for listings where the "Address yes/no" field is set to "No" -- I believe we are changing the address on these listings to "000 Confidential" but can you double-check that for me as well?

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

As you have time can you pls work on building a small application for migrating photos from old-updater to new-updater? As discussed above, the old-updater server's performance is not always good and so when a region is converted to the new updater system, photos of that region should be moved to the Crystaltech cloud server.

As far as photo processing is concerned, the main difference between two updater systems is the way photos are organized. As you already know, each photo is resized in three different sizes i.e. thumbnail , regular and large. These photos are organized under the mlsthumbs, mlspics and mlslarge folders respectively. The actual organization however varies for some regions. Some of the recently added regions support organization in sub-folders. As of now the following three regions support organization by sub-folders - 10 (SoCal), 24 (Denver) and 25 (MIBOR); for rest of the regions photos are organized under single folder.

To give you an idea about the folder organization on the existing photo serve:

Root-Photo-Folder

    3
      mlsthumbs
      mlspics
      mlslarge

    ...

    25_1
      mlsthumbs
      mlspics
      mlslarge

     ...

    25_100
      mlsthumbs
      mlspics
      mlslarge
...

This structure has been tweaked for new updater system as follows

Root-Photo-Folder

  10
    mlsthumbs
        AA
        BB

        ...

    mlspics
        AA
        BB

        ...

    mlslarge
        AA
        BB

        ...

Going forward all regions will need to conform to the new photo-organization approach (as implemented in the new updater system). This will be done on region-by-region basis as and when regions are converted to the new updater system.

So the goal of the new application would be to organize photos per the new approach. It will have to deal with two types of existing organization

For start, you can implement the application to process MIBOR photos and move them per new organization. It can later be refined to support all regions.

As and when photos are moved to new organization, we would have to set certain flag in the corresponding MLSPhotos record so that the front-end site builds correct photo-url. As discussed in the comment-#52, a new field PhotoServer would need to be added to the MLSPhotos table. When a photo is organized per new approach, the corresponding PhotoServer value would be set to 1 so that the front-end site builds appropriate url.

My thought is that the application can be implemented to perform the following steps

1 -- to read photo records from the MLSPhotos table for MIBOR region (25).

2 -- it will then copy the corresponding photos (thumbnail, regular and large) from the old folder-structure and move to new structure. Pls use the logic of generating sub-folder name from the PhotoDownloader code (GetHashedDirectoryName function).

3 -- if successful, the corresponding PhotoServer flag should be set to 1.

Since the goal of the application is to support all regions, a configurable parameter should be added to indicate the region to process. A couple of additional parameters can be used for setting source / destination folders. Also, it would be fine to create a new console application (similar to the updater console application) under the updater solution so that it can utilize the existing framework / source-code where possible.

Pls let me know if you have any queries / suggestions.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

George,

I tested the latest changes. The ListTypeDescrip value is correctly populated now but the ListType value doesn't reflect the type of listing. It should be 1 for residential, 4 for land / lots and 5 for multi-family. Currently, it set to 1 for all types of listings. Pls make necessary change.

Also, I think we may need to change the way SourceUniqueID field's value is set. Currently the ListingKey (150) value is assigned to this field. I think this value may not be unique in the MIBOR RETS server. Can you pls look into assigning "sysid" value to the SoruceUniqueID field? The SourceUniqueID field should store the unique value, which can be used to identify record in the MIBOR database (kind of primary-key). This value is used mainly in checking physically removed listings (In the PurgeListing step (18)).

Thanks. Posted by Shah Vihar(unfuddle username: vshah)