class CL_user(QtWidgets.QDialog):
dirname = ''
userid = ''
branch_list = []
new_branch_list = []
section_list = []
new_section_list = []
def __init__(self):
super(CL_user, self).__init__()
cwd = Path.cwd()
mod_path = Path(__file__).parent.parent.parent
self.dirname = mod_path.__str__() + '/presentation/authorization_ui'
self.conn = db1.connect()
#Todo: method for load ui of modify user
def FN_LOAD_MODIFY(self):
filename = self.dirname + '/modifyUser.ui'
loadUi(filename, self)
self.CMB_branch = CheckableComboBox(self)
self.CMB_branch.setGeometry(150, 120, 171, 25)
self.CMB_branch.setLayoutDirection(QtCore.Qt.LeftToRight)
self.CMB_branch.setStyleSheet("background-color: rgb(198, 207, 199)")
self.CMB_section = CheckableComboBox(self)
self.CMB_section.setGeometry(150, 170, 171, 25)
self.CMB_section.setLayoutDirection(QtCore.Qt.LeftToRight)
self.CMB_section.setStyleSheet("background-color: rgb(198, 207, 199)")
self.FN_GET_BRANCHES()
self.FN_GET_Section()
self.FN_GET_USERTYPE()
self.CMB_userStatus.addItems(["Active", "Inactive"])
records = self.FN_GET_USERS()
for row in records:
self.CMB_userName.addItems([row[0]])
self.FN_GET_USER()
self.CMB_userName.currentIndexChanged.connect(self.FN_GET_USER)
self.BTN_modifyUser.clicked.connect(self.FN_MODIFY_USER)
#Todo: method for load ui of create User
def FN_LOAD_CREATE(self):
filename = self.dirname + '/createUser.ui'
loadUi(filename, self)
self.setWindowTitle('Users')
self.BTN_createUser.clicked.connect(self.FN_CREATE_USER)
self.CMB_branch = CheckableComboBox(self)
self.CMB_branch.setGeometry(150, 85, 171, 25)
self.CMB_branch.setLayoutDirection(QtCore.Qt.LeftToRight)
self.CMB_branch.setStyleSheet("background-color: rgb(198, 207, 199)")
self.CMB_section = CheckableComboBox(self)
self.CMB_section.setGeometry(150, 130, 171, 25)
self.CMB_section.setEnabled(False)
self.CMB_section.setLayoutDirection(QtCore.Qt.LeftToRight)
self.CMB_section.setStyleSheet("background-color: rgb(198, 207, 199)")
self.FN_GET_BRANCHES()
self.FN_GET_Section()
self.FN_GET_USERTYPE()
self.checkBox.toggled.connect(self.FN_EnableDepartment)
self.CMB_userStatus.addItems(["Active", "Inactive"])
# Set Style
# self.voucher_num.setStyleSheet(label_num)
# self.label_2.setStyleSheet(desc_5)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
#Todo: method for get user type
def FN_GET_USERTYPE(self):
mycursor = self.conn.cursor()
self.CMB_userType.clear()
sql_select_query = "SELECT USER_TYPE_DESC FROM SYS_USER_TYPE where USER_TYPE_STATUS = 1 "
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row in records:
self.CMB_userType.addItems([row[0]])
mycursor.close()
#Todo: method for get all branches
def FN_GET_BRANCHES(self):
mycursor = self.conn.cursor()
self.CMB_branch.clear()
sql_select_query = "SELECT BRANCH_DESC_A ,BRANCH_NO FROM BRANCH where BRANCH_STATUS = 1 "
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row, val in records:
self.CMB_branch.addItem(row, val)
mycursor.close()
#Todo: method for get all sections
def FN_GET_Section(self):
mycursor = self.conn.cursor()
self.CMB_section.clear()
sql_select_query = "SELECT SECTION_DESC ,SECTION_ID FROM SECTION "
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row, val in records:
self.CMB_section.addItem(row, val)
mycursor.close()
#Todo: method for load copy ui
def FN_LOAD_COPY(self):
filename = self.dirname + '/copyUser.ui'
loadUi(filename, self)
records = self.FN_GET_USERS()
for row in records:
self.CMB_userName.addItems([row[0]])
self.CMB_userName1.addItems([row[0]])
self.BTN_copyUser.clicked.connect(self.FN_COPY_USER)
self.CMB_userName.currentIndexChanged.connect(self.FN_ASSIGN_ID)
self.CMB_userName1.currentIndexChanged.connect(self.FN_ASSIGN_ID)
self.FN_ASSIGN_ID()
#Todo: method for get data from user combobox
def FN_ASSIGN_ID(self):
self.user1 = self.CMB_userName.currentText()
self.user2 = self.CMB_userName1.currentText()
self.LB_userID.setText(self.FN_GET_USERID_N(self.user1))
self.LB_userID2.setText(self.FN_GET_USERID_N(self.user2))
#Todo: method to copy user
def FN_COPY_USER(self):
newUser = self.LB_userID2.text()
if self.user1 == self.user2:
QtWidgets.QMessageBox.warning(self, "Error",
"Please enter 2 different users")
else:
mycursor = self.conn.cursor()
mycursor1 = self.conn.cursor()
mycursor2 = self.conn.cursor()
sql_select_query = "select ur.ROLE_ID ,ur.BRANCH_NO ,ur.UR_STATUS " \
"from SYS_USER_ROLE ur inner join SYS_USER u ON u.USER_ID = ur.USER_ID " \
"where u.USER_NAME = %s "
x = (self.user1, )
mycursor.execute(sql_select_query, x)
records = mycursor.fetchall()
mycursor2 = self.conn.cursor()
sql_select_query1 = "delete from SYS_USER_ROLE where USER_ID = '" + newUser + "'"
mycursor2.execute(sql_select_query1)
db1.connectionCommit(self.conn)
mycursor1.execute(
"SELECT max(cast(UR_USER_ROLE_ID AS UNSIGNED)) FROM SYS_USER_ROLE"
)
myresult = mycursor1.fetchone()
creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
id = int(myresult[0]) + 1
for row in records:
mycursor3 = self.conn.cursor()
sql = "INSERT INTO SYS_USER_ROLE (UR_USER_ROLE_ID, USER_ID, ROLE_ID, BRANCH_NO, UR_CREATED_BY, UR_CREATED_ON, UR_CHANGED_BY, UR_CHANGED_ON, UR_STATUS) " \
"VALUES ( %s, %s, %s, %s,%s, %s,%s,%s,%s)"
val = (id, newUser, row[0], row[1], CL_userModule.user_name,
creationDate, '', '', row[2])
print(str(sql))
mycursor3.execute(sql, val)
db1.connectionCommit(self.conn)
print(mycursor3.rowcount, "record inserted.")
id = id + 1
QtWidgets.QMessageBox.information(self, "Success",
"User is copied successfully")
mycursor2.close()
mycursor1.close()
mycursor.close()
self.close()
#Todo: method for get all users and display all data
def FN_GET_USER(self):
user = self.CMB_userName.currentText()
mycursor = self.conn.cursor()
sql_select_query = "select * from SYS_USER where USER_NAME = '" + user + "'"
print(sql_select_query)
mycursor.execute(sql_select_query)
record = mycursor.fetchone()
print(record)
self.LB_userID.setText(record[0])
self.userid = record[0]
self.LE_name.setText(record[2])
self.LE_password.setText(record[3])
self.LE_fullName.setText(record[4])
self.LE_hrId.setText(record[5])
self.CMB_userType.setCurrentText(record[11])
if record[10] == '1':
self.CMB_userStatus.setCurrentText('Active')
else:
self.CMB_userStatus.setCurrentText('Inactive')
print(mycursor.rowcount, "record retrieved.")
mycursor.close()
self.FN_check_branch()
self.FN_check_section()
self.branch_list.clear()
if len(self.CMB_branch.currentData()) > 0:
for i in self.CMB_branch.currentData():
self.branch_list.append(i)
if len(self.CMB_section.currentData()) > 0:
for i in self.CMB_section.currentData():
self.section_list.append(i)
#Todo: method for edit user
def FN_MODIFY_USER(self):
try:
self.id = self.LB_userID.text()
self.name = self.LE_name.text().strip()
self.password = self.LE_password.text().strip()
self.branch = self.CMB_branch.currentData()[0]
self.fullName = self.LE_fullName.text().strip()
self.hrId = self.LE_hrId.text().strip()
self.userType = self.CMB_userType.currentText()
self.status = self.CMB_userStatus.currentText()
if self.status == 'Active':
self.status = 1
else:
self.status = 0
if CL_validation.FN_isEmpty(self.name) or CL_validation.FN_isEmpty(
self.password) or CL_validation.FN_isEmpty(
self.fullName) or CL_validation.FN_isEmpty(self.hrId):
QtWidgets.QMessageBox.warning(
self, "Error", "Please enter all required fields")
else:
if CL_validation.FN_validation_password(
self, self.password) == False:
mycursor = self.conn.cursor()
if len(self.CMB_branch.currentData()) > 0:
for i in self.CMB_branch.currentData():
self.new_branch_list.append(i)
if len(self.CMB_section.currentData()) > 0:
for i in self.CMB_section.currentData():
self.new_section_list.append(i)
changeDate = str(
datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
sql = "UPDATE SYS_USER set USER_NAME= %s , USER_PASSWORD= %s , BRANCH_NO = %s, USER_FULLNAME = %s , USER_HR_ID = %s, USER_CHANGED_ON = %s , USER_CHANGED_BY = %s, USER_STATUS = %s, USERTYPE_ID = %s where USER_id= %s "
val = (self.name, self.password, self.branch,
self.fullName, self.hrId, changeDate,
CL_userModule.user_name, self.status, self.userType,
self.id)
mycursor.execute(sql, val)
if len(self.branch_list) > len(self.new_branch_list):
for row in self.branch_list:
print(row)
if row in self.new_branch_list:
print("found")
else:
print("not found")
mycursor = self.conn.cursor()
sql5 = "update SYS_USER_BRANCH set STATUS= 0 where USER_ID='" + self.userid + "' and BRANCH_NO = '" + row + "'"
mycursor.execute(sql5)
else:
for row in self.new_branch_list:
print(row)
if row in self.branch_list:
print("found")
else:
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT * FROM SYS_USER_BRANCH where BRANCH_NO='"
+ row + "' and USER_ID='" + self.userid +
"'")
record = mycursor.fetchall()
if mycursor.rowcount > 0:
mycursor = self.conn.cursor()
sql8 = "update SYS_USER_BRANCH set STATUS= 1 where USER_ID='" + self.userid + "' and BRANCH_NO = '" + row + "'"
mycursor.execute(sql8)
print(sql8)
else:
mycursor = self.conn.cursor()
sql6 = "INSERT INTO SYS_USER_BRANCH (USER_ID,COMPANY_ID,BRANCH_NO,STATUS) VALUES (%s,%s,%s,%s)"
val6 = (self.userid, '1', row, '1')
mycursor.execute(sql6, val6)
if len(self.section_list) > len(self.new_section_list):
for row in self.section_list:
print(row)
if row in self.new_section_list:
print("found")
else:
print("not found")
mycursor = self.conn.cursor()
sql5 = "update SYS_USER_SECTION set STATUS= 0 where USER_ID='" + self.userid + "' and SECTION_ID = '" + row + "'"
mycursor.execute(sql5)
else:
for row in self.new_section_list:
print(row)
if row in self.section_list:
print("found")
else:
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT * FROM SYS_USER_SECTION where SECTION_ID='"
+ row + "' and USER_ID='" + self.userid +
"'")
record = mycursor.fetchall()
if mycursor.rowcount > 0:
mycursor = self.conn.cursor()
sql8 = "update SYS_USER_SECTION set STATUS= 1 where USER_ID='" + self.userid + "' and SECTION_ID = '" + row + "'"
mycursor.execute(sql8)
print(sql8)
else:
mycursor = self.conn.cursor()
sql6 = "INSERT INTO SYS_USER_SECTION (USER_ID,SECTION_ID,STATUS) VALUES (%s,%s,%s)"
val6 = (self.userid, row, '1')
mycursor.execute(sql6, val6)
mycursor.close()
db1.connectionCommit(self.conn)
print(mycursor.rowcount, "record Modified.")
QtWidgets.QMessageBox.information(
self, "Success", "User is modified successfully")
db1.connectionClose(self.conn)
self.close()
except:
print(sys.exc_info())
#Todo: method for get user data
def FN_GET_USERS(self):
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT USER_NAME USER_ID FROM SYS_USER order by USER_ID asc")
records = mycursor.fetchall()
mycursor.close()
return records
#Todo: method for get user id
def FN_GET_USERID_N(self, user):
mycursor = self.conn.cursor()
sql_select_query = "SELECT USER_ID FROM SYS_USER WHERE USER_NAME = %s "
x = (user, )
mycursor.execute(sql_select_query, x)
myresult = mycursor.fetchone()
return myresult[0]
#Todo: method for create user
def FN_CREATE_USER(self):
try:
sql_select_Query = "select * from SYS_USER where USER_NAME = '" + self.LE_name.text(
) + "' and USER_STATUS = 1"
print(sql_select_Query)
mycursor = self.conn.cursor()
mycursor.execute(sql_select_Query)
print(mycursor.fetchall())
if mycursor.rowcount > 0:
QtWidgets.QMessageBox.warning(self, "Error",
"Username is already exists")
else:
self.name = self.LE_name.text().strip()
self.password = self.LE_password.text().strip()
print(self.CMB_branch.currentData()[0])
self.branch = self.CMB_branch.currentData()[0]
self.fullName = self.LE_fullName.text().strip()
self.hrId = self.LE_hrId.text().strip()
self.userType = self.CMB_userType.currentText()
self.status = self.CMB_userStatus.currentText()
if self.status == 'Active':
self.status = 1
else:
self.status = 0
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT max(cast(USER_ID AS UNSIGNED)) FROM SYS_USER")
myresult = mycursor.fetchone()
if myresult[0] == None:
self.id = "1"
else:
self.id = int(myresult[0]) + 1
creationDate = str(
datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
if CL_validation.FN_isEmpty(
self.name) or CL_validation.FN_isEmpty(
self.password) or CL_validation.FN_isEmpty(
self.fullName) or CL_validation.FN_isEmpty(
self.hrId):
QtWidgets.QMessageBox.warning(
self, "Error", "Please enter all required fields")
else:
if CL_validation.FN_validation_password(
self, self.password) == False:
sql = "INSERT INTO SYS_USER (USER_ID, BRANCH_NO, USER_NAME, USER_PASSWORD, USER_FULLNAME, USER_HR_ID, USER_CREATED_ON, USER_CREATED_BY, USER_CHANGED_ON, USER_CHANGED_BY,USER_STATUS, USERTYPE_ID) VALUES ( %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s)"
val = (self.id, self.branch, self.name, self.password,
self.fullName, self.hrId, creationDate,
CL_userModule.user_name, '', '', self.status,
self.userType)
mycursor.execute(sql, val)
for i in range(len(self.CMB_branch.currentData())):
sql2 = "INSERT INTO SYS_USER_BRANCH (USER_ID, COMPANY_ID, BRANCH_NO, STATUS) VALUES ( %s, %s, %s, %s)"
val = (self.id, '1',
self.CMB_branch.currentData()[i], '1')
mycursor.execute(sql2, val)
if self.checkBox.isChecked():
for i in range(len(
self.CMB_section.currentData())):
sql = "INSERT INTO SYS_USER_SECTION (USER_ID, SECTION_ID, STATUS) VALUES (%s, %s, %s)"
val = (self.id,
self.CMB_section.currentData()[i], '1')
mycursor.execute(sql, val)
mycursor.close()
print(mycursor.rowcount, "record inserted.")
QtWidgets.QMessageBox.information(
self, "Success", "User is created successfully")
db1.connectionCommit(self.conn)
db1.connectionClose(self.conn)
self.close()
except:
print(sys.exc_info())
#Todo: method for change password
def FN_RESET_USER(self):
mycursor = self.conn.cursor()
changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
if CL_validation.FN_isEmpty(self.LE_password.text()):
QtWidgets.QMessageBox.warning(self, "Error",
"Enter Password Please")
elif CL_validation.FN_isEmpty(self.LE_password2.text()):
QtWidgets.QMessageBox.warning(self, "Error", " RePassword Please")
elif CL_validation.FN_validation_password(
self, self.LE_password.text()) == False:
if self.LE_password.text() == self.LE_password2.text():
sql_select_Query = "select * from SYS_USER where USER_NAME = %s and USER_PASSWORD = %s and USER_STATUS = 1"
x = (self.LE_username.text(), self.old_password.text())
mycursor = self.conn.cursor()
mycursor.execute(sql_select_Query, x)
record = mycursor.fetchone()
if mycursor.rowcount > 0:
sql = "UPDATE SYS_USER set USER_PASSWORD= %s , USER_CHANGED_ON = %s , USER_CHANGED_BY = %s where USER_NAME= %s and USER_PASSWORD= %s "
val = (self.LE_password.text(), changeDate,
self.LE_username.text(), self.LE_username.text(),
self.old_password.text())
print(sql)
mycursor.execute(sql, val)
mycursor.close()
db1.connectionCommit(self.conn)
print(mycursor.rowcount, "password changed")
QtWidgets.QMessageBox.information(
self, "Success", "Password is reset successfully")
db1.connectionClose(self.conn)
self.close()
else:
QtWidgets.QMessageBox.warning(
self, "Error", "Incorrect Username and Password")
print("Please Enter Correct Username and Password")
else:
QtWidgets.QMessageBox.warning(
self, "Error", "Please enter 2 different Passwords")
#Todo: method for load reset password ui
def FN_LOAD_RESET(self):
filename = self.dirname + '/resetUserPassword.ui'
loadUi(filename, self)
self.BTN_resetPass.clicked.connect(self.FN_RESET_USER)
#Todo: method for change password
def FN_RESET_USER_MAIN(self):
mycursor = self.conn.cursor()
if CL_validation.FN_isEmpty(self.LE_password.text()):
QtWidgets.QMessageBox.warning(self, "Error",
"Enter Password Please")
elif CL_validation.FN_isEmpty(self.LE_password2.text()):
QtWidgets.QMessageBox.warning(self, "Error", " RePassword Please")
elif CL_validation.FN_validation_password(
self, self.LE_password.text()) == False:
changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
if self.LE_password.text() == self.LE_password2.text():
sql_select_Query = "select * from SYS_USER where USER_NAME = '" + self.LE_username.currentText(
) + "' and USER_STATUS = 1"
mycursor = self.conn.cursor()
mycursor.execute(sql_select_Query)
print(sql_select_Query)
record = mycursor.fetchone()
print(record)
if mycursor.rowcount > 0:
sql = "UPDATE SYS_USER set USER_PASSWORD= %s , USER_CHANGED_ON = %s , USER_CHANGED_BY = %s where USER_NAME= %s"
val = (self.LE_password.text(), changeDate,
self.LE_username.currentText(),
self.LE_username.currentText())
print(sql)
mycursor.execute(sql, val)
mycursor.close()
db1.connectionCommit(self.conn)
print(mycursor.rowcount, "password changed")
QtWidgets.QMessageBox.information(
self, "Success", "Password is reset successfully")
db1.connectionClose(self.conn)
self.close()
else:
QtWidgets.QMessageBox.warning(self, "Error",
"Incorrect Username ")
print("Please Enter Correct Username and Password")
else:
QtWidgets.QMessageBox.warning(
self, "Error", "Please enter 2 different Passwords")
#Todo: method for load reset password ui
def FN_LOAD_RESET_MAIN(self):
filename = self.dirname + '/resetUserPasswordMain.ui'
loadUi(filename, self)
self.FN_GET_User()
self.BTN_resetPass.clicked.connect(self.FN_RESET_USER_MAIN)
#Todo: method for all user name
def FN_GET_User(self):
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT USER_NAME FROM SYS_USER")
records = mycursor.fetchall()
for row in records:
self.LE_username.addItems(row)
mycursor.close()
#Todo: method for checked branches assigned to user
def FN_check_branch(self):
self.FN_unCheckedALL()
mycursor = self.conn.cursor()
sql_select_branch = "SELECT BRANCH_NO FROM BRANCH"
mycursor.execute(sql_select_branch)
record = mycursor.fetchall()
i = 0
for row in record:
for row1 in self.FN_SELECT_branch():
if row[0] == row1[0]:
items = self.CMB_branch.findText(row[0])
for item in range(items + 2):
if int(row1[1]) == 1:
self.CMB_branch.setChecked(i)
i = i + 1
mycursor.close()
#Todo: method for get all branches assigned to user
def FN_SELECT_branch(self):
mycursor = self.conn.cursor()
sql = "SELECT BRANCH_NO , STATUS FROM SYS_USER_BRANCH where USER_ID = %s"
c = (self.userid, )
mycursor.execute(sql, c)
records = mycursor.fetchall()
mycursor.close()
return records
#Todo: method to refresh checkable combobox for branch
def FN_unCheckedALL(self):
mycursor = self.conn.cursor()
sql_select_branch = "Select BRANCH_NO from BRANCH where BRANCH_STATUS=1"
mycursor.execute(sql_select_branch)
record = mycursor.fetchall()
print(record)
i = 0
for row in record:
self.CMB_branch.unChecked(i)
i += 1
#Todo: method to enable department
def FN_EnableDepartment(self):
if self.checkBox.isChecked():
self.CMB_section.setEnabled(True)
else:
self.CMB_section.setEnabled(False)
#Todo: method to get section assigned to user and checked it
def FN_check_section(self):
self.FN_unCheckedSection()
mycursor = self.conn.cursor()
sql_select_branch = "SELECT SECTION_ID FROM SECTION"
mycursor.execute(sql_select_branch)
record = mycursor.fetchall()
i = 0
for row in record:
for row1 in self.FN_SELECT_section():
if row[0] == row1[0]:
items = self.CMB_section.findText(row[0])
for item in range(items + 2):
if int(row1[1]) == 1:
self.CMB_section.setChecked(i)
i = i + 1
mycursor.close()
#Todo: method to get section assigned to user
def FN_SELECT_section(self):
mycursor = self.conn.cursor()
sql = "SELECT SECTION_ID , STATUS FROM SYS_USER_SECTION where USER_ID = %s"
c = (self.userid, )
mycursor.execute(sql, c)
records = mycursor.fetchall()
mycursor.close()
return records
#Todo: method to refresh checkable combobox for section
def FN_unCheckedSection(self):
mycursor = self.conn.cursor()
sql_select_branch = "Select SECTION_ID from SECTION where SECTION_STATUS=1"
mycursor.execute(sql_select_branch)
record = mycursor.fetchall()
print(record)
i = 0
for row in record:
self.CMB_section.unChecked(i)
i += 1
class CL_redeemTypeValue(QtWidgets.QDialog):
switch_window = QtCore.pyqtSignal()
dirname = ''
parent =''
modify_flag=0
whereClause1= ''
field_names = ['رقم العمليه', 'رقم العميل','اسم العميل','نوع الإسترجاع','الشركه','الفرع','ماكينه الكاشير','رقم الفاتوره','تاريخ الفاتوره','نقاط العميل قبل','النقاط المكتسبه','تقاط العميل بعد','الحاله']
def __init__(self):
super(CL_redeemTypeValue, self).__init__()
cwd = Path.cwd()
mod_path = Path( __file__ ).parent.parent.parent
self.dirname = mod_path.__str__() + '/presentation/reports_ui'
def FN_LOAD_DISPLAY(self):
try:
filename = self.dirname + '/redeemTypeValue.ui'
loadUi(filename, self)
self.Qbtn_search.clicked.connect(self.FN_SEARCH)
#self.Qline_cust.textChanged.connect(self.FN_CLEAR_FEILDS)
self.Qbtn_search_details.clicked.connect(self.FN_SEARCH_DETAILS)
self.Qbtn_print.clicked.connect(self.printpreviewDialog)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
valid_from = str(datetime.today().strftime('%Y-%m-%d'))
xto = valid_from.split("-")
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_from.setDate(d)
self.Qcombo_company = CheckableComboBox(self)
self.Qcombo_company.setGeometry(490, 42, 179, 18)
self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_company.setStyleSheet("background-color: rgb(198, 207, 199)")
self.Qcombo_branch = CheckableComboBox(self)
self.Qcombo_branch.setGeometry(490, 95, 179, 18)
self.Qcombo_branch.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_branch.setStyleSheet("background-color: rgb(198, 207, 199)")
self.FN_GET_COMPANIES()
self.Qcombo_company.model().dataChanged.connect(self.FN_GET_Branch)
self.FN_GET_Branch()
self.FN_GET_REDEEMTPS()
except Exception as err:
print(err)
def FN_GET_REDEEMTPS(self):
self.conn = db1.connect()
try:
mycursor = self.conn.cursor()
mycursor.execute("SELECT REDEEMT_DESC ,REDEEMT_TYPE_ID FROM Hyper1_Retail.REDEEM_TYPE ")
records = mycursor.fetchall()
for row, val in records:
self.CMB_redeemType.addItem(row, val)
except Exception as err:
print(err)
def FN_GET_Branch(self):
self.Qcombo_branch.clear()
i = 0
try:
# Todo: method for fills the Branch combobox
self.conn = db1.connect()
mycursor = self.conn.cursor()
val3 = ""
for a in range(len(self.Qcombo_company.currentData())):
if a < len(self.Qcombo_company.currentData()) - 1:
val3 = val3 + "'" + self.Qcombo_company.currentData()[a] + "',"
else:
val3 = val3 + "'" + self.Qcombo_company.currentData()[a] + "'"
sqlite3 = "SELECT BRANCH_DESC_A ,BRANCH_NO FROM BRANCH WHERE COMPANY_ID in (" + val3 + ")"
mycursor.execute(sqlite3)
records = mycursor.fetchall()
for row, val in records:
for bra in CL_userModule.branch:
if val in bra:
self.Qcombo_branch.addItem(row, val)
i += 1
mycursor.close()
self.Qcombo_branch.setCurrentIndex(-1)
except Exception as err:
print(err)
def FN_GET_COMPANIES(self):
try:
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY")
records = mycursor.fetchall()
print(records)
for row, val in records:
self.Qcombo_company.addItem(row, val)
mycursor.close()
except Exception as err:
print(err)
def FN_SEARCH_DETAILS(self):
try:
self.FN_SEARCH()
for i in reversed(range(self.Qtable_customer.rowCount())):
self.Qtable_customer.removeRow(i)
conn = db1.connect()
mycursor = conn.cursor()
self.sql = "SELECT MEMBERSHIP_POINTS_TRANS 'رقم العمليه',cp.POSC_CUST_ID 'رقم العميل', c.POSC_NAME 'اسم العميل',REDEEM_TYPE_ID 'نوع الإسترجاع',COMPANY_ID 'الشركه' ,BRANCH_NO 'الفرع',POS_NO 'ماكينه الكاشير',INVOICE_NO 'رقم الفاتوره' ,INVOICE_DATE 'تاريخ الفاتوره', POSC_POINTS_BEFORE 'نقاط العميل قبل', TRANS_POINTS_QTY 'النقاط المكتسبه',POSC_POINTS_AFTER 'تقاط العميل بعد',TRANS_STATUS 'الحاله' FROM Hyper1_Retail.LOYALITY_POINTS_TRANSACTION_LOG cp " \
" left outer join Hyper1_Retail.POS_CUSTOMER c " \
" on cp.POSC_CUST_ID = c.POSC_CUST_ID where " + self.whereClause1
mycursor.execute(self.sql)
print(self.sql)
myresult = mycursor.fetchall()
for row_number, row_data in enumerate(myresult):
self.Qtable_customer.insertRow(row_number)
for column_number, data in enumerate(row_data):
if column_number == 3:
data = util.FN_GET_REDEEMTYPE_DESC(str(data))
elif column_number == 4:
data = util.FN_GET_COMP_DESC(str(data))
elif column_number == 5:
data = util.FN_GET_BRANCH_DESC(str(data))
elif column_number == 12:
data = self.FN_GET_TRANS_STATUS(str(data))
self.Qtable_customer.setItem(row_number, column_number, QTableWidgetItem(str(data)))
self.Qtable_customer.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
mycursor.close()
except Exception as err:
print(err)
def FN_GET_TRANS_STATUS(self,status):
if status == '2':
return "Completed"
elif status== '0':
return "Reversed"
elif status == '1':
return "Cancelled"
def FN_SEARCH(self):
try:
self.FN_CLEAR_FEILDS()
branchs = self.Qcombo_branch.currentData()
companies = self.Qcombo_company.currentData()
date_from = self.Qdate_from.dateTime().toString('yyyy-MM-dd')
date_to = self.Qdate_to.dateTime().toString('yyyy-MM-dd')
redeem_type = self.CMB_redeemType.currentData()
conn = db1.connect()
mycursor = conn.cursor()
whereClause = ""
whereClause = whereClause + " `TRANS_CREATED_ON` >= '" + date_from + "' and `TRANS_CREATED_ON` <= '" + date_to + "' "
whereClause = whereClause + " and REDEEM_TYPE_ID = '" +redeem_type+"'"
company_list = companies
if len(company_list) > 0:
if len(company_list) == 1:
whereClause = whereClause + " and `COMPANY_ID` = '" + company_list[0] + "'"
else:
company_list_tuple = tuple(company_list)
whereClause = whereClause + " and `COMPANY_ID` in {}".format(company_list_tuple)
# get branchs
branch_list = branchs
if len(branch_list) > 0:
if len(branch_list) == 1:
whereClause = whereClause + " and BRANCH_NO ='" + branch_list[0] + "'"
else:
branch_list_tuple = tuple(branch_list)
whereClause = whereClause + " and BRANCH_NO in {} ".format(branch_list_tuple)
sql_select_query = "SELECT sum( TRANS_POINTS_QTY) ,sum(`TRANS_POINTS_VALUE`) FROM Hyper1_Retail.LOYALITY_POINTS_TRANSACTION_LOG where " + whereClause + " group by REDEEM_TYPE_ID"
#print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
if len(records) >0:
self.Qline_points.setText(str (records[0][0]))
self.Qline_point_value.setText(str (records[0][1]))
mycursor.close()
self.whereClause1 = whereClause
except Exception as err:
print(err)
def printpreviewDialog(self):
try:
# Todo: method for export reports pdf file
points = self.Qline_points.text().strip()
pointValue = self.Qline_point_value.text().strip()
redeem_type = self.CMB_redeemType.currentText()
title = Text()
title.setFooter(
" س ت 36108 ملف ضريبي 212/306/5 مأموريه ضرائب الشركات المساهمة رقم التسجيل بضرائب المبيعات 153/846/310 ")
title.setFont('Scheherazade-Regular.ttf')
title.setFontsize(10)
title.setName("Redeem Type values")
#title.setcodeText("15235")
title.setwaterText("hyperone company")
#title.settelText("1266533")
title.setbrachText("Entrance 1,EL Sheikh Zayed City")
#title.setCursor("Testing")
title.setQuery(self.sql)
title.setCursor(self.field_names)
data = [['Redeem Type:' , ' ',redeem_type ],
['No of points ' , ' ',points ,' ', 'Point value:' , ' ',pointValue],]
title.setData(data)
body()
QtWidgets.QMessageBox.information(self, "Success", "Report is printed successfully")
import os
os.system('my_file.pdf')
except Exception as err:
print(err)
def FN_CLEAR_FEILDS (self):
self.Qline_points.setText("")
self.Qline_point_value.setText("")
class CL_installmentReport(QtWidgets.QDialog):
switch_window = QtCore.pyqtSignal()
dirname = ''
field_names = [
'رقم البرنامج', 'اسم البرنامج', 'مجموعات العملاء', 'الشركه', 'الفرع',
'الإداره', 'القسم', 'من تاريخ', 'إلى تاريخ', 'المصاريف الإداريه',
'الحاله', 'مده التقسيط', 'تقسيط هايبر', 'تقسيط بنك', 'تقسيط مورد',
'نسبه هايبر', 'نسبه المورد', 'نسبه العميل'
]
barcodes = []
def __init__(self):
super(CL_installmentReport, self).__init__()
cwd = Path.cwd()
mod_path = Path(__file__).parent.parent.parent
self.dirname = mod_path.__str__() + '/presentation/reports_ui'
conn = db1.connect()
def FN_EXPORT(self):
try:
filename = QFileDialog.getSaveFileName(self, "Save File", '',
"(*.xls)")
wb = xlwt.Workbook()
# add_sheet is used to create sheet.
sheet = wb.add_sheet('Sheet 1')
sheet.write(0, 0, 'رقم البرنامج')
sheet.write(0, 1, 'اسم البرنامج')
sheet.write(0, 2, 'مجموعات العملاء')
sheet.write(0, 3, 'الشركه')
sheet.write(0, 4, 'الفرع')
sheet.write(0, 5, 'الإداره')
sheet.write(0, 6, 'القسم')
sheet.write(0, 7, 'من تاريخ')
sheet.write(0, 8, 'إلى تاريخ')
sheet.write(0, 9, 'المصاريف الإداريه')
sheet.write(0, 10, 'الحاله')
sheet.write(0, 11, 'مده التقسيط')
sheet.write(0, 12, 'تقسيط هايبر')
sheet.write(0, 13, 'تقسيط بنك')
sheet.write(0, 14, 'تقسيط مورد')
sheet.write(0, 15, 'نسبه هايبر')
sheet.write(0, 16, 'نسبه المورد')
sheet.write(0, 17, 'نسبه العميل')
rowNo = self.Qtable_inst.rowCount() + 1
for currentColumn in range(self.Qtable_inst.columnCount()):
for currentRow in range(self.Qtable_inst.rowCount()):
teext = str(
self.Qtable_inst.item(currentRow,
currentColumn).text())
if teext != None:
sheet.write(currentRow + 1, currentColumn, teext)
else:
sheet.write(currentRow + 1, currentColumn, ' ')
# # wb.save('test11.xls')
wb.save(str(filename[0]))
# wb.close()
import webbrowser
webbrowser.open(filename[0])
except Exception as err:
print(err)
def FN_LOAD_DISPLAY(self):
try:
filename = self.dirname + '/installment.ui'
loadUi(filename, self)
conn = db1.connect()
mycursor = conn.cursor()
self.Qbtn_search.clicked.connect(self.FN_SEARCH)
self.Qbtn_export.clicked.connect(self.FN_EXPORT)
for row_number, row_data in enumerate(CL_userModule.myList):
if row_data[1] == 'Installment':
if row_data[4] == 'None':
print('hh')
else:
sql_select_query = "select i.ITEM_DESC from Hyper1_Retail.SYS_FORM_ITEM i where ITEM_STATUS= 1 and i.item_id =%s"
x = (row_data[4], )
mycursor.execute(sql_select_query, x)
result = mycursor.fetchone()
# print(result)
if result[0] == 'search':
self.Qbtn_search.setEnabled(True)
elif result[0] == 'export':
self.Qbtn_export.setEnabled(True)
self.Qbtn_loadItems.clicked.connect(self.FN_LOAD_BARCODES)
# Apply Style For Design
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
#Get installment type
self.Qcombo_installmentType.addItem("أختر المده", '0')
self.FN_GET_installment_types_period()
# test Multi selection for company
#drob down list with multiselection for company
self.Qcombo_company = CheckableComboBox(self)
self.Qcombo_company.setGeometry(490, 20, 179, 20)
self.Qcombo_company.setLayoutDirection(QtCore.Qt.RightToLeft)
self.Qcombo_company.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.FN_GET_Company()
# TODO Click listner for changing list of company
self.Qcombo_company.model().dataChanged.connect(self.FN_GET_Branch)
#drob down list with multiselection for bracnch
self.Qcombo_branch = CheckableComboBox(self)
self.Qcombo_branch.setGeometry(490, 60, 179, 20)
self.Qcombo_branch.setLayoutDirection(QtCore.Qt.RightToLeft)
self.Qcombo_branch.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.FN_GET_Branch()
#validation for not pick date before today
datefrom = str(datetime.today().strftime('%Y-%m-%d'))
xfrom = datefrom.split("-")
d = QDate(int(xfrom[0]), int(xfrom[1]), int(xfrom[2]))
self.Qdate_from.setDate(d)
self.Qdate_to.setDate(d)
# Get customer Groupe
self.Qcombo_customerGroupe = CheckableComboBox(self)
self.Qcombo_customerGroupe.setGeometry(490, 100, 179, 20)
self.Qcombo_customerGroupe.setLayoutDirection(
QtCore.Qt.RightToLeft)
self.Qcombo_customerGroupe.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.FN_GET_customerGroupe()
# Multi selection for department
self.Qcombo_department = CheckableComboBox(self)
self.Qcombo_department.setGeometry(630, 190, 85, 22)
self.Qcombo_department.setEnabled(False)
self.Qcombo_department.setLayoutDirection(QtCore.Qt.RightToLeft)
self.Qcombo_department.setStyleSheet(
"background-color: rgb(198, 207, 199)")
# TODO Click listner for changing list of department
self.Qcombo_department.model().dataChanged.connect(
self.FN_WhenChecksection)
# get Department list if check box
self.checkBox_department.stateChanged.connect(
self.FN_WhenCheckDepartment)
# self.FN_WhenCheckDepartment()
# Multi selection for sections
self.Qcombo_section = CheckableComboBox(self)
self.Qcombo_section.setGeometry(490, 190, 80, 22)
self.Qcombo_section.setLayoutDirection(QtCore.Qt.RightToLeft)
self.Qcombo_section.setEnabled(False)
self.Qcombo_section.setStyleSheet(
"background-color: rgb(198, 207, 199)")
# get sections list if check box
self.checkBox_section.stateChanged.connect(
self.FN_WhenChecksection)
# self.FN_GET_sections()
# TODO Click listner for changing list of department
self.Qcombo_section.model().dataChanged.connect(
self.FN_WhenCheckBMC_Level)
# Multi selection for BMCLevel
self.Qcombo_BMCLevel = CheckableComboBox(self)
self.Qcombo_BMCLevel.setGeometry(270, 190, 110, 22)
self.Qcombo_BMCLevel.setLayoutDirection(QtCore.Qt.RightToLeft)
self.Qcombo_BMCLevel.setEnabled(False)
self.Qcombo_BMCLevel.setStyleSheet(
"background-color: rgb(198, 207, 199)")
# get BMC LEVEL4 list if check box
self.checkBox_BMCLevel.stateChanged.connect(
self.FN_WhenCheckBMC_Level)
self.checkBox_Barcode.stateChanged.connect(
self.FN_WhenCheckBarcode)
# get Banks list if readio button clicked
self.RBTN_bank.clicked.connect(self.FN_InstallMent_Checked)
# get Vendor list if readio button clicked
self.RBTN_vendor.clicked.connect(self.FN_InstallMent_Checked)
# if readio button clicked hyperone
self.RBTN_hyperone.clicked.connect(self.FN_InstallMent_Checked)
# this function for what enabled or not when start
self.EnabledWhenOpen()
self.Rbtn_stsAll.setChecked(True)
#enable the search and the export buttons
except Exception as err:
print(err)
def FN_LOAD_BARCODES(self):
try:
self.Qlab_msg.setText("")
options = QFileDialog.Options()
options |= QFileDialog.DontUseNativeDialog
fileName, _ = QFileDialog.getOpenFileName(
self,
"QFileDialog.getOpenFileName()",
"",
" Files (*.xlsx)",
options=options)
self.barcodes = []
if fileName != '':
#self.LE_fileName.setText(fileName)
wb = xlrd.open_workbook(fileName)
sheet = wb.sheet_by_index(0)
conn = db1.connect()
mycursor = conn.cursor()
for i in range(sheet.nrows):
error = 0
bar = int(sheet.cell_value(i, 0))
self.barcodes.append(str(bar))
if len(self.barcodes) > 0:
self.Qlab_msg.setText("تم تحميل الأصناف")
#print(barcodes)
except Exception as err:
print(err)
# this function for what enabled or not when start
def EnabledWhenOpen(self):
self.checkBox_department.setEnabled(True)
self.checkBox_section.setEnabled(False)
self.Qcombo_section.setEnabled(False)
self.checkBox_BMCLevel.setEnabled(False)
self.Qcombo_BMCLevel.setEnabled(False)
#get installments period list
def FN_GET_installment_types_period(self):
#self.Qcombo_installmentType.clear()
conn = db1.connect()
mycursor = conn.cursor()
mycursor.execute(
"SELECT InstT_Installment_Period ,INSTT_TYPE_ID FROM INSTALLMENT_TYPE"
)
records = mycursor.fetchall()
mycursor.close()
for row, val in records:
self.Qcombo_installmentType.addItem(row, val)
# get branches list
# get companys list
def FN_GET_Company(self):
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY")
records = mycursor.fetchall()
for row, val in records:
self.Qcombo_company.addItem(row, val)
mycursor.close()
# get branches list
def FN_GET_Branch(self):
self.Qcombo_branch.clear()
i = 0
try:
# Todo: method for fills the Branch combobox
self.conn = db1.connect()
mycursor = self.conn.cursor()
val3 = ""
for a in range(len(self.Qcombo_company.currentData())):
if a < len(self.Qcombo_company.currentData()) - 1:
val3 = val3 + "'" + self.Qcombo_company.currentData(
)[a] + "',"
else:
val3 = val3 + "'" + self.Qcombo_company.currentData(
)[a] + "'"
sqlite3 = "SELECT BRANCH_DESC_A ,BRANCH_NO FROM BRANCH WHERE COMPANY_ID in (" + val3 + ")"
mycursor.execute(sqlite3)
records = mycursor.fetchall()
for row, val in records:
for bra in CL_userModule.branch:
if val in bra:
self.Qcombo_branch.addItem(row, val)
i += 1
mycursor.close()
self.Qcombo_branch.setCurrentIndex(-1)
except:
print(sys.exc_info())
# get customer Groupe list
def FN_GET_customerGroupe(self):
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT CG_DESC,CG_GROUP_ID FROM CUSTOMER_GROUP")
records = mycursor.fetchall()
for row, val in records:
self.Qcombo_customerGroupe.addItem(row, val)
mycursor.close()
# after check department check box
def FN_WhenCheckDepartment(self):
if self.checkBox_department.isChecked():
self.FN_GET_Department()
self.Qcombo_department.setEnabled(True)
self.checkBox_section.setEnabled(True)
self.Qcombo_department.setCurrentIndex(-1)
else:
self.Qcombo_department.unCheckedList()
self.Qcombo_department.setEnabled(False)
self.checkBox_section.setEnabled(False)
self.checkBox_section.setChecked(False)
self.checkBox_BMCLevel.setChecked(False)
self.Qcombo_department.setCurrentIndex(-1)
# get Department list
def FN_GET_Department(self):
self.Qcombo_department.clear()
i = 0
try:
# Todo: method for fills the section combobox
"""
self.Qcombo_department.clear()
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT DEPARTMENT_DESC,DEPARTMENT_ID FROM DEPARTMENT")
records = mycursor.fetchall()
for row, val in records:
for sec in CL_userModule.section :
if val in sec:
self.Qcombo_department.addItem(row, val)
i += 1
mycursor.close()
"""
print("FN_GET_Department", CL_userModule.section)
for row, val, row1, val1 in CL_userModule.section:
self.Qcombo_department.addItem(val1, row1)
except:
print(sys.exc_info())
# after check section check box
def FN_WhenChecksection(self):
if self.checkBox_section.isChecked():
self.FN_GET_sections()
self.checkBox_BMCLevel.setEnabled(True)
self.Qcombo_section.setEnabled(True)
self.Qcombo_section.setCurrentIndex(-1)
else:
self.Qcombo_section.unCheckedList()
self.checkBox_BMCLevel.setChecked(False)
self.checkBox_BMCLevel.setEnabled(False)
self.Qcombo_section.setEnabled(False)
self.Qcombo_section.setCurrentIndex(-1)
# self.Qcombo_section.unChecked()
def FN_WhenCheckBarcode(self):
if self.checkBox_Barcode.isChecked():
self.Qline_barcode.setEnabled(True)
self.Qbtn_loadItems.setEnabled(True)
else:
self.Qline_barcode.setEnabled(False)
self.Qbtn_loadItems.setEnabled(False)
self.Qlab_msg.setText('')
# get sections list
def FN_GET_sections(self):
self.Qcombo_section.clear()
i = 0
try:
conn = db1.connect()
mycursor = conn.cursor()
#print("currentData", self.Qcombo_department.currentData())
val3 = ""
for a in range(len(self.Qcombo_department.currentData())):
if a < len(self.Qcombo_department.currentData()) - 1:
val3 = val3 + "'" + self.Qcombo_department.currentData(
)[a] + "',"
else:
val3 = val3 + "'" + self.Qcombo_department.currentData(
)[a] + "'"
#print("deparments", val3)
mycursor.execute(
"SELECT SECTION_DESC,SECTION_ID FROM SECTION where DEPARTMENT_ID in ("
+ val3 + ")")
# print("Query"+"SELECT SECTION_DESC,SECTION_ID FROM SECTION where DEPARTMENT_ID in ("+val3+")")
records = mycursor.fetchall()
mycursor.close()
for row, val in records:
self.Qcombo_section.addItem(row, val)
i += 1
except:
print(sys.exc_info())
# after check department check box
# after check BMC Level check box
def FN_WhenCheckBMC_Level(self):
if self.checkBox_BMCLevel.isChecked():
self.FN_GET_BMC_Level()
self.Qcombo_BMCLevel.setEnabled(True)
self.Qcombo_BMCLevel.setCurrentIndex(-1)
# self.FN_
else:
self.Qcombo_BMCLevel.unCheckedList()
self.checkBox_BMCLevel.setChecked(False)
self.Qcombo_BMCLevel.setEnabled(False)
self.Qcombo_BMCLevel.setCurrentIndex(-1)
# get BMC LEVEL4 list
def FN_GET_BMC_Level(self):
self.Qcombo_BMCLevel.clear()
i = 0
try:
conn = db1.connect()
mycursor = conn.cursor()
val3 = ""
for a in range(len(self.Qcombo_section.currentData())):
if a < len(self.Qcombo_section.currentData()) - 1:
val3 = val3 + "'" + self.Qcombo_section.currentData(
)[a] + "',"
else:
val3 = val3 + "'" + self.Qcombo_section.currentData(
)[a] + "'"
#print("sections", val3)
mycursor.execute(
"SELECT BMC_LEVEL4_DESC,BMC_LEVEL4 FROM BMC_LEVEL4 where SECTION_ID in ("
+ val3 + ")")
records = mycursor.fetchall()
mycursor.close()
for row, val in records:
self.Qcombo_BMCLevel.addItem(row, val)
i += 1
except:
print(sys.exc_info())
def FN_InstallMent_Checked(self):
try:
if self.RBTN_bank.isChecked():
self.FN_GET_Banks()
self.Qcombo_bank.setEnabled(True)
self.Qcombo_vendor.setEnabled(False)
self.Qcombo_vendor.clear()
elif self.RBTN_vendor.isChecked():
self.Qcombo_bank.setEnabled(False)
self.Qcombo_bank.clear()
self.FN_GET_Vendor()
self.Qcombo_vendor.setEnabled(True)
elif self.RBTN_hyperone.isChecked():
self.Qcombo_bank.setEnabled(False)
self.Qcombo_bank.clear()
self.Qcombo_vendor.setEnabled(False)
self.Qcombo_vendor.clear()
# TO clear Data From QTable
except:
print(sys.exc_info())
# get Banks list
def FN_GET_Banks(self):
self.Qcombo_bank.clear()
self.Qcombo_bank.addItem("أختر بنك", "0")
conn = db1.connect()
mycursor = conn.cursor()
mycursor.execute("SELECT Bank_Desc,Bank_ID FROM BANK")
records = mycursor.fetchall()
mycursor.close()
for row, val in records:
self.Qcombo_bank.addItem(row, val)
# get Vendor list
def FN_GET_Vendor(self):
self.Qcombo_vendor.clear()
self.Qcombo_vendor.addItem("أختر مورد", "0")
conn = db1.connect()
mycursor = conn.cursor()
mycursor.execute(
"SELECT SPONSOR_NAME,SPONSOR_ID FROM Hyper1_Retail.SPONSOR")
records = mycursor.fetchall()
mycursor.close()
for row, val in records:
self.Qcombo_vendor.addItem(row, val)
def FN_CHECK_VALID_BARCCODE(self, id):
try:
conn = db1.connect()
mycursor11 = conn.cursor()
sql = "SELECT * FROM Hyper1_Retail.POS_ITEM where POS_GTIN = '" + str(
id) + "'"
mycursor11.execute(sql)
myresult = mycursor11.fetchone()
if mycursor11.rowcount > 0:
mycursor11.close()
return True
else:
mycursor11.close()
return False
except Exception as err:
print(err)
def FN_SEARCH(self):
try:
for i in reversed(range(self.Qtable_inst.rowCount())):
self.Qtable_inst.removeRow(i)
cust_gps = self.Qcombo_customerGroupe.currentData()
branchs = self.Qcombo_branch.currentData()
companies = self.Qcombo_company.currentData()
bar = self.Qline_barcode.text()
BMC = []
sec = []
dep = []
conn = db1.connect()
mycursor = conn.cursor()
whereClause = ""
ret = True
if self.checkBox_Barcode.isChecked():
if len(bar) > 0:
ret = self.FN_CHECK_VALID_BARCCODE(bar)
self.barcodes = []
else:
self.barcodes = []
if ret == True:
if self.checkBox_BMCLevel.isChecked():
#get bmc
BMC = self.Qcombo_BMCLevel.currentData()
elif self.checkBox_section.isChecked():
#get sec
sec = self.Qcombo_section.currentData()
elif self.checkBox_department.isChecked():
# get dep
dep = self.Qcombo_department.currentData()
instType = self.Qcombo_installmentType.currentData()
instDesc = self.LE_desc.text()
date_from = self.Qdate_from.dateTime().toString('yyyy-MM-dd')
date_to = self.Qdate_to.dateTime().toString('yyyy-MM-dd')
### Call Validation Function
if self.Rbtn_stsActive.isChecked():
whereClause = whereClause + ' INST_STATUS = 1'
elif self.Rbtn_stsInactive.isChecked():
whereClause = whereClause + ' INST_STATUS = 0'
elif self.Rbtn_stsAll.isChecked():
whereClause = whereClause + ' INST_STATUS in ( 0,1)'
if self.RBTN_bank.isChecked():
if self.Qcombo_bank.currentData() == "0":
whereClause = whereClause + "and s.BANK_ID <> '' "
else:
whereClause = whereClause + "and s.BANK_ID = '" + str(
self.Qcombo_bank.currentData()) + "'"
elif self.RBTN_vendor.isChecked():
if self.Qcombo_vendor.currentData() == "0":
whereClause = whereClause + " and s.SPONSOR_ID <> '' "
else:
whereClause = whereClause + " and s.SPONSOR_ID = '" + str(
self.Qcombo_vendor.currentData()) + "'"
elif self.RBTN_hyperone.isChecked():
whereClause = whereClause + " and s.HYPERONE <> '' "
#elif self.RBTN_all.isChecked():
#whereClause = whereClause + " and s.HYPERONE <> '' "
if instDesc != '':
whereClause = whereClause + " and p.INST_DESC like '%" + str(
instDesc) + "%' "
# get COMPANY
if len(companies) > 0:
if len(companies) == 1:
whereClause = whereClause + " and c.COMPANY_ID = '" + companies[
0] + "'"
else:
company_list_tuple = tuple(companies)
whereClause = whereClause + " and c.COMPANY_ID in {}".format(
company_list_tuple)
# get branchs
if len(branchs) > 0:
if len(branchs) == 1:
whereClause = whereClause + " and b.BRANCH_NO ='" + branchs[
0] + "'"
else:
branch_list_tuple = tuple(branchs)
whereClause = whereClause + " and b.BRANCH_NO in {} ".format(
branch_list_tuple)
# get customer gp id
if len(cust_gps) > 0:
if len(cust_gps) == 1:
whereClause = whereClause + " and g.CG_GROUP_ID ='" + str(
cust_gps[0]) + "'"
else:
cust_gp_list_tuple = tuple(cust_gps)
whereClause = whereClause + " and g.CG_GROUP_ID in {}".format(
cust_gp_list_tuple)
#get BMC levels
if len(BMC) > 0:
if len(BMC) == 1:
whereClause = whereClause + " and sec.BMC_ID ='" + str(
BMC[0]) + "'"
else:
bmc_tuple = tuple(BMC)
whereClause = whereClause + " and sec.BMC_ID in {}".format(
bmc_tuple)
elif len(sec) > 0:
if len(sec) == 1:
whereClause = whereClause + " and sec.SECTION_ID ='" + str(
sec[0]) + "'"
else:
sec_tuple = tuple(sec)
whereClause = whereClause + " and sec.SECTION_ID in {}".format(
sec_tuple)
elif len(dep) > 0:
if len(dep) == 1:
whereClause = whereClause + " and sec.DEPARTMENT_ID ='" + str(
dep[0]) + "'"
else:
dep_tuple = tuple(dep)
whereClause = whereClause + " and sec.DEPARTMENT_ID in {}".format(
dep_tuple)
if instType != '0':
whereClause = whereClause + " and tp.INSTT_TYPE_ID = '" + str(
instType) + "'"
if len(bar) > 0:
whereClause = whereClause + " and ii.POS_GTIN = '" + str(
bar) + "'"
if len(self.barcodes) > 0:
if len(self.barcodes) == 1:
whereClause = whereClause + "and ii.POS_GTIN = '" + str(
self.barcodes[0]) + "'"
else:
bar_tuple = tuple(self.barcodes)
whereClause = whereClause + " and ii.POS_GTIN in {}".format(
bar_tuple)
whereClause = whereClause + " and INST_VALID_FROM >= '" + date_from + "' and INST_VALID_TO <= '" + date_to + "' "
sql_select_query = "SELECT distinct p.INST_PROGRAM_ID, p.INST_DESC ,'customer_gp','company','branch','dep', 'sec',p.INST_VALID_FROM,p.INST_VALID_TO,p.INST_ADMIN_EXPENSES_PERC,p.INST_STATUS , tp.INSTT_DESC,s.HYPERONE,bank.Bank_Desc ,spon.SPONSOR_NAME , r.INSTR_HYPER_RATE,r.INSTR_SPONSOR_RATE,r.INSTR_CUSTOMER_RATE FROM Hyper1_Retail.INSTALLMENT_PROGRAM p inner join Hyper1_Retail.INSTALLMENT_RULE r on p.INSTR_RULEID = r.INSTR_RULEID inner join Hyper1_Retail.INSTALLMENT_TYPE tp on r.INSTT_TYPE_ID = tp.INSTT_TYPE_ID inner join Hyper1_Retail.INSTALLMENT_GROUP g on p.INST_PROGRAM_ID = g.INST_PROGRAM_ID inner join Hyper1_Retail.INSTALLMENT_BRANCH b on p.INST_PROGRAM_ID = b.INST_PROGRAM_ID inner join Hyper1_Retail.INSTALLMENT_SPONSOR s on p.INSTR_RULEID = s.INSTR_RULEID left outer join Hyper1_Retail.BANK bank on s.BANK_ID = bank.Bank_ID left outer join Hyper1_Retail.SPONSOR spon on spon.SPONSOR_ID = s.SPONSOR_ID inner join CUSTOMER_GROUP cg on g.CG_GROUP_ID = cg.CG_GROUP_ID inner join Hyper1_Retail.BRANCH branch on b.COMPANY_ID = branch.COMPANY_ID and b.BRANCH_NO = branch.BRANCH_NO inner join Hyper1_Retail.COMPANY c on branch.COMPANY_ID =c.COMPANY_ID " \
"left outer join Hyper1_Retail.INSTALLMENT_SECTION sec on sec.INSTR_RULEID = r.INSTR_RULEID" \
" left outer join Hyper1_Retail.INSTALLMENT_ITEM ii on ii.INSTR_RULEID = r.INSTR_RULEID " \
" where"+ whereClause
print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
inst_prog_no = ''
for row_number, row_data in enumerate(records):
#get customer groups
self.Qtable_inst.insertRow(row_number)
inst_prog_no = ''
for column_number, data in enumerate(row_data):
if column_number == 0:
inst_prog_no = str(data)
if column_number == 2:
data = self.FN_GET_CUST_GP(inst_prog_no)
if column_number == 3:
data = self.FN_GET_COMP(inst_prog_no)
if column_number == 4:
data = self.FN_GET_BRANCH(inst_prog_no)
if column_number == 5:
data = self.FN_GET_DEPT(inst_prog_no)
if column_number == 6:
data = self.FN_GET_SEC(inst_prog_no)
if column_number == 10:
data = util.FN_GET_STATUS_DESC(str(data))
if data == None or data == '':
data = '----'
self.Qtable_inst.setItem(row_number, column_number,
QTableWidgetItem(str(data)))
self.Qtable_inst.setEditTriggers(
QtWidgets.QTableWidget.NoEditTriggers)
mycursor.close()
else:
QtWidgets.QMessageBox.warning(self, "خطأ", "الباركود غير صحيح")
except Exception as err:
print(err)
def FN_GET_DEPT(self, inst_no):
try:
conn = db1.connect()
mycursor = conn.cursor()
dept = ''
sql_select_query ="SELECT DEPARTMENT_DESC FROM Hyper1_Retail.INSTALLMENT_SECTION isec " \
" inner join Hyper1_Retail.INSTALLMENT_RULE irule on isec.INSTR_RULEID = irule.INSTR_RULEID " \
" inner join Hyper1_Retail.INSTALLMENT_PROGRAM p on irule.INSTR_RULEID = p.INSTR_RULEID" \
" inner join Hyper1_Retail.DEPARTMENT d " \
" on isec.DEPARTMENT_ID = d.DEPARTMENT_ID " \
" where p.INST_PROGRAM_ID = '"+inst_no+"'"
#print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for rec in records:
dept = dept + ',' + rec[0]
mycursor.close()
return dept
except Exception as err:
print(err)
def FN_GET_SEC(self, inst_no):
try:
conn = db1.connect()
mycursor = conn.cursor()
sec = ''
sql_select_query = "SELECT SECTION_DESC FROM Hyper1_Retail.INSTALLMENT_SECTION isec " \
" inner join Hyper1_Retail.INSTALLMENT_RULE irule on isec.INSTR_RULEID = irule.INSTR_RULEID " \
" inner join Hyper1_Retail.INSTALLMENT_PROGRAM p on irule.INSTR_RULEID = p.INSTR_RULEID" \
" inner join Hyper1_Retail.SECTION s "\
" on isec.SECTION_ID = s.SECTION_ID where p.INST_PROGRAM_ID = '" + inst_no + "'"
#print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for rec in records:
sec = sec + ',' + rec[0]
mycursor.close()
return sec
except Exception as err:
print(err)
def FN_GET_CUST_GP(self, inst_no):
try:
conn = db1.connect()
mycursor = conn.cursor()
cust_gp = ''
sql_select_query ="SELECT CG_DESC FROM Hyper1_Retail.INSTALLMENT_GROUP ig inner join Hyper1_Retail.CUSTOMER_GROUP cg " \
"on ig.CG_GROUP_ID = cg.CG_GROUP_ID where INST_PROGRAM_ID = '"+inst_no+"' and CG_DESC !='H1'"
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for rec in records:
cust_gp = cust_gp + ',' + rec[0]
mycursor.close()
return cust_gp
except Exception as err:
print(err)
def FN_GET_COMP(self, inst_no):
try:
conn = db1.connect()
mycursor = conn.cursor()
comp = ''
sql_select_query = "SELECT distinct COMPANY_DESC FROM Hyper1_Retail.INSTALLMENT_BRANCH ib inner join Hyper1_Retail.COMPANY c on ib.COMPANY_ID =c.COMPANY_ID where INST_PROGRAM_ID = '" + inst_no + "'"
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for rec in records:
comp = comp + ' , ' + rec[0]
mycursor.close()
return comp
except Exception as err:
print(err)
def FN_GET_BRANCH(self, inst_no):
try:
conn = db1.connect()
mycursor = conn.cursor()
branch = ''
sql_select_query = "SELECT distinct BRANCH_DESC_A,ib.COMPANY_ID FROM Hyper1_Retail.INSTALLMENT_BRANCH ib inner join Hyper1_Retail.BRANCH branch on ib.COMPANY_ID = branch.COMPANY_ID and ib.BRANCH_NO = branch.BRANCH_NO where INST_PROGRAM_ID = '" + inst_no + "'"
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for rec in records:
branch = branch + ' , ' + rec[0]
mycursor.close()
return branch
except Exception as err:
print(err)