def FN_GET_FORMID(self):
try:
self.form = self.CMB_formName.currentText()
conn = db1.connect()
mycursor = conn.cursor()
sql_select_query = "SELECT FORM_ID FROM SYS_FORM WHERE FORM_DESC = %s "
x = (self.form, )
mycursor.execute(sql_select_query, x)
myresult = mycursor.fetchone()
if mycursor.rowcount > 0:
self.LB_formId.setText(myresult[0])
mycursor.close()
self.CMB_formItemName.activated[str].connect(
self.FN_GET_FORMITEMID)
self.FN_GET_FORMItems()
self.FN_GET_FORMITEMID()
except Exception as err:
print(err)
def FN_GET_FORM_ITEM(self):
conn = db1.connect()
self.LE_desc.clear()
self.FN_GET_FORMITEMID()
self.id = self.LB_formItemID.text()
mycursor = conn.cursor()
sql_select_query = "select ITEM_DESC ,ITEM_STATUS from SYS_FORM_ITEM where ITEM_ID = %s "
x = (self.id, )
mycursor.execute(sql_select_query, x)
record = mycursor.fetchall()
for row in record:
self.LE_desc.setText(row[0])
if row[1] == '1':
self.CMB_formItemStatus.setCurrentText('Active')
else:
self.CMB_formItemStatus.setCurrentText('Inactive')
mycursor.close()
print(mycursor.rowcount, "record retrieved.")
def FN_GET_CUST_NAME(self):
conn = db1.connect()
mycursor = conn.cursor()
no = self.LE_custNo.text().strip()
self.LE_custName.setText('')
sql = "SELECT POSC_NAME FROM Hyper1_Retail.POS_CUSTOMER where POSC_CUST_ID = '" + str(
no) + "'"
mycursor.execute(sql)
myresult = mycursor.fetchone()
if mycursor.rowcount > 0:
self.LE_custName.setText(myresult[0])
print(myresult[0])
mycursor.close()
return self.LE_custName.text()
def FN_CHECK_VALID_BRANCH(self, id):
try:
conn = db1.connect()
mycursor11 = conn.cursor()
sql = "SELECT BRANCH_NO FROM Hyper1_Retail.BRANCH where BRANCH_NO = '" + str(
id) + "'"
# print(sql)
mycursor11.execute(sql)
myresult = mycursor11.fetchone()
if mycursor11.rowcount > 0:
if myresult[0] in CL_userModule.branch[0]:
mycursor11.close()
return True
else:
mycursor11.close()
return False
except (Error, Warning) as e:
print(e)
def FN_MODIFY_CUST(self):
#get customer data
try:
id = self.LE_complainNo.text().strip()
complainType = self.CMB_complainType.currentData()
# city = self.CMB_city.currentData()
# branch = self.CMB_branch.currentData()
# dept = self.CMB_city.currentData()
# sec = self.CMB_branch.currentData()
status = self.CMB_status.currentData()
responsible = self.LE_responsible.text().strip()
details = self.details.toPlainText().strip()
#details
conn = db1.connect()
mycursor = conn.cursor()
changeDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
sql = "update Hyper1_Retail.CUSTOMER_COMPLAINT set CCT_TYPE_ID = %s ,CC_STATUS =%s " \
",CC_RESPONSIBLE = %s ,CC_CHANGED_ON = %s , CC_CHANGED_BY = %s,CC_DETAIL = %s where CC_COMPLAINT_ID = %s"
print(sql)
val = (complainType, status, responsible, changeDate,
CL_userModule.user_name, details, id)
mycursor.execute(sql, val)
mycursor.close()
print(mycursor.rowcount, "record updated.")
QtWidgets.QMessageBox.information(self, "تم", "تم التعديل")
db1.connectionCommit(conn)
self.close()
self.FN_REFRESH_GRID(id)
if self.oldStatus != status:
util.FN_INSERT_IN_LOG("CUSTOMER_COMPLAINT", "status", status,
self.oldStatus, id)
if self.oldResponsible != responsible:
util.FN_INSERT_IN_LOG("CUSTOMER_COMPLAINT", "responsible",
responsible, self.oldResponsible, id)
if str(self.oldComplainType) != str(complainType):
util.FN_INSERT_IN_LOG("CUSTOMER_COMPLAINT", "complainType",
complainType, self.oldComplainType, id)
except Exception as err:
print(err)
def FN_SEARCH_CUSTGP(self):
self.conn1 = db1.connect()
try:
for i in reversed(range(self.Qtable_custGP.rowCount())):
self.Qtable_custGP.removeRow(i)
mycursor = self.conn1.cursor()
name = self.LE_desc.text().strip()
self.custGroup = self.CMB_custGroup.currentText()
if self.custGroup == 'Active':
whereClause = "where CG_Status =1 "
else:
whereClause = "where CG_Status = 0 "
if name != '':
whereClause = whereClause + "and CG_DESC like '%" + str(
name) + "%'"
sql_select_query = "select CG_GROUP_ID, CG_DESC , CG_Status from Hyper1_Retail.CUSTOMER_GROUP " + whereClause + " and CG_DESC !='H1' order by CG_GROUP_ID*1 asc"
#print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row_number, row_data in enumerate(records):
self.Qtable_custGP.insertRow(row_number)
for column_number, data in enumerate(row_data):
item = QTableWidgetItem(str(data))
if column_number == 2:
data = util.FN_GET_STATUS_DESC(str(data))
item = QTableWidgetItem(str(data))
item.setFlags(
QtCore.Qt.ItemFlags(~QtCore.Qt.ItemIsEditable))
self.Qtable_custGP.setItem(row_number, column_number, item)
#self.Qtable_custGP.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
#
self.Qtable_custGP.doubleClicked.connect(self.FN_GET_CUSTGP)
#mycursor.close()
#self.Qtable_custGP.setItem(0, 0, QTableWidgetItem(str('11111')))
except Exception as err:
print(err)
def FN_Create(self):
try:
self.conn = db1.connect()
self.conn.autocommit = False
mycursor = self.conn.cursor()
self.conn.start_transaction()
sql_select_Query = "select * from POS_QUICK_GROUP_POS where COMPANY_ID=%s and BRANCH_NO=%s and POS_NO=%s and GROUP_ID = %s "
x = (self.Qcombo_company.currentData(),
self.Qcombo_branch.currentData(),
self.Qcombo_pos.currentData(),
self.Qcombo_group.currentData())
mycursor.execute(sql_select_Query, x)
record = mycursor.fetchone()
if mycursor.rowcount > 0:
QtWidgets.QMessageBox.warning(self, "خطا",
"المكنه موجود بالفعل")
else:
try:
sql0 = " LOCK TABLES Hyper1_Retail.POS_QUICK_GROUP_POS WRITE "
mycursor.execute(sql0)
sql = "INSERT INTO POS_QUICK_GROUP_POS (COMPANY_ID,BRANCH_NO,POS_NO,GROUP_ID,STATUS)" \
" VALUES (%s, %s, %s, %s, %s) "
val = (self.Qcombo_company.currentData(),
self.Qcombo_branch.currentData(),
self.Qcombo_pos.currentData(),
self.Qcombo_group.currentData(),
self.CMB_Status.currentIndex())
mycursor.execute(sql, val)
sql00 = " UNLOCK tables "
mycursor.execute(sql00)
db1.connectionCommit(self.conn)
mycursor.close()
QtWidgets.QMessageBox.warning(self, "Done", "تم الانشاء")
except:
print(sys.exc_info())
self.conn.rollback()
finally:
if self.conn.is_connected():
mycursor.close()
self.conn.close()
print("connection is closed")
except:
print(sys.exc_info())
def FN_getDatabyID(self):
try:
self.conn = db1.connect()
mycursor = self.conn.cursor()
indx = self.CMB_CouponDes.currentData()
self.labe_id.setText(str(indx))
sql_select_Query = "SELECT * FROM VOUCHER where GV_ID = %s "
x = (indx, )
mycursor.execute(sql_select_Query, x)
record = mycursor.fetchone()
self.LE_desc_2.setValue(float(record[4]))
datefrom = record[12]
xfrom = datefrom.split("-")
self.dfrom = QDate(int(xfrom[0]), int(xfrom[1]), int(xfrom[2]))
self.Qdate_from.setDate(self.dfrom)
self.dfrom = QDate(int(xfrom[0]), int(xfrom[1]), int(xfrom[2]))
dateto = record[14]
xto = dateto.split("-")
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_to.setDate(d)
if (record[18] == "1"):
self.checkBox_Multi.setChecked(True)
elif (record[17] == "1"):
self.checkBox_rechange.setChecked(True)
elif (record[16] == "1"):
self.checkBox_refundable.setChecked(True)
sql_select_Query3 = "select * from POS_CUSTOMER where POSC_CUST_ID ='" + str(
record[19]) + "'"
print(sql_select_Query3)
mycursor.execute(sql_select_Query3)
record3 = mycursor.fetchone()
self.LE_desc_5.setText(str(record3[0]))
self.desc_13.setText(str(record3[3]))
sql_select_Query2 = "SELECT * from SPONSOR where SPONSOR_ID=( SELECT SPONSER_ID FROM VOUCHER_SPONSOR where GV_ID = '" + str(
record[0]) + "')"
print(sql_select_Query2)
mycursor.execute(sql_select_Query2)
record2 = mycursor.fetchone()
self.LE_desc_6.setText(record2[2])
mycursor.close()
except:
print(sys.exc_info())
def FN_MODIFY_CUSTGP(self):
self.conn1 = db1.connect()
if len(self.Qtable_custGP.selectedIndexes()) > 0:
rowNo = self.Qtable_custGP.selectedItems()[0].row()
id = self.LB_custGpId.text().strip()
desc_old = self.Qtable_custGP.item(rowNo, 1).text()
desc = self.LE_desc.text().strip()
custGroup = self.CMB_custGroup.currentText()
if custGroup == 'Active':
status = 1
else:
status = 0
#
error = 0
if self.desc == '':
QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء إدخال الاسم")
else:
if desc != desc_old:
if self.FN_CHECK_DUP_NAME(desc, id) != False:
QtWidgets.QMessageBox.warning(self, "خطأ",
"الاسم مكرر")
error = 1
if error != 1:
mycursor = self.conn1.cursor()
changeDate = str(
datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
sql = "update Hyper1_Retail.CUSTOMER_GROUP set CG_Status= %s ,CG_DESC = %s ,CG_CHANGED_ON=%s , CG_CHANGED_BY =%s where CG_GROUP_ID = %s"
val = (status, desc, changeDate, CL_userModule.user_name,
id)
mycursor.execute(sql, val)
#mycursor.close()
#
print(mycursor.rowcount, "record updated.")
QtWidgets.QMessageBox.information(self, "نجاح",
"تم التعديل")
db1.connectionCommit(self.conn1)
self.FN_GET_CUSTGPS()
self.FN_CLEAR_FEILDS()
else:
QtWidgets.QMessageBox.warning(self, "خطأ",
"برجاء اختيار السطر المراد تعديله ")
def FN_DISPLAY_PRIVILAGE(self):
self.conn = db1.connect()
self.w1.clear()
self.w1.setRowCount(0)
mycursor = self.conn.cursor()
sql_select_query = "SELECT * FROM Hyper1_Retail.SYS_CKECK_LIST"
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
records = list(dict.fromkeys(records))
mycursor.close()
for row_number, row_data in enumerate(records):
self.w1.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.w1.setItem(row_number, column_number, QTableWidgetItem( str( data ) ) )
val = self.w1.item(row_number, 5).text()
self.w1.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
header_labels = ['List ID', 'Name', 'Notes', 'Created Date', 'Created By', 'Status']
self.w1.setHorizontalHeaderLabels(header_labels)
self.w1.doubleClicked.connect(self.Fn_Get_selected_row)
def FN_CHECK_REPEATED_NATIONALID(self, nationalID, id):
try:
conn = db1.connect()
mycursor = conn.cursor()
# get max id
mycursor.execute(
"SELECT * FROM Hyper1_Retail.POS_CUSTOMER where POSC_NATIONAL_ID ='"
+ nationalID + "' and POSC_CUST_ID != '" + id + "'")
myresult = mycursor.fetchone()
if myresult[0] == None:
mycursor.close()
return True
else:
mycursor.close()
return False
except Exception as err:
print(err)
def FN_LOAD_MODIFY(self):
try:
#print("id is ", id)
filename = self.dirname + '/customerService_modify.ui'
loadUi(filename, self)
#get company id
conn = db1.connect()
#mycursor = conn.cursor()
#mycursor.close()
records = util.FN_GET_COMPANIES()
for row, val in records:
self.CMB_company.addItem(row, val)
records = util.FN_GET_DEPARTMENTS()
for row, val in records:
self.CMB_department.addItem(row, val)
self.CMB_status.addItem("Created", '0')
self.CMB_status.addItem("Finished", '1')
self.CMB_status.addItem("Inprogress", '2')
records = self.FN_GET_COMPLAIN_TYPE()
for row, val in records:
self.CMB_complainType.addItem(row, val)
self.CMB_department.activated.connect(self.FN_GET_SECTIONS)
#self.LE_custNo.textChanged.connect(self.FN_GET_CUST)
#self.Rbtn_custNo.clicked.connect(self.onClicked)
#self.Rbtn_complainNo.clicked.connect(self.onClicked)
self.btn_modify.clicked.connect(self.FN_MODIFY_CUST)
self.Qbtn_search.clicked.connect(self.FN_SEARCH_CUST_SERVICE)
#self.setFixedWidth(723)
#self.setFixedHeight(633)
css_path = Path(__file__).parent.parent.parent
path = css_path.__str__() + '/presentation/Themes/Style.css'
self.setStyleSheet(open(path).read())
except Exception as err:
print(err)
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 FN_GET_POINTS(self):
self.conn = db1.connect()
try:
for i in reversed(range(self.Qtable_point.rowCount())):
self.Qtable_point.removeRow(i)
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT POINTS_ID,POINTS_QTY,POINTS_VALUE,POINTS_VALID_FROM,POINTS_VALID_TO FROM Hyper1_Retail.LOYALITY_POINT order by POINTS_ID*1 asc")
records = mycursor.fetchall()
for row_number, row_data in enumerate(records):
self.Qtable_point.insertRow(row_number)
for column_number, data in enumerate(row_data):
item = QTableWidgetItem(str(data))
self.Qtable_point.setItem(row_number, column_number, item)
# self.Qtable_redeemTp.doubleClicked.connect(self.FN_GET_CUSTGP)
# mycursor.close()
except Exception as err:
print(err)
def FN_LOAD_REDEEM_ITEMS(self):
conn = db1.connect()
mycursor = conn.cursor()
self.CMB_redeemItem.clear()
branch = []
for id, name in CL_userModule.branch:
branch.append(id)
branch_list_tuple = tuple(branch)
sql = "select POS_GTIN_DESC_A ,REDEEM_ITEM.POS_GTIN from Hyper1_Retail.REDEEM_ITEM Inner join " \
"POS_ITEM on REDEEM_ITEM.POS_GTIN= POS_ITEM.POS_GTIN where" \
" REDEEM_VALID_FROM <= %s and REDEEM_VALID_TO >= %s and REDEEM_STATUS = '1' and BRANCH_NO in {}".format(branch_list_tuple)
print(sql)
currentDate = str(datetime.today().strftime('%Y-%m-%d'))
x = (currentDate, currentDate)
mycursor.execute(sql, x)
myresult = mycursor.fetchall()
myresult = list(dict.fromkeys(myresult))
for row, val in myresult:
self.CMB_redeemItem.addItem(row, val)
mycursor.close()
def FN_GET_SECTIONS(self):
conn = db1.connect()
mycursor = conn.cursor()
self.CMB_section.clear()
dept = self.CMB_department.currentText()
sql_select_query = "SELECT SECTION_DESC ,SECTION_ID FROM Hyper1_Retail.SECTION s inner join Hyper1_Retail.DEPARTMENT d ON " \
"d.`DEPARTMENT_ID` = s.`DEPARTMENT_ID`" \
"where SECTION_STATUS = 1 and `DEPARTMENT_DESC`= '"+dept+"'"
mycursor.execute( sql_select_query )
records = mycursor.fetchall()
if mycursor.rowcount >0 :
for row, val in records:
for sec in CL_userModule.section:
if str(val) in sec:
self.CMB_section.addItem(row, val)
self.FN_GET_BMCLEVEL4()
mycursor.close()
def FN_SEARCH_CUSTTP(self):
try:
for i in reversed(range(self.Qtable_custTP.rowCount())):
self.Qtable_custTP.removeRow(i)
self.conn = db1.connect()
mycursor = self.conn.cursor()
name = self.LE_desc.text().strip()
self.custType = self.CMB_custType.currentText()
if self.custType == 'Active':
whereClause = "where LOYCT_STATUS =1 "
else:
whereClause = "where LOYCT_STATUS = 0 "
if name != '':
whereClause = whereClause + "and LOYCT_DESC like '%" + str(
name) + "%'"
whereClause = whereClause + " and LOYCT_TYPE_ID != 'H1'"
sql_select_query = "select LOYCT_TYPE_ID,LOYCT_DESC , LOYCT_POINTS_TO_PROMOTE,LOYCT_TYPE_NEXT,LOYCT_STATUS from Hyper1_Retail.LOYALITY_CUSTOMER_TYPE " + whereClause + "order by LOYCT_TYPE_ID*1 asc"
#print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row_number, row_data in enumerate(records):
self.Qtable_custTP.insertRow(row_number)
for column_number, data in enumerate(row_data):
item = QTableWidgetItem(str(data))
if column_number == 3:
data = util.FN_GET_STATUS_DESC(str(data))
item = QTableWidgetItem(str(data))
if column_number == 2 and row_number != 0:
data = self.FN_GET_NEXTlEVEL_DESC(str(data))
item = QTableWidgetItem(str(data))
item.setFlags(
QtCore.Qt.ItemFlags(~QtCore.Qt.ItemIsEditable))
self.Qtable_custTP.setItem(row_number, column_number,
QTableWidgetItem(item))
mycursor.close()
#self.Qtable_custTP.doubleClicked.connect(self.FN_GET_CUSTTP)
except Exception as err:
print(err)
def FN_SEARCH_REDEEMTP(self):
self.conn1 = db1.connect()
try:
for i in reversed(range(self.Qtable_redeemTp.rowCount())):
self.Qtable_redeemTp.removeRow(i)
mycursor = self.conn1.cursor()
name = self.LE_desc.text().strip()
redeemTp = self.CMB_redeemType.currentText()
if redeemTp == 'Active':
whereClause = "where REDEEMT_STATUS =1 "
else:
whereClause = "where REDEEMT_STATUS = 0 "
if name != '':
whereClause = whereClause + "and REDEEMT_DESC like '%" + str(
name) + "%'"
sql_select_query = "select REDEEMT_TYPE_ID,REDEEMT_DESC,REDEEMT_STATUS from Hyper1_Retail.REDEEM_TYPE " + whereClause + " order by REDEEMT_TYPE_ID*1 asc"
#print(sql_select_query)
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row_number, row_data in enumerate(records):
self.Qtable_redeemTp.insertRow(row_number)
for column_number, data in enumerate(row_data):
item = QTableWidgetItem(str(data))
if column_number == 2:
data = util.FN_GET_STATUS_DESC(str(data))
item = QTableWidgetItem(str(data))
item.setFlags(
QtCore.Qt.ItemFlags(~QtCore.Qt.ItemIsEditable))
self.Qtable_redeemTp.setItem(row_number, column_number,
item)
except Exception as err:
print(err)
def FN_MODIFY_CUSTCD(self):
if self.error != 1:
self.conn = db1.connect()
mycursor = self.conn.cursor()
no = self.LE_custNo.text().strip()
name = self.LE_custName.text().strip()
if len(name) > 0:
print(len(no))
sql = "update `Hyper1_Retail`.`POS_CUSTOMER_CARD` set CARD_STATUS = '0' where CARD_SERIAL = %s "
val = (self.card_serial, )
mycursor.execute(sql, val)
mycursor.close()
#util.FN_INSERT_IN_LOG("POS_CUSTOMER_CARD", "status", '0', '0', self.card_serial)
QtWidgets.QMessageBox.information(self, "تم", "تم التعديل")
db1.connectionCommit(self.conn)
else:
if len(no) > 0:
QtWidgets.QMessageBox.warning(self, "خطأ",
" رقم العميل غير موجود")
else:
QtWidgets.QMessageBox.warning(self, "خطأ",
"برجاء إدخال رقم العميل")
def FN_CREATE_VOUCHER(self):
try:
self.conn = db1.connect()
mycursor = self.conn.cursor()
creationDate = str(datetime.today().strftime('%Y-%m-%d'))
if len( self.LE_desc.text().strip()) == 0 or len(self.LE_value.text().strip())== 0 or len(self.LE_maxCount.text().strip() )== 0:
QtWidgets.QMessageBox.warning(self, "خطا", "اكمل العناصر الفارغه")
else:
desc = self.LE_desc.text().strip()
sql_select_Query = "select * from `Hyper1_Retail`.`PROMOTIONAL_VOUCHER` where PROMV_VOUCHER_DESC = %s"
x = (desc,)
mycursor.execute(sql_select_Query, x)
record = mycursor.fetchone()
if mycursor.rowcount > 0:
QtWidgets.QMessageBox.warning(self, "خطا", "الاسم موجود بالفعل")
elif self.Qdate_to.dateTime() < self.Qdate_from.dateTime():
QtWidgets.QMessageBox.warning(self, "Error",
"تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء")
else:
sql = "INSERT INTO `Hyper1_Retail`.`PROMOTIONAL_VOUCHER` (`PROMV_VOUCHER_DESC`,`PROMV_VOUCHER_VAL`,`PROMV_MAX_COUNT`,`PROMV_CREATED_BY`,`PROMV_CREATED_ON`,`PROMV_VALID_FROM`,`PROMV_VALID_TO`,`PROMV_STATUS`)VALUES " \
"('"+self.LE_desc.text().strip()+"',"+self.LE_value.text().strip()+ ","+self.LE_maxCount.text().strip()+ ",'" +CL_userModule.user_name+"','"+ creationDate+"','"+self.Qdate_from.dateTime().toString('yyyy-MM-dd')+"','"+self.Qdate_to.dateTime().toString('yyyy-MM-dd')+"','0')"
print (sql)
mycursor.execute(sql)
db1.connectionCommit(self.conn)
mycursor.execute("SELECT * FROM `Hyper1_Retail`.`PROMOTIONAL_VOUCHER` Where PROMV_VOUCHER_DESC = '" + desc + "'")
c = mycursor.fetchone()
id = c[0]
QtWidgets.QMessageBox.information(self, "Done", "رقم قسيمه الشراء هو " + str(id))
self.voucher_num.setText(str(id))
mycursor.close()
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_INSERT_IN_LOG(tableName,
fieldName,
newValue,
oldValue,
pk1,
pk2=None,
pk3=None,
pk4=None,
pk5=None):
try:
conn = db1.connect()
mycursor = conn.cursor()
changeDate = str(datetime.today().strftime('%Y-%m-%d'))
sql = "insert into Hyper1_Retail.SYS_CHANGE_LOG (TABLE_NAME,FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,CHANGED_ON,CHANGED_BY,ROW_KEY_ID,ROW_KEY_ID2,ROW_KEY_ID3,ROW_KEY_ID4,ROW_KEY_ID5) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
val = (tableName, fieldName, oldValue, newValue, changeDate,
CL_userModule.user_name, pk1, pk2, pk3, pk4, pk5)
mycursor.execute(sql, val)
mycursor.close()
db1.connectionCommit(conn)
except Exception as err:
print(err)
def FN_SEARCH_BARCODES(self):
# self.Qbtn_search.setEnabled(False)
self.tableWidget.clearSelection() # clear selection
self.QcheckBox_all.setChecked(False)
self.tableWidget.setRowCount(0)
self.tableWidget.setColumnCount(5)
# self.tableWidget.setHorizontalHeaderLabels(['DEPARTMENT_ID', 'DEPARTMENT_DESC', 'DEPARTMENT_STATUS', 'test'])
#
self.tableWidget.setHorizontalHeaderLabels(
['الباركود', 'رقم الصنف', 'القسم الفرعى', 'وحدة القياس', 'الوصف'])
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute(
" SELECT POS_GTIN , POS_ITEM_NO , BMC_ID , POS_UOM, POS_GTIN_DESC_A from Hyper1_Retail.POS_ITEM "
)
# mycursor.execute("SELECT * from Hyper1_Retail.POS_ITEM ")
# print(self.query)
records = mycursor.fetchall()
# print(records)
headers = []
for row_number, row_data in enumerate(records):
self.tableWidget.setEditTriggers(
QtWidgets.QTableWidget.NoEditTriggers)
headers.append(row_data)
self.tableWidget.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.tableWidget.setItem(row_number, column_number,
QTableWidgetItem(str(data)))
self.tableWidget.resizeColumnsToContents(
) # item(0, 1).EditTriggers
self.tableWidget.setSortingEnabled(True)
# self.tableWidget.wordWrap()
self.tableWidget.setCornerButtonEnabled(False)
mycursor.close()
self.iterate()
def FN_REFRESH_DATA_GRID(self):
try:
self.Qline_barcode.setEnabled(True)
self.Qcombo_group3.show()
self.Qcombo_group4.show()
self.CMB_branch.hide()
self.CMB_company.hide()
for i in reversed(range(self.Qtable_redeem.rowCount())):
self.Qtable_redeem.removeRow(i)
conn = db1.connect()
mycursor = conn.cursor()
sql_select_query = "select POS_GTIN, COMPANY_ID,BRANCH_NO,REDEEM_POINTS_QTY,REDEEM_VALID_FROM,REDEEM_VALID_TO,REDEEM_STATUS from Hyper1_Retail.REDEEM_ITEM "
mycursor.execute(sql_select_query)
records = mycursor.fetchall()
for row_number, row_data in enumerate(records):
self.Qtable_redeem.insertRow(row_number)
for column_number, data in enumerate(row_data):
if column_number == 6:
data = util.FN_GET_STATUS_DESC(str(data))
elif column_number == 1:
data = util.FN_GET_COMP_DESC(str(data))
elif column_number == 2:
data = util.FN_GET_BRANCH_DESC(str(data))
self.Qtable_redeem.setItem(row_number, column_number,
QTableWidgetItem(str(data)))
mycursor.close()
except (Error, Warning) as e:
print(e)
self.Qtable_redeem.setEditTriggers(
QtWidgets.QTableWidget.NoEditTriggers)
def FN_Create(self):
self.conn = db1.connect()
self.conn.autocommit = False
mycursor = self.conn.cursor()
self.conn.start_transaction()
try:
sql0 = " LOCK TABLES Hyper1_Retail.POS_WORKING_DAY WRITE "
mycursor.execute(sql0)
sql = "INSERT INTO POS_WORKING_DAY (COMPANY_ID,BRANCH_NO,WORKING_DATE,START_DATE,START_BY," \
"END_DATE,END_BY,DAY_STATUS)" \
" VALUES (%s, %s, %s,%s, %s, %s, %s, %s) "
val = (
self.Qcombo_company.currentData(),self.Qcombo_branch.currentData(),
self.Qdate_work.dateTime().toString('yyyy-MM-dd'),
self.Qdate_from.dateTime().toString('yyyy-MM-dd'),
CL_userModule.user_name,
self.Qdate_to.dateTime().toString('yyyy-MM-dd'),
CL_userModule.user_name,
self.CMB_Status.currentIndex())
mycursor.execute(sql, val)
sql00 = " UNLOCK tables "
mycursor.execute(sql00)
db1.connectionCommit(self.conn)
mycursor.close()
QtWidgets.QMessageBox.warning(self, "Done", "تم الانشاء")
except:
print(sys.exc_info())
self.conn.rollback()
finally:
if self.conn.is_connected():
mycursor.close()
self.conn.close()
print("connection is closed")
def FN_GET_VOUCHER(self):
try :
desc = self.CMB_PromVoucher.currentText()
if self.status == '':
print("ee")
self.LE_desc.setText(desc)
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT PROMV_VOUCHER_ID ,`PROMV_VOUCHER_VAL`,`PROMV_MAX_COUNT`,`PROMV_VALID_FROM`,`PROMV_VALID_TO`,`PROMV_STATUS` from `Hyper1_Retail`.`PROMOTIONAL_VOUCHER` where PROMV_VOUCHER_DESC = '"+desc+"'")
records = mycursor.fetchone()
self.voucher_id.setText(str(records[0]))
self.LE_value.setValue(records[1])
self.LE_maxCount.setValue(records[2])
#for logging
self.oldDesc = desc
self.oldValue=records[1]
self.oldMaxVal = records[2]
self.oldValidFrom = records[3]
self.oldValidTo = records[4]
xto = records[3].split("-")
print(xto)
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_from.setDate(d)
xto1 = records[4].split("-")
d1 = QDate(int(xto1[0]), int(xto1[1]), int(xto1[2]))
self.Qdate_to.setDate(d1)
print(xto)
status = util.FN_GET_STATUS_DESC(str(records[5]))
self.LE_PromVoucherStatus.setText(status)
mycursor.close()
except Exception as err:
print(err)
def FN_SEARCH_CUST_ALL(self):
#print('in search' +var)
# self.Qtable_customer.clearcontents()
#self.Qbtn_search.setEnabled(False)
try:
for i in reversed(range(self.Qtable_customer.rowCount())):
self.Qtable_customer.removeRow(i)
conn = db1.connect()
mycursor = conn.cursor()
orderClause = " order by POSC_CUST_ID*1 asc"
self.sql_select_query = "select POSC_CUST_ID 'رقم العميل',POSC_NAME 'اسم العميل',LOYCT_TYPE_ID 'مجموعه العملاء',POSC_PHONE 'رقم الهاتف', POSC_MOBILE 'الموبيل',POSC_JOB 'الوطيفه' , POSC_ADDRESS 'العنوان',POSC_CITY 'المدينه' " \
",POSC_DISTICT 'المجاوره',POSC_BUILDING 'المبنى',POSC_FLOOR 'الطابق',POSC_EMAIL 'الإيميل',POSC_STATUS 'حاله العميل' from Hyper1_Retail.POS_CUSTOMER " + orderClause
# print(sql_select_query)
mycursor.execute(self.sql_select_query)
records = mycursor.fetchall()
for row_number, row_data in enumerate(records):
self.Qtable_customer.insertRow(row_number)
for column_number, data in enumerate(row_data):
if column_number == 12:
data = util.FN_GET_STATUS_DESC(str(data))
elif column_number == 2:
data = util.FN_GET_CUSTTP_DESC(str(data))
elif column_number == 7:
data = util.FN_GET_CITY_DESC(str(data))
elif column_number == 8:
data = util.FN_GET_DISTRICT_DESC(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_LOAD_DISPLAY(self):
try:
filename = self.dirname + '/customer_funds.ui'
loadUi(filename, self)
conn = db1.connect()
mycursor = conn.cursor()
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("-")
print(xto)
d = QDate(int(xto[0]), int(xto[1]), int(xto[2]))
self.Qdate_from.setDate(d)
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_CUSTTPS(self):
for i in reversed(range(self.Qtable_custTP.rowCount())):
self.Qtable_custTP.removeRow(i)
self.conn = db1.connect()
mycursor = self.conn.cursor()
mycursor.execute(
"SELECT LOYCT_TYPE_ID, LOYCT_DESC, LOYCT_POINTS_TO_PROMOTE, LOYCT_TYPE_NEXT, LOYCT_STATUS FROM Hyper1_Retail.LOYALITY_CUSTOMER_TYPE where LOYCT_TYPE_ID != 'H1' order by LOYCT_TYPE_ID*1 asc"
)
records = mycursor.fetchall()
print(records)
for row_number, row_data in enumerate(records):
self.Qtable_custTP.insertRow(row_number)
for column_number, data in enumerate(row_data):
item = QTableWidgetItem(str(data))
if column_number == 4:
data = util.FN_GET_STATUS_DESC(str(data))
item = QTableWidgetItem(str(data))
if column_number == 3 and row_number != 0:
data = self.FN_GET_NEXTlEVEL_DESC(str(data))
item = QTableWidgetItem(str(data))
item.setFlags(QtCore.Qt.ItemFlags(~QtCore.Qt.ItemIsEditable))
self.Qtable_custTP.setItem(row_number, column_number,
QTableWidgetItem(item))
