public static string Chk_ColumnValue(SqlConnection cn, string rowStatus, string tblName, string columnName, string columnLabelName, string columnValue, string pkeysConds)
{
int count;
if (rowStatus == "A")
{
count = Convert.ToInt32(SqlTool2.GetOneDataValue(cn, "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(SqlTool2.GetOneDataValue(cn, "select count(*) from " + tblName + " where statusx='10' and " + columnName + "='" + columnValue + "' " + pkeysConds));
if (count > 0)
{
return(string.Format("啟用中之{0}:【{1}】,<br>不可重複 ", columnLabelName, columnValue));
}
}
return("");
}
public ActionResult ActSingle(Models.ModelBase.Row_users actRow)
{
ZhWebClassV3.UserData userData = ZhWebClassV3.UserHelper.GetUserData();
JObject jo;
#region 判斷啟用中 值,不可重複
if (actRow.statusx.ToString() == "A")
{
//errStr += ZhWebClassV3.CheckObj.Chk_ColumnValue(actRow.RowStatus.ToString(), "S10_users", "userName", "使用者姓名", actRow.userName.ToString(), "");
errStr += ZhWebClassV3.CheckObj.Chk_ColumnValue(actRow.RowStatus.ToString(), "S10_users", "userId", "使用者代號", actRow.userId.ToString(), "");
}
else
{
//errStr += ZhWebClassV3.CheckObj.Chk_ColumnValue(actRow.RowStatus.ToString(), "S10_users", "userName", "使用者姓名", actRow.userName.ToString(), " and sysUserId<>'" + actRow.sysUserId.ToString() + "' ");
errStr += ZhWebClassV3.CheckObj.Chk_ColumnValue(actRow.RowStatus.ToString(), "S10_users", "userId", "使用者代號", actRow.userId.ToString(), " and sysUserId<>'" + actRow.sysUserId.ToString() + "' ");
}
if (errStr != "")
{
jo = new JObject();
jo.Add("status", "error");
jo.Add("error", errStr);
return(Content(JsonConvert.SerializeObject(jo), "application/json"));
}
#endregion
try
{
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection cn = new SqlConnection(ZhConfig.GlobalSystemVar.StrConnection1))
{
cn.Open();
#region act users
try
{
#region ACall_checkIsDBNull
actRow.sysUserId = ZhConfig.ZhIniObj.ACall_checkIsDBNull(actRow.sysUserId);
actRow.userName = ZhConfig.ZhIniObj.ACall_checkIsDBNull(actRow.userName);
actRow.userId = ZhConfig.ZhIniObj.ACall_checkIsDBNull(actRow.userId);
actRow.userPassword = ZhConfig.ZhIniObj.ACall_checkIsDBNull(actRow.userPassword);
actRow.userEmail = ZhConfig.ZhIniObj.ACall_checkIsDBNull(actRow.userEmail);
actRow.userPhone = ZhConfig.ZhIniObj.ACall_checkIsDBNull(actRow.userPhone);
actRow.userTel = ZhConfig.ZhIniObj.ACall_checkIsDBNull(actRow.userTel);
actRow.statusx = ZhConfig.ZhIniObj.ACall_checkIsDBNull(actRow.statusx);
actRow.memo = ZhConfig.ZhIniObj.ACall_checkIsDBNull(actRow.memo);
actRow.creatUser = ZhConfig.ZhIniObj.ACall_checkIsDBNull(actRow.creatUser);
actRow.actUser = ZhConfig.ZhIniObj.ACall_checkIsDBNull(actRow.actUser);
#endregion
#region 新增或修改 資料到後端資料庫
#region 設置 要傳入的 SqlParameter 資料
SqlParameter[] param =
{
new SqlParameter("sysUserId", SqlDbType.Int, 4, ParameterDirection.InputOutput, false, 0, 0, "", DataRowVersion.Proposed, actRow.sysUserId),
new SqlParameter("userName", SqlDbType.NVarChar, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, actRow.userName),
new SqlParameter("userId", SqlDbType.VarChar, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, actRow.userId),
new SqlParameter("userPassword", SqlDbType.VarChar, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, actRow.userPassword),
new SqlParameter("userEmail", SqlDbType.VarChar, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, actRow.userEmail),
new SqlParameter("userPhone", SqlDbType.VarChar, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, actRow.userPhone),
new SqlParameter("userTel", SqlDbType.VarChar, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, actRow.userTel),
new SqlParameter("statusx", SqlDbType.VarChar, 255, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, actRow.statusx),
new SqlParameter("memo", SqlDbType.NVarChar, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, actRow.memo),
new SqlParameter("creatUser", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, userData.sysUserId), //actRow.creatUser
new SqlParameter("actUser", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, userData.sysUserId), //actRow.actUser
new SqlParameter("pk_sysUserId", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "sysUserId", DataRowVersion.Original, actRow.sysUserId)
};
#endregion
switch (actRow.RowStatus.ToString())
{
case "A": //sysUserId,@sysUserId,,actUser,actTime ,@actUser,getdate()
strSql.Append("insert into S10_users (userName,userId,userPassword,userEmail,userPhone,userTel,statusx,memo,creatUser,creatTime) values (@userName,@userId,@userPassword,@userEmail,@userPhone,@userTel,@statusx,@memo,@creatUser,getdate())");
strSql.Append("SELECT @sysUserId = SCOPE_IDENTITY()");
actRow.creatUser = userData.userName;
actRow.creatTime = DateTime.Now;
errStr = SqlTool2.ExecuteNonQuery(cn, strSql.ToString(), param);
actRow.sysUserId = param[0].Value;
break;
case "M": //sysUserId=@sysUserId,
strSql.Append("update S10_users set userName=@userName,userId=@userId,userPassword=@userPassword,userEmail=@userEmail,userPhone=@userPhone,userTel=@userTel,statusx=@statusx,memo=@memo,actUser=@actUser,actTime=getdate() where sysUserId=@pk_sysUserId");
actRow.actUser = userData.userName;
actRow.actTime = DateTime.Now;
errStr = SqlTool2.ExecuteNonQuery(cn, strSql.ToString(), param);
if (errStr != "")
{
throw new Exception(errStr);
}
break;
}
#region OperLog 異動使用者資料
if (Convert.ToBoolean(ZhConfig.GlobalSystemVar.tbl_OperLogFlag.Rows.Find(funcId)["isOperLogEnable"]) && errStr == "")
{
#region Gen tbl_operLog1 Data (Add/Modify)
DataTable tbl_operLog1 = userData.Get_tbl_operLogPart1();
tbl_operLog1.Columns.Add("sysUserId1", typeof(string));
DataRow operLogRow = tbl_operLog1.NewRow();
//operLogRow["rowId"] =0;
operLogRow["actSerial"] = userData.actSerial;
operLogRow["sysUserId"] = userData.sysUserId;
operLogRow["actStatus"] = actRow.RowStatus.ToString();
operLogRow["sysUserId1"] = actRow.sysUserId.ToString();
tbl_operLog1.Rows.Add(operLogRow);
#endregion
errStr = ZhWebClassSet.Log.LogSet.SaveOperLog(funcId, "S10_users", tbl_operLog1);
if (errStr != "")
{
throw new Exception(errStr);
}
}
#endregion
if (errStr != "")
{
jo = new JObject();
jo.Add("status", "error");
jo.Add("error", errStr);
return(Content(JsonConvert.SerializeObject(jo), "application/json"));
}
#endregion
}
catch (Exception ex)
{
jo = new JObject();
jo.Add("status", "error");
jo.Add("error", ex.Message);
return(Content(JsonConvert.SerializeObject(jo), "application/json"));
}
#endregion
#region act userVsGroup
if (actRow.RowStatus.ToString() == "M")
{//若是修改就先刪除再整個新增
strSql.Remove(0, strSql.Length);
strSql.Append("delete S10_userVsGroup where sysUserId='" + actRow.sysUserId + "'");
errStr = SqlTool2.ExecuteNonQuery(cn, strSql.ToString());
if (errStr != "")
{
jo = new JObject();
jo.Add("status", "error");
jo.Add("error", errStr);
return(Content(JsonConvert.SerializeObject(jo), "application/json"));
}
}
if (actRow.userGroups != null)
{
string[] sysUserGroupId = actRow.userGroups.ToString().Split(',');
foreach (string gId in sysUserGroupId)
{
if (gId == "")
{
continue;
}
strSql.Remove(0, strSql.Length);
strSql.Append("insert into S10_userVsGroup (sysUserId,sysUserGroupId,statusx) values ('" + actRow.sysUserId.ToString() + "','" + gId + "','10') ");
errStr = SqlTool2.ExecuteNonQuery(cn, strSql.ToString());
if (errStr != "")
{
jo = new JObject();
jo.Add("status", "error");
jo.Add("error", errStr);
return(Content(JsonConvert.SerializeObject(jo), "application/json"));
}
}
}
#endregion
}
scope.Complete();
}
#region return Info
if (errStr == "")
{
JArray ja = new JArray();
var itemObject = new JObject();
itemObject.Add("sysUserId", actRow.sysUserId.ToString());
if (actRow.RowStatus.ToString() == "M")
{
itemObject.Add("actUser", actRow.actUser.ToString());
itemObject.Add("actTime", actRow.actTime.ToString());
}
else //A
{
itemObject.Add("creatUser", actRow.creatUser.ToString());
itemObject.Add("creatTime", actRow.creatTime.ToString());
}
ja.Add(itemObject);
jo = new JObject();
jo.Add("status", "OK");
jo.Add("row", ja);
}
else
{
jo = new JObject();
jo.Add("status", "error");
jo.Add("error", errStr);
}
return(Content(JsonConvert.SerializeObject(jo), "application/json"));
#endregion
}
catch (Exception ex)
{
jo = new JObject();
jo.Add("status", "error");
jo.Add("error", ex.Message);
return(Content(JsonConvert.SerializeObject(jo), "application/json"));
}
}
public static string SaveOperLog(SqlConnection cn, string funcId, string tableName, DataTable OperLogTable)
{
//object oriRowId;
object actStatus;
object actSerial;
object sysUserId;
object rowId = null;
DataRow[] delRow;
string strSql = "insert into S90_operLog (actSerial,sysUserId, menuId,actStatus,tableName,tblPrimaryKeysAndValues) values (@actSerial,@sysUserId, @menuId,@actStatus,@tableName,@tblPrimaryKeysAndValues);SELECT @rowId = SCOPE_IDENTITY()";
for (int i = 0; i < OperLogTable.Rows.Count; i++)
{
#region KeyAndValues
string strPKeysAndValues = "";
string pKeyName;
string pKeyValue;
//若有需要串接到operLogDeatil 會用到,目前用不到 ,且一開始rowId 若有值只是自動編來跟 operLogDeatil 串接的
//oriRowId = OperLogTable.Rows[i][0].ToString();
actStatus = OperLogTable.Rows[i]["actStatus"];
actSerial = OperLogTable.Rows[i]["actSerial"];
sysUserId = OperLogTable.Rows[i]["sysUserId"];
for (int j = 5; j < OperLogTable.Columns.Count; j++)
{
pKeyName = OperLogTable.Columns[j].ColumnName;
pKeyValue = OperLogTable.Rows[i][j].ToString().Trim();
strPKeysAndValues += pKeyName + "='" + pKeyValue + "'|";
}
strPKeysAndValues = strPKeysAndValues.Substring(0, strPKeysAndValues.Length - 1);
#endregion
SqlParameter[] param = new SqlParameter[7];
param[0] = new SqlParameter("@sysUserId", SqlDbType.Char, 10);
param[0].Value = sysUserId;
param[1] = new SqlParameter("@funcId", SqlDbType.Char, 8);
param[1].Value = funcId;
param[2] = new SqlParameter("@actStatus", SqlDbType.Char, 1);
param[2].Value = actStatus;
param[3] = new SqlParameter("@tableName", SqlDbType.VarChar, 255);
param[3].Value = tableName;
param[4] = new SqlParameter("@tblPrimaryKeysAndValues", SqlDbType.NVarChar, 255);
param[4].Value = strPKeysAndValues;
param[5] = new SqlParameter("@rowId", SqlDbType.Int, 4, ParameterDirection.InputOutput, false, 0, 0, "", DataRowVersion.Proposed, rowId);
param[5].Value = rowId;
param[6] = new SqlParameter("@actSerial", SqlDbType.Int, 4);
param[6].Value = actSerial;
string errStr = SqlTool2.ExecuteNonQuery(cn, strSql, param);
if (errStr != "")
{
delRow = OperLogTable.Select("actStatus='*'");
foreach (DataRow dr in delRow)
{
dr.Delete();
}
return(errStr);
}
OperLogTable.Rows[i]["actStatus"] = "*";
rowId = param[5].Value;
//有需要時 寫入 異動前後的值到operLogDetail
}
delRow = OperLogTable.Select("actStatus='*'");
foreach (DataRow dr in delRow)
{
dr.Delete();
}
OperLogTable.AcceptChanges();
return("");
}
