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)
