def FN_VALIDATE_FIELDS(self):
no = self.LE_custNo.text().strip()
name = self.LE_custName.text().strip()
phone = self.LE_custPhone.text().strip()
error = 0
if name == '' or phone == '':
QtWidgets.QMessageBox.warning(self, "خطأ",
"برجاء إدخال جميع البيانات")
error = 1
else:
ret = CL_validation.FN_validation_int(phone)
if ret == False:
QtWidgets.QMessageBox.warning(self, "خطأ",
"رقم الموبايل غير صحيح")
error = 1
ret = CL_validation.FN_validation_mobile(phone)
if ret == 3:
QtWidgets.QMessageBox.warning(
self, "خطأ", "رقم الموبايل يجب أن يكون 11 رقم")
error = 1
elif ret == 2:
QtWidgets.QMessageBox.warning(self, "خطأ",
"رقم الموبايل يجب أن يبدأ ب 01")
error = 1
# ret = util.FN_VALIDATE_CUST(no)
# if ret == False:
# QtWidgets.QMessageBox.warning(self, "خطأ", "رقم العميل غير صحيح")
# error = 1
return error
def FN_VALIDATE_FIELDS(self):
self.name = self.LE_name.text().strip()
self.phone = self.lE_phone.text().strip()
self.mobile = self.lE_mobile.text().strip()
self.building = self.LE_building.text().strip()
self.floor = self.LE_floor.text().strip()
self.email = self.LE_email.text().strip()
self.company = self.LE_company.text().strip()
self.workPhone = self.LE_workPhone.text().strip()
self.workAddress = self.LE_workAddress.text().strip()
error = 0
if self.name == '' or self.mobile == '' or self.job == '' or self.address == '' or self.building == '' \
or self.floor == '' or self.email == '':
QtWidgets.QMessageBox.warning(self, "خطأ",
"برجاء إدخال جميع البيانات")
error = 1
return error
ret = CL_validation.FN_validation_int(self.phone)
if ret == False:
QtWidgets.QMessageBox.warning(self, "خطأ", "رقم التليفون غير صحيح")
error = 1
ret = CL_validation.FN_validation_mobile(self.mobile)
if ret == 3:
QtWidgets.QMessageBox.warning(self, "خطأ",
"رقم الموبايل يجب أن يكون 11 رقم")
error = 1
elif ret == 2:
QtWidgets.QMessageBox.warning(self, "خطأ",
" رقم الموبايل يجب أن يبدأ ب 01")
error = 1
ret = self.FN_CHECK_REPEATED_MOBILE(self.mobile)
if ret == False:
QtWidgets.QMessageBox.warning(self, "خطأ", "موبايل مكرر ")
error = 1
ret = CL_validation.FN_validation_int(self.workPhone)
if ret == False:
QtWidgets.QMessageBox.warning(self, "خطأ", "رقم هاتف غير صحيح")
error = 1
ret = CL_validation.FN_valedation_mail(self.email)
if ret == False:
QtWidgets.QMessageBox.warning(self, "خطأ", "إيميل غير صحسح")
error = 1
return error
def FN_CREATE_Parameters(self):
sql_select_Query = "SELECT * FROM Hyper1_Retail.SYS_CONFIG_PARAMETERS where PARAMETER_DESC = '"+self.LE_name.text()+"' and 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", "Parameter is already exists")
else:
self.name = self.LE_name.text().strip()
self.Value = self.LE_Value.text().strip()
self.D_Value = self.LE_D_Value.text().strip()
self.Notes = self.LE_Notes.text().strip()
self.status = self.CMB_Status.currentText()
if self.status == 'Active':
self.status = 1
else:
self.status = 0
mycursor = self.conn.cursor()
# get max userid
mycursor.execute("SELECT max(cast(PARAMETER_ID AS UNSIGNED)) FROM SYS_CONFIG_PARAMETERS")
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.Value) or CL_validation.FN_isEmpty(
self.D_Value):
QtWidgets.QMessageBox.warning(self, "Error", "Please enter all required fields")
else:
sql = "INSERT INTO SYS_CONFIG_PARAMETERS (PARAMETER_ID, PARAMETER_DESC, PARAMETER_VALUE, DEFAULT_VALUE, NOTES, CHANGED_ON, CHANGED_BY, STATUS) VALUES ( %s, %s, %s, %s,%s, %s, %s, %s)"
val = (
self.id, self.name, self.Value, self.D_Value, self.Notes, creationDate,
CL_userModule.user_name,self.status)
mycursor.execute(sql, val)
mycursor.close()
print(mycursor.rowcount, "Record inserted.")
QtWidgets.QMessageBox.information(self, "Success", "Parameter is created successfully")
db1.connectionCommit(self.conn)
db1.connectionClose(self.conn)
self.FN_DISPLAY_PRIVILAGE()
def FN_CREATEList(self):
if len(self.Qcombo_List.currentData()) == 0:
QtWidgets.QMessageBox.warning(self, "خطا", "اكمل العناصر الفارغه")
sql_select_Query = "SELECT * FROM Hyper1_Retail.SYS_CKECK_LIST_POS where STATUS = 2"
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", "List is already exists")
else:
self.name = self.LE_name.text().strip()
# self.Notes = self.LE_Notes.text().strip()
self.status = self.CMB_Status.currentText()
if self.status == 'Active':
self.status = 1
else:
self.status = 0
mycursor = self.conn.cursor()
# get max userid
# mycursor.execute("SELECT max(cast(LIST_ID AS UNSIGNED)) FROM Hyper1_Retail.SYS_CKECK_LIST")
# 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):
QtWidgets.QMessageBox.warning(self, "Error", "Please enter all required fields")
else:
#sql = "INSERT INTO Hyper1_Retail.SYS_CKECK_LIST_POS (POS_NO, Company_ID,BRANCH_NO,LIST_ID , CHANGED_ON, CHANGED_BY, STATUS) VALUES ( %s, %s, %s, %s,%s, %s, %s)"
#val = (
# self.name, self.Notes, creationDate,
# CL_userModule.user_name, self.status)
#mycursor.execute(sql, val)
for i in range(len(self.Qcombo_Comapny.currentData())):
for j in range(len(self.Qcombo_Branch.currentData())):
for x in range(len(self.Qcombo_List.currentData())):
sql3 = "INSERT INTO Hyper1_Retail.SYS_CKECK_LIST_POS (POS_NO, Company_ID,BRANCH_NO,LIST_ID , CHANGED_ON, CHANGED_BY, STATUS) VALUES ( %s, %s, %s, %s,%s, %s, %s)"
val3 = (self.name,
self.Qcombo_Comapny.currentData()[i],
self.Qcombo_Branch.currentData()[j],
self.Qcombo_List.currentData()[x],
creationDate,
CL_userModule.user_name, self.status)
mycursor.execute(sql3, val3)
mycursor.close()
print(mycursor.rowcount, "Record inserted.")
QtWidgets.QMessageBox.information(self, "Success", "List is created successfully")
db1.connectionCommit(self.conn)
db1.connectionClose(self.conn)
self.FN_DISPLAY_PRIVILAGE()
def FN_Update_Parameters(self):
self.name = self.LE_name.text().strip()
self.Value = self.LE_Value.text().strip()
self.D_Value = self.LE_D_Value.text().strip()
self.Notes = self.LE_Notes.text().strip()
self.status = self.CMB_Status.currentText()
if self.status == 'Active':
self.status = 1
else:
self.status = 0
creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
if CL_validation.FN_isEmpty(self.name) or CL_validation.FN_isEmpty(
self.Value) or CL_validation.FN_isEmpty(
self.D_Value):
QtWidgets.QMessageBox.warning(self, "Error", "Please enter all required fields")
else:
try:
rowNo = self.w1.selectedItems()[0].row()
desc = self.w1.item(rowNo, 1).text()
if self.LE_name.text().strip() != desc:
QtWidgets.QMessageBox.warning(self, "Error", "Parameter Name Can't be change")
return
else:
mycursor = self.conn.cursor()
sql = "Update SYS_CONFIG_PARAMETERS set PARAMETER_VALUE = %s, DEFAULT_VALUE = %s, NOTES = %s, CHANGED_ON = %s, CHANGED_BY = %s, STATUS = %s where PARAMETER_DESC = %s"
val = (self.Value, self.D_Value, self.Notes, creationDate,
CL_userModule.user_name,self.status,self.name)
mycursor.execute(sql, val)
mycursor.close()
print(mycursor.rowcount, "Record Updated.")
QtWidgets.QMessageBox.information(self, "Success", "Parameter is Updated successfully")
db1.connectionCommit(self.conn)
db1.connectionClose(self.conn)
self.LE_Value.setText("")
self.LE_name.setText("")
self.LE_D_Value.setText("")
self.LE_Notes.setText("")
except Exception as err:
print(err)
self.FN_DISPLAY_PRIVILAGE()
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")
def FN_REPLACE_CLICKED(self):
try:
replacedPts=self.Qline_replace.text().strip()
if (self.Qline_replace.text().strip() !='' and self.Qline_points.text().strip() !='') or (replacedPts == '-') :
print(self.Qline_points.text().strip())
ret = CL_validation.FN_validation_int(self.Qline_replace.text().strip())
if ret == True:
replacedPoints = float(self.Qline_replace.text().strip())
actualPoints = float(self.Qline_points.text().strip())
remainingPoints = actualPoints - replacedPoints
self.Qline_remainder.setText(str(remainingPoints))
self.FN_GET_POINTS_VALUE(replacedPoints)
else:
QtWidgets.QMessageBox.warning(self, "خطأ", "replaced points is not integer ")
except Exception as err:
print(err)
def FN_ModifyList(self):
self.name = self.LE_name.text().strip()
self.status = self.CMB_Status.currentText()
if self.status == 'Active':
self.status = 1
else:
self.status = 0
creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
if CL_validation.FN_isEmpty(self.name):
QtWidgets.QMessageBox.warning(self, "Error", "Please enter all required fields")
else:
try:
rowNo = self.w1.selectedItems()[0].row()
desc = self.w1.item(rowNo, 0).text().strip()
if self.LE_name.text().strip() != desc:
QtWidgets.QMessageBox.warning(self, "Error", "List Name Can't be change")
return
else:
mycursor = self.conn.cursor()
sql = "delete from Hyper1_Retail.SYS_CKECK_LIST_POS where POS_NO = %s "
val = (desc,)
mycursor.execute(sql, val)
mycursor.close()
self.FN_CREATEList()
db1.connectionCommit(self.conn)
db1.connectionClose(self.conn)
QtWidgets.QMessageBox.information(self, "Success", "List is Updated successfully")
self.LE_name.setText("")
except Exception as err:
print(err)
self.FN_DISPLAY_PRIVILAGE()
def FN_SAVE_UPLOAD(self):
if self.fileName != '':
self.LE_fileName.setText(self.fileName)
wb = xlrd.open_workbook(self.fileName)
sheet = wb.sheet_by_index(0)
conn = db1.connect()
mycursor = conn.cursor()
errorMsg = ''
createdCust = 0
nonCreatedCust = 0
#print (sheet.nrows)
error_message = ''
for i in range(sheet.nrows):
error = 0
try:
self.name = sheet.cell_value(i, 0)
error_message = error_message + " \n username " + self.name
self.custGroup = int(sheet.cell_value(i, 1))
self.loyalityType = int(sheet.cell_value(i, 2))
self.phone = int(sheet.cell_value(i, 3))
self.mobile = sheet.cell_value(i, 4)
self.job = sheet.cell_value(i, 5)
self.address = sheet.cell_value(i, 6)
self.city = int(sheet.cell_value(i, 7))
self.district = int(sheet.cell_value(i, 8))
self.building = int(sheet.cell_value(i, 9))
self.floor = int(sheet.cell_value(i, 10))
self.email = sheet.cell_value(i, 11)
self.company = sheet.cell_value(i, 12)
self.workPhone = int(sheet.cell_value(i, 13))
self.workAddress = sheet.cell_value(i, 14)
self.status = int(sheet.cell_value(i, 15))
self.notes = sheet.cell_value(i, 16)
nationalID = sheet.cell_value(i, 17)
#QtWidgets.QMessageBox.warning(self, "خطأ", "Please select the row you want to modify ")
if self.name == '' or self.mobile == '' or self.job == '' or self.address == '' or self.city == '' or self.district == '' or self.building == '' \
or self.email == '' or nationalID =='':
error = 1
error_message = error_message + " user has an empty fields"
ret = CL_validation.FN_validation_mobile(str(self.mobile))
if ret == 3:
error_message = error_message + "رقم الموبايل يجب أن يكون 11 رقم"
error = 1
elif ret == 2:
error_message = error_message + " رقم الموبايل يجب أن يبدأ ب 01"
error = 1
ret = CL_validation.FN_validation_int(str(self.phone))
if ret == False:
error_message = error_message + " , صحيح غير الهاتف رقم "
error = 1
ret = CL_validation.FN_validation_int(nationalID)
if ret == False:
QtWidgets.QMessageBox.warning(self, "خطأ",
"رقم البطاقه غير صحيح")
error = 1
ret = CL_validation.FN_valedation_mail(self.email)
if ret == False:
error_message = error_message + " إيميل غير صحسح"
error = 1
if error != 1:
sql0 = " LOCK TABLES Hyper1_Retail.POS_CUSTOMER WRITE "
mycursor.execute(sql0)
creationDate = str(
datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
sql = "INSERT INTO Hyper1_Retail.POS_CUSTOMER( LOYCT_TYPE_ID, CG_GROUP_ID, POSC_NAME, POSC_PHONE," \
" POSC_MOBILE, POSC_JOB, POSC_ADDRESS, POSC_CITY, POSC_DISTICT, POSC_BUILDING,POSC_FLOOR, POSC_EMAIL, " \
"POSC_CREATED_BY, POSC_CREATED_ON ,POSC_CHANGED_BY , POSC_COMPANY, " \
"POSC_WORK_PHONE, POSC_WORK_ADDRESS, POSC_NOTES, POSC_STATUS,`POSC_NATIONAL_ID`) " \
" VALUES (%s, %s, %s,%s,%s, %s, %s, %s, %s, " \
"%s,%s, %s, %s,%s, %s,%s, %s, %s, %s,%s,%s)"
# sql = "INSERT INTO SYS_USER (USER_ID,USER_NAME) VALUES (%s, %s)"
val = (self.loyalityType, self.custGroup, self.name,
str(self.phone),
self.mobile, self.job, self.address,
str(self.city), str(self.district),
str(self.building), self.floor, self.email,
CL_userModule.user_name, creationDate, ' ',
self.company, self.workPhone, self.workAddress,
self.notes, self.status, nationalID)
#print(val)
mycursor.execute(sql, val)
createdCust = createdCust + 1
sql00 = " UNLOCK tables "
mycursor.execute(sql00)
db1.connectionCommit(conn)
else:
nonCreatedCust = nonCreatedCust + 1
# self.msgBox1.setText(error_message)
# self.msgBox1.show()
except Exception as err:
print(err)
mycursor.close()
self.msgBox = QMessageBox()
# Set the various texts
self.msgBox.setWindowTitle("Information")
self.msgBox.setStandardButtons(QMessageBox.Ok)
self.msgBox.setText(error_message + "\n No of created Cust '" +
str(createdCust) +
"' No of non created Cust '" +
str(nonCreatedCust) + "'")
self.msgBox.show()
self.close()
#Extracting number of rows
else:
QtWidgets.QMessageBox.warning(self, "خطأ", "برجاء اختيار الملف")
def FN_MODIFY_REDITEM(self):
try:
if len(self.Qtable_redeem.selectedIndexes()) > 0:
branch = self.CMB_branch.currentText()
comp = self.CMB_company.currentText()
bar = self.Qline_barcode.text().strip()
date_from = self.Qdate_from.date().toString('yyyy-MM-dd')
date_to = self.Qdate_to.date().toString('yyyy-MM-dd')
comp = util.FN_GET_COMP_ID(comp)
branch = util.FN_GET_BRANCH_ID(branch, comp)
points = self.Qline_points.text().strip()
creationDate1 = str(datetime.today().strftime('%Y-%m-%d'))
if self.Qradio_active.isChecked():
status = 1
else:
status = 0
conn = db1.connect()
mycursor = conn.cursor()
if points == '':
QtWidgets.QMessageBox.warning(self, "خطأ",
"برجاء إدخال النقاط ")
elif date_to < date_from:
QtWidgets.QMessageBox.warning(
self, "خطأ",
"تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء"
)
# elif date_from < creationDate1:
# QtWidgets.QMessageBox.warning(self, "خطأ", "تاريخ التعديل يجب أن يكون أكبرمن أو يساوي تاريخ اليوم")
else:
self.CMB_branch.hide()
self.CMB_company.hide()
self.Qcombo_group3.show()
self.Qcombo_group4.show()
self.Qline_barcode.setEnabled(True)
self.Qline_barcode.setText('')
self.Qline_points.setText('')
ret2 = CL_validation.FN_validation_int(points)
if ret2 == True:
sql = "update Hyper1_Retail.REDEEM_ITEM " \
"set REDEEM_POINTS_QTY =%s,REDEEM_VALID_FROM =%s , REDEEM_VALID_TO = %s , " \
"REDEEM_STATUS =%s where POS_GTIN = %s and COMPANY_ID = %s and BRANCH_NO = %s "
val = (points, date_from, date_to, status, bar, comp,
branch)
mycursor.execute(sql, val)
mycursor.close()
print(mycursor.rowcount, "record updated.")
QtWidgets.QMessageBox.information(
self, "نجاح", "تم التعديل ")
db1.connectionCommit(conn)
self.FN_REFRESH_DATA_GRID()
self.old_status = util.FN_GET_STATUS_id(
str(self.old_status))
if str(status) != str(self.old_status):
util.FN_INSERT_IN_LOG("REDEEM_ITEM", "status",
status, self.old_status, bar,
comp, branch)
if str(points) != str(self.old_points):
util.FN_INSERT_IN_LOG("REDEEM_ITEM", "points",
points, self.old_points, bar,
comp, branch)
if str(date_from) != str(self.old_valid_from):
util.FN_INSERT_IN_LOG("REDEEM_ITEM", "valid_from",
date_from,
self.old_valid_from, bar,
comp, branch)
if str(date_to) != str(self.old_valid_to):
util.FN_INSERT_IN_LOG("REDEEM_ITEM", "valid_to",
date_to, self.old_valid_to,
bar, comp, branch)
print("in modify red item")
else:
QtWidgets.QMessageBox.warning(
self, "خطأ", "برجاء اختيار السطر المراد تعديله ")
except Exception as err:
print(err)
def FN_CREATE_REDITEM(self):
try:
branchs = self.Qcombo_group4.currentData()
companies = self.Qcombo_group3.currentData()
bar = self.Qline_barcode.text().strip()
date_from = self.Qdate_from.date().toString('yyyy-MM-dd')
date_to = self.Qdate_to.date().toString('yyyy-MM-dd')
points = self.Qline_points.text().strip()
if self.Qradio_active.isChecked():
status = 1
else:
status = 0
# mycursor = self.conn.cursor(buffered=True)
conn = db1.connect()
mycursor = conn.cursor()
creationDate = str(datetime.today().strftime('%Y-%m-%d-%H:%M-%S'))
creationDate1 = str(datetime.today().strftime('%Y-%m-%d'))
# get COMPANY
company_list = companies
# get branchs
branch_list = branchs
if len(self.Qcombo_group3.currentData()) == 0 or len(
self.Qcombo_group4.currentData()
) == 0 or bar == '' or points == '' or date_from == '' or date_to == '':
QtWidgets.QMessageBox.warning(self, "خطأ",
"برجاء إدخال جميع البيانات")
elif date_to < date_from:
QtWidgets.QMessageBox.warning(
self, "خطأ",
"تاريخ الانتهاء يجب ان يكون اكبر من او يساوي تاريخ الانشاء"
)
elif date_from < creationDate1:
QtWidgets.QMessageBox.warning(
self, "خطأ",
"تاريخ الإنشاء يجب أن يكون أكبرمن أو يساوي تاريخ اليوم")
else:
ret1 = self.FN_CHECK_VALID_BARCCODE(bar)
if ret1 == True:
ret2 = CL_validation.FN_validation_int(points)
if ret2 == True:
for com in company_list:
for br in branch_list:
ret = self.FN_CHECK_EXIST(com, br, bar)
if ret == False:
mycursor1 = conn.cursor()
#get BMC ID
mycursor1.execute(
"select BMC_ID from Hyper1_Retail.POS_ITEM where POS_GTIN ='"
+ bar + "'")
myresult = mycursor1.fetchone()
BMC_ID = myresult[0]
sql = "INSERT INTO Hyper1_Retail.REDEEM_ITEM (POS_GTIN,COMPANY_ID," \
"BRANCH_NO,REDEEM_POINTS_QTY,REDEEM_CREATED_ON,REDEEM_CREATED_BY,REDEEM_VALID_FROM" \
",REDEEM_VALID_TO,REDEEM_STATUS,BMC_ID)" \
"values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
val = (bar, com, br, points, creationDate,
CL_userModule.user_name, date_from,
date_to, status, BMC_ID)
mycursor1.execute(sql, val)
db1.connectionCommit(conn)
mycursor1.close()
QtWidgets.QMessageBox.information(
self, "تم", "تم الإنشاء")
else:
QtWidgets.QMessageBox.warning(
self, "خطأ", "المدخلات موجوده بالفعل ")
self.FN_REFRESH_DATA_GRID()
else:
QtWidgets.QMessageBox.warning(
self, "خطأ", "النقاط يجب أن تكون أرقام")
else:
QtWidgets.QMessageBox.warning(self, "خطأ",
"الباركود غير صحيح")
except Exception as err:
print(err)
def FN_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())
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())
def __init__(self):
title = Text()
val = CL_validation()
pdfmetrics.registerFont(TTFont('Scheherazade', 'adobearabic.ttf'))
data = [[
'Nubmber reset:', '248361 5/1/2018',
' ', 'Client Data', ''
],
[
'phone number:',
title.gettelText(), ' ',
'Customer Code',
title.getcodeText()
],
[
'mobile number',
title.gettelText(), ' ',
'Customer Name', 'TEST'
],
['', '', ' ', 'City', 'Giza']]
f = Table(title.getData(), repeatRows=1, repeatCols=1, hAlign='CENTER')
d = Drawing(100, 5)
d.add(Line(16, 6, 500, 6))
connection = db1.connect()
frame = pd.read_sql(str(title.getQuery()), connection)
df = pd.DataFrame(frame, columns=field_names)
# ['PROM_ID', 'PROM_TYPE_ID', 'PROM_CREATED_BY', 'PROM_CREATED_BY', 'PROM_CREATED_ON','PROM_LINE_NO'])
df = df.reset_index()
df = df.rename(columns={"index": "ID"})
z = df.size
print(z)
row, col = df.shape
# df['PROM_ID'] = df['PROM_ID'].str.wrap(60)
total = 0
# numCount = 0
for y in range(col):
for x in range(row):
val = df.iloc[:, y].values[x]
print(type(val))
if type(val) == str:
arabic_text = arabic_reshaper.reshape(val)
arabic_text = get_display(arabic_text)
df.iloc[x, y] = arabic_text
# numCount += 1
num = 0
for x in range(row):
val = df[field_names[0]].values[x]
total += int(val)
num = x
df.sort_values(by=field_names[0], inplace=True)
df.at[num + 2, field_names[0]] = str(total)
col_lst = df.columns.to_list()
for i in range(1, len(col_lst)):
arabic_text = arabic_reshaper.reshape(col_lst[i])
arabic_text = get_display(arabic_text)
col_lst[i] = arabic_text
data = [col_lst] + df.values.tolist()
# data = [df.columns.to_list()] + df.values.tolist()
table = Table(data,
repeatRows=1,
repeatCols=1,
rowHeights=20,
hAlign='CENTER')
table.setStyle(
TableStyle([('INNERGRID', (0, 0), (-1, -1), 0.2, colors.black),
('BOX', (0, 0), (-1, -1), 0.2, colors.black),
('LINEBELOW', (0, 0), (-1, -1), 1, colors.black),
('FONTNAME', (0, 0), (-1, -1), 'Scheherazade'),
('GRID', (0, 0), (-1, -1), 0.01 * inch, (
0,
0,
0,
)), ('FONTSIZE', (0, 0), (-1, -1), 6),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
('FONT', (0, 0), (-1, 0), 'Scheherazade'),
('FONTSIZE', (0, 0), (-1, 0), 6),
('FONT', (0, -1), (-1, -1), 'Scheherazade'),
('FONTSIZE', (0, -1), (-1, -1), 10)]))
genStr = "Total LienceNumber: " + str(total)
styles = getSampleStyleSheet()
elements = []
elements.append(f)
elements.append(d)
elements.append(table)
elements.append(Spacer(20, 20))
elements.append(Paragraph(genStr))
elements.append(PageBreak())
foo = Foo()
foo.foo_func(title.getName())
foo.setFooter(title.getFooter())
foo.setFont(title.getFont())
foo.setFontsize(title.getFontsize())
foo.setcodeText(title.getcodeText())
foo.setwaterText(title.getwaterText())
foo.settelText(title.gettelText())
foo.setbrachText(title.getbrachText())
p = FooterCanvas
size = 210 / len(field_names)
print(len(field_names))
print(size)
doc = SimpleDocTemplate("my_file.pdf",
pagesize=A4,
rightMargin=50,
leftMargin=50,
topMargin=100)
doc.multiBuild(elements, canvasmaker=p)