def __init__(self, parent=None): QtGui.QDialog.__init__(self, parent) self.ui = Ui_Unload() self.ui.setupUi(self) self.shift = parent.shift #self.connect(self.ui.formButton, QtCore.SIGNAL("clicked()"),QtCore.SLOT("on_click_formButton()")) self.connect(self.ui.saveButton, QtCore.SIGNAL("clicked()"),QtCore.SLOT("on_click_saveButton()")) self.connect(self.ui.uploadButton, QtCore.SIGNAL("clicked()"),QtCore.SLOT("on_click_loadButton()"))
class Unload_Class(QtGui.QDialog): """Класс для работы с загрузкой выгрузкой данных""" #порядок импорта таблиц не ошибиться с названиями import_order = ( 'cashier', 'shift', 'measure', 'supplier', 'section', 'product', 'dish', 'consumption', 'income', 'calculate', 'menu', 'company', 'worker', 'sale' ) def __init__(self, parent=None): QtGui.QDialog.__init__(self, parent) self.ui = Ui_Unload() self.ui.setupUi(self) self.shift = parent.shift #self.connect(self.ui.formButton, QtCore.SIGNAL("clicked()"),QtCore.SLOT("on_click_formButton()")) self.connect(self.ui.saveButton, QtCore.SIGNAL("clicked()"),QtCore.SLOT("on_click_saveButton()")) self.connect(self.ui.uploadButton, QtCore.SIGNAL("clicked()"),QtCore.SLOT("on_click_loadButton()")) #def on_click_formButton(self): #self.get_date() #self.export_base() def on_click_saveButton(self): self.get_date() self.export_base() def on_click_loadButton(self): self.import_base() def get_date(self): self.start = self.ui.startDateEdit.date().toString('yyyy-MM-dd 00:00:00') self.finish = self.ui.finishDateEdit.date().toString('yyyy-MM-dd 23:59:59') self.base = "1" def export_base(self): db = localDb_Class() root = etree.Element("root", base = self.base, start = self.start, finish = self.finish) tables = db.exec_query("SELECT name FROM sqlite_master WHERE type='table'")['rows'] tables.remove(tables[1]) for table in tables: #try: tableXML = etree.SubElement(root, "%s" % table[0]) query = db.exec_query("SELECT * FROM {} WHERE date>='{}' AND date<='{}'".format(table[0],self.start,self.finish)) rows, keys = query['rows'], query['keys'] for row in rows: id = etree.SubElement(tableXML, "id", value = "%s" % row["id"]) for key in keys: id.append(etree.Element("data", field = "%s" % key, value = "%s" % row["%s" % key])) #except: #None db.close_db() with open('export/base_'+"%s" % self.shift.base+'.xml', 'w') as f: f.write(etree.tostring(root, pretty_print=True, encoding="UTF-8")) def import_base(self): db = localDb_Class() for table_tag in self.import_order: print table_tag element = etree.iterparse('import/base_'+self.shift.base+'.xml', events = ('end',), tag = table_tag) for event, elem in element: for element_id in elem.iter(tag="row"): data = {i.get('field'):i.get('value') for i in element_id.iter(tag='data')} getattr(self, 'import_%s' % table_tag)(data) elem.clear() while elem.getprevious() is not None: del elem.getparent()[0] del element db.close_db() def check_value(self,table,val,col = 'id'): """Сравнивает на наличие подобной записи""" try: self.save_data(table,val) except: db = localDb_Class() answer = db.select_val_by_col(table,col,val[col]) db.close_db() self.save_to_compare(answer['rows'],val) def save_data(self, table, val): """Записывает """ db = localDb_Class() query = "INSERT INTO %s " % table query += " (%s) " % ",".join([ "%s" % field for field in val]) query += " VALUES (%s) " % ",".join([ "'%s'" % val[field] for field in val]) db.exec_query(query) db.close_db() def save_to_compare(self,exist_vals,changed_vals): """Сохраняет список для сравнения существующих записей""" None def import_cashier(self,val): self.check_value('cashier', val) def import_shift(self,val): None def import_measure(self,val): None def import_supplier(self,val): None def import_section(self,val): self.check_value('section', val) def import_product(self,val): self.check_value('product', val) def import_dish(self,val): self.check_value('dish', val) def import_consumption(self,val): self.check_value('consumption', val) def import_income(self,val): del val['base'] self.check_value('income', val) def import_calculate(self,val): self.check_value('calculate', val) def import_menu(self,val): self.check_value('menu', val) def import_company(self,val): self.check_value('company', val) def import_worker(self,val): self.check_value('worker', val) def import_sale(self,val): self.check_value('product', val)
class Unload_Class(QtGui.QDialog): """Класс для работы с загрузкой выгрузкой данных""" # порядок импорта таблиц не ошибиться с названиями import_order = ( "cashier", "shift", "measure", "supplier", "section", "product", "dish", "consumption", "income", "calculate", "menu", "company", "worker", "sale", ) def __init__(self, parent=None): QtGui.QDialog.__init__(self, parent) self.parent = parent self.shift = parent.shift self.ui = Ui_Unload() self.ui.setupUi(self) self.connect(self.ui.formButton, QtCore.SIGNAL("clicked()"), QtCore.SLOT("on_click_to1CButton()")) self.connect(self.ui.saveButton, QtCore.SIGNAL("clicked()"), QtCore.SLOT("on_click_saveButton()")) self.connect(self.ui.uploadButton, QtCore.SIGNAL("clicked()"), QtCore.SLOT("on_click_loadButton()")) def on_click_to1CButton(self): self.get_date() rootXML = etree.Element(u"ФайлПунктПитания") self.valToXML(rootXML, u"НачалоПериода", self.start) self.valToXML(rootXML, u"КонецПериода", self.finish) db = localDb_Class() query = "SELECT * FROM worker WHERE company='101' AND active=1" workers = db.exec_query(query)["rows"] db.close_db() for worker in workers: infoXML = etree.SubElement(rootXML, u"СВЕДЕНИЯ_ЗА_ПЕРИОД") self.valToXML(infoXML, u"Фамилия", worker["name"].split(" ")[0]) self.valToXML(infoXML, u"Имя", worker["name"].split(" ")[1]) self.valToXML(infoXML, u"Отчество", worker["name"].split(" ")[2]) self.valToXML(infoXML, u"ТабНомер", u"%s" % worker["employee"]) self.valToXML(infoXML, u"СуммаЗаПериод", u"%s" % self.getSumByWorker(worker["employee"])) with open("export/sum.xml", "w") as f: f.write(etree.tostring(rootXML, pretty_print=True, encoding="Windows-1251")) def getSumByWorker(self, worker): db = localDb_Class() query = ( "SELECT SUM(m.price*s.number) AS sum FROM worker AS w, menu AS m, sale AS s WHERE s.menu=m.id AND s.worker='%s'" % worker ) result = db.exec_query(query)["rows"][0]["sum"] db.close_db() return result def valToXML(self, parent, child, text): val = etree.Element(child) val.text = text parent.append(val) def on_click_saveButton(self): self.get_date() self.export_base() def on_click_loadButton(self): self.import_base() def get_date(self): self.start = self.ui.startDateEdit.date().toString("yyyy-MM-dd 00:00:00") self.finish = self.ui.finishDateEdit.date().toString("yyyy-MM-dd 23:59:59") self.base = self.shift.base print self.base def export_base(self): db = localDb_Class() rootXML = etree.Element("root", base="%s" % self.base, start=self.start, finish=self.finish) tables = db.exec_query("SELECT name FROM sqlite_master WHERE type='table' AND name <> 'sqlite_sequence'")[ "rows" ] for table in tables: try: tableXML = etree.SubElement(rootXML, "%s" % table[0]) try: query = db.exec_query( "SELECT * FROM {} WHERE date>='{}' AND date<='{}' AND base='{}'".format( table[0], self.start, self.finish, self.base ) ) except: query = db.exec_query( "SELECT * FROM {} WHERE date>='{}' AND date<='{}'".format(table[0], self.start, self.finish) ) rows, keys = query["rows"], query["keys"] for row in rows: rowXML = etree.SubElement(tableXML, "row", value="%s" % row["id"]) for key in keys: rowXML.append(etree.Element("data", field="%s" % key, value="%s" % row["%s" % key])) except: None db.close_db() with open("export/base_%s.xml" % self.shift.base, "w") as f: f.write(etree.tostring(rootXML, pretty_print=True, encoding="UTF-8")) def import_base(self): self.open_compare() db = localDb_Class() for table_tag in self.import_order: print table_tag self.open_compareTable(table_tag) element = etree.iterparse("import/base_%s" % +self.shift.base + ".xml", events=("end",), tag=table_tag) for event, elem in element: for element_id in elem.iter(tag="id"): data = {i.get("field"): i.get("value") for i in element_id.iter(tag="data")} getattr(self, "import_%s" % table_tag)(data) elem.clear() while elem.getprevious() is not None: del elem.getparent()[0] del element db.close_db() def open_compare(self): """Открвает окно сравнения""" self.compareWindow = QtGui.QWidget(self.parent, QtCore.Qt.Window) self.compareWindow.setWindowModality(QtCore.Qt.WindowModal) self.compareWindow.ui = Ui_Compare() self.compareWindow.ui.setupUi(self.compareWindow) self.compareWindow.show() self.tab = {} def open_compareTable(self, tag): """Вставляет вкладку с таблицей""" try: tab = self.compareWindow.ui.tabWidget.indexOf(self.tab[tag]) # ~ self.formMenu.renew() except: self.tab[tag] = QtGui.QWidget(self.compareWindow) self.tab[tag].ui = Ui_CompareTable() self.tab[tag].ui.setupUi(self.tab[tag]) tab = self.compareWindow.ui.tabWidget.addTab(self.tab[tag], u"%s" % tag) def check_value(self, table, val, col="id"): """Сравнивает на наличие подобной записи""" try: self.save_data(table, val) except: db = localDb_Class() print val answer = db.select_val_by_col(table, col, val[col]) db.close_db() self.save_to_compare(table, answer["rows"], val) def save_data(self, table, val): """Записывает """ db = localDb_Class() query = "INSERT INTO %s " % table query += " (%s) " % ",".join(["%s" % field for field in val]) query += " VALUES (%s) " % ",".join(["'%s'" % val[field] for field in val]) print query db.exec_query(query) db.close_db() def save_to_compare(self, tag, exist_vals, changed_vals): """Сохраняет список для сравнения существующих записей""" compared = {"exist": exist_vals, "changed": changed_vals} print exist_vals exist_item = QtGui.QTableWidgetItem() exist_item.setText(u"%s" % exist_vals[0][1]) change_item = QtGui.QTableWidgetItem(u"%s" % changed_vals["id"]) self.tab[tag].ui.compareTable.setRowCount(self.tab[tag].ui.compareTable.rowCount() + 1) self.tab[tag].ui.compareTable.setItem(self.tab[tag].ui.compareTable.rowCount() - 1, 1, exist_item) self.tab[tag].ui.compareTable.setItem(self.tab[tag].ui.compareTable.rowCount() - 1, 2, change_item) def import_cashier(self, val): self.check_value("cashier", val) def import_shift(self, val): val["base"] = self.shift.base self.check_value("shift", val) def import_measure(self, val): None def import_supplier(self, val): None def import_section(self, val): self.check_value("section", val) def import_product(self, val): self.check_value("product", val) def import_dish(self, val): self.check_value("dish", val) def import_consumption(self, val): self.check_value("consumption", val) def import_income(self, val): self.check_value("income", val) def import_calculate(self, val): val["base"] = self.shift.base self.check_value("calculate", val) # ~ self.save_data('calculate',val) def import_menu(self, val): val["base"] = self.shift.base self.check_value("menu", val) def import_company(self, val): self.check_value("company", val) def import_worker(self, val): self.check_value("worker", val) def import_sale(self, val): self.check_value("product", val)