ERP仓库管理系统查询（十）

时间：2022-04-28 04:25:32
需求：
   1.根据仓库编号，获取仓库信息绑定至页面相关控件。
2.根据仓库编号，获取管理员信息绑定到页面相关控件

修改的界面：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="StockEdit.aspx.cs" Inherits="BioErpWeb.StockSystem.StockEdit" %>

<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="cc1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <link href="../Styles/ERPBaseStyle.css" rel="stylesheet" type="text/css" />

    <link href="../Styles/CalenderStyle.css" rel="stylesheet" type="text/css" />

    <style type="text/css">

        .style1

        {

            width: 100px;

        }

    </style>

    <script src="../JS/CheckUserNames.js" type="text/javascript"></script>

    <script src="../Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>

    <script type="text/javascript">

        var i = 0;

        $(document).ready(function () {

            $("#btnaddRow").click(function () {

                i++;

                var tr = '<tr id="tr' + i + '"><td><input type="text" name="UserId"  id="txtUserName"/></td><td class="style1"><input type="button" value="选择员工"  style=" width:100px;" onclick="showDialog()"/></td></tr>';

                $("#caption").before(tr);

            });

            $("#btnDeleteRow").click(function () {

                var lasttr = $("#tr" + i);

                lasttr.remove();

                i--;

            });

            //验证

            $("#btnSubmit").click(function () {

                var stockname = $("#txtStockName");

                if (stockname.val() == '') {

                    alert("请填写仓库名称");

                    return false;

                }

                var stockaddress = $("#txtAddress");

                if (stockaddress.val() == '') {

                    alert("请填写仓库地址");

                    return false;

                }

                //2011年10月26日9:54:56 完善下拉列表验证

                var selectcompany = $("#ddlCompany");

                var selectCaption = selectcompany.select();

                if (selectCaption.val() == 0) {

                    alert("请选择所属公司");

                    return false;

                }

                var userids = document.getElementsByName("UserId");

                for (var j = 0; j < userids.length; j++) {

                    if (userids[j].value == '' || userids[j].value == '请选择') {

                        alert("请选择第" + (j + 1) + "行用户编号");

                        return false;

                    }

                }

                return true;

            });

        });

        //添加count行 单元格

        function addRow(count) {

            for (var j = 1; j < count; j++) {

                i++;

                var tr = '<tr id="tr' + i + '"><td><input type="text" name="UserId"  id="txtUserName"/></td><td class="style1"><input type="button" value="选择员工"  style=" width:100px;" onclick="showDialog()"/></td></tr>';

                $("#caption").before(tr);

            }

        }

        //给员工控件设置userid

        function setValues(userids) {

//            alert(userids);

            var useridlist = userids.split(',');

            var txtuserids = document.getElementsByName("UserId");

            for (var j = 0; j < useridlist.length; j++) {

                txtuserids[j].value = useridlist[j];

            }

        }

    </script>

</head>

<body>

    <form id="form1" runat="server">

    <asp:ScriptManager ID="ScriptManager1" runat="server">

    </asp:ScriptManager>

    <div>

     <table class="maintable">

         <tr>

             <td class="titlebar"  colspan="2">

                 <span>仓库信息管理系统</span>

             </td>

         </tr>

         <tr>

             <td>

                 库房名称：

             </td>

             <td>

                 <asp:TextBox ID="txtStockName" Width="200px" runat="server"></asp:TextBox>

             </td>

         </tr>

         <tr>

             <td>

                 库房地址：

             </td>

             <td>

                 <asp:TextBox ID="txtAddress" Width="200px" runat="server"></asp:TextBox>

             </td>

         </tr>

         <tr>

             <td>

                 所属公司

             </td>

             <td>

                 <asp:DropDownList ID="ddlCompany" Width="200px" runat="server">

                 </asp:DropDownList>

             </td>

         </tr>

         <tr>

             <td>

                 仓库管理员

             </td>

             <td>

                 <table>

                 <tr><td><input type="text" name="UserId"  id="txtUserName"/></td><td class="style1"><input type="button" value="选择员工"  style=" width:100px;" onclick="showDialog()"/></td></tr>

                 <tr id="caption"><td colspan="2" style=" text-align:right;"><input type="button" id="btnaddRow" value="添加一行"  style=" width:100px;"/> <input type="button" value="删除一行" id="btnDeleteRow"  style=" width:100px;"/></td></tr>

                 </table>

             </td>

         </tr>

            <tr>

             <td>

                 仓库状态

             </td>

             <td>

                 <asp:DropDownList ID="ddlState" runat="server">

                     <asp:ListItem Value="1">正常</asp:ListItem>

                     <asp:ListItem Value="0">停用</asp:ListItem>

                 </asp:DropDownList>

             </td>

         </tr>

         <tr >

             <td class="bottomtd" colspan="2">

                 <asp:Button ID="btnSubmit" runat="server" Text="仓库信息修改" CssClass="submitbutton"

                     onclick="btnSubmit_Click"  />

             </td>

         </tr>

     </table>

        <br />

    </div>

    </form>

</body>

</html>

存储过程（使用通用的）：

-- Description:	根据指定列，指定条件，指定表查询数据

-- =============================================

ALTER PROCEDURE [dbo].[GetDataByCondition]

	@tableName nvarchar(200),

	@columns nvarchar(500),

	@condition nvarchar(500)=' 1=1'

	AS

BEGIN

	SET NOCOUNT ON;

    DECLARE @sqlStr nvarchar(2000)

    SET @sqlStr='select '+@columns+' from '+@tableName+' where '+@condition

    EXEC(@sqlStr)

END

定义一个标量值函数：

-- Description:	根据员工编号返回员工姓名

-- =============================================

ALTER FUNCTION [dbo].[getUserNameByUserID]

(

	@UserID int

)

RETURNS Nvarchar(20)

AS

BEGIN

	-- Declare the return variable here

	DECLARE @UserName nvarchar(20)

	-- Add the T-SQL statements to compute the return value here

	SELECT @UserName=UserName FROM UserManager WHERE UserManager.UserId=@UserID

	-- Return the result of the function

	RETURN @UserName

END

根据条件返回一个数据阅读器对象

  /// <summary>

        /// 根据条件返回一个数据阅读器对象

        /// </summary>

        /// <returns></returns>

        public static SqlDataReader GetDataReaderByCondition(string tableName, string columns, string condition)

        {

            SqlParameter[] pars = new SqlParameter[]{

                  new SqlParameter("@tableName",tableName),

                  new SqlParameter("@columns",columns),

                  new SqlParameter("@condition",condition)

                };

            SqlDataReader reader = DataBaseHelper.SelectSQLReturnReader("GetDataByCondition", CommandType.StoredProcedure, pars);

            return reader;

        }

BLL层的代码：

       /// <summary>

       /// 根据ID查询仓库表内容

       /// </summary>

       /// <param name="id">仓库ID</param>

       /// <returns>StockTable</returns>

       public StockTable getStockTableByID(int id)

       {

           //调用的通用的存储过程

       SqlDataReader reader=  SqlComm.GetDataReaderByCondition("BioErpStockTable", "*", " id="+id.ToString());

         StockTable stock = new StockTable();

         stock.ID = id;

         while (reader.Read())

         {

             stock.StockName = reader["StockName"].ToString();

             stock.StockAddress = reader["StockAddress"].ToString();

             stock.FarhterCompany = int.Parse(reader["FarhterCompany"].ToString());

             stock.IsDel = bool.Parse(reader["IsDel"].ToString());

         }

         reader.Close();

         return stock;

       }

根据StockID绑定员工列表

 /// <summary>

      /// 根据StockID绑定员工列表

      /// </summary>

      /// <param name="id">StockID</param>

      /// <returns>DataTable</returns>

      public DataTable getStockUsersListByStockID(int id)

      {

          DataTable dt = CommTool.SqlComm.GetDataByCondition("BioErpStockUsers", "ID,StockID,UserID,Username=dbo.getUserNameByUserID(UserID)", " StockID=" + id.ToString()).Tables[0];

          return dt;

      }

后台的代码：

  public partial class StockEdit : System.Web.UI.Page

    {

        CompanyTableBll companybll = new CompanyTableBll();

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                CompanyListBind();

                StockInfoBind();

                 StockUsersIist();

            } 

        }

        BioErpStockTableBLL stocktablebll = new BioErpStockTableBLL();

        BioErpStockUsersBLL stockuserbll = new BioErpStockUsersBLL();

       static BioErpStockUsers stockusers = new BioErpStockUsers();

       static StockTable stocktable = new StockTable();

        /// <summary>

        /// 仓库基本信息绑定

        /// </summary>

        private void StockInfoBind()

        {

            if (Request.QueryString["stockid"] != null)

            {

              string id=  Request.QueryString["stockid"].ToString();

              stocktable=  stocktablebll.getStockTableByID(int.Parse(id));

             this.txtStockName.Text= stocktable.StockName;

             this.txtAddress.Text = stocktable.StockAddress;

             this.ddlCompany.SelectedValue = stocktable.FarhterCompany.ToString();

             this.ddlState.SelectedValue = Convert.ToBoolean(stocktable.IsDel) ? "0" : "1";

            }

            else

            {

                Server.Transfer("StockTableList.aspx");

            }

        }

        private void CompanyListBind()

        {

            this.ddlCompany.DataSource = companybll.GetCompanyList();

            this.ddlCompany.DataTextField = "CompanyName";

            this.ddlCompany.DataValueField = "ID";

            this.ddlCompany.DataBind();

            this.ddlCompany.Items.Add(new ListItem("--请选择--","0"));

            this.ddlCompany.SelectedValue = "0";

        }

        /// <summary>

        /// 绑定仓库管理员列表

        /// </summary>

        private void StockUsersIist()

        {

             if (Request.QueryString["stockid"] != null)

            {

                 string id=Request.QueryString["stockid"].ToString();

            //查询仓库的管理员列表

             System.Data.DataTable dt=    stockuserbll.getStockUsersListByStockID(int.Parse(id));

             string userids = "";

             for (int i = 0; i < dt.Rows.Count; i++)

             {

                 userids = userids + dt.Rows[i]["UserID"].ToString()+",";

             }

            //计算仓库管理员个数

             int count = dt.Rows.Count;

            //调用RegisterStartupScript方法去调用前端的addRow 脚本函数 实现动态添加表格

             ClientScript.RegisterStartupScript(this.GetType(), "test", "addRow(" + count + ");", true);

             ClientScript.RegisterStartupScript(this.GetType(), "setValues", "setValues('" + userids + "');", true);

             }

        }

        protected void btnSubmit_Click(object sender, EventArgs e)

        {

            stocktable.StockName = txtStockName.Text;

            stocktable.StockAddress = txtAddress.Text;

            stocktable.IsDel = this.ddlState.SelectedValue == "1" ? false : true;

            stocktable.FarhterCompany =int.Parse(ddlCompany.SelectedValue.ToString());

            stocktablebll.StockTableUpdate(stocktable);

          string userids=  Request["UserId"].ToString();

          string[] useridlist = userids.Split(',');

          SqlComm.DeleteTableByCondition("BioErpStockUsers", "where StockID="+stocktable.ID.ToString());

          for (int i = 0; i < useridlist.Length; i++)

          {

              stockusers.StockID = stocktable.ID;

              stockusers.UserID =int.Parse(useridlist[i].ToString());

              stockuserbll.StockUserAdd(stockusers);

          }

          Server.Transfer("StockTableList.aspx");

        }

    }

分页：

前台页面：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="StockTableList.aspx.cs" Inherits="BioErpWeb.StockSystem.StockTableList" %>

<%@ Register assembly="AspNetPager" namespace="Wuqi.Webdiyer" tagprefix="webdiyer" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <link href="../Styles/ERPBaseStyle.css" rel="stylesheet" type="text/css" />

    <link href="../Styles/AspNetPagerStyle.css" rel="stylesheet" type="text/css" />

</head>

<body>

    <form id="form1" runat="server">

    <div>

       <table class="maintable">

         <tr>

             <td class="titlebar">

                 <span>仓库信息管理系统</span>

             </td>

         </tr>

         <tr>

         <td>

          <table style=" width:790px;">

              <tr>

                  <td>

                      <span>仓库名：</span><asp:TextBox ID="txtStotckName" runat="server" Width="100px"></asp:TextBox>

                  </td>

                  <td>

                      <span>仓库地址： </span>

                      <asp:TextBox ID="txtAddress" runat="server" Width="100px"></asp:TextBox>

                  </td>

                  <td>

                      <span>所属公司：</span><asp:DropDownList ID="ddlCompany" runat="server">

                      </asp:DropDownList>

                  </td>

                  <td>

                      <span>状态：</span><asp:DropDownList ID="ddlState" runat="server">

                          <asp:ListItem Value="1">正常</asp:ListItem>

                          <asp:ListItem Value="0">停用</asp:ListItem>

                      </asp:DropDownList>

                  </td>

                  <td>

                      <asp:Button ID="btnSearch" Width="100px" runat="server" Text="搜索" onclick="btnSearch_Click" />

                  </td>

              </tr>

          </table>

         </td>

         </tr>

         <tr>

             <td>

                 <asp:GridView ID="GridView1"  AutoGenerateColumns="False" Width="800px"

                     runat="server" onrowcommand="GridView1_RowCommand">

                     <Columns>

                         <asp:TemplateField HeaderText="编号">

                             <ItemTemplate>

                                 <asp:Label ID="Label1" runat="server" Text='<%# Eval("ID") %>'></asp:Label>

                             </ItemTemplate>

                         </asp:TemplateField>

                         <asp:TemplateField HeaderText="仓库名">

                             <ItemTemplate>

                                 <asp:Label ID="Label2" runat="server" Text='<%# Eval("StockName") %>'></asp:Label>

                             </ItemTemplate>

                         </asp:TemplateField>

                         <asp:TemplateField HeaderText="地址">

                             <ItemTemplate>

                                 <asp:Label ID="Label3" runat="server" Text='<%# Eval("StockAddress") %>'></asp:Label>

                             </ItemTemplate>

                         </asp:TemplateField>

                         <asp:TemplateField HeaderText="所属公司">

                             <ItemTemplate>

                                 <asp:Label ID="Label4" runat="server" Text='<%# Eval("FarhterCompany") %>'></asp:Label>

                             </ItemTemplate>

                         </asp:TemplateField>

                         <asp:TemplateField HeaderText="管理员">

                             <ItemTemplate>

                                 <asp:Label ID="Label5" runat="server" Text='<%# Eval("UserNames") %>'></asp:Label>

                             </ItemTemplate>

                         </asp:TemplateField>

                         <asp:TemplateField HeaderText="操作">

                           <ItemTemplate>

                                       <asp:ImageButton ID="imgEditBtn" Width="50" Height="20" CommandName="imgEdit" CommandArgument='<%#Eval("ID") %>' CausesValidation="false" ImageUrl="~/Web/images/Edit.gif" runat="server" />                                     

                           </ItemTemplate>

                            <ItemStyle HorizontalAlign="Center"  Width="120px"/>

                         </asp:TemplateField>

                     </Columns>

                 </asp:GridView>

             </td>

         </tr>

         <tr >

             <td>

                 <webdiyer:AspNetPager ID="AspNetPager1" CssClass="paginator"

                     CurrentPageButtonClass="cpb" runat="server"

                     onpagechanged="AspNetPager1_PageChanged">

                 </webdiyer:AspNetPager>

             </td>

         </tr>

     </table>

    </div>

    </form>

</body>

</html>

分页及查询的后台：

public partial class StockTableList : System.Web.UI.Page

    {

        static string Condition = "";

        static int pageindex=0;

        static int pagesize = 8;

        CompanyTableBll companybll = new CompanyTableBll();

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                this.AspNetPager1.RecordCount = SqlComm.getDataCountByCondition("View_StockList", Condition);

                this.AspNetPager1.PageSize = pagesize;

                getCompanyList();

                getStockList();

            }

        }

        private void getCompanyList()

        {

            this.ddlCompany.DataSource = companybll.GetCompanyList();

            this.ddlCompany.DataTextField = "CompanyName";

            this.ddlCompany.DataValueField = "ID";

            this.ddlCompany.DataBind();

            this.ddlCompany.Items.Add(new ListItem("--请选择--", "0"));

            this.ddlCompany.SelectedValue = "0";

        }

        /// <summary>

        /// 获取仓库信息列表

        /// </summary>

        private void getStockList()

        {

          this.GridView1.DataSource=  SqlComm.getDataByPageIndex("View_StockList", "ID,StockName,FarhterCompany,StockAddress,IsDel,UserNames", "ID",Condition, pageindex, pagesize);

          this.GridView1.DataBind();

        }

        protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

        {

            if (e.CommandName == "imgEdit")

            {

                string id = e.CommandArgument.ToString();

                Server.Transfer("StockEdit.aspx?stockid=" + id);

            }

        }

        protected void btnSearch_Click(object sender, EventArgs e)

        {

            Condition = "";

            if (this.txtStotckName.Text != "")

            {

                Condition = " and StockName like '" + this.txtStotckName.Text + "%' ";

            }

            if (this.txtAddress.Text != "")

            {

                Condition = " and StockAddress like '" + this.txtAddress.Text + "%' ";

            }

            if (this.ddlCompany.SelectedValue != "0")

            {

                Condition = " and FarhterCompany='" + this.ddlCompany.SelectedItem.Text + "' ";

            }

            if (this.ddlState.SelectedValue == "0")

            {

                Condition = " and IsDel='True'  ";

            }

            else {

                Condition = " and IsDel='False'  ";

            }

            getStockList();

        }

        protected void AspNetPager1_PageChanged(object sender, EventArgs e)

        {

            pageindex = this.AspNetPager1.CurrentPageIndex - 1;

            getStockList();

        }

    }

相关文章