transactions - ZeyOS Schema Documentation

Details

Status codes

Status	Activity	ID	Color	Quotes (0)	Orders (1)	Deliveries (2)	Invoices (3)	Credits (4)
Draft	DRAFT	0	green	X	X	X	X	X
Booked	OPEN	1	green	X	X	X	X	X
Held (On Hold)	OPEN	2	orange	X	X	X	X	X
Cancelled	CANCELLED	3	red	X	X	X	X	X
Closed	CLOSED	4	black	X	X	X	X	X
Partly Ordered	OPEN	5	orange	X	-	-	-	-
Partly Ordered / Cancelled	CANCELLED	6	red	X	-	-	-	-
Partly Ordered / Closed	CLOSED	7	black	X	-	-	-	-
Ordered	CLOSED	8	black	X	-	-	-	-
Partly Delivered	OPEN	9	orange	-	X	-	-	-
Partly Delivered / Cancelled	CANCELLED	10	red	-	X	-	-	-
Partly Delivered / Closed	CLOSED	11	black	-	X	-	-	-
Delivered	CLOSED	12	black	-	X	-	-	-
Partly Invoiced	OPEN	13	orange	-	X	X	-	-
Partly Invoiced / Cancelled	CANCELLED	14	red	-	X	X	-	-
Partly Invoiced / Closed	CLOSED	15	black	-	X	X	-	-
Invoiced	CLOSED	16	black	-	X	X	-	-
Partly Paid	OPEN	17	orange	-	-	-	X	X
Partly Paid / Cancelled	CANCELLED	18	red	-	-	-	X	X
Partly Paid / Closed	CLOSED	19	black	-	-	-	X	X
Paid	CLOSED	20	black	-	-	-	X	X
Overpaid	CLOSED	21	black	-	-	-	X	X

Transaction Items

Read and Write

Every once in a while, you might want to take a closer look at the items involved in the transactions at hand. You can do so by retrieving them in a db:select and then decoding them from JSON into an associative array with decode:json like this:

<db:select var_result="transaction" type="self">
    <db:fields>
        <db:field>items</db:field>
    </db:fields>
    <db:table>transactions</db:table>
    <db:is field="transactionnum">L.1409.1234</db:is>
</db:select>

<decode:json var="transitems">$transaction.items</decode:json>

Conversely, when you create a transaction, you can initialize the items with transitems and encode them with encode:json before you write them into the database:

<transitems var="transitems" />

<encode:json var="transitems" var_result="jsonItems" />

<db:insert table="transactions">
    ...
    <db:data field="items">$jsonItems</db:data>
</db:insert>

Structure

Transaction items are stored as an array of objects (like a list of products). A product has the following basic properties:

Property	Description
type	0 for line item, 1 for text
original	The original (previous) transaction
subindex	The subindex of that transaction
references	Further references to other transactions (siblings and descendants)

Each of the references has two properties:

Property	Description
transaction	The referenced transaction
subindex	The subindex of the transaction

If the line is just text, its properties can be found here:

Property	Description
variant	The variant of the text entry (description, annotation, subtitle, title, header)
text	The subindex of the transaction

There are further properties that describe the product:

Property	Description
name	Name of the product
manufacturer	Manufacturer
itemnum	Item number
barcode	Bar code / EAN
itemtype	Item type
unit	Unit of measure
amount	Amount
amounttaken	Amount taken
sellingprice	Selling price
purchaseprice	Purchase price
rebate	Rebate
discount	Discount
discount2	Second discount
taxrate	Tax rate
weight	Weight
item	ID of the item
reservation	ID of the reservation stock transaction (only for active orders)
transactions	Transactions array

The transactions array looks as follows:

Property	Description
transaction	ID of the stock transaction
storage	ID of the storage
amount	Amount
chargenum	Charge number
location	Stock location
serials	Serial

The JSON of a full-blown items field looks somewhat like this:


    [{
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 4,
            "text" : "Header-Test"
        }, {
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 3,
            "text" : "Title Test"
        }, {
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 2,
            "text" : "Subtitle Test"
        }, {
            "type" : 0,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "name" : "Testartikel",
            "manufacturer" : "Ich",
            "itemnum" : "18929537",
            "barcode" : "2541235256262626233",
            "itemtype" : 1,
            "unit" : "C62",
            "amount" : 23,
            "amounttaken" : 0,
            "sellingprice" : 50,
            "purchaseprice" : 40,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 19,
            "weight" : 2,
            "item" : 1,
            "reservation" : null,
            "transactions" : []
        }, {
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 0,
            "text" : "Description Test"
        }, {
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 2,
            "text" : "Subtitle Test 2"
        }, {
            "type" : 0,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "name" : "Serienartikel",
            "manufacturer" : "FACTORY INC.",
            "itemnum" : "ARTNUM12345",
            "barcode" : "165749875341",
            "itemtype" : 1,
            "unit" : "C62",
            "amount" : 2,
            "amounttaken" : 0,
            "sellingprice" : 0,
            "purchaseprice" : 0,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 0,
            "weight" : 0,
            "item" : 4,
            "reservation" : null,
            "transactions" : []
        }, {
            "type" : 0,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "name" : "Testartikel",
            "manufacturer" : "Ich",
            "itemnum" : "18929537",
            "barcode" : "2541235256262626233",
            "itemtype" : 1,
            "unit" : "C62",
            "amount" : 12,
            "amounttaken" : 0,
            "sellingprice" : 50,
            "purchaseprice" : 40,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 19,
            "weight" : 2,
            "item" : 1,
            "reservation" : null,
            "transactions" : []
        }, {
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 0,
            "text" : "Description Test 2"
        }, {
            "type" : 0,
            "original" : 46,
            "subindex" : 0,
            "references" : [],
            "name" : "Testartikel",
            "manufacturer" : "Ich",
            "itemnum" : "18929537",
            "barcode" : "2541235256262626233",
            "itemtype" : 0,
            "unit" : "C62",
            "amount" : 5,
            "amounttaken" : 0,
            "sellingprice" : 50,
            "purchaseprice" : 40,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 19,
            "weight" : 2,
            "item" : 1,
            "reservation" : null,
            "transactions" : [{
                    "transaction" : 47,
                    "storage" : 1,
                    "amount" : 12,
                    "chargenum" : "",
                    "serials" : []
                }
            ]
        }, {
            "type" : 0,
            "original" : 46,
            "subindex" : 0,
            "references" : [],
            "name" : "Testartikel",
            "manufacturer" : "Ich",
            "itemnum" : "18929537",
            "barcode" : "2541235256262626233",
            "itemtype" : 0,
            "unit" : "C62",
            "amount" : 12,
            "amounttaken" : 0,
            "sellingprice" : 50,
            "purchaseprice" : 40,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 19,
            "weight" : 2,
            "item" : 1,
            "reservation" : null,
            "transactions" : [{
                    "transaction" : 47,
                    "storage" : 1,
                    "amount" : 12,
                    "chargenum" : "",
                    "serials" : []
                }
            ]
        }, {
            "type" : 0,
            "original" : 57,
            "subindex" : 0,
            "references" : [],
            "name" : "Serienartikel",
            "manufacturer" : "FACTORY INC.",
            "itemnum" : "ARTNUM12345",
            "barcode" : "165749875341",
            "itemtype" : 1,
            "unit" : "C62",
            "amount" : 1,
            "amounttaken" : 0,
            "sellingprice" : 0,
            "purchaseprice" : 0,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 0,
            "weight" : 0,
            "item" : 4,
            "reservation" : null,
            "transactions" : [{
                    "transaction" : 59,
                    "storage" : 1,
                    "amount" : 1,
                    "chargenum" : "",
                    "serials" : ["98144-98144-98144-98144"]
                }
            ]
        }
    ]

Columns

Related columns Constraint Comments

Search:

Column Type Size Nulls Auto Default Children Parents Comments

ID

int4

10

√

null

actionsteps.transaction	fk_actionsteps_transaction N
couponcodes.transaction	fk_couponcodes_transaction N
dunning2transactions.transaction	fk_dunning2transactions_transaction C
payments.transaction	fk_payments_transaction N
stocktransactions.transaction	fk_stocktransactions_transaction N

Transaction ID

ownergroup

int4

10

√

null

groups.ID

fk_transactions_ownergroup C

Owner group ID (null=PUBLIC)

creator

int4

10

√

null

users.ID

fk_transactions_creator N

Creator user ID (defaults to authenticated user on creation)

assigneduser

int4

10

√

null

users.ID

fk_transactions_assigneduser N

Assigned user ID

creationdate

int8

19

date_part('epoch'::text, now())

Creation date and time as a Unix time stamp (defaults to current date and time on creation)

lastmodified

int8

19

date_part('epoch'::text, now())

Last modification date and time as a Unix time stamp (auto-reset on modification)

account

int4

10

√

null

accounts.ID

fk_transactions_account N

Account ID; must be null for PRODUCTION

item

int4

10

√

null

items.ID

fk_transactions_item C

Item ID; is required for PRODUCTION, otherwise must be null

transactionnum

text

2147483647

null

Transaction number

type

int2

5

0

Transaction type (0=BILLING_QUOTE, 1=BILLING_ORDER, 2=BILLING_DELIVERY, 3=BILLING_INVOICE, 4=BILLING_CREDIT, 5=PROCUREMENT_REQUEST, 6=PROCUREMENT_ORDER, 7=PROCUREMENT_DELIVERY, 8=PROCUREMENT_INVOICE, 9=PROCUREMENT_CREDIT, 10=PRODUCTION_FABRICATION, 11=PRODUCTION_DISASSEMBLY)

date

int8

19

date_part('epoch'::text, now())

Designated date and time as a Unix time stamp (defaults to current date and time on creation)

duedate

int8

19

√

null

Due date as a Unix time stamp

status

int2

5

0

Status (0=DRAFT, 1=BOOKED, 2=HOLD, 3=CANCELLED, 4=CLOSED, 5=PARTLYORDERED, 6=PARTLYORDERED_CANCELLED, 7=PARTLYORDERED_CLOSED, 8=ORDERED, 9=PARTLYDELIVERED, 10=PARTLYDELIVERED_CANCELLED, 11=PARTLYDELIVERED_CLOSED, 12=DELIVERED, 13=PARTLYINVOICED, 14=PARTLYINVOICED_CANCELLED, 15=PARTLYINVOICED_CLOSED, 16=INVOICED, 17=PARTLYPAID, 18=PARTLYPAID_CANCELLED, 19=PARTLYPAID_CLOSED, 20=PAID, 21=OVERPAID, 22=PROCESSED, 23=PROCESSED_CANCELLED)

productionfactor

int4

10

√

null

Production factor; is required for PRODUCTION, otherwise must be null

currency

varchar

3

null

Currency code (ISO 4217) (Pattern: ^[A-Z]{3}$)

exchangerate

float8

17,17

1

Exchange rate as a multiple of one monetary unit of the fixed system currency

shippingrecipient

text

2147483647

''::text

Shipping recipient

shippingaddress

text

2147483647

''::text

Shipping address (street and building/suite number)

shippingpostalcode

text

2147483647

''::text

Shipping postal or ZIP code

shippingcity

text

2147483647

''::text

Shipping city or municipality

shippingregion

text

2147483647

''::text

Shipping region or state

shippingcountry

varchar

2

''::character varying

Shipping country code (ISO 3166-1 alpha-2) (Pattern: ^([A-Z]{2})?$)

billingrecipient

text

2147483647

''::text

Billing recipient

billingaddress

text

2147483647

''::text

Billing address (street and building/suite number)

billingpostalcode

text

2147483647

''::text

Billing postal or ZIP code

billingcity

text

2147483647

''::text

Billing city or municipality

billingregion

text

2147483647

''::text

Billing region or state

billingcountry

varchar

2

''::character varying

Billing country code (ISO 3166-1 alpha-2) (Pattern: ^([A-Z]{2})?$)

discount

float8

17,17

0

Total absolute discount

netamount

float8

17,17

0

Total net amount

tax

float8

17,17

0

Total tax amount

margin

float8

17,17

0

Total absolute margin

weight

float8

17,17

0

Total shipping weight in kilogram

items

json

2147483647

√

null

contract

int4

10

√

null

contracts.ID

fk_transactions_contract N

Contract ID

Showing 1 to 35 of 35 entries

Table contained 0 rows

Indexes

Search:

Constraint Name	Type	Sort	Column(s)
pk_transactions	Primary key	Asc	ID
fk_transactions_account	Performance	Asc	account
fk_transactions_contract	Performance	Asc	contract
fk_transactions_item	Performance	Asc	item
i_transactions_date_ID	Performance	Desc/Desc	date + ID
i_transactions_noowner	Performance	Asc	ownergroup
i_transactions_owner	Performance	Asc/Asc	ownergroup + assigneduser
i_transactions_transactionnum_ID	Performance	Desc/Desc	transactionnum + ID
i_transactions_type	Performance	Asc	type
s_transactions	Performance	Asc	transactionnum
u_transactions_transactionnum_type	Must be unique	Asc/Asc	transactionnum + type

Showing 1 to 11 of 11 entries

Relationships

Close relationships within degrees of separation

One
Two degrees