recretquery.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
            BindGrid()
        End If
    End Sub
    
    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
        str2="select count(*) as nums,sum(retnum) as retnums,sum(retnum*price) as summy,sum(retnum*price*recretdis) as sumsy from recretbooksheet a,bookinfosheet b,buyerinfosheet c,pressinfosheet d where a.buyercode=c.code and a.bookcode=b.code and presscode=d.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("retnums").tostring & " 总码洋 " & datar("summy").tostring & " 总实洋  " & datar("sumsy").tostring
            end if
        end if
        datar.close()
        str1="select a.code,ddate,c.name as buyername,bookcode,b.name as bookname,d.abbrname,author,price,retnum,recretdis from recretbooksheet a,bookinfosheet b,buyerinfosheet c,pressinfosheet d where a.buyercode=c.code and a.bookcode=b.code and presscode=d.code " & str1 & "  order by a.code desc,bookcode"
        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
        dim bookcodestr as string=E.Item.Cells(3).text
    
        if CType(e.CommandSource,LinkButton).CommandName="delete" then
        '删除操作
            cnn=new sqlconnection(configurationsettings.appsettings("connection"))
            cnn.open()
            sql="delete from recretbooksheet where code='" & trim(codestr) & "' and bookcode='" & trim(bookcodestr) & "'"
            cmd=new sqlcommand(sql,cnn)
            cmd.executeNonQuery
            cnn.close
            bindgrid()
        else
            if CType(e.CommandSource,LinkButton).CommandName="deleteunit" then
                '删除整单操作
                cnn=new sqlconnection(configurationsettings.appsettings("connection"))
                cnn.open()
                sql="delete from recretbooksheet where code='" & codestr & "'"
                cmd=new sqlcommand(sql,cnn)
                cmd.executeNonQuery
                cnn.close()
                bindgrid()
            end if
        end if
    End Sub
    
    Sub Grid1_Update(Sender as object,E as DataGridCommandEventArgs)
        dim BookCodeStr as String=E.Item.Cells(3).Text
        dim NumStr As String=CType(E.Item.Cells(8).Controls(0),TextBox).Text
        dim DiscountStr as string=ctype(E.Item.Cells(9).Controls(0),TextBox).Text
        dim CodeStr as String=E.Item.Cells(0).Text
        dim theDiscount as single
        dim sql as string
        if Isnumeric(numstr) then
            thediscount=1
            if isnumeric(discountstr) then
                if csng(discountstr)>0 and csng(discountstr)<=1 then
                    thediscount=csng(discountstr)
                end if
            end if
            cnn=new sqlconnection(configurationsettings.appsettings("connection"))
            cnn.open()
    
            sql="update recretbooksheet set retnum=" & cint(numstr) & ",recretdis=" & thediscount & " where code='" & codestr & "' and bookcode='" & bookcodestr & "'"
            cmd=new sqlcommand(sql,cnn)
            cmd.executeNonQuery
            cnn.close()
        end if
        DataGrid1.EditItemIndex=-1
        BindGrid()
    End Sub
    
    Sub Grid1_Cancel(Sender as object,E as DataGridCommandEventArgs)
        datagrid1.EditItemIndex=-1
        BindGrid()
    End Sub
    
    Sub Grid1_Edit(Sender as object,E as DataGridCommandEventArgs)
        DataGrid1.EditItemIndex=E.Item.ItemIndex
        BindGrid()
    End Sub
    
    Sub Grid1_PageIndexChanged(Sender As Object, E As DataGridPageChangedEventArgs)
        datagrid1.CurrentPageIndex = e.NewPageIndex
        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

</script>
<html>
<head>
    <title>收退查询</title>
    <link href="../Main.css" type="text/css" rel="stylesheet" />
</head>
<body>
    <form runat="server">
        <b>收退查询</b>
        <hr size="1" />
        <table id="search" cellspacing="1" cellpadding="0" width="100%" align="center" border="0">
            <tbody>
                <tr class="tr2">
                    <td style="PADDING-LEFT: 5px" height="25">
                        检索条件：
                        <asp:DropDownList class="input_text" id="query_tj" runat="server" width="97">
                            <asp:ListItem Value="a.code" Selected="True">收退单编码</asp:ListItem>
                            <asp:ListItem Value="c.name">购书单位</asp:ListItem>
                            <asp:ListItem Value="b.name">图书名称</asp:ListItem>
                            <asp:ListItem Value="author">作者</asp:ListItem>
                        </asp:DropDownList>
                        &nbsp;&nbsp;检索内容：
                        <asp:TextBox class="input_text" id="query_content" runat="server" width="120"></asp:TextBox>
                        &nbsp;&nbsp;
                        <asp:button class="input_button" id="QueryBtn" onclick="Query_Click" runat="server" CausesValidation="False" text="查  询"></asp:button>
                        <br />
                        <b><asp:Label id="Msg" runat="server" forecolor="red"></asp:Label></b></td>
                </tr>
                <tr>
                    <td style="PADDING-LEFT: 5px" height="25">
                        <asp:DataGrid id="DataGrid1" runat="server" width="100%" OnupdateCommand="Grid1_Update" OnCancelCommand="Grid1_Cancel" OnEditCommand="Grid1_Edit" PageSize="12" AutoGenerateColumns="False" OnItemCommand="Grid1_Del" headerstyle-backcolor="#F8FAFC" font-size="8pt" font-name="verdana" cellpadding="2" bordercolor="Black" Font-Names="verdana" AllowPaging="True" AllowSorting="True" OnPageIndexChanged="Grid1_PageIndexChanged">
                            <HeaderStyle font-bold="True" horizontalalign="Center" verticalalign="Middle" backcolor="#F8FAFC"></HeaderStyle>
                            <PagerStyle nextpagetext="下一页" font-names="宋体" font-bold="True" prevpagetext="上一页" horizontalalign="Right" forecolor="Blue" mode="NumericPages"></PagerStyle>
                            <Columns>
                                <asp:BoundColumn DataField="Code" ReadOnly="True" HeaderText="收退单编码"></asp:BoundColumn>
                                <asp:BoundColumn DataField="ddate" ReadOnly="True" HeaderText="收退日期" DataFormatString="{0:d}"></asp:BoundColumn>
                                <asp:BoundColumn DataField="buyername" ReadOnly="True" HeaderText="购书单位"></asp:BoundColumn>
                                <asp:BoundColumn Visible="False" DataField="bookcode" ReadOnly="True" HeaderText="图书编码"></asp:BoundColumn>
                                <asp:BoundColumn DataField="bookname" ReadOnly="True" HeaderText="图书名称"></asp:BoundColumn>
                                <asp:BoundColumn DataField="abbrname" ReadOnly="True" HeaderText="版别"></asp:BoundColumn>
                                <asp:BoundColumn DataField="author" ReadOnly="True" HeaderText="作者"></asp:BoundColumn>
                                <asp:BoundColumn DataField="price" ReadOnly="True" HeaderText="定价" DataFormatString="{0:C}"></asp:BoundColumn>
                                <asp:BoundColumn DataField="retnum" HeaderText="收退数"></asp:BoundColumn>
                                <asp:BoundColumn DataField="recretdis" HeaderText="折扣"></asp:BoundColumn>
                                <asp:EditCommandColumn ButtonType="LinkButton" UpdateText="更新" HeaderText="编辑" CancelText="取消" EditText="编辑"></asp:EditCommandColumn>
                                <asp:ButtonColumn Text="删除" HeaderText="删除" CommandName="delete"></asp:ButtonColumn>
                                <asp:ButtonColumn Text="删除整单" HeaderText="删除整单" CommandName="deleteunit"></asp:ButtonColumn>
                            </Columns>
                        </asp:DataGrid>
                    </td>
                </tr>
            </tbody>
        </table>
    </form>
</body>
</html>