BTCiXLxZ

1-- Get count tickets and summary replies from start of month
2select sysadmin_niks_id, COUNT(ticket_id), SUM(replies)
3from sysadmin_activities
4where (lastreply between  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )
5GROUP by sysadmin_niks_id;
6
7-- #test count of tickets by user
8/* snik_id
9select name,sysadmin_niks_id,COUNT(DISTINCT ticket_id)AS tickets_count, COUNT(lastreply) AS replies_count,SUM(time_uses)  AS time_sum 
10FROM sysadmin_activities
11LEFT JOIN sysadmin_niks AS sniks ON sniks.id=sysadmin_niks_id
12LEFT JOIN users ON users.id=user_id
13WHERE sysadmin_niks_id IN (3,7)
14AND (lastreply between  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )
15GROUP BY (sysadmin_niks_id);
16*/
17
18SELECT users.name,COUNT(DISTINCT ticket_id)AS tickets_count, COUNT(sact.lastreply) AS replies_count,SUM(time_uses)  AS time_sum, Sum(serv.compl)
19FROM sysadmin_activities as sact
20LEFT JOIN sysadmin_niks AS sniks ON sniks.id=sysadmin_niks_id
21LEFT JOIN users ON users.id=user_id
22join tickets ON tickets.id=ticket_id
23RIGHT JOIN services as serv ON tickets.service_id=serv.id
24WHERE sysadmin_niks_id IN (
25	SELECT sniks.id FROM users
26	LEFT JOIN sysadmin_niks AS sniks ON sniks.user_id=users.id
27WHERE users.id=2
28)
29AND (sact.lastreply between  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )
30GROUP BY (users.name) ORDER BY tickets_count DESC, replies_count DESC;
31
32-- get summary and comp count
33SELECT u.name, COUNT(DISTINCT ticket_id) AS tickets_count, COUNT(sact.lastreply) AS replies_count ,SUM(time_uses), SUM(serv.compl)
34-- SELECT u.name, ticket_id AS tickets_count, sact.lastreply AS replies_count ,time_uses, services.compl
35FROM sysadmin_activities AS sact
36LEFT JOIN sysadmin_niks AS sniks ON sniks.id=sysadmin_niks_id
37LEFT JOIN users AS u ON u.id=user_id
38join tickets ON tickets.id=ticket_id
39RIGHT JOIN services as serv ON tickets.service_id=serv.id
40WHERE sysadmin_niks_id IN (
41	SELECT sniks.id FROM users
42	LEFT JOIN sysadmin_niks AS sniks ON sniks.user_id=users.id
43-- WHERE users.id=2
44)
45## curr month
46-- AND (sact.lastreply between  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )
47# last month
48AND (sact.lastreply between  DATE_FORMAT('2018-07-01' ,'%Y-%m-01') AND DATE_FORMAT('2018-09-31','%Y-%m-%d') )
49GROUP BY (name) ORDER BY tickets_count DESC, replies_count DESC;
50-- /summary and compl count
51-- ORM
52/*
53 * DB::table('sysadmin_activities')->
54 * select(DB::raw('sysadmin_niks_id, COUNT(DISTINCT ticket_id) AS tickets_count,COUNT(lastreply) AS replies_count, SUM(time_uses) AS time_sum'))->
55 * whereBetween('lastreply',[\Carbon\Carbon::now()->startOfMonth(),\Carbon\Carbon::now()])->
56 * where('sysadmin_niks_id',2)->
57 * groupBy('sysadmin_niks_id')->
58-- do whatever
59*/
60/*
61 * 4 all users
62 DB::table('sysadmin_activities')->
63  select(DB::raw('name, COUNT(DISTINCT ticket_id) AS tickets_count,COUNT(lastreply) AS replies_count, SUM(time_uses) AS time_sum'))->
64  leftJoin('sysadmin_niks as sniks','sniks.id','=','sysadmin_niks_id')->
65  leftJoin('users','users.id','=','user_id')->
66  whereIn('sysadmin_niks_id',function($q){
67  $q->select(DB::raw('sniks.id from users left join sysadmin_niks as sniks on sniks.user_id=users.id'));
68  })->
69  whereBetween('lastreply',[\Carbon\Carbon::now()->startOfMonth(),\Carbon\Carbon::now()])->
70  groupBy('name')->
71  do whatever
72  */
73
74-- get sniks.id 4 user
75SELECT sniks.id FROM users
76LEFT JOIN sysadmin_niks AS sniks ON sniks.user_id=users.id
77WHERE users.id=2;
78
79
80-- /test count of tickets by user
81
82UPDATE tickets SET user_assign_id=2 WHERE ticketid=10597 AND service_id=1;
83
84SELECT COUNT(last_replier_nik_id) AS serv_1 FROM tickets
85WHERE service_id=1
86UNION SELECT COUNT(last_replier_nik_id) AS serv_2 FROM tickets
87WHERE service_id=2;
88
89-- get rate 4 users or curr user in current service beetwen in date
90select services.name as service, COUNT(DISTINCT sact.ticket_id) AS tickets_count,COUNT(sact.lastreply) AS replies_count, u.name as user_name, SUM(sact.time_uses) AS sum_time, COUNT(DISTINCT ticket_id)*compl AS rate
91from sysadmin_activities as sact
92RIGHT JOIN tickets AS t ON sact.ticket_id=t.id
93RIGHT JOIN services ON t.service_id=services.id
94LEFT JOIN sysadmin_niks AS sniks ON sniks.id=sact.sysadmin_niks_id
95LEFT JOIN users AS u ON u.id=sniks.user_id
96where sact.sysadmin_niks_id in(select sniks.id from users LEFT JOIN sysadmin_niks as sniks on sniks.user_id=users.id 
97where users.id=1
98)
99AND (sact.lastreply between  DATE_FORMAT('2018-07-01' ,'%Y-%m-%d') AND DATE_FORMAT('2018-09-31','%Y-%m-%d') )
100AND services.id=1
101GROUP BY service, user_name
102ORDER BY tickets_count DESC, rate DESC;
103-- /get rate 4 users or curr user in current service beetwen in date
104
105
106UPDATE services SET compl=0.8 where services.id=1;
107
108-- get rate without summing
109select services.name as service, sact.ticket_id, sact.lastreply AS replies_count, u.name as user_name, sact.time_uses
110from sysadmin_activities as sact
111RIGHT JOIN tickets AS t ON sact.ticket_id=t.id
112RIGHT JOIN services ON t.service_id=services.id
113LEFT JOIN sysadmin_niks AS sniks ON sniks.id=sact.sysadmin_niks_id
114LEFT JOIN users AS u ON u.id=sniks.user_id
115where sact.sysadmin_niks_id in(select sniks.id from users LEFT JOIN sysadmin_niks as sniks on sniks.user_id=users.id 
116where users.id=1
117)
118AND (sact.lastreply between  DATE_FORMAT('2018-07-01' ,'%Y-%m-%d') AND DATE_FORMAT('2018-07-31','%Y-%m-%d') )
119AND services.id=1;
120-- /get rate without summing
121
122SELECT id from tickets WHERE service_id=1 LIMIT 1;
123
124SELECT t.id FROM tickets AS t
125WHERE t.is_closed=0 AND t.last_replier_nik_id=0 AND t.user_assign_id is NULL;
126
127-- add and drop indexes
128CREATE INDEX ticket_open ON tickets (is_closed);
129DROP INDEX ticket_open ON tickets;
130
131SELECT * from tickets USE INDEX(ticket_open);
132
133DROP DATABASE crm_tickets_db;
134CREATE DATABASE crm_tickets_db;
135GRANT ALL PRIVILEGES ON crm_tickets_db.* TO 'boss'@'localhost';
136flush PRIVILEGES;
137UPDATE tickets SET user_assign_id=NULL where user_assign_id=9;
138
139
140-- talbe like
141CREATE DATABASE test;
142USE test;
143CREATE TABLE if NOT EXISTS users (
144     id_1 MEDIUMINT NOT NULL AUTO_INCREMENT,
145     name CHAR(30) NOT NULL,
146     PRIMARY KEY (id_1)
147);
148INSERT INTO users (name) VALUES('bob'),('mike'),('george'),('steve');
149SELECT * from test.users;
150CREATE TABLE `like` (
151    id_2 MEDIUMINT,
152    id_3 MEDIUMINT,
153FOREIGN KEY(id_2) REFERENCES users(id_1) ON UPDATE CASCADE on DELETE CASCADE,
154FOREIGN KEY(id_3) REFERENCES users(id_1) on UPDATE CASCADE on DELETE CASCADE
155); 
156ALTER TABLE `like` 
157ADD CONSTRAINT fk_id_2
158FOREIGN KEY(id_2) REFERENCES users(id_1);
159DROP TABLE `like`;
160
161INSERT into `like` VALUES(1,2),(1,3),(2,3);
162SELECT * FROM `like`;
163
164SELECT u.name,id_3,id_1 FROM `like` as l
165RIGHT JOIN users as u ON l.id_3=u.id_1
166-- RIGHT JOIN users  ON l.id_2=users.id_1
167WHERE l.id_3 is null;
168
169SELECT * FROM users
170LEFT JOIN `like` as l ON l.id_3=id_1
171WHERE l.id_3 IS NULL; 
172DROP DATABASE test;
173
174INSERT INTO sysadmin_niks(service_id,admin_nik,user_id) VALUES(1,'Mooryan',1);
175
176-- admin activities
177INSERT INTO sysadmin_activities(sysadmin_niks_id,ticket_id,lastreply,time_uses) VALUES(2,146,'2018-08-27 08:45:31',15);
178UPDATE sysadmin_activities SET `lastreply`='2018-09-27 08:45:31' WHERE ticket_id=146;
179-- service month statistic
180SELECT DISTINCT(t.id), COUNT(DISTINCT(sact.ticket_id)) as t_count,t.subject,s.name,t.ticketid, SUM(sact.time_uses) as sum_time FROM tickets as t
181RIGHT JOIN sysadmin_activities AS sact ON sact.ticket_id=t.id
182JOIN services AS s ON t.service_id=s.id
183-- WHERE created_at>= subdate(curdate(),1)
184-- yesterday
185-- WHERE sact.lastreply>=SUBDATE(NOW(),2)
186WHERE (t.created_at BETWEEN DATE_FORMAT('2018-10-01' ,'%Y-%m-%d') AND last_day('2018-10-01'))
187-- AND t.service_id=1
188GROUP BY t.id ORDER by sum_time;
189
190SELECT s.id,s.name,COUNT(t.id),SUM(sact.time_uses) FROM services AS s
191LEFT JOIN tickets AS t ON t.service_id=s.id
192JOIN sysadmin_activities AS sact ON sact.ticket_id=t.id
193WHERE s.id = 4 AND sact.lastreply BETWEEN
194GROUP BY s.name;
195
196-- service statistic by yesterday
197SELECT DISTINCT(t.id),t.subject,s.name,t.ticketid, SUM(sact.time_uses) FROM tickets as t
198LEFT JOIN sysadmin_activities AS sact ON sact.ticket_id=t.id
199JOIN services AS s ON t.service_id=s.id
200-- WHERE sact.lastreply= curdate()
201AND t.service_id=4
202GROUP BY t.id;
203
204
205SELECT id FROM tickets WHERE created_at 
206-- BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW();
207-- BETWEEN DATE_FORMAT('2018-09-01','%Y-%m-%d') AND DATE_FORMAT('2018-09-30','%Y-%m-%d');
208--  BETWEEN DATE_FORMAT('2018-09-01','%Y-%m-%d') AND NOW();
209 BETWEEN DATE_FORMAT('2018-09-01','%Y-%m-%d') AND LAST_DAY('2018-09-01');
210 
211 SELECT last_day(now());
212SELECT SUBDATE(CURDATE(),10);
213SELECT SUBDATE(NOW(),1);
214SELECT NOW() - INTERVAL 10 DAY;
215SELECT id, lastreply FROM sysadmin_activities
216WHERE lastreply BETWEEN DATE_FORMAT(CURDATE(),'%Y-%m-%d 00:00:00') and now();
217
218SELECT id, lastreply FROM sysadmin_activities
219WHERE lastreply BETWEEN DATE_FORMAT(subdate(CURDATE(),1),'%Y-%m-%d 00:00:00') and DATE_FORMAT(subdate(CURDATE(),1),'%Y-%m-%d 23:59:59');
220
221
222SELECT SUM(tmp.sum_time_uses) AS total from (
223SELECT sum(time_uses) sum_time_uses FROM sysadmin_activities WHERE  ticket_id=3) as tmp;
224
225select NOW();
226select DATE_FORMAT(CURDATE(),'%Y-%m-%d 00:00:00')
227UNION
228SELECT SUM(tmp.sum_time_uses) AS total from (
229SELECT sum(time_uses) sum_time_uses FROM sysadmin_activities WHERE  ticket_id=3) as tmp;
230
231
232SELECT SUM(time_uses) sum_time_uses FROM sysadmin_activities WHERE  ticket_id=3;
233
234-- create table 4 emailing
235CREATE TABLE IF NOT EXISTS emails(
236id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
237email VARCHAR(86) NOT NULL UNIQUE,
238is_main TINYINT(1) UNSIGNED DEFAULT 0
239);
240
241CREATE TABLE IF NOT EXISTS intervals(
242id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
243name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL UNIQUE,
244url_attr VARCHAR(22) NOT NULL UNIQUE,
245INDEX itreval_indx (name)
246);
247
248CREATE table if NOT EXISTS mailables(
249id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
250service_id TINYINT UNSIGNED NOT NULL,
251interval_id TINYINT UNSIGNED NOT NULL,
252FOREIGN KEY(service_id) REFERENCES services(id) ON DELETE CASCADE ON UPDATE CASCADE,
253FOREIGN KEY (interval_id) REFERENCES intervals(id) ON DELETE CASCADE on UPDATE CASCADE
254);
255
256CREATE TABLE IF NOT EXISTS mail_lists(
257id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
258mailable_id SMALLINT UNSIGNED NOT NULL,
259email_id SMALLINT UNSIGNED NOT NULL,
260FOREIGN KEY(email_id) REFERENCES emails(id) ON UPDATE CASCADE on DELETE CASCADE,
261FOREIGN KEY (mailable_id) REFERENCES mailables(id) on DELETE CASCADE ON UPDATE CASCADE
262);
263-- /create tables 4 emailngs
264
265DROP TABLE intervals;
266
267INSERT INTO mailables (service_id,mail_id,interval_id) VALUES (1,2,1);
268
269SELECT * FROM intervals;
270
271SELECT DISTINCT(s.name) as sname,
272e.email
273FROM mailables AS m
274INNER JOIN services as s ON m.service_id=s.id
275JOIN intervals as i on m.interval_id=i.id
276JOIN emails as e on m.mail_id=e.id
277where s.id=1
278GROUP BY sname;
279
280ALTER TABLE emails ADD is_main TINYINT(1) UNSIGNED DEFAULT 0;
281
282SELECT s.name, e.email, i.name
283FROM mailables AS m
284LEFT JOIN services AS s ON s.id=m.service_id
285LEFT JOIN emails AS e ON e.id=m.mail_id
286LEFT JOIN intervals AS i ON i.id=m.interval_id;
287
288SELECT service_id,interval_id FROM mailables
289GROUP BY service_id,interval_id ORDER BY service_id;
290
291SELECT mail_id FROM mailables
292WHERE service_id = 1 AND interval_id=1;
293
294INSERT INTO mailables(service_id,interval_id) VALUES(1,1),(1,2),(2,3);
295INSERT INTO mail_lists(mailable_id,email_id) VALUES(1,1),(1,2),(3,1);
296
297SELECT * FROM tickets USE INDEX(ticket_open);
298
299INSERT INTO sysadmin_activities (sysadmin_niks_id, ticket_id, lastreply, time_uses) VALUES(2,16,'2018-10-09 11:00:21', 103);
300DELETE FROM tickets WHERE id=2;
301
302-- FK
303select CONSTRAINT_NAME
304from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
305where TABLE_NAME = 'tickets';
306
307ALTER TABLE tickets DROP FOREIGN KEY tickets_service_id_foreign;
308
309ALTER TABLE tickets ADD CONSTRAINT tickets_service_id_foreign
310FOREIGN KEY(service_id) REFERENCES services(id) ON DELETE CASCADE ON UPDATE CASCADE;
311
312-- /FK
313
314DELETE FROM mailables;
315
316-- remove old mailables posibilities
317ALTER TABLE services ADD email VARCHAR(86) NULL;
318ALTER TABLE emails DROP is_main;
319-- /old mailable posibilities
320
321UPDATE services SET email='endnet@ukr.net' WHERE name='adminvps';
322
323SELECT t.id, t.ticketid, s.name, t.subject, sact.lastreply, sact.id as sact_id, sact.time_uses, snik.admin_nik, u.name as user_name
324FROM tickets as t
325RIGHT JOIN sysadmin_activities as sact ON sact.ticket_id=t.id
326INNER JOIN sysadmin_niks as snik ON snik.id=sact.sysadmin_niks_id
327LEFT JOIN users as u ON snik.user_id=u.id
328LEFT JOIN services as s ON s.id=t.service_id
329ORDER BY sact.lastreply;
330
331CREATE USER 'boss'@'localhost' identified BY '1111';
332
333GRANT ALL PRIVILEGES ON crm_tickets_db.* TO "boss"@"localhost";
334flush PRIVILEGES;
335
336select * from `sessions`;
337
338
339
340CREATE USER 'boss2'@'localhost' IDENTIFIED WITH mysql_native_password BY '1111';
341GRANT ALL PRIVILEGES ON crm_db_clone.* TO 'boss2'@'localhost' WITH GRANT OPTION;
342CREATE USER 'boss2'@'%' IDENTIFIED WITH mysql_native_password BY '1111';
343GRANT ALL PRIVILEGES ON crm_db_clone.* TO 'boss2'@'%' WITH GRANT OPTION;
344
345GRANT ALL ON `crm_tickets_db`.* TO 'boss1'@'%' ;
346FLUSH PRIVILEGES ;
347
348CREATE DATABASE _db;
349CREATE USER 'boss1'@'localhost' identified WITH mysql_native_password BY '111111';
350GRANT ALL PRIVILEGES ON oauth_db.* TO 'er'@'localhost' WITH GRANT OPTION;
351
352SELECT * FROM transactions
353WHERE date=STR_TO_DATE('20.10.2018','%d.%m.%Y');
354
355select * from `cards` where `user_id` = 26 limit 1;
356
357SHOW DATABASES;
358
359ALTER TABLE sysadmin_niks ADD INDEX (service_id);
360ALTER TABLE sysadmin_activities ADD INDEX (ticket_id);
361
362SELECT COUNT(id) FROM tickets;