=========================================================================== === === === SAPPHIRE ACCOUNTING === === === =========================================================================== NOTE: Sapphire's accounting feature, when properly implemented, is a Zero- Maintenance feature. However, if you turn on accounting and do not also set up the event to export and purge the information file, you can fill up your disk. By default, accounting is turned OFF. ---------------------------- What is Sapphire Accounting? ---------------------------- Sapphire Accounting enables you to export usage records to another program so you can bill users for their online time and downloads. If you turn on Sapphire accounting (SYSOP:CONFIG:SECURITY:ACCOUNTING) each download and user session will generate an accounting record in a text file named SAPPHIRE.ACC. You can set up a daily event (using Sapphire's EVENT command) to extract the accounting data you need, then send the data to another program for processing. ------------------- Extracting the Data ------------------- A convenient way to preprocess the data is with Pinnacle Software's Parse-O-Matic program. This handy utility extracts and reformats data from an input file according to rules you specify. The file ACCOUNT.POM (included in the standard Sapphire package) is a Parse-O-Matic control file created for Sapphire Accounting. It will read the accounting file, strip out certain unwanted data, and create a "comma-delimited" file appropriate for importing into a spreadsheet. To try it out: 1) Obtain the Parse-O-Matic program (evaluation copy $6 by mail, or free from our web site at www.cam.org/~pinnacl and our BBS at 514-345-8654). Install it in your Sapphire directory (or preferably, in a new directory in your DOS PATH). 2) Turn on accounting via SYSOP:CONFIG:SECURITY:ACCOUNTING. 3) Start up Sapphire. 4) Log on and off a few times to create some accounting data. 5) Shut down Sapphire. 6) Run Parse-O-Matic by entering this command at the DOS prompt: POM ACCOUNT.POM SAPPHIRE.ACC TEST.TXT This will process the accounting data file SAPPHIRE.ACC according to the rules in the Parse-O-Matic control file ACCOUNT.POM and place the result in the file TEST.TXT. 7) Enter the command SEE TEST.TXT to view the resulting file. This is a comma-delimited file appropriate for importation into another program such as a spreadsheet (e.g. Lotus 1-2-3, Excel, Quattro Pro) or database (e.g. Paradox, DBase). 8) If you find that the information in TEST.TXT is not quite right for your application (wrong format, missing or unwanted data), you can modify the Parse-O-Matic control file (ACCOUNT.POM) using a text editor. Be sure to make a backup copy first. -------------------- Setting Up the Event -------------------- After you have created a Parse-O-Matic control file appropriate to your needs, you will set up the batch file to be called by the daily event. The batch file will look something like the following example (a copy of which is available in the file ACCOUNT.BAT): BATCH FILE LINE EXPLANATION 컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴 컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴� @ECHO OFF Turn off batch file echo POM ACCOUNT.POM SAPPHIRE.ACC ACCOUNT.TMP Run Parse-O-Matic IF ERRORLEVEL 1 GOTO ERR Quit if some kind of data error REM - - - - - - - - - - - - - - - - - - Comment line REM Run import procedure here Comment line REM - - - - - - - - - - - - - - - - - - Comment line GOTO SAVE Jump ahead to SAVE label :ERR Batch file label COPY ACC.ERR+SAPPHIRE.ACC ACC.ERR Preserve the data :SAVE Batch file label IF EXIST SAPPHIRE.AC$ DEL SAPPHIRE.AC$ Delete backup copy of data RENAME SAPPHIRE.ACC SAPPHIRE.AC$ Make the data file a backup copy The various steps of the event are described in more detail, below. The Parsing Procedure --------------------- The batch file runs Parse-O-Matic (the POM command) to extract data from the file SAPPHIRE.ACC, according to the rules specified in the Parse-O-Matic control file ACCOUNT.POM. The resulting data is placed in the file ACCOUNT.TMP. The Import Procedure -------------------- The import procedure (indicated by comments in the sample batch file) is the step that actually reads the extracted data (which resides in the file ACCOUNT.TMP) into your spreadsheet or database. You will have to write a spreadsheet or database script or macro to perform the import. Alternatively, you can use a DOS keyboard stacker utility (such as "STACKKEY"), or a keyboard macro processor (such as Superkey or Newkey) to play back the keystrokes required to perform the import. The Error Trapping Procedure ---------------------------- After Parse-O-Matic is run, the batch file checks the DOS ERRORLEVEL. In the unlikely event that it is set to 1 or higher, it means that something went wrong in the data extraction step, so the batch file jumps to the :ERR label -- skipping the import procedure. The error handling procedure executes this command: COPY ACC.ERR+SAPPHIRE.ACC ACC.ERR This command will place the raw accounting data at the end of the file ACC.ERR. Thus, even if you don't notice the error for several days, you won't lose any data -- it will be preserved in the file ACC.ERR. After the Event --------------- There is no need to add code to the batch file to start up Sapphire. The event-processing feature of Sapphire actually runs a copy of the batch file (rather than the batch file itself) and adds the necessary commands to restart the BBS. After the batch file is run, the file SAPPHIRE.ACC is gone -- it is renamed to SAPPHIRE.AC$ by the batch file -- but it will be recreated automatically by Sapphire. ----------------------- Accounting Record Types ----------------------- Several kinds of records appear in the accounting log: TYPE DESCRIPTION 컴컴 컴컴컴컴컴컴컴컴컴컴컴 A Arrival record D Download record S Session summary record These are described in more detail, below. The Arrival Record ------------------ A "Type A" record contains the letter "A" in column one. It looks like this: COL. ----+---10----+---20----+---30----+---40--- DATA A AAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAA DESCRIP REAL NAME USER NAME The fields are as follows: START FIELD COLUMN LENGTH DESCRIPTION 컴컴컴 컴컴컴 컴컴컴컴컴� 1 1 The letter "A" -- this identifies a "Type A" record 3 20 The user's real name (as entered during sign-up) 24 20 The user's sign-on name (a.k.a. "Handle" or "Alias") The Download Record ------------------- A "Type D" record contains the letter "D" in column one. It is written to the accounting file whenever the user successfully completes a download. It looks like this: COL. ----+---10----+---20----+---30- DATA D AAAAAAAA AAA A A A NNNNNNNNNN DESCRIP ROOTNAME EXT L A P FILE SIZE The fields identify a file that was downloaded, as follows: START FIELD COLUMN LENGTH DESCRIPTION 컴컴컴 컴컴컴 컴컴컴컴컴� 1 1 The letter "D" -- this identifies a "Type D" record 3 8 Root name of the file (e.g. XYZ.DAT's root is XYZ) 12 3 Extension of the file (e.g. XYZ.DAT's extension is DAT) 16 1 Minimum level required to download this file 18 1 File area in which the file was placed 20 1 Protocol used to download the file 22 10 File size in bytes (not kilobytes, as in other records) The Session Summary Record -------------------------- A "Type S" record contains the letter "S" in column one. Using the default date format, the record looks like this: COL. ----+---10----+---20----+---30----+---40----+---50----+---60----+ DATA S AAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAA A NNNNN DDDDD MMMM DESCRIP REAL NAME USER NAME E CALLS LAST CALL COL. ---70----+---80----+---90----+--100----+--110----+--120----+--130 DATA YY/MM/DD HH:MM:SS DDDDD MMMM W YY/MM/DD HH:MM:SS DDDDD MMMM W DESCRIP START OF CALL END OF CALL COL. ----+--140----+--150----+--160----+--170----+--180----+--190----+ DATA NNNNN NNNNN NNNNNNNNN NNNNNNNNN NNNNN NNNNN NNNNNNNNN NNNNNNNNN DESCRIP SESSN TOTAL SESSION TOTAL SESSN TOTAL SESSION TOTAL DNLDS DNLDS DOWNLOADK DOWNLOADK UPLDS UPLDS UPLOADK UPLOADK COL. --200----+--210----+--220----+--230 旼컴컴컴컴컴컴컴컴컴컴컴커 DATA NNNNN NNNNN **************** Blank spaces are DESCRIP SESSN TOTAL READ FLAGS reserved for expansion POSTS POSTS Using the default date format, the fields are as follows: START FIELD COLUMN LENGTH DESCRIPTION 컴컴컴 컴컴컴 컴컴컴컴컴� 1 1 The letter "S" -- this identifies a "Type S" record 3 20 The user's real name (as entered during sign-up) 24 20 The user's sign-on name (a.ka. "Handle" or "Alias") 45 1 User level (0 to 8) 47 5 Total number of visits by this user (*) 53 5 Day # of previous call from this user 59 4 Minute-of-day of previous call from this user 66 8 Start of call date (Year/Month/Date format) 75 8 Start of call time (Hour:Minute:Second format) 84 5 Start of call day # 90 4 Start of call minute-of-day 95 1 Start of call day-of-week (0=Sunday ... 6=Saturday) 99 8 End of call date (Year/Month/Date format) 108 8 End of call time (Hour:Minute:Second format) 117 5 End of call day # 123 4 End of call minute-of-day 128 1 End of call day-of-week (0=Sunday ... 6=Saturday) 131 5 Number of downloads (this call) 139 5 Number of downloads (all calls) 143 9 Kilobytes downloaded (this call) 153 9 Kilobytes downloaded (all calls) 165 5 Number of uploads (this call) 171 5 Number of uploads (all calls) 177 9 Kilobytes uploaded (this call) 187 9 Kilobytes uploaded (all calls) 201 5 Number of SENDs (this call) 207 5 Number of SENDs (all calls) 215 1 Read flag: Checked OBSERVE ("*" for Yes, "-" for No) 216 15 Read flags: 15 message bases ("*" if read, "-" if not) (*) For reasons relating to its internal data format, Sapphire stops incrementing this value when it reaches 65000 visits. The read flags for message bases are set (i.e. show "*") only if the user actually saw some new messages. The "*" will not be set if he entered a READ command for a particular base and saw nothing there, or if he used SET to back up to earlier messages. Except for "Start of Call" data, all data is obtained at the END of the call. So if you alter a user's level during the call (via function key), the modified level will be recorded. The record format shown above will change if you set a different date format. The default date format is YY/MM/DD (Year, Month, Day). However, you can set a different date format if you wish, using the CONFIG program: - Select SYSOP:CONFIG:SECURITY:ACCOUNTING; move your cursor to Date Format - Press F1 for an explanation of the format string