frmcl.frm

适合于中小型企业管理

            AllowUpdate     =   0   'False
            AllowArrows     =   0   'False
            DefColWidth     =   57
            HeadLines       =   1
            RowHeight       =   15
            TabAction       =   2
            BeginProperty HeadFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
               Name            =   "宋体"
               Size            =   9
               Charset         =   134
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
               Name            =   "宋体"
               Size            =   9
               Charset         =   134
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            ColumnCount     =   2
            BeginProperty Column00 
               DataField       =   ""
               Caption         =   ""
               BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
                  Type            =   0
                  Format          =   ""
                  HaveTrueFalseNull=   0
                  FirstDayOfWeek  =   0
                  FirstWeekOfYear =   0
                  LCID            =   2052
                  SubFormatType   =   0
               EndProperty
            EndProperty
            BeginProperty Column01 
               DataField       =   ""
               Caption         =   ""
               BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
                  Type            =   0
                  Format          =   ""
                  HaveTrueFalseNull=   0
                  FirstDayOfWeek  =   0
                  FirstWeekOfYear =   0
                  LCID            =   2052
                  SubFormatType   =   0
               EndProperty
            EndProperty
            SplitCount      =   1
            BeginProperty Split0 
               BeginProperty Column00 
                  ColumnWidth     =   1635.024
               EndProperty
               BeginProperty Column01 
                  ColumnWidth     =   1635.024
               EndProperty
            EndProperty
         End
      End
   End
End
Attribute VB_Name = "FrmCl02"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim TxtSql As String
Public TjDate As String
Dim Rstmp As ADODB.Recordset

Private Sub Command1_Click()
    Dim CmdExe As ADODB.Command
     Set CmdExe = New ADODB.Command
     CmdExe.CommandTimeout = 0
     CmdExe.ActiveConnection = Cw_DataEnvi.DataConnect
    If CmbBm1.ListIndex > -1 Then
        CmdExe.CommandText = "execute Sd_日产量 '" & CmbBm1.Text & "','" & DTPicker0.Value & "'"
        CmdExe.Execute
    Else
        MsgBox "请选择部门名称!"
        Exit Sub
    End If

If OptGx.Value = 0 Then
    If OptCJygmx.Value = 0 Then '车间报表（默认镀膜）
        TxtSql = "SELECT 部门名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材, SUM(领一级) as 领一级, SUM(领二级) as 领二级,SUM(送检数) as 送检数, SUM(一级品) as 一级品, (sum(一级品)+sum(留用数))/sum(送检数)*100 as 正品率,SUM(留用数) as 留用数, SUM(站二级) as 站二级,SUM(站报废) as 站报废,  SUM(擦二级) as 擦二级,Sum (擦报废) as 擦报废, Sum(返工数) as 返工数 From dbo.Dm_产量表,Bs_产品图号 where 擦片人 is not null and dm_产量表.图号=Bs_产品图号.图号 and 部门名称='" _
        & CmbBm1.Text & "' GROUP BY 部门名称,Bs_产品图号.图号, Bs_产品图号.品名, Bs_产品图号.规格,Bs_产品图号.硝材 order by Bs_产品图号.图号"
        Set Rstmp = New ADODB.Recordset
        Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
        Set DataGrid1.DataSource = Rstmp

    Else                     '车间员工明细报表（默认镀膜）
        TxtSql = "SELECT 部门名称, 擦片人,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材, SUM(领一级) as 领一级, SUM(领二级) as 领二级,SUM(送检数) as 送检数, SUM(一级品) as 一级品, (sum(一级品)+sum(留用数))/sum(送检数)*100 as 正品率,SUM(留用数) as 留用数, SUM(站二级) as 站二级,SUM(站报废) as 站报废,  SUM(擦二级) as 擦二级,Sum (擦报废) as 擦报废, Sum(返工数) as 返工数 From dbo.Dm_产量表,Bs_产品图号 where dm_产量表.图号=Bs_产品图号.图号 and 擦片人 is not null and 部门名称='" _
        & CmbBm1.Text & "' GROUP BY 部门名称,Bs_产品图号.图号, Bs_产品图号.品名, Bs_产品图号.规格,Bs_产品图号.硝材, 擦片人 order by 擦片人, Bs_产品图号.图号"
        Set Rstmp = New ADODB.Recordset
        Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
        Set DataGrid1.DataSource = Rstmp

    End If
Else
    If OptCJygmx.Value = 0 Then  '车间报表（站机）
        TxtSql = "SELECT 部门名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材, SUM(领一级) as 领一级, SUM(领二级) as 领二级,SUM(送检数) as 送检数, SUM(一级品) as 一级品, (sum(一级品)+sum(留用数))/sum(送检数)*100 as 正品率,SUM(留用数) as 留用数,  SUM(站二级) as 站二级,SUM(站报废) as 站报废, SUM(擦二级) as 擦二级,Sum (擦报废) as 擦报废, Sum(返工数) as 返工数 From dbo.Dm_产量表,Bs_产品图号 where 站机人 is not null and dm_产量表.图号=Bs_产品图号.图号 and 部门名称='" _
        & CmbBm1.Text & "' GROUP BY 部门名称,Bs_产品图号.图号, Bs_产品图号.品名, Bs_产品图号.规格,Bs_产品图号.硝材 order by Bs_产品图号.图号"
        Set Rstmp = New ADODB.Recordset
        Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
        Set DataGrid1.DataSource = Rstmp
      
    Else                        '车间员工明细报表（站机）
        TxtSql = "SELECT 部门名称, 站机人,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材,  SUM(领一级) as 领一级, SUM(领二级) as 领二级,SUM(送检数) as 送检数, SUM(一级品) as 一级品, (sum(一级品)+sum(留用数))/sum(送检数)*100 as 正品率,SUM(留用数) as 留用数,  SUM(站二级) as 站二级,SUM(站报废) as 站报废, SUM(擦二级) as 擦二级,Sum (擦报废) as 擦报废, Sum(返工数) as 返工数 From dbo.Dm_产量表,Bs_产品图号 where dm_产量表.图号=Bs_产品图号.图号 and 站机人 is not null and 部门名称='" _
        & CmbBm1.Text & "' GROUP BY 部门名称, 站机人, Bs_产品图号.图号, Bs_产品图号.品名, Bs_产品图号.规格,Bs_产品图号.硝材 order by 站机人, Bs_产品图号.图号"
        Set Rstmp = New ADODB.Recordset
        Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
        Set DataGrid1.DataSource = Rstmp


    End If
End If
End Sub

Private Sub Command10_Click()
    Cdlg.DialogTitle = "另存为Excel文件："
    Cdlg.Filter = "Excel文件|*.Xls|所有文件|*.*"
    Cdlg.ShowSave
    If Cdlg.FileName = "" Then Exit Sub
         OutTxt3.Text = Cdlg.FileName
End Sub

Private Sub Command11_Click()
    Dim CmdExe As ADODB.Command
    Set CmdExe = New ADODB.Command
    CmdExe.CommandTimeout = 0
    CmdExe.ActiveConnection = Cw_DataEnvi.DataConnect
    CmdExe.CommandText = "execute Sd_车间产量 '" & DTPicker3.Value & "','" & DTPicker4.Value & "'"
    CmdExe.Execute

    TxtSql = "select 部门名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材,sum(领一级) as 领一级,sum(领二级) as 领二级,sum(送检数) as 送检数,sum(一级品) as 一级品,sum(站二级) as 站二级,sum(站报废) as 站报废,sum(擦二级) as 擦二级,sum(擦报废) as 擦报废,sum(留用数) as 留用数,sum(返工数) as 返工数 from Dm_产量表,Bs_产品图号 where dm_产量表.图号=Bs_产品图号.图号 and Bs_产品图号.品名 like '%" & TxtPm2.Text & "%' and Bs_产品图号.图号 like '%" & TxtTh2.Text & "%' and Bs_产品图号.规格 like '%" & TxtGg2.Text & "%' and 站机人 is not null group by 部门名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材 order by Bs_产品图号.图号,部门名称"
    Set Rstmp = New ADODB.Recordset
    Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
      Set DataGrid3.DataSource = Rstmp
End Sub

Private Sub Command2_Click()
On Error GoTo errs
    Dim Rs As ADODB.Recordset
    Dim ExcelApp As Excel.Application
    Dim ExcelBook As Excel.Workbook
    Dim ExcelSheet As Excel.Worksheet

    Set ExcelApp = New Excel.Application
    ExcelApp.Visible = False
    Set ExcelBook = ExcelApp.Workbooks.Add
    Set ExcelSheet = ExcelBook.Worksheets.Item(1)
    
    Set Rs = New ADODB.Recordset
     
    Rs.Open TxtSql, Cw_DataEnvi.DataConnect, , adLockPessimistic, adCmdText
    RecordsetToExcel Rs, ExcelSheet
    If OutTxt1.Text = "" Then
      MsgBox "请指定输出文件位置和文件名！", 16, "严重错误"
      Exit Sub
    End If
    On Error GoTo ErrSave
     ExcelBook.Close True, OutTxt1.Text
     MsgBox "输出成功！文件位于" & OutTxt1.Text
     Rs.Close

Exit Sub
errs:
    MsgBox "Select 语句错误！", 16, "严重错误"
    ExcelBook.Close False
     Exit Sub
ErrSave:
    MsgBox "输出错误！", 16, "严重错误"
End Sub

Private Sub Command3_Click()
    Cdlg.DialogTitle = "另存为Excel文件："
    Cdlg.Filter = "Excel文件|*.Xls|所有文件|*.*"
    Cdlg.ShowSave
    If Cdlg.FileName = "" Then Exit Sub
         OutTxt1.Text = Cdlg.FileName
End Sub

Private Sub Command4_Click()
    Dim CmdExe As ADODB.Command
     Set CmdExe = New ADODB.Command
     CmdExe.CommandTimeout = 0
     CmdExe.ActiveConnection = Cw_DataEnvi.DataConnect
    If CmbBm1.ListIndex > -1 Then
        CmdExe.CommandText = "execute Sd_日产量 '" & CmbBm1.Text & "','" & DTPicker0.Value & "'"
        CmdExe.Execute
    Else
        MsgBox "请选择部门名称!"
        Exit Sub
    End If
    DataReport3.Orientation = rptOrientLandscape
    DataReport4.Orientation = rptOrientLandscape
    DataReport5.Orientation = rptOrientLandscape
If OptGx.Value = 0 Then
    If OptCJygmx.Value = 0 Then
        DataReport4.Sections(2).Controls("LableTitle").Caption = "宇迪 " & CStr(CmbBm1.Text) & " 日产量报表"
        DataReport4.Sections(2).Controls("LabelDate").Caption = "出表时间：" & CStr(GsdateT)
            DataReport4.Sections(2).Controls("Label3").Caption = "统计日期：" & DTPicker0.Value
        TxtSql = "SELECT 部门名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材, SUM(领一级) as 领一级, SUM(领二级) as 领二级,SUM(送检数) as 送检数, SUM(一级品) as 一级品, (sum(一级品)+sum(留用数))/sum(送检数)*100 as 正品率,SUM(留用数) as 留用数, SUM(站二级) as 站二级,SUM(站报废) as 站报废,  SUM(擦二级) as 擦二级,Sum (擦报废) as 擦报废, Sum(返工数) as 返工数 From dbo.Dm_产量表,Bs_产品图号 where 擦片人 is not null dm_产量表.图号=Bs_产品图号.图号 and 部门名称='" _
        & CmbBm1.Text & "' GROUP BY 部门名称,Bs_产品图号.图号, Bs_产品图号.品名, Bs_产品图号.规格,Bs_产品图号.硝材 order by Bs_产品图号.图号"
            Set Mrc = New ADODB.Recordset
            Mrc.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
            Set DataReport4.DataSource = Mrc
            DataReport4.Refresh
            DataReport4.Show 1
            Mrc.Close
    Else
            DataReport5.Sections(2).Controls("LableTitle").Caption = "宇迪" & CStr(CmbBm1.Text) & "－擦片 日产量报表"
            DataReport5.Sections(2).Controls("LabelDate").Caption = "出表时间：" & CStr(GsdateT)
            DataReport5.Sections(2).Controls("Label3").Caption = "统计日期：" & DTPicker0.Value
        TxtSql = "SELECT 部门名称, 擦片人,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材, SUM(领一级) as 领一级, SUM(领二级) as 领二级,SUM(送检数) as 送检数, SUM(一级品) as 一级品, (sum(一级品)+sum(留用数))/sum(送检数)*100 as 正品率,SUM(留用数) as 留用数, SUM(站二级) as 站二级,SUM(站报废) as 站报废,  SUM(擦二级) as 擦二级,Sum (擦报废) as 擦报废, Sum(返工数) as 返工数 From dbo.Dm_产量表,Bs_产品图号 where dm_产量表.图号=Bs_产品图号.图号 and 擦片人 is not null and 部门名称='" _
        & CmbBm1.Text & "' GROUP BY 部门名称,Bs_产品图号.图号, Bs_产品图号.品名, Bs_产品图号.规格,Bs_产品图号.硝材, 擦片人 order by 擦片人, Bs_产品图号.图号"
            Set Mrc = New ADODB.Recordset
            Mrc.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
            Set DataReport5.DataSource = Mrc
            DataReport5.Refresh
            DataReport5.Show 1
            Mrc.Close
    End If
Else
    If OptCJygmx.Value = 0 Then
        DataReport4.Sections(2).Controls("LableTitle").Caption = "宇迪 " & CStr(CmbBm1.Text) & " 日产量报表"
        DataReport4.Sections(2).Controls("LabelDate").Caption = "出表时间：" & CStr(GsdateT)
            DataReport4.Sections(2).Controls("Label3").Caption = "统计日期：" & DTPicker0.Value
        TxtSql = "SELECT 部门名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材, SUM(领一级) as 领一级, SUM(领二级) as 领二级,SUM(送检数) as 送检数, SUM(一级品) as 一级品, (sum(一级品)+sum(留用数))/sum(送检数)*100 as 正品率,SUM(留用数) as 留用数,  SUM(站二级) as 站二级,SUM(站报废) as 站报废, SUM(擦二级) as 擦二级,Sum (擦报废) as 擦报废, Sum(返工数) as 返工数 From dbo.Dm_产量表,Bs_产品图号 where 站机人 is not null and dm_产量表.图号=Bs_产品图号.图号 and 部门名称='" _
        & CmbBm1.Text & "' GROUP BY 部门名称,Bs_产品图号.图号, Bs_产品图号.品名, Bs_产品图号.规格,Bs_产品图号.硝材 order by Bs_产品图号.图号"
            Set Mrc = New ADODB.Recordset
            Mrc.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
            Set DataReport4.DataSource = Mrc
            DataReport4.Refresh
            DataReport4.Show 1
            Mrc.Close
    Else
            DataReport3.Sections(2).Controls("LableTitle").Caption = "宇迪 " & CStr(CmbBm1.Text) & "－站机 日产量报表"
            DataReport3.Sections(2).Controls("LabelDate").Caption = "出表时间：" & CStr(GsdateT)
            DataReport3.Sections(2).Controls("Label3").Caption = "统计日期：" & DTPicker0.Value
        TxtSql = "SELECT 部门名称, 站机人,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材,  SUM(领一级) as 领一级, SUM(领二级) as 领二级,SUM(送检数) as 送检数, SUM(一级品) as 一级品, (sum(一级品)+sum(留用数))/sum(送检数)*100 as 正品率,SUM(留用数) as 留用数,  SUM(站二级) as 站二级,SUM(站报废) as 站报废, SUM(擦二级) as 擦二级,Sum (擦报废) as 擦报废, Sum(返工数) as 返工数 From dbo.Dm_产量表,Bs_产品图号 where dm_产量表.图号=Bs_产品图号.图号 and 站机人 is not null and 部门名称='" _
        & CmbBm1.Text & "' GROUP BY 部门名称, 站机人, Bs_产品图号.图号, Bs_产品图号.品名, Bs_产品图号.规格,Bs_产品图号.硝材 order by 站机人, Bs_产品图号.图号"
        Set Mrc = New ADODB.Recordset
            Mrc.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
            Set DataReport3.DataSource = Mrc
            DataReport3.Refresh
            DataReport3.Show 1
            Mrc.Close
    End If

End If
End Sub

Private Sub Command5_Click()
    Dim CmdExe As ADODB.Command
    DataReport3.Orientation = rptOrientLandscape
    DataReport4.Orientation = rptOrientLandscape
    DataReport5.Orientation = rptOrientLandscape
If OptGx2.Value = 0 Then