-- set up the days of the week

set EmpDayStart to 9 -- row 9, the starting row of the first Employee out of pocket expenses

set CompDayStart to 19 -- row 19, the starting row of the first Comp. Credit Card expenses



set Mon to "" -- we're not using days of the week in this form, so set to blank.

set Tue to ""

set Wed to ""

set Thu to ""

set Fri to ""

set Sat to ""

set Sun to ""

set dateList to {1, 2, 3, 4, 5, 6, 7, 8}

set compdateList to {1, 2, 3, 4, 5, 6, 7, 8}

set itemsInDateList to 0

set itemsInCompDateList to 0



-- set up the description variables

set miscDescCol to 2 -- the column of the Misc items' description (Column 'B', or 2)

-- Note: we're using the "Item" field here.  If you prefer the

-- description info to go into the "Explanation" field, then change

-- this number to 5 (column 'E')



set miscAmountCol to 14 -- the column of the Misc items' amounts (Column 'N', or 14)

set miscDateCol to 1 -- the column of the Misc items' dates (Column 'A', or 1)

set miscRow to 53 -- the starting row of the Misc itemizations (Row 53)

set miscRowStop to 62



set entDateCol to 1

set entPlaceCol to 2

set entPersonsCol to 3 -- the column of the Misc items' description (Column 'C', or 3)

set entPurposeCol to 10

set entAmountCol to 14

set entRow to 38

set entRowStop to 48



-- set up the categories

set mileage to 3 -- col 3

set taxi to 5

set Fuel to taxi

set parking to taxi

set rental to taxi + 1

set fares to rental + 1

set lodging to fares + 1

set Breakfast to lodging + 1 -- row 22

set snack to Breakfast -- add snack to breakfast category

set Lunch to Breakfast + 1

set dinner to Lunch + 1

set Phone to dinner + 1

set entertainment to Phone + 1

set misc to entertainment + 1 -- even though we're funneling this into the "ent." category,

set supplies to misc

set tips to misc



-- we need to keep it separate for separate itemizations on page 2 of

-- the expense report.



-- You can only make changes in the top section of this script (above this line)

-- and below the End ExpensePlus data line.



-- The next section is overwritten each time you send data from the Newton

-- to the ExpensePlus Receiver.  If you add or delete any information in this

-- section, it will simply be overwritten and deleted.  It is important that

-- you do not change the "Start ExpensePlus data" and "End ExpensePlus data"

-- lines in any way.  If you do, the program will not work properly.

-- %%% Start ExpensePlus data %%% -- Do not change or delete this line!!!



set UserName to "Gary Amstutz"

set CompanyName to "State Of The Art"

set LocationVisited to ""

set Purpose to ""

set CashAdvance to "0"

set MileageRate to "0.28"

set UsersCountry to "USA"



set WeekEndingFriday to "10/28/94"

set WeekEndingSaturday to "10/29/94"

set WeekEndingSunday to "10/30/94"



set usersCurrencySign to "$"

set leadingCurrency to "1"

set countries to { "USA", 1}

set ExpenseData to {{Wed, "10/19/94", Taxi, 23, "", " ", "USA"},  B

{Wed, "10/19/94", Tips, 12, "", " ", "USA"},  B

{Wed, "10/19/94", Lodging, 230, "", " ", "USA"},  B

{Thu, "10/20/94", Lodging, 230, "", " ", "USA"},  B

{Fri, "10/21/94", Lodging, 230, "", " ", "USA"},  B

{Sat, "10/22/94", Lodging, 230, "", " ", "USA"},  B

{Sun, "10/23/94", Lodging, 230, "", " ", "USA"},  B

{Mon, "10/24/94", Lodging, 230, "", " ", "USA"},  B

{Tue, "10/25/94", Lodging, 230, "", " ", "USA"}}



-- %%% End ExpensePlus data %%% -- Do not change or delete this line!!!



-- You can make changes from here on down to the end of this script.



-- Note: The next line was added on 10/24/94 (script version 1.0.4a)

-- because UserName became a reserved word using Excel 5.0 with AppleScript

-- UserName was changed in 2 other places below (marked with "%%%")

set MyUserName to UserName -- %%% line added 10/24/94, GPA



tell application "Microsoft Excel"

	activate



	try

	open "Excel Expense Template 2"

	on error

		choose file with prompt "Find the 'Excel Expense Template 2' file:"

		open the result

		activate

	end try



	Open "Excel Expense Template 2"

	if Cell "UserName" exists then

		Activate

	else

		Activate

		choose file with prompt "Find the 'Excel Expense Template 2' file"

		Open the result

		Activate

	end if

	

	set excelVersion to version

	if excelVersion < 5 or excelVersion > 12 then set excelVersion to 4

	if (excelVersion > 4) then

		set winNumber to index of front Window

		if (IsZoomed of Window winNumber is false and zoomable of Window winNumber is true) then

			set IsZoomed of Window winNumber to true

		end if

	end if



	set Value of Cell "UserName" to MyUserName

	set Value of Cell "CompanyName" to CompanyName

	set Value of Cell "WeekEnding" to WeekEndingSaturday

	set Value of Cell "LocationVisited" to LocationVisited

	set Value of Cell "Purpose" to Purpose

	set Value of Cell "MileageRate" to MileageRate

	set Value of Cell "advances" to CashAdvance

	

	set currentItem to 1

	

	repeat with i from 1 to (number of items in ExpenseData)

		set ExpenseRecord to item i of ExpenseData

		set dayOfWeek to item 1 of ExpenseRecord

		set thisExpenseDate to item 2 of ExpenseRecord

		set myCategory to item 3 of ExpenseRecord

		set expAmount to item 4 of ExpenseRecord

		set expDescription to item 5 of ExpenseRecord

		set expMarker to item 6 of ExpenseRecord

		set currencyCountry to item 7 of ExpenseRecord

		

		if currencyCountry is not UsersCountry then

			if myCategory is not mileage then

				repeat with countryNum from 1 to (number of items in countries)

					if item countryNum of countries = currencyCountry then

						set expAmount to expAmount V (item (countryNum + 1) of countries)

						exit repeat

					end if

				end repeat

			end if

		end if

		

		set details1 to ""

		set details2 to ""

		set details3 to ""

		

		if expMarker is not "P" then -- if it's not a personal expense

			if number of items in ExpenseRecord > 7 then

				set details1 to item 8 of ExpenseRecord -- odometer start for miles, location for entertainment/meals

				if number of items in ExpenseRecord > 8 then

					set details2 to item 9 of ExpenseRecord -- odometer end for miles, persons present for ent/meals

					if number of items in ExpenseRecord > 9 then

						set details3 to item 10 of ExpenseRecord -- purpose of entertainment/meal

					end if

				end if

			end if

			

			if expMarker is "C" then -- if it was paid on the company credit card

				if compdateList does not contain thisExpenseDate then

					set itemsInCompDateList to itemsInCompDateList + 1

					if (itemsInCompDateList > (number of items in compdateList)) then

						set itemsInDateList to 1

						set dateList to {1, 2, 3, 4, 5, 6, 7, 8}

						set compdateList to {1, 2, 3, 4, 5, 6, 7, 8}

						set itemsInDateList to 1

						set itemsInCompDateList to 1

						set EmpDayStart to 9 -- row 9, the starting row of the first Employee out of pocket expenses

						set CompDayStart to 19 -- row 19, the starting row of the first Comp. Credit Card expenses

						set miscRow to 53 -- the starting row of the Misc itemizations (Row 38)

						set entRow to 38 -- the starting row of the Entertainment itemizations (Row 44)

						Open "Excel Expense Template 2"

						if (Cell "UserName" exists) and (Value of Cell "UserName" is 0) then

							Activate

						else

							Activate

							choose file with prompt "Open another 'Excel Expense Template 2' file"

						end if

						

						set Value of Cell "UserName" to MyUserName

						set Value of Cell "CompanyName" to CompanyName

						set Value of Cell "WeekEnding" to WeekEndingSaturday

						set Value of Cell "LocationVisited" to LocationVisited

						set Value of Cell "Purpose" to Purpose

						set Value of Cell "MileageRate" to MileageRate

					end if

					set item itemsInCompDateList of compdateList to thisExpenseDate

					set Value of Cell (itemsInCompDateList + CompDayStart - 1) of Column 1 to thisExpenseDate

					set Value of Cell (itemsInCompDateList + CompDayStart - 1) of Column 2 to expDescription

				else

					-- if there's more than one item per dayOfWeek, concatenate descriptions

					if (expDescription is not "") then

						if (number of Characters of (expDescription & B

							Value of Cell (itemsInDateList + EmpDayStart - 1) of Column 2)) < 200 then

							set Value of Cell (itemsInCompDateList + CompDayStart - 1) of Column 2 to (B

								Value of Cell (itemsInCompDateList + CompDayStart - 1) of Column 2) & ", " & expDescription

						end if

					end if

				end if

			else -- otherwise, employee paid for this expense

				if dateList does not contain thisExpenseDate then

					set itemsInDateList to itemsInDateList + 1

					if (itemsInDateList > (number of items in dateList)) then

						set itemsInDateList to 1

						set dateList to {1, 2, 3, 4, 5, 6, 7, 8}

						set compdateList to {1, 2, 3, 4, 5, 6, 7, 8}

						set itemsInDateList to 1

						set itemsInCompDateList to 1

						set EmpDayStart to 9 -- row 9, the starting row of the first Employee out of pocket expenses

						set CompDayStart to 19 -- row 19, the starting row of the first Comp. Credit Card expenses

						set miscRow to 53 -- the starting row of the Misc itemizations (Row 38)

						set entRow to 38 -- the starting row of the Entertainment itemizations (Row 44)

						Open "Excel Expense Template 2"

						if (Cell "UserName" exists) and (Value of Cell "UserName" is 0 or Value of Cell "UserName" is "") then

							Activate

						else

							Activate

							choose file with prompt "Open another 'Excel Expense Template 2' file"

						end if

						

						set Value of Cell "UserName" to MyUserName

						set Value of Cell "CompanyName" to CompanyName

						set Value of Cell "WeekEnding" to WeekEndingSaturday

						set Value of Cell "LocationVisited" to LocationVisited

						set Value of Cell "Purpose" to Purpose

						set Value of Cell "MileageRate" to MileageRate

					end if

					set item itemsInDateList of dateList to thisExpenseDate

					set Value of Cell (itemsInDateList + EmpDayStart - 1) of Column 1 to thisExpenseDate

					set Value of Cell (itemsInDateList + EmpDayStart - 1) of Column 2 to expDescription

				else

					-- if there's more than one item per dayOfWeek, concatenate descriptions

					if (expDescription is not "") then

						if (number of Characters of (expDescription & B

							Value of Cell (itemsInDateList + EmpDayStart - 1) of Column 2)) < 200 then

							set Value of Cell (itemsInDateList + EmpDayStart - 1) of Column 2 to (B

								Value of Cell (itemsInDateList + EmpDayStart - 1) of Column 2) & ", " & expDescription

						end if

					end if

				end if

			end if

			

			if myCategory is misc or myCategory is supplies then

				set Value of Cell miscRow of Column miscAmountCol to expAmount

				set Value of Cell miscRow of Column miscDateCol to thisExpenseDate

				set Value of Cell miscRow of Column miscDescCol to expDescription

				-- We have no explanation data to auto enter. (this may show up in a future version of ExpensePlus)

				-- User will have to hand-write their explanation on

				-- the printed form, or type it into the finished Excel Expense Report.

				set miscRow to miscRow + 1

				-- if the miscellaneous itemization section is fullI

				if miscRow is miscRowStop then

					set miscRow to 1

					set miscAmountCol to miscAmountCol + 14

					set miscDateCol to miscDateCol + 14

					set miscDescCol to miscDescCol + 14

				end if

			end if

			

			if myCategory is entertainment or myCategory is Breakfast or myCategory is Lunch or myCategory is dinner B

				or myCategory is snack then

				if details1 - "" or details2 - "" or details3 - "" then

					set Value of Cell entRow of Column entAmountCol to expAmount

					set Value of Cell entRow of Column entDateCol to thisExpenseDate

					set Value of Cell entRow of Column entPlaceCol to details1

					set Value of Cell entRow of Column entPersonsCol to details2

					set Value of Cell entRow of Column entPurposeCol to details3

					-- if the entertainment section is fullI

					if entRow is entRowStop then

						set entRow to entRow + 25

					end if

					set entRow to entRow + 1

				end if

			end if

			

			if expMarker is "C" then -- if it was paid on the company credit card

				set curRow to (itemsInCompDateList + CompDayStart - 1)

			else

				set curRow to (itemsInDateList + EmpDayStart - 1)

			end if

			

			if myCategory is supplies then set myCategory to entertainment -- funnel Supplies category into Entertainment category

			if myCategory is misc then set myCategory to myCategory - 1 -- funnel Misc category into Entertainment category

			

			set Value of Cell curRow of Column myCategory to (Value of Cell curRow of Column myCategory) + expAmount

		end if

		

	end repeat

end tell