Prasad Bolla's SharePoint Blog
Click Here to go through the Interesting posts within my Blog.

Click Here to go through the new posts in my blog.

Thursday, December 20, 2012

How to generate a Dynamic SpQuery using Client Object Model and JQuery

List Data


Aspx
<%@ Page masterpagefile="~masterurl/default.master" language="C#" inherits="Microsoft.SharePoint.WebPartPages.WebPartPage, Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" meta:progid="SharePoint.WebPartPage.Document" meta:webpartpageexpansion="full" %>
               

<asp:Content id="Content1" runat="Server" contentplaceholderid="PlaceHolderMain">
<style type="text/css">
.HeadingClass
{
                font-size:13px;
                background-color:blue;
                color:white;
                font-weight:bold;
}
.LoopRecordsClass
{
                font-size:13px;
                background-color:Green;
                color:white;
}

.LoopRecordsClass a
                                                {
                                                                text-decoration:none;
                                                                font-size:13px;
                                                                color:white;
                                                }
                                               
                                                .LoopRecordsClass a:hover
                                                 {
                                                                text-decoration:underline;
                                                                font-size:13px;
                                                                color:white;
                                                 }
                                                 
                                                 .LoopRecordsClass a:after
                                                 {
                                                                text-deocration:none;
                                                                font-size:13px;
                                                                color:white;
                                                 }
</style>
<script type="text/javascript" src="/UserPages/JS/jquery-1.7.2.min.js"></script>
<script type="text/javascript" src="/UserPages/JS/Company_Advanced_Search.JS"></script>

<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td>
<table width="100%" cellpadding="0" cellspacing="0">
<tr><td colspan="6">&nbsp;</td></tr>
<tr><td colspan="6">&nbsp;</td></tr>
<tr>
<td colspan="6" align="center"><strong>Company Data Advanced Search</strong></td>
</tr>
<tr><td colspan="6">&nbsp;</td></tr>
<tr>
<td>Enter Employee Name</td>
<td><input type="text" id="txtEmpName" /></td>
<td>Select Employee ID</td>
<td><select id="ddlEmployeeID"></select></td>
<td>Select Category</td>
<td><select id="ddlCategory"></select></td>
</tr>
<tr>
<td>Select Position</td>
<td><select id="ddlPosition"></select></td>
<td>Enter Salary</td>
<td><input type="text" id="txtSalary" /></td>
<td>Enter Address</td>
<td><input type="text" id="txtAddress" /></td>
</tr>
<tr>
<td colspan="6" align="center">
<input type="button" value="Search" onclick="JavaScrip:SearchCompanyData();" />
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="2" id="tdDynamicQuery"></td>
</tr>
</table>

</asp:Content>
Logic JS File
var myColl="";
var PositionColl="";
var CategoryColl="";
var EmployeeIDColl="";

function SearchCompanyData()
{
                var count=0;
                var strInnerQuery=new Array();
                var sQuery="";
               
                if($("#txtEmpName").val()!="")
                {
                                count++;
                                strInnerQuery[count]="<BeginsWith><FieldRef Name='Title' /><Value Type='Text'>"+$("#txtEmpName").val()+"</Value></BeginsWith>";
                }
               
                if($("select[id='ddlEmployeeID'] option:selected").index()!=0)
                {
                                count++;
                                strInnerQuery[count]="<Eq><FieldRef Name='EmpID' /><Value Type='Text'>"+$("select[id='ddlEmployeeID'] option:selected").text()+"</Value></Eq>";
                }
               
                if($("select[id='ddlCategory'] option:selected").index()!=0)
                {
                                count++;
                                strInnerQuery[count]="<Eq><FieldRef Name='Category' LookupId='TRUE' /><Value Type='Lookup'>"+$("#ddlCategory").val()+"</Value></Eq>";
                }
               
                if($("select[id='ddlPosition'] option:selected").index()!=0)
                {
                                count++;
                                strInnerQuery[count]="<Eq><FieldRef Name='Position' LookupId='TRUE' /><Value Type='Lookup'>"+$("#ddlPosition").val()+"</Value></Eq>";
                }
               
                if($("#txtSalary").val()!="")
                {
                                count++;
                                strInnerQuery[count]="<Eq><FieldRef Name='Salary' /><Value Type='Number'>"+$("#txtSalary").val()+"</Value></Eq>";
                }
               
                if($("#txtAddress").val()!="")
                {
                                count++;
                                strInnerQuery[count]="<Contains><FieldRef Name='Address' /><Value Type='Note'>"+$("#txtAddress").val()+"</Value></Contains>";
                }

                sQuery+="<View><Query><Where>";
                if(count==1)
                {
                                sQuery+=strInnerQuery[1];
                }
               
                if(count==2)
                {
                                sQuery+="<And>"+strInnerQuery[1]+strInnerQuery[2]+"</And>";
                }
               
                if(count==3)
                {
                                sQuery+="<And><And>"+strInnerQuery[1]+strInnerQuery[2]+"</And>"+strInnerQuery[3]+"</And>";
                }
               
                if(count==4)
                {
                                sQuery+="<And><And><And>"+strInnerQuery[1]+strInnerQuery[2]+"</And>"+strInnerQuery[3]+"</And>"+strInnerQuery[4]+"</And>";
                }
               
                if(count==5)
                {
                                sQuery+="<And><And><And><And>"+strInnerQuery[1]+strInnerQuery[2]+"</And>"+strInnerQuery[3]+"</And>"+strInnerQuery[4]+"</And>"+strInnerQuery[5]+"</And>";
                }
               
                if(count==6)
                {
                                sQuery+="<And><And><And><And><And>"+strInnerQuery[1]+strInnerQuery[2]+"</And>"+strInnerQuery[3]+"</And>"+strInnerQuery[4]+"</And>"+strInnerQuery[5]+"</And>"+strInnerQuery[6]+"</And>";
                }
               
                sQuery+="</Where><OrderBy><FieldRef Name='Modified' /></OrderBy></Query></View>";
               
                var context = new SP.ClientContext.get_current();
                var web = context.get_web();
                var list = web.get_lists().getByTitle('Company Data');
                var camlQuery = new SP.CamlQuery();
                camlQuery.set_viewXml(sQuery);
                this.myColl = list.getItems(camlQuery);
                context.load(this.myColl, 'Include(Title, EmpID, Category, Position, Salary, Address, CurrencyType)');
                context.executeQueryAsync(Function.createDelegate(this, this.BindDynamicQuerySuccess), Function.createDelegate(this, this.BindDynamicQueryFailed));
}

function BindDynamicQuerySuccess()
{
                var strCompanyData="";
                strCompanyData+="<Table width='100%'>";
                if(myColl.get_count()!=0)
                {
                               
                                var listEnumerator = this.myColl.getEnumerator();
                                strCompanyData+="<Tr>";
                                strCompanyData+="<Td class='HeadingClass'>";
                                strCompanyData+="Employee Name";
                                strCompanyData+="</Td>";
                                strCompanyData+="<Td class='HeadingClass'>";
                                strCompanyData+="Employee ID";
                                strCompanyData+="</Td>";
                                strCompanyData+="<Td class='HeadingClass'>";
                                strCompanyData+="Catgeory";
                                strCompanyData+="</Td>";
                                strCompanyData+="<Td class='HeadingClass'>";
                                strCompanyData+="Position";
                                strCompanyData+="</Td>";
                                strCompanyData+="<Td class='HeadingClass'>";
                                strCompanyData+="Salary";
                                strCompanyData+="</Td>";
                                strCompanyData+="<Td class='HeadingClass'>";
                                strCompanyData+="Address";
                                strCompanyData+="</Td>";
                                strCompanyData+="</Tr>";
                                while (listEnumerator.moveNext())
                                {
                                                var item = listEnumerator.get_current();
                                                strCompanyData+="<Tr>";
                                                strCompanyData+="<Td class='LoopRecordsClass'>";
                                                strCompanyData+=item.get_item('Title');
                                                strCompanyData+="</Td>";
                                                strCompanyData+="<Td class='LoopRecordsClass'>";
                                                strCompanyData+=item.get_item('EmpID');
                                                strCompanyData+="</Td>";
                                                strCompanyData+="<Td class='LoopRecordsClass'>";
                                                strCompanyData+=item.get_item('Category').get_lookupValue();
                                                strCompanyData+="</Td>";
                                                strCompanyData+="<Td class='LoopRecordsClass'>";
                                                strCompanyData+=item.get_item('Position').get_lookupValue();
                                                strCompanyData+="</Td>";
                                                strCompanyData+="<Td class='LoopRecordsClass'>";
                                                strCompanyData+=item.get_item('CurrencyType');
                                                strCompanyData+=item.get_item('Salary');
                                                strCompanyData+=".00";
                                                strCompanyData+="</Td>";
                                                strCompanyData+="<Td class='LoopRecordsClass'>";
                                                strCompanyData+=item.get_item('Address')
                                                strCompanyData+="</Td>";
                                                strCompanyData+="</Tr>";

                                }
                               
                               
                               
                               
                }
                else
                {
                                alert('No record found with the selected criteria');
                               
                }
               
                strCompanyData+="</Table>";
                $("#tdDynamicQuery").html(strCompanyData);
}

function BindDynamicQueryFailed(sender, args)
{
                alert('failed. Message:' + args.get_message());
}

function BindEmployeeID()
{
                var context = new SP.ClientContext.get_current();
    var web = context.get_web();
                var list = web.get_lists().getByTitle('Company Data');
                var sQuery="<View><Query><OrderBy><FieldRef Name='ID' /></OrderBy></Query></View>";
                var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml(sQuery);
    this.EmployeeIDColl = list.getItems(camlQuery);
    context.load(this.EmployeeIDColl, 'Include(EmpID, ID)');
    context.executeQueryAsync(Function.createDelegate(this, this.BindEmployeeIDSuccess), Function.createDelegate(this, this.BindEmployeeIDfailed));
}

function BindEmployeeIDSuccess()
{
                var listEnumerator = this.EmployeeIDColl.getEnumerator();
                var strEmployeeID="";
                strEmployeeID+="<Option>";
                strEmployeeID+="Select Employee ID";
                strEmployeeID+="</Option>";
                while (listEnumerator.moveNext())
                {
                    var item = listEnumerator.get_current();
                    strEmployeeID+="<Option value='"+item.get_item('ID')+"'>";
                    strEmployeeID+=item.get_item('EmpID');
                    strEmployeeID+="</Option>";               
    }
   
    $("#ddlEmployeeID").html(strEmployeeID);

}

function BindEmployeeIDfailed(sender, args)
{
                alert('failed. Message:' + args.get_message());
}

function BindCategory()
{
                var context = new SP.ClientContext.get_current();
    var web = context.get_web();
                var list = web.get_lists().getByTitle('Category');
                var sQuery="<View><Query><OrderBy><FieldRef Name='ID' /></OrderBy></Query></View>";
                var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml(sQuery);
    this.CategoryColl = list.getItems(camlQuery);
    context.load(this.CategoryColl, 'Include(Title, ID)');
    context.executeQueryAsync(Function.createDelegate(this, this.BindCategorySuccess), Function.createDelegate(this, this.BindCategoryfailed));
}

function BindCategorySuccess()
{
                var listEnumerator = this.CategoryColl.getEnumerator();
                var strCategory="";
                strCategory+="<Option>";
                strCategory+="Select Category";
                strCategory+="</Option>";
                while (listEnumerator.moveNext())
                {
                    var item = listEnumerator.get_current();
                    strCategory+="<Option value='"+item.get_item('ID')+"'>";
                    strCategory+=item.get_item('Title');
                    strCategory+="</Option>";               
    }
                $("#ddlCategory").html(strCategory);
}

function BindCategoryfailed(sender, args)
{
                alert('failed. Message:' + args.get_message());
}

function BindPosition()
{
                var context = new SP.ClientContext.get_current();
    var web = context.get_web();
                var list = web.get_lists().getByTitle('Position');
                var sQuery="<View><Query><OrderBy><FieldRef Name='ID' /></OrderBy></Query></View>";
                var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml(sQuery);
    this.PositionColl= list.getItems(camlQuery);
    context.load(this.PositionColl, 'Include(Title, ID)');
    context.executeQueryAsync(Function.createDelegate(this, this.BindPositionSuccess), Function.createDelegate(this, this.BindPositionfailed));
}

function BindPositionSuccess()
{
                var listEnumerator = this.PositionColl.getEnumerator();
                var strPosition="";
                strPosition+="<Option>";
                strPosition+="Select Position";
                strPosition+="</Option>";
                while (listEnumerator.moveNext())
                {
                    var item = listEnumerator.get_current();
                    strPosition+="<Option value='"+item.get_item('ID')+"'>";
                    strPosition+=item.get_item('Title');
                    strPosition+="</Option>";               
    }
                $("#ddlPosition").html(strPosition);
}

function BindPositionfailed(sender, args)
{
                alert('failed. Message:' + args.get_message());
}

function LoadDropDowns()
{
                BindEmployeeID();
                BindCategory();
                BindPosition();
}

ExecuteOrDelayUntilScriptLoaded(LoadDropDowns, "sp.js");
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)