Sisällys

• 1 Tehtävänanto
  • 1.1 Verkkokauppa (aihe nro 3)
• 2 Järjestelmän vaatimukset
• 3 Tietokantatoteutus
  • 3.1 ER-kaavio
  • 3.2 ER-kaavion sanallinen selvennys
  • 3.3 Tietokannan luontikomennot
  • 3.4 Triggereiden esittely
  • 3.5 Taulujen esimerkkidataa
  • 3.6 Taulujen funktionaaliset riippuvuudet
• 4 Käyttöliittymä
  • 4.1 Toteutustavan esittely
  • 4.2 Käyttöliittymäsivujen esittely
    • 4.2.1 AdminAdd
    • 4.2.2 AdminEdit
    • 4.2.3 AdminInfo
    • 4.2.4 AdminStock
    • 4.2.5 BrowseCategories
    • 4.2.6 BrowseProducts
    • 4.2.7 ModifyCart
    • 4.2.8 LogIn
    • 4.2.9 MakeDeal
    • 4.2.10 OrderManagement
    • 4.2.11 EmptyTable
    • 4.2.12 CustomerAdd
    • 4.2.13 CustomerEdit
• 5 Palaute ja oman työn arviointi

Tässä esitetty harjoitustyö oli osa Teknillisen korkeakoulun kurssin T-76.1143 Tiedonhallintajärjestelmät (5 op) suorittamista. Harjoitustyössä toteutettiin pienimuotoinen verkkokauppajärjestelmä, joka hyödynsi tietokantoja. Kielenä käytettiin PL/SQL-kieltä ja tietokantana TKK:n tarjoamaa Oraclen tietokantaa. Toteutetun järjestelmän ohjelmakoodit löytyvät kokonaisuudessaan tiedostosta ohjelmakoodit.zip. Toteutetun verkkokaupan käyttöliittymä ei ole enää toiminnassa, mutta tässä dokumentaatiossa on kuvankaappauksia järjestelmästä. Työn arvostelussa yksi olennainen näkökulma oli työn laajuus ja monimutkaisuus, joten maksimiarvosanaan vaikutti se, minkä aiheen valitsi. Verkkokauppajärjestelmän toteuttaminen oli luokiteltu keskivaikeaksi. Oraclen PL/SQL-kielestä löytyy hyviä ohjeita englanniksi mm. Tech on the Netistä.

1 Tehtävänanto

1.1 Verkkokauppa (aihe nro 3)

Asiakas voi selailla ja ostaa verkkokaupan tuotteita. Tuotteet on järjestetty kategorioihin (kategoriat voivat olla myös toistensa ylä -ja alakategorioita).

Käyttäjä tekee ostokset keräämällä tuotteita ostoskoriin, ja ilmoittamalla lopuksi tietonsa (ellei ole aiemmin rekisteröitynyt). Tuotteet toimitetaan postimyynnillä. Järjestelmän täytyy tukea kirjanpitoa verkkokaupan asiakkaista ja näiden tilauksista, varaston tilasta sekä täydennystilauksista verkkokaupan alihankkijoille. Järjestelmä voi myös generoida ylläpitäjille tiedotusviestejä näiden sähköpostiosoitteisiin asiaan kuuluvissa tilanteissa.

2 Järjestelmän vaatimukset

Järjestelmän tarkoituksena on pitää kirjaa verkkokaupan ostoksista, asiakkaista ja varaston tilasta sekä tarjota asiakkaille käyttöliittymä, jossa he voivat lisätä tuotteita ostoskoriin ja lopuksi tilata ostoskorin sisältämät tuotteet postimyynnillä.

Asiakas selaa tuotteita kategorioittain. Kategorioiden alla voi olla alikategorioita. Asiakas lisää haluamansa tuotteet ostoskoriin ja siirtyy lopuksi kassalle, josta hän vahvistaa tilauksen ja voi tilata tuotteet. Asiakkaalta vaaditaan rekisteröityminen verkkokauppaan. Jos hän on jo aiemmin rekisteröitynyt, riittää sähköpostiosoitteen ja salasanan antaminen. Tuotteiden tilaamisesta lähetetään asiakkaalle sähköpostitse tilausvahvistus.

Tuotteet postitetaan asiakkaalle manuaalisesti. Kun tuote tilataan, se varataan. Eli tuotteen varastomäärään ei kosketa ennen kun tuote on postitettu. Ylläpito näkee hallintasivuilla senhetkiset toimittamattomat tilaukset tilausjärjestyksessä ja postittaa näitä järjestyksessä. Ylläpito voi laittaa tuotteet postiin, jos varastossa on riittävästi tuotteita, muutoin järjestelmä ilmoittaa, että täytyy odottaa varastotäydennystä. Jos jonkun tuotteen osalta varaston tila menee nollaan, lähtee tästä sähköpostiviesti ylläpidolle (voisi myös mennä tuotteen alihankkijalle). Ylläpito saa myös tiedon sähköpostitse, jos varattujen tuotteiden määrä ylittää varastossa olevien tuotteiden määrän, jolloin pitäisi tilata lisää tuotteita.

Asiakas voi muuttaa omia tietojaan ja perua jo tilaamansa tuotteet itse nettikäyttöliittymän kautta, jos tuotteita ei ole ehditty postittaa. Ylläpito voi perua tilauksen postituksen jälkeenkin hallintasivun kautta. Ylläpito voi lisätä/poistaa/muokata tuotteita ja kategorioita hallintasivujen kautta. Lisäksi ylläpito pystyy tarkastelemaan asiakkaiden tietoja ja tilauksia sekä tietoa tuotteiden myynnistä ja varaston tilasta.

3 Tietokantatoteutus

3.1 ER-kaavio

Kuva 1. Tietokannan ER-kaavio

3.2 ER-kaavion sanallinen selvennys

Tietokannassa pidetään kirjaa asiakkaista (Customer). Asiakkaalla on uniikki asiakasnumero, josta hänet tunnistetaan. Lisäksi asiakkaasta tallennetaan nimi, osoite, sähköpostiosoite ja salasana rekisteröitymisen yhteydessä.

Tuotteella (Product) on uniikki tuote ID, josta se tunnistetaan. Tuotteella on myös nimi, kuvaus ja hinta. Tuote sijaitsee jossain varaston hyllyssä (Shelf). Jokaisella tuotteella on siis oma hylly. Hyllyssä on jokin määrä tuotetta varastossa (quantity) ja hyllyllä on jokin maksimikapasiteetti (capacity), jota enemmän siihen ei mahdu kyseistä tuotetta.

Tuote kuuluu johonkin kategoriaan (tai tuote voi olla myös kuulumatta mihinkään kategoriaan, jolloin se on poistettu myynnistä, mutta tuotteen tiedot on silti vielä tietokannassa, koska Dealistä voi olla viittauksia siihen ja tuote on näin helppo palauttaa takaisin myyntiin).

Kategorialla on uniikki nimi, josta se tunnistetaan. Lisäksi kategorialla on kuvaus, mitä tuotteita/alikategorioita se pitää sisällään. Kategorialla voi siis olla nolla tai yksi yläkategoria (eli on siis jonkun toisen kategorian alakategoria). Käytännössä on vain yksi kategoria, jolla ei ole yläkategoriaa ja se on "Tuotteet" kategoria, joka on siis juurikategoria, johon muut kategoriat liittyvät.

Ostos (Deal) liittyy aina tasan yhteen asiakkaaseen ja tasan yhteen tuotteeseen. Ostos voi siis sisältää tietyn määrän (number_of_products) yhtä tuotetta. Ostos saa uniikin tilausnumeron (deal_nr), jolla se tunnistetaan. Ostoksessa on tila (state), joka kertoo onko tuote "tilattu" vai "toimitettu". Lisäksi ostoksessa on päivämäärä ja kellonaika (date) sekä ostoksen yhteishinta (total_price). Yhteishinta tarvitaan, että tuotteiden myöhempi hinnanalennus ei sekoittaisi myydyistä tuotteista saadun rahamäärän kirjanpitoa.

Ostoskori (ShoppingCart) on tunnisteriippuvainen tuotteesta. Ostoskori ei ole mitenkään yhteydessä asiakkaaseen, vaan se on sessiokohtainen. Ostoskorilla on järjestelmän generoima uniikki session-ID, jonka avulla tunnistetaan mitkä ostoskorit kuuluvat millekin käyttäjälle. Ostoskorissa on siis yhtä tuotetta tietty määrä. Jokaista tuotetta varten tehdään uusi rivi Ostoskori-tauluun samalla käyttäjän session_id:llä. Ostoskorissa on lisäksi tuotteen lisäyksen päivämäärä, jotta vanhat ostoskorit voidaan siivota pois järjestelmästä.

3.3 Tietokannan luontikomennot

(SQL_skriptit.sql)

CREATE TABLE Category(
   name VARCHAR2(50) PRIMARY KEY,
   description VARCHAR2(1001),
   topCategory VARCHAR(50) REFERENCES Category(name) 
	CONSTRAINT topCategoryCheck CHECK (topCategory <> name)
);

CREATE TABLE Shelf(
   shelf_nr INTEGER PRIMARY KEY,
   quantity INTEGER NOT NULL CHECK (quantity>=0),
   capacity INTEGER NOT NULL CONSTRAINT capacityCheck 
		CHECK (capacity >= quantity)
);

CREATE TABLE Product(
   product_id INTEGER PRIMARY KEY,
   name VARCHAR2(50) NOT NULL,
   category VARCHAR2(50),
   price DECIMAL(10, 2) NOT NULL,
   shelf_nr INTEGER REFERENCES Shelf(shelf_nr),
   description VARCHAR2(1001)
);


CREATE TABLE Customer (
   customer_nr INTEGER PRIMARY KEY,
   name VARCHAR2(50) NOT NULL,
   e_mail VARCHAR2(50) UNIQUE,
   password VARCHAR2(50) NOT NULL,
   address VARCHAR2(200) NOT NULL
);


CREATE TABLE ShoppingCart (
   session_id INTEGER,
   product_id INTEGER REFERENCES Product(product_id),
   cart_date DATE NOT NULL,
   number_of_products INTEGER NOT NULL,
   PRIMARY KEY(session_id, product_id)
);

CREATE SEQUENCE session_sequence
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE deal_sequence
START WITH 1
INCREMENT BY 1;

CREATE TABLE Deal (
   deal_nr INTEGER PRIMARY KEY,
   customer_nr INTEGER REFERENCES Customer(customer_nr),
   product_id INTEGER REFERENCES Product(product_id),
   state CHAR(1) NOT NULL CONSTRAINT stateCheck 
		CHECK(state IN ('O', 'D')),
   deal_date timestamp(0) NOT NULL,
   number_of_products INTEGER NOT NULL,
   total_price DECIMAL(10,2) NOT NULL
);

Category-taulussa tarkastetaan, että kategorian yläkategoria ei voi olla sama kuin kategoria itse. Shelf-taulussa tarkastetaan, että tuotteen varaston määrä ei voi olla yli hyllyn kapasiteetin. Deal-taulussa tarkastetaan, että state-attribuutin arvo on joko "O" tai "D" eli O = Ordered, D = Delivered.

3.4 Triggereiden esittely

(TRIGGER_quantityCheck.sql)

CREATE or REPLACE TRIGGER quantityCheck
AFTER UPDATE OF quantity ON Shelf
  REFERENCING
	NEW AS newValues
	OLD AS oldValues
FOR EACH ROW
WHEN (oldValues.quantity>0 AND newValues.quantity<=0 
	AND newValues.capacity>0)
  DECLARE
	mailhost VARCHAR2(64) := 'smtp.hut.fi';
	sender VARCHAR2(64) := 'thj_grp041@niksula.cs.hut.fi';
	recipient VARCHAR2(64);
	subject VARCHAR2(64) := 'Tuote numero ' 
		||:newValues.shelf_nr|| ' loppu varastosta';
	mail_conn utl_smtp.connection;
	lastSold INTEGER;
	system_date DATE;

BEGIN

	SELECT sysdate-14 INTO system_date FROM DUAL;
	SELECT e_mail INTO recipient FROM Customer WHERE customer_nr=0;

	SELECT SUM (number_of_products)
	INTO lastSold
	FROM Deal
	WHERE state='D' AND product_id = :newValues.shelf_nr 
		AND deal_date>system_date;

   IF (lastSold IS NULL) THEN
	   lastSold := 0;
   END IF;

	BEGIN

   mail_conn := utl_smtp.open_connection(mailhost, 25);
   utl_smtp.helo(mail_conn, mailhost);
   utl_smtp.mail(mail_conn, sender);
   utl_smtp.rcpt(mail_conn, recipient);
   utl_smtp.open_data(mail_conn);
   utl_smtp.write_data(mail_conn, 'Subject: ' || subject || utl_tcp.CRLF);
   utl_smtp.write_data(mail_conn, 'To: '|| recipient || utl_tcp.CRLF);
   utl_smtp.write_data(mail_conn,'Tuote numero '|| :newValues.shelf_nr ||' on
	loppunut varastosta.' ||CHR(10)||'');
   utl_smtp.write_data(mail_conn,'Varastosaldo on nyt: 
	'|| :newValues.quantity || ' kpl (aikaisemmin '|| :oldValues.quantity || 
	' kpl). '||CHR(10)||'');
   utl_smtp.write_data(mail_conn,'Tuotetta on myyty viimeisen 2 viikon aikana '
	|| lastSold ||' kpl. Tilaa siis suunnilleen saman verran uusia varastoon. '
	||CHR(10)||'');
   utl_smtp.write_data(mail_conn,'Tuotetta mahtuu varastoon '
	|| :newValues.capacity ||' kpl. Huomioi se tilauksessa.' ||CHR(10)|| '');
   utl_smtp.write_data(mail_conn,'_________________' ||CHR(10)|| '' ||	CHR(10)|| '');
   utl_smtp.write_data(mail_conn,'Oraclen generoima automaattinen ilmoitus');
   utl_smtp.close_data(mail_conn);
   utl_smtp.quit(mail_conn);

   EXCEPTION
	  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
		BEGIN
		 utl_smtp.quit(mail_conn);
		END;
	   END;

END quantityCheck;
/
show errors

Tietokannassa on käytetty yhtä herätintä (triggeriä) nimeltä quantityCheck. Kyseinen triggeri tarkastaa aina tuotteiden varaston määrän muuttamisen jälkeen, onko varaston määrä nollassa. Jos tuotteen varaston määrä on nollassa, lähetetään ylläpidolle tästä sähköpostiviesti, jossa kehotetaan tilaamaan tuotetta heti lisää.

Ylläpidon sähköpostiosoitteen voi muuttaa muokkaamalla asiakasnumerolle 0 tallennettua ylläpidon sähköpostiosoitetta. Sähköpostiosoitteen muokkaus tapahtuu osoitteessa http://database.hut.fi/pls/gen/thj_grp041.logIn?operation=2. Eli aivan kuten normaalinkin asiakkaan tietojen muokkaaminen. AdminInfo-sivulta näkee kirjautumiseen vaadittavan ylläpitäjän nykyisen sähköpostiosoitteen asiakasnumero 0:n kohdalta. Salasana, joka vaaditaan sähköpostiosoitteen muokkaamiseen on "salasana".

3.5 Taulujen esimerkkidataa

INSERT INTO Category VALUES('Tuotteet', 'rootNode', NULL);

INSERT INTO Category VALUES('Komponentit','Tietokoneen komponentteja', 'Tuotteet');
INSERT INTO Category VALUES('Muistit', 'Muistikammat', 'Komponentit');
INSERT INTO Category VALUES('Naytonohjaimet', 'Naytonohjaimet', 'Komponentit');
INSERT INTO Category VALUES('Suorittimet', 'Suorittimet', 'Komponentit');
INSERT INTO Category VALUES('Naytot', 'Naytot', 'Komponentit');

INSERT INTO Shelf VALUES(1, 5, 100);
INSERT INTO Shelf VALUES(2, 7, 100);
INSERT INTO Shelf VALUES(3, 90, 100);
INSERT INTO Shelf VALUES(4, 50, 100);
 
INSERT INTO Product VALUES(1, 'Muistipalikka', 'Muistit', 99.99, 1, '1GB 400MHZ DDR');
INSERT INTO Product VALUES(2, 'Naytonohjain', 'Naytonohjaimet', 199.90, 2, 'RADEON');
INSERT INTO Product VALUES(3, 'Suoritin', 'Suorittimet', 299.95, 3, 'AMD 64 3500+');
INSERT INTO Product VALUES(4, 'Naytto', 'Naytot', 400.00, 4, 'SyncMaster 930BF');

INSERT INTO Customer VALUES(0, 'Administrator', 'admin@hut.fi', 'salasana', 
			'No address');
INSERT INTO Customer VALUES(1, 'Teemu Teekkari', 'tteekkari@tkk.fi', 
	'salasana', 'Teekkarikyla 10');
INSERT INTO Customer VALUES(2, 'Timo Tikkilainen', 'ttikki@cc.hut.fi', 
	'password', 'Kiltahuone');

INSERT INTO Deal VALUES(1, 1, 5, 'O',  timestamp'2006-03-22 09:00:00', 2, 18.8);
INSERT INTO ShoppingCart VALUES (1, 4, '02.01.2006', 2);

3.6 Taulujen funktionaaliset riippuvuudet

Category:
name → description, topCategory

Shelf:
shelf_nr → quantity, capacity

Product:
product_id → name, category, price, shelf_nr, description

Customer:
customer_nr → name, e_mail, password, address

ShoppingCart:
session_id, product_id → cart_date, number_of_products

Deal:
deal_nr → customer_nr, product_id, state, deal_date, number_of_products, total_price

Eli kaikkien taulujen riippuvuuksien vasemmalla puolella on taulun avain, jolloin kaikki järjestelmän taulut ovat Boyce-Codd normaalimuodossa.

4 Käyttöliittymä

4.1 Toteutustavan esittely

Käyttöliittymä tehtiin käyttäen Oraclen PL/SQL-kieltä. Käyttöliittymässä käytettiin cookieita ("keksejä") ostoskorin toteuttamista varten sekä admin-osiooon kirjautumista varten. Käyttöliittymäsivut ovat valmiit, joten niihin ei tehdä suurempia muutoksia dokumentin palautuksen jälkeen (ellei sitten löydy yllättäen jotain suurempia bugeja).

4.2 Käyttöliittymäsivujen esittely

Admin-osion kaikki sivut on suojattu salasanalla (joka on salasana). Kun ylläpitäjä ei ole kirjautunut sisään, kysytään salasanaa. Kun ylläpitäjä kirjautuu sisään oikealla salasanalla, tallennetaan hänen koneelleen cookie, joka sisältää salasanan. Näin hän pysyy kirjautuneena sisään.

4.2.1 AdminAdd

AdminAdd-sivulla pystyy lisäämään järjestelmään tuotteita ja kategorioita. Kategorialla täytyy olla nimi, joka ei saa olla yli 50 merkkiä. Lisäksi kategorialla voi olla kuvaus maksimissaan 1000 merkkiä. Kategoria pitää olla jonkun kategorian alakategoria ("Tuotteet"-kategoria on juurikategoria, johon muut kategoriat liittyvät). Kategorian yläkategoriaksi hyväksytään vain kategoriat, joissa ei ole tuotteita.

Lisättävälle tuotteelle annetaan nimi (max 50 mrk). Lisäyksessä tarkastetaan, että hinta on desimaaliluku ja että varastomäärä ja "varastoon mahtuu" ovat kokonaislukuja ja että "varastoon mahtuu" on suurempi tai yhtä suuri kuin varastomäärä. Tuotteelle voi antaa kuvauksen, jonka pituus on maksimissaan 1000 merkkiä. Tuote pitää sijoittaa johonkin kategoriaan, joka voi olla kategoria, jolla ei ole alakategorioita.

Kuva 2. AdminAdd screenshot

AdminAdd-sivun olennaisimpia SQL-lauseita:

Kaikki kategoriat:

SELECT *
FROM Category
ORDER BY name;

Kaikki tuotteet:

SELECT *
FROM Product;

Kategoriat, joilla ei ole alakategorioita:

(SELECT name
FROM Category
MINUS
SELECT DISTINCT
  topCategory AS name
FROM Category)
ORDER BY name;

Tuotteita sisältävät kategoriat:

SELECT category
FROM Product
GROUP BY category;

Kategorian lisääminen kantaan:

INSERT INTO Category
VALUES(categoryInsert,
	 categoryDescription,
	 topCategoryName);

4.2.2 AdminEdit

AdminEdit-sivua käytetään AdminStock-sivun kautta (muokkaa- ja poista-linkeillä). Ylläpito voi muokata kategorian kuvausta ja yläkategoriaa sekä muokata tuotteen nimeä, hintaa, varastomääriä ja tuotteen kuvausta sekä tuotteen kategoriaa.

Muokattavalle tuotteelle/kategorialle tehdään samat tarkastukset kuin AdminAdd-sivullakin ja lisäksi muutama uusi tarkastus. Kategorian ylikategoriaksi hyväksytään vain kategoria, joka ei ole kyseisen kategorian alikategoria (tai kategoria itse) ja jossa ei ole tuotteita. Lomakkeessa näytetään oletuksena senhetkiset tuotteen/kategorian arvot.

Kuva 3. AdminEdit screenshot

AdminEdit-sivun olennaisimpia SQL-lauseita:

Tyhjät kategoriat:

SELECT name
FROM Category
MINUS
SELECT DISTINCT topCategory 
  AS name
FROM Category
MINUS
SELECT category AS name
FROM Product;

Tuotteen tietojen päivittäminen:

UPDATE Product 
SET name=productName,
   category=productCategory,
   price=productPrice,
   description=productDescription
WHERE product_id=product_nr;

Kategorian mahdolliset yläkategoriat:

SELECT name
FROM Category
WHERE name <> whatCategory AND
	(topCategory <> whatCategory OR
	 topCategory IS NULL)
MINUS
SELECT category AS name
FROM Product;

Kategorian tietojen päivittäminen:

UPDATE Category 
SET description=categoryDescription,
	topCategory=categoryTopCat
WHERE name=categoryName;

4.2.3 AdminInfo

AdminInfo-sivulla voi tarkastella asiakkaiden tietoja ja heidän tilauksiaan. Lisäksi sivulla on linkki "Siivoa ostoskori vanhoista tilauksista", jota klikkaamalla ShoppingCart-taulusta poistetaan kaikki yli 30 päivää vanhat ostokset, jotka ovat jääneet sinne esimerkiksi kun asiakkaan selaimen cookie on vanhentunut ja hän on saanut uuden sessionID:n.

Sivulle listataan kaikki asiakkaiden tilaukset, joita ei ole vielä postitettu. Tilaukset on järjestetty ostopäivän mukaan, joten ylläpito voi postittaa tuotteita helposti tilausjärjestyksessä. Järjestelmä ei hyväksy tuotteen postittamista, jos tilatun tuotteen määrä ylittää varastossa olevien tuotteiden määrän. Tällöin järjestelmä kehottaa odottamaan, että varastoon saadaan täydennystä. Tilauksen voi perua samasta paikasta milloin vain, jolloin tiedot tilauksesta poistetaan kokonaan tietokannasta.

Asiakkaan tilauksissa näkee, onko tilaus tilausvaiheessa vai onko se toimitettu (eli onko tuote vasta tilattu vai onko se jo ehditty postittaa). Tilan voi vaihtaa "Postita"-painikkeella, jolloin asiakkaalle lähtee samalla sähköpostiviesti, jossa kerrotaan, että hänen tekemänsä tilaus on postitettu. Peru painikkeella tilauksen voi poistaa tietokannasta, jos vaikka asiakas palauttaisikin tuotteen tai perusi kaupat ennen toimitusta.

Kuva 4. AdminInfo screenshot

AdminInfo-sivun olennaisimpia SQL-lauseita:

Asiakkaan ostokset:

SELECT *
FROM Deal
WHERE customer_nr=custNumb;

Asiakkaan toimittamattomien tilausten määrä:

SELECT COUNT(*) 
FROM Deal 
WHERE state = 'O' AND customer_nr =
	 i.customer_nr;

Toimittamattomat ostokset aikajärjestyksessä:

SELECT deal_nr, product_id, state,
	 deal_date, number_of_products,
	 total_price, Deal.customer_nr,
	 name, address
FROM Deal, Customer
WHERE Deal.customer_nr =  
  Customer.customer_nr AND state='O'
ORDER BY deal_date;

4.2.4 AdminStock

AdminStock-sivulla voi tarkastella järjestelmän sisältämiä tuotteita ja kategorioita ja muokata/poistaa niitä. Tuotteet voi listata nousevassa järjestyksessä joko tuoteID:n mukaan, tuotteen nimen mukaan, kategorian mukaan tai hinnan mukaan. Tämä tapahtuu klikkaamalla vastaavia taulukon otsikkojen linkkejä. Tuotteista on listattu kaikki oleellisimmat tiedot.

Tuotteen voi poistaa myynnistä Poista-linkillä, jolloin sivu lähettää pyynnön adminEdit-sivulle, joka huolehtii tuotteen poistosta. Tuotteelle annetaan kategoriaksi NULL ja sen varastomäärä ja hyllytila laitetaan nollaan. Tuotetta voi muokata klikkaamalla Muokkaa-linkkiä, jolloin siirrytään myös adminEdit-sivulle, joka huolehtii muokkauksesta.

Kategorioiden muokkaus ja poisto toimivat vastaavasti. Kategoriat voi järjestää joko nimen tai ylikategorian mukaan. Kategoriaa ei anneta poistaa, jos siinä on tuotteita tai sen alla on alikategorioita. Nämä pitää poistaa/siirtää muualle, ennen kuin poisto onnistuu.

Kategorian nimen linkkä klikkaamalla pääsee verkkokauppaan selaamaan kategorian alikategorioita. Kategorian tuotteet linkkiä klikkaamalla pääsee verkkokauppaan tarkastelemen kategorian sisältämiä tuotteita.

Kuva 5. AdminStock screenshot

AdminStock- sivun olennaisimpia SQL-lauseita:

Listaa kaupan tuotteet kategorian mukaan:

SELECT product_id, name, category,
	price, quantity, capacity, description
FROM Product, Shelf
WHERE Product.shelf_nr = Shelf.shelf_nr
ORDER BY category;

Ostettujen tuotteiden määrä:

SELECT SUM (number_of_products) 
INTO soldProducts 
FROM Deal 
WHERE state='D';

Myydyistä tuotteista saatu yhteishinta:

SELECT SUM(total_price) 
INTO totalCash 
FROM Deal 
WHERE state='D';

Tuotteiden määrä kaupassa:

SELECT COUNT (*) 
INTO produtsInStore 
FROM Product 
WHERE category IS NOT NULL;

4.2.5 BrowseCategories

Kuva 6. BrowseCategories screenshot

BrowseCategories toimii verkkokaupan kategorioiden/tuotteiden selausjärjestelmän ytimenä. Sivu aukeaa oletusarvoisesti "Tuotteet" kategorian kohdalta, joka toimii kaupan juurikategoriana. Sivulle generoidaan linkit valittuna olevan kategorian alakategorioihin, ostoskoriin, admin osioon, käyttäjätietojen muokkaukseen, sekä tilausten hallintaan.

BrowseCategories-sivun olennaisimpia SQL-lauseita:

Kaikki alakategoriat aakkosjärjestyksessä:

SELECT *
FROM Category
WHERE topCategory = categoryName
GROUP BY name;

Alakategorioiden määrä:

SELECT COUNT(*) 
INTO sum_of_children
FROM Category 
WHERE topCategory = i.name;

4.2.6 BrowseProducts

BrowseProducts osiossa näytetään tiedot alakategorian sisältämistä tuotteista. Käyttäjä voi lisätä haluamansa tuotteen ostoskoriin valitsemalla ensin kappalemäärän ja painamalla tämän jälkeen "add to cart" painiketta. Tuote lisätään ostoskoriin vain jos kappalemääräksi on valittu yli nolla kpl.

Kuva 7. BrowseProducts screenshot

BrowseProducts-sivun olennaisimpia SQL-lauseita:

Laske paljonko kutakin tuotetta on varastossa (i on FOR lauseen iteraatiossa kulkija):

SELECT quantity 
INTO quantity_in_shelf 
FROM Shelf 
WHERE shelf_nr = (SELECT shelf_nr 
				FROM Product 
				WHERE product_id = i.product_id);

Laske paljonko kutakin tuotetta on varattu:

SELECT SUM(number_of_products)
INTO reserved_products
FROM Deal WHERE state='O' AND product_id = i.product_id;

4.2.7 ModifyCart

Ostoskoriin kootaan kaikki browseProducts-sivun avulla valitut tuotteet. Käyttäjä tunnistetaan cookien avulla. Taulukossa näytetään tuotteet, niiden yksittäishinta, käyttäjän valitsema kappalemäärä, tuotekohtainen yhteishinta ja kaikkien ostosten yhteishinta. Käyttäjälle tarjotaan myös jokaisen tuotteen kohdalla mahdollisuus poistaa kyseinen tuote ostoskorista. Ostoskorissa on myös linkki ostoskorin tyhjentämiseen kaikista tuotteista. Jos ostoskori on tyhjä, niin scripti generoi tästä viestin. Käyttäjä voi lopulta siirtyä ostamaan tuotteet "Kassalle" linkin avulla.

Kuva 8. ModifyCart screenshot

ModifyCart-sivun olennaisimpia SQL-lauseita:

Uniikin session_id:n määritys:

SELECT session_sequence.NEXTVAL INTO sid FROM DUAL;

Valitse kaikki tietyn session_id:n omaavat tuotteet ostoskorista:

SELECT Product.product_id, name, description, price
FROM ShoppingCart, Product
WHERE session_id = sid AND ShoppingCart.product_id = Product.product_id;

Löytyykö tuote jo ostoskorista?

SELECT COUNT(*)
INTO already_in_cart
FROM ShoppingCart
WHERE product_id = itemNumber AND session_id = sid;

Tuotteen lisäys jos tuote oli jo ostoskorissa (kasvatetaan tuotteen kappalemäärää):

UPDATE ShoppingCart
SET number_of_products = (number_of_products + quantity)
WHERE product_id = itemNumber AND session_id = sid;

Uuden tuotteen lisäys:

INSERT INTO ShoppingCart 
VALUES(sid, itemNumber, system_date, quantity);

Rivien määrän laskeminen:

SELECT COUNT(*) INTO rowcount 
FROM ShoppingCart 
WHERE session_id = sid;

4.2.8 LogIn

Kuva 9. LogIn screenshot 1

Kuva 10. LogIn screenshot 2

LogIn-scriptin avulla käyttäjä voi kirjautua sisään verkkokauppaan. Kirjautumista tarvitaan tuotteiden ostamisen, käyttäjätietojen lisäyksen ja muokkauksen, sekä tilausten hallinnan yhteydessä. Sisäänkirjautumisprosessi koostuu kahdesta vaiheesta. Ensimmäisessä syötetään käyttäjänimi (e-mail) ja salasana.

Login painiketta painettaessa tarkistetaan että molemmat kentät ovat täytetty, e-mail osoite löytyy käyttäjätietokannasta ja että salasana vastaa e-mail osoitetta. Jos jokin tarkistusehto ei toteudu, niin generoidaan virheilmoitus. Jos sisäänkirjautuminen onnistui, näytetään käyttäjälle painike josta voidaan siirtyä halutulle sivulle. Jos käyttäjä ei ole vielä rekisteröitynyt verkkokaupan asiakkaaksi, voi hän rekisteröityä "Uusi asiakas?" linkin kautta.

LogIn-sivun olennaisimpia SQL-lauseita:

Onko käyttäjän e-mail tietokannassa?

SELECT COUNT(*)
INTO mailCheck
FROM Customer
WHERE e_mail = customerMail;

Käyttäjänumero, nimi ja salasana:

SELECT customer_nr, name, password 
INTO customerID, customerName, passwordCheck
FROM Customer
WHERE e_mail = customerMail;

4.2.9 MakeDeal

MakeDeal sivun kautta käyttäjä voi ostaa ostoskoriin valitsemansa tuotteet. Sivulle generoidaan käyttäjän henkilötiedot ja taulukko ostoskoriin valittuna olevista tuotteista. Käyttäjää pyydetään tarkistamaan omat tietonsa; Jos tiedot ovat oikein, voidaan tuotteet varata painamalla "Osta tuotteet" painiketta. Tämän jälkeen tuotteet merkitään varatuiksi ja automaattinen varmistussähköposti lähetetään käyttäjän antamaan e-mail osoitteeseen. Lopuksi näytetään tietoja tilauksen onnistumisesta. Jos annettu sähköpostiosoite ei ollut validi, kehotetaan käyttäjää tarkistamaan tietonsa uudelleen.

Kuva 11. MakeDeal screenshot 1

Kuva 12. MakeDeal screenshot 2

MakeDeal-sivun olennaisimpia SQL-lauseita:

Uniikin deal_nr:n määritys:

SELECT deal_sequence.NEXTVAL 
INTO deal_nr FROM DUAL;

Kaupantekoajan määritys:

SELECT systimestamp
INTO system_timestamp FROM DUAL;

Paljonko tiettyä tuotetta on myyty kahden viime viikon aikana:

SELECT SUM (number_of_products) INTO lastSold 
FROM Deal
WHERE state='D' AND product_id = i.product_id 
	  AND deal_date>dateTwoWeeksAgo;

Löytyykö tilattava tuote käyttäjän aikaisemmista varauksista?

SELECT COUNT(*) INTO already_ordered 
FROM Deal 
WHERE customer_nr = customerID 
	  AND product_id = i.product_id AND state = 'O';

Tuotteen varaus jos aikaisempi varaus löytyy (lisätään aiempaan varaukseen):

UPDATE Deal
SET number_of_products = old_quantity + quantity_in_cart
WHERE customer_nr = customerID 
	  AND product_id = i.product_id AND state = 'O';
	
UPDATE Deal
SET total_price = (old_quantity + quantity_in_cart) * i.price
WHERE customer_nr = customerID 
	  AND product_id = i.product_id AND state = 'O';

Uusi varaus:

INSERT INTO Deal 
VALUES(deal_nr, customerID, i.product_id, 'O', system_timestamp, 
	   quantity_in_cart, i.price * quantity_in_cart);

4.2.10 OrderManagement

Kun käyttäjä ostaa tuotteita MakeDeal-sivulta, merkitään tuotteet varatuiksi. Tuotteet lähetetään vasta kun verkkokaupan henkilökunta käsittelee tilauksen. OrderManagement-sivun tarkoitus on tarjota käyttäjälle mahdollisuus perua tilauksensa, ennen kuin ne on ehditty postittaa.

Kuva 13. OrderManagement screenshot

OrderManagement-sivun olennaisimpia SQL-lauseita:

Kaikki käyttäjän varaamat tuotteet:

SELECT deal_nr, state, Product.product_id, name, description, 
	   price, total_price,number_of_products
FROM Deal, Product
WHERE Deal.customer_nr = customerID 
	  AND Deal.product_id = Product.product_id 
	  AND state = 'O';

Poista tuote varauksista:

DELETE FROM Deal 
WHERE product_id = itemNumber 
	 AND customer_nr = customerID AND state = 'O';

4.2.11 EmptyTable

Sekä ostoskorin että tilausten hallinnan yhteydessä on tarve "poista kaikki" tyyliseen toimintoon. EmptyTable on pieni skripti joka poistaa kaikki tilaukset tai tyhjentää ostoskorin ja ilmoittaa toiminnon onnistumisesta käyttäjälle.

EmptyTable-sivun olennaisimpia SQL-lauseita:

Tyhjennä ostoskori:

DELETE FROM ShoppingCart
WHERE session_id = sid;

Peru kaikki varaukset:

DELETE FROM Deal
WHERE customer_nr = customerID 
	  AND state = 'O';

4.2.12 CustomerAdd

Kuva 14. CustomerAdd screenshot

CustomerAdd on lomake, jolla lisätään asiakkaita asiakasrekisteriin. Se pyytää tärkeimmät tiedot, nimen, sähköpostiosoitteen, postiosoitteen sekä salasanan. Lisäksi se antaa kaikille asiakkaille oman asiakasnumeron. Lisää-nappia painamalla customerAdd ensin tarkistaa onko kaikki kentät täytetty ja sitten onko olemassa asiakasta samalla sähköpostiosoitteella (tai asiakasnumerolla) jos ei, tarkistetaan täsmäävätkö salasanat. Jos lomake läpäisee nämä testit, asiakas lisätään Customer-tauluun. Lopuksi tarkistetaan vielä onko sähköpostiosoite oikeaa muotoa. Jos ei, asiakas poistetaan rekisteristä. Kaikki ongelmatilanteet tuottavat virheilmoituksen (toivottavasti), ei pitäisi koskaan olla epäselvää missä vika on. Asiakasnumero määräytyy korkeimman olemassa olevan numeron + 1 mukaan.

CustomerAdd-sivun olennaisimpia SQL-lauseita:

Asiakkaan lisäys:

INSERT INTO Customer
VALUES(customerNumber, customerName,
   customerEmail, customerPassword,
   customerAdress);

Asiakkaan poisto, jos sähköposti laiton:

DELETE FROM Customer 
WHERE customer_nr=customerNumber;

4.2.13 CustomerEdit

Kuva 15. CustomerEdit screenshot

CustomerEdit toimii kuten CustomerAdd mutta muuttaa jo rekisteröityneen asiakkaan tietoja. Lomake ottaa vastaan sähköpostiosoitteen ja salasanan sisäänkirjautumissivulta ja antaa käyttäjän muokata tietojaan.

CustomerEdit-sivun olennaisimpia SQL-lauseita:

Asiakkaan muokkaus:

UPDATE Customer 
SET name=customerName,
  e_mail=customerEmail,
  password=customerPassword,
  address=customerAdress 
WHERE customer_nr = editCustomer;

Asiakastietojen lisäys lomakkeeseen:

SELECT customer_nr, name, e_mail,
	 password, address 
INTO customerEditNumber,
   customerEditName, 
   customerEditEmail,
   customerEditPassword,
   customerEditAdress
FROM Customer
WHERE customer_nr=editCustomer AND
	password=customerPassword;

5 Palaute ja oman työn arviointi

Ryhmällämme oli yhteensä kolme fyysistä tapaamiskertaa:

Käytimme lisäksi hyväksi IRC-kanavaa, joka oli päivittäisessä käytössä ja osoittautui erittäin hyödylliseksi keskustelukanavaksi harjoituksen toteutuksen kannalta. Seuraavassa ryhmämme jäsenten omat palautteet, vastuualueet ja arvio tuntityömäärästä: