ERP仓库管理系统（九）

最新推荐文章于 2020-08-25 10:38:45 发布

转载最新推荐文章于 2020-08-25 10:38:45 发布 · 366 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/sunliyuan/p/5965513.html

文章标签：

#xhtml #ui #javascript #ViewUI

需求分析：

　　1.设计库房表，至少包括两个字段，库房名称，库房所属公司的ID（在客户资质审批表中找到对应公司的ID）

2.设计增、删、改、查一套程序，其中的删除要做限制，只要有库存数据存在则不允许删除对应库房的记录。

3.库房管理权限为高级权限，程序开发开始后，到配一个新的权限值。

新增的原理：

仓库管理员的新增和修改：

ALTER PROCEDURE [dbo].[BioErpStockUsers_ADD]
@StockID int,
@UserID int

 AS 
	INSERT INTO [BioErpStockUsers](
	[StockID],[UserID]
	)VALUES(
	@StockID,@UserID
	)

ALTER PROCEDURE [dbo].[BioErpStockUsers_Update]
@ID int,
@StockID int,
@UserID int
 AS 
	UPDATE [BioErpStockUsers] SET 
	[StockID] = @StockID,[UserID] = @UserID
	WHERE ID=@ID

仓库信息的新增和修改存储过程：

--修改：删除了LeaderUserID字段，新增加@ID输出参数
------------------------------------
ALTER PROCEDURE [dbo].[BioErpStockTable_ADD]
--输出的参数
@ID int output,
@StockName nvarchar(80),
@FarhterCompany int,
@StockAddress nvarchar(100),
@IsDel bit

 AS 
	INSERT INTO [BioErpStockTable](
	[StockName],[FarhterCompany],[StockAddress],[IsDel]
	)VALUES(
	@StockName,@FarhterCompany,@StockAddress,@IsDel
	)
	--赋值   @@为全局变量  获取当前表的最大表示列（与下面的是等效的）
 SET @ID=@@IDENTITY
-- SELECT @ID=MAX(ID) FROM BioErpStockTable

修改的存储过程：

ALTER PROCEDURE [dbo].[BioErpStockTable_Update]
@ID int,
@StockName nvarchar(80),
@FarhterCompany int,
@StockAddress nvarchar(100),
@IsDel bit
 AS 
	UPDATE [BioErpStockTable] SET 
	[StockName] = @StockName,[FarhterCompany] = @FarhterCompany,[StockAddress] = @StockAddress,[IsDel] = @IsDel
	WHERE ID=@ID

实现的效果图：

前台界面：

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

<%@ 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">
        $(document).ready(function () {

            var i = 0;
            $("#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 == '') 
                    {
                        alert("请选择第"+(j+1)+"行用户编号");
                        return false;
                    }

                }
                return true;

            });


        });


    </script>

</head>
<body>
    <form id="form1" runat="server">
    <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 class="bottomtd" colspan="2">
                 <asp:Button ID="btnSubmit" runat="server" Text="仓库信息登记" CssClass="submitbutton" OnClick="btnSubmit_Click" />
             </td>
         </tr>
     </table>
        <br />
    </div>
    </form>
</body>
</html>

选择员工的界面：

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

<!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>
    <script type="text/javascript">
        function search() {
            if (document.getElementById("txtSearchName").value == "") {
                alert("请输入员工姓名");
                return;
            }
            document.getElementById("PersonIframeList").src = "PersonListSelect.aspx?username=" + encodeURIComponent(document.getElementById("txtSearchName").value);

        }


        function choose() {
            window.returnValue = PersonIframeList.document.getElementById("sltUserList").value;
            window.close();
        }
    </script>
    <style type="text/css">
        .style1
        {
            width: 157px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     <table style=" width:310px; ">
         <tr>
             <td class="style1">员工姓名
             </td>
             <td>
                <input id="txtSearchName" name="txtSearchName" type="text" size="10"  maxlength="10"/> <input type="button" value="查询" onclick="search()" />
             </td>
         </tr>
         <tr>
             <td class="style1">
             <iframe width="120px" id="PersonIframeList" frameborder=0 src="PersonListSelect.aspx" height="310px" >
             
             </iframe>
             </td>
             <td style=" vertical-align:bottom; padding-bottom:20px;">
             <input type="button"  value="选择" onclick="choose()"/>
             </td>
         </tr>
     </table>
    </div>
    </form>
</body>
</html>

选择不同的员工显示不同的编号：

//人员选择对话框 
function showDialog() {
    var re = showModalDialog("../PersonList/PersonList.aspx", "", "dialogWidth=320px;dialogHeight=350px");
    //找到点击的事件
    var obj = window.event.srcElement;
    var tr = obj.parentNode.parentNode;
    var rowIndex = tr.rowIndex;
    var txts = tr.getElementsByTagName('input');
    
    if (re == null || re == "") 
    {

        if (txts[0].value == "" || txts[0].value == null) {
            txts[0].value = "请选择";

        }
    }
    else
     {
        txts[0].value = re;
    }
}

查询公司列表的BLL：

  public   class CompanyTableBll
    {
      /// <summary>
      /// 查询公司列表
      /// </summary>
      /// <returns>DataTable</returns>
      public DataTable GetCompanyList()
      {
        return   SqlComm.GetDataByTableNameValue("dbo.BioErpCompanyTable", "*").Tables[0];
      }
    }

添加和修改仓库管理员数据：

 public  class BioErpStockUsersBLL
    {

      /// <summary>
      /// 添加管理员数据
      /// </summary>
      /// <param name="stockuser"></param>
      /// <returns></returns>
      public int StockUserAdd(BioErpStockUsers stockuser)
      {
          SqlParameter[] parameters = {
					new SqlParameter("@StockID", SqlDbType.Int,4),
					new SqlParameter("@UserID", SqlDbType.Int,4)};
                    parameters[0].Value = stockuser.StockID;
                    parameters[1].Value = stockuser.UserID;
                    return DataBaseHelper.ExcuteSqlReturnInt("BioErpStockUsers_ADD", CommandType.StoredProcedure, parameters);
      
      }

      /// <summary>
      /// 根据ID修改仓库管理员信息
      /// </summary>
      /// <param name="stockuser"></param>
      /// <returns></returns>
      public int StockUserUpdeat(BioErpStockUsers stockuser)
      {
          SqlParameter[] parameters = {
                    new SqlParameter("@ID",SqlDbType.Int,4),
					new SqlParameter("@StockID", SqlDbType.Int,4),
					new SqlParameter("@UserID", SqlDbType.Int,4)};
          parameters[0].Value = stockuser.ID;
          parameters[1].Value = stockuser.StockID;
          parameters[2].Value = stockuser.UserID;
          return DataBaseHelper.ExcuteSqlReturnInt("BioErpStockUsers_Update", CommandType.StoredProcedure, parameters);

      }
    }

添加UI层后台的代码：

public partial class StockAdd : System.Web.UI.Page
    {
        CompanyTableBll companybll = new CompanyTableBll();
     
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                CompanyListBind();
            }
        }

        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";

        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            StockTable stock = new StockTable();
            stock.StockName = this.txtStockName.Text;
            stock.StockAddress = this.txtAddress.Text;
            stock.FarhterCompany = int.Parse(this.ddlCompany.SelectedValue.ToString());
            stock.IsDel = false;

            BioErpStockTableBLL stocktablebll = new BioErpStockTableBLL();
            int stockid= stocktablebll.StockTableAdd(stock);

            BioErpStockUsers stockusers = new BioErpStockUsers();
            BioErpStockUsersBLL stockusersbll = new BioErpStockUsersBLL();
            stockusers.StockID = stockid;
           string userids= Request["UserId"].ToString();
           string[] userarrayList = userids.Split(',');
           for (int i = 0; i < userarrayList.Length; i++)
           {
               stockusers.UserID = int.Parse(userarrayList[i].ToString());
               stockusersbll.StockUserAdd(stockusers);
           }

       Server.Transfer("StockTableList.aspx");

} }

游标的介绍：

Transact_SQL 游标是由DECLARE CURSOR 语法定义、主要用在Transact_SQL 脚本、存储过程和触发器中。Transact_SQL 游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL 语句或是批处理、存储过程、触发器中的Transact_SQL 进行管理。

查询实现的需求：

1.联合2个表查询，将返回结果绑定给GridView控件。

2.将员工编号对应的姓名组装成姓名字符串，显示在列表中。

游标的定义方式：

-- Description: <通过userid'2,331,332,333'等查询对应的用户名>
-- =============================================
--select dbo.[BioErpTbFN_GetUserNameListByID](',2,331,332,333,')

ALTER FUNCTION [dbo].[BioErpTbFN_GetUserNameListByID]
(@userID NVARCHAR(200))
RETURNS  NVARCHAR(1000)
AS
BEGIN
	declare cur cursor for select UserID,UserName from UserManager  where CHARINDEX(','+convert(NVARCHAR(10) ,id)+',',(','+@userID+','))>0

	open cur
	declare @id int
	declare @name NVARCHAR(50)
	declare @nn NVARCHAR(3000)
	set @nn=''
	fetch next from cur into @id, @name
	while @@fetch_status = 0
	begin
 	    set @nn=@nn+@name+';' 
        if len(@nn)>50
           begin
            set @nn=SUBSTRING(@nn,0,50)
            set @nn=@nn+'……'
            break
           end
              fetch next from cur into @id, @name
   end
	close cur
	DEALLOCATE cur
 RETURN @nn
END

游标的具体使用：

-- Description:	根据仓库编号返回员工姓名字符串
-- =============================================
ALTER FUNCTION [dbo].[GetUserListByStockID]
(  
    --传递的参数
	@StockID int
)
--返回的类型
RETURNS  nvarchar(200)
AS
BEGIN
	
	--定义游标
	declare cur cursor for 
	--子查询
	SELECT userName FROM UserManager WHERE UserManager.UserId IN ( SELECT  UserID	FROM BioErpStockUsers WHERE StockID=@StockID)
	--打开游标
	open cur
	--定义姓名的参数
	DECLARE @name nvarchar(20)
	--姓名组装后的字符串的参数
	DECLARE @names nvarchar(1000)
	--给变量设置值
	SET @names=''
	--从游标中取出数据下个数据放到变量中去
	FETCH next FROM cur INTO @name
	--取到了一行数据
	WHILE @@FETCH_STATUS =0
	--取到数据之后组装字符串
	BEGIN
	     --赋值
		SET @names=@names+@name+','
		--在去下一条
		FETCH next FROM cur INTO @name
	END
	--关闭游标
	 CLOSE cur
	 --回收游标
	 DEALLOCATE cur
	 --返回变量
	 RETURN @names
	 
END

--调用
select [dbo].[GetUserListByStockID](1)

结合实例的具体使用：

仓库信息列表的显示列：

<%@ 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" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
       <table class="maintable">
         <tr>
             <td class="titlebar">
                 <span>仓库信息管理系统</span>
             </td>
         </tr>
         <tr>
             <td>
           
                 <asp:GridView ID="GridView1"  AutoGenerateColumns="False" Width="800px" 
                     runat="server">
                     <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" />
                                       <asp:ImageButton ID="imgDeleteBtn" Width="50" Height="20" CommandName="imgDelete"  OnClientClick="return confirm('是否确定删除？');" CommandArgument='<%#Eval("ID") %>' ImageUrl="~/Web/images/Delete.gif" runat="server" />
                                    
                           </ItemTemplate>
                            <ItemStyle HorizontalAlign="Center"  Width="120px"/>
                         </asp:TemplateField>
                     </Columns>
                 </asp:GridView>
           
             </td>
         </tr>
        
         
         <tr >
             <td>
                 <webdiyer:AspNetPager ID="AspNetPager1" runat="server">
                 </webdiyer:AspNetPager>
             </td>
         </tr>
     </table>
    </div>
    </form>
</body>
</html>

把查询的数据放入视图中：

create View [View_StockList]
  as
  select
    ID,
	[FarhterCompany]=[dbo].[getCompanyNameByCompanyID](FarhterCompany),
	[StockAddress],
    [IsDel],
	'UserNames'=[dbo].[GetUserListByStockID](ID)
	from
	 [dbo].[BioErpStockTable]

对应的公司名称（标量函数）：

-- Description:	根据公司编号返回公司名称
-- =============================================
ALTER FUNCTION [dbo].[getCompanyNameByCompanyID] 
(
	@ID int
)
--返回的值
RETURNS nvarchar(100)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @companyname nvarchar(100)

	-- Add the T-SQL statements to compute the return value here
	SELECT @companyname=CompanyName FROM BioErpCompanyTable WHERE ID=@ID
	-- 返回变量
	RETURN @companyname

END

显示的代码：

 public partial class StockTableList : System.Web.UI.Page
    {
        static string Condition = "";
        static int pageindex=0;
        static int pagesize = 8;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                getStockList();
            }

        }

        /// <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();
        
        }
    }

转载于:https://www.cnblogs.com/sunliyuan/p/5965513.html