要点:外部数据里面有一个信用评价,然而这里面没有这个字段,我建了一个中间表B(nncq_TestingCompanyGrade)里面有通过NAME获取的一个评分数据。然后把外部表A(t_bp_custom)跟表B 建立视图(view_JCcompanyAndGrade)将评价跟得分链接起来,点击外部表的查看后,里面有两个部分,一个是上面的 机构信息,下面是机构评价信息(这里我建了一个表C(nncq_TestingCompanyPJ))里面主要是存储所有 有关传递进来NAME机构的 评价信息。
不足:就是我添加后,应该页面自动刷新获取最新分分数。添加评价记录的时候麻烦不简洁,更新的时候老是更新不了数据,我就先删除那条数据再重新添加。
经验:多写就会熟悉。
IF EXISTS (SELECT * FROM sysobjects WHERE /*检测是否存在*/
name = 'view_JCcompanyAndGrade')
DROP VIEW view_JCcompanyAndGrade /*删除视图*/
GO
CREATE VIEW view_JCcompanyAndGrade /*创建视图*/
AS
SELECT lefta.*,b.CreditGrade,b.Grade FROM dbo.t_bp_custom AS lefta
LEFT JOIN dbo.nncq_TestingCompanyGrade AS b ON lefta.NAME=b.CorpName;
GO
create table nncq_TestingCompanyPJ
(
ID uniqueidentifier primary key,--主键
CorpName varchar(50) not null,--公司名称跟前面B得分表里面的NAME关联
ActionCode varchar(50),--行为代码
NoAction varchar(50),--失信行为
KoufenBZ varchar(50),--扣分标准
KoufenQK int,--扣分情况
Beizhu varchar(50),--备注
SortCode varchar(50), --排序条件,我想去掉它不知道怎么搞
IsDel bit --是否删除
)
-- 检测机构评价得分 中间表 表B
create table nncq_TestingCompanyGrade
(
ID uniqueidentifier primary key,--主键
CorpName varchar(50) not null,--公司名称跟前面B得分表里面的NAME关联
Grade int,--分数
CreditGrade varchar(50), --信用登记
SortCode varchar(50), --排序条件,我想去掉它不知道怎么搞
IsDel bit --是否删除
)
js--------------------js------------------js--------------------js------------------js--------------------js------------------js-------------------
$(document).ready(function () {
var prorow;
var CorpName;
var table = $("#table-list").uTable({
url: "ITestingCompanyPJ",
page: {
pageSize: 20,
reqindex: "index",
reqsize: "size"
},
click: function (r) {
prorow = r;
},
heads: [
{ key: "area", name: "所属地区" },
{ key: "NAME", name: "所属机构名称" },
{ key: "ADDRESS", name: "机构地址" },
{ key: "STATIONID", name: "机构代码" },
{ key: "CreditGrade", name: "信用评价" },
{ html: '<a href="javascript:" class="read" data-id="{{ID}}" data-NAME="{{NAME}}">查看</a>', name: "操作" }
]
});
// 检测机构评价列表-->点击查看按钮
$("#table-list").on("click", ".read", function () {
pid = $(this).attr("data-id");//id
CorpName = $(this).attr("data-NAME");
EditDetail(pid,CorpName);
})
function EditDetail(pid,CorpName) {
$.layout.detail("VTestingCompanyPJ/EditDetail", function () {
$("#projinfo").detail({
data: prorow
});
$("#projectTab").utab({
click: function (i) {
}
});
//下面的得分问题
var grade = $("#table-GradeAndCreditGrade-list").uTable({
url: "ITestingCompanyPJ/CompanyGrade?CorpName=" + CorpName,
page: {
enable: false
},
heads: [
{ key: "Grade", name: "得分" },
{ key: "CreditGrade", name: "信用等级" },
]
});
//添加 机构评价信息
var regutable = $("#table-CompanyPJ-list").uTable({
url: "ITestingCompanyPJ/CompanyPJQk?CorpName=" + CorpName,
buttons: [
{
text: "新增", style: "btn-primary EQ-btn", click: function () {
RegUserEdit();
}
}
],
page: {
pageSize: 10,
reqindex: "index",
reqsize: "size"
},
heads: [
{ num:true, name: "序号" },
{ key: "ActionCode", name: "行为代码" },
{ key: "NoAciton", name: "失信行为" },
{ key: "KoufenBZ", name: "扣分标准" },
{ key: "KoufenQK", name: "扣分情况(扣分值:-)" },
{ key: "Beizhu", name: "备注" },
{ html: ' <a href="javascript:" class="del EQ-btn" data-id="{{ID}}" >删除</a>', name: "操作" }
]
});
//编辑按钮
$("#table-user-list").on("click", ".edit", function () {
var cid = $(this).attr("data-id");
RegUserEdit(cid); //下面有方法
});
//删除见证人员登记
$("#table-CompanyPJ-list").on("click", ".del", function () {
var cid = $(this).attr("data-id");
$.msg.confirm("确定要删除该记录吗?", function () {
$.get("ITestingCompanyPJ/DelRegUser", { id: cid }, function (rd) {
if (rd.error == 0) {
regutable.reload();
}
});
});
});
//编辑见证人员注册
function RegUserEdit(cid) {
$("#modaltemplatebox").modal({
title: "添加机构评价信息",
url: "VTestingCompanyPJ/PJEdit",
buttons: [
{
text: "确定", click: function () {
$("#edit-form").submit();
}
},
{ text: "关闭", click: function () { /*逻辑处理*/$(this).close(); } }
],
afterCreate: function () {
$("#addCorpName").val(CorpName);
$("#edit-form").Form({
success: function () {
regutable.refresh();//刷新列表
$("#modaltemplatebox").close();
},
});
}
});
}
});
}
});
---------------I 控制器---------------I 控制器---------------I 控制器---------------I 控制器---------------I 控制器---------------I 控制器
using fmtx.Framework.Expend;
using NNCQ.Domain;
using NNCQ.Domain.ViewModels;
using NNCQ.IBLL;
using NNCQ.SqlService;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
namespace NNCQ.Web.Controllers.Api
{
public class ITestingCompanyPJController : BaseController
{
// GET: ITestingCompanyPJ
private readonly IDBHelper<CompanyPJ> _CompanyPJ;
private readonly IDBHelper<CompanyGrade> _CompanyGrade;
public ITestingCompanyPJController(IDBHelper<CompanyPJ> CompanyPJ,
IDBHelper<CompanyGrade> CompanyGrade)
{
_CompanyPJ = CompanyPJ;
_CompanyGrade = CompanyGrade;
}
public ActionResult Index(int index, int size,string NAME)
{
int count = 0;
var c = NAME.Trim();
var wherestr = new StringBuilder();
if (!string.IsNullOrEmpty(c))
{
wherestr.AppendFormat(" and NAME like '%{0}%' ", c);
}
var list = SqlDBHelper.Query(index, size, "*", "view_JCcompanyAndGrade", wherestr.ToString(), "ID asc", out count);
return Table(list, count);
}
public ActionResult CompanyPJQK(string CorpName)
{
int count = 0;
string sql = "select *from nncq_TestingCompanyPJ where CorpName=@CorpName ";
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CorpName", CorpName) };
DataTable bo = SqlDBHelper.Query(sql, paras);
return Table(bo, count);
}
public ActionResult CompanyGrade(string CorpName,CompanyGradeVM beanvm)
{
//先通过传递进来的NAME去找这个公司的所有扣分情况,把分数统计出来。
//在把统计出来的分数跟等级写进登记表里面。
string sql= "select 100-sum(KoufenQK) Grade from nncq_TestingCompanyPJ where CorpName=@CorpName ";
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CorpName", CorpName) };
DataTable bo = SqlDBHelper.Query(sql, paras);
//这里注意一个问题,就是当没有评价记录的时候,bo第一行的值是0,这里会出现出错,所以要把0的情况考虑然后把得分表里面的残余数据删除
if (bo.Rows[0]["Grade"].ToString() == "")
{
var sqldel = "delete nncq_TestingPersonGrade where selfnum=@selfnum";
var param = new SqlParameter[] {
new SqlParameter("@selfnum",selfnum)};
SqlDBHelper.ExecuteNonQuery(sqldel, param);
return Table(bo, counttwo);
}
int Grade = int.Parse(bo.Rows[0]["Grade"].ToString());
string CreditGrade;
if (Grade > 90) { CreditGrade = "A级";}
else if (Grade > 80) { CreditGrade = "B级"; }
else if (Grade > 60) { CreditGrade = "C级"; }
else { CreditGrade = "D级"; }
//添加进去
string sqltwo = "select CorpName from nncq_TestingCompanyGrade where CorpName=@CorpName ";
SqlParameter[] parastwo = new SqlParameter[] { new SqlParameter("@CorpName", CorpName) };
var addGrade = _CompanyGrade.Get(sqltwo,parastwo);
if (addGrade != null)
{
var sqldel = "delete nncq_TestingCompanyGrade where CorpName=@CorpName";
var param = new SqlParameter[] {
new SqlParameter("@CorpName",CorpName)
};
SqlDBHelper.ExecuteNonQuery(sqldel, param);
var bean = new CompanyGrade();
bean.CopyModel(beanvm, false);
//
beanvm.Grade = Grade;
bean.Grade = beanvm.Grade;
beanvm.CreditGrade = CreditGrade;
bean.CreditGrade = beanvm.CreditGrade;
//
_CompanyGrade.Insert(bean);
}
else
{
var bean = new CompanyGrade();
bean.CopyModel(beanvm, false);
//
beanvm.Grade = Grade;
bean.Grade = beanvm.Grade;
beanvm.CreditGrade = CreditGrade;
bean.CreditGrade = beanvm.CreditGrade;
//
_CompanyGrade.Insert(bean);
}
int count = 0;
string sqlCG = "select *from nncq_TestingCompanyGrade where CorpName=@CorpName ";
SqlParameter[] parasCG = new SqlParameter[] { new SqlParameter("@CorpName", CorpName) };
DataTable boCG = SqlDBHelper.Query(sqlCG, paras);
return Table(boCG, count);
//return Success();
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult DelRegUser(Guid id)
{
var sql = "delete nncq_TestingCompanyPJ where ID=@id";
var param = new SqlParameter[] {
new SqlParameter("@id",id)
};
SqlDBHelper.ExecuteNonQuery(sql, param);
return Success();
}
/// <summary>
/// 添加
/// </summary>
/// <returns></returns>
public ActionResult SavePJ(CompanyPJVM beanvm)
{
var bo = _CompanyPJ.Get(beanvm.ID);
if (bo != null)
{
bo.ID = beanvm.ID;
bo.CorpName = beanvm.CorpName;
bo.ActionCode = beanvm.ActionCode;
bo.NoAction = beanvm.NoAction;
bo.KoufenBZ = beanvm.KoufenBZ;
bo.KoufenQK = beanvm.KoufenQK;
bo.Beizhu = beanvm.Beizhu;
_CompanyPJ.Update(bo);
}
else
{
var bean = new CompanyPJ();
bean.CopyModel(beanvm, false);
_CompanyPJ.Insert(bean);
}
return Success();
}
}
}
-------------V控制器-------------V控制器-------------V控制器-------------V控制器-------------V控制器-------------V控制器-------------V控制器
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace NNCQ.Web.Controllers.Views
{
public class VTestingCompanyPJController : Controller
{
/// <summary>
/// 检测机构评价
/// </summary>
/// <returns></returns>
// GET: VTestingCompanyPJ
public ActionResult Index()
{
return View();
}
public ActionResult EditDetail()
{
return PartialView();
}
public ActionResult PJEdit()
{
return PartialView();
}
}
}
--------index--------index--------index--------index--------index--------index
@{
ViewBag.Title = "机构信用评价";
}
<div class="row layout-list">
<div class="col-lg-12 col-md-12">
<div id="table-list">
<div class="table-search">
<form fm-search>
<table>
<tr>
<td><label>机构名称:</label></td>
<td>
<input type="text" name="NAME" />
</td>
</tr>
</table>
</form>
</div>
</div>
</div>
</div>
@section Scripts{
<script src="~/Scripts/business/TestingCompanyPJ.js"></script>
<script type="text/javascript">
//其他操作
</script>
}
-----PJEdit-----PJEdit-----PJEdit-----PJEdit-----PJEdit-----PJEdit-----PJEdit-----PJEdit
<table>
<tr>
<td>
<input type="hidden" id="addCorpName" readonly class="form-control" name="CorpName"style="width:200px;" />
</td>
</tr>
<tr>
<td><label>行为代码</label></td>
<td>
<input id="ActionCode" fm-null="行为代码!" class="form-control"placeholder="行为代码" name="ActionCode" type="text" />
</td>
</tr>
<tr>
<td><label>失信行为</label></td>
<td>
<input id="NoAction" fm-null="失信行为!" class="form-control" placeholder="失信行为"name="NoAction" type="text" />
</td>
</tr>
<tr>
<td><label>扣分标准</label></td>
<td>
<input id="KoufenBZ" fm-null="扣分标准!"class="form-control"placeholder="扣分标准" name="KoufenBZ" type="text" />
</td>
</tr>
<tr>
<td><label>扣分情况</label></td>
<td>
<input id="KoufenQK" fm-null="扣分情况!"class="form-control" fm-norreg="^[0-9]{1,2}$,请输入0-99数字!" placeholder="扣分情况" name="KoufenQK" type="text" />
</td>
</tr>
<tr>
<td><label>备注</label></td>
<td>
<input id="Beizhu" class="form-control" placeholder="备注"name="Beizhu" type="text" />
</td>
</tr>
</table>
<input type="hidden" name="ID" />
</form>
-------EditDetail-------EditDetail-------EditDetail-------EditDetail-------EditDetail-------EditDetail-------EditDetail
<fieldset>
<legend>机构信用信息</legend>
<table id="projinfo" class="table" style="background:rgba(255,255,255,.15);">
<tr>
<td colspan="1"><label>机构名称</label></td>
<td colspan="4"><span name="NAME"></span></td>
<td colspan="1"><label>机构资质</label></td>
<td colspan="4"><span name="ECONOMICNATURE"></span></td>
</tr>
<tr>
<td colspan="1"><label>工地试验室及现场检测项目设立数量</label></td>
<td colspan="4"><span name="zzxmgs"></span></td>
<td colspan="1"><label>联系电话</label></td>
<td colspan="4"><span name="TEL"></span></td>
</tr>
<tr>
<td><label>获证日期</label></td>
<td><span name="CREATETIME"></span></td>
<td><label>试验检测工程师(人)</label></td>
<td><span name="jsglcount"></span></td>
<td><label>试验检测员(人)</label></td>
<td><span name="testcount"></span></td>
</tr>
<tr>
<td><label>行政负责人</label></td>
<td><label>姓名</label></td>
<td><span name="FR"></span></td>
<td><label>职称</label></td>
<td><span name=""></span></td>
<td><label>持证证书号(人)</label></td>
<td><span name=""></span></td>
</tr>
<tr>
<td><label>技术负责人</label></td>
<td><label>姓名</label></td>
<td><span name="JSNAME"></span></td>
<td><label>职称</label></td>
<td><span name="JSTIILE"></span></td>
<td><label>持证证书号(人)</label></td>
<td><span name=""></span></td>
</tr>
<tr>
<td><label>质量负责人</label></td>
<td><label>姓名</label></td>
<td><span name="ZLNAME"></span></td>
<td><label>职称</label></td>
<td><span name="ZLTITLE"></span></td>
<td><label>持证证书号(人)</label></td>
<td><span name=""></span></td>
</tr>
</table>
</fieldset>
</div>
<div class="fm-tab" id="projectTab">
<div class="tab-content">
<div data-name="机构评价情况">
<div id="table-CompanyPJ-list"></div>
<div id="table-GradeAndCreditGrade-list">
@*<label>得分</label>
<input id="Grade"readonly class="form-control" placeholder="得分" name="Grade" type="text" />
<label>信用等级</label>
<input id="CreditGrade" readonly class="form-control" placeholder="信用等级" name="CreditGrade" type="text" />*@
</div>
</div>
</div>
</div>