Модуль взаимодействия с автоматизированной системой расчётов компании ОАО "Мобильные ТелеСистемы"

53. Моргун, И. UML2 и ER диаграммы [Электронный ресурс] / И. Моргун. - Режим доступа: http://proft.me/2013/05/27/uml-2-tipy-diagramm/

54. Ларин, А. UML - это просто. Диаграммы последовательностей. [Электронный ресурс] / А. Ларин. - Режим доступа: http://larin.in/archives/32

55. Червенчук, И.В. Моделирование информационных систем с помощью UML: учеб. / И.В. Червенчук - Омск, 2006. - 65 с.

56. Иванов Д. Моделирование на UML. Учебно-методическое пособие: учеб. / Д. Иванов, Ф. Новиков. - СПб.: СПбГУ ИТМО, 2010. - 200 с.

57. Фаулер, М. UML. Основы: учеб. / М. Фаулер., К. Скотт - СПБ: Символ-Плюс, 2002. - 192 с.

58. Маклаков, С.В. BPWIN и ERWIN: CASE-средства для разработки информационных систем / С. В. Маклаков. - М.: Диалог-МИФИ, 2000.- 295 с.

59. Маклаков, С.В. Создание информационных систем с AllFusion Modeling Suite/ С.В. Маклаков. - М.: ДИАЛОГ - МИФИ, 2002. - 224 с.

60. Черемных, С.В. Структурный анализ систем: IDEF-технологии / С.В. Черемных, И.О. Семенов, В.С. Ручкин. - М.: Финансы и статистика, 2003.

61. Маклаков, С.В. BPwin и Erwin. CASE-средства для разработки информационных систем [Электронный ресурс] / С.В. Маклаков. - Режим доступа: http://coollib.net/b/147153/read

Приложение А

Файл mts_programm.pro

QT += core gui sql

greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

TARGET = mts_programm

TEMPLATE = app

SOURCES += main.cpp\login.cpp \mainwork.cpp

HEADERS += login.h \mainwork.h

FORMS += login.ui \mainwork.ui

ПРИЛОЖЕНИЕ Б

Файл login.h

#ifndef LOGIN_H

#define LOGIN_H

#include <QMainWindow>

#include <QtSql>

#include <QDebug>

#include <QFileInfo>

#include "mainwork.h"

namespace Ui {

class login;

}

class login : public QMainWindow

{

Q_OBJECT

public:

QSqlDatabase qt_mts;

void connClose()

{

qt_mts.close();

qt_mts.removeDatabase(QSqlDatabase::defaultConnection);

}

bool connOpen()

{

qt_mts=QSqlDatabase::addDatabase("QMYSQL");

qt_mts.setHostName("localhost");

qt_mts.setDatabaseName("qt_mts");

qt_mts.setUserName("qt_mts");

qt_mts.setPassword("qt_mts");

if(!qt_mts.open()){

qDebug()<<("Fail to connect");

return false;

}

else{

qDebug()<<("database connected");

return true;

}

}

public:

explicit login(QWidget *parent = 0);

~login();

private slots:

void on_pushButton_ok_clicked();

private:

Ui::login *ui;

};

#endif // LOGIN_H

Приложение В

Файл mainwork.h

#ifndef MAINWORK_H

#define MAINWORK_H

#include <QDialog>

#include "login.h"

namespace Ui {

class Mainwork;

}

class Mainwork : public QDialog

{

Q_OBJECT

public:

explicit Mainwork(QWidget *parent = 0);

~Mainwork();

private slots:

void on_zd_button_ok_clicked();

void on_pa_button_ok_clicked();

void on_pl_button_ok_clicked();

void on_plp_button_ok_clicked();

void on_bl_button_ok_clicked();

void on_dp_button_ok_clicked();

void on_sp_button_ok_clicked();

void on_spp_button_ok_clicked();

void on_osv_button_ok_clicked();

void on_rp_button_ok_clicked();

void on_rm_button_ok_clicked();

void on_vt_button_ok_clicked();

private:

Ui::Mainwork *ui;

};

#endif // MAINWORK_H

Приложение Г

Файл login.cpp

#include "login.h"

#include "ui_login.h"

login::login(QWidget *parent) :

QMainWindow(parent),

ui(new Ui::login)

{

ui->setupUi(this);

QPixmap img("C:/Users/BENJi/Desktop/mts_logo.png");

ui->label_image->setPixmap(img);

if(!connOpen())

ui->label_status->setText("Ошибка подключения");

else

ui->label_status->setText("Соединение с БД установлено");

}

login::~login()

{

delete ui;

}

void login::on_pushButton_ok_clicked()

{

QString login,pass;

login=ui->lineEdit_login->text();

pass=ui->lineEdit_pass->text();

if(!connOpen()){

qDebug()<<"Failed to open the db";

return;

}

connOpen();

QSqlQuery qry;

qry.prepare("select * from managers where login='"+login+"' and pass='"+pass+"'");

if(qry.exec())

{

int count=0;

while(qry.next())

{

count++;

}

if(count==1){

ui->label_status->setText("Вход");

connClose();

this->hide();

Mainwork mainwork;

mainwork.setModal(true);

mainwork.exec();

}

if(count>1)

ui->label_status->setText("Дублирование логина/пароля");

if(count<1)

ui->label_status->setText("Неправильный ввод логина/пароля");}}

Приложение Д

Файл main.cpp

#include "login.h"

#include <QApplication>

int main(int argc, char *argv[])

{

QApplication a(argc, argv);

login w;

w.show();

return a.exec();

}

Приложение Е

Файл mainwork.cpp

#include "mainwork.h"

#include "ui_mainwork.h"

Mainwork::Mainwork(QWidget *parent) :

QDialog(parent),

ui(new Ui::Mainwork)

{

ui->setupUi(this);

login conn;

if(!conn.connOpen())

ui->label_second_status->setText("Ошибка подключения");

else

ui->label_second_status->setText("Соединение с БД установлено");

}

Mainwork::~Mainwork()

{

delete ui;

}

void Mainwork::on_zd_button_ok_clicked()

{

login conn;

QString zd_ot,zd_po,zd_sum_p,zd_ocn_p,zd_uslugi_p,zd_polz_type,zd_usl_type;

zd_ot=ui->date_ot_zd->text();

zd_po=ui->date_po_zd->text();

zd_polz_type="";

zd_ocn_p="";

zd_sum_p="";

zd_uslugi_p="";

zd_usl_type="";

if(ui->zd_fiz_type->isChecked())

{ zd_polz_type="and ac.type=2"; }

if(ui->zd_ur_type->isChecked())

{ zd_polz_type="and ac.type=1"; }

if(ui->zd_tv_usl->isChecked())

{ zd_usl_type="and aav.value=7 and aav.agrm_id = a.agrm_id"; }

if(ui->zd_internet_usl->isChecked())

{ zd_usl_type="and aav.value=6 and aav.agrm_id = a.agrm_id"; }

if(ui->zd_ocn->isChecked())

{ zd_ocn_p="a.number,"; }

if(ui->zd_sum->isChecked())

{ zd_sum_p="sum(a.number),"; }

if(ui->zd_uslugi->isChecked())

{ zd_uslugi_p="concat(aav.value,'color') as color,"; }

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare("select '"+zd_uslugi_p+"' '"+zd_ocn_p+"' '"+zd_sum_p+"' ac.name, a.date from agreements a, accounts ac ,agreements_addons_vals aav where a.uid = ac.uid '"+zd_polz_type+"' '"+zd_usl_type+"' and aav.agrm_id = a.agrm_id and a.archive = 0 and date(v.acc_ondate) >='"+zd_ot+"' and date(v.acc_ondate) <='"+zd_po+"' order by date");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

void Mainwork::on_pa_button_ok_clicked()

{

login conn;

QString pa_ot,pa_po,pa_sum_p,pa_ocn_p,pa_uslugi_p,pa_polz_type,pa_usl_type;

pa_ot=ui->date_ot_pa->text();

pa_po=ui->date_po_pa->text();

pa_polz_type="";

pa_ocn_p="";

pa_sum_p="";

pa_uslugi_p="";

pa_usl_type="";

if(ui->pa_fiz_type->isChecked())

{ pa_polz_type="and ac.type=2"; }

if(ui->pa_ur_type->isChecked())

{ pa_polz_type="and ac.type=1"; }

if(ui->pa_tv_usl->isChecked())

{ pa_usl_type="and aav.value=7 and aav.agrm_id = a.agrm_id"; }

if(ui->pa_internet_usl->isChecked())

{ pa_usl_type="and aav.value=6 and aav.agrm_id = a.agrm_id"; }

if(ui->pa_ocn->isChecked())

{ pa_ocn_p="a.number,"; }

if(ui->pa_sum->isChecked())

{ pa_sum_p="sum(a.number),"; }

if(ui->pa_uslugi->isChecked())

{ pa_uslugi_p="concat(aav.value,'color') as color,"; }

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare("select '"+pa_uslugi_p+"' '"+pa_ocn_p+"' '"+pa_sum_p+"' ac.name,acc_ondate from vgroups v, agreements a, accounts ac, agreements_addons_vals aav where a.uid = ac.uid and aav.agrm_id = a.agrm_id and v.agrm_id = a.agrm_id and date(v.acc_ondate) >='"+pa_ot+"' and date(v.acc_ondate) <='"+pa_po+"' '"+pa_polz_type+"' '"+pa_usl_type+"' and date(v.acc_offdate) ='0000-00-00' and ((a.date >= '"+pa_ot+"' - interval 1 month) or (a.date >= '"+pa_po+"' - interval 1 month)) and v.tar_id !=14 and v.tar_id !=145 order by creation_date;");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

void Mainwork::on_pl_button_ok_clicked()

{

login conn;

QString pl_ot,pl_po,pl_sum_p,pl_ocn_p,pl_uslugi_p,pl_polz_type,pl_usl_type,pl_plat;

pl_ot=ui->date_ot_pl->text();

pl_po=ui->date_po_pl->text();

pl_polz_type="";

pl_ocn_p="";

pl_sum_p="";

pl_uslugi_p="";

pl_usl_type="";

pl_plat="";

if(ui->pl_fiz_type->isChecked())

{ pl_polz_type="and ac.type=2"; }

if(ui->pl_ur_type->isChecked())

{ pl_polz_type="and ac.type=1"; }

if(ui->pl_tv_usl->isChecked())

{ pl_usl_type="and aav.value=7 and aav.agrm_id = a.agrm_id"; }

if(ui->pl_internet_usl->isChecked())

{ pl_usl_type="and aav.value=6 and aav.agrm_id = a.agrm_id"; }

if(ui->pl_online->isChecked())

{ pl_plat=" and p.mod_person=105"; }

if(ui->pl_psystem->isChecked())

{ pl_plat=" and p.mod_person in (102,81)"; }

if(ui->pl_all->isChecked())

{ pl_plat=" and p.mod_person !=24 and p.mod_person !=92 "; }

if(ui->pl_ocn->isChecked())

{ pl_ocn_p="a.number,"; }

if(ui->pl_sum->isChecked())

{ pl_sum_p="sum(a.number),"; }

if(ui->pl_uslugi->isChecked())

{ pl_uslugi_p="concat(aav.value,'color') as color,"; }

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare("select '"+pl_uslugi_p+"' '"+pl_ocn_p+"' '"+pl_sum_p+"' ac.name, p.amount,p.comment, p.pay_date from agreements a, accounts ac, payments_history p, managers m, agreements_addons_vals aav where ac.uid = a.uid and p.status_new<>2 and a.agrm_id = p.agrm_id and p.mod_person = m.person_id and date(v.acc_ondate) >='"+pl_ot+"' and date(v.acc_ondate) <='"+pl_po+"' '"+pl_plat+"' '"+pl_polz_type+"' '"+pl_usl_type+"' order by p.pay_date");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

void Mainwork::on_plp_button_ok_clicked()

{

login conn;

QString plp_ot,plp_po,plp_sum_p,plp_ocn_p,plp_uslugi_p,plp_usl_type,plp_dop;

plp_ot=ui->date_ot_plp->text();

plp_po=ui->date_po_plp->text();

plp_ocn_p="";

plp_sum_p="";

plp_uslugi_p="";

plp_usl_type="";

plp_dop="";

if(ui->plp_tv_usl->isChecked())

{

plp_usl_type="and aav.value=7 and aav.agrm_id = a.agrm_id";

}

if(ui->plp_internet_usl->isChecked())

{

plp_usl_type="and aav.value=6 and aav.agrm_id = a.agrm_id";

}

if(ui->plp_sum->isChecked())

{

plp_dop="sum(p.amount),";

}

if(ui->plp_all->isChecked())

{

plp_dop=" ";

}

if(ui->plp_ocn->isChecked())

{

plp_ocn_p="a.number,";

}

if(ui->plp_sum->isChecked())

{

plp_sum_p="sum(a.number),";

}

if(ui->plp_uslugi->isChecked())

{

plp_uslugi_p="concat(aav.value,'color') as color,";

}

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare("SELECT '"+plp_uslugi_p+"' '"+plp_ocn_p+"' '"+plp_sum_p+"' m.login, ac.name, p.amount,'"+plp_dop+"' p.pay_date, p.receipt FROM payments p, accounts ac, agreements a,managers m, agreements_addons_vals aav WHERE p.agrm_id = a.agrm_id and date(v.acc_ondate) >='"+plp_ot+"' and date(v.acc_ondate) <='"+plp_po+"' and a.uid = ac.uid '"+plp_usl_type+"' and m.parent_template = 109 and p.mod_person = m.person_id order by login,pay_date");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

void Mainwork::on_bl_button_ok_clicked()

{

login conn;

QString bl_ot,bl_sum_p,bl_ocn_p,bl_uslugi_p,bl_usl_type,bl_dop_adr,bl_dop_m,bl_dop_pod,bl_polz_type,bl_balance;

bl_ot=ui->date_ot_bl->text();

bl_ocn_p="";

bl_sum_p="";

bl_uslugi_p="";

bl_usl_type="";

bl_dop_adr="";

bl_dop_m="";

bl_dop_pod="";

if(ui->bl_tv_usl->isChecked())

{

bl_usl_type="and aav.value=7 and aav.agrm_id = a.agrm_id";

}

if(ui->bl_internet_usl->isChecked())

{

bl_usl_type="and aav.value=6 and aav.agrm_id = a.agrm_id";

}

if(ui->bl_dop_adr->isChecked())

{

bl_dop_adr=", SUBSTRING_INDEX(accd.address, ',,', -1)";

}

if(ui->bl_dop_nep->isChecked())

{

bl_dop_m="and b.balance !='-140' and b.balance !='-30'";

}

if(ui->bl_dop_pod->isChecked())

{

bl_dop_pod="";

}

if(ui->bl_fiz_type->isChecked())

{

bl_polz_type="and ac.type=2";

}

if(ui->bl_ur_type->isChecked())

{

bl_polz_type="and ac.type=1";

}

if(ui->bl_balance_m->isChecked())

{

bl_balance="and b.balance < 0";

}

if(ui->bl_balance_p->isChecked())

{

bl_balance="and b.balance >0";

}

if(ui->bl_ocn->isChecked())

{

bl_ocn_p="a.number,";

}

if(ui->bl_sum->isChecked())

{

bl_sum_p="sum(a.number),";

}

if(ui->bl_uslugi->isChecked())

{

bl_uslugi_p="concat(aav.value,'color') as color,";

}

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare("select '"+bl_uslugi_p+"' '"+bl_ocn_p+"' '"+bl_sum_p+"' ac.name, round(b.balance,2) , '"+bl_dop_adr+"' from balances b, accounts ac , agreements a , accounts_addr accd,agreements_addons_vals aav where b.agrm_id=a.agrm_id and b.date='"+bl_ot+"' '"+bl_usl_type+"' '"+bl_polz_type+"' and a.uid=ac.uid '"+bl_balance+"' '"+bl_dop_m+"' and accd.uid=ac.uid and accd.type=1 order by number");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

void Mainwork::on_dp_button_ok_clicked()

{

login conn;

QString dp_ot,dp_sum_p,dp_ocn_p,dp_uslugi_p,dp_polz_type,dp_usl_type;

dp_ot=ui->date_ot_zd->text();

dp_polz_type="";

dp_ocn_p="";

dp_sum_p="";

dp_uslugi_p="";

dp_usl_type="";

if(ui->dp_fiz_type->isChecked())

{

dp_polz_type="and ac.type=2";

}

if(ui->dp_ur_type->isChecked())

{

dp_polz_type="and ac.type=1";

}

if(ui->dp_tv_usl->isChecked())

{

dp_usl_type="and aav.value=7 and aav.agrm_id = a.agrm_id";

}

if(ui->dp_internet_usl->isChecked())

{

dp_usl_type="and aav.value=6 and aav.agrm_id = a.agrm_id";

}

if(ui->dp_ocn->isChecked())

{

dp_ocn_p="a.number,";

}

if(ui->dp_sum->isChecked())

{

dp_sum_p="sum(a.number),";

}

if(ui->dp_uslugi->isChecked())

{

dp_uslugi_p="concat(aav.value,'color') as color,";

}

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare(" SELECT '"+dp_uslugi_p+"' '"+dp_ocn_p+"' '"+dp_sum_p+"' c.descr, round(sum(amount),2), sum(r.volume) FROM report'"+dp_ot+"' r, categories c, agreements a, accounts ac, agreements_addons_vals aav and r.agrm_id=a.agrm_id and r.cat_idx=c.cat_idx and r.tar_id=c.tar_id and(r.tar_id=14 or r.tar_id=145) '"+dp_polz_type+"' '"+dp_usl_type+"' group by c.descr");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

void Mainwork::on_sp_button_ok_clicked()

{

login conn;

QString sp_ot,sp_sum_p,sp_ocn_p,sp_uslugi_p,sp_polz_type,sp_usl_type;

sp_ot=ui->date_ot_sp->text();

sp_polz_type="";

sp_ocn_p="";

sp_sum_p="";

sp_uslugi_p="";

sp_usl_type="";

if(ui->sp_fiz_type->isChecked())

{

sp_polz_type="and ac.type=2";

}

if(ui->sp_ur_type->isChecked())

{

sp_polz_type="and ac.type=1";

}

if(ui->sp_tv_usl->isChecked())

{

sp_usl_type="and aav.value=7 and aav.agrm_id = a.agrm_id";

}

if(ui->sp_internet_usl->isChecked())

{

sp_usl_type="and aav.value=6 and aav.agrm_id = a.agrm_id";

}

if(ui->sp_ocn->isChecked())

{

sp_ocn_p="a.number,";

}

if(ui->sp_sum->isChecked())

{

sp_sum_p="sum(a.number),";

}

if(ui->sp_uslugi->isChecked())

{

sp_uslugi_p="concat(aav.value,'color') as color,";

}

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare(" SELECT '"+sp_uslugi_p+"' '"+sp_ocn_p+"' '"+sp_sum_p+"' a.number, ac.name,t.descr, round(sum(r.amount),2) as sum FROM report'"+sp_ot+"' r, tarifs t, agreements a, accounts ac ,agreements_addons_vals aav WHERE t.tar_id=r.tar_id '"+sp_polz_type+"' '"+sp_usl_type+"' and ac.uid = a.uid and r.agrm_id = a.agrm_idgroup by a.number");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

void Mainwork::on_spp_button_ok_clicked()

{

login conn;

QString spp_ot,spp_sum_p,spp_ocn_p,spp_uslugi_p,spp_polz_type,spp_usl_type;

spp_ot=ui->date_ot_spp->text();

spp_polz_type="";

spp_ocn_p="";

spp_sum_p="";

spp_uslugi_p="";

spp_usl_type="";

if(ui->spp_fiz_type->isChecked())

{

spp_polz_type="and ac.type=2";

}

if(ui->spp_ur_type->isChecked())

{

spp_polz_type="and ac.type=1";

}

if(ui->spp_tv_usl->isChecked())

{

spp_usl_type="and aav.value=7 and aav.agrm_id = a.agrm_id";

}

if(ui->spp_internet_usl->isChecked())

{

spp_usl_type="and aav.value=6 and aav.agrm_id = a.agrm_id";

}

if(ui->spp_ocn->isChecked())

{

spp_ocn_p="a.number,";

}

if(ui->spp_sum->isChecked())

{

spp_sum_p="sum(a.number),";

}

if(ui->spp_uslugi->isChecked())

{

spp_uslugi_p="concat(aav.value,'color') as color,";

}

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare("select t.descr, '"+spp_uslugi_p+"' '"+spp_ocn_p+"' '"+spp_sum_p+"' round(sum(r.amount),2) as s from report'"+spp_ot+"' r, agreements a, accounts ac, tarifs t ,agreements_addons_vals aav where r.agrm_id = a.agrm_id and ac.uid = a.uid '"+spp_polz_type+"' '"+spp_usl_type+"' and t.tar_id = r.tar_id group by t.descr order by descr,s");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

void Mainwork::on_osv_button_ok_clicked()

{

login conn;

QString osv_ot,osv_dop_table,osv_polz_type,osv_usl_type;

osv_ot=ui->date_ot_osv->text();

osv_polz_type="";

osv_usl_type="";

osv_dop_table="";

if(ui->osv_fiz_type->isChecked())

{

osv_polz_type="and ac.type=2";

}

if(ui->osv_ur_type->isChecked())

{

osv_polz_type="and ac.type=1";

}

if(ui->osv_tv_usl->isChecked())

{

osv_usl_type="and aav.value=7 and aav.agrm_id = a.agrm_id";

}

if(ui->osv_internet_usl->isChecked())

{

osv_usl_type="and aav.value=6 and aav.agrm_id = a.agrm_id";

}

if(ui->osv_table->isChecked())

{

osv_dop_table="";

}

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare("select ac.name,a.agreements, t.tarifs from agreements a, accounts ac, tarifs t ,agreements_addons_vals aav where r.agrm_id = a.agrm_id and ac.uid = a.uid '"+osv_polz_type+"' '"+osv_usl_type+"' and t.tar_id = r.tar_id group by t.descr order by descr,s");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

void Mainwork::on_rp_button_ok_clicked()

{

login conn;

QString rp_ot,rp_polz_type,rp_usl_type;

rp_ot=ui->date_ot_rp->text();

rp_polz_type="";

rp_usl_type="";

if(ui->rp_fiz_type->isChecked())

{

rp_polz_type="and ac.type=2";

}

if(ui->rp_ur_type->isChecked())

{

rp_polz_type="and ac.type=1";

}

if(ui->rp_tv_usl->isChecked())

{

rp_usl_type="and aav.value=7 and aav.agrm_id = a.agrm_id";

}

if(ui->rp_internet_usl->isChecked())

{

rp_usl_type="and aav.value=6 and aav.agrm_id = a.agrm_id";

}

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare("select t.date as color_date,fio,sum(amount) as sum, t.comment, t.descr from tmp1 t, agreements a, accounts ac ,agreements_addons_vals aav where a.uid=ac.uid and t.agrm_id = a.agrm_id '"+rp_polz_type+"' '"+rp_usl_type+"' group by t.date,fio,comment");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

void Mainwork::on_rm_button_ok_clicked()

{

login conn;

QString rm_ot,rm_po,rp_dop="";

rm_ot=ui->date_ot_rm->text();

rm_po=ui->date_po_rm->text();

rp_dop="";

if(ui->rm_dog->isChecked())

{

rp_dop="and c.cat_idx = us.cat_idx";

}

if(ui->rm_sum->isChecked())

{

rp_dop="and c.tar_id = us.tar_id";

}

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare("SELECT date(timefrom) as color_date, c.descr as opis, sum(mul) as kolvo, (above * sum(mul)) as summa, above FROM usbox_services us, categories c, vgroups v where us.tar_id = 145 and date(timefrom) >='"+rm_ot+"' and date(timefrom) <= '"+rm_po+"' and c.tar_id = us.tar_id and c.cat_idx = us.cat_idx and v.vg_id = us.vg_id and v.tar_id = us.tar_id and c.tar_id = us.tar_id and v.archive = 0 group by color_date, opis order by color_date, opis");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

void Mainwork::on_vt_button_ok_clicked()

{

login conn;

QString vt_sum_p,vt_ocn_p,vt_uslugi_p,vt_polz_type,vt_usl_type;

vt_polz_type="";

vt_ocn_p="";

vt_sum_p="";

vt_uslugi_p="";

vt_usl_type="";

if(ui->vt_fiz_type->isChecked())

{

vt_polz_type="and ac.type=2";

}

if(ui->vt_ur_type->isChecked())

{

vt_polz_type="and ac.type=1";

}

if(ui->vt_tv_usl->isChecked())

{

vt_usl_type="and aav.value=7 and aav.agrm_id = a.agrm_id";

}

if(ui->vt_internet_usl->isChecked())

{

vt_usl_type="and aav.value=6 and aav.agrm_id = a.agrm_id";

}

if(ui->vt_ocn->isChecked())

{

vt_ocn_p="a.number,";

}

if(ui->vt_sum->isChecked())

{

vt_sum_p="sum(a.number),";

}

if(ui->vt_uslugi->isChecked())

{ vt_uslugi_p="concat(aav.value,'color') as color,"; }

QSqlQueryModel * model = new QSqlQueryModel();

conn.connOpen();

QSqlQuery* qry = new QSqlQuery(conn.qt_mts);

qry->prepare("select '"+vt_uslugi_p+"' '"+vt_ocn_p+"' '"+vt_sum_p+"' ac.name, round(a.balance,2) as bal, ac.phone, t.descr from agreements a, accounts ac, vgroups v, tarifs t ,agreements_addons_vals aav where a.agrm_id = aav.agrm_id '"+vt_polz_type+"' '"+vt_usl_type+"' and a.uid = ac.uid and v.agrm_id = a.agrm_id and v.tar_id = t.tar_id and ac.phone rlike '89[0-9]{9}' group by a.agrm_id");

qry->exec();

model->setQuery(*qry);

ui->tableView->setModel(model);

conn.connClose();

}

Размещено на Allbest.ru