qrystockmonth.frm

一套鞋厂的销售管理系统

    .Field9 = Me.Caneta
    .Field10 = Me.Model
    .Field11 = Me.Product
    Set .DataControl1.Recordset = Me.Adodc1.Recordset
    .Show
End With
End Sub

Private Sub Form_Load()
Me.Left = 0
Me.Top = 0
ComboListChick Place, "SELECT name FROM area WHERE  style='1' Order by code;"
ComboListChick Brand, "select brandchina as name FROM order_brand;"
ComboListChick season, "select seasonname as name FROM order_season;"
Me.Area.Text = "*"
Me.Place.Text = "*"
Me.Province.Text = "*"
Me.City.Text = "*"
Me.Sex.Text = "*"
Me.Model.Text = "*"
Me.Caneta.Text = "*"
Me.shop = "*"
Me.DTPicker2.Value = Date
Me.DTPicker1.Value = Date - Day(Date) + 1
                    Me.vsFlexGrid1.ColWidth(0) = 100
                    Me.vsFlexGrid1.Cols = 8
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 1) = "产品名称"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 2) = "店名"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 3) = "本期进货"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 4) = "累计进货"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 5) = "本期销售"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 6) = "累计销售"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 7) = "本期库存"
                    Me.vsFlexGrid1.ColWidth(1) = 3000
                    Me.vsFlexGrid1.ColWidth(2) = 1200
                    Me.vsFlexGrid1.ColWidth(3) = 1000
                    Me.vsFlexGrid1.ColWidth(4) = 1000
                    Me.vsFlexGrid1.ColWidth(5) = 1200
                    Me.vsFlexGrid1.ColWidth(6) = 1200
                    Me.vsFlexGrid1.ColWidth(7) = 1200
                    
    vsFlexGrid2.Cols = vsFlexGrid1.Cols
    For i = 0 To vsFlexGrid1.Cols - 1
        vsFlexGrid2.ColWidth(i) = vsFlexGrid1.ColWidth(i)
    Next
    vsFlexGrid2.Cell(flexcpText, 0, 2) = "合计："
    vsFlexGrid2.Refresh
    vsFlexGrid1_Scroll

End Sub

Private Sub Model_DropDown()
Dim vCode As String, WhereString As String
WhereString = ""
vCode = ""
If Me.Brand <> "*" Then
    vCode = NameCode("select brandcode as code from Order_Brand where brandchina='" & Me.Brand & "' ")
    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "left(Order_Model.modelcode,1)='" & vCode & "'"
End If


If Me.season <> "*" Then
    vCode = NameCode("select seasoncode as code from Order_Season where seasonname='" & Me.season & "' ")
    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "substring(Order_Model.modelcode,2,1)='" & vCode & "'"
End If

If Me.Caneta <> "*" And Me.Caneta <> "" Then
    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "substring(Order_Model.modelcode,3,3)='" & Me.Caneta & "'"
End If
   
If WhereString <> "" Then
    ComboListChick Model, "SELECT modelcode as name FROM Order_Model  WHERE " & WhereString & " Order by modelcode;"
End If
     
End Sub

Private Sub Place_Click()
Dim vCode As String
   If Me.Place = "*" Then
       ComboListChick Province, "SELECT name FROM area WHERE  style='2' Order by code;"
   Else
       vCode = NameCode("SELECT code From AREA where name='" & Me.Place & "'")
       ComboListChick Province, "SELECT name FROM area WHERE  Province='" & vCode & "' Order by code;"
   End If

   Me.Province.Text = "*"
   Me.City.Text = "*"
   Me.shop.Text = "*"
End Sub
Private Sub Province_Click()
   Me.City.Text = "*"
   Me.shop.Text = "*"
End Sub

Private Sub shop_DropDown()
Dim vCode As String
   If Me.Province = "*" And Me.Place = "*" And Me.City = "*" Then
       ComboListChick shop, "select name FROM Shop_Dossier WHERE [level]='1'or [level]='2' and [name] is not null;"
   Else
        If (Me.Place <> "*" Or Me.Province <> "*") And Me.City = "*" Then
               MsgBox "请选择城市!", , "提示信息"
               Me.Province.SetFocus
        Else
                vCode = NameCode("SELECT code From AREA where name='" & Me.City & "'")
                ComboListChick shop, "select name FROM Shop_Dossier WHERE substring(code,5,2)='" & vCode & "' ;"
       End If
   End If
End Sub

Private Sub CmdRefrsh_Click()
SqlRunCode
End Sub
Private Sub SqlRunCode()
    Dim str1 As String
    Dim zCode As String
    Dim WhereString As String, sqlstring As String
    Dim rs1 As New ADODB.Recordset
'-------------------------------------------------------
               
                DoEvents
                zCode = ""
                WhereString = " (date between '" & Format(Me.DTPicker1, "yyyy/mm/dd") & "' and '" & Format(Me.DTPicker2, "yyyy/mm/dd") & "') "

                If Time1 <> "" Then
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "v_StockMonth.[Date]>='" & Format(Time1, "yyyy - mm - dd") & "'"
                End If


                If Time2 <> "" Then
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "v_StockMonth.[Date]<='" & Format(Time2, "yyyy-mm-dd") & "'"
                End If

                If Area <> "*" And Area <> "全国" Then
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "Shop_Dossier.Direction='" & Me.Area & "'"
                End If

                If Place <> "*" Then
                    zCode = NameCode("SELECT code From AREA where name='" & Me.Place & "'")
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "left(v_StockMonth.address,2)='" & zCode & "'"
                End If

                If Province <> "*" Then
                    zCode = NameCode("SELECT code From AREA where name='" & Me.Province & "'")
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "substring(v_StockMonth.address,3,2)='" & zCode & "'"
                End If

                If City <> "*" Then
                    zCode = NameCode("SELECT code From AREA where name='" & Me.City & "'")
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "substring(v_StockMonth.address,5,2)='" & zCode & "'"
                End If

                If Me.shop <> "*" Then
                    zCode = NameCode("SELECT code From Shop_Dossier where name='" & Me.shop & "'")
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "v_StockMonth.address='" & zCode & "'"
                End If

                Select Case Me.Sex
                    Case "男"
                        WhereString = IIf(WhereString = "", "", WhereString & " and ") & "left(v_StockMonth.OrderCode,1)='1'"
                    Case "女"
                        WhereString = IIf(WhereString = "", "", WhereString & " and ") & "left(v_StockMonth.OrderCode,1)='0'"
                End Select
                
                If Me.Brand <> "*" Then
                    zCode = NameCode("select brandcode as code from Order_Brand where brandchina='" & Me.Brand & "' ")
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "substring(v_StockMonth.OrderCode,2,1)='" & zCode & "'"
                End If


                If Me.season <> "*" Then
                   zCode = NameCode("select seasoncode as code from Order_Season where seasonname='" & Me.season & "' ")
                   WhereString = IIf(WhereString = "", "", WhereString & " and ") & "substring(v_StockMonth.OrderCode,3,1)='" & zCode & "'"
                End If

                If Me.Caneta <> "*" And Me.Caneta <> "" Then
                   WhereString = IIf(WhereString = "", "", WhereString & " and ") & "substring(v_StockMonth.OrderCode,4,3)='" & Me.Caneta & "'"
                End If

                If Me.Model <> "*" And Me.Model <> "" Then
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "substring(v_StockMonth.OrderCode,4,5)='" & Me.Model & "'"
                End If
                If Me.Product <> "*" And Me.Product <> "" Then
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "v_StockMonth.OrderCode='" & Me.Product & "'"
                End If
                
                
                If WhereString = "" Then
                             MsgBox "请输入查询条件!", , "提示信息"
                Else
                    MsgBox WhereString
                    If Me.Option2 = True Then
                            sqlstring = "SELECT v_StockMonth.OrderName AS OrderName, Max(Distinct v_StockMonth.ShopName) AS Name,SUM(distinct v_StockMonth.ThisIn) AS ThisIn, MAX(DISTINCT Shop_Stock.CountIn) AS CountIn, -SUM(distinct v_StockMonth.ThisOut) AS ThisOut,-Max(DISTINCT Shop_Stock.CountOut) AS CountOut,MAX(DISTINCT Shop_Stock.[count]) AS [count]" _
                                & " FROM v_StockMonth INNER JOIN Shop_Stock ON (v_StockMonth.OrderCode = Shop_Stock.OrderCode AND v_StockMonth.Address = Shop_Stock.Address)" _
                                & " where " & WhereString _
                                & " GROUP BY v_StockMonth.OrderName,Shop_Stock.Address   Order by  -SUM(distinct v_StockMonth.ThisOut) desc  "

                    Else
                            sqlstring = " SELECT v_StockMonth.OrderName, v_StockMonth.AreaName AS Name,SUM(distinct v_StockMonth.ThisIn) AS ThisIn,Max(Distinct v_StockArea.CountIn) as CountIn, -SUM(distinct v_StockMonth.ThisOut) AS ThisOut, -max(distinct v_StockArea.CountOut) AS CountOut,max(distinct v_StockArea.[Count]) as [count]" _
                                & " FROM v_StockArea RIGHT OUTER JOIN v_StockMonth ON v_StockArea.AreaCode = v_StockMonth.AreaCode AND v_StockArea.OrderCode = v_StockMonth.OrderCode" _
                                & " where " & WhereString _
                                & " GROUP BY v_StockMonth.OrderName, v_StockMonth.AreaName Order by  -SUM(distinct v_StockMonth.ThisOut) desc"
                    
                    
'                             sqlstring = " SELECT v_StockMonth.OrderName, v_StockMonth.AreaName AS Name,SUM(distinct v_StockMonth.ThisIn) AS ThisIn,v_StockArea.CountIn, -SUM(distinct v_StockMonth.ThisOut) AS ThisOut, -v_StockArea.CountOut AS CountOut,v_StockArea.[Count]" _
'                                & " FROM v_StockArea RIGHT OUTER JOIN v_StockMonth ON v_StockArea.AreaCode = v_StockMonth.AreaCode AND v_StockArea.OrderCode = v_StockMonth.OrderCode" _
'                                & " where " & WhereString _
'                                & " GROUP BY v_StockMonth.OrderName, v_StockMonth.AreaName,v_StockArea.CountIn, v_StockArea.CountOut, v_StockArea.[Count] Order by  -SUM(distinct v_StockMonth.ThisOut) desc"
                   
                    End If
                  'MsgBox sqlstring
                  Opadc Adodc1, sqlstring
                    
                    'MsgBox Adodc1.Recordset.RecordCount
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 1) = "产品名称"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 2) = "店名"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 3) = "本期进货"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 4) = "累计进货"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 5) = "本期销售"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 6) = "累计销售"
                    Me.vsFlexGrid1.Cell(flexcpText, 0, 7) = "库存"
                    Me.vsFlexGrid1.ColWidth(1) = 3000
                    Me.vsFlexGrid1.ColWidth(2) = 1200
                    Me.vsFlexGrid1.ColWidth(3) = 1000
                    Me.vsFlexGrid1.ColWidth(4) = 1000
                    Me.vsFlexGrid1.ColWidth(5) = 1200
                    Me.vsFlexGrid1.ColWidth(6) = 1200
                    Me.vsFlexGrid1.ColWidth(7) = 1200
                    
                    'Me.vsFlexGrid1.ColFormat(8) = "#.##%"
                    'Me.vsFlexGrid1.ColFormat(9) = "#.##%"
                    vsFlexGrid2.Cols = vsFlexGrid1.Cols
                    For i = 0 To vsFlexGrid1.Cols - 1
                        vsFlexGrid2.ColWidth(i) = vsFlexGrid1.ColWidth(i)
                    Next
                    vsFlexGrid2.Cell(flexcpText, 0, 2) = "合计："
                    
                    vsFlexGrid2.Refresh
                    vsFlexGrid1_Scroll

                End If
                
                
    ReDim Counts(1 To 5)
    For i = 1 To vsFlexGrid1.Rows - 1
        'vsFlexGrid1.Cell(flexcpText, i, 0) = i
        Counts(1) = Counts(1) + Val(vsFlexGrid1.Cell(flexcpText, i, 3))
        Counts(2) = Counts(2) + Val(vsFlexGrid1.Cell(flexcpText, i, 4))
        Counts(3) = Counts(3) + Val(vsFlexGrid1.Cell(flexcpText, i, 5))
        Counts(4) = Counts(4) + Val(vsFlexGrid1.Cell(flexcpText, i, 6))
        Counts(5) = Counts(5) + Val(vsFlexGrid1.Cell(flexcpText, i, 7))
    Next
    vsFlexGrid2.Cell(flexcpText, 0, 3) = Counts(1)
    vsFlexGrid2.Cell(flexcpText, 0, 4) = Counts(2)
    vsFlexGrid2.Cell(flexcpText, 0, 5) = Counts(3)
    vsFlexGrid2.Cell(flexcpText, 0, 6) = Counts(4)
    vsFlexGrid2.Cell(flexcpText, 0, 7) = Counts(5)
    
    Dim stock As Double, Cin As Double, COut As Double
    stock = Val(vsFlexGrid2.Cell(flexcpText, 0, 7))
    Cin = Val(vsFlexGrid2.Cell(flexcpText, 0, 4))
    COut = Val(vsFlexGrid2.Cell(flexcpText, 0, 6))
    If Cin <> 0 Then
        'vsFlexGrid2.Cell(flexcpText, 0, 8) = stock / Cin
        'vsFlexGrid2.Cell(flexcpText, 0, 9) = COut / Cin
    End If
    'Me.vsFlexGrid2.ColFormat(8) = "#.##%"
    'Me.vsFlexGrid2.ColFormat(9) = "#.##%"

    vsFlexGrid1_Scroll
'-----------------------------------------------------------------------------------------------------
End Sub

Private Sub vsFlexGrid1_AfterSort(ByVal Col As Long, Order As Integer)
    vsFlexGrid1_Scroll

End Sub

Private Sub vsFlexGrid1_AfterUserResize(ByVal Row As Long, ByVal Col As Long)
    vsFlexGrid2.ColWidth(Col) = vsFlexGrid1.ColWidth(Col)
    vsFlexGrid2.LeftCol = vsFlexGrid1.LeftCol
End Sub

Private Sub vsFlexGrid1_Scroll()
    Dim i As Long, j As Long
    On Error Resume Next
    vsFlexGrid2.LeftCol = vsFlexGrid1.LeftCol
    For i = vsFlexGrid1.TopRow To vsFlexGrid1.BottomRow
        For j = vsFlexGrid1.LeftCol To vsFlexGrid1.RightCol
            If i Mod 2 = 0 Then
                vsFlexGrid1.Cell(flexcpBackColor, i, j) = &HE3FBFB    '&HBEE4E1
            Else
                vsFlexGrid1.Cell(flexcpBackColor, i, j) = &HBEE4E1   '&HC0EEC0     '&HE3FBFB
            End If
        Next
    Next
End Sub
'Private Sub vsFlexGrid1_DblClick()
'    Dim xCol As Long, xRow As Long, i As Long
'    If Me.vsFlexGrid1.MouseRow = 0 And Me.vsFlexGrid1.MouseCol > 0 Then
'        xRow = Me.vsFlexGrid1.MouseRow
'        xCol = Me.vsFlexGrid1.MouseCol
'        Me.vsFlexGrid1.Col = xCol
'        Me.vsFlexGrid1.ColDataType(xCol) = flexDTDate
'        Debug.Print Me.vsFlexGrid1.Cell(flexcpText, 0, xCol)
'        'MsgBox Types(xCol)
'        Select Case xCol
'            Case 1, 2
'                Me.vsFlexGrid1.ColSort(xCol) = flexSortStringAscending
'                Me.vsFlexGrid1.Sort = flexSortStringAscending
'            Case Else
'                Me.vsFlexGrid1.ColSort(xCol) = flexSortNumericDescending
'                Me.vsFlexGrid1.Sort = flexSortNumericDescending
'        End Select
'        Me.vsFlexGrid1.Refresh
'        vsFlexGrid1_Scroll
'        For i = 1 To vsFlexGrid1.Rows - 1
'            vsFlexGrid1.Cell(flexcpText, i, 0) = i
'        Next
'    End If
'End Sub
'