Monday, December 22, 2003

Authorize.net using PHP and curl

I needed a method to collect donations from patrons to the museum website that involved something a little less that what we offered before, PayPal. Not that I don't like the idea of PayPal, mind you, but it requires that the user create an account if they haven't got one, and that is a pain in the ass. So since I finished our OSC shopping cart for the Online Museum Store, we already had the mechanism in place. SSL certs, Authorize.net account, etc. Found a nice sample script at Zend.com that got me started. It was very simple, and I needed to get more from it. This is what I ended up with. First, a file called donate.php, that contains the form, and passes vars to the processing file, auth.php. Here are the files. Hope they help someone. JC

Saturday, December 06, 2003

Sourceforge website done...

...it's here. Now to just find a way to get folks to find it and use it... JC

Wednesday, December 03, 2003

Sourceforge project accepted.

Woohoo! My sourceforge submission of the museum collection management database was accepted. No to throw a website together to draw some attention to it...

Friday, November 21, 2003

SSH and Samba

One of my tasks was to set up a Campaign office in downtown Racine, to house our Development Office. Their primary role was to raise money for the new Racine Art Museum.

I set up SBC DSL, small office package for the connection to the web. Parked a Linux firewall/file-server/proxy server in front of the workstations to protect them from the baddies ;-). It was an old 233mhz machine I picked up for change, and worked like a champ. I bullet-proofed it with scripts and tutorials from Dranch and his work with TrinityOS. Great job David! I also use Tripwire, a great project.

I needed to provide some sort of method to provide these users access to our file server at our main office, where we were being supplied with SBS DSL, but this time the service came with a 5 port router, and all external IP's were NAT'd to internal addresses in the router.

I wanted to use FreeS/WAN, but I was having too much trouble punching a hole through the router, so I used SSH and Samba. I setup a common user on the remote and local box, and created a password-less key.

Then I used this script:

#!/bin/sh
# Source function library.
. /etc/init.d/functions

# Get config.
. /etc/sysconfig/network

# Check that networking is up.
if [ ${NETWORKING} = "no" ]
then
        echo "Networking is not setup on this computer."
        exit 0
fi


# Check that DSL is up.
host="ameritech.net"
ping -c1 $host 2>&1 1>/dev/null

if [ $? -eq  0 ]; then
        echo "$host is up..."
        else
        echo "$host is not up, RAM is DOWN."
        echo "Call Ameritech and check our service."
        exit 0
fi


## Check that Wustum is up.
if ! wget -T 30 -O /dev/null -q http://www.wustum.org/web/Default.htm; then
        echo "Wustum is DOWN."
        echo "Call John and have him check our service."
        exit 0
else
        echo "Wustum is up..."
fi



# If smb is already started, stop it
#
[ -f "/var/lock/subsys/smb" ] || /etc/rc.d/init.d/smb stop


#
# If Netbios is listening, kill the proc
#
NETBIOS_PID=`lsof -i TCP | grep 'netbios-ssn (LISTEN)' | grep -v grep | awk '{print$2}'`

if [ "$NETBIOS_PID" -gt "0" ]
        then
         kill pid "$NETBIOS_PID"
fi

killall -9 ssh

#
# Forward these ports
#
REMOTE_SMB_HOST=192.168.10.10 # fill in IP address here
REMOTE_SSH_HOST=wustum.org # fill in IP address here

ssh -N -f -l smbuser -i /home/public/.ssh/id_dsa -g \
        -L 5137:$REMOTE_SMB_HOST:137 \
        -L 5138:$REMOTE_SMB_HOST:138 \
        -L 5139:$REMOTE_SMB_HOST:139 \
        $REMOTE_SSH_HOST

#
# Start it back up again
#
/etc/rc.d/init.d/smb start

echo
echo

#
# Find out what is mounted, we don't want to mount it twice
#
#APPS=`mount | grep '/home/apps' | grep -v grep | awk '{print $3}'`
OFSCAN=`mount | grep '/home/ofscan' | grep -v grep | awk '{print $3}'`
COMPANY=`mount | grep '/home/Company' | grep -v grep | awk '{print $3}'`
JSIMONSEN=`mount | grep '/home/jsimonsen' | grep -v grep | awk '{print $3}'`
SBUHLER=`mount | grep '/home/sbuhler-maki' | grep -v grep | awk '{print $3}'`

#count=0
#echo "Checking the apps folder..."
#count=$(ls -fa /home/apps | wc -l)

#if [ "$count" -gt "2" ]; then
#       echo "Apps is connected and contains files!"
#       echo
#       echo
#else
#
#       if [ "$APPS" != "/home/apps" ]
#               then
#               echo "Apps is connected but contains no files!"
#               echo
#               echo "Reconnecting the apps folder..."
#               echo
#               umount /home/apps
#       fi

#       mount -t smbfs -o dmask=2777,port=5139,username=myuser,password=mypassword //localhost/apps /home/apps


#       echo "The apps folder has been reconnected."
#       echo
#fi

count=0
echo "Checking the ofscan folder..."
count=$(ls -fa /home/ofscan | wc -l)

if [ "$count" -gt "2" ]; then
        echo "Ofscan is connected and contains files!"
        echo
else
        if [ "$OFSCAN" != "/home/ofscan" ]
                then
                echo "Ofscan is connected but contains no files."
                echo
                echo "Reconnecting the Ofscan folder..."
                echo
                umount /home/ofscan
        fi

        mount -t smbfs -o dmask=2777,port=5139,username=myuser,password=mypassword //localhost/ofscan /home/ofscan

        echo "The Ofscan folder has been reconnected."
        echo
fi

count=0
echo "Checking the company folder..."
count=$(ls -fa /home/Company | wc -l)

if [ "$count" -gt "2" ]; then
        echo "Company is connected and contains files!"
        echo
else
        if [ "$COMPANY" != "/home/Company" ]
                then
                echo "Company is connected but contains no files!"
                echo
                echo "Reconnecting the Company folder..."
                echo
                umount /home/Company
        fi

        mount -t smbfs -o dmask=2777,port=5139,username=myuser,password=mypassword //localhost/Company /home/Company

        echo "The Company folder has been reconnected."
        echo
fi

count=0
echo "Checking Sue's folder..."
count=$(ls -fa /home/sbuhler-maki | wc -l)

if [ "$count" -gt "2" ]; then
        echo "Sbuhler-Maki is connected and contains files!"
        echo
else
        if [ "$SBUHLER" != "/home/sbuhler-maki" ]
                then
                echo "Sbuhler-Maki is connected but contains no files!"
                echo
                echo "Reconnecting the sbuhler-maki folder..."
                echo
                umount /home/sbuhler-maki
        fi

        mount -t smbfs -o dmask=2777,port=5139,username=myuser,password=mypassword //localhost/SBuhlerMaki /home/sbuhler-maki

        echo "The sbuhler-maki folder has been reconnected."
        echo
fi


count=0
echo "Checking Joedy's folder..."
count=$(ls -fa /home/jsimonsen | wc -l)

if [ "$count" -gt "2" ]; then
        echo "JSimonsen is connected and contains files!"
        echo
else
        if [ "$JSIMONSEN" != "/home/jsimonsen" ]
                then
                echo "JSimonsen is connected but contains no files!"
                echo
                echo "Reconnecting the JSimonsen folder..."
                echo
                umount /home/jsimonsen
        fi

        mount -t smbfs -o dmask=2777,port=5139,username=myuser,password=mypass //localhost/JSimonsen /home/jsimonsen

        echo "The jsimonsen folder has been reconnected."
        echo
fi
exit 0

This script was also set up to be run by the folks in the office via a WebMin interface. Since the building they were in was so antiquated, and thus the wiring and power supply were "delicate", they experienced LOTS of phone wire noise and frequent power outages, breaking the connection.

Thank God that's all over...

Even with all the headaches, they still got Virus scan updates, and could work on files stored on the remote server. If we had a little more speed on the upstream end at the home office, things would have been a little more tolerable (it was only 128k up...)

Wednesday, November 19, 2003

Cool VBA Scripting

We use My Survey MySQL to record visitors to the museum. Unfortunately, My Survey records to the exact second each entry is recorded. We also chose to record zip codes for each visitor. That posed an interesting problem, since My Survey simply sorts the data as input, giving us no way to display by month, or sort the zip codes by state, unless mucho hacking took place, and I'm no perl dude... Since our Development Director along with the Marketing Commitee needed a more resonable way to view this, I decided to use Excel and VBA. My Survey allows the user to export a csv file, so the following code works like a champ in a code module for Excel:

Option Explicit
Dim intRow As Integer, sCol As String, rRange As String
    Dim szFilter As String
    Dim szTitle As String
    Dim szFile As String, intReturn As Integer, intCount As Integer, blnFilled As Boolean




Sub SelectCSV()

    szFilter = "Survey Files (*.csv),*.csv"
    szTitle = "Please Select a survey File"

    szFile = Application.GetOpenFilename(szFilter, , szTitle)


' This makes sure the correct file is used, since nothing else works.

    If szFile <> "False" Then
        If LCase(Right$(szFile, 10)) <> "survey.csv" Then
            MsgBox "Bad Filename!", vbCritical, "Error"
        End If
    End If

End Sub



Sub auto_open()


'
' Insert_Data

'Open the file
Call SelectCSV

'This can be "broken" by hitting ESC twice...
If szFile = "False" Then
    intReturn = MsgBox("Would you like to try again?", vbYesNo, "Try Again?")
    Select Case intReturn
    Case Is = 6
    Call SelectCSV
    Case Is = 7
    Application.Quit
    End Select
End If


'Insert a new sheet
Sheets.Add
ActiveSheet.Name = "Data"
     With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & szFile, Destination:=Range("A2"))
        .Name = "survey"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 2, 1, 2, 2, 2, 2, 1)
        .Refresh BackgroundQuery:=False
    End With
    
'Location of last row
    intRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    
    
'
' Insert_Header
'

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ZIP"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "AGE"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "GENDER"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "DATE (Web)"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "MEMBER"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "RACE"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "TYPE"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "DIABILITY"
    Rows("1:1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Columns("H:H").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "STATE"
    Range("B2").Select


'
' Insert_State_Data
'

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],ZipCodes!R2C2:R33108C3,2,FALSE)"
    Range("B2").Select
    sCol = "B"
    rRange = sCol & intRow
    Range("B2", rRange).Select
    Selection.FillDown
    ActiveWindow.ScrollRow = 1
    
    
' Insert_Visitor_By_State
'


    Sheets("ZipCodes").Select
    Range("H1:H52").Select
    Range("H52").Activate
    Selection.Copy
    Sheets("Data").Select
    Range("K1").Select
    Columns("K:K").ColumnWidth = 36.71
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 3
    Range("L1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "VISITORS"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "STATE"
    Range("L2").Select
    'ActiveCell.FormulaR1C1 = "=COUNTIF("B2:R" & intRow & "C[-10],RC[-1])"
    ActiveCell.FormulaR1C1 = "=COUNTIF(R2C[-10]:R" & intRow & "C[-10], RC11)"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L52"), Type:=xlFillDefault
    Range("L2:L52").Select
    Range("K2:L52").Select
    Range("L52").Activate
    Selection.Font.Bold = True


' Human_Readable_Date
' Since the date recorded by My Survey is in Unix Time stamp (# secs from Jan 1, 1970)


    Range("J1").Select
    ActiveCell.FormulaR1C1 = "DATE"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]/86400+DATE(1970,1,1)"
    Range("J2").Select
    sCol = "J"
    rRange = sCol & intRow
    Range("J2", rRange).Select
    Selection.FillDown
    Selection.NumberFormat = "mm/yyyy"
    
    
    
' Visitor_By_Date
' 

    Range("M1").Select
    ActiveCell.FormulaR1C1 = "YEAR"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "2003"
    Selection.AutoFill Destination:=Range("M2:M13"), Type:=xlFillDefault
    Range("M14").Select
    ActiveCell.FormulaR1C1 = "2004"
    Selection.AutoFill Destination:=Range("M14:M25"), Type:=xlFillDefault
    Range("M26").Select
    ActiveCell.FormulaR1C1 = "2005"
    Selection.AutoFill Destination:=Range("M26:M37"), Type:=xlFillDefault
    Range("M38").Select
    ActiveCell.FormulaR1C1 = "2006"
    Selection.AutoFill Destination:=Range("M38:M49"), Type:=xlFillDefault
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "MONTH"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "VISITOR"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=SUMPRODUCT((YEAR(R2C10:R" & intRow & "C10)=RC13)*(MONTH(R2C10:R" & intRow & "C10)=RC16)*1)"
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O49"), Type:=xlFillDefault

'Populate the number of month for calculations
'select our first cell
    Range("P2").Select
'set the counter
    intCount = 1
    Do While intCount < 13
        ActiveCell.FormulaR1C1 = intCount
        ActiveCell.Offset(1, 0).Select
        intCount = intCount + 1
    Loop

'select our next cell
    Range("P14").Select
'reset counter
    intCount = 1
    Do While intCount < 13
        ActiveCell.FormulaR1C1 = intCount
        ActiveCell.Offset(1, 0).Select
        intCount = intCount + 1
    Loop

'select our next cell
    Range("P26").Select
'reset counter
    intCount = 1
    Do While intCount < 13
        ActiveCell.FormulaR1C1 = intCount
        ActiveCell.Offset(1, 0).Select
        intCount = intCount + 1
    Loop
    
'select our next cell
    Range("P38").Select
'reset counter
    intCount = 1
    Do While intCount < 13
        ActiveCell.FormulaR1C1 = intCount
        ActiveCell.Offset(1, 0).Select
        intCount = intCount + 1
    Loop

' Insert a user friendly date format for the chart

    Range("N2").Select
    ActiveCell.FormulaR1C1 = "JAN '03"
    Range("N3").Select
    ActiveCell.FormulaR1C1 = "FEB '03"
    Range("N4").Select
    ActiveCell.FormulaR1C1 = "MAR '03"
    Range("N5").Select
    ActiveCell.FormulaR1C1 = "APRIL '03"
    Range("N6").Select
    ActiveCell.FormulaR1C1 = "MAY '03"
    Range("N7").Select
    ActiveCell.FormulaR1C1 = "JUNE '03"
    Range("N8").Select
    ActiveCell.FormulaR1C1 = "JULY '03"
    Range("N9").Select
    ActiveCell.FormulaR1C1 = "AUG '03"
    Range("N10").Select
    ActiveCell.FormulaR1C1 = "SEPT '03"
    Range("N11").Select
    ActiveCell.FormulaR1C1 = "OCT '03"
    Range("N12").Select
    ActiveCell.FormulaR1C1 = "NOV '03"
    Range("N13").Select
    ActiveCell.FormulaR1C1 = "DEC '03"
    Range("N14").Select
    ActiveCell.FormulaR1C1 = "JAN '04"
    Range("N15").Select
    ActiveCell.FormulaR1C1 = "FEB '04"
    Range("N16").Select
    ActiveCell.FormulaR1C1 = "MAR '04"
    Range("N17").Select
    ActiveCell.FormulaR1C1 = "APRIL '04"
    Range("N18").Select
    ActiveCell.FormulaR1C1 = "MAY '04"
    Range("N19").Select
    ActiveCell.FormulaR1C1 = "JUNE '04"
    Range("N20").Select
    ActiveCell.FormulaR1C1 = "JULY '04"
    Range("N21").Select
    ActiveCell.FormulaR1C1 = "AUG '04"
    Range("N22").Select
    ActiveCell.FormulaR1C1 = "SEPT '04"
    Range("N23").Select
    ActiveCell.FormulaR1C1 = "OCT '04"
    Range("N24").Select
    ActiveCell.FormulaR1C1 = "NOV '04"
    Range("N25").Select
    ActiveCell.FormulaR1C1 = "DEC '04"
    Range("N26").Select
    ActiveCell.FormulaR1C1 = "JAN '05"
    Range("N27").Select
    ActiveCell.FormulaR1C1 = "FEB '05"
    Range("N28").Select
    ActiveCell.FormulaR1C1 = "MAR '05"
    Range("N29").Select
    ActiveCell.FormulaR1C1 = "APR '05"
    Range("N30").Select
    ActiveCell.FormulaR1C1 = "MAY '05"
    Range("N31").Select
    ActiveCell.FormulaR1C1 = "JUNE '05"
    Range("N32").Select
    ActiveCell.FormulaR1C1 = "JULY '05"
    Range("N33").Select
    ActiveCell.FormulaR1C1 = "AUG '05"
    Range("N34").Select
    ActiveCell.FormulaR1C1 = "SEPT '05"
    Range("N35").Select
    ActiveCell.FormulaR1C1 = "OCT '05"
    Range("N36").Select
    ActiveCell.FormulaR1C1 = "NOV '05"
    Range("N37").Select
    ActiveCell.FormulaR1C1 = "DEC '05"
    Range("N38").Select
    ActiveCell.FormulaR1C1 = "JAN '06"
    Range("N39").Select
    ActiveCell.FormulaR1C1 = "FEB '06"
    Range("N40").Select
    ActiveCell.FormulaR1C1 = "MAR '06"
    Range("N41").Select
    ActiveCell.FormulaR1C1 = "APRIL '06"
    Range("N42").Select
    ActiveCell.FormulaR1C1 = "MAY '06"
    Range("N43").Select
    ActiveCell.FormulaR1C1 = "JUNE '06"
    Range("N44").Select
    ActiveCell.FormulaR1C1 = "JULY '06"
    Range("N45").Select
    ActiveCell.FormulaR1C1 = "AUG '06"
    Range("N46").Select
    ActiveCell.FormulaR1C1 = "SEPT '06"
    Range("N47").Select
    ActiveCell.FormulaR1C1 = "OCT '06"
    Range("N48").Select
    ActiveCell.FormulaR1C1 = "NOV '06"
    Range("N49").Select
    ActiveCell.FormulaR1C1 = "DEC '06"
    Range("M1:O49").Select
    Selection.Font.Bold = True


' Chart_By_State
'

    Range("K1:L60").Select
    Charts.Add
    ActiveChart.ChartType = xl3DBarClustered
    ActiveChart.SetSourceData Source:=Sheets("Data").Range("K1:L52"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Visitor By State"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory).HasTitle = False
        .Axes(xlSeries).HasTitle = False
        .Axes(xlValue).HasTitle = False
    End With
    With ActiveChart
        .HasAxis(xlCategory) = True
        .HasAxis(xlSeries) = False
        .HasAxis(xlValue) = True
    End With
    ActiveChart.Axes(xlCategory).CategoryType = xlAutomatic
    ActiveChart.HasLegend = False
    ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False
    ActiveChart.HasDataTable = False
    With ActiveChart.PageSetup
        .Orientation = xlPortrait
    End With
    ActiveChart.Walls.Select
    ActiveChart.PlotArea.Select
    ActiveChart.Axes(xlCategory).Select
    Selection.TickLabels.AutoScaleFont = True
    With Selection.TickLabels.Font
        .Name = "Avenir 35 Light"
        .Size = 5
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With


'Chart by month '03
Sheets("Data").Select
Range("O2").Select
intCount = 1
Do While intCount < 13
    If ActiveCell = 0 Then
    blnFilled = False
    Else
    blnFilled = True
    Exit Do
    End If
    intCount = intCount + 1
    ActiveCell.Offset(1, 0).Select
Loop

If blnFilled = True Then
    Range("N1:O13").Select
    Charts.Add
    ActiveChart.ChartType = xlPie
    ActiveChart.SetSourceData Source:=Sheets("Data").Range("N1:O13"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
        "Visitors By Month '03"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Vistors By Month '03"
    End With
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlLeft
    ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False, _
        HasLeaderLines:=True
            ActiveChart.Legend.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Regular"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    Selection.Height = 187
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.ChartTitle.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
End If

blnFilled = False

'Chart by month '04

Sheets("Data").Select
Range("O14").Select
intCount = 1
Do While intCount < 13
    If ActiveCell = 0 Then
    blnFilled = False
    Else
    blnFilled = True
    Exit Do
    End If
    intCount = intCount + 1
    ActiveCell.Offset(1, 0).Select
Loop

If blnFilled = True Then
    Range("N14:O25").Select
    Charts.Add
    ActiveChart.ChartType = xlPie
    ActiveChart.SetSourceData Source:=Sheets("Data").Range("N14:O25"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
        "Visitors By Month '04"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Vistors By Month '04"
    End With
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlLeft
    ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False, _
        HasLeaderLines:=True
            ActiveChart.Legend.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Regular"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    Selection.Height = 187
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.ChartTitle.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
End If

blnFilled = False



'Chart by month '05

Sheets("Data").Select
Range("O26").Select
intCount = 1
Do While intCount < 13
    If ActiveCell = 0 Then
    blnFilled = False
    Else
    blnFilled = True
    Exit Do
    End If
    intCount = intCount + 1
    ActiveCell.Offset(1, 0).Select
Loop

If blnFilled = True Then
    Range("N26:O37").Select
    Charts.Add
    ActiveChart.ChartType = xlPie
    ActiveChart.SetSourceData Source:=Sheets("Data").Range("N26:O37"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
        "Visitors By Month '05"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Vistors By Month '05"
    End With
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlLeft
    ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False, _
        HasLeaderLines:=True
            ActiveChart.Legend.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Regular"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    Selection.Height = 187
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.ChartTitle.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
End If

blnFilled = False


'Chart by month '06

Sheets("Data").Select
Range("O38").Select
intCount = 1
Do While intCount < 13
    If ActiveCell = 0 Then
    blnFilled = False
    Else
    blnFilled = True
    Exit Do
    End If
    intCount = intCount + 1
    ActiveCell.Offset(1, 0).Select
Loop

If blnFilled = True Then
    Range("N38:O49").Select
    Charts.Add
    ActiveChart.ChartType = xlPie
    ActiveChart.SetSourceData Source:=Sheets("Data").Range("N38:O49"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
        "Visitors By Month '06"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Vistors By Month '06"
    End With
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlLeft
    ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False, _
        HasLeaderLines:=True
            ActiveChart.Legend.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Regular"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    Selection.Height = 187
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.ChartTitle.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Avenir 35 Light"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
End If

blnFilled = False


' Set this workbook saved to discourage saving
ThisWorkbook.Saved = True

End Sub

Then make sure you put the following in the "ThisWorkbook" object code area:
Option Explicit


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then Cancel = False Else Cancel = True
End Sub

This prevents the "Do you want to save changes?" dialog, since the ThisWorkbook.Saved = True flag is set. The ZipCodes page contains a list of zipcodes lifted from the US Census website. You'll have to use something like this:
CRAPZIPSTATE   ABBFULL NAME
AL3500435004ALABAMA   ALALABAMA
The CRAP is what was lifted. ZIP is "=--RIGHT(A2,5)", STATE is "=VLOOKUP(LEFT(A2,2), $G$2:$H$52,2,0)" and the ABB and FULL NAME is used just for the VLOOKUP. Cool...have fun.

Friday, October 24, 2003

Thursday, September 11, 2003

Web Site Maintenance

This was a thing that I eagerly enjoyed. I got to play around with updating our web site (this link goes to an archive), finding ways to make it easier to keep current, etc. PHP played a good part in that, and is even more so now. It occurred to me that we needed to keep news releases up to date, so I created a WebDav resource on the Apache server that allowed our marketing director to log in and deposit files into folders that were categorized by month/year. When the browser points to that directory, the index file uses the opendir method provided by PHP and populates the page with nicely formatted contents. I also added RSS feeds to that same area of the web. I figured if our news wasn't so dynamic, at least the RSS would add something to it. I did something similar to our current situation at RAM. Since our webserver and fileserver sit on the same Linux box, I just created a symlink to our news directory in the marketing persons personal share. Now all she has to do is format the file correctly, and toss it in. PHP reads in all files in the directory and formats the page nicy-nice. Now I just need read up on sending RSS out...

Collaboration

Letting one another know what you were doing at any given time was another issue for us at Wustum. There are a number of employees that need to leave the building for various reasons, and instead of putting up a In/Out board, I decided that web-based calendaring was in order. Our friends at M$ do make a nice add-on to Exchange 5.5 called Team Folders. Unfortunately, they don't seem to be supporting it any longer, so get it while you can. It allows all your Outlook users to view a common calendar, much like thier own personal one. It supports permissions, and is fully customizable. I set up another one to track our many classrooms that we use for education. It eliminated double-booking issues. When we opened the new Racine Art Museum, I had to address the same issues on two campuses and viewing abilities across the internet. That was easily solved using GNU licensed tools. One is called WebCalendar for employee tracking, and the other is Meeting Room Booking System. We just transferred all events from the Exchange Team Folders to these, and took them off line. Ain't life grand?

Saturday, August 30, 2003

No collection management software.

Another thing I wanted to add to the effeciency of the workplace there was a database to collect information about our permanent collection and our sales/rental gallery. All of the information for the permanent collection was stored in a FileMaker Pro spreadsheet, and sales/rental items were tracked by paper... Unfortunately for the museum, the person that handled the sales/rental area left them, and in doing so took all the information that was in her head about that area. It was really pretty scary, having all that artwork, and needing to somehow come up with a system to track it's arrival, rental or sale, or even return to the artist. What I did was use the NorthWind database example from M$ Access and hacked it up to satisfy our needs. I used quite a bit of error checking on the input of data, really putting some tight reigns on that. I didn't want the data entry to become tainted. Some of the fine points are these:
  1. All the artists that exist in the DB have related objects, and possibly an exhibition to that object. This allowed us to enter an artist ONCE, and any objects and exhibits they were in at any time.
  2. I fleshed out the invoicing process to include things not present in the NW example;
    • percentages off on sales for museum members, s&h expenses, etc.
    • Lots of reports were added to perform invoicing, contract generation and the like
    • Automated the generation of exhibit labels using Word and merge [THAT took some VBA in the backround :)]
  3. Added a help file
Alot of this also meant that the Curatorial staff could request features at any time. This could have saved us literally thousands, since most software of this type is quite expensive. Unfortunately, we have stopped using this software because management seems to think that it has worn out it's usefullness... I'm sure we will have to "get off the pot, or shit" sooner or later, because we just took one HUGE step backwards. Buy or write, THAT is the question.

Tuesday, August 26, 2003

Some modifications in the work flow...

I'd been working at the Wustum Museum for a couple of months when I started noticing what I would call an inefficient use of users time spent doing daily tasks. The biggest problem to tackle was the "Mailing List From Hell". They were using FileMaker Pro for years with their old Mac LCII's and since the systems weren't networked, there were multiple copies of many lists floating all over the place... Once the PC's were put in by my predecessor, the lists were "somewhat" consolodated, but there were those that just couldn't relinquish control. The real problem was to collect all of them, remove them from the users computers, mesh them together, and send them in to our mailing guru that found duplicates, updated addresses, etc. It was a real mess, and took some time to fix, but now we have a pretty clean list of 10,000 + names, and a fund raising program called Paradigm to manage it and other things. Now we all use the DB, and no more multiple copies of "Mailing Lists From Hell".

Monday, August 25, 2003

I start working at Wustum Musuem as IT Manager

So I start at Wustum Museum as Techology Operations Manager. What a mess. The woman that set up the system used a local company that installed a leased system for them. Bad business decision, and even worse setup. SBS from M$ (I'm not a lover of M$, but they have their place). No patches in place, no connection to the internet except for dial up to an ISP that has a collection of mail boxes that they connect to at specific periods. Lame. I convinced them to allow me to turn the mail accounts off, get DSL and set up Exchange 5.5 to do what is was supposed to do, serve mail. Shortly thereafter, I set up a RedHat box on an old Acer 233mhz box that served as our firewall and web server. Still works to this day, even after I set up a chroot'ed caching DNS server, SMB (just to transfer backups to the M$ box from the RAM site), Webmin, SSH, and even MRTG. WOW that's a big load for a little box. I love this stuff.

I've decided to journalize my work history.

I've decided that I should document what I've been doing in my position at the Racine Art Museum these past couple of years. I've taken on a number of projects, and while I should have done this progressively as I performed them, I'll try to do it now. My interest in computers started long ago, when I joined the Army. I wanted a job that involved computers, and they were happy to oblige me. Unfortunately, I knew nothing about computers, only that I wanted to play...so they assigned me as a 13E MOS. Field Artilliary Fire Direction Specialist. The only computer they had was FADAC, or "Freddy". It computed the trajectory of bullets for us when we were too lazy to do it on paper and using protractors and slide rules. And I hate math... Once I got out of the service and finally ended up at Rode's Camera some years later, I started going to school, puttering around with machines at home (Alpha server, PC's, Mac's, Linux, etc) and learning lots. I wrote my first production piece of software for them. They were logging every film processing order on paper, and writing pricing by hand on envelopes full of pictures. My program, written in VB, utilized a used thermal printer from UPS (thanks Ebay), and simple algorithm's to compute different types of orders, print labels, and keep an electronic log. They still use it today, and I've since made some improvements. In addition to selling to corporate clients, I set up a Yahoo Store for them, and also a vigorous Ebay presence that is still maintained in my absence. I worked with a man named Skip, and his wife worked at the museum as Deputy Director. She saw and heard what I was doing for Rode's and hired me. I was so geeked!

Saturday, August 23, 2003

Wow!

Woke up this A.M. to edit some of my wedding video and run some tests on output. I decided that I needed to open a browser to my WebMin interface on my firewall/webserver here at home to whip up a quick file repository that my siblings and friends could navigate to and view my latest "creations". What did my eyes behold...but a Blog option suddenly appeared in my trusty Google search bar! Now that is a nice thing to wake up to. Now I can journal my daily routines and put the masses to sleep...

Sunday, August 03, 2003

results