Hi,
Thank you for the reply,
unfortuntatly your logging suggestion isn't populating the log table
here is the full report for the list
DROP PROCEDURE IF EXISTS sp_DBR_SKU_Profitabilty
$$
CREATE PROCEDURE sp_DBR_SKU_Profitabilty(settlement bigint(250), inLogin varchar(30))
BEGIN
select cid into @cid from dsguru_eBayMadeEasy.users u where u.username = inLogin;
Select date(Min(a.date/time) )as 'Start Date', date(max(a.date/time)) as 'End Date'
from dsguru_eBayMadeEasy.AzTransactionRpts a
where a.settlement id = settlement and a.cid = @cid;
drop table if exists SkuSums;
CREATE TEMPORARY TABLE IF NOT EXISTS SkuSums
AS select a.settlement id as 'Setid',
a.sku as 'msku',
sum(a.quantity) as 'TotalQty',
sum(a.total)-sum(a.sales tax collected) as 'Net',
sum(a.puc) as 'ExtCost',
sum(a.total)-sum(a.sales tax collected)-sum(a.puc) as 'Profit'
from dsguru_eBayMadeEasy.AzTransactionRpts a
where a.cid = @cid and Type='Order' and a.settlement id = settlement
group by a.settlement id, a.sku;
select '' as '------------------- SKU Profitablilty per period -----------------';
select 'dbr.editable', '[UnitCost]', 'sp_DBR_Update_PUC', 'uid=cid', 'setid=SettlementID', 'msku=Sku';
select 'dbr.sum',6,7;
select 'dbr.colstyle', 'SettlementID', mydbr_style('NumAsStr');
select 'dbr.colstyle', 'ROI', '%.0f %';
select 'dbr.colstyle', 'UnitCost', '[background: #d0edfe;]';
select
a.cid as 'cid',
a.settlement id as 'Settlement ID[SettlementID]',
a.sku as 'Sku[Sku]',
a.description as 'Description',
sum(a.quantity) as 'Qty',
sum(a.total)-sum(a.sales tax collected) as 'Net Net Income',
s.Profit as 'Profit',
s.ExtCost as 'Extended Cost',
sum(case when s.ExtCost > 0 then ((s.Profit / s.ExtCost)*100) else ' ' end) as 'ROI' ,
a.puc as 'Unit Cost[UnitCost]'
from dsguru_eBayMadeEasy.AzTransactionRpts a
inner join SkuSums s on s.msku = a.sku and s.Setid = a.settlement id
where a.cid = @cid and Type='Order' and a.settlement id = settlement
group by a.settlement id, a.sku
order by a.settlement id desc, a.sku desc;
END
$$
here is the full code for the edit report
DROP PROCEDURE IF EXISTS sp_DBR_Update_PUC
$$
CREATE PROCEDURE sp_DBR_Update_PUC( uid int,
msku varchar(250) CHARSET utf8,
setid bigint(250),
inValue decimal(6,2))
BEGIN
insert into dsguru_eBayMadeEasy.param_log
select concat( uid,':', setid,':', msku, ':', inLogin, ':', inValue);
if (inValue>0) then
update dsguru_eBayMadeEasy.AzTransactionRpts a
set a.puc = inValue
where a.cid = uid and a.settlement id = setid and a.sku = msku;
else
select a.puc
from dsguru_eBayMadeEasy.AzTransactionRpts a
where a.cid = uid and a.settlement id = setid and a.sku = msku;
end if;
END
$$
here is the path to the new param_log table
[code]
Server: localhost » Database: dsguru_eBayMadeEasy » Table: param_log
(and yes all those db column names are surrounded by the `, they are showing brown in the editor so I think the spaces in column names isn't the issue. The table column names can not be changed due to code for importing the data into the database.)
---------------------------------------------------------
I was exploring the other alternatives to editing the cost field and I was able to get the the popup window (via dbr.report) to edit the field and refresh the table, unfortunately I don't know how to display the msku and description contents as un-editiable fields. If I use the = they don't display. If I use the pop up window I would want the only field editable is the cost. The user would need to know what the sku and description of the item they are about to edit.
Thanks in Advance!
