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