ERP完善合同起草（二十八）
前端的代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CRMContractNew.aspx.cs" Inherits="BioErpWeb.CRMSystem.CRMContract.CRMContractNew" %>
<!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" />
<script src="../../JS/CheckUserName.js" type="text/javascript"></script>
<script src="../../Scripts/jquery-1.4.1.js" type="text/javascript"></script>
<script src="../../Scripts/jquery.validate.js" type="text/javascript"></script>
<script src="../../Scripts/validateExtender.js" type="text/javascript"></script>
<script src="../../Scripts/ValidateMessage_ZW.js" type="text/javascript"></script>
<script src="../../Scripts/jquery.metadata.js" type="text/javascript"></script>
<script src="../../JS/CustomerName.js" type="text/javascript"></script>
<link href="../../Styles/InputStyle1.css" rel="stylesheet" type="text/css" />
<script src="../../JS/CheckDepartMent.js" type="text/javascript"></script>
<script src="../../Scripts/jquery-ui-1.7.custom.min.js" type="text/javascript"></script>
<link href="../../Scripts/jquery-ui-1.7.custom.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
$(document).ready(function () {
$("#form1").validate();
$("#txtCreatTime").datepicker({ dateFormat: 'yy-mm-dd' });
$("#txtSinTime").datepicker({ dateFormat: 'yy-mm-dd' });
$("#txtEffectiveTime").datepicker({ dateFormat: 'yy-mm-dd' });
$("#txtEndTime").datepicker({ dateFormat: 'yy-mm-dd' })
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table class="maintable Inupttable" style=" width:900px;">
<tr>
<td colspan="8" class="titlebar">合同起草</td>
</tr>
<tr>
<td>合同名称</td><td><asp:TextBox ID="txtName" CssClass="required" runat="server"></asp:TextBox></td>
<td>下次审批人</td><td><asp:TextBox ID="txtUser1" CssClass="{required:true,digits:true, min:1}" runat="server"></asp:TextBox>
<input id="btnuser1" type="button" value="选择" onclick="showDialog3()" class="btnchoose"/>
</td>
<td>合同负责人</td><td><asp:TextBox ID="txtUser2" CssClass="{required:true,digits:true, min:1}" runat="server"></asp:TextBox>
<input id="btnUser2" type="button" value="选择" onclick="showDialog3()" class="btnchoose"/>
</td>
<td>所属部门</td><td>
<asp:TextBox ID="txtDepartMentID" CssClass="{required:true,digits:true, min:1}" runat="server"></asp:TextBox>
<input id="btnDepart" type="button" value="选择" class="btnchoose" onclick="showDepartMentDialog2()" /></td>
</tr>
<tr>
<td>签约客户</td><td><asp:TextBox ID="txtCustomer" CssClass="{required:true,digits:true, min:1}" runat="server"></asp:TextBox>
<input id="btncustomer" type="button" value="选择" onclick="showCustomerDialog3()" class="btnchoose"/>
</td>
<td>签约金额</td><td><asp:TextBox ID="txtSum" CssClass="{required:true,number:true}" runat="server"></asp:TextBox>元（RMB）</td>
<td>合同创建时间</td><td><asp:TextBox ID="txtCreatTime" CssClass="{required:true,dateISO:true}" runat="server"></asp:TextBox></td>
<td>计划签约时间</td><td><asp:TextBox ID="txtSinTime" CssClass="{required:true,dateISO:true}" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>生效时间</td><td ><asp:TextBox ID="txtEffectiveTime" CssClass="{required:true,dateISO:true}" runat="server"></asp:TextBox></td>
<td>截止</td><td colspan="5"><asp:TextBox ID="txtEndTime" CssClass="{required:true,dateISO:true}" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>合同内容</td><td colspan="7">
<asp:TextBox ID="txtDesc" CssClass="required"
runat="server" Height="376px" Width="100%" Rows="10" TextMode="MultiLine"></asp:TextBox></td>
</tr>
<tr>
<td>合同附件</td><td colspan="5">
<asp:FileUpload ID="FileUpload1" runat="server" Width="200px" />
</td>
<td>合同类型</td><td>
<asp:DropDownList ID="ddlType" runat="server">
<asp:ListItem>买卖合同</asp:ListItem>
<asp:ListItem>运输合同</asp:ListItem>
<asp:ListItem>技术合同</asp:ListItem>
<asp:ListItem>保管合同</asp:ListItem>
<asp:ListItem>仓储合同</asp:ListItem>
<asp:ListItem>委托合同</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td colspan="8" class="bottomtd">
<asp:Button ID="btnSubmit" runat="server" Text="合同申请提交"
onclick="btnSubmit_Click" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
创建触发器：
CREATE TRIGGER [dbo].[CreateContractNoByContractID] ON [dbo].[BioCRMContract] AFTER INSERT AS BEGIN DECLARE @str nvarchar(10) DECLARE @ContractNo nvarchar(50) DECLARE @ContractID nvarchar(50) SET @str='0000000' SELECT @ContractID=MAX(ContractID) FROM dbo.BioCRMContract SET @ContractNo=RIGHT(@str+@ContractID,7) UPDATE dbo.BioCRMContract SET ContractNumber=@ContractNo WHERE ContractID=@ContractID end
后端的代码：
提交不成功删除原有的数据
protected void btnSubmit_Click(object sender, EventArgs e)
{
CrmContractBLL conbll = new CrmContractBLL();
BioCRMContract contract = new BioCRMContract();
CRMContractDocument condocument =null;
TaskListRecord tasklistRecord =null;
BioCrmCorrelationDocument document=null;
//获取合同相关信息
contract.UserID = int.Parse(this.txtUser2.Text);
contract.ContractName = this.txtName.Text;
contract.CustomerID = int.Parse(this.txtCustomer.Text);
contract.ContractSum = decimal.Parse(this.txtSum.Text);
contract.ContractType = this.ddlType.SelectedValue;
contract.EndTime = Convert.ToDateTime(this.txtEndTime.Text);
contract.EffectiveTime = Convert.ToDateTime(this.txtEffectiveTime.Text);
contract.SignTime = Convert.ToDateTime(this.txtSinTime.Text);
contract.ContractDesc = this.txtDesc.Text;
contract.ExecutiveState = "待审核";
contract.CreateTime = Convert.ToDateTime(this.txtCreatTime.Text);
int ContractID = conbll.BioCRMContractadd(contract);
if (ContractID != 0)
{
//文档基本信息
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();
int documentid = documentbll.BioCrmCorrelationDocumentAdd(document);
if (documentid != 0)
{
condocument = new CRMContractDocument()
{
DocumentID = documentid,
ContractID = ContractID
};
int count = conbll.CRMContractDocumentADD(condocument);
if (count != 0)
{
tasklistRecord = new TaskListRecord()
{
Accepter = int.Parse(this.txtUser1.Text),
Transmitter = int.Parse(this.txtUser2.Text),
AuditingSate = 0,
DepartMentId = int.Parse(this.txtDepartMentID.Text),
FirstAccepter = int.Parse(this.txtUser1.Text),
FirstSumitTime = DateTime.Now,
FirstTransmitter = int.Parse(Session["Userid"].ToString()),
Mind = "",
Pass = 1,
Subject = this.txtName.Text,
SumitTime = DateTime.Now,
TaskID = ContractID,
TaskTableID = 1
};
if (SqlComm.TaskListRecordAdd(tasklistRecord) != 0)
{
ClientScript.RegisterStartupScript(this.GetType(), "test", "alert('合同申请提交成功,下次审批人员编号：" + this.txtUser1.Text + "')", true);
}
else
{
SqlComm.DeleteTableByCondition("BioErpCRMContractDocument", " where ContractID=" + ContractID);
SqlComm.DeleteTableByCondition("BioCrmCorrelationDocument", " where DocumentID=" + documentid);
SqlComm.DeleteTableByCondition("BioCRMContract", " where ContractID=" + ContractID);
}
}
else {
SqlComm.DeleteTableByCondition("BioErpCRMContractDocument", " where ContractID=" + ContractID);
SqlComm.DeleteTableByCondition("BioCrmCorrelationDocument", " where DocumentID=" + documentid);
}
}
else
{
SqlComm.DeleteTableByCondition("BioCRMContract", " where ContractID=" + ContractID);
}
}
}
修改的业务（前端）：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CRMContractEidt.aspx.cs" Inherits="BioErpWeb.CRMSystem.CRMContract.CRMContractEidt" %>
<!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" />
<script src="../../JS/CheckUserName.js" type="text/javascript"></script>
<script src="../../Scripts/jquery-1.4.1.js" type="text/javascript"></script>
<script src="../../Scripts/jquery.validate.js" type="text/javascript"></script>
<script src="../../Scripts/validateExtender.js" type="text/javascript"></script>
<script src="../../Scripts/ValidateMessage_ZW.js" type="text/javascript"></script>
<script src="../../Scripts/jquery.metadata.js" type="text/javascript"></script>
<script src="../../JS/CustomerName.js" type="text/javascript"></script>
<link href="../../Styles/InputStyle1.css" rel="stylesheet" type="text/css" />
<script src="../../JS/CheckDepartMent.js" type="text/javascript"></script>
<script src="../../Scripts/jquery-ui-1.7.custom.min.js" type="text/javascript"></script>
<link href="../../Scripts/jquery-ui-1.7.custom.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
$(document).ready(function () {
$("#form1").validate();
$("#txtCreatTime").datepicker({ dateFormat: 'yy-mm-dd' });
$("#txtSinTime").datepicker({ dateFormat: 'yy-mm-dd' });
$("#txtEffectiveTime").datepicker({ dateFormat: 'yy-mm-dd' });
$("#txtEndTime").datepicker({ dateFormat: 'yy-mm-dd' })
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table class="maintable Inupttable" style=" width:900px;">
<tr>
<td colspan="8" class="titlebar">合同审核</td>
</tr>
<tr>
<td>合同名称</td><td><asp:TextBox ID="txtName" CssClass="required" runat="server"></asp:TextBox></td>
<td>下次审批人</td><td><asp:TextBox ID="txtUser1" CssClass="{required:true,digits:true, min:1}" runat="server"></asp:TextBox>
<input id="btnuser1" type="button" value="选择" onclick="showDialog3()" class="btnchoose"/>
</td>
<td>合同负责人</td><td><asp:TextBox ID="txtUser2" CssClass="{required:true,digits:true, min:1}" runat="server"></asp:TextBox>
<input id="btnUser2" type="button" value="选择" onclick="showDialog3()" class="btnchoose"/>
</td>
<td>所属部门</td><td>
<asp:TextBox ID="txtDepartMentID" CssClass="{required:true,digits:true, min:1}" runat="server"></asp:TextBox>
<input id="btnDepart" type="button" value="选择" class="btnchoose" onclick="showDepartMentDialog2()" /></td>
</tr>
<tr>
<td>签约客户</td><td><asp:TextBox ID="txtCustomer" CssClass="{required:true,digits:true, min:1}" runat="server"></asp:TextBox>
<input id="btncustomer" type="button" value="选择" onclick="showCustomerDialog3()" class="btnchoose"/>
</td>
<td>签约金额</td><td><asp:TextBox ID="txtSum" CssClass="{required:true,number:true}" runat="server"></asp:TextBox>元（RMB）</td>
<td>合同创建时间</td><td><asp:TextBox ID="txtCreatTime" CssClass="{required:true,dateISO:true}" runat="server"></asp:TextBox></td>
<td>计划签约时间</td><td><asp:TextBox ID="txtSinTime" CssClass="{required:true,dateISO:true}" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>生效时间</td><td ><asp:TextBox ID="txtEffectiveTime" CssClass="{required:true,dateISO:true}" runat="server"></asp:TextBox></td>
<td>截止</td><td colspan="5"><asp:TextBox ID="txtEndTime" CssClass="{required:true,dateISO:true}" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>合同内容</td><td colspan="7">
<asp:TextBox ID="txtDesc" CssClass="required"
runat="server" Height="300px" Width="100%" Rows="10" TextMode="MultiLine"></asp:TextBox></td>
</tr>
<tr>
<td>合同附件</td><td colspan="5">
<asp:FileUpload ID="FileUpload1" runat="server" Width="200px" />
</td>
<td>合同类型</td><td>
<asp:DropDownList ID="ddlType" runat="server">
<asp:ListItem>买卖合同</asp:ListItem>
<asp:ListItem>运输合同</asp:ListItem>
<asp:ListItem>技术合同</asp:ListItem>
<asp:ListItem>保管合同</asp:ListItem>
<asp:ListItem>仓储合同</asp:ListItem>
<asp:ListItem>委托合同</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td colspan="8" class="bottomtd">
<asp:Button ID="btnSubmit" runat="server" Text="合同申请提交"
onclick="btnSubmit_Click" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
定义相关的视图：根据合同编号获取合同的视图
CREATE VIEW [dbo].[View_ContactInfo]
AS
SELECT dbo.BioCRMContract.*, dbo.BioErpCRMContractDocument.AccessoryID, dbo.BioCrmCorrelationDocument.DocumentID, dbo.BioCrmCorrelationDocument.Subject,
dbo.BioCrmCorrelationDocument.Name, dbo.BioCrmCorrelationDocument.Type, dbo.BioCrmCorrelationDocument.DocumentLevel,
dbo.BioCrmCorrelationDocument.DocumentSize, dbo.BioCrmCorrelationDocument.ExetendName, dbo.BioCrmCorrelationDocument.[Content],
dbo.BioCrmCorrelationDocument.UploadTime, dbo.BioCrmCorrelationDocument.Remark
FROM dbo.BioCRMContract Left JOIN
dbo.BioErpCRMContractDocument ON dbo.BioCRMContract.ContractID = dbo.BioErpCRMContractDocument.ContractID Left JOIN
BLL层代码：
/// <summary>
/// 根据合同通编号获取合同视图实体对象
/// </summary>
/// <param name="id">编号</param>
/// <returns>ViewContactInfo</returns>
public ViewContactInfo getViewContactInfoByContactID(string id)
{
ViewContactInfo contactinfo= null;
SqlDataReader reader= SqlComm.GetDataReaderByCondition("View_ContactInfo", "*", " ContractID=" + id);
if (reader.Read())
{
contactinfo = new ViewContactInfo()
{
UserID = Convert.ToInt32(reader["UserID"].ToString()),
AccessoryID = Convert.ToInt32(reader["AccessoryID"].ToString()),
AssessorAuditing = Convert.ToBoolean(reader["AssessorAuditing"].ToString()),
ContractDesc = reader["ContractDesc"].ToString(),
ContractID = Convert.ToInt32(reader["ContractID"].ToString()),
ContractName = reader["ContractName"].ToString(),
ContractNumber = reader["ContractNumber"].ToString(),
ContractSum = Decimal.Parse(reader["ContractSum"].ToString()),
ContractType = reader["ContractType"].ToString(),
CreateTime = Convert.ToDateTime(reader["CreateTime"].ToString()),
CustomerID = Convert.ToInt32(reader["CustomerID"].ToString()),
DeleteState = Convert.ToBoolean(reader["DeleteState"].ToString()),
DocumentID = Convert.ToInt32(reader["DocumentID"].ToString()),
DocumentLevel = reader["DocumentLevel"].ToString(),
DocumentSize = long.Parse(reader["DocumentSize"].ToString()),
EndTime = Convert.ToDateTime(reader["EndTime"].ToString()),
EffectiveTime = Convert.ToDateTime(reader["EffectiveTime"].ToString()),
UploadTime = Convert.ToDateTime(reader["UploadTime"].ToString()),
ExecutiveState = reader["ExecutiveState"].ToString(),
ExetendName = reader["ExetendName"].ToString(),
Name = reader["Name"].ToString(),
Remark = reader["Remark"].ToString(),
SignTime = Convert.ToDateTime(reader["SignTime"].ToString()),
Subject = reader["Subject"].ToString(),
Type = reader["Type"].ToString()
};
}
reader.Close();
return contactinfo;
}
根据任务表单编号和任务表编号查询返回数据表
/// <summary>
/// 根据任务表单编号，和 任务表编号进行查询返回数据表
/// </summary>
/// <param name="TaskID">TaskID：如合同管理单号为5</param>
/// <param name="TaskTableID">TaskTableID：如合同管理单（1）、商品供货单（2）、商品采购申请单（3）</param>
/// <returns></returns>
public static DataTable getTaskListRecordsByCondition(string TaskID, string TaskTableID)
{
SqlParameter[] pars = new SqlParameter[]{
new SqlParameter("@tableName","TaskListRecord"),
new SqlParameter("@columns"," * "),
new SqlParameter("@condition"," TaskID='"+TaskID+"' and TaskTableID='"+TaskTableID+"'")
};
return DataBaseHelper.SelectSQLReturnTable("GetDataByCondition", CommandType.StoredProcedure, pars);
}
存储过程：
-- Description: 根据指定列，指定条件，指定表查询数据
-- =============================================
CREATE 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
