/******************************************************************
* Timesheet2CSV.c
* (c) Copyright Stuart Nicholson, 1998-1999.
* Utility for converting stored Palmpilot Timesheet database (*.PDB) file into
* a more useful CSV (comma separated values) format which can be loaded into
* most common office suites.
*
* WARNING: This code is kludgy and Intel x86 SPECIFIC as it makes some extremely
*          horrible assumptions about byte ordering and such. I would most grateful if
*          a Macintosh programmer out there could massage this code and release
*          a Mac friendly version. The problematic sections start with a comment
*          beginning 'WARNING:'.
*
* author : stuart nicholson
* email  : snic@ihug.co.nz
* version: 15-November-1998
******************************************************************/

/******************************************************************
* #includes
******************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>

/******************************************************************
* #defines
******************************************************************/
/* The application database type */
#define TSDBType    ('data')
/* The application database name */
#define TSDBName    ("TimesheetDB")
/* The version number of the database (more or less corresponds to the application version number). */
#define TSDBMajorVersion (1)
#define TSDBMinorVersion (5)
#define TSDBVersion ((TSDBMajorVersion << 8) | (TSDBMinorVersion))

/* The maximum number of client, projects or task entries allowed,
* including the the 2 automatic entries of 'none' and 'Edit...' */
#define TSMaxCatEntries_v140     (22)
#define TSMaxCatEntries_v150     (102)
/* The length of each client, project or task list entry. */
#define TSMaxCatEntryLen_v110    (15 + 1)
#define TSMaxCatEntryLen_v120	 (17 + 1)

/* The database record index of the first actual day record in the timesheet database.
* All records prior to this record hold application resource information. */
#define TSFirstDayRecIdx     (4)

/* WARNING: Intel specific! */
/* convert Motorola shorts and ints to Intel shorts and ints */
#define MAC2PC_SHORT(s) (((s & 0x00FF) << 8) | (s >> 8))
#define MAC2PC_INT(i) (((i & 0x000000FF) << 24) | ((i & 0x0000FF00) << 8) | ((i & 0x00FF0000) >> 8) | (i >> 24))

/******************************************************************
* structs
******************************************************************/

/******************************************************************
* PDB struct that holds information about the PDB file we're converting
******************************************************************/
typedef struct
  {
  char name[32 + 1];
  short attributes;
  short version;
  int creationDate;
  int modificationDate;
  int lastBackupDate;
  int modificationNumber;
  int appInfoID;
  int sortInfoID;
  char type[4];
  char creator[4];
  int uniqueIDSeed;
  }
PDBHeaderType;

/******************************************************************
* PDB struct that holds information about and individual PDB record
******************************************************************/
typedef struct
  {
  unsigned int localChunkID;
  char attributes;
  char uniqueID[3];
  }
PDBRecordType;

/* Structures from the Timesheet Palmpilot application */

/******************************************************************
* Struct used to hold (and store) application internal data over
* multiple app executions.
******************************************************************/
/* Timesheet Version 1.2.0. */
typedef struct
  {
  /* The number of entries in each category (clients/projects/tasks). */
  char numCatEntries[3];
  }
TSAppPrefType_v120;

/* Timesheet Version 1.4.0. */
typedef struct
  {
  /* The number of entries in each category (clients/projects/tasks). */
  char numCatEntries[3];
  char newCatEntryIdx[3];	/* saved default category indexes for new entries (for Auto Categories pref) */
  char prefFlags;		/* pref flags */
  char newCatHours;		/* saved default category hours/minutes for new entries (for Auto Duration pref) */
  char filler[2];		/* additional future pref space */
  }
TSAppPrefType_v140;

/******************************************************************
* Struct used to hold Client, Project and Task category lists.
******************************************************************/
typedef struct
  {
  /* The translation table for the entries */
  char transTable[TSMaxCatEntries_v140];
  /* The text of the actual entries, will always contain at least two automatic entries, 'none' and 'Edit...' */
  char catsStartHere;
  }
TSCatRecType_v140;

typedef struct
  {
  /* The translation table for the entries */
  char transTable[TSMaxCatEntries_v150];
  /* The text of the actual entries, will always contain at least two automatic entries, 'none' and 'Edit...' */
  char catsStartHere;
  }
TSCatRecType_v150;

/******************************************************************
* Struct used to hold (and store) day records.
******************************************************************/
typedef struct
  {
  /* Number of entries in this day */
  char numEntries;
  /* WARNING: Intel specific! (kinda) */
  /* Date this day record relates to, in the PalmPilot this is actually a DateType struct, but because
  * I'm not entirely sure how structs and byte order relate so I convert dates the hard way. */
  unsigned char date[3];
  }
TSDayRecType;

/******************************************************************
* Struct used to hold (and store) entry records.
******************************************************************/
/* Timesheet Version 1.2.0. */
typedef struct
  {
  /* Which client does this record relate to. */
  char clientIdx;
  /* Which project does this record relate to. */
  char projectIdx;
  /* Which task does this record relate to. */
  char taskIdx;
  /* How many hours does this record use. */
  char hours;
  /* Following the end of the structure is the first character of descriptive text for to this record. */
  }
TSEntryRecType_v120;

/* Timesheet Version 1.4.0. */
typedef struct
  {
  /* Which client does this record relate to. */
  char clientIdx;
  /* Which project does this record relate to. */
  char projectIdx;
  /* Which task does this record relate to. */
  char taskIdx;
  /* How many hours does this record use. */
  char hours;
  /* Entry number of this entry within the current day (i.e. first entry in day = 1). Used during binary searching
  * of Timesheet database. Must be updated each time a record is DELETED from day. */
  char entryNum;
  /* Filler for future possible expansion */
  char filler;
  /* Note there's no 'chargeable' variable. This because 'chargeable' is indicated by setting the highest bit in the
  * record category (part of attributes) on. */
  /* Following the end of the structure is the first character of descriptive text for to this record. */
  }
TSEntryRecType_v140;

/******************************************************************
* function prototypes
******************************************************************/
PDBHeaderType *loadDBHeader(FILE * dbFile);
short loadDBRecords(int dbMinorVersion, int dbMajorVersion, PDBHeaderType * header, FILE * dbFile, PDBRecordType ** records);
int dbHasFiller(FILE * dbFile, int numRecords, PDBRecordType * records);
void *loadDBRecord(FILE * dbFile, int recNum, PDBRecordType * records, int numRecords);
void timesheet2Csv(FILE * dbFile, FILE * csvFile);
char *lookupCatName(void *catPtr, int catIdx, int dbMajorVersion, int dbMinorVersion);
void fixupEntryComment(char *entryComment);
int main(int argc, char **argv);

/******************************************************************
* Loads the header information for a PalmPilot PDB file and returns
* a new PDBHeaderType structure containing the header info if it
* was successfully loaded. Returns NULL if the PDB file doesn't contain
* a valid header (which means it's probably not a PDB file).
******************************************************************/
PDBHeaderType *
loadDBHeader(FILE * dbFile)
  {
  PDBHeaderType *newHeaderPtr = NULL;
  
  /* dynamically allocate a new header */
  if ((newHeaderPtr = malloc(sizeof(PDBHeaderType))) == NULL)
    {
    /* Out of memory */
    perror("error reading database header");
    exit(EXIT_FAILURE);
    }
  /* attempt to retrieve DB header from file */
  if (fread(newHeaderPtr->name, sizeof(char), 32, dbFile) == 0)
    {
    return NULL;
    }
  if (fread(&(newHeaderPtr->attributes), sizeof(short), 1, dbFile) == 0)
    {
    return NULL;
    }
  /* WARNING: Intel specific! */
  newHeaderPtr->attributes = MAC2PC_SHORT(newHeaderPtr->attributes);
  if (fread(&(newHeaderPtr->version), sizeof(short), 1, dbFile) == 0)
    {
    return NULL;
    }
  /* WARNING: Intel specific! */
  newHeaderPtr->version = MAC2PC_SHORT(newHeaderPtr->version);
  if (fread(&(newHeaderPtr->creationDate), sizeof(int), 1, dbFile) == 0)
    {
    return NULL;
    }
  if (fread(&(newHeaderPtr->modificationDate), sizeof(int), 1, dbFile) == 0)
    {
    return NULL;
    }
  if (fread(&(newHeaderPtr->lastBackupDate), sizeof(int), 1, dbFile) == 0)
    {
    return NULL;
    }
  if (fread(&(newHeaderPtr->modificationNumber), sizeof(int), 1, dbFile) == 0)
    {
    return NULL;
    }
  if (fread(&(newHeaderPtr->appInfoID), sizeof(int), 1, dbFile) == 0)
    {
    return NULL;
    }
  if (fread(&(newHeaderPtr->sortInfoID), sizeof(int), 1, dbFile) == 0)
    {
    return NULL;
    }
  if (fread(&(newHeaderPtr->type), sizeof(char), 4, dbFile) == 0)
    {
    return NULL;
    }
  if (fread(&(newHeaderPtr->creator), sizeof(char), 4, dbFile) == 0)
    {
    return NULL;
    }
  if (fread(&(newHeaderPtr->uniqueIDSeed), sizeof(int), 1, dbFile) == 0)
    {
    return NULL;
    }
  /* read complete header */
  return newHeaderPtr;
  }

  /******************************************************************
  * Load the record information for every record in the database file.
  * Returns the number of records that exist in the database, or -1
  * if some error has occurred. May exit the utility if out of memory.
******************************************************************/
short
loadDBRecords(int dbMinorVersion, int dbMajorVersion, PDBHeaderType * header, FILE * dbFile, PDBRecordType ** records)
  {
  int i;
  int nextRecordListID = 0;
  int numRecords = 0;
  
  if (fread(&nextRecordListID, sizeof(int), 1, dbFile) == 0)
    {
    return -1;
    }
  if (fread(&numRecords, sizeof(short), 1, dbFile) == 0)
    {
    return -1;
    }
  numRecords = MAC2PC_SHORT(numRecords);
  /* dynamically allocate the record array */
  if (((*records) = malloc(sizeof(PDBRecordType) * numRecords)) == NULL)
    {
    /* Out of memory */
    perror("error reading records");
    exit(EXIT_FAILURE);
    }
  /* load all day and entry records */
  for (i = 0; i < numRecords; i++)
    {
    if (fread(&((*records)[i].localChunkID), sizeof(unsigned int), 1, dbFile) == 0)
      {
      return -1;
      }
    /* WARNING: Intel specific! */
    (*records)[i].localChunkID = MAC2PC_INT((*records)[i].localChunkID);
    if (fread(&((*records)[i].attributes), sizeof(char), 1, dbFile) == 0)
      {
      return -1;
      }
    if (fread(&((*records)[i].uniqueID), sizeof(char), 3, dbFile) == 0)
      {
      return -1;
      }
    }

  return numRecords;
  }

  
  /******************************************************************
  * Load the actual data for a specific record in the database. 
  * Returns a pointer to data loaded for the record, or NULL if the record wasn't loaded for some reason.
******************************************************************/
void *
loadDBRecord(FILE * dbFile, int recNum, PDBRecordType * records, int numRecords)
  {
  int recLength = 0;
  long fileOffset = 0;
  void *recPtr;
  
  /* sanity check */
  if (recNum < 0 || recNum >= numRecords)
    {
    return NULL;
    }
  /* calculate the length of the record */
  if (recNum == numRecords - 1)
    {
    /* at the last record in the database file, have to calculate final record length based on
    * what's left in the file to read, this is a little ugly and may not be portable. */
    fseek(dbFile, 0, SEEK_END);
    recLength = ftell(dbFile) - records[recNum].localChunkID;
    fseek(dbFile, fileOffset, SEEK_SET);
    }
  else
    {
    /* at intermediate record, calculate length based on offset of NEXT record */
    recLength = records[recNum + 1].localChunkID - records[recNum].localChunkID;
    }
  /* dynamically allocate memory to contain the record */
  if ((recPtr = malloc(recLength)) == NULL)
    {
    /* ack! out of memory? */
    perror("error reading record");
    exit(EXIT_FAILURE);
    }
  /* retrieve record contents */
  fseek(dbFile, (long)records[recNum].localChunkID, SEEK_SET);
  if (fread(recPtr, 1, recLength, dbFile) == 0)
    {
    return NULL;
    }
  return recPtr;
  }

  /******************************************************************
  * Read the Timesheet database from dbFile and convert it to CSV file in csvFile.
  * This function may exit the program if a serious problem occurs (truncated files,
  * out of memory etc).
  * NOTE: This is a rather large function that should be split into parts...my apologies.
******************************************************************/
void
timesheet2csv(FILE * dbFile, FILE * csvFile)
  {
  int i, j;
  int dbMinorVersion = 0;
  int dbMajorVersion = 0;
  short numRecords = 0;
  short year = 0;
  short month = 0;
  short day = 0;
  short chargeable = 0;
  short hours = 0;
  short minutes = 0;
  char ctimeStr[26 + 1] = "\x0";
  PDBHeaderType *headerPtr = NULL;
  PDBRecordType *records = NULL;
  TSAppPrefType_v120 *tsAppPrefs = NULL;
  void *tsClients = NULL;
  void *tsProjects = NULL;
  void *tsTasks = NULL;
  TSDayRecType *tsDay = NULL;
  TSEntryRecType_v120 *tsEntry_v120 = NULL;
  char *entryComment = NULL;
  time_t localTime;
  const char monthNames[12][3 + 1] =
    {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
  
  /* read the DB header */
  if ((headerPtr = loadDBHeader(dbFile)) == NULL)
    {
    fprintf(stderr, "error: input file does not appear to be a PalmPilot PDB database.\n");
    exit(EXIT_FAILURE);
    }
  /* print some database information for the user */
  fprintf(stderr, "Palmpilot PDB database file:\n"
    "\tname   : %s\n\ttype   : %s\n\tcreator: %s\n",
    headerPtr->name, headerPtr->type, headerPtr->creator);
  /* confirm the database is a Timesheet database this utility can convert */
  if (strcmp(headerPtr->name, TSDBName) != 0)
    {
    fprintf(stderr, "\nerror: this PalmPilot PDB file is not a Timesheet database.\n");
    exit(EXIT_FAILURE);
    }
  /* check the Timesheet database version against the utility version */
  dbMajorVersion = (headerPtr->version) >> 8;
  dbMinorVersion = (headerPtr->version) & 0x00FF;
  fprintf(stderr, "\tversion: %d.%d\n", dbMajorVersion, dbMinorVersion);
  if ((dbMajorVersion > TSDBMajorVersion) ||
    (dbMajorVersion == TSDBMajorVersion && dbMinorVersion > TSDBMinorVersion))
    {
    fprintf(stderr, "\nerror: incompatible Timesheet database version.\n"
      "This version of Timesheet2CSV translates databases version %d.%d and earlier.\n",
      TSDBMajorVersion, TSDBMinorVersion);
    exit(EXIT_FAILURE);
    }
  /* looks good, so load all the record information */
  numRecords = loadDBRecords(dbMinorVersion, dbMajorVersion, headerPtr, dbFile, &records);
  if (numRecords == -1)
    {
    perror("error reading records");
    exit(EXIT_FAILURE);
    }
  fprintf(stderr, "\t%d records in database.\n", numRecords);
  /* check if the database is empty or truncated */
  if (numRecords < TSFirstDayRecIdx)
    {
    fprintf(stderr, "error: Timesheet database appears to be truncated (not enough records).\n");
    exit(EXIT_FAILURE);
    }
  if (numRecords == TSFirstDayRecIdx)
    {
    fprintf(stderr, "error: Timesheet database contains no entry records to convert.\n");
    exit(EXIT_FAILURE);
    }
  /* put a header on the CSV file */
  time(&localTime);
  strcpy(ctimeStr, ctime(&localTime));
  ctimeStr[strlen(ctimeStr) - 1] = '\x0';
  fprintf(csvFile, "\"Date\",\"Year\",\"Month\",\"Day\",\"Client\",\"Project\",\"Task\",\""
    "Hours\",\"Minutes\",\"Duration\",\"Chargeable\",\"Comment\"\n");
  /* begin loading the actual Timesheet database records */
  /* first 4 records in the database are the Timesheet application preference records */
  tsAppPrefs = loadDBRecord(dbFile, 0, records, numRecords);
  if (tsAppPrefs == NULL)
    {
    fprintf(stderr, "error: unable to read record 0.\n");
    exit(EXIT_FAILURE);
    }
  tsClients = loadDBRecord(dbFile, 1, records, numRecords);
  if (tsClients == NULL)
    {
    fprintf(stderr, "error: unable to read record 1.\n");
    exit(EXIT_FAILURE);
    }
  tsProjects = loadDBRecord(dbFile, 2, records, numRecords);
  if (tsProjects == NULL)
    {
    fprintf(stderr, "error: unable to read record 2.\n");
    exit(EXIT_FAILURE);
    }
  tsTasks = loadDBRecord(dbFile, 3, records, numRecords);
  if (tsTasks == NULL)
    {
    fprintf(stderr, "error: unable to read record 3.\n");
    exit(EXIT_FAILURE);
    }
  /* then the actual Timesheet day/entry records begin */
  for (i = TSFirstDayRecIdx; i < numRecords; i++)
    {
    /* get the day record */
    tsDay = loadDBRecord(dbFile, i, records, numRecords);
    if (tsDay == NULL)
      {
      fprintf(stderr, "error: unable to read record %d.\n", i);
      exit(EXIT_FAILURE);
      }
    /* WARNING: INTEL SPECIFIC CODE */
    /* build the date the hard way because I can't figure out how compiler works it's structs... */
    year = ((tsDay->date[1] >> 1) & 0x7F) + 1920;
    month = ((tsDay->date[1] & 0x01) << 3) | ((tsDay->date[2] >> 5) & 0x07);
    day = tsDay->date[2] & 0x1F;
    /* process each entry in the day */
    for (j = 0; j < tsDay->numEntries; j++, i++)
      {
      /* retrieve the entry record in v120 format or earlier */
      tsEntry_v120 = loadDBRecord(dbFile, i + 1, records, numRecords);
      if (tsEntry_v120 == NULL)
	{
	fprintf(stderr, "error: unable to read record %d.\n", i + 1);
	exit(EXIT_FAILURE);
	}
      /* extract the chargeable flag from the high bit of the low attribute byte */
      chargeable = records[i + 1].attributes & 0x08;
      /* extract hours and minutes */
      hours = tsEntry_v120->hours & 0x0F;
      minutes = ((tsEntry_v120->hours >> 4) & 0x0F) * 5;
      /* produce another CSV row in the output file for this entry */
      /* This is an ugly bit of coding. Particularly the way I've treated the tsClient, tsProject and tsTask record pointers.
      * I've just assumed they're version 1.5 or higher. This assumption works for earlier versions because there should be no
      * client/project/task index greater than 22. Told you this utility was a hack ;) */
      fprintf(csvFile, "%d-%s-%d,%d,%d,%d,\"%s\",\"%s\",\"%s\",%d,%d,\"%d:%d\",\"%c\",",
	day, monthNames[month - 1], year,
	year, month, day,
	lookupCatName(tsClients, ((TSCatRecType_v150 *) tsClients)->transTable[tsEntry_v120->clientIdx], dbMajorVersion, dbMinorVersion),
	lookupCatName(tsProjects, ((TSCatRecType_v150 *) tsProjects)->transTable[tsEntry_v120->projectIdx], dbMajorVersion, dbMinorVersion),
	lookupCatName(tsTasks, ((TSCatRecType_v150 *) tsTasks)->transTable[tsEntry_v120->taskIdx], dbMajorVersion, dbMinorVersion),
	hours, minutes, hours, minutes,
	chargeable ? ('Y') : ('N'));
	/* now for a bit of dodgy coding. The rest of the entry record (after the structure ends)
      * contains the text of the entry description, so do some nasty pointer arithmetic */
      if (dbMajorVersion == 1 && dbMinorVersion <= 2)
	{
	  /* dealing with v120 or earlier entry record */
	  entryComment = (char *) tsEntry_v120 + sizeof(TSEntryRecType_v120);
	}
      else
	{
	  /* dealing with v140 or later entry record */
	  entryComment = (char *) tsEntry_v120 + sizeof(TSEntryRecType_v140);
	}
      /* fixup comment for csv by converting all embedded newlines into spaces */
      fixupEntryComment(entryComment);
      fprintf(csvFile, "\"%s\"\n", entryComment);
      /* free the entry record now we've finished with it */
      free(tsEntry_v120);
      tsEntry_v120 = NULL;
      }
    /* free the day record now we've finished with it */
    free(tsDay);
    tsDay = NULL;
    }
  /* put trailer on csv file */
  fprintf(csvFile, "\n\"End of database.\"\n");
  /* free all the memory we've allocated so far */
  free(headerPtr);
  headerPtr = NULL;
  free(records);
  records = NULL;
  free(tsAppPrefs);
  tsAppPrefs = NULL;
  free(tsClients);
  tsClients = NULL;
  free(tsProjects);
  tsClients = NULL;
  free(tsTasks);
  tsClients = NULL;
}

/******************************************************************
* Returns the category name for the specified category index. Required as the 
* database category format changed somewhat from database v1.0, v1.1 -> v1.2
* and again from v1.4 -> v1.5.
******************************************************************/
char *
lookupCatName(void *catPtr, int catIdx, int dbMajorVersion, int dbMinorVersion)
  {
  char *catNames;
  
  if (dbMajorVersion == 1 && dbMinorVersion < 5)
    {
    /* version 1.4 and earlier category format (22 category names max) */
    catNames = &(((TSCatRecType_v140 *) catPtr)->catsStartHere);
    }
  else
    {
    /* version 1.5 and later category format (102 category names max) */
    catNames = &(((TSCatRecType_v150 *) catPtr)->catsStartHere);
    }
  if (dbMajorVersion > 1 || dbMinorVersion > 1)
    {
    /* new v1.2 category format */
    return &(catNames[sizeof(char) * catIdx * TSMaxCatEntryLen_v120]);
    }
  else
    {
    /* old v1.0 category format */
    return &(catNames[sizeof(char) * catIdx * TSMaxCatEntryLen_v110]);
    }
  }

  /******************************************************************
  * Fixup comment for csv by converting all embedded newlines into spaces
  ******************************************************************/
void fixupEntryComment(char *entryComment)
  {
    while((*entryComment) != '\x0')
      {
	if ((*entryComment) == '\n')
	  {
	    (*entryComment) = ' ';
	  }
	entryComment ++;
      }
  }

  /******************************************************************
  * Timesheet2CSV main function, just checks the command line is okay,
  * opens the input and output files and starts the translation.
******************************************************************/
int
main(int argc, char **argv)
  {
  FILE *dbFile = NULL;
  FILE *csvFile = NULL;
  
  fprintf(stderr, "Timesheet2CSV utility, version %d.%d.\n"
    "Converts a saved PalmPilot Timesheet database into a CSV file.\n"
    "(c) Copyright 1998, Stuart Nicholson.\n\n", TSDBMajorVersion, TSDBMinorVersion);
  /* check the command line contains input, output file names */
  if (argc != 3)
    {
    fprintf(stderr, "error: bad command line.\nusage: Timesheet2CSV input_dbFile output_csvFile\n");
    exit(EXIT_FAILURE);
    }
  /* open the input file */
  if ((dbFile = fopen(argv[1], "rb")) == NULL)
    {
    perror("error opening input database file");
    exit(EXIT_FAILURE);
    }
  /* open the output file */
  if ((csvFile = fopen(argv[2], "wt")) == NULL)
    {
    perror("error opening output csv file");
    exit(EXIT_FAILURE);
    }
  /* translate the palmpilot database file into a csv file on stdout */
  timesheet2csv(dbFile, csvFile);
  /* close the input and output file */
  fclose(dbFile);
  dbFile = NULL;
  fclose(csvFile);
  csvFile = NULL;
  /* successfully converted Timesheet database */
  fprintf(stderr, "Finished.\n");
  return EXIT_SUCCESS;
  }

/* End of file */