public ActionResult Edit(int id)
{
tb_Sample eSample = new tb_Sample();
List <E_tb_ExpePlan> ExpePlanList = new List <E_tb_ExpePlan>();
if (id > 0)
{
eSample = _dSample.GetSampleInfo(new tb_Sample()
{
id = id
});
ExpePlanList = new D_tb_ExpePlan().GetExpePlanList(new E_tb_ExpePlan()
{
SampleID = eSample.id
});
}
else
{
eSample.sampleNum = GetSampleNum();
eSample.sampleAdmin = CurrentUserInfo.PersonnelName;
}
ViewBag.Info = eSample;
ViewBag.ClientList = new BLL.ClientManage.T_tb_ClientManage().GetModelList("");
ViewBag.ProjectList = new BLL.Laboratory.T_tb_Project().GetModelList("");
ViewBag.ExpePlanList = ExpePlanList;
return(View("/views/Sample/SampleEdit.cshtml"));
}
/// <summary>
/// 获取非农残检测项目 需要填充的数据
/// </summary>
/// <param name="SampleID">样品ID</param>
/// <param name="SampleDataRange">检测项目对应模板中的数据范围</param>
/// <returns>需要填充的数据</returns>
public PageOffice.ExcelWriter.Workbook GetNoPesCheckWorkbook(int SampleID, string SampleDataRange)
{
tb_Sample eSample = tSample.GetModel(SampleID);
ArrayList CellArr = new ArrayList {
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"
};
string StrDataRange = SampleDataRange.Replace("：", ":").ToUpper();
int CellStartIndex = CellArr.IndexOf(StrDataRange.Split(':')[0].Substring(0, 1).ToUpper());
int DRangeStartIndex = int.Parse(StrDataRange.Split(':')[0].Substring(1, StrDataRange.Split(':')[0].Length - 1)) + 1;
int DRangeEndIndex = int.Parse(StrDataRange.Split(':')[1].Substring(1, StrDataRange.Split(':')[0].Length - 1));
//定义Workbook对象
PageOffice.ExcelWriter.Workbook workBook = new PageOffice.ExcelWriter.Workbook();
//定义Sheet对象，"Sheet1"是打开的Excel表单的名称
PageOffice.ExcelWriter.Sheet sheet = workBook.OpenSheet("Sheet1");
for (int i = DRangeStartIndex; i <= DRangeEndIndex; i++)
{
//或者直接给Cell赋值
sheet.OpenCell(CellArr[CellStartIndex].ToString() + i).Value = eSample.sampleNum; //样品编号
sheet.OpenCell(CellArr[CellStartIndex + 1].ToString() + i).Value = eSample.name; //样品名称
sheet.OpenCell(CellArr[CellStartIndex + 2].ToString() + i).Value = eSample.productDate.ToString();; //生产日期
sheet.OpenCell(CellArr[CellStartIndex + 3].ToString() + i).Value = eSample.detectionDate.ToString(); //抽样日期
}
return(workBook);
}
/// <summary>
/// 保存样品
/// </summary>
public JsonResult Save(tb_Sample eSample)
{
bool result = false;
string msg = "";
//判断样品编号是否已存在
if (_dSample.IsExistsSampleNum(eSample.sampleNum, eSample.id))
{
msg = "样品编号已存在，请更换样品编号！";
return(Json(new { result = result, msg = msg }, JsonRequestBehavior.AllowGet));
}
eSample.updateUser = CurrentUserInfo.PersonnelID;
eSample.updateDate = DateTime.Now;
if (eSample.id > 0)//若存在数据，执行更新
{
result = _dSample.Update(eSample);
msg = (result)?"修改成功": "修改失败";
}
else
{
eSample.createUser = CurrentUserInfo.PersonnelID;
eSample.createDate = DateTime.Now;
eSample.handleUser = 0;
eSample.AreaID = (int)CurrentUserInfo.AreaID;
eSample.id = _dSample.Add(eSample);//若不存在数据，直接插入
result = eSample.id > 0;
msg = (result)?"添加成功": "添加失败";
}
return(Json(new { result = result, msg = msg, sampleid = eSample.id, areaid = eSample.AreaID, createuser = eSample.createUser, createdate = eSample.createDate.ToString() }, JsonRequestBehavior.AllowGet));
}
//数据统计
public JsonResult Report(string ProjectID, string StartTime, string EndTime, string RecordSampleIDS)
{
RecordSampleIDS = RecordSampleIDS.TrimEnd(',');
//获取符合要求的数据集合
string sqlWhere = "T.SampleID in (" + RecordSampleIDS.TrimEnd(',') + ") and T.ProjectID=" + ProjectID + " and T.DetectTime>=cast('" + StartTime + "' as datetime) and T.DetectTime<=cast('" + EndTime + "' as datetime) ";
DataTable dt = tRecordSample.GetListForProject(sqlWhere).Tables[0];
ArrayList XNamelist = new ArrayList(); //横坐标
ArrayList DateList = new ArrayList(); //横坐标对应日期集合
ArrayList res = new ArrayList(); //返回结果
//拼接横坐标
DateTime startTime = Convert.ToDateTime(StartTime);
DateTime endTime = Convert.ToDateTime(EndTime);
for (DateTime time = startTime; time <= endTime; time = time.AddDays(1))
{
DateList.Add(time);
XNamelist.Add(time.ToString("MM月dd日"));
}
//获取数据结果
for (int j = 0; j < RecordSampleIDS.Split(',').Length; j++)
{
double[] DataList = new double[DateList.Count]; //数据结果
string SampleID = RecordSampleIDS.Split(',')[j]; //当前样品ID
for (int i = 0; i < DateList.Count; i++) //遍历所有横坐标日期
{
double Score = 0;
DateTime TempDateTime = Convert.ToDateTime(DateList[i]);
DataRow TempRow = dt.Select("SampleID=" + SampleID + " and DetectTime='" + TempDateTime.ToString() + "'").FirstOrDefault();
Score = (TempRow != null) ? Convert.ToDouble(TempRow["Result"].ToString()) : 0;
DataList[i] = Score;
}
//组装坐标点集合
List <List <double> > TempListSum = new List <List <double> >();
for (int k = 0; k < DataList.Count(); k++)
{
List <double> TempList = new List <double>();
if (DataList[k] != 0)
{
TempList.Add(k);
TempList.Add(DataList[k]);
TempListSum.Add(TempList);
}
}
//添加到集合
tb_Sample eSample = tSample.GetModel(Convert.ToInt32(SampleID));
res.Add(new { name = eSample.name, data = TempListSum });
}
var reauts = new { XName = XNamelist, YData = res };
return(Json(reauts, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 获取样品详情
/// </summary>
/// <param name="id">样品ID</param>
/// <returns>样品实体</returns>
public tb_Sample GetSampleInfo(tb_Sample model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select * from tb_Sample where id =@id");
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
model = conn.Query <tb_Sample>(strSql.ToString(), model)?.FirstOrDefault();
}
return(model);
}
/// <summary>
/// 删除样品
/// </summary>
/// <param name="model">样品实体</param>
/// <returns>是否执行成功</returns>
public bool Delete(tb_Sample model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"delete from tb_Sample where id = @id");
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
int count = conn.Execute(strSql.ToString(), model);
return(count > 0);
}
}
/// <summary>
/// 销毁样品
/// </summary>
/// <param name="model">样品实体</param>
/// <returns>是否执行成功</returns>
public bool BatchDestroy(tb_Sample model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"update tb_Sample set
handleUser=@handleUser,
handleDate=@handleDate
where id in (@ids)");
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
int count = conn.Execute(strSql.ToString(), model);
return(count > 0);
}
}
/// <summary>
/// 更新
/// </summary>
public bool Update(tb_Sample model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"update tb_Sample set
AreaID=@AreaID,
name=@name,
standard=@standard,
batch=@batch,
productDate=@productDate,
modelType=@modelType,
expirationDate=@expirationDate,
packaging=@packaging,
isDetection=@isDetection,
detectionUser=@detectionUser,
detectionDate=@detectionDate,
createUser=@createUser,
createDate=@createDate,
updateUser=@updateUser,
updateDate=@updateDate,
temp1=@temp1,
temp2=@temp2,
putArea=@putArea,
sampleHandle=@sampleHandle,
handleUser=@handleUser,
handleDate=@handleDate,
sampleAdmin=@sampleAdmin,
detectionGist=@detectionGist,
detectionMethod=@detectionMethod,
InspectCompany=@InspectCompany,
detectionAdress=@detectionAdress,
detectionCompany=@detectionCompany,
InspectAddress=@InspectAddress,
projectName=@projectName,
testMethod=@testMethod,
sampleNum=@sampleNum,
protNum=@protNum,
storcondition=@storcondition,
checkdepar=@checkdepar,
urgentlevel=@urgentlevel
where id=@id");
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
int count = conn.Execute(strSql.ToString(), model);
return(count > 0);
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model">样品实体</param>
/// <returns>新增样品ID</returns>
public int Add(tb_Sample model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into tb_Sample(");
strSql.Append("AreaID,name,standard,batch,productDate,modelType,expirationDate,packaging,isDetection,detectionUser,detectionDate,createUser,createDate,updateUser,updateDate,temp1,temp2,putArea,sampleHandle,handleUser,handleDate,sampleAdmin,detectionGist,detectionMethod,InspectCompany,detectionAdress,detectionCompany,InspectAddress,projectName,testMethod,sampleNum,protNum,storcondition,checkdepar,urgentlevel)");
strSql.Append(" values (");
strSql.Append("@AreaID,@name,@standard,@batch,@productDate,@modelType,@expirationDate,@packaging,@isDetection,@detectionUser,@detectionDate,@createUser,@createDate,@updateUser,@updateDate,@temp1,@temp2,@putArea,@sampleHandle,@handleUser,@handleDate,@sampleAdmin,@detectionGist,@detectionMethod,@InspectCompany,@detectionAdress,@detectionCompany,@InspectAddress,@projectName,@testMethod,@sampleNum,@protNum,@storcondition,@checkdepar,@urgentlevel)");
strSql.Append(";select @@IDENTITY");
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
int id = Convert.ToInt32(conn.ExecuteScalar(strSql.ToString(), model));
return(id);
}
}
/// <summary>
/// 销毁数据
/// 作者：章建国
/// </summary>
/// <param name="id">主键ID</param>
/// <returns></returns>
public JsonResult Destroy_Item(int id)
{
string str = "销毁失败！";
try
{
tb_Sample model = new tb_Sample();
model = _samplebll.GetModel(id);
model.handleUser = CurrentUserInfo.PersonnelID.ToString();
model.handleDate = DateTime.Now;
if (_samplebll.Update(model))
{
str = "销毁成功！";
}
}
catch
{
}
return(Json(str, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 批量销毁数据
/// 作者：张伟
/// </summary>
/// <param name="id">主键ID</param>
/// <returns></returns>
public JsonResult BatchDestroy_Item(string ids)
{
string str = "销毁失败！";
try
{
string[] arr = ids.Split(',');
for (int i = 0; i < arr.Length; i++)
{
tb_Sample model = new tb_Sample();
model = _samplebll.GetModel(int.Parse(arr[i]));
model.handleUser = CurrentUserInfo.PersonnelID.ToString();
model.handleDate = DateTime.Now;
_samplebll.Update(model);
}
str = "销毁成功！";
}
catch
{
}
return(Json(str, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 新增、更新数据
/// 作者：章建国
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public string doSampleCU(tb_Sample model)
{
string flag = "0";
try
{
model.updateDate = DateTime.Now;
model.updateUser = CurrentUserInfo.PersonnelID;
if (model.id > 0)
{
if (model != null && model.id > 0)
{
model.updateDate = DateTime.Now;
model.updateUser = CurrentUserInfo.PersonnelID;
if (_samplebll.Update(model))
{
flag = "1";
}
}
}
else
{
model.createDate = DateTime.Now;
model.createUser = CurrentUserInfo.PersonnelID;
model.updateDate = DateTime.Now;
model.updateUser = CurrentUserInfo.PersonnelID;
model.sampleAdmin = CurrentUserInfo.PersonnelName;
if (_samplebll.Add(model) > 0)
{
flag = "1";
}
}
}
catch
{
flag = "0";
}
return(flag);
}
/// <summary>
/// 提取并保存文档中的数据
/// </summary>
/// <param name="FilePath"></param>
/// <param name="ProjectID"></param>
/// <returns></returns>
public ActionResult SaveData(string FilePath, int ProjectID, int RecordID)
{
PageOffice.ExcelReader.Workbook wb = new PageOffice.ExcelReader.Workbook();
PageOffice.ExcelReader.Sheet sheet = wb.OpenSheet("Sheet1");
E_tb_Project eProject = tProject.GetModel(ProjectID);
PageOffice.ExcelReader.Table table = sheet.OpenTable(eProject.SampleDataRange.Replace("：", ":").ToUpper());
DataTable dt = new DataTable();
for (int i = 0; i < table.DataFields.Count; i++)
{
dt.Columns.Add(table.DataFields[i].Text);
}
table.NextRow();
while (!table.EOF)
{
//获取提交的数值
if (!table.DataFields.IsEmpty)
{
DataRow row = dt.NewRow();
for (int i = 0; i < table.DataFields.Count; i++)
{
row[i] = table.DataFields[i].Text;
}
dt.Rows.Add(row);
}
try
{
table.NextRow(); //循环进入下一行
}
catch
{
break;
}
}
//删除历史数据
tRecordSample.DeleteListByWhere("RecordFilePath='" + FilePath + "'");
tTestReportData.DeleteByWhere("RecordFilePath='" + FilePath + "'");
//关联检验报告
Regex reg = new Regex("^[0-9]+(.[0-9])?$");
for (int i = 0; i < dt.Rows.Count; i++)
{
if (eProject.IsPesCheck != null && Convert.ToInt32(eProject.IsPesCheck.ToString()) == 1) //判断是否为检验农药残留项目
{
DataRow item = dt.Rows[i];
string SampleName = item["样品名称"].ToString(); //样品名称
string strResult = item["复检值2"].ToString(); //复检值2
if (strResult.Trim() == "")
{
strResult = item["最终值"].ToString();//最终值
}
if (!string.IsNullOrEmpty(SampleName.Trim()))
{
E_tb_TestReportData eTestReportData = new E_tb_TestReportData();
eTestReportData.RecordID = RecordID; //原始记录ID
eTestReportData.RecordFilePath = FilePath; //原始记录文件名
eTestReportData.ReportID = 0; //检验报告ID
eTestReportData.TestName = SampleName; //检验名称/检验项目名称 (农药残留检验项目，直接显示样品名称)
eTestReportData.TestStandard = ""; //检验标准
eTestReportData.TestResult = strResult; //检验结果
eTestReportData.QualifiedLevel = ""; //是否合格
eTestReportData.TestPersonnelName = ""; //检验人
tTestReportData.Add(eTestReportData);
}
}
else
{
DataRow item = dt.Rows[i];
string strSampleID = item["编号"].ToString();
string strResult = item["最终值"].ToString();
tb_Sample eSample = tSample.GetModelList("sampleNum='" + strSampleID.Trim() + "'").FirstOrDefault();
if (eSample != null && !string.IsNullOrEmpty(strResult))
{
E_tb_TestReportData eTestReportData = new E_tb_TestReportData();
eTestReportData.RecordID = RecordID; //原始记录ID
eTestReportData.RecordFilePath = FilePath; //原始记录文件名
eTestReportData.ReportID = 0; //检验报告ID
eTestReportData.TestName = ""; //检验名称/检验项目名称
eTestReportData.TestStandard = ""; //检验标准
eTestReportData.TestResult = strResult; //检验结果
eTestReportData.QualifiedLevel = ""; //是否合格
eTestReportData.TestPersonnelName = ""; //检验人
tTestReportData.Add(eTestReportData);
}
}
}
table.Close();
wb.Close();
return(View("/views/OriginalRecord/SaveData.cshtml"));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(tb_Sample model)
{
return(dal.Add(model));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public tb_Sample DataRowToModel(DataRow row)
{
tb_Sample model = new tb_Sample();
if (row != null)
{
if (row["id"] != null && row["id"].ToString() != "")
{
model.id = int.Parse(row["id"].ToString());
}
if (row["name"] != null)
{
model.name = row["name"].ToString();
}
if (row["standard"] != null)
{
model.standard = row["standard"].ToString();
}
if (row["batch"] != null)
{
model.batch = row["batch"].ToString();
}
if (row["productDate"] != null && row["productDate"].ToString() != "")
{
model.productDate = DateTime.Parse(row["productDate"].ToString());
}
if (row["modelType"] != null)
{
model.modelType = row["modelType"].ToString();
}
if (row["expirationDate"] != null)
{
model.expirationDate = row["expirationDate"].ToString();
}
if (row["packaging"] != null)
{
model.packaging = row["packaging"].ToString();
}
if (row["isDetection"] != null && row["isDetection"].ToString() != "")
{
if ((row["isDetection"].ToString() == "1") || (row["isDetection"].ToString().ToLower() == "true"))
{
model.isDetection = true;
}
else
{
model.isDetection = false;
}
}
if (row["detectionUser"] != null)
{
model.detectionUser = row["detectionUser"].ToString();
}
if (row["detectionDate"] != null && row["detectionDate"].ToString() != "")
{
model.detectionDate = DateTime.Parse(row["detectionDate"].ToString());
}
if (row["createUser"] != null && row["createUser"].ToString() != "")
{
model.createUser = int.Parse(row["createUser"].ToString());
}
if (row["createDate"] != null && row["createDate"].ToString() != "")
{
model.createDate = DateTime.Parse(row["createDate"].ToString());
}
if (row["updateUser"] != null && row["updateUser"].ToString() != "")
{
model.updateUser = int.Parse(row["updateUser"].ToString());
}
if (row["updateDate"] != null && row["updateDate"].ToString() != "")
{
model.updateDate = DateTime.Parse(row["updateDate"].ToString());
}
if (row["temp1"] != null)
{
model.temp1 = row["temp1"].ToString();
}
if (row["temp2"] != null)
{
model.temp2 = row["temp2"].ToString();
}
if (row["putArea"] != null)
{
model.putArea = row["putArea"].ToString();
}
if (row["sampleHandle"] != null)
{
model.sampleHandle = row["sampleHandle"].ToString();
}
if (row["handleUser"] != null)
{
model.handleUser = row["handleUser"].ToString();
}
if (row["handleDate"] != null && row["handleDate"].ToString() != "")
{
model.handleDate = DateTime.Parse(row["handleDate"].ToString());
}
if (row["sampleAdmin"] != null)
{
model.sampleAdmin = row["sampleAdmin"].ToString();
}
if (row["detectionGist"] != null)
{
model.detectionGist = row["detectionGist"].ToString();
}
if (row["detectionMethod"] != null)
{
model.detectionMethod = row["detectionMethod"].ToString();
}
if (row["InspectCompany"] != null)
{
model.InspectCompany = row["InspectCompany"].ToString();
}
if (row["detectionAdress"] != null)
{
model.detectionAdress = row["detectionAdress"].ToString();
}
if (row["detectionCompany"] != null)
{
model.detectionCompany = row["detectionCompany"].ToString();
}
if (row["InspectAddress"] != null)
{
model.InspectAddress = row["InspectAddress"].ToString();
}
if (row["projectName"] != null)
{
model.projectName = row["projectName"].ToString();
}
if (row["testMethod"] != null)
{
model.testMethod = row["testMethod"].ToString();
}
if (row["sampleNum"] != null)
{
model.sampleNum = row["sampleNum"].ToString();
}
if (row["protNum"] != null)
{
model.protNum = row["protNum"].ToString();
}
}
return(model);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public tb_Sample GetModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id,name,standard,batch,productDate,modelType,expirationDate,packaging,isDetection,detectionUser,detectionDate,createUser,createDate,updateUser,updateDate,temp1,temp2,putArea,sampleHandle,handleUser,handleDate,sampleAdmin,detectionGist,detectionMethod,InspectCompany,detectionAdress,detectionCompany,InspectAddress,projectName,testMethod,sampleNum,protNum from tb_Sample ");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = id;
tb_Sample model = new tb_Sample();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["id"].ToString() != "")
{
model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
}
if (ds.Tables[0].Rows[0]["name"] != null)
{
model.name = ds.Tables[0].Rows[0]["name"].ToString();
}
if (ds.Tables[0].Rows[0]["standard"] != null)
{
model.standard = ds.Tables[0].Rows[0]["standard"].ToString();
}
if (ds.Tables[0].Rows[0]["batch"] != null)
{
model.batch = ds.Tables[0].Rows[0]["batch"].ToString();
}
if (ds.Tables[0].Rows[0]["productDate"].ToString() != "")
{
model.productDate = DateTime.Parse(ds.Tables[0].Rows[0]["productDate"].ToString());
}
if (ds.Tables[0].Rows[0]["modelType"] != null)
{
model.modelType = ds.Tables[0].Rows[0]["modelType"].ToString();
}
if (ds.Tables[0].Rows[0]["expirationDate"] != null)
{
model.expirationDate = ds.Tables[0].Rows[0]["expirationDate"].ToString();
}
if (ds.Tables[0].Rows[0]["packaging"] != null)
{
model.packaging = ds.Tables[0].Rows[0]["packaging"].ToString();
}
if (ds.Tables[0].Rows[0]["isDetection"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["isDetection"].ToString() == "1") || (ds.Tables[0].Rows[0]["isDetection"].ToString().ToLower() == "true"))
{
model.isDetection = true;
}
else
{
model.isDetection = false;
}
}
if (ds.Tables[0].Rows[0]["detectionUser"] != null)
{
model.detectionUser = ds.Tables[0].Rows[0]["detectionUser"].ToString();
}
if (ds.Tables[0].Rows[0]["detectionDate"].ToString() != "")
{
model.detectionDate = DateTime.Parse(ds.Tables[0].Rows[0]["detectionDate"].ToString());
}
if (ds.Tables[0].Rows[0]["createUser"].ToString() != "")
{
model.createUser = int.Parse(ds.Tables[0].Rows[0]["createUser"].ToString());
}
if (ds.Tables[0].Rows[0]["createDate"].ToString() != "")
{
model.createDate = DateTime.Parse(ds.Tables[0].Rows[0]["createDate"].ToString());
}
if (ds.Tables[0].Rows[0]["updateUser"].ToString() != "")
{
model.updateUser = int.Parse(ds.Tables[0].Rows[0]["updateUser"].ToString());
}
if (ds.Tables[0].Rows[0]["updateDate"].ToString() != "")
{
model.updateDate = DateTime.Parse(ds.Tables[0].Rows[0]["updateDate"].ToString());
}
if (ds.Tables[0].Rows[0]["temp1"] != null)
{
model.temp1 = ds.Tables[0].Rows[0]["temp1"].ToString();
}
if (ds.Tables[0].Rows[0]["temp2"] != null)
{
model.temp2 = ds.Tables[0].Rows[0]["temp2"].ToString();
}
if (ds.Tables[0].Rows[0]["putArea"] != null)
{
model.putArea = ds.Tables[0].Rows[0]["putArea"].ToString();
}
if (ds.Tables[0].Rows[0]["sampleHandle"] != null)
{
model.sampleHandle = ds.Tables[0].Rows[0]["sampleHandle"].ToString();
}
if (ds.Tables[0].Rows[0]["handleUser"] != null)
{
model.handleUser = ds.Tables[0].Rows[0]["handleUser"].ToString();
}
if (ds.Tables[0].Rows[0]["handleDate"].ToString() != "")
{
model.handleDate = DateTime.Parse(ds.Tables[0].Rows[0]["handleDate"].ToString());
}
if (ds.Tables[0].Rows[0]["sampleAdmin"] != null)
{
model.sampleAdmin = ds.Tables[0].Rows[0]["sampleAdmin"].ToString();
}
if (ds.Tables[0].Rows[0]["detectionGist"] != null)
{
model.detectionGist = ds.Tables[0].Rows[0]["detectionGist"].ToString();
}
if (ds.Tables[0].Rows[0]["detectionMethod"] != null)
{
model.detectionMethod = ds.Tables[0].Rows[0]["detectionMethod"].ToString();
}
if (ds.Tables[0].Rows[0]["InspectCompany"] != null)
{
model.InspectCompany = ds.Tables[0].Rows[0]["InspectCompany"].ToString();
}
if (ds.Tables[0].Rows[0]["detectionAdress"] != null)
{
model.detectionAdress = ds.Tables[0].Rows[0]["detectionAdress"].ToString();
}
if (ds.Tables[0].Rows[0]["detectionCompany"] != null)
{
model.detectionCompany = ds.Tables[0].Rows[0]["detectionCompany"].ToString();
}
if (ds.Tables[0].Rows[0]["InspectAddress"] != null)
{
model.InspectAddress = ds.Tables[0].Rows[0]["InspectAddress"].ToString();
}
if (ds.Tables[0].Rows[0]["projectName"] != null)
{
model.projectName = ds.Tables[0].Rows[0]["projectName"].ToString();
}
if (ds.Tables[0].Rows[0]["testMethod"] != null)
{
model.testMethod = ds.Tables[0].Rows[0]["testMethod"].ToString();
}
if (ds.Tables[0].Rows[0]["sampleNum"] != null)
{
model.sampleNum = ds.Tables[0].Rows[0]["sampleNum"].ToString();
}
if (ds.Tables[0].Rows[0]["protNum"] != null)
{
model.protNum = ds.Tables[0].Rows[0]["protNum"].ToString();
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(tb_Sample model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update tb_Sample set ");
strSql.Append("name=@name,");
strSql.Append("standard=@standard,");
strSql.Append("batch=@batch,");
strSql.Append("productDate=@productDate,");
strSql.Append("modelType=@modelType,");
strSql.Append("expirationDate=@expirationDate,");
strSql.Append("packaging=@packaging,");
strSql.Append("isDetection=@isDetection,");
strSql.Append("detectionUser=@detectionUser,");
strSql.Append("detectionDate=@detectionDate,");
strSql.Append("createUser=@createUser,");
strSql.Append("createDate=@createDate,");
strSql.Append("updateUser=@updateUser,");
strSql.Append("updateDate=@updateDate,");
strSql.Append("temp1=@temp1,");
strSql.Append("temp2=@temp2,");
strSql.Append("putArea=@putArea,");
strSql.Append("sampleHandle=@sampleHandle,");
strSql.Append("handleUser=@handleUser,");
strSql.Append("handleDate=@handleDate,");
strSql.Append("sampleAdmin=@sampleAdmin,");
strSql.Append("detectionGist=@detectionGist,");
strSql.Append("detectionMethod=@detectionMethod,");
strSql.Append("InspectCompany=@InspectCompany,");
strSql.Append("detectionAdress=@detectionAdress,");
strSql.Append("detectionCompany=@detectionCompany,");
strSql.Append("InspectAddress=@InspectAddress,");
strSql.Append("projectName=@projectName,");
strSql.Append("testMethod=@testMethod,");
strSql.Append("sampleNum=@sampleNum,");
strSql.Append("protNum=@protNum");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@name", SqlDbType.NVarChar, 200),
new SqlParameter("@standard", SqlDbType.NVarChar, 200),
new SqlParameter("@batch", SqlDbType.VarChar, 200),
new SqlParameter("@productDate", SqlDbType.DateTime),
new SqlParameter("@modelType", SqlDbType.VarChar, 200),
new SqlParameter("@expirationDate", SqlDbType.NVarChar, 200),
new SqlParameter("@packaging", SqlDbType.NVarChar, 50),
new SqlParameter("@isDetection", SqlDbType.Bit, 1),
new SqlParameter("@detectionUser", SqlDbType.NVarChar, 200),
new SqlParameter("@detectionDate", SqlDbType.DateTime),
new SqlParameter("@createUser", SqlDbType.Int, 4),
new SqlParameter("@createDate", SqlDbType.DateTime),
new SqlParameter("@updateUser", SqlDbType.Int, 4),
new SqlParameter("@updateDate", SqlDbType.DateTime),
new SqlParameter("@temp1", SqlDbType.NVarChar),
new SqlParameter("@temp2", SqlDbType.NVarChar),
new SqlParameter("@putArea", SqlDbType.NVarChar),
new SqlParameter("@sampleHandle", SqlDbType.NVarChar),
new SqlParameter("@handleUser", SqlDbType.NVarChar, 50),
new SqlParameter("@handleDate", SqlDbType.DateTime),
new SqlParameter("@sampleAdmin", SqlDbType.NVarChar),
new SqlParameter("@detectionGist", SqlDbType.NVarChar, 200),
new SqlParameter("@detectionMethod", SqlDbType.NVarChar),
new SqlParameter("@InspectCompany", SqlDbType.NVarChar),
new SqlParameter("@detectionAdress", SqlDbType.NVarChar),
new SqlParameter("@detectionCompany", SqlDbType.NVarChar),
new SqlParameter("@InspectAddress", SqlDbType.NVarChar),
new SqlParameter("@projectName", SqlDbType.NVarChar),
new SqlParameter("@testMethod", SqlDbType.NVarChar),
new SqlParameter("@sampleNum", SqlDbType.NVarChar),
new SqlParameter("@protNum", SqlDbType.NVarChar),
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = model.name;
parameters[1].Value = model.standard;
parameters[2].Value = model.batch;
parameters[3].Value = model.productDate;
parameters[4].Value = model.modelType;
parameters[5].Value = model.expirationDate;
parameters[6].Value = model.packaging;
parameters[7].Value = model.isDetection;
parameters[8].Value = model.detectionUser;
parameters[9].Value = model.detectionDate;
parameters[10].Value = model.createUser;
parameters[11].Value = model.createDate;
parameters[12].Value = model.updateUser;
parameters[13].Value = model.updateDate;
parameters[14].Value = model.temp1;
parameters[15].Value = model.temp2;
parameters[16].Value = model.putArea;
parameters[17].Value = model.sampleHandle;
parameters[18].Value = model.handleUser;
parameters[19].Value = model.handleDate;
parameters[20].Value = model.sampleAdmin;
parameters[21].Value = model.detectionGist;
parameters[22].Value = model.detectionMethod;
parameters[23].Value = model.InspectCompany;
parameters[24].Value = model.detectionAdress;
parameters[25].Value = model.detectionCompany;
parameters[26].Value = model.InspectAddress;
parameters[27].Value = model.projectName;
parameters[28].Value = model.testMethod;
parameters[29].Value = model.sampleNum;
parameters[30].Value = model.protNum;
parameters[31].Value = model.id;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(tb_Sample model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into tb_Sample(");
strSql.Append("name,standard,batch,productDate,modelType,expirationDate,packaging,isDetection,detectionUser,detectionDate,createUser,createDate,updateUser,updateDate,temp1,temp2,putArea,sampleHandle,handleUser,handleDate,sampleAdmin,detectionGist,detectionMethod,InspectCompany,detectionAdress,detectionCompany,InspectAddress,projectName,testMethod,sampleNum,protNum)");
strSql.Append(" values (");
strSql.Append("@name,@standard,@batch,@productDate,@modelType,@expirationDate,@packaging,@isDetection,@detectionUser,@detectionDate,@createUser,@createDate,@updateUser,@updateDate,@temp1,@temp2,@putArea,@sampleHandle,@handleUser,@handleDate,@sampleAdmin,@detectionGist,@detectionMethod,@InspectCompany,@detectionAdress,@detectionCompany,@InspectAddress,@projectName,@testMethod,@sampleNum,@protNum)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@name", SqlDbType.NVarChar, 200),
new SqlParameter("@standard", SqlDbType.NVarChar, 200),
new SqlParameter("@batch", SqlDbType.VarChar, 200),
new SqlParameter("@productDate", SqlDbType.DateTime),
new SqlParameter("@modelType", SqlDbType.VarChar, 200),
new SqlParameter("@expirationDate", SqlDbType.NVarChar, 200),
new SqlParameter("@packaging", SqlDbType.NVarChar, 50),
new SqlParameter("@isDetection", SqlDbType.Bit, 1),
new SqlParameter("@detectionUser", SqlDbType.NVarChar, 200),
new SqlParameter("@detectionDate", SqlDbType.DateTime),
new SqlParameter("@createUser", SqlDbType.Int, 4),
new SqlParameter("@createDate", SqlDbType.DateTime),
new SqlParameter("@updateUser", SqlDbType.Int, 4),
new SqlParameter("@updateDate", SqlDbType.DateTime),
new SqlParameter("@temp1", SqlDbType.NVarChar),
new SqlParameter("@temp2", SqlDbType.NVarChar),
new SqlParameter("@putArea", SqlDbType.NVarChar),
new SqlParameter("@sampleHandle", SqlDbType.NVarChar),
new SqlParameter("@handleUser", SqlDbType.NVarChar, 50),
new SqlParameter("@handleDate", SqlDbType.DateTime),
new SqlParameter("@sampleAdmin", SqlDbType.NVarChar),
new SqlParameter("@detectionGist", SqlDbType.NVarChar, 200),
new SqlParameter("@detectionMethod", SqlDbType.NVarChar),
new SqlParameter("@InspectCompany", SqlDbType.NVarChar),
new SqlParameter("@detectionAdress", SqlDbType.NVarChar),
new SqlParameter("@detectionCompany", SqlDbType.NVarChar),
new SqlParameter("@InspectAddress", SqlDbType.NVarChar),
new SqlParameter("@projectName", SqlDbType.NVarChar),
new SqlParameter("@testMethod", SqlDbType.NVarChar),
new SqlParameter("@sampleNum", SqlDbType.NVarChar),
new SqlParameter("@protNum", SqlDbType.NVarChar)
};
parameters[0].Value = model.name;
parameters[1].Value = model.standard;
parameters[2].Value = model.batch;
parameters[3].Value = model.productDate;
parameters[4].Value = model.modelType;
parameters[5].Value = model.expirationDate;
parameters[6].Value = model.packaging;
parameters[7].Value = model.isDetection;
parameters[8].Value = model.detectionUser;
parameters[9].Value = model.detectionDate;
parameters[10].Value = model.createUser;
parameters[11].Value = model.createDate;
parameters[12].Value = model.updateUser;
parameters[13].Value = model.updateDate;
parameters[14].Value = model.temp1;
parameters[15].Value = model.temp2;
parameters[16].Value = model.putArea;
parameters[17].Value = model.sampleHandle;
parameters[18].Value = model.handleUser;
parameters[19].Value = model.handleDate;
parameters[20].Value = model.sampleAdmin;
parameters[21].Value = model.detectionGist;
parameters[22].Value = model.detectionMethod;
parameters[23].Value = model.InspectCompany;
parameters[24].Value = model.detectionAdress;
parameters[25].Value = model.detectionCompany;
parameters[26].Value = model.InspectAddress;
parameters[27].Value = model.projectName;
parameters[28].Value = model.testMethod;
parameters[29].Value = model.sampleNum;
parameters[30].Value = model.protNum;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(tb_Sample model)
{
return(dal.Update(model));
}
/// <summary>
/// 跳转页面
/// 作者：章建国
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult doSampleInfo(int id)
{
ViewBag._PersonnelName = CurrentUserInfo.PersonnelName;
BLL.RoleManage.T_tb_Area _area = new BLL.RoleManage.T_tb_Area();
String _areaname = _area.GetModel(CurrentUserInfo.AreaID.Value).AreaName;
switch (_areaname)
{
case "食品检测中心":
{
_areaname = "JC";
break;
}
case "葫芦岛实验室":
{
_areaname = "HL";
break;
}
case "深圳实验室":
{
_areaname = "SZ";
break;
}
case "湛江实验室":
{
_areaname = "ZJ";
break;
}
case "龙口实验室":
{
_areaname = "LK";
break;
}
case "烹饪加工营养项目":
{
_areaname = "PR";
break;
}
case "原料食品安全项目":
{
_areaname = "YL";
break;
}
default:
{
_areaname = "";
break;
}
}
tb_Sample model = new tb_Sample();
if (id == 0)
{
model.sampleNum = DateTime.Now.ToShortDateString();
var list = _samplebll.GetModelList("DATEDIFF(day , createDate, '" + DateTime.Now.ToShortDateString() + "')=0 and createUser in (select PersonnelID from tb_InPersonnel where AreaID = " + CurrentUserInfo.AreaID + ")");
if (list != null && list.Count > 0)
{
int count = list.Count + 1;
model.sampleNum = _areaname + DateTime.Now.ToString("yyyyMMdd") + "—" + count.ToString("D3");
}
else
{
model.sampleNum = _areaname + DateTime.Now.ToString("yyyyMMdd") + "—001";
}
return(View(model));
}
model = _samplebll.GetModel(id);
return(View(model));
}
/// <summary>
/// 更新检验报告
/// </summary>
/// <param name="eOriginalRecord"></param>
/// <param name="eExpePlan"></param>
public JsonResult UpdateTestReport(int RecordID)
{
E_tb_OriginalRecord eOriginalRecord = _dOriginalRecord.GetModel(new E_tb_OriginalRecord()
{
RecordID = RecordID
});
E_tb_ExpePlan eExpePlan = _dExpePlan.GetExpePlanInfo(new E_tb_ExpePlan()
{
TaskNo = eOriginalRecord.TaskNo
});
tb_Sample eSample = tSample.GetModel(eOriginalRecord.SampleID.Value);
string productNum = eSample.protNum;//产品批次
E_tb_TestReport eTestReport = null;
var tempmodel = tTestReport.GetModelList(" A.SampleNum = '" + eSample.sampleNum + "'");
if (tempmodel != null && tempmodel.Count > 0)
{
eTestReport = tempmodel.First();
eTestReport.SampleNum = eSample.sampleNum; //样品编号
eTestReport.SampleName = eSample.name; //样品名称
}
if (eTestReport == null)
{
eTestReport = new E_tb_TestReport();
eTestReport.productNum = productNum; //批次号
eTestReport.SampleNum = eSample.sampleNum; //样品编号
eTestReport.SampleName = eSample.name; //样品名称
eTestReport.ProductionTime = eSample.productDate; //生产日期
eTestReport.Specifications = eSample.modelType; //规格型号
eTestReport.ShelfLife = eSample.expirationDate; //保质期
string Department = "/";
if (eSample.isDetection)
{
Department = eSample.detectionCompany;
}
else
{
var client = new BLL.ClientManage.T_tb_ClientManage().GetModel(Convert.ToInt32(eSample.InspectCompany));
Department = (client != null ? client.ClientName : "");
}
eTestReport.Department = Department; //送/抽检单位
eTestReport.SendTestAddress = eSample.InspectAddress; //送检单位地址
eTestReport.SamplingPlace = eSample.detectionAdress; //抽样地点
eTestReport.SamplingCompany = eSample.detectionCompany; //抽样单位
eTestReport.SamplingPersonnel = eSample.detectionUser; //抽样者
eTestReport.Packing = eSample.packaging; //包装形式
eTestReport.SamplingTime = eSample.detectionDate; //抽样日期
eTestReport.TestBasis = new BLL.Laboratory.T_tb_Project().GetModel(eOriginalRecord.ProjectID.Value).ExpeMethod;
eTestReport.TestTime = eOriginalRecord.DetectTime; //检验日期
DataTable dt = tOriginalRecord.GetRecordIDListBySampleID(int.Parse(eExpePlan.SampleID.ToString()));
string RecordIDS = "";
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
RecordIDS += dt.Rows[i]["RecordID"].ToString() + ",";
}
RecordIDS = RecordIDS.TrimEnd(',');
}
eTestReport.RecordIDS = RecordIDS;//原始记录IDS
DataTable dtExpePlan = tExpePlan.GetList("SampleID=" + eExpePlan.SampleID.ToString()).Tables[0];
string TaskNoS = "";
if (dtExpePlan != null && dtExpePlan.Rows.Count > 0)
{
for (int i = 0; i < dtExpePlan.Rows.Count; i++)
{
TaskNoS += dtExpePlan.Rows[i]["PlanID"].ToString() + ",";
}
TaskNoS = TaskNoS.TrimEnd(',');
}
eTestReport.TaskNoS = TaskNoS;//任务单号 对应的检验计划IDS
eTestReport.EditPersonnelID = CurrentUserInfo.PersonnelID;
eTestReport.AreaID = CurrentUserInfo.AreaID;
eTestReport.AddTime = Convert.ToDateTime(DateTime.Now.ToShortDateString());
eTestReport.UpdateTime = DateTime.Now;
eTestReport.ReportID = tTestReport.Add(eTestReport);
}
else
{
DataTable dt = tOriginalRecord.GetRecordIDListBySampleID(int.Parse(eExpePlan.SampleID.ToString()));
string RecordIDS = "";
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
RecordIDS += dt.Rows[i]["RecordID"].ToString() + ",";
}
RecordIDS = RecordIDS.TrimEnd(',');
}
eTestReport.RecordIDS = RecordIDS;//原始记录IDS
DataTable dtExpePlan = tExpePlan.GetList("SampleID=" + eExpePlan.SampleID.ToString()).Tables[0];
string TaskNoS = "";
if (dtExpePlan != null && dtExpePlan.Rows.Count > 0)
{
for (int i = 0; i < dtExpePlan.Rows.Count; i++)
{
TaskNoS += dtExpePlan.Rows[i]["PlanID"].ToString() + ",";
}
TaskNoS = TaskNoS.TrimEnd(',');
}
eTestReport.TaskNoS = TaskNoS;//任务单号 对应的检验计划IDS
eTestReport.AreaID = CurrentUserInfo.AreaID;
eTestReport.EditPersonnelID = CurrentUserInfo.PersonnelID;
eTestReport.UpdateTime = DateTime.Now;
eTestReport.TestBasis = new BLL.Laboratory.T_tb_Project().GetModel(eOriginalRecord.ProjectID.Value).ExpeMethod;
tTestReport.Update(eTestReport);
}
//更新检验报告数据
List <E_tb_TestReportData> eTestReportDataList = tTestReportData.GetModelList("RecordFilePath='" + eOriginalRecord.FilePath + "'");
if (eTestReportDataList != null)
{
E_tb_Project eProject = tProject.GetModel(Convert.ToInt32(eOriginalRecord.ProjectID));
if (eProject.IsPesCheck != null && int.Parse(eProject.IsPesCheck.ToString()) == 1)//判断是否为农药残留检查项目
{
foreach (E_tb_TestReportData eTestReportData in eTestReportDataList)
{
eTestReportData.RecordID = eOriginalRecord.RecordID; //原始记录ID
eTestReportData.ReportID = eTestReport.ReportID; //检验报告ID
eTestReportData.TestStandard = eProject.ExpeMethod; //检验标准
if (!String.IsNullOrEmpty(eOriginalRecord.InsStand))
{
String[] strs = eOriginalRecord.InsStand.Split('：');
if (strs == null || strs.Length <= 2)
{
strs = eOriginalRecord.InsStand.Split(':');
}
if (strs != null && strs.Length >= 2)
{
String str = strs[1];
int result = 0;
if (!String.IsNullOrEmpty(str))
{
// 正则表达式剔除非数字字符（不包含小数点.）
str = Regex.Replace(str, @"[^\d.\d]", "");
// 如果是数字，则转换为decimal类型
if (Regex.IsMatch(str, @"^[+-]?\d*[.]?\d*$"))
{
result = int.Parse(str);
}
if (!String.IsNullOrEmpty(eTestReportData.TestResult))
{
Decimal _testResult = Convert.ToDecimal(eTestReportData.TestResult);
if (_testResult <= result)
{
eTestReportData.QualifiedLevel = "合格";
}
else
{
eTestReportData.QualifiedLevel = "不合格";
}
}
}
}
}
tTestReportData.Update(eTestReportData);
}
}
else
{
foreach (E_tb_TestReportData eTestReportData in eTestReportDataList)
{
eTestReportData.RecordID = eOriginalRecord.RecordID; //原始记录ID
eTestReportData.ReportID = eTestReport.ReportID; //检验报告ID
eTestReportData.TestName = eProject.ProjectName; //检验名称/检验项目名称
eTestReportData.TestStandard = eProject.ExpeMethod; //检验标准
if (!String.IsNullOrEmpty(eOriginalRecord.InsStand))
{
String[] strs = eOriginalRecord.InsStand.Split('：');
if (strs == null || strs.Length <= 2)
{
strs = eOriginalRecord.InsStand.Split(':');
}
if (strs != null && strs.Length >= 2)
{
String str = strs[1];
int result = 0;
if (!String.IsNullOrEmpty(str))
{
// 正则表达式剔除非数字字符（不包含小数点.）
str = Regex.Replace(str, @"[^\d.\d]", "");
// 如果是数字，则转换为decimal类型
if (Regex.IsMatch(str, @"^[+-]?\d*[.]?\d*$") && !string.IsNullOrEmpty(str))
{
result = int.Parse(str);
}
if (!String.IsNullOrEmpty(eTestReportData.TestResult) && !string.IsNullOrEmpty(str))
{
Decimal _testResult = Convert.ToDecimal(eTestReportData.TestResult);
if (_testResult <= result)
{
eTestReportData.QualifiedLevel = "合格";
}
else
{
eTestReportData.QualifiedLevel = "不合格";
}
}
}
}
}
tTestReportData.Update(eTestReportData);
}
}
}
return(Json(new { result = true, msg = "更新成功！" }, JsonRequestBehavior.AllowGet));
}
