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_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 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 JsonResult Register(UsersModel newUser)
{
DataTable dt = new DataTableGenerator().GetDataTable(@"Select * from Users where email = '" + newUser.email.ToString() + "'");
if (dt.Rows.Count == 1)
{
return(Json(new UsersModel(), JsonRequestBehavior.AllowGet));
}
else
{
UsersModel model = new UsersModel();
SqlTool sql = new SqlTool();
model.fname = newUser.fname;
model.lname = newUser.lname;
model.username = newUser.username;
model.password = newUser.password;
model.email = newUser.email;
sql.runQuery("Insert into Users (fname,lname,email,password,username) select '" + model.fname.ToString() + "','" +
model.lname.ToString() + "','" + model.email.ToString() + "','" + model.password.ToString() + "','" +
model.username + "'");
return(Json(model, JsonRequestBehavior.AllowGet));
}
}
public static string SaveUserLog(string strConnection, int actSerial, int sysUserId, LogActType actType, string funcId, object actIp, string userId)
{
SqlParameter[] param =
{
new SqlParameter("actSerial", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, actSerial),
new SqlParameter("sysUserId", SqlDbType.Char, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, sysUserId),
new SqlParameter("actType", SqlDbType.TinyInt, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, actType),
new SqlParameter("funcId", SqlDbType.Char, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, (funcId == null || funcId.ToString().Trim() == "" ? "": (object)funcId)),
new SqlParameter("actIp", SqlDbType.VarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, (actIp == null || actIp.ToString().Trim() == "" ? "": actIp)),
new SqlParameter("userId", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, userId),
};
string strSql = "insert into S90_userLog (actSerial,sysUserId,actType,funcId,actIp,userId,actTime) values (@actSerial,@sysUserId,@actType,@funcId,@actIp,@userId,getdate())";
string errStr = SqlTool.ExecuteNonQuery(strConnection, strSql.ToString(), param);
if (errStr != "")
{
return(errStr);
}
else
{
return("");
}
}
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);
}
protected void ButtonSubmit_Click(object sender, EventArgs e)
{
int id = 0;
if (!int.TryParse(this.TextBoxID.Text, out id))
{
this.LabelOutput.Text = "invaild ID";
return;
}
if (id < 5000)
{
this.LabelOutput.Text = "Participant ID has to be a team ID.";
return;
}
int marbles_num = 0;
if (this.DropDownListSurvival.SelectedValue == "Yes")
{
int.TryParse(this.TextBoxMarbleNum.Text, out marbles_num);
}
string[] info = this.getTeamInfoById(this.TextBoxID.Text);
//insert record
try
{
SqlConnection mySqlConnection = new SqlConnection(SqlTool.GetConnectionString());
string sqlQuery = "INSERT INTO floatable_moatable_report (participant_id, student_names, school, " +
"grade_group, marbles_num, people_num) VALUES " +
"('" + this.TextBoxID.Text + "','" + info[0] + "','" + info[1] + "','" + info[3]
+ "','" + marbles_num + "','" + info[2] + "');";
SqlCommand Command = new SqlCommand(sqlQuery, mySqlConnection);
SqlDataReader mySqlDataReader;
mySqlConnection.Open();
mySqlDataReader = Command.ExecuteReader();
while (mySqlDataReader.Read())
{
}
mySqlDataReader.Dispose();
mySqlConnection.Close();
this.LabelOutput.Text = "ID: " + this.TextBoxID.Text +
", Your record was inserted.";
}
catch (Exception ex)
{
this.LabelDebug.Text = "Inserting Record Error: " + ex.Message;
}
}
/// <summary>
/// 重新加载数据
/// </summary>
protected override void reset()
{
Dictionary <randomKey <keyType>, valueType> dictionary = dictionary <randomKey <keyType> > .Create <valueType>();
foreach (valueType value in SqlTool.Where(null, memberMap))
{
setMemberCacheAndValue(value);
dictionary[GetKey(value)] = value;
}
this.dictionary.SetVersion(dictionary);
}
protected void ButtonSubmit_Click(object sender, EventArgs e)
{
string[] info;
int id = 0;
if (!int.TryParse(this.TextBoxID.Text, out id))
{
this.LabelOutput.Text = "invaild ID";
return;
}
if (id >= 5000)
{
this.LabelOutput.Text = "Participant ID has to be a student ID.";
return;
}
info = this.getStudentInfoById(this.TextBoxID.Text);
try
{
SqlConnection SqlConnection = new SqlConnection(SqlTool.GetConnectionString());
string sqlQuery = "INSERT INTO paper_airplanes_report (participant_id, student_names, school," +
"grade_group, Plane_num, dist1_ft, dist1_in, offset1_ft, offset1_in, score1, " +
"dist2_ft, dist2_in, offset2_ft, offset2_in, score2, max_dist) VALUES " +
"('" + this.TextBoxID.Text + "','" + info[0] + "','" + info[1] + "','" + info[3]
+ "','" + this.TextBoxPlaneNum.Text + "','" + this.TextBoxDist_ft.Text + "','" + this.TextBoxDist_in.Text +
"','" + this.TextBoxOffset_ft.Text + "','" + this.TextBoxOffset_in.Text + "','" + this.TextBoxScore.Text + "','"
+ this.TextBoxDist2_ft.Text + "','" + this.TextBoxDist2_in.Text + "','" +
this.TextBoxOffset2_ft.Text + "','" + this.TextBoxOffset2_in.Text + "','" + this.TextBoxScore2.Text + "','"
+ this.TextBoxMaxDist.Text + "');";
SqlCommand Command = new SqlCommand(sqlQuery, SqlConnection);
SqlDataReader SqlDataReader;
SqlConnection.Open();
SqlDataReader = Command.ExecuteReader();
while (SqlDataReader.Read())
{
}
SqlDataReader.Dispose();
SqlConnection.Close();
this.LabelOutput.Text = "ID: " + this.TextBoxID.Text +
", Your record was inserted. Your max distance is " + this.TextBoxMaxDist.Text;
}
catch (Exception ex)
{
this.LabelOutput.Text = "Inserting Record Error: " + ex.Message;
}
}
public JsonResult UpdateScore(ScoresModel scoreToUpdate)
{
SqlTool sql = new SqlTool();
HandicapCalculator handicap = new HandicapCalculator();
decimal adjustedScore = handicap.AdjustScore(scoreToUpdate.score, scoreToUpdate.courseid);
string updateScoreQuery = @"Update scores set Score = " + scoreToUpdate.score + ",adjustedscore = " + adjustedScore + " where id = " + scoreToUpdate.id;
sql.runQuery(updateScoreQuery);
return(Json(null, JsonRequestBehavior.AllowGet));
}
public JsonResult DeleteScore(ScoresModel scoreToDelete)
{
SqlTool sqltool = new SqlTool();
int scoreid = scoreToDelete.id;
string deleteScoreQuery = @"Delete from Scores where id = " + scoreid;
sqltool.runQuery(deleteScoreQuery);
return(Json(null, JsonRequestBehavior.AllowGet));
}
public List <SoftProjectAreaEntity> SelectCells(CommandType CommandType, string TSql, SqlParameter[] paras)
{
List <SoftProjectAreaEntity> CellTs = new List <SoftProjectAreaEntity>();
using (SqlDataReader sdr = SqlHelper.ExecuteReader(ProviderHelper.ConnectionString,
CommandType, TSql, paras))
{
while (sdr.Read())
{
SoftProjectAreaEntity cellT = SqlTool.Read <SoftProjectAreaEntity>(new SafeDataReader(sdr));
CellTs.Add(cellT);
}
}
return(CellTs);
}
/// <summary>
/// 获取数据集合
/// </summary>
/// <param name="key">关键字</param>
/// <returns>数据集合</returns>
protected list <valueType> getList(keyType key)
{
list <valueType> list = queueCache.Get(key, null);
if (list == null)
{
list = SqlTool.Where(getWhere(key), memberMap).getList() ?? new list <valueType>();
queueCache[key] = list;
if (queueCache.Count > maxCount)
{
queueCache.Pop();
}
}
return(list);
}
public JsonResult AddScore(ScoresModel newScore)
{
SqlTool sqltool = new SqlTool();
HandicapCalculator handicap = new HandicapCalculator();
//populate Course Model
decimal adjustedScore = handicap.AdjustScore(newScore.score, newScore.courseid);
string newScoreInsert = String.Format(@"INSERT INTO Scores(userid,courseid,score,dateplayed,adjustedscore) SELECT {0},{1},{2},'{3}',{4}",
Session["id"], newScore.courseid, newScore.score, newScore.dateplayed.ToShortDateString(), adjustedScore);
sqltool.runQuery(newScoreInsert);
return(Json(newScore, JsonRequestBehavior.AllowGet));
}
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);
}
/// <summary>
/// 获取数据集合
/// </summary>
/// <param name="key">关键字</param>
/// <returns>数据集合</returns>
protected listOrderLadyValue <valueType> getCache(keyType key)
{
listOrderLadyValue <valueType> cache = queueCache.Get(key, default(listOrderLadyValue <valueType>));
if (cache.List == null)
{
cache.List = SqlTool.Where(getWhere(key), memberMap).getList() ?? new list <valueType>();
queueCache[key] = cache;
if (queueCache.Count > maxCount)
{
queueCache.Pop();
}
}
return(cache);
}
/// <summary>
/// 重新加载数据
/// </summary>
protected override void reset()
{
valueType[] values = SqlTool.Where(null, memberMap).getArray();
int maxIdentity = values.maxKey(value => GetKey(value), 0);
if (memberGroup == 0)
{
SqlTool.Identity64 = maxIdentity + baseIdentity;
}
int length = maxIdentity >= identityArray.ArraySize ? 1 << ((uint)maxIdentity).bits() : identityArray.ArraySize;
identityArray <valueType> newValues = new identityArray <valueType>(length);
pointer.size newCounts = unmanaged.Get(length * sizeof(int), true);
try
{
int *intCounts = newCounts.Int;
foreach (valueType value in values)
{
setMemberCacheAndValue(value);
int identity = GetKey(value);
newValues[identity] = value;
intCounts[identity] = 1;
}
for (int step = 2; step != length; step <<= 1)
{
for (int index = step, countStep = step >> 1; index != length; index += step)
{
intCounts[index] += intCounts[index - countStep];
}
}
unmanaged.Free(ref counts);
this.values = newValues;
counts = newCounts;
size = length;
Count = values.Length;
newCounts.Null();
}
catch (Exception error)
{
log.Error.Add(error, null, true);
}
finally
{
unmanaged.Free(ref newCounts);
}
}
/// <summary>
/// 重新加载数据
/// </summary>
private void reset()
{
valueType[] values = SqlTool.Where(null, memberMap).getArray();
int maxIdentity = values.maxKey(value => getIdentity(value), 0);
int length = maxIdentity >= 1024 ? 1 << ((uint)maxIdentity).bits() : 1024;
valueType[] newValues = new valueType[length];
int * newCounts = unmanaged.Get(length * sizeof(int)).Int;
bool isFree = false;
try
{
foreach (valueType value in values)
{
int identity = getIdentity(value);
newValues[identity] = value;
newCounts[identity] = 1;
}
for (int step = 2; step != length; step <<= 1)
{
for (int index = step, countStep = step >> 1; index != length; index += step)
{
newCounts[index] += newCounts[index - countStep];
}
}
unmanaged.Free(counts);
isFree = true;
this.values = newValues;
counts = newCounts;
size = length;
Count = values.Length;
}
catch (Exception error)
{
log.Default.Add(error, null, true);
}
finally
{
if (!isFree)
{
unmanaged.Free(newCounts);
}
}
}
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);
}
/// <summary>
///
/// </summary>
/// <param name="rowStatus"></param>
/// <param name="tblName"></param>
/// <param name="columnName"></param>
/// <param name="columnLabelName"></param>
/// <param name="columnValue"></param>
/// <param name="pkeysConds">"and sysProductClassId !='" + actRow.sysProductClassId.ToString() + "'"</param>
/// <returns></returns>
public static string Chk_ColumnValue(string rowStatus, string tblName, string columnName, string columnLabelName, string columnValue, string pkeysConds)
{
int count;
if (rowStatus == "A")
{
count = Convert.ToInt32(SqlTool.GetOneDataValue("select count(*) from " + tblName + " where statusx='10' and " + columnName + "='" + columnValue + "'"));
if (count > 0)
{
return(string.Format("啟用中之{0}:【{1}】,<br>不可重複 ", columnLabelName, columnValue));
}
}
else
{
count = Convert.ToInt32(SqlTool.GetOneDataValue("select count(*) from " + tblName + " where statusx='10' and " + columnName + "='" + columnValue + "' " + pkeysConds));
if (count > 0)
{
return(string.Format("啟用中之{0}:【{1}】,<br>不可重複 ", columnLabelName, columnValue));
}
}
return("");
}
/// <summary>
/// 重新加载数据
/// </summary>
protected override void reset()
{
valueType[] values = SqlTool.Where(null, memberMap).getFindArray(isValue);
Dictionary <int, valueType> newValues = fastCSharp.dictionary.CreateInt <valueType>(values.Length);
int maxIdentity = 0;
foreach (valueType value in values)
{
setMemberCacheAndValue(value);
int identity = GetKey(value);
if (identity > maxIdentity)
{
maxIdentity = identity;
}
newValues.Add(identity, value);
}
if (memberGroup == 0)
{
SqlTool.Identity64 = maxIdentity + baseIdentity;
}
this.dictionary.Set(newValues);
Count = values.Length;
++this.dictionary.Version;
}