PageRenderTime 60ms CodeModel.GetById 32ms app.highlight 24ms RepoModel.GetById 1ms app.codeStats 0ms

/projects/PigeonCms.Core/DAL/SectionsManager.cs

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