public ReturnResultEntity <PageResultEntity <ShiJuanEntity> > GetEnableShiJuanByCondition(ShiJuanSearchEntity shiJuanSearchEntity)
{
ReturnResultEntity <PageResultEntity <ShiJuanEntity> > returnMessageEntity = new ReturnResultEntity <PageResultEntity <ShiJuanEntity> >();
List <ShiJuanEntity> listShiJuan = new List <ShiJuanEntity>();
int nRowCount = 0;
StringBuilder sbWhere = new StringBuilder();
sbWhere.Append(" where is_deleted=0 ");
if (shiJuanSearchEntity.shiJuanName != null)
{
sbWhere.Append(string.Format(" and shi_juan_name like'%{0}%' ", shiJuanSearchEntity.shiJuanName));
}
if (shiJuanSearchEntity.beginTime != null)
{
sbWhere.Append(string.Format(" and c_t >='{0}' ", shiJuanSearchEntity.beginTime));
}
if (shiJuanSearchEntity.endTime != null)
{
sbWhere.Append(string.Format(" and c_t <='{0}' ", shiJuanSearchEntity.endTime));
}
string strSqlRowCount = "select count(1) rowCount from shi_juan " + sbWhere.ToString();
object objRowCount = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteScalar(strSqlRowCount);
if (objRowCount != null)
{
nRowCount = Convert.ToInt32(objRowCount);
}
string strLimit = string.Format(" order by id limit {0},{1}",
shiJuanSearchEntity.pageIndex * shiJuanSearchEntity.pageSize, shiJuanSearchEntity.pageSize);
string strSql = "select *from shi_juan" + sbWhere.ToString() + strLimit;
DataTable dtShiJuan = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteTable(strSql);
if (dtShiJuan != null && dtShiJuan.Rows.Count > 0)
{
foreach (DataRow dr in dtShiJuan.Rows)
{
int id = dr["id"] == DBNull.Value ? 0 : Convert.ToInt32(dr["id"].ToString());
string shiJuanName = dr["shi_juan_name"] == DBNull.Value ? string.Empty : dr["shi_juan_name"].ToString();
int isDeleted = dr["is_deleted"] == DBNull.Value ? 0 : Convert.ToInt32(dr["is_deleted"].ToString());
DateTime dtCt = dr["c_t"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(dr["c_t"].ToString());
DateTime dtUt = dr["u_t"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(dr["u_t"].ToString());
ShiJuanEntity shiJuanEntity = new ShiJuanEntity();
shiJuanEntity.id = id;
shiJuanEntity.shiJuanName = shiJuanName;
shiJuanEntity.isDeleted = isDeleted;
shiJuanEntity.cT = dtCt;
shiJuanEntity.uT = dtUt;
listShiJuan.Add(shiJuanEntity);
}
}
PageResultEntity <ShiJuanEntity> pageResultEntity = new PageResultEntity <ShiJuanEntity>();
pageResultEntity.rowCount = nRowCount;
pageResultEntity.dataList = listShiJuan;
returnMessageEntity.success = true;
returnMessageEntity.data = pageResultEntity;
return(returnMessageEntity);
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public PageResultEntity GetPageData(string sql)
{
DataSet dt = _currentDAO.GetGridData(sql);
PageResultEntity r = new PageResultEntity();
r.GridData = dt.Tables[0];
r.RowsCount = Convert.ToInt32(dt.Tables[1].Rows[0][0]);
r.PageCount = Convert.ToInt32(dt.Tables[1].Rows[0][1]);
return(r);
}
private async void doSearch(string strUrl, string strJsonPar)
{
try
{
List <ShiTiEntity> listShiTi = new List <ShiTiEntity>();
int nRowCount = 0;
var stringContent = new StringContent(strJsonPar, Encoding.UTF8, "application/json");
var response = await client.PostAsync(strUrl, stringContent);
if (response.IsSuccessStatusCode)
{
string strJsonRtn = response.Content.ReadAsStringAsync().Result;
//List < ShiJuanEntity > listShiJuan
ReturnResultEntity <PageResultEntity <ShiTiEntity> > returnResultEntity = JsonHelper.FromJson <ReturnResultEntity <PageResultEntity <ShiTiEntity> > >(strJsonRtn);
if (returnResultEntity != null)
{
if (returnResultEntity.success)
{
PageResultEntity <ShiTiEntity> pageResultEntity = returnResultEntity.data;
listShiTi = pageResultEntity.dataList;
nRowCount = pageResultEntity.rowCount;
}
else
{
MessageBox.Show("查询失败," + returnResultEntity.errorMsg);
}
}
#region 初始化
dgvShiTi.DataSource = null;
lblAllPageNum.Text = "共0页";
lblRowNums.Text = "0道试题";
txtCurrentPage.Text = "1";
#endregion 初始化
dgvShiTi.DataSource = listShiTi.ToArray();
nPageCount = (nRowCount + (nPageSize - 1)) / nPageSize;
lblAllPageNum.Text = "共" + nPageCount.ToString() + "页";
lblRowNums.Text = nRowCount.ToString() + "道试题";
txtCurrentPage.Text = (nPageIndex + 1).ToString();
}
else
{
MessageBox.Show("Post查询全部试题失败，调用WebApi出错了");
}
}
catch (Exception ex)
{
MessageBox.Show("查询全部试题异常，" + ex.Message);
}
}
public ReturnResultEntity <PageResultEntity <ShiTiEntity> > GetEnableShiTiByCondition(ShiTiSearchEntity shiTiSearchEntity)
{
ReturnResultEntity <PageResultEntity <ShiTiEntity> > returnMessageEntity = new ReturnResultEntity <PageResultEntity <ShiTiEntity> >();
if (shiTiSearchEntity == null)
{
returnMessageEntity.success = false;
returnMessageEntity.errorMsg = "传入参数为null";
return(returnMessageEntity);
}
List <ShiTiEntity> listShiTi = new List <ShiTiEntity>();
int nRowCount = 0;
StringBuilder sbWhere = new StringBuilder();
sbWhere.Append(" where is_deleted=0 ");
if (shiTiSearchEntity.shiTiId > 0)
{
sbWhere.Append(string.Format(" and id ={0} ", shiTiSearchEntity.shiTiId));
}
else
{
if (shiTiSearchEntity.shiJuanId > 0)
{
sbWhere.Append(string.Format(" and shi_juan_id ={0} ", shiTiSearchEntity.shiJuanId));
}
if (shiTiSearchEntity.tiXing != null && shiTiSearchEntity.tiXing != "全部")
{
sbWhere.Append(string.Format(" and ti_xing ='{0}' ", shiTiSearchEntity.tiXing));
}
if (shiTiSearchEntity.zhengWen != null)
{
sbWhere.Append(string.Format(" and zheng_wen like'%{0}%' ", shiTiSearchEntity.zhengWen));
}
}
string strSqlRowCount = "select count(1) rowCount from shi_ti " + sbWhere.ToString();
object objRowCount = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteScalar(strSqlRowCount);
if (objRowCount != null)
{
nRowCount = Convert.ToInt32(objRowCount);
}
string strLimit = string.Format(" order by id limit {0},{1}",
shiTiSearchEntity.pageIndex * shiTiSearchEntity.pageSize, shiTiSearchEntity.pageSize);
string strSql = "select *from shi_ti" + sbWhere.ToString() + strLimit;
DataTable dtShiJuan = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteTable(strSql);
if (dtShiJuan != null && dtShiJuan.Rows.Count > 0)
{
foreach (DataRow dr in dtShiJuan.Rows)
{
ShiTiEntity shiTiEntity = new ShiTiEntity();
shiTiEntity = ConvertDataRowToShiTiEntity(dr);
listShiTi.Add(shiTiEntity);
}
}
PageResultEntity <ShiTiEntity> pageResultEntity = new PageResultEntity <ShiTiEntity>();
pageResultEntity.rowCount = nRowCount;
pageResultEntity.dataList = listShiTi;
returnMessageEntity.success = true;
returnMessageEntity.data = pageResultEntity;
return(returnMessageEntity);
}
private void DoWork()
{
while (true)
{
try
{
ConfigurationManager.RefreshSection("appSettings");
var time = ConfigurationManager.AppSettings["Timing"];
TimeSpan.TryParse(time, out _timeSpan);
if (DateTime.Now.TimeOfDay.Hours == _timeSpan.Hours)
{
//支持配置多客户
string[] customerIDs = ConfigurationManager.AppSettings["CustomerIDs"].Split(',');
foreach (string customerID in customerIDs)
{
loggingSessionInfo = Default.GetBSLoggingSession(customerID, "1");
var eventsService = new LEventsBLL(loggingSessionInfo);
LEventsEntity queryEntity = new LEventsEntity();
queryEntity.EventType = 0;
var data = eventsService.WEventGetWebEvents(queryEntity, 0, 100000000);
foreach (var item in data)
{
XieHuiBaoBLL b = new XieHuiBaoBLL(loggingSessionInfo, "vip");
List <DefindControlEntity> l = new List <DefindControlEntity>();
PageResultEntity pageEntity = null;
try
{
pageEntity = b.GetPageDataByEventID(l, 100000000, 0, item.EventID);
}
catch (Exception e)
{
Loggers.Debug(new DebugLogInfo
{
Message = string.Format("{0} {1} (EventID:{3})报名人员信息通知邮件发送{2}", DateTime.Now.ToShortDateString(), DateTime.Now.TimeOfDay, "失败", item.EventID)
});
Loggers.Exception(new BasicUserInfo(), e);
continue;
}
if (pageEntity.GridData != null)
{
if (pageEntity.GridData.Rows.Count == 0)
{
continue;
}
}
GridInitEntity g = GetInitGridDataByEventID(item.EventID);
if (pageEntity != null && pageEntity.GridData != null)
{
#region 替换标题信息
if (g != null && g.GridColumnDefinds != null)
{
if (pageEntity.GridData.Columns.Contains("ROW_NUMBER"))
{
pageEntity.GridData.Columns.Remove("ROW_NUMBER");
}
if (pageEntity.GridData.Columns.Contains("SignUpID"))
{
pageEntity.GridData.Columns.Remove("SignUpID");
}
if (pageEntity.GridData.Columns.Count == 0)
{
continue;
}
for (int i = 0; i < pageEntity.GridData.Columns.Count; i++)
{
for (int j = 0; j < g.GridColumnDefinds.Count; j++)
{
if (pageEntity.GridData.Columns[i].ColumnName.ToLower() == g.GridColumnDefinds[j].DataIndex.ToLower())
{
pageEntity.GridData.Columns[i].ColumnName = g.GridColumnDefinds[j].ColumnText;
break;
}
}
}
}
#endregion
}
//数据获取
Workbook wb = DataTableExporter.WriteXLS(pageEntity.GridData, 0);
wb.Worksheets[0].Name = "参加活动人员信息";
string savePath = ConfigurationManager.AppSettings["FileURL"];
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
savePath = savePath + "/" + item.Title + DateTime.Now.ToString("yyyyMMdd") + ".xls";
wb.Save(savePath);//保存Excel文件
#region 邮件发送
FromSetting fs = new FromSetting();
fs.SMTPServer = ConfigurationManager.AppSettings["SmtpServer"];
fs.SendFrom = ConfigurationManager.AppSettings["MailSendFrom"];
fs.UserName = ConfigurationManager.AppSettings["MailUserName"];
fs.Password = ConfigurationManager.AppSettings["MailUserPassword"];
string mailTo = ConfigurationManager.AppSettings["MailTo"];
string isSendMail = ConfigurationManager.AppSettings["IsSendMailToCreator"];
//是否发送成功
bool result = false;
if (!string.IsNullOrEmpty(mailTo))
{
result = Mail.SendMail(fs, mailTo, ConfigurationManager.AppSettings["MailTitle"], ConfigurationManager.AppSettings["Subject"], savePath.Split(','));
}
if (isSendMail == "1")
{
if (!string.IsNullOrEmpty(item.Email))
{
if (DateTime.Now < DateTime.Parse(item.EndTime))
{
//等于0表示不发送邮件
if (item.MailSendInterval == null || item.MailSendInterval == 0)
{
continue;
}
//日期差与间隔时间取余判断是否符合发送邮件日期
int dateDiff = ((TimeSpan)(DateTime.Now.Date - item.LastUpdateTime.Value.Date)).Days;
if (dateDiff % item.MailSendInterval != 0)
{
continue;
}
result = Mail.SendMail(fs, item.Email, ConfigurationManager.AppSettings["MailTitle"], ConfigurationManager.AppSettings["Subject"], savePath.Split(','));
Loggers.Debug(new DebugLogInfo
{
Message = string.Format("{0} {1} (EventID:{3})报名人员信息通知邮件发送{2}", DateTime.Now.ToShortDateString(), DateTime.Now.TimeOfDay, result ? "成功" : "失败", item.EventID)
});
}
}
}
#endregion
}
}
}
}
catch (Exception ex)
{
Loggers.Exception(new BasicUserInfo(), ex);
}
Thread.Sleep(new TimeSpan(0, 1, 0, 0));
}
}
