Forums (Obsolete)

Member

eng.rupalikulkarni-gmail - 7/23/2012 2:40:35 PM

searched Records are not displaying

Hi,

I have HTML Head webpart at the page having following code;

<script type="text/javascript">
$(function(){
if($('#dvList').length == 0){
$('#dvFilter').remove();
} else {
$('#dvNoResults').remove();
}
if($('#pSearchParams').find('span').text() == ''){

$('#pSearchParams').remove();
}

});
</script>

SQL Search Dialog webpart, In which following contents are there at HTML after property

<p style="padding: 20px;" id="pSearchParams"><strong>
Results for: ({%searchmode%}) <span>{%searchtext%}</span>
</strong></p>

(Repeater with custom query Injection Disabled) webpart having following properties set to respective values;

Query name:CMS.Root.UsedEquipmentSearch

SELECT * FROM TABLE
FROM View_CUSTOM_EquipmentUsed_Joined) AS TblA
WHERE ##WHERE##
ORDER BY ##ORDERBY##

ORDER BY expression:{#PreOwnedOrderBy|(param){?order?}#}

WHERE condition:{#PreOwnedSearch|(column) {?SearchText?}#}

Transformation name*:AIS.EquipmentUsedInventory.EquipmentViewSearchList

<script runat="server">
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
if (this.Parent is IDataItemContainer)
{
IDataItemContainer cont = this.Parent as IDataItemContainer;
if (cont != null)
{
System.Data.DataRowView drv = (System.Data.DataRowView)cont.DataItem as System.Data.DataRowView;
if (drv != null)
{
//SetMetaTags(drv);
LoadImage(drv);
}
}
}

}

//Retrieve Inventory Item Images
protected void LoadImage(System.Data.DataRowView drv){
string[] files;
string relpath = "~/AISEquip/Used/Media/" + drv["eqStockNumber"];
string path = Server.MapPath(relpath);

//BZS - AH 7-2-1012 - changed the logic to look for file numbers that start with 0
if(System.IO.Directory.Exists(path)){
string pattern = @"^.*(" + drv["eqStockNumber"] + "_File(\\d*)_(Medium|Small)\\.(jpg|gif|png))$";
System.Text.RegularExpressions.Regex re;
re = new System.Text.RegularExpressions.Regex(pattern, System.Text.RegularExpressions.RegexOptions.IgnoreCase);

files = System.IO.Directory.GetFiles(path);

foreach (string f in files)
{
if(re.IsMatch(f))
{
//BZS - AH 7-2-1012 - changed the logic to look for file numbers that start with 0
if((re.Matches(f)[0].Groups[2].ToString() == "001" ||
re.Matches(f)[0].Groups[2].ToString() == "1") && re.Matches(f)[0].Groups[3].ToString().ToUpper() == "SMALL")
{
ltListImage.Text += "<img src="" + ResolveUrl(/AISEquip/Used/Media/" + drv["eqStockNumber"]+ "/" + re.Matches(f)[0].Groups[1].ToString()) + "\" alt=\"" + drv["eqName"].ToString() + "\" title=\"" + drv["eqName"].ToString() + "\" >";
}
}
}
}

if(String.IsNullOrEmpty(ltListImage.Text))
{
ltListImage.Text = GetImageByUrl("~/Images/no-image.aspx", 100, 100, 75, drv["eqName"].ToString());
}
}

//Configure SEO Meta Information
protected void SetMetaTags(System.Data.DataRowView drv){

}
</script>

<a href="/Pre-Owned/inventory/<%# Eval("CategorySEOFriendlyName") %>/<%# Eval("eqManufacturer") %>/<%# Eval("eqSEOFriendlyName") %>/<%# Eval("eqStockNumber") %>.aspx" class="equipmentListLink">
<div class="equipmentList" >
<div class="itemImage" onclick="window.location='/Pre-Owned/inventory/<%# Eval("CategorySEOFriendlyName") %>/<%# Eval("eqManufacturer") %>/<%# Eval("eqSEOFriendlyName") %>/<%# Eval("eqStockNumber") %>.aspx'" >
<asp:Literal ID="ltListImage" runat="server"></asp:Literal>
</div>
<div class="itemMake"><%# Eval("eqName") %></div>
<div class="itemManufacturer">
<%# Eval("ManufacturerName") %><br /><br />
<%# (Convert.ToBoolean(Eval("eqIsKomatsuCertified")) ? GetImageByUrl("/Images/Pages/Details/certification-komatsu.aspx", 80) : String.Empty) %>
<%# (Convert.ToBoolean(Eval("eqIsAISCertified")) ? GetImageByUrl("/Images/Pages/Details/certification-ais.aspx", 80) : String.Empty) %>
</div>
<div class="itemPrice"><%# String.Format("{0:C}", Eval("eqPrice")) %></div>
<div class="itemLocation"><%# Eval("LocationName") %></div>
<div class="itemYear"><%# Eval("eqYear") %></div>
<div class="itemHours"><%# Eval("eqHours") %></div>
</div>
</a>

Paging mode: Postback

The problem is (Repeater with custom query Injection Disabled) webpart not displaying anything , If I remove search condition it is displaying all my records… But when I enter search keword in there it is not displaying anything into results..

Can you please suggest some solution to this problem?

Thanks!


Member	eng.rupalikulkarni-gmail - 7/23/2012 4:44:42 PM RE:searched Records are not displaying This problem started after i upgraded from kentico version 5.5 to version 6

Kentico Support

kentico_jurajo - 7/24/2012 2:23:03 AM

RE:searched Records are not displaying

Hi,

Have you tried using the SQL Debug to see the actual SQL query and how does it look like?

Also, nesting different types of macros was not supported - in your case it worked by accident in previous version. Right now, you need to use data macros, below is an example:

{% GetString(„string“, "default") %}

Or, you can customize your custom macro and include the query string checking inside it.

Best regards,
Juraj Ondrus


Member	eng.rupalikulkarni-gmail - 7/24/2012 10:07:28 AM RE:searched Records are not displaying Hi, Thank you very much for your quick reply... Can you please tell me ,,, Do i need to change complete code of my transformation; AIS.EquipmentUsedInventory.EquipmentViewSearchList or just Where condition , and Order by conditions?

Member

eng.rupalikulkarni-gmail - 7/24/2012 5:19:58 PM

RE:searched Records are not displaying

Hi,

Below is my CMScustom.cs file code in which all macro's definition is there

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using CMS.GlobalHelper;
using CMS.Compatibility;
using CMS.SettingsProvider;
using CMS.DataEngine;
using CMS.SiteProvider;
using CMS.CMSHelper;
using CMS.Scheduler;
using CMS.EventLog;

using System.Collections.Generic;

/// <summary>
/// CMSCustom methods
/// </summary>
public static class CMSCustom
{
/// <summary>
/// Initializes the custom methods
/// </summary>
public static void Init()
{

MacroResolverCompatibility.OnResolveCustomMacro += new MacroResolverCompatibility.MacroHandler(ResolveCustomMacro);
ClassHelperCompatibility.OnGetCustomClass += new ClassHelperCompatibility.GetClassEventHandler(GetCustomClass);

}

/// <summary>
/// Custom macro handler
/// </summary>
/// <param name="sender">Sender (active macro resolver)</param>
/// <param name="expression">Expression to resolve</param>
/// <param name="match">Returns true if the macro matches (was resolved)</param>
public static string ResolveCustomMacro(MacroResolver sender, string expression, out bool match)
{

match = false;
string result = expression;
string[] expr = expression.Split('|');
string param = String.Empty;

//loop through all parameters
for (int i = 0; i < expr.Length; i++)
{
//param=expr;
if (expr.ToUpper().StartsWith("(COLUMN)"))
{
//only looking for column parameter
param = expr.Remove(0, 8).Trim();
}

if (expr.ToUpper().StartsWith("(PARAM)"))
{
param = expr.Remove(0, 7).Trim();
}

}

// Add your custom macro evaluation
switch (expr[0].ToLower())
{
case "preownedsearch":
match = true;

result = ResolveWhereCondition(param);
break;
case "preownedorderbylink":
match = true;
result = ResolveOrderByLink(param);
break;
case "preownedorderby":
match = true;
result = ResolveOrderByParam(param);
break;
case "recentlyviewedwhere":
match = true;
result = ResolveRecentlyViewedWhereCondition(param);
break;
}

return result;
}

private static string ResolveRecentlyViewedWhereCondition(string param)
{
string s = String.Empty;
string qry = CMS.CMSHelper.CMSContext.ResolveMacros(param);
//string qry = param;

if (String.IsNullOrEmpty(qry))
{
s = "eqStockNumber IS NULL";
}
else
{
s = "eqStockNumber IN (" + qry + ")";
}
return s;
}

private static string ResolveOrderByParam(string param)
{
string s = String.Empty;
string qry = CMS.CMSHelper.CMSContext.ResolveMacros(param);

if(!String.IsNullOrEmpty(qry))
{
switch(qry.ToLower())
{
case "make":
s = "eqModel";
break;
case "manufacturer":
s = "ManufacturerName";
break;
case "price":
s = "eqPrice";
break;
case "location":
s = "LocationName";
break;
case "year":
s = "eqYear";
break;
case "hours":
s = "eqHours";
break;
}
}

return s;
}

private static string ResolveOrderByLink(string col)
{
string cUrl = UrlHelper.RawUrl;
string[] p = {"make", "manufacturer", "price", "location", "year", "hours"};
string s = String.Empty;

//remove all possible order by params
cUrl = UrlHelper.RemoveParameterFromUrl(cUrl, "order");

if(!String.IsNullOrEmpty(col))
{
switch(col.ToUpper())
{
case "EQMODEL":
s = cUrl = UrlHelper.AddParameterToUrl(cUrl, "order", p[0]);
break;
case "MANUFACTURERNAME":
s = cUrl = UrlHelper.AddParameterToUrl(cUrl, "order", p[1]);
break;
case "EQPRICE":
s = cUrl = UrlHelper.AddParameterToUrl(cUrl, "order", p[2]);
break;
case "LOCATIONNAME":
s = cUrl = UrlHelper.AddParameterToUrl(cUrl, "order", p[3]);
break;
case "EQYEAR":
s = cUrl = UrlHelper.AddParameterToUrl(cUrl, "order", p[4]);
break;
case "EQHOURS":
s = cUrl = UrlHelper.AddParameterToUrl(cUrl, "order", p[5]);
break;
}
}

return s;
}

private static string ResolveWhereCondition(string col)
{
string text = CMS.CMSHelper.CMSContext.ResolveMacros("{%searchtext%}");
string mode = CMS.CMSHelper.CMSContext.ResolveMacros("{%searchmode%}");
string s = String.Empty;

if (!String.IsNullOrEmpty(col))
{
string[] w = text.Split(' ');
switch (mode)
{
case "anyword":
for (int i = 0; i < w.Length; i++)
{
s += "searchtext" + " LIKE '% " + SQLEncode(w) + " %' OR ";
}
s = s.Remove(s.Length - 4);
break;
case "allwords":
for (int i = 0; i < w.Length; i++)
{
s += "searchtext" + " LIKE ''% " + SQLEncode(w) + " %'' AND ";
}
s = s.Remove(s.Length - 5);
break;
case "exactphrase":
s = "searchtext" + " LIKE ''%" + SQLEncode(text) + "%''";
break;
default:
s = "SearchText = NULL";
break;
}
}
else
{
//default column
s = "SearchText = NULL";
}

return s;
}

private static string SQLEncode(string str)
{
string characters = "'%";

foreach(char s in characters)
{
switch(s)
{
case '\'':
str = str.Replace(s.ToString(), "''");
break;
case '%':
str = str.Replace(s.ToString(), "[" + s.ToString() + "]");
break;
}
}
return str;
}

/// <summary>
/// Gets the custom class object based on the given class name. This handler is called when the assembly name is App_Code
/// </summary>
/// <param name="className">Class name</param>
public static object GetCustomClass(string className)
{
// Provide your custom classes
switch (className)
{
// Define the class MyTask implementing ITask and you can provide your scheduled tasks out of App_Code
case "Custom.MyTask":
return new MyTask();
}

return null;
}

/// <summary>
/// Sample task class
/// </summary>
public class MyTask : ITask
{
/// <summary>
/// Executes the task
/// </summary>
/// <param name="ti">Task info</param>
public string Execute(TaskInfo ti)
{
EventLogProvider ev = new EventLogProvider();
ev.LogEvent("I", DateTime.Now, "MyTask", "Execute", null, "This task was executed from '~/App_Code/Global/CMS/CMSCustom.cs'.");

return null;
}
}
}

Please let me know, do i need to make any changes to get it work??

Thanks!


Kentico Support	kentico_jurajo - 7/25/2012 1:18:29 AM RE:searched Records are not displaying Hi, Why don't you add the query string value directly in your custom macro code to the returned condition string? "<wherecondition>" + GetString("string", "default value").ToString(); Best regards, Juraj Ondrus

Member

eng.rupalikulkarni-gmail - 7/25/2012 9:57:49 AM

RE:searched Records are not displaying

Hi,

I can not do that,As its dynamic..

Can you please tell me is there any specific way in which i should write macros in version 6?

My macro definitions looks correct to you?

My code in where condition is as below;
{#PreOwnedSearch|(column)searchtext#}

And it is resolving it from CMSCustom.cs file... Its code i already posted here..

Thanks!


Kentico Support	kentico_jurajo - 7/26/2012 3:15:34 AM RE:searched Records are not displaying Hi, It does not make sense why you cannot do that? What is dynamic? the value of the query string parameter? That is OK and you can get this dynamic value in the custom macro code as well. Best regards, Juraj Ondrus