Anup Ghume's Blog: March 2015

Friday, 27 March 2015

Store data into SP list using Java Query.

Details functionality with snap

when click on Add new row , one below popup will open .

when I fill some data into these text box . the particular data should stored to one SP List fields so my JSON will use that data . For that I have created " ContactPersonDetails" field in SP List.

Fill some data and click on save . The respective data will be stored into JQX grid.

We can edit also - for edit go to edit button - one popup will open with some value which we wanted to edit

and edited value will be stored into JQX grid.

Finally click on Sharepoint save button will store these data into SP list field " ContactPersonDetails".

In SP List we have a column name : ContactPersonDetails where I will store these data so while displaying data my JSON will use these field.

I will use this column "ContactPersonDetails" when I wanted to display data in JQX grid OR display data while edit. My JSON will refer these field.

will set display none for " ContactPersonDetails" field in .aspx

<SharePoint:FormField runat="server" id="ff5{$Pos}" ControlMode="New" FieldName="ContactPersonDetails" __designer:bind="{ddwrt:DataBind('i',concat('ff5',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@ContactPersonDetails')}"/>

</div>

We need to js one will be use for New form [In new form will add all JS field into SP list field ContactPersonDetails ] and other will be use for Edit form.

1] .JS for new form

var editrow = -1;

var editrowEdit = -1;

$(document).ready(function (source, arguments) {

ExecuteOrDelayUntilScriptLoaded(HideForNewForm, "sp.js");

$('input[title="Date"]')[0].readOnly = true;

$('input[title="NameOfBusinessPartner"]').focus();

//$('input[title="Purpose"]')[0].css("width","500px");

});

function HideForNewForm() {

var data = null;

var source =

{

localdata: data,

datatype: "array",

datafields:

[

{ name: 'Designation', type:'string' },

{ name: 'Qualification', type: 'string' },

{ name: 'NetWorth', type: 'int' }

addrow: function (rowid, rowdata, position, commit) {

// synchronize with the server - send insert command

// call commit with parameter true if the synchronization with the server is successful

//and with parameter false if the synchronization failed.

// you can pass additional argument to the commit callback which represents the new ID if it is generated from a DB.

commit(true);

}

};

var dataAdapter = new $.jqx.dataAdapter(source);

// initialize jqxGrid

$("#jqxgrid").jqxGrid(

{

width: 745,

height: 350,

source: dataAdapter,

altrows: true,

sortable: true,

autoheight: true,

selectionmode: 'multiplecellsextended',

columns: [

{ text: 'Designation', datafield: 'Designation', width: 150,align: 'center' },

{ text: 'Qualification', datafield: 'Qualification', width: 438,align: 'center' },

{ text: 'Net Worth', datafield: 'NetWorth', width: 100, columntype: 'numberinput',align: 'center' },

{ text: 'Edit', datafield: 'Edit', width: 55, columntype: 'button', align: 'center', cellsrenderer: function () {

return "Edit";

}, buttonclick: function (row) {

// open the popup window when the user clicks a button.

editrowEdit = row;

var offset = $("#jqxgrid").offset();

$("#popupWindowEdit").jqxWindow({ position: { x: parseInt(offset.left) + 60, y: parseInt(offset.top) + 60 } });

// get the clicked row's data and initialize the input fields.

var dataRecord = $("#jqxgrid").jqxGrid('getrowdata', editrowEdit);

$("#EditDesignation").val(dataRecord.Designation);

$("#EditQualification").val(dataRecord.Qualification);

$("#EditNetWorth").val(dataRecord.NetWorth);

// show the popup window.

$("#popupWindowEdit").jqxWindow('open');

}

]

});

$("#popupWindow").jqxWindow({

width: 400, resizable: false, isModal: true, autoOpen: false, cancelButton: $("#Cancel"), modalOpacity: 0.01

});

$("#popupWindowEdit").jqxWindow({

width: 400, resizable: false, isModal: true, autoOpen: false, cancelButton: $("#Cancel"), modalOpacity: 0.01

});

//$("#jsonExport").jqxButton({ theme: theme });

$("#addnewRow").click(function () {

//editrow = row;

$('#popupWindow').css('display', '');

editrow = editrow + 1;

var offset = $("#jqxgrid").offset();

$("#popupWindow").jqxWindow({ position: { x: parseInt(offset.left) + 60, y: parseInt(offset.top) + 60 } });

// get the clicked row's data and initialize the input fields.

//var dataRecord = $("#jqxgrid").jqxGrid('getrowdata', editrow);

$("#Designation").val("");

$("#Qualification").val("");

$("#NetWorth").val("");

// show the popup window.

$("#popupWindow").jqxWindow('open');

});

$("#jsonExport").click(function () {

$("#jqxgrid").jqxGrid('exportdata', 'json', 'jqxGrid');

});

///This event fires when add new row in popup and save the row

$("#Save").click(function () {

if (editrow >= 0) {

var row = {

Designation: $("#Designation").val(), Qualification: $("#Qualification").val(), NetWorth: $("#NetWorth").val()

};

//var rowID = $('#jqxgrid').jqxGrid('getrowid', editrow);

$('#jqxgrid').jqxGrid('addrow', null, row);

//var datarow = generaterow();

//var commit = $("#jqxgrid").jqxGrid('addrow', null, null);

$("#popupWindow").jqxWindow('hide');

var MaterialdescriptioJSON = $("#jqxgrid").jqxGrid('exportdata', 'json');

//alert(MaterialdescriptioJSON);

//var MaterialDescription=$('inout[title="MaterialDescription"]');

$('textarea[title="ContactPersonDetails"]').val("");

var MaterialDescription = $('textarea[title="ContactPersonDetails"]').val(MaterialdescriptioJSON);

}

});

$("#SaveEdit").click(function () {

if (editrowEdit >= 0) {

var row = {

Designation: $("#EditDesignation").val(), Qualification: $("#EditQualification").val(), NetWorth: $("#EditNetWorth").val()

};

var rowID = $('#jqxgrid').jqxGrid('getrowid', editrowEdit);

$('#jqxgrid').jqxGrid('updaterow', rowID, row);

$("#popupWindowEdit").jqxWindow('hide');

var MaterialdescriptioJSON = $("#jqxgrid").jqxGrid('exportdata', 'json');

//alert(MaterialdescriptioJSON);

//var MaterialDescription=$('inout[title="MaterialDescription"]');

$('textarea[title="PartDetails"]').val("");

var MaterialDescription = $('textarea[title="PartDetails"]').val(MaterialdescriptioJSON);

}

});

}

function IsNumeric(e) {

var keyCode = e.which ? e.which : e.keyCode

var ret = ((keyCode >= 48 && keyCode <= 57) || specialKeys.indexOf(keyCode) != -1);

document.getElementById("error").style.display = ret ? "none" : "inline";

return ret;

}

var specialKeys = new Array();

specialKeys.push(8); //Backspace

function IsNumeric(e) {

var keyCode = e.which ? e.which : e.keyCode

var ret = ((keyCode >= 48 && keyCode <= 57) || specialKeys.indexOf(keyCode) != -1);

document.getElementById("error").style.display = ret ? "none" : "inline";

return ret;

}

2] .JS for Display/View form

$(document).ready(function (source, arguments) {

ExecuteOrDelayUntilScriptLoaded(BusinessPartnersLOIDetails, "sp.js");

//$('span[title="Data Entry"]').find('input [Type="radio"]')[0].onchange = TypeofEntry_OnChange;

//$('span[title="Upload File"]').find('input [Type="radio"]')[0].onchange = TypeofEntry_OnChange;

//$('span[title="Data Entry"]').children()[0].onchange = TypeofEntry_OnChange;

//$('span[title="Upload File"]').children()[0].onchange = TypeofEntry_OnChange;

});

function BusinessPartnersLOIDetails() {

var MaterialDescJSON = $('#anchorLOIDetails')[0].innerHTML;

//alert(MaterialDescJSON);

var Decoded = MaterialDescJSON.replace(/&quot;/g, '"');

//alert(Decoded);

var NewDecoded = Decoded.replace(/,"Edit":""/g, '');

//alert(NewDecoded);

//var Decoded = JSON.parse(MaterialDescJSON);

//alert(Decoded); //var Decoded = JSON.stringify(MaterialDescJSON);

//alert(Decoded);

var data="";

if(Decoded!="" && Decoded!=null){

data=Decoded;

}

else{

data=null;

}

var source =

{

datatype: "json",

localdata: data,

//datafields:

//[

//{ name: 'SrNo', type: 'string' },

//{ name: 'MaterialDescription', type: 'string' },

//{ name: 'Unit', type: 'int' },

//{ name: 'RequiredQty', type: 'int' },

//{ name: 'IssuedQty', type: 'int' },

//{ name: 'Remark', type: 'string' }

//]

datafields:

[

{ name: 'Designation', type: 'string' },

{ name: 'Qualification', type: 'string' },

{ name: 'Net Worth', type: 'string' }

]

};

var dataAdapter = new $.jqx.dataAdapter(source);

// initialize jqxGrid

$("#jqxgrid").jqxGrid(

{

width: 745,

source: dataAdapter,

autoheight: true,

columnsresize: true,

columns: [

{ text: 'Designation', datafield: 'Designation', width: 200,align: 'center'},

{ text: 'Qualification', datafield: 'Qualification', width: 445 ,align: 'center'},

{ text: 'Net Worth', datafield: 'Net Worth', width: 100,align: 'center' }

]

});

$('#anchorLOIDetails')[0].className = 'hide';

///This event fires when add new row in popup and save the row

}

Here I am going to design two form using SP Designer One for New I.e. Form will open when click on New Item And another for View.

1] Form for new item - Click on

In .aspx for new we need to add reference of BisunessPartnerLOIForm.js , jquery-1.11.1.min.js and others .js file which will support for JQX Grid , We can get these js from here .

we need to add below one for "Add New Row" button.

<div id="jqxgrid"></div> - for jq grid

below html use for open popup on "Add new row "button .

<table>

<tr>

<td align="right">Designation:</td>

</tr>

<tr>

<td align="right">Qualification:</td>

</tr>

<tr>

<td align="right">Net Worth:</td>

<span id="error" style="color: Red; display: none">* Input digits (0 - 9)</span>

</td>

</tr>

<tr>

</tr>

</table>

</div>

And Below html use for open popup while edit

<table>

<tr>

<td align="right">Designation:</td>

</tr>

<tr>

<td align="right">Qualification:</td>

</tr>

<tr>

<td align="right">Net Worth:</td>

<span id="errors" style="color: Red; display: none">* Input digits (0 - 9)</span>

</td>

</tr>

<tr>

</tr>

</table>

</div>

2] Form for View

In .aspx for view we need to put below html. and set a reference of js for Display/View form.

</div>

</div>

We need to add following reference in our .aspx for both form (I.e. New and View form) using SP

Designer

Tuesday, 17 March 2015

Passing Parameters to the CAML Query in SharePoint.

private static string GenerateAlertQuery(string frequency, DateTime lastRun)
{

string query = @"<Where>
<And>
<And>
<Eq>
<FieldRef Name='Active' />
<Value Type='Boolean'>1</Value>
</Eq>
<Eq>
<FieldRef Name='Frequency' />
<Value Type='Choice'>{0}</Value>
</Eq>
</And>
<Lt>
<FieldRef Name='Last_x0020_Run' />
<Value Type='DateTime' IncludeTimeValue='True'>{1}</Value>
</Lt>
</And>
</Where>";
}

{0} in CAML Query will refer first parameter i.e. frequency
{1) in CAML Query will refer second parameter i.e. lastRun.

Get Event Log into text file

If Event Viewer is not working. In that case we can get log into one text file. For that execute below command on windows powershell.

Get-EventLog Application -Newest 10 -Source PPAP | Format-List | Out-File "C:\log.text"

10 - indicate latest 10 log.
You can replace with any number.

Friday, 6 March 2015

regular expression pattern matching in Sharepoint

Linq/regular expression pattern matching is the best option when we have to get something custom list item as result.

For example : I have a title column in list and title column contains value like

cf-3

cf-3/4-cr

cf-3/4

cf-3/4-sb-30

cf-3 1/2

cf 3

cf 3/4 cr

cf 3/4

cf 3/4 sb 30

cf 3 1/2

Enter Title :

If user enter cf-3 OR cf 3 and wants a result like in both case i.e. with dash(-) or with space( ).

cf-3,

cf-3/4-cr

cf-3/4

cf-3/4-sb-30

cf-3 1/2

cf 3

cf 3/4 cr

cf 3/4

cf 3/4 sb 30

cf 3 1/2

To achieve this , will use regular expression pattern matching.

First will get contains query for dash search (i.e. cf-3) and for space search (i.e. cf 3). call SearchDashResult() method for dash search (i.e. cf-3) and SearchSpaceResult() method for space search (i.e. cf 3).

Case "Title"

If (txtTProp4.Text.Trim().IndexOf("-") <> -1) Then

wordArrPattern = txtTProp4.Text.Trim().Split("-")

SearchDashResult()

ElseIf (txtTProp4.Text.Trim().IndexOf(" ") <> -1) Then

wordArrPattern = txtTProp4.Text.Trim().Split(" ")

SearchSpaceResult()

Else

strArr.Add("<Contains><FieldRef Name='Title' /><Value Type='Text'>" & txtTProp4.Text.Trim() & "</Value></Contains>")

End If

Then , will get contains query for dash search (i.e. cf-3) and for space search (i.e. cf 3).

Public Sub SearchDashResult()

If wordArrPattern.Length = 2 Then 'A-B

beforeSpace = wordArrPattern(0)

afterSpace = wordArrPattern(1)

withDash = "-" & afterSpace

withSpace = " " & afterSpace

strArr.Add("<Or><And><Contains><FieldRef Name='Title'/><Value Type='Text'>" & beforeSpace & "</Value></Contains><Contains><FieldRef Name='Title'/><Value Type='Text'>" & withDash & "</Value></Contains></And><And><Contains><FieldRef Name='Title'/><Value Type='Text'>" & beforeSpace & "</Value></Contains><Contains><FieldRef Name='Title' /> <Value Type='Text'>" & withSpace & "</Value></Contains></And></Or>")

End If

End Sub

Public Sub SearchSpaceResult()

'wordArrPattern = txtTProp2.Text.Trim().Split(" ")

If wordArrPattern.Length = 2 Then 'A B

beforeSpace = wordArrPattern(0)

afterSpace = wordArrPattern(1)

withDash = "-" & afterSpace

withSpace = " " & afterSpace

End If

End Sub

Here, we are building caml query.

If strArr.Count <> 0 Then

entireCAMLQuery = BuildingCAMLUsingStringArr(strArr)

query.Query = entireCAMLQuery.ToString()

query.ViewAttributes = "Scope=""Recursive"""

GetItemThroughtLoop(webSite, strList, query)

strArr.Clear()

End If

Private Function BuildingCAMLUsingStringArr(strArr As List(Of String)) As String

Dim caml As String = String.Empty

Try

If strArr.Count = 1 Then

caml = strArr(0)

ElseIf strArr.Count > 1 Then

Dim count As Integer = strArr.Count - 1

caml = CAMLRecursiveCall(strArr, count)

End If

Catch

Throw

Finally

End Try

Return "<Where>" & caml & "</Where>"

End Function

Private Function CAMLRecursiveCall(strArr As List(Of String), count As Integer) As String

Dim strBuild As New StringBuilder()

If count > 0 Then

strBuild.Append("<And>")

strBuild.Append(CAMLRecursiveCall(strArr, count - 1))

strBuild.Append(strArr(count))

strBuild.Append("</And>")

Else

strBuild.Append(strArr(count))

End If

Return strBuild.ToString()

End Function

And here , will create linq expression which will give a result which we want

Public Sub GetItemThroughtLoop(webSite As SPWeb, ByVal strList As String, serachQuery As SPQuery)

Dim site As String = webSite.Url

Dim List As SPList = webSite.Lists(strList)

Dim collListItems As SPListItemCollection = List.GetItems(serachQuery)

Dim txt1val As String = Convert.ToString(txtTProp1.Text.Trim())

Dim txt2val As String = Convert.ToString(txtTProp2.Text.Trim())

Dim txt3val As String = Convert.ToString(txtTProp3.Text.Trim())

Dim txt4val As String = Convert.ToString(txtTProp4.Text.Trim())

If ((dropListcol1T.Text.Trim() = "Title") OrElse (dropListcol2T.Text.Trim() = "Title") OrElse (dropListcol3T.Text.Trim() = "Title") OrElse (dropListcol4T.Text.Trim() = "Title")) Then

If (((txt1val.IndexOf("-") <> -1) And (dropListcol1T.Text.Trim() = "Title")) OrElse ((txt2val.IndexOf("-") <> -1) And (dropListcol2T.Text.Trim() = "Title")) OrElse ((txt3val.IndexOf("-") <> -1) And (dropListcol3T.Text.Trim() = "Title")) OrElse ((txt4val.IndexOf("-") <> -1) And (dropListcol4T.Text.Trim() = "Title")) OrElse ((txt1val.IndexOf(" ") <> -1) And (dropListcol1T.Text.Trim() = "Title")) OrElse ((txt2val.IndexOf(" ") <> -1) And (dropListcol2T.Text.Trim() = "Title")) OrElse ((txt3val.IndexOf(" ") <> -1) And (dropListcol3T.Text.Trim() = "Title")) OrElse ((txt4val.IndexOf(" ") <> -1) And (dropListcol4T.Text.Trim() = "Title"))) Then

getMyPattern = SetResultPatternForTitle()

strArrPattern = getMyPattern.Split("/")

sPattern = strArrPattern(0)

sPattern1 = strArrPattern(1)

End If

If (dropListcol4T.Text.Trim = "Doc Num") Then

getPatternForDocNum = SetResultPatternForDocNum()

strArrPattern = getPatternForDocNum.Split("/") ' will create new var. for that - it's for just testing

sPattern = strArrPattern(0) ' will create new var. for that - it's for just testing

sPattern1 = strArrPattern(1) ' will create new var. for that - it's for just testing

End If

If collListItems.Count <> 0 Then

Dim dt As DataTable

dt = collListItems.GetDataTable()

Dim source = dt.AsEnumerable()

If ((dropListcol1T.Text.Trim() = "Title") OrElse (dropListcol2T.Text.Trim() = "Title") OrElse (dropListcol3T.Text.Trim() = "Title") OrElse (dropListcol4T.Text.Trim() = "Title")) Then

If ((txt1val.IndexOf("-") <> -1) OrElse (txt1val.IndexOf(" ") <> -1)) OrElse ((txt2val.IndexOf("-") <> -1) OrElse (txt2val.IndexOf(" ") <> -1)) OrElse ((txt3val.IndexOf("-") <> -1) OrElse (txt3val.IndexOf(" ") <> -1)) OrElse ((txt4val.IndexOf("-") <> -1) OrElse (txt4val.IndexOf(" ") <> -1)) Then

Dim FilterResults = From matchingItem In source Where ((Regex.IsMatch(matchingItem.Field(Of String)("Title"), sPattern, RegexOptions.IgnoreCase)) OrElse (Regex.IsMatch(matchingItem.Field(Of String)("Title"), sPattern1, RegexOptions.IgnoreCase))) Select matchingItem

getItemThroughtRowCollection(webSite, strList, FilterResults)

Else

getItemThroughtRowCollection(webSite, strList, source)

End If

Else

getItemThroughtRowCollection(webSite, strList, source)

End If

End Sub