table Stock
StockBalance
Status เก็บค่าสินค้าเข้าหรอืออก
Code (VB.NET)
sb.Remove(0, sb.Length)
sb.Append("SELECT p.ProductID ,p.ProductName,p.ProductSale,pd.Discount,Sum (s.StockBalance) as Balance")
sb.Append(" FROM Product p left join PromotionDetail pd on p.ProductID = pd.ProductID left join Stock s on p.ProductID = s.ProductID left join Promotion pr on pr.PromotionID = pd.PromotionID")
sb.Append(" WHERE p.ProductID=@ProductID")
sb.Append(" AND s.Status = 'I' ")
sb.Append(" Group By p.ProductID,p.ProductName,p.ProductSale,pd.Discount")
Dim dtfInfo As DateTimeFormatInfo
sqlStr = " Select p.ProductID ,p.ProductName,p.ProductSale,pd.Discount,Sum (s.StockBalance) as Balance "
sqlStr += " FROM Product p left join PromotionDetail pd on p.ProductID = pd.ProductID left join Stock s on p.ProductID = s.ProductID "
sqlStr += "left join Promotion pr on pr.PromotionID = pd.PromotionID"
sqlStr += " Where p.ProductID = '" & Str & "' "
sqlStr += " AND s.Status = 'I'"
sqlStr += " Group By p.ProductID,p.ProductName,p.ProductSale,pd.Discount"
da = New SqlDataAdapter(sqlStr, Conn)
da.Fill(ds, "Product")
dtTable11 = ds.Tables("Product")
TotalRecord = dtTable11.Rows.Count
If TotalRecord = 1 Then
ClearBinding()
txtSearch.DataBindings.Add("Text", ds.Tables("Product"), "ProductID")
lblproductname.DataBindings.Add("Text", ds.Tables("Product"), "ProductName")
txtLastCost.DataBindings.Add("Text", ds.Tables("Product"), "ProductSale")
txtStockQty.DataBindings.Add("Text", ds.Tables("Product"), "Balance")
txtAmount.Focus()
txtAmount.SelectAll()
'dr.Close()
End If
sqlStr = " Select p.ProductID,pd.Discount,StartDate,EndDate "
sqlStr += " FROM Product p left join PromotionDetail pd on p.ProductID = pd.ProductID left join Stock s on p.ProductID = s.ProductID "
sqlStr += "left join Promotion pr on pr.PromotionID = pd.PromotionID"
sqlStr += " Where '" & DtpSell.Value.ToString("s", dtfInfo) & "' BETWEEN StartDate And EndDate"
da = New SqlDataAdapter(sqlStr, Conn)
da.Fill(ds, "Promotion")
dtTable12 = ds.Tables("Promotion")
TotalRecord = dtTable12.Rows.Count
If TotalRecord = 1 Then
txtDc.DataBindings.Add("Text", ds.Tables("Promotion"), "Discount")
End If
Load balance : Server 04
