MySql select the first row for each group

In the following table the enrollment of students in different program are shown. I want to select the last programme (ordered by date) in which each of the student is enrolled in.

 

  
id
  
studentId
  
programmeId
  
startDate
  
endDate
 

 

  
1
  
444
  
101
  
2012-01-01
  
2012-06-01
 

 

  
2
  
555
  
101
  
2012-01-01
  
2012-06-01
 

 

  
3
  
666
  
101
  
2012-01-01
  
2012-06-01
 

 

  
4
  
444
  
201
  
2012-06-15
  
2012-12-20
 

 

  
5
  
555
  
301
  
2011-07-01
  
2012-02-01
 

 

  
6
  
666
  
201
  
2012-06-15
  
2012-12-20
 

 

  
7
  
444
  
301
  
2011-07-01
  
2012-02-01
 

We have to make group of students and sort the programmes for each student by the order (descending) of their programme-end-Dates, and then select the first programme from each student-group. And the result should look like this.

 

  

   studentId
  
  

   programme
  
 
 

  

   444
  
  

   201
  
 
 

  

   555
  
  

   101
  
 
 

  

   666
  
  

   201
  
 

To generate the above result the query can be written like this:

select
    R.studentId,
    (
        select
            R1.programmeId
        from
            Registration R1
        where
            R1.studentId=R.studentId
        order by
            R1.endDate DESC limit 1
    ) as "programme"
from
    Registration R
group by
    R.studentId;

MySql: Select Query- Make A Cumulative Sum Column

Suppose we have a table like following:

 

  
transaction_id
  
acc_no
  
amount
 
 

  
1
  
100001
  
20000
 
 

  
2
  
100002
  
30000
 
 

  
3
  
100003
  
30000
 
 

  
4
  
100004
  
10000
 
 

  
5
  
100005
  
50000
 

And now we want a query that select a cumulative sum column along with other columns like following:

 

  
transaction_id
  
amount
  
cumulative
 
 

  
1
  
20000
  
20000.0
 
 

  
2
  
30000
  
50000.0
 
 

  
3
  
30000
  
80000.0
 
 

  
4
  
10000
  
90000.0
 
 

  
5
  
50000
  
140000.0
 

We can use pl/sql variables like below to generate the above result:

select
    transaction_id,
    amount,
    (@cum_sum:=@cum_sum+amount) as "cumulative"
from
    account
    JOIN (select @cum_sum := 0.0) B;

MySql Query: PLSQL Conditions within query

Suppose we have a table like:

 

  
transaction_id
  
acc_no
  
amount
 
 

  
1
  
100001
  
20000
 
 

  
2
  
100002
  
30000
 
 

  
3
  
100003
  
30000
 
 

  
4
  
100004
  
10000
 
 

  
5
  
100005
  
50000
 

Now the query

select
    acc_no,
    SUM(IF(amount>20000,amount,0)) as total_value
from
    account
where
    acc_no=100002
group by acc_no;

will produce the following result:

 

   

      
acc_no
      
amount
   
   

      
100002
      
80000
    

Case statements can also be used within a query like shown in the answer of this stackoverflow question

Load/Import huge database in MySQL

phpMyAdmin doesn't allow importing database of huge size, so we have to do it from command prompt.

Go to command prompt:

cd ".../mysql5.5.16/bin"
mysql -u root -p[password if any, otherwise keep blank]
use dbname;
source .../.../dbname.sql;

Here dbname is the name of your database.

wxPython TextCtrl Handling KayEvent 1

The following code shows how we can Bind a wx.EVT_KEY_UP KeyEvent with a TextCtrl widget:

import wx

class MyForm(wx.Frame):

    def __init__(self):
        wx.Frame.__init__(self, None, wx.ID_ANY, "Show Caret Position", size=(400, 140))
        panel = wx.Panel(self, wx.ID_ANY)
        sizer = wx.BoxSizer(wx.VERTICAL)
        text = wx.StaticText(panel, -1, "Text:", (10, 22))
        self.textCtrl = wx.TextCtrl(
                panel,
                -1, "",
                (50,5),
                size=(250, 50),
                style=wx.TE_MULTILINE
            )
        self.textCtrl.SetInsertionPoint(0)
        self.textCtrl.Bind(wx.EVT_KEY_UP,self.onTextKeyEvent)
        self.statusbar = self.CreateStatusBar(1)
        panel.SetSizerAndFit(sizer, wx.VERTICAL)

    def onTextKeyEvent(self, event):
        statusText = "Caret Position: "+str(self.textCtrl.GetInsertionPoint())
        self.SetStatusText(statusText,0)
        event.Skip()


#Run application
if __name__ == "__main__":
    app = wx.PySimpleApp()
    frame = MyForm()
    frame.Show()
    app.MainLoop()

Create and use preverified JAR files in Blackberry applications

This post "blackberry-tips-bikas.blogspot.com: how-to-create-library-jar-file-and-use" describes how a JAR file can be created and used as a library in Blackberry applications. But I faced some problem after following the approach described there. Then I've tried by skipping the step 5 of "How to create the JAR file" and succeeded. I'm describing the full process again here for both Eclipse and JDE:

For Eclipse:

Create the JAR file:

-- Create your Blackberry project (e.g. "MyLib")
-- Clean and build the project after adding all necessary files in the "src" folder and all necessary
    resource files in the "res" folder.
-- Package the project: right click on the project--> BlackBerry --> Package Projects
-- The "MyLib.jar" file will be created in the "ProjectDirectory\deliverables\Standard\X.0.0\" directory
    (here 'X' will be different depending on the version of OS set for your project.)
-- Go to your Eclipse JDE plugins installation folder (e.g. "C:\Eclipse"

-- Go to "C:\Eclipse\plugins\net.rim.ejde.componentpack5.0.0_5.0.0.25\components\bin\"
    The "componentpack5.0.0_5.0.0.25" can be different depending on your Eclipse JDE plugin version.

-- Copy MyLib.jar file into the "...\components\bin\" folder.
-- Open command prompt and change your current directory to the bin directory
    (>cd "C:\Eclipse\plugins\net.rim.ejde.componentpack5.0.0_5.0.0.25\components\bin\")
-- Then execute the following command:
    preverify -classpath "C:\Eclipse\plugins\net.rim.ejde.componentpack5.0.0_5.0.0.25\components\lib\net_rim_api.jar"  "MyLib.jar"
-- A folder named "output" will be created in the bin directory. The preverified jar file will be found in
   the output directory. 
-- Now extract the jar file with WinZip or such other application. Your project folders and files will be
    extracted there.

-- Delete the .cod, .cls, .cso files from there.
-- Make jar again with the remaining files and folders (i.e. make zip and rename to .jar).
-- Now you can use this jar file in another project as a library.

Use the JAR in other projects:

-- Copy the jar and paste into the lib folder of your project.
-- Right click on the jar file just pasted, and click "Add to Build Path"
-- Right click on the project, go to Build Path --> Configure Build Paths --> Java Build Paths. Select the "Libraries" tab and you will see your jar file in the list of libraries there.
-- Select the "Order and Export" tab and check the checkbox of "MyLib.jar". Press 'Ok'.
-- Your jar is added to your project.

For JDE:

 Create the JAR file:

-- Create your workspace (e.g. MyLib.jdw)
-- Create your project (e.g. MyLib.jdp)
-- Add your classes that you want your jar to contain.
-- Build your project.
-- A .jar file named MyLib.jar will be created in the "jdeworkspace-->MyLib" folder.
-- Go to your JDE installation folder
    (usually: "C:\Program Files\Research In Motion\BlackBerry JDE 5.0.0\bin")
-- Copy MyLib.jar file to the bin folder.
-- Open command prompt and change your current directory to the bin directory
    (execute command > cd "C:\Program Files\Research In Motion\BlackBerry JDE 5.0.0\bin")
-- Then execute the following command:
    preverify -classpath "C:\Program Files\Research In Motion\BlackBerry JDE 5.0.0\lib
        \net_rim_api.jar"  "MyLib.jar"
-- A folder named "output" will be created in the bin directory. The preverified jar file will be found in
   the output directory. 
-- Now extract the jar file with WinZip or such other application. Your project folders and files will be
    extracted there.
-- Delete the .cod, .cls, .cso files from there.
-- Make jar again with the remaining files and folders.
-- Now you can use this jar file in another project as a library.

Use the JAR in other project:

-- Right click on the project on JDE
-- Click properties and click on the "Build" tab.
-- Add "MyLib.jar" as Imported jar files.

Customizing the Rows of a BlackBerry TreeField

Here is an example of how we can customize a TreeField of BlackBerry:

import net.rim.device.api.system.Bitmap;
import net.rim.device.api.system.Bitmap;
import net.rim.device.api.system.Display;
import net.rim.device.api.ui.Field;
import net.rim.device.api.ui.Graphics;
import net.rim.device.api.ui.component.LabelField;
import net.rim.device.api.ui.component.TreeField;
import net.rim.device.api.ui.component.TreeFieldCallback;
import net.rim.device.api.ui.container.MainScreen;
import net.rim.device.api.ui.container.VerticalFieldManager;

public class TreeDemo extends MainScreen {
 int parent[] = {1,2,3,4,5,6,7,8,9};
 int child[][] = new int [10][10];
 int child_child[][][] = new int [10][10][10];
 
 int rowHeight = 27;
 
 CustomTreeFieldCallback treeCallback = new CustomTreeFieldCallback();
 VerticalFieldManager vm = new VerticalFieldManager(
         Field.FOCUSABLE 
        |  VERTICAL_SCROLL 
        |  VERTICAL_SCROLLBAR
       );
 TreeField myTree = new TreeField(treeCallback, Field.FOCUSABLE);

 public TreeDemo() {
  vm.add(new LabelField("Table:"));
  myTree.setRowHeight(rowHeight);
  myTree.setIndentWidth(15);
  myTree.setDefaultExpanded(false);
  for(int i = parent.length-1; i >= 0 ; i--) {
   parent[i] = myTree.addChildNode(0, "Parent_" + (i+1));
   child[i] = new int[4];
   for(int j = child[i].length-1; j >=0 ; j--) {
    child[i][j] = 
      myTree.addChildNode(
       parent[i], 
       "Child_"+ (i+1) + "_" + (j+1)
      );
    child_child[i][j] = new int[3];
    for(int k = child_child[i][j].length-1; k >= 0 ; k--) {
     child_child[i][j][k] 
                    = myTree.addChildNode(
                      child[i][j], 
                      "Child_of_Child_"+ 
                        (i+1) + "_" + 
                         (j+1)+ "_" + (k+1)
                      );
    }
   }
  }
  vm.add(myTree);
  add(vm);
 }
 
 private class CustomTreeFieldCallback implements TreeFieldCallback {

  public void drawTreeItem(TreeField treeField, Graphics graphics, int node,
    int y, int width, int indent) {
   // TODO Auto-generated method stub
   String string = (String) treeField.getCookie(node);
   int preservedColor = graphics.getColor();
   
   if(treeField.getCurrentNode() == node) {
    graphics.setColor(0x0CCCC0);
   } else {
    graphics.setColor(0x404040);
   }
   graphics.fillRect(0, y, Display.getWidth(), treeField.getRowHeight());
   
   Bitmap iconImage;
   int iconImageWidth = 0;
   indent -= 20; // decrease the extra indentation for all nodes.
   if(treeField.getFirstChild(node) != -1){ // if the node is not a leaf node
    if(treeField.getExpanded(node)) {
     iconImage = Bitmap.getBitmapResource("icon_arrow_down.png");
     iconImageWidth = iconImage.getWidth();
    } else {
     iconImage = Bitmap.getBitmapResource("icon_arrow_right.png");
     iconImageWidth = iconImage.getWidth();
    }
    graphics.drawBitmap(
       indent, 
       y, 
       indent+iconImageWidth, 
       treeField.getRowHeight(), 
       iconImage, 
       0, 
       0
      );
   }

   if( treeField.getCurrentNode() == node ) {
    graphics.setColor(0x404040);   
   } else {
    graphics.setColor(0x0CCCC0);
   }
   graphics.drawText(string, indent+iconImageWidth, y);
   
   graphics.setColor(preservedColor);
  }

 }
}

In order to associate some actions for clicking on the leaf nodes, the navigationClick or touchEvent methods of TreeField class should be customized.

Invoking Google Map From BlackBerry Applications

Google Maps can be invoked as an application from blackberry applications. For that we need to first download Google Maps and then use the following code to invoke google maps as an application:

   /**
     * Starts the Google Maps application and the specified locatin is shown on map
     * @param latitude the latitude of the location to show
     * @param longitude the longitude of the location to show
     * @param title the title of the location to show
     * @param description the description of the location to show
     */
    public void showGoogleMap(double latitude, double longitude, String title,  String description) {
        try {
            int mh = CodeModuleManager.getModuleHandle("GoogleMaps");
            if (mh == 0) {
                 throw new ApplicationManagerException("GoogleMaps isn't installed");
            }
            URLEncodedPostData uepd = new URLEncodedPostData(null, false);
            uepd.append("action","LOCN");
            uepd.append("a", "@latlon:"+latitude+","+longitude);
            uepd.append("title", title);
            uepd.append("description", description);
            String[] args = { "http://gmm/x?"+uepd.toString() };
            ApplicationDescriptor ad = CodeModuleManager.getApplicationDescriptors(mh)[0];
            ApplicationDescriptor ad2 = new ApplicationDescriptor(ad, args);
            ApplicationManager.getApplicationManager().runApplication(ad2, true);
        } catch(final Exception excp) {
            Dialog.alert("Sorry, can't start Google Map: " + excp.getMessage());
        }
    }

For more info, see this StackOverflow Answer.

BlackBerry DateTimePicker: Selecting a date withing 7 days from now

DateTimePicker can be used to select a particular date. After user selects a date, it can be checked whether the date is within a particular period of time using the after() and before() methods of Calendar.

The DateTimePicker is shown each time the DateTimePicker.doModal()  method is called. A Calendar object of the selected date can be found by calling the DateTimePicker.getDateTime() method.

private String dateText;
Calendar todaysDate = Calendar.getInstance();

public void pickDate()
{
 DateTimePicker datePicker = null;
 
 Calendar dateAfter7days = Calendar.getInstance();
 
 long millsAfter7Days = System.currentTimeMillis() + 6*24*60*60*1000 ;
 dateAfter7days.setTime(new Date(millsAfter7Days));
 
 datePicker=DateTimePicker.createInstance(todaysDate,"MMMM dd, yyyy",null);
 if(datePicker.doModal())
 {
  if(
    datePicker.getDateTime().before(todaysDate)
    || 
    datePicker.getDateTime().after(dateAfter7days)   
  ) {
   final String current = 
    todaysDate.get(Calendar.DAY_OF_MONTH) +
    "/" +
    ( todaysDate.get(Calendar.MONTH)+1 ) +
    "/"+
    todaysDate.get(Calendar.YEAR);
   
   final String after7days = 
    dateAfter7days.get(Calendar.DAY_OF_MONTH) +
    "/" +
    ( dateAfter7days.get(Calendar.MONTH)+1 ) +
    "/"+
    dateAfter7days.get(Calendar.YEAR);
   
   UiApplication.getUiApplication().invokeLater(new Runnable() {      
     public void run() {
       Dialog.alert("Date should be within "+ current +" to " + after7days ); 
     }
   });
  } else {
   Calendar chosenDate = datePicker.getDateTime();
   dateText =
    chosenDate.get(Calendar.DAY_OF_MONTH) +
    "/" +
    ( chosenDate.get(Calendar.MONTH)+1 ) +
    "/"+
    chosenDate.get(Calendar.YEAR);
  }
 }
}

Animated GIF image Field With Text

This is a sample code for an animated GIF image field with a text at the bottom:

import net.rim.device.api.ui.Color;
import net.rim.device.api.ui.UiApplication;
import net.rim.device.api.system.EncodedImage;
import net.rim.device.api.system.GIFEncodedImage;
import net.rim.device.api.ui.Graphics;
import net.rim.device.api.ui.component.BitmapField;

//A field that displays an animated GIF.

public class ImageTextField extends BitmapField
{
    private GIFEncodedImage gifimage;     //The image to draw.
    private String text;
    private int currentFrame;          //The current frame in the animation sequence.
    private AnimatorThread animatorThread;
    
    private final int vSpace = 6; 
 private final int padding = 5;
 
 private int xImage = 0;
 private int yImage = 0;
 private int xText = 0;
 private int yText = 0;
    
    public ImageTextField(EncodedImage image, String txt)
    {
     super(image.getBitmap(), 0);
     byte[] arr = image.getData();
  gifimage = (GIFEncodedImage) EncodedImage.createEncodedImage(arr, 0, arr.length);
  text = txt;
  
  animatorThread = new AnimatorThread(this);
        animatorThread.start();
    }
    
    public ImageTextField(EncodedImage image, String txt, long style)
    {
     super(image.getBitmap(), style);
     byte[] arr = image.getData();
  gifimage = (GIFEncodedImage) EncodedImage.createEncodedImage(arr, 0, arr.length);
  text = txt;
  
 animatorThread = new AnimatorThread(this);
        animatorThread.start();
    }
    
    public int getPreferredWidth() {
  return (gifimage==null ? 0 : gifimage.getWidth()) + 50;
    }


 public int getPreferredHeight() {
  int imageHeight = gifimage==null ? 0 : gifimage.getHeight();
  int textHeight = getFont().getHeight();
  return imageHeight + vSpace + textHeight + 2 * padding;
 }
 

 protected void layout(int width, int height) {
  width = Math.min(width, getPreferredWidth());
  height = Math.min(getPreferredHeight(), height);
  
  int imageWidth = (gifimage==null ? 0 : gifimage.getWidth());
  int imageHeight = (gifimage==null ? 0 : gifimage.getHeight());

  xImage = (width - imageWidth) / 2;
  yImage = padding;

  xText = (width - getFont().getAdvance(text)) / 2;
  yText = yImage + imageHeight + vSpace;

  setExtent(width, height);
 }

    protected void paint(Graphics graphics)
    {

        graphics.drawImage(xImage, yImage,
        gifimage.getFrameWidth(currentFrame), gifimage.getFrameHeight(currentFrame), gifimage, currentFrame, 0, 0);
        
        int oldColor = graphics.getColor();
        graphics.setColor(Color.WHITE);
        graphics.drawRoundRect(0, 0, getWidth(), getHeight(), 5, 5);
        graphics.drawText(text, xText, yText);
        graphics.setColor(oldColor);
    }

    //Stop the animation thread when the screen the field is on is
    //popped off of the display stack.
    protected void onUndisplay()
    {
        animatorThread.stop();
        super.onUndisplay();
    }


    //A thread to handle the animation.
    private class AnimatorThread extends Thread
    {
        private ImageTextField theImageField;
        private boolean keepGoing = true;
        private int totalFrames;     //The total number of frames in the image.
        private int loopCount;       //The number of times the animation has looped (completed).
        private int totalLoops;      //The number of times the animation should loop (set in the image).

        public AnimatorThread(ImageTextField theField)
        {
            theImageField = theField;
            totalFrames = gifimage.getFrameCount();
            totalLoops = gifimage.getIterations();

        }

        public synchronized void stop()
        {
            keepGoing = false;
        }

        public void run()
        {
            while(keepGoing)
            {
                //Invalidate the field so that it is redrawn.
                UiApplication.getUiApplication().invokeAndWait(new Runnable()
                {
                    public void run()
                    {
                        theImageField.invalidate();
                    }
                });

                try
                {
                    //Sleep for the current frame delay before
                    //the next frame is drawn.
                    sleep(gifimage.getFrameDelay(currentFrame) * 10);
                }
                catch (InterruptedException iex)
                {} //Couldn't sleep.

                //Increment the frame.
                ++currentFrame;

                if (currentFrame == totalFrames)
                {
                    //Reset back to frame 0 if we have reached the end.
                    currentFrame = 0;

                    ++loopCount;

                    //Check if the animation should continue.
                    if (loopCount == totalLoops)
                    {
                        keepGoing = false;
                    }
                }
            }
        }
    }
}

Now this animated image field can be added to a manager:

VerticalFieldManager mainLayout = getCustomManager();
mainLayout.setBackground(BackgroundFactory.createSolidBackground(Color.BLACK));
EncodedImage ec = EncodedImage.getEncodedImageResource("myimage.gif");
ImageTextField ag = new ImageTextField(ec, "My Text", FIELD_HCENTER);
mainLayout.add(ag);

Calculate Distance on Sphere ( Given latitude & longitude values)

This is the code for calculating the distance between two given points by their latitude and longitude values.

public static double getOrthodromicDistance(
   double lat1,
   double lon1,
   double lat2,
   double lon2)
 {
  double R = 6371.0; // km
  double dLat = convertDegreeToRadians((lat2 - lat1));
  double dLon = convertDegreeToRadians((lon2 - lon1));
  lat1 = convertDegreeToRadians(lat1);
  lat2 = convertDegreeToRadians(lat2);

  double a =  Math.sin(dLat / 2) * Math.sin(dLat / 2) +
     Math.sin(dLon / 2) * Math.sin(dLon / 2) * Math.cos(lat1) * Math.cos(lat2);
  double c = 2 * MathUtilities.atan2(Math.sqrt(a), Math.sqrt(1 - a));
  double distance = R * c;
  return distance;
 }

The formula was taken from here.

The MathUtilities.atan2() method is found only in BlackBerry API 4.6 or higher See The definition of atan2 here
This can also be calculated like this:

double atan2t(double y, double x) {
    double lo, hi, res, val, resval;
    double ERR = 1e-9;
    if (Math.abs(x) < ERR) {
        if (Math.abs(y) >= ERR) {
            return ((y / Math.abs(y)) * PI / 2.0);
        } else {
            return (0.0);
        }
    }

    val = Math.abs(y / x);
    lo = 0.0;

    hi = PI / 2;
    res = (lo + hi) / 2.0;
    resval = Math.tan(res);

    while (Math.abs(hi - lo) > ERR) {
        if (resval > val) {
            hi = res;
        } else {
            lo = res;
        }

        res = (hi + lo) / 2.0;
        resval = Math.tan(res);
    }

    if (x < 0.0) {
        res = PI - res;
    }

    if (Math.abs(y) >= ERR) {
        return ((y / Math.abs(y)) * res);
    } else {
        return res;
    }
}