VB开发的ERP系统

Attribute VB_Name = "IniModule"
'数据初始化
Option Explicit
Dim Sql As String
Dim SqlField As String
Dim Rsc As New ADODB.Recordset
Public Sub initializtion()
    '删除工资数据表
    Sql = ""
    Sql = "delete pm_payroll"                       '工资表
    Sql = Sql & " delete pm_AttendRecord"            '考勤表
    Sql = Sql & " delete pm_OpeDept"                 '操作员部门权限
    Sql = Sql & " delete pm_OpeSort"                 '操作员类别权限
    Sql = Sql & " delete pm_TaxRate"                 '税率表
    Sql = Sql & " delete pm_TaxData"                 '税率数据表
    Sql = Sql & " delete pm_BankItem"                '银行代发项目
    Sql = Sql & " delete pm_BankPara"                '银行代发路径
    Sql = Sql & " delete pm_Bank"                    '银行信息
    Sql = Sql & " delete pm_StandTblData"            '标准表数据
    Sql = Sql & " delete pm_StandTbl"                '标准表
    Sql = Sql & " delete pm_SortEmp"                 '类别人员
    Sql = Sql & " delete pm_SortItem"                '类别项目
    Sql = Sql & " delete pm_Sort"                    '工资类别
    Sql = Sql & " delete pm_ReportItem"              '报表项目
    Sql = Sql & " delete pm_Formula"                 '公式
    '删除工资表、考勤表中的自定义字段,首先删除缺省值。用DropColumn函数
    
    '将选用的人事字段的addminusitem置0
    Sql = Sql & " update rs_items set addminusitem=0 where (sid=1 or sid=2 ) and ltrim(rtrim(fieldname))<>'deptcode' and ltrim(rtrim(fieldname))<>'empsort'" & _
          " and ltrim(rtrim(fieldname))<>'empno' and ltrim(rtrim(fieldname))<>'empname'"
    '将rs_items的工资项目、考勤项目删除。
    Sql = Sql & " delete rs_items where (sid=3 or sid =4) and ynroot=0 "
    '将会计日历表复原
    Sql = Sql & " update gy_kjrlb set pmjzbz=0 "
    SqlField = DropColumn
    On Error GoTo Err1
    Cw_DataEnvi.DataConnect.BeginTrans
    If Trim(SqlField) <> "" Then
      Cw_DataEnvi.DataConnect.Execute SqlField
    End If
    Cw_DataEnvi.DataConnect.Execute Sql
    Call Xtxxts("数据初始化成功！", 0, 4)
    Cw_DataEnvi.DataConnect.CommitTrans
    Exit Sub
Err1:
   Cw_DataEnvi.DataConnect.RollbackTrans
   Call Xtxxts("数据初始化不成功！", 0, 1)
End Sub
Private Function DropColumn() As String
  '删除工资表
   SqlField = ""
   If Rsc.State = 1 Then Rsc.Close
   Set Rsc = Cw_DataEnvi.DataConnect.Execute("select * from rs_items where sid=3  and ynroot=0 ")
   With Rsc
     Do While Not .EOF
       SqlField = SqlField & " alter table pm_Payroll drop CONSTRAINT df_" & Trim(!FieldName)
       SqlField = SqlField & " alter table pm_payroll drop column " & Trim(!FieldName)
       .MoveNext
     Loop
   End With
   '删除考勤表
   If Rsc.State = 1 Then Rsc.Close
   Set Rsc = Cw_DataEnvi.DataConnect.Execute("select * from rs_items where sid=4 ")
   With Rsc
     Do While Not .EOF
       SqlField = SqlField & " alter table pm_attendRecord drop CONSTRAINT df_" & Trim(!FieldName)
       SqlField = SqlField & " alter table pm_attendRecord drop column " & Trim(!FieldName)
       .MoveNext
     Loop
   End With
   '删除工资表中的人事项目
   If Rsc.State = 1 Then Rsc.Close
   Set Rsc = Cw_DataEnvi.DataConnect.Execute("select FieldName from rs_Items where (Sid=1 or Sid=2) and addminusItem=1 and " & _
           " FieldName<>'DeptCode' and FieldName<>'EmpNO' and FieldName<>'EmpName' and FieldName<>'Empsort' ")
   With Rsc
        Do While Not .EOF
            SqlField = SqlField & " alter table PM_Payroll drop column " & Trim(Rsc!FieldName)
            .MoveNext
        Loop
   End With
   
   DropColumn = SqlField
End Function