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));
}
