public List <ItemGroupTbl> GetAll(string SortBy) { List <ItemGroupTbl> _DataItems = new List <ItemGroupTbl>(); TrackerDb _TDB = new TrackerDb(); string _sqlCmd = CONST_SQL_SELECT; _sqlCmd += " ORDER BY " + ((String.IsNullOrEmpty(SortBy)) ? "ItemTypeSortPos" : SortBy); // Add order by string // params would go here if need IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); if (_DataReader != null) { while (_DataReader.Read()) { ItemGroupTbl _DataItem = new ItemGroupTbl(); #region StoreThisDataItem _DataItem.ItemGroupID = (_DataReader["ItemGroupID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemGroupID"]); _DataItem.GroupItemTypeID = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["GroupItemTypeID"]); _DataItem.ItemTypeID = (_DataReader["ItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeID"]); _DataItem.ItemTypeSortPos = (_DataReader["ItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeSortPos"]); _DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["Enabled"]); _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString(); #endregion _DataItems.Add(_DataItem); } _DataReader.Close(); } _TDB.Close(); return(_DataItems); }
/// <summary> /// Get NextGroup Item for the Client and Item Group passed: /// Uses: ContactID, ItemGroupID and returns ItemTypeID /// • If the contactID+ItemGroupID exists then: /// o Get the Last Item used in the UsedItemGroup. /// o Retrieve the next item as per the LastSortItem number so select Item where SortItem#>LastSortItem, get ready to return it. /// • Else get the first item in the ItemGroup and set ItemTypeToUse /// • Save to the table that this item is now the LastItem, and save sortpos, including the date it was changed. /// • Return the ItemTypeID. /// </summary> /// <param name="pContactID">The Contact / Client ID for this group</param> /// <param name="pGroupItemTypeID">The Item Group that the Item is from</param> /// <returns>ItemID to use</returns> public ItemGroupTbl GetNextGroupItem(long pContactID, int pGroupItemTypeID, DateTime pDeliveryDate) { ItemGroupTbl _IGT = new ItemGroupTbl(); UsedItemGroupTbl _LastItem = new UsedItemGroupTbl(); _LastItem = ContactLastGroupItem(pContactID, pGroupItemTypeID); if (_LastItem.UsedItemGroupID == TrackerDb.CONST_INVALIDID) { _IGT = _IGT.GetFirstGroupItemType(pGroupItemTypeID); // save it for next time _LastItem.ContactID = pContactID; _LastItem.GroupItemTypeID = pGroupItemTypeID; _LastItem.LastItemTypeID = _IGT.ItemTypeID; _LastItem.LastItemTypeSortPos = _IGT.ItemTypeSortPos; _LastItem.LastItemDateChanged = pDeliveryDate; Insert(_LastItem); //does not seem to be updating - or finding next. } else { _IGT = _IGT.GetNextGroupItemType(pGroupItemTypeID, _LastItem.LastItemTypeSortPos); // save it for next time _LastItem.LastItemTypeID = _IGT.ItemTypeID; _LastItem.LastItemTypeSortPos = _IGT.ItemTypeSortPos; _LastItem.LastItemDateChanged = pDeliveryDate; Update(_LastItem); //does not seem to be updating - or finding next. } return(_IGT); }
public int IncItemSortPos(int pGroupItemTypeID, int pThisItemID, int pSortPos) { int _NewSortPos = pSortPos; int _LastItemSortPos = GetLastGroupItemSortPos(pGroupItemTypeID); if (pSortPos < _LastItemSortPos) { ItemGroupTbl _DataItem = GetNextGroupItemType(pGroupItemTypeID, pSortPos); if (_DataItem != null) { _NewSortPos = _DataItem.ItemTypeSortPos; _DataItem.ItemTypeSortPos--; // increment the last one by one UpdateItemGroup(_DataItem); } UpdateItemsSortPos(_NewSortPos, pGroupItemTypeID, pThisItemID); //_NewSortPos++; //int _CurrItemInSortPosID = GetSortPosItemInGroup(pGroupItemTypeID, _NewSortPos); //// if an item excists in the current pos the swop it around otherwise place this item there //if (_CurrItemInSortPosID != TrackerDb.CONST_INVALIDID) // UpdateItemsSortPos(pSortPos, pGroupItemTypeID, _CurrItemInSortPosID); //UpdateItemsSortPos(_NewSortPos, pGroupItemTypeID, pThisItemID); } return(_NewSortPos); }
public ItemGroupTbl GetLastGroupItem(int pGroupItemTypeID) { ItemGroupTbl _DataItem = new ItemGroupTbl(); TrackerDb _TDB = new TrackerDb(); _TDB.AddWhereParams(pGroupItemTypeID, DbType.Int32, "GroupItemTypeID"); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SELECT_LASTINGROUP); if (_DataReader != null) { if (_DataReader.Read()) { #region StoreThisDataItem _DataItem.ItemGroupID = (_DataReader["ItemGroupID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemGroupID"]); _DataItem.GroupItemTypeID = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["GroupItemTypeID"]); _DataItem.ItemTypeID = (_DataReader["ItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeID"]); _DataItem.ItemTypeSortPos = (_DataReader["ItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeSortPos"]); _DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["Enabled"]); _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString(); #endregion } _DataReader.Close(); } _TDB.Close(); return(_DataItem); }
public string UpdateItemGroup(ItemGroupTbl pItemGroupTbl, int pOrignal_ItemGroupID) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); #region UpdateParameters if (pOrignal_ItemGroupID > 0) { _TDB.AddWhereParams(pOrignal_ItemGroupID, DbType.Int32); // check this line it assumes id field is int32 } else { _TDB.AddWhereParams(pItemGroupTbl.ItemGroupID, DbType.Int32, "@ItemGroupID"); } _TDB.AddParams(pItemGroupTbl.GroupItemTypeID, DbType.Int32, "@GroupItemTypeID"); _TDB.AddParams(pItemGroupTbl.ItemTypeID, DbType.Int32, "@ItemTypeID"); _TDB.AddParams(pItemGroupTbl.ItemTypeSortPos, DbType.Int32, "@ItemTypeSortPos"); _TDB.AddParams(pItemGroupTbl.Enabled, DbType.Boolean, "@Enabled"); _TDB.AddParams(pItemGroupTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE); _TDB.Close(); return(_result); }
public List <ItemGroupTbl> GetAllByGroupItemTypeID(int pGroupItemID, string SortBy) { List <ItemGroupTbl> _DataItems = new List <ItemGroupTbl>(); if (!pGroupItemID.Equals(TrackerDb.CONST_INVALIDID)) { TrackerDb _TDB = new TrackerDb(); string _sqlCmd = CONST_SQL_SELECTBYGOUPITEMID; // = "SELECT ItemGroupID, GroupItemTypeID, ItemTypeID, ItemTypeSortPos, Enabled, Notes FROM ItemGroupTbl WHERE GroupItemTypeID=?"; _sqlCmd += " ORDER BY " + ((String.IsNullOrEmpty(SortBy)) ? "ItemTypeSortPos" : SortBy); // Add order by string // params would go here if need _TDB.AddWhereParams(pGroupItemID, DbType.Int32, "@GroupItemTypeID"); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); if (_DataReader != null) { while (_DataReader.Read()) { ItemGroupTbl _DataItem = new ItemGroupTbl(); #region StoreThisDataItem _DataItem.ItemGroupID = (_DataReader["ItemGroupID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemGroupID"]); _DataItem.GroupItemTypeID = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["GroupItemTypeID"]); _DataItem.ItemTypeID = (_DataReader["ItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeID"]); _DataItem.ItemTypeSortPos = (_DataReader["ItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeSortPos"]); _DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["Enabled"]); _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString(); #endregion _DataItems.Add(_DataItem); } _DataReader.Close(); } _TDB.Close(); } return(_DataItems); }
public int GetLastGroupItemSortPos(int pGroupItemTypeID) { int _LastItemSortPos = TrackerDb.CONST_INVALIDID; ItemGroupTbl _DataItem = GetLastGroupItem(pGroupItemTypeID); if (_DataItem != null) { _LastItemSortPos = _DataItem.ItemTypeSortPos; } return(_LastItemSortPos); }
public string InsertItemGroup(ItemGroupTbl pItemGroupTbl) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); #region InsertParameters _TDB.AddParams(pItemGroupTbl.GroupItemTypeID, DbType.Int32, "@GroupItemTypeID"); _TDB.AddParams(pItemGroupTbl.ItemTypeID, DbType.Int32, "@ItemTypeID"); _TDB.AddParams(pItemGroupTbl.ItemTypeSortPos, DbType.Int32, "@ItemTypeSortPos"); _TDB.AddParams(pItemGroupTbl.Enabled, DbType.Boolean, "@Enabled"); _TDB.AddParams(pItemGroupTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT); _TDB.Close(); return(_result); }
public int DecItemSortPos(ItemGroupTbl pItemGroupTbl) { pItemGroupTbl.ItemTypeSortPos = DecItemSortPos(pItemGroupTbl.GroupItemTypeID, pItemGroupTbl.ItemTypeID, pItemGroupTbl.ItemTypeSortPos); return(pItemGroupTbl.ItemTypeSortPos); }
public string DeleteItemGroup(ItemGroupTbl pItemGroupTbl) { return(DeleteItemGroup(pItemGroupTbl.ItemGroupID)); }
public string UpdateItemGroup(ItemGroupTbl pItemGroupTbl) { return(UpdateItemGroup(pItemGroupTbl, 0)); }