class CL_EditCoupon(QtWidgets.QDialog):
valueType=""
valueData=""
serialCount = ""
MultiCount = ""
MultiUse = ""
movement=0
serial_num=0
usage=0
branch_list = []
new_branch_list = []
multiusage=0
serial_type=0
dfrom=QDate(1,1,2000)
Special=0
DescOldValue= ""
COPDISCOUNToldVAL= ""
COPDISCOUNToldprecnt= ""
Othertype=""
row=""
oldlist=[]
newlist=[]
oldstatus=""
def __init__(self):
super(CL_EditCoupon, 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 edit coupon
def FN_LOADUI(self):
filename = self.dirname + '/editCoupon.ui'
loadUi(filename, self)
self.Qcombo_company = CheckableComboBox(self)
self.Qcombo_company.setGeometry(550, 135, 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, 165, 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.CMB_CouponStatus.addItems(["Inactive","Active"])
self.FN_getData()
self.CMB_CouponDes.activated[str].connect(self.FN_getDatabyID)
self.FN_getDatabyID()
self.radioButton_Value.clicked.connect(self.FN_EnableDiscVal)
self.radioButton_Percentage.clicked.connect(self.FN_EnablePercentage)
self.checkBox_Multi.toggled.connect(self.FN_endableMultiUser)
self.BTN_editCoupon.clicked.connect(self.FN_editAction)
self.setWindowFlags(QtCore.Qt.WindowCloseButtonHint | QtCore.Qt.WindowMinimizeButtonHint)
# Set Style
self.labe_id.setStyleSheet(label_num)
self.label.setStyleSheet(desc_5)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
#Todo: method for 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()
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 to get name and id of coupon
def FN_getData(self):
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT COP_DESC,COP_ID FROM COUPON")
records = mycursor.fetchall()
for row,val in records:
self.CMB_CouponDes.addItem(row,val)
mycursor.close()
# Todo: method to get all data about coupon
def FN_getDatabyID(self):
try:
self.branch_list = []
self.new_branch_list = []
self.FN_Clear()
indx = self.CMB_CouponDes.currentData()
self.labe_id.setText(str(indx))
self.conn = db1.connect()
mycursor = self.conn.cursor()
sql_select_Query = "SELECT * FROM COUPON where COP_ID = %s"
x = (indx,)
mycursor = self.conn.cursor()
mycursor.execute(sql_select_Query, x)
record = mycursor.fetchone()
self.row=record[0]
self.LE_desc_1.setText(record[1])
self.DescOldValue=record[1]
self.COPDISCOUNToldVAL=str(record[2])
self.COPDISCOUNToldprecnt=str(record[3])
if (record[2]!=None and len(self.COPDISCOUNToldVAL) > 0):
self.radioButton_Value.setChecked(True)
self.LE_desc_2.setValue(float(record[2]))
self.LE_desc_2.setEnabled(True)
self.LE_desc_3.setEnabled(False)
self.LE_desc_3.clear()
self.valueType = "COP_DISCOUNT_VAL"
self.valueData = self.LE_desc_2.text()
self.Othertype="COP_DISCOUNT_PERCENT"
else:
self.radioButton_Percentage.setChecked(True)
self.LE_desc_3.setValue(float(record[3]))
self.LE_desc_3.setEnabled(True)
self.LE_desc_2.setEnabled(False)
self.LE_desc_2.clear()
self.valueType = "COP_DISCOUNT_PERCENT"
self.valueData = self.LE_desc_3.text()
self.Othertype="COP_DISCOUNT_VAL"
dateto = record[13]
xto = dateto.split("-")
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_to.setDate(d)
datefrom = record[11]
xfrom = datefrom.split("-")
self.dfrom = QDate(int(xfrom[0]), int(xfrom[1]), int(xfrom[2]))
self.Qdate_from.setDate(self.dfrom)
self.dfrom = QDateTime(int(xfrom[0]), int(xfrom[1]), int(xfrom[2]), 00, 00, 00, 00)
self.LE_desc_4.setValue(float(record[4]))
self.serial_num=int(record[4])
self.multiusage=int(record[5])
self.Special=int(record[5])
if (int(record[5]) == 1):
self.checkBox_Multi.setChecked(True)
self.LE_desc_5.setValue(float(record[6]))
self.LE_desc_4.setEnabled(False)
self.LE_desc_5.setEnabled(True)
else:
self.checkBox_Multi.setChecked(False)
self.LE_desc_5.setEnabled(False)
self.LE_desc_4.setEnabled(True)
self.CMB_CouponStatus.setCurrentIndex(int(record[15]))
self.oldstatus =str(record[15])
timefrom = record[12]
tfrom = timefrom.split(":")
some_time = QtCore.QTime(int(tfrom[0]), int(tfrom[1]), 00)
self.Qtime_from.setTime(some_time)
timeto = record[14]
tto = timeto.split(":")
some_time = QtCore.QTime(int(tto[0]), int(tto[1]), 00)
self.Qtime_to.setTime(some_time)
self.FN_check_company(indx)
self.FN_check_branch(indx)
sql_select_Query = " select * FROM COUPON_SERIAL_PRINT_LOG where COUPON_SERIAL_ID IN(SELECT COPS_SERIAL_ID FROM COUPON_SERIAL , COUPON WHERE COUPON_ID = COP_ID AND COP_ID = %s) "
x = (indx,)
mycursor = self.conn.cursor()
mycursor.execute(sql_select_Query, x)
record1 = mycursor.fetchall()
if mycursor.rowcount>0:
self.movement=1
sql_select_Query = " select * FROM COUPON_USAGE where COPS_SERIAL_ID IN(SELECT COPS_SERIAL_ID FROM COUPON_SERIAL , COUPON WHERE COUPON_ID = COP_ID AND COP_ID = %s) "
x = (indx,)
mycursor = self.conn.cursor()
mycursor.execute(sql_select_Query, x)
record2 = mycursor.fetchall()
if mycursor.rowcount > 0:
self.usage = 1
mycursor.close()
if self.usage==1:
self.LE_desc_1.setEnabled(False)
self.LE_desc_2.setEnabled(False)
self.LE_desc_3.setEnabled(False)
self.LE_desc_4.setEnabled(False)
self.LE_desc_5.setEnabled(False)
self.Qcombo_company.setEnabled(False)
self.Qcombo_branch.setEnabled(False)
self.Qdate_to.setEnabled(False)
self.Qdate_from.setEnabled(False)
self.usage = 0
else:
self.LE_desc_1.setEnabled(True)
self.LE_desc_2.setEnabled(True)
self.LE_desc_3.setEnabled(True)
self.LE_desc_4.setEnabled(True)
self.LE_desc_5.setEnabled(True)
self.Qcombo_company.setEnabled(True)
self.Qcombo_branch.setEnabled(True)
self.Qdate_to.setEnabled(True)
self.Qdate_from.setEnabled(True)
if (record[2] != None and len(self.COPDISCOUNToldVAL) > 0):
self.LE_desc_3.setEnabled(False)
else:
self.LE_desc_2.setEnabled(False)
if (int(record[5]) == 1):
self.LE_desc_4.setEnabled(False)
else:
self.checkBox_Multi.setChecked(False)
self.LE_desc_5.setEnabled(False)
self.branch_list.clear()
if len(self.Qcombo_branch.currentData()) > 0:
for i in self.Qcombo_branch.currentData():
self.branch_list.append(i)
self.oldlist=self.Qcombo_branch.currentData()
except:
print(sys.exc_info())
# 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)
self.LE_desc_4.setValue(1.0)
self.multiusage=1
else:
self.LE_desc_5.setEnabled(False)
self.LE_desc_4.setEnabled(True)
self.multiusage=0
# Todo: method to edit coupon
def FN_editAction(self):
try:
self.newlist = self.Qcombo_branch.currentData()
if len(self.Qcombo_company.currentData()) == 0 or len(self.Qcombo_branch.currentData()) == 0 or len(
self.LE_desc_1.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, "خطا", "اكمل العناصر الفارغه")
else:
if self.Qdate_to.dateTime() < self.Qdate_from.dateTime():
QtWidgets.QMessageBox.warning(self, "Done", "تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء")
elif self.Qdate_from.dateTime() < self.dfrom:
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:
mycursor = self.conn.cursor()
creationDate = str(datetime.today().strftime('%Y-%m-%d'))
if self.checkBox_Multi.isChecked():
self.serialCount = "1"
self.MultiCount = self.LE_desc_5.text()
self.MultiUse = "1"
self.serial_type=1
print("multi use="+str(self.multiusage))
if self.multiusage==0:
sql2 = "update COUPON_SERIAL set COPS_STATUS='0' where COUPON_ID='" + str(
self.CMB_CouponDes.currentData()) + "'"
mycursor.execute(sql2)
value = randint(0, 1000000000000)
creationDate = str(datetime.today().strftime('%Y-%m-%d'))
mycursor = self.conn.cursor()
sql7 = "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)"
val7 = (
str(self.CMB_CouponDes.currentData()), "HCOP"+bin(value), CL_userModule.user_name,self.serial_type,
creationDate, 0,
'1')
mycursor.execute(sql7, val7)
self.multiusage=1
else:
self.serialCount = self.LE_desc_4.text()
self.MultiCount = "0"
self.MultiUse = "0"
self.serial_type=0
print(self.serial_num)
print(int(self.LE_desc_4.text()))
if int(self.LE_desc_4.text()) < self.serial_num and self.movement == 1:
QtWidgets.QMessageBox.warning(self, "Error", "برجاء ادخل عدد اكبر من السابق")
else:
if self.valueType == "COP_DISCOUNT_VAL":
self.valueData = self.LE_desc_2.text()
elif self.valueType == "COP_DISCOUNT_PERCENT":
self.valueData = self.LE_desc_3.text()
sql = "update COUPON set COP_DESC='" + self.LE_desc_1.text().strip() + "'," + self.valueType + "=" + self.valueData +","+self.Othertype+"="+"null"+",COP_SERIAL_COUNT=" + self.serialCount + ",COP_MULTI_USE=" + self.MultiUse + ",COP_MULTI_USE_COUNT=" + self.MultiCount + ",COP_CHANGED_BY='" + CL_userModule.user_name + "',COP_CHANGED_ON='" + creationDate + "',COP_VALID_FROM='" + self.Qdate_from.dateTime().toString(
'yyyy-MM-dd') + "',COP_VALID_TO='" + self.Qdate_to.dateTime().toString(
'yyyy-MM-dd') + "',COP_STATUS='" + str(
self.CMB_CouponStatus.currentIndex()) + "',COP_TIME_FROM='"+str(self.Qtime_from.dateTime().toString('hh:mm'))+"',COP_TIME_TO='"+str(self.Qtime_to.dateTime().toString('hh:mm'))+"' where COP_ID='" + str(
self.CMB_CouponDes.currentData()) + "'"
print(sql)
mycursor.execute(sql)
if len(self.Qcombo_branch.currentData()) > 0:
for i in self.Qcombo_branch.currentData():
self.new_branch_list.append(i)
if len(self.branch_list) > len(self.new_branch_list):
for row in self.branch_list:
print(row)
if row in self.new_branch_list:
print("found")
else:
print("not found")
mycursor = self.conn.cursor()
sql5 = "update COUPON_BRANCH set STATUS= 0 where COUPON_ID='" + str(
self.CMB_CouponDes.currentData()) + "' and BRANCH_NO = '" + row + "'"
mycursor.execute(sql5)
print(sql5)
else:
for row in self.new_branch_list:
print(row)
if row in self.branch_list:
print("found")
else:
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT * FROM COUPON_BRANCH where BRANCH_NO='" + row + "' and COUPON_ID='" + str(
self.CMB_CouponDes.currentData()) + "'")
record = mycursor.fetchall()
if mycursor.rowcount > 0:
mycursor = self.conn.cursor()
sql8 = "update COUPON_BRANCH set STATUS= 1 where COUPON_ID='" + str(
self.CMB_CouponDes.currentData()) + "' and BRANCH_NO = '" + row + "'"
mycursor.execute(sql8)
print(sql8)
else:
mycursor = self.conn.cursor()
sql6 = "INSERT INTO COUPON_BRANCH (COMPANY_ID,BRANCH_NO,COUPON_ID,STATUS) VALUES (%s,%s,%s,%s)"
val6 = (
str(self.Qcombo_company.currentData()[0]), row,
str(self.CMB_CouponDes.currentData()),
'1')
mycursor.execute(sql6, val6)
if(self.multiusage==1):
mycursor = self.conn.cursor()
sql9 = "update COUPON_SERIAL set COPS_STATUS= 0 where COUPON_ID='" + str(
self.CMB_CouponDes.currentData()) + "' and COPS_SERIAL_type = 0"
mycursor.execute(sql9)
value = randint(0, 1000000000000)
creationDate = str(datetime.today().strftime('%Y-%m-%d'))
mycursor = self.conn.cursor()
sql7 = "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)"
val7 = (
str(self.CMB_CouponDes.currentData()), "HCOP" + bin(value),
CL_userModule.user_name,
self.serial_type,
creationDate, 0,
'1')
mycursor.execute(sql7, val7)
else:
mycursor = self.conn.cursor()
if(int(self.Special)==1):
self.serial_num=0
print("num"+str(self.serial_num))
sql9 = "update COUPON_SERIAL set COPS_STATUS= 0 where COUPON_ID='" + str(
self.CMB_CouponDes.currentData()) + "' and COPS_SERIAL_type = 1"
mycursor.execute(sql9)
if int(self.LE_desc_4.text()) < self.serial_num:
indx = self.CMB_CouponDes.currentData()
sql_select_Query = "SELECT COPS_SERIAL_ID FROM COUPON_SERIAL where COUPON_ID = %s and COPS_STATUS = 1 and COPS_SERIAL_type = 0"
x = (indx,)
mycursor = self.conn.cursor()
mycursor.execute(sql_select_Query, x)
record = mycursor.fetchall()
print(record)
num = 0
for row in range(self.serial_num - int(self.LE_desc_4.text())):
mycursor = self.conn.cursor()
sql9 = "update COUPON_SERIAL set COPS_STATUS= 0 where COUPON_ID='" + str(
self.CMB_CouponDes.currentData()) + "' and COPS_SERIAL_ID = '" + str(
record[num][0]) + "'"
mycursor.execute(sql9)
print(sql9)
num += 1
self.serial_num = int(self.LE_desc_4.text())
else:
for row in range(int(self.LE_desc_4.text()) - self.serial_num):
value = randint(0, 1000000000000)
creationDate = str(datetime.today().strftime('%Y-%m-%d'))
mycursor = self.conn.cursor()
sql7 = "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)"
val7 = (
str(self.CMB_CouponDes.currentData()), "HCOP" + bin(value),
CL_userModule.user_name,
self.serial_type,
creationDate, 0,
'1')
mycursor.execute(sql7, val7)
self.serial_num = int(self.LE_desc_4.text())
if (self.LE_desc_1.text() != self.DescOldValue):
CL_userModule.FN_AddLog(self,'COUPON', 'COP_DESC', self.DescOldValue, self.LE_desc_1.text().strip(), creationDate,
CL_userModule.user_name,self.row,None,None,None,None,mycursor)
elif (self.CMB_CouponStatus.currentIndex() != self.oldstatus):
sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY) VALUES (%s,%s,%s,%s,%s,%s,%s)"
val8 = (self.row, 'COUPON', 'STATUS', self.oldstatus,
str(self.CMB_CouponStatus.currentIndex()),
creationDate,
CL_userModule.user_name)
mycursor.execute(sql8, val8)
elif collections.Counter(self.Qcombo_branch.currentData())== collections.Counter(self.oldlist):
print("the same list")
elif len(collections.Counter(self.Qcombo_branch.currentData())) > len(collections.Counter(self.oldlist)):
print(self.Diff(self.newlist, self.oldlist))
if len(collections.Counter(self.Qcombo_branch.currentData())) > len(collections.Counter(record)):
for row in self.Diff(record, self.newlist):
sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY,ROW_KEY_ID2) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"
val8 = (self.row, 'COUPON_BRANCH', 'STATUS', "null",
"1",
creationDate,
CL_userModule.user_name, row)
mycursor.execute(sql8, val8)
else:
for row in self.Diff(self.oldlist, self.newlist):
sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY,ROW_KEY_ID2) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"
val8 = (self.row, 'COUPON_BRANCH', 'STATUS', "0",
"1",
creationDate,
CL_userModule.user_name, row)
mycursor.execute(sql8, val8)
elif len(collections.Counter(self.Qcombo_branch.currentData())) < len(collections.Counter(self.oldlist)):
print(self.Diff(self.oldlist, self.newlist))
for row in self.Diff(self.oldlist, self.newlist):
sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY,ROW_KEY_ID2) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"
val8 = (self.row, 'COUPON_BRANCH', 'STATUS', "1",
"0",
creationDate,
CL_userModule.user_name, row)
mycursor.execute(sql8, val8)
elif(self.LE_desc_2.text() != self.COPDISCOUNToldVAL):
sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY) VALUES (%s,%s,%s,%s,%s,%s,%s)"
val8 = (self.row, 'COUPON', 'COP_DISCOUNT_VAL', self.COPDISCOUNToldVAL, self.LE_desc_2.text().strip(),
creationDate,
CL_userModule.user_name)
mycursor.execute(sql8, val8)
elif (self.LE_desc_3.text() != self.COPDISCOUNToldprecnt):
sql8 = "INSERT INTO SYS_CHANGE_LOG (ROW_KEY_ID,TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY) VALUES (%s,%s,%s,%s,%s,%s,%s)"
val8 = (self.row, 'COUPON', 'COP_DISCOUNT_PERCENT', self.COPDISCOUNToldprecnt,
self.LE_desc_3.text().strip(),
creationDate,
CL_userModule.user_name)
mycursor.execute(sql8, val8)
db1.connectionCommit(self.conn)
mycursor.close()
QtWidgets.QMessageBox.warning(self, "Done", "Done")
for i in self.FN_GetMathchBranch():
self.branch_list.append(i)
self.FN_getDatabyID()
except:
print(sys.exc_info())
# Todo: method when make coupon use DISCOUNT_VAL
def FN_EnableDiscVal(self):
self.valueType="COP_DISCOUNT_VAL"
self.LE_desc_2.setEnabled(True)
self.LE_desc_3.setEnabled(False)
self.Othertype="COP_DISCOUNT_PERCENT"
# Todo: method when make coupon use DISCOUNT_PERCENT
def FN_EnablePercentage(self):
self.valueType = "COP_DISCOUNT_PERCENT"
self.LE_desc_3.setEnabled(True)
self.LE_desc_2.setEnabled(False)
self.Othertype="COP_DISCOUNT_VAL"
# Todo: method to clear edit text
def FN_Clear(self):
self.LE_desc_1.clear()
self.LE_desc_2.clear()
self.LE_desc_3.clear()
self.LE_desc_4.clear()
self.LE_desc_5.clear()
# Todo: method to get company assigned to coupon
def FN_SELECT_company(self):
indx = self.CMB_CouponDes.currentData()
mycursor = self.conn.cursor()
sql="SELECT COMPANY_ID FROM COUPON_BRANCH where COUPON_ID = %s"
c = (indx,)
mycursor.execute(sql, c)
records = mycursor.fetchall()
mycursor.close()
return records
# Todo: method to get branch assigned to coupon
def FN_SELECT_branch(self):
indx = self.CMB_CouponDes.currentData()
mycursor = self.conn.cursor()
sql="SELECT BRANCH_NO , STATUS FROM COUPON_BRANCH where COUPON_ID = %s"
c = (indx,)
mycursor.execute(sql,c)
records = mycursor.fetchall()
mycursor.close()
return records
# Todo: method to get check company assigned to coupon
def FN_check_company(self, indx):
mycursor = self.conn.cursor()
sql_select_company ="SELECT COMPANY_ID FROM COMPANY"
mycursor.execute(sql_select_company)
record = mycursor.fetchall()
i = 0
for row in record:
for row1 in self.FN_SELECT_company():
if row[0] == row1[0]:
items = self.Qcombo_company.findText(row[0])
for item in range(items + 2):
self.Qcombo_company.setChecked(i)
i = i + 1
mycursor.close()
# Todo: method to get check branch assigned to coupon
def FN_check_branch(self,index):
self.FN_unCheckedALL()
mycursor = self.conn.cursor()
sql_select_branch = "SELECT BRANCH_NO FROM BRANCH"
mycursor.execute(sql_select_branch)
record = mycursor.fetchall()
i = 0
for row in record:
for row1 in self.FN_SELECT_branch():
if row[0] == row1[0]:
items = self.Qcombo_branch.findText(row[0])
for item in range(items +2):
if int(row1[1])==1:
self.Qcombo_branch.setChecked(i)
i = i + 1
mycursor.close()
# Todo: method refresh Qcombo_branch
def FN_unCheckedALL(self):
i=0
for row in CL_userModule.branch:
self.Qcombo_branch.unChecked(i)
i+=1
# Todo: method get branch has this coupon
def FN_GetMathchBranch(self):
indx = self.CMB_CouponDes.currentData()
mycursor = self.conn.cursor()
sql = "SELECT BRANCH_NO FROM COUPON_BRANCH where COUPON_ID = %s and STATUS = 1"
c = (indx,)
mycursor.execute(sql, c)
records = mycursor.fetchall()
mycursor.close()
return records
# Todo: method get diff between two list
def Diff(self,li1, li2):
return list(set(li1) - set(li2)) + list(set(li2) - set(li1))
class CL_user(QtWidgets.QDialog):
dirname = ''
userid = ''
branch_list = []
new_branch_list = []
section_list = []
new_section_list = []
def __init__(self):
super(CL_user, self).__init__()
cwd = Path.cwd()
mod_path = Path(__file__).parent.parent.parent
self.dirname = mod_path.__str__() + '/presentation/authorization_ui'
self.conn = db1.connect()
#Todo: method for load ui of modify user
def FN_LOAD_MODIFY(self):
filename = self.dirname + '/modifyUser.ui'
loadUi(filename, self)
self.CMB_branch = CheckableComboBox(self)
self.CMB_branch.setGeometry(150, 120, 171, 25)
self.CMB_branch.setLayoutDirection(QtCore.Qt.LeftToRight)
self.CMB_branch.setStyleSheet("background-color: rgb(198, 207, 199)")
self.CMB_section = CheckableComboBox(self)
self.CMB_section.setGeometry(150, 170, 171, 25)
self.CMB_section.setLayoutDirection(QtCore.Qt.LeftToRight)
self.CMB_section.setStyleSheet("background-color: rgb(198, 207, 199)")
self.FN_GET_BRANCHES()
self.FN_GET_Section()
self.FN_GET_USERTYPE()
self.CMB_userStatus.addItems(["Active", "Inactive"])
records = self.FN_GET_USERS()
for row in records:
self.CMB_userName.addItems([row[0]])
self.FN_GET_USER()
self.CMB_userName.currentIndexChanged.connect(self.FN_GET_USER)
self.BTN_modifyUser.clicked.connect(self.FN_MODIFY_USER)
#Todo: method for load ui of create User
def FN_LOAD_CREATE(self):
filename = self.dirname + '/createUser.ui'
loadUi(filename, self)
self.setWindowTitle('Users')
self.BTN_createUser.clicked.connect(self.FN_CREATE_USER)
self.CMB_branch = CheckableComboBox(self)
self.CMB_branch.setGeometry(150, 85, 171, 25)
self.CMB_branch.setLayoutDirection(QtCore.Qt.LeftToRight)
self.CMB_branch.setStyleSheet("background-color: rgb(198, 207, 199)")
self.CMB_section = CheckableComboBox(self)
self.CMB_section.setGeometry(150, 130, 171, 25)
self.CMB_section.setEnabled(False)
self.CMB_section.setLayoutDirection(QtCore.Qt.LeftToRight)
self.CMB_section.setStyleSheet("background-color: rgb(198, 207, 199)")
self.FN_GET_BRANCHES()
self.FN_GET_Section()
self.FN_GET_USERTYPE()
self.checkBox.toggled.connect(self.FN_EnableDepartment)
self.CMB_userStatus.addItems(["Active", "Inactive"])
# Set Style
# self.voucher_num.setStyleSheet(label_num)
# self.label_2.setStyleSheet(desc_5)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
#Todo: method for get user type
def FN_GET_USERTYPE(self):
mycursor = self.conn.cursor()
self.CMB_userType.clear()
sql_select_query = "SELECT USER_TYPE_DESC FROM SYS_USER_TYPE where USER_TYPE_STATUS = 1 "
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row in records:
self.CMB_userType.addItems([row[0]])
mycursor.close()
#Todo: method for get all branches
def FN_GET_BRANCHES(self):
mycursor = self.conn.cursor()
self.CMB_branch.clear()
sql_select_query = "SELECT BRANCH_DESC_A ,BRANCH_NO FROM BRANCH where BRANCH_STATUS = 1 "
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row, val in records:
self.CMB_branch.addItem(row, val)
mycursor.close()
#Todo: method for get all sections
def FN_GET_Section(self):
mycursor = self.conn.cursor()
self.CMB_section.clear()
sql_select_query = "SELECT SECTION_DESC ,SECTION_ID FROM SECTION "
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row, val in records:
self.CMB_section.addItem(row, val)
mycursor.close()
#Todo: method for load copy ui
def FN_LOAD_COPY(self):
filename = self.dirname + '/copyUser.ui'
loadUi(filename, self)
records = self.FN_GET_USERS()
for row in records:
self.CMB_userName.addItems([row[0]])
self.CMB_userName1.addItems([row[0]])
self.BTN_copyUser.clicked.connect(self.FN_COPY_USER)
self.CMB_userName.currentIndexChanged.connect(self.FN_ASSIGN_ID)
self.CMB_userName1.currentIndexChanged.connect(self.FN_ASSIGN_ID)
self.FN_ASSIGN_ID()
#Todo: method for get data from user combobox
def FN_ASSIGN_ID(self):
self.user1 = self.CMB_userName.currentText()
self.user2 = self.CMB_userName1.currentText()
self.LB_userID.setText(self.FN_GET_USERID_N(self.user1))
self.LB_userID2.setText(self.FN_GET_USERID_N(self.user2))
#Todo: method to copy user
def FN_COPY_USER(self):
newUser = self.LB_userID2.text()
if self.user1 == self.user2:
QtWidgets.QMessageBox.warning(self, "Error",
"Please enter 2 different users")
else:
mycursor = self.conn.cursor()
mycursor1 = self.conn.cursor()
mycursor2 = self.conn.cursor()
sql_select_query = "select ur.ROLE_ID ,ur.BRANCH_NO ,ur.UR_STATUS " \
"from SYS_USER_ROLE ur inner join SYS_USER u ON u.USER_ID = ur.USER_ID " \
"where u.USER_NAME = %s "
x = (self.user1, )
mycursor.execute(sql_select_query, x)
records = mycursor.fetchall()
mycursor2 = self.conn.cursor()
sql_select_query1 = "delete from SYS_USER_ROLE where USER_ID = '" + newUser + "'"
mycursor2.execute(sql_select_query1)
db1.connectionCommit(self.conn)
mycursor1.execute(
"SELECT max(cast(UR_USER_ROLE_ID AS UNSIGNED)) FROM SYS_USER_ROLE"
)
myresult = mycursor1.fetchone()
creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
id = int(myresult[0]) + 1
for row in records:
mycursor3 = self.conn.cursor()
sql = "INSERT INTO SYS_USER_ROLE (UR_USER_ROLE_ID, USER_ID, ROLE_ID, BRANCH_NO, UR_CREATED_BY, UR_CREATED_ON, UR_CHANGED_BY, UR_CHANGED_ON, UR_STATUS) " \
"VALUES ( %s, %s, %s, %s,%s, %s,%s,%s,%s)"
val = (id, newUser, row[0], row[1], CL_userModule.user_name,
creationDate, '', '', row[2])
print(str(sql))
mycursor3.execute(sql, val)
db1.connectionCommit(self.conn)
print(mycursor3.rowcount, "record inserted.")
id = id + 1
QtWidgets.QMessageBox.information(self, "Success",
"User is copied successfully")
mycursor2.close()
mycursor1.close()
mycursor.close()
self.close()
#Todo: method for get all users and display all data
def FN_GET_USER(self):
user = self.CMB_userName.currentText()
mycursor = self.conn.cursor()
sql_select_query = "select * from SYS_USER where USER_NAME = '" + user + "'"
print(sql_select_query)
mycursor.execute(sql_select_query)
record = mycursor.fetchone()
print(record)
self.LB_userID.setText(record[0])
self.userid = record[0]
self.LE_name.setText(record[2])
self.LE_password.setText(record[3])
self.LE_fullName.setText(record[4])
self.LE_hrId.setText(record[5])
self.CMB_userType.setCurrentText(record[11])
if record[10] == '1':
self.CMB_userStatus.setCurrentText('Active')
else:
self.CMB_userStatus.setCurrentText('Inactive')
print(mycursor.rowcount, "record retrieved.")
mycursor.close()
self.FN_check_branch()
self.FN_check_section()
self.branch_list.clear()
if len(self.CMB_branch.currentData()) > 0:
for i in self.CMB_branch.currentData():
self.branch_list.append(i)
if len(self.CMB_section.currentData()) > 0:
for i in self.CMB_section.currentData():
self.section_list.append(i)
#Todo: method for edit user
def FN_MODIFY_USER(self):
try:
self.id = self.LB_userID.text()
self.name = self.LE_name.text().strip()
self.password = self.LE_password.text().strip()
self.branch = self.CMB_branch.currentData()[0]
self.fullName = self.LE_fullName.text().strip()
self.hrId = self.LE_hrId.text().strip()
self.userType = self.CMB_userType.currentText()
self.status = self.CMB_userStatus.currentText()
if self.status == 'Active':
self.status = 1
else:
self.status = 0
if CL_validation.FN_isEmpty(self.name) or CL_validation.FN_isEmpty(
self.password) or CL_validation.FN_isEmpty(
self.fullName) or CL_validation.FN_isEmpty(self.hrId):
QtWidgets.QMessageBox.warning(
self, "Error", "Please enter all required fields")
else:
if CL_validation.FN_validation_password(
self, self.password) == False:
mycursor = self.conn.cursor()
if len(self.CMB_branch.currentData()) > 0:
for i in self.CMB_branch.currentData():
self.new_branch_list.append(i)
if len(self.CMB_section.currentData()) > 0:
for i in self.CMB_section.currentData():
self.new_section_list.append(i)
changeDate = str(
datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
sql = "UPDATE SYS_USER set USER_NAME= %s , USER_PASSWORD= %s , BRANCH_NO = %s, USER_FULLNAME = %s , USER_HR_ID = %s, USER_CHANGED_ON = %s , USER_CHANGED_BY = %s, USER_STATUS = %s, USERTYPE_ID = %s where USER_id= %s "
val = (self.name, self.password, self.branch,
self.fullName, self.hrId, changeDate,
CL_userModule.user_name, self.status, self.userType,
self.id)
mycursor.execute(sql, val)
if len(self.branch_list) > len(self.new_branch_list):
for row in self.branch_list:
print(row)
if row in self.new_branch_list:
print("found")
else:
print("not found")
mycursor = self.conn.cursor()
sql5 = "update SYS_USER_BRANCH set STATUS= 0 where USER_ID='" + self.userid + "' and BRANCH_NO = '" + row + "'"
mycursor.execute(sql5)
else:
for row in self.new_branch_list:
print(row)
if row in self.branch_list:
print("found")
else:
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT * FROM SYS_USER_BRANCH where BRANCH_NO='"
+ row + "' and USER_ID='" + self.userid +
"'")
record = mycursor.fetchall()
if mycursor.rowcount > 0:
mycursor = self.conn.cursor()
sql8 = "update SYS_USER_BRANCH set STATUS= 1 where USER_ID='" + self.userid + "' and BRANCH_NO = '" + row + "'"
mycursor.execute(sql8)
print(sql8)
else:
mycursor = self.conn.cursor()
sql6 = "INSERT INTO SYS_USER_BRANCH (USER_ID,COMPANY_ID,BRANCH_NO,STATUS) VALUES (%s,%s,%s,%s)"
val6 = (self.userid, '1', row, '1')
mycursor.execute(sql6, val6)
if len(self.section_list) > len(self.new_section_list):
for row in self.section_list:
print(row)
if row in self.new_section_list:
print("found")
else:
print("not found")
mycursor = self.conn.cursor()
sql5 = "update SYS_USER_SECTION set STATUS= 0 where USER_ID='" + self.userid + "' and SECTION_ID = '" + row + "'"
mycursor.execute(sql5)
else:
for row in self.new_section_list:
print(row)
if row in self.section_list:
print("found")
else:
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT * FROM SYS_USER_SECTION where SECTION_ID='"
+ row + "' and USER_ID='" + self.userid +
"'")
record = mycursor.fetchall()
if mycursor.rowcount > 0:
mycursor = self.conn.cursor()
sql8 = "update SYS_USER_SECTION set STATUS= 1 where USER_ID='" + self.userid + "' and SECTION_ID = '" + row + "'"
mycursor.execute(sql8)
print(sql8)
else:
mycursor = self.conn.cursor()
sql6 = "INSERT INTO SYS_USER_SECTION (USER_ID,SECTION_ID,STATUS) VALUES (%s,%s,%s)"
val6 = (self.userid, row, '1')
mycursor.execute(sql6, val6)
mycursor.close()
db1.connectionCommit(self.conn)
print(mycursor.rowcount, "record Modified.")
QtWidgets.QMessageBox.information(
self, "Success", "User is modified successfully")
db1.connectionClose(self.conn)
self.close()
except:
print(sys.exc_info())
#Todo: method for get user data
def FN_GET_USERS(self):
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT USER_NAME USER_ID FROM SYS_USER order by USER_ID asc")
records = mycursor.fetchall()
mycursor.close()
return records
#Todo: method for get user id
def FN_GET_USERID_N(self, user):
mycursor = self.conn.cursor()
sql_select_query = "SELECT USER_ID FROM SYS_USER WHERE USER_NAME = %s "
x = (user, )
mycursor.execute(sql_select_query, x)
myresult = mycursor.fetchone()
return myresult[0]
#Todo: method for create user
def FN_CREATE_USER(self):
try:
sql_select_Query = "select * from SYS_USER where USER_NAME = '" + self.LE_name.text(
) + "' and USER_STATUS = 1"
print(sql_select_Query)
mycursor = self.conn.cursor()
mycursor.execute(sql_select_Query)
print(mycursor.fetchall())
if mycursor.rowcount > 0:
QtWidgets.QMessageBox.warning(self, "Error",
"Username is already exists")
else:
self.name = self.LE_name.text().strip()
self.password = self.LE_password.text().strip()
print(self.CMB_branch.currentData()[0])
self.branch = self.CMB_branch.currentData()[0]
self.fullName = self.LE_fullName.text().strip()
self.hrId = self.LE_hrId.text().strip()
self.userType = self.CMB_userType.currentText()
self.status = self.CMB_userStatus.currentText()
if self.status == 'Active':
self.status = 1
else:
self.status = 0
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT max(cast(USER_ID AS UNSIGNED)) FROM SYS_USER")
myresult = mycursor.fetchone()
if myresult[0] == None:
self.id = "1"
else:
self.id = int(myresult[0]) + 1
creationDate = str(
datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
if CL_validation.FN_isEmpty(
self.name) or CL_validation.FN_isEmpty(
self.password) or CL_validation.FN_isEmpty(
self.fullName) or CL_validation.FN_isEmpty(
self.hrId):
QtWidgets.QMessageBox.warning(
self, "Error", "Please enter all required fields")
else:
if CL_validation.FN_validation_password(
self, self.password) == False:
sql = "INSERT INTO SYS_USER (USER_ID, BRANCH_NO, USER_NAME, USER_PASSWORD, USER_FULLNAME, USER_HR_ID, USER_CREATED_ON, USER_CREATED_BY, USER_CHANGED_ON, USER_CHANGED_BY,USER_STATUS, USERTYPE_ID) VALUES ( %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s)"
val = (self.id, self.branch, self.name, self.password,
self.fullName, self.hrId, creationDate,
CL_userModule.user_name, '', '', self.status,
self.userType)
mycursor.execute(sql, val)
for i in range(len(self.CMB_branch.currentData())):
sql2 = "INSERT INTO SYS_USER_BRANCH (USER_ID, COMPANY_ID, BRANCH_NO, STATUS) VALUES ( %s, %s, %s, %s)"
val = (self.id, '1',
self.CMB_branch.currentData()[i], '1')
mycursor.execute(sql2, val)
if self.checkBox.isChecked():
for i in range(len(
self.CMB_section.currentData())):
sql = "INSERT INTO SYS_USER_SECTION (USER_ID, SECTION_ID, STATUS) VALUES (%s, %s, %s)"
val = (self.id,
self.CMB_section.currentData()[i], '1')
mycursor.execute(sql, val)
mycursor.close()
print(mycursor.rowcount, "record inserted.")
QtWidgets.QMessageBox.information(
self, "Success", "User is created successfully")
db1.connectionCommit(self.conn)
db1.connectionClose(self.conn)
self.close()
except:
print(sys.exc_info())
#Todo: method for change password
def FN_RESET_USER(self):
mycursor = self.conn.cursor()
changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
if CL_validation.FN_isEmpty(self.LE_password.text()):
QtWidgets.QMessageBox.warning(self, "Error",
"Enter Password Please")
elif CL_validation.FN_isEmpty(self.LE_password2.text()):
QtWidgets.QMessageBox.warning(self, "Error", " RePassword Please")
elif CL_validation.FN_validation_password(
self, self.LE_password.text()) == False:
if self.LE_password.text() == self.LE_password2.text():
sql_select_Query = "select * from SYS_USER where USER_NAME = %s and USER_PASSWORD = %s and USER_STATUS = 1"
x = (self.LE_username.text(), self.old_password.text())
mycursor = self.conn.cursor()
mycursor.execute(sql_select_Query, x)
record = mycursor.fetchone()
if mycursor.rowcount > 0:
sql = "UPDATE SYS_USER set USER_PASSWORD= %s , USER_CHANGED_ON = %s , USER_CHANGED_BY = %s where USER_NAME= %s and USER_PASSWORD= %s "
val = (self.LE_password.text(), changeDate,
self.LE_username.text(), self.LE_username.text(),
self.old_password.text())
print(sql)
mycursor.execute(sql, val)
mycursor.close()
db1.connectionCommit(self.conn)
print(mycursor.rowcount, "password changed")
QtWidgets.QMessageBox.information(
self, "Success", "Password is reset successfully")
db1.connectionClose(self.conn)
self.close()
else:
QtWidgets.QMessageBox.warning(
self, "Error", "Incorrect Username and Password")
print("Please Enter Correct Username and Password")
else:
QtWidgets.QMessageBox.warning(
self, "Error", "Please enter 2 different Passwords")
#Todo: method for load reset password ui
def FN_LOAD_RESET(self):
filename = self.dirname + '/resetUserPassword.ui'
loadUi(filename, self)
self.BTN_resetPass.clicked.connect(self.FN_RESET_USER)
#Todo: method for change password
def FN_RESET_USER_MAIN(self):
mycursor = self.conn.cursor()
if CL_validation.FN_isEmpty(self.LE_password.text()):
QtWidgets.QMessageBox.warning(self, "Error",
"Enter Password Please")
elif CL_validation.FN_isEmpty(self.LE_password2.text()):
QtWidgets.QMessageBox.warning(self, "Error", " RePassword Please")
elif CL_validation.FN_validation_password(
self, self.LE_password.text()) == False:
changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
if self.LE_password.text() == self.LE_password2.text():
sql_select_Query = "select * from SYS_USER where USER_NAME = '" + self.LE_username.currentText(
) + "' and USER_STATUS = 1"
mycursor = self.conn.cursor()
mycursor.execute(sql_select_Query)
print(sql_select_Query)
record = mycursor.fetchone()
print(record)
if mycursor.rowcount > 0:
sql = "UPDATE SYS_USER set USER_PASSWORD= %s , USER_CHANGED_ON = %s , USER_CHANGED_BY = %s where USER_NAME= %s"
val = (self.LE_password.text(), changeDate,
self.LE_username.currentText(),
self.LE_username.currentText())
print(sql)
mycursor.execute(sql, val)
mycursor.close()
db1.connectionCommit(self.conn)
print(mycursor.rowcount, "password changed")
QtWidgets.QMessageBox.information(
self, "Success", "Password is reset successfully")
db1.connectionClose(self.conn)
self.close()
else:
QtWidgets.QMessageBox.warning(self, "Error",
"Incorrect Username ")
print("Please Enter Correct Username and Password")
else:
QtWidgets.QMessageBox.warning(
self, "Error", "Please enter 2 different Passwords")
#Todo: method for load reset password ui
def FN_LOAD_RESET_MAIN(self):
filename = self.dirname + '/resetUserPasswordMain.ui'
loadUi(filename, self)
self.FN_GET_User()
self.BTN_resetPass.clicked.connect(self.FN_RESET_USER_MAIN)
#Todo: method for all user name
def FN_GET_User(self):
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute("SELECT USER_NAME FROM SYS_USER")
records = mycursor.fetchall()
for row in records:
self.LE_username.addItems(row)
mycursor.close()
#Todo: method for checked branches assigned to user
def FN_check_branch(self):
self.FN_unCheckedALL()
mycursor = self.conn.cursor()
sql_select_branch = "SELECT BRANCH_NO FROM BRANCH"
mycursor.execute(sql_select_branch)
record = mycursor.fetchall()
i = 0
for row in record:
for row1 in self.FN_SELECT_branch():
if row[0] == row1[0]:
items = self.CMB_branch.findText(row[0])
for item in range(items + 2):
if int(row1[1]) == 1:
self.CMB_branch.setChecked(i)
i = i + 1
mycursor.close()
#Todo: method for get all branches assigned to user
def FN_SELECT_branch(self):
mycursor = self.conn.cursor()
sql = "SELECT BRANCH_NO , STATUS FROM SYS_USER_BRANCH where USER_ID = %s"
c = (self.userid, )
mycursor.execute(sql, c)
records = mycursor.fetchall()
mycursor.close()
return records
#Todo: method to refresh checkable combobox for branch
def FN_unCheckedALL(self):
mycursor = self.conn.cursor()
sql_select_branch = "Select BRANCH_NO from BRANCH where BRANCH_STATUS=1"
mycursor.execute(sql_select_branch)
record = mycursor.fetchall()
print(record)
i = 0
for row in record:
self.CMB_branch.unChecked(i)
i += 1
#Todo: method to enable department
def FN_EnableDepartment(self):
if self.checkBox.isChecked():
self.CMB_section.setEnabled(True)
else:
self.CMB_section.setEnabled(False)
#Todo: method to get section assigned to user and checked it
def FN_check_section(self):
self.FN_unCheckedSection()
mycursor = self.conn.cursor()
sql_select_branch = "SELECT SECTION_ID FROM SECTION"
mycursor.execute(sql_select_branch)
record = mycursor.fetchall()
i = 0
for row in record:
for row1 in self.FN_SELECT_section():
if row[0] == row1[0]:
items = self.CMB_section.findText(row[0])
for item in range(items + 2):
if int(row1[1]) == 1:
self.CMB_section.setChecked(i)
i = i + 1
mycursor.close()
#Todo: method to get section assigned to user
def FN_SELECT_section(self):
mycursor = self.conn.cursor()
sql = "SELECT SECTION_ID , STATUS FROM SYS_USER_SECTION where USER_ID = %s"
c = (self.userid, )
mycursor.execute(sql, c)
records = mycursor.fetchall()
mycursor.close()
return records
#Todo: method to refresh checkable combobox for section
def FN_unCheckedSection(self):
mycursor = self.conn.cursor()
sql_select_branch = "Select SECTION_ID from SECTION where SECTION_STATUS=1"
mycursor.execute(sql_select_branch)
record = mycursor.fetchall()
print(record)
i = 0
for row in record:
self.CMB_section.unChecked(i)
i += 1
class CL_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)