frmcheckstore.frm

注释：用VB开发的进销存系统源码

VERSION 5.00
Begin VB.Form frmCheckStore 
   BorderStyle     =   3  'Fixed Dialog
   Caption         =   "库存校验"
   ClientHeight    =   1875
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   4890
   Icon            =   "frmCheckStore.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   1875
   ScaleWidth      =   4890
   ShowInTaskbar   =   0   'False
   StartUpPosition =   1  '所有者中心
   Begin VB.CommandButton Command2 
      Caption         =   "结束"
      Height          =   570
      Left            =   2550
      TabIndex        =   1
      Top             =   600
      Width           =   1290
   End
   Begin VB.CommandButton Command1 
      Caption         =   "开始"
      Height          =   585
      Left            =   780
      TabIndex        =   0
      Top             =   615
      Width           =   1200
   End
End
Attribute VB_Name = "frmCheckStore"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub Command1_Click()
    On Error Resume Next
    Dim RR As New ADODB.Recordset
    Cmd.ActiveConnection = Conn
'    Conn.BeginTrans
    
'    Cmd.CommandText = "delete 配送中心库存"
'    Cmd.Execute
    
    RsTemp.Close
    sSQL = " SELECT a.商品编码,a.品名,a.单位,a.颜色,a.尺寸,a.数量,a.含税进价金额/(1+b.税率/100) as 进价金额,a.含税进价金额 FROM " & _
            " ( SELECT 商品编码, 品名, 单位, 颜色, 尺寸, SUM(收入数量)-Sum(发出数量) AS 数量,(sum(收入金额)/SUM(收入数量))*(SUM(收入数量)-Sum(发出数量)) as 含税进价金额 " & _
            " From V_IN_OUT_DETAIL  GROUP BY 商品编码, 品名, 单位, 颜色, 尺寸  having sum(收入数量)<>0 " & _
            " ) as a left join 商品主档 as b on a.商品编码=b.商品编码"
    RsTemp.Open sSQL, Conn, adOpenStatic, adLockReadOnly
            
'    Cmd.CommandText = " insert into 配送中心库存(商品编码,品名,单位,颜色,尺寸,数量,进价金额,含税进价金额) " & _
'            " SELECT a.商品编码,a.品名,a.单位,a.颜色,a.尺寸,a.数量,a.含税进价金额/(1*b.税率),a.含税进价金额 FROM " & _
'            " ( SELECT 商品编码, 品名, 单位, 颜色, 尺寸, SUM(收入数量)-Sum(发出数量) AS 数量,(sum(收入金额)/SUM(收入数量))*(SUM(收入数量)-Sum(发出数量)) as 含税进价金额 " & _
'            " From V_IN_OUT_DETAIL  GROUP BY 商品编码, 品名, 单位, 颜色, 尺寸  having sum(收入数量)<>0 " & _
'            " ) as a left join 商品主档 as b on a.商品编码=b.商品编码"
'    Cmd.Execute
    While Not RsTemp.EOF
'        sSQL = "insert into 配送中心库存(商品编码,品名,单位,颜色,尺寸,数量,进价金额,含税进价金额) values ('" & _
'             RsTemp("商品编码").Value & "','" & _
'             RsTemp("品名").Value & "','" & _
'             RsTemp("单位").Value & "','" & _
'             RsTemp("颜色").Value & "','" & _
'             RsTemp("尺寸").Value & "','" & _
'             RsTemp("数量").Value & "','" & _
'             RsTemp("进价金额").Value & "','" & _
'             RsTemp("含税进价金额").Value & "')"
          sSQL = " select * from 配送中心库存 where 商品编码='" & Trim(RsTemp("商品编码").Value) & _
            "' and 颜色='" & RsTemp("颜色").Value & "' and 尺寸='" & _
             RsTemp("尺寸").Value & "'"
          Set RR = Nothing
          RR.Open sSQL, Conn, adOpenStatic, adLockReadOnly
          If RR.RecordCount > 0 Then
            sSQL = "update 配送中心库存 set 数量=" & RsTemp("数量").Value & _
                ",进价金额=" & RsTemp("进价金额").Value & _
                ",含税进价金额=" & RsTemp("含税进价金额").Value & _
                " where 商品编码='" & Trim(RsTemp("商品编码").Value) & _
                "' and 颜色='" & RsTemp("颜色").Value & "' and 尺寸='" & _
                RsTemp("尺寸").Value & "'"
          Else
            sSQL = "insert into 配送中心库存(商品编码,品名,单位,颜色,尺寸,数量,进价金额,含税进价金额) values ('" & _
                 RsTemp("商品编码").Value & "','" & _
                 RsTemp("品名").Value & "','" & _
                 RsTemp("单位").Value & "','" & _
                 RsTemp("颜色").Value & "','" & _
                 RsTemp("尺寸").Value & "','" & _
                 RsTemp("数量").Value & "','" & _
                 RsTemp("进价金额").Value & "','" & _
                 RsTemp("含税进价金额").Value & "')"
        End If
        Cmd.CommandText = sSQL
        Cmd.Execute
        RsTemp.MoveNext
    Wend
'    Conn.CommitTrans
    MsgBox "校验完毕！", vbOKOnly, "提示信息"
End Sub

Private Sub Command2_Click()
    Unload Me
End Sub