//查询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(); }