class MyThread(QThread):
    mysignal=pyqtSignal(int)
    sheet=""
    def __init__(self,parent=None):
        QThread.__init__(self,parent)
    def sale(self):
            try:
                conn=getConnection()
                cursor=conn.cursor()
                for r in range(1,self.sheet.nrows):
                    uid=self.sheet.cell(r,0).value
                    dis=self.sheet.cell(r,3).value
                    orient=self.sheet.cell(r,5).value if self.sheet.cell(r,5).value!='' else '-----'
                    kol=self.sheet.cell(r,8).value
                    floor=self.sheet.cell(r,9).value+'/'+self.sheet.cell(r,10).value
                    square=self.sheet.cell(r,11).value
                    street=self.sheet.cell(r,4).value  if self.sheet.cell(r,4).value!='' else '-----'
                    sost=self.sheet.cell(r,13).value if self.sheet.cell(r,13).value!='' else '-----'
                    cost=self.sheet.cell(r,6).value+' '+self.sheet.cell(r,7).value
                    phone=self.sheet.cell(r,17).value
                    name=self.sheet.cell(r,16).value if self.sheet.cell(r,16).value!='' else '-----'
                    long="-----"
                    lat="-----"
                    photo="-----"
                    status=0
                    sql=f'INSERT INTO Rielt_sale (uid,district,orient,room_number,floor,furniture,square,street,condition_of_repair,price,telephon_number,name,longitude,latitude,photo,status) VALUES({uid},"{dis}","{orient}",{kol},"{floor}","{obst}",{float(square)},"{street}",{sost}","{cost}","{phone}","{name}","{long}","{lat}","{photo}","{status}");'
                    self.mysignal.emit(r)
                    print(sql)
                    try:
                        cursor.execute(sql)
                        print(sql)
                        #conn.commit()
                    except Exception as e:
                        print(e)
                        continue
                conn.close()
            except Exception as e:
                er=Error1(e)
                er.show()
    def rent(self):
            try:
                    conn=getConnection()
                    cursor=conn.cursor()
                    for r in range(1,self.sheet.nrows):
                        uid=self.sheet.cell(r,15).value
                        dis=self.sheet.cell(r,1).value
                        street=self.sheet.cell(r,2).value
                        orient=self.sheet.cell(r,3).value if self.sheet.cell(r,3).value!='' else '-----'
                        kol=self.sheet.cell(r,5).value
                        floor=self.sheet.cell(r,6).value+'/'+self.sheet.cell(r,7).value
                        square=self.sheet.cell(r,8).value
                        sost=self.sheet.cell(r,9).value if self.sheet.cell(r,9).value!='' else '-----'
                        obst=self.sheet.cell(r,10).value if self.sheet.cell(r,10).value!='' else '-----'
                        cost=self.sheet.cell(r,4).value+' '+self.sheet.cell(r,5).value
                        phone=self.sheet.cell(r,13).value
                        name=self.sheet.cell(r,14).value if self.sheet.cell(r,14).value!='' else '-----'
                        long="-----"
                        lat="-----"
                        photo="-----"
                        status=0
                        sql=f'INSERT INTO Rielt_rent (uid,district,orient,room_number,floor,square,street,condition_of_repair,furniture,price,telephon_number,name,longitude,latitude,photo,status) VALUES ({uid},"{dis}","{orient}",{kol},"{floor}",{float(square)},"{street}","{sost}","{obst}","{cost}","{phone}","{name}","{long}","{lat}","{photo}",{status})'
                        print(sql)
                        try:
                            cursor.execute(sql)
                            #cursor.commit()
                        except Exception as e:
                            print(e)
                            continue
                        self.mysignal.emit(r)
                    conn.close()
            except Exception as e:
                er=Error1(e)
                er.show()


class Example(QWidget):

    def __init__(self):
        super().__init__()
        self.initUI()
    def initUI(self):
        self.vb=QVBoxLayout()
        self.pb=QProgressBar()
        self.rb1=QRadioButton("Продажа")
        self.rb2=QRadioButton("Аренда")
        hb1=QHBoxLayout()
        hb1.addWidget(self.rb1)
        hb1.addWidget(self.rb2)
        hb2=QHBoxLayout()
        self.text_line=QLineEdit()
        b2=QPushButton("Начать выгрузку")
        b=QPushButton("Выбрать")
        hb2.addWidget(self.text_line)
        hb2.addWidget(b)
        self.vb.addLayout(hb2)
        self.vb.addLayout(hb1)
        self.vb.addWidget(b2)
        self.vb.addWidget(self.pb)
        self.setLayout(self.vb)
        self.mythread=MyThread()
        b.clicked.connect(self.opens)
        b2.clicked.connect(self.export)
        self.mythread.started.connect(self.startd)
        self.mythread.mysignal.connect(self.pbar,Qt.QueuedConnection)
        self.setWindowTitle("Передача")
        self.resize(300,120)
    def opens(self):
        self.text_line.setText(QFileDialog.getOpenFileName(self, "Open file")[0])
    def export(self):
        book=xlrd.open_workbook(self.text_line.text())
        self.mythread.sheet=book.sheet_by_name("Worksheet")
        self.pb.setRange(0,self.mythread.sheet.nrows)
        if self.rb1.isChecked():
            self.mythread.sale()
        else:
            self.mythread.rent()
        self.mythread.start()
    def pbar(self,val):
        #print("$$$$$")
        self.pb.setValue(val)
    def startd(self):
        print("!!!!")

class Error1(QWidget):
    def __init__(self,er_text):
        super().__init__()
        self.initUI(er_text)
    def initUI(self,err):
        vb=QVBoxLayout()
        lbl=QLabel("Возникла ошибка!!!")
        lbl2=QLabel()
        er_lbl=QLabel()
        er_lbl.setText(str(err))
        vb.addWidget(lbl)
        vb.addWidget(er_lbl)
        vb.addWidget(lbl2)
        self.setLayout(vb)
        self.setWindowTitle("Ошибка")


if __name__ == "__main__":

    app = QApplication(sys.argv)
    ex = Example()
    ex.show()
    sys.exit(app.exec_())