DB irasu sudarymas

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;

namespace DB_Lab1_IrasuSudarymas
{
    class Program
    {
        #region Listai
        static List<string> moteruVardai = new List<string>()
            { "Gabija", "Emilija", "Gabriele", "Kamile", "Ugne",
            "Austeja", "Ieva", "Viktorija", "Greta", "Urte",
            "Vilte", "Karolina", "Rugile", "Migle", "Evelina",
            "Goda", "Auguste", "Deimante", "Ema", "Liepa",
            "Saule", "Paulina", "Aiste", "Kotryna", "Karina" };
        static List<string> vyruVardai = new List<string>()
            { "Lukas", "Matas", "Nojus", "Dominykas", "Dovydas",
            "Mantas", "Rokas", "Ignas", "Martynas", "Jonas",
            "Tomas", "Jokubas", "Arnas", "Kajus", "Deividas",
            "Karolis", "Paulius", "Justas", "Danielius", "Benas",
            "Emilis", "Augustas", "Domantas", "Domas", "Gabrielius" };
        static List<string> moteruPavardes = new List<string>()
            { "Makereviciute", "Butkute", "Stonkute", "Ivanovic", "Kerpauskiene",
            "Navickyte", "Paulauskiene", "Rozeviciute", "Katinaite", "Paskeviciute",
            "Petrauskiene", "Zukauskiene", "Urbone", "Bukyte", "Petrulyte",
            "Jankauskaite", "Kazlauskiene", "Miskiene", "Miciute", "Kutniauskaite" };
        static List<string> vyruPavardes = new List<string>()
            { "Alijevas", "Askauskas", "Eidukevicius", "Adomenas", "Imbrasas",
            "Jankavicius", "Cekavicius", "Gailius", "Gurevicius", "Ivaskevicius",
            "Zukas", "Zvirblis", "Jankauskas", "Norkus", "Butkevicius",
            "Teisutis", "Steikunas", "Tarvydas", "Mackevicius", "Malakauskas" };
        static List<string> moteruPareigos = new List<string>() { "kambarine", "administratore" };
        static List<string> vyruPareigos = new List<string>() { "administratorius", "santechnikas" };

        static List<string> viesbuciuPavadinimai = new List<string>() {
                "Babilonas", "Magnus", "Amberton Cozy", "Metropolis", "Daugirdas Old City",
                "Europa Royale", "Hermis", "Amberton Hotel", "Bohema", "Memel Hotel",
                "Navalis", "Old Mill", "Aribe", "Morena", "Pajurio Vieskelis",
                "Panorama", "Urbihop", "Ecotel", "Corner", "Old Town Trio"};
        static List<string> miestai = new List<string>() { "Klaipėda", "Kaunas", "Vilnius", "Šiauliai" };
        static List<string> gatves = new List<string>() { "Miško g.", "Vyturių g.", "Tilto g.", "Islandijos pl.", "Nemenčinės g.", "Aukštoji g.", "Pušų g.", "Žalgirio g.", "Aušros g.", "Gėlių g." };
        #endregion

        static void Main(string[] args)
        {
            atliekamaSql();
        }

        static void atliekamaSql()
        {
            using (var writer = new StreamWriter(new FileStream(@"..\..\atliekamaSql.txt", FileMode.OpenOrCreate), Encoding.UTF8))
            {
                Random rnd = new Random();
                List<int> Kambariai = new List<int>() { 1, 2, 3, 9, 15, 16, 17, 20, 21, 22, 23, 32, 33, 34, 35 };
                for (int i = 1; i < 36; i++)
                {

                    int kambarys = rnd.Next(Kambariai.Count);
                    writer.WriteLine($"insert into `atliekama` (fk_Kambario_Prieziuraid_Kambario_Prieziura, fk_Kambarysid_Kambarys) values " +
                        $"('{i}', '{Kambariai.ElementAt(kambarys)}');");
                }
            }
        }

        static void prieziuraSql()
        {
            using (var writer = new StreamWriter(new FileStream(@"..\..\prieziuraSql.txt", FileMode.OpenOrCreate), Encoding.UTF8))
            {
                Random rnd = new Random();
                for (int i = 1; i < 36; i++)
                {
                    DateTime pradzia = new DateTime(2019, 1, 1, 7, 0, 0);
                    DateTime pabaiga = new DateTime(2019, 1, 7, 15, 0, 0);
                    int dienuTarpas = (pabaiga - pradzia).Days;
                    DateTime data = pradzia.AddDays(rnd.Next(dienuTarpas));

                    writer.WriteLine($"insert into `kambario_prieziura` (id_Kambario_Prieziura, tvarkymo_data, tvarkymo_valanda, fk_DarbuotojastabNr) values " +
                        $"('{i}', '{data.Date}', '{data.Hour+":"+data.Minute}', '1111');");
                }
            }
        }

        static void rezervacijaSql()
        {
            using (var writer = new StreamWriter(new FileStream(@"..\..\rezervacijaSql.txt", FileMode.OpenOrCreate), Encoding.UTF8))
            {
                Random rnd = new Random();
                int klientoId = 20;
                for (int i = 1; i < 21; i++)
                {
                    DateTime pradzia = new DateTime(2018, 7, 1);
                    DateTime pabaiga = new DateTime(2018, 12, 31);
                    int dienuTarpas = (pabaiga - pradzia).Days;
                    DateTime randomData = pradzia.AddDays(rnd.Next(dienuTarpas));
                    int naktys = rnd.Next(2, 7);
                    int kambarysId = rnd.Next(1, 35);

                    writer.WriteLine($"insert into `rezervacija` (id_Rezervacija, atvykimo_data, naktys, atlikta, fk_Saskaitaid_Saskaita, fk_Kambarysid_Kambarys, fk_Klientasid_Klientas) values " +
                        $"('{i}', '{randomData}', '{naktys}', '2018', '{i}', '{kambarysId}', '{klientoId--}');");
                }
            }
        }

        static void saskaitaSql()
        {
            using (var writer = new StreamWriter(new FileStream(@"..\..\saskaitaSql.txt", FileMode.OpenOrCreate), Encoding.UTF8))
            {
                Random rnd = new Random();
                int klientas = 20;
                for (int i = 1; i < 21; i++)
                {
                    double poKablelio = rnd.NextDouble();
                    int priesKableli = rnd.Next(50, 181);
                    double suma = priesKableli + poKablelio;
                    int statusas = rnd.Next(1, 4);
                    DateTime pradzia = new DateTime(2018, 1, 1);
                    DateTime pabaiga = new DateTime(2018, 6, 30);
                    int dienuTarpas = (pabaiga - pradzia).Days;
                    var randomData = pradzia.AddDays(rnd.Next(dienuTarpas));

                    writer.WriteLine($"insert into `saskaita` (id_Saskaita, suma, sudarymo_data, statusas, fk_Klientasid_Klientas) values " +
                        $"('{i}', '{suma.ToString("F2")}', '{randomData.Date}', '{statusas}', '{klientas--}');");
                }
            }
        }

        private static void klientasSql()
        {
            using (var writer = new StreamWriter(new FileStream(@"..\..\klientasSql.txt", FileMode.OpenOrCreate), Encoding.UTF8))
            {
                String vardas = null;
                String pavarde = null;
                /*String lowerVardas = null;
                String lowerPavarde = null;
                string mergedName = null;*/
                Random rnd = new Random();
                int lytis;
                for (int i = 1; i < 21; i++)
                {
                    lytis = rnd.Next(2);
                    if (lytis == 0)
                    {
                        vardas = vyruVardai.ElementAt(i - 1);
                        pavarde = vyruPavardes.ElementAt(i - 1);
                    }
                    else if (lytis == 1)
                    {
                        vardas = moteruVardai.ElementAt(i - 1);
                        pavarde = moteruPavardes.ElementAt(i - 1);
                    }
                    /*lowerVardas = vardas.ToLower();
                    lowerPavarde = pavarde.ToLower();
                    mergedName = lowerVardas + "." + lowerPavarde + "@e.mail";*/
                    writer.WriteLine($"insert into `klientas` (id_Klientas, vardas, pavarde, epastas) values ('{i}', '{vardas}', '{pavarde}', '{vardas.ToLower() + "." + pavarde.ToLower() + "@e.mail"}');");
                }
            }
        }

        private static void kambarysSql()
        {
            using (var writer = new StreamWriter(new FileStream(@"..\..\kambarysSql.txt", FileMode.OpenOrCreate), Encoding.UTF8))
            {
                string irasas = null;
                Random rnd = new Random();
                int kambarioId = 1;
                int kambarioNr;
                for (int i = 1; i < 21; i++)
                {
                    int kambariuSk = rnd.Next(3);
                    for (int j = 0; j <= kambariuSk; j++)
                    {
                        int kambarioTipas = rnd.Next(1, 6);
                        switch (kambarioTipas)
                        {
                            case 1:
                                kambarioNr = rnd.Next(101, 151);
                                irasas = $"insert into `kambarys` (id_Kambarys, numeris, vonia, dusas, virtuvele, balkonas, televizorius, tipas, fk_Viesbutisid_Viesbutis) " +
                                    $"values ('{kambarioId++}', '{kambarioNr}', '0', '1', '0', '0', '1', '1', '{i}');";
                                break;
                            case 2:
                                kambarioNr = rnd.Next(201, 251);
                                irasas = $"insert into `kambarys` (id_Kambarys, numeris, vonia, dusas, virtuvele, balkonas, televizorius, tipas, fk_Viesbutisid_Viesbutis) " +
                                    $"values ('{kambarioId++}', '{kambarioNr}', '0', '1', '0', '1', '1', '2', '{i}');";
                                break;
                            case 3:
                                kambarioNr = rnd.Next(301, 351);
                                irasas = $"insert into `kambarys` (id_Kambarys, numeris, vonia, dusas, virtuvele, balkonas, televizorius, tipas, fk_Viesbutisid_Viesbutis) " +
                                    $"values ('{kambarioId++}', '{kambarioNr}', '0', '1', '1', '1', '1', '3', '{i}');";
                                break;
                            case 4:
                                kambarioNr = rnd.Next(401, 451);
                                irasas = $"insert into `kambarys` (id_Kambarys, numeris, vonia, dusas, virtuvele, balkonas, televizorius, tipas, fk_Viesbutisid_Viesbutis) " +
                                    $"values ('{kambarioId++}', '{kambarioNr}', '1', '0', '1', '1', '1', '4', '{i}');";
                                break;
                            case 5:
                                kambarioNr = rnd.Next(501, 551);
                                irasas = $"insert into `kambarys` (id_Kambarys, numeris, vonia, dusas, virtuvele, balkonas, televizorius, tipas, fk_Viesbutisid_Viesbutis) " +
                                    $"values ('{kambarioId++}', '{kambarioNr}', '1', '1', '1', '1', '1', '5', '{i}');";
                                break;
                        }
                        writer.WriteLine(irasas);
                    }
                }
            }
        }

        private static void darbuotojasSql()
        {
            using (var writer = new StreamWriter(new FileStream(@"..\..\darbuotojaiSql.txt", FileMode.OpenOrCreate), Encoding.UTF8))
            {
                Random rnd = new Random();
                for (int i = 1; i < 21; i++)
                {
                    for (int j = 0; j < 2; j++)
                    {
                        int lytis = rnd.Next(2);
                        int rndTabelis = rnd.Next(1000, 10000);
                        int rndPareiga = rnd.Next(2);
                        DateTime pradzia = new DateTime(2009, 1, 1);
                        int viso = (DateTime.Today - pradzia).Days;
                        var rndData = pradzia.AddDays(rnd.Next(viso));
                        int rndVardas = rnd.Next(25);
                        int rndPavarde = rnd.Next(20);
                        int rndNumeris = rnd.Next(1000000, 10000000);

                        if (lytis == 0)
                        {
                            writer.WriteLine($"insert into `darbuotojas` (tabNr, pareigos, dirba_nuo, vardas, pavarde, telefono_nr, fk_Viesbutisid_Viesbutis) values " +
                                $"('{rndTabelis}', '{vyruPareigos.ElementAt(rndPareiga)}', '{rndData}', '{vyruVardai.ElementAt(rndVardas)}', '{vyruPavardes.ElementAt(rndPavarde)}', '{"+3706"+rndNumeris}', '{i});");
                        }
                        else if(lytis == 1)
                        {
                            writer.WriteLine($"insert into `darbuotojas` (tabNr, pareigos, dirba_nuo, vardas, pavarde, telefono_nr, fk_Viesbutisid_Viesbutis) values " +
                                $"('{rndTabelis}', '{moteruPareigos.ElementAt(rndPareiga)}', '{rndData}', '{moteruVardai.ElementAt(rndVardas)}', '{moteruPavardes.ElementAt(rndPavarde)}', '{"+3706"+rndNumeris}', '{i});");
                        }
                    }
                }
            }
        }

        private static void viesbutisSql()
        {
            using (var writer = new StreamWriter(new FileStream(@"..\..\viesbutisSql.txt", FileMode.OpenOrCreate), Encoding.UTF8))
            {
                Random rnd = new Random();
                for (int i = 1; i < 21; i++)
                {
                    int rndMiestas = rnd.Next(0, 4);
                    int rndGatve = rnd.Next(0, 10);
                    int rndNumeris = rnd.Next(5, 80);
                    writer.WriteLine($"insert into `viesbutis` (id_Viesbutis, pavadinimas, miestas, gatve, namo_nr, telefono_nr, epastas) values" +
                        $"('{i}', '{viesbuciuPavadinimai.ElementAt(i - 1)}', '{miestai.ElementAt(rndMiestas)}', '{gatves.ElementAt(rndGatve)}', '{rndNumeris}', '+3706', 'info@.lt');");
                }
            }
        }
    }


}