/// <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);
}
}
private void OnLoad()
{
database = new DataBase.DataBase();
//database.connectDB();
DateTime now = DateTime.Now;
startDatePicker.Value = now.Date;
startTimePicker.Value = now.ToUniversalTime();
now = now.AddDays(1.0);
stopDatePicker.Value = now.Date;
stopTimePicker.Value = now.ToUniversalTime();
schedulerComboBox.SelectedIndex = 0;
List <string> objectiveFunctionnames = Forms.ObjectiveBuilderForm.getSavedObjectiveNames();
for (int i = 0; i < objectiveFunctionnames.Count; i++)
{
objectiveComboBox.Items.Add(objectiveFunctionnames[i]);
}
if (objectiveComboBox.Items.Count > 0)
{
objectiveComboBox.SelectedIndex = 0;
}
comboScenarioBox.SelectedIndex = 0;
database.displayAllSatellites(checkedSatellites);
database.displayAllStations(checkedStations);
validateRichTextBox();
runList = new List <AutomatedRun>();
}
/// <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);
}
}
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>儲存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 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 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 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 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 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>儲存系統參數資料</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 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>更新使用者登入時間</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>取得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 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;
}
}
}
protected void AddParametersFromCache(DbCommand command, DataBase.DataBase database)
{
IDataParameter[] cachedParameterSet = this.cache.GetCachedParameterSet(database.ConnectionString, command);
foreach (IDataParameter parameter in cachedParameterSet)
{
command.Parameters.Add(parameter);
}
}
/// <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>取得條件式最後的群組編號</summary>
public static string GetMaxGroupID()
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//取回資料
db.StrSQL = "SELECT MAX(GroupID) FROM [Group]";
string strID = db.ExecuteScalar();
return(strID == "" ? "00" : strID);
}
}
//public static void SaveDockDoorInfo(string truckID, string Status,string dockdoorID)
//{
// string O_Status = null;
// DataTable dt = null;
// DataTable dt1 = null;
// string IP = null;
// int Total = 0;
// int iNum = 0;
// Result r = new Result();
// string ASN = null;
// string DN = null;
// string CaptionPanelIP = null;
// try
// {
// //Save in ORACLE
// using (var db = new DataBase.DataBase(Definition.Conn))
// {
// db.StrSQL = "SELECT COUNT(PALLET_ID) Total, COUNT(RFID_OK_FLAG) iNum FROM WMS_RFID_CONTAINER_CHECK_V";
// DataTable dtable = db.ExecuteDataTable();
// DataRow dr = dtable.Rows[0];
// if (dtable.DefaultView.Count > 0)
// {
// Total = Convert.ToInt32(dr["Total"]);
// iNum = Convert.ToInt32(dr["iNum"]);
// }
// db.ExecuteSQL();
// if (Status == "I")
// {
// 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 (ASN != null && ASN != "")
// {
// db.StrSQL = "UPDATE WMS_RFID_TRUCK SET STATUS='I',DOCKED='" + dockdoorID + "' WHERE TRUCK_NO='" + truckID + "'";
// }
// else
// {
// db.StrSQL = "SELECT DN_NO FROM WMS_RFID_CONTAINER_CHECK WHERE CONTAINER_NO='" + truckID + "'";
// dt1 = db.ExecuteDataTable();
// if (dt1.DefaultView.Count > 0)
// {
// DataRow DN_dr = dt1.Rows[0];
// DN = DN_dr["DN_NO"].ToString();
// 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();
// db.StrSQL = "SELECT STATUS FROM WMS_RFID_TRUCK WHERE TRUCK_NO='" + truckID + "'";
// DataTable dt2 = db.ExecuteDataTable();
// DataRow row = dt2.Rows[0];
// if (dt2.DefaultView.Count > 0)
// {
// O_Status = row["STATUS"].ToString();
// }
// }
// else
// {
// db.StrSQL = "UPDATE WMS_RFID_TRUCK SET STATUS='" + null + "',DOCKED='" + null + "' WHERE TRUCK_NO='" + truckID + "'";
// db.ExecuteSQL();
// }
// }
// //開啟資料庫存取物件
// using (var db = new DataBase.DataBase(Definition.Conn))
// {
// 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;
// //Get IP
// if (Status == "I")
// {
// DataTable dtable = null;
// db.StrSQL = "SELECT IP,CaptionPanelIP FROM Innolux_DockDoor WHERE DockDoorID='" + dockdoorID + "'";
// dtable = db.ExecuteDataTable();
// DataRow row = dtable.Rows[0];
// IP = row["IP"].ToString();
// CaptionPanelIP = row["CaptionPanelIP"].ToString();
// }
// else
// {
// DataTable dtable = null;
// db.StrSQL = "SELECT IP,CaptionPanelIP FROM Innolux_DockDoor WHERE DockDoorID='" + dockdoorID + "'";
// dtable = db.ExecuteDataTable();
// DataRow row = dtable.Rows[0];
// IP = row["IP"].ToString();
// CaptionPanelIP = row["CaptionPanelIP"].ToString();
// }
// if (Status == "I")
// {
// db.StrSQL = " UPDATE [Innolux_DockDoor] SET ContainerID=@ContainerID, ContainerStatus=@ContainerStatus,UpdateTime=@UpdateTime,Flag=@Flag WHERE DockDoorID=@DockDoorID";
// db.ExecuteSQL();
// var mes = "櫃號：" + truckID + " 已進站";
// ApiDataAccess.ControlMachine(CaptionPanelIP, mes, 0);
// }
// else
// {
// db.StrSQL = " UPDATE [Innolux_DockDoor] SET ContainerID='', ContainerStatus='',UpdateTime=@UpdateTime, Flag=@Flag WHERE ContainerID=@ContainerID";
// db.ExecuteSQL();
// }
// //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=' '");
// }
// }
// else
// {
// if (Total == iNum)
// {
// var msg = string.Format("櫃號：{0}({1}/{2})已離廠", truckID, iNum, Total);
// 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, iNum, Total);
// 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')");
// }
// }
// }
// }
// catch
// {
// }
//}
public static DataTable GetDockDoorListDetails()
{
//開啟資料庫存取物件
using (var db = new DataBase.DataBase(Definition.Conn))
{
//判斷Status成功或失敗
DataTable dt = null;
db.StrSQL = "SELECT ContainerID,Flag FROM Innolux_DockDoor WHERE 1=1";
dt = db.ExecuteDataTable();
return(dt);
}
}
/// <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 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);
}
}
public string getOrderByParams(DataBase.DataBase nDataBase)
{
var OrderByParams = new StringBuilder("");
foreach (var Item in this._Columns)
{
if (OrderByParams.Length > 0)
{
OrderByParams.Append(", ");
}
OrderByParams.Append(nDataBase.FormatToDatabaseColumnName(Item.ColumnName) + " " + (Item.Ascendente ? nDataBase.Identifier_OrderBy_ASC : nDataBase.Identifier_OrderBy_DESC));
}
return(OrderByParams.ToString());
}
private void addButton_Click(object sender, EventArgs e)
{
if (radioButton3.Checked)
{
//Manual Input
DataBase.DataBase db = new DataBase.DataBase();
double lat = double.Parse(textLat.Text, CultureInfo.GetCultureInfo("en-US"));
double lon = double.Parse(textLon.Text, CultureInfo.GetCultureInfo("en-US"));
double height = double.Parse(textHeight.Text, CultureInfo.GetCultureInfo("en-US"));
Ground.Station station = new Ground.Station(textStationName.Text, lat, lon, height);
db.writeStation(station);
this.Close();
}
if (radioButton1.Checked)
{
using (var reader = new StreamReader(stationFilePath))
{
DataBase.DataBase db = new DataBase.DataBase();
string line;
while ((line = reader.ReadLine()) != null)
{
string _name;
double _latitude;
double _longitude;
double _height;
string[] res = line.Split(' ');
if (res.Count() >= 3)
{
_name = res[0];
_latitude = Convert.ToDouble(res[1]);
_longitude = Convert.ToDouble(res[2]);
if (res.Count() == 4)
{
_height = Convert.ToDouble(res[3]);
}
else
{
_height = 0.0;
}
Ground.Station station = new Ground.Station(_name, _latitude, _longitude, _height);
db.writeStation(station);
}
}
}
}
}
/// <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);
}
}
//! Draw Stations from Database onto image.
/*!
* \param DataGridViewRowCollections rows of stations to draw
* \param DataBase database that holds all the information
* \return Image bmp-Image
*/
public static Image drawStation(DataGridViewRowCollection rows, DataBase.DataBase db)
{
Image imgStation = Properties.Resources.worldsmaller;
Pen penRest = new Pen(Color.Orange, 2);
using (var graphics = Graphics.FromImage(imgStation))
{
for (int i = 0; i < rows.Count; i++)
{
Ground.Station item = db.getStationFromDB(rows[i].Cells[0].Value.ToString());
Point p = item.getGeoCoordinate().toPoint(imgStation.Width, imgStation.Height);
graphics.DrawRectangle(penRest, p.X - 10, p.Y - 10, 20, 20);
}
}
return(imgStation);
}
/// <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>取得群組底下的使用者清單</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);
}
}
private void InitializeConnection(DataBase.DataBaseType nType, string nConnectionString)
{
this._DataBase = DataBaseFactory.CreateDatabase(nType, nConnectionString);
this._IsRemoting = (DataBaseFactory.GetRemotingUrl(nConnectionString) != "");
this._IsTrusted = DataBaseFactory.GetRemotingTrusted(nConnectionString);
}
