/// <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;
}
}
}