//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"));
}