常用数据库表与字段
用户信息表
select top 10 * from t_sec_user
重置密码
---- 密码改为6个8
update t_sec_user
set fpassword='9EF0664B011C3DC6305C8B213378BACC2451FAEA', fmemo='8d2fea58-cda1-4035-9024-56cc6df8957c'
where fuserid=119087
-- 525b1cda73e400e5e6b64d09fb3d417438ffd9c286d4124901b521feca7ffb6d 000352540018e2798ad311edf608d84de510
二次检权数据
select *
from T_BAS_USERPARAMETER
where FPARAMETEROBJID='SEC_CHECKIDENTITY';
用户登入日志
select top 10 * from T_BAS_USERLOGINRECORD
用户设置参数表
select top 10 * from T_BAS_UserParameter
主配置
来之数据库 K3DBConfiger
账号系统表
select * from T_BAS_DATACENTER
where FDATACENTERID = '646312c743e2f6'
账套号名称
select * from T_BAS_DATACENTER_L
where FDATACENTERID='646312c743e2f6'
关联关系 通过 ”FDATACENTERID“字段
select v.FDATACENTERID,v.FDATAbasename ,n.fname ,v.FCREATEDATE
from T_BAS_DATACENTER v left join T_BAS_DATACENTER_L n
on v.FDATACENTERID = n.FDATACENTERID
账套备份记录
select * from T_BAS_DATACENTERBACKUPLOG
用户相关
用户信息表
select top 10 * from t_sec_user
员工表
T_BD_STAFF_L
物料代码
select * from t_bd_material a
left join T_BD_MATERIAL_L b on a.FMASTERID = b.FMATERIALID
where a.FNUMBER ='5.06.52.0006'
用户密码表
select top 10 * from T_SEC_USERPASSPORT
用户登入日志
select top 10 * from T_BAS_USERLOGINRECORD
用户最后登入时间
select * from
(select FUSERID,max(FDATETIME) m
from T_BAS_USERLOGINRECORD group by FUSERID ) vt
inner join T_SEC_USER ta on vt.FUSERID=ta.FUSERID
用户设置参数表
select top 10 * from T_BAS_UserParameter
财务相关
查凭证
SELECT *
FROM
(SELECT t0.FDATE fdate,
t0.FYEAR fyear,
t0.FPERIOD fperiod,
t0.FVOUCHERGROUPID fvouchergroupid_id,
t0.FVOUCHERGROUPNO fvouchergroupno,
t1.FEXPLANATION fexplanation,
t1.FACCOUNTID faccountid_id,
t1.FCURRENCYID fcurrencyid_id,
t1.FAMOUNTFOR famountfor,
t1.FDEBIT fdebit,
t1.FCREDIT fcredit,
t0.FCREATORID fcreatorid_id,
t0.FCHECKERID fcheckerid_id,
t0.FPOSTERID fposterid_id,
t0.FCASHIERID fcashierid_id,
t0.FATTACHMENTS fattachments,
t0.FBILLNO fbillno,
t0.FSYSTEMID fsystemid_id,
t0.FSOURCEBILLKEY fsourcebillkey_id,
t0.FDOCUMENTSTATUS fdocumentstatus,
t0.FINVALID finvalid,
t1.FBUSNO fbusno,
t0.FBASECURRENCYID fbasecurrencyid_id,
t0.FACCTORGID faccbookorgid,
t0.FACCTORGID faccbookorgid_id,
t1.FEXCHANGERATETYPE fexchangeratetype,
t0.FVOUCHERID fvoucherid,
t1.FENTRYID t1_fentryid,
t1.FEntrySeq t1_fentryseq,
ROW_NUMBER() OVER(
ORDER BY st01_L.FNAME ASC, t0.FYEAR ASC, t0.FPERIOD ASC, st02_L.FNAME ASC, t0.FVOUCHERGROUPNO ASC, t0.FVOUCHERID ASC, FENTRYSEQ ASC) fidentityid
FROM T_GL_VOUCHER t0 LEFT OUTER
JOIN T_GL_VOUCHERENTRY t1
ON t0.FVOUCHERID = t1.FVOUCHERID LEFT OUTER
JOIN T_BD_ACCOUNTBOOK_L st01_L
ON (t0.FACCOUNTBOOKID = st01_L.FBOOKID
AND st01_L.FLocaleId = 2052) LEFT OUTER
JOIN T_BD_VOUCHERGROUP_L st02_L
ON (t0.FVOUCHERGROUPID = st02_L.FVCHGROUPID
AND st02_L.FLocaleId = 2052)
INNER JOIN
(SELECT DISTINCT FVOUCHERID
FROM
(SELECT t0.FVOUCHERID fvoucherid
FROM T_GL_VOUCHER t0 LEFT OUTER
JOIN T_GL_VOUCHERENTRY t1
ON t0.FVOUCHERID = t1.FVOUCHERID LEFT OUTER
JOIN T_BD_ACCOUNTBOOK_L st01_L
ON (t0.FACCOUNTBOOKID = st01_L.FBOOKID
AND st01_L.FLocaleId = 2052)
WHERE (((((st01_L.FNAME = N'宁波市欧琦烟具有限公司'
AND t0.FYEAR = 2025)
AND t0.FPERIOD = 6)
AND EXISTS
(SELECT 1
FROM T_BD_ACCOUNTBOOK AB
WHERE (AB.FFORBIDSTATUS = 'A'
AND AB.FBOOKID = t0.FACCOUNTBOOKID)))
AND t0.FISADJUSTVOUCHER = '0')
AND (t1.FEXPLANATION LIKE N'%公司%'))) tm) vch
ON vch.FVOUCHERID = t0.FVOUCHERID
WHERE EXISTS
(SELECT FVOUCHERID
FROM
(SELECT t0.FVOUCHERID
FROM T_GL_VOUCHER t0
WHERE t0.FACCTORGID = 1) txs
WHERE txs.FVOUCHERID = t0.FVOUCHERID)) tlist
WHERE ((fidentityid >= 1)
AND (fidentityid <= 200))
自查凭证sql
SELECT
FDATE AS 日期,
FYEAR AS 会计年度,
FPERIOD AS 期间,
FVOUCHERGROUPNO AS 凭证号,
凭证记号.fname as 凭证字,
凭证.FBILLNO AS 凭证编号,
凭证明细.fentryseq AS 行号,
凭证明细.FEXPLANATION as 摘要,
业务类型名字.fname AS 来源系统,
凭证.FSourceBillKey AS 业务类型,
单据类型名称.名称 AS 业务类型名称,
科目.FNUMBER AS 科目编号,
科目语言.FNAME AS 科目名称,
凭证明细.FDetailID as 维度id,
币别名字.FNAME AS 币别,
凭证明细.fdebit AS 借方金额,
凭证明细.fcredit AS 贷方金额,
凭证明细.FDC AS 借贷方向,
用户.fname AS 创建人 --,凭证明细.*
FROM
T_GL_VOUCHER 凭证
LEFT JOIN T_GL_VOUCHERENTRY 凭证明细 ON 凭证.FVOUCHERID = 凭证明细.FVOUCHERID
LEFT JOIN t_bd_account 科目 ON 科目.FACCTID = 凭证明细.FACCOUNTID
LEFT JOIN t_bd_account_l 科目语言 ON 科目.FACCTID = 科目语言.FACCTID
LEFT JOIN T_META_SUBSYSTEM 业务类型 ON 业务类型.fid = 凭证.FSYSTEMID
LEFT JOIN T_META_SUBSYSTEM_L 业务类型名字 ON 业务类型.fid = 业务类型名字.fid
LEFT JOIN (
select
FENUMID 编号,
FCAPTION 名称
from
T_META_FORMENUMITEM_L
where
FLOCALEID = 2052
union
select
FBILLFORMID,
FNAME
from
T_META_NEEDTYPEBILL_L
where
FLOCALEID = 2052
) 单据类型名称 ON 单据类型名称.编号 = 凭证.FSourceBillKey
LEFT JOIN t_sec_user 用户 ON 用户.FUSERID = 凭证.FCREATORID
LEFT JOIN T_BD_CURRENCY_L 币别名字 ON 币别名字.fcurrencyid = 凭证明细.FCURRENCYID
LEFT JOIN T_BD_VOUCHERGROUP_L 凭证记号 ON 凭证记号.fvchgroupid = 凭证.FVOUCHERGROUPID
WHERE
1 = 1
AND fdate BETWEEN '2025-05-01'
AND '2025-08-01'
ORDER BY
期间 DESC,
凭证字,
凭证号,
行号
单据类型来源与两个地方（FSourceBillKey）
select count(*) from T_META_FORMENUMITEM_L
select count(*) from T_META_NEEDTYPEBILL_L
核算维度
select FFLEX9 from T_BD_FLEXITEMDETAILV where fid = 100445 --维度指向费用
-- 维度展开 (基础资料/辅助资料)
select 维度描述.FVALUETYPE , 业务表.ftablename as 表名 ,业务表.fpkfieldname as 主键 ,FFLEXNUMBER ,维度描述名.fname 维度名,FVALUESOURCE
from T_BD_FLEXITEMPROPERTY 维度描述
left join T_BD_FLEXITEMPROPERTY_L 维度描述名 on (维度描述名.fid=维度描述.fid and FLOCALEID = 2052)
left join T_META_LOOKUPCLASS 业务表 on ( 业务表 .FFORMID = 维度描述. FVALUESOURCE )
where 维度描述.FVALUETYPE = 0
-- 辅助资料
select 辅料明细 .FNUMBER, 辅料.FNUMBER , 辅料说明.fname , 辅料明细名 .FDATAVALUE as 辅助资料名称, *
from T_BAS_ASSISTANTDATA 辅料
left join T_BAS_ASSISTANTDATA_L 辅料说明 on ( 辅料 .fid =辅料说明.fid and 辅料说明.FLOCALEID = 2052 )
left join T_BAS_ASSISTANTDATAENTRY 辅料明细 on ( 辅料 .fid =辅料明细.fid )
left join T_BAS_ASSISTANTDATAENTRY_L 辅料明细名 on ( 辅料明细名.fentryid = 辅料明细.fentryid and 辅料明细名.FLOCALEID = 2052)
维度展开
t_BD_Supplier FSupplierId FFLEX4 供应商
T_BD_DEPARTMENT FDEPTID FFLEX5 部门
T_BD_CUSTOMER FCUSTID FFLEX6 客户
T_HR_EMPINFO FID FFLEX7 员工
T_BD_MATERIAL FMATERIALID FFLEX8 物料
T_BD_EXPENSE FEXPID FFLEX9 费用项目
T_FA_ASSETTYPE FID FFLEX10 资产类别
T_ORG_Organizations FOrgID FFLEX11 组织机构
T_BD_MATERIALGROUP FID FFLEX12 物料分组
T_BD_CUSTOMERGROUP FID FFLEX13 客户分组
T_BD_BANK FBANKID FFLEX14 银行
T_CN_BANKACNT FBANKACNTID FFLEX15 银行账号
T_FIN_OTHERS FID FFLEX16 其他往来单位
V_BD_OPERATORSTAFF FID FF100002 业务员
V_BD_SALESMAN FID FF100006 销售员
创建视图
DROP VIEW IF EXISTS 凭证维度展开视图
go
CREATE VIEW 凭证维度展开视图
AS SELECT
FYEAR AS 会计年度,
FPERIOD AS 期间,
FVOUCHERGROUPNO AS 凭证号,
凭证记号.fname as 凭证字,
凭证.FBILLNO AS 凭证编号,
凭证明细.fentryseq AS 行号,
凭证明细.FEXPLANATION as 摘要,
业务类型名字.fname AS 来源系统,
凭证.FSourceBillKey AS 业务类型,
单据类型名称.名称 AS 业务类型名称,
科目.FNUMBER AS 科目编号,
科目语言.FNAME AS 科目名称,
币别名字.FNAME AS 币别,
凭证明细.fdebit AS 借方金额,
凭证明细.fcredit AS 贷方金额,
凭证明细.FDC AS 借贷方向,
用户.fname AS 创建人 ,
凭证明细.FDetailID as 维度id
--------------
,维度.FFLEX4 AS 维度id供应商
,维度.FFLEX5 AS 维度id部门
,维度.FFLEX6 AS 维度id客户
,维度.FFLEX7 AS 维度id员工
,维度.FFLEX8 AS 维度id物料
,维度.FFLEX9 AS 维度id费用项目
,维度.FFLEX10 AS 维度id资产类别
,维度.FFLEX11 AS 维度id组织机构
,维度.FFLEX12 AS 维度id物料分组
,维度.FFLEX13 AS 维度id客户分组
,维度.FFLEX14 AS 维度id银行
,维度.FFLEX15 AS 维度id银行账号
,维度.FFLEX16 AS 维度id其他往来单位
,维度.FF100002 AS 维度id业务员
,维度.FF100006 AS 维度id销售员
FROM
T_GL_VOUCHER 凭证
LEFT JOIN T_GL_VOUCHERENTRY 凭证明细 ON 凭证.FVOUCHERID = 凭证明细.FVOUCHERID
LEFT JOIN t_bd_account 科目 ON 科目.FACCTID = 凭证明细.FACCOUNTID
LEFT JOIN t_bd_account_l 科目语言 ON 科目.FACCTID = 科目语言.FACCTID
LEFT JOIN T_META_SUBSYSTEM 业务类型 ON 业务类型.fid = 凭证.FSYSTEMID
LEFT JOIN T_META_SUBSYSTEM_L 业务类型名字 ON 业务类型.fid = 业务类型名字.fid
LEFT JOIN (
select
FENUMID 编号,
FCAPTION 名称
from
T_META_FORMENUMITEM_L
where
FLOCALEID = 2052
union
select
FBILLFORMID,
FNAME
from
T_META_NEEDTYPEBILL_L
where
FLOCALEID = 2052
) 单据类型名称 ON 单据类型名称.编号 = 凭证.FSourceBillKey
LEFT JOIN t_sec_user 用户 ON 用户.FUSERID = 凭证.FCREATORID
LEFT JOIN T_BD_CURRENCY_L 币别名字 ON 币别名字.fcurrencyid = 凭证明细.FCURRENCYID
LEFT JOIN T_BD_VOUCHERGROUP_L 凭证记号 ON 凭证记号.fvchgroupid = 凭证.FVOUCHERGROUPID
left join T_BD_FLEXITEMDETAILV 维度 on ( 维度 . fid = 凭证明细.FDetailID)
WHERE
1 = 1
AND fdate BETWEEN '2025-05-01'
AND '2025-08-01'
通过视图来简化查询
select 凭证.*
,供应商cn.fname as 供应商名称
,部门cn.fname as 部门名称
, 客户cn.fname as 客户名称
, 员工cn .fname as 员工名称
,物料.FNUMBER 物料编码
,费用项目cn.fname as 费用名称
,资产类别cn.fname as 资产类别名
, 组织机构cn .fname 机构名称
, 物料分组.fnumber as 分组编号
,客户分组cn.FNAME 客户分组名
,银行cn .FNAME AS 银行名称
, 银行账号cn.fname as 银行账号名
, 其他往来单位cn.fname 其他来往单位
,业务员cn .fname as 业务员
, 销售员cn.fname as 销售员
from 凭证维度展开视图 凭证
LEFT JOIN t_BD_Supplier 供应商 on (供应商. FSupplierId = 凭证.维度id供应商)
left join T_BD_SUPPLIER_L 供应商cn on ( 供应商cn .FSupplierId = 供应商.FSupplierId and 供应商cn.FLOCALEID = 2052)
LEFT JOIN T_BD_DEPARTMENT 部门 on ( 部门. FDEPTID = 凭证.维度id部门)
left join T_BD_DEPARTMENT_L 部门cn on ( 部门cn .FDEPTID = 部门.FDEPTID and 部门cn.FLOCALEID = 2052)
LEFT JOIN T_BD_CUSTOMER 客户 on (客户. FCUSTID = 凭证.维度id客户)
left join T_BD_CUSTOMER_L 客户cn on (客户. FCUSTID = 客户cn.FCUSTID and 客户cn.FLOCALEID = 2052)
LEFT JOIN T_HR_EMPINFO 员工 on (员工. FID = 凭证.维度id员工)
left join T_HR_EMPINFO_L 员工cn on (员工. fid = 员工cn.fid and 员工cn.FLOCALEID = 2052)
LEFT JOIN T_BD_MATERIAL 物料 on (物料 .FMATERIALID = 凭证.维度id物料)
LEFT JOIN T_BD_EXPENSE 费用项目 on (费用项目.FEXPID = 凭证.维度id费用项目)
left join T_BD_EXPENSE_L 费用项目cn on (费用项目 .FEXPID =费用项目cn.FEXPID and 费用项目cn.FLOCALEID = 2052)
LEFT JOIN T_FA_ASSETTYPE 资产类别 on ( 资产类别.FID = 凭证.维度id资产类别)
left join T_FA_ASSETTYPE_L 资产类别cn on ( 资产类别.FID =资产类别cn.FID and 资产类别cn.FLOCALEID = 2052)
LEFT JOIN T_ORG_Organizations 组织机构 on ( 组织机构.FOrgID = 凭证.维度id组织机构)
left join T_ORG_ORGANIZATIONS_L 组织机构cn on ( 组织机构.FOrgID =组织机构cn.FOrgID and 组织机构cn.FLOCALEID = 2052)
LEFT JOIN T_BD_MATERIALGROUP 物料分组 on ( 物料分组.FID = 凭证.维度id物料分组)
LEFT JOIN T_BD_CUSTOMERGROUP 客户分组 on (客户分组. FID = 凭证.维度id客户分组)
left join T_BD_CUSTOMERGROUP_L 客户分组cn on (客户分组. FID =客户分组cn.FID and 客户分组cn.FLOCALEID = 2052)
LEFT JOIN T_BD_BANK 银行 on ( 银行.FBANKID = 凭证.维度id银行)
left join T_BD_BANK_L 银行cn on ( 银行.FBANKID =银行cn.FBANKID and 银行cn.FLOCALEID = 2052)
LEFT JOIN T_CN_BANKACNT 银行账号 on (银行账号. FBANKACNTID = 凭证.维度id银行账号)
left join T_CN_BANKACNT_L 银行账号cn on (银行账号. FBANKACNTID =银行账号cn.FBANKACNTID and 银行账号cn.FLOCALEID = 2052)
LEFT JOIN T_FIN_OTHERS 其他往来单位 on (其他往来单位. FID = 凭证.维度id其他往来单位)
left join T_FIN_OTHERS_L 其他往来单位cn on (其他往来单位. FID =其他往来单位cn.FID and 其他往来单位cn.FLOCALEID = 2052)
LEFT JOIN V_BD_OPERATORSTAFF 业务员 on (业务员. FID = 凭证.维度id业务员)
left join V_BD_OPERATORSTAFF_L 业务员cn on (业务员. FID =业务员cn.FID and 业务员cn.FLOCALEID = 2052)
LEFT JOIN V_BD_SALESMAN 销售员 on (销售员 .FID = 凭证.维度id销售员)
left join V_BD_SALESMAN_L 销售员cn on (销售员 .FID =销售员cn.FID and 销售员cn.FLOCALEID = 2052)
包含摘要和流水表
SELECT * FROM "AIS20230131214054"."dbo"."T_AP_PAYBILLENTRY" WHERE LOWER("FWRITTENOFFSTATUS") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FCOMMENT") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FPAYITEMTYPE") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FPAYITEM") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FASSBILLNO") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FOPPOSITEBANKNAME") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FOPPOSITEBANKACCOUNT") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FOPPOSITECCOUNTNAME") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FBLEND") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FSETTLENO") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FISPOST") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FPURCHASEORDERNO") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FSWIFTCODE") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FSIGNATURE") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' ;
SELECT * FROM "AIS20230131214054"."dbo"."T_BAS_VOUCHERENTRY" WHERE LOWER("FEXPLANATION") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FSETTLENO") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FAUTOMATIC") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FBUSNO") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FVCHTEMPLATEINFO") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' ;
SELECT * FROM "AIS20230131214054"."dbo"."T_GL_VOUCHERENTRY" WHERE LOWER("FEXPLANATION") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FSETTLENO") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FCASHFLOWITEM") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FISMULTICOLLECT") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FBUSNO") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FCASHFLOWDATA") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' ;
SELECT * FROM "AIS20230131214054"."dbo"."V_CN_TRANS" WHERE LOWER("FBILLTYPEIDKEY") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FBILLNOKEY") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FSETTLENOKEY") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FEXPLANATIONKEY") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FOPPOSITEBANKNAMEKEY") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FOPPOSITEBANKACNTNOKEY") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FOPPOSITECCOUNTNAMEKEY") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FBLENDKEY") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FTYPE") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FDOCUMENTSTATUSKEY") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FCONTACTUNITTYPEKEY") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' ;
SELECT * FROM "AIS20230131214054"."dbo"."V_CN_VOUCHERENTRY" WHERE LOWER("FEXPLANATION") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FSETTLENO") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FCASHFLOWITEM") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' OR LOWER("FISMULTICOLLECT") LIKE '慈溪市宇诺五金配件有限公司2月货款-欧琦工行' ;