· 8 years ago · May 08, 2018, 05:46 PM
1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5using System.Threading.Tasks;
6using MySql.Data.MySqlClient;
7
8namespace sql1
9{
10 class Program
11 {
12 public static Random random = new Random();
13
14 static void Main(string[] args)
15 {
16 Random rnd = new Random();
17
18 string connectionString = "Data Source=localhost;Initial Catalog=samoposluga;User ID=root;Password=";
19 MySqlConnection conn = new MySqlConnection(connectionString);
20 conn.Open();
21
22 MySqlCommand command = conn.CreateCommand();
23
24 #region uklanjanje tabela
25
26 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`ukradeniProizvodi`";
27 executeNonQuery(command);
28
29 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`detaljiRaÄuna`";
30 executeNonQuery(command);
31
32 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`proizvodiSamoposluge`";
33 executeNonQuery(command);
34
35 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`raÄun`";
36 executeNonQuery(command);
37
38 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`naÄinPlaćanja`";
39 executeNonQuery(command);
40
41 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`VipKorisnici`";
42 executeNonQuery(command);
43
44 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`samoposluge`";
45 executeNonQuery(command);
46
47 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`menadžeri`";
48 executeNonQuery(command);
49
50 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`vrsteSamoposluga`";
51 executeNonQuery(command);
52
53 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`zaposleni`";
54 executeNonQuery(command);
55
56 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`proizvodi`";
57 executeNonQuery(command);
58
59 command.CommandText = "DROP TABLE IF EXISTS `samoposluga`.`kategorijeProizvoda`";
60 executeNonQuery(command);
61
62 #endregion
63
64 #region kreiranje tabela
65
66 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`kategorijeProizvoda` ("
67 + "`idKategorijeProizvoda` INT NOT NULL AUTO_INCREMENT, "
68 + "`naziv` VARCHAR(255) NOT NULL, "
69 + "PRIMARY KEY(`idKategorijeProizvoda`),"
70 + "UNIQUE INDEX `naziv` (`naziv`))";
71 executeNonQuery(command);
72
73 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`proizvodi` ("
74 + "`idProizvoda` INT NOT NULL AUTO_INCREMENT,"
75 + "`naziv` VARCHAR(255) NOT NULL,"
76 + "`prodajnaCijena` DECIMAL NOT NULL,"
77 + "`cijenaDobavljaÄa` DECIMAL NOT NULL,"
78 + "`rokTrajanja` DATE NULL,"
79 + "`idKategorijeProizvoda` INT NOT NULL,"
80 + "`koliÄina` INT NOT NULL,"
81 + "PRIMARY KEY(`idProizvoda`),"
82 + "INDEX `fk_proizvodi_kategorijeProizvoda1_idx` (`idKategorijeProizvoda` ASC),"
83 + "CONSTRAINT `fk_proizvodi_kategorijeProizvoda1`"
84 + "FOREIGN KEY(`idKategorijeProizvoda`)"
85 + "REFERENCES `samoposluga`.`kategorijeProizvoda` (`idKategorijeProizvoda`))";
86 executeNonQuery(command);
87
88 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`zaposleni` ("
89 + "`idZaposlenog` INT NOT NULL AUTO_INCREMENT,"
90 + "`ime` VARCHAR(255) NOT NULL,"
91 + "`prezime` VARCHAR(255) NOT NULL,"
92 + "`jmbg` VARCHAR(255) NOT NULL,"
93 + "`telefon` INT NOT NULL,"
94 + "`plata` INT NOT NULL,"
95 + "PRIMARY KEY(`idZaposlenog`),"
96 + "UNIQUE INDEX `jmbg` (`jmbg`))";
97 executeNonQuery(command);
98
99 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`vrsteSamoposluga` ("
100 + "`idVrsteSamoposluge` INT NOT NULL AUTO_INCREMENT,"
101 + "`naziv` VARCHAR(255) NOT NULL,"
102 + "PRIMARY KEY(`idVrsteSamoposluge`),"
103 + "UNIQUE INDEX `naziv` (`naziv`))";
104 executeNonQuery(command);
105
106 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`menadžeri` ("
107 + "`idMenadžera` INT NOT NULL AUTO_INCREMENT,"
108 + "`ime` VARCHAR(255) NOT NULL,"
109 + "`prezime` VARCHAR(255) NOT NULL,"
110 + "`jmbg` VARCHAR(255) NOT NULL,"
111 + "`telefon` INT NOT NULL,"
112 + "`plata` INT NOT NULL,"
113 + "PRIMARY KEY(`idMenadžera`),"
114 + "UNIQUE INDEX `jmbg` (`jmbg`))";
115 executeNonQuery(command);
116
117 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`samoposluge` ("
118 + "`idSamoposluge` INT NOT NULL AUTO_INCREMENT,"
119 + "`broj` INT NOT NULL,"
120 + "`idVrsteSamoposluge` INT NOT NULL,"
121 + "`idMenadžera` INT NOT NULL,"
122 + "`opština` VARCHAR(255) NOT NULL,"
123 + "PRIMARY KEY(`idSamoposluge`),"
124 + "UNIQUE INDEX `broj` (`broj`),"
125 + "INDEX `fk_samoposluge_vrsteSamoposluga1_idx` (`idVrsteSamoposluge` ASC),"
126 + "INDEX `fk_samoposluge_menadžeri1_idx` (`idMenadžera` ASC),"
127 + "CONSTRAINT `fk_samoposluge_vrsteSamoposluga1`"
128 + "FOREIGN KEY(`idVrsteSamoposluge`)"
129 + "REFERENCES `samoposluga`.`vrsteSamoposluga` (`idVrsteSamoposluge`),"
130 + "CONSTRAINT `fk_samoposluge_menadžeri1`"
131 + "FOREIGN KEY(`idMenadžera`)"
132 + "REFERENCES `samoposluga`.`menadžeri` (`idMenadžera`))";
133 executeNonQuery(command);
134
135 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`VipKorisnici` ("
136 + "`brojKartice` INT NOT NULL AUTO_INCREMENT,"
137 + "`ime` VARCHAR(255) NOT NULL,"
138 + "`prezime` VARCHAR(255) NOT NULL,"
139 + "`jmbg` VARCHAR(255) NOT NULL,"
140 + "`telefon` INT NOT NULL,"
141 + "PRIMARY KEY(`brojKartice`),"
142 + "UNIQUE INDEX `jmbg` (`jmbg`))";
143 executeNonQuery(command);
144
145 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`naÄinPlaćanja` ("
146 + "`idNaÄinaPlaćanja` INT NOT NULL AUTO_INCREMENT,"
147 + "`naziv` VARCHAR(255) NOT NULL,"
148 + "PRIMARY KEY (`idNaÄinaPlaćanja`),"
149 + "UNIQUE INDEX `naziv` (`naziv`))";
150 executeNonQuery(command);
151
152 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`raÄun` ("
153 + "`idRaÄuna` INT NOT NULL AUTO_INCREMENT,"
154 + "`datum` DATE NOT NULL,"
155 + "`idZaposlenog` INT NOT NULL,"
156 + "`idNaÄinaPlaćanja` INT NOT NULL,"
157 + "`brojKartice` INT NULL,"
158 + "PRIMARY KEY(`idRaÄuna`),"
159 + "INDEX `fk_raÄun_zaposleni1_idx` (`idZaposlenog` ASC),"
160 + "INDEX `fk_raÄun_VipKorisnici1_idx` (`brojKartice` ASC),"
161 + "INDEX `fk_raÄun_naÄinPlaćanja1_idx` (`idNaÄinaPlaćanja` ASC),"
162 + "CONSTRAINT `fk_raÄun_zaposleni1`"
163 + "FOREIGN KEY(`idZaposlenog`)"
164 + "REFERENCES `samoposluga`.`zaposleni` (`idZaposlenog`),"
165 + "CONSTRAINT `fk_raÄun_VipKorisnici1`"
166 + "FOREIGN KEY(`brojKartice`)"
167 + "REFERENCES `samoposluga`.`VipKorisnici` (`brojKartice`),"
168 + "CONSTRAINT `fk_raÄun_naÄinPlaćanja1`"
169 + "FOREIGN KEY(`idNaÄinaPlaćanja`)"
170 + "REFERENCES `samoposluga`.`naÄinPlaćanja` (`idNaÄinaPlaćanja`))";
171 executeNonQuery(command);
172
173 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`proizvodiSamoposluge` ("
174 + "`idProizvodiSamoposluge` INT NOT NULL AUTO_INCREMENT,"
175 + "`koliÄina` INT NOT NULL,"
176 + "`idSamoposluge` INT NOT NULL,"
177 + "`idProizvoda` INT NOT NULL,"
178 + "PRIMARY KEY(`idProizvodiSamoposluge`),"
179 + "INDEX `fk_samoposluge_has_proizvodi_proizvodi1_idx` (`idProizvoda` ASC),"
180 + "INDEX `fk_samoposluge_has_proizvodi_samoposluge1_idx` (`idSamoposluge` ASC),"
181 + "CONSTRAINT `fk_samoposluge_has_proizvodi_samoposluge1`"
182 + "FOREIGN KEY(`idSamoposluge`)"
183 + "REFERENCES `samoposluga`.`samoposluge` (`idSamoposluge`),"
184 + "CONSTRAINT `fk_samoposluge_has_proizvodi_proizvodi1`"
185 + "FOREIGN KEY(`idProizvoda`)"
186 + "REFERENCES `samoposluga`.`proizvodi` (`idProizvoda`))";
187 executeNonQuery(command);
188
189 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`detaljiRaÄuna` ("
190 + "`koliÄina` INT NOT NULL,"
191 + "`cijena` DECIMAL NOT NULL,"
192 + "`idRaÄuna` INT NOT NULL,"
193 + "`cijenaDobavljaÄa` DECIMAL NOT NULL,"
194 + "`idProizvodiSamoposluge` INT NOT NULL,"
195 + "INDEX `fk_detaljiRaÄuna_raÄun1_idx` (`idRaÄuna` ASC),"
196 + "INDEX `fk_detaljiRaÄuna_proizvodiSamoposluge1_idx` (`idProizvodiSamoposluge` ASC),"
197 + "CONSTRAINT `fk_detaljiRaÄuna_raÄun1`"
198 + "FOREIGN KEY(`idRaÄuna`)"
199 + "REFERENCES `samoposluga`.`raÄun` (`idRaÄuna`),"
200 + "CONSTRAINT `fk_detaljiRaÄuna_proizvodiSamoposluge1`"
201 + "FOREIGN KEY(`idProizvodiSamoposluge`)"
202 + "REFERENCES `samoposluga`.`proizvodiSamoposluge` (`idProizvodiSamoposluge`))";
203 executeNonQuery(command);
204
205 command.CommandText = "CREATE TABLE IF NOT EXISTS `samoposluga`.`ukradeniProizvodi` ("
206 + "`koliÄina` INT NOT NULL,"
207 + "`datum` DATE NOT NULL,"
208 + "`idProizvoda` INT NOT NULL,"
209 + "`idUkradenogProizvoda` INT NOT NULL AUTO_INCREMENT,"
210 + "`cijenaDobavljaÄa` VARCHAR(255) NOT NULL,"
211 + "INDEX `fk_ukradeniProizvodi_proizvodi1_idx` (`idProizvoda` ASC),"
212 + "PRIMARY KEY(`idUkradenogProizvoda`),"
213 + "CONSTRAINT `fk_ukradeniProizvodi_proizvodi1`"
214 + "FOREIGN KEY(`idProizvoda`)"
215 + "REFERENCES `samoposluga`.`proizvodi` (`idProizvoda`))";
216 executeNonQuery(command);
217
218 #endregion
219
220 #region popunjavanje tabela
221
222 //Kategorije proizvoda
223 string[] kategorijeProizvoda = { "hrana", "piće", "bijela tehnika", "kozmetika", "igraÄke", "party program", "Å¡kola i kancelarija", "auto program" };
224 for (int i = 0; i < kategorijeProizvoda.Length; i++)
225 {
226 command.CommandText = "INSERT INTO `samoposluga`.`kategorijeproizvoda` (`naziv`) VALUES('" + kategorijeProizvoda[i] + "')";
227 executeNonQuery(command);
228 }
229
230 //Proizvodi
231 double kupovnaCena;
232 double prodajnaCena;
233 for (int i = 0; i < 50; i++)
234 {
235 kupovnaCena = rnd.Next(1, 10000);
236 prodajnaCena = kupovnaCena * 1.40;
237 command.CommandText = "INSERT INTO `samoposluga`.`proizvodi` (`naziv`, `prodajnaCijena`, `cijenaDobavljaÄa`, `rokTrajanja`, `idKategorijeProizvoda`, `koliÄina`) VALUES('" + randStr(rnd.Next(3, 14)) + "', '" + prodajnaCena + "', '" + kupovnaCena + "', '" + dtfordb(DateTime.Now.AddDays(rnd.Next(1, 365))) + "', '" + rnd.Next(1, 9) + "', '" + rnd.Next(50000) + "')";
238 executeNonQuery(command);
239 }
240
241 //Vrste samoposluga
242 string[] vrsteSamoposluga = { "minimarket", "supermarket", "megamarket", "hipermarket" };
243 for (int i = 0; i < vrsteSamoposluga.Length; i++)
244 {
245 command.CommandText = "INSERT INTO `samoposluga`.`vrstesamoposluga` (`naziv`) VALUES ('" + vrsteSamoposluga[i] + "')";
246 executeNonQuery(command);
247 }
248
249 //Menadžeri
250 string[] imena = { "Marko", "Janko", "Jovana", "Milica", "Todor", "Milovan", "Petar", "Zoran", "Goran", "Dragan", "Jelena", "Ana", "Mia", "Kristina", "Lazar", "Miroslav", "Mitar", "Ilija", "Rajko", "Nedo", "Neda" };
251 string[] prezimena = { "Marković", "Janković", "Jović", "Milić", "Todorović", "Milovanović", "Petrović", "Zoranović", "Goranović", "Draganić", "Jelić", "Janić", "Mićić", "Krstić", "Lazarević", "Mikić", "Mirić", "Ilić", "Rajković", "Nedić", "Nikić" };
252 for (int i = 0; i < 10; i++)
253 {
254 command.CommandText = "INSERT INTO `samoposluga`.`menadžeri` (`ime`, `prezime`, `jmbg`, `telefon`, `plata`) VALUES('" + imena[rnd.Next(imena.Length)] + "', '" + prezimena[rnd.Next(prezimena.Length)] + "', '" + randomLong(13) + "', '" + rnd.Next(111111111, 999999999) + "', '" + 25000 + "')";
255 executeNonQuery(command);
256 }
257
258 //NaÄini plaćanja
259 string[] nacinPlacanja = { "keš", "kartica" };
260 for (int i = 0; i < nacinPlacanja.Length; i++)
261 {
262 command.CommandText = "INSERT INTO `samoposluga`.`naÄinplaćanja` (`naziv`) VALUES ('" + nacinPlacanja[i] + "')";
263 executeNonQuery(command);
264 }
265
266 //Zaposleni
267 for (int i = 0; i < 20; i++)
268 {
269 command.CommandText = "INSERT INTO `samoposluga`.`zaposleni` (`ime`, `prezime`, `jmbg`, `telefon`, `plata`) VALUES('" + imena[rnd.Next(imena.Length)] + "', '" + prezimena[rnd.Next(prezimena.Length)] + "', '" + randomLong(13) + "', '" + rnd.Next(111111111, 999999999) + "', '" + 25000 + "')";
270 executeNonQuery(command);
271 }
272
273 string[] gradovi = { "Beograd","Valjevo","Vranje","ZajeÄar","Zrenjanin","Jagodina","Kragujevac",
274 "Kraljevo","KruÅ¡evac","Leskovac","Loznica","NiÅ¡","Novi Pazar", "Novi Sad","PanÄevo","Požarevac",
275 "PriÅ¡tina","Smederevo","Sombor","Sremska Mitrovica","Subotica","Užice","ÄŒaÄak","Å abac","Pirot" };
276 //Samoposluge
277 for (int i = 0; i < 5; i++)
278 {
279 command.CommandText = "INSERT INTO `samoposluga`.`samoposluge` (`broj`, `idVrsteSamoposluge`, `idMenadžera`, `opština`) VALUES('" + i + "', '" + rnd.Next(1, 4) + "', '" + (i+1) + "', '" + gradovi[rnd.Next(gradovi.Length)] + "')";
280 executeNonQuery(command);
281 }
282
283 //VIP korisnici
284 for (int i = 0; i < 5; i++)
285 {
286 command.CommandText = "INSERT INTO `samoposluga`.`vipkorisnici` (`ime`, `prezime`, `jmbg`, `telefon`) VALUES('" + imena[rnd.Next(imena.Length)] + "', '" + prezimena[rnd.Next(prezimena.Length)] + "', '" + randomLong(13) + "', '" + rnd.Next(111111111, 999999999) + "')";
287 executeNonQuery(command);
288 }
289
290 //RaÄun
291 int placanje;
292 for (int i = 0; i < 10; i++)
293 {
294 placanje = rnd.Next(0, 3);
295 if(placanje == 1)
296 {
297 command.CommandText = "INSERT INTO `samoposluga`.`racun` (`datum`, `idZaposlenog`, `idNacinaPlacanja`, `brojKartice`) VALUES('" + dtfordb(RandomDay()) + "', '" + rnd.Next(1, 20) + "', '" + 2 + "', '" + rnd.Next(1, 5) + "')";
298 }
299 else if(placanje == 2)
300 {
301 command.CommandText = "INSERT INTO `samoposluga`.`racun` (`datum`, `idZaposlenog`, `idNacinaPlacanja`, `brojKartice`) VALUES('" + dtfordb(RandomDay()) + "', '" + rnd.Next(1, 20) + "', '" + 1 + "', '" + rnd.Next(1, 5) + "')";
302 }
303 else
304 {
305 command.CommandText = "INSERT INTO `samoposluga`.`racun` (`datum`, `idZaposlenog`, `idNacinaPlacanja`) VALUES('" + dtfordb(RandomDay()) + "', '" + rnd.Next(1, 20) + "', '" + 1 + "')";
306 }
307 executeNonQuery(command);
308 }
309
310 //ProizvodiSamoposluge
311 for (int i = 0; i < 10; i++)
312 {
313 command.CommandText = "INSERT INTO `samoposluga`.`proizvodiSamoposluge` (`kolicina`, `idSamoposluge`, `idProizvoda`) VALUES('" + rnd.Next(1, 1000) + "', '" + rnd.Next(1, 5) + "', '" + rnd.Next(1, 50) + "')";
314 executeNonQuery(command);
315 }
316
317 //Ukradeni proizvodi
318 for (int i = 0; i < 5; i++)
319 {
320 command.CommandText = "INSERT INTO `samoposluga`.`ukradeniProizvodi` (`kolicina`, `datum`, `idProizvoda`, `cijenaDobavljaca`) VALUES('" + rnd.Next(1, 100) + "', '" + dtfordb(RandomDay()) + "', '" + rnd.Next(1, 50) + "', '" + rnd.Next(1, 10000) + "')";
321 executeNonQuery(command);
322 }
323
324 //Detalji raÄuna
325 for (int i = 0; i < 50; i++)
326 {
327 kupovnaCena = rnd.Next(1, 10000);
328 prodajnaCena = kupovnaCena * 1.40;
329 command.CommandText = "INSERT INTO samoposluga.`detaljiracuna` (`kolicina`, `cijena`, `idRacuna`, `cijenaDobavljaca`, `idProizvodiSamoposluge`) VALUES('" + rnd.Next(1, 20) + "', '" + prodajnaCena + "', '" + rnd.Next(1, 10) + "', '" + kupovnaCena + "', '" + rnd.Next(1, 10) + "')";
330 executeNonQuery(command);
331 }
332
333 #endregion
334
335 conn.Close();
336 }
337
338 public static string dtfordb(DateTime dt)
339 {
340 return dt.Year + "-" + dt.Month + "-" + dt.Day + " " + dt.TimeOfDay;
341 }
342
343 public static string randomLong(int length)
344 {
345 const string chars = "0123456789";
346 return new string(Enumerable.Repeat(chars, length)
347 .Select(s => s[random.Next(s.Length)]).ToArray());
348 }
349
350 public static string randStr(int length)
351 {
352 const string chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
353 return new string(Enumerable.Repeat(chars, length)
354 .Select(s => s[random.Next(s.Length)]).ToArray());
355 }
356
357 public static DateTime RandomDay()
358 {
359 DateTime start = new DateTime(2010, 10, 10);
360 int range = (DateTime.Today - start).Days;
361 return start.AddDays(random.Next(range));
362 }
363
364 public static void executeNonQuery(MySqlCommand commandsw)
365 {
366 bool b = false;
367
368 while (!b)
369 {
370 try
371 {
372 if (!commandsw.Connection.Ping() || commandsw.Connection.State == System.Data.ConnectionState.Closed)
373 {
374 commandsw.Connection.Open();
375 }
376 commandsw.ExecuteNonQuery();
377 b = true;
378 }
379 catch
380 {
381 Console.WriteLine(DateTime.Now + "Vrtimo se u petlji upisa u bazu");
382 Console.WriteLine(DateTime.Now + " " + commandsw.CommandText);
383 b = false;
384 }
385 }
386 }
387
388 public static System.Data.DataSet executeSelect(MySqlCommand command)
389 {
390 bool b = false;
391 System.Data.DataSet cas = new System.Data.DataSet();
392
393 while (!b)
394 {
395 try
396 {
397 if (!command.Connection.Ping() || command.Connection.State == System.Data.ConnectionState.Closed)
398 {
399 command.Connection.Open();
400 }
401 MySqlDataAdapter adapter = new MySqlDataAdapter(command.CommandText, command.Connection);
402 adapter.Fill(cas);
403 b = true;
404 }
405 catch
406 {
407 Console.WriteLine(DateTime.Now + "Vrtimo se u petlji SELECT u bazi");
408 Console.WriteLine(DateTime.Now + " " + command.CommandText);
409 b = false;
410 }
411 }
412 return cas;
413 }
414 }
415}