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…

 

Advertisements

About Shankar Balachandran
Technical Manager, Openbravo operations, Kailao Consulting Private Limited

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

%d bloggers like this: