/// <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 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; } } }
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; } } }
/// <summary>儲存群組資料</summary> public static void SaveGroupInfo(GroupDetailViewModel m) { string action = ""; //有無群組編號,若無則視為新增 if (string.IsNullOrEmpty(m.GroupID)) { action = "Add"; //設定新群組編號 string strMaxGroupID = GroupDataAccess.GetMaxGroupID(); m.GroupID = Util.GetPadLeftString(strMaxGroupID, 1); } //開啟資料庫存取物件 using (var db = new DataBase.DataBase(Definition.Conn, true)) { try { //資料庫參數 var param = new DataBase.SqlParams(); param.Add("GroupID", m.GroupID); param.Add("GroupName", m.GroupName); param.Add("GroupDesc", m.GroupDesc); db.SqlParams = param; if (action == "Add") { //新增群組 db.StrSQL = "INSERT [Group](GroupID, GroupName, GroupDesc) VALUES(@GroupID, @GroupName, @GroupDesc);"; } else { //修改群組 db.StrSQL = "UPDATE [Group] SET GroupName=@GroupName, GroupDesc=@GroupDesc WHERE GroupID=@GroupID;"; } //刪除舊有群組選單權限與使用者清單 db.StrSQL += @"DELETE FROM [Auth] WHERE GroupID = @GroupID; DELETE FROM [GroupUser] WHERE GroupID = @GroupID;"; 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("GroupID", m.GroupID); 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(@GroupID, '', @MenuNo, @Add, @Edit, @Del, @Query, @Audit, @Print, @Export, @Import, @Admin, @Enabled, @CreateDate, @ModifyDate);"; db.ExecuteSQL(); } } //逐筆新增群組底下的使用者 foreach (var u in m.GroupUserItems) { param.Clear(); param.Add("GroupID", m.GroupID); param.Add("Account", u.Account); db.SqlParams = param; db.StrSQL = @"INSERT [GroupUser](GroupID, Account) VALUES(@GroupID, @Account);"; db.ExecuteSQL(); } //確認執行交易 db.Commit(); } catch (Exception e) { //回復交易 db.Rollback(); throw e; } } }