Организация базы данных провайдера

Оглавление

1 Анализ предметной области. 2

1.1 Деловой регламент. 2

1.2 Функциональная структура. 3

1.3 Диаграмма потоков данных. 3

1.4 Выделение информационных объектов и их атрибутов. 3

2 Концептуальная модель. 3

3 Логическое моделирование. 3

3.1 Построение логической модели. 3

3.3 Целостность данных. 3

3.3.1 Целостность объекта. 3

3.3.2 Целостность приложения. 3

3.3.3 Ссылочная целостность. 3

4 Выбор СУБД.. 3

5 Физическая модель. 3

5.1 Нормализация……………………………………………………..18

6 Проектирование и реализация информационной системы.. 3

6.1 Описание средств, использованных при реализации. 3

6.2 Тексты SQL-запросов и результаты их выполнения. 3

6.3 Клиентская часть. 3

7 Заключение. 3

8 Список литературы.. 3

9 Приложения. 3

Приложение A Макетные данные. 3

Приложение B Код клиентской части. 3

1 Анализ предметной области

1.1 Деловой регламент

Заключается договор провайдера с пользователем.

Интернет-провайдер, иногда просто Провайдер, (англ. Internet Service Provider, ISP, букв. "поставщик Интернет-услуги") — организация, предоставляющая услуги доступа к Интернету и иные связанные с Интернетом услуги.

Пользователь - лицо заключившее договор с провайдером на предоставление каких либо услуг.

Пользователь может заключить только один договор. Срок действия договора год, по истечении срока автоматически продляется.

При Заключении договора пользователь выбирает тарифный план.

Тарифный план это совокупность цен и предоставляемых услуг.

Есть возможность выбрать следующие тарифные планы:

Без лимитный 256 - стоимость 350 руб. Ограничение скорости 256 кбит/сек

Без лимитный 512 - стоимость 500 руб. Ограничение скорости 512 кбит/сек

Без лимитный 768 - стоимость 600 руб. Ограничение скорости 768 кбит/сек

Без лимитный 1000 - стоимость 700 руб. Ограничение скорости 1024 кбит/сек

Без лимитный 2000 - стоимость 1200руб. Ограничение скорости 2048 кбит/сек

Провайдер оставляет за собой право перевести пользователя на другой тарифный план в новом месяце, предварительно уведомив его.

Списание денежных средств по договору происходит в конце месяца.

Трафиком считается объем переданной информации. Трафик делиться на:

- Внешний (входящий, исходящий)

- Локальный (входящий, исходящий)

Локальным трафиком считается весь трафик проходящий в диапазоне IP адресов провайдера.

Локальный трафик не ограничивается в скорости. А так же не тарифицируется (бесплатен).

Внешним трафиком считается весь трафик кроме локального.

Тарифицируется только входящий трафик. Исходящий является бесплатным.

Пополнение баланса выполняется при помощи карт оплаты.

Карта оплаты содержит пароль и уникальный номер. Есть вариации карт оплаты:

- На 100 руб.

- На 200 руб.

- На 500 руб.

Для активации карты следует ввести номер и пароль карты на сайте статистики.

После активации карта становиться не действительной.

Номера и пароли всех карт хранятся на сервере провайдера.

1.2 Функциональная структура

Кратко функции БД изображены на функциональной структуре (рис.1.1)

Как видно из рис.1.1 , БД «Провайдер» имеет возможности:

-Заключение договора

- Просмотр клиентов

- Пополнение баланса

- Редактирование договора

- Статистика оплат

- Активность портов

- Полезная информация

1.3 Диаграмма потоков данных

Функциональная структура системы представляет собой набор диаграмм потоков данных (далее - ДПД), которые описывают смысл операций и ограничений. ДПД отражает функциональные зависимости значений, вычисляемых в системе, включая входные значения, выходные значения и внутренние хранилища данных. ДПД - это граф, на котором показано движение значений данных от их источников через преобразующие их процессы к их потребителям в других объектах. ДПД содержит процессы, которые преобразуют данные. Потоки данных, которые переносят данные. Активные объекты, которые производят и потребляют данные, и хранилища данных, которые пассивно хранят данные.

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

Поток данных соединяет выход объекта (или процесса) со входом другого объекта (или процесса). Он представляет промежуточные данные вычислений. Поток данных изображается в виде стрелки между производителем и потребителем данных, помеченной именами соответствующих данных; примеры стрелок, изображающих потоки данных, представлены на рисунке ДПД.

Хранилище данных - это пассивный объект в составе ДПД, в котором данные сохраняются для последующего доступа. Хранилище данных допускает доступ к хранимым в нем данным в порядке, отличном от того, в котором они были туда помещены. Агрегатные хранилища данных, как например, списки и таблицы, обеспечивают доступ к данным в порядке их поступления, либо по ключам. Диаграмма ПД моего проекта представлена на (рис1.2).

На функциональной диаграмме листовыми будут являться функции:

-Заключение договора

- Просмотр клиентов

- Пополнение баланса

- Редактирование договора

- Статистика оплат (Выводит данные об оплатах, совершенных пользователем)

- Активность портов (Выводит суммарный объем информации принятый на каждый порт)

- Полезная информация (Содержит такую информацию, как использование услуг, объем прибыли и др.)

Рисунок 1.1 Функциональная структура

Рисунок 1.2 Диаграмма потоков данных

1.4 Выделение информационных объектов и их атрибутов

В результате детального анализа предметной области, построения функциональной структуры и схемы потоков данных были выделены следующие объекты:

1. Клиент

1.1Номер

1.2 Ф.И.О.

1.3 Паспорт

1.4 Регистрация

2. Договор

2.1 Номер

2.2 Дата приёма на работу

2.3 Контактный телефон

2.4 Место нахождения

3. Карта

3.1 Номер

3.2 Сумма

3.3 Пароль

3.4 Состояние

4. История оплат

4.1 Дата, время

4.2 Номер карты

4.3 Сумма

5. Дебит

5.1Сумма

5.2Дата время

6. Услуга

6.1 Шифр

6.2 Название

6.3 Стоимость

6.4 Описание

7 IP

7.1 С порта

7.2 На порт

7.3 С адреса

7.4 На адрес

7.5 С интерфейса

7.6 На интерфейс

7.7 Трафик

7.8 Дата, время

2 Концептуальная модель

При построении модели сущность-связь на начальном этапе каждый информационный объект заменяем сущностью, при этом каждое свойство объекта становится атрибутом сущности.

Теперь построим предварительную концептуальную модель и покажем количественное значение мощностей связей Рисунке 2.1.

У данной модели есть следующие связи:

- Провайдер – Договор: У одного провайдера может быть много клиентов, А у каждого клиента может быть только один провайдер.

- Договор – Дебит: Под дебитом здесь понимается списание средств. И таким образом: по одному договору может быть несколько списаний средств. И одно списание средств может быть только у одного договора.

- Договор – Клиент: Клиент может заключить только один договор. И один договор оформляется только на одного клиента.(Но тем не менее в сущность договор идет внешним ключом id из таблицы “Клиент” )

- Услуга договор: Одна услуга может предоставляться по нескольким договорам, и один договор может иметь несколько услуг.

- Карта оплаты – Провайдер: Провайдер предоставляет множество карт оплат. И одна карта оплаты принадлежит только одному провайдеру.

- IP – договор: В сущности IPсодержатся единичные записи о трафике проходящем от и до клиента. И тогда одна запись Ipпринадлежит только одному договору. А у договора может быть много записей о трафике.

Рисунок 2.1 Предварительная концептуальная модель

3 Логическое моделирование

3.1 Построение логической модели

По концептуальной модели необходимо построить логическую модель. При этом атрибуты добавляются, в сущности, соответственно связям, показанным на концептуальной модели.

Например, в сущность «Oplata» добавляются атрибуты «Useri» (Id клиента – первичный ключ сущности «Useri»). Таким же образом добавляются атрибуты и в другие сущности.

Добавленные, в сущности-потомки, атрибуты являются внешними ключами для сущностей-предков, в которых данные атрибуты являются первичными ключами. Логическая модель приведена на (рис. 3.1).

Рисунок 3.1 Логическая модель

3.3 Целостность данных

3.3.1 Целостность объекта

Ограничения целостности объектов защищают отношения (таблицы-объекты) от неправильного внесения изменений, связанных с удалением существующих картежей и вставкой новых.

Например, в нашей базе данных в отношение «Пользователь» при вставке информации о новом клиенте, необходимо сначала вставить значение в поле, являющееся первичным ключом («id»), а затем уже заносить информацию в остальные поля. Аналогично и с удалением, например, при удалении картежа из таблицы «Dogovor», необходимо сначала удалить информацию из вторичных атрибутов, а затем уже удалять значение первичного ключа. Целостность объекта реализуется самой СУБД, и обычно пользователю нет необходимости об этом беспокоиться.

3.3.2 Целостность приложения

Ограничения целостности приложения определяют отношения, в которые пользователь может вносить изменения, связанные с удалением, обновлением и вставкой. Ведь в базе данных существуют и такие отношения, в которые изменения вноситься не должны (по крайней мере, пользователем) – эти отношения формируются один раз при создании базы данных и далее в течение долгого времени данные в них не меняются. Эти отношения называются «справочниками» (точнее, некоторые из них).

В базе данных «Провайдер» это отношения «Usluga» (хотя, возможность изменения этой таблицы реализована в приложении).

Ограничения ссылочной целостности формируются при проектировании приложения (клиентской части) к базе данных.

3.3.3 Ссылочная целостность

Ссылочная целостность отражает взаимосвязь между значениями атрибутов, входящих в разные таблицы – родительские и дочерние

Ограничения ссылочной целостности предполагают:

1) Задание пары ключей родительского и внешнего ключей;

2) Родительский и внешний ключи могут быть простыми, либо составными. Для простых ключей должно совпадать количество атрибутов, входящих в родительский и внешний ключи, а также попарно типы и размеры данных.

Требования к родительскому ключу – уникальность, либо неопределённость, поэтому в качестве родительского ключа выбирается либо первичный ключ, либо потенциальный ключ.

Значения внешнего ключа должны совпадать с одним из значений родительского ключа, либо должны быть неопределёнными. Значения внешнего ключа могут повторяться в различных картежах дочерней таблицы (конечно, если это поле не является первичным ключом для этой таблицы).

Таким образом, значения ссылочной целостности защищают базу данных от ошибок, связанных с вставкой, удалением и обновлением данных.

Например, в нашу базу данных в таблицу «Dogovor» нельзя занести информацию о новом клиенте без внесения данных об этом клиенте в таблицу «Useri», т.к. отношение «Useri» является родительским для отношения «Dogovor». А в таблицу «Ip» нельзя внести информацию о работнике, которого нет в отношении «Dogovor» (т.е. нельзя указать расходование трафика не указав причины(пользователя)).

Такая же ситуация обстоит и с удалением и обновлением картежей в отношениях.

Например, нельзя удалить картеж из отношения «Useri», так как у него имеется потомок – отношение «Dogovor», а если возникает необходимость удаления, то соответствующие картежи необходимо удалить и из всех дочерних отношений.

Аналогичная связь прослеживается и в других отношениях.

4 Выбор СУБД

Для реализации базы данных «Провайдер» я выбрал СУБД Oracle 10g . Это объясняется следующими возможностями данной СУБД:

- Поддержка языка SQL, который достаточно прост в обращении и позволяет без особых затрат времени извлекать любую информацию из базы данных;

- Real Application Cluster (RAC) обеспечивает работу одного экземпляра базы данных на нескольких узлах grid, позволяя управлять нагрузкой и гибко масштабировать систему в случае необходимости;

- Automatic Storage Management (ASM) позволяет автоматически распределять данные между имеющимися ресурсами систем хранения данных, что повышает отказоустойчивость системы и снижает общую стоимость владения (TCO);

- Производительность. Oracle Database 10g позволяет автоматически управлять уровнями сервиса и тиражировать эталонные конфигурации в рамках всей сети;

- Простые средства разработки. Новый инструмент разработки приложений HTML DB позволяет простым пользователям создавать эффективные приложения для работы с базами данных в короткие сроки;

- Самоуправление. Специальные механизмы Oracle Database 10g позволяют самостоятельно перераспределять нагрузку на систему, оптимизировать и корректировать SQL-запросы, выявлять и прогнозировать ошибки;

- Большие базы данных. Максимальный размер экземпляра базы данных Oracle может достигать 8 экзабайт;

- Недорогие серверные системы. Oracle Database 10g может использовать недорогие однопроцессорные компьютеры или модульные системы из "серверов-лезвий";

- Обеспечение транзакционной целостност, связана с организацией одновременного доступа значительного количества пользователей к данным размещенным на физическом носителе. Доступ производится в процессе выполнения транзакций представляющей собой логическую связь последовательных запросов и операций манипулирования данных;

- Буферирование. На обработку первого запроса уходит больше времени, чем на все последующие запросы это характеризуется тем, что их повторное использование позволяет исключить физический ввод данных с дискового устройства и уменьшает время выполнения запроса, ограничение объема буфера приводит к перманентному вытеснению из него раннее считанных блоков.

- Ряд вышеперечисленных возможностей, выделяет СУБД Oracle 10g как наиболее подходящую для реализации нашей базы данных по предоставляемым возможностям.

5 Физическая модель

Физическая модель данных представлена реляционными таблицами, в которых в виде кортежей реляционных отношений хранится информация. Для хранения информации выбраны Oracle 10g таблицы, как удобные в работе и распространенные. Таблицы Oracle 10g поддерживают многие приложения, что обеспечивает более высокую универсальность системы.

Таблица 5.1 Типы данных

Для создания таблиц мы использовали следующие скрипты:

-- Create table

createtable USLUGI

(

shifr INTEGERnotnull,

name1 VARCHAR2(250),

stoimost FLOAT,

type_us VARCHAR2(250)

);

-- Create/Recreate primary, unique and foreign key constraints

alter table USLUGI

add primary key (SHIFR);

-- Create table

createtable USLUGA_DOGOVOR

(

shifr INTEGERnotnull,

nomer CHAR(7) notnull

)

-- Create/Recreate primary, unique and foreign key constraints

altertable USLUGA_DOGOVOR

addforeignkey (SHIFR)

references USLUGI (SHIFR);

altertable USLUGA_DOGOVOR

addforeignkey (NOMER)

references DOGOVOR (NOMER);

-- Create table

createtable USERI

(

idNUMBERnotnull,

pasport NUMBERnotnull,

fio VARCHAR2(250),

registration VARCHAR2(250),

telephone VARCHAR2(250)

)

-- Create/Recreate primary, unique and foreign key constraints

altertable USERI

addunique (ID);

-- Create table

createtable PROVAIDER

(

inn NUMBERnotnull,

nameorg VARCHAR2(250),

adress VARCHAR2(250),

director VARCHAR2(250),

telephone NUMBER

)

-- Create/Recreate primary, unique and foreign key constraints

altertable PROVAIDER

addprimarykey (INN)

-- Create table

createtable OPLATA

(

id_karti NUMBERnotnull,

data_vremya DATE,

summa FLOAT,

id_d CHAR(7)

)

-- Create/Recreate primary, unique and foreign key constraints

altertable OPLATA

addforeignkey (ID_D)

references DOGOVOR (NOMER);

altertable OPLATA

addforeignkey (ID_KARTI)

references KARTA (NOMER)

disable;

-- Create table

createtable KARTA

(

nomer NUMBERnotnull,

summa FLOAT,

sostoyanie INTEGER,

password_k VARCHAR2(250),

inn NUMBER

)

-- Create/Recreate primary, unique and foreign key constraints

altertable KARTA

addforeignkey (INN)

references PROVAIDER (INN);

-- Create table

createtable IP

(

time1 TIMESTAMP(6),

with_adress VARCHAR2(250),

with_port NUMBER,

on_adress VARCHAR2(250),

on_port VARCHAR2(250),

bait NUMBER,

with_interfase NUMBER,

on_interfase INTEGER,

nomer CHAR(7)

)

-- Create/Recreate primary, unique and foreign key constraints

altertable IP

addforeignkey (NOMER)

references DOGOVOR (NOMER);

-- Create table

createtable DOGOVOR

(

nomer CHAR(7) notnull,

date2 DATE,

date3 DATE,

inn NUMBER,

id_u NUMBER,

money FLOAT,

pass VARCHAR2(250),

status NUMBER

)

-- Create/Recreate primary, unique and foreign key constraints

altertable DOGOVOR

addprimarykey (NOMER)

altertable DOGOVOR

addforeignkey (INN)

references PROVAIDER (INN);

altertable DOGOVOR

addforeignkey (ID_U)

references USERI (ID);

-- Create table

createtable DEBIT

(

time1 DATE,

money FLOAT,

why VARCHAR2(255),

id_d CHAR(7)

)

-- Create/Recreate primary, unique and foreign key constraints

altertable DEBIT

addforeignkey (ID_D)

referencesDOGOVOR (NOMER);

6 Проектирование и реализация информационной системы

6.1 Описание средств, использованных при реализации

Для создания базы данных, сначала создаются таблицы с помощью средств, предлагаемых СУБД Oracle 10g :

- Создание таблицы помощью графического инструментального средства конфигурирования Oracle Database Configuration Assistant (DBCA);

- Создание таблицы шаблонов баз данных (database templates),;

- Создание таблицы путём ввода данных.

При создании таблиц был использован 1-й вариант, как наиболее удобный из всех предлагаемых. Сначала создаётся каркас таблицы, отмечаются ключевые поля, устанавливаются типы данных для каждого поля, а уже потом таблицы заполняются макетными данными (приведены в приложении A).

После того, как таблицы созданы, необходимо их связать по ключевым полям.

Уже на данном этапе можно сформулировать достаточно большое количество запросов, которые понадобятся в дальнейшем при создании форменного приложения.

При создании запросов в СУБД Oracle 10g был использован режим SQL . Это объясняется удобством и простотой языка SQL, который представляет достаточно богатый набор средств для формирования запросов.

6.2 Тексты SQL-запросов и результаты их выполнения

Ниже приведены все SQL-запросы, использованные в процессе создания информационной системы.

1. Какой номер последнего договора?

SELECT *

FROM DOGOVOR

WHERE NOMER = (SELECT MAX(NOMER)

FROM DOGOVOR)

2. Какие предоставляются тарифы? Вывести, упорядочив по шифру.

SELECT *

FROM USLUGI

WHERE type_us = 'tarif'

ORDERBY STOIMOST

3. Вывести историю оплат заданного пользователя. Например, npi001.

SELECT *

FROM OPLATA

WHERE ID_D = 'npi001'

4. Найти карту оплаты с заданным номером и паролем.

SELECT *

FROM KARTA

WHERE NOMER = '1'AND PASSWORD_K = '1'

5. Установить статус заданной карты в состояние использована.

UPDATE KARTA

SET SOSTOYANIE = 1

WHERE NOMER = ‘nomer’ AND PASSWORD_K = 'password'

6. Выбрать договор по заданному номеру.

SELECT *

FROM DOGOVOR

WHERE NOMER = 'npi001'

7. Обновить значение средств на счету в заданном контракте.

UPDATE DOGOVOR

SET MONEY = '100'

WHERE NOMER = 'npi001'

8. Добавить нового пользователя.

INSERT INTO USERI VALUES(14,2345,'Ф. И. О.','Бомж','1234567')

9. Выбрать все номера договоров упорядочив по возрастанию.

SELECT NOMER

FROM DOGOVOR

ORDER BY NOMER

10. Выдать информацию о пользователе, включая информацию по договору.

SELECT *

FROM USERI,DOGOVOR

WHERE USERI.ID = DOGOVOR.ID_U AND DOGOVOR.NOMER = 'npi001’

11. Выдать список услуг с количеством клиентов, которые подключены здесь.

12. Какая услуга ни разу не использована?

SELECT SHIFR, NAME1

FROM USLUGI d1

WHERENOTEXISTS (

SELECT USLUGI.SHIFR

FROM USLUGA_DOGOVOR, USLUGI

WHERE d1.SHIFR = USLUGA_DOGOVOR.SHIFR)

13. Какая прибыль в этом месяце?

SELECTSUM(MONEY) AS "Profit"

FROM DEBIT

WHERE TO_CHAR(TIME1, 'mm.yyyy') = TO_CHAR(sysdate, 'mm.yyyy')

Результат запроса: 1260.

14. Максимальное число байт принято от куда?

SELECT TIME1, WITH_ADRESS, WITH_PORT, BAIT, ip.nomer

FROM IP

WHERE BAIT = (SELECT MAX(BAIT)

FROM IP

WHERE IP.WITH_INTERFASE = 5)

15. Выдать полную информацию о клиентах.

SELECT d1.NOMER, d1.money, d4.name1, d1.DATE2, d1.DATE3, d2.fio,

d2.pasport, d2.registration, d2.telephone

FROM DOGOVOR d1, USERI d2 , USLUGA_DOGOVOR d3, USLUGI d4

WHERE (d1.id_u = d2.id) AND d3.nomer = d1.nomer

AND d4.shifr = d3.shifr AND d4.type_us = 'tarif'

16. Вывести отчет о списании средств с абонента

SELECT *

FROM DEBIT

WHERE ID_D = 'npi001 ’

17. Сколько и на какие порты принималась информация в сумме?

18. Какой договор истекает в текущем месяце?

SELECT d1.NOMER, d1.DATE2, d1.DATE3, d2.fio, d2.pasport,d2.registration, d2.telephone

FROM DOGOVOR d1, USERI d2

WHERE d1.id_u = d2.id AND (MONTHS_BETWEEN(sysdate, date3) < 0) AND ( MONTHS_BETWEEN(sysdate, date3) > -1)

19. Какой договор уже истек?

SELECT d1.NOMER, d1.DATE2, d1.DATE3, d2.fio, d2.pasport,d2.registration, d2.telephone

FROM DOGOVOR d1, USERI d2

WHERE (d1.id_u = d2.id) AND (MONTHS_BETWEEN(date3, sysdate) < 0)

20. От куда и во сколько принималась информация?

SELECT *

FROM "IP"

WHERE with_interfase=5

21. Сколько пользователи скачали?

SELECT NOMER, SUM(BAIT)/(1024*1024) AS "Trafic"

FROM "IP"

WHERE IP.WITH_INTERFASE = 5

GROUPBY NOMER

ORDERBY NOMER

22. Какой клиент принес больше всего прибыли?

SELECT *

FROM (SELECTSUM(SUMMA) AS "MONEY", id_d

FROM OPLATA

GROUPBY ID_D

ORDERBY1/MONEY) d

WHERE MONEY = (SELECTMAX(SUM(SUMMA))

FROM OPLATA

GROUPBY ID_D

)

23. Какие клиенты еще не принесли прибыли?

SELECT NOMER, FIO

FROM DOGOVOR INNERJOIN USERI ON DOGOVOR.ID_U = USERI.ID

WHERE NOMER notin

(SELECT id_d

FROM OPLATA

GROUPBY ID_D)

24. Какая услуга подключена у каждого пользователя?

SELECT SHIFR, NAME1 , STOIMOST

FROM

(SELECTCOUNT(NOMER) AS "COUNTS", SHIFR AS "ID1"

FROM USLUGA_DOGOVOR

GROUPBY SHIFR) INNERJOIN USLUGI ON ID1 = USLUGI.SHIFR

WHERE COUNTS =

(SELECTCOUNT(NOMER)

FROM DOGOVOR)

25. Какую прибыль планируется получить от услуг в конце месяца?

SELECT MONEY * STOIMOST AS "PROFIT", SHIFR

FROM (SELECTCOUNT(NOMER) AS "MONEY", USLUGI.SHIFR AS "ID1"

FROM USLUGA_DOGOVOR INNERJOIN USLUGI ON USLUGA_DOGOVOR.SHIFR = USLUGI.SHIFR

GROUPBY USLUGI.SHIFR) INNERJOIN USLUGI ON USLUGI.SHIFR = "ID1"

ORDERBY1/"PROFIT"

26. Найти услугу которая принесет наибольшую прибыль в конце месяца.

SELECT PROFIT, SHIFR, NAME1

FROM (SELECT MONEY * STOIMOST AS "PROFIT", SHIFR, NAME1

FROM (SELECTCOUNT(NOMER) AS "MONEY", USLUGI.SHIFR AS "ID1"

FROM USLUGA_DOGOVOR INNERJOIN USLUGI ON USLUGA_DOGOVOR.SHIFR = USLUGI.SHIFR

GROUPBY USLUGI.SHIFR) INNERJOIN USLUGI ON USLUGI.SHIFR = "ID1"

ORDERBY1/"PROFIT")

WHERE PROFIT = (

SELECTMAX(MONEY * STOIMOST) AS "PROFIT"

FROM (SELECTCOUNT(NOMER) AS "MONEY", USLUGI.SHIFR AS "ID1"

FROM USLUGA_DOGOVOR INNERJOIN USLUGI ON USLUGA_DOGOVOR.SHIFR = USLUGI.SHIFR

GROUPBY USLUGI.SHIFR) INNERJOIN USLUGI ON USLUGI.SHIFR = "ID1")

27. Какие услуги принесут доход ниже среднего?

SELECT PROFIT, SHIFR, NAME1

FROM (SELECT MONEY * STOIMOST AS "PROFIT", SHIFR, NAME1

FROM (SELECTCOUNT(NOMER) AS "MONEY", USLUGI.SHIFR AS "ID1"

FROM USLUGA_DOGOVOR INNERJOIN USLUGI ON USLUGA_DOGOVOR.SHIFR = USLUGI.SHIFR

GROUPBY USLUGI.SHIFR) INNERJOIN USLUGI ON USLUGI.SHIFR = "ID1"

ORDERBY1/"PROFIT")

WHERE PROFIT < (

SELECTAVG(MONEY * STOIMOST) AS "PROFIT"

FROM (SELECTCOUNT(NOMER) AS "MONEY", USLUGI.SHIFR AS "ID1"

FROM USLUGA_DOGOVOR INNERJOIN USLUGI ON USLUGA_DOGOVOR.SHIFR = USLUGI.SHIFR

GROUPBY USLUGI.SHIFR) INNERJOIN USLUGI ON USLUGI.SHIFR = "ID1")

ORDERBY PROFIT

6.3 Клиентская часть

Клиентская часть была реализована в виде сайта на PHP. В качестве WEBсервера был использован бесплатный сервер APACHE.

Почему был выбран WEBинтерфейс? Так как данный проект нацелен на реализацию обслуживания провайдера, то клиентам проще всего получить информацию просто зайдя на сайт статистики, нежели устанавливать специальное ПО.

Apache HTTP-сервер (произносится /ə.ˈpæ.ʧi/, сокращение от англ. a patchy server) — свободный веб-сервер.

Apacheявляется кроссплатформенным ПО, поддерживает операционные системы Linux, BSD, MacOS, MicrosoftWindows, NovellNetWare, BeOS.

Основными достоинствами Apache считаются надёжность и гибкость конфигурации. Он позволяет подключать внешние модули для предоставления данных, использовать СУБД для аутентификации пользователей, модифицировать сообщения об ошибках и т. д. Поддерживает IPv6.

Ядро Apache включает в себя основные функциональные возможности, такие как обработка конфигурационных файлов, протокол HTTP и система загрузки модулей. Ядро (в отличие от модулей) полностью разрабатывается Apache Software Foundation, без участия сторонних программистов.

Теоретически, ядро apache может функционировать в чистом виде, без использования модулей. Однако, функциональность такого решения крайне ограничена.

Ядро Apache полностью написано на языке программирования C.

Система конфигурации Apache основана на текстовых конфигурационных файлах. Имеет три условных уровня конфигурации:

Конфигурация сервера (httpd.conf).

Конфигурация виртуального хоста (httpd.conf c версии 2.2 extra/httpd-vhosts.conf).

Конфигурация уровня директории (.htaccess).

Имеет собственный язык конфигурационных файлов, основанный на блоках директив. Практически все параметры ядра могут быть изменены через конфигурационные файлы, вплоть до управления MPM. Большая часть модулей имеет собственные параметры.

Часть модулей использует в своей работе конфигурационные файлы операционной системы (например /etc/passwd и /etc/hosts).

Помимо этого, параметры могут быть заданы через ключи командной строки.

Для работы PHP с Oracle 10gбыл использован модуль php_oci8. Он представляет из себя динамическую библиотеку данных DLL.

В ходе выполнения проекта были выполнены функции представленные на рисунке 6.1. Условные обозначения представлены в таблице 6.1

Таблица 6.1 Условные обозначения

Рисунок 6.1 Функции клиентской части

Рисунок 6.2 Статистика оплат

Рисунок 6.3 Добавление клиента

Рисунок 6.4 Список клиентов

Рисунок 6.5 Пополнение баланса.

Рисунок 6.6 Изменение данных о пользователе.

Рисунок 6.7 Активность портов

Рисунок 6.8 Различная информация

7 Заключение

В результате выполнения курсового проекта была спроектирована информационная система «Провайдер». Была проанализирована предметная область, изучены её функции, описаны смысл операций и ограничений с помощью диаграммы потоков данных. На основе выделенных информационных объектов и их атрибутов построена концептуальная и логическая модель. Для защиты нашей базы данных от неправильного внесения изменений, целостность данных была разграничена на три составляющие: целостность объектов, целостность приложения и ссылочная целостность.

Физическая модель данных представлена реляционными таблицами, вся информация которых содержится в базе данных, реализованной на СУБД Oracle 10g. Была спроектирована и реализована информационная система описанная созданием базы и запросов на языке SQL.

Так же в процессе выполнения проекта была реализована клиентская часть в виде сайта. Сайт был выполнен в виде шаблона для дальнейшего использования. Т.е в нем минимум дизайна и максимум функциональности. В целом при создании сайта статистики данная модель будет вполне работоспособна.

8 Список литературы

1. Дж. Грофф, П. Вайнберг: «Энциклопедия SQL», Питер, 2004 год;

2. Г.А Черноморов «Базы данных в среде промышленных СУБД»- Новочеркасск : ЮРГТУ, 2006;

3. Дейт К. Введение в системы баз данных. К.; М.; Спб; Издат. Дом «Вильямс». 2000.

4. Г.Хансен, Д. Хансен «Базы данных:разработка и управление»//Пер. с англ. – М.: ЗАО «Издательство БИНОМ», - 2003.-704 с.:ил

5. М.Я. Выгодский: «Проектирование баз данных», Москва, 2005.

6.http://rostow-na-donu.ru/organizacii/dsbw~tours.html

9 Приложения

Приложение AМакетные данные

Таблица 9.A.1 «Debit».

Таблица 9.A.2 «Dogovor».

Таблица 9.A.3 «Ip»

Таблица9.A.4 «Karta».

Таблица 9.A.5 «Oplata»

Таблица9.A.6 «Provaider».

Таблица 9.A.7 «Useri».

Таблица9.A.8 «USluga_Dogovor».

Таблица9.A.9 «Usluga».

Приложение BКод клиентской части

Файл “add_dogovor.php”

<HTML>

<HEAD>

<TITLE>Сайт статистики::Заключение договора</TITLE>

</HEAD>

<BODY>

<TABLE BORDER="1" align="center">

<TR >

<TD ><img src="logotip.jpg"></TD>

<TD align="center">Заключение договора</TD>

</TR>

<TR >

<TD align="center"><a href="http://winer/statistic.php"> Статистика оплат</a></TD>

<TD rowspan="7" align="justify" valign=top>

<?php

/*------------------------------------------------------------------------------------------------------*/

$ALTA2 = "(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SID = ALTA2)

(SERVER = DEDICATED)

)

)";

if(isset($_REQUEST['next'])) {/*если вход выполнен со страницы регистрации абонента*/

?>

<form action="add_dogovor.php" method = post>

<table>

<tr><td>Логин</td><td>

<?

if($db1=OCILogon("b27", "b27", $ALTA2)) {

$stmt = oci_parse($db1,"

SELECT *

FROM DOGOVOR

WHERE NOMER =(SELECT MAX(NOMER)

FROM DOGOVOR)

");

oci_execute($stmt);

oci_fetch_all($stmt, $results);

$NomerDogovor = $results['NOMER'][0][3] . $results['NOMER'][0][4] . $results['NOMER'][0][5];

$NomerDogovor++;

$Nomer_dogovora;

if($NomerDogovor < 10) $Nomer_dogovora = "npi00" . $_REQUEST['NomerDogovor'];

else if(($NomerDogovor > 10 )&&($NomerDogovor < 100 )) {$Nomer_dogovora = "npi0" . $NomerDogovor;}

else if(($NomerDogovor > 100 )&&($NomerDogovor < 1000 )) $Nomer_dogovora = "npi" . $NomerDogovor;

echo $Nomer_dogovora;

$now_date = date("d.m.Y");

?>

<input type=hidden name=NomerDogovor value=<?echo $Nomer_dogovora;?>>

<input type=hidden name=IdUser value=<?echo $_REQUEST['IdUser'];?>>

<input type=hidden name=now_date value=<?echo $now_date;?>>

<?

OCILogoff($db1);

}else echo "</br>Не удалось соедениться с базой данных";

?>

</td></tr>

<tr><td>Дата заключения</td><td><?echo $now_date?></td></tr>

<tr><td>Пароль</td><td><input type=text name="password" value="password"></td></tr>

<tr><td>Тариф</td><td>

<? /*выбор тарифа*/

if($db1=OCILogon("b27", "b27", $ALTA2)) {

$stmt = oci_parse($db1,"

SELECT *

FROM USLUGI

WHERE type_us = 'tarif'

ORDER BY STOIMOST");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

//echo "<pre>";

//var_dump($results);

//echo "</pre>";

echo "<select name=tarif size=1>";

for($i = 0; $i < $nrows; $i++){

echo "n</br><option value={$results['SHIFR'][$i]}>{$results['NAME1'][$i]}</option>";

}

echo "</select>";

OCILogoff($db1);

}else{

echo "</br>Не удалось соедениться с базой данных";

}

?>

</td></tr>

<tr><td colspan=2></td></tr>

</table>

<input type=submit name="Add" value="Добавить"></br>

</form>

<?

}

else{

if($db1=OCILogon("b27", "b27", $ALTA2)) {

$Next_date = date("Y") + 1;

$Next_date = date("d.m") . "." . $Next_date;

$Nomer_dogovora = $_REQUEST['NomerDogovor'];

$tempstr = "INSERT INTO DOGOVOR VALUES('{$Nomer_dogovora}', TO_DATE('{$_REQUEST['now_date']}', 'DD.MM.YYYY'), TO_DATE('{$Next_date}','DD.MM.YYYY'), '777777777777',{$_REQUEST['IdUser']}, 0 ,'{$_REQUEST['password']}','1')";

echo $tempstr;

$stmt = oci_parse($db1,$tempstr);

oci_execute($stmt);

oci_commit($db1);

echo "<pre>";

var_dump($_REQUEST['tarif'][0]);

$tempstr = "INSERT INTO USLUGA_DOGOVOR VALUES('{$_REQUEST['tarif'][0]}', '{$Nomer_dogovora}')";

echo "</pre></br>".$tempstr;

$stmt = oci_parse($db1,$tempstr);

oci_execute($stmt);

oci_commit($db1);

?>

<form action="" method = get>

</br><input type=submit name="next" value="Next">

</form>

<?

OCILogoff($db1);

}else{

echo "/n</br><h1>Не удалось соедениться с сервером баз данных</h1>/n";

}

}

/*------------------------------------------------------------------------------------------------------*/

?>

</TD>

</TR>

<TD height=39 align="center"><a href="http://winer/add_user.php">Добавление клиента</a></TD>

<TR >

<TD align="center" ><a href="http://winer/spisok_userov.php">Список клиентов</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/add_schet.php?nomer_d=npi001">Пополнение баланса</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/alter_user.php"> Изменение данных</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/any_information.php"> Некоторая информация</a></br></TD>

</TR>

<TR >

<TD align="center" valign="top" ><a href="http://winer/summ_port.php">Активность портов</a></TD>

</TR>

</TABLE>

</BODY>

</HTML>

Файл “add_schet.php”

<HTML>

<HEAD>

<TITLE>Сайт статистики::Пополнение баланса</TITLE>

</HEAD>

<BODY>

<TABLE BORDER="1" align="center">

<TR >

<TD ><img src="logotip.jpg"></TD>

<TDalign="center">Пополнение баланса</TD>

</TR>

<TR >

<TD align="center"><a href="http://winer/statistic.php"> Статистика оплат</a></TD>

<TD rowspan="7" align="justify" valign=top>

<?php

/*------------------------------------------------------------------------------------------------------*/

$ALTA2 = "(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SID = ALTA2)

(SERVER = DEDICATED)

)

)";

function print_karta($nomer_d){

$ALTA2 = "(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SID = ALTA2)

(SERVER = DEDICATED)

)

)";

if($db1=OCILogon("b27", "b27", $ALTA2)){

$stmt = oci_parse($db1,"

SELECT *

FROM OPLATA

WHERE ID_D = '{$nomer_d}'

");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

echo "<table border=1 width='40%'>n";

echo "<tr><td width=100>Дата</td><td>Номер карты</td><td width=60>Сумма</td></tr>";

for ($i = 0; $i < $nrows; $i++ ) {

echo "<tr><td>" . $results['DATA_VREMYA'][$i] ."</td><td>" . $results['ID_KARTI'][$i] ."</td><td>" . $results['SUMMA'][$i] ."</td></tr>";

}

echo "</table>";

OCILogoff($db1);

}else echo "</br> Не удалось подключиться к базе";

}

if(!isset($_REQUEST['put'])) {/*если вход выполнен не из за нажатия кнопки*/

?>

</br>

<form action=add_schet.php method=post>

Номер карты:<input type=text size=7 name=nomer value=123456> &nbsp;&nbsp;&nbsp;

Пароль:<input type=text size=7 name=password value=pass>

<input type=hidden name=nomer_d value=<?echo $_REQUEST['nomer_d'];?>>

</br><input type=submit name=put value=Положить>

</form>

<?

print_karta($_REQUEST['nomer_d']);

}else{

if($db1=OCILogon("b27", "b27", $ALTA2)){

$stmt = oci_parse($db1,"

SELECT *

FROM KARTA

WHERE NOMER = '{$_REQUEST['nomer']}' AND PASSWORD_K = '{$_REQUEST['password']}'");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

if($nrows == 0) {

echo "Неправильно введен номер или пароль</br>";

?>

<form action=add_schet.php method=post>

Номер карты:<input type=text size=7 name=nomer value=123456> &nbsp;&nbsp;&nbsp;

Пароль:<input type=text size=7 name=password value=pass>

<input type=hidden name=nomer_d value=<?echo $_REQUEST['nomer_d'];?>>

</br><input type=submit name=put value=Положить>

</form>

<?

print_karta($_REQUEST['nomer_d']);

}else

if($results['SOSTOYANIE'][0] == 1){

echo "Эта карта уже использовалась</br>";

?>

<form action=add_schet.php method=post>

Номер карты:<input type=text size=7 name=nomer value=123456> &nbsp;&nbsp;&nbsp;

Пароль:<input type=text size=7 name=password value=pass>

<input type=hidden name=nomer_d value=<?echo $_REQUEST['nomer_d'];?>>

</br><input type=submit name=put value=Положить>

</form>

<?

print_karta($_REQUEST['nomer_d']);

}else{

$dat = date("d.m.Y H:i");

$stmt = oci_parse($db1,"

UPDATE KARTA

SET SOSTOYANIE = 1

WHERE NOMER = '{$_REQUEST['nomer']}' AND PASSWORD_K = '{$_REQUEST['password']}'");

oci_execute($stmt);

echo "</pre>";

$tempstr = "INSERT INTO OPLATA VALUES('{$_REQUEST['nomer']}', TO_DATE('{$dat}','dd.mm.yyyy HH24:MI'),'{$results['SUMMA'][0]}', '{$_REQUEST['nomer_d']}')";

$stmt = oci_parse($db1,$tempstr);

oci_execute($stmt);

$stmt = oci_parse($db1,"

SELECT *

FROM DOGOVOR

WHERE NOMER = '{$_REQUEST['nomer_d']}'");

oci_execute($stmt);

oci_fetch_all($stmt, $results2);

$temp = $results['SUMMA'][0] + $results2['MONEY'][0];

$tempstr ="UPDATE DOGOVOR

SET MONEY = '{$temp}'

WHERE NOMER = '{$results2['NOMER'][0]}'

";

$stmt = oci_parse($db1,$tempstr);

oci_execute($stmt);

oci_commit($db1);

OCILogoff($db1);

?>

<form action=add_schet.php method=post>

</br>

Номер карты:<input type=text size=7 name=nomer value=123456> &nbsp;&nbsp;&nbsp;

Пароль:<input type=text size=7 name=password value=pass>

<input type=hidden name=nomer_d value=<?echo $_REQUEST['nomer_d'];?>>

</br><input type=submit name=put value=Положить>

</form>

<?

print_karta($_REQUEST['nomer_d']);

}

}else{echo "</br> Нет доступа к базе данных при чтении оплаты";}

}

/*------------------------------------------------------------------------------------------------------*/

?>

</TD>

</TR>

<TD height=39 align="center"><a href="http://winer/add_user.php">Добавление клиента</a></TD>

<TR >

<TD align="center" ><a href="http://winer/spisok_userov.php">Список клиентов</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/add_schet.php?nomer_d=npi001">Пополнение баланса</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/alter_user.php"> Изменение данных</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/any_information.php"> Некоторая информация</a></br></TD>

</TR>

<TR >

<TD align="center" valign="top" ><a href="http://winer/summ_port.php">Активность портов</a></TD>

</TR>

</TABLE>

</BODY>

</HTML>

Файл “add_user.php”

<HTML>

<HEAD>

<TITLE>Сайт статистики::Добавление пользователя</TITLE>

</HEAD>

<BODY>

<TABLE BORDER="1" align="center">

<TR >

<TD ><img src="logotip.jpg"></TD>

<TDalign="center">Добавление пользователя</TD>

</TR>

<TR >

<TD align="center"><a href="http://winer/statistic.php"> Статистика оплат</a></TD>

<TD rowspan="7" align="justify" valign=top>

<?php

/*------------------------------------------------------------------------------------------------------*/

if(!isset($_REQUEST['doGo'])) {?>

<form action="add_user.php" method = post>

<table>

<tr><td>Паспорт: </td><td><input type=text name="pasport" value="Паспорт"></td></tr>

<tr><td>Ф. И.О.: </td><td><input type=text name="fio" value="Ф. И. О."></td></tr>

<tr><td>Место Регистрации в г. Новочеркасске:</td><td><input type=text name="registration" value="Место Регистрации в г. Новочеркасске:"></td></tr>

<tr><td>Номер Телефона</td><td><input type=text name="telephone" value="Телефон"></td></tr>

<tr><td colspan=2></td></tr>

</table>

<input type=submit name=doGo value="Зарегистрировать"></br>

</form>

<?} else{

$ALTA2 = "(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SID = ALTA2)

(SERVER = DEDICATED)

)

)";

if($db1=OCILogon("b27", "b27", $ALTA2)) {

$stmt = oci_parse($db1,"

SELECT *

FROM USERI

WHERE ID =(SELECT MAX(ID)

FROM USERI)

");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

$temp = $results['ID'][0] + 1;

$tempstr = "INSERT INTO USERI VALUES({$temp},{$_REQUEST['pasport']},'{$_REQUEST['fio']}','{$_REQUEST['registration']}','{$_REQUEST['telephone']}')";

if($stmt = oci_parse($db1,$tempstr)){

oci_execute($stmt);

echo "</br><H1>Запись добавлена успешна</H1>";

echo "<br>({$temp},{$_REQUEST['pasport']},'{$_REQUEST['fio']}','{$_REQUEST['registration']}','{$_REQUEST['telephone']}')";

echo "</br>Для продолжения нажимите кнопку далее";

?>

<form action="add_dogovor.php" method = get>

</br><input type=hidden name=IdUser value=<?echo $temp;?>

</br><input type=submit name="next" value="Далее">

</form>

<?

OCILogoff($db1);

}else{

echo "</br><H1>Ошибка добавления</H1>";

OCILogoff($db1);

}

}

}

/*------------------------------------------------------------------------------------------------------*/

?>

</TD>

</TR>

<TD height=39 align="center"><a href="http://winer/add_user.php">Добавление клиента</a></TD>

<TR >

<TD align="center" ><a href="http://winer/spisok_userov.php">Список клиентов</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/add_schet.php?nomer_d=npi001">Пополнение баланса</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/alter_user.php"> Изменение данных</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/any_information.php"> Некоторая информация</a></br></TD>

</TR>

<TR >

<TD align="center" valign="top" ><a href="http://winer/summ_port.php">Активность портов</a></TD>

</TR>

</TABLE>

</BODY>

</HTML>

Файл “alter_user.php”

<HTML>

<HEAD>

<TITLE>Сайт статистики::Редактирование</TITLE>

</HEAD>

<BODY>

<TABLE BORDER="1" align="center">

<TR >

<TD ><img src="logotip.jpg"></TD>

<TD align="center">Редактирование</TD>

</TR>

<TR >

<TD align="center"><a href="http://winer/statistic.php"> Статистика оплат</a></TD>

<TD rowspan="7" align="justify" valign=top>

<?php

/*------------------------------------------------------------------------------------------------------*/

$ALTA2 = "(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SID = ALTA2)

(SERVER = DEDICATED)

)

)";

?>

<form action="alter_user.php" method = get>

<?

if($db1=OCILogon("b27", "b27", $ALTA2)) {

$stmt = oci_parse($db1,"

SELECT NOMER

FROM DOGOVOR

ORDER BY NOMER");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

echo "<select name=dogovor size=1>";

for($i = 0; $i < $nrows; $i++){

echo "n</br><option value={$results['NOMER'][$i]}>{$results['NOMER'][$i]}</option>";

}

echo "</select>";

OCILogoff($db1);

}else{

echo "</br>Не удалось соедениться с базой данных";

}

?>

<input type=submit name=Next value=Далее>

</form>

<?

if(isset($_REQUEST['Next'])){

if($db1=OCILogon("b27", "b27", $ALTA2)) {

$stmt = oci_parse($db1,"

SELECT *

FROM USERI,DOGOVOR

WHERE USERI.ID = DOGOVOR.ID_U AND DOGOVOR.NOMER = '{$_REQUEST['dogovor']}'

");

oci_execute($stmt);

oci_fetch_all($stmt, $results);

?>

<form action="alter_user.php" method = post>

<table>

<tr><td>Паспорт: </td><td><input type=text name="pasport" value=<?echo $results['PASPORT'][0]?>></td></tr>

<tr><td>Ф. И.О.: </td><td><input type=text name="fio" value="<?echo $results['FIO'][0]?>"></td></tr>

<tr><td>Место Регистрации в г. Новочеркасске:</td><td><input type=text name="registration" value="<?echo $results['REGISTRATION'][0]?>"></td></tr>

<tr><td>Номер Телефона</td><td><input type=text name="telephone" value="<?echo $results['TELEPHONE'][0]?>"></td></tr>

</table>

<input type=hidden name=nomer value=<?echo $_REQUEST['dogovor']?>>

<input type=hidden name=id_u value=<?echo $results['ID_U'][0]?>>

<input type=submit name=alter value="Изменить"></br>

</form>

<?

OCILogoff($db1);

}else{

echo "</br><H1>Ошибка добавления</H1>";

}

}

if(isset($_REQUEST['alter'])){

if($db1=OCILogon("b27", "b27", $ALTA2)) {

$tempstr = "UPDATE USERI

SET FIO = '{$_REQUEST['fio']}', PASPORT = {$_REQUEST['pasport']}, REGISTRATION = '{$_REQUEST['registration']}', TELEPHONE = '{$_REQUEST['telephone']}'

WHERE USERI.ID = '{$_REQUEST['id_u']}'

";

echo $tempstr;

$stmt = oci_parse($db1,$tempstr);

oci_execute($stmt);

oci_commit($db1);

OCILogoff($db1);

}else{

echo "</br><H1>Ошибка подключения</H1>";

}

}

/*------------------------------------------------------------------------------------------------------*/

?>

</TD>

</TR>

<TD height=39 align="center"><a href="http://winer/add_user.php">Добавление клиента</a></TD>

<TR >

<TD align="center" ><a href="http://winer/spisok_userov.php">Список клиентов</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/add_schet.php?nomer_d=npi001">Пополнение баланса</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/alter_user.php"> Изменение данных</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/any_information.php"> Некоторая информация</a></br></TD>

</TR>

<TR >

<TD align="center" valign="top" ><a href="http://winer/summ_port.php">Активность портов</a></TD>

</TR>

</TABLE>

</BODY>

</HTML>

Файл “any_information.php”

<HTML>

<HEAD>

<TITLE>Сайт статистики::Различная информация</TITLE>

</HEAD>

<BODY>

<TABLE BORDER="1" align="center">

<TR >

<TD ><img src="logotip.jpg"></TD>

<TDalign="center">Различная информация</TD>

</TR>

<TR >

<TD align="center"><a href="http://winer/statistic.php"> Статистика оплат</a></TD>

<TD rowspan="7" align="justify" valign=top>

<h2>Различная информация:</h2>

<?php

/*------------------------------------------------------------------------------------------------------*/

$ALTA2 = "(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SID = ALTA2)

(SERVER = DEDICATED)

)

)

";

if($db1=OCILogon("b27", "b27", $ALTA2)) {

echo "n<h3>Сколько и какие услуги использованы:</h3>";

$stmt = oci_parse($db1,"

SELECT USLUGI.NAME1, COUNT(USLUGI.SHIFR) AS "NUMBER"

FROM USLUGA_DOGOVOR, USLUGI

WHERE USLUGI.SHIFR = USLUGA_DOGOVOR.SHIFR

GROUP BY USLUGI.NAME1

ORDER BY USLUGI.NAME1");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

echo "<table border = 1>n";

echo "<tr> <td>Имя услуги </td> <td>COUNT</td></tr>";

for($i = 0; $i < $nrows; $i++){

echo "<tr><td width=100>{$results['NAME1'][$i]}</td><td>{$results['NUMBER'][$i]}</td></tr>n";

}

echo "</table>";

echo "n<h3>Какие услуги не использованы</h3>";

$stmt = oci_parse($db1,"

SELECT SHIFR, NAME1

FROM USLUGI d1

WHERE NOT EXISTS (

SELECT USLUGI.SHIFR

FROM USLUGA_DOGOVOR, USLUGI

WHERE d1.SHIFR = USLUGA_DOGOVOR.SHIFR

)");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

echo "<table border = 1>n";

echo "<tr> <td>Код услуги </td> <td>Имя услуги</td></tr>";

for($i = 0; $i < $nrows; $i++){

echo "<tr><td width=100>{$results['SHIFR'][$i]}</td><td>{$results['NAME1'][$i]}</td></tr>n";

}

echo "</table>";

$stmt = oci_parse($db1,"

SELECT SUM(MONEY) AS "Profit"

FROM DEBIT

WHERE TO_CHAR(TIME1, 'mm.yyyy') = TO_CHAR(sysdate, 'mm.yyyy')");

oci_execute($stmt);

oci_fetch_all($stmt, $results);

echo "<h3>Прибыль в текущем месяце состовляет {$results['Profit'][0]} руб.</h3>";

$stmt = oci_parse($db1,"SELECT TIME1, WITH_ADRESS, WITH_PORT, BAIT, ip.nomer FROM IP WHERE BAIT = (SELECT MAX(BAIT) FROM IP WHERE IP.WITH_INTERFASE = 5)");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

echo "<h3>Максимальное количество байт принято</h3>";

echo $results['TIME1'][0] . " от " . $results['WITH_ADRESS'][0] . " с порта " . $results['WITH_PORT'][0] . " обьемом в " . $results['BAIT'][0];

OCILogoff($db1);

}else {

$err = OCIError();

echo "Oracle Connect Error " . $err['message'];

}

?>

</TD>

</TR>

<TD height=39 align="center"><a href="http://winer/add_user.php">Добавление клиента</a></TD>

<TR >

<TD align="center" ><a href="http://winer/spisok_userov.php">Список клиентов</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/add_schet.php?nomer_d=npi001">Пополнение баланса</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/alter_user.php"> Изменение данных</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/any_information.php"> Некоторая информация</a></br></TD>

</TR>

<TR >

<TD align="center" valign="top" ><a href="http://winer/summ_port.php">Активность портов</a></TD>

</TR>

</TABLE>

</BODY>

</HTML>

Файл “index.html”

<HTML>

<HEAD>

<TITLE>Сайт статистики::Главная</TITLE>

</HEAD>

<BODY>

<TABLE BORDER="1" align="center">

<TR >

<TD ><img src="logotip.jpg"></TD>

<TD align="center">ГЛАВНАЯ</TD>

</TR>

<TR >

<TD align="center"><a href="http://winer/statistic.php"> Статистика оплат</a></TD>

<TD rowspan="7" align="justify">

<center><h2>Это главная страница сайта статистики.</h2></center>

</TD>

</TR>

<TD height=39 align="center"><a href="http://winer/add_user.php">Добавление клиента</a></TD>

<TR >

<TD align="center" ><a href="http://winer/spisok_userov.php">Список клиентов</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/add_schet.php?nomer_d=npi001">Пополнение баланса</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/alter_user.php"> Изменение данных</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/any_information.php"> Некоторая информация</a></br></TD>

</TR>

<TR >

<TD align="center" valign="top" ><a href="http://winer/summ_port.php">Активность портов</a></TD>

</TR>

</TABLE>

</BODY>

</HTML>

Файл “spisok_userov.php”

<HTML>

<HEAD>

<TITLE>Сайт статистики::Заключение договора</TITLE>

</HEAD>

<BODY>

<TABLE BORDER="1" align="center">

<TR >

<TD ><img src="logotip.jpg"></TD>

<TDalign="center">Заключение договора</TD>

</TR>

<TR >

<TD align="center"><a href="http://winer/statistic.php"> Статистика оплат</a></TD>

<TD rowspan="7" align="justify" valign=top>

<?php

$numb = 5;

$ALTA2 = "(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SID = ALTA2)

(SERVER = DEDICATED)

)

)

";

if($db1=OCILogon("b27", "b27", $ALTA2)) {

if(!$stmt = oci_parse($db1, "SELECT d1.NOMER, d1.money, d4.name1, d1.DATE2, d1.DATE3, d2.fio, d2.pasport, d2.registration, d2.telephone

FROM DOGOVOR d1, USERI d2 , USLUGA_DOGOVOR d3, USLUGI d4

WHERE (d1.id_u = d2.id) AND d3.nomer = d1.nomer AND d4.shifr = d3.shifr AND d4.type_us = 'tarif'

"))echo "</br>eror";

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

echo "<table border=1 >n<tr>n";

echo "<td ><b>#договора</b></td><td >Баланс</td><td width=200>Тариф</td><td>Дата заключения</td><td>Дата истечения</td><td>Ф.И.О.</td><td>Паспорт</td><td>регистрация</td><td>телефон</td></tr>";

for ($i = 0; $i < $nrows; $i++ ) {

echo "<tr>n";

echo "<td>" . $results["NOMER"][$i] . "</td>";

echo "<td>" . $results["MONEY"][$i]. "</td>";

echo "<td>" . $results["NAME1"][$i]. "</td>";

echo "<td>" . $results["DATE2"][$i]. "</td>";

echo "<td>" . $results["DATE3"][$i]. "</td>";

echo "<td>" . $results["FIO"][$i]. "</td>";

echo "<td>" . $results["PASPORT"][$i]. "</td>";

echo "<td>" . $results["REGISTRATION"][$i]. "</td>";

echo "<td>" . $results["TELEPHONE"][$i]. "</td>";

echo "</tr>n";

}

echo "<tr><td colspan='9'> Number of Users: $nrows</td></tr></table>";

echo "<br><em>If you see data, then it works!</em><br></center>n";

OCILogoff($db1);

}else {

$err = OCIError();

echo "Oracle Connect Error " . $err['message'];

}

?>

</form>

</TD>

</TR>

<TD height=39 align="center"><a href="http://winer/add_user.php">Добавление клиента</a></TD>

<TR >

<TD align="center" ><a href="http://winer/spisok_userov.php">Список клиентов</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/add_schet.php?nomer_d=npi001">Пополнение баланса</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/alter_user.php"> Изменение данных</a></br></TD>

</TR>

</TR>

<TR>

<TD align="center" ><a href="http://winer/any_information.php"> Некоторая информация</a></br></TD>

</TR>

<TR >

<TD align="center" valign="top" ><a href="http://winer/summ_port.php">Активность портов</a></TD>

</TR>

</TABLE>

</BODY>

</HTML>

Файл “statistic.php”

<HTML>

<HEAD>

<TITLE>Сайт статистики::Заключение договора</TITLE>

</HEAD>

<BODY>

<TABLE BORDER="1" align="center">

<TR >

<TD ><img src="logotip.jpg"></TD>

<TD align="center">Статистика</TD>

</TR>

<TR >

<TD align="center"><a href="http://winer/statistic.php"> Статистика оплат</a></TD>

<TD rowspan="7" align="justify" valign=top>

<?php

$ALTA2 = "(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SID = ALTA2)

(SERVER = DEDICATED)

)

)";

if(!isset($_REQUEST['do'])) {/*если вход выполнен не из за нажатия кнопки*/

if($db1=OCILogon("b27", "b27", $ALTA2)){

$stmt = oci_parse($db1,"

SELECT *

FROM DOGOVOR

ORDER BY NOMER");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

echo "n<form action="statistic.php" method = post>n";

echo "<select name=NOMER size=1>n";

for($i = 0; $i < $nrows; $i++){

echo "<option value={$results['NOMER'][$i]}>{$results['NOMER'][$i]}</option>";

}

echo "</select>";

echo "</br><input type=submit name=do value=Далее>";

echo "</form>";

OCILogoff($db1);

}else echo "</br> Нет доступа к базе данных";

}else{

if($db1=OCILogon("b27", "b27", $ALTA2)){

$stmt = oci_parse($db1,"

SELECT *

FROM DOGOVOR INNER JOIN USERI ON DOGOVOR.ID_U = USERI.ID

WHERE DOGOVOR.NOMER = '{$_REQUEST['NOMER']}'

");

oci_execute($stmt);

oci_fetch_all($stmt, $results);

echo "</br>" . $results['NOMER'][0] . " " . $results['FIO'][0];

OCILogoff($db1);

}else{echo "</br> Нет доступа к базе данных при просмотре информации о абоненте";}

if($db1=OCILogon("b27", "b27", $ALTA2)){

$stmt = oci_parse($db1,"

SELECT *

FROM OPLATA

WHERE ID_D = '{$_REQUEST['NOMER']}'

");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

echo "<div width=60% style="position:;">";

echo "<div width=% style="position:;background-color:;">";

echo "<table border=1 width='100%'>n";

echo "<tr><td colspan=3><h3>Приход</h3></td></tr>";

echo "<tr><td width=100>Дата</td><td>Номер карты</td><td width=60>Сумма</td></tr>";

for ($i = 0; $i < $nrows; $i++ ) {

echo "<tr><td>" . $results['DATA_VREMYA'][$i] ."</td><td>" . $results['ID_KARTI'][$i] ."</td><td>" . $results['SUMMA'][$i] ."</td></tr>";

}

echo "</table></div style="position:relative;">";

$stmt = oci_parse($db1,"

SELECT *

FROM DEBIT

WHERE ID_D = '{$_REQUEST['NOMER']}'

");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

echo "<div style="position:;background-color:;">";

echo "<table border=1 width='100%'>";

echo "<tr><td colspan=3><h3>Расход</h3></td></tr>";

echo "<tr><td width=100>Дата</td><td>Описание</td><td width=60>Сумма</td></tr>";

for ($i = 0; $i < $nrows; $i++ ) {

echo "<tr><td>" . $results['TIME1'][$i] ."</td><td>" . $results['WHY'][$i] ."</td><td>" . $results['MONEY'][$i] ."</td></tr>";

}

echo "</table></div>";

echo "</div>";

OCILogoff($db1);

if($db1=OCILogon("b27", "b27", $ALTA2)){

echo "<div style=":;">";

$stmt = oci_parse($db1,"

SELECT *

FROM DOGOVOR

ORDER BY NOMER");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

echo "n<form action="statistic.php" method = post>n";

echo "<select name=NOMER size=1>n";

for($i = 0; $i < $nrows; $i++){

echo "<option value={$results['NOMER'][$i]}>{$results['NOMER'][$i]}</option>";

}

echo "</select>";

echo "</br><input type=submit name=do value=Далее>";

echo "</form>";

OCILogoff($db1);

echo" </div>";

}elseecho "</br> Нет доступа к базе данных";

}else{echo "</br> Нет доступа к базе данных при чтении оплаты";}

}

?>

</TD>

</TR>

<TD height=39 align="center"><a href="http://winer/add_user.php">Добавление клиента</a></TD>

<TR >

<TD align="center" ><a href="http://winer/spisok_userov.php">Список клиентов</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/add_schet.php?nomer_d=npi001">Пополнение баланса</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/alter_user.php"> Изменение данных</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/any_information.php"> Некоторая информация</a></br></TD>

</TR>

<TR >

<TD align="center" valign="top" ><a href="http://winer/summ_port.php">Активность портов</a></TD>

</TR>

</TABLE>

</BODY>

</HTML>

Файл “sum_port.php”

<HTML>

<HEAD>

<TITLE>Сайт статистики::Заключение договора</TITLE>

</HEAD>

<BODY>

<TABLE BORDER="1" align="center">

<TR >

<TD ><img src="logotip.jpg"></TD>

<TD align="center">Заключение договора</TD>

</TR>

<TR >

<TD align="center"><a href="http://winer/statistic.php"> Статистика оплат</a></TD>

<TD rowspan="7" align="justify" valign=top>

<?php

$ALTA2 = "(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SID = ALTA2)

(SERVER = DEDICATED)

)

)

";

if($db1=OCILogon("b27", "b27", $ALTA2)) {

$stmt = oci_parse($db1,"SELECT WITH_PORT, SUM(BAIT) / (1024*1024)

FROM "IP"

WHERE IP.WITH_INTERFASE = 5

GROUP BY WITH_PORT

ORDER BY 1/SUM(BAIT) ");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

OCILogoff($db1);

}else {

$err = OCIError();

echo "Oracle Connect Error " . $err['message'];

}

echo "<center><h2>Активность портов</h2><br></center>";

?>

<form action="summ_port.php" method=get>

<input type=submit name="DoGo" value=Next>

<?

if(!isset($_REQUEST['DoGo']) & !isset($_REQUEST['DoGoPrevios'])){

if($nrows <100) $limit = $nrows; else $limit = 100;

echo "n</br>Выводяться 1 - {$limit}n";

echo "<table border=1 cellspacing='0' width='50%'>n<tr>n";

echo "<td><b>Номер порта</b><td>Обьем полученной информации</td></tr>";

for ($i = 0; $i < $limit; $i++ ) {

echo "<tr>n";

echo "<td>" . $results["WITH_PORT"][$i] . "</td>";

echo "<td>" . $results["SUM(BAIT)/(1024*1024)"][$i] . "</td>";

echo "</tr>n";

}

echo "<tr><td colspan='2'> <center>Total $nrows ports</center></td></tr></table>";

?>

<form action="summ_port.php" method=get>

<input type=hidden name="index" value ="<?echo $limit?>">

<input type=submit name="DoGo" value=Next>

</form>

<?

}if(isset($_REQUEST['DoGo']) || isset($_REQUEST['DoGoPrevios'])){

?>

<input type=submit name="DoGoPrevios" value=Previos>

<?

$flag_nrowsMax = false;

$flag_nrowsMin = false;

$flag_Next = false;

$flag_Previos = false;

if(isset($_REQUEST['DoGo'])){

$flag_Next = true;

if(($_REQUEST['index'] + 100) > $nrows) $flag_nrowsMax = true;

}

if(isset($_REQUEST['DoGoPrevios'])){

$flag_Previos = true;

if(($_REQUEST['index'] - 100) < 0 )$flag_nrowsMin = true;

}

if($flag_Previos & $flag_nrowsMin){

$limit = 0;

} else

if ($flag_Previos){$limit = $_REQUEST['index'] - 100;}

if($flag_Next & $flag_nrowsMax){

$limit = $nrows;

} else

if($flag_Next) $limit = $_REQUEST['index'] + 100;

if($_REQUEST['index'] < $limit){

$Start = $_REQUEST['index']; $End = $limit;

}else{

$End = $_REQUEST['index']; $Start = $limit;

}

echo "n</br>Выводяться {$Start} - {$End}n";

echo "<table border=1 cellspacing='0' width='50%'>n<tr>n";

echo "<td><b>Номер порта</b><td>Обьем полученной информации</td></tr>";

for ($i = $Start; $i < $End; $i++ ) {

echo "<tr>n";

echo "<td>" . $results["WITH_PORT"][$i] . "</td>";

echo "<td>" . $results["SUM(BAIT)/(1024*1024)"][$i] . "</td>";

echo "</tr>n";

}

echo "<tr><td colspan='2'> <center>Total $nrows ports</center></td></tr></table>";

?>

<form action="summ_port.php" method=get>

<input type=hidden name="index" value ="<?if($flag_Next)echo $End; else echo $Start;?>">

<input type=submit name="DoGo" value=Next>

<input type=submit name="DoGoPrevios" value=Previos>

</form>

<?

}

?>

</form>

</TD>

</TR>

<TD height=39 align="center"><a href="http://winer/add_user.php">Добавление клиента</a></TD>

<TR >

<TD align="center" ><a href="http://winer/spisok_userov.php">Список клиентов</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/add_schet.php?nomer_d=npi001">Пополнение баланса</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/alter_user.php"> Изменение данных</a></br></TD>

</TR>

<TR>

<TD align="center" ><a href="http://winer/any_information.php"> Некоторая информация</a></br></TD>

</TR>

<TR >

<TD align="center" valign="top" ><a href="http://winer/summ_port.php">Активность портов</a></TD>

</TR>

</TABLE>

</BODY>

</HTML>