def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self.ok_icon = JPPub().MainForm.getIcon('yes.ico')
self.cancel_icon = JPPub().MainForm.getIcon('cancel.ico')
self.delivery_icon = JPPub().MainForm.getIcon('delivery.png')
def __init__(self):
super().__init__()
self.CopyInfo = JPPub().getCopysInfo('BillCopys_QuotationOrder')
self.Copys = len(self.CopyInfo)
def init_ReportFooter(self):
RF = self.ReportFooter
RF.AddItemRect(1, (430, 0, 140, 20),
"金额合计SubTotal:",
Font=self.font_YaHei_8,
AlignmentFlag=Qt.AlignRight)
RF.AddItemRect(3, (570, 0, 80, 20),
"fAmount",
FormatString='{:,.2f}',
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
RF.AddItemRect(1, (430, 20, 140, 20),
"折扣Desconto:",
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
RF.AddItemRect(3, (570, 20, 80, 20),
"fDesconto",
AlignmentFlag=Qt.AlignRight,
FormatString='{:,.2f}',
Font=self.font_YaHei_8)
RF.AddItemRect(1, (430, 40, 140, 20),
"税金IVA:",
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
RF.AddItemRect(3, (570, 40, 80, 20),
"fTax",
FormatString='{:,.2f}',
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
RF.AddItemRect(1, (430, 60, 140, 20),
"应付金额Valor a Pagar:",
Font=self.font_YaHei_8,
AlignmentFlag=Qt.AlignRight)
RF.AddItemRect(3, (570, 60, 80, 20),
"fPayable",
FormatString='{:,.2f}',
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
RF.AddItemRect(3, (0, 0, 430, 80),
"fNote1",
FormatString='备注Note:\n{}',
Bolder=True,
AlignmentFlag=(Qt.AlignLeft | Qt.TextWordWrap),
Font=self.font_YaHei_8)
# 签字部分
RF.AddItemRect(1, (0, 110, 100, 20),
'制作人 Productor:',
Bolder=False,
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
RF.AddItemRect(1, (100, 125, 100, 0), '')
RF.AddItemRect(1, (420, 110, 120, 20),
'审核人 Aprovar::',
Bolder=False,
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
RF.AddItemRect(1, (540, 125, 100, 0), '')
noteStr = JPPub().ConfigData()['Bank_Account'].split('\n')
#noteStr = JPDb().getOnConfigValue('Bank_Account', str).split('\n')
self.Arial_Black = QFont("Arial")
self.Arial_Black.setPointSize(8)
self.Arial_Black.setBold(True)
for i, txt in enumerate(noteStr):
RF.AddItem(1,
5,
135 + i * 15,
650,
20,
txt,
Bolder=False,
AlignmentFlag=(Qt.AlignLeft | Qt.TextWordWrap),
Font=self.Arial_Black)
# 下面的框要删除，因为跨页
RF.AddItemRect(1, (0, 135, 650, len(noteStr) * 15), " ", Bolder=True)
self.PageFooter.AddItemRect(4, (10, 0, 100, 20),
'',
FormatString='Page: {Page}/{Pages}',
Bolder=False,
AlignmentFlag=Qt.AlignLeft,
Font=self.font_YaHei_8)
self.PageFooter.AddItemRect(
5, (110, 0, 540, 20),
'',
FormatString="PrintTime: %Y-%m-%d %H:%M:%S",
Bolder=False,
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
class EditForm_Product(JPFormModelMain):
def __init__(self, sql_main, PKValue, edit_mode, flags=Qt.WindowFlags()):
super().__init__(Ui_Form_Edit(),
sql_main=sql_main,
PKValue=PKValue,
edit_mode=edit_mode,
flags=flags)
JPPub().MainForm.addLogoToLabel(self.ui.label_logo)
JPPub().MainForm.addOneButtonIcon(self.ui.butSave, 'save.png')
JPPub().MainForm.addOneButtonIcon(self.ui.butCancel, 'cancel.png')
self.ui.fProductPic.hide()
self.readData()
pic = self.ui.label_Tax_Registration
self.ui.fID.setEnabled(False)
self.ui.fProductName.setFocus()
pic.to_FullPath = None
pic.NewFileName = None
pic.setScaledContents(False)
pic.setWordWrap(True)
self.dispPixmap = None
fn_m = self.mainTableFieldsInfo.DataRows[0].Datas[8]
if fn_m:
try:
self.dispPixmap = JPPub().MainForm.getTaxCerPixmap(fn_m)
except FileExistsError as e:
self.ui.label_Tax_Registration.setText('File not found!')
# QMessageBox.warning(JPPub().MainForm, '错误', e.Msg)
else:
self.dispPixmap = JPPub().MainForm.getPixmap('big_certificate.png')
if self.isReadOnlyMode:
self.ui.btn_SelectPic.setEnabled(False)
self.toPath = JPPub().getConfigData()['TaxRegCerPath']
def resizeEvent(self, resizeEvent):
if self.dispPixmap:
size = self.ui.label_Tax_Registration.size()
Pixmap = self.dispPixmap.scaled(size, Qt.KeepAspectRatio)
self.ui.label_Tax_Registration.setPixmap(Pixmap)
def onFirstHasDirty(self):
self.ui.butSave.setEnabled(True)
@pyqtSlot()
def on_butCancel_clicked(self):
self.close()
@pyqtSlot()
def on_btn_SelectPic_clicked(self):
fileName_choose, filetype = QFileDialog.getOpenFileName(
JPPub().MainForm,
"Select a Jpeg File",
os.getcwd(), # 起始路径
"Jpeg Files (*.jpg)")
if not fileName_choose:
return
pic = self.ui.label_Tax_Registration
pic.NewFileName = fileName_choose
r_path, r_file = os.path.split(fileName_choose)
fn_split = r_file.split(".")
newName = GetFileMd5(fileName_choose)
toPath = self.toPath
fn_m = f'tax_reg_{newName}'
fn_e = fn_split[len(fn_split) - 1]
pic.to_FullPath = f"{toPath}\\{fn_m}.{fn_e}"
saveName = f'{fn_m}.{fn_e}'
self.ui.fProductPic.refreshValueNotRaiseEvent(saveName, True)
pic.setScaledContents(True)
pic.setPixmap(QPixmap(fileName_choose))
@pyqtSlot()
def on_butSave_clicked(self):
try:
lst0 = self.getSqls(self.PKRole)
lst = lst0 + [JPDb().LAST_INSERT_ID_SQL()]
isOK, result = JPDb().executeTransaction(lst)
if isOK:
self.ui.butSave.setEnabled(False)
self.afterSaveData.emit(str(result))
self.__SavePic(result)
#JPPub().INITProduct()
QMessageBox.information(self, '完成',
'保存数据完成！\nSave data complete!')
except Exception as e:
msgBox = QMessageBox(QMessageBox.Critical, u'提示', str(e))
msgBox.exec_()
def __SavePic(self, result):
pic = self.ui.label_Tax_Registration
if not (pic.to_FullPath and pic.NewFileName):
return
try:
myCopy(pic.NewFileName, pic.to_FullPath)
except Exception as e:
raise MyCopyFileError(pic.NewFileName, pic.to_FullPath, str(e))
def onAfterSaveData(self, data):
act = 'new' if self.isNewMode else 'edit'
JPPub().broadcastMessage(tablename="t_Product", action=act, PK=data)
super().onAfterSaveData(data)
def __init__(self, parent=None):
super(MyButtonDelegate, self).__init__(parent)
self.icon = self.icon = JPPub().MainForm.getIcon('del_line.ico')
def _setEditFormButtonsIcon(ui):
pub = JPPub()
fun = pub.MainForm.addOneButtonIcon
fun(ui.butSave, "save.png")
fun(ui.butPrint, "print.png")
fun(ui.butPDF, "pdf.png")
def onGetFieldsRowSources(self):
pub = JPPub()
u_lst = [[item[1], item[0]] for item in JPUser().getAllUserList()]
return [('fCustomerID', pub.getCustomerList(), 1),
('fPaymentMethodID', pub.getEnumList(3), 1),
('fPayeeID', u_lst, 1), ('fOrderID', [['DIBOTO']], 0)]
def setAppIcon(self, logoName: str):
'''设置主应用程序图标,参数为图标文件名'''
icon = QIcon()
icon.addPixmap(QPixmap(JPPub().getIcoPath(logoName)))
self.__mainForm.setWindowIcon(icon)
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self.tabFont = QFont("Times", 10)
self.tabFont.setBold(False)
self.tabFont.setStretch(150)
self.ok_icon = JPPub().MainForm.getIcon('yes.ico')
def show(self):
"""启动应用程序"""
# 启动数据改变事件的监听
JPPub().receiveMessage(self.__app)
self.__mainForm.showMaximized()
sys_exit(self.__app.exec_())
def setMainFormLogo(self, logoName: str):
'''设置主窗体Logo图标,参数为图标文件名'''
pixmap = QPixmap(JPPub().getLogoPath(logoName))
self.__mainForm.ui.label_logo.setPixmap(pixmap)
self.__mainForm.logoPixmap = pixmap
def addOneButtonIcon(self, btn, icoName):
icon = QIcon(JPPub().getIcoPath(icoName))
btn.setIcon(icon)
def getPixmap(self, icoName) -> QPixmap:
return QPixmap(JPPub().getIcoPath(icoName))
def getIcon(self, icoName) -> QIcon:
return QIcon(JPPub().getIcoPath(icoName))
def __init__(self,
curdate,
cur_tab,
tongji_tab,
dateString,
PaperSize=QPrinter.A4,
Orientation=QPrinter.Orientation(0)):
super().__init__(PaperSize, Orientation)
self.configData = JPPub().getConfigData()
self.font_YaHei = QFont("Microsoft YaHei")
self.font_YaHei_8 = QFont(self.font_YaHei)
self.font_YaHei_8.setPointSize(8)
self.font_YaHei_10 = QFont(self.font_YaHei)
self.font_YaHei_10.setPointSize(20)
self.font_YaHei_10.setBold(True)
self.BackColor = JPPub().getConfigData(
)['PrintHighlightBackgroundColor']
rpt = self
rpt.logo = JPPub().MainForm.logoPixmap
rpt.ReportHeader.AddItemRect(2, (0, 0, 274, 50), rpt.logo)
rpt.ReportHeader.AddItemRect(1, (274, 0, 446, 60),
'de vendas diárias 收款日报表',
Bolder=False,
AlignmentFlag=(Qt.AlignCenter),
Font=self.font_YaHei_10)
rpt.ReportHeader.AddItemRect(1, (0, 50, 720, 20),
'Date:{}'.format(curdate),
Bolder=False,
AlignmentFlag=(Qt.AlignRight),
Font=self.font_YaHei_8)
title = [
'序号\nID', '客户名\nCliente', '收款额\nAmount', '收款人\nfPayee',
'收款方式\nModoPago', '单据号\nOrderID', '备注\nNote'
]
fns = [
'fID', 'fCustomerName', 'fAmountCollected', 'fPayee',
'fPaymentMethod', 'fOrderID', 'fNote'
]
cols = 7
al_c = Qt.AlignCenter
al_r = (Qt.AlignVCenter | Qt.AlignRight)
al_l = (Qt.AlignVCenter | Qt.AlignLeft)
rpt.SetMargins(30, 30, 30, 30)
rpt.ReportHeader.AddPrintLables(0,
72,
40,
Texts=title,
Widths=[40, 210, 80, 80, 100, 120, 90],
Aligns=[al_c] * cols)
rpt.Detail.addPrintRowCountItem(0,
0,
40,
20,
AlignmentFlag=al_c,
Font=self.font_YaHei_8)
rpt.Detail.AddItem(
3,
40,
0,
210,
20,
fns[1],
FormatString=' {}',
AlignmentFlag=al_l,
# 超出长度省略
AutoShrinkFont=self.configData['AutoShrinkFonts'],
AutoEllipsis=self.configData['AutoEllipsis'],
Font=self.font_YaHei_8)
rpt.Detail.AddItemRect(3, (250, 0, 80, 20),
fns[2],
AlignmentFlag=al_r,
FormatString='{:,.2f} ',
Font=self.font_YaHei_8)
rpt.Detail.AddItemRect(3, (330, 0, 80, 20),
fns[3],
AlignmentFlag=al_c,
Font=self.font_YaHei_8)
rpt.Detail.AddItemRect(3, (410, 0, 100, 20),
fns[4],
AlignmentFlag=al_c,
Font=self.font_YaHei_8)
rpt.Detail.AddItemRect(3, (510, 0, 120, 20),
fns[5],
AlignmentFlag=al_l,
FormatString=' {}',
Font=self.font_YaHei_8)
rpt.Detail.AddItemRect(3, (630, 0, 90, 20),
fns[6],
AlignmentFlag=al_l,
FormatString=' {}',
Font=self.font_YaHei_8)
sum_j = 0
for i in range(len(cur_tab)):
sum_j += cur_tab.getOnlyData([i, 4])
rpt.ReportFooter.AddPrintLables(
0,
0,
20,
Texts=["合计Sum", JPGetDisplayText(sum_j), " "],
Widths=[250, 80, 390],
Aligns=[al_c, al_r, al_c],
FillColor=self.BackColor,
Font=self.font_YaHei_8)
sql_payable = f"""
SELECT SUM(fPayable) AS sumPayable,
COUNT(fOrderID) AS countOrderID
FROM v_all_sales
WHERE (fOrderDate = STR_TO_DATE('{dateString}', '%Y-%m-%d'))
"""
sql_SKFS = f"""
select if(isnull(Q.fPaymentMethod),'Sum合计',Q.fPaymentMethod) as skfs,
Q.今日收款,Q.今日收款笔数,Q.DIBOTO,Q.DIBOTO笔数,Q.Prepaid,Q.Prepaid笔数,Q.小计Subtotal,Q.笔数小计Subcount
from (
SELECT fPaymentMethod
, SUM(if(fOrderID = 'DIBOTO' or fOrderID = 'Prepaid', NULL, fAmountCollected)) AS 今日收款
, COUNT(if(fOrderID = 'DIBOTO' or fOrderID = 'Prepaid', NULL, fAmountCollected)) AS 今日收款笔数
, SUM(if(fOrderID = 'DIBOTO', fAmountCollected, NULL)) AS DIBOTO
, COUNT(if(fOrderID = 'DIBOTO', fAmountCollected, NULL)) AS DIBOTO笔数
, SUM(if(fOrderID = 'Prepaid', fAmountCollected, NULL)) AS Prepaid
, COUNT(if(fOrderID = 'Prepaid', fAmountCollected, NULL)) AS Prepaid笔数
, SUM(fAmountCollected) AS 小计Subtotal, COUNT(fAmountCollected) AS 笔数小计Subcount
FROM v_receivables
WHERE fReceiptDate=STR_TO_DATE('{dateString}', '%Y-%m-%d')
GROUP BY fPaymentMethod WITH ROLLUP) as Q
"""
title_height = 20
rpt.ReportFooter.AddItem(1,
0,
title_height,
720,
30,
"本日结算方式统计Today's settlement statistics",
Bolder=False,
AlignmentFlag=al_c)
title_height += 30
title = [
'方式PM', "收当日订单Today's Order Rec", '收欠款DIBOTO', '预付款Prepaid',
'小计SubTotle'
]
rpt.ReportFooter.AddPrintLables(0,
title_height,
25,
title,
Widths=[120, 150, 150, 150, 150],
Aligns=[al_c] * 5,
Font=self.font_YaHei_8)
tongji_tab = JPQueryFieldInfo(sql_SKFS)
title_height += 25
for r in range(len(tongji_tab)):
FillColor = QColor(255, 255,
255) if r < (len(tongji_tab) - 1) else QColor(
194, 194, 194)
rpt.ReportFooter.AddItem(1,
0,
title_height + r * 20,
120,
20,
tongji_tab.getDispText([r, 0]),
FormatString=' {}',
AlignmentFlag=al_l,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
120,
title_height + r * 20,
100,
20,
tongji_tab.getDispText([r, 1]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
220,
title_height + r * 20,
50,
20,
tongji_tab.getDispText([r, 2]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
270,
title_height + r * 20,
100,
20,
tongji_tab.getDispText([r, 3]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
370,
title_height + r * 20,
50,
20,
tongji_tab.getDispText([r, 4]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
420,
title_height + r * 20,
100,
20,
tongji_tab.getDispText([r, 5]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
520,
title_height + r * 20,
50,
20,
tongji_tab.getDispText([r, 6]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
570,
title_height + r * 20,
100,
20,
tongji_tab.getDispText([r, 7]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
670,
title_height + r * 20,
50,
20,
tongji_tab.getDispText([r, 8]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
# 总结部分
title_height += (len(tongji_tab) - 1) * 20
title_height += 40
title = [
"当日订单应付Today's Order Payable",
"收当日订单Today's Order Rec",
'欠款Arrears',
]
rpt.ReportFooter.AddPrintLables(120,
title_height,
25,
title,
Widths=[200, 200, 200],
Aligns=[al_c] * 3,
Font=self.font_YaHei_8)
rpt.ReportFooter.AddItem(1,
0,
title_height,
120,
45,
'总结\nsummary',
FormatString='{} ',
AlignmentFlag=al_c,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
title_height += 25
payable_tab = JPQueryFieldInfo(sql_payable)
v1 = payable_tab.getOnlyData([0, 0])
v2 = tongji_tab.getOnlyData([len(tongji_tab) - 1, 1])
v3 = '{:,.2f}'.format((v1 if v1 else 0) - (v2 if v2 else 0))
ShouDangRiDingDan = tongji_tab.getDispText([len(tongji_tab) - 1, 1
]) + " " if v2 else "0 "
txt = [
payable_tab.getDispText([0, 0]) + " ",
JPGetDisplayText(len(payable_tab), str) + " ", ShouDangRiDingDan,
tongji_tab.getDispText([len(tongji_tab) - 1, 2]) + " ", v3 + " "
]
rpt.ReportFooter.AddPrintLables(120,
title_height,
20,
txt,
Widths=[150, 50, 150, 50, 200],
Aligns=[al_r] * 5,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
# 页脚
self.PageFooter.AddItemRect(4, (10, 0, 100, 20),
'',
FormatString='Page: {Page}/{Pages}',
Bolder=False,
AlignmentFlag=Qt.AlignLeft,
Font=self.font_YaHei_8)
self.PageFooter.AddItemRect(
5, (0, 0, 720, 20),
'',
FormatString="PrintTime: %Y-%m-%d %H:%M:%S",
Bolder=False,
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
self.DataSource = [
cur_tab.getRowValueDict(i) for i in range(len(cur_tab))
]
def onGetFieldsRowSources(self):
pub = JPPub()
u_lst = [[item[1], item[0]] for item in JPUser().getAllUserList()]
return [('fCustomerID', pub.getCustomerList(), 1),
('fVendedorID', pub.getEnumList(10), 1),
('fEntryID', u_lst, 1)]
def on_butInsertPageNumber_clicked(self):
QF = QFileDialog.getOpenFileName
path, filetype = QF(
self,
"Select a File",
JPPub().getOrSetlastOpenDir(), # 起始路径
"Files (*.pdf)")
if not path:
return
JPPub().getOrSetlastOpenDir(path)
def createPagePdf(num, tmp, pageWidths, benin):
c = canvas.Canvas(tmp)
for i in range(1, num + 1):
x = int(pageWidths[i - 1]) // 2.8333 // 2
no = benin + i - 1
c.drawString((x) * mm, (6) * mm, "—{}—".format(no))
c.showPage()
c.save()
return
# with open(tmp, 'rb') as f:
# pdf = PdfFileReader(f)
# layer = pdf.getPage(0)
# return layer
if len(sys.argv) == 1:
if not os.path.isfile(path):
sys.exit(1)
else:
path = sys.argv[1]
base = os.path.basename(path)
tmp = "__tmp.pdf"
batch = 10
batch = 0
output = PdfFileWriter()
with open(path, 'rb') as f:
pdf = PdfFileReader(f, strict=False)
n = pdf.getNumPages()
pageWidths = []
self.showInfo(n)
for p in range(n):
page = pdf.getPage(p)
pageWidths.append(page.artBox[2])
self.dispInfo("正在生成页码{}/{}".format(p + 1, n), p)
if batch == 0:
batch = -n
createPagePdf(n, tmp, pageWidths, self.ui.spinBoxBeginNo.value())
if not os.path.isdir('temp/'):
os.mkdir('temp/')
with open(tmp, 'rb') as ftmp:
numberPdf = PdfFileReader(ftmp)
self.showInfo(n)
for p in range(n):
# if not p % batch and p:
# newpath = path.replace(
# base, 'pdfWithNumbers/' + base[:-4] + '_page_%d' % (p//batch) + path[-4:])
# with open(newpath, 'wb') as f:
# output.write(f)
# output = PdfFileWriter()
self.dispInfo("正在写入页码{} of {}".format(p + 1, n), p)
print('page: %d of %d' % (p + 1, n))
page = pdf.getPage(p)
numberLayer = numberPdf.getPage(p)
page.mergePage(numberLayer)
output.addPage(page)
if output.getNumPages():
newpath = self.createNewSavePath(path, "加入页码")
self.dispInfo("正在写入文件{}，请耐心等待".format(newpath), p)
with open(newpath, 'wb') as f:
output.write(f)
print('完成!')
self.dispInfo("完成！", n)
os.remove(tmp)
def onAfterSaveData(self, data):
act = 'new' if self.isNewMode else 'edit'
JPPub().broadcastMessage(tablename="t_order", action=act, PK=data)
if self.isNewMode:
self.ui.fOrderID.refreshValueNotRaiseEvent(data, True)
def __init__(self, mainform):
super().__init__()
self.MainForm = mainform
self.ui = Ui_Form()
self.ui.setupUi(self)
#findButtonAndSetIcon(self)
mainform.addForm(self)
self.SQLCustomerArrearsList = """
select c.fCustomerID as 客户编号ID,
c.fCustomerName as `客户名Cliente`,
c.fCity as 城市City,
if( YS.fYS=0,null,YS.fYS) as `应收合计TotalReceivables`,
if(SK.fSK=0,null,SK.fSK) as `收款累计SumRec`,
if(if(isnull(YS.fYS),0,YS.fYS)-if(isnull(SK.fSK),0,SK.fSK)=0,null,if(isnull(YS.fYS),0,YS.fYS)-if(isnull(SK.fSK),0,SK.fSK)) as `欠款Arrears`,
r1.fReceiptDate as `最后收款日LastfReceiptDate`,
cast(r1.fAmountCollected as DECIMAL) as `最后收款额LastAmountCollected`
from t_customer as c left join
(select o.fCustomerID,
cast(sum(o.fPayable) as DECIMAL) as fYS
from v_all_sales as o
group by o.fCustomerID) as YS
on c.fCustomerID=YS.fCustomerID left join
(select r.fCustomerID,
cast(sum(r.fAmountCollected) as DECIMAL) as fSK,
max(r.fID) as LastRecID
from t_receivables as r
group by r.fCustomerID ) as SK
on c.fCustomerID=SK.fCustomerID
left join t_receivables as r1
on r1.fID=SK.LastRecID
where c.fCustomerID={CustomerID}
"""
self.SQLCustomerRecorder = """
select
Q.fDate as 日期OrderDate,
Q.fOrderID as 订单号码OrderID,
Q.fPayable as 应收金额Payable,
Q.fAmountCollected as 收款fAmountCollected
from
(
select
o.fOrderDate as fDate,
o.fOrderID,
cast(o.fPayable as DECIMAL) as fPayable,
null as fAmountCollected,
o.ts
from
v_all_sales as o
where
o.fCustomerID ={CustomerID}
union all
select
r.fReceiptDate as fDate,
Null as fOrderID,
Null as fPayable,
cast(r.fAmountCollected as DECIMAL) as fAmountCollected,
r.ts
from
t_receivables as r
where
r.fCustomerID ={CustomerID}
union all
select
null as fDate,
'Sum' as fOrderID,
cast(
sum(Q1.fPayable) as DECIMAL
) as fPayable,
cast(
sum(Q1.fAmountCollected) as DECIMAL
) as fAmountCollected,
null as ts
from
(
select
o.fOrderDate as fDate,
o.fOrderID,
cast(o.fPayable as DECIMAL) as fPayable,
null as fAmountCollected,
o.ts
from
v_all_sales as o
where
o.fCustomerID ={CustomerID}
union all
select
r.fReceiptDate as fDate,
Null as fOrderID,
Null as fPayable,
cast(r.fAmountCollected as DECIMAL),
r.ts
from
t_receivables as r
where
r.fCustomerID ={CustomerID}
) as Q1
) as Q
order by
Q.TS DESC
"""
self.SQLCurrentDayRec = """
SELECT fID as 流水号ID,
fCustomerID as 客户编号CustomerID,
fCustomerName as 客户名Cliente,
fReceiptDate as 收款日期ReceiptDate,
fAmountCollected as 收款额AmountCollected,
fPayee as 收款人fPayee,
fPaymentMethod AS 收款方式ModoPago,
fOrderID as 订单号OrderID,
fNote as 备注Note
FROM v_receivables as r
WHERE
r.fReceiptDate = STR_TO_DATE('{dateString}', '%Y-%m-%d')
order by fID DESC
"""
self.SQLSumPaymentMethod = """
SELECT e.fTitle AS `收款方式PaymentMethod` ,
sum(fAmountCollected) AS `收款合计Collection of receipts`,
count(fID) AS `收款笔数Number of Payments Received`
FROM t_receivables AS r
LEFT JOIN t_enumeration AS e
ON r.fPaymentMethodID=e.fItemID
WHERE
r.fReceiptDate = STR_TO_DATE('{dateString}', '%Y-%m-%d')
GROUP BY r.fPaymentMethodID
"""
self.ui.SelectDate.dateChanged.connect(self.dateChanged)
# 引发一次日期修改事件，刷新 左上、右上
self.ui.SelectDate.setDate(QDate.currentDate())
# 引发一次事件，刷新左下、右下
self.currentCustomerChanged()
self.pub = JPPub()
self.pub.UserSaveData.connect(self.UserSaveData)
self.MsgWindowsPoped = False
def __init__(self, MainForm):
super().__init__(MainForm)
self.MainForm = MainForm
sql_0 = """
SELECT o.fOrderID as 订单号码OrderID,
fOrderDate as 日期OrderDate,
fCustomerName as 客户名Cliente,
fCity as 城市City,
fSubmited1 as 提交Submited,
fSubmit_Name as 提交人Submitter,
fRequiredDeliveryDate as 交货日期RequiredDeliveryDate,
o.fAmount as 金额SubTotal,
fDesconto as 折扣Desconto,
fTax as 税金IVA,
fPayable as `应付金额Valor a Pagar`,
fContato as 联系人Contato,
fCelular as 手机Celular,
fSubmited AS fSubmited,
fEntry_Name as 录入Entry,
Null as ``,
t.fQuant AS '数量Qtd',
fProductName AS '名称Descrição',
fLength AS '长Comp.',
fWidth AS '宽Larg.',
t.fPrice AS '单价P. Unitario',
t.fAmount AS '金额Total'
FROM v_order AS o left join t_order_detail as t on o.fOrderID=t.fOrderID"""
sql_1 = sql_0 + """
WHERE fCanceled=0
AND left(o.fOrderID,2)='CP'
AND (fSubmited={ch1} OR fSubmited={ch2})
AND fOrderDate{date}
ORDER BY o.fOrderID DESC"""
sql_2 = sql_0 + """
WHERE fCanceled=0
AND left(o.fOrderID,2)='CP'
ORDER BY o.fOrderID DESC"""
self.backgroundWhenValueIsTrueFieldName = ['fSubmited']
self.checkBox_1.setText('Submited')
self.checkBox_2.setText('UnSubmited')
self.checkBox_1.setChecked(False)
self.checkBox_2.setChecked(True)
super().setListFormSQL(sql_1, sql_2)
self.tableView.setColumnHidden(13, True)
self.fSubmited_column = 13
self.pub = JPPub()
self.pub.UserSaveData.connect(self.UserSaveData)
m_sql = """
SELECT fOrderID as 订单号码OrderID
, fOrderDate as 日期OrderDate
, fVendedorID as 销售人员Vendedor
, fRequiredDeliveryDate as 交货日期RequiredDeliveryDate
, fCustomerID as 客户名Cliente
, fContato
, fCelular
, fAmount
, fTelefone
, fTax
, fPayable
, fDesconto
, fNote
,fEntryID
FROM t_order
WHERE fOrderID = '{}'
"""
s_sql = """
SELECT fID, fOrderID, fQuant AS '数量Qtd',
fProductName AS '名称Descrição',
fLength AS '长Comp.', fWidth AS '宽Larg.',
fPrice AS '单价P. Unitario', fAmount AS '金额Total'
FROM t_order_detail
WHERE fOrderID = '{}'
"""
self.setEditFormSQL(m_sql, s_sql)
frm = form_createor[self.menu_id](self)
else:
frm = Form_Background(self)
# 尝试给窗体添加按钮,要求窗体中有一个名为 “Layout_Button”的布局
self.addButtons(frm, btns)
return
def closeEvent(self, *args):
# 关闭主窗体前先关闭数据库游标
JPDb().close()
if __name__ == "__main__":
QApplication.setAttribute(Qt.AA_EnableHighDpiScaling)
QApplication.setStyle('Fusion')
app = QApplication(argv)
db = JPDb()
db.setDatabaseType(JPDbType.MySQL)
MainWindow = JPMainWindow()
icon = QIcon()
icon.addPixmap(
QPixmap(MainWindow.icoPath.format("medical_invoice_information.png")))
MainWindow.setWindowIcon(icon)
MainWindow.ui.splitter.setStretchFactor(0, 2)
MainWindow.ui.splitter.setStretchFactor(1, 11)
# 启动数据改变事件的监听
JPPub().receiveMessage(app)
MainWindow.showMaximized()
sys_exit(app.exec_())
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self.flag_icon = JPPub().MainForm.getIcon('flag_red.png')
def onAfterSaveData(self, data):
act = 'new' if self.isNewMode else 'edit'
JPPub().broadcastMessage(tablename="t_Product", action=act, PK=data)
super().onAfterSaveData(data)
def onAfterSaveData(self, data):
# 重新加载一次用户数据
JPUser().INIT()
act = 'new' if self.isNewMode else 'edit'
JPPub().broadcastMessage(tablename="sysusers", PK=data, action=act)
super().onAfterSaveData(data)