/// <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;
}
}
}
/// <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>刪除指定的選單編號</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>儲存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();
}
}
/// <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 PostResult DelContainer(Container m)
{
PostResult r = new PostResult();
//取得此碼頭資料
DataTable dtDockDoor = DockDoorDataAccess.GetDockDoorList(null, m.ip, null, null, new Pages());
DataRow rowDoor = dtDockDoor.Rows[0];
string strDockDoorID = rowDoor["DockDoorID"].ToString();
string strContainerID = rowDoor["ContainerID"].ToString();
string strContainerStatus = rowDoor["ContainerStatus"].ToString();
string strCaptionPanelIP = rowDoor["CaptionPanelIP"].ToString();
try
{
string[] StatusList = new string[] { "I", "O" };
if (StatusList.Contains(strContainerStatus))
{
//判斷裝卸載數量
string strNum, strTotal;
GetCountProcess(strDockDoorID, strContainerID, strContainerStatus, out strNum, out strTotal);
if (strNum == strTotal)
{
r.result = "Y";
r.msg = string.Format("櫃號：{0}({1}/{2})已離廠", strContainerID, strNum, strTotal);
}
else
{
throw new Exception(string.Format("櫃號：{0}({1}/{2})尚未完成作業", strContainerID, strNum, strTotal));
}
}
else
{
r.result = "Y";
r.msg = string.Format("櫃號：{0}已離廠", strContainerID);
}
}
catch (Exception ex)
{
r.result = "N";
r.msg = ex.Message;
}
finally
{
//清空碼頭目前停靠的貨櫃ID
using (var db = new DataBase.DataBase(Definition.Conn))
{
db.StrSQL = "UPDATE Innolux_DockDoor SET ContainerID = '', ContainerStatus = '', Alarm = '' WHERE IP = '" + m.ip + "'";
db.ExecuteSQL();
}
}
ControlMachine(strCaptionPanelIP, r.msg, 3);
return(r);
}
/// <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 UpdateTruckStatus(string IP, out string ContainerStatus)
{
string strContainerID = "", strMsg = "";
//取得DockDoor資訊
DataTable dtDoor = GetDockDoorList(null, IP, null, null, new Pages());
DataRow row = dtDoor.Rows[0];
//取得貨櫃狀態
strContainerID = row["ContainerID"].ToString();
ContainerStatus = row["ContainerStatus"].ToString();
if (strContainerID != "")
{
//取得並更新貨櫃狀態
ContainerStatus = ApiDataAccess.GetContainerStatus(strContainerID);
using (var db = new DataBase.DataBase(Definition.Conn))
{
db.StrSQL = "UPDATE Innolux_DockDoor SET ContainerStatus = '" + ContainerStatus + "' WHERE IP = '" + IP + "'";
db.ExecuteSQL();
}
//更新控制器的貨櫃狀態
ApiDataAccess.UpdateControlDB(IP, "UPDATE status SET status = '" + ContainerStatus + "'");
//依狀態回傳結果
if (ContainerStatus == "O")
{
strMsg = string.Format("貨櫃號碼{0}的狀態為：{1}", strContainerID, "待裝櫃");
}
else if (ContainerStatus == "I")
{
strMsg = string.Format("貨櫃號碼{0}的狀態為：{1}", strContainerID, "待卸櫃");
}
else
{
throw new Exception("無" + strContainerID + "的貨櫃狀態");
}
}
else if (ContainerStatus == "N")
{
throw new Exception("無貨櫃標籤");
}
else
{
throw new Exception("無貨櫃靠站資訊");
}
return(strMsg);
}
/// <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>儲存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 void ClearAlarm(string id)
{
//取得指定警報中的碼頭
DataTable dtDoor = DockDoorDataAccess.GetDockDoorList(id, null, null, null, new Pages());
if (dtDoor.Rows.Count > 0)
{
DataRow row = dtDoor.Rows[0];
string strIP = row["IP"].ToString();
string strContainerID = row["ContainerID"].ToString();
string strCaptionPanelIP = row["CaptionPanelIP"].ToString();
//修改Reader DB，以觸發DB Trigger
UpdateControlDB(strIP, "UPDATE alarm SET alarm='" + DateTime.Now.ToString("yyyyMMddHHmmss") + "'");
//清除碼頭的異常狀態
using (var db = new DataBase.DataBase(Definition.Conn))
{
db.StrSQL = "UPDATE Innolux_DockDoor SET Alarm = '' WHERE DockDoorID = '" + id + "'";
db.ExecuteSQL();
}
//若有貨櫃停靠，則播放最後一次成功訊息
if (strContainerID != "")
{
using (var db = new DataBase.DataBase(Definition.Conn))
{
string strToday = DateTime.Now.ToString("yyyyMMdd");
db.StrSQL = "SELECT TOP 1 Msg FROM Innolux_TagLog WHERE (DockDoorID = '" + id + "') AND (Status IN ('Y','A')) AND (Msg LIKE '%" + strContainerID + "%') AND (Convert(varchar, DateTime, 112) = '" + strToday + "') ORDER BY DateTime DESC";
string strMsg = db.ExecuteScalar() ?? "";
ControlMachine(strCaptionPanelIP, strMsg, 0);
}
}
else
{
ControlMachine(strCaptionPanelIP, "", 1);
}
}
}
/// <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();
}
}
//static string DB_Prestr = "";
/// <summary>貨櫃進站函式</summary>
public static PostResult AddContainer(Container m)
{
PostResult r = new PostResult();
string strContainerID = "", strContainerStatus = "";
try
{
//判斷是否有貨櫃標籤
if (m.containers.Count == 0)
{
throw new Exception("無貨櫃標籤");
}
else
{
m.containers = m.containers.Select(x => x.Trim()).ToList();
} //貨櫃號碼去空白
using (var db = new DataBase.DataBase(Definition.Conn))
{
//檢查是否誤讀到其它貨櫃
for (int i = m.containers.Count - 1; i >= 0; i--)
{
string CID = m.containers[i].Substring(1);
db.StrSQL = "SELECT COUNT(*) FROM Innolux_DockDoor WHERE IP <> '" + m.ip + "' AND ContainerID = '" + CID + "'";
//ExecuteScalar 返回單行單列的結果集
if (db.ExecuteScalar() != "0")
{
m.containers.RemoveAt(i);
}
}
//判斷過濾後是否只剩一筆貨櫃ID
if (m.containers.Count == 1)
{
strContainerID = m.containers[0].Substring(1);
strContainerStatus = GetContainerStatus(strContainerID);
//更新控制器貨櫃狀態
UpdateControlDB(m.ip, "UPDATE status SET status='" + strContainerStatus + "'");
r.result = strContainerStatus;
r.msg = "櫃號：" + strContainerID + "已進站";
}
else
{
throw new Exception("無貨櫃標籤");
}
}
}
catch (Exception ex)
{
r.result = "N";
r.msg = ex.Message;
}
finally
{
using (var db = new DataBase.DataBase(Definition.Conn))
{
//記錄碼頭目前停靠的貨櫃ID與狀態
string strAlarm = (r.result == "N" ? "Y" : "");
db.StrSQL = "UPDATE Innolux_DockDoor SET ContainerID = '" + strContainerID + "', ContainerStatus = '" + r.result + "', Alarm = '" + strAlarm + "' WHERE IP = '" + m.ip + "'";
db.ExecuteSQL();
}
//控制語音與字幕機
DataTable dtDockDoor = DockDoorDataAccess.GetDockDoorList(null, m.ip, null, null, new Pages());
DataRow rowDoor = dtDockDoor.Rows[0];
string strCaptionPanelIP = rowDoor["CaptionPanelIP"].ToString();
ControlMachine(strCaptionPanelIP, r.msg, 0);
}
return(r);
}
/// <summary>收料邏輯</summary>
private static PostResult POST_IN(string DockDoorID, string ContainerID, string ContainerStatus, PostModel m)
{
PostResult r = new Models.ApiModel.PostResult();
using (var db = new DataBase.OracleDataBase(Definition.WMSConn))
{
//讀取指定棧板號碼的資料
//20180214 增加 RESERVED_03 = ContainerID 條件 --> 要讓一單多車棧板資料可以上報(查詢出的資料只有一筆才合法)
string strRFIDList = string.Join(",", m.pallets.Select(x => "'" + x.pallet + "'"));
string strSQL = "SELECT * FROM " + DB_Prestr + "WMS_RFID_CHECK_LIST_VIEW WHERE (RESERVED_03 = '" + ContainerID + "') AND (DOCK_DOOR_NO = '" + DockDoorID + "') AND (RFID IN (" + strRFIDList + "))";
db.StrSQL = strSQL;
DataTable dtCheckList = db.ExecuteDataTable();
using (var dba = new DataBase.DataBase(Definition.Conn))
{
//20180214 增加 --> 記錄 Select View SQL 語法
dba.StrSQL = "INSERT DebugSQL (sql,updatetime) VALUES ('" + strSQL.Replace("'", "") + "','" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "')";
dba.ExecuteSQL();
}
#region 檢查RFID資訊是否異常
foreach (PalletItem item in m.pallets)
{
DataRow[] rows = dtCheckList.Select("RFID = '" + item.pallet + "'");
if (rows.Length == 1)
{
string strStatus = rows[0]["STATUS"].ToString();
if (strStatus != "0")
{
throw new Exception(item.pallet + "異常資訊");
}
}
else
{
throw new Exception(item.pallet + "異常資訊");
}
}
#endregion
//判斷卸櫃資料是否正確
//20180226 增加 RESERVED_05 == Definition.AppPlant 條件 --> 確認屬於該廠的進貨
var listPallet = dtCheckList.AsEnumerable().Where(x => x.Field <string>("RESERVED_03") == ContainerID && x.Field <string>("RESERVED_05") == Definition.AppPlant).Select(x => x.Field <string>("RFID")).ToList();
var listErrorPallet = m.pallets.FindAll(x => !listPallet.Contains(x.pallet)).Select(x => x.pallet);
if (listErrorPallet.Count() > 0)
{
throw new Exception(string.Join(",", listErrorPallet) + " 不屬於該廠區");
}
//逐筆執行過帳
foreach (DataRow row in dtCheckList.Rows)
{
//設定DB參數值
var param = new DataBase.SqlParams();
foreach (DataColumn col in dtCheckList.Columns)
{
param.Add(col.ColumnName, row[col.ColumnName].ToString());
}
db.SqlParams = param;
db.StrSQL = "INSERT INTO " + DB_Prestr + "WMS_RFID_OK_LIST(PLANT, DOCK_DOOR_NO, ACTION, RFID, RESERVED_01, RESERVED_02, RESERVED_03, RESERVED_04, RESERVED_05, RESERVED_06, RESERVED_07, RESERVED_08, RESERVED_09, RESERVED_10, RESERVED_11, RESERVED_12, RESERVED_13, RESERVED_14, RESERVED_15, RESERVED_16, RESERVED_17, RESERVED_18, RESERVED_19, RESERVED_20) " +
"VALUES(:PLANT, :DOCK_DOOR_NO, :ACTION, :RFID, :RESERVED_01, :RESERVED_02, :RESERVED_03, :RESERVED_04, :RESERVED_05, :RESERVED_06, :RESERVED_07, :RESERVED_08, :RESERVED_09, :RESERVED_10, :RESERVED_11, :RESERVED_12, :RESERVED_13, :RESERVED_14, :RESERVED_15, :RESERVED_16, :RESERVED_17, :RESERVED_18, :RESERVED_19, :RESERVED_20)";
db.ExecuteSQL();
}
//判斷卸載數量
string strNum, strTotal;
GetCountProcess(DockDoorID, ContainerID, ContainerStatus, out strNum, out strTotal);
if (strNum != strTotal)
{
r.result = "Y";
r.msg = string.Format("櫃號:{0}({1}/{2})收料中", ContainerID, strNum, strTotal);
}
else
{
r.result = "A";
r.msg = string.Format("櫃號:{0}({1}/{2})收料完成", ContainerID, strNum, strTotal);
}
}
return(r);
}
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 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;
}
}
}
/// <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;
}
}
}