//public static string Get_tbl_users_json(ZhWebClass.CmnObj.IsAddIndexZero isAddIndexZero,string statusx = "10") //{ // DataTable tmpTable = Get_tbl_users(isAddIndexZero, statusx); // JArray ja = new JArray(); // foreach (DataRow dr in tmpTable.Rows) // { // JObject itemObject = new JObject // { // {"sysUserId",dr["sysUserId"].ToString().Trim()}, // {"userName2",dr["userName2"].ToString().Trim()} // }; // ja.Add(itemObject); // } // return JsonConvert.SerializeObject(ja); //} #endregion #region 問卷對象設定 public static DataTable Get_tbl_objectId(ZhConfig.IsAddIndexZero isAddIndexZero, string creatUser) { //string strSql = "select objectId,objectName,convert(nvarchar(max),objectId)+':'+objectName as objectName2 from Q10_objects "; string strSql = "select sysObjectId, objectId, objectName from Q10_objects where creatUser='******' and statusx='10' "; #region 組查詢條件 //string strCond = "Where statusx='" + statusx + "'"; #endregion //strSql = strSql + strCond; DataTable tmpTable = SqlTool.GetDataTable(ZhConfig.GlobalSystemVar.StrConnection1, strSql, "Q10_objects"); tmpTable.PrimaryKey = new DataColumn[] { tmpTable.Columns["sysObjectId"] }; if (isAddIndexZero == ZhConfig.IsAddIndexZero.Yes) { DataRow tmpRow = tmpTable.NewRow(); tmpRow["sysObjectId"] = 0; ZhConfig.ZhIniObj.addZeroRowColumnInfo(tmpRow, "objectName"); tmpTable.Rows.InsertAt(tmpRow, 0); } return(tmpTable); }
public static DataTable Get_tbl_users(ZhConfig.IsAddIndexZero isAddIndexZero, string statusx = "10") { string strSql = "select sysUserId,userName,userId+':'+userName as userName2 from u_S10_users "; #region 組查詢條件 string strCond = "Where statusx='" + statusx + "'"; #endregion strSql = strSql + strCond; DataTable tmpTable = SqlTool.GetDataTable(ZhConfig.GlobalSystemVar.StrConnection1, strSql, "tbl_users"); tmpTable.PrimaryKey = new DataColumn[] { tmpTable.Columns["sysUserId"] }; if (isAddIndexZero == ZhConfig.IsAddIndexZero.Yes) { DataRow tmpRow = tmpTable.NewRow(); tmpRow["sysUserId"] = 0; ZhConfig.ZhIniObj.addZeroRowColumnInfo(tmpRow, "userName", "userName2"); tmpTable.Rows.InsertAt(tmpRow, 0); } return(tmpTable); }
public static DataTable Get_tbl_menus(string sysUserGroupIds) { string[] a = sysUserGroupIds.Split(','); string userGroups = ""; for (int i = 0; i < a.Length; i++) { userGroups += a[i] + "','"; } if (userGroups.Length > 0) { userGroups = "'" + userGroups.Substring(0, userGroups.Length - 2); } else { userGroups = "'#'"; } StringBuilder strSql = new StringBuilder(200); strSql.Append("select m.sysMenuId,menuId,menuName,sysParentId,menuParentId,sortValue,url,ug.sysUserGroupId into #tmpTbl from u_S00_menus m "); strSql.Append("INNER join S10_userGroupPermissions ug on m.sysMenuId=ug.sysMenuId "); strSql.Append("where ug.sysUserGroupId in (" + userGroups + ") and ug.limitId>0 and statusx='10' order by sortvalue "); strSql.Append("select distinct sysMenuId,menuId,menuName,sysParentId,menuParentId,sortValue,url from #tmpTbl "); DataTable tmpTable = SqlTool.GetDataTable(ZhConfig.GlobalSystemVar.StrConnection1, strSql.ToString(), "tbl_menus"); tmpTable.PrimaryKey = new DataColumn[] { tmpTable.Columns["menuId"] }; return(tmpTable); }
public static DataTable Get_tbl_sysParameters(ZhConfig.IsAddIndexZero isAddIndexZero, string parentId, string statusx = "10") { string strSql = "select paraId,paraName,paraId+':'+paraName as paraName2 from S00_parameters where parentId='" + parentId + "' and statusx='" + statusx + "' "; DataTable tmpTable = SqlTool.GetDataTable(ZhConfig.GlobalSystemVar.StrConnection1, strSql, "S00_parameters"); tmpTable.PrimaryKey = new DataColumn[] { tmpTable.Columns["paraId"] }; if (parentId == "statusx") { tmpTable.Rows.Find("30").Delete(); } tmpTable.AcceptChanges(); if (isAddIndexZero == ZhConfig.IsAddIndexZero.Yes) { DataRow tmpRow = tmpTable.NewRow(); tmpRow["paraId"] = ""; ZhConfig.ZhIniObj.addZeroRowColumnInfo(tmpRow, "paraName", "paraName2"); tmpTable.Rows.InsertAt(tmpRow, 0); } return(tmpTable); }
public static DataTable Get_tbl_menus() { string strSql = "select sysMenuId,menuId,menuName,sysParentId,menuParentId,sortValue,url from u_S00_menus where statusx='10' order by sortvalue "; DataTable tmpTable = SqlTool.GetDataTable(ZhConfig.GlobalSystemVar.StrConnection1, strSql, "u_S00_menus"); //DataRow tmprow = tmpTable.NewRow(); //tmprow["reasonId"] = ""; //tmprow["reasonName"] = "請選擇"; //tmprow["reasonName2"] = "請選擇"; //tmpTable.Rows.InsertAt(tmprow, 0); tmpTable.PrimaryKey = new DataColumn[] { tmpTable.Columns["menuId"] }; return(tmpTable); }
public static List <SelectListItem> Get_tbl_right_Type1_selItem(ZhConfig.IsAddIndexZero isAddIndexZero) { List <SelectListItem> selItem = new List <SelectListItem>(); string strSql = " select right_Type1 value,'(' + right_Type1 + ')' + Alias text from 行政使用權限 "; strSql += " where Right_Type1 not like '%Z%' and Right_Type1 not like '%W%' and Right_Type1 not like '%Y%' order by Right_Type1 "; DataTable tmpTable = SqlTool.GetDataTable(ZhConfig.GlobalSystemVar.StrConnection2, strSql, "tmpTbl"); //tmpTable.PrimaryKey = new DataColumn[] { tmpTable.Columns["縣市ID"] }; if (isAddIndexZero == ZhConfig.IsAddIndexZero.Yes) { DataRow tmpRow = tmpTable.NewRow(); tmpRow["value"] = ""; tmpRow["text"] = "所有通報管理權限"; //ZhConfig.ZhIniObj.addZeroRowColumnInfo(tmpRow, "縣市"); tmpTable.Rows.InsertAt(tmpRow, 0); } for (int i = 0; i < tmpTable.Rows.Count; i++) { if (i == 0) { selItem.Add(new SelectListItem() { Value = tmpTable.Rows[i]["value"].ToString(), Text = tmpTable.Rows[i]["text"].ToString(), Selected = true }); } else { selItem.Add(new SelectListItem() { Value = tmpTable.Rows[i]["value"].ToString(), Text = tmpTable.Rows[i]["text"].ToString() }); } } return(selItem); }
public static List <SelectListItem> Get_tbl_cityId_selItem(ZhConfig.IsAddIndexZero isAddIndexZero) { List <SelectListItem> selItem = new List <SelectListItem>(); string strSql = "select 縣市ID , 縣市 from 縣市_NEW "; DataTable tmpTable = SqlTool.GetDataTable(ZhConfig.GlobalSystemVar.StrConnection2, strSql, "tmpTbl"); //tmpTable.PrimaryKey = new DataColumn[] { tmpTable.Columns["縣市ID"] }; if (isAddIndexZero == ZhConfig.IsAddIndexZero.Yes) { DataRow tmpRow = tmpTable.NewRow(); tmpRow["縣市ID"] = 0; ZhConfig.ZhIniObj.addZeroRowColumnInfo(tmpRow, "縣市"); tmpTable.Rows.InsertAt(tmpRow, 0); } for (int i = 0; i < tmpTable.Rows.Count; i++) { if (i == 0) { selItem.Add(new SelectListItem() { Value = tmpTable.Rows[i]["縣市ID"].ToString(), Text = tmpTable.Rows[i]["縣市"].ToString(), Selected = true }); } else { selItem.Add(new SelectListItem() { Value = tmpTable.Rows[i]["縣市ID"].ToString(), Text = tmpTable.Rows[i]["縣市"].ToString() }); } } return(selItem); }
//public static string Get_tbl_userGroup_json(ZhWebClass.CmnObj.IsAddIndexZero isAddIndexZero,string statusx = "10") //{ // DataTable tmpTable = Get_tbl_userGroup(isAddIndexZero, statusx); // JArray ja = new JArray(); // foreach (DataRow dr in tmpTable.Rows) // { // JObject itemObject = new JObject // { // {"sysUserGroupId",dr["sysUserGroupId"].ToString().Trim()}, // {"userGroupName",dr["userGroupName"].ToString().Trim()} // }; // ja.Add(itemObject); // } // return JsonConvert.SerializeObject(ja); //} #endregion #region GetData tbl_limit public static DataTable Get_tbl_limit(ZhConfig.IsAddIndexZero isAddIndexZero) { string strSql = "SELECT limitId,limitName,limitId+':'+limitName as limitName FROM S00_limit where enablex=1 "; DataTable tmpTable = SqlTool.GetDataTable(ZhConfig.GlobalSystemVar.StrConnection1, strSql, "limit"); tmpTable.PrimaryKey = new DataColumn[] { tmpTable.Columns["limitId"] }; if (isAddIndexZero == ZhConfig.IsAddIndexZero.Yes) { DataRow tmpRow = tmpTable.NewRow(); tmpRow["limitId"] = 0; ZhConfig.ZhIniObj.addZeroRowColumnInfo(tmpRow, "limitName", "limitName2"); tmpTable.Rows.InsertAt(tmpRow, 0); } return(tmpTable); }
//[ValidateAntiForgeryToken] public ActionResult Login2(Models.ModelBase.ZhLoginViewModel model) { JObject jo = new JObject(); string errStr = ""; try { if (!ModelState.IsValid) { return(View(model)); } string strSql = ""; if (model.userType == "admin") { strSql = "select sysUserId,userName, 'QD' as objectName, '' as USERSSN from S10_users where userId=@userId and userPassword=@userPassword and statusx='10'"; } else { strSql = "select sysUserId=-1,USERName as userName, USERSSN, '' as objectName from USERS where USERID=@userId and PW=@userPassword "; } SqlParameter[] param = { new SqlParameter("userId", model.userId), new SqlParameter("userPassword", model.userPassword) }; DataTable tmpTbl = new DataTable(); if (model.userType == "admin") { tmpTbl = SqlTool.GetDataTable(strSql, "users", param); } else { tmpTbl = SqlTool.GetDataTable(ZhConfig.GlobalSystemVar.StrConnection2, strSql, "users", param); if (tmpTbl.Rows.Count == 0) { strSql = "select sysUserId=-2, USERName as userName, 行政管理SN as USERSSN, '' as objectName from 行政管理成員 where USERID=@userId and PW=@userPassword "; tmpTbl = SqlTool.GetDataTable(ZhConfig.GlobalSystemVar.StrConnection2, strSql, "users", param); } } if (tmpTbl.Rows.Count >= 1) //可以登入 { strSql = "SELECT sysUserGroupId from S10_userVsGroup where sysUserId='" + tmpTbl.Rows[0]["sysUserId"].ToString() + "'"; #region 取得使用者群組 DataTable tmpTbl2 = SqlTool.GetDataTable(strSql, "userVsGroup"); //ZhWebClassV3.UserData userData = new ZhWebClassV3.UserData(); ZhWebClassV3.UserData userDataObj = new ZhWebClassV3.UserData(); userDataObj.actSerial = Convert.ToInt32(ZhClass.AutoSerialNoType2.A_GetAutoSerial("S90_userLog", "actSerial")); userDataObj.sysUserId = Convert.ToInt32(tmpTbl.Rows[0]["sysUserId"]); userDataObj.userId = model.userId; userDataObj.userName = tmpTbl.Rows[0]["userName"].ToString(); userDataObj.sysUserGroupIds = ""; userDataObj.objectName = tmpTbl.Rows[0]["objectName"].ToString(); userDataObj.USERSSN = tmpTbl.Rows[0]["USERSSN"].ToString(); foreach (DataRow dr in tmpTbl2.Rows) { userDataObj.sysUserGroupIds += dr["sysUserGroupId"].ToString() + ","; } if (userDataObj.sysUserGroupIds.Length > 0) { userDataObj.sysUserGroupIds = userDataObj.sysUserGroupIds.Substring(0, userDataObj.sysUserGroupIds.Length - 1); } string userData = JsonConvert.SerializeObject(userDataObj); #endregion #region save user 登入的 useLog //todo: 取得 Client login IP //string loginIp = null; //取得loginIP errStr = ZhClass.Log.LogV1.A_SaveUserLog2(userDataObj.actSerial, userDataObj.sysUserId, ZhClass.Log.LogActType.LogIn, null, Request.UserHostAddress); if (errStr != "") { ClsUtilObj.WriteErrorLog(System.Reflection.MethodInfo.GetCurrentMethod().ToString() + " @" + errStr); //ModelState.AddModelError("", "登入嘗試失試。"); //return View(model); throw new Exception("登入嘗試失試。"); } #endregion ////Session["userInfo"] = userData; FormsAuthentication.SetAuthCookie(model.userId, false); FormsAuthenticationTicket ticket = new FormsAuthenticationTicket(1, userDataObj.sysUserId.ToString(), DateTime.Now, DateTime.Now.AddMinutes(30), true, JsonConvert.SerializeObject(userDataObj), FormsAuthentication.FormsCookieName); string encTicket = FormsAuthentication.Encrypt(ticket); var cookie = new HttpCookie(FormsAuthentication.FormsCookieName, encTicket); cookie.HttpOnly = true; Response.Cookies.Add(cookie); } else { throw new Exception("登入嘗試失試。"); //ModelState.AddModelError("", "。"); //if (selItem_userType == null) //{ // selItem_userType = new List<SelectListItem>(); // selItem_userType.Add(new SelectListItem() { Text = "一般帳戶", Value = "user", Selected = true }); // selItem_userType.Add(new SelectListItem() { Text = "管理員", Value = "admin" }); //} //ViewBag.selItem_userType = selItem_userType; ////ViewBag.selItem_sysCorpId = ZhWebClass.CmnObj.Get_tbl_corps_selItem(ZhConfig.IsAddIndexZero.Yes); //return View(model); } jo.Add("status", "OK"); } catch (Exception ex) { jo.Add("status", "error"); jo.Add("error", ex.Message); } return(Content(JsonConvert.SerializeObject(jo), "application/json")); }