def dispDetail(self):
pid = -1
tv = self.ui.tableView
index = tv.selectionModel().currentIndex()
if index.isValid():
pid = self.dataInfo.getOnlyData([index.row(), 0])
d1 = JPDateConver(self.ui.dateBegin.date(), str)
d2 = JPDateConver(self.ui.dateEditEnd.date(), str)
self.sql_detail = f"""
SELECT q.fOrderDate AS 日期OrderDate,
q.fOrderID AS 单据号码OrderID,
ksmc AS 客商Merchants,
rk AS 入库In ,
ck AS 出库Out
FROM
(SELECT o.fOrderDate,
o.fOrderID,
s.fSupplierName AS ksmc,
d.fQuant AS rk,
null AS ck,
d.TS
FROM t_product_warehousereceipt_order_detail AS d
LEFT JOIN t_product_warehousereceipt_order AS o
ON d.fOrderID=o.fOrderID
LEFT JOIN t_supplier AS s
ON o.fSupplierID=s.fSupplierID
WHERE o.fOrderDate
BETWEEN '{d1}'
AND '{d2}'
AND fProductID={pid}
AND o.fSubmited=1
UNION all
SELECT o.fOrderDate,
o.fOrderID,
s.fCustomerName AS ksmc,
NULL AS rk,
d.fQuant AS ck,
d.TS
FROM t_product_outbound_order_detail AS d
LEFT JOIN t_product_outbound_order AS o
ON d.fOrderID=o.fOrderID
LEFT JOIN t_customer AS s
ON o.fCustomerID=s.fCustomerID
WHERE o.fOrderDate
BETWEEN '{d1}'
AND '{d2}'
AND fProductID={pid}
AND o.fSubmited=1
) AS q
ORDER BY q.Ts DESC
"""
self.dataInfo_detail = JPQueryFieldInfo(self.sql_detail)
self.mod3 = JPTableViewModelReadOnly(self.ui.tableView_rec,
self.dataInfo_detail)
self.ui.tableView_rec.setModel(self.mod3)
self.ui.tableView_rec.resizeColumnsToContents()
bz = (len(self.dataInfo_detail) > 0)
self.ui.CmdExportToExcel_Detail.setEnabled(bz)
self.ui.CmdPrint_Detail.setEnabled(bz)
def on_CmdPrint_clicked(self):
d1 = JPDateConver(self.ui.dateEdit_begin.date(), str)
d2 = JPDateConver(self.ui.dateEdit_end.date(), str)
if self.list_sql:
rpt = FormReport_ProductInfo_InOutDetail()
rpt.sql = self.list_sql.format(d1=d1, d2=d2)
rpt.beginDate = self.ui.dateEdit_begin.date()
rpt.endDate = self.ui.dateEdit_end.date()
rpt.initItem()
rpt.BeginPrint()
def actionClick(self, where_sql=None):
d1 = JPDateConver(self.ui.dateEdit_begin.date(), str)
d2 = JPDateConver(self.ui.dateEdit_end.date(), str)
tv = self.ui.tableView
self.dataInfo = JPQueryFieldInfo(self.list_sql.format(d1=d1, d2=d2))
self.mod = myJPTableViewModelReadOnly(tv, self.dataInfo)
tv.setModel(self.mod)
#tv.setItemDelegateForColumn(9, de)
tv.resizeColumnsToContents()
def on_CmdDailyRreport_clicked(self):
if len(self.tabinfoCurrentDayRec) == 0:
return
try:
p = FormReport_Rec_print(
JPDateConver(self.ui.SelectDate.date(), str),
self.tabinfoCurrentDayRec, self.tabinfoFangShiTongJi,
JPDateConver(self.ui.SelectDate.date(), str))
p.BeginPrint()
except Exception as e:
print(e)
pass
def _onValueChange(self, v):
v = self.date()
if self.minimumDate() == v and v == QDate(1752, 9, 14):
return
self.FieldInfo.Value = JPDateConver(self.date(), datetime.date)
if self.__RaiseEvent:
super()._onValueChange(self.FieldInfo.Value)
def getSqlValue(self) -> str:
v = self.date()
if self.minimumDate() == v and v == QDate(1752, 9, 14):
self.FieldInfo.Value = None
return self.getNullValue()
else:
self.clearStyleSheet()
return "'{}'".format(JPDateConver(self.date(), str))
def getSql(self, tablename):
sql = "select * from {fn}".format(fn=tablename)
con = JPDb().currentConn
cur = con.cursor()
cur.execute(sql)
con.commit()
tab = cur._result.rows
if len(tab) == 0:
return
c_col = len(cur._result.fields)
fns = ['`{}`'.format(r.name) for r in cur._result.fields]
fg = ", "
sql_ins = 'INSERT INTO `{tn}` ({f}) VALUES \n{v};'
values = []
vs = []
for r in range(len(tab)):
d = tab[r]
vs = []
for col in range(c_col):
v = d[col]
if v is None:
vs.append('Null')
continue
elif isinstance(v, str):
vs.append("'{}'".format(v.replace("'", "\\'")))
elif isinstance(v, (int, float, Decimal)):
vs.append('{}'.format(v))
elif isinstance(v, bytes):
v = 0 if v == bytes([0]) else 1
vs.append('{}'.format(v))
elif isinstance(v, (datetime.datetime, datetime.date)):
vs.append("'{}'".format(JPDateConver(v), str))
else:
raise TypeError("没有该类型的转换器【{v}】={t}".format(v=fns[col],
t=type(v)))
vs = [str(r) for r in vs]
values.append('(' + fg.join(vs) + ')')
self.exportOneRecord.emit()
sql_ins = sql_ins.format(tn=tablename,
f=fg.join(fns),
v=(fg + '\n\t').join(values))
return sql_ins
def dateChanged(self, s_data):
str_date = JPDateConver(self.ui.SelectDate.date(), str)
# 设置当前日收款记录（左上）
sql = self.SQLCurrentDayRec.format(dateString=str_date)
self.tabinfoCurrentDayRec = JPQueryFieldInfo(sql)
self.modCurrentDayRec = JPTableViewModelReadOnly(
self.ui.tabCurrentDayRec, self.tabinfoCurrentDayRec)
self.ui.tabCurrentDayRec.setModel(self.modCurrentDayRec)
self.ui.tabCurrentDayRec.resizeColumnsToContents()
self.ui.tabCurrentDayRec.selectionModel().currentRowChanged[
QModelIndex, QModelIndex].connect(self.currentCustomerChanged)
# 设置当前日收款方式统计（右上）
self.tabinfoFangShiTongJi = JPQueryFieldInfo(
self.SQLSumPaymentMethod.format(dateString=str_date))
self.modFangShiTongJi = JPTableViewModelReadOnly(
self.ui.SumPaymentMethod, self.tabinfoFangShiTongJi)
self.ui.SumPaymentMethod.setModel(self.modFangShiTongJi)
self.ui.SumPaymentMethod.resizeColumnsToContents()
def setModelData(self, editor: QWidget, model: QAbstractItemModel,
index: QModelIndex):
index.model().setData(index, JPDateConver(editor.date(),
datetime.date), Qt.EditRole)
def setEditorData(self, editor: QWidget, index: QModelIndex):
data = index.model().data(index, Qt.EditRole)
if data is None:
return
editor.setDate(JPDateConver(data, QDate))
def Value(self):
return JPDateConver(self.date(), datetime.date)
def refreshValueNotRaiseEvent(self, value):
self.__RaiseEvent = False
value = value if value else self.minimumDate()
self.FieldInfo.Value = JPDateConver(value, datetime.date)
self.setDate(JPDateConver(self.FieldInfo.Value, QDate))
self.__RaiseEvent = True
def __customerIDChanged(self):
# 刷新客户有关信息
sql = '''
SELECT fNUIT, fCity, fEndereco, fCelular, fContato
, fTelefone, Q0.fAmountPayable , Q1.fAmountPaid,
Q0.fAmountPayable-Q1.fAmountPaid as fArrears,-1 as fPaymentMethodID
FROM t_customer c
LEFT JOIN (
SELECT fCustomerID, SUM(fPayable) AS fAmountPayable
FROM v_all_sales
WHERE fCustomerID = {CustomerID}
) Q0
ON c.fCustomerID = Q0.fCustomerID
LEFT JOIN (
SELECT fCustomerID, SUM(fAmountCollected) AS fAmountPaid
FROM t_receivables
WHERE fCustomerID = {CustomerID}
) Q1
ON c.fCustomerID = Q1.fCustomerID
WHERE c.fCustomerID = {CustomerID}
'''
sql = sql.format(CustomerID=self.ui.fCustomerID.Value())
tab = JPQueryFieldInfo(sql)
obj_name = [
'fNUIT', 'fCity', 'fEndereco', 'fCelular', 'fContato', 'fTelefone',
'fAmountPayable', 'fAmountPaid', 'fArrears'
]
tup = (JPWidgets.QLineEdit, JPWidgets.QDateEdit, JPWidgets.QComboBox,
JPWidgets.QTextEdit, JPWidgets.QCheckBox)
fld_dict = tab.getRowFieldsInfoDict(0)
for i, nm in enumerate(obj_name):
obj = self.findChild(tup, nm)
if obj:
obj.setRowsData(tab.DataRows[0])
obj.setMainModel(self)
obj.setFieldInfo(fld_dict[nm])
obj.refreshValueNotRaiseEvent(tab.getOnlyData([0, i]), True)
# 刷新该客户名下当日单据号码
sql_orderID = """
select 'DIBOTO' as fOrderID
union all
select 'Prepaid' as fOrderID
union all
select fOrderID from t_order
where fCustomerID={CustomerID}
and fOrderDate=STR_TO_DATE('{dateString}', '%Y-%m-%d')
and fConfirmed=1
and fSubmited=1
and fCanceled=0
union all
select fOrderID from t_product_outbound_order
where fCustomerID={CustomerID}
and fOrderDate=STR_TO_DATE('{dateString}', '%Y-%m-%d')
and fSubmited=1
and fCanceled=0
"""
sql_orderID = sql_orderID.format(
CustomerID=self.ui.fCustomerID.Value(),
dateString=JPDateConver(self._currentDate, str))
tab = JPQueryFieldInfo(sql_orderID)
fld = self.ui.fOrderID.FieldInfo
fld.RowSource = [[r.Datas[0]] for r in tab.DataRows]
fld.BindingColumn = 0
fld.Value = None
fld.NotNull = True
self.ui.fOrderID.setFieldInfo(fld, False)
self.ui.fOrderID.setEnabled(True)
class JPMySQLFieldInfo(JPFieldInfo):
NOT_NULL_FLAG = 1
PRI_KEY_FLAG = 2
UNIQUE_KEY_FLAG = 4 #唯一约束
MULTIPLE_KEY_FLAG = 8 #复合主键
BLOB_FLAG = 16
UNSIGNED_FLAG = 32 # 无符号
ZEROFILL_FLAG = 64 # 前导零
BINARY_FLAG = 128
ENUM_FLAG = 256
AUTO_INCREMENT_FLAG = 512 #自动编号
TIMESTAMP_FLAG = 1024
NO_DEFAULT_VALUE_FLAG = 4096
PART_KEY_FLAG = 16384
tp = {
FIELD_TYPE.TINY: JPFieldInfo.Int,
FIELD_TYPE.SHORT: JPFieldInfo.Int,
FIELD_TYPE.LONG: JPFieldInfo.Int,
FIELD_TYPE.LONGLONG: JPFieldInfo.Int,
FIELD_TYPE.INT24: JPFieldInfo.Int,
FIELD_TYPE.YEAR: JPFieldInfo.Int,
FIELD_TYPE.DECIMAL: JPFieldInfo.Float,
FIELD_TYPE.FLOAT: JPFieldInfo.Float,
FIELD_TYPE.DOUBLE: JPFieldInfo.Float,
FIELD_TYPE.NEWDECIMAL: JPFieldInfo.Float,
FIELD_TYPE.VARCHAR: JPFieldInfo.String,
FIELD_TYPE.JSON: JPFieldInfo.String,
FIELD_TYPE.VAR_STRING: JPFieldInfo.String,
FIELD_TYPE.STRING: JPFieldInfo.String,
FIELD_TYPE.DATE: JPFieldInfo.Date,
FIELD_TYPE.DATETIME: JPFieldInfo.Date,
FIELD_TYPE.NEWDATE: JPFieldInfo.Date,
FIELD_TYPE.TIMESTAMP: JPFieldInfo.Date,
FIELD_TYPE.BIT: JPFieldInfo.Boolean
}
SqlValueCreater = {
JPFieldInfo.Int:
lambda x: "'{}'".format(x),
JPFieldInfo.Float:
lambda x: "'{}'".format(x),
JPFieldInfo.String:
lambda x: "'{}'".format(x),
JPFieldInfo.Date:
lambda x: "'{}'".format(JPDateConver(x, str)),
JPFieldInfo.Boolean:
lambda x: "'{}'".format(ord(x) if isinstance(x, bytes) else x),
JPFieldInfo.Other:
lambda x: "'{}'".format(x)
}
@staticmethod
def getConvertersDict() -> dict:
def v_float(x):
if isinstance(x, Decimal):
return float(x.to_eng_string())
if isinstance(x, (float, int, str)):
return float(x)
def v_date(x):
if isinstance(x, QDate):
return x
if isinstance(x, datetime_date):
return QDate(x.year, x.month, x.day)
def v_bool(x):
if isinstance(x, bool):
return 1 if x else 0
if isinstance(x, bytes):
return ord(x)
def v_int(x):
if isinstance(x, str):
if len(x) > 0:
return int(x)
return x
return {
JPFieldInfo.Int: v_int,
JPFieldInfo.Float: v_float,
JPFieldInfo.String: lambda x: x,
JPFieldInfo.Date: v_date,
JPFieldInfo.Boolean: v_bool,
JPFieldInfo.Other: lambda x: x
}
def __init__(self, cursors_field):
"""此类在处理时，应该已经把从表中取得的数据全部转换成了Python内部数据类型"""
super().__init__()
f = cursors_field
fl = JPMySQLFieldInfo
self.FieldName = f.org_name if f.org_name else f.name
self.Title = f.name
self.TypeCode = self.tp.get(f.type_code, 0)
self.Scale = f.scale
self.Length = f.length
self.NotNull = f.flags & fl.NOT_NULL_FLAG != 0
self.IsPrimarykey = f.flags & fl.PRI_KEY_FLAG != 0
self.NoDefaultValue = f.flags & fl.NO_DEFAULT_VALUE_FLAG != 0
self.Auto_Increment = f.flags & fl.AUTO_INCREMENT_FLAG != 0
def sqlValue(self, value, check_null: bool = False):
v = value
if check_null:
if self.NotNull and v is None:
t = self.Title if self.Title else self.FieldName
raise ValueError(
"字段[{title}]的值不能为空!\nField[{title}] not be Empty!".format(
title=t))
tp = self.TypeCode
if v is None:
return 'Null'
return self.SqlValueCreater[tp](v)
def initItem(self):
rpt = self
rpt.PageHeader.AddItemRect(2, (0, 0, 274, 50), self.logo)
rpt.PageHeader.AddItemRect(1, (274, 0, 746, 60),
self.title,
Bolder=False,
AlignmentFlag=(Qt.AlignCenter),
Font=self.font_YaHei_10)
rpt.PageHeader.AddItemRect(1, (0, 50, 1020, 20),
'Date:{}'.format(
JPDateConver(QDate.currentDate(), str)),
Bolder=False,
AlignmentFlag=(Qt.AlignRight),
Font=self.font_YaHei_8)
cols = len(self.title_detail)
al_c = Qt.AlignCenter
al_r = (Qt.AlignVCenter | Qt.AlignRight)
al_l = (Qt.AlignVCenter | Qt.AlignLeft)
rpt.SetMargins(30, 30, 30, 30)
title_height = 20
rpt.ReportHeader.AddPrintLables(
0,
72,
40,
Texts=self.title_detail,
Widths=[40, 470, 60, 60, 60, 60, 90, 90, 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,
470,
20,
self.fns[1],
FormatString='{}',
AlignmentFlag=al_l,
# 超出长度省略
AutoShrinkFont=self.configData['AutoShrinkFonts'],
AutoEllipsis=self.configData['AutoEllipsis'],
Font=self.font_YaHei_8)
rpt.Detail.AddPrintFields(510,
0,
20,
self.fns[2:], [60, 60, 60, 60, 90, 90, 90],
[al_c] * 7,
FormatString=' {}',
Font=self.font_YaHei_8)
# 页脚
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, (100, 0, 920, 20),
'',
FormatString="PrintTime: %Y-%m-%d %H:%M:%S",
Bolder=False,
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
self.DataSource = JPDb().getDict(self.sql)
def initItem(self):
rpt = self
rpt.PageHeader.AddItemRect(2, (0, 0, 274, 50), self.logo)
rpt.PageHeader.AddItemRect(1, (274, 0, 376, 60),
self.title,
Bolder=False,
AlignmentFlag=(Qt.AlignCenter),
Font=self.font_YaHei_12)
rpt.PageHeader.AddItemRect(1, (0, 50, 650, 20),
'Date:{}'.format(
JPDateConver(self.beginDate, str) +
"--" + JPDateConver(self.endDate, str)),
Bolder=False,
AlignmentFlag=(Qt.AlignRight),
Font=self.font_YaHei_8)
cols = len(self.title_detail)
al_c = Qt.AlignCenter
al_r = (Qt.AlignVCenter | Qt.AlignRight)
al_l = (Qt.AlignVCenter | Qt.AlignLeft)
rpt.SetMargins(30, 30, 30, 30)
title_height = 20
rpt.ReportHeader.AddPrintLables(0,
72,
40,
Texts=self.title_detail,
Widths=[30, 70, 120, 310, 60, 60],
Aligns=[al_c] * cols)
rpt.Detail.addPrintRowCountItem(0,
0,
30,
20,
AlignmentFlag=al_c,
Font=self.font_YaHei_8)
rpt.Detail.AddPrintFields(30,
0,
20,
self.fns[1:4], [70, 120, 310], [al_c] * 3,
FormatString=' {}',
Font=self.font_YaHei_8)
rpt.Detail.AddPrintFields(530,
0,
20,
self.fns[4:], [60]*2, [al_r] * 2,
FormatString='{} ',
Font=self.font_YaHei_8)
# 页脚
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, 650, 20),
'',
FormatString="PrintTime: %Y-%m-%d %H:%M:%S",
Bolder=False,
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
self.DataSource = JPDb().getDict(self.sql)
