Hello Team, Thank you for all your valuable help. Thank you Sifiso. Thank you Sangamesh, that exactly is the help I wanted and expected. I was reading about MySQL functions of converting number to word & You have understood my query. I am gonna implement it now and let you know the result mate.
Regards, Abiy A. On Mon, Feb 4, 2019 at 2:29 PM sangamesh n <sangameshc...@gmail.com> wrote: > Dear Abiy, > > Please find the attached screen shot. > > Thanks, > > On Fri, Feb 8, 2019 at 4:48 PM sangamesh n <sangameshc...@gmail.com> > wrote: > >> Hello Abiy, >> >> Que - What I understand is that you want to fetch the Numbers and convert >> it into Words automatically using Pentaho, right? >> Ans - Not sure if Penatho can convert numbers into words.But you could >> convert it through mysql stored routines/functions and fetch result into >> pentaho. To do that, Please follow steps as mentioned below:- >> >> *Step 1* - Go to Heidisql tool, Select database then right click on that >> and click on *Create new* - *Stored routine*. >> Then update the values as mentioned below: >> >> *a) Under Options:- * >> Name - number_to_word >> Definer - root@localhost >> Type - Function (returns as result) >> Data access - Contains SQL >> Returns - text >> SQL security - Definer >> >> *b) Under Routine body:- *Copy and replace below content >> BEGIN >> -- This function returns the string representation of a number. >> -- It's just an example... I'll restrict it to hundreds, but >> -- it can be extended easily. >> -- The idea is: >> -- For each digit you need a position, >> -- For each position, you assign a string >> declare ans varchar(500); >> declare dig1, dig2, dig3, dig4, dig5, dig6 int; >> >> set ans = ''; >> >> set dig6 = CAST(RIGHT(CAST(floor(n / 100000) as CHAR(8)), 1) as SIGNED); >> set dig5 = CAST(RIGHT(CAST(floor(n / 10000) as CHAR(8)), 1) as SIGNED); >> set dig4 = CAST(RIGHT(CAST(floor(n / 1000) as CHAR(8)), 1) as SIGNED); >> set dig3 = CAST(RIGHT(CAST(floor(n / 100) as CHAR(8)), 1) as SIGNED); >> set dig2 = CAST(RIGHT(CAST(floor(n / 10) as CHAR(8)), 1) as SIGNED); >> set dig1 = CAST(RIGHT(floor(n), 1) as SIGNED); >> >> if dig6 > 0 then >> case >> when dig6=1 then set ans=concat(ans, 'one lakh'); >> when dig6=2 then set ans=concat(ans, 'two lakh'); >> when dig6=3 then set ans=concat(ans, 'three lakh'); >> when dig6=4 then set ans=concat(ans, 'four lakh'); >> when dig6=5 then set ans=concat(ans, 'five lakh'); >> when dig6=6 then set ans=concat(ans, 'six lakh'); >> when dig6=7 then set ans=concat(ans, 'seven lakh'); >> when dig6=8 then set ans=concat(ans, 'eight lakh'); >> when dig6=9 then set ans=concat(ans, 'nine lakh'); >> else set ans = ans; >> end case; >> end if; >> >> if dig5 = 1 then >> case >> when (dig5*10 + dig4) = 10 then set ans=concat(ans, ' ten >> thousand '); >> when (dig5*10 + dig4) = 11 then set ans=concat(ans, ' eleven >> thousand '); >> when (dig5*10 + dig4) = 12 then set ans=concat(ans, ' twelve >> thousand '); >> when (dig5*10 + dig4) = 13 then set ans=concat(ans, ' thirteen >> thousand '); >> when (dig5*10 + dig4) = 14 then set ans=concat(ans, ' fourteen >> thousand '); >> when (dig5*10 + dig4) = 15 then set ans=concat(ans, ' fifteen >> thousand '); >> when (dig5*10 + dig4) = 16 then set ans=concat(ans, ' sixteen >> thousand '); >> when (dig5*10 + dig4) = 17 then set ans=concat(ans, ' seventeen >> thousand '); >> when (dig5*10 + dig4) = 18 then set ans=concat(ans, ' eighteen >> thousand '); >> when (dig5*10 + dig4) = 19 then set ans=concat(ans, ' nineteen >> thousand '); >> else set ans=ans; >> end case; >> else >> if dig5 > 0 then >> case >> when dig5=2 then set ans=concat(ans, ' twenty'); >> when dig5=3 then set ans=concat(ans, ' thirty'); >> when dig5=4 then set ans=concat(ans, ' fourty'); >> when dig5=5 then set ans=concat(ans, ' fifty'); >> when dig5=6 then set ans=concat(ans, ' sixty'); >> when dig5=7 then set ans=concat(ans, ' seventy'); >> when dig5=8 then set ans=concat(ans, ' eighty'); >> when dig5=9 then set ans=concat(ans, ' ninety'); >> else set ans=ans; >> end case; >> end if; >> if dig4 > 0 then >> case >> when dig4=1 then set ans=concat(ans, ' one thousand '); >> when dig4=2 then set ans=concat(ans, ' two thousand '); >> when dig4=3 then set ans=concat(ans, ' three thousand '); >> when dig4=4 then set ans=concat(ans, ' four thousand '); >> when dig4=5 then set ans=concat(ans, ' five thousand '); >> when dig4=6 then set ans=concat(ans, ' six thousand '); >> when dig4=7 then set ans=concat(ans, ' seven thousand '); >> when dig4=8 then set ans=concat(ans, ' eight thousand '); >> when dig4=9 then set ans=concat(ans, ' nine thousand '); >> else set ans=ans; >> end case; >> end if; >> if dig4 = 0 AND (dig5 != 0 || dig6 != 0) then >> set ans=concat(ans, ' thousand '); >> end if; >> end if; >> >> if dig3 > 0 then >> case >> when dig3=1 then set ans=concat(ans, 'one hundred'); >> when dig3=2 then set ans=concat(ans, 'two hundred'); >> when dig3=3 then set ans=concat(ans, 'three hundred'); >> when dig3=4 then set ans=concat(ans, 'four hundred'); >> when dig3=5 then set ans=concat(ans, 'five hundred'); >> when dig3=6 then set ans=concat(ans, 'six hundred'); >> when dig3=7 then set ans=concat(ans, 'seven hundred'); >> when dig3=8 then set ans=concat(ans, 'eight hundred'); >> when dig3=9 then set ans=concat(ans, 'nine hundred'); >> else set ans = ans; >> end case; >> end if; >> >> if dig2 = 1 then >> case >> when (dig2*10 + dig1) = 10 then set ans=concat(ans, ' ten'); >> when (dig2*10 + dig1) = 11 then set ans=concat(ans, ' eleven'); >> when (dig2*10 + dig1) = 12 then set ans=concat(ans, ' twelve'); >> when (dig2*10 + dig1) = 13 then set ans=concat(ans, ' thirteen'); >> when (dig2*10 + dig1) = 14 then set ans=concat(ans, ' fourteen'); >> when (dig2*10 + dig1) = 15 then set ans=concat(ans, ' fifteen'); >> when (dig2*10 + dig1) = 16 then set ans=concat(ans, ' sixteen'); >> when (dig2*10 + dig1) = 17 then set ans=concat(ans, ' seventeen'); >> when (dig2*10 + dig1) = 18 then set ans=concat(ans, ' eighteen'); >> when (dig2*10 + dig1) = 19 then set ans=concat(ans, ' nineteen'); >> else set ans=ans; >> end case; >> else >> if dig2 > 0 then >> case >> when dig2=2 then set ans=concat(ans, ' twenty'); >> when dig2=3 then set ans=concat(ans, ' thirty'); >> when dig2=4 then set ans=concat(ans, ' fourty'); >> when dig2=5 then set ans=concat(ans, ' fifty'); >> when dig2=6 then set ans=concat(ans, ' sixty'); >> when dig2=7 then set ans=concat(ans, ' seventy'); >> when dig2=8 then set ans=concat(ans, ' eighty'); >> when dig2=9 then set ans=concat(ans, ' ninety'); >> else set ans=ans; >> end case; >> end if; >> if dig1 > 0 then >> case >> when dig1=1 then set ans=concat(ans, ' one'); >> when dig1=2 then set ans=concat(ans, ' two'); >> when dig1=3 then set ans=concat(ans, ' three'); >> when dig1=4 then set ans=concat(ans, ' four'); >> when dig1=5 then set ans=concat(ans, ' five'); >> when dig1=6 then set ans=concat(ans, ' six'); >> when dig1=7 then set ans=concat(ans, ' seven'); >> when dig1=8 then set ans=concat(ans, ' eight'); >> when dig1=9 then set ans=concat(ans, ' nine'); >> else set ans=ans; >> end case; >> end if; >> end if; >> >> return trim(ans); >> END >> >> *c)Under Parameter:- * >> Click on Add button and type n >> >> d) Click on *Save *button. >> >> *Step 2 *- Test if the value in number converts into word by using >> select query:- >> *select number_to_word(round(x.principal_amount)) from m_loan x where >> x.id <http://x.id>=5* >> >> Output will show in words for example- >> *fifty thousand. * >> >> *Step 3-* Now you could write your sql query and use that in pentaho >> designer and generate pentaho report. >> >> Thanks, >> Sangamesh.Nadagoudar >> Skype - sangamesh.n >> >> >> On Mon, Feb 4, 2019 at 2:47 PM A A <abiy...@gmail.com> wrote: >> >>> Dear All, >>> >>> I am having difficulties in converting the numbers to words in document >>> outputs. Which I need to use for printing receipts. How can I do this? Any >>> ideas? >>> >>> Regards, >>> Abiy A. >>> >>> Mifos-developer mailing list >>> mifos-develo...@lists.sourceforge.net >>> Unsubscribe or change settings at: >>> https://lists.sourceforge.net/lists/listinfo/mifos-developer >> >> Mifos-developer mailing list > mifos-develo...@lists.sourceforge.net > Unsubscribe or change settings at: > https://lists.sourceforge.net/lists/listinfo/mifos-developer
_______________________________________________ Mifos-users mailing list Mifos-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/mifos-users
