frmwagecustomchange.frm

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

         Top             =   255
         Width           =   2940
      End
   End
End
Attribute VB_Name = "frmWageCustomChange"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private m_str As String
Private m_strSQL As String
Private m_oRs4This As New ADODB.Recordset

Private Sub Command4Cancel_Click()
    Unload Me
End Sub

Private Sub Command4OK_Click()
    Dim i As Long
    If Trim(SSComboBoxEx4ChangField.text) = "" Or Trim(Text4NowWage.text) = "" Then MsgBox "请选择替换字段和现金额!", vbOKOnly, "提示": Exit Sub
    If Option4Select.Value = True Then
        Frame4Statues.Caption = "替换进度(共" & g_int4rowstate & "条记录）"
        DoEvents
        If Trim(SSComboBoxEx4ChangeTerm.text) <> "" Then
            ExcuteSQL "begin tran"
            For i = 1 To g_int4rowstate
                m_strSQL = "update T_WAGE_STANDARD set " & SSComboBoxEx4ChangField.ItemData(SSComboBoxEx4ChangField.ListIndex) & "=" _
                        & "cast(" & SSComboBoxEx4ChangField.ItemData(SSComboBoxEx4ChangField.ListIndex) & SSComboBoxEx4ChangeTerm.ItemData(SSComboBoxEx4ChangeTerm.ListIndex) & CDec(Trim(Text4NowWage.text)) & " as numeric(6,2))" _
                        & " where EMP_NO=" & g_array4WorkerType(1, i) & " and ORGAN_NO='" & g_array4WorkerType(2, i) & "'"
                If i = g_int4rowstate Then
                    If ExcuteSQL(m_strSQL) <> 0 Then
                        ExcuteSQL "rollback tran"
                        MsgBox "批量替换失败!", vbOKOnly, "提示"
                        ProgressBar4Statues.Value = 0
                        Exit Sub
                    End If
                Else
                    If ExcuteSQL(m_strSQL) <> 0 Then
                        ExcuteSQL "rollback tran"
                        MsgBox "批量替换失败!", vbOKOnly, "提示"
                        ProgressBar4Statues.Value = 0
                        Exit Sub
                    End If
                End If
                If ProgressBar4Statues.Value < 1000 - (1000 / g_int4rowstate) Then ProgressBar4Statues.Value = ProgressBar4Statues.Value + (1000 / g_int4rowstate)
            Next
            ExcuteSQL "commit tran"
        Else
            ExcuteSQL "begin tran"
            For i = 1 To g_int4rowstate
                m_strSQL = "update T_WAGE_STANDARD set " & SSComboBoxEx4ChangField.ItemData(SSComboBoxEx4ChangField.ListIndex) & "=" & CDec(Trim(Text4NowWage.text)) _
                        & " where EMP_NO=" & g_array4WorkerType(1, i) & " and ORGAN_NO='" & g_array4WorkerType(2, i) & "'"
                If i = g_int4rowstate Then
                    If ExcuteSQL(m_strSQL) <> 0 Then
                        ExcuteSQL "rollback tran"
                        MsgBox "批量替换失败!", vbOKOnly, "失败"
                        ProgressBar4Statues.Value = 0
                        Exit Sub
                    End If
                Else
                    If ExcuteSQL(m_strSQL) <> 0 Then
                        ExcuteSQL "rollback tran"
                        MsgBox "批量替换失败!", vbOKOnly, "提示"
                        ProgressBar4Statues.Value = 0
                        Exit Sub
                    End If
                End If
                If ProgressBar4Statues.Value < 1000 - (1000 / g_int4rowstate) Then ProgressBar4Statues.Value = ProgressBar4Statues.Value + (1000 / g_int4rowstate)
            Next
            ExcuteSQL "commit tran"
        End If
    End If
    If Option4Custom.Value = True Then
        If CheckSQL = True Then
            Frame4Statues.Caption = "替换进度(共" & m_oRs4This.Fields(0).Value & "条记录）"
            DoEvents
            ProgressBar4Statues.Value = ProgressBar4Statues.Value + 500
            If Trim(SSComboBoxEx4ChangeTerm.text) <> "" Then
                m_strSQL = "update T_WAGE_STANDARD set " & SSComboBoxEx4ChangField.ItemData(SSComboBoxEx4ChangField.ListIndex) & "=" _
                        & "cast(" & SSComboBoxEx4ChangField.ItemData(SSComboBoxEx4ChangField.ListIndex) & SSComboBoxEx4ChangeTerm.ItemData(SSComboBoxEx4ChangeTerm.ListIndex) & CDec(Trim(Text4NowWage.text)) & " as numeric(6,2))" _
                        & " where" & m_str
                If ExcuteSQL(m_strSQL) <> 0 Then
                    MsgBox "批量替换失败!", vbOKOnly, "提示"
                    ProgressBar4Statues.Value = 0
                    Exit Sub
                End If
            Else
                m_strSQL = "update T_WAGE_STANDARD set " & SSComboBoxEx4ChangField.ItemData(SSComboBoxEx4ChangField.ListIndex) & "=" & CDec(Trim(Text4NowWage.text)) _
                        & " where" & m_str
                If ExcuteSQL(m_strSQL) <> 0 Then
                    MsgBox "批量替换失败!", vbOKOnly, "提示"
                    ProgressBar4Statues.Value = 0
                    Exit Sub
                End If
            End If
        Else
            Exit Sub
        End If
    End If
    ProgressBar4Statues.Value = 1000
    MsgBox "批量替换成功!", vbOKOnly, "提示"
    ProgressBar4Statues.Value = 0
End Sub

Private Sub Form_Load()
    Dim i As Long
    If g_int4rowstate = 0 Then
        Option4Select.Enabled = False
        Option4Custom.Value = True
    Else
        Option4Select.Caption = "替换选中的" & g_int4rowstate & "条记录"
        Option4Select.Value = True
    End If
    ProgressBar4Statues.Min = 0
    ProgressBar4Statues.Max = 1000
    With VSFlexGrid4SelectTerm
        .Cols = 7
        .Rows = 2
        .ColWidth(0) = .RowHeight(0)
        .TextMatrix(0, 0) = "+"
        .TextMatrix(1, 0) = "*"
        .TextMatrix(0, 1) = "连接符"
        .TextMatrix(0, 2) = "字段名"
        .TextMatrix(0, 3) = "条件运算符"
        .TextMatrix(0, 4) = "金额"
        .TextMatrix(0, 5) = "连接符"
        .TextMatrix(0, 6) = "逻辑运算符"
        .ColWidth(1) = 650
        .ColWidth(2) = 1300
        .ColWidth(3) = 1000
        .ColWidth(4) = 650
        .ColWidth(5) = 700
        .ColWidth(6) = 850
        .ExtendLastCol = True
        For i = 1 To .Cols - 1
            .ColAlignment(i) = flexAlignCenterCenter
        Next
        .Editable = flexEDKbdMouse
        .ColComboList(1) = "#(;(|#);)|#; "
        .ColComboList(2) = MakeComboListString4VSFlex(Me.VSFlexGrid4SelectTerm, "Dictionary", "COLUMN_CNAME", "COLUMN_NAME", " where TABLE_NAME='T_WAGE_STANDARD' and COLUMN_NAME<>'EMP_NO' and COLUMN_NAME<>'ORGAN_NO' and COLUMN_NAME<>'WAGE_SORT_NO' and COLUMN_NAME<>'WAGE_RATE_NO' and COLUMN_NAME<>'AREA_SORT_NO' and COLUMN_NAME<>'AGE_WAGE' and right(COLUMN_CNAME,1) <> '）'")
        .ColComboList(3) = "#=;等于|#<>;不等于|#<;小于|#>;大于|#<=;小于等于|#>=;大于等于|#; "
        .ColComboList(5) = "#(;(|#);)|#; "
        .ColComboList(6) = "#and;并且|#or;或者|#; "
    End With
    FillComboBox SSComboBoxEx4ChangField, "Dictionary", "COLUMN_NAME", "COLUMN_CNAME", 0, "where TABLE_NAME='T_WAGE_STANDARD' and COLUMN_NAME<>'WAGE_SORT_NO' and COLUMN_NAME<>'WAGE_RATE_NO' and COLUMN_NAME<>'AREA_SORT_NO' and COLUMN_NAME<>'AGE_WAGE' and COLUMN_NAME<>'EMP_NO' and COLUMN_NAME<>'ORGAN_NO' and right(COLUMN_CNAME,1) <> '）'"
    SSComboBoxEx4ChangeTerm.AddItem "加", 0
    SSComboBoxEx4ChangeTerm.ItemData(0) = "+"
    SSComboBoxEx4ChangeTerm.AddItem "减", 1
    SSComboBoxEx4ChangeTerm.ItemData(1) = "-"
    SSComboBoxEx4ChangeTerm.AddItem "乘", 2
    SSComboBoxEx4ChangeTerm.ItemData(2) = "*"
    SSComboBoxEx4ChangeTerm.AddItem "除", 3
    SSComboBoxEx4ChangeTerm.ItemData(3) = "/"
    SSComboBoxEx4ChangeTerm.AddItem "", 4
    SSComboBoxEx4ChangeTerm.ItemData(4) = ""
    Call ControlIsEnabled
End Sub

Private Sub ControlIsEnabled()
    If Option4Select.Value = True Then VSFlexGrid4SelectTerm.Enabled = False
    If Option4Custom.Value = True Then VSFlexGrid4SelectTerm.Enabled = True
End Sub

Private Sub Option4Custom_Click()
    Call ControlIsEnabled
End Sub

Private Sub Option4Select_Click()
    Call ControlIsEnabled
End Sub

Private Sub Text4NowWage_KeyPress(KeyAscii As Integer)
    CheckText KeyAscii, Text4NowWage.text, Text4NowWage.SelStart, True
End Sub

Private Sub VSFlexGrid4SelectTerm_Click()
    With VSFlexGrid4SelectTerm
        If .MouseRow < 0 Then Exit Sub
        If .MouseCol = 0 Then
            If .MouseRow = 0 Then
                .TextMatrix(.Rows - 1, 0) = ""
                .AddItem "*"
            End If
            If .MouseRow = .Rows - 1 And .Rows - 1 > 1 Then
                .RemoveItem .Rows - 1
                .TextMatrix(.Rows - 1, 0) = "*"
            End If
        End If
        If .Col = 4 And .Row > 0 And .TextMatrix(.Row, 2) <> "" Then
            If .TextMatrix(.Row, 2) = "AGE_WAGE" Then .ColComboList(4) = "": Exit Sub
            .ColComboList(4) = "|" & MakeComboListString4Wage(VSFlexGrid4SelectTerm, "T_WAGE_STANDARD", .TextMatrix(.Row, 2)): Exit Sub
        End If
    End With
End Sub

Private Function MakeComboListString4Wage(ByRef form4This As VSFlexGrid, ByVal strTableName As String, ByVal strFieldName As String, Optional ByVal strCond As String = "") As String
    Dim strSQL As String
    Dim oRs4This As New ADODB.Recordset
    
    strSQL = "select distinct " & strFieldName & " from " & strTableName & strCond
    oRs4This.Open strSQL, g_oConnection4This, adOpenKeyset, adLockOptimistic
    
    MakeComboListString4Wage = form4This.BuildComboList(oRs4This, strFieldName, , vbGreen)
    If MakeComboListString4Wage = "" Then MakeComboListString4Wage = " "
    oRs4This.Close
    Set oRs4This = Nothing
End Function

Private Function CheckSQL() As Boolean
    Dim i As Long
    Dim strLen As Long
    Dim intLeftCount As Long
    Dim intRightCount As Long
On Error GoTo Err:
    CheckSQL = False
    With VSFlexGrid4SelectTerm
        m_str = ""
        For i = 1 To .Rows - 1
            If Trim(.TextMatrix(i, 2)) = "" And Trim(.TextMatrix(i, 3)) = "" And Trim(.TextMatrix(i, 4)) = "" Then
                .RemoveItem i
                i = i - 1
            ElseIf Trim(.TextMatrix(i, 2)) = "" Or Trim(.TextMatrix(i, 3)) = "" Or Trim(.TextMatrix(i, 4)) = "" Then
                MsgBox "第" & i & "行缺少字段名、条件运算符或金额，请输入完整条件!", vbOKOnly, "提示"
                Exit Function
            ElseIf (Trim(.TextMatrix(i, 6)) = "" And i <> .Rows - 1) Or (Trim(.TextMatrix(i, 6)) <> "" And i = .Rows - 1) Then
                MsgBox "第" & i & "行逻辑运算符出错!", vbOKOnly, "提示"
                Exit Function
            Else
                m_str = m_str & " " & .TextMatrix(i, 1) & " " & .TextMatrix(i, 2) & " " & .TextMatrix(i, 3) & " " & .TextMatrix(i, 4) & " " & .TextMatrix(i, 5) & " " & .TextMatrix(i, 6)
            End If
        Next
        If m_str = "" Then MsgBox "请输入替换条件!", vbOKOnly, "提示": Exit Function
        strLen = Len(m_str)
        intLeftCount = 0
        intRightCount = 0
        For i = 1 To strLen
            If Mid(m_str, i, 1) = "(" Then intLeftCount = intLeftCount + 1
            If Mid(m_str, i, 1) = ")" Then intRightCount = intRightCount + 1
        Next
        If intLeftCount <> intRightCount Then MsgBox "查询条件中括号输入错误!" & vbCr & m_str, vbOKOnly, "错误": Exit Function
    End With
    If m_oRs4This.State = adStateOpen Then m_oRs4This.Close
    m_oRs4This.CursorLocation = adUseClient
    m_strSQL = "select count(*) from T_WAGE_STANDARD where" & m_str
    m_oRs4This.Open m_strSQL, g_oConnection4This, adOpenKeyset, adLockOptimistic
    If vbYes = MsgBox("根据查询条件共查到" & m_oRs4This.Fields(0).Value & "条记录,是否要进行替换?", vbYesNo, "提示") Then
        CheckSQL = True
    Else
        CheckSQL = False
    End If
    Exit Function
Err:
    MsgBox "查询条件出错!", vbOKOnly, "错误"
End Function