📄 mdlsubroutine.bas
字号:
adoCmd.CommandText = "INSERT INTO tUSU_AccountFormat" & _
" VALUES('" & sAccountType & "','" & sAccountFormat & _
"','" & sColWidth & "')"
adoCmd.Execute
End If
i = 1
ReDim iColWidth(1 To i)
For j = 1 To Len(sColWidth)
If j = 1 Then
iColWidth(i) = Mid(sColWidth, j, 1)
ElseIf Mid(sColWidth, j, 1) <> "," Then
iColWidth(i) = iColWidth(i) & Mid(sColWidth, j, 1)
Else
i = i + 1
ReDim Preserve iColWidth(1 To i)
End If
Next j
GetColWidth = iColWidth
End Function
Public Function GetColWidth1(ByVal sAccountType, ByVal sAccountFormat As String, _
ByVal sDefaultColWidth As String) As Integer()
Dim rstTemp As ADODB.Recordset
Dim adoCmd As ADODB.Command
Dim sSQL As String
Dim iColWidth() As Integer
Dim sColWidth As String
Dim i As Integer
Dim j As Integer
sColWidth = ""
sColWidth = sDefaultColWidth
i = 1
ReDim iColWidth(1 To i)
For j = 1 To Len(sColWidth)
If j = 1 Then
iColWidth(i) = Mid(sColWidth, j, 1)
ElseIf Mid(sColWidth, j, 1) <> "," Then
iColWidth(i) = iColWidth(i) & Mid(sColWidth, j, 1)
Else
i = i + 1
ReDim Preserve iColWidth(1 To i)
End If
Next j
GetColWidth1 = iColWidth
End Function
'得到当前账套的单位名称
Public Function GetDWMC() As String
Dim rstTemp As ADODB.Recordset
Dim sSQL As String
Set rstTemp = New ADODB.Recordset
rstTemp.CursorLocation = adUseClient
'取出当前账套的单位名称
sSQL = "SELECT EnterName FROM tSYS_Account" & _
" WHERE AccountID = '" & glo.sAccountID & "'"
With rstTemp
.Open sSQL, gloSys.cnnSYS, adOpenStatic, adLockReadOnly
If .RecordCount > 0 Then
GetDWMC = Trim(.Fields("EnterName").value)
.Close
Else
MsgBox "单位名不存在!", vbInformation
.Close
Exit Function
End If
End With
End Function
'得到一个科目数量单位和外币单位
Public Function GetSldwAndWbdw(ByVal sSubjCode As String, ByRef sSldw As String, ByRef sWbdw As String)
Dim rstTemp As ADODB.Recordset
Dim sSQL As String
Set rstTemp = New ADODB.Recordset
rstTemp.CursorLocation = adUseClient
'取出会计科目的数量单位和外币币名
sSQL = "SELECT sldw,wbdw FROM tZW_Km" & glo.sOperateYear & _
" WHERE kmdm = '" & sSubjCode & "'"
With rstTemp
.Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
If .RecordCount > 0 Then
sSldw = "" & Trim(.Fields("sldw").value)
sWbdw = "" & Trim(.Fields("wbdw").value)
.Close
End If
End With
End Function
Public Function GetSldwAndWbdw1(ByVal sSubjCode As String, ByRef sSldw As String, ByRef sWbdw As String, ByRef sformat As String)
Dim rstTemp As ADODB.Recordset
Dim sSQL As String
Set rstTemp = New ADODB.Recordset
rstTemp.CursorLocation = adUseClient
'取出会计科目的数量单位和外币币名
sSQL = "SELECT sldw,wbdw,zygs FROM tZW_Km" & glo.sOperateYear & _
" WHERE kmdm = '" & sSubjCode & "'"
With rstTemp
.Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
If .RecordCount > 0 Then
sSldw = "" & Trim(.Fields("sldw").value)
sWbdw = "" & Trim(.Fields("wbdw").value)
sformat = "" & Trim(.Fields("zygs").value)
.Close
End If
End With
End Function
''数据导入时创建表
'Public Sub CreateTable_SJZH(ByVal sYear As String)
' Dim sSQL() As String
' Dim adoCmd As ADODB.Command
' Dim i As Long
'
' On Error Resume Next
'
' Set adoCmd = New ADODB.Command
' adoCmd.ActiveConnection = glo.cnnMain
' adoCmd.CommandType = adCmdText
'
' ReDim sSQL(1 To 8)
'
''1
'
''会计科目表
' sSQL(1) = "CREATE TABLE tZW_km" & sYear & "(" & _
'" kmdm varchar(18) NOT NULL PRIMARY KEY," & _
'" kmmc varchar(40) NULL, kmmcEng varchar(80) NULL, zjm varchar(4) NULL, kmlx varchar(20) NOT NULL," & _
'" kmjc smallint NOT NULL, IsEndkm smallint default -1," & _
'" yefx char(4) NOT NULL, zygs varchar(10) NULL," & _
'" hzdykm varchar(18) Null, sldw varchar(10) NULL, wbdw varchar(10) NULL," & _
'" IsRjz smallint default 0, IsYhz smallint default 0, IsGrwlhs smallint default 0, IsKhwlhs smallint default 0," & _
'" IsGyswlhs smallint default 0, IsBmhs smallint default 0, IsXmhs smallint default 0, IsXjllkm smallint default 0," & _
'" Xjlllb varchar(10) NULL, JfKze decimal(15,2) NULL, DfKze decimal(15,2) NULL," & _
'" YeKze decimal(15,2) NULL, kmqx smallint default 0," & _
'" CwfxLx varchar(20) NULL, IsFc smallint default 0," & _
'" bUse smallint default 0, bAdd smallint default -1, cLawless varchar(255) NULL," & _
'" ljjsl00 decimal(15, 3) default 0,ljjwb00 decimal(15, 2) default 0,ljj00 decimal(15, 2) default 0,ljdsl00 decimal(15, 3) default 0,ljdwb00 decimal(15, 2) default 0, ljd00 decimal(15, 2) default 0," & _
'" ljjsl01 decimal(15, 3) default 0,ljjwb01 decimal(15, 2) default 0,ljj01 decimal(15, 2) default 0,ljdsl01 decimal(15, 3) default 0,ljdwb01 decimal(15, 2) default 0, ljd01 decimal(15, 2) default 0," & _
'" ljjsl02 decimal(15, 3) default 0,ljjwb02 decimal(15, 2) default 0,ljj02 decimal(15, 2) default 0,ljdsl02 decimal(15, 3) default 0,ljdwb02 decimal(15, 2) default 0, ljd02 decimal(15, 2) default 0," & _
'" ljjsl03 decimal(15, 3) default 0,ljjwb03 decimal(15, 2) default 0,ljj03 decimal(15, 2) default 0,ljdsl03 decimal(15, 3) default 0,ljdwb03 decimal(15, 2) default 0, ljd03 decimal(15, 2) default 0," & _
'" ljjsl04 decimal(15, 3) default 0,ljjwb04 decimal(15, 2) default 0,ljj04 decimal(15, 2) default 0,ljdsl04 decimal(15, 3) default 0,ljdwb04 decimal(15, 2) default 0, ljd04 decimal(15, 2) default 0," & _
'" ljjsl05 decimal(15, 3) default 0,ljjwb05 decimal(15, 2) default 0,ljj05 decimal(15, 2) default 0,ljdsl05 decimal(15, 3) default 0,ljdwb05 decimal(15, 2) default 0, ljd05 decimal(15, 2) default 0," & _
'" ljjsl06 decimal(15, 3) default 0,ljjwb06 decimal(15, 2) default 0,ljj06 decimal(15, 2) default 0,ljdsl06 decimal(15, 3) default 0,ljdwb06 decimal(15, 2) default 0, ljd06 decimal(15, 2) default 0," & _
'" ljjsl07 decimal(15, 3) default 0,ljjwb07 decimal(15, 2) default 0,ljj07 decimal(15, 2) default 0,ljdsl07 decimal(15, 3) default 0,ljdwb07 decimal(15, 2) default 0, ljd07 decimal(15, 2) default 0," & _
'" ljjsl08 decimal(15, 3) default 0,ljjwb08 decimal(15, 2) default 0,ljj08 decimal(15, 2) default 0,ljdsl08 decimal(15, 3) default 0,ljdwb08 decimal(15, 2) default 0, ljd08 decimal(15, 2) default 0," & _
'" ljjsl09 decimal(15, 3) default 0,ljjwb09 decimal(15, 2) default 0,ljj09 decimal(15, 2) default 0,ljdsl09 decimal(15, 3) default 0,ljdwb09 decimal(15, 2) default 0, ljd09 decimal(15, 2) default 0," & _
'" ljjsl10 decimal(15, 3) default 0,ljjwb10 decimal(15, 2) default 0,ljj10 decimal(15, 2) default 0,ljdsl10 decimal(15, 3) default 0,ljdwb10 decimal(15, 2) default 0, ljd10 decimal(15, 2) default 0," & _
'" ljjsl11 decimal(15, 3) default 0,ljjwb11 decimal(15, 2) default 0,ljj11 decimal(15, 2) default 0,ljdsl11 decimal(15, 3) default 0,ljdwb11 decimal(15, 2) default 0, ljd11 decimal(15, 2) default 0," & _
'" ljjsl12 decimal(15, 3) default 0,ljjwb12 decimal(15, 2) default 0,ljj12 decimal(15, 2) default 0,ljdsl12 decimal(15, 3) default 0,ljdwb12 decimal(15, 2) default 0, ljd12 decimal(15, 2) default 0)"
'
''2
'
''"/*(ZW)12.凭证数据文件xxxx；“xxxx”表示该账套当前使用年份*/"
''/*1-19是凭证及明细账，20为银行账科目调整前余额，21为期初待核银行账, _
' 22为个人往来期初，23为客户往来期初，24为供应商往来期初，25部门及项目期初*/
''/*修改标志: 9-凭证有错；0-未复核，可修改；1-已复核；2-已记账*/
' sSQL(2) = _
'"CREATE TABLE tZW_Pzsj" & sYear & " (" & _
'" kjqj smallint NOT NULL , /*会计期间*/" & _
'" pzzl char(2) NOT NULL , /*凭证种类(简称)*/ pzbh char(4) NOT NULL , /*凭证编号*/ jlhm smallint NOT NULL , /*记录号码(区别凭证内各条分录)*/" & _
'" pzrq " & gloSys.sDateType & " NULL, /*凭证日期*/ fjzs smallint default 0, /*附件张数*/ " & _
'" zdrmCode varchar(4) NULL , /*制单人代码*/ zdrm char(8) NULL , /*制单人名*/" & _
'" fhrmCode varchar(4) NULL , /*复核人代码*/ fhrm char(8) NULL , /*复核人名*/" & _
'" zgrmCode varchar(4) NULL , /*主管人代码*/ zgrm char(8) NULL , /*主管人名*/" & _
'" xgbz char(1) NOT NULL , /*修改标志*/ bSpecial smallint default 0 , /*T-特殊凭证(多借多贷)；F-普通凭证*/" & _
'" bCashFlow smallint default 0 , /*是否现金流量凭证*/ bSplitRec smallint default 0 , /*T-已拆分；F-未拆分*/" & _
'" pzzy char(255) NULL , /*凭证摘要*/ kmdm char(18) NULL , /*科目代码*/ kmmc char(255) NULL , /*科目名称路径*/ fx char(2) NULL , /*凭证记账借贷方向*/" & _
'" je decimal(15,2) default 0 , /*金额*/ sl decimal(15,4) default 0 , /*数量*/ dj decimal(15,2) default 0 , /*单价*/ wb decimal(15,2) default 0 , /*外币*/ hl decimal(15,8) NULL , /*汇率*/" & _
'" yhdz_jsfsCode char(3) NULL, yhdz_jsfs char(12) NULL , /*结算方式*/" & _
'" yhdz_Bill char(12) NULL , /*银行票号*/ yhdz_Date " & gloSys.sDateType & " NULL,/*银行票据日期*/ yhdz_lqbz smallint NULL , /*银行对账两清标志*/ yhdz_hxbz smallint default 0 , /*银行对账核销标志*/" & _
'" yhdz_id int NULL , /*与对账单表的外键*/ Grwl_Code char(20) NULL , /*个人往来个人代码*/ Grwl_Name char(20) NULL , /*个人往来个人名称*/ Grwl_Bill char(12) NULL , /*个人往来票号*/" & _
'" Grwl_Date " & gloSys.sDateType & " NULL,/*个人往来日期*/ Grwl_Lqflg smallint NULL , /*个人往来两清标志*/ Grwl_Qcflg smallint default 0 , /*个人往来清除标志*/ Khwl_Code char(12) NULL , /*客户往来客户代码*/" & _
'" Khwl_Name char(60) NULL , /*客户往来客户名称*/ Khwl_Bill char(12) NULL , /*客户往来票号*/ Khwl_Date " & gloSys.sDateType & " NULL , /*客户往来日期*/ Khwl_CBDcode char(20) NULL , /*业务员代码*/" & _
'" Khwl_CBDname char(20) NULL , /*业务员名称*/ Khwl_Lqflg smallint NULL , /*客户往来两清标志*/ Khwl_Qcflg smallint default 0 , /*客户往来清除标志*/ Gyswl_Code char(12) NULL ," & _
'" Gyswl_Name char(60) NULL , Gyswl_Bill char(12) NULL , Gyswl_Date " & gloSys.sDateType & " NULL , Gyswl_CBDcode char(20) NULL ," & _
'" Gyswl_CBDname char(20) NULL , Gyswl_Lqflag smallint NULL , Gyswl_Qcflg smallint default 0 , bmdm char(12) NULL , /*部门代码*/ bmmc char(20) NULL , /*部门名称*/" & _
'" xmdm char(30) NULL , /*项目代码*/ xmmc char(50) NULL /*项目名称*/" & _
'")"
'
''3
' Select Case g_FLAT
' Case "SQL"
' sSQL(3) = _
' "ALTER TABLE tZW_Pzsj" & sYear & " WITH NOCHECK ADD " & _
' " CONSTRAINT PK_ZW_Pzsj" & sYear & " PRIMARY KEY NONCLUSTERED " & _
' " (" & _
' " kjqj," & _
' " pzzl," & _
' " pzbh," & _
' " jlhm" & _
' " )"
' Case "ORACLE"
' sSQL(3) = "CREATE UNIQUE INDEX PK_ZW_Pzsj" & sYear & " ON " & _
' "tZW_Pzsj" & sYear & "(kjqj,pzzl,pzbh,jlhm)"
' End Select
'
''4
' sSQL(4) = _
'"/*(ZW)2.支票登记簿表*/" & _
'"CREATE TABLE tZW_Check" & sYear & "(" & _
'" kmdm varchar(18) NOT NULL, /*科目代码*/" & _
'" checkNo varchar(10) NOT NULL, /*支票号*/" & _
'" usedate " & gloSys.sDateType & " NULL, /*领用日期*/" & _
'" DepartCode varchar(12) NULL, /*领用部门代码*/" & _
'" PersonCode varchar(20) NULL, /*领用人编号*/" & _
'" money decimal(15,2) default 0, /*限领*/" & _
'" usertext varchar(30) NULL, /*用途*/" & _
'" outdate " & gloSys.sDateType & " NULL, /*报销日期*/" & _
'" text varchar(30) NULL /*备注*/" & _
'")"
'
''5
' Select Case g_FLAT
' Case "SQL"
' sSQL(5) = _
' "Alter TABLE tZW_Check" & sYear & " WITH NOCHECK ADD " & _
' " CONSTRAINT PK_ZW_Check" & sYear & " PRIMARY KEY NONCLUSTERED " & _
' " (" & _
' " kmdm," & _
' " checkNo " & _
' " )"
' Case "ORACLE"
' sSQL(5) = "CREATE UNIQUE INDEX PK_ZW_Check" & sYear & " ON " & _
' "tZW_Check" & sYear & "(kmdm,checkNo)"
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -