1. 

   ajdjackson, Member

   Hi

   I've created a simple crosstab report and what I'm trying to do is to allow the user to click in any of the blank data cells and it then shades that cell in green and writes a value from one of the cells in the stub.

   I've got this working up to the point of where I need to insert values into a table. I'm not sure how I go about this.

   
   select 'dbr.javascript',"
   
       $(document).ready(function() {
   
   $('table.prodruns td.cell.align_r.ct_set_start_one').click(function () {
   
      var value = $(this).html();
   
     if (value == '') { 
   
      var cell = $(this),
   
           state = cell.data('state') || 'first';
   
       switch (state) {
   
           case 'first':
   
               cell.addClass('bggreen');
   
               cell.data('state', 'second');
   
               break;
   
           case 'second':
   
               cell.removeClass('bggreen');
   
               cell.data('state', 'first');
   
               break;
   
           default:
   
               break;
   
       }
   
       // add to do value to the clicked cell
   
       var column_num = parseInt( $(this).index() ) + 1;
   
       var A = col_value_get( this, 'todo' );
   
       col_value_set( this,column_num, 0, A);
   
       }
   
   });
   
     });
   
   ";

   I have a stored procedure to do the insert but it requires 3 values - the orderid, date, and quantity.

   The crosstab is as follows:

   
   
   select 'dbr.hidecolumn','OID';
   
   select 'dbr.crosstab','pWeek';
   
   select 'dbr.hdr','Line';
   
   select 'dbr.lockcolumns','dReq';
   
   select 'dbr.resultclass','prodruns';
   
   select 'dbr.colstyle','ICode','[min-width:70px;max-width: 70px;white-space:nowrap;overflow:hidden;text-overflow: ellipsis;]';
   
   select 'dbr.colstyle','IDesc','[text-align:center;min-width:250px;max-width:250px;white-space:nowrap;overflow:hidden;text-overflow: ellipsis;]';
   
   select 'dbr.colstyle','Cust','[min-width:120px;max-width:120px;white-space:nowrap;overflow:hidden;text-overflow: ellipsis;]';
   
   select 'dbr.colstyle','CustCon','[min-width:120px;max-width:120px;white-space:nowrap;overflow:hidden;text-overflow: ellipsis;]';
   
   select 'dbr.colstyle','Notes','[min-width:250px;max-width:250px;white-space:nowrap;overflow:hidden;text-overflow: ellipsis;]';
   
   select 'dbr.colstyle','oQTY','%0.0f';
   
   select 'dbr.colstyle','mQTY','%0.0f';
   
   select 'dbr.colstyle','dQTY','%0.0f';
   
   select 'dbr.colstyle','pQTY','[border-left:1px solid #CCC;]%0.0f;[text-align:right;min-width:50px;border-left:1px solid #CCC;] ;';
   
   select 'dbr.colclass','dQTY','todo';
   
   select 'dbr.colclass','pQTY','wqty';
   
   select a.OrderID as "Order ID[OID]",
   
         a.Line as "Line[Line]",
   
         a.Priority as "Priority[Prior]",
   
         a.ItemCode as "Item Code[ICode]",
   
         c.`Item Description` as "Description[IDesc]",
   
         a.`Order` as "FPO[oFPO]",
   
         d.CustName as "Customer[Cust]",
   
         a.CustCon as "Cust.Control[CustCon]",
   
         a.CustOrder as "Cust.Order[COrder]",
   
         a.QtyOrdered as "Qty.Ord[oQTY]",
   
         a.Qtyout as "Made Qty[mQTY]",
   
         a.QtyOrdered-a.Qtyout as "To Made[dQTY]",
   
         date(a.Reqd) as "Required[dReq]",
   
         a.AddNotes as "Notes[Notes]",
   
         date_format(b.pdate,"%d/%m/%Y") as "Week[pWeek]",
   
         b.pQty as "Prod.Qty[pQTY]"
   
   from schedule a
   
   join p_forecast b on a.OrderID=b.oid
   
   join `item master` c on a.ItemCode=c.`item code`
   
   join customer d on c.`Customer ID`=d.`Customer Code`
   
   where a.Complete="N" order by a.Line,a.Priority,b.pdate;

   I can get (I hope) the orderid and cell value but I don't know how to get the date in an easy way.

   Hope you follow that.

   Cheers

   Jake
2. 

   myDBR Team, Key Master

   Jake,
   what is it that you are trying to do? Mark the cells that do not have value? What happens after you have marked them? Store the missing information to the database or what?

   It probably will be much easier just to use normal dbr.report to do this rather than writing your own JavaScript to do it.

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   This report is to help with production forecasting.

   On first run none of the data cells will be populated. I wish to give users the facility to click into a data cell and one of two things will happen:

   If cell is blank then populate with the dQTY value and change the cell's background colour to green. The orderID, date and dQTY would be written into a table that would then populate the report on subsequent loading. As you can see I check in my javascript to see if the cell is blank and it will only execute if it is.

   If the cell isn't blank I'll use the usual dbr.editable to make allow users to make changes to populated cells.

   So my js is only to cope with blank cells - it is to make it easier for the users in that they don';t have to manually enter the production quantity.

   Hope that makes sense.

   Thanks

   Jake
4. 

   myDBR Team, Key Master

   Jake,
   so the process for the empty cell would be:

   1) User click's on the empty cell and makes it green (to indicate that that cell has been processed?)
   2) The ID and the expected value (?) of the cell is recorded somehow (orderID, date and dQTY) to same/diffrent table
   3) User reloads the page and the values will be fetched? Does the green cell info disappear or do you retain it via recording the default value to different table?

   Why not just do it with dbr.report? Saves you the trouble of the custom JavaScript and gives you easy way to access the database at the same time (inclding date and dQTY).

   --
   myDBR Team
5. 

   ajdjackson, Member

   Hi
   Sorry for the delay in getting back - I'm on hols at the moment so only grabbing the odd hour online now and then.

   With regards to your questions above:

   1) Yes by clicking a cell he is indicating that this week is when the production will happen. So it is turned green.
   2) Yes the order id, week date beginning on a Monday, and the production quantity are written to a separate table. This table is then read when report is loaded and the data is shown in the crosstab. I have an issue with this that I'll address later.
   3) I would prefer the report not to refresh after each data entry as it's quite a large report > 400 rows and spans 6 months.

   I have made some progress and have got most of my initial query solved but I see issues.

   
   
   select 'dbr.javascript',"
   
       $(document).ready(function() {
   
       $('.hiddendiv').hide();
   
       $('table.prodruns th:last-child,table.prodruns td:last-child').css('color', 'white');
   
   // change cell background color if clicked
   
     $('table.prodruns td.cell.align_r.ct_set_start_one').click(function () {
   
      var value = $(this).html();
   
     if (value == '') { 
   
      var cell = $(this),
   
           state = cell.data('state') || 'first';
   
       switch (state) {
   
           case 'first':
   
               cell.addClass('bggreen');
   
               cell.data('state', 'second');
   
               break;
   
           case 'second':
   
               cell.removeClass('bggreen');
   
               cell.data('state', 'first');
   
               break;
   
           default:
   
               break;
   
       }
   
       // add to do value to the clicked cell
   
       var column_num = parseInt( $(this).index() ) + 1;
   
       var tQTY = col_value_get( this, 'todo' );
   
       var OID = col_value_get( this, 'ordid' );
   
       col_value_set( this,column_num, 0, tQTY);
   
       //get pdate
   
               var $cell = $(this);
   
               var cellIndex = $cell.index();
   
               // get the value of the matching header
   
               var pdate = $cell.closest( 'table' ).find( 'th.cell_ct' ).eq( cellIndex-13 ).html();
   
       //insert into table
   
       var url = 'report.php?r=166&u1='+OID+'&u2='+pdate+'&u3='+tQTY+'&m=13&h=3433aea0d235c7c91a2227e239644ff4b8fd6847';
   
       $('.hiddendiv').load(url);
   
       }
   
   });
   
     });
   
   ";

   This works and inserts the correct data in the table. However I now have issues about how to allow user to edit those cells that have been clicked - easy once the page is reloaded - unless dbr.editable will handle those clicked cells - I haven't got that far yet.

   The only reason I'm doing this way rather that using db.editable is that I wish to pass a default value - production quantity. Can dbr.editable pull this value from another column if the cell value is 0? If it could it would make what I'm trying to do a lot easier.

   I have come across a couple of issues to do with the crosstab itself.

   1) I find that dbr.lockcolumns is sketchy when used with crosstabs - not just this one. I wish to lock the last column of the stub and have the data columns flow underneath the locked columns but doesn't work like as I expected (hard to explain). Has it something to with me varying the stub columns's widths?

   2) Strange one this! As I said above when the report is loaded it also reads in data from the separate table that stores the orderid,week, and quantity. I wish to set the cell background colour to green and bold the text if there is data in it. However I have found that if there is data in 1 cell the whole row of data cells are coloured green. I have tracked this down to me declaring the crosstab as follows:

   select 'dbr.crosstab','pWeek','pQTY';

   and the cell I wish to conditionally format is pQTY. If declare the crosstab as select 'dbr.crosstab','pWeek'; only, the conditional formatting works but I get an extra data column in the crosstab which I don't want.

   The conditional formatting is coded as follows:

   
   
   select 'dbr.css','.bggreen {background-color:#66ff33;font-weight:bold;}';
   
   select 'dbr.cellclass', 'pQTY', 'style';
   
   select 'dbr.calc','style', "[pQTY]>0 ? 'bggreen' : ''";
   
   select
   
         a.Line as "Line[Line]",
   
         a.Priority as "Priority[Prior]",
   
         a.ItemCode as "Item Code[ICode]",
   
         c.`Item Description` as "Description[IDesc]",
   
         a.`Order` as "FPO[oFPO]",
   
         d.CustName as "Customer[Cust]",
   
         a.CustCon as "Cust.Control[CustCon]",
   
         a.CustOrder as "Cust.Order[COrder]",
   
         a.QtyOrdered as "Qty.Ord[oQTY]",
   
         a.Qtyout as "Made Qty[mQTY]",
   
         a.QtyOrdered-a.Qtyout as "To Make[dQTY]",
   
         date(a.Reqd) as "Required[dReq]",
   
         a.AddNotes as "Notes[Notes]",
   
         date_format(b.pdate,"%d/%m/%Y") as "Week[pWeek]",
   
         b.pQty as "Prod.Qty[pQTY]",
   
         a.OrderID as "Order ID[OID]",
   
         null as 'style'
   
   from schedule a
   
   join p_forecast b on a.OrderID=b.oid
   
   join `item master` c on a.ItemCode=c.`item code`
   
   join customer d on c.`Customer ID`=d.`Customer Code`
   
   where a.Complete="N" order by a.Line,a.Priority,b.pdate;

   Am I doing something wrong here or is it a bug?

   Many thanks for your help

   Jake
6. 

   myDBR Team, Key Master

   Jake,
   before going into the code, let's try to clarify what are you trying to achive:

   1) Report loads with cells with data and empty cells.
   2) Cells with data are editable, empty cells will be clickable
   3) When user clicks the clickable cell, the cell turns green and in the background cells value is written in a table
   4) User refreshes the report and those green cells will become cells with values and therefore editable, not clickable.

   Question:
   After the click, the cells still shows and empty value but is green. When user reloads the report, the recorded value is shown and the cell is no longer green? Why do you need the green status? Why not just fetch the new value into the cell and make the cell editable so that the user can directly edit it if needed. Why do you force user to refresh before (s)he sees the new values?

   Why not use logic:

   1) Report loads with cells with data and empty cells.
   2) Cells with data are editable, empty cells will be clickable (mabye highlighted)
   3) When user clicks the clickable cell, the default value will be stored into the database, shown in the cell and the cell becomes editable (and the optional cell highlight is removed).

   --
   myDBR Team
7. 

   ajdjackson, Member

   Hi

   1) Yes crosstab loads with a mixture of empty and data in cells. All cells with values in them will be highlighted in green. This signifies that production of that item will happen in that week.

   2) The user then can do one of two things:
   edit cells with data in them. If the edit still results in a value greater than zero then the cell remains highlighted in green.
   If value is zeroed or blanked then the highlight is removed. The data table is updated accordingly.

   If the cell is initially zero (I populate the temporary table with zeroes) then clicking in the cell populates it with the
   quantity to be made and highlights it in green.

   Regarding your question

   After the click, the cells still shows and empty value but is green.

   - no, the cell is made green and the to-be-made quantity is written into the cell (at present I'm using js to write this value into cell). The data table is updated with these new values. So the user sees the value immediately he clicks into empty cell.

   My reason for trying to use js to perform this is that I don't see a way to fetch the to-be-made quantity, your default value, have it update table and show in the cell.

   Hope that makes a bit more sense.

   Cheers

   Jake
8. 

   myDBR Team, Key Master

   OK,
   probably not the easiest use case, but best way of doing this would be following (removes the need for the lengthy JavaScript, hardcoded report URLs and manual Ajax calls) :

   1. Attach dbr.editable to all cells.
   2. Attach dbr.report to empty/zero cells with 'event=dblclick' and set target to embedded target (both dbr.editable and dbr.report by default attach to click-event and will therefore confilct)
   3. Disable the dbr.editable from empty/zero cells.
   4. When user double-clicks the empty/zero cell, the linked report is called (update the db and fetch the value into the cell using mydbr_selected_cell. After this the dbr.editable is re-enabled and the double-click event removed.
   
   

   To disable the dbr.editable initially on cells containing zero:

   select 'dbr.javascript', "
   
   $('.editable.yourcolumn').filter(function(){
   
       return $(this).text() === '0';
   
   }).removeClass('editable').editable('disable');", 'onload';
   
   

   When dbr.report is exeduted return the JavaScript which will set the value to the cell, re-enables the editing and removes the double-click event functionality:

   select 'dbr.javascript', concat("
   
   $(mydbr_selected_cell).text('", v_value ,"').addClass('editable').editable('enable');
   
   mydbr_selected_cell.ondblclick=null;");

   Make sure you use latest version of myDBR so you can use event=dblclick.

   --
   myDBR Team
9. 

   ajdjackson, Member

   Hi

   Many thanks for this - I believe I have got it working.

   I added the following to the report that inserts a new forecast (the dbr.report sp):

   
   
   select 'dbr.javascript', concat("
   
   $(mydbr_selected_cell).text('", v_value ,"').addClass('editable bggreen').editable('enable');
   
   mydbr_selected_cell.ondblclick=null;");
   
   

   I included the .bggreen class to make it green.

   When you said attach the dbr.report to only the zero cells the only way I could get this to work was by doing this:

   
   select 'dbr.cellclass','pQTY','link';
   
   select
   
   ''''
   
   if (b.pQTY>0, 'no_link', '') as 'link'

   When I tried the following I got no rows only headers in the crosstab:

   
   
   select 'dbr.report','sp_DBR_New_Forecast','[pQTY]','hiddendiv','inOID=OID','inDate=pWeek','inValue=dQTY','event=dblclick','show_link=[pQTY]=0';

   Am I doing this the correct way?

   One other minor issue is the value displayed in the double-clicked cells has no commas.

   I tried

   select format(inValue,0,'en_GB') into v_value;

   with/without the 'en_GB' but the value was truncated to the comma in the feeder cell ie 12,300 was displayed as 12 although the value written to the db is correct.

   Only a minor issue - no biggie - but your help has been most appreciated.

   Thanks

   Jake
10. 

    myDBR Team, Key Master

    Jake,
    the show_link-condition needs to be a comparison operator, not an assignment:

    select 'dbr.report','sp_DBR_New_Forecast','[pQTY]','hiddendiv','inOID=OID','inDate=pWeek','inValue=dQTY','event=dblclick','show_link=[pQTY]==0';

    Your version will work as well, it is just longer and therefore harder to maintain. Make sure your myDBR is up-to-date, so the dblclick is supported.

    One other minor issue is the value displayed in the double-clicked cells has no commas.

    To ensure that the default numbers are formatted, you can use cell_value_set-function which will use the user locale for number formatting (do not rely on all users using en_GB, also do not hardcode things):

    select 'dbr.javascript', concat("
    
    $(mydbr_selected_cell).text(cell_value_set(mydbr_selected_cell,", v_value ,")).removeClass('green').editable('enable');
    
    mydbr_selected_cell.ondblclick=null;");

    --
    myDBR Team
11. 

    ajdjackson, Member

    Perfect!

    Many thanks for all your help.

    Cheers

    Jake
12. 

    ajdjackson, Member

    Hi

    Sorry to bother you again but I've got sort of stuck again :)

    Everything described above works but when a user subsequently deletes an entry by either entering 0 or 'emptying' the cell I need to remove the bggreen class, disable editing and make dbclick active on that cell again.

    So far I've tried to get the first 2 of these working but I'm failing.

    I've created a simple editing procedure (the db updating is working) attached to dbr.editable:

    CREATE PROCEDURE `sp_DBR_Edit_Forecast`(inOID int,inPDate date,inVal float) BEGIN

    declare new_val float;

    select ifnull(inVal,0) into new_val;

    update pharmapac.tblProdForecast set fQTY=new_val where fOrderID=inOID and fDate=inPDate;

    if new_val = 0 then

    select 'dbr.javascript', "$(mydbr_selected_cell).removeClass('bggreen').editable('disable');";

    end if;

    END

    No idea what's wrong with the above and I'm unsure as how to make dblclick active on the cell again - mydbr_selected_cell.ondblclick=1;??

    Thanks again.

    Jake
13. 

    ajdjackson, Member

    Hi
    I've made a bit of progress (when I thought about exactly what I was doing). DOH!

    I've attached a call back function of dbr.editable:

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

    var edited_value;   edited_value = col_value_get( this );

    alert(edited_value);

    if ( (edited_value == 0 || edited_value == null) ) {     $(this).removeClass('bggreen').editable('disable');  }   }   ";

    My only issue is how activate/bind the dblclick again to that cell.

    I've tried this $(this).dblclick(); but no joy.

    Thanks

    Jake
14. 

    myDBR Team, Key Master

    If you want to switch between the click-event ( editable) and dblclick-event ( dbr.report ), you need to store the mydbr_selected_cell.ondblclick event handler and then when needed, restore it.

    So when you disable the double-click, before setting the mydbr_selected_cell.ondblclick=null, store the value. You can use jQeury data for this:

    select 'dbr.javascript', concat("
    
    $(mydbr_selected_cell).text(cell_value_set(mydbr_selected_cell,", v_value ,")).removeClass('green').editable('enable');
    
    $(mydbr_selected_cell).data( 'dblclickhandler', mydbr_selected_cell.ondblclick );
    
    mydbr_selected_cell.ondblclick=null;");

    Then when you need to re-enable the double-click event handler, restore the event handler in the callback:

    this.ondblclick = $(this).data( 'dblclickhandler' );

    --
    myDBR Team

Reply

You must log in to post.