<%
IF Request.Form("Continue") = "Continue" Then
set ftp = Server.CreateObject("Majodio.FTP")
ftp.URL = "205.240.85.29"
ftp.Username = "capeg_CHLEM"
ftp.Password = "00u9i9s253"
ftp.port = 21
ftp.TransferType = 2
ftp.Passive = True
ftp.OpenConnection()
ftp.GetFile "/listings-commercial.txt.gz", "D:\webroot\localuser\houseview\IDX\listings-commercial.txt.gz", True
ftp.GetFile "/listings-multifamily.txt.gz", "D:\webroot\localuser\houseview\IDX\listings-multifamily.txt.gz", True
ftp.GetFile "/listings-residential.txt.gz", "D:\webroot\localuser\houseview\IDX\listings-residential.txt.gz", True
ftp.GetFile "/listings-land.txt.gz", "D:\webroot\localuser\houseview\IDX\listings-land.txt.gz", True
ftp.GetFile "/features.txt.gz", "D:\webroot\localuser\houseview\IDX\features.txt.gz", True
set ftp = Nothing
path = Server.mapPath(".") & "/"
Set zip = Server.CreateObject("Miraplacid.MSCGZip")
zip.DecompressFile path & "listings-commercial.txt.gz", path & "listings-commercial.txt"
zip.DecompressFile path & "listings-multifamily.txt.gz", path & "listings-multifamily.txt"
zip.DecompressFile path & "listings-residential.txt.gz", path & "listings-residential.txt"
zip.DecompressFile path & "listings-land.txt.gz", path & "listings-land.txt"
zip.DecompressFile path & "features.txt.gz", path & "features.txt"
Set zip = Nothing
'========================================================================================
'========================================================================================
'Parse the Tab Delimited Data in to the Database......
'Back up existing database and Empty Current database to re-populate
'Create an intence of the FSO object
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
'Database Name And Location and Connection
strDbPathAndName = Server.MapPath("/IDX/IDX.mdb")
'Back up the database in case something goes wrong
objFSO.CopyFile strDbPathAndName, Replace(strDbPathAndName, ".mdb", "-backup.mdb", 1, -1, 1)
'Get the destination and name of the to be worked then compacted database
strWorkDB = Replace(strDbPathAndName, ".mdb", "-wrk.mdb", 1, -1, 1)
'Make a Working Copy of the database
objFSO.CopyFile strDbPathAndName, strWorkDB
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strWorkDB
'Empty the Listings Table to be re-populated
Connect.execute("DELETE * FROM Listings")
For data = 1 to 4 Step 1
Select Case data
Case 1
datafile = "listings-commercial.txt"
strMLS = 0
strPRICE = 4
strCITY = 9
strSTATE = 10
strZIP = 11
strGROUPING = 23
strAGENT = 21
strAGENTPHONE = 22
strOFFICE = 24
strOFFICEPHONE = 25
strNOTES = 105
strBED = 55
strBATHFULL = 55
strBATHHALF = 55
strFEATURES = 104
strTYPE1 = 2
strTYPE2 = 63
strBASEMENT = 14
strSQFT = 18
strAGE = 16
strTYPE3 = 50
strMASTERLEVEL = 50
strAUX1 = 50
strAUX2 = 50
strAUX3 = 50
strAUX4 = 50
strAUX5 = 50
strAUX6 = 50
strSTREETNUM = 5
strSTREETDIRECTION = 6
strSTREETNAME = 7
strCAT = 1
Case 2
datafile = "listings-residential.txt"
strMLS = 0
strPRICE = 4
strCITY = 9
strSTATE = 10
strZIP = 11
strGROUPING = 40
strAGENT = 24
strAGENTPHONE = 25
strOFFICE = 41
strOFFICEPHONE = 42
strNOTES = 151
strBED = 14
strBATHFULL = 15
strBATHHALF = 16
strFEATURES = 150
strTYPE1 = 1
strTYPE2 = 2
strBASEMENT = 18
strSQFT = 19
strAGE = 20
strTYPE3 = 21
strMASTERLEVEL = 22
strAUX1 = 55
strAUX2 = 55
strAUX3 = 55
strAUX4 = 55
strAUX5 = 55
strAUX6 = 55
strSTREETNUM = 5
strSTREETDIRECTION = 6
strSTREETNAME = 7
strCAT = 2
Case 3
datafile = "listings-land.txt"
strMLS = 0
strPRICE = 4
strCITY = 9
strSTATE = 10
strZIP = 11
strGROUPING = 24
strAGENT = 22
strAGENTPHONE = 23
strOFFICE = 25
strOFFICEPHONE = 23
strNOTES = 141
strBED = 55
strBATHFULL = 55
strBATHHALF = 55
strFEATURES = 140
strTYPE1 = 1
strTYPE2 = 2
strBASEMENT = 55
strSQFT = 19
strAGE = 20
strTYPE3 = 17
strMASTERLEVEL = 55
strAUX1 = 55
strAUX2 = 55
strAUX3 = 55
strAUX4 = 55
strAUX5 = 55
strAUX6 = 55
strSTREETNUM = 5
strSTREETDIRECTION = 8
strSTREETNAME = 3
strCAT = 3
Case 4
datafile = "listings-multifamily.txt"
strMLS = 0
strPRICE = 4
strCITY = 9
strSTATE = 10
strZIP = 11
strGROUPING = 22
strAGENT = 20
strAGENTPHONE = 21
strOFFICE = 20
strOFFICEPHONE = 21
strNOTES = 14
strBED = 55
strBATHFULL = 55
strBATHHALF = 55
strFEATURES = 136
strTYPE1 = 2
strTYPE2 = 14
strBASEMENT = 55
strSQFT = 55
strAGE = 15
strTYPE3 = 104
strMASTERLEVEL = 55
strAUX1 = 55
strAUX2 = 55
strAUX3 = 55
strAUX4 = 55
strAUX5 = 55
strAUX6 = 55
strSTREETNUM = 5
strSTREETDIRECTION = 6
strSTREETNAME = 7
strCAT = 4
End Select
Set MyTextFile = objFSO.OpenTextFile(Server.MapPath("/IDX/" & datafile),1,-2)
Do While MyTextFile.AtEndOfStream <> TRUE
strLine = Trim(MyTextFile.ReadLine)
allelements = Split(strLine,VbTab)
sqlnew = "insert into Listings(MLS,PRICE,CITY,STATE,ZIP,GROUPING,AGENT,AGENTPHONE,OFFICE,OFFICEPHONE,NOTES,BED,BATHFULL,BATHHALF,FEATURES,TYPE1,TYPE2,BASEMENT,SQFT,AGE,TYPE3,MASTERLEVEL,AUX1,AUX2,AUX3,AUX4,AUX5,AUX6,STREETNUM,STREETDIRECTION,STREETNAME,CATEGORY) values("&allelements(strMLS)&","&allelements(strPRICE)&",'"&allelements(strCITY)&"','"&allelements(strSTATE)&"','"&allelements(strZIP)&"',"&allelements(strGROUPING)&",'"&allelements(strAGENT)&"','"&allelements(strAGENTPHONE)&"','"&allelements(strOFFICE)&"','"&allelements(strOFFICEPHONE)&"','"&Replace(allelements(strNOTES),"'","''")&"','"&allelements(strBED)&"','"&allelements(strBATHFULL)&"','"&allelements(strBATHHALF)&"','"&allelements(strFEATURES)&"','"&allelements(strTYPE1)&"','"&allelements(strTYPE2)&"','"&allelements(strBASEMENT)&"','"&allelements(strSQFT)&"','"&allelements(strAGE)&"','"&allelements(strTYPE3)&"','"&allelements(strMASTERLEVEL)&"','"&allelements(strAUX1)&"','"&allelements(strAUX2)&"','"&allelements(strAUX3)&"','"&allelements(strAUX4)&"','"&allelements(strAUX5)&"','"&allelements(strAUX6)&"','"&Replace(allelements(strSTREETNUM),"'","''")&"','"&Replace(allelements(strSTREETDIRECTION),"'","''")&"','"&Replace(allelements(strSTREETNAME),"'","''")&"','"&strCAT&"')"
Connect.execute(sqlnew)
Loop
Set MyTextFile = Nothing
Next
Connect.Close
Set Connect = Nothing
'Compact and Restore database Routine================================================================
'====================================================================================================
'Create an intence of the JET engine object
Set objJetEngine = Server.CreateObject("JRO.JetEngine")
'Get the destination and name of the to be worked then compacted database
strCompactDB = Replace(strDbPathAndName, ".mdb", "-tmp.mdb", 1, -1, 1)
'Make a Working Copy of the database
strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strWorkDB
'Compact database
objJetEngine.CompactDatabase strCon, "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strCompactDB
'Release Jet object
Set objJetEngine = Nothing
'Delete old databases
objFSO.DeleteFile strDbPathAndName
objFSO.DeleteFile strWorkDB
'Rename temporary database to old name
objFSO.MoveFile strCompactDB, strDbPathAndName
'Compact and Restore database Routine================================================================
'====================================================================================================
'Release FSO object
Set objFSO = Nothing
'End Database Generation
'========================================================================================
'========================================================================================
'Dump Contents into the SQL Server Database+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Set SQLConnect = Server.CreateObject("ADODB.Connection")
SQLConnect.Open "Driver={SQL Server};Server=208.18.48.246;Database=houseviewonline;Uid=houseviewonline;Pwd=supernova04;"
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\webroot\localuser\houseview\IDX\IDX.mdb;"
'End Delete/Re-populate SQL Server Listings--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Un-comment to empty database prior to re-populate
SQLConnect.Execute("DELETE IDXListings")
Response.Write("
Listings Deleted Successfully From SQL Server
")
sqlnew="SELECT * FROM Listings ORDER BY MLS ASC"
Set RS = Connect.execute(sqlnew)
If Not RS.BOF Then
RS.MoveFirst
Do Until RS.EOF
sqlnew="insert into IDXListings(MLS,PRICE,CITY,STATE,ZIP,GROUPING,AGENT,AGENTPHONE,OFFICE,OFFICEPHONE,NOTES,BED,BATHFULL,BATHHALF,FEATURES,TYPE1,TYPE2,BASEMENT,SQFT,AGE,TYPE3,MASTERLEVEL,AUX1,AUX2,AUX3,AUX4,AUX5,AUX6,STREETNUM,STREETDIRECTION,STREETNAME,CATEGORY) values ("&RS.Fields("MLS")&","&RS.Fields("PRICE")&",'"&Replace(RS.Fields("CITY"),"'","''")&"','"&RS.Fields("STATE")&"','"&RS.Fields("ZIP")&"',"&RS.Fields("GROUPING")&",'"&RS.Fields("AGENT")&"','"&RS.Fields("AGENTPHONE")&"','"&RS.Fields("OFFICE")&"','"&RS.Fields("OFFICEPHONE")&"','"&Replace(RS.Fields("NOTES"),"'","''")&"','"&RS.Fields("BED")&"','"&RS.Fields("BATHFULL")&"','"&RS.Fields("BATHHALF")&"','"&Replace(RS.Fields("FEATURES"),"'","''")&"','"&RS.Fields("TYPE1")&"','"&RS.Fields("TYPE2")&"','"&RS.Fields("BASEMENT")&"','"&RS.Fields("SQFT")&"','"&RS.Fields("AGE")&"','"&RS.Fields("TYPE3")&"','"&RS.Fields("MASTERLEVEL")&"','"&Replace(RS.Fields("AUX1"),"'","''")&"','"&Replace(RS.Fields("AUX2"),"'","''")&"','"&Replace(RS.Fields("AUX3"),"'","''")&"','"&Replace(RS.Fields("AUX4"),"'","''")&"','"&Replace(RS.Fields("AUX5"),"'","''")&"','"&Replace(RS.Fields("AUX6"),"'","''")&"','"&Replace(RS.Fields("STREETNUM"),"'","''")&"','"&Replace(RS.Fields("STREETDIRECTION"),"'","''")&"','"&Replace(RS.Fields("STREETNAME"),"'","''")&"','"&RS.Fields("CATEGORY")&"')"
SQLConnect.execute(sqlnew)
RS.MoveNext
Loop
End if
RS.Close
Set RS = Nothing
sqlnew=""
Response.Write("Listings Successfully Re-Synced From Access to SQL Server
")
'End Delete/Re-populate SQL Server Listings--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'End Delete/Re-populate SQL Server Features--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Un-comment to empty database prior to re-populate
SQLConnect.Execute("DELETE IDXFeatures")
Response.Write("
Features Deleted Successfully From SQL Server
")
sqlnew="SELECT * FROM Features ORDER BY FeatureID ASC"
Set RS = Connect.execute(sqlnew)
If Not RS.BOF Then
RS.MoveFirst
Do Until RS.EOF
sqlnew="insert into IDXFeatures(FeatureID,CatID,Code,Abbrev,Description) values ("&RS.Fields("FeatureID")&","&RS.Fields("CatID")&","&RS.Fields("Code")&",'"&Replace(RS.Fields("Abbrev"),"'","''")&"','"&Replace(RS.Fields("Description"),"'","''")&"')"
SQLConnect.execute(sqlnew)
RS.MoveNext
Loop
End if
RS.Close
Set RS = Nothing
sqlnew=""
Response.Write("Features Successfully Re-Synced From Access to SQL Server
")
'End Delete/Re-populate SQL Server Features--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'End Delete/Re-populate SQL Server Feature Categories----------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Un-comment to empty database prior to re-populate
SQLConnect.Execute("DELETE IDXFeatureCategories")
Response.Write("
Feature Categories Deleted Successfully From SQL Server
")
sqlnew="SELECT * FROM FeatureCategories ORDER BY CatID ASC"
Set RS = Connect.execute(sqlnew)
If Not RS.BOF Then
RS.MoveFirst
Do Until RS.EOF
sqlnew="insert into IDXFeatureCategories(CatID,ClassID,Code,Description) values ("&RS.Fields("CatID")&","&RS.Fields("ClassID")&",'"&Replace(RS.Fields("Code"),"'","''")&"','"&Replace(RS.Fields("Description"),"'","''")&"')"
SQLConnect.execute(sqlnew)
RS.MoveNext
Loop
End if
RS.Close
Set RS = Nothing
sqlnew=""
Response.Write("Feature Categories Successfully Re-Synced From Access to SQL Server
")
'End Delete/Re-populate SQL Server Feature Categories----------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'End Delete/Re-populate SQL Server Agency---------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Un-comment to empty database prior to re-populate
SQLConnect.Execute("DELETE IDXAgency")
Response.Write("
Agency Deleted Successfully From SQL Server
")
sqlnew="SELECT * FROM Agency ORDER BY ID ASC"
Set RS = Connect.execute(sqlnew)
If Not RS.BOF Then
RS.MoveFirst
Do Until RS.EOF
sqlnew="insert into IDXAgency(agency,grouping,address,city,state,zip,phone,fax,email,other,logo,extra1,extra2,extra3,extra4,extra5) values ('"&RS.Fields("agency")&"',"&RS.Fields("grouping")&",'"&RS.Fields("address")&"','"&RS.Fields("city")&"','"&RS.Fields("state")&"',"&RS.Fields("zip")&",'"&RS.Fields("phone")&"','"&RS.Fields("fax")&"','"&RS.Fields("email")&"','"&RS.Fields("other")&"','"&RS.Fields("logo")&"','"&RS.Fields("extra1")&"','"&RS.Fields("extra2")&"','"&RS.Fields("extra3")&"','"&RS.Fields("extra4")&"','"&RS.Fields("extra5")&"')"
SQLConnect.execute(sqlnew)
RS.MoveNext
Loop
End if
RS.Close
Set RS = Nothing
sqlnew=""
Response.Write("Agency Successfully Re-Synced From Access to SQL Server
")
'End Delete/Re-populate SQL Server Agency----------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Response.Write("
HouseViewOnline.com Access/SQL Server IDX Sync Completed on " & now() & "
")
Connect.Close
Set Connect = Nothing
SQLConnect.Close
Set SQLConnect = Nothing
'Dump Contents into the SQL Server Database+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
set msg = Server.CreateOBject("JMail.Message")
msg.Logging = True
msg.silent = True
msg.From = "scott@houseviewonline.com"
msg.FromName = "Scott Slinkard"
msg.AddRecipient "scott@houseviewonline.com"
msg.AddRecipient "5732254023@mobile.mycingular.com"
msg.Subject = "IDX Updated"
msg.Body = "IDX Update Completed Successfully at " & now()
msg.Priority = 3
msg.Send("mail.sitewerkz.com")
set msg = Nothing
Response.Write("
FTP, Un-Zip SuccessFul!
")
End if
%>
This will Update the IDX Feed
|
|
|
