====== Portal społecznościowy GdziePoPiwo?pl (t) ======
[[kruzeltomasz@gmail.com|Tomasz Krużel]],
[[krzysiek861@wp.pl|Krzysztof Lelito]],
[[phobo@gmail.com|Janusz Lisiecki]]

==== Streszczenie ====
Projekt bazy danych wykorzystanej w stronie internetowej http://www.gdziepopiwo.pl.

==== Sformułowanie zadania projektowego ====
Celem projektu jest stworzenie strony internetowej na której będzie można znaleźć najtańsze piwo w okolicy. Nasza strona będzie oparta o rozbudowany system CMS. Użytkownik mógłby w łatwy sposób wyszukać gdzie w jego okolicy można dostać konkretne piwo i w jakiej cenie. Baza cen, asortymentu i sklepów byłaby modyfikowana przez samych zarejestrowanych użytkowników portalu. Integracja z API Google Maps ułatwi lokalizację sklepów.

==== Analiza stanu wyjściowego ====
Jeśli chodzi o polski rynek, to w momencie projektowania, nie było rozwiązania takiego jak nasze. Istnieją jedynie portale dotyczące innych produktów, np. strona dotyczący cen paliwa http://www.dobaku.pl/
adresowana do kierowców gdzie możemy znaleźć najtańszą stację z paliwem w Krakowie. Jedyne rozwiązanie związane z piwem jakie udało nam się znaleźć to http://www.twenga.pl/ które jest portalem o ogólnej tematyce wyszukującym ceny jedynie spośród sklepów internetowych. Obie z powyższych stron zarabiają na reklamach wystawianych na stronie. Kolejny powiązany z tematem portal to http://napiwo.org/ oferująca mapę Krakowa wraz z naniesionymi klubami i cenami piwa, witryna ta ma bardzo wąski zakres funkcjonalności i jest stworzona w celach całkowicie niekomercyjnych.

==== Analiza wymagań użytkownika (wstępna) ====
**Główne funkcjonalności (Must have):**
  * rejestracja użytkowników, aktywacja, ostrzeżenia za łamanie regulaminu, banowanie, przypomnienia hasła.
  * wyszukiwanie piwa według lokalizacji, przedziału cenowego, nazwy, opakowania.
  * statystyki odwiedzin strony.
  * integracja z API Google Maps, wyświetlenie mapy z zaznaczonymi sklepami i ich cenami.

**Dodatkowe funkcjonalności (Could have):**
  * integracja z portalami społecznościowymi (wykop, facebook, blip, nasza klasa).
  * informacje o promocjach.

==== Określenie scenariuszy użycia ====

   - **Scenariusze dla gościa**:
     - Rejestracja
     - Wyszukiwanie piwa
     - Przeglądanie informacji o sklepie
   - **Scenariusze dla użytkownika**:
     - Dostęp do serwisu:
       * Logowanie
       * Wylogowanie
       * Przypomnienie hasła
     - Zarządzanie kontem:
       * Zmiana danych użytkownika
     - Edycja bazy sklepów i asortymentu:
       * Dodawanie sklepów
       * Dodawanie piw
       * Edycja piw
       * Usuwanie piw
     - Wyszukiwanie piwa
       * Wyszukiwanie z użyciem podstawowej wyszukiwarki
       * Wyszukiwanie z użyciem zaawansowanej wyszukiwarki
     - Przeglądanie informacji o sklepie
   - **Scenariusze dla administratora**:
    - Zarządzanie piwami
       * Dodawanie piwa
       * Edycja piwa
       * Usuwanie piwa
    - Zarządzanie piwami w sklepach
       * Dodawanie piwa do sklepu
       * Edycja korelacji piwa ze sklepem
       * Usuwanie piwa ze sklepu
    - Zarządzanie miastami
       * Dodawanie miasta
       * Edycja miasta
       * Usuwanie miasta
    - Zarządzanie korporacjami
       * Dodawanie korporacji
       * Edycja korporacji
       * Usuwanie korporacji
    - Zarządzanie promocjami
       * Dodawanie promocji
       * Edycja promocji
       * Usuwanie promocji
    - Zarządzanie sklepami
       * Dodawanie sklepu
       * Edycja sklepu
       * Usuwanie sklepu
    - Zarządzanie użytkownikami:
       * Zmiana danych użytkownika
       * Banowanie użytkownika
    - Przeglądanie ostrzeżeń
       * Usuwanie ostrzeżeń
    - Przeglądanie statystyk


Diagram przypadków użycia:

{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:usecase.png|}}

==== Identyfikacja funkcji ====

**Część administracyjna**:
  * Logowanie/wylogowywanie z panelu administracyjnego
  * Dodawanie,modyfikacja,usuwanie piw
  * Dodawanie,modyfikacja,usuwanie piw w sklepach
  * Dodawanie,modyfikacja,usuwanie miast
  * Dodawanie,modyfikacja,usuwanie korporacji handlowych
  * Dodawanie,modyfikacja,usuwanie promocji
  * Dodawanie,modyfikacja,usuwanie sklepów
  * Dodawanie,modyfikacja,usuwanie, banowanie, aktywowanie użytkowników
  * Usuwanie ostrzeżeń użytkowników
  * Statystki odwiedzi
    * ilość wszystkich wejść,
    * wejsc unikalnych,
    * typu przeglądarki internautów


**Część publiczna**:
  * Logowanie/wylogowywanie z serwisu
  * Rejestracja użytkownika, modyfikacja danych, przypominanie hasła
  * Wyszukiwanie piwa wg ceny, marki, miasta, opakowania
  * Zaawansowane wyszukiwanie piwa wg lokalizacji geograficznej
  * Dodawanie sklepów, uaktualnianie asortymentu, zapisywanie ulubionych sklepów
  * Dodawanie promocji, dodawanie piwa do sklepu, edycja cen, raportowanie innych użytkowników
  * Integracja z portalami spolecznosciowymi

==== Analiza hierarchii funkcji projektowanej aplikacji ====

FHD - Functional Hierarchy Diagram

{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:fhd.png|}}

==== Budowa i analiza diagramu przepływu danych ====

Diagram kontekstowy

{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:przeplywdanych0.png|}}

Diagramy systemowe

{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:przeplywdanych1a.png|}}

{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:przeplywdanych1b.png|}}

{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:przeplywdanych1c.png|}}

{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:przeplywdanych1d.png|}}

==== Wybór encji (obiektów) i ich atrybutów ====

**BEERS**
  * id int PK
  * type enum('can','bottle')
  * name text
  * volume enum('0.33','0.5','0.65','1.0','1.5')

**BEER_STORE**
  * beer_id int FK
  * store_id int FK
  * user_id int FK
  * price double(4,2)
  * last_update int

**BOOKMARKS**
  * user_id int FK
  * store_id int FK

**CITIES**
  * id int
  * name varchar(64)

**CORPORATIONS**
  * id int
  * name text

**PROMOTIONS**
  * id int
  * beer_id int FK
  * corporation_id int FK
  * description text
  * date_start int
  * date_finish int

**STATISTICS**
  * id int
  * browser varchar(128)
  * ip varchar(15)
  * date int
  * user_id int FK

**STORES**
  * id int
  * corporation_id int FK
  * name text
  * city_id int FK
  * address text
  * description text
  * latitude float(6,4)
  * longitude float(6,4)
  * open text
  * open24h tinyint
  * location point
  * user_id int FK

**USERS**
  * id int
  * login text
  * md5sum text
  * email text
  * privilage int
  * last_change int
  * last_delete int

**WARNINGS**
  * id int
  * user_id int FK
  * warning text


==== Projektowanie powiązań (relacji) pomiędzy encjami ====

{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:eer.png|}}


==== Projekt diagramów STD ====

{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:std1.gif|}}

{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:std2.gif|}}

{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:std3.gif|}}

==== Słowniki danych ====
 Specyfikacja słownika danych. Specyfikacja dziedzin i ograniczeń. 
Robimy to w ogóle ?

==== Projektowanie tabel, kluczy, kluczy obcych, powiązań między tabelami, indeksów ====

<code sql>
CREATE SCHEMA IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8 COLLATE utf8;
USE mydb;

CREATE  TABLE IF NOT EXISTS mydb.cities (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  name VARCHAR(64) NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


CREATE  TABLE IF NOT EXISTS mydb.users (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  login TEXT NOT NULL ,
  md5sum TEXT NOT NULL ,
  email TEXT NOT NULL ,
  privilage INT(11) NOT NULL ,
  last_change INT(11) NOT NULL ,
  last_delete INT(11) NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


CREATE  TABLE IF NOT EXISTS mydb.corporations (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  name TEXT NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


CREATE  TABLE IF NOT EXISTS mydb.stores (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  corporation_id INT(11) NOT NULL ,
  name TEXT NOT NULL ,
  city_id INT(11) NOT NULL ,
  address TEXT NOT NULL ,
  description TEXT NOT NULL ,
  latitude FLOAT NOT NULL ,
  longitude FLOAT NOT NULL ,
  open TEXT NOT NULL ,
  open24h TINYINT(4) NOT NULL DEFAULT '0' ,
  location POINT NOT NULL ,
  user_id INT(11) NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_city_id (city_id ASC) ,
  INDEX fk_user_id (user_id ASC) ,
  INDEX fk_corporation_id (corporation_id ASC) ,
  CONSTRAINT fk_city_id
    FOREIGN KEY (city_id )
    REFERENCES mydb.cities (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_user_id
    FOREIGN KEY (user_id )
    REFERENCES mydb.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_corporation_id
    FOREIGN KEY (corporation_id )
    REFERENCES mydb.corporations (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


CREATE  TABLE IF NOT EXISTS mydb.beer_store (
  beer_id INT(11) NOT NULL ,
  store_id INT(11) NOT NULL ,
  user_id INT(11) NOT NULL ,
  price DOUBLE NOT NULL ,
  last_update INT(11) NOT NULL ,
  INDEX fk_beer_id (beer_id ASC) ,
  INDEX fk_store_id (store_id ASC) ,
  INDEX fk_user_id (user_id ASC) ,
  CONSTRAINT fk_beer_id
    FOREIGN KEY (beer_id )
    REFERENCES mydb.beers (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_store_id
    FOREIGN KEY (store_id )
    REFERENCES mydb.stores (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_user_id
    FOREIGN KEY (user_id )
    REFERENCES mydb.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


CREATE  TABLE IF NOT EXISTS mydb.bookmarks (
  user_id INT(11) NOT NULL ,
  store_id INT(11) NOT NULL ,
  INDEX fk_user_id (user_id ASC) ,
  INDEX fk_store_id (store_id ASC) ,
  CONSTRAINT fk_user_id
    FOREIGN KEY (user_id )
    REFERENCES mydb.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_store_id
    FOREIGN KEY (store_id )
    REFERENCES mydb.stores (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


CREATE  TABLE IF NOT EXISTS mydb.promotions (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  beer_id INT(11) NOT NULL ,
  corporation_id INT(11) NOT NULL ,
  description TEXT NOT NULL ,
  date_start INT(11) NOT NULL ,
  date_finish INT(11) NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_corporation_id (corporation_id ASC) ,
  INDEX fk_beer_id (beer_id ASC) ,
  CONSTRAINT fk_corporation_id
    FOREIGN KEY (corporation_id )
    REFERENCES mydb.corporations (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_beer_id
    FOREIGN KEY (beer_id )
    REFERENCES mydb.beers (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


CREATE  TABLE IF NOT EXISTS mydb.statistics (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  browser VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  ip VARCHAR(15) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  date INT(11) NOT NULL ,
  user_id INT(11) NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_user_id (user_id ASC) ,
  CONSTRAINT fk_user_id
    FOREIGN KEY (user_id )
    REFERENCES mydb.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


CREATE  TABLE IF NOT EXISTS mydb.warnings (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  user_id INT(11) NOT NULL ,
  warning TEXT NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_user_id (user_id ASC) ,
  CONSTRAINT fk_user_id
    FOREIGN KEY (user_id )
    REFERENCES mydb.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


</code>

==== Analiza zależności funkcyjnych i normalizacja tabel ====
Wydaje nam się, że baza danych spełnia założenia 3NF. Wynika to z faktu, że przechowywane w niej dane są istotnie atomiczne - z punktu widzenia implementacji projektu. W każdej tabeli atrybuty są w pełni bezpośrednio zależne od klucza głównego. Generalnie baza była projektowana tak by przy możliwie maksymalnej funkcjonalności tabel, zapewnić trzecią postać normalną.

==== Projektowanie operacji na danych ====

<code sql>
SELECT b.*, bs.price as price, concat(s.name,crp.name) as store, s.id AS store_id, c.name as city, s.latitude, s.longitude
FROM beer AS b
LEFT JOIN beer_store AS bs ON b.id = bs.beer_id
JOIN stores AS s ON bs.store_id = s.id
LEFT JOIN corporation AS crp ON s.corporation_id = crp.id
LEFT JOIN city AS c ON s.city_id = c.id

SELECT s.*, c.name as corporation, t.name as city, u.login
FROM stores AS s
LEFT JOIN city AS t ON s.city_id = t.id
LEFT JOIN corporation AS c ON s.corporation_id = c.id
LEFT JOIN user AS u ON s.user_id = u.id
WHERE s.id = $id

$sql_part = "GeomFromText('Polygon((".($x-$distance)." ".($y-$distance).",".($x-$distance)." ".($y+$distance).",".($x+$distance)." ".($y+$distance).",".($x+$distance)." ".($y-$distance).",".($x-$distance)." ".($y-$distance)."))')";


SELECT * FROM stores

WHERE Contains('.$sql_part.' , location )

</code>

==== Zdefiniowanie interfejsów do prezentacji, edycji i obsługi danych ====

  * Rejestracja nowego użytkownika
{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:rejestracja.jpg|}}

  * Dodawanie nowego sklepu przez użytkownika
{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:dodaj_sklep.jpg|}}

  * Dodawanie promocji do sklepu przez użytkownika
{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:dodaj_promocje.jpg|}}

  * Dodawanie piwa przez użytkownika
{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:dodaj_piwo.jpg|}}

  * Edytowanie ceny piwa przez użytkownika
{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:edytuj_cene.jpg|}}

  * Wyszukiwanie podstawowe
{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:szukaj.jpg|}}

  * Wyszukiwanie zaawansowane
{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:szukaj_z.jpg|}}

==== Zdefiniowanie panelu sterowania aplikacji ====

  * Panel administracyjny - lista wszystkich piw
{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:admin.jpg|}}

  * Panel administracyjny - dodawanie piwa
{{:pl:dydaktyka:ztb:2010:projekty:gdziepopiwo:dodaj_piwo2.jpg|}}



==== Opracowanie doświadczeń wynikających z realizacji projektu ====

Podział prac na 3 osoby dał możliwość szybkiej realizacji pracy. Każda osoba była odpowiedzialna za swoją część i sumiennie wywiązała się z zadania. Nie wiem co tu można lepszego napisać. Przecież nikt nie liczył mandaysów.


==== Literatura ====

  * http://www.spatial.cs.umn.edu/Book/labs/vania/spatialQueries.html
  * http://postgis.refractions.net
  * http://wb.mysql.com