根据任务表编号，及相关表单编号获取当前流程表的编号：
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=" 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);
}
}