createsalaryreport.asp

财务管理系统 报账等基本功能实现 有完整的实现

<%
'创建指定月份的工资表
Public Sub CreateSalaryReport(TmpiMonth)
  Dim cmd,cmd1,sqlInsert
  Set cmd = Server.CreateObject("ADODB.Command")
  Set cmd1 = Server.CreateObject("ADODB.Command")
  '设置cmd的ActiveConnection属性，指定与其关联的数据库连接
  Set cmd.ActiveConnection = conn

  Dim rs1,rsItem,sqlItem
  Dim BSalary   '基本工资
  Dim isOper,sFormula
  '税前工资总额、应纳税工资额、纳税后工资额和实发工资
  Dim BTotal        '税前工资总额
  Dim TTotal        '应纳税工资额
  Dim ATotal        '纳税后工资额
  Dim RealSalary    '实发工资  
  Dim BTax          '工资纳税基数
  Dim Total         '基本工资
  Set rs1 = Server.CreateObject("ADODB.RECORDSET")
  Set rsItem = Server.CreateObject("ADODB.RECORDSET")
  '读取纳税基数，为表TaxRate中ItemId=1记录
  sql = "Select * From TaxRate Where Id=1"
  rs1.Open sql,conn,1,1
  If Not rs1.EOF Then
    BTax = rs1("LowerLimit")
  End If
  rs1.Close()
  '提取在职员工信息，表Employees中所有员工
  SqlStmt = "SELECT EmpId,Salary FROM Employees Order By EmpId"
  rs1.Open SqlStmt,conn,1,1
  Do While Not rs1.EOF
    '执行循环计算前将变量置为0
    BTASum = 0
    BTDSum = 0
    ATASum = 0
    ATDSum = 0
    '1)提取员工基本工资(工资项目中系统数据值)，插入数据库
    EmpId = rs1("EmpId")  '员工编号
    iMonth = TmpiMonth    '工资月份
    Total = rs1("Salary") '基本工资
    BSalary = Total
    '取得基本工资项对应的编号，即ItemId
    sqlItem = "SELECT ItemId FROM SalaryItem WHERE ItemType=0"
    rsItem.Open sqlItem,Conn,1,1
    If Not rsItem.EOF Then
      ItemId = rsItem("ItemId")  '工资项目编号
    End If
    '插入统计表
    sqlInsert = "INSERT INTO SalaryStatistics Values(" & EmpId _
       & "," & iMonth + "," & ItemId & "," & Total & ")"
    Set cmd.ActiveConnection = conn
    '设置要执行的命令文本
    cmd.CommandText = sqlInsert
    '执行命令
    cmd.Execute
    rsItem.Close()
    
    '2)提取工资项目信息中工资项目类型为固定值和计算值的、且显示计算的项目
    sqlItem = "SELECT ItemId,ItemSum,Operation FROM SalaryItem " _
       + "WHERE ItemType=1 AND isVisible=1"
    rsItem.Open sqlItem,conn,1,1
    Do While Not rsItem.EOF
      ItemId = rsItem("ItemId")
      Total = rsItem("ItemSum")
      isOper = rsItem("Operation")
      '插入统计表
      sqlInsert = "INSERT INTO SalaryStatistics Values(" + Trim(EmpId) _
         + "," + Trim(iMonth) + "," + Trim(ItemId) + "," + Trim(Total) + ")"
      Set cmd.ActiveConnection = conn
      '设置要执行的命令文本
      cmd.CommandText = sqlInsert
      '执行命令
      cmd.Execute
      '计算各公共变量数值
      If isOper = 0 Then       '税前加
        BTASum = Clng(BTASum) + CLng(Total)
      ElseIf isOper = 1 Then  '税前减
        BTDSum = BTDSum + Total
      ElseIf isOper = 2 Then   '税后加
        ATASum = ATASum + Total
      ElseIf isOper = 3 Then   '税后减
        ATDSum = ATDSum + Total
      End If
      rsItem.MoveNext
    Loop
    rsItem.Close()
    '3)提取工资项目信息中工资项目类型为固定值和计算值的、且显示计算的项目
    sqlItem = "SELECT ItemId,ItemSum,Operation,Formula FROM SalaryItem " _
       + "WHERE ItemType=2 AND isVisible=1"
    rsItem.Open sqlItem,Conn,1,3
    Set cmd1.ActiveConnection = Conn
    cmd1.CommandText = "GetItemValue"
    cmd1.CommandType = adCmdStoredProc
    Do While Not rsItem.EOF
      ItemId = rsItem.Fields(0)
      isOper = rsItem.Fields(2)
      sFormula = Trim(rsItem.Fields(3))
      With cmd1
        .Parameters.Refresh '创建存储过程参数
        .Parameters("@@Fm") = sFormula   '为参数赋值
        .Parameters("@@nEmpId") = EmpId
        .Execute    '执行存储过程
        Total = .Parameters("@@dValue") '显示存储过程的执行结果
      End With
            
      '插入统计表
      sqlInsert = "INSERT INTO SalaryStatistics Values(" + Trim(EmpId) _
         + "," + Trim(iMonth) + "," + Trim(ItemId) + "," + Trim(Total) + ")"
      Set cmd.ActiveConnection = Conn
      '设置要执行的命令文本
      cmd.CommandText = sqlInsert
      '执行命令
      cmd.Execute
      
      '计算各公共变量数值
      If isOper = 0 Then       '税前加
        BTASum = CLng(BTASum) + CLng(Total)
      ElseIf isOper = 1 Then   '税前减
        BTDSum = CLng(BTDSum) + CLng(Total)
      ElseIf isOper = 2 Then   '税后加
        ATASum = CLng(ATASum) + CLng(Total)
      ElseIf isOper = 3 Then  '税后减
        ATDSum = CLng(ATDSum) + CLng(Total)
      End If
      rsItem.MoveNext
    Loop
    '根据公关变量的值计算：
    '税前工资总额 = 基本工资 + BTASum - BTDSum；
    '应纳税工资额 = 税前工资总额 - 工资纳税基数；
    '纳税后工资额 = 应纳税工资额*纳税系数 - 速算扣除数；
    '纳税金额 = 税前工资总额 - 纳税后工资额
    '实发工资 = 纳税后工资额 + ATASum - ATDSum + BTax
    '将税前工资总额、应纳税工资额、纳税后工资额和实发工资记录插入统计表中，
    '其中ItemId分别设置为-1、-2、-3、-4
    Set cmd.ActiveConnection = Conn
    BTotal = BSalary + BTASum - BTDSum
    ItemId = -1
    Total = BTotal
    sqlInsert = "INSERT INTO SalaryStatistics Values(" + Trim(EmpId) _
       + "," + Trim(iMonth) + "," + Trim(ItemId) + "," + Trim(Total) + ")"
    '设置要执行的命令文本
    cmd.CommandText = sqlInsert
    '执行命令
    cmd.Execute
    '应纳税工资额
    TTotal = BTotal - BTax
    ItemId = -2
    Total = TTotal
    sqlInsert = "INSERT INTO SalaryStatistics Values(" + Trim(EmpId) _
       + "," + Trim(iMonth) + "," + Trim(ItemId) + "," + Trim(Total) + ")"
    '设置要执行的命令文本
    cmd.CommandText = sqlInsert
    '执行命令
    cmd.Execute
    '计算纳税后金额
    ATotal = CalculateSum(TTotal)
    ItemId = -3
    Total = ATotal
    sqlInsert = "INSERT INTO SalaryStatistics Values(" + Trim(EmpId) _
       + "," + Trim(iMonth) + "," + Trim(ItemId) + "," + Trim(Total) + ")"
    '设置要执行的命令文本
    cmd.CommandText = sqlInsert
    '执行命令
    cmd.Execute
    '实发金额
    RealSalary = ATotal + ATASum - ATDSum + BTax
    ItemId = -4
    Total = RealSalary
    sqlInsert = "INSERT INTO SalaryStatistics Values(" + Trim(EmpId) _
       + "," + Trim(iMonth) + "," + Trim(ItemId) + "," + Trim(Total) + ")"
    '设置要执行的命令文本
    cmd.CommandText = sqlInsert
    '执行命令
    cmd.Execute
    rsItem.Close()
    rs1.MoveNext()
  Loop
End Sub
'根据金额计算纳税后金额
Public Function CalculateSum(TmpSum)
  Dim rs2
  Set rs2 = Server.CreateObject("ADODB.RECORDSET")
  SqlStmt = "SELECT * FROM TaxRate WHERE Id<>1 AND " _
     + Trim(TmpSum) + " BETWEEN LowerLimit" + " AND UpperLimit"
  '将结果集读取到rs2中
  Set rs2 = Conn.Execute(SqlStmt)
  If Not rs2.EOF Then
    CalculateSum = CDbl(TmpSum) - (CDbl(TmpSum) * CDbl(rs2.Fields(3)) - CDbl(rs2.Fields(4)))
  Else
    CalculateSum = TmpSum  '没有纳税范围则返回原数据
  End If
End Function

%>