Powerbuilder - Extract Menu To Datawindow

1. Create Window, Datawindow and controls

2. Create A sample menu

3. Create window function

 public function boolean of_extract (menu am_menu, integer ai_from, integer ai_level, string as_parent);string     ls_text, ls_code  
 integer li_loop, li_count  
 Long ll_row  
 li_count = UpperBound(am_menu.Item[])  
 for li_loop = ai_from to li_count  
      ls_code = as_parent + string(li_loop,"00")  
      ls_text = am_menu.item[li_loop].Text  
      ll_row = dw_menu.InsertRow(0)  
      dw_menu.SetItem(ll_row,"menu_id", ls_code)  
      dw_menu.SetItem(ll_row,"label_menu", ls_text)  
      dw_menu.SetItem(ll_row,"level_id", ai_level)  
      dw_menu.SetItem(ll_row,"parent_id", as_parent)  
      of_extract(am_menu.Item[li_loop],1, ai_level + 1, ls_code)  
 next  
 return true  
 end function  

4. cb_extract Event Clicked()

 menu lm_main  
 if ddlb_1.text = 'm_main' then  
  lm_main = create m_main  
 else  
  lm_main = create m_sample  
 end if  
 dw_menu.reset()  
 of_extract(lm_main,1,0,'')   
 destroy lm_main  

5.See them in action

6. Cheers..



DOWNLOAD source code from 4Shared

Powerbuilder - Custom Messagebox

1. Create a Response window named w_msg with 1 static text and 3 command button.

2. w_msg event open

 string ls_data,ls_parse  
 integer li_loop,li_count  
 ls_data = message.stringparm  
 for li_loop = 1 to len(ls_data)  
      ls_parse += mid(ls_data,li_loop,1)  
      if right(ls_parse,1) = ',' then  
           ls_parse = left(ls_parse,len(ls_parse) - 1)  
           li_count++  
           choose case li_count  
                case 1  
                     st_msg.text = ls_parse  
                case 2  
                     cb_1.text = ls_parse  
                case 3  
                     cb_2.text = ls_parse  
                case 4  
                     cb_3.text = ls_parse  
           end choose  
           ls_parse = ''  
      end if  
 next  

3. cb_1 event clicked

 closewithreturn(parent,'1')  

4. cb_2 event clicked

 closewithreturn(parent,'2')  

5. cb_3 event clicked

 closewithreturn(parent,'3')  

6. Create Global Function named f_message

 global function integer f_message (string as_text, string as_option1, string as_option2, string as_option3);  
   
 string ls_return,ls_parm  
 ls_parm = as_text+','+as_option1+','+as_option2+','+as_option3+','  
 openwithparm(w_msg,ls_parm)  
 ls_return = message.stringparm  
 return integer(ls_return)  
   
 end function  
   

7. Call Your Custom Messagebox

   
 int li_option  
 li_option = f_message('Web Browser are you using','Chrome','Firefox','Internet Explorer')  
 choose case li_option  
      case 1  
           //Chrome  
      case 2  
           //Firefox  
      case 3  
           //Internet Explorer  
 end choose  

8. See Them in Action

9. Cheers.. :D

What Is PowerBuilder and How do I learn how to use it.

Congratulations on getting to work in a great environment!
Powerbuilder (PB) is an Object Oriented, Event Driven, Graphical User Interface (GUI) environment primarily geared for client / server applications.  PowerBuilder uses its own language, called PowerScript. Powerbuilder is considered a 4th generation language.  (C++ and Java are considered 3rd generation languages.)  With Powerbuilder, you get the object oriented power of the 3rd generation languages along with the GUI front-end of 4th generation languages.

1. Powerbuilder's  first true strength is an object called  the "Datawindow". It will be important for you to learn how to truly use this object and its related control, the datawindow control.  If you become a good programmer
who understands Powerbuilder, you should almost never have to embed SQL in your Powerbuilder applications, as there is virtually no need.  Datawindows allow you to encapsulate your SQL (along with a display format) into an object that can be used throughout your application.

2.  Powerbuilder's second major strength is that it is truly object-oriented - Inheritance, Polymorphism, Encapsulation.  In Powerbuilder, Learn how to create your own classes and inherit from them, making your programs much more robust and often easier to maintain.

3.  Powerbuilder's third major strength is that it can connect to most databases.  Unlike vendor specific tools, Powerbuilder allows the developer the freedom to develop on any database and often through native drivers.  (Much faster, more stable, and better than using ODBC connections, for example.) For example, I created one application for an Access database and was able to place it directly onto an Oracle database without making 30 minutes worth of changes.  Had I used embedded sql, this task would have taken weeks.

There is no other environment that I have used which even comes close to Powerbuilder in client/server.  VB is not object oriented (no inheritance, though VB.Net makes claims that it now has that capability) and does not have near the database manipulation power that Powerbuilder provides.  However, if you have used VB before, then you will be familar with some of the standard controls found in Powerbuilder. Java and C++ may provide a bit more flexibility, performance, and fewer limitations but the design and maintenance costs would be extremely higher than a 4th generation language like Powerbuilder.

PB Books and Tips: 

1. Make sure PowerBuilder's "ONLINE BOOKS" are installed onto your work computer!  This will become your life's blood.

2. Utilize the software package that comes with the PB software (if purchased) called "Infobase".  Infobase will provide you with a lot of great examples of how to implement your code.

3. Get some good PB books and get old issues of Powerbuilder Journal Magazine.  You did not say which version of PB you will be using.  There were some changes with the interface from version 6.5 to 7, but there is an incredible amount of changes from 7 to 8.  You should get a basic fundamentals book and any of the unleashed books.  Start off with the basic book and graduate to the unleashed. (Go to amazon.com and type in powerbuilder to get a list of available books.)
Amazon or Book Pool

4.  Learn your datawindow - Know your datawindow - Love your datawindow. There is an entire book devoted to the datawindow in powerbuilder - get it. :)

5.  Once you have the fundamentals down, begin learning the proper techniques for implementing good Object Oriented Programming.  Learn to create user objects, custom user objects, and non-visual user objects.  Inherit from those user objects rather than recreating a new controls like VB programmers typically do.  (Powerbuilder is only object oriented if the programmer codes that way!)

6.  Graduate into learning Powerbuilder's PFC's (Powerbuilder Foundation Classes) and using "Services".

7.  Did I say Know your datawindow?

8.  Know your SQL.  Powerbuilder offers a nice GUI interface for creating your SQL statements for your Datawindows but it is always best to know how to do it yourself.

9.  Check your local junior colleges for courses on Powerbuilder.

10.  SHIFT-F1 in the script painter brings up Help.  Learn how to use it!

Good Luck and Welcome to Powerbuilder.


Source : Tek-Tips Community

Powerbuilder - Open Window As String

Example for open window as string.

string ls_name
window lw_sheet
ls_name = "w_customer"
open(lw_sheet,ls_name)

As simple as that.
Cheers..

Javascript round() Method

function roundNumber(num, dec) {
 var result = Math.round(num*Math.pow(10,dec))/Math.pow(10,dec);
 return result;
}

and then write code something like this :
roundNumber(dec_number,2)

As simple as that!


Cheers..

Simple Query to Calculate FIFO

I assume that you have table below :
tbl_purchasing
-------------------------------------------------------------------------------------
purchase_number - item_id - date_purchase - unit_cost - qty  - last_qty
-------------------------------------------------------------------------------------
001 - A2 -11/11/2012 - 1000 - 120 - 50
002 - A2 -11/11/2012 - 1100 - 100 - 100
003 - A2 -13/11/2012 - 1350 - 10 - 10
-------------------------------------------------------------------------------------
Note : last_qty is available qty to sale. In this case, it mean 70 was sold.

Sample case :
at 14/11/2012 we need to input sales invoice with item A2 and qty = 152.
How to calculate price using FIFO method?
Aha! as easy as you think.. :p lol
A simple query will solve that :

set @param:=40;
set @counter:=40;

select purchasing.purchase_number, purchasing.sisa,purchasing.unit_cost,
case when @param <= purchasing.sisa then @param else purchasing.sisa end * unit_cost harga,

@param := @param - purchasing.sisa as sampah_masyarakat

from
   (
        select doc_id,sum(last_qty) sisa,unit_cost
        from tbl_purchasing
        where item_id = 'A2'
        group by unit_cost
    order by date_purchase,purchase_number
     ) purchasing
having (harga > 0)

just change 40,A2 into variable and ..
Done!

Very simple, even dont need to create func,proc,trigger, whatever..
Juat a simple single query!
Created by me with some reference from stackoverflow.com

Enjoy..
Cheers!


Note : tested in mysql not sure run in another..

Replace string function

Sample : f_replace(powerbuilder,r,s) will return powesbuildes


f_replace(string as_text,string as_before,string as_after)  returns string

integer li_Posisi
string ls_return

li_Posisi = pos(as_text,as_before)
do while li_Posisi > 0
    ls_return += mid(as_text,1,li_Posisi - 1) + as_after
    as_text = mid(as_text, li_Posisi + Len(as_before))
    li_Posisi = pos(as_text,as_before)
loop
ls_return += as_text

return ls_text

cheers..

Export Datawindow to Excel

Export Datawindow to excel files without dw2xls
First, create a global function

f_excel(datawindow idw)
int li_rtn
string xlsname,nama, ls_alamat
//Check for row(s) in datawindow
if idw.rowcount( ) <= 0 then
        messagebox(' Export Excel','No rows to export!')
        return
end if
//excel file save directory
li_rtn   =   GetFileSaveName( "Select   File",   &  
            xlsname,   nama,   "",   &  
            "Excel 97-2003 Workbook(*.xls) ,*.*,")
if idw.saveas( xlsname+'.xls' , htmltable!, TRUE ) = -1 then
    MessageBox(" Warning", "error!",Exclamation! )
    return ;
end if ;
oleobject excel ;
excel = create oleobject ;
if excel.connecttoobject(xlsname+ '.xls' ) = 0 then
   Excel.Application. DisplayAlerts = false ;
   Excel.Application. Workbooks( 1 ).Parent.Windows
   Excel.Application. workbooks( 1 ).Name ).Visible true ;
   Excel.Application. Workbooks( 1 ).SaveAs(xlsname+ '.xls', 39 ) ;
   Excel.Application. Workbooks( 1 ).close() ;
   messagebox(' Export Excel','datawindow exported succesfully!')
        run("c:\windows\ explorer. exe "+xlsname+'. xls')
end if ;
destroy excel

Sample usage : f_excel(dw_customer)

Cheers..

Keycode to string

With most objects, you can trap the key strokes using user events.
For example, on a datawindow object,
you can create a user event using Event Id = pbm_dwnkey.
In this event, you'll find a parameter of type keycode.
This is a predefined enumerated data type with values such as KeyA!, KeyB!, etc.
But if you want to convert this value to a string,
you'll find your choices are limited.
It looks something like this :

choose case a_key
       case keyA!
            ls_string = "A"
       case keyB!
            ls_string = "B"
       case keyC!
           ls_string = "C"
...
end choose




As simple as that!
Cheers..

Sending Key Press

You can make a control likely pressing (a) key(s). 

First declare the Win32 API modul on Declare > Local External Functions

Subroutine keybd_event( int bVk, int bScan, int dwFlags, int dwExtraInfo) Library "user32.dll"

//Next is an example to simulate pressing "A" key
integer li_vkey
li_vkey = 65 // Character A
sle_1.setfocus() // the desired control to view
keybd_event( li_vkey, 1, 0, 0)


//Another example to simulate "Backspace" key
integer li_vkey
integer li_pos
li_pos = len(sle_1.Text) + 1
sle_1.selectText(li_pos, 0)// Cursor position on last text
li_vkey = asc ("~b") // backspace
keybd_event( li_vkey, 1, 0, 0)


It 100% works.
Cheers..

Import excel into datawindow

Tested in Microsoft Ofice Excel 2003

oleobject excel
long ll_hasil,ll_return,ll_count
boolean lb_cek
excel = create oleobject;
//connect to office application
ll_return = excel.connecttonewobject("excel.application")
if ll_return <> 0 then
messagebox('Peringatan','Tidak dapat mengeksekusi MS.Excel!',exclamation!)
destroy excel
return 0
end if
//open file excel (you can make this string as variable)
excel.workbooks.open( "c:\xls\contoh file excel.xls" )
excel.application.visible = false
excel.windowstate = 2
//cek rows in excel sheet with return value copy
lb_cek = excel.worksheets(3).activate
excel.worksheets(3).range("A2:Z5000").Copy //column range in excel files
ll_count = dw_1.importclipboard()
if ll_count <= 0 then
messagebox('Informasi','Data tidak ditemukan!')
end if
excel.worksheets(3).range("A10000:A10000").Copy
excel.application.quit
excel.disconnectobject()
destroy excel;

How it works?
This script open excel file, read cell and copy to clipboard. And then insert into datawindow.
You also import multiple sheet or multiple excel file.
Cheers..