public UsedItemGroupTbl GetLastUsedItemID(long pContactID, int pItemID, DateTime pDeliveryDate) { UsedItemGroupTbl _DataItem = new UsedItemGroupTbl(); TrackerDb _TDB = new TrackerDb(); _TDB.AddWhereParams(pContactID, DbType.Int64, "@ContactID"); _TDB.AddWhereParams(pItemID, DbType.Int32, "@LastItemTypeID"); _TDB.AddWhereParams(pDeliveryDate, DbType.Date, "@LastItemDateChanged "); //"SELECT UsedItemGroupID, GroupItemTypeID, LastItemTypeSortPos, Notes FROM UsedItemGroupTbl WHERE ContactID = ? AND LastItemTypeID = ? AND LastItemDateChanged = ?"; IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SELECTLASTUSEDITEMID); if (_DataReader != null) { if (_DataReader.Read()) { _DataItem.UsedItemGroupID = (_DataReader["UsedItemGroupID"] == DBNull.Value) ? TrackerDb.CONST_INVALIDID : Convert.ToInt32(_DataReader["UsedItemGroupID"]); _DataItem.ContactID = pContactID; _DataItem.GroupItemTypeID = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? TrackerDb.CONST_INVALIDID : Convert.ToInt32(_DataReader["GroupItemTypeID"]); _DataItem.LastItemTypeID = pItemID; _DataItem.LastItemTypeSortPos = (_DataReader["LastItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastItemTypeSortPos"]); _DataItem.LastItemDateChanged = pDeliveryDate; _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString(); } _DataReader.Close(); } _TDB.Close(); return(_DataItem); }
public List <UsedItemGroupTbl> GetAll(string SortBy) { List <UsedItemGroupTbl> _DataItems = new List <UsedItemGroupTbl>(); TrackerDb _TDB = new TrackerDb(); string _sqlCmd = CONST_SQL_SELECT; if (!String.IsNullOrEmpty(SortBy)) { _sqlCmd += " ORDER BY " + SortBy; // Add order by string } // params would go here if need IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); if (_DataReader != null) { while (_DataReader.Read()) { UsedItemGroupTbl _DataItem = new UsedItemGroupTbl(); #region StoreThisDataItem _DataItem.UsedItemGroupID = (_DataReader["UsedItemGroupID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["UsedItemGroupID"]); _DataItem.ContactID = (_DataReader["ContactID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["ContactID"]); _DataItem.GroupItemTypeID = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["GroupItemTypeID"]); _DataItem.LastItemTypeID = (_DataReader["LastItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastItemTypeID"]); _DataItem.LastItemTypeSortPos = (_DataReader["LastItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastItemTypeSortPos"]); _DataItem.LastItemDateChanged = (_DataReader["LastItemDateChanged"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LastItemDateChanged"]); _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); }
/// <summary> /// Check to see if this contact has ever taken an item from this group before - if so return it otherwise return -1 /// </summary> /// <param name="pContactID">The ID of the contact</param> /// <param name="pGroupItemTypeID">The Group ID requesed</param> /// <returns>True if it </returns> public UsedItemGroupTbl ContactLastGroupItem(long pContactID, int pGroupItemTypeID) { UsedItemGroupTbl _DataItem = new UsedItemGroupTbl(); TrackerDb _TDB = new TrackerDb(); _TDB.AddWhereParams(pContactID, DbType.Int64, "@ContactID"); _TDB.AddWhereParams(pGroupItemTypeID, DbType.Int32, "@GroupItemTypeID"); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_GETGROUPITEMTYPED); //SELECT UsedItemGroupID, LastItemTypeID, LastItemTypeSortPos, LastItemDateChanged, Notes FROM UsedItemGroupTbl WHERE(ContactID = ?) AND (GroupItemTypeID = ?)"; if (_DataReader != null) { if (_DataReader.Read()) { _DataItem.UsedItemGroupID = (_DataReader["UsedItemGroupID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["UsedItemGroupID"]); _DataItem.ContactID = pContactID; _DataItem.GroupItemTypeID = pGroupItemTypeID; _DataItem.LastItemTypeID = (_DataReader["LastItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastItemTypeID"]); _DataItem.LastItemTypeSortPos = (_DataReader["LastItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastItemTypeSortPos"]); _DataItem.LastItemDateChanged = (_DataReader["LastItemDateChanged"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LastItemDateChanged"]); _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString(); } _DataReader.Close(); } _TDB.Close(); return(_DataItem); }
public string Update(UsedItemGroupTbl pUsedItemGroupTbl, int pOrignal_UsedItemGroupID) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); #region UpdateParameters if (pOrignal_UsedItemGroupID > 0) { _TDB.AddWhereParams(pOrignal_UsedItemGroupID, DbType.Int32); // check this line it assumes id field is int32 } else { _TDB.AddWhereParams(pUsedItemGroupTbl.UsedItemGroupID, DbType.Int32, "@UsedItemGroupID"); } _TDB.AddParams(pUsedItemGroupTbl.ContactID, DbType.Int64, "@ContactID"); _TDB.AddParams(pUsedItemGroupTbl.GroupItemTypeID, DbType.Int32, "@GroupItemTypeID"); _TDB.AddParams(pUsedItemGroupTbl.LastItemTypeID, DbType.Int32, "@LastItemTypeID"); _TDB.AddParams(pUsedItemGroupTbl.LastItemTypeSortPos, DbType.Int32, "@LastItemTypeSortPos"); _TDB.AddParams(pUsedItemGroupTbl.LastItemDateChanged, DbType.Date, "@LastItemDateChanged"); _TDB.AddParams(pUsedItemGroupTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE); // UPDATE UsedItemGroupTbl SET ContactID = ?, GroupItemTypeID = ?, LastItemTypeID = ?, LastItemTypeSortPos = ?, LastItemDateChanged = ?, Notes = ? WHERE (UsedItemGroupID = ?)"; _TDB.Close(); return(_result); }
/// <summary> /// Update an item if it was a group item /// </summary> /// <param name="pContactID">contact/companyid</param> /// <param name="pItemTypeID">ItemTypeID</param> /// <param name="pOldDeliveryDate">PrevDeliverDate</param> /// <param name="pNewDeliveryDate">NewDeliveryDate</param> /// <returns></returns> public bool UpdateIfGroupItem(long pContactID, int pItemTypeID, DateTime pOldDeliveryDate, DateTime pNewDeliveryDate) { bool _result = false; if (!pNewDeliveryDate.Equals(pOldDeliveryDate)) { UsedItemGroupTbl _DataItem = new UsedItemGroupTbl(); _DataItem = GetLastUsedItemID(pContactID, pItemTypeID, pOldDeliveryDate); if (_DataItem.UsedItemGroupID != TrackerDb.CONST_INVALIDID) { _DataItem.LastItemDateChanged = pNewDeliveryDate; _result = Update(_DataItem) == string.Empty; } } return(_result); }
public string Insert(UsedItemGroupTbl pUsedItemGroupTbl) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); #region InsertParameters _TDB.AddParams(pUsedItemGroupTbl.ContactID, DbType.Int64, "@ContactID"); _TDB.AddParams(pUsedItemGroupTbl.GroupItemTypeID, DbType.Int32, "@GroupItemTypeID"); _TDB.AddParams(pUsedItemGroupTbl.LastItemTypeID, DbType.Int32, "@LastItemTypeID"); _TDB.AddParams(pUsedItemGroupTbl.LastItemTypeSortPos, DbType.Int32, "@LastItemTypeSortPos"); _TDB.AddParams(pUsedItemGroupTbl.LastItemDateChanged, DbType.Date, "@LastItemDateChanged"); _TDB.AddParams(pUsedItemGroupTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT); _TDB.Close(); return(_result); }
public string Delete(UsedItemGroupTbl pUsedItemGroupTbl) { return(Delete(pUsedItemGroupTbl.UsedItemGroupID)); }
public string Update(UsedItemGroupTbl pUsedItemGroupTbl) { return(Update(pUsedItemGroupTbl, 0)); }