//查询sync表
private void SelectSync(object parameter)
{
try
{
SyncInfos.Clear();
// 获取已选择的任务
List_AllChoose.Clear();
TaskInfos.ForEach((x) =>
{
if (x.IsChecked)
{
List_AllChoose.Add(x.ServiceId);
}
});
if (List_AllChoose.Count == 0)
{
Notice.Show("请至少选择一项任务搜索", "错误", 3, MessageBoxIcon.Error);
return;
}
// 已选择的任务和park_Base的表取交集
List_ParkBaseChoose = List_ParkBase.Intersect(List_AllChoose).ToList <string>();
// 总的已选择的和选择的park_Base取差集
List_SyncChoose = List_AllChoose.Except(List_ParkBaseChoose).ToList <string>();
string sql_xmpp = $"SELECT AddTime,protocoldata,status,remark,failmessage,updatetime from sync_xmpp_history ";
string sql_base = $"SELECT AddTime,protocoldata,status,remark from sync_park_base_history ";
//根据protocol数据筛选查询 不允许为空
if (!string.IsNullOrEmpty(SelectLike))
{
if (SelectLike.Trim() == "")
{
Notice.Show("筛选数据条件不允许为空", "错误", 3, MessageBoxIcon.Error);
return;
}
sql_xmpp += "where protocoldata like '%" + SelectLike + "%' ";
sql_base += "where protocoldata like '%" + SelectLike + "%' ";
}
else
{
Notice.Show("筛选数据条件不允许为空", "错误", 3, MessageBoxIcon.Error);
return;
}
//根据时间筛选查询
if (EndTime < StartTime)
{
Notice.Show("结束时间不能比开始时间早", "错误", 3, MessageBoxIcon.Error);
return;
}
sql_xmpp += "and addtime between '" + StartTime.ToString() + "' and '" + EndTime.ToString() + "' ";
sql_base += "and addtime between '" + StartTime.ToString() + "' and '" + EndTime.ToString() + "' ";
//根据任务筛选in
string xmpptask = "";
if (List_SyncChoose.Count >= 1)
{
List_SyncChoose.ForEach((x) =>
{
xmpptask += "'" + x + "',";
});
xmpptask = xmpptask.Substring(0, xmpptask.Length - 1);
}
else
{
xmpptask = "''";
}
sql_xmpp += "and serviceid in (" + xmpptask + ") ";
string basetask = "";
if (List_ParkBaseChoose.Count >= 1)
{
List_ParkBaseChoose.ForEach((x) =>
{
basetask += "'" + x + "',";
});
basetask = basetask.Substring(0, basetask.Length - 1);
}
else
{
basetask = "''";
}
sql_base += "and serviceid in (" + basetask + ") ";
//根据ID排序
sql_xmpp += "ORDER BY id desc ";
sql_base += "ORDER BY id desc ";
//根据limit限制
if (limit10)
{
sql_xmpp += "limit 10 "; sql_base += "limit 10 ";
}
else if (limit20)
{
sql_xmpp += "limit 20 "; sql_base += "limit 20 ";
}
else if (limit50)
{
sql_xmpp += "limit 50 "; sql_base += "limit 50";
}
else if (limit100)
{
sql_xmpp += "limit 100 "; sql_base += "limit 100 ";
}
else
{
return;
}
//sync_xmpp_history表里查询
LogHelper.CommLogger.Info("查询上传执行的SQL语句(sync_xmpp_history):" + sql_xmpp.Replace(",failmessage,updatetime", ""));
DataTable dt = MySqlHelper.ExecuteDataset(EnvironmentInfo.ConnectionString, sql_xmpp.Replace(",failmessage,updatetime", "")).Tables[0];//获取所有的表
foreach (DataRow dr in dt.Rows)
{
SyncInfo syncinfo = new SyncInfo
{
AddTime = dr["AddTime"].ToString(),
ProtocolData = dr["protocoldata"].ToString(),
status = dr["status"].ToString(),
remark = dr["remark"].ToString(),
failmessage = "",
updatetime = ""
};
SyncInfos.Add(syncinfo);
}
//sync_xmpp表里查询
string sql2 = sql_xmpp.Replace("sync_xmpp_history", "sync_xmpp");
LogHelper.CommLogger.Info("查询上传执行的SQL语句(sync_xmpp):" + sql2);
DataTable dt2 = MySqlHelper.ExecuteDataset(EnvironmentInfo.ConnectionString, sql2).Tables[0];//获取所有的表
foreach (DataRow dr in dt2.Rows)
{
SyncInfo syncinfo = new SyncInfo
{
AddTime = dr["AddTime"].ToString(),
ProtocolData = dr["protocoldata"].ToString(),
status = dr["status"].ToString(),
remark = dr["remark"].ToString(),
failmessage = dr["failmessage"].ToString(),
updatetime = dr["updatetime"].ToString()
};
SyncInfos.Add(syncinfo);
}
// 在sync_park_base_history表里查询
// 怎么sync_park_base_history结构还和sync_xmpp不一样的
LogHelper.CommLogger.Info("查询上传执行的SQL语句(sync_park_base_history):" + sql_base);
DataTable dt3 = MySqlHelper.ExecuteDataset(EnvironmentInfo.ConnectionString, sql_base).Tables[0];//获取所有的表
foreach (DataRow dr in dt3.Rows)
{
SyncInfo syncinfo = new SyncInfo
{
AddTime = dr["AddTime"].ToString(),
ProtocolData = dr["protocoldata"].ToString(),
status = dr["status"].ToString(),
remark = dr["remark"].ToString(),
failmessage = "",
updatetime = ""
};
SyncInfos.Add(syncinfo);
}
if (SyncInfos.Count == 0)
{
Notice.Show("搜索完成，未找到结果", "提示", 3, MessageBoxIcon.Info);
}
else
{
Notice.Show("搜索完成，共计找到" + SyncInfos.Count + "条结果", "提示", 3, MessageBoxIcon.Info);
}
GC.Collect();
}
catch (Exception ex)
{
LogHelper.CommLogger.Error(ex.ToString());
throw;
}
}
/// <summary>
/// 全选
/// </summary>
/// <param name="parameter"></param>
private void ChooseAll(object parameter)
{
TaskInfos.ForEach(x => x.IsChecked = IsSelectedAll);
//WriteConfig();
}