/// <summary>
/// 通过存储过程获取CGL不良信息及不良条数
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public List <AIORAA> GetCGLNgInfoByProc(DateTime startDate, DateTime endDate)
{
aIORAAs = new List <AIORAA>();
int MaxNgCount = 0;
sampleRaas = new List <AIORAA>();
OracleParameter[] oracleParameters = new OracleParameter[]
{
new OracleParameter("startDate", OracleDbType.Varchar2),
new OracleParameter("endDate", OracleDbType.Varchar2),
new OracleParameter("rs", OracleDbType.RefCursor, ParameterDirection.Output),
new OracleParameter("NGCOUNT", OracleDbType.Int32, ParameterDirection.Output)
};
oracleParameters[0].Value = startDate.GetDateTimeFormats()[99];
oracleParameters[1].Value = endDate.GetDateTimeFormats()[99];
DataSet dataSet = MemoryCacheUtil.GetOrAddCacheItem(startDate.ToShortDateString() + endDate.ToShortDateString(),
() =>
{
ReadingDb(100, 2); //进入数据库访问时初始化一个进度到窗口
return(OraHelper.GetDataSet("pkg_aio.proc_GetCGL_NG_INFO", oracleParameters, CommandType.StoredProcedure));
}, "cglNgInfo");
aIORAAs = Util.ToList <AIORAA>(dataSet.Tables[0]);
MaxNgCount = MemoryCacheUtil.GetOrAddCacheItem(startDate.ToShortDateString() + endDate.ToShortDateString() + "MaxNgCount",
() => Convert.ToInt32(oracleParameters[3].Value.ToString()), "MaxNgCount");
string snIn = "";
//for (int i = 0; i < aIORAAs.Count; i++)
//{
// snIn+= aIORAAs[i].SN.Substring(36, 17)+"'";
// aIORAAs[i] = GetMachineInfo(aIORAAs[i]);
// processCount++;
//}
int pageSize = 1000;
int totalPage = MaxNgCount % pageSize == 0 ? MaxNgCount / pageSize : MaxNgCount / pageSize + 1;
if (MemoryCacheUtil.GetCacheItem <object>(startDate.ToShortDateString() + endDate.ToShortDateString() + "completeCglNg") == null)
{
for (int i = 0; i < totalPage; i++)
{
List <string> ss = (aIORAAs.Skip(i * pageSize).Take(pageSize)).Select(p => "'" + p.SN.Substring(36, 17) + "'").ToList();
snIn = string.Join(",", ss);
GetMachineInfo(snIn, aIORAAs);
ReadingDb(totalPage, i + 1);
}
}
else
{
ReadingDb(100, 100);//如果缓存中有数据直接将进度条置为 100%
}
sampleRaas = MemoryCacheUtil.GetOrAddCacheItem(startDate.ToShortDateString() + endDate.ToShortDateString() + "completeCglNg",
() => { return(sampleRaas); }, "completeCglNg");
return(sampleRaas);
}
/// <summary>
/// 获取CGL不良信息
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public List <AIORAA> GetCGLNg(string startDate, string endDate)
{
StringBuilder sqlsb = new StringBuilder(@"select count(1) from shqt.FOOLPROOF_STATION_ALLINONE_NEW where STATION_NAME='All-In-One' and WORK_TIME between to_date('{0}','YYYY-MM-DD HH24:MI:SS')
and to_date('{1}','YYYY-MM-DD HH24:MI:SS') and SENSOR_RESULT<>'OK'");
sqlsb.Append(@"select sn,STATION_IP,SENSOR_RESULT,WORK_TIME,SPC_G_P1,SPC_F_P2,SPC_AS_P3,SPC_E_P4,SPC_D_P5,SPC_C_P6,SPC_B_P9,SPC_A_P7,SPC_H_P8 ");
sqlsb.Append(@" from shqt.FOOLPROOF_STATION_ALLINONE_NEW where STATION_NAME='All-In-One' and WORK_TIME between to_date('{2}','YYYY-MM-DD HH24:MI:SS')
and to_date('{3}','YYYY-MM-DD HH24:MI:SS') and SENSOR_RESULT<>'OK'");
aIORAAs = new List <AIORAA>();
string sql = string.Format(sqlsb.ToString(), startDate, endDate, startDate, endDate);
OracleDataReader reader = null;
try
{
using (reader = OraHelper.GetDataReader(string.Format(sqlsb.ToString(), startDate, endDate, startDate, endDate)))
{
while (reader.Read())
{
//aIORAAs.Add(GetMachineInfo(new AIORAA()
//{
// SN = reader["sn"].ToString(),
// Work_Time = Convert.ToDateTime(reader["WORK_TIME"]),
// SENSOR_RESULT = reader["SENSOR_RESULT"].ToString(),
// STATION_IP = reader["STATION_IP"].ToString(),
// SPC_G_P1 = Convert.ToDouble(reader["SPC_G_P1"]),
// SPC_F_P2 = Convert.ToDouble(reader["SPC_F_P2"]),
// SPC_AS_P3 = Convert.ToDouble(reader["SPC_AS_P3"]),
// SPC_E_P4 = Convert.ToDouble(reader["SPC_E_P4"]),
// SPC_D_P5 = Convert.ToDouble(reader["SPC_D_P5"]),
// SPC_C_P6 = Convert.ToDouble(reader["SPC_C_P6"]),
// SPC_B_P9 = Convert.ToDouble(reader["SPC_B_P9"]),
// SPC_A_P7 = Convert.ToDouble(reader["SPC_A_P7"]),
// SPC_H_P8 = Convert.ToDouble(reader["SPC_H_P8"])
//}));
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (!reader.IsClosed)
{
reader.Close();
}
}
return(aIORAAs);
}