首页 > 代码库 > ERP合同管理流程查询（三）
ERP合同管理流程查询（三）
根据任务表编号，及相关表单编号获取当前流程表的编号：
CREATE FUNCTION [dbo].[FN_GetDynamicId] ( @tasktableid INT, @taskid INT)RETURNS INT ASBEGIN DECLARE @listid INT SELECT TOP 1 @listid=ListID FROM TaskListRecord WHERE TaskTableID=@tasktableid AND TaskID=@taskid ORDER BY ListID DESC RETURN @listidENDGO
根据用户的编号返回用户姓名：
CREATE FUNCTION [dbo].[FN_GetUserNameByID]( @UserID INT)RETURNS NVARCHAR(20) ASBEGIN DECLARE @UserName NVARCHAR(20) SELECT @UserName=UserName FROM dbo.UserManager WHERE UserId=@UserID RETURN @UserNameEND
根据当前流程表返回当前任务提者：
CREATE FUNCTION [dbo].[FN_CurrentTransmitter]( @TaskTableID INT, @taskid INT )RETURNS NVARCHAR(20) ASBEGIN 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 @UserNameEND
获取当前任务的所属部门：
CREATE FUNCTION [dbo].[getDepartMentByTaskListRecord]( @TaskTableID INT, @TaskID INT)RETURNS NVARCHAR(50)ASBEGIN 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 @departMentEND
创建视图：
CREATE VIEW [dbo].[View_ContractShowList]ASSELECT 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)ASBEGIN DECLARE @CustomerName NVARCHAR(100) SELECT @CustomerName=CustomerName FROM dbo.BioCrmCustomer WHERE CustomerID=@ID -- Return the result of the function RETURN @CustomerNameEND
前端界面：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CRMContractListShow.aspx.cs" Inherits="BioErpWeb.CRMSystem.CRMContract.CRMContractListShow" %><%@ Register assembly="AspNetPager" namespace="Wuqi.Webdiyer" tagprefix="webdiyer" %><%@ Register src="http://www.mamicode.com/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="http://www.mamicode.com/Styles/ERPBaseStyle.css" rel="stylesheet" type="text/css" /> <link href="http://www.mamicode.com/Styles/AspNetPagerStyle.css" rel="stylesheet" type="text/css" /> <script src="http://www.mamicode.com/Scripts/jquery-1.4.1.js" type="text/javascript"></script> <link href="http://www.mamicode.com/Scripts/jquery-ui-1.7.custom.css" rel="stylesheet" type="text/css" /> <script src="http://www.mamicode.com/Scripts/jquery-ui-1.7.custom.min.js" type="text/javascript"></script> <link href="http://www.mamicode.com/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="http://www.mamicode.com/0">否</asp:ListItem> <asp:ListItem Value="http://www.mamicode.com/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="http://www.mamicode.com/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 =http://www.mamicode.com/="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); } }
ERP合同管理流程查询（三）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。