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
} // 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
} // 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
} // 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
} // 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
} // 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
/// <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