/// <summary>
/// 添加群組班次關係
/// </summary>
/// <param name="groupInfo">群組信息</param>
/// <param name="shiftInfo">班次信息</param>
/// <param name="sqlTrans">事務實體</param>
/// <returns></returns>
public bool AddGroupShiftTypeInfo(ShiftTypeGroup_stg_Info groupInfo, ShiftTypeMaster_stm_Info shiftInfo, SqlTransaction sqlTrans)
{
string strSql = string.Empty;
if (groupInfo != null && shiftInfo != null && sqlTrans != null)
{
strSql += "insert into ShiftTypeGroupItem_stgi" + Environment.NewLine;
strSql += "(stgi_GroupID, stgi_iSTMID)" + Environment.NewLine;
strSql += "values" + Environment.NewLine;
strSql += "('" + groupInfo.stg_GroupID.ToString() + "'," + shiftInfo.stm_iRecordID.ToString() + ")" + Environment.NewLine;
try
{
return ExecuteSQL(strSql, sqlTrans);
}
catch (Exception Ex)
{
throw Ex;
}
}
else
{
return false;
}
}
/// <summary>
/// 添加班次群組主檔信息
/// </summary>
/// <param name="info"></param>
/// <param name="sqlTrans"></param>
/// <returns></returns>
public bool AddShiftTypeGroupInfo(ShiftTypeGroup_stg_Info info, SqlTransaction sqlTrans, string groupType)
{
string strSql = string.Empty;
if (info != null && sqlTrans != null)
{
if (CheckIsExistShiftGroupName(info.stg_GroupID, info.stg_cGroupName))
{
//return false;
throw new Exception("已經存在的群組名稱！");
}
//允許同一日多個班次 Edit by ximonliang
//if (info.stg_dSpecifiedDate != null && CheckIsExistShiftGroupDate(info.stg_GroupID, info.stg_dSpecifiedDate.Value))
//{
// //return false;
// throw new Exception("已經存在的班次！");
//}
strSql += "insert into ShiftTypeGroup_stg" + Environment.NewLine;
strSql += "(stg_GroupID, stg_cGroupName, stg_cGroupType, stg_dSpecifiedDate)" + Environment.NewLine;
strSql += "values (newid(),N'" + DbHelperSQL.ProcessSQLAttack(info.stg_cGroupName.Trim()) + "','" + groupType + "'," + Environment.NewLine;
if (groupType == Common.DefineConstantValue.MachineShiftSettingTypeDefine.SpecificSetting)
{
if (info.stg_dSpecifiedDate != null)
{
strSql += "'" + info.stg_dSpecifiedDate.Value.ToString("yyyy-MM-dd") + "')";
}
else
{
strSql += "null)";
}
}
else
{
strSql += "null)";
}
try
{
return ExecuteSQL(strSql, sqlTrans);
}
catch (Exception Ex)
{
throw Ex;
}
}
else
{
return false;
}
}
/// <summary>
/// 添加普通機台班次群組信息
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public bool AddCommonShiftTypeGroupInfo(string GroupName)
{
bool lRes = false;
try
{
using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
{
SqlTransaction transaction = null;
conn.Open(); //開啟連接
transaction = conn.BeginTransaction(); //開啟事務
try
{
ShiftTypeGroup_stg_Info group = new ShiftTypeGroup_stg_Info();
group.stg_cGroupName = GroupName;
lRes = this._machineShiftSettingDAL.AddShiftTypeGroupInfo(group, transaction, Common.DefineConstantValue.MachineShiftSettingTypeDefine.CommonSetting);
}
catch (Exception Ex)
{
transaction.Rollback();
throw Ex;
}
finally
{
if (lRes)
transaction.Commit();
}
}
}
catch (Exception ex)
{
throw ex;
}
return lRes;
}
/// <summary>
/// 刪除群組機台關係
/// </summary>
/// <param name="group"></param>
/// <param name="sqlTrans"></param>
/// <returns></returns>
public bool DeleteShiftTypeGroupMachine(ShiftTypeGroup_stg_Info group, SqlTransaction sqlTrans)
{
string strSql = string.Empty;
if (group != null && sqlTrans != null)
{
strSql += "delete from MachineShiftTypeGroup_mstg" + Environment.NewLine;
strSql += "where mstg_GroupID='" + group.stg_GroupID + "'" + Environment.NewLine;
try
{
return ExecuteSQL(strSql, sqlTrans);
}
catch (Exception Ex)
{
throw Ex;
}
}
else
{
return false;
}
}
/// <summary>
/// 更新班次群組信息
/// </summary>
/// <param name="groupInfo">組別信息</param>
/// <param name="sqlTrans">事務</param>
/// <returns></returns>
public bool UpdateGroupInfo(ShiftTypeGroup_stg_Info groupInfo, SqlTransaction sqlTrans)
{
bool res = false;
if (groupInfo != null && sqlTrans != null)
{
string strGroupName = groupInfo.stg_cGroupName;
DateTime? dGroupDate = groupInfo.stg_dSpecifiedDate;
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("update ShiftTypeGroup_stg set ");
try
{
if (dGroupDate != null)
{
strSql.AppendLine("stg_dSpecifiedDate='" + dGroupDate.Value.ToString("yyyy-MM-dd") + "' ");
}
else
{
strSql.AppendLine("stg_dSpecifiedDate=null ");
}
if (strGroupName != null)
{
strSql.AppendLine(",stg_cGroupName = N'" + strGroupName.Trim() + "'");
}
strSql.AppendLine("where stg_GroupID = '" + groupInfo.stg_GroupID.ToString() + "'");
res = ExecuteSQL(strSql.ToString(), sqlTrans);
return res;
}
catch (Exception Ex)
{
throw Ex;
}
}
else
{
return false;
}
}
/// <summary>
/// 刪除機臺班次群組信息
/// </summary>
/// <returns></returns>
public bool DeleteShiftTypeGroupInfo(ShiftTypeGroup_stg_Info group)
{
bool lRes = false;
try
{
using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
{
SqlTransaction transaction = null;
conn.Open(); //開啟連接
transaction = conn.BeginTransaction(); //開啟事務
try
{
//刪除群組與機臺關係
lRes = this._machineShiftSettingDAL.DeleteShiftTypeGroupMachine(group, transaction);
if (lRes)
{
//刪除群組下所有班次
lRes = this._machineShiftSettingDAL.DeleteShiftTypeInfoByGroup(group, transaction);
if (lRes)
{
//刪除群組所有班次關係
lRes = this._machineShiftSettingDAL.DeleteGroupShiftTypeInfoByGroup(group, transaction);
if (lRes)
{
//刪除群組信息
lRes = this._machineShiftSettingDAL.DelShiftTypeGroupInfo(group, transaction);
}
}
}
}
catch (Exception Ex)
{
transaction.Rollback();
throw Ex;
}
finally
{
if (lRes)
transaction.Commit();
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return lRes;
}
/// <summary>
/// 添加群組班次信息
/// </summary>
/// <returns></returns>
public ReturnValueInfo AddGroupShiftTypeInfo(ShiftTypeGroup_stg_Info group, ShiftTypeMaster_stm_Info shiftType)
{
ReturnValueInfo checkInfo = new ReturnValueInfo();
bool lRes = false;
try
{
checkInfo = CheckGroupShiftTypeInfo(group, shiftType);
if (checkInfo.boolValue)
{
using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
{
SqlTransaction transaction = null;
conn.Open(); //開啟連接
transaction = conn.BeginTransaction(); //開啟事務
try
{
lRes = this._machineShiftSettingDAL.AddShiftTypeInfo(shiftType, transaction);
if (lRes)
{
lRes = this._machineShiftSettingDAL.AddGroupShiftTypeInfo(group, shiftType, transaction);
if (!lRes)
{
checkInfo.boolValue = false;
checkInfo.messageText = "添加群組班次關係失敗！";
transaction.Rollback();
}
}
else
{
checkInfo.boolValue = false;
checkInfo.messageText = "添加班次信息失敗！";
}
}
catch (Exception Ex)
{
transaction.Rollback();
throw Ex;
}
finally
{
if (lRes)
{
checkInfo.boolValue = true;
transaction.Commit();
}
}
}
}
else
{
}
}
catch (Exception Ex)
{
throw Ex;
}
return checkInfo;
}
private void btnGroupDel_Click(object sender, EventArgs e)
{
if (lvwGroupList.SelectedItems != null && lvwGroupList.SelectedItems.Count > 0)
{
DialogResult dr = MessageBox.Show("確定刪除班次群組信息？", "溫馨提示", MessageBoxButtons.YesNo);
if (dr == DialogResult.Yes)
{
try
{
this.m_selectedGroupID = new Guid(lvwGroupList.SelectedItems[0].Text);
ShiftTypeGroup_stg_Info group = new ShiftTypeGroup_stg_Info();
group.stg_GroupID = this.m_selectedGroupID;
bool IsUseShiftTypeInfo = this.m_machineShiftSettingBLL.CheckIsUserShiftTypeInfoByGroup(group.stg_GroupID);
if (!IsUseShiftTypeInfo)
{
bool isSuccess = this.m_machineShiftSettingBLL.DeleteShiftTypeGroupInfo(group);
if (isSuccess)
{
//取得新最群組列表并顯示
GetShiftTypeGroupInfo();
ShowShiftTypeGropInfo();
//重置可用及選中的機台列表
this.m_allMachineList.ForEach(t => t.mmt_lActive = false);
ShowMachineInfo();
//重置群組班次信息列表
this.m_allShiftTypeList = new List<ShiftTypeMaster_stm_Info>();
ShowGroupShiftTypeInfo();
}
else
{
base.ShowErrorMessage("刪除班次群組信息失敗！");
}
}
else
{
base.ShowWarningMessage("群組下班次信息已經被使用！");
}
}
catch (Exception Ex)
{
base.ShowErrorMessage(Ex.Message);
}
}
}
else
{
base.ShowWarningMessage("請選擇要刪除的班次群組信息！");
}
}
/// <summary>
/// 檢查班次的有效性
/// </summary>
/// <param name="group"></param>
/// <returns></returns>
public ReturnValueInfo CheckGroupShiftTypeInfo(ShiftTypeGroup_stg_Info group, ShiftTypeMaster_stm_Info shiftType)
{
ReturnValueInfo returnValue = new ReturnValueInfo();
//取消規則
returnValue.boolValue = true;
//List<ShiftTypeMaster_stm_Info> ShiftTypeInfo = GetGroupShiftTypeInfo(group.stg_GroupID);
//returnValue = CheckShiftInfoPart1(shiftType, ShiftTypeInfo);
//if (returnValue.boolValue)
//{
// returnValue = CheckShiftInfoPart2(shiftType, ShiftTypeInfo);
// //if (returnValue.boolValue)
// //{
// // returnValue = CheckShiftInfoPart3(shiftType, ShiftTypeInfo);
// //}
//}
return returnValue;
}
/// <summary>
/// 更新機台班次群組信息
/// </summary>
/// <param name="groupInfo">組別信息</param>
/// <returns></returns>
private ReturnValueInfo UpdateShiftTypeGroup(ShiftTypeGroup_stg_Info groupInfo)
{
ReturnValueInfo rvInfo = new ReturnValueInfo();
if (groupInfo.stg_GroupID != Guid.Empty)
{
try
{
using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
{
SqlTransaction transaction = null;
conn.Open(); //開啟連接
transaction = conn.BeginTransaction(); //開啟事務
try
{
if (this._machineShiftSettingDAL.CheckIsExistShiftGroupName(groupInfo.stg_GroupID, groupInfo.stg_cGroupName))
{
rvInfo.messageText = "保存失败，已存在相同的組別名稱，請重新輸入。";
return rvInfo;
}
if (groupInfo.stg_dSpecifiedDate != null && this._machineShiftSettingDAL.CheckIsExistShiftGroupDate(groupInfo.stg_GroupID, groupInfo.stg_dSpecifiedDate.Value))
{
rvInfo.messageText = "保存失败，已存在相同的組別日期，請重新輸入。";
return rvInfo;
}
rvInfo.boolValue = this._machineShiftSettingDAL.UpdateGroupInfo(groupInfo, transaction);
if (rvInfo.boolValue)
{
rvInfo.messageText = "保存成功。";
}
else
{
rvInfo.messageText = "保存失败。";
}
}
catch (Exception Ex)
{
transaction.Rollback();
throw Ex;
}
finally
{
if (rvInfo.boolValue)
transaction.Commit();
}
}
}
catch (Exception ex)
{
rvInfo.isError = true;
rvInfo.messageText = ex.Message;
}
}
else
{
rvInfo.messageText = "保存失败，組別ID不能為空值。";
}
return rvInfo;
}
/// <summary>
/// 更新機台班次群組信息
/// </summary>
/// <param name="strShiftTypeName">群組名稱</param>
/// <param name="dtSpeical">指定日期</param>
/// <returns></returns>
public ReturnValueInfo UpdateShiftTypeGroupInfo(Guid GroupID, string strGroupName, DateTime dtSpeical)
{
ShiftTypeGroup_stg_Info groupInfo = new ShiftTypeGroup_stg_Info();
groupInfo.stg_GroupID = GroupID;
groupInfo.stg_cGroupName = strGroupName;
groupInfo.stg_dSpecifiedDate = dtSpeical;
ReturnValueInfo rvInfo = UpdateShiftTypeGroup(groupInfo);
return rvInfo;
}
/// <summary>
/// 更新機台班次群組信息
/// </summary>
/// <param name="strShiftTypeName">群組名稱</param>
/// <returns></returns>
public ReturnValueInfo UpdateShiftTypeGroupInfo(Guid GroupID, string strGroupName)
{
ShiftTypeGroup_stg_Info groupInfo = new ShiftTypeGroup_stg_Info();
groupInfo.stg_GroupID = GroupID;
groupInfo.stg_cGroupName = strGroupName;
ReturnValueInfo rvInfo = UpdateShiftTypeGroup(groupInfo);
return rvInfo;
}
public ReturnValueInfo UpdateGroupShiftTypeInfo(Guid groupID, ShiftTypeMaster_stm_Info shiftType)
{
ReturnValueInfo checkInfo = new ReturnValueInfo();
bool lRes = false;
//bool chkIsUserShiftType = true;
try
{
ShiftTypeGroup_stg_Info group = new ShiftTypeGroup_stg_Info();
group.stg_GroupID = groupID;
checkInfo = CheckGroupShiftTypeInfo_ModifyCheck(group, shiftType);
if (checkInfo.boolValue)
{
checkInfo = this.CheckUseingShiftTypeUpdate(shiftType);
if (!checkInfo.boolValue)
{
using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
{
SqlTransaction transaction = null;
conn.Open(); //開啟連接
transaction = conn.BeginTransaction(); //開啟事務
try
{
lRes = this._machineShiftSettingDAL.UpdateShiftTypeInfo(shiftType, transaction);
if (!lRes)
{
checkInfo.boolValue = false;
checkInfo.messageText = "添加班次信息失敗！";
}
}
catch (Exception Ex)
{
transaction.Rollback();
throw Ex;
}
finally
{
if (lRes)
{
checkInfo.boolValue = true;
transaction.Commit();
}
}
}
}
else
{
checkInfo.boolValue = false;
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return checkInfo;
}
private void btnShiftAdd_Click(object sender, EventArgs e)
{
if (VaildAddShiftTypeInfo())
{
ShiftTypeGroup_stg_Info group = new ShiftTypeGroup_stg_Info();
group.stg_GroupID = this.m_selectedGroupID;
ShiftTypeMaster_stm_Info shiftType = new ShiftTypeMaster_stm_Info();
//shiftType.stm_cShiftName = txtcShiftTypeName.Text.Trim();
shiftType.stm_cShiftName = this.cboShiftTypeName.Text;
shiftType.stm_cShiftCode = this.cboShiftTypeName.SelectedValue.ToString();
shiftType.stm_cBeginTime = txtBeginTime.Text.Trim();
shiftType.stm_cEndTime = txtEndTime.Text.Trim();
shiftType.stm_cAdd = UserInformation.usm_cUserLoginID;
shiftType.stm_cLast = UserInformation.usm_cUserLoginID;
shiftType.stm_lIsAtive = true;
try
{
ReturnValueInfo returnInfo = this.m_machineShiftSettingBLL.AddGroupShiftTypeInfo(group, shiftType);
if (returnInfo.boolValue)
{
GetGroupShiftTypeInfo(this.m_selectedGroupID);
ShowGroupShiftTypeInfo();
//txtcShiftTypeName.Text = "";
cboShiftTypeName.SelectedIndex = -1;
txtBeginTime.Text = string.Empty;
txtEndTime.Text = string.Empty;
}
else
{
ShowWarningMessage(returnInfo.messageText);
}
}
catch (Exception Ex)
{
ShowErrorMessage(Ex.Message);
}
}
}
/// <summary>
/// 刪除群組內所有班次信息
/// </summary>
/// <param name="group"></param>
/// <param name="sqlTrans"></param>
/// <returns></returns>
public bool DeleteShiftTypeInfoByGroup(ShiftTypeGroup_stg_Info group, SqlTransaction sqlTrans)
{
string strSql = string.Empty;
if (group != null && group.stg_GroupID != Guid.Empty && sqlTrans != null)
{
strSql += "delete from ShiftTypeMaster_stm" + Environment.NewLine;
strSql += "where stm_iRecordID " + Environment.NewLine;
strSql += "in( " + Environment.NewLine;
strSql += "select stgi_iSTMID " + Environment.NewLine;
strSql += "from ShiftTypeGroupItem_stgi" + Environment.NewLine;
strSql += "where stgi_GroupID='" + group.stg_GroupID + "'" + Environment.NewLine;
strSql += ")" + Environment.NewLine;
try
{
return ExecuteSQL(strSql, sqlTrans);
}
catch (Exception Ex)
{
throw Ex;
}
}
else
{
return false;
}
}
/// <summary>
/// 刪除班次群組主檔信息
/// </summary>
/// <param name="info"></param>
/// <param name="sqlTrans"></param>
/// <returns></returns>
public bool DelShiftTypeGroupInfo(ShiftTypeGroup_stg_Info info, SqlTransaction sqlTrans)
{
string strSql = string.Empty;
if (info != null && sqlTrans != null)
{
strSql += "delete from ShiftTypeGroup_stg" + Environment.NewLine;
strSql += "where stg_GroupID='" + info.stg_GroupID.ToString() + "'" + Environment.NewLine;
try
{
return ExecuteSQL(strSql, sqlTrans);
}
catch (Exception Ex)
{
throw Ex;
}
}
else
{
return false;
}
}
/// <summary>
/// 檢查修改的班次的有效性
/// </summary>
/// <param name="group"></param>
/// <returns></returns>
public ReturnValueInfo CheckGroupShiftTypeInfo_ModifyCheck(ShiftTypeGroup_stg_Info group, ShiftTypeMaster_stm_Info shiftType)
{
ReturnValueInfo returnValue = new ReturnValueInfo();
returnValue.boolValue = true;
//取消檢查
//List<ShiftTypeMaster_stm_Info> ShiftTypeInfo = GetGroupShiftTypeInfo(group.stg_GroupID);
//returnValue = CheckShiftInfoPart1_ModifyCheck(shiftType, ShiftTypeInfo);
//修改時 取消24小時時長限制
//if (returnValue.boolValue)
//{
// returnValue = CheckShiftInfoPart2_ModifyCheck(shiftType, ShiftTypeInfo);
//}
return returnValue;
}
/// <summary>
/// 取得班次群組主檔列表信息
/// </summary>
/// <param name="groupType"></param>
/// <returns></returns>
public List<ShiftTypeGroup_stg_Info> GetShiftTypeGroupList(string groupType)
{
List<ShiftTypeGroup_stg_Info> ShiftTypeGroupList = new List<ShiftTypeGroup_stg_Info>();
string strSql = string.Empty;
strSql += "select stg_GroupID, stg_cGroupName,stg_cGroupType,stg_dSpecifiedDate" + Environment.NewLine;
strSql += "from ShiftTypeGroup_stg (NOLOCK)" + Environment.NewLine;
strSql += "where stg_cGroupType='" + groupType + "'" + Environment.NewLine;
strSql += "order by stg_cGroupName";
try
{
using (SqlDataReader reader = DbHelperSQL.ExecuteReader(strSql))
{
while (reader.Read())
{
ShiftTypeGroup_stg_Info groupInfo = new ShiftTypeGroup_stg_Info();
if (reader["stg_GroupID"] != null && reader["stg_GroupID"].ToString() != string.Empty)
{
groupInfo.stg_GroupID = new Guid(reader["stg_GroupID"].ToString());
}
if (reader["stg_cGroupName"] != null && reader["stg_cGroupName"].ToString() != string.Empty)
{
groupInfo.stg_cGroupName = reader["stg_cGroupName"].ToString();
}
if (reader["stg_cGroupType"] != null && reader["stg_cGroupType"].ToString() != string.Empty)
{
groupInfo.stg_cGroupType = reader["stg_cGroupType"].ToString();
}
if (reader["stg_dSpecifiedDate"] != null && reader["stg_dSpecifiedDate"].ToString() != string.Empty)
{
groupInfo.stg_dSpecifiedDate = Convert.ToDateTime(reader["stg_dSpecifiedDate"].ToString());
groupInfo.cSpecifiedDate = groupInfo.stg_dSpecifiedDate.Value.ToString("dd/MM/yyyy").Replace('-', '/');
}
ShiftTypeGroupList.Add(groupInfo);
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return ShiftTypeGroupList;
}
/// <summary>
/// 保存群組機台
/// </summary>
/// <returns></returns>
private bool SaveShiftTypeGroupMachineInfo()
{
List<MachineMaster_mmt_Info> selectedMachines = new List<MachineMaster_mmt_Info>();
ShiftTypeGroup_stg_Info group = new ShiftTypeGroup_stg_Info();
bool isSuccess = false;
foreach (ListViewItem selectData in lvwSelectMachine.Items)
{
MachineMaster_mmt_Info machine = new MachineMaster_mmt_Info();
machine.mmt_cMachineID = selectData.SubItems[(int)EnumSelectdMacInfoColumn.MacID].Text.Trim();
selectedMachines.Add(machine);
}
try
{
isSuccess = this.m_machineShiftSettingBLL.AddShiftTypeGroupMachineInfo(selectedMachines, this.m_selectedGroupID);
}
catch (Exception Ex)
{
ShowErrorMessage(Ex.Message);
}
return isSuccess;
}
