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_CREATE_VOUCHER(self): try: # insert voucher value = self.Qline_point_value.text().strip() customer = self.Qline_cust.text().strip() conn = db1.connect() mycursor = conn.cursor() creationDate = str(datetime.today().strftime('%d-%m-%Y')) # insert voucher value11 = randint(0, 1000000000000) voucherBarcode ="RVOU" + bin(value11) sql = "INSERT INTO Hyper1_Retail.VOUCHER (GV_DESC, GVT_ID, GV_BARCODE, GV_VALUE, GV_NET_VALUE, GV_CREATED_BY, GV_CREATED_ON, GV_VALID_FROM, GV_VALID_TO, POSC_CUST_ID, GV_PRINTRED,GV_STATUS) VALUES (%s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) " val = ('Redeem Points', '1', "RVOU" + bin(value11), value, value, CL_userModule.user_name, creationDate, creationDate, '31-12-9999', customer, '0', '0') mycursor.execute(sql, val) db1.connectionCommit(conn) mycursor.execute( "select GV_ID from Hyper1_Retail.VOUCHER where GV_BARCODE ='"+voucherBarcode+"'") result= mycursor.fetchone() QtWidgets.QMessageBox.information(self, "Done", str(result[0])+"رقم القسيمه هو " ) except Exception as err: print(err)
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")
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_CREATE_CUSTCD(self): try: self.conn = db1.connect() mycursor = self.conn.cursor() no = self.LE_custNo.text().strip() name = self.LE_custName.text().strip() expire_date = self.expire_date.date().toString('yyyy-MM-dd') if name == '' or no == '': QtWidgets.QMessageBox.warning(self, "خطأ", "برجاءادخال العميل") else: ret = self.FN_VALIDATE_CUST(no) if ret == True: mask_serial = self.FN_GET_MASKED_CARD_SERIAL(no) print(mask_serial) sql = "INSERT INTO `Hyper1_Retail`.`POS_CUSTOMER_CARD`(`CARD_SERIAL_BARCODE`,`POS_CUST_ID`,`EXPIRY_DATE`,`CARD_STATUS`) " \ " VALUES ( %s, %s, %s, %s)" # sql = "INSERT INTO SYS_USER (USER_ID,USER_NAME) VALUES (%s, %s)" val = (mask_serial, no, expire_date, self.CMB_status.currentData()) mycursor.execute(sql, val) # mycursor.execute(sql) mycursor.close() QtWidgets.QMessageBox.information(self, "نجاح", "تم الإنشاء") db1.connectionCommit(self.conn) except Exception as err: print(err)
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_Create(self): self.conn = db1.connect() self.conn.autocommit = False mycursor = self.conn.cursor() self.conn.start_transaction() try: sql0 = " LOCK TABLES Hyper1_Retail.POS_WORKING_DAY WRITE " mycursor.execute(sql0) sql = "INSERT INTO POS_WORKING_DAY (COMPANY_ID,BRANCH_NO,WORKING_DATE,START_DATE,START_BY," \ "END_DATE,END_BY,DAY_STATUS)" \ " VALUES (%s, %s, %s,%s, %s, %s, %s, %s) " val = (self.Qcombo_company.currentData(), self.Qcombo_branch.currentData(), self.Qdate_work.dateTime().toString('yyyy-MM-dd'), self.Qdate_from.dateTime().toString('yyyy-MM-dd'), CL_userModule.user_name, self.Qdate_to.dateTime().toString('yyyy-MM-dd'), CL_userModule.user_name, self.CMB_Status.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")
def FN_COPY_ROLE(self): newRole = self.LB_roleID2.text() if self.role1 == self.role2: 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.FORM_ID ,ur.ACTION_ID " \ "from SYS_PRIVILEGE ur inner join SYS_ROLE u ON u.ROLE_ID = ur.ROLE_ID " \ "where u.ROLE_NAME = %s " x = (self.role1, ) mycursor.execute(sql_select_query, x) records = mycursor.fetchall() mycursor2 = self.conn.cursor() sql_select_query1 = "delete from SYS_PRIVILEGE where ROLE_ID = '" + newRole + "'" mycursor2.execute(sql_select_query1) db1.connectionCommit(self.conn) mycursor1.execute( "SELECT max(cast(PRIV_ID AS UNSIGNED)) FROM SYS_PRIVILEGE") myresult = mycursor1.fetchone() id = int(myresult[0]) + 1 for row in records: mycursor3 = self.conn.cursor() sql = "INSERT INTO SYS_PRIVILEGE VALUES ( %s, %s, %s, %s)" val = (id, newRole, row[0], row[1]) mycursor3.execute(sql, val) db1.connectionCommit(self.conn) print(mycursor3.rowcount, "record inserted.") id = id + 1 mycursor2.close() mycursor1.close() mycursor.close() 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_CHANGE_STATUS(self): try: id = self.voucher_id.text() self.conn = db1.connect() mycursor = self.conn.cursor() # get old status sql = "select PROMV_STATUS from `Hyper1_Retail`.`PROMOTIONAL_VOUCHER` where PROMV_VOUCHER_ID = %s" val = ( id,) mycursor.execute(sql, val) records = mycursor.fetchone() print (records[0]) print(self.status) if self.status!=records[0]: changeDate = str(datetime.today().strftime('%Y-%m-%d')) sql = "update `Hyper1_Retail`.`PROMOTIONAL_VOUCHER` set PROMV_STATUS =%s where PROMV_VOUCHER_ID = %s " val = (self.status, id) mycursor.execute(sql, val) db1.connectionCommit(self.conn) mycursor.close() self.LE_PromVoucherStatus.setText(util.FN_GET_STATUS_DESC(str(self.status))) #add in log table util.FN_INSERT_IN_LOG("PROMOTIONAL_VOUCHER", "status", self.status , records[0], id) self.status='' except Exception as err: print(err)
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_UpdateStatus(self): mycursor = self.conn.cursor() print(self.CMB_CouponStatus.currentIndex()) print(self.CMB_CouponDes.currentData()) sql = "update COUPON set COP_STATUS='"+str(self.CMB_CouponStatus.currentIndex())+"' where COP_ID='"+str(self.CMB_CouponDes.currentData())+"'" mycursor.execute(sql) db1.connectionCommit(self.conn) mycursor.close() QtWidgets.QMessageBox.warning(self, "Done", "Done")
def FN_Stop(self): mycursor = self.conn.cursor() string = self.lineDesc_2.text() x = (string[0:4] + bin(int(string[4:len(string)]))) sql = " update COUPON_SERIAL set COPS_STATUS=0 where COPS_BARCODE=(%s) " val = (x, ) mycursor.execute(sql, val) db1.connectionCommit(self.conn) mycursor.close() QtWidgets.QMessageBox.warning(self, "Done", "Done")
def FN_MODIFY_LOYPOINT(self): try: if len(self.Qtable_point.selectedIndexes()) > 0: id = self.LB_pointId.text() date_from = self.Qdate_from.date().toString('yyyy-MM-dd') date_to = self.Qdate_to.date().toString('yyyy-MM-dd') qty=self.LE_pointQty.text().strip() val=self.LE_pointValue.text().strip() conn = db1.connect() mycursor = conn.cursor() creationDate1 = str(datetime.today().strftime('%Y-%m-%d')) if qty == 0 or val == 0: QtWidgets.QMessageBox.warning(self, "Error", "برجاء إدخال جميع البيانات") elif date_to < date_from: QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء") elif date_from < creationDate1: QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ التعديل يجب أن يكون أكبرمن أو يساوي تاريخ اليوم") else: sql = "update Hyper1_Retail.LOYALITY_POINT " \ "set POINTS_QTY =%s,POINTS_VALUE =%s , POINTS_VALID_FROM = %s , " \ "POINTS_VALID_TO =%s where POINTS_ID = %s " val = (qty,val,date_from,date_to,id) mycursor.execute(sql, val) mycursor.close() print(mycursor.rowcount, "record updated.") QtWidgets.QMessageBox.information(self, "نجاح", "تم التعديل ") db1.connectionCommit(conn) self.FN_GET_POINTS() if str(qty) != str(self.old_qty): util.FN_INSERT_IN_LOG("LOYALITY_POINT", "quantity", qty, self.old_qty, id) if str(val) != str(self.old_value): util.FN_INSERT_IN_LOG("LOYALITY_POINT","value", val, self.old_value,id) if str(date_from) != str(self.old_valid_from): util.FN_INSERT_IN_LOG("LOYALITY_POINT", "valid_from", date_from, self.old_valid_from, id) if str(date_to) != str(self.old_valid_to): util.FN_INSERT_IN_LOG("LOYALITY_POINT", "valid_to", date_to, self.old_valid_to, id) print("in modify loy pt") else: QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء اختيار السطر المراد تعديله ") except Exception as err: print(err)
def FN_CREATE_CUSTGP(self): self.conn = db1.connect() self.name = self.LE_desc.text().strip() self.custGroup = self.CMB_custGroup.currentText() if self.custGroup == 'Active': self.status = 1 else: self.status = 0 mycursor = self.conn.cursor() # get max userid mycursor.execute( "SELECT max(cast(CG_GROUP_ID AS UNSIGNED)) FROM Hyper1_Retail.CUSTOMER_GROUP" ) 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 == '': QtWidgets.QMessageBox.warning(self, "خطأ", "برجاءادخال الاسم") else: try: if self.FN_CHECK_DUP_NAME(self.name) != False: QtWidgets.QMessageBox.warning(self, "خطأ", "الاسم مكرر") mycursor.close() else: sql = "INSERT INTO Hyper1_Retail.CUSTOMER_GROUP(CG_GROUP_ID, CG_DESC , CG_CREATED_ON, CG_CREATED_BY , CG_Status) " \ " VALUES ( %s, %s, %s, %s,%s)" # sql = "INSERT INTO SYS_USER (USER_ID,USER_NAME) VALUES (%s, %s)" val = (self.id, self.name, creationDate, CL_userModule.user_name, self.status) mycursor.execute(sql, val) # mycursor.execute(sql) mycursor.close() print(mycursor.rowcount, "Cust Gp inserted.") QtWidgets.QMessageBox.information(self, "نجاح", "تم الإنشاء") db1.connectionCommit(self.conn) self.FN_GET_CUSTGPS() self.FN_CLEAR_FEILDS() #db1.connectionClose(self.conn) #self.close() except Exception as err: print(err) print("in create cust", self.name)
def FN_CREATE_CUSTTP(self): try: name = self.LE_desc.text().strip() points = self.LE_points.text().strip() custType = self.CMB_custType.currentText() nextLevel = self.CMB_nextLevel.currentText() if custType == 'Active': status = 1 else: status = 0 conn = db1.connect() mycursor = conn.cursor() # get max userid mycursor.execute( "SELECT max(cast(LOYCT_TYPE_ID AS UNSIGNED)) FROM Hyper1_Retail.LOYALITY_CUSTOMER_TYPE " ) myresult = mycursor.fetchone() if myresult[0] == None: self.id = "1" else: self.id = int(myresult[0]) + 1 if name == '' or points == '': QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء إدخال جميع البيانات ") else: if self.FN_CHECK_DUP_NAME(name) != False: QtWidgets.QMessageBox.warning(self, "خطأ", "الاسم مكرر") else: nextLevel1 = self.FN_GET_NEXTlEVEL_ID(nextLevel) if self.FN_CHECK_DUP_NEXTLEVEL(nextLevel1) != False: QtWidgets.QMessageBox.warning(self, "خطأ", "المستوى التالي مكرر") else: sql = "INSERT INTO Hyper1_Retail.LOYALITY_CUSTOMER_TYPE" \ " VALUES ( %s, %s, %s, %s,%s)" # sql = "INSERT INTO SYS_USER (USER_ID,USER_NAME) VALUES (%s, %s)" val = (self.id, name, points, nextLevel1, status) mycursor.execute(sql, val) #mycursor.close() print(mycursor.rowcount, "Cust Tp inserted.") QtWidgets.QMessageBox.information( self, "نجاح", "تم الإنشاء") db1.connectionCommit(conn) self.FN_GET_CUSTTPS() self.FN_GET_NEXTLVL() self.FN_CLEAR_FEILDS() except Exception as err: #mycursor.close() print(err)
def FN_CREATE_REDEEMTP(self): self.conn = db1.connect() self.name = self.LE_desc.text().strip() self.redeemTp = self.CMB_redeemType.currentText() if self.redeemTp == 'Active': self.status = 1 else: self.status = 0 mycursor = self.conn.cursor() # get max userid mycursor.execute( "SELECT max(cast(REDEEMT_TYPE_ID AS UNSIGNED)) FROM Hyper1_Retail.REDEEM_TYPE" ) 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 == '': QtWidgets.QMessageBox.warning(self, "خطأ", "برجاءادخال الاسم") else: try: if self.FN_CHECK_DUP_NAME(self.name) != False: QtWidgets.QMessageBox.warning(self, "خطأ", "الاسم مكرر") mycursor.close() else: sql = "INSERT INTO Hyper1_Retail.REDEEM_TYPE " \ " VALUES ( %s, %s, %s)" # sql = "INSERT INTO SYS_USER (USER_ID,USER_NAME) VALUES (%s, %s)" val = (self.id, self.name, self.status) mycursor.execute(sql, val) # mycursor.execute(sql) mycursor.close() print(mycursor.rowcount, "Redeem type inserted.") QtWidgets.QMessageBox.information(self, "نجاح", "تم الإنشاء") db1.connectionCommit(self.conn) self.FN_GET_REDEEMTPS() self.FN_CLEAR_FEILDS() #db1.connectionClose(self.conn) #self.close() except Exception as err: print(err)
def FN_MODIFY_VOUCHER(self): try: todayDate = str(datetime.today().strftime('%Y-%m-%d')) self.date_to = self.Qdate_to.date().toString('yyyy-MM-dd') self.conn = db1.connect() mycursor = self.conn.cursor() changeDate = str(datetime.today().strftime('%Y-%m-%d')) if len(self.LE_desc.text().strip()) == 0 or len(self.LE_value.text().strip()) == 0 or len( self.LE_maxCount.text().strip()) == 0: QtWidgets.QMessageBox.warning(self, "خطا", "اكمل العناصر الفارغه") else: desc = self.LE_desc.text().strip() id= self.voucher_id.text().strip() sql_select_Query = "select * from `Hyper1_Retail`.`PROMOTIONAL_VOUCHER` where PROMV_VOUCHER_DESC = %s and PROMV_VOUCHER_ID != %s " x = (desc,id) mycursor.execute(sql_select_Query, x) record = mycursor.fetchone() if mycursor.rowcount > 0: QtWidgets.QMessageBox.warning(self, "خطا", "الاسم موجود بالفعل") elif self.Qdate_to.dateTime() < self.Qdate_from.dateTime(): QtWidgets.QMessageBox.warning(self, "خطا", "تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء") elif self.date_to <todayDate: QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ الإنتهاء يجب أن يكون أكبرمن أو يساوي تاريخ اليوم") else: sql = "update `Hyper1_Retail`.`PROMOTIONAL_VOUCHER` set PROMV_VOUCHER_DESC = %s , `PROMV_VOUCHER_VAL` = %s,`PROMV_MAX_COUNT` = %s,`PROMV_CHANGED_BY` = %s,`PROMV_CHANGED_ON` = %s,`PROMV_VALID_FROM` = %s,`PROMV_VALID_TO` = %s where PROMV_VOUCHER_ID =%s" \ val = (self.LE_desc.text().strip() , self.LE_value.text().strip(),self.LE_maxCount.text().strip(),changeDate,CL_userModule.user_name,self.Qdate_from.dateTime().toString('yyyy-MM-dd'),self.Qdate_to.dateTime().toString('yyyy-MM-dd'),id) #print(sql) mycursor.execute(sql,val) db1.connectionCommit(self.conn) mycursor.close() QtWidgets.QMessageBox.information(self, "sucess", "تم التعديل") valid_from =self.Qdate_from.dateTime().toString('yyyy-MM-dd') valid_to = self.Qdate_to.dateTime().toString('yyyy-MM-dd') if self.oldDesc != desc: util.FN_INSERT_IN_LOG("PROMOTIONAL_VOUCHER", "desc", desc,self.oldDesc, id) if int(self.oldValue) != int(self.LE_value.text().strip()): util.FN_INSERT_IN_LOG("PROMOTIONAL_VOUCHER", "value", self.LE_value.text().strip(),self.oldValue, id) if self.oldMaxVal != int(self.LE_maxCount.text().strip()): util.FN_INSERT_IN_LOG("PROMOTIONAL_VOUCHER", "max count", self.LE_maxCount.text().strip(),self.oldMaxVal, id) if self.oldValidFrom != valid_from: util.FN_INSERT_IN_LOG("PROMOTIONAL_VOUCHER", "valid from",valid_from, self.oldValidFrom, id) if self.oldValidTo != valid_to: util.FN_INSERT_IN_LOG("PROMOTIONAL_VOUCHER", "valid to", valid_to,self.oldValidTo, id) except Exception as err: print(err)
def FN_MODIFY_REDEEMTP(self): self.conn1 = db1.connect() if len(self.Qtable_redeemTp.selectedIndexes()) > 0: rowNo = self.Qtable_redeemTp.selectedItems()[0].row() id = self.LB_redeemTpId.text().strip() desc_old = self.Qtable_redeemTp.item(rowNo, 1).text() desc = self.LE_desc.text().strip() redeemTp = self.CMB_redeemType.currentText() old_status = self.Qtable_redeemTp.item(rowNo, 2).text() old_status = util.FN_GET_STATUS_id(str(old_status)) if redeemTp == 'Active': status = 1 else: status = 0 # error = 0 if desc == '': QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء إدخال الاسم") else: if desc != desc_old: if self.FN_CHECK_DUP_NAME(desc, id) != False: QtWidgets.QMessageBox.warning(self, "خطأ", "الاسم مكرر") error = 1 if error != 1: mycursor = self.conn1.cursor() sql = "update Hyper1_Retail.REDEEM_TYPE set REDEEMT_STATUS= %s ,REDEEMT_DESC = %s where REDEEMT_TYPE_ID = %s" val = (status, desc, id) mycursor.execute(sql, val) print(mycursor.rowcount, "record updated.") QtWidgets.QMessageBox.information(self, "نجاح", "تم التعديل") db1.connectionCommit(self.conn1) self.FN_GET_REDEEMTPS() self.FN_CLEAR_FEILDS() if str(status) != str(old_status): util.FN_INSERT_IN_LOG("REDEEM_TYPE", "status", status, old_status, id) if str(desc) != str(desc_old): util.FN_INSERT_IN_LOG("REDEEM_TYPE", "desc", desc, desc_old, id) else: QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء اختيار السطر المراد تعديله ")
def FN_UPDATE_CUST_POINTS(self): try: # insert voucher value = self.Qline_point_value.text().strip() customer = self.Qline_cust.text().strip() replacedPoints = float(self.Qline_replace.text().strip()) conn = db1.connect() mycursor = conn.cursor() creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) actualPoints = float(self.Qline_points.text().strip()) remainingPoints = float(self.Qline_remainder.text().strip()) pts = remainingPoints - actualPoints sql0 = " LOCK TABLES Hyper1_Retail.POS_CUSTOMER_POINT WRITE , " \ " Hyper1_Retail.LOYALITY_POINTS_TRANSACTION_LOG WRITE " mycursor.execute(sql0) # get point value result = self.FN_GET_POINTS_VALUE(replacedPoints) sql = "INSERT INTO `Hyper1_Retail`.`LOYALITY_POINTS_TRANSACTION_LOG` " \ "(`POSC_CUST_ID`,`REDEEM_TYPE_ID`,`COMPANY_ID`,`BRANCH_NO`,`TRANS_CREATED_BY`," \ "`TRANS_CREATED_ON`,`POSC_POINTS_BEFORE`,`VALUE_OF_POINTS`,`TRANS_POINTS_QTY`,`TRANS_POINTS_VALUE`,`TRANS_REASON`,`POSC_POINTS_AFTER`,`TRANS_STATUS` ,REFERENCE_TRANS)" \ " VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" val = (customer, 4, '1', 'H010', CL_userModule.user_name, creationDate, actualPoints, result[1], pts, float(result[1]) * pts, 'Gift redeem', remainingPoints, '2', self.CMB_redeemItem.currentData()) mycursor.execute(sql, val) mycursor.execute( "SELECT max(cast(`MEMBERSHIP_POINTS_TRANS` AS UNSIGNED)) FROM LOYALITY_POINTS_TRANSACTION_LOG" ) myresult = mycursor.fetchone() MEMBERSHIP_POINTS_TRANS = myresult[0] sql = "update Hyper1_Retail.POS_CUSTOMER_POINT set MEMBERSHIP_POINTS_TRANS=%s , POSC_POINTS_BEFORE =%s ,TRANS_POINTS = %s ,POSC_POINTS_AFTER=%s, POINTS_CHANGED_ON =%s , TRANS_SIGN = '0' where POSC_CUSTOMER_ID = %s" val = (MEMBERSHIP_POINTS_TRANS, actualPoints, pts, remainingPoints, creationDate, customer) mycursor.execute(sql, val) # QtWidgets.QMessageBox.warning(self, "Done", "Voucher is created") sql00 = " UNLOCK tables " mycursor.execute(sql00) db1.connectionCommit(conn) print("customer points are updated") except Exception as err: print(err)
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_Create(self): self.conn = db1.connect() self.conn.autocommit = False mycursor = self.conn.cursor() self.conn.start_transaction() indx = self.code_key.text().strip() sql_select_Query = "select * from POS_FN_KEY where KEY_CODE = %s " x = (indx, ) mycursor.execute(sql_select_Query, x) record = mycursor.fetchone() if mycursor.rowcount > 0: QtWidgets.QMessageBox.warning(self, "خطا", "الاسم موجود بالفعل") else: if len(self.code_key.text()) == 0: QtWidgets.QMessageBox.warning(self, "خطا", "اكمل العناصر الفارغه") else: try: sql0 = " LOCK TABLES Hyper1_Retail.POS_FN_KEY WRITE " mycursor.execute(sql0) sql = "INSERT INTO POS_FN_KEY (KEY_CODE,KEY_FUNCTION,KEY_SECURITY_LEVEL,NOTES,STATUS)" \ " VALUES (%s, %s, %s, %s, %s) " val = (self.code_key.text().strip(), self.function_key.text().strip(), self.security_key.text().strip(), self.notes.toPlainText().strip(), self.CMB_Status.currentIndex()) mycursor.execute(sql, val) mycursor.execute( "SELECT * FROM POS_FN_KEY Where KEY_CODE = '" + indx + "'") c = mycursor.fetchone() id = c[0] sql00 = " UNLOCK tables " mycursor.execute(sql00) db1.connectionCommit(self.conn) mycursor.close() QtWidgets.QMessageBox.warning(self, "Done", "تم الانشاء") self.label_num.setText(str(id)) except: print(sys.exc_info()) self.conn.rollback() finally: if self.conn.is_connected(): mycursor.close() self.conn.close() print("connection is closed")
def FN_CREATE_LOYPOINT(self): try: date_from = self.Qdate_from.date().toString('yyyy-MM-dd') date_to = self.Qdate_to.date().toString('yyyy-MM-dd') ret=self.FN_CHK_PT_VALIDITY(date_from,date_to) if ret == False: QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء العلم أنه يوجد فتره فعاله ") else: qty = self.LE_pointQty.text().strip() val = self.LE_pointValue.text().strip() conn = db1.connect() mycursor = conn.cursor() creationDate1 = str(datetime.today().strftime('%Y-%m-%d')) if qty == 0 or val==0: QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء إدخال جميع البيانات") elif date_to < date_from: QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء") elif date_from < creationDate1: QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ الإنشاء يجب أن يكون أكبرمن أو يساوي تاريخ اليوم") else: mycursor.execute("SELECT max(cast(POINTS_ID AS UNSIGNED)) FROM Hyper1_Retail.LOYALITY_POINT") myresult = mycursor.fetchone() if myresult[0] == None: id = "1" else: id = int(myresult[0]) + 1 sql = "INSERT INTO Hyper1_Retail.LOYALITY_POINT ( POINTS_ID, POINTS_QTY,POINTS_VALUE,POINTS_VALID_FROM,POINTS_VALID_TO,POINTS_CREATED_ON,POINTS_CREATED_BY) VALUES ( '" +str(id)+"', '"+str(qty)+"','"+str(val)+"' ,'"+date_from+"','"+date_to+"','"+self.creationDate+"','"+CL_userModule.user_name+"')" # sql = "INSERT INTO SYS_USER (USER_ID,USER_NAME) VALUES (%s, %s)" print(sql) #val = (id, qty, val,date_from,date_to,creationDate,CL_userModule.user_name) #print(val) mycursor.execute(sql) db1.connectionCommit(conn) print(mycursor.rowcount, "loy point inserted.") QtWidgets.QMessageBox.information(self, "تم", "تم الإنشاء") mycursor.close() self.FN_GET_POINTS() self.FN_CLEAR_FEILDS() except Exception as err: print(err)
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())
def FN_Create(self): try: self.conn = db1.connect() self.conn.autocommit = False mycursor = self.conn.cursor() self.conn.start_transaction() sql_select_Query = "select * from POS_QUICK_GROUP_POS where COMPANY_ID=%s and BRANCH_NO=%s and POS_NO=%s and GROUP_ID = %s " x = (self.Qcombo_company.currentData(), self.Qcombo_branch.currentData(), self.Qcombo_pos.currentData(), self.Qcombo_group.currentData()) mycursor.execute(sql_select_Query, x) record = mycursor.fetchone() if mycursor.rowcount > 0: QtWidgets.QMessageBox.warning(self, "خطا", "المكنه موجود بالفعل") else: try: sql0 = " LOCK TABLES Hyper1_Retail.POS_QUICK_GROUP_POS WRITE " mycursor.execute(sql0) sql = "INSERT INTO POS_QUICK_GROUP_POS (COMPANY_ID,BRANCH_NO,POS_NO,GROUP_ID,STATUS)" \ " VALUES (%s, %s, %s, %s, %s) " val = (self.Qcombo_company.currentData(), self.Qcombo_branch.currentData(), self.Qcombo_pos.currentData(), self.Qcombo_group.currentData(), self.CMB_Status.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") except: print(sys.exc_info())
def FN_MODIFY_CUST(self): #get customer data try: id = self.LE_complainNo.text().strip() complainType = self.CMB_complainType.currentData() # city = self.CMB_city.currentData() # branch = self.CMB_branch.currentData() # dept = self.CMB_city.currentData() # sec = self.CMB_branch.currentData() status = self.CMB_status.currentData() responsible = self.LE_responsible.text().strip() details = self.details.toPlainText().strip() #details conn = db1.connect() mycursor = conn.cursor() changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) sql = "update Hyper1_Retail.CUSTOMER_COMPLAINT set CCT_TYPE_ID = %s ,CC_STATUS =%s " \ ",CC_RESPONSIBLE = %s ,CC_CHANGED_ON = %s , CC_CHANGED_BY = %s,CC_DETAIL = %s where CC_COMPLAINT_ID = %s" print(sql) val = (complainType, status, responsible, changeDate, CL_userModule.user_name, details, id) mycursor.execute(sql, val) mycursor.close() print(mycursor.rowcount, "record updated.") QtWidgets.QMessageBox.information(self, "تم", "تم التعديل") db1.connectionCommit(conn) self.close() self.FN_REFRESH_GRID(id) if self.oldStatus != status: util.FN_INSERT_IN_LOG("CUSTOMER_COMPLAINT", "status", status, self.oldStatus, id) if self.oldResponsible != responsible: util.FN_INSERT_IN_LOG("CUSTOMER_COMPLAINT", "responsible", responsible, self.oldResponsible, id) if str(self.oldComplainType) != str(complainType): util.FN_INSERT_IN_LOG("CUSTOMER_COMPLAINT", "complainType", complainType, self.oldComplainType, id) except Exception as err: print(err)
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_MODIFY_CUSTGP(self): self.conn1 = db1.connect() if len(self.Qtable_custGP.selectedIndexes()) > 0: rowNo = self.Qtable_custGP.selectedItems()[0].row() id = self.LB_custGpId.text().strip() desc_old = self.Qtable_custGP.item(rowNo, 1).text() desc = self.LE_desc.text().strip() custGroup = self.CMB_custGroup.currentText() if custGroup == 'Active': status = 1 else: status = 0 # error = 0 if self.desc == '': QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء إدخال الاسم") else: if desc != desc_old: if self.FN_CHECK_DUP_NAME(desc, id) != False: QtWidgets.QMessageBox.warning(self, "خطأ", "الاسم مكرر") error = 1 if error != 1: mycursor = self.conn1.cursor() changeDate = str( datetime.today().strftime('%Y-%m-%d-%H:%M-%S')) sql = "update Hyper1_Retail.CUSTOMER_GROUP set CG_Status= %s ,CG_DESC = %s ,CG_CHANGED_ON=%s , CG_CHANGED_BY =%s where CG_GROUP_ID = %s" val = (status, desc, changeDate, CL_userModule.user_name, id) mycursor.execute(sql, val) #mycursor.close() # print(mycursor.rowcount, "record updated.") QtWidgets.QMessageBox.information(self, "نجاح", "تم التعديل") db1.connectionCommit(self.conn1) self.FN_GET_CUSTGPS() self.FN_CLEAR_FEILDS() else: QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء اختيار السطر المراد تعديله ")
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")