class Update_Daily_Bill(QDialog, Ui_Update_Daily_bill): def __init__(self, parent=None): super(Update_Daily_Bill, self).__init__(parent) self.setupUi(self) self.setWindowTitle("Update Daily Bill") self.onlyint = QtGui.QIntValidator() self.phone_le.setValidator(self.onlyint) self.bill_le.setValidator(self.onlyint) self.total_le.setValidator(self.onlyint) self.recieved_le.setValidator(self.onlyint) self.due_le.setValidator(self.onlyint) self.pay_le.setValidator(self.onlyint) self.balance_le.setValidator(self.onlyint) self.pay_le.setPlaceholderText(str(0)) self.cal_tool.clicked.connect(self.delivery_calender) self.save_btn.clicked.connect(self.savebtn) self.print_btn.clicked.connect(self.printbill) self.close_btn.clicked.connect(self.closebtn) self.pay_le.textChanged.connect(self.amountdue) self.current_date() self.connectdb() config_name = 'update_daily_bill.cfg' # determine if application is a script file or frozen exe if getattr(sys, 'frozen', False): application_path = os.path.dirname(sys.executable) elif __file__: application_path = os.path.dirname(__file__) config_path = os.path.join(application_path, config_name) icon_image = os.path.join(application_path, "images", "VASA_ICON.png") self.setWindowIcon(QtGui.QIcon(icon_image)) save_image = os.path.join(application_path, "images", "save.png") print_image = os.path.join(application_path, "images", "print.png") clear_image = os.path.join(application_path, "images", "clear.png") close_image = os.path.join(application_path, "images", "close.png") self.calender_image = os.path.join(application_path, "images", "calender.png") self.setWindowIcon(QtGui.QIcon(icon_image)) self.save_btn.setIcon(QtGui.QIcon(save_image)) self.save_btn.setIconSize(QtCore.QSize(35, 35)) self.print_btn.setIcon(QtGui.QIcon(print_image)) self.print_btn.setIconSize(QtCore.QSize(35, 35)) self.delete_btn.setIcon(QtGui.QIcon(clear_image)) self.delete_btn.setIconSize(QtCore.QSize(35, 35)) self.close_btn.setIcon(QtGui.QIcon(close_image)) self.close_btn.setIconSize(QtCore.QSize(35, 35)) shortcut = QtWidgets.QShortcut( QtGui.QKeySequence(QtCore.Qt.Key_Tab), self.bill_le, context=QtCore.Qt.WidgetWithChildrenShortcut, activated=self.bill_value_fetch) def connectdb(self): global cur global connect cur, con = Add_Odbc_Connection.connectdb(self) connect = con return cur '''global cur global connect connect = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};' 'Server=DHANALAKSHMI_PC\SQLEXPRESS;' 'Database=VASADB;' 'Trusted_Connection=yes;') cur = connect.cursor() return cur''' def current_date(self): now = QDate.currentDate() print(now) print(now.toString(Qt.ISODate)) today = datetime.date.today() self.billdate = today.strftime("%d/%m/%Y") date_format = today.strftime("%d/%m/%Y %A") print(date_format) self.date_le.setText(date_format) def delivery_calender(self): self.calender = QCalendarWidget() self.calender.setMinimumDate(QDate(1900, 1, 1)) self.calender.setMaximumDate(QDate(2999, 12, 31)) self.calender.setGridVisible(True) self.calender.clicked.connect(self.updatedate) self.calender.setWindowModality(Qt.ApplicationModal) self.calender.setWindowTitle("Delivery Date") self.calender.setWindowIcon(QtGui.QIcon(self.calender_image)) self.calender.show() def updatedate(self, *args): getdate = self.calender.selectedDate().toString("dd/MM/yyyy") print('get date value is ', getdate) self.delivery_date_le.setText(getdate) self.calender.deleteLater() def table_records(self): self.viewtable.setRowCount(0) self.viewtable.setColumnCount(7) header_label = [ 'Sl.No', 'Category', 'Size', 'Rate', 'Qty', 'Amount', 'Date' ] self.viewtable.verticalHeader().setVisible(False) self.viewtable.setHorizontalHeaderLabels(header_label) for i in range(0, 7): self.viewtable.horizontalHeaderItem(i).setTextAlignment( Qt.AlignCenter) header = self.viewtable.horizontalHeader() font = QtGui.QFont() font.setFamily("Calibri") font.setPointSize(14) font.setBold(True) font.setWeight(75) header.setFont(font) header.setSectionResizeMode(0, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(1, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(2, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(3, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(4, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(5, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(6, QtWidgets.QHeaderView.Stretch) header.setStyleSheet("QHeaderView::section { border: 1px solid ;}") def bill_value_fetch(self): print("This function get passed") bill_no = self.bill_le.text() print('The bill no is ', bill_no) self.table_records() select_query = "SELECT ORD.CATEGORY,ORD.SIZE,ORD.RATE,ORD.QTY,ORD.TOTAL_AMOUNT,BILL.BILLING_DATE,\ BILL.TOTAL_AMOUNT,BILL.AMOUNT_RECIEVED,BILL.AMOUNT_DUE\ FROM DBO.ORDER_DETAILS ORD INNER JOIN DBO.BILLING_TABLE BILL ON\ ORD.BILL_NO = BILL.BILL_NO\ AND BILL.BILL_NO = ? AND BILL_TYPE ='DAILY'" cur.execute(select_query, bill_no) result = cur.fetchall() result_check = len(result) print('the test result value is', result_check) if result_check >= 1: new_result = [] for i in range(len(result)): tuple_value = result[i] tuple_value = list(tuple_value) tuple_value[5] = tuple_value[5].strftime('%d/%m/%Y') tuple_value = [ str(value) for value in tuple_value if type(value) != 'str' ] print('the modified tuple value is ', tuple_value) new_result.append(tuple_value) print('The new result value is ', new_result) print("the length is ", len(new_result)) print("the column range is ", range(self.viewtable.columnCount())) print("the row range is ", range(len(new_result))) for row in range(0, len(new_result)): self.viewtable.insertRow(row) self.viewtable.setItem( row, 0, self.table_item(str(row + 1), Qt.ItemIsSelectable | Qt.ItemIsEnabled)) for column in range(1, self.viewtable.columnCount()): print( ' the result value for ' + str(row - 1) + ' and ' + str(column) + ' is ', new_result[(row - 1)][(column - 1)]) value = new_result[(row - 1)][(column - 1)] print('the value is ', value, ' and type is ', type(value)) self.viewtable.setItem( row, column, self.table_item(value, Qt.ItemIsSelectable | Qt.ItemIsEnabled)) print('The value inserted in row ', row, ' and column ', column, ' is :', new_result[(row - 1)][(column - 1)]) bill_select_query = "SELECT CUSTOMER_NAME,PHONE_NO,DELIVERY_DATE FROM \ dbo.BILLING_TABLE WHERE BILL_TYPE='DAILY' AND BILL_NO =?" cur.execute(bill_select_query, bill_no) bill_result = cur.fetchall() print(" The bill result value is", bill_result) bill_tuple_list = list(bill_result[0]) print("the new bill result is ", bill_tuple_list) bill_tuple_list[2] = bill_tuple_list[2].strftime('%d/%m/%Y') bill_tuple_list = [ str(data) for data in bill_tuple_list if type(data) != 'str' ] print("the new value for bill list is ", bill_tuple_list) db_total_amount = new_result[0][6] db_amount_recieved = new_result[0][7] db_amount_due = new_result[0][8] db_customer_name = bill_tuple_list[0] db_phone_no = bill_tuple_list[1] db_delivery_date = bill_tuple_list[2] self.total_le.setText(db_total_amount) self.recieved_le.setText(db_amount_recieved) self.due_le.setText(db_amount_due) self.customer_le.setText(db_customer_name) self.phone_le.setText(db_phone_no) self.delivery_date_le.setText(db_delivery_date) else: QMessageBox.warning(self, 'Error', 'Please enter correct Bill No.') def table_item(self, text, flag): tablewidgetitem = QTableWidgetItem(text) tablewidgetitem.setFlags(flag) return tablewidgetitem def amountdue(self): print('Amount due function in') print("the test value is ", self.due_le.text()) amount_due = float(self.due_le.text()) if self.pay_le.text() == "": Recievedamount = float(0) else: Recievedamount = float(self.pay_le.text()) print('Amount due is ', amount_due) print('amount recieved is ', Recievedamount) balance = float(amount_due - Recievedamount) print('The balanced amount is ', balance) self.balance_le.setText(str(balance)) def savebtn(self): if self.bill_le.text() != "": billno = int(self.bill_le.text()) customer_name = self.customer_le.text() phone_no = self.phone_le.text() delivery_date = self.delivery_date_le.text() amount_paid_now = self.pay_le.text() amount_recieved_prev = int(float(self.recieved_le.text())) if amount_paid_now == '': amount_paid_now = '0' print('amount paid now is ', amount_paid_now) print('amount paid previous is ', amount_recieved_prev) amount_recieved = int(amount_recieved_prev + int(amount_paid_now)) print('amount received sum is', amount_recieved) if self.balance_le.text() == '': self.balance_le.setText('0') amount_due = float(self.balance_le.text()) int_phone_no = int(phone_no) delivery_date = datetime.datetime.strptime(delivery_date, '%d/%m/%Y').date() int_amount_due = int(amount_due) update_query = "UPDATE dbo.BILLING_TABLE SET CUSTOMER_NAME =?,PHONE_NO =?,DELIVERY_DATE =?,\ AMOUNT_RECIEVED=?,AMOUNT_DUE=? WHERE BILL_NO =? AND BILL_TYPE='DAILY'" update_data = [ customer_name, int_phone_no, delivery_date, amount_recieved, int_amount_due, billno ] print("the update data is ", update_data) cur.execute(update_query, update_data) connect.commit() category_list = [ self.viewtable.item(row, 1).text() for row in range(self.viewtable.rowCount()) ] frame_size_list = [ self.viewtable.item(row, 2).text() for row in range(self.viewtable.rowCount()) ] rate_list = [ self.viewtable.item(row, 3).text() for row in range(self.viewtable.rowCount()) ] qty_list = [ self.viewtable.item(row, 4).text() for row in range(self.viewtable.rowCount()) ] amount_list = [ self.viewtable.item(row, 5).text() for row in range(self.viewtable.rowCount()) ] self.billno = billno self.customer_name = customer_name self.phone_number = phone_no self.total_amount = self.total_le.text() self.amount_recieved = amount_paid_now self.due_amount = amount_due self.prod_details = category_list self.prod_size = frame_size_list self.qty_list = qty_list self.amount_list = amount_list print('the category list is', category_list) print('the frame size list is ', frame_size_list) print('the rate list is ', rate_list) print('the qty list is', qty_list) print('the amount list is ', amount_list) if int(amount_due) == 0 and int(amount_paid_now) != 0: QMessageBox.information(self, 'Message', 'Bill is successfully closed') else: QMessageBox.information(self, 'Message', 'Bill is updated Successfully') self.save_btn.setEnabled(False) else: QMessageBox.warning(self, "Warning", "Please enter a bill no") def billcontent(self): folder_path = "D:\\Python\\Project\\bills\\" timenow = datetime.datetime.now() year = str(timenow.year) month = timenow.strftime("%B") print("the year is", year) print("the month is ", month) time = str(timenow.strftime("%H-%M")) footer_time = str(timenow.strftime("%I:%M %p")) print("the footer time is ", footer_time) today = datetime.date.today() bill_number = str(self.billno) cust_name = self.customer_name place = 'Mannargudi' lines = "\n====================================================================" today_date = today.strftime("%d/%m/%Y") product = self.prod_details size = self.prod_size qty = self.qty_list amount = self.amount_list total_amount = self.total_amount amount_paid = self.amount_recieved amount_due = self.due_amount print("the time is ", time, " and the format is ", type(time)) folder = folder_path + year + "\\" + month + "\\" + str(today) + '\\' filename = str(folder) + "Update_Daily_Bill_" + str(time) + ".rtf" print("the file name is ", filename) if not os.path.exists(folder): os.makedirs(folder) file = open(filename, 'w+') # file = open('D:/Python/bills/2019-12-30/Daily_Bill_16:49:50.docx','w+') # file =open('C:\\bill\\Daily_Bill.rtf','w') header = "\n\n\t\t\t\t VASA PHOTOGRAPHY" address1 = "\n\t\t\t No.100, Balakrishna Nagar," address2 = "\n\t\t\t\tKeerthi Clinic [opp]," address3 = "\n\t\t\t\t Mannargudi -614001" address4 = "\n\n Phone: 9944332270" address5 = "\t\t\t\tEmail: [email protected]" title = "\n\n\t\t\t\t\t\"CASH BILL\"\n====================================================================" bill_section = "\n\n Bill No\t: " + bill_number + "\t\t\t\tName : " + cust_name bill_section2 = "\n Date\t\t: " + today_date + "\t\t\tAddr : " + place + "\n\n====================================================================" table_header = "\n SL.No\tPRODUCT\t\t\tSIZE\t\tQTY\t\tAMOUNT\n====================================================================" final = header + address1 + address2 + address3 + address4 + address5 + title + bill_section + bill_section2 + table_header file.write(final) slno = 1 table_data = '' for prd, size, qty, amt in zip(product, size, qty, amount): if len(prd) < 6 and prd != 'FRAME': table_value = "\n " + str( slno) + "\t\t" + prd + "\t\t\t\t" + size + "\t\t" + str( qty) + "\t\t" + str(amt) file.write(table_value) elif len(prd) > 5 and prd != 'FRAME': table_value = "\n " + str( slno) + "\t\t" + prd + "\t\t\t" + size + "\t\t" + str( qty) + "\t\t" + str(amt) file.write(table_value) elif prd == 'FRAME' and len(size) > 5: table_value = "\n " + str( slno) + "\t\t" + prd + "\t\t\t\t" + size + "\t" + str( qty) + "\t\t" + str(amt) file.write(table_value) else: table_value = "\n " + str( slno) + "\t\t" + prd + "\t\t\t\t" + size + "\t\t" + str( qty) + "\t\t" + str(amt) file.write(table_value) slno += 1 table_data = table_data + table_value file.write(lines) netamount = 'NET AMOUNT' #'''{\\rtf1 \\b Net Amount \\b0\line\}''' footer1 = "\n\n TIME :" + footer_time + "\t\t\t\t\tTOTAL AMT\t:\t" + str( float(total_amount)) footer_net_amount = "\n\n\t\t\t\t\t" + netamount + "\t\tRs. " + str( float(total_amount)) footer2 = "\n\t\t\t\t\t\tAMT. RECIEVED\t:\t" + str(float(amount_paid)) footer3 = "\n SIGN :\t\t\t\t\t\tAMT. DUE\t:\t" + str( float(amount_due)) footer_final = footer1 + footer_net_amount + lines + footer2 + footer3 file.write(footer_final) file.write(lines) message = "\n\t\t*** THANK YOU... PLEASE VISIT US AGAIN ***" file.write(message) self.file_name = filename file.close() def printbill(self): print("the save btn status is ", self.save_btn.isEnabled()) if not self.save_btn.isEnabled(): self.billcontent() os.startfile(self.file_name, 'print') else: QMessageBox.warning( self, 'Warning', 'Please save the data first before printing the bill.') def closebtn(self): self.close()
class Daily_Report_Page(QDialog,Ui_daily_report): def __init__(self,parent=None): super(Daily_Report_Page,self).__init__(parent) self.setupUi(self) self.setWindowTitle("Daily Bill Report") self.table_records() self.from_cal_tool_btn.clicked.connect(self.from_calender) self.to_cal_tool_btn.clicked.connect(self.to_calender) self.current_date() #self.table_population() self.show_btn.clicked.connect(self.table_population) self.export_btn.clicked.connect(self.exportbtn) self.close_btn.clicked.connect(self.closebtn) config_name = 'daily_report.cfg' # determine if application is a script file or frozen exe if getattr(sys, 'frozen', False): application_path = os.path.dirname(sys.executable) elif __file__: application_path = os.path.dirname(__file__) config_path = os.path.join(application_path, config_name) icon_image = os.path.join(application_path, "images", "VASA_ICON.png") self.setWindowIcon(QtGui.QIcon(icon_image)) excel_image = os.path.join(application_path, "images", "excel.png") close_image = os.path.join(application_path, "images", "close.png") show_image = os.path.join(application_path, "images", "search.png") self.export_btn.setIcon(QtGui.QIcon(excel_image)) self.export_btn.setIconSize(QtCore.QSize(30, 30)) self.close_btn.setIcon(QtGui.QIcon(close_image)) self.close_btn.setIconSize(QtCore.QSize(30, 30)) self.show_btn.setIcon(QtGui.QIcon(show_image)) self.show_btn.setIconSize(QtCore.QSize(30, 30)) def connectdb(self): global cur global connect cur, con = Add_Odbc_Connection.connectdb(self) connect = con return cur '''global cur global connect connect = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};' 'Server=DHANALAKSHMI_PC\SQLEXPRESS;' 'Database=VASADB;' 'Trusted_Connection=yes;') cur = connect.cursor() return cur''' def table_records(self): self.daily_table.setRowCount(0) self.daily_table.verticalHeader().setVisible(False) header = self.daily_table.horizontalHeader() header.setSectionResizeMode(0, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(1, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(2, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(3, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(4, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(5, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(6, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(7, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(8, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(9, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(10, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(11, QtWidgets.QHeaderView.ResizeToContents) header.setStyleSheet("QHeaderView::section { border: 1px solid ;}") def from_calender(self): self.from_calender = QCalendarWidget() self.from_calender.setMinimumDate(QDate(1900,1,1)) self.from_calender.setMaximumDate(QDate(2999,12,31)) self.from_calender.setGridVisible(True) self.from_calender.clicked.connect(self.from_updatedate) self.from_calender.setWindowModality(Qt.ApplicationModal) self.from_calender.setWindowTitle("From Date") self.from_calender.show() def to_calender(self): self.to_calender = QCalendarWidget() self.to_calender.setMinimumDate(QDate(1900,1,1)) self.to_calender.setMaximumDate(QDate(2999,12,31)) self.to_calender.setGridVisible(True) self.to_calender.clicked.connect(self.to_updatedate) self.to_calender.setWindowModality(Qt.ApplicationModal) self.to_calender.setWindowTitle("To Date") self.to_calender.show() def from_updatedate(self,*args): getdate = self.from_calender.selectedDate().toString("dd-MMM-yyyy") print('From date value is ',getdate) self.from_date.setText(getdate) self.from_calender.deleteLater() def to_updatedate(self,*args): getdate = self.to_calender.selectedDate().toString("dd-MMM-yyyy") print('to date value is ',getdate) self.to_date.setText(getdate) self.to_calender.deleteLater() def current_date(self): now = QDate.currentDate() print(now) print(now.toString(Qt.ISODate)) today = datetime.date.today() date_format = today.strftime("%d-%b-%Y") print(date_format) self.from_date.setText(date_format) self.to_date.setText(date_format) def table_population(self): self.connectdb() self.daily_table.setRowCount(0) from_date = self.from_date.text() from_date =datetime.datetime.strptime(from_date,'%d-%b-%Y').date() self.table_from_date =from_date print("The from date value is ",from_date) to_date = self.to_date.text() to_date=datetime.datetime.strptime(to_date,'%d-%b-%Y').date() self.table_to_date = to_date select_query_all = "SELECT BILL.BILL_NO,ROW_NUMBER() OVER( PARTITION BY BILL.BILL_NO ORDER BY CATEGORY) SLNO,\ BILL.BILLING_DATE,CATEGORY,ORD.SIZE,ORD.RATE,ORD.QTY,ORD.TOTAL_AMOUNT,BILL.TOTAL_AMOUNT,BILL.AMOUNT_RECIEVED,\ BILL.AMOUNT_DUE,BILL.CUSTOMER_NAME,BILL.PHONE_NO \ FROM DBO.ORDER_DETAILS ORD INNER JOIN DBO.BILLING_TABLE BILL ON \ ORD.BILL_NO = BILL.BILL_NO \ WHERE BILL_TYPE ='DAILY' AND BILLING_DATE BETWEEN ? AND ? ORDER BY BILL.BILL_NO" select_query_due = "SELECT BILL.BILL_NO,ROW_NUMBER() OVER( PARTITION BY BILL.BILL_NO ORDER BY CATEGORY) SLNO,\ BILL.BILLING_DATE,CATEGORY,ORD.SIZE,ORD.RATE,ORD.QTY,ORD.TOTAL_AMOUNT,BILL.TOTAL_AMOUNT,BILL.AMOUNT_RECIEVED,\ BILL.AMOUNT_DUE,BILL.CUSTOMER_NAME,BILL.PHONE_NO \ FROM DBO.ORDER_DETAILS ORD INNER JOIN DBO.BILLING_TABLE BILL ON \ ORD.BILL_NO = BILL.BILL_NO \ WHERE BILL_TYPE ='DAILY' AND AMOUNT_DUE <> 0 AND BILLING_DATE BETWEEN ? AND ? \ ORDER BY BILL.BILL_NO" select_count_all = "SELECT COUNT(DISTINCT BILL.BILL_NO) BILL_COUNT ,(SELECT COUNT(A.AMOUNT_DUE) FROM \ (SELECT BILL.BILL_NO, AMOUNT_DUE FROM DBO.ORDER_DETAILS ORD INNER JOIN DBO.BILLING_TABLE BILL \ ON \ ORD.BILL_NO = BILL.BILL_NO \ WHERE BILL_TYPE ='DAILY' AND BILLING_DATE BETWEEN ? AND ? AND \ AMOUNT_DUE<>0 GROUP BY BILL.BILL_NO,AMOUNT_DUE) AS A) DUE_COUNT FROM DBO.ORDER_DETAILS ORD \ INNER JOIN DBO.BILLING_TABLE BILL ON \ ORD.BILL_NO = BILL.BILL_NO \ WHERE BILL_TYPE ='DAILY' AND BILLING_DATE BETWEEN ? AND ? " select_count_due = "SELECT COUNT(DISTINCT BILL.BILL_NO) BILL_COUNT ,(SELECT COUNT(A.AMOUNT_DUE) FROM \ (SELECT BILL.BILL_NO, AMOUNT_DUE FROM DBO.ORDER_DETAILS ORD INNER JOIN DBO.BILLING_TABLE BILL \ ON \ ORD.BILL_NO = BILL.BILL_NO \ WHERE BILL_TYPE ='DAILY' AND BILLING_DATE BETWEEN ? AND ? AND \ AMOUNT_DUE<>0 GROUP BY BILL.BILL_NO,AMOUNT_DUE) AS A) DUE_COUNT FROM DBO.ORDER_DETAILS ORD \ INNER JOIN DBO.BILLING_TABLE BILL ON \ ORD.BILL_NO = BILL.BILL_NO \ WHERE BILL_TYPE ='DAILY' AND BILLING_DATE BETWEEN ? AND ? AND AMOUNT_DUE<>0 " if self.show_all_radio.isChecked(): cur.execute(select_query_all, (from_date, to_date,)) result = cur.fetchall() cur.execute(select_count_all,(from_date,to_date,from_date, to_date)) count_result =cur.fetchone() else: cur.execute(select_query_due, (from_date, to_date)) result = cur.fetchall() cur.execute(select_count_due, (from_date, to_date,from_date, to_date)) count_result = cur.fetchone() print("result is", result) self.data_result = [data for data in result] result_check = len(result) self.total_amount = 0 self.amount_recieved = 0 self.due_amount = 0 self.prev_row_bill_no = None if result_check >= 1: new_result = [] column_count = self.daily_table.columnCount() for row in range(result_check): tuple_value = list(result[row]) tuple_value[2] = tuple_value[2].strftime('%d/%m/%Y') tuple_value = [str(value) for value in tuple_value if type(value) != 'str'] # tuple_value.extend(self.work_finish) print('the modified tuple value is ', tuple_value) current_row_bill_no = tuple_value[0] if (current_row_bill_no != self.prev_row_bill_no) or self.prev_row_bill_no== None: self.total_amount = self.total_amount+int(float(tuple_value[8])) self.amount_recieved = self.amount_recieved + int(float(tuple_value[9])) self.due_amount = self.due_amount+ int(float(tuple_value[10])) self.prev_row_bill_no =tuple_value[0] self.daily_table.insertRow(row) for column in range(column_count): value = tuple_value[column] self.daily_table.setItem(row, column, self.table_item(value, Qt.ItemIsSelectable | Qt.ItemIsEnabled)) print("The total amount value is ", self.total_amount) print("The total amount recieved value is ", self.amount_recieved) print("The total due amount value is ", self.due_amount) self.total_le.setText(str(round(float(self.total_amount), 2))) self.recieved_le.setText(str(round(float(self.amount_recieved), 2))) self.due_le.setText(str(round(float(self.due_amount), 2))) print("The count values are",count_result) bill_count = str(count_result[0]) due_count = str(count_result[1]) self.total_order_label_value.setText(bill_count) self.total_due_label_value.setText(due_count) def table_item(self, text, flag): tablewidgetitem = QTableWidgetItem(text) tablewidgetitem.setFlags(flag) return tablewidgetitem def exportbtn(self): if self.daily_table.rowCount() >=1: file_path =QFileDialog.getSaveFileName(self, 'Export to Excel', "Daily_Bill_Report_"+self.table_from_date.strftime("%d_%b_%Y")+ "_to_"+self.table_to_date.strftime("%d_%b_%Y")+".xlsx", 'Excel files (.xlsx) ;; All Files ()') if file_path != ('', ''): print("File path is",file_path) excel_book_name = file_path[0] workbook = xlsxwriter.Workbook(excel_book_name) worksheet = workbook.add_worksheet("Today Report") title_format = workbook.add_format({'bold' : True}) title_format.set_bg_color('yellow') title_format.set_font_color('brown') title_format.set_border() data = self.data_result cell_format = workbook.add_format() cell_format.set_border() money_format = workbook.add_format() money_format.set_border() money_format.set_num_format('#,##0.00') print("The first data is ",data) print("check value",data[0][0]) data_modified =[] list_value = [] data_modified = [tuple(str(item) for item in i) for i in data] print ( "The workbook Name is ",excel_book_name) title =["BILL_NO","SL_NO","DATE","CATEGORY","FRAME_SIZE","RATE","QTY","AMOUNT","TOTAL_AMOUNT","AMOUNT_RECIEVED", "AMOUNT_DUE","CUSTOMER_NAME","PHONE_NO"] title_size =[len(i) for i in title] print('the title size is',title_size) for col in range(0,len(title)): worksheet.write(0,col,title[col],title_format) for row_level in range(len(data_modified)): for col_level in range(len(data_modified[0])): col_list =[5,7,7,9,10] if col_level in col_list: value = int(float(data_modified[row_level][col_level])) worksheet.write(row_level + 1, col_level, value, money_format) else: value = data_modified[row_level][col_level] worksheet.write(row_level+1,col_level,value,cell_format) print ( " the row number value is",row_level) footer_list =["Total Order Amount :","Total Recieved :","Due Amount :"] total = int(float(self.total_amount)) recieved = int(float(self.amount_recieved)) due =int(float(self.due_amount)) footer_amt_list =[total,recieved,due] footer_format = workbook.add_format() footer_format.set_border() footer_format.set_bold(True) footer_format.set_font_size(14) footer_money_format = workbook.add_format() footer_money_format.set_border() footer_money_format.set_bold(True) footer_money_format.set_num_format('#,##0.00') footer_money_format.set_font_color('green') footer_money_format.set_font_size(14) footer_money_format_due = workbook.add_format() footer_money_format_due.set_border() footer_money_format_due.set_bold(True) footer_money_format_due.set_num_format('#,##0.00') footer_money_format_due.set_font_color('red') footer_money_format_due.set_font_size(14) row_num =row_level +6 for i in range(3): footer_format.set_font_color('black') worksheet.merge_range(row_num,0,row_num,2,footer_list[i],footer_format) if i==2: worksheet.set_column(row_num,3,12) worksheet.write(row_num, 3, footer_amt_list[i], footer_money_format_due) else: worksheet.set_column(row_num, 3, 12) worksheet.write(row_num, 3, footer_amt_list[i], footer_money_format) row_num+=1 worksheet.set_column(0,2,10) workbook.close() QMessageBox.information(self,"File Export","Daily Report Exported Successfully...") else: QMessageBox.warning(self,"Warning", "Please click show button to get data and then select Export") def closebtn(self): self.close()
class Add_Daily_Bill(QDialog, Ui_Daily_bill): def __init__(self, parent=None): super(Add_Daily_Bill, self).__init__(parent) self.setupUi(self) self.setWindowTitle("Daily Bill") self.onlyint = QtGui.QIntValidator() self.phone_le.setValidator(self.onlyint) self.bill_le.setValidator(self.onlyint) self.total_le.setValidator(self.onlyint) self.recieved_le.setValidator(self.onlyint) self.due_le.setValidator(self.onlyint) self.total_le.setEnabled(False) self.total_le.setText(str(0)) self.due_le.setEnabled(False) self.due_le.setText(str(0)) #self.recieved_le.setText(str(0)) self.recieved_le.setPlaceholderText(str(0)) self.recieved_le.textChanged.connect(self.amountdue) self.cal_tool_btn.clicked.connect(self.delivery_calender) self.new_row_btn.clicked.connect(self.newrowbtn) self.remove_btn.clicked.connect(self.deleterow) self.save_btn.clicked.connect(self.savebtn) self.clear_btn.clicked.connect(self.clearbtn) self.close_btn.clicked.connect(self.closebtn) self.print_btn.clicked.connect(self.printbill) self.total_le.textChanged.connect(self.amountdue) self.bill_generator() self.current_date() self.table_records() #self.connectdb() #self.totalamount() config_name = 'daily_bill.cfg' # determine if application is a script file or frozen exe if getattr(sys, 'frozen', False): application_path = os.path.dirname(sys.executable) elif __file__: application_path = os.path.dirname(__file__) config_path = os.path.join(application_path, config_name) icon_image = os.path.join(application_path, "images", "VASA_ICON.png") save_image = os.path.join(application_path, "images", "save.png") print_image = os.path.join(application_path, "images", "print.png") clear_image = os.path.join(application_path, "images", "clear.png") close_image = os.path.join(application_path, "images", "close.png") self.calender_image = os.path.join(application_path, "images", "calender.png") self.setWindowIcon(QtGui.QIcon(icon_image)) self.save_btn.setIcon(QtGui.QIcon(save_image)) self.save_btn.setIconSize(QtCore.QSize(30, 30)) self.print_btn.setIcon(QtGui.QIcon(print_image)) self.print_btn.setIconSize(QtCore.QSize(30, 30)) self.clear_btn.setIcon(QtGui.QIcon(clear_image)) self.clear_btn.setIconSize(QtCore.QSize(30, 30)) self.close_btn.setIcon(QtGui.QIcon(close_image)) self.close_btn.setIconSize(QtCore.QSize(30, 30)) def connectdb(self): global cur global connect cur, con = Add_Odbc_Connection.connectdb(self) connect = con return cur '''global cur global connect connect = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};' 'Server=DHANALAKSHMI_PC\SQLEXPRESS;' 'Database=VASADB;' 'Trusted_Connection=yes;') cur = connect.cursor() return cur''' def table_records(self): self.viewtable.setRowCount(0) self.viewtable.verticalHeader().setVisible(False) header = self.viewtable.horizontalHeader() header.setSectionResizeMode(0, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(1, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(2, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(3, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(4, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(5, QtWidgets.QHeaderView.Stretch) header.setStyleSheet("QHeaderView::section { border: 1px solid ;}") self.existingvaluechange() self.viewtable.itemChanged.connect(self.totalamount) def newrowbtn(self): self.combovalue() rowvalue = self.viewtable.rowCount() print('the row value is ', rowvalue) self.viewtable.insertRow(rowvalue) self.newrow() def newrow(self): self.combo1 = QComboBox() self.combo2 = QComboBox() self.combo1.addItems(self.combolist) self.combo1.setCurrentText('NA') self.combo2.addItems(self.framelist) self.combo2.setCurrentText('NA') self.rowposition = self.viewtable.rowCount() - 1 print('The number of rows is ', self.rowposition) self.viewtable.setItem(self.rowposition, 0, QTableWidgetItem(str(self.rowposition + 1))) self.viewtable.setCellWidget(self.rowposition, 1, self.combo1) self.viewtable.setCellWidget(self.rowposition, 2, self.combo2) self.combo1.currentIndexChanged.connect(self.framecomboassign) self.combo1.currentIndexChanged.connect(self.ratevalue) #self.combo2.currentIndexChanged.connect(self.ratevalue) print('the value is ', self.combo1.currentText()) if self.combo1.currentText() == 'FRAME': self.combo2.setEnabled(True) else: self.combo2.setEnabled(False) def deleterow(self): rownum = self.viewtable.currentRow() print('the rownum is ', rownum) if rownum == -1: QMessageBox.warning(self, 'Warning', 'Please select a row for delete') else: self.viewtable.removeRow(rownum) rowcount = self.viewtable.rowCount() print('the row number is ', rowcount) for i in range(rowcount): value = i + 1 print('the row numbers is ', i) rowvalue = QTableWidgetItem() rowvalue.setText(str(value)) self.viewtable.setItem(i, 0, rowvalue) total = 0 for row in range(rowcount): rowvalue = float(self.viewtable.item(row, 5).text()) total += rowvalue self.total_le.setText(str(total)) def framecomboassign(self): rownum = self.viewtable.currentRow() print('The row number value is ', rownum) current_value = self.combo1.currentText() print(current_value) if current_value == 'FRAME': self.combo2.setEnabled(True) else: self.combo2.setEnabled(False) def ratevalue(self): rownum = self.viewtable.currentRow() curr_value = self.combo1.currentText() if curr_value != 'FRAME': SEL_QUERY = " SELECT PRICE from dbo.PROD_DETAILS WHERE PROD_TYPE ='DAILY' AND PROD_NAME =?" cur.execute(SEL_QUERY, curr_value) result = cur.fetchall() rate_value = str(result[0][0]) rate_table = QTableWidgetItem() rate_table.setText(rate_value) rate_table.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled) self.viewtable.setItem(rownum, 3, rate_table) self.viewtable.itemChanged.connect(self.amount_rate_calculation) print('the result value is ', rate_value) print(type(rate_value)) else: self.combo2.currentIndexChanged.connect(self.frame_rate_value) def frame_rate_value(self): SEL_QUERY = "SELECT PRICE from dbo.PROD_DETAILS WHERE PROD_TYPE ='DAILY' AND PROD_NAME =? AND SIZE=?" frame_curr_value = self.combo2.currentText() curr_value = self.combo1.currentText() rownum = self.viewtable.currentRow() cur.execute(SEL_QUERY, (curr_value, frame_curr_value)) result = cur.fetchall() rate_value = str(result[0][0]) rate_table = QTableWidgetItem() rate_table.setText(rate_value) rate_table.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled) self.viewtable.setItem(rownum, 3, rate_table) self.viewtable.itemChanged.connect(self.amount_rate_calculation) def amount_rate_calculation(self, item): rownum = item.row() colnum = item.column() print('the row number is ', rownum) print('the column number is ', colnum) if colnum == 4: rate_value = self.viewtable.item(rownum, 3).text() print('the rate value is ', rate_value, ' and its type is ', type(rate_value)) qty_value = self.viewtable.item(rownum, 4).text() print('the qty value is ', qty_value) int_rate_value = int(rate_value) int_qty_value = int(qty_value) amount_value = int_rate_value * int_qty_value print('the amount value is', amount_value) amount_table = QTableWidgetItem() amount_table.setText(str(amount_value)) amount_table.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled) self.viewtable.setItem(rownum, 5, amount_table) def totalamount(self, item): self.total_le.setEnabled(False) if item.column() == 5: rowcount = self.viewtable.rowCount() if rowcount == 0: self.total_le.setText(str(0)) else: total = 0 for row in range(rowcount): rowvalue = float(self.viewtable.item(row, 5).text()) total += rowvalue self.total_le.setText(str(total)) def amountdue(self): print('Amount due function in') print("the test value is ", self.total_le.text()) totalamount = float(self.total_le.text()) if self.recieved_le.text() == "": Recievedamount = float(0) else: Recievedamount = float(self.recieved_le.text()) print('Amount due is ', totalamount) print('amoint recieved is ', Recievedamount) balance = float(totalamount - Recievedamount) print('The balanced amount is ', balance) self.due_le.setText(str(balance)) def existingvaluechange(self): rownum = self.viewtable.cellWidget(0, 1) print('The row number value is ', rownum) #print('The value in Row is ',self.viewtable.item(rownum,1)) def combovalue(self): self.combolist = set() self.framelist = set() sel_query = "SELECT PROD_NAME from dbo.PROD_DETAILS WHERE PROD_TYPE ='DAILY'" self.connectdb() cur.execute(sel_query) result = cur.fetchall() for i in result: x = i[0] self.combolist.add(x) self.combolist.add('NA') frame_query = "SELECT SIZE FROM dbo.PROD_DETAILS WHERE PROD_TYPE ='DAILY' AND PROD_NAME ='FRAME' AND SIZE IS NOT NULL AND SIZE !=''" cur.execute(frame_query) frame_result = cur.fetchall() for j in frame_result: x = j[0] self.framelist.add(x) self.framelist.add('NA') def bill_generator(self): sel_query = 'SELECT MAX(BILL_NO) FROM dbo.BILLING_TABLE' self.connectdb() cur.execute(sel_query) result = cur.fetchall() value = result[0][0] if value != None: value = value + 1 print(value) else: value = 1 print('The else condition ', value) print(type(value)) self.bill_le.setText(str(value)) def current_date(self): now = QDate.currentDate() print(now) print(now.toString(Qt.ISODate)) today = datetime.date.today() self.billdate = today.strftime("%d/%m/%Y") date_format = today.strftime("%d/%m/%Y %A") print(date_format) self.date_le.setText(date_format) def delivery_calender(self): self.calender = QCalendarWidget() self.calender.setMinimumDate(QDate(1900, 1, 1)) self.calender.setMaximumDate(QDate(2999, 12, 31)) self.calender.setGridVisible(True) self.calender.clicked.connect(self.updatedate) self.calender.setWindowModality(Qt.ApplicationModal) self.calender.setWindowTitle("Delivery Date") self.calender.setWindowIcon(QtGui.QIcon(self.calender_image)) self.calender.show() def updatedate(self, *args): getdate = self.calender.selectedDate().toString("dd/MM/yyyy") print('get date value is ', getdate) self.delivery_date_le.setText(getdate) self.calender.deleteLater() def savebtn(self): if self.customer_le.text() == '' or self.phone_le.text() == '': QMessageBox.warning(self, 'Warning', 'Please enter the customer information') elif self.viewtable.rowCount() == 0: QMessageBox.warning(self, 'Warning', 'Please add the bill entry') else: billno = self.bill_le.text() orderdate = datetime.datetime.now().date() print('the order date value is ', orderdate, ' and type is ', type(orderdate)) customername = self.customer_le.text() phoneno = self.phone_le.text() totalamount = self.total_le.text() if self.recieved_le.text() == "": amountreceived = 0 else: amountreceived = self.recieved_le.text() dueamount = self.due_le.text() deliverydate = self.delivery_date_le.text() phoneno = int(phoneno) #orderdate = #orderdate = datetime.datetime.strptime(orderdate,'%d/%m/%Y').date() #orderdate= datetime.date.today() print('The order date is ', orderdate, ' and type is ', type(orderdate)) totalamount = float(totalamount) amountreceived = float(amountreceived) dueamount = float(dueamount) billno = int(billno) bill_type = 'DAILY' if deliverydate != '': deliverydate = datetime.datetime.strptime( deliverydate, '%d/%m/%Y').date() else: deliverydate = datetime.date(9999, 12, 31) print('the deliver date value is ', deliverydate, ' and type is ', type(deliverydate)) sql_order_date = orderdate.strftime("%d/%m/%Y") sql_delivery_date = deliverydate.strftime("%d/%m/%Y") ins_query = 'INSERT INTO dbo.BILLING_TABLE VALUES(?,?,?,?,?,?,?,?,?,?)' ins_query1 = "INSERT INTO dbo.BILLING_TABLE (BILL_NO,CUSTOMER_NAME,PHONE_NO,BILLING_DATE,DELIVERY_DATE,TOTAL_AMOUNT) VALUES(?,?,?,?,?,?)" data = (billno, customername, phoneno, orderdate, deliverydate, totalamount, amountreceived, dueamount, None, bill_type) data1 = (billno, customername, phoneno, orderdate, deliverydate, totalamount) cur.execute(ins_query, data) connect.commit() connect.close() # order details in order_table: max_order_id_query = " SELECT MAX(ORDER_ID) FROM DBO.ORDER_DETAILS" self.connectdb() cur.execute(max_order_id_query) result = cur.fetchall() order_value = result[0][0] if order_value != None: order_value = order_value + 1 print("The order_id Value is ", order_value) else: order_value = 1 print('The new Order Id value is ', order_value) print(type(order_value)) category_list = [ self.viewtable.cellWidget(row, 1).currentText() for row in range(self.viewtable.rowCount()) ] frame_size_list = [ self.viewtable.cellWidget(row, 2).currentText() for row in range(self.viewtable.rowCount()) ] rate_list = [ self.viewtable.item(row, 3).text() for row in range(self.viewtable.rowCount()) ] qty_list = [ self.viewtable.item(row, 4).text() for row in range(self.viewtable.rowCount()) ] amount_list = [ self.viewtable.item(row, 5).text() for row in range(self.viewtable.rowCount()) ] print('the category values are', category_list) print('the frame values are', frame_size_list) print('the rate values are', rate_list) print('the qty values are', qty_list) print('the amount values are', amount_list) # finding Prd_id prd_id_list = [] for category, frame in zip(category_list, frame_size_list): if category != 'FRAME': prd_select_query = "SELECT PROD_ID FROM dbo.PROD_DETAILS WHERE PROD_TYPE ='DAILY' AND PROD_NAME =?" cur.execute(prd_select_query, category) result = cur.fetchall() print('the result value is ', result[0][0]) prd_id_list.append(result[0][0]) else: prd_select_query = "SELECT PROD_ID FROM dbo.PROD_DETAILS WHERE PROD_TYPE ='DAILY' AND PROD_NAME ='FRAME' AND SIZE =?" cur.execute(prd_select_query, frame) result = cur.fetchall() prd_id_list.append(result[0][0]) print("The PRD_ID list values are ", prd_id_list) # insert data into ORDER_DETAILS table: for prd_id, category, size, rate, qty, amount in zip( prd_id_list, category_list, frame_size_list, rate_list, qty_list, amount_list): insert_qry_order_table = "INSERT INTO dbo.ORDER_DETAILS VALUES (?,?,?,?,?,?,?,?)" cur.execute(insert_qry_order_table, (order_value, billno, prd_id, category, size, rate, qty, amount)) connect.commit() connect.close() self.billno = billno self.customer_name = customername self.phone_number = phoneno self.total_amount = totalamount self.amount_recieved = amountreceived self.due_amount = dueamount self.prod_details = category_list self.prod_size = frame_size_list self.qty_list = qty_list self.amount_list = amount_list QMessageBox.information(self, 'Message', 'Data saved successfully') self.save_btn.setEnabled(False) def clearbtn(self): columns_list = [self.customer_le, self.phone_le, self.delivery_date_le] for i in columns_list: i.clear() self.viewtable.setRowCount(0) self.total_le.setText(str(0)) print("the clearbtn total amt value is ", self.total_le.text()) self.recieved_le.setText(str(0)) self.due_le.setText(str(0)) def closebtn(self): self.close() def billcontent(self): folder_path = "D:\\Python\\Project\\bills\\" timenow = datetime.datetime.now() year = str(timenow.year) month = timenow.strftime("%B") print("the year is", year) print("the month is ", month) time = str(timenow.strftime("%H-%M")) footer_time = str(timenow.strftime("%I:%M %p")) print("the footer time is ", footer_time) today = datetime.date.today() bill_number = str(self.billno) cust_name = self.customer_name place = 'Mannargudi' lines = "\n====================================================================" today_date = today.strftime("%d/%m/%Y") product = self.prod_details size = self.prod_size qty = self.qty_list amount = self.amount_list total_amount = self.total_amount amount_paid = self.amount_recieved amount_due = self.due_amount print("the time is ", time, " and the format is ", type(time)) folder = folder_path + year + "\\" + month + "\\" + str(today) + '\\' filename = str(folder) + "Daily_Bill_" + str(time) + ".rtf" print("the file name is ", filename) if not os.path.exists(folder): os.makedirs(folder) file = open(filename, 'w+') # file = open('D:/Python/bills/2019-12-30/Daily_Bill_16:49:50.docx','w+') # file =open('C:\\bill\\Daily_Bill.rtf','w') header = "\n\n\t\t\t\t VASA PHOTOGRAPHY" address1 = "\n\t\t\t No.100, Balakrishna Nagar," address2 = "\n\t\t\t\tKeerthi Clinic [opp]," address3 = "\n\t\t\t\t Mannargudi -614001" address4 = "\n\n Phone: 9944332270" address5 = "\t\t\t\tEmail: [email protected]" title = "\n\n\t\t\t\t\t\"CASH BILL\"\n====================================================================" bill_section = "\n\n Bill No\t: " + bill_number + "\t\t\t\tName : " + cust_name bill_section2 = "\n Date\t\t: " + today_date + "\t\t\tAddr : " + place + "\n\n====================================================================" table_header = "\n SL.No\tPRODUCT\t\t\tSIZE\t\tQTY\t\tAMOUNT\n====================================================================" final = header + address1 + address2 + address3 + address4 + address5 + title + bill_section + bill_section2 + table_header file.write(final) slno = 1 table_data = '' for prd, size, qty, amt in zip(product, size, qty, amount): if len(prd) < 6 and prd != 'FRAME': table_value = "\n " + str( slno) + "\t\t" + prd + "\t\t\t\t" + size + "\t\t" + str( qty) + "\t\t" + str(amt) file.write(table_value) elif len(prd) > 5 and prd != 'FRAME': table_value = "\n " + str( slno) + "\t\t" + prd + "\t\t\t" + size + "\t\t" + str( qty) + "\t\t" + str(amt) file.write(table_value) elif prd == 'FRAME' and len(size) > 5: table_value = "\n " + str( slno) + "\t\t" + prd + "\t\t\t\t" + size + "\t" + str( qty) + "\t\t" + str(amt) file.write(table_value) else: table_value = "\n " + str( slno) + "\t\t" + prd + "\t\t\t\t" + size + "\t\t" + str( qty) + "\t\t" + str(amt) file.write(table_value) slno += 1 table_data = table_data + table_value file.write(lines) footer1 = "\n\n TIME :" + footer_time + "\t\t\t\t\tSUB TOTAL\t:\t" + str( float(total_amount)) footer2 = "\n\t\t\t\t\t\t\tAMT.PAID\t:\t" + str(float(amount_paid)) footer3 = "\n SIGN :\t\t\t\t\t\tAMT.DUE\t:\t" + str( float(amount_due)) footer_final = footer1 + footer2 + footer3 file.write(footer_final) file.write(lines) message = "\n\t\t*** THANK YOU... PLEASE VISIT US AGAIN ***" file.write(message) self.file_name = filename file.close() def printbill(self): print("the save btn status is ", self.save_btn.isEnabled()) if not self.save_btn.isEnabled(): self.billcontent() os.startfile(self.file_name, 'print') else: QMessageBox.warning( self, 'Warning', 'Please save the data first before printing the bill.')
class Add_Function_Bill(QDialog, Ui_new_function): def __init__(self, parent=None): super(Add_Function_Bill, self).__init__(parent) self.setupUi(self) self.setWindowTitle("Add Function Bill") self.onlyint = QtGui.QIntValidator() self.phone_le.setValidator(self.onlyint) self.bill_le.setValidator(self.onlyint) self.total_le.setValidator(self.onlyint) self.recieved_le.setValidator(self.onlyint) self.due_le.setValidator(self.onlyint) self.current_date() self.bill_generator() self.functioncombovalue() self.package_combo.setEnabled(False) self.package_combo.setStyleSheet( 'QPushButton: disabled{background - color:grey}') self.function_combo.currentIndexChanged['QString'].connect( self.comboindexchanged) self.total_le.textChanged.connect(self.amountdue) self.recieved_le.textChanged.connect(self.amountdue) self.cal_tool_btn.clicked.connect(self.delivery_calender) self.save_btn.clicked.connect(self.savebtn) self.clear_btn.clicked.connect(self.clearbtn) self.close_btn.clicked.connect(self.closebtn) self.print_btn.clicked.connect(self.printbill) config_name = 'function_bill.cfg' # determine if application is a script file or frozen exe if getattr(sys, 'frozen', False): application_path = os.path.dirname(sys.executable) elif __file__: application_path = os.path.dirname(__file__) config_path = os.path.join(application_path, config_name) icon_image = os.path.join(application_path, "images", "VASA_ICON.png") self.setWindowIcon(QtGui.QIcon(icon_image)) save_image = os.path.join(application_path, "images", "save.png") print_image = os.path.join(application_path, "images", "print.png") clear_image = os.path.join(application_path, "images", "clear.png") close_image = os.path.join(application_path, "images", "close.png") self.calender_image = os.path.join(application_path, "images", "calender.png") self.save_btn.setIcon(QtGui.QIcon(save_image)) self.save_btn.setIconSize(QtCore.QSize(35, 35)) self.print_btn.setIcon(QtGui.QIcon(print_image)) self.print_btn.setIconSize(QtCore.QSize(35, 35)) self.clear_btn.setIcon(QtGui.QIcon(clear_image)) self.clear_btn.setIconSize(QtCore.QSize(35, 35)) self.close_btn.setIcon(QtGui.QIcon(close_image)) self.close_btn.setIconSize(QtCore.QSize(35, 35)) def connectdb(self): global cur global connect cur, con = Add_Odbc_Connection.connectdb(self) connect = con return cur '''global cur global connect connect = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};' 'Server=DHANALAKSHMI_PC\SQLEXPRESS;' 'Database=VASADB;' 'Trusted_Connection=yes;') cur = connect.cursor() return cur''' def current_date(self): now = QDate.currentDate() print(now) print(now.toString(Qt.ISODate)) today = datetime.date.today() self.billdate = today.strftime("%d/%m/%Y") date_format = today.strftime("%d/%m/%Y %A") print(date_format) self.date_le.setText(date_format) def bill_generator(self): sel_query = 'SELECT MAX(BILL_NO) FROM dbo.BILLING_TABLE' self.connectdb() cur.execute(sel_query) result = cur.fetchall() value = result[0][0] if value != None: value = value + 1 print(value) else: value = 1 print('The else condition ', value) print(type(value)) self.bill_le.setText(str(value)) def delivery_calender(self): self.calender = QCalendarWidget() self.calender.setMinimumDate(QDate(1900, 1, 1)) self.calender.setMaximumDate(QDate(2999, 12, 31)) self.calender.setGridVisible(True) self.calender.clicked.connect(self.updatedate) self.calender.setWindowModality(Qt.ApplicationModal) self.calender.setWindowTitle("Booking Calender") self.calender.setWindowIcon(QtGui.QIcon(self.calender_image)) self.calender.show() def updatedate(self, *args): getdate = self.calender.selectedDate().toString("dd/MM/yyyy") print('get date value is ', getdate) self.booking_le.setText(getdate) self.calender.deleteLater() def functioncombovalue(self): self.combolist = set('', ) sel_query = "SELECT DISTINCT PROD_NAME from dbo.PROD_DETAILS WHERE PROD_TYPE ='FUNCTION'" self.connectdb() cur.execute(sel_query) result = cur.fetchall() for i in result: x = i[0] self.combolist.add(x) self.function_combo.addItems(self.combolist) def combopackagevalue(self): self.combolist = set('', ) self.package_combo.clear() sel_query = "SELECT PACKAGE_NAME from dbo.PACKAGE_DETAILS" self.connectdb() cur.execute(sel_query) result = cur.fetchall() for i in result: x = i[0] self.combolist.add(x) self.package_combo.addItems(self.combolist) self.package_combo.currentIndexChanged['QString'].connect( self.totalamount) def comboindexchanged(self): current_value = self.function_combo.currentText() print("The category combo box current value is ", current_value) if current_value == 'MARRIAGE': self.total_le.clear() self.package_combo.setEnabled(True) self.package_combo.setStyleSheet("border-radius:10px;\n" "border-style:outset;\n" "border-width:2px;\n" "border-color:black;\n" "font: 11pt \"Cambria\";\n" "font:bold") self.combopackagevalue() else: self.package_combo.clear() self.package_combo.setEnabled(False) self.package_combo.setStyleSheet( 'QPushButton: disabled{background - color:grey}') self.totalamount() def totalamount(self): print('the total amount function is in') function_name = self.function_combo.currentText() print('the function name is', function_name) if function_name != 'MARRIAGE': SEL_QUERY = " SELECT PRICE from dbo.PROD_DETAILS WHERE PROD_TYPE ='FUNCTION' AND PROD_NAME =?" cur.execute(SEL_QUERY, function_name) result = cur.fetchall() total_amount_value = str(result[0][0]) print("the total amount is ", total_amount_value) self.total_le.setText(total_amount_value) else: package_name = self.package_combo.currentText() print('the package name is', package_name) SEL_QUERY = " SELECT PROD.PRICE from dbo.PROD_DETAILS PROD JOIN dbo.PACKAGE_DETAILS PACKAGE ON PROD.PACKAGE_ID = PACKAGE.PACKAGE_ID AND PROD_TYPE ='FUNCTION' AND PROD_NAME ='MARRIAGE' AND PACKAGE.PACKAGE_NAME =?" cur.execute(SEL_QUERY, package_name) result = cur.fetchall() total_amount_value = str(result[0][0]) print("the total amount is ", total_amount_value) self.total_le.setText(total_amount_value) def amountdue(self): print('Amount due function in') print("the test value is ", self.total_le.text()) if self.total_le.text() == "": totalamount = float(0) else: totalamount = float(str(self.total_le.text())) if self.recieved_le.text() == "": Recievedamount = float(0) else: Recievedamount = float(self.recieved_le.text()) print('Amount due is ', totalamount) print('amount recieved is ', Recievedamount) balance = float(totalamount - Recievedamount) print('The balanced amount is ', balance) self.due_le.setText(str(balance)) def savebtn(self): if self.customer_le.text() == '' or self.phone_le.text() == '': QMessageBox.warning(self, 'Warning', 'Please enter the customer information') elif self.total_le.text() == '': QMessageBox.warning(self, 'Warning', 'Please select proper function details') else: billno = self.bill_le.text() orderdate = datetime.datetime.now().date() print('the order date value is ', orderdate, ' and type is ', type(orderdate)) customername = self.customer_le.text() phoneno = self.phone_le.text() totalamount = self.total_le.text() if self.recieved_le.text() == "": amountreceived = 0 else: amountreceived = self.recieved_le.text() dueamount = self.due_le.text() booking_date = self.booking_le.text() phoneno = int(phoneno) print('The order date is ', orderdate, ' and type is ', type(orderdate)) totalamount = float(totalamount) amountreceived = float(amountreceived) dueamount = float(dueamount) billno = int(billno) bill_type = 'FUNCTION' if booking_date != '': bookingdate = datetime.datetime.strptime( booking_date, '%d/%m/%Y').date() else: bookingdate = datetime.date(9999, 12, 31) print('the deliver date value is ', bookingdate, ' and type is ', type(bookingdate)) sql_order_date = orderdate.strftime("%d/%m/%Y") sql_delivery_date = bookingdate.strftime("%d/%m/%Y") ins_query1 = 'INSERT INTO dbo.BILLING_TABLE VALUES(?,?,?,?,?,?,?,?,?)' ins_query = "INSERT INTO dbo.BILLING_TABLE (BILL_NO,CUSTOMER_NAME,PHONE_NO,BILLING_DATE,FUNCTION_BOOKING_DATE,TOTAL_AMOUNT,AMOUNT_RECIEVED,AMOUNT_DUE,BILL_TYPE) VALUES(?,?,?,?,?,?,?,?,?)" data = (billno, customername, phoneno, orderdate, bookingdate, totalamount, amountreceived, dueamount, bill_type) data1 = (billno, customername, phoneno, orderdate, bookingdate, totalamount) cur.execute(ins_query, data) connect.commit() connect.close() # order details in order_table: max_order_id_query = " SELECT MAX(ORDER_ID) FROM DBO.ORDER_DETAILS" self.connectdb() cur.execute(max_order_id_query) result = cur.fetchall() order_value = result[0][0] if order_value != None: order_value = order_value + 1 print("The order_id Value is ", order_value) else: order_value = 1 print('The new Order Id value is ', order_value) print(type(order_value)) function_name = self.function_combo.currentText() package_name = self.package_combo.currentText() total_amount = self.total_le.text() print('the function_name values are', function_name) print('the package_name values are', package_name) print('the total amount values are', total_amount) # finding Prd_id if function_name != 'MARRIAGE': prd_select_query = "SELECT PROD_ID FROM dbo.PROD_DETAILS WHERE PROD_TYPE ='FUNCTION' AND PROD_NAME =?" cur.execute(prd_select_query, function_name) result = cur.fetchall() print('the result value is ', result[0][0]) prd_id = result[0][0] else: prd_select_query = "SELECT PROD.PROD_ID from dbo.PROD_DETAILS PROD JOIN dbo.PACKAGE_DETAILS PACKAGE ON PROD.PACKAGE_ID = PACKAGE.PACKAGE_ID AND PROD_TYPE ='FUNCTION' AND PROD_NAME ='MARRIAGE' AND PACKAGE.PACKAGE_NAME =?" cur.execute(prd_select_query, package_name) result = cur.fetchall() prd_id = result[0][0] print("The PRD_ID values is ", prd_id) # insert data into ORDER_DETAILS table: size = 'NA' qty = 1 rate = total_amount insert_qry_order_table = "INSERT INTO dbo.ORDER_DETAILS VALUES (?,?,?,?,?,?,?,?)" cur.execute(insert_qry_order_table, (order_value, billno, prd_id, function_name, size, rate, qty, total_amount)) connect.commit() connect.close() self.billno = billno self.customer_name = customername self.phone_number = phoneno self.total_amount = total_amount self.amount_recieved = amountreceived self.due_amount = dueamount self.function_name = function_name self.package_name = package_name self.booking_date = bookingdate QMessageBox.information(self, 'Message', 'Data saved successfully') self.save_btn.setEnabled(False) def clearbtn(self): columns_list = [ self.customer_le, self.phone_le, self.booking_le, self.total_le, self.recieved_le, self.due_le ] for i in columns_list: i.clear() self.total_le.setText(str(0)) print("the clearbtn total amt value is ", self.total_le.text()) self.recieved_le.setText(str(0)) self.due_le.setText(str(0)) def closebtn(self): self.close() def billcontent(self): folder_path = "D:\\Python\\Project\\bills\\" timenow = datetime.datetime.now() year = str(timenow.year) month = timenow.strftime("%B") print("the year is", year) print("the month is ", month) time = str(timenow.strftime("%H-%M")) footer_time = str(timenow.strftime("%I:%M %p")) print("the footer time is ", footer_time) today = datetime.date.today() bill_number = str(self.billno) cust_name = self.customer_name place = 'Mannargudi' lines = "\n====================================================================" today_date = today.strftime("%d/%m/%Y") phoneno = str(self.phone_number) total_amount = self.total_amount amount_paid = self.amount_recieved amount_due = self.due_amount function_name = self.function_name package_name = self.package_name book = str(self.booking_date) print("the time is ", time, " and the format is ", type(time)) folder = folder_path + year + "\\" + month + "\\" + str(today) + '\\' filename = str(folder) + "Function_Bill_" + str(time) + ".rtf" print("the file name is ", filename) if not os.path.exists(folder): os.makedirs(folder) file = open(filename, 'w+') # file = open('D:/Python/bills/2019-12-30/Daily_Bill_16:49:50.docx','w+') # file =open('C:\\bill\\Daily_Bill.rtf','w') header = "\n\n\t\t\t\t VASA PHOTOGRAPHY" address1 = "\n\t\t\t No.100, Balakrishna Nagar," address2 = "\n\t\t\t\tKeerthi Clinic [opp]," address3 = "\n\t\t\t\t Mannargudi -614001" address4 = "\n\n Phone: 9944332270" address5 = "\t\t\t\tEmail: [email protected]" title = "\n\n\t\t\t\t\"FUNCTION BILL\"\n====================================================================" bill_section = "\n\n Bill No\t: " + bill_number + "\t\t\t\tDate : " + today_date + "\n" bill_section2 = "\n\n Customer Name\t\t: " + cust_name bill_section21 = "\n\n Customer Phone No. \t: " + phoneno bill_section22 = "\n\n Function Name \t\t: " + function_name bill_section23 = "\n\n Booking Date \t\t: " + book bill_section_package = "\n\n Package Name \t\t: " + package_name bill_section_combined = bill_section2 + bill_section21 + bill_section22 + bill_section23 + "\n" + lines bill_section_combined_marriage = bill_section2 + bill_section21 + bill_section22 + bill_section_package + bill_section23 + "\n" + lines if function_name == 'MARRIAGE': final = header + address1 + address2 + address3 + address4 + address5 + title + bill_section + lines + bill_section_combined_marriage file.write(final) else: final = header + address1 + address2 + address3 + address4 + address5 + title + bill_section + lines + bill_section_combined file.write(final) footer1 = "\n\n TIME :" + footer_time + "\t\t\t\tTOTAL AMOUNT\t:\t" + str( float(total_amount)) footer2 = "\n\t\t\t\t\t\tAMOUNT PAID\t\t:\t" + str(float(amount_paid)) footer3 = "\n SIGN :\t\t\t\t\tAMOUNT DUE\t\t:\t" + str( float(amount_due)) + "\n" footer_final = footer1 + footer2 + footer3 file.write(footer_final) file.write(lines) message = "\n\t\t*** THANK YOU... PLEASE VISIT US AGAIN ***" file.write(message) self.file_name = filename file.close() def printbill(self): print("the save btn status is ", self.save_btn.isEnabled()) if not self.save_btn.isEnabled(): self.billcontent() os.startfile(self.file_name, 'print') else: QMessageBox.warning( self, 'Warning', 'Please save the data first before printing the bill.')
class Add_Daily_Bill(QDialog, Ui_Daily_bill): def __init__(self, parent=None): super(Add_Daily_Bill, self).__init__(parent) self.setupUi(self) self.onlyint = QtGui.QIntValidator() self.phone_le.setValidator(self.onlyint) self.bill_le.setValidator(self.onlyint) self.total_le.setValidator(self.onlyint) self.recieved_le.setValidator(self.onlyint) self.due_le.setValidator(self.onlyint) self.cal_tool_btn.clicked.connect(self.delivery_calender) self.bill_generator() self.current_date() self.table_records() def connectdb(self): global cur global connect connect = pyodbc.connect('Driver={SQL SERVER};' 'Server=DHANALAKSHMI_PC\SQLEXPRESS;' 'Database=VASADB;' 'Trusted_Connection=yes;') cur = connect.cursor() return cur def table_records(self): self.viewtable.setRowCount(0) header = self.viewtable.horizontalHeader() header.setSectionResizeMode(0, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(1, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(2, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(3, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(4, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(5, QtWidgets.QHeaderView.Stretch) header.setStyleSheet("QHeaderView::section { border: 1px solid ;}") self.combovalue() self.newrow() #self.viewtable.setCellWidget(0,1,self.category_combo) #self.viewtable.setCellWidget(0,2,self.frame_combo) #self.category_combo.currentIndexChanged.connect(self.newrow) def newrow(self): curr_value = self.category_combo.currentText() #curr_value1 = self.combo1.currentText() self.combo1 = QComboBox() self.combo2 = QComboBox() self.combo1.addItems(self.combolist) self.combo1.setCurrentText('NA') self.combo2.addItems(self.framelist) self.combo2.setCurrentText('NA') rowposition = self.viewtable.rowCount() print('The number of rows is ', rowposition) self.viewtable.insertRow(rowposition) #print(self.viewtable.row()) self.viewtable.setItem(rowposition, 0, QTableWidgetItem(str(rowposition + 1))) self.viewtable.setCellWidget(rowposition, 1, self.combo1) self.viewtable.setCellWidget(rowposition, 2, self.combo2) self.combo1.currentIndexChanged.connect(self.framecomboassign) print('the value is ', self.combo1.currentText()) if self.combo1.currentText() == 'FRAME': self.combo2.setEnabled(True) else: self.combo2.setEnabled(False) print('The current row is ', self.viewtable.currentRow()) print('the row position is ', rowposition) current_row = self.viewtable.currentRow() if current_row == -1: current_row = 0 print(current_row) if current_row >= rowposition: self.combo1.currentIndexChanged.connect(self.newrow) def framecomboassign(self): current_value = self.combo1.currentText() print(current_value) if current_value == 'FRAME': self.combo2.setEnabled(True) else: self.combo2.setEnabled(False) def combovalue(self): self.combolist = set() self.framelist = set() sel_query = 'SELECT PROD_NAME,SIZE from dbo.PROD_DETAILS' self.connectdb() cur.execute(sel_query) result = cur.fetchall() for i in result: x = i[0] self.combolist.add(x) self.combolist.add('NA') frame_query = "SELECT SIZE FROM dbo.PROD_DETAILS WHERE PROD_NAME ='FRAME' AND SIZE IS NOT NULL AND SIZE !=''" cur.execute(frame_query) frame_result = cur.fetchall() for j in frame_result: x = j[0] self.framelist.add(x) self.framelist.add('NA') self.category_combo = QComboBox() self.category_combo.addItems(self.combolist) self.category_combo.setCurrentText('NA') self.frame_combo = QComboBox() self.frame_combo.addItems(self.framelist) self.frame_combo.setCurrentText('NA') def bill_generator(self): sel_query = 'SELECT MAX(BILL_NO) FROM dbo.BILLING_TABLE' self.connectdb() cur.execute(sel_query) result = cur.fetchall() value = result[0][0] if value != None: value = value + 1 print(value) else: value = 1 print('The else condition ', value) print(type(value)) self.bill_le.setText(str(value)) def current_date(self): now = QDate.currentDate() print(now) print(now.toString(Qt.ISODate)) today = datetime.date.today() date_format = today.strftime("%d/%m/%Y %A") print(date_format) self.date_le.setText(date_format) def delivery_calender(self): self.calender = QCalendarWidget() self.calender.setMinimumDate(QDate(1900, 1, 1)) self.calender.setMaximumDate(QDate(2999, 12, 31)) self.calender.setGridVisible(True) self.calender.clicked.connect(self.updatedate) self.calender.show() def updatedate(self, *args): getdate = self.calender.selectedDate().toString("dd/MM/yyyy") print('get date value is ', getdate) self.delivery_date_le.setText(getdate) self.calender.deleteLater()
class Expense_Report_Page(QDialog,Ui_expense_report): def __init__(self,parent=None): super(Expense_Report_Page,self).__init__(parent) self.setupUi(self) self.setWindowTitle("Expense Report") self.table_records() self.from_cal_tool_btn.clicked.connect(self.from_calender) self.to_cal_tool_btn.clicked.connect(self.to_calender) self.current_date() self.show_btn.clicked.connect(self.table_population) self.export_btn.clicked.connect(self.exportbtn) self.close_btn.clicked.connect(self.closebtn) config_name = 'expense_report.cfg' # determine if application is a script file or frozen exe if getattr(sys, 'frozen', False): application_path = os.path.dirname(sys.executable) elif __file__: application_path = os.path.dirname(__file__) config_path = os.path.join(application_path, config_name) icon_image = os.path.join(application_path, "images", "VASA_ICON.png") self.setWindowIcon(QtGui.QIcon(icon_image)) excel_image = os.path.join(application_path, "images", "excel.png") close_image = os.path.join(application_path, "images", "close.png") show_image = os.path.join(application_path, "images", "search.png") self.export_btn.setIcon(QtGui.QIcon(excel_image)) self.export_btn.setIconSize(QtCore.QSize(30, 30)) self.close_btn.setIcon(QtGui.QIcon(close_image)) self.close_btn.setIconSize(QtCore.QSize(30, 30)) self.show_btn.setIcon(QtGui.QIcon(show_image)) self.show_btn.setIconSize(QtCore.QSize(30, 30)) def connectdb(self): global cur global connect cur, con = Add_Odbc_Connection.connectdb(self) connect = con return cur '''global cur global connect connect = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};' 'Server=DHANALAKSHMI_PC\SQLEXPRESS;' 'Database=VASADB;' 'Trusted_Connection=yes;') cur = connect.cursor() return cur''' def table_records(self): self.expense_table.setRowCount(0) self.expense_table.verticalHeader().setVisible(False) header = self.expense_table.horizontalHeader() header.setSectionResizeMode(0, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(1, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(2, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(3, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(4, QtWidgets.QHeaderView.ResizeToContents) header.setStyleSheet("QHeaderView::section { border: 1px solid ;}") def from_calender(self): self.from_calender = QCalendarWidget() self.from_calender.setMinimumDate(QDate(1900,1,1)) self.from_calender.setMaximumDate(QDate(2999,12,31)) self.from_calender.setGridVisible(True) self.from_calender.clicked.connect(self.from_updatedate) self.from_calender.setWindowModality(Qt.ApplicationModal) self.from_calender.setWindowTitle("From Date") self.from_calender.show() def to_calender(self): self.to_calender = QCalendarWidget() self.to_calender.setMinimumDate(QDate(1900,1,1)) self.to_calender.setMaximumDate(QDate(2999,12,31)) self.to_calender.setGridVisible(True) self.to_calender.clicked.connect(self.to_updatedate) self.to_calender.setWindowModality(Qt.ApplicationModal) self.to_calender.setWindowTitle("To Date") self.to_calender.show() def from_updatedate(self,*args): getdate = self.from_calender.selectedDate().toString("dd-MMM-yyyy") print('From date value is ',getdate) self.from_date.setText(getdate) self.from_calender.deleteLater() def to_updatedate(self,*args): getdate = self.to_calender.selectedDate().toString("dd-MMM-yyyy") print('to date value is ',getdate) self.to_date.setText(getdate) self.to_calender.deleteLater() def current_date(self): now = QDate.currentDate() print(now) print(now.toString(Qt.ISODate)) today = datetime.date.today() date_format = today.strftime("%d-%b-%Y") print(date_format) self.from_date.setText(date_format) self.to_date.setText(date_format) def table_population(self): self.connectdb() self.expense_table.setRowCount(0) from_date = self.from_date.text() from_date =datetime.datetime.strptime(from_date,'%d-%b-%Y').date() self.table_from_date =from_date print("The from date value is ",from_date) to_date = self.to_date.text() to_date=datetime.datetime.strptime(to_date,'%d-%b-%Y').date() self.table_to_date = to_date select_query = "SELECT ROW_NUMBER() OVER (ORDER BY EXPENSE_DATE DESC) RNO,EXPENSE_NAME,AMOUNT,DESCRIPTION,EXPENSE_DATE FROM dbo.EXPENSE_DETAILS \ WHERE EXPENSE_DATE BETWEEN ? AND ?" cur.execute(select_query, (from_date, to_date,)) result = cur.fetchall() print("result is", result) self.data_result = [data for data in result] result_check = len(result) self.total_amount = 0 if result_check >= 1: new_result = [] column_count = self.expense_table.columnCount() for row in range(result_check): tuple_value = list(result[row]) tuple_value[4] = tuple_value[4].strftime('%d/%m/%Y') tuple_value = [str(value) for value in tuple_value if type(value) != 'str'] # tuple_value.extend(self.work_finish) print('the modified tuple value is ', tuple_value) self.total_amount = self.total_amount+int(float(tuple_value[2])) self.expense_table.insertRow(row) for column in range(column_count): value = tuple_value[column] self.expense_table.setItem(row, column, self.table_item(value, Qt.ItemIsSelectable | Qt.ItemIsEnabled)) print("The total amount value is ", self.total_amount) self.total_le.setText(str(round(float(self.total_amount), 2))) def table_item(self, text, flag): tablewidgetitem = QTableWidgetItem(text) tablewidgetitem.setFlags(flag) return tablewidgetitem def exportbtn(self): if self.expense_table.rowCount() >=1: file_path =QFileDialog.getSaveFileName(self, 'Export to Excel', "Expenses_Report_"+self.table_from_date.strftime("%d_%b_%Y")+ "_to_"+self.table_to_date.strftime("%d_%b_%Y")+".xlsx", 'Excel files (.xlsx) ;; All Files ()') print("the file path value is ",file_path) if file_path != ('', ''): print("File path is",file_path) excel_book_name = file_path[0] workbook = xlsxwriter.Workbook(excel_book_name) worksheet = workbook.add_worksheet("Expense Report") title_format = workbook.add_format({'bold' : True}) title_format.set_bg_color('yellow') title_format.set_font_color('brown') title_format.set_border() data = self.data_result cell_format = workbook.add_format() cell_format.set_border() money_format = workbook.add_format() money_format.set_border() money_format.set_num_format('#,##0.00') print("The first data is ",data) print("check value",data[0][0]) data_modified =[] list_value = [] data_modified = [tuple(str(item) for item in i) for i in data] print ( "The workbook Name is ",excel_book_name) title =["SL_NO","EXPENSE_NAME","AMOUNT","DESCRIPTION","EXPENSE_DATE"] title_size =[len(i) for i in title] print('the title size is',title_size) for col in range(0,len(title)): worksheet.write(0,col,title[col],title_format) for row_level in range(len(data_modified)): for col_level in range(len(data_modified[0])): if col_level ==2: value = int(float(data_modified[row_level][col_level])) worksheet.write(row_level + 1, col_level, value, money_format) else: value = data_modified[row_level][col_level] worksheet.write(row_level+1,col_level,value,cell_format) print ( " the row number value is",row_level) footer_list =["Total Expense Amount :"] total = int(float(self.total_amount)) footer_amt_list =[total] footer_format = workbook.add_format() footer_format.set_border() footer_format.set_bold(True) footer_format.set_font_size(14) footer_money_format = workbook.add_format() footer_money_format.set_border() footer_money_format.set_bold(True) footer_money_format.set_num_format('#,##0.00') footer_money_format.set_font_color('red') footer_money_format.set_font_size(14) row_num =row_level +6 for i in range(1): footer_format.set_font_color('black') worksheet.merge_range(row_num,0,row_num,2,footer_list[i],footer_format) worksheet.write(row_num, 3, footer_amt_list[i], footer_money_format) row_num+=1 worksheet.set_column(0,0,8) worksheet.set_column(3,3, 22) worksheet.set_column(1,1, 18) worksheet.set_column(2, 2, 10) worksheet.set_column(4, 4, 13) workbook.close() QMessageBox.information(self,"File Export","Expense Report Exported Successfully...") else: QMessageBox.warning(self,"Warning", "Please click show button to get data and then select Export") def closebtn(self): self.close()
class Work_Complete_Page(QDialog, Ui_work_complete): def __init__(self, parent=None): super(Work_Complete_Page, self).__init__(parent) self.setupUi(self) self.setWindowTitle("Work Completed") self.table_records() self.from_cal_tool_btn.clicked.connect(self.from_calender) self.to_cal_tool_btn.clicked.connect(self.to_calender) self.current_date() self.table_population() self.finish_btn.clicked.connect(self.table_population) config_name = 'work_complete.cfg' # determine if application is a script file or frozen exe if getattr(sys, 'frozen', False): application_path = os.path.dirname(sys.executable) elif __file__: application_path = os.path.dirname(__file__) config_path = os.path.join(application_path, config_name) icon_image = os.path.join(application_path, "images", "VASA_ICON.png") self.setWindowIcon(QtGui.QIcon(icon_image)) show_image = os.path.join(application_path, "images", "search.png") self.finish_btn.setIcon(QtGui.QIcon(show_image)) self.finish_btn.setIconSize(QtCore.QSize(50, 50)) def connectdb(self): global cur global connect cur, con = Add_Odbc_Connection.connectdb(self) connect = con return cur '''global cur global connect connect = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};' 'Server=DHANALAKSHMI_PC\SQLEXPRESS;' 'Database=VASADB;' 'Trusted_Connection=yes;') cur = connect.cursor() return cur''' def table_records(self): self.finish_table.setRowCount(0) self.finish_table.verticalHeader().setVisible(False) header = self.finish_table.horizontalHeader() header.setSectionResizeMode(0, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(1, QtWidgets.QHeaderView.ResizeToContents) header.setSectionResizeMode(2, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(3, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(4, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(5, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(6, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(7, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(8, QtWidgets.QHeaderView.Stretch) header.setSectionResizeMode(9, QtWidgets.QHeaderView.ResizeToContents) header.setStyleSheet("QHeaderView::section { border: 1px solid ;}") def from_calender(self): self.from_calender = QCalendarWidget() self.from_calender.setMinimumDate(QDate(1900, 1, 1)) self.from_calender.setMaximumDate(QDate(2999, 12, 31)) self.from_calender.setGridVisible(True) self.from_calender.clicked.connect(self.from_updatedate) self.from_calender.setWindowModality(Qt.ApplicationModal) self.from_calender.setWindowTitle("Delivery Date") self.from_calender.show() def to_calender(self): self.to_calender = QCalendarWidget() self.to_calender.setMinimumDate(QDate(1900, 1, 1)) self.to_calender.setMaximumDate(QDate(2999, 12, 31)) self.to_calender.setGridVisible(True) self.to_calender.clicked.connect(self.to_updatedate) self.to_calender.setWindowModality(Qt.ApplicationModal) self.to_calender.setWindowTitle("Delivery Date") self.to_calender.show() def from_updatedate(self, *args): getdate = self.from_calender.selectedDate().toString("dd-MMM-yyyy") print('From date value is ', getdate) self.from_date.setText(getdate) self.from_calender.deleteLater() def to_updatedate(self, *args): getdate = self.to_calender.selectedDate().toString("dd-MMM-yyyy") print('to date value is ', getdate) self.to_date.setText(getdate) self.to_calender.deleteLater() def current_date(self): now = QDate.currentDate() print(now) print(now.toString(Qt.ISODate)) today = datetime.date.today() date_format = today.strftime("%d-%b-%Y") print(date_format) self.from_date.setText(date_format) self.to_date.setText(date_format) def table_population(self): self.connectdb() self.finish_table.setRowCount(0) from_date = self.from_date.text() from_date = datetime.datetime.strptime(from_date, '%d-%b-%Y').date() print("The from date value is ", from_date) to_date = self.to_date.text() to_date = datetime.datetime.strptime(to_date, '%d-%b-%Y').date() select_query = "SELECT BILL_NO,CUSTOMER_NAME,PHONE_NO,EVENT_NAME,BOOKING_DATE,TOTAL_AMOUNT,\ WORK_START_DATE,WORK_FINISH_DATE,STUDIO_NAME,STATUS FROM dbo.WORK_ALLOCATE \ WHERE STATUS ='COMPLETED' AND WORK_FINISH_DATE BETWEEN ? AND ?" cur.execute(select_query, (from_date, to_date)) result = cur.fetchall() print("result is", result) result_check = len(result) print("The total number of records fetched from table is ", result_check) if result_check >= 1: new_result = [] column_count = self.finish_table.columnCount() for row in range(result_check): tuple_value = list(result[row]) tuple_value[4] = tuple_value[4].strftime('%d/%m/%Y') tuple_value[6] = tuple_value[6].strftime('%d/%m/%Y') tuple_value[7] = tuple_value[7].strftime('%d/%m/%Y') tuple_value = [ str(value) for value in tuple_value if type(value) != 'str' ] # tuple_value.extend(self.work_finish) print('the modified tuple value is ', tuple_value) self.finish_table.insertRow(row) for column in range(column_count): print("the row number is ", row) print("the column number is", column) value = tuple_value[column] self.finish_table.setItem( row, column, self.table_item(value, Qt.ItemIsSelectable | Qt.ItemIsEnabled)) def table_item(self, text, flag): tablewidgetitem = QTableWidgetItem(text) tablewidgetitem.setFlags(flag) return tablewidgetitem
class Update_Function_Bill(QDialog, Ui_new_function): def __init__(self, parent=None): super(Update_Function_Bill, self).__init__(parent) self.setupUi(self) self.setWindowTitle("Update Function Bill") self.onlyint = QtGui.QIntValidator() self.phone_le.setValidator(self.onlyint) self.bill_le.setValidator(self.onlyint) self.total_le.setValidator(self.onlyint) self.recieved_le.setValidator(self.onlyint) self.due_le.setValidator(self.onlyint) self.paid_le.setValidator(self.onlyint) self.paid_le.setPlaceholderText(str(0)) self.cal_tool_btn.clicked.connect(self.delivery_calender) self.recieved_le.textChanged.connect(self.amountdue) self.save_btn.clicked.connect(self.savebtn) self.clear_btn.clicked.connect(self.clearbtn) self.close_btn.clicked.connect(self.closebtn) self.print_btn.clicked.connect(self.printbill) self.connectdb() config_name = 'update_function_bill.cfg' # determine if application is a script file or frozen exe if getattr(sys, 'frozen', False): application_path = os.path.dirname(sys.executable) elif __file__: application_path = os.path.dirname(__file__) config_path = os.path.join(application_path, config_name) icon_image = os.path.join(application_path, "images", "VASA_ICON.png") self.setWindowIcon(QtGui.QIcon(icon_image)) save_image = os.path.join(application_path, "images", "save.png") print_image = os.path.join(application_path, "images", "print.png") clear_image = os.path.join(application_path, "images", "clear.png") close_image = os.path.join(application_path, "images", "close.png") self.calender_image = os.path.join(application_path, "images", "calender.png") self.save_btn.setIcon(QtGui.QIcon(save_image)) self.save_btn.setIconSize(QtCore.QSize(35, 35)) self.print_btn.setIcon(QtGui.QIcon(print_image)) self.print_btn.setIconSize(QtCore.QSize(35, 35)) self.clear_btn.setIcon(QtGui.QIcon(clear_image)) self.clear_btn.setIconSize(QtCore.QSize(35, 35)) self.close_btn.setIcon(QtGui.QIcon(close_image)) self.close_btn.setIconSize(QtCore.QSize(35, 35)) shortcut = QtWidgets.QShortcut( QtGui.QKeySequence(QtCore.Qt.Key_Tab), self.bill_le, context=QtCore.Qt.WidgetWithChildrenShortcut, activated=self.bill_value_fetch) def connectdb(self): global cur global connect cur, con = Add_Odbc_Connection.connectdb(self) connect = con return cur '''global cur global connect connect = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};' 'Server=DHANALAKSHMI_PC\SQLEXPRESS;' 'Database=VASADB;' 'Trusted_Connection=yes;') cur = connect.cursor() return cur''' def delivery_calender(self): self.calender = QCalendarWidget() self.calender.setMinimumDate(QDate(1900, 1, 1)) self.calender.setMaximumDate(QDate(2999, 12, 31)) self.calender.setGridVisible(True) self.calender.clicked.connect(self.updatedate) self.calender.setWindowModality(Qt.ApplicationModal) self.calender.setWindowTitle("Booking Date") self.calender.setWindowIcon(QtGui.QIcon(self.calender_image)) self.calender.show() def updatedate(self, *args): getdate = self.calender.selectedDate().toString("dd/MM/yyyy") print('get date value is ', getdate) self.booking_le.setText(getdate) self.calender.deleteLater() def bill_value_fetch(self): print("This function get passed") bill_no = self.bill_le.text() print('The bill no is ', bill_no) bill_value = int(bill_no) # find the function name: function_sel_query = "SELECT CATEGORY FROM DBO.ORDER_DETAILS WHERE BILL_NO =?" cur.execute(function_sel_query, bill_value) function_result = cur.fetchall() result_check = len(function_result) if result_check >= 1: function_name = function_result[0][0] print('the function name is ', function_name) non_marriage_select_query = "SELECT BILL.CUSTOMER_NAME,BILL.PHONE_NO,ORD.CATEGORY,ORD.TOTAL_AMOUNT,\ BILL.BILLING_DATE,\ BILL.TOTAL_AMOUNT,BILL.AMOUNT_RECIEVED,BILL.AMOUNT_DUE,BILL.FUNCTION_BOOKING_DATE\ FROM DBO.ORDER_DETAILS ORD , DBO.BILLING_TABLE BILL WHERE\ ORD.BILL_NO = BILL.BILL_NO\ AND BILL.BILL_NO = ? AND BILL_TYPE ='FUNCTION' " marriage_select_query = "SELECT BILL.CUSTOMER_NAME,BILL.PHONE_NO, ORD.CATEGORY,ORD.TOTAL_AMOUNT,\ BILL.BILLING_DATE,BILL.TOTAL_AMOUNT, \ BILL.AMOUNT_RECIEVED,BILL.AMOUNT_DUE,BILL.FUNCTION_BOOKING_DATE, PACKAGE.PACKAGE_NAME\ FROM DBO.ORDER_DETAILS ORD , DBO.BILLING_TABLE BILL, DBO.PROD_DETAILS PROD,\ DBO.PACKAGE_DETAILS PACKAGE WHERE ORD.BILL_NO = BILL.BILL_NO\ AND PROD.PROD_ID =ORD.PRD_ID\ AND PROD.PACKAGE_ID = PACKAGE.PACKAGE_ID\ AND BILL.BILL_NO = ? AND BILL_TYPE ='FUNCTION' AND PROD_TYPE ='FUNCTION'" if function_name == 'MARRIAGE': cur.execute(marriage_select_query, bill_no) result = cur.fetchall() else: cur.execute(non_marriage_select_query, bill_no) result = cur.fetchall() result_check = len(result) print('the test result length is', result_check) print('the test results are', result) print(" The output result value is", result) bill_tuple_list = list(result[0]) print("the new bill result is ", bill_tuple_list) bill_tuple_list[4] = bill_tuple_list[4].strftime('%d/%m/%Y') bill_tuple_list[8] = bill_tuple_list[8].strftime('%d/%m/%Y') bill_tuple_list = [ str(data) for data in bill_tuple_list if type(data) != 'str' ] print("the new value for bill list is ", bill_tuple_list) db_customer_name = bill_tuple_list[0] db_phone_no = bill_tuple_list[1] db_function_name = bill_tuple_list[2] db_order_total_amount = bill_tuple_list[3] db_billing_date = bill_tuple_list[4] db_bill_total_amount = bill_tuple_list[5] db_amount_paid = bill_tuple_list[6] db_amount_due = bill_tuple_list[7] db_booking_date = bill_tuple_list[8] if db_function_name == 'MARRIAGE': db_package_name = bill_tuple_list[9] else: db_package_name = 'NA' print('the package name is', db_package_name) self.customer_le.setText(db_customer_name) self.phone_le.setText(db_phone_no) self.function_le.setText(db_function_name) self.total_le.setText(db_bill_total_amount) self.paid_le.setText(db_amount_paid) self.due_le.setText(db_amount_due) self.date_le.setText(db_billing_date) self.package_le.setText(db_package_name) self.booking_le.setText(db_booking_date) else: QMessageBox.warning(self, 'Error', 'Please enter correct Bill No.') self.bill_le.clear() def amountdue(self): print('Amount due function in') print("the test value is ", self.due_le.text()) print('the total amount is ', self.total_le.text()) if self.paid_le.text() == "": amount_paid_prev = float(0) else: amount_paid_prev = float(self.paid_le.text()) if self.total_le.text() == "": total_amount = float(0) else: total_amount = float(self.total_le.text()) amount_due = total_amount - amount_paid_prev if self.recieved_le.text() == "": Recievedamount = float(0) else: Recievedamount = float(self.recieved_le.text()) print('Amount due is ', amount_due) print('amount recieved is ', Recievedamount) balance = float(amount_due - Recievedamount) print('The balanced amount is ', balance) self.due_le.setText(str(balance)) def savebtn(self): if self.bill_le.text() != "": billno = int(self.bill_le.text()) customer_name = self.customer_le.text() phone_no = self.phone_le.text() booking_date = self.booking_le.text() amount_paid_now = self.recieved_le.text() amount_recieved_prev = int(float(self.paid_le.text())) function_name = self.function_le.text() package_name = self.package_le.text() if amount_paid_now == '': amount_paid_now = '0' print('amount paid now is ', amount_paid_now) print('amount paid previous is ', amount_recieved_prev) amount_recieved = int(amount_recieved_prev + int(amount_paid_now)) print('amount received sum is', amount_recieved) if self.due_le.text() == '': self.due_le.setText('0') amount_due = float(self.due_le.text()) int_phone_no = int(phone_no) booking_date = datetime.datetime.strptime(booking_date, '%d/%m/%Y').date() int_amount_due = int(amount_due) update_query = "UPDATE dbo.BILLING_TABLE SET CUSTOMER_NAME =?,PHONE_NO =?,FUNCTION_BOOKING_DATE=?,\ AMOUNT_RECIEVED=?,AMOUNT_DUE=? WHERE BILL_NO =? AND BILL_TYPE='FUNCTION'" update_data = [ customer_name, int_phone_no, booking_date, amount_recieved, int_amount_due, billno ] print("the update data is ", update_data) cur.execute(update_query, update_data) connect.commit() self.billno = billno self.customer_name = customer_name self.phone_number = phone_no self.total_amount = self.total_le.text() self.amount_recieved = amount_paid_now self.due_amount = amount_due self.function_name = function_name self.package_name = package_name self.booking_date = booking_date if int(amount_due) == 0 and int(amount_paid_now) != 0: QMessageBox.information(self, 'Message', 'Bill is successfully closed') else: QMessageBox.information(self, 'Message', 'Bill is updated Successfully') self.save_btn.setEnabled(False) else: QMessageBox.warning(self, "Warning", "Please enter the Bill No") def clearbtn(self): columns_list = [ self.bill_le, self.customer_le, self.phone_le, self.booking_le, self.total_le, self.recieved_le, self.due_le, self.function_le, self.package_le, self.paid_le ] for i in columns_list: i.clear() self.total_le.setText(str(0)) print("the clearbtn total amt value is ", self.total_le.text()) self.recieved_le.setText(str(0)) self.due_le.setText(str(0)) def closebtn(self): self.close() def billcontent(self): folder_path = "D:\\Python\\Project\\bills\\" timenow = datetime.datetime.now() year = str(timenow.year) month = timenow.strftime("%B") print("the year is", year) print("the month is ", month) time = str(timenow.strftime("%H-%M")) footer_time = str(timenow.strftime("%I:%M %p")) print("the footer time is ", footer_time) today = datetime.date.today() bill_number = str(self.billno) cust_name = self.customer_name place = 'Mannargudi' lines = "\n====================================================================" today_date = today.strftime("%d/%m/%Y") phoneno = str(self.phone_number) total_amount = self.total_amount amount_paid = self.amount_recieved amount_due = self.due_amount function_name = self.function_name package_name = self.package_name book = str(self.booking_date) print("the time is ", time, " and the format is ", type(time)) folder = folder_path + year + "\\" + month + "\\" + str(today) + '\\' filename = str(folder) + "Update_Function_Bill_" + str(time) + ".rtf" print("the file name is ", filename) if not os.path.exists(folder): os.makedirs(folder) file = open(filename, 'w+') # file = open('D:/Python/bills/2019-12-30/Daily_Bill_16:49:50.docx','w+') # file =open('C:\\bill\\Daily_Bill.rtf','w') header = "\n\n\t\t\t\t VASA PHOTOGRAPHY" address1 = "\n\t\t\t No.100, Balakrishna Nagar," address2 = "\n\t\t\t\tKeerthi Clinic [opp]," address3 = "\n\t\t\t\t Mannargudi -614001" address4 = "\n\n Phone: 9944332270" address5 = "\t\t\t\tEmail: [email protected]" title = "\n\n\t\t\t\t\"FUNCTION BILL\"\n====================================================================" bill_section = "\n\n Bill No\t: " + bill_number + "\t\t\t\tDate : " + today_date + "\n" bill_section2 = "\n\n Customer Name\t\t: " + cust_name bill_section21 = "\n\n Customer Phone No. \t: " + phoneno bill_section22 = "\n\n Function Name \t\t: " + function_name bill_section23 = "\n\n Booking Date \t\t: " + book bill_section_package = "\n\n Package Name \t\t: " + package_name bill_section_combined = bill_section2 + bill_section21 + bill_section22 + bill_section23 + "\n" + lines bill_section_combined_marriage = bill_section2 + bill_section21 + bill_section22 + bill_section_package + bill_section23 + "\n" + lines if function_name == 'MARRIAGE': final = header + address1 + address2 + address3 + address4 + address5 + title + bill_section + lines + bill_section_combined_marriage file.write(final) else: final = header + address1 + address2 + address3 + address4 + address5 + title + bill_section + lines + bill_section_combined file.write(final) footer1 = "\n\n TIME :" + footer_time + "\t\t\t\tTOTAL AMOUNT\t:\t" + str( float(total_amount)) footer2 = "\n\t\t\t\t\t\tAMOUNT PAID\t\t:\t" + str(float(amount_paid)) footer3 = "\n SIGN :\t\t\t\t\tAMOUNT DUE\t\t:\t" + str( float(amount_due)) + "\n" footer_final = footer1 + footer2 + footer3 file.write(footer_final) file.write(lines) message = "\n\t\t*** THANK YOU... PLEASE VISIT US AGAIN ***" file.write(message) self.file_name = filename file.close() def printbill(self): print("the save btn status is ", self.save_btn.isEnabled()) if not self.save_btn.isEnabled(): self.billcontent() os.startfile(self.file_name, 'print') else: QMessageBox.warning( self, 'Warning', 'Please save the data first before printing the bill.')