📄 frmdlcommisiongoods.frm
字号:
Private CurrencyID As Long '币种ID
Private DetailID As Long 'DetailID为单据业务ID
Private WithEvents mclsGrid As NewGrid '声明类模块
Attribute mclsGrid.VB_VarHelpID = -1
Private mlhls As rdoResultset '声明数据表类型
Private hldate As Date '声明系统期间日期
Private strWorkID As String '在筛选或栏目设置时需保留的ID串
Private ytextRow As Integer '记录当前FLEXGRID单元的行号
Private ytextCol As Integer '记录当前FLEXGRID单元的列号
Private Customernm As String '单位名称
Private Currencynm As String '币种名称
Private Const intViewID = 123 '视图ID号
Private Const intfixl = 40 '列偏移行数
Private inthl As Integer '币种保留小数点位数
Private dblRate As Double
Private Const msgcaption = "委托代销商品调拨结算" 'SHOWMSG窗体的标题名称
Private Const intFormHeight = 4018 '当前窗体的最小高度
Private Const intFormWidth = 7368 '当前窗体的最小宽度
Private mblnFormNoRezise As Boolean '窗体是否允许Resize
Private isinit As Boolean '窗体金额栏是否需要汇总
Private blnisDirect As Boolean '币种是否为间接计价方式
Private blnIsCancel As Boolean '按钮退出吗
Private bsdate As Base 'Base声明
'响应完全结算菜单
Private Sub chklist1_Click()
BalenceAll
End Sub
Private Sub BalenceAll() '完全结算
Dim intCount As Integer, k As Integer, i As Integer
Dim xx As Double
i = intfixl
k = intfixl
'本循环找出本次调拨所在的列
While (msgGrid.TextMatrix(0, i) <> "调拨金额")
i = i + 1
Wend
While (msgGrid.TextMatrix(0, k) <> "调拨数量")
k = k + 1
Wend
ytextRow = 1
'响应完全结算菜单
While ytextRow < msgGrid.Rows
'打√情况
msgGrid.TextMatrix(ytextRow, 1) = "√"
xx = getnumber(ytextRow, 4) - getnumber(ytextRow, i)
msgGrid.TextMatrix(ytextRow, i) = CStr(getnumber(ytextRow, i) + xx)
hLb(i).Caption = CStr(CDbl(IIf(Len(hLb(i).Caption) = 0, "0", hLb(i).Caption)) + xx)
'总结算金额及数量的更新
xx = getnumber(ytextRow, 5) - getnumber(ytextRow, k)
msgGrid.TextMatrix(ytextRow, k) = CStr(getnumber(ytextRow, 5))
ytextRow = ytextRow + 1
Wend
End Sub
'响应完全取消菜单
Private Sub chklist2_Click()
AbolishAll
End Sub
Private Sub AbolishAll()
'完全取消
Dim k As Integer, i As Integer
'响应完全取消菜单
i = intfixl
k = intfixl
'本循环找出本次调拨所在的列
While (msgGrid.TextMatrix(0, i) <> "调拨金额")
i = i + 1
Wend
While (msgGrid.TextMatrix(0, k) <> "调拨数量")
k = k + 1
Wend
ytextRow = 1
While ytextRow < msgGrid.Rows
' If getnumber(ytextRow, 2) = 0 Then
msgGrid.TextMatrix(ytextRow, 1) = "" '取消打√(核销)情况
' Else
' msgGrid.TextMatrix(ytextRow, 1) = "√"
' End If
hLb(i).Caption = CStr(CDbl(hLb(i).Caption) - getnumber(ytextRow, i))
msgGrid.TextMatrix(ytextRow, k) = ""
' msgGrid.TextMatrix(ytextRow, 3)
msgGrid.TextMatrix(ytextRow, i) = ""
' msgGrid.TextMatrix(ytextRow, 2)
ytextRow = ytextRow + 1
Wend
End Sub
Private Sub Cmdall_Click(Index As Integer)
'响应窗体按钮动作
Select Case Index
Case 7 '确定存盘
blnIsCancel = True
If msgGrid.Rows > 1 Then
SaveData (1)
Else
ExitGrid
End If
Case 1 '取消
blnIsCancel = True
ExitGrid
Case 2 '筛选
FilterData
Case 3 '栏目设置
setColumn
Case 4
'BillPublic.ShowBill getnumber(msgGrid.Row, 6), DetailID
Case 5 '全部选择
If msgGrid.Rows > 1 Then
BalenceAll
End If
Case 6
If msgGrid.Rows > 1 Then
AbolishAll '全部取消
End If
End Select
End Sub
'不存盘返回
Private Sub ExitGrid()
Unload Me
End Sub
'从对应视图取SQL语句并打开、初始化之
Private Sub GetList()
Dim sqlselect As String, strSql As String, strSelect As String, str As String
Dim strWhere As String, pbsql As String, strFrom As String
Dim recRecordset As rdoResultset, intCount As Integer
Dim querrysql As QueryDef
' On Error GoTo Err
mclsGrid.ListSet.ViewId = intViewID
'//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'// 取本次结算金额、本次结算数量算法（连接对照表的方法）： //
'// '首先：因为本次结算金额、本次结算数量是针对本次单据业务ID的；所以，可通过ItemActivity表的lngItemActivityID字段 //
'// '和ItemActivityDetail表的lngItemActivityID字段将此二表连接起来；再通过ItemActivityDetail表的lngItemActivityDetailID字段 //
'// '和LendToSale表的lngSaleActivityDetailID字段将此二表连接起来;这样一来表ItemActivity、ItemActivityDetail和LendToSale就被有机连接起来了。 //
'// '再用ItemActivity中的lngItemActivityID作为取本次结算金额、本次结算数量记录的条件。把单据中取过来的单据业务ID作为参数传给lngItemActivityID //
'// '就得到了所有取本次结算金额、本次结算数量的记录（取LendToSale表的全部字段）。 //
'// '然后：将上面生成的查询作为一个查询表（取别名LendToSale）再同表ItemActivityDetail连接回去就得到了我们需要的结果。连接方法是 //
'// '通过ItemActivityDetail表的lngItemActivityDetailID字段和LendToSale表的lngLendActivityDetailID字段将二表连接起来。 //
'// '结束 //
'//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
With msgGrid
.Redraw = False
.FixedCols = 0
End With
strWhere = ""
str = ""
strSql = ""
strFrom = ""
pbsql = ""
With mclsGrid.ListSet
strFrom = .FromOfSql
pbsql = .SelectOfSql
strWhere = .WhereOfSql
End With
' If DetailID < 1 Then
' Cmdall(4).Enabled = False
' End If
'str = " Where ((([ItemActivityDetail ].[dblCurrAmount]+ " _
& " [ItemActivityDetail].[dblCurrTaxAmount]-[ItemActivityDetail].[dblCurrSettlementAmount])<>0) AND " _
& " ((ItemActivity.lngCustomerID)=[customerID]) AND ((ItemActivity.lngCurrencyID)=[currencyID]) AND " _
& " ((ItemActivity.lngActivityTypeID) In (13,24,26)))"
str = " Where (((([ItemActivityDetail ].[dblCurrAmount]+ " _
& " [ItemActivityDetail].[dblCurrTaxAmount]-[ItemActivityDetail].[dblCurrSettlementAmount]+ " _
& " IIf(IsNull([LendToSale].[dblCurrAmount]),0,[LendToSale].[dblCurrAmount])))<>0) AND " _
& " ((ItemActivity.lngCustomerID)=[customerID]) AND ((ItemActivity.lngCurrencyID)=[currencyID]) AND " _
& " ((ItemActivity.lngActivityTypeID) In (13,24,26)))"
If Len(Trim(strWhere)) >= 1 Then str = str & "AND" & " ( " & strWhere & ")"
If Len(Trim(strWorkID)) > 4 Then
str = str & " OR ( ItemActivityDetail.lngActivityDetailID IN " & strWorkID & ")"
End If
strSql = strFrom & str
sqlselect = " Select ItemActivityDetail.lngActivityDetailID As 业务ID, iif(LendToSale.dblCurrAmount<>0,'√','') As 选择, LendToSale.dblCurrAmount AS 原调拨金额," _
& " Trim(Str(Int(Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity])/[ItemUnit].[dblFactor])))+iif([ItemUnit].[dblFactor]=1,' ','.')+String(Len(Trim(Str([ItemUnit].[dblFactor])))" _
& "-Len(Trim(Str(Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity]) Mod [ItemUnit].[dblFactor]))),'0')+iif([ItemUnit].[dblFactor]=1,' ',Trim(Str(Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity]) Mod [ItemUnit].[dblFactor]))) AS 原调拨数量, " _
& " ([ItemActivityDetail ].[dblCurrAmount]+[ItemActivityDetail].[dblCurrTaxAmount]-[ItemActivityDetail].[dblCurrSettlementAmount]+IIf(IsNull([LendToSale].[dblCurrAmount]),0,[LendToSale].[dblCurrAmount])) AS 原可调金额," _
& " (Trim(Str(Int(([ItemActivityDetail].[dblQuantity]-[ItemActivityDetail].[dblSettlementQuantity]+Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity]))/[ItemUnit].[dblFactor])))+iif([ItemUnit].[dblFactor]=1,' ','.')+String(Len(Trim(Str([ItemUnit].[dblFactor])))" _
& "-Len(Trim(Str(([ItemActivityDetail].[dblQuantity]-[ItemActivityDetail].[dblSettlementQuantity]+Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity])) Mod [ItemUnit].[dblFactor]))),'0')+iif([ItemUnit].[dblFactor]=1,' ',Trim(Str(([ItemActivityDetail].[dblQuantity]-"
str = "[ItemActivityDetail].[dblSettlementQuantity]+Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity])) Mod [ItemUnit].[dblFactor])))) AS 原可调数量,ItemActivity.lngActivityTypeID AS 类型ID, " _
& " ItemActivityDetail.dblQuantity As 原总数量,(ItemActivityDetail.dblCurrAmount+ItemActivityDetail.dblCurrTaxAmount) As 原含税金额,ItemUnit.dblFactor As 换算因子,ItemActivityDetail.lngItemID As 10,ItemActivityDetail.lngUnitID As 11,dblCurrPrice*dblFactor As 12," _
& " ItemActivityDetail.lngTaxID As 13,ItemActivityDetail.lngJobID As 14,ItemActivityDetail.lngCustomID0,ItemActivityDetail.lngCustomID1,ItemActivityDetail.lngCustomID2,ItemActivityDetail.lngCustomID3,ItemActivityDetail.lngCustomID4,ItemActivityDetail.lngCustomID5,'' As 21,'' As 22,ItemActivityDetail.intValidDay As 23," _
& " (strItemCode & ' ' & strItemName & ' ' & strItemStyle) As 24,strUnitName As 25,strTaxName As 26,Item.lngPositionID As 27,strPositionName As 28,strJobName As 29,Custom0.strCustomName As 30,Custom1.strCustomName As 31,Custom2.strCustomName As 32,Custom3.strCustomName As 33,Custom4.strCustomName As 34," _
& " Custom5.strCustomName As 35,[ItemActivityDetail].[strProduceNum] As 36,ItemActivityDetail.strProduceDate As 37, ItemActivityDetail.strValidDate As 38,'' As 行号," & pbsql
'备份"_' sqlselect = " Select ItemActivityDetail.lngActivityDetailID As 业务ID, iif(LendToSale.dblCurrAmount<>0,'√','') As √, LendToSale.dblCurrAmount AS 原调拨金额," _
& " Trim(Str(Int(Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity])/[ItemUnit].[dblFactor])))+'.'+String(Len(Trim(Str([ItemUnit].[dblFactor])))" _
& "-Len(Trim(Str(Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity]) Mod [ItemUnit].[dblFactor]))),'0')+Trim(Str(Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity]) Mod [ItemUnit].[dblFactor])) AS 原调拨数量, " _
& " ([ItemActivityDetail ].[dblCurrAmount]+[ItemActivityDetail].[dblCurrTaxAmount]-[ItemActivityDetail].[dblCurrSettlementAmount]+IIf(IsNull([LendToSale].[dblCurrAmount]),0,[LendToSale].[dblCurrAmount])) AS 原可调金额," _
& " (Trim(Str(Int(([ItemActivityDetail].[dblQuantity]-[ItemActivityDetail].[dblSettlementQuantity]+Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity]))/[ItemUnit].[dblFactor])))+'.'+String(Len(Trim(Str([ItemUnit].[dblFactor])))" _
& "-Len(Trim(Str(([ItemActivityDetail].[dblQuantity]-[ItemActivityDetail].[dblSettlementQuantity]+Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity])) Mod [ItemUnit].[dblFactor]))),'0')+Trim(Str(([ItemActivityDetail].[dblQuantity]-" _
& "[ItemActivityDetail].[dblSettlementQuantity]+Iif(IsNull([LendToSale].[dblQuantity]),0,[LendToSale].[dblQuantity])) Mod [ItemUnit].[dblFactor]))) AS 原可调数量,ItemActivity.lngActivityTypeID AS 类型ID, " _
& " ItemActivityDetail.dblQuantity As 原总数量,(ItemActivityDetail.dblCurrAmount+ItemActivityDetail.dblCurrTaxAmount) As 含税金额,ItemUnit.dblFactor As 换算因子,ItemActivityDetail.lngItemID As 10,ItemActivityDetail.lngUnitID As 11,dblCurrPrice*dblFactor As 12," _
& " ItemActivityDetail.lngTaxID As 13,ItemActivityDetail.lngJobID As 14,ItemActivityDetail.lngCustomID0,ItemActivityDetail.lngCustomID1,ItemActivityDetail.lngCustomID2,ItemActivityDetail.lngCustomID3,ItemActivityDetail.lngCustomID4,ItemActivityDetail.lngCustomID5,'' As 21,'' As 22,ItemActivityDetail.intValidDay As 23," _
& " (strItemCode & ' ' & strItemName & ' ' & strItemStyle) As 24,strUnitName As 25,strTaxName As 26,Item.lngPositionID As 27,strPositionName As 28,strJobName As 29,Custom0.strCustomName As 30,Custom1.strCustomName As 31,Custom2.strCustomName As 32,Custom3.strCustomName As 33,Custom4.strCustomName As 34," _
& " Custom5.strCustomName As 35,[ItemActivityDetail].[strProduceNum] As 36,ItemActivityDetail.strProduceDate As 37, ItemActivityDetail.strValidDate As 38," & pbsql
sqlselect = sqlselect & str
strSql = sqlselect & strSql
'& " Order By √ Desc"
Set querrysql = gclsBase.BaseDB.CreateQueryDef("", strSql)
querrysql.Parameters("DetailID") = DetailID
querrysql.Parameters("CustomerID") = CustomerID
querrysql.Parameters("CurrencyID") = CurrencyID
Set recRecordset = querrysql.OpenRecordset(dbOpenSnapshot)
'列表是否为空
If recRecordset.RowCount = 0 Then
msgGrid.HighLight = flexHighlightNever
' ShowMsg me.hWnd, "该委托调拨结算单无对应委托调拨资料！", MB_SYSTEMMODAL + MB_ICONEXCLAMATION, msgcaption
isinit = False
Else
recRecordset.MoveLast
End If
Set Datadl.Recordset = recRecordset
msgGrid.HighLight = flexHighlightAlways
If isinit = True Then
Set mlhls = recRecordset
inittext
isinit = False
End If
recRecordset.Close
With msgGrid
.SelectionMode = flexSelectionFree
.FocusRect = flexFocusNone
.ColWidth(0) = 0
.ColWidth(1) = 488
intCount = 2
While intCount <= 39
.ColWidth(intCount) = 0
intCount = intCount + 1
Wend
.Redraw = True
End With
'Exit Sub
'Err:
' ShowMsg Me.hWnd, "商品调拨初始化失败，请检查数据库是否被修改！ ", MB_SYSTEMMODAL + MB_ICONEXCLAMATION, "委托代销调拨"
End Sub
'初始化各text框中数据
Private Sub inittext()
Dim intCount As Integer, dx As Double, dz As Double
Dim wjje As Double, yjje As Double, l As Integer, n As Integer
Dim recRecordset As rdoResultset
dx = 0
dz = 0
Set Datadl.Recordset = mlhls
mlhls.MoveFirst
For intCount = 1 To mlhls.RowCount
wjje = C2Dbl(mlhls.rdoColumns("可调金额"))
yjje = C2Dbl(IIf(IsNull(mlhls.rdoColumns("调拨金额")), 0, mlhls.rdoColumns("调拨金额")))
dx = dx + wjje
dz = dz + yjje
mlhls.MoveNext
Next intCount
l = intfixl
n = l
While (msgGrid.TextMatrix(0, l) <> "可调金额")
l = l + 1
Wend
While (msgGrid.TextMatrix(0, n) <> "调拨金额")
n = n + 1
Wend
hLb(l).Caption = CStr(dx)
hLb(n).Caption = CStr(dz)
hLb(l).Refresh
hLb(n).Refresh
End Sub
'结算内容筛选
Private Sub FilterData()
On Error GoTo Err
Dim i As Integer
'If msgGrid.Rows > 1 Then
i = 1
strWorkID = ""
Do While i < msgGrid.Rows
If msgGrid.TextMatrix(i, 1) = "√" Then
If Len(strWorkID) > 0 Then
strWorkID = strWorkID & "," & CStr(getnumber(i, 0))
Else
strWorkID = CStr(getnumber(i, 0))
End If
End If
i = i + 1
Loop
strWorkID = "( " & strWorkID & " )"
If msgGrid.Rows > 1 Then
If ShowMsg(Me.hwnd, "筛选操作后，你刚刚做的结算将被取消，需要先存盘吗", MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON2 + MB_SYSTEMMODAL, "委托代销调拨") = IDYES Then
SaveData (0)
End If
End If
If mclsGrid.ListSet.ListID < 1 Then
mclsGrid.ListSet.SaveList
End If
Filter.ShowFilter mclsGrid.ListSet.ListID, 1
mclsGrid.ColOfs = intfixl
If msgGrid.Rows > 1 Then
mclsGrid.ListSet.SaveList
End If
isinit = True
GetList
mclsGrid.SetupStyle
If msgGrid.Rows > 1 Then
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -