set ftp = WScript.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 = "D:\webroot\localuser\houseview\IDX\" 
Set zip = WScript.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 = Wscript.CreateObject("Scripting.FileSystemObject")

 	 'Database Name And Location and Connection 
        strDbPathAndName = "D:\webroot\localuser\houseview\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 = Wscript.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("D:\webroot\localuser\houseview\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 = Wscript.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 MYSQL Server Database+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




  Set SQLConnect = Wscript.CreateObject("ADODB.Connection")
  SQLConnect.Open "IDX"
  Set Connect = Wscript.CreateObject("ADODB.Connection")
  Connect.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\webroot\localuser\houseview\IDX\IDX.mdb;"




'End Delete/Re-populate MYSQL Server Listings--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQLConnect.Execute("TRUNCATE TABLE Listings")


  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 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 ("&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=""
'End Delete/Re-populate MYSQL Server Listings--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



'End Delete/Re-populate MYSQL Server Features--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQLConnect.Execute("TRUNCATE TABLE Features")

  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 Features(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=""
'End Delete/Re-populate MYSQL Server Features--------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


'End Delete/Re-populate SQL Server Feature Categories----------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQLConnect.Execute("TRUNCATE TABLE FeatureCategories")

  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 FeatureCategories(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=""
'End Delete/Re-populate MYSQL Server Feature Categories----------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



'End Delete/Re-populate MYSQL Server Agency---------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQLConnect.Execute("TRUNCATE TABLE Agency")

  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 Agency(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=""
'End Delete/Re-populate MYSQL Server Agency----------------------------------------------------------
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


   Connect.Close
   Set Connect = Nothing
   SQLConnect.Close
   Set SQLConnect = Nothing



'Dump Contents into the MYSQL Server Database+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



 set msg = Wscript.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