frmpolicyedit.frm

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

         Height          =   345
         Left            =   90
         TabIndex        =   7
         Top             =   915
         Width           =   1665
      End
      Begin VB.Label Label4IndexPolicy 
         Caption         =   "政策文件文号："
         Height          =   330
         Left            =   3765
         TabIndex        =   6
         Top             =   90
         Width           =   1890
      End
      Begin VB.Label Label4Name 
         Caption         =   "政策名称："
         Height          =   330
         Left            =   90
         TabIndex        =   5
         Top             =   90
         Width           =   1665
      End
   End
End
Attribute VB_Name = "frmPolicyEdit"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private m_oRs4This As New ADODB.Recordset
'Private m_oRs4FillCombo As New ADODB.Recordset
Private m_strSQL As String
Private m_str4FileName As String

'Private Sub Combo4PolicyFileCode_LostFocus()
'    If m_oRs4FillCombo.State = adStateOpen Then m_oRs4FillCombo.Close
'    m_oRs4This.Open ("select policy_file_name from T_policy_file where policy_file_code like '%" & Me.Combo4PolicyFileCode.text & "%'"), g_oConnection4This, adOpenKeyset, adLockOptimistic
'    If Not m_oRs4This.EOF Then
'        Me.Combo4PolicyFileName.text = m_oRs4This.Fields("policy_file_name")
'    Else
'        Me.Combo4PolicyFileName.text = Me.Combo4PolicyFileName.text
'    End If
'    m_oRs4FillCombo.Open "select policy_file_code from T_policy_file where policy_file_code='" & Me.Combo4PolicyFileCode.text & "'", , g_oConnection4This, adOpenKeyset, adLockOptimistic
'    FillComboBoxByBxm Me.Combo4PolicyFileName, "T_policy_file", "policy_file_code ", "policy_file_name", 1, "where policy_file_code='" & Me.Combo4PolicyFileCode.text & "'"
'End Sub

'Private Sub Combo4PolicyFileName_LostFocus()
'    If m_oRs4This.State = 1 Then m_oRs4This.Close
'    m_oRs4This.Open ("select policy_file_code from T_policy_file where policy_file_name like '%" & Me.Combo4PolicyFileName.text & "%'"), g_oConnection4This, adOpenKeyset, adLockOptimistic
'    If Not m_oRs4This.EOF Then
'        Me.Combo4PolicyFileCode.text = m_oRs4This.Fields("policy_file_code")
'    Else
'        Me.Combo4PolicyFileCode.text = Me.Combo4PolicyFileCode.text
'    End If
'
'End Sub

Private Sub Command4Close_Click()
    Unload Me
End Sub

Private Sub Command4Save_Click()
    Dim lNo As Long
    Dim l4This As Long
    Dim ifor As Long
    Dim oRs4policyContent As New ADODB.Recordset
    If Trim(Me.Combo4PolicyName) = "" Then
         MsgBox "政策名称不能为空！", vbOKOnly, "警告"
         Exit Sub
    End If
    If Trim(Me.Combo4PolicyFileName) = "" Then
         MsgBox "政策名称文件不能为空！", vbOKOnly, "警告"
         Exit Sub
    End If
    If Trim(Me.Combo4PolicyFileCode) = "" Then
         MsgBox "政策文件编号不能为空！", vbOKOnly, "警告": Exit Sub
         Exit Sub
    End If
    If Me.DTP4PolicyFileTime.Value = "" Then
         MsgBox "发文日期不能为空！", vbOKOnly, "警告": Exit Sub
         Exit Sub
    End If
    ExcuteSQL "begin transaction"
    If g_int4statuse = 0 Then
        '插入政策分类表，如果政策分类的名字存在就不做插入
        m_strSQL = "if not exists(select policy_name from t_policy where policy_name like '%" & Me.Combo4PolicyName.text & "%')" _
                   & " insert into t_policy(policy_name) values('" & Me.Combo4PolicyName.text & "')"
        If ExcuteSQL(m_strSQL) <> 0 Then
            ExcuteSQL "rollback transaction"
            Exit Sub
        End If
        '插入政策文件表
        If m_oRs4This.State = adStateOpen Then m_oRs4This.Close
        m_oRs4This.Open ("select policy_file_code from T_policy_file where policy_file_code like '%" & Me.Combo4PolicyFileCode.text & "%'"), g_oConnection4This, adOpenKeyset, adLockOptimistic
        If Not m_oRs4This.EOF Then
            If Me.Combo4PolicyFileCode.text = m_oRs4This.Fields("policy_file_code") Then
                MsgBox "数据库中有相同的政策文件编号相符 ", vbOKOnly, "警告"
                ExcuteSQL "rollback transaction"
                Exit Sub
            End If
        Else
            m_strSQL = " declare @PolicyNo int "
            m_strSQL = m_strSQL & "select @PolicyNo=(select Policy_No from T_policy  where policy_name like '%" & Me.Combo4PolicyName.text & "%')" & " from T_policy "
            m_strSQL = m_strSQL & "insert into t_policy_file(policy_file_name,policy_file_code,policy_file_note,policy_file_time,policy_no) " _
                       & " values ('" & Me.Combo4PolicyFileName.text & "','" & Me.Combo4PolicyFileCode.text & "',"
            If Me.Text4PolicyFileNote.text <> "" Then
                m_strSQL = m_strSQL & "'" & Me.Text4PolicyFileNote.text & "','"
            Else
                m_strSQL = m_strSQL & "null,'"
            End If
               m_strSQL = m_strSQL & Me.DTP4PolicyFileTime.Value & "',@PolicyNo" & ")"
        End If
        If ExcuteSQL(m_strSQL) <> 0 Then
            ExcuteSQL "rollback transaction"
            Exit Sub
        End If
         '插入政策文件明细表
        With Me.VSFlexGrid4This
            For ifor = 1 To .Rows - 1
                m_strSQL = "declare @PolicyFileNo int "
                m_strSQL = m_strSQL & "select @PolicyFileNo=(select Policy_file_No from T_policy_file  where policy_file_name like '%" & Me.Combo4PolicyFileName.text & "%')" & " from T_policy_File "
                m_strSQL = m_strSQL & "INSERT into t_policy_file_detail(POLICY_FILE_NO,FILE_PATH,DETAIL_NO,DETAIL_CONTENT) values(" _
                                    & "@PolicyFileNo,'"
                m_strSQL = m_strSQL & .TextMatrix(ifor, 2) & "'," & CSQL(.TextMatrix(ifor, 1), 0) & ",null)"
                If ExcuteSQL(m_strSQL) <> 0 Then
                    ExcuteSQL "rollback transaction"
                    Exit Sub
                End If
                m_strSQL = "select * from t_policy_file_detail where  DETAIL_id=(select max(DETAIL_id) from t_policy_file_detail)"
                If oRs4policyContent.State = adStateOpen Then oRs4policyContent.Close
                oRs4policyContent.CursorLocation = adUseClient
                oRs4policyContent.Open m_strSQL, g_oConnection4This, adOpenKeyset, adLockOptimistic
                DoEvents
                '存放二进制
                FileToBlob .TextMatrix(ifor, 2), oRs4policyContent.Fields("DETAIL_CONTENT"), BLOCK_SIZE
                oRs4policyContent.Update
             Next
        End With
    End If
        
    ExcuteSQL "commit transaction"
    MsgBox "保存完毕!", vbOKOnly, "成功"
    FillComboBoxByBxm Me.Combo4PolicyName, "T_policy", "policy_no ", "policy_name", 1
    FillComboBoxByBxm Me.Combo4PolicyFileCode, "T_policy_file", " ", "policy_file_code", 1
    FillComboBoxByBxm Me.Combo4PolicyFileName, "T_policy_file", "policy_file_no ", "policy_file_name", 1
    If g_int4statuse = 0 Then
        ClearText
    Else
        If g_int4rowstate = 1 Then Unload Me
    End If
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then SendKeys "{tab}"
End Sub

Private Sub Form_Load()
    Dim oRs4PolicyNo As New ADODB.Recordset
    Me.Label4IndexPolicy.ForeColor = g_ForeColor4NotNullFields
    Me.DTP4PolicyFileTime.CheckBox = True
    Me.DTP4PolicyFileTime.Value = Date

    If g_int4statuse = 0 Then
        VSFlexGrid4This_AfterDataRefresh
        SSSpin4This.Visible = False
    Else
       If g_int4rowstate = 1 Then
            Me.SSSpin4This.Visible = False
            SSSpin4This.text = 1
        Else
            SSSpin4This.Visible = True
            SSSpin4This.SpinDatatype = ssNumerics
            SSSpin4This.MaxValue = g_int4rowstate
            SSSpin4This.MinValue = 1
            SSSpin4This.Increment = 1
            SSSpin4This.text = 1
        End If
        SSSpin4This_Change
    End If
'    Combo4PolicyName.AddItem "综合类"
'    Combo4PolicyName.AddItem "人事管理"
'    Combo4PolicyName.AddItem "党组织管理"
'    Combo4PolicyName.AddItem "劳动组织"
'    Combo4PolicyName.AddItem "劳动报酬"
'    Combo4PolicyName.AddItem "劳动关系"
'    Combo4PolicyName.AddItem "职工培训"
'    Combo4PolicyName.AddItem "职业技能鉴定"
'    Combo4PolicyName.AddItem "劳动保障"
'    Combo4PolicyName.AddItem "人才交流"
    FillComboBoxByBxm Me.Combo4PolicyName, "T_policy", "policy_no ", "policy_name", 1
    FillComboBoxByBxm Me.Combo4PolicyFileCode, "T_policy_file", " ", "policy_file_code", 1
    FillComboBoxByBxm Me.Combo4PolicyFileName, "T_policy_file", "policy_file_no ", "policy_file_name", 1
End Sub

Private Sub Form_Resize()
'    SetWindowPos Me.hwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_SHOWWINDOW
End Sub

Private Sub ClearText()
'    Me.Combo4PolicyFileCode.text = ""
'    Me.Text4PolicyFileNote.text = ""
'    Me.Combo4PolicyFileName.text = ""
    Me.VSFlexGrid4This.Rows = 1
End Sub

Private Sub LoadData()
    m_strSQL = "select policy_name,Policy_File_Name,POLICY_FILE_TIME," & _
        "Policy_File_Code from T_policy where policy_no =" & g_array4WorkerType(1, SSSpin4This.text)
    If m_oRs4This.State = adStateOpen Then m_oRs4This.Close
    m_oRs4This.CursorLocation = adUseClient
    m_oRs4This.Open m_strSQL, g_oConnection4This
'    Me.Text4Name.text = CheckVariant(m_oRs4This.Fields("policy_name").Value)
'    Me.Text4Index.text = CheckVariant(m_oRs4This.Fields("policy_index").Value)
'    Me.Text4Note.text = CheckVariant(m_oRs4This.Fields("policy_note").Value)
'    Me.DTPicker4Date.Value = ConvertInt2Date(m_oRs4This.Fields("policy_start_date").Value)
    m_strSQL = "select POLICY_DETAIL_CODE,POLICY_DETAIL_NAME,POLICY_DETAIL_NO from t_policy_detail where policy_no =" & g_array4WorkerType(1, SSSpin4This.text) & " order by POLICY_DETAIL_CODE "
    If m_oRs4This.State = adStateOpen Then m_oRs4This.Close
    m_oRs4This.CursorLocation = adUseClient
    m_oRs4This.Open m_strSQL, g_oConnection4This, adOpenKeyset, adLockOptimistic
    Set Me.VSFlexGrid4This.DataSource = m_oRs4This
End Sub

Private Sub SSSpin4This_Change()
    MousePointer = 11
    Me.SSSpin4This.EnableContextMenu = False
    LoadData
    Me.SSSpin4This.EnableContextMenu = True
    MousePointer = 1
End Sub

Private Sub VSFlexGrid4This_AfterDataRefresh()
    Dim ifor As Long
    With VSFlexGrid4This
        .ColWidth(0) = .RowHeight(0)
        .Editable = flexEDKbdMouse
        If g_int4statuse = 0 Then .Rows = 1
        .Cols = 4
        .TextMatrix(0, 0) = "+"
        .TextMatrix(0, 1) = "序号"
        .TextMatrix(0, 2) = "政策文件内容"
        .ColHidden(3) = True
        .ExtendLastCol = True
        .ColComboList(2) = "..."
        .AutoSize 1, .Cols - 1
        .AllowUserResizing = flexResizeColumns
        .AllowBigSelection = False
        .AllowSelection = False
        .ExplorerBar = flexExSortShow
        .SelectionMode = flexSelectionByRow
        .ExtendLastCol = True
        For ifor = 1 To .Rows - 1
            .TextMatrix(ifor, 0) = "*"
            .Cell(flexcpData, ifor, 2) = .TextMatrix(ifor, 2)
        Next
    End With
End Sub

Private Sub VSFlexGrid4This_CellButtonClick(ByVal Row As Long, ByVal Col As Long)
    With CmnDlg4PolicyEdit
        .Filter = "Bitmap (*.bmp,*.dib)|*.bmp,*.dib|Gif Image (*.gif)|*.gif|Jpeg Image (*.jpg)|*.jpg|Wetafiles (*.wmf,*.Emf)|*.wmf,*.Emf|Icon Files (*.ico,*.cur)|*.ico,*.cur|All files (*.*)|*.*"
        .DialogTitle = "打开源文件"
        .ShowOpen
        On Error Resume Next
        If .FileName = "" Then Exit Sub
        'Me.VSFlexGrid4This.Cell(flexcpData, Me.VSFlexGrid4This.Row, 2) = .FileName
        Me.VSFlexGrid4This.TextMatrix(Me.VSFlexGrid4This.Row, 2) = .FileName
        If g_int4statuse = 0 Then Me.VSFlexGrid4This.TextMatrix(Me.VSFlexGrid4This.Row, 1) = Me.VSFlexGrid4This.Row
    End With
End Sub

Private Sub VSFlexGrid4This_Click()
    Dim RealRow As Long
    With Me.VSFlexGrid4This
        RealRow = .MouseRow
        If .MouseRow < 0 Then Exit Sub
        If .MouseCol = 0 Then
            If .MouseRow = 0 Then
                .AddItem "*"
            Else
                If vbYes = MsgBox("确认删除该记录吗?", vbYesNo + vbDefaultButton2, "警告") Then
                    If Trim(.TextMatrix(RealRow, 3)) = "" Then
                        .RemoveItem RealRow
                    Else
                        m_strSQL = "delete from t_POLICY_DETAIL where POLICY_DETAIL_NO=" & .TextMatrix(RealRow, 3)
                        If ExcuteSQL(m_strSQL) = 0 Then .RemoveItem RealRow
                    End If
                End If
            End If
        End If
    End With
End Sub

Private Sub VSFlexGrid4This_KeyPressEdit(ByVal Row As Long, ByVal Col As Long, KeyAscii As Integer)
    If Row <= 0 Then Exit Sub
    If Col = 1 Then CheckText KeyAscii
End Sub