ushouwen.ctl

公文管理系统》是一套通过行文收发

      Left            =   4230
      TabIndex        =   51
      Top             =   5700
      Width           =   990
   End
End
Attribute VB_Name = "UShouWen"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
Public pIP As String
Public pConn As String
Dim strUserName As String

Private Sub cmdAll_Click()
    datPrimaryRS.Recordset.Filter = ""
    datPrimaryRS.Refresh
    SSTab1.Tab = 1
End Sub

Private Sub cmdCancel_Click()
    SSTab1.Tab = 1
End Sub

Private Sub cmdFilter_Click()
    Dim strFilter As String
    '生成filter字符串
    strFilter = ""
    If Trim(txtFields(2).Text) <> "" Then
        strFilter = "年度=" & Trim(txtFields(2).Text)
    End If
    If Trim(txtFields(6).Text) <> "" Then
        If strFilter = "" Then
            strFilter = "序号=" & Trim(txtFields(6).Text)
        Else
            strFilter = strFilter & " and 序号=" & Trim(txtFields(6).Text)
        End If
    End If
    If DataCombo4.BoundText <> "" Then
        If strFilter = "" Then
            strFilter = "部门ID=" & DataCombo4.BoundText
        Else
            strFilter = strFilter & " and 部门ID=" & DataCombo4.BoundText
        End If
    End If
    If Not IsNull(DTPicker1(2).Value) Then
        If strFilter = "" Then
            strFilter = "收文日期 >= #" & Format(DTPicker1(2).Value, "yyyy-mm-dd") & "#"
        Else
            strFilter = strFilter & " and 收文日期 >= #" & Format(DTPicker1(2).Value, "yyyy-mm-dd") & "#"
        End If
    End If
    If Not IsNull(DTPicker1(3).Value) Then
        If strFilter = "" Then
            strFilter = "收文日期 <= #" & Format(DTPicker1(3).Value, "yyyy-mm-dd") & "#"
        Else
            strFilter = strFilter & " and 收文日期 <= #" & Format(DTPicker1(3).Value, "yyyy-mm-dd") & "#"
        End If
    End If
    If txtFields(10).Text <> "" Then
        If strFilter = "" Then
            strFilter = "主题词 like '%" & txtFields(10).Text & "%'"
        Else
            strFilter = strFilter & " and 主题词 like '%" & txtFields(10).Text & "%'"
        End If
    End If
    datPrimaryRS.Recordset.Filter = ""               'adFilterNone
    datPrimaryRS.Recordset.Filter = strFilter
    SSTab1.Tab = 1
End Sub

Private Sub cmdPrint_Click()
    Dim strSQL As String
    Dim rsTmp As New ADODB.Recordset
    Dim conn As New ADODB.Connection
    Dim oWrd As Object, i As Integer

    Set oWrd = CreateObject("Word.Application")
    oWrd.Visible = True
    oWrd.Activate
    oWrd.Documents.Add

    With oWrd.ActiveDocument.PageSetup
        .TopMargin = 60
        .BottomMargin = 40
        .LeftMargin = 66
        .RightMargin = 66
        .HeaderDistance = 30
        .FooterDistance = 48
        .PageWidth = 595.3
        .PageHeight = 841.9
    End With
    
    With oWrd.Selection
      
      .ParagraphFormat.Alignment = 3
      .Font.Underline = 1
      .Font.Size = 20    '小四
      .Font.Name = "宋体"
      .Font.Bold = True
      .TypeText "文件接收单"
      .Font.Underline = 0
      .Font.Size = 14
      .TypeText Space(36) & txtFields(0).Text & "收" & Format(txtFields(1).Text, "000")
      .TypeParagraph
      
      '部门
      .ParagraphFormat.Alignment = 3
      .ParagraphFormat.SpaceBefore = 24
      .ParagraphFormat.LineSpacing = 20
      .Font.Size = 11    '五号
      .Font.Name = "宋体"
      .Font.Bold = False
      .TypeText "部门名称：" & Space(2) & DataCombo1.Text
      .TypeParagraph
            
      '来文单位
      .ParagraphFormat.Alignment = 3
      .ParagraphFormat.SpaceBefore = 0
      .TypeText "来文单位：" & Space(2) & txtFields(3).Text
      .TypeParagraph
      
      '文件编号
      .ParagraphFormat.Alignment = 3
      .TypeText "文件编号：" & Space(2) & txtFields(4).Text
      .TypeParagraph
      
      '文件名称
      .ParagraphFormat.Alignment = 3
      .TypeText "文件名称：" & Space(2) & txtFields(5).Text
      .TypeParagraph
      
      '文件日期、收文日期
      .ParagraphFormat.Alignment = 3
      .TypeText "文件日期：" & Space(2) & Format(DTPicker1(0).Value, "yyyy-mm-dd") & Space(10) & "收文日期：" & Space(2) & Format(DTPicker2.Value, "yyyy-mm-dd")
      .TypeParagraph
      
      '文件页数、附件数、密级
      .ParagraphFormat.Alignment = 3
      .TypeText "文件页数：" & Space(2) & txtFields(8).Text & Space(10) & "附件数：" & Space(2) & txtFields(9).Text & Space(10) & "密级：" & Space(2) & DataCombo2.Text
      .TypeParagraph
      .TypeParagraph
      .Font.Size = 10.5
      .ParagraphFormat.LineSpacing = 15
      
      conn.ConnectionString = pConn
      conn.Open
    
      '签字表格
      strSQL = "select 签发人姓名 from 签发人 order by 签发人排序"
      rsTmp.Open strSQL, conn, adOpenStatic, adLockReadOnly
      rsTmp.MoveLast
      rsTmp.MoveFirst
      i = rsTmp.RecordCount
      .Tables.Add Range:=.Range, NumRows:=Int(i / 2 + 0.5) + 1, NumColumns:=8  ', DefaultTableBehavior:=1, AutoFitBehavior:=0
      .Tables(1).Columns(1).SetWidth ColumnWidth:=51, RulerStyle:=0
      .Tables(1).Columns(2).SetWidth ColumnWidth:=56.7, RulerStyle:=0
      .Tables(1).Columns(3).SetWidth ColumnWidth:=68, RulerStyle:=0
      .Tables(1).Columns(4).SetWidth ColumnWidth:=68, RulerStyle:=0
      .Tables(1).Columns(5).SetWidth ColumnWidth:=51, RulerStyle:=0
      .Tables(1).Columns(6).SetWidth ColumnWidth:=56.7, RulerStyle:=0
      .Tables(1).Columns(7).SetWidth ColumnWidth:=68, RulerStyle:=0
      .Tables(1).Columns(8).SetWidth ColumnWidth:=68, RulerStyle:=0
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="院领导"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="签字"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="送文日期"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="还文日期"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="院领导"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="签字"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="送文日期"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="还文日期"
      .MoveRight unit:=12
      If Not rsTmp.EOF Then
        .TypeText Text:=rsTmp.Fields("签发人姓名").Value
        rsTmp.MoveNext
      End If
      Do While Not rsTmp.EOF
        .MoveRight unit:=12, Count:=4
        .TypeText Text:=rsTmp.Fields("签发人姓名").Value
        rsTmp.MoveNext
      Loop
      
      .MoveDown unit:=5, Count:=1
      .TypeParagraph
    
      '部门表格
      strSQL = "select 部门名称 from 部门 order by 部门名称"
      rsTmp.Close
      rsTmp.Open strSQL, conn, adOpenStatic, adLockReadOnly
      rsTmp.MoveLast
      rsTmp.MoveFirst
      i = rsTmp.RecordCount
      .Tables.Add Range:=.Range, NumRows:=i + 2, NumColumns:=5   ', DefaultTableBehavior:=1, AutoFitBehavior:=0
'      .Cells.VerticalAlignment = 1
      .Tables(1).Columns(1).SetWidth ColumnWidth:=100, RulerStyle:=0
      .Tables(1).Columns(2).SetWidth ColumnWidth:=73.7, RulerStyle:=0
      .Tables(1).Columns(3).SetWidth ColumnWidth:=120, RulerStyle:=0
      .Tables(1).Columns(4).SetWidth ColumnWidth:=73.7, RulerStyle:=0
      .Tables(1).Columns(5).SetWidth ColumnWidth:=120, RulerStyle:=0
      .TypeText Text:="部门"
      .MoveRight unit:=12
      .TypeText Text:="送文"
      .MoveRight unit:=12, Count:=2
      .TypeText Text:="还文"
      .MoveRight unit:=12, Count:=3
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="签字"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="日期"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="签字"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .TypeText Text:="日期"
      .MoveUp unit:=5, Count:=1
      .MoveLeft unit:=1, Count:=1, Extend:=1
      .Cells.Merge
      .ParagraphFormat.Alignment = 1
      .MoveLeft unit:=1, Count:=2
      .MoveLeft unit:=1, Count:=1, Extend:=1
      .Cells.Merge
      .ParagraphFormat.Alignment = 1
      .MoveLeft unit:=1, Count:=2
      .MoveDown unit:=5, Count:=1, Extend:=1
      .ParagraphFormat.Alignment = 1
      .Cells.Merge
      Do While Not rsTmp.EOF
        .MoveDown unit:=5
        .TypeText Text:=rsTmp.Fields("部门名称").Value
        rsTmp.MoveNext
      Loop
      
      .HomeKey unit:=6

    End With

    Set oWrd = Nothing
    Set conn = Nothing
    Set rsTmp = Nothing
    
'    Set 打印文件接收单.Report = Nothing
'    打印文件接收单.Report.TextBM.SetText DataCombo1.Text
'    打印文件接收单.Report.TextLWDW.SetText txtFields(3).Text
'    打印文件接收单.Report.TextWJBH.SetText txtFields(4).Text
'    打印文件接收单.Report.TextWJMC.SetText txtFields(5).Text
'    打印文件接收单.Report.TextWJRQ.SetText Format(DTPicker1.Value, "yyyy-mm-dd")
'    打印文件接收单.Report.TextSWRQ.SetText Format(DTPicker2.Value, "yyyy-mm-dd")
'    打印文件接收单.Report.TextWJYS.SetText txtFields(8).Text
'    打印文件接收单.Report.TextFJS.SetText txtFields(9).Text
'    打印文件接收单.Report.TextMJ.SetText DataCombo2.Text
'    打印文件接收单.Show vbModal
End Sub

Private Sub datPrimaryRS_Error(ByVal ErrorNumber As Long, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, fCancelDisplay As Boolean)
  '错误处理程序代码置于此处
  '想要忽略错误，注释掉下一行
  '想要捕获它们，在此添加代码以处理它们
  MsgBox "Data error event hit err:" & Description
End Sub

Private Sub datPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
  '为这个 recordset 显示当前记录位置
  datPrimaryRS.Caption = CStr(datPrimaryRS.Recordset.AbsolutePosition)
  lbl记录数.Caption = CStr(datPrimaryRS.Recordset.RecordCount)
End Sub

Private Sub datPrimaryRS_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
  '验证代码置于此处
  '下列动作发生时该事件被调用
  Dim bCancel As Boolean

  Select Case adReason
  Case adRsnAddNew
  Case adRsnClose
  Case adRsnDelete
  Case adRsnFirstChange
  Case adRsnMove
  Case adRsnRequery
  Case adRsnResynch
  Case adRsnUndoAddNew
  Case adRsnUndoDelete
  Case adRsnUndoUpdate
  Case adRsnUpdate
  End Select

  If bCancel Then adStatus = adStatusCancel
End Sub

Private Sub cmdAdd_Click()
  On Error GoTo AddErr
  datPrimaryRS.Recordset.AddNew

  Exit Sub
AddErr:
  MsgBox Err.Description
End Sub

Private Sub cmdDelete_Click()
  On Error GoTo DeleteErr
  
    Dim nYN As Byte
    
     nYN = MsgBox("您正准备删除当前记录。" & Chr(13) & Chr(13) & _
        "假如您单击“是”，您将不能撤消这个删除操作。" & Chr(13) & _
        "您确认删除这条记录吗?", vbExclamation + vbYesNo)
    
    If nYN = vbYes Then
      With datPrimaryRS.Recordset
            If .EOF And .BOF Then Exit Sub
            .Delete
            .MoveNext
            
            If .RecordCount > 0 And .EOF Then
                .MoveLast
                ElseIf .RecordCount = 0 Then .MovePrevious
            End If

        End With
    End If

  Exit Sub
DeleteErr:
  MsgBox Err.Description
End Sub

Private Sub cmdRefresh_Click()
  '只有多用户应用程序需要
  On Error GoTo RefreshErr
  datPrimaryRS.Refresh
  Exit Sub
RefreshErr:
  MsgBox Err.Description
End Sub

Private Sub cmdUpdate_Click()
  On Error GoTo UpdateErr

  datPrimaryRS.Recordset.UpdateBatch adAffectAll
  Exit Sub
UpdateErr:
  MsgBox Err.Description
End Sub


Private Sub UserControl_Initialize()
    pConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OA;Data Source=lzy"
    strUserName = GetSetting("JGYOA", "Login", "UserName", "")
    
    With datPrimaryRS
        .ConnectionString = pConn
        .RecordSource = "select 年度,序号,文件编号,文件名称,文件日期,收文日期,文件页数,附件数,部门ID,来文单位,密级ID,主题词,文件分类ID,运转部门 from 收文登记 Order by 年度,序号"
        .Refresh
    End With
    With Adodc1
        .ConnectionString = pConn
        .RecordSource = "部门"
        .Refresh
    End With
    With Adodc2
        .ConnectionString = pConn
        .RecordSource = "文件密级"
        .Refresh
    End With
    With Adodc3
        .ConnectionString = pConn
        .RecordSource = "文件分类"
        .Refresh
    End With
    With DataCombo1
        Set .DataSource = datPrimaryRS
        .DataField = "部门id"
        Set .RowSource = Adodc1
        .ListField = "部门名称"
        .BoundColumn = "部门id"
    End With
End Sub