Dim StockQty As Integer
Dim NewRealQuantity As Integer = 0
While (CAmount > 0)
sb.Remove(0, sb.Length)
sb.Append("SELECT select top 1 StockLotNo,ProductID,")
sb.Append("StockQtyIn")
sb.Append(" FROM Stock")
sb.Append(" WHERE (StockQtyIn>0)")
sb.Append(" AND (ProductID=@ProductID)")
sb.Append(" ORDER BY StockLotNo ")
sqlSave = sb.ToString()
With com
.CommandText = sqlSave
.Parameters.Clear()
.Parameters.Add("@ProductID", SqlDbType.Int).Value = CProductID
dr = .ExecuteReader()
If dr.HasRows Then
dr.Read()
StockQty = dr.GetInt32(2)
dr.Close()
If StockQty >= CAmount Then
sb.Remove(0, sb.Length)
sb.Append("UPDATE Stock")
sb.Append(" SET StockQtyIn = StockQtyIn - CAmount")
sb.Append(" WHERE (ProductID=@ProductID)")
sb.Append(" AND (StockLotNo=@StockLotNo)")
sqlSave = sb.ToString()
CAmount = 0
With com
.CommandText = sqlSave
.Parameters.Clear()
.Parameters.Add("@StockQtyIn", SqlDbType.Int).Value = CAmount
.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = CProductID
End With
ElseIf StockQty < CAmount Then
sb.Append("UPDATE Stock")
sb.Append(" SET StockQtyIn = 0")
sb.Append(" WHERE (ProductID=@ProductID)")
sb.Append(" AND (StockLotNo=@StockLotNo)")
sqlSave = sb.ToString()
CAmount = CAmount - StockQty
End If
With com
.CommandText = sqlSave
.Parameters.Clear()
.Parameters.Add("@StockQtyIn", SqlDbType.Int).Value = CAmount
.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = CProductID
End With
End If
End With
dr.Close()
End While
If StockQty >= CAmount Then
sqlStr = "UPDATE Stock Set StockQtyln =StockQty - CAmount where ProductID =@ProductID and StockLotNo=@StockLotNo"
CAmount = 0
ElseIf StockQty < CAmount Then
sqlStr = "update Stock Set StockQty = 0 where ProductID =@ProductID and StockLotNo=@StockLotNo"
CAmount = CAmount - StockQty
Store Procedure ก็จะคำนวณตัดให้เองตาม Lot ที่มาถึงก่อน(ดูจากวันที่) แล้วถ้าหากมันไม่พอตัดก็จะตัดไปบางส่วน
แล้วจะ RaisError ส่วนที่เหลือว่าขาดเท่าไหร่
Create Procedure proc_CheckOut_Stock( @ProductID int, @QTY int)
as
Begin
Set nocount on
Declare @StockLotNo varchar(5)
Declare @StockQtyIn int
Declare @QTYTemp int
Set @QTYTemp = @QTY
Declare c1 cursor for
Select StockLotNo, StockQtyIn from stock order by StockDate ASC, StockLotNo ASC
Open c1
Fetch c1 into @StockLotNo, @StockQtyIn
While (@@FETCH_STATUS=0 AND @QTYTemp > 0)
Begin
if (@StockQtyIn >= @QTYTemp)
begin
Update stock
Set StockQtyIn = StockQtyIn - @QTYTemp
Where StockLotNo = @StockLotNo
Set @QTYTemp = 0
end
else if (@StockQtyIn < @QTYTemp)
begin
Update stock
Set StockQtyIn = 0
Where StockLotNo = @StockLotNo
Set @QTYTemp = @QTYTemp - @StockQtyIn
end
Fetch c1 into @StockLotNo, @StockQtyIn
End
close c1
Deallocate c1
if (@QTYTemp > 0)
begin
-- Stock ไม่พอตัด (แต่ตัดไปบางส่วนแล้ว
Declare @msg varchar(100)
Set @msg = 'Outstanding = '+CONVERT(varchar,@QTYTemp)
RaisError ( @msg, 16,1)
end
Create Procedure proc_CheckOut_Stock( @ProductID int, @QTY int)
as
Begin
Set nocount on
Declare @StockLotNo varchar(5)
Declare @StockDate datetime
Declare @StockQtyIn int
Declare @QTYTemp int
Set @QTYTemp = @QTY
Declare c1 cursor for
Select StockLotNo, StockDate, StockQtyIn from stock
where ProductID = @ProductID
order by StockDate ASC, StockLotNo ASC
Open c1
Fetch c1 into @StockLotNo, @StockDate, @StockQtyIn
If (@@FETCH_STATUS != 0)
begin
RaisError ( 'Product not found', 16,1)
end
else
begin
While (@@FETCH_STATUS=0 AND @QTYTemp > 0)
Begin
if (@StockQtyIn >= @QTYTemp)
begin
Update stock
Set StockQtyIn = StockQtyIn - @QTYTemp
Where StockLotNo = @StockLotNo
and StockDate = @StockDate
and ProductID = @ProductID
Set @QTYTemp = 0
end
else if (@StockQtyIn < @QTYTemp)
begin
Update stock
Set StockQtyIn = 0
Where StockLotNo = @StockLotNo
and StockDate = @StockDate
and ProductID = @ProductID
Set @QTYTemp = @QTYTemp - @StockQtyIn
end
Fetch c1 into @StockLotNo, @StockDate, @StockQtyIn
End
close c1
Deallocate c1
if (@QTYTemp > 0)
begin
-- Stock ไม่พอตัด (แต่ตัดไปบางส่วนแล้ว
Declare @msg varchar(100)
Set @msg = 'Outstanding = '+CONVERT(varchar,@QTYTemp)
RaisError ( @msg, 16,1)
end
end
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
If MessageBox.Show("คุณต้องการบันทึกรายการขายสินค้า ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
Dim sqlAdd As String = ""
Dim cmdAdd As SqlCommand = New SqlCommand
Dim sqlSave As String
Dim Today As Date
sqlSave = sb.ToString()
Today = Date.Now
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
Try
Auto() 'เรียกเลขออโต้
sb.Remove(0, sb.Length)
sb.Append("INSERT INTO Sell (SellID,CustomerID,SellDate,NetTotal,NetDiscount,NetVat,PriceBeforeVat,PaymentTypeID)")
sb.Append(" VALUES (@SellID,@CustomerID,@SellDate,@NetTotal,@NetDiscount,@NetVat,@PriceBeforeVat,@PaymentTypeID)")
sqlSave = sb.ToString()
tr = Conn.BeginTransaction()
With com
.CommandText = sqlSave
.CommandType = CommandType.Text
.Connection = Conn
.Parameters.Clear()
.Transaction = tr
.Parameters.Add("@SellID", SqlDbType.VarChar).Value = lblSellID.Text
.Parameters.Add("@CustomerID", SqlDbType.Int).Value = txtCustomerID.Text
.Parameters.Add("@SellDate", SqlDbType.DateTime).Value = Today
.Parameters.Add("@NetTotal", SqlDbType.Float).Value = lblNet.Text
.Parameters.Add("@NetDiscount", SqlDbType.Float).Value = lblNetDC.Text
.Parameters.Add("@NetVat", SqlDbType.Float).Value = lblNetVAT.Text
.Parameters.Add("@PriceBeforeVat", SqlDbType.Float).Value = lblTotelnet.Text
If optcash.Checked = True Then
.Parameters.Add("@PaymentTypeID", SqlDbType.Int).Value = "0"
Else
.Parameters.Add("@PaymentTypeID", SqlDbType.Int).Value = "1"
End If
.ExecuteNonQuery()
End With
Dim i As Integer
'Dim SellID As Integer
Dim CProductID As String
Dim CAmount As Integer = 0
Dim CSellPrice As Double = 0.0
Dim CTotal As Double = 0.0
Dim Cdiscount As Double = 0.0
Dim Cno As String
Dim Cvat As Double = 0.0
For i = 0 To lsvProductList.Items.Count - 1
Cno = lsvProductList.Items(i).SubItems(0).Text
CProductID = lsvProductList.Items(i).SubItems(1).Text
CAmount = CInt(lsvProductList.Items(i).SubItems(4).Text)
CSellPrice = CDbl(lsvProductList.Items(i).SubItems(3).Text)
CTotal = CDbl(lsvProductList.Items(i).SubItems(7).Text)
Cdiscount = CDbl(lsvProductList.Items(i).SubItems(6).Text)
Cvat = CDbl(lsvProductList.Items(i).SubItems(5).Text)
sb.Remove(0, sb.Length)
sb.Append("INSERT INTO SellDetail(SellID,SellNo,ProductID,qty,SellPrice,Total,DiscountPrice,Vat)")
sb.Append(" VALUES (@SellID,@SellNo,@ProductID,@qty,@SellPrice,@Total,@DiscountPrice,@Vat)")
sqlSave = sb.ToString()
With com
.CommandText = sqlSave
.CommandType = CommandType.Text
.Parameters.Clear()
.Parameters.Add("@SellID", SqlDbType.VarChar).Value = lblSellID.Text
.Parameters.Add("@SellNo", SqlDbType.VarChar).Value = Cno
.Parameters.Add("@ProductID", SqlDbType.Int).Value = CProductID
.Parameters.Add("@qty", SqlDbType.Int).Value = CAmount
.Parameters.Add("@SellPrice", SqlDbType.Float).Value = CSellPrice
.Parameters.Add("@Total", SqlDbType.Float).Value = CTotal
.Parameters.Add("@DiscountPrice", SqlDbType.Float).Value = Cdiscount
.Parameters.Add("@Vat", SqlDbType.Float).Value = Cvat
.ExecuteNonQuery()
End With
Dim StockQty As Integer = 0
While (CAmount > 0)
sb.Remove(0, sb.Length)
sb.Append("SELECT top 1 StockLotNo,ProductID,")
sb.Append("StockQtyIn")
sb.Append(" FROM Stock")
sb.Append(" WHERE (StockQtyIn>0)")
sb.Append(" AND (ProductID=@ProductID)")
sb.Append(" ORDER BY StockLotNo ")
sqlSave = sb.ToString()
With com
.CommandText = sqlSave
.Parameters.Clear()
'.Parameters.Add("@StockLotNo", SqlDbType.VarChar).Value = txtStockID.Text
.Parameters.Add("@ProductID", SqlDbType.Int).Value = CProductID
dr = .ExecuteReader()
If dr.HasRows Then
dr.Read()
StockQty = dr.GetInt32(2)
dr.Close()
If StockQty >= CAmount Then
sb.Remove(0, sb.Length)
sb.Append("UPDATE Stock")
sb.Append(" SET StockQtyIn = StockQtyIn - CAmount")
sb.Append(" WHERE (ProductID=@ProductID)")
sb.Append(" AND (StockLotNo=@StockLotNo)")
sqlSave = sb.ToString()
CAmount = 0
With com
.CommandText = sqlSave
.Parameters.Clear()
.Parameters.Add("@StockQtyIn", SqlDbType.Int).Value = CAmount
.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = CProductID
End With
ElseIf StockQty < CAmount Then
sb.Append("UPDATE Stock")
sb.Append(" SET StockQtyIn = 0")
sb.Append(" WHERE (ProductID=@ProductID)")
sb.Append(" AND (StockLotNo=@StockLotNo)")
sqlSave = sb.ToString()
CAmount = CAmount - StockQty
End If
With com
.CommandText = sqlSave
.Parameters.Clear()
.Parameters.Add("@StockQtyIn", SqlDbType.Int).Value = CAmount
.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = CProductID
End With
End If
End With
dr.Close()
End While
Next
MessageBox.Show("บันทึกรายการขายสินค้า เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
ClearNet()
ClearProduct()
tr.Commit()
Catch ex As Exception
'tr.Rollback()
MessageBox.Show("ไม่สามารถบันทึกรายการขายสินค้าได้ เนื่องจาก " & ex.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Warning)
btnsave.Focus()
Finally
Conn.Close()
End Try
End If
End Sub
Dim StockQty As Integer
Dim lot As String = ""
While (CAmount > 0)
sb.Remove(0, sb.Length)
sb.Append("SELECT top 1 StockLotNo,ProductID,")
sb.Append("StockQtyIn")
sb.Append(" FROM Stock")
sb.Append(" WHERE (StockQtyIn>0)")
'sb.Append(" AND (StockLotNo=@StockLotNo)")
sb.Append(" AND (ProductID=@ProductID)")
sb.Append(" ORDER BY StockLotNo ASC")
sqlSave = sb.ToString()
With com
.CommandText = sqlSave
.Parameters.Clear()
.Parameters.Add("@ProductID", SqlDbType.Int).Value = CProductID
dr = .ExecuteReader()
If dr.HasRows Then
dr.Read()
lot = dr.GetString(0)
StockQty = dr.GetInt32(2)
dr.Close()
If StockQty >= CAmount Then
sb.Remove(0, sb.Length)
sb.Append("UPDATE Stock")
sb.Append(" SET StockQtyIn=@StockQtyIn")
sb.Append(" WHERE (ProductID=@ProductID)")
sb.Append(" AND (StockLotNo=@StockLotNo)")
sqlSave = sb.ToString()
'sqlStr = "UPDATE Stock Set StockQtyIn = where ProductID =@ProductID and StockLotNo=@StockLotNo"
With com
.CommandText = sqlSave
.CommandType = CommandType.Text
.Parameters.Clear()
.Parameters.Add("@ProductID", SqlDbType.Int).Value = CProductID
.Parameters.Add("@StockLotNo", SqlDbType.VarChar).Value = lot
.Parameters.Add("@StockQtyIn", SqlDbType.Int).Value = StockQty - CAmount
.ExecuteNonQuery()
End With
CAmount = 0
ElseIf StockQty < CAmount Then
sb.Remove(0, sb.Length)
sb.Append("UPDATE Stock")
sb.Append(" SET StockQtyIn=@StockQtyIn")
sb.Append(" WHERE (ProductID=@ProductID)")
sb.Append(" AND (StockLotNo=@StockLotNo)")
sqlSave = sb.ToString()
With com
.CommandText = sqlSave
.CommandType = CommandType.Text
.Parameters.Clear()
.Parameters.Add("@ProductID", SqlDbType.Int).Value = CProductID
.Parameters.Add("@StockLotNo", SqlDbType.VarChar).Value = lot
.Parameters.Add("@StockQtyIn", SqlDbType.Int).Value = CAmount - StockQty
.ExecuteNonQuery()
End With
CAmount = CAmount - StockQty
End If
End If
End With
End While
Load balance : Server 02
