You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
885 lines
38 KiB
C#
885 lines
38 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using DSWeb.MvcShipping.Models.MsMainForm;
|
|
using DSWeb.MvcShipping.Models.MsTemplet;
|
|
using DSWeb.MvcShipping.Models.UserBaseInfo;
|
|
using Microsoft.Practices.EnterpriseLibrary.Data;
|
|
using DSWeb.Areas.CommMng.Models;
|
|
using HcUtility.Comm;
|
|
|
|
|
|
namespace DSWeb.MvcShipping.DAL.MsMainForm
|
|
{
|
|
public class MsMainFormDAL
|
|
{
|
|
#region Inquery DataList
|
|
|
|
static public List<Mainmodule> GetDataList(string strCondition, string companyid, string sort = null)
|
|
{
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("[GID],[MODULENAME],[MODULETITLE],[MODULEURL],[MODULEWIDTH],[ISUSE],[ISSYS]");
|
|
strSql.Append(" from main_module where ISUSE<>0 ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" and " + strCondition);
|
|
}
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else {
|
|
strSql.Append(" order by CREATETIME ");
|
|
|
|
}
|
|
return SetData(strSql);
|
|
}
|
|
|
|
|
|
private static List<Mainmodule> SetData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<Mainmodule>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
Mainmodule data = new Mainmodule();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
data.MODULENAME = Convert.ToString(reader["MODULENAME"]);
|
|
data.MODULETITLE = Convert.ToString(reader["MODULETITLE"]);
|
|
data.MODULEURL = Convert.ToString(reader["MODULEURL"]);
|
|
if (reader["MODULEWIDTH"] != DBNull.Value)
|
|
data.MODULEWIDTH = Convert.ToInt16(reader["MODULEWIDTH"]);
|
|
if (reader["ISSYS"] != DBNull.Value)
|
|
data.ISSYS = Convert.ToBoolean(reader["ISSYS"]);
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
#region 个人模块信息
|
|
public static List<Mainmodule> GetUserMainFormSetInfo(string gid, string companyid)
|
|
{
|
|
StringBuilder sql = new StringBuilder();
|
|
sql.AppendLine("select B.MODULEURL,B.MODULEWIDTH as DEFAULTWIDTH,B.MODULEHEIGHT as DEFAULTHEIGHT, ");
|
|
sql.AppendLine("A.MODULEWIDTH,A.MODULEHEIGHT,A.MODULETOP,A.MODULELEFT ");
|
|
sql.AppendLine("from user_mainmd_set as A ");
|
|
sql.AppendLine("left join main_module as B on A.MODULEID=B.GID ");
|
|
sql.AppendLine("where A.GID='" + gid + "' and B.COMPANYID='" + companyid + "' and B.ISUSE=1 ");
|
|
sql.AppendLine("order by B.CREATETIME ");
|
|
var headList = new List<Mainmodule>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
Mainmodule data = new Mainmodule();
|
|
#region Set DB data to Object
|
|
data.MODULEURL = Convert.ToString(reader["MODULEURL"]);
|
|
if (reader["DEFAULTWIDTH"] != DBNull.Value)
|
|
{
|
|
data.DEFAULTWIDTH = Convert.ToInt16(reader["DEFAULTWIDTH"]);
|
|
}
|
|
else
|
|
{
|
|
data.DEFAULTWIDTH = 0;
|
|
}
|
|
if (reader["DEFAULTHEIGHT"] != DBNull.Value)
|
|
{
|
|
data.DEFAULTHEIGHT = Convert.ToInt16(reader["DEFAULTHEIGHT"]);
|
|
}
|
|
else
|
|
{
|
|
data.DEFAULTHEIGHT = 0;
|
|
}
|
|
if (reader["MODULEWIDTH"] != DBNull.Value)
|
|
{
|
|
data.MODULEWIDTH = Convert.ToInt16(reader["MODULEWIDTH"]);
|
|
}
|
|
else
|
|
{
|
|
data.MODULEWIDTH = 0;
|
|
}
|
|
if (reader["MODULEHEIGHT"] != DBNull.Value)
|
|
{
|
|
data.MODULEHEIGHT = Convert.ToInt16(reader["MODULEHEIGHT"]);
|
|
}
|
|
else
|
|
{
|
|
data.MODULEHEIGHT = 0;
|
|
}
|
|
if (reader["MODULETOP"] != DBNull.Value)
|
|
{
|
|
data.MODULETOP = Convert.ToInt16(reader["MODULETOP"]);
|
|
}
|
|
else
|
|
{
|
|
data.MODULETOP = 0;
|
|
}
|
|
if (reader["MODULELEFT"] != DBNull.Value)
|
|
{
|
|
data.MODULELEFT = Convert.ToInt16(reader["MODULELEFT"]);
|
|
}
|
|
else
|
|
{
|
|
data.MODULELEFT = 0;
|
|
}
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
#region 个人模板列表
|
|
public static List<MsTempletStructure> GetUserTemplet(string gid, string companyid)
|
|
{
|
|
StringBuilder sql = new StringBuilder();
|
|
sql.AppendLine("select distinct TEMPLETID,TEMPLETNAME,TEMPLETTHUMB, ");
|
|
sql.AppendLine("(select distinct C.TEMPLETID ");
|
|
sql.AppendLine("from user_mainmd_set as A ");
|
|
sql.AppendLine("left join main_module as B on B.GID=A.MODULEID ");
|
|
sql.AppendLine("left join templet as C on C.TEMPLETID=B.USERTEMPLETID ");
|
|
sql.AppendLine("where A.GID='" + gid + "' and B.COMPANYID='" + companyid + "') as SHOWTEMPLETID ");
|
|
sql.AppendLine("from templet ");
|
|
var headList = new List<MsTempletStructure>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsTempletStructure data = new MsTempletStructure();
|
|
#region Set DB data to Object
|
|
data.TEMPLETID = Convert.ToString(reader["TEMPLETID"]);
|
|
data.TEMPLETNAME = Convert.ToString(reader["TEMPLETNAME"]);
|
|
data.TEMPLETTHUMB = Convert.ToString(reader["TEMPLETTHUMB"]);
|
|
data.SHOWTEMPLETID = Convert.ToString(reader["SHOWTEMPLETID"]);
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
#region 个人模板项目分组
|
|
public static List<MsTempletStructure> GetUserTempletItem(string gid)
|
|
{
|
|
StringBuilder sql = new StringBuilder();
|
|
sql.AppendLine("select ");
|
|
sql.AppendLine("(select GID from user_templet where PARENTID=A.ITEMID) as GID, ");
|
|
sql.AppendLine("(case when IMGURL is not null then (select ITEMNAME from user_templet where PARENTID=A.ITEMID) end) as TITLE ");
|
|
sql.AppendLine("from templet as A ");
|
|
sql.AppendLine("where A.TEMPLETURL=(select distinct C.TEMPLETURL ");
|
|
sql.AppendLine("from user_mainmd_set as A ");
|
|
sql.AppendLine("left join main_module as B on A.MODULEID=B.GID ");
|
|
sql.AppendLine("left join templet as C on B.USERTEMPLETID=C.TEMPLETID ");
|
|
sql.AppendLine("where A.GID='" + gid + "') ");
|
|
sql.AppendLine("and A.ITEMID in (select ITEMID from templet where PARENTITEMID='.' and IMGURL is not null) ");
|
|
sql.AppendLine("order by A.SORT ");
|
|
var headList = new List<MsTempletStructure>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsTempletStructure data = new MsTempletStructure();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
data.ITEMNAME = Convert.ToString(reader["TITLE"]);
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
#region 个人模板项目明细
|
|
public static List<MsTempletStructure> GetUserTempletDetail(string gid, string itemName)
|
|
{
|
|
StringBuilder sql = new StringBuilder();
|
|
sql.AppendLine("select B.* ");
|
|
sql.AppendLine("from templet as A ");
|
|
sql.AppendLine("left join user_templet as B on A.ITEMID=B.PARENTID ");
|
|
sql.AppendLine("where A.TEMPLETURL=(select distinct C.TEMPLETURL ");
|
|
sql.AppendLine("from user_mainmd_set as A ");
|
|
sql.AppendLine("left join main_module as B on A.MODULEID=B.GID ");
|
|
sql.AppendLine("left join templet as C on B.USERTEMPLETID=C.TEMPLETID ");
|
|
sql.AppendLine("where A.GID='" + gid + "') ");
|
|
sql.AppendLine("and A.ITEMID=(select ITEMID from templet where ITEMTYPE=1 and PARENTITEMID=(select PARENTID from user_templet where ITEMNAME='" + itemName + "')) ");
|
|
sql.AppendLine("order by A.SORT,B.SORT ");
|
|
var headList = new List<MsTempletStructure>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsTempletStructure data = new MsTempletStructure();
|
|
#region Set DB data to Object
|
|
data.ITEMID = Convert.ToString(reader["ITEMID"]);
|
|
data.ITEMNAME = Convert.ToString(reader["ITEMNAME"]);
|
|
data.LINKURL = Convert.ToString(reader["LINKURL"]);
|
|
data.SQL = Convert.ToString(reader["SQL"]);
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
#region 公司雇员列表
|
|
public static List<UserBaseInfoModel> GetEmployeeList(string companyid)
|
|
{
|
|
StringBuilder sql = new StringBuilder();
|
|
sql.AppendLine("select A.GID,A.SHOWNAME from [user] as A ");
|
|
sql.AppendLine("left join [user_company] as B on A.GID=B.USERID ");
|
|
sql.AppendLine("where B.COMPANYID='" + companyid + "' and SHOWNAME!='系统管理员' ");
|
|
var headList = new List<UserBaseInfoModel>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
UserBaseInfoModel data = new UserBaseInfoModel();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
data.SHOWNAME = Convert.ToString(reader["SHOWNAME"]);
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
#region 公司带权限雇员列表
|
|
public static List<UserBaseInfoModel> GetEmployeePopedom(string id)
|
|
{
|
|
StringBuilder sql = new StringBuilder();
|
|
/*sql.AppendLine("select USERID from user_mainmd_set ");
|
|
sql.AppendLine("where MODULEID in (select MODULEID from user_mainmd_set where GID='" + id + "') ");*/
|
|
sql.AppendLine("if(exists(select * from main_module where GID=(select moduleid from user_mainmd_set where GID='" + id + "' and ISSYS=0))) ");
|
|
sql.AppendLine("begin ");
|
|
sql.AppendLine("select USERID from user_mainmd_set ");
|
|
sql.AppendLine("where MODULEID in (select MODULEID from user_mainmd_set where GID='" + id + "') ");
|
|
sql.AppendLine("end ");
|
|
sql.AppendLine("else ");
|
|
sql.AppendLine("begin ");
|
|
sql.AppendLine("select GID as USERID from [user] ");
|
|
sql.AppendLine("end ");
|
|
var headList = new List<UserBaseInfoModel>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
UserBaseInfoModel data = new UserBaseInfoModel();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["USERID"]);
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
#region 保存模板信息分组
|
|
public static DBResult InsertItem(string globalid, string itemid, string itemname, string userid, string companyid)
|
|
{
|
|
string templetid = "";
|
|
string templetname = "";
|
|
string templeturl = "";
|
|
string templetthumb = "";
|
|
string imgurl = "";
|
|
//string parentitemid = ".";
|
|
string templetitemid = System.Guid.NewGuid().ToString();
|
|
string parentid = "";
|
|
//int itemtype = 0;
|
|
int sort = 0;
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
StringBuilder sql = new StringBuilder();
|
|
sql.AppendLine("select distinct TEMPLETID,TEMPLETNAME,TEMPLETURL,TEMPLETTHUMB,IMGURL ");
|
|
sql.AppendLine("from templet as A ");
|
|
sql.AppendLine("left join main_module as B on A.TEMPLETID=B.USERTEMPLETID ");
|
|
sql.AppendLine("left join user_mainmd_set as C on C.MODULEID=B.GID ");
|
|
sql.AppendLine("where C.GID='" + globalid + "' and IMGURL!='' ");
|
|
var tempInfo = new List<MsTempletStructure>();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
templetid = Convert.ToString(reader["TEMPLETID"]);
|
|
templetname = Convert.ToString(reader["TEMPLETNAME"]);
|
|
templeturl = Convert.ToString(reader["TEMPLETURL"]);
|
|
templetthumb = Convert.ToString(reader["TEMPLETTHUMB"]);
|
|
imgurl = Convert.ToString(reader["IMGURL"]);
|
|
}
|
|
}
|
|
sql.Clear();
|
|
sql.AppendLine("select top 1 ");
|
|
sql.AppendLine("(case when A.SORT in not null then A.SORT else 0 end) as SORT ");
|
|
sql.AppendLine("from templet as A ");
|
|
sql.AppendLine("left join main_module as B on A.TEMPLETID=B.USERTEMPLETID ");
|
|
sql.AppendLine("left join user_mainmd_set as C on C.MODULEID=B.GID ");
|
|
sql.AppendLine("where C.GID='" + globalid + "' ");
|
|
sql.AppendLine("order by A.SORT desc ");
|
|
var tempSort = new List<MsTempletStructure>();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
sort = Convert.ToInt16(reader["SORT"]);
|
|
}
|
|
}
|
|
|
|
var result = new DBResult();
|
|
|
|
//Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
string cmdInsertStr = "insert into templet(TEMPLETID,TEMPLETNAME,TEMPLETURL,TEMPLETTHUMB,HASIMG,MAINIMGURL,";
|
|
cmdInsertStr += "HASLIST,LISTSTYLE,HASCHILD,PARENTITEMID,ITEMID,ITEMTYPE,IMGURL,SORT) values('" + templetid + "','" + templetname;
|
|
cmdInsertStr += "','" + templeturl + "','" + templetthumb + "',0,'',0,'-1',1,'.','" + templetitemid + "','0','" + imgurl + "','" + (sort + 1) + "')";
|
|
var cmdInsert = db.GetSqlStringCommand(cmdInsertStr);
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
|
|
sort++;
|
|
|
|
parentid = System.Guid.NewGuid().ToString();
|
|
string cmdInsertStr3 = "insert into templet(TEMPLETID,TEMPLETNAME,TEMPLETURL,TEMPLETTHUMB,HASIMG,MAINIMGURL,";
|
|
cmdInsertStr3 += "HASLIST,LISTSTYLE,HASCHILD,PARENTITEMID,ITEMID,ITEMTYPE,IMGURL,SORT) values('" + templetid + "','" + templetname;
|
|
cmdInsertStr3 += "','" + templeturl + "','" + templetthumb + "',0,'',0,'-1',1,'" + templetitemid + "','" + parentid + "','5','','" + (sort + 1) + "')";
|
|
var cmdInsert3 = db.GetSqlStringCommand(cmdInsertStr3);
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
|
|
sort++;
|
|
|
|
string cmdInsertStr4 = "insert into templet(TEMPLETID,TEMPLETNAME,TEMPLETURL,TEMPLETTHUMB,HASIMG,MAINIMGURL,";
|
|
cmdInsertStr4 += "HASLIST,LISTSTYLE,HASCHILD,PARENTITEMID,ITEMID,ITEMTYPE,IMGURL,SORT) values('" + templetid + "','" + templetname;
|
|
cmdInsertStr4 += "','" + templeturl + "','" + templetthumb + "',0,'',0,'-1',1,'" + templetitemid + "','" + System.Guid.NewGuid().ToString() + "','1','','" + (sort + 1) + "')";
|
|
var cmdInsert4 = db.GetSqlStringCommand(cmdInsertStr4);
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
|
|
string cmdInsertStr2 = "insert into user_templet(TEMPLETID,COMPANYID,USERID,PARENTID,ITEMID,ITEMNAME,LINKURL,";
|
|
cmdInsertStr2 += "SQL,SORT) values('" + templetid + "','" + companyid + "','" + userid + "','" + parentid + "','" + itemid + "','" + itemname + "','.','','-1')";
|
|
var cmdInsert2 = db.GetSqlStringCommand(cmdInsertStr2);
|
|
db.ExecuteNonQuery(cmdInsert2, tran);
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
tran.Rollback();
|
|
result.Success = false;
|
|
result.Message = "保存出现错误，请重试或联系系统管理员";
|
|
return result;
|
|
}
|
|
}
|
|
result.Success = true;
|
|
result.Message = "保存成功";
|
|
|
|
return result;
|
|
}
|
|
#endregion
|
|
|
|
#region 删除模板信息分组
|
|
public static DBResult DeleteItem(string id, string name, string userid, string companyid)
|
|
{
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
var result = new DBResult();
|
|
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
string cmdDeleteStr = "delete templet where ITEMID=(select PARENTID from user_templet where GID='" + id + "' and ITEMNAME='" + name + "')";
|
|
var cmdDelete = db.GetSqlStringCommand(cmdDeleteStr);
|
|
db.ExecuteNonQuery(cmdDelete, tran);
|
|
|
|
string cmdDeleteStr3 = "delete user_templet ";
|
|
cmdDeleteStr3 += "where PARENTID=(";
|
|
cmdDeleteStr3 += "select ITEMID from templet ";
|
|
cmdDeleteStr3 += "where PARENTITEMID=(select PARENTID from user_templet where GID='" + id + "' and ITEMNAME='" + name + "') and ITEMTYPE='1'";
|
|
cmdDeleteStr3 += ")";
|
|
var cmdDelete3 = db.GetSqlStringCommand(cmdDeleteStr3);
|
|
db.ExecuteNonQuery(cmdDelete3, tran);
|
|
|
|
string cmdDeleteStr2 = "delete user_templet where GID='" + id + "' and ITEMNAME='" + name + "'";
|
|
var cmdDelete2 = db.GetSqlStringCommand(cmdDeleteStr2);
|
|
db.ExecuteNonQuery(cmdDelete2, tran);
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
tran.Rollback();
|
|
result.Success = false;
|
|
result.Message = "删除出现错误，请重试或联系系统管理员";
|
|
return result;
|
|
}
|
|
}
|
|
result.Success = true;
|
|
result.Message = "删除成功";
|
|
|
|
return result;
|
|
}
|
|
#endregion
|
|
|
|
#region 保存模板信息
|
|
public static DBResult InsertItemLink(string groupid, string parentValue, string inputid, string itemname, string linkurl, string inquire, string userid, string companyid)
|
|
{
|
|
string itemid = "";// System.Guid.NewGuid().ToString();
|
|
string templetid = "";
|
|
string parentid = "";
|
|
int sort = 0;
|
|
|
|
itemid = inputid;
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
StringBuilder sql = new StringBuilder();
|
|
/*sql.AppendLine("SELECT ITEMID,TEMPLETID ");
|
|
sql.AppendLine("FROM templet ");
|
|
sql.AppendLine("WHERE ITEMTYPE=1 and PARENTITEMID= ");
|
|
sql.AppendLine("(SELECT PARENTID FROM user_templet WHERE GID='" + groupid + "') ");// and ITEMNAME='" + groupname + "'
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
templetid = Convert.ToString(reader["TEMPLETID"]);
|
|
parentid = Convert.ToString(reader["ITEMID"]);
|
|
}
|
|
}*/
|
|
/*templetid*/
|
|
/*select USERTEMPLETID from main_module
|
|
where GID=(select MODULEID from user_mainmd_set where GID='')*/
|
|
/*parentid*/
|
|
/*select ITEMID from templet
|
|
where ITEMTYPE=1 and PARENTITEMID=(select ITEMID from user_templet where ITEMNAME='')*/
|
|
/*sql.AppendLine("select ");
|
|
sql.AppendLine("(select USERTEMPLETID from main_module ");
|
|
sql.AppendLine("where GID=(select MODULEID from user_mainmd_set where GID='" + groupid + "')) as TEMPLETID, ");
|
|
sql.AppendLine("(select PARENTITEMID from templet ");
|
|
sql.AppendLine("where ITEMID=(select ITEMID from user_templet where ITEMNAME='" + parentValue + "')) as PARENTID ");*/
|
|
sql.AppendLine("select ");
|
|
sql.AppendLine("(select USERTEMPLETID from main_module ");
|
|
sql.AppendLine("where GID=(select MODULEID from user_mainmd_set where GID='" + groupid + "')) as TEMPLETID, ");
|
|
sql.AppendLine("(select ITEMID from templet where itemtype=1 and parentitemid = (select PARENTITEMID from templet ");
|
|
sql.AppendLine("where ITEMID=(select ITEMID from user_templet where ITEMNAME='" + parentValue + "'))) as PARENTID ");
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
templetid = Convert.ToString(reader["TEMPLETID"]);
|
|
parentid = Convert.ToString(reader["PARENTID"]);
|
|
}
|
|
}
|
|
sql.Clear();
|
|
/*sql.AppendLine("SELECT TOP 1 ");
|
|
sql.AppendLine("(case when SORT is not null then SORT else 0 end ) as SORT ");
|
|
sql.AppendLine("FROM user_templet WHERE PARENTID='" + parentid + "' ORDER BY SORT DESC ");*/
|
|
sql.AppendLine("SELECT TOP 1 ");
|
|
sql.AppendLine("(case when SORT is not null then SORT else 0 end ) as SORT ");
|
|
sql.AppendLine("FROM user_templet WHERE PARENTID='" + parentid + "' ORDER BY SORT DESC ");
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
sort = Convert.ToInt16(reader["SORT"]);
|
|
}
|
|
}
|
|
|
|
var result = new DBResult();
|
|
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
/*string cmdInsertStr = "insert into templet(TEMPLETID,TEMPLETNAME,TEMPLETURL,TEMPLETTHUMB,HASIMG,MAINIMGURL,";
|
|
cmdInsertStr += "HASLIST,LISTSTYLE,HASCHILD,PARENTITEMID,ITEMID,ITEMTYPE,IMGURL,SORT) values('" + templetid + "','" + templetname;
|
|
cmdInsertStr += "','" + templeturl + "','" + templetthumb + "',0,'',0,'-1',1,'.','" + templetitemid + "','0','" + imgurl + "','" + (sort + 1) + "')";
|
|
var cmdInsert = db.GetSqlStringCommand(cmdInsertStr);
|
|
db.ExecuteNonQuery(cmdInsert, tran);*/
|
|
var _gid = "";
|
|
sql.Clear();
|
|
sql.AppendLine("SELECT GID FROM user_templet WHERE ITEMID='" + inputid + "' ");
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
_gid = Convert.ToString(reader["GID"]);
|
|
}
|
|
}
|
|
|
|
string cmdStr = "";
|
|
if (inquire.IndexOf("'") != -1 && inquire.IndexOf("''") == -1)
|
|
{
|
|
inquire = inquire.Replace("'", "''");
|
|
}
|
|
if (_gid != "")
|
|
{
|
|
cmdStr = "update user_templet set ITEMNAME='" + itemname + "',LINKURL='" + linkurl + "',SQL='" + inquire + "' where ITEMID='" + itemid + "' ";
|
|
}
|
|
else
|
|
{
|
|
cmdStr = "insert into user_templet(TEMPLETID,COMPANYID,USERID,PARENTID,ITEMID,ITEMNAME,LINKURL,SQL,SORT) ";
|
|
cmdStr += "values('" + templetid + "','" + companyid + "','" + userid + "','" + parentid + "','" + itemid + "','" + itemname + "','" + linkurl + "','" + inquire + "','" + (sort + 1) + "')";
|
|
}
|
|
|
|
var cmdInsert = db.GetSqlStringCommand(cmdStr);
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
tran.Rollback();
|
|
result.Success = false;
|
|
result.Message = "保存出现错误，请重试或联系系统管理员";
|
|
return result;
|
|
}
|
|
}
|
|
result.Success = true;
|
|
result.Message = "保存成功";
|
|
|
|
return result;
|
|
}
|
|
#endregion
|
|
|
|
#region 删除模板信息
|
|
public static DBResult DeleteItemLink(string itemid)
|
|
{
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
|
var result = new DBResult();
|
|
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
string cmdDeleteStr = "delete user_templet where ITEMID='" + itemid + "' ";
|
|
var cmdDelete = db.GetSqlStringCommand(cmdDeleteStr);
|
|
db.ExecuteNonQuery(cmdDelete, tran);
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
tran.Rollback();
|
|
result.Success = false;
|
|
result.Message = "删除出现错误，请重试或联系系统管理员";
|
|
return result;
|
|
}
|
|
}
|
|
result.Success = true;
|
|
result.Message = "删除成功";
|
|
|
|
return result;
|
|
}
|
|
#endregion
|
|
|
|
#region 保存模块信息
|
|
public static DBResult SaveFormSet(string title, string usergid, string top, string left, string width, string height, string userid)
|
|
{
|
|
string gid = "";
|
|
int count = 0;
|
|
int sort = 0;
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
StringBuilder sql = new StringBuilder();
|
|
sql.AppendLine("select GID from main_module where MODULENAME='" + title + "' ");
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
gid = Convert.ToString(reader["GID"]);
|
|
}
|
|
}
|
|
sql.Clear();
|
|
sql.AppendLine("select count(*) as count from user_mainmd_set where MODULEID='" + gid + "' and USERID='" + usergid + "'");
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
count = Convert.ToInt16(reader["count"]);
|
|
}
|
|
}
|
|
sql.Clear();
|
|
sql.AppendLine("select top 1 sort from user_mainmd_set where MODULEID='" + gid + "' order by sort desc");
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
sort = Convert.ToInt16(reader["sort"]);
|
|
}
|
|
}
|
|
|
|
var result = new DBResult();
|
|
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
string cmdInsertStr = "";
|
|
|
|
if (count == 0)
|
|
{
|
|
cmdInsertStr = "insert into user_mainmd_set(GID,MODULEID,USERID,MODULEWIDTH,MODULEHEIGHT,MODULETOP,MODULELEFT,COLOURSTYLE,ISVISIABLE,SORT,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME) ";
|
|
cmdInsertStr += "values('" + System.Guid.NewGuid().ToString() + "','"+gid+"','"+usergid+"','"+width+"','"+height+"','"+top+"','"+left+"','1',1,'"+(sort+1)+"','"+userid+"','"+DateTime.Now.ToString()+"','"+userid+"','"+DateTime.Now.ToString()+"')";
|
|
}
|
|
else
|
|
{
|
|
cmdInsertStr = "update user_mainmd_set set MODULEWIDTH='" + width + "',MODULEHEIGHT='" + height + "',MODULETOP='" + top + "',MODULELEFT='" + left + "' where MODULEID='" + gid + "' and USERID='" + usergid + "'";
|
|
}
|
|
var cmdInsert = db.GetSqlStringCommand(cmdInsertStr);
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
tran.Rollback();
|
|
result.Success = false;
|
|
result.Message = "保存出现错误，请重试或联系系统管理员";
|
|
return result;
|
|
}
|
|
}
|
|
|
|
result.Success = true;
|
|
result.Message = "保存成功";
|
|
|
|
return result;
|
|
}
|
|
#endregion
|
|
|
|
#region 个人模块列表
|
|
|
|
static public List<UserMainmoduleSet> GetUserDataList(string strCondition, string userid, string sort = null)
|
|
{
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("U.GID,U.MODULEID,U.USERID,U.MODULEWIDTH,U.SORT,M.MODULENAME,M.MODULETITLE,M.MODULEURL,M.ISSYS");
|
|
strSql.Append(" from user_mainmd_set U left join main_module m on (m.GID=U.MODULEID) where U.userid='"+userid+"'");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" and " + strCondition);
|
|
}
|
|
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else
|
|
{
|
|
strSql.Append(" order by SORT ");
|
|
|
|
}
|
|
return SetUserModuleData(strSql);
|
|
}
|
|
|
|
|
|
static public List<UserMainmoduleSet> GetUserMainList(string strCondition, string userid, string sort = null)
|
|
{
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("U.GID,U.MODULEID,U.USERID,U.MODULEWIDTH,U.SORT,M.MODULENAME,M.MODULETITLE,M.MODULEURL,M.ISSYS");
|
|
strSql.Append(" from user_mainmd_set U left join main_module m on (m.GID=U.MODULEID) where U.userid='" + userid + "'");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" and " + strCondition);
|
|
}
|
|
|
|
strSql.Append("UNION ");
|
|
strSql.Append(" GID,GID AS MODULEID,'"+userid+"' AS USERID,MODULEWIDTH,0 SORT,MODULENAME,MODULETITLE,MODULEURL,ISSYS");
|
|
strSql.Append(" from main_module WHERE ISSYS=1 AND ISUSE=1 AND GID NOT IN (SELECT MODULEID from user_mainmd_set WHERE userid='" + userid + "')");
|
|
|
|
|
|
strSql.Append(" order by SORT ");
|
|
|
|
|
|
return SetUserModuleData(strSql);
|
|
}
|
|
|
|
|
|
private static List<UserMainmoduleSet> SetUserModuleData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<UserMainmoduleSet>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
UserMainmoduleSet data = new UserMainmoduleSet();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
data.MODULEID = Convert.ToString(reader["MODULEID"]);
|
|
data.USERID = Convert.ToString(reader["USERID"]);
|
|
data.MODULENAME = Convert.ToString(reader["MODULENAME"]);
|
|
data.MODULETITLE = Convert.ToString(reader["MODULETITLE"]);
|
|
data.MODULEURL = Convert.ToString(reader["MODULEURL"]);
|
|
if (reader["SORT"] != DBNull.Value)
|
|
data.SORT = Convert.ToInt16(reader["SORT"]);
|
|
|
|
if (reader["MODULEWIDTH"] != DBNull.Value)
|
|
data.MODULEWIDTH = Convert.ToInt16(reader["MODULEWIDTH"]);
|
|
if (reader["ISSYS"] != DBNull.Value)
|
|
data.ISSYS = Convert.ToBoolean(reader["ISSYS"]);
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
|
|
public static DBResult SaveUserSetDetail(List<UserMainmoduleSet> bodyList, string userid)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
|
|
|
|
var cmdDelete = db.GetSqlStringCommand("delete from user_mainmd_set where USERID='" + userid + "'");
|
|
db.ExecuteNonQuery(cmdDelete, tran);
|
|
|
|
var cmdInsert =
|
|
db.GetSqlStringCommand(
|
|
@"insert into user_mainmd_set (GID, MODULEID, USERID,MODULEWIDTH,SORT,CREATEUSER,CREATETIME)
|
|
values (@GID, @MODULEID, @USERID,@MODULEWIDTH,@SORT,@CREATEUSER,@CREATETIME) ");
|
|
|
|
if (bodyList != null)
|
|
{
|
|
foreach (var enumValue in bodyList)
|
|
{
|
|
cmdInsert.Parameters.Clear();
|
|
|
|
db.AddInParameter(cmdInsert, "@GID", DbType.String, enumValue.GID);
|
|
db.AddInParameter(cmdInsert, "@MODULEID", DbType.String, enumValue.MODULEID);
|
|
db.AddInParameter(cmdInsert, "@USERID", DbType.String, userid);
|
|
db.AddInParameter(cmdInsert, "@MODULEWIDTH", DbType.Int16, enumValue.MODULEWIDTH);
|
|
db.AddInParameter(cmdInsert, "@SORT", DbType.Int16, enumValue.SORT);
|
|
db.AddInParameter(cmdInsert, "@CREATEUSER", DbType.String, userid);
|
|
db.AddInParameter(cmdInsert, "@CREATETIME", DbType.DateTime, DateTime.Now);
|
|
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
}
|
|
}
|
|
|
|
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
|
|
result.Success = false;
|
|
result.Message = "保存出现错误，请重试或联系系统管理员";
|
|
|
|
return result;
|
|
}
|
|
}
|
|
|
|
result.Success = true;
|
|
result.Message = "保存成功";
|
|
|
|
return result;
|
|
}
|
|
|
|
public static DBResult DeleteDetail(string gid)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
|
|
|
|
var cmdDelete = db.GetSqlStringCommand("delete from user_mainmd_set where GID='" + gid + "'");
|
|
db.ExecuteNonQuery(cmdDelete, tran);
|
|
|
|
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
|
|
result.Success = false;
|
|
result.Message = "删除出现错误，请重试或联系系统管理员";
|
|
|
|
return result;
|
|
}
|
|
}
|
|
|
|
result.Success = true;
|
|
result.Message = "删除成功";
|
|
|
|
return result;
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
#region 参照部分
|
|
|
|
#endregion
|
|
}
|
|
}
|