Dynamic column references in SQR

Lets say you are given a snapshot of data as shown in Table-1.

TABLE-1

Emplid Effdt Field Value
100 01-Jan-2000 COMPANY 203
100 01-Jan-2000 BUSINESS_UNIT 00203
100 01-Jan-2000 JOBCODE 320032
100 01-Jan-2000 DEPTID 20302
100 01-Jan-2000 COMPRATE 50000
100 11-Jan-2000 JOBCODE 340045
100 11-Jan-2000 DEPTID 20301
100 01-Feb-2001 COMPRATE 55000

and you are asked to transpose the data, as represented by TABLE-2

TABLE-2

Emplid Effdt Company BU Deptid Jobcode Comprate
100 01-Jan-2000 203 00203 20302 320032 50000
100 11-Jan-2000 20301 340045
100 01-Feb-2001 55000

To achieve this in SQR, the crudest solution is to use a combination of BEGIN-SELECT and BEGIN-SQL.

BEGIN-SELECT
EMPLID
EFFDT
FIELD
VALUE
      do UPDATE-TABLE-2
FROM TABLE-1

Now, lets focus on what would go in UPDATE-TABLE-2 procedure. Every row returned by the BEGIN-SELECT warrants an update on a different column in TABLE-2. A quick solution is to create an update statement for each of the columns BU, DEPTID, JOBCODE etc., and make them execute only when that value occurs in FIELD column.

BEGIN-PROCEDURE UPDATE-TABLE-2

If &FIELD = 'BU'
Begin-SQL
UPDATE TABLE-2
SET BU = &VALUE
WHERE EMPLID = &EMPLID
AND EFFDT = &EFFDT
End-SQL
end-if

IF &FIELD = 'DEPTID'
Begin-SQL
UPDATE TABLE-2
SET DEPTID = &VALUE
WHERE EMPLID = &EMPLID
AND EFFDT = &EFFDT
End-SQL
end-if

!...and continue to create an IF condition each for COMPANY, JOBCODE, COMPRATE etc.

END-PROCEDURE

The crudeness of the solution shows up, as the number of columns in TABLE-2 increases. SQR packs a beautiful technique for dynamic column references. With this technique, the UPDATE-TABLE-2 procedure is compressed and light-weighted as show below:

BEGIN-PROCEDURE UPDATE-TABLE-2

Begin-SQL
UPDATE TABLE-2
SET [&FIELD] = &VALUE
WHERE EMPLID = &EMPLID
AND EFFDT = &EFFDT
End-SQL

END-PROCEDURE

Any variable surrounded by square brackets “[]” is treated as database column in a SQL statement.

Advertisements

Connecting to postgres database in java code

To connect your Java program with Postgresql driver, add the Postgresql library jar (postgresql-jdbc3-8.2.jar or any current jar file) file in the libraries. Then the following code can be modified for your particular database and table.

package javaapplication1;
import java.sql.*;
/**
*
* @author shankar
*/
public class Main {

/**
* @param args the command line arguments
*/
public static void main(String[] args) {
// TODO code application logic here
System.out.println("-------- PostgreSQL JDBC Connection Testing ------------");

try {
Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/sample","postgres", "postgres");
if (connection != null)
{



String query="select ad_alert_id from ad_alert";
ResultSet rs=s.executeQuery(query);
while(rs.next())
{
System.out.print(" "+rs.getString(1));
}


}
else
System.out.println("Connection Failed!");
}
catch(Exception e){
e.printStackTrace();
}

}
}

Email Validation using Callouts in Openbravo

Hi Folks,

I have a written a simple Java code that validates an email ID. This is not a completely validated one, but a basic validated one. This can also be also used as a template for creating Callouts in Openbravo. special mention to my colleague Pandi and one more thanks to Ivan from Openbravo in helping me accomplish this task…:)

package org.openbravo.erpCommon.ad_callouts;

import javax.servlet.ServletException;
import org.apache.log4j.Logger;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.openbravo.erpCommon.ad_callouts.SimpleCallout;
import org.openbravo.erpCommon.utility.SequenceIdData;
import org.openbravo.erpCommon.utility.OBError;
import org.openbravo.scheduling.ProcessBundle;

public class Email_CL extends SimpleCallout {

   private static final long serialVersionUID = 1L;
   private static Logger log = Logger.getLogger(Email_CL.class);

 @Override
 protected void execute(CalloutInfo info) throws ServletException {

   String mailid = info.getStringParameter("inpemail",null);
   // Set the email pattern string
    Pattern p = Pattern.compile(".+@.+\\.[a-z]+");
   
   // Match the given string with the pattern
   Matcher m = p.matcher(mailid.toString());

   // check whether match is found
   boolean matchFound = m.matches();

   StringTokenizer st = new StringTokenizer(mailid, ".");
   String lastToken = null;

    while (st.hasMoreTokens())
    {
       lastToken = st.nextToken();
    }

   //if (matchFound && lastToken.length() >= 2
   //     mailid.length() - 1 != lastToken.length())

   if(!matchFound)
       info.addResult("MESSAGE","InValid Email");
   
 }
}

Importing it into the Application :
Generally callouts are present in the folder ad_callouts in org/openbravo/erpCommon in the source folder of openbravo. You can put your callout file here and then we should define a callout in our application in Application Dictionary under setup folder callout is there. Now Give a name for the callout preferably the name of your java file because the mapping class file and a auto-generated html file will be generated. You check the existing callouts for path and adjust yours accordingly. Then this defined callout should be assigned to the field where the action has to be performed. for the above example it would be the string field where email will be entered. To assign callout to a column go to tables and columns and choose the corresponding column. There will be a field called callout. Choose your name there. Compile and deploy and check your callout.

Note:
To check whether your callout is called properly, u can use the empty frame at the end of each page. For that go to src/org/openbravo/erpCommon/security. There will be a file called Login_FS.Html. In line No.52 the frame ratio would be “100%,*”. Change that too “90%,20%”. This will create a empty text area at the end of every page and if that page has callout, the text “Callout response page” will be displayed. Or else if some error is displayed your path settings in callout definition has to be changed.

Happy Working..:)

Openbravo Environment Setup in Eclipse

I have been using Openbravo for more than an year now and what I noticed is that its a big jungle to enter. You have to learn all about Ubuntu (the preferred platform), then setting up the stack viz, Java, Postgres, PgAdmin(GUI of postgres), Tomcat, Ant, Openbravo, Pentaho(BI tool), etc etc…A good play ground to dig and play, but you can be rest assured your suits wont go unscathed more often than not. This is where you need a good dig tool and IDE’s are just for that task.  Though my personal favorite IDE is Netbeans, Eclipse is the preferred environment for Openbravo, so I decided to go through that route..:)

I went through  a lot of hardships, but nevertheless the result was satisfying and i wanted to bring you that. There is a Eclipse installation Manual (http://wiki.openbravo.com/wiki/ERP/2.50/Developers_Guide/Development_Environment/Setting_up_Development_Environment_with_Eclipse_IDE), but I can assure you that with that link, its bound to take you at least 2 days to complete the installation. So am I going to provide something better than what the Openbravo guys did? Na Na..:) I am just going to shrink and stay up to the point, so that you can have it up and running in a single day.

Importing Openbravo in Eclipse can be subdivided into the following sections.
1. Eclipse setup and configuration
2. Openbravo import and setup
3. Tomcat server and setup

1. Eclipse Setup and configuration:
We can download eclipse from, http://www.eclipse.org/downloads/packages/release/galileo/sr2. Make sure you download the EE version so that most packages are already available. The following are the architectural elements that are needed for Openbravo to run.
1. Subclipse
2. Mercurial
3. Openarchitectureware (for template changes in core)
So once  you have downloaded and installed eclipse (its just a click and choose process), you will have the IDE that resembles something like this..:)

Now we have installed eclipse successfully. First hurdle crossed. Next to add the above listed elements(plugins), go to Help -> Install New Software. Here click the Add Button and provide the following link in the Location Field. http://subclipse.tigris.org/update_1.4.x. In the Name field Provide subclipse or anything for your reference. Then give Next, next and choose finish to install it. Once this is complete eclipse will be restarted. Next lets install Mercurial. For this Use the same Install New Software under Help and use the Add Button to add the Following URL in the Location Field, http://subclipse.tigris.org/update_1.4.x .
Once you provide that, you will get a window similar to that.

If you are installing in ubuntu you can uncheck the windows binaries. Then click next and give finish. The same is to be followed for installing Openarchitectureware, using the following URL, http://www.openarchitectureware.org/updatesite/milestone/site.xml. Once Eclipse is installed, our next step is to import Openbravo.

2. Openbravo import and setup
Go to File -> New -> Other and in the  window that pops out choose Mercurial and the sub option as Clone from Existing Repository. Provide the URL from which you want to clone. If there is no repository, use File -> Import and choose the source folder of Openbravo. Then right click the Project folder and choose Properties. In the source tab add the following folders.

1. src folders of your modules and openbravo src folder
2. src-test folders of your modules and openbravo src-test folder
3. src-gen folder
4. src-core->src
5. src-wad->src
6. src-wad->build->javasqlc->src
7. src-trl->src
8. src-trl->build->javasqlc->src

 

 

 

Add the following libraries in the library tab.

1. lib->runtime
2. lib->build
3. src-db->database->lib

Refresh the project. Next step is to compile this application.

To add ant commands go to Run-> External Tools -> External Tools Configurations. Then double click, “Ant Build” and then you can find a screen as follows.

So you can add new tasks using the small new button and add as many tasks as you want. You just have to give path as provided in Main Tab. And tick the appropriate task in the Targets Tab. Openbravo is configured..:)

3. Tomcat server and setup
To add tomcat 6.-0 server, go to File -> New -> Other and choose tomcat 6.0 as the server and map the corresponding tomcat folder. And then add openbravo to it and give finish.

The eclipse would now appear as follows.

Note Openbravo attached to tomcat server. Now you can right click the project and choose Run ->  “run on server” to deploy Openbravo. Done. Openbravo will be up and running..:)

Happy working..:)

Displaying Images in Openbravo

A picture speaks thousand words. Images are one of the key features of any ERP System. Specially in a feature rich application like Openbravo, images would add more beauty to the window..:) Openbravo provides a table called ad_image that saved image in the form of binary data or in the form of URL. Now in general when you want the reference data from some other table in Openbravo, we use the tabledir to provide direct table mapping or table reference to reference data. For basics on References, refer here. Also for creating external references, refer [1] and [2]. But in the case of ad_image table if you reference it as tabledir, it just comes as a dropdown as attached below.
But that does not show the true power of the image table. To display an image in the grid view and pen view, the “reference” type of the respective column should be set as “Image BLOB”. Then the image will be displayed as shown in the screen-shot below. Whatever the features an application has, User Interface is the main factor that decides the success of it, and I guess subtle things like this will enhance the look and feel of the application a little more..:)
Attached below is the screen shot of the column where image is mapped. Size of the image can be anything..:) since it stores binary data, I gave such high values…

Restoration of Openbravo Environment

All of us depend on backup most of the time. That’s one side-effect of innovative product development. Sometimes when our imagination unfolds in the form of thousands of ideas, the first thing that suffers is the application coz its new for it and the last thing is you got to restore it..:) Sometimes we do development in one environment and recreate the environment elsewhere. I will present how I did it for Openbravo in ubuntu. Openbravo in ubuntu has two parts. One if the folder structure and the other of course is the database. I use postgres as database because of the simple reason that its free. So the first step is i need a backup or a dump of the database. If you have pgadmin installed this is a easier task. Just right click on your database, and choose backup. In the options that present before you, choose the plain option. This option is to create a backup in plain text format that you can see. You can almost sense whats there in that dump with the plain format backup.

Now if you don’t use pgadmin then use the command
pg_dump  -U postgres dbname > nameofbackupfile” for more doubts in this, visit this link http://www.postgresql.org/docs/8.1/static/app-pgdump.html.
We have our database copy now. Next step is copy your current folder structure and transfer it to the destination machine. Next step is restoring the database. This command is really simple..:)
psql -U postgres -d newdb_name -f path_of_backupfile“. Note that you need to create an empty database before restoring into that..:) i forgot his once, so only added this. Next thing there is option restore in Pgadmin but sadly that does not work for plain type database dump that we took.
So we now have a database and folder structure. next step is modify the openbravo properties file and set the source directory and database name accordingly…(refer obwiki for these stuffs if u have doubts, or i ll put another blog about openberavo properties..:) ). So after modifying the properties file, just go ahead and do a complete build and you have the openbravo ready in your new environment…revert back with questions if you have any, in fact i am waiting for many…:)

Getting window and menu details from table name

Hi Folks,

Its quite a tedious task in Openbravo to find a table associated with a window and to find the window , tab and menu for a given table. I have written a small function to provide the window name and menu names given a table name.


CREATE OR REPLACE FUNCTION get_details(tab_name character varying)
RETURNS void AS
$BODY$ DECLARE
	window_id varchar(32);
	window_name varchar(100);
	menu_id varchar(32);
	tmp_men varchar(100);
	menu_name varchar(100)[];
	par_id varchar(32);
	i numeric;
	j numeric;
	Menu varchar(250);

	BEGIN
		i:=0;
		j:=0;
		Menu:='Menu : Openbravo';
		
		---- get window corresponding to table

		Raise Notice '----------------------Table Details----------------------';
		Raise Notice 'Table Name : %',tab_name;
		
			select a.ad_window_id,b.name into window_id,window_name 
			from ad_table a,ad_window b where a.tablename=tab_name and a.ad_window_id=b.ad_window_id;

		Raise Notice '----------------------Window Details----------------------';
		Raise Notice 'Window Id Mapped : %',window_id;
		Raise Notice 'Window Mapped : %',window_name;

			select ad_menu_id,name into menu_id,tmp_men
			from ad_menu where ad_window_id=window_id;
			menu_name[i]=tmp_men;

		Raise Notice '----------------------Menu Details----------------------';
		Raise Notice 'Menu Id : % ',menu_id;
		Raise Notice 'Menu Name : % ',menu_name[i];

			select parent_id into par_id from ad_treenode where node_id=menu_id;
			
        while not par_id='0' loop
			i:=i+1;
			Raise Notice '';

			Raise Notice '----------------------Parent Menu Details----------------------';
			Raise Notice '';
			Raise Notice 'Above Parent id %',par_id;

				select ad_menu_id,name into menu_id,tmp_men from ad_menu where ad_menu_id=par_id;
				menu_name[i]=tmp_men;
			
			Raise Notice 'Level % Menu Name %',i,menu_name[i];
					select parent_id into par_id from ad_treenode where node_id=menu_id;
			--Raise Notice 'Above Parent id %',par_id;
		end loop;

		Raise Notice '----------------------Results----------------------';
		Raise Notice '';
		--Raise Notice 'Level % is the summary level menu and its name is %',i,menu_name[i];
		Raise Notice 'Reqiured Menu is %',menu_name[0];
		Raise Notice '';

		while not i<0 loop
			tmp_men:=menu_name[i];
			Menu:=Menu||' --> ';
			Menu:=Menu||tmp_men;
			j:=j+1;
			i:=i-1;
		end loop;

		Raise Notice '%',Menu; 
		return;
END;   $BODY$
LANGUAGE 'plpgsql' VOLATILE

provide the table name as argument for the function and run it in pgadmin using select.

eg. select get_details(c_order)

Hope its helpful to you. We will soon publish a way to integrate it within the application itself..:)

Happy Working…