Const OutlookPublicContactsFolderName = "DebtorList"
Const OutlookPublicCalendarFolderName = "JiwaSMDiary"
Const SMCalendarDaysInArrears = 1
Const SMCalendarDaysInAdvance = 1

Dim hwndRead
Dim FName,SName,ContactAccountNo,DebtorAccountNo,CompanyName,EmailAddress
Dim DBPhone,DBFax,CNPhone,CNFax,CNMobile,Address1,Address2,Suburb,State,PostCode

Dim SQSQL
Dim objOutlook,objItmContact,objItmEvent,objMyItem,ContactItems,JiwaCalItems,NumItems,Counter
Dim strFirstName,strLastName,strFullName,strCompanyName,strEmailAddress
Dim strBusinessPhone,strHomePhone,strBusinessFax,strMobilePhone
Dim strAddress1,strAddress2,strSuburb,strState,strPostCode
Dim MapiNameSpace,PublicFolders,AllPublicFolders,DebtorListFolder,JiwaCalFolder

Dim LoopApptStart,LoopApptEnd,LoopApptCategories
Dim Today,FilterStartIncl,FilterEndExcl,FilterEndIncl,FilterTechName
Dim DeletedCount,AddedCount
Dim DaysInArrears,DaysInAdvance,PublicFolderToPopulate

Const olAction = 32 
Const olActions = 33 
Const olAddressEntries = 21 
Const olAddressEntry = 8 
Const olAddressList = 7 
Const olAddressLists = 20 
Const olApplication = 0 
Const olAppointment = 26 
Const olAttachment = 5 
Const olAttachments = 18 
Const olContact = 40 
Const olDistributionList = 69 
Const olDocument = 41 
Const olException = 30 
Const olExceptions = 29 
Const olExplorer = 34 
Const olExplorers = 60 
Const olFolder = 2 
Const olFolders = 15 
Const olFormDescription = 37 
Const olInspector = 35 
Const olInspectors = 61 
Const olItems = 16 
Const olJournal = 42 
Const olLink = 75 
Const olLinks = 76 
Const olMail = 43 
Const olMeetingCancellation = 54 
Const olMeetingRequest = 53 
Const olMeetingResponseNegative = 55 
Const olMeetingResponsePositive = 56 
Const olMeetingResponseTentative = 57 
Const olNamespace = 1 
Const olNote = 44 
Const olOutlookBarGroup = 66 
Const olOutlookBarGroups = 65 
Const olOutlookBarPane = 63 
Const olOutlookBarShortcut = 68 
Const olOutlookBarShortcuts = 67 
Const olOutlookBarStorage = 64 
Const olOutlookPropertyPageSite = 70 
Const olPages = 36 
Const olPanes = 62 
Const olPost = 45 
Const olPropertyPages = 71 
Const olRecipient = 4 
Const olRecipients = 17 
Const olRecurrencePattern = 28 
Const olRemote = 47 
Const olReport = 46 
Const olSelection = 74 
Const olSyncObject = 72 
Const olSyncObjects = 73 
Const olTask = 48 
Const olTaskRequest = 49 
Const olTaskRequestAccept = 51 
Const olTaskRequestDecline = 52 
Const olTaskRequestUpdate = 50 
Const olUserProperties = 38 
Const olUserProperty = 39 

Const olFolderCalendar = 9
Const olFolderContacts = 10
Const olFolderDeletedItems = 3
Const olFolderDrafts = 16
Const olFolderInbox = 6
Const olFolderJournal = 11
Const olFolderNotes = 12
Const olFolderOutbox = 6
Const olFolderSentMail = 5
Const olFolderTasks = 13

Const olMailItem = 0
Const olAppointmentItem = 1
Const olContactItem = 2
Const olTaskItem = 3
Const olJournalItem = 4
Const olNoteItem = 5
Const olPostItem = 6

If (MenuIndex = 0) Or (MenuIndex = 1) Then

	PublicFolderToPopulate = OutlookPublicContactsFolderName

	If (MenuIndex = 0) Then
		MsgBox "RACS - Exporting Contacts to PUBLIC '" + PublicFolderToPopulate + "' Folder"
	Else
		MsgBox "RACS - Exporting Contacts to PERSONAL Contacts Folder"
	End If

    Set objOutlook = CreateObject("Outlook.application")
	Set MapiNameSpace = objOutlook.GetNamespace("MAPI")
	If (MenuIndex = 0) Then
		Set PublicFolders    = MapiNameSpace.Folders("Public Folders")
		Set AllPublicFolders = PublicFolders.Folders("All Public Folders")
		Set DebtorListFolder = AllPublicFolders.Folders(PublicFolderToPopulate)
	Else
		Set DebtorListFolder = MapiNameSpace.GetDefaultFolder(olFolderContacts)
	End If

'    Set DebtorListFolder = objOutlook.Session.GetDefaultFolder(olFolderContacts)
'    For Each objMyItem In DebtorListFolder.Items
'	wscript.echo objMyItem.FirstName & objMyItem.LastName & objMyItem.Email1Address
'	objMyItem.Delete
'    Next
	' delete all items in the folder  {{{
	' (see http://support.microsoft.com/?kbid=222480)
    Set ContactItems = DebtorListFolder.Items
    NumItems = ContactItems.Count
    For Counter = NumItems To 1 Step -1
      If (ContactItems(Counter).Categories = "Jiwa Contact") Then
        ContactItems(Counter).Delete
      End If
    Next
	' }}}
'	MsgBox "Deletions Complete"

	NumItems = 0
	With JobObject.Database
		hwndRead = .StatementOpen(.ConnectionRead1, False, "", 0)
		SQSQL= "Execute sp_racs_GrabAllDebtorContacts"
		If .ExecuteSelect(CInt(hwndRead),SQSQL,True) = True Then
			Do While (.FetchRow(CInt(hwndRead)) = True)
				FName = .GetData(CInt(hwndRead), 1)
				SName = .GetData(CInt(hwndRead), 2)
				ContactAccountNo = .GetData(CInt(hwndRead), 3)
				DebtorAccountNo = .GetData(CInt(hwndRead), 4)
				CompanyName = .GetData(CInt(hwndRead), 5)
				EmailAddress = .GetData(CInt(hwndRead), 6)
				DBPhone = .GetData(CInt(hwndRead), 7)
				DBFax = .GetData(CInt(hwndRead), 8)
				CNPhone = .GetData(CInt(hwndRead), 9)
				CNFax = .GetData(CInt(hwndRead), 10)
				CNMobile = .GetData(CInt(hwndRead), 11)
				Address1 = .GetData(CInt(hwndRead), 12)
				Address2 = .GetData(CInt(hwndRead), 13)
				Suburb = .GetData(CInt(hwndRead), 14)
				State = .GetData(CInt(hwndRead), 15)
				PostCode = .GetData(CInt(hwndRead), 16)
				NumItems = NumItems + 1


	Set objItmContact = DebtorListFolder.Items.Add(olContactItem)
'	Set objItmContact = objOutlook.CreateItem(olContactItem)
	strFirstName = Trim(FName)

	If (IsNull(SName)) Then
	  strLastName = ""
	Else
	  strLastName = Trim(SName)
	End If

	strFullName = Trim(strFirstName + " " + strLastName)

	strCompanyName = Trim(CompanyName)

	If (IsNull(EmailAddress)) Then
	  strEMailAddress = ""
	Else
	  strEMailAddress = Trim(EmailAddress)
	End If

	If (IsNull(DBPhone) Or (Trim(DBPhone) = "")) Then
	  strBusinessPhone = ""
	Else
'	  strBusinessPhone = "+61 " + trim(DBPhone)
	  strBusinessPhone = Trim(DBPhone)
	End If

	If (IsNull(CNPhone) Or (Trim(CNPhone) = "")) Then
	  strHomePhone = ""
	Else
'	  strHomePhone = "+61 " + trim(CNPhone)
	  strHomePhone = Trim(CNPhone)
	End If

	If (IsNull(DBFax) Or (Trim(DBFax) = "")) Then
	  If (IsNull(CNFax) Or (Trim(CNFax) = "")) Then
	    strBusinessFax = ""
	  Else
'	    strBusinessFax = "+61 " + trim(CNFax)
	    strBusinessFax = Trim(CNFax)
	  End If
	Else
'	  strBusinessFax = "+61 " + trim(DBFax)
	  strBusinessFax = Trim(DBFax)
	End If

	If (IsNull(CNMobile) Or (Trim(CNMobile) = "")) Then
	  strMobilePhone = ""
	Else
'	  strMobilePhone = "+61 " + trim(CNMobile)
	  strMobilePhone = Trim(CNMobile)
	End If

	strAddress1 = Trim(Address1)
	strAddress2 = Trim(Address2)
	strSuburb = Trim(Suburb)
	strState = Trim(State)
	strPostCode = Trim(PostCode)

'    WScript.Echo "Processing AccountNo '" & DebtorAccountNo & "' - '" & strFullName & "'"
	With objItmContact
	  .FirstName = strFirstName
	  .LastName = strLastName
	  .FullName = strFullName
	  .CompanyName = strCompanyName
	  .EMail1Address = strEmailAddress
	  .BusinessTelephoneNumber = Replace(strBusinessPhone,"(0","+61 (")
	  .HomeTelephoneNumber = Replace(strHomePhone,"(0","+61 (")
	  .BusinessFaxNumber = Replace(strBusinessFax,"(0","+61 (")
	  .MobileTelephoneNumber = Replace(strMobilePhone,"(0","+61 (")
	  .Categories = "Jiwa Contact"
	  .MailingAddressStreet = strAddress1 + VBCRLF + strAddress2
	  .MailingAddressCity = strSuburb
	  .MailingAddressState = strState
	  .MailingAddressPostalCode = strPostCode
	  .MailingAddressCountry = "Australia"
	  .Save
	End With
	Set objItmContact = Nothing

			Loop
			MsgBox "Records = " + CStr(numItems)
		Else
			MsgBox .ErrorMessage
		End If
		.StatementClose CInt(hwndRead)
	End With

End If
If (MenuIndex = 2) Or (MenuIndex = 3) Then

	DaysInArrears = SMCalendarDaysInArrears
	DaysInAdvance = SMCalendarDaysInAdvance
	PublicFolderToPopulate = OutlookPublicCalendarFolderName

	FilterTechName = ""
	FilterTechName = Trim(InputBox("Enter Jiwa Tech name for calendar you require exported (leave blank for ALL Techs)"))
	If (MenuIndex = 2) Then
		MsgBox "RACS - Export Appointments to PUBLIC '" + PublicFolderToPopulate + "' Folder"
	Else
		MsgBox "RACS - Export Appointments to PERSONAL Calendar Folder"
	End If

	Today = JobObject.Database.FormatDate(JobObject.Database.SysDateTime)
	'MsgBox Today
	Today = Replace(Today,"{d '","")
	Today = Replace(Today,"'}","")
	'MsgBox "Today" + CStr(Today)
	FilterStartIncl = DateAdd("d",-(DaysInArrears),Today)
	FilterStartIncl = JobObject.Database.FormatDate(FilterStartIncl)
	FilterStartIncl = Replace(FilterStartIncl,"{d '","")
	FilterStartIncl = Replace(FilterStartIncl,"'}","")
	FilterStartIncl = CStr(Day(FilterStartIncl)) + "/" + MonthName(Month(FilterStartIncl)) + "/" + CStr(Year(FilterStartIncl))
	'MsgBox "Start" + FilterStartIncl
	FilterEndExcl = DateAdd("d",(DaysInAdvance + 1),Today)
	FilterEndExcl = JobObject.Database.FormatDate(FilterEndExcl)
	FilterEndExcl = Replace(FilterEndExcl,"{d '","")
	FilterEndExcl = Replace(FilterEndExcl,"'}","")
	FilterEndExcl = CStr(Day(FilterEndExcl)) + "/" + MonthName(Month(FilterEndExcl)) + "/" + CStr(Year(FilterEndExcl))
	'MsgBox "EndExcl" + FilterEndExcl
	FilterEndIncl = DateAdd("d",(DaysInAdvance),Today)
	FilterEndIncl = JobObject.Database.FormatDate(FilterEndIncl)
	FilterEndIncl = Replace(FilterEndIncl,"{d '","")
	FilterEndIncl = Replace(FilterEndIncl,"'}","")
	FilterEndIncl = CStr(Day(FilterEndIncl)) + "/" + MonthName(Month(FilterEndIncl)) + "/" + CStr(Year(FilterEndIncl))
	'MsgBox "EndIncl" + FilterEndIncl + CStr(CDate(FilterEndIncl))
	SQSQL= "Execute sp_racs_HoursWorked '" & FilterTechName & "','','" & FilterStartIncl & "','" & FilterEndIncl & "','0'"
	FilterStartIncl = CDate(FilterStartIncl)
	FilterEndExcl = CDate(FilterEndExcl)
	FilterEndIncl = CDate(FilterEndIncl)

    Set objOutlook = CreateObject("Outlook.application")
	Set MapiNameSpace = objOutlook.GetNamespace("MAPI")
	If (MenuIndex = 2) Then
		Set PublicFolders    = MapiNameSpace.Folders("Public Folders")
		Set AllPublicFolders = PublicFolders.Folders("All Public Folders")
		Set JiwaCalFolder = AllPublicFolders.Folders(PublicFolderToPopulate)
	Else
		Set JiwaCalFolder = MapiNameSpace.GetDefaultFolder(olFolderCalendar)
'		Set JiwaCalFolder = AllPublicFolders.Folders(FilterTechName)
	End If

    Set JiwaCalItems = JiwaCalFolder.Items
    NumItems = JiwaCalItems.Count
    DeletedCount = 0
    For Counter = NumItems To 1 Step -1
    	LoopApptStart = JiwaCalItems(Counter).Start
    	LoopApptEnd = JiwaCalItems(Counter).End
    	LoopApptCategories = JiwaCalItems(Counter).Categories
    	If(LoopApptCategories = "JiwaAutoAppt" And _
				(  ((LoopApptStart >= FilterStartIncl) And (LoopApptStart < FilterEndExcl)) _
				Or ((LoopApptStart <  FilterStartIncl) And (LoopApptEnd   > FilterStartIncl))) _
				) Then
'			WScript.Echo "Deleting " & JiwaCalItems(Counter).Subject
      		JiwaCalItems(Counter).Delete
      		DeletedCount = DeletedCount + 1
      	End If
    Next
	MsgBox "Total Deleted : " & DeletedCount

	NumItems = 0
	AddedCount = 0
	With JobObject.Database
		hwndRead = .StatementOpen(.ConnectionRead1, False, "", 0)
' Next line had to be moved due to bloody date conversion crap !!!
'		SQSQL= "Execute sp_racs_HoursWorked '" & FilterTechName & "','','" & FilterStartIncl & "','" & FilterEndIncl & "','0'"
'		MsgBox SQSQL
		If .ExecuteSelect(CInt(hwndRead),SQSQL,True) = True Then
			Do While (.FetchRow(CInt(hwndRead)) = True)
				ServiceNo = .GetData(CInt(hwndRead), 1)
				AccountNo = .GetData(CInt(hwndRead), 2)
				FName = .GetData(CInt(hwndRead), 3)
				JobNo = .GetData(CInt(hwndRead), 4)
				Description = .GetData(CInt(hwndRead), 5)
				StatusDesc = .GetData(CInt(hwndRead), 7)
				StartTime = .GetData(CInt(hwndRead), 9)
				EndTime = .GetData(CInt(hwndRead), 10)
				NumItems = NumItems + 1

	Set objItmEvent = JiwaCalFolder.Items.Add(olAppointmentItem)
'    WScript.Echo "Processing '" & SQRS("ServiceNo") & "-" & SQRS("JobNo") & "'"
	With objItmEvent
	  .Start = StartTime
	  .End = EndTime
	  .Subject = FName & " : " & AccountNo & " - " & StatusDesc
	  .Body = ServiceNo & "-" & JobNo & " - " & Description
	  .Categories = "JiwaAutoAppt"
	  .ReminderSet = False
	  .Save
	End With
	AddedCount = AddedCount + 1
	Set objItmEvent = Nothing

			Loop
			MsgBox "Total Added : " & AddedCount
		Else
			MsgBox .ErrorMessage
		End If
		.StatementClose CInt(hwndRead)
	End With

End If

Set objItmContact = Nothing
Set objItmEvent = Nothing
Set objOutlook = Nothing
Set DebtorListFolder = Nothing
Set JiwaCalFolder = Nothing
Set PublicFolders    = Nothing
Set AllPublicFolders = Nothing
Set MapiNameSpace = Nothing

End Sub