CitrusDB 2.1 Help File

Open Source Billing and Customer Service Software

Table of Contents

1 Introduction

CitrusDB is an open source customer service and billing database. It can be used by customer service personnel to provide sales and support to customers, and by billing staff to bill customers for their services via invoices and credit card batches. Customers may access the Online customer account manager to view their services, billing history, and make service and support requests online.

CitrusDB has similar requirements to other open source PHP projects. Specifically it requires PHP 5.0.4 or greater, MySQL 4.x or greater database, and a modern web browser such as Firefox, Chrome, Safari, or Internet Explorer with support for cookies, and Javascript turned on.

2 Chapter 1: Installation

Before you install CitrusDB you should check whether the Apache web server, PHP module, and MySQL database are installed on your server. To check whether Apache, MySQL, and PHP are installed in your server correctly you can upload a simple PHP file to your server that contains the following code:


View your uploaded file in a web browser by going to your site, eg: This will show you what version of PHP you are running and a lot of information about your PHP installation. Further down the screen there will be a section about your MySQL installation that will confirm whether MySQL is supported by your PHP installation. If the file you uploaded does not load, give an error, or does not show anything about MySQL, your server is not configured correctly. You will need to fix this before you continue.

There are many methods to install Apache, MySQL and PHP on your server. On Linux you will usually be able to find a package available for your distro that you can install via a package manager like synaptic, apt-get, or yum. Mac OS X already has Apache installed by default, all you'll need to do is enable PHP and install MySQL or you can use a pre-built package like XAMPP. If you are using Windows you can find there pre-built packages like XAMPP that include all the pieces you need for Apache, MySQL, and PHP.

After you have installed your server software you can begin installing CitrusDB. The main internal application is the largest part of CitrusDB. It is built for use by customer service representatives to handle customer service queries and sales, billing staff to send bills and process payments, and by administrators to activate new services, deactivate services, and monitor customer service operations.

If you are simply trying out CitrusDB it can be installed on any server you like. When using CitrusDB with real production data it should be installed on a private LAN or on a well protected web server with limited access by using firewalls and SSL, or other comparable security measures to protect your customer data and limit access to only you and your staff. Follow the steps below to begin your installation.

2.1 Installing the Main Internal Application

  • Un-tar and un-gzip the citrus file wherever you want the CitrusDB program to be located on your web server.
  • Create a MySQL database called "citrus" using the citrus.sql file. You can do this using a utility like phpMyAdmin or the command line like the one shown here:

          mysqladmin create citrus
          mysql citrus < /path/to/citrusdb/citrus.sql 

  • Create a MySQL database user with access to the citrus database you just created.
  • Edit the configuration file: /citrusdb/include/
  • Change the database host, to your server name, often this will be the localhost it is all on one server. Enter your database username and password that you just created before.


  • Change the path to citrus variable to the filesystem path of your installation:

    On a unix installation it may be something like these depending on where you installed your citrus files:


    On a windows installation it may be something like:


  • Change the hidden hash var to your own secret hashphrase. You will never need to remember this hashphrase so you can make it as complicated as you want.


  • Edit the $lang variable to point to your local language file. This localization file holds the values for all the text in CitrusDB as well as the currency symbol used. Please note that CitrusDB can only work with one currency.

    $lang = './include/local/';

  • Set the gpg command that will be used for encryption of stored credit card data. In order to use gpg you must generate a public and private keypair. It is probably a good idea to generate a gpg keypair just for citrusdb to use. This public key can then be used on the order side to store credit card numbers before they are imported as well as storing cards entered directly into citrusdb.

    To generate a keypair run the command: gpg –gen-key

    I recommend using the shortest recommended key length of 1024 bits since this key will be used over and over again, using the shorter key length will make decryption faster. This is a trade off of speed vs. strength. One could also use gpg with the symmetric encryption option or any other encryption system that can output an OpenPGP ascii armor message format. Symmetric encryption could be significantly faster, however it will require protection of the passphrase that is used when encrypting the card data.

    The example shown below stores the gpg key as the www-data user in their home directory. This home directory was created for the user that the web server is running as. This is the easiest way to make sure that the web server running citrusdb has access to the keys that it will use to encrypt and decrypt the card data. If the web server you run citrusdb is inside your network and dedicated to your internal use this should not be an issue. If you are unable to do this, then you will need to modify the permissions of your .gnupg folder and the keyrings inside of it to be readable by everyone. This is less desireable as it may expose them to other users of that system as well as cause permission problems in the future if you edit keys.

          $gpg_command = "/usr/bin/gpg --homedir /home/www-data/.gnupg
          --armor --batch -e -r 'CitrusDB'";

  • Set the gpg decrypt command that is used when decrypting the cards for batch exports or when re-keying the data. The decrypt command must include the –passphrase-fd 0 command because the passphrase will be passed to gpg by citrus's decryption function.

          $gpg_decrypt = "/usr/bin/gpg --homedir /home/www-data/.gnupg
          --passphrase-fd 0 --yes --no-tty --skip-verify --decrypt";

  • Set the gpg sign command will be used by citrus to verify the passphrase entered before creating a batch file.

        $gpg_sign = "/usr/bin/gpg --homedir /home/www-data/.gnupg
        --passphrase-fd 0 --yes --no-tty --clearsign -u 'CitrusDB'";

  • Set the regular url and ssl url prefixes for your database url. The ssl url is required for entering billing data or when exporting credit card data. You may use a self-signed certificate for this function as long as all the users of the database have that SSL certificate installed into their browsers. If you have other compensating controls or do not want to use ssl, you can enter the same http url in both variables.

    $url_prefix = "";
    $ssl_url_prefix = "";

  • add a cron job to run the statusupdate script (available in the citrusdb folder) every night or after billing is done for the day. This script will update the customer's account status and create a file that can be used to activate and deactivate customer services automatically.
  • Go to your web site address /citrusdb/index.php, you can log in with username "admin", password "test" to start.

You have now finished the main installation of CitrusDB. It's a good idea to login and change your admin password first. If you are not going to install the customer account manager can skip to Chapter 2 - Setup, to continue to setup of the internal CitrusDB application.

2.2 Installing the Public System for Customer Self-Service

CitrusDB also includes an online customer component for customers to view information about their own account, request changes, and make updates. The online customer account manager is stored in the /online folder of the CitrusDB download. It is a simple interface for customers to login using their customer number and password of their choice.

The online folder is meant to be installed on a separate web server. If you have a firewall, you'll want to configure it to allow access to the database server from this web server, so that the online component can query the customer information. It is also recommended to make a new login name and password for use by the online customer component that has more restrictions than your normal database user from the main system. This new database login name should restrict the access to your citrus database to SELECT only, allowing INSERT for only the customer_history table and SELECT, INSERT, UPDATE, and DELETE for the session2 table.

The online customer system is configured similarly to the main system by editing the file inside the citrusdb/online/include folder. There are some additional variables to configure for the online customer system.

The $payment_url variable should be set to whatever online form or payment system you have such as, paypal, your own custom form etc.

The $notify_user variable holds a username from the internal system that will be notified of new requests that customers make online.

2.3 Upgrading CitrusDB

When a there is a new installation or critical security update, you will want to be able to upgrade your installation to the latest release. To update from an older version to a newer one:

  • Backup the php files by copying them to another location and backup your citrus database by using a utility like mysqldump.

          mysqldump –user <username> –password <passwd> db_name > backup.sql

  • After you have backed up your current installation, replace the old citrusdb files with the new ones from the package you downloaded
  • Edit the in /citrusdb/include with the database and path information from your old config file.
  • To update your database schema from the old to the new, open the update.php file in your web browser ( and click the Update button.
  • Your database has now been updated, and you are ready to use your new version of CitrusDB.
  • If you are upgrading from version 1.x to 2.x you will need to make sure you have setup the new gpg commands in the config file and then run the encryptcards script to encrypt the cards in the database.

3 Chapter 2: Setup

3.1 Login

After you have installed CitrusDB the first thing you should do is login with the default username (admin) and password (test) and then go update the password to something new. To update the password, click on the Tools icon, and then click on Change Password. Enter a new password for the default admin user.

The login system will stop your login attempts from your IP address if you fail to login with the correct username and password after 5 tries in the last 24 hours. If this happens, your IP address must be removed from the login_failures table by the database administrator.

3.2 Organization Configuration

The Organization Configuration section holds information about the company or organization using CitrusDB. This should be the first thing you configure when setting up CitrusDB. The information is used in many places for invoices, billing, and customer messages.

The Billed By field holds the organization's brand name. If you have more than one organization brand name you can add multiple organization brands using the Add link to create more than one organization listing. This is used to create bills under different brand names and different credit card batches for those brands. If your organization does not need multiple brands you can simply edit the default configuration.

Enter the organization's name that will appear on the bill in the Billed By field. Then the street, city, state, zip, phone, and email information. The billing phone and billing email will appear on the printed and emailed invoices.

Credit Card Export Variable Order is the order of the variables that are exported in the credit card batch file for processing by your credit card processing system. You can export a number of different variables. All the available variables are listed at the bottom of the organization screen. Please note that you need to put the $ in front of the variable name so that they are evaluated correctly. CitrusDB will insert the words "CREDIT" or "CHARGE" in quotes at the beginning of the variables, depending on what kind of credit card transaction this is. The recommended export format uses comma and quote delimited items. You must keep the commas between the items and quotes around them to make sure other systems can import the information correctly.


Export File Prefix is is appended to the beginning of all credit card batch files exported by citrus. If you have multiple organizations exporting from the same citrus system, you can use this file prefix name to identify which exported batches belong to which organization.

Past Due Days are the number of days an account is past due before the status is updated by the statusupdate script to one of the three different past due status types: Past Due, Turned Off, or Canceled. Past Due accounts will be noted as past due and will be notified in email by the statusupdate script about their status. A Turned Off status will cause the account to send a disable message to the account activation system, which can then disable access to the account. An account in a Turned Off status should be disabled and non-working. The Canceled status is for accounts that have been through the first two phases of being past due and have not yet paid for services. Canceled will send a delete message to the account activation system. The account should be removed from your systems, and if you have a collections department or processing system, can be forwarded to them for past due amount recovery.

Carrier Dependent Past Due Days are for third party services that have special requirements when they are turned off. They act the same as normal Past Due Days however they have a Shutoff Notice period in between being Past Due and having their service being Turned Off. This shutoff notice is to notify the customer that their service will be turned off and also to notify the billing group that the service is in jeopardy of being shutoff. The billing group will receive notifications when this service is to be turned off or canceled, since they will need to be able to contact the third party service carrier to handle type of cancel request.

The next fields are the four different kinds of invoice notes that are printed at the bottom of the invoices according to the account status. The Default Invoice Note is printed on an account in good standing, usually this is a note thanking them for using your services. The Past Due Invoice Note is printed on invoices while an account is in the Past Due status. The Turned Off Invoice Note is printed on invoices while an account is in the Turned Off status. The Collections Invoice Note is printed on invoices for accounts in the collections status. These messages can be overridden by entering a custom message in the Notes field on the customer's billing record.

The declined subject and message field hold the data used in the email that is sent to customers when their credit card has been declined. Here you should enter a message that will prompt them to contact you and get their billing information up to date.

The last field is an invoice footer field that holds text that will be placed at the bottom of every invoice.

After you have setup the organization information you are ready to save your changes and continue setting up your CitrusDB system.

3.3 Settings

The settings tool holds system wide settings for citrus.

At the top of the Settings screen you will see a Citrus Database Version and Citrus Software Version. The database version is the version of the SQL Schema that is being used. The software version indicates the version of the software application files. These two versions should be the same. If they are different it may mean that you have not run the update script to update your database to the correct version or that you are running a Pre-release version of CitrusDB.

The Path to Credit Card File should be set to a file path on the server to a folder that is outside the path of the web server and other network processes. When credit card and account activation data is created it will be saved to a new file inside this folder. You can then write scripts that will automatically process new files left in that folder, or download those files to process them manually. You should make a new folder to store this data in, for example, on a Unix system you can set this to something like


and on a Windows system it can be set to a path such as


This will keep this data outside of the web server path, but will still allow it to be accessed by 3rd party scripts running on the server that can process this data automatically.

The Default Group is used by the support notes and privileges system to allow notifications to certain users. Normally leaving this as the default of "users" is recommended.

The Billing Group is a group that you will need to create if you are using the carrier dependent services functions. The status update script will send the billing group notes if a carrier dependent service is in a bad billing status, like past due, turned off, or canceled with fees owed.

The Shipping Group is a group that you will need to create if you are going to use the Ship Field Asset feature. Ship Field Assets lets you associate a field asset like a CPE or other device with that customer's service. The shipping group will get a note when a field asset is setup to be shipped.

The carrier dependent cancel url is for third party carrier dependent services. This can link to an internal web form or other form when a carrier dependent service is canceled to notify the third party carrier that this service is to be canceled.

The Billing Date Rollover time should be set to the time of day when new accounts created after this time will not be billed until the next billing day. This is used to prevent new accounts from being given the current billing date after the batch has already been processed for the day. This time field is in 24 hour time format.

The Weekend indicators allow the billing system to automatically skip certain days when assigning new billing dates to new customers. This is especially usefull for billing around weekends, so that a new customer's billing date does not fall on a weekend. You can choose what days of the week you want to assign as weekends here.

3.4 Users

The Users Tool will allow you to add CitrusDB database users. This is used to add your staff personnel that will be using the database to access customer information and billing. The user tool will show you a list of current users. In the default installation there will be two users present. An admin user that has all privileges to edit everything about the database, and an online user that is used to demonstrate the online customer account manager's message notification. If you have not yet done so, you should change the password for both the Admin user and Online Request user to something new.

To add a new user click Add New Database User. You will then be presented with a form to fill out, including the user's real name, their username, and their password. You will also choose their tool privileges here. Admin privileges will allow access to add new users, edit billing types, add new services, and add new modules. The Manager privilege will allow access to the credit card import/export features, invoices, payments, reports, and account activation. All other users should have No selected for both Admin and Manager privileges.

You can edit existing users by clicking the Edit link next to their name. This will allow you to change their tool privileges, their username, and their password. You can also delete a user by clicking the Delete link next to their name. When you delete a user or change their username, any notes they have made will stay in the database under their old username. You will also need to update any groups they are a part of to include their new username.

3.5 Groups

The Groups tool is used to assign users to different groups for notification and permission purposes. By default all the users who are added to the database are put in the "users" group. This should probably not be changed, since it affects access to many pieces of the system. You can add as many other groups as necessary. For instance, if you have a group of people who perform shipping duties, you might make a shipping group and assign them to this group. When a particular piece of equipment is needed you can send a note to the shipping group, which will notify all the individuals in that group about your message. When someone in the group marks the message finished, it will dissapear from view of all the people in the group.

This is also where you will add the billing group you specified in the settings tool if you are using the carrier dependent service functions.

3.6 Billing Types

The Edit Billing Types tool will allow you to edit what billing types are available in the customer's billing record. CitrusDB comes with a number of billing types already created, so you may not need to create more. In fact, it may be better to delete those that you do not need, so they don't get in the way. There are 6 methods of billing:

  • creditcard: For billing via a credit card batch export.
  • einvoice: For emailing invoices. The invoice is in text format, and will be sent to the email address indicated on the billing record.
  • invoice: For printed invoices.
  • prepaycc: For prepayments done with a creditcard. A prepaid account will not have it's payment dates updated until payment is made, it will not go into a Past Due status, it will become Not Renewed, and should be turned off if left un-renewed.
  • prepay: For prepayments done via checks, cash, or electronic funds transfer. A prepaid account will not have it's payment dates updated until payment is made, it will not go into a Past Due status, it will become Not Renewed, and should be turned off if left un-renewed.
  • free: For free accounts. These accounts will never be billed and will never be past due. The free billing type can be used for items like demo accounts or temporary free items.

If you need to add new billing types, they can be added using the form at the bottom of the Edit Billing Types tool window. You will need to provide the name of the new billing type. This will show up in the list in the billing record. The frequency of the billing type. This frequency is used to determine how often (in months) that billing type is processed. For example a Monthly billing type would use a frequency of 1, a Yearly billing type would use a frequency of 12 (for the 12 months between bills). There is also a special case frequency of 0 (zero) for a free or one time billing type. Lastly, select what billing method this new billing type will use, creditcard, prepay, invoice, einvoice, etc.

You can remove billing types from the database, however before doing so you will want to make sure that none of your current billing records are using that billing type, otherwise they may not get billed properly.

3.7 Holidays

CitrusDB has a table in the database called "holiday". This table consists of just one date field called "holiday_date" that holds dates that are billing holidays when billing is not preformed. If a new customer's billing date should fall on one of these days it will be moved up a day until it does not fall on a holiday. You should determine what holidays there are during the year that are not already covered by the weekends setting when billing will not be performed and add those dates to the table. This table can only be edited by using a database editor such as phpMyAdmin or SQL insert queries.

3.8 Services

CitrusDB comes with a default example service, credit, and prorate services. To make CitrusDB work for your organization you will have to add your services using the Edit Services tool. CitrusDB can support many different kinds of services, one time services and fees, recurring services, taxes and fees, linked or bundled services, and services with measured usage or hourly cost.

Open the Edit Services tool to see all your current services. Click Add New Service to begin adding the services that you provide.

3.8.1 Adding Your Services

The Billed By menu lets you choose which organization this service is billed by. This would only change if you have more than one brand organization inside of your citrusdb installation.

The Description field holds the service name that will appear in the service listing and on invoices.

Price holds the unit price of the service. For example if you have a service that is 9.95 per month, enter 9.95, if you have a service that is 39.95 per year, enter 39.95, a service that is 70.00 per hour, enter 70.00.

The Frequency indicates the service's monthly billing cycle. A service that is billed monthly would have a frequency of 1 (for every month), a service that is billed yearly would have a frequency of 12 (for every 12 months), a service that is only billed one time would have the special frequency of 0 (zero) for a one time service. One time services are automatically removed from the customer's service record listing and put into the service history after they are billed.

The Options/Attributes Table field holds the name of the table that holds the attributes for that service. When you make a service, you can also name a table in the database that holds the attributes that are associated with that service, such as location, equipment, usernames, special contact info, anything that may be associated with that service itself. This options table name is not required, so if your service has no attributes you can leave this blank, however if you add an attributes table at a later time the services that were already added will not be associated with the new attributes.

The attributes tables allow CitrusDB to support any kind of service at all, so it is not limited to one industry. If you are making a new attributes table, choose a table name and enter it in the Options/Attributes Table field. After you have added the service, go to the Options/Attributes Table link at the top and click Create next to the table name in the listing. This will create an empty table with the correct fields to link it with your new service. You can then add any attribute fields you want after the first two relationship fields, using SQL alter table commands or a tool like phpMyAdmin.

Most of the attribute fields will be shown as simple text fields, however if you want to make a menu of choices, or a Yes/No choice, you can use an enum field type that will show all the items in the enum within a drop down selection on the web page. You can also add text or blob fields that will be shown as textarea entry fields.

The Category field is a case sensitive name of a category to group the service under when choosing services to add to the customer's service record. This can be left blank if you only have a few services, but when you have many services it will be helpful to organize them into categories so you can jump to the type of service you are looking for.

Selling Active is on by default. It indicates whether this service is currently being sold. If set to Yes, it will show up in the list of services that can be added to an account. If set to No, it will not show up in the list of services that can be added to an account, however it can still be billed to customers who still have the service. This can be used to deactivate services that were only available for a limited time and are still being billed, but are no longer available for new sales.

The Hide Online field is set to No by default. This indicates whether you want to show or hide this service in the customer account manager and in the default view when adding services. You will probably will want to hide services such as setup fees, install fees, and sometimes bundled services. The services are hidden to regular internal CitrusDB users, and are only visible to manager and admin users using the Show All link when adding services to a customer.

The Activate Notify field holds the username or group name of the CitrusDB user who will be sent a support ticket note when this kind of service is added to an account. This can be used to notify order coordinators or a service activation department about services being added to an account if further manual processing is required.

The Shutoff Notify field holds the username or group name of the CitrusDB user who will be sent a support ticket note when this service is removed from an account. This can be used to notify order coordinators or a service activation department about services being removed from an account if further manual processing is required.

The Modification Notify field holds the username or group name of the CitrusDB user who will be sent a support ticket for services are are to be enabled, that were previously disabled, but not totally shutoff.

The Support Notify field holds the username or group name of the CitrusDB user who will be entered by default in new support tickets that are opened for this service.

Activation String is used to hold the field names that are passed to the statusupdate tool with this service. For example, when you add a new Internet account service you will probably want to provide the username and password from the options_table to the account activation system. To do that enter the string “username,password” in the Activation String field. The field names must be entered in matching case and be comma seperated with no spaces or other characters between the field name.

The Usage Label is used to label the Measured Usage field when adding measured usage services. For example if you have a service that is 70.00 per hour, you would put “hours” in this field, or if you have a service that is 5.00 per gigabytes, you would put “gigabytes” in this field.

Carrier Dependent services are those that are provided by a third party that needs a seperate notification of service activation and deactivation that is not part of your own account activation system. A Carrier Dependent service has additional steps when they are being deactivated that will send a shutoff notice to the customer to make sure they are aware the service is being shutoff. It will also send simultaneous notifications to the default billing group that is indicated in the Settings so that group can take care of communication to the third party carrier about what to do with a past due, turned off, or canceled service.

A carrier dependent service that owes money when their account is canceled will be marked as the status cancelwfee instead of canceled so that the billing group can find those customers and go after them for the money they owe. This is usually necessary since as a provider you will owe money for those services to the third party that was providing them and will have more incentive to collect on those charges rather than just cancel that service and be done with their account.

After you have filled in all the relevant service information you can click the Add button to add your new service to the database. You can now assign this service to your customers.

If you have many many services to add it may be easier to make a spreadsheet with all their attributes and import them into the master_services table directly using SQL insert queries or a database editor such as phpMyAdmin.

3.9 Linking Services with Setup Fees, and Bundled Services

Setup Fees and Bundled services are added like any other service using the Add New Service function. After you have added the setup fee or bundle service, choose the Link Services item to connect them to the service it goes with. For example, you may make a link From a Monthly Service Account to a Service Account Setup Fee. Now whenever a Monthly Service Account is added, the Setup Fee is automatically added to the service record also.

3.10 Taxes and Fees

CitrusDB comes with some default tax rates for certain locations. This data may be incorrect or out of date, please consult your local tax code to make sure you are billing for the correct tax amounts.

To add a new tax rate click the Taxes link in the Service Editor. and then click on Tax Rates. At the bottom of the Tax Rates screen is a form to add new Tax Rates. The Description holds the name of the tax, for example “Massachusetts Sales Tax”. The Rate holds the percentage of the tax, for a 5% tax enter “0.05”. The If-Field holds the name of the field in the customer record that is checked to see if this tax applies to the customer. For example, if the tax is based on what state a customer is in, enter “state”. The field names are the names according to the SQL database names for matching customer table columns, and will usually be in all lower case. The last field is the If Value, if this tax is for customers who are only in the state of Massachusetts, enter “MA” in the Value field. You can leave the If Field and If Value fields blank if the tax is applied regardless of the customer attributes.

You can remove tax rates by clicking on the Delete link. Make sure you do not have any services associated with that tax rate before removing it, or you may not bill them correctly.

3.10.1 Linking taxes

After you have added your applicable tax rates you need to link those taxes to the services that are being taxed. Click on the Taxed Services item. Here you can link a service with a tax. The tax will be added to customers with that service if the customer record meets the If Field/Value checks, and if the customer is not tax exempt for that tax type.

3.11 Field Assets

Field assets refer to devices out in the field like customer premises equipment (CPE) or other devices you want to track that are associated with a service.

To add Field Assets to a service first you must setup what Field Assets types are available for each category of service. Open the edit services tool and click on Edit Field Asset Types. Here you can enter the device description, choose the status of current for devices that you are currently shipping to your customers. If you have devices in the field but do not ship them to customers anymore you can change the device to the old status using this same edit screen. You can enter the weight of the device here which is not used yet, but may be used some day to calculate a shipping fee. Finally choose what service category that this device belongs to.

Now that you have added your Field Assets when you edit a customers' service you will see a choice called Ship Field Asset under the service attributes. If you want to track a field asset shipment you can then choose the type of field asset you are shipping and then enter the device details and shipping information. This will now associate this device with that service record and will show you that device on the service edit screen.

If one returns the field asset device you can edit the customer's service record and click on Return Device and enter information about why the device has been returned.

3.12 Searches

CitrusDB comes with a number of searches pre-installed in the searches table. Search fields are edited in each module's search.php file and show up on the search page. You will probably want to create search queries for your new service attributes, so you can find services by the specific attributes that have been specified in their options/attributes table. You can add new searches by adding a new row to the searches table. For example in the query field:

   SELECT * FROM customer WHERE phone LIKE '%%s1%%' 

This will allow you to search the customer table for a phone number. The %%s1%% will be replaced with the information filled out in the search form. Then link to the search row id using a small input form in the corresponding module search.php page. In this case you would edit the /modules/customer/search.php file and add new html for a form:

   <form ACTION="index.php?load=dosearch&type=fs" METHOD="POST">
   Find Phone Number: <input type=text name=s1>
   <input type=hidden name=id value=4> 
   <-- "4" is the id of the row in the searches table -->
   <input type=submit name=submit value="search" class=smallbutton>

You can search with multiple field inputs by naming each field s1, s2, s3, s4, or s5.

3.13 Attribute/Option URL Links

You can add a link to the right of the service attribute field that will allow users to query a web address with that service attribute. This can be used for things like checking the finger results on a Unix server or checking other account status through a website. The URL Options Links are stored in a table called options_urls. You can add new links or edit existing ones using SQL queries or a utility like phpMyAdmin. The URL link is matched by the name of the attributes table field, so any attributes table with a matching name will get a link next to it. For example if you have a username field and you want people to be able to run a finger.cgi on any attribute table field with a name of username you would make a new entry into the options_url table with the fieldname of username and a url such as

the %s1% is a place holder where the value of the attribute will be put into the URL.

3.14 Setup Finished

You have now completed the setup of CitrusDB to work with your organization. You can now begin adding customers and billing them for services!

Customer Service:

Find an exisiting customer account record

Click the search icon at the top of the screen. Here you can search for a customer by the account number or other search fields that have been specified by the citrus administrator.

Adding a new customer

Click the New icon at the top of the screen. Fill in the initial customer account information and click Add. You will then be taken to the customer's new account record. On the left side you'll see the account number that was assigned to the new customer and the customer's name and company name. Now you can edit the customer's information, add services, billing, and support information.

Add services to a customer

Click on the Services tab to view the services that are assigned to this customer. If any services have taxes or fees associated with them, they will show up under the Taxes and Fees heading. To add a new service click on the Add Service link. This will show all the available services that can be added. You can click on the service name or the add button to add the service. If the service you are adding has attributes it will prompt you to fill in any service attributes. You'll see a field here called Usage Multiple. This field is used to multiply the cost of a service by that amount. This can be used for billing services that cost by the minute, hour, megabyte, etc.

Adding Prorate Services

Most installations of citrusdb include a premade Prorate service. If you do not have a permade Prorate service you will need to add one. A Prorate service is simply a service with a frequency of zero, a Pricerate of 1, and a Usage Label of whatever currency you are using.

First add the service they are getting to their account and then add a onetime prorate service to the customer's account. When adding the prorate service set the Usage Multiple to the amount you want to prorate the account for to get it into the right billing cycle. You need to do a little math here on your own. So say you add a 19.95 service, in a 30 day month each day is 0.665 cents. If you want to bill them on July 1st, and today is June 8th, then there are 22 days of prorated service, which is 14.63. So put 14.63 in the Multiple field. Their first bill will then equal 34.58 with the service item plus the prorate item.

In the billing section for that customer you would set their next billing date to be July 1st, set the from date to July 1st also, and then it will fill in the to date equal to whatever number of months the billing type covers starting July 1st, so if you had a monthly invoice service the to date would be August 1st. This will make the billing dates at the top of the invoice not exactly match the prorate time period since there is a prorate item in there and they really started service on the 8th. In the prorate description one could put the date of the prorate period.

Edit service information

To edit a service click on the Services tab. Here you'll see all the services that the customer has. You can edit the service by clicking on it's name in the list or by clicking the Edit button. In the edit screen you can modify any service attributes that are stored in the options_table for that service type. You can change the Usage Multiple for this service. A usage multiple is used to multiply the price of the service by the usage. You can use this to multiply by item, hourly, daily, megabyte, gigabyte, or other amounts etc. The Billing ID field is used to assign this service to a different billing id than the default billing id for this customer. This can be used to allow multiple billing types on different billing cycles for one customer.

View a customer's service history

Click the Service Tab and then click the History link. This will show the customer's service history, including any canceled services and one time services that have already been billed for.

Edit a customer's information

To edit a customer's information click on the Customer tab and click the Edit Customer link. After making any updates make sure you click the Save Changes button to save them.

Add a billing information to a customer

To add billing information to a customer click on the Billing tab. When you have a new customer the billing information will be empty. Click on the Reset Address to Customer link to copy the customer's name and address information to the billing information if they are the same. To edit the information click on the Edit Default Billing link. Here you can make any changes you need to. Make sure you choose a Billing Type for the account when editing their billing information for the first time.

Add an alternate billing type to a customer's service

Click the billing tab in the customer record. Click the Add Alternate Billing link on this screen. This will prompt you for new billing information. Click Add when you are done. You can now assign this alternate billing type to a service by going to the Service tab, Editing a service, and changing the billing id on the edit screen. This allows you to have different billing types for different services, such as when some services will be billed monthly and another service is to be billed on a yearly basis.

Rerun a declined or invalid credit card

Click the billing tab and then click the Rerun link on this screen. This is to be used to rerun the credit card billing if a previous credit card transaction was declined. This will run past transactions but will not add new ones to the bill.

Put a note on a customer's account record

After you provide help to a customer or make a change things on their account it can be helpful to make a note of what was done. To leave a note, click the Support tab on the customer record. Here you can begin entering the note you want to leave. You may choose to notify a specific group or user of the database or not notify anybody of your note. When you are done writing your note click the Add button. Notes will show up in the customer history under the Notes tab on their account record.

Check notes sent to you

The number of notes that are sent to you will show up on the message notification tab, it will say how many messages you have waiting. You can click this tab to view the notes or click the Support tab and the Check Notes link. You can set the note to pending while you are waiting for more information or performing the operation. When you are done with the note, click Finished and it will be hidden from your new message listing.

Account history tabs


Shows notes and customer history


Shows invoices and credit card transactions sent


Shows payments that have been made

Billing Details

Shows what service the payments were applied to

Changing your citrusdb access password

Click on the Tools icon at the top of the screen, then click on the Change Your Password icon. Any user may change their password using the Change Your Password tool. To do so they must enter their Old Password and the New Password that they desire to have and then again to confirm it. After it has been changed, the next time the user logs into CitrusDB it will require the new password. Passwords are stored in an MD5 encrypted format inside the user table of the database.

Managing Services

Activating Accounts

Click on the Tools icon at the top of the screen and then click on the Activate Account icon. This will create a file in the path_to_ccfile you specified (the same path as credit card data) with a list of accounts to ADD, DELETE, ENABLE and DISABLE today. This file can be used in conjunction with cron jobs or scripts that check for new account files and process them automatically. The interface also gives you the option to download the file to process it on your local PC. It will print an ADD line for new services added today. A DELETE line for services that have a removal date of today. An ENABLE entry for services that should be turned back on today that were previously disabled due to billing issues. A DISABLE line for services that should be shut off today such as accounts with declined credit cards or past due. It prints them in a standard text format that could be processed by an activation script at a later time. Example:
Each line item is in quote comma delimited format (with no spaces).

Printing Welcome Letters

Click on the Tools icon and select the Welcome Letters icon. This will print a simple welcome letter for new customers that can be mailed. It has a summary of the services they signed up for included.


Import new accounts from a file

Online order forms can save the order data and you can use Import New Accounts tool to import the orders into CitrusDB. The New Accounts tool can also be used when migrating to CitrusDB, by importing existing customer records into citrus with all their customer, billing, and service information. Here is an example of what each line in order data file should look like

   Online, Test User, Test Company, 523 Test Ave., Testcity, CA, USA,95113, 408-555-5555, 408-555-6666, 408-555-7777,,, What is your favorite color, red, testpassword    
   Test User, Test Company, 1 Test Street, Testcity, MA, USA, 01234,555-555-1234, 555-555-1235,, 1, 4***********1111,0406, 1
   3, usernm, passwd, Linux, 1 Test Street, Cisco Thing        
   3, nameuser, wordpass, Windows, 123 Test Street, USB Thing       
   -----BEGIN PGP MESSAGE-----
   -----END PGP MESSAGE-----

The first line of the import record is the customer data, the second line is the billing data. The credit card number here should be masked with ****'s in it so that one cannot view it. The real card number will be stored in the encrypted PGP text below. The third section of lines are for services to add to the account upon import. The final lines are for the PGP ascii armored credit card data so that you can encrypt the data with your public key before importing it into the database, preferably directly in the order system that is saving these new customers. If the customer does not use a credit card you will need to include the -----BEGIN PGP MESSAGE----- and ------END PGP MESSAGE----- lines with nothing in between them. This denotes the end of that customer record.

Field definitions:

   source, name, company, street, city, state, country, zip, phone,alt_phone, fax, contact_email, tax_exempt_id, secret_question,secret_answer, account_manager_password
   Name, Company, Street, City, State, Country, Zip, Phone, Fax,Email,Billing Type ID, Masked Creditcard Number, Creditcard Expiration, organization_id   
   Service ID, (any fields that make up the services options_table) inthis case, Username, Password, OS, Street, Device        
   -----BEGIN PGP MESSAGE-----
   -----END PGP MESSAGE-----

Export credit card batch

Click on the Tools icon and select the Export Credit Cards icon. This tool is used to export the credit cards to bill in a batch format. This will prompt you for what date you want bill. It will then show you a summary of the services it found to bill. You can then export the cards to a file. This file will be stored in the folder you specified using the Path To Credit Card File in the general configuration. This can be used in conjunction with cron jobs or scripts that check for new billing files and process them automatically. The interface also gives you the option to download the file to process it on your local PC. This file can then be formatted and transmitted to your credit card billing provider via a batch upload. The format for these line items is controlled by the Credit Card Export Variable Order in the general configuration.

Thehe file will be exported in a format that looks like:

"CHARGE","1","19","4111111111111111","0909","1036.4","12345","1 Example St"

Import credit card batch

This will prompt you to browse your hard drive for the batch results file from your credit card processing system. The format for this file is:
"CHARGE or CREDIT","card number","card expire","amount","billing id","approved (Yes) or declined (No)", "avs response"

it will look something like:

Upon importing this file accounts will have their billing status updated accordingly. If a CREDIT is imported it should also have a response of declined (No). It will show as a credit in their payment history, but will not change their live account status.

Import credit card change file

Click the Tools icon and select the Credit Card Changes icon. This tool is used to import credit card changes that have been sent in via external methods such as online customer management forms, usually done after a card has declined and a notice has been sent to the customer. The format for this file is:
account_number, name, street, city, state,zip,card_number (masked), card_expiration_date
This will updated the credit card number on all billing id's assigned to that customer.

Print invoices

Click on Tools and select the Print Invoices icon. This tool can print batches of invoices based on their Next Billing Date, or individual invoices based on their Billing ID number, or Account Number. After you enter this information it will then provide you with a summary of what it found and prompt you to print the invoices. Then it will render the invoice page as a PDF file that can be opened with an PDF reader.

Email Invoices

Click on Tools and select the Print Invoices icon. This tool can print batches of invoices based on their Next Billing Date, or individual invoices based on their Billing ID number, or Account Number. After you enter this information it will then provide you with a summary of what it found and prompt you to print the invoices. Then it send an HTML formatted email invoice to the billing_email specified in their billing record.

Enter Payments

Click the Tools and select the Enter Payments icon. To enter payments you must enter at least three pieces of information, the billing ID, the payment amount, and the payment type.
  • Billing ID: This is the billing id number of the customer who is being paid
  • Invoice Number: This is an optional value for the number of the invoice that is being paid. This causes the billing system to pay only those services that were on this invoice, instead of any services that owe money.
  • Payment Amount: This is the amount that was paid
  • Payment Type: This is the type, Check, Cash, or EFT of the payment.
If an over-payment is made, it will show you the amount left over as the over-payment. You may then make a new credit on the account for that over-payment or apply it to a different account, for example, if the customer has multiple records.

Invoice Maintenance

You can reach this tool two different ways. Either by going to the Tools icon and selecing the Invoice mantenance icon, then enter the billing ID that you want to view. You can also reach the invoice maintenance for a specific customer from the Billing tab on that customer's record by clicking the Invoice Maintenance link. This tool will list out the invoices for that billing ID. You can click on the pdf or html link to see a snapshot of that invoice in pdf or html format, click the email link to email a copy of the invoice to the customer, or click on remove to remove that invoice and related charges from their account. You may only remove an invoice that has not been paid for or had any credits applied to it.

Refund a customer

The Refund Report link on the customer's billing record can be used to indicate what service and amount of a refund to apply to a service. After a refund is indicated here, you can run a credit card batch of refunds all at once using the Refund Tool link in the Tools section. This will create a new credit card batch file, in the same format as your regular credit card batches, full of all the outstanding CREDITs that have yet to be processed. After these are processed you can import them using the credit card import tool, to indicate the credits in the customer's payment history.


Customer Summary

Click on the Tools icon and select the Customer Summary icon. This will produce a summary that shows the total number of customers and a break down of the number of customers per each service type.

Revenue Report

This report shows the total amount of revenue for each service in the time period entered. It will also show you the total number of service records that were charged to create that revenue in parentheses.

Past Due Report

This report shows the past due accounts according to the days indicated in the general configuration.

Recent Declines

Click on the Tools icon and then select the Recent Declines icon. You will be prompted for what day from the previous week you want to find declined cards for. It will then produce a report of the credit card declines from that day. You can use this report to contact customers to get new billing information.