Callback advice

(8 posts) (2 voices)
  • Started 2 years ago by ajdjackson
  • Latest reply from myDBR Team
  1. ajdjackson, Member

    Hi

    I've created a report that lists the sales invoices created in a month. Also included is the cost of materials and labour.

    I've a inline linked report that shows the breakdown of the materials used. This report consisted of several resultsets. The first resultset is a table that lists the items and quantities used. This report is grouped by item code.

    The second resultset is a table that again shows the above data but not grouped as any item used could be the sum of several batches of that item. Hope you follow that.

    What I wish to do is:

    1) to be able to edit the quantities used in this second resultset (i'm ok with that) but then refresh the first resultset (or update the actual item in the first resultset and recalculate the summary information).

    2) update the quantities used in the main report with the values from the 'refreshed' first resultset in the inline report and also update the summary fields in the main report.

    Hope that make sense.

    Is this possible? If so can you give me some pointers as how best to do it?

    Thanks

    Jake

    Posted 2 years ago #
  2. myDBR Team, Key Master

    Without seeing the actual report, the answer will be generic. You basically have two options.

    1) Use JavaScript to update and recalculate the other result set based on your data. You update the value in question and refresh the summary calculation with autosum-functions.

    2) You place a hidden button into the report which you can then click from the callback. The hidden button refreshes the desired result set with a linked report with Ajax.

    --
    myDBR Team

    Posted 2 years ago #
  3. ajdjackson, Member

    Hi

    Thanks for the pointers. I've been able to make some progress on this but I'm stuck at the last step.

    The main report that calls the breakdown report is:

    select 'dbr.hdr','Cust'; select 'dbr.hdr','InvNo'; select 'dbr.sum','Net','Mats','BCost','LHrs','LCost','GM'; select 'dbr.summary.options','skip_single_line_summary'; select 'dbr.colstyle','QtyD','%0.0f'; select 'dbr.colstyle','Mats','[font-weight:bold;border-right:1px solid gray;border-left:1px solid gray;]%0.2f'; select 'dbr.colstyle','PMats','%0.2f'; select 'dbr.colstyle','Net','[font-weight:bold;border-right:1px solid gray;border-left:1px solid gray;]%0.2f'; select 'dbr.colstyle','LCost','[font-weight:bold;border-right:1px solid gray;border-left:1px solid gray;]%0.2f'; select 'dbr.colstyle','PCost','[font-weight:bold;border-right:1px solid gray;border-left:1px solid gray;]%0.2f'; select 'dbr.calc','PMats','([Mats]/[Net])*100'; select 'dbr.calc','MEff','([BCost]/[Mats])*100'; select 'dbr.calc','LCost','[LHrs]*10.99'; select 'dbr.calc','PCost','[LCost]+[Mats]'; select 'dbr.calc','LabPer','([LCost]/[Net]*100)'; select 'dbr.calc','GM','[Net]-[PCost]'; select 'dbr.calc','GMPer','([GM]/[Net])*100'; select 'dbr.report','sp_DBR_Pharma_Mat_Det','[Mats]','inline','inFPO=FPONo','inDate=IDate','inInvNo=InvNo', 'inItem=ICode'; select 'dbr.resultclass','ordersummary'; select 'dbr.css','.ordersummary .summary_level1 {background-color:#eff5f5;font-weight:bold;}'; select 'dbr.css','.ordersummary .summary_level0 {font-weight:bold; background-color: rgb(3, 255, 247); border-top:2px solid black; border-bottom:2px solid black;}'; select 'dbr.css','.ordersummary tr.odd {background-color:white;}'; select 'dbr.css','body {background-color: #e6faff;}'; select 'dbr.hidecolumns','class'; select 'dbr.rowstyle', 'class';

    select a.CustName as 'Customer[Cust]', a.YourOrder as 'Cust Order#[COrdNo]', a.InvNumber as 'Inv #[InvNo]', date(a.InvoiceDate) as 'Inv. Date[IDate]', a.ItemCode as 'Item Code[ICode]', a.Description as 'Description', a.QTYInvoiced as 'QTY Inv.[QtyD]', a.Price as 'Price', ifnull((a.LineTot),0) as 'Net [Net]', a.FPONo as "FPO #[FPONo]", ifnull(((Mats*a.QTYInvoiced)/c.fQty),0) as "Mat Costs[Mats]", null as "% Mats[PMats]", ifnull(((b.bQty*a.QTYInvoiced)/c.fQty),0) as "BOM Cost[BCost]", null as "Mat Eff[MEff]", if (a.ItemCode not in (select par_code from pharmapac.tbllabour_overview where tot_hours > 0), ifnull((a.QTYInvoiced*e.num_ops/e.tar_output),0), ifnull((a.QTYInvoiced*d.tot_hours/d.tot_output),0)) as "Lab Hrs[LHrs]", null as "Lab Cost[LCost]", null as "Lab %[LabPer]", null as "Prod. Cost[PCost]", null as "G Margin[GM]", null as "GM,%[GMPer]", if ((ifnull((a.LineTot),0)-ifnull(((Mats*a.QTYInvoiced)/c.fQty),0)-if (a.ItemCode not in (select par_code from pharmapac.tbllabour_overview where tot_hours > 0), ifnull((a.QTYInvoiced*e.num_ops/e.tar_output),0), ifnull((a.QTYInvoiced*d.tot_hours/d.tot_output),0))*10.99) < 0, 'background-color: #ffe6e6;','') as 'class[class]'

    from pharmapac.order_tmp a left outer join bom_eff_sum b on a.FPONo = b.bFPONo left outer join pharmapac.fpo_qty c on a.FPONo = c.fFPONo left outer join pharmapac.tbllabour_overview d on a.ItemCode = d.par_code left outer join pharmapac.tbllabour_prod e on a.ItemCode = e.par_code order by CustName,YourOrder,InvoiceDate;

    When you click the 'Mats' column the detailed report opens:

    select 'dbr.css','td.inline_report_cell {border: 3px solid gray;border-radius: 10px;margin:5px;box-shadow: 10px 10px 5px #888888;}'; select 'dbr.css','.invheader {display:none;}';

    select 'dbr.javascript',' $("#Update").click(function () {

    var inInvNo = $("table.invheader tr td").parent("tr:first").find("td:eq(0)").text(); var inFPO = $("table.invheader tr td").parent("tr:first").find("td:eq(2)").text(); var inItem = $("table.invheader tr td").parent("tr:first").find("td:eq(3)").text();

    //alert (inInvNo +" "+inFPO +" "+inItem);

    inInvNo = $.trim(inInvNo); //strip leading and trailing spaces inFPO = $.trim(inFPO); //strip leading and trailing spaces inItem = $.trim(inItem); //strip leading and trailing spaces

    var url = "report.php?r=331&u1=" + inFPO + "&u2=" + inInvNo+ "&u3=" +inItem + "&m=11&h=b0837278940d8e3b2d09673ce99ba9bedbf90160 #showthis";

    $(".matused").load(url,function() {

    var newcost = $("table.matused tr:last-child td:nth-child(6)").text();

    var tableRow = $("table.ordersummary tr").each(function(i){ var onum = $(this).find("td:eq(2)").text(); onum = $.trim(onum);

    if(onum == inInvNo){ var oFPO = $(this).find("td").eq(9).text(); oFPO =$.trim(oFPO); if(oFPO == inFPO){ var tableRow = i;

    } }

    });

    alert(newcost +" "+tableRow);

    });

    }); ';

    select 'dbr.resultclass','invheader'; select 'dbr.hideheader'; select 'dbr.title','';

    select inInvNo as 'Inv #[InvNo]', inDate as 'Date[IDate]', inFPO as 'FPO #[IFPO]', inItem as 'Item[IItem]', b.`Item Description` as "Description[IDesc]" from pharmapac.`Item Master` b where b.`Item Code` = inItem;

    select 'dbr.title','Actual Material versus Expected BOM Usage'; select 'dbr.colstyle','POCost','%0.5f'; select 'dbr.colstyle','BQty','%0.5f'; select 'dbr.colstyle','UCost','[font-weight:bold;]'; select 'dbr.colstyle','BCost','[font-weight:bold;]'; select 'dbr.calc','UCost','[UQty]*[POCost]'; select 'dbr.calc','BCost','[BQty]*[POCost]'; select 'dbr.calc','QEff','([BQty]/[UQty])*100'; select 'dbr.calc','CEff','([BCost]/[UCost])*100'; select 'dbr.sum','BCost','UCost'; select 'dbr.resultclass','matused'; select 'dbr.css','.matused .summary_level0 {font-weight:bold; background-color: rgb(3, 255, 247); border-top:2px solid black; border-bottom:2px solid black;}'; select 'dbr.css','.matused .summary_level0:nth-child(9) {display:none;}'; select 'dbr.css','.matused .summary_level0:nth-child(10) {display:none;}';

    select a.oItem as "Item #[Item]", b.`Item Description` as "Description[IDesc]", a.oPType as "Mat Type[MType]", sum(a.oQty*d.iQty/f.fQty) as "Qty Used[UQty]", c.POPrice as "PO Cost[POCost]", null as "Used Cost[UCost]", d.iQty*e.QuantityPer as "BOM Qty[BQty]", null as "BOM Cost[BCost]", null as "Qty Eff[QEff]", null as "Cost Eff[CEff]"

    from pharmapac.tblorder_bom_det a join pharmapac.`Item Master` b on a.oItem = b.`Item Code` left outer join (select oFPONo as FPO, oItem as Item, sum(oQty*oPO_Price)/sum(oQty) as POPrice from pharmapac.tblorder_bom_det where oFPONo=inFPO group by oFPONo,oItem) c on a.oFPONo = c.FPO and a.oItem = c.Item left outer join (select InvNumber as oInv, ItemCode as iItem,QTYInvoiced as iQty from pharmapac.order_tmp where InvNumber = inInvNo ) d on d.oInv = inInvNO and d.iItem = inItem left outer join pharmapac.bom_detail e on /*d.ItemCode =*/ e.Parent_Code = inItem and e.Component_Code = a.oItem left outer join pharmapac.fpo_qty f on f.fFPONo = inFPO

    where a.oFPONo = inFPO and a.oB1 is null

    group by a.oItem

    order by b.`Item Description`;

    select 'dbr.title','Production Material Usage and Cost Breakdown'; select 'dbr.hdr','Bom'; select 'dbr.calc','LTotal','[Qty]*[Cost]'; select 'dbr.sum','LTotal'; select 'dbr.colstyle','Qty','%0.5f'; select 'dbr.colstyle','Cost','%0.5f'; select 'dbr.resultclass','orderdet'; select 'dbr.css','.orderdet tr.summary_row.row[data-set="1"]>td {font-weight:bold; background-color: rgb(3, 255, 247); border-top:2px solid black; border-bottom:2px solid black;}'; select 'dbr.css','.orderdet .summary_level0 {display:none;}'; select 'dbr.editable','Qty','sp_DBR_Pharma_Order_Qty_Edit','inQFPO=[inFPO]','inQInvNo=[inInvNo]','inQDate=[inDate]','inBatch=Batch','inOQty=Qty','inItem=ICode',"options={'callback':mycallback}"; select 'dbr.hidecolumns','inFPO';

    select (case when ob1 is null then "Bom Level 0" when ob2 is null then "Bom Level 1" when ob3 is null then "Bom Level 2" when ob4 is null then "Bom Level 3" else "Bom Level 4" end) as "Bom[Bom]", a.oItem as "Item Code[ICode]", b.`Item Description` as 'Item Desc.[IDesc]', a.oGRN as "Batch #[Batch]", a.oPType as "Type[PType]", a.oQTY as "Qty[Qty]", b.UoM as "Unit[UOM]", a.oPO_Num as "PO-Num[PON]", a.oPO_Price as "Cost[Cost]", null as "Line Total[LTotal]", inFPO as '[inFPO]', inInvNo as 'inInvNo', inDate as 'inDate'

    from pharmapac.tblorder_bom_det a join pharmapac.`item master` b on a.oItem = b.`item code` where a.oFPONo = inFPO;

    select 'dbr.title','Bill of Materials'; select 'dbr.colstyle','QPer','%0.5f'; select 'dbr.resultclass','bom';

    select b.Component_Code as "Item#[ICode]", c.`Item Description` as "Description[Desc]", b.QuantityPer as "Qty Per[QPer]", b.Overage as "Overage[Over]" from pharmapac.invdetails a join pharmapac.bom_detail b on a.ItemCode = b.Parent_Code join pharmapac.`item master` c on b.Component_Code = c.`Item Code` where a.InvNumber = inInvNo group by b.Component_Code order by c.`Item Description`;

    select 'dbr.title','Audit Trail Data'; select 'dbr.resultclass','audittrail'; SELECT ID, Atype, Auid, date(Adate), aItem,`Item Description`, aLot, aQty, aFromLoc, aToLoc, aorigqty, aCustLot FROM pharmapac.`audit trail` join pharmapac.`Item Master` on aItem = `Item Code` where aToLoc = inFPO or aFromLoc = inFPO and datediff(inDate,aDate) < 60 order by aItem,alot,adate;

    select 'dbr.html','<input type="button" name="Update" id="Update" value="Update" style="display:none" />';

    select 'dbr.javascript', 'function mycallback() {

    var A = col_value_get( this);

    var B = col_value_get( this,9 );

    // Get ItemCode

    var itemcode = $(this).closest("tr").find("td:nth-child(2)").text();

    // Set the value of 10th column

    col_value_set( this, 10, 0, A*B);

    autosum_float(this,10,2);

    $("#Update").click();

    }';

    Where I'm stuck is getting back to the main report and updating the [Mats] with new quantity ie this doesn't work:

    var tableRow = $("table.ordersummary tr").each(function(i){ var onum = $(this).find("td:eq(2)").text(); onum = $.trim(onum);

    if(onum == inInvNo){ var oFPO = $(this).find("td").eq(9).text(); oFPO =$.trim(oFPO); if(oFPO == inFPO){ var tableRow = i;

    } }

    });

    tableRow returns object Object instead of the table row.

    Any thoughts on where I'm going wrong?

    Thanks

    Jake

    Posted 2 years ago #
  4. myDBR Team, Key Master

    Jake,
    that was a long code block.

    Couple of quick notes:

    - What was the purpose of "var url = "report.php?r=331&u1..."?
    - Where do you expect the tableRow value come from (you have multiple declarations of this).

    --
    myDBR Team

    Posted 2 years ago #
  5. ajdjackson, Member

    Hi

    Sorry about that.

    The flow is as follows:

    The user selects a month and year in the main report. This displays that invoices and associated costs. One of those costs is the cost of the materials used [Mats].

    The user can then click the [Mats] column and an in-line report opens that displays a breakdown of the materials used. This report has 4 tables: 1st is a summary of qty and cost by item code, the 2nd table shows the same info as the 1st table but at item code and batch code level. The other 2 tables can be neglected for this issue.

    I wish to give the user the ability to change the quantities used (I'm logging the changes in a separate table). This action is down in the 2nd table. I use a simple call back function to recalculate summaries etc in that table. The call backup function also clicks a hidden button that loads a report in the 'matused' div that over-writes the 1st table showing the new quantities.

    I've got all this working.

    Where I'm stuck is at the writing of the new total cost of the materials, taken from the summary row of the 1st table, back into the original cell I clicked in the main report. I've been tying to identify the row number ie tableRow, by searching for inFPO and inInvNo, in the main report.

    It's this bit of js that's not working:

    $("table.ordersummary tr").each(function(i){ var onum = $(this).find("td:eq(2)").text(); onum = $.trim(onum);

    if(onum == inInvNo){ var oFPO = $(this).find("td").eq(9).text(); oFPO =$.trim(oFPO); if(oFPO == inFPO){ var tableRow = i;

    } }

    });

    It doesn't find the row which has the inInvNo and inFPO. The data for these are in the 3rd and 10th columns.

    Hope that makes a bit more sense.

    Thanks

    Jake

    Posted 2 years ago #
  6. myDBR Team, Key Master

    The hidden button can be created by dbr.button & dbr.report, so you do not have to create URL's manually or use Ajax-queries yourself; myDBR will do that for you. Additionally, you do not expose the linked report to everyone as you do now.

    As for the JavaScript problem, use debugger-statement in the code to set a breakpoint and debug the code execution with the browser's inspector. That should give you idea why your code does not work.

    --
    myDBR Team

    Posted 2 years ago #
  7. ajdjackson, Member

    Hi

    Thanks for that - I'll have a look at creating the button as you suggest above.

    I've done a few changes to the js and I now can get the row number.

    What I did was declare the inInvNo and inFPO (plus other variables) outside of a function. DOH.

    However when I try to read the value in the 11th column of that row it's reading the data from the wrong table.

    The table I need to read is has a class = ordersummary but I'm getting the data from a table with class = audittrail. No idea why :(

    Here's my amended js:

    select 'dbr.javascript','

    var inInvNo; var inFPO; var inItem; var tableRow = 0;

    $("#Update").click(function () {

    inInvNo = $("table.invheader tr td").parent("tr:first").find("td:eq(0)").text(); inFPO = $("table.invheader tr td").parent("tr:first").find("td:eq(2)").text(); inItem = $("table.invheader tr td").parent("tr:first").find("td:eq(3)").text();

    inInvNo = $.trim(inInvNo); //strip leading and trailing spaces inFPO = $.trim(inFPO); //strip leading and trailing spaces inItem = $.trim(inItem); //strip leading and trailing spaces

    var url = "report.php?r=331&u1=" + inFPO + "&u2=" + inInvNo+ "&u3=" +inItem + "&m=11&h=b0837278940d8e3b2d09673ce99ba9bedbf90160 #showthis";

    //alert(url);

    $(".matused").load(url,function() {

    var newcost = $("table.matused tr:last-child td:nth-child(6)").text();

    $("table.ordersummary tr").each(function(i){ var onum = $(this).find("td:eq(2)").text(); onum = $.trim(onum);

    if(onum == inInvNo){ var oFPO = $(this).find("td:eq(9)").text(); oFPO =$.trim(oFPO); if(oFPO == inFPO){ tableRow = i; return tableRow; } }

    });

    var oldcost = $("table.ordersummary tr:nth-child("+tableRow+")").find("td:eq(10)").text();

    alert(/*newcost /*+" "+*/tableRow+" "+oldcost);

    });

    }); ';

    So 'oldcost' is being taken from the wrong table.

    Thanks for persevering with me - I'm new to JS

    Jake

    Posted 2 years ago #
  8. myDBR Team, Key Master

    Try to learn how to use JavaScript debugger. It will help you a lot.

    Also, take a look if you really need that "var url = "report.php..." method, looks overly complex.

    --
    myDBR Team

    Posted 2 years ago #

Reply

You must log in to post.