/// <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;
}
}
public ShiftTypeMaster_stm_Info DisplayRecord(Model.IModel.IModelObject KeyObject)
{
ShiftTypeMaster_stm_Info info = new ShiftTypeMaster_stm_Info();
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
ShiftTypeMaster_stm disTab = db.ShiftTypeMaster_stm.SingleOrDefault(t => t.stm_iRecordID == ((KeyObject) as ShiftTypeMaster_stm_Info).stm_iRecordID);
if (disTab != null)
{
info = Common.General.CopyObjectValue<ShiftTypeMaster_stm, ShiftTypeMaster_stm_Info>(disTab);
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return info;
}
public bool DeleteRecord(Model.IModel.IModelObject KeyObject)
{
bool isSuccess = false;
ShiftTypeMaster_stm_Info info = new ShiftTypeMaster_stm_Info();
info = KeyObject as ShiftTypeMaster_stm_Info;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
ShiftTypeMaster_stm delTab = db.ShiftTypeMaster_stm.SingleOrDefault(t => t.stm_iRecordID == info.stm_iRecordID);
if (delTab != null)
{
db.ShiftTypeMaster_stm.DeleteOnSubmit(delTab);
db.SubmitChanges();
isSuccess = true;
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return isSuccess;
}
/// <summary>
/// 檢查一 新班次不能與現在班次區間重復，並且開始時間要等於上一班次結束時間
/// </summary>
/// <param name="shiftType"></param>
/// <param name="ShiftTypeList"></param>
/// <returns></returns>
public ReturnValueInfo CheckShiftInfoPart1_ModifyCheck(ShiftTypeMaster_stm_Info shiftType, List<ShiftTypeMaster_stm_Info> ShiftTypeList)
{
ReturnValueInfo returnValue = new ReturnValueInfo();
if (shiftType != null)
{
if (ShiftTypeList != null && ShiftTypeList.Count > 0)
{
//新班次的開始時間
DateTime? beginT = ConvertToDateTime(shiftType.stm_cBeginTime.Trim());
//新班次的結束時間
DateTime? endT = ConvertToDateTime(shiftType.stm_cEndTime.Trim());
if (shiftType.stm_iRecordID == ShiftTypeList.FirstOrDefault().stm_iRecordID)
{
//修改 第一條班次信息
returnValue.boolValue = true;
return returnValue;
}
foreach (ShiftTypeMaster_stm_Info item in ShiftTypeList.Where(x => x.stm_iRecordID < shiftType.stm_iRecordID).OrderByDescending(t => Convert.ToInt32(t.stm_iRecordID)))
//foreach (ShiftTypeMaster_stm_Info item in ShiftTypeList)
{
//舊班次的開始時間
DateTime? recordB = ConvertToDateTime(item.stm_cBeginTime.Trim());
//舊班次的結束時間
DateTime? recordE = ConvertToDateTime(item.stm_cEndTime.Trim());
if (recordB.HasValue && recordE.HasValue)
{
if (beginT == recordE)
{
returnValue.boolValue = true;
}
else
{
//新班次開始時間必須等于上一班次的結束時間！
returnValue.boolValue = false;
returnValue.messageText = "新班次開始時間必須等于上一班次的結束時間！";
return returnValue;
}
}
else
{
//舊班次開始、結束時間有誤
returnValue.boolValue = false;
returnValue.messageText = "已有的班次開始、結束時間有誤！";
return returnValue;
}
//只取最后的班次判斷
break;
}
}
else
{
//沒有舊班次信息
returnValue.boolValue = true;
}
}
else
{
//新班次信息為空
returnValue.boolValue = false;
returnValue.messageText = "新班次信息為空!";
}
return returnValue;
}
/// <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="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;
}
private string GetShiftTypeMasterSQL(ShiftTypeMaster_stm_Info model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.stm_iRecordID != null)
{
strSql1.Append("stm_iRecordID,");
strSql2.Append("" + model.stm_iRecordID + ",");
}
if (model.stm_cShiftName != null)
{
strSql1.Append("stm_cShiftName,");
strSql2.Append("'" + model.stm_cShiftName + "',");
}
if (model.stm_cBeginTime != null)
{
strSql1.Append("stm_cBeginTime,");
strSql2.Append("'" + model.stm_cBeginTime + "',");
}
if (model.stm_cEndTime != null)
{
strSql1.Append("stm_cEndTime,");
strSql2.Append("'" + model.stm_cEndTime + "',");
}
if (model.stm_lIsAtive != null)
{
strSql1.Append("stm_lIsAtive,");
strSql2.Append("" + (model.stm_lIsAtive ? 1 : 0) + ",");
}
if (model.stm_cAdd != null)
{
strSql1.Append("stm_cAdd,");
strSql2.Append("'" + model.stm_cAdd + "',");
}
if (model.stm_dAddDate != null)
{
strSql1.Append("stm_dAddDate,");
strSql2.Append("'" + model.stm_dAddDate.ToString(this._sqlLiteDatetimeFormat) + "',");
}
if (model.stm_cLast != null)
{
strSql1.Append("stm_cLast,");
strSql2.Append("'" + model.stm_cLast + "',");
}
if (model.stm_dLastDate != null)
{
strSql1.Append("stm_dLastDate,");
strSql2.Append("'" + model.stm_dLastDate.ToString(this._sqlLiteDatetimeFormat) + "',");
}
strSql.Append("insert into ShiftTypeMaster_stm(");
strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
strSql.Append(")");
strSql.Append(" values (");
strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
strSql.Append(")");
return strSql.ToString();
}
/// <summary>
/// 更新班次信息
/// </summary>
/// <param name="info"></param>
/// <param name="sqlTrans"></param>
/// <returns></returns>
public bool UpdateShiftTypeInfo(ShiftTypeMaster_stm_Info info, SqlTransaction sqlTrans)
{
bool res = false;
if (info != null && sqlTrans != null)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("update ShiftTypeMaster_stm set ");
if (info.stm_cShiftName != null)
{
strSql.AppendLine(" stm_cShiftName=N'" + info.stm_cShiftName.Trim() + "'");
}
else
{
strSql.AppendLine(" stm_cShiftName=null");
}
if (info.stm_cBeginTime != null)
{
strSql.AppendLine(" ,stm_cBeginTime='" + info.stm_cBeginTime.Trim() + "'");
}
else
{
strSql.AppendLine(" ,stm_cBeginTime=null");
}
if (info.stm_cEndTime != null)
{
strSql.AppendLine(" ,stm_cEndTime='" + info.stm_cEndTime.Trim() + "'");
}
else
{
strSql.AppendLine(" ,stm_cEndTime=null");
}
if (!string.IsNullOrEmpty(info.stm_cShiftCode))
{
strSql.AppendLine(" ,stm_cShiftCode='" + info.stm_cShiftCode.Trim() + "'");
}
else
{
strSql.AppendLine(" ,stm_cShiftCode=''");
}
strSql.AppendLine(" ,stm_cLast='" + info.stm_cLast + "',stm_dLastDate=getdate() ");
strSql.AppendLine("where stm_iRecordID=" + info.stm_iRecordID.ToString());
res = ExecuteSQL(strSql.ToString(), sqlTrans);
return res;
}
else
{
return false;
}
}
void SetUIState()
{
switch (this.EditState)
{
case Common.DefineConstantValue.EditStateEnum.OE_ReaOnly:
this._objInfo = this.BaseParam as ShiftTypeMaster_stm_Info;
this.SysToolBar.BtnSave_IsEnabled = false;
this.SysToolBar.BtnSave_IsUsed = false;
this.SysToolBar.BtnExit_IsEnabled = true;
this.SysToolBar.BtnExit_IsUsed = true;
break;
case Common.DefineConstantValue.EditStateEnum.OE_Update:
this._objInfo = this.BaseParam as ShiftTypeMaster_stm_Info;
this.SysToolBar.BtnSave_IsEnabled = true;
this.SysToolBar.BtnSave_IsUsed = true;
this.SysToolBar.BtnExit_IsEnabled = true;
this.SysToolBar.BtnExit_IsUsed = true;
break;
case Common.DefineConstantValue.EditStateEnum.OE_Insert:
this._objInfo = new ShiftTypeMaster_stm_Info();
this.SysToolBar.BtnSave_IsEnabled = true;
this.SysToolBar.BtnSave_IsUsed = true;
this.SysToolBar.BtnExit_IsEnabled = true;
this.SysToolBar.BtnExit_IsUsed = true;
break;
}
}
/// <summary>
/// 獲取特別班次類型記錄
/// </summary>
/// <returns></returns>
public List<ShiftTypeMaster_stm_Info> GetSpecialShiftTypeMaster(string machineID)
{
try
{
List<ShiftTypeMaster_stm_Info> list = new List<ShiftTypeMaster_stm_Info>();
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("select");
strSql.AppendLine("stm_iRecordID,stm_cShiftName,stm_cBeginTime,stm_cEndTime,stm_lIsAtive,stm_cAdd,stm_dAddDate,stm_cLast,stm_dLastDate,stg_dSpecifiedDate ");
strSql.AppendLine("from MachineShiftTypeGroup_mstg ");
strSql.AppendLine("inner join ShiftTypeGroup_stg on stg_GroupID=mstg_GroupID and stg_cGroupType='" + Common.DefineConstantValue.MachineShiftSettingTypeDefine.SpecificSetting.Trim() + "' ");
strSql.AppendLine("inner join ShiftTypeGroupItem_stgi on stgi_GroupID=mstg_GroupID ");
strSql.AppendLine("inner join ShiftTypeMaster_stm on stgi_iSTMID=stm_iRecordID ");
strSql.AppendLine("where stm_lIsAtive=1 and mstg_cMachineID='" + machineID.Trim() + "'");
using (SqlDataReader sdr = DbHelperSQL.ExecuteReader(strSql.ToString()))
{
while (sdr.Read())
{
ShiftTypeMaster_stm_Info model = new ShiftTypeMaster_stm_Info();
if (sdr["stm_iRecordID"] != null && sdr["stm_iRecordID"].ToString() != "")
{
model.stm_iRecordID = int.Parse(sdr["stm_iRecordID"].ToString());
}
if (sdr["stm_cShiftName"] != null && sdr["stm_cShiftName"].ToString() != "")
{
model.stm_cShiftName = sdr["stm_cShiftName"].ToString();
}
if (sdr["stm_cBeginTime"] != null && sdr["stm_cBeginTime"].ToString() != "")
{
model.stm_cBeginTime = sdr["stm_cBeginTime"].ToString();
}
if (sdr["stm_cEndTime"] != null && sdr["stm_cEndTime"].ToString() != "")
{
model.stm_cEndTime = sdr["stm_cEndTime"].ToString();
}
if (sdr["stm_lIsAtive"] != null && sdr["stm_lIsAtive"].ToString() != "")
{
if ((sdr["stm_lIsAtive"].ToString() == "1") || (sdr["stm_lIsAtive"].ToString().ToLower() == "true"))
{
model.stm_lIsAtive = true;
}
else
{
model.stm_lIsAtive = false;
}
}
if (sdr["stm_cAdd"] != null && sdr["stm_cAdd"].ToString() != "")
{
model.stm_cAdd = sdr["stm_cAdd"].ToString();
}
if (sdr["stm_dAddDate"] != null && sdr["stm_dAddDate"].ToString() != "")
{
model.stm_dAddDate = DateTime.Parse(sdr["stm_dAddDate"].ToString());
}
if (sdr["stm_cLast"] != null && sdr["stm_cLast"].ToString() != "")
{
model.stm_cLast = sdr["stm_cLast"].ToString();
}
if (sdr["stm_dLastDate"] != null && sdr["stm_dLastDate"].ToString() != "")
{
model.stm_dLastDate = DateTime.Parse(sdr["stm_dLastDate"].ToString());
}
if (sdr["stg_dSpecifiedDate"] != null && sdr["stg_dSpecifiedDate"].ToString() != "")
{
model.ShiftDate = DateTime.Parse(sdr["stg_dSpecifiedDate"].ToString());
}
list.Add(model);
}
}
return list;
}
catch (Exception ex)
{
throw ex;
}
}
public List<ShiftTypeMaster_stm_Info> FindRecord(ShiftTypeMaster_stm_Info info)
{
List<ShiftTypeMaster_stm_Info> list = new List<ShiftTypeMaster_stm_Info>();
string sqlString = string.Empty;
string whereString = string.Empty;
sqlString = "SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine;
sqlString += "*" + Environment.NewLine;
sqlString += "FROM dbo.ShiftTypeMaster_stm" + Environment.NewLine;
whereString = "WHERE 1=1" + Environment.NewLine;
if (info.stm_iRecordID != 0)
{
whereString += "AND stm_iRecordID = " + info.stm_iRecordID.ToString() + Environment.NewLine;
}
if (info.stm_cShiftName != "")
{
whereString += "AND stm_cShiftName='" + info.stm_cShiftName + "' " + Environment.NewLine;
}
//if (info.cmt_cKey2 != "")
//{
// whereString += "AND cmt_cKey2='" + info.cmt_cKey2 + "' " + Environment.NewLine;
//}
IEnumerable<ShiftTypeMaster_stm_Info> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<ShiftTypeMaster_stm_Info>(sqlString + whereString, new object[] { });
if (infos != null)
{
list = infos.ToList<ShiftTypeMaster_stm_Info>();
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return list;
}
public bool UpdateRecord(ShiftTypeMaster_stm_Info infoObject)
{
bool isSuccess = false;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
ShiftTypeMaster_stm query = db.ShiftTypeMaster_stm.SingleOrDefault(t => t.stm_iRecordID == infoObject.stm_iRecordID);
if (query != null)
{
query.stm_cShiftName = infoObject.stm_cShiftName;
query.stm_cBeginTime = infoObject.stm_cBeginTime;
query.stm_cEndTime = infoObject.stm_cEndTime;
//*(No: )Modify By Leothlink Sunwind Lee (Date:2013/03/06)*/lIsDeleted改為lIsAtive
//query.stm_lIsDeleted = infoObject.stm_lIsDeleted;
query.stm_lIsAtive = infoObject.stm_lIsAtive;
//*(No: )End Modify By Leothlink Sunwind Lee (Date:2013/03/06)*/lIsDeleted改為lIsAtive
//query.stm_iSeq = infoObject.stm_iSeq;
query.stm_cLast = infoObject.stm_cLast;
query.stm_dLastDate = infoObject.stm_dLastDate;
db.SubmitChanges();
isSuccess = true;
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return isSuccess;
}
public bool IsExistRecord(object KeyObject)
{
ShiftTypeMaster_stm_Info info = new ShiftTypeMaster_stm_Info();
info = KeyObject as ShiftTypeMaster_stm_Info;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
ShiftTypeMaster_stm query = db.ShiftTypeMaster_stm.SingleOrDefault(t => t.stm_iRecordID == info.stm_iRecordID);
if (query != null)
{
return true;
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return false;
}
public bool InsertRecord(ShiftTypeMaster_stm_Info infoObject)
{
bool isSuccess = false;
ShiftTypeMaster_stm_Info info = new ShiftTypeMaster_stm_Info();
info = infoObject;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
ShiftTypeMaster_stm newTab = Common.General.CopyObjectValue<ShiftTypeMaster_stm_Info, ShiftTypeMaster_stm>(info);
db.ShiftTypeMaster_stm.InsertOnSubmit(newTab);
db.SubmitChanges();
isSuccess = true;
}
}
catch (Exception Ex)
{
throw Ex;
}
return isSuccess;
}
/// <summary>
/// 檢查二 群組下所有班次，工作時間總和不能大于24小時
/// </summary>
/// <param name="shiftType"></param>
/// <param name="ShiftTypeList"></param>
/// <returns></returns>
public ReturnValueInfo CheckShiftInfoPart2_ModifyCheck(ShiftTypeMaster_stm_Info shiftType, List<ShiftTypeMaster_stm_Info> ShiftTypeList)
{
ReturnValueInfo returnValue = new ReturnValueInfo();
if (shiftType != null)
{
//班次的開始時間
DateTime? beginT = ConvertToDateTime(shiftType.stm_cBeginTime.Trim());
//班次的結束時間
DateTime? endT = ConvertToDateTime(shiftType.stm_cEndTime.Trim());
if (beginT.HasValue && endT.HasValue)
{
//時間段跨天
if (beginT > endT)
{
endT = endT.Value.AddDays(1);
}
}
else
{
//班次開始、結束時間有誤
returnValue.boolValue = false;
returnValue.messageText = "新班次開始、結束時間有誤！";
return returnValue;
}
//上班時長
TimeSpan sp = new TimeSpan();
if (ShiftTypeList != null && ShiftTypeList.Count > 0)
{
#region 計算所以班次時長是否有超24小時
foreach (ShiftTypeMaster_stm_Info item in ShiftTypeList)
{
//舊班次的开始時間
DateTime? recordB = null;
//舊班次的結束時間
DateTime? recordE = null;
if (item.stm_iRecordID == shiftType.stm_iRecordID)
{
recordB = beginT;
recordE = endT;
}
else
{
recordB = ConvertToDateTime(item.stm_cBeginTime.Trim());
recordE = ConvertToDateTime(item.stm_cEndTime.Trim());
}
if (recordB.HasValue && recordE.HasValue)
{
//時間段跨天
if (recordB > recordE)
{
recordE = recordE.Value.AddDays(1);
}
//計算累計工作時間
sp += recordE.Value - recordB.Value;
}
else
{
//舊班次信息有誤
returnValue.boolValue = false;
returnValue.messageText = "舊班次信息有誤！";
return returnValue;
}
}
if (sp.TotalHours > 24)
{
//所有班次累計上班時長超過24小時
returnValue.boolValue = false;
returnValue.messageText = "所有班次累計上班時長超過24小時！";
return returnValue;
}
else
{
returnValue.boolValue = true;
return returnValue;
}
#endregion
}
else
{
#region 新增首條班次信息
if (beginT != null && endT != null && endT > beginT)
{
if (sp.TotalHours > 0 && sp.TotalHours < 24)
{
returnValue.boolValue = true;
return returnValue;
}
else
{
//新增班次 上班時長超過24小時
returnValue.boolValue = false;
returnValue.messageText = "新增班次上班時長超過24小時！";
return returnValue;
}
}
else
{
//開始時間大于結束時間
returnValue.boolValue = false;
returnValue.messageText = "開始時間大于結束時間！";
return returnValue;
}
#endregion
}
}
else
{
returnValue.boolValue = false;
returnValue.messageText = "新增班次信息為空！";
}
return returnValue;
}
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>
/// <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 btnShiftModify_Click(object sender, EventArgs e)
{
if (lvwGroupShiftDetail.SelectedItems.Count > 0)
{
if (VaildAddShiftTypeInfo())
{
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_cLast = UserInformation.usm_cUserLoginID;
try
{
shiftType.stm_iRecordID = int.Parse(lvwGroupShiftDetail.SelectedItems[0].Text);
ReturnValueInfo returnInfo = this.m_machineShiftSettingBLL.UpdateGroupShiftTypeInfo(this.m_selectedGroupID, 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);
}
}
}
else
{
ShowWarningMessage("請先選擇要修改的班次信息！");
}
}
/// <summary>
/// 取得單筆班次信息
/// </summary>
/// <param name="shiftID"></param>
/// <returns></returns>
public ShiftTypeMaster_stm_Info GetShiftTypeInfo(Int32 shiftID)
{
ShiftTypeMaster_stm_Info shiftType = new ShiftTypeMaster_stm_Info();
string strSql = string.Empty;
strSql += "select *" + Environment.NewLine;
strSql += "from ShiftTypeMaster_stm" + Environment.NewLine;
strSql += "where stm_iRecordID=" + shiftID + Environment.NewLine;
try
{
using (SqlDataReader reader = DbHelperSQL.ExecuteReader(strSql))
{
while (reader.Read())
{
if (reader["stm_iRecordID"] != null && reader["stm_iRecordID"].ToString() != string.Empty)
{
shiftType.stm_iRecordID = Convert.ToInt32(reader["stm_iRecordID"].ToString());
}
if (reader["stm_cShiftName"] != null && reader["stm_cShiftName"].ToString() != string.Empty)
{
shiftType.stm_cShiftName = reader["stm_cShiftName"].ToString();
}
if (reader["stm_cBeginTime"] != null && reader["stm_cBeginTime"].ToString() != string.Empty)
{
shiftType.stm_cBeginTime = reader["stm_cBeginTime"].ToString();
}
if (reader["stm_cEndTime"] != null && reader["stm_cEndTime"].ToString() != string.Empty)
{
shiftType.stm_cEndTime = reader["stm_cEndTime"].ToString();
}
if (reader["stm_lIsAtive"] != null && reader["stm_lIsAtive"].ToString() != string.Empty)
{
shiftType.stm_lIsAtive = (reader["stm_lIsAtive"].ToString().ToUpper() == "TRUE" ? true : false);
}
break;
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return shiftType;
}
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;
}
/// <summary>
/// 添加班次信息
/// </summary>
/// <param name="info"></param>
/// <param name="sqlTrans"></param>
/// <returns></returns>
public bool AddShiftTypeInfo(ShiftTypeMaster_stm_Info info, SqlTransaction sqlTrans)
{
string strSql = string.Empty;
int iRes = 0;
if (info != null && sqlTrans != null)
{
strSql += "insert into ShiftTypeMaster_stm" + Environment.NewLine;
strSql += "(stm_cShiftName, stm_cBeginTime, stm_cEndTime, stm_lIsAtive, stm_cAdd, stm_dAddDate, stm_cLast, stm_dLastDate,stm_cShiftCode)" + Environment.NewLine;
strSql += "values" + Environment.NewLine;
strSql += "(N'" + DbHelperSQL.ProcessSQLAttack(info.stm_cShiftName.Trim()) + "','" + DbHelperSQL.ProcessSQLAttack(info.stm_cBeginTime.Trim()) + "','" + DbHelperSQL.ProcessSQLAttack(info.stm_cEndTime.Trim()) + "',1,'" + info.stm_cAdd + "',getdate(),'" + info.stm_cLast + "',getdate()," + DbHelperSQL.ProcessSQLAttack(info.stm_cShiftCode.Trim()) + ")" + Environment.NewLine;
strSql += "select @@identity";
try
{
//int iRes = DbHelperSQL.ExecuteNonQuery(sqlTrans, CommandType.Text, strSql.ToString());
SqlDataReader reader = DbHelperSQL.ExecuteReader(strSql);
while (reader.Read())
{
iRes = Convert.ToInt32(reader[0]);
break;
}
if (iRes > 0)
{
info.stm_iRecordID = iRes;
return true;
}
else
{
return false;
}
}
catch (Exception Ex)
{
throw Ex;
}
}
else
{
return false;
}
}
private ReturnValueInfo CheckUseingShiftTypeUpdate(ShiftTypeMaster_stm_Info newShiftType)
{
ReturnValueInfo returnValue = new ReturnValueInfo();
returnValue.boolValue = false;
//檢查班次是否已被使用
returnValue.boolValue = this._machineShiftSettingDAL.CheckIsUseShiftTypeInfo(newShiftType.stm_iRecordID);
if (returnValue.boolValue)
{
ShiftTypeMaster_stm_Info oldShiftTypeInfo = this._machineShiftSettingDAL.GetShiftTypeInfo(newShiftType.stm_iRecordID);
if (oldShiftTypeInfo != null)
{
//班次已被使用，不能修改開始、結束時間
if (GetDateValue(oldShiftTypeInfo.stm_cBeginTime.Trim()) == GetDateValue(newShiftType.stm_cBeginTime.Trim()) && GetDateValue(oldShiftTypeInfo.stm_cEndTime.Trim()) == GetDateValue(newShiftType.stm_cEndTime.Trim()))
{
returnValue.boolValue = false;
}
else
{
returnValue.boolValue = true;
returnValue.messageText = "班次信息已經被使用，不能修改開始時間及結束時間。";
}
}
}
return returnValue;
}
/// <summary>
/// 取得群組班次信息
/// </summary>
/// <returns></returns>
public List<ShiftTypeMaster_stm_Info> GetGroupShiftTypeInfo(Guid groupID)
{
List<ShiftTypeMaster_stm_Info> shiftTypeList = new List<ShiftTypeMaster_stm_Info>();
if (groupID != Guid.Empty)
{
string strSql = string.Empty;
strSql += "select stm_iRecordID, stm_cShiftName,stm_cBeginTime,stm_cEndTime,stm_lIsAtive,stm_cShiftCode " + Environment.NewLine;
strSql += "from ShiftTypeGroupItem_stgi" + Environment.NewLine;
strSql += "left join ShiftTypeMaster_stm" + Environment.NewLine;
strSql += "on stm_iRecordID=stgi_iSTMID" + Environment.NewLine;
strSql += "where stgi_GroupID='" + groupID.ToString() + "'" + Environment.NewLine;
try
{
using (SqlDataReader reader = DbHelperSQL.ExecuteReader(strSql))
{
while (reader.Read())
{
ShiftTypeMaster_stm_Info shiftType = new ShiftTypeMaster_stm_Info();
if (reader["stm_iRecordID"] != null && reader["stm_iRecordID"].ToString() != string.Empty)
{
shiftType.stm_iRecordID = Convert.ToInt32(reader["stm_iRecordID"].ToString());
}
if (reader["stm_cShiftName"] != null && reader["stm_cShiftName"].ToString() != string.Empty)
{
shiftType.stm_cShiftName = reader["stm_cShiftName"].ToString();
}
if (reader["stm_cBeginTime"] != null && reader["stm_cBeginTime"].ToString() != string.Empty)
{
shiftType.stm_cBeginTime = reader["stm_cBeginTime"].ToString();
}
if (reader["stm_cEndTime"] != null && reader["stm_cEndTime"].ToString() != string.Empty)
{
shiftType.stm_cEndTime = reader["stm_cEndTime"].ToString();
}
if (reader["stm_lIsAtive"] != null && reader["stm_lIsAtive"].ToString() != string.Empty)
{
shiftType.stm_lIsAtive = (reader["stm_lIsAtive"].ToString().ToUpper() == "TRUE" ? true : false);
}
if (reader["stm_cShiftCode"] != null && reader["stm_cShiftCode"].ToString() != string.Empty)
{
shiftType.stm_cShiftCode = reader["stm_cShiftCode"].ToString();
}
shiftTypeList.Add(shiftType);
}
}
}
catch (Exception Ex)
{
throw Ex;
}
}
return shiftTypeList;
}
/// <summary>
/// 班次類型主檔
/// </summary>
/// <returns></returns>
public List<ShiftTypeMaster_stm_Info> GetShiftTypeMaster()
{
try
{
List<ShiftTypeMaster_stm_Info> list = new List<ShiftTypeMaster_stm_Info>();
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("select");
strSql.AppendLine("stm_iRecordID,stm_cShiftName,stm_cBeginTime,stm_cEndTime,stm_lIsAtive,stm_cAdd,stm_dAddDate,stm_cLast,stm_dLastDate ");
strSql.AppendLine("from ShiftTypeMaster_stm ");
using (SQLiteDataReader sdr = DbHelperSQLite.ExecuteReader(strSql.ToString()))
{
while (sdr.Read())
{
ShiftTypeMaster_stm_Info model = new ShiftTypeMaster_stm_Info();
if (sdr["stm_iRecordID"] != null && sdr["stm_iRecordID"].ToString() != "")
{
model.stm_iRecordID = int.Parse(sdr["stm_iRecordID"].ToString());
}
if (sdr["stm_cShiftName"] != null && sdr["stm_cShiftName"].ToString() != "")
{
model.stm_cShiftName = sdr["stm_cShiftName"].ToString();
}
if (sdr["stm_cBeginTime"] != null && sdr["stm_cBeginTime"].ToString() != "")
{
model.stm_cBeginTime = sdr["stm_cBeginTime"].ToString();
}
if (sdr["stm_cEndTime"] != null && sdr["stm_cEndTime"].ToString() != "")
{
model.stm_cEndTime = sdr["stm_cEndTime"].ToString();
}
if (sdr["stm_lIsAtive"] != null && sdr["stm_lIsAtive"].ToString() != "")
{
if ((sdr["stm_lIsAtive"].ToString() == "1") || (sdr["stm_lIsAtive"].ToString().ToLower() == "true"))
{
model.stm_lIsAtive = true;
}
else
{
model.stm_lIsAtive = false;
}
}
if (sdr["stm_cAdd"] != null && sdr["stm_cAdd"].ToString() != "")
{
model.stm_cAdd = sdr["stm_cAdd"].ToString();
}
if (sdr["stm_dAddDate"] != null && sdr["stm_dAddDate"].ToString() != "")
{
model.stm_dAddDate = DateTime.Parse(sdr["stm_dAddDate"].ToString());
}
if (sdr["stm_cLast"] != null && sdr["stm_cLast"].ToString() != "")
{
model.stm_cLast = sdr["stm_cLast"].ToString();
}
if (sdr["stm_dLastDate"] != null && sdr["stm_dLastDate"].ToString() != "")
{
model.stm_dLastDate = DateTime.Parse(sdr["stm_dLastDate"].ToString());
}
list.Add(model);
}
}
return list;
}
catch (Exception ex)
{
throw ex;
}
}
