public string GetDocuments(string date)
{
try
{
var query =
@"
SELECT DocumentLastPublished,DocumentContent, [Description], case when ISNULL(DocumentMenuJavascript, '') = '' then NodeAliasPath else Replace(Replace(Replace(Replace( Replace(Documentmenujavascript,'window.open(',''),')','') ,'''_blank'';return false;','') ,''',',''),'''','') end as NodeAliasPath ,View_SME_CONTENT_MenuItem_Joined.NodeName,View_SME_CONTENT_MenuItem_Joined.IsSecuredNode ,View_SME_CONTENT_MenuItem_Joined.NodeOwnerFullName,View_SME_CONTENT_MenuItem_Joined.DocumentPageDescription ,View_SME_CONTENT_MenuItem_Joined.DocumentPageKeyWords ,View_SME_CONTENT_MenuItem_Joined.DocumentPageTitle , SearchResultName = CASE View_SME_CONTENT_MenuItem_Joined.DocumentName WHEN '' THEN '/' else DocumentName END
FROM View_SME_CONTENT_MenuItem_Joined
WHERE
SiteName= @SiteName
AND (Published = 1) AND ((DocumentSearchExcluded IS NULL) OR (DocumentSearchExcluded = 0))
AND ClassName in ('CMS.MenuItem','SME.MenuItem') AND (@IgnoreDate = 1 OR DocumentModifiedWhen >= @CreatedWhen)
ORDER BY DocumentLastPublished,DocumentName ";
var parameters = new QueryDataParameters();
parameters.Add("@SiteName", CMS.SiteProvider.SiteContext.CurrentSiteName);
var createdWhen = DateTime.Now;
parameters.Add("@IgnoreDate", !DateTime.TryParse(date, out createdWhen), typeof(bool));
parameters.AddDateTime("@CreatedWhen", createdWhen);
var ds = CMS.DataEngine.ConnectionHelper.ExecuteQuery(query, parameters, CMS.DataEngine.QueryTypeEnum.SQLQuery);
return(ds == null ? String.Empty : ds.GetXml());
}
catch (Exception ex)
{
return(String.Empty);
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Initialize score filter
FormFieldInfo ffi = new FormFieldInfo();
ffi.Name = " HAVING SUM(Value)";
ffi.DataType = FormFieldDataTypeEnum.Integer;
ucScoreFilter.FieldInfo = ffi;
ucScoreFilter.DefaultOperator = ">=";
ucScoreFilter.WhereConditionFormat = "{0} {2} {1}";
// Get modify permission of current user
modifyPermission = ContactHelper.AuthorizedModifyContact(SiteId, false);
// Set where condition
gridElem.WhereCondition = "(ScoreId = @ScoreID) AND (Expiration IS NULL OR Expiration > @CurrentDate) GROUP BY OM_SelectScoreContact.ContactID, ContactFullNameJoined, ContactStatusID"
+ ucScoreFilter.GetWhereCondition();
// Add parameters
QueryDataParameters parameters = new QueryDataParameters();
parameters.AddDateTime("@CurrentDate", DateTime.Now);
parameters.AddId("@CurrentSiteID", SiteId);
parameters.AddId("@ScoreID", QueryHelper.GetInteger("ScoreID", 0));
gridElem.QueryParameters = parameters;
// Register OnExternalDataBound
gridElem.OnExternalDataBound += new OnExternalDataBoundEventHandler(gridElem_OnExternalDataBound);
gridElem.OnBeforeFiltering += new OnBeforeFiltering(gridElem_OnBeforeFiltering);
// Initialize dropdown lists
if (!RequestHelper.IsPostBack())
{
drpAction.Items.Add(new ListItem(GetString("general." + Action.SelectAction), Convert.ToInt32(Action.SelectAction).ToString()));
if ((modifyPermission || ContactGroupHelper.AuthorizedModifyContactGroup(SiteId, false)) && ContactGroupHelper.AuthorizedReadContactGroup(SiteId, false))
{
drpAction.Items.Add(new ListItem(GetString("om.account." + Action.AddToGroup), Convert.ToInt32(Action.AddToGroup).ToString()));
}
if (modifyPermission)
{
drpAction.Items.Add(new ListItem(GetString("om.account." + Action.ChangeStatus), Convert.ToInt32(Action.ChangeStatus).ToString()));
}
drpWhat.Items.Add(new ListItem(GetString("om.contact." + What.Selected), Convert.ToInt32(What.Selected).ToString()));
drpWhat.Items.Add(new ListItem(GetString("om.contact." + What.All), Convert.ToInt32(What.All).ToString()));
}
else
{
if (RequestHelper.CausedPostback(btnOk))
{
// Set delayed reload for unigrid if mass action is performed
gridElem.DelayedReload = true;
}
}
// Register JS scripts
RegisterScripts();
}
/// <summary>
/// Returns query parameters.
/// </summary>
private QueryDataParameters GetQueryParameters()
{
QueryDataParameters parameters = new QueryDataParameters();
// Date parameters
if (OlderThan > 0)
{
// OlderThan parameter
if (OlderThan > SQL_DATE_TIME_LIMIT)
{
OlderThan = SQL_DATE_TIME_LIMIT;
}
DateTime param = DateTime.Now.AddDays(-Math.Abs(OlderThan));
parameters.AddDateTime("@OlderThan", param);
}
else if (HowOld > 0 && HowOld < SQL_DATE_TIME_LIMIT)
{
// HowOld parameter
DateTime from = DateTime.Now.AddDays(-Math.Abs(HowOld));
parameters.AddDateTime("@From", from);
}
return(parameters);
}
public string GetStructuredDocumentsData(string date)
{
try
{
var query =
@"
WITH CTE AS (SELECT DocumentID,
DocumentModifiedWhen,
DocumentLastPublished,
EventDate AS DocumentCreatedWhen,
DocumentPageKeyWords,
DocumentPageDescription,
DocumentPageTitle,
IsSecuredNode,
Published,
NodeAliasPath,
NodeName,
(EventName + ' ' + EventSummary + ' ' + EventDetails + ' ' + EventLocation) AS ContentData,
(EventSummary + ' ' + EventLocation) AS Summary,
ClassName
FROM View_SME_CONTENT_Event_Joined va
UNION
SELECT DocumentID,
DocumentModifiedWhen,
DocumentLastPublished,
EventDate AS DocumentCreatedWhen,
DocumentPageKeyWords,
DocumentPageDescription,
DocumentPageTitle,
IsSecuredNode,
Published,
NodeAliasPath,
NodeName,
(EventName + ' ' + EventSummary + ' ' + EventDetails + ' ' + EventLocation) AS ContentData,
(EventSummary + ' ' + EventLocation) AS Summary,
ClassName
FROM View_UCA_CONTENT_Event_Joined va
UNION
SELECT DocumentID,
DocumentModifiedWhen,
DocumentLastPublished,
DocumentCreatedWhen,
DocumentPageKeyWords,
DocumentPageDescription,
DocumentPageTitle,
IsSecuredNode,
Published,
NodeAliasPath,
NodeName,
(DocumentContent + ' ' + Title + ' ' + Author) AS ContentData,
'' AS Summary,
ClassName
FROM View_SME_Publication_Joined va)
SELECT
STUFF(
(SELECT ', ' + CategoryDisplayName
FROM CMS_Category C
WHERE categoryID IN (SELECT
categoryID
FROM cms_documentcategory
WHERE documentid = CTE.DocumentID)
FOR xml PATH (''))
, 1, 1, '') AS Categoryname,
STUFF(
(SELECT ', ' + convert(nvarchar(4), CategoryID)
FROM CMS_Category C
WHERE categoryID IN (SELECT
categoryID
FROM cms_documentcategory
WHERE documentid = CTE.DocumentID)
FOR xml PATH (''))
, 1, 1, '') AS CategoryIds,
DocumentModifiedWhen,
DocumentLastPublished,
DocumentCreatedWhen,
DocumentPageKeyWords,
DocumentPageDescription,
DocumentPageTitle,
IsSecuredNode,
Published,
NodeAliasPath,
NodeName,
ContentData,
Summary,
ClassName
FROM CTE WHERE Published = 1
AND (@IgnoreDate = 1 OR DocumentModifiedWhen >= @CreatedWhen)
ORDER BY DocumentModifiedWhen desc";
var parameters = new QueryDataParameters();
var createdWhen = DateTime.Now;
parameters.Add("@IgnoreDate", !DateTime.TryParse(date, out createdWhen), typeof(bool));
parameters.AddDateTime("@CreatedWhen", createdWhen);
var ds = CMS.DataEngine.ConnectionHelper.ExecuteQuery(query, parameters, CMS.DataEngine.QueryTypeEnum.SQLQuery);
return(ds == null ? String.Empty : ds.GetXml());
}
catch (Exception ex)
{
return(String.Empty);
}
}
protected void Page_Load(object sender, EventArgs e)
{
gridElem.IsLiveSite = IsLiveSite;
// Get score ID from query string
scoreId = QueryHelper.GetInteger("ScoreID", 0);
// Initialize score filter
FormFieldInfo ffi = new FormFieldInfo();
ffi.Name = " HAVING SUM(Value)";
ffi.DataType = FormFieldDataTypeEnum.Integer;
ucScoreFilter.FieldInfo = ffi;
ucScoreFilter.DefaultOperator = ">=";
ucScoreFilter.WhereConditionFormat = "{0} {2} {1}";
// Get modify permission of current user
modifyPermission = ContactHelper.AuthorizedModifyContact(SiteId, false);
// Set where condition
gridElem.WhereCondition = "(ScoreId = @ScoreID) AND (Expiration IS NULL OR (DATEDIFF(d, getdate(), Expiration) >= 0)) GROUP BY ContactID"
+ ucScoreFilter.GetWhereCondition();
// Add parameters
QueryDataParameters parameters = new QueryDataParameters();
parameters.AddDateTime("@CurrentDate", DateTime.Now);
parameters.AddId("@CurrentSiteID", SiteId);
parameters.AddId("@ScoreID", scoreId);
gridElem.QueryParameters = parameters;
// Register OnExternalDataBound
gridElem.OnExternalDataBound += gridElem_OnExternalDataBound;
gridElem.OnBeforeFiltering += gridElem_OnBeforeFiltering;
// Initialize dropdown lists
if (!RequestHelper.IsPostBack())
{
drpAction.Items.Add(new ListItem(GetString("general." + Action.SelectAction), Convert.ToInt32(Action.SelectAction).ToString()));
if ((modifyPermission || ContactGroupHelper.AuthorizedModifyContactGroup(SiteId, false)) && ContactGroupHelper.AuthorizedReadContactGroup(SiteId, false))
{
drpAction.Items.Add(new ListItem(GetString("om.account." + Action.AddToGroup), Convert.ToInt32(Action.AddToGroup).ToString()));
}
if (modifyPermission)
{
drpAction.Items.Add(new ListItem(GetString("om.account." + Action.ChangeStatus), Convert.ToInt32(Action.ChangeStatus).ToString()));
}
drpWhat.Items.Add(new ListItem(GetString("om.contact." + What.Selected), Convert.ToInt32(What.Selected).ToString()));
drpWhat.Items.Add(new ListItem(GetString("om.contact." + What.All), Convert.ToInt32(What.All).ToString()));
}
else
{
if (RequestHelper.CausedPostback(btnOk))
{
// Set delayed reload for unigrid if mass action is performed
gridElem.DelayedReload = true;
}
}
// Register JS scripts
RegisterScripts();
}
/// <summary>
/// Returns query parameters.
/// </summary>
private QueryDataParameters GetQueryParameters()
{
QueryDataParameters parameters = new QueryDataParameters();
// Date parameters
if (OlderThan > 0)
{
// OlderThan parameter
if (OlderThan > SQL_DATE_TIME_LIMIT)
{
OlderThan = SQL_DATE_TIME_LIMIT;
}
DateTime param = DateTime.Now.AddDays(-Math.Abs(OlderThan));
parameters.AddDateTime("@OlderThan", param);
}
else if (HowOld > 0 && HowOld < SQL_DATE_TIME_LIMIT)
{
// HowOld parameter
DateTime from = DateTime.Now.AddDays(-Math.Abs(HowOld));
parameters.AddDateTime("@From", from);
}
return parameters;
}
