Saturday, April 26, 2014

GOMONTH() on an End Of Month, Beware!!!

What is GOMONTH() Function?

GOMONTH() gives us the ability to move forward/backward on a specified number of months before or after a given Date or Date/Time expression:

Create Cursor junk (xdate d)
ldBase = Date(2014,1,1)
For lnloop = 1 To 12
      Insert Into junk Values (GOMONTH(m.ldBase,1))
      ldBase = xdate
Next
Browse Normal

I have been comfortably using GOMONTH() without any thought of its known bug which is that it returns empty dates before 09/14/1752 because I definitely won't need to go way back that far for any date requirements.

However, I expect that when I perform GOMONTH() on end of month dates, that it will return the end of months of the succeeding months as well.  A fellow Foxite member Gene Wirchenko mentioned that it does not that if dates fall between 28 to 30 (February 28 & 29 plus months that ends on 30), succeeding dates will show the same day; not the end of month.   I tested now and he is right:

Thursday, April 17, 2014

Get a list of Countries and Capitals of the World

Here is a simple snippet to get the countries of the world and its corresponding capitals, again via XMLHttpRequest. And so the result is dependent whether the source site won't change their HTML codes.

Actually, this is just a proposed answer to a member in need inside Foxite as he is looking for a way to get a list of something like this.  I surfed the net a while ago and saw a good site to extract info from and since there is a possibility that  someone out there who is not a member of the forum may need something like this, then here it is:

****************
* Get Countries & Capitals
****************

BizCore Popper

This is something I cannot easily share because its usage is dependent on how I design my ERP app... but hopefully it can give you ideas to do the same on your end.

What is BizCore Popper

Well, originally I plan to call this BizCore Messenger but then there is no originality on that.  And I wanted my apps to sort of look and feel unique.  So I changed the name into Popper (it pops up a message, isn't it?).  So there you go, BizCore Popper.

Brief History 

We actually have a messenger for our LAN here but said messenger do not run under Windows higher than XP.  So it is half used in our office.

My BizCore ERP done in VFP9 runs though on any Windows OS we have so I thought instead of relying onto a 3rd party app, why not create my own?  And then the thought got shelved somewhere in the corner of my mind and remained dormant for a very long time due to a lot other things I do.

However, one time Ony Too shared a new library with a cachy name FoxGrowl inside Foxite which is actually a desktop notification tool.  And my plan on creating a messenger has been reactivated thinking I will turn FoxGrowl into a messenger.  Though the design for FoxGrowl is to notify every one on the LAN and not a specific user, it can be turned into a selective/specific user notification.

But then, I realized I will have to change a lot on said Library to suit my needs that in the end the design will be totally different than the growling notification it is intended to be.  So I abandoned the modification of the library and instead built myself something totally different which is what this ERP is using now.  If you are intrigued on what FoxGrowl is, here is the link showing also the download link:  http://www.foxite.com/archives/0000403119.htm

Wednesday, April 9, 2014

Working on Invisible Objects

I remembered this is another trick I use on my end (mostly in connection with ssToExcel and ssExcelPivot classes) and some may not know that this can be done so here it is.

When I designed both classes, I am still working back then with ssButton series.  While I still sometimes use those, now I prefer mostly ButtonX of ssUltimate library which I started working on after I shared ssClasses in CodePlex; the purpose is to give my subscribers an edge for the monetary contributions they gave me (later I will start showing here the new classes on that new library)

Having said, ButtonX is cleaner, and has improved codes and looks than its ancestors ssButton series.  As I keep on designing buttons, switches and other things over the years on Photoshop, I believe I was able to slowly refine the new looks for those classes.  And so using ssToExcel or ssExcelPivot classes now on my forms will somewhat break the look of my recent GUI designs.

Tuesday, April 8, 2014

Extending ssToExcel & ssExcelPivot classes

For those who are familiar with ssToExcel, ssToExcel2, and ssExcelPivot classes, then they already know that it can easily export records from a table or cursor into Excel with added formats or create a pivot report by setting just a few properties in no time at all.  For those who are wondering what these classes are, then here are my original posts on this:

ssToExcel2:
http://sandstorm36.blogspot.com.au/2012/02/sstoexcel2.html

ssExcelPivot:
http://sandstorm36.blogspot.com.au/2012/12/ssexcelpivot-class-enhancements.html
http://sandstorm36.blogspot.com.au/2012/02/ssexcelpivot-class.html


Those classes are designed to be used generic as users may export different data to excel, in their own particular field ordering based on their needs.  But sometimes though, the generated output of these two classes may still lack something.  Like for instance, while I gave the ability to show total of a numeric columns like this:

      This._totalfields = 'Regular,OT,Holiday,Total'

Which will create a total on those 3 fields (vertically), I have not added a horizontal summation capability.  Why?  Because a user's exported data may vary based on the record source, the last may not be a column representing the numeric total of the fields on its left.

Is there a way to perform extra automation on the sheets generated by these classes?

Excel, Knowing the last row per sheet

Here is a very simple automation trick on how to find out the last row on an Excel Sheet.  This also shows how to find out the number of sheets, how to transfer between those, plus knowing the last row of each sheet.

Enjoy!


#Define xlLastCell 11

Local lcFile, loExcel As Excel.Application, lnLastRow, lnloop
lcFile = Getfile("xls,xlsx")
If !Empty(m.lcFile)   && check if not cancelled
      loExcel = Createobject("excel.application")
      With loExcel
            .Workbooks.Open(m.lcFile)
            For lnloop = 1 To .sheets.Count
                  .Worksheets(m.lnloop).Select
                  .ActiveCell.SpecialCells(xlLastCell).Select
                  lnLastRow = .ActiveCell.Row
                  Messagebox('Sheet'+Transform(m.lnloop)+' last Row'+;
                                     Transform(m.lnLastRow))
            Next
            * Visually Check
            .Visible = .T.
      Endwith
Else
      Messagebox("Aborted by user!",0+64,"Oppppssss!")
Endif

Saturday, April 5, 2014

Localized Data II - Animated Weather

They say a picture paints a thousand words so I will try to show here my weather forecast section and what the animation effect brings to that.

IMHO, this gives a better feel for the users of my app than the normal weather animations we see on the web or on our mobile gadgets.  Plus I always like to have something unique for my apps.

Now, for you be able to achieve the same effect, what you need to do is download animated images from the web, create a table for it, store it to a memo or blob field, and create another field to trap the weather condition which is unique with the web site you will be getting your forecast from.

Like I said with the first blog about this, what I do then on my end is refresh data every 60 seconds and display result on the section of my form for that.  Then I perform an SQL SELECT to get all animations stored on my table which has the same weather condition on the current hour forecast, and finally picks one randomly to be shown above the forecast.

Here is the section of said weather forecast for you to see.  Although the image of the grid representing the forecast is not clear here because I have to resize it larger to fit with the animated image I have shown here whereas on my app, it is the other way around.  The animated image is stretched to fit with the same size of my actual grid below.

Foolish me!  I like this animated image of raining so I chose it.  So as not to confuse the readers, I've overwritten the Cloudy weather with Heavy Rain but forgot to change the caption of Current Weather.  LOL!


And here is the portion where it randomly picks for an animated image:

* Select Animation
Use weather In 0 Shared
Use weathergrp In 0 Shared
With This.imgweather
      .Picture = ''
      * Get an animation based on Weather
      Select t1.wimage, t2.Descript From weather t1;
            LEFT Outer Join weathergrp t2;
            ON t2.wgpk = t1.wgfk;
            WHERE Alltrim(t2.Descript) == m.lcWeather ;
            Into Cursor JunkWeather NOFILTER
      If _Tally > 0
            Goto (1 + _Tally * Rand( ))
            lcImage = Addbs(Getenv("TEMP"))+'weather.gif'
            Strtofile(JunkWeather.wimage,m.lcImage)
            .Picture = m.lcImage
            .Visible = .T.
      Else
            .Visible = .F.
      Endif
      Use In Select('Weather')
      Use In Select('WeatherGrp')
Endwith
      * End of Select Animation

Its advantage is speed, being all data are localized.  You won't see it lagging as both the weather forecast info and weather animation is updated/changed in a split second .

You can achieve the same on your end, just follow the ideas I shared here and in the first part of localized data blog.  Cheers!


Friday, April 4, 2014

Localized Data

Cousin Glen shared a way to put the weather on our form in his blog as well as in foxite last time and so when I decided that I finally needed one because we are working simultaneously on several road construction projects these days and the weather forecast will be helpful, I did not need to look far away and simply checked what he has shared which is via XMLHttpRequest.

What is XMLHttpRequest?

XMLHttpRequest (XHR) is an API available to web browser scripting languages such as JavaScript. It is used to send HTTP or HTTPS requests to a web server and load the server response data back into the script.  http://en.wikipedia.org/wiki/XMLHttpRequest

Using XMLHttpRequest has some (expected) peculiarities like it appears based on my observation that the previous request is compounding seeing my laptop which is doing the data fetching getting slower over time. Thus, I informed him of such and we decided to do these additional things:

Wednesday, April 2, 2014

Combobox using RowSourceType = 3, How to update content?

Well I use this to answer inside foxite forum earlier but then I just thought maybe some does not know yet how to use a cursor from an SQL SELECT as RowSource of a combobox, or if some do, they are maybe wondering how to update such when there is a change from the source table/cursor, in result keeping them away over its usage on a combobox; so I transferred it over here as part of my simple tutorials.

You see, I prefer to work on cursors on my end as that makes my app works faster, less prone to corruption (possibility of corruption may happen only during transferring of data from cursor to actual table), and easier to maintain.

So I use cursors on grids, listbox, combobox, etc.  However, some may have been wondering that if they use a cursor for a combobox RowSource, how to update said combobox then when there is a change in data from the actual table it is coming from?  Or in other words, how to show the changes back to the combobox when it is already set to run on a cursor that was created earlier than when the change on the actual source table/cursor happened?