Thursday 13 November 2014

Get Data Vertical to Horizontal form in SqlServer

Here I am explain how to get data  vertical into horizontal Form with a T-SQL Query (max. 3 Columns).
Table
Old View:
StoreNo.
PhoneNo.
1111
0120-2411000
1111
0120-2411001
1111
0120-2411002


New View:
StoreNo
Phone1
Phone2
Phone3
1111
0120-2411000
0120-2411001
0120-2411002

IF OBJECT_ID('Gangwar..#Temp') IS NOT NULL
DROP TABLE  #Temp;
GO
;

CREATE TABLE #Temp
(
    StoreNo  INT
   ,PhoneN0 VARCHAR(20)

)
;

INSERT INTO #Temp (StoreNo,PhoneN0)
           SELECT 1111 , '0120-2411000'
UNION ALL  SELECT 1111 , '0120-2411001'
UNION ALL  SELECT 1111 , '0120-2411002'
;

; WITH CTE AS
( 
      SELECT T.StoreNo , T.PhoneN0
            , RN = ROW_NUMBER() OVER(PARTITION BY T.StoreNo ORDER BY T.PhoneNo)
      FROM  #Temp T
)
SELECT Store
       , [1] AS Phone1
         , [2] AS Phone2
         , [3] AS Phone3
FROM CTE

PIVOT ( MAX(PhoneNr) FOR RN IN ([1],[2],[3])) Pivot_Handle

OutPut :

How to get Day, Month and Year Part from DateTime in Sql Server

In this article I am explain how to get Day, Month and Year Part from DateTime in Sql Server. 

1. DAY part of DateTime in Sql Server

Following are the different ways of getting DAY part of the DateTime in Sql Server

Demo 1: Using DAY Function

We can use DAY() function to get the DAY part of the DateTime in Sql Server.

SELECT GETDATE() 'Today', DAY(GETDATE()) 'Day Part'
RESULT:

Demo 2: Using DATEPART Function


We can use DATEPART() function to get MONTH part of the DateTime in Sql Server, here we need specify datepart parameter of the DATEPART function as month or mm or all will return the same result.
SELECT GETDATE() 'Today', DATEPART(day,GETDATE()) 'Day Part'
SELECT GetDate() 'Today', DATEPART(dd,GETDATE())  'Day Part'
SELECT GetDate() 'Today', DATEPART(d,GETDATE())   'Day Part'

Demo 3: Day returned should always be of TWO digits.

If we see the previous two approaches as Today is 3rd day of February, it is always returning day as 3 i.e one digit instead of 03. Below examples shows how to get two digits day part of a DateTime.

SELECT GETDATE() 'Today',
  CONVERT(varchar(2), getdate(), 103) 'Day Part'
SELECT GETDATE() 'Today',
  RIGHT('0' + CAST(DAY(GETDATE()) AS varchar(2)), 2) 'Day Part'

2. MONTH part of DateTime in Sql Server

Following are the different ways of getting MONTH part of the DateTime in Sql Server

Demo 1: Using MONTH Function

We can use MONTH() function to get the MONTH part of the DateTime in Sql Server.
SELECT GETDATE() 'Today', MONTH(GETDATE()) 'MONTH Part'


Demo 2: Using DATEPART Function

We can use DATEPART() function to get MONTH part of the DateTime in Sql Server, here we need specify datepart parameter of the DATEPART function as month or mm or all will return the same result.
SELECT GETDATE() 'Today',DATEPART(month,GETDATE()) 'Month Part'
SELECT GetDate() 'Today', DATEPART(mm,GETDATE())  'Month Part'
SELECT GetDate() 'Today', DATEPART(m,GETDATE())   'Month Part'


Demo 3: Month returned should always be of TWO digits.

If we see the previous two approaches as Today’s month is February, it is always returning month as 2 i.e one digit instead of 02. Below examples shows how to get two digits month part of a DateTime.
SELECT GETDATE() 'Today',
 CONVERT(varchar(2), getdate(), 101) 'Month Part'
SELECT GETDATE() 'Today',
 RIGHT('0'+CAST(MONTH(GETDATE()) AS varchar(2)),2) 'Month Part'


3. YEAR part of DateTime in Sql Server

Following are the different ways of getting YEAR part of the DateTime in Sql Server

Demo 1: Using YEAR Function

We can use YEAR() function to get the YEAR part of the DateTime in Sql Server.
SELECT GETDATE() 'Today', YEAR(GETDATE()) 'YEAR Part'


Demo 2: Using DATEPART Function

We can use DATEPART() function to get YEAR part of the DateTime in Sql Server, here we need specify datepart parameter of the DATEPART function as year or yyyy or yy all will return the same result.
SELECT GETDATE() 'Today', DATEPART(year,GETDATE()) 'Year Part'
SELECT GetDate() 'Today', DATEPART(yyyy,GETDATE()) 'Year Part'
SELECT GetDate() 'Today', DATEPART(yy,GETDATE())   'Year Part'


How to get Monthly Data in Sql Server

In this article I am explain how to get Monthly data in Sql Server in different formats as shown  below . Here Sales table data is presented in two different Monthly aggregated sales data formats.
we create a Sales table and insert 1000 Sample Sales Records With Random sales date in past 0-798 days as the sales date by using the below script.

--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With
--Random past 0-798 days as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(dd, - CONVERT(INT, (798+1)*RAND()),GETDATE()))

GO 1000

Demo 1: Getting Monthly Data

SELECT YEAR(SalesDate) [Year], MONTH(SalesDate) [Month],
 DATENAME(MONTH,SalesDate) [Month Name], COUNT(1) [Sales Count]
FROM #Sales
GROUP BY YEAR(SalesDate), MONTH(SalesDate),
 DATENAME(MONTH, SalesDate)
ORDER BY 1,2

Demo 2: Getting Monthly Data using PIVOT

SELECT *
FROM (SELECT YEAR(SalesDate) [Year],
       DATENAME(MONTH, SalesDate) [Month],
       COUNT(1) [Sales Count]
      FROM #Sales
      GROUP BY YEAR(SalesDate),
      DATENAME(MONTH, SalesDate)) AS MontlySalesData
PIVOT( SUM([Sales Count])  
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS MNamePivot

Tuesday 11 November 2014

How to add Days, Weeks, Months, Quarters or Years to a Date in Sql Server

How to add Days to DateTime in Sql Server?
We can use DATEADD() function like below to add days to DateTime in Sql Server. DATEADD() functions first parameter value can be day or dd or d all will return the same result. Below example shows how we can add two days to Current DateTime in Sql Server:
SELECT GETDATE() 'Today',
           DATEADD(day,2,GETDATE()) 'Today + 2 Days'
SELECT GETDATE() 'Today',
           DATEADD(dd,2,GETDATE()) 'Today + 2 Days'
SELECT GETDATE() 'Today',
           DATEADD(d,2,GETDATE()) 'Today + 2 Days'
SELECT GETDATE() 'Today', GETDATE() + 2 'Today + 2 Days'


How to add Weeks to DateTime in Sql Server?

We can use DATEADD() function to add weeks to DateTime in Sql Server. DATEADD() functions first parameter value can be week or wk or ww, all will return the same result. Below example shows how we can add two weeks to Current DateTime in Sql Server:
SELECT GETDATE() 'Today',
           DATEADD(week,2,GETDATE()) 'Today + 2 Weeks'
SELECT GETDATE() 'Today',
           DATEADD(wk,2,GETDATE()) 'Today + 2 weeks'
SELECT GETDATE() 'Today',

           DATEADD(ww,2,GETDATE()) 'Today + 2 Weeks'

How to add Months to DateTime in Sql Server?

We can use DATEADD() function like below to add Months to DateTime in Sql Server. DATEADD() functions first parameter value can be month or mm or m, all will return the same result. Below example shows how we can add two months to Current DateTime in Sql Server:
SELECT GETDATE() 'Today',
           DATEADD(month,2,GETDATE()) 'Today + 2 Months'
SELECT GETDATE() 'Today',
           DATEADD(mm,2,GETDATE()) 'Today + 2 Months'
SELECT GETDATE() 'Today',
           DATEADD(m,2,GETDATE()) 'Today + 2 Months'

How to add Quarters to DateTime in Sql Server?

We can use DATEADD() function like below to add Quarters to DateTime in Sql Server. DATEADD() functions first parameter value can be quarter or qq or q, all will return the same result. Below example shows how we can add two months to Current DateTime in Sql Server:
SELECT GETDATE() 'Today',
           DATEADD(quarter,2,GETDATE()) 'Today + 2 Quarters'
SELECT GETDATE() 'Today',
           DATEADD(qq,2,GETDATE()) 'Today + 2 Quarters'
SELECT GETDATE() 'Today',
           DATEADD(q,2,GETDATE()) 'Today + 2 Quarters'

How to add Years to DateTime in Sql Server?

We can use DATEADD() function like below to add Years to DateTime in Sql Server. DATEADD() functions first parameter value can be year or yyyy or yy, all will return the same result. Below example shows how we can add two Years to Current DateTime in Sql Server:
SELECT GETDATE() 'Today',
           DATEADD(year,2,GETDATE()) 'Today + 2 Years'
SELECT GETDATE() 'Today',
           DATEADD(yyyy,2,GETDATE()) 'Today + 2 Years'
SELECT GETDATE() 'Today',
           DATEADD(yy,2,GETDATE()) 'Today + 2 Years'

Thursday 6 November 2014

Jquery Important Codes

/// <summary>
///----------Bind drpCondition_ dropdwon on the based of drpColumn1_ selected field.
/// </summary>
$(document).on('change', '[id^="drpColumn1_"]', function () {
var ids = this.id;
var id = ids.split('_')[1];
var drpDataType = $("#drpColumn1_" + id + " option:selected").attr("datatype");
switch (drpDataType) {
case 'string':                         
var appendData = '<option value="-1">Please Select Condition</option><option value="equals">equals</option><option value="not equal to">not equal to</option><option value="starts with">starts with</option><option value="end with">end with</option><option value="includes">includes</option><option value="excludes">excludes</option><option value="contains">contains</option><option value="does not contains">does not contains</option>';
$("#drpCondition_" + id).html(appendData);
break;
case 'int':
var appendData = '<option value="-1">Please Select Condition</option><option value="equals">equals</option><option value="not equal to">not equal to</option><option value="greater than">greater than</option><option value=" less than"> less than</option><option value="greater or equal">greater or equal</option><option value="less or equal">less or equal</option><option value="starts with">starts with</option><option value="contains">contains</option><option value="does not contains">does not contains</option>';
$("#drpCondition_" + id).html(appendData);
break;
}
});
/// <summary>
/// ----------------Adding more dynamically dropdown and text box and binding dropdown dynamically.
/// </summary>
$(document).ready(function () {
$("#btnadd").click(function () {
var counter = $("#HidCounter1").val();
var $clone = $("#drpColumn1_0").clone();
if (counter != 10) {
$('<p><tr ><td><select style="width: 68px" id="DrpOperator_' + counter + '" name="DrpOperator[' + counter + ']"><option value="AND">AND</option><option value="OR">OR</option></select></td>' + '<td><select style="width:180px;" id="drpColumn1_' + counter + '" name="drpColumn1[' + counter + ']">' + $clone.html() + '</select></td>' +
'<td><select style="width:190px;" id="drpCondition_' + counter + '" name="drpCondition[' + counter + ']"><option value="-1">Please Select Condition</option></select></td>' +
'<td><input style="width:120px; height:22px;" type="text" onkeypress="javascript:return AlphnumericCommaKeyAndAlpha(event)" id="txtCondition_' + counter + '" name="txtCondition[' + counter + ']"/></td><td> <input style="margin-bottom: 10px;" type="button"  id="btndel_' + counter + '" class="btn" value="Remove"  /></td></tr></p>').appendTo("#tablelist");
counter++;
$("#HidCounter1").val(counter);
return false;
}
else {
errMsgFoeLenght();
}
});
});

$(document).on('click', '[id^="btndel_"]', function () {
$(this).parent('p').remove();
var counter = $("#HidCounter1").val() - 1;
$("#HidCounter1").val(counter);
});
/// <summary>
/// ----------------Clear the dropdwon selected value on clear button click.
/// </summary>
// regular exp:- "^[\s\S]{5,8}$"
function minmax(value, min, max) {
if (parseInt(value) < 5 || isNaN(value))
return 5;
else if (parseInt(value) > 50)
return 50;
else return value;
}
/// <summary>
/// ----------------Clear the dropdwon selected value on clear button click.
/// </summary>
$(document).ready(function () {
$("#btnClear").click('[#tablelist p]', function () {
$('[id^=drpColumn1_]').find('option:first').attr('selected', 'selected');
$('[id^=drpCondition_]').find('option:first').attr('selected', 'selected');
$('[id^=txtCondition_]').val(''); DrpOperator_2
$('[id^=DrpOperator_]').find('option:first').attr('selected', 'selected');
});
});

/// <summary>
/// ----------------Create Five(5) dropdown dynamically on page.
/// </summary>
function CreateDynamicDrpDwon() {
var counter = $("#HidCounter1").val();
var $clone = $("#drpColumn1_0").clone();
for (i = counter; i < 5; i++) {
$('<p><tr ><td><select style="width: 68px" id="DrpOperator_' + counter + '" name="DrpOperator[' + counter + ']"><option value="AND">AND</option><option value="OR">OR</option></select></td>' + '<td><select style="width:180px;" id="drpColumn1_' + counter + '" name="drpColumn1[' + counter + ']">' + $clone.html() + '</select></td>' +
'<td><select style="width:190px;" id="drpCondition_' + counter + '" name="drpCondition[' + counter + ']"><option value="-1">Please Select Condition</option></select></td>' +
'<td><input style="width:120px; height:22px;" type="text" onkeypress="javascript:return AlphnumericCommaKeyAndAlpha(event)" id="txtCondition_' + counter + '" name="txtCondition[' + counter + ']"/></td><td> <input style="margin-bottom: 10px;" type="button" id="btndel_' + counter + '" class="btn" value="Remove" /></td></tr></p>').appendTo("#tablelist");
counter++;
$("#HidCounter1").val(counter);
}
}
/// <summary>
/// ----------------Validate view name textbox should accept Alphnumeric and  three special character ("&","_","-").
/// </summary>

function isAlphnumericCommaKey(evt) {
var c = (evt.which) ? evt.which : event.keyCode
if (!((c >= 65 && c <= 90) || (c >= 97 && c <= 122) || (c >= 48 && c <= 57) || (c == 32) || (c == 45) || (c == 95)))
return false;
return true;
}
/// <summary>
/// ----------------Validate filter condition textbox should accept Alpha numeric, underscore, hyphen, period.
/// </summary>

function AlphnumericCommaKeyAndAlpha(evt) {
var c = (evt.which) ? evt.which : event.keyCode
if (!((c >= 65 && c <= 90) || (c >= 97 && c <= 122) || (c >= 48 && c <= 57) || (c == 32) || (c == 45) || (c == 95) || (c == 46) || (c == 64)))
return false;
return true;
}
</script>
<script type="text/javascript">
/// <summary>
///------------function for Bind the Filter conditions, DropDownList Values and TextBox Data and condition.
/// </summary>
function BindFilterConditions() {
var counter = 1;
var viewid = $('#hidViewId').val();
$("#HidCounter1").val(1);
if (viewid > 0) {
$.ajax({
url: "CreateView.aspx/GetFilterConditions?ViewID=" + viewid,
data: {},
type: "POST",
dataType: "json",
contentType: "application/json;charset=utf-8",
success: function (data1) {
var $clone = $("#drpColumn1_0").clone();
var data = $clone.html();
var arr_val = JSON.parse(data1.d);
var opt_ddl = [];
for (var x = 0; x < arr_val.length; x++) {
if (x == 0) {
$("#drpColumn1_0").val(arr_val[x].FieldId);
var drpDataType = $("#drpColumn1_0 option:selected").attr("datatype");
switch (drpDataType) {
case 'string':
var appendData = '<option value="-1">Please Select Condition</option><option value="equals">equals</option><option value="not equal to">not equal to</option><option value="starts with">starts with</option><option value="end with">end with</option><option value="includes">includes</option><option value="excludes">excludes</option><option value="contains">contains</option><option value="does not contains">does not contains</option>';
$("#drpCondition_0").html(appendData);

break;
case 'int':
var appendData = '<option value="-1">Please Select Condition</option><option value="equals">equals</option><option value="not equal to">not equal to</option><option value="greater than">greater than</option><option value=" less than"> less than</option><option value="greater or equal">greater or equal</option><option value="less or equal">less or equal</option><option value="starts with">starts with</option><option value="contains">contains</option><option value="does not contains">does not contains</option>';
$("#drpCondition_0").html(appendData);
break;
}
$("#drpCondition_0").val(arr_val[x].Condition);
$("#txtCondition_0").val(arr_val[x].TexBoxtData);
$("#DrpOperator_0").val(arr_val[x].Operator);

}
else {
//------Bind Column-------------
DrpColmValue = "<option value='" + arr_val[x].FieldId + "' selected=selected>" + arr_val[x].displayname + "</option>";

//------Bind operator Column-----
if (arr_val[x].Operator == 'AND')
DrpOperatortxt = '<option selected=selected value="AND">AND</option><option value="OR">OR</option> ';
if (arr_val[x].Operator == 'OR')
DrpOperatortxt = '<option value="AND">AND</option> <option selected=selected value="OR">OR</option>';

// ----Bind Condition Column----
else if (arr_val[x].Condition == 'contains')
DrpCondition = '<option value="-1">Please Select Condition</option><option selected=selected value="contains">contains</option>  <option value="equals">equals</option> <option value="not equal to">not equal to</option> <option value="starts with">starts with</option> <option value="does not contain">does not contain</option> <option value="less than">less than</option> <option value="greater than">greater than</option> <option value="less or equal">less or equal</option> <option value="greater or equal">greater or equal</option> <option value="includes">includes</option> <option value="excludes">excludes</option> ';

else if (arr_val[x].Condition == 'equals')
DrpCondition = '<option value="-1">Please Select Condition</option><option value="contains">contains</option>  <option selected=selected value="equals">equals</option> <option value="not equal to">not equal to</option> <option value="starts with">starts with</option> <option value="does not contain">does not contain</option> <option value="less than">less than</option> <option value="greater than">greater than</option> <option value="less or equal">less or equal</option> <option value="greater or equal">greater or equal</option> <option value="includes">includes</option> <option value="excludes">excludes</option> ';

if (arr_val[x].Condition == 'not equal to')
DrpCondition = '<option value="-1">Please Select Condition</option><option value="contains">contains</option>  <option value="equals">equals</option> <option selected=selected value="not equal to">not equal to</option> <option value="starts with">starts with</option> <option value="does not contain">does not contain</option> <option value="less than">less than</option> <option value="greater than">greater than</option> <option value="less or equal">less or equal</option> <option value="greater or equal">greater or equal</option> <option value="includes">includes</option> <option value="excludes">excludes</option> ';

else if (arr_val[x].Condition == 'starts with')
DrpCondition = '<option value="-1">Please Select Condition</option><option value="contains">contains</option>  <option value="equals">equals</option> <option value="not equal to">not equal to</option> <option selected=selected value="starts with">starts with</option> <option value="does not contain">does not contain</option> <option value="less than">less than</option> <option value="greater than">greater than</option> <option value="less or equal">less or equal</option> <option value="greater or equal">greater or equal</option> <option value="includes">includes</option> <option value="excludes">excludes</option> ';

if (arr_val[x].Condition == 'does not contain')
DrpCondition = '<option value="-1">Please Select Condition</option><option value="contains">contains</option>  <option value="equals">equals</option> <option value="not equal to">not equal to</option> <option value="starts with">starts with</option> <option selected=selected value="does not contain">does not contain</option> <option value="less than">less than</option> <option value="greater than">greater than</option> <option value="less or equal">less or equal</option> <option value="greater or equal">greater or equal</option> <option value="includes">includes</option> <option value="excludes">excludes</option> ';

else if (arr_val[x].Condition == 'less than')
DrpCondition = '<option value="-1">Please Select Condition</option><option value="contains">contains</option>  <option value="equals">equals</option> <option value="not equal to">not equal to</option> <option value="starts with">starts with</option> <option value="does not contain">does not contain</option> <option selected=selected value="less than">less than</option> <option value="greater than">greater than</option> <option value="less or equal">less or equal</option> <option value="greater or equal">greater or equal</option> <option value="includes">includes</option> <option value="excludes">excludes</option> ';

if (arr_val[x].Condition == 'greater than')
DrpCondition = '<option value="-1">Please Select Condition</option><option value="contains">contains</option>  <option value="equals">equals</option> <option value="not equal to">not equal to</option> <option value="starts with">starts with</option> <option value="does not contain">does not contain</option> <option value="less than">less than</option> <option selected=selected  value="greater than">greater than</option> <option value="less or equal">less or equal</option> <option value="greater or equal">greater or equal</option> <option value="includes">includes</option> <option value="excludes">excludes</option> ';

else if (arr_val[x].Condition == 'less or equal')
DrpCondition = '<option value="-1">Please Select Condition</option><option value="contains">contains</option>  <option value="equals">equals</option> <option value="not equal to">not equal to</option> <option value="starts with">starts with</option> <option value="does not contain">does not contain</option> <option value="less than">less than</option> <option value="greater than">greater than</option> <option  selected=selected  value="less or equal">less or equal</option> <option value="greater or equal">greater or equal</option> <option value="includes">includes</option> <option value="excludes">excludes</option> ';

if (arr_val[x].Condition == 'greater or equal')
DrpCondition = '<option value="-1">Please Select Condition</option><option value="contains">contains</option>  <option value="equals">equals</option> <option value="not equal to">not equal to</option> <option value="starts with">starts with</option> <option value="does not contain">does not contain</option> <option value="less than">less than</option> <option value="greater than">greater than</option> <option value="less or equal">less or equal</option> <option selected=selected value="greater or equal">greater or equal</option> <option value="includes">includes</option> <option value="excludes">excludes</option> ';

else if (arr_val[x].Condition == 'includes')
DrpCondition = '<option value="-1">Please Select Condition</option><option value="contains">contains</option>  <option value="equals">equals</option> <option value="not equal to">not equal to</option> <option value="starts with">starts with</option> <option value="does not contain">does not contain</option> <option value="less than">less than</option> <option value="greater than">greater than</option> <option value="less or equal">less or equal</option> <option value="greater or equal">greater or equal</option> <option selected=selected value="includes">includes</option> <option value="excludes">excludes</option> ';

if (arr_val[x].Condition == 'excludes')
DrpCondition = '<option value="-1">Please Select Condition</option><option value="contains">contains</option>  <option value="equals">equals</option> <option value="not equal to">not equal to</option> <option value="starts with">starts with</option> <option value="does not contain">does not contain</option> <option value="less than">less than</option> <option value="greater than">greater than</option> <option value="less or equal">less or equal</option> <option value="greater or equal">greater or equal</option> <option value="includes">includes</option> <option  selected=selected  value="excludes">excludes</option> ';

data = data.replace('selected=selected', '');
//data = data.replace("<option datatype=\"" + arr_val[x].datatype + "\">, value=\"" + arr_val[x].FieldId + "\">" + arr_val[x].displayname + "</option>", "<option datatype=\"" + arr_val[x].datatype + "\">, value=\"" + arr_val[x].FieldId + "\" selected=selected>" + arr_val[x].displayname + "</option>");
$('<p><tr ><td><select style="width: 68px" id="DrpOperator_' + counter + '" name="DrpOperator[' + counter + ']">' + DrpOperatortxt + '</select></td>' +
'<td><select style="width:180px;" id="drpColumn1_' + counter + '" name="drpColumn1[' + counter + ']">' + data +'</select></td>' +
'<td><select style="width:190px;" id="drpCondition_' + counter + '" name="drpCondition[' + counter + ']">' + DrpCondition + '</select></td>' +
'<td><input style="width:120px; height:22px;" type="text" onkeypress="javascript:return AlphnumericCommaKeyAndAlpha(event)" value=' + arr_val[x].TexBoxtData + ' id="txtCondition_' + counter + '"  name="txtCondition[' + counter + ']"/></td><td> <input style="margin-bottom: 10px;" type="button" id="btndel_' + counter + '" class="btn" value="Remove" /></td></tr></p>').appendTo("#tablelist");
$("#drpColumn1_" + counter).val(arr_val[x].FieldId);
counter++;
}
}
$("#HidCounter1").val(counter);
CreateDynamicDrpDwon();
},
failure: function () {
error();
}
});
}
else {
CreateDynamicDrpDwon();
}
}
/// <summary>
///------------function for Bind the Dispaly Fields while editing during the page.
/// </summary>
function BindDispalyFields() {
var counter = 1;
$('#Div1').html('');
var viewid = $('#hidViewId').val();
if (viewid > 0) {
$.ajax({
url: "CreateView.aspx/GetDispalyFields?ViewID=" + viewid,
data: {},
type: "POST",
dataType: "json",
contentType: "application/json;charset=utf-8",
success: function (data1) {

var arr_val = JSON.parse(data1.d);
for (var x = 0; x < arr_val.length; x++) {

var $clone = $("#div_columnname").clone();
$clone.attr('id', 'div_' + arr_val[x].displayname);
$clone.find("#imgdisplay").attr('id', 'imgdisplay_' + arr_val[x].displayname);
$clone.find("#columnName").attr('id', arr_val[x].displayname);
$clone.find("#btn_div").attr('id', arr_val[x].FieldId);
$clone.find("#condMatch").attr('id', 'condMatch_' + arr_val[x].displayname);
$clone.find(".colummnName").html(arr_val[x].displayname);
$clone.find("#divadd_name").attr('id', 'divadd_' + arr_val[x].FieldId);
$clone.find("#imgCondition_name").attr('id', 'imgCondition_' + arr_val[x].displayname);
$("#" + arr_val[x].FieldId).hide();
$clone.appendTo('#showDragnDrop').show();
$("#Div1").append(arr_val[x].FieldId + ",");
}
},
failure: function () {
error();
}
});
}
}
/// <summary>
/// ---------seeting for small add button of column name div.
/// </summary>
$(document).on('mouseenter', '.divStyle', function (id) {
$(this).find('.btn').show();
});

$(document).on('mouseleave', '.divStyle', function (id) {
$(this).find('.btn').hide();
});

$(document).ready(function () {
$("#ddlMailingList").chosen();
});

$(document).on('click', "#ddlMailingList_chzn", function () {
$(this).children().removeClass('errorClass');
});

$(document).on('click', '[id^=divClick]', function () {

var ids = (this.id).split('_');
var id = "imgdisplay_" + ids[1];
if (!$("#" + id).hasClass('opened')) {

$("#" + id).attr('src', '../images/minus_ls.gif');
$("#" + id).addClass('opened');
$("#" + id).parent().parent().parent().css('background-color', '#d9edf7');
$("#" + id).parent().parent().first().css('background-color', '#d9edf7');
$("#" + ids[1]).slideDown();
}
else {
$("#" + id).attr('src', '../images/plus.png');
$("#" + ids[1]).slideUp();
$("#" + id).removeClass('opened');
$("#" + id).parent().parent().parent().css('background-color', '');
$("#" + id).parent().parent().first().css('background-color', '');
}
});
/// <summary>
///------------function for get the text and dropdown value for saving.
/// </summary>
function GetTxtAndDrpVal() {
var pp = validateme();
$("#Div1").each(function () {
element += $(this).html() + ",";
});
var count = 0;
var AllData = [];
var lenth = 0;
$("#tablelist p").each(function () {
var drpOperatortxt = "";
if (lenth != 0) drpOperatortxt = $(this).find('[id^="DrpOperator_"] ').val();
var drpColumnId = $(this).find('[id^="drpColumn1_"] ').val();
var drpConditionValue = $(this).find('[id^="drpCondition_"] ').val();
var txtconditiontxt = $(this).find("input[type=text]").attr('value');
if (drpColumnId == "-1" && drpConditionValue == "-1" && txtconditiontxt == "") {
if (lenth == 0) {
count = 1;

return false;
}
lenth++;
}
else if (drpColumnId == "-1" || drpConditionValue == "-1" || txtconditiontxt == "") {
count = 2;
lenth++;
return false;
}
else {
AllData.push(drpOperatortxt + "-" + drpColumnId + "-" + drpConditionValue + "-" + txtconditiontxt);
lenth++;
}

});
var df = element;
df = df.replace(/\,/g, '');
df = df.replace(/undefined/g, '')
if (count == 1) {
FilterConditionValid("Please add at least one Filter conditions.");
return false;
}
else if (count == 2) {
FilterConditionValid("Please fill all fields of Filter conditions.");
return false;
}
if (pp == true && df != "" && df != "undefined,") {
var viewName = $("#txtViewName").attr('value');
var IsDefault = $("#chkIsDefault").attr("checked") ? 1 : 0;
var element = "";
$("#Div1").each(function () {
element += $(this).html() + ",";
});
var viewid = $('#hidViewId').val();
var parameters = "{'AllData':'" + AllData + "','viewName':'" + viewName + "','defaultValue':'" + IsDefault +"','columns':'" + element + "','Id':'" + viewid + "'}";
$.ajax({
url: "CreateView.aspx/InsertCreareViewName",
data: parameters,
type: "POST",
dataType: "json",
cache: false,
contentType: "application/json;charset=utf-8",
success: function (data1) {
if (data1.d == "DuplicateViewName") {
DuplicateViewName();
}
else {
if (viewid == "") {
//If viewid is blank,show msg view name is created sucessfully.
window.location.href = "Leads.aspx?CreateView=Create";
}
else {
//If viewid is not blank,show msg view name is updated sucessfully.
window.location.href = "Leads.aspx?CreateView=Update";
}
}
},
failure: function () {
error();
}
});
}
else {
FilterConditionValid("Please add the column name from the right hand side to Dispaly Fields");
}
}
/// <summary>
///----------Check Max characters for a view name max should be 40.
/// </summary>
$('#txtViewName').live('keyup', function (textBox, e, maxLength) {
var VNameLength = $(this).val().length;
var VNametext = $(this).val();
if (VNameLength > 39) {
alert("You can enter max 40 character.");
return true;
}
else {
return false;
}
});
/// <summary>
///----------Call GetTxtAndDrpVal() on Both Save Button Click(Top Save button and Bottom save button).
/// </summary>

$(document).ready(function () {
$('#btnSave').click(function () {
GetTxtAndDrpVal();
});
});

$(document).ready(function () {
$('#btnSave1').click(function () {
GetTxtAndDrpVal();
});
});
/// <summary>
///----------Function and AJAX method for Binding the Columns.
/// </summary>
$(document).ready(function () {
$.ajax({
url: "CreateView.aspx/Binddiv",
data: {},
type: "POST",
dataType: "json",
contentType: "application/json;charset=utf-8",
success: function (data1) {
$("#divSource").append(data1.d);
},
failure: function () {
error();
}
});
});
/// <summary>
///----------Bind Defautl dropdwon of coloum on page.
/// </summary>
$(document).ready(function () {
$.ajax({
url: "CreateView.aspx/LoadCreateView",
data: {},
type: "POST",
dataType: "json",
contentType: "application/json;charset=utf-8",
success: function (data1) {
var arr_val = JSON.parse(data1.d);
var opt_ddl = [];
for (var x = 0; x < arr_val.length; x++) {
opt_ddl.push("<option dataType=" + arr_val[x].dataType + " value=" + arr_val[x].id + ">" + arr_val[x].displayname + "</option>");
}
$("#drpColumn1_0").append(opt_ddl);
var appendData = '<option value="-1">Please Select Condition</option>';
$("#drpCondition_0").html(appendData);
BindFilterConditions();
BindDispalyFields();
},
failure: function () {
error();
}
});
});
/// <summary>
///----------Calling ajax at load time to Display Coloumn.
/// </summary>
$(document).ready(function () {
$("#ddlMailingList").change(function () {
$('#Div1').html('');
$.ajax({
url: "CreateView.aspx/Binddiv",
data: {},
type: "POST",
dataType: "json",
contentType: "application/json;charset=utf-8",
success: function (data1) {
$("#divSource").html('');
$("#divSource").append(data1.d);
},
failure: function () {
error();
}
});
$("#showDragnDrop").find($('div')).remove();
$("#ddlMailingList_chzn").children().removeClass('errorClass');
if ($(this).val() != -1) {
$.ajax({
url: "CreateView.aspx/AddColumnToDispalyField",
data: {},
type: "POST",
dataType: "json",
contentType: "application/json;charset=utf-8",
success: function (data1) {
if (data1.d == "NoData") {
// nodata do nothing
}
else if (data1.d == 'error') {
//error handling
}
else {
DisplayConditions(data1.d);
}
},
failure: function () {
error();
}
});
}
});
});
/// <summary>
///----------Function for changing the image and For Slideup and SlideDown.
/// </summary>
function Display(id) {
var ids = id.split('_');
if (!$("#" + id).hasClass('opened')) {
$("#" + id).attr('src', '../images/minus_ls.gif');
$("#" + id).addClass('opened');
$("#" + id).parent().parent().parent().css('background-color', '#d9edf7');
$("#" + id).parent().parent().first().css('background-color', '#d9edf7');
$("#" + ids[1]).slideDown();
}
else {
$("#" + id).attr('src', '../images/plus.png');
$("#" + ids[1]).slideUp();
$("#" + id).removeClass('opened');
$("#" + id).parent().parent().parent().css('background-color', '');
$("#" + id).parent().parent().first().css('background-color', '');
}
}
/// <summary>
///----------Function to show error.
/// </summary>
function error() {
var $alertdiv = $('<div id = "alertmsg" />');
$alertdiv.text("Please select a valid User Name.");
$alertdiv.bind('click', function () {
$(this).slideUp(200);
$("#alertmsg").remove();
});
$(document.body).append($alertdiv);
$("#alertmsg").slideDown("slow");
setTimeout(function () {
$alertdiv.slideUp(200);
$("#alertmsg").remove();
}, 3000);
}
/// <summary>
///----------Function to show error for Filter Condition.
/// </summary>
function FilterConditionValid(msg) {
$('#aaaalertmsg').html('');
$('#aaaalertmsg').append(msg);
$('#aaaalertmsg').show();
$('#aaaalertmsg').dialog({
resizable: false,
height: 180,
width: 450,
modal: true,
buttons: {
"Okay": function () {
$(this).dialog("close");
}
}
});
}
/// <summary>
///----------Function to show error.
 /// </summary>
function errormssg() {
$('#aaaalertmsg').html('');
$('#aaaalertmsg').append("Please add the column name from the right hand side to Dispaly Fields.");
$('#aaaalertmsg').show();
$('#aaaalertmsg').dialog({
resizable: false,
height: 180,
width: 450,
modal: true,
buttons: {
"Okay": function () {
$(this).dialog("close");
}
}
});
}
/// <summary>
///----------fuction for duplicate view name.
/// </summary>
function DuplicateViewName() {
$('#aaaalertmsg').html('');
$('#aaaalertmsg').append("View Name is allready exits.");
$('#aaaalertmsg').show();
$('#aaaalertmsg').dialog({
resizable: false,
height: 180,
width: 450,
modal: true,
buttons: {
"Okay": function () {
$(this).dialog("close");
}
}
});
}
/// <summary>
///----------fuction for show if Filter Condition is more than 5.
/// </summary>
function errMsgFoeLenght() {
$('#errforfiltercondition').html('');
$('#errforfiltercondition').append("Filter Condition Should not more than ten.");
$('#errforfiltercondition').show();
$('#errforfiltercondition').dialog({
resizable: false,
height: 180,
width: 450,
modal: true,
buttons: {
"Okay": function () {
$(this).dialog("close");
}
}
});
}

/// <summary>
///----------Method for adding the Column name in dispaly fieid.
 /// </summary>
$(function () {
$(document).on('click', '[id^=btn_]', function () {
var ItmID = (this.id).split('_')[1];
var ItmName = $(this).parent().attr('displayname');
var element = "";
$("#Div1").each(function () {
element += $(this).html() + ",";
});

var arrelement = element.split(',');
if (arrelement.length > 0) {
for (var i = 0; i < arrelement.length; i++) {
if ($.trim(arrelement[i]) == ItmID) {
alert('Duplicate Column is not allowed');
return;
}
}
}
$("#Div1").append(ItmID + ",");
$("#" + ItmID).hide();
// Making Clone of Div to add it Dynamically
var $clone = $("#div_columnname").clone();
$clone.attr('id', 'div_' + ItmName);
$clone.find("#imgdisplay").attr('id', 'imgdisplay_' + ItmName);
$clone.find("#columnName").attr('id', ItmName);
$clone.find("#btn_div").attr('id', ItmID);
$clone.find("#condMatch").attr('id', 'condMatch_' + ItmName);
$clone.find(".colummnName").html(ItmName);
$clone.find("#divadd_name").attr('id', 'divadd_' + ItmID);
$clone.find("#imgCondition_name").attr('id', 'imgCondition_' + ItmName);
$clone.appendTo('#showDragnDrop').show();
$(this.id).attr('disabled', 'disabled');
});
});
/// <summary>
///----------Function for Removing the Div
/// </summary>
$(document).on('click', '.close', function (id) {
var ids = this.id;
var success = true;
var elen = $(this).parent().parent().attr('id');
if ($("#AddCondition_" + elen.split('_')[1]).children().length == 1) {
$("#AddCondition_" + elen.split('_')[1]).children().find("input[type=text]").each(function () {
if ($(this).val() == '') {
success = false;
}
});
}
if (success) {
var a = $("#Div1").html();
var b = a.replace(ids, '');
$("#Div1").html(b);
$("#" + (ids)).parent().parent().remove();
$("#" + (ids)).show();
}
});

/// <summary>
///----------Order the display.
/// </summary>

$(document).ready(function () {
var selected = 0;
$(document).on("click", "#showDragnDrop > div", function () {
selected = $(this).index();
//alert(selected);
$('.divcolor').removeClass('divcolor'); ;
$(this).addClass('divcolor');
});
//Move to up
$("#up").click(function (e) {
var itemlist = $('#showDragnDrop');
var len = $(itemlist).children().length;
// alert(selected);
e.preventDefault();
if (selected > 1) {
jQuery($(itemlist).children().eq(selected - 1)).before(jQuery($(itemlist).children().eq(selected)));
OrderList(selected, 'up');
selected = selected - 1;
}
});
//Move to down
$("#down").click(function (e) {
var itemlist = $('#showDragnDrop');
var len = $(itemlist).children().length;
//alert(selected);
e.preventDefault();
if (selected < len) {
jQuery($(itemlist).children().eq(selected + 1)).after(jQuery($(itemlist).children().eq(selected)));
OrderList(selected, 'down');
selected = selected + 1;
}
});
//Move to top
$("#top").click(function (e) {
var itemlist = $('#showDragnDrop');
var len = $(itemlist).children().length;
//alert(selected);
e.preventDefault();
if (selected < len && selected > 1) {
jQuery($(itemlist).children().eq(1)).before(jQuery($(itemlist).children().eq(selected)));
OrderList(selected, 'top');
selected = 1;
}
});
//Move to bottom
$("#bottom").click(function (e) {
var itemlist = $('#showDragnDrop');
var len = $(itemlist).children().length;
//alert(selected);
e.preventDefault();
if (selected < len - 1) {
jQuery($(itemlist).children().eq(len - 1)).after(jQuery($(itemlist).children().eq(selected)));
OrderList(selected, 'bottom');
selected = len - 1;
}
});

});
function OrderList(selected, order) {
var idList = $("#Div1").html();
var arry = idList.split(',');
switch (order) {
case 'top':
var temp = arry[0];
arry[0] = arry[selected - 1];
arry[selected - 1] = temp;
break;
case 'bottom':
var temp = arry[arry.length - 2];
arry[arry.length - 2] = arry[selected - 1];
arry[selected - 1] = temp;
break;
case 'up':
var temp = arry[selected - 2];
arry[selected - 2] = arry[selected - 1];
arry[selected - 1] = temp;
break;
case 'down':
var temp = arry[selected - 1];
arry[selected - 1] = arry[selected];
arry[selected] = temp;
break;
}
$("#Div1").html('');
var element = '';
for (var i = 0; i < arry.length - 1; i++) {
element = element + arry[i] + ',';
}
$("#Div1").html(element);
}