reference : http://billing.sunsaturn.com user/pass : test/test Asterisk billing module. DID= VOIP term for a phone number like 2045559999 (this document is an attempt to describe how to interface in php with a c module that uses many existing tables to determine how to rate/place calls) Your job is to understand how the c module operates and program a presentable interface to it and a complete billing solution for customers to download your php code. On completion of this project all php code will be downloadable, and a demo interface to login to see the interface live are the goals. You will be given a ftp account to work on this project as well as phpmyadmin interface to access the database. My intent will be to show you how to interface mostly, as a complete overview of c module is beyond the scope of this text. You must understand what all the fields are for, and understand what should be going into those fields from your webinterface. You must understand what needs to be displayed to enduser, and what error checking is a must. SO lets get to it, i'll describe what all the fields are for the tables then describe what needs to happen with them. This is a database table only reference, the design of the interface will be included in webinterface.txt. Database table reference: DIDs areacodes tollfree iax sip voicemail cdr_$month(all 12 months) Descriptions of tables: ----------------------------------------------------------- DIDs table is master of all. OUR MOST IMPORTANT TABLE. Here is the complete table layout: CREATE TABLE `DIDs` ( `DID` varchar(30) NOT NULL, `forwardto` varchar(20) NOT NULL, `money_left` decimal(10,4) NOT NULL default '0.0000', `rate` decimal(10,4) NOT NULL default '0.0000', `discount` decimal(10,4) NOT NULL default '0.0000', `surcharge` decimal(10,4) NOT NULL default '0.0000', `monthly_charge` decimal(10,4) NOT NULL default '0.0000', `firstname` varchar(30) default NULL, `lastname` varchar(40) default NULL, `accountcode` varchar(20) NOT NULL, `lastcall` timestamp NOT NULL default CURRENT_TIMESTAMP, `bill_seconds` int(11) NOT NULL default '0', `bill_increment` int(11) NOT NULL default '0', `r1_seconds` int(11) NOT NULL default '0', `r1` decimal(10,4) NOT NULL default '0.0000', `r2_seconds` int(11) NOT NULL default '0', `r2` decimal(10,4) NOT NULL default '0.0000', `r3_seconds` int(11) NOT NULL default '0', `r3` decimal(10,4) NOT NULL default '0.0000', `route` varchar(80) NOT NULL default 'IAX2/1003@1003', `payment_type` varchar(20) NOT NULL default 'prepaid', `route_type` varchar(30) NOT NULL default 'longdistance', KEY `DID` (`DID`), KEY `accountcode` (`accountcode`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Tables may not be renamed as well as field names may not be changed or deleted. You may however add new fields to help you, to existing tables. Description of fields: DID: this can only consist of 2 things, a physical phone number like 2045559999, or the word 'longdistance'. Placing a number like 2045559999 tells the module to grab rates for that call from that row, otherwise if 'longdistance' module grabs its rates from areacodes table for number called, but still uses discount, surcharge, and monthly_charge for that DIDs row. I'll be going on more about this field later on and how it applies to different "route_type" entries, for now remember to error check only integers or the word longdistance ever gets into this field. forwardto: this field is for integers only and is a number like 2045559999, a call gets placed to say the DID field number of 1800555999, then module determines to forward to this number afterwards. Again your only concern is to just insert a number in here and error check its actually a number. money_left: the heart of the money making...NO SCREW UPS ON THIS FIELD WHATSOEVER. If someone credits their account 20 dollars, this should add 20 dollars to this field. The paypal IPN interface comes in here, you goal is to place enough security on the IPN interface to ensure that payments are real. You are to create a section called paypal for admin user where they can enter their email/code to use for collecting payments when normal users are logged into their sections. Remember this will be downloadable, so hardcoding any paypal emails etc will not work. Module will debit this field accordingly on its own after calls. rate: what do we bill the customer per min for the call? This is the field we use, 2 cents a minute would mean putting 0.02 in this field. Notice type of this field is set to decimal(10,4) so even rates like 0.039 are legal. Just putting 2 in this field means charge 2 dollars a minute! Make sure your using format 0.02 for this field. Do proper error checking to ensure only decimal, integer types get in here. I never want a php error returned saying bad type for entry, your code should check the input always first and inform them of their error. Assume all end users are stupid and will always put bad input...error check, error check, error check! discount: same decimal type as rate so same error checking applies. this is used for a convenient way to discount a rate on a per user basis. So lets say our rate is 0.02 cents a minute for this DID. But we want to only charge them 0.01 cents per min for it. Well we can put 0.01 in discount field so 0.02-0.01=0.01. Now hes getting charged 0.01 cents a minute. You may be wondering why not just change rate to 0.01 and not use discount at all? Well remember you can have a type called 'longdistance' for a DID in DID field. So any rate for any areacode it looks up in areacodes table and finds it would automatically discount 0.01 from it....thats the benefit. surcharge: exact opposite of discount, lets add 0.01 to the rate. So if we had 0.02 cents a minute for a rate and want to charge him 0.03 instead we could just put 0.01 in surcharge...0.02+0.01=0.03. Module does all this math from these fields. I won;t get into any more detail, its same as discount cept it adds to rate, same erro checking applies. monthly_charge: this is not completely finished yet as it needs a separate program running in cronjob to debit this balance monthly, but it will apply on calls being made. monthly_charge is a charge that is debitted from their money_left field once a month. Module also takes precautions to not allow a call if they don;t have at least this amount left in money_left. Whats best way to deal with this field? In a section called customer, if I selected a customer it should bring up a list of everything that accountcode owns. 10 DIDs and 1 type of longdistance of instance, and i could place a per monthly_charge on each DID or the type of longdistance if i wished. Another good place for it is in section called LOCAL, nice to see if their is a monthly charge there as well when displaying DIDs. Error checking, make sure this is a money value! End users can actually disable this field completely in the c config file for the module, but i think most of the time they will leave it on. firstname: firstname of customer, error check on max length of 30 chars and set fieldlength to max 30 as well. Please use javascript mostly for error checking highlighting any errors made on anything , and backend error checking in php in case javascript was disable in their browser. When working on error checking, do your checking in jscript, then disable jscript in your browser and do it again watching php can error check as well. Last thing we want is things like "hidude" getting into a field like money_left! lastname : same as above applies, last name of customer. accountcode: our most important one for tracking everything about a customer! This field only contains a number that is unique to a customer. NO other customer should ever get this number. This field would be unique except problem is same customer can have multiple DIDs , so same accountcode is used for multiple DIDs this customer owns. I suggest creating a seperate table just to keep track of next number to use so noone ever gets same accountcode as someone else. I won;t accept using random on this.... increment based on last accountcode used. Deleting a customer later on should be as easy deleting everything in the table with their accountcode. ie : delete from DIDs where accountcode='1002', and please use some kind of popup saying, "are you sure you wish to delete this customer" on deletes! Again assume all end users are stupid and accidently delete things. lastcall: always just insert NULL here so mysql internal updates timestamp on this field. bill_seconds: this would actually have been better called "bill minimum". The minimum amount of seconds to bill a customer per call. Self explanatory i think, just make sure only an integer ever gets in here. bill_increment: increment to charge a call. Best way to understand this is to do some example math. A person calls someone for 11 seconds, but our bill_increment is 2. So this means he will be billed for either 2,4,6,8,10,12,14 etc seconds when call completes. So in this case he would get billed for 12. Again you don;t have to worry about this just ensure error checking its an integer, module worries about how to bill. ----the next 6 fields need a good amount of understanding ----read them over once, then re-read each of them again as error checking ----is very important here. YOu could segfault the module completely ----trying to let it add letters together that are suppose to be numbers! ----although the c program does error checking on this, end user ----will be wondering why calls aren;t going through, do it on this end! r1_seconds: the next 6 fields here all go together. An interesting problem was proposed where what if we want to bill a customer 3 different rates for a call? ie : 0.05 cents a min for first 10 seconds, 0.02 cents a min for next 5 seconds, 0.01 for rest of call. This is where these fields come in. r1_seconds is the first how many seconds to bill the customer at a rate of r1(our next field). If r1 is set, r1_seconds must be more than 0! Make sure to error check this, DO NOT ALLOW r1 to be set if r1_seconds is not greater than 0!!! r1: rate to bill for how many r1_seconds. If r1 is set, we override the field rate in the module and continue with r2 and r3 as well if those are set otherwise we just bill r1 for the entire call. r2_seconds: if r1 and r1_seconds are set, module checks if r2_seconds and r2 are set. If so as in our example we'll bill them 0.02 cents for next 5 seconds. DO NOT ALLOW r2 to be set if r2_seconds is not greater than 0! Error check r1 and r1_seconds are greater than 0 as well to set the r2 fields. r2: rate to bill for how many r2_seconds. If r2 and r2_seconds are greater than 0 and r1 and r1_seconds was set we will be using this rate as well in the c module. Error check for integer only. r3_seconds: this one just has be greater than 0 to enable it. Ie from example bill r3 for the rest of the call. So we could just set this to 1 and bill rest of call at r3. As long as this field is greater than 0 we will bill at r3 for remainder of the call. In fact there is no need to include this in interface, if they set r3, just set r3_seconds to 1 and your done. r2 and r2_seconds have to be greater than 0 to set this at all....make sure to check this. r3: bill what rate for remainder of call? Ok so you get the idea, error checking central on those 6 fields. That will probably be the biggest error checking needed for entire project. route: routes can take various shapes depending on what 'route_type' (another field) they are using. Most commonly they look like this: IAX2/1003@1003 or SIP/1003@1003 or Zap/1 I haven;t found an efficient simple way to do this one, but best I can figure just let them input what they want here to their hearts content. Theres a varchar 80 ready for it. Route is where to route the call over the internet to actually make the call. I think we'll devise something intelligent to do with this one when project is close to complete. payment_type: can only be 1 of 2 things, 'prepaid' or 'postpaid'. This should be a dropdown so there is no room for error. route_type: how the module knows what to do with the call, does it just call a number? or does it call a longdistance number? or does it take a call to a number then forward it to another number? etc..... well we have 5 types that deal with this problem of letting the module know how to route the call. The types in order are 'local', 'longdistance', 'forward', 'tollfree' and 'tollfree_forward' and should be dropdowns so there is no room for error again. An example will be needed in interface to help endusers understand those 5 contexts as they need to understand what contexts do what differently to a call. I'll go more in detail of what they each are later, your concern here is they are dropdowns so only 1 of those 5 gets in the field. ----------------------------------------------------------- Table areacodes: well if you understand the DIDs table well, this one is a peice of cake, its pretty much the same and abit simpler. So i won;t go on repeating myself, on fields you already came across. table structure: CREATE TABLE `areacodes` ( `country` varchar(50) NOT NULL default '', `dialcode` varchar(50) NOT NULL default '0', `rate` decimal(10,4) NOT NULL default '0.0000', `bill_seconds` int(11) NOT NULL default '0', `bill_increment` int(11) NOT NULL default '0', `r1` decimal(10,4) NOT NULL default '0.0000', `r1_seconds` int(11) NOT NULL default '0', `r2` decimal(10,4) NOT NULL default '0.0000', `r2_seconds` int(11) NOT NULL default '0', `r3` decimal(10,4) NOT NULL default '0.0000', `r3_seconds` int(11) NOT NULL default '0', `route` varchar(80) NOT NULL default 'IAX2/1003@1003', `provider_accountcode` varchar(20) NOT NULL default '1', UNIQUE KEY `dialcode` (`dialcode`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; This table contains rates we bill customers at for different areacodes. ie: lets charge 0.02 cents for calls to areacode 204 and 0.30 cents a call to albania dialcode 1135569 etc. So basically if we don't own the DID they are dialing, we'll be looking up rates here most of the time and the route to send the call to. An accountcode must have a DID of 'longdistance' in order to be allowed to make longdistance calls or module will just deny the call altogether. Each accountcode may only have one DID called 'longdistance' in the DIDs table. Description of fields: country: self explanatory, what name to call the 'dialcode' field. dialcode: whats the area/dial code. Error checking to make sure only integers here. rate: same as DIDs table, rate per min, everything applies. bill_seconds: same as DIDs table bill_increment: same as DIDs table r1 r2 r3 r1_seconds r2_seconds r3_seconds: same as DIDs table with the heavy error checking. route: same as DIDs table. provider_accountcode: accountcode of our provider for this areacode. We will need an import section for admin user to import these rates from providers, as an example lets look at voipjets: http://www.voipjet.com/voipjet30dec05.txt They should be able to import a list like that into this table. I leave your creativity to accomplish this, a possible php script exploding on some delimiter in some way would be best for this when they upload a text file like this to you. ----------------------------------------------------------- table tollfree: CREATE TABLE `tollfree` ( `country` varchar(50) NOT NULL default '', `dialcode` varchar(50) NOT NULL default '0', `rate` decimal(10,4) NOT NULL default '0.0000', `bill_seconds` int(11) NOT NULL default '0', `bill_increment` int(11) NOT NULL default '0', `r1` decimal(10,4) NOT NULL default '0.0000', `r1_seconds` int(11) NOT NULL default '0', `r2` decimal(10,4) NOT NULL default '0.0000', `r2_seconds` int(11) NOT NULL default '0', `r3` decimal(10,4) NOT NULL default '0.0000', `r3_seconds` int(11) NOT NULL default '0', UNIQUE KEY `dialcode` (`dialcode`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; This table is used for tollfree numbers. The way providers work they charge based on where a call came from, not what number called to. Backwards from how everything works. To handle this we have this table called tollfree which is basically same format as areacodes table but c module looks up the number called from on this table instead of number called to when 'route_type' is say set to type of 'tollfree'. Not going to go in great detail on any of these fields, you know them all already, also needs a section to import lists into this table like with areacodes table. -------------------------------------------------------------------- table cdr_$month these 12 tables are call detail record tables the module writes to after a call. Your only responsibility with these tables are displaying them. A normal user logs into their interface, they should be able to see the calls they made. Display them by month since tables are split this way. Table structure for 1 of the 12: CREATE TABLE `cdr_November` ( `callstart` datetime NOT NULL default '0000-00-00 00:00:00', `callend` datetime NOT NULL default '0000-00-00 00:00:00', `clid` varchar(80) NOT NULL default '', `src` varchar(80) NOT NULL default '', `dst` varchar(80) NOT NULL default '', `dcontext` varchar(80) NOT NULL default '', `channel` varchar(80) NOT NULL default '', `dstchannel` varchar(80) NOT NULL default '', `lastapp` varchar(80) NOT NULL default '', `lastdata` varchar(80) NOT NULL default '', `duration_billed` int(11) NOT NULL default '0', `duration_forward` int(11) NOT NULL default '0', `duration` int(11) NOT NULL default '0', `billsec` int(11) NOT NULL default '0', `disposition` varchar(45) NOT NULL default '', `amaflags` int(11) NOT NULL default '0', `accountcode` varchar(20) NOT NULL, `oldaccountcode` varchar(20) NOT NULL, `charge` decimal(10,4) NOT NULL, `uniqueid` varchar(32) NOT NULL default '', `userfield` varchar(255) NOT NULL default '' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; What your concerned with is selecting that users accountcode from accountcode field and display their calls. This is where your design skills really come into play. Pie charts line, bar etc would be great. I think all these fields are pretty much self-explanatory, for this I would be displaying all the fields for a call except for dcontext, channel, dstchannel, lastapp, billsec, amaflags, uniqueid, accountcode, oldaccountcode and userfield. duration and duration_forward are tricky ones. Only when the 'route_type' of a call is forward or tollfree_forward will duration_forward ever be set. You can tell what type of call it was from the userfield field. If you add these 2 fields together you get duration_billed which the module does. So what would be preferable is if you detect the field 'userfield' as matching tollfree_forward or forward, to actually display both of these fields and the charge for the call, otherwise just display duration_billed and be done with it. In other words type is forward: display duration and duration_forward and duration_billed as the total. Type is not forward: display duration_billed. On the subject again of stats: use these tables, have a dropdown of the 12 months, allowing them to see all calls for a certain month, defaulting to current month for a normal user login looking at stats and their balance left. On admin side: let the admin pick a user they choose and display stats for that user just like they would see them. Basically just a convenient way of admin not having to login to diff user accounts to see them. Paging will be important here, don;t want to many calls displayed per page. -------------------------------------------------------------- Table traffic_$year These tables are used for the admin for advanced stats. An admin needs to be able to see how many calls are being made a day on his system as well as how many calls per hour. Table structure CREATE TABLE `traffic_2005` ( `date` date NOT NULL, `00` int(10) NOT NULL default '0', `01` int(10) NOT NULL default '0', `02` int(10) NOT NULL default '0', `03` int(10) NOT NULL default '0', `04` int(10) NOT NULL default '0', `05` int(10) NOT NULL default '0', `06` int(10) NOT NULL default '0', `07` int(10) NOT NULL default '0', `08` int(10) NOT NULL default '0', `09` int(10) NOT NULL default '0', `10` int(10) NOT NULL default '0', `11` int(10) NOT NULL default '0', `12` int(10) NOT NULL default '0', `13` int(10) NOT NULL default '0', `14` int(10) NOT NULL default '0', `15` int(10) NOT NULL default '0', `16` int(10) NOT NULL default '0', `17` int(10) NOT NULL default '0', `18` int(10) NOT NULL default '0', `19` int(10) NOT NULL default '0', `20` int(10) NOT NULL default '0', `21` int(10) NOT NULL default '0', `22` int(10) NOT NULL default '0', `23` int(10) NOT NULL default '0', `totalcalls` int(10) NOT NULL default '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; As you can see the table has fields of hours of the day and a field called 'totalcalls'. A default of 365 entries are placed in the table for that year already in advance and the module just updates that field for whatever day it currently is. So lets say i placed a call at 22:30, the field 22 would update by 1 and so would total calls. So your job is to make a nice interface for the admin for stats using these tables. Admin should be able to select a running total for the week, month, or year as well as display on a per day to day basis how many calls per hour that day and total amount of calls for that day. This will be one of the pages an admin would look at every single day to project how many users his system can handle, so make it pretty! ------------------------------------------------------------- table sip There are only really 2 tables for creating new users and settting them up with a username and password so they can make calls. Those are the sip and iax tables. They would only ever be used on new signups only or creating very new brand new accounts. ie: their first new accountcode for the system ever. I think creating new users deserves attention so I will explain it abit before continueing on with sip and iax table fields. When you create a new user you are essentially giving them a username and password to make calls on the system by placing an entry in the sip or iax tables or both. The accountcode field in these tables must match up when they create DIDs for their account or their longdistance DID entry access. By default when we create a new account one of 3 things will happen, 1) we insert them into sip table for a new entry and insert them once into the DIDs table with a DID of 'longdistance'. 2) same as 1 except use iax table instead 3) insert into both sip and iax tables with same accountcode and the DIDs table for that default entry. Lets continue with table fields: CREATE TABLE `sip` ( `id` int(11) NOT NULL auto_increment, `name` varchar(80) NOT NULL default '', `accountcode` varchar(20) default NULL, `amaflags` varchar(7) default NULL, `callgroup` varchar(10) default NULL, `callerid` varchar(80) default NULL, `canreinvite` char(3) default 'no', `context` varchar(80) default NULL, `defaultip` varchar(15) default NULL, `dtmfmode` varchar(7) default NULL, `fromuser` varchar(80) default NULL, `fromdomain` varchar(80) default NULL, `host` varchar(31) NOT NULL default '', `insecure` varchar(4) default NULL, `language` char(2) default NULL, `mailbox` varchar(50) default NULL, `md5secret` varchar(80) default NULL, `nat` varchar(5) NOT NULL default 'no', `deny` varchar(95) default NULL, `permit` varchar(95) default NULL, `mask` varchar(95) default NULL, `pickupgroup` varchar(10) default NULL, `port` varchar(5) NOT NULL default '', `qualify` char(3) default NULL, `restrictcid` char(1) default NULL, `rtptimeout` char(3) default NULL, `rtpholdtimeout` char(3) default NULL, `secret` varchar(80) default NULL, `type` varchar(6) NOT NULL default 'friend', `username` varchar(80) NOT NULL default '', `disallow` varchar(100) default 'all', `allow` varchar(100) default 'g729;ilbc;gsm;ulaw;alaw', `musiconhold` varchar(100) default NULL, `regseconds` int(11) NOT NULL default '0', `ipaddr` varchar(15) NOT NULL default '', `regexten` varchar(80) NOT NULL default '', `cancallforward` char(3) default 'yes', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `name_2` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=2 ; There are alot of fields here but we are only concerned with editting 8 or so of them, so I will only describe ones we will be using. name: username to connect to our service with. username: set to same thing as name secret: password to connect to our service with. (these are for actual VOIP phone logins not webinterface) callerid: format is "firstname lastname <2223334444>" without the double quotes. I would default this to what they put as firstname lastname and their phone number upon signup. context: sunsaturn will always be sunsaturn context. host: dynamic or userinputable ip address. this field should contain buttons, one to select dynamic or to select a button that lets them enter a static ip address. Error checking must be done to ensure valid ip address. allow: codec preference list to be given. the codecs are "g729 , ulaw, gsm, alaw" and should allow end user to pick their codec order they want. Once they have done this you insert into the allow field in the format "g729;gsm;ulaw;alaw" in order they selected without the double quotes. accountcode: crucial to make sure unique accountcode is set for this new account. Also to delete their account with it. No 2 users will have same accountcode in this table. Thats it for new signup forms. For admin however editting a users sip account, it should allow editting of all fields defaulting to whatever it already contains for editting. -------------------------------------------------------------- iax table CREATE TABLE `iax` ( `name` varchar(30) NOT NULL, `username` varchar(30) default NULL, `type` varchar(6) NOT NULL, `secret` varchar(50) default NULL, `md5secret` varchar(32) default NULL, `dbsecret` varchar(100) default NULL, `notransfer` varchar(10) default NULL, `inkeys` varchar(100) default NULL, `auth` varchar(100) default NULL, `accountcode` varchar(100) default NULL, `amaflags` varchar(100) default NULL, `callerid` varchar(100) default NULL, `context` varchar(100) default NULL, `defaultip` varchar(15) default NULL, `host` varchar(31) NOT NULL default 'dynamic', `language` char(5) default NULL, `mailbox` varchar(50) default NULL, `deny` varchar(95) default NULL, `permit` varchar(95) default NULL, `qualify` varchar(4) default NULL, `disallow` varchar(100) default NULL, `allow` varchar(100) default NULL, `ipaddr` varchar(15) default NULL, `port` int(11) default '0', `regseconds` int(11) default '0', PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Iax fields no different than sip table for new signups. Use exact same fields and allow editting of all for admin. --------------------------------------------------------------- Table voicemail. This is very simple table. This table just adds voicemail to a sip/iax account. Only time this would need an entry added to it, is when they purchase a DID and want voicemail with it. Here is breakdown... CREATE TABLE `voicemail` ( `uniqueid` int(11) NOT NULL auto_increment, `customer_id` varchar(11) NOT NULL default '0', `context` varchar(50) NOT NULL default 'default', `mailbox` varchar(11) NOT NULL default '0', `password` varchar(5) NOT NULL default '0', `fullname` varchar(150) NOT NULL default '', `email` varchar(50) NOT NULL default '', `pager` varchar(50) NOT NULL default '', `tz` varchar(10) NOT NULL default 'central', `attach` varchar(4) NOT NULL default 'yes', `saycid` varchar(4) NOT NULL default 'yes', `dialout` varchar(10) NOT NULL default '', `callback` varchar(10) NOT NULL default '', `review` varchar(4) NOT NULL default 'no', `operator` varchar(4) NOT NULL default 'no', `envelope` varchar(4) NOT NULL default 'no', `sayduration` varchar(4) NOT NULL default 'no', `saydurationm` tinyint(4) NOT NULL default '1', `sendvoicemail` varchar(4) NOT NULL default 'no', `delete` varchar(4) NOT NULL default 'no', `nextaftercmd` varchar(4) NOT NULL default 'yes', `forcename` varchar(4) NOT NULL default 'no', `forcegreetings` varchar(4) NOT NULL default 'no', `hidefromdir` varchar(4) NOT NULL default 'yes', `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`uniqueid`), KEY `mailbox_context` (`mailbox`,`context`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; So lets say they bought the DID 2045559999 and want voicemail added to it. All you have to do is insert their accountcode into customer_id field, their DID of 2045559999 into mailbox field, and some numeric password into password field if they want a password. Error checking on userinput of password to make sure it is numeric with a maxlength of 5. ---------------------------------------------------------------- table extensions this table is used to control dialplan flow for users. although it will be used down the road, I don't see it being used on this project right away...but here is breakdown, just don;t worry about this one for now, when time comes for it I will explain it. CREATE TABLE `extensions` ( `id` int(11) NOT NULL auto_increment, `context` varchar(20) NOT NULL default '', `exten` varchar(20) NOT NULL default '', `priority` tinyint(4) NOT NULL default '0', `app` varchar(20) NOT NULL default '', `appdata` varchar(255) NOT NULL default '', KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; That is the complete breakdown of the most important tables. You are free to add existing fields to those tables but again are not to change table names or field names from those tables. You are free to create new tables to associate accountcode with some nice web user login/password interface, or whatever you choose. There are other tables in this voip database but they were for project that never got completed, just leave them and make new ones for now. Now read webinterface.txt so we can pull all these tables together to make this project come together.