#!/usr/bin/perl
# album2gallery.pl - PSE/PSA/PSPA/TP -> Gallery Exporter
#################################################################################
# LICENSE
#
# Copyright (C) 2003 David LaPorte
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or (at
# your option) any later version.
# 
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
#
#################################################################################
# AUTHORS
#
# David LaPorte		<dave@laportestyle.org>
#  - album2gallery.pl
#
#################################################################################
# DESCRIPTION
#
# This script allows you to export a Photoshop Album, Jasc Photo Album,
# ThumbsPlus gallery, or folder of images to a Gallery server.  The album
# thumbnail and description as well as photo captions are all preserved when
# available.
#
#################################################################################
# CHANGLOG
#
# 1.0   Initial Release
# 1.1   Minor bugfixes, exclude functionality
# 1.2   Total rewrite of gallery functions, incremental functionality
# 1.5   Jasc Photo Album support
# 1.6   Specifiable parent gallery
# 1.6.1 Fixed filename truncation issue
# 1.7   ThumbsPlus support, changed Jasc Photo Album marking
# 1.8   EXIF caption support
# 1.9   Ability to upload a folder of images (DIR)
# 2.0   Upload sort order
# 2.0.1 Cleaned up some SQL queries, Photoshop Elements 3 support
# 2.0.2 Change to make TP4 memo fields work - thanks to Jeff Olson
#       I guess that mean means TP4 is supported now!
# 2.0.3 Photoshop Elements 4 support - thanks for Chad O'Kray!
#
# REQUIREMENTS
#
# Adobe Photoshop Album, Photoshop Elements 3 or 4, JASC Photo Album 4, ThumbsPlus 6,
# or a folder of images
# ActivePerl (http://www.activestate.com/Products/ActivePerl/)
# Assorted perl modules including:
#  DBI
#  DBD-ODBC
#  TermReadKey
#  Win32-DriveInfo
#  Image-Info
#  DateManip
#
# If using Adobe Photoshop Album, Photoshop Elements 3 or 4, JASC Photo Album 4,
# or ThumbsPlus 6, an ODBC data source linked to the appropriate database
#
#################################################################################
# INSTRUCTIONS (Windows XP, although it will likely work on 2000/2003)
#
# Setup a ODBC data source
#  - Go to Control Panel->Administrative Tools->Data Sources (ODBC)
#  - Click "Add", select "Microsoft Access Driver (*.mdb)" 
#  - Set the Data Source Name to anything you'd like
#  - Click the "Select" button and browse to your PSE3, PSA, PSPA, or TP database
#    * PSE3 catalog usually lives at:
#      C:\Documents and Settings\All Users\Application Data\Adobe\Catalogs\My Catalog.psa
#    * PSA catalog usually lives at:
#      C:\Documents and Settings\All Users\Application Data\Adobe\Photoshop Album\Catalogs\My Catalog.psa
#    * PSPA database usually lives at:
#      C:\Program Files\Jasc Software Inc\Paint Shop Photo Album\system\ixdb.mdb
#    * TP database usually lives at:
#      C:\Program Files\Thumbs6\Thumbs.td4
#  - Click OK
#
# Install ActiveState Perl
#  - It's easy - you can figure it out
#
# Install necessary Perl modules using the Perl Package Manager that 
# came with ActiveState Perl.  You will need to install Class-DBI,
# DBD-ODBC, TermReadKey, Image-Info, DateManip, and Win32-DriveInfo.
# Use the "search" function to find each, and then "install" to install them.
#################################################################################
# USAGE
#
#  Usage: album2gallery.pl -p [PSE3|PSA|PSPA|TP|DIR] -d <DSN|DIR> -a <ALBUM> -u <URL> [OPTIONS]
#        -p <PROGRAM> PSE3, PSA, PSPA, TP, or DIR (default: PSE3)
#        -d <DSN>     DSN of catalog (default: PSA) or directory name (DIR)
#        -a <ALBUM>   Album to export
#        -g <GALLERY> Specify alternate gallery title
#        -t <PARENT>  Specify parent gallery
#        -u <URL>     Gallery URL
#        -m <MARK>    Export only images marked with <MARK> in notes field
#        -e <MARK>    Exclude files marked with <MARK> in notes field
#        -s <METHOD>  Sort method for upload list (NONE|NAME|CAPTURE)
#        -i           Incremental mode - upload only new files to an existing gallery
#        -f           Include filename in image caption
#        -c           Use embedded EXIF captions (descriptions)
#        -r           Recurse and upload all child albums (currently only support for DIR)
#        -v           Verbose
#        -h           Help
#
# The only required options are -d, -a, and -u.  Set the -d option to the ODBC
# data source name you created above or a directory if using "DIR".  The -a
# option specifies the album name *exactly* as it appears in the folder hierarchy
# within the program (if using "DIR", this should be the folder name).  This
# typically should be quoted:
#
# album2gallery.pl -p PSE3 -d DSN -a "Europe Trip, 2003" -u http://www.blah.com/gallery -m export -v
#
# The -u option specifies the Gallery server you will be uploading to.  The
# other parameters are optional.  The -g option allows you to set the name of
# the gallery to something other than the album name.  The -s option specifies
# the sort order of files to be uploaded: NONE (upload in order of returned by
# SQL query), FILENAME (order by filename), and CAPTURE (order by date image
# was taken/scanned).

#  The -m option allows you to "mark" images with a keyword in the Notes field
# (PSA|PSE3), by assigning a keyword (PSPA|TP), or by setting the Comments EXIF field
# (DIR) with a value so that only those images will be uploaded.  The -e option
# does the opposite - uploads all images but those marked.  These options are
# particularly useful if you keep all images within your album program and only
# want to upload the "best of" to Gallery or if you want to keep certain images
# out of public view.
#
#  The -v option prints out some useful output as the script runs - use it if
# you are having difficulties.  The -i option allows you to continually update
# an album and only upload the new images to a gallery.  This is a one-way synch
# - images you delete from your album will *NOT* be deleted from your gallery.
# The -t option allows you to specify where in the gallery hierarchy your new
# gallery should be created. The "parent" gallery is the one you'd like to
# create the new gallery in.  For instance, if you have the following hierarchy:
#
# Albums
#   2003
#     Trips
#       Florida
#
# 
# Specifying a "-t Trips" for an upload of the "New York" album would give you:
#
# Albums
#   2003
#     Trips
#       Florida
#       New York
#
# Image captions are pulled from the PSA|PSE3 caption field, the PSPA title field,
# the TP annotation field, or the Description EXIF field.  The "-f" option will
# append the filename in parentheses to the image caption, or use the filename
# as the caption if none already exists.
#
# You will be prompted for the Gallery username/password when you run the script.
#################################################################################

use strict;
use DBI;
use DBD::ODBC;
use LWP::UserAgent;
use HTTP::Cookies;
use HTTP::Request::Common;
use File::Basename;
use Getopt::Std;
use Term::ReadKey;
use Win32::DriveInfo;
use Image::Info qw(image_info dim);
use Date::Manip;

&Date_Init("TZ=EST");

my %args;
my $ua;
my $dsn;
my $db;
my $dbh;
my $sth;
my $url;
my $username;
my $password;
my $proto;
my $rproto;
my $remote;
my $gallery;
my $album;
my @gallery_images;
my @album_images;
my @upload_list;
my %thumbnail;
my $verbose;
my $marked;
my $excluded;
my $ignored;
my $incremental;
my $program;
my $displayfn;
my $parent;
my $exifcaption;
my $sort;

getopts("d:a:g:u:m:e:s:p:t:fcivh", \%args) or usage();
usage() if ($args{h});
usage() if (!$args{u});
usage() if (!$args{a});

$url         = $args{u};
$program     = $args{p} || "PSE3";
$db          = $args{d} || "PSE3";
$sort        = $args{s} || "NONE";
$album       = $args{a};
$verbose     = $args{v};
$gallery     = $args{g};
$marked      = $args{m};
$excluded    = $args{e};
$incremental = $args{i};
$displayfn   = $args{f};
$parent      = $args{t};
$exifcaption = $args{c};

$program = uc($program);
$sort = uc($sort);

usage() if ($program !~ /^(PSE3|PSA|PSPA|TP|DIR)$/);
usage() if ($sort !~ /^(NONE|FILENAME|CAPTURE)$/);

$dsn = "dbi:ODBC:$db";
$remote = "/gallery_remote2.php";
$proto = "2.0";
$gallery = $album if (!$gallery);

# make sure album exists
my $exists;
foreach my $alb (list_albums($program)) {
  $exists++ if ($alb eq $album);
}
die "Album $album does not exist!\n" if (!$exists);
die "Muliple albums of same name!\n" if ($exists > 1);

# set up the user agent
$ua = LWP::UserAgent->new;
$ua->cookie_jar(HTTP::Cookies->new(file => "cookie_jar", autosave => 1));

# check the url
if ($url !~ m|^http://|) {
    $url = "http://" . $url . $remote;
} else {
    $url = $url . $remote;
}

# log in
print "Username: ";
$username = ReadLine(0);
chomp $username;
ReadMode("noecho");
print "Password: ";
$password = ReadLine(0);
chomp $password;
print "\n";
gallery_login($username, $password, $url);

# Create gallery if not an incremental update
if (!$incremental) {
  create_gallery($gallery);
} else {
#  die "Remote server does not support incremental updates ($rproto < 2.4)!\n" if ($rproto < 2.4);
  @gallery_images = gallery_images($gallery);
}

# If album thumbnail exists, push to front of upload queue
if (%thumbnail = album_thumbnail($program, $album)) {
  push(@upload_list, {%thumbnail});
}

# Add images to queue
foreach my $image (album_images($program, $album)) {
  push(@upload_list, $image) if ($image->{'filename'} ne $thumbnail{'filename'});
}

if  ($sort eq "FILENAME") {
  @upload_list = sort by_name @upload_list;
} elsif ($sort eq "CAPTURE") {
  @upload_list = sort by_date @upload_list;
} elsif ($sort eq "NONE"){
}

foreach my $image (@upload_list) {
  if ($incremental) {
    $ignored = 0;
    foreach (@gallery_images) {
      if (basename($image->{'filename'}) =~ /$_/i) {
        $ignored = 1;
        last;
      }
    }
  }
  if (!$ignored) {
    my $caption;
    if (!$exifcaption) {
      $caption = $image->{'caption'};
    } else {
      my $info = image_info($image->{'filename'});
      if (my $error = $info->{error}) {
        die "Can't parse image info: $error\n";
      }
      $caption = $info->{ImageDescription};
    }
    if ($displayfn) {
      my $fn = $image->{'filename'};
      chop $fn if ($fn =~ /\s$/);
      $caption .= " (".basename($fn).")";
    }
    if ((!$marked && !$excluded) || ($image->{'filename'} eq $thumbnail{'filename'})) {
      add_image($gallery, $image->{'filename'}, $caption);
    } elsif ($excluded) {
      add_image($gallery, $image->{'filename'}, $caption) if ($image->{'notes'} !~ /$excluded/);;
    } elsif ($marked) {
      add_image($gallery, $image->{'filename'}, $caption) if ($image->{'notes'} =~ /$marked/);
    }
  }
}

sub by_date {
  my($date1, $date2);
  $date1 = &ParseDate($a->{'captured'});
  $date2 = &ParseDate($b->{'captured'});
  return (&Date_Cmp($date1,$date2));
}

sub by_name {
 $a->{'filename'} cmp $b->{'filename'};
}

sub usage {
  my $cmd = basename($0);
  print STDERR << "EOF";
  Usage: $cmd -p [PSE3|PSA|PSPA|TP|DIR] -d <DSN|DIR> -a <ALBUM> -u <URL> [OPTIONS]
        -p <PROGRAM> PSE3, PSA, PSPA, TP, or DIR (default: PSE3)
        -d <DSN>     DSN of catalog (PSA|PSPA|TP) or directory name (DIR)
        -a <ALBUM>   Album (PSE3|PSA|PSPA|TP) or folder (DIR)
        -g <GALLERY> Specify alternate gallery title
        -t <PARENT>  Specify parent gallery
        -u <URL>     Gallery URL
        -m <MARK>    Export only images marked with <MARK> in notes field
        -e <MARK>    Exclude images marked with <MARK> in the notes field
        -s <ORDER>   Sort method for upload list: (NONE|FILENAME|CAPTURE)
        -c           Use embedded EXIF captions (descriptions)
        -r           Recurse and upload all child albums (currently only support for DIR)
        -i           Incremental - upload new files to existing gallery
        -f           Include filename in image caption
        -v           Verbose
        -h           Help
EOF
  exit;
}

sub send_command {
  my ($cmd, %data) = @_;
  my (%Content, $response, %return, $code, $text);
  $Content{protocol_version} = $proto;
  $Content{cmd} = $cmd;
  %Content = (%Content, %data);
  $response = $ua->request(POST $url, Content_Type => "form-data", Content => [ %Content ]);
  if ($response->is_error) {
    my $error = $response->error_as_HTML;
    $error =~ s/<.+?>//g;
    $error =~ s/\n//g;
    $error =~ s/An Error Occurred//g;
    die "Login Failed: $error\n";
  } else {
    foreach my $line (split("\n", $response->content)) {
      if ($line =~ /=/) {
        my ($param, $value) = split(/=/, $line);
        $return{$param} = $value;
      }
    }
  }	
  return(%return);
}

sub gallery_login {
  my ($username, $password, $url) = @_;
  my (%data, %return);
  print "Logging in to $url as $username...\n" if ($verbose);
  $data{uname} = $username;
  $data{password} = $password;
  %return = send_command("login", %data);
  if ($return{status_text} =~ /Login successful/) {
    if ($verbose) {
      print "Login successful\n";
    }
  } else {
    die "Login Failed: $return{status_text}\n";
  }
  $rproto = $return{server_version};
}

sub list_galleries {
  my (@galleries);
  my (%return);
  %return = send_command("fetch-albums", "");
  foreach my $key (keys(%return)) {
    if ($key =~ /^album\.title/) {
      push @galleries, $return{$key};
    }
  }
  return @galleries;
}

sub gallery_images {
  my ($gallery) = @_;
  $gallery =~ s/\W//g;
  my (%data, %return, @images);
  $data{set_albumName} = $gallery;
  %return = send_command("fetch-album-images", %data);
  die "Gallery $gallery does not appear to exist!\n" if (!$return{image_count});
  foreach my $key (keys(%return)) {
    if ($key =~ /^image\.name/) {
      push @images, $return{$key};
    }
  }
  return @images;
}

sub list_albums {
  my ($program) = @_;
  my ($dbh, $sth, $count, $folder, @folders);
  if ($program eq "PSE3" || $program eq "PSA") { 
    $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 1});
    $sth = $dbh->prepare("select count(*) from FolderTable");
    $sth->execute();
    ($count) = $sth->fetchrow_array();
    $sth = $dbh->prepare("select fFolderName from FolderTable");
    $sth->execute();
  } elsif ($program eq "PSPA") {
    $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 1});
    $sth = $dbh->prepare("select count(*) from Albums");
    $sth->execute();
    ($count) = $sth->fetchrow_array();
    $sth = $dbh->prepare("select Name from Albums");
    $sth->execute();
  } elsif ($program eq "TP") {
    $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 1});
    $sth = $dbh->prepare("select count(*) from Gallery");
    $sth->execute();
    ($count) = $sth->fetchrow_array();
    $sth = $dbh->prepare("select Name from Gallery");
    $sth->execute();
  } elsif ($program eq "DIR") {
    return $album;
  }
  for (my $i = 0; $i < $count; $i++) {
    $folder = $sth->fetchrow_array();
    push @folders, $folder if ($folder);
  }
  $sth->finish();
  return @folders;
}

sub album_thumbnail {
  my ($program,$album) = @_;
  my ($dbh, $sth, %image, $count); 
  if ($program eq "PSE3" || $program eq "PSA") { 
    $dbh = DBI->connect($dsn, "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 1});
    $sth = $dbh->prepare("select fFolderThumbImageId from FolderTable where fFolderName=".$dbh->quote($album));
    $sth->execute();
    my ($thumb_id) = $sth->fetchrow_array();
    if ($program eq "PSE3") {
      $sth = $dbh->prepare("SELECT ImageTable.fMediaFullPath, ImageTable.fMediaFullPath, MediaShortCaptionTable.fMediaShortCaption FROM ImageTable LEFT JOIN MediaShortCaptionTable ON ImageTable.fMediaShortCaptionIdFromMedia = MediaShortCaptionTable.fMediaShortCaptionId where fImageId=$thumb_id");
    } elsif ($program eq "PSA") {
      $sth = $dbh->prepare("SELECT fMediaFullPath, fMediaEditedFullPath, fImageCaption FROM ImageTable where fImageId=$thumb_id");        
    }
    $sth->execute();
    my @data = $sth->fetchrow_array();
    $image{'filename'} = lc($data[0]);
    if ($data[1] !~ /\w/) {
      $image{'filename'} = lc($data[0]);
    } else {
      $image{'filename'} = lc($data[1]);
    }
    $image{'caption'} = $data[2];
  } elsif ($program eq "PSPA") {
    return;
  } elsif ($program eq "TP") {
    return;
  } elsif ($program eq "DIR") {
    return;
  }
  $sth->finish();
  return %image;
}

sub resolve_folder {
  my ($album) = @_;
  my ($dbh, $sth, $count, $folder, @folders);
  $dbh = DBI->connect($dsn, "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 1});
  if ($program eq "PSA" || $program eq "PSE3") {
    $sth = $dbh->prepare("select fFolderId from FolderTable where fFolderName=".$dbh->quote($album));
    $sth->execute();
  } elsif ($program eq "PSPA") {
    $sth = $dbh->prepare("select AlbumID from Albums where Name=".$dbh->quote($album));
    $sth->execute();
  } elsif ($program eq "TP") {
    $sth = $dbh->prepare("select idGallery from Gallery where Name=".$dbh->quote($album));
    $sth->execute();
  }
  my ($album_id) = $sth->fetchrow_array();
  $sth->finish();
  return $album_id;
}

sub album_images {
  my ($program, $album) = @_;
  my ($dbh, $sth, $count, $filename, $note, $caption, $capture, $import, $original, $album_id, @seen, @data, @images);
  my ($ii) = 0;
  if ($program eq "PSE3" || $program eq "PSA") {
    $dbh = DBI->connect($dsn, "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 1});
    my ($album_id) = resolve_folder($album);
    # find images tagged with folder id
    if ($program eq "PSE3") {
      $sth = $dbh->prepare("SELECT ImageTable.fImageId, ImageTable.fMediaFullPath, ImageTable.fMediaFullPath, MediaShortCaptionTable.fMediaShortCaption, ImageTable.fFolderInfoArray, ImageLongCaptionTable.fImageLongCaption, ImageTable.fImageTime, ImageTable.fImageOriginalFileName FROM (ImageTable LEFT JOIN MediaShortCaptionTable ON ImageTable.fMediaShortCaptionIdFromMedia = MediaShortCaptionTable.fMediaShortCaptionId) LEFT JOIN ImageLongCaptionTable ON ImageTable.fImageLongCaptionIdFromImage = ImageLongCaptionTable.fImageLongCaptionId;");
    } elsif ($program eq "PSA") {
      $sth = $dbh->prepare("SELECT ImageTable.fImageId, ImageTable.fMediaFullPath, ImageTable.fMediaEditedFullPath, ImageTable.fImageCaption, ImageTable.fFolderInfoArray, ImageLongCaptionTable.fImageLongCaption, ImageTable.fImageTime, ImageTable.fImageOriginalFileName from ImageTable LEFT JOIN ImageLongCaptionTable ON ImageTable.fImageLongCaptionIdFromImage = ImageLongCaptionTable.fImageLongCaptionId");
    }
    $sth->execute();
    while (@data = $sth->fetchrow_array()) {
      $note = $data[5];
      $caption = $data[3];
      $capture = $data[6];
      $import = $data[6];
      $original = lc($data[7]);
      if ($data[2] !~ /\w/) {
        $filename = lc($data[1]);
      } else {
        $filename = lc($data[2]);
      }
      
      # thanks to http://www.adobeforums.com/cgi-bin/webx?128@217.eamBbqi2jdY.8@.1de82262
      for (my $i = 0; $i < 50; $i++) {
        if (unpack("N", pack("V", vec($data[4], $i, 32))) == $album_id && !grep(/^$original$/, @seen)) {
          $images[$ii]{'notes'}    = $note;
          $images[$ii]{'filename'} = $filename;
          $images[$ii]{'caption'}  = $caption;
          $images[$ii]{'captured'} = $capture;
          push @seen, $original;
          $ii++;
        }
      }
    }
    $sth->finish();
  } elsif ($program eq "PSPA") {
    $dbh = DBI->connect($dsn, "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 1});
    ($album_id) = resolve_folder($album);
    $sth = $dbh->prepare("select Folder from Albums where Name=".$dbh->quote($album));
    $sth->execute();
    my ($prefix) = $sth->fetchrow_array();
    $sth = $dbh->prepare("select Filename, Title, ImageID, DateTaken from Images where AlbumID=$album_id");
    $sth->execute();
    while (@data = $sth->fetchrow_array()) {
      $images[$ii]{'filename'} = lc("$prefix\\$data[0]");
      $images[$ii]{'caption'} = $data[1];
      $images[$ii]{'captured'} = $data[3];
      my $tmp_dbh = DBI->connect($dsn, "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 1});
      my $tmp_sth = $tmp_dbh->prepare("SELECT KeyWords.KeyWord FROM KeyWords, ImageKeyWords WHERE KeyWords.KeyWordID=ImageKeyWords.KeyWordID and ImageKeyWords.ImageID=$data[2]");
      $tmp_sth->execute();
      while (my ($keyword) = $tmp_sth->fetchrow_array()) {
        $images[$ii]{'notes'} .= " $keyword";
      }
      $ii++;
    }
    $sth->finish();
  } elsif ($program eq "TP") {
    $dbh = DBI->connect($dsn, "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 1});
    # For memo field, set max length to grab - thanks to Jeff Olson
     $dbh->{LongReadLen} = 65535;
    ($album_id) = resolve_folder($album);    $sth = $dbh->prepare("SELECT Path.name, Thumbnail.name, Thumbnail.annotation, Thumbnail.idThumb, Volume.label, Thumbnail.file_time, Thumbnail.thumbnail_time FROM Thumbnail, Path, GalleryThumb, Volume WHERE Thumbnail.idThumb=GalleryThumb.idThumb and Path.idPath=Thumbnail.idPath and Path.idVol=Volume.idVol and GalleryThumb.idGallery=$album_id");
    $sth->execute();
    while (@data = $sth->fetchrow_array()) {
      foreach my $drive (Win32::DriveInfo::DrivesInUse()) {
        if ((Win32::DriveInfo::VolumeInfo($drive))[0] eq $data[4]) {
          $images[$ii]{'filename'} = lc("$drive:\\$data[0]\\$data[1]");
          last;
        }
      }
      $images[$ii]{'caption'} = $data[2];
      $images[$ii]{'captured'} = "epoch $data[5]";
      $images[$ii]{'imported'} = "epoch $data[6]";
      my $tmp_dbh = DBI->connect($dsn, "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 1});
      my $tmp_sth = $tmp_dbh->prepare("SELECT Keyword.keyword FROM Keyword, ThumbnailKeyword WHERE Keyword.idKeyword=ThumbnailKeyword.idKeyword and ThumbnailKeyword.idThumb=$data[3]");
      $tmp_sth->execute();
      while (my ($keyword) = $tmp_sth->fetchrow_array()) {
        $images[$ii]{'notes'} .= " $keyword";
      }
      $ii++;
    }
    $sth->finish();
  } elsif ($program eq "DIR") {
    opendir(DIR, "$db\\$album") || die "Unable to open $db\\$album: $!\n";
    while (defined($filename = readdir(DIR))) {

      next if ($filename !~ /\.(jpg|gif|png)$/i);
      my $info = image_info("$db\\$album\\$filename");
      if (my $error = $info->{error}) {
        die "Can't parse image info: $error\n";
      }
      $images[$ii]{'filename'} = lc("$db\\$album\\$filename");
      $images[$ii]{'caption'}  = $info->{ImageDescription};
      $images[$ii]{'notes'}    = $info->{Comment};

      $import = (stat("$db\\$album\\$filename"))[9];
      $images[$ii]{'imported'} = "epoch $import";

      $capture = $info->{DateTimeOriginal};
      my($date, $time) = split(/\s+/, $capture);
      $date =~ s/:/\//g;
      $capture = $date." ".$time;
      $images[$ii]{'captured'} = $capture;

      $ii++;
    }
    closedir(DIR);
  }
  return @images;
}

sub create_gallery {
  my ($gallery) = @_;
  my ($name) = $gallery;
  $name =~ s/\W//g;
  my ($dbh, $sth, $note, %data, %return);
  foreach (list_galleries()) {
    die "Gallery $gallery already exists!\n" if ($_ =~ /^$gallery$/i);
  }
  if ($program eq "PSE3" || $program eq "PSA") {
    $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 1});
    $sth = $dbh->prepare("select fFolderNote from FolderTable where fFolderName=".$dbh->quote($album));
    $sth->execute();
  ($note) = $sth->fetchrow_array();
  } elsif ($program eq "PSPA") {
    $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 1});
    $sth = $dbh->prepare("select Title from Albums where Name=".$dbh->quote($album));
    $sth->execute();
  ($note) = $sth->fetchrow_array();
  } elsif ($program eq "TP") {
    $note = $name;
  } elsif ($program eq "DIR") {
    $note = $name;
  }
  print "Creating gallery $gallery\n" if ($verbose);
  $data{set_albumName} = 0;
  $data{newAlbumName} = $name;
  $data{newAlbumTitle} = $gallery;
  $data{newAlbumDesc} = $note;
  $data{set_albumName} = $parent if ($parent);
  %return = send_command("new-album", %data);
}

sub add_image {
  my ($gallery, $image, $caption) = @_;
  $gallery =~ s/\W//g;
  my (%data, %return);
  print "Uploading image ".basename($image)."\n" if ($verbose);
  $data{set_albumName} = $gallery;
  $data{caption} = $caption;
  $data{userfile} = ["$image"];
  %return = send_command("add-item", %data);
}