public static List <SelectListItem> Get_tbl_objectId_selItem(ZhConfig.IsAddIndexZero isAddIndexZero, string creatUser)
{
List <SelectListItem> selItem = new List <SelectListItem>();
DataTable tmpTable = Get_tbl_objectId(isAddIndexZero, creatUser);
for (int i = 0; i < tmpTable.Rows.Count; i++)
{
if (i == 0)
{
selItem.Add(new SelectListItem()
{
Value = tmpTable.Rows[i]["sysObjectId"].ToString(), Text = tmpTable.Rows[i]["objectName"].ToString(), Selected = true
});
}
else
{
selItem.Add(new SelectListItem()
{
Value = tmpTable.Rows[i]["sysObjectId"].ToString(), Text = tmpTable.Rows[i]["objectName"].ToString()
});
}
}
return(selItem);
}
public static List <SelectListItem> Get_tbl_users_selItem(ZhConfig.IsAddIndexZero isAddIndexZero, string statusx = "10")
{
List <SelectListItem> selItem = new List <SelectListItem>();
DataTable tmpTable = Get_tbl_users(isAddIndexZero, statusx);
for (int i = 0; i < tmpTable.Rows.Count; i++)
{
if (i == 0)
{
selItem.Add(new SelectListItem()
{
Value = tmpTable.Rows[i]["sysUserId"].ToString(), Text = tmpTable.Rows[i]["userName2"].ToString(), Selected = true
});
}
else
{
selItem.Add(new SelectListItem()
{
Value = tmpTable.Rows[i]["sysUserId"].ToString(), Text = tmpTable.Rows[i]["userName2"].ToString()
});
}
}
return(selItem);
}
//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_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_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 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 string Get_tbl_limit_json(ZhConfig.IsAddIndexZero isAddIndexZero)
{
DataTable tmpTable = Get_tbl_limit(isAddIndexZero);
JArray ja = new JArray();
foreach (DataRow dr in tmpTable.Rows)
{
JObject itemObject = new JObject
{
{ "limitId", dr["limitId"].ToString().Trim() },
{ "limitName", dr["limitName"].ToString().Trim() }
};
ja.Add(itemObject);
}
return(JsonConvert.SerializeObject(ja));
}
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);
}
public static List <SelectListItem> Get_tbl_year_selItem(ZhConfig.IsAddIndexZero isAddIndexZero)
{
List <SelectListItem> selItem = new List <SelectListItem>();
string strSql = "select year, year + '年' as year1 from u_Q00030 group by year ";
DataTable tmpTable = ZhClass.SqlTool.GetDataTable(ZhConfig.GlobalSystemVar.StrConnection1, strSql.ToString(), "tbl_QueryData1");
if (isAddIndexZero == ZhConfig.IsAddIndexZero.Yes)
{
DataRow tmpRow = tmpTable.NewRow();
tmpRow["year"] = "";
//selItem.Add(new SelectListItem() { Value = "", Text = "請選擇", Selected = true });
ZhConfig.ZhIniObj.addZeroRowColumnInfo(tmpRow, "year1");
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]["year"].ToString(), Text = tmpTable.Rows[i]["year1"].ToString(), Selected = true
});
}
else
{
selItem.Add(new SelectListItem()
{
Value = tmpTable.Rows[i]["year"].ToString(), Text = tmpTable.Rows[i]["year1"].ToString()
});
}
}
return(selItem);
}
/// <summary>
///
/// </summary>
/// <param name="isAddIndexZero"></param>
/// <param name="parentId"></param>
/// <param name="statusx"></param>
/// <param name="actSubType">0:全部,1:公司動作類型 2:客戶動作類型</param>
/// <returns></returns>
public static List <SelectListItem> Get_tbl_sysParameters_selItem(ZhConfig.IsAddIndexZero isAddIndexZero, string parentId, string statusx = "10", string actSubType = "0")
{
List <SelectListItem> selItem = new List <SelectListItem>();
DataTable tmpTable = Get_tbl_sysParameters(isAddIndexZero, parentId);
for (int i = 0; i < tmpTable.Rows.Count; i++)
{
string paraId = tmpTable.Rows[i]["paraId"].ToString();
switch (actSubType)
{
//case "0":
// break;
case "1":
if (paraId == "10" || paraId == "20")
{
continue;
}
break;
case "2":
if (paraId == "30" || paraId == "40" || paraId == "50" || paraId == "60" || paraId == "70")
{
continue;
}
break;
}
selItem.Add(new SelectListItem()
{
Value = paraId, Text = tmpTable.Rows[i]["paraName"].ToString()
});
}
selItem[0].Selected = true;
return(selItem);
}
public static string Get_tbl_limit_json2(ZhConfig.IsAddIndexZero isAddIndexZero)
{
return(JsonConvert.SerializeObject(Get_tbl_limit_selItem(isAddIndexZero), Formatting.Indented));
}
public static string Get_tbl_sysParameters_json(ZhConfig.IsAddIndexZero isAddIndexZero, string parentId, string statusx = "10")
{
return(JsonConvert.SerializeObject(Get_tbl_sysParameters(isAddIndexZero, parentId, statusx), Formatting.Indented));
}
