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'
        }
    ]
});

REFACTORING

 What is Refactoring? A software is built initially to serve a purpose, or address a need. But there is always a need for enhancement, fixin...