paycash.aspx

本系统主要内容是围绕图书销售管理系统而展开讨论的

<%@ 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
    Sub Page_Load(Sender As Object, E As EventArgs)
        If Not IsPostBack Then
            SelProvider.DataSource = CreateDataSource()
            SelProvider.DataTextField = "abbrname"
            SelProvider.DataValueField = "code"
            SelProvider.DataBind()
            ddate.text=year(now) & "-" & format(month(now),"00") & "-" & format(day(now),"00")
            BindGrid()
        End If
    End Sub
    
    Function CreateDataSource() As ICollection
        dim dtcmd as sqldataadapter
        dim dset as dataset
        cnn=new sqlconnection(configurationsettings.appsettings("connection"))
        dtcmd=new sqldataadapter("select * from providerinfosheet order by code",cnn)
        dset=new dataset
        dtcmd.fill(dset)
        Dim dv As New DataView(dset.tables(0))
        Return dv
        cnn.close()
    End Function
    
    Sub JS_Click(Sender As Object,E As EventArgs)
        dim datar as sqldatareader
        dim sql as string
        dim rkmoney as single
        dim retmoney as single
        dim hadpay as single
        dim shouldpay as single
        dim ProviderCode as string=selprovider.selecteditem.value
        cnn=new sqlconnection(configurationsettings.appsettings("connection"))
        cnn.open()
        '计算该供应商的入库金额
        sql="select isnull(sum(instorenum*price*discount),0) as inmoney from instoresheet a,bookinfosheet b where a.bookcode=b.code and providercode='" & providercode & "'"
        cmd=new sqlcommand(sql,cnn)
        datar=cmd.executereader
        if datar.read() then
            rkmoney=datar("inmoney")
        else
            rkmoney=0
        end if
        datar.close()
        '计算该供应商的退书金额
        sql="select isnull(sum(retnum*price*retdiscount),0) as retmoney from sendretbooksheet a,bookinfosheet b where a.bookcode=b.code and providercode='" & providercode & "'"
        cmd=new sqlcommand(sql,cnn)
        datar=cmd.executereader
        if datar.read() then
            retmoney=datar("retmoney")
        else
            retmoney=0
        end if
        datar.close()
        '计算该供应商的已付金额
        sql="select isnull(sum(paymoney),0) as paymoney from paycashsheet where providercode='" & providercode & "'"
        cmd=new sqlcommand(sql,cnn)
        datar=cmd.executereader
        if datar.read() then
            hadpay=datar("paymoney")
        else
            hadpay=0
        end if
        datar.close()
        shouldpay=rkmoney-retmoney-hadpay
        label1.text="进书金额 " & rkmoney & "    退书金额  " & retmoney & "    已付金额  " & hadpay & "    应付金额  " & shouldpay
    
    End Sub
    
    Sub Query_Click(sender As Object, e As EventArgs)
        Bindgrid()
    End Sub
    
    Sub Save_Click(Sender As Object, E As EventArgs)
        dim UnitCode as string
        dim paymoney as string=txtcash.text
        dim CashMode as string=selcashmode.selecteditem.value
        dim fphstr as string=txtfph.text
        dim memo as string=txtmemo.text
        dim theDate as date
        dim sql as string
        dim dataR as sqldatareader
        if not isdate(ddate.text) then
            page.registerstartupscript("",getalertinfo("错误的日期！"))
            exit sub
        end if
        if not isnumeric(paymoney) then
            page.registerstartupscript("",getalertinfo("错误的金额！"))
            exit sub
        end if
        thedate=cdate(ddate.text.tostring())
        cnn=new sqlconnection(configurationsettings.appsettings("connection"))
        cnn.open()
        unitcode=trim(right(year(now),2)) & trim(format(month(now),"00")) & trim(format(day(now),"00"))
        sql="select * from paycashsheet where left(code,6)='" & trim(unitcode) & "' order by code desc"
        cmd=new sqlcommand(sql,cnn)
        datar=cmd.executereader()
        if datar.read() then
            unitcode=trim(unitcode) & trim(format(cint(right(datar("code").tostring,3))+1,"000"))
        else
            unitcode=trim(unitcode) & "001"
        end if
        datar.close()
    
        sql="insert into paycashsheet(code,ddate,providercode,paymoney,paymode,fph,memo) values('" & unitcode & "','" & thedate & "','" & selprovider.selecteditem.value & "'," & csng(paymoney) & ",'" & cashmode & "','" & fphstr & "','" & memo & "')"
        cmd=new sqlcommand(sql,cnn)
        cmd.executeNonQuery
        cnn.close()
        bindgrid()
    
        txtcash.text=""
        txtfph.text=""
        txtmemo.text=""
        label1.text=""
        page.registerstartupscript("",getalertinfo("数据保存成功！"))
    
    End Sub
    
    Sub BindGrid()
        dim dataR as sqldatareader
        dim dtcmd as sqldataadapter
        dim dt as new datatable
        dim MaxPage as integer  '计算最大页数
        dim str2 as string
        dim Str1 as string
        cnn=new sqlconnection(configurationsettings.appsettings("connection"))
        if query_content.text<>"" then
            str1=" and " & query_tj.SelectedItem.value & " like '%" & query_content.text & "%'"
        end if
        str2="select count(*) as nums,sum(paymoney) as moneys from paycashsheet a,providerinfosheet b where a.providercode=b.code " & str1
        cmd=new sqlcommand(str2,cnn)
        cnn.open()
        datar=cmd.executereader()
        if datar.read() then
            if datar("nums").tostring()="0" then
                msg.text=""
            else
                msg.text="合计：记录数 " & datar("nums").tostring() & "  总金额 " & datar("moneys").tostring
            end if
        end if
        datar.close()
        str1="select a.code,ddate,b.abbrname as providername,paymoney,paymode,fph,memo from paycashsheet a,providerinfosheet b where a.providercode=b.code " & str1 & "  order by a.code desc"
        dtcmd=new sqldataadapter(str1,cnn)
        dtcmd.fill(dt)
    
        if (dt.defaultview.count mod datagrid1.pagesize)>0 then
            maxpage=dt.defaultview.count \datagrid1.pagesize+1
        else
            maxpage=dt.defaultview.count \datagrid1.pagesize
        end if
        do while datagrid1.currentpageindex>0
            if maxpage<datagrid1.currentpageindex+1 then
                datagrid1.currentpageindex=datagrid1.currentpageindex-1
            else
                exit do
            end if
        loop
        datagrid1.datasource=dt
        datagrid1.databind()
        cnn.close()
    End Sub
    
    Sub Grid1_del(Sender As Object, E As DataGridCommandEventArgs)
        dim codestr as string=E.Item.Cells(0).Text
        dim sql as string
        dim datar as sqldatareader
    
        if CType(e.CommandSource,LinkButton).CommandName="delete" then