public ObservableCollection <clsItem> itemsCollection()
        {
            clsDataAccess db;
            ObservableCollection <clsItem> col_Items;

            db = new clsDataAccess();
            string  sSQL;                 //Holds an SQL statement
            int     iRet = 0;             //Number of return values
            DataSet ds   = new DataSet(); //Holds the return values
            clsItem items;                //Used to load the return values into the combo box

            sSQL      = "SELECT ItemCode, ItemDesc, Cost " + "FROM ItemDesc";
            col_Items = new ObservableCollection <clsItem>();

            ds = db.ExecuteSQLStatement(sSQL, ref iRet);

            //Creates Flight objects based on the data pulled from the query than adds the object to a list
            for (int i = 0; i < iRet; i++)
            {
                items = new clsItem();

                items.ItemCode = ds.Tables[0].Rows[i][0].ToString();
                items.ItemDesc = ds.Tables[0].Rows[i]["ItemDesc"].ToString();
                items.Cost     = ds.Tables[0].Rows[i]["Cost"].ToString();

                col_Items.Add(items);
            }
            return(col_Items);
        }
Exemple #2
0
        /// <summary>
        /// Adds a new invoice to the database
        /// </summary>
        /// <param name="date"></param>
        /// <param name="totalCharge"></param>
        /// <param name="items"></param>
        public void addInvoice(string date, string totalCharge, ObservableCollection <clsItem> items)
        {
            try
            {
                db = new clsDataAccess();
                int iRet = 0;

                string sSQL = "INSERT INTO Invoices( InvoiceDate, TotalCharge) VALUES(#" +
                              date + "#, " + totalCharge + ")";
                db.ExecuteNonQuery(sSQL);

                string sSQLInvoiceNumber = "SELECT MAX(InvoiceNum) FROM Invoices";

                DataSet newInvoice    = db.ExecuteSQLStatement(sSQLInvoiceNumber, ref iRet);
                string  invoiceNumber = newInvoice.Tables[0].Rows[0][0].ToString();

                int i = 1;
                foreach (clsItem item in items)
                {
                    string sSQLLineItem = "INSERT INTO LineItems(InvoiceNum, LineItemNum, ItemCode) " +
                                          "VALUES('" + invoiceNumber + "', '" + i + "' , '" + item.ItemCode + "')";
                    db.ExecuteNonQuery(sSQLLineItem);
                    i += 1;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
            }
        }
Exemple #3
0
        /// <summary>
        /// Returns the full list of invoices from the database
        /// </summary>
        /// <returns></returns>
        public ObservableCollection <clsInvoice> invoicesCollection()
        {
            try
            {
                string sSQL;     //Holds an SQL statement
                int    iRet = 0; //Number of return values
                ds = new DataSet();
                db = new clsDataAccess();
                ObservableCollection <clsInvoice> col_Invoices = new ObservableCollection <clsInvoice>();
                clsInvoice invoice;

                sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
                       "FROM Invoices";
                ds = db.ExecuteSQLStatement(sSQL, ref iRet);

                for (int i = 0; i < iRet; i++)
                {
                    invoice = new clsInvoice();

                    invoice.InvoiceNum  = ds.Tables[0].Rows[i][0].ToString();
                    invoice.InvoiceDate = ds.Tables[0].Rows[i]["InvoiceDate"].ToString();
                    invoice.TotalCharge = ds.Tables[0].Rows[i]["TotalCharge"].ToString();

                    col_Invoices.Add(invoice);
                }
                return(col_Invoices);
            }
            catch (Exception ex)
            {
                throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
            }
        }
Exemple #4
0
        DataSet ds;       // Dataset to hold the returned data from queries

        /// <summary>
        /// Returns the full list of jewelry items from the database
        /// </summary>
        /// <returns></returns>
        public ObservableCollection <clsItem> itemsCollection()
        {
            try
            {
                db = new clsDataAccess();
                ObservableCollection <clsItem> col_Items = new ObservableCollection <clsItem>();
                string sSQL;        //Holds an SQL statement
                int    iRet = 0;    //Number of return values
                ds = new DataSet(); //Holds the return values
                clsItem items;      //Used to load the return values into the combo box
                sSQL = "SELECT ItemCode, ItemDesc, Cost " + "FROM ItemDesc";

                ds = db.ExecuteSQLStatement(sSQL, ref iRet);

                //Creates item objects based on the data pulled from the query than adds the object to a list
                for (int i = 0; i < iRet; i++)
                {
                    items = new clsItem();

                    items.ItemCode = ds.Tables[0].Rows[i][0].ToString();
                    items.ItemDesc = ds.Tables[0].Rows[i]["ItemDesc"].ToString();
                    items.Cost     = ds.Tables[0].Rows[i]["Cost"].ToString();

                    col_Items.Add(items);
                }
                return(col_Items);
            }
            catch (Exception ex)
            {
                throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
            }
        }
Exemple #5
0
        /// <summary>
        /// Returns the collection of items from a certain invoice
        /// </summary>
        /// <param name="invoiceNumber"></param>
        /// <returns></returns>
        public ObservableCollection <clsItem> invoiceItems(string invoiceNumber)
        {
            try
            {
                db = new clsDataAccess();
                ObservableCollection <clsItem> col_Items = new ObservableCollection <clsItem>();
                List <string> itemCode = new List <string>();
                string        sSQL;     //Holds an SQL statement
                int           iRet = 0; //Number of return values
                ds = new DataSet();     //Holds the return values
                clsItem items;          //Used to load the return values into the combo box
                sSQL = "SELECT ItemCode FROM LineItems "
                       + "WHERE InvoiceNum = " + invoiceNumber;

                ds = db.ExecuteSQLStatement(sSQL, ref iRet);

                // adds all of the different item codes on the invoice to a list
                for (int i = 0; i < iRet; i++)
                {
                    itemCode.Add(ds.Tables[0].Rows[i]["ItemCode"].ToString());
                }

                if (itemCode.Count > 0)
                {
                    foreach (string code in itemCode)
                    {
                        sSQL = "SELECT ItemCode, ItemDesc, Cost FROM ItemDesc "
                               + "WHERE ItemCode = '" + code + "'";

                        ds = db.ExecuteSQLStatement(sSQL, ref iRet);

                        items = new clsItem();

                        items.ItemCode = ds.Tables[0].Rows[0][0].ToString();
                        items.ItemDesc = ds.Tables[0].Rows[0]["ItemDesc"].ToString();
                        items.Cost     = ds.Tables[0].Rows[0]["Cost"].ToString();

                        col_Items.Add(items);
                    }
                }
                return(col_Items);
            }
            catch (Exception ex)
            {
                throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
            }
        }
Exemple #6
0
        /// <summary>
        /// This SQL statement gets item names of all available items
        /// </summary>
        /// <param name="sItemCode"></param>
        /// <returns>Item names</returns>
        public static clsInvoice getInvoice(int invoiceNumber)
        {
            try
            {
                //local variables to store returned data
                clsDataAccess ds      = new clsDataAccess();
                clsInvoice    invoice = new clsInvoice();
                DataSet       dataSet = new DataSet();
                ObservableCollection <clsItem> list = new ObservableCollection <clsItem>();

                //set invoice object attribute invoice number
                invoice.setInvoiceNumber(invoiceNumber);

                //get invoice date using invoice number
                string sSQL = "SELECT InvoiceDate FROM Invoices WHERE InvoiceNum = " + invoiceNumber;
                invoice.setInvoiceDate(ds.ExecuteScalarSQL(sSQL).ToString());

                //get line items
                int iRef = 0;
                sSQL = "SELECT LineItems.ItemCode, ItemDesc.ItemDesc, ItemDesc.Cost FROM ItemDesc " +
                       "INNER JOIN LineItems ON ItemDesc.ItemCode = LineItems.ItemCode WHERE LineItems.InvoiceNum = " + invoiceNumber;
                dataSet = ds.ExecuteSQLStatement(sSQL, ref iRef);
                foreach (DataRow dr in dataSet.Tables[0].Rows)
                {
                    clsItem item = new clsItem();
                    item.sItemCode = dr[0].ToString();
                    item.sItemDesc = dr[1].ToString();
                    item.sCost     = dr[2].ToString();
                    list.Add(item);
                }
                invoice.setLineItemList(list);

                return(invoice);
            }
            catch (Exception ex)
            {
                throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
                                    MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message);
            }
        }