def FN_ASSIGN(self):
self.CMB_roleName = CheckableComboBox(self)
self.CMB_roleName.setGeometry(100, 85, 179, 18)
self.CMB_roleName.setLayoutDirection(QtCore.Qt.LeftToRight)
self.CMB_roleName.setStyleSheet("background-color: rgb(198, 207, 199)")
filename = self.dirname + '/assignUserToRole.ui'
loadUi(filename, self)
self.BTN_assignRole.clicked.connect(self.FN_ASSIGN_ROLE)
self.CMB_userRoleStatus.addItems(["Active", "Inactive"])
self.FN_GET_USERS()
self.FN_GET_USERID()
self.FN_GET_ROLES()
self.CMB_userName.currentIndexChanged.connect(self.FN_GET_USERID)
def __init__(self):
#Todo: method for initialization components
super(CL_report1, self).__init__()
try:
cwd = Path.cwd()
mod_path = Path(__file__).parent.parent.parent
dirname = mod_path.__str__() + '/presentation/reports_ui'
filename = dirname + '/Coupon_display.ui'
loadUi(filename, self)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
self.setWindowTitle('تقرير الكوبون')
self.Qcombo_company = CheckableComboBox(self)
self.Qcombo_company.setGeometry(450, 50, 200, 18)
self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_company.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_branchEdition = CheckableComboBox(self)
self.Qcombo_branchEdition.setGeometry(450, 120, 200, 18)
self.Qcombo_branchEdition.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_branchEdition.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qbtn_exit.clicked.connect(self.FN_close)
self.Qbtn_search.clicked.connect(self.FN_loadData)
self.FN_GET_Company()
self.FN_GET_Branch()
self.Qtable_promotion.setEditTriggers(
QtWidgets.QTableWidget.NoEditTriggers)
self.radioBtnPromExpired.clicked.connect(self.FN_Check_Expired)
self.radioBtnPromActive.clicked.connect(self.FN_Check_Active)
self.radioBtnPromAll.clicked.connect(self.FN_Check_All)
self.Qbtn_exprot.clicked.connect(self.handleSave)
self.Qbtn_print.clicked.connect(self.printpreviewDialog)
self.disable()
except:
print(sys.exc_info())
def FN_LOADUI(self):
filename = self.dirname + '/createCoupon.ui'
loadUi(filename, self)
self.Qcombo_company = CheckableComboBox(self)
self.Qcombo_company.setGeometry(550, 35, 271, 25)
self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_company.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_branch = CheckableComboBox(self)
self.Qcombo_branch.setGeometry(550, 65, 271, 25)
self.Qcombo_branch.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_branch.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.FN_GET_Company()
self.FN_GET_Branch()
self.FN_EnableDiscVal()
self.checkBox_Multi.toggled.connect(self.FN_endableMultiUser)
self.radioButton_Value.clicked.connect(self.FN_EnableDiscVal)
self.radioButton_Percentage.clicked.connect(self.FN_EnablePercentage)
self.BTN_createCoupon.clicked.connect(self.FN_Create)
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.setMinimumDate(d)
self.Qdate_to.setMinimumDate(d)
self.setWindowFlags(QtCore.Qt.WindowCloseButtonHint
| QtCore.Qt.WindowMinimizeButtonHint)
self.LE_desc_5.setEnabled(False)
css_path = Path(__file__).parent.parent.parent
# Apply Style For Design
self.label_num.setStyleSheet(label_num)
self.desc_5.setStyleSheet(desc_5)
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
this_moment = QtCore.QTime.currentTime()
self.Qtime_from.setMinimumTime(this_moment)
def FN_LOAD_DISPlAY(self):
try:
filename = self.dirname + '/createModifyReedemItem.ui'
loadUi(filename, self)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
conn = db1.connect()
mycursor = conn.cursor()
self.Qbtn_search.clicked.connect(self.FN_SEARCH_REDITEM)
self.Qbtn_search_all.clicked.connect(self.FN_REFRESH_DATA_GRID)
#self.Qbtn_export.clicked.connect(self.FN_SAVE)
self.Qbtn_exit.clicked.connect(self.FN_exit)
self.Qtable_redeem.setEditTriggers(
QtWidgets.QTableWidget.NoEditTriggers)
self.Qtable_redeem.doubleClicked.connect(self.FN_GET_REDITEM)
self.Qradio_active.setChecked(True)
self.Qcombo_group3 = CheckableComboBox(self)
self.Qcombo_group3.setGeometry(380, 45, 179, 18)
self.Qcombo_group3.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_group4 = CheckableComboBox(self)
self.Qcombo_group4.setGeometry(380, 90, 179, 18)
self.Qcombo_group4.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.CMB_branch.hide()
self.CMB_company.hide()
self.FN_GET_COMPANIES()
self.FN_GET_BRANCHES()
for row_number, row_data in enumerate(CL_userModule.myList):
if row_data[1] == 'Redeem_Item':
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] == 'create':
self.Qbtn_create.setEnabled(True)
self.Qbtn_create.clicked.connect(
self.FN_CREATE_REDITEM)
elif result[0] == 'modify':
self.Qbtn_modify.setEnabled(True)
self.Qbtn_modify.clicked.connect(
self.FN_MODIFY_REDITEM)
elif result[0] == 'upload':
try:
self.Qbtn_upload.setEnabled(True)
self.Qbtn_upload.clicked.connect(
self.FN_UPLOAD_REDITEM)
except Exception as err:
print(err)
valid_from = str(datetime.today().strftime('%Y-%m-%d'))
xto = valid_from.split("-")
print(xto)
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_from.setDate(d)
# Todo: method for get branches
except Exception as err:
print(err)
class CL_redItem(QtWidgets.QDialog):
switch_window = QtCore.pyqtSignal()
dirname = ''
old_points = ''
old_valid_from = ''
old_valid_to = ''
old_status = ''
def __init__(self):
super(CL_redItem, self).__init__()
cwd = Path.cwd()
mod_path = Path(__file__).parent.parent.parent
self.dirname = mod_path.__str__() + '/presentation/loyalty_ui'
def FN_LOAD_DISPlAY(self):
try:
filename = self.dirname + '/createModifyReedemItem.ui'
loadUi(filename, self)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
conn = db1.connect()
mycursor = conn.cursor()
self.Qbtn_search.clicked.connect(self.FN_SEARCH_REDITEM)
self.Qbtn_search_all.clicked.connect(self.FN_REFRESH_DATA_GRID)
#self.Qbtn_export.clicked.connect(self.FN_SAVE)
self.Qbtn_exit.clicked.connect(self.FN_exit)
self.Qtable_redeem.setEditTriggers(
QtWidgets.QTableWidget.NoEditTriggers)
self.Qtable_redeem.doubleClicked.connect(self.FN_GET_REDITEM)
self.Qradio_active.setChecked(True)
self.Qcombo_group3 = CheckableComboBox(self)
self.Qcombo_group3.setGeometry(380, 45, 179, 18)
self.Qcombo_group3.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_group4 = CheckableComboBox(self)
self.Qcombo_group4.setGeometry(380, 90, 179, 18)
self.Qcombo_group4.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.CMB_branch.hide()
self.CMB_company.hide()
self.FN_GET_COMPANIES()
self.FN_GET_BRANCHES()
for row_number, row_data in enumerate(CL_userModule.myList):
if row_data[1] == 'Redeem_Item':
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] == 'create':
self.Qbtn_create.setEnabled(True)
self.Qbtn_create.clicked.connect(
self.FN_CREATE_REDITEM)
elif result[0] == 'modify':
self.Qbtn_modify.setEnabled(True)
self.Qbtn_modify.clicked.connect(
self.FN_MODIFY_REDITEM)
elif result[0] == 'upload':
try:
self.Qbtn_upload.setEnabled(True)
self.Qbtn_upload.clicked.connect(
self.FN_UPLOAD_REDITEM)
except Exception as err:
print(err)
valid_from = str(datetime.today().strftime('%Y-%m-%d'))
xto = valid_from.split("-")
print(xto)
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_from.setDate(d)
# Todo: method for get branches
except Exception as err:
print(err)
def FN_SEARCH_REDITEM(self):
try:
self.Qline_barcode.setEnabled(True)
self.Qcombo_group3.show()
self.Qcombo_group4.show()
self.CMB_branch.hide()
self.CMB_company.hide()
for i in reversed(range(self.Qtable_redeem.rowCount())):
self.Qtable_redeem.removeRow(i)
branchs = self.Qcombo_group4.currentData()
companies = self.Qcombo_group3.currentData()
date_from = self.Qdate_from.dateTime().toString('yyyy-MM-dd')
date_to = self.Qdate_to.dateTime().toString('yyyy-MM-dd')
conn = db1.connect()
mycursor = conn.cursor()
whereClause = ""
if self.Qradio_active.isChecked():
whereClause = whereClause + 'REDEEM_STATUS = 1 '
elif self.Qradio_inactive.isChecked():
whereClause = whereClause + 'REDEEM_STATUS = 0 '
whereClause = whereClause + " and REDEEM_VALID_FROM >= '" + date_from + "' and REDEEM_VALID_TO <= '" + date_to + "' "
barcode = self.Qline_barcode.text().strip()
if barcode != '':
whereClause = whereClause + " and POS_GTIN ='" + barcode + "'"
# get COMPANY
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)
# print(whereClause)
# print(whereClause)
sql_select_query = "select POS_GTIN, COMPANY_ID,BRANCH_NO,REDEEM_POINTS_QTY,REDEEM_VALID_FROM,REDEEM_VALID_TO,REDEEM_STATUS from Hyper1_Retail.REDEEM_ITEM where " + whereClause
#print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row_number, row_data in enumerate(records):
self.Qtable_redeem.insertRow(row_number)
for column_number, data in enumerate(row_data):
if column_number == 6:
data = util.FN_GET_STATUS_DESC(str(data))
elif column_number == 1:
data = util.FN_GET_COMP_DESC(str(data))
elif column_number == 2:
data = util.FN_GET_BRANCH_DESC(str(data))
self.Qtable_redeem.setItem(row_number, column_number,
QTableWidgetItem(str(data)))
mycursor.close()
except Exception as err:
print(err)
# Todo: method for get the redeem item details that is mentioned to modify
def FN_CREATE_REDITEM(self):
try:
branchs = self.Qcombo_group4.currentData()
companies = self.Qcombo_group3.currentData()
bar = self.Qline_barcode.text().strip()
date_from = self.Qdate_from.date().toString('yyyy-MM-dd')
date_to = self.Qdate_to.date().toString('yyyy-MM-dd')
points = self.Qline_points.text().strip()
if self.Qradio_active.isChecked():
status = 1
else:
status = 0
# mycursor = self.conn.cursor(buffered=True)
conn = db1.connect()
mycursor = conn.cursor()
creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
creationDate1 = str(datetime.today().strftime('%Y-%m-%d'))
# get COMPANY
company_list = companies
# get branchs
branch_list = branchs
if len(self.Qcombo_group3.currentData()) == 0 or len(
self.Qcombo_group4.currentData()
) == 0 or bar == '' or points == '' or date_from == '' or date_to == '':
QtWidgets.QMessageBox.warning(self, "خطأ",
"برجاء إدخال جميع البيانات")
elif date_to < date_from:
QtWidgets.QMessageBox.warning(
self, "خطأ",
"تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء"
)
elif date_from < creationDate1:
QtWidgets.QMessageBox.warning(
self, "خطأ",
"تاريخ الإنشاء يجب أن يكون أكبرمن أو يساوي تاريخ اليوم")
else:
ret1 = self.FN_CHECK_VALID_BARCCODE(bar)
if ret1 == True:
ret2 = CL_validation.FN_validation_int(points)
if ret2 == True:
for com in company_list:
for br in branch_list:
ret = self.FN_CHECK_EXIST(com, br, bar)
if ret == False:
mycursor1 = conn.cursor()
#get BMC ID
mycursor1.execute(
"select BMC_ID from Hyper1_Retail.POS_ITEM where POS_GTIN ='"
+ bar + "'")
myresult = mycursor1.fetchone()
BMC_ID = myresult[0]
sql = "INSERT INTO Hyper1_Retail.REDEEM_ITEM (POS_GTIN,COMPANY_ID," \
"BRANCH_NO,REDEEM_POINTS_QTY,REDEEM_CREATED_ON,REDEEM_CREATED_BY,REDEEM_VALID_FROM" \
",REDEEM_VALID_TO,REDEEM_STATUS,BMC_ID)" \
"values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
val = (bar, com, br, points, creationDate,
CL_userModule.user_name, date_from,
date_to, status, BMC_ID)
mycursor1.execute(sql, val)
db1.connectionCommit(conn)
mycursor1.close()
QtWidgets.QMessageBox.information(
self, "تم", "تم الإنشاء")
else:
QtWidgets.QMessageBox.warning(
self, "خطأ", "المدخلات موجوده بالفعل ")
self.FN_REFRESH_DATA_GRID()
else:
QtWidgets.QMessageBox.warning(
self, "خطأ", "النقاط يجب أن تكون أرقام")
else:
QtWidgets.QMessageBox.warning(self, "خطأ",
"الباركود غير صحيح")
except Exception as err:
print(err)
def FN_REFRESH_DATA_GRID(self):
try:
self.Qline_barcode.setEnabled(True)
self.Qcombo_group3.show()
self.Qcombo_group4.show()
self.CMB_branch.hide()
self.CMB_company.hide()
for i in reversed(range(self.Qtable_redeem.rowCount())):
self.Qtable_redeem.removeRow(i)
conn = db1.connect()
mycursor = conn.cursor()
sql_select_query = "select POS_GTIN, COMPANY_ID,BRANCH_NO,REDEEM_POINTS_QTY,REDEEM_VALID_FROM,REDEEM_VALID_TO,REDEEM_STATUS from Hyper1_Retail.REDEEM_ITEM "
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row_number, row_data in enumerate(records):
self.Qtable_redeem.insertRow(row_number)
for column_number, data in enumerate(row_data):
if column_number == 6:
data = util.FN_GET_STATUS_DESC(str(data))
elif column_number == 1:
data = util.FN_GET_COMP_DESC(str(data))
elif column_number == 2:
data = util.FN_GET_BRANCH_DESC(str(data))
self.Qtable_redeem.setItem(row_number, column_number,
QTableWidgetItem(str(data)))
mycursor.close()
except (Error, Warning) as e:
print(e)
self.Qtable_redeem.setEditTriggers(
QtWidgets.QTableWidget.NoEditTriggers)
def FN_GET_REDITEM(self):
try:
if len(self.Qtable_redeem.selectedIndexes()) > 0:
rowNo = self.Qtable_redeem.selectedItems()[0].row()
branch = self.Qtable_redeem.item(rowNo, 2).text()
br_id = util.FN_GET_BRANCH_ID(branch, '1')
self.found = 0
for br in CL_userModule.branch:
if br_id == br[0]:
self.found = 1
bar = self.Qtable_redeem.item(rowNo, 0).text()
company = self.Qtable_redeem.item(rowNo, 1).text()
points = self.Qtable_redeem.item(rowNo, 3).text()
valid_from = self.Qtable_redeem.item(rowNo, 4).text()
valid_to = self.Qtable_redeem.item(rowNo, 5).text()
status = self.Qtable_redeem.item(rowNo, 6).text()
self.Qline_barcode.setText(bar)
self.Qline_points.setText(points)
self.old_points = points
self.old_valid_from = valid_from
self.old_valid_to = valid_to
self.old_status = status
self.Qcombo_group3.hide()
self.Qcombo_group4.hide()
self.CMB_branch.show()
self.CMB_company.show()
# comp = util.FN_GET_COMP_ID(company)
# br =util.FN_GET_BRANCH_DESC(branch)
# print(br)
self.CMB_branch.setCurrentText(branch)
self.CMB_company.setCurrentText(company)
self.Qline_barcode.setEnabled(False)
# self.CMB_company.hide()
# self.CMB_branch.hide()
if status == 'Active':
self.Qradio_active.setChecked(True)
else:
self.Qradio_inactive.setChecked(True)
xto = valid_from.split("-")
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_from.setDate(d)
xto = valid_to.split("-")
d1 = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_to.setDate(d1)
if self.found == 0:
QtWidgets.QMessageBox.warning(
self, "خطأ", "ليس لك صلاحيه على هذا الفرع")
except Exception as err:
print(err)
return False
def FN_MODIFY_REDITEM(self):
try:
if len(self.Qtable_redeem.selectedIndexes()) > 0:
branch = self.CMB_branch.currentText()
comp = self.CMB_company.currentText()
bar = self.Qline_barcode.text().strip()
date_from = self.Qdate_from.date().toString('yyyy-MM-dd')
date_to = self.Qdate_to.date().toString('yyyy-MM-dd')
comp = util.FN_GET_COMP_ID(comp)
branch = util.FN_GET_BRANCH_ID(branch, comp)
points = self.Qline_points.text().strip()
creationDate1 = str(datetime.today().strftime('%Y-%m-%d'))
if self.Qradio_active.isChecked():
status = 1
else:
status = 0
conn = db1.connect()
mycursor = conn.cursor()
if points == '':
QtWidgets.QMessageBox.warning(self, "خطأ",
"برجاء إدخال النقاط ")
elif date_to < date_from:
QtWidgets.QMessageBox.warning(
self, "خطأ",
"تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء"
)
# elif date_from < creationDate1:
# QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ التعديل يجب أن يكون أكبرمن أو يساوي تاريخ اليوم")
else:
self.CMB_branch.hide()
self.CMB_company.hide()
self.Qcombo_group3.show()
self.Qcombo_group4.show()
self.Qline_barcode.setEnabled(True)
self.Qline_barcode.setText('')
self.Qline_points.setText('')
ret2 = CL_validation.FN_validation_int(points)
if ret2 == True:
sql = "update Hyper1_Retail.REDEEM_ITEM " \
"set REDEEM_POINTS_QTY =%s,REDEEM_VALID_FROM =%s , REDEEM_VALID_TO = %s , " \
"REDEEM_STATUS =%s where POS_GTIN = %s and COMPANY_ID = %s and BRANCH_NO = %s "
val = (points, date_from, date_to, status, bar, comp,
branch)
mycursor.execute(sql, val)
mycursor.close()
print(mycursor.rowcount, "record updated.")
QtWidgets.QMessageBox.information(
self, "نجاح", "تم التعديل ")
db1.connectionCommit(conn)
self.FN_REFRESH_DATA_GRID()
self.old_status = util.FN_GET_STATUS_id(
str(self.old_status))
if str(status) != str(self.old_status):
util.FN_INSERT_IN_LOG("REDEEM_ITEM", "status",
status, self.old_status, bar,
comp, branch)
if str(points) != str(self.old_points):
util.FN_INSERT_IN_LOG("REDEEM_ITEM", "points",
points, self.old_points, bar,
comp, branch)
if str(date_from) != str(self.old_valid_from):
util.FN_INSERT_IN_LOG("REDEEM_ITEM", "valid_from",
date_from,
self.old_valid_from, bar,
comp, branch)
if str(date_to) != str(self.old_valid_to):
util.FN_INSERT_IN_LOG("REDEEM_ITEM", "valid_to",
date_to, self.old_valid_to,
bar, comp, branch)
print("in modify red item")
else:
QtWidgets.QMessageBox.warning(
self, "خطأ", "برجاء اختيار السطر المراد تعديله ")
except Exception as err:
print(err)
def FN_LOAD_UPLOAD(self):
try:
filename = self.dirname + '/uploadRedeemItem.ui'
loadUi(filename, self)
self.BTN_browse.clicked.connect(self.FN_OPEN_FILE)
self.BTN_load.clicked.connect(self.FN_SAVE_UPLOAD)
self.BTN_uploadTemp.clicked.connect(self.FN_DISPLAY_TEMP1)
# self.fileName = ''
except (Error, Warning) as e:
print(e)
def FN_OPEN_FILE(self):
options = QFileDialog.Options()
options |= QFileDialog.DontUseNativeDialog
fileName, _ = QFileDialog.getOpenFileName(
self,
"QFileDialog.getOpenFileName()",
"",
" Files (*.xlsx)",
options=options)
self.LE_fileName.setText(fileName)
def FN_UPLOAD_REDITEM(self):
try:
self.window_two = CL_redItem()
self.window_two.FN_LOAD_UPLOAD()
self.window_two.show()
except Exception as err:
print(err)
def FN_DISPLAY_TEMP1(self):
try:
filename = QFileDialog.getSaveFileName(self, "Template File", '',
"(*.xls)")
print(filename)
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, 'الحاله')
# # 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_SAVE_UPLOAD(self):
try:
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 = ''
createdItem = 0
nonCreatedItem = 0
for i in range(sheet.nrows):
bar = int(sheet.cell_value(i, 0))
company = int(sheet.cell_value(i, 1))
branch = sheet.cell_value(i, 2)
points = int(sheet.cell_value(i, 3))
validFrom = sheet.cell_value(i, 4)
validTo = sheet.cell_value(i, 5)
status = int(sheet.cell_value(i, 6))
creationDate1 = str(datetime.today().strftime('%Y-%m-%d'))
if validFrom == '' or validTo == '' or status == '' or company == '' or branch == '' \
or points == '':
nonCreatedItem = nonCreatedItem + 1
QtWidgets.QMessageBox.warning(
self, "خطأ", "برجاء إدخال جميع البيانات")
break
elif validFrom < creationDate1:
QtWidgets.QMessageBox.warning(
self, "خطأ",
"تاريخ الإنشاء يجب أن يكون أكبرمن أو يساوي تاريخ اليوم"
)
break
else:
ret2 = self.FN_CHECK_VALID_BARCCODE(bar)
ret5 = self.FN_CHECK_VALID_BRANCH(branch)
ret6 = self.FN_CHECK_VALID_COMPANY(company)
ret = self.FN_CHECK_EXIST(company, branch, bar)
if ret == False and ret5 == True and ret6 == True and ret2 == True:
conn = db1.connect()
mycursor1 = conn.cursor()
mycursor1.execute(
"select BMC_ID from Hyper1_Retail.POS_ITEM where POS_GTIN ='"
+ str(bar) + "'")
myresult = mycursor1.fetchone()
BMC_ID = myresult[0]
creationDate = str(
datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
sql = "INSERT INTO Hyper1_Retail.REDEEM_ITEM (POS_GTIN,COMPANY_ID," \
"BRANCH_NO,REDEEM_POINTS_QTY,REDEEM_CREATED_ON,REDEEM_CREATED_BY,REDEEM_VALID_FROM" \
",REDEEM_VALID_TO,REDEEM_STATUS,BMC_ID)" \
"values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
val = (bar, company, branch, points, creationDate,
CL_userModule.user_name, validFrom, validTo,
status, str(BMC_ID))
mycursor1.execute(sql, val)
db1.connectionCommit(conn)
createdItem = createdItem + 1
db1.connectionCommit(conn)
mycursor1.close()
#
else:
nonCreatedItem = nonCreatedItem + 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")
if ret2 == False:
j = i + 1
self.msgBox1.setText("Line " + str(j) +
" has invalid Barcode")
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
# 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 items '" +
str(createdItem) +
"' No of non created items '" +
str(nonCreatedItem) + "'")
self.msgBox.show()
self.close()
else:
QtWidgets.QMessageBox.warning(self, "خطأ", "اختر الملف ")
except Exception as err:
print(err)
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 (Error, Warning) as e:
print(e)
def FN_CHECK_EXIST(self, comp, branch, barcode):
try:
conn = db1.connect()
cursor = conn.cursor()
comp = str(comp)
barcode = str(barcode)
sql = "SELECT * FROM Hyper1_Retail.REDEEM_ITEM where POS_GTIN ='" + barcode + "' and COMPANY_ID ='" + comp + "' and BRANCH_NO = '" + branch + "'"
#print(sql)
cursor.execute(sql)
myresult = cursor.fetchone()
if cursor.rowcount > 0:
return True
else:
cursor.close()
return False
except (Error, Warning) as e:
return False
def FN_CHECK_VALID_COMPANY(self, id):
try:
conn = db1.connect()
mycursor11 = conn.cursor()
sql = "SELECT * FROM Hyper1_Retail.COMPANY where COMPANY_ID = '" + str(
id) + "'"
# print(sql)
mycursor11.execute(sql)
myresult = mycursor11.fetchone()
if mycursor11.rowcount > 0:
mycursor11.close()
return True
else:
mycursor11.close()
return False
except (Error, Warning) as e:
print(e)
def FN_CHECK_VALID_BRANCH(self, id):
try:
conn = db1.connect()
mycursor11 = conn.cursor()
sql = "SELECT BRANCH_NO FROM Hyper1_Retail.BRANCH where BRANCH_NO = '" + str(
id) + "'"
# print(sql)
mycursor11.execute(sql)
myresult = mycursor11.fetchone()
if mycursor11.rowcount > 0:
if myresult[0] in CL_userModule.branch[0]:
mycursor11.close()
return True
else:
mycursor11.close()
return False
except (Error, Warning) as e:
print(e)
def FN_GET_BRANCHES(self):
conn = db1.connect()
mycursor = conn.cursor()
self.company = self.CMB_company.currentData()
self.CMB_branch.clear()
sql_select_query = "SELECT BRANCH_DESC_A ,`BRANCH_NO` FROM Hyper1_Retail.BRANCH where BRANCH_STATUS = 1 and COMPANY_ID = '" + self.company + "'"
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row, val in records:
for br in CL_userModule.branch:
if str(val) in br:
self.Qcombo_group4.addItem(row, val)
self.CMB_branch.addItem(row, val)
mycursor.close()
def FN_GET_COMPANIES(self):
conn = db1.connect()
mycursor = conn.cursor()
self.CMB_company.clear()
sql_select_query = "SELECT COMPANY_DESC ,COMPANY_ID FROM Hyper1_Retail.COMPANY where COMPANY_STATUS = 1 "
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row, val in records:
self.Qcombo_group3.addItem(row, val)
self.CMB_company.addItem(row, val)
mycursor.close()
def FN_exit(self):
QApplication.quit()
class CL_report1(QtWidgets.QDialog):
model = QStandardItemModel()
switch_window = QtCore.pyqtSignal()
cond = 1
sql = ""
company_list = []
branch_list = []
conn = db1.connect()
prom_status = ""
prom_CG = ""
sponsor_prom = ""
multi = ""
copNum = ""
list_num = []
field_names = []
def FN_close(self):
#Todo: method for close window
self.close()
def FN_loadData(self, PROM_ID):
#Todo: method for searching about promotions in data base
self.company_list.clear()
self.branch_list.clear()
if len(self.Qcombo_company.currentData()) > 0:
for i in self.Qcombo_company.currentData():
self.company_list.append("'" + i + "'")
else:
self.company_list.append("'" "'")
if len(self.Qcombo_branchEdition.currentData()) > 0:
for i in self.Qcombo_branchEdition.currentData():
self.branch_list.append("'" + i + "'")
try:
self.company_list.clear()
self.branch_list.clear()
if len(self.Qcombo_company.currentData()) > 0 and len(
self.Qcombo_branchEdition.currentData()) > 0:
for i in self.Qcombo_company.currentData():
self.company_list.append("'" + i + "'")
for i in self.Qcombo_branchEdition.currentData():
self.branch_list.append("'" + i + "'")
else:
QtWidgets.QMessageBox.warning(self, "Error",
"برجاء تحديد محددات البحث")
self.company_list.append("'" "'")
self.branch_list.append("'" "'")
if self.QcheckBox_multi.isChecked():
self.multi = "AND COP_MULTI_USE = 1 "
else:
self.multi = ""
if len(self.Qline_promotion.text()) > 0:
self.copNum = "Cop_ID = '" + self.Qline_promotion.text(
) + "' AND "
else:
self.copNum = ""
self.Qtable_promotion.setRowCount(0)
self.conn = db1.connect()
mycursor = self.conn.cursor()
sql = ""
if self.cond == 1:
self.Qbtn_search.setEnabled(False)
self.sql = (
"SELECT COP_ID رقم_الكوبون, COP_DESC وصف_الكوبون, BRANCH_DESC_A اسم_الشركة, "
"COP_DISCOUNT_VAL قيمة_الخصم, COP_DISCOUNT_PERCENT نسبة_الخصم, "
"COP_SERIAL_COUNT عدد_السريال, COP_MULTI_USE متعدد_الاستخدام, COP_MULTI_USE_COUNT "
"عدد_الاستخدامات_المتاحة, COP_CREAED_ON تاريخ_الانشاء, COP_CHANGED_ON تاريخ_التغيير, "
"COP_VALID_FROM متاح_من, COP_VALID_TO متاح_الي, COP_STATUS الحالة FROM COUPON "
"join COUPON_BRANCH on COP_ID = COUPON_ID "
"join BRANCH on COUPON_BRANCH.BRANCH_NO = BRANCH.BRANCH_NO "
"WHERE " + self.copNum + self.prom_status+" COP_VALID_FROM >= '" + self.Qdate_from.dateTime().toString(
'yyyy-MM-dd') + "' and COP_VALID_TO<='"
+ self.Qdate_to.dateTime().toString(
'yyyy-MM-dd') + "'") + self.multi + "AND BRANCH.COMPANY_ID IN (" + ','.join(self.company_list) + ") " \
"and BRANCH.BRANCH_NO IN (" + ','.join(self.branch_list) + ")"
self.runQuery(mycursor)
self.Qbtn_search.setEnabled(True)
self.field_names = [
'رقم_الكوبون', 'وصف_الكوبون', 'اسم_الشركة', 'قيمة_الخصم',
'نسبة_الخصم', 'عدد_السريال', 'متعدد_الاستخدام',
'عدد_الاستخدامات_المتاحة', 'متاح_من', 'متاح_الي', 'الحالة'
]
elif self.cond == 0:
QtWidgets.QMessageBox.warning(self, "Error",
"برجاء تحديد محددات البحث")
mycursor.close()
self.disable()
except:
print(sys.exc_info())
def runQuery(self, mycursor):
print(self.sql)
mycursor.execute(self.sql)
records = mycursor.fetchall()
for row_number, row_data in enumerate(records):
self.Qtable_promotion.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.Qtable_promotion.setItem(row_number, column_number,
QTableWidgetItem(str(data)))
mycursor.close()
def FN_GET_Company(self):
#Todo: method for fills the company combobox
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY")
records = mycursor.fetchall()
print(records)
for row, val in records:
self.Qcombo_company.addItem(row, val)
mycursor.close()
def FN_GET_Branch(self):
#Todo: method for fills the Branch combobox
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT BRANCH_DESC_A ,BRANCH_NO FROM BRANCH")
records = mycursor.fetchall()
for row, val in records:
for bra in CL_userModule.branch:
if val in bra:
self.Qcombo_branchEdition.addItem(row, val)
mycursor.close()
def FN_Check_Active(self):
#Todo: method for change status to active
self.disable()
self.prom_status = "COP_STATUS = '1' AND "
def FN_Check_Expired(self):
#Todo: method for change status to Expired
self.disable()
self.prom_status = "COP_STATUS = '0' AND "
def FN_Check_All(self):
#Todo: method for change status to Expired
self.disable()
self.prom_status = ""
def handleSave(self):
#Todo: method for export reports excel file
frame = pd.read_sql(str(self.sql), self.conn)
df = pd.DataFrame(frame, columns=self.field_names)
# Dump Pandas DataFrame to Excel sheet
writer = pd.ExcelWriter('myreport.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', startrow=2)
writer.save()
import os
os.system('myreport.xlsx')
####################################
def printpreviewDialog(self):
#Todo: method for export reports pdf file
points = ''
pointValue = ''
redeem_type = ''
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
],
]
data = [['']]
title.setData(data)
body()
QtWidgets.QMessageBox.information(self, "Success",
"Report is printed successfully")
import os
os.system('my_file.pdf')
# title = Text()
# title.setName("Invoice")
# title.setFooter(
# " س ت 36108 ملف ضريبي 212/306/5 مأموريه ضرائب الشركات المساهمة رقم التسجيل بضرائب المبيعات 153/846/310 ")
# title.setFont('Scheherazade-Regular.ttf')
# title.setFontsize(10)
# title.setcodeText("15235")
# title.setwaterText("hyperone company")
# title.settelText("1266533")
# title.setbrachText("Entrance 1,EL Sheikh Zayed City")
# title.setCursor(self.Qline_promotion.text)
# title.setQuery(self.query)
# title.setCursor(self.field_names)
# body()
# QtWidgets.QMessageBox.information(self, "Success", "Report is printed successfully")
# import os
# os.system('my_file.pdf')
def disable(self):
#Todo: method for disable or enable table based on count of row
if self.Qtable_promotion.rowCount() == 0:
self.Qbtn_exprot.setEnabled(False)
self.Qbtn_print.setEnabled(False)
else:
self.Qbtn_exprot.setEnabled(True)
self.Qbtn_print.setEnabled(True)
def __init__(self):
#Todo: method for initialization components
super(CL_report1, self).__init__()
try:
cwd = Path.cwd()
mod_path = Path(__file__).parent.parent.parent
dirname = mod_path.__str__() + '/presentation/reports_ui'
filename = dirname + '/Coupon_display.ui'
loadUi(filename, self)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
self.setWindowTitle('تقرير الكوبون')
self.Qcombo_company = CheckableComboBox(self)
self.Qcombo_company.setGeometry(450, 50, 200, 18)
self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_company.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_branchEdition = CheckableComboBox(self)
self.Qcombo_branchEdition.setGeometry(450, 120, 200, 18)
self.Qcombo_branchEdition.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_branchEdition.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qbtn_exit.clicked.connect(self.FN_close)
self.Qbtn_search.clicked.connect(self.FN_loadData)
self.FN_GET_Company()
self.FN_GET_Branch()
self.Qtable_promotion.setEditTriggers(
QtWidgets.QTableWidget.NoEditTriggers)
self.radioBtnPromExpired.clicked.connect(self.FN_Check_Expired)
self.radioBtnPromActive.clicked.connect(self.FN_Check_Active)
self.radioBtnPromAll.clicked.connect(self.FN_Check_All)
self.Qbtn_exprot.clicked.connect(self.handleSave)
self.Qbtn_print.clicked.connect(self.printpreviewDialog)
self.disable()
except:
print(sys.exc_info())
def __init__(self):
#Todo: method for initialization components
super(CL_report, self).__init__()
try:
cwd = Path.cwd()
mod_path = Path(__file__).parent.parent.parent
dirname = mod_path.__str__() + '/presentation/reports_ui'
filename = dirname + '/Promotion_display.ui'
loadUi(filename, self)
self.setWindowTitle('HyperPOS Reporting')
self.Qcombo_company = CheckableComboBox(self)
self.Qcombo_company.setGeometry(242, 20, 179, 18)
self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_company.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_branchEdition = CheckableComboBox(self)
self.Qcombo_branchEdition.setGeometry(242, 45, 179, 18)
self.Qcombo_branchEdition.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_branchEdition.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_cust_group = CheckableComboBox(self)
self.Qcombo_cust_group.setGeometry(242, 75, 179, 18)
self.Qcombo_cust_group.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_cust_group.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_sponsor = CheckableComboBox(self)
self.Qcombo_sponsor.setGeometry(242, 98, 179, 18)
self.Qcombo_sponsor.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_sponsor.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_magazine = CheckableComboBox(self)
self.Qcombo_magazine.setGeometry(242, 145, 179, 18)
self.Qcombo_magazine.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_magazine.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qbtn_exit.clicked.connect(self.FN_close)
self.Qbtn_search.clicked.connect(self.FN_loadData)
self.FN_GET_Company()
self.FN_GET_Branch()
self.FN_GET_CustomerGroup()
self.FN_GET_MAGAZINE()
self.FN_GET_department()
self.FN_GET_promotion_sponser()
self.FN_GET_promotion_type()
self.Qtable_promotion.setEditTriggers(
QtWidgets.QTableWidget.NoEditTriggers)
self.radioBtnPromNum.clicked.connect(self.FN_Checked_Selected2)
self.radioButton_2.clicked.connect(self.FN_Checked_Selected)
self.radioBtnPromNum_2.clicked.connect(self.FN_Checked_Selected3)
self.radioBtnPromExpired.clicked.connect(self.FN_Check_Expired)
self.radioBtnPromStop.clicked.connect(self.FN_Check_Stopped)
self.radioBtnPromActive.clicked.connect(self.FN_Check_Active)
self.radioBtnPromAll.clicked.connect(self.FN_Check_All)
self.Qline_promotion.setEnabled(False)
self.Qcombo_cust_group.setEnabled(False)
self.Qcombo_sponsor.setEnabled(False)
self.Qcombo_department.setEnabled(False)
self.Qcombo_magazine.setEnabled(False)
self.QcheckBox_customer_group.toggled.connect(self.FN_Check_Group)
self.QcheckBox_sponsor_prom.toggled.connect(self.FN_Check_Sponsor)
self.QcheckBox_department.toggled.connect(self.FN_Check_department)
self.QcheckBox_magazine.toggled.connect(self.FN_Check_Magazine)
self.Qbtn_exprot.clicked.connect(self.handleSave)
self.groupBox_2.setEnabled(False)
self.Qcombo_promotion.setEnabled(False)
self.Qline_promotion_2.setEnabled(False)
self.Qbtn_print.clicked.connect(self.printpreviewDialog)
self.Qcombo_department.activated[str].connect(
self.updatestatecombo)
self.Qcombo_section.activated[str].connect(self.updateBMCcombo)
self.updatestatecombo()
self.disable()
except:
print(sys.exc_info())
class CL_report(QtWidgets.QDialog):
# self.Qtime_from.dateTime().toString('hh:mm:ss')
model = QStandardItemModel()
switch_window = QtCore.pyqtSignal()
cond = 0
status = 0
query = ""
customer_group_list = []
sponsor_list = []
magazine_list = []
company_list = []
branch_list = []
conn = db1.connect()
prom_status = ""
prom_CG = ""
sponsor_prom = ""
magazine_prom = ""
bmc = ""
list_num = []
field_names = []
def FN_close(self):
#Todo: method for close window
self.close()
def FN_loadData(self, PROM_ID):
#Todo: method for searching about promotions in data base
try:
self.customer_group_list.clear()
self.sponsor_list.clear()
self.magazine_list.clear()
self.company_list.clear()
self.branch_list.clear()
if len(self.Qcombo_company.currentData()) > 0:
for i in self.Qcombo_company.currentData():
self.company_list.append("'" + i + "'")
else:
self.company_list.append("'" "'")
if len(self.Qcombo_branchEdition.currentData()) > 0:
for i in self.Qcombo_branchEdition.currentData():
self.branch_list.append("'" + i + "'")
else:
self.branch_list.append("'" "'")
if self.QcheckBox_customer_group.isChecked():
if len(self.Qcombo_cust_group.currentData()) > 0:
for i in self.Qcombo_cust_group.currentData():
self.customer_group_list.append("'" + i + "'")
self.prom_CG = "and `PROMOTION_GROUP`.`CG_GROUP_ID` IN (" + ','.join(
self.customer_group_list) + ")"
if self.QcheckBox_sponsor_prom.isChecked():
if len(self.Qcombo_sponsor.currentData()) > 0:
for i in self.Qcombo_sponsor.currentData():
self.sponsor_list.append("'" + i + "'")
self.sponsor_prom = "and `PROMOTION_SPONSOR`.`SPONSOR_ID`IN(" + ','.join(
self.sponsor_list) + ")"
if self.QcheckBox_magazine.isChecked():
if len(self.Qcombo_magazine.currentData()) > 0:
for i in self.Qcombo_magazine.currentData():
self.magazine_list.append("'" + i + "'")
self.magazine_prom = "AND `PROMOTION_HEADER`.`MAGAZINE_ID` IN (" + ','.join(
self.magazine_list) + ")"
if self.QcheckBox_department.isChecked():
self.bmc = "AND `PROMOTION_DETAIL`.`BMC_ID` IN (" + self.Qcombo_classification.currentData(
) + ")"
self.Qtable_promotion.setRowCount(0)
self.conn = db1.connect()
mycursor = self.conn.cursor()
query = ""
if self.cond == 1:
self.query = (
"SELECT `PROMOTION_HEADER`.`PROM_ID`, `PROMOTION_HEADER`.`PROM_TYPE_ID`, `PROMOTION_HEADER`.`PROM_CREATED_BY`, `PROMOTION_HEADER`.`PROM_CREATED_ON`, `PROMOTION_DETAIL`.`PROM_LINE_NO`, `PROMOTION_DETAIL`.`POS_ITEM_NO`,`PROMOTION_DETAIL`.`POS_GTIN`,`PROMOTION_DETAIL`.`BMC_ID`,`PROMOTION_DETAIL`.`PROM_PRICE_BEFORE_DISC`,`PROMOTION_DETAIL`.`PROM_ITEM_SCALE_FLAG`,`PROMOTION_DETAIL`.`PROM_GROUP_SCALE_FLAG`,`PROMOTION_DETAIL`.`PROM_DISCOUNT_FLAG`,`PROMOTION_DETAIL`.`PROM_ITEM_QTY`,`PROMOTION_DETAIL`.`PROM_ITEM_DISC_VAL`,`PROMOTION_DETAIL`.`PROM_ITEM_PRICE`,`PROMOTION_DETAIL`.`PROM_START_DATE`,`PROMOTION_DETAIL`.`PROM_END_DATE`,`PROMOTION_DETAIL`.`PROM_STATUS` FROM `PROMOTION_HEADER` JOIN `PROMOTION_DETAIL` ON `PROMOTION_HEADER`.`PROM_ID`=`PROMOTION_DETAIL`.`PROM_ID`and `PROMOTION_HEADER`.`PROM_ID`= '"
+ self.Qline_promotion.text() + "'")
self.runQuery(mycursor)
elif self.cond == 2:
self.query = (
"SELECT `PROMOTION_HEADER`.`PROM_ID`, `PROMOTION_HEADER`.`PROM_TYPE_ID`, `PROMOTION_HEADER`.`PROM_CREATED_BY`, `PROMOTION_HEADER`.`PROM_CREATED_ON`, `PROMOTION_DETAIL`.`PROM_LINE_NO`, `PROMOTION_DETAIL`.`POS_ITEM_NO`,`PROMOTION_DETAIL`.`POS_GTIN`,`PROMOTION_DETAIL`.`BMC_ID`,`PROMOTION_DETAIL`.`PROM_PRICE_BEFORE_DISC`,`PROMOTION_DETAIL`.`PROM_ITEM_SCALE_FLAG`,`PROMOTION_DETAIL`.`PROM_GROUP_SCALE_FLAG`,`PROMOTION_DETAIL`.`PROM_DISCOUNT_FLAG`,`PROMOTION_DETAIL`.`PROM_ITEM_QTY`,`PROMOTION_DETAIL`.`PROM_ITEM_DISC_VAL`,`PROMOTION_DETAIL`.`PROM_ITEM_PRICE`,`PROMOTION_DETAIL`.`PROM_START_DATE`,`PROMOTION_DETAIL`.`PROM_END_DATE`,`PROMOTION_DETAIL`.`PROM_STATUS` FROM `PROMOTION_HEADER` "
"JOIN `PROM_BRANCH` ON `PROM_BRANCH`.`PROM_ID` = `PROMOTION_HEADER`.`PROM_ID`"
"JOIN `PROMOTION_GROUP` ON `PROMOTION_GROUP`.`PROMID` = `PROMOTION_HEADER`.`PROM_ID`"
"JOIN `PROMOTION_SPONSOR` ON `PROMOTION_SPONSOR`.`PROMID` = `PROMOTION_HEADER`.`PROM_ID`"
"JOIN `PROMOTION_DETAIL` ON `PROMOTION_HEADER`.`PROM_ID`=`PROMOTION_DETAIL`.`PROM_ID`"
"where"
"`PROM_BRANCH`.`COMPANY_ID` IN (" +
','.join(self.company_list) + ")"
"and `PROM_BRANCH`.`BRANCH_NO`IN (" +
','.join(self.branch_list) + ")"
"and `PROMOTION_HEADER`.`PROM_TYPE_ID`= '" +
str(self.Qcombo_promotion.currentData()) + "'" +
self.magazine_prom + " " + self.prom_CG + " " +
self.sponsor_prom + " " + self.bmc +
"and `PROMOTION_DETAIL`.`PROM_START_DATE` >= '" +
self.Qdate_from.dateTime().toString('yyyy-MM-dd') + " " +
"00:00:00" +
"' and `PROMOTION_DETAIL`.`PROM_END_DATE`<='" +
self.Qdate_to.dateTime().toString('yyyy-MM-dd') + " " +
"23:59:00" + "'" + self.prom_status)
self.runQuery(mycursor)
elif self.cond == 3:
self.query = (
"SELECT `PROMOTION_HEADER`.`PROM_ID`, `PROMOTION_HEADER`.`PROM_TYPE_ID`, `PROMOTION_HEADER`.`PROM_CREATED_BY`, `PROMOTION_HEADER`.`PROM_CREATED_ON`, `PROMOTION_DETAIL`.`PROM_LINE_NO`, `PROMOTION_DETAIL`.`POS_ITEM_NO`,`PROMOTION_DETAIL`.`POS_GTIN`,`PROMOTION_DETAIL`.`BMC_ID`,`PROMOTION_DETAIL`.`PROM_PRICE_BEFORE_DISC`,`PROMOTION_DETAIL`.`PROM_ITEM_SCALE_FLAG`,`PROMOTION_DETAIL`.`PROM_GROUP_SCALE_FLAG`,`PROMOTION_DETAIL`.`PROM_DISCOUNT_FLAG`,`PROMOTION_DETAIL`.`PROM_ITEM_QTY`,`PROMOTION_DETAIL`.`PROM_ITEM_DISC_VAL`,`PROMOTION_DETAIL`.`PROM_ITEM_PRICE`,`PROMOTION_DETAIL`.`PROM_START_DATE`,`PROMOTION_DETAIL`.`PROM_END_DATE`,`PROMOTION_DETAIL`.`PROM_STATUS` FROM `PROMOTION_HEADER` "
"JOIN `PROMOTION_DETAIL` ON `PROMOTION_HEADER`.`PROM_ID`=`PROMOTION_DETAIL`.`PROM_ID`"
"JOIN `PROMOTION_GROUP` ON `PROMOTION_GROUP`.`PROMID` = `PROMOTION_HEADER`.`PROM_ID`"
"JOIN `PROMOTION_SPONSOR` ON `PROMOTION_SPONSOR`.`PROMID` = `PROMOTION_HEADER`.`PROM_ID`"
"JOIN `PROM_BRANCH` ON `PROM_BRANCH`.`PROM_ID` = `PROMOTION_HEADER`.`PROM_ID`"
"where"
"`PROM_BRANCH`.`COMPANY_ID` IN (" +
','.join(self.company_list) + ")"
" and `PROM_BRANCH`.`BRANCH_NO`IN (" +
','.join(self.branch_list) + ")"
" " + self.prom_CG + " " + self.sponsor_prom + " " +
self.magazine_prom + " " + self.bmc + " "
"and `PROMOTION_DETAIL`.`POS_GTIN`= '" +
self.Qline_promotion_2.text() + "'" + self.prom_status +
" " + "and `PROMOTION_DETAIL`.`PROM_START_DATE`>='" +
self.Qdate_from.dateTime().toString('yyyy-MM-dd') + " " +
"00:00:00" +
"' and `PROMOTION_DETAIL`.`PROM_END_DATE`<='" +
self.Qdate_to.dateTime().toString('yyyy-MM-dd') + " " +
"23:59:00" + "'")
self.runQuery(mycursor)
elif self.cond == 0:
QtWidgets.QMessageBox.warning(self, "Error",
"برجاء تحديد محددات البحث")
mycursor.close()
self.disable()
except:
print(sys.exc_info())
def runQuery(self, mycursor):
print(self.query)
mycursor.execute(self.query)
records = mycursor.fetchall()
for row_number, row_data in enumerate(records):
self.Qtable_promotion.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.Qtable_promotion.setItem(row_number, column_number,
QTableWidgetItem(str(data)))
mycursor.close()
def FN_GET_Company(self):
#Todo: method for fills the company combobox
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY")
records = mycursor.fetchall()
print(records)
for row, val in records:
self.Qcombo_company.addItem(row, val)
mycursor.close()
def FN_GET_Branch(self):
#Todo: method for fills the Branch combobox
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT BRANCH_DESC_A ,BRANCH_NO FROM BRANCH")
records = mycursor.fetchall()
for row, val in records:
self.Qcombo_branchEdition.addItem(row, val)
mycursor.close()
def FN_GET_CustomerGroup(self):
#Todo: method for fills the Customer Group combobox
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_cust_group.addItem(row, val)
mycursor.close()
def FN_GET_MAGAZINE(self):
#Todo: method for fills the MAGAZINE combobox
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT MAGAZINE_DESC , MAGAZINE_ID FROM MAGAZINE")
records = mycursor.fetchall()
for row, val in records:
self.Qcombo_magazine.addItem(row, val)
mycursor.close()
def FN_GET_department(self):
#Todo: method for fills the department combobox
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:
self.Qcombo_department.addItem(row, val)
mycursor.close()
def FN_GET_promotion_sponser(self):
#Todo: method for fills the promotion_sponser combobox
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT SPONSOR_NAME ,SPONSOR_ID FROM SPONSOR")
records = mycursor.fetchall()
for row, val in records:
self.Qcombo_sponsor.addItem(row, val)
mycursor.close()
def FN_GET_section(self, id):
#Todo: method for fills the section combobox through departmet id
self.Qcombo_section.clear()
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT SECTION_DESC ,SECTION_ID FROM SECTION where DEPARTMENT_ID = '"
+ id + "'")
records = mycursor.fetchall()
print(records)
for row, val in records:
self.Qcombo_section.addItem(row, val)
mycursor.close()
def FN_GET_classification(self, id):
#Todo: method for fills the Subsection combobox through SECTION id
self.Qcombo_classification.clear()
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT BMC_LEVEL4_DESC , BMC_LEVEL4 FROM BMC_LEVEL4 where SECTION_ID ="
+ id + "")
records = mycursor.fetchall()
print(records)
for row, val in records:
self.Qcombo_classification.addItem(row, val)
mycursor.close()
def FN_GET_promotion_type(self):
#Todo: method for fills the section combobox through SECTION id
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT PROMT_NAME_AR , PROMOTION_TYPE_ID FROM PROMOTION_TYPE order by PROMOTION_TYPE_ID*1 "
)
records = mycursor.fetchall()
for row, val in records:
self.Qcombo_promotion.addItem(row, val)
mycursor.close()
def FN_Checked_Selected(self):
#Todo: method for change kind of Search parameters to search by promotion type
self.radioBtnPromNum_2.setChecked(False)
self.radioBtnPromNum.setChecked(False)
self.radioButton_2.setChecked(True)
self.Qline_promotion.setEnabled(False)
self.Qline_promotion.clear()
self.groupBox_2.setEnabled(True)
self.Qcombo_promotion.setEnabled(True)
self.Qline_promotion_2.setEnabled(False)
self.Qline_promotion_2.clear()
self.cond = 2
self.Qtable_promotion.setRowCount(0)
self.disable()
def FN_Checked_Selected2(self):
#Todo: method for change kind of Search parameters to search by promotion number
self.radioBtnPromNum_2.setChecked(False)
self.radioButton_2.setChecked(False)
self.radioBtnPromNum.setChecked(True)
self.Qcombo_promotion.setEnabled(False)
self.Qline_promotion.setEnabled(True)
self.groupBox_2.setEnabled(False)
self.Qline_promotion_2.setEnabled(False)
self.Qline_promotion_2.clear()
self.cond = 1
self.Qtable_promotion.setRowCount(0)
self.disable()
def FN_Checked_Selected3(self):
#Todo: method for change kind of Search parameters to search by gtin
self.radioBtnPromNum.setChecked(False)
self.radioButton_2.setChecked(False)
self.radioBtnPromNum_2.setChecked(True)
self.Qline_promotion.clear()
self.Qline_promotion_2.setEnabled(True)
self.groupBox_2.setEnabled(False)
self.groupBox_2.setEnabled(True)
self.Qcombo_promotion.setEnabled(False)
self.cond = 3
self.Qtable_promotion.setRowCount(0)
self.disable()
def FN_Check_Group(self):
#Todo: method for enable or disable customer group combobox
if self.QcheckBox_customer_group.isChecked():
self.Qcombo_cust_group.setEnabled(True)
self.Qtable_promotion.setRowCount(0)
self.disable()
else:
self.customer_group_list.clear()
self.Qcombo_cust_group.setEnabled(False)
self.prom_CG = ""
def FN_Check_Sponsor(self):
#Todo: method for enable or disable Sponsor combobox
if self.QcheckBox_sponsor_prom.isChecked():
self.Qcombo_sponsor.setEnabled(True)
self.Qtable_promotion.setRowCount(0)
self.disable()
else:
self.Qcombo_sponsor.setEnabled(False)
self.sponsor_prom = ""
def FN_Check_department(self):
#Todo: method for enable or disable department combobox
if self.QcheckBox_department.isChecked():
self.Qcombo_department.setEnabled(True)
self.Qcombo_section.setEnabled(True)
self.Qcombo_classification.setEnabled(True)
self.Qtable_promotion.setRowCount(0)
self.disable()
else:
self.Qcombo_department.setEnabled(False)
self.Qcombo_section.setEnabled(False)
self.Qcombo_classification.setEnabled(False)
self.bmc = ""
def FN_Check_Magazine(self):
#Todo: method for enable or disable Magazine combobox
if self.QcheckBox_magazine.isChecked():
self.Qcombo_magazine.setEnabled(True)
self.Qtable_promotion.setRowCount(0)
self.disable()
else:
self.Qcombo_magazine.setEnabled(False)
self.magazine_prom = ""
def FN_Check_Active(self):
#Todo: method for change status to active
self.status = 1
self.Qtable_promotion.setRowCount(0)
self.disable()
self.prom_status = "and `PROMOTION_DETAIL`.`PROM_STATUS`='1'"
def FN_Check_Stopped(self):
#Todo: method for change status to Stopped
self.status = 0
self.Qtable_promotion.setRowCount(0)
self.disable()
self.prom_status = "and `PROMOTION_DETAIL`.`PROM_STATUS`='0'"
def FN_Check_Expired(self):
#Todo: method for change status to Expired
self.status = 2
self.Qtable_promotion.setRowCount(0)
self.disable()
self.prom_status = "and `PROMOTION_DETAIL`.`PROM_STATUS`='2'"
def FN_Check_All(self):
#Todo: method for change status to All
self.Qtable_promotion.setRowCount(0)
self.disable()
self.prom_status = ""
def handleSave(self):
#Todo: method for export reports excel file
frame = pd.read_sql(str(self.query), self.conn)
df = pd.DataFrame(frame,
columns=[
'PROM_ID', 'PROM_TYPE_ID', 'PROM_CREATED_BY',
'PROM_CREATED_BY', 'PROM_CREATED_ON',
'PROM_LINE_NO'
])
# Dump Pandas DataFrame to Excel sheet
writer = pd.ExcelWriter('myreport.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', startrow=2)
writer.save()
import os
os.system('myreport.xlsx')
####################################
def printpreviewDialog(self):
#Todo: method for export reports pdf file
self.field_names = [
'PROM_ID', 'PROM_TYPE_ID', 'PROM_CREATED_BY', 'PROM_CREATED_BY',
'PROM_CREATED_ON', 'PROM_LINE_NO'
]
title = Text()
title.setName("Invoice")
title.setFooter(
" س ت 36108 ملف ضريبي 212/306/5 مأموريه ضرائب الشركات المساهمة رقم التسجيل بضرائب المبيعات 153/846/310 "
)
title.setFont('Scheherazade-Regular.ttf')
title.setFontsize(10)
title.setcodeText("15235")
title.setwaterText("hyperone company")
title.settelText("1266533")
title.setbrachText("Entrance 1,EL Sheikh Zayed City")
title.setCursor(self.Qline_promotion.text)
title.setQuery(self.query)
title.setCursor(self.field_names)
data = [[
'Nubmber reset:', '248361 5/1/2018',
' ', 'Client Data', ''
],
[
'phone number:',
title.gettelText(), ' ',
'Customer Code',
title.getcodeText()
],
[
'mobile number',
title.gettelText(), ' ',
'Customer Name', 'TEST'
],
['', '', ' ', 'City', 'Giza']]
title.setData(data)
body()
QtWidgets.QMessageBox.information(self, "Success",
"Report is printed successfully")
import os
os.system('my_file.pdf')
def disable(self):
#Todo: method for disable or enable table based on count of row
if self.Qtable_promotion.rowCount() == 0:
self.Qbtn_exprot.setEnabled(False)
self.Qbtn_print.setEnabled(False)
else:
self.Qbtn_exprot.setEnabled(True)
self.Qbtn_print.setEnabled(True)
def updatestatecombo(self):
#Todo: method for do action when select item from department to fill another combobox like Qcombo_section
indx = self.Qcombo_department.currentData()
self.FN_GET_section(indx)
indx = self.Qcombo_section.currentData()
self.Qcombo_classification.clear()
self.FN_GET_classification(indx)
def updateBMCcombo(self):
#Todo: method for do action when select item from Qcombo_section to fill another combobox like Qcombo_classification
indx = self.Qcombo_section.currentData()
self.FN_GET_classification(indx)
def __init__(self):
#Todo: method for initialization components
super(CL_report, self).__init__()
try:
cwd = Path.cwd()
mod_path = Path(__file__).parent.parent.parent
dirname = mod_path.__str__() + '/presentation/reports_ui'
filename = dirname + '/Promotion_display.ui'
loadUi(filename, self)
self.setWindowTitle('HyperPOS Reporting')
self.Qcombo_company = CheckableComboBox(self)
self.Qcombo_company.setGeometry(242, 20, 179, 18)
self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_company.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_branchEdition = CheckableComboBox(self)
self.Qcombo_branchEdition.setGeometry(242, 45, 179, 18)
self.Qcombo_branchEdition.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_branchEdition.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_cust_group = CheckableComboBox(self)
self.Qcombo_cust_group.setGeometry(242, 75, 179, 18)
self.Qcombo_cust_group.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_cust_group.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_sponsor = CheckableComboBox(self)
self.Qcombo_sponsor.setGeometry(242, 98, 179, 18)
self.Qcombo_sponsor.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_sponsor.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_magazine = CheckableComboBox(self)
self.Qcombo_magazine.setGeometry(242, 145, 179, 18)
self.Qcombo_magazine.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_magazine.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qbtn_exit.clicked.connect(self.FN_close)
self.Qbtn_search.clicked.connect(self.FN_loadData)
self.FN_GET_Company()
self.FN_GET_Branch()
self.FN_GET_CustomerGroup()
self.FN_GET_MAGAZINE()
self.FN_GET_department()
self.FN_GET_promotion_sponser()
self.FN_GET_promotion_type()
self.Qtable_promotion.setEditTriggers(
QtWidgets.QTableWidget.NoEditTriggers)
self.radioBtnPromNum.clicked.connect(self.FN_Checked_Selected2)
self.radioButton_2.clicked.connect(self.FN_Checked_Selected)
self.radioBtnPromNum_2.clicked.connect(self.FN_Checked_Selected3)
self.radioBtnPromExpired.clicked.connect(self.FN_Check_Expired)
self.radioBtnPromStop.clicked.connect(self.FN_Check_Stopped)
self.radioBtnPromActive.clicked.connect(self.FN_Check_Active)
self.radioBtnPromAll.clicked.connect(self.FN_Check_All)
self.Qline_promotion.setEnabled(False)
self.Qcombo_cust_group.setEnabled(False)
self.Qcombo_sponsor.setEnabled(False)
self.Qcombo_department.setEnabled(False)
self.Qcombo_magazine.setEnabled(False)
self.QcheckBox_customer_group.toggled.connect(self.FN_Check_Group)
self.QcheckBox_sponsor_prom.toggled.connect(self.FN_Check_Sponsor)
self.QcheckBox_department.toggled.connect(self.FN_Check_department)
self.QcheckBox_magazine.toggled.connect(self.FN_Check_Magazine)
self.Qbtn_exprot.clicked.connect(self.handleSave)
self.groupBox_2.setEnabled(False)
self.Qcombo_promotion.setEnabled(False)
self.Qline_promotion_2.setEnabled(False)
self.Qbtn_print.clicked.connect(self.printpreviewDialog)
self.Qcombo_department.activated[str].connect(
self.updatestatecombo)
self.Qcombo_section.activated[str].connect(self.updateBMCcombo)
self.updatestatecombo()
self.disable()
except:
print(sys.exc_info())
class CL_role(QtWidgets.QDialog):
dirname = ''
def __init__(self):
super(CL_role, 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 to load ui of copy role
def FN_LOAD_COPY(self):
filename = self.dirname + '/copyRole.ui'
loadUi(filename, self)
records = self.FN_GET_ROLES_N()
for row in records:
self.CMB_roleName.addItems([row[0]])
self.CMB_roleName1.addItems([row[0]])
self.BTN_copyRole.clicked.connect(self.FN_COPY_ROLE)
self.CMB_roleName.currentIndexChanged.connect(self.FN_ASSIGN_ID)
self.CMB_roleName1.currentIndexChanged.connect(self.FN_ASSIGN_ID)
self.FN_ASSIGN_ID()
#Todo: method to set text by id
def FN_ASSIGN_ID(self):
self.role1 = self.CMB_roleName.currentText()
self.role2 = self.CMB_roleName1.currentText()
self.LB_roleID.setText(self.FN_GET_ROLEID_N(self.role1))
self.LB_roleID2.setText(self.FN_GET_ROLEID_N(self.role2))
#Todo: method to copy role action
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()
#Todo: method to get all role name and id
def FN_GET_ROLES_N(self):
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT ROLE_NAME ROLE_ID FROM SYS_ROLE order by ROLE_ID asc")
records = mycursor.fetchall()
mycursor.close()
return records
#Todo: method to get role id
def FN_GET_ROLEID_N(self, role):
mycursor = self.conn.cursor()
sql_select_query = "SELECT ROLE_ID FROM SYS_ROLE WHERE ROLE_NAME = %s "
x = (role, )
mycursor.execute(sql_select_query, x)
myresult = mycursor.fetchone()
return myresult[0]
#Todo: method to load ui of assignUserToRole
def FN_ASSIGN(self):
self.CMB_roleName = CheckableComboBox(self)
self.CMB_roleName.setGeometry(100, 85, 179, 18)
self.CMB_roleName.setLayoutDirection(QtCore.Qt.LeftToRight)
self.CMB_roleName.setStyleSheet("background-color: rgb(198, 207, 199)")
filename = self.dirname + '/assignUserToRole.ui'
loadUi(filename, self)
self.BTN_assignRole.clicked.connect(self.FN_ASSIGN_ROLE)
self.CMB_userRoleStatus.addItems(["Active", "Inactive"])
self.FN_GET_USERS()
self.FN_GET_USERID()
self.FN_GET_ROLES()
self.CMB_userName.currentIndexChanged.connect(self.FN_GET_USERID)
#Todo: method to load ui of modify role
def FN_LOAD_MODIFY(self):
filename = self.dirname + '/modifyRole.ui'
loadUi(filename, self)
self.CMB_roleStatus.addItems(["Active", "Inactive"])
self.FN_GET_ROLES1()
self.FN_GET_ROLEID()
self.FN_GET_ROLE()
self.CMB_roleName.currentIndexChanged.connect(self.FN_GET_ROLE)
self.BTN_modifyRole.clicked.connect(self.FN_MODIFY_ROLE)
#Todo: method to load ui of create role
def FN_LOAD_CREATE(self):
filename = self.dirname + '/createRole.ui'
loadUi(filename, self)
self.BTN_createRole.clicked.connect(self.FN_CREATE_ROLE)
self.CMB_roleStatus.addItems(["Active", "Inactive"])
#Todo: method to get user id
def FN_GET_USERID(self):
self.user = self.CMB_userName.currentText()
mycursor = self.conn.cursor()
sql_select_query = "SELECT USER_ID FROM SYS_USER WHERE USER_NAME = %s"
x = (self.user, )
mycursor.execute(sql_select_query, x)
myresult = mycursor.fetchone()
self.LB_userID.setText(myresult[0])
self.FN_GET_ROLES()
#Todo: method to get role id
def FN_GET_ROLEID1(self, roleNm):
if roleNm is not None:
self.role = roleNm
else:
self.role = self.CMB_roleName.currentText()
mycursor = self.conn.cursor()
sql_select_query = "SELECT ROLE_ID FROM SYS_ROLE WHERE ROLE_NAME = %s"
x = (self.role, )
mycursor.execute(sql_select_query, x)
myresult = mycursor.fetchone()
self.LB_roleID.setText(myresult[0])
mycursor.close()
return myresult[0]
#Todo: method to get role id
def FN_GET_ROLEID(self):
self.role = self.CMB_roleName.currentText()
mycursor = self.conn.cursor()
sql_select_query = "SELECT ROLE_ID FROM SYS_ROLE WHERE ROLE_NAME = %s"
x = (self.role, )
mycursor.execute(sql_select_query, x)
myresult = mycursor.fetchone()
self.LB_roleID.setText(myresult[0])
mycursor.close()
return myresult[0]
#Todo: method to get all users name
def FN_GET_USERS(self):
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT USER_NAME FROM SYS_USER where USER_STATUS = 1 order by USER_ID asc"
)
records = mycursor.fetchall()
for row in records:
self.CMB_userName.addItems([row[0]])
mycursor.close()
#Todo: method to get role assigned to user and checked it
def FN_GET_ROLES(self):
self.CMB_roleName.clear()
if self.LB_userID is not None:
selectedRoles = self.FN_SELECT_USER_ROLES()
mycursor = self.conn.cursor()
mycursor.execute("SELECT ROLE_NAME FROM SYS_ROLE order by ROLE_ID asc")
records = mycursor.fetchall()
j = 0
for row in records:
self.CMB_roleName.addItems(row)
for row1 in selectedRoles:
if row[0] == row1[0]:
items = self.CMB_roleName.findText(row[0])
for item in range(items + 1):
self.CMB_roleName.setChecked(j)
j = j + 1
mycursor.close()
#Todo: method to get all role name
def FN_GET_ROLES1(self):
mycursor = self.conn.cursor()
mycursor.execute("SELECT ROLE_NAME FROM SYS_ROLE order by ROLE_ID asc")
records = mycursor.fetchall()
for row in records:
self.CMB_roleName.addItems(row)
mycursor.close()
#Todo: method to get all role assigned to user
def FN_SELECT_USER_ROLES(self):
self.user = self.LB_userID.text()
mycursor = self.conn.cursor()
sql_select_query = "SELECT ROLE_NAME FROM SYS_USER_ROLE INNER JOIN SYS_ROLE on SYS_ROLE.ROLE_ID= SYS_USER_ROLE.ROLE_ID where SYS_USER_ROLE.USER_ID= %s "
x = (self.user, )
mycursor.execute(sql_select_query, x)
records = mycursor.fetchall()
return records
#Todo: method to assign role to user
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_GET_ROLE(self):
self.FN_GET_ROLEID()
self.name = self.CMB_roleName.currentText()
mycursor = self.conn.cursor()
sql_select_query = "select * from SYS_ROLE where ROLE_NAME = %s "
x = (self.name, )
mycursor.execute(sql_select_query, x)
record = mycursor.fetchone()
self.LE_name.setText(record[1])
self.LE_DESC.setText(record[2])
if record[7] == '1':
self.CMB_roleStatus.setCurrentText('Active')
else:
self.CMB_roleStatus.setCurrentText('Inactive')
mycursor.close()
print(mycursor.rowcount, "record retrieved.")
#Todo: method to modify role
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")
#Todo: method to get create role
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")
class CL_CreateCoupon(QtWidgets.QDialog):
dirname = ''
valueType = ""
valueData = ""
serialCount = ""
MultiCount = ""
MultiUse = ""
serialType = 0
def __init__(self):
super(CL_CreateCoupon, self).__init__()
cwd = Path.cwd()
mod_path = Path(__file__).parent.parent.parent
self.dirname = mod_path.__str__() + '/presentation/coupon_ui'
self.conn = db1.connect()
# Todo: method to load ui of create coupon
def FN_LOADUI(self):
filename = self.dirname + '/createCoupon.ui'
loadUi(filename, self)
self.Qcombo_company = CheckableComboBox(self)
self.Qcombo_company.setGeometry(550, 35, 271, 25)
self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_company.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.Qcombo_branch = CheckableComboBox(self)
self.Qcombo_branch.setGeometry(550, 65, 271, 25)
self.Qcombo_branch.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_branch.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.FN_GET_Company()
self.FN_GET_Branch()
self.FN_EnableDiscVal()
self.checkBox_Multi.toggled.connect(self.FN_endableMultiUser)
self.radioButton_Value.clicked.connect(self.FN_EnableDiscVal)
self.radioButton_Percentage.clicked.connect(self.FN_EnablePercentage)
self.BTN_createCoupon.clicked.connect(self.FN_Create)
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.setMinimumDate(d)
self.Qdate_to.setMinimumDate(d)
self.setWindowFlags(QtCore.Qt.WindowCloseButtonHint
| QtCore.Qt.WindowMinimizeButtonHint)
self.LE_desc_5.setEnabled(False)
css_path = Path(__file__).parent.parent.parent
# Apply Style For Design
self.label_num.setStyleSheet(label_num)
self.desc_5.setStyleSheet(desc_5)
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
this_moment = QtCore.QTime.currentTime()
self.Qtime_from.setMinimumTime(this_moment)
# Todo: method to make coupon multi use
def FN_endableMultiUser(self):
if self.checkBox_Multi.isChecked():
self.LE_desc_5.setEnabled(True)
self.LE_desc_4.setEnabled(False)
else:
self.LE_desc_5.setEnabled(False)
self.LE_desc_4.setEnabled(True)
# Todo: method to make coupon use discount value
def FN_EnableDiscVal(self):
self.valueType = "COP_DISCOUNT_VAL"
self.LE_desc_2.setEnabled(True)
self.LE_desc_3.setEnabled(False)
# Todo: method to make coupon use percentage value
def FN_EnablePercentage(self):
self.valueType = "COP_DISCOUNT_PERCENT"
self.LE_desc_3.setEnabled(True)
self.LE_desc_2.setEnabled(False)
# Todo: method for fills the company combobox
def FN_GET_Company(self):
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY")
records = mycursor.fetchall()
print(records)
for row, val in records:
self.Qcombo_company.addItem(row, val)
mycursor.close()
# Todo: method for fills the Branch combobox
def FN_GET_Branch(self):
i = 0
try:
for row, val in CL_userModule.branch:
self.Qcombo_branch.addItem(val, row)
i += 1
except:
print(sys.exc_info())
# Todo: method for create coupon
def FN_Create(self):
try:
self.conn = db1.connect()
self.conn.autocommit = False
mycursor = self.conn.cursor()
self.conn.start_transaction()
print(int(self.Qtime_from.dateTime().toString('hh')))
if len(self.Qcombo_company.currentData()) == 0 or len(
self.Qcombo_branch.currentData()) == 0 or len(
self.LE_desc.text().strip()) == 0 or len(
self.LE_desc_3.text().strip()) == 0 and len(
self.LE_desc_2.text().strip()) == 0:
QtWidgets.QMessageBox.warning(self, "خطا",
"اكمل العناصر الفارغه")
elif self.Qdate_to.dateTime() < self.Qdate_from.dateTime():
QtWidgets.QMessageBox.warning(
self, "Done",
"تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء"
)
elif (self.Qdate_from.date() == self.Qdate_to.date()
) and int(self.Qtime_from.dateTime().toString('hh')) + int(
self.Qtime_from.dateTime().toString('mm')) > int(
self.Qtime_to.dateTime().toString('hh')) + int(
self.Qtime_to.dateTime().toString('mm')):
QtWidgets.QMessageBox.warning(
self, "خطا",
"وقت الانتهاء يجب ان يكون اكبر من او يساوي وقت الانشاء")
else:
if self.checkBox_Multi.isChecked():
self.serialCount = "1"
self.MultiCount = self.LE_desc_5.text()
self.MultiUse = "1"
self.serialType = 1
else:
self.serialCount = self.LE_desc_4.text()
self.MultiCount = "0"
self.MultiUse = "0"
self.serialType = 0
creationDate = str(datetime.today().strftime('%Y-%m-%d'))
if self.radioButton_Percentage.isChecked():
if len(self.LE_desc_3.text().strip()) == 0:
QtWidgets.QMessageBox.warning(self, "خطا",
"اكمل العناصر الفارغه")
else:
self.valueData = self.LE_desc_3.text()
elif self.radioButton_Value.isChecked():
if len(self.LE_desc_2.text().strip()) == 0:
QtWidgets.QMessageBox.warning(self, "خطا",
"اكمل العناصر الفارغه")
else:
self.valueData = self.LE_desc_2.text()
indx = self.LE_desc.text().strip()
sql_select_Query = "select * from COUPON where COP_DESC = %s "
x = (indx, )
mycursor.execute(sql_select_Query, x)
record = mycursor.fetchone()
if mycursor.rowcount > 0:
QtWidgets.QMessageBox.warning(self, "خطا",
"الاسم موجود بالفعل")
else:
sql0 = " LOCK TABLES Hyper1_Retail.COUPON WRITE , " \
" Hyper1_Retail.COUPON_SERIAL WRITE , " \
" Hyper1_Retail.COUPON_BRANCH WRITE "
mycursor.execute(sql0)
id = 0
print("srial" + self.serialCount)
sql = "INSERT INTO COUPON (COP_DESC, " + self.valueType + ", COP_SERIAL_COUNT,COP_MULTI_USE, COP_MULTI_USE_COUNT, COP_CREATED_BY, COP_CREAED_ON, COP_VALID_FROM, COP_TIME_FROM, COP_VALID_TO, COP_TIME_TO, COP_STATUS)" \
" VALUES (%s, %s, %s,%s, %s, %s, %s, %s, %s , %s , %s, %s) "
print(self.Qdate_from.dateTime().toString('yyyy-MM-dd'))
val = (self.LE_desc.text(), self.valueData, self.serialCount,
self.MultiUse, self.MultiCount,
CL_userModule.user_name, creationDate,
self.Qdate_from.dateTime().toString('yyyy-MM-dd'),
str(self.Qtime_from.dateTime().toString('hh:mm')),
self.Qdate_to.dateTime().toString('yyyy-MM-dd'),
str(self.Qtime_to.dateTime().toString('hh:mm')), '0')
mycursor.execute(sql, val)
indx = self.LE_desc.text()
mycursor.execute("SELECT * FROM COUPON Where COP_DESC = '" +
indx + "'")
c = mycursor.fetchone()
id = c[0]
for i in range(int(self.serialCount)):
value = randint(0, 1000000000000)
sql_select_Query = "select * from COUPON_SERIAL where COPS_BARCODE = %s "
x = ("HCOP" + bin(value), )
mycursor.execute(sql_select_Query, x)
record = mycursor.fetchone()
if mycursor.rowcount > 0:
value = value + 1
sql2 = "INSERT INTO COUPON_SERIAL (COUPON_ID,COPS_BARCODE,COPS_CREATED_BY,COPS_SERIAL_type,COPS_CREATED_On,COPS_PRINT_COUNT,COPS_STATUS) VALUES (%s,%s,%s,%s,%s,%s,%s)"
val2 = (id, "HCOP" + bin(value), CL_userModule.user_name,
self.serialType, creationDate, 0, '1')
print(sql2, val2)
mycursor.execute(sql2, val2)
for j in range(len(self.Qcombo_company.currentData())):
for i in range(len(self.Qcombo_branch.currentData())):
sql3 = "INSERT INTO COUPON_BRANCH (COMPANY_ID,BRANCH_NO,COUPON_ID,STATUS) VALUES (%s,%s,%s,%s)"
val3 = (self.Qcombo_company.currentData()[j],
self.Qcombo_branch.currentData()[i], id, '1')
mycursor.execute(sql3, val3)
sql00 = " UNLOCK tables "
mycursor.execute(sql00)
db1.connectionCommit(self.conn)
mycursor.close()
QtWidgets.QMessageBox.warning(self, "Done",
"رقم الكوبون هو " + str(id))
self.label_num.setText(str(id))
except mysql.connector.Error as error:
print("Failed to update record to database rollback: {}".format(
error))
# reverting changes because of exception
self.conn.rollback()
finally:
# closing database connection.
if self.conn.is_connected():
mycursor.close()
self.conn.close()
print("connection is closed")
def FN_LOAD_DISPLAY(self):
filename = self.dirname + '/createModifyLoyalityProg.ui'
loadUi(filename, self)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
conn = db1.connect()
mycursor = conn.cursor()
valid_from = str(datetime.today().strftime('%Y-%m-%d'))
xto = valid_from.split("-")
print(xto)
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_from.setDate(d)
self.Qradio_barcode.clicked.connect(self.onClicked)
self.Qradio_bmc.clicked.connect(self.onClicked)
self.Qradio_bmc.setChecked(True)
self.Qradio_active.setChecked(True)
self.Qcombo_group2 = CheckableComboBox(self)
self.Qcombo_group2.setGeometry(385, 64, 179, 18)
self.Qcombo_group2.setStyleSheet("background-color: rgb(198, 207, 199)")
self.Qcombo_group3 = CheckableComboBox(self)
self.Qcombo_group3.setGeometry(385, 25, 179, 18)
self.Qcombo_group3.setStyleSheet("background-color: rgb(198, 207, 199)")
self.Qcombo_group4 = CheckableComboBox(self)
self.Qcombo_group4.setGeometry(385, 45, 179, 18)
self.Qcombo_group4.setStyleSheet("background-color: rgb(198, 207, 199)")
self.Qcombo_group5 = CheckableComboBox(self)
self.Qcombo_group5.setGeometry(385, 85, 179, 18)
self.Qcombo_group5.setStyleSheet("background-color: rgb(198, 207, 199)")
self.Qcombo_group6 = CheckableComboBox(self)
self.Qcombo_group6.setGeometry(170, 190, 120, 18)
self.Qcombo_group6.setStyleSheet("background-color: rgb(198, 207, 199)")
self.CMB_custGroup.hide()
self.CMB_branch.hide()
self.CMB_company.hide()
self.CMB_loyalityType.hide()
self.CMB_level4.hide()
self.FN_GET_COMPANIES()
self.FN_GET_BRANCHES()
self.FN_GET_CUSTGP()
self.FN_GET_CUSTTP()
#
self.FN_GET_DEPARTMENTS()
#self.FN_GET_SECTIONS()
#self.FN_GET_BMCLEVEL4()
self.CMB_department.activated.connect( self.FN_GET_SECTIONS )
self.CMB_section.activated.connect(self.FN_GET_BMCLEVEL4)
#self.Qbtn_add.clicked.connect(self.FN_ADD_LOYPROG)
self.Qbtn_search.clicked.connect(self.FN_SEARCH_LOYPROG)
self.Qbtn_exit.clicked.connect(self.FN_exit)
# # #check authorization
#print(CL_userModule.myList)
for row_number, row_data in enumerate( CL_userModule.myList ):
if row_data[1] =='Display_Loyality':
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] == 'create' :
self.Qbtn_create.setEnabled(True)
self.Qbtn_create.clicked.connect(self.FN_CREATE_LOYPROG)
elif result[0] == 'modify':
self.Qbtn_modify.setEnabled(True)
self.Qbtn_modify.clicked.connect(self.FN_MODIFY_LOYPROG)
elif result[0] == 'upload':
try:
self.Qbtn_upload.setEnabled(True)
self.Qbtn_upload.clicked.connect(self.FN_UPLOAD_LOYPROG)
except Exception as err:
print(err)
elif result[0] == 'activate':
try:
self.Qbtn_activate.setEnabled(True)
self.Qbtn_activate.clicked.connect(self.FN_ACTIVATE_LOYPROG)
except Exception as err:
print(err)
class CL_loyProg(QtWidgets.QDialog):
switch_window = QtCore.pyqtSignal()
dirname = ''
def __init__(self):
super(CL_loyProg, self).__init__()
cwd = Path.cwd()
mod_path = Path( __file__ ).parent.parent.parent
self.dirname = mod_path.__str__() + '/presentation/loyalty_ui'
self.creationDate1 = str(datetime.today().strftime('%Y-%m-%d'))
#self.p=pp
def onClicked(self):
#radioButton = self.sender()
#print(radioButton.name)
if self.Qradio_barcode.isChecked ():
self.Qline_barcode.setEnabled(True)
self.CMB_department.setEnabled(False)
self.CMB_section.setEnabled(False)
self.CMB_level4.setEnabled(False)
elif self.Qradio_bmc.isChecked ():
self.Qline_barcode.setEnabled(False)
self.CMB_department.setEnabled(True)
self.CMB_section.setEnabled(True)
self.CMB_level4.setEnabled(True)
def FN_LOAD_DISPLAY(self):
filename = self.dirname + '/createModifyLoyalityProg.ui'
loadUi(filename, self)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
conn = db1.connect()
mycursor = conn.cursor()
valid_from = str(datetime.today().strftime('%Y-%m-%d'))
xto = valid_from.split("-")
print(xto)
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_from.setDate(d)
self.Qradio_barcode.clicked.connect(self.onClicked)
self.Qradio_bmc.clicked.connect(self.onClicked)
self.Qradio_bmc.setChecked(True)
self.Qradio_active.setChecked(True)
self.Qcombo_group2 = CheckableComboBox(self)
self.Qcombo_group2.setGeometry(385, 64, 179, 18)
self.Qcombo_group2.setStyleSheet("background-color: rgb(198, 207, 199)")
self.Qcombo_group3 = CheckableComboBox(self)
self.Qcombo_group3.setGeometry(385, 25, 179, 18)
self.Qcombo_group3.setStyleSheet("background-color: rgb(198, 207, 199)")
self.Qcombo_group4 = CheckableComboBox(self)
self.Qcombo_group4.setGeometry(385, 45, 179, 18)
self.Qcombo_group4.setStyleSheet("background-color: rgb(198, 207, 199)")
self.Qcombo_group5 = CheckableComboBox(self)
self.Qcombo_group5.setGeometry(385, 85, 179, 18)
self.Qcombo_group5.setStyleSheet("background-color: rgb(198, 207, 199)")
self.Qcombo_group6 = CheckableComboBox(self)
self.Qcombo_group6.setGeometry(170, 190, 120, 18)
self.Qcombo_group6.setStyleSheet("background-color: rgb(198, 207, 199)")
self.CMB_custGroup.hide()
self.CMB_branch.hide()
self.CMB_company.hide()
self.CMB_loyalityType.hide()
self.CMB_level4.hide()
self.FN_GET_COMPANIES()
self.FN_GET_BRANCHES()
self.FN_GET_CUSTGP()
self.FN_GET_CUSTTP()
#
self.FN_GET_DEPARTMENTS()
#self.FN_GET_SECTIONS()
#self.FN_GET_BMCLEVEL4()
self.CMB_department.activated.connect( self.FN_GET_SECTIONS )
self.CMB_section.activated.connect(self.FN_GET_BMCLEVEL4)
#self.Qbtn_add.clicked.connect(self.FN_ADD_LOYPROG)
self.Qbtn_search.clicked.connect(self.FN_SEARCH_LOYPROG)
self.Qbtn_exit.clicked.connect(self.FN_exit)
# # #check authorization
#print(CL_userModule.myList)
for row_number, row_data in enumerate( CL_userModule.myList ):
if row_data[1] =='Display_Loyality':
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] == 'create' :
self.Qbtn_create.setEnabled(True)
self.Qbtn_create.clicked.connect(self.FN_CREATE_LOYPROG)
elif result[0] == 'modify':
self.Qbtn_modify.setEnabled(True)
self.Qbtn_modify.clicked.connect(self.FN_MODIFY_LOYPROG)
elif result[0] == 'upload':
try:
self.Qbtn_upload.setEnabled(True)
self.Qbtn_upload.clicked.connect(self.FN_UPLOAD_LOYPROG)
except Exception as err:
print(err)
elif result[0] == 'activate':
try:
self.Qbtn_activate.setEnabled(True)
self.Qbtn_activate.clicked.connect(self.FN_ACTIVATE_LOYPROG)
except Exception as err:
print(err)
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_DISPLAY_TEMP(self):
try:
filename = QFileDialog.getSaveFileName(self, "Template File", '', "(*.xls)")
print(filename)
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, 'BMC')
sheet.write(0, 11, 'مبلغ الشراء')
sheet.write(0, 12, 'النقاط المستحقه')
# # 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_UPLOAD(self):
try:
filename = self.dirname + '/uploadLoyalityProg.ui'
loadUi(filename, self)
self.BTN_browse.clicked.connect(self.FN_OPEN_FILE)
self.BTN_load.clicked.connect(self.FN_SAVE_UPLOAD)
self.BTN_uploadTemp.clicked.connect(self.FN_DISPLAY_TEMP)
#self.fileName = ''
self.setFixedWidth(576)
self.setFixedHeight(178)
except (Error, Warning) as e:
print(e)
def FN_GET_BRANCHES(self):
conn = db1.connect()
mycursor = conn.cursor()
self.company = self.CMB_company.currentData()
self.CMB_branch.clear()
sql_select_query = "SELECT BRANCH_DESC_A ,`BRANCH_NO` FROM Hyper1_Retail.BRANCH where BRANCH_STATUS = 1 and COMPANY_ID = '"+self.company+"'"
mycursor.execute( sql_select_query )
records = mycursor.fetchall()
for row, val in records:
for br in CL_userModule.branch:
if str(val) in br:
self.Qcombo_group4.addItem(row, val)
self.CMB_branch.addItem(row, val)
mycursor.close()
def FN_GET_COMPANIES(self):
conn = db1.connect()
mycursor = conn.cursor()
self.CMB_company.clear()
sql_select_query = "SELECT COMPANY_DESC ,COMPANY_ID FROM Hyper1_Retail.COMPANY where COMPANY_STATUS = 1 "
mycursor.execute( sql_select_query )
records = mycursor.fetchall()
for row, val in records:
self.Qcombo_group3.addItem(row, val)
self.CMB_company.addItem(row, val)
mycursor.close()
def FN_GET_DEPARTMENTS(self):
conn = db1.connect()
mycursor = conn.cursor()
self.CMB_department.clear()
sql_select_query = "SELECT DEPARTMENT_DESC FROM Hyper1_Retail.DEPARTMENT where DEPARTMENT_STATUS = 1 "
mycursor.execute( sql_select_query )
records = mycursor.fetchall()
for row in records:
self.CMB_department.addItems( [row[0]] )
mycursor.close()
self.FN_GET_SECTIONS()
def FN_GET_SECTIONS(self):
conn = db1.connect()
mycursor = conn.cursor()
self.CMB_section.clear()
dept = self.CMB_department.currentText()
sql_select_query = "SELECT SECTION_DESC ,SECTION_ID FROM Hyper1_Retail.SECTION s inner join Hyper1_Retail.DEPARTMENT d ON " \
"d.`DEPARTMENT_ID` = s.`DEPARTMENT_ID`" \
"where SECTION_STATUS = 1 and `DEPARTMENT_DESC`= '"+dept+"'"
mycursor.execute( sql_select_query )
records = mycursor.fetchall()
if mycursor.rowcount >0 :
for row, val in records:
for sec in CL_userModule.section:
if str(val) in sec:
self.CMB_section.addItem(row, val)
self.FN_GET_BMCLEVEL4()
mycursor.close()
def FN_GET_BMCLEVEL4(self):
conn = db1.connect()
mycursor = conn.cursor()
self.Qcombo_group6.clear()
sec = self.CMB_section.currentData()
if sec != None:
sql_select_query = "SELECT BMC_LEVEL4_DESC ,BMC_LEVEL4 FROM Hyper1_Retail.BMC_LEVEL4 b where BMC_LEVEL4_STATUS = 1 " \
"and SECTION_ID= '"+sec+"'"
#print(sql_ select_query)
mycursor.execute( sql_select_query )
records = mycursor.fetchall()
self.Qcombo_group6.addItem("All")
for row, val in records:
self.Qcombo_group6.addItem(row, val)
mycursor.close()
self.Qcombo_group6.setChecked(0)
def FN_GET_CUSTGP(self):
#print("pt11")
conn = db1.connect()
mycursor = conn.cursor()
mycursor.execute( "SELECT CG_DESC ,CG_GROUP_ID FROM Hyper1_Retail.CUSTOMER_GROUP order by CG_GROUP_ID asc" )
records = mycursor.fetchall()
mycursor.close()
#print("pt12")
for row, val in records:
self.CMB_custGroup.addItem(row, val)
self.Qcombo_group2.addItem(row, val)
#print(self.Qcombo_group2.currentData())
def FN_GET_CUSTTP(self):
conn = db1.connect()
mycursor = conn.cursor()
mycursor.execute( "SELECT LOYCT_DESC ,LOYCT_TYPE_ID FROM Hyper1_Retail.LOYALITY_CUSTOMER_TYPE order by LOYCT_TYPE_ID asc" )
records = mycursor.fetchall()
mycursor.close()
for row, val in records:
self.CMB_loyalityType.addItem(row, val)
self.Qcombo_group5.addItem(row, val)
def FN_GET_CUSTTP_ID(self,desc):
conn = db1.connect()
mycursor = conn.cursor()
mycursor.execute( "SELECT LOYCT_TYPE_ID FROM Hyper1_Retail.LOYALITY_CUSTOMER_TYPE where LOYCT_DESC = '"+desc+"'" )
records = mycursor.fetchone()
mycursor.close()
return records[0]
def FN_CHECK_VALID_BMC(self,id):
try:
conn = db1.connect()
mycursor11 = conn.cursor()
sql="SELECT * FROM Hyper1_Retail.BMC_LEVEL4 where BMC_LEVEL4 = '" + str(id) + "'"
#print(sql)
mycursor11.execute(sql)
myresult = mycursor11.fetchone()
if mycursor11.rowcount>0:
mycursor11.close()
return True
else :
mycursor11.close()
return False
except (Error, Warning) as e:
print(e)
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 (Error, Warning) as e:
print(e)
def FN_CHECK_VALID_CUSTGP(self,id):
try:
conn = db1.connect()
mycursor11 = conn.cursor()
sql = "SELECT * FROM Hyper1_Retail.CUSTOMER_GROUP where CG_GROUP_ID ='" + str(id) + "'"
mycursor11.execute(sql)
myresult = mycursor11.fetchone()
if mycursor11.rowcount > 0:
mycursor11.close()
return True
else:
mycursor11.close()
return False
except (Error, Warning) as e:
print(e)
def FN_CHECK_VALID_CUSTTP(self,id):
try:
conn = db1.connect()
mycursor11 = conn.cursor()
sql = "SELECT * FROM Hyper1_Retail.LOYALITY_CUSTOMER_TYPE where LOYCT_TYPE_ID ='" + str(id) + "'"
mycursor11.execute(sql)
myresult = mycursor11.fetchone()
if mycursor11.rowcount > 0:
mycursor11.close()
return True
else:
mycursor11.close()
return False
except (Error, Warning) as e:
print(e)
def FN_CHECK_VALID_COMPANY(self,id):
try:
conn = db1.connect()
mycursor11 = conn.cursor()
sql = "SELECT * FROM Hyper1_Retail.COMPANY where COMPANY_ID = '" + str(id) + "'"
#print(sql)
mycursor11.execute(sql)
myresult = mycursor11.fetchone()
if mycursor11.rowcount > 0:
mycursor11.close()
return True
else:
mycursor11.close()
return False
except (Error, Warning) as e:
print(e)
def FN_CHECK_VALID_BRANCH(self,id):
try:
conn = db1.connect()
mycursor11 = conn.cursor()
sql = "SELECT * FROM Hyper1_Retail.BRANCH where BRANCH_NO = '" + str(id) + "'"
#print(sql)
mycursor11.execute(sql)
myresult = mycursor11.fetchone()
if mycursor11.rowcount > 0:
mycursor11.close()
return True
else:
mycursor11.close()
return False
except (Error, Warning) as e:
print(e)
# # endregion
def FN_CHECK_EXIST(self,comp,branch,ctgp,cttp,level4,barcode):
conn = db1.connect()
cursor = conn.cursor()
comp = str(comp)
level4= str(level4)
if barcode =='' :
sql ="SELECT * FROM Hyper1_Retail.LOYALITY_PROGRAM where LOY_STATUS = 1 and COPMAPNY_ID ='"+str(comp)+"' and BRANCH_NO = '"+branch+"' and CG_GROUP_ID='"+str(ctgp)+"' and LOYCT_TYPE_ID ='"+str(cttp)+"' and BMC_ID = '"+str(level4)+"' "
#print(sql)
cursor.execute(sql)
else :
sql = "SELECT * FROM Hyper1_Retail.LOYALITY_PROGRAM where LOY_STATUS = 1 and COPMAPNY_ID ='" + str(comp) + "' and BRANCH_NO = '" + branch + "' and CG_GROUP_ID='" + str(ctgp) + "' and LOYCT_TYPE_ID ='" + str(cttp) + "' and POS_GTIN = '" + str(barcode) + "'"
#print(sql)
cursor.execute(sql)
myresult = cursor.fetchone()
try:
if cursor.rowcount > 0:
#cursor.close()
return True
else:
cursor.close()
return False
except (Error, Warning) as e:
return False
def FN_GET_CUSTGP_DESC(self, id):
conn = db1.connect()
mycursor = conn.cursor()
mycursor.execute("SELECT CG_DESC FROM Hyper1_Retail.CUSTOMER_GROUP where CG_GROUP_ID = '" + id + "'")
myresult = mycursor.fetchone()
return myresult[0]
def FN_GET_BMC_DESC(self, id):
conn = db1.connect()
mycursor = conn.cursor()
mycursor.execute("SELECT BMC_LEVEL4_DESC FROM Hyper1_Retail.BMC_LEVEL4 where BMC_LEVEL4= '" + id + "'")
myresult = mycursor.fetchone()
return myresult[0]
def FN_CLEAR_FEILDS(self):
self.label_ID.setText("")
self.Qline_name.setText("")
self.Qtext_desc.setText("")
self.Qline_purchAmount.setValue(0)
self.Qline_points.setValue(0)
def FN_SEARCH_LOYPROG(self):
try:
self.FN_CLEAR_FEILDS()
for i in reversed(range(self.Qtable_loyality.rowCount())):
self.Qtable_loyality.removeRow(i)
sec = self.CMB_section.currentText()
BMC_LEVEL4s = self.Qcombo_group6.currentData()
#self.mycursor = self.conn.cursor()
cust_gps = self.Qcombo_group2.currentData()
cust_tps = self.Qcombo_group5.currentData()
branchs = self.Qcombo_group4.currentData()
companies = self.Qcombo_group3.currentData()
date_from = self.Qdate_from.dateTime().toString('yyyy-MM-dd')
date_to = self.Qdate_to.dateTime().toString('yyyy-MM-dd')
conn = db1.connect()
mycursor = conn.cursor()
whereClause = ""
if self.Qradio_active.isChecked():
whereClause = whereClause + 'LOY_STATUS = 1 '
elif self.Qradio_inactive.isChecked():
whereClause = whereClause + 'LOY_STATUS = 0 '
whereClause = whereClause + " and LOY_VALID_FROM >= '" + date_from + "' and LOY_VALID_TO <= '" + date_to + "' "
if self.Qradio_barcode.isChecked():
barcode = self.Qline_barcode.text().strip()
whereClause = whereClause + " and POS_GTIN ='" + barcode + "'"
elif self.Qradio_bmc.isChecked():
# get bmc_level4
BMC_LEVEL4_list = []
if len(BMC_LEVEL4s) == 0:
BMC_LEVEL4s[0] = 'All'
if BMC_LEVEL4s[0] == 'All':
mycursor.execute(
"SELECT BMC_LEVEL4 FROM Hyper1_Retail.BMC_LEVEL4 b inner join Hyper1_Retail.SECTION s ON " \
"b.`SECTION_ID` = s.`SECTION_ID`" \
"where BMC_LEVEL4_STATUS = 1 and `SECTION_DESC`= '" + sec + "'")
records =mycursor.fetchall()
for row in records:
BMC_LEVEL4_list.append(row[0])
else:
# for BMC_LEVEL4 in BMC_LEVEL4s:
# sql = "SELECT BMC_LEVEL4 FROM Hyper1_Retail.BMC_LEVEL4 where BMC_LEVEL4_DESC = '" + BMC_LEVEL4 + "'"
# mycursor.execute(sql)
# myresult = mycursor.fetchone()
# BMC_LEVEL4_list.append(myresult[0])
BMC_LEVEL4_list = BMC_LEVEL4s
if len(BMC_LEVEL4_list) > 0:
if len(BMC_LEVEL4_list) == 1 :
if BMC_LEVEL4_list == 'ALL' :
BMC_LEVEL4_list_tuple = tuple(BMC_LEVEL4_list)
whereClause = whereClause + " and BMC_ID in {}".format(BMC_LEVEL4_list_tuple)
else:
whereClause = whereClause + " and BMC_ID = '" + BMC_LEVEL4_list[0] + "'"
else:
BMC_LEVEL4_list_tuple = tuple(BMC_LEVEL4_list)
whereClause = whereClause + " and BMC_ID in {}".format(BMC_LEVEL4_list_tuple)
# get COMPANY
company_list = companies
if len(company_list) > 0:
if len(company_list) == 1:
whereClause = whereClause + " and COPMAPNY_ID = '" + company_list[0] + "'"
else:
company_list_tuple = tuple(company_list)
whereClause = whereClause + " and COPMAPNY_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)
# get customer gp id
cust_gp_list = cust_gps
if len(cust_gp_list) > 0:
if len(cust_gp_list) == 1:
whereClause = whereClause + " and CG_GROUP_ID ='" + cust_gp_list[0] + "'"
else:
cust_gp_list_tuple = tuple(cust_gp_list)
whereClause = whereClause + " and CG_GROUP_ID in {}".format(cust_gp_list_tuple)
# get customer type
cust_tp_list = cust_tps
if len(cust_tp_list) > 0:
if len(cust_tp_list) == 1:
whereClause = whereClause + " and LOYCT_TYPE_ID = '" + cust_tp_list[0] + "'"
else:
cust_tp_list_tuple = tuple(cust_tp_list)
whereClause = whereClause + " and LOYCT_TYPE_ID in {}".format(cust_tp_list_tuple)
sql_select_query = "select LOY_PROGRAM_ID, LOY_NAME ,LOY_DESC,LOY_VALID_FROM,LOY_VALID_TO, LOY_STATUS,COPMAPNY_ID,BRANCH_NO,CG_GROUP_ID,LOYCT_TYPE_ID,POS_GTIN,BMC_ID,LOY_VALUE,LOY_POINTS from Hyper1_Retail.LOYALITY_PROGRAM where " + whereClause
print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row_number, row_data in enumerate(records):
self.Qtable_loyality.insertRow(row_number)
for column_number, data in enumerate(row_data):
if column_number == 5:
data = util.FN_GET_STATUS_DESC(str(data))
elif column_number == 6:
data = util.FN_GET_COMP_DESC(str(data))
elif column_number == 7:
data = util.FN_GET_BRANCH_DESC(str(data))
elif column_number == 8:
data = self.FN_GET_CUSTGP_DESC(str(data))
elif column_number == 9:
data = util.FN_GET_CUSTTP_DESC(str(data))
elif column_number == 11:
data = self.FN_GET_BMC_DESC(str(data))
self.Qtable_loyality.setItem(row_number, column_number, QTableWidgetItem(str(data)))
self.Qtable_loyality.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
self.Qtable_loyality.doubleClicked.connect(self.FN_GET_LOYPROG)
mycursor.close()
except (Error, Warning) as e:
print(e)
def FN_GET_LOYPROG(self):
try:
if len(self.Qtable_loyality.selectedIndexes()) > 0:
rowNo = self.Qtable_loyality.selectedItems()[0].row()
id =self.Qtable_loyality.item(rowNo, 0).text()
name = self.Qtable_loyality.item(rowNo, 1).text()
desc = self.Qtable_loyality.item(rowNo, 2).text()
status = self.Qtable_loyality.item(rowNo, 5).text()
amount = self.Qtable_loyality.item(rowNo, 12).text()
points = self.Qtable_loyality.item(rowNo, 13).text()
valid_from =self.Qtable_loyality.item(rowNo, 3).text()
valid_to= self.Qtable_loyality.item(rowNo, 4).text()
self.old_name=name
self.old_status=util.FN_GET_STATUS_id(status)
self.old_amount=amount
self.old_points=points
self.old_valid_from = valid_from
self.old_valid_to = valid_to
self.Qline_name.setText(name)
self.label_ID.setText(id)
self.Qtext_desc.setText(desc)
self.Qline_purchAmount.setValue(float(amount))
self.Qline_points.setValue(int(points))
if status == 'Active' :
self.Qradio_active.setChecked(True)
else:
self.Qradio_inactive.setChecked(True)
xto = valid_from.split("-")
print(xto)
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_from.setDate(d)
xto = valid_to.split("-")
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_to.setDate(d)
except Exception as err:
print(err)
def FN_exit(self):
QApplication.quit()
def FN_CREATE_LOYPROG(self):
ids=[]
cust_gps = self.Qcombo_group2.currentData()
cust_tps = self.Qcombo_group5.currentData()
branchs = self.Qcombo_group4.currentData()
companies = self.Qcombo_group3.currentData()
self.name = self.Qline_name.text().strip()
self.desc = self.Qtext_desc.toPlainText().strip()
self.date_from = self.Qdate_from.date().toString('yyyy-MM-dd')
self.date_to = self.Qdate_to.date().toString('yyyy-MM-dd')
BMC_LEVEL4s = self.Qcombo_group6.currentData()
self.section = self.CMB_section.currentData()
self.level4 = self.CMB_level4.currentData()
self.barcode = self.Qline_barcode.text().strip()
self.purchAmount = self.Qline_purchAmount.text().strip()
self.points = self.Qline_points.text().strip()
conn = db1.connect()
# mycursor = self.conn.cursor(buffered=True)
self.mycursor = conn.cursor()
creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
company_list = companies
branch_list = branchs
cust_gp_list = cust_gps
cust_tp_list = cust_tps
if self.Qradio_barcode.isChecked():
self.barcode = self.Qline_barcode.text().strip()
elif self.Qradio_bmc.isChecked():
# get BMC level
BMC_LEVEL4_list = []
if len(self.Qcombo_group6.currentData()) > 0:
if BMC_LEVEL4s[0] =='All' :
conn = db1.connect()
mycursor = conn.cursor()
sql_select_query = "SELECT BMC_LEVEL4_DESC ,BMC_LEVEL4 FROM Hyper1_Retail.BMC_LEVEL4 b where BMC_LEVEL4_STATUS = 1 " \
"and SECTION_ID= '" + self.section + "'"
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row in records:
BMC_LEVEL4_list.append(row[1])
mycursor.close()
else:
BMC_LEVEL4_list= BMC_LEVEL4s
if len(self.Qcombo_group2.currentData()) == 0 or len(self.Qcombo_group3.currentData()) == 0 or len(
self.Qcombo_group4.currentData()) == 0 or len(self.Qcombo_group6.currentData()) == 0 or len(
self.Qcombo_group5.currentData()) == 0 or self.name == '' or self.desc == '' or float(self.purchAmount) == 0 or self.points == '0' or self.date_from == '' or self.date_to == '' \
:
QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء إدخال جميع البيانات")
elif self.date_to < self.date_from :
QtWidgets.QMessageBox.warning(self, "خطأ",
"تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء")
elif self.date_from < self.creationDate1:
QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ الإنشاء يجب أن يكون أكبرمن أو يساوي تاريخ اليوم")
else:
for com in company_list:
for br in branch_list:
for ctgp in cust_gp_list:
for cttp in cust_tp_list:
for BMC_LEVEL4 in BMC_LEVEL4_list :
ret = self.FN_CHECK_EXIST(com, br, ctgp, cttp, BMC_LEVEL4, self.barcode)
if ret == False:
try:
print("pt1")
# self.mycursor.close()
self.mycursor1 = conn.cursor()
self.mycursor1.execute(
"SELECT max(cast(LOY_PROGRAM_ID AS UNSIGNED)) FROM Hyper1_Retail.LOYALITY_PROGRAM")
myresult = self.mycursor1.fetchone()
if myresult[0] == None:
self.id = "1"
else:
self.id = int(myresult[0]) + 1
ids.append(self.id)
# mycursor = self.conn.cursor()
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)"
val = (self.id, com, br, ctgp, BMC_LEVEL4, self.barcode, self.name, self.desc,
creationDate, CL_userModule.user_name, self.date_from, self.date_to,
self.purchAmount, self.points, cttp, '0')
self.mycursor1.execute(sql, val)
db1.connectionCommit(conn)
self.mycursor1.close()
except (Error, Warning) as e:
print(e)
else:
QtWidgets.QMessageBox.warning(self, "Error", "المدخلات بالفعل نوجوده")
continue
self.FN_REFRESH_DATA_GRID(ids)
# self.close()
def FN_REFRESH_DATA_GRID(self,ids):
try:
for i in reversed(range(self.Qtable_loyality.rowCount())):
self.Qtable_loyality.removeRow(i)
#time.sleep(5)
conn = db1.connect()
mycursor = conn.cursor()
i = 0
for id in ids:
sql_select_query = "select LOY_PROGRAM_ID, LOY_NAME ,LOY_DESC,LOY_VALID_FROM,LOY_VALID_TO, LOY_STATUS,COPMAPNY_ID,BRANCH_NO,CG_GROUP_ID,LOYCT_TYPE_ID,POS_GTIN,BMC_ID,LOY_VALUE,LOY_POINTS from Hyper1_Retail.LOYALITY_PROGRAM where LOY_PROGRAM_ID =%s"
val = (id,)
mycursor.execute(sql_select_query,val)
record = mycursor.fetchone()
self.Qtable_loyality.insertRow(i)
for column_number, data in enumerate(record):
if column_number == 5:
data = util.FN_GET_STATUS_DESC(str(data))
elif column_number == 6:
data = util.FN_GET_COMP_DESC(str(data))
elif column_number == 7:
data = util.FN_GET_BRANCH_DESC(str(data))
elif column_number == 8:
data = self.FN_GET_CUSTGP_DESC(str(data))
elif column_number == 9:
data = util.FN_GET_CUSTTP_DESC(str(data))
elif column_number == 11:
data = self.FN_GET_BMC_DESC(str(data))
self.Qtable_loyality.setItem(i, column_number, QTableWidgetItem(str(data)))
i=i+1
except (Error, Warning) as e:
print(e)
self.Qtable_loyality.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
def FN_UPLOAD_LOYPROG(self):
try:
self.window_two = CL_loyProg()
self.window_two.FN_LOAD_UPLOAD()
self.window_two.show()
except Exception as err:
print(err)
def FN_OPEN_FILE(self):
options = QFileDialog.Options()
options |= QFileDialog.DontUseNativeDialog
fileName, _ = QFileDialog.getOpenFileName(self, "QFileDialog.getOpenFileName()", "",
" Files (*.xlsx)", options=options)
self.LE_fileName.setText(fileName)
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)