Generating SQL for BIRT reports

[Brett]

We want to integrate BIRT reports more closely with OFBiz.  Our BIRT design
templates use standard SQL to generate the reports.   We would like to use
the OFBiz entity engine to generate the SQL so we can leverage our existing
OFBiz application code.

For example, we would like to use a class similar to the GenericDelegator
but instead of retrieving the data from a table the class would generate the
SQL for the report.   We would pass in entity names and conditions as we do
with the Delegator and this new class would generate the SQL for us.

I can see some helper classes like SQLProcessor or GenericDAO that we could
use to help generate the SQL but wanted to know if anyone else has done this
before.

Thanks in advance for your help.


 Brett

[guo weizhan]

I remember BIRT can support the script language and can invoke the
entity method directly. they can work fine with each other.

2008/12/16 Brett Palmer <[hidden email]>:

> We want to integrate BIRT reports more closely with OFBiz.  Our BIRT design
> templates use standard SQL to generate the reports.   We would like to use
> the OFBiz entity engine to generate the SQL so we can leverage our existing
> OFBiz application code.
>
> For example, we would like to use a class similar to the GenericDelegator
> but instead of retrieving the data from a table the class would generate the
> SQL for the report.   We would pass in entity names and conditions as we do
> with the Delegator and this new class would generate the SQL for us.
>
> I can see some helper classes like SQLProcessor or GenericDAO that we could
> use to help generate the SQL but wanted to know if anyone else has done this
> before.
>
> Thanks in advance for your help.
>
>
>  Brett
>

[Brett]

Which script language are you referring to?  Do you mean BIRT's JavaScript
support or something else (bsh, groovy, etc)?

Brett

On Mon, Dec 15, 2008 at 6:20 PM, guo weizhan <[hidden email]> wrote:

> I remember BIRT can support the script language and can invoke the
> entity method directly. they can work fine with each other.
>
> 2008/12/16 Brett Palmer <[hidden email]>:
> > We want to integrate BIRT reports more closely with OFBiz.  Our BIRT
> design
> > templates use standard SQL to generate the reports.   We would like to
> use
> > the OFBiz entity engine to generate the SQL so we can leverage our
> existing
> > OFBiz application code.
> >
> > For example, we would like to use a class similar to the GenericDelegator
> > but instead of retrieving the data from a table the class would generate
> the
> > SQL for the report.   We would pass in entity names and conditions as we
> do
> > with the Delegator and this new class would generate the SQL for us.
> >
> > I can see some helper classes like SQLProcessor or GenericDAO that we
> could
> > use to help generate the SQL but wanted to know if anyone else has done
> this
> > before.
> >
> > Thanks in advance for your help.
> >
> >
> >  Brett
> >
>

[guo weizhan]

I have create jira
before(https://issues.apache.org/jira/browse/OFBIZ-1348) but not
finished all of them, yes, you can use the javascript and create one
special datasource,
here is example code:
public class CreateDataSource {
       
        public static final String module = CreateDataSource.class.getName();
       
        private String viewName ;
       
        public CreateDataSource(String viewName){
                this.viewName = viewName;
        }
       
        public List readData(){
                List dataSources = null;
                try{
                        GenericDelegator delegator = GenericDelegator.getGenericDelegator("default");
                        dataSources = delegator.findAll(viewName);
                }catch( GenericEntityException e ){
                         Debug.logWarning(e.getMessage(), module);
                }
                return dataSources;
        }
}


in design file,something like this:

  <property name="dataSource">OrderItemPriceInfo</property>
            <method name="open"><![CDATA[orderItemPriceInfoClass = new
Packages.org.ofbiz.birt.CreateDataSource("OrderItemPriceInfo");
orderItemPriceInfo = orderItemPriceInfoClass.readData();
totalrows = orderItemPriceInfo.size();
currentrow = 0;]]></method>
            <method name="fetch"><![CDATA[if( currentrow >= totalrows){
        return (false);
}

var gv = orderItemPriceInfo.get(currentrow);

var orderId = gv.get("orderId");
var orderItemSeqId = gv.get("orderItemSeqId");
var productPriceRuleId = gv.get("productPriceRuleId");
var productPriceActionSeqId = gv.get("productPriceActionSeqId");
var modifyAmount = gv.get("modifyAmount");

row["orderId"] = orderId;
row["orderItemSeqId"] = orderItemSeqId;
row["productPriceRuleId"] = productPriceRuleId;
row["productPriceActionSeqId"] = productPriceActionSeqId;
row["modifyAmount"] = modifyAmount;

currentrow = currentrow +1 ;
return (true);]]></method>
            <method name="close"><![CDATA[orderItemPriceInfoClass = null;
orderItemPriceInfo = null;]]></method>


2008/12/16 Brett Palmer <[hidden email]>:

> Which script language are you referring to?  Do you mean BIRT's JavaScript
> support or something else (bsh, groovy, etc)?
>
> Brett
>
> On Mon, Dec 15, 2008 at 6:20 PM, guo weizhan <[hidden email]> wrote:
>
>> I remember BIRT can support the script language and can invoke the
>> entity method directly. they can work fine with each other.
>>
>> 2008/12/16 Brett Palmer <[hidden email]>:
>> > We want to integrate BIRT reports more closely with OFBiz.  Our BIRT
>> design
>> > templates use standard SQL to generate the reports.   We would like to
>> use
>> > the OFBiz entity engine to generate the SQL so we can leverage our
>> existing
>> > OFBiz application code.
>> >
>> > For example, we would like to use a class similar to the GenericDelegator
>> > but instead of retrieving the data from a table the class would generate
>> the
>> > SQL for the report.   We would pass in entity names and conditions as we
>> do
>> > with the Delegator and this new class would generate the SQL for us.
>> >
>> > I can see some helper classes like SQLProcessor or GenericDAO that we
>> could
>> > use to help generate the SQL but wanted to know if anyone else has done
>> this
>> > before.
>> >
>> > Thanks in advance for your help.
>> >
>> >
>> >  Brett
>> >
>>
>

[Brett]

Thanks that was helpful.


Brett

On Mon, Dec 15, 2008 at 7:00 PM, guo weizhan <[hidden email]> wrote:

> I have create jira
> before(https://issues.apache.org/jira/browse/OFBIZ-1348) but not
> finished all of them, yes, you can use the javascript and create one
> special datasource,
> here is example code:
> public class CreateDataSource {
>
>        public static final String module =
> CreateDataSource.class.getName();
>
>        private String viewName ;
>
>        public CreateDataSource(String viewName){
>                this.viewName = viewName;
>        }
>
>        public List readData(){
>                List dataSources = null;
>                try{
>                        GenericDelegator delegator =
> GenericDelegator.getGenericDelegator("default");
>                        dataSources = delegator.findAll(viewName);
>                }catch( GenericEntityException e ){
>                         Debug.logWarning(e.getMessage(), module);
>                }
>                return dataSources;
>        }
> }
>
>
> in design file,something like this:
>
>  <property name="dataSource">OrderItemPriceInfo</property>
>            <method name="open"><![CDATA[orderItemPriceInfoClass = new
> Packages.org.ofbiz.birt.CreateDataSource("OrderItemPriceInfo");
> orderItemPriceInfo = orderItemPriceInfoClass.readData();
> totalrows = orderItemPriceInfo.size();
> currentrow = 0;]]></method>
>            <method name="fetch"><![CDATA[if( currentrow >= totalrows){
>        return (false);
> }
>
> var gv = orderItemPriceInfo.get(currentrow);
>
> var orderId = gv.get("orderId");
> var orderItemSeqId = gv.get("orderItemSeqId");
> var productPriceRuleId = gv.get("productPriceRuleId");
> var productPriceActionSeqId = gv.get("productPriceActionSeqId");
> var modifyAmount = gv.get("modifyAmount");
>
> row["orderId"] = orderId;
> row["orderItemSeqId"] = orderItemSeqId;
> row["productPriceRuleId"] = productPriceRuleId;
> row["productPriceActionSeqId"] = productPriceActionSeqId;
> row["modifyAmount"] = modifyAmount;
>
> currentrow = currentrow +1 ;
> return (true);]]></method>
>            <method name="close"><![CDATA[orderItemPriceInfoClass = null;
> orderItemPriceInfo = null;]]></method>
>
>
> 2008/12/16 Brett Palmer <[hidden email]>:
> > Which script language are you referring to?  Do you mean BIRT's
> JavaScript
> > support or something else (bsh, groovy, etc)?
> >
> > Brett
> >
> > On Mon, Dec 15, 2008 at 6:20 PM, guo weizhan <[hidden email]>
> wrote:
> >
> >> I remember BIRT can support the script language and can invoke the
> >> entity method directly. they can work fine with each other.
> >>
> >> 2008/12/16 Brett Palmer <[hidden email]>:
> >> > We want to integrate BIRT reports more closely with OFBiz.  Our BIRT
> >> design
> >> > templates use standard SQL to generate the reports.   We would like to
> >> use
> >> > the OFBiz entity engine to generate the SQL so we can leverage our
> >> existing
> >> > OFBiz application code.
> >> >
> >> > For example, we would like to use a class similar to the
> GenericDelegator
> >> > but instead of retrieving the data from a table the class would
> generate
> >> the
> >> > SQL for the report.   We would pass in entity names and conditions as
> we
> >> do
> >> > with the Delegator and this new class would generate the SQL for us.
> >> >
> >> > I can see some helper classes like SQLProcessor or GenericDAO that we
> >> could
> >> > use to help generate the SQL but wanted to know if anyone else has
> done
> >> this
> >> > before.
> >> >
> >> > Thanks in advance for your help.
> >> >
> >> >
> >> >  Brett
> >> >
> >>
> >
>