Thursday, January 27, 2011

Converting number into words in SQL Server, Java and C#

This sql function and call(fNumToWords)it wher ever u want
1.First convert digit to figures
2.it converts 10-99 digit to figures

create Function dbo.fConvertDigit(@decNumber decimal)
returns varchar(6)
as
Begin
declare
@strWords varchar(6)
Select @strWords = Case @decNumber
When '1' then 'One'
When '2' then 'Two'
When '3' then 'Three'
When '4' then 'Four'
When '5' then 'Five'
When '6' then 'Six'
When '7' then 'Seven'
When '8' then 'Eight'
When '9' then 'Nine'
Else ''
end
return @strWords
end

2 function this will convert 10-99

create Function dbo.fConvertTens(@decNumber varchar(2))
returns varchar(30)
as
Begin
declare @strWords varchar(30)
--Is value between 10 and 19?
If Left(@decNumber, 1) = 1
begin
Select @strWords = Case @decNumber
When '10' then 'Ten'
When '11' then 'Eleven'
When '12' then 'Twelve'
When '13' then 'Thirteen'
When '14' then 'Fourteen'
When '15' then 'Fifteen'
When '16' then 'Sixteen'
When '17' then 'Seventeen'
When '18' then 'Eighteen'
When '19' then 'Nineteen'
end
end
else -- otherwise it's between 20 and 99.
begin
Select @strWords = Case Left(@decNumber, 1)
When '0' then ''
When '2' then 'Twenty '
When '3' then 'Thirty '
When '4' then 'Forty '
When '5' then 'Fifty '
When '6' then 'Sixty '
When '7' then 'Seventy '
When '8' then 'Eighty '
When '9' then 'Ninety '
end
Select @strWords = @strWords + dbo.fConvertDigit(Right(@decNumber, 1))
end
--Convert ones place digit.

return @strWords
end

3.The following function calls above two functions
we need to call this function to see the output.

create function dbo.fNumToWords (@decNumber decimal(12, 2))
returns varchar(300)
As
Begin
Declare
@strNumber varchar(100),
@strRupees varchar(200),
@strPaise varchar(100),
@strWords varchar(300),
@intIndex integer,
@intAndFlag integer

Select @strNumber = Cast(@decNumber as varchar(100))
Select @intIndex = CharIndex('.', @strNumber)
if(@decNumber>99999999.99)
BEGIN
RETURN ''
END
If @intIndex > 0
begin
Select @strPaise = dbo.fConvertTens(Right(@strNumber, Len(@strNumber) - @intIndex))
Select @strNumber = SubString(@strNumber, 1, Len(@strNumber) - 3)
If Len(@strPaise) > 0 Select @strPaise = @strPaise + ' paise'
end
Select @strRupees = ''
Select @intIndex=len(@strNumber)
Select @intAndFlag=2
while(@intIndex>0)
begin
if(@intIndex=8)
begin
Select @strRupees=@strRupees+dbo.fConvertDigit(left(@decNumber,1))+' Crore '
Select @strNumber=substring(@strNumber,2,len(@strNumber))
Select @intIndex=@intIndex-1

end
else if(@intIndex=7)
begin
if(substring(@strNumber,1,1)='0')
begin
if substring(@strNumber,2,1)<>'0'
begin
if (@strRupees<>NULL and substring(@strNumber,3,1)='0'
and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0'
and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0'
and @intAndFlag=2 and @strPaise=NULL)
begin
Select @strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
end

Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
else
begin
Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
end
else
begin
if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and
substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and
substring(@strNumber,7,1)='0' and @intAndFlag=2 and @strPaise='')
begin
Select @strRupees=@strRupees+' and ' + dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
end
Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
end
else if(@intIndex=6)
begin
if(substring(@strNumber,2,1)<>'0' or substring(@strNumber,3,1)<>'0'
and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and
substring(@strNumber,6,1)='0' and @intAndFlag=2 and @strPaise='')
begin

if len(@strRupees) <= 0
begin
if convert(int,substring(@strNumber,1,1)) = 1
begin
Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
Select @intAndFlag=2
end
else
begin
Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
Select @intAndFlag=2
end
end
else
begin
if convert(int,substring(@strNumber,1,1)) = 1
begin
Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
Select @intAndFlag=1
end
end
end
else
begin
if convert(int,substring(@strNumber,1,1)) = 1
begin
Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
end
else
begin
Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
end
end
Select @strNumber=substring(@strNumber,2,len(@strNumber))
Select @intIndex=@intIndex-1
end
else if(@intIndex=5)
begin
if(substring(@strNumber,1,1)='0')
begin
if substring(@strNumber,2,1)<>'0'
begin
if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0'
and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
begin
Select @strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
end
Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
else
begin
Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
end
else
begin
if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0'
and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
begin
--Select @strRupees=@strRupees+'andjo'+dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
end
Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
end
else if(@intIndex=4)
begin
if ( (substring(@strNumber,3,1)<>'0' or substring(@strNumber,4,1)<>'0') and
substring(@strNumber,2,1)='0' and @intAndFlag=2 and @strPaise='')
begin
Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
end
Select @strNumber=substring(@strNumber,2,len(@strNumber))
Select @intIndex=@intIndex-1
end
else if(@intIndex=3)
begin
if substring(@strNumber,1,1)<>'0'
begin
Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Hundred '
Select @strNumber=substring(@strNumber,2,len(@strNumber))

if( (substring(@strNumber,1,1)<>'0' or substring(@strNumber,2,1)<>'0') and
@intAndFlag=2 )
begin
Select @strRupees=@strRupees+' and '
Select @intAndFlag=1
end
Select @intIndex=@intIndex-1
end
else
begin
Select @strNumber=substring(@strNumber,2,len(@strNumber))
Select @intIndex=@intIndex-1
end
end
else if(@intIndex=2)
begin
if substring(@strNumber,1,1)<>'0'
begin
Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))
Select @intIndex=@intIndex-2
end
else
begin
Select @intIndex=@intIndex-1
end
end
else if(@intIndex=1)
begin
if(@strNumber<>'0')
begin
Select @strRupees=@strRupees+dbo.fConvertDigit(@strNumber)
end
Select @intIndex=@intIndex-1

end
continue
end
if len(@strRupees)>0 Select @strRupees=@strRupees+ ' rupees '
IF(len(@strPaise)<>0)
BEGIN
if len(@strRupees)>0 Select @strRupees=@strRupees + ' and '
END
Select @strWords = IsNull(@strRupees, '') + IsNull(@strPaise, '')
select @strWords = @strWords + ' only'
Return @strWords
End
-----------------------------------------------------------------------------------

This is the sql Server Function that will generate String word of the number input...

The Java Function in English Format will be...............................................................................

/**
*
* @author Laxman @link laxmangautam@gmail.com
*/
public class NumberToWord {
/**
* This Method will convert and display the Word of the Input integer Number
* For example it is use full to display the Amount in wrod of Money Value
* In English Format like ###,###,###
* @param inputNum
* @return the string in word of the input.
*/
public static String integerToWords(long inputNum)
{
int dig1, dig2, dig3, level = 0, lasttwo, threeDigits;

String retval = "";
String x = "";
String[] ones ={
"zero",
"one",
"two",
"three",
"four",
"five",
"six",
"seven",
"eight",
"nine",
"ten",
"eleven",
"twelve",
"thirteen",
"fourteen",
"fifteen",
"sixteen",
"seventeen",
"eighteen",
"nineteen"
};
String[] tens ={
"zero",
"ten",
"twenty",
"thirty",
"forty",
"fifty",
"sixty",
"seventy",
"eighty",
"ninety"
};
String[] thou ={
"",
"thousand",
"million",
"billion",
"trillion",
"quadrillion",
"quintillion"
};

boolean isNegative = false;
if (inputNum < 0)
{
isNegative = true;
inputNum *= -1;
}

if (inputNum == 0)
return ("zero");

String s = String.valueOf(inputNum);

while (s.length() > 0)
{
// Get the three rightmost characters
x = (s.length() < 3) ? s : s.substring(s.length() - 3, s.length());

// Separate the three digits
threeDigits = Integer.parseInt(x);
lasttwo = threeDigits % 100;
dig1 = threeDigits / 100;
dig2 = lasttwo / 10;
dig3 = (threeDigits % 10);

// append a "thousand" where appropriate
if (level > 0 && dig1 + dig2 + dig3 > 0)
{
retval = thou[level] + " " + retval;
retval = retval.trim();
}

// check that the last two digits is not a zero
if (lasttwo > 0)
{
if (lasttwo < 20) // if less than 20, use "ones" only
retval = ones[lasttwo] + " " + retval;
else // otherwise, use both "tens" and "ones" array
retval = tens[dig2] + " " + ones[dig3] + " " + retval;
}

// if a hundreds part is there, translate it
if (dig1 > 0)
retval = ones[dig1] + " hundred " + retval;

s = (s.length() - 3) > 0 ? s.substring(0, s.length() - 3) : "";
level++;
}

while (retval.indexOf(" ") > 0)
retval = retval.replace(" ", " ");

retval = retval.trim();

if (isNegative)
retval = "negative " + retval;

return (retval);
}

/**
* @param args the command line arguments
*/
public static void main(String[] args) {
System.out.println("The word or 987654321 "+integerToWords(987654321));
// The out put will be
//The word or 987654321 nine hundred eighty seven million six hundred fifty four thousand three hundred twenty one

}

}

------------------------------------------------------------------------------------------------------------------------
C# Function will be...............
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
MessageBox.Show(IntegerToWords(23) , "System Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}

public string IntegerToWords(long inputNum)
{
int dig1, dig2, dig3, level = 0, lasttwo, threeDigits;

string retval = "";
string x = "";
string[] ones ={
"zero",
"one",
"two",
"three",
"four",
"five",
"six",
"seven",
"eight",
"nine",
"ten",
"eleven",
"twelve",
"thirteen",
"fourteen",
"fifteen",
"sixteen",
"seventeen",
"eighteen",
"nineteen"
};
string[] tens ={
"zero",
"ten",
"twenty",
"thirty",
"forty",
"fifty",
"sixty",
"seventy",
"eighty",
"ninety"
};
string[] thou ={
"",
"thousand",
"million",
"billion",
"trillion",
"quadrillion",
"quintillion"
};

bool isNegative = false;
if (inputNum < 0)
{
isNegative = true;
inputNum *= -1;
}

if (inputNum == 0)
return ("zero");

string s = inputNum.ToString();

while (s.Length > 0)
{
// Get the three rightmost characters
x = (s.Length < 3) ? s : s.Substring(s.Length - 3, 3);

// Separate the three digits
threeDigits = int.Parse(x);
lasttwo = threeDigits % 100;
dig1 = threeDigits / 100;
dig2 = lasttwo / 10;
dig3 = (threeDigits % 10);

// append a "thousand" where appropriate
if (level > 0 && dig1 + dig2 + dig3 > 0)
{
retval = thou[level] + " " + retval;
retval = retval.Trim();
}

// check that the last two digits is not a zero
if (lasttwo > 0)
{
if (lasttwo < 20) // if less than 20, use "ones" only
retval = ones[lasttwo] + " " + retval;
else // otherwise, use both "tens" and "ones" array
retval = tens[dig2] + " " + ones[dig3] + " " + retval;
}

// if a hundreds part is there, translate it
if (dig1 > 0)
retval = ones[dig1] + " hundred " + retval;

s = (s.Length - 3) > 0 ? s.Substring(0, s.Length - 3) : "";
level++;
}

while (retval.IndexOf(" ") > 0)
retval = retval.Replace(" ", " ");

retval = retval.Trim();

if (isNegative)
retval = "negative " + retval;

return (retval);
}
}
}

Thursday, January 20, 2011

PostgreSQL Date and Time Function Examples

PostgreSQL Date and Time Function Examples

now()
now()::date
now()::time
date_part()
age()
extract()
date_trunc()
to_char()
to_timestamp()

1. Get Current Date and Time using PostgreSQL now()

Get current date and time using the following query.

dbase=# select now();
             now
------------------------------
 2010-06-19 09:28:43.98216-07
(1 row)

Use the ::time as shown below to get only the time (without date).

dbase=# select now()::time;
      now
----------------
 09:29:12.19297
(1 row)

Use the ::date as shown below to get only the date (without time).

dbase=# select now()::date;
    now
------------
 2010-06-19
(1 row)

2. Get Interval Between Two PostgreSQL Dates

In order to get the number of days between two dates, use the '-' operator. This works like an arithmetic operator.

Let us assume that the employee table contains following records.

# select name,date_of_join from employee;
   name  | date_of_join
--------+------------
 Neeraj | 2002-11-23
 Kiran  | 2003-01-01
 Sam    | 2005-11-23
 John   | 2006-01-01
(4 rows)

Following examples displays number of days between the current date and date_of_join of employees.

dbase=#  select now() - date_of_join as days from employee ;
           days
---------------------------
 2762 days 08:52:33.436868
 2723 days 08:52:33.436868
 1666 days 08:52:33.436868
  1627 days 08:52:33.436868
(4 rows)

You can also subtract numeric value from the date. This subtracts number of days from a specific date. The following query subtracts 7 days from the date_of_join field value.

dbase=# select date_of_join – 7 as output from employee;
   output
------------
 2002-11-16
 2002-12-25
 2005-11-16
 2005-12-25
(4 rows)

3. Round the interval (above difference) to the nearest day using date_part()

Use the following query to round the number of days to the nearest day.

dbase=# select date_part('days', now() - date_of_join) as days from employee;
 days
 ------
 2762
 2723
 1666
 1627
(4 rows)

4. Breakdown the date interval into number of years, months and days using age()

There is the another way to find the interval between the current date and date_of_join as shown below.

dbase=# select age(date_of_join) from employee;
          age
------------------------
 7 years 6 mons 23 days
 7 years 5 mons 15 days
 4 years 6 mons 23 days
 4 years 5 mons 15 days
 (4 rows)

5. Retrieve any sub-fields from the Timestamp using PostgreSQL extract()

Postgres date functions allows you to extract the specific sub-field form the date. Following query extracts the year from date_of_join field.

dbase=# select extract(year from date_of_join) as output from employee;
 output
--------
   2002
   2003
   2005
   2006
(4 rows)

6. Truncate a particular date field using PostgreSQL date_trunc()

Postgres provides the facility to truncate the date to specific precision. The following query gives you start date of the month based on the value in the date_of_join field.

dbase=# select date_trunc('month',date_of_join) as output from employee ;
       output
------------------------
 2002-11-01 00:00:00-08
 2003-01-01 00:00:00-08
 2005-11-01 00:00:00-08
  2006-01-01 00:00:00-08
(4 rows)

7. Display Postgresql Date in Various Format using to_char()

The following query displays the date in "dd/mm/yy" format.

dbase=# select to_char(date_of_join,'mm/dd/yy') as output from employee;
 output
----------
 11/23/02
 01/01/03
 11/23/05
 01/01/06
(4 rows)

The month and day of date_of_join field can be displayed as shown below.

dbase=# select to_char(date_of_join, 'FMMonth FMDDth') as output from employee;
   output
---------------
 November 23rd
 January 1st
 November 23rd
 January 1st
(4 rows)

Display the full abbreviation of day and month as shown below.

dbase=# select to_char(startdate, 'Dy (Day), Mon (Month)') as output from employee;
              output
----------------------------------
 Sat (Saturday ), Nov (November )
 Wed (Wednesday), Jan (January  )
  Wed (Wednesday), Nov (November )
 Sun (Sunday   ), Jan (January  )
(4 rows)

8. Convert String to Date using PostgreSQL to_timestamp()

Postgres provides the way to convert a string value into proper date format as shown below.

dbase=# select to_timestamp('201024June10:12am', 'YYYYDDFMMonthHH12:MIam') as valid_time;
       valid_time
------------------------
 2010-06-24 10:12:00-07
(1 row)

To changed the Hidden attribute and display the Hidden files...

To View the all hidden files in specific location such as

1. D:\Eclipse_DataBaseExample\microfin\bin>dir /ah

2. D:\Eclipse_DataBaseExample\microfin\bin>dir
01/20/2011 03:26 PM    <DIR>          .svn
               0 File(s)              0 bytes
               1 Dir(s) 36,811,091,968 bytes free

To changed the hidden attribute of specific files run fullowing command..
For example to changed the hidden attribute of .svn type following command..

3. D:\Eclipse_DataBaseExample\microfin\bin>attrib .svn -h -a

Wednesday, January 12, 2011

How to Integrating Jasper reports into SWT

This Rar Files contain all the jar files that To call and View the jasper report in SWT and swing application.....
jar files at..
http://jumbofiles.com/ud7livpynjfg

How To Integrating Jasper reports into SWT

And Exampel Code For SWT application...

Ireport LifeCycle.JPG

LedgerDetails.java Veiw Class Which call and display the .jrxml Files.....

package mcg.microfin.account.view;

import java.sql.Connection;
import java.util.HashMap;
import java.util.Map;

import mcg.microfin.common.db.

DbConnection;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.design.JasperDesign;

import org.eclipse.swt.SWT;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.layout.GridData;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.widgets.Combo;
import org.eclipse.swt.widgets.Composite;
import org.eclipse.swt.widgets.Group;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.Text;
import org.eclipse.ui.part.ViewPart;

import com.jasperassistant.designer.viewer.ViewerComposite;
import com.swtdesigner.SWTResourceManager;

public class LedgerDetails extends ViewPart {
private Button chkExpenses;
private Button chkIncome;
private Button chkLiabilities;
private Button chkAll;
private Button chkAssets;
private Text text;
private Text text_1;
private Composite comLedgerReport;

// Using the SWTJasperViewer to load the Jasper report to the SWT composite
private ViewerComposite viewerComposite;

/// viewerComposite is the viewer composite where report will display......

public LedgerDetails() {

}

@Override
public void createPartControl(Composite parent) {

// base container holding all other components
Composite comContainer = new Composite(parent, SWT.NONE);
comContainer.setFont(SWTResourceManager
.getFont("Segoe UI", 9, SWT.BOLD));

// Form title
Label lblGeneralLedgerDetails = new Label(comContainer, SWT.NONE);
lblGeneralLedgerDetails.setFont(SWTResourceManager.getFont("Tahoma",
10, SWT.BOLD));
lblGeneralLedgerDetails.setBounds(429, 10, 152, 21);
lblGeneralLedgerDetails.setText("General Ledger Details");

// composite for holding the choices for generating report
Composite comChoices = new Composite(comContainer, SWT.NONE);
comChoices.setBounds(10, 10, 190, 709);

// group holding the account type selection
Group grpAccTypeSelection = new Group(comChoices, SWT.NONE);
grpAccTypeSelection.setText("Account Type");
grpAccTypeSelection.setBounds(10, 20, 170, 160);

// checkbox for assets account type
chkAssets = new Button(grpAccTypeSelection, SWT.CHECK);
chkAssets.setBounds(10, 27, 93, 16);
chkAssets.setText("Assets");

// checkbox for expenses account type
chkExpenses = new Button(grpAccTypeSelection, SWT.CHECK);
chkExpenses.setBounds(10, 49, 93, 16);
chkExpenses.setText("Expenses");

// checkbox for income account type
chkIncome = new Button(grpAccTypeSelection, SWT.CHECK);
chkIncome.setBounds(10, 70, 93, 16);
chkIncome.setText("Income");

// checkbox for liabilities account type
chkLiabilities = new Button(grpAccTypeSelection, SWT.CHECK);
chkLiabilities.setBounds(10, 92, 93, 16);
chkLiabilities.setText("Liabilities");

// checkbox for selecting all four account types
chkAll = new Button(grpAccTypeSelection, SWT.CHECK);
chkAll.setBounds(10, 114, 93, 16);
chkAll.setText("All");

// lblFrom label
Label lblFrom = new Label(comChoices, SWT.NONE);
lblFrom.setBounds(10, 209, 55, 15);
lblFrom.setText("From");

// From date
text = new Text(comChoices, SWT.BORDER);
text.setBounds(10, 228, 170, 21);

// lblTo label
Label lblTo = new Label(comChoices, SWT.NONE);
lblTo.setBounds(10, 271, 55, 15);
lblTo.setText("To");

// To date
text_1 = new Text(comChoices, SWT.BORDER);
text_1.setBounds(10, 292, 170, 21);

// label for Account Code
Label lblAccCode = new Label(comChoices, SWT.NONE);
lblAccCode.setBounds(10, 340, 91, 15);
lblAccCode.setText("Account Code");

// combo displaying the list of Account Codes
Combo combo = new Combo(comChoices, SWT.NONE);
combo.setBounds(10, 361, 170, 23);

// label for Account Head
Label lblAccHead = new Label(comChoices, SWT.NONE);
lblAccHead.setBounds(10, 399, 91, 15);
lblAccHead.setText("Account Head");

// composite for holding the ledger table(report)
comLedgerReport = new Composite(comContainer, SWT.NONE);
comLedgerReport.setBounds(206, 39, 802, 648);

// combo displaying the list of Account Heads
Combo combo_1 = new Combo(comChoices, SWT.NONE);
combo_1.setBounds(10, 420, 170, 23);

Button cmdView = new Button(comChoices, SWT.NONE);
cmdView.addSelectionListener(new SelectionAdapter() {
@Override
public void widgetSelected(SelectionEvent e) {

/*
* Calling the generateReport() and passing the acc_code as
* parameter.
*/
JasperPrint jprint = null;
jprint = generateReport(text_1.getText());// requesting jspser report ... it will return jasper print which is going to display under viewerComposite
jprint.setPageHeight(500);
jprint.setPageWidth(730);

try {

// Loading the JasperPrint object in the viewerComposite
viewerComposite.getReportViewer().setDocument(jprint);
viewerComposite.pack();
viewerComposite.setEnabled(true);

} catch (Exception e2) {
e2.printStackTrace();
}
}
});
cmdView.setBounds(10, 449, 75, 25);
cmdView.setText("View");

// Defining an instance of ViewerComposite into the view
viewerComposite = new ViewerComposite(comLedgerReport, SWT.BORDER);
viewerComposite.setBounds(10, 0, 767, 638);
viewerComposite.setLayoutData(new GridData(GridData.FILL,
GridData.FILL, true, true));

}

/**
* generateReport() that receives the parameters to generate the report,
* genereates the final JPrint ready to be outputted and returns the same.
*
* @param accCode
* ()
* @return JasperPrint (An instance of type JasperPrint that is ready to be
* outputted into the screen.)
*/
public static JasperPrint generateReport(String accCode) {
Connection conn = null;
JasperPrint myJPrint = null;

try {

conn = DbConnection.getConnection();

// Loading my jasper file
JasperDesign jasperDesign = null;
JasperReport jasperReport = null;

jasperDesign = JasperManager
.loadXmlDesign(LedgerDetails.class.getClassLoader()
.getResourceAsStream("ledgerReport.jrxml"));

// Incase of directly loading the jasper file.
// jasperReport = (JasperReport) JRLoader
// .loadObject(LedgerDetails.class.getClassLoader()
// .getResourceAsStream("report1.jasper"));

jasperReport = JasperCompileManager.compileReport(jasperDesign);

// Passing parameters to the report
Map<String, Object> parameters = new HashMap();
parameters.put("acc_code", accCode);

/*
* Filling the report with data from the database based on the
* parameters passed.
*/
myJPrint = JasperFillManager.fillReport(jasperReport, parameters,
conn);
parameters.clear();

} catch (JRException ex) {
ex.printStackTrace();
}

return myJPrint;

}

@Override
public void setFocus() {

}

}

///ledgerReport.jrxml is inside in lib/testAccount.jar files.....
--------___________------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MANIFEST.MF

Manifest-Version: 1.0
Bundle-ManifestVersion: 2
Bundle-Name: Microfin
Bundle-SymbolicName: microfin;singleton:=true
Bundle-Version: 1.0.0.qualifier
Bundle-Activator: mcg.microfin.Activator
Require-Bundle: org.eclipse.ui,
org.eclipse.core.runtime
Bundle-ActivationPolicy: lazy
Bundle-RequiredExecutionEnvironment: JavaSE-1.6
Bundle-ClassPath: .,
lib/sqljdbc4.jar,
lib/images.jar,
swt-grouplayout.jar,
lib/commons-beanutils-1.8.2.jar,
lib/commons-collections-3.2.1.jar,
lib/commons-dbcp-1.2.2.jar,
lib/commons-digester-1.7.jar,
lib/commons-javaflow-20060411.jar,
lib/commons-logging-1.1.jar,
lib/groovy-all-1.7.5.jar,
lib/iText-2.1.7.jar,
lib/iTextAsian.jar,
lib/jasperreports-3.7.6.jar,
lib/swtjasperviewer-1.2.0.jar,
lib/testAccount.jar

----------------------- This lib/testAccount.jar is the Report jar files which Contain jrxml and jspser files or each report..
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

build.properties

source.. = src/
output.. = bin/
bin.includes = plugin.xml,\
META-INF/,\
.,\
lib/images.jar,\
lib/sqljdbc4.jar,\
swt-grouplayout.jar,\
lib/commons-beanutils-1.8.2.jar,\
lib/commons-collections-3.2.1.jar,\
lib/commons-dbcp-1.2.2.jar,\
lib/commons-digester-1.7.jar,\
lib/commons-javaflow-20060411.jar,\
lib/commons-logging-1.1.jar,\
lib/groovy-all-1.7.5.jar,\
lib/iText-2.1.7.jar,\
lib/iTextAsian.jar,\
lib/jasperreports-3.7.6.jar,\
lib/swtjasperviewer-1.2.0.jar,\
lib/testAccount.jar

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Final out Put will look like this if all the procedure is goes correctly....

Final Output_jasperReport Clling IN SWT application..JPG

Final Output_jasperReport Clling IN SWT application..JPG

For More Information Use This link..........
http://www.eclipsezone.com/eclipse/forums/t53387.html

Again
If u have any problem then email on laxmangautam@gmail.com

Wednesday, January 5, 2011

Re: Cursor Loop example in MSSQL server,

Cursor Loopin MSSQL server: An Overview

We'll talk about the basics of cursors. These let you move through
rows one at a time and perform processing on each row. (This article
has been updated through SQL Server 2005. and 2008. )

SQL Server is very good at handling sets of data. For example, you can
use a single UPDATE statement to update many rows of data. There are
times when you want to loop through a series of rows a perform
processing for each row. In this case you can use a cursor.

Please note that cursors are the SLOWEST way to access data inside
SQL Server. The should only be used when you truly need to access one
row at a time. The only reason I can think of for that is to call a
stored procedure on each row. In the Cursor Performance article I
discovered that cursors are over thirty times slower than set based
alternatives.

The basic syntax of a cursor is:

DECLARE @AuthorID char(11)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @AuthorID

FETCH NEXT FROM c1
INTO @AuthorID

END

CLOSE c1
DEALLOCATE c1

The DECLARE CURSOR statement defines the SELECT statement that forms
the basis of the cursor. You can do just about anything here that you
can do in a SELECT statement. The OPEN statement statement executes
the SELECT statement and populates the result set. The FETCH statement
returns a row from the result set into the variable. You can select
multiple columns and return them into multiple variables. The variable
@@FETCH_STATUS is used to determine if there are any more rows. It
will contain 0 as long as there are more rows. We use a WHILE loop to
move through each row of the result set.

The READ_ONLY clause is important in the code sample above. That
dramatically improves the performance of the cursor.

In this example, I just print the contents of the variable. You can
execute any type of statement you wish here. In a recent script I
wrote I used a cursor to move through the rows in a table and call a
stored procedure for each row passing it the primary key. Given that
cursors are not very fast and calling a stored procedure for each row
in a table is also very slow, my script was a resource hog. However,
the stored procedure I was calling was written by the software vendor
and was a very easy solution to my problem. In this case, I might have
something like this:

EXEC spUpdateAuthor (@AuthorID)

instead of my Print statement. The CLOSE statement releases the row
set and the DEALLOCATE statement releases the resources associated
with a cursor.

If you are going to update the rows as you go through them, you can
use the UPDATE clause when you declare a cursor. You'll also have to
remove the READ_ONLY clause from above.

DECLARE c1 CURSOR FOR
SELECT au_id, au_lname
FROM authors
FOR UPDATE OF au_lname

You can code your UPDATE statement to update the current row in the
cursor like this

UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1

On 1/4/11, laxman gautam <laxmangautam@gmail.com> wrote:
> Cursor Example In SQl server 2008,
>
> USE [skbbl]
> GO
> /****** Object: UserDefinedFunction [dbo].[get_account_holder]
> Script Date: 01/05/2011 16:01:39 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> ALTER FUNCTION [dbo].[get_account_holder](@acc_no nvarchar(50))
> RETURNS nvarchar(2500)
> WITH EXECUTE AS CALLER
> AS
> BEGIN
> DECLARE @Holder_Name nvarchar(255)
> DECLARE @Ret_Holder_Names nvarchar(255)
>
> DECLARE Cursor_Acc_Holder CURSOR READ_ONLY
>
> FOR
> SELECT m.full_name FROM mem_general_mcg m join sav_acc_holder_mcg sa
> ON m.mid=sa.mid and sa.acc_no=@acc_no
> --SELECT au_id FROM authors
> OPEN Cursor_Acc_Holder
> SET @Ret_Holder_Names=''
>
> FETCH NEXT FROM Cursor_Acc_Holder INTO @Holder_Name
> WHILE @@FETCH_STATUS = 0
> BEGIN
>
> --PRINT @Holder_Name
> SET @Ret_Holder_Names=@Ret_Holder_Names+@Holder_Name+','
> FETCH NEXT FROM Cursor_Acc_Holder
> INTO @Holder_Name
>
> END
>
> CLOSE Cursor_Acc_Holder
> DEALLOCATE Cursor_Acc_Holder
> RETURN @Ret_Holder_Names
> END
>

Cursor Loop example in MSSQL server,

Cursor Example In SQl server 2008,

USE [skbbl]
GO
/****** Object: UserDefinedFunction [dbo].[get_account_holder]
Script Date: 01/05/2011 16:01:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[get_account_holder](@acc_no nvarchar(50))
RETURNS nvarchar(2500)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @Holder_Name nvarchar(255)
DECLARE @Ret_Holder_Names nvarchar(255)

DECLARE Cursor_Acc_Holder CURSOR READ_ONLY

FOR
SELECT m.full_name FROM mem_general_mcg m join sav_acc_holder_mcg sa
ON m.mid=sa.mid and sa.acc_no=@acc_no
--SELECT au_id FROM authors
OPEN Cursor_Acc_Holder
SET @Ret_Holder_Names=''

FETCH NEXT FROM Cursor_Acc_Holder INTO @Holder_Name
WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @Holder_Name
SET @Ret_Holder_Names=@Ret_Holder_Names+@Holder_Name+','
FETCH NEXT FROM Cursor_Acc_Holder
INTO @Holder_Name

END

CLOSE Cursor_Acc_Holder
DEALLOCATE Cursor_Acc_Holder
RETURN @Ret_Holder_Names
END

LaxmanGautam

Thursday, January 27, 2011

Converting number into words in SQL Server, Java and C#

Converting number into words in SQL Server, Java and C#

Thursday, January 20, 2011

PostgreSQL Date and Time Function Examples

1. Get Current Date and Time using PostgreSQL now()

2. Get Interval Between Two PostgreSQL Dates

3. Round the interval (above difference) to the nearest day using date_part()

4. Breakdown the date interval into number of years, months and days using age()

5. Retrieve any sub-fields from the Timestamp using PostgreSQL extract()

6. Truncate a particular date field using PostgreSQL date_trunc()

7. Display Postgresql Date in Various Format using to_char()

8. Convert String to Date using PostgreSQL to_timestamp()

To changed the Hidden attribute and display the Hidden files...

Wednesday, January 12, 2011

How to Integrating Jasper reports into SWT

How To Integrating Jasper reports into SWT

Wednesday, January 5, 2011

Re: Cursor Loop example in MSSQL server,

Cursor Loop example in MSSQL server,

About Me

Blog Archive