def FN_CREATE_ROLE(self): self.name = self.LE_name.text().strip() self.desc = self.LE_DESC.text().strip() self.status = self.CMB_roleStatus.currentText() if self.status == 'Active': self.status = '1' else: self.status = '0' if self.name == '' or self.desc == '': QtWidgets.QMessageBox.warning(self, "Error", "Please all required field") else: mycursor = self.conn.cursor() mycursor.execute( "SELECT max(cast(role_ID AS UNSIGNED)) FROM SYS_ROLE") 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')) sql = "INSERT INTO SYS_ROLE (ROLE_ID, ROLE_NAME,ROLE_DESC,ROLE_CREATED_BY,ROLE_CREATED_ON, ROLE_STATUS) " \ "VALUES ('" + str( self.id) + "','" + self.name + "','" + self.desc + "', '" + CL_userModule.user_name + "', '" + creationDate + "','" + self.status + "')" print(sql) mycursor.execute(sql) mycursor.close() db1.connectionCommit(self.conn) print(mycursor.rowcount, "record inserted.") db1.connectionClose(self.conn) self.close() QtWidgets.QMessageBox.information(self, "Success", "Role is created successfully")
def FN_CREATE_FORM(self): self.desc = self.LE_desc.text().strip() self.type = self.CMB_formType.currentText() self.help = self.LE_help.text() self.status = self.CMB_formStatus.currentText() if self.status == 'Active': self.status = '1' else: self.status = '0' if self.desc == '' or self.type == '': QtWidgets.QMessageBox.warning(self, "Error", "Please all required field") else: mycursor = self.conn.cursor() mycursor.execute( "SELECT max(cast(FORM_ID AS UNSIGNED)) FROM SYS_FORM") myresult = mycursor.fetchone() if myresult[0] == None: self.id = "1" else: self.id = int(myresult[0]) + 1 sql = "INSERT INTO SYS_FORM (FORM_ID, FORM_DESC, FORM_TYPE,FORM_STATUS,FORM_HELP) VALUES ( %s, %s, %s, %s,%s)" val = (self.id, self.desc, self.type, self.status, self.help) mycursor.execute(sql, val) db1.connectionCommit(self.conn) mycursor.close() db1.connectionClose(self.conn) print(mycursor.rowcount, "record inserted.") self.close() QtWidgets.QMessageBox.information(self, "Success", "Form is created successfully")
def FN_MODIFY_ROLE(self): self.old_name = self.CMB_roleName.currentText() self.name = self.LE_name.text().strip() self.desc = self.LE_DESC.text().strip() self.status = self.CMB_roleStatus.currentText() if self.status == 'Active': self.status = '1' else: self.status = '0' if self.name == '' or self.desc == '': QtWidgets.QMessageBox.warning(self, "Error", "Please all required field") else: mycursor = self.conn.cursor() changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) sql = "UPDATE SYS_ROLE set ROLE_NAME= %s , ROLE_DESC= %s , ROLE_CHANGED_ON = %s , ROLE_CHANGED_BY = %s, ROLE_STATUS = %s where ROLE_NAME= %s " val = (self.name, self.desc, changeDate, CL_userModule.user_name, self.status, self.old_name) mycursor.execute(sql, val) mycursor.close() db1.connectionCommit(self.conn) print(mycursor.rowcount, "record Modified.") db1.connectionClose(self) self.close() QtWidgets.QMessageBox.information(self, "Success", "Role is modified successfully")
def FN_MODIFY_BANK(self): #self.id = self.LB_userID.text() self.bankDesc = self.LE_bankDesc.text().strip() self.bankAddress = self.LE_bankAddress.text().strip() self.accountNumber = self.LE_accountNumber.text().strip() self.Bankstatus = self.CMB_bankStatus.currentText() if self.bankDesc == '' or self.bankAddress == '' or self.accountNumber == '': QtWidgets.QMessageBox.warning(self, "Error", "Please all required field") else: mycursor = self.conn.cursor() changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) sql = "UPDATE BANK set BANK_DESC= %s , BANK_CHANGED_ON = %s , BANK_CHANGED_BY = %s,BANK_ACCOUNT_NO = %s, BANK_ADDRESS = %s, USER_STATUS = %s where BANK_DESC= %s " val = (self.bankDesc, changeDate, CL_userModule.user_name, self.accountNumber, self.bankAddress, self.Bankstatus, self.bankDesc) print(val) mycursor.execute(sql, val) mycursor.close() db1.connectionCommit(self.conn) print(mycursor.rowcount, "record Modified.") db1.connectionClose(self.conn) self.close()
def FN_MODIFY_FORM(self): self.id = self.LB_formID.text() self.desc = self.LE_desc.text().strip() self.type = self.CMB_formType.currentText() self.status = self.CMB_formStatus.currentText() if self.status == 'Active': self.status = '1' else: self.status = '0' self.help = self.LE_help.text() if self.desc == '' or self.type == '': QtWidgets.QMessageBox.warning(self, "Error", "Please all required field") else: mycursor = self.conn.cursor() changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) sql = "UPDATE SYS_FORM set FORM_DESC= %s ,FORM_TYPE= %s , FORM_STATUS = %s, FORM_HELP = %s where FORM_id= %s " val = (self.desc, self.type, self.status, self.help, self.id) mycursor.execute(sql, val) db1.connectionCommit(self.conn) mycursor.close() db1.connectionClose(self.conn) print(mycursor.rowcount, "record Modified.") self.close() QtWidgets.QMessageBox.information(self, "Success", "Form is modified successfully")
def FN_CREATEList(self): if len(self.Qcombo_List.currentData()) == 0: QtWidgets.QMessageBox.warning(self, "خطا", "اكمل العناصر الفارغه") sql_select_Query = "SELECT * FROM Hyper1_Retail.SYS_CKECK_LIST_POS where STATUS = 2" 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_POS (POS_NO, Company_ID,BRANCH_NO,LIST_ID , CHANGED_ON, CHANGED_BY, STATUS) VALUES ( %s, %s, %s, %s,%s, %s, %s)" #val = ( # self.name, self.Notes, creationDate, # CL_userModule.user_name, self.status) #mycursor.execute(sql, val) for i in range(len(self.Qcombo_Comapny.currentData())): for j in range(len(self.Qcombo_Branch.currentData())): for x in range(len(self.Qcombo_List.currentData())): sql3 = "INSERT INTO Hyper1_Retail.SYS_CKECK_LIST_POS (POS_NO, Company_ID,BRANCH_NO,LIST_ID , CHANGED_ON, CHANGED_BY, STATUS) VALUES ( %s, %s, %s, %s,%s, %s, %s)" val3 = (self.name, self.Qcombo_Comapny.currentData()[i], self.Qcombo_Branch.currentData()[j], self.Qcombo_List.currentData()[x], 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_create_branch(self): self.branchId = self.CMB_branchId.currentText() self.branchCompany = self.QLE_branchCompany.text() self.branchCompany = self.QLE_branchCompany.text() self.branchDescA = self.QLE_branchDescA.text() self.branchDescE = self.QLE_branchDescE.text() self.branchAddress = self.QLE_branchAddress.text() self.branchCity = self.QLE_branchCity.text() self.branchTel1 = self.QLE_branchTel1.text() self.branchTel2 = self.QLE_branch_tel2.text() self.branchFax = self.QLE_branch_fax.text() self.branchEmail = self.QLE_branchEmail.text() self.branchNotes = self.QLE_branchNotes.text() self.branchChangedOn = self.QLE_branchChangedOn.text() self.branchCurrency = self.QLE_branchCurrency.text() self.branchNotes = self.QLE_branchNotes.text() self.branchStatus = self.CMB_branch_status.currentText() # connection = mysql.connector.connect(host='localhost',database='test',user='******',password='******',port='3306') mycursor = db1.connect(self) # get max userid # mycursor.execute("SELECT max(USER_ID) FROM SYS_USER") # myresult = mycursor.fetchone() # get max branch i mycursor.execute("SELECT max(CAST(BRANCH_NO as SIGNED)) FROM BRANCH") myresult = mycursor.fetchone() if len(myresult) == 0: self.branchId = 1 else: self.branchId = myresult[0] + 1 # self.id=int(myresult[0])+1 creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) print(creationDate) sql = "INSERT INTO BRANCH (BRANCH_NO, BRANCH_COMPANY, BRANCH_DESC_A, BRANCH_DESC_E, BRANCH_ADDRESS, BRANCH_CITY, BRANCH_TEL1, BRANCH_TEL2, BRANCH_FAX, BRANCH_EMAIL,BRANCH_NOTES, BRANCH_CHANGED_ON,BRANCH_CURRENCY, BRANCH_STATUS) VALUES ( %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # sql = "INSERT INTO SYS_USER (USER_ID,USER_NAME) VALUES (%s, %s)" val = (self.branchId, self.branchCompany, self.name, self.password, self.fullName, self.hrId, creationDate, '', '', '', self.status, self.userType) mycursor.execute(sql, val) # mycursor.execute(sql) db1.connectionClose() mycursor.close() print(mycursor.rowcount, "record inserted.") self.close()
def FN_CREATE_Parameters(self): sql_select_Query = "SELECT * FROM Hyper1_Retail.SYS_HW_COMPONENT where HW_PARAMETER_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", "HW Parameter 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(HW_PARAMETER_ID AS UNSIGNED)) FROM SYS_HW_COMPONENT" ) 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 SYS_HW_COMPONENT (HW_PARAMETER_ID, HW_PARAMETER_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) mycursor.close() print(mycursor.rowcount, "Record inserted.") QtWidgets.QMessageBox.information( self, "Success", "HW Parameter is created successfully") db1.connectionCommit(self.conn) db1.connectionClose(self.conn) self.FN_DISPLAY_PRIVILAGE()
def FN_RESET_USER(self): mycursor = self.conn.cursor() changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) if self.LE_password.text() == self.LE_password2.text(): sql = "UPDATE SYS_USER set USER_PASSWORD= %s , USER_CHANGED_ON = %s , USER_CHENGED_BY = %s where USER_NAME= %s " val = (self.LE_password.text(), changeDate, CL_userModule.user_name, CL_userModule.user_name) #print( val ) mycursor.execute(sql, val) mycursor.close() db1.connectionCommit(self.conn) print(mycursor.rowcount, "password changed") db1.connectionClose(self.conn) self.close() else: QtWidgets.QMessageBox.warning( self, "Error", "Please enter 2 different Passwords")
def FN_Update_Parameters(self): self.name = self.LE_name.text().strip() self.Value = self.LE_Value.text().strip() self.D_Value = self.LE_D_Value.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) or CL_validation.FN_isEmpty( self.Value) or CL_validation.FN_isEmpty( self.D_Value): 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() if self.LE_name.text().strip() != desc: QtWidgets.QMessageBox.warning(self, "Error", "Parameter Name Can't be change") return else: mycursor = self.conn.cursor() sql = "Update SYS_CONFIG_PARAMETERS set PARAMETER_VALUE = %s, DEFAULT_VALUE = %s, NOTES = %s, CHANGED_ON = %s, CHANGED_BY = %s, STATUS = %s where PARAMETER_DESC = %s" val = (self.Value, self.D_Value, 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", "Parameter is Updated successfully") db1.connectionCommit(self.conn) db1.connectionClose(self.conn) self.LE_Value.setText("") self.LE_name.setText("") self.LE_D_Value.setText("") self.LE_Notes.setText("") except Exception as err: print(err) self.FN_DISPLAY_PRIVILAGE()
def FN_CREATE_Bank(self, bankDesc, bankAddress, accountNumber): self.bankDes = self.bankDesc.strip() self.bankAddress = self.bankAddress.strip() self.accountNumber = self.accountNumber.currentText() self.status = self.CMB_bankStatus.currentText() mycursor = self.conn.cursor() # get max userid mycursor.execute("SELECT max(cast(BANK_ID AS UNSIGNED)) FROM BANK") 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 self.name == '' or self.password == '' or self.fullName == '' or self.hrId == '': QtWidgets.QMessageBox.warning(self, "Error", "Please all required field") else: sql = "INSERT INTO BANK (BANK_ID, BANK_DES, BANK_CREATED_ON, BANK_CREATED_BY, BANK_CHANGED_ON , BANK_CHANGED_BY, BANK_ACCOUNT_NO, BANK_ADDRESS, BANK_STATUS) VALUES ( %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s)" # sql = "INSERT INTO SYS_USER (USER_ID,USER_NAME) VALUES (%s, %s)" val = (self.id, self.bankDes, creationDate, CL_userModule.user_name, '', '', self.accountNumber, self.bankAddress, self.status) mycursor.execute(sql, val) # mycursor.execute(sql) print(CL_userModule.user_name) mycursor.close() print(mycursor.rowcount, "record inserted.") db1.connectionCommit(self.conn) db1.connectionClose(self.conn) self.close()
def FN_ASSIGN_ROLE(self): self.status = self.CMB_userRoleStatus.currentText() self.user = self.LB_userID.text() self.role = self.LB_roleID.text() self.status = self.CMB_userRoleStatus.currentText() if self.status == 'Active': self.status = 1 else: self.status = 0 mycursor = self.conn.cursor(buffered=True) sql_select_query = "delete from SYS_USER_ROLE where SYS_USER_ROLE.USER_ID= '" + self.user + "'" mycursor.execute(sql_select_query) db1.connectionCommit(self.conn) items = self.CMB_roleName.currentData() x = [] for i in range(len(items)): roleId = self.FN_GET_ROLEID1(str(items[i])) mycursor = self.conn.cursor() mycursor.execute( "SELECT max(cast(UR_USER_ROLE_ID AS UNSIGNED)) FROM SYS_USER_ROLE" ) 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')) 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 = (self.id, self.user, roleId, '1', CL_userModule.user_name, creationDate, '', '', self.status) mycursor.execute(sql, val) mycursor.close() db1.connectionCommit(self.conn) print(mycursor.rowcount, "record inserted.") db1.connectionClose(self.conn) self.close() QtWidgets.QMessageBox.information(self, "Success", "Role is assigned successfully")
def FN_MODIFY_FORM(self): self.id = self.LB_formItemID.text() self.form = self.LB_formID.text() self.desc = self.LE_desc.text().strip() self.status = self.CMB_formItemStatus.currentText() if self.status == 'Active': self.status = '1' else: self.status = '0' if self.desc == '': QtWidgets.QMessageBox.warning(self, "Error", "Please all required field") else: mycursor = self.conn.cursor() sql = "UPDATE SYS_FORM_ITEM set FORM_ID= %s ,ITEM_DESC= %s , ITEM_STATUS = %s where ITEM_id= %s " val = (self.form, self.desc, self.status, self.id) mycursor.execute(sql, val) mycursor.close() db1.connectionCommit(self.conn) db1.connectionClose(self.conn) print(mycursor.rowcount, "record Modified.") self.close() QtWidgets.QMessageBox.information(self, "Success", "Form Item is modified successfully")
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")
def FN_ModifyList(self): self.name = self.LE_name.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, 0).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 = "delete from Hyper1_Retail.SYS_CKECK_LIST_POS where POS_NO = %s " val = (desc,) mycursor.execute(sql, val) mycursor.close() self.FN_CREATEList() db1.connectionCommit(self.conn) db1.connectionClose(self.conn) QtWidgets.QMessageBox.information(self, "Success", "List is Updated successfully") self.LE_name.setText("") except Exception as err: print(err) self.FN_DISPLAY_PRIVILAGE()
def FN_ACTIVATE_LOYPROG(self): ids = [] id = self.label_ID.text().strip() if self.Qradio_active.isChecked(): status = 1 else: status = 0 conn = db1.connect() mycursor = conn.cursor() sql = "update Hyper1_Retail.LOYALITY_PROGRAM set LOY_STATUS=%s where LOY_PROGRAM_ID = %s " val = (status, id) mycursor.execute(sql, val) mycursor.close() QtWidgets.QMessageBox.information(self, "نجاح", " تم تعديل الحاله") db1.connectionCommit(conn) db1.connectionClose(conn) if str(status) != str(self.old_status): util.FN_INSERT_IN_LOG("LOYALITY_PROGRAM", "status", status, self.old_status, id) ids.append(id) self.FN_REFRESH_DATA_GRID(ids)
def FN_CREATE_PRIVILAGE(self): conn = db1.connect() mycursor = conn.cursor(buffered=True) self.role = self.LB_roleId.text() sql_select_query = "SELECT PRIV_ID FROM SYS_PRIVILEGE WHERE ROLE_id = %s" x = (self.role, ) mycursor.execute(sql_select_query, x) if mycursor.rowcount > 0: records = mycursor.fetchall() for row in records: sql_select_query = "SELECT ITEM_ID FROM SYS_PRIVILEG_ITEM WHERE PRIV_ID = '" + row[ 0] + "'" mycursor.execute(sql_select_query) myresult = mycursor.fetchone() if mycursor.rowcount > 0: sql_select_query = "delete from SYS_PRIVILEG_ITEM where PRIV_ID = '" + row[ 0] + "'" mycursor.execute(sql_select_query) db1.connectionCommit(conn) sql_select_query1 = "delete from SYS_PRIVILEGE where PRIV_ID = '" + row[ 0] + "'" mycursor.execute(sql_select_query1) db1.connectionCommit(conn) allRows = self.w1.rowCount() for row in range(0, allRows): mycursor.execute( "SELECT max(cast(PRIV_ID AS UNSIGNED)) FROM SYS_PRIVILEGE") myresult = mycursor.fetchone() if myresult[0] == None: self.id = "1" else: self.id = int(myresult[0]) + 1 roleId = self.w1.item(row, 1) formId = self.w1.item(row, 3) actionName = self.w1.item(row, 4) sql_select_query = "SELECT ACTION_ID FROM SYS_PRINT_EXPORT WHERE ACTION_DESC = %s" x = (actionName.text(), ) mycursor.execute(sql_select_query, x) myresult = mycursor.fetchone() if mycursor.rowcount > 0: actionId = myresult[0] sql = "INSERT INTO SYS_PRIVILEGE (PRIV_ID, ROLE_ID,FORM_ID,ACTION_ID) " \ "VALUES ( %s, %s, %s, %s)" val = (self.id, roleId.text(), formId.text(), actionId) mycursor.execute(sql, val) db1.connectionCommit(conn) formItemName = self.w1.item(row, 5) sql_select_query = "SELECT ITEM_ID FROM SYS_FORM_ITEM WHERE ITEM_DESC = %s and form_ID =%s" x = (formItemName.text(), formId.text()) mycursor.execute(sql_select_query, x) myresult = mycursor.fetchone() if mycursor.rowcount > 0: formItemId = myresult[0] sql = "INSERT INTO SYS_PRIVILEG_ITEM (PRIV_ID, FORM_ID,ITEM_ID) " \ "VALUES ( %s, %s, %s)" val = (self.id, formId.text(), formItemId) mycursor.execute(sql, val) print(mycursor.rowcount, "record inserted.") db1.connectionCommit(conn) mycursor.close() db1.connectionClose(conn) self.close() QtWidgets.QMessageBox.information(self, "Success", "Privilage is created successfully")
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())
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())
def FN_SAVE_UPLOAD(self): #self.fileName ="C:/Users/Shaymaa/Desktop/Book2.xlsx" #self.FN_OPEN_FILE() fileName = self.LE_fileName.text() if fileName !='': #self.LE_fileName.setText(self.fileName) wb = xlrd.open_workbook(fileName) sheet = wb.sheet_by_index(0) errorMsg = '' createdProg = 0 nonCreatedProg = 0 for i in range(sheet.nrows): try: name = sheet.cell_value(i, 0) desc = sheet.cell_value(i, 1) #validFrom = sheet.cell_value(i, 2) validFrom = str(sheet.cell_value(i, 2)) validTo = sheet.cell_value(i, 3) status = int(sheet.cell_value(i, 4)) company = int(sheet.cell_value(i, 5)) branch = sheet.cell_value(i, 6) custGroup = int(sheet.cell_value(i, 7)) loyalityType = int(sheet.cell_value(i, 8)) barcode = '' if sheet.cell_value(i, 9) == '' : print('barcode') else: barcode = int(sheet.cell_value(i, 9)) bmc = int(sheet.cell_value(i, 10)) purchAmount = int(sheet.cell_value(i, 11)) points = int(sheet.cell_value(i, 12)) except Exception as err: print(err) if name == '' or desc == '' or validFrom == '' or validTo == '' or status == '' or company == '' or branch == '' \ or custGroup == '' or loyalityType == '' or purchAmount == '' or points == '': nonCreatedProg = nonCreatedProg + 1 QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء إدخال جميع البيانات") elif validTo < validFrom: QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء") break elif validFrom < self.creationDate1: QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ الإنشاء يجب أن يكون أكبرمن أو يساوي تاريخ اليوم") break # # try: #elif CL_validation.FN_validate_date1(validFrom) == True and CL_validation.FN_validation_int(status): else: try: ret= self.FN_CHECK_EXIST(company, branch, custGroup, loyalityType, bmc,barcode) ids = [] if barcode == '': ret2 = True bmc = int(bmc) ret1 = self.FN_CHECK_VALID_BMC(bmc) else: ret1=True ret2 = self.FN_CHECK_VALID_BARCCODE(barcode) ret3 = self.FN_CHECK_VALID_CUSTGP(custGroup) ret4 = self.FN_CHECK_VALID_CUSTTP(loyalityType) ret5 = self.FN_CHECK_VALID_BRANCH(branch) ret6 = self.FN_CHECK_VALID_COMPANY(company) if ret == False and ret1 == True and ret2 == True and ret3 == True and ret4 == True and ret5 == True and ret6 == True : # get max userid conn = db1.connect() mycursor1 = conn.cursor() mycursor1.execute("SELECT max(cast(LOY_PROGRAM_ID AS UNSIGNED)) FROM Hyper1_Retail.LOYALITY_PROGRAM") myresult = mycursor1.fetchone() if myresult[0] == None: self.id = "1" else: self.id = int(myresult[0]) + 1 ids.append(self.id) creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) sql = "INSERT INTO Hyper1_Retail.LOYALITY_PROGRAM (LOY_PROGRAM_ID,COPMAPNY_ID," \ "BRANCH_NO,CG_GROUP_ID,BMC_ID,POS_GTIN,LOY_NAME,LOY_DESC,LOY_CREATED_ON,LOY_CREATED_BY," \ "LOY_VALID_FROM,LOY_VALID_TO,LOY_VALUE,LOY_POINTS,LOYCT_TYPE_ID,LOY_STATUS)" \ "values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" # "values ('"+self.id+"','" +com+"', '"+br+"',' "+ctgp+"','','"+self.barcode+"','" +self.name+"' ,'" +self.desc+"','" + creationDate+"',CL_userModule.user_name+"',' \ # '" +self.date_to+"'+ self.purchAmount+"',self.points+"','" +cttp+"','" +self.status +"")" val = (self.id, company, branch, custGroup, bmc, barcode, name, desc, creationDate, CL_userModule.user_name, validFrom, validTo, purchAmount, points, loyalityType, status) mycursor1.execute(sql, val) createdProg = createdProg + 1 db1.connectionCommit(conn) mycursor1.close() # else: nonCreatedProg = nonCreatedProg + 1 self.msgBox1 = QMessageBox() self.msgBox1.setWindowTitle("Information") self.msgBox1.setStandardButtons(QMessageBox.Ok) if ret ==True: j=i+1 self.msgBox1.setText("Line " + str(j) + " already exists") elif ret1==False: j = i + 1 self.msgBox1.setText("Line " + str(j) + " has invalid BMC") elif ret2 == False: j = i + 1 self.msgBox1.setText("Line " + str(j) + " has invalid Barcode") elif ret3 == False: j = i + 1 self.msgBox1.setText("Line " + str(j) + " has invalid Customer Gp") elif ret4 == False: j = i + 1 self.msgBox1.setText("Line " + str(j) + " has invalid Customer Type") elif ret5 == False: j = i + 1 self.msgBox1.setText("Line " + str(j) + " has invalid Branch") elif ret6 == False: j = i + 1 self.msgBox1.setText("Line " + str(j) + " has invalid Company") self.msgBox1.show() #self.close() break except Exception as err: print(err) if createdProg >0: db1.connectionClose(conn) # QtWidgets.QMessageBox.warning( self, "Information", "No of created user ",counter) self.msgBox = QMessageBox() # Set the various texts self.msgBox.setWindowTitle("Information") self.msgBox.setStandardButtons(QMessageBox.Ok) self.msgBox.setText( "No of created programs '" + str(createdProg) + "' No of non created programs '" + str(nonCreatedProg) + "'") self.msgBox.show() #if createdProg>0: #self.FN_REFRESH_DATA_GRID(ids) self.close() else: QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء إختيار الملف ")
def FN_MODIFY_LOYPROG(self): try: ids=[] id = self.label_ID.text().strip() if len(id) == 0: QtWidgets.QMessageBox.warning(self, "خطأ", "لم يتم إختيار أي برنامج") else: name = self.Qline_name.text().strip() desc = self.Qtext_desc.toPlainText().strip() purchAmount = self.Qline_purchAmount.text().strip() points = self.Qline_points.text().strip() date_from = self.Qdate_from.date().toString('yyyy-MM-dd') date_to = self.Qdate_to.date().toString('yyyy-MM-dd') if name == '' or desc == '' or float(purchAmount) == 0 or points == '0' or date_from == '' or date_to == '' \ : QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء إدخال جميع البيانات") elif date_to < date_from: QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء") elif date_from < self.creationDate1: QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ التعديل يجب أن يكون أكبرمن أو يساوي تاريخ اليوم") else: conn = db1.connect() mycursor = conn.cursor() changeDate = str( datetime.today().strftime( '%Y-%m-%d-%H:%M-%S' ) ) # get customer gp id sql = "update Hyper1_Retail.LOYALITY_PROGRAM set LOY_NAME = %s , LOY_DESC = %s , LOY_VALID_FROM = %s ,LOY_VALID_TO = %s ,LOY_VALUE = %s ,LOY_POINTS = %s , LOY_CHANGED_BY = %s where LOY_PROGRAM_ID = %s " val = (name ,desc ,date_from,date_to, purchAmount ,points,CL_userModule.user_name,id) mycursor.execute(sql, val) mycursor.close() ids.append(id) print( mycursor.rowcount, "record updated." ) QtWidgets.QMessageBox.information(self, "Success", "تم التعديل") db1.connectionCommit( conn ) db1.connectionClose( conn ) #self.close() if str(points) != str(self.old_points): util.FN_INSERT_IN_LOG("LOYALITY_PROGRAM", "points", points, self.old_points,id) if str(date_from) != str(self.old_valid_from): util.FN_INSERT_IN_LOG("LOYALITY_PROGRAM", "valid_from", date_from, self.old_valid_from,id) if str(date_to) != str(self.old_valid_to): util.FN_INSERT_IN_LOG("LOYALITY_PROGRAM", "valid_to", date_to, self.old_valid_to,id) if str(purchAmount) != str(self.old_amount): util.FN_INSERT_IN_LOG("LOYALITY_PROGRAM", "amount", purchAmount, self.old_amount,id) if str(name) != str(self.old_name): util.FN_INSERT_IN_LOG("LOYALITY_PROGRAM", "name", name, self.old_name,id) print( "in modify cust" ) self.FN_REFRESH_DATA_GRID(ids) except Exception as err: print(err)