ofbiz to Excel

Export results from ofbiz to Excel:

There is an example of how to do this in the Facilities application.
the example is at https://localhost:8443/facility/control/ViewFacilityInventoryByProduct?facilityId=WebStoreWarehouse

Excel can easily open a well formed XML document and preserve the color and style of the original report.
To output in XML: In the view-map tag in controller.xml change "type=screen" to "type=screenxml" and you will get xml output!

All your FTL code must be in valid XML. That means that <link>

<link rel='stylesheet' href='/assets/css/style.css' type='text/css'> 

is not valid but

<link rel='stylesheet' href='/assets/css/style.css' type='text/css' /> 

is valid.
Other tags to check:

• area
• input
• img
• param
• is not valid. Use &160;instead
  

then, change the attribute content-type="text/xml" to content-type="application/ms-excel"
and users will be prompted to download a file instead of viewing it in the browser. if you change the link and the view-map to control/myPage.xls instead of control/myPage, then windows will offer to open the file with MS Excel.
Note:
Using a https to do this in Internet Explorer may return the message that "Internet explorer cannot download [yourReport] from localhost". If that is the case, you can turn off https for this request. If that is not an option, you must add a header to the response somehow. The view-map tag does not support other headers besides content-type, so this header gets set in the beanshell file with lines like:
fileName = "courseSetCompletionReportsExcel.xls";

// the following instruction prompts the user with option to open report in Excel (instead of in the browser):

response.setHeader( "Content-disposition", "attachment; filename=\"" + fileName + "\"");

//the following seems to have no effect, probably because my headers are set this way somewhere else:
response.setHeader("Cache-Control", "cache");

// next two lines fixed the refusal to download issue:
response.setHeader("Cache-Control", "must-revalidate");
response.setHeader( "Pragma", "public" );

turn off ServerHitBin complaints

When ofbiz falls down a flight of stairs and then writes a novel about it, one of the themes is often ServerHitBin. Message associated with the ServerHitBin error:

If you turn of persistance of visits you should also turn off persistence of hits.

I don't know how to configure persistance of either one, but I'd sure like to. So, how?

I see that this message comes from the ServerHitBin.saveHit method, as a result of checking UtilProperties.propertyValueEqualsIgnoreCase(serverstats ... something)
which, after some grep practice, turns out to be in framework/webapp/config/serverstats.properties
where you can adjust how carefully the webapp tracks visits and hits. I notice some of these options were set to the mysterious troolean value of ftrue.

Now that I turned all the stats collection off, I get no more ServerHitBin errors

EntityWhereString

EntityWhereString simply allows you to add custom SQL syntax to the where clause for an entity operation. It extends EntityCondition so it can be used in the same way.

List pList = new ArrayList();
EntityWhereString conditions = new EntityWhereString("char_length(last_Name)>8");
EntityConditionList assocExprList = new EntityConditionList(UtilMisc.toList(conditions),EntityOperator.AND);

pList = delegator.findByCondition("Person",assocExprList,null,null);

you must use database names and not entity engine names. that is, if the column name in the database is "First_Name" then you can't use FirstName etc.
you can use database specific functions like char_length(), substr(), and the ever useful

EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');

Custom functions could be used here as well.

Consider that this would allow SQL-injection if your application did not adequately scrub parameters coming from the client. This, I suppose, is the reason for this disclaimer in the source code:

 
Encapsulates SQL expressions used for where clause snippets.
NOTE: This is UNSAFE and BREAKS the idea behind the Entity Engine where
you avoid directly specifying SQL. So, KEEP IT MINIMAL and preferably replace
it when the feature you are getting at is implemented in a more automatic way for you.

conditions with EntityWhereString can be strung together with other normal sorts of EntityConditions in the EntityConditionList:

EntityConditionList assocExprList = new EntityConditionList(UtilMisc.toList(conditions,exp1),EntityOperator.AND);

where "conditions" is a EntityWhereString and exp1 is an EntityExpr

Does EntityWhereString work on entity-views??
Hell Yes! Even though the entity-view does not exist as far as the database is concerned, ofbiz passes the SQL where string back to the underlying table. Sweet.

access huge entity-views in entity maintenance

My co-worker showed me this.

Some of my views are too huge for the entity maintenance screen and there is no "find" link to go straight to a subset of one of these views (There is only a link to "all"). If I click "all", I get an "out of memory" error in OFBIZ. Even if I didn't get that error, I don't want to wait 15 minutes for my view to show up.

A way around this is to right-click on the "all" link and select "copy link location" and paste the results into the address bar of the browser. Then, delete the text after your entity name and press enter. Then you will see a find screen with no records for your entity view and be able to look stuff up without calling up the entire view.

Increasing ViewSize in EntityEngine

10 records at a time when viewing an entity-view? Why and how can I change this?

Of course, you can change it in the address bar. Just change the settings for VIEW_SIZE and VIEW_INDEX to navigate around your entity:

https://localhost:8443/webtools/control/FindGeneric
?entityName=CombinedView&find=true&VIEW_SIZE=100&VIEW_INDEX=7660


or, to change the default behavior, I did a search on VIEW_SIZE in *.xml in the ofbiz workspace and I found nothing. A search for VIEW_SIZE in *.* led me to findGeneric.bsh, where I found the code:

String viewSizeString = (String)request.getParameter("VIEW_SIZE");
if (viewSizeString == null || viewSizeString.length() == 0) {
viewSizeString = "10";
}

and changed the "10" to "50" and now my view entities give me a nice fat 50 rows per page. Of course, this will be blown away next time I update ofbiz, so it may be easier to just edit the address bar.

SQL-injection and OFBIZ

One of the great things about the OFBIZ entity engine is that it is well nigh impossible to make SQL-injections against it.

None of the web-application security scans I've done against OFBIZ applications have ever yielded a SQL injection vulnerability and it isn't because of careful parameter scrubbing. Looking at the code, I can't really see how any SQL injection could work. I'm not ready to declare that OFBIZ is completely free of SQL injection problems, but it is beyond me to find any.

Of course, one gives this protection up when switching to JDBC and the limitations of the entity engine may just induce one to do that. Take security into consideration when making that decision.

GenericPK

A google search for ofbiz GenericPK will return the code for this class, but no explanation of what it is for. All the code examples in the OFBIZ codebase seem to use it in conjuction with GenericValue so that one may use a GenericValue retrieved from the EntityEngine to populate a an arbitrary or Generic PK for use with an operation like FindByPrimaryKey or RemoveByPrimaryKey. In other words, you got some data from somewhere and you would like to run a "PrimaryKey" operation. So first convert the data to a GenericPK and then use that for your operation.