/// <summary>
/// 获得质检记录表中的水分各项平均值
/// </summary>
/// <param name="iqcinfoID">质检编号</param>
/// <param name="QCRecord_QCRetest_ID">复测编号</param>
/// <returns></returns>
public static List <QCRecordAVGEnitiy> GetQCRecord_RESULT_AVERAGE(int iqcinfoID, int iQCRecord_QCRetest_ID, string strTestItems_NAME)
{
List <QCRecordAVGEnitiy> list = new List <QCRecordAVGEnitiy>();
using (DCQUALITYDataContext db = new DCQUALITYDataContext())
{
try
{
Expression <Func <QCRecord, bool> > expr = (Expression <Func <QCRecord, bool> >)PredicateExtensionses.True <QCRecord>();
if (iqcinfoID > 0)
{
expr = expr.And(p => (p.QCRecord_QCInfo_ID == iqcinfoID));
}
if (iQCRecord_QCRetest_ID > 0)
{
expr = expr.And(p => (p.QCRecord_QCRetest_ID == iQCRecord_QCRetest_ID));
}
if (strTestItems_NAME != "")
{
int testItemsID = db.TestItems.First(d => (d.TestItems_NAME == strTestItems_NAME && d.Dictionary.Dictionary_Value == "启动")).TestItems_ID;
expr = expr.And(p => (p.TestItems.Tes_TestItems_ID == testItemsID));
}
expr = expr.And(p => (p.Dictionary.Dictionary_Value == "启动" && (p.QCRecord_RESULT != null && p.QCRecord_RESULT > 0)));
var c = (from p in db.QCRecord.Where(expr)
group p by new
{
p.TestItems.TestItems_NAME,
p.QCRecord_QCInfo_ID,
p.QCRecord_QCRetest_ID
} into g
select new
{
TestItemName = g.Key.TestItems_NAME,
Count = g.Count(),
Avg = g.Average(p => p.QCRecord_RESULT)
}).ToList();
if (c != null)
{
QCRecordAVGEnitiy enitiy;
for (int i = 0; i < c.Count; i++)
{
enitiy = new QCRecordAVGEnitiy();
enitiy.TestItemName = c[i].TestItemName;
enitiy.Count = c[i].Count;
enitiy.Avg = (double)c[i].Avg.Value;
list.Add(enitiy);
}
#region 计算水分检测平均值
string strwateItem = "水分检测";
int testItemsID = db.TestItems.First(d => (d.TestItems_NAME == strwateItem && d.Dictionary.Dictionary_Value == "启动")).TestItems_ID;
enitiy = new QCRecordAVGEnitiy();
enitiy.TestItemName = strwateItem;
enitiy.Count = 0;
if (iQCRecord_QCRetest_ID > 0)
{
var f = (from p in db.QCRecord
where p.QCRecord_QCInfo_ID == iqcinfoID && p.QCRecord_QCRetest_ID == iQCRecord_QCRetest_ID && p.TestItems.Tes_TestItems_ID == testItemsID && p.Dictionary.Dictionary_Value == "启动" && (p.QCRecord_RESULT != null && p.QCRecord_RESULT > 0)
select p.QCRecord_RESULT).Average();
if (f != null)
{
enitiy.Avg = (double)f.Value;
}
}
else
{
var f = (from p in db.QCRecord
where p.QCRecord_QCInfo_ID == iqcinfoID && p.TestItems.Tes_TestItems_ID == testItemsID && p.Dictionary.Dictionary_Value == "启动" && (p.QCRecord_RESULT != null && p.QCRecord_RESULT > 0)
select p.QCRecord_RESULT).Average();
if (f != null)
{
enitiy.Avg = (double)f.Value;
}
}
list.Add(enitiy);
#endregion
}
#region 注释
//int testItemsID = db.TestItems.First(d => (d.TestItems_NAME == strTestItems_NAME && d.Dictionary.Dictionary_Value == "启动")).TestItems_ID;
//if (iQCRecord_QCRetest_ID >= 1)
//{
// var c = (from p in db.QCRecord
// where p.QCRecord_QCInfo_ID == iqcinfoID && p.TestItems.Tes_TestItems_ID == testItemsID && p.Dictionary.Dictionary_Value == "启动" && (p.QCRecord_RESULT != null && p.QCRecord_RESULT > 0) && (p.QCRecord_QCRetest_ID != null && p.QCRecord_QCRetest_ID == iQCRecord_QCRetest_ID)
// group p by new
// {
// p.TestItems.TestItems_NAME
// //, p.QCRecord_QCInfo_ID,
// //p.QCRecord_QCRetest_ID
// } into g
// select new
// {
// TestItemName = g.Key.TestItems_NAME,
// Count = g.Count(),
// Avg = g.Average(p => p.QCRecord_RESULT)
// }).ToList();
// if (c != null)
// {
// QCRecordAVGEnitiy enitiy;
// for (int i = 0; i < c.Count; i++)
// {
// enitiy = new QCRecordAVGEnitiy();
// enitiy.TestItemName = c[i].TestItemName;
// enitiy.Count = c[i].Count;
// enitiy.Avg = (double)c[i].Avg.Value;
// list.Add(enitiy);
// }
// var f = (from p in db.QCRecord
// where p.QCRecord_QCInfo_ID == iqcinfoID && p.TestItems.Tes_TestItems_ID == testItemsID && p.Dictionary.Dictionary_Value == "启动" && (p.QCRecord_RESULT != null && p.QCRecord_RESULT > 0) && (p.QCRecord_QCRetest_ID != null && p.QCRecord_QCRetest_ID == iQCRecord_QCRetest_ID)
// select p.QCRecord_RESULT).Average();
// enitiy = new QCRecordAVGEnitiy();
// enitiy.TestItemName = strTestItems_NAME;
// enitiy.Count = 0;
// enitiy.Avg = (double)f.Value;
// list.Add(enitiy);
// }
//}
//else
//{
// var c = (from p in db.QCRecord
// where p.QCRecord_QCInfo_ID == iqcinfoID && p.TestItems.Tes_TestItems_ID == testItemsID && p.Dictionary.Dictionary_Value == "启动" && (p.QCRecord_RESULT != null && p.QCRecord_RESULT > 0)
// group p by new
// {
// // p.QCRecord_QCInfo_ID,
// p.TestItems.TestItems_NAME
// // , p.QCRecord_QCRetest_ID
// } into g
// select new
// {
// TestItemName = g.Key.TestItems_NAME,
// Count = g.Count(),
// Avg = g.Average(p => p.QCRecord_RESULT)
// }).ToList();
// if (c != null)
// {
// QCRecordAVGEnitiy enitiy;
// for (int i = 0; i < c.Count; i++)
// {
// enitiy = new QCRecordAVGEnitiy();
// enitiy.TestItemName = c[i].TestItemName;
// enitiy.Count = c[i].Count;
// enitiy.Avg = (double)c[i].Avg.Value;
// list.Add(enitiy);
// }
// var f = (from p in db.QCRecord
// where p.QCRecord_QCInfo_ID == iqcinfoID && p.TestItems.Tes_TestItems_ID == testItemsID && p.Dictionary.Dictionary_Value == "启动" && (p.QCRecord_RESULT != null && p.QCRecord_RESULT > 0)
// select p.QCRecord_RESULT).Average();
// enitiy = new QCRecordAVGEnitiy();
// enitiy.TestItemName = strTestItems_NAME;
// enitiy.Count = 0;
// enitiy.Avg = (double)f.Value;
// list.Add(enitiy);
// }
//}
#endregion
return(list);
}
catch (Exception ex)
{
list = null;
throw new Exception(string.Format("获得质检记录表中的水分各项平均值：{0}", ex.Message));
//log
}
finally { db.Connection.Close(); }
}
}
/// <summary>
/// 获得质检记录表中的重量各项总和
/// </summary>
/// <param name="iqcinfoID">质检编号</param>
/// <param name="QCRecord_QCRetest_ID">复测编号</param>
/// <returns></returns>
public static List <QCRecordAVGEnitiy> GetQCRecord_RESULT_SUM(int iqcinfoID, int iQCRecord_QCRetest_ID, string strTestItems_NAME)
{
List <QCRecordAVGEnitiy> list = new List <QCRecordAVGEnitiy>();
using (DCQUALITYDataContext db = new DCQUALITYDataContext())
{
try
{
Expression <Func <QCRecord, bool> > expr = (Expression <Func <QCRecord, bool> >)PredicateExtensionses.True <QCRecord>();
if (iqcinfoID > 0)
{
expr = expr.And(p => (p.QCRecord_QCInfo_ID == iqcinfoID));
}
if (iQCRecord_QCRetest_ID > 0)
{
expr = expr.And(p => (p.QCRecord_QCRetest_ID == iQCRecord_QCRetest_ID));
}
if (strTestItems_NAME != "")
{
int testItemsID = db.TestItems.First(d => (d.TestItems_NAME == strTestItems_NAME && d.Dictionary.Dictionary_Value == "启动")).TestItems_ID;
expr = expr.And(p => (p.TestItems.Tes_TestItems_ID == testItemsID));
}
expr = expr.And(p => (p.Dictionary.Dictionary_Value == "启动" && (p.QCRecord_RESULT != null && p.QCRecord_RESULT > 0)));
var c = (from p in db.QCRecord.Where(expr)
group p by new
{
p.TestItems.TestItems_NAME,
p.QCRecord_QCInfo_ID,
p.QCRecord_QCRetest_ID
} into g
select new
{
TestItemName = g.Key.TestItems_NAME,
Count = g.Count(),
Avg = g.Sum(p => p.QCRecord_RESULT)
}).ToList();
if (c != null)
{
QCRecordAVGEnitiy enitiy;
for (int i = 0; i < c.Count; i++)
{
enitiy = new QCRecordAVGEnitiy();
enitiy.TestItemName = c[i].TestItemName;
enitiy.Count = c[i].Count;
enitiy.Avg = (double)c[i].Avg.Value;
list.Add(enitiy);
}
}
return(list);
}
catch (Exception ex)
{
throw new Exception(string.Format("获得质检记录表中的重量各项总和：{0}", ex.Message));
}
finally { db.Connection.Close(); }
}
}
/// <summary>
/// 获得QCInfo表中的水分总值、杂质总重量、杂纸总重量的平均值
/// </summary>
/// <param name="iqcinfoID">质检编号</param>
/// <returns></returns>
public static List <QCRecordAVGEnitiy> GetQCInfo_RESULT_AVERAGE(int iqcinfoID, string CNTR_NO)
{
List <QCRecordAVGEnitiy> list = new List <QCRecordAVGEnitiy>();
using (DCQUALITYDataContext db = new DCQUALITYDataContext())
{
try
{
Expression <Func <QCInfo, bool> > expr = (Expression <Func <QCInfo, bool> >)PredicateExtensionses.True <QCInfo>();
if (iqcinfoID > 0)
{
expr = expr.And(p => (p.QCInfo_ID == iqcinfoID));
}
if (CNTR_NO != "")
{
int qcinfoID = db.QCInfo.First(d => (d.DRAW_EXAM_INTERFACE.CNTR_NO == CNTR_NO && d.Dictionary.Dictionary_Value == "启动")).QCInfo_ID;
expr = expr.And(p => (p.QCInfo_ID == qcinfoID));
}
// 平均水分：QCInfo_MOIST_PER_SAMPLE 《《《 有疑问 》》》
expr = expr.And(p => (p.Dictionary.Dictionary_Value == "启动" && (p.QCInfo_MOIST_PER_SAMPLE != null && p.QCInfo_MOIST_PER_SAMPLE > 0)));
var c = (from p in db.QCInfo.Where(expr)
group p by new // 分组
{
p.DRAW_EXAM_INTERFACE.CNTR_NO, // 车牌号
p.QCInfo_STATE, // 质检状态
p.DRAW_EXAM_INTERFACE.WEIGHT_TICKET_NO, // 磅单号
p.DRAW_EXAM_INTERFACE.PO_NO, // 采购单
p.DRAW_EXAM_INTERFACE.SHIPMENT_NO // 送货单
} into g
select new
{
CNTR_NO = g.Key.CNTR_NO, // 以车牌号为Key键
Count = g.Count(), // 每个车牌号出现的次数，及按车牌号统计数据
Sum = g.Sum(p => p.QCInfo_MOIST_PER_SAMPLE), // 总水分值
Sum1 = g.Sum(p => p.QCInfo_MATERIAL_WEIGHT), // 总杂质重量
Sum2 = g.Sum(p => p.QCInfo_PAPER_WEIGHT), // 总杂纸重量
Avg = g.Average(p => p.QCInfo_MOIST_PER_SAMPLE), // 水分平均值
Avg1 = g.Average(p => p.QCInfo_MATERIAL_WEIGHT), // 杂质重量平均值
Avg2 = g.Average(p => p.QCInfo_PAPER_WEIGHT), // 杂纸重量平均值
}).ToList();
if (c != null)
{
QCRecordAVGEnitiy enitiy;
for (int i = 0; i < c.Count; i++)
{
enitiy = new QCRecordAVGEnitiy();
enitiy.TestItemName = c[i].CNTR_NO; // 车牌号
enitiy.Count = c[i].Count; // 计算每个车牌号的车辆载重次数
enitiy.Sum = (double)c[i].Sum.Value; // 总重量
enitiy.Sum1 = (double)c[i].Sum1.Value;
enitiy.Sum2 = (double)c[i].Sum2.Value;
enitiy.Avg = (double)c[i].Avg.Value; // 平均值
enitiy.Avg1 = (double)c[i].Avg1.Value;
enitiy.Avg2 = (double)c[i].Avg2.Value;
list.Add(enitiy);
}
}
return(list);
}
catch (Exception ex)
{
return(null);
}
finally { db.Connection.Close(); }
}
}