MYSQL: HAVING, GROUP BY in SQL UPDATE query

             I have to update the rows of a table in which a column value have unique count, ie the value of that column comes only once.


For eg: as per the table shown below:

     

            I want to update the value of b=1, for every rows having the count of value of column a is unique. Here the value a=3 comes in 6th row only, so update the value of that row.

             I can't use a HAVING COUNT(T1.a) = 1 in a update query. I also filed to use a IN in WHERE clause with a subquery. Finally I can use a INNER JOIN to achieve the updation.

The working SQL query as below:

UPDATE table1
JOIN (
      SELECT T1.id
        FROM table1 T1
        WHERE T1.c = 0
        GROUP BY T1.a
        HAVING COUNT(T1.a) = 1
     )T2 ON T2.id = table1.id
SET b = 1
WHERE 1 


NB: This is for my future reference only

SalesForce: Searching with Multipicklist Available Values

         I have to crate a custom search form which has a drop-down having a multi-pick list available values as options. The search is to find some child records based on any one of the selected value of a multi-pick list . So I have crated a pick list (drop-down) with options as the available values of the multi-pick list . For that I find a solution to get available values of a multi-pick list by Apex coding.


Retrieving  available values of a multi-pick list

   The used function as below:

  public List<selectOption> getPickListValues(Sobject objectName, String fieldName, String firstValueLabel, String firstValue) {

            List<selectOption> options = new List<selectOption>();
            if (firstValueLabel != null) { //if first value
                options.add(new selectOption(firstValue, firstValueLabel)); //add the first option
            }
            Schema.sObjectType sobjectType = objectName.getSObjectType(); //get the sobject from schema
            Schema.DescribeSObjectResult sobjectDescribe = sobjectType.getDescribe(); //get the describe of sobject
            Map fieldMap = sobjectDescribe.fields.getMap(); //get a map of all  fields for sobject
            List pickListValues = fieldMap.get(fieldName).getDescribe().getPickListValues(); //get the list of picklist values for the field on the sobject
            for (Schema.PicklistEntry a : pickListValues) { //for all values in the picklist list
                if(a.getValue() != 'unknown' && a.getValue() != 'Other' )        
                    options.add(new selectOption(a.getValue(), a.getLabel())); //add the value and label to options
            }
            return options;
        }


  For eg:  getPickValues(new Account(), 'Select_List__c', 'All', 'All');

   Here my parent object is Account and the field is Select_List__c.

Using multi-pick list value in SOQL

   The query that I used as:

    [SELECT name FROM child__c WHERE Account__r.Select_List__c INCLUDES (:filter)]; 


References:

http://boards.developerforce.com/t5/Apex-Code-Development/How-to-retrieve-multi-select-picklists-using-includes-excludes/td-p/172210

http://boards.developerforce.com/t5/Apex-Code-Development/Looping-through-multi-select-picklist-values/td-p/401647 

NB: This is for my future reference only

Facebook Insights using Graph API & Google Visualization: Area Chart

          The Facebook Insights reports can be integrated to external websites by any programming languages such as PHP, Java, JavaScript, apex, .net, etc. through Graph API. I have integrated the Insights report to SalesForce Visualforce Pages by Apex with jQuery. Google Visualization: Area Chart is used to draw chart.

      I have used a combination of Graph API Insights requests and a set of Facebook Query Language(FQL) requests to create a monthly report.

1. Creating the chart
  

   The following requests are used for geting data from Facebook.

   For "People Talking About This" :

        https://graph.facebook.com/[pageId]/insights/page_storytellers/week?since=[startTime]&until=[endTime]&access_token=[accessToken]

   For "People Reached" :

        https://graph.facebook.com/[pageId]/insights/page_impressions_unique/week?since=[startTime]&until=[endTime]&access_token=[accessToken]

   *** startTime, endTime are in Unix timestamps based on midnight in PST described in UTC.

      A multidimensional JavaScript array has been created from the resulted json for the Google Visualization: Area Chart. I have added custom toll tip and formatted date labels in the chart. The JavaScript code as below:

 // Prepare chart data  for series 1


     chartData = new Array();
     jQuery.each(jsonData.values, function(i, item) {
            dataArray = new Array();
            dataArray[0] = formatDate(item.end_time);
            dataArray[1] = item.value;
            dataArray[2] = getToolTipText(item.end_time, item.value, 'People Talking About This');
            chartData[i]= dataArray;
        });


 // function to draw chart

   function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Date'); // Implicit domain label col.
        data.addColumn('number', 'People Talking About This'); // Implicit series 1 data col.
        data.addColumn({type:'string', role:'tooltip'}); // Implicit tool tip 1 data col.
        data.addColumn('number', 'Weekly Total Reach'); // Implicit series 2 data col.
        data.addColumn({type:'string', role:'tooltip'});// Implicit tool tip 2 data col.
        data.addRows(chartData);

        var options = {
            width: 900,
            height: 350,
            colors: ['#FF9900', '#3B5998'],
            vAxis: { gridlines: { color : '#fff'}, logScale: true},
            hAxis: {  showTextEvery : 7 },
            pointSize: 4,
            legend: {position: 'top', textStyle: {fontSize: 14}},
            tooltip: {textStyle: {fontSize: 12}}
        };

        var chart = new google.visualization.AreaChart(document.getElementById('chart_div'));
        chart.draw(data, options);
}



function getToolTipText(endDate, value, text){
    toolTipText = 'custom tooltip';
    return toolTipText;
}

** function formatDate in Some JavaScript Date Formatting Tips 

2. Page Posts table list

    Unfortunately I can't get the stream data and insight data together in a single FQL query. So I have used a FQL multi query to get the stream data and insight data together and used a iterating loop to make  stream data with the corresponding insight data. The used FQL as follows

    {"pageStream": "SELECT post_id, substr(message, 0, 50), type, created_time,attachment.name
                    FROM stream
                    WHERE source_id = [pageId]
                        AND created_time <= [endTime]
                        AND created_time >= [stratTime]
                        AND type != ''
                    LIMIT 0, 30;",
    "pageInsight": "SELECT object_id, metric, value, end_time
                    FROM insights
                    WHERE object_id IN (SELECT post_id FROM #pageStream)
                        AND metric IN ('post_engaged_users','post_storytellers', 'post_impressions_unique')
                        AND period = period('lifetime');"}

*** startTime, endTime are in Unix timestamps based on midnight in PST described in UTC.

3. Page fan count comparison

   Another thing that I have done  Page like count comparison with previous month. Here also I am used a multi query to get both the counts together

     {"currentCount": "SELECT object_id, metric, value, end_time
                       FROM insights
                       WHERE object_id  = [pageId]
                            AND metric IN ('page_fans')
                            AND period = period('lifetime')
                            AND end_time = end_time_date('2012-8-31')",
    "previousCount": "SELECT object_id, metric, value, end_time
                      FROM insights
                      WHERE object_id  = [pageId]
                        AND metric IN ('page_fans')
                        AND period = period('lifetime')
                        AND end_time = end_time_date('2012-7-30')"}

  
  Reference: 
    https://developers.facebook.com/docs/reference/fql/insights/
    https://developers.facebook.com/docs/reference/api/insights/
    https://developers.google.com/chart/interactive/docs/gallery/areachart

Some JavaScript Date Formatting Tips

1. Date formatting from date string

    strDate =  "2012-08-02T07:00:00+0000"; // For Eg

    function formatDate(strDate){
        var monthNames = new Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec");
        var date = new Date(strDate);
        return monthNames[date.getMonth()]+ ' '+ date.getDate();
    }


 2. Date formatting from time stamp

function timeStampToDate(timeStamp){
    var date = new Date(timeStamp * 1000);
    return (date.getMonth() +1 )+ '/'+ date.getDate()+'/'+ date.getFullYear();
}

3. Week start or week end for a day

  function getWeekDay(date, day){
    var targetDay;
    var currentDay = new Date(endDate);
    if(day == 'next'){
        // add 6 days
        targetDay = new Date(currentDay.getTime() + (86400000 * 6 ));
        // 24*60*60*1000 = 86400000 (day in milliseconds)
    }else{ // previous
        // subtract 6 days
        targetDay = new Date(currentDay.getTime() - (86400000 * 6 ));
        // 24*60*60*1000 = 86400000 (day in milliseconds)
    }
    return targetDay
   }

Reference:

http://www.javascriptkit.com/jsref/date.shtml

NB: This for my future reference

Salesforce: Creating user for testing / test methods

                Sometimes we have to crate user objects in tests & test methods. To crate a user we have to select a profile and set the required fields in the user objects as the apex code below.

    Profile pfl = [select id from profile where name='Standard User'];

    User testUser = new User(alias = 'u1', email='u1@testorg.com',
            emailencodingkey='UTF-8', lastname='Testing', languagelocalekey='en_US',
            localesidkey='en_US', profileid = pfl.Id,  country='United States', CommunityNickname = 'u1',
            timezonesidkey='America/Los_Angeles', username='u1@testorg.com');

URL validation using regular expression for javascript, Salesforce Apex, Java , PHP & Ruby

Pattern for javascript, Java , PHP & Ruby

/^((http|https):\/\/)?(www[.])?([a-zA-Z0-9]|-)+([.][a-zA-Z0-9(-|\/|=|?)?]+)+$/


Validation function in Javascript

function checkForValidURL(value) {

    var urlregex = new RegExp("^((http|https):\/\/)?(www[.])?([a-zA-Z0-9]|-)+([.][a-zA-Z0-9(-|\/|=|?)?]+)+$");
    if (urlregex.test(value)) {
        return (true);
    }
    return (false);
}

Pattern for Salesforce Apex

  ^((http|https)://)??(www[.])??([a-zA-Z0-9]|-)+?([.][a-zA-Z0-9(-|/|=|?)??]+?)+?$
It can also be used for validation fields and Visualforce pages

Validation function in Apex

public Boolean checkForValidURL(String url){ 
 if(url != Null)   {
    String regexFormat ='(^((http|https)://)??(www[.])??([a-zA-Z0-9]|-)+?([.][a-zA-Z0-9(-|/|=|?)??]+?)+?$)'; 
   Pattern urlFormat = Pattern.Compile(regexFormat); 
   Matcher format_of_url = urlFormat.Match(url);
   if (format_of_url.Matches()){
       return true;       //return true - if success 
   }
 return false ;
}

Google Image Chart API - Compare data by Line chart

This API is very useful to generate graphs/charts without any API libraries or JavaScript. It provides the chart as images just by creating a URL. Using any language such as PHP, Java, .Net, Ruby, JavaScript, Apex, Visual force, Sales force, etc.. we can generate dynamic charts. 

For example:

The corresponding URL :

[http://chart.apis.google.com/chart?chds=0,50&chd=t:33,44,24,8,38,27,23,29,32,35,16,36,33,30,27,17,12,8,36,17,20,24,18,22,13,22,31,26,36,27,18%7C17,35,47,36,31,38,16,10,34,33,37,25,16,11,8,20,32,23,32,19,18,22,29,35,22,39,26,15,18,18,_&chxs=0,000000,12,0,lt,000000%7C1,000000,12,0,lt,000000&chxt=x,y&chco=9BCE32,0098FF&chls=2%7C2.5&chg=23.33,20.00&chxr=0,01,31,7%7C1,0,50,10&chxl=0:%7C1-Apr+%28Mar%29%7C8-Apr+%28Mar%29%7C15-Apr+%28Mar%29%7C22-Apr+%28Mar%29%7C29-Apr+%28Mar%29&chm=d,9BCE32,0,-1,9.0%7Cd,0098FF,1,-1,9.0&chma=9,9,9,9&chs=500x270&cht=lc&]

         I have generated this chart for comparing two months Google analytic data. I have integrated this chart to Sales force CRM using Apex language and Visual force UI along with the Google Analyics API.


Set the chart properties

   Chart size : chs=<width>x<height> Eg: chs=500x270
   Chart type : cht=<type>    Eg:cht=lc    (for line chart)

Set the chart data

   "chd" is the parameter used to send the chart data with the following format. The data series are separated by pipe character: "|" . The null values or empty values are represented by "_".

   chd=t:val,val,val|val,val,val...

Eg:- chd=t:33,44,24,8,38,27,23,29,32,35,16,36,33,30,27,17,12,8,36,17,20,24,18,22,13,22,31,26,36,27,18|

 17,35,47,36,31,38,16,10,34,33,37,25,16,11,8,20,32,23,32,19,18,22,29,35,22,39,26,15,18,18,_

Set the chart axis properties

   Visible Axes : chxt= <axis_1>,...,<axis_n> Eg: chxt=x,y
   Axis Range   : chxr= <axis_index>,<start_val>,<end_val>,<opt_step> |...| <axis_index>,<start_val>,<end_val>,<opt_step>
                         Eg: chxr=0,01,31,7|1,0,50,10
   Axis Labels  : chxl=<axis_index>:|<label_1>|...|<label_n>    |...|  <axis_index>:|<label_1>|...|<label_n>
                     Eg: chxl=0:|1-Apr+%28Mar%29|8-Apr+%28Mar%29|15-Apr+%28Mar%29|22-Apr+%28Mar%29|29-Apr+%28Mar%29


   Axis Label Styles : chxs= <axis_index><opt_format_string>,<opt_label_color>,<opt_font_size>,<opt_alignment>,<opt_axis_or_tick>,<opt_tick_color>,<opt_axis_color>
                             |...|
                             <axis_index><opt_format_string>,<opt_label_color>,<opt_font_size>,<opt_alignment>,<opt_axis_or_tick>,<opt_tick_color>,<opt_axis_color>
                           Eg: 0,000000,12,0,lt,000000|1,000000,12,0,lt,000000

Dynamic axis label position

     For x-axis, the default labels positions are 0 to 100. In my chart, need to show the days of a month. I have to show only 7 days apart.  so I set

       chxr=0,01,31,7 (0:x-axis, 01:fist_day, 31:last_day, 7: steps).

    For y-axis,  the default labels positions are 0 to 100. But we have to plot dynamic values. So I have find the y-min & y-max from both set of data and used a fixed step 10
    ie chxr=1,0,50,10 (1:y-axis, 0:y-min, 31:y-max, 10: steps)

  Combining both chxr=0,01,31,7|1,0,50,10

 
Dynamic axis scaling

    The y-axis data may vary dynamically. We have y-axis based on the plotted value, so use y-min & y-max from both set of data.
    ie, chds=0,50 (y-min, y-max)

Dynamic grid line position
 
   we have to position the grid lines on the axis labels that we are labelled dynamically. For both x & y axis, there are default 100 grid line step sizes. Then we can to set the grid positions using the parameter "chg".

   chg= <x_axis_step_size>,<y_axis_step_size>,<opt_dash_length>,<opt_space_length>,<opt_x_offset>,<opt_y_offset>

  so we have to calculate the x_axis_step_size & y_axis_step_size dynamically.

  We can use the (100 * axis_steps) / (axis_ max_value - axis_ min_value) to find out the gidline step size.

  Hence for x-axis & y -axis ,
     x_axis_step_size =(100 * x_steps) / (x_max - x_min) = (100 * 7) / (31 - 1) = 23.33
     y_axis_step_size = (100 * y_steps) / (y_max - y_min) = (100 * 10) / (50 - 0) = 20

  ie, chg=23.33, 20


Apex code to set up chart axis

Integer yMax, yMin, yDiv = 10;
decimal ySteps, xSteps;        
if(math.mod(maxValue,yDiv) != 0){
    yMax = ((maxValue/yDiv)+1)*yDiv;
}else{
    yMax = maxValue;
}

if(math.mod(minValue,yDiv)!= 0){
    yMin = ((minValue/yDiv))*yDiv;
}else{
    yMin = minValue;
}       
string str = '0,01,'+monthEnd+',7%7C1,'+yMin+','+yMax+','+yDiv; //0,01,31,7|1,0,20,5
chartObj.changeParam('cht' ,'lc');
chartObj.changeParam('chxr',str);
xSteps = decimal.valueOf(100*7)/decimal.valueOf(monthEnd-1); // 100*7/(31-1) = 23.33
if(yMax <= 0){
    yMax = 10;
}
ySteps = (decimal.valueOf(1000)/(decimal.valueOf(yMax) - decimal.valueOf(yMin)));
str = xSteps.setScale(2)+','+ySteps.setScale(2);
chartObj.changeParam('chds',yMin+','+yMax);
chartObj.changeParam('chg',str);
chartObj.changeParam('chxl','0:%7C1-'+currMonthName+'+%28'+prevMonthName+'%29'+'%7C8-'+currMonthName+'+%28'+prevMonthName+'%29'+'%7C15-'+currMonthName+'+%28'+prevMonthName+'%29'+'%7C22-'+currMonthName+'+%28'+prevMonthName+'%29'+'%7C29-'+currMonthName+'+%28'+prevMonthName+'%29');// 0:|01|08|15|22|29|31st

References:
https://developers.google.com/chart/image/docs/chart_params#gcharts_grid_lines/docs/chart_params#gcharts_grid_lines

NB: These posts are for my future references 

Decimal / Floating point number validation using javascript / jQuery (regular expression)

To validate any decimal number

    function validateDecimal(value)    {
        var RE = /^\d*\.?\d*$/;
        if(RE.test(value)){
           return true;
        }else{
           return false;
        }
    }

Eg: 54     -> true
     1.235  -> true
     12.258 -> true
     1.2.5  -> false
     sdf123 -> false

To validate decimal number with two digit precision

function validateDecimal(value)    {
    var RE = /^\d*(\.\d{1})?\d{0,1}$/;
    if(RE.test(value)){
       return true;
    }else{
       return false;
    }
}

Eg: 54     -> true
    5.2       -> true
    1.23   -> true
    155.89 -> true
    12.258 -> false
    1.2.5  -> false
    sdf123 -> false


General form of regular expression for decimal a number

    ^\d{0,M}(\.\d{1})?\d{0,N}$

M = at most number of digits before decimal point.
N = (n-1); n = at most number of digits after decimal point.

MYSQL: JOIN in UPDATE query

       As in the SELECT query we can also use JOIN in UPDATE query. But there is difference in using join in SELECT query and in UPDATE query.

For eg:- (in SELECT query)

    SELECT * FROM  table1 AS T1
    JOIN table2 AS T2 ON T2.t1 = T1.id
    WHERE T2.a = 3;

 As per this we will write the JOIN statement just before the WHERE statement in UPDATE query, but it is wrong. The JOIN statement statement must be near to the first table. That means the SET statement must be after the JOIN statement.

For eg:-

    UPDATE table1 AS T1
    JOIN table2 AS T2 ON T2.t1 = T1.id
    SET T1.a = 2, T2.a = 2
    WHERE T1.b = 3 AND T2.a = 4;

Note: This post is for my reference only.

Table row background repeats in columns for Google Chrome & IE7

          While working with tables, I struck with a css issue in table row background in Google Chrome & in Internet Explorer 7, no issue in other browsers (Firefox, Internet Explorer 8 & 9). If we specify a background image for a row (tr), it will repeats in every columns(td or th) in that row. The issue as the image:

     By Googling and using some tricks, I can fix this issue (thanks God) in both browsers. The initial HTML and CSS as:
<table cellspacing="0" cellpadding="0" class="bidTable">
           <tbody>
        <tr class="bidHeader">
               <th id="category">Category</th>
               <th id="minBid">Min Bid</th>
               <th id="highBid">High Bid</th>
           </tr>
           <tr>
               <td class="cat">Test Category </td>
               <td class="min-bid">$0 </td>
               <td class="high-bid">$13 </td>
           </tr>
       </tbody>
</table>


.bidTable tr {
    background: url("../images/bid_bottom.png") no-repeat scroll 0 0 transparent;
    height: 53px;
    width: 464px;
}

.bidTable tr.bidHeader {
    background: url("../images/bid_header.png") no-repeat scroll 0 0 transparent;
    height: 45px;
    width: 464px;
}

.bidTable th {
    color: #FFFFFF;
    font-size: 15px;
    font-weight: normal;
}

.bidHeader #category {
    padding-left: 20px;
    text-align: center;
    width: 260px;
}

.bidHeader #minBid {
    width: 92px;
}
.bidHeader #highBid {
    width: 92px;
}


For other browsers it seen as:

   For Google Chrome, I have to use use the CSS hack. The issue is fixed by adding  display: inline-table; for both td & th. But I have to do extra css to keep the alignment. The CSS is like this:


/* for google crome */
@media screen and (-webkit-min-device-pixel-ratio:0) {
  .bidTable td, .bidTable th {
      display: inline-table;
      height: 9px;
      padding: 12px 0 0 0;
  }
  .bidHeader #category, .bidTable .cat{
      width: 267px;
      padding-left: 20px;
      text-align: center;
  }
  .bidHeader #minBid, .bidTable .min-bid{
      width: 73px;
  }

  .bidHeader #highBid, .bidTable .high-bid{
      width: 78px;
  }
}


For IE7, also used the CSS hack, for tr added position: relative; and for th & td added background: none; like this.

  /* for IE 7 */
  *+html .bidTable tr {
      position: relative;
      padding: 0;     
  }
  *+html .bidTable th {
      background: none;     
  }
  *+html .bidTable td {
      background: none;     
  }