PageRenderTime 50ms CodeModel.GetById 18ms app.highlight 26ms RepoModel.GetById 1ms app.codeStats 0ms

/projects/PigeonCms.Core/DAL/CategoriesManager.cs

http://pigeoncms.googlecode.com/
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}