def FN_LOAD_CREATE(self): filename = self.dirname + '/createUser.ui' loadUi(filename, self) self.setWindowTitle('Users') self.BTN_createUser.clicked.connect(self.FN_CREATE_USER) self.CMB_branch = CheckableComboBox(self) self.CMB_branch.setGeometry(150, 85, 171, 25) self.CMB_branch.setLayoutDirection(QtCore.Qt.LeftToRight) self.CMB_branch.setStyleSheet("background-color: rgb(198, 207, 199)") self.CMB_section = CheckableComboBox(self) self.CMB_section.setGeometry(150, 130, 171, 25) self.CMB_section.setEnabled(False) self.CMB_section.setLayoutDirection(QtCore.Qt.LeftToRight) self.CMB_section.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_BRANCHES() self.FN_GET_Section() self.FN_GET_USERTYPE() self.checkBox.toggled.connect(self.FN_EnableDepartment) self.CMB_userStatus.addItems(["Active", "Inactive"]) # Set Style # self.voucher_num.setStyleSheet(label_num) # self.label_2.setStyleSheet(desc_5) css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read())
def FN_LOADUI(self): filename = self.dirname + '/editCoupon.ui' loadUi(filename, self) self.Qcombo_company = CheckableComboBox(self) self.Qcombo_company.setGeometry(550, 135, 271, 25) self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_company.setStyleSheet("background-color: rgb(198, 207, 199)") self.Qcombo_branch = CheckableComboBox(self) self.Qcombo_branch.setGeometry(550, 165, 271, 25) self.Qcombo_branch.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_branch.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_Company() self.FN_GET_Branch() self.CMB_CouponStatus.addItems(["Inactive","Active"]) self.FN_getData() self.CMB_CouponDes.activated[str].connect(self.FN_getDatabyID) self.FN_getDatabyID() self.radioButton_Value.clicked.connect(self.FN_EnableDiscVal) self.radioButton_Percentage.clicked.connect(self.FN_EnablePercentage) self.checkBox_Multi.toggled.connect(self.FN_endableMultiUser) self.BTN_editCoupon.clicked.connect(self.FN_editAction) self.setWindowFlags(QtCore.Qt.WindowCloseButtonHint | QtCore.Qt.WindowMinimizeButtonHint) # Set Style self.labe_id.setStyleSheet(label_num) self.label.setStyleSheet(desc_5) css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read())
def FN_LOAD_CREATE(self): filename = self.dirname + '/Pos_Parameter.ui' loadUi(filename, self) self.setWindowTitle('Pos_Parameters') css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) self.Qcombo_parameter = CheckableComboBox(self) self.Qcombo_parameter.setGeometry(50, 160, 271, 25) self.Qcombo_parameter.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_parameter.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_Company() self.FN_GET_Branch() self.FN_GET_Parameter() self.Qcombo_branch.activated[str].connect(self.FN_GET_POS) self.CMB_CouponStatus.addItems(["Inactive", "Active"]) self.pushButton.clicked.connect(self.FN_create)
def FN_LOAD_CREATE(self): filename = self.dirname + '/createUser.ui' loadUi(filename, self) self.setWindowTitle('Users') self.BTN_createUser.clicked.connect(self.FN_CREATE_USER) self.CMB_branch = CheckableComboBox(self) self.CMB_branch.setGeometry(150, 85, 171, 25) self.CMB_branch.setLayoutDirection(QtCore.Qt.LeftToRight) self.CMB_branch.setStyleSheet("background-color: rgb(198, 207, 199)") self.CMB_section = CheckableComboBox(self) self.CMB_section.setGeometry(150, 130, 171, 25) self.CMB_section.setEnabled(False) self.CMB_section.setLayoutDirection(QtCore.Qt.LeftToRight) self.CMB_section.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_BRANCHES() self.FN_GET_Section() self.FN_GET_USERTYPE() self.checkBox.toggled.connect(self.FN_EnableDepartment) self.CMB_userStatus.addItems(["Active", "Inactive"])
def FN_LOAD_CREATE(self): filename = self.dirname + '/List.ui' loadUi(filename, self) self.Qcombo_Parameter = CheckableComboBox(self) self.Qcombo_Parameter.setGeometry(210, 230, 280, 25) self.Qcombo_Parameter.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_Parameter.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_Parameter() self.setWindowTitle('list') self.BTN_CreateList.clicked.connect(self.FN_CREATEList) self.BTN_ModifyList.clicked.connect(self.FN_ModifyList) self.CMB_Status.addItems(["Active", "Inactive"]) self.FN_DISPLAY_PRIVILAGE() # Apply Style css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read())
def FN_LOAD_MODIFY(self): filename = self.dirname + '/modifyUser.ui' loadUi(filename, self) self.CMB_branch = CheckableComboBox(self) self.CMB_branch.setGeometry(150, 120, 171, 25) self.CMB_branch.setLayoutDirection(QtCore.Qt.LeftToRight) self.CMB_branch.setStyleSheet("background-color: rgb(198, 207, 199)") self.CMB_section = CheckableComboBox(self) self.CMB_section.setGeometry(150, 170, 171, 25) self.CMB_section.setLayoutDirection(QtCore.Qt.LeftToRight) self.CMB_section.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_BRANCHES() self.FN_GET_Section() self.FN_GET_USERTYPE() self.CMB_userStatus.addItems(["Active", "Inactive"]) records = self.FN_GET_USERS() for row in records: self.CMB_userName.addItems([row[0]]) self.FN_GET_USER() self.CMB_userName.currentIndexChanged.connect(self.FN_GET_USER) self.BTN_modifyUser.clicked.connect(self.FN_MODIFY_USER)
def FN_LOAD_DISPLAY(self): try: filename = self.dirname + '/redeemTypeValue.ui' loadUi(filename, self) self.Qbtn_search.clicked.connect(self.FN_SEARCH) #self.Qline_cust.textChanged.connect(self.FN_CLEAR_FEILDS) self.Qbtn_search_details.clicked.connect(self.FN_SEARCH_DETAILS) self.Qbtn_print.clicked.connect(self.printpreviewDialog) css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) valid_from = str(datetime.today().strftime('%Y-%m-%d')) xto = valid_from.split("-") d = QDate(int(xto[0]), int(xto[1]), int(xto[2])) self.Qdate_from.setDate(d) self.Qcombo_company = CheckableComboBox(self) self.Qcombo_company.setGeometry(490, 42, 179, 18) self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_company.setStyleSheet("background-color: rgb(198, 207, 199)") self.Qcombo_branch = CheckableComboBox(self) self.Qcombo_branch.setGeometry(490, 95, 179, 18) self.Qcombo_branch.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_branch.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_COMPANIES() self.Qcombo_company.model().dataChanged.connect(self.FN_GET_Branch) self.FN_GET_Branch() self.FN_GET_REDEEMTPS() except Exception as err: print(err)
def FN_LOADUI(self): try: filename = self.dirname + '/editVoucher.ui' loadUi(filename, self) css_path = Path(__file__).parent.parent.parent # Apply Style For Design path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) self.Qcombo_company = CheckableComboBox(self) self.Qcombo_company.setGeometry(580, 160, 271, 25) self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_company.setStyleSheet("background-color: rgb(198, 207, 199)") self.Qcombo_branch = CheckableComboBox(self) self.Qcombo_branch.setGeometry(580, 200, 271, 25) self.Qcombo_branch.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_branch.setStyleSheet("background-color: rgb(198, 207, 199)") self.Qcombo_section = CheckableComboBox(self) self.Qcombo_section.setGeometry(580, 240, 271, 25) self.Qcombo_section.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_section.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_Company() self.FN_GET_Branch() self.FN_GET_Section() self.FN_GET_sponsor() self.CMB_CouponStatus.addItems(["Inactive", "Active"]) self.FN_getData() self.FN_getDatabyID() self.CMB_CouponDes.activated[str].connect(self.FN_getDatabyID) self.checkBox_Multi.toggled.connect(self.FN_multiuse) self.checkBox_rechange.toggled.connect(self.FN_Rechangable) self.checkBox_refundable.toggled.connect(self.FN_Refundable) self.BTN_editCoupon.clicked.connect(self.FN_editAction) self.LE_desc_5.textChanged.connect(self.FN_search) except: print(sys.exc_info())
class CL_redeemTypeValue(QtWidgets.QDialog): switch_window = QtCore.pyqtSignal() dirname = '' parent ='' modify_flag=0 whereClause1= '' field_names = ['رقم العمليه', 'رقم العميل','اسم العميل','نوع الإسترجاع','الشركه','الفرع','ماكينه الكاشير','رقم الفاتوره','تاريخ الفاتوره','نقاط العميل قبل','النقاط المكتسبه','تقاط العميل بعد','الحاله'] def __init__(self): super(CL_redeemTypeValue, self).__init__() cwd = Path.cwd() mod_path = Path( __file__ ).parent.parent.parent self.dirname = mod_path.__str__() + '/presentation/reports_ui' def FN_LOAD_DISPLAY(self): try: filename = self.dirname + '/redeemTypeValue.ui' loadUi(filename, self) self.Qbtn_search.clicked.connect(self.FN_SEARCH) #self.Qline_cust.textChanged.connect(self.FN_CLEAR_FEILDS) self.Qbtn_search_details.clicked.connect(self.FN_SEARCH_DETAILS) self.Qbtn_print.clicked.connect(self.printpreviewDialog) css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) valid_from = str(datetime.today().strftime('%Y-%m-%d')) xto = valid_from.split("-") d = QDate(int(xto[0]), int(xto[1]), int(xto[2])) self.Qdate_from.setDate(d) self.Qcombo_company = CheckableComboBox(self) self.Qcombo_company.setGeometry(490, 42, 179, 18) self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_company.setStyleSheet("background-color: rgb(198, 207, 199)") self.Qcombo_branch = CheckableComboBox(self) self.Qcombo_branch.setGeometry(490, 95, 179, 18) self.Qcombo_branch.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_branch.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_COMPANIES() self.Qcombo_company.model().dataChanged.connect(self.FN_GET_Branch) self.FN_GET_Branch() self.FN_GET_REDEEMTPS() except Exception as err: print(err) def FN_GET_REDEEMTPS(self): self.conn = db1.connect() try: mycursor = self.conn.cursor() mycursor.execute("SELECT REDEEMT_DESC ,REDEEMT_TYPE_ID FROM Hyper1_Retail.REDEEM_TYPE ") records = mycursor.fetchall() for row, val in records: self.CMB_redeemType.addItem(row, val) except Exception as err: print(err) def FN_GET_Branch(self): self.Qcombo_branch.clear() i = 0 try: # Todo: method for fills the Branch combobox self.conn = db1.connect() mycursor = self.conn.cursor() val3 = "" for a in range(len(self.Qcombo_company.currentData())): if a < len(self.Qcombo_company.currentData()) - 1: val3 = val3 + "'" + self.Qcombo_company.currentData()[a] + "'," else: val3 = val3 + "'" + self.Qcombo_company.currentData()[a] + "'" sqlite3 = "SELECT BRANCH_DESC_A ,BRANCH_NO FROM BRANCH WHERE COMPANY_ID in (" + val3 + ")" mycursor.execute(sqlite3) records = mycursor.fetchall() for row, val in records: for bra in CL_userModule.branch: if val in bra: self.Qcombo_branch.addItem(row, val) i += 1 mycursor.close() self.Qcombo_branch.setCurrentIndex(-1) except Exception as err: print(err) def FN_GET_COMPANIES(self): try: self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY") records = mycursor.fetchall() print(records) for row, val in records: self.Qcombo_company.addItem(row, val) mycursor.close() except Exception as err: print(err) def FN_SEARCH_DETAILS(self): try: self.FN_SEARCH() for i in reversed(range(self.Qtable_customer.rowCount())): self.Qtable_customer.removeRow(i) conn = db1.connect() mycursor = conn.cursor() self.sql = "SELECT MEMBERSHIP_POINTS_TRANS 'رقم العمليه',cp.POSC_CUST_ID 'رقم العميل', c.POSC_NAME 'اسم العميل',REDEEM_TYPE_ID 'نوع الإسترجاع',COMPANY_ID 'الشركه' ,BRANCH_NO 'الفرع',POS_NO 'ماكينه الكاشير',INVOICE_NO 'رقم الفاتوره' ,INVOICE_DATE 'تاريخ الفاتوره', POSC_POINTS_BEFORE 'نقاط العميل قبل', TRANS_POINTS_QTY 'النقاط المكتسبه',POSC_POINTS_AFTER 'تقاط العميل بعد',TRANS_STATUS 'الحاله' FROM Hyper1_Retail.LOYALITY_POINTS_TRANSACTION_LOG cp " \ " left outer join Hyper1_Retail.POS_CUSTOMER c " \ " on cp.POSC_CUST_ID = c.POSC_CUST_ID where " + self.whereClause1 mycursor.execute(self.sql) print(self.sql) myresult = mycursor.fetchall() for row_number, row_data in enumerate(myresult): self.Qtable_customer.insertRow(row_number) for column_number, data in enumerate(row_data): if column_number == 3: data = util.FN_GET_REDEEMTYPE_DESC(str(data)) elif column_number == 4: data = util.FN_GET_COMP_DESC(str(data)) elif column_number == 5: data = util.FN_GET_BRANCH_DESC(str(data)) elif column_number == 12: data = self.FN_GET_TRANS_STATUS(str(data)) self.Qtable_customer.setItem(row_number, column_number, QTableWidgetItem(str(data))) self.Qtable_customer.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers) mycursor.close() except Exception as err: print(err) def FN_GET_TRANS_STATUS(self,status): if status == '2': return "Completed" elif status== '0': return "Reversed" elif status == '1': return "Cancelled" def FN_SEARCH(self): try: self.FN_CLEAR_FEILDS() branchs = self.Qcombo_branch.currentData() companies = self.Qcombo_company.currentData() date_from = self.Qdate_from.dateTime().toString('yyyy-MM-dd') date_to = self.Qdate_to.dateTime().toString('yyyy-MM-dd') redeem_type = self.CMB_redeemType.currentData() conn = db1.connect() mycursor = conn.cursor() whereClause = "" whereClause = whereClause + " `TRANS_CREATED_ON` >= '" + date_from + "' and `TRANS_CREATED_ON` <= '" + date_to + "' " whereClause = whereClause + " and REDEEM_TYPE_ID = '" +redeem_type+"'" company_list = companies if len(company_list) > 0: if len(company_list) == 1: whereClause = whereClause + " and `COMPANY_ID` = '" + company_list[0] + "'" else: company_list_tuple = tuple(company_list) whereClause = whereClause + " and `COMPANY_ID` in {}".format(company_list_tuple) # get branchs branch_list = branchs if len(branch_list) > 0: if len(branch_list) == 1: whereClause = whereClause + " and BRANCH_NO ='" + branch_list[0] + "'" else: branch_list_tuple = tuple(branch_list) whereClause = whereClause + " and BRANCH_NO in {} ".format(branch_list_tuple) sql_select_query = "SELECT sum( TRANS_POINTS_QTY) ,sum(`TRANS_POINTS_VALUE`) FROM Hyper1_Retail.LOYALITY_POINTS_TRANSACTION_LOG where " + whereClause + " group by REDEEM_TYPE_ID" #print(sql_select_query) mycursor.execute(sql_select_query) records = mycursor.fetchall() if len(records) >0: self.Qline_points.setText(str (records[0][0])) self.Qline_point_value.setText(str (records[0][1])) mycursor.close() self.whereClause1 = whereClause except Exception as err: print(err) def printpreviewDialog(self): try: # Todo: method for export reports pdf file points = self.Qline_points.text().strip() pointValue = self.Qline_point_value.text().strip() redeem_type = self.CMB_redeemType.currentText() title = Text() title.setFooter( " س ت 36108 ملف ضريبي 212/306/5 مأموريه ضرائب الشركات المساهمة رقم التسجيل بضرائب المبيعات 153/846/310 ") title.setFont('Scheherazade-Regular.ttf') title.setFontsize(10) title.setName("Redeem Type values") #title.setcodeText("15235") title.setwaterText("hyperone company") #title.settelText("1266533") title.setbrachText("Entrance 1,EL Sheikh Zayed City") #title.setCursor("Testing") title.setQuery(self.sql) title.setCursor(self.field_names) data = [['Redeem Type:' , ' ',redeem_type ], ['No of points ' , ' ',points ,' ', 'Point value:' , ' ',pointValue],] title.setData(data) body() QtWidgets.QMessageBox.information(self, "Success", "Report is printed successfully") import os os.system('my_file.pdf') except Exception as err: print(err) def FN_CLEAR_FEILDS (self): self.Qline_points.setText("") self.Qline_point_value.setText("")
class CL_List(QtWidgets.QDialog): def __init__(self): super(CL_List, self).__init__() cwd = Path.cwd() mod_path = Path(__file__).parent.parent.parent self.dirname = mod_path.__str__() + '/presentation/configuration_ui' self.conn = db1.connect() def FN_LOAD_CREATE(self): filename = self.dirname + '/List.ui' loadUi(filename, self) self.Qcombo_Parameter = CheckableComboBox(self) self.Qcombo_Parameter.setGeometry(210, 230, 280, 25) self.Qcombo_Parameter.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_Parameter.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_Parameter() self.setWindowTitle('list') self.BTN_CreateList.clicked.connect(self.FN_CREATEList) self.BTN_ModifyList.clicked.connect(self.FN_ModifyList) self.CMB_Status.addItems(["Active", "Inactive"]) self.FN_DISPLAY_PRIVILAGE() # Apply Style css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) def FN_GET_Parameter(self): # Todo: method for fills the Parameter combobox self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT distinct HW_PARAMETER_DESC , HW_PARAMETER_ID FROM SYS_HW_COMPONENT") records = mycursor.fetchall() print(records) for row, val in records: self.Qcombo_Parameter.addItem(row, val) mycursor.close() def FN_CREATEList(self): if len(self.Qcombo_Parameter.currentData()) == 0 : QtWidgets.QMessageBox.warning(self, "خطا", "اكمل العناصر الفارغه") sql_select_Query = "SELECT * FROM Hyper1_Retail.SYS_CKECK_LIST where LIST_DESC = '"+self.LE_name.text().strip()+"' and STATUS = 1" print(sql_select_Query) mycursor = self.conn.cursor() mycursor.execute(sql_select_Query) print(mycursor.fetchall()) if mycursor.rowcount>0: QtWidgets.QMessageBox.warning(self, "Error", "List is already exists") else: self.name = self.LE_name.text().strip() self.Notes = self.LE_Notes.text().strip() self.status = self.CMB_Status.currentText() if self.status == 'Active': self.status = 1 else: self.status = 0 mycursor = self.conn.cursor() # get max userid mycursor.execute("SELECT max(cast(LIST_ID AS UNSIGNED)) FROM Hyper1_Retail.SYS_CKECK_LIST") myresult = mycursor.fetchone() if myresult[0] == None: self.id = "1" else: self.id = int(myresult[0]) + 1 creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) if CL_validation.FN_isEmpty(self.name) : QtWidgets.QMessageBox.warning(self, "Error", "Please enter all required fields") else: sql = "INSERT INTO Hyper1_Retail.SYS_CKECK_LIST (LIST_ID, LIST_DESC , NOTES, CHANGED_ON, CHANGED_BY, STATUS) VALUES ( %s, %s, %s, %s,%s, %s)" val = ( self.id, self.name, self.Notes, creationDate, CL_userModule.user_name,self.status) mycursor.execute(sql, val) for i in range(len(self.Qcombo_Parameter.currentData())): sql3 = "INSERT INTO SYS_CKECK_LIST_HW_COMPONENT (LIST_ID,HW_PARAMETER_ID,CHANGED_ON,CHANGED_BY,STATUS) VALUES (%s,%s,%s,%s,%s)" val3 = ( self.id, self.Qcombo_Parameter.currentData()[i], creationDate, CL_userModule.user_name,self.status) mycursor.execute(sql3, val3) mycursor.close() print(mycursor.rowcount, "Record inserted.") QtWidgets.QMessageBox.information(self, "Success", "List is created successfully") db1.connectionCommit(self.conn) db1.connectionClose(self.conn) self.FN_DISPLAY_PRIVILAGE() def FN_DISPLAY_PRIVILAGE(self): self.conn = db1.connect() self.w1.clear() self.w1.setRowCount(0) mycursor = self.conn.cursor() sql_select_query = "SELECT * FROM Hyper1_Retail.SYS_CKECK_LIST" mycursor.execute(sql_select_query) records = mycursor.fetchall() records = list(dict.fromkeys(records)) mycursor.close() for row_number, row_data in enumerate(records): self.w1.insertRow(row_number) for column_number, data in enumerate(row_data): self.w1.setItem(row_number, column_number, QTableWidgetItem( str( data ) ) ) val = self.w1.item(row_number, 5).text() self.w1.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers) header_labels = ['List ID', 'Name', 'Notes', 'Created Date', 'Created By', 'Status'] self.w1.setHorizontalHeaderLabels(header_labels) self.w1.doubleClicked.connect(self.Fn_Get_selected_row) def Fn_Get_selected_row(self): try: if len(self.w1.selectedIndexes()) > 0: rowNo = self.w1.selectedItems()[0].row() id = self.w1.item(rowNo, 0).text() desc = self.w1.item(rowNo, 1).text() Notes = self.w1.item(rowNo, 2).text() status = self.w1.item(rowNo, 5).text() if status == '0': status = 'Inactive' else: status = 'Active' self.LE_name.setText(desc) self.LE_Notes.setText(Notes) self.CMB_Status.setCurrentText(status) except Exception as err: print(err) def FN_ModifyList(self): self.name = self.LE_name.text().strip() self.Notes = self.LE_Notes.text().strip() self.status = self.CMB_Status.currentText() if self.status == 'Active': self.status = 1 else: self.status = 0 creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) if CL_validation.FN_isEmpty(self.name) : QtWidgets.QMessageBox.warning(self, "Error", "Please enter all required fields") else: try: rowNo = self.w1.selectedItems()[0].row() desc = self.w1.item(rowNo, 1).text().strip() if self.LE_name.text().strip() != desc: QtWidgets.QMessageBox.warning(self, "Error", "List Name Can't be change") return else: mycursor = self.conn.cursor() sql = "Update Hyper1_Retail.SYS_CKECK_LIST set NOTES = %s, CHANGED_ON = %s, CHANGED_BY = %s, STATUS = %s where LIST_DESC = %s" val = (self.Notes, creationDate, CL_userModule.user_name,self.status,self.name) mycursor.execute(sql, val) mycursor.close() print(mycursor.rowcount, "Record Updated.") QtWidgets.QMessageBox.information(self, "Success", "List is Updated successfully") db1.connectionCommit(self.conn) db1.connectionClose(self.conn) self.LE_name.setText("") self.LE_Notes.setText("") except Exception as err: print(err) self.FN_DISPLAY_PRIVILAGE()
class CL_user(QtWidgets.QDialog): dirname = '' userid = '' branch_list = [] new_branch_list = [] section_list = [] new_section_list = [] def __init__(self): super(CL_user, self).__init__() cwd = Path.cwd() mod_path = Path(__file__).parent.parent.parent self.dirname = mod_path.__str__() + '/presentation/authorization_ui' self.conn = db1.connect() #Todo: method for load ui of modify user def FN_LOAD_MODIFY(self): filename = self.dirname + '/modifyUser.ui' loadUi(filename, self) self.CMB_branch = CheckableComboBox(self) self.CMB_branch.setGeometry(150, 120, 171, 25) self.CMB_branch.setLayoutDirection(QtCore.Qt.LeftToRight) self.CMB_branch.setStyleSheet("background-color: rgb(198, 207, 199)") self.CMB_section = CheckableComboBox(self) self.CMB_section.setGeometry(150, 170, 171, 25) self.CMB_section.setLayoutDirection(QtCore.Qt.LeftToRight) self.CMB_section.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_BRANCHES() self.FN_GET_Section() self.FN_GET_USERTYPE() self.CMB_userStatus.addItems(["Active", "Inactive"]) records = self.FN_GET_USERS() for row in records: self.CMB_userName.addItems([row[0]]) self.FN_GET_USER() self.CMB_userName.currentIndexChanged.connect(self.FN_GET_USER) self.BTN_modifyUser.clicked.connect(self.FN_MODIFY_USER) #Todo: method for load ui of create User def FN_LOAD_CREATE(self): filename = self.dirname + '/createUser.ui' loadUi(filename, self) self.setWindowTitle('Users') self.BTN_createUser.clicked.connect(self.FN_CREATE_USER) self.CMB_branch = CheckableComboBox(self) self.CMB_branch.setGeometry(150, 85, 171, 25) self.CMB_branch.setLayoutDirection(QtCore.Qt.LeftToRight) self.CMB_branch.setStyleSheet("background-color: rgb(198, 207, 199)") self.CMB_section = CheckableComboBox(self) self.CMB_section.setGeometry(150, 130, 171, 25) self.CMB_section.setEnabled(False) self.CMB_section.setLayoutDirection(QtCore.Qt.LeftToRight) self.CMB_section.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_BRANCHES() self.FN_GET_Section() self.FN_GET_USERTYPE() self.checkBox.toggled.connect(self.FN_EnableDepartment) self.CMB_userStatus.addItems(["Active", "Inactive"]) # Set Style # self.voucher_num.setStyleSheet(label_num) # self.label_2.setStyleSheet(desc_5) css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) #Todo: method for get user type def FN_GET_USERTYPE(self): mycursor = self.conn.cursor() self.CMB_userType.clear() sql_select_query = "SELECT USER_TYPE_DESC FROM SYS_USER_TYPE where USER_TYPE_STATUS = 1 " mycursor.execute(sql_select_query) records = mycursor.fetchall() for row in records: self.CMB_userType.addItems([row[0]]) mycursor.close() #Todo: method for get all branches def FN_GET_BRANCHES(self): mycursor = self.conn.cursor() self.CMB_branch.clear() sql_select_query = "SELECT BRANCH_DESC_A ,BRANCH_NO FROM BRANCH where BRANCH_STATUS = 1 " mycursor.execute(sql_select_query) records = mycursor.fetchall() for row, val in records: self.CMB_branch.addItem(row, val) mycursor.close() #Todo: method for get all sections def FN_GET_Section(self): mycursor = self.conn.cursor() self.CMB_section.clear() sql_select_query = "SELECT SECTION_DESC ,SECTION_ID FROM SECTION " mycursor.execute(sql_select_query) records = mycursor.fetchall() for row, val in records: self.CMB_section.addItem(row, val) mycursor.close() #Todo: method for load copy ui def FN_LOAD_COPY(self): filename = self.dirname + '/copyUser.ui' loadUi(filename, self) records = self.FN_GET_USERS() for row in records: self.CMB_userName.addItems([row[0]]) self.CMB_userName1.addItems([row[0]]) self.BTN_copyUser.clicked.connect(self.FN_COPY_USER) self.CMB_userName.currentIndexChanged.connect(self.FN_ASSIGN_ID) self.CMB_userName1.currentIndexChanged.connect(self.FN_ASSIGN_ID) self.FN_ASSIGN_ID() #Todo: method for get data from user combobox def FN_ASSIGN_ID(self): self.user1 = self.CMB_userName.currentText() self.user2 = self.CMB_userName1.currentText() self.LB_userID.setText(self.FN_GET_USERID_N(self.user1)) self.LB_userID2.setText(self.FN_GET_USERID_N(self.user2)) #Todo: method to copy user def FN_COPY_USER(self): newUser = self.LB_userID2.text() if self.user1 == self.user2: QtWidgets.QMessageBox.warning(self, "Error", "Please enter 2 different users") else: mycursor = self.conn.cursor() mycursor1 = self.conn.cursor() mycursor2 = self.conn.cursor() sql_select_query = "select ur.ROLE_ID ,ur.BRANCH_NO ,ur.UR_STATUS " \ "from SYS_USER_ROLE ur inner join SYS_USER u ON u.USER_ID = ur.USER_ID " \ "where u.USER_NAME = %s " x = (self.user1, ) mycursor.execute(sql_select_query, x) records = mycursor.fetchall() mycursor2 = self.conn.cursor() sql_select_query1 = "delete from SYS_USER_ROLE where USER_ID = '" + newUser + "'" mycursor2.execute(sql_select_query1) db1.connectionCommit(self.conn) mycursor1.execute( "SELECT max(cast(UR_USER_ROLE_ID AS UNSIGNED)) FROM SYS_USER_ROLE" ) myresult = mycursor1.fetchone() creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) id = int(myresult[0]) + 1 for row in records: mycursor3 = self.conn.cursor() sql = "INSERT INTO SYS_USER_ROLE (UR_USER_ROLE_ID, USER_ID, ROLE_ID, BRANCH_NO, UR_CREATED_BY, UR_CREATED_ON, UR_CHANGED_BY, UR_CHANGED_ON, UR_STATUS) " \ "VALUES ( %s, %s, %s, %s,%s, %s,%s,%s,%s)" val = (id, newUser, row[0], row[1], CL_userModule.user_name, creationDate, '', '', row[2]) print(str(sql)) mycursor3.execute(sql, val) db1.connectionCommit(self.conn) print(mycursor3.rowcount, "record inserted.") id = id + 1 QtWidgets.QMessageBox.information(self, "Success", "User is copied successfully") mycursor2.close() mycursor1.close() mycursor.close() self.close() #Todo: method for get all users and display all data def FN_GET_USER(self): user = self.CMB_userName.currentText() mycursor = self.conn.cursor() sql_select_query = "select * from SYS_USER where USER_NAME = '" + user + "'" print(sql_select_query) mycursor.execute(sql_select_query) record = mycursor.fetchone() print(record) self.LB_userID.setText(record[0]) self.userid = record[0] self.LE_name.setText(record[2]) self.LE_password.setText(record[3]) self.LE_fullName.setText(record[4]) self.LE_hrId.setText(record[5]) self.CMB_userType.setCurrentText(record[11]) if record[10] == '1': self.CMB_userStatus.setCurrentText('Active') else: self.CMB_userStatus.setCurrentText('Inactive') print(mycursor.rowcount, "record retrieved.") mycursor.close() self.FN_check_branch() self.FN_check_section() self.branch_list.clear() if len(self.CMB_branch.currentData()) > 0: for i in self.CMB_branch.currentData(): self.branch_list.append(i) if len(self.CMB_section.currentData()) > 0: for i in self.CMB_section.currentData(): self.section_list.append(i) #Todo: method for edit user def FN_MODIFY_USER(self): try: self.id = self.LB_userID.text() self.name = self.LE_name.text().strip() self.password = self.LE_password.text().strip() self.branch = self.CMB_branch.currentData()[0] self.fullName = self.LE_fullName.text().strip() self.hrId = self.LE_hrId.text().strip() self.userType = self.CMB_userType.currentText() self.status = self.CMB_userStatus.currentText() if self.status == 'Active': self.status = 1 else: self.status = 0 if CL_validation.FN_isEmpty(self.name) or CL_validation.FN_isEmpty( self.password) or CL_validation.FN_isEmpty( self.fullName) or CL_validation.FN_isEmpty(self.hrId): QtWidgets.QMessageBox.warning( self, "Error", "Please enter all required fields") else: if CL_validation.FN_validation_password( self, self.password) == False: mycursor = self.conn.cursor() if len(self.CMB_branch.currentData()) > 0: for i in self.CMB_branch.currentData(): self.new_branch_list.append(i) if len(self.CMB_section.currentData()) > 0: for i in self.CMB_section.currentData(): self.new_section_list.append(i) changeDate = str( datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) sql = "UPDATE SYS_USER set USER_NAME= %s , USER_PASSWORD= %s , BRANCH_NO = %s, USER_FULLNAME = %s , USER_HR_ID = %s, USER_CHANGED_ON = %s , USER_CHANGED_BY = %s, USER_STATUS = %s, USERTYPE_ID = %s where USER_id= %s " val = (self.name, self.password, self.branch, self.fullName, self.hrId, changeDate, CL_userModule.user_name, self.status, self.userType, self.id) mycursor.execute(sql, val) if len(self.branch_list) > len(self.new_branch_list): for row in self.branch_list: print(row) if row in self.new_branch_list: print("found") else: print("not found") mycursor = self.conn.cursor() sql5 = "update SYS_USER_BRANCH set STATUS= 0 where USER_ID='" + self.userid + "' and BRANCH_NO = '" + row + "'" mycursor.execute(sql5) else: for row in self.new_branch_list: print(row) if row in self.branch_list: print("found") else: mycursor = self.conn.cursor() mycursor.execute( "SELECT * FROM SYS_USER_BRANCH where BRANCH_NO='" + row + "' and USER_ID='" + self.userid + "'") record = mycursor.fetchall() if mycursor.rowcount > 0: mycursor = self.conn.cursor() sql8 = "update SYS_USER_BRANCH set STATUS= 1 where USER_ID='" + self.userid + "' and BRANCH_NO = '" + row + "'" mycursor.execute(sql8) print(sql8) else: mycursor = self.conn.cursor() sql6 = "INSERT INTO SYS_USER_BRANCH (USER_ID,COMPANY_ID,BRANCH_NO,STATUS) VALUES (%s,%s,%s,%s)" val6 = (self.userid, '1', row, '1') mycursor.execute(sql6, val6) if len(self.section_list) > len(self.new_section_list): for row in self.section_list: print(row) if row in self.new_section_list: print("found") else: print("not found") mycursor = self.conn.cursor() sql5 = "update SYS_USER_SECTION set STATUS= 0 where USER_ID='" + self.userid + "' and SECTION_ID = '" + row + "'" mycursor.execute(sql5) else: for row in self.new_section_list: print(row) if row in self.section_list: print("found") else: mycursor = self.conn.cursor() mycursor.execute( "SELECT * FROM SYS_USER_SECTION where SECTION_ID='" + row + "' and USER_ID='" + self.userid + "'") record = mycursor.fetchall() if mycursor.rowcount > 0: mycursor = self.conn.cursor() sql8 = "update SYS_USER_SECTION set STATUS= 1 where USER_ID='" + self.userid + "' and SECTION_ID = '" + row + "'" mycursor.execute(sql8) print(sql8) else: mycursor = self.conn.cursor() sql6 = "INSERT INTO SYS_USER_SECTION (USER_ID,SECTION_ID,STATUS) VALUES (%s,%s,%s)" val6 = (self.userid, row, '1') mycursor.execute(sql6, val6) mycursor.close() db1.connectionCommit(self.conn) print(mycursor.rowcount, "record Modified.") QtWidgets.QMessageBox.information( self, "Success", "User is modified successfully") db1.connectionClose(self.conn) self.close() except: print(sys.exc_info()) #Todo: method for get user data def FN_GET_USERS(self): mycursor = self.conn.cursor() mycursor.execute( "SELECT USER_NAME USER_ID FROM SYS_USER order by USER_ID asc") records = mycursor.fetchall() mycursor.close() return records #Todo: method for get user id def FN_GET_USERID_N(self, user): mycursor = self.conn.cursor() sql_select_query = "SELECT USER_ID FROM SYS_USER WHERE USER_NAME = %s " x = (user, ) mycursor.execute(sql_select_query, x) myresult = mycursor.fetchone() return myresult[0] #Todo: method for create user def FN_CREATE_USER(self): try: sql_select_Query = "select * from SYS_USER where USER_NAME = '" + self.LE_name.text( ) + "' and USER_STATUS = 1" print(sql_select_Query) mycursor = self.conn.cursor() mycursor.execute(sql_select_Query) print(mycursor.fetchall()) if mycursor.rowcount > 0: QtWidgets.QMessageBox.warning(self, "Error", "Username is already exists") else: self.name = self.LE_name.text().strip() self.password = self.LE_password.text().strip() print(self.CMB_branch.currentData()[0]) self.branch = self.CMB_branch.currentData()[0] self.fullName = self.LE_fullName.text().strip() self.hrId = self.LE_hrId.text().strip() self.userType = self.CMB_userType.currentText() self.status = self.CMB_userStatus.currentText() if self.status == 'Active': self.status = 1 else: self.status = 0 mycursor = self.conn.cursor() mycursor.execute( "SELECT max(cast(USER_ID AS UNSIGNED)) FROM SYS_USER") myresult = mycursor.fetchone() if myresult[0] == None: self.id = "1" else: self.id = int(myresult[0]) + 1 creationDate = str( datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) if CL_validation.FN_isEmpty( self.name) or CL_validation.FN_isEmpty( self.password) or CL_validation.FN_isEmpty( self.fullName) or CL_validation.FN_isEmpty( self.hrId): QtWidgets.QMessageBox.warning( self, "Error", "Please enter all required fields") else: if CL_validation.FN_validation_password( self, self.password) == False: sql = "INSERT INTO SYS_USER (USER_ID, BRANCH_NO, USER_NAME, USER_PASSWORD, USER_FULLNAME, USER_HR_ID, USER_CREATED_ON, USER_CREATED_BY, USER_CHANGED_ON, USER_CHANGED_BY,USER_STATUS, USERTYPE_ID) VALUES ( %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s)" val = (self.id, self.branch, self.name, self.password, self.fullName, self.hrId, creationDate, CL_userModule.user_name, '', '', self.status, self.userType) mycursor.execute(sql, val) for i in range(len(self.CMB_branch.currentData())): sql2 = "INSERT INTO SYS_USER_BRANCH (USER_ID, COMPANY_ID, BRANCH_NO, STATUS) VALUES ( %s, %s, %s, %s)" val = (self.id, '1', self.CMB_branch.currentData()[i], '1') mycursor.execute(sql2, val) if self.checkBox.isChecked(): for i in range(len( self.CMB_section.currentData())): sql = "INSERT INTO SYS_USER_SECTION (USER_ID, SECTION_ID, STATUS) VALUES (%s, %s, %s)" val = (self.id, self.CMB_section.currentData()[i], '1') mycursor.execute(sql, val) mycursor.close() print(mycursor.rowcount, "record inserted.") QtWidgets.QMessageBox.information( self, "Success", "User is created successfully") db1.connectionCommit(self.conn) db1.connectionClose(self.conn) self.close() except: print(sys.exc_info()) #Todo: method for change password def FN_RESET_USER(self): mycursor = self.conn.cursor() changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) if CL_validation.FN_isEmpty(self.LE_password.text()): QtWidgets.QMessageBox.warning(self, "Error", "Enter Password Please") elif CL_validation.FN_isEmpty(self.LE_password2.text()): QtWidgets.QMessageBox.warning(self, "Error", " RePassword Please") elif CL_validation.FN_validation_password( self, self.LE_password.text()) == False: if self.LE_password.text() == self.LE_password2.text(): sql_select_Query = "select * from SYS_USER where USER_NAME = %s and USER_PASSWORD = %s and USER_STATUS = 1" x = (self.LE_username.text(), self.old_password.text()) mycursor = self.conn.cursor() mycursor.execute(sql_select_Query, x) record = mycursor.fetchone() if mycursor.rowcount > 0: sql = "UPDATE SYS_USER set USER_PASSWORD= %s , USER_CHANGED_ON = %s , USER_CHANGED_BY = %s where USER_NAME= %s and USER_PASSWORD= %s " val = (self.LE_password.text(), changeDate, self.LE_username.text(), self.LE_username.text(), self.old_password.text()) print(sql) mycursor.execute(sql, val) mycursor.close() db1.connectionCommit(self.conn) print(mycursor.rowcount, "password changed") QtWidgets.QMessageBox.information( self, "Success", "Password is reset successfully") db1.connectionClose(self.conn) self.close() else: QtWidgets.QMessageBox.warning( self, "Error", "Incorrect Username and Password") print("Please Enter Correct Username and Password") else: QtWidgets.QMessageBox.warning( self, "Error", "Please enter 2 different Passwords") #Todo: method for load reset password ui def FN_LOAD_RESET(self): filename = self.dirname + '/resetUserPassword.ui' loadUi(filename, self) self.BTN_resetPass.clicked.connect(self.FN_RESET_USER) #Todo: method for change password def FN_RESET_USER_MAIN(self): mycursor = self.conn.cursor() if CL_validation.FN_isEmpty(self.LE_password.text()): QtWidgets.QMessageBox.warning(self, "Error", "Enter Password Please") elif CL_validation.FN_isEmpty(self.LE_password2.text()): QtWidgets.QMessageBox.warning(self, "Error", " RePassword Please") elif CL_validation.FN_validation_password( self, self.LE_password.text()) == False: changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) if self.LE_password.text() == self.LE_password2.text(): sql_select_Query = "select * from SYS_USER where USER_NAME = '" + self.LE_username.currentText( ) + "' and USER_STATUS = 1" mycursor = self.conn.cursor() mycursor.execute(sql_select_Query) print(sql_select_Query) record = mycursor.fetchone() print(record) if mycursor.rowcount > 0: sql = "UPDATE SYS_USER set USER_PASSWORD= %s , USER_CHANGED_ON = %s , USER_CHANGED_BY = %s where USER_NAME= %s" val = (self.LE_password.text(), changeDate, self.LE_username.currentText(), self.LE_username.currentText()) print(sql) mycursor.execute(sql, val) mycursor.close() db1.connectionCommit(self.conn) print(mycursor.rowcount, "password changed") QtWidgets.QMessageBox.information( self, "Success", "Password is reset successfully") db1.connectionClose(self.conn) self.close() else: QtWidgets.QMessageBox.warning(self, "Error", "Incorrect Username ") print("Please Enter Correct Username and Password") else: QtWidgets.QMessageBox.warning( self, "Error", "Please enter 2 different Passwords") #Todo: method for load reset password ui def FN_LOAD_RESET_MAIN(self): filename = self.dirname + '/resetUserPasswordMain.ui' loadUi(filename, self) self.FN_GET_User() self.BTN_resetPass.clicked.connect(self.FN_RESET_USER_MAIN) #Todo: method for all user name def FN_GET_User(self): self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT USER_NAME FROM SYS_USER") records = mycursor.fetchall() for row in records: self.LE_username.addItems(row) mycursor.close() #Todo: method for checked branches assigned to user def FN_check_branch(self): self.FN_unCheckedALL() mycursor = self.conn.cursor() sql_select_branch = "SELECT BRANCH_NO FROM BRANCH" mycursor.execute(sql_select_branch) record = mycursor.fetchall() i = 0 for row in record: for row1 in self.FN_SELECT_branch(): if row[0] == row1[0]: items = self.CMB_branch.findText(row[0]) for item in range(items + 2): if int(row1[1]) == 1: self.CMB_branch.setChecked(i) i = i + 1 mycursor.close() #Todo: method for get all branches assigned to user def FN_SELECT_branch(self): mycursor = self.conn.cursor() sql = "SELECT BRANCH_NO , STATUS FROM SYS_USER_BRANCH where USER_ID = %s" c = (self.userid, ) mycursor.execute(sql, c) records = mycursor.fetchall() mycursor.close() return records #Todo: method to refresh checkable combobox for branch def FN_unCheckedALL(self): mycursor = self.conn.cursor() sql_select_branch = "Select BRANCH_NO from BRANCH where BRANCH_STATUS=1" mycursor.execute(sql_select_branch) record = mycursor.fetchall() print(record) i = 0 for row in record: self.CMB_branch.unChecked(i) i += 1 #Todo: method to enable department def FN_EnableDepartment(self): if self.checkBox.isChecked(): self.CMB_section.setEnabled(True) else: self.CMB_section.setEnabled(False) #Todo: method to get section assigned to user and checked it def FN_check_section(self): self.FN_unCheckedSection() mycursor = self.conn.cursor() sql_select_branch = "SELECT SECTION_ID FROM SECTION" mycursor.execute(sql_select_branch) record = mycursor.fetchall() i = 0 for row in record: for row1 in self.FN_SELECT_section(): if row[0] == row1[0]: items = self.CMB_section.findText(row[0]) for item in range(items + 2): if int(row1[1]) == 1: self.CMB_section.setChecked(i) i = i + 1 mycursor.close() #Todo: method to get section assigned to user def FN_SELECT_section(self): mycursor = self.conn.cursor() sql = "SELECT SECTION_ID , STATUS FROM SYS_USER_SECTION where USER_ID = %s" c = (self.userid, ) mycursor.execute(sql, c) records = mycursor.fetchall() mycursor.close() return records #Todo: method to refresh checkable combobox for section def FN_unCheckedSection(self): mycursor = self.conn.cursor() sql_select_branch = "Select SECTION_ID from SECTION where SECTION_STATUS=1" mycursor.execute(sql_select_branch) record = mycursor.fetchall() print(record) i = 0 for row in record: self.CMB_section.unChecked(i) i += 1
def FN_LOAD_DISPLAY(self): try: filename = self.dirname + '/installment.ui' loadUi(filename, self) conn = db1.connect() mycursor = conn.cursor() self.Qbtn_search.clicked.connect(self.FN_SEARCH) self.Qbtn_export.clicked.connect(self.FN_EXPORT) for row_number, row_data in enumerate(CL_userModule.myList): if row_data[1] == 'Installment': if row_data[4] == 'None': print('hh') else: sql_select_query = "select i.ITEM_DESC from Hyper1_Retail.SYS_FORM_ITEM i where ITEM_STATUS= 1 and i.item_id =%s" x = (row_data[4], ) mycursor.execute(sql_select_query, x) result = mycursor.fetchone() # print(result) if result[0] == 'search': self.Qbtn_search.setEnabled(True) elif result[0] == 'export': self.Qbtn_export.setEnabled(True) self.Qbtn_loadItems.clicked.connect(self.FN_LOAD_BARCODES) # Apply Style For Design css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) #Get installment type self.Qcombo_installmentType.addItem("أختر المده", '0') self.FN_GET_installment_types_period() # test Multi selection for company #drob down list with multiselection for company self.Qcombo_company = CheckableComboBox(self) self.Qcombo_company.setGeometry(490, 20, 179, 20) self.Qcombo_company.setLayoutDirection(QtCore.Qt.RightToLeft) self.Qcombo_company.setStyleSheet( "background-color: rgb(198, 207, 199)") self.FN_GET_Company() # TODO Click listner for changing list of company self.Qcombo_company.model().dataChanged.connect(self.FN_GET_Branch) #drob down list with multiselection for bracnch self.Qcombo_branch = CheckableComboBox(self) self.Qcombo_branch.setGeometry(490, 60, 179, 20) self.Qcombo_branch.setLayoutDirection(QtCore.Qt.RightToLeft) self.Qcombo_branch.setStyleSheet( "background-color: rgb(198, 207, 199)") self.FN_GET_Branch() #validation for not pick date before today datefrom = str(datetime.today().strftime('%Y-%m-%d')) xfrom = datefrom.split("-") d = QDate(int(xfrom[0]), int(xfrom[1]), int(xfrom[2])) self.Qdate_from.setDate(d) self.Qdate_to.setDate(d) # Get customer Groupe self.Qcombo_customerGroupe = CheckableComboBox(self) self.Qcombo_customerGroupe.setGeometry(490, 100, 179, 20) self.Qcombo_customerGroupe.setLayoutDirection( QtCore.Qt.RightToLeft) self.Qcombo_customerGroupe.setStyleSheet( "background-color: rgb(198, 207, 199)") self.FN_GET_customerGroupe() # Multi selection for department self.Qcombo_department = CheckableComboBox(self) self.Qcombo_department.setGeometry(630, 190, 85, 22) self.Qcombo_department.setEnabled(False) self.Qcombo_department.setLayoutDirection(QtCore.Qt.RightToLeft) self.Qcombo_department.setStyleSheet( "background-color: rgb(198, 207, 199)") # TODO Click listner for changing list of department self.Qcombo_department.model().dataChanged.connect( self.FN_WhenChecksection) # get Department list if check box self.checkBox_department.stateChanged.connect( self.FN_WhenCheckDepartment) # self.FN_WhenCheckDepartment() # Multi selection for sections self.Qcombo_section = CheckableComboBox(self) self.Qcombo_section.setGeometry(490, 190, 80, 22) self.Qcombo_section.setLayoutDirection(QtCore.Qt.RightToLeft) self.Qcombo_section.setEnabled(False) self.Qcombo_section.setStyleSheet( "background-color: rgb(198, 207, 199)") # get sections list if check box self.checkBox_section.stateChanged.connect( self.FN_WhenChecksection) # self.FN_GET_sections() # TODO Click listner for changing list of department self.Qcombo_section.model().dataChanged.connect( self.FN_WhenCheckBMC_Level) # Multi selection for BMCLevel self.Qcombo_BMCLevel = CheckableComboBox(self) self.Qcombo_BMCLevel.setGeometry(270, 190, 110, 22) self.Qcombo_BMCLevel.setLayoutDirection(QtCore.Qt.RightToLeft) self.Qcombo_BMCLevel.setEnabled(False) self.Qcombo_BMCLevel.setStyleSheet( "background-color: rgb(198, 207, 199)") # get BMC LEVEL4 list if check box self.checkBox_BMCLevel.stateChanged.connect( self.FN_WhenCheckBMC_Level) self.checkBox_Barcode.stateChanged.connect( self.FN_WhenCheckBarcode) # get Banks list if readio button clicked self.RBTN_bank.clicked.connect(self.FN_InstallMent_Checked) # get Vendor list if readio button clicked self.RBTN_vendor.clicked.connect(self.FN_InstallMent_Checked) # if readio button clicked hyperone self.RBTN_hyperone.clicked.connect(self.FN_InstallMent_Checked) # this function for what enabled or not when start self.EnabledWhenOpen() self.Rbtn_stsAll.setChecked(True) #enable the search and the export buttons except Exception as err: print(err)
class CL_EditCoupon(QtWidgets.QDialog): valueType="" valueData="" serialCount = "" MultiCount = "" MultiUse = "" movement=0 serial_num=0 usage=0 branch_list = [] new_branch_list = [] multiusage=0 serial_type=0 dfrom=QDate(1,1,2000) Special=0 DescOldValue= "" COPDISCOUNToldVAL= "" COPDISCOUNToldprecnt= "" Othertype="" row="" oldlist=[] newlist=[] oldstatus="" def __init__(self): super(CL_EditCoupon, self).__init__() cwd = Path.cwd() mod_path = Path(__file__).parent.parent.parent self.dirname = mod_path.__str__() + '/presentation/coupon_ui' self.conn = db1.connect() #Todo: method to load ui of edit coupon def FN_LOADUI(self): filename = self.dirname + '/editCoupon.ui' loadUi(filename, self) self.Qcombo_company = CheckableComboBox(self) self.Qcombo_company.setGeometry(550, 135, 271, 25) self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_company.setStyleSheet("background-color: rgb(198, 207, 199)") self.Qcombo_branch = CheckableComboBox(self) self.Qcombo_branch.setGeometry(550, 165, 271, 25) self.Qcombo_branch.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_branch.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_Company() self.FN_GET_Branch() self.CMB_CouponStatus.addItems(["Inactive","Active"]) self.FN_getData() self.CMB_CouponDes.activated[str].connect(self.FN_getDatabyID) self.FN_getDatabyID() self.radioButton_Value.clicked.connect(self.FN_EnableDiscVal) self.radioButton_Percentage.clicked.connect(self.FN_EnablePercentage) self.checkBox_Multi.toggled.connect(self.FN_endableMultiUser) self.BTN_editCoupon.clicked.connect(self.FN_editAction) self.setWindowFlags(QtCore.Qt.WindowCloseButtonHint | QtCore.Qt.WindowMinimizeButtonHint) # Set Style self.labe_id.setStyleSheet(label_num) self.label.setStyleSheet(desc_5) css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) #Todo: method for fills the company combobox def FN_GET_Company(self): self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY") records = mycursor.fetchall() for row, val in records: self.Qcombo_company.addItem(row, val) mycursor.close() # Todo: method for fills the Branch combobox def FN_GET_Branch(self): i = 0 try: for row, val in CL_userModule.branch: self.Qcombo_branch.addItem(val, row) i += 1 except: print(sys.exc_info()) # Todo: method to get name and id of coupon def FN_getData(self): self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT COP_DESC,COP_ID FROM COUPON") records = mycursor.fetchall() for row,val in records: self.CMB_CouponDes.addItem(row,val) mycursor.close() # Todo: method to get all data about coupon def FN_getDatabyID(self): try: self.branch_list = [] self.new_branch_list = [] self.FN_Clear() indx = self.CMB_CouponDes.currentData() self.labe_id.setText(str(indx)) self.conn = db1.connect() mycursor = self.conn.cursor() sql_select_Query = "SELECT * FROM COUPON where COP_ID = %s" x = (indx,) mycursor = self.conn.cursor() mycursor.execute(sql_select_Query, x) record = mycursor.fetchone() self.row=record[0] self.LE_desc_1.setText(record[1]) self.DescOldValue=record[1] self.COPDISCOUNToldVAL=str(record[2]) self.COPDISCOUNToldprecnt=str(record[3]) if (record[2]!=None and len(self.COPDISCOUNToldVAL) > 0): self.radioButton_Value.setChecked(True) self.LE_desc_2.setValue(float(record[2])) self.LE_desc_2.setEnabled(True) self.LE_desc_3.setEnabled(False) self.LE_desc_3.clear() self.valueType = "COP_DISCOUNT_VAL" self.valueData = self.LE_desc_2.text() self.Othertype="COP_DISCOUNT_PERCENT" else: self.radioButton_Percentage.setChecked(True) self.LE_desc_3.setValue(float(record[3])) self.LE_desc_3.setEnabled(True) self.LE_desc_2.setEnabled(False) self.LE_desc_2.clear() self.valueType = "COP_DISCOUNT_PERCENT" self.valueData = self.LE_desc_3.text() self.Othertype="COP_DISCOUNT_VAL" dateto = record[13] xto = dateto.split("-") d = QDate(int(xto[0]), int(xto[1]), int(xto[2])) self.Qdate_to.setDate(d) datefrom = record[11] xfrom = datefrom.split("-") self.dfrom = QDate(int(xfrom[0]), int(xfrom[1]), int(xfrom[2])) self.Qdate_from.setDate(self.dfrom) self.dfrom = QDateTime(int(xfrom[0]), int(xfrom[1]), int(xfrom[2]), 00, 00, 00, 00) self.LE_desc_4.setValue(float(record[4])) self.serial_num=int(record[4]) self.multiusage=int(record[5]) self.Special=int(record[5]) if (int(record[5]) == 1): self.checkBox_Multi.setChecked(True) self.LE_desc_5.setValue(float(record[6])) self.LE_desc_4.setEnabled(False) self.LE_desc_5.setEnabled(True) else: self.checkBox_Multi.setChecked(False) self.LE_desc_5.setEnabled(False) self.LE_desc_4.setEnabled(True) self.CMB_CouponStatus.setCurrentIndex(int(record[15])) self.oldstatus =str(record[15]) timefrom = record[12] tfrom = timefrom.split(":") some_time = QtCore.QTime(int(tfrom[0]), int(tfrom[1]), 00) self.Qtime_from.setTime(some_time) timeto = record[14] tto = timeto.split(":") some_time = QtCore.QTime(int(tto[0]), int(tto[1]), 00) self.Qtime_to.setTime(some_time) self.FN_check_company(indx) self.FN_check_branch(indx) sql_select_Query = " select * FROM COUPON_SERIAL_PRINT_LOG where COUPON_SERIAL_ID IN(SELECT COPS_SERIAL_ID FROM COUPON_SERIAL , COUPON WHERE COUPON_ID = COP_ID AND COP_ID = %s) " x = (indx,) mycursor = self.conn.cursor() mycursor.execute(sql_select_Query, x) record1 = mycursor.fetchall() if mycursor.rowcount>0: self.movement=1 sql_select_Query = " select * FROM COUPON_USAGE where COPS_SERIAL_ID IN(SELECT COPS_SERIAL_ID FROM COUPON_SERIAL , COUPON WHERE COUPON_ID = COP_ID AND COP_ID = %s) " x = (indx,) mycursor = self.conn.cursor() mycursor.execute(sql_select_Query, x) record2 = mycursor.fetchall() if mycursor.rowcount > 0: self.usage = 1 mycursor.close() if self.usage==1: self.LE_desc_1.setEnabled(False) self.LE_desc_2.setEnabled(False) self.LE_desc_3.setEnabled(False) self.LE_desc_4.setEnabled(False) self.LE_desc_5.setEnabled(False) self.Qcombo_company.setEnabled(False) self.Qcombo_branch.setEnabled(False) self.Qdate_to.setEnabled(False) self.Qdate_from.setEnabled(False) self.usage = 0 else: self.LE_desc_1.setEnabled(True) self.LE_desc_2.setEnabled(True) self.LE_desc_3.setEnabled(True) self.LE_desc_4.setEnabled(True) self.LE_desc_5.setEnabled(True) self.Qcombo_company.setEnabled(True) self.Qcombo_branch.setEnabled(True) self.Qdate_to.setEnabled(True) self.Qdate_from.setEnabled(True) if (record[2] != None and len(self.COPDISCOUNToldVAL) > 0): self.LE_desc_3.setEnabled(False) else: self.LE_desc_2.setEnabled(False) if (int(record[5]) == 1): self.LE_desc_4.setEnabled(False) else: self.checkBox_Multi.setChecked(False) self.LE_desc_5.setEnabled(False) self.branch_list.clear() if len(self.Qcombo_branch.currentData()) > 0: for i in self.Qcombo_branch.currentData(): self.branch_list.append(i) self.oldlist=self.Qcombo_branch.currentData() except: print(sys.exc_info()) # Todo: method to make coupon multi use def FN_endableMultiUser(self): if self.checkBox_Multi.isChecked(): self.LE_desc_5.setEnabled(True) self.LE_desc_4.setEnabled(False) self.LE_desc_4.setValue(1.0) self.multiusage=1 else: self.LE_desc_5.setEnabled(False) self.LE_desc_4.setEnabled(True) self.multiusage=0 # Todo: method to edit coupon def FN_editAction(self): try: self.newlist = self.Qcombo_branch.currentData() if len(self.Qcombo_company.currentData()) == 0 or len(self.Qcombo_branch.currentData()) == 0 or len( self.LE_desc_1.text().strip()) == 0 or len(self.LE_desc_3.text().strip()) == 0 and len(self.LE_desc_2.text().strip()) == 0: QtWidgets.QMessageBox.warning(self, "خطا", "اكمل العناصر الفارغه") else: if self.Qdate_to.dateTime() < self.Qdate_from.dateTime(): QtWidgets.QMessageBox.warning(self, "Done", "تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء") elif self.Qdate_from.dateTime() < self.dfrom: QtWidgets.QMessageBox.warning(self, "Done", "تاريخ الانشاء الجديد يجب ان يكون اكبر او يساوي تاريخ الانشاء قبل التعديل") elif (self.Qdate_from.date() == self.Qdate_to.date()) and int(self.Qtime_from.dateTime().toString('hh')) + int( self.Qtime_from.dateTime().toString('mm')) > int( self.Qtime_to.dateTime().toString('hh')) + int(self.Qtime_to.dateTime().toString('mm')): QtWidgets.QMessageBox.warning(self, "خطا", "وقت الانتهاء يجب ان يكون اكبر من او يساوي وقت الانشاء") else: mycursor = self.conn.cursor() creationDate = str(datetime.today().strftime('%Y-%m-%d')) if self.checkBox_Multi.isChecked(): self.serialCount = "1" self.MultiCount = self.LE_desc_5.text() self.MultiUse = "1" self.serial_type=1 print("multi use="+str(self.multiusage)) if self.multiusage==0: sql2 = "update COUPON_SERIAL set COPS_STATUS='0' where COUPON_ID='" + str( self.CMB_CouponDes.currentData()) + "'" mycursor.execute(sql2) value = randint(0, 1000000000000) creationDate = str(datetime.today().strftime('%Y-%m-%d')) mycursor = self.conn.cursor() sql7 = "INSERT INTO COUPON_SERIAL (COUPON_ID,COPS_BARCODE,COPS_CREATED_BY,COPS_SERIAL_type,COPS_CREATED_On,COPS_PRINT_COUNT,COPS_STATUS) VALUES (%s,%s,%s,%s,%s,%s,%s)" val7 = ( str(self.CMB_CouponDes.currentData()), "HCOP"+bin(value), CL_userModule.user_name,self.serial_type, creationDate, 0, '1') mycursor.execute(sql7, val7) self.multiusage=1 else: self.serialCount = self.LE_desc_4.text() self.MultiCount = "0" self.MultiUse = "0" self.serial_type=0 print(self.serial_num) print(int(self.LE_desc_4.text())) if int(self.LE_desc_4.text()) < self.serial_num and self.movement == 1: QtWidgets.QMessageBox.warning(self, "Error", "برجاء ادخل عدد اكبر من السابق") else: if self.valueType == "COP_DISCOUNT_VAL": self.valueData = self.LE_desc_2.text() elif self.valueType == "COP_DISCOUNT_PERCENT": self.valueData = self.LE_desc_3.text() sql = "update COUPON set COP_DESC='" + self.LE_desc_1.text().strip() + "'," + self.valueType + "=" + self.valueData +","+self.Othertype+"="+"null"+",COP_SERIAL_COUNT=" + self.serialCount + ",COP_MULTI_USE=" + self.MultiUse + ",COP_MULTI_USE_COUNT=" + self.MultiCount + ",COP_CHANGED_BY='" + CL_userModule.user_name + "',COP_CHANGED_ON='" + creationDate + "',COP_VALID_FROM='" + self.Qdate_from.dateTime().toString( 'yyyy-MM-dd') + "',COP_VALID_TO='" + self.Qdate_to.dateTime().toString( 'yyyy-MM-dd') + "',COP_STATUS='" + str( self.CMB_CouponStatus.currentIndex()) + "',COP_TIME_FROM='"+str(self.Qtime_from.dateTime().toString('hh:mm'))+"',COP_TIME_TO='"+str(self.Qtime_to.dateTime().toString('hh:mm'))+"' where COP_ID='" + str( self.CMB_CouponDes.currentData()) + "'" print(sql) mycursor.execute(sql) if len(self.Qcombo_branch.currentData()) > 0: for i in self.Qcombo_branch.currentData(): self.new_branch_list.append(i) if len(self.branch_list) > len(self.new_branch_list): for row in self.branch_list: print(row) if row in self.new_branch_list: print("found") else: print("not found") mycursor = self.conn.cursor() sql5 = "update COUPON_BRANCH set STATUS= 0 where COUPON_ID='" + str( self.CMB_CouponDes.currentData()) + "' and BRANCH_NO = '" + row + "'" mycursor.execute(sql5) print(sql5) else: for row in self.new_branch_list: print(row) if row in self.branch_list: print("found") else: mycursor = self.conn.cursor() mycursor.execute( "SELECT * FROM COUPON_BRANCH where BRANCH_NO='" + row + "' and COUPON_ID='" + str( self.CMB_CouponDes.currentData()) + "'") record = mycursor.fetchall() if mycursor.rowcount > 0: mycursor = self.conn.cursor() sql8 = "update COUPON_BRANCH set STATUS= 1 where COUPON_ID='" + str( self.CMB_CouponDes.currentData()) + "' and BRANCH_NO = '" + row + "'" mycursor.execute(sql8) print(sql8) else: mycursor = self.conn.cursor() sql6 = "INSERT INTO COUPON_BRANCH (COMPANY_ID,BRANCH_NO,COUPON_ID,STATUS) VALUES (%s,%s,%s,%s)" val6 = ( str(self.Qcombo_company.currentData()[0]), row, str(self.CMB_CouponDes.currentData()), '1') mycursor.execute(sql6, val6) if(self.multiusage==1): mycursor = self.conn.cursor() sql9 = "update COUPON_SERIAL set COPS_STATUS= 0 where COUPON_ID='" + str( self.CMB_CouponDes.currentData()) + "' and COPS_SERIAL_type = 0" mycursor.execute(sql9) value = randint(0, 1000000000000) creationDate = str(datetime.today().strftime('%Y-%m-%d')) mycursor = self.conn.cursor() sql7 = "INSERT INTO COUPON_SERIAL (COUPON_ID,COPS_BARCODE,COPS_CREATED_BY,COPS_SERIAL_type,COPS_CREATED_On,COPS_PRINT_COUNT,COPS_STATUS) VALUES (%s,%s,%s,%s,%s,%s,%s)" val7 = ( str(self.CMB_CouponDes.currentData()), "HCOP" + bin(value), CL_userModule.user_name, self.serial_type, creationDate, 0, '1') mycursor.execute(sql7, val7) else: mycursor = self.conn.cursor() if(int(self.Special)==1): self.serial_num=0 print("num"+str(self.serial_num)) sql9 = "update COUPON_SERIAL set COPS_STATUS= 0 where COUPON_ID='" + str( self.CMB_CouponDes.currentData()) + "' and COPS_SERIAL_type = 1" mycursor.execute(sql9) if int(self.LE_desc_4.text()) < self.serial_num: indx = self.CMB_CouponDes.currentData() sql_select_Query = "SELECT COPS_SERIAL_ID FROM COUPON_SERIAL where COUPON_ID = %s and COPS_STATUS = 1 and COPS_SERIAL_type = 0" x = (indx,) mycursor = self.conn.cursor() mycursor.execute(sql_select_Query, x) record = mycursor.fetchall() print(record) num = 0 for row in range(self.serial_num - int(self.LE_desc_4.text())): mycursor = self.conn.cursor() sql9 = "update COUPON_SERIAL set COPS_STATUS= 0 where COUPON_ID='" + str( self.CMB_CouponDes.currentData()) + "' and COPS_SERIAL_ID = '" + str( record[num][0]) + "'" mycursor.execute(sql9) print(sql9) num += 1 self.serial_num = int(self.LE_desc_4.text()) else: for row in range(int(self.LE_desc_4.text()) - self.serial_num): value = randint(0, 1000000000000) creationDate = str(datetime.today().strftime('%Y-%m-%d')) mycursor = self.conn.cursor() sql7 = "INSERT INTO COUPON_SERIAL (COUPON_ID,COPS_BARCODE,COPS_CREATED_BY,COPS_SERIAL_type,COPS_CREATED_On,COPS_PRINT_COUNT,COPS_STATUS) VALUES (%s,%s,%s,%s,%s,%s,%s)" val7 = ( str(self.CMB_CouponDes.currentData()), "HCOP" + bin(value), CL_userModule.user_name, self.serial_type, creationDate, 0, '1') mycursor.execute(sql7, val7) self.serial_num = int(self.LE_desc_4.text()) if (self.LE_desc_1.text() != self.DescOldValue): CL_userModule.FN_AddLog(self,'COUPON', 'COP_DESC', self.DescOldValue, self.LE_desc_1.text().strip(), creationDate, CL_userModule.user_name,self.row,None,None,None,None,mycursor) elif (self.CMB_CouponStatus.currentIndex() != self.oldstatus): sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY) VALUES (%s,%s,%s,%s,%s,%s,%s)" val8 = (self.row, 'COUPON', 'STATUS', self.oldstatus, str(self.CMB_CouponStatus.currentIndex()), creationDate, CL_userModule.user_name) mycursor.execute(sql8, val8) elif collections.Counter(self.Qcombo_branch.currentData())== collections.Counter(self.oldlist): print("the same list") elif len(collections.Counter(self.Qcombo_branch.currentData())) > len(collections.Counter(self.oldlist)): print(self.Diff(self.newlist, self.oldlist)) if len(collections.Counter(self.Qcombo_branch.currentData())) > len(collections.Counter(record)): for row in self.Diff(record, self.newlist): sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY,ROW_KEY_ID2) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)" val8 = (self.row, 'COUPON_BRANCH', 'STATUS', "null", "1", creationDate, CL_userModule.user_name, row) mycursor.execute(sql8, val8) else: for row in self.Diff(self.oldlist, self.newlist): sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY,ROW_KEY_ID2) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)" val8 = (self.row, 'COUPON_BRANCH', 'STATUS', "0", "1", creationDate, CL_userModule.user_name, row) mycursor.execute(sql8, val8) elif len(collections.Counter(self.Qcombo_branch.currentData())) < len(collections.Counter(self.oldlist)): print(self.Diff(self.oldlist, self.newlist)) for row in self.Diff(self.oldlist, self.newlist): sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY,ROW_KEY_ID2) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)" val8 = (self.row, 'COUPON_BRANCH', 'STATUS', "1", "0", creationDate, CL_userModule.user_name, row) mycursor.execute(sql8, val8) elif(self.LE_desc_2.text() != self.COPDISCOUNToldVAL): sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY) VALUES (%s,%s,%s,%s,%s,%s,%s)" val8 = (self.row, 'COUPON', 'COP_DISCOUNT_VAL', self.COPDISCOUNToldVAL, self.LE_desc_2.text().strip(), creationDate, CL_userModule.user_name) mycursor.execute(sql8, val8) elif (self.LE_desc_3.text() != self.COPDISCOUNToldprecnt): sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY) VALUES (%s,%s,%s,%s,%s,%s,%s)" val8 = (self.row, 'COUPON', 'COP_DISCOUNT_PERCENT', self.COPDISCOUNToldprecnt, self.LE_desc_3.text().strip(), creationDate, CL_userModule.user_name) mycursor.execute(sql8, val8) db1.connectionCommit(self.conn) mycursor.close() QtWidgets.QMessageBox.warning(self, "Done", "Done") for i in self.FN_GetMathchBranch(): self.branch_list.append(i) self.FN_getDatabyID() except: print(sys.exc_info()) # Todo: method when make coupon use DISCOUNT_VAL def FN_EnableDiscVal(self): self.valueType="COP_DISCOUNT_VAL" self.LE_desc_2.setEnabled(True) self.LE_desc_3.setEnabled(False) self.Othertype="COP_DISCOUNT_PERCENT" # Todo: method when make coupon use DISCOUNT_PERCENT def FN_EnablePercentage(self): self.valueType = "COP_DISCOUNT_PERCENT" self.LE_desc_3.setEnabled(True) self.LE_desc_2.setEnabled(False) self.Othertype="COP_DISCOUNT_VAL" # Todo: method to clear edit text def FN_Clear(self): self.LE_desc_1.clear() self.LE_desc_2.clear() self.LE_desc_3.clear() self.LE_desc_4.clear() self.LE_desc_5.clear() # Todo: method to get company assigned to coupon def FN_SELECT_company(self): indx = self.CMB_CouponDes.currentData() mycursor = self.conn.cursor() sql="SELECT COMPANY_ID FROM COUPON_BRANCH where COUPON_ID = %s" c = (indx,) mycursor.execute(sql, c) records = mycursor.fetchall() mycursor.close() return records # Todo: method to get branch assigned to coupon def FN_SELECT_branch(self): indx = self.CMB_CouponDes.currentData() mycursor = self.conn.cursor() sql="SELECT BRANCH_NO , STATUS FROM COUPON_BRANCH where COUPON_ID = %s" c = (indx,) mycursor.execute(sql,c) records = mycursor.fetchall() mycursor.close() return records # Todo: method to get check company assigned to coupon def FN_check_company(self, indx): mycursor = self.conn.cursor() sql_select_company ="SELECT COMPANY_ID FROM COMPANY" mycursor.execute(sql_select_company) record = mycursor.fetchall() i = 0 for row in record: for row1 in self.FN_SELECT_company(): if row[0] == row1[0]: items = self.Qcombo_company.findText(row[0]) for item in range(items + 2): self.Qcombo_company.setChecked(i) i = i + 1 mycursor.close() # Todo: method to get check branch assigned to coupon def FN_check_branch(self,index): self.FN_unCheckedALL() mycursor = self.conn.cursor() sql_select_branch = "SELECT BRANCH_NO FROM BRANCH" mycursor.execute(sql_select_branch) record = mycursor.fetchall() i = 0 for row in record: for row1 in self.FN_SELECT_branch(): if row[0] == row1[0]: items = self.Qcombo_branch.findText(row[0]) for item in range(items +2): if int(row1[1])==1: self.Qcombo_branch.setChecked(i) i = i + 1 mycursor.close() # Todo: method refresh Qcombo_branch def FN_unCheckedALL(self): i=0 for row in CL_userModule.branch: self.Qcombo_branch.unChecked(i) i+=1 # Todo: method get branch has this coupon def FN_GetMathchBranch(self): indx = self.CMB_CouponDes.currentData() mycursor = self.conn.cursor() sql = "SELECT BRANCH_NO FROM COUPON_BRANCH where COUPON_ID = %s and STATUS = 1" c = (indx,) mycursor.execute(sql, c) records = mycursor.fetchall() mycursor.close() return records # Todo: method get diff between two list def Diff(self,li1, li2): return list(set(li1) - set(li2)) + list(set(li2) - set(li1))
class CL_Pos_Parameters(QtWidgets.QDialog): branch=[] def __init__(self): super(CL_Pos_Parameters, self).__init__() cwd = Path.cwd() mod_path = Path(__file__).parent.parent.parent self.dirname = mod_path.__str__() + '/presentation/configuration_ui' self.conn = db1.connect() def FN_LOAD_CREATE(self): filename = self.dirname + '/Pos_Parameter.ui' loadUi(filename, self) self.setWindowTitle('Pos_Parameters') css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) self.Qcombo_parameter = CheckableComboBox(self) self.Qcombo_parameter.setGeometry(50, 160, 271, 25) self.Qcombo_parameter.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_parameter.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_Company() self.FN_GET_Branch() self.FN_GET_Parameter() self.Qcombo_branch.activated[str].connect(self.FN_GET_POS) self.CMB_CouponStatus.addItems(["Inactive", "Active"]) self.pushButton.clicked.connect(self.FN_create) def FN_GET_Company(self): self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY") records = mycursor.fetchall() print(records) for row, val in records: self.Qcombo_company.addItem(row, val) mycursor.close() def FN_GET_Branch(self): i = 0 try: for row, val in CL_userModule.branch: self.Qcombo_branch.addItem(val, row) self.branch.append(row) i += 1 except: print(sys.exc_info()) def FN_GET_POS(self): try: self.Qcombo_pos.clear() self.conn = db1.connect() mycursor = self.conn.cursor() print("SELECT POS_NO , POS_NO FROM POS where BRANCH_NO ='"+str(self.Qcombo_branch.currentData())+"'") mycursor.execute("SELECT POS_NO , POS_NO FROM POS where BRANCH_NO ='"+str(self.Qcombo_branch.currentData())+"'") records = mycursor.fetchall() for row, val in records: self.Qcombo_pos.addItem(row, val) mycursor.close() except: print(sys.exc_info()) def FN_GET_Parameter(self): try: self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT PARAMETER_DESC,PARAMETER_ID FROM SYS_CONFIG_PARAMETERS") records = mycursor.fetchall() for row, val in records: self.Qcombo_parameter.addItem(row, val) mycursor.close() except: print(sys.exc_info()) def FN_create(self): self.conn = db1.connect() self.conn.autocommit = False mycursor = self.conn.cursor() self.conn.start_transaction() if len(self.Qcombo_company.currentData()) == 0 or len(self.Qcombo_branch.currentData()) == 0: QtWidgets.QMessageBox.warning(self, "خطا", "اكمل العناصر الفارغه") else: try: sql0 = " LOCK TABLES Hyper1_Retail.POS_PARAMETER_POS WRITE " mycursor.execute(sql0) for row in range(len(self.Qcombo_parameter.currentData())): sql = "INSERT INTO POS_PARAMETER_POS (COMPANY_ID,BRANCH_NO,POS_NO,PARAMETER_ID,STATUS)" \ " VALUES (%s, %s, %s, %s, %s) " val = (self.Qcombo_company.currentData(), self.Qcombo_branch.currentData(),self.Qcombo_pos.currentData(),self.Qcombo_parameter.currentData()[row],self.CMB_CouponStatus.currentIndex()) mycursor.execute(sql, val) sql00 = " UNLOCK tables " mycursor.execute(sql00) db1.connectionCommit(self.conn) mycursor.close() QtWidgets.QMessageBox.warning(self, "Done", "تم الانشاء") except : print(sys.exc_info()) self.conn.rollback() finally: if self.conn.is_connected(): mycursor.close() self.conn.close() print("connection is closed")
class CL_EditVoucher(QtWidgets.QDialog): GV_REFUNDABLE = 0 GV_RECHARGABLE = 0 GV_MULTIUSE = 0 recharge = 0 branch_list = [] new_branch_list = [] section_list = [] new_section_list = [] searchpos=False oldValue="" oldlist = [] newlist = [] row="" def __init__(self): super(CL_EditVoucher, self).__init__() cwd = Path.cwd() mod_path = Path(__file__).parent.parent.parent self.dirname = mod_path.__str__() + '/presentation/voucher_ui' self.conn = db1.connect() # Todo: method to load ui of editVoucher def FN_LOADUI(self): try: filename = self.dirname + '/editVoucher.ui' loadUi(filename, self) css_path = Path(__file__).parent.parent.parent # Apply Style For Design path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) self.Qcombo_company = CheckableComboBox(self) self.Qcombo_company.setGeometry(580, 160, 271, 25) self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_company.setStyleSheet("background-color: rgb(198, 207, 199)") self.Qcombo_branch = CheckableComboBox(self) self.Qcombo_branch.setGeometry(580, 200, 271, 25) self.Qcombo_branch.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_branch.setStyleSheet("background-color: rgb(198, 207, 199)") self.Qcombo_section = CheckableComboBox(self) self.Qcombo_section.setGeometry(580, 240, 271, 25) self.Qcombo_section.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_section.setStyleSheet("background-color: rgb(198, 207, 199)") self.FN_GET_Company() self.FN_GET_Branch() self.FN_GET_Section() self.FN_GET_sponsor() self.CMB_CouponStatus.addItems(["Inactive", "Active"]) self.FN_getData() self.FN_getDatabyID() self.CMB_CouponDes.activated[str].connect(self.FN_getDatabyID) self.checkBox_Multi.toggled.connect(self.FN_multiuse) self.checkBox_rechange.toggled.connect(self.FN_Rechangable) self.checkBox_refundable.toggled.connect(self.FN_Refundable) self.BTN_editCoupon.clicked.connect(self.FN_editAction) self.LE_desc_5.textChanged.connect(self.FN_search) except: print(sys.exc_info()) # Todo: method for fills the company combobox def FN_GET_Company(self): self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY") records = mycursor.fetchall() for row, val in records: self.Qcombo_company.addItem(row, val) mycursor.close() # Todo: method for fills the Branch combobox def FN_GET_Branch(self): i = 0 try: for row, val in CL_userModule.branch: self.Qcombo_branch.addItem(val, row) i += 1 except: print(sys.exc_info()) # Todo: method to get company of voucher def FN_SELECT_company(self): indx = self.CMB_CouponDes.currentData() mycursor = self.conn.cursor() sql = "SELECT COMPANY_ID FROM VOUCHER_BRANCH where GV_ID = %s" c = (indx,) mycursor.execute(sql, c) records = mycursor.fetchall() mycursor.close() return records # Todo: method to get branches of voucher def FN_SELECT_branch(self): indx = self.CMB_CouponDes.currentData() mycursor = self.conn.cursor() sql = "SELECT BRANCH_NO , STATUS FROM VOUCHER_BRANCH where GV_ID = %s" c = (indx,) mycursor.execute(sql, c) records = mycursor.fetchall() mycursor.close() return records # Todo: method to get sections of voucher def FN_SELECT_section(self): indx = self.CMB_CouponDes.currentData() mycursor = self.conn.cursor() sql = "SELECT SECTION_ID , STATUS FROM VOUCHER_SECTION where GV_ID = %s" c = (indx,) mycursor.execute(sql, c) records = mycursor.fetchall() mycursor.close() return records # Todo: method to check company assgined to voucher def FN_check_company(self, indx): mycursor = self.conn.cursor() sql_select_company = "SELECT COMPANY_ID FROM COMPANY" mycursor.execute(sql_select_company) record = mycursor.fetchall() i = 0 for row in record: for row1 in self.FN_SELECT_company(): if row[0] == row1[0]: items = self.Qcombo_company.findText(row[0]) for item in range(items + 2): self.Qcombo_company.setChecked(i) i = i + 1 mycursor.close() # Todo: method to check branch assgined to voucher def FN_check_branch(self, index): self.FN_unCheckedALL() mycursor = self.conn.cursor() sql_select_branch = "SELECT BRANCH_NO FROM SYS_USER_BRANCH where USER_ID='" + CL_userModule.user_name + "'" mycursor.execute(sql_select_branch) record = mycursor.fetchall() i = 0 for row in record: for row1 in self.FN_SELECT_branch(): if row[0] == row1[0]: items = self.Qcombo_branch.findText(row[0]) for item in range(items + 2): if int(row1[1]) == 1: self.Qcombo_branch.setChecked(i) i = i + 1 mycursor.close() # Todo: method to check section assgined to voucher def FN_check_section(self, index): self.FN_unCheckedALLsection() mycursor = self.conn.cursor() sql_select_branch = "SELECT SECTION_ID FROM SYS_USER_SECTION where USER_ID='" + CL_userModule.user_name + "'" mycursor.execute(sql_select_branch) record = mycursor.fetchall() i = 0 for row in record: for row1 in self.FN_SELECT_section(): if row[0] == row1[0]: items = self.Qcombo_section.findText(row[0]) for item in range(items + 2): if int(row1[1]) == 1: self.Qcombo_section.setChecked(i) i = i + 1 mycursor.close() # Todo: method to refresh Qcombo_branch def FN_unCheckedALL(self): i = 0 for row in CL_userModule.branch: self.Qcombo_branch.unChecked(i) i += 1 # Todo: method to refresh Qcombo_section def FN_unCheckedALLsection(self): i = 0 for row in CL_userModule.section: self.Qcombo_section.unChecked(i) i += 1 # Todo: method to get branch of voucher def FN_GetMathchBranch(self): indx = self.CMB_CouponDes.currentData() mycursor = self.conn.cursor() sql = "SELECT BRANCH_NO FROM VOUCHER_BRANCH where GV_ID = %s and STATUS = 1" c = (indx,) mycursor.execute(sql, c) records = mycursor.fetchall() mycursor.close() return records # Todo: method for fills the section combobox def FN_GET_Section(self): try: for row, val,row1,val1 in CL_userModule.section: self.Qcombo_section.addItem(val, row) except: print(sys.exc_info()) # Todo: method for fills the sponsor combobox def FN_GET_sponsor(self): self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT SPONSOR_NAME,SPONSOR_ID FROM SPONSOR") records = mycursor.fetchall() print(records) for row, val in records: self.Qcombo_sponser.addItem(row, val) mycursor.close() # Todo: method for get all voucher def FN_getData(self): self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT GV_DESC,GV_ID FROM VOUCHER where GVT_ID in (2,3)") records = mycursor.fetchall() for row, val in records: self.CMB_CouponDes.addItem(row, val) mycursor.close() # Todo: method for get data about voucher def FN_getDatabyID(self): try: self.branch_list = [] self.new_branch_list = [] self.section_list = [] self.new_section_list = [] indx = self.CMB_CouponDes.currentData() self.labe_id.setText(str(indx)) self.conn = db1.connect() mycursor = self.conn.cursor() sql_select_Query = "SELECT * FROM VOUCHER where GV_ID = %s" x = (indx,) mycursor.execute(sql_select_Query, x) record = mycursor.fetchone() self.row=str(record[0]) self.LE_desc_1.setText(str(record[1])) self.LE_desc_2.setValue(float(record[4])) self.CMB_CouponStatus.setCurrentIndex(int(record[22])) self.LE_desc_5.setText(str(record[19])) self.FN_search() timefrom = record[13] tfrom = timefrom.split(":") some_time = QtCore.QTime(int(tfrom[0]), int(tfrom[1]), 00) self.Qtime_from.setTime(some_time) timeto = record[15] tto = timeto.split(":") some_time = QtCore.QTime(int(tto[0]), int(tto[1]), 00) self.Qtime_to.setTime(some_time) self.oldValue=record[1] datefrom = record[12] xfrom = datefrom.split("-") self.dfrom = QDate(int(xfrom[0]), int(xfrom[1]), int(xfrom[2])) self.Qdate_from.setDate(self.dfrom) self.dfrom = QDateTime(int(xfrom[0]), int(xfrom[1]), int(xfrom[2]), 00, 00, 00, 00) dateto = record[14] xto = dateto.split("-") d = QDate(int(xto[0]), int(xto[1]), int(xto[2])) self.Qdate_to.setDate(d) if int(record[16]) == 1: self.checkBox_refundable.setChecked(True) else: self.checkBox_refundable.setChecked(False) if int(record[17]) == 1: self.checkBox_rechange.setChecked(True) self.LE_desc_3.setEnabled(True) else: self.checkBox_rechange.setChecked(False) self.LE_desc_3.setEnabled(False) if int(record[18]) == 1: self.checkBox_Multi.setChecked(True) else: self.checkBox_Multi.setChecked(False) self.FN_check_section(indx) self.FN_check_company(indx) self.FN_check_branch(indx) sql_select = "select * from SPONSOR where SPONSOR_ID=( SELECT SPONSOR_ID FROM VOUCHER_SPONSOR where GV_ID = %s)" x = (indx,) mycursor.execute(sql_select, x) record = mycursor.fetchone() self.Qcombo_sponser.setCurrentText(record[2]) self.branch_list.clear() self.section_list.clear() if len(self.Qcombo_branch.currentData()) > 0: for i in self.Qcombo_branch.currentData(): self.branch_list.append(i) if len(self.Qcombo_section.currentData()) > 0: for x in self.Qcombo_section.currentData(): self.section_list.append(x) self.oldlist=self.Qcombo_branch.currentData() except: print(sys.exc_info()) # Todo: method to make voucher multiuse def FN_multiuse(self): if self.checkBox_Multi.isChecked(): self.GV_MULTIUSE = 1 else: self.GV_MULTIUSE = 0 # Todo: method to make voucher Rechangable def FN_Rechangable(self): if self.checkBox_rechange.isChecked(): self.GV_RECHARGABLE = 1 self.LE_desc_3.setEnabled(True) else: self.GV_RECHARGABLE = 0 self.LE_desc_3.setEnabled(False) # Todo: method to make voucher Refundable def FN_Refundable(self): if self.checkBox_refundable.isChecked(): self.GV_REFUNDABLE = 1 else: self.GV_REFUNDABLE = 0 # Todo: method to edit voucher def FN_editAction(self): try: self.FN_search() self.newlist = self.Qcombo_branch.currentData() if len(self.Qcombo_company.currentData()) == 0 or len(self.Qcombo_branch.currentData()) == 0 or len( self.LE_desc_1.text().strip()) == 0 or len(self.Qcombo_section.currentData()) == 0 or len( self.LE_desc_5.text().strip()) == 0: QtWidgets.QMessageBox.warning(self, "خطا", "اكمل العناصر الفارغه") elif (self.Qdate_from.date() == self.Qdate_to.date()) and int( self.Qtime_from.dateTime().toString('hh')) + int(self.Qtime_from.dateTime().toString('mm')) > int( self.Qtime_to.dateTime().toString('hh')) + int(self.Qtime_to.dateTime().toString('mm')): QtWidgets.QMessageBox.warning(self, "خطا", "وقت الانتهاء يجب ان يكون اكبر من او يساوي وقت الانشاء") else: if self.Qdate_to.dateTime() < self.Qdate_from.dateTime(): QtWidgets.QMessageBox.warning(self, "Done", "تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء") elif self.Qdate_from.dateTime() < self.dfrom: QtWidgets.QMessageBox.warning(self, "Done", "تاريخ الانشاء الجديد يجب ان يكون اكبر او يساوي تاريخ الانشاء قبل التعديل") elif self.searchpos== False : QtWidgets.QMessageBox.warning(self, "Done", "العميل غير موجود") else: mycursor = self.conn.cursor() creationDate = str(datetime.today().strftime('%Y-%m-%d')) sql = "update VOUCHER set GV_DESC='" + self.LE_desc_1.text().strip() + "',GV_RECHARGE_VALUE='" + self.LE_desc_3.text().strip() + "',GV_REFUNDABLE=" + str( self.GV_REFUNDABLE) + ",GV_RECHARGABLE=" + str(self.GV_RECHARGABLE) + ",GV_MULTIUSE=" + str( self.GV_MULTIUSE) + " ,GV_CHANGED_BY='" + CL_userModule.user_name + "',GV_CHANGE_ON='" + creationDate + "',GV_VALID_FROM='" + self.Qdate_from.dateTime().toString( 'yyyy-MM-dd') + "',GV_VALID_TO='" + self.Qdate_to.dateTime().toString( 'yyyy-MM-dd') + "',GV_STATUS='" + str(self.CMB_CouponStatus.currentIndex()) + "',POSC_CUST_ID='"+self.LE_desc_5.text().strip()+"' ,GV_TIME_FROM='"+str(self.Qtime_from.dateTime().toString('hh:mm'))+"',GV_TIME_TO='"+str(self.Qtime_to.dateTime().toString('hh:mm'))+"' where GV_ID='" + str(self.CMB_CouponDes.currentData()) + "'" print(sql) mycursor.execute(sql) if len(self.Qcombo_branch.currentData()) > 0: for i in self.Qcombo_branch.currentData(): self.new_branch_list.append(i) if len(self.Qcombo_section.currentData()) > 0: for i in self.Qcombo_section.currentData(): self.new_section_list.append(i) if len(self.branch_list) > len(self.new_branch_list): for row in self.branch_list: print(row) if row in self.new_branch_list: print("found") else: print("not found") mycursor = self.conn.cursor() sql5 = "update VOUCHER_BRANCH set STATUS= 0 where GV_ID='" + str( self.CMB_CouponDes.currentData()) + "' and BRANCH_NO = '" + row + "'" mycursor.execute(sql5) print(sql5) else: for row in self.new_branch_list: print(row) if row in self.branch_list: print("found") else: mycursor = self.conn.cursor() mycursor.execute( "SELECT * FROM VOUCHER_BRANCH where BRANCH_NO='" + row + "' and GV_ID='" + str( self.CMB_CouponDes.currentData()) + "'") record = mycursor.fetchall() if mycursor.rowcount > 0: mycursor = self.conn.cursor() sql8 = "update VOUCHER_BRANCH set STATUS= 1 where GV_ID='" + str( self.CMB_CouponDes.currentData()) + "' and BRANCH_NO = '" + row + "'" mycursor.execute(sql8) print(sql8) else: mycursor = self.conn.cursor() sql6 = "INSERT INTO VOUCHER_BRANCH (COMPANY_ID,BRANCH_NO,GV_ID,STATUS) VALUES (%s,%s,%s,%s)" val6 = ( str(self.Qcombo_company.currentData()[0]), row, str(self.CMB_CouponDes.currentData()), '1') mycursor.execute(sql6, val6) if len(self.section_list) > len(self.new_section_list): for row in self.section_list: print(row) if row in self.new_section_list: print("found") else: print("not found") mycursor = self.conn.cursor() sql5 = "update VOUCHER_SECTION set STATUS= 0 where GV_ID='" + str( self.CMB_CouponDes.currentData()) + "' and SECTION_ID = '" + row + "'" mycursor.execute(sql5) print(sql5) else: for row in self.new_branch_list: print(row) if row in self.branch_list: print("found") else: mycursor = self.conn.cursor() mycursor.execute( "SELECT * FROM VOUCHER_SECTION where SECTION_ID='" + row + "' and GV_ID='" + str( self.CMB_CouponDes.currentData()) + "'") record = mycursor.fetchall() if mycursor.rowcount > 0: mycursor = self.conn.cursor() sql8 = "update VOUCHER_SECTION set STATUS= 1 where GV_ID='" + str( self.CMB_CouponDes.currentData()) + "' and SECTION_ID = '" + row + "'" mycursor.execute(sql8) print(sql8) else: mycursor = self.conn.cursor() sql6 = "INSERT INTO VOUCHER_SECTION (SECTION_ID,GV_ID,STATUS) VALUES (%s,%s,%s)" val6 = ( row, str(self.CMB_CouponDes.currentData()), '1') mycursor.execute(sql6, val6) if (self.LE_desc_1.text() != self.oldValue): sql7 = "INSERT INTO SYS_CHANGE_LOG (TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY) VALUES (%s,%s,%s,%s,%s,%s)" val7 = ('VOUCHER', 'GV_DESC', self.oldValue, self.LE_desc_1.text().strip(), creationDate, CL_userModule.user_name) mycursor.execute(sql7, val7) elif collections.Counter(self.Qcombo_branch.currentData()) == collections.Counter(self.oldlist): print("the same list") elif len(collections.Counter(self.Qcombo_branch.currentData())) > len( collections.Counter(self.oldlist)): print(self.Diff(self.newlist, self.oldlist)) if len(collections.Counter(self.Qcombo_branch.currentData())) > len( collections.Counter(record)): for row in self.Diff(record, self.newlist): sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY,ROW_KEY_ID2) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)" val8 = (self.row, 'COUPON_BRANCH', 'STATUS', "null", "1", creationDate, CL_userModule.user_name, row) mycursor.execute(sql8, val8) else: for row in self.Diff(self.oldlist, self.newlist): sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY,ROW_KEY_ID2) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)" val8 = (self.row, 'COUPON_BRANCH', 'STATUS', "0", "1", creationDate, CL_userModule.user_name, row) mycursor.execute(sql8, val8) elif len(collections.Counter(self.Qcombo_branch.currentData())) < len( collections.Counter(self.oldlist)): print(self.Diff(self.oldlist, self.newlist)) for row in self.Diff(self.oldlist, self.newlist): sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY,ROW_KEY_ID2) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)" val8 = (self.row, 'COUPON_BRANCH', 'STATUS', "1", "0", creationDate, CL_userModule.user_name, row) mycursor.execute(sql8, val8) db1.connectionCommit(self.conn) mycursor.close() self.FN_getDatabyID() QtWidgets.QMessageBox.warning(self, "Done", "Done") except: print(sys.exc_info()) # Todo: method to search about customer def FN_search(self): try: self.conn = db1.connect() mycursor = self.conn.cursor() name = self.LE_desc_5.text().strip() sql_select_Query = "select * from POS_CUSTOMER where POSC_CUST_ID = '" + name + "'" mycursor.execute(sql_select_Query) records = mycursor.fetchone() if mycursor.rowcount > 0: self.desc_13.setText(str(records[3])) self.searchpos=True else: self.desc_13.setText("العميل غير موجود") self.searchpos=False mycursor.close() except: print(sys.exc_info()) # Todo: method get diff between two list def Diff(self,li1, li2): return list(set(li1) - set(li2)) + list(set(li2) - set(li1))
class CL_Pos_Parameters_Modify(QtWidgets.QDialog): branch = [] parameter = [] parameterlist = [] new_parameter_list = [] def __init__(self): super(CL_Pos_Parameters_Modify, self).__init__() cwd = Path.cwd() mod_path = Path(__file__).parent.parent.parent self.dirname = mod_path.__str__() + '/presentation/configuration_ui' self.conn = db1.connect() def FN_LOAD_CREATE(self): filename = self.dirname + '/Pos_Parameter_Modify.ui' loadUi(filename, self) self.setWindowTitle('Pos_Parameters_Modify') css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) self.Qcombo_parameter = CheckableComboBox(self) self.Qcombo_parameter.setGeometry(50, 160, 271, 25) self.Qcombo_parameter.setLayoutDirection(QtCore.Qt.LeftToRight) self.Qcombo_parameter.setStyleSheet( "background-color: rgb(198, 207, 199)") self.FN_GET_Company() self.FN_GET_Branch() self.FN_GET_Parameter() self.Qcombo_branch.activated[str].connect(self.FN_GET_POS) self.Qcombo_pos.activated[str].connect(self.FN_check_branch) self.CMB_CouponStatus.addItems(["Inactive", "Active"]) self.pushButton.clicked.connect(self.FN_Edit) def FN_GET_Company(self): self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY") records = mycursor.fetchall() print(records) for row, val in records: self.Qcombo_company.addItem(row, val) mycursor.close() def FN_GET_Branch(self): i = 0 try: for row, val in CL_userModule.branch: self.Qcombo_branch.addItem(val, row) self.branch.append(row) i += 1 except: print(sys.exc_info()) def FN_GET_POS(self): try: self.Qcombo_pos.clear() self.conn = db1.connect() mycursor = self.conn.cursor() print("SELECT POS_NO , POS_NO FROM POS where BRANCH_NO ='" + str(self.Qcombo_branch.currentData()) + "'") mycursor.execute( "SELECT POS_NO , POS_NO FROM POS where BRANCH_NO ='" + str(self.Qcombo_branch.currentData()) + "'") records = mycursor.fetchall() for row, val in records: self.Qcombo_pos.addItem(row, val) mycursor.close() except: print(sys.exc_info()) def FN_GET_Parameter(self): try: self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute( "SELECT PARAMETER_DESC,PARAMETER_ID FROM SYS_CONFIG_PARAMETERS" ) records = mycursor.fetchall() for row, val in records: self.Qcombo_parameter.addItem(row, val) self.parameter.append(val) mycursor.close() except: print(sys.exc_info()) def FN_unCheckedALL(self): i = 0 for row in self.parameter: self.Qcombo_parameter.unChecked(i) i += 1 def FN_check_branch(self): try: self.FN_unCheckedALL() mycursor = self.conn.cursor() sql_select_branch = "SELECT PARAMETER_ID FROM POS_PARAMETER_POS where POS_NO='" + self.Qcombo_pos.currentData( ) + "'" mycursor.execute(sql_select_branch) record = mycursor.fetchall() print(self.parameter) i = 0 for row in record: for row1 in self.parameter: if row[0] == int(row1[0]): self.Qcombo_parameter.setChecked(i) i = i + 1 mycursor.close() if len(self.Qcombo_parameter.currentData()) > 0: for i in self.Qcombo_parameter.currentData(): self.parameterlist.append(i) except: print(sys.exc_info()) def FN_Edit(self): self.new_parameter_list.clear() try: mycursor = self.conn.cursor() if len(self.Qcombo_parameter.currentData()) > 0: for i in self.Qcombo_parameter.currentData(): self.new_parameter_list.append(i) if len(self.parameterlist) > len(self.new_parameter_list): for row in self.parameterlist: print(row) if row in self.new_parameter_list: print("found") else: print("not found") mycursor = self.conn.cursor() sql5 = "delete from POS_PARAMETER_POS where PARAMETER_ID ='" + row + "' and BRANCH_NO ='" + self.Qcombo_branch.currentData( ) + "' and POS_NO='" + self.Qcombo_pos.currentData( ) + "'" print(sql5) mycursor.execute(sql5) else: for row in self.new_parameter_list: print(row) if row in self.parameterlist: print("found") else: mycursor = self.conn.cursor() sql6 = "INSERT INTO POS_PARAMETER_POS (COMPANY_ID,PARAMETER_ID,POS_NO,BRANCH_NO,STATUS) VALUES (%s,%s,%s,%s,%s)" val6 = (str(self.Qcombo_company.currentData()), row, self.Qcombo_pos.currentData(), self.Qcombo_branch.currentData(), self.CMB_CouponStatus.currentIndex()) mycursor.execute(sql6, val6) db1.connectionCommit(self.conn) mycursor.close() QtWidgets.QMessageBox.warning(self, "Done", "Done") self.parameterlist.clear() self.FN_check_branch() print(self.new_parameter_list) print(self.parameterlist) except: print(sys.exc_info())
class CL_installmentReport(QtWidgets.QDialog): switch_window = QtCore.pyqtSignal() dirname = '' field_names = [ 'رقم البرنامج', 'اسم البرنامج', 'مجموعات العملاء', 'الشركه', 'الفرع', 'الإداره', 'القسم', 'من تاريخ', 'إلى تاريخ', 'المصاريف الإداريه', 'الحاله', 'مده التقسيط', 'تقسيط هايبر', 'تقسيط بنك', 'تقسيط مورد', 'نسبه هايبر', 'نسبه المورد', 'نسبه العميل' ] barcodes = [] def __init__(self): super(CL_installmentReport, self).__init__() cwd = Path.cwd() mod_path = Path(__file__).parent.parent.parent self.dirname = mod_path.__str__() + '/presentation/reports_ui' conn = db1.connect() def FN_EXPORT(self): try: filename = QFileDialog.getSaveFileName(self, "Save File", '', "(*.xls)") wb = xlwt.Workbook() # add_sheet is used to create sheet. sheet = wb.add_sheet('Sheet 1') sheet.write(0, 0, 'رقم البرنامج') sheet.write(0, 1, 'اسم البرنامج') sheet.write(0, 2, 'مجموعات العملاء') sheet.write(0, 3, 'الشركه') sheet.write(0, 4, 'الفرع') sheet.write(0, 5, 'الإداره') sheet.write(0, 6, 'القسم') sheet.write(0, 7, 'من تاريخ') sheet.write(0, 8, 'إلى تاريخ') sheet.write(0, 9, 'المصاريف الإداريه') sheet.write(0, 10, 'الحاله') sheet.write(0, 11, 'مده التقسيط') sheet.write(0, 12, 'تقسيط هايبر') sheet.write(0, 13, 'تقسيط بنك') sheet.write(0, 14, 'تقسيط مورد') sheet.write(0, 15, 'نسبه هايبر') sheet.write(0, 16, 'نسبه المورد') sheet.write(0, 17, 'نسبه العميل') rowNo = self.Qtable_inst.rowCount() + 1 for currentColumn in range(self.Qtable_inst.columnCount()): for currentRow in range(self.Qtable_inst.rowCount()): teext = str( self.Qtable_inst.item(currentRow, currentColumn).text()) if teext != None: sheet.write(currentRow + 1, currentColumn, teext) else: sheet.write(currentRow + 1, currentColumn, ' ') # # wb.save('test11.xls') wb.save(str(filename[0])) # wb.close() import webbrowser webbrowser.open(filename[0]) except Exception as err: print(err) def FN_LOAD_DISPLAY(self): try: filename = self.dirname + '/installment.ui' loadUi(filename, self) conn = db1.connect() mycursor = conn.cursor() self.Qbtn_search.clicked.connect(self.FN_SEARCH) self.Qbtn_export.clicked.connect(self.FN_EXPORT) for row_number, row_data in enumerate(CL_userModule.myList): if row_data[1] == 'Installment': if row_data[4] == 'None': print('hh') else: sql_select_query = "select i.ITEM_DESC from Hyper1_Retail.SYS_FORM_ITEM i where ITEM_STATUS= 1 and i.item_id =%s" x = (row_data[4], ) mycursor.execute(sql_select_query, x) result = mycursor.fetchone() # print(result) if result[0] == 'search': self.Qbtn_search.setEnabled(True) elif result[0] == 'export': self.Qbtn_export.setEnabled(True) self.Qbtn_loadItems.clicked.connect(self.FN_LOAD_BARCODES) # Apply Style For Design css_path = Path(__file__).parent.parent.parent path = css_path.__str__() + '/presentation/Themes/Style.css' self.setStyleSheet(open(path).read()) #Get installment type self.Qcombo_installmentType.addItem("أختر المده", '0') self.FN_GET_installment_types_period() # test Multi selection for company #drob down list with multiselection for company self.Qcombo_company = CheckableComboBox(self) self.Qcombo_company.setGeometry(490, 20, 179, 20) self.Qcombo_company.setLayoutDirection(QtCore.Qt.RightToLeft) self.Qcombo_company.setStyleSheet( "background-color: rgb(198, 207, 199)") self.FN_GET_Company() # TODO Click listner for changing list of company self.Qcombo_company.model().dataChanged.connect(self.FN_GET_Branch) #drob down list with multiselection for bracnch self.Qcombo_branch = CheckableComboBox(self) self.Qcombo_branch.setGeometry(490, 60, 179, 20) self.Qcombo_branch.setLayoutDirection(QtCore.Qt.RightToLeft) self.Qcombo_branch.setStyleSheet( "background-color: rgb(198, 207, 199)") self.FN_GET_Branch() #validation for not pick date before today datefrom = str(datetime.today().strftime('%Y-%m-%d')) xfrom = datefrom.split("-") d = QDate(int(xfrom[0]), int(xfrom[1]), int(xfrom[2])) self.Qdate_from.setDate(d) self.Qdate_to.setDate(d) # Get customer Groupe self.Qcombo_customerGroupe = CheckableComboBox(self) self.Qcombo_customerGroupe.setGeometry(490, 100, 179, 20) self.Qcombo_customerGroupe.setLayoutDirection( QtCore.Qt.RightToLeft) self.Qcombo_customerGroupe.setStyleSheet( "background-color: rgb(198, 207, 199)") self.FN_GET_customerGroupe() # Multi selection for department self.Qcombo_department = CheckableComboBox(self) self.Qcombo_department.setGeometry(630, 190, 85, 22) self.Qcombo_department.setEnabled(False) self.Qcombo_department.setLayoutDirection(QtCore.Qt.RightToLeft) self.Qcombo_department.setStyleSheet( "background-color: rgb(198, 207, 199)") # TODO Click listner for changing list of department self.Qcombo_department.model().dataChanged.connect( self.FN_WhenChecksection) # get Department list if check box self.checkBox_department.stateChanged.connect( self.FN_WhenCheckDepartment) # self.FN_WhenCheckDepartment() # Multi selection for sections self.Qcombo_section = CheckableComboBox(self) self.Qcombo_section.setGeometry(490, 190, 80, 22) self.Qcombo_section.setLayoutDirection(QtCore.Qt.RightToLeft) self.Qcombo_section.setEnabled(False) self.Qcombo_section.setStyleSheet( "background-color: rgb(198, 207, 199)") # get sections list if check box self.checkBox_section.stateChanged.connect( self.FN_WhenChecksection) # self.FN_GET_sections() # TODO Click listner for changing list of department self.Qcombo_section.model().dataChanged.connect( self.FN_WhenCheckBMC_Level) # Multi selection for BMCLevel self.Qcombo_BMCLevel = CheckableComboBox(self) self.Qcombo_BMCLevel.setGeometry(270, 190, 110, 22) self.Qcombo_BMCLevel.setLayoutDirection(QtCore.Qt.RightToLeft) self.Qcombo_BMCLevel.setEnabled(False) self.Qcombo_BMCLevel.setStyleSheet( "background-color: rgb(198, 207, 199)") # get BMC LEVEL4 list if check box self.checkBox_BMCLevel.stateChanged.connect( self.FN_WhenCheckBMC_Level) self.checkBox_Barcode.stateChanged.connect( self.FN_WhenCheckBarcode) # get Banks list if readio button clicked self.RBTN_bank.clicked.connect(self.FN_InstallMent_Checked) # get Vendor list if readio button clicked self.RBTN_vendor.clicked.connect(self.FN_InstallMent_Checked) # if readio button clicked hyperone self.RBTN_hyperone.clicked.connect(self.FN_InstallMent_Checked) # this function for what enabled or not when start self.EnabledWhenOpen() self.Rbtn_stsAll.setChecked(True) #enable the search and the export buttons except Exception as err: print(err) def FN_LOAD_BARCODES(self): try: self.Qlab_msg.setText("") options = QFileDialog.Options() options |= QFileDialog.DontUseNativeDialog fileName, _ = QFileDialog.getOpenFileName( self, "QFileDialog.getOpenFileName()", "", " Files (*.xlsx)", options=options) self.barcodes = [] if fileName != '': #self.LE_fileName.setText(fileName) wb = xlrd.open_workbook(fileName) sheet = wb.sheet_by_index(0) conn = db1.connect() mycursor = conn.cursor() for i in range(sheet.nrows): error = 0 bar = int(sheet.cell_value(i, 0)) self.barcodes.append(str(bar)) if len(self.barcodes) > 0: self.Qlab_msg.setText("تم تحميل الأصناف") #print(barcodes) except Exception as err: print(err) # this function for what enabled or not when start def EnabledWhenOpen(self): self.checkBox_department.setEnabled(True) self.checkBox_section.setEnabled(False) self.Qcombo_section.setEnabled(False) self.checkBox_BMCLevel.setEnabled(False) self.Qcombo_BMCLevel.setEnabled(False) #get installments period list def FN_GET_installment_types_period(self): #self.Qcombo_installmentType.clear() conn = db1.connect() mycursor = conn.cursor() mycursor.execute( "SELECT InstT_Installment_Period ,INSTT_TYPE_ID FROM INSTALLMENT_TYPE" ) records = mycursor.fetchall() mycursor.close() for row, val in records: self.Qcombo_installmentType.addItem(row, val) # get branches list # get companys list def FN_GET_Company(self): self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY") records = mycursor.fetchall() for row, val in records: self.Qcombo_company.addItem(row, val) mycursor.close() # get branches list def FN_GET_Branch(self): self.Qcombo_branch.clear() i = 0 try: # Todo: method for fills the Branch combobox self.conn = db1.connect() mycursor = self.conn.cursor() val3 = "" for a in range(len(self.Qcombo_company.currentData())): if a < len(self.Qcombo_company.currentData()) - 1: val3 = val3 + "'" + self.Qcombo_company.currentData( )[a] + "'," else: val3 = val3 + "'" + self.Qcombo_company.currentData( )[a] + "'" sqlite3 = "SELECT BRANCH_DESC_A ,BRANCH_NO FROM BRANCH WHERE COMPANY_ID in (" + val3 + ")" mycursor.execute(sqlite3) records = mycursor.fetchall() for row, val in records: for bra in CL_userModule.branch: if val in bra: self.Qcombo_branch.addItem(row, val) i += 1 mycursor.close() self.Qcombo_branch.setCurrentIndex(-1) except: print(sys.exc_info()) # get customer Groupe list def FN_GET_customerGroupe(self): self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT CG_DESC,CG_GROUP_ID FROM CUSTOMER_GROUP") records = mycursor.fetchall() for row, val in records: self.Qcombo_customerGroupe.addItem(row, val) mycursor.close() # after check department check box def FN_WhenCheckDepartment(self): if self.checkBox_department.isChecked(): self.FN_GET_Department() self.Qcombo_department.setEnabled(True) self.checkBox_section.setEnabled(True) self.Qcombo_department.setCurrentIndex(-1) else: self.Qcombo_department.unCheckedList() self.Qcombo_department.setEnabled(False) self.checkBox_section.setEnabled(False) self.checkBox_section.setChecked(False) self.checkBox_BMCLevel.setChecked(False) self.Qcombo_department.setCurrentIndex(-1) # get Department list def FN_GET_Department(self): self.Qcombo_department.clear() i = 0 try: # Todo: method for fills the section combobox """ self.Qcombo_department.clear() self.conn = db1.connect() mycursor = self.conn.cursor() mycursor.execute("SELECT DEPARTMENT_DESC,DEPARTMENT_ID FROM DEPARTMENT") records = mycursor.fetchall() for row, val in records: for sec in CL_userModule.section : if val in sec: self.Qcombo_department.addItem(row, val) i += 1 mycursor.close() """ print("FN_GET_Department", CL_userModule.section) for row, val, row1, val1 in CL_userModule.section: self.Qcombo_department.addItem(val1, row1) except: print(sys.exc_info()) # after check section check box def FN_WhenChecksection(self): if self.checkBox_section.isChecked(): self.FN_GET_sections() self.checkBox_BMCLevel.setEnabled(True) self.Qcombo_section.setEnabled(True) self.Qcombo_section.setCurrentIndex(-1) else: self.Qcombo_section.unCheckedList() self.checkBox_BMCLevel.setChecked(False) self.checkBox_BMCLevel.setEnabled(False) self.Qcombo_section.setEnabled(False) self.Qcombo_section.setCurrentIndex(-1) # self.Qcombo_section.unChecked() def FN_WhenCheckBarcode(self): if self.checkBox_Barcode.isChecked(): self.Qline_barcode.setEnabled(True) self.Qbtn_loadItems.setEnabled(True) else: self.Qline_barcode.setEnabled(False) self.Qbtn_loadItems.setEnabled(False) self.Qlab_msg.setText('') # get sections list def FN_GET_sections(self): self.Qcombo_section.clear() i = 0 try: conn = db1.connect() mycursor = conn.cursor() #print("currentData", self.Qcombo_department.currentData()) val3 = "" for a in range(len(self.Qcombo_department.currentData())): if a < len(self.Qcombo_department.currentData()) - 1: val3 = val3 + "'" + self.Qcombo_department.currentData( )[a] + "'," else: val3 = val3 + "'" + self.Qcombo_department.currentData( )[a] + "'" #print("deparments", val3) mycursor.execute( "SELECT SECTION_DESC,SECTION_ID FROM SECTION where DEPARTMENT_ID in (" + val3 + ")") # print("Query"+"SELECT SECTION_DESC,SECTION_ID FROM SECTION where DEPARTMENT_ID in ("+val3+")") records = mycursor.fetchall() mycursor.close() for row, val in records: self.Qcombo_section.addItem(row, val) i += 1 except: print(sys.exc_info()) # after check department check box # after check BMC Level check box def FN_WhenCheckBMC_Level(self): if self.checkBox_BMCLevel.isChecked(): self.FN_GET_BMC_Level() self.Qcombo_BMCLevel.setEnabled(True) self.Qcombo_BMCLevel.setCurrentIndex(-1) # self.FN_ else: self.Qcombo_BMCLevel.unCheckedList() self.checkBox_BMCLevel.setChecked(False) self.Qcombo_BMCLevel.setEnabled(False) self.Qcombo_BMCLevel.setCurrentIndex(-1) # get BMC LEVEL4 list def FN_GET_BMC_Level(self): self.Qcombo_BMCLevel.clear() i = 0 try: conn = db1.connect() mycursor = conn.cursor() val3 = "" for a in range(len(self.Qcombo_section.currentData())): if a < len(self.Qcombo_section.currentData()) - 1: val3 = val3 + "'" + self.Qcombo_section.currentData( )[a] + "'," else: val3 = val3 + "'" + self.Qcombo_section.currentData( )[a] + "'" #print("sections", val3) mycursor.execute( "SELECT BMC_LEVEL4_DESC,BMC_LEVEL4 FROM BMC_LEVEL4 where SECTION_ID in (" + val3 + ")") records = mycursor.fetchall() mycursor.close() for row, val in records: self.Qcombo_BMCLevel.addItem(row, val) i += 1 except: print(sys.exc_info()) def FN_InstallMent_Checked(self): try: if self.RBTN_bank.isChecked(): self.FN_GET_Banks() self.Qcombo_bank.setEnabled(True) self.Qcombo_vendor.setEnabled(False) self.Qcombo_vendor.clear() elif self.RBTN_vendor.isChecked(): self.Qcombo_bank.setEnabled(False) self.Qcombo_bank.clear() self.FN_GET_Vendor() self.Qcombo_vendor.setEnabled(True) elif self.RBTN_hyperone.isChecked(): self.Qcombo_bank.setEnabled(False) self.Qcombo_bank.clear() self.Qcombo_vendor.setEnabled(False) self.Qcombo_vendor.clear() # TO clear Data From QTable except: print(sys.exc_info()) # get Banks list def FN_GET_Banks(self): self.Qcombo_bank.clear() self.Qcombo_bank.addItem("أختر بنك", "0") conn = db1.connect() mycursor = conn.cursor() mycursor.execute("SELECT Bank_Desc,Bank_ID FROM BANK") records = mycursor.fetchall() mycursor.close() for row, val in records: self.Qcombo_bank.addItem(row, val) # get Vendor list def FN_GET_Vendor(self): self.Qcombo_vendor.clear() self.Qcombo_vendor.addItem("أختر مورد", "0") conn = db1.connect() mycursor = conn.cursor() mycursor.execute( "SELECT SPONSOR_NAME,SPONSOR_ID FROM Hyper1_Retail.SPONSOR") records = mycursor.fetchall() mycursor.close() for row, val in records: self.Qcombo_vendor.addItem(row, val) def FN_CHECK_VALID_BARCCODE(self, id): try: conn = db1.connect() mycursor11 = conn.cursor() sql = "SELECT * FROM Hyper1_Retail.POS_ITEM where POS_GTIN = '" + str( id) + "'" mycursor11.execute(sql) myresult = mycursor11.fetchone() if mycursor11.rowcount > 0: mycursor11.close() return True else: mycursor11.close() return False except Exception as err: print(err) def FN_SEARCH(self): try: for i in reversed(range(self.Qtable_inst.rowCount())): self.Qtable_inst.removeRow(i) cust_gps = self.Qcombo_customerGroupe.currentData() branchs = self.Qcombo_branch.currentData() companies = self.Qcombo_company.currentData() bar = self.Qline_barcode.text() BMC = [] sec = [] dep = [] conn = db1.connect() mycursor = conn.cursor() whereClause = "" ret = True if self.checkBox_Barcode.isChecked(): if len(bar) > 0: ret = self.FN_CHECK_VALID_BARCCODE(bar) self.barcodes = [] else: self.barcodes = [] if ret == True: if self.checkBox_BMCLevel.isChecked(): #get bmc BMC = self.Qcombo_BMCLevel.currentData() elif self.checkBox_section.isChecked(): #get sec sec = self.Qcombo_section.currentData() elif self.checkBox_department.isChecked(): # get dep dep = self.Qcombo_department.currentData() instType = self.Qcombo_installmentType.currentData() instDesc = self.LE_desc.text() date_from = self.Qdate_from.dateTime().toString('yyyy-MM-dd') date_to = self.Qdate_to.dateTime().toString('yyyy-MM-dd') ### Call Validation Function if self.Rbtn_stsActive.isChecked(): whereClause = whereClause + ' INST_STATUS = 1' elif self.Rbtn_stsInactive.isChecked(): whereClause = whereClause + ' INST_STATUS = 0' elif self.Rbtn_stsAll.isChecked(): whereClause = whereClause + ' INST_STATUS in ( 0,1)' if self.RBTN_bank.isChecked(): if self.Qcombo_bank.currentData() == "0": whereClause = whereClause + "and s.BANK_ID <> '' " else: whereClause = whereClause + "and s.BANK_ID = '" + str( self.Qcombo_bank.currentData()) + "'" elif self.RBTN_vendor.isChecked(): if self.Qcombo_vendor.currentData() == "0": whereClause = whereClause + " and s.SPONSOR_ID <> '' " else: whereClause = whereClause + " and s.SPONSOR_ID = '" + str( self.Qcombo_vendor.currentData()) + "'" elif self.RBTN_hyperone.isChecked(): whereClause = whereClause + " and s.HYPERONE <> '' " #elif self.RBTN_all.isChecked(): #whereClause = whereClause + " and s.HYPERONE <> '' " if instDesc != '': whereClause = whereClause + " and p.INST_DESC like '%" + str( instDesc) + "%' " # get COMPANY if len(companies) > 0: if len(companies) == 1: whereClause = whereClause + " and c.COMPANY_ID = '" + companies[ 0] + "'" else: company_list_tuple = tuple(companies) whereClause = whereClause + " and c.COMPANY_ID in {}".format( company_list_tuple) # get branchs if len(branchs) > 0: if len(branchs) == 1: whereClause = whereClause + " and b.BRANCH_NO ='" + branchs[ 0] + "'" else: branch_list_tuple = tuple(branchs) whereClause = whereClause + " and b.BRANCH_NO in {} ".format( branch_list_tuple) # get customer gp id if len(cust_gps) > 0: if len(cust_gps) == 1: whereClause = whereClause + " and g.CG_GROUP_ID ='" + str( cust_gps[0]) + "'" else: cust_gp_list_tuple = tuple(cust_gps) whereClause = whereClause + " and g.CG_GROUP_ID in {}".format( cust_gp_list_tuple) #get BMC levels if len(BMC) > 0: if len(BMC) == 1: whereClause = whereClause + " and sec.BMC_ID ='" + str( BMC[0]) + "'" else: bmc_tuple = tuple(BMC) whereClause = whereClause + " and sec.BMC_ID in {}".format( bmc_tuple) elif len(sec) > 0: if len(sec) == 1: whereClause = whereClause + " and sec.SECTION_ID ='" + str( sec[0]) + "'" else: sec_tuple = tuple(sec) whereClause = whereClause + " and sec.SECTION_ID in {}".format( sec_tuple) elif len(dep) > 0: if len(dep) == 1: whereClause = whereClause + " and sec.DEPARTMENT_ID ='" + str( dep[0]) + "'" else: dep_tuple = tuple(dep) whereClause = whereClause + " and sec.DEPARTMENT_ID in {}".format( dep_tuple) if instType != '0': whereClause = whereClause + " and tp.INSTT_TYPE_ID = '" + str( instType) + "'" if len(bar) > 0: whereClause = whereClause + " and ii.POS_GTIN = '" + str( bar) + "'" if len(self.barcodes) > 0: if len(self.barcodes) == 1: whereClause = whereClause + "and ii.POS_GTIN = '" + str( self.barcodes[0]) + "'" else: bar_tuple = tuple(self.barcodes) whereClause = whereClause + " and ii.POS_GTIN in {}".format( bar_tuple) whereClause = whereClause + " and INST_VALID_FROM >= '" + date_from + "' and INST_VALID_TO <= '" + date_to + "' " sql_select_query = "SELECT distinct p.INST_PROGRAM_ID, p.INST_DESC ,'customer_gp','company','branch','dep', 'sec',p.INST_VALID_FROM,p.INST_VALID_TO,p.INST_ADMIN_EXPENSES_PERC,p.INST_STATUS , tp.INSTT_DESC,s.HYPERONE,bank.Bank_Desc ,spon.SPONSOR_NAME , r.INSTR_HYPER_RATE,r.INSTR_SPONSOR_RATE,r.INSTR_CUSTOMER_RATE FROM Hyper1_Retail.INSTALLMENT_PROGRAM p inner join Hyper1_Retail.INSTALLMENT_RULE r on p.INSTR_RULEID = r.INSTR_RULEID inner join Hyper1_Retail.INSTALLMENT_TYPE tp on r.INSTT_TYPE_ID = tp.INSTT_TYPE_ID inner join Hyper1_Retail.INSTALLMENT_GROUP g on p.INST_PROGRAM_ID = g.INST_PROGRAM_ID inner join Hyper1_Retail.INSTALLMENT_BRANCH b on p.INST_PROGRAM_ID = b.INST_PROGRAM_ID inner join Hyper1_Retail.INSTALLMENT_SPONSOR s on p.INSTR_RULEID = s.INSTR_RULEID left outer join Hyper1_Retail.BANK bank on s.BANK_ID = bank.Bank_ID left outer join Hyper1_Retail.SPONSOR spon on spon.SPONSOR_ID = s.SPONSOR_ID inner join CUSTOMER_GROUP cg on g.CG_GROUP_ID = cg.CG_GROUP_ID inner join Hyper1_Retail.BRANCH branch on b.COMPANY_ID = branch.COMPANY_ID and b.BRANCH_NO = branch.BRANCH_NO inner join Hyper1_Retail.COMPANY c on branch.COMPANY_ID =c.COMPANY_ID " \ "left outer join Hyper1_Retail.INSTALLMENT_SECTION sec on sec.INSTR_RULEID = r.INSTR_RULEID" \ " left outer join Hyper1_Retail.INSTALLMENT_ITEM ii on ii.INSTR_RULEID = r.INSTR_RULEID " \ " where"+ whereClause print(sql_select_query) mycursor.execute(sql_select_query) records = mycursor.fetchall() inst_prog_no = '' for row_number, row_data in enumerate(records): #get customer groups self.Qtable_inst.insertRow(row_number) inst_prog_no = '' for column_number, data in enumerate(row_data): if column_number == 0: inst_prog_no = str(data) if column_number == 2: data = self.FN_GET_CUST_GP(inst_prog_no) if column_number == 3: data = self.FN_GET_COMP(inst_prog_no) if column_number == 4: data = self.FN_GET_BRANCH(inst_prog_no) if column_number == 5: data = self.FN_GET_DEPT(inst_prog_no) if column_number == 6: data = self.FN_GET_SEC(inst_prog_no) if column_number == 10: data = util.FN_GET_STATUS_DESC(str(data)) if data == None or data == '': data = '----' self.Qtable_inst.setItem(row_number, column_number, QTableWidgetItem(str(data))) self.Qtable_inst.setEditTriggers( QtWidgets.QTableWidget.NoEditTriggers) mycursor.close() else: QtWidgets.QMessageBox.warning(self, "خطأ", "الباركود غير صحيح") except Exception as err: print(err) def FN_GET_DEPT(self, inst_no): try: conn = db1.connect() mycursor = conn.cursor() dept = '' sql_select_query ="SELECT DEPARTMENT_DESC FROM Hyper1_Retail.INSTALLMENT_SECTION isec " \ " inner join Hyper1_Retail.INSTALLMENT_RULE irule on isec.INSTR_RULEID = irule.INSTR_RULEID " \ " inner join Hyper1_Retail.INSTALLMENT_PROGRAM p on irule.INSTR_RULEID = p.INSTR_RULEID" \ " inner join Hyper1_Retail.DEPARTMENT d " \ " on isec.DEPARTMENT_ID = d.DEPARTMENT_ID " \ " where p.INST_PROGRAM_ID = '"+inst_no+"'" #print(sql_select_query) mycursor.execute(sql_select_query) records = mycursor.fetchall() for rec in records: dept = dept + ',' + rec[0] mycursor.close() return dept except Exception as err: print(err) def FN_GET_SEC(self, inst_no): try: conn = db1.connect() mycursor = conn.cursor() sec = '' sql_select_query = "SELECT SECTION_DESC FROM Hyper1_Retail.INSTALLMENT_SECTION isec " \ " inner join Hyper1_Retail.INSTALLMENT_RULE irule on isec.INSTR_RULEID = irule.INSTR_RULEID " \ " inner join Hyper1_Retail.INSTALLMENT_PROGRAM p on irule.INSTR_RULEID = p.INSTR_RULEID" \ " inner join Hyper1_Retail.SECTION s "\ " on isec.SECTION_ID = s.SECTION_ID where p.INST_PROGRAM_ID = '" + inst_no + "'" #print(sql_select_query) mycursor.execute(sql_select_query) records = mycursor.fetchall() for rec in records: sec = sec + ',' + rec[0] mycursor.close() return sec except Exception as err: print(err) def FN_GET_CUST_GP(self, inst_no): try: conn = db1.connect() mycursor = conn.cursor() cust_gp = '' sql_select_query ="SELECT CG_DESC FROM Hyper1_Retail.INSTALLMENT_GROUP ig inner join Hyper1_Retail.CUSTOMER_GROUP cg " \ "on ig.CG_GROUP_ID = cg.CG_GROUP_ID where INST_PROGRAM_ID = '"+inst_no+"' and CG_DESC !='H1'" mycursor.execute(sql_select_query) records = mycursor.fetchall() for rec in records: cust_gp = cust_gp + ',' + rec[0] mycursor.close() return cust_gp except Exception as err: print(err) def FN_GET_COMP(self, inst_no): try: conn = db1.connect() mycursor = conn.cursor() comp = '' sql_select_query = "SELECT distinct COMPANY_DESC FROM Hyper1_Retail.INSTALLMENT_BRANCH ib inner join Hyper1_Retail.COMPANY c on ib.COMPANY_ID =c.COMPANY_ID where INST_PROGRAM_ID = '" + inst_no + "'" mycursor.execute(sql_select_query) records = mycursor.fetchall() for rec in records: comp = comp + ' , ' + rec[0] mycursor.close() return comp except Exception as err: print(err) def FN_GET_BRANCH(self, inst_no): try: conn = db1.connect() mycursor = conn.cursor() branch = '' sql_select_query = "SELECT distinct BRANCH_DESC_A,ib.COMPANY_ID FROM Hyper1_Retail.INSTALLMENT_BRANCH ib inner join Hyper1_Retail.BRANCH branch on ib.COMPANY_ID = branch.COMPANY_ID and ib.BRANCH_NO = branch.BRANCH_NO where INST_PROGRAM_ID = '" + inst_no + "'" mycursor.execute(sql_select_query) records = mycursor.fetchall() for rec in records: branch = branch + ' , ' + rec[0] mycursor.close() return branch except Exception as err: print(err)