class CL_redeemTypeValue(QtWidgets.QDialog):
switch_window = QtCore.pyqtSignal()
dirname = ''
parent =''
modify_flag=0
whereClause1= ''
field_names = ['رقم العمليه', 'رقم العميل','اسم العميل','نوع الإسترجاع','الشركه','الفرع','ماكينه الكاشير','رقم الفاتوره','تاريخ الفاتوره','نقاط العميل قبل','النقاط المكتسبه','تقاط العميل بعد','الحاله']
def __init__(self):
super(CL_redeemTypeValue, self).__init__()
cwd = Path.cwd()
mod_path = Path( __file__ ).parent.parent.parent
self.dirname = mod_path.__str__() + '/presentation/reports_ui'
def FN_LOAD_DISPLAY(self):
try:
filename = self.dirname + '/redeemTypeValue.ui'
loadUi(filename, self)
self.Qbtn_search.clicked.connect(self.FN_SEARCH)
#self.Qline_cust.textChanged.connect(self.FN_CLEAR_FEILDS)
self.Qbtn_search_details.clicked.connect(self.FN_SEARCH_DETAILS)
self.Qbtn_print.clicked.connect(self.printpreviewDialog)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
valid_from = str(datetime.today().strftime('%Y-%m-%d'))
xto = valid_from.split("-")
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_from.setDate(d)
self.Qcombo_company = CheckableComboBox(self)
self.Qcombo_company.setGeometry(490, 42, 179, 18)
self.Qcombo_company.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_company.setStyleSheet("background-color: rgb(198, 207, 199)")
self.Qcombo_branch = CheckableComboBox(self)
self.Qcombo_branch.setGeometry(490, 95, 179, 18)
self.Qcombo_branch.setLayoutDirection(QtCore.Qt.LeftToRight)
self.Qcombo_branch.setStyleSheet("background-color: rgb(198, 207, 199)")
self.FN_GET_COMPANIES()
self.Qcombo_company.model().dataChanged.connect(self.FN_GET_Branch)
self.FN_GET_Branch()
self.FN_GET_REDEEMTPS()
except Exception as err:
print(err)
def FN_GET_REDEEMTPS(self):
self.conn = db1.connect()
try:
mycursor = self.conn.cursor()
mycursor.execute("SELECT REDEEMT_DESC ,REDEEMT_TYPE_ID FROM Hyper1_Retail.REDEEM_TYPE ")
records = mycursor.fetchall()
for row, val in records:
self.CMB_redeemType.addItem(row, val)
except Exception as err:
print(err)
def FN_GET_Branch(self):
self.Qcombo_branch.clear()
i = 0
try:
# Todo: method for fills the Branch combobox
self.conn = db1.connect()
mycursor = self.conn.cursor()
val3 = ""
for a in range(len(self.Qcombo_company.currentData())):
if a < len(self.Qcombo_company.currentData()) - 1:
val3 = val3 + "'" + self.Qcombo_company.currentData()[a] + "',"
else:
val3 = val3 + "'" + self.Qcombo_company.currentData()[a] + "'"
sqlite3 = "SELECT BRANCH_DESC_A ,BRANCH_NO FROM BRANCH WHERE COMPANY_ID in (" + val3 + ")"
mycursor.execute(sqlite3)
records = mycursor.fetchall()
for row, val in records:
for bra in CL_userModule.branch:
if val in bra:
self.Qcombo_branch.addItem(row, val)
i += 1
mycursor.close()
self.Qcombo_branch.setCurrentIndex(-1)
except Exception as err:
print(err)
def FN_GET_COMPANIES(self):
try:
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY")
records = mycursor.fetchall()
print(records)
for row, val in records:
self.Qcombo_company.addItem(row, val)
mycursor.close()
except Exception as err:
print(err)
def FN_SEARCH_DETAILS(self):
try:
self.FN_SEARCH()
for i in reversed(range(self.Qtable_customer.rowCount())):
self.Qtable_customer.removeRow(i)
conn = db1.connect()
mycursor = conn.cursor()
self.sql = "SELECT MEMBERSHIP_POINTS_TRANS 'رقم العمليه',cp.POSC_CUST_ID 'رقم العميل', c.POSC_NAME 'اسم العميل',REDEEM_TYPE_ID 'نوع الإسترجاع',COMPANY_ID 'الشركه' ,BRANCH_NO 'الفرع',POS_NO 'ماكينه الكاشير',INVOICE_NO 'رقم الفاتوره' ,INVOICE_DATE 'تاريخ الفاتوره', POSC_POINTS_BEFORE 'نقاط العميل قبل', TRANS_POINTS_QTY 'النقاط المكتسبه',POSC_POINTS_AFTER 'تقاط العميل بعد',TRANS_STATUS 'الحاله' FROM Hyper1_Retail.LOYALITY_POINTS_TRANSACTION_LOG cp " \
" left outer join Hyper1_Retail.POS_CUSTOMER c " \
" on cp.POSC_CUST_ID = c.POSC_CUST_ID where " + self.whereClause1
mycursor.execute(self.sql)
print(self.sql)
myresult = mycursor.fetchall()
for row_number, row_data in enumerate(myresult):
self.Qtable_customer.insertRow(row_number)
for column_number, data in enumerate(row_data):
if column_number == 3:
data = util.FN_GET_REDEEMTYPE_DESC(str(data))
elif column_number == 4:
data = util.FN_GET_COMP_DESC(str(data))
elif column_number == 5:
data = util.FN_GET_BRANCH_DESC(str(data))
elif column_number == 12:
data = self.FN_GET_TRANS_STATUS(str(data))
self.Qtable_customer.setItem(row_number, column_number, QTableWidgetItem(str(data)))
self.Qtable_customer.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
mycursor.close()
except Exception as err:
print(err)
def FN_GET_TRANS_STATUS(self,status):
if status == '2':
return "Completed"
elif status== '0':
return "Reversed"
elif status == '1':
return "Cancelled"
def FN_SEARCH(self):
try:
self.FN_CLEAR_FEILDS()
branchs = self.Qcombo_branch.currentData()
companies = self.Qcombo_company.currentData()
date_from = self.Qdate_from.dateTime().toString('yyyy-MM-dd')
date_to = self.Qdate_to.dateTime().toString('yyyy-MM-dd')
redeem_type = self.CMB_redeemType.currentData()
conn = db1.connect()
mycursor = conn.cursor()
whereClause = ""
whereClause = whereClause + " `TRANS_CREATED_ON` >= '" + date_from + "' and `TRANS_CREATED_ON` <= '" + date_to + "' "
whereClause = whereClause + " and REDEEM_TYPE_ID = '" +redeem_type+"'"
company_list = companies
if len(company_list) > 0:
if len(company_list) == 1:
whereClause = whereClause + " and `COMPANY_ID` = '" + company_list[0] + "'"
else:
company_list_tuple = tuple(company_list)
whereClause = whereClause + " and `COMPANY_ID` in {}".format(company_list_tuple)
# get branchs
branch_list = branchs
if len(branch_list) > 0:
if len(branch_list) == 1:
whereClause = whereClause + " and BRANCH_NO ='" + branch_list[0] + "'"
else:
branch_list_tuple = tuple(branch_list)
whereClause = whereClause + " and BRANCH_NO in {} ".format(branch_list_tuple)
sql_select_query = "SELECT sum( TRANS_POINTS_QTY) ,sum(`TRANS_POINTS_VALUE`) FROM Hyper1_Retail.LOYALITY_POINTS_TRANSACTION_LOG where " + whereClause + " group by REDEEM_TYPE_ID"
#print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
if len(records) >0:
self.Qline_points.setText(str (records[0][0]))
self.Qline_point_value.setText(str (records[0][1]))
mycursor.close()
self.whereClause1 = whereClause
except Exception as err:
print(err)
def printpreviewDialog(self):
try:
# Todo: method for export reports pdf file
points = self.Qline_points.text().strip()
pointValue = self.Qline_point_value.text().strip()
redeem_type = self.CMB_redeemType.currentText()
title = Text()
title.setFooter(
" س ت 36108 ملف ضريبي 212/306/5 مأموريه ضرائب الشركات المساهمة رقم التسجيل بضرائب المبيعات 153/846/310 ")
title.setFont('Scheherazade-Regular.ttf')
title.setFontsize(10)
title.setName("Redeem Type values")
#title.setcodeText("15235")
title.setwaterText("hyperone company")
#title.settelText("1266533")
title.setbrachText("Entrance 1,EL Sheikh Zayed City")
#title.setCursor("Testing")
title.setQuery(self.sql)
title.setCursor(self.field_names)
data = [['Redeem Type:' , ' ',redeem_type ],
['No of points ' , ' ',points ,' ', 'Point value:' , ' ',pointValue],]
title.setData(data)
body()
QtWidgets.QMessageBox.information(self, "Success", "Report is printed successfully")
import os
os.system('my_file.pdf')
except Exception as err:
print(err)
def FN_CLEAR_FEILDS (self):
self.Qline_points.setText("")
self.Qline_point_value.setText("")
class CL_installmentReport(QtWidgets.QDialog):
switch_window = QtCore.pyqtSignal()
dirname = ''
field_names = [
'رقم البرنامج', 'اسم البرنامج', 'مجموعات العملاء', 'الشركه', 'الفرع',
'الإداره', 'القسم', 'من تاريخ', 'إلى تاريخ', 'المصاريف الإداريه',
'الحاله', 'مده التقسيط', 'تقسيط هايبر', 'تقسيط بنك', 'تقسيط مورد',
'نسبه هايبر', 'نسبه المورد', 'نسبه العميل'
]
barcodes = []
def __init__(self):
super(CL_installmentReport, self).__init__()
cwd = Path.cwd()
mod_path = Path(__file__).parent.parent.parent
self.dirname = mod_path.__str__() + '/presentation/reports_ui'
conn = db1.connect()
def FN_EXPORT(self):
try:
filename = QFileDialog.getSaveFileName(self, "Save File", '',
"(*.xls)")
wb = xlwt.Workbook()
# add_sheet is used to create sheet.
sheet = wb.add_sheet('Sheet 1')
sheet.write(0, 0, 'رقم البرنامج')
sheet.write(0, 1, 'اسم البرنامج')
sheet.write(0, 2, 'مجموعات العملاء')
sheet.write(0, 3, 'الشركه')
sheet.write(0, 4, 'الفرع')
sheet.write(0, 5, 'الإداره')
sheet.write(0, 6, 'القسم')
sheet.write(0, 7, 'من تاريخ')
sheet.write(0, 8, 'إلى تاريخ')
sheet.write(0, 9, 'المصاريف الإداريه')
sheet.write(0, 10, 'الحاله')
sheet.write(0, 11, 'مده التقسيط')
sheet.write(0, 12, 'تقسيط هايبر')
sheet.write(0, 13, 'تقسيط بنك')
sheet.write(0, 14, 'تقسيط مورد')
sheet.write(0, 15, 'نسبه هايبر')
sheet.write(0, 16, 'نسبه المورد')
sheet.write(0, 17, 'نسبه العميل')
rowNo = self.Qtable_inst.rowCount() + 1
for currentColumn in range(self.Qtable_inst.columnCount()):
for currentRow in range(self.Qtable_inst.rowCount()):
teext = str(
self.Qtable_inst.item(currentRow,
currentColumn).text())
if teext != None:
sheet.write(currentRow + 1, currentColumn, teext)
else:
sheet.write(currentRow + 1, currentColumn, ' ')
# # wb.save('test11.xls')
wb.save(str(filename[0]))
# wb.close()
import webbrowser
webbrowser.open(filename[0])
except Exception as err:
print(err)
def FN_LOAD_DISPLAY(self):
try:
filename = self.dirname + '/installment.ui'
loadUi(filename, self)
conn = db1.connect()
mycursor = conn.cursor()
self.Qbtn_search.clicked.connect(self.FN_SEARCH)
self.Qbtn_export.clicked.connect(self.FN_EXPORT)
for row_number, row_data in enumerate(CL_userModule.myList):
if row_data[1] == 'Installment':
if row_data[4] == 'None':
print('hh')
else:
sql_select_query = "select i.ITEM_DESC from Hyper1_Retail.SYS_FORM_ITEM i where ITEM_STATUS= 1 and i.item_id =%s"
x = (row_data[4], )
mycursor.execute(sql_select_query, x)
result = mycursor.fetchone()
# print(result)
if result[0] == 'search':
self.Qbtn_search.setEnabled(True)
elif result[0] == 'export':
self.Qbtn_export.setEnabled(True)
self.Qbtn_loadItems.clicked.connect(self.FN_LOAD_BARCODES)
# Apply Style For Design
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
#Get installment type
self.Qcombo_installmentType.addItem("أختر المده", '0')
self.FN_GET_installment_types_period()
# test Multi selection for company
#drob down list with multiselection for company
self.Qcombo_company = CheckableComboBox(self)
self.Qcombo_company.setGeometry(490, 20, 179, 20)
self.Qcombo_company.setLayoutDirection(QtCore.Qt.RightToLeft)
self.Qcombo_company.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.FN_GET_Company()
# TODO Click listner for changing list of company
self.Qcombo_company.model().dataChanged.connect(self.FN_GET_Branch)
#drob down list with multiselection for bracnch
self.Qcombo_branch = CheckableComboBox(self)
self.Qcombo_branch.setGeometry(490, 60, 179, 20)
self.Qcombo_branch.setLayoutDirection(QtCore.Qt.RightToLeft)
self.Qcombo_branch.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.FN_GET_Branch()
#validation for not pick date before today
datefrom = str(datetime.today().strftime('%Y-%m-%d'))
xfrom = datefrom.split("-")
d = QDate(int(xfrom[0]), int(xfrom[1]), int(xfrom[2]))
self.Qdate_from.setDate(d)
self.Qdate_to.setDate(d)
# Get customer Groupe
self.Qcombo_customerGroupe = CheckableComboBox(self)
self.Qcombo_customerGroupe.setGeometry(490, 100, 179, 20)
self.Qcombo_customerGroupe.setLayoutDirection(
QtCore.Qt.RightToLeft)
self.Qcombo_customerGroupe.setStyleSheet(
"background-color: rgb(198, 207, 199)")
self.FN_GET_customerGroupe()
# Multi selection for department
self.Qcombo_department = CheckableComboBox(self)
self.Qcombo_department.setGeometry(630, 190, 85, 22)
self.Qcombo_department.setEnabled(False)
self.Qcombo_department.setLayoutDirection(QtCore.Qt.RightToLeft)
self.Qcombo_department.setStyleSheet(
"background-color: rgb(198, 207, 199)")
# TODO Click listner for changing list of department
self.Qcombo_department.model().dataChanged.connect(
self.FN_WhenChecksection)
# get Department list if check box
self.checkBox_department.stateChanged.connect(
self.FN_WhenCheckDepartment)
# self.FN_WhenCheckDepartment()
# Multi selection for sections
self.Qcombo_section = CheckableComboBox(self)
self.Qcombo_section.setGeometry(490, 190, 80, 22)
self.Qcombo_section.setLayoutDirection(QtCore.Qt.RightToLeft)
self.Qcombo_section.setEnabled(False)
self.Qcombo_section.setStyleSheet(
"background-color: rgb(198, 207, 199)")
# get sections list if check box
self.checkBox_section.stateChanged.connect(
self.FN_WhenChecksection)
# self.FN_GET_sections()
# TODO Click listner for changing list of department
self.Qcombo_section.model().dataChanged.connect(
self.FN_WhenCheckBMC_Level)
# Multi selection for BMCLevel
self.Qcombo_BMCLevel = CheckableComboBox(self)
self.Qcombo_BMCLevel.setGeometry(270, 190, 110, 22)
self.Qcombo_BMCLevel.setLayoutDirection(QtCore.Qt.RightToLeft)
self.Qcombo_BMCLevel.setEnabled(False)
self.Qcombo_BMCLevel.setStyleSheet(
"background-color: rgb(198, 207, 199)")
# get BMC LEVEL4 list if check box
self.checkBox_BMCLevel.stateChanged.connect(
self.FN_WhenCheckBMC_Level)
self.checkBox_Barcode.stateChanged.connect(
self.FN_WhenCheckBarcode)
# get Banks list if readio button clicked
self.RBTN_bank.clicked.connect(self.FN_InstallMent_Checked)
# get Vendor list if readio button clicked
self.RBTN_vendor.clicked.connect(self.FN_InstallMent_Checked)
# if readio button clicked hyperone
self.RBTN_hyperone.clicked.connect(self.FN_InstallMent_Checked)
# this function for what enabled or not when start
self.EnabledWhenOpen()
self.Rbtn_stsAll.setChecked(True)
#enable the search and the export buttons
except Exception as err:
print(err)
def FN_LOAD_BARCODES(self):
try:
self.Qlab_msg.setText("")
options = QFileDialog.Options()
options |= QFileDialog.DontUseNativeDialog
fileName, _ = QFileDialog.getOpenFileName(
self,
"QFileDialog.getOpenFileName()",
"",
" Files (*.xlsx)",
options=options)
self.barcodes = []
if fileName != '':
#self.LE_fileName.setText(fileName)
wb = xlrd.open_workbook(fileName)
sheet = wb.sheet_by_index(0)
conn = db1.connect()
mycursor = conn.cursor()
for i in range(sheet.nrows):
error = 0
bar = int(sheet.cell_value(i, 0))
self.barcodes.append(str(bar))
if len(self.barcodes) > 0:
self.Qlab_msg.setText("تم تحميل الأصناف")
#print(barcodes)
except Exception as err:
print(err)
# this function for what enabled or not when start
def EnabledWhenOpen(self):
self.checkBox_department.setEnabled(True)
self.checkBox_section.setEnabled(False)
self.Qcombo_section.setEnabled(False)
self.checkBox_BMCLevel.setEnabled(False)
self.Qcombo_BMCLevel.setEnabled(False)
#get installments period list
def FN_GET_installment_types_period(self):
#self.Qcombo_installmentType.clear()
conn = db1.connect()
mycursor = conn.cursor()
mycursor.execute(
"SELECT InstT_Installment_Period ,INSTT_TYPE_ID FROM INSTALLMENT_TYPE"
)
records = mycursor.fetchall()
mycursor.close()
for row, val in records:
self.Qcombo_installmentType.addItem(row, val)
# get branches list
# get companys list
def FN_GET_Company(self):
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT COMPANY_DESC , COMPANY_ID FROM COMPANY")
records = mycursor.fetchall()
for row, val in records:
self.Qcombo_company.addItem(row, val)
mycursor.close()
# get branches list
def FN_GET_Branch(self):
self.Qcombo_branch.clear()
i = 0
try:
# Todo: method for fills the Branch combobox
self.conn = db1.connect()
mycursor = self.conn.cursor()
val3 = ""
for a in range(len(self.Qcombo_company.currentData())):
if a < len(self.Qcombo_company.currentData()) - 1:
val3 = val3 + "'" + self.Qcombo_company.currentData(
)[a] + "',"
else:
val3 = val3 + "'" + self.Qcombo_company.currentData(
)[a] + "'"
sqlite3 = "SELECT BRANCH_DESC_A ,BRANCH_NO FROM BRANCH WHERE COMPANY_ID in (" + val3 + ")"
mycursor.execute(sqlite3)
records = mycursor.fetchall()
for row, val in records:
for bra in CL_userModule.branch:
if val in bra:
self.Qcombo_branch.addItem(row, val)
i += 1
mycursor.close()
self.Qcombo_branch.setCurrentIndex(-1)
except:
print(sys.exc_info())
# get customer Groupe list
def FN_GET_customerGroupe(self):
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT CG_DESC,CG_GROUP_ID FROM CUSTOMER_GROUP")
records = mycursor.fetchall()
for row, val in records:
self.Qcombo_customerGroupe.addItem(row, val)
mycursor.close()
# after check department check box
def FN_WhenCheckDepartment(self):
if self.checkBox_department.isChecked():
self.FN_GET_Department()
self.Qcombo_department.setEnabled(True)
self.checkBox_section.setEnabled(True)
self.Qcombo_department.setCurrentIndex(-1)
else:
self.Qcombo_department.unCheckedList()
self.Qcombo_department.setEnabled(False)
self.checkBox_section.setEnabled(False)
self.checkBox_section.setChecked(False)
self.checkBox_BMCLevel.setChecked(False)
self.Qcombo_department.setCurrentIndex(-1)
# get Department list
def FN_GET_Department(self):
self.Qcombo_department.clear()
i = 0
try:
# Todo: method for fills the section combobox
"""
self.Qcombo_department.clear()
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT DEPARTMENT_DESC,DEPARTMENT_ID FROM DEPARTMENT")
records = mycursor.fetchall()
for row, val in records:
for sec in CL_userModule.section :
if val in sec:
self.Qcombo_department.addItem(row, val)
i += 1
mycursor.close()
"""
print("FN_GET_Department", CL_userModule.section)
for row, val, row1, val1 in CL_userModule.section:
self.Qcombo_department.addItem(val1, row1)
except:
print(sys.exc_info())
# after check section check box
def FN_WhenChecksection(self):
if self.checkBox_section.isChecked():
self.FN_GET_sections()
self.checkBox_BMCLevel.setEnabled(True)
self.Qcombo_section.setEnabled(True)
self.Qcombo_section.setCurrentIndex(-1)
else:
self.Qcombo_section.unCheckedList()
self.checkBox_BMCLevel.setChecked(False)
self.checkBox_BMCLevel.setEnabled(False)
self.Qcombo_section.setEnabled(False)
self.Qcombo_section.setCurrentIndex(-1)
# self.Qcombo_section.unChecked()
def FN_WhenCheckBarcode(self):
if self.checkBox_Barcode.isChecked():
self.Qline_barcode.setEnabled(True)
self.Qbtn_loadItems.setEnabled(True)
else:
self.Qline_barcode.setEnabled(False)
self.Qbtn_loadItems.setEnabled(False)
self.Qlab_msg.setText('')
# get sections list
def FN_GET_sections(self):
self.Qcombo_section.clear()
i = 0
try:
conn = db1.connect()
mycursor = conn.cursor()
#print("currentData", self.Qcombo_department.currentData())
val3 = ""
for a in range(len(self.Qcombo_department.currentData())):
if a < len(self.Qcombo_department.currentData()) - 1:
val3 = val3 + "'" + self.Qcombo_department.currentData(
)[a] + "',"
else:
val3 = val3 + "'" + self.Qcombo_department.currentData(
)[a] + "'"
#print("deparments", val3)
mycursor.execute(
"SELECT SECTION_DESC,SECTION_ID FROM SECTION where DEPARTMENT_ID in ("
+ val3 + ")")
# print("Query"+"SELECT SECTION_DESC,SECTION_ID FROM SECTION where DEPARTMENT_ID in ("+val3+")")
records = mycursor.fetchall()
mycursor.close()
for row, val in records:
self.Qcombo_section.addItem(row, val)
i += 1
except:
print(sys.exc_info())
# after check department check box
# after check BMC Level check box
def FN_WhenCheckBMC_Level(self):
if self.checkBox_BMCLevel.isChecked():
self.FN_GET_BMC_Level()
self.Qcombo_BMCLevel.setEnabled(True)
self.Qcombo_BMCLevel.setCurrentIndex(-1)
# self.FN_
else:
self.Qcombo_BMCLevel.unCheckedList()
self.checkBox_BMCLevel.setChecked(False)
self.Qcombo_BMCLevel.setEnabled(False)
self.Qcombo_BMCLevel.setCurrentIndex(-1)
# get BMC LEVEL4 list
def FN_GET_BMC_Level(self):
self.Qcombo_BMCLevel.clear()
i = 0
try:
conn = db1.connect()
mycursor = conn.cursor()
val3 = ""
for a in range(len(self.Qcombo_section.currentData())):
if a < len(self.Qcombo_section.currentData()) - 1:
val3 = val3 + "'" + self.Qcombo_section.currentData(
)[a] + "',"
else:
val3 = val3 + "'" + self.Qcombo_section.currentData(
)[a] + "'"
#print("sections", val3)
mycursor.execute(
"SELECT BMC_LEVEL4_DESC,BMC_LEVEL4 FROM BMC_LEVEL4 where SECTION_ID in ("
+ val3 + ")")
records = mycursor.fetchall()
mycursor.close()
for row, val in records:
self.Qcombo_BMCLevel.addItem(row, val)
i += 1
except:
print(sys.exc_info())
def FN_InstallMent_Checked(self):
try:
if self.RBTN_bank.isChecked():
self.FN_GET_Banks()
self.Qcombo_bank.setEnabled(True)
self.Qcombo_vendor.setEnabled(False)
self.Qcombo_vendor.clear()
elif self.RBTN_vendor.isChecked():
self.Qcombo_bank.setEnabled(False)
self.Qcombo_bank.clear()
self.FN_GET_Vendor()
self.Qcombo_vendor.setEnabled(True)
elif self.RBTN_hyperone.isChecked():
self.Qcombo_bank.setEnabled(False)
self.Qcombo_bank.clear()
self.Qcombo_vendor.setEnabled(False)
self.Qcombo_vendor.clear()
# TO clear Data From QTable
except:
print(sys.exc_info())
# get Banks list
def FN_GET_Banks(self):
self.Qcombo_bank.clear()
self.Qcombo_bank.addItem("أختر بنك", "0")
conn = db1.connect()
mycursor = conn.cursor()
mycursor.execute("SELECT Bank_Desc,Bank_ID FROM BANK")
records = mycursor.fetchall()
mycursor.close()
for row, val in records:
self.Qcombo_bank.addItem(row, val)
# get Vendor list
def FN_GET_Vendor(self):
self.Qcombo_vendor.clear()
self.Qcombo_vendor.addItem("أختر مورد", "0")
conn = db1.connect()
mycursor = conn.cursor()
mycursor.execute(
"SELECT SPONSOR_NAME,SPONSOR_ID FROM Hyper1_Retail.SPONSOR")
records = mycursor.fetchall()
mycursor.close()
for row, val in records:
self.Qcombo_vendor.addItem(row, val)
def FN_CHECK_VALID_BARCCODE(self, id):
try:
conn = db1.connect()
mycursor11 = conn.cursor()
sql = "SELECT * FROM Hyper1_Retail.POS_ITEM where POS_GTIN = '" + str(
id) + "'"
mycursor11.execute(sql)
myresult = mycursor11.fetchone()
if mycursor11.rowcount > 0:
mycursor11.close()
return True
else:
mycursor11.close()
return False
except Exception as err:
print(err)
def FN_SEARCH(self):
try:
for i in reversed(range(self.Qtable_inst.rowCount())):
self.Qtable_inst.removeRow(i)
cust_gps = self.Qcombo_customerGroupe.currentData()
branchs = self.Qcombo_branch.currentData()
companies = self.Qcombo_company.currentData()
bar = self.Qline_barcode.text()
BMC = []
sec = []
dep = []
conn = db1.connect()
mycursor = conn.cursor()
whereClause = ""
ret = True
if self.checkBox_Barcode.isChecked():
if len(bar) > 0:
ret = self.FN_CHECK_VALID_BARCCODE(bar)
self.barcodes = []
else:
self.barcodes = []
if ret == True:
if self.checkBox_BMCLevel.isChecked():
#get bmc
BMC = self.Qcombo_BMCLevel.currentData()
elif self.checkBox_section.isChecked():
#get sec
sec = self.Qcombo_section.currentData()
elif self.checkBox_department.isChecked():
# get dep
dep = self.Qcombo_department.currentData()
instType = self.Qcombo_installmentType.currentData()
instDesc = self.LE_desc.text()
date_from = self.Qdate_from.dateTime().toString('yyyy-MM-dd')
date_to = self.Qdate_to.dateTime().toString('yyyy-MM-dd')
### Call Validation Function
if self.Rbtn_stsActive.isChecked():
whereClause = whereClause + ' INST_STATUS = 1'
elif self.Rbtn_stsInactive.isChecked():
whereClause = whereClause + ' INST_STATUS = 0'
elif self.Rbtn_stsAll.isChecked():
whereClause = whereClause + ' INST_STATUS in ( 0,1)'
if self.RBTN_bank.isChecked():
if self.Qcombo_bank.currentData() == "0":
whereClause = whereClause + "and s.BANK_ID <> '' "
else:
whereClause = whereClause + "and s.BANK_ID = '" + str(
self.Qcombo_bank.currentData()) + "'"
elif self.RBTN_vendor.isChecked():
if self.Qcombo_vendor.currentData() == "0":
whereClause = whereClause + " and s.SPONSOR_ID <> '' "
else:
whereClause = whereClause + " and s.SPONSOR_ID = '" + str(
self.Qcombo_vendor.currentData()) + "'"
elif self.RBTN_hyperone.isChecked():
whereClause = whereClause + " and s.HYPERONE <> '' "
#elif self.RBTN_all.isChecked():
#whereClause = whereClause + " and s.HYPERONE <> '' "
if instDesc != '':
whereClause = whereClause + " and p.INST_DESC like '%" + str(
instDesc) + "%' "
# get COMPANY
if len(companies) > 0:
if len(companies) == 1:
whereClause = whereClause + " and c.COMPANY_ID = '" + companies[
0] + "'"
else:
company_list_tuple = tuple(companies)
whereClause = whereClause + " and c.COMPANY_ID in {}".format(
company_list_tuple)
# get branchs
if len(branchs) > 0:
if len(branchs) == 1:
whereClause = whereClause + " and b.BRANCH_NO ='" + branchs[
0] + "'"
else:
branch_list_tuple = tuple(branchs)
whereClause = whereClause + " and b.BRANCH_NO in {} ".format(
branch_list_tuple)
# get customer gp id
if len(cust_gps) > 0:
if len(cust_gps) == 1:
whereClause = whereClause + " and g.CG_GROUP_ID ='" + str(
cust_gps[0]) + "'"
else:
cust_gp_list_tuple = tuple(cust_gps)
whereClause = whereClause + " and g.CG_GROUP_ID in {}".format(
cust_gp_list_tuple)
#get BMC levels
if len(BMC) > 0:
if len(BMC) == 1:
whereClause = whereClause + " and sec.BMC_ID ='" + str(
BMC[0]) + "'"
else:
bmc_tuple = tuple(BMC)
whereClause = whereClause + " and sec.BMC_ID in {}".format(
bmc_tuple)
elif len(sec) > 0:
if len(sec) == 1:
whereClause = whereClause + " and sec.SECTION_ID ='" + str(
sec[0]) + "'"
else:
sec_tuple = tuple(sec)
whereClause = whereClause + " and sec.SECTION_ID in {}".format(
sec_tuple)
elif len(dep) > 0:
if len(dep) == 1:
whereClause = whereClause + " and sec.DEPARTMENT_ID ='" + str(
dep[0]) + "'"
else:
dep_tuple = tuple(dep)
whereClause = whereClause + " and sec.DEPARTMENT_ID in {}".format(
dep_tuple)
if instType != '0':
whereClause = whereClause + " and tp.INSTT_TYPE_ID = '" + str(
instType) + "'"
if len(bar) > 0:
whereClause = whereClause + " and ii.POS_GTIN = '" + str(
bar) + "'"
if len(self.barcodes) > 0:
if len(self.barcodes) == 1:
whereClause = whereClause + "and ii.POS_GTIN = '" + str(
self.barcodes[0]) + "'"
else:
bar_tuple = tuple(self.barcodes)
whereClause = whereClause + " and ii.POS_GTIN in {}".format(
bar_tuple)
whereClause = whereClause + " and INST_VALID_FROM >= '" + date_from + "' and INST_VALID_TO <= '" + date_to + "' "
sql_select_query = "SELECT distinct p.INST_PROGRAM_ID, p.INST_DESC ,'customer_gp','company','branch','dep', 'sec',p.INST_VALID_FROM,p.INST_VALID_TO,p.INST_ADMIN_EXPENSES_PERC,p.INST_STATUS , tp.INSTT_DESC,s.HYPERONE,bank.Bank_Desc ,spon.SPONSOR_NAME , r.INSTR_HYPER_RATE,r.INSTR_SPONSOR_RATE,r.INSTR_CUSTOMER_RATE FROM Hyper1_Retail.INSTALLMENT_PROGRAM p inner join Hyper1_Retail.INSTALLMENT_RULE r on p.INSTR_RULEID = r.INSTR_RULEID inner join Hyper1_Retail.INSTALLMENT_TYPE tp on r.INSTT_TYPE_ID = tp.INSTT_TYPE_ID inner join Hyper1_Retail.INSTALLMENT_GROUP g on p.INST_PROGRAM_ID = g.INST_PROGRAM_ID inner join Hyper1_Retail.INSTALLMENT_BRANCH b on p.INST_PROGRAM_ID = b.INST_PROGRAM_ID inner join Hyper1_Retail.INSTALLMENT_SPONSOR s on p.INSTR_RULEID = s.INSTR_RULEID left outer join Hyper1_Retail.BANK bank on s.BANK_ID = bank.Bank_ID left outer join Hyper1_Retail.SPONSOR spon on spon.SPONSOR_ID = s.SPONSOR_ID inner join CUSTOMER_GROUP cg on g.CG_GROUP_ID = cg.CG_GROUP_ID inner join Hyper1_Retail.BRANCH branch on b.COMPANY_ID = branch.COMPANY_ID and b.BRANCH_NO = branch.BRANCH_NO inner join Hyper1_Retail.COMPANY c on branch.COMPANY_ID =c.COMPANY_ID " \
"left outer join Hyper1_Retail.INSTALLMENT_SECTION sec on sec.INSTR_RULEID = r.INSTR_RULEID" \
" left outer join Hyper1_Retail.INSTALLMENT_ITEM ii on ii.INSTR_RULEID = r.INSTR_RULEID " \
" where"+ whereClause
print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
inst_prog_no = ''
for row_number, row_data in enumerate(records):
#get customer groups
self.Qtable_inst.insertRow(row_number)
inst_prog_no = ''
for column_number, data in enumerate(row_data):
if column_number == 0:
inst_prog_no = str(data)
if column_number == 2:
data = self.FN_GET_CUST_GP(inst_prog_no)
if column_number == 3:
data = self.FN_GET_COMP(inst_prog_no)
if column_number == 4:
data = self.FN_GET_BRANCH(inst_prog_no)
if column_number == 5:
data = self.FN_GET_DEPT(inst_prog_no)
if column_number == 6:
data = self.FN_GET_SEC(inst_prog_no)
if column_number == 10:
data = util.FN_GET_STATUS_DESC(str(data))
if data == None or data == '':
data = '----'
self.Qtable_inst.setItem(row_number, column_number,
QTableWidgetItem(str(data)))
self.Qtable_inst.setEditTriggers(
QtWidgets.QTableWidget.NoEditTriggers)
mycursor.close()
else:
QtWidgets.QMessageBox.warning(self, "خطأ", "الباركود غير صحيح")
except Exception as err:
print(err)
def FN_GET_DEPT(self, inst_no):
try:
conn = db1.connect()
mycursor = conn.cursor()
dept = ''
sql_select_query ="SELECT DEPARTMENT_DESC FROM Hyper1_Retail.INSTALLMENT_SECTION isec " \
" inner join Hyper1_Retail.INSTALLMENT_RULE irule on isec.INSTR_RULEID = irule.INSTR_RULEID " \
" inner join Hyper1_Retail.INSTALLMENT_PROGRAM p on irule.INSTR_RULEID = p.INSTR_RULEID" \
" inner join Hyper1_Retail.DEPARTMENT d " \
" on isec.DEPARTMENT_ID = d.DEPARTMENT_ID " \
" where p.INST_PROGRAM_ID = '"+inst_no+"'"
#print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for rec in records:
dept = dept + ',' + rec[0]
mycursor.close()
return dept
except Exception as err:
print(err)
def FN_GET_SEC(self, inst_no):
try:
conn = db1.connect()
mycursor = conn.cursor()
sec = ''
sql_select_query = "SELECT SECTION_DESC FROM Hyper1_Retail.INSTALLMENT_SECTION isec " \
" inner join Hyper1_Retail.INSTALLMENT_RULE irule on isec.INSTR_RULEID = irule.INSTR_RULEID " \
" inner join Hyper1_Retail.INSTALLMENT_PROGRAM p on irule.INSTR_RULEID = p.INSTR_RULEID" \
" inner join Hyper1_Retail.SECTION s "\
" on isec.SECTION_ID = s.SECTION_ID where p.INST_PROGRAM_ID = '" + inst_no + "'"
#print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for rec in records:
sec = sec + ',' + rec[0]
mycursor.close()
return sec
except Exception as err:
print(err)
def FN_GET_CUST_GP(self, inst_no):
try:
conn = db1.connect()
mycursor = conn.cursor()
cust_gp = ''
sql_select_query ="SELECT CG_DESC FROM Hyper1_Retail.INSTALLMENT_GROUP ig inner join Hyper1_Retail.CUSTOMER_GROUP cg " \
"on ig.CG_GROUP_ID = cg.CG_GROUP_ID where INST_PROGRAM_ID = '"+inst_no+"' and CG_DESC !='H1'"
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for rec in records:
cust_gp = cust_gp + ',' + rec[0]
mycursor.close()
return cust_gp
except Exception as err:
print(err)
def FN_GET_COMP(self, inst_no):
try:
conn = db1.connect()
mycursor = conn.cursor()
comp = ''
sql_select_query = "SELECT distinct COMPANY_DESC FROM Hyper1_Retail.INSTALLMENT_BRANCH ib inner join Hyper1_Retail.COMPANY c on ib.COMPANY_ID =c.COMPANY_ID where INST_PROGRAM_ID = '" + inst_no + "'"
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for rec in records:
comp = comp + ' , ' + rec[0]
mycursor.close()
return comp
except Exception as err:
print(err)
def FN_GET_BRANCH(self, inst_no):
try:
conn = db1.connect()
mycursor = conn.cursor()
branch = ''
sql_select_query = "SELECT distinct BRANCH_DESC_A,ib.COMPANY_ID FROM Hyper1_Retail.INSTALLMENT_BRANCH ib inner join Hyper1_Retail.BRANCH branch on ib.COMPANY_ID = branch.COMPANY_ID and ib.BRANCH_NO = branch.BRANCH_NO where INST_PROGRAM_ID = '" + inst_no + "'"
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for rec in records:
branch = branch + ' , ' + rec[0]
mycursor.close()
return branch
except Exception as err:
print(err)