Champions Technologies

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Windows;

namespace ChampionsTechnologies
{
    public class ChampionsHelper
    {
        // Declare the Oledb class variables
        OleDbCommand command = new OleDbCommand();
        
        OleDbConnection connect = new OleDbConnection();
        
        OleDbDataReader reader;
        //OleDbDataAdapter adapter = new OleDbDataAdapter();
        //DataSet ds = new DataSet();

        // declare strings 
        String dbName = "StockSheet";
        String formLoadBeginString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Champions\\ChampionsTechnologies\\ChampionsTechnologies\\bin\\StockSheet.accdb";

        // DateTime variable
        DateTime dateVariable = new DateTime();
        DateTime fromdateVariable = new DateTime();
        DateTime todateVariable = new DateTime();

        // AutoCompleteStringCollection
        //AutoCompleteStringCollection theItemsList = new AutoCompleteStringCollection();

        public FrmHelper m_Helper;
        public ChampionsHelper()
        {
        }
        public ChampionsHelper(FrmHelper mHelper)
        {
            m_Helper = mHelper;
            //connect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Kamal CHA documents\\Champions\\ChampionsTechnologies\\ChampionsTechnologies\\bin\\Debug\\" + dbName + ".accdb";
            connect.ConnectionString = formLoadBeginString;
        }

        public void connOpen()
        {
            if (connect.State != ConnectionState.Open)
            {
                try
                {                    
                    connect.Open();                    
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString() + " !!! Problem opening a connection to database !!! ");
                }
            }
        }
        public void connClose()
        {
            if (connect.State != ConnectionState.Closed)
            {
                try
                {
                    connect.Close();                    
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString() + " !!! Problem closing the connection to database !!! ");
                }
            }
        }
        // 
        public void StringEmptyQuery()
        {
            reader.Close();
            m_Helper.frm_comboBoxItems.Items.Clear(); // clear if the SelectedText is empty and user wants to keep checking different dates
            m_Helper.frm_comboBoxIN.Items.Clear(); // clear so that user can check on different dates
            m_Helper.frm_comboBoxOUT.Items.Clear(); // clear so that user can check on different dates
            //ClearFields();
            command.CommandText = "SELECT Items,IN_Name,OUT_Name from " + dbName + " WHERE Date_of_Inv = #" + dateVariable.ToShortDateString() + "# ";

            reader = command.ExecuteReader();
            //populate the combobox with items for that date
            while (reader.Read())
            {
                m_Helper.frm_comboBoxItems.Items.Add(reader["Items"].ToString());
                m_Helper.frm_comboBoxIN.Items.Add(reader["IN_Name"].ToString());
                m_Helper.frm_comboBoxOUT.Items.Add(reader["OUT_Name"].ToString());
                //m_Helper.frm_comboBoxIN.Items.Add(reader["OUT_Name"].ToString());
                //m_Helper.frm_comboBoxOUT.Items.Add(reader["IN_Name"].ToString());
            }
            m_Helper.frm_lblItems.Text = "";
            m_Helper.frm_lblItems.Text = " Total items in inventory  for " + dateVariable.ToLongDateString() + " ";
            m_Helper.frm_lblItems.Text += "= " + m_Helper.frm_comboBoxItems.Items.Count.ToString() + " ";
        }
        public void clearTextfrmCombobox_Click()
        {
            
            connClose();
            connOpen();
            m_Helper.frm_comboBoxItems.Text = String.Empty;
            m_Helper.frm_comboBoxIN.Text = String.Empty;
            m_Helper.frm_comboBoxOUT.Text = String.Empty;
            ClearFields();
            StringEmptyQuery();
        }
        //
        public void InitValues()
        {
            if(m_Helper.frm_start_of_Day.Text == String.Empty)
                m_Helper.frm_start_of_Day.Text = "0";
           
            if(m_Helper.frm_in_Inventory.Text == String.Empty)
                m_Helper.frm_in_Inventory.Text = "0";

            if(m_Helper.frm_out_Inventory.Text == String.Empty)
                m_Helper.frm_out_Inventory.Text = "0";

            if(m_Helper.frm_sold.Text == String.Empty)
                m_Helper.frm_sold.Text = "0";

            if (m_Helper.frm_stock.Text == String.Empty)
                m_Helper.frm_stock.Text = "0";

            if(m_Helper.frm_cost.Text == String.Empty)
                m_Helper.frm_cost.Text = "0";
        }
        public void FormLoadBegin()
        {
            // theItemsList.Clear();
            connClose(); // close any previous connections

            connOpen();
            
            command.Connection = connect;
            

            // default date to today
            dateVariable = m_Helper.frm_monthCalendar1.TodayDate;

            // disable the todateVariable , enable it when user selects fromdateVariable
            //m_Helper.frm_monthCalendarTo.Enabled = false;

            command.CommandText = "SELECT Items,IN_Name,OUT_Name from " + dbName + " WHERE Date_of_Inv = #" + dateVariable.ToShortDateString() + "#";
            bool hasTodayRows = false;
                            
            reader = command.ExecuteReader();
            // 
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    //theItemsList.Add(reader["Items"].ToString());
                    m_Helper.frm_comboBoxItems.Items.Add(reader["Items"].ToString());
                    m_Helper.frm_comboBoxIN.Items.Add(reader["IN_Name"].ToString());
                    m_Helper.frm_comboBoxOUT.Items.Add(reader["OUT_Name"].ToString());
                }
                hasTodayRows = true;
            }
            
            reader.Close();
            command.Cancel();
            connClose();
                        
            if (!hasTodayRows)
            {
                // no rows for today so transfer from  
                // last known date to today and display a message

                // theItemsList.Clear();
                connClose(); // close any previous connections

                connOpen();

                command.Connection = connect;

                command.CommandText = "INSERT INTO " + dbName + " " +
                                            " ([Date_of_Inv]," +
                                            " Items, Start_of_Day, IN_Inv, IN_Name, OUT_Inv, OUT_Name, Sold, Stock, Cost )" +
                                            "  SELECT TOP 1 #" + dateVariable.ToShortDateString() + "#," +
                                            " Items, Start_of_Day, IN_Inv, IN_Name, OUT_Inv, OUT_Name, Sold, Stock ,Cost " +
                                            " FROM " + dbName + " " +
                                            " WHERE ( ([Date_of_Inv]) <>  ( #" + dateVariable.ToShortDateString() + "# )  )" +
                                            " ORDER BY Date_of_Inv DESC";

                reader = command.ExecuteReader();
                int count = reader.RecordsAffected;
                //                 
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        //theItemsList.Add(reader["Items"].ToString());
                        m_Helper.frm_comboBoxItems.Items.Add(reader["Items"].ToString());
                        m_Helper.frm_comboBoxIN.Items.Add(reader["IN_Name"].ToString());
                        m_Helper.frm_comboBoxOUT.Items.Add(reader["OUT_Name"].ToString());
                    }
                    hasTodayRows =false;
                } // end if (reader.HasRows)
                
                MessageBox.Show("Software has successfully transferred   " + count + "  Items from the last known date to " + dateVariable.ToLongDateString() + " ");

            } // end if (!hasTodayRows)

            reader.Close();
            command.Cancel();
            connClose();

            connOpen();
            StringEmptyQuery();
            connClose();

        } // end public void FormLoadBegin()

        public void ClearFields()
        {
            m_Helper.frm_start_of_Day.Clear();
            m_Helper.frm_in_Inventory.Clear();
            m_Helper.frm_out_Inventory.Clear();
            m_Helper.frm_sold.Clear();
            m_Helper.frm_stock.Clear();
            m_Helper.frm_cost.Clear();
            m_Helper.frm_comboBoxIN.Text = String.Empty;
            m_Helper.frm_comboBoxOUT.Text = String.Empty;
        }
        public void ClearTexts()
        {
            m_Helper.frm_lblItems.Text = "";
            m_Helper.frm_lblResult.Text = "";
        }
        public void CloseForm()
        {
            m_Helper.frm_comboBoxItems.Items.Clear();
            m_Helper.frm_comboBoxIN.Items.Clear();
            m_Helper.frm_comboBoxOUT.Items.Clear();
            ClearFields();
            ClearTexts();
            reader.Close();
            command.Cancel();
            connClose();
            
        }
        public void DeleteItem()
        {
            connClose(); // close previous connections if any
            
            connOpen(); // open the connection again
            if ((dateVariable == DateTime.MinValue) || (m_Helper.frm_comboBoxItems.Text == String.Empty))
            {
                MessageBox.Show(" PLEASE SELECT A VALID DATE AND ITEM TO DELETE ");
            }
            else
            {
                reader.Close();
                command.CommandText =   "DELETE Date_of_Inv, Items , " +
                                        " Start_of_Day, IN_Inv, IN_Name, OUT_Inv, OUT_Name, Sold, Stock, Cost " +
                                        " FROM " + dbName + " " +
                                        " WHERE (((Date_of_Inv) = #" + dateVariable.ToShortDateString() + "#) " +
                                        " AND ((Items) = '" + m_Helper.frm_comboBoxItems.Text + "'))";

                DialogResult dialogResult = MessageBox.Show(" Delete ''" + m_Helper.frm_comboBoxItems.Text + "'' from Stock data for " + dateVariable.ToLongDateString() + " ", "DELETE", MessageBoxButtons.YesNo);
                
                if (dialogResult == DialogResult.Yes)
                {
                    reader = command.ExecuteReader();
                    ClearFields();
                    
                    m_Helper.frm_comboBoxItems.Text = String.Empty;
                    m_Helper.frm_comboBoxIN.Text = String.Empty;
                    m_Helper.frm_comboBoxOUT.Text = String.Empty;

                    connClose();
                    connOpen();
                    StringEmptyQuery();                                        
                }
                else if (dialogResult == DialogResult.No)
                {
                    // these lines are commented from previous delete function
                    // turn them on if needed
                    //m_Helper.frm_comboBoxItems.Items.Clear();
                    //m_Helper.frm_comboBoxItems.Text = String.Empty;
                    //ClearFields();
                }

            } // end else ((dateVariable == DateTime.MinValue) || (m_Helper.frm_comboBoxItems.SelectedText == String.Empty))
            connClose();
        } // end DeleteItem()

        public void monthCalendar1_DateChanged()
        {
            ClearTexts();
            ClearFields();

            dateVariable = m_Helper.frm_monthCalendar1.SelectionStart; // Assign the dateVariable            
            m_Helper.frm_lblResult.Text = " You Selected : " + dateVariable.ToLongDateString() + " ";
            
            //m_Helper.frm_comboBoxItems.SelectedText = String.Empty;
            connClose();
            connOpen();

            if (m_Helper.frm_comboBoxItems.Text == String.Empty) // select all items into comboboxItems.Items
            {
                //reader.Close();
                //m_Helper.frm_comboBoxItems.Items.Clear(); // clear if the SelectedText is empty and user wants to keep checking different dates
                ////ClearFields();
                //command.CommandText = "SELECT Items from " + dbName + " WHERE Date_of_Inv = #" + dateVariable.ToShortDateString() + "# ";

                //reader = command.ExecuteReader();
                ////populate the combobox with items for that date
                //while (reader.Read())
                //{
                //    m_Helper.frm_comboBoxItems.Items.Add(reader["Items"].ToString());
                //}
                //m_Helper.frm_lblItems.Text = "";
                //m_Helper.frm_lblItems.Text = " Total items in inventory  for " + dateVariable.ToLongDateString() + " ";
                //m_Helper.frm_lblItems.Text += "= " + m_Helper.frm_comboBoxItems.Items.Count.ToString() + " ";
                StringEmptyQuery();

            }
            else // m_Helper.frm_comboBoxItems.Text has some text
            {
                reader.Close();
                ClearFields();
                String selText = m_Helper.frm_comboBoxItems.Text;
                
                
                command.CommandText =   "SELECT Items , " +
                                        " Start_of_Day, IN_Inv, IN_Name, OUT_Inv, OUT_Name, Sold, Stock ,Cost " +
                                        " FROM " + dbName + " " +
                                        " WHERE (((Date_of_Inv) = #" + dateVariable.ToShortDateString() + "#) " +
                                        " AND ((Items) = '" + m_Helper.frm_comboBoxItems.Text + "'))";

                
                reader = command.ExecuteReader();
                //m_Helper.frm_comboBoxItems.Items.Clear(); //clear all items in list and add items in while for that date
                //m_Helper.frm_comboBoxItems.SelectedText = String.Empty;
                while (reader.Read())
                {
                    //m_Helper.frm_comboBoxItems.Items.Add(reader["Items"].ToString());
                    m_Helper.frm_start_of_Day.Text = reader["Start_of_Day"].ToString();
                    m_Helper.frm_in_Inventory.Text = reader["IN_Inv"].ToString();
                    m_Helper.frm_comboBoxIN.Text = reader["IN_Name"].ToString();
                    m_Helper.frm_out_Inventory.Text = reader["OUT_Inv"].ToString();
                    m_Helper.frm_comboBoxOUT.Text = reader["OUT_Name"].ToString();
                    m_Helper.frm_sold.Text = reader["sold"].ToString();
                    m_Helper.frm_stock.Text = reader["stock"].ToString();
                    m_Helper.frm_cost.Text = reader["cost"].ToString();
                } //end while (reader.Read())

                // get the IN_Name and OUT_Name from the current selected Item and date
                String selIN_Name = m_Helper.frm_comboBoxIN.Text;
                String selOUT_Name = m_Helper.frm_comboBoxOUT.Text;
                StringEmptyQuery();
                m_Helper.frm_comboBoxItems.Text = selText;
                m_Helper.frm_comboBoxIN.Text = selIN_Name; // reproduce
                m_Helper.frm_comboBoxOUT.Text = selOUT_Name; // reproduce

            } // end if (m_Helper.frm_comboBoxItems.Text == String.Empty) // select all items into comboboxItems.Items

            // before closing the function
            //ClearFields();
            //ClearTexts();
            //reader.Close();
            //command.Cancel();
            //dateVariable = DateTime.MinValue;
            connClose();
        } // end public void monthCalendar1_DateChanged()

        public void comboBoxItems_SelectedIndexChanged()
        {
            ClearTexts();
            ClearFields();          
            
            connClose();
            connOpen();

            m_Helper.frm_lblResult.Text = " You Selected : " + dateVariable.ToLongDateString() + " ";

           
            if (m_Helper.frm_comboBoxItems.Text == String.Empty)
            {
                //reader.Close();
                //m_Helper.frm_comboBoxItems.Items.Clear();
                //command.CommandText = "SELECT Items from " + dbName + " WHERE Date_of_Inv = #" + dateVariable.ToShortDateString() + "# ";

                //reader = command.ExecuteReader();
                
                //while (reader.Read())
                //{
                //    m_Helper.frm_comboBoxItems.Items.Add(reader["Items"].ToString());
                //}
                //m_Helper.frm_lblItems.Text = "";
                //m_Helper.frm_lblItems.Text = " Total items in inventory  for " + dateVariable.ToLongDateString() + " ";
                //m_Helper.frm_lblItems.Text += "= " + m_Helper.frm_comboBoxItems.Items.Count.ToString() + " ";
                StringEmptyQuery();
            }
            else
            {
                reader.Close();
                ClearFields();

                command.CommandText =   "SELECT Items , " +
                                        " Start_of_Day, IN_Inv, IN_Name, OUT_Inv, OUT_Name, Sold, Stock, Cost " +
                                        " FROM " + dbName + " " +
                                        " WHERE (((Date_of_Inv) = #" + dateVariable.ToShortDateString() + "#) " +
                                        " AND ((Items) = '" + m_Helper.frm_comboBoxItems.Text + "'))";

                reader = command.ExecuteReader();
                String selText = m_Helper.frm_comboBoxItems.Text;
                //String selIN_Name = m_Helper.frm_comboBoxIN.Text;
                //String selOUT_Name = m_Helper.frm_comboBoxOUT.Text;

                //m_Helper.frm_comboBoxItems.Items.Clear();
                while (reader.Read())
                {
                    //m_Helper.frm_comboBoxItems.Items.Add(reader["Items"].ToString());
                    if (m_Helper.frm_comboBoxItems.Items.Contains(reader["Items"].ToString()))
                    {
                        //m_Helper.frm_comboBoxItems.Items.Add(reader["Items"].ToString());
                        m_Helper.frm_comboBoxItems.Text = selText;
                        m_Helper.frm_start_of_Day.Text = reader["Start_of_Day"].ToString();
                        m_Helper.frm_in_Inventory.Text = reader["IN_Inv"].ToString();
                        m_Helper.frm_comboBoxIN.Text = reader["IN_Name"].ToString();
                        m_Helper.frm_out_Inventory.Text = reader["OUT_Inv"].ToString();
                        m_Helper.frm_comboBoxOUT.Text = reader["OUT_Name"].ToString();
                        m_Helper.frm_sold.Text = reader["sold"].ToString();
                        m_Helper.frm_stock.Text = reader["stock"].ToString();
                        m_Helper.frm_cost.Text = reader["cost"].ToString();
                    }
                    else
                    {
                        m_Helper.frm_comboBoxItems.Items.Add(reader["Items"].ToString());
                        //m_Helper.frm_comboBoxIN.Items.Add(reader["IN_Name"].ToString());
                        //m_Helper.frm_comboBoxOUT.Items.Add(reader["OUT_Name"].ToString());
                    }
                    
                } // end while (reader.Read())                

                m_Helper.frm_lblItems.Text = "";
                m_Helper.frm_lblItems.Text = " Total items in inventory  for " + dateVariable.ToLongDateString() + " ";
                m_Helper.frm_lblItems.Text += "= " + m_Helper.frm_comboBoxItems.Items.Count.ToString() + " ";
            } // end if (m_Helper.frm_comboBoxItems.Text == String.Empty)


            reader.Close();
            connClose();
        }// end public void comboBoxItems_SelectedIndexChanged()

        public void btnAdd_Click()
        {
            InitValues();
            connClose(); // close previous connections if any

            connOpen(); // open the connection again
            if ((dateVariable == DateTime.MinValue) || (m_Helper.frm_comboBoxItems.Text == String.Empty))
            {
                MessageBox.Show(" PLEASE SELECT A VALID DATE AND ITEM TO ADD ");
            }
            else
            {
                reader.Close();
                command.CommandText = "SELECT Items " +                                       
                                       " FROM " + dbName + " " +
                                       " WHERE (((Date_of_Inv) = #" + dateVariable.ToShortDateString() + "#) " +
                                       " AND ((Items) = '" + m_Helper.frm_comboBoxItems.Text + "'))";

                reader = command.ExecuteReader();
                String selText = m_Helper.frm_comboBoxItems.Text;
                while (reader.Read())
                {
                    if (m_Helper.frm_comboBoxItems.Items.Contains(reader["Items"].ToString()))
                    {
                        MessageBox.Show(" YOU ARE ENTERING THE SAME ITEM TWICE, CHANGE DATE OR ITEM ");
                        m_Helper.frm_comboBoxItems.Text = String.Empty;
                        return;
                    }
                }
                int tempStock = int.Parse(m_Helper.frm_start_of_Day.Text) + int.Parse(m_Helper.frm_in_Inventory.Text) - int.Parse(m_Helper.frm_out_Inventory.Text) - int.Parse(m_Helper.frm_sold.Text);
                String tmpStock = tempStock.ToString();
                reader.Close();
                command.CommandText = "INSERT INTO " + dbName + " " +
                        " ([Date_of_Inv], Items, Start_of_Day, IN_Inv, IN_Name, OUT_Inv, OUT_Name, Sold, Stock, Cost )" +
                        " VALUES (#" + dateVariable.ToShortDateString() + "#, " +
                        " '" + m_Helper.frm_comboBoxItems.Text + "'," +
                        " " + int.Parse(m_Helper.frm_start_of_Day.Text) + ", " +
                        " " + int.Parse(m_Helper.frm_in_Inventory.Text) + ", " +
                        " '" + m_Helper.frm_comboBoxIN.Text + "'," +
                        " " + int.Parse(m_Helper.frm_out_Inventory.Text) + ", " +
                        " '" + m_Helper.frm_comboBoxOUT.Text + "'," +
                        " " + int.Parse(m_Helper.frm_sold.Text) + ", " +
                        " " + tempStock + ", " +
                        " " + int.Parse(m_Helper.frm_cost.Text) + ") ";

                command.ExecuteReader();
                m_Helper.frm_stock.Text = tmpStock;
                m_Helper.frm_lblResult.Text = "";
                m_Helper.frm_lblResult.Text = "You added the following Item : " + m_Helper.frm_comboBoxItems.Text + " ";
                m_Helper.frm_lblResult.Text += " on " + dateVariable.ToLongDateString() + " ";                
            } // end else ((dateVariable == DateTime.MinValue) || (m_Helper.frm_comboBoxItems.Text == String.Empty))
            connClose();
        } // end public void btnAdd_Click

        public void btnChange_Click()
        {
            InitValues();
            connClose(); // close previous connections if any

            connOpen(); // open the connection again
            if ((dateVariable == DateTime.MinValue) || (m_Helper.frm_comboBoxItems.Text == String.Empty))
            {
                MessageBox.Show(" PLEASE SELECT A VALID DATE AND ITEM TO UPDATE ");
            }
            else
            {
                m_Helper.frm_stock.Enabled = false;
                int tempStock = int.Parse(m_Helper.frm_start_of_Day.Text) + int.Parse(m_Helper.frm_in_Inventory.Text) - int.Parse(m_Helper.frm_out_Inventory.Text) - int.Parse(m_Helper.frm_sold.Text);
                
                reader.Close();
                
                command.CommandText = "UPDATE " + dbName + " " +
                    " SET " +
                    " Start_of_Day = " + int.Parse(m_Helper.frm_start_of_Day.Text) + ", " +
                     " IN_Inv = " + int.Parse(m_Helper.frm_in_Inventory.Text) + ", " +
                     " IN_Name = '" + m_Helper.frm_comboBoxIN.Text + "', " +
                     " OUT_Inv = " + int.Parse(m_Helper.frm_out_Inventory.Text) + ", " +
                     " OUT_Name = '" + m_Helper.frm_comboBoxOUT.Text + "', " +                      
                     " Sold = " + int.Parse(m_Helper.frm_sold.Text) + ", " +
                     "Stock = " +tempStock+ ", " +
                     "Cost = " + int.Parse(m_Helper.frm_cost.Text) + "  "+
                     " WHERE " +
                     " (([Date_of_Inv] = #" + dateVariable.ToShortDateString() + "# )" +
                     " AND " +
                     " (Items = '" + m_Helper.frm_comboBoxItems.Text + "' ))"; 
                
                command.ExecuteReader();
                m_Helper.frm_stock.Text = "" +tempStock+"";
                m_Helper.frm_lblResult.Text = "";
                m_Helper.frm_lblResult.Text = "You changed the following Item : " + m_Helper.frm_comboBoxItems.Text + " ";
                m_Helper.frm_lblResult.Text += " on " + dateVariable.ToLongDateString() + " ";
            } // end else ((dateVariable == DateTime.MinValue) || (m_Helper.frm_comboBoxItems.Text == String.Empty))
            connClose();

        } // end public void btnChange_Click()

        public void monthCalendarFrom_DateChanged()
        {
            fromdateVariable =  m_Helper.frm_monthCalendarFrom.SelectionStart; // Assign the from Date
            //m_Helper.frm_lblResult.Text = "SELECT A VALID DATE TO TRANSFER DATA BY SELECTING A DATE FROM TODATE CALENDAR";
            // enable the toDateVariable
            //m_Helper.frm_monthCalendarTo.Enabled = true;
        } // end public void monthCalendarFrom_DateChanged()

        public void monthCalendarTo_DateChanged()
        {
            todateVariable = m_Helper.frm_monthCalendarTo.SelectionStart; // Assign the todateVariable

            //m_Helper.frm_monthCalendarTo.Enabled = false;
            // validate both from and to dates
            if (fromdateVariable.ToShortDateString() == todateVariable.ToShortDateString())
            {
                MessageBox.Show("Enter a different date to transfer data, you cannot transfer data to the same day");
                return;
            } // end if
            
            connClose(); // close any previous connections
            connOpen();                
            reader.Close();
                
            command.CommandText = "SELECT Items,IN_Name,OUT_Name from " + dbName + " WHERE Date_of_Inv = #" + todateVariable.ToShortDateString() + "#";
            //bool hasTodayRows = false;

            reader = command.ExecuteReader();
            int count = reader.RecordsAffected;
            if (reader.HasRows)
            {
                MessageBox.Show("You have Items in database for " + todateVariable.ToLongDateString() + " cannot transfer because items may get repeated ");
                //hasTodayRows = true;
                reader.Close();
                command.Cancel();
                connClose();
                return;
            } // end if (reader.HasRows)


            else 
            {
                // no rows for today so transfer from  
                // last known date to today and display a message

                // theItemsList.Clear();
                connClose(); // close any previous connections

                connOpen();
                reader.Close();
                command.Connection = connect;

                //command.CommandText = "INSERT INTO " + dbName + " " +
                //                        " ([Date_of_Inv], Items, Start_of_Day, IN_Inv, IN_Name, OUT_Inv, OUT_Name, Sold, Stock, Cost )" +
                //                        "  SELECT #" + todateVariable.ToShortDateString() + "# ," +
                //                        " Items, Start_of_Day, IN_Inv, IN_Name, OUT_Inv, OUT_Name, Sold, Stock ,Cost " +
                //                        " FROM " + dbName + " " +
                //                        " WHERE ((Date_of_Inv) = #" + fromdateVariable.ToShortDateString() + "#) ";

                command.CommandText = "INSERT INTO " + dbName + " " +
                                        " ([Date_of_Inv], Items, Start_of_Day, IN_Inv, IN_Name, OUT_Inv, OUT_Name, Sold, Stock, Cost )" +
                                        "  SELECT #" + todateVariable.ToShortDateString() + "# ," +
                                        " Items, Start_of_Day, IN_Inv, IN_Name, OUT_Inv, OUT_Name, Sold, Stock ,Cost " +
                                        " FROM " + dbName + " " +
                                        " WHERE ((Date_of_Inv) = #" + fromdateVariable.ToShortDateString() + "#) ORDER BY Items ASC  ";
                reader = command.ExecuteReader();

                count = reader.RecordsAffected;
                MessageBox.Show("You successfully transferred  " + count + "  Items from " + fromdateVariable.ToLongDateString() + " to " + todateVariable.ToLongDateString() + " ");
                
            } // end else
         

            reader.Close();
            command.Cancel();
            connClose();
       
        } // end public void monthCalendarTo_DateChanged()

        public void btndaily_Stock()
        {
            // theItemsList.Clear();
            connClose(); // close any previous connections

            connOpen();

            command.Connection = connect;


            // default date to today
            //dateVariable = m_Helper.frm_monthCalendar1.TodayDate;            

            command.CommandText = "SELECT Items , Start_of_Day, IN_Inv, IN_Name, OUT_Inv, OUT_Name, Sold, Stock from " +
                                    "" + dbName + " WHERE Date_of_Inv = #" + dateVariable.ToShortDateString() + "#";
            bool hasTodayRows = false;

            reader = command.ExecuteReader();
            // 
            if (reader.HasRows)
            {
                Form2 f2 = new Form2();
                f2.DailyStock = reader; // Assign the OleDBDataReader 
                f2.DateReader = dateVariable.Day.ToString() +"/"+ dateVariable.Month.ToString() + "/" + dateVariable.Year.ToString();
                f2.loadDataGrid();
                f2.Show();
                //f2.PassReader = reader;
                //f2.dtReader = dateVariable.ToShortDateString();
                //f2.Show();

                //while (reader.Read())
                //{
                //    //theItemsList.Add(reader["Items"].ToString());
                //    m_Helper.frm_comboBoxItems.Items.Add(reader["Items"].ToString());
                //    m_Helper.frm_comboBoxIN.Items.Add(reader["IN_Name"].ToString());
                //    m_Helper.frm_comboBoxOUT.Items.Add(reader["OUT_Name"].ToString());
                //}
                hasTodayRows = true;
            }
            else
            {
                MessageBox.Show("You do not have any items to view for " + dateVariable.ToLongDateString() + "");
            }

            reader.Close();
            command.Cancel();
            connClose();
        } // end public void daily_Stock()

    } // end public class ChampionsHelper
} // end namespace ChampionsTechnologies