Пишу программу для зарплаты, многопользовательская. Самоучка, программирование как хобби, изучаю MySQL. В главном окне выдаётся вся информация о сотрудниках:
При переключении текущего месяца вся информация обновляется таким запросом:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
INSERT INTO accounts (Employee, Month, Year, BeforeAccruals, BeforePenalties, BeforePayments, Accruals, Penalties, Payments)
SELECT e.idx,8,2014,
(SELECT SUM(`Sum`) FROM accruals WHERE Employee = e.idx AND ((Year = 2014 AND Month < 8) OR (Year < 2014))),
(SELECT SUM(`Sum`) FROM penalties WHERE Employee = e.idx AND ((Year = 2014 AND Month < 8) OR (Year < 2014))),
(SELECT SUM(`Sum`) FROM payments WHERE Employee = e.idx AND ((Year = 2014 AND Month < 8) OR (Year < 2014))),
(SELECT SUM(`Sum`) FROM accruals WHERE Employee = e.idx AND (Year = 2014 AND Month = 8)),
(SELECT SUM(`Sum`) FROM penalties WHERE Employee = e.idx AND (Year = 2014 AND Month = 8)),
(SELECT SUM(`Sum`) FROM payments WHERE Employee = e.idx AND (Year = 2014 AND Month = 8))
FROM employees e
WHERE e.idx=45
ON DUPLICATE KEY UPDATE
BeforeAccruals=(SELECT SUM(`Sum`) FROM accruals WHERE Employee = accounts.Employee AND ((Year = 2014 AND Month < 8) OR (Year < 2014))),
BeforePenalties=(SELECT SUM(`Sum`) FROM penalties WHERE Employee = accounts.Employee AND ((Year = 2014 AND Month < 8) OR (Year < 2014))),
BeforePayments=(SELECT SUM(`Sum`) FROM payments WHERE Employee = accounts.Employee AND ((Year = 2014 AND Month < 8) OR (Year < 2014))),
Accruals=(SELECT SUM(`Sum`) FROM accruals WHERE Employee = accounts.Employee AND (Year = 2014 AND Month = 8)),
Penalties=(SELECT SUM(`Sum`) FROM penalties WHERE Employee = accounts.Employee AND (Year = 2014 AND Month = 8)),
Payments=(SELECT SUM(`Sum`) FROM payments WHERE Employee = accounts.Employee AND (Year = 2014 AND Month = 8));
Запрос очень медленный. 15 секунд выполняется. Пока меня основная идея - запускать этот запрос в фоне, после выполнения обновить втихую информацию на экране. Всё-таки без полного обновления всех сотрудников за текущий месяц возможно будут выводится неправильные общие суммы начислений и пр., т.к. параллельно предыдущий месяц может редактироваться другим бухгалтером.
Структура таблиц:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
CREATE TABLE IF NOT EXISTS `accounts` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`Employee` int(11) NOT NULL,
`Month` int(11) NOT NULL,
`Year` int(11) NOT NULL,
`BeforeAccruals` decimal(8,2) DEFAULT NULL,
`BeforePenalties` decimal(8,2) DEFAULT NULL,
`BeforePayments` decimal(8,2) DEFAULT NULL,
`Accruals` decimal(8,2) DEFAULT NULL,
`Penalties` decimal(8,2) DEFAULT NULL,
`Payments` decimal(8,2) DEFAULT NULL,
PRIMARY KEY (`idx`),
UNIQUE KEY `UNIQ_DATES` (`Employee`,`Month`,`Year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10608 ;
CREATE TABLE IF NOT EXISTS `accruals` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`Employee` int(11) NOT NULL,
`Month` int(11) NOT NULL,
`Year` int(11) NOT NULL,
`Type` int(11) NOT NULL,
`Amount` decimal(8,2) NOT NULL,
`Price` decimal(8,2) NOT NULL,
`Sum` decimal(8,2) NOT NULL,
`Company` int(11) DEFAULT NULL,
PRIMARY KEY (`idx`),
KEY `Employee` (`Employee`),
KEY `Month` (`Month`),
KEY `Year` (`Year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3609 ;
CREATE TABLE IF NOT EXISTS `accruals_types` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(100) NOT NULL,
PRIMARY KEY (`idx`),
UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;
CREATE TABLE IF NOT EXISTS `companies` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(100) NOT NULL,
`Price1H_lic` decimal(8,2) NOT NULL,
`Price1H_nonlic` decimal(8,2) NOT NULL,
`Firm` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`idx`),
UNIQUE KEY `Name` (`Name`),
KEY `Firm` (`Firm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=103 ;
CREATE TABLE IF NOT EXISTS `employees` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(100) NOT NULL,
`Firm` int(11) NOT NULL DEFAULT '1',
`Title` int(11) NOT NULL,
`License` tinyint(1) NOT NULL DEFAULT '0',
`YOB` int(11) NOT NULL,
`Children` int(11) NOT NULL DEFAULT '0',
`InitSum` decimal(8,2) NOT NULL DEFAULT '0.00',
`Active` tinyint(1) NOT NULL DEFAULT '1',
`StartMonth` int(11) NOT NULL DEFAULT '1',
`StartYear` int(11) NOT NULL DEFAULT '2013',
`QuitMonth` int(11) NOT NULL DEFAULT '0',
`QuitYear` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`idx`),
UNIQUE KEY `Name` (`Name`),
KEY `Title` (`Title`),
KEY `StartMonth` (`StartMonth`),
KEY `StartYear` (`StartYear`),
KEY `QuitMonth` (`QuitMonth`),
KEY `QuitYear` (`QuitYear`),
KEY `Firm` (`Firm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=703 ;
CREATE TABLE IF NOT EXISTS `firms` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(100) NOT NULL,
PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
CREATE TABLE IF NOT EXISTS `log` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`DateTime` datetime NOT NULL,
`Firm` int(11) NOT NULL,
`User` int(11) NOT NULL,
`Action` varchar(200) NOT NULL,
PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=93 ;
CREATE TABLE IF NOT EXISTS `payments` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`Day` int(11) NOT NULL DEFAULT '1',
`Month` int(11) NOT NULL,
`Year` int(11) NOT NULL,
`Employee` int(11) NOT NULL,
`Sum` decimal(8,2) NOT NULL,
PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2255 ;
CREATE TABLE IF NOT EXISTS `penalties` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`Employee` int(11) NOT NULL,
`Month` int(11) NOT NULL,
`Year` int(11) NOT NULL,
`Type` int(11) NOT NULL,
`Amount` decimal(8,4) NOT NULL,
`Price` decimal(8,2) NOT NULL,
`Sum` decimal(8,2) NOT NULL,
PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1603 ;
CREATE TABLE IF NOT EXISTS `penalty_types` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(100) NOT NULL,
PRIMARY KEY (`idx`),
UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=56 ;
CREATE TABLE IF NOT EXISTS `titles` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(100) NOT NULL,
`SortOrder` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`idx`),
UNIQUE KEY `Title` (`Name`),
KEY `SortOrder` (`SortOrder`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=108 ;
|
