Проектирование и реализация базы данных «On-line магазин»

   FROM goods g WHERE ( (lower(g.name) LIKE lower('%' || whattofind || '%')) or

  (lower(g.developer) LIKE lower('%' || whattofind || '%')) or (lower(g.publisher) LIKE lower('%' || whattofind || '%')) ) AND (g.count_at_storehouse<>0) LOOP

RETURN NEXT;

END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100 ROWS 1000;

 

 

 

 

 

 

Результат работы:

 

 

 

 

8. TopGood(Поиск самого популярного товара по результатам продаж)

 

CREATE OR REPLACE FUNCTION "public"."TopGood" (out "NameOfGood" varchar, out "SellingCount" integer) RETURNS record AS

$body$

DECLARE

  g_id INTEGER;

  gname VARCHAR;

  cnt INTEGER;

  cntSumm INTEGER;

BEGIN

  "SellingCount":=0;

  FOR g_id, gname IN SELECT g.goods_id, g.name FROM goods g LOOP

   cntSumm:=0;

    FOR cnt IN SELECT r.reteil_count FROM reteil r WHERE (r.goods_id=g_id) LOOP

     cntSumm:=cntSumm+cnt;

    END LOOP;

     IF cntSumm>"SellingCount" THEN

      "SellingCount":=cntSumm;

      "NameOfGood":=gname;

     END IF;

  END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

 

Результат работы:

 

 

 

 

9. Top_Seller(Поиск самого активного продавца по количеству оформленных счетов)

 

CREATE OR REPLACE FUNCTION "public"."Top_Seller" (out "Name" varchar, out "Selled" integer) RETURNS record AS

$body$

DECLARE

  id_s INTEGER;

  cnt INTEGER;

  sname VARCHAR;

BEGIN

"Selled":=0;

  FOR id_s, sname IN SELECT ss.employee_id, ss.last_name FROM shop_staff ss LOOP

   SELECT INTO cnt COUNT(a.account_id) FROM accounts a WHERE (a.employee_id=id_s);

   IF cnt>"Selled" THEN

    "Selled":=cnt;

    "Name":=sname;

   END IF;

  END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

 

Результат работы:

 

 

7. Разработка клиент-серверного приложения.

 

Для более  удобной работы с базой данных, было разработано клиент-серверное  web-приложение на языке высокого уровня Java, в котором в качестве клиента используется браузер, а в качестве сервера приложений – сервлет-контейнер Apache Tomcat 6.0, который связан с серверной частью СУБД PostgreSQL.

При разработке приложения были использованы технологии: JDBC, XML, JSP и Ajax (JQuery), а также паттерны: MVC, Command Factory, Singlton и Object Pool.

Подключение всех пользователей к БД осуществляется с полными правами к БД, а разграничение доступа для разных пользователей реализовано на уровне приложения (ограничение использования паттерна Object Pool).

 

Возможности приложения:

• Регистрация покупателей (для новых пользователей)
• Редактирование персональных данных покупателя (для покупателей)
• Поиск игр по названию/издателю/разработчику (для всех пользователей)
• Осуществление покупок (для покупателей)
• Добавление новых товаров (для продавцов и администратора)
• Вывод на экран истории заказов (для покупателей)
• Вывод на экран зарплаты сотрудников (для администратора)
• Вывод на экран невостребованных больше месяца товаров (для продавцов и администратора)

 

Рисунок 7.1. Главная страница разработанного web-приложения.

7.1 Руководство  пользователя.

 

7.1.1 Регистрация.

 

Незарегистрированные  пользователи имеют доступ к функции  регистрации и поиска товара.

Для регистрации  нового пользователя, пройдите по ссылке «Регистрация» и заполните все  поля, затем нажмите на кнопку «Зарегистрироваться» – рис. 7.1. В случае успешной регистрации, Вы сможете войти на сайт под своим  логином и паролем.

 

 

Рисунок 7.1. Регистрация нового пользователя.

 

7.1.2 Поиск.

 

Поиск по каталогу товаров может осуществлять любой  пользователь магазина. Для осуществления  поиска введите в соответствующее  поле ввода искомый текст и нажмите на кнопку «Искать» - рис. 7.2. Если в поле ввода текста ничего не ввести и нажать на кнопку «Искать», то будет выведен на экран весь список товаров в каталоге, которые есть в наличии на текущий день.

 

 

Рисунок 7.2. Поиск товара.

 

 

7.1.3 Авторизация и деавторизация.

 

Если Вы являетесь зарегистрированным пользователем, то для авторизации введите свой логии и пароль в соответствующие  поля и нажмите «Войти» - рис. 7.3.

 

Рисунок 7.3. Авторизация.

 

Для деавторизации  достаточно нажать на кнопку «Выйти», которая появляется, после успешной авторизации на месте полей ввода логина и пароля – рис. 7.4.

 

Рисунок 7.4. Деавторизация.

 

В случае успешной авторизации, в зависимости от Вашей  роли в магазине (покупатель, продавец, администратор), смотрите соответствующий  раздел данного руководства пользователя.

 

7.1.4 Возможности для покупателей.

 

Покупатель  может выполнять следующие действия: редактировать свой профиль, совершать  покупки, а также просматривать  историю своих заказов.

Для просмотра истории своих заказов, нажмите на соответствующую кнопку в меню покупателя – рис. 7.5.

 

 

Рисунок 7.5. Меню покупателя.

 

 

Пример  истории заказов представлен  на рисунке 7.6.

 

Рисунок 7.6. Пример истории заказов.

 

Для редактирования своего профиля, нажмите на соответствующую  кнопку в меню покупателя – рис. 7.5. Вы увидите ваши регистрационные данные с возможностью их изменить. Для сохранения результатов нажмите на кнопку «Сохранить изменения» - рис. 7.7.

 

 

Рисунок 7.7. Редактирование профиля.

 

Чтобы совершить  покупку, с помощью функции поиска (раздел 7.1.2) найдите интересующий Вас товар, а затем в колонке «Купить» нажмите на ссылку с изображением корзины напротив интересующего Вас товара – рис 7.8.

 

 

Рисунок 7.8. Осуществление покупки.

После перехода по ссылке Вы увидите страницу оформления заказа, где можете ввести количество покупаемого товара – рис. 7.9. Для осуществления покупки нажмите на кнопку «Купить».

 

 

Рисунок 7.9. Страница оформления заказа.

 

7.1.5 Возможности для продавцов.

 

Продавец  с помощью меню продавца может  выполнять следующие действия: добавлять  в каталог новые товары, а также  узнавать о невостребованном товаре в каталоге магазина (товар, который  не продавался больше месяца) – рис. 7.10.

 

 

Рисунок 7.10. Меню продавца.

 

Чтобы добавить новый товар, нажмите на кнопку «Добавить  новый товар», а затем после  перехода на страницу добавления товара, заполните все поля и нажмите  на кнопку «Добавить товар» - рис. 7.11.

 

 

Рисунок 7.11. Страница добавления нового товара.

Чтобы посмотреть невостребованный товар, нажмите на соответствующую кнопку в меню продавца – рис. 7.10.

Пример  результата поиска невостребованного  товара представлен на рисунке 7.12.

 

 

Рисунок 7.12. Пример результата поиска невостребованного товара.

 

 

7.1.6 Возможности для администратора.

 

Администратор может выполнять следующие действия: добавлять новый товар, просматривать  невостребованный товар, а также  просматривать зарплату сотрудников  магазина. Добавление товара и просмотр невостребованного товара осуществляется аналогично с данными действиями для продавцов (раздел 7.1.5), только из меню администратора – рис. 7.13.

 

 

Рисунок 7.13. Меню администратора.

 

Для просмотра  зарплаты сотрудников, нажмите на соответствующую  кнопку в меню администратора.

Пример  отчета по зарплате сотрудников представлен  на рисунке 7.14.

 

 

Рисунок 7.14. Пример отчета по зарплате сотрудников.

 

2. Листинг программы.

 

В данном разделе  представлены части листинга приложения, отвечающие за основные функции работы с СУБД PostgreSQL 8.4. Остальные функции приложения, связанные с работой с СУБД, реализованы аналогично ниже представленным.

 

7.2.1 Регистрация нового покупателя.

 

public class Registration implements Command {

    public void execute(HttpServletRequest request, HttpServletResponse response, ServletContext sc, HttpSession session) throws IOException {

        String s = "";

        Connection c = ((JDBCConnectionPool) sc.getAttribute("ConPool")).checkOut();

        String login = request.getParameter("Rlogin");

        String pass = request.getParameter("Rpass");

        String cpass = request.getParameter("RCpass");

        String email = request.getParameter("email");

        String lastname = request.getParameter("Rlastname");

        String firstname = request.getParameter("Rfirstname");

        String thirdname = request.getParameter("Rthirdname");

        String webmoney = request.getParameter("Rwebmoney");

        String address = request.getParameter("Raddress");

        String phone = request.getParameter("Rphone");

 

 if (!phone.equals("")  && !cpass.equals("") && !email.equals("") && !login.equals("") && !pass.equals("") && !lastname.equals("") && !firstname.equals("") && !thirdname.equals("") && !webmoney.equals("") && !address.equals("")){

        try {

            Statement stmt = c.createStatement();

            stmt.executeUpdate("INSERT INTO buyers (last_name,first_name,third_name,login, password, email, webmoney_account_number,home_address, phone) " +

                    "VALUES ('" + lastname + "','" + firstname + "','" + thirdname + "','" + login + "','" + pass + "','" + email + "','" + webmoney + "','" + address + "','" + phone + "');");

            s = "Регистрация прошла успешно. Теперь Вы можете войти на сайт под своим логином и паролем.";

        } catch (SQLException e) {

            s = "К сожалению регистрация невозможна!";

        }

        }else

         s = "<center><h3>Заполенены не все поля!</h3><p><p> <a href=\"javascript:history.go(-1)\" class=\"link\">Назад</a></center>";

        request.setAttribute("answer", s);

        RequestDispatcher dispatcher = sc.getRequestDispatcher("/index.jsp");

        try {

            dispatcher.forward(request, response);

        } catch (ServletException e) {

            e.printStackTrace();

        }

        ((JDBCConnectionPool) sc.getAttribute("ConPool")).checkIn(c);

    }

}

 

7.2.2 Редактирование профиля покупателя.

 

public class EditBuyerProfile implements Command {

    public void execute(HttpServletRequest request, HttpServletResponse response, ServletContext sc, HttpSession session) throws IOException {

        String s = "";

        Connection c = ((JDBCConnectionPool) sc.getAttribute("ConPool")).checkOut();

        String login = request.getParameter("Rlogin");

        String pass = request.getParameter("Rpass");

        String email = request.getParameter("email");

        String lastname = request.getParameter("Rlastname");

        String firstname = request.getParameter("Rfirstname");

        String cpass = request.getParameter("RCpass");

        String thirdname = request.getParameter("Rthirdname");

        String webmoney = request.getParameter("Rwebmoney");

        String address = request.getParameter("Raddress");

        String phone = request.getParameter("Rphone");

        int b_id = (Integer) session.getAttribute("id");

        if (session.getAttribute("role") != null)

            request.setAttribute("succ", true);

        if (!phone.equals("") && !cpass.equals("") && !email.equals("") && !login.equals("") && !pass.equals("") && !lastname.equals("") && !firstname.equals("") && !thirdname.equals("")

                && !webmoney.equals("") && !address.equals("")) {

            try {

                Statement stmt = c.createStatement();

                stmt.executeUpdate("UPDATE buyers SET last_name='" + lastname + "',first_name='" + firstname + "',third_name='" + thirdname + "',login='" + login + "', password='" + pass + "', email='" + email + "', webmoney_account_number='" + webmoney + "',home_address='" + address + "', phone='" + phone + "' " +

                        " WHERE buyers.buyer_id=" + b_id + ";");

                s = "Данные успешно изменены!";

            } catch (SQLException e) {

                s = "<center><h3>К сожалению данные изменить невозможно!</h3><p><p> <a href=\"javascript:history.go(-1)\" class=\"link\">Назад</a></center>";

            }

        } else

            s = "<center><h3>Заполенены не все поля!</h3><p><p> <a href=\"javascript:history.go(-1)\" class=\"link\">Назад</a></center>";

        request.setAttribute("answer", s);

        RequestDispatcher dispatcher = sc.getRequestDispatcher("/index.jsp");

        try {

            dispatcher.forward(request, response);

        } catch (ServletException e) {

            e.printStackTrace();

        }

        ((JDBCConnectionPool) sc.getAttribute("ConPool")).checkIn(c);

    }

}

 

7.2.3 Вывод на экран истории заказов клиента.

 

public class OrdersHistory implements Command {

    public void execute(HttpServletRequest request, HttpServletResponse response, ServletContext sc, HttpSession session) throws IOException {

        String s = "";

        try {

            Connection c = ((JDBCConnectionPool) sc.getAttribute("ConPool")).checkOut();

            Statement stmt = c.createStatement();

            s = FindBadGoods(stmt, session);

            ((JDBCConnectionPool) sc.getAttribute("ConPool")).checkIn(c);

        } catch (SQLException e) {

            e.printStackTrace();

        }

        if (session.getAttribute("role") != null)

            request.setAttribute("succ", true);

        request.setAttribute("search", s);

        RequestDispatcher dispatcher = sc.getRequestDispatcher("/index.jsp");

        try {

            dispatcher.forward(request, response);

        } catch (ServletException e) {

            e.printStackTrace();

        }

    }

 

    private String FindBadGoods(Statement stmt, HttpSession session) {

        ResultSet rs;

        String Name;

        int Count;

        Date DateOfReteil;

        StringBuffer sb = new StringBuffer();

        Integer b_id;

        b_id = (Integer) session.getAttribute("id");

        if (b_id != null && (session.getAttribute("role").equals("buyer"))) {

            sb.append("<table border=\"1\">\n" +