/// <summary>
/// 获得数据列表
/// </summary>
public List <E_tb_TestReportData> DataTableToList(DataTable dt)
{
List <E_tb_TestReportData> modelList = new List <E_tb_TestReportData>();
int rowsCount = dt.Rows.Count;
if (rowsCount > 0)
{
E_tb_TestReportData model;
for (int n = 0; n < rowsCount; n++)
{
model = new E_tb_TestReportData();
if (dt.Rows[n]["ReportDataID"].ToString() != "")
{
model.ReportDataID = int.Parse(dt.Rows[n]["ReportDataID"].ToString());
}
if (dt.Rows[n]["RecordID"].ToString() != "")
{
model.RecordID = int.Parse(dt.Rows[n]["RecordID"].ToString());
}
model.RecordFilePath = dt.Rows[n]["RecordFilePath"].ToString();
if (dt.Rows[n]["ReportID"].ToString() != "")
{
model.ReportID = int.Parse(dt.Rows[n]["ReportID"].ToString());
}
model.TestName = dt.Rows[n]["TestName"].ToString();
model.TestStandard = dt.Rows[n]["TestStandard"].ToString();
model.TestResult = dt.Rows[n]["TestResult"].ToString();
model.QualifiedLevel = dt.Rows[n]["QualifiedLevel"].ToString();
model.TestPersonnelName = dt.Rows[n]["TestPersonnelName"].ToString();
modelList.Add(model);
}
}
return(modelList);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public E_tb_TestReportData GetModel(int ReportDataID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ReportDataID,RecordID,RecordFilePath,ReportID,TestName,TestStandard,TestResult,QualifiedLevel,TestPersonnelName from tb_TestReportData ");
strSql.Append(" where ReportDataID=@ReportDataID");
SqlParameter[] parameters =
{
new SqlParameter("@ReportDataID", SqlDbType.Int, 4)
};
parameters[0].Value = ReportDataID;
E_tb_TestReportData model = new E_tb_TestReportData();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["ReportDataID"].ToString() != "")
{
model.ReportDataID = int.Parse(ds.Tables[0].Rows[0]["ReportDataID"].ToString());
}
if (ds.Tables[0].Rows[0]["RecordID"].ToString() != "")
{
model.RecordID = int.Parse(ds.Tables[0].Rows[0]["RecordID"].ToString());
}
if (ds.Tables[0].Rows[0]["RecordFilePath"] != null)
{
model.RecordFilePath = ds.Tables[0].Rows[0]["RecordFilePath"].ToString();
}
if (ds.Tables[0].Rows[0]["ReportID"].ToString() != "")
{
model.ReportID = int.Parse(ds.Tables[0].Rows[0]["ReportID"].ToString());
}
if (ds.Tables[0].Rows[0]["TestName"] != null)
{
model.TestName = ds.Tables[0].Rows[0]["TestName"].ToString();
}
if (ds.Tables[0].Rows[0]["TestStandard"] != null)
{
model.TestStandard = ds.Tables[0].Rows[0]["TestStandard"].ToString();
}
if (ds.Tables[0].Rows[0]["TestResult"] != null)
{
model.TestResult = ds.Tables[0].Rows[0]["TestResult"].ToString();
}
if (ds.Tables[0].Rows[0]["QualifiedLevel"] != null)
{
model.QualifiedLevel = ds.Tables[0].Rows[0]["QualifiedLevel"].ToString();
}
if (ds.Tables[0].Rows[0]["TestPersonnelName"] != null)
{
model.TestPersonnelName = ds.Tables[0].Rows[0]["TestPersonnelName"].ToString();
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(E_tb_TestReportData model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update tb_TestReportData set ");
strSql.Append("RecordID=@RecordID,");
strSql.Append("RecordFilePath=@RecordFilePath,");
strSql.Append("ReportID=@ReportID,");
strSql.Append("TestName=@TestName,");
strSql.Append("TestStandard=@TestStandard,");
strSql.Append("TestResult=@TestResult,");
strSql.Append("QualifiedLevel=@QualifiedLevel,");
strSql.Append("TestPersonnelName=@TestPersonnelName");
strSql.Append(" where ReportDataID=@ReportDataID");
SqlParameter[] parameters =
{
new SqlParameter("@RecordID", SqlDbType.Int, 4),
new SqlParameter("@RecordFilePath", SqlDbType.NVarChar, 150),
new SqlParameter("@ReportID", SqlDbType.Int, 4),
new SqlParameter("@TestName", SqlDbType.NVarChar, 50),
new SqlParameter("@TestStandard", SqlDbType.NVarChar, 50),
new SqlParameter("@TestResult", SqlDbType.NVarChar, 200),
new SqlParameter("@QualifiedLevel", SqlDbType.NVarChar, 50),
new SqlParameter("@TestPersonnelName", SqlDbType.NVarChar, 50),
new SqlParameter("@ReportDataID", SqlDbType.Int, 4)
};
parameters[0].Value = model.RecordID;
parameters[1].Value = model.RecordFilePath;
parameters[2].Value = model.ReportID;
parameters[3].Value = model.TestName;
parameters[4].Value = model.TestStandard;
parameters[5].Value = model.TestResult;
parameters[6].Value = model.QualifiedLevel;
parameters[7].Value = model.TestPersonnelName;
parameters[8].Value = model.ReportDataID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Save(E_tb_TestReport eTestReport)
{
//检验数据
List <E_ReportData> ReportDataList = new List <E_ReportData>();
string strJson = eTestReport.TestReportDataJson;
if (!string.IsNullOrEmpty(strJson))
{
strJson = strJson.Replace("\r\n", "").Replace("\n", "");
ReportDataList = JsonHelper.JsonDeserialize <List <E_ReportData> >(strJson);
}
//删除原有检验数据
tTestReportData.DeleteByWhere("ReportID=" + eTestReport.ReportID);
foreach (E_ReportData item in ReportDataList)
{
E_tb_TestReportData eTestReportData = new E_tb_TestReportData();
eTestReportData.ReportID = eTestReport.ReportID;
eTestReportData.TestName = item.TestName;
eTestReportData.TestStandard = item.TestStandard;
eTestReportData.TestResult = item.TestResult;
eTestReportData.QualifiedLevel = item.QualifiedLevel;
eTestReportData.TestPersonnelName = item.TestPersonnelName;
eTestReportData.RecordID = int.Parse(item.RecordID);
eTestReportData.RecordFilePath = item.RecordFilePath;
tTestReportData.Add(eTestReportData);
}
//结论
DataTable TestReportDataDT = tTestReportData.GetList("ReportID=" + eTestReport.ReportID + " and QualifiedLevel=''").Tables[0];
eTestReport.EditPersonnelID = CurrentUserInfo.PersonnelID;
eTestReport.AreaID = tInPersonnel.GetModel(eTestReport.MainTestPersonnelID.Value).AreaID;
eTestReport.RecordIDS = eTestReport.RecordIDS.TrimEnd(',');
eTestReport.TaskNoS = eTestReport.TaskNoS.TrimEnd(',');
eTestReport.UpdateTime = DateTime.Now;
tTestReport.Update(eTestReport);
//判断检验报告是否审核通过
if (eTestReport.ApprovalPersonnelID > 0 && eTestReport.examinePersonnelID > 0 && eTestReport.MainTestPersonnelID > 0)
{
dTestReport.ExpePlanPass(eTestReport.ReportID.ToString());
}
return(true);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(E_tb_TestReportData model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into tb_TestReportData(");
strSql.Append("RecordID,RecordFilePath,ReportID,TestName,TestStandard,TestResult,QualifiedLevel,TestPersonnelName)");
strSql.Append(" values (");
strSql.Append("@RecordID,@RecordFilePath,@ReportID,@TestName,@TestStandard,@TestResult,@QualifiedLevel,@TestPersonnelName)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@RecordID", SqlDbType.Int, 4),
new SqlParameter("@RecordFilePath", SqlDbType.NVarChar, 150),
new SqlParameter("@ReportID", SqlDbType.Int, 4),
new SqlParameter("@TestName", SqlDbType.NVarChar, 50),
new SqlParameter("@TestStandard", SqlDbType.NVarChar, 50),
new SqlParameter("@TestResult", SqlDbType.NVarChar, 200),
new SqlParameter("@QualifiedLevel", SqlDbType.NVarChar, 50),
new SqlParameter("@TestPersonnelName", SqlDbType.NVarChar, 50)
};
parameters[0].Value = model.RecordID;
parameters[1].Value = model.RecordFilePath;
parameters[2].Value = model.ReportID;
parameters[3].Value = model.TestName;
parameters[4].Value = model.TestStandard;
parameters[5].Value = model.TestResult;
parameters[6].Value = model.QualifiedLevel;
parameters[7].Value = model.TestPersonnelName;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <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 bool Update(E_tb_TestReportData model)
{
return(dal.Update(model));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(E_tb_TestReportData model)
{
return(dal.Add(model));
}
public string Save(E_tb_TestReport eTestReport)
{
string msg = "0";
try
{
//检验数据
List <E_ReportData> ReportDataList = new List <E_ReportData>();
string strJson = eTestReport.TestReportDataJson;
if (!string.IsNullOrEmpty(strJson))
{
strJson = strJson.Replace("\r\n", "");
ReportDataList = JsonHelper.JsonDeserialize <List <E_ReportData> >(strJson);
}
//删除原有检验数据
tTestReportData.DeleteByWhere("ReportID=" + eTestReport.ReportID);
foreach (E_ReportData item in ReportDataList)
{
E_tb_TestReportData eTestReportData = new E_tb_TestReportData();
eTestReportData.ReportID = eTestReport.ReportID;
eTestReportData.TestName = item.TestName;
eTestReportData.TestStandard = item.TestStandard;
eTestReportData.TestResult = item.TestResult;
eTestReportData.QualifiedLevel = item.QualifiedLevel;
eTestReportData.TestPersonnelName = item.TestPersonnelName;
eTestReportData.RecordID = int.Parse(item.RecordID);
eTestReportData.RecordFilePath = item.RecordFilePath;
tTestReportData.Add(eTestReportData);
}
//结论
//eTestReport.Conclusion = "合格";
DataTable TestReportDataDT = tTestReportData.GetList("ReportID=" + eTestReport.ReportID + " and QualifiedLevel=''").Tables[0];
//if (TestReportDataDT != null && TestReportDataDT.Rows.Count > 0)
//{
// eTestReport.Conclusion = "";
//}
//TestReportDataDT = tTestReportData.GetList("ReportID=" + eTestReport.ReportID + " and QualifiedLevel='不合格'").Tables[0];
//if (TestReportDataDT != null && TestReportDataDT.Rows.Count > 0)
//{
// eTestReport.Conclusion = "不合格";
//}
eTestReport.EditPersonnelID = CurrentUserInfo.PersonnelID;
eTestReport.AreaID = new BLL.PersonnelManage.T_tb_InPersonnel().GetModel(eTestReport.MainTestPersonnelID.Value).AreaID;
//if (CurrentUserInfo.DataRange != 1)
//{
// eTestReport.AreaID = CurrentUserInfo.AreaID;
//}
eTestReport.RecordIDS = eTestReport.RecordIDS.TrimEnd(',');
eTestReport.TaskNoS = eTestReport.TaskNoS.TrimEnd(',');
eTestReport.UpdateTime = DateTime.Now;
tTestReport.Update(eTestReport);
//判断检验报告是否审核通过
if (eTestReport.ApprovalPersonnelID > 0 && eTestReport.examinePersonnelID > 0 && eTestReport.MainTestPersonnelID > 0)
{
tExpePlan.UpdateStatusByPlanIDS(eTestReport.TaskNoS, eTestReport.ReportID);
}
msg = "1";
}
catch (Exception ex)
{
msg = ex.Message;
}
return(msg);
}
