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
