Configurable Dashboards


Problem Statement :

ADempiere has very good mechanism to show a glimpse of overall  enterprise performance at single location through dashboard concept. Using dashboard, user can see all the required information in one place. But whenever user wants to see a new report on dashboard (something which doesn’t come off-the-shelf), there is a need for customization.

It is three step process

  • Create ZK java file, to show the required content
  • Create Zul file to call Java file
  • Configure Zul file in Dashboard content window

Doing all these steps is time consuming for sure. If there is a way to avoid writing ZK java file and make it configurable then, that will be a good for the development. It saves lot of development and maintenance effort.

Scope Of this Article :

This article describes that way to generate a dash board with minimal configuration changes and without writing single line of code

Technology :

Adempiere 3.6 or above

Detail :

To achieve the desired behaviour ‘Dashboard Content Edit’ window is enhanced to have few more new widgets and changes were made to render the content from existing ‘Window & Tab’ framework

  1. Open ‘Dashboard Content Edit‘ window, it has three new widgetsDashboard
    • is Dynamic Dashboard – Indicates that for this entry, no need to write zul file and it is Dynamic entry
    • Window – Displays list of windows, User can select one window from one its tab will be rendered as dashboard entry
    • Tab – Based on window selection, corresponding tabs will be filtered, Dashboard content will be rendered from this tab
  2. Three more new widgets are also added to support Dynamic zoom functionality on same window i.e. ‘Dashboard Content Edit‘ windowZoomDashboard
    • Event – List of events (actions), user can perform on particular record of the dashboard. As of now, only ‘onClick’ event is supported.
    • Zoom Window – Window to which users needs to be taken, When user clicks on particular record,
    • Tab – Tab to which users needs to be taken, When user clicks on particular record( Zoom option)
  3. Now create new dashboard content entry, to dynamically render the dashboard entryConfigDashboard
  4. Select Zoom option to same window and tabZoomDashBoard
  5. Now logout and login again. Have a look at Dashboard now,  it has cashbook rendered as dashboard entryDashboardonScreen

Code Changes:

To achieve the required behaviour we made changes in following files

  • DefaultDesktop.java:  Added changes from line number 318 to 368 to get and set on context the configured dashboard table, zoom table, zoom window, page size, event and call generic dynamic dashboard .zul file                                                                                                                                 

    // [11-01-2013]

    /*To handle DynamicDashboard. Added new if block
    * If the configuration is dynamic dash board, It finds by using IsDynamicDashboard value. If it is “Y”, Control
    * forwards to the Dynamic Dashboard corresponding zul file path, interns it forwards to DynamicDashboard class.
    * For this few result set values are set to context. */

    if (AD_Window_ID > 0 && isDynamicDashBoard.equals(“Y”)) {

    try {

    Env.setContext(
    Env.getCtx(),
    “#Tab_ID”,
    rs.getInt(X_PA_DashboardContent.COLUMNNAME_AD_Tab_ID));//setting Tab ID to context
    Env.setContext(
    Env.getCtx(),
    “#PageSize”,
    rs.getInt(X_PA_DashboardContent.COLUMNNAME_pagesize));
    Env.setContext(
    Env.getCtx(),
    “#Zoom_Tab_ID”,
    rs.getInt(X_PA_DashboardContent.COLUMNNAME_Zoom_Tab_ID));

    Env.setContext(
    Env.getCtx(),
    “#Zoom_Window_ID”,
    rs.getInt(X_PA_DashboardContent.COLUMNNAME_Zoom_Window_ID));

    Env.setContext(
    Env.getCtx(),
    “#OnEvent”,
    rs.getString(X_PA_DashboardContent.COLUMNNAME_onevent));

    Component component = Executions.createComponents(dynamic_Dashboard_zulFilepath,
    content, null);

    if (component != null) {
    if (component instanceof DashboardPanel) {
    DashboardPanel dashboardPanel = (DashboardPanel) component;
    if (!dashboardPanel.getChildren().isEmpty()) {
    content.appendChild(dashboardPanel);
    dashboardRunnable.add(dashboardPanel);

    panelEmpty = false;
    }
    } else {
    content.appendChild(component);
    panelEmpty = false;
    }
    }
    } catch (Exception e) {
    logger.log(Level.WARNING,
    “Failed to create components. zul=” + dynamic_Dashboard_zulFilepath, e);
    }

    } 

  • DynamicDashBoard.java(New class)   : It handles following functionality.                                                                                           
  1. Get required data from  context and prepared sql query with configured dashboard table selected fields, add zoom table primary key as one more field to sql query if user configured zoom table, appended tab level where clauses with valid convertion  
  2. Functionality to Prepare rows and add events, if user configured events , default/configured page size        
  1. public DynamicDashBoard() {
    		super();
    		// initialize the context values
    		tabID = Env.getContextAsInt(ctx, "#Tab_ID");
    		pageSize = Env.getContextAsInt(ctx, "#PageSize");
    		zoomWindowId = Env.getContextAsInt(ctx, "#Zoom_Window_ID");
    		zoomTabId = Env.getContextAsInt(ctx, "#Zoom_Tab_ID");
    		event = Env.getContext(ctx, "#OnEvent");
    		createView();
    	}
    
    	/**
    	 * createView creates the dynamic dash board view based on configuration
    	 * values if configured, otherwise it creates dash board with default values
    	 */
    	private void createView() {
    		Columns columns = new Columns();
    		prepareSelectQuery();//prepares a select query. 
    
    		int columnsSize = column.length;
    		for (int i1 = 0; i1 < columnsSize; i1++) {
    			if (column[i1] != null) {
    				column[i1].setSort("auto");
    				columns.appendChild(column[i1]);
    			}
    
    		}
    		grid.appendChild(columns);
    		columns.setSizable(true);
    		vbox.appendChild(grid);
    
    		vbox.setStyle("overflow:auto");
    		grid.setMold("paging");
    		if (pageSize <= 0) {
    			grid.setPageSize(MSysConfig.getIntValue(
    					EagleConstants.DASHBOARD_PAGE_SIZE, 5));// default size
    		} else {
    
    			grid.setPageSize(pageSize);
    		}
    		grid.appendChild(createRows());
    		this.appendChild(vbox);
    		grid.renderAll();
    	}
    
    	/**
    	 * 
    	 * @return String<br>
    	 * 
    	 *         This method builds the sql query to generate Result set. It creates Columns and labels as well.
    	 * 
    	 * 
    	 */
    	public String prepareSelectQuery() {
    
    		// Getting isDisplayed fields based on the tab id
    		StringBuffer whereClause = new StringBuffer(X_AD_Tab.COLUMNNAME_AD_Tab_ID + " = " + tabID
    													+ " AND isDisplayed = 'Y'");
    		List<MField> fieldList = new Query(Env.getCtx(), I_AD_Field.Table_Name,
    											whereClause.toString(), null)
    										.setOnlyActiveRecords(true)
    										.list();
    
    		if (fieldList != null && !fieldList.isEmpty()) {
    			lable = new Label[fieldList.size()];
    			column = new Column[fieldList.size()];
    			sqlQuery = sqlQuery.append(" SELECT ");
    
    			// evaluating whether event is enabled
    
    			if (zoomWindowId > 0 && zoomTabId > 0) {
    				whereClause = new StringBuffer("AD_Tab_ID= " + zoomTabId);
    				zoomTab = new Query(Env.getCtx(), I_AD_Tab.Table_Name,
    									whereClause.toString(), null)
    								.setOnlyActiveRecords(true)
    								.first();
    
    				if (zoomTab != null) {
    					// Getting Zoom table based on the zoom tab
    					whereClause = new StringBuffer("AD_Table_ID= "
    							+ zoomTab.getAD_Table_ID());
    					zoomTable = new Query(Env.getCtx(), I_AD_Table.Table_Name,
    										  whereClause.toString(), null)
    										.setOnlyActiveRecords(true)
    										.first();
    
    					if (zoomTable != null) {
    
    						// table or view? based on suffix "_v". It assumed that
    						// created view is having this suffix
    						//@The View Name must be Actual Table Name and It must ends with one of above suffixes @
    						//If this view combination of multiple tables, the view name must be prefix with any one of Joining Tables
    						//And That table should have primary key::: Eg: AD_USer_v, here AD_User_ID is Primary key of AD_User
    
    						zoomTableName = zoomTable.getTableName();
    						if (zoomTable.isView()) {
    							if (zoomTableName.endsWith("_vt")) {
    								zoomTableColumnName = zoomTableName.replace("_vt", "_ID");
    								sqlQuery = sqlQuery.append(zoomTableColumnName+", ");
    							}
    							// if view ends with _v only
    							else if (zoomTableName.endsWith("_v")) {
    								zoomTableColumnName = zoomTableName.replace("_v", "_ID");
    								sqlQuery = sqlQuery.append(zoomTableColumnName+", ");
    							}
    
    						} else {
    							zoomTableColumnName = zoomTable.getTableName()+ "_ID";
    							sqlQuery = sqlQuery.append(zoomTable.getTableName()
    									+ "_ID, ");
    						}
    					}
    				}
    			}
    			int i = 0;
    			for (MField field : fieldList) {
    				if (i != 0) {
    					sqlQuery = sqlQuery.append(", ");
    				}
    				// creating column and preparing sql query with db column names
    				column[i] = new Column(field.getName(), null, null);
    				StringBuffer columnWhereClause = new StringBuffer(
    						"AD_Column_ID = " + field.getAD_Column_ID());
    				MColumn column = new Query(Env.getCtx(),
    											I_AD_Column.Table_Name, columnWhereClause.toString(),null)
    											.setOnlyActiveRecords(true)
    											.first();
    				sqlQuery = sqlQuery.append(column.getColumnName());
    				i = i + 1;
    
    			}
    
    		}
    
    		whereClause = new StringBuffer("AD_Tab_ID= " + tabID);
    		MTab tab = new Query(Env.getCtx(), I_AD_Tab.Table_Name,
    								whereClause.toString(), null)
    								.setOnlyActiveRecords(true)
    								.first();
    		if (tab != null) {
    
    			whereClause = new StringBuffer("AD_Table_ID= "
    					+ tab.getAD_Table_ID());
    			 table = new Query(Env.getCtx(), I_AD_Table.Table_Name,
    					 			whereClause.toString(), null)
    			 					.setOnlyActiveRecords(true)
    			 					.first();
    
    			// Associating sql query with table name 
    			sqlQuery.append(" FROM " + table.getTableName());
    
    			String where = tab.getWhereClause();//get where clause of this tab
    			String orderBy = tab.getOrderByClause();//get orderBy value of this tab
    			if (where != null) {
    				boolean success = true;
    				do {
    					int index = where.indexOf("@");
    					int index2 = where.indexOf("@", index + 1);
    					boolean integer = false;
    					int replacedValue = 0;
    					String replacedString = null;
    
    					if (index > 0 || index2 > 0) {
    						String subString1 = where.substring(index + 1, index2);
    						if (subString1.contains("#")) {
    							if (subString1.endsWith("_ID")) {
    								replacedValue = Env.getContextAsInt(ctx,
    										subString1);
    								integer = true;
    							} else {
    								replacedString = Env
    										.getContext(ctx, subString1);
    							}
    							if (integer) {
    
    								where = where.replaceAll(
    										where.substring(index, index2 + 1),
    										String.valueOf(replacedValue));
    
    							} else {
    								where = where.replaceAll(
    										where.substring(index, index2 + 1), "'"
    												+ replacedString + "'");
    
    							}
    						} else {
    							where = where.replaceAll(
    									where.substring(index, index2 + 1),
    									where.substring(index + 1, index2));
    						}
    						success = true;
    					} else {
    						success = false;
    					}
    
    				} while (success);
    
    				sqlQuery.append(" Where " + where);
    
    			}
    			if(orderBy!=null){
    				sqlQuery.append(" Order By "+orderBy);
    			}
    		}
    
    		return sqlQuery.toString(); 
    	}
    
    	/**
    	 * 
    	 * @return Rows <BR>
    	 * 
    	 * 
    	 *         This method creates the Rows
    	 */
    	public Rows createRows() {
    
    		Vbox msgbox = new Vbox();
    		Label rowItem = null;
    		Rows rows = new Rows();
    		ResultSet rs = null;
    
    		if (rows.getChildren() != null) {
    			List<Component> childs = rows.getChildren();
    			for (Component cmp : childs) {
    				rows.removeChild(cmp);
    			}
    		}
    
    		PreparedStatement pstmt = null;
    		pstmt = DB.prepareStatement(sqlQuery.toString(), null);
    		try {
    			rs = pstmt.executeQuery();
    
    			try {
    				if (rs.next()) {
    					grid.setVisible(true);
    					msgbox.setVisible(false);
    					prepareRow(rs, lable, rows);
    					while (rs.next()) {
    
    						prepareRow(rs, lable, rows);
    
    					}
    
    				} else {
    					vbox.removeChild(msgbox);
    					grid.setVisible(false);
    					msgbox.setVisible(true);
    
    					if (msgbox != null && msgbox.getChildren().size() > 0) {
    						msgbox.removeChild(rowItem);
    					}
    					grid.setVisible(false);
    					msgbox.setVisible(true);
    					String msg = Msg.getMsg(Env.getCtx(), "NO_DATA");
    					rowItem = new Label(msg);
    
    					rowItem.setParent(msgbox);
    					msgbox.setParent(vbox);
    				}
    
    			} catch (SQLException e) {
    				logger.log(Level.SEVERE, "SQL  faile");
    			}
    
    		} catch (Exception e) {
    			logger.log(
    					Level.SEVERE,
    					e.toString()
    							+ "  Check for configured Zoom Tab having Primary key of Table:"
    							+ table.getTableName());
    		} finally {
    			if (rs != null) {
    				try {
    					rs.close();
    				} catch (SQLException e) {
    					logger.log(Level.SEVERE, "Unable to close Resultset:"+e);
    				}
    			}
    		}
    		return rows;
    	}
    
    	/**
    	 * 
    	 * @param rs
    	 * @param labelList
    	 * @param rows
    	 * <BR>
    	 * 
    	 * 
    	 *            This method holding the logic to prepare Row based on
    	 *            ResultSet.
    	 * 
    	 * 
    	 */
    	public void prepareRow(ResultSet rs, Label labelList[], Rows rows) {
    		Row row = null;
    
    		try {
    			ResultSetMetaData rsm = rs.getMetaData();
    
    			int count = rsm.getColumnCount();
    
    			int i = 0;
    			int a = 0;
    			if (zoomWindowId > 0 && zoomTabId > 0) {
    				i = 2;	//Event enabled
    				a = 2;	//To start creating labels from second column as first column is record ID. 
    
    			} else {
    				i = 1;//If Zoom functionality not enabled, start Label creation from first column
    				a = 1;
    
    			}
    
    			row = new Row();
    			//create rows with data
    			while (i <= count) {
    				String name = rsm.getColumnTypeName(i);
    				if (name.equalsIgnoreCase("varchar")) {
    					labelList[i - a] = new Label(rs.getString(i));
    				} else if (name.equalsIgnoreCase("numeric")) {
    					labelList[i - a] = new Label(
    							new Integer(rs.getInt(i)).toString());
    				} else if (name.equalsIgnoreCase("bpchar")) {
    					labelList[i - a] = new Label(rs.getString(i));
    				}else if (name.equalsIgnoreCase("timestamp")) {
    					if (rs.getTimestamp(i) != null) {
    						labelList[i - a] = new Label(rs.getTimestamp(i)
    								.toString());
    
    					} else {
    						labelList[i - a] = new Label(" ");
    					}
    				}
    				i = i + 1;
    
    			}
    			for (int j = 0; j < labelList.length; j++) {
    				row.appendChild(labelList[j]);
    
    			}
    
    			if (zoomWindowId > 0 && zoomTabId > 0) {
    				row.setId(String.valueOf(rs.getInt(1)));
    				if (event == null || event.length() == 0) {
    					event = Events.ON_DOUBLE_CLICK;//default event
    
    				}
    				row.addEventListener(event, this);
    
    			}
    			row.setParent(rows);
    
    		} catch (Exception e) {
    			logger.log(Level.WARNING, "Result set execution failed" + e);
    		}
    
    	}
    
    	public void onEvent(Event event) throws Exception {
    		Component comp = event.getTarget();
    		Row row = (Row) comp;
    		int recordId = new Integer(row.getId());
    		MQuery query = new MQuery();
    		query.setZoomValue(recordId);
    		query.setZoomTableName(zoomTableName);
    		query.setZoomColumnName(zoomTableColumnName);
    		query.setRecordCount(1);
    		query.addRestriction(zoomTableColumnName,MQuery.EQUAL, new Integer(recordId));
    		AEnv.zoom(zoomWindowId, query);
    	}
    
    	public void refresh(ServerPushTemplate template) {
    		if(true){
    			template.execute(this);
    		}
    	}
    	@SuppressWarnings("unchecked")
    	public void updateUI() {
    		Component c = (Component) this.getParent();
    		updateView(); 
    		if(this.getPreviousSibling() != null)
    			this.getPreviousSibling().setParent(null);
    	}
    	private void updateView(){
    		grid.getRows().setParent(null);
    		grid.appendChild(createRows());
    	}
  • DynamicDashBoard.zul : zul file to render DynamicDashboard.java object
    <?xml version="1.0" encoding="UTF-8"?>
    <!-- $Id: activities.zul 1015 2012-02-09 11:28:22Z  $ -->
    <window use="org.adempiere.webui.dashboard.DynamicDashBoard"/>

Important Notes:

  • As we are using Window Tab concept, all the implicit logic like hiding and showing funtionality of widgets are implicitly supported
  • User can configure a different but relevant zoom window tab
  • In dashboard, if the user likes to see data from multiple tables, view can be created and rendered as tab
  • whereClause functionality of tab works as well
  • Existing dashboard functionality is retained as it is, if user likes to go with customization that is still supported
  • Zoom window and Zoom tab selection is not mandatory

Summary:

With simple changes in ADempiere we can make dashboards configurable,Most of Window & Tab functionality brought to Dashboard entries as well.  This functionality saves lot development and maintenance effort.

Walking Tree promotes Adempiere and we support the users as well as the developers to ensure that the business is able to take complete advantage of Adempiere’s wonderful capability. In case you are looking for a professional assistance then do visit our website to get in touch with us.

Tagged with: , ,
Posted in ADempiere, EagleRP

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

We Have Moved Our Blog!

We have moved our blog to our company site. Check out https://walkingtree.tech/index.php/blog for all latest blogs.

Sencha Select Partner Sencha Training Partner
Xamarin Authorized Partner
Recent Publication
%d bloggers like this: