Today is Sunday
May 20, 2012

Category: Development

February 18, 2010

Currency amount in words in indian format using T-SQL Stored Procedure

by Ashish Sehajpal — Categories: Development — Tags: , — 2 Comments

I know it is not that much crucial but neither easy to get rid of financial application’s requirements as clients demand more and more facilities even they are small in size.

Here is one that kinda SQL Script that every finance domain expert looks for. That is the currency writer in WORDS instead of digits.

CREATE FUNCTION dbo.AMOUNT_IN_WORDS
(
 @Amount INT,
 @PAISE INT
)
RETURNS int

BEGIN

DECLARE
–Step 1:
–Here we define the temporary tables to hold the WORDs representation of possible digits/amounts

DECLARE @INNTBL_01 TABLE (RECNO INT IDENTITY(1, 1), MTEXT NVARCHAR(50))

DECLARE @INNTBL_02 TABLE (RECNO INT IDENTITY(1, 1), MTEXT NVARCHAR(50))

–Step 2:
–Now add up the salt and pepper onto table  i.e. insert the WORDs into it

INSERT INTO @INNTBL_01 SELECT ‘ONE’
INSERT INTO @INNTBL_01 SELECT ‘TWO’
INSERT INTO @INNTBL_01 SELECT ‘THREE’
INSERT INTO @INNTBL_01 SELECT ‘FOUR’
INSERT INTO @INNTBL_01 SELECT ‘FIVE’
INSERT INTO @INNTBL_01 SELECT ‘SIX’
INSERT INTO @INNTBL_01 SELECT ‘SEVEN’
INSERT INTO @INNTBL_01 SELECT ‘EIGHT’
INSERT INTO @INNTBL_01 SELECT ‘NINE’
INSERT INTO @INNTBL_01 SELECT ‘TEN’
INSERT INTO @INNTBL_01 SELECT ‘ELEVEN’
INSERT INTO @INNTBL_01 SELECT ‘TWELVE’
INSERT INTO @INNTBL_01 SELECT ‘THIRTEEN’
INSERT INTO @INNTBL_01 SELECT ‘FOURTEEN’
INSERT INTO @INNTBL_01 SELECT ‘FIFTEEN’
INSERT INTO @INNTBL_01 SELECT ‘SIXTEEN’
INSERT INTO @INNTBL_01 SELECT ‘SEVENTEEN’
INSERT INTO @INNTBL_01 SELECT ‘EIGHTEEN’
INSERT INTO @INNTBL_01 SELECT ‘NINETEEN’
INSERT INTO @INNTBL_01 SELECT ‘TWENTY’–Similary, insert the multiples

INSERT INTO @INNTBL_02 SELECT ‘TEN’
INSERT INTO @INNTBL_02 SELECT ‘TWENTY’
INSERT INTO @INNTBL_02 SELECT ‘THIRTY’
INSERT INTO @INNTBL_02 SELECT ‘FORTY’
INSERT INTO @INNTBL_02 SELECT ‘FIFTY’
INSERT INTO @INNTBL_02 SELECT ‘SIXTY’
INSERT INTO @INNTBL_02 SELECT ‘SEVENTY’
INSERT INTO @INNTBL_02 SELECT ‘EIGHTY’
INSERT INTO @INNTBL_02 SELECT ‘NINETY’

–Step 3:
–Check for the limit of the amount i.e. what is the place value of digits – LACs, Thousands or Hundreds
DECLARE @WORD VARCHAR(300)
SELECT @WORD = ”

DECLARE @M_AMT01  INT, @M_AMT02 INT

IF @AMOUNT < 10000000 AND @AMOUNT >= 100000 BEGIN
SET @M_AMT01 = @AMOUNT
SELECT @AMOUNT = ( @AMOUNT % 100000 )
SET @M_AMT01 = ( @M_AMT01 – @AMOUNT ) / 100000
DECLARE @WORD1 VARCHAR(300)
SET @WORD1 = ”
IF @M_AMT01 < 100 AND @M_AMT01 > 20 BEGIN
SET @M_AMT02 = @M_AMT01
SET @M_AMT01 = ( @M_AMT01 % 10)
SET @M_AMT02 = ( @M_AMT02 – @M_AMT01 ) / 10
SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_02 WHERE RECNO = @M_AMT02 )
END

IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGIN
SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )
END
SET @WORD = @WORD + @WORD1 + ‘ LAC ‘
END
IF @AMOUNT < 100000 AND @AMOUNT >= 1000 BEGIN
SET @M_AMT01 = @AMOUNT
SET @AMOUNT = ( @AMOUNT % 1000 )
SET @M_AMT01 = ( @M_AMT01 – @AMOUNT ) / 1000
SET @WORD1 = ”
IF @M_AMT01 < 100 AND @M_AMT01 > 20 BEGIN
SET @M_AMT02 = @M_AMT01
SET @M_AMT01 = ( @M_AMT01 % 10 )
SET @M_AMT02 = ( @M_AMT02 – @M_AMT01 ) / 10
SET @WORD1 = ( SELECT @WORD1 + MTEXT + ‘ ‘ FROM @INNTBL_02 WHERE RECNO = @M_AMT02 )
END
IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGIN
SET @WORD1 = ( SELECT @WORD1 + MTEXT +’ ‘ FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )
END
SET @WORD = @WORD + @WORD1 + ‘ THOUSAND ‘
END
IF @AMOUNT < 1000 AND @AMOUNT > = 100 BEGIN
SET @M_AMT01 = @AMOUNT
SET @AMOUNT = ( @AMOUNT % 100 )
SET @M_AMT01 = ( @M_AMT01 – @AMOUNT ) / 100
SET @WORD = ( SELECT @WORD + ‘ ‘ +MTEXT + ‘ HUNDRED ‘ FROM @INNTBL_01 WHERE RECNO = @M_AMT01

)
END
IF @AMOUNT < 100 AND @AMOUNT > 20 BEGIN
SET @M_AMT01 = @AMOUNT
SET @AMOUNT = ( @AMOUNT % 10 )
SET @M_AMT01 = ( @M_AMT01 – @AMOUNT ) / 10
SET @WORD = ( SELECT @WORD + MTEXT + ‘ ‘ FROM @INNTBL_02 WHERE RECNO = @M_AMT01 )
END
IF @AMOUNT <= 20 AND @AMOUNT >= 1 BEGIN
SET @WORD = ( SELECT @WORD + MTEXT +’ ‘ FROM @INNTBL_01 WHERE RECNO = @AMOUNT )
END

–STEP 4:
–Calculate the paise also.
DECLARE @WORDP VARCHAR(300)
SET @WORDP = ”
IF @PAISE <> 0 BEGIN
IF @PAISE < 100 AND @PAISE > 20 BEGIN
DECLARE @PAISE_01 VARCHAR(300)
SET @PAISE_01 = @PAISE
SET @PAISE = ( @PAISE % 10 )
SET @PAISE_01 = ( @PAISE_01 – @PAISE ) / 10
SET @WORDP = ( SELECT @WORDP + MTEXT FROM @INNTBL_02 WHERE RECNO = @PAISE_01 )
END
IF @PAISE <= 20 AND @PAISE >= 1 BEGIN
SET @WORDP = ( SELECT @WORDP + MTEXT FROM @INNTBL_01 WHERE RECNO = @PAISE )
END
SET @WORD = @WORD + ‘AND ‘ + @WORDP + ‘ PAISE’
END

–STEP 5:
–Time to return answer from the function.
RETURN (REPLACE(@WORD, ‘  ‘, ‘ ‘))

–Its done dude. Try by yourself.
END

Now when you need to use this function, simply call it inline to any select statement.

CALLING THE FUNCTION

Select dbo.AMOUNT_IN_WORDS(IM.GRAND_TOTAL, 0) AS words from TBL_INVOICE_MAIN IM

Here, it will convert GRAND_TOTAL field from the table TBL_INVOICE_MAIN.

or more simply,

Select dbo.AMOUNT_IN_WORDS(654321, 0)

Isn’t it simple enough !

February 18, 2010

Create Word Document with Hyperlinks in it with C# code

by Ashish Sehajpal — Categories: Development — Tags: , — Comments Off

First of all you need to add reference to COM library in project.
Add reference to Microsoft word 11.0 word library under COM reference tab Add this in the code behind “using” section

using Microsoft.Office.Interop.Word;
private void CreateWordDocument()
{
// create annonymous object to be referenced throughout
object oMissingReference = System.Reflection.Missing.Value;
object Visible=true;
// define arbitrary starting point for defining range object.
object oStartRange = 0;
object oEndRange = 0;
// create instance of application class
ApplicationClass WordApp = new ApplicationClass();
// create document instance
Document docObject = WordApp.Documents.Add(ref oMissingReference , ref oMissingReference , ref oMissingReference , ref oMissingReference );
// select a range to add some text/image there
Range rng=docObject.Range(ref oStartRange,ref oMissingReference );
rng.Font.Name=”Arial”;
rng.InsertAfter(”Ashish Sehajpal”);
// create Hyperlink object and assign address, screen tip to it
Object address = @”http://www.sehajpal.com”;
Object screenTip = “Welcome to sehajpal.com”;
// adding Hyperlink to the text “Ashish Sehajpal“
rng.Hyperlinks.Add(rng, ref address, ref oMissingReference , ref screenTip, ref oMissingReference , ref oMissingReference );
// give file name to it so as to save the document
object filename = @”C:\createdbycsharp.doc”;
docObject.SaveAs(ref filename, ref oMissingReference , ref oMissingReference , ref oMissingReference , ref oMissingReference , ref oMissingReference ,ref oMissingReference , ref oMissingReference , ref oMissingReference , ref oMissingReference , ref oMissingReference , ref oMissingReference , ref oMissingReference , ref oMissingReference , ref oMissingReference , ref oMissingReference );

WordApp.Visible=true;
}

February 18, 2010

Unable to debug the silverlight project

by Ashish Sehajpal — Categories: Development — Tags: , — Comments Off

I have some strange thing happening in my application. I was able to debug anything but silverlight code !!
I did so much of RnD on net and tried various tricks, but only this thing worked : -

When you add a Silverlight project to a asp.net solution, you create 2 projects…
A. the startup project (interface starter)
B. the interface itself

1. right click on the startup project (SilverlightWeb — the one with default.aspx in it )…
2. click on ‘Property Pages’
3. open ‘Start Options’ Tab
4. enable Silverlight Debugger
5. OK

This was the solution for me

February 18, 2010

Versioning problem in reference.cs in WCF service consumption in Silverlight 2

by Ashish Sehajpal — Categories: Development — Tags: , — 2 Comments

A starnge problem occured when I was trying to add reference to one of my WCF service in my silverlight project. When deeply researched I found that some of the data contracts have been references twice in the reference.cs i.e. some classes with version 3.0.0.0 and some with 2.0.0.0 version. Strange!!

A quick work around was to generate the reference via svcutil.exe and it worked well in case where the project was justa relay service and not the silverlight application. In silverlght app, it throws the error for async and completed methods. Then I used SLSvcutil.exe i.e. svcutil for silverlight which is bundled with silverlight 3 only. I used this and generated the refences.cs for me. One problem left is FAULTCONTRACTS in the code. That i removed manually and it is working now.

February 18, 2010

How to detect resolution of client in WPF?

by Ashish Sehajpal — Categories: Development — Tags: , — 1 Comment

I was wondering about how to detect scrren resolution for client’s machine in wpf. I searched internet, tried googling but in vein.

Here is the snippet that can help the folks looking for similar solution.

// Detect screen height available. Remember it is the inner area of screen.
SystemParameters.FullPrimaryScreenWidth

// Detect screen width available. Remember it is the inner area of screen.
SystemParameters.FullPrimaryScreenHeight;

February 16, 2010

How to open popup or redirect to web page in SilverLight

by Ashish Sehajpal — Categories: Development — Tags: , — 1 Comment

This is the code to open a popup window in SilverLight.

To open a website in a popup using SilverLight, we need to call Window.Navigate method. This method can be used in two different ways i.e. simply navigating to that webpage or using the window option [_blank, _self] etc.

// Navigate to the webpage itself
System.Windows.Browser.HtmlPage.Window.Navigate(new Uri(”http://www.microsoft.com”));

//or if you want to open a popup (in a separate window)
System.Windows.Browser.HtmlPage.Window.Navigate(new Uri(”http://www.microsoft.com”), “_blank”);

February 16, 2010

How to open popup in wpf?

by Ashish Sehajpal — Categories: Development — Tags: , — 1 Comment

This is the code to open a popup window in wpf.

We need to create an empty window for popup and we’ll assign the object of the WPF Window to it. In this example we wre trying to open PopupForm.xaml as popup.

private void OpenPopup()
{
   var oPopupForm = new ucPopupFormPopup();

  // create POPUP window
  var oWindow = new Window();

  //setting up its starting position
  oWindow.WindowStartupLocation =WindowStartupLocation.CenterScreen;

  // we can make it available or not in taskbar
  oWindow.ShowInTaskbar =false;

  oWindow.Width = 630;
  oWindow.Height = 500;
  oWindow.ResizeMode =ResizeMode.NoResize;
  oWindow.Title =”Ashish Sehajpal – Trying WPF”;

  // Access Popup’s Controls
  oPopupForm.TextBox1.Text = “Ashish Sehajpal is here.”;

  // Assign Form to Popup window
  oWindow.Content = oPopupForm;

  // If you need to do some action on popup’s closing
  // Assignt the event handler to it
  oWindow.Closing += oWindow_Closing;
  // it’s time to show POPUP
  oWindow.ShowDialog();

}

  // eventhandler for the popup closeing event
private void oWindow_Closing(object sender, CancelEventArgs e)
{
  // do something nice here
}

February 16, 2010

Calling constructor inside constructor in C#

by Ashish Sehajpal — Categories: Development — Tags: , — Comments Off

To call one C# constructor from another, before the body of the constructor, use either:

: base (parameters)

to call a constructor in the base class; or:

: this (parameters)

to call a constructor in this class.

The following examples illustrate how to call one constructor from another.

public class myPopUp
{
   public myPopUp(int width, int height, myPopUp parentPopUp) : this (width, height)
   {
       this.ParentPopUp = parentPopUp;
   }
}

© 2012 Sehajpal.com All rights reserved - Have fun!