sklr.aspx

来自「ASP.NET开发实战详解」· ASPX 代码 · 共 635 行 · 第 1/2 页

<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="system.data" %>
<%@ import Namespace="system.data.sqlclient" %>
<script runat="server">

    dim Cnn as sqlconnection
    dim cmd as sqlcommand
    Shared DTable as new datatable("Subtable")
    Shared ZnjStr as single

    Sub Page_Load(Sender As Object, E As EventArgs)
      dim Str1 as string
      dim datar as sqldatareader

        if dtable.columns.count=0 then
            dtable.columns.add("id",System.Type.GetType("System.Int32"))
            dtable.columns.add("sfitem",system.type.gettype("System.String"))
            dtable.columns.add("sfterm",system.type.gettype("System.Int32"))
            dtable.columns.add("shouldmoney",system.type.gettype("System.Single"))
            dtable.columns.add("realmoney",system.type.gettype("System.Single"))
            dtable.columns.add("sfys",system.type.gettype("System.String"))
            dtable.columns.add("ddate",system.type.gettype("System.DateTime"))
            dtable.columns.add("memo",system.type.gettype("System.String"))
            dim thekey() as datacolumn={dtable.columns("id")}
            dtable.primarykey=thekey
        end if
      If Not IsPostBack Then
         If Session("curruser") = "" Then
            Response.Redirect("../index.aspx")
         End If

                cnn=new sqlconnection(configurationsettings.appsettings("connection"))
                cnn.open()
                str1="select * from cw_para"
                cmd=new sqlcommand(str1,cnn)
                datar=cmd.executereader
                if datar.read() THEN
                   znjstr=datar("znmoney")
                else
                   znjstr=0
                end if
                datar.close()
                cnn.close() 
        bindgrid()
        sftime.text=year(now) & "-" & month(now) & "-" & day(now)  
      end if
    End Sub
    
    Sub save_click(Sender As Object, E As EventArgs)
      dim datestr as string=sftime.text
      dim sffsstr as string=sffs.selecteditem.value
      dim jfrstr as string=jfr.text
      dim jsrstr as string=jsr.text
      dim myDataRowCollection as datarowcollection
      dim theRow as datarow      
      dim thetag as integer
      dim str1 as string
      if trim(datestr)="" then
         page.registerstartupscript("",getalertinfo("收费日期不可为空！"))
         exit sub 
      end if
      mydatarowcollection=dtable.rows
      if mydatarowcollection.count=0 then
         Page.RegisterStartupScript("",getalertinfo("没有收费数据，无法保存！"))
         exit sub
      end if
      thetag=0
      cnn=new sqlconnection(configurationsettings.appsettings("connection"))
      cnn.open
      for each therow in mydatarowcollection
        if cint(therow(4))<>0 then
          str1="update cw_sf set ddate='" & datestr & "',jfr='" & jfrstr & "',agent='" & jsrstr & "',sffs='" & sffsstr & "',realmoney=" & therow(4) & ",memo='" & therow(7) & "' where id=" & therow(0)
          cmd=new sqlcommand(str1,cnn)
          cmd.executenonquery
          thetag=1
        end if
      next
      if thetag<>1 then
         Page.RegisterStartupScript("",getalertinfo("没有添加收费数据，无法保存！"))
         exit sub
      end if        
      mydatarowcollection.clear()
      page.registerstartupscript("",getalertinfo("数据保存成功！"))
      ysk.text=""
      ccsk.text=""
      znj.text=""
      hj.text=""
      bindgrid()
    End Sub
    
    Function GetAlertInfo(byval str1 as string) as String
        dim Str2 as String
        str2="<script language='javascript'>alert('" & str1 & "')<"
        str2+="/"
        str2+="script>"
        return trim(str2)
    End Function

         Sub BindGrid()
            datagrid1.datasource=dtable
            datagrid1.databind()
         End Sub


    Sub yskbtn_click(Sender As Object, E As EventArgs)
       dim str1 as string
       dim datar as sqldatareader
       dim datar1 as sqldatareader
       dim dystr as string=dyname.text
       dim NumStr as single
       dim dycode as string 
       dim DFMoney as single
       dim SFMoney as single
       dim QFMoney as single
       dim DFItem as integer
       dim SFItem as integer
       dim QFItem as integer

   dim Cnn1 as sqlconnection
   dim cmd1 as sqlcommand
   dim thetag as integer
   dim thedate as datetime
   dim themonth as integer
   dim themoney as single

       dim yfstr as integer=cint(sfyf.selecteditem.value)
       if trim(dystr)="" then
          page.registerstartupscript("",getalertinfo("请先输入单元名称！"))
          exit sub
       end if
       cnn=new sqlconnection(configurationsettings.appsettings("connection"))
       cnn.open
       str1="select * from fc_cell where name='" & trim(dystr) & "'"
       cmd=new sqlcommand(str1,cnn)
       datar=cmd.executereader
       if datar.read() then
          dycode=trim(datar("code"))
       else
          page.registerstartupscript("",getalertinfo("所输入的单元不存在！"))
          datar.close()
          exit sub
       end if
       datar.close()

                str1="select * from cw_sf_other_set where sfitem='电费'"
                cmd=new sqlcommand(str1,cnn)
                datar=cmd.executereader
                if datar.read() THEN
                   dfitem=datar("id")
                end if
                datar.close()
                str1="select * from cw_sf_other_set where sfitem='水费'"
                cmd=new sqlcommand(str1,cnn)
                datar=cmd.executereader
                if datar.read() THEN
                   sfitem=datar("id")
                end if
                datar.close()
                str1="select * from cw_sf_other_set where sfitem='气费'"
                cmd=new sqlcommand(str1,cnn)
                datar=cmd.executereader
                if datar.read() THEN
                   qfitem=datar("id")
                end if
                datar.close() 


       '判断是否已有应收款记录且已实际收款
       str1= "select * from cw_sf where cellcode='" & Trim(dyCode) & "' and sfterm=" & yfstr & " and realmoney<>0"
       cmd=new sqlcommand(str1,cnn)
       datar=cmd.executereader
       if datar.read() Then
          page.registerstartupscript("",getalertinfo("该单元本月已存在收款信息，无法再次生成应收款！")) 
          datar.close()
          exit sub
       end if
       datar.close()

       '如无实际收款，删除原有应收款记录
       str1 = "delete from cw_sf where cellcode='" & Trim(dyCode) & "' and sfterm=" & yfstr
       cmd=new sqlcommand(str1,cnn)
       cmd.executenonquery

   '电费
   str1="select cw_biao_user.id,initnum,price from cw_biao_info,cw_biao_user where biaocode=cw_biao_info.id and type='电表' and cellcode='" & Trim(dyCode) & "' order by cw_biao_user.id"
   cmd=new sqlcommand(str1,cnn)
   datar=cmd.executereader
   dfmoney=0
   do while datar.read()
      numstr=retnum(yfstr,datar("id"),datar("initnum"))
      dfmoney=dfmoney+numstr * datar("price")
   loop
   datar.close
   if dfmoney<>0 then
      str1="insert into cw_sf(cellcode,itemcode,sfterm,shouldmoney,realmoney) values('" & trim(dycode) & "'," & dfitem & "," & yfstr & "," & dfmoney & ",0)"
      cmd=new sqlcommand(str1,cnn)
      cnn.open
      cmd.executenonquery
   end if
  
   '水费
   cnn.close()
   cnn=new sqlconnection(configurationsettings.appsettings("connection"))
   cnn.open()
   str1="select cw_biao_user.id,initnum,price from cw_biao_info,cw_biao_user where biaocode=cw_biao_info.id and type='水表' and cellcode='" & Trim(DYCode) & "' order by cw_biao_user.id"
   cmd=new sqlcommand(str1,cnn)
   datar=cmd.executereader
   sfmoney=0
   do while datar.read()
      numstr=retnum(yfstr,datar("id"),datar("initnum"))
      sfmoney=sfmoney+numstr * datar("price")
   loop
   datar.close()
   if sfmoney<>0 then
      str1="insert into cw_sf(cellcode,itemcode,sfterm,shouldmoney,realmoney) values('" & trim(dycode) & "'," & sfitem & "," & yfstr & "," & sfmoney & ",0)"
      cmd=new sqlcommand(str1,cnn)
      cnn.open()
      cmd.executenonquery
   end if     

   
   '气费
   cnn.close()
   cnn=new sqlconnection(configurationsettings.appsettings("connection"))
   cnn.open()
   str1="select cw_biao_user.id,initnum,price from cw_biao_info,cw_biao_user where biaocode=cw_biao_info.id and type='气表' and cellcode='" & Trim(DYCode) & "' order by cw_biao_user.id"
   cmd=new sqlcommand(str1,cnn)
   datar=cmd.executereader
   qfmoney=0
   do while datar.read()
      numstr=retnum(yfstr,datar("id"),datar("initnum"))
      qfmoney=qfmoney+numstr * datar("price")
   loop
   datar.close()
   if qfmoney<>0 then
      str1="insert into cw_sf(cellcode,itemcode,sfterm,shouldmoney,realmoney) values('" & trim(dycode) & "'," & qfitem & "," & yfstr & "," & sfmoney & ",0)"
      cmd=new sqlcommand(str1,cnn)
      cnn.open
      cmd.executenonquery
   end if     


   '额外设置的费用

   cnn1=new sqlconnection(configurationsettings.appsettings("connection"))
   cnn1.open
   cnn.close()
   cnn=new sqlconnection(configurationsettings.appsettings("connection"))
   cnn.open()
   str1="select itemcode,sfitem,sfprice,sfunit,sfterm,sfstart,sfstartdate,ysorno from cw_sf_other_set,cw_sf_user where itemcode=cw_sf_other_set.id and ttype<>'0' and cellcode='" & Trim(dycode) & "'"   
   cmd=new sqlcommand(str1,cnn)
   datar=cmd.executereader
   do while datar.read()
      '考虑如下几个要素：收费价格、收费单位、收费周期、收费开始时间
      select case trim(datar("sfstart"))
      case 0  '按业主收楼日期算起
        str1="select slrq from zh_owner where cellcode='" & trim(dycode) & "'"
        cmd1=new sqlcommand(str1,cnn1)
        datar1=cmd1.executereader
        if datar1.read() then
          if isdbnull(datar1("slrq")) then
            thetag=0
          else
            thedate=datar1("slrq")
            thetag=1
          end if
        else
          thetag=0
        end if
        datar1.close()
     case 1  '按户主入住日期算起
        str1="select rzrq from zh_master where cellcode='" & trim(dycode) & "'"
        cmd1=new sqlcommand(str1,cnn1)
        datar1=cmd1.executereader
        if datar1.read() then
          if isdbnull(datar1("rzrq")) then
            thetag=0
          else
            thedate=datar1("rzrq")
            thetag=1
          end if
        else
          thetag=0
        end if
        datar1.close()
     case else   '按指定日期算起
        if isdbnull(datar("sfstartdate")) then
          thedate=trim(datar("sfstartdate"))
          thetag=1
        else
          thetag=0
        end if
     end select
 
     'i为0表示没有设置相关收费开始日期，暂不收费
     if thetag=1 and yfstr>month(thedate) then
       themonth=yfstr-month(thedate)
       if themonth>0 or (themonth=0 and datar("ysorno")="1") then
       '超过指定日期或是当月且预收
         if themonth mod datar("sfterm")=0 then
           '符合收费条件
           str1="select isnull(sfmj,0) as sfmj,isnull(symj,0) as symj,isnull(jzmj,0) as jzmj,isnull(fjmj,0) as fjmj from fc_cell where code='" & trim(dycode) & "'"
           cmd1=new sqlcommand(str1,cnn1)
           datar1=cmd1.executereader
           if datar1.read() then
             select case trim(datar("sfunit"))
             case "0"  '按户进行收费
               themoney=datar("sfprice")
             case "1"  '按平方米(建筑面积)收费 
               themoney=datar("sfprice")*datar1("jzmj")
             case "2" '按平方米(使用面积)收费
               themoney=datar("sfprice")*datar1("symj")
             case "3"  '按平方米(收费面积)收费
               themoney=datar("sfprice")*datar1("sfmj")
             case "4"  '按平方米(附加面积)收费
               themoney=datar("sfprice")*datar1("fjmj")
             end select
             datar1.close()