0 votes

Hi!

I want to customize the AddRelation function in Custom.js :
On document, when a product of type Batch (FAMILY=1) i linked, I want to link all the products that are link to the Batch.

For the moment, I did it like this :
1) Custom.js call a serverscript (Document.js)
2) Document.js check if the product is a batch (FAMILY=1)
3) if it's a batch, I fill an array that contains one AddRelation url foreach of them
4) Custom.js get the array and caal each AddRelation url.

It works, but I need to fill a custom field when I do this : F_LOT. It must contains the K_PRODUCT of the Batch, but with the AddRelation request, it is fill with NULL value

I can fill this field with InsertDetail and UpdateDetail but I have a problem :
When the DocuEdit page is refresh, Efficy fail to find the name (blank NAME column)
enter image description here

If I use a CommitChanges(Context, true) the name are visible, but the relation are registered in the DB so the "cancel' button don't cancel these links..
Here, "Lot 3" is linked with the basic AddRelation call

Can anyone explain me how the request AddRelation in the dll do this "half commit"?

Custom.js :

function AddRelation(EditHandle, Bookmark, Entity, Key, Entity2, Key2, RefreshTopWindow) {
  // check if the product is a Batch (group of product)
  if(Entity=='Prod') {
    $.ajax({
      url: "dialog?_macrofile=MacroAjax&_macro=RunScript&file=Document&func=gestionLot&editHandle="+EditHandle +"&prodKey="+EscapeName(Key),
      type: "GET",
      async: false,
      success: function (html, textStatus, jqXHR) {
        if(IsNotEfficyErrorHtml(html, function(errorMsg) {alert(errorMsg)})) {
          var array = html.split(','); // html = string --> convert to array
          console.log(array);
          for(url of array) {
            url = url + "&bookmark="+Bookmark;
            LocationSet(top.opener, url);
            sleep(100); // --> permet a Efficy de prendre en compte chaque LocationSet
          }
        }
      }
    })
  }

  var URL = "addrelation?edithandle=" + EditHandle + "&bookmark=" + Bookmark + "&entity=" + Entity + "&key=" + EscapeName(Key)
  if (Entity2) URL += "&entity2=" + Entity2 + "&key2=" + EscapeName(Key2)
  if (window.ClickFromPopup)
    LocationSet(window, URL)
  else if (!RefreshTopWindow && OpenerExists() && !IsDesktopWindow(top.opener.top)) {
    top.opener.EntityModified = false;
    LocationSet(top.opener, URL)
    CloseTopWindow()
  }
  else
    LocationSet(window, URL)
}

function sleep(milliseconds) {
    var start = new Date().getTime();
    for (var i = 0; i < 1e7; i++) {
        if ((new Date().getTime() - start) > milliseconds){
            break;
        }
    }
}

serverscripts/Document.js :

function gestionLot() {
    // get Arguments
    var EditHandle = StrToIntDef(Request.Argument("editHandle"), 0),
    prodKey = Request.Argument("prodKey"); // product to link

    var editEntity = Database.GetEditEntity(EditHandle);
    if(editEntity != ntDocu) { // is Docu ?
        return;
    }

    // Check Key
    if(!prodKey || prodKey==0) {
        throw new Error("Product key undefined / Cle de produit indefini");
    }

    // get FAMILY of product
    var SQLGetProdFamily = "select FAMILY from <#TABLE NAME='PRODUCTS'> where K_PRODUCT=:param1";
    var result = ExecuteQuery(SQLGetProdFamily, prodKey);
    if(!result.IsEmpty) {
        result.First;
        FAMILY = result.FieldByName('FAMILY').AsFloat;

        // si lot ==> insertion produits du lot
        if(FAMILY==1) {
            return addProductsFromLot(EditHandle, prodKey);
        }
    }
    else {
        throw new Error("Can't find the product / resultat vide, Produit introuvable (Key=" + prodKey + ")");
    }
}

function addProductsFromLot(EditHandle, lotKey) {
    var queryHandle=0;
    var URLs = [];
    var ProdContext = Database.OpenConsultContext(ntProd);
    try {
        // get Products of the batch and link each of them
        var ProdDS = Database.ConsultDetail(queryHandle, ProdContext, lotKey, ntProd, false, true, 0);
        if(!ProdDS.IsEmpty) {
            ProdDS.First;
            while (!ProdDS.Eof) {
                var K_PRODUCT = ProdDS.fieldByName('K_PRODUCT').asFloat;
                Database.InsertDetail(EditHandle, ntProd, K_PRODUCT, -1, false);
                Database.UpdateDetail(EditHandle, ntProd, 0, 0, 'F_LOT', lotKey);
                //URLs.push("addrelation?edithandle=" + EditHandle + "&entity=Prod" + "&key=" + K_PRODUCT) // addrelation request used by Custom.js to add relation without persist
                ProdDS.Next;
            }   
            Database.CommitChanges(EditHandle, true); // persist changes ==> "Cancel" button don't cancel these relations
        }
    } finally {
        Database.CloseContext(ProdContext);
        return URLs;
    }
}

function ExecuteQuery(SQL, QueryParams, StoreID) {
    var QueryHandle = 0;
    if (StoreID == "")
        StoreID = 0;

    var testSQL = new String(SQL);
    testSQL = testSQL.toUpperCase();
    if (testSQL.indexOf("INSERT", 0) < 0 && testSQL.indexOf("DELETE", 0) < 0 && testSQL.indexOf("UPDATE", 0) < 0 && testSQL.indexOf("ALTER", 0) < 0) {
        var ContextHandle = Database.OpenTemporaryContext();
        try {
            var DS = Database.ExecuteSystemSQLQuery(QueryHandle, ContextHandle, SQL, QueryParams, true, true, StoreID);
            if (!DS.IsEmpty)
                DS.First;
            return DS;
        } catch (e) {
            throw new Error('ExecuteSystemSQLQuery error :' + e.message + "\nSQL: " + SQL + "\nParams: " + QueryParams);
        }
    } else {
        try {
            Database.ExecSQL(SQL, QueryParams, false);
            return true;
        } catch (e) {
            throw new Error('ExecSQL error :' + e.message + "\nSQL: " + SQL + "\nParams: " + QueryParams);
        }
    }
}

Thanks a lot !

asked in How to by (245 points)
edited by

1 Answer

+1 vote
Best answer

Hi there.

I believe you can achieve everything you want purely with DB workflow, nothing is required client side. Just attach code to the event OnInsertDetailDocuProd.

DB Workflow script:

var C = {
    LK_PROD_FAMILY: {
        BATCH: 1
    }
}

function OnInsertDetailDocuProd(editDocuProd, detail, detailkey) {
    insertBatchChildren(editDocuProd, detail, detailkey)
}

function insertBatchChildren(editDocuProd, detail, k_Product) {
    try {
        var consProd = Efficy.openConsultContext(ntProd),
            dsProd = Efficy.consult(consProd, k_Product, false),
            family = dsProd.fieldByName("FAMILY").asInteger;

        if (family != C.LK_PROD_FAMILY.BATCH) return;

        var queryHandle,
            dsProdProd = Efficy.consultDetail(queryHandle, consProd, k_Product, ntProd, false, true, 0)
            dsProdProd.first;
        while (!dsProdProd.eof) {
            Efficy.insertDetail(editDocuProd, ntProd, k_Product, -1, false);
            Efficy.updateDetail(editDocuProd, ntProd, 0, -1, "NAME", dsProdProd.fieldByName("NAME").asString);
            Efficy.updateDetail(editDocuProd, ntProd, 0, -1, "COMMENT", "Batch key is " + k_Product);
            dsProdProd.next;
        }
    } finally {
        Efficy.closeContext(consProd);
    }   
}

When inserting a Car product (family=Batch), the child car brand products are also inserted (three in my example). The NAME field in the tableview DocuProd is set using updateDetail to avoid the empty name when the page reloads. In the COMMENT field, we store the key of the Cars product. You can replace that with F_LOT.

Regards
Kristof

Products tree

Document edit

answered by (6.9k points)
selected by
Hi and thanks to you.

I already tried this but I didn't use "Update detail NAME" because I helped myself with Create.js / CreateInstantDoc() and they don't use it.

I need to sort Product like this by "F_LOT", LK_PROD_FAMILY.KEY, NAME
I customized SYS_TABLEVIEWS but it seems that Efficy ignore SELECTORDERBY and sort them by K_RELATION

EDIT : I have some problems to customize this  grid, so I have opened a new question you can look here :
https://overflow.efficy.com/?qa=3469/use-ms-sql-case-in-sys_tableviews
Welcome to Efficy Overflow, where you can ask questions and receive answers from other members of the community.
1,173 questions
1,432 answers
1,726 comments
325 users