public List <TreeGridItem> GetImportDefinitions(string sortering, string searchString)
{
List <TreeGridItem> returnList = new List <TreeGridItem>();
BaseCollection <NewsletterImportDefinition> definitions;
if (searchString != "")
{
searchString = "SELECT importdefinition.* FROM `importdefinition` WHERE CONCAT (importdefinition.FileName, importdefinition.FileExtension, importdefinition.NameColumn, importdefinition.NamePrefixColumn, importdefinition.ForeNameColumn, importdefinition.EmailColumn, importdefinition.Delimiter ) like'%" + searchString + "%' FK_Site='" + SessionObject.CurrentSite.ID.ToString() + "'";
definitions = BaseCollection <NewsletterImportDefinition> .LoadFromSql(searchString);
}
else
{
definitions = BaseCollection <NewsletterImportDefinition> .Get("FK_Site='" + SessionObject.CurrentSite.ID.ToString() + "'" + searchString, sortering);
}
foreach (NewsletterImportDefinition definition in definitions)
{
TreeGridItem def = new TreeGridItem();
def.ID = definition.ID;
def.Name = definition.Name;
def.Field1 = definition.FileExtension;
def.Field2 = definition.Delimiter;
def.CreateDate = definition.CreateDate;
returnList.Add(def);
}
return(returnList);
}
internal string CalculateNewRootOrderingNumber()
{
string sql = String.Format(@"SELECT ID, Name, 'Item' AS Title, OrderingNumber From DataItem WHERE FK_DataCollection = '{0}' AND FK_Parent_Group IS NULL
UNION SELECT ID, Name, 'Group' AS Title, OrderingNumber From DataGroup WHERE FK_DataCollection = '{0}' AND FK_Parent_Group IS NULL", this.ID);
BaseCollection <BaseDataObject> dataObjects = BaseCollection <BaseDataObject> .LoadFromSql(sql);
int newOrderNumber = dataObjects.Count + 1;
string newOrderingNumberString = newOrderNumber.ToString("0000");
return(newOrderingNumberString);
}
private string FillLookUpDropDown(Tag tag)
{
string options = "";
string fieldName = tag.Name.Replace("{DropDown", "").Replace("}", "");
string sql = "SELECT datalookupvalue.* FROM `datalookupvalue` JOIN datafield ON datalookupvalue.FK_DataField = datafield.ID WHERE datafield.Name = '" + fieldName + "' AND datafield.FK_DataCollection = '" + this.DataCollection.ID.ToString() + "'";
BaseCollection <DataLookupValue> lookUpValues = BaseCollection <DataLookupValue> .LoadFromSql(sql);
foreach (DataLookupValue lookUpValue in lookUpValues)
{
options += "<option value=\"" + lookUpValue.ID + "\">" + lookUpValue.Name + "</option>";
}
return(tag.ReplaceValue.Replace("{options}", options));
}
private string FillCheckboxList(Tag tag)
{
string checkboxes = "";
string fieldName = tag.Name.Replace("{CheckboxList", "").Replace("}", "");
string sql = "SELECT datalookupvalue.* FROM `datalookupvalue` JOIN datafield ON datalookupvalue.FK_DataField = datafield.ID WHERE datafield.Name = '" + fieldName + "' AND datafield.FK_DataCollection = '" + this.DataCollection.ID.ToString() + "'";
BaseCollection <DataLookupValue> lookUpValues = BaseCollection <DataLookupValue> .LoadFromSql(sql);
foreach (DataLookupValue lookUpValue in lookUpValues)
{
checkboxes += tag.ReplaceValue + "<br />";
checkboxes = checkboxes.Replace("{ElementID}", lookUpValue.ID.ToString("N")).Replace("{ElementValue}", lookUpValue.Name);
}
return(checkboxes);
}
public List <TreeGridItem> GetSubscribers(string sortering, string searchString)
{
List <TreeGridItem> returnList = new List <TreeGridItem>();
BaseCollection <NewsletterSubscriber> subscribers;
if (searchString != "")
{
searchString = "SELECT newslettersubscriber.* FROM `newslettersubscriber` WHERE CONCAT (newslettersubscriber.Email, newslettersubscriber.ForeName, newslettersubscriber.NamePrefix, newslettersubscriber.Name, newslettersubscriber.CompanyName) like'%" + searchString + "%'";
subscribers = BaseCollection <NewsletterSubscriber> .LoadFromSql(searchString);
}
else
{
subscribers = BaseCollection <NewsletterSubscriber> .Get("FK_Site='" + SessionObject.CurrentSite.ID.ToString() + "' AND UnsubscribeDate IS NULL" + searchString, sortering);
}
foreach (NewsletterSubscriber subscriber in subscribers)
{
TreeGridItem item = new TreeGridItem();
item.ID = subscriber.ID;
item.Field1 = subscriber.Email;
item.Field3 = subscriber.CompleteName;
item.CreateDate = subscriber.CreateDate;
item.Status = (subscriber.Confirmed) ? "<input type=\"checkbox\" checked=\"checked\" disabled=\"disabled\" />" : "<input type=\"checkbox\" disabled=\"disabled\" />";
foreach (NewsletterGroup group in subscriber.SubscribedGroups)
{
item.Field2 += group.Name + ", ";
}
if (item.Field2 != "" && item.Field2 != null)
{
item.Field2 = item.Field2.Substring(0, item.Field2.Length - 2);
}
/* if (subscriber.User != null)
* {
* item.Name = subscriber.User.CompleteName;
* } */
returnList.Add(item);
}
return(returnList);
}
private static void buildGroup(Guid datacollectionID, BaseDataObject group)
{
DataCollection collection = BaseObject.GetById <DataCollection>(datacollectionID);
if (treeHtml == "")
{
//treeHtml += "<a id='' href='javascript:BITBACKEND.loadDataCollections();'>terug</a>";
treeHtml += String.Format("<ul><li id='{0}'><a id='{0}' class='bitTreeGroupIcon' data-groupid=''>{1}</a><ul id='treeRoot'>", collection.ID, collection.Name.ToUpper());
}
string displayString = "";
if (group != null)
{
displayString = group.Name;
}
//zowel groepen als items krijgen volgorde in de tree
//daarom union query waarbij groepen en item in 1 query staan.
//veld title misbruiken om hier type (groep of item) in te bewaren
string sql = "";
/* if (group == null)
* {
* sql = String.Format(@"SELECT ID, Name, 'Item' AS Title, OrderingNumber, DateFrom, DateTill From DataItem WHERE FK_DataCollection='{0}' AND FK_Parent_Group IS NULL
* UNION SELECT ID, Name, 'Group' AS Title, OrderingNumber, DateFrom, DateTill From DataGroup WHERE FK_DataCollection='{0}' AND FK_Parent_Group IS NULL ORDER BY OrderingNumber, Name", datacollectionID);
* }
* else
* {
* sql = String.Format(@"SELECT ID, Name, 'Item' AS Title, OrderingNumber, DateFrom, DateTill From DataItem WHERE FK_DataCollection='{0}' AND FK_Parent_Group = '{1}'
* UNION SELECT ID, Name, 'Group' AS Title, OrderingNumber, DateFrom, DateTill From DataGroup WHERE FK_DataCollection='{0}' AND FK_Parent_Group = '{1}' ORDER BY OrderingNumber, Name", datacollectionID, group.ID);
*
* } */
if (group == null)
{
sql = String.Format(@"SELECT ID, Name, Active, 'Item' AS Title, OrderNumber, DateFrom, DateTill From DataItem WHERE FK_DataCollection='{0}' AND FK_Parent_Group IS NULL
UNION SELECT ID, Name, Active, 'Group' AS Title, OrderNumber, DateFrom, DateTill From DataGroup WHERE FK_DataCollection='{0}' AND FK_Parent_Group IS NULL ORDER BY OrderNumber, Name", datacollectionID);
}
else
{
sql = String.Format(@"SELECT ID, Name, Active, 'Item' AS Title, OrderNumber, DateFrom, DateTill From DataItem WHERE FK_DataCollection='{0}' AND FK_Parent_Group = '{1}'
UNION SELECT ID, Name, Active, 'Group' AS Title, OrderNumber, DateFrom, DateTill From DataGroup WHERE FK_DataCollection='{0}' AND FK_Parent_Group = '{1}' ORDER BY OrderNumber, Name", datacollectionID, group.ID);
}
BaseCollection <BaseDataObject> dataObjects = BaseCollection <BaseDataObject> .LoadFromSql(sql);
foreach (BaseDataObject dataObject in dataObjects)
{
string inactiveClass = "";
if (!dataObject.IsActive)
{
inactiveClass = "inactive";
}
if (dataObject.Title == "Item")
{
//treeHtml += String.Format("<li class='jstree-drag' id='{0}'><a class='bitTreeItemIcon {2}' href='#' data-groupid='{3}'>{1}</a></li>", dataObject.ID, dataObject.Name, inactiveClass, (group != null) ? group.ID.ToString() : "");
}
else
{
if (dataObject.IsLeaf())
{
treeHtml += String.Format("<li class='jstree-drop jstree-drag' id='{0}'><a class='bitTreeGroupIcon {2}' href='#' data-groupid='{0}'>{1}</a></li>", dataObject.ID, dataObject.Name, inactiveClass);
}
else
{
treeHtml += String.Format("<li class='jstree-drop jstree-drag' id='{0}'><a class='bitTreeGroupIcon {2}' href='#' data-groupid='{0}'>{1}</a><ul>", dataObject.ID, dataObject.Name, inactiveClass);
buildGroup(datacollectionID, dataObject);
treeHtml += "</ul></li>";
}
}
}
}