Database structure

From ISPWiki

Jump to: navigation, search

The following article describes how to transfer data from one accounting system to another. The description is given for the Corporate version, however it can be applied to all other versions, as the data structure is identical, and some data are not used at all.

We need to transfer users' data from an existing base.

Configure the Provider's settings, Companies, Administrators, Payment methods, Gateways for domains registration, Packages, etc. via the BILLmanager interface. The tables related to these data are not described.

Once the information in database has been modified, you must restart BILLmanager (killall billmgr), or reset cache of a certain table (/usr/local/ispmgr/sbin/mgrctl -m billmgr drop.cache elid=TABLE_NAME)

Customer's data

Information about a customer is listed in 3 tables:

  • account - information about customers' accounts
    • id - customer unique identifier
    • name - any internal name that cab used to identify a customer
    • level - access level, default value for a customer is 5.
    • parent - link to the provider's account, normally 1
    • partner - affiliate link, if a customer was registered by someone's affiliate link
    • bonustype - type of the selected affiliate program
    • regdate - registration date
    • regip - registration IP-address
    • note - additional information
    • user - customer's name (customer may have several users)
    • id - user unique identifier
    • name - user name that he uses to log in to the system
    • account - link to the user's account
    • password - password hash (DES, MD5, BLF are acceptable)
    • realname - user's full name
    • email - email box
    • superuser - normally 1, a single user cannot be restricted in access privileges
  • profile - customer's users (customer may have several users)
    • id - payer unique identifier
    • name - name
    • account - link to the payer's account
    • ptype - type of a payer (1 - individual, 2 - legal entity)
    • person - contact person
    • vatnum - VAT number
    • kpp - KPP
    • country - postal address, link to the country (the country table)
    • zip - physical address, zip code
    • city - physical address, city
    • address - physical address, street, building, etc.
    • ccountry - legal address, link to the country (the country table)
    • czip - legal address, zip code
    • ccity - legal address, city
    • caddress - legal address, street, building, etc.
    • phone - phone number
    • fax - fax
    • email - e-mail box
    • www - web-site
    • director - contract information, full name of a person entering into a contract
    • jobtitle - contract information, job title of a person entering into a contract
    • rdirector - Contract information, full name of a person entering into a contract
    • rjobtitle - Contract information, job title of a person entering into a contract
    • baseaction - Contract information, acts on the grounds of...
    • passport - contact information, individuals, passport data
    • regdate - registration date.
    • id1c - identifier in 1C:Accounting (for configuring the integration)

Beside basic data, you should create several connections:

  • projects that are associated with a customer's account, account2project
    • account - link to the account table
    • project - project unique identifier. Use 1 in BILLmanager Advanced and Standard
  • companies a customer may use, profile2profile
    • profile - link to the profile table
    • company - company unique identifier (you may locate it in the companies' list, for Standard version - 1)

Customer's products/services

  • item - general description
    • id - service unique identifier
    • name - service name
    • account - link to the account a service belongs to
    • price - link to the pricelist, service package
    • period - link to the priceperiod, specifies an order period
    • server - link to the server, if any
    • status - service current status (1 - ordered, 2 - active, 3 - suspended, 4 - deleted, 5 - in progress)
    • createdate - order date
    • suspenddate — suspension date
    • deletedate - removal date
    • billdate — the latest date when the service was processed
    • expiredate — expiration date
    • autoprolong - link to priceperiod, renewal period
    • note — additional information
  • vhost - virtual hosting data
    • pid - link to the item, hosting identifier
    • domain - hosting domain name
    • username — user name
    • password — user password
    • ip - hosting IP-address
  • vds - VPS information
    • pid - link to the item, virtual server identifier
    • domain - virtual server' domain name
    • ip - virtual server' IP address
    • ostempl - disk template
    • password - password to gain access to the control panel
  • domaincontact - domains' contacts
    • id - contact unique identifier
    • ctype - contact type (person - individual, company, generic - administrator)
    • name - contact name
    • account - link to an account, owner of a contact
    • company - company
    • company_ru - company in Russian
    • firstname - first name
    • firstname_ru - first name in Russian
    • middlename - middle name
    • middlename_ru - middle name in Russian
    • lastname - last name
    • lastname_ru - last name in Russian
    • email - email address
    • phone - phone number
    • fax - fax
    • birthdate - date of birth
    • passport — passport data
    • inn - INN
    • kpp - KPP
    • private - hide contact data in WHOIS (1 - hide, 0 - show)
    • Contact's address:
    • la_country - country
    • la_state - state or province
    • la_postcode - zip code
    • la_city - city
    • la_address - address
    • Contact's postal address:
    • pa_country - country
    • pa_state - state
    • pa_postcode - zip code
    • pa_city - city
    • pa_address - address
    • pa_addressee - addressee
  • domain - domain information
    • pid - link to the item, domain identifier
    • name - domain name without a zone
    • tld - link to the tld, that specifies a domain zone
    • owner - link to the domaincontact, owner's contact
    • admin - link to the domaincontact, domain administrative contact
    • tech - link to the domaincontact, technical support contact
    • bill - link to the domaincontact, billing department contact
    • ns0, ns1, ns2, ns3 - domain name server
    • autoperiod - link to the priceperiod, specifies a renewal period
    • registrar -link to a registrar, specifies a domain registrar
    • expire - registration expiration date
    • status - domain current status
  • itemip - additional IP-addresses
    • id - IP-address identifier
    • name - IP-address
    • item - link to the item, specifies a service that an alias IP-address is assigned to
    • cdate - order date

Funds, payments, expenses, personal account

  • subaccount - customer's account, one for a separate project
    • id - account unique identifier
    • name - description
    • account - link to the owner of the personal account
    • currency - account currency, link to the currency table
    • project - link to the account's project
    • balance - current balance of account
    • creditlimit - credit limit
    • postpayd - select to allow for postpaid services
  • credit - payments
    • id - payment unique identifier
    • sender - payer, link to the profile
    • recipient - recipient, link to the profile
    • type - payment method, link to the companycrtype table
    • num - payment number
    • cdate - creation date
    • pdate - payment date
    • currency - payment currency, link to the currency table
    • amount - payment amount
    • nativeamount - amount in native currency
    • usedamount - amount used
    • state - current status status ( 1 - not paid, 2 - in progress, 3 - pending, 4 - paid, 5 - fraud)
    • subaccount - account that is used to make payment, link to the subaccount table
    • description - payment destination
    • info - additional information
  • expense - expenses
    • id - expense identifier
    • name - description
    • subaccount - account that was debited, link to the subaccount table
    • item - services that was charged for, link to the item
    • cdate - charge date
    • amount - amount
    • notpayd - amount due
  • expense2credit - expense is related to the particular payment
    • expense - link to the expense table
    • credit - link to the credit table
    • amount - amount
Was this helpful? Yes | No
Personal tools