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))