20231012_python练习_服务端与客户端数据交互v2_增加xlsx表格数据批量导入数据库

服务端增加根据上传附件格式 xlsx 类型，将表格第一个sheet数据批量快速导入数据库

服务端-20231024 修改了导入检测代码，防止导入数据异常导致失败

import socketserver
import json
import os
#import pymysql
import cx_Oracle #Oracle 数据库连接
import time
import tqdm
import pandas as pd
import openpyxl

class MyServer(socketserver.BaseRequestHandler):


    def handle(self):
        self.add_ip = self.client_address[0]
        self.add_post = str(self.client_address[1])

        while True:
            try:
                data = self.request.recv(102400)
                #如果获取为空就退出
                if not data:break
                #否则解码处理数据
                self.data = json.loads(data.decode('utf-8'))
                # data 是获取字典内容，self.client_address 是 ip地址与 端口
                print('客户端的消息:',self.data,self.client_address)
                #数据库对访问记录存档
                self.log_record()
                # 定义处理规则
                self.visit_response()
                #将结果反馈给客户端
                self.request.sendall(self.fankui.encode('utf-8'))
            except :
                #print('连接异常')
                break


    #定义处理规则
    def visit_response(self):
        #获取时间
        in_time = self.get_current_time()[0:14]
        print('self.data1111',self.data)

        if self.data['leixing'] == '文件传递':
            try:
                file_download_statr,path,file_type = self.file_download()
                # 如果文件名类型是 xlsx 就把数据导入数据库
                # time.sleep(0.5)
                try:
                    print('file_type', file_type)
                    if str(file_type).upper() == 'XLSX':
                        wb = openpyxl.load_workbook(path)
                        #wb.sheetnames[0] 第一个sheet名
                        ws = wb[wb.sheetnames[0]]

                        data_import = pd.read_excel(path, sheet_name=wb.sheetnames[0], header=0, dtype='str',keep_default_na=False);
                        data_import = data_import.where(data_import.notnull(), None);

                        file_path_state1 = os.path.exists(path)
                        print('path', type(path), file_path_state1)
                        ws_type, excel_nr,sheet_name = excel_type(str(path))
                        print('结果', ws_type, excel_nr,sheet_name)
                        table_name = 'ceshi_tmp_' + str(self.add_ip).replace('.', '')
                        excel_in_state = excel_in_new(table_name, ws_type, excel_nr,path,sheet_name)
                        print('excel_in_state', excel_in_state)
                except:
                    print('数据导入数据库失败')

                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, excel_in_state, file_download_statr)
            except:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, self.add_post, '传递失败')

        elif self.data['leixing'] == '文件下载': #***********************20230906新增
            self.user_ip = self.service_user()
            print('user_ip', self.user_ip, type(self.user_ip))
            #限制ip地址，不在里面的不允许
            if self.add_ip in ('134.35.33.250','134.80.99.107','134.80.98.158','134.80.98.173','134.35.10.10') or self.add_ip in self.user_ip:
                # self.user_ip 是通过 shzc.yytowz_service_user 这个表维护
                # 获取sql语句生成表格
                download_start, file_path, file_name, file_size = Oracle_download(self.data['mac'], in_time,str(self.data['ziduan1']).replace("^","'"))
                print('download_start', download_start, '大小字节', file_size, '文件名', file_name)
                try:
                    # 将表格返回客户端
                    file_download_statr = self.file_up(file_path,self.data['ziduan3'])
                    self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                    self.data['leixing'], file_path, file_size, file_download_statr)
                except:
                    self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                    self.data['leixing'], file_path, file_size, '传递失败')
            else:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                    self.data['leixing'], 'file_path', '0', '传递失败')

        elif self.data['leixing'] == '发起访问':
            try:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, self.add_post, '访问成功')
            except:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, self.add_post, '访问失败')

        else:
            try:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, self.add_post, '访问成功')
            except:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, self.add_post, '访问失败')

    #文件下载函数
    def file_download(self):
        in_time = self.get_current_time()[0:14]
        file_path = self.data['ziduan1']
        file_size = self.data['ziduan2']
        file_hz = file_path.split('/')[-1]
        file_type = file_hz.split('.')[-1]
        print('接收文件名：',file_hz,' 接收文件大小：',file_size,' 字节')
        # 文件传输的缓冲区
        BUFFER_SIZE = 4096
        # 接受客户端信息
        filename, file_size, new_filename = self.data['ziduan1'], self.data['ziduan2'], str(self.data['ziduan3'])[0:6]
        #判断月文件夹是否存在，不存在创建一个
        file_path_state1 = os.path.exists('./file_server/'+new_filename)
        if file_path_state1 == False:
            os.mkdir('./file_server/'+new_filename)
        # 获取文件的名字
        filename = os.path.basename(filename)
        path = './file_server/'+new_filename +'/' + self.data['mac'] + '_' + in_time + '_' + file_hz
        print('filename',os.path.isfile(filename),filename)
        file_size = int(file_size)
        if os.path.isfile(path):
            f = open(path, "wb")
        else:
            f = open(path, "wb")
        rece_size = 0
        while rece_size < file_size:
            data = self.request.recv(BUFFER_SIZE)
            f.write(data)
            rece_size += len(data)
        else:
            return '传递成功',path,file_type

    def file_up(self,fujian_label,in_time):
        # 文件传输的缓冲区
        BUFFER_SIZE = 4096
        # 传递文件到指定目录下
        filename = fujian_label.replace('/', '//')
        # 文件大小
        file_size = os.path.getsize(filename)
        # 创建连接
        chuandi_tup = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%d","ziduan3":"%s" }' % (
            '文件提取', filename, file_size, in_time)
        self.request.sendall(chuandi_tup.encode('utf-8'))
        # 文件传输
        progress = tqdm.tqdm(range(file_size), f"发送{filename}", unit="B", unit_divisor=1024)

        with open(filename, "rb") as f:
            for _ in progress:
                # 读取文件
                bytes_read = f.read(BUFFER_SIZE)
                if not bytes_read:
                    break
                # sendall确保及时网络忙碌的时候，数据仍然可以传输
                self.request.sendall(bytes_read)
                progress.update(len(bytes_read))
        # 关闭资源
        self.request.close()

    #数据库登录
    def mysql_execute(self, in_sql, leixing):
        # 登录数据库
        #conn = pymysql.connect(host='127.0.0.1', port=3306, user='szc', password='szcNSP850219', database='szc_sql',charset='utf8')

        dsn = "134.80.200.216/pdbzbjs1"
        try:
            conn = cx_Oracle.connect(user="zbweb", password="zibo_533_03", dsn=dsn, encoding="UTF-8")
        except:
            time.sleep(10)
            conn = cx_Oracle.connect(user="zbweb", password="zibo_533_03", dsn=dsn, encoding="UTF-8")

        # 得到一个可以执行SQL语句的光标对象
        cursor = conn.cursor()
        # 数据库执行导入的语句
        if leixing == '数量':
            # 反馈数量
            count = cursor.execute(in_sql)
        elif leixing == '单条':
            # 反馈单条
            cursor.execute(in_sql)
            count = cursor.fetchone()[0]
        elif leixing == '多条':
            # 反馈多条
            cursor.execute(in_sql)
            count = cursor.fetchall()
        elif leixing == '编辑':
            count = cursor.execute(in_sql)
            conn.commit()
        # 关闭光标对象
        cursor.close()
        # 关闭数据库连接
        conn.close()
        # 反馈
        return count

    # 时间计算
    def get_current_time(self):
        ct = time.time()
        local_time = time.localtime(ct)
        data_head = time.strftime("%Y%m%d%H%M%S", local_time)
        data_secs = abs(ct - round(ct)) * 1000
        time_stamp = "%s%03d" % (data_head, data_secs)
        return time_stamp

    #日志留存
    def log_record(self):
        in_time = self.get_current_time()[0:14]
        ziduan1 = str(self.data['ziduan1']).replace("'","^")
        sql = "insert into shzc.yytowz_service_title (leixing,ziduan1,ziduan2,ziduan3,ip_id,post_id,in_time,mac,hostname,ip) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') " % (
            self.data['leixing'], ziduan1, self.data['ziduan2'], self.data['ziduan3'], str(self.client_address[0]),str(self.client_address[1]),in_time, self.data['mac'], self.data['hostname'], self.data['ip'])
        num = self.mysql_execute(sql,'编辑')
        #print('num',num)
        return num

    # shzc.yytowz_service_user 获取文件下载ip范围
    def service_user(self):
        sql = "select distinct a.ip_id from shzc.yytowz_service_user a where STATUS='1' "
        num = self.mysql_execute(sql, '多条')
        ip_list = []
        for ip in num:
            for ip_id in ip:
                ip_list.append(ip_id)
        return ip_list

# 时间计算
def get_current_time(input_date='0'):
    # 如果时间传入为空
    if input_date == '0':
        ct = time.time() # - 24 * 60 * 60  #如果是取昨天日期是减数值
        local_time = time.localtime(ct)
        data_head = time.strftime("%Y%m%d%H%M%S", local_time)
        data_secs = abs(ct - round(ct)) * 1000
        time_stamp = "%s%03d" % (data_head, data_secs)
    else:
        time_stamp = input_date + '120000001'
    return time_stamp

def file_transfer(user,file_name):
    #获取月份
    in_month = get_current_time()[0:6]
    # 文件传递给服务器
    file_path = file_name
    file_statr = user.file_up(file_path, in_month)
    # 如果执行结果不成功，再次执行一次，保底
    if file_statr['ziduan3'] != '传递成功':
        file_statr = file_transfer(user,file_name)
        return file_statr
    else:
        return file_statr

#这里用作程序预备，目前建立必要的文件夹
def server_init():
    # 程序执行前先确认 ./file_server/ 是否存在，不存在新建
    file_path_state1 = os.path.exists('./file_server')
    if file_path_state1 == False:os.mkdir('./file_server')
    file_path_state1 = os.path.exists('./file_server/file_out')
    if file_path_state1 == False: os.mkdir('./file_server/file_out')

#文件数据生成，分mac与时间，不然没法同步下载
def Oracle_download(mac,in_time,sql='0'):
    # 结果数据生成表格准备发送
    dsn = "134.80.200.216/pdbzbjs1"
    conn = cx_Oracle.connect(user="zbweb", password="zibo_533_03", dsn=dsn, encoding="UTF-8")
    df = pd.read_sql("""%s""" % sql, con=conn)
    df.to_excel("./file_server/file_out/"+mac+"_"+in_time+"_结果下载.xlsx", index=False)
    # 文件大小
    file_path = './file_server/file_out/'+mac+'_'+in_time+'_结果下载.xlsx'
    file_name = file_path.split('/')[-1]
    file_size = os.path.getsize(file_path)
    #返回根据语句处理结果与
    return "结果下载",file_path,file_name,file_size

#获取表格数据 #.replace(" ","").replace("[","").replace("]","").replace("'","").split(",")
def excel_type(path):
    excel_nr ={}
    wb = openpyxl.load_workbook(path)
    print('sheetnames',wb.sheetnames)
    ws = wb[wb.sheetnames[0]]
    sheet_name = wb.sheetnames[0]
    minr = ws.min_row
    minc = ws.min_column
    maxr = ws.max_row
    maxc = ws.max_column
    # 数据库内容
    print(minr, minc, maxr, maxc)
    ws_type = {'hang': maxr, 'lie': maxc}
    rngs = ws.iter_rows(min_row=minr, min_col=minc, max_row=maxr, max_col=maxc)
    row_cs = 0
    for row in rngs:
        #标题做表头
        if row_cs == 0:
            value = [c.value for c in row]
            # print(len(value))
            row_bt = []
            for valus_id in range(len(value)):
                try:
                    value_nr = str(value[valus_id]).replace('-','_').replace('(','').replace(')','').replace('/','').replace('（','').replace('）','').replace('、','').replace('+','').replace('，','')
                except:value_nr ='_'
                row_bt.append('y' + str(value_nr +'_'+ str(valus_id)))
            excel_nr[row_cs] = row_bt
        elif row_cs == 1:
            value = [c.value for c in row]
            # print(len(value))
            row_nr = []
            for valus_id in range(len(value)):
                row_nr.append(str(value[valus_id]))
            excel_nr[row_cs] = row_nr
        row_cs += 1
    try:
        excel_nr.pop(None)
    except:
        pass
    return ws_type, excel_nr,sheet_name

#数据库登录
def mysql_execute(in_sql,leixing):
    # 登录数据库
    #conn = pymysql.connect(host='127.0.0.1', port=3306, user='szc', password='szcNSP850219', database='szc_sql',charset='utf8')
    dsn = "134.80.200.216/pdbzbjs1"
    try:
        conn = cx_Oracle.connect(user="zbweb", password="zibo_533_03", dsn=dsn, encoding="UTF-8")
    except:
        time.sleep(10)
        conn = cx_Oracle.connect(user="zbweb", password="zibo_533_03", dsn=dsn, encoding="UTF-8")

    # 得到一个可以执行SQL语句的光标对象
    cursor = conn.cursor()
    # 数据库执行导入的语句
    if leixing == '数量':
        # 反馈数量
        count = cursor.execute(in_sql)
    elif leixing == '单条':
        # 反馈单条
        cursor.execute(in_sql)
        count = cursor.fetchone()[0]
    elif leixing == '多条':
        # 反馈多条
        cursor.execute(in_sql)
        count = cursor.fetchall()
    elif leixing == '编辑':
        count = cursor.execute(in_sql)
        conn.commit()
    # 关闭光标对象
    cursor.close()
    # 关闭数据库连接
    conn.close()
    # 反馈
    return count


def excel_in_new(table_name,ws_type,excel_nr,path,sheet_name):

    print('结果',table_name, ws_type)
    # 先删除这个表
    sql = "drop table zhyw." + table_name
    try:mysql_execute(sql, '编辑')
    except:pass
    # 创建临时表
    sql = "create table zhyw." + table_name + " ("
    for i in range(ws_type['lie']):
        # print('i',i,excel_nr[0][i])
        sql = sql + excel_nr[0][i] + " varchar2(3999),"
    sql = sql[:-1] + ")"
    print('sql:', sql)
    try:mysql_execute(sql, '编辑')
    except:pass


    dsn = "134.80.200.216/pdbzbjs1";
    conn = cx_Oracle.connect(user="zbweb", password="zibo_533_03", dsn=dsn, encoding="UTF-8");
    cursor = conn.cursor();
    data_import = pd.read_excel(path, sheet_name=sheet_name, header=0, dtype='str', keep_default_na=False);

    #print('data_import111111111111',len(data_import),data_import[0:1])

    data_import = data_import.replace('\n', '', regex=True)
    data_import = data_import.replace('\r', '', regex=True)
    data_import = data_import.replace('"', '', regex=True)
    data_import = data_import.replace("'", "", regex=True)

    data_import = data_import.where(data_import.notnull(), None);

    data_import = data_import.replace('\n', '', regex=True)
    data_import = data_import.replace('\r', '', regex=True)
    data_import = data_import.replace('"', '', regex=True)
    data_import = data_import.replace("'", "", regex=True)

    #print('data_import222222222222', len(data_import),data_import[0:1])

    df = data_import.apply(lambda x: tuple(x), axis=1).values.tolist()  # dataframe 需要改改为包含元组的列表才可以导入数据库

    deal_export = "insert into zhyw." + table_name + " values("
    for i in range(ws_type['lie']): deal_export = deal_export + ":" + str(i + 1) + ","
    deal_export = deal_export[:-1] + ")"

    #print('data_import333333333333', len(df),df[0:1])
    xh_num = 100
    num_cs = len(df) // xh_num + 1
    #数据量过大时一次只导入1000
    for i in range(num_cs):
        num_min = i * xh_num
        num_max = (i + 1) * xh_num
        df_new = df[num_min:num_max]
        #print('数据量过大时一次只导入:', num_min, num_max, len(df_new))
        cursor.prepare(deal_export);  # 编译sql
        #print('deal_export', deal_export)
        try:cursor.executemany(None, df_new)
        except:
            for i in range(num_min, num_max):
                df_new = df[i:i+1]
                try:cursor.executemany(None, df_new)
                except:print('数据导入异常:', num_min, num_max,'*********',i,df[i:i+1])
    conn.commit();
    # 关闭光标对象
    cursor.close()
    # 关闭数据库连接
    conn.close()
    return "zhyw."+table_name


if __name__ == '__main__':
    #服务器文件夹准备
    server_init()
    #服务器开始
    s = socketserver.ThreadingTCPServer(('134.35.10.10', 8967), MyServer)
    #类似实现连接循环
    s.serve_forever()

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
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439

客户端与之前无变化

import socket
import json
import time
import os
import tqdm
import uuid #获取系统mac

class My_Main():

    def __init__(self):
        # 程序执行前先确认 ./file_server/file_work_order/ 是否存在，不存在新建
        file_path_state1 = os.path.exists('./file_main')
        if file_path_state1 == False: os.mkdir('./file_main')
        # 定义服务端地址
        self.ip_num, self.port_num = '134.35.10.10', 8967
        # mac
        self.mac = uuid.UUID(int=uuid.getnode()).hex[-12:]
        # 获取主机名
        self.hostname = socket.gethostname()
        # 获取IP
        self.ip = socket.gethostbyname(self.hostname)

    #发起访问调用模块
    def socket_dlgc(self,leixing, name_text, pass_text):
        # 获取14位长度时间，年月日时分秒的
        self.in_time = get_current_time()[0:14]
        tcp_client = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        tcp_client.connect((self.ip_num, self.port_num))
        if None == name_text:print('与服务器断开连接')
        #发送
        msg = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s","mac":"%s","hostname":"%s","ip":"%s" }' % (
            str(leixing), str(name_text), str(pass_text), self.in_time,self.mac, self.hostname, self.ip)
        tcp_client.send(msg.encode("utf-8"))  # 说话    #
        data = tcp_client.recv(102400)  # 听话
        js_data = json.loads(data.decode('utf-8'))
        tcp_client.close()
        return js_data

    # 文件下载函数
    def file_download(self,sql):
        # 获取14位长度时间，年月日时分秒的
        self.in_time = get_current_time()[0:14]
        # 套接字是对访问的ip地址和端口反馈，需要从开始定好
        tcp_client = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        tcp_client.connect((self.ip_num, self.port_num))
        # 申请数据下载
        chuandi_tup = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%d","ziduan3":"%s","mac":"%s","hostname":"%s","ip":"%s" }' % (
            '文件下载', sql, 1, self.in_time+'.xlsx',self.mac, self.hostname, self.ip)
        tcp_client.send(chuandi_tup.encode("utf-8"))  # 说话
        self.data = tcp_client.recv(102400)  # 听话
        js_data = json.loads(self.data.decode('utf-8'))
        #print(js_data, type(js_data))
        if js_data['ziduan1'] not in ('', None):
            file_size = int(js_data['ziduan2'])
            #print(file_size)
            filename = js_data['ziduan3']
            rece_size = 0
            recv_data = tcp_client.recv(4096)
            if recv_data:  # 如果获取数据不为空
                try:
                    with open('./file_main/' + filename, "wb")as f:
                        f.write(recv_data)
                        while rece_size < file_size:
                            recv_data = tcp_client.recv(4096)
                            f.write(recv_data)
                            rece_size += len(self.data)

                    js_data = {'leixing': '文件下载', 'ziduan1': js_data['ziduan3'], 'ziduan2': file_size, 'ziduan3': '传递成功'}
                except:
                    js_data = {'leixing': '文件下载', 'ziduan1': js_data['ziduan3'], 'ziduan2': file_size, 'ziduan3': '传递失败'}
            # 关闭套接字
            tcp_client.close()
        return js_data

    #客户端文件上传，fujian_label 文件全路径
    def file_up(self,fujian_label):
        # 获取14位长度时间，年月日时分秒的
        self.in_time = get_current_time()[0:14]
        # 文件传输的缓冲区
        BUFFER_SIZE = 4096
        # 创建连接
        s = socket.socket()
        s.connect((self.ip_num, self.port_num))
        # 传递文件到指定目录下
        filename = fujian_label.replace('/', '//')
        # 文件大小
        file_size = os.path.getsize(filename)

        chuandi_tup = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%d","ziduan3":"%s","mac":"%s","hostname":"%s","ip":"%s" }' % (
            '文件传递', filename, file_size, self.in_time,self.mac, self.hostname, self.ip )
        s.send(chuandi_tup.encode())
        # 文件传输
        progress = tqdm.tqdm(range(file_size), f"发送{filename}", unit="B", unit_divisor=BUFFER_SIZE)
        with open(filename, "rb") as f:
            for _ in progress:
                # 读取文件
                bytes_read = f.read(BUFFER_SIZE)
                if not bytes_read:
                    break
                try:
                    # sendall确保及时网络忙碌的时候，数据仍然可以传输
                    s.sendall(bytes_read)
                    progress.update(len(bytes_read))
                except:
                    js_data = {'leixing': '文件传递', 'ziduan1': self.ip_num, 'ziduan2': self.port_num, 'ziduan3': '传递失败'}
                    break
        #文件传递完后，看看是否有反馈，有的话函数返回
        try:
            data = s.recv(102400)  # 听话
            js_data = json.loads(data.decode('utf-8'))
        except:
            js_data = {'leixing': '文件传递', 'ziduan1': self.ip_num, 'ziduan2': self.port_num, 'ziduan3': '传递失败'}
        # 关闭资源
        s.close()
        return js_data

# 时间计算
def get_current_time(input_date='0'):
    # 如果时间传入为空
    if input_date == '0':
        ct = time.time() # - 24 * 60 * 60  #如果是取昨天日期是减数值
        local_time = time.localtime(ct)
        data_head = time.strftime("%Y%m%d%H%M%S", local_time)
        data_secs = abs(ct - round(ct)) * 1000
        time_stamp = "%s%03d" % (data_head, data_secs)
    else:
        time_stamp = input_date + '120000001'
    return time_stamp

#文件传递给服务器
def file_transfer(user,file_name,num=0):
    # 防止有语法错误等原因导致死循环，限制最多处理4次
    if num < 5:
        # 文件传递给服务器
        try:
            file_statr = user.file_up(file_name)
            num += 1
        except:
            file_statr = {'leixing': '文件传递', 'ziduan1': file_name, 'ziduan2': '0', 'ziduan3': '传递失败'}
            num += 1
        # 如果执行结果不成功，再次执行一次，保底
        if file_statr['ziduan3'] != '传递成功':
            file_statr = file_transfer(user,file_name,num)
            return file_statr
        else:
            return file_statr
    else:
        file_statr = {'leixing': '文件传递', 'ziduan1': file_name, 'ziduan2': '0', 'ziduan3': '传递失败'}
        return file_statr

#文件传递给服务器
def file_gain(user,sql,num=0):
    #防止有语法错误等原因导致死循环，限制最多处理4次
    if num < 5:
        # 文件传递给服务器
        try:
            file_statr = user.file_download(sql)
            num += 1
        except:
            file_statr = {'leixing': '文件下载', 'ziduan1': sql, 'ziduan2': '0', 'ziduan3': '传递失败'}
            num += 1
        # 如果执行结果不成功，再次执行一次，保底
        if file_statr['ziduan3'] != '传递成功':
            file_statr = file_gain(user,sql,num)
            return file_statr
        else:
            return file_statr
    else:
        file_statr = {'leixing': '文件下载', 'ziduan1': sql, 'ziduan2': '0', 'ziduan3': '传递失败'}
        return file_statr

def use_show():
    print('请您选择需要处理的类型(请输入选择的编码)：')
    print('类型：发起访问 编码：1')
    print('类型：文件传递 编码：2')
    print('类型：文件下载 编码：3')

def use_choice(use_input):
    if use_input == '1':use_type = '发起访问'
    elif use_input == '2':use_type = '文件传递'
    elif use_input == '3':use_type = '文件下载'
    else:use_type = '类型不详'
    return use_type

def use_decision(user,use_input):
    if use_input == '1':
        name_text = input('请您输入需要发起的标题：')
        pass_text = input('请您输入需要发起的内容：')
        socket_dlgc_statr = user.socket_dlgc('发起访问', name_text, pass_text)
        print('系统反馈：', socket_dlgc_statr)

    elif use_input == '2':
        wav_write = input('请您输入需要发送文件名：')
        transfer_statr = file_transfer(user, wav_write)
        print('系统反馈：',transfer_statr)

    elif use_input == '3':
        sql = sql_out()
        gain_statr = file_gain(user, sql)
        print('系统反馈：', gain_statr)
    else:
        use_type = '类型不详'
    pass

#获取sql语句处理
def sql_out():
    # 语句存储
    sql_text = ''
    # 循环执行
    while True:
        sql = input('请您输入需要发送的语句：').replace("'", "^")
        try:
            sql_out = sql.index(';')
            #print('sql_out', sql_out, type(sql_out))
        except:
            sql_out = -1
            #print('sql_out', sql_out, type(sql_out))
        if sql_out == -1:
            sql_text = sql_text + ' ' + sql
        else:
            sql_text = sql_text + ' ' + sql
            break
    return sql_text.replace(";", " ")

if __name__ == '__main__':

    # 加载类
    user = My_Main()

    # 循环执行
    while True:
        use_show()
        use_input = input('请您输入选择的编码：')
        use_type = use_choice(use_input)

        print('您选择需要处理的类型：',use_type)

        #类型不详就断开
        if use_type=='类型不详':break

        use_decision(user, use_input)

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
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243

增加数据导入后的数据处于与展现过程

create or replace procedure biller533.shzc_zhouw_kefu_tsxqtj_show(
p_userid varchar2,p_ip_path  varchar2,p_type  varchar2,
p_cursor in out Results.ref_cursor_type) is
v_sql      varchar2(18000);
SQL_STRING  VARCHAR2(30000);
 v_qx       varchar2(10);
 v_ip_path varchar2(30);---登录人所在的ip地址去掉。

begin
  
v_ip_path :=replace(p_ip_path,'.',''); 


zhyw.shc_drop_retable(upper('zhouw_tsxqtj_jdwl_long'),'SHZC');
SQL_STRING:='create table SHZC.zhouw_tsxqtj_jdwl_long as
select '''||p_userid||''' p_userid,sysdate in_time,'''||p_ip_path||'''  p_ip_path,'''||v_ip_path||''' v_ip_path  from dual  ' ;
execute immediate (SQL_STRING);

SQL_STRING:='insert into shzc.zhouw_tsxqtj_jdwl_long_bd
select * from SHZC.zhouw_tsxqtj_jdwl_long a ' ;
execute immediate (SQL_STRING);
commit;

if p_ip_path <>'127.0.0.1' then

--清除原数据


SQL_STRING:='delete zhyw.zhouw_tsxqgd_jdwl_mxsj a
where exists ( select 1 from zhyw.ceshi_tmp_'||v_ip_path||' b where a.Y流水号_1=b.Y流水号_1) ' ;
execute immediate (SQL_STRING);
commit;

--新增数据

SQL_STRING:='insert into zhyw.zhouw_tsxqgd_jdwl_mxsj 
select * from zhyw.ceshi_tmp_'||v_ip_path||' a  ' ;
execute immediate (SQL_STRING);
commit;

      

zhyw.shc_drop_retable(upper('zhouw_tsxqgd_jdwl_mxsja'),'ZHYW');
SQL_STRING:='create table zhyw.zhouw_tsxqgd_jdwl_mxsja as
select b.y地市_0,b.y区县_1,b.y调研请求流水_2,b.y评测场景ccsbizcode_4,
nvl(case when b.y评测场景ccsbizcode_4 in (''投诉处理-10086热线'',''投诉处理-互联网渠道'',''其他投诉场景'') then ''集团'' end,''不详'') 集团,
nvl(case when b.y评测场景ccsbizcode_4 in (''省内评测-10086热线'') then ''省内'' end,''不详'') 省内,
nvl(case when b.y评测场景ccsbizcode_4 in (''省内评测-10086热线'',''投诉处理-10086热线'',''投诉处理-互联网渠道'',''其他投诉场景'') then ''融合'' end,''不详'') 融合,  
b.y评测手机号码_5,b.y省内工单流水_6,b.y热点大类_8,b.y投诉内容_9,
b.y处理工作组_12,b.y地市处理工号_11,b.y工单结办满意度满意不满意_15,b.y工单处理时长_16,b.y满意度打分_73,b.y满意度_74,b.ynone_75,b.ynone_83,b.ynone_90,
a.y查证__受理部门_24,case 
when nvl(a.y查证__受理部门_24,''0'') like ''%沂源%'' then ''沂源''
when nvl(a.y查证__受理部门_24,''0'') like ''%博山%'' then ''博山''
when nvl(a.y查证__受理部门_24,''0'') like ''%淄川%'' then ''淄川''
when nvl(a.y查证__受理部门_24,''0'') like ''%周村%'' then ''周村''
when nvl(a.y查证__受理部门_24,''0'') like ''%临淄%'' then ''临淄''
when nvl(a.y查证__受理部门_24,''0'') like ''%桓台%'' then ''桓台''
when nvl(a.y查证__受理部门_24,''0'') like ''%高青%'' then ''高青''
when nvl(a.y查证__受理部门_24,''0'') like ''%张店%'' then ''张店''
when nvl(a.y查证__受理部门_24,''0'') like ''%高新%'' then ''高新''
when nvl(a.y查证__受理部门_24,''0'') like ''%经开%'' then ''经开''
when nvl(a.y查证__受理部门_24,''0'') like ''%南定%'' then ''经开'' end 区县,
nvl(substr(a.y答复__受理工号_27,1,8),''0'') y答复__受理工号_27,
nvl(substr(a.y派单__受理工号_19,1,8),''0'') y派单__受理工号_19
from  zhyw.zhouw_10086gd_jdwl_mxbd b,
zhyw.zhouw_tsxqgd_jdwl_mxsj a
where b.y省内工单流水_6 =a.y流水号_1' ;
execute immediate (SQL_STRING);


----区县汇总



zhyw.shc_drop_retable(upper('zhouw_tsxqgd_jdwl_hzjg'),'ZHYW');
SQL_STRING:='create table zhyw.zhouw_tsxqgd_jdwl_hzjg as
select a.区县,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q1,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q2,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q3,
round(sum(nvl(case when a.集团=''集团'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团3个问题平均值,
round((round(sum(nvl(case when a.集团=''集团'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 集团满意度, 
------------------
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q1,
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q2,
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q3,
round(sum(nvl(case when a.省内=''省内'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内3个问题平均值,
round((round(sum(nvl(case when a.省内=''省内'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 省内满意度, 
------------------
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q1,
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q2,
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q3,
round(sum(nvl(case when a.融合=''融合'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合3个问题平均值,
round((round(sum(nvl(case when a.融合=''融合'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 融合满意度
from zhyw.zhouw_tsxqgd_jdwl_mxsja a
where a.区县 is not null
group by a.区县
union all
select ''全市'' 区县,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q1,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q2,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q3,
round(sum(nvl(case when a.集团=''集团'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团3个问题平均值,
round((round(sum(nvl(case when a.集团=''集团'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 集团满意度, 
------------------
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q1,
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q2,
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q3,
round(sum(nvl(case when a.省内=''省内'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内3个问题平均值,
round((round(sum(nvl(case when a.省内=''省内'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 省内满意度, 
------------------
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q1,
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q2,
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q3,
round(sum(nvl(case when a.融合=''融合'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合3个问题平均值,
round((round(sum(nvl(case when a.融合=''融合'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 融合满意度
from zhyw.zhouw_tsxqgd_jdwl_mxsja a
where a.区县 is not null' ;
execute immediate (SQL_STRING);



zhyw.shc_drop_retable(upper('zhouw_tsxqgd_jdwl_hzjga'),'ZHYW');
SQL_STRING:='create table zhyw.zhouw_tsxqgd_jdwl_hzjga as
select b.county_id,a.* from zhyw.zhouw_tsxqgd_jdwl_hzjg a,
(select * from zhyw.rpt_county b where b.county_id<>''0'') b
 where a.区县=b.name(+) ' ;
execute immediate (SQL_STRING);
 

 
 zhyw.shc_drop_retable(upper('zhouw_tsxqgd_jdwl_tsymx'),'ZHYW');
SQL_STRING:='create table zhyw.zhouw_tsxqgd_jdwl_tsymx as
 select a.*,b.opername 答复_受理,d.opername 派单_受理,nvl(b.opername,d.opername) 投诉处理员
from zhyw.zhouw_tsxqgd_jdwl_mxsja a,
tbcs.operator@bcv b,
tbcs.operator@bcv d
where (a.y答复__受理工号_27 in (''c0000130'', ''c200000a'', ''c200000e'', ''c2300008'', ''c2300012'', ''c2300018'', ''c2300019'', ''c2300020'', ''c2300023'',
 ''c2300024'', ''c2300028'', ''c1995935'', ''c200000d'', ''c200000f'', ''c1995958'', ''c1995953'', ''c1995962'', ''c1995957'', ''c230000b'', ''c1995948'', ''c1995951'', ''c230000c'')
 or a.y派单__受理工号_19 in (''c0000130'', ''c200000a'', ''c200000e'', ''c2300008'', ''c2300012'', ''c2300018'', ''c2300019'', ''c2300020'', ''c2300023'',
 ''c2300024'', ''c2300028'', ''c1995935'', ''c200000d'', ''c200000f'', ''c1995958'', ''c1995953'', ''c1995962'', ''c1995957'', ''c230000b'', ''c1995948'', ''c1995951'', ''c230000c''))
 and a.y答复__受理工号_27=b.operid(+)
 and a.y派单__受理工号_19=d.operid(+)' ;
execute immediate (SQL_STRING);


 zhyw.shc_drop_retable(upper('zhouw_tsxqgd_jdwl_tsyjg'),'ZHYW');
SQL_STRING:='create table zhyw.zhouw_tsxqgd_jdwl_tsyjg as
 select a.投诉处理员,count(a.y省内工单流水_6) 工单量,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q1,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q2,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q3,
round(sum(nvl(case when a.集团=''集团'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团3个问题平均值,
round((round(sum(nvl(case when a.集团=''集团'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 集团满意度, 
------------------
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q1,
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q2,
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q3,
round(sum(nvl(case when a.省内=''省内'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内3个问题平均值,
round((round(sum(nvl(case when a.省内=''省内'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 省内满意度, 
------------------
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q1,
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q2,
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q3,
round(sum(nvl(case when a.融合=''融合'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合3个问题平均值,
round((round(sum(nvl(case when a.融合=''融合'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 融合满意度
 from zhyw.zhouw_tsxqgd_jdwl_tsymx a
 group by a.投诉处理员
 union all
  select ''合计''投诉处理员,count(a.y省内工单流水_6) 工单量,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q1,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q2,
round(sum(nvl(case when a.集团=''集团'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团平均值项Q3,
round(sum(nvl(case when a.集团=''集团'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2) 集团3个问题平均值,
round((round(sum(nvl(case when a.集团=''集团'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.集团=''集团'' then a.y省内工单流水_6 end),0,1,count(case when a.集团=''集团'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 集团满意度, 
------------------
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q1,
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q2,
round(sum(nvl(case when a.省内=''省内'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内平均值项Q3,
round(sum(nvl(case when a.省内=''省内'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2) 省内3个问题平均值,
round((round(sum(nvl(case when a.省内=''省内'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.省内=''省内'' then a.y省内工单流水_6 end),0,1,count(case when a.省内=''省内'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 省内满意度, 
------------------
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_75) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q1,
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_83) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q2,
round(sum(nvl(case when a.融合=''融合'' then to_number(a.ynone_90) end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合平均值项Q3,
round(sum(nvl(case when a.融合=''融合'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2) 融合3个问题平均值,
round((round(sum(nvl(case when a.融合=''融合'' then (to_number(a.ynone_90)+to_number(a.ynone_83)+to_number(a.ynone_75))/3 end,0))/
decode(count(case when a.融合=''融合'' then a.y省内工单流水_6 end),0,1,count(case when a.融合=''融合'' then a.y省内工单流水_6 end)),2)-1)/9*100,2) 融合满意度
 from zhyw.zhouw_tsxqgd_jdwl_tsymx a' ;
execute immediate (SQL_STRING);
      


      
      SQL_STRING:='insert into zhyw.zhouw_tsxqgd_jdwl_hzjg_bd  
       select '''||p_userid||''' user_id,'''||p_ip_path||''' ip_path,sysdate in_time,a.* from zhyw.zhouw_tsxqgd_jdwl_hzjga a  ';
      EXECUTE IMMEDIATE (SQL_STRING);
      commit;
      
      SQL_STRING:='insert into zhyw.zhouw_tsxqgd_jdwl_tsyjg_bd  
       select '''||p_userid||''' user_id,'''||p_ip_path||''' ip_path,sysdate in_time,a.* from zhyw.zhouw_tsxqgd_jdwl_tsyjg a  ';
      EXECUTE IMMEDIATE (SQL_STRING);
      commit;
      
      
       SQL_STRING:='update zhyw.zhouw_tsxqgd_jdwl_tsyjg_bd  a set a.集团满意度=0  where a.集团满意度<0 ';
      EXECUTE IMMEDIATE (SQL_STRING);
      commit;
      SQL_STRING:='update zhyw.zhouw_tsxqgd_jdwl_tsyjg_bd  a set a.省内满意度=0  where a.省内满意度<0 ';
      EXECUTE IMMEDIATE (SQL_STRING);
      commit;
      SQL_STRING:='update zhyw.zhouw_tsxqgd_jdwl_tsyjg_bd  a set a.融合满意度=0  where a.融合满意度<0 ';
      EXECUTE IMMEDIATE (SQL_STRING);
      commit;
      
      SQL_STRING:='update zhyw.zhouw_tsxqgd_jdwl_hzjg_bd  a set a.集团满意度=0  where a.集团满意度<0 ';
      EXECUTE IMMEDIATE (SQL_STRING);
      commit;
      SQL_STRING:='update zhyw.zhouw_tsxqgd_jdwl_hzjg_bd  a set a.省内满意度=0  where a.省内满意度<0 ';
      EXECUTE IMMEDIATE (SQL_STRING);
      commit;
      SQL_STRING:='update zhyw.zhouw_tsxqgd_jdwl_hzjg_bd  a set a.融合满意度=0  where a.融合满意度<0 ';
      EXECUTE IMMEDIATE (SQL_STRING);
      commit;
      
      
      
      
zhyw.shc_drop_retable(upper('zhouw_tsxqgd_jdwl_qxmx'),'ZHYW');
SQL_STRING:='create table zhyw.zhouw_tsxqgd_jdwl_qxmx as
select * from zhyw.zhouw_tsxqgd_jdwl_mxsja a where a.区县 is not null 
or a.y答复__受理工号_27 in (''c0000130'', ''c200000a'', ''c200000e'', ''c2300008'', ''c2300012'', ''c2300018'', ''c2300019'', ''c2300020'', ''c2300023'',
 ''c2300024'', ''c2300028'', ''c1995935'', ''c200000d'', ''c200000f'', ''c1995958'', ''c1995953'', ''c1995962'', ''c1995957'', ''c230000b'', ''c1995948'',
  ''c1995951'', ''c230000c'')
or a.y派单__受理工号_19 in (''c0000130'', ''c200000a'', ''c200000e'', ''c2300008'', ''c2300012'', ''c2300018'', ''c2300019'', ''c2300020'', ''c2300023'',
 ''c2300024'', ''c2300028'', ''c1995935'', ''c200000d'', ''c200000f'', ''c1995958'', ''c1995953'', ''c1995962'', ''c1995957'', ''c230000b'', ''c1995948'',
  ''c1995951'', ''c230000c'') ';
      EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper('zhouw_tsxqgd_jdwl_qxmxa'),'ZHYW');
SQL_STRING:='create table zhyw.zhouw_tsxqgd_jdwl_qxmxa as
 select a.y地市_0,
        a.y区县_1,
        a.y调研请求流水_2||''z'' y调研请求流水_2,
        a.y评测场景ccsbizcode_4,
        a.集团,
        a.省内,
        a.融合,
        a.y评测手机号码_5,
        a.y省内工单流水_6||''z'' y省内工单流水_6,
        a.y热点大类_8,
        a.y投诉内容_9,
        a.y处理工作组_12,
        a.y地市处理工号_11,
        a.y工单结办满意度满意不满意_15,
        a.y工单处理时长_16,
        a.y满意度打分_73,
        a.y满意度_74,
        a.ynone_75,
        a.ynone_83,
        a.ynone_90,
        a.y查证__受理部门_24,
        a.区县,
        a.y答复__受理工号_27,
        a.y派单__受理工号_19,nvl(b.opername,d.opername) 投诉处理员
from zhyw.zhouw_tsxqgd_jdwl_qxmx a,
tbcs.operator@bcv b,
tbcs.operator@bcv d
where a.y答复__受理工号_27=b.operid(+)
 and a.y派单__受理工号_19=d.operid(+) ';
      EXECUTE IMMEDIATE (SQL_STRING);
      
      
      

      if p_type ='区县汇总' then

          v_sql:='select ''USER_ID'',''IP_PATH'',''IN_TIME'',''COUNTY_ID'',''区县'',''集团平均值项Q1'',''集团平均值项Q2'',
          ''集团平均值项Q3'',''集团3个问题平均值'',''集团满意度'',''省内平均值项Q1'',''省内平均值项Q2'',''省内平均值项Q3'',
          ''省内3个问题平均值'',''省内满意度'',''融合平均值项Q1'',''融合平均值项Q2'',''融合平均值项Q3'',''融合3个问题平均值'',
          ''融合满意度'' from dual union all 
          select * from (
          select '''||p_userid||''','''||p_ip_path||''',to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),COUNTY_ID,区县,to_char(集团平均值项Q1),to_char(集团平均值项Q2),
          to_char(集团平均值项Q3),to_char(集团3个问题平均值),to_char(集团满意度),to_char(省内平均值项Q1),to_char(省内平均值项Q2),to_char(省内平均值项Q3),
          to_char(省内3个问题平均值),to_char(省内满意度),to_char(融合平均值项Q1),to_char(融合平均值项Q2),to_char(融合平均值项Q3),to_char(融合3个问题平均值),
          to_char(融合满意度) from zhyw.zhouw_tsxqgd_jdwl_hzjga a 
          order by county_id ) a  ';
          

           
      elsif p_type ='投诉处理员' then
          v_sql:='select ''USER_ID'',''IP_PATH'',''IN_TIME'',''投诉处理员'',''工单量'',''集团平均值项Q1'',''集团平均值项Q2'',''集团平均值项Q3'',''集团3个问题平均值'',
          ''集团满意度'',''省内平均值项Q1'',''省内平均值项Q2'',''省内平均值项Q3'',''省内3个问题平均值'',''省内满意度'',''融合平均值项Q1'',''融合平均值项Q2'',
          ''融合平均值项Q3'',''融合3个问题平均值'',''融合满意度'' from dual union all 
           select * from (
           select '''||p_userid||''','''||p_ip_path||''',to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),投诉处理员,to_char(工单量),to_char(集团平均值项Q1),to_char(集团平均值项Q2),
          to_char(集团平均值项Q3),to_char(集团3个问题平均值),to_char(集团满意度),to_char(省内平均值项Q1),to_char(省内平均值项Q2),to_char(省内平均值项Q3),
          to_char(省内3个问题平均值),to_char(省内满意度),to_char(融合平均值项Q1),to_char(融合平均值项Q2),to_char(融合平均值项Q3),to_char(融合3个问题平均值),
          to_char(融合满意度) from zhyw.zhouw_tsxqgd_jdwl_tsyjg a ) a  ';
          
          
      elsif p_type ='明细提取' then
        
      v_sql:='select ''Y地市_0'',''Y区县_1'',''Y调研请求流水_2'',''Y评测场景CCSBIZCODE_4'',''集团'',''省内'',''融合'',''Y评测手机号码_5'',''Y省内工单流水_6'',
      ''Y热点大类_8'',''Y投诉内容_9'',''Y处理工作组_12'',''Y地市处理工号_11'',''Y工单结办满意度满意不满意_15'',''Y工单处理时长_16'',''Y满意度打分_73'',
      ''Y满意度_74'',''YNONE_75'',''YNONE_83'',''YNONE_90'',''Y查证__受理部门_24'',''区县'',''Y答复__受理工号_27'',''Y派单__受理工号_19'',''投诉处理员''
       from dual union all 
       select * from (
       select Y地市_0,Y区县_1,Y调研请求流水_2,Y评测场景CCSBIZCODE_4,集团,省内,融合,replace(Y评测手机号码_5,''1'',''l''),Y省内工单流水_6,Y热点大类_8,replace(Y投诉内容_9,''1'',''l''),
       Y处理工作组_12,Y地市处理工号_11,Y工单结办满意度满意不满意_15,Y工单处理时长_16,Y满意度打分_73,Y满意度_74,YNONE_75,YNONE_83,YNONE_90,
       Y查证__受理部门_24,区县,Y答复__受理工号_27,Y派单__受理工号_19,投诉处理员 from zhyw.ZHOUW_TSXQGD_JDWL_QXMXA a ) a  ';
           
      end if;
       
       
       

else
  
      if p_type ='区县汇总' then

          v_sql:='select ''USER_ID'',''IP_PATH'',''IN_TIME'',''COUNTY_ID'',''区县'',''集团平均值项Q1'',''集团平均值项Q2'',
          ''集团平均值项Q3'',''集团3个问题平均值'',''集团满意度'',''省内平均值项Q1'',''省内平均值项Q2'',''省内平均值项Q3'',
          ''省内3个问题平均值'',''省内满意度'',''融合平均值项Q1'',''融合平均值项Q2'',''融合平均值项Q3'',''融合3个问题平均值'',
          ''融合满意度'' 
          from dual union all 
          select * from (
          select USER_ID,IP_PATH,to_char(IN_TIME,''yyyy-mm-dd hh24:mi:ss''),COUNTY_ID,区县,to_char(集团平均值项Q1),to_char(集团平均值项Q2),
          to_char(集团平均值项Q3),to_char(集团3个问题平均值),to_char(集团满意度),to_char(省内平均值项Q1),to_char(省内平均值项Q2),to_char(省内平均值项Q3),
          to_char(省内3个问题平均值),to_char(省内满意度),to_char(融合平均值项Q1),to_char(融合平均值项Q2),to_char(融合平均值项Q3),to_char(融合3个问题平均值),
          to_char(融合满意度) from zhyw.zhouw_tsxqgd_jdwl_hzjg_bd  a
          where user_id = '''||p_userid||'''
          and a.in_time in (select max(a.in_time) from zhyw.zhouw_tsxqgd_jdwl_hzjg_bd  a
          where user_id = '''||p_userid||''')
           order by county_id ) a  ';
          

           
      elsif p_type ='投诉处理员' then
          v_sql:='select ''USER_ID'',''IP_PATH'',''IN_TIME'',''投诉处理员'',''工单量'',''集团平均值项Q1'',''集团平均值项Q2'',''集团平均值项Q3'',''集团3个问题平均值'',
          ''集团满意度'',''省内平均值项Q1'',''省内平均值项Q2'',''省内平均值项Q3'',''省内3个问题平均值'',''省内满意度'',''融合平均值项Q1'',''融合平均值项Q2'',
          ''融合平均值项Q3'',''融合3个问题平均值'',''融合满意度''
           from dual union all 
           select * from (
           select USER_ID,IP_PATH,to_char(IN_TIME,''yyyy-mm-dd hh24:mi:ss''),投诉处理员,to_char(工单量),to_char(集团平均值项Q1),to_char(集团平均值项Q2),
          to_char(集团平均值项Q3),to_char(集团3个问题平均值),to_char(集团满意度),to_char(省内平均值项Q1),to_char(省内平均值项Q2),to_char(省内平均值项Q3),
          to_char(省内3个问题平均值),to_char(省内满意度),to_char(融合平均值项Q1),to_char(融合平均值项Q2),to_char(融合平均值项Q3),to_char(融合3个问题平均值),
          to_char(融合满意度) from zhyw.zhouw_tsxqgd_jdwl_tsyjg_bd  a
           where user_id = '''||p_userid||'''
           and a.in_time in (select max(a.in_time) from zhyw.zhouw_tsxqgd_jdwl_tsyjg_bd  a
           where user_id = '''||p_userid||''') ) a  ';
           
       elsif p_type ='明细提取' then
        
      v_sql:='select ''Y地市_0'',''Y区县_1'',''Y调研请求流水_2'',''Y评测场景CCSBIZCODE_4'',''集团'',''省内'',''融合'',''Y评测手机号码_5'',''Y省内工单流水_6'',
      ''Y热点大类_8'',''Y投诉内容_9'',''Y处理工作组_12'',''Y地市处理工号_11'',''Y工单结办满意度满意不满意_15'',''Y工单处理时长_16'',''Y满意度打分_73'',
      ''Y满意度_74'',''YNONE_75'',''YNONE_83'',''YNONE_90'',''Y查证__受理部门_24'',''区县'',''Y答复__受理工号_27'',''Y派单__受理工号_19'',''投诉处理员''
       from dual union all 
       select * from (
       select Y地市_0,Y区县_1,Y调研请求流水_2,Y评测场景CCSBIZCODE_4,集团,省内,融合,replace(Y评测手机号码_5,''1'',''l''),Y省内工单流水_6,Y热点大类_8,replace(Y投诉内容_9,''1'',''l''),
       Y处理工作组_12,Y地市处理工号_11,Y工单结办满意度满意不满意_15,Y工单处理时长_16,Y满意度打分_73,Y满意度_74,YNONE_75,YNONE_83,YNONE_90,
       Y查证__受理部门_24,区县,Y答复__受理工号_27,Y派单__受理工号_19,投诉处理员 from zhyw.ZHOUW_TSXQGD_JDWL_QXMXA a ) a  ';
           
           
      end if;



end if;

 ------------------------------------------------------------------------------------------------------------------------------
     open  p_cursor for v_sql;
end;

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
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433