vb6 mysql源码_mysql和VB6，存儲過程

最新推荐文章于 2024-08-22 00:33:22 发布

无名野人

最新推荐文章于 2024-08-22 00:33:22 发布

阅读量166

点赞数

文章标签： vb6 mysql源码

本文链接：https://blog.csdn.net/weixin_32900811/article/details/114008522

版权

你好，我有下面的下面的代碼...mysql和VB6，存儲過程

DELIMITER $$

USE `gateshms`$$

DROP PROCEDURE IF EXISTS `SP_Rank_by_Company`$$

CREATE DEFINER=`gates`@`%` PROCEDURE `SP_Rank_by_Company`(PETSA VARCHAR(50),ins VARCHAR(200))

BEGIN

IF ins = "DELETE" THEN

DELETE FROM tblrevrankreport WHERE xdate = PETSA;

ELSEIF ins = "INSERT" THEN

INSERT INTO tblrevrankreport (ndate, xDate, tid, transid, rmno, rmcat, rmcatdesc, rmtariff,

marketdesc, cindate, coutdate,

mulrate,

revamt,

othamt,

Company)

SELECT a.xDate, a.xDate, b.id, a.guestno, a.roomno, b.RoomCat, b.roomcat, b.Room_Tariff,

b.Market, b.CInDate, b.coutdate,

b.MultipleRate,

IF(c.ratecode=1,(a.amount-(a.AMT_VAT+a.AMT_SC+a.AMT_LT)),''),

IF(c.ratecode<>1,(a.amount-(a.AMT_VAT+a.AMT_SC+a.AMT_LT)),''),b.Company

FROM tbltransactionpostingdumy a

LEFT JOIN tblcustomerinfo b ON a.MaintransNo = b.transactionID

LEFT JOIN tblreftransaction c ON a.trcode = c.xcode

WHERE a.xDate = PETSA AND c.xcat = 'ROOM CHARGE';

ELSEIF ins = "UPDATE_NNN1" THEN

UPDATE tblrevrankreport tr INNER JOIN tblcustomerinfohistory tc

ON tr.xDate = tc.xDate

AND tr.`transid` = tc.`TransactionID`

AND tr.`xDate` = PETSA

AND tr.COutDate > tr.xdate

AND (tr.rmcat <> 'PQ' OR tr.rmcat <> 'Walkin')

SET tr.nyts = 1,

tr.NoOfAdult = tc.NoOfAdult,

tr.NoOfChild = tc.NoOfChild;

ELSEIF ins = "UPDATE_NNN2" THEN

UPDATE tblrevrankreport

SET

nyts = '0',

NoOfAdult = '0',

NoOfChild = '0'

WHERE xdate = PETSA

AND coutdate<=xdate;

ELSEIF ins = "UPDATE_ROOMTARIFFDESC" THEN

UPDATE tblrevrankreport,tblroomrate

SET tblrevrankreport.`rmtariffdesc` = tblroomrate.`rateDesc`

WHERE tblrevrankreport.rmtariff = tblroomrate.`room_type`

AND tblrevrankreport.xdate = PETSA;

ELSEIF ins = "UPDATE_ROOMDESC" THEN

UPDATE tblrevrankreport,tblroom

SET tblrevrankreport.`rmdesc` = tblroom.`roomdesc`

WHERE tblrevrankreport.`rmno` = tblroom.`Room_No`

AND tblrevrankreport.xdate = PETSA;

ELSEIF ins = "UPDATE_MARKETCODE" THEN

UPDATE tblrevrankreport,tblcmarket

SET tblrevrankreport.marketCode = tblcmarket.`xCode`

WHERE tblcmarket.`xDesc` = tblrevrankreport.`marketdesc`

AND tblrevrankreport.xdate = PETSA;

END IF;

END$$

DELIMITER ;

對於某些報告，因爲我不能在手術過程中添加一個進度條正在運行(這將引領應用程序中沒有響應)，我添加了一個名爲「插件」只是爲了節省每次查詢的整個過程則在其之間添加一定的UI操作變量，見下面的代碼：

Public Sub processRevRank(ByVal MyDate1 As String, ByVal MyDate2 As String)

'-- LATEST CODE AS OF JUNE 08, 2017

Dim RSC As ADODB.Recordset

Dim sDate As String

sDate = Format(MyDate1, "yyyy-mm-dd")

Dim xDays As Integer

If MyDate1 = "" And MyDate2 <> "" Then

MyDate2 = MyDate1

xDays = DateDiff("d", MyDate2, MyDate2)

ElseIf MyDate1 <> "" And MyDate2 = "" Then

MyDate1 = MyDate2

xDays = DateDiff("d", MyDate1, MyDate1)

ElseIf MyDate1 <> "" And MyDate2 <> "" Then

xDays = DateDiff("d", MyDate1, MyDate2)

Else

Unload frmLoadingReports

Exit Sub

End If

Dim xWidth As Integer

If xDays <> 0 Then

xWidth = 886/xDays

Else

xWidth = 886

End If

Do Until CDate(sDate) > CDate(MyDate2)

frmLoadingReports.lblTitle.Caption = "PROCESSING (Rank by Company : " & sDate & ")"

frmLoadingReports.lblLoadingTitleBack.Caption = "Deleting Records"

frmLoadingReports.lblLoadingTitleFront.Caption = "Deleting Records"