qrystockquery.frm

一套鞋厂的销售管理系统

    Next
    vsFlexGrid2.Refresh
For j = 1 To vsFlexGrid2.Cols - 1
        vsFlexGrid2.Cell(flexcpBackColor, 0, j) = &HE3FBFB   '&HBEE4E1
        vsFlexGrid2.Cell(flexcpBackColor, 1, j) = &HBEE4E1   '&HC0EEC0     '&HE3FBFB
Next

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 = "v_Shop_Stock.[Count]>0 "

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


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

                If Area <> "*" And Area <> "全国" Then
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "v_Shop_Stock.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_Shop_Stock.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_Shop_Stock.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_Shop_Stock.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_Shop_Stock.address='" & zCode & "'"
                End If

                Select Case Me.Sex
                    Case "男"
                        WhereString = IIf(WhereString = "", "", WhereString & " and ") & "left(v_Shop_Stock.OrderCode,1)='1'"
                    Case "女"
                        WhereString = IIf(WhereString = "", "", WhereString & " and ") & "left(v_Shop_Stock.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_Shop_Stock.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_Shop_Stock.OrderCode,3,1)='" & zCode & "'"
                End If

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

                If Me.Model <> "*" And Me.Model <> "" Then
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "substring(v_Shop_Stock.OrderCode,4,5)='" & Me.Model & "'"
                End If
                If Me.Product <> "*" And Me.Product <> "" Then
                    WhereString = IIf(WhereString = "", "", WhereString & " and ") & "OrderCode='" & Me.Product & "'"
                End If
                
                
                If WhereString = "" Then
                             MsgBox "请输入查询条件!", , "提示信息"
                Else
                    'MsgBox WhereString
                    If Me.Option2 Then
                       sqlstring = "SELECT  OrderName, name, developyear, [count], S340, S345, S350, S355, S360,  S365 , S370, S375, S380, S385, S390, S395, S400, S405, S410, S415, S420, S425,S430,S435,S440,S445,S450,S455,S460,S465,S470 FROM v_shop_stock  WHERE " & WhereString & " Order by OrderName, name"
                    
                    Else
                       sqlstring = "SELECT   order_list.OrderName,area1.name AS name,MIN(DISTINCT v_Shop_Stock.developyear) AS developyear, SUM(v_Shop_Stock.[Count]) As [Count], SUM(v_Shop_Stock.S340) AS S340, SUM(v_Shop_Stock.S345) AS S345, SUM(v_Shop_Stock.S350) AS S350, SUM(v_Shop_Stock.S355) AS S355, SUM(v_Shop_Stock.S360) AS S360,SUM(v_Shop_Stock.S365) AS S365," _
                                   & "SUM(v_Shop_Stock.S370) AS S370, SUM(v_Shop_Stock.S375) AS S375, SUM(v_Shop_Stock.S380) AS S380, SUM(v_Shop_Stock.S385) AS S385, SUM(v_Shop_Stock.S390) AS S390, SUM(v_Shop_Stock.S395) AS S395, SUM(v_Shop_Stock.S400) AS S400,SUM(v_Shop_Stock.S405) AS S405, SUM(v_Shop_Stock.S410) AS S410," _
                                   & "SUM(v_Shop_Stock.S415) AS S415, SUM(v_Shop_Stock.S420) AS S420,SUM(v_Shop_Stock.S425) AS S425, SUM(v_Shop_Stock.S430) AS S430,SUM(v_Shop_Stock.S435) AS S435, SUM(v_Shop_Stock.S440) AS S440, SUM(v_Shop_Stock.S445) AS S445, SUM(v_Shop_Stock.S450) AS S450, SUM(v_Shop_Stock.S455) AS S455, SUM(v_Shop_Stock.S460) AS S460,SUM(v_Shop_Stock.S465) AS S465, SUM(v_Shop_Stock.S470) AS S470 " _
                                   & "FROM area1 INNER JOIN  Shop_Dossier ON area1.code = Shop_Dossier.AreaCode RIGHT OUTER JOIN  order_list RIGHT OUTER JOIN v_Shop_Stock ON order_list.FullCode = v_Shop_Stock.OrderCode ON Shop_Dossier.Code = v_Shop_Stock.Address  WHERE " & WhereString & "  GROUP BY area1.name, order_list.OrderName  Order by order_list.OrderName,area1.name"
                    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) = "数量"
                        ReDim Counts(0 To 32)
                        For i = 1 To vsFlexGrid1.Rows - 1
                            For j = 4 To 31
                                Counts(j) = Counts(j) + Val(vsFlexGrid1.Cell(flexcpText, i, j))
                                vsFlexGrid2.Cell(flexcpText, 0, j) = Counts(j)
                            Next
                        Next
                        vsFlexGrid1_Scroll
                        
                        vsFlexGrid2.Cols = vsFlexGrid1.Cols
                        For i = 0 To vsFlexGrid1.Cols - 1
                            vsFlexGrid2.ColWidth(i) = vsFlexGrid1.ColWidth(i)
                        Next
                        vsFlexGrid2.Cell(flexcpText, 0, 2) = "合计："
                        vsFlexGrid2.Cell(flexcpText, 1, 2) = "总公司："
                        vsFlexGrid2.Refresh


    End If
'-----------------------------------------------------------------------------------------------------
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_RowColChange()
    If vsFlexGrid1.Row <> xRow Then
        Me.vsFlexGrid2.Cell(flexcpText, 1, 1) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 4) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 5) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 6) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 7) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 8) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 9) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 10) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 11) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 12) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 13) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 14) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 15) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 16) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 17) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 18) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 19) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 20) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 21) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 22) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 23) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 24) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 25) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 26) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 27) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 28) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 29) = ""
        Me.vsFlexGrid2.Cell(flexcpText, 1, 30) = ""
    
    End If
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()
On Error Resume Next
    Dim i As Long, ss As New Recordset, OrdCode As String
        xRow = Me.vsFlexGrid1.MouseRow
        xCol = Me.vsFlexGrid1.MouseCol
    If Me.vsFlexGrid1.MouseRow = 0 And Me.vsFlexGrid1.MouseCol > 0 Then
        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
    Else
        Me.vsFlexGrid2.Cell(flexcpText, 1, 1) = Me.vsFlexGrid1.Cell(flexcpText, xRow, 1)
        Opads ss, "Select * from Order_List where OrderName='" & Me.vsFlexGrid1.Cell(flexcpText, xRow, 1) & "'"
        OrdCode = ss!FullCode
        ss.Close
        Opads ss, "Select * from Stock where OrderCode='" & OrdCode & "'"
        Me.vsFlexGrid2.Cell(flexcpText, 1, 4) = ss![Count]
        Me.vsFlexGrid2.Cell(flexcpText, 1, 5) = ss!S340
        Me.vsFlexGrid2.Cell(flexcpText, 1, 6) = ss!S345
        Me.vsFlexGrid2.Cell(flexcpText, 1, 7) = ss!S350
        Me.vsFlexGrid2.Cell(flexcpText, 1, 8) = ss!S355
        Me.vsFlexGrid2.Cell(flexcpText, 1, 9) = ss!S360
        Me.vsFlexGrid2.Cell(flexcpText, 1, 10) = ss!S365
        Me.vsFlexGrid2.Cell(flexcpText, 1, 11) = ss!S370
        Me.vsFlexGrid2.Cell(flexcpText, 1, 12) = ss!S375
        Me.vsFlexGrid2.Cell(flexcpText, 1, 13) = ss!S380
        Me.vsFlexGrid2.Cell(flexcpText, 1, 14) = ss!S385
        Me.vsFlexGrid2.Cell(flexcpText, 1, 15) = ss!S390
        Me.vsFlexGrid2.Cell(flexcpText, 1, 16) = ss!S400
        Me.vsFlexGrid2.Cell(flexcpText, 1, 17) = ss!S405
        Me.vsFlexGrid2.Cell(flexcpText, 1, 18) = ss!S410
        Me.vsFlexGrid2.Cell(flexcpText, 1, 19) = ss!S415
        Me.vsFlexGrid2.Cell(flexcpText, 1, 20) = ss!S420
        Me.vsFlexGrid2.Cell(flexcpText, 1, 21) = ss!S425
        Me.vsFlexGrid2.Cell(flexcpText, 1, 22) = ss!S430
        Me.vsFlexGrid2.Cell(flexcpText, 1, 23) = ss!S435
        Me.vsFlexGrid2.Cell(flexcpText, 1, 24) = ss!S440
        Me.vsFlexGrid2.Cell(flexcpText, 1, 25) = ss!S445
        Me.vsFlexGrid2.Cell(flexcpText, 1, 26) = ss!S450
        Me.vsFlexGrid2.Cell(flexcpText, 1, 27) = ss!S455
        Me.vsFlexGrid2.Cell(flexcpText, 1, 28) = ss!S460
        Me.vsFlexGrid2.Cell(flexcpText, 1, 29) = ss!S465
        Me.vsFlexGrid2.Cell(flexcpText, 1, 30) = ss!S470
    End If
End Sub



Private Sub vsFlexGrid2_LostFocus()
vsFlexGrid2.Row = 0
End Sub