/// <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]);
}
