Our Aim is to create a feature which can search multiple brand, multiple colors type products.
For this we first need to create form which contain multiple checboxes to filter, even you can also show checkbox of brand available from database. But in this post i am normally going to show you how normally logic can work for this.
So add below code in your view
<form action="#" method="post" class="colorForm" id="colorForm">
<div class="form-group">
////////////Adding a hidden field which contain url to which we can use in ajax, in below url i am parsing pagination,searching parameters and in later ajax i will pass checkbox values///////////////
<input type="hidden" id="urltoquery" name="urltoquery" data-location="/home/AllProductWithRefiner?L=1&Name=@Name&pageno=@pageno&pagenoforpagination=@fixedpagination&rcount=@rcount">
////////////////////////////////Add all checkbox that contain brand or color code values///////////////
/// ////////////////// here i am passing color code id ing checkbox value///////////////////////
Color Refining Form
<input type="hidden" id="urltoquery" name="urltoquery" data-location="/home/AllProductWithRefiner?L=1&Name=@Name&pageno=@pageno&pagenoforpagination=@fixedpagination&rcount=@rcount">
<div class="checkbox">
<label>
<input type="checkbox" name="checkboxname" value="1" > <span class="colour white"> </span> White (14)
</label>
</div>
<div class="checkbox">
<label>
<input type="checkbox" name="checkboxname" value="2" <span class="colour blue"> </span> Blue (10)
</label>
</div>
<div class="checkbox">
<label>
<input type="checkbox" name="checkboxname" value="3" > <span class="colour green"> </span> Green (20)
</label>
</div>
<div class="checkbox">
<label>
<input type="checkbox" name="checkboxname" value="4" > <span class="colour yellow"> </span> Yellow (13)
</label>
</div>
<div class="checkbox">
<label>
<input type="checkbox" name="checkboxname" value="5" > <span class="colour red"> </span> Red (10)
</label>
</div>
</div>
</form>
Add below code in script on page
<script type="text/javascript>
$(document).ready(function () {
//**********************Color Form ******************************************
////////////////Getting Url Where We Have To Post Our Data///////////////////////////
var url = $('#urltoquery').attr('data-location');
/////////////////////Getting Form All Chekbox///////////////////////////
var checkboxes = document.getElementsByName("checkboxname");
////Based on each checkbox storing check box value in value variable as a string seperated by "," if checkbox is checked/////////////////////////////
for (var i = 0; i < checkboxes.length; i++) {
var checkbox = checkboxes[i];
checkbox.onclick = (function (chk) {
return function () {
var value = "";
for (var j = 0; j < checkboxes.length; j++) {
if (checkboxes[j].checked) {
if (value === "") {
value += checkboxes[j].value;
} else {
value += "," + checkboxes[j].value;
}
}
}
If you have noticed i am using URL of same page where i am coding. By this my code becomes smaller little. I am parsing data to same view with l = 1 which makes layout null in top of view so it catch its dom. You can even write long html designing here too after returning.
var colorlocation = url + '&color=' + value;
/////////////// Adding Parameters to URL and parsing URL to ajax actions/////////
$.ajax({
type: "GET",
url: colorlocation,
data: '',
success: function (data) {
$("#loader").hide();
/////////////////////////You can desing your resultant product here//////////////////
$('#mainDivId').html(data); /////On its return i am passing data to dom
},
error: function (req, error) {
if (error === 'error') { error = req.statusText; }
var errormsg = 'There was a communication error: ' + error;
console.log(errormsg);
$("#loader").hide();
},
beforeSend: function (data) {
$("#loader").show();
}
});
}
})(checkbox);
}
</script>
In my case its call goes to a function declaration in same page because url i am passing is of same. You can return data via ajax through JsonResults() too.
System.Data.DataTable dt = ShopAndStudio.Models.ecommerceQueriesModel.Allproductrefiner(rcount, pageno, ref recordcount, Name, color); ////color is collection of our checkbox values separated by ",".
////////////////////////And My function calls is here///////////////////////////
///////It calls a stored procedure of sql which in result return product////////////////
static public DataTable Allproductrefiner(Nullable<Int32> counter, Nullable<Int32> PageNumber, ref Nullable<Int32> recordcount, string Name, string color)
{
SubSonic.Schema.StoredProcedure sp = new StoredProcedure("AllProductWithRefiner");
sp.Command.AddParameter("@count", counter, DbType.Int64);
sp.Command.AddParameter("@PageNo", PageNumber, DbType.Int64);
sp.Command.AddParameter("@color", color, DbType.String);
sp.Command.AddParameter("@Name", Name, DbType.String);
DataSet ds = sp.ExecuteDataSet();
DataTable dt = ds.Tables[0];
recordcount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
return sp.ExecuteDataSet().Tables[0];
}
Now we will move to procedure but our data is useless until we separate each "," from value so for separating "," we need to create a function which can do it better as i did below:
Creating a function that can split "," from string and treat each as separate value.
CREATE FUNCTION dbo.SplitStringIntoInteger(@ColorList varchar(max))
RETURNS
@ParsedList table -- /////////Declaring A Table With int varable
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int --////////Declaring above declared column and postion variable
SET @ColorList = LTRIM(RTRIM(@ColorList))+ ',' --//// After Triming extra spaces adding ","
SET @Pos = CHARINDEX(',', @ColorList, 1) --///////Finding first postion of "," in color value we passed
IF REPLACE(@ColorList, ',', '') <> '' --///// Replacing all "," with empty
BEGIN
WHILE @Pos > 0 ---//////As we find "," in string so our pos value is 1
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@ColorList, @Pos - 1)))
---///splitting "," from string and when splitted inserting to dynamic table
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @ColorList = RIGHT(@ColorList, LEN(@ColorList) - @Pos)
SET @Pos = CHARINDEX(',', @ColorList, 1)
END
END
RETURN --- this will return table
END
Now our final procedure which will get data and return to json or our c# functions.
CREATE procedure AllProductWithRefiner
@productID bigint = 0,
@sku nvarchar(50) ='',
@listOrder int = 0,
@productName nvarchar(150)='',
@defaultImage nvarchar(500) = '',
@isProduct bit =1,
@count bigint = 0,
@PageNo bigint =0,
@Name nvarchar(250) = '' ,
@color nvarchar(500) = '' --////////color list parameter will comer here
As
begin
Set NOCOUNT ON;
if(@color='') --/////Checking if our color value is null or not if null do other operation
begin
----------------------Do Other operations-----------------------
end
else if(@color<>'') ---/////////else if color contain list values then start query operation
begin
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY productID DESC) AS rownum, utc.* From MyProductTable utc where
utc.ProductId IN (Select ProductId from Product_Color_Map Where ColorId IN(Select * from SplitStringIntoInteger(@color)))) As MyProductTable
where (rownum > CAST((5 * 0) AS VARCHAR(15)) AND rownum <= + CAST((5 * (0 + 1)) AS VARCHAR(15)) )
SELECT count(utc.productID) as 'TotalCount' From MyProductTable utc where (utc.isProduct = 1)
----///////////This return my pagination data and based on color id values takes product on that color from product table
end
set NOCOUNT off
End
and i get my result as you can see below:
0 comments:
Post a Comment