/projects/PigeonCms.Core/DAL/SectionsManager.cs
C# | 447 lines | 381 code | 46 blank | 20 comment | 38 complexity | 66003519ec76163cbb203f3bdf5aeb48 MD5 | raw file
1using System; 2using System.Data; 3using System.Configuration; 4using System.Web; 5using System.Web.Security; 6using System.Web.UI; 7using System.Web.UI.WebControls; 8using System.Web.UI.WebControls.WebParts; 9using System.Web.UI.HtmlControls; 10using System.ComponentModel; 11using System.Collections.Generic; 12using System.IO; 13using System.Data.Common; 14using PigeonCms; 15using System.Diagnostics; 16 17namespace PigeonCms 18{ 19 /// <summary> 20 /// DAL for categoria obj (in table categorie) 21 /// </summary> 22 public class SectionsManager : TableManager<Section, SectionsFilter, int>, ITableManagerWithPermission 23 { 24 private bool checkUserContext = false; 25 private bool writeMode = false; 26 27 public bool CheckUserContext 28 { 29 get { return checkUserContext; } 30 } 31 32 public bool WriteMode 33 { 34 get { return writeMode; } 35 } 36 37 [DebuggerStepThrough()] 38 public SectionsManager(): this(false, false) 39 { } 40 41 public SectionsManager(bool checkUserContext, bool writeMode) 42 { 43 this.TableName = "#__sections"; 44 this.KeyFieldName = "Id"; 45 this.checkUserContext = checkUserContext; 46 this.writeMode = writeMode; 47 if (this.writeMode) this.checkUserContext = true; //forced 48 } 49 50 public override Dictionary<string, string> GetList() 51 { 52 Dictionary<string, string> res = new Dictionary<string, string>(); 53 SectionsFilter filter = new SectionsFilter(); 54 List<Section> list = GetByFilter(filter, ""); 55 foreach (Section item in list) 56 { 57 res.Add(item.Id.ToString(), item.Title); 58 } 59 return res; 60 } 61 62 public override List<Section> GetByFilter(SectionsFilter filter, string sort) 63 { 64 DbProviderFactory myProv = Database.ProviderFactory; 65 DbConnection myConn = myProv.CreateConnection(); 66 DbDataReader myRd = null; 67 DbCommand myCmd = myConn.CreateCommand(); 68 string sSql; 69 List<Section> result = new List<Section>(); 70 71 try 72 { 73 myConn.ConnectionString = Database.ConnString; 74 myConn.Open(); 75 myCmd.Connection = myConn; 76 77 sSql = "SELECT t.Id, t.Enabled, " 78 + " t.AccessType, t.PermissionId, t.AccessCode, t.AccessLevel, " 79 + " t.WriteAccessType, t.WritePermissionId, t.WriteAccessCode, t.WriteAccessLevel, " 80 + " t.MaxItems, t.MaxAttachSizeKB " 81 + " FROM ["+ this.TableName +"] t " 82 + " WHERE t.Id > 0 "; 83 if (filter.Id > 0 || filter.Id == -1) 84 { 85 sSql += " AND t.Id = @Id "; 86 myCmd.Parameters.Add(Database.Parameter(myProv, "Id", filter.Id)); 87 } 88 if (filter.Enabled != Utility.TristateBool.NotSet) 89 { 90 sSql += " AND t.Enabled = @Enabled "; 91 myCmd.Parameters.Add(Database.Parameter(myProv, "Enabled", filter.Enabled)); 92 } 93 if (!string.IsNullOrEmpty(sort)) 94 { 95 sSql += " ORDER BY " + sort; 96 } 97 98 myCmd.CommandText = Database.ParseSql(sSql); 99 myRd = myCmd.ExecuteReader(); 100 while (myRd.Read()) 101 { 102 var item = new Section(); 103 FillObject(item, myRd); 104 result.Add(item); 105 } 106 myRd.Close(); 107 108 //(other loop to avoid multiple reader on same command) 109 foreach (Section item in result) 110 { 111 //load read roles 112 if (item.ReadPermissionId > 0 && item.ReadAccessType != MenuAccesstype.Public) 113 item.ReadRolenames = new PermissionProvider().GetPermissionRoles(item.ReadPermissionId); 114 //load write roles 115 if (item.WritePermissionId > 0 && item.WriteAccessType != MenuAccesstype.Public) 116 item.WriteRolenames = new PermissionProvider().GetPermissionRoles(item.WritePermissionId); 117 } 118 if (this.CheckUserContext) 119 { 120 result.RemoveAll(new PermissionProvider().IsItemNotAllowed); 121 if (this.WriteMode) 122 result.RemoveAll(new PermissionProvider().IsItemNotAllowedForWrite); 123 } 124 125 //culture specifics 126 foreach (Section item in result) 127 { 128 getCultureSpecific(item, myRd, myCmd, myProv); 129 } 130 } 131 finally 132 { 133 myConn.Dispose(); 134 } 135 return result; 136 } 137 138 public override Section GetByKey(int id) 139 { 140 var result = new Section(); 141 var resultList = new List<Section>(); 142 var filter = new SectionsFilter(); 143 filter.Id = id == 0 ? -1 : id; 144 resultList = GetByFilter(filter, ""); 145 if (resultList.Count > 0) 146 result = resultList[0]; 147 148 return result; 149 } 150 151 public override int Update(Section theObj) 152 { 153 DbProviderFactory myProv = Database.ProviderFactory; 154 DbTransaction myTrans = null; 155 DbConnection myConn = myProv.CreateConnection(); 156 DbCommand myCmd = myConn.CreateCommand(); 157 string sSql; 158 int result = 0; 159 160 try 161 { 162 //fill ReadPermissionId and WritePermissionId before trans 163 new PermissionProvider().UpdatePermissionObj(theObj); 164 165 myConn.ConnectionString = Database.ConnString; 166 myConn.Open(); 167 myCmd.Connection = myConn; 168 169 myTrans = myConn.BeginTransaction(); 170 myCmd.Transaction = myTrans; 171 172 sSql = "UPDATE [" + this.TableName + "] " 173 + " SET Enabled=@Enabled, " 174 + " AccessType=@AccessType, PermissionId=@PermissionId, " 175 + " [AccessCode]=@AccessCode, AccessLevel=@AccessLevel, " 176 + " WriteAccessType=@WriteAccessType, WritePermissionId=@WritePermissionId, " 177 + " [WriteAccessCode]=@WriteAccessCode, WriteAccessLevel=@WriteAccessLevel, " 178 + " [MaxItems]=@MaxItems, MaxAttachSizeKB=@MaxAttachSizeKB " 179 + " WHERE Id = @Id"; 180 myCmd.CommandText = Database.ParseSql(sSql); 181 myCmd.Parameters.Add(Database.Parameter(myProv, "Id", theObj.Id)); 182 myCmd.Parameters.Add(Database.Parameter(myProv, "Enabled", theObj.Enabled)); 183 //read permissions 184 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessType", theObj.ReadAccessType)); 185 myCmd.Parameters.Add(Database.Parameter(myProv, "PermissionId", theObj.ReadPermissionId)); 186 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessCode", theObj.ReadAccessCode)); 187 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessLevel", theObj.ReadAccessLevel)); 188 //write permissions 189 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessType", theObj.WriteAccessType)); 190 myCmd.Parameters.Add(Database.Parameter(myProv, "WritePermissionId", theObj.WritePermissionId)); 191 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessCode", theObj.WriteAccessCode)); 192 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessLevel", theObj.WriteAccessLevel)); 193 //limits 194 myCmd.Parameters.Add(Database.Parameter(myProv, "MaxItems", theObj.MaxItems)); 195 myCmd.Parameters.Add(Database.Parameter(myProv, "MaxAttachSizeKB", theObj.MaxAttachSizeKB)); 196 197 result = myCmd.ExecuteNonQuery(); 198 updateCultureText(theObj, myCmd, myProv); 199 myTrans.Commit(); 200 } 201 catch (Exception e) 202 { 203 myTrans.Rollback(); 204 throw e; 205 } 206 finally 207 { 208 myTrans.Dispose(); 209 myConn.Dispose(); 210 } 211 return result; 212 } 213 214 public override Section Insert(Section newObj) 215 { 216 DbProviderFactory myProv = Database.ProviderFactory; 217 DbTransaction myTrans = null; 218 DbConnection myConn = myProv.CreateConnection(); 219 DbCommand myCmd = myConn.CreateCommand(); 220 string sSql; 221 Section result = new Section(); 222 223 try 224 { 225 //create read/write permission 226 new PermissionProvider().CreatePermissionObj(newObj); 227 228 myConn.ConnectionString = Database.ConnString; 229 myConn.Open(); 230 myCmd.Connection = myConn; 231 232 myTrans = myConn.BeginTransaction(); 233 myCmd.Transaction = myTrans; 234 235 result = newObj; 236 result.Id = base.GetNextId(); 237 238 sSql = "INSERT INTO [" + this.TableName + "](Id, Enabled, " 239 + " AccessType, PermissionId, AccessCode, AccessLevel, " 240 + " WriteAccessType, WritePermissionId, WriteAccessCode, WriteAccessLevel, " 241 + " MaxItems, MaxAttachSizeKB) " 242 + " VALUES(@Id, @Enabled, " 243 + " @AccessType, @PermissionId, @AccessCode, @AccessLevel, " 244 + " @WriteAccessType, @WritePermissionId, @WriteAccessCode, @WriteAccessLevel, " 245 + " @MaxItems, @MaxAttachSizeKB) "; 246 myCmd.CommandText = Database.ParseSql(sSql); 247 248 myCmd.Parameters.Add(Database.Parameter(myProv, "Id", result.Id)); 249 myCmd.Parameters.Add(Database.Parameter(myProv, "Enabled", result.Enabled)); 250 //read permissions 251 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessType", (int)result.ReadAccessType)); 252 myCmd.Parameters.Add(Database.Parameter(myProv, "PermissionId", result.ReadPermissionId)); 253 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessCode", (string)result.ReadAccessCode)); 254 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessLevel", (int)result.ReadAccessLevel)); 255 //write permissions 256 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessType", (int)result.WriteAccessType)); 257 myCmd.Parameters.Add(Database.Parameter(myProv, "WritePermissionId", result.WritePermissionId)); 258 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessCode", (string)result.WriteAccessCode)); 259 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessLevel", (int)result.WriteAccessLevel)); 260 //limits 261 myCmd.Parameters.Add(Database.Parameter(myProv, "MaxItems", (int)result.MaxItems)); 262 myCmd.Parameters.Add(Database.Parameter(myProv, "MaxAttachSizeKB", (int)result.MaxAttachSizeKB)); 263 264 myCmd.ExecuteNonQuery(); 265 updateCultureText(result, myCmd, myProv); 266 myTrans.Commit(); 267 } 268 catch (Exception e) 269 { 270 myTrans.Rollback(); 271 throw e; 272 } 273 finally 274 { 275 myTrans.Dispose(); 276 myConn.Dispose(); 277 } 278 return result; 279 } 280 281 public int DeleteById(int id, bool deleteChilds) 282 { 283 DbProviderFactory myProv = Database.ProviderFactory; 284 DbTransaction myTrans = null; 285 DbConnection myConn = myProv.CreateConnection(); 286 DbCommand myCmd = myProv.CreateCommand(); 287 string sSql; 288 int res = 0; 289 290 if (!deleteChilds && this.hasChilds(id)) 291 { 292 throw new ArgumentException("current obj has childs"); 293 } 294 295 try 296 { 297 var currObj = this.GetByKey(id); 298 if (deleteChilds && id>0) 299 { 300 //delete all its categories 301 var catman = new CategoriesManager(); 302 var catfilter = new CategoriesFilter(); 303 catfilter.SectionId = id; 304 var catList = catman.GetByFilter(catfilter, ""); 305 foreach (var cat in catList) 306 { 307 catman.DeleteById(cat.Id, true); 308 } 309 } 310 311 currObj.DeleteImages(); 312 currObj.DeleteFiles(); 313 new PermissionProvider().RemovePermissionById(currObj.ReadPermissionId); 314 new PermissionProvider().RemovePermissionById(currObj.WritePermissionId); 315 316 myConn.ConnectionString = Database.ConnString; 317 myConn.Open(); 318 myCmd.Connection = myConn; 319 320 myTrans = myConn.BeginTransaction(); 321 myCmd.Transaction = myTrans; 322 323 sSql = "DELETE FROM [" + this.TableName + "] WHERE Id = @Id "; 324 myCmd.CommandText = Database.ParseSql(sSql); 325 myCmd.Parameters.Add(Database.Parameter(myProv, "Id", id)); 326 res = myCmd.ExecuteNonQuery(); 327 328 sSql = "DELETE FROM [" + this.TableName + "_Culture] WHERE SectionId = @SectionId "; 329 myCmd.CommandText = Database.ParseSql(sSql); 330 myCmd.Parameters.Clear(); 331 myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", id)); 332 myCmd.ExecuteNonQuery(); 333 334 myTrans.Commit(); 335 } 336 catch (Exception e) 337 { 338 myTrans.Rollback(); 339 throw e; 340 } 341 finally 342 { 343 myTrans.Dispose(); 344 myConn.Dispose(); 345 } 346 return res; 347 } 348 349 public override int DeleteById(int id) 350 { 351 return this.DeleteById(id, false); 352 } 353 354 protected override void FillObject(Section result, DbDataReader myRd) 355 { 356 if (!Convert.IsDBNull(myRd["Id"])) 357 result.Id = (int)myRd["Id"]; 358 if (!Convert.IsDBNull(myRd["Enabled"])) 359 result.Enabled = (bool)myRd["Enabled"]; 360 361 //read permissions 362 if (!Convert.IsDBNull(myRd["AccessType"])) 363 result.ReadAccessType = (MenuAccesstype)int.Parse(myRd["AccessType"].ToString()); 364 if (!Convert.IsDBNull(myRd["PermissionId"])) 365 result.ReadPermissionId = (int)myRd["PermissionId"]; 366 if (!Convert.IsDBNull(myRd["AccessCode"])) 367 result.ReadAccessCode = (string)myRd["AccessCode"]; 368 if (!Convert.IsDBNull(myRd["AccessLevel"])) 369 result.ReadAccessLevel = (int)myRd["AccessLevel"]; 370 //write permissions 371 if (!Convert.IsDBNull(myRd["WriteAccessType"])) 372 result.WriteAccessType = (MenuAccesstype)int.Parse(myRd["WriteAccessType"].ToString()); 373 if (!Convert.IsDBNull(myRd["WritePermissionId"])) 374 result.WritePermissionId = (int)myRd["WritePermissionId"]; 375 if (!Convert.IsDBNull(myRd["WriteAccessCode"])) 376 result.WriteAccessCode = (string)myRd["WriteAccessCode"]; 377 if (!Convert.IsDBNull(myRd["WriteAccessLevel"])) 378 result.WriteAccessLevel = (int)myRd["WriteAccessLevel"]; 379 //limits 380 if (!Convert.IsDBNull(myRd["MaxItems"])) 381 result.MaxItems = (int)myRd["MaxItems"]; 382 if (!Convert.IsDBNull(myRd["MaxAttachSizeKB"])) 383 result.MaxAttachSizeKB = (int)myRd["MaxAttachSizeKB"]; 384 } 385 386 private bool hasChilds(int sectionId) 387 { 388 bool res = false; 389 var man = new CategoriesManager(); 390 var filter = new CategoriesFilter(); 391 filter.SectionId = sectionId; 392 if (man.GetByFilter(filter, "").Count > 0) 393 res = true; 394 return res; 395 } 396 397 private void getCultureSpecific(Section result, DbDataReader myRd, 398 DbCommand myCmd, DbProviderFactory myProv) 399 { 400 string sSql; 401 //culture specific 402 sSql = "SELECT CultureName, SectionId, Title, Description " 403 + " FROM [" + this.TableName + "_culture] t " 404 + " WHERE SectionId = @SectionId "; 405 myCmd.CommandText = Database.ParseSql(sSql); 406 myCmd.Parameters.Clear(); 407 myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", result.Id)); 408 myRd = myCmd.ExecuteReader(); 409 while (myRd.Read()) 410 { 411 if (!Convert.IsDBNull(myRd["Title"])) 412 result.TitleTranslations.Add((string)myRd["cultureName"], (string)myRd["Title"]); 413 if (!Convert.IsDBNull(myRd["Description"])) 414 result.DescriptionTranslations.Add((string)myRd["cultureName"], (string)myRd["Description"]); 415 } 416 myRd.Close(); 417 } 418 419 private void updateCultureText(Section theObj, DbCommand myCmd, DbProviderFactory myProv) 420 { 421 string sSql = ""; 422 423 foreach (KeyValuePair<string, string> item in theObj.TitleTranslations) 424 { 425 string descriptionValue = ""; 426 theObj.DescriptionTranslations.TryGetValue(item.Key, out descriptionValue); 427 428 sSql = "DELETE FROM [" + this.TableName + "_culture] WHERE CultureName=@CultureName AND SectionId=@SectionId "; 429 myCmd.CommandText = Database.ParseSql(sSql); 430 myCmd.Parameters.Clear(); 431 myCmd.Parameters.Add(Database.Parameter(myProv, "CultureName", item.Key)); 432 myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", theObj.Id)); 433 myCmd.ExecuteNonQuery(); 434 435 sSql = "INSERT INTO [" + this.TableName + "_culture](CultureName, SectionId, Title, Description) " 436 + " VALUES(@CultureName, @SectionId, @Title, @Description) "; 437 myCmd.CommandText = Database.ParseSql(sSql); 438 myCmd.Parameters.Clear(); 439 myCmd.Parameters.Add(Database.Parameter(myProv, "CultureName", item.Key)); 440 myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", theObj.Id)); 441 myCmd.Parameters.Add(Database.Parameter(myProv, "Title", item.Value)); 442 myCmd.Parameters.Add(Database.Parameter(myProv, "Description", descriptionValue)); 443 myCmd.ExecuteNonQuery(); 444 } 445 } 446 } 447}