public DataSet GetOrganizationInfo()
{
StringBuilder sbql = new StringBuilder();
sbql.Append("select name from sysobjects where type='u' and Name like 'SYS_SucceedSendMessage%'");
DataSet dsTableName = DbHelperSQL.DQuery(sbql.ToString());
StringBuilder sbselect = new StringBuilder();
sbselect.Append("select distinct OZ.OrgID,OrgName from (");
for (int i = 0; i < dsTableName.Tables[0].Rows.Count; i++)
{
sbselect.Append("select OperatorID from " + dsTableName.Tables[0].Rows[i][0].ToString() + " union");
}
sbselect.Remove(sbselect.Length - 6, 6);
sbselect.Append(") as SSM");
sbselect.Append(" inner join Sys_Operator O on O.OperatorID = SSM.OperatorID");
sbselect.Append(" inner join Sys_Organization OZ on OZ.OrgID = O.OrgID");
DataSet dsOrgInfo = DbHelperSQL.DQuery(sbselect.ToString());
return(dsOrgInfo);
}
/// <summary>
/// 继续发送短信
/// </summary>
/// <param name="FailerID"></param>
/// <param name="year"></param>
/// <returns></returns>
#region 继续发送短信
public int InsertIntoReadySendMessage(string FailerID, int year)
{
int Irow = 0;
List <string> Ltsql = new List <string>();
string strsql = "select PhoneNumber,MContent,FailerDate from Sys_FailerSendMessage" + year + " where FSMID in(" + FailerID + ")";
DataSet dsFailerMessage = DbHelperSQL.DQuery(strsql);
if (null != dsFailerMessage)
{
if (dsFailerMessage.Tables[0].Rows.Count != 0)
{
DataRowCollection drc = dsFailerMessage.Tables[0].Rows;
for (int i = 0; i < drc.Count; i++)
{
Ltsql.Add("insert into Sys_ReadySendMessage" + year + " (PhoneNumber,MContent,SendDate) values('" + drc[i]["PhoneNumber"].ToString() + "','" + drc[i]["MContent"].ToString() + "','" + drc[i]["FailerDate"].ToString() + "');");
}
Ltsql.Add("delete from Sys_ReadySendMessage" + year + " where FSMID in (" + FailerID + ");");
Irow = DbHelperSQL.ExecuteSqlTran(Ltsql);
}
}
else
{
Irow = 0;
}
return(Irow);
}
/// <summary>
/// 获得数据列表
/// </summary>
//public DataSet GetList(int PageSize,int PageIndex,string strWhere)
//{
//return dal.GetList(PageSize,PageIndex,strWhere);
//}
public void LogTypeBind(System.Web.UI.WebControls.DropDownList ddlControl)
{
string str = "select Distinct LOGTYPE from SYS_OperateLog";
ddlControl.DataSource = DbHelperSQL.DQuery(str);
ddlControl.DataTextField = "LOGTYPE";
ddlControl.DataValueField = "LOGTYPE";
ddlControl.DataBind();
ddlControl.Items.Insert(0, new System.Web.UI.WebControls.ListItem("请选择操作类型", ""));
}
/// <summary>
/// 由“系统编号”和“职位编号”获取功能组
/// </summary>
/// <param name="sysCode">系统编号</param>
/// <param name="positionCode">职位编号</param>
protected DataSet GetFunctionGroup(string sysCode, string positionCode)
{
StringBuilder queryStringBulider = new StringBuilder();
queryStringBulider.Append("SELECT FunctionID,FunName,FunCode FROM V_PositionsOnFunctions WHERE ");
queryStringBulider.AppendFormat("PositionCode = '{0}' AND ", positionCode);
queryStringBulider.AppendFormat("SysCode = '{0}' AND ", sysCode);
queryStringBulider.Append("FunType = '1'");
return(DbHelperSQL.DQuery(queryStringBulider.ToString()));
}
/// <summary>
/// 获取某个功能组下的所有权限
/// </summary>
/// <param name="sysCode">系统编号</param>
/// <param name="positionCode">职位编号</param>
/// <param name="funCode">功能组编号</param>
protected DataSet GetFunctions(string sysCode, string positionCode, string funCode)
{//PicturePath
StringBuilder queryStringBulier = new StringBuilder();
queryStringBulier.Append("SELECT FunName,FunFile,ModulePicture,(FunName+','+FunFile) as DataKey FROM V_PositionsOnFunctions WHERE ");
queryStringBulier.AppendFormat("PositionCode = '{0}' AND ", positionCode);
queryStringBulier.AppendFormat("SysCode = '{0}' AND ", sysCode);
queryStringBulier.AppendFormat("ParentCode = '{0}' AND ", funCode);
queryStringBulier.Append("FunType = '2'");
return(DbHelperSQL.DQuery(queryStringBulier.ToString()));
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="strWhere">查询条件不带where</param>
/// <returns>DataSet</returns>
public DataSet GetList(string strWhere)
{
StringBuilder sb = new StringBuilder();
sb.Append("select * ");
sb.Append(" from SYS_MENU");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
sb.AppendFormat(" where {0}", strWhere);
}
return(DbHelperSQL.DQuery(sb.ToString()));
}
/// <summary>
/// 获取待发短信信息
/// </summary>
/// <returns></returns>
public DataTable GetReadySendMessageInfo()
{
DataTable dtReadySendMessageInfo = null;
int iYear = DateTime.Now.Year;
int ICount = 0;
string strsql = "select count(*) from Sys_ReadySendMessage" + (iYear - 1) + "";
try
{
ICount = Convert.ToInt32(DbHelperSQL.GetSingle(strsql));
}
catch
{
ICount = 0;
}
StringBuilder sbsql = new StringBuilder();
#region 拼接查询
sbsql.Append("select top 20 RSMID,PhoneNumber,MContent,SendDate");
if (ICount == 0)
{
sbsql.Append(" from Sys_ReadySendMessage" + iYear + " RSM");
sbsql.Append(" order by SendDate desc");
}
else
{
sbsql.Append(" from (");
sbsql.Append("select * from Sys_ReadySendMessage" + iYear + "");
sbsql.Append(" union");
sbsql.Append(" select * from Sys_ReadySendMessage" + (iYear - 1) + "");
sbsql.Append(") as RSM");
sbsql.Append(" order by SendDate desc");
}
#endregion
DataSet ds = DbHelperSQL.DQuery(sbsql.ToString());
dtReadySendMessageInfo = ds.Tables[0];
return(dtReadySendMessageInfo);
}
private void BindTree(string SysCode)
{
string strSql = "";
if (CookieManager.GetCookieValue("uid") != "0") //非超级管理员用户
{
strSql = "select * from SYS_MENU where menuID in(select distinct menuID from SYS_menuPosition where PositionCode in(" + CookieManager.GetCookieValue("PositionCode") + ")) Order by displayOrder";
}
else
{
strSql = "select * from SYS_MENU Order by displayOrder";
}
DataSet DS = DbHelperSQL.DQuery(strSql);
DataTable dt = DS.Tables[0];
DataView dv = new DataView(dt);
dv.RowFilter = " parentsID ='" + sysid + "' ";
foreach (DataRowView drv in dv)
{
TreeNode node = new TreeNode();
node.Text = drv["menuName"].ToString();
node.Value = drv["menuID"].ToString();
node.ImageUrl = "Images/icons/openroomiconinfo.gif";
node.SelectAction = TreeNodeSelectAction.Expand;
//根据测试需求加的
//node.NavigateUrl = drv["menuAction"].ToString();
//if ("0" == drv["IsPop"].ToString())
//{
// node.NavigateUrl = "javascript:setTitleAndUrl('" + node.Text + "','" + drv["menuAction"].ToString() + "');";
//}
//else
//{
// node.NavigateUrl = "javascript:window.open('" + drv["menuAction"].ToString() + "');";
//}
node.Expanded = false;
this.TreeView1.Nodes.Add(node);
AddReplies(dt, node);
}
}
public DataSet GetSysType()
{
StringBuilder sbql = new StringBuilder();
sbql.Append("select name from sysobjects where type='u' and Name like 'SYS_SucceedSendMessage%'");
DataSet dsTableName = DbHelperSQL.DQuery(sbql.ToString());
StringBuilder sbselect = new StringBuilder();
sbselect.Append("select distinct MenuID,MenuName from (");
for (int i = 0; i < dsTableName.Tables[0].Rows.Count; i++)
{
sbselect.Append("select systype from " + dsTableName.Tables[0].Rows[i][0].ToString() + " union");
}
sbselect.Remove(sbselect.Length - 6, 6);
sbselect.Append(") as SSM");
sbselect.Append(" inner join Sys_Menu M on M.MenuID = SSM.SysType");
DataSet dsSysType = DbHelperSQL.DQuery(sbselect.ToString());
return(dsSysType);
}
//#region Repeater控件分页帮定
///// <summary #region GridView控件分页帮定
/// <summary>
/// GridView控件分页帮定
/// </summary>
/// <param name="anpager">AspNetPager分页控件</param>
/// <param name="strQuaryCondition">查询Where条件，不含Where</param>
/// <param name="strOrderCondition">需要排序的字段名</param>
/// <param name="rptControl">GridView控件</param>
public void GridViewPagerBindbyRowNumber(Wuqi.Webdiyer.AspNetPager anpager, string strWhere, string strOrderCondition, System.Web.UI.WebControls.GridView grvControl, int startyear, int endyear)
{
StringBuilder sbReceiveTable = new StringBuilder();
DataSet dstTemp = new DataSet();
#region 根据时间获取要查询的短信表
for (int i = startyear; i <= endyear; i++)
{
sbReceiveTable.Append("'Sys_ReceiveMessage" + i + "',");
}
sbReceiveTable.Remove(sbReceiveTable.Length - 1, 1);
string strselectTable = "select name from sysobjects where type='u' and name in(" + sbReceiveTable + ")";
DataSet dsReceiveTable = DbHelperSQL.DQuery(strselectTable);
StringBuilder sbsql = new StringBuilder();
sbsql.Append("select RMID,PhoneNumber,MContent,ReceiveDate from (");
for (int i = 0; i < dsReceiveTable.Tables[0].Rows.Count; i++)
{
sbsql.Append("select * from " + dsReceiveTable.Tables[0].Rows[i][0].ToString() + " union");
}
sbsql.Remove(sbsql.Length - 6, 6);
sbsql.Append(") as RM");
#endregion
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from (select row_number() over (order by " + strOrderCondition + ") as rowno,* from (" + sbsql.ToString() + ") as VT_ReceiveMessage");
strSql.Append(strWhere);
strSql.Append(" ) as result Where (rowno Between " + ((anpager.CurrentPageIndex - 1) * anpager.PageSize + 1) + " and " + anpager.CurrentPageIndex * anpager.PageSize + ")");
StringBuilder strb = new StringBuilder();
strb.Append("Select Count(*) From (" + sbsql.ToString() + ") as VT_ReceiveMessage");
strb.Append(strWhere);
anpager.RecordCount = Convert.ToInt32(DbHelperSQL.GetSingle(strb.ToString()));
dstTemp = DbHelperSQL.Query(strSql.ToString());
if (dstTemp.Tables[0].Rows.Count == 0)
{
grvControl.DataSource = null;
grvControl.DataBind();
}
else
{
grvControl.DataSource = dstTemp.Tables[0];
grvControl.DataBind();
}
//动态设置用户自定义文本内容
anpager.CustomInfoHTML = "共有<font color=\"blue\"><b>" + anpager.RecordCount.ToString() + "</b></font>条记录";
anpager.CustomInfoHTML += " 总页数：<font color=\"blue\"><b>" + anpager.PageCount.ToString() + "</b></font>页";
anpager.CustomInfoHTML += " 当前页：第<font color=\"red\"><b>" + anpager.CurrentPageIndex.ToString() + "</b></font>页";
}
