public string GetAdvertMenuIds(int mid)
{
try
{
string ids = string.Empty;
StringBuilder sbSql4org = new StringBuilder();
List <SqlParameter> List = new List <SqlParameter>();
sbSql4org.Append(@"select id from PortalTreeData where pid=(
select pid from [dbo].[PortalTreeData] where id=@mid)
And Display=@Display And (BeforeAfter=@BA or BeforeAfter=@BeforeAfter)");
List.Add(new SqlParameter("@mid", mid));
List.Add(new SqlParameter("@Display", ((int)Display.显示).ToString()));
List.Add(new SqlParameter("@BA", ((int)BeforeAfter.前后台展示).ToString()));
List.Add(new SqlParameter("@BeforeAfter", ((int)BeforeAfter.前台展示).ToString()));
DataTable dt = SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, List.ToArray());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
ids += row["Id"] + ",";
}
if (!string.IsNullOrWhiteSpace(ids))
{
ids = ids.Substring(0, ids.Length - 1);
}
}
return(ids);
}
catch (Exception ex)
{
return(string.Empty);
}
}
/// <summary>
/// 获得首页左侧导航处菜单信息
/// </summary>
public DataTable GetLeftNavigationMenu(string useridcard, string pid)
{
StringBuilder sbSql4org;
List <SqlParameter> pms = new List <SqlParameter>();
sbSql4org = new StringBuilder();
if (useridcard == "00000000000000000X")//如果是默认最大权限的管理员
{
sbSql4org.Append(@"select distinct mi.* from Plat_SysOfMenu_Rel sm
inner join ModelManage mi on sm.MenuId=mi.Id and mi.Id is not null and mi.IsMenu=0 and mi.isShow=3
where mi.Pid=@Pid ");
}
else
{
sbSql4org.Append(@"select distinct mi.* from Plat_RoleOfUser ru
inner join Plat_RoleOfMenu rm on ru.RoleId=rm.RoleId
inner join ModelManage mi on mi.Id=rm.MenuId
where mi.Id is not null and mi.IsMenu=0 and mi.Pid=@Pid and mi.isShow=3 and ru.UserIDCard=@UserIDCard ");
}
sbSql4org.Append(" order by mi.Id ");
pms.Add(new SqlParameter("@UserIDCard", useridcard));
pms.Add(new SqlParameter("@Pid", pid));
return(SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, pms.ToArray()));
}
public DataTable GetSubButtonByUrl(string purl, string uniqueNo)
{
List <SqlParameter> pms = new List <SqlParameter>();
string sql = string.Empty;
if (uniqueNo == "00000000000000000X")//如果是默认最大权限的管理员
{
sql = @"select distinct mi.*
,(select STUFF((select ','+CAST(ISNULL(mbtn.ID,0)AS NVARCHAR(MAX))+'|'+ CAST(ModelName AS NVARCHAR(MAX))+'|'+ CAST(iconCss AS NVARCHAR(MAX)) from ModelManage mbtn
where Pid = mi.Id and IsMenu = 1 ORDER By ModelName FOR xml path('')), 1, 1, '')) as ButtonField
from ModelManage mi
where 1=1 and mi.Url=@Url ";
}
else
{
sql = @" select distinct mi.*,(select STUFF((select ','+CAST(ISNULL(mbtn.ID,0)AS NVARCHAR(MAX))+'|'+ CAST(ModelName AS NVARCHAR(MAX))+'|'+ CAST(iconCss AS NVARCHAR(MAX)) from ModelManage mbtn
where Pid = mi.Id and IsMenu = 1 ORDER By ModelName FOR xml path('')), 1, 1, '')) as ButtonField
from Plat_RoleOfUser ru inner join Plat_RoleOfMenu rm on ru.RoleId=rm.RoleId inner join ModelManage mi on mi.Id=rm.MenuId
inner join ModelCatogory catory on mi.ModelType=catory.ID where catory.Status=1 and mi.Id is not null
and ru.UserIDCard=@UniqueNo and mi.Linkurl=@Url ";
}
//if (!string.IsNullOrEmpty(menuCode))
//{
// sql += " and mi.MenuCode=@MenuCode ";
//}
pms.Add(new SqlParameter("@UniqueNo", uniqueNo));
pms.Add(new SqlParameter("@Url", purl));
//pms.Add(new SqlParameter("@MenuCode", menuCode));
return(SQLHelp.ExecuteDataTable(sql, CommandType.Text, pms.ToArray()));
}
public string GetMenuInfoByParentID(int pid)
{
try
{
StringBuilder sbSql4org = new StringBuilder();
List <SqlParameter> List = new List <SqlParameter>();
string ids = string.Empty;
sbSql4org.Append("select * from PortalTreeData where PId=@PId and IsDelete=@IsDelete and (BeforeAfter=@Before or BeforeAfter=@BeforeAfter) Order by SortId desc");
List.Add(new SqlParameter("@PId", pid));
List.Add(new SqlParameter("@IsDelete", ((int)SysStatus.正常).ToString()));
List.Add(new SqlParameter("@Before", ((int)BeforeAfter.前台展示).ToString()));
List.Add(new SqlParameter("@BeforeAfter", (int)BeforeAfter.前后台展示));
DataTable dt = SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, List.ToArray());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
string itemId = row["Id"].ToString();
ids += itemId + ",";
}
if (!string.IsNullOrWhiteSpace(ids))
{
ids = ids.Substring(0, ids.Length - 1);
}
}
return(ids);
}
catch (Exception)
{
return(null);
}
}
/// <summary>
/// 根据条件获取所有数据
/// </summary>
/// <param name="entity">实体类</param>
/// <param name="where">条件（例如：id>1）</param>
/// <param name="order">排序（例如：createtime desc）</param>
/// <returns></returns>
public virtual DataTable GetData(T entity, string where, string order)
{
DataTable dt = null;
try
{
string sql = string.Format(@"select * from {0} ", entity.GetType().Name);
StringBuilder strFirst = new StringBuilder();
List <SqlParameter> pms = new List <SqlParameter>();
if (!string.IsNullOrEmpty(where))
{
sql += string.Format(@" where {0}", where);
}
if (!string.IsNullOrEmpty(order))
{
sql += string.Format(@" Order by {0}", order);
}
dt = SQLHelp.ExecuteDataTable(sql, CommandType.Text, null);
}
catch (Exception ex)
{
LogHelper.Error(ex);
}
return(dt);
}
public DataTable GetPortalTreeDataForParentId(Hashtable ht)
{
try
{
StringBuilder sbSql4org = new StringBuilder();
//不在某个项内【其实不需要通过这种通过方式】
string notIn = string.Empty;
string _NotItem = Convert.ToString(ht["NotItem"]);
if (!string.IsNullOrEmpty(_NotItem))
{
notIn = string.Format(" and Id not in ({0})", _NotItem);
}
//判断获取不为前端或后端显示的内容
string BeforeAfterNot = Convert.ToString(ht["BeforeAfterNot"]);
string _beforeafter = string.Empty;
if (!string.IsNullOrEmpty(BeforeAfterNot))
{
_beforeafter = string.Format(" and BeforeAfter !='{0}'", BeforeAfterNot);
}
sbSql4org.Append(" select * from PortalTreeData where IsDelete!=" + ((int)PMSUtility.SysStatus.除) + _beforeafter + notIn + " and Display=" + ((int)PMSUtility.Display.显示) + " and (ID=@Id or PId=@Idd) Order by SortId desc,CreateTime asc");
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@Id", ht["Id"]),
new SqlParameter("@Idd", ht["Id"])
};
DataTable dt = SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, param);
return(dt);
}
catch (Exception)
{
return(null);
}
}
public DataTable GetMoreChildTree(Hashtable ht)
{
try
{
if (ht.ContainsKey("Id") && !string.IsNullOrWhiteSpace(ht["Id"].SafeToString()))
{
string sql = "select * from [dbo].[PortalTreeData] where pid=@Id and Display=@Display and IsDelete=@IsDelete union select * from [dbo].[PortalTreeData] where pid in (select id from [dbo].[PortalTreeData] where pid=@Id2 and Display=@Display2 and IsDelete=@IsDelete2)";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@Id", ht["Id"].SafeToString()),
new SqlParameter("@Id2", ht["Id"].SafeToString()),
new SqlParameter("@Display", ht["Display"].SafeToString()),
new SqlParameter("@Display2", ht["Display"].SafeToString()),
new SqlParameter("@IsDelete", ht["IsDelete"].SafeToString()),
new SqlParameter("@IsDelete2", ht["IsDelete"].SafeToString())
};
DataTable dt = SQLHelp.ExecuteDataTable(sql, CommandType.Text, param);
return(dt);
}
return(null);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 获得导航处菜单信息
/// </summary>
/// <param name="uniqueNo">用户唯一号</param>
/// <param name="pid">菜单Pid</param>
/// <param name="isAllLeaf">是否查找pid下的所有子菜单</param>
/// <returns></returns>
public DataTable GetNavigationMenu(string uniqueNo, string pid, bool isAllLeaf)
{
List <SqlParameter> pms = new List <SqlParameter>();
string sql = string.Empty;
if (uniqueNo == "00000000000000000X")//如果是默认最大权限的管理员
{
sql = @"select " + (isAllLeaf ? "" : " distinct ") + @" mi.* from Sys_MenuInfo mi where mi.IsMenu=0 and mi.isShow=3 " + (isAllLeaf ? "" : " and mi.Pid=@Pid ");
}
else
{
sql = @"select " + (isAllLeaf ? "" : " distinct ") + @"mi.* from Sys_RoleOfUser ru
inner join Sys_RoleOfMenu rm on ru.RoleId=rm.RoleId
inner join Sys_MenuInfo mi on mi.Id=rm.MenuId
where mi.Id is not null and mi.IsMenu=0 and mi.isShow=3 and ru.UniqueNo=@UniqueNo " + (isAllLeaf ? "" : " and mi.Pid=@Pid ");
}
if (isAllLeaf)
{
sql = @"WITH r AS (
SELECT * FROM (" + sql + @" and mi.Pid=@Pid ) as menu1
UNION ALL
SELECT menu2.* FROM (" + sql + @") menu2 JOIN r ON menu2.Pid=r.id
)SELECT distinct *,(select count(1) from Sys_MenuInfo where Pid=r.Id and IsMenu=0 and isShow=3) as ChildCount FROM r ORDER BY Sort";
}
else
{
sql += " order by mi.Sort ";
}
pms.Add(new SqlParameter("@UniqueNo", uniqueNo));
pms.Add(new SqlParameter("@Pid", pid));
return(SQLHelp.ExecuteDataTable(sql, CommandType.Text, pms.ToArray()));
}
public DataTable GetSubButtonByUrl(string purl, string uniqueNo, string menuCode)
{
List <SqlParameter> pms = new List <SqlParameter>();
string sql = string.Empty;
if (uniqueNo == "00000000000000000X")//如果是默认最大权限的管理员
{
sql = @"select distinct mi.*
,(select STUFF((select ','+CAST(ISNULL(mbtn.Id,0)AS NVARCHAR(MAX))+'|'+ CAST(Name AS NVARCHAR(MAX))+'|'+ CAST(MenuCode AS NVARCHAR(MAX)) from Sys_MenuInfo mbtn
where Pid = mi.Id and IsMenu = 1 ORDER By Name FOR xml path('')), 1, 1, '')) as ButtonField
from Sys_MenuInfo mi
where 1=1 and mi.IsShow!=0 and mi.Url like '%'+@Url ";
}
else
{
sql = @"select distinct mi.*
,(select STUFF((select ','+CAST(ISNULL(btnrm.MenuId,0)AS NVARCHAR(MAX))+'|'+ CAST(Name AS NVARCHAR(MAX))+'|'+ CAST(MenuCode AS NVARCHAR(MAX)) from Sys_RoleOfUser btnru
inner join Sys_RoleOfMenu btnrm on btnru.RoleId=btnrm.RoleId
inner join Sys_MenuInfo btnmi on btnmi.Id=btnrm.MenuId
where Pid = mi.Id and IsMenu = 1 and btnru.UniqueNo=@UniqueNo ORDER By Name FOR xml path('')), 1, 1, '')) as ButtonField
from Sys_RoleOfUser ru
inner join Sys_RoleOfMenu rm on ru.RoleId=rm.RoleId
inner join Sys_MenuInfo mi on mi.Id=rm.MenuId
where mi.Id is not null and mi.IsShow!=0 and ru.UniqueNo=@UniqueNo and mi.Url like '%'+@Url ";
}
if (!string.IsNullOrEmpty(menuCode))
{
sql += " and mi.MenuCode=@MenuCode ";
}
pms.Add(new SqlParameter("@UniqueNo", uniqueNo));
pms.Add(new SqlParameter("@Url", purl));
pms.Add(new SqlParameter("@MenuCode", menuCode));
return(SQLHelp.ExecuteDataTable(sql, CommandType.Text, pms.ToArray()));
}
public int GetMaxSortId(int mid)
{
try
{
StringBuilder sbSql4org = new StringBuilder();
List <SqlParameter> List = new List <SqlParameter>();
sbSql4org.Append(@" select max(SortId) as SortId from Advertising where MenuId in (
select id from PortalTreeData where pid=(
select pid from [dbo].[PortalTreeData] where id=@mid)
And Display=@Display And (BeforeAfter=@BA or BeforeAfter=@BeforeAfter))");
List.Add(new SqlParameter("@mid", mid));
List.Add(new SqlParameter("@Display", ((int)Display.显示).ToString()));
List.Add(new SqlParameter("@BA", ((int)BeforeAfter.前后台展示).ToString()));
List.Add(new SqlParameter("@BeforeAfter", ((int)BeforeAfter.前台展示).ToString()));
DataTable dt = SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, List.ToArray());
if (dt != null && dt.Rows.Count > 0)
{
int number = Convert.ToInt32(dt.Rows[0]["SortId"]);
return(number);
}
else
{
return(0);
}
}
catch (Exception)
{
return(0);
}
}
public static DataTable get_praise(string id)//
{
SqlParameter[] param =
{
new SqlParameter("@id", id)
};
return(SQLHelp.ExecuteDataTable("get_praise", CommandType.StoredProcedure, param));
}
//跟进记录获取所有跟进客户
public static DataTable get_cust_list(string userid)
{
SqlParameter[] param =
{
new SqlParameter("@userid", userid)
};
return(SQLHelp.ExecuteDataTable("get_cust_list", CommandType.StoredProcedure, param));
}
/// <summary>
/// 根据id获取联系人职位
/// </summary>
/// <param name="sign_userid"></param>
/// <returns></returns>
public static DataTable get_link_position_by_id(string id)
{
SqlParameter[] param =
{
new SqlParameter("@id", id)
};
return(SQLHelp.ExecuteDataTable("get_link_position_by_id", CommandType.StoredProcedure, param));
}
/// <summary>
/// 无验证-不分页
/// </summary>
/// <param name="ht"></param>
/// <returns></returns>
public JsonModel GetData_NoVerification(Hashtable ht)
{
BLLCommon com = new BLLCommon();
JsonModel JsonModel;
try
{
string SQL = " select " + ht["Columns"].ToString() + " from " + ht["TableName"].ToString() + " where 1=1 ";
if (ht.Contains("Where") && !string.IsNullOrWhiteSpace(ht["Where"].ToString()))
{
SQL += ht["Where"].ToString();
}
if (ht.Contains("Order") && !string.IsNullOrWhiteSpace(ht["Order"].ToString()))
{
SQL += " order by " + ht["Order"].ToString();
}
DataTable dt = SQLHelp.ExecuteDataTable(SQL, CommandType.Text);
if (dt == null)
{
JsonModel = new JsonModel()
{
errNum = 1,
errMsg = "失败",
retData = ""
};
LogService.WriteErrorLog("DataTable为NULL");
return JsonModel;
}
if (dt.Rows.Count == 0)
{
JsonModel = new JsonModel()
{
errNum = 999,
errMsg = "无数据",
retData = ""
};
return JsonModel;
}
JsonModel = new JsonModel()
{
errNum = 0,
errMsg = "成功",
retData = com.DataTableToList(dt)
};
return JsonModel;
}
catch (Exception ex)
{
JsonModel = new JsonModel()
{
errNum = 400,
errMsg = ex.Message,
retData = ""
};
LogService.WriteErrorLog(ex.Message);
return JsonModel;
}
}
public bool DelVote(Hashtable ht)
{
using (SqlTransaction tans = SQLHelp.BeginTransaction())
{
try
{
string sql = "delete from System_Vote where Id=@Id";
if (ht.ContainsKey("Id") && !string.IsNullOrWhiteSpace(ht["Id"].SafeToString()))
{
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@Id", ht["Id"].ToString())
};
int number = SQLHelp.ExecuteNonQuery(sql, CommandType.Text, param);
if (number < 0)
{
tans.Rollback();
}
sql = "select * from System_VoteDetails where VoteId=@Id";
SqlParameter[] param_sel = new SqlParameter[] {
new SqlParameter("@Id", ht["Id"].ToString())
};
DataTable dt = SQLHelp.ExecuteDataTable(sql, CommandType.Text, param_sel);
sql = "delete from System_VoteDetails where VoteId=@Id";
SqlParameter[] param_del = new SqlParameter[] {
new SqlParameter("@Id", ht["Id"].ToString())
};
int num = SQLHelp.ExecuteNonQuery(sql, CommandType.Text, param_del);
if (dt != null && dt.Rows.Count > 0)
{
string detailsId = "";
foreach (DataRow row in dt.Rows)
{
detailsId += row["Id"].ToString() + ",";
}
if (detailsId.Length > 0)
{
detailsId = detailsId.Substring(0, detailsId.Length - 1);
sql = "delete from System_VoteRecord where DetailsId in (" + detailsId + ")";
int ok = SQLHelp.ExecuteNonQuery(sql, CommandType.Text, null);
tans.Commit();
return(true);
}
}
else
{
tans.Commit();
return(true);
}
}
return(false);
}
catch (Exception)
{
tans.Rollback();
return(false);
}
}
}
public static DataTable get_cust_customer_by_custname(string cust_name, long cust_id)
{
SqlParameter[] param =
{
new SqlParameter("@cust_name", cust_name),
new SqlParameter("@cust_id", cust_id)
};
return(SQLHelp.ExecuteDataTable("get_cust_customer_by_custname", CommandType.StoredProcedure, param));
}
/// <summary>
/// 获取日历是否签到
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public static DataTable get_is_sign(string sign_date, int userid)
{
SqlParameter[] param =
{
new SqlParameter("@sign_date", sign_date),
new SqlParameter("@userid", userid)
};
return(SQLHelp.ExecuteDataTable("get_is_sign", CommandType.StoredProcedure, param));
}
/// <summary>
/// 获取统计信息
/// </summary>
/// <returns></returns>
public static DataTable get_statistic_detail(string userid, string sd_type)
{
SqlParameter[] param =
{
new SqlParameter("@userid", userid),
new SqlParameter("@sd_type", sd_type)
};
return(SQLHelp.ExecuteDataTable("get_statistic_detail", CommandType.StoredProcedure, param));
}
/// <summary>
/// 获取全部角色，返回DataTable
/// </summary>
public DataTable GetAllRoleList()
{
StringBuilder sbSql4org;
sbSql4org = new StringBuilder();
sbSql4org.Append(@"select distinct * from Sys_Role where IsDelete=0 ");
sbSql4org.Append(" order by Id desc ");
return(SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, null));
}
/// <summary>
/// 获取全部角色，返回DataTable
/// </summary>
public DataTable GetAllRoleList()
{
StringBuilder sbSql4org;
List <SqlParameter> pms = new List <SqlParameter>();
sbSql4org = new StringBuilder();
sbSql4org.Append(@"select distinct * from Plat_Role where IsDelete=0 order by Id desc ");
return(SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, pms.ToArray()));
}
public static DataTable getcomment_list(string com_table_id, string com_type)
{
SqlParameter[] param =
{
new SqlParameter("@com_table_id", com_table_id),
new SqlParameter("@com_type", com_type)
};
return(SQLHelp.ExecuteDataTable("getcomment_list", CommandType.StoredProcedure, param));
}
public DataTable GetSectionStudentData()
{
List <SqlParameter> pms = new List <SqlParameter>();
string sql = string.Empty;
sql = @"select learn.Id,learn.Academic,stu.StuCount from Sys_StudySection learn
left join (select SectionID,count(1) as StuCount from Grad_Class_rel group by SectionID)stu on learn.Id=stu.SectionID ";
return(SQLHelp.ExecuteDataTable(sql, CommandType.Text, pms.ToArray()));
}
//获取图片集合
public static DataTable getpicture_list(string pic_en_table, string pic_table_id)
{
SqlParameter[] param =
{
new SqlParameter("@pic_en_table", pic_en_table),
new SqlParameter("@pic_table_id", pic_table_id)
};
return(SQLHelp.ExecuteDataTable("getpicture_list", CommandType.StoredProcedure, param));
}
//学生姓名，身份证号，年级，学期，班级，班主任，班长
//select d.Name,d.idcard,c.GradeName,b.Academic,a.className,a.Headteacherno,a.monitorno from Sys_ClassInfo a,Grad_Class_rel R,Sys_StudySection b,Sys_GradeInfo c,Sys_UserInfo d where a.ClassNO=R.ClassNo and R.SectionID=b.Id and R.GradeID=c.ID
//and d.registerorg=a.classno and d.id=1154
public DataTable IsPwdTure(string UserName, string PassWord)
{
string PhotoPre = ConfigHelper.GetConfigString("PhotoPre");
string strSql = "select Id,UniqueNo,UserType,Name,Nickname,Sex,Phone,Birthday,LoginName,Password,IDCard,HeadPic,RegisterOrg,AuthenType,Address,Remarks,CreateUID,EditUID,IsEnable,IsDelete,CheckMsg,'" + PhotoPre + "' +HeadPic as AbsHeadPic from Sys_UserInfo where LoginName='" +
UserName + "' and Password='******'";
DataTable dt = SQLHelp.ExecuteDataTable(strSql, CommandType.Text, null);
return(dt);
}
public static DataTable get_statistic_today(string userid, string username, string type)
{
SqlParameter[] param =
{
new SqlParameter("@userid", userid),
new SqlParameter("@username", username),
new SqlParameter("@type", type)
};
return(SQLHelp.ExecuteDataTable("get_static_today", CommandType.StoredProcedure, param));
}
public static DataTable cust_customer_nearby(string sign_x, string sign_y, string cust_users)
{
SqlParameter[] param =
{
new SqlParameter("@sign_x", sign_x),
new SqlParameter("@sign_y", sign_y),
new SqlParameter("@cust_users", cust_users)
};
return(SQLHelp.ExecuteDataTable("cust_customer_nearby", CommandType.StoredProcedure, param));
}
/// <summary>
/// 获取签到记录第一条信息
/// </summary>
/// <returns></returns>
public static DataTable getfollow_up_list(string follow_userid, string tableid, string type)
{
SqlParameter[] param =
{
new SqlParameter("@follow_userid", follow_userid),
new SqlParameter("@tableid", tableid),
new SqlParameter("@type", type)
};
return(SQLHelp.ExecuteDataTable("getfollow_up_list", CommandType.StoredProcedure, param));
}
/// <summary>
/// 获取工作计划第一条信息
/// </summary>
/// <returns></returns>
public static DataTable getworkplan_list(string wp_userid, string table_id, string type)
{
SqlParameter[] param =
{
new SqlParameter("@wp_userid", wp_userid),
new SqlParameter("@table_id", table_id),
new SqlParameter("@type", type)
};
return(SQLHelp.ExecuteDataTable("getworkplan_list", CommandType.StoredProcedure, param));
}
public void sendEmail(int type)
{
using (SqlTransaction trans = SQLHelp.BeginTransaction())
{
try
{
string sql = "select * from System_Message where isSend=" + (int)isSend.未发送 + " and IsDelete!=" + (int)SysStatus.除 + " and [Type]=" + type + " and Timing=" + (int)MessageTiming.定时发送;
DataTable dt = SQLHelp.ExecuteDataTable(sql, CommandType.Text);
string ids = "";
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
DateTime pushDate = Convert.ToDateTime(row["CreateTime"]);
DateTime curDate = DateTime.Now;
TimeSpan ts = pushDate - curDate;
if (ts.Minutes <= 2)
{
ids += row["Id"].ToString() + ",";
}
if (!string.IsNullOrWhiteSpace(ids) && ids.Length > 0)
{
ids = ids.Substring(0, ids.Length - 1);
string upSql = "update System_Message set isSend=" + (int)isSend.已发送 + " where Id in (" + ids + ")";
int number = SQLHelp.ExecuteNonQuery(upSql, CommandType.Text);
trans.Commit();
}
}
foreach (DataRow row in dt.Rows)
{
string Subject = string.IsNullOrWhiteSpace(Convert.ToString(row["Title"])) ? "无题" : row["Title"].ToString();
string Body = string.IsNullOrWhiteSpace(Convert.ToString(row["Contents"])) ? "无内容" : row["Contents"].ToString();
if (!string.IsNullOrWhiteSpace(Convert.ToString(row["ReceiverEmail"])))
{
string href = "";
if (!string.IsNullOrWhiteSpace(Convert.ToString(row["CreatorName"])))
{
Body += "<br/><h4>发件人：" + row["CreatorName"].ToString() + "</h4>";
}
if (!string.IsNullOrWhiteSpace(Convert.ToString(row["Href"])))
{
href = "<br/><h3><a href=" + row["Href"].ToString() + ">点击此处可查看详细信息</a></h3>";
}
SendMailMessage.SendMessage(Subject, Body + href, row["ReceiverEmail"].ToString());
}
}
}
}
catch (Exception ex)
{
_logger.Info("EmailPost：" + ex.Message);
trans.Rollback();
}
}
}
/// <summary>
/// 获取统计信息
/// </summary>
/// <returns></returns>
public static DataTable get_statistic(string userid, string username, string startdate, string enddate)
{
SqlParameter[] param =
{
new SqlParameter("@userid", userid),
new SqlParameter("@username", username),
new SqlParameter("@startdate", startdate),
new SqlParameter("@enddate", enddate)
};
return(SQLHelp.ExecuteDataTable("proc_getstatic_detail", CommandType.StoredProcedure, param));
}