手动执行预设补贴功能-原创手记-慕课网

SQL中有两个表，pridectsubsidy，employee
实现功能为当手动执行预设补贴时，将补贴表里的补贴值存储到人员表对应得人员上。
其中有两个Tag 1，自动清零、2，补贴月份
1、如果客户端传递自动补贴清零参数到存储数据时，先进行清零操作否则不进行
2、补贴月份是要更新到人员表中的数据，用来跟predate进行对比判断。

核心判断代码:
if (cInt(rs_E("lastsubsidyid")) <> cInt(dateM)) and (tagClear = 1) then '判断补贴月份是否相同，且是否补贴清零.

其次，无论判断是否满足条件，最终都应该对人员表进行操作。

代码如下:

其中 RechargeRecordClear，RechargeRecordSave 为封装内部方法没有列出。
2016-07-02 15:52:27
dim sql_p,rs_p,dateP
dim sql_E,rs_E

dim sql_U,sequence,mSequence,SubsidyBalance

dateP = now

dim dateM

dateM = month(now)

'response.write(dateM) 

dim RdWorkNo,Cardid,mMacId,CardDiscount

dim MacDiscount,RealExpend,AccountCashRealExpend,AccountSubsidyRealExpend

dim AccountCashBalance,AccountSubsidyBalance,sex,DeptName

dim PositionName,CardTypeName,UserName

dim sql_Del

sql_p = "select * from predictsubsidy where predate <= '"& dateP &"'"

set rs_p = server.createobject("adodb.recordset")

rs_p.open sql_p,conn,1,3

'response.write(rs_p.recordcount)

do while not rs_p.eof '遍历predictSubsidy表

    sql_E = "select * from employee where workno = '"& rs_p("workno") &"' and f_delflag = 0 " '获取满足条件的人员数据'

    set rs_E = server.createobject("adodb.recordset")

    rs_E.open sql_E,conn,1,1

    if not rs_E.eof then 

            if (cInt(rs_E("lastsubsidyid")) <> cInt(dateM)) and (tagClear = 1) then '判断补贴月份是否相同，且是否补贴清零.

                sequence = clng(rs_E("LastRecSequence"))
                mSequence = sequence + 1
                SubsidyBalance = 0
                'SubsidyBalance = SubsidyBalance + rs_p("subsidy")

                RdWorkNo = rs_E("workno")
                Cardid =  rs_E("Cardid")
                mMacId = 0
                CardDiscount = 100
                MacDiscount = 100
                RealExpend = rs_E("subsidy")
                AccountCashRealExpend = 0
                AccountSubsidyRealExpend = rs_E("subsidy")
                AccountCashBalance = csng(rs_E("yuer"))
                AccountSubsidyBalance = SubsidyBalance

                Sex = rs_E("sex")

                DeptName = rs_E("DeptName")
                PositionName = rs_E("PositionName")
                CardTypeName = rs_E("CardTypeName")
                UserName = rs_E("Name")

                call RechargeRecordClear

                sql_U = "Update Employee Set LastSubsidyID =" & month(rs_p("predate")) & ", subsidy = 0 ,  LastRecSequence =" & mSequence &  " ,yuerlastupdate ='"& now &"'  Where F_Delflag = 0 And WorkNo='" & rs_E("workno") & "' "

                conn.execute(sql_U)

                rs_E.close   '将集合关闭方便二次访问
                rs_E.open sql_E,conn,1,1

            end if

            sequence = clng(rs_E("LastRecSequence"))
            mSequence = sequence + 1
            SubsidyBalance = csng(rs_E("subsidy"))
            SubsidyBalance = SubsidyBalance + rs_p("subsidy")

            RdWorkNo = rs_E("workno")
            Cardid =  rs_E("Cardid")
            mMacId = 0
            CardDiscount = 100
            MacDiscount = 100
            RealExpend = rs_p("subsidy")
            AccountCashRealExpend = 0
            AccountSubsidyRealExpend = rs_p("subsidy")
            AccountCashBalance = csng(rs_E("yuer"))
            AccountSubsidyBalance = SubsidyBalance

            Sex = rs_E("sex")

            DeptName = rs_E("DeptName")
            PositionName = rs_E("PositionName")
            CardTypeName = rs_E("CardTypeName")
            UserName = rs_E("Name")

            sql_U = "Update Employee Set LastSubsidyID =" & month(rs_p("predate")) & ", subsidy =" & SubsidyBalance & ",  LastRecSequence =" & mSequence &  " ,yuerlastupdate ='"& now &"'  Where F_Delflag = 0 And WorkNo='" & rs_E("workno") & "' "

            conn.execute(sql_U)

            call RechargeRecordSave

    end if 

    rs_E.close

    dim DelId

    DelId = rs_p("id")

    rs_p.movenext

    sql_Del = "delete from predictsubsidy where id = "& DelId  
    conn.execute(sql_Del)

loop 

rs_p.close

%>