virtual table

[Volker Trapp]

i am trying to get python code on the server running.

I have created a virtual table:

and I have created the following server module ( stripped to minimum )

def on_open(item, params):

    print("on_open")

    # raise Exception("on_open")

def on_before_open(item, query, params, connection):

    print("on_before_open")

    # raise Exception("on_before_open")

def on_after_apply_record(item, delta, params, connection):

    print("on_after_apply_record")

    # raise Exception("on_after_apply_record")

def on_after_open(item, query, params, connection, dataset):

    print("on_after_open")

    # raise Exception("on_after_open")

I expected these to print on the server console, when they are called,

but this is not happening.

I know on_open() is depriciated.

To me it looks like the code on the server is never called.

Is there a place where I have to activate this ?

I have tried restarting the server and also did a ctrl F5 in the browser.

[Dean D. Babic]

Maybe a stupid question, but where is the JS code?
None if this events will fire. Why? Because VT has no dataset.
The VT needs a dataset  JS on_after_open:

https://groups.google.com/g/jam-py/c/ZxV93YlrmR0/m/1peF2TYnAQAJ

VT has no "Soft delete".

[Volker Trapp]

Maybe a wrong understanding of mine.

I wanted to populate the data on the python side:

def on_before_open(item, query, params, connection):

    print("on_before_open")

    # raise Exception("on_before_open")

    cursor = item.task.db.create_cursor()

    sSql = """

        select c.vorid, a.vorposid, d.projektnummer

        from KHKVKBelegePositionen a, KHKVKBelege b, KHKVKVorgaengePositionen c, KHKVKVorgaenge d, KHKProjekte e

        where a.BelID = b.BelID and b.Belegart = 'Auftragsbestätigung' and a.USER_PosType = 65 and

        a.VorPosID = c.VorPosID and c.GGErfuellt = 0 and d.vorid = c.vorid and

        d.Projektnummer = e.Projektnummer and e.Kostenstelle = 'T10000' and a.vorposid <> 0

    """

    # sSql auführen

    cursor.execute(sSql)

    rows = cursor.fetchall()

   

    # Die virtuelle Tabelle mit den Ergebnissen füllen

    for row in rows:

        item.append() # Erzeugt eine neue (temporäre) Zeile

        item.vorid.value = row[0]

        item.vorposid.value = row[1]

        item.projektnummer = row[2]

        item.post() # Bestätigt die Zeile im Speicher

since the on_before_open on the server side is not called, this didnot work.

Is this the wrong way ?

[Dean D. Babic]

Sorry, 

I'm confused. 

I assume u want to display the data from SQL, correct?

So in that thread I posted, Sarip provided the working example:

Here is my client module:
function on_after_open(item) {
    item.disable_controls();
    let order_rows = item.server('get_order_rows');
    order_rows.forEach(function(v){
        item.append();
        item.id.value = v.id;
        item.master_rec_id.value = v.master_rec_id;
        item.sku.value = v.sku;
        item.title.value = v.title;
        item.colours.value = v.colours;
        item.post();
    });
    item.first();
    item.enable_controls();
}

And here is my server module:

def get_order_rows(item):
    rows = []
    recs = item.task.execute_select('''
        SELECT a.id, a.master_rec_id, b.sku, b.title, a.colours
        FROM order_ordertable a
        INNER JOIN order_products b
            ON a.sku = b.id
        INNER JOIN order_orders c
            ON a.master_rec_id = c.id
                AND c.completed = false
        WHERE a.deleted = 0 and a.start_date IS NULL and a.machine IS NULL;
    ''')
    for v in recs:
        rows.append({
            'id': v[0],
            'master_rec_id': v[1],
            'sku': v[2],
            'title': v[3],
            'colours': v[4]
        })
    return rows

This is example for v5, since he used master_rec_id back then. 
V7 has no master_rec_id.

Hence, you are missing function on_after_open(item) and the Python
get_order_rows (or whatever you call it)...
Nothing to do with on_xyz method.

It is quite simple really.

[Volker Trapp]

ok from the documenation:

on_before_open  event is triggered before the sql request is executed and can be used to validate the request and add additional filters.

   

I thought I could populate the item here ( seams not to work)

So I tried your example. I added a js function:

function on_after_open(item) {

    alert('on_after_open');

    item.disable_controls();

    let rows = item.server('get_rows');

    rows.forEach(function(v) {

        item.append();

        item.id = v.belposid;

        item.vorid = v.vorid;

        item.vorposid = v.vorposid;

        item.produktnummer = v.produktnummer;

        item.post();

    })

    item.first();

    item.enable_controls();

}

I have added a python function:

def get_rows(item):

    rows = []

    recs = item.task.execute_select("""

        select a.belposid, c.vorid, a.vorposid, d.projektnummer

        from KHKVKBelegePositionen a, KHKVKBelege b, KHKVKVorgaengePositionen c, KHKVKVorgaenge d, KHKProjekte e

        where a.BelID = b.BelID and b.Belegart = 'Auftragsbestätigung' and a.USER_PosType = 65 and

        a.VorPosID = c.VorPosID and c.GGErfuellt = 0 and d.vorid = c.vorid and

        d.Projektnummer = e.Projektnummer and e.Kostenstelle = 'T10000' and a.vorposid <> 0    

    """)

    for v in recs:

        rows.appen({

            'id': v[0],

            'vorid': v[1],

            'vorposid': v[2],

            'projektnummer': v[3]

        })

       

    return rows

nothing happens.

I have placed a breakpoint in the firefox debugger on the alert function

function Events38() { // Auftrag.catalogs.auftragsliste

function on_after_open(item) {
alert('on_after_open');
item.disable_controls();
let rows = item.server('get_rows');
rows.forEach(function(v) {
item.append();
item.id = v.belposid;
item.vorid = v.vorid;
item.vorposid = v.vorposid;
item.produktnummer = v.produktnummer;
item.post();
})
item.first();
item.enable_controls();
}

it never hits.

No errors in the js console.

And yes, I thought it is quite simple.

[Dean D. Babic]

See northwind.pythonanywhere.com

Two VT tables.  RFM and API call

[Dean D. Babic]

Obviously 

on_view_form_created

is needed, which he did not show in his code.

Inventory List is ALSO a VT on northwind example.

Have a look how nicely the message is displayed when click on:

Show products to reorder

The examples are here for you guys, so you can study the code:

function on_view_form_created(item) {

item.view_options.open_item = false;

item.view_options.form_header = false;

item.open({open_empty: true});

item.paginate = false;

item.view_form.find("#edit-btn").hide();

item.view_form.find("#delete-btn").hide();

item.view_form.find("#new-btn").hide();

item.alert('Fetching!');

item.server('send', function(records, err) {...

[Volker Trapp]

Thanks Dean,

I will have a closer look at the Northwind.

[Dean D. Babic]

Even this app has it:
https://jampy.pythonanywhere.com/

"Get invoice sales"

function on_view_form_created(item) {
    item.paginate = false;
    item.table_options.new = false;

    item.view_form.find("#edit-btn").hide();
    item.view_form.find("#delete-btn").hide();
    item.view_form.find("#new-btn").hide();

    show_rfm(item);
}
function show_rfm(item) {
    let id = [];
    item.alert('Working! Please try again if no data!');
    item.open(function() {
        item.server('get_sales', function(records) {
            item.disable_controls();
            try {
                records.forEach(function(res) {
                    item.append();
                    item.id.value = res.id;
                    item.customer.value = res.customer;
                    item.total.value = res.total;
                    // item.customer_lookup.value = res.customer_lookup;
                    item.post();
                });
                item.first();
            }
            finally {
                item.enable_controls();
                // console.table(records);
            }
        });
    });
}

def get_sales(item):
    sales = task.invoices
    sales.open(
        fields=['id', 'customer', 'firstname', 'total'],
        funcs={'total': 'sum'},
        group_by=['customer'],
        order_by=['customer']
    )
    res = []
    for rec in sales:
        row = {
            'id': rec.customer.value,
            'customer': f"{rec.firstname.lookup_value} {rec.customer.lookup_value}",
            'total': round(rec.total.value, 2)
        }
        res.append(row)
    return res