Csabi progtech beadandója

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//az adatbázis miatt kell:
using System.Data;
using System.Data.OleDb;

namespace progtechbeadandó
{

    //az adatbázis osztály singleton
    //excel alapú adatbázishoz:
    class DatabaseExcel
    {
        private static DatabaseExcel instance;

        private DatabaseExcel()
        {
        }

        public static DatabaseExcel GetInstance()
        {
            if (instance == null)
            {
                instance = new DatabaseExcel();
            }
            return instance;
        }

        private string GetConnectionString()
        {
            Dictionary<string, string> props = new Dictionary<string, string>();

            // XLSX - Excel 2007, 2010, 2012, 2013
            //props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
            //props["Extended Properties"] = "Excel 12.0 XML";
            //props["Data Source"] = "C:\\Progtechbeadandó\\Database.xlsx";

            // XLS - Excel 2003 and Older
            props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
            props["Extended Properties"] = "Excel 8.0";
            props["Data Source"] = "C:\\Progtechbeadandó\\items.xls";

            StringBuilder sb = new StringBuilder();

            foreach (KeyValuePair<string, string> prop in props)
            {
                sb.Append(prop.Key);
                sb.Append('=');
                sb.Append(prop.Value);
                sb.Append(';');
            }

            return sb.ToString();
        }

        public void Write(string command)
        {
            string connectionString = GetConnectionString();

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;

                //cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";
                //cmd.ExecuteNonQuery();

                //cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";
                //cmd.ExecuteNonQuery();

                //cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03');";
                //cmd.ExecuteNonQuery();

                //cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03');";
                //cmd.ExecuteNonQuery();

                //cmd.CommandText = "UPDATE [table1] SET name = 'DDDD' WHERE id = 3;";
                //cmd.ExecuteNonQuery();

                conn.Close();
            }
        }

        public DataSet Read(string queryString)
        {
            DataSet results = new DataSet();

            string connectionString = GetConnectionString();

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;

                // Get all Sheets in Excel File
                DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                // Loop through all Sheets to get data
                foreach (DataRow dr in dtSheet.Rows)
                {
                    // ide jön a lekérdezés
                    cmd.CommandText = queryString;

                    DataTable dt = new DataTable();
                    dt.TableName = "results";

                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    da.Fill(dt);

                    results.Tables.Add(dt);
                }

                cmd = null;
                conn.Close();
            }

            return results;
        }

    } //adatbázis class vége

    //.csv (txt) alapú adatbázishoz:
    class Database
    {
        private static Database instance;
        protected static string _filepath = System.IO.Path.Combine(Environment.CurrentDirectory, "items.csv");
        public static string filepath
        {
            get { return _filepath; }
            set { _filepath = value; }
        }
        public static string filenameStatic;

        public string filename
        {
            get { return filenameStatic;  }
        }

        private Database()
        { }

        public static Database GetInstance()
        {
            if (instance == null)
            {
                instance = new Database();
                fájlnévbeállítás();
            }

            return instance;
        }

        private static void fájlnévbeállítás()
        {
            filenameStatic = System.IO.Path.GetFileName(filepath);
        }

        private string GetConnectionString()
        {
            Dictionary<string, string> props = new Dictionary<string, string>();

            //csv connection string parts
            //props["Provider"] = "Microsoft.Jet.OLEDB.4.0;";
            //props["Extended Properties"] = "'text;HDR=Yes;FMT=Delimited(,)';";
            //props["Extended Properties"] = "text;HDR=Yes;FMT=Delimited";
            //props["Data Source"] = "\"" + file.DirectoryName + "\";";

            StringBuilder sb = new StringBuilder();

            foreach (KeyValuePair<string, string> prop in props)
            {
                sb.Append(prop.Key);
                sb.Append('=');
                sb.Append(prop.Value);
                sb.Append(';');
            }

            return sb.ToString();
        }

        public DataTable Read(string querystring)
        {
            //lekérdezés formátuma:
            //string.Format("SELECT * FROM [{0}]", file.Name),

            DataTable eredmeny = new DataTable();


            string fajl = System.IO.Path.GetFileName(filepath);
            string mappa = System.IO.Path.GetDirectoryName(filepath);

            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
            + "Data Source=\"" + mappa + "\\\";"
            + "Extended Properties=\"text;HDR=Yes;FMT=Delimited\"";
            //string lekerdezes = "SELECT * FROM " + fajl;

            OleDbDataAdapter Adapter = new OleDbDataAdapter(querystring, connString);

            try
            {
                Adapter.Fill(eredmeny);
            }
            catch (InvalidOperationException /*e*/)
            { }
            Adapter.Dispose();

            return eredmeny;
        }

    } //DataBaseCsv class vége


    //az előre definiált kategóriák (amik tulajdonképpen előre megírt különbféle lekérdezések eredményei) stratégia tervezési minta segítségével lettek megvalósítva
    //a 3 kategóriának (notebook-tablet-telefon osztály) van egy lekérdezés mezője, ahová a választástól függően kerül be ezen előre megírt lekérdezés osztályok példánya
    public abstract class BaseQuery
    {
        protected string _name;
        public string name
        {
            get { return _name; }
            set { _name = value; }
        }

        public BaseQuery()
        {
        }


        public abstract DataTable DoQuery();
    }

    //ide jönnek majd a BaseQueryből származtatott lekérdezések, amik az előre választható paraméterezési opciók lesznek gyakorlatilag
    //a smartphone kategóriával kapcsolatosak:
    public class AllSmartPhonesQuery : BaseQuery
    {
        public AllSmartPhonesQuery()
        {
            name = "minden okostelefon";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, PRICE FROM " + db.filename + " WHERE CATEGORY=1");
            return dt;

        }
    }

    public class CheapestSmartPhoneQuery : BaseQuery
    {
        public CheapestSmartPhoneQuery()
        {
            name = "legolcsóbb okostelefon";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, MIN(PRICE) AS PRICE FROM " + db.filename + " WHERE CATEGORY=1");
            return dt;
        }
    }

    public class MostExpensiveSmartPhoneQuery : BaseQuery
    {
        public MostExpensiveSmartPhoneQuery()
        {
            name = "legdrágább okostelefon";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, MAX(PRICE) AS PRICE FROM " + db.filename + " WHERE CATEGORY=1");
            return dt;
        }

    }

    public class AllAppleSmartPhonesQuery : BaseQuery
    {
        public AllAppleSmartPhonesQuery()
        {
            name = "Apple okostelefonok";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, PRICE FROM " + db.filename + " WHERE (CATEGORY=1) AND (MANUFACTURER LIKE \"Apple\")");
            return dt;
        }

    }

    public class AllSamsungSmartPhonesQuery : BaseQuery
    {
        public AllSamsungSmartPhonesQuery()
        {
            name = "Samsung okostelefonok";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, PRICE FROM " + db.filename + " WHERE (CATEGORY=1) AND (MANUFACTURER LIKE \"Samsung\")");
            return dt;
        }

    }

    //a tablet kategóriával kapcsolatosak:
    public class AllTabletsQuery : BaseQuery
    {
        public AllTabletsQuery()
        {
            name = "minden tablet";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, PRICE FROM " + db.filename + " WHERE CATEGORY=2");
            return dt;

        }
    }

    public class CheapestTabletQuery : BaseQuery
    {
        public CheapestTabletQuery()
        {
            name = "legolcsóbb tablet";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, MIN(PRICE) AS PRICE FROM " + db.filename + " WHERE CATEGORY=2");
            return dt;
        }
    }

    public class MostExpensiveTabletQuery : BaseQuery
    {
        public MostExpensiveTabletQuery()
        {
            name = "legdrágább tablet";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, MAX(PRICE) AS PRICE FROM " + db.filename + " WHERE CATEGORY=2");
            return dt;
        }

    }

    public class AllAppleTabletsQuery : BaseQuery
    {
        public AllAppleTabletsQuery()
        {
            name = "Apple tabletek";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, PRICE FROM " + db.filename + " WHERE (CATEGORY=2) AND (MANUFACTURER LIKE \"Apple\")");
            return dt;
        }

    }

    public class AllSamsungTabletsQuery : BaseQuery
    {
        public AllSamsungTabletsQuery()
        {
            name = "Samsung tabletek";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, PRICE FROM " + db.filename + " WHERE (CATEGORY=2) AND (MANUFACTURER LIKE \"Samsung\")");
            return dt;
        }

    }

    //a notebook kategóriával kapcsolatosak:
    public class AllNotebooksQuery : BaseQuery
    {
        public AllNotebooksQuery()
        {
            name = "minden notebook";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, PRICE FROM " + db.filename + " WHERE CATEGORY=3");
            return dt;

        }
    }

    public class CheapestNotebookQuery : BaseQuery
    {
        public CheapestNotebookQuery()
        {
            name = "legolcsóbb notebook";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, MIN(PRICE) AS PRICE FROM " + db.filename + " WHERE CATEGORY=3");
            return dt;
        }
    }

    public class MostExpensiveNotebookQuery : BaseQuery
    {
        public MostExpensiveNotebookQuery()
        {
            name = "legdrágább notebook";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();

            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, MAX(PRICE) AS PRICE FROM " + db.filename + " WHERE CATEGORY=3");
            return dt;
        }

    }

    public class AllAppleNotebooksQuery : BaseQuery
    {
        public AllAppleNotebooksQuery()
        {
            name = "Apple notebookok";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, PRICE FROM " + db.filename + " WHERE (CATEGORY=3) AND (MANUFACTURER LIKE \"Apple\")");
            return dt;
        }

    }

    public class AllSamsungNotebooksQuery : BaseQuery
    {
        public AllSamsungNotebooksQuery()
        {
            name = "Samsung notebook";
        }

        public override DataTable DoQuery()
        {
            DataTable dt = new DataTable();
            Database db = Database.GetInstance();
            dt = db.Read("SELECT MANUFACTURER, MODEL, PRICE FROM " + db.filename + " WHERE (CATEGORY=3) AND (MANUFACTURER LIKE \"Samsung\")");
            return dt;
        }

    }


    //a 3 kategória (notebook-tablet-mobil) template method segítségével lett megvalósítva
    public abstract class Category
    {
        protected List<BaseQuery> _possibilities = new List<BaseQuery>(); //ez reprezentálja a termékszűkítés menüpontjait
        public List<BaseQuery> possibilities
        {
            get { return _possibilities; }
            set { _possibilities = value; }
        }
        protected BaseQuery _currentQuery; //mező a majd végrehajtandó lekérdezésnek (stratégia minta része!)
        public BaseQuery currentQuery
        {
            get { return _currentQuery; }
            set { _currentQuery = value; }
        }
        protected DataTable _result = new DataTable(); //ebbe kerülnek majd a találatok
        public DataTable result
        {
            get { return _result; }
            set { _result = value; }
        }
        protected bool _success;
        public bool success
        {
            get { return _success; }
            set { _success = value; }
        }


        public void Run() //ez vezérli a programot, az első körben választott kategóriától függően (template method része!)
        {
            ListParameters();
            SelectParameters();
            ExecuteQuery();
            ShowResults();
            success = LastChoice();
        }

        protected abstract void ListParameters();

        private void SelectParameters()
        {
            bool b = false;
            int x = 0;
            while (!b)
            {
                Console.WriteLine("Kérlek válassz, mely lehetőség alapján szeretnéd szűkíteni a termékeket:");
                x = int.Parse(Console.ReadLine());
                if (x > 0 && x <= possibilities.Count) b = true;
            }

            currentQuery = possibilities[x-1];
        }

        private void ExecuteQuery()
        {
            try
            {
                this.result = currentQuery.DoQuery();
            }
            catch (Exception)
            {
                Console.WriteLine("Hiba történt az adatbázis olvasásakor... bocsi");
            }

        }

        private void ShowResults()
        {
            Console.WriteLine("A paraméter(ek)nek megfelelő termék(ek):");

            for (int curCol = 0; curCol < result.Columns.Count; curCol++)
            {
                Console.Write(result.Columns[curCol].ColumnName.Trim() + "\t");
            }
            for (int curRow = 0; curRow < result.Rows.Count; curRow++)
            {
                for (int curCol = 0; curCol < result.Columns.Count; curCol++)
                {
                    Console.Write(result.Rows[curRow][curCol].ToString().Trim() + "\t");
                }
                Console.WriteLine();
            }

        }

        private bool LastChoice()
        {
            int aaa = -1;
            aaa = result.Rows.Count;
            int beírt = -1;

            while (beírt > aaa && beírt < 0)
            {
                Console.WriteLine("Add meg, hogy melyik terméket szeretnéd megvásárolni (írd be a sorszámát):");
                Console.WriteLine("A \"0\" beírásával visszajuthatsz a kategória választáshoz!");
                beírt = int.Parse(Console.ReadLine());
            }

            if (beírt == 0) return false;
            else return true;
        }

    } //category class vége

    public class Notebook : Category
    {
        public Notebook()
        {
            possibilities.Add(new AllNotebooksQuery());
            possibilities.Add(new CheapestNotebookQuery());
            possibilities.Add(new MostExpensiveNotebookQuery());
            possibilities.Add(new AllAppleNotebooksQuery());
            possibilities.Add(new AllSamsungNotebooksQuery());
            currentQuery = null;
            success = false;
        }

        protected override void ListParameters()
        {
            Console.WriteLine("Választható paraméterek:");
            for (int i = 0; i < possibilities.Count; i++)
            {
                Console.WriteLine("{0}. {1}", i + 1, this.possibilities[i].name);
            }
        }

    }

    public class Tablet : Category
    {
        public Tablet()
        {
            possibilities.Add(new AllTabletsQuery());
            possibilities.Add(new CheapestTabletQuery());
            possibilities.Add(new MostExpensiveTabletQuery());
            possibilities.Add(new AllAppleTabletsQuery());
            possibilities.Add(new AllSamsungTabletsQuery());
            currentQuery = null;
            success = false;
        }

        protected override void ListParameters()
        {
            Console.WriteLine("Választható paraméterek:");
            for (int i = 0; i < possibilities.Count; i++)
            {
                Console.WriteLine("{0}. {1}", i + 1, possibilities[i].name);
            }
        }

    }

    public class SmartPhone : Category
    {
        public SmartPhone()
        {
            possibilities.Add(new AllSmartPhonesQuery(/*"minden okostelefon"*/));
            possibilities.Add(new CheapestSmartPhoneQuery());
            possibilities.Add(new MostExpensiveSmartPhoneQuery());
            possibilities.Add(new AllAppleSmartPhonesQuery());
            possibilities.Add(new AllSamsungSmartPhonesQuery());
            currentQuery = null;
            success = false;
        }

        protected override void ListParameters()
        {
            Console.WriteLine("Választható paraméterek:");
            for (int i = 0; i < possibilities.Count; i++)
            {
                Console.WriteLine("{0}. {1}", i + 1, possibilities[i].name);
            }
        }

    }

    class bolt
    {
        protected Category _notipéldány;
        public Category notipéldány
        {
            get { return _notipéldány; }
            set { _notipéldány = value; }
        }

        protected Category _táblapéldány;
        public Category táblapéldány
        {
            get { return _táblapéldány; }
            set { _táblapéldány = value; }
        }

        protected Category _telópéldány;
        public Category telópéldány
        {
            get { return _telópéldány; }
            set { _telópéldány = value; }
        }

        public bolt(Category noti, Category tábla, Category teló)
        {
            notipéldány = noti;
            táblapéldány = tábla;
            telópéldány = teló;
        }

        public void start()
        {
            while (notipéldány.success != true || táblapéldány.success != true || telópéldány.success != true)
            {
                Console.WriteLine("Üdvözöllek az Almádi Electronics Kft. boltjában!");

                int x = 0;
                while (x != 1 && x != 2 && x != 3)
                {
                    Console.WriteLine("Kérlek, válassz, hogy milyen terméket szeretnél:");
                    Console.WriteLine("1.Okostelefon | 2.Tablet | 3.Notebook");
                    x = int.Parse(Console.ReadLine());
                }

                if (x == 1)
                {
                    telópéldány.Run();
                }
                else if (x == 2)
                {
                    táblapéldány.Run();
                }
                else
                {
                    notipéldány.Run();
                }
            }

            Console.WriteLine("Sikeres vásárlás! Köszönjük!");
        }

    }


    class Program
    {
        static void Main(string[] args)
        {
            bolt shop = new bolt(new Notebook(), new Tablet(), new SmartPhone());
            shop.start();

            Console.WriteLine("Nyomj egy tetszőleges gombot a kilépéshez! Viszlát!");
            Console.ReadKey();
        }
    }
}