/// <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(); } } }
public static List <QCRecordAVGEnitiy> GetQCInfo_RESULT_AVG(Expression <Func <View_QCInfo_InTerface, bool> > expr) { List <QCRecordAVGEnitiy> list = new List <QCRecordAVGEnitiy>(); using (DCQUALITYDataContext db = new DCQUALITYDataContext()) { try { expr = (Expression <Func <View_QCInfo_InTerface, bool> >)PredicateExtensionses.True <View_QCInfo_InTerface>(); var c = (from p in db.View_QCInfo_InTerface.Where(expr) group p by new // 分组 { p.CNTR_NO, // 车牌号 p.WEIGHT_TICKET_NO, // 磅单号 p.PO_NO, // 采购单 p.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; //enitiy.BeginTime = (DateTime)c[i].BeginTime; // 日期时间 //enitiy.EndTime = (DateTime)c[i].EndTime; list.Add(enitiy); } } return(list); } catch (Exception ex) { throw new Exception(string.Format("获得质检记录表中的水分各项平均值:{0}", ex.Message)); } finally { db.Connection.Close(); } } }
/// <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> /// 获得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(); } } }