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