# -*- codeing = utf-8 -*-
# @Time : 2021/10/21 11:25
# @Author : 熊蓓乐
# @File :  xls.py
# @Software : PyCharm
import xlrd
import os
import numpy as np
from tkinter import *
from tkinter import scrolledtext

def main():
    window=Tk()
    window.title("老君庙作业区日报管理工具   报错请联系：技术信息室熊蓓乐ymxbl@petrochina.com.cn")
    window.geometry("1100x650")
    lbl1 = Label(window, text="请输入修井日报文件路径：")
    lbl1.grid(column=0, row=0)
    ribao1 = Entry(window, width=30)
    ribao1.grid(column=0, row=1)

    def get_dizhi2(path):
        x_path = []
        y_path = []
        for x in os.listdir(path):
            x = path + "\\" + x
            x_path.append(x)
            for y in os.listdir(x):
                y = x + "\\" + y
                y_path.append(y)
        return y_path

    def select2():
        path=ribao1.get()
        dizhi = get_dizhi2(path)
        for i in range(len(dizhi)):
            data_load2(dizhi[i])

    def data_load2(path_1):
        xls = xlrd.open_workbook(path_1)
        sheet1 = xls.sheet_by_name("Sheet2")
        txt6.insert(INSERT, path_1)
        txt6.insert(INSERT, "\n")
        xiujing_row1, xiujing_row2 = row_val2("修井记录", sheet1)
        c=[]
        save_l2("修井记录", xiujing_row1, xiujing_row2, sheet1, 1,c)

    def save_l2(nam, stt, enp, sheet1, a,c):
        for j in range(stt[0] + a, enp[0]):
            list_shop = []
            b=sheet1.cell(1, 1).value
            if(b==""):
                time=xlrd.xldate_as_tuple(sheet1.cell(1, 0).value, 0)
            else:
                time = xlrd.xldate_as_tuple(b, 0)
            list_shop.append(str(time))
            jinghao = sheet1.row_values(j)[3]
            name_o = sheet1.row_values(j)[4]
            neirong = sheet1.row_values(j)[11]
            jihua=sheet1.row_values(j)[14]
            if ("完井" in neirong):
                list_shop.append(str(jinghao))
                list_shop.append(str(name_o))
                list_shop.append("完井")
                txt1.insert(INSERT, list_shop)
                txt1.insert(INSERT, "\n")
            if ("完成" in neirong):
                list_shop.append(str(jinghao))
                list_shop.append(str(name_o))
                list_shop.append("完井")
                txt1.insert(INSERT, list_shop)
                txt1.insert(INSERT, "\n")
            else:
                list_shop.append(str(jinghao))
                list_shop.append(str(name_o))
                list_shop.append(str(jihua))
                txt2.insert(INSERT, list_shop)
                txt2.insert(INSERT, "\n")

    def row_val2(vals, sheet1):
        stb = []
        end = []
        for i in range(sheet1.nrows):
            if (sheet1.cell(i, 3).value == "井号"):
                stb.append(i)
                for j in range(i, sheet1.nrows):
                    if (sheet1.cell(j, 3).value == ""):
                        end.append(j)
                        break
        return stb, end

    btn = Button(window, text="运行",command=select2)
    btn.grid(column=0, row=2)
    lbl = Label(window, text="修完井记录")
    lbl.grid(column=0, row=3)
    txt1 = scrolledtext.ScrolledText(window, width=50, height=10)
    txt1.grid(column=0, row=4)
    lbl = Label(window, text="修井进度")
    lbl.grid(column=0, row=5)
    txt2 = scrolledtext.ScrolledText(window, width=50, height=10)
    txt2.grid(column=0, row=6)
    lbl1 = Label(window, text="打开文件")
    lbl1.grid(column=0, row=9)
    txt6 = scrolledtext.ScrolledText(window, width=50, height=1)
    txt6.grid(column=0, row=10)

    lbl = Label(window, text="请输入岗位日报文件路径：")
    lbl.grid(column=1, row=0)
    ribao = Entry(window, width=30)
    ribao.grid(column=1, row=1)

    def get_dizhi(path):
        x_path = []
        y_path = []
        for x in os.listdir(path):
            x = path + "\\" + x
            x_path.append(x)
            for y in os.listdir(x):
                y = x + "\\" + y
                y_path.append(y)
        return y_path

    def select():
        path=ribao.get()
        dizhi = get_dizhi(path)
        for i in range(len(dizhi)):
            data_load(dizhi[i])

    def data_load(path_1):
        xls = xlrd.open_workbook(path_1)
        sheet1 = xls.sheet_by_index(0)

        xijingjilu_row1, xijingjilu_row2 = row_val("洗井记录", sheet1)
        caicuo_row1, caicuo_row2 = row_val("采油小措施", sheet1)
        buzhengchang_row1, buzhengchang_row2 = row_val("新增不正常井", sheet1)

        txt7.insert(INSERT, path_1)
        txt7.insert(INSERT, "\n")

        save_l("洗井记录", xijingjilu_row1, xijingjilu_row2, sheet1, 2,1)
        save_l("采油小措施", caicuo_row1, caicuo_row2, sheet1, 2,2)
        save_l("新增不正常井", buzhengchang_row1, buzhengchang_row2, sheet1, 3,3)

    def save_l(nam, stt, enp, sheet1, a,c):
        for j in range(stt[0] + a, enp[0]):
            list_shop = []
            time = list(filter(None, sheet1.row_values(1)))[-1].replace(" ", "")
            time = time.replace("填报日期：", "")
            list_shop.append(str(time))
            list_shop.append(",")
            for n in range(sheet1.ncols):
                H = str(sheet1.row_values(j)[n])
                list_shop.append(H)
                list_shop.append(",")
            if (list_shop[4] == ""):
                break
            else:
                if(c==1):
                    txt4.insert(INSERT, list_shop)
                    txt4.insert(INSERT, "\n")
                if (c==2):
                    txt3.insert(INSERT, list_shop)
                    txt3.insert(INSERT, "\n")
                if (c==3):
                    txt5.insert(INSERT, list_shop)
                    txt5.insert(INSERT, "\n")

    def row_val(vals, sheet1):
        shop = []
        stb = []
        end = []
        for t in range(len(name)):
            if name[t] != vals:
                shop.append(name[t])
        for i in range(sheet1.nrows):
            if (sheet1.cell(i, 0).value == vals):
                stb.append(i)
                for j in range(i, sheet1.nrows):
                    if (sheet1.cell(j, 0).value in shop):
                        end.append(j)
                        break
        return stb, end

    btn = Button(window, text="运行", command=select)
    btn.grid(column=1, row=2)
    lbl = Label(window, text="采油措施记录")
    lbl.grid(column=1, row=3)
    txt3 = scrolledtext.ScrolledText(window, width=50, height=10)
    txt3.grid(column=1, row=4)
    lbl = Label(window, text="洗井记录")
    lbl.grid(column=1, row=5)
    txt4 = scrolledtext.ScrolledText(window, width=50, height=10)
    txt4.grid(column=1, row=6)
    lbl = Label(window, text="不正常井记录")
    lbl.grid(column=1, row=7)
    txt5 = scrolledtext.ScrolledText(window, width=50, height=10)
    txt5.grid(column=1, row=8)
    lbl = Label(window, text="打开文件")
    lbl.grid(column=1, row=9)
    txt7 = scrolledtext.ScrolledText(window, width=50, height=1)
    txt7.grid(column=1, row=10)

    lbl = Label(window, text="请输入井号：")
    lbl.grid(column=2, row=0)
    jinghao = Entry(window, width=30)
    jinghao.grid(column=2, row=1)

    def get_list(jing,path):
        list_shop=[]
        jing=" "+jing+" "
        for i in path:
            if(jing in i):
                list_shop.append(str(i))
                list_shop.append("\n")
        return list_shop

    def list_part():
        txt8.delete(1.0,"end")
        txt9.delete(1.0,"end")
        txt10.delete(1.0,"end")
        txt11.delete(1.0,"end")

        jing = jinghao.get()
        caicuo_list=txt3.get(1.0,"end").split("\n")
        xijing_list=txt4.get(1.0,"end").split("\n")
        buzhnegchang_list=txt5.get(1.0,"end").split("\n")
        xiujing_list=txt1.get(1.0,"end").split("\n")

        caicuo_list2=get_list(jing,caicuo_list)
        xijing_list2=get_list(jing,xijing_list)
        buzhnegchang_list2=get_list(jing,buzhnegchang_list)
        xiujing_list2=get_list(jing,xiujing_list)
        txt8.insert(INSERT, caicuo_list2)
        txt9.insert(INSERT, xijing_list2)
        txt10.insert(INSERT, buzhnegchang_list2)
        txt11.insert(INSERT, xiujing_list2)

    btn = Button(window, text="查找", command=list_part)
    btn.grid(column=2, row=2)
    lbl = Label(window, text="采油措施记录")
    lbl.grid(column=2, row=3)
    txt8 = scrolledtext.ScrolledText(window, width=50, height=10)
    txt8.grid(column=2, row=4)
    lbl = Label(window, text="洗井记录")
    lbl.grid(column=2, row=5)
    txt9 = scrolledtext.ScrolledText(window, width=50, height=10)
    txt9.grid(column=2, row=6)
    lbl = Label(window, text="不正常井记录")
    lbl.grid(column=2, row=7)
    txt10 = scrolledtext.ScrolledText(window, width=50, height=10)
    txt10.grid(column=2, row=8)
    lbl = Label(window, text="修井记录")
    lbl.grid(column=2, row=9)
    txt11 = scrolledtext.ScrolledText(window, width=50, height=1)
    txt11.grid(column=2, row=10)

    window.mainloop()


if __name__ == '__main__':
    name = ["114", "19#岗位采油四队新井（含跨年井）生产日报表（10月2日)", "19#岗位采油四队新井（含跨年井）生产日报表（10月3日)", "19#岗位采油四队新井（含跨年井）生产日报表（10月4日)",
            "19#岗位采油四队新井（含跨年井）生产日报表（10月5日)", "19#岗位采油四队新井（含跨年井）生产日报表（10月6日)", "19#岗位采油四队新井（含跨年井）生产日报表（10月7日)",
            "19#岗位大修井井生产日报表（10月2日）", "19#岗位大修井井生产日报表（10月3日）", "19#岗位大修井井生产日报表（10月4日）", "19#岗位大修井井生产日报表（10月5日）",
            "19#岗位大修井井生产日报表（10月6日）", "19#岗位大修井井生产日报表（10月7日）", "19#岗位维护性措施生产日报表（10月2日）", "19#岗位维护性措施生产日报表（10月3日）",
            "19#岗位维护性措施生产日报表（10月4日）", "19#岗位维护性措施生产日报表（10月5日）", "19#岗位维护性措施生产日报表（10月6日）", "19#岗位维护性措施生产日报表（10月7日）",
            "19#岗位无砂压裂井生产日报表（10月2日）", "19#岗位无砂压裂井生产日报表（10月3日）", "19#岗位无砂压裂井生产日报表（10月4日）", "19#岗位无砂压裂井生产日报表（10月5日）",
            "19#岗位无砂压裂井生产日报表（10月6日）", "19#岗位无砂压裂井生产日报表（10月7日）", "19#岗位压裂井生产日报表（10月2日）", "19#岗位压裂井生产日报表（10月3日）",
            "19#岗位压裂井生产日报表（10月4日）", "19#岗位压裂井生产日报表（10月5日）", "19#岗位压裂井生产日报表（10月6日）", "19#岗位压裂井生产日报表（10月7日）",
            "19#岗位重点井生产日报表（10月2日）", "19#岗位重点井生产日报表（10月3日）", "19#岗位重点井生产日报表（10月4日）", "19#岗位重点井生产日报表（10月5日）",
            "19#岗位重点井生产日报表（10月6日）", "19#岗位重点井生产日报表（10月7日）", "19#岗位自喷/防压井生产日报表（10月2日）", "19#岗位自喷/防压井生产日报表（10月3日）",
            "19#岗位自喷/防压井生产日报表（10月4日）", "19#岗位自喷/防压井生产日报表（10月5日）", "19#岗位自喷/防压井生产日报表（10月6日）", "19#岗位自喷/防压井生产日报表（10月7日）",
            "采油小措施", "单量情况统计", "工程/地质设计完成情况", "合计", "老君庙作业区   19#    岗位油井生产日报", "老君庙作业区  19#    岗位油井生产日报",
            "老君庙作业区  19#  岗位油井生产日报", "其他需要协调问题", "取样记录", "生产动态变化原因", "填报人：", "洗井记录", "新增不正常井", "修井情况统计", "总开井数", "昨日动态"]
    main()