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);
}
/// <summary>
/// Execute the SQL statement does not return results, such as: delete, update, insert operation
/// </summary>
/// <param name="strSQL">SQL String of a non Query Type</param>
/// <returns>success or failure</returns>
public bool UpdateOrderHeader(OrderHeaderData pOrderHeader, List <string> pOrders)
{
bool _resultState = false;
string _strSQL = CONST_ORDERUPDATEHEADER_SQL + " WHERE ";
// for all the OrderIds passed create a where clause
for (int i = 0; i < pOrders.Count - 1; i++)
{
_strSQL += " OrderID = " + pOrders[i] + " OR";
}
_strSQL += " OrderID = " + pOrders[pOrders.Count - 1];
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(pOrderHeader.CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(pOrderHeader.OrderDate, DbType.Date, "@OrderDate");
_TDB.AddParams(pOrderHeader.RoastDate, DbType.Date, "@RoastDate");
_TDB.AddParams(pOrderHeader.ToBeDeliveredBy, DbType.Int64, "@ToBeDeliveredBy");
_TDB.AddParams(pOrderHeader.RequiredByDate, DbType.Date, "@RequiredByDate");
_TDB.AddParams(pOrderHeader.Confirmed, DbType.Boolean, "@Confirmed");
_TDB.AddParams(pOrderHeader.Done, DbType.Boolean, "@Done");
_TDB.AddParams(pOrderHeader.InvoiceDone, DbType.Boolean, "@InvoiceDone");
_TDB.AddParams(pOrderHeader.PurchaseOrder, DbType.String, "@PurchaseOrder");
_TDB.AddParams(pOrderHeader.Notes, DbType.String, "@Notes");
_resultState = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_strSQL));
_TDB.Close();
return(_resultState);
}
public string Update(PriceLevelsTbl pPriceLevelsTbl, int pOrignal_PriceLevelID)
{
string _result = string.Empty;
TrackerDb _TDB = new TrackerDb();
#region UpdateParameters
if (pOrignal_PriceLevelID > 0)
{
_TDB.AddWhereParams(pOrignal_PriceLevelID, DbType.Int32); // check this line it assumes id field is int32
}
else
{
_TDB.AddWhereParams(pPriceLevelsTbl.PriceLevelID, DbType.Boolean, "@PriceLevelID");
}
_TDB.AddParams(pPriceLevelsTbl.PriceLevelDesc, DbType.String, "@PriceLevelDesc");
_TDB.AddParams(Math.Round(pPriceLevelsTbl.PricingFactor, 3), DbType.Double, "@PricingFactor");
_TDB.AddParams(pPriceLevelsTbl.Enabled, DbType.Int32, "@Enabled");
_TDB.AddParams(pPriceLevelsTbl.Notes, DbType.String, "@Notes");
#endregion
// Now we have the parameters excute the SQL
_result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE);
_TDB.Close();
return(_result);
}
public string InsertTransactionType(TransactionTypesTbl pTransactionType)
{
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(pTransactionType.TransactionID, DbType.Int32);
_TDB.AddParams(pTransactionType.TransactionType);
_TDB.AddParams(pTransactionType.Notes);
string _Result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT);
_TDB.Close();
return(_Result);
}
public string UpdateContact(ContactType pContact)
{
string _Err = String.Empty;
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(pContact.CustomerTypeID, DbType.Int32);
_TDB.AddParams(pContact.PredictionDisabled, DbType.Boolean);
_TDB.AddWhereParams(pContact.CustomerID, DbType.Int64);
// CONST_SQL_UPDATE
_Err = _TDB.ExecuteNonQuerySQLWithParams(CONST_SQL_UPDATE, _TDB.Params, _TDB.WhereParams);
_TDB.Close();
return(_Err);
}
public string Insert(PriceLevelsTbl pPriceLevelsTbl)
{
string _result = string.Empty;
TrackerDb _TDB = new TrackerDb();
#region InsertParameters
_TDB.AddParams(pPriceLevelsTbl.PriceLevelDesc, DbType.String, "@PriceLevelDesc");
_TDB.AddParams(Math.Round(pPriceLevelsTbl.PricingFactor, 3), DbType.Single, "@PricingFactor");
_TDB.AddParams(pPriceLevelsTbl.Enabled, DbType.Int32, "@Enabled");
_TDB.AddParams(pPriceLevelsTbl.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 SetStatusDoneByTempOrder()
{
string _Result = String.Empty;
List <RepairsTbl> _RelatedRepairs = GetListOfRelatedTempOrders();
if (_RelatedRepairs.Count > 0)
{
TempOrdersLinesTbl _TOLines = new TempOrdersLinesTbl();
for (int i = 0; i < _RelatedRepairs.Count; i++)
{
if (_RelatedRepairs[i].RepairStatusID <= CONST_REPAIRSTATUS_WORKSHOP)
{
_TOLines.DeleteByOriginalID(_RelatedRepairs[i].RelatedOrderID);
OrderTbl _Order = new OrderTbl();
_Order.UpdateIncDeliveryDateBy7(_RelatedRepairs[i].RelatedOrderID);
}
else
{
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(CONST_REPAIRSTATUS_DONE, DbType.Int32);
_Result += _TDB.ExecuteNonQuerySQL(CONST_SQL_SETDONEBYID);
_TDB.Close();
}
}
}
return(_Result);
}
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 string InsertCityPrepDay(CityPrepDaysTbl objCityPrepDaysTbl)
{
string _Result = String.Empty;
TrackerDb _TDB = new TrackerDb();
/// CityID, PrepDayOfWeekID, DeliveryDelayDays, DeliveryOrder
_TDB.AddParams(objCityPrepDaysTbl.CityID, System.Data.DbType.Int32);
_TDB.AddParams(objCityPrepDaysTbl.PrepDayOfWeekID, System.Data.DbType.Byte);
_TDB.AddParams(objCityPrepDaysTbl.DeliveryDelayDays, System.Data.DbType.Int32);
_TDB.AddParams(objCityPrepDaysTbl.DeliveryOrder, System.Data.DbType.Int32);
_Result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT);
_TDB.Close();
return(_Result);
}
public string DeleteRepair(int RepairID)
{
string _result = String.Empty;
TrackerDb _TrackerDb = new TrackerDb();
_TrackerDb.AddParams(RepairID, DbType.Int32);
_result = _TrackerDb.ExecuteNonQuerySQL(CONST_SQL_DELETE);
return(_result);
}
/*
* string _connectionStr = ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString;
*
* using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
* {
* string _sqlCmd = CONST_SQL_SELECT;
* OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the query we have built
* _conn.Open();
* OleDbDataReader _DataReader = _cmd.ExecuteReader();
* if (_DataReader.Read())
* {
* _DataItem.SCEMTID = (_DataReader["SCEMTID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SCEMTID"]);
* _DataItem.Header = (_DataReader["Header"] == DBNull.Value) ? string.Empty : _DataReader["Header"].ToString();
* _DataItem.Body = (_DataReader["Body"] == DBNull.Value) ? string.Empty : _DataReader["Body"].ToString();
* _DataItem.Footer = (_DataReader["Footer"] == DBNull.Value) ? string.Empty : _DataReader["Footer"].ToString();
* _DataItem.DateLastChange = (_DataReader["DateLastChange"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["DateLastChange"]);
* _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
* }
* }
* return _DataItem;
* }
*/
public string UpdateTexts(SendCheckEmailTextsData pEmailTextsData, int pOriginalID)
{
string _errString = "";
TrackerDb _TDB = new TrackerDb();
#region Parameters
// Add data sent
_TDB.AddParams(pEmailTextsData.Header, DbType.String, "@Header");
_TDB.AddParams(pEmailTextsData.Body, DbType.String, "@Body");
_TDB.AddParams(pEmailTextsData.Footer, DbType.String, "@Footer");
_TDB.AddParams(DateTime.Now.Date, DbType.Date, "@DateLastChange");
_TDB.AddParams(pEmailTextsData.Notes, DbType.String, "@Notes");
// " WHERE SCEMTID = ?)";
_TDB.AddWhereParams(pOriginalID, DbType.Int32, "@SCEMTID");
#endregion
_errString = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE);
_TDB.Close();
return(_errString);
}
public string UpdateCityPrepDay(CityPrepDaysTbl objCityPrepDaysTbl, int origCityPrepDaysID)
{
string _Result = String.Empty;
if (origCityPrepDaysID > 0)
{
TrackerDb _TDB = new TrackerDb();
/// CityID, PrepDayOfWeekID, DeliveryDelayDays, DeliveryOrder
_TDB.AddParams(objCityPrepDaysTbl.CityID, System.Data.DbType.Int32);
_TDB.AddParams(objCityPrepDaysTbl.PrepDayOfWeekID, System.Data.DbType.Byte);
_TDB.AddParams(objCityPrepDaysTbl.DeliveryDelayDays, System.Data.DbType.Int32);
_TDB.AddParams(objCityPrepDaysTbl.DeliveryOrder, System.Data.DbType.Int32);
/// Where CityPrepDayID
_TDB.AddWhereParams(origCityPrepDaysID, DbType.Int32);
_Result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATEBYID);
_TDB.Close();
}
return(_Result);
}
/*
* OleDbConnection _conn = new OleDbConnection(_connectionString);
* // custoemr ZZname = 9 is a general customer name so condition must be to that
* string _sqlCmd = "SELECT [ItemTypeID], [QuantityOrdered], [PackagingID], [OrderID] FROM [OrdersTbl] WHERE ";
* if (CustomerId == 9)
* _sqlCmd += "([CustomerId] = 9) AND ([RequiredByDate] = ?) AND ([Notes] = ?)";
* else
* _sqlCmd += "([CustomerId] = ?) AND ([RequiredByDate] = ?)";
* // attach the command
* OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
* if (CustomerId == 9)
* {
* _cmd.Parameters.Add(new OleDbParameter { Value = DeliveryDate });
* _cmd.Parameters.Add(new OleDbParameter { Value = Notes });
* }
* else
* {
* _cmd.Parameters.Add(new OleDbParameter { Value = CustomerId });
* _cmd.Parameters.Add(new OleDbParameter { Value = DeliveryDate });
* }
* try
* {
* _conn.Open();
* OleDbDataReader _DataReader = _cmd.ExecuteReader();
* while (_DataReader.Read())
* {
* OrderDetailData od = new OrderDetailData();
*
* od.ItemTypeID = (_DataReader["ItemTypeID"] == DBNull.Value) ? 0 : (Int32)_DataReader["ItemTypeID"];
* od.PackagingID = (_DataReader["PackagingID"] == DBNull.Value) ? 0 : (Int32)_DataReader["PackagingID"];
* od.OrderID = (Int32)_DataReader["OrderId"]; // this is the PK cannot be null
* od.QuantityOrdered = (_DataReader["QuantityOrdered"] == DBNull.Value) ? 1 : Math.Round(Convert.ToDouble(_DataReader["QuantityOrdered"]),2);
*
* oDetails.Add(od);
* }
* _DataReader.Close();
* }
* catch (Exception _ex)
* {
* lastError = _ex.Message;
* }
* finally
* {
* _conn.Close();
* }
* return oDetails;
* }
*/
/// <summary>
/// Update Order Details, using the orderID update the line info.
/// </summary>
/// <param name="ItemTypeID"></param>
/// <param name="QuantityOrdered"></param>
/// <param name="PackagingID"></param>
/// <param name="OrderID"></param>
/// <returns></returns>
public bool UpdateOrderDetails(Int64 OrderID, Int64 CustomerID, Int32 ItemTypeID, DateTime DeliveryDate, double QuantityOrdered, Int32 PackagingID)
{
bool _Success = false;
string _sqlCmd = "UPDATE OrdersTbl SET ItemTypeID = ?, QuantityOrdered = ?, PackagingID = ? WHERE (OrderId = ?)";
TrackerDb _TDB = new TrackerDb();
// check if the item is a group item then get the next group.
TrackerTools _TT = new TrackerTools();
ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(CustomerID, ItemTypeID, DeliveryDate);
_TDB.AddParams(ItemTypeID, DbType.Int32, "@ItemTypeID");
_TDB.AddParams(Math.Round(QuantityOrdered, 2), DbType.Double, "@QuantityOrdered");
_TDB.AddParams(PackagingID, DbType.Int32, "@PackagingID");
_TDB.AddWhereParams(OrderID, DbType.Int64, "@OrderID");
_Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_sqlCmd));
_TDB.Close();
return(_Success);
}
public string UpdateItemsSortPos(int pSortPos, int pGroupItemID, int pItemTypeID)
{
string _result = string.Empty;
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(pSortPos, DbType.Int32, "@SortPos");
_TDB.AddWhereParams(pGroupItemID, DbType.Int32, "@GroupItemTypeID");
_TDB.AddWhereParams(pItemTypeID, DbType.Int32, "@ItemTypeID");
_result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATEITEMSORTPOS);
_TDB.Close();
return(_result);
}
public string Update(SysDataTbl SysDataItem, int orig_ID)
{
string _Result = String.Empty;
if (orig_ID > 0)
{
TrackerDb _TDB = new TrackerDb();
// LastReoccurringDate = ?, DoReoccuringOrders = ?, DateLastPrepDateCalcd = ?, MinReminderDate = ?, GroupItemTypeID = ?" +
_TDB.AddParams(SysDataItem.LastReoccurringDate, DbType.Date);
_TDB.AddParams(SysDataItem.DoReoccuringOrders, DbType.Boolean);
_TDB.AddParams(SysDataItem.DateLastPrepDateCalcd, DbType.Date);
_TDB.AddParams(SysDataItem.MinReminderDate, DbType.Date);
_TDB.AddParams(SysDataItem.GroupItemTypeID, DbType.Int32, "@GroupItemTypeID");
// "WHERE (SysDataTbl.ID = ?)
_TDB.AddWhereParams(orig_ID, DbType.Int32);
_Result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATEBYID);
_TDB.Close();
}
return(_Result);
}
public string Update(PaymentTermsTbl pPaymentTermsTbl, int pOrignal_PaymentTermID)
{
string _result = string.Empty;
TrackerDb _TDB = new TrackerDb();
#region UpdateParameters
if (pOrignal_PaymentTermID > 0)
{
_TDB.AddWhereParams(pOrignal_PaymentTermID, DbType.Int32); // check this line it assumes id field is int32
}
else
{
_TDB.AddWhereParams(pPaymentTermsTbl.PaymentTermID, DbType.Boolean, "@PaymentTermID");
}
_TDB.AddParams(pPaymentTermsTbl.PaymentTermDesc, DbType.String, "@PaymentTermDesc");
_TDB.AddParams(pPaymentTermsTbl.PaymentDays, DbType.Int32, "@PaymentDays");
_TDB.AddParams(pPaymentTermsTbl.DayOfMonth, DbType.Int32, "@DayOfMonth");
_TDB.AddParams(pPaymentTermsTbl.UseDays, DbType.Boolean, "@UseDays");
_TDB.AddParams(pPaymentTermsTbl.Enabled, DbType.Boolean, "@Enabled");
_TDB.AddParams(pPaymentTermsTbl.Notes, DbType.String, "@Notes");
#endregion
// Now we have the parameters excute the SQL
_result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE);
_TDB.Close();
return(_result);
}
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);
}
public static void UpdateTransaction(TransactionTypesTbl pTransactionType, int pOrignal_TransactionID)
{
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(pTransactionType.TransactionType);
_TDB.AddParams(pTransactionType.Notes);
if (pOrignal_TransactionID > 0)
{
_TDB.AddWhereParams(pOrignal_TransactionID, DbType.Int32);
}
else
{
_TDB.AddWhereParams(pTransactionType.TransactionID, DbType.Int32);
}
string _Result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE);
_TDB.Close();
// return _Result;
}
public string Insert(InvoiceTypeTbl pInvoiceTypeTbl)
{
string _result = string.Empty;
if (pInvoiceTypeTbl.InvoiceTypeID > 0)
{
// they have called insert by mistake ( or the environment has)
_result = Update(pInvoiceTypeTbl, 0);
}
else
{
TrackerDb _TDB = new TrackerDb();
#region InsertParameters
_TDB.AddParams(pInvoiceTypeTbl.InvoiceTypeDesc, DbType.String, "@InvoiceTypeDesc");
_TDB.AddParams(pInvoiceTypeTbl.Enabled, DbType.Boolean, "@Enabled");
_TDB.AddParams(pInvoiceTypeTbl.Notes, DbType.String, "@Notes");
#endregion
// Now we have the parameters excute the SQL
_result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT);
_TDB.Close();
}
return(_result);
}
/// <summary>
/// Update only Contact Type, usese pContact.CustType and pContact.CustID
/// </summary>
/// <param name="pContact">cus</param>
/// <returns></returns>
public string UpdateContactTypeIfInfoOnly(long pCustomerID, int pCustomerTypeID)
{
string _Err = String.Empty;
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(pCustomerTypeID, DbType.Int32);
_TDB.AddWhereParams(pCustomerID, DbType.Int64);
_TDB.AddWhereParams(CustomerTypeTbl.CONST_INFO_ONLY, DbType.Int32);
// CONST_SQL_UPDATE
_Err = _TDB.ExecuteNonQuerySQLWithParams(CONST_SQL_UPDATETYPEONLY, _TDB.Params, _TDB.WhereParams);
_TDB.Close();
return(_Err);
}
public bool InsertLogItem(LogTbl objLog)
{
bool _inserted = false;
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(DateTime.Now, DbType.DateTime); // other insert DateTime does not work only date
_TDB.AddParams(objLog.UserID, DbType.Int32);
_TDB.AddParams(objLog.SectionID, DbType.Int32);
_TDB.AddParams(objLog.TranactionTypeID, DbType.Int32);
_TDB.AddParams(objLog.CustomerID, DbType.Int64);
_TDB.AddParams(objLog.Details);
_TDB.AddParams(objLog.Notes);
_inserted = string.IsNullOrWhiteSpace(_TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT));
_TDB.Close();
return(_inserted);
}
/// <summary>
/// Insert Contact item into Temp Line
/// </summary>
/// <param name="pLineData">Contact Line Data to add</param>
/// <returns>success or failure</returns>
public bool InsertContactItems(ItemContactRequires pLineData)
{
bool _Success = false;
TrackerDb _TDB = new TrackerDb();
#region Parameters
// Add data sent CustomerId, OrderDate, RoastDate, RequiredByDate, ToBeDeliveredByID, Confirmed, Done, Notes
_TDB.AddParams(pLineData.CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(pLineData.ItemID, DbType.Int32, "@ItemID");
_TDB.AddParams(pLineData.ItemQty, DbType.Double, "@ItemQty");
_TDB.AddParams(pLineData.ItemPrepID, DbType.Int32, "@ItemPrepID");
_TDB.AddParams(pLineData.ItemPackagID, DbType.Int32, "@ItemPackagID");
_TDB.AddParams(pLineData.AutoFulfill, DbType.Boolean, "@AutoFulfill");
_TDB.AddParams(pLineData.ReoccurID, DbType.Int64, "@ReoccurID");
#endregion
_Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(CONST_SQL_INSERTNEWITEMS));
_TDB.Close();
return(_Success);
}
public string InsertLogItem(SentRemindersLogTbl pSentRemindersLog)
{
string _ErrString = String.Empty;
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(pSentRemindersLog.CustomerID, DbType.Int64);
_TDB.AddParams(pSentRemindersLog.DateSentReminder, DbType.Date);
_TDB.AddParams(pSentRemindersLog.NextPrepDate, DbType.Date);
_TDB.AddParams(pSentRemindersLog.ReminderSent, DbType.Boolean);
_TDB.AddParams(pSentRemindersLog.HadAutoFulfilItem, DbType.Boolean);
_TDB.AddParams(pSentRemindersLog.HadReoccurItems, DbType.Boolean);
_ErrString = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT);
_TDB.Close();
return(_ErrString);
}
public string Insert(PaymentTermsTbl pPaymentTermsTbl)
{
string _result = string.Empty;
TrackerDb _TDB = new TrackerDb();
#region InsertParameters
_TDB.AddParams(pPaymentTermsTbl.PaymentTermDesc, DbType.String, "@PaymentTermDesc");
_TDB.AddParams(pPaymentTermsTbl.PaymentDays, DbType.Int32, "@PaymentDays");
_TDB.AddParams(pPaymentTermsTbl.DayOfMonth, DbType.Int32, "@DayOfMonth");
_TDB.AddParams(pPaymentTermsTbl.UseDays, DbType.Boolean, "@UseDays");
_TDB.AddParams(pPaymentTermsTbl.Enabled, DbType.Boolean, "@Enabled");
_TDB.AddParams(pPaymentTermsTbl.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 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);
}
/// <summary>
/// Insert an item into the log using the securityusername
/// </summary>
/// <param name="pSecurityUserName">security user name as per the Members table</param>
/// <param name="pSectionID">Section's ID</param>
/// <param name="pTranasctionTypeID">Transaction's Type ID</param>
/// <param name="pDetails">Detai of what was done</param>
/// <param name="pNotes">Notes of what was done</param>
/// <returns>if the item was insertted</returns>
public bool InsertLogItem(string pSecurityUserName, int pSectionID, int pTransactionTypeID,
long pCustomerID, string pDetails, string pNotes)
{
bool _inserted = false;
PersonsTbl _Persons = new PersonsTbl();
int _SecurityUserID = _Persons.PersonsIDoFSecurityUsers(pSecurityUserName);
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(DateTime.Now, DbType.Date);
_TDB.AddParams(_SecurityUserID, DbType.Int32);
_TDB.AddParams(pSectionID, DbType.Int32);
_TDB.AddParams(pTransactionTypeID, DbType.Int32);
_TDB.AddParams(pCustomerID, DbType.Int64);
_TDB.AddParams(pDetails);
_TDB.AddParams(pNotes);
_inserted = string.IsNullOrWhiteSpace(_TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT));
_TDB.Close();
return(_inserted);
}
/// <summary>
/// Insert Contact item into Temp Header
/// </summary>
/// <param name="pHeaderData">Contact Header Data to add</param>
/// <returns>success or failure</returns>
public bool InsertContacts(ContactToRemindDetails pHeaderData)
{
bool _Success = false;
TrackerDb _TDB = new TrackerDb();
#region Parameters
// Add data sent CustomerId, OrderDate, RoastDate, RequiredByDate, ToBeDeliveredByID, Confirmed, Done, Notes
_TDB.AddParams(pHeaderData.CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(pHeaderData.CompanyName, DbType.String, "@CompanyName");
_TDB.AddParams(pHeaderData.ContactFirstName, DbType.String, "@ContactFirstName");
_TDB.AddParams(pHeaderData.ContactAltFirstName, DbType.String, "@ContactAltFirstName");
_TDB.AddParams(pHeaderData.CityID, DbType.Int32, "@CityID");
_TDB.AddParams(pHeaderData.EmailAddress, DbType.String, "@EmailAddress");
_TDB.AddParams(pHeaderData.AltEmailAddress, DbType.String, "@AltEmailAddress");
_TDB.AddParams(pHeaderData.CityID, DbType.Int32, "@CityID");
_TDB.AddParams(pHeaderData.EquipTypeID, DbType.Int32, "@EquipTypeID");
_TDB.AddParams(pHeaderData.TypicallySecToo, DbType.Boolean, "@TypicallySecToo");
_TDB.AddParams(pHeaderData.PreferedAgentID, DbType.Int32, "@PreferedAgentID");
_TDB.AddParams(pHeaderData.SalesAgentID, DbType.Int32, "@SalesAgentID");
_TDB.AddParams(pHeaderData.UsesFilter, DbType.Boolean, "@UsesFilter");
_TDB.AddParams(pHeaderData.enabled, DbType.Boolean, "@enabled");
_TDB.AddParams(pHeaderData.AlwaysSendChkUp, DbType.Boolean, "@AlwaysSendChkUp");
_TDB.AddParams(pHeaderData.RequiresPurchOrder, DbType.Boolean, "@RequiresPurchOrder");
_TDB.AddParams(pHeaderData.ReminderCount, DbType.Int32, "@ReminderCount");
_TDB.AddParams(pHeaderData.NextPrepDate, DbType.Date, "@NextPrepDate");
_TDB.AddParams(pHeaderData.NextDeliveryDate, DbType.Date, "@NextDeliveryDate");
_TDB.AddParams(pHeaderData.NextCoffee, DbType.Date, "@NextCoffee");
_TDB.AddParams(pHeaderData.NextClean, DbType.Date, "@NextClean");
_TDB.AddParams(pHeaderData.NextFilter, DbType.Date, "@NextFilter");
_TDB.AddParams(pHeaderData.NextDescal, DbType.Date, "@NextDescal");
_TDB.AddParams(pHeaderData.NextService, DbType.Date, "@NextService");
#endregion
_Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(CONST_SQL_INSERTNEWCONTACTS));
_TDB.Close();
return(_Success);
}
/*
* try
* {
* _conn.Open();
* if (_cmd.ExecuteNonQuery() > 0)
*
*
* OleDbConnection _conn = new OleDbConnection(_connectionString);
*
* // add parameters in the order they appear in the update command
* OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
* _cmd.Parameters.Add(new OleDbParameter { Value = ItemTypeID });
* _cmd.Parameters.Add(new OleDbParameter { Value = Math.Round(QuantityOrdered,2) });
* _cmd.Parameters.Add(new OleDbParameter { Value = PackagingID });
* _cmd.Parameters.Add(new OleDbParameter { Value = OrderID });
*
* try
* {
* _conn.Open();
* if (_cmd.ExecuteNonQuery() > 0)
* return false;
* }
* catch (OleDbException ex)
* {
* string _ErrStr = ex.Message;
* return _ErrStr == "";
* }
* finally
* {
* _conn.Close();
* }
*
* return true;
* }
*/
public bool InsertOrderDetails(Int64 CustomerID, DateTime OrderDate, DateTime RoastDate, Int32 ToBeDeliveredBy,
DateTime RequiredByDate, Boolean Confirmed, Boolean Done, String Notes,
double QuantityOrdered, Int32 PackagingID, Int32 ItemTypeID)
{
string _sqlCmd = "INSERT INTO OrdersTbl (CustomerId, OrderDate, RoastDate, RequiredByDate, ToBeDeliveredBy, Confirmed, Done, Notes, " +
" ItemTypeID, QuantityOrdered, PackagingID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
//1 2 3 4 5 6 7 8 9 10 11
bool _Success = false;
// check if the item is a group item then get the next group.
TrackerTools _TT = new TrackerTools();
ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(CustomerID, ItemTypeID, RequiredByDate);
TrackerDb _TDB = new TrackerDb();
// first summary data
_TDB.AddParams(CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(OrderDate, DbType.Date, "@OrderDate");
_TDB.AddParams(RoastDate, DbType.Date, "@RoastDate");
_TDB.AddParams(RequiredByDate, DbType.Date, "@RequiredByDate");
_TDB.AddParams(ToBeDeliveredBy, DbType.Int32, "@ToBeDeliveredBy");
_TDB.AddParams(Confirmed, DbType.Boolean, "@Confirmed");
_TDB.AddParams(Done, DbType.Boolean, "@Done");
_TDB.AddParams(Notes, DbType.String, "@Notes");
// Now line data
_TDB.AddParams(ItemTypeID, DbType.Int32, "@ItemTypeID");
_TDB.AddParams(Math.Round(QuantityOrdered, 2), DbType.Double, "@QuantityOrdered");
_TDB.AddParams(PackagingID, DbType.Int32, "@PackagingID");
_Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_sqlCmd));
_TDB.Close();
return(_Success);
}
public string Insert(CustomersAccInfoTbl pCustomersAccInfoTbl)
{
string _result = string.Empty;
if (pCustomersAccInfoTbl.CustomerID == 0)
{
_result = "error: customer number cannot be 0";
}
else
{
TrackerDb _TDB = new TrackerDb();
#region InsertParameters
_TDB.AddParams(pCustomersAccInfoTbl.CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(pCustomersAccInfoTbl.RequiresPurchOrder, DbType.Int32, "@RequiresPurchOrder");
_TDB.AddParams(pCustomersAccInfoTbl.CustomerVATNo, DbType.String, "@CustomerVATNo");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr1, DbType.String, "@BillAddr1");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr2, DbType.String, "@BillAddr2");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr3, DbType.String, "@BillAddr3");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr4, DbType.String, "@BillAddr4");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr5, DbType.String, "@BillAddr5");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr1, DbType.String, "@ShipAddr1");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr2, DbType.String, "@ShipAddr2");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr3, DbType.String, "@ShipAddr3");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr4, DbType.String, "@ShipAddr4");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr5, DbType.String, "@ShipAddr5");
_TDB.AddParams(pCustomersAccInfoTbl.AccEmail, DbType.String, "@AccEmail");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccEmail, DbType.String, "@AltAccEmail");
_TDB.AddParams(pCustomersAccInfoTbl.PaymentTermID, DbType.Int32, "@PaymentTermID");
_TDB.AddParams(pCustomersAccInfoTbl.Limit, DbType.Currency, "@Limit");
_TDB.AddParams(pCustomersAccInfoTbl.FullCoName, DbType.String, "@FullCoName");
_TDB.AddParams(pCustomersAccInfoTbl.AccFirstName, DbType.String, "@AccFirstName");
_TDB.AddParams(pCustomersAccInfoTbl.AccLastName, DbType.String, "@AccLastName");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccFirstName, DbType.String, "@AltAccFirstName");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccLastName, DbType.String, "@AltAccLastName");
_TDB.AddParams(pCustomersAccInfoTbl.PriceLevelID, DbType.Int32, "@PriceLevelID");
_TDB.AddParams(pCustomersAccInfoTbl.InvoiceTypeID, DbType.Int32, "@InvoiceTypeID");
_TDB.AddParams(pCustomersAccInfoTbl.RegNo, DbType.String, "@RegNo");
_TDB.AddParams(pCustomersAccInfoTbl.BankAccNo, DbType.String, "@BankAccNo");
_TDB.AddParams(pCustomersAccInfoTbl.BankBranch, DbType.String, "@BankBranch");
_TDB.AddParams(pCustomersAccInfoTbl.Enabled, DbType.Boolean, "@Enabled");
_TDB.AddParams(pCustomersAccInfoTbl.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 Update(CustomersAccInfoTbl pCustomersAccInfoTbl, long pOrignal_CustomersAccInfoID)
{
string _result = string.Empty;
TrackerDb _TDB = new TrackerDb();
if (pCustomersAccInfoTbl.CustomerID == 0)
{
_result = "error: CustomerID cannot be 0";
}
else
{
#region UpdateParameters
if (pOrignal_CustomersAccInfoID > 0)
{
_TDB.AddWhereParams(pOrignal_CustomersAccInfoID, DbType.Int64); // check this line it assumes id field is int32
}
else
{
_TDB.AddWhereParams(pCustomersAccInfoTbl.CustomersAccInfoID, DbType.Int64, "@CustomersAccInfoID");
}
_TDB.AddParams(pCustomersAccInfoTbl.CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(pCustomersAccInfoTbl.RequiresPurchOrder, DbType.Int32, "@RequiresPurchOrder");
_TDB.AddParams(pCustomersAccInfoTbl.CustomerVATNo, DbType.String, "@CustomerVATNo");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr1, DbType.String, "@BillAddr1");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr2, DbType.String, "@BillAddr2");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr3, DbType.String, "@BillAddr3");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr4, DbType.String, "@BillAddr4");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr5, DbType.String, "@BillAddr5");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr1, DbType.String, "@ShipAddr1");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr2, DbType.String, "@ShipAddr2");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr3, DbType.String, "@ShipAddr3");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr4, DbType.String, "@ShipAddr4");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr5, DbType.String, "@ShipAddr5");
_TDB.AddParams(pCustomersAccInfoTbl.AccEmail, DbType.String, "@AccEmail");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccEmail, DbType.String, "@AltAccEmail");
_TDB.AddParams(pCustomersAccInfoTbl.PaymentTermID, DbType.Int32, "@PaymentTermID");
_TDB.AddParams(pCustomersAccInfoTbl.Limit, DbType.Double, "@Limit");
_TDB.AddParams(pCustomersAccInfoTbl.FullCoName, DbType.String, "@FullCoName");
_TDB.AddParams(pCustomersAccInfoTbl.AccFirstName, DbType.String, "@AccFirstName");
_TDB.AddParams(pCustomersAccInfoTbl.AccLastName, DbType.String, "@AccLastName");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccFirstName, DbType.String, "@AltAccFirstName");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccLastName, DbType.String, "@AltAccLastName");
_TDB.AddParams(pCustomersAccInfoTbl.PriceLevelID, DbType.Int32, "@PriceLevelID");
_TDB.AddParams(pCustomersAccInfoTbl.InvoiceTypeID, DbType.Int32, "@InvoiceTypeID");
_TDB.AddParams(pCustomersAccInfoTbl.RegNo, DbType.String, "@RegNo");
_TDB.AddParams(pCustomersAccInfoTbl.BankAccNo, DbType.String, "@BankAccNo");
_TDB.AddParams(pCustomersAccInfoTbl.BankBranch, DbType.String, "@BankBranch");
_TDB.AddParams(pCustomersAccInfoTbl.Enabled, DbType.Boolean, "@Enabled");
_TDB.AddParams(pCustomersAccInfoTbl.Notes, DbType.String, "@Notes");
#endregion
// Now we have the parameters excute the SQL
_result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE);
_TDB.Close();
}
return(_result);
}