特定条件下的数量和价格 [英] Sum Quantity and Price On Specific Condition

问题描述

我正在尝试在 excel 文件中计算价格和数量的总和，并将它们存储在数据库表中.所以这里是(自己尝试学习) - 假设，这些是 excel 文件数据:

I am trying to do a sum of price and quantity in an excel file and store them in database table. So here it's (Trying by myself for learning) - Suppose, these are the excel file data:

ProductId - Invoice No - Invoice Date - Price - Quantity
101 - Inv-1000 - 7/10/2017 10:00 - 1000 - 10
101 - Inv-1000 - 7/10/2017 10:30 - 200 - 2 
101 - Inv-1000 - 7/10/2017 10:30 - 400 - 4 'These should be merged with the above one as they have the same invoice, product id and date time
102 - Inv-1000 - 7/10/2017 10:30 - 400 - 20
101 - Inv-1001 - 7/11/2017 10:30 - 300 - 5
102 - Inv-1001 - 7/11/2017 10:30 - 200 - 5

我的要求是，如果有任何具有相同发票和发票日期时间的产品 ID，那么它应该合并这些结果，并且数据库表中的输出如下:

My requirement is if there is any product id with the same invoice and invoice date time, then it should merge those results and the output in the database table would be the following:

ProductId - Invoice No - Invoice Date - Price - Quantity - Auto No
101 - Inv-1000 - 7/10/2017 10:00 - 1000 - 10 - 1
101 - Inv-1000 - 7/10/2017 10:30 - 600 - 6 - 2 'Finally merged
102 - Inv-1000 - 7/10/2017 10:30 - 400 - 20 - 1
101 - Inv-1001 - 7/11/2017 10:30 - 300 - 5 - 1
102 - Inv-1001 - 7/11/2017 10:30 - 200 - 5 - 1

所以我尝试使用以下代码验证 sql 查询中的发票编号、产品 ID 和发票日期时间:

So I tried it with following code validating invoice no, product id and invoice date time in a sql query:

str = "SELECT IIF(SUM([Price]) IS NULL, 0, SUM([Price])) AS SumPrice, IIF(SUM([Quantity]) IS NULL, 0, SUM([Quantity])) AS SumQuantity FROM [" & strSheet & "$]" & _
      " WHERE [Invoice No] = '" + InvNo + "'" & _
      " AND [ProductId] = '" + ProductId+ "'" & _
      " AND [Invoice Date] = '" + strDate + "'"

Set rs = con.Execute(str)

但是在 WHERE 子句中，我收到这些错误 - 条件表达式中的数据类型不匹配.

But in WHERE clause, I get these error - Data type mismatch in criteria expression.

在错误消息之后，我确实只使用了带有产品 ID 的发票号来检查它是否有效(对于发票 'Inv-1000' 和产品 ID 101)，就像这样，但其余的 excel 数据不是't 加载 -

After the error message, I did use only the invoice no with product id to check if that works and it did (For the invoice 'Inv-1000' and product id 101) like this but rest of the excel data aren't loaded -

Excel 数据:

ProductId - Invoice No - Invoice Date - Price - Quantity
101 - Inv-1000 - 7/10/2017 10:00 - 1000 - 10
101 - Inv-1000 - 7/10/2017 10:30 - 200 - 2 
101 - Inv-1000 - 7/10/2017 10:30 - 400 - 4 
102 - Inv-1000 - 7/10/2017 10:30 - 400 - 20
101 - Inv-1001 - 7/11/2017 10:30 - 300 - 5
102 - Inv-1001 - 7/11/2017 10:30 - 200 - 5

数据库表中的输出:

ProductId - Invoice No - Invoice Date - Price - Quantity - Auto No
101 - Inv-1000 - 7/10/2017 10:00 - 1600 - 16 - 1

注意:还有一件事，如果总和已完成，我想再次检查或验证该总和不应为该特定发票编号、产品 ID 和发票日期输入两次(我已经在示例项目中完成了验证，但总而言之，验证是否有效?).

Note: One more thing, if the sum is done, I again would like to check or validate the sum shouldn't be entered twice for that particular invoice no, product id and invoice date (I've already done the validation in the sample project but for the sum, will the validation work?).

这里是完整的代码(使用TextBox并将excel文件保存在D目录中，最后将其写入TextBox - D:\SampleExcel.xlsx):

Here is the full code (Use a TextBox and keep the excel file in D directory, finally write this in the TextBox - D:\SampleExcel.xlsx):

Dim recordCount As Integer 'Variable to get record count
Dim i As Integer

Private Sub btnUpload_Click()
   LoadExcelSheet
End Sub

'**Method To Upload Excel File - Starts**
Public Sub LoadExcelSheet()
Dim con As ADODB.Connection
Dim conn As ADODB.Connection

'**Record Set To Check Table Records - Starts**
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset
'**Record Set To Check Table Records - Ends**

Dim i As Long

Dim strQuery As String
Dim str As String
Dim str2 As String
Dim strQuery2 As String
Dim strQuery3 As String

Dim strFile As String
Dim strSheet As String


Set con = New ADODB.Connection
Set conn = New ADODB.Connection

Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset

i = 0

strFile = txtFileName.Text
strSheet = "Sheet1"
con.Provider = "Microsoft.ACE.OLEDB.12.0"
con.ConnectionString = "Data Source = " & strFile & ";" & "Extended Properties = Excel 12.0;"

conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Demo;Data Source=.;"
con.Open

strQuery = "SELECT * FROM [" & strSheet & "$]"
strQuery2 = "SELECT ProductId, [Invoice No], [Invoice Date] FROM DataExcel"
strQuery3 = "SELECT ProductId, [Invoice No], [Invoice Date], [Price], [Quantity] FROM DataExcel"

rs.Open strQuery, con, adOpenStatic, adLockOptimistic
rs2.Open strQuery2, conn, adOpenStatic, adLockOptimistic
rs3.Open strQuery3, conn, adOpenStatic, adLockOptimistic

strDate = Format(Now, "YYYY-MM-DD") + " 00:00:00"

If (rs2.recordCount > 1) Then
  MsgBox "Few or all records already exist! Check excel file."
ElseIf (rs.Fields(0).Name <> rs3.Fields(0).Name Or rs.Fields(1).Name <> rs3.Fields(1).Name Or rs.Fields(2).Name <> rs3.Fields(2).Name Or rs.Fields(3).Name <> rs3.Fields(3).Name Or rs.Fields(4).Name <> rs3.Fields(4).Name) Then
  MsgBox "Column names don't match! Please check excel file."
Else
    Do Until rs.EOF
    Dim InvNo As String
    InvNo = rs.Fields(1).Value

    Dim AutoNo As String
    Dim AutoNo2 As Integer

    Dim ProductId As String
    ProductId = rs.Fields(0).Value

    Dim ProductId2 As Integer
    ProductId2 = rs.Fields(0).Value

    Dim InvoiceDate As String
    InvoiceDate = Trim(rs.Fields(2).Value)

    Dim Price As String
    Price = Trim(rs.Fields(3).Value)

    Dim Quantity As String
    Quantity = Trim(rs.Fields(4).Value)

    strDate = Format(InvoiceDate, "YYYY/MM/DD hh:mm:ss")

    'This is what I am doing - Checking the same invoice no, product id and invoice date.
    'If any found in the excel file, then sum up the quantity and price
    str = "SELECT IIF(SUM([Price]) IS NULL, 0, SUM([Price])) AS SumPrice, IIF(SUM([Quantity]) IS NULL, 0, SUM([Quantity])) AS SumQuantity FROM [" & strSheet & "$]" & _
          " WHERE [Invoice No] = '" + InvNo + "'" & _
          " AND [ProductId] = 101" & _
          " AND [Invoice Date] = '" + strDate + "'"

    Set rs = con.Execute(str)

    Quantity = rs.Fields("SumQuantity").Value
    Price = rs.Fields("SumPrice").Value

    'Here is the trick - Initially passed the excel file data to verify
    'and checking if any product id exists with the same invoice number in the database table
    str = "SELECT ISNULL(MAX([Auto No]),0) AS AutoNo FROM DataExcel" & _
          " WHERE [Invoice No] = '" + InvNo + "'" & _
          " AND [ProductId] = '" + ProductId + "'"

    Set rs2 = conn.Execute(str) 'Gets the auto number

    AutoNo2 = rs2.Fields("AutoNo").Value + 1 'Increments the number by one if any duplicate exists
    AutoNo = AutoNo2 & ""

    str = "INSERT INTO DataExcel (" & _
          "[ProductId], " & _
          "[Invoice No], " & _
          "[Invoice Date], " & _
          "Price, " & _
          "Quantity, " & _
          "[Auto No]" & _
          ") VALUES (" & _
          "'" + ProductId + "'," & _
          "'" + InvNo + "'," & _
          "'" + InvoiceDate + "'," & _
          "'" + Trim(Price) + "'," & _
          "'" + Trim(Quantity) + "'," & _
          "'" + AutoNo + "')"
    conn.Execute (str) 'Finally stores data with tracking or serial numbers

  rs.MoveNext
Loop
End If

rs.Close

Set rs = Nothing

con.Close
conn.Close

Set con = Nothing
Set conn = Nothing
End Sub
'**Method To Upload Excel File - Ends**

推荐答案

TBH，我没有尝试运行您的查询，因此我无法说明您的错误.但是，这里的查询:

TBH, i didn't tried to run your query, so i can't say anything about your error. But, this query here:

strQuery = "SELECT [Invoice No],[invoice Date],[ProductId],"
strQuery += "SUM(Price) AS Price,SUM(Quantity) as Quantity"
strQuery += " FROM [" & strSheet & "$]"
strQuery += " GROUP BY [Invoice No],[invoice Date],[ProductId]"

将产生以下行:

Inv-1000    2017-07-10 10:00:00 101 1000 10
Inv-1000    2017-07-10 10:30:00 101  600  6
Inv-1000    2017-07-10 10:30:00 102  400 20
Inv-1001    2017-07-11 10:30:00 101  300  5
Inv-1001    2017-07-11 10:30:00 102  200  5

因此，您可以遍历这些行以导入您的发票.

so, you can iterate over this rows to import your invoices.

关于检查已经导入的数据:

About the check for already imported data:

支票的行为应该像以前一样，您可以跳过两次导入相同的数据，只需对支票使用相同的查询 - 因为我们按唯一发票日期分组，因此也授予了整个分组的唯一性.

The check should behave like before, you can anyway skip to import the same data twice, just use the same query for the check - because we are grouping by unique invoice date, also the uniqueness of the whole grouping is granted as well.

恕我直言，我相信，使用这种分组的唯一区别是，您还需要检查 Excel 文件中的数量总和是否与导入表中已经存在的数量总和相同.然后，您可以决定使用新值更新数量总和、跳过导入或引发错误.

IMHO, i believe, the only difference here by using this kind of grouping, will be that you need to check also if the sum of the quantities in the Excel file is the same as the one already existing in the import table. Then, you can decide either to update the sum of the quantity with the new value, skip the import or raise an error.

这篇关于特定条件下的数量和价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！