/projects/PigeonCms.Core/DAL/CategoriesManager.cs
C# | 696 lines | 605 code | 61 blank | 30 comment | 83 complexity | 0264571577a9a0c5a2090c0cf78e31ec 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 CategoriesManager : TableManagerWithOrdering<Category, CategoriesFilter, 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 CategoriesManager(): this(false, false) 39 { } 40 41 public CategoriesManager(bool checkUserContext, bool writeMode) 42 { 43 this.TableName = "#__categories"; 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 CategoriesFilter filter = new CategoriesFilter(); 54 List<Category> list = GetByFilter(filter, "SectionId"); 55 foreach (Category item in list) 56 { 57 string sectionTitle = ""; 58 sectionTitle = new SectionsManager().GetByKey(item.SectionId).Title; 59 res.Add(item.Id.ToString(), sectionTitle + " > " + item.Title); 60 } 61 return res; 62 } 63 64 public override List<Category> GetByFilter(CategoriesFilter filter, string sort) 65 { 66 DbProviderFactory myProv = Database.ProviderFactory; 67 DbConnection myConn = myProv.CreateConnection(); 68 DbDataReader myRd = null; 69 DbCommand myCmd = myConn.CreateCommand(); 70 string sSql; 71 List<Category> result = new List<Category>(); 72 var sectionsList = new List<Section>(); 73 74 try 75 { 76 myConn.ConnectionString = Database.ConnString; 77 myConn.Open(); 78 myCmd.Connection = myConn; 79 80 sSql = "SELECT t.Id, t.SectionId, t.ParentId, t.Enabled, " 81 + " t.Ordering, t.DefaultImageName, " 82 + " t.AccessType, t.PermissionId, t.AccessCode, t.AccessLevel, " 83 + " t.WriteAccessType, t.WritePermissionId, t.WriteAccessCode, t.WriteAccessLevel, " 84 + " sect.AccessType sectAccessType, sect.PermissionId sectPermissionId, " 85 + " sect.AccessCode sectAccessCode, sect.AccessLevel sectAccessLevel, " 86 + " sect.WriteAccessType sectWriteAccessType, sect.WritePermissionId sectWritePermissionId, " 87 + " sect.WriteAccessCode sectWriteAccessCode, sect.WriteAccessLevel sectWriteAccessLevel " 88 + " FROM [" + this.TableName + "] t " 89 + " LEFT JOIN [" + this.TableName + "_Culture]c ON t.Id=c.CategoryId " 90 + " LEFT JOIN #__sections sect ON t.SectionId = sect.Id " 91 + " WHERE t.Id > 0 "; 92 if (filter.Id > 0 || filter.Id == -1) 93 { 94 sSql += " AND t.Id = @Id "; 95 myCmd.Parameters.Add(Database.Parameter(myProv, "Id", filter.Id)); 96 } 97 if (filter.SectionId > 0 || filter.SectionId == -1) 98 { 99 sSql += " AND t.SectionId = @SectionId "; 100 myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", filter.SectionId)); 101 } 102 if (filter.ParentId > 0) 103 { 104 sSql += " AND t.ParentId = @ParentId "; 105 myCmd.Parameters.Add(Database.Parameter(myProv, "ParentId", filter.ParentId)); 106 } 107 //if (!string.IsNullOrEmpty(filter.Title)) 108 //{ 109 // sSql += " AND c.Title = @Title "; 110 // myCmd.Parameters.Add(Database.Parameter(myProv, "Title", filter.Title)); 111 //} 112 if (filter.Enabled != Utility.TristateBool.NotSet) 113 { 114 sSql += " AND t.Enabled = @Enabled "; 115 myCmd.Parameters.Add(Database.Parameter(myProv, "Enabled", filter.Enabled)); 116 } 117 if (!string.IsNullOrEmpty(filter.Alias)) 118 { 119 sSql += " AND (replace(lower(c.title),' ','-') = @Alias) "; 120 myCmd.Parameters.Add(Database.Parameter(myProv, "Alias", filter.Alias)); 121 } 122 123 sSql += "GROUP BY t.Id, t.SectionId, t.ParentId, t.Enabled, " 124 + " t.Ordering, t.DefaultImageName, " 125 + " t.AccessType, t.PermissionId, t.AccessCode, t.AccessLevel, " 126 + " t.WriteAccessType, t.WritePermissionId, t.WriteAccessCode, t.WriteAccessLevel, " 127 + " sect.AccessType, sect.PermissionId, " 128 + " sect.AccessCode, sect.AccessLevel, " 129 + " sect.WriteAccessType, sect.WritePermissionId, " 130 + " sect.WriteAccessCode, sect.WriteAccessLevel"; 131 if (!string.IsNullOrEmpty(sort)) 132 sSql += " ORDER BY " + sort; 133 else 134 sSql += " ORDER BY t.SectionId, t.Ordering "; 135 136 myCmd.CommandText = Database.ParseSql(sSql); 137 myRd = myCmd.ExecuteReader(); 138 while (myRd.Read()) 139 { 140 var item = new Category(); 141 FillObject(item, myRd); 142 result.Add(item); 143 144 var sec = new Section(); 145 fillSection(sec, myRd); 146 if (!sectionsList.Exists( 147 delegate(Section s) 148 { 149 return s.Id == sec.Id; 150 })) 151 sectionsList.Add(sec); 152 153 } 154 myRd.Close(); 155 156 //(other loop to avoid multiple reader on same command) 157 foreach (Category item in result) 158 { 159 //load read roles 160 if (item.ReadPermissionId > 0 && item.ReadAccessType != MenuAccesstype.Public) 161 item.ReadRolenames = new PermissionProvider().GetPermissionRoles(item.ReadPermissionId); 162 //load write roles 163 if (item.WritePermissionId > 0 && item.WriteAccessType != MenuAccesstype.Public) 164 item.WriteRolenames = new PermissionProvider().GetPermissionRoles(item.WritePermissionId); 165 } 166 if (this.CheckUserContext) 167 { 168 result.RemoveAll(new PermissionProvider().IsItemNotAllowed); 169 if (this.WriteMode) 170 result.RemoveAll(new PermissionProvider().IsItemNotAllowedForWrite); 171 172 //check parent sections 173 loadSectionsRoles(sectionsList); 174 sectionsList.RemoveAll(new PermissionProvider().IsItemNotAllowed); 175 if (this.WriteMode) 176 sectionsList.RemoveAll(new PermissionProvider().IsItemNotAllowedForWrite); 177 //remove items for not allowed sections 178 result.RemoveAll(new SectionPredicate(sectionsList).IsItemNotInSection); 179 } 180 181 //culture specifics 182 foreach (var item in result) 183 { 184 getCultureSpecific(item, myRd, myCmd, myProv); 185 } 186 } 187 finally 188 { 189 myConn.Dispose(); 190 } 191 return result; 192 } 193 194 public Category GetByAlias(string alias) 195 { 196 var result = new Category(); 197 var resultList = new List<Category>(); 198 var filter = new CategoriesFilter(); 199 filter.Alias = alias; 200 resultList = GetByFilter(filter, ""); 201 if (resultList.Count > 0) 202 result = resultList[0]; 203 return result; 204 } 205 206 public override Category GetByKey(int id) 207 { 208 var result = new Category(); 209 var resultList = new List<Category>(); 210 var filter = new CategoriesFilter(); 211 filter.Id = id; 212 resultList = GetByFilter(filter, ""); 213 if (resultList.Count > 0) 214 result = resultList[0]; 215 return result; 216 } 217 218 public override int Update(Category theObj) 219 { 220 DbProviderFactory myProv = Database.ProviderFactory; 221 DbTransaction myTrans = null; 222 DbConnection myConn = myProv.CreateConnection(); 223 DbCommand myCmd = myConn.CreateCommand(); 224 string sSql; 225 int result = 0; 226 227 if (theObj.Ordering == 0) 228 { 229 theObj.Ordering = this.GetNextOrdering(); 230 } 231 try 232 { 233 //fill ReadPermissionId and WritePermissionId before trans 234 new PermissionProvider().UpdatePermissionObj(theObj); 235 236 myConn.ConnectionString = Database.ConnString; 237 myConn.Open(); 238 myCmd.Connection = myConn; 239 240 myTrans = myConn.BeginTransaction(); 241 myCmd.Transaction = myTrans; 242 243 sSql = "UPDATE [" + this.TableName + "] " 244 + " SET SectionId=@SectionId, ParentId=@ParentId, Enabled=@Enabled, " 245 + " Ordering=@Ordering, DefaultImageName=@DefaultImageName, " 246 + " AccessType=@AccessType, PermissionId=@PermissionId, " 247 + " [AccessCode]=@AccessCode, AccessLevel=@AccessLevel, " 248 + " WriteAccessType=@WriteAccessType, WritePermissionId=@WritePermissionId, " 249 + " [WriteAccessCode]=@WriteAccessCode, WriteAccessLevel=@WriteAccessLevel " 250 + " WHERE Id = @Id"; 251 myCmd.CommandText = Database.ParseSql(sSql); 252 myCmd.Parameters.Add(Database.Parameter(myProv, "Id", theObj.Id)); 253 myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", theObj.SectionId)); 254 myCmd.Parameters.Add(Database.Parameter(myProv, "ParentId", theObj.ParentId)); 255 myCmd.Parameters.Add(Database.Parameter(myProv, "Enabled", theObj.Enabled)); 256 myCmd.Parameters.Add(Database.Parameter(myProv, "Ordering", theObj.Ordering)); 257 myCmd.Parameters.Add(Database.Parameter(myProv, "DefaultImageName", theObj.DefaultImageName)); 258 //read permissions 259 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessType", theObj.ReadAccessType)); 260 myCmd.Parameters.Add(Database.Parameter(myProv, "PermissionId", theObj.ReadPermissionId)); 261 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessCode", theObj.ReadAccessCode)); 262 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessLevel", theObj.ReadAccessLevel)); 263 //write permissions 264 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessType", theObj.WriteAccessType)); 265 myCmd.Parameters.Add(Database.Parameter(myProv, "WritePermissionId", theObj.WritePermissionId)); 266 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessCode", theObj.WriteAccessCode)); 267 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessLevel", theObj.WriteAccessLevel)); 268 269 result = myCmd.ExecuteNonQuery(); 270 updateCultureText(theObj, myCmd, myProv); 271 myTrans.Commit(); 272 } 273 catch (Exception e) 274 { 275 myTrans.Rollback(); 276 throw e; 277 } 278 finally 279 { 280 myTrans.Dispose(); 281 myConn.Dispose(); 282 } 283 return result; 284 } 285 286 public override Category Insert(Category newObj) 287 { 288 DbProviderFactory myProv = Database.ProviderFactory; 289 DbTransaction myTrans = null; 290 DbConnection myConn = myProv.CreateConnection(); 291 DbCommand myCmd = myConn.CreateCommand(); 292 string sSql; 293 Category result = new Category(); 294 295 try 296 { 297 //create read/write permission 298 new PermissionProvider().CreatePermissionObj(newObj); 299 300 myConn.ConnectionString = Database.ConnString; 301 myConn.Open(); 302 myCmd.Connection = myConn; 303 304 myTrans = myConn.BeginTransaction(); 305 myCmd.Transaction = myTrans; 306 307 result = newObj; 308 result.Id = base.GetNextId(); 309 result.Ordering = base.GetNextOrdering(); 310 311 sSql = "INSERT INTO [" + this.TableName + "](Id, SectionId, ParentId, Enabled, Ordering, DefaultImageName, " 312 + " AccessType, PermissionId, AccessCode, AccessLevel, " 313 + " WriteAccessType, WritePermissionId, WriteAccessCode, WriteAccessLevel) " 314 + " VALUES(@Id, @SectionId, @ParentId, @Enabled, @Ordering, @DefaultImageName, " 315 + " @AccessType, @PermissionId, @AccessCode, @AccessLevel, " 316 + " @WriteAccessType, @WritePermissionId, @WriteAccessCode, @WriteAccessLevel) "; 317 myCmd.CommandText = Database.ParseSql(sSql); 318 319 myCmd.Parameters.Add(Database.Parameter(myProv, "Id", result.Id)); 320 myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", result.SectionId)); 321 myCmd.Parameters.Add(Database.Parameter(myProv, "ParentId", result.ParentId)); 322 myCmd.Parameters.Add(Database.Parameter(myProv, "Enabled", result.Enabled)); 323 myCmd.Parameters.Add(Database.Parameter(myProv, "Ordering", result.Ordering)); 324 myCmd.Parameters.Add(Database.Parameter(myProv, "DefaultImageName", result.DefaultImageName)); 325 //read permissions 326 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessType", (int)result.ReadAccessType)); 327 myCmd.Parameters.Add(Database.Parameter(myProv, "PermissionId", result.ReadPermissionId)); 328 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessCode", (string)result.ReadAccessCode)); 329 myCmd.Parameters.Add(Database.Parameter(myProv, "AccessLevel", (int)result.ReadAccessLevel)); 330 //write permissions 331 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessType", (int)result.WriteAccessType)); 332 myCmd.Parameters.Add(Database.Parameter(myProv, "WritePermissionId", result.WritePermissionId)); 333 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessCode", (string)result.WriteAccessCode)); 334 myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessLevel", (int)result.WriteAccessLevel)); 335 336 myCmd.ExecuteNonQuery(); 337 updateCultureText(result, myCmd, myProv); 338 myTrans.Commit(); 339 } 340 catch (Exception e) 341 { 342 myTrans.Rollback(); 343 throw e; 344 } 345 finally 346 { 347 myTrans.Dispose(); 348 myConn.Dispose(); 349 } 350 return result; 351 } 352 353 public int DeleteById(int id, bool deleteChilds) 354 { 355 DbProviderFactory myProv = Database.ProviderFactory; 356 DbTransaction myTrans = null; 357 DbConnection myConn = myProv.CreateConnection(); 358 DbCommand myCmd = myProv.CreateCommand(); 359 string sSql; 360 int res = 0; 361 362 if (!deleteChilds && this.hasChilds(id)) 363 { 364 throw new ArgumentException("current obj has childs"); 365 } 366 367 try 368 { 369 var currObj = this.GetByKey(id); 370 if (deleteChilds && id > 0) 371 { 372 //delete all its items 373 var itemsManager = new ItemsManager<Item, ItemsFilter>(); 374 var itemsFilter = new ItemsFilter(); 375 itemsFilter.CategoryId = id; 376 var itemsList = itemsManager.GetByFilter(itemsFilter, ""); 377 foreach (var item in itemsList) 378 { 379 itemsManager.DeleteById(item.Id); 380 } 381 } 382 383 currObj.DeleteImages(); 384 currObj.DeleteFiles(); 385 new PermissionProvider().RemovePermissionById(currObj.ReadPermissionId); 386 new PermissionProvider().RemovePermissionById(currObj.WritePermissionId); 387 388 myConn.ConnectionString = Database.ConnString; 389 myConn.Open(); 390 myCmd.Connection = myConn; 391 392 myTrans = myConn.BeginTransaction(); 393 myCmd.Transaction = myTrans; 394 395 sSql = "DELETE FROM [" + this.TableName + "] WHERE Id = @Id "; 396 myCmd.CommandText = Database.ParseSql(sSql); 397 myCmd.Parameters.Add(Database.Parameter(myProv, "Id", id)); 398 res = myCmd.ExecuteNonQuery(); 399 400 sSql = "DELETE FROM [" + this.TableName + "_Culture] WHERE CategoryId = @CategoryId "; 401 myCmd.CommandText = Database.ParseSql(sSql); 402 myCmd.Parameters.Clear(); 403 myCmd.Parameters.Add(Database.Parameter(myProv, "CategoryId", id)); 404 myCmd.ExecuteNonQuery(); 405 406 myTrans.Commit(); 407 } 408 catch (Exception e) 409 { 410 myTrans.Rollback(); 411 throw e; 412 } 413 finally 414 { 415 myTrans.Dispose(); 416 myConn.Dispose(); 417 } 418 return res; 419 } 420 421 public override int DeleteById(int id) 422 { 423 return this.DeleteById(id, false); 424 } 425 426 private class SectionPredicate 427 { 428 private List<Section> list = null; 429 public SectionPredicate(List<Section> list) 430 { 431 this.list = list; 432 } 433 434 public bool IsItemNotInSection(Category item) 435 { 436 foreach (var s in list) 437 if (s.Id == item.SectionId) return false; 438 return true; 439 } 440 } 441 442 private void loadSectionsRoles(List<Section> list) 443 { 444 foreach (var item in list) 445 { 446 //load read roles 447 if (item.ReadPermissionId > 0 && item.ReadAccessType != MenuAccesstype.Public) 448 item.ReadRolenames = new PermissionProvider().GetPermissionRoles(item.ReadPermissionId); 449 //load write roles 450 if (item.WritePermissionId > 0 && item.WriteAccessType != MenuAccesstype.Public) 451 item.WriteRolenames = new PermissionProvider().GetPermissionRoles(item.WritePermissionId); 452 } 453 } 454 455 private void fillSection(Section result, DbDataReader myRd) 456 { 457 if (!Convert.IsDBNull(myRd["SectionId"])) 458 result.Id = (int)myRd["SectionId"]; 459 //read permissions 460 if (!Convert.IsDBNull(myRd["SectAccessType"])) 461 result.ReadAccessType = (MenuAccesstype)int.Parse(myRd["SectAccessType"].ToString()); 462 if (!Convert.IsDBNull(myRd["SectPermissionId"])) 463 result.ReadPermissionId = (int)myRd["SectPermissionId"]; 464 if (!Convert.IsDBNull(myRd["SectAccessCode"])) 465 result.ReadAccessCode = (string)myRd["SectAccessCode"]; 466 if (!Convert.IsDBNull(myRd["SectAccessLevel"])) 467 result.ReadAccessLevel = (int)myRd["SectAccessLevel"]; 468 //write permissions 469 if (!Convert.IsDBNull(myRd["SectWriteAccessType"])) 470 result.WriteAccessType = (MenuAccesstype)int.Parse(myRd["SectWriteAccessType"].ToString()); 471 if (!Convert.IsDBNull(myRd["SectWritePermissionId"])) 472 result.WritePermissionId = (int)myRd["SectWritePermissionId"]; 473 if (!Convert.IsDBNull(myRd["SectWriteAccessCode"])) 474 result.WriteAccessCode = (string)myRd["SectWriteAccessCode"]; 475 if (!Convert.IsDBNull(myRd["SectWriteAccessLevel"])) 476 result.WriteAccessLevel = (int)myRd["SectWriteAccessLevel"]; 477 } 478 479 protected override void FillObject(Category result, DbDataReader myRd) 480 { 481 if (!Convert.IsDBNull(myRd["Id"])) 482 result.Id = (int)myRd["Id"]; 483 if (!Convert.IsDBNull(myRd["SectionId"])) 484 result.SectionId = (int)myRd["SectionId"]; 485 if (!Convert.IsDBNull(myRd["ParentId"])) 486 result.ParentId = (int)myRd["ParentId"]; 487 if (!Convert.IsDBNull(myRd["Enabled"])) 488 result.Enabled = (bool)myRd["Enabled"]; 489 if (!Convert.IsDBNull(myRd["Ordering"])) 490 result.Ordering = (int)myRd["Ordering"]; 491 if (!Convert.IsDBNull(myRd["DefaultImageName"])) 492 result.DefaultImageName = myRd["DefaultImageName"].ToString(); 493 //read permissions 494 if (!Convert.IsDBNull(myRd["AccessType"])) 495 result.ReadAccessType = (MenuAccesstype)int.Parse(myRd["AccessType"].ToString()); 496 if (!Convert.IsDBNull(myRd["PermissionId"])) 497 result.ReadPermissionId = (int)myRd["PermissionId"]; 498 if (!Convert.IsDBNull(myRd["AccessCode"])) 499 result.ReadAccessCode = (string)myRd["AccessCode"]; 500 if (!Convert.IsDBNull(myRd["AccessLevel"])) 501 result.ReadAccessLevel = (int)myRd["AccessLevel"]; 502 //write permissions 503 if (!Convert.IsDBNull(myRd["WriteAccessType"])) 504 result.WriteAccessType = (MenuAccesstype)int.Parse(myRd["WriteAccessType"].ToString()); 505 if (!Convert.IsDBNull(myRd["WritePermissionId"])) 506 result.WritePermissionId = (int)myRd["WritePermissionId"]; 507 if (!Convert.IsDBNull(myRd["WriteAccessCode"])) 508 result.WriteAccessCode = (string)myRd["WriteAccessCode"]; 509 if (!Convert.IsDBNull(myRd["WriteAccessLevel"])) 510 result.WriteAccessLevel = (int)myRd["WriteAccessLevel"]; 511 } 512 513 protected override int GetPreviousRecordInOrder(int ordering, int currentRecordId) 514 { 515 DbProviderFactory myProv = Database.ProviderFactory; 516 DbConnection myConn = myProv.CreateConnection(); 517 DbDataReader myRd = null; 518 DbCommand myCmd = myConn.CreateCommand(); 519 string sSql; 520 int result = currentRecordId; 521 522 try 523 { 524 var o1 = new PigeonCms.Category(); 525 o1 = this.GetByKey(currentRecordId); 526 527 myConn.ConnectionString = Database.ConnString; 528 myConn.Open(); 529 myCmd.Connection = myConn; 530 531 sSql = "SELECT TOP 1 t.Id " 532 + " FROM [" + this.TableName + "] t " 533 + " LEFT JOIN #__sections sect ON t.SectionId = sect.Id " 534 + " WHERE t.Ordering < @Ordering " 535 + " AND t.SectionId = @SectionId " 536 + " ORDER BY t.Ordering DESC "; 537 myCmd.CommandText = Database.ParseSql(sSql); 538 myCmd.Parameters.Add(Database.Parameter(myProv, "Ordering", ordering)); 539 myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", o1.Section.Id)); 540 myRd = myCmd.ExecuteReader(); 541 if (myRd.Read()) 542 { 543 if (myRd[0] != DBNull.Value) 544 { 545 result = (int)myRd[0]; 546 } 547 } 548 myRd.Close(); 549 //se nn trovo un record prendo quello precedente per chiave (per init tabella) 550 if (result == 0) 551 { 552 sSql = "SELECT TOP 1 [Id] FROM " + TableName 553 + " WHERE [" + KeyFieldName + "] < @currentRecordId ORDER BY t.Ordering ASC "; 554 myCmd.CommandText = Database.ParseSql(sSql); 555 myCmd.Parameters.Add(Database.Parameter(myProv, "currentRecordId", currentRecordId)); 556 myRd = myCmd.ExecuteReader(); 557 if (myRd.Read()) 558 { 559 if (myRd[0] != DBNull.Value) 560 { 561 result = (int)myRd[0]; 562 } 563 } 564 myRd.Close(); 565 } 566 } 567 finally 568 { 569 myConn.Dispose(); 570 } 571 return result; 572 } 573 574 protected override int GetNextRecordInOrder(int ordering, int currentRecordId) 575 { 576 DbProviderFactory myProv = Database.ProviderFactory; 577 DbConnection myConn = myProv.CreateConnection(); 578 DbDataReader myRd = null; 579 DbCommand myCmd = myConn.CreateCommand(); 580 string sSql; 581 int result = currentRecordId; 582 583 try 584 { 585 var o1 = new PigeonCms.Category(); 586 o1 = this.GetByKey(currentRecordId); 587 588 myConn.ConnectionString = Database.ConnString; 589 myConn.Open(); 590 myCmd.Connection = myConn; 591 592 sSql = "SELECT TOP 1 t.Id " 593 + " FROM [" + this.TableName + "] t " 594 + " LEFT JOIN #__sections sect ON t.SectionId = sect.Id " 595 + " WHERE t.Ordering > @Ordering " 596 + " AND t.SectionId = @SectionId " 597 + " ORDER BY t.Ordering ASC "; 598 myCmd.CommandText = Database.ParseSql(sSql); 599 myCmd.Parameters.Add(Database.Parameter(myProv, "Ordering", ordering)); 600 myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", o1.Section.Id)); 601 myRd = myCmd.ExecuteReader(); 602 if (myRd.Read()) 603 { 604 if (myRd[0] != DBNull.Value) 605 { 606 result = (int)myRd[0]; 607 } 608 } 609 myRd.Close(); 610 //se nn trovo un record prendo quello successivo per chiave (per init tabella) 611 if (result == currentRecordId) 612 { 613 sSql = "SELECT TOP 1 [Id] FROM " + TableName 614 + " WHERE [" + KeyFieldName + "] > @currentRecordId ORDER BY Ordering, [" + KeyFieldName + "] "; 615 myCmd.CommandText = Database.ParseSql(sSql); 616 myCmd.Parameters.Add(Database.Parameter(myProv, "currentRecordId", currentRecordId)); 617 myRd = myCmd.ExecuteReader(); 618 if (myRd.Read()) 619 { 620 if (myRd[0] != DBNull.Value) 621 { 622 result = (int)myRd[0]; 623 } 624 } 625 myRd.Close(); 626 } 627 } 628 finally 629 { 630 myConn.Dispose(); 631 } 632 return result; 633 } 634 635 private bool hasChilds(int categoryId) 636 { 637 bool res = false; 638 var man = new ItemsManager<Item, ItemsFilter>(); 639 var filter = new ItemsFilter(); 640 filter.CategoryId = categoryId; 641 if (man.GetByFilter(filter, "").Count > 0) 642 res = true; 643 return res; 644 } 645 646 647 private void getCultureSpecific(Category result, DbDataReader myRd, 648 DbCommand myCmd, DbProviderFactory myProv) 649 { 650 string sSql; 651 //culture specific 652 sSql = "SELECT CultureName, CategoryId, Title, Description " 653 + " FROM [" + this.TableName + "_culture] t " 654 + " WHERE CategoryId = @CategoryId "; 655 myCmd.CommandText = Database.ParseSql(sSql); 656 myCmd.Parameters.Clear(); 657 myCmd.Parameters.Add(Database.Parameter(myProv, "CategoryId", result.Id)); 658 myRd = myCmd.ExecuteReader(); 659 while (myRd.Read()) 660 { 661 if (!Convert.IsDBNull(myRd["Title"])) 662 result.TitleTranslations.Add((string)myRd["cultureName"], (string)myRd["Title"]); 663 if (!Convert.IsDBNull(myRd["Description"])) 664 result.DescriptionTranslations.Add((string)myRd["cultureName"], (string)myRd["Description"]); 665 } 666 myRd.Close(); 667 } 668 669 private void updateCultureText(Category theObj, DbCommand myCmd, DbProviderFactory myProv) 670 { 671 string sSql = ""; 672 foreach (KeyValuePair<string, string> item in theObj.TitleTranslations) 673 { 674 string descriptionValue = ""; 675 theObj.DescriptionTranslations.TryGetValue(item.Key, out descriptionValue); 676 677 sSql = "DELETE FROM [" + this.TableName + "_culture] WHERE CultureName=@CultureName AND CategoryId=@CategoryId "; 678 myCmd.CommandText = Database.ParseSql(sSql); 679 myCmd.Parameters.Clear(); 680 myCmd.Parameters.Add(Database.Parameter(myProv, "CultureName", item.Key)); 681 myCmd.Parameters.Add(Database.Parameter(myProv, "CategoryId", theObj.Id)); 682 myCmd.ExecuteNonQuery(); 683 684 sSql = "INSERT INTO [" + this.TableName + "_culture](CultureName, CategoryId, Title, Description) " 685 + " VALUES(@CultureName, @CategoryId, @Title, @Description) "; 686 myCmd.CommandText = Database.ParseSql(sSql); 687 myCmd.Parameters.Clear(); 688 myCmd.Parameters.Add(Database.Parameter(myProv, "CultureName", item.Key)); 689 myCmd.Parameters.Add(Database.Parameter(myProv, "CategoryId", theObj.Id)); 690 myCmd.Parameters.Add(Database.Parameter(myProv, "Title", item.Value)); 691 myCmd.Parameters.Add(Database.Parameter(myProv, "Description", descriptionValue)); 692 myCmd.ExecuteNonQuery(); 693 } 694 } 695 } 696}