Monday, October 7, 2013

GROUP BY MONTH, YEAR AND WEEK IN SQL

Say, You have a table from which you need to get the count of records, sum of amount and average amount on a weekly, monthly and yearly basis.

These are the queries:

Select
COUNT(id) as COUNT, SUM(AMOUNT) as SUM, AVG(AMOUNT) as AVG, DATEPART(WEEK, CREATED_DATE) as WEEKCOUNT
from TABLE
GROUP BY DATEPART(WEEK, CREATED_DATE);

Select
COUNT(id) as COUNT, SUM(AMOUNT) as SUM, AVG(AMOUNT) as AVG, MONTH(CREATED_DATE) as MONTHCOUNT
from TABLE
GROUP BY MONTH(CREATED_DATE);

Select
COUNT(id) as COUNT, SUM(AMOUNT) as SUM, AVG(AMOUNT) as AVG, YEAR(CREATED_DATE) as YEARCOUNT
from TABLE
GROUP BY YEAR(CREATED_DATE);

Friday, January 25, 2013

Conditional coloring of the list rows in Sencha Touch 2

I had a tough time doing this. Finally its up. You need to manipulate on the dom.
Just need to add the following listener on your list item.

listeners:{
                activate:function(list,index,item,record){
                var itemClass =Ext.get('id-of-your-list').dom.getElementsByClassName("x-list-item");
                    for (var i = 0; i < itemClass.length; i++) {                       
                        if(this.getStore().getData().items[i].raw.status ===1){ //condition on the record
                            itemClass[i].style.backgroundColor='#f3f3c7'; //change the color of the row
                        }else if(this.getStore().getData().items[i].raw.status ===2){
                            itemClass[i].style.backgroundColor='#c7f3c7'; //change the color of the row
                        }
                    }
                }
            }

Thursday, January 10, 2013

Sencha Touch- Add a Nested List to a Tab Panel

Most of the code has been inspired from the sencha docs. Here is an example of a nested list inside a tab panel in Sencha Touch.

Ext.setup({
        onReady: function() {

var data = {
     text: 'Groceries',
     items: [{
         text: 'Drinks',
         items: [{
             text: 'Water',
             items: [{
                 text: 'Sparkling',
                 leaf: true
             }, {
                 text: 'Still',
                 leaf: true
             }]
         }, {
             text: 'Coffee',
             leaf: true
         }, {
             text: 'Espresso',
             leaf: true
         }, {
             text: 'Redbull',
             leaf: true
         }, {
             text: 'Coke',
             leaf: true
         }, {
             text: 'Diet Coke',
             leaf: true
         }]
     }, {
         text: 'Fruit',
         items: [{
             text: 'Bananas',
             leaf: true
         }, {
             text: 'Lemon',
             leaf: true
         }]
     }, {
         text: 'Snacks',
         items: [{
             text: 'Nuts',
             leaf: true
         }, {
             text: 'Pretzels',
             leaf: true
         }, {
             text: 'Wasabi Peas',
             leaf: true
         }]
     }]
 };

 Ext.define('ListItem', {
     extend: 'Ext.data.Model',
     config: {
         fields: [{
             name: 'text',
             type: 'string'
         }]
     }
 });

 var store = Ext.create('Ext.data.TreeStore', {
     model: 'ListItem',
     defaultRootProperty: 'items',
     root: data
 });

 var nestedList = Ext.create('Ext.NestedList', {
    // fullscreen: true,
     title: 'Groceries',
     xtype: 'nestedList',
     displayField: 'text',
     iconCls: 'user',
     store: store
 });

var tabPanel = new Ext.TabPanel({   
    fullscreen: true,
    ui: 'light',
    layout: 'card',
    animation: {
        type: 'cardslide',
        cover: true
    },
    tabBar: {
         docked: 'bottom',
        layout: {
            pack: 'center'
        }
    },
    animation: {
        type: 'cardslide',
        cover: true
    },
    items: [
       nestedList,
       {
            title: 'Home',
            iconCls: 'home',
            html: 'Home Screen'
        }
    ]
});

Tuesday, October 9, 2012

Hide the browser toolbar in jsp

I am so tired with testers raising bugs they get when they click on the browser back button.
So, its better to hide the browser buttons altogether.

Here is the code.

My web.xml has my welcome file to index.jsp, which in turn just redirects the control to my Login.jsp.

web.xml


        /pages/Login.jsp

index.jsp

<%
response.sendRedirect("login.do");
%>

Where login.do is my action mapping.
Now, I change my index.jsp to



This opens my application in a new browser, where the browser toolbar and menubar do not appear...and I get rid of my Back and Forward button bugs. Hmpfffff!!!

Thursday, October 4, 2012

Read and wite a video in java

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

public class VideoTest {
    public static void main(String args[]) throws IOException {
        File file1 = new File("C:/Users/splashtechie/Pictures/test.mp4");
        FileOutputStream foStream = new FileOutputStream(file1);
        ByteArrayOutputStream oStream = new ByteArrayOutputStream();
        InputStream is2 = new FileInputStream("C:/Users/splashtechie/Videos/test.mp4");
        //InputStream is2 = rs.getBinaryStream(1);
        byte[] bytes = new byte[is2.available()];
        is2.read(bytes);
        oStream.write(bytes);
        System.out.println("writing....");
        oStream.writeTo(foStream);
       
        System.out.println("done!!!");
    }

}

Tuesday, September 25, 2012

Encryption and Decryption in Java

I had already written a post on the same earlier. But it works only when pass the encrypted value to the decryptor method before the SecretKey is generated again. This is because, each time we call to generate the SecretKey, it generates a new one each time.

I have found a solution to this. This comes handy when you are encrypting the data and storing it somewhere and later want to retrieve the data and decrypt it while doing so. This can be done using the 'AES' algorithm instead of the 'DES' used in the previous example.

The AES algorithm, generates the same SecretKey each time you generate it. Here is the implementation :

 private static final String ALGO = "AES";
        private static final byte[] keyValue =
            new byte[] { 'm', 'a', 'n', 'a', 's', 'w', 'i','t', 'a', '_', 'm','i', 's', 'h', 'r', 'a' };

/**
     * Encrypt the password before saving   
     * @param Data
     * @return
     * @throws Exception
     */
    public static String encrypt(String Data) throws Exception {
        Key key = generateKey();
        Cipher c = Cipher.getInstance(ALGO);
        c.init(Cipher.ENCRYPT_MODE, key);
        byte[] encVal = c.doFinal(Data.getBytes());
        String encryptedValue = new BASE64Encoder().encode(encVal);
        return encryptedValue;
    }

    /**
     * Decrypt the password before recieving
     * @param encryptedData
     * @return
     * @throws Exception
     */
    public static String decrypt(String encryptedData) throws Exception {
        Key key = generateKey();
        Cipher c = Cipher.getInstance(ALGO);
        c.init(Cipher.DECRYPT_MODE, key);
        byte[] decordedValue = new BASE64Decoder().decodeBuffer(encryptedData);
        byte[] decValue = c.doFinal(decordedValue);
        String decryptedValue = new String(decValue);
        return decryptedValue;
    }
    /**
     * Key Generator for encryption and decryption
     * @return
     * @throws Exception
     */
    private static Key generateKey() throws Exception {
        Key key = new SecretKeySpec(keyValue, ALGO);
        return key;
}

Here is a simple client to test the implementation above

public static void main(String[] args) throws Exception {
        String password = "password";
        String passwordEnc = AESencrp.encrypt(password);
        String passwordDec = AESencrp.decrypt(passwordEnc);

        // This key generates the same value each time you run it
        System.out.println(generateKey());

        System.out.println("Original password : " + password);
        System.out.println("Encrypted password: " + passwordEnc);
        System.out.println("Decrypted password: " + passwordDec);
    }

Thursday, September 13, 2012

Andriod App that uses Google App Engine as Backend

Now that was 4 days of tiresome job. But now that its finally done, I would like to post it to my blog. I would be happy if I can rescue anyone else who has been slogging like me.

So, I want to create an Android app that consumes data from an application hosted on the Google app Engine.
The first rule is to get the steps right. That was the basic mistake I did, by trying n no. of solutions without making a clear picture in my mind how to achieve it. Here are the basic steps to be taken in the same order they have been jotted down:

1. Create a GAE application that exposes your data in JSON or XML format. The example I would be mentioning below uses JSON. Publish it to GAE.

2. Create an Android Application. Preferably make an Hello Wold application first, so that you get a hang of things.

3. Now, change your Android app to access any public URL to see if you can read from it.

4. Finally, pass the URL of your GAE application.

Step 1:

public void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws IOException, ServletException {

        
resp.setContentType("text/plain");

        List list = new ArrayList();
        DatastoreService datastore = DatastoreServiceFactory.getDatastoreService();

        Query fetchQuery = new Query("Offers");

        List pages = new ArrayList();

        pages =  datastore.prepare(fetchQuery).asList(FetchOptions.Builder.withDefaults()); 

............................

I assume, we are familiar with the above code snippet. This is going to fetch from your table on GAE.

for (Entity page: pages) {
            // Add the pojo as a JSONObject
            list.add(new JSONObject(page));
        }
        // Create a JSONArray based from the list of JSON Obejcts
        JSONArray jsonArray = new JSONArray(list);

        // Then output the JSON string to the servlet response
         resp.getWriter().println(jsonArray.toString());

The above code converts your list data into JSONObject and attaches it to the response object. And with this we complete the first step.


Step 4 : 

Now, in your android onCreate method, add the following code to access the JSON Object sent as a response from the GAE Application.

ArrayList listItems = new ArrayList();
            
            try {
                URL url= new URL(
                        "the GAE app URL here");
                URLConnection tc = url.openConnection();
                BufferedReader in = new BufferedReader(new InputStreamReader(
                        tc.getInputStream()));
   
                String line;
                while ((line = in.readLine()) != null) {
                    JSONArray ja = new JSONArray(line);
   
                    for (int i = 0; i < ja.length(); i++) {
                        JSONObject jo = (JSONObject) ja.get(i);
                       
                        String obj=jo.getString("properties");
                        listItems.add(
obj);
                    }
                }
            } catch (MalformedURLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (JSONException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return listItems;
        }

Assign the list to the TextView in your main.xml

TextView text = (TextView) findViewById(R.id.textviewId);
              for(String s: listItems){
                  sb.append(s+"\n");
              }
              text.setText(sb.toString());

And now you have a GAE application that posts data into a store and an Android Application that's listening.



 
Home | About | Link | Link
Simple Proff Blogger Template Created By Herro | Inspiring By Busy Bee Woo Themes