module4sysinit.bas

本公司开发得大请油田人事管理系统c/s结构

Attribute VB_Name = "ModuleSysInit"
Option Explicit

Private m_strSQL As String
Private m_oRs4OrganBreak As New ADODB.Recordset
Private m_Array4OrganBreak() As Variant
Private m_lArrayCount As Long
Public g_i4ExcuteSql As Integer

'按钮分组
Public Const C_FIRST_GROUP = 1
Public Const C_SECOND_GROUP = 2
Public Const C_ALL_MEMBER = 1
Public Const C_UNALL_MEMBER = 2
Public Const C_SELECT_MEMBER = 3
Public Const C_LUMP_MEMBER = 4
Public Const C_TO_EXCEL_MEMBER = 5
Public Const C_TO_PRN_MEMBER = 6
Public Const C_CLOSE_MEMBER = 7
Public Const C_OTHER_MEMBER = 8
Public Const C_FIELD_SUBTOTAL = 9
Public Const C_UPDATE_MEMBER = 1
Public Const C_INSERT_MEMBER = 2
Public Const C_DELETE_MEMBER = 3
Public Const C_WAGE_CUSTOM_CHANGE = 4
Public Const C_USE_COLOR = vbBlue
Public Const C_UNUSE_COLOR = vbRed

'记录代码表的内容 用作ColComboList
Public g_strArray4ColComboList(60) As String
Public g_intArray4ColComboList(60) As Integer

Public Const C_T_APPOINTED_TYPE = 1
Public Const C_T_AREA_CODE = 2
Public Const C_T_AREA_SORT = 3
Public Const C_T_BRIGADE_TYPE = 4
Public Const C_T_CADRE_SORT = 5
Public Const C_T_COMPACT_LIMIT = 6
Public Const C_T_DEGREE = 7
Public Const C_T_DUTY = 8
Public Const C_T_DUTY_LEVEL = 9
Public Const C_T_DUTY_SPECIALTY = 10
Public Const C_T_EDUCATION_LEVEL = 11
Public Const C_T_EDUCATION_LEVEL_WAY = 12
Public Const C_T_EMP_SORT = 13
Public Const C_T_EMP_TYPE = 14
Public Const C_T_ENGAGE_SPECIALTY = 15
Public Const C_T_ENTERPRISE_PROPERTY = 16
Public Const C_T_EVOLVEMENT_SORT = 17
Public Const C_T_EXAMINATION_SORT = 18
Public Const C_T_FOREIGN_LANGUAGE = 19
Public Const C_T_HEALTH = 20
Public Const C_T_MASTER_DEGREE = 21
Public Const C_T_NATION = 22
Public Const C_T_OCCUPATION_SORT = 23
Public Const C_T_POLITICS = 24
Public Const C_T_PREPARE_MATURE_DEGREE = 25
Public Const C_T_PREPARE_TRAIN_LEVEL = 26
Public Const C_T_PREPARE_TRAIN_MEASURE = 27
Public Const C_T_PREPARE_TRAIN_TARGET = 28
Public Const C_T_PUNISH_REASON = 29
Public Const C_T_PUNISH_SORT = 30
Public Const C_T_PUNISH_TYPE = 31
Public Const C_T_REMOVE_SORT = 32
Public Const C_T_REWARD_LEVEL = 33
Public Const C_T_REWARD_SORT = 34
Public Const C_T_SCIENTIFIC_SEARCH_SORT = 35
Public Const C_T_SEARCH_GRADE = 36
Public Const C_T_SEARCH_LEVEL = 37
Public Const C_T_SENIORITY_NAME = 38
Public Const C_T_SENIORITY_SPECIALTY = 39
Public Const C_T_SOCIATY_RELATION_CODE = 40
Public Const C_T_SPECIALTY_SORT = 41
Public Const C_T_STATION_SORT = 42
Public Const C_T_TECHNICAL_APTITUDE = 43
Public Const C_T_TECHNICAL_DUTY = 44
Public Const C_T_TECHNICAL_GRADE = 45
Public Const C_T_TECHNICAL_LEAD_LEVEL = 46
Public Const C_T_TECHNICAL_LEAD_TARGET = 47
Public Const C_T_TECHNICAL_NAME_LEVEL = 48
Public Const C_T_TRAIN_CONTENT = 49
Public Const C_T_TRAIN_LEVEL = 50
Public Const C_T_TRAIN_SORT = 51
Public Const C_T_WORK_PROPERTY = 52
Public Const C_T_WORKER_SORT = 53
Public Const C_T_WORKER_SOURCE = 54
Public Const C_T_WORKER_TYPE = 55
Public Const C_T_USEWORK_TYPE = 56
Public Const C_T_OLD_WAGE_SORT = 57
Public Const C_T_OLD_WAGE_RATE = 58
Public Const C_T_OLD_WAGE_EVOLVEMENT_STANDARD = 59
Public Const C_T_ORGAN = 60

Public Sub MakeComboList4Sting(ByVal form4This As VSFlexGrid, Optional int4Index As Integer = 0)
    Dim oRs4This As New ADODB.Recordset
    Dim oRs4That As New ADODB.Recordset
    Dim oRs4ComList As New ADODB.Recordset
    Dim strSQL As String
    Dim i As Long

    If int4Index = 0 Then
        strSQL = "select table_name,column_name,comlist_index from dictionary where comlist_index is not null and is_key is not null order by comlist_index "
    Else
        strSQL = "select table_name,column_name,comlist_index from dictionary where comlist_index=" & int4Index & " and is_key is not null order by comlist_index "
    End If
    If oRs4This.State = adStateOpen Then oRs4This.Close
    oRs4This.CursorLocation = adUseClient
    oRs4This.Open strSQL, g_oConnection4This, adOpenKeyset, adLockOptimistic
    frmStart.ProgressBar4Init.Value = 15
    If int4Index = 0 Then
        strSQL = "select table_name,column_name,comlist_index from dictionary where comlist_index is not null and is_key is  null order by comlist_index "
    Else
        strSQL = "select table_name,column_name,comlist_index from dictionary where comlist_index =" & int4Index & " and is_key is  null order by comlist_index "
    End If
    If oRs4That.State = adStateOpen Then oRs4That.Close
    oRs4That.CursorLocation = adUseClient
    oRs4That.Open strSQL, g_oConnection4This, adOpenKeyset, adLockOptimistic
    frmStart.ProgressBar4Init.Value = 20
    i = 0
    Do While oRs4This.EOF = False And oRs4That.EOF = False
        If oRs4This.Fields("comlist_index").Value = oRs4That.Fields("comlist_index").Value Then
            strSQL = "select * from " & oRs4This.Fields("table_name").Value
            oRs4ComList.Open strSQL, g_oConnection4This, adOpenKeyset, adLockOptimistic
            g_strArray4ColComboList(oRs4This.Fields("comlist_index").Value) = form4This.BuildComboList(oRs4ComList, oRs4That.Fields("column_name").Value, oRs4This.Fields("column_name").Value, vbGreen)
            oRs4ComList.Close
            Set oRs4ComList = Nothing
            i = i + 1
            If i = 6 And frmStart.ProgressBar4Init.Value < 100 Then
                frmStart.ProgressBar4Init.Value = frmStart.ProgressBar4Init.Value + 5
                i = 0
            End If
        Else
            MsgBox "系统启动失败！", vbCritical, "错误"
            End
        End If
        oRs4This.MoveNext
        oRs4That.MoveNext
    Loop
    '加入机构数组
    strSQL = "select ORGAN_NO,ORGAN_NAME from T_ORGAN"
    If oRs4ComList.State = adStateOpen Then oRs4ComList.Close
    oRs4ComList.Open strSQL, g_oConnection4This, adOpenKeyset, adLockOptimistic
    If frmStart.ProgressBar4Init.Value < 100 Then frmStart.ProgressBar4Init.Value = frmStart.ProgressBar4Init.Value + 5
    g_strArray4ColComboList(C_T_ORGAN) = form4This.BuildComboList(oRs4ComList, "ORGAN_NAME", "ORGAN_NO", vbGreen)
    If frmStart.ProgressBar4Init.Value < 100 Then frmStart.ProgressBar4Init.Value = frmStart.ProgressBar4Init.Value + 5
    oRs4ComList.Close
    Set oRs4ComList = Nothing
End Sub

Public Function OrganBreak(ByVal bIsChange As Boolean) As Boolean
Dim i As Long
Dim j As Long
Dim FactoryNo As String
Dim CompanyNo As String
Dim LargeNo As String
Dim SmallNo As String
On Error GoTo Err:
    OrganBreak = False
    m_strSQL = "select * from sysobjects where name = 'T_TEMP_ORGAN_BREAK'"
    If m_oRs4OrganBreak.State = adStateOpen Then m_oRs4OrganBreak.Close
    m_oRs4OrganBreak.Open m_strSQL, g_oConnection4This, adOpenKeyset, adLockOptimistic
    If m_oRs4OrganBreak.EOF = False And bIsChange = False Then
        OrganBreak = True
        Exit Function
    End If
    '删除表并且创建新表
    If m_oRs4OrganBreak.EOF Or bIsChange = True Then
        m_strSQL = "if exists(select * from sysobjects where name = 'T_TEMP_ORGAN_BREAK') drop table T_TEMP_ORGAN_BREAK"
        If ExcuteSQL(m_strSQL) <> 0 Then OrganBreak = False: Exit Function
        m_strSQL = "create table T_TEMP_ORGAN_BREAK(ORGAN_NO varchar(15) not null,FACTORY_ORGAN_NO varchar(50) null,COMPANY_ORGAN_NO varchar(50) null,LARGE_ORGAN_NO varchar(50) null,SAMLL_ORGAN_NO varchar(50) null)"
        If ExcuteSQL(m_strSQL) <> 0 Then OrganBreak = False: Exit Function
        If Len(g_str4LoginUnit) <> C_BUREAUE_LENGTH Then m_strSQL = "select organ_no,organ_level,organ_name from T_ORGAN where organ_no like '" & g_str4LoginUnit & "%' and Len(organ_no)>3 " & HaveOrganMark
        If Len(g_str4LoginUnit) = C_BUREAUE_LENGTH Then m_strSQL = "select organ_no,organ_level,organ_name from T_ORGAN where Len(organ_no)>3 " & HaveOrganMark
        If m_oRs4OrganBreak.State = adStateOpen Then m_oRs4OrganBreak.Close
        m_oRs4OrganBreak.Open m_strSQL, g_oConnection4This, adOpenKeyset, adLockOptimistic
        m_Array4OrganBreak = m_oRs4OrganBreak.GetRows
        m_lArrayCount = UBound(m_Array4OrganBreak, 2)
        For i = 0 To m_lArrayCount
            For j = 0 To m_lArrayCount
                If left(m_Array4OrganBreak(0, i), 6) = m_Array4OrganBreak(0, j) Then
                    FactoryNo = m_Array4OrganBreak(0, j)
                    Exit For
                End If
            Next
            If m_Array4OrganBreak(1, i) = 0 Then
                SmallNo = m_Array4OrganBreak(0, i)
                If Len(m_Array4OrganBreak(0, i)) = 15 Then
                    For j = 0 To m_lArrayCount
                        If m_Array4OrganBreak(0, j) = left(m_Array4OrganBreak(0, i), 9) Then CompanyNo = m_Array4OrganBreak(0, j)
                        If m_Array4OrganBreak(0, j) = left(m_Array4OrganBreak(0, i), 12) Then LargeNo = m_Array4OrganBreak(0, j)
                        If Trim(CompanyNo) <> "" And Trim(LargeNo) <> "" Then Exit For
                    Next
                ElseIf Len(m_Array4OrganBreak(0, i)) = 12 Then
                    For j = 0 To m_lArrayCount
                        If m_Array4OrganBreak(0, j) = left(m_Array4OrganBreak(0, i), 9) Then
                            If m_Array4OrganBreak(1, j) = 1 Then
                                LargeNo = m_Array4OrganBreak(0, j)
                                CompanyNo = ""
                            Else
                                CompanyNo = m_Array4OrganBreak(0, j)
                                LargeNo = ""
                            End If
                        Exit For
                        End If
                    Next
                ElseIf Len(m_Array4OrganBreak(0, i)) = 9 Then
                    LargeNo = ""
                    CompanyNo = ""
                End If
            ElseIf m_Array4OrganBreak(1, i) = 1 Then
                SmallNo = ""
                LargeNo = m_Array4OrganBreak(0, i)
                If Len(m_Array4OrganBreak(0, i)) = 12 Then
                    For j = 0 To m_lArrayCount
                        If m_Array4OrganBreak(0, j) = left(m_Array4OrganBreak(0, i), 9) Then CompanyNo = m_Array4OrganBreak(0, j): Exit For
                    Next
                ElseIf Len(m_Array4OrganBreak(0, i)) = 9 Then CompanyNo = ""
                End If
            ElseIf m_Array4OrganBreak(1, i) = 2 Then
                CompanyNo = m_Array4OrganBreak(0, i)
                SmallNo = ""
                LargeNo = ""
            ElseIf m_Array4OrganBreak(1, i) = 3 Then
                CompanyNo = ""
                SmallNo = ""
                LargeNo = ""
            End If
            m_strSQL = "insert into T_TEMP_ORGAN_BREAK(ORGAN_NO,FACTORY_ORGAN_NO,COMPANY_ORGAN_NO,LARGE_ORGAN_NO,SAMLL_ORGAN_NO) values ('" & m_Array4OrganBreak(0, i) & "','" & FactoryNo & "','" & CompanyNo & "','" & LargeNo & "','" & SmallNo & "')"
            If ExcuteSQL(m_strSQL) <> 0 Then OrganBreak = False: Exit Function
        Next
    End If
    OrganBreak = True
    Exit Function
Err:
    OrganBreak = False
End Function