public ActionResult GetUserList()
{
Users users = new Users();
string strWhere = " 1=1 ";
string KeyWord = DNTRequest.GetString("KeyWord");
string RoleID = DNTRequest.GetString("RoleID");
int rows = DNTRequest.GetInt("rows", 10);
if (KeyWord != "")
{
strWhere += " and (Uname like '%" + KeyWord + "%') or ( UId in (select UId from .[dbo].Users where truename like '%" + KeyWord + "%')) ";
}
if (RoleID != "")
{
strWhere += " and UId in (select UId from .[dbo].Users where roleId=" + RoleID.ToString() + ")";
}
int count = 0;
//string sql = @"select p.Uname,p.Sex,p.roleId,p.truename,p.UId from Users p order by UId desc";
//string sql = @"select * ,(select top 1 truename from [dbo].Users where UId=m.UId) as truename, (select roleName from Role where roleId in(select top 1 roleId from Users where UId=m.UId)) as roelName from .[dbo].Users m where " + strWhere;
DataSet ds = bll.GetUserList();
// DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, 7, rows, "UId desc"), out count);
List <VURser> list = TBToList <VURser> .ConvertToList(ds.Tables[0]).ToList();
var grid = new EasyuiDataGrid <List <VURser> >();
grid.total = count;
grid.rows = list;
return(Json(grid, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 普通无分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public static List <T> GetEntitiesListByDB <T>(string sql)
{
IRDBHelper helper = null;
List <T> list = new List <T>();
try
{
string strCon = ManageConfig.instance.GetConnectionStrings("SHHConnection");
helper = RDBFactory.CreateDbHelper(strCon, DatabaseType.MSSQL);
DataTable dataTable = helper.ExecuteDatatable("table", sql, true);
list = TBToList.ToList <T>(dataTable);
return(list);
}
catch (Exception ex)
{
LogAPI.Debug(ex);
return(list);
}
finally
{
if (helper != null)
{
helper.DisConnect();
helper = null;
}
}
}
public ActionResult GetRoleList(int?page)
{
page = page == null ? 1 : page;
//string sql = "select m.*,d.ItemText StatusName from tb_Role m left join tb_Doption d on d.MoptionID=2 and d.ItemValue=m.Status where 1=1 ";
string KeyWord = DNTRequest.GetString("KeyWord");
string Status = DNTRequest.GetString("Status");
int rows = DNTRequest.GetInt("rows", 10);
//if (KeyWord != "")
//{
// sql += " and (m.RoleName like '%" + KeyWord + "%') ";
//}
//if (Status != "")
//{
// sql += " and (m.Status =" + Status + ")";
//}
int count = 0;
DataSet ds = ubll.GetList();
//DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, page.Value, rows, "RoleID desc"), out count);
List <Role> list = TBToList <Role> .ConvertToList(ds.Tables[0]).ToList();
var grid = new EasyuiDataGrid <List <Role> >();
grid.total = count;
grid.rows = list;
//JavaScriptSerializer js = new JavaScriptSerializer();
return(Json(grid, JsonRequestBehavior.AllowGet));
}
public List <Share_County> GetCountyByCode(string code)
{
string sql = "select top 1 * from Share_County where code=@code";
SqlParameter[] parameters = { new SqlParameter("@code", code) };
DataTable dt = SqlHelper.GetDataTable(CommandType.Text, sql, parameters);
IList <Share_County> IList = new TBToList <Share_County>().ToList(dt);
return(IList.ToList());
}
/// <summary>
/// RDBHelper 查询分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">sql语句</param>
/// <param name="skip">页数</param>
/// <param name="take">每页显示条数</param>
/// <param name="count">总数</param>
/// <returns></returns>
public static List <T> GetEntitiesListByDB <T>(string sql, int skip, int take, out int count)
{
IRDBHelper helper = null;
List <T> list = new List <T>();
try
{
string strCon = ManageConfig.instance.GetConnectionStrings("SHHConnection");
helper = RDBFactory.CreateDbHelper(strCon, DatabaseType.MSSQL);
if (helper != null)
{
string countSQL = string.Format("select count(1) as NUM from({0})", sql);
DataTable dt = helper.ExecuteDatatable("table", countSQL, true);
if (dt != null && dt.Rows.Count > 0)
{
count = int.Parse(dt.Rows[0]["NUM"].ToString());
}
else
{
count = 0;
LogAPI.Debug("查询总数失败！");
}
//分页查询
string strBegin = "select * from (select a.* ,rownum rn from ( ";
string strEnd = string.Format(")a where rownum < {0} ) where rn>{1} ", take, skip);
string querySQL = strBegin + sql + strEnd;
DataTable dataTable = helper.ExecuteDatatable("table", querySQL, true);
list = TBToList.ToList <T>(dataTable);
}
else
{
count = 0;
LogAPI.Debug("数据库打开失败！");
}
return(list);
}
catch (Exception ex)
{
count = 0;
LogAPI.Debug("查询受理列表异常：");
LogAPI.Debug(ex);
return(list);
}
finally
{
if (helper != null)
{
helper.DisConnect();
helper = null;
}
}
}
public ActionResult GetGoodsList()
{
int count = 0;
//string sql = @"select p.Uname,p.Sex,p.roleId,p.truename,p.UId from Users p order by UId desc";
//string sql = @"select * ,(select top 1 truename from [dbo].Users where UId=m.UId) as truename, (select roleName from Role where roleId in(select top 1 roleId from Users where UId=m.UId)) as roelName from .[dbo].Users m where " + strWhere;
DataSet ds = bll.GetGoodsList();
List <Goods> list = TBToList <Goods> .ConvertToList(ds.Tables[0]).ToList();
var grid = new EasyuiDataGrid <List <Goods> >();
grid.total = count;
grid.rows = list;
return(Json(grid, JsonRequestBehavior.AllowGet));
}
public ActionResult GetSOList()
{
int count = 0;
//string sql = @"select p.Uname,p.Sex,p.roleId,p.truename,p.UId from Users p order by UId desc";
//string sql = @"select * ,(select top 1 truename from [dbo].Users where UId=m.UId) as truename, (select roleName from Role where roleId in(select top 1 roleId from Users where UId=m.UId)) as roelName from .[dbo].Users m where " + strWhere;
DataSet ds = bll.GetsOrdermin();
// DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, 7, rows, "UId desc"), out count);
List <VOrderSminxi> list = TBToList <VOrderSminxi> .ConvertToList(ds.Tables[0]).ToList();
var grid = new EasyuiDataGrid <List <VOrderSminxi> >();
grid.total = count;
grid.rows = list;
return(Json(grid, JsonRequestBehavior.AllowGet));
}
public EasyUIGridModel GetAttachLst(FileUploadQueryForm form, EasyUIGridSetting gridSetting)
{
string sql = string.Format(@"SELECT CID,PNODE as SLBH,CNAME,to_char(CREATEDATE,'yy-mm-dd hh24:mi:ss') CREATEDATE
FROM (Select ROWNUM AS ROWNO, T.*
from (select CID,PNODE,CNAME,CREATEDATE from WFM_ATTACHLST where {0}) T
WHERE ROWNUM <= {1}) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO > {2}", form.GetWhere(), gridSetting.PageIndex * gridSetting.PageSize, (gridSetting.PageIndex - 1) * gridSetting.PageSize);
string connectStr = ConfigurationManager.ConnectionStrings["bdcggkConnection"].ToString();
DataSet ds = DBHelper.GetDataSet(connectStr, sql);
TBToList <AttachModel> list = new TBToList <AttachModel>();
IList <AttachModel> tags = list.ConvertToModel(ds.Tables[0]);
string countSql = string.Format("Select count(1) from WFM_ATTACHLST where {0}", form.GetWhere());
int count = DBHelper.GetScalar(connectStr, countSql);
EasyUIGridModel ret = new EasyUIGridModel(gridSetting.PageIndex, count, tags);
return(ret);
}
public EasyUIGridModel GetWriteBackResult(WriteBackResultQueryForm form, EasyUIGridSetting gridSetting)
{
string sql = string.Format(@"SELECT SLBH,DJLX,SFTS,PUSHDATA,PUSHDATE,PUSHRESULT,WBERRINFO,CGERRINFO,PID,ADDRESSTYPE
FROM (Select ROWNUM AS ROWNO, T.*
from (select * from FC_SPFHX_TAG where {0} order by pushdate desc) T
WHERE ROWNUM <= {1}) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO > {2} ", form.GetWhere(), gridSetting.PageIndex * gridSetting.PageSize, (gridSetting.PageIndex - 1) * gridSetting.PageSize, gridSetting.SortBy.PropertyName, gridSetting.SortBy.SortType);
if (gridSetting.SortBy.PropertyName != "")
{
sql += " order by " + gridSetting.SortBy.PropertyName + " " + gridSetting.SortBy.SortType;
}
DataSet ds = DBHelper.GetDataSet(sql);
TBToList <SPFHX_TAG> list = new TBToList <SPFHX_TAG>();
IList <SPFHX_TAG> tags = list.ConvertToModel(ds.Tables[0]);
string countSql = string.Format("Select count(1) from FC_SPFHX_TAG where {0}", form.GetWhere());
int count = DBHelper.GetScalar(countSql);
EasyUIGridModel ret = new EasyUIGridModel(gridSetting.PageIndex, count, tags);
return(ret);
}
public ActionResult GetProductTypeList(int id = 0, string KeyWord = "")
{
int count = 0;
string sql = "SELECT [id] ,[code] ,[name],[ParentId],[AddTime] FROM [" + DBName + "].[dbo].[tb_ProductType] ";
string strWhere = " Where 1=1";
if (KeyWord != "")
{
strWhere += " and ( id in ( select id from [" + DBName + "].[dbo].[tb_ProductType] where code like '%" + KeyWord + "%' or name like '%" + KeyWord + "%' ) ";
strWhere += " or id in ( select ParentId from [" + DBName + "].[dbo].[tb_ProductType] where code like '%" + KeyWord + "%' or name like '%" + KeyWord + "%' ) ) ";
}
//sql = sql + strWhere;
//DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, 1, 10000, "id desc"), out count);
//List<MenuPage> list = TBToList<MenuPage>.ConvertToList(ds.Tables[0]).ToList();
////List<tb_ProductType> list = ef.tb_ProductType.Where(p => p.MenuID > 0).ToList();
//List<MenuPage> listJson = new List<MenuPage>();
//foreach (tb_ProductType model in list.Where(p => p.ParentId == 0).OrderBy(p => p.id))
//{
// MakeMenuJson(list, model, id);
// listJson.Add(model);
//}
//return Json(listJson, JsonRequestBehavior.AllowGet);
GoodsBLL bn = new GoodsBLL();
//string sql = @"select p.Uname,p.Sex,p.roleId,p.truename,p.UId from Users p order by UId desc";
//string sql = @"select * ,(select top 1 truename from [dbo].Users where UId=m.UId) as truename, (select roleName from Role where roleId in(select top 1 roleId from Users where UId=m.UId)) as roelName from .[dbo].Users m where " + strWhere;
DataSet ds = bn.GetMenu();
// DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, 7, rows, "UId desc"), out count);
List <MenuPage> list = TBToList <MenuPage> .ConvertToList(ds.Tables[0]).ToList();
var grid = new EasyuiDataGrid <List <MenuPage> >();
grid.total = count;
grid.rows = list;
return(Json(grid, JsonRequestBehavior.AllowGet));
}
public ActionResult GetListtb_Store(int?page)
{
string KeyWord = DNTRequest.GetString("KeyWord");
int rows = DNTRequest.GetInt("rows", 10);
string strWhere = " 1=1 ";
if (KeyWord != "")
{
strWhere += " and (Name like '%" + KeyWord + "%' or code like '%" + KeyWord + "%') ";
}
int count = 0;
DataSet ds = gb.GetMenushop();
//string sql = @"select * from [" + DBName + @"].[dbo].[tb_Store] where " + strWhere;
//DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, page.Value, rows, "id desc"), out count);
List <VUShop> list = TBToList <VUShop> .ConvertToList(ds.Tables[0]).ToList();
var grid = new EasyuiDataGrid <List <VUShop> >();
grid.total = count;
grid.rows = list;
return(Json(grid, JsonRequestBehavior.AllowGet));
}
