金蝶库存去除开票未审核

'定义插件对象接口. 必须具有的声明, 以此来获得事件
Private WithEvents m_BillTransfe   As K3BillTransfer.Bill
 
Public Sub Show(ByVal oBill As Object)
 
    '接口实现
    '注意: 此方法必须存在, 请勿修改
    Set m_BillTransfe = oBill
 
End Sub

Private Sub Class_Terminate()
 
    '释放接口对象
    '注意: 此方法必须存在, 请勿修改
    Set m_BillTransfe = Nothing

End Sub

Private Sub m_BillTransfe_BillInitialize()
    Dim rs As ADODB.Recordset
    Dim MOBJ As Object
    'Dim conn As ADODB.Connection
    Dim sql1, sql2 As String
    Dim FitemID As Integer     ''物料内码
    Dim faux As Integer '物料属性
    Dim stock As Integer '仓库
    Dim Row As Integer
    Dim FitemNumber As String
    Dim FUserPrice As Double      ''用户自定义价格(价格库中查找的价格)
    Dim ImmediateQTY, FAvailableStockQTY, QFAvailableStockQTY As Double ''定义即时库存,已分配库存
   
    ImmediateQTY = 0
    FAvailableStockQTY = 0
    QFAvailableStockQTY = 0

End Sub

Private Sub m_BillTransfe_LeveCell(ByVal col As Long, ByVal Row As Long, ByVal NewCol As Long, ByVal NewRow As Long, Cancel As Boolean)
Static Mcol, code1, code2, code3, code4, code5 As Double

If Mcol = 0 Then
Do
Mcol = Mcol + 1
If m_BillTransfe.GetGridText(0, Mcol) = "产品代码" Then
code1 = Mcol
End If
If m_BillTransfe.GetGridText(0, Mcol) = "辅助属性" Then
code2 = Mcol
End If
If m_BillTransfe.GetGridText(0, Mcol) = "可开票数量" Then
code3 = Mcol
End If
If m_BillTransfe.GetGridText(0, Mcol) = "发货仓库" Then
code4 = Mcol
End If
If m_BillTransfe.GetGridText(0, Mcol) = "未审核数量" Then
code5 = Mcol
End If
Loop While m_BillTransfe.GetGridText(0, Mcol) <> "" '判断列名

End If
If m_BillTransfe.GetGridText(Row, code1) <> "" Then

'Set conn = New ADODB.Connection
'conn.CursorLocation = adUseClient
'conn.Open m_BillTransfe.Cnnstring
'Set rs = New ADODB.Recordset

Set MOBJ = CreateObject("K3Connection.AppConnection")

sql1 = "select fitemid from t_icitemcore where fnumber='" & CStr(m_BillTransfe.GetGridText(Row, code1)) & "'"

'rs.Open sql1, conn, 0, 1
Set rs = MOBJ.Execute(sql1)

If Not rs.EOF Then

FitemID = rs.Fields(0).Value

Else

  Exit Sub

End If

rs.Close

 

'去即时库存表取即时库存

If (m_BillTransfe.GetGridText(Row, code2) = "" And m_BillTransfe.GetGridText(Row, code4) = "") Then '没有物料属性,没有仓库
sql1 = "select fitemid,sum(FQty) as fqty from ICInventory where fitemid=" & FitemID & " group by fitemid"
Else
If (m_BillTransfe.GetGridText(Row, code2) <> "" And m_BillTransfe.GetGridText(Row, code4) <> "") Then '有物料属性，有仓库
sql1 = "select fitemid from t_AuxItem where fname='" & CStr(m_BillTransfe.GetGridText(Row, code2)) & "'" '取属性内码

'rs.Open sql1, conn, 0, 1
Set rs = MOBJ.Execute(sql1)

If Not rs.EOF Then

faux = rs.Fields(0).Value

End If

rs.Close
sql1 = "select fitemid from t_stock where fname='" & CStr(m_BillTransfe.GetGridText(Row, code4)) & "'"   '取仓库内码

'rs.Open sql1, conn, 0, 1
Set rs = MOBJ.Execute(sql1)

If Not rs.EOF Then

stock = rs.Fields(0).Value

End If

rs.Close
If (faux = "" Or stock = "") Then
Exit Sub
Else
sql1 = "select fitemid,sum(FQty) as fqty from ICInventory where fitemid=" & FitemID & "and fauxpropid='" & faux & "'" & "and fstockid='" & stock & "'" & " group by fitemid"
End If
Else
If m_BillTransfe.GetGridText(Row, code4) = "" Then '没有仓库
sql1 = "select fitemid from t_AuxItem where fname='" & CStr(m_BillTransfe.GetGridText(Row, code2)) & "'" '取属性内码

'rs.Open sql1, conn, 0, 1
Set rs = MOBJ.Execute(sql1)

If Not rs.EOF Then

faux = rs.Fields(0).Value

End If

rs.Close
If faux = "" Then
Exit Sub
Else
sql1 = "select fitemid,sum(FQty) as fqty from ICInventory where fitemid=" & FitemID & "and fauxpropid='" & faux & "'" & " group by fitemid"
End If
Else '没有物料属性

sql1 = "select fitemid from t_stock where fname='" & CStr(m_BillTransfe.GetGridText(Row, code4)) & "'"   '取仓库内码

'rs.Open sql1, conn, 0, 1
Set rs = MOBJ.Execute(sql1)

If Not rs.EOF Then

stock = rs.Fields(0).Value

End If

rs.Close
If stock = "" Then
Exit Sub
Else
sql1 = "select fitemid,sum(FQty) as fqty from ICInventory where fitemid=" & FitemID & "and fstockid='" & stock & "'" & " group by fitemid"
End If
End If
End If
End If

'rs.Open sql1, conn, 0, 1
Set rs = MOBJ.Execute(sql1)

If Not rs.EOF() Then

ImmediateQTY = rs.Fields(1).Value

Else

ImmediateQTY = 0

End If

rs.Close

 

'去销售出库未审核库存
If (faux = "" And stock = "") Then
sql2 = "select fitemid,sum(FQty) as fqty from icstockbillentry  where fitemid='" & FitemID & "'" & "and finterid in (select finterid from vwicbill_8  where fcheckflag='' and fcancellation='' and fqty>0)" & " group by fitemid"
Else
 If (faux <> "" And stock <> "") Then
 sql2 = "select fitemid,sum(FQty) as fqty from icstockbillentry  where fauxpropid='" & faux & "'" & "and fdcstockid='" & stock & "'" & "and fitemid='" & FitemID & "'" & "and finterid in (select finterid from vwicbill_8  where fcheckflag='' and fcancellation='' and fqty>0)" & " group by fitemid"
 Else
  If faux = "" Then
  sql2 = "select fitemid,sum(FQty) as fqty from icstockbillentry  where fdcstockid='" & stock & "'" & "and fitemid='" & FitemID & "'" & "and finterid in (select finterid from vwicbill_8  where fcheckflag='' and fcancellation='' and fqty>0)" & " group by fitemid"
  Else
  sql2 = "select fitemid,sum(FQty) as fqty from icstockbillentry  where fauxpropid='" & faux & "'" & "and fitemid='" & FitemID & "'" & "and finterid in (select finterid from vwicbill_8  where fcheckflag='' and fcancellation='' and fqty>0)" & " group by fitemid"
  End If
 End If
End If

'rs.Open sql2, conn, 0, 1
Set rs = MOBJ.Execute(sql2)

If Not rs.EOF() Then

FAvailableStockQTY = rs.Fields(1).Value

Else

FAvailableStockQTY = 0

End If

rs.Close

'去其它出库未审核库存
If (faux = "" And stock = "") Then
sql2 = "select fitemid,sum(FQty) as fqty from icstockbillentry  where fitemid='" & FitemID & "'" & "and finterid in (select finterid from vwicbill_16  where fcheckflag='' and fcancellation='' and fqty>0)" & " group by fitemid"
Else
 If (faux <> "" And stock <> "") Then
 sql2 = "select fitemid,sum(FQty) as fqty from icstockbillentry  where fauxpropid='" & faux & "'" & "and fdcstockid='" & stock & "'" & "and fitemid='" & FitemID & "'" & "and finterid in (select finterid from vwicbill_16  where fcheckflag='' and fcancellation='' and fqty>0)" & " group by fitemid"
 Else
  If faux = "" Then
  sql2 = "select fitemid,sum(FQty) as fqty from icstockbillentry  where fdcstockid='" & stock & "'" & "and fitemid='" & FitemID & "'" & "and finterid in (select finterid from vwicbill_16  where fcheckflag='' and fcancellation='' and fqty>0)" & " group by fitemid"
  Else
  sql2 = "select fitemid,sum(FQty) as fqty from icstockbillentry  where fauxpropid='" & faux & "'" & "and fitemid='" & FitemID & "'" & "and finterid in (select finterid from vwicbill_16  where fcheckflag='' and fcancellation='' and fqty>0)" & " group by fitemid"
  End If
 End If
End If

'rs.Open sql2, conn, 0, 1
Set rs = MOBJ.Execute(sql2)

If Not rs.EOF() Then

QFAvailableStockQTY = rs.Fields(1).Value

Else

QFAvailableStockQTY = 0

End If

rs.Close
 

'将可用库存显示到表体中

m_BillTransfe.SetGridText Row, code3, ImmediateQTY - FAvailableStockQTY - QFAvailableStockQTY
m_BillTransfe.SetGridText Row, code5, FAvailableStockQTY + QFAvailableStockQTY

Set rs = Nothing

'conn.Close
'Set conn = Nothing

End If
End Sub