PageRenderTime 68ms CodeModel.GetById 15ms RepoModel.GetById 0ms app.codeStats 1ms

/projects/PigeonCms.Core/DAL/ItemsManager.cs

http://pigeoncms.googlecode.com/
C# | 1145 lines | 984 code | 87 blank | 74 comment | 185 complexity | 7bc6e56dfc93ad3922c892e828b46c28 MD5 | raw file
Possible License(s): LGPL-3.0, GPL-3.0, LGPL-2.1, GPL-2.0
  1. using System;
  2. using System.Data;
  3. using System.Configuration;
  4. using System.Web;
  5. using System.Web.Security;
  6. using System.Web.UI;
  7. using System.Web.UI.WebControls;
  8. using System.Web.UI.WebControls.WebParts;
  9. using System.Web.UI.HtmlControls;
  10. using System.ComponentModel;
  11. using System.Collections.Generic;
  12. using System.IO;
  13. using System.Data.Common;
  14. using PigeonCms;
  15. using System.Diagnostics;
  16. namespace PigeonCms
  17. {
  18. public class ItemsManager<T, F> : TableManagerWithOrdering<T, F, int>, ITableManagerWithPermission
  19. where T: Item, new()
  20. where F: ItemsFilter, new()
  21. {
  22. public const string MaxItemsException = "1";
  23. private bool checkUserContext = false;
  24. private bool writeMode = false;
  25. public bool CheckUserContext
  26. {
  27. get { return checkUserContext; }
  28. }
  29. public bool WriteMode
  30. {
  31. get { return writeMode; }
  32. }
  33. /// <summary>
  34. /// CheckUserContext=false
  35. /// WriteMode=false
  36. /// </summary>
  37. [DebuggerStepThrough()]
  38. public ItemsManager(): this(false, false)
  39. { }
  40. public ItemsManager(bool checkUserContext, bool writeMode)
  41. {
  42. this.TableName = "#__items";
  43. this.KeyFieldName = "Id";
  44. this.checkUserContext = checkUserContext;
  45. this.writeMode = writeMode;
  46. if (this.writeMode) this.checkUserContext = true; //forced
  47. }
  48. public override Dictionary<string, string> GetList()
  49. {
  50. Dictionary<string, string> res = new Dictionary<string, string>();
  51. F filter = new F();
  52. Category cat = new Category();
  53. List<T> list = GetByFilter(filter, "");
  54. foreach (T item in list)
  55. {
  56. string sectionTitle = "";
  57. string categoryTitle = "";
  58. cat = new CategoriesManager().GetByKey(item.CategoryId);
  59. categoryTitle = cat.Title;
  60. sectionTitle = new SectionsManager().GetByKey(cat.SectionId).Title;
  61. res.Add(item.Id.ToString(), sectionTitle + " > " + categoryTitle + " > " + item.Title);
  62. }
  63. return res;
  64. }
  65. public override List<T> GetByFilter(F filter, string sort)
  66. {
  67. DbProviderFactory myProv = Database.ProviderFactory;
  68. DbConnection myConn = myProv.CreateConnection();
  69. DbDataReader myRd = null;
  70. DbCommand myCmd = myConn.CreateCommand();
  71. string sSql;
  72. List<T> result = new List<T>();
  73. var sectionsList = new List<Section>();
  74. var categoriesList = new List<Category>();
  75. string topItems = "";
  76. try
  77. {
  78. myConn.ConnectionString = Database.ConnString;
  79. myConn.Open();
  80. myCmd.Connection = myConn;
  81. if (filter.NumOfRecords > 0)
  82. {
  83. topItems = "TOP " + filter.NumOfRecords.ToString();
  84. }
  85. sSql = "SELECT " + topItems + " t.Id, t.ItemType, t.CategoryId, categ.SectionId, "
  86. + " t.Enabled, t.Alias, t.Ordering, t.DefaultImageName, "
  87. + " t.[DateInserted], t.[UserInserted], t.[DateUpdated], t.[UserUpdated], "
  88. + " t.ItemDate, t.ValidFrom, t.ValidTo, "
  89. + " t.CustomBool1, t.CustomBool2, t.CustomBool3, "
  90. + " t.CustomDate1, t.CustomDate2, t.CustomDate3, "
  91. + " t.CustomDecimal1, t.CustomDecimal2, t.CustomDecimal3, "
  92. + " t.CustomInt1, t.CustomInt2, t.CustomInt3, "
  93. + " t.CustomString1, t.CustomString2, t.CustomString3, "
  94. + " t.ItemParams, t.AccessType, t.PermissionId, t.AccessCode, t.AccessLevel, "
  95. + " t.CommentsGroupId, t.WriteAccessType, t.WritePermissionId, t.WriteAccessCode, t.WriteAccessLevel, "
  96. + " t.ThreadId, "
  97. + " categ.AccessType categAccessType, categ.PermissionId categPermissionId, "
  98. + " categ.AccessCode categAccessCode, categ.AccessLevel categAccessLevel, "
  99. + " categ.WriteAccessType categWriteAccessType, categ.WritePermissionId categWritePermissionId, "
  100. + " categ.WriteAccessCode categWriteAccessCode, categ.WriteAccessLevel categWriteAccessLevel, "
  101. + " sect.AccessType sectAccessType, sect.PermissionId sectPermissionId, "
  102. + " sect.AccessCode sectAccessCode, sect.AccessLevel sectAccessLevel, "
  103. + " sect.WriteAccessType sectWriteAccessType, sect.WritePermissionId sectWritePermissionId, "
  104. + " sect.WriteAccessCode sectWriteAccessCode, sect.WriteAccessLevel sectWriteAccessLevel "
  105. + " FROM [" + this.TableName + "] t "
  106. + " LEFT JOIN [" + this.TableName + "_Culture] c ON t.Id = c.ItemId "
  107. + " LEFT JOIN #__categories categ ON t.CategoryId = categ.Id "
  108. + " LEFT JOIN #__sections sect ON categ.SectionId = sect.Id "
  109. + " WHERE t.Id > 0 ";
  110. if (filter.ShowOnlyRootItems)
  111. {
  112. //default
  113. sSql += " AND (t.ThreadId = t.Id) ";
  114. }
  115. if (filter.Id > 0 || filter.Id == -1)
  116. {
  117. sSql += " AND t.Id = @Id ";
  118. myCmd.Parameters.Add(Database.Parameter(myProv, "Id", filter.Id));
  119. }
  120. if (!string.IsNullOrEmpty(filter.ItemType))
  121. {
  122. sSql += " AND (t.ItemType = @ItemType) ";
  123. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemType", filter.ItemType));
  124. }
  125. if (filter.CategoryId > 0 || filter.CategoryId == -1)
  126. {
  127. sSql += " AND t.CategoryId = @CategoryId ";
  128. myCmd.Parameters.Add(Database.Parameter(myProv, "CategoryId", filter.CategoryId));
  129. }
  130. if (filter.SectionId > 0 || filter.SectionId == -1)
  131. {
  132. sSql += " AND categ.SectionId = @SectionId ";
  133. myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", filter.SectionId));
  134. }
  135. if (!string.IsNullOrEmpty(filter.TitleSearch))
  136. {
  137. sSql += " AND (c.Title like @TitleSearch) ";
  138. myCmd.Parameters.Add(Database.Parameter(myProv, "TitleSearch", "%" + filter.TitleSearch + "%"));
  139. }
  140. if (!string.IsNullOrEmpty(filter.Alias))
  141. {
  142. sSql += " AND t.Alias = @Alias ";
  143. myCmd.Parameters.Add(Database.Parameter(myProv, "Alias", filter.Alias));
  144. }
  145. if (!string.IsNullOrEmpty(filter.DescriptionSearch))
  146. {
  147. sSql += " AND (c.Description like @DescriptionSearch) ";
  148. myCmd.Parameters.Add(Database.Parameter(myProv, "DescriptionSearch", "%" + filter.DescriptionSearch + "%"));
  149. }
  150. if (!string.IsNullOrEmpty(filter.FullSearch))
  151. {
  152. sSql += " AND (c.Title like @TitleSearch OR c.Description like @DescriptionSearch) ";
  153. myCmd.Parameters.Add(Database.Parameter(myProv, "TitleSearch", "%" + filter.FullSearch + "%"));
  154. myCmd.Parameters.Add(Database.Parameter(myProv, "DescriptionSearch", "%" + filter.FullSearch + "%"));
  155. }
  156. if (filter.Enabled != Utility.TristateBool.NotSet)
  157. {
  158. sSql += " AND t.Enabled = @Enabled ";
  159. myCmd.Parameters.Add(Database.Parameter(myProv, "Enabled", filter.Enabled));
  160. }
  161. if (!string.IsNullOrEmpty(filter.AccessCode))
  162. {
  163. sSql += " AND t.AccessCode = @AccessCode ";
  164. myCmd.Parameters.Add(Database.Parameter(myProv, "AccessCode", filter.AccessCode));
  165. }
  166. if (filter.AccessLevel > 0)
  167. {
  168. sSql += " AND t.AccessLevel <= @AccessLevel ";
  169. myCmd.Parameters.Add(Database.Parameter(myProv, "AccessLevel", filter.AccessLevel));
  170. }
  171. if (!string.IsNullOrEmpty(filter.WriteAccessCode))
  172. {
  173. sSql += " AND t.WriteAccessCode = @WriteAccessCode ";
  174. myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessCode", filter.WriteAccessCode));
  175. }
  176. if (filter.WriteAccessLevel > 0)
  177. {
  178. sSql += " AND t.WriteAccessLevel <= @WriteAccessLevel ";
  179. myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessLevel", filter.WriteAccessLevel));
  180. }
  181. if (filter.ThreadId > 0 || filter.ThreadId == -1)
  182. {
  183. sSql += " AND t.ThreadId = @ThreadId ";
  184. myCmd.Parameters.Add(Database.Parameter(myProv, "ThreadId", filter.ThreadId));
  185. }
  186. //datesrange filter
  187. sSql += " AND ("
  188. + Database.AddDatesRangeParameters(myCmd.Parameters, myProv, "t.ItemDate", filter.ItemDateRange)
  189. + ")";
  190. if (filter.IsValidItem != Utility.TristateBool.NotSet)
  191. {
  192. DateTime getDate = DateTime.Now.Date;
  193. if (filter.IsValidItem == Utility.TristateBool.True)
  194. {
  195. sSql += " AND (t.ValidFrom <= @GetDate OR t.ValidFrom is null) ";
  196. sSql += " AND (t.ValidTo >= @GetDate OR t.ValidTo is null) ";
  197. myCmd.Parameters.Add(Database.Parameter(myProv, "GetDate", getDate));
  198. }
  199. else
  200. {
  201. sSql += " AND (t.ValidFrom >= @GetDate OR t.ValidTo <= @GetDate) ";
  202. myCmd.Parameters.Add(Database.Parameter(myProv, "GetDate", getDate));
  203. }
  204. }
  205. //custom fields
  206. if (filter.CustomBool1 != Utility.TristateBool.NotSet)
  207. {
  208. sSql += " AND t.CustomBool1 = @CustomBool1 ";
  209. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomBool1", filter.CustomBool1));
  210. }
  211. if (filter.CustomBool2 != Utility.TristateBool.NotSet)
  212. {
  213. sSql += " AND t.CustomBool2 = @CustomBool2 ";
  214. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomBool2", filter.CustomBool2));
  215. }
  216. if (filter.CustomBool1 != Utility.TristateBool.NotSet)
  217. {
  218. sSql += " AND t.CustomBool3 = @CustomBool3 ";
  219. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomBool3", filter.CustomBool3));
  220. }
  221. if (filter.CustomInt1 > 0)
  222. {
  223. sSql += " AND t.CustomInt1 = @CustomInt1 ";
  224. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomInt1", filter.CustomInt1));
  225. }
  226. if (filter.CustomInt2 > 0)
  227. {
  228. sSql += " AND t.CustomInt2 = @CustomInt2 ";
  229. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomInt2", filter.CustomInt2));
  230. }
  231. if (filter.CustomInt3 > 0)
  232. {
  233. sSql += " AND t.CustomInt3 = @CustomInt3 ";
  234. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomInt3", filter.CustomInt3));
  235. }
  236. if (!string.IsNullOrEmpty(filter.CustomString1))
  237. {
  238. sSql += " AND t.CustomString1 = @CustomString1 ";
  239. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString1", filter.CustomString1));
  240. }
  241. if (!string.IsNullOrEmpty(filter.CustomString2))
  242. {
  243. sSql += " AND t.CustomString2 = @CustomString2 ";
  244. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString2", filter.CustomString2));
  245. }
  246. if (!string.IsNullOrEmpty(filter.CustomString3))
  247. {
  248. sSql += " AND t.CustomString3 = @CustomString3 ";
  249. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString3", filter.CustomString3));
  250. }
  251. sSql += " GROUP BY t.Id, t.ItemType, t.CategoryId, categ.SectionId, "
  252. + " t.Enabled, t.Alias, t.Ordering, t.DefaultImageName, "
  253. + " t.[DateInserted], t.[UserInserted], t.[DateUpdated], t.[UserUpdated], "
  254. + " t.ItemDate, t.ValidFrom, t.ValidTo, "
  255. + " t.CustomBool1, t.CustomBool2, t.CustomBool3, "
  256. + " t.CustomDate1, t.CustomDate2, t.CustomDate3, "
  257. + " t.CustomDecimal1, t.CustomDecimal2, t.CustomDecimal3, "
  258. + " t.CustomInt1, t.CustomInt2, t.CustomInt3, "
  259. + " t.CustomString1, t.CustomString2, t.CustomString3, "
  260. + " t.ItemParams, t.AccessType, t.PermissionId, t.AccessCode, t.AccessLevel, "
  261. + " t.CommentsGroupId, t.WriteAccessType, t.WritePermissionId, t.WriteAccessCode, t.WriteAccessLevel, "
  262. + " t.ThreadId, "
  263. + " categ.AccessType, categ.PermissionId, "
  264. + " categ.AccessCode, categ.AccessLevel, "
  265. + " categ.WriteAccessType, categ.WritePermissionId, "
  266. + " categ.WriteAccessCode, categ.WriteAccessLevel, "
  267. + " sect.AccessType, sect.PermissionId, "
  268. + " sect.AccessCode, sect.AccessLevel, "
  269. + " sect.WriteAccessType, sect.WritePermissionId, "
  270. + " sect.WriteAccessCode, sect.WriteAccessLevel";
  271. if (!string.IsNullOrEmpty(sort))
  272. {
  273. sSql += " ORDER BY " + sort;
  274. }
  275. else
  276. {
  277. sSql += " ORDER BY categ.SectionId, t.CategoryId, t.Ordering ";
  278. //sSql += " ORDER BY categ.SectionId, t.CategoryId, t.ThreadId, t.Ordering ";
  279. }
  280. myCmd.CommandText = Database.ParseSql(sSql);
  281. myRd = myCmd.ExecuteReader();
  282. while (myRd.Read())
  283. {
  284. T item = new T();
  285. FillObject(item, myRd);
  286. result.Add(item);
  287. var sec = new Section();
  288. fillSection(sec, myRd);
  289. if (!sectionsList.Exists(
  290. delegate(Section s)
  291. {
  292. return s.Id == sec.Id;
  293. }))
  294. sectionsList.Add(sec);
  295. var cat = new Category();
  296. fillCategory(cat, myRd);
  297. if (!categoriesList.Exists(
  298. delegate(Category s)
  299. {
  300. return s.Id == cat.Id;
  301. }))
  302. categoriesList.Add(cat);
  303. }
  304. myRd.Close();
  305. //(other loop to avoid multiple reader on same command)
  306. foreach (T item in result)
  307. {
  308. //load read roles
  309. if (item.ReadPermissionId > 0 && item.ReadAccessType != MenuAccesstype.Public)
  310. item.ReadRolenames = new PermissionProvider().GetPermissionRoles(item.ReadPermissionId);
  311. //load write roles
  312. if (item.WritePermissionId > 0 && item.WriteAccessType != MenuAccesstype.Public)
  313. item.WriteRolenames = new PermissionProvider().GetPermissionRoles(item.WritePermissionId);
  314. }
  315. if (this.CheckUserContext)
  316. {
  317. result.RemoveAll(new PermissionProvider().IsItemNotAllowed);
  318. if (this.WriteMode)
  319. result.RemoveAll(new PermissionProvider().IsItemNotAllowedForWrite);
  320. //check parent sections
  321. loadSectionsRoles(sectionsList);
  322. sectionsList.RemoveAll(new PermissionProvider().IsItemNotAllowed);
  323. if (this.WriteMode)
  324. sectionsList.RemoveAll(new PermissionProvider().IsItemNotAllowedForWrite);
  325. //remove items for not allowed sections
  326. result.RemoveAll(new SectionPredicate(sectionsList).IsItemNotInSection);
  327. //check parent categories
  328. loadCategoriesRoles(categoriesList);
  329. categoriesList.RemoveAll(new PermissionProvider().IsItemNotAllowed);
  330. if (this.WriteMode)
  331. categoriesList.RemoveAll(new PermissionProvider().IsItemNotAllowedForWrite);
  332. //remove items for not allowed categories
  333. result.RemoveAll(new CategoryPredicate(categoriesList).IsItemNotInCategory);
  334. }
  335. //culture specifics
  336. foreach (T item in result)
  337. {
  338. getCultureSpecific(item, myRd, myCmd, myProv);
  339. }
  340. }
  341. finally
  342. {
  343. myConn.Dispose();
  344. }
  345. return result;
  346. }
  347. public override T GetByKey(int id)
  348. {
  349. T result = new T();
  350. List<T> resultList = new List<T>();
  351. F filter = new F();
  352. filter.Id = id==0 ? -1 : id;
  353. resultList = GetByFilter(filter, "");
  354. if (resultList.Count > 0)
  355. result = resultList[0];
  356. return result;
  357. }
  358. public override int Update(T theObj)
  359. {
  360. DbProviderFactory myProv = Database.ProviderFactory;
  361. DbTransaction myTrans = null;
  362. DbConnection myConn = myProv.CreateConnection();
  363. DbCommand myCmd = myConn.CreateCommand();
  364. string sSql;
  365. int result = 0;
  366. if (theObj.Ordering == 0)
  367. {
  368. theObj.Ordering = this.GetNextOrdering();
  369. }
  370. theObj.DateUpdated = DateTime.Now;
  371. theObj.UserUpdated = PgnUserCurrent.UserName;
  372. if (theObj.DateInserted == DateTime.MinValue)
  373. theObj.DateInserted = DateTime.Now;
  374. try
  375. {
  376. //fill ReadPermissionId and WritePermissionId before trans
  377. new PermissionProvider().UpdatePermissionObj(theObj);
  378. myConn.ConnectionString = Database.ConnString;
  379. myConn.Open();
  380. myCmd.Connection = myConn;
  381. myTrans = myConn.BeginTransaction();
  382. myCmd.Transaction = myTrans;
  383. sSql = "UPDATE [" + this.TableName + "] "
  384. + " SET ItemType=@ItemType, CategoryId=@CategoryId, Enabled=@Enabled, "
  385. + " Alias=@Alias, Ordering=@Ordering, DefaultImageName=@DefaultImageName, "
  386. + " [DateInserted]=@DateInserted, [UserInserted]=@UserInserted, "
  387. + " [DateUpdated]=@DateUpdated, [UserUpdated]=@UserUpdated, "
  388. + " ItemDate=@ItemDate, ValidFrom=@ValidFrom, ValidTo=@ValidTo, "
  389. + " CustomBool1=@CustomBool1, CustomBool2=@CustomBool2, CustomBool3=@CustomBool3, "
  390. + " CustomDate1=@CustomDate1, CustomDate2=@CustomDate2, CustomDate3=@CustomDate3, "
  391. + " CustomDecimal1=@CustomDecimal1, CustomDecimal2=@CustomDecimal2, CustomDecimal3=@CustomDecimal3, "
  392. + " CustomInt1=@CustomInt1, CustomInt2=@CustomInt2, CustomInt3=@CustomInt3, "
  393. + " CustomString1=@CustomString1, CustomString2=@CustomString2, CustomString3=@CustomString3, "
  394. + " [ItemParams]=@ItemParams, AccessType=@AccessType, PermissionId=@PermissionId, "
  395. + " [AccessCode]=@AccessCode, AccessLevel=@AccessLevel, CommentsGroupId=@CommentsGroupId, "
  396. + " WriteAccessType=@WriteAccessType, WritePermissionId=@WritePermissionId, [WriteAccessCode]=@WriteAccessCode, "
  397. + " WriteAccessLevel=@WriteAccessLevel, ThreadId=@ThreadId "
  398. + " WHERE Id = @Id";
  399. myCmd.CommandText = Database.ParseSql(sSql);
  400. myCmd.Parameters.Add(Database.Parameter(myProv, "Id", theObj.Id));
  401. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemType", theObj.ItemTypeName));
  402. myCmd.Parameters.Add(Database.Parameter(myProv, "CategoryId", theObj.CategoryId));
  403. myCmd.Parameters.Add(Database.Parameter(myProv, "Enabled", theObj.Enabled));
  404. myCmd.Parameters.Add(Database.Parameter(myProv, "Alias", theObj.Alias));
  405. myCmd.Parameters.Add(Database.Parameter(myProv, "Ordering", theObj.Ordering));
  406. myCmd.Parameters.Add(Database.Parameter(myProv, "DefaultImageName", theObj.DefaultImageName));
  407. myCmd.Parameters.Add(Database.Parameter(myProv, "DateInserted", theObj.DateInserted));
  408. myCmd.Parameters.Add(Database.Parameter(myProv, "UserInserted", theObj.UserInserted));
  409. myCmd.Parameters.Add(Database.Parameter(myProv, "DateUpdated", theObj.DateUpdated));
  410. myCmd.Parameters.Add(Database.Parameter(myProv, "UserUpdated", theObj.UserUpdated));
  411. if (theObj.ItemDate == DateTime.MinValue)
  412. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemDate", DBNull.Value));
  413. else
  414. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemDate", theObj.ItemDate));
  415. if (theObj.ValidFrom == DateTime.MinValue)
  416. myCmd.Parameters.Add(Database.Parameter(myProv, "ValidFrom", DBNull.Value));
  417. else
  418. myCmd.Parameters.Add(Database.Parameter(myProv, "ValidFrom", theObj.ValidFrom));
  419. if (theObj.ValidTo == DateTime.MinValue)
  420. myCmd.Parameters.Add(Database.Parameter(myProv, "ValidTo", DBNull.Value));
  421. else
  422. myCmd.Parameters.Add(Database.Parameter(myProv, "ValidTo", theObj.ValidTo));
  423. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomBool1", theObj.CustomBool1));
  424. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomBool2", theObj.CustomBool2));
  425. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomBool3", theObj.CustomBool3));
  426. if (theObj.CustomDate1 == DateTime.MinValue)
  427. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate1", DBNull.Value));
  428. else
  429. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate1", theObj.CustomDate1));
  430. if (theObj.CustomDate2 == DateTime.MinValue)
  431. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate2", DBNull.Value));
  432. else
  433. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate2", theObj.CustomDate2));
  434. if (theObj.CustomDate3 == DateTime.MinValue)
  435. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate3", DBNull.Value));
  436. else
  437. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate3", theObj.CustomDate3));
  438. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDecimal1", theObj.CustomDecimal1));
  439. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDecimal2", theObj.CustomDecimal2));
  440. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDecimal3", theObj.CustomDecimal3));
  441. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomInt1", theObj.CustomInt1));
  442. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomInt2", theObj.CustomInt2));
  443. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomInt3", theObj.CustomInt3));
  444. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString1", theObj.CustomString1));
  445. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString2", theObj.CustomString2));
  446. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString3", theObj.CustomString3));
  447. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemParams", theObj.ItemParams));
  448. //read permissions
  449. myCmd.Parameters.Add(Database.Parameter(myProv, "AccessType", theObj.ReadAccessType));
  450. myCmd.Parameters.Add(Database.Parameter(myProv, "PermissionId", theObj.ReadPermissionId));
  451. myCmd.Parameters.Add(Database.Parameter(myProv, "AccessCode", theObj.ReadAccessCode));
  452. myCmd.Parameters.Add(Database.Parameter(myProv, "AccessLevel", theObj.ReadAccessLevel));
  453. //write permissions
  454. myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessType", theObj.WriteAccessType));
  455. myCmd.Parameters.Add(Database.Parameter(myProv, "WritePermissionId", theObj.WritePermissionId));
  456. myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessCode", theObj.WriteAccessCode));
  457. myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessLevel", theObj.WriteAccessLevel));
  458. myCmd.Parameters.Add(Database.Parameter(myProv, "CommentsGroupId", theObj.CommentsGroupId));
  459. myCmd.Parameters.Add(Database.Parameter(myProv, "ThreadId", theObj.ThreadId));
  460. result = myCmd.ExecuteNonQuery();
  461. updateCultureText(theObj, myCmd, myProv);
  462. myTrans.Commit();
  463. }
  464. catch (Exception e)
  465. {
  466. myTrans.Rollback();
  467. throw e;
  468. }
  469. finally
  470. {
  471. myTrans.Dispose();
  472. myConn.Dispose();
  473. }
  474. return result;
  475. }
  476. public override T Insert(T newObj)
  477. {
  478. DbProviderFactory myProv = Database.ProviderFactory;
  479. DbTransaction myTrans = null;
  480. DbConnection myConn = myProv.CreateConnection();
  481. DbCommand myCmd = myConn.CreateCommand();
  482. string sSql;
  483. T result = new T();
  484. if (!checkMaxItemsAllowed(newObj))
  485. throw new CustomException(
  486. MaxItemsException, CustomExceptionSeverity.Info, CustomExceptionLogLevel.Log);
  487. try
  488. {
  489. //create read/write permission
  490. new PermissionProvider().CreatePermissionObj(newObj);
  491. myConn.ConnectionString = Database.ConnString;
  492. myConn.Open();
  493. myCmd.Connection = myConn;
  494. myTrans = myConn.BeginTransaction();
  495. myCmd.Transaction = myTrans;
  496. result = newObj;
  497. result.Id = base.GetNextId();
  498. if (result.ThreadId == 0)
  499. result.ThreadId = result.Id;
  500. result.Ordering = base.GetNextOrdering();
  501. result.DateInserted = DateTime.Now;
  502. result.UserInserted = PgnUserCurrent.UserName;
  503. result.DateUpdated = DateTime.Now;
  504. result.UserUpdated = PgnUserCurrent.UserName;
  505. sSql = "INSERT INTO [" + this.TableName + "](Id, ItemType, CategoryId, Enabled, "
  506. + " Alias, Ordering, DefaultImageName, "
  507. + " DateInserted, UserInserted, DateUpdated, UserUpdated, "
  508. + " ItemDate, ValidFrom, ValidTo, "
  509. + " CustomBool1, CustomBool2, CustomBool3, "
  510. + " CustomDate1, CustomDate2, CustomDate3, "
  511. + " CustomDecimal1, CustomDecimal2, CustomDecimal3, "
  512. + " CustomInt1, CustomInt2, CustomInt3, "
  513. + " CustomString1, CustomString2, CustomString3, "
  514. + " ItemParams, AccessType, PermissionId, AccessCode, AccessLevel, CommentsGroupId, "
  515. + " WriteAccessType, WritePermissionId, WriteAccessCode, WriteAccessLevel, "
  516. + " ThreadId) "
  517. + " VALUES(@Id, @ItemType, @CategoryId, @Enabled, "
  518. + " @Alias, @Ordering, @DefaultImageName, "
  519. + " @DateInserted, @UserInserted, @DateUpdated, @UserUpdated, "
  520. + " @ItemDate, @ValidFrom, @ValidTo, "
  521. + " @CustomBool1, @CustomBool2, @CustomBool3, "
  522. + " @CustomDate1, @CustomDate2, @CustomDate3, "
  523. + " @CustomDecimal1, @CustomDecimal2, @CustomDecimal3, "
  524. + " @CustomInt1, @CustomInt2, @CustomInt3, "
  525. + " @CustomString1, @CustomString2, @CustomString3, "
  526. + " @ItemParams, @AccessType, @PermissionId, @AccessCode, @AccessLevel, @CommentsGroupId, "
  527. + " @WriteAccessType, @WritePermissionId, @WriteAccessCode, @WriteAccessLevel, "
  528. + " @ThreadId)";
  529. myCmd.CommandText = Database.ParseSql(sSql);
  530. myCmd.Parameters.Add(Database.Parameter(myProv, "Id", result.Id));
  531. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemType", result.ItemTypeName));
  532. myCmd.Parameters.Add(Database.Parameter(myProv, "CategoryId", result.CategoryId));
  533. myCmd.Parameters.Add(Database.Parameter(myProv, "Enabled", result.Enabled));
  534. myCmd.Parameters.Add(Database.Parameter(myProv, "Alias", result.Alias));
  535. myCmd.Parameters.Add(Database.Parameter(myProv, "Ordering", result.Ordering));
  536. myCmd.Parameters.Add(Database.Parameter(myProv, "DefaultImageName", result.DefaultImageName));
  537. myCmd.Parameters.Add(Database.Parameter(myProv, "DateInserted", result.DateInserted));
  538. myCmd.Parameters.Add(Database.Parameter(myProv, "UserInserted", result.UserInserted));
  539. myCmd.Parameters.Add(Database.Parameter(myProv, "DateUpdated", result.DateUpdated));
  540. myCmd.Parameters.Add(Database.Parameter(myProv, "UserUpdated", result.UserUpdated));
  541. if (result.ItemDate == DateTime.MinValue)
  542. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemDate", DBNull.Value));
  543. else
  544. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemDate", result.ItemDate));
  545. if (result.ValidFrom == DateTime.MinValue)
  546. myCmd.Parameters.Add(Database.Parameter(myProv, "ValidFrom", DBNull.Value));
  547. else
  548. myCmd.Parameters.Add(Database.Parameter(myProv, "ValidFrom", result.ValidFrom));
  549. if (result.ValidTo == DateTime.MinValue)
  550. myCmd.Parameters.Add(Database.Parameter(myProv, "ValidTo", DBNull.Value));
  551. else
  552. myCmd.Parameters.Add(Database.Parameter(myProv, "ValidTo", result.ValidTo));
  553. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomBool1", result.CustomBool1));
  554. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomBool2", result.CustomBool2));
  555. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomBool3", result.CustomBool3));
  556. if (result.CustomDate1 == DateTime.MinValue)
  557. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate1", DBNull.Value));
  558. else
  559. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate1", result.CustomDate1));
  560. if (result.CustomDate2 == DateTime.MinValue)
  561. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate2", DBNull.Value));
  562. else
  563. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate2", result.CustomDate2));
  564. if (result.CustomDate3 == DateTime.MinValue)
  565. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate3", DBNull.Value));
  566. else
  567. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDate3", result.CustomDate3));
  568. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDecimal1", result.CustomDecimal1));
  569. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDecimal2", result.CustomDecimal2));
  570. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomDecimal3", result.CustomDecimal3));
  571. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomInt1", result.CustomInt1));
  572. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomInt2", result.CustomInt2));
  573. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomInt3", result.CustomInt3));
  574. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString1", result.CustomString1));
  575. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString2", result.CustomString2));
  576. myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString3", result.CustomString3));
  577. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemParams", result.ItemParams));
  578. //read permissions
  579. myCmd.Parameters.Add(Database.Parameter(myProv, "AccessType", (int)result.ReadAccessType));
  580. myCmd.Parameters.Add(Database.Parameter(myProv, "PermissionId", result.ReadPermissionId));
  581. myCmd.Parameters.Add(Database.Parameter(myProv, "AccessCode", (string)result.ReadAccessCode));
  582. myCmd.Parameters.Add(Database.Parameter(myProv, "AccessLevel", (int)result.ReadAccessLevel));
  583. //write permissions
  584. myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessType", (int)result.WriteAccessType));
  585. myCmd.Parameters.Add(Database.Parameter(myProv, "WritePermissionId", result.WritePermissionId));
  586. myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessCode", (string)result.WriteAccessCode));
  587. myCmd.Parameters.Add(Database.Parameter(myProv, "WriteAccessLevel", (int)result.WriteAccessLevel));
  588. myCmd.Parameters.Add(Database.Parameter(myProv, "CommentsGroupId", result.CommentsGroupId));
  589. myCmd.Parameters.Add(Database.Parameter(myProv, "ThreadId", result.ThreadId));
  590. myCmd.ExecuteNonQuery();
  591. updateCultureText(result, myCmd, myProv);
  592. myTrans.Commit();
  593. }
  594. catch (Exception e)
  595. {
  596. myTrans.Rollback();
  597. throw e;
  598. }
  599. finally
  600. {
  601. myTrans.Dispose();
  602. myConn.Dispose();
  603. }
  604. return result;
  605. }
  606. public override int DeleteById(int id)
  607. {
  608. DbProviderFactory myProv = Database.ProviderFactory;
  609. DbConnection myConn = myProv.CreateConnection();
  610. DbCommand myCmd = myProv.CreateCommand();
  611. int res = 0;
  612. try
  613. {
  614. T currObj = this.GetByKey(id);
  615. var list = new List<Item>();
  616. if (currObj.IsThreadRoot)
  617. list = currObj.ThreadList;
  618. else
  619. list.Add(currObj);
  620. myConn.ConnectionString = Database.ConnString;
  621. myConn.Open();
  622. myCmd.Connection = myConn;
  623. foreach (var item in list)
  624. {
  625. deleteObj(item, myProv, myConn, myCmd);
  626. }
  627. }
  628. catch (Exception e)
  629. {
  630. throw e;
  631. }
  632. finally
  633. {
  634. myConn.Dispose();
  635. }
  636. return res;
  637. }
  638. private void deleteObj(Item currObj,
  639. DbProviderFactory myProv, DbConnection myConn, DbCommand myCmd)
  640. {
  641. string sSql;
  642. currObj.DeleteImages();
  643. currObj.DeleteFiles();
  644. new PermissionProvider().RemovePermissionById(currObj.ReadPermissionId);
  645. new PermissionProvider().RemovePermissionById(currObj.WritePermissionId);
  646. //myTrans = myConn.BeginTransaction();
  647. //myCmd.Transaction = myTrans;
  648. sSql = "DELETE FROM [" + this.TableName + "] WHERE Id = @Id ";
  649. myCmd.CommandText = Database.ParseSql(sSql);
  650. myCmd.Parameters.Clear();
  651. myCmd.Parameters.Add(Database.Parameter(myProv, "Id", currObj.Id));
  652. myCmd.ExecuteNonQuery();
  653. sSql = "DELETE FROM [" + this.TableName + "_Culture] WHERE ItemId = @ItemId ";
  654. myCmd.CommandText = Database.ParseSql(sSql);
  655. myCmd.Parameters.Clear();
  656. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemId", currObj.Id));
  657. myCmd.ExecuteNonQuery();
  658. }
  659. private class SectionPredicate
  660. {
  661. private List<Section> list = null;
  662. public SectionPredicate(List<Section> list)
  663. {
  664. this.list = list;
  665. }
  666. public bool IsItemNotInSection(T item)
  667. {
  668. foreach (var s in list)
  669. if (s.Id == item.SectionId) return false;
  670. return true;
  671. }
  672. }
  673. private void loadSectionsRoles(List<Section> list)
  674. {
  675. foreach (var item in list)
  676. {
  677. //load read roles
  678. if (item.ReadPermissionId > 0 && item.ReadAccessType != MenuAccesstype.Public)
  679. item.ReadRolenames = new PermissionProvider().GetPermissionRoles(item.ReadPermissionId);
  680. //load write roles
  681. if (item.WritePermissionId > 0 && item.WriteAccessType != MenuAccesstype.Public)
  682. item.WriteRolenames = new PermissionProvider().GetPermissionRoles(item.WritePermissionId);
  683. }
  684. }
  685. private class CategoryPredicate
  686. {
  687. private List<Category> list = null;
  688. public CategoryPredicate(List<Category> list)
  689. {
  690. this.list = list;
  691. }
  692. public bool IsItemNotInCategory(T item)
  693. {
  694. foreach (var s in list)
  695. if (s.Id == item.CategoryId) return false;
  696. return true;
  697. }
  698. }
  699. private void loadCategoriesRoles(List<Category> list)
  700. {
  701. foreach (var item in list)
  702. {
  703. //load read roles
  704. if (item.ReadPermissionId > 0 && item.ReadAccessType != MenuAccesstype.Public)
  705. item.ReadRolenames = new PermissionProvider().GetPermissionRoles(item.ReadPermissionId);
  706. //load write roles
  707. if (item.WritePermissionId > 0 && item.WriteAccessType != MenuAccesstype.Public)
  708. item.WriteRolenames = new PermissionProvider().GetPermissionRoles(item.WritePermissionId);
  709. }
  710. }
  711. private void fillSection(Section result, DbDataReader myRd)
  712. {
  713. if (!Convert.IsDBNull(myRd["SectionId"]))
  714. result.Id = (int)myRd["SectionId"];
  715. //read permissions
  716. if (!Convert.IsDBNull(myRd["SectAccessType"]))
  717. result.ReadAccessType = (MenuAccesstype)int.Parse(myRd["SectAccessType"].ToString());
  718. if (!Convert.IsDBNull(myRd["SectPermissionId"]))
  719. result.ReadPermissionId = (int)myRd["SectPermissionId"];
  720. if (!Convert.IsDBNull(myRd["SectAccessCode"]))
  721. result.ReadAccessCode = (string)myRd["SectAccessCode"];
  722. if (!Convert.IsDBNull(myRd["SectAccessLevel"]))
  723. result.ReadAccessLevel = (int)myRd["SectAccessLevel"];
  724. //write permissions
  725. if (!Convert.IsDBNull(myRd["SectWriteAccessType"]))
  726. result.WriteAccessType = (MenuAccesstype)int.Parse(myRd["SectWriteAccessType"].ToString());
  727. if (!Convert.IsDBNull(myRd["SectWritePermissionId"]))
  728. result.WritePermissionId = (int)myRd["SectWritePermissionId"];
  729. if (!Convert.IsDBNull(myRd["SectWriteAccessCode"]))
  730. result.WriteAccessCode = (string)myRd["SectWriteAccessCode"];
  731. if (!Convert.IsDBNull(myRd["SectWriteAccessLevel"]))
  732. result.WriteAccessLevel = (int)myRd["SectWriteAccessLevel"];
  733. }
  734. private void fillCategory(Category result, DbDataReader myRd)
  735. {
  736. if (!Convert.IsDBNull(myRd["CategoryId"]))
  737. result.Id = (int)myRd["CategoryId"];
  738. //read permissions
  739. if (!Convert.IsDBNull(myRd["CategAccessType"]))
  740. result.ReadAccessType = (MenuAccesstype)int.Parse(myRd["CategAccessType"].ToString());
  741. if (!Convert.IsDBNull(myRd["CategPermissionId"]))
  742. result.ReadPermissionId = (int)myRd["CategPermissionId"];
  743. if (!Convert.IsDBNull(myRd["CategAccessCode"]))
  744. result.ReadAccessCode = (string)myRd["CategAccessCode"];
  745. if (!Convert.IsDBNull(myRd["CategAccessLevel"]))
  746. result.ReadAccessLevel = (int)myRd["CategAccessLevel"];
  747. //write permissions
  748. if (!Convert.IsDBNull(myRd["CategWriteAccessType"]))
  749. result.WriteAccessType = (MenuAccesstype)int.Parse(myRd["CategWriteAccessType"].ToString());
  750. if (!Convert.IsDBNull(myRd["CategWritePermissionId"]))
  751. result.WritePermissionId = (int)myRd["CategWritePermissionId"];
  752. if (!Convert.IsDBNull(myRd["CategWriteAccessCode"]))
  753. result.WriteAccessCode = (string)myRd["CategWriteAccessCode"];
  754. if (!Convert.IsDBNull(myRd["CategWriteAccessLevel"]))
  755. result.WriteAccessLevel = (int)myRd["CategWriteAccessLevel"];
  756. }
  757. protected override void FillObject(T result, DbDataReader myRd)
  758. {
  759. if (!Convert.IsDBNull(myRd["Id"]))
  760. result.Id = (int)myRd["Id"];
  761. if (!Convert.IsDBNull(myRd["ItemType"]))
  762. result.ItemTypeName = (string)myRd["ItemType"];
  763. if (!Convert.IsDBNull(myRd["CategoryId"]))
  764. result.CategoryId = (int)myRd["CategoryId"];
  765. if (!Convert.IsDBNull(myRd["SectionId"]))
  766. result.SectionId = (int)myRd["SectionId"];
  767. if (!Convert.IsDBNull(myRd["Enabled"]))
  768. result.Enabled = (bool)myRd["Enabled"];
  769. if (!Convert.IsDBNull(myRd["Ordering"]))
  770. result.Ordering = (int)myRd["Ordering"];
  771. if (!Convert.IsDBNull(myRd["Alias"]))
  772. result.Alias = (string)myRd["Alias"];
  773. if (!Convert.IsDBNull(myRd["DefaultImageName"]))
  774. result.DefaultImageName = myRd["DefaultImageName"].ToString();
  775. if (!Convert.IsDBNull(myRd["DateInserted"]))
  776. result.DateInserted = (DateTime)myRd["DateInserted"];
  777. if (!Convert.IsDBNull(myRd["UserInserted"]))
  778. result.UserInserted = (string)myRd["UserInserted"];
  779. if (!Convert.IsDBNull(myRd["DateUpdated"]))
  780. result.DateUpdated = (DateTime)myRd["DateUpdated"];
  781. if (!Convert.IsDBNull(myRd["UserUpdated"]))
  782. result.UserUpdated = (string)myRd["UserUpdated"];
  783. if (!Convert.IsDBNull(myRd["ItemDate"]))
  784. result.ItemDate = (DateTime)myRd["ItemDate"];
  785. if (!Convert.IsDBNull(myRd["ValidFrom"]))
  786. result.ValidFrom = (DateTime)myRd["ValidFrom"];
  787. if (!Convert.IsDBNull(myRd["ValidTo"]))
  788. result.ValidTo = (DateTime)myRd["ValidTo"];
  789. //custom fields
  790. if (!Convert.IsDBNull(myRd["CustomBool1"]))
  791. result.CustomBool1 = (bool)myRd["CustomBool1"];
  792. if (!Convert.IsDBNull(myRd["CustomBool2"]))
  793. result.CustomBool2 = (bool)myRd["CustomBool2"];
  794. if (!Convert.IsDBNull(myRd["CustomBool3"]))
  795. result.CustomBool3 = (bool)myRd["CustomBool3"];
  796. if (!Convert.IsDBNull(myRd["CustomDate1"]))
  797. result.CustomDate1 = (DateTime)myRd["CustomDate1"];
  798. if (!Convert.IsDBNull(myRd["CustomDate2"]))
  799. result.CustomDate2 = (DateTime)myRd["CustomDate2"];
  800. if (!Convert.IsDBNull(myRd["CustomDate3"]))
  801. result.CustomDate3 = (DateTime)myRd["CustomDate3"];
  802. if (!Convert.IsDBNull(myRd["CustomDecimal1"]))
  803. result.CustomDecimal1 = (decimal)myRd["CustomDecimal1"];
  804. if (!Convert.IsDBNull(myRd["CustomDecimal2"]))
  805. result.CustomDecimal2 = (decimal)myRd["CustomDecimal2"];
  806. if (!Convert.IsDBNull(myRd["CustomDecimal3"]))
  807. result.CustomDecimal3 = (decimal)myRd["CustomDecimal3"];
  808. if (!Convert.IsDBNull(myRd["CustomInt1"]))
  809. result.CustomInt1 = (int)myRd["CustomInt1"];
  810. if (!Convert.IsDBNull(myRd["CustomInt2"]))
  811. result.CustomInt2 = (int)myRd["CustomInt2"];
  812. if (!Convert.IsDBNull(myRd["CustomInt3"]))
  813. result.CustomInt3 = (int)myRd["CustomInt3"];
  814. if (!Convert.IsDBNull(myRd["CustomString1"]))
  815. result.CustomString1 = (string)myRd["CustomString1"];
  816. if (!Convert.IsDBNull(myRd["CustomString2"]))
  817. result.CustomString2 = (string)myRd["CustomString2"];
  818. if (!Convert.IsDBNull(myRd["CustomString3"]))
  819. result.CustomString3 = (string)myRd["CustomString3"];
  820. if (!Convert.IsDBNull(myRd["ItemParams"]))
  821. result.ItemParams = (string)myRd["ItemParams"];
  822. //read permissions
  823. if (!Convert.IsDBNull(myRd["AccessType"]))
  824. result.ReadAccessType = (MenuAccesstype)int.Parse(myRd["AccessType"].ToString());
  825. if (!Convert.IsDBNull(myRd["PermissionId"]))
  826. result.ReadPermissionId = (int)myRd["PermissionId"];
  827. if (!Convert.IsDBNull(myRd["AccessCode"]))
  828. result.ReadAccessCode = (string)myRd["AccessCode"];
  829. if (!Convert.IsDBNull(myRd["AccessLevel"]))
  830. result.ReadAccessLevel = (int)myRd["AccessLevel"];
  831. //write permissions
  832. if (!Convert.IsDBNull(myRd["WriteAccessType"]))
  833. result.WriteAccessType = (MenuAccesstype)int.Parse(myRd["WriteAccessType"].ToString());
  834. if (!Convert.IsDBNull(myRd["WritePermissionId"]))
  835. result.WritePermissionId = (int)myRd["WritePermissionId"];
  836. if (!Convert.IsDBNull(myRd["WriteAccessCode"]))
  837. result.WriteAccessCode = (string)myRd["WriteAccessCode"];
  838. if (!Convert.IsDBNull(myRd["WriteAccessLevel"]))
  839. result.WriteAccessLevel = (int)myRd["WriteAccessLevel"];
  840. if (!Convert.IsDBNull(myRd["ThreadId"]))
  841. result.ThreadId = (int)myRd["ThreadId"];
  842. }
  843. protected override int GetPreviousRecordInOrder(int ordering, int currentRecordId)
  844. {
  845. DbProviderFactory myProv = Database.ProviderFactory;
  846. DbConnection myConn = myProv.CreateConnection();
  847. DbDataReader myRd = null;
  848. DbCommand myCmd = myConn.CreateCommand();
  849. string sSql;
  850. int result = currentRecordId;
  851. var manCateg = new CategoriesManager();
  852. try
  853. {
  854. var o1 = new PigeonCms.Item();
  855. o1 = this.GetByKey(currentRecordId);
  856. var cat1 = new PigeonCms.Category();
  857. cat1 = manCateg.GetByKey(o1.CategoryId);
  858. myConn.ConnectionString = Database.ConnString;
  859. myConn.Open();
  860. myCmd.Connection = myConn;
  861. sSql = "SELECT TOP 1 t.Id "
  862. + " FROM [" + this.TableName + "] t "
  863. + " LEFT JOIN #__categories categ ON t.CategoryId = categ.Id "
  864. + " WHERE t.Ordering < @Ordering "
  865. + " AND t.CategoryId = @CategoryId "
  866. + " AND categ.SectionId = @SectionId "
  867. + " ORDER BY t.Ordering DESC ";
  868. myCmd.CommandText = Database.ParseSql(sSql);
  869. myCmd.Parameters.Add(Database.Parameter(myProv, "Ordering", ordering));
  870. myCmd.Parameters.Add(Database.Parameter(myProv, "CategoryId", o1.CategoryId));
  871. myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", cat1.SectionId));
  872. myRd = myCmd.ExecuteReader();
  873. if (myRd.Read())
  874. {
  875. if (myRd[0] != DBNull.Value)
  876. {
  877. result = (int)myRd[0];
  878. }
  879. }
  880. myRd.Close();
  881. //se nn trovo un record prendo quello precedente per chiave (per init tabella)
  882. if (result == 0)
  883. {
  884. sSql = "SELECT TOP 1 [Id] FROM " + TableName
  885. + " WHERE [" + KeyFieldName + "] < @currentRecordId ORDER BY t.Ordering ASC ";
  886. myCmd.CommandText = Database.ParseSql(sSql);
  887. myCmd.Parameters.Add(Database.Parameter(myProv, "currentRecordId", currentRecordId));
  888. myRd = myCmd.ExecuteReader();
  889. if (myRd.Read())
  890. {
  891. if (myRd[0] != DBNull.Value)
  892. {
  893. result = (int)myRd[0];
  894. }
  895. }
  896. myRd.Close();
  897. }
  898. }
  899. finally
  900. {
  901. myConn.Dispose();
  902. }
  903. return result;
  904. }
  905. protected override int GetNextRecordInOrder(int ordering, int currentRecordId)
  906. {
  907. DbProviderFactory myProv = Database.ProviderFactory;
  908. DbConnection myConn = myProv.CreateConnection();
  909. DbDataReader myRd = null;
  910. DbCommand myCmd = myConn.CreateCommand();
  911. string sSql;
  912. int result = currentRecordId;
  913. var manCateg = new CategoriesManager();
  914. try
  915. {
  916. var o1 = new PigeonCms.Item();
  917. o1 = this.GetByKey(currentRecordId);
  918. var cat1 = new PigeonCms.Category();
  919. cat1 = manCateg.GetByKey(o1.CategoryId);
  920. myConn.ConnectionString = Database.ConnString;
  921. myConn.Open();
  922. myCmd.Connection = myConn;
  923. sSql = "SELECT TOP 1 t.Id "
  924. + " FROM [" + this.TableName + "] t "
  925. + " LEFT JOIN #__categories categ ON t.CategoryId = categ.Id "
  926. + " WHERE t.Ordering > @Ordering "
  927. + " AND t.CategoryId = @CategoryId "
  928. + " AND categ.SectionId = @SectionId "
  929. + " ORDER BY t.Ordering ASC ";
  930. myCmd.CommandText = Database.ParseSql(sSql);
  931. myCmd.Parameters.Add(Database.Parameter(myProv, "Ordering", ordering));
  932. myCmd.Parameters.Add(Database.Parameter(myProv, "CategoryId", o1.CategoryId));
  933. myCmd.Parameters.Add(Database.Parameter(myProv, "SectionId", cat1.SectionId));
  934. myRd = myCmd.ExecuteReader();
  935. if (myRd.Read())
  936. {
  937. if (myRd[0] != DBNull.Value)
  938. {
  939. result = (int)myRd[0];
  940. }
  941. }
  942. myRd.Close();
  943. //se nn trovo un record prendo quello successivo per chiave (per init tabella)
  944. if (result == currentRecordId)
  945. {
  946. sSql = "SELECT TOP 1 [Id] FROM " + TableName
  947. + " WHERE [" + KeyFieldName + "] > @currentRecordId ORDER BY Ordering, [" + KeyFieldName + "] ";
  948. myCmd.CommandText = Database.ParseSql(sSql);
  949. myCmd.Parameters.Add(Database.Parameter(myProv, "currentRecordId", currentRecordId));
  950. myRd = myCmd.ExecuteReader();
  951. if (myRd.Read())
  952. {
  953. if (myRd[0] != DBNull.Value)
  954. {
  955. result = (int)myRd[0];
  956. }
  957. }
  958. myRd.Close();
  959. }
  960. }
  961. finally
  962. {
  963. myConn.Dispose();
  964. }
  965. return result;
  966. }
  967. private bool checkMaxItemsAllowed(T item)
  968. {
  969. bool res = true;
  970. int maxItems = item.Category.Section.MaxItems;
  971. if (maxItems > 0 &&
  972. item.Category.Section.NumOfItems >= maxItems)
  973. res = false;
  974. return res;
  975. }
  976. private void getCultureSpecific(T result, DbDataReader myRd,
  977. DbCommand myCmd, DbProviderFactory myProv)
  978. {
  979. string sSql;
  980. //culture specific
  981. sSql = "SELECT CultureName, ItemId, Title, Description "
  982. + " FROM [" + this.TableName + "_culture] t "
  983. + " WHERE ItemId = @ItemId ";
  984. //CustomString1, CustomString2, CustomString3, CustomText1, CustomText2, CustomText3
  985. myCmd.CommandText = Database.ParseSql(sSql);
  986. myCmd.Parameters.Clear();
  987. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemId", result.Id));
  988. myRd = myCmd.ExecuteReader();
  989. while (myRd.Read())
  990. {
  991. if (!Convert.IsDBNull(myRd["Title"]))
  992. result.TitleTranslations.Add((string)myRd["cultureName"], (string)myRd["Title"]);
  993. if (!Convert.IsDBNull(myRd["Description"]))
  994. result.DescriptionTranslations.Add((string)myRd["cultureName"], (string)myRd["Description"]);
  995. //custom fields
  996. //if (!Convert.IsDBNull(myRd["CustomString1"]))
  997. // result.CustomString1Translations.Add((string)myRd["cultureName"], (string)myRd["CustomString1"]);
  998. //if (!Convert.IsDBNull(myRd["CustomString2"]))
  999. // result.CustomString2Translations.Add((string)myRd["cultureName"], (string)myRd["CustomString2"]);
  1000. //if (!Convert.IsDBNull(myRd["CustomString3"]))
  1001. // result.CustomString3Translations.Add((string)myRd["cultureName"], (string)myRd["CustomString3"]);
  1002. //if (!Convert.IsDBNull(myRd["CustomText1"]))
  1003. // result.CustomText1Translations.Add((string)myRd["cultureName"], (string)myRd["CustomText1"]);
  1004. //if (!Convert.IsDBNull(myRd["CustomText2"]))
  1005. // result.CustomText2Translations.Add((string)myRd["cultureName"], (string)myRd["CustomText2"]);
  1006. //if (!Convert.IsDBNull(myRd["CustomText3"]))
  1007. // result.CustomText3Translations.Add((string)myRd["cultureName"], (string)myRd["CustomText3"]);
  1008. }
  1009. myRd.Close();
  1010. }
  1011. private void updateCultureText(T theObj, DbCommand myCmd, DbProviderFactory myProv)
  1012. {
  1013. foreach (KeyValuePair<string, string> item in theObj.TitleTranslations)
  1014. {
  1015. string sSql = "";
  1016. string descriptionValue = "";
  1017. //string customString1 = "";
  1018. //string customString2 = "";
  1019. //string customString3 = "";
  1020. //string customText1 = "";
  1021. //string customText2 = "";
  1022. //string customText3 = "";
  1023. theObj.DescriptionTranslations.TryGetValue(item.Key, out descriptionValue);
  1024. //theObj.CustomString1Translations.TryGetValue(item.Key, out customString1);
  1025. //theObj.CustomString2Translations.TryGetValue(item.Key, out customString2);
  1026. //theObj.CustomString3Translations.TryGetValue(item.Key, out customString3);
  1027. //theObj.CustomText1Translations.TryGetValue(item.Key, out customText1);
  1028. //theObj.CustomText2Translations.TryGetValue(item.Key, out customText2);
  1029. //theObj.CustomText3Translations.TryGetValue(item.Key, out customText3);
  1030. //delete previous entry (if exists)
  1031. sSql = "DELETE FROM [" + this.TableName + "_culture] WHERE CultureName=@CultureName AND ItemId=@ItemId ";
  1032. myCmd.CommandText = Database.ParseSql(sSql);
  1033. myCmd.Parameters.Clear();
  1034. myCmd.Parameters.Add(Database.Parameter(myProv, "CultureName", item.Key));
  1035. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemId", theObj.Id));
  1036. myCmd.ExecuteNonQuery();
  1037. //insert current culture entry
  1038. sSql = "INSERT INTO [" + this.TableName + "_culture](CultureName, ItemId, Title, Description) "
  1039. + " VALUES(@CultureName, @ItemId, @Title, @Description) ";
  1040. //CustomString1, CustomString2, CustomString3, CustomText1, CustomText2, CustomText3
  1041. //@CustomString1, @CustomString2, @CustomString3, @CustomText1, @CustomText2, @CustomText3
  1042. myCmd.CommandText = Database.ParseSql(sSql);
  1043. myCmd.Parameters.Clear();
  1044. myCmd.Parameters.Add(Database.Parameter(myProv, "CultureName", item.Key));
  1045. myCmd.Parameters.Add(Database.Parameter(myProv, "ItemId", theObj.Id));
  1046. myCmd.Parameters.Add(Database.Parameter(myProv, "Title", item.Value));
  1047. myCmd.Parameters.Add(Database.Parameter(myProv, "Description", descriptionValue));
  1048. //myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString1", customString1));
  1049. //myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString2", customString2));
  1050. //myCmd.Parameters.Add(Database.Parameter(myProv, "CustomString3", customString3));
  1051. //myCmd.Parameters.Add(Database.Parameter(myProv, "CustomText1", customText1));
  1052. //myCmd.Parameters.Add(Database.Parameter(myProv, "CustomText2", customText2));
  1053. //myCmd.Parameters.Add(Database.Parameter(myProv, "CustomText3", customText3));
  1054. myCmd.ExecuteNonQuery();
  1055. }
  1056. }
  1057. }
  1058. }