sendsubadd.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
    Shared DTable as new datatable("Subtable")
    'Public DTable as datatable
    Sub Page_Load(Sender As Object, E As EventArgs)
    '    SaveBtn.Attributes.Add("onclick","return confirm('skdfjsdfk');")
    
        if dtable.columns.count=0 then
            dtable.columns.add("code",System.Type.GetType("System.String"))
            dtable.columns.add("name",system.type.gettype("System.String"))
            dtable.columns.add("cbs",system.type.gettype("System.String"))
            dtable.columns.add("author",system.type.gettype("System.String"))
            dtable.columns.add("price",system.type.gettype("System.Single"))
            dtable.columns.add("recnum",system.type.gettype("System.Int32"))
            dtable.columns.add("Hadsubnum",system.type.gettype("System.Int32"))
            dtable.columns.add("subnum",system.type.gettype("System.Int32"))
            dtable.columns.add("kcnum",system.type.gettype("System.Int32"))
            dim thekey() as datacolumn={dtable.columns("code")}
            dtable.primarykey=thekey
            bindgrid1()
        end if
        If Not IsPostBack Then
            SelBuyer.DataSource = CreateDataSource()
            SelBuyer.DataTextField = "abbrname"
            SelBuyer.DataValueField = "code"
            SelBuyer.DataBind()
            ddate.text=year(now) & "-" & format(month(now),"00") & "-" & format(day(now),"00")
    
            BindGrid()
            BindGrid1()
        End If
    End Sub
    
    Sub Clear_Click(Sender As Object, E As EventArgs)
        dtable.rows.clear()
        bindgrid1()
    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 desc",cnn)
        dset=new dataset
        dtcmd.fill(dset)
        Dim dv As New DataView(dset.tables(0))
        Return dv
        cnn.close()
    End Function
    
    Sub Query_Click(sender As Object, e As EventArgs)
        Bindgrid()
    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
        str1="select a.*,abbrname,storeamounta,storeamountz from bookinfosheet a,pressinfosheet b,bookstoresheeta c,bookstoresheetz d where a.presscode=b.code and a.code=c.bookcode and a.code=d.bookcode " & str1 & " order by a.code"
        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 BindGrid1()
        dim MaxPage as integer  '计算最大页数
        dim str2 as string
        dim Str1 as string
        if (dtable.defaultview.count mod datagrid2.pagesize)>0 then
            maxpage=dtable.defaultview.count \datagrid2.pagesize+1
        else
            maxpage=dtable.defaultview.count \datagrid2.pagesize
        end if
        do while datagrid2.currentpageindex>0
            if maxpage<datagrid2.currentpageindex+1 then
                datagrid2.currentpageindex=datagrid2.currentpageindex-1
            else
                exit do
            end if
        loop
        datagrid2.datasource=dtable
        datagrid2.databind()
    End Sub
    
    
    Sub AddToSub_Click(Sender As Object, E As DataGridCommandEventArgs)
        dim dataR as sqldatareader
        if CType(e.CommandSource,LinkButton).CommandName="addtosub" then
            dim CodeStr as string=E.Item.Cells(0).text
            dim NameStr as string=E.Item.Cells(1).text
            dim CbsStr as string=E.Item.Cells(2).text
            dim AuthorStr as string=E.Item.Cells(4).text
            dim PriceStr as single=Csng(E.Item.Cells(3).text.tostring())
            dim kcstr as integer=cint(E.Item.Cells(6).text.tostring())
            dim myDataRowCollection as datarowcollection
            dim Num1 as integer
            dim Num2 as integer
            dim SumNum as integer
            dim sql as string
            dim IntRecSubNum as integer
            dim IntHadSubNum as integer
    
            dim DRow as datarow=dtable.newrow()
            mydatarowcollection=dtable.rows
            if mydatarowcollection.Contains(codestr) then
                Page.RegisterStartupScript("",getalertinfo("该图书已在订单中，无法再次添加！"))
            else
                cnn=new sqlconnection(configurationsettings.appsettings("connection"))
                cnn.open
                '获取总的收订数
                sql="select isnull(sum(subnum),0) as nums from receivesubsheet where bookcode='" & trim(codestr) & "'"
                cmd=new sqlcommand(sql,cnn)
                datar=cmd.executereader()
                if datar.read() then
                   intrecsubnum=cint(datar("nums"))
                else
                   intrecsubnum=0
                end if
                datar.close()
                '获取总的已订数
                sql="select isnull(sum(subnum),0) as nums from sendsubsheet where bookcode='" & trim(codestr) & "'"
                cmd=new sqlcommand(sql,cnn)
                datar=cmd.executereader
                if datar.read() then
                    inthadsubnum=cint(datar("nums"))
                else
                    inthadsubnum=0
                end if
                datar.close()
                cnn.close()
    
                drow(0)=trim(codestr)
                drow(1)=trim(namestr)
                drow(2)=trim(cbsstr)
                drow(3)=trim(authorstr)
                drow(4)=trim(pricestr)
                drow(5)=trim(intrecsubnum)
                drow(6)=trim(inthadsubnum)
                drow(8)=trim(kcstr)
                if (intrecsubnum-inthadsubnum-kcstr)>0 then
                    drow(7)=trim(intrecsubnum-inthadsubnum-kcstr)
                end if
    
                dtable.rows.add(drow)
    
    
    
                '定位到当前所添加的记录上
      '          sumnum=dtable.defaultview.count+1
     '           num1=sumnum \ datagrid2.pagesize
    '            num2=sumnum mod datagrid2.pagesize
    '            if num1>1 then
    '                datagrid2.currentpageindex=num1
    '            end if
    '            if num2=0 then
    '                datagrid2.EditItemIndex=datagrid2.pagesize-1
    '            else
    '                datagrid2.EditItemIndex=num2-1
    '            end if
    '            trace.write("currentpageindex",datagrid2.currentpageindex)
    '            trace.write("edititemindex",datagrid2.edititemindex)
    '            trace.write("num1",num1)
                bindgrid1()
            end if
        end if
    end Sub
    
    sub Jh_Click(Sender As Object,E As EventArgs)
        dim datar as sqldatareader
        dim sql as string
        dim DRow as datarow
        dtable.rows.clear()
        cnn=new sqlconnection(configurationsettings.appsettings("connection"))
        cnn.open()
        sql="select bookcode,sum(subnum) as recnum,0 as sendsub,0 as kc into #mid1 from receivesubsheet group by bookcode"
        cmd=new sqlcommand(sql,cnn)
        cmd.executenonquery
        sql="select sendsubsheet.bookcode as bookcode1,sum(subnum) as sendsubnum into #mid2 from sendsubsheet,#mid1 where sendsubsheet.bookcode=#mid1.bookcode group by sendsubsheet.bookcode"
        cmd=new sqlcommand(sql,cnn)
        cmd.executenonquery
        sql="update #mid1 set sendsub=sendsubnum from #mid2 where bookcode=bookcode1"
        cmd=new sqlcommand(sql,cnn)
        cmd.executenonquery
        sql="update #mid1 set kc=storeamountz from bookstoresheetz where #mid1.bookcode=bookstoresheetz.bookcode"
        cmd=new sqlcommand(Sql,cnn)
        cmd.executenonquery
        sql="select bookcode,abbrname,author,price,name,recnum,sendsub,kc from #mid1 a,bookinfosheet b,pressinfosheet c where a.bookcode=b.code and presscode=c.code and (recnum-sendsub-kc)>0 order by bookcode"
        cmd=new sqlcommand(sql,cnn)
        datar=cmd.executereader
        do while datar.read()
            drow=dtable.newrow()
            drow(0)=trim(datar("bookcode"))
            drow(1)=trim(datar("name"))
            drow(2)=trim(datar("abbrname"))
            drow(3)=trim(datar("author"))
            drow(4)=trim(csng(datar("price")))
            drow(5)=trim(cint(datar("recnum")))
            drow(6)=trim(cint(datar("sendsub")))
            drow(8)=trim(cint(datar("kc")))
            drow(7)=cint(datar("recnum"))-cint(datar("sendsub"))-cint(datar("kc"))
            dtable.rows.add(drow)
        loop
        datar.close()
        sql="drop table #mid1"
        cmd=new sqlcommand(sql,cnn)
        cmd.executenonquery
        sql="drop table #mid2"
        cmd=new sqlcommand(sql,cnn)