transactions


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
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 Payed OPEN 17 orange - - - X X
Partly Payed / Cancelled CANCELLED 18 red - - - X X
Partly Payed / Closed CLOSED 19 black - - - X X
Payed CLOSED 20 black - - - X X
Overpayed 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

Column Type Size Nulls Auto Default Children Parents Comments
ID serial 10 nextval('"transactions_ID_seq"'::regclass)
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
ownergroup int4 10 null
groups.ID FK_transactions_ownergroup C

The owner group (if owneruser and ownergroup are NULL, the element is public)

creator int4 10 null
users.ID FK_transactions_creator N

The object creator

assigneduser int4 10 null
users.ID FK_transactions_assigneduser N

The assigned user

creationdate int8 19 (date_part('epoch'::text, now()))::bigint

Last modification date/time (Unix timestamp in seconds)

lastmodified int8 19 (date_part('epoch'::text, now()))::bigint

Last modification date/time (Unix timestamp in seconds)

account int4 10 null
accounts.ID FK_transactions_account N

Reference to the associated account

item int4 10 null
items.ID FK_transactions_item C
transactionnum text 2147483647 null

Unique transaction number

type int2 5 null

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

date int8 19 (date_part('epoch'::text, now()))::bigint

Unix timestamp in seconds

duedate int8 19 null

Unix timestamp in seconds

status int2 5 0

0: Draft, 1: Booked, 2: Hold, 3: Cancelled, 4: Closed, 5: Partly ordered, 6: Partly ordered cancelled, 7: Partly ordered closed, 8: Ordered, 9: Partly delivered, 10: Partly delivered cancelled, 11: Partly delivered closed, 12: Delivered, 13: Partly invoiced, 14: Partly invoiced cancelled, 15: Partly invoiced closed, 16: Invoiced, 17: Partly paid, 18: Partly paid cancelled, 19: Partly paid closed, 20: Paid, 21: Overpaid

productionfactor int4 10 null
currency varchar 3 null
exchangerate float8 17,17 1

Exchange rate for foreign currency

shippingrecipient text 2147483647 ''::text
shippingaddress text 2147483647 ''::text
shippingpostalcode text 2147483647 ''::text
shippingcity text 2147483647 ''::text
shippingregion text 2147483647 ''::text
shippingcountry varchar 2 ''::character varying
billingrecipient text 2147483647 ''::text
billingaddress text 2147483647 ''::text
billingpostalcode text 2147483647 ''::text
billingcity text 2147483647 ''::text
billingregion text 2147483647 ''::text
billingcountry varchar 2 ''::character varying
discount float8 17,17 0
netamount float8 17,17 0
tax float8 17,17 0
margin float8 17,17 0
weight float8 17,17 0
items json 2147483647 null

Table contained 0 rows

Indexes

Constraint Name Type Sort Column(s)
PK_transactions_ID Primary key Asc ID
UNQ_transactions_transactionnum_type Must be unique Asc/Asc transactionnum + type

Relationships