/// <summary>刪除群組</summary>
public static void RemoveGroup(string GroupID)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn, true))
{
try
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("GroupID", GroupID);
db.SqlParams = param;
//刪除舊有群組選單權限與使用者清單
db.StrSQL += @"DELETE FROM [Auth] WHERE GroupID = @GroupID;
DELETE FROM [GroupUser] WHERE GroupID = @GroupID;
DELETE FROM [Group] WHERE GroupID = @GroupID;";
db.ExecuteSQL();
//確認執行交易
db.Commit();
}
catch (Exception e)
{
//回復交易
db.Rollback();
throw e;
}
}
}
/// <summary>取得條件式最後的選單編號</summary>
public static string GetMaxMenuNo(string ParentMenuNo, int?MenuNoLen)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("ParentMenuNo", ParentMenuNo + "%");
param.Add("MenuNoLen", MenuNoLen);
db.SqlParams = param;
//條件式
string strWhere = "";
if (!string.IsNullOrEmpty(ParentMenuNo))
{
strWhere += " AND MenuNo LIKE @ParentMenuNo ";
}
if (MenuNoLen != null)
{
strWhere += " AND LEN(MenuNo) = @MenuNoLen ";
}
//取回資料
db.StrSQL = "SELECT MAX(MenuNo) FROM Menu WHERE 1=1" + strWhere;
//編號轉換，若回傳空值則從00編號開始
string strMaxMenuNo = db.ExecuteScalar();
if (string.IsNullOrEmpty(strMaxMenuNo))
{
strMaxMenuNo = ParentMenuNo + "00";
}
return(strMaxMenuNo);
}
}
/// <summary>取得所有選單檔</summary>
public static DataTable GetAllMenuList(bool?Enabled)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("Enabled", Enabled);
db.SqlParams = param;
//條件式
string strWHERE = "";
if (Enabled != null)
{
strWHERE += " AND Enabled = @Enabled ";
}
//取回資料
db.StrSQL = "SELECT * FROM Menu WHERE 1=1" + strWHERE + "ORDER BY OrderID";
var dt = db.ExecuteDataTable();
var dtNew = dt.Clone();
//依選單順序排列
foreach (var r in dt.Select("LEN(MenuNo) = 2", "OrderID"))
{
dtNew.Rows.Add(r.ItemArray);
foreach (var r1 in dt.Select("LEN(MenuNo) = 4 AND MenuNo LIKE '" + r["MenuNo"].ToString() + "%'"))
{
dtNew.Rows.Add(r1.ItemArray);
}
}
return(dtNew);
}
}
/// <summary>刪除指定的選單編號</summary>
public static void DelMenu(string MenuNo)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("MenuNo", MenuNo + "%");
db.SqlParams = param;
//刪除相關資料表
db.StrSQL =
@"DECLARE @Count int;
SET @Count = (SELECT COUNT(*) FROM [Menu] WHERE MenuNo LIKE @MenuNo);
IF (@Count = 1) BEGIN
DELETE FROM [Menu] WHERE MenuNo LIKE @MenuNo;
DELETE FROM [Auth] WHERE MenuNo LIKE @MenuNo;
END ELSE BEGIN
THROW 51000, '無法刪除子選單!', 1;
END";
db.ExecuteSQL();
}
}
/// <summary>取得群組資料</summary>
public static DataTable GetGroupList(string GroupID, string GroupName, Pages _page)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("GroupID", GroupID);
param.Add("GroupName", "%" + GroupName + "%");
db.SqlParams = param;
//條件式
string strWHERE = "";
if (!string.IsNullOrEmpty(GroupID))
{
strWHERE += " AND GroupID = @GroupID ";
}
if (!string.IsNullOrEmpty(GroupName))
{
strWHERE += " AND GroupName LIKE @GroupName ";
}
//取回資料
int iTotal;
var sql = "SELECT * FROM [Group] WHERE 1=1" + strWHERE;
var dt = db.ExecuteDataTable(sql, "GroupID", _page.PageIndex, _page.PageSize, out iTotal);
_page.TotalRows = iTotal;
return(dt);
}
}
/// <summary>更新使用者登入時間</summary>
public static void SetUserLoginDate(LoginViewModel m)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn, true))
{
try
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("Account", m.Account);
param.Add("IP", m.IP);
param.Add("LastLoginDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
db.SqlParams = param;
//更新使用者最後登入時間，記錄使用者登錄Log
db.StrSQL = @"UPDATE [User] SET LastLoginDate = @LastLoginDate WHERE Account = @Account;
INSERT [UserLoginLog](Account, IP, LoginDate) VALUES(@Account, @IP, @LastLoginDate);";
db.ExecuteSQL();
//確認執行交易
db.Commit();
}
catch (Exception e)
{
//回復交易
db.Rollback();
throw e;
}
}
}
/// <summary>修改使用者密碼</summary>
public static bool EditPassword(PasswordEditViewModel m)
{
//找出此使用者資訊
DataTable dt = UserDataAccess.GetUserList(m.ID, null, null, null, null, new Pages());
if (dt.Rows.Count > 0)
{
//驗証
string strOldPwd = dt.Rows[0]["Password"].ToString();
string strOldPwd1 = Util.MD5(m.Password, 32);
if (strOldPwd1 != strOldPwd)
{
throw new Exception("原密碼不相符");
}
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("ID", m.ID);
param.Add("NewPassword", Util.MD5(m.NewPassword, 32));
db.SqlParams = param;
//異動資料
db.StrSQL = "UPDATE [User] SET Password = @NewPassword WHERE ID = @ID";
int iResult = db.ExecuteSQL();
return(iResult > 0);
}
}
else
{
throw new Exception("無此使用者相關資訊");
}
}
/// <summary>儲存DockDoor InnoapStatus 資料</summary>
public static void SaveDockDoorInfo_InnoapStatus(string IP, string InnoapStatus)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("IP", IP);
param.Add("InnoapStatus", InnoapStatus);
db.SqlParams = param;
//修改
db.StrSQL =
@"DECLARE @Count int;
SET @Count = (SELECT COUNT(*) FROM [Innolux_DockDoor] WHERE IP = @IP);
IF (@Count = 1) BEGIN
UPDATE [Innolux_DockDoor] SET InnoapStatus=@InnoapStatus WHERE IP=@IP;
END ELSE BEGIN
THROW 51000, 'IP不存在', 1;
END";
db.ExecuteSQL();
}
}
public static DataTable GetSpecialTruckFilteredDetails(string TRUCK_NO)
{
DataTable dt = null;
string Io_no = System.Web.Configuration.WebConfigurationManager.AppSettings["IO_No"];
using (var db = new DataBase.DataBase(Definition.Conn))
{
var param = new DataBase.SqlParams();
param.Add("TRUCK_NO", TRUCK_NO);
param.Add("IO_NO", Io_no);
db.SqlParams = param;
string strWHERE = "";
if (!string.IsNullOrEmpty(Io_no))
{
strWHERE += " AND IO_NO = '" + Io_no + "' ";
}
db.StrSQL = "SELECT DISTINCT TRUCK_NO FROM WMS_RFID_TRUCK WHERE 1=1 AND (DOCKED IS NULL OR DOCKED='') AND TRUCK_NO = '" + TRUCK_NO + "'" + strWHERE;
dt = db.ExecuteDataTable();
return(dt);
}
}
/// <summary>取得使用者清單資料檔</summary>
public static DataTable GetUserLoginLog(string _account, string _ip, Pages _page)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("Account", _account);
param.Add("IP", _ip);
db.SqlParams = param;
//條件式
string strWhere = "";
if (!string.IsNullOrEmpty(_account))
{
strWhere += " AND Account = @Account ";
}
if (!string.IsNullOrEmpty(_ip))
{
strWhere += " AND IP = @IP ";
}
//取回資料
int iTotal;
var sql = "SELECT * FROM [UserLoginLog] WHERE 1=1 " + strWhere;
var dt = db.ExecuteDataTable(sql, "LoginDate DESC", _page.PageIndex, _page.PageSize, out iTotal);
_page.TotalRows = iTotal;
return(dt);
}
}
/// <summary>儲存系統參數資料</summary>
public static bool SaveSysParamsInfo(SysParamsDetailViewModel m)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("ParaCode", m.ParaCode);
param.Add("ParaValue", m.ParaValue);
param.Add("ParaDesc", m.ParaDesc);
db.SqlParams = param;
if (m.Action == "Add")
{
//驗証系統代碼是否重覆
var dtParams = GetSysParams(m.ParaCode, null, new Pages());
if (dtParams.Rows.Count > 0)
{
throw new Exception("已有重覆的系統代碼");
}
//新增系統參數
db.StrSQL = "INSERT [SysParams](ParaCode, ParaValue, ParaDesc) VALUES(@ParaCode, @ParaValue, @ParaDesc);";
}
else
{
//修改系統參數
db.StrSQL = "UPDATE [SysParams] SET ParaValue = @ParaValue, ParaDesc = @ParaDesc WHERE ParaCode = @ParaCode;";
}
int iResult = db.ExecuteSQL();
return(iResult > 0);
}
}
/// <summary>取得系統參數資料</summary>
public static DataTable GetSysParams(string ParaCode, string ParaDesc, Pages _page)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("ParaCode", ParaCode);
param.Add("ParaDesc", "%" + ParaDesc + "%");
db.SqlParams = param;
//條件式
string strWHERE = "";
if (!string.IsNullOrEmpty(ParaCode))
{
strWHERE += " AND ParaCode = @ParaCode ";
}
if (!string.IsNullOrEmpty(ParaDesc))
{
strWHERE += " AND ParaDesc LIKE @ParaDesc ";
}
//取回資料
int iTotal;
var sql = "SELECT * FROM [SysParams] WHERE 1=1" + strWHERE;
var dt = db.ExecuteDataTable(sql, "ParaCode", _page.PageIndex, _page.PageSize, out iTotal);
_page.TotalRows = iTotal;
return(dt);
}
}
/// <summary>取得TagLog資料</summary>
public static DataTable GetTagLogList(string DockDoorID, string Status, Pages _page)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//判斷Status成功或失敗
Status = (Status == "Y" ? "'Y','A'" : Status);
Status = (Status == "N" ? "'N','0'" : Status);
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("DockDoorID", DockDoorID);
db.SqlParams = param;
//條件式
string strWHERE = "";
if (!string.IsNullOrEmpty(DockDoorID))
{
strWHERE += " AND DockDoorID = @DockDoorID ";
}
if (!string.IsNullOrEmpty(Status))
{
strWHERE += " AND Status IN (" + Status + ") ";
}
//取回資料
int iTotal;
var sql = "SELECT * FROM Innolux_TagLog WHERE 1=1" + strWHERE;
var dt = db.ExecuteDataTable(sql, "ID DESC", _page.PageIndex, _page.PageSize, out iTotal);
_page.TotalRows = iTotal;
return(dt);
}
}
/// <summary>取得使用者權限檔</summary>
public static DataTable GetUserAuthority(string _account)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("Account", _account);
param.Add("Enabled", true);
db.SqlParams = param;
//取回資料
db.StrSQL = @"SELECT MenuNo,
Convert(bit, MAX(Convert(int, [Add]))) [Add],
Convert(bit, MAX(Convert(int, [Edit]))) [Edit],
Convert(bit, MAX(Convert(int, [Del]))) [Del],
Convert(bit, MAX(Convert(int, [Query]))) [Query],
Convert(bit, MAX(Convert(int, [Audit]))) [Audit],
Convert(bit, MAX(Convert(int, [Print]))) [Print],
Convert(bit, MAX(Convert(int, [Export]))) [Export],
Convert(bit, MAX(Convert(int, [Import]))) [Import],
Convert(bit, MAX(Convert(int, [Admin]))) [Admin],
Convert(bit, MAX(Convert(int, [Enabled]))) [Enabled]
FROM(
SELECT a.* FROM [Auth] a WHERE a.Account = @Account AND a.Enabled = @Enabled
UNION ALL
SELECT a.* FROM [Auth] a, [GroupUser] g WHERE a.GroupID = g.GroupID AND a.Enabled = @Enabled AND g.Account = @Account
) r
GROUP BY MenuNo";
return(db.ExecuteDataTable());
}
}
/// <summary>取得使用者的選單權限</summary>
public static DataTable GetUserMenuAuth(string Account)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("Account", Account);
db.SqlParams = param;
//取回資料
db.StrSQL = @"SELECT m.MenuNo, m.MenuName, m.MenuIco, m.OrderID, a.* FROM Menu m
LEFT OUTER JOIN Auth a ON a.MenuNo = m.MenuNo AND a.Account = @Account
ORDER BY OrderID";
var dt = db.ExecuteDataTable();
var dtNew = dt.Clone();
//依選單順序排列
foreach (var r in dt.Select("LEN(MenuNo) = 2", "OrderID"))
{
dtNew.Rows.Add(r.ItemArray);
foreach (var r1 in dt.Select("LEN(MenuNo) = 4 AND MenuNo LIKE '" + r["MenuNo"].ToString() + "%'"))
{
dtNew.Rows.Add(r1.ItemArray);
}
}
return(dtNew);
}
}
/// <summary>儲存DockDoor資料</summary>
public static void SaveDockDoorInfo(DockDoorDetailViewModel m)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("ID", m.ID);
param.Add("DockDoorID", m.DockDoorID);
param.Add("Remark", m.Remark);
param.Add("Locate", m.Locate);
param.Add("IP", m.IP);
param.Add("CaptionPanelIP", m.CaptionPanelIP);
param.Add("FilterCode", m.FilterCode);
param.Add("Mask", m.Mask);
param.Add("MaskOut", m.MaskOut);
param.Add("Alarm", "N");
param.Add("ContainerID", "");
param.Add("ContainerStatus", "");
param.Add("Account", Definition.UserInfo.Account);
db.SqlParams = param;
if (m.ID == 0)
{
//新增
db.StrSQL =
@"DECLARE @Count int;
SET @Count = (SELECT COUNT(*) FROM [Innolux_DockDoor] WHERE (DockDoorID = @DockDoorID) OR (IP = @IP));
IF (@Count = 0) BEGIN
INSERT [Innolux_DockDoor](DockDoorID, Remark, Locate, IP, CaptionPanelIP, FilterCode, Mask, MaskOut, Alarm, ContainerID, ContainerStatus, CreateTime, Creater, UpdateTime, Updater) VALUES(@DockDoorID, @Remark, @Locate, @IP, @CaptionPanelIP, @FilterCode, @Mask, @MaskOut, @Alarm, @ContainerID, @ContainerStatus, GetDate(), @Account, GetDate(), @Account);
END ELSE BEGIN
THROW 51000, '碼頭編號或IP已存在', 1;
END";
}
else
{
//修改
db.StrSQL =
@"DECLARE @Count int;
SET @Count = (SELECT COUNT(*) FROM [Innolux_DockDoor] WHERE (ID <> @ID) AND (DockDoorID = @DockDoorID OR IP = @IP));
IF (@Count = 0) BEGIN
UPDATE [Innolux_DockDoor] SET DockDoorID=@DockDoorID, Remark=@Remark, Locate=@Locate, IP=@IP, CaptionPanelIP=@CaptionPanelIP, FilterCode=@FilterCode, Mask=@Mask, MaskOut=@MaskOut, UpdateTime=GetDate(), Updater=@Account WHERE ID=@ID;
END ELSE BEGIN
THROW 51000, '碼頭編號或IP已存在', 1;
END";
}
db.ExecuteSQL();
}
}
/// <summary>取得系統參數值</summary>
public static string GetSysParamsValue(string ParaCode)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("ParaCode", ParaCode);
db.SqlParams = param;
//回傳資料
db.StrSQL = "SELECT ParaValue FROM [SysParams] WHERE ParaCode = @ParaCode";
return(db.ExecuteScalar());
}
}
/// <summary>儲存選單資料</summary>
public static bool SaveMenuInfo(MenuDetailViewModel m)
{
string action = "";
//有無選單編號，若無則視為新增
if (string.IsNullOrEmpty(m.MenuNo))
{
action = "Add";
//判斷是否為空值
m.ParentMenuNo = string.IsNullOrWhiteSpace(m.ParentMenuNo) ? "" : m.ParentMenuNo;
//設定新選單編號
string strMaxMenuNo = MenuDataAccess.GetMaxMenuNo(m.ParentMenuNo, m.ParentMenuNo.Length + 2);
m.MenuNo = Util.GetPadLeftString(strMaxMenuNo, 1);
}
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("MenuNo", m.MenuNo);
param.Add("MenuName", m.MenuName);
param.Add("MenuLink", m.MenuLink);
param.Add("Type", m.Type);
param.Add("MenuDesc", m.MenuDesc);
param.Add("OrderID", m.OrderID);
param.Add("Enabled", m.Enabled);
param.Add("MenuIco", m.MenuIco);
db.SqlParams = param;
if (action == "Add")
{
//新增資料
db.StrSQL = @"--設定排序值--
SET @OrderID = (SELECT MAX(OrderID) + 1 FROM Menu WHERE LEN(MenuNo) = LEN(@MenuNo));
INSERT Menu(MenuNo, MenuName, MenuLink, Type, MenuDesc, OrderID, Enabled, MenuIco) VALUES(@MenuNo, @MenuName, @MenuLink, @Type, @MenuDesc, @OrderID, @Enabled, @MenuIco)";
}
else
{
db.StrSQL = "UPDATE Menu SET MenuName=@MenuName, MenuLink=@MenuLink, Type=@Type, MenuDesc=@MenuDesc, OrderID=@OrderID, Enabled=@Enabled, MenuIco=@MenuIco WHERE MenuNo=@MenuNo";
}
int iResult = db.ExecuteSQL();
return(iResult > 0);
}
}
/// <summary>取得使用者清單資料檔</summary>
public static DataTable GetUserList(int?_id, string _account, string _password, string _email, string _name, Pages _page)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("ID", _id);
param.Add("Account", _account);
param.Add("Password", _password);
param.Add("Email", "%" + _email + "%");
param.Add("Name", "%" + _name + "%");
db.SqlParams = param;
//條件式
string strWhere = "";
if (_id != null)
{
strWhere += " AND ID = @ID ";
}
if (!string.IsNullOrEmpty(_account))
{
strWhere += " AND Account = @Account ";
}
if (!string.IsNullOrEmpty(_password))
{
strWhere += " AND Password = @Password ";
}
if (!string.IsNullOrEmpty(_email))
{
strWhere += " AND Email LIKE @Email ";
}
if (!string.IsNullOrEmpty(_name))
{
strWhere += " AND Name LIKE @Name ";
}
//取回資料
int iTotal;
var sql = "SELECT * FROM [User] WHERE 1=1 " + strWhere;
var dt = db.ExecuteDataTable(sql, "ID", _page.PageIndex, _page.PageSize, out iTotal);
_page.TotalRows = iTotal;
return(dt);
}
}
/// <summary>取得使用者所屬的群組清單</summary>
public static DataTable GetUserGroup(string Account)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("Account", Account);
db.SqlParams = param;
//取回資料
db.StrSQL = @"SELECT g.* FROM GroupUser gu
INNER JOIN [Group] g ON g.GroupID = gu.GroupID
WHERE Account = @Account";
var dt = db.ExecuteDataTable();
return(dt);
}
}
/// <summary>取得群組底下的使用者清單</summary>
public static DataTable GetGroupUser(string GroupID)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("GroupID", GroupID);
db.SqlParams = param;
//取回資料
db.StrSQL = @"SELECT u.* FROM GroupUser gu
INNER JOIN [User] u ON u.Account = gu.Account
WHERE gu.GroupID = @GroupID";
var dt = db.ExecuteDataTable();
return(dt);
}
}
/// <summary>取得DockDoor資料</summary>
public static DataTable GetDockDoorList(string DockDoorID, string IP, string Alarm, string ContainerID, Pages _page)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("DockDoorID", DockDoorID);
param.Add("IP", IP);
param.Add("Alarm", Alarm);
param.Add("ContainerID", ContainerID);
db.SqlParams = param;
//條件式
string strWHERE = "";
if (!string.IsNullOrEmpty(DockDoorID))
{
strWHERE += " AND DockDoorID = @DockDoorID ";
}
if (!string.IsNullOrEmpty(IP))
{
strWHERE += " AND IP = @IP ";
}
if (!string.IsNullOrEmpty(Alarm))
{
strWHERE += " AND Alarm = @Alarm ";
}
if (!string.IsNullOrEmpty(ContainerID))
{
strWHERE += " AND ContainerID = @ContainerID ";
}
//取回資料
int iTotal;
var sql = @"SELECT d.*, f.FilterName, f.FilterRules FROM Innolux_DockDoor d
INNER JOIN Innolux_Filter f ON f.FilterCode = d.FilterCode
WHERE 1=1" + strWHERE;
var dt = db.ExecuteDataTable(sql, "DockDoorID", _page.PageIndex, _page.PageSize, out iTotal);
_page.TotalRows = iTotal;
return(dt);
}
}
/// <summary>儲存Filter資料</summary>
public static void SaveFilter(Models.ViewModel.TagRule.TagRuleDetailViewModel m)
{
//驗証白名單字元
string[] Rules = m.FilterRules.Split(',');
int iRules = Rules.Count(x => x.Length == 2);
if (iRules != Rules.Length)
{
throw new Exception("白名單規則每組只允許2個字元");
}
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("FilterCode", m.FilterCode);
param.Add("FilterName", m.FilterName);
param.Add("FilterRules", m.FilterRules);
db.SqlParams = param;
if (m.Action == "Add")
{
//新增群組
db.StrSQL =
@"DECLARE @Count int;
SET @Count = (SELECT COUNT(*) FROM Innolux_Filter WHERE FilterCode = @FilterCode);
IF (@Count = 0) BEGIN
INSERT Innolux_Filter(FilterCode, FilterName, FilterRules) VALUES(@FilterCode, @FilterName, @FilterRules);
END ELSE BEGIN
THROW 51000, '規則編號已存在', 1;
END";
}
else
{
//修改群組
db.StrSQL = "UPDATE Innolux_Filter SET FilterName=@FilterName, FilterRules=@FilterRules WHERE FilterCode=@FilterCode";
}
db.ExecuteSQL();
}
}
/// <summary>還原使用者密碼為預設密碼</summary>
public static bool SetUserDefaultPassword(string Account)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//預設密碼
string pwd = SysParamsDataAccess.GetSysParamsValue("DefaultUserPassword");
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("Account", Account);
param.Add("Password", Util.MD5(pwd, 32));
db.SqlParams = param;
//更新使用者最後登入時間
db.StrSQL = "UPDATE [User] SET Password = @Password WHERE Account = @Account";
int iResult = db.ExecuteSQL();
return(iResult > 0);
}
}
/// <summary>新增讀取Tag Log</summary>
private static void AddTagLog(string _DockDoorID, int _Mask, PostModel m, PostResult r, string _Data)
{
using (var db = new DataBase.DataBase(Definition.Conn))
{
var param = new DataBase.SqlParams();
param.Add("DockDoorID", _DockDoorID);
param.Add("Mask", _Mask);
param.Add("IP", m.ip);
param.Add("DateTime", DateTime.Now);
param.Add("Status", r.result);
param.Add("Alarm", r.result == "N" ? "Y" : "");
param.Add("Msg", r.msg);
param.Add("Data", _Data);
param.Add("PostData", JsonConvert.SerializeObject(m));
db.SqlParams = param;
db.StrSQL = "UPDATE Innolux_DockDoor SET Alarm = @Alarm WHERE IP = @IP;" +
"INSERT [Innolux_TagLog](DockDoorID, IP, DateTime, Mask, Status, Msg, Data, PostData) VALUES(@DockDoorID, @IP, @DateTime, @Mask, @Status, @Msg, @Data, @PostData);";
db.ExecuteSQL();
}
}
/// <summary>取得白名單規則</summary>
public static DataTable GetFilterList(string FilterCode)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("FilterCode", FilterCode);
db.SqlParams = param;
//條件式
string strWHERE = "";
if (!string.IsNullOrEmpty(FilterCode))
{
strWHERE += " AND FilterCode = @FilterCode ";
}
//取回資料
db.StrSQL = "SELECT * FROM Innolux_Filter WHERE 1=1" + strWHERE;
return(db.ExecuteDataTable());
}
}
/// <summary>移除白名單規則</summary>
public static void RemoveFilter(string FilterCode)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("FilterCode", FilterCode);
db.SqlParams = param;
//取回資料
db.StrSQL =
@"DECLARE @Count int;
SET @Count = (SELECT COUNT(*) FROM Innolux_DockDoor WHERE FilterCode = @FilterCode);
IF(@Count = 0) BEGIN
DELETE FROM Innolux_Filter WHERE FilterCode = @FilterCode;
END ELSE BEGIN
THROW 51000, '無法刪除，已有碼頭使用此規則!', 1;
END";
db.ExecuteSQL();
}
}
/// <summary>取得選單檔</summary>
public static DataTable GetMenuList(int?MenuNoLen, string MenuNo, string MenuName, bool?Enabled)
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("MenuNoLen", MenuNoLen);
param.Add("MenuNo", MenuNo);
param.Add("MenuName", MenuName);
param.Add("Enabled", Enabled);
db.SqlParams = param;
//條件式
string strWhere = "";
if (MenuNoLen != null)
{
strWhere += " AND LEN(MenuNo) = @MenuNoLen ";
}
if (!string.IsNullOrEmpty(MenuNo))
{
strWhere += " AND MenuNo = @MenuNo ";
}
if (!string.IsNullOrEmpty(MenuName))
{
strWhere += " AND MenuName = @MenuName ";
}
if (Enabled != null)
{
strWhere += " AND Enabled = @Enabled ";
}
//取回資料
db.StrSQL = "SELECT * FROM Menu WHERE 1=1" + strWhere + " ORDER BY OrderID";
return(db.ExecuteDataTable());
}
}
public static void SaveDockDoorInfo(string truckID, string Status, string dockdoorID)
{
string O_Status = null;
DataTable dt = null;
DataTable dt1 = null;
string IP = null;
string strNum, strTotal;
string cid = string.Empty;
string cstatus = string.Empty;
Result r = new Result();
string ASN = null;
string DN = null;
string CaptionPanelIP = null;
//Save in ORACLE
using (var db = new DataBase.OracleDataBase(Definition.WMSConn))
{
db.StrSQL = "SELECT DN_NO FROM WMS_RFID_CONTAINER_CHECK_V WHERE CONTAINER_NO='" + truckID + "'";
dt1 = db.ExecuteDataTable();
if (dt1.DefaultView.Count > 0)
{
DataRow DN_dr = dt1.Rows[0];
DN = DN_dr["DN_NO"].ToString();
}
}
using (var db = new DataBase.DataBase(Definition.Conn, true))
{
try
{
db.StrSQL = "SELECT ASN_NO FROM WMS_RFID_TRUCK WHERE TRUCK_NO='" + truckID + "'";
dt = db.ExecuteDataTable();
DataRow dr_ASN = dt.Rows[0];
if (dt.DefaultView.Count > 0)
{
ASN = dr_ASN["ASN_NO"].ToString();
}
if (Status == "I")
{
if (ASN != null && ASN != "")
{
db.StrSQL = "UPDATE WMS_RFID_TRUCK SET STATUS='I',DOCKED='" + dockdoorID + "' WHERE TRUCK_NO='" + truckID + "'";
}
else
{
if (DN != null && DN != "")
{
db.StrSQL = "UPDATE WMS_RFID_TRUCK SET STATUS='O',DOCKED='" + dockdoorID + "' WHERE TRUCK_NO='" + truckID + "'";
}
else
{
db.StrSQL = "UPDATE WMS_RFID_TRUCK SET STATUS=' ',DOCKED='" + dockdoorID + "' WHERE TRUCK_NO='" + truckID + "'";
}
}
db.ExecuteSQL();
// db.Commit();
}
else
{
db.StrSQL = "UPDATE WMS_RFID_TRUCK SET STATUS='" + DBNull.Value + "',DOCKED='" + DBNull.Value + "' WHERE TRUCK_NO='" + truckID + "'";
db.ExecuteSQL();
// db.Commit();
}
db.StrSQL = "SELECT STATUS FROM WMS_RFID_TRUCK WHERE TRUCK_NO='" + truckID + "'";
DataTable dt2 = db.ExecuteDataTable();
DataRow dtstatus = dt2.Rows[0];
if (dt2.DefaultView.Count > 0)
{
O_Status = dtstatus["STATUS"].ToString();
}
var param = new DataBase.SqlParams();
param.Add("ContainerID", truckID);
param.Add("ContainerStatus", O_Status);
param.Add("DockDoorID", dockdoorID);
DateTime dtime = DateTime.Now;
param.Add("UpdateTime", dtime);
if (Status == "I")
{
param.Add("Flag", false);
}
else
{
param.Add("Flag", true);
}
db.SqlParams = param;
if (Status == "I")
{
db.StrSQL = " UPDATE [Innolux_DockDoor] SET ContainerID=@ContainerID, ContainerStatus=@ContainerStatus,UpdateTime=@UpdateTime,Flag=@Flag WHERE DockDoorID=@DockDoorID";
db.ExecuteSQL();
}
DataTable dtIP = null;
db.StrSQL = "SELECT IP,CaptionPanelIP,ContainerID,ContainerStatus FROM Innolux_DockDoor WHERE DockDoorID='" + dockdoorID + "'";
dtIP = db.ExecuteDataTable();
DataRow row = dtIP.Rows[0];
if (dtIP.DefaultView.Count > 0)
{
IP = row["IP"].ToString();
CaptionPanelIP = row["CaptionPanelIP"].ToString();
cid = row["ContainerID"].ToString();
cstatus = row["ContainerStatus"].ToString();
}
if (Status == "O")
{
db.StrSQL = " UPDATE [Innolux_DockDoor] SET ContainerID='', ContainerStatus=@ContainerStatus,UpdateTime=@UpdateTime, Flag=@Flag WHERE ContainerID=@ContainerID";
db.ExecuteSQL();
}
//Get IP
//Save in MySql
if (Status == "I")
{
if (ASN != null && ASN != "")
{
// ApiDataAccess.UpdateControlDB(IP, "INSERT checkin(car_in,car_out)VALUES('I','0')");
ApiDataAccess.UpdateControlDB(IP, "UPDATE checkin SET car_in='I',car_out='0'");
ApiDataAccess.UpdateControlDB(IP, "UPDATE status SET status='I'");
}
else if (DN != null && DN != "")
{
// ApiDataAccess.UpdateControlDB(IP, "INSERT checkin(car_in,car_out)VALUES('O','0')");
ApiDataAccess.UpdateControlDB(IP, "UPDATE checkin SET car_in='O',car_out='0'");
ApiDataAccess.UpdateControlDB(IP, "UPDATE status SET status='O'");
}
else
{
// ApiDataAccess.UpdateControlDB(IP, "INSERT checkin(car_in,car_out)VALUES('_','0')");
ApiDataAccess.UpdateControlDB(IP, "UPDATE checkin SET car_in=' ',car_out='0'");
ApiDataAccess.UpdateControlDB(IP, "UPDATE status SET status=' '");
}
var mes = "櫃號：" + truckID + " 已進站";
ApiDataAccess.ControlMachine(CaptionPanelIP, mes, 0);
}
else
{
if (cstatus == "I" || cstatus == "O")
{
ApiDataAccess.GetCountProcess(dockdoorID, cid, cstatus, out strNum, out strTotal);
if (strNum == strTotal)
{
var msg = string.Format("櫃號：{0}({1}/{2})已離廠", truckID, strNum, strTotal);
ApiDataAccess.ControlMachine(CaptionPanelIP, msg, 3);
ApiDataAccess.UpdateControlDB(IP, "UPDATE checkin SET car_in='0',car_out='Y'");
}
else
{
var msg = string.Format("櫃號：{0}({1}/{2})尚未完成作業", truckID, strNum, strTotal);
ApiDataAccess.ControlMachine(CaptionPanelIP, msg, 3);
ApiDataAccess.UpdateControlDB(IP, "UPDATE checkin SET car_in='0',car_out='N'");
// ApiDataAccess.UpdateControlDB(IP, "INSERT checkin(car_in,car_out)VALUES('0','N')");
}
}
else if (cstatus == " ")
{
var msg = string.Format("櫃號：{0})已離廠", truckID);
ApiDataAccess.ControlMachine(CaptionPanelIP, msg, 3);
ApiDataAccess.UpdateControlDB(IP, "UPDATE checkin SET car_in='0',car_out='Y'");
}
}
db.Commit();
}
catch (Exception ex)
{
var mes = ex.Message;
db.Rollback();
throw ex;
}
}
}
/// <summary>修改使用者資料</summary>
public static void SaveUserInfo(Models.ViewModel.User.UserDetailViewModel m)
{
string action = "";
//新增
if (m.ID == 0)
{
action = "Add";
m.Password = SysParamsDataAccess.GetSysParamsValue("DefaultUserPassword"); //預設密碼
//驗証帳號是否重覆
var dt = GetUserList(null, m.Account, null, null, null, new Pages());
if (dt.Rows.Count > 0)
{
throw new Exception("已有重覆的帳號");
}
}
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn, true))
{
try
{
//資料庫參數
var param = new DataBase.SqlParams();
param.Add("ID", m.ID);
param.Add("Email", m.Email);
param.Add("Password", Util.MD5(m.Password, 32));
param.Add("Account", m.Account);
param.Add("Name", m.Name);
param.Add("IsSuper", m.IsSuper);
param.Add("IsLock", m.IsLock);
param.Add("LastLoginDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
param.Add("CreateDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
db.SqlParams = param;
if (action == "Add")
{
//新增使用者
db.StrSQL = "INSERT [User](Email, Password, Account, Name, IsSuper, IsLock, LastLoginDate, CreateDate) VALUES(@Email, @Password, @Account, @Name, @IsSuper, @IsLock, @LastLoginDate, @CreateDate);";
}
else
{
//修改使用者
db.StrSQL = "UPDATE [User] SET Email=@Email, Name=@Name, IsSuper=@IsSuper, IsLock=@IsLock WHERE ID=@ID;";
}
//刪除舊有使用者選單權限與群組資料
db.StrSQL += @"DELETE FROM [Auth] WHERE Account = @Account;
DELETE FROM [GroupUser] WHERE Account = @Account";
db.ExecuteSQL();
//逐筆判斷選單權限
foreach (var ma in m.AuthList)
{
if (ma.Add || ma.Edit || ma.Del || ma.Query || ma.Audit || ma.Print || ma.Export || ma.Import || ma.Admin)
{
param.Clear();
param.Add("Account", m.Account);
param.Add("MenuNo", ma.MenuNo);
param.Add("Add", ma.Add);
param.Add("Edit", ma.Edit);
param.Add("Del", ma.Del);
param.Add("Query", ma.Query);
param.Add("Audit", ma.Audit);
param.Add("Print", ma.Print);
param.Add("Export", ma.Export);
param.Add("Import", ma.Import);
param.Add("Admin", ma.Admin);
param.Add("Enabled", true);
param.Add("CreateDate", DateTime.Now);
param.Add("ModifyDate", DateTime.Now);
db.SqlParams = param;
db.StrSQL = @"INSERT [Auth](GroupID, Account, MenuNo, [Add], Edit, Del, Query, Audit, [Print], Export, Import, Admin, Enabled, CreateDate, ModifyDate)
VALUES('', @Account, @MenuNo, @Add, @Edit, @Del, @Query, @Audit, @Print, @Export, @Import, @Admin, @Enabled, @CreateDate, @ModifyDate);";
db.ExecuteSQL();
}
}
//逐筆新增使用者所屬的群組
foreach (var g in m.GroupItems)
{
param.Clear();
param.Add("GroupID", g.GroupID);
param.Add("Account", m.Account);
db.SqlParams = param;
db.StrSQL = "INSERT [GroupUser](GroupID, Account) VALUES(@GroupID, @Account)";
db.ExecuteSQL();
}
//確認執行交易
db.Commit();
}
catch (Exception e)
{
//回復交易
db.Rollback();
throw e;
}
}
}
