/// <summary> /// 厂家 监测指标 合格率 /// </summary> /// <param name="StartDate"></param> /// <param name="EndDate"></param> /// <param name="TestRoom"></param> /// <param name="Item"></param> /// <param name="Factory"></param> /// <param name="Attribute"></param> /// <returns></returns> public static DataTable FactoryQualified( string StartDate, string EndDate, string TestRoom, string Item, string Factory, string Attribute) { string Where = " "; Where += string.IsNullOrEmpty(StartDate) ? " " : " AND BGRQ >='" + StartDate + "' "; Where += string.IsNullOrEmpty(EndDate) ? " " : " AND BGRQ <='" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' "; Where += string.IsNullOrEmpty(TestRoom) ? " " : " AND TestRoomCode in(" + TestRoom + ") "; Where += string.IsNullOrEmpty(Item) ? " " : " AND sys_TJ_MainData.ItemID ='" + Item + "' "; Where += string.IsNullOrEmpty(Factory) ? " " : " AND FactoryID in(" + Factory + ") "; // ItemCollection Attr = BLOB.Attribute(Item); Where += " AND (" + Attribute + " <> '' AND " + Attribute + " is not null ) "; return(null); }
public void Chart() { string As = "AS".RequestStr(); string StartDate = "StartDate".RequestStr(); string EndDate = "EndDate".RequestStr(); string Json = string.Empty; ItemCollection Items = BLOB.Attribute("Item".RequestStr()); if (Items.ContainsKey("型号")) { DataSet Ds = BLOB.RawAttrByModel("Item".RequestStr(), "Attr".RequestStr(), "AttrName".RequestStr(), Items["型号"].BindField, "FID".RequestStr(), StartDate, EndDate); DataTable DT = Ds.Tables[0]; DataTable TempTB; decimal SumV = 0; decimal Count = 0; int Max = 0; int Min = 0; int ii = 0; #region 时间段 string D3 = string.Empty; foreach (DataRow Dr in Ds.Tables[1].Rows) { DT.DefaultView.RowFilter = Items["型号"].BindField + "='" + Dr[0].ToString() + "'"; TempTB = DT.DefaultView.ToTable(); Count = TempTB.Rows.Count; if (Count > 0) { SumV = TempTB.Compute("sum(" + "Attr".RequestStr() + ")", "").Todecimal(); //平均值 SumV = Math.Round((SumV / Count).Todouble(), 2).Todecimal(); if (SumV == 0) { continue; } #region 标准差 double FC = 0; foreach (DataRow DrFC in TempTB.Rows) { FC += Math.Pow((DrFC[1].Todouble() - SumV.Todouble()), 2); } FC = Math.Sqrt((FC / Count.Todouble()).Todouble()).Todouble(); FC = Math.Round(FC, 2); #endregion #region 变异系数 double BY = (FC / SumV.Todouble()).Todouble() * 100; BY = Math.Round(BY, 2); #endregion D3 += D3.IsNullOrEmpty() ? "" : ","; D3 += "{\"tit\":\"" + Dr[0].ToString() + "\",\"SV\":\"" + SumV + "\", \"FC\":\"" + FC + "\", \"BY\":\"" + BY + "\",\"ZVal\":\"" + TempTB.Rows[0]["StandardValue"].ToString() + "\"}"; } } SumV = 0; Count = 0; TempTB = null; D3 = "[" + D3 + "]"; #endregion #region 月份计算 List <DateTime> Months = new List <DateTime>(); DateTime StD = StartDate.ToDateTime(); while (StD <= EndDate.ToDateTime()) { Months.Add(StD); StD = StD.AddMonths(1); } int i = 0; foreach (DateTime nDT in Months) { #region Json += i == 0 ? "" : ","; Json += "{"; Json += "\"M\":\"" + nDT.ToString("yyyy-MM") + "月份\""; foreach (DataRow Dr in Ds.Tables[1].Rows) { DT.DefaultView.RowFilter = Items["型号"].BindField + "='" + Dr[0].ToString() + "' AND bgrq>='" + nDT.ToString("yyyy-MM-dd") + "-1' AND bgrq <='" + nDT.AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd") + "'"; TempTB = DT.DefaultView.ToTable(); Count = TempTB.Rows.Count; if (Count > 0) { SumV = TempTB.Compute("sum(" + "Attr".RequestStr() + ")", "").Todecimal(); //平均值 SumV = Math.Round((SumV / Count).Todouble(), 2).Todecimal(); if (SumV == 0) { continue; } #region 标准差 double FC = 0; foreach (DataRow DrFC in TempTB.Rows) { FC += Math.Pow((DrFC[1].Todouble() - SumV.Todouble()), 2); } FC = Math.Sqrt((FC / Count.Todouble()).Todouble()).Todouble(); FC = Math.Round(FC, 2); #endregion #region 变异系数 double BY = (FC / SumV.Todouble()).Todouble() * 100; BY = Math.Round(BY, 2); #endregion Json += ",\"" + Dr[0].ToString() + "\":\"" + SumV + "\"";//bullet Json += ",\"" + Dr[0].ToString() + "-FC\":\"" + FC + "\""; Json += ",\"" + Dr[0].ToString() + "-BY\":\"" + BY + "%\""; if (ii == 0) { Max = (int)SumV + 1; Min = (int)SumV - 1; ii++; } Max = (int)SumV >= Max ? (int)SumV + 1 : Max; Min = (int)SumV <= Min ? (int)SumV - 1 : Min; } } Json += "}"; #endregion i++; } #endregion string Ms = string.Empty; foreach (DataRow Dr in Ds.Tables[1].Rows) { if (!Dr[0].ToString().IsNullOrEmpty()) { Ms += Ms.IsNullOrEmpty() ? "\"" + Dr[0].ToString() + "\"" : ",\"" + Dr[0].ToString() + "\""; } } Json = "{\"D1\":[" + Json + "],\"D2\":[" + Ms + "],\"Max\":" + Max + ",\"Min\":" + Min + ",\"D3\":" + D3 + "}"; Response.Write(Json); } Response.End(); }
/// <summary> /// 采集分析 /// </summary> /// <param name="RecordCount"></param> /// <param name="PageIndex"></param> /// <param name="PageSize"></param> /// <param name="StartDate"></param> /// <param name="EndDate"></param> /// <param name="TestRoom"></param> /// <param name="Item"></param> /// <param name="Factory"></param> /// <param name="Model"></param> /// <param name="Person"></param> /// <returns></returns> public static DataTable SamplingAnalysis(out int RecordCount, int PageIndex, int PageSize, string StartDate, string EndDate, string TestRoom, string Item, string Factory, string Model, string Person ) { #region SQL string Sort = " ORDER BY BGRQ DESC "; if (PageSize > 10000) { Sort = " ORDER BY BGRQ ASC "; } string Clomun = @" t1.description+' '+ sys_tree.description as description, BGBH, CONVERT(char(10),bgrq,23) as BGRQ , sys_TJ_Item.ItemName as 'YC', FactoryName , sys_Module.Name as 'MName', {1} , {2} as C, {3} as P ,biz_machinelist.col_norm_c6 as XH "; //{0} as XH , string From = @" sys_TJ_MainData join sys_TJ_Item on sys_TJ_Item.ItemID = sys_TJ_MainData.ItemID left outer join sys_tree on sys_tree.NodeCode = sys_TJ_MainData.testroomcode left outer join sys_tree as t1 on t1.NodeCode = left(sys_TJ_MainData.testroomcode,8) left outer join sys_Module on sys_Module.ID = sys_TJ_MainData.ModuleID left outer join biz_machinelist on biz_machinelist.scpt = {0} "; string Where = " "; Where += string.IsNullOrEmpty(StartDate) ? " " : " AND BGRQ >='" + StartDate + "' "; Where += string.IsNullOrEmpty(EndDate) ? " " : " AND BGRQ <='" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' "; Where += string.IsNullOrEmpty(TestRoom) ? " " : " AND TestRoomCode in(" + TestRoom + ") "; Where += string.IsNullOrEmpty(Item) ? " " : " AND sys_TJ_MainData.ItemID ='" + Item + "' "; Where += string.IsNullOrEmpty(Factory) ? " " : " AND FactoryID ='" + Factory + "' "; ItemCollection Attr = BLOB.Attribute(Item); Where += string.IsNullOrEmpty(Model) ? " " : Attr["设备型号"] == null ? "" : " AND " + Attr["设备型号"].BindField + " = '" + Model + "' "; Where += string.IsNullOrEmpty(Person) ? " " : Attr["试验员"] == null ? "" : " AND " + Attr["试验员"].BindField + " = '" + Person + "' "; string Temp = string.Empty; int cc = 1; foreach (Item K in Attr) { if ((K.ItemName.IndexOf("抗拉") > -1) || K.ItemName.IndexOf("强度值") > -1) { Temp += Temp.IsNullOrEmpty() ? K.BindField + " as Val" + cc : " ," + K.BindField + " as Val" + cc; cc++; } } Clomun = string.Format(Clomun, Attr.ContainsKey("设备型号") ? Attr["设备型号"].BindField : " ' '", !Temp.IsNullOrEmpty() ? Temp : " ' '", Attr.ContainsKey("代表数量") ? Attr["代表数量"].BindField : " ' '", Attr.ContainsKey("试验人员") ? Attr["试验人员"].BindField : " ' '" ); From = string.Format(From, Attr.ContainsKey("设备型号") ? Attr["设备型号"].BindField : " ' '"); #endregion Attr.Clear(); Attr = null; RecordCount = 0; string Sql = Expand.SqlPage(PageIndex, PageSize, "DataID", From, Where, Sort, Clomun); DataSet Ds = GetDataSet(Sql); if (Ds.Tables.Count < 1) { return(new DataTable()); } RecordCount = int.Parse(Ds.Tables[1].Rows[0][0].ToString()); return(Ds.Tables[0]); }
/// <summary> /// 施工部位的原材料使用情况 /// </summary> /// <param name="RecordCount"></param> /// <param name="PageIndex"></param> /// <param name="PageSize"></param> /// <param name="StartDate"></param> /// <param name="EndDate"></param> /// <param name="TestRoom"></param> /// <param name="Item"></param> /// <returns></returns> public static DataTable ItemPosition(out int RecordCount, int PageIndex, int PageSize, string StartDate, string EndDate, string TestRoom, string Item ) { #region SQL string Sql = @" DECLARE @Page int DECLARE @PageSize int SET @Page = {1} SET @PageSize = {2} SET NOCOUNT ON DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(200)) INSERT INTO @TempTable ( _keyID ) select sys_TJ_MainData.DataID from [dbo].[sys_TJ_MainData] join [dbo].[sys_TJ_Item_Module] on [dbo].[sys_TJ_MainData].ModuleID = [dbo].[sys_TJ_Item_Module].ModuleID WHERE 1=1 {0} SELECT sys_TJ_MainData.FactoryName as '生产厂家', sys_TJ_Item.ItemName as '原材', {3} as '型号', sys_TJ_MainData.SGBW as '工程部位', sys_TJ_MainData.BGRQ as '报告日期', sys_TJ_MainData.BGBH as '报告编号' from sys_TJ_MainData JOIN sys_TJ_Item_Module on sys_TJ_MainData.ModuleID = sys_TJ_Item_Module.ModuleID Join sys_TJ_Item on sys_TJ_Item.ItemID = sys_TJ_Item_Module.ItemID INNER JOIN @TempTable t ON sys_TJ_MainData.DataID = t._keyID WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) {0} Order By BGRQ Desc DECLARE @C int select @C= count(DataID) from [dbo].[sys_TJ_MainData] join [dbo].[sys_TJ_Item_Module] on [dbo].[sys_TJ_MainData].ModuleID = [dbo].[sys_TJ_Item_Module].ModuleID where 1=1 {0} select @C "; #endregion string Where = " "; Where += string.IsNullOrEmpty(StartDate) ? " " : " AND BGRQ >='" + StartDate + "' "; Where += string.IsNullOrEmpty(EndDate) ? " " : " AND BGRQ <='" + DateTime.Parse(EndDate).AddDays(1).ToShortDateString() + "' "; Where += string.IsNullOrEmpty(TestRoom) ? " " : " AND TestRoomCode in(" + TestRoom + ") "; Where += string.IsNullOrEmpty(Item) ? " " : " AND sys_TJ_MainData.ItemID ='" + Item + "' "; ItemCollection Attr = BLOB.Attribute(Item); Sql = string.Format(Sql, Where, PageIndex, PageSize, Attr.ContainsKey("型号") ? Attr["型号"].BindField : " ' ' " ); Attr.Clear(); Attr = null; RecordCount = 0; BLL_Document BLL = new BLL_Document(); DataSet Ds = BLL.GetDataSet(Sql); RecordCount = int.Parse(Ds.Tables[1].Rows[0][0].ToString()); return(Ds.Tables[0]); }
/// <summary> /// 试验检测数据分析 /// </summary> /// <param name="RecordCount"></param> /// <param name="PageIndex"></param> /// <param name="PageSize"></param> /// <param name="StartDate">起始日期</param> /// <param name="EndDate">结束日期</param> /// <param name="TestRoom">试验室</param> /// <param name="Item">原材</param> /// <param name="Factory">厂家</param> /// <param name="Attribute">检测指标</param> /// <param name="Model">型号</param> /// <returns></returns> public static DataTable DataAnalysis(out int RecordCount, int PageIndex, int PageSize, string StartDate, string EndDate, string TestRoom, string Item, string Factory, string Attribute, string AttributeName, string Model ) { #region SQL string Sort = " ORDER BY BGRQ DESC "; if (PageSize > 10000) { Sort = " ORDER BY BGRQ ASC "; } string Clomun = @" sys_tree.description , BGBH,DataID, CONVERT(char(10),bgrq,23) as BGRQ , sys_TJ_Item.ItemName as 'YC', FactoryName , sys_Module.Name as 'MName', {0} as XH , sys_TJ_StandardValue.StandardValue as ZVal, {1} as Val, '' as BVal "; string From = @" sys_TJ_MainData join sys_TJ_Item on sys_TJ_Item.ItemID = sys_TJ_MainData.ItemID left outer join sys_tree on sys_tree.NodeCode = sys_TJ_MainData.testroomcode left outer join sys_Module on sys_Module.ID = sys_TJ_MainData.ModuleID left outer join sys_TJ_StandardValue on sys_TJ_StandardValue.ItemID = sys_TJ_MainData.ItemID AND (sys_TJ_StandardValue.Model = {0} OR '/' = sys_TJ_StandardValue.Model ) AND sys_TJ_StandardValue.ItemName = '{1}' AND sys_TJ_StandardValue.ModuleID = sys_TJ_MainData.ModuleID"; string Where = " "; Where += string.IsNullOrEmpty(StartDate) ? " " : " AND BGRQ >='" + StartDate + "' "; Where += string.IsNullOrEmpty(EndDate) ? " " : " AND BGRQ <='" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' "; Where += string.IsNullOrEmpty(TestRoom) ? " " : " AND TestRoomCode in(" + TestRoom + ") "; Where += string.IsNullOrEmpty(Item) ? " " : " AND sys_TJ_MainData.ItemID ='" + Item + "' "; Where += string.IsNullOrEmpty(Factory) ? " " : " AND FactoryID ='" + Factory + "' "; ItemCollection Attr = BLOB.Attribute(Item); Where += string.IsNullOrEmpty(Model) ? " " : Attr["型号"] == null ? "" : " AND " + Attr["型号"].BindField + " = '" + Model + "' "; Where += " AND (" + Attribute + " <> '' AND " + Attribute + " is not null ) "; Clomun = string.Format(Clomun, Attr.ContainsKey("型号") ? Attr["型号"].BindField : " ' '", Attribute); From = string.Format(From, Attr.ContainsKey("型号") ? Attr["型号"].BindField : " ' '", AttributeName); #endregion Attr.Clear(); Attr = null; RecordCount = 0; string Sql = Expand.SqlPage(PageIndex, PageSize, "DataID", From, Where, Sort, Clomun); DataSet Ds = GetDataSet(Sql); if (Ds.Tables.Count < 1) { return(new DataTable()); } RecordCount = int.Parse(Ds.Tables[1].Rows[0][0].ToString()); foreach (DataRow Dr in Ds.Tables[0].Rows) { Dr["BVal"] = StandardDeviation(Dr["ZVal"].ToString(), Dr["Val"].ToString()); } return(Ds.Tables[0]); }