SELECT
BIN,
SUM(
CASE WHEN collector = "Person A" THEN quantity ELSE 0 END
) AS "Person A",
SUM(
CASE WHEN collector = "Person B" THEN quantity ELSE 0 END
) AS "Person B",
SUM(
CASE WHEN collector = "Person C" THEN quantity ELSE 0 END
) AS "Person C"
FROM
bin_quantity
GROUP BY
BIN
SET @pivot_sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN collector = "', `collector`, '" THEN quantity ELSE 0 end) AS "', `collector`, '"'
)
)
INTO @pivot_sql
FROM
bin_quantity;
SET @pivot_sql = CONCAT('SELECT bin, ', @pivot_sql, ' FROM bin_quantity GROUP BY bin');
PREPARE stmt FROM @pivot_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.7.26-0ubuntu0.16.04.1-log - (Ubuntu)
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)SET @pivot_sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN collector = "', `collector`, '" THEN quantity ELSE 0 end) AS "', `collector`, '"'
)
)
INTO @pivot_sql
FROM
bin_quantity;
SET @pivot_sql = CONCAT('SELECT bin, ', @pivot_sql, ' FROM bin_quantity GROUP BY bin');
PREPARE stmt FROM @pivot_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;SELECT bin, SUM(CASE WHEN collector = "Person A" THEN quantity ELSE 0 end) AS "Person A",
SUM(CASE WHEN collector = "Person B" THEN quantity ELSE 0 end) AS "Person B",
SUM(CASE WHEN collector = "Person C" THEN quantity ELSE 0 end) AS "Person C"
FROM bin_quantity GROUP BY binSET @pivot_sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN collector = "', `collector`, '" THEN quantity ELSE 0 end) AS "', `collector`, '"'
)
)
INTO @pivot_sql
FROM
bin_quantity;
SELECT @pivot_sql;
SELECT
test.title,
COUNT(CASE WHEN MONTH(`date_close`) = 1 THEN `id_test` END) AS Январь,
COUNT(CASE WHEN MONTH(`date_close`) = 2 THEN `id_test` END) AS Февраль,
COUNT(CASE WHEN MONTH(`date_close`) = 3 THEN `id_test` END) AS Март,
COUNT(CASE WHEN MONTH(`date_close`) = 4 THEN `id_test` END) AS Апрель,
COUNT(CASE WHEN MONTH(`date_close`) = 5 THEN `id_test` END) AS Май,
COUNT(CASE WHEN MONTH(`date_close`) = 6 THEN `id_test` END) AS Июнь,
COUNT(CASE WHEN MONTH(`date_close`) = 7 THEN `id_test` END) AS Июль,
COUNT(CASE WHEN MONTH(`date_close`) = 8 THEN `id_test` END) AS Август,
COUNT(CASE WHEN MONTH(`date_close`) = 9 THEN `id_test` END) AS Сентябрь,
COUNT(CASE WHEN MONTH(`date_close`) = 10 THEN `id_test` END) AS Октябрь,
COUNT(CASE WHEN MONTH(`date_close`) = 11 THEN `id_test` END) AS Ноябрь,
COUNT(CASE WHEN MONTH(`date_close`) = 12 THEN `id_test` END) AS Декабрь
FROM
test_selected
LEFT JOIN test ON test_selected.id_test=test.id
WHERE date_close BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY
test.title
SerialClass::SerialClass(QString analizator)
{
QSettings *settings = new QSettings("conf.ini",QSettings::IniFormat);
portName=settings->value(analizator+"/port").toString();
baudRate=settings->value(analizator+"/baudrate").toInt();
dataBits=settings->value(analizator+"/dataBits").toInt();
parity=settings->value(analizator+"/parity").toInt();
stopBits=settings->value(analizator+"/stopBits").toInt();
flowControl=settings->value(analizator+"/flowControl").toInt();
}
void SerialClass::Start()
{
serialPort=new QSerialPort();
serialPort->setPortName(portName);
serialPort->setBaudRate(baudRate);
serialPort->setDataBits(QSerialPort::DataBits(dataBits));
serialPort->setParity(QSerialPort::Parity(parity));
serialPort->setStopBits(QSerialPort::StopBits(stopBits));
serialPort->setFlowControl(QSerialPort::FlowControl(flowControl));
serialPort->open(QIODevice::ReadWrite);
}
void SerialClass::Stop()
{
serialPort->close();
}
void SerialClass::ReadData()
{
const auto data=serialPort->readAll();
qDebug()<<data;
emit readyRead(data);
}
void SerialClass::RecieveMsg(QByteArray data)
{
serialPort->write(data);
}
bool SerialClass::StatusConnection()
{
if(serialPort->isOpen()){
return true;
}
return false;
}MainWindow::MainWindow(QWidget *parent)
: QMainWindow(parent)
, ui(new Ui::MainWindow)
{
ui->setupUi(this);
mysql=new MysqlClass();
mysql->Start();
TimerMysql=new QTimer(this);
connect(TimerMysql,SIGNAL(timeout()),this,SLOT(TimerSlot()));
TimerMysql->start(10000);
SerialClass *serial;
serial=new SerialClass("Device");
connect(serial, &SerialClass::readyRead, this, &MainWindow::readData);
serial->Start();
// serial->RecieveMsg("dfsdsfsdfdsfsdf");
}
MainWindow::~MainWindow()
{
delete ui;
}
void MainWindow::TimerSlot()
{
if(mysql->StatusConnectMysql()!=true)
{
QMessageBox::warning(this,"Error to connect Mysql Server","Программа потеряла соединение с Базой данных Mysql");
time++;
}
}
void MainWindow::readData(const QByteArray &data)
{
qDebug()<<data;
}connect(serialPort,SIGNAL(readyRead()),this,SLOT(ReadData());ldd /home/admin/lis_uploader/libqsqlmysql.so
/home/admin/lis_uploader/libqsqlmysql.so: /lib64/libQt5Sql.so.5: version `Qt_5_PRIVATE_API' not found (required by /home/admin/lis_uploader/libqsqlmysql.so)
/home/admin/lis_uploader/libqsqlmysql.so: /lib64/libQt5Core.so.5: version `Qt_5_PRIVATE_API' not found (required by /home/admin/lis_uploader/libqsqlmysql.so)
linux-vdso.so.1 (0x00007fffa7fd8000)
libQt5Sql.so.5 => /lib64/libQt5Sql.so.5 (0x00007fbe66c8b000)
libQt5Core.so.5 => /lib64/libQt5Core.so.5 (0x00007fbe66543000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fbe66323000)
libmariadb.so.3 => /lib64/libmariadb.so.3 (0x00007fbe660d0000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fbe65d3f000)
libm.so.6 => /lib64/libm.so.6 (0x00007fbe659be000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fbe657a6000)
libc.so.6 => /lib64/libc.so.6 (0x00007fbe653e5000)
libsystemd.so.0 => /lib64/libsystemd.so.0 (0x00007fbe6512d000)
libz.so.1 => /lib64/libz.so.1 (0x00007fbe64f12000)
libdouble-conversion.so.3 => /lib64/libdouble-conversion.so.3 (0x00007fbe64d04000)
libicui18n.so.60 => /lib64/libicui18n.so.60 (0x00007fbe64863000)
libicuuc.so.60 => /lib64/libicuuc.so.60 (0x00007fbe644ac000)
libicudata.so.60 => /lib64/libicudata.so.60 (0x00007fbe62902000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fbe626fe000)
libpcre2-16.so.0 => /lib64/libpcre2-16.so.0 (0x00007fbe6247a000)
libzstd.so.1 => /lib64/libzstd.so.1 (0x00007fbe621a8000)
libgthread-2.0.so.0 => /lib64/libgthread-2.0.so.0 (0x00007fbe61fa6000)
libglib-2.0.so.0 => /lib64/libglib-2.0.so.0 (0x00007fbe61c7c000)
/lib64/ld-linux-x86-64.so.2 (0x00007fbe670e8000)
libssl.so.1.1 => /lib64/libssl.so.1.1 (0x00007fbe619eb000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007fbe61509000)
librt.so.1 => /lib64/librt.so.1 (0x00007fbe61300000)
liblzma.so.5 => /lib64/liblzma.so.5 (0x00007fbe610da000)
liblz4.so.1 => /lib64/liblz4.so.1 (0x00007fbe60ebb000)
libgcrypt.so.20 => /lib64/libgcrypt.so.20 (0x00007fbe60b9c000)
libpcre.so.1 => /lib64/libpcre.so.1 (0x00007fbe6092b000)
libgpg-error.so.0 => /lib64/libgpg-error.so.0 (0x00007fbe6070a000)