示例#1
0
        public List <RepairStatusesTbl> GetAll(string SortBy)
        {
            List <RepairStatusesTbl> _DataItems = new List <RepairStatusesTbl>();
            TrackerDb _TrackerDb = new TrackerDb();
            string    _sqlCmd    = CONST_SQL_SELECT;

            _sqlCmd += " ORDER BY " + (String.IsNullOrEmpty(SortBy) ? "SortOrder" : SortBy);
            // params would go here if need
            IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    RepairStatusesTbl _DataItem = new RepairStatusesTbl();

                    _DataItem.RepairStatusID   = (_DataReader["RepairStatusID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairStatusID"]);
                    _DataItem.RepairStatusDesc = (_DataReader["RepairStatusDesc"] == DBNull.Value) ? string.Empty : _DataReader["RepairStatusDesc"].ToString();
                    _DataItem.EmailClient      = (_DataReader["EmailClient"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["EmailClient"]);
                    _DataItem.SortOrder        = (_DataReader["SortOrder"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SortOrder"]);
                    _DataItem.Notes            = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
                    _DataItems.Add(_DataItem);
                }
                _DataReader.Close();
            }
            _TrackerDb.Close();
            return(_DataItems);
        }
示例#2
0
        public List <ClientUsageLinesTbl> GetAllClientUsageLinesTbl(long pCustomerID, string SortBy)
        {
            List <ClientUsageLinesTbl> _DataItems = new List <ClientUsageLinesTbl>();
            string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;

            using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
            {
                string _sqlCmd = CONST_SQL_SELECT + " WHERE CustomerID = " + pCustomerID.ToString();
                if (!String.IsNullOrEmpty(SortBy))
                {
                    _sqlCmd += " ORDER BY " + SortBy;                            // Add order by string
                }
                TrackerDb       _TrackerDB  = new TrackerDb();
                OleDbDataReader _DataReader = _TrackerDB.ReturnDataReader(_sqlCmd);
                while (_DataReader.Read())
                {
                    ClientUsageLinesTbl _DataItem = new ClientUsageLinesTbl();

                    _DataItem.ClientUsageLineNo = (_DataReader["ClientUsageLineNo"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ClientUsageLineNo"]);
                    _DataItem.CustomerID        = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
                    _DataItem.LineDate          = (_DataReader["LineDate"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LineDate"]);
                    _DataItem.CupCount          = (_DataReader["CupCount"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CupCount"]);
                    _DataItem.ServiceTypeID     = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
                    _DataItem.Qty   = (_DataReader["Qty"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["Qty"]);
                    _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
                    _DataItems.Add(_DataItem);
                }
            }
            return(_DataItems);
        }
示例#3
0
        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);
        }
示例#4
0
        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);
        }
示例#5
0
        protected void BuildDeliverySheet(bool pPrintForm, string pActiveDeliveryDate, string pOnlyDeliveryBy)
        {
            // set the session variable for DeliveryBy, for later
            Session[CONST_SESSION_DELIVERTBY] = pOnlyDeliveryBy;

            string _strSQL = "SELECT DISTINCT OrdersTbl.OrderID, CustomersTbl.CompanyName AS CoName," +
                             " OrdersTbl.CustomerId, OrdersTbl.OrderDate, OrdersTbl.RoastDate, OrdersTbl.ItemTypeID, ItemTypeTbl.ItemDesc," +
                             " OrdersTbl.QuantityOrdered, ItemTypeTbl.ItemShortName, ItemTypeTbl.ItemEnabled, ItemTypeTbl.ReplacementID,  CityPrepDaysTbl.DeliveryOrder, " +
                             " ItemTypeTbl.SortOrder, OrdersTbl.RequiredByDate, OrdersTbl.ToBeDeliveredBy, OrdersTbl.PurchaseOrder, OrdersTbl.Confirmed, OrdersTbl.InvoiceDone, OrdersTbl.Done, OrdersTbl.Notes," +
                             " PackagingTbl.Description AS PackDesc, PackagingTbl.BGColour, PersonsTbl.Abreviation" +
                             " FROM ((((CityPrepDaysTbl RIGHT OUTER JOIN CustomersTbl ON CityPrepDaysTbl.CityID = CustomersTbl.City) RIGHT OUTER JOIN " +
                             " (OrdersTbl LEFT OUTER JOIN PersonsTbl ON OrdersTbl.ToBeDeliveredBy = PersonsTbl.PersonID) ON CustomersTbl.CustomerID = OrdersTbl.CustomerId) LEFT OUTER JOIN " +
                             "  PackagingTbl ON OrdersTbl.PackagingID = PackagingTbl.PackagingID) LEFT OUTER JOIN ItemTypeTbl ON OrdersTbl.ItemTypeID = ItemTypeTbl.ItemTypeID)" +
                             " WHERE (OrdersTbl.RequiredByDate = #" + pActiveDeliveryDate + "#" + ")" + ((pOnlyDeliveryBy != "") ? " AND OrdersTbl.ToBeDeliveredBy=" + pOnlyDeliveryBy : "") +
                             " ORDER BY OrdersTbl.RequiredByDate, OrdersTbl.ToBeDeliveredBy, CityPrepDaysTbl.DeliveryOrder, CustomersTbl.CompanyName, ItemTypeTbl.SortOrder";
            // sort order needs to depend on day choosen, since at the moment if there are 2 different sort orders on a day even if the day of prep is different it duplicates the item

            TrackerDb   _TDB        = new TrackerDb();
            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_strSQL);

            BuildDeliveryTable(_DataReader, pPrintForm);
            _DataReader.Close();
            _TDB.Close();

/*
 *
 *    OleDbConnection oleConn = GetTrackerOLEConnection();
 *    // open connection
 *    oleConn.Open();
 *    OleDbCommand oleCommand = new OleDbCommand(_strSQL, oleConn);
 *
 *    OleDbDataReader oleData = oleCommand.ExecuteReader();
 *    BuildDeliveryTable(oleData, pPrintForm);
 */
        }
示例#6
0
        public List <TransactionTypesTbl> GetAll(string SortBy)
        {
            List <TransactionTypesTbl> _DataItems = new List <TransactionTypesTbl>();
            TrackerDb _TrackerDb = 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 = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    TransactionTypesTbl _DataItem = new TransactionTypesTbl();

                    _DataItem.TransactionID   = (_DataReader["TransactionID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["TransactionID"]);
                    _DataItem.TransactionType = (_DataReader["TransactionType"] == DBNull.Value) ? string.Empty : _DataReader["TransactionType"].ToString();
                    _DataItem.Notes           = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
                    _DataItems.Add(_DataItem);
                }
                _DataReader.Close();
            }
            _TrackerDb.Close();
            return(_DataItems);
        }
示例#7
0
        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);
        }
示例#8
0
        public List <CustomerTrackedServiceItemsData> GetAll(string SortBy)
        {
            List <CustomerTrackedServiceItemsData> _DataItems = new List <CustomerTrackedServiceItemsData>();

            TrackerDb _TDB    = new TrackerDb();
            string    _sqlCmd = CONST_SQL_SELECT;

            if (!String.IsNullOrEmpty(SortBy))
            {
                _sqlCmd += " ORDER BY " + SortBy;                              // Add order by string
            }
            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    CustomerTrackedServiceItemsData _DataItem = new CustomerTrackedServiceItemsData();

                    _DataItem.CustomerTrackedServiceItemsID = (_DataReader["CustomerTrackedServiceItemsID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTrackedServiceItemsID"]);
                    _DataItem.CustomerTypeID = (_DataReader["CustomerTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTypeID"]);
                    _DataItem.ServiceTypeID  = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
                    _DataItem.Notes          = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
                    _DataItems.Add(_DataItem);
                }
                _DataReader.Close();
            }
            _TDB.Close();

            return(_DataItems);
        }
示例#9
0
        public List <CompanyNames> GetAllDemo()
        {
            List <CompanyNames> _CompanyNames = new List <CompanyNames>();

            TrackerDb _TDB = new TrackerDb();

            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECTDEMOS);

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    CompanyNames _Company = new CompanyNames();

                    _Company.CustomerID  = Convert.ToInt64(_DataReader["CustomerID"]);
                    _Company.CompanyName = (_DataReader["CompanyName"] == DBNull.Value) ? "" : _DataReader["CompanyName"].ToString();
                    _Company.enabled     = (_DataReader["enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["enabled"]);

                    _CompanyNames.Add(_Company);
                }

                _DataReader.Close();
            }
            _TDB.Close();

            return(_CompanyNames);
        }
示例#10
0
        public List <SentRemindersLogTbl> GetAllByDate(DateTime pDateSent, string SortBy)
        {
            List <SentRemindersLogTbl> _DataItems = new List <SentRemindersLogTbl>();
            TrackerDb _TrackerDb = new TrackerDb();
            string    _sqlCmd    = CONST_SQL_SELECT_BYDATESENT;

            if (!String.IsNullOrEmpty(SortBy))
            {
                _sqlCmd += " ORDER BY " + SortBy;                              // Add order by string
            }
            // params would go here if need
            _TrackerDb.AddWhereParams(pDateSent.Date, DbType.Date);
            IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    SentRemindersLogTbl _DataItem = new SentRemindersLogTbl();

                    _DataItem.ReminderID        = (_DataReader["ReminderID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ReminderID"]);
                    _DataItem.CustomerID        = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
                    _DataItem.DateSentReminder  = (_DataReader["DateSentReminder"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["DateSentReminder"]).Date;
                    _DataItem.NextPrepDate      = (_DataReader["NextPrepDate"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["NextPrepDate"]).Date;
                    _DataItem.ReminderSent      = (_DataReader["ReminderSent"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["ReminderSent"]);
                    _DataItem.HadAutoFulfilItem = (_DataReader["HadAutoFulfilItem"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["HadAutoFulfilItem"]);
                    _DataItem.HadReoccurItems   = (_DataReader["HadReoccurItems"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["HadReoccurItems"]);
                    _DataItems.Add(_DataItem);
                }
                _DataReader.Close();
            }
            _TrackerDb.Close();
            return(_DataItems);
        }
示例#11
0
/*
 *    string _connectionStr = ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString;
 *
 *    using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
 *    {
 *      string _sqlCmd = CONST_SQL_SELECT;
 *      if (!String.IsNullOrEmpty(SortBy)) _sqlCmd += " ORDER BY " + SortBy;     // Add order by string
 *      OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);                    // run the query we have built
 *      _conn.Open();
 *      OleDbDataReader _DataReader = _cmd.ExecuteReader();
 *      while (_DataReader.Read())
 *      {
 *        CustomerTrackedServiceItemsData _DataItem = new CustomerTrackedServiceItemsData();
 *
 *        _DataItem.CustomerTrackedServiceItemsID = (_DataReader["CustomerTrackedServiceItemsID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTrackedServiceItemsID"]);
 *        _DataItem.CustomerTypeID = (_DataReader["CustomerTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTypeID"]);
 *        _DataItem.ServiceTypeID = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
 *        _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
 *        _DataItems.Add(_DataItem);
 *      }
 *    }
 *    return _DataItems;
 *  }
 */
        public List <CustomerTrackedServiceItemsData> GetAllByCustomerTypeID(int pCustomerTypeID)
        {
            List <CustomerTrackedServiceItemsData> _DataItems = new List <CustomerTrackedServiceItemsData>();
            TrackerDb _TDB = new TrackerDb();

            _TDB.AddWhereParams(pCustomerTypeID, DbType.Int32, "@CustomerTypeID");
            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECT_FORCUSTOMERTYPE);

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    CustomerTrackedServiceItemsData _DataItem = new CustomerTrackedServiceItemsData();

                    _DataItem.CustomerTrackedServiceItemsID = (_DataReader["CustomerTrackedServiceItemsID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTrackedServiceItemsID"]);
                    _DataItem.CustomerTypeID = pCustomerTypeID;
                    _DataItem.ServiceTypeID  = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
                    _DataItem.Notes          = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();

                    _DataItems.Add(_DataItem);
                }
                _DataReader.Close();
            }
            _TDB.Close();
            return(_DataItems);
        }
示例#12
0
        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);
        }
示例#13
0
        public List <LogTbl> GetAll(string SortBy)
        {
            TrackerDb _TDB    = new TrackerDb();
            string    _sqlCmd = CONST_SQL_SELECT;

            _sqlCmd += (!String.IsNullOrEmpty(SortBy)) ? " ORDER BY " + SortBy : " ORDER BY DateAdded"; // add default order\

            IDataReader   _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
            List <LogTbl> _DataItems  = new List <LogTbl>();

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    LogTbl _DataItem = new LogTbl();

                    _DataItem.LogID            = (_DataReader["LogID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LogID"]);
                    _DataItem.DateAdded        = (_DataReader["DateAdded"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["DateAdded"]).Date;
                    _DataItem.UserID           = (_DataReader["UserID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["UserID"]);
                    _DataItem.SectionID        = (_DataReader["SectionID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SectionID"]);
                    _DataItem.TranactionTypeID = (_DataReader["TranactionTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["TranactionTypeID"]);
                    _DataItem.CustomerID       = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
                    _DataItem.Details          = (_DataReader["Details"] == DBNull.Value) ? string.Empty : _DataReader["Details"].ToString();
                    _DataItem.Notes            = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
                    _DataItems.Add(_DataItem);
                }
                _DataReader.Close();
            }
            _TDB.Close();
            return(_DataItems);
        }
示例#14
0
        public List <CityPrepDaysTbl> GetAllByCityId(int pCityID)
        {
            string _sqlCmd = CONST_SQL_SELECTBYCITYID;
            //if (!String.IsNullOrEmpty(SortBy))
            //  _sqlCmd += " ORDER BY " + SortBy;

            TrackerDb _TrackerDb = new TrackerDb();

            _TrackerDb.AddWhereParams(pCityID, DbType.Int32);

            List <CityPrepDaysTbl> _DataItems  = new List <CityPrepDaysTbl>();
            IDataReader            _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    CityPrepDaysTbl _DataItem = new CityPrepDaysTbl();

                    _DataItem.CityID            = pCityID;
                    _DataItem.CityPrepDaysID    = (_DataReader["CityPrepDaysID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CityPrepDaysID"]);
                    _DataItem.PrepDayOfWeekID   = (_DataReader["PrepDayOfWeekID"] == DBNull.Value) ? byte.MinValue : Convert.ToByte(_DataReader["PrepDayOfWeekID"]);
                    _DataItem.DeliveryDelayDays = (_DataReader["DeliveryDelayDays"] == DBNull.Value) ? 0 : Convert.ToInt16(_DataReader["DeliveryDelayDays"]);
                    _DataItem.DeliveryOrder     = (_DataReader["DeliveryOrder"] == DBNull.Value) ? 0 : Convert.ToInt16(_DataReader["DeliveryOrder"]);

                    _DataItems.Add(_DataItem);
                }
                _DataReader.Close();
            }
            _TrackerDb.Close();
            return(_DataItems);
        }
示例#15
0
        protected void GoBtn_Click(object sender, EventArgs e)
        {
            // construct the string with the where clause as per above parameters.
            string _strSQL = "SELECT ItemTypeTbl.ItemDesc, ROUND(SUM(OrdersTbl.QuantityOrdered),2) AS Quantity" +
                             " FROM (OrdersTbl INNER JOIN ItemTypeTbl ON OrdersTbl.ItemTypeID = ItemTypeTbl.ItemTypeID)";

            bool _ByRoastDate = ddlFilterByRoastDate.SelectedValue.Equals("RoastDate");

            if (_ByRoastDate)
            {
                _strSQL += " WHERE (OrdersTbl.RoastDate >= ?) AND (OrdersTbl.RoastDate <= ?) ";
            }
            else
            {
                _strSQL += "  WHERE (OrdersTbl.RequiredByDate >= ?) AND (OrdersTbl.RequiredByDate <= ?) ";
            }
            _strSQL += " AND (ItemTypeTbl.ServiceTypeID = 2) GROUP BY ItemTypeTbl.ItemDesc";

            TrackerDb _TDB    = new TrackerDb();
            DateTime  _dtFrom = Convert.ToDateTime(tbxDateFrom.Text); // (ddlDateFrom.SelectedValue);

            _TDB.AddWhereParams(_dtFrom, DbType.Date, "@RoastDateFrom");
            _TDB.AddWhereParams(Convert.ToDateTime(tbxDateTo.Text), DbType.Date, "@RoastDateTo"); // ddlDateTo.SelectedValue), DbType.Date, "@RoastDateTo");
            double _Dbl = _dtFrom.DayOfYear / 7;

            ViewState[CONST_WEEKDESC] = "Y" + _dtFrom.Year.ToString() + " Wk " + Convert.ToString(Math.Ceiling(_Dbl) + 1);
            ltrlDates.Text            = String.Format("{0} - From: {1} to {2}", ((_ByRoastDate) ? "Roast Date" : "Prep Date"), tbxDateFrom.Text, tbxDateTo.Text); // ddlDateFrom.SelectedValue + " " + ddlDateTo.SelectedValue;
            // zero values
            ZeroViewStateVals();
            ;
            DataSet _ds = _TDB.ReturnDataSet(_strSQL);

            gvPreperationSummary.DataSource = _ds;
            gvPreperationSummary.DataBind();
        }
示例#16
0
        public List <ActiveDeliveryData> GetActiveDeliveryDateWithDeliveryPerson(string SortBy)
        {
            List <ActiveDeliveryData> _DataItems = new List <ActiveDeliveryData>();

            TrackerDb _TDB    = new TrackerDb();
            string    _sqlCmd = CONST_SQL_SELECT_ACTIVEDELIVERIES;

            if (!String.IsNullOrEmpty(SortBy))
            {
                _sqlCmd += " ORDER BY " + SortBy;                             // Add order by string
            }
            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);  // run the query we have built

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    ActiveDeliveryData _DataItem = new ActiveDeliveryData();

                    _DataItem.RequiredByDate = (_DataReader["RequiredByDate"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["RequiredByDate"]).Date;
                    _DataItem.Person         = (_DataReader["Person"] == DBNull.Value) ? string.Empty : _DataReader["Person"].ToString();
                    _DataItem.PersonID       = (_DataReader["PersonID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PersonID"]);
                    _DataItems.Add(_DataItem);
                }
                _DataReader.Close();
            }
            _TDB.Close();

            return(_DataItems);
        }
示例#17
0
        public string GetTransactionTypeByID(Int32 pTransactionID)
        {
            string    _TransactionType = string.Empty;
            TrackerDb _TDB             = new TrackerDb();
            // for some reason this is not working so have replaced with string
            //_TDB.AddWhereParams(pTransactionID, DbType.Int32);
            string _sql = CONST_SQL_SELECTTTANSACTIONBYID;

            _sql = _sql.Replace("?", pTransactionID.ToString());

            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sql);

            if (_DataReader != null)
            {
                if (!_DataReader.Read())
                {
                    _TransactionType = (_DataReader["TransactionType"] == DBNull.Value) ? string.Empty : _DataReader["TransactionType"].ToString();
                }
                else
                {
                    _TransactionType = pTransactionID.ToString();
                }
                _DataReader.Close();
            }
            _TDB.Close();

            return(_TransactionType);
        }
示例#18
0
        /*
         *   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);
        }
示例#19
0
        public const int CONST_DEFAULT_CITYID = 1; // cape town

        public static List <CityTblData> GetAllCityTblData(string SortBy)
        {
            List <CityTblData> _ListCitys = new List <CityTblData>();
            TrackerDb          _TDB       = new TrackerDb();

            string _sqlCmd = CONST_SQL_SUMMARYDATA;

            // Add order by string
            _sqlCmd += " ORDER BY " + (!String.IsNullOrEmpty(SortBy) ? SortBy : " City");
            // run the query we have built
            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    CityTblData _CityTblItem = new CityTblData();

                    _CityTblItem.ID   = Convert.ToInt32(_DataReader["ID"]);
                    _CityTblItem.City = (_DataReader["City"] == DBNull.Value) ? "" : _DataReader["City"].ToString();

                    _ListCitys.Add(_CityTblItem);
                }
            }
            _DataReader.Close();
            _TDB.Close();

            return(_ListCitys);
        }
示例#20
0
        public List <SysDataTbl> GetAll() //(string SortBy)
        {
            List <SysDataTbl> _DataItems = new List <SysDataTbl>();
            TrackerDb         _TrackerDb = 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 = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    SysDataTbl _DataItem = new SysDataTbl();

                    _DataItem.ID = (_DataReader["ID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ID"]);
                    _DataItem.LastReoccurringDate   = (_DataReader["LastReoccurringDate"] == DBNull.Value) ? System.DateTime.MinValue : Convert.ToDateTime(_DataReader["LastReoccurringDate"]).Date;
                    _DataItem.DoReoccuringOrders    = (_DataReader["DoReoccuringOrders"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["DoReoccuringOrders"]);
                    _DataItem.DateLastPrepDateCalcd = (_DataReader["DateLastPrepDateCalcd"] == DBNull.Value) ? System.DateTime.MinValue : Convert.ToDateTime(_DataReader["DateLastPrepDateCalcd"]).Date;
                    _DataItem.MinReminderDate       = (_DataReader["MinReminderDate"] == DBNull.Value) ? System.DateTime.MinValue : Convert.ToDateTime(_DataReader["MinReminderDate"]).Date;
                    _DataItem.GroupItemTypeID       = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? TrackerDb.CONST_INVALIDID : Convert.ToInt32(_DataReader["GroupItemTypeID"]);
                    _DataItems.Add(_DataItem);
                }
                _DataReader.Close();
            }
            _TrackerDb.Close();
            return(_DataItems);
        }
示例#21
0
        /// <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);
        }
示例#22
0
        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);
        }
示例#23
0
        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);
        }
示例#24
0
        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);
        }
示例#25
0
        protected void btnFind_Click(object sender, EventArgs e)
        {
            string _strSQL = "SELECT DISTINCT OrdersTbl.OrderID, CustomersTbl.CompanyName AS CoName," +
                             " OrdersTbl.CustomerId, OrdersTbl.OrderDate, OrdersTbl.RoastDate, OrdersTbl.ItemTypeID, ItemTypeTbl.ItemDesc," +
                             " OrdersTbl.QuantityOrdered, ItemTypeTbl.ItemShortName, ItemTypeTbl.ItemEnabled, ItemTypeTbl.ReplacementID,  CityPrepDaysTbl.DeliveryOrder, " +
                             " ItemTypeTbl.SortOrder, OrdersTbl.RequiredByDate, OrdersTbl.ToBeDeliveredBy, OrdersTbl.PurchaseOrder, OrdersTbl.Confirmed, OrdersTbl.InvoiceDone, OrdersTbl.Done, OrdersTbl.Notes," +
                             " PackagingTbl.Description AS PackDesc, PackagingTbl.BGColour, PersonsTbl.Abreviation" +
                             " FROM ((((CityPrepDaysTbl RIGHT OUTER JOIN CustomersTbl ON CityPrepDaysTbl.CityID = CustomersTbl.City) RIGHT OUTER JOIN " +
                             " (OrdersTbl LEFT OUTER JOIN PersonsTbl ON OrdersTbl.ToBeDeliveredBy = PersonsTbl.PersonID) ON CustomersTbl.CustomerID = OrdersTbl.CustomerId) LEFT OUTER JOIN " +
                             "  PackagingTbl ON OrdersTbl.PackagingID = PackagingTbl.PackagingID) LEFT OUTER JOIN ItemTypeTbl ON OrdersTbl.ItemTypeID = ItemTypeTbl.ItemTypeID)" +
                             " WHERE (CustomersTbl.CompanyName LIKE '%" + tbxFindClient.Text + "%') AND (OrdersTbl.Done = false)" +
                             " ORDER BY OrdersTbl.RequiredByDate, OrdersTbl.ToBeDeliveredBy, CityPrepDaysTbl.DeliveryOrder, CustomersTbl.CompanyName, ItemTypeTbl.SortOrder";
            // sort order needs to depend on day choosen, since at the moment if there are 2 different sort orders on a day even if the day of prep is different it duplicates the item
            TrackerDb   _TDB        = new TrackerDb();
            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_strSQL);

            BuildDeliveryTable(_DataReader, false);
            _DataReader.Close();
            _TDB.Close();

/*
 *    OleDbConnection oleConn = GetTrackerOLEConnection();
 *    // open connection
 *    oleConn.Open();
 *    OleDbCommand oleCommand = new OleDbCommand(_strSQL, oleConn);
 *    OleDbDataReader oleData = oleCommand.ExecuteReader();
 *
 *    // now build the standard table
 *    BuildDeliveryTable(oleData, false);
 */
        }
示例#26
0
        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);
        }
示例#27
0
        public List <MachineConditionsTbl> GetAll(string SortBy)
        {
            List <MachineConditionsTbl> _DataItems = new List <MachineConditionsTbl>();
            TrackerDb _TrackerDb = new TrackerDb();
            string    _sqlCmd    = CONST_SQL_SELECT;

            _sqlCmd += " ORDER BY " + (String.IsNullOrEmpty(SortBy) ? "SortOrder" : SortBy);
            // params would go here if need
            IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    MachineConditionsTbl _DataItem = new MachineConditionsTbl();

                    _DataItem.MachineConditionID = (_DataReader["MachineConditionID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["MachineConditionID"]);
                    _DataItem.ConditionDesc      = (_DataReader["ConditionDesc"] == DBNull.Value) ? string.Empty : _DataReader["ConditionDesc"].ToString();
                    _DataItem.SortOrder          = (_DataReader["SortOrder"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SortOrder"]);
                    _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
                    _DataItems.Add(_DataItem);
                }
                _DataReader.Close();
            }
            _TrackerDb.Close();
            return(_DataItems);
        }
示例#28
0
        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);
        }
示例#29
0
        public List <PriceLevelsTbl> GetAll(string SortBy)
        {
            List <PriceLevelsTbl> _DataItems = new List <PriceLevelsTbl>();
            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())
                {
                    PriceLevelsTbl _DataItem = new PriceLevelsTbl();

                    #region StoreThisDataItem
                    _DataItem.PriceLevelID   = (_DataReader["PriceLevelID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PriceLevelID"]);
                    _DataItem.PriceLevelDesc = (_DataReader["PriceLevelDesc"] == DBNull.Value) ? string.Empty : _DataReader["PriceLevelDesc"].ToString();
                    _DataItem.PricingFactor  = (_DataReader["PricingFactor"] == DBNull.Value) ? 0.0 : Math.Round(Convert.ToDouble(_DataReader["PricingFactor"]), 3);
                    _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);
        }
示例#30
0
        /// <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);
        }