Userlog_data.php
11 KB
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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
<?php namespace App\Console\Commands;
use Illuminate\Console\Command;
use DB;
use Config;
use App\Models\User;
use App\Models\Accesslog;
use App\Models\CRMCall;
use Schema;
use PDO;
use Illuminate\Database\Schema\Blueprint;
class Userlog_data extends Command {
/**
* The console command name.
*
* @var string
*/
protected $signature = 'Userlog_data';
/**
* The console command description.
*
* @var string
*/
protected $description = 'Userlog_data';
/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
$nowts=time();
echo "\n".date('Y-m-d')."\n";
$logdate=strtotime('-1 day');
$tcol=0;$fieldsarr=array();$extrahdrarr=array();
$server_ip=env('app_ip');
$central_ip=env('central_ip');
$conn = array(
'driver' => 'mysql',
'host' => $central_ip,
'database' => env('DB_DATABASE', 'kstych_flexydial'),
'username' => env('DB_USERNAME', 'root'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'options' => array(
PDO::ATTR_TIMEOUT => 5,
),
);
Config::set("database.connections.conn", $conn);
if(DB::connection("conn")->getDatabaseName())
{
$serverclist=DB::connection("conn")->select(DB::raw("select id from server_details where server_ip='$server_ip'"));
$server_id=$serverclist[0]->id;
if($server_id<10){
$server_id="0".$server_id;
}
$i=0;
$ulist=DB::select(DB::raw("select * from users WHERE 1"));
foreach($ulist as $uline) {
$users[$uline->id] = $uline->username;
}
if($alist=DB::select(DB::raw("select * from userlogs WHERE created_at>'".date("Y-m-d",$logdate)."' and created_at<'".date("Y-m-d",$logdate+24*60*60)."'"))){
foreach($alist as $aline) {
$i++;
$global_id = $server_id . $i;
if($aline->enddate=='0000-00-00'|| $aline->endtime=='00:00:00' || $aline->durationsec=='0')
{
$enddatetime=date("Y-m-d H:i:s",strtotime($aline->updated_at));
$enddate=explode(" ",$enddatetime)[0];
$endtime=explode(" ",$enddatetime)[1];
$durationsec=date("Y-m-d H:i:s",strtotime($endtime-$aline->starttime));
}
else
{
$enddate=$aline->enddate;
$endtime=$aline->endtime;
$durationsec=$aline->durationsec;
}
$rowdata = array('server'=>$server_id,'server_ip'=>$server_ip,'global_id'=>$global_id,'id'=>$aline->id,
'created_at'=>$aline->created_at,'updated_at'=>$aline->updated_at,'user_id'=>$aline->user_id,'user'=>$users[$aline->user_id],
'startdate'=>$aline->startdate,'starttime'=>$aline->starttime,'enddate'=>$enddate,'endtime'=>$endtime,
'durationsec'=>$durationsec,'data'=>$aline->data,'group'=>$aline->group,'login'=>'','dialnext'=>'','dialnext-agentbriefing'=>'','dialnext-downtime'=>'','dialnext-floorannouncements'=>'','dialnext-incoming'=>'','dialnext-lunchbreak'=>'','dialnext-manual'=>'','dialnext-notready'=>'','dialnext-qualityfeedback'=>'','dialnext-teabreak'=>'','dialnext-teammeeting'=>'','dialnext-utilitybreak'=>'','manual'=>'','manual-agentbriefing'=>'','manual-agentbriefing'=>'','manual-downtime'=>'','manual-floorannouncements'=>'','manual-incoming'=>'','manual-lunchbreak'=>'','manual-manual'=>'','manual-notready'=>'','manual-qualityfeedback'=>'','manual-teabreak'=>'','manual-teammeeting'=>'','manual-utilitybreak'=>'','paused'=>'','paused-agentbriefing'=>'','paused-downtime'=>'','paused-floorannouncements'=>'','paused-incoming'=>'','paused-lunchbreak'=>'','paused-manual'=>'','paused-notready'=>'','paused-qualityfeedback'=>'','paused-teabreak'=>'','paused-teammeeting'=>'','paused-utilitybreak'=>'','paused-autowrapup'=>'','paused-wrapup'=>'','progressive'=>'','progressive-agentbriefing'=>'','progressive-agentbriefing'=>'','progressive-downtime'=>'','progressive-floorannouncements'=>'','progressive-incoming'=>'','progressive-lunchbreak'=>'','progressive-manual'=>'','progressive-notready'=>'','progressive-qualityfeedback'=>'','progressive-teabreak'=>'','progressive-teammeeting'=>'','progressive-utilitybreak'=>'','ready-incoming'=>''
);
$data=json_decode($aline->data,true);
foreach($data as $sipid=>$sdata)
{
$prets= isset($sdata[1]) ? $sdata[1] : (strtotime($aline->startdate . " " . $aline->starttime)+19600)*1000;
if(isset($sdata['states']))
{
$previous="login";
foreach($sdata['states'] as $fts=>$states)
{
if($states[0] != 1)
{
$rowdata[$previous] +=round(($fts-$prets)/1000,2);
$previous = (trim($states[1]) != '') ? strtolower($states[0]."-".$states[1]) : strtolower($states[0]);
$prets=$fts;
}
}
$rowdata[$previous] += round(($sdata['ts']-$prets)/1000,2);
}
}
$rowdata["login"] = $aline->durationsec;
$rowdata['not-ready']=$rowdata['paused-agentbriefing']+$rowdata['paused-autowrapup']+$rowdata['paused-downtime']+$rowdata['paused-floorannouncements']+$rowdata['paused-lunchbreak']+$rowdata['paused-notready']+$rowdata['paused-qualityfeedback']+$rowdata['paused-teammeeting']+$rowdata['paused-teabreak']+$rowdata['paused-utilitybreak'];
//$rowdata["not-ready"] = $aline->durationsec-($rowdata['dialnext']+$rowdata['dialnext-agentbriefing']+$rowdata['dialnext-downtime']+$rowdata['dialnext-floorannouncements']+$rowdata['dialnext-incoming']+$rowdata['dialnext-lunchbreak']+$rowdata['dialnext-manual']+$rowdata['dialnext-notready']+$rowdata['dialnext-qualityfeedback']+$rowdata['dialnext-teabreak']+$rowdata['dialnext-teammeeting']+$rowdata['dialnext-utilitybreak']+$rowdata['manual']+$rowdata['manual-agentbriefing']+$rowdata['manual-downtime']+$rowdata['manual-floorannouncements']+$rowdata['manual-incoming']+$rowdata['manual-lunchbreak']+$rowdata['manual-manual']+$rowdata['manual-notready']+$rowdata['manual-qualityfeedback']+$rowdata['manual-teabreak']+$rowdata['manual-teammeeting']+$rowdata['manual-utilitybreak']+$rowdata['paused']+$rowdata['paused-agentbriefing']+$rowdata['paused-autowrapup']+$rowdata['paused-downtime']+$rowdata['paused-floorannouncements']+$rowdata['paused-incoming']+$rowdata['paused-lunchbreak']+$rowdata['paused-manual']+$rowdata['paused-notready']+$rowdata['paused-qualityfeedback']+$rowdata['paused-teabreak']+$rowdata['paused-teammeeting']+$rowdata['paused-utilitybreak']+$rowdata['paused-autowrapup']+$rowdata['paused-wrapup']+$rowdata['progressive']+$rowdata['progressive-agentbriefing']+$rowdata['progressive-agentbriefing']+$rowdata['progressive-downtime']+$rowdata['progressive-floorannouncements']+$rowdata['progressive-incoming']+$rowdata['progressive-lunchbreak']+$rowdata['progressive-manual']+$rowdata['progressive-notready']+$rowdata['progressive-qualityfeedback']+$rowdata['progressive-teabreak']+$rowdata['progressive-teammeeting']+$rowdata['progressive-utilitybreak']+$rowdata['ready-incoming']);
$key_value = '';
foreach($rowdata AS $key=>$value) {
if($key != 1)
$key_value .= "`$key` = '$value', ";
}
$startTime=$aline->startdate." ".$aline->starttime;
$endTime=$aline->enddate." ".$aline->endtime;
$crmCalls=DB::select(DB::raw("select user_id,type,ts_Wait,ts_Call,ts_Talk,ts_Recstart,ts_Recend,ts_Dispo,ts_Close from crmcalls WHERE updated_at>='".$startTime."' and updated_at<'".$endTime."' and user_id='".$aline->user_id."'"));
$ts_Wait=0;$ts_Call=0;$ts_Talk=0;$ts_Dispo=0;
$progTs_Wait=0;$progTs_Call=0;$progTs_Talk=0;$progTs_Dispo=0;
$manTs_Wait=0;$manTs_Call=0;$manTs_Talk=0;$manTs_Dispo=0;
$inbTs_Wait=0;$inbTs_Call=0;$inbTs_Talk=0;$inbTs_Dispo=0;
$tt_prog=0;$tt_man=0;$tt_inb=0;
if($crmCalls!=null){
foreach($crmCalls as $crmCall){
$ts_Wait += round(($crmCall->ts_Call - $crmCall->ts_Wait)/1000,2);
$ts_Call += round(($crmCall->ts_Talk - $crmCall->ts_Call)/1000,2);
$ts_Talk += round(($crmCall->ts_Dispo - $crmCall->ts_Talk)/1000,2);
$ts_Dispo += round(($crmCall->ts_Close - $crmCall->ts_Dispo)/1000,2);
if($crmCall->type == 'Progressive')
{
$progTs_Wait += round(($crmCall->ts_Call - $crmCall->ts_Wait)/1000,2);
$progTs_Call += round(($crmCall->ts_Talk - $crmCall->ts_Call)/1000,2);
$progTs_Talk += round(($crmCall->ts_Dispo - $crmCall->ts_Talk)/1000,2);
$progTs_Dispo += round(($crmCall->ts_Close - $crmCall->ts_Dispo)/1000,2);
}
if($crmCall->type == 'Manual')
{
$manTs_Wait += round(($crmCall->ts_Call - $crmCall->ts_Wait)/1000,2);
$manTs_Call += round(($crmCall->ts_Talk - $crmCall->ts_Call)/1000,2);
$manTs_Talk += round(($crmCall->ts_Dispo - $crmCall->ts_Talk)/1000,2);
$manTs_Dispo += round(($crmCall->ts_Close - $crmCall->ts_Dispo)/1000,2);
}
if($crmCall->type == 'Inbound')
{
$inbTs_Wait += round(($crmCall->ts_Call - $crmCall->ts_Wait)/1000,2);
$inbTs_Call += round(($crmCall->ts_Talk - $crmCall->ts_Call)/1000,2);
$inbTs_Talk += round(($crmCall->ts_Dispo - $crmCall->ts_Talk)/1000,2);
$inbTs_Dispo += round(($crmCall->ts_Close - $crmCall->ts_Dispo)/1000,2);
}
}
}
$tt_prog = $progTs_Wait + $progTs_Call + $progTs_Talk + $progTs_Dispo;
$tt_man = $manTs_Wait + $manTs_Call + $manTs_Talk + $manTs_Dispo;
$tt_inb = $inbTs_Wait + $inbTs_Call + $inbTs_Talk + $inbTs_Dispo;
//$rowdata['not-ready']=$aline->durationsec-($rowdata['paused-agentbriefing']+$rowdata['paused-autowrapup']+$rowdata['paused-downtime']+$rowdata['paused-floorannouncements']+$rowdata['paused-lunchbreak']+$rowdata['paused-notready']+$rowdata['paused-qualityfeedback']+$rowdata['paused-teammeeting']+$rowdata['paused-teabreak']+$rowdata['paused-utilitybreak']+$tt_prog+$tt_man+$tt_inb);
$prod_TOS = $ts_Wait + $ts_Call + $ts_Talk + $ts_Dispo;
//$rowdata['not-ready']=$aline->durationsec-$prod_TOS;
$key_value .= "`tt_prog` = '$tt_prog', ";
$key_value .= "`tt_man` = '$tt_man', ";
$key_value .= "`tt_inb` = '$tt_inb', ";
$key_value .= "`ts_Wait` = '$ts_Wait', ";
$key_value .= "`ts_Call` = '$ts_Call', ";
$key_value .= "`ts_Talk` = '$ts_Talk', ";
$key_value .= "`ts_Dispo` = '$ts_Dispo', ";
$key_value .= "`progts_Wait` = '$progTs_Wait', ";
$key_value .= "`progts_Call` = '$progTs_Call', ";
$key_value .= "`progts_Talk` = '$progTs_Talk', ";
$key_value .= "`progts_Dispo` = '$progTs_Dispo', ";
$key_value .= "`mants_Wait` = '$manTs_Wait', ";
$key_value .= "`mants_Call` = '$manTs_Call', ";
$key_value .= "`mants_Talk` = '$manTs_Talk', ";
$key_value .= "`mants_Dispo` = '$manTs_Dispo', ";
$key_value .= "`incts_Wait` = '$inbTs_Wait', ";
$key_value .= "`incts_Call` = '$inbTs_Call', ";
$key_value .= "`incts_Talk` = '$inbTs_Talk', ";
$key_value .= "`incts_Dispo` = '$inbTs_Dispo', ";
$key_value .= "`prod_tos` = '$prod_TOS', ";
$key_value = substr($key_value, 0, -2);
$userlogsTable = "userlogs_".date("d_m_Y",$logdate);
DB::connection("conn")->insert(DB::raw("INSERT INTO ".$userlogsTable." SET $key_value"));
}
}
}
}
}