phpBMS

Show
Ignore:
Timestamp:
04/07/09 11:44:18 (3 years ago)
Author:
nate
Message:
  • Merged Nathan branch back into trunk.
Files:
1 modified

Legend:

Unmodified
Added
Removed

  • trunk/phpbms/modules/bms/install/tablecolumns.sql

    r442 r485  
    44INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('2', 'type', 'clients.type', 'left', '', '0', '', '0', '', NULL, '0'); 
    55INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('2', 'phone', 'IF(clients.workphone != \'\' OR clients.homephone != \'\' OR clients.mobilephone != \'\' OR clients.otherphone != \'\',IF(clients.workphone != \'\', concat(clients.workphone, \' (w)\'), IF(clients.homephone != \'\', concat(clients.homephone, \' (h)\'), IF(clients.mobilephone != \'\', concat(clients.mobilephone, \' (m)\'), IF(clients.otherphone != \'\', concat(clients.otherphone, \' (o)\'), \'\')))) ,\'\')', 'left', '', '4', 'concat(clients.workphone, clients.homephone, clients.mobilephone,clients.otherphone)', '0', '', NULL, '0'); 
    6 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (3,'id','invoices.id','left','',0,'',0,'',NULL,0); 
    7 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (3,'type','invoices.type','left','',2,'',0,'',NULL,0); 
    8 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (3,'status','invoicestatuses.name','left','',3,'',0,'',NULL,0); 
    9 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (3,'date','if(invoices.type=\"Invoice\",invoices.invoicedate,invoices.orderdate)','left','',5,'',0,'','date',0); 
    10 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (3,'client name / company','concat(\"<strong>\",if(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company),\"</strong>\")','left','',6,NULL,0,'100%','noencoding',0); 
    11 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (3,'total','if(invoices.type!=\"VOID\",invoices.totalti,\"-----\")','right','sum(if(invoices.type!=\"VOID\",invoices.totalti,0))',8,'if(invoices.type!=\"VOID\",invoices.totalti,0)',0,'','currency',0); 
    12 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (3,'due','if(invoices.type!=\"VOID\",invoices.totalti-invoices.amountpaid,\"-----\")','right','sum(if(invoices.type!=\"VOID\",(invoices.totalti-invoices.amountpaid),0))',9,'if(invoices.type!=\"VOID\",invoices.totalti-invoices.amountpaid,0)',0,'','currency',0); 
    13 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (3,'web','invoices.weborder','center','',4,'',0,'','boolean',0); 
    14 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (3,'RTP','invoices.readytopost','center','',1,'',0,'','boolean',0); 
    15 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (3,'payment','paymentmethods.name','left','',7,'',0,'',NULL,0); 
    16 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (4,'part number','products.partnumber','left','',0,'',0,'',NULL,0); 
    17 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (4,'name','products.partname','left','',1,'',1,'100%',NULL,0); 
    18 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (4,'status','products.status','left','',3,'',0,'',NULL,0); 
    19 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (4,'unit price','products.unitprice','right','',4,'',0,'','currency',0); 
    20 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (4,'type','products.type','left','',2,'',0,'',NULL,0); 
    21 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'id','lineitems.id','left','',0,'',0,'',NULL,0); 
    22 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'invoice id','lineitems.invoiceid','left','',1,'',0,'',NULL,0); 
    23 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'part #','products.partnumber','left','',4,'',0,'',NULL,0); 
    24 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'name','products.partname','left','',5,'',1,'100%',NULL,0); 
    25 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'unit price','lineitems.unitprice','right','sum(lineitems.unitprice)',6,'lineitems.unitprice',0,'','currency',0); 
    26 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'qty','format(lineitems.quantity,2)','center','format(sum(lineitems.quantity),2)',7,'lineitemd.quantity',0,'',NULL,0); 
    27 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'price ext.','lineitems.unitprice*lineitems.quantity','right','sum(lineitems.unitprice*lineitems.quantity)',8,'',0,'','currency',0); 
    28 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'cost ext.','lineitems.unitcost*lineitems.quantity','right','sum(lineitems.unitcost*lineitems.quantity)',9,'',0,'','currency',0); 
    29 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'total wt.','format((lineitems.unitweight*lineitems.quantity),2)','right','format(sum(lineitems.unitweight*lineitems.quantity),2)',11,'lineitems.unitweight*lineitems.quantity',0,'',NULL,0); 
    30 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'type','invoices.type','left','',2,'',0,'',NULL,0); 
    31 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'date','if(invoices.type=\"Invoice\",invoices.invoicedate,invoices.orderdate)\r\n','left','',3,'',1,'','date',0); 
    32 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (5,'tax','lineitems.taxable','center','',10,'',0,'','boolean',0); 
    33 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (6,'id','tax.id','center','',0,'',0,'',NULL,0); 
    34 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (6,'name','concat(\"<strong>\",tax.name,\"</strong>\")','left','',1,NULL,0,'','noencoding',0); 
    35 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (6,'percentage','concat(tax.percentage,\"%\")','left','',2,'tax.percentage',0,'98%',NULL,0); 
    36 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (7,'id','productcategories.id','center','',0,'',0,'',NULL,0); 
    37 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (7,'name','productcategories.name','left','',1,'',0,'100%',NULL,0); 
    38 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (7,'date created','productcategories.creationdate','left','',2,'productcategories.creationdate',0,'','datetime',0); 
    39 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (8,'name','childproducts.partname','left','',1,'',1,'100%',NULL,0); 
    40 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (8,'part number','childproducts.partnumber','left','',0,'',0,'',NULL,0); 
     6INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('3', 'id', 'invoices.id', 'left', '', '0', '', '0', '', NULL, '0'); 
     7INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('3', 'type', 'invoices.type', 'left', '', '2', '', '0', '', NULL, '0'); 
     8INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('3', 'status', 'invoicestatuses.name', 'left', '', '3', '', '0', '', NULL, '0'); 
     9INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('3', 'date', 'if(invoices.type=\"Invoice\",invoices.invoicedate,invoices.orderdate)', 'left', '', '5', '', '0', '', 'date', '0'); 
     10INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('3', 'client name / company', 'concat(\"<strong>\",if(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company),\"</strong>\")', 'left', '', '6', NULL, '0', '100%', 'noencoding', '0'); 
     11INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('3', 'total', 'if(invoices.type!=\"VOID\",invoices.totalti,\"-----\")', 'right', 'sum(if(invoices.type!=\"VOID\",invoices.totalti,0))', '8', 'if(invoices.type!=\"VOID\",invoices.totalti,0)', '0', '', 'currency', '0'); 
     12INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('3', 'due', 'if(invoices.type!=\"VOID\",invoices.totalti-invoices.amountpaid,\"-----\")', 'right', 'sum(if(invoices.type!=\"VOID\",(invoices.totalti-invoices.amountpaid),0))', '9', 'if(invoices.type!=\"VOID\",invoices.totalti-invoices.amountpaid,0)', '0', '', 'currency', '0'); 
     13INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('3', 'web', 'invoices.weborder', 'center', '', '4', '', '0', '', 'boolean', '0'); 
     14INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('3', 'RTP', 'invoices.readytopost', 'center', '', '1', '', '0', '', 'boolean', '0'); 
     15INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('3', 'payment', 'paymentmethods.name', 'left', '', '7', '', '0', '', NULL, '0'); 
     16INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('4', 'part number', 'products.partnumber', 'left', '', '0', '', '0', '', NULL, '0'); 
     17INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('4', 'name', 'products.partname', 'left', '', '1', '', '1', '100%', NULL, '0'); 
     18INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('4', 'status', 'products.status', 'left', '', '3', '', '0', '', NULL, '0'); 
     19INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('4', 'unit price', 'products.unitprice', 'right', '', '4', '', '0', '', 'currency', '0'); 
     20INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('4', 'type', 'products.type', 'left', '', '2', '', '0', '', NULL, '0'); 
     21INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'id', 'lineitems.id', 'left', '', '0', '', '0', '', NULL, '0'); 
     22INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'invoice id', 'lineitems.invoiceid', 'left', '', '1', '', '0', '', NULL, '0'); 
     23INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'part #', 'products.partnumber', 'left', '', '4', '', '0', '', NULL, '0'); 
     24INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'name', 'products.partname', 'left', '', '5', '', '1', '100%', NULL, '0'); 
     25INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'unit price', 'lineitems.unitprice', 'right', 'sum(lineitems.unitprice)', '6', 'lineitems.unitprice', '0', '', 'currency', '0'); 
     26INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'qty', 'format(lineitems.quantity,2)', 'center', 'format(sum(lineitems.quantity),2)', '7', 'lineitemd.quantity', '0', '', NULL, '0'); 
     27INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'price ext.', 'lineitems.unitprice*lineitems.quantity', 'right', 'sum(lineitems.unitprice*lineitems.quantity)', '8', '', '0', '', 'currency', '0'); 
     28INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'cost ext.', 'lineitems.unitcost*lineitems.quantity', 'right', 'sum(lineitems.unitcost*lineitems.quantity)', '9', '', '0', '', 'currency', '0'); 
     29INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'total wt.', 'format((lineitems.unitweight*lineitems.quantity),2)', 'right', 'format(sum(lineitems.unitweight*lineitems.quantity),2)', '11', 'lineitems.unitweight*lineitems.quantity', '0', '', NULL, '0'); 
     30INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'type', 'invoices.type', 'left', '', '2', '', '0', '', NULL, '0'); 
     31INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'date', 'if(invoices.type=\"Invoice\",invoices.invoicedate,invoices.orderdate)\r\n', 'left', '', '3', '', '1', '', 'date', '0'); 
     32INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('5', 'tax', 'lineitems.taxable', 'center', '', '10', '', '0', '', 'boolean', '0'); 
     33INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('6', 'id', 'tax.id', 'center', '', '0', '', '0', '', NULL, '0'); 
     34INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('6', 'name', 'concat(\"<strong>\",tax.name,\"</strong>\")', 'left', '', '1', NULL, '0', '', 'noencoding', '0'); 
     35INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('6', 'percentage', 'concat(tax.percentage,\"%\")', 'left', '', '2', 'tax.percentage', '0', '98%', NULL, '0'); 
     36INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('7', 'id', 'productcategories.id', 'center', '', '0', '', '0', '', NULL, '0'); 
     37INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('7', 'name', 'productcategories.name', 'left', '', '1', '', '0', '100%', NULL, '0'); 
     38INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('7', 'date created', 'productcategories.creationdate', 'left', '', '2', 'productcategories.creationdate', '0', '', 'datetime', '0'); 
     39INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('8', 'name', 'childproducts.partname', 'left', '', '1', '', '1', '100%', NULL, '0'); 
     40INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('8', 'part number', 'childproducts.partnumber', 'left', '', '0', '', '0', '', NULL, '0'); 
    4141INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('18', 'id', 'clients.id', 'center', '', '0', '', '0', '', NULL, '0'); 
    4242INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('18', 'type', 'clients.type', 'left', '', '1', '', '0', '', NULL, '0'); 
    4343INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('18', 'name', 'IF(clients.company != \'\', CONCAT(clients.company,IF(clients.lastname != \'\' OR clients.firstname != \'\', CONCAT(\' (\',if(clients.lastname != \'\', clients.lastname, \'{blank}\'),\', \',if(clients.firstname != \'\', clients.firstname, \'{blank}\'),\')\'), \'\')), IF(clients.lastname != \'\' OR clients.firstname != \'\', CONCAT(if(clients.lastname != \'\', clients.lastname, \'{blank}\'),\', \',if(clients.firstname != \'\', clients.firstname, \'{blank}\')), \'\'))', 'left', '', '2', 'concat(clients.company,clients.lastname,clients.firstname)', '1', '100%', NULL, '0'); 
    44 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (22,'name','clientemailprojects.name','left','',0,'',0,'100%',NULL,0); 
    45 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (22,'last run','clientemailprojects.lastrun','right','',1,'',0,'','datetime',0); 
    46 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (25,'amount/percent','discounts.value','right','',2,'',0,'',NULL,0); 
    47 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (25,'type','discounts.type','left','',1,'',0,'',NULL,0); 
    48 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (25,'name','discounts.name','left','',0,'',0,'100%',NULL,0); 
    49 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (300,'name','shippingmethods.name','left','',1,'',0,'99%',NULL,0); 
    50 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (300,'estimate','shippingmethods.canestimate','center','',2,'',0,'','boolean',0); 
    51 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (300,'priority','shippingmethods.priority','right','',0,'',0,'',NULL,0); 
    52 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (301,'name','paymentmethods.name','left','',1,'',0,'99%',NULL,0); 
    53 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (301,'type','paymentmethods.type','left','',2,'',0,'',NULL,0); 
    54 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (301,'id','paymentmethods.id','center','',0,'',0,'',NULL,0); 
    55 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (301,'priority','paymentmethods.priority','center','',3,'',0,'',NULL,0); 
    56 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (301,'online process','paymentmethods.onlineprocess','center','',4,'',0,'','boolean',0); 
    57 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (302,'name','invoicestatuses.name','left','',0,'',1,'99%',NULL,0); 
    58 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (302,'priority','invoicestatuses.priority','right','',1,'',0,'',NULL,0); 
    59 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (302,'inactive','invoicestatuses.inactive','center','',2,'',0,'','boolean',0); 
    60 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (303,'type','aritems.type','left','',1,'',0,'',NULL,0); 
    61 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (303,'date','aritems.itemdate','left','',3,'',0,'','date',0); 
    62 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (303,'client','if(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company)','left','',4,'',1,'100%',NULL,0); 
    63 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (303,'doc amt','aritems.amount','right','SUM(aritems.amount)',5,'',0,'','currency',0); 
    64 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (303,'amt due','aritems.amount-aritems.paid','right','SUM(aritems.amount-aritems.paid)',6,'',0,'','currency',0); 
    65 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (303,'doc ref','aritems.relatedid','left','',2,'',0,'',NULL,0); 
    66 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (303,'status','aritems.status','left','',0,'',0,'',NULL,0); 
    67 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (304,'id','receipts.id','left','',0,'',0,'',NULL,0); 
    68 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (304,'client','if(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company)','left','',4,'',1,'100%',NULL,0); 
    69 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (304,'RTP','receipts.readytopost','center','',1,'',0,'','boolean',0); 
    70 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (304,'amount','receipts.amount','right','sum(receipts.amount)',6,'',0,'','currency',0); 
    71 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (304,'payment','IF(receipts.paymentmethodid = -1,concat( concat("Other... (", receipts.paymentother), ")"), paymentmethods.name)','left','',5,'',0,'',NULL,0); 
    72 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (304,'date','receipts.receiptdate','left','',3,'',0,'','date',0); 
    73 INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES (304,'status','receipts.status','left','',2,'',0,'',NULL,0); 
     44INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('22', 'name', 'clientemailprojects.name', 'left', '', '0', '', '0', '100%', NULL, '0'); 
     45INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('22', 'last run', 'clientemailprojects.lastrun', 'right', '', '1', '', '0', '', 'datetime', '0'); 
     46INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('25', 'amount/percent', 'discounts.value', 'right', '', '2', '', '0', '', NULL, '0'); 
     47INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('25', 'type', 'discounts.type', 'left', '', '1', '', '0', '', NULL, '0'); 
     48INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('25', 'name', 'discounts.name', 'left', '', '0', '', '0', '100%', NULL, '0'); 
     49INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('300', 'name', 'shippingmethods.name', 'left', '', '1', '', '0', '99%', NULL, '0'); 
     50INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('300', 'estimate', 'shippingmethods.canestimate', 'center', '', '2', '', '0', '', 'boolean', '0'); 
     51INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('300', 'priority', 'shippingmethods.priority', 'right', '', '0', '', '0', '', NULL, '0'); 
     52INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('301', 'name', 'paymentmethods.name', 'left', '', '1', '', '0', '99%', NULL, '0'); 
     53INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('301', 'type', 'paymentmethods.type', 'left', '', '2', '', '0', '', NULL, '0'); 
     54INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('301', 'id', 'paymentmethods.id', 'center', '', '0', '', '0', '', NULL, '0'); 
     55INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('301', 'priority', 'paymentmethods.priority', 'center', '', '3', '', '0', '', NULL, '0'); 
     56INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('301', 'online process', 'paymentmethods.onlineprocess', 'center', '', '4', '', '0', '', 'boolean', '0'); 
     57INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('302', 'name', 'invoicestatuses.name', 'left', '', '0', '', '1', '99%', NULL, '0'); 
     58INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('302', 'priority', 'invoicestatuses.priority', 'right', '', '1', '', '0', '', NULL, '0'); 
     59INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('302', 'inactive', 'invoicestatuses.inactive', 'center', '', '2', '', '0', '', 'boolean', '0'); 
     60INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('303', 'type', 'aritems.type', 'left', '', '1', '', '0', '', NULL, '0'); 
     61INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('303', 'date', 'aritems.itemdate', 'left', '', '3', '', '0', '', 'date', '0'); 
     62INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('303', 'client', 'if(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company)', 'left', '', '4', '', '1', '100%', NULL, '0'); 
     63INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('303', 'doc amt', 'aritems.amount', 'right', 'SUM(aritems.amount)', '5', '', '0', '', 'currency', '0'); 
     64INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('303', 'amt due', 'aritems.amount-aritems.paid', 'right', 'SUM(aritems.amount-aritems.paid)', '6', '', '0', '', 'currency', '0'); 
     65INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('303', 'doc ref', 'aritems.relatedid', 'left', '', '2', '', '0', '', NULL, '0'); 
     66INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('303', 'status', 'aritems.status', 'left', '', '0', '', '0', '', NULL, '0'); 
     67INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('304', 'id', 'receipts.id', 'left', '', '0', '', '0', '', NULL, '0'); 
     68INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('304', 'client', 'if(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company)', 'left', '', '4', '', '1', '100%', NULL, '0'); 
     69INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('304', 'RTP', 'receipts.readytopost', 'center', '', '1', '', '0', '', 'boolean', '0'); 
     70INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('304', 'amount', 'receipts.amount', 'right', 'sum(receipts.amount)', '6', '', '0', '', 'currency', '0'); 
     71INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('304', 'payment', 'IF(receipts.paymentmethodid = -1,concat( concat(\"Other... (\", receipts.paymentother), \")\"), paymentmethods.name)', 'left', '', '5', '', '0', '', NULL, '0'); 
     72INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('304', 'date', 'receipts.receiptdate', 'left', '', '3', '', '0', '', 'date', '0'); 
     73INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('304', 'status', 'receipts.status', 'left', '', '2', '', '0', '', NULL, '0'); 
    7474INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('305', 'address', 'concat(if(addresses.title != \'\',concat(\'[b]\',addresses.title,\'[/b][br]\'),\'\'),IF(addresses.address1!=\'\',addresses.address1,\'\'),if(addresses.address2 != \'\',concat(\'[br]\', addresses.address2),\'\'),if(addresses.city != \'\',concat(\'[br]\',addresses.city,\', \',if(addresses.state != \'\',addresses.state, \'\'),\' \',if(addresses.postalcode != \'\', addresses.postalcode, \'\')),\'\'),if(addresses.country != \'\',concat(\'[br]\',addresses.country),\'\'))', 'left', '', '0', '', '1', '100%', 'bbcode', '0'); 
    7575INSERT INTO `tablecolumns` (`tabledefid`, `name`, `column`, `align`, `footerquery`, `displayorder`, `sortorder`, `wrap`, `size`, `format`, `roleid`) VALUES ('305', 'default ship to', 'addresstorecord.defaultshipto', 'center', '', '4', '', '0', '', 'boolean', '0'); 
phpBMS vulnerability assesment provided by Orvant Inc. Copyright © 2010 Kreotek, LLC. All Rights reserved.