public CmsLocalFileOnDiskMetaItem[] FetchAllForFile(CmsLocalFileOnDisk resource)
{
string sql = "SELECT AutoIncId, ResourceId, ResourceRevisionNumber, `Name`, `Value` from resourceitemmetadata ";
sql += " WHERE " + DBDialect.isNull("Deleted") + " AND ResourceId = " + resource.ResourceId + " AND ResourceRevisionNumber = " + resource.RevisionNumber + " ; ";
List <CmsLocalFileOnDiskMetaItem> arrayList = new List <CmsLocalFileOnDiskMetaItem>();
DataSet ds = this.RunSelectQuery(sql);
if (this.hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
CmsLocalFileOnDiskMetaItem item = new CmsLocalFileOnDiskMetaItem();
item.autoincid = Convert.ToInt32(dr["AutoIncId"]);
item.resourceid = Convert.ToInt32(dr["ResourceId"]);
item.resourcerevisionnumber = Convert.ToInt32(dr["ResourceRevisionNumber"]);
item.name = (dr["Name"]).ToString();
item.itemvalue = (dr["Value"]).ToString();
arrayList.Add(item);
} // foreach row
} // if there is data
return(arrayList.ToArray());
} // getAll
private string getStandardUserSQL(string whereClause, WebPortalUser.SortUsersBy sortBy, PortalApplication portalApp)
{
if (whereClause.Trim() != "")
{
whereClause = " AND " + whereClause;
}
string permissionsWhere = "";
string appName = portalApp.GetApplicationName();
if (appName != "" && portalApp.GetAllPermissionsForApplication().Length > 0)
{
permissionsWhere = " AND (" + DBDialect.isNull("p.PermissionsId") + " or p.ApplicationName = '" + dbEncode(appName) + "' ) ";
}
string sql = @"
select appuser.appuserid, appuser.username, appuser.password, appuser.FullName, appuser.EmailAddress, appuser.LastLoginDateTime,
roles.Name as RoleName, roles.roleid, roles.Description as RoleDesc,
x.`key` as exKey, x.`value` as exVal,
p.PermissionsId, p.ApplicationName, p.Action, p.Description
from appuser
LEFT JOIN appuserroles on (appuser.appuserid = appuserroles.appuserid)
LEFT JOIN roles ON (appuserroles.roleid = roles.roleid)
LEFT JOIN appuserextendedinfo x on (x.userId = appuser.AppUserId)
LEFT JOIN appuserpermissions up on (appuser.AppUserId = up.AppUserId)
LEFT JOIN permissions p ON (p.PermissionsId = up.PermissionsId)
WHERE " + DBDialect.isNull("appuser.deleted") + " AND " + DBDialect.isNull("roles.deleted") + " and " + DBDialect.isNull("x.deleted") + " and " + DBDialect.isNull("p.Deleted") + " ";
sql = sql + permissionsWhere;
sql = sql + whereClause;
switch (sortBy)
{
case WebPortalUser.SortUsersBy.UserId:
sql = sql + @" ORDER BY appuserid ";
break;
case WebPortalUser.SortUsersBy.UserName:
sql = sql + @" ORDER BY username ";
break;
case WebPortalUser.SortUsersBy.UserFullName:
sql = sql + @" ORDER BY FullName ";
break;
default:
throw new ArgumentException("Invalid SortBy given");
}
return(sql);
} // getStandardUserSQL
} // getAllUserRoles
/// <summary>
/// returns null if role name not found
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public WebPortalUserRole FetchUserRole(string name)
{
string sql = "select * from roles where " + DBDialect.isNull("Deleted") + " and name like '" + dbEncode(name) + "' ";
DataSet ds = this.RunSelectQuery(sql);
if (hasSingleRow(ds))
{
DataRow dr = ds.Tables[0].Rows[0];
return(roleFromRow(dr));
}
return(null);
} // getUserRole
public string getExtendedInfo(WebPortalUser user, string key, string notFoundValue)
{
if (user.uid > -1)
{
key = key.ToLower();
string sql = "SELECT value from appuserextendedinfo a WHERE ";
sql = sql + " a.userid = " + user.uid.ToString();
sql = sql + " AND a.key = '" + key + "' ";
sql = sql + " AND " + DBDialect.isNull("a.Deleted") + " ";
DataSet ds = this.RunSelectQuery(sql);
if (ds.Tables[0] != null & ds.Tables[0].Rows.Count == 1)
{
return(ds.Tables[0].Rows[0]["value"].ToString());
}
}
return(notFoundValue);
}
} // removeExtendedInfo
private int ExtendedInfoExists(WebPortalUser user, string key)
{
if (user.uid > -1)
{
key = key.ToLower();
string sql = "SELECT ExtendedInfoId from appuserextendedinfo a where ";
sql = sql + " a.userid = " + user.uid.ToString();
sql = sql + " AND a.key = '" + dbEncode(key) + "' ";
sql = sql + " AND " + DBDialect.isNull("a.Deleted") + " ";
DataSet ds = this.RunSelectQuery(sql);
if (ds.Tables[0] != null & ds.Tables[0].Rows.Count == 1)
{
return(Convert.ToInt32(ds.Tables[0].Rows[0]["ExtendedInfoId"].ToString()));
}
}
return(-1);
}
public WebPortalUserRole[] FetchAllUserRoles()
{
List <WebPortalUserRole> ret = new List <WebPortalUserRole>();
string sql = "select * from roles where " + DBDialect.isNull("Deleted") + " ";
sql += " ORDER BY Name ";
DataSet ds = this.RunSelectQuery(sql);
if (hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
ret.Add(roleFromRow(dr));
} // foreach
}
return(ret.ToArray());
} // getAllUserRoles
} // FetchAll
public string[] FetchAllApplicationNames()
{
string sql = "select distinct ApplicationName from permissions ";
sql += " WHERE " + DBDialect.isNull("Deleted") + " ";
sql += " order by ApplicationName, Action ";
List <string> ret = new List <string>();
DataSet ds = this.RunSelectQuery(sql);
if (this.hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
ret.Add(dr["ApplicationName"].ToString());
} // foreach row
} // if there is data
return(ret.ToArray());
} // FetchAllApplicationNames
} // getCategory
public ContactDataCategory[] getAllContactCategories()
{
List <ContactDataCategory> tempList = new List <ContactDataCategory>();
string sql = "select * from contactdatacategory where " + DBDialect.isNull("Deleted") + " ";
// sql += " ORDER BY title ";
DataSet ds = this.RunSelectQuery(sql);
if (this.hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
tempList.Add(fillFromDataRow(dr));
} // foreach
}
// fill in return value
return(tempList.ToArray());
} // getAllEventCategories
} // getAllEventCategories
public ContactDataCategory[] getContactCategories(int[] contactIds)
{
List <ContactDataCategory> tempList = new List <ContactDataCategory>();
string sql = "select * from contactdatacategory where " + DBDialect.isNull("Deleted") + " ";
sql += " AND categoryId in (" + StringUtils.Join(",", contactIds) + ") ";
DataSet ds = this.RunSelectQuery(sql);
if (this.hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
tempList.Add(fillFromDataRow(dr));
} // foreach
}
// fill in return value
return(tempList.ToArray());
} // getAllEventCategories
} // FetchAll
public PortalApplicationPermission[] FetchAll()
{
string sql = "SELECT PermissionsId, ApplicationName, Action, Description from permissions ";
sql += " WHERE " + DBDialect.isNull("Deleted") + " ";
sql += " order by ApplicationName, Action ";
List <PortalApplicationPermission> arrayList = new List <PortalApplicationPermission>();
DataSet ds = this.RunSelectQuery(sql);
if (this.hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
arrayList.Add(GetFromRow(dr));
} // foreach row
} // if there is data
return(arrayList.ToArray());
} // FetchAll
public NameValueCollection getAllExtendedInfo(WebPortalUser user)
{
NameValueCollection ret = new NameValueCollection();
string sql = "select a.key, a.value from appuserextendedinfo a WHERE ";
sql += " a.userid = " + user.uid.ToString();
sql += " AND " + DBDialect.isNull("a.Deleted") + " ";
DataSet ds = this.RunSelectQuery(sql);
if (ds.Tables[0] != null & ds.Tables[0].Rows.Count >= 1)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
ret.Add((dr["key"].ToString()), (dr["value"].ToString()));
} // foreach row
}
return(ret);
}
} // getAllUserRoles
public WebPortalUserRole[] FetchAllUserRoles(int[] matchingRoleIds)
{
if (matchingRoleIds.Length == 0)
{
return(new WebPortalUserRole[0]);
}
List <WebPortalUserRole> ret = new List <WebPortalUserRole>();
string sql = "select * from roles where " + DBDialect.isNull("Deleted") + " ";
sql += " AND roleId in (" + StringUtils.Join(",", matchingRoleIds) + ") ";
sql += " ORDER BY Name ";
DataSet ds = this.RunSelectQuery(sql);
if (hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
ret.Add(roleFromRow(dr));
} // foreach
}
return(ret.ToArray());
} // getAllUserRoles
public EnableForeignKey(DBDialect.ForeignKeyScope scope)
{
_Scope = scope;
}
} // getContact
public ContactData[] getContacts(ContactPlaceholderData data, int[] categoryIds, string[] orgNamesToDisplay)
{
string sql = "SELECT c.ContactId, c.firstName, c.lastName, c.title, c.organizationName, c.address1, c.address2, c.city, c.provinceState, c.postalZipCode, c.phoneNumber1, c.phoneNumber2, c.faxNumber, c.mobileNumber, c.emailAddress, l.CategoryId ";
sql += " from contactdata c left join ContactLinkToCategory l on (c.ContactId = l.ContactId) ";
sql += " WHERE " + DBDialect.isNull("c.Deleted") + " ";
if (categoryIds.Length > 0)
{
sql += " AND l.CategoryId in (" + StringUtils.Join(",", categoryIds) + ") ";
}
if (orgNamesToDisplay.Length > 0)
{
foreach (string org in orgNamesToDisplay)
{
sql += " AND c.organizationName like '" + dbEncode(org) + "' ";
}
}
switch (data.nameDisplayMode)
{
case ContactPlaceholderData.ContactNameDisplayMode.FirstnameLastname:
sql += " ORDER BY c.firstName ";
break;
case ContactPlaceholderData.ContactNameDisplayMode.LastnameFirstname:
sql += " ORDER BY c.lastName ";
break;
default:
throw new ArgumentException("invalid ContactNameDisplayMode");
}// switch
Dictionary <int, ContactData> list = new Dictionary <int, ContactData>();
DataSet ds = this.RunSelectQuery(sql);
if (this.hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
ContactData item = new ContactData();
item.contactId = Convert.ToInt32(dr["ContactId"]);
if (list.ContainsKey(item.contactId))
{
item = list[item.contactId];
}
else
{
item.firstName = (dr["firstName"]).ToString();
item.lastName = (dr["lastName"]).ToString();
item.title = (dr["title"]).ToString();
item.organizationName = (dr["organizationName"]).ToString();
item.address1 = (dr["address1"]).ToString();
item.address2 = (dr["address2"]).ToString();
item.city = (dr["city"]).ToString();
item.provinceState = (dr["provinceState"]).ToString();
item.postalZipCode = (dr["postalZipCode"]).ToString();
item.phoneNumber1 = (dr["phoneNumber1"]).ToString();
item.phoneNumber2 = (dr["phoneNumber2"]).ToString();
item.faxNumber = (dr["faxNumber"]).ToString();
item.mobileNumber = (dr["mobileNumber"]).ToString();
item.emailAddress = (dr["emailAddress"]).ToString();
}
int CategoryId = getPossiblyNullValue(dr, "CategoryId", Int32.MinValue);
if (CategoryId >= 0)
{
item.contactCategoryIds.Add(CategoryId);
}
if (!list.ContainsKey(item.contactId))
{
list.Add(item.contactId, item);
}
} // foreach row
} // if there is data
List <ContactData> ret = new List <ContactData>(list.Values);
return(ret.ToArray());
} // getContacts
public ContactData getContact(int contactId)
{
string sql = "SELECT c.ContactId, c.firstName, c.lastName, c.title, c.organizationName, c.address1, c.address2, c.city, c.provinceState, c.postalZipCode, c.phoneNumber1, c.phoneNumber2, c.faxNumber, c.mobileNumber, c.emailAddress, l.CategoryId ";
sql += " from contactdata c left join ContactLinkToCategory l on (c.ContactId = l.ContactId) ";
sql += " WHERE " + DBDialect.isNull("c.Deleted") + " ";
sql += " AND C.ContactId = " + contactId + "; ";
Dictionary <int, ContactData> list = new Dictionary <int, ContactData>();
DataSet ds = this.RunSelectQuery(sql);
if (this.hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
ContactData item = new ContactData();
item.contactId = Convert.ToInt32(dr["ContactId"]);
if (list.ContainsKey(item.contactId))
{
item = list[item.contactId];
}
else
{
item.firstName = (dr["firstName"]).ToString();
item.lastName = (dr["lastName"]).ToString();
item.title = (dr["title"]).ToString();
item.organizationName = (dr["organizationName"]).ToString();
item.address1 = (dr["address1"]).ToString();
item.address2 = (dr["address2"]).ToString();
item.city = (dr["city"]).ToString();
item.provinceState = (dr["provinceState"]).ToString();
item.postalZipCode = (dr["postalZipCode"]).ToString();
item.phoneNumber1 = (dr["phoneNumber1"]).ToString();
item.phoneNumber2 = (dr["phoneNumber2"]).ToString();
item.faxNumber = (dr["faxNumber"]).ToString();
item.mobileNumber = (dr["mobileNumber"]).ToString();
item.emailAddress = (dr["emailAddress"]).ToString();
}
int CategoryId = getPossiblyNullValue(dr, "CategoryId", Int32.MinValue);
if (CategoryId >= 0)
{
item.contactCategoryIds.Add(CategoryId);
}
if (!list.ContainsKey(item.contactId))
{
list.Add(item.contactId, item);
}
} // foreach row
} // if there is data
List <ContactData> ret = new List <ContactData>(list.Values);
if (ret.Count == 1)
{
ContactData[] arr = ret.ToArray();
return(arr[0]);
}
return(new ContactData());
} // getContact
/// <summary>
/// checks to see if a User's username is active and is already in the system
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
private int UserNameExists(string userName)
{
string sql = "SELECT AppUserId from appuser where UserName like '" + dbEncode(userName.Trim()) + "' and " + DBDialect.isNull("Deleted") + " ";
DataSet ds = this.RunSelectQuery(sql);
if (hasSingleRow(ds))
{
return(Convert.ToInt32(ds.Tables[0].Rows[0]["AppUserId"]));
}
return(-1);
} // userExists
} // userExists
/// <summary>
/// checks to see if a role exists in the system. Returns the Role's RoleID if successful, -1 if not.
/// </summary>
/// <param name="role"></param>
/// <returns>the Role's RoleID if successful, -1 if not</returns>
private int RoleNameExists(string roleName)
{
if (roleName != "")
{
roleName = roleName.Trim();
string sql = "SELECT r.roleId from roles r where r.name like '" + dbEncode(roleName) + "' AND " + DBDialect.isNull("r.Deleted") + " ";
DataSet ds = this.RunSelectQuery(sql);
if (hasSingleRow(ds))
{
return(Convert.ToInt32(ds.Tables[0].Rows[0]["roleId"]));
}
}
return(-1);
} // userExists
