def __init__(self, parent=None):
QtGui.QWidget.__init__(self, parent)
self.ui = Ui_mainWindow()
self.ui.setupUi(self)
self.uiD = dogovor()
#Подготовка таблицы
self.ui.tableWidget.setColumnCount(14)
self.ui.tableWidget.setHorizontalHeaderLabels(["Имя","Сумма", "Номер договора", "Срок", "Начало", "Окончание", "Ставка", "Сумма % \r\nк уплате", "Текущий\r\n период (дни)", "Начало", "Окончание", "Сумма % к уплате\r\n за период","Всего к уплате \r\nза период","Договор"])
#Кнопка клиента
QtCore.QObject.connect(self.ui.pushButton_2, QtCore.SIGNAL("clicked()"), self.add_client)
#добавление новой записи
QtCore.QObject.connect(self.ui.pushAdd, QtCore.SIGNAL("clicked()"), self.add_new_record)
QtCore.QObject.connect(self.ui.pushDelete, QtCore.SIGNAL("clicked()"), self.del_client)
#удаление договора
QtCore.QObject.connect(self.uiD.btn_delete_dog, QtCore.SIGNAL("clicked()"), self.del_dog)
#сохранение данных
QtCore.QObject.connect(self.uiD.pushButton, QtCore.SIGNAL("clicked()"), self.save_data)
QtCore.QObject.connect(self.ui.pushEdit, QtCore.SIGNAL("clicked()"), self.edit_window)
#изменение поиска выбора по людям
self.ui.comboName.currentIndexChanged['QString'].connect(self.update_contract)
#изменения критерия поиска
self.ui.comboBox.currentIndexChanged['QString'].connect(self.update_search)
#клик по таблице
self.ui.tableWidget.cellClicked.connect(self.cell_clicked)
#textchnged line3 srok
self.uiD.line3.textChanged.connect(self.days_changed)
#days
self.uiD.line4.textChanged.connect(self.days_changed)
#textchanged line1 summa
self.uiD.line1.textChanged.connect(self.money_changed)
#stavka
self.uiD.line6.textChanged.connect(self.money_changed)
#tek period
self.uiD.line8.textChanged.connect(self.tek_period)
self.uiD.line9.textChanged.connect(self.tek_period)
con = sqlite3.connect('ebank.db')
with con:
cursor = con.cursor()
list_loadr = cursor.execute('SELECT name FROM people')
list_load = list_loadr.fetchall()
li = ("Все",)
con.close()
for x in list_load:
li += x
self.ui.comboName.clear()
self.ui.comboName.addItems(li)
class StartMyMain(QtGui.QMainWindow):
_name = ""
_curr = ''
_rows_len = 0
_id = 0
_id_row = 0
_new = False
def __init__(self, parent=None):
QtGui.QWidget.__init__(self, parent)
self.ui = Ui_mainWindow()
self.ui.setupUi(self)
self.uiD = dogovor()
#Подготовка таблицы
self.ui.tableWidget.setColumnCount(14)
self.ui.tableWidget.setHorizontalHeaderLabels(["Имя","Сумма", "Номер договора", "Срок", "Начало", "Окончание", "Ставка", "Сумма % \r\nк уплате", "Текущий\r\n период (дни)", "Начало", "Окончание", "Сумма % к уплате\r\n за период","Всего к уплате \r\nза период","Договор"])
#Кнопка клиента
QtCore.QObject.connect(self.ui.pushButton_2, QtCore.SIGNAL("clicked()"), self.add_client)
#добавление новой записи
QtCore.QObject.connect(self.ui.pushAdd, QtCore.SIGNAL("clicked()"), self.add_new_record)
QtCore.QObject.connect(self.ui.pushDelete, QtCore.SIGNAL("clicked()"), self.del_client)
#удаление договора
QtCore.QObject.connect(self.uiD.btn_delete_dog, QtCore.SIGNAL("clicked()"), self.del_dog)
#сохранение данных
QtCore.QObject.connect(self.uiD.pushButton, QtCore.SIGNAL("clicked()"), self.save_data)
QtCore.QObject.connect(self.ui.pushEdit, QtCore.SIGNAL("clicked()"), self.edit_window)
#изменение поиска выбора по людям
self.ui.comboName.currentIndexChanged['QString'].connect(self.update_contract)
#изменения критерия поиска
self.ui.comboBox.currentIndexChanged['QString'].connect(self.update_search)
#клик по таблице
self.ui.tableWidget.cellClicked.connect(self.cell_clicked)
#textchnged line3 srok
self.uiD.line3.textChanged.connect(self.days_changed)
#days
self.uiD.line4.textChanged.connect(self.days_changed)
#textchanged line1 summa
self.uiD.line1.textChanged.connect(self.money_changed)
#stavka
self.uiD.line6.textChanged.connect(self.money_changed)
#tek period
self.uiD.line8.textChanged.connect(self.tek_period)
self.uiD.line9.textChanged.connect(self.tek_period)
con = sqlite3.connect('ebank.db')
with con:
cursor = con.cursor()
list_loadr = cursor.execute('SELECT name FROM people')
list_load = list_loadr.fetchall()
li = ("Все",)
con.close()
for x in list_load:
li += x
self.ui.comboName.clear()
self.ui.comboName.addItems(li)
def days_changed(self, string):
try:
dt = datetime.strptime(str(self.uiD.line4.text()), "%Y-%m-%d")
srok = int(self.uiD.line3.text())
itog = dt + timedelta(days=srok)
self.uiD.line5.setText(str(itog.date()))
except ValueError:
self.uiD.line5.setText("")
#line5
def money_changed(self, string):
try:
summa = float(self.uiD.line1.text())
stavka = int(self.uiD.line6.text()) / 100
itog = summa * stavka
self.uiD.line7.setText(str(itog))
except ValueError:
self.uiD.line7.setText("")
#line7
def tek_period(self, string):
try:
srok = int(self.uiD.line8.text())
dt = datetime.strptime(str(self.uiD.line9.text()), "%Y-%m-%d")
itog = dt + timedelta(days=srok)
self.uiD.line10.setText(str(itog.date()))
except ValueError: self.uiD.line10.setText("")
def save_data(self):
con = sqlite3.connect('ebank.db')
with con:
cursor = con.cursor()
cursor.execute('pragma foreign_keys=ON')
cursor.execute('pragma foreign_keys')
if self._new:
cursor.execute('INSERT INTO contract(man_id, summa, numer_dog, days, nachalo, okonchanie, rate, summa_k_uplate, tek_period, tek_nachalo, tek_okonchanie, summa_za_period, vsego_za_period, word_doc) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)', (self._id,float(self.uiD.line1.text()), self.uiD.line2.text(), int(self.uiD.line3.text()), self.uiD.line4.text(), self.uiD.line5.text(), int(self.uiD.line6.text()), float(self.uiD.line7.text()), int(self.uiD.line8.text()), self.uiD.line9.text(), self.uiD.line10.text(), float(self.uiD.line11.text()), float(self.uiD.line12.text()), self.uiD.label_word.text()))
self._new = False
else:
cursor.execute("UPDATE contract SET summa=?, numer_dog=?, days=?, nachalo=?, okonchanie=?, rate=?, summa_k_uplate=?, tek_period=?, tek_nachalo=?,tek_okonchanie=?, summa_za_period=?, vsego_za_period=?, word_doc=? WHERE man_id=? AND numer_dog=?", (float(self.uiD.line1.text()), self.uiD.line2.text(), int(self.uiD.line3.text()), self.uiD.line4.text(), self.uiD.line5.text(), int(self.uiD.line6.text()), float(self.uiD.line7.text()), int(self.uiD.line8.text()), self.uiD.line9.text(), self.uiD.line10.text(), float(self.uiD.line11.text()), float(self.uiD.line12.text()), self.uiD.label_word.text(),self._id, self._curr[self._id_row][2]))
con.commit()
con.close()
self.update_contract(self._name)
self.uiD.close()
def del_dog(self):
con = sqlite3.connect("ebank.db")
with con:
cursor = con.cursor()
cursor.execute('pragma foreign_keys=ON')
cursor.execute('pragma foreign_keys')
cursor.execute("DELETE FROM contract WHERE man_id = '" +str(self._id)+"' AND numer_dog='"+self._curr[self._id_row][2]+"'")
con.close()
self.update_contract(self._name)
self.uiD.close()
def add_client(self):
text, ok = QtGui.QInputDialog.getText(self, 'Добавление',
'Введите ФИО:')
if ok and text != "":
con = sqlite3.connect("ebank.db")
with con:
cursor = con.cursor()
cursor.execute('pragma foreign_keys=ON')
cursor.execute('pragma foreign_keys')
cursor.execute("INSERT INTO people(name) VALUES(?)", ((text,)))
list_loadr = cursor.execute("SELECT name FROM people")
list_l = list_loadr.fetchall()
con.commit()
con.close()
li = ('Все',)
for x in list_l:
li += x
self.ui.comboName.clear()
self.ui.comboName.addItems(li)
def del_client(self):
con = sqlite3.connect("ebank.db")
with con:
cursor = con.cursor()
cursor.execute('pragma foreign_keys=ON')
cursor.execute('pragma foreign_keys')
cursor.execute("DELETE FROM people WHERE id_number = " + str(self._id))
list_loadr = cursor.execute('SELECT name FROM people')
list_load = list_loadr.fetchall()
li = ("Все",)
con.close()
for x in list_load:
li += x
self.ui.comboName.clear()
self.ui.comboName.addItems(li)
self.ui.comboName.setCurrentIndex(0)
def add_new_record(self):
self._new = True
#datetime.strptime("","%Y-%m-%d")
if self._name != "Все":
self.uiD.show()
self.uiD.label_name.setText(self._name)
self.uiD.line1.setText("")
self.uiD.line2.setText("")
self.uiD.line3.setText("365")
self.uiD.line4.setText("0000-00-00")
self.uiD.line5.setText("")
self.uiD.line6.setText("")
self.uiD.line7.setText("")
self.uiD.line8.setText("")
self.uiD.line9.setText("")
self.uiD.line10.setText("")
self.uiD.line11.setText("")
self.uiD.line12.setText("")
self.uiD.label_word.setText("Не выбран")
self.uiD.btn_delete_dog.setEnabled(False)
def update_contract(self, text):
self.ui.pushEdit.setEnabled(False)
self.ui.comboBox.clear()
self.ui.label_col.setText("Столбец")
self.ui.tableWidget.setRowCount(0)
self._name = text
con = sqlite3.connect('ebank.db')
with con:
cursor = con.cursor()
cursor.execute('pragma foreign_keys=ON')
cursor.execute('pragma foreign_keys')
if self._name != "":
if self._name == "Все":
self.ui.pushAdd.setEnabled(False)
self.ui.pushDelete.setEnabled(False)
list_loadr = cursor.execute('SELECT people.name, summa, numer_dog , days , nachalo , okonchanie , rate , summa_k_uplate , tek_period , tek_nachalo , tek_okonchanie , summa_za_period , vsego_za_period , word_doc, man_id FROM contract LEFT JOIN people ON people.id_number = contract.man_id')
list_load = list_loadr.fetchall()
else:
self.ui.pushAdd.setEnabled(True)
self.ui.pushDelete.setEnabled(True)
list_loadr = cursor.execute('SELECT people.name, summa, numer_dog , days , nachalo , okonchanie , rate , summa_k_uplate , tek_period , tek_nachalo , tek_okonchanie , summa_za_period , vsego_za_period , word_doc, man_id FROM contract LEFT JOIN people ON people.id_number = contract.man_id WHERE people.name ="'+self._name+'"')
list_load = list_loadr.fetchall()
row = len(list_load)-1
if row == -1:
list_loadr = cursor.execute("SELECT id_number FROM people WHERE name = '" + self._name + "'")
list_load = list_loadr.fetchone()
self._id = list_load[0]
print(self._id)
else:
self._id = list_load[0][14]
li = tuple()
self._rows_len = len(list_load)
self.ui.tableWidget.setRowCount(self._rows_len)
for row in range(self._rows_len):
for column in range(14):
item = list_load[row][column]
self.ui.tableWidget.setItem(row, column,QtGui.QTableWidgetItem(str(item)))
self._curr = list_load
con.close()
def cell_clicked(self, item):
row = self.ui.tableWidget.currentItem().row()
print(row)
col = self.ui.tableWidget.currentItem().column()
self._id_row = row
item1 = self.ui.tableWidget.horizontalHeaderItem(col).text()
self.ui.label_col.setText(item1)
for_search = ("Все",)
if col == 0:
pass
self.ui.comboBox.clear()
self.ui.comboBox.addItem("Все")
else:
for_search += tuple(str(x[col]) for x in self._curr)
self.ui.comboBox.clear()
self.ui.comboBox.addItems(for_search)
self.ui.comboBox.clearEditText()
if self._name != "Все":
self.ui.pushEdit.setEnabled(True)
#self.edit_window()
def update_search(self, text):
if text == "Все":
self.ui.tableWidget.setRowCount(self._rows_len)
for row in range(self._rows_len):
for column in range(14):
item = self._curr[row][column]
self.ui.tableWidget.setItem(row, column,QtGui.QTableWidgetItem(str(item)))
elif text != "":
itog = []
for x in self._curr:
if text in map(str,x): itog.append(x)
rows_len = len(itog)
self.ui.tableWidget.setRowCount(rows_len)
for row in range(rows_len):
for column in range(14):
item = itog[row][column]
self.ui.tableWidget.setItem(row, column,QtGui.QTableWidgetItem(str(item)))
def edit_window(self):
self._new = False
self.uiD.show()
self.uiD.label_name.setText(self._name)
self.uiD.line1.setText(str(self._curr[self._id_row][1]))
self.uiD.line2.setText(str(self._curr[self._id_row][2]))
self.uiD.line3.setText(str(self._curr[self._id_row][3]))
self.uiD.line4.setText(str(self._curr[self._id_row][4]))
self.uiD.line5.setText(str(self._curr[self._id_row][5]))
self.uiD.line6.setText(str(self._curr[self._id_row][6]))
self.uiD.line7.setText(str(self._curr[self._id_row][7]))
self.uiD.line8.setText(str(self._curr[self._id_row][8]))
self.uiD.line9.setText(str(self._curr[self._id_row][9]))
self.uiD.line10.setText(str(self._curr[self._id_row][10]))
self.uiD.line11.setText(str(self._curr[self._id_row][11]))
self.uiD.line12.setText(str(self._curr[self._id_row][12]))
self.uiD.label_word.setText(str(self._curr[self._id_row][13]))
self.uiD.btn_delete_dog.setEnabled(True)