frm分店销售查询.frm

服装销售系统,VB开发.没有解压密码

         _ExtentY        =   1005
         _Version        =   131073
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "宋体"
            Size            =   9
            Charset         =   134
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Caption         =   "打印[&P]"
         ButtonStyle     =   3
         PictureAlignment=   6
      End
      Begin Threed.SSCommand cmdQuery 
         Height          =   570
         Left            =   15
         TabIndex        =   8
         TabStop         =   0   'False
         Top             =   -30
         Width           =   1170
         _ExtentX        =   2064
         _ExtentY        =   1005
         _Version        =   131073
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "宋体"
            Size            =   9
            Charset         =   134
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Caption         =   "刷新[&R]"
         ButtonStyle     =   3
         PictureAlignment=   6
      End
      Begin Threed.SSCommand cmdExit 
         Height          =   570
         Left            =   2415
         TabIndex        =   3
         TabStop         =   0   'False
         Top             =   0
         Width           =   1170
         _ExtentX        =   2064
         _ExtentY        =   1005
         _Version        =   131073
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "宋体"
            Size            =   9
            Charset         =   134
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Caption         =   "退出[&X]"
         ButtonStyle     =   3
         PictureAlignment=   6
      End
   End
   Begin SSDataWidgets_B_OLEDB.SSOleDBGrid grdR 
      Align           =   2  'Align Bottom
      Height          =   3900
      Left            =   0
      TabIndex        =   20
      Top             =   2070
      Width           =   9645
      _Version        =   196617
      ForeColorEven   =   0
      BackColorOdd    =   16777215
      RowHeight       =   450
      Columns(0).Width=   3200
      Columns(0).DataType=   8
      Columns(0).FieldLen=   4096
      _ExtentX        =   17013
      _ExtentY        =   6879
      _StockProps     =   79
      Caption         =   "分店销售查询"
   End
End
Attribute VB_Name = "frmQueryChainSale"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Option Explicit


Public Rs As New ADODB.Recordset               '用于只打开单记录集时

Public Sub CalculateTotal()
    On Error Resume Next
    Dim I, Qty, sum
    Qty = 0
    sum = 0
    Rs.MoveFirst
    
    While Not Rs.EOF
        Qty = Qty + Rs("数量")
        sum = sum + Rs("金额")
        Rs.MoveNext
    Wend
    AD.Panels("MSG").Text = "查询结果数量为:" & Rs.RecordCount & "数量合计:" & CStr(Qty) & "金额合计:" & CStr(sum)
End Sub

Private Function GenerateQuerySQLDet() As String
    Dim strTemp As String
    
    sSQL = "select 表单号,配送日期 as 日期,分店编码,分店名称,商品编码,品名,颜色,尺寸,单位,sum(配送数量) as 数量,零售价 as 单价,sum(售价金额) as 金额 from lschainxsd"
    
    If txtSuppno.Text <> "" Then
        strTemp = strTemp & " 分店编码  like '" & _
                txtSuppno.Text & "' AND "
    End If
    
    If txtClientName.Text <> "" Then
        strTemp = strTemp & " 分店名称  like '" & _
                txtClientName.Text & "' AND "
    End If
    
    If txtIncode.Text <> "" Then
        strTemp = strTemp & " 商品编码 " & _
                AnalyseCondition(txtIncode.Text, True) & " AND "
    End If
    If txtSname.Text <> "" Then
        strTemp = strTemp & " 品名 " & _
                AnalyseCondition(txtSname.Text, True) & " AND "
    End If
    sSQL = sSQL & " WHERE " & strTemp & " 配送日期 between '" & Format(dtpDateBegin.Value, "yyyy-mm-dd") & "' and '" & Format(dtpDateEnd.Value, "yyyy-mm-dd") & "' and 确认状态=1 "
    sSQL = sSQL & " group by 表单号,配送日期,分店编码,分店名称,商品编码,品名,颜色,尺寸,单位,零售价 ORDER BY 商品编码"
    GenerateQuerySQLDet = sSQL
End Function


Private Function GenerateQuerySQLTotal() As String
    Dim strTemp As String
    
    sSQL = "select 表单号,分店编码,分店名称,商品编码,品名,单位,sum(配送数量) as 数量,零售价 as 单价,sum(售价金额) as 金额 from lschainxsd"
    
    If txtSuppno.Text <> "" Then
        strTemp = strTemp & " 分店编码  like '" & _
                txtSuppno.Text & "' AND "
    End If

    
    If txtClientName.Text <> "" Then
        strTemp = strTemp & " 分店名称 like '" & _
                txtClientName.Text & "' AND "
    End If
    
    If txtIncode.Text <> "" Then
        strTemp = strTemp & " 商品编码 " & _
                AnalyseCondition(txtIncode.Text, True) & " AND "
    End If
    If txtSname.Text <> "" Then
        strTemp = strTemp & " 品名 " & _
                AnalyseCondition(txtSname.Text, True) & " AND "
    End If
    sSQL = sSQL & " WHERE " & strTemp & " 配送日期 between '" & Format(dtpDateBegin.Value, "yyyy-mm-dd") & "' and '" & Format(dtpDateEnd.Value, "yyyy-mm-dd") & "' and 确认状态=1 "
    sSQL = sSQL & " group by 表单号,分店编码,分店名称,商品编码,品名,单位,零售价 ORDER BY 商品编码"
    GenerateQuerySQLTotal = sSQL
End Function



Private Sub cmdExit_Click()
    Set Rs = Nothing
    Set RsTemp = Nothing
    Unload Me
End Sub


Private Sub cmdQuery_Click()
    On Error Resume Next
    If optDet.Value Then
        sSQL = GenerateQuerySQLDet()
    Else
        sSQL = GenerateQuerySQLTotal()
    End If
    Set Rs = Nothing
    Rs.Open sSQL, Conn, adOpenStatic, adLockReadOnly
    If Rs.EOF Then
        MsgBox "无匹配记录！", vbInformation, "提示窗口"
        Exit Sub
    End If
    Set grdR.DataSource = Nothing
    grdR.Refresh
    Set grdR.DataSource = Rs
    grdR.Refresh
    
    Call CalculateTotal
    'AD.Panels("MSG").Text = "查询结果数量为:" & Rs.RecordCount
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then SendKeys "{TAB}"
End Sub


Private Sub txtPrd_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then
        Call cmdQuery_Click
    End If
End Sub

Private Sub Form_Load()
    Call SetFormToCenter(Me)
    dtpDateBegin.Value = Now
    dtpDateEnd.Value = Now
End Sub

Private Sub Form_Resize()
    On Error Resume Next
    grdR.Height = Me.ScaleHeight - Picture1.Height - AD.Height - Frame1.Height
    AD.Height = AD.Height

End Sub

Private Sub SSCommand1_Click()
    grdR.PrintData ssPrintAllRows, True, True
End Sub

Private Sub txtSuppno_InitColumnProps()
    On Error GoTo LinkErr
    Dim Rs As New ADODB.Recordset
    Set Rs = Nothing
    Rs.Open "SELECT * FROM 分店主档 order by 分店编码", Conn, adOpenStatic, adLockReadOnly
    While Not Rs.EOF
        txtSuppno.AddItem Rs("分店编码") & vbTab & Rs("分店名称")
        Rs.MoveNext
    Wend
    Exit Sub
LinkErr:
    MsgBox "初始化数据错误！" & Err.Description, vbExclamation, "错误窗口"
End Sub