db

SQL statements

Tables: * = creusr, creda, updusr, updda

cpl (id, code, description, remark, prn, valid, subtot, *)
inv (id, sup_id, description, amount, currency, bkamount, decr, docseq, docdate, remark, prn, *)
ixc (id, inv_id, year, amount, cpl_code, remark, *)
sup (id, name, description, lastcurrency, subcount, adr_id, extid, lastcpl, mostcpl, prn, *)

cpl - inv - ixc - sup

cpl costcode (orig. costplace)

Field Type Attributes Null Default Extra purpose
int(10) No auto_increment record id
varchar(10) No costcode (short form human read.)
varchar(30) No use of the code
varchar(30) No extra remark about it
char(1) No to print switch
char(1) No valid switch: not valid=archive?
double(16,4) No 0 saldo this bookyear
varchar(10) No created by
datetime No 0000-00-00 00:00:00 created on
varchar(10) No changed by
datetime No 0000-00-00 00:00:00 changed on

cpl - inv - ixc - sup

inv Incoming invoices

Field Type Attributes Null Default Extra purpose
int(10) No auto_increment record id
int(11) No 0 supplier id
varchar(30) No what is invoiced?
decimal(16,4) No 0 netto amount
varchar(5) No as on invoice
decimal(16,4) No 0.0000 amount in “home” currency
char(1) No debit - or credit +
varchar(10) No incoming serial nr
date No 0000-00-00 invoice date
varchar(10) No free to use
bigint(20) No some number or counter value
char(1) No print switch on:off
varchar(10) No created by
datetime No 0000-00-00 00:00:00 created on
varchar(10) No changed by
datetime No 0000-00-00 00:00:00 changed on
char(1) No invoice booked, payed, …

cpl - inv - ixc - sup

ixc invoice-amount assigned to costcode

Field Type Attributes Null Default Extra purpose
int(11) No auto_increment record id
int(11) No 0 invoice
varchar(4) No year for cost
decimal(16,4) No 0 cost amount
varchar(10) No costplace
varchar(80) No
varchar(10) No
datetime No 0000-00-00 00:00:00
varchar(10) No
datetime No 0000-00-00 00:00:00

cpl - inv - ixc - sup

sup Supplier Invoice data

Field Type Attributes Null Default Extra purpose
int(10) No auto_increment record id
varchar(30) No supplier name
varchar(30) No mostly product(s) supplier is about
varchar(5) No curency used last time
int(10) No 0 saldo cost year
varchar(10) No pointer to address record
varchar(10) No pointer to external supplier record, probably where imported from.
varchar(10) No last used costcode
varchar(10) No most used costcode
varchar(30) No standard invoice description (1)
varchar(10) No
datetime No 0000-00-00 00:00:00
varchar(10) No
datetime No 0000-00-00 00:00:00
char(1) No

(1) sup_invdescription is added from version 0.2.0 on!

cpl - inv - ixc - sup

Note: from version 0.0.89 on, the table structure can be made from within the facs program, menu File, Start New.

Other possibility is bij using the sql file “facstables.sql” distributed in the project source.

Previous versions: cut and past from (printed) sql textfile:

Knip hier en plak in Kate of andere editor:
(en bewaar als facstables.sql) of, als dat problemen geeft met geknipte html tekens, download hier

# phpMyAdmin SQL Dump
# version 2.5.3
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Jan 21, 2009 at 01:10 PM
# Server version: 4.0.15
# PHP Version: 4.3.3
#
# Database : `simplefacs`
#

# ——————————————————–

#
# Table structure for table `cpl`
#

DROP TABLE IF EXISTS `cpl`;
CREATE TABLE `cpl` (
`cpl_id` int(10) unsigned NOT NULL auto_increment,
`cpl_code` varchar(10) NOT NULL default ”,
`cpl_description` varchar(30) NOT NULL default ”,
`cpl_remark` varchar(30) NOT NULL default ”,
`cpl_prn` char(1) NOT NULL default ”,
`cpl_valid` char(1) NOT NULL default ”,
`cpl_subtot` double(16,4) NOT NULL default ‘0.0000′,
`cpl_creusr` varchar(10) NOT NULL default ”,
`cpl_creda` datetime NOT NULL default ‘0000-00-00 00:00:00′,
`cpl_updusr` varchar(10) NOT NULL default ”,
`cpl_updda` datetime NOT NULL default ‘0000-00-00 00:00:00′,
PRIMARY KEY (`cpl_id`),
UNIQUE KEY `cpl_code` (`cpl_code`)
) TYPE=MyISAM COMMENT=’costplace’ AUTO_INCREMENT=1 ;

# ——————————————————–

#
# Table structure for table `inv`
#

DROP TABLE IF EXISTS `inv`;
CREATE TABLE `inv` (
`inv_id` int(10) unsigned NOT NULL auto_increment,
`sup_id` int(11) unsigned NOT NULL default ‘0′,
`inv_description` varchar(30) NOT NULL default ”,
`inv_amount` decimal(16,4) NOT NULL default ‘0.0000′,
`inv_currency` varchar(5) NOT NULL default ”,
`inv_bkamount` decimal(16,4) NOT NULL default ‘0.0000′,
`inv_decr` char(1) NOT NULL default ”,
`inv_docseq` varchar(10) NOT NULL default ”,
`inv_docdate` date NOT NULL default ‘0000-00-00′,
`inv_remark` varchar(10) NOT NULL default ”,
`inv_prn` char(1) NOT NULL default ”,
`inv_creusr` varchar(10) NOT NULL default ”,
`inv_creda` datetime NOT NULL default ‘0000-00-00 00:00:00′,
`inv_updusr` varchar(10) NOT NULL default ”,
`inv_updda` datetime NOT NULL default ‘0000-00-00 00:00:00′,
`inv_ok` char(1) NOT NULL default ”,
PRIMARY KEY (`inv_id`)
) TYPE=MyISAM COMMENT=’Incoming invoices’ AUTO_INCREMENT=1 ;

# ——————————————————–

#
# Table structure for table `ixc`
#

DROP TABLE IF EXISTS `ixc`;
CREATE TABLE `ixc` (
`ixc_id` int(11) NOT NULL auto_increment,
`inv_id` int(11) NOT NULL default ‘0′,
`ixc_year` varchar(4) NOT NULL default ”,
`ixc_amount` double(16,4) NOT NULL default ‘0.0000′,
`cpl_code` varchar(10) NOT NULL default ”,
`ixc_remark` varchar(80) NOT NULL default ”,
`ixc_creusr` varchar(10) NOT NULL default ”,
`ixc_creda` datetime NOT NULL default ‘0000-00-00 00:00:00′,
`ixc_updusr` varchar(10) NOT NULL default ”,
`ixc_updda` datetime NOT NULL default ‘0000-00-00 00:00:00′,
PRIMARY KEY (`ixc_id`,`ixc_id`)
) TYPE=MyISAM COMMENT=’invoiceamount assigned to costcenter’ AUTO_INCREMENT=1 ;

# ——————————————————–

#
# Table structure for table `sup`
#

DROP TABLE IF EXISTS `sup`;
CREATE TABLE `sup` (
`sup_id` int(10) NOT NULL auto_increment,
`sup_name` varchar(30) NOT NULL default ”,
`sup_description` varchar(30) NOT NULL default ”,
`sup_lastcurrency` varchar(5) NOT NULL default ”,
`sup_subcount` int(10) NOT NULL default ‘0′,
`adr_id` varchar(10) NOT NULL default ”,
`sup_extid` varchar(10) NOT NULL default ”,
`sup_lastcpl` varchar(10) NOT NULL default ”,
`sup_mostcpl` varchar(10) NOT NULL default ”,
`sup_creusr` varchar(10) NOT NULL default ”,
`sup_creda` datetime NOT NULL default ‘0000-00-00 00:00:00′,
`sup_updusr` varchar(10) NOT NULL default ”,
`sup_updda` datetime NOT NULL default ‘0000-00-00 00:00:00′,
`sup_prn` char(1) NOT NULL default ”,
PRIMARY KEY (`sup_id`)
) TYPE=MyISAM COMMENT=’Supplier Invoice data’ AUTO_INCREMENT=1 ;

Importeer, vb op server met MySql 5 en phpMyAdmin:
- create table cosy
- Import, File to import: cosy.sql, utf8 (?), SQL compatible: none (of MySQL 4.x ?)
- resultaat (hopelijk): Import has been successfully finished, 8 queries executed.: 4 lege tabellen.

Gambas, open project facs, kies “modData”, vul in onder PUBLIC SUB invConnect():
WITH $hConInv
.Type = “mysql”
.Host = “localhost”
.Login = “gambasapp”
.Password = “facs2test”
.Name = “cosy”
END WITH

En indien nog niet gebeurd, in phpMyAdmin:
Database, Add new user, gambasapp, password invullen. (in ons vb facs2test)
met als resultaat:

CREATE USER 'gambasapp'@'%' IDENTIFIED BY '*********';

Geef rechten (phpMyAdmin geeft dat zo weer:)

GRANT USAGE ON * . * TO ‘gambasapp’@'%’ IDENTIFIED BY ‘*********’ WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

Of in MySQL prompt:

GRANT USAGE ON cosy.* TO ‘gambasapp’@'%’ IDENTIFIED BY ‘*********’ WITH GRANT OPTION;

En dan in phpMyAdmin:
User ‘gambasapp’@'%’ : Edit Privileges, Database-specific privileges

GRANT ALL PRIVILEGES ON `cosy` . * TO 'gambasapp'@'%' WITH GRANT OPTION ;

(die grant option is waarschijnlijk niet nodig)

Je kan de tabellen dan aanmaken vanuit mysql door bovenstaande sql code in tekstfiles.sql te bewaren en uit te voeren, of ze in phpMyAdmin plakken:

Your SQL query has been executed successfully

zou het antwoord moeten zijn

(anders moet je de syntax checken van de geplakte tekst; tekens die veranderd zijn door het knippen uit een webpagina bv).

Daarna kan je nog voor elke tabe de unicode instelling wijzigen, bv voor cpl:

ALTER TABLE `cpl` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

Primary Key

Set primary key to :
cpl : cpl_id
inv : inv_id
ixc : ixc_id
sup : sup_id

Top - cpl - inv - ixc - sup


Comments

Comments are closed.