根据任务表编号，及相关表单编号获取当前流程表的编号：
CREATE FUNCTION [dbo].[FN_GetDynamicId]
(
@tasktableid INT,
@taskid INT
)
RETURNS INT
AS
BEGIN DECLARE @listid INT SELECT TOP 1 @listid=ListID FROM TaskListRecord
WHERE TaskTableID=@tasktableid AND TaskID=@taskid
ORDER BY ListID DESC RETURN @listid
END
GO
根据用户的编号返回用户姓名：
CREATE FUNCTION [dbo].[FN_GetUserNameByID]
(
@UserID INT
)
RETURNS NVARCHAR(20)
AS
BEGIN DECLARE @UserName NVARCHAR(20)
SELECT @UserName=UserName FROM dbo.UserManager
WHERE UserId=@UserID
RETURN @UserName END
根据当前流程表返回当前任务提者：
CREATE FUNCTION [dbo].[FN_CurrentTransmitter]
(
@TaskTableID INT,
@taskid INT )
RETURNS NVARCHAR(20)
AS
BEGIN DECLARE @UserName NVARCHAR(20)
SELECT TOP 1 @UserName=dbo.FN_GetUserNameByID(Transmitter) FROM dbo.TaskListRecord
WHERE TaskTableID=@TaskTableID AND TaskID=@taskid
ORDER BY ListID DESC RETURN @UserName
END
获取当前任务的所属部门：
CREATE FUNCTION [dbo].[getDepartMentByTaskListRecord]
(
@TaskTableID INT,
@TaskID INT
)
RETURNS NVARCHAR(50)
AS
BEGIN DECLARE @departMent NVARCHAR(50)
SELECT TOP 1 @departMent =dbo.FN_GetDepartMentByID(DepartMentId)
FROM dbo.TaskListRecord WHERE TaskID=@TaskID AND TaskTableID=@TaskTableID
ORDER BY ListID DESC RETURN @departMent END
创建视图：
CREATE VIEW [dbo].[View_ContractShowList]
AS
SELECT
ContractID,
ContractName,
ContractNumber,
CustomerID,
CustomerName=dbo.getCustomerByID(CustomerID),
CreateTime,
ContractSum,
SignTime,
EffectiveTime,
EndTime,
ContractType,
UserID,
UserName=dbo.getUserNameByUserID(UserID),
ExecutiveState,
ContractDesc,
AssessorAuditing,
DeleteState,
Transmitter=ISNULL(dbo.FN_CurrentTransmitter(1,ContractID),'数据错误'),
Listid= ISNULL(dbo.FN_GetDynamicId(1,ContractID),0),
AuditingSate=ISNULL(dbo.FN_CurrentAuditingSate(1,ContractID),0),
DepartMent=ISNULL(dbo.getDepartMentByTaskListRecord(1,ContractID),'数据错误') FROM
BioCRMContract
根据客户编号返回客户信息：
CREATE FUNCTION [dbo].[getCustomerByID]
(
@ID INT
)
RETURNS NVARCHAR(100)
AS
BEGIN DECLARE @CustomerName NVARCHAR(100)
SELECT @CustomerName=CustomerName FROM dbo.BioCrmCustomer
WHERE CustomerID=@ID
-- Return the result of the function
RETURN @CustomerName
END
前端界面：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CRMContractListShow.aspx.cs" Inherits="BioErpWeb.CRMSystem.CRMContract.CRMContractListShow" %> <%@ Register assembly="AspNetPager" namespace="Wuqi.Webdiyer" tagprefix="webdiyer" %> <%@ Register src="../../UserControl/CRMChannelMenuBar.ascx" tagname="CRMChannelMenuBar" tagprefix="uc1" %> <!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" />
<script src="../../Scripts/jquery-1.4.1.js" type="text/javascript"></script>
<link href="../../Scripts/jquery-ui-1.7.custom.css" rel="stylesheet" type="text/css" />
<script src="../../Scripts/jquery-ui-1.7.custom.min.js" type="text/javascript"></script>
<link href="../../Styles/TopMenu2.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
$().ready(function () {
$("#txtNoteTime").datepicker({ dateFormat: 'yy-mm-dd' });
});
</script> </head>
<body>
<form id="form1" runat="server"> <div> </div>
<div>
<table class="maintable" style=" width:900px;">
<tr>
<td colspan="5" class="titlebar">
<span>合同审核列表查询</span>
</td>
</tr>
<tr>
<td class="tdsearch">
<asp:Label ID="Label1" runat="server" Text="合同名称："></asp:Label>
<asp:TextBox ID="txtName" runat="server" Width="100px"></asp:TextBox>
</td>
<td class="tdsearch">
<asp:Label ID="Label2" runat="server" Text="合同负责人："></asp:Label>
<asp:TextBox ID="txtUserName" runat="server" Width="100px"></asp:TextBox>
</td>
<td class="tdsearch">
<asp:Label ID="Label11" runat="server" Text="提交时间"></asp:Label>
<asp:TextBox ID="txtNoteTime" runat="server" Width="100px"></asp:TextBox>
</td>
<td class="tdsearch">
<asp:Label ID="Label3" runat="server" Text="是否删除"></asp:Label>
<asp:DropDownList ID="ddlState" runat="server">
<asp:ListItem Value="0">否</asp:ListItem>
<asp:ListItem Value="1">是</asp:ListItem>
</asp:DropDownList>
</td> <td class="tdsearch">
<asp:ImageButton ID="imgbutnSearch" Width="60" Height="22" runat="server"
ImageUrl="~/Web/images/Btnsearch.gif" onclick="imgbutnSearch_Click" />
</td>
</tr>
<tr>
<td colspan="6" class="bottomtd">
<asp:GridView ID="GridView1" Width="100%" runat="server" AutoGenerateColumns="False" DataKeyNames="ContractID">
<Columns>
<asp:TemplateField HeaderText="合同编号" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Eval("ContractNumber") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="合同名称" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate> <asp:Label ID="Label5" runat="server" Text='<%# Eval("ContractName") %>'></asp:Label>
</ItemTemplate>
<ItemStyle Width="120px" HorizontalAlign="Center" />
<HeaderStyle HorizontalAlign="Center"></HeaderStyle> <ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:BoundField DataField="AuditingSate" HeaderText="状态" HeaderStyle-HorizontalAlign="Center"/> <asp:TemplateField HeaderText="合同负责人" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Eval("UserName") %>'></asp:Label>
</ItemTemplate> <HeaderStyle HorizontalAlign="Center"></HeaderStyle> <ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="创建时间" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%# Convert.ToDateTime(Eval("CreateTime")).ToString("yyyy-MM-dd") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField> <asp:TemplateField HeaderText="所属部门" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label9" runat="server" Text='<%# Eval("DepartMent") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField> <asp:TemplateField HeaderText="送达人" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label10" runat="server" Text='<%#Eval("Transmitter").ToString()%>'></asp:Label>
</ItemTemplate> <HeaderStyle HorizontalAlign="Center"></HeaderStyle> <ItemStyle HorizontalAlign="Center" />
</asp:TemplateField> <asp:TemplateField HeaderText="" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<a href="CRMContractShow.aspx?taskid=<%#Eval("ContractID") %>&listid=<%#Eval("Listid")%>">查看详细</a>
</ItemTemplate> <HeaderStyle HorizontalAlign="Center"></HeaderStyle> <ItemStyle HorizontalAlign="Center" />
</asp:TemplateField> </Columns>
</asp:GridView>
</td>
</tr>
<tr>
<td colspan="5">
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" CssClass="paginator" CurrentPageButtonClass="cpb"
onpagechanged="AspNetPager1_PageChanged">
</webdiyer:AspNetPager>
</td>
</tr> </table> </div>
</form>
</body>
</html>
后台代码：
public static int pageindex = 0;
public static int pagesize = 10;
public static string condition = ""; protected void Page_Load(object sender, EventArgs e)
{
//Session["Userid"] = "29";
if (Session["Userid"] == null)
{
Response.Redirect("../../web/UserLogin.aspx");
} if (!IsPostBack)
{ getallBioCRMContractList();
}
} /// <summary>
/// 查询所有员工信息
/// </summary>
private void getallBioCRMContractList()
{
//如果是市场部经理，或客服部经理可以查看所有
if (SqlComm.getUserRightsByUserId(Session["Userid"].ToString()).Contains(",44,"))
{
this.AspNetPager1.RecordCount = SqlComm.getDataCountByCondition("dbo.View_ContractShowList", condition);
this.AspNetPager1.PageSize = pagesize;
this.GridView1.DataSource = SqlComm.getDataByPageIndex("dbo.View_ContractShowList", "*", "ContractID", condition, pageindex, pagesize);
this.GridView1.DataBind();
}
else //员工只能看自己的客户信息
{
condition = condition + " and userid=" + Session["Userid"].ToString();
this.AspNetPager1.RecordCount = SqlComm.getDataCountByCondition("dbo.View_ContractShowList", condition);
this.AspNetPager1.PageSize = pagesize;
this.GridView1.DataSource = SqlComm.getDataByPageIndex("dbo.View_ContractShowList", "*", "ContractID", condition, pageindex, pagesize);
this.GridView1.DataBind();
} for (int i = 0; i < GridView1.Rows.Count; i++)
{
switch (GridView1.Rows[i].Cells[2].Text)
{
case "0":
GridView1.Rows[i].Cells[2].Text = "待审核";
GridView1.Rows[i].Cells[2].ForeColor = System.Drawing.Color.Green;
break;
case "2":
GridView1.Rows[i].Cells[2].Text = "执行中";
GridView1.Rows[i].Cells[2].ForeColor = System.Drawing.Color.Orange;
break;
case "3":
GridView1.Rows[i].Cells[2].Text = "已完成";
GridView1.Rows[i].Cells[2].ForeColor = System.Drawing.Color.Green;
break;
default:
GridView1.Rows[i].Cells[2].Text = "出现错误";
break;
} }
} protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
pageindex = this.AspNetPager1.CurrentPageIndex - 1;
getallBioCRMContractList();
} protected void imgbutnSearch_Click(object sender, ImageClickEventArgs e)
{
pageindex = 0;
condition = "";
if (txtName.Text.Trim() != null && this.txtName.Text.Trim().Length != 0)
{
condition = condition + " and ContractName like '" + txtName.Text + "%'";
} if (this.txtUserName.Text.Trim() != null && this.txtUserName.Text.Trim().Length != 0)
{
condition = condition + " and UserName like '" + txtUserName.Text + "%'";
} if (this.ddlState.SelectedValue == "1")
{
condition = condition + " and DeleteState ='True'";
}
else
{
condition = condition + " and DeleteState ='False'";
}
if (this.txtNoteTime.Text.Trim() != null && this.txtNoteTime.Text.Trim().Length != 0)
{
condition = condition + " and (CreateTime>= '" + this.txtNoteTime.Text + "' and CreateTime<'" + Convert.ToDateTime(this.txtNoteTime.Text).AddDays(1) + "')";
}
getallBioCRMContractList();
}
审核过程中可以上传合同附件（但必须是合同负责人本人上传）
int count = conbll.BioCRMContractaUpdate(contract);
//文档基本信息
if (this.FileUpload1.HasFile)
{
document = new BioCrmCorrelationDocument()
{
Subject = this.txtName.Text,
Content = this.FileUpload1.FileBytes,
DocumentSize =this.FileUpload1.FileContent.Length,
Type = this.FileUpload1.PostedFile.ContentType,
ExetendName = System.IO.Path.GetExtension(this.FileUpload1.FileName),
DocumentLevel = "绝密",
Name = this.FileUpload1.FileName,
Remark = "合同附件文档",
UserID = int.Parse(this.txtUser2.Text),
UploadTime = DateTime.Now
}; BioCrmCorrelationDocumentBLL documentbll = new BioCrmCorrelationDocumentBLL();
//页面加载时如果检查当前合同信息没有附件，此时则上传新的文件，即添加
if (documentsid == null)
{
int docid = documentbll.BioCrmCorrelationDocumentAdd(document);
if (docid != 0)
{
CRMContractDocument condocument = new CRMContractDocument()
{
DocumentID = docid,
ContractID = int.Parse(Request.QueryString["taskid"].ToString()) };
conbll.CRMContractDocumentADD(condocument);
}
}
else //则修改
{
document.DocumentID = int.Parse(documentsid.ToString());
documentbll.BioCrmCorrelationDocumentUpdate(document);
}
}