public List<ShiftTypeMaster_stm_Info> CboShiftType()
{
//Add by peizhiwu
List<ShiftTypeMaster_stm_Info> list = new List<ShiftTypeMaster_stm_Info>();
string strSQL = string.Empty;
strSQL = " SELECT stm_iRecordID,stm_cShiftName,stm_cBeginTime,stm_cEndTime,stm_lIsAtive,stm_cAdd,stm_dAddDate,stm_cLast,stm_dLastDate " + Environment.NewLine;
strSQL += " FROM ShiftTypeMaster_stm WHERE stm_lIsAtive = 1 " + Environment.NewLine;
IEnumerable<ShiftTypeMaster_stm_Info> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<ShiftTypeMaster_stm_Info>(strSQL, new object[] { });
if (infos != null)
{
list = infos.ToList<ShiftTypeMaster_stm_Info>();
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return list;
}
/// <summary>
/// 取得日志
/// </summary>
/// <param name="p_strIP">IP</param>
/// <param name="p_strSystemName">系统名</param>
/// <param name="p_strModel">模组</param>
/// <param name="p_strType">类型</param>
/// <param name="p_strOperator">操作人</param>
/// <returns></returns>
public List<LogDetail_lgd_Info> GetLogs(string p_strIP, string p_strSystemName, string p_strModel, string p_strType, string p_strOperator)
{
StringBuilder l_strSQL = new StringBuilder();
l_strSQL.AppendLine("Select Top 200 lgd_iID,");
l_strSQL.AppendLine("lgd_cIpAddr,");
l_strSQL.AppendLine("lgd_cSysName,");
l_strSQL.AppendLine("lgd_cClassMethodName,");
l_strSQL.AppendLine("lgd_cLogType,");
l_strSQL.AppendLine("lgd_cLogMessage,");
l_strSQL.AppendLine("lgd_cRemark,");
l_strSQL.AppendLine("lgd_cOperator,");
l_strSQL.AppendLine("lgd_dOperateDateTime");
l_strSQL.AppendLine("From LogDetail_lgd");
l_strSQL.AppendLine("Where 1=1");
if (p_strIP != "")
{
l_strSQL.AppendLine("And lgd_cIpAddr = '" + p_strIP + "'");
}
if (p_strSystemName != "")
{
l_strSQL.AppendLine("And lgd_cSysName = '" + p_strSystemName + "'");
}
if (p_strModel != "")
{
l_strSQL.AppendLine("And lgd_cClassMethodName = '" + p_strModel + "'");
}
if (p_strType != "")
{
l_strSQL.AppendLine("And lgd_cLogType = '" + p_strType + "'");
}
if (p_strOperator != "")
{
l_strSQL.AppendLine("And lgd_cOperator = '" + p_strOperator + "'");
}
l_strSQL.AppendLine("Order By lgd_dOperateDateTime Desc");
using (MainDBDataContext db = new MainDBDataContext())
{
return db.ExecuteQuery<LogDetail_lgd_Info>(l_strSQL.ToString(), new object[] { }).ToList();
}
}
public List<WorkGroupTypeMaster_wgt_Info> FindRecord(WorkGroupTypeMaster_wgt_Info info)
{
List<WorkGroupTypeMaster_wgt_Info> list = new List<WorkGroupTypeMaster_wgt_Info>();
string sqlString = string.Empty;
string whereString = string.Empty;
sqlString = "SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine;
sqlString += "*" + Environment.NewLine;
sqlString += "FROM dbo.WorkGroupTypeMaster_wgt" + Environment.NewLine;
whereString = "WHERE 1=1" + Environment.NewLine;
if (info.wgt_cWGTName != "")
{
whereString += "AND wgt_cWGTName='" + info.wgt_cWGTName + "' " + Environment.NewLine;
}
if (info.wgt_cDescription != "")
{
whereString += "AND wgt_cDescription='" + info.wgt_cDescription + "' " + Environment.NewLine;
}
IEnumerable<WorkGroupTypeMaster_wgt_Info> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<WorkGroupTypeMaster_wgt_Info>(sqlString + whereString, new object[] { });
if (infos != null)
{
list = infos.ToList<WorkGroupTypeMaster_wgt_Info>();
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return list;
}
List<StructureMaster_stt_Info> StructureGetSub(StructureMaster_stt_Info objInfo)
{
//return new List<StructureMaster_stt_Info>();
List<StructureMaster_stt_Info> list = new List<StructureMaster_stt_Info>();
string sqlString = string.Empty;
string whereString = string.Empty;
sqlString = "SELECT " + Environment.NewLine;
sqlString += "*" + Environment.NewLine;
sqlString += "FROM dbo.StructureMaster_stt" + Environment.NewLine;
whereString = "WHERE 1=1" + Environment.NewLine;
//if (objInfo.stt_cKey1 != new Guid())
//{
// whereString += "AND stt_cKey1='" + objInfo.stt_cKey1 + "' " + Environment.NewLine;
//}
if (objInfo.stt_cKey2 != new Guid())
{
whereString += "AND stt_cKey2='" + objInfo.stt_cKey2 + "' " + Environment.NewLine;
}
if (objInfo.stt_cKey3 != new Guid())
{
whereString += "AND stt_cKey3='" + objInfo.stt_cKey3 + "' " + Environment.NewLine;
}
IEnumerable<StructureMaster_stt_Info> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<StructureMaster_stt_Info>(sqlString + whereString, new object[] { });
if (infos != null)
{
list = infos.ToList<StructureMaster_stt_Info>();
foreach (StructureMaster_stt_Info SubItem in list)
{
SubItem.objSubStructureList = StructureGetSub(SubItem);
}
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return list;
}
public List<MaintainPlanView> SearchRecord(MaintainPlanView searchInfo)
{
List<MaintainPlanView> listReturn = new List<MaintainPlanView>();
try
{
MaintainPlanView srmSearchInfo = searchInfo as MaintainPlanView;
StringBuilder sbSQL = new StringBuilder();
//sbSQL.AppendLine("select * from dbo.MaintainPlan_mtp where 1=1 ");
sbSQL.AppendLine("select cmt_cRemark as mtp_cMachineModelName, * from dbo.MaintainPlan_mtp "
+ " join dbo.MaintainOperationMaster_mom "
+ " on mtp_iMOMID=mom_iRecordID "
+ "left join CodeMaster_cmt on cmt_cKey1='" + Common.DefineConstantValue.CodeMasterDefine.KEY1_TYPEVALUE + "' and cmt_cKey2='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_MACHINEMODEL + "' and cmt_cValue=mtp_cMachineModel where 1=1");
StringBuilder sbWhere = new StringBuilder();
if (!String.IsNullOrEmpty(searchInfo.mom_cOperationName))
{
sbWhere.AppendLine(" and mom_cMachineModel ='" + searchInfo.mom_cOperationName + "' ");
}
sbSQL.Append(sbWhere);
IEnumerable<MaintainPlanView> infos = null;
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<MaintainPlanView>(sbSQL.ToString(), new object[] { });
if (infos != null)
{
listReturn = infos.ToList();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listReturn;
}
private Sys_RoleMaster_rlm_Info FindUserRole(Sys_RoleMaster_rlm_Info info)
{
string sqlString = string.Empty;
sqlString += "SELECT usm_cUserLoginID,usm_cChaName " + Environment.NewLine;
sqlString += "FROM Sys_UserMaster_usm " + Environment.NewLine;
sqlString += "LEFT JOIN Sys_UserRoles_usr" + Environment.NewLine;
sqlString += "ON usm_cUserLoginID=usr_cUserLoginID" + Environment.NewLine;
sqlString += "LEFT JOIN Sys_RoleMaster_rlm" + Environment.NewLine;
sqlString += "ON rlm_cRoleID=usr_cRoleID WHERE rlm_cRoleID='" + info.rlm_cRoleID + "'";
IEnumerable<Sys_UserMaster_usm_Info> infos = null;
List<Sys_UserMaster_usm_Info> infoList = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<Sys_UserMaster_usm_Info>(sqlString, new object[] { });
if (infos != null)
{
infoList = infos.ToList<Sys_UserMaster_usm_Info>();
}
foreach (Sys_UserMaster_usm_Info t in infoList)
{
info.userMasterList.Add(t);
}
return info;
}
}
catch (Exception Ex)
{
throw Ex;
}
}
public List<Sys_RoleMaster_rlm_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
{
string sqlString = string.Empty;
string whereString = string.Empty;
sqlString = "SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine;
sqlString += " rlm_iRecordID," + Environment.NewLine;
sqlString += " rlm_cRoleID," + Environment.NewLine;
sqlString += " rlm_cRoleDesc," + Environment.NewLine;
sqlString += " rlm_cAdd," + Environment.NewLine;
sqlString += " rlm_dAddDate," + Environment.NewLine;
sqlString += " rlm_cLast," + Environment.NewLine;
sqlString += " rlm_dLastDate " + Environment.NewLine;
sqlString += " FROM Sys_RoleMaster_rlm" + Environment.NewLine;
Sys_RoleMaster_rlm_Info info = null;
info = searchCondition as Sys_RoleMaster_rlm_Info;
if (info != null)
{
whereString = " WHERE 1=1 ";
if (info.rlm_iRecordID > 0)
{
whereString += "AND rlm_iRecordID = " + info.rlm_iRecordID.ToString().Trim() + " ";
}
else
{
if (info.rlm_cRoleID.Trim() != "")
{
if (info.rlm_cRoleID.ToString().Contains("*") || info.rlm_cRoleID.ToString().Contains("?"))
{
whereString += " AND rlm_cRoleID LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.rlm_cRoleID) + "'";
}
else
{
whereString += "AND rlm_cRoleID = N'" + info.rlm_cRoleID.ToString().Trim() + "'";
}
}
}
}
sqlString += whereString;
IEnumerable<Sys_RoleMaster_rlm_Info> infos = null;
List<Sys_RoleMaster_rlm_Info> infoList = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<Sys_RoleMaster_rlm_Info>(sqlString, new object[] { });
if (infos != null)
{
infoList = infos.ToList<Sys_RoleMaster_rlm_Info>();
}
foreach (Sys_RoleMaster_rlm_Info roleInfo in infoList)
{
List<Sys_UserRoles_usr> listUR = db.Sys_UserRoles_usr.Where(x => x.usr_cRoleID == roleInfo.rlm_cRoleID).ToList();
if (listUR != null)
{
roleInfo.userMasterList = new List<Sys_UserMaster_usm_Info>();
foreach (Sys_UserRoles_usr urItem in listUR)
{
List<Sys_UserMaster_usm> listUser = db.Sys_UserMaster_usm.Where(x => x.usm_cUserLoginID == urItem.usr_cUserLoginID).ToList();
if (listUser != null && listUser.Count > 0)
{
Sys_UserMaster_usm_Info userInfo = Common.General.CopyObjectValue<Sys_UserMaster_usm, Sys_UserMaster_usm_Info>(listUser[0]);
if (userInfo != null)
{
roleInfo.userMasterList.Add(userInfo);
}
}
}
}
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return infoList;
}
public List<MixMaterialDetail_mmdl_Info> SearchRecords(IModelObject searchCondition)
{
List<MixMaterialDetail_mmdl_Info> list = new List<MixMaterialDetail_mmdl_Info>();
MixMaterialDetail_mmdl_Info info = new MixMaterialDetail_mmdl_Info();
info = searchCondition as MixMaterialDetail_mmdl_Info;
StringBuilder sqlString = new StringBuilder();
sqlString.AppendLine("SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString());
sqlString.AppendLine("*");
sqlString.AppendLine("FROM MixMaterialDetail_mmdl ");
sqlString.AppendLine("WHERE 1=1 --And mlpf_lIsDeleted=0");
if (info.mmdl_cMaterialCode != "" && info.mmdl_cMaterialCode != null)
{
sqlString.AppendLine("AND mmdl_cMaterialCode='" + info.mmdl_cMaterialCode + "'");
}
if (info.mmdl_cMaterialGroupNo != "" && info.mmdl_cMaterialGroupNo != null)
{
sqlString.AppendLine("AND mmdl_cMaterialGroupNo='" + info.mmdl_cMaterialGroupNo + "'");
}
IEnumerable<MixMaterialDetail_mmdl_Info> infos = null;
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<MixMaterialDetail_mmdl_Info>(sqlString.ToString(), new object[] { });
if (infos != null)
{
list = infos.ToList<MixMaterialDetail_mmdl_Info>();
}
}
return list;
}
/// <summary>
/// 獲得符合日期的班次內工程信息 by SIMON
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private List<ComboboxDataInfo> GetShirtProjList(ProjectStopRecord_psrd_Info query)
{
List<ComboboxDataInfo> infoList = new List<ComboboxDataInfo>();
if (query != null)
{
string strSQL = string.Empty;
strSQL += "select Convert(nvarchar(max), spl_RecordID) as ValueMember,swl_cItemName as DisplayMember" + Environment.NewLine;
strSQL += "from dbo.ShiftProjList_spl" + Environment.NewLine;
strSQL += "left join dbo.PrintProject_ppj" + Environment.NewLine;
strSQL += "on spl_PPJID=ppj_RecordID" + Environment.NewLine;
strSQL += "left join dbo.ScheduleProjList_swl" + Environment.NewLine;
strSQL += "on ppj_SWLID=swl_RecordID" + Environment.NewLine;
strSQL += "where" + Environment.NewLine;
strSQL += "((spl_dBeginTime<='" + query.psrd_dBeginTime.Value.ToString("yyyy-MM-dd HH:mm") + "' and spl_dEndTime >='" + query.psrd_dBeginTime.Value.ToString("yyyy-MM-dd HH:mm") + "' )" + Environment.NewLine;
strSQL += "or" + Environment.NewLine;
strSQL += "(spl_dBeginTime<='" + query.psrd_dEndTime.Value.ToString("yyyy-MM-dd HH:mm") + "' and spl_dEndTime >='" + query.psrd_dEndTime.Value.ToString("yyyy-MM-dd HH:mm") + "' ))" + Environment.NewLine;
IEnumerable<ComboboxDataInfo> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<ComboboxDataInfo>(strSQL, new object[] { });
if (infos != null)
{
foreach (ComboboxDataInfo item in infos)
{
infoList.Add(item);
}
}
}
}
catch (Exception Ex)
{
throw Ex;
}
}
return infoList;
}
StopReasonMaster_srm_Info SearchRecord(int iRecordID)
{
try
{
StopReasonMaster_srm_Info srmRes = null;
List<StopReasonMaster_srm_Info> listReturn = new List<StopReasonMaster_srm_Info>();
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString());
sbSQL.AppendLine("*");
sbSQL.AppendLine("FROM dbo.StopReasonMaster_srm");
sbSQL.AppendLine("WHERE 1=1");
sbSQL.AppendLine("AND srm_iRecordID = " + iRecordID.ToString());
string strSQL = sbSQL.ToString();
IEnumerable<StopReasonMaster_srm_Info> infos = null;
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<StopReasonMaster_srm_Info>(strSQL, new object[] { });
if (infos != null)
{
listReturn = infos.ToList();
if (listReturn != null && listReturn.Count > 0)
{
srmRes = listReturn[0];
}
}
}
return srmRes;
}
catch (Exception ex)
{ throw ex; }
}
public List<PrepareProjectMaster_ppm_Info> GetAllProjects()
{
//throw new NotImplementedException();
List<PrepareProjectMaster_ppm_Info> list = new List<PrepareProjectMaster_ppm_Info>();
PrepareProjectMaster_ppm_Info info = new PrepareProjectMaster_ppm_Info();
//info = searchCondition as PrepareProjectMaster_ppm_Info;
StringBuilder sqlString = new StringBuilder();
sqlString.AppendLine("SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString());
sqlString.AppendLine("*,isnull(cmt1.cmt_cRemark,'') as ppm_cMachineTypeName,isnull(cmt2.cmt_cRemark,'') as ppm_cItemTypeName");
sqlString.AppendLine("FROM dbo.PrepareProjectMaster_ppm");
sqlString.AppendLine("LEFT JOIN CodeMaster_cmt as cmt1");
sqlString.AppendLine("on ppm_cMachineType=cmt1.cmt_cValue and cmt1.cmt_cKey1='" + Common.DefineConstantValue.CodeMasterDefine.KEY1_TYPEVALUE + "' and cmt1.cmt_cKey2='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_MACHINEMODEL + "'");
sqlString.AppendLine("LEFT JOIN CodeMaster_cmt as cmt2");
sqlString.AppendLine("on ppm_cItemType=cmt2.cmt_cValue and cmt2.cmt_cKey1='" + Common.DefineConstantValue.CodeMasterDefine.KEY1_TYPEVALUE + "' and cmt2.cmt_cKey2='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_PREPARETYPE + "'");
sqlString.AppendLine("WHERE 1=1");
sqlString.AppendLine(" order by ppm_iSeq");
IEnumerable<PrepareProjectMaster_ppm_Info> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<PrepareProjectMaster_ppm_Info>(sqlString.ToString(), new object[] { });
if (infos != null)
{
list = infos.ToList<PrepareProjectMaster_ppm_Info>();
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return list;
}
/// <summary>
/// 搜索多條記錄
/// </summary>
/// <param name="searchCondition"></param>
/// <returns></returns>
/// <remarks>Create By Leothlink TonyWu On 01/02/2013</remarks>
public List<PrepareProjectMaster_ppm_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
{
List<PrepareProjectMaster_ppm_Info> list = new List<PrepareProjectMaster_ppm_Info>();
PrepareProjectMaster_ppm_Info info = new PrepareProjectMaster_ppm_Info();
info = searchCondition as PrepareProjectMaster_ppm_Info;
StringBuilder sqlString = new StringBuilder();
sqlString.AppendLine("SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString());
sqlString.AppendLine("*,cmt1.cmt_cRemark as ppm_cMachineTypeName,cmt2.cmt_cRemark as ppm_cItemTypeName");
sqlString.AppendLine("FROM dbo.PrepareProjectMaster_ppm");
sqlString.AppendLine("LEFT JOIN CodeMaster_cmt as cmt1");
sqlString.AppendLine("on ppm_cMachineType=cmt1.cmt_cValue and cmt1.cmt_cKey1='" + Common.DefineConstantValue.CodeMasterDefine.KEY1_TYPEVALUE + "' and cmt1.cmt_cKey2='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_MACHINEMODEL + "'");
sqlString.AppendLine("LEFT JOIN CodeMaster_cmt as cmt2");
sqlString.AppendLine("on ppm_cItemType=cmt2.cmt_cValue and cmt2.cmt_cKey1='" + Common.DefineConstantValue.CodeMasterDefine.KEY1_TYPEVALUE + "' and cmt2.cmt_cKey2='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_PREPARETYPE + "'");
sqlString.AppendLine("WHERE 1=1");
if (info.ppm_cItemCode != "")
{
sqlString.AppendLine("AND ppm_cItemCode='" + info.ppm_cItemCode + "'");
}
if (info.ppm_cMachineType != "")
{
sqlString.AppendLine("AND ppm_cMachineType='" + info.ppm_cMachineType + "'");
}
if (info.ppm_cItemType != "")
{
sqlString.AppendLine("AND ppm_cItemType='" + info.ppm_cItemType + "'");
}
if (info.ppm_cItemName != "")
{
sqlString.AppendLine("AND ppm_cItemName Like N'" + info.ppm_cItemName.ToString().Replace("*", "%").Replace("?", "_") + "'");
if (info.ppm_cItemName.ToString().Contains("*") || info.ppm_cItemName.ToString().Contains("?"))
{
sqlString.AppendLine("AND ppm_cItemName Like N'" + info.ppm_cItemName.ToString().Replace("*", "%").Replace("?", "_") + "'");
}
else
{
sqlString.AppendLine("AND ppm_cItemName LIKE N'%" + info.ppm_cItemName + "%'");
}
}
if (info.ppm_iPredictedTime != 0)
{
sqlString.AppendLine("AND ppm_iPredictedTime=" + info.ppm_iPredictedTime.ToString());
}
sqlString.AppendLine(" order by ppm_iSeq");
IEnumerable<PrepareProjectMaster_ppm_Info> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<PrepareProjectMaster_ppm_Info>(sqlString.ToString(), new object[] { });
if (infos != null)
{
list = infos.ToList<PrepareProjectMaster_ppm_Info>();
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return list;
}
public PrepareProjectMaster_ppm_Info GetPPItems(Guid PIPFID)
{
PrepareProjectMaster_ppm_Info info = new PrepareProjectMaster_ppm_Info();
StringBuilder sqlString = new StringBuilder();
sqlString.AppendLine("SELECT TOP 1 ");
sqlString.AppendLine("*,isnull(cmt1.cmt_cRemark,'') as ppm_cMachineTypeName,isnull(cmt2.cmt_cRemark,'') as ppm_cItemTypeName");
sqlString.AppendLine("FROM dbo.PrepareProjectMaster_ppm");
sqlString.AppendLine("LEFT JOIN CodeMaster_cmt as cmt1");
sqlString.AppendLine("on ppm_cMachineType=cmt1.cmt_cValue and cmt1.cmt_cKey1='" + Common.DefineConstantValue.CodeMasterDefine.KEY1_TYPEVALUE + "' and cmt1.cmt_cKey2='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_MACHINEMODEL + "'");
sqlString.AppendLine("LEFT JOIN CodeMaster_cmt as cmt2");
sqlString.AppendLine("on ppm_cItemType=cmt2.cmt_cValue and cmt2.cmt_cKey1='" + Common.DefineConstantValue.CodeMasterDefine.KEY1_TYPEVALUE + "' and cmt2.cmt_cKey2='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_PREPARETYPE + "'");
sqlString.AppendLine("WHERE ppm_RecordID='" + PIPFID + "'");
sqlString.AppendLine(" order by ppm_iSeq");
IEnumerable<PrepareProjectMaster_ppm_Info> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<PrepareProjectMaster_ppm_Info>(sqlString.ToString(), new object[] { });
if (infos != null)
{
info = infos.ToList<PrepareProjectMaster_ppm_Info>().FirstOrDefault(); ;
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return info;
}
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 List<ProStatusMonitoring> SearchRecords(ProStatusMonitoring objInfo)
{
List<ProStatusMonitoring> proStatusMonitoring = new List<ProStatusMonitoring>();
using (MainDBDataContext db = new MainDBDataContext())
{
string sqlString = string.Empty;
sqlString += "select" + Environment.NewLine;
sqlString += "ISNULL( mmt_cMachineID,0) as MachineID," + Environment.NewLine;
sqlString += "case when swl_cProjStatus is not null then ISNULL( swl_cProjStatus,'空閒') else ISNULL( sst_cStatusName,'空閒') end as MacStatus," + Environment.NewLine;
sqlString += "case when swl_cProjStatus is not null then ISNULL( datediff(minute,swl_dStatusChange,getdate()),0) else ISNULL( datediff (minute,sst_dBeginTime,sst_dEndTime),0) end as holdTime," + Environment.NewLine;
sqlString += "ISNULL( swl_cSONO ,'') as SONO ," + Environment.NewLine;
sqlString += "ISNULL( sim_cStaffName,'') as MachineCaption," + Environment.NewLine;
sqlString += "ISNULL(ppj_iProjRunTime,0) as RunTime" + Environment.NewLine;
sqlString += "from MachineMaster_mmt" + Environment.NewLine;
sqlString += "left join ScheduleProjList_swl" + Environment.NewLine;
sqlString += "on swl_cMachineNO=mmt_cMachineID and ISNULL( swl_cProjStatus,'') not in ('','SCHEDULE','FINISH','STOP')" + Environment.NewLine;
sqlString += "left join dbo.SpareStatus_sst" + Environment.NewLine;
sqlString += "on sst_cMachineNO=mmt_cMachineID" + Environment.NewLine;
sqlString += "left join PrintProject_ppj" + Environment.NewLine;
sqlString += "on swl_RecordID=ppj_SWLID and ppj_lActiveRecord=1" + Environment.NewLine;
sqlString += "left join ShiftProjList_spl" + Environment.NewLine;
sqlString += "on spl_PPJID=ppj_RecordID" + Environment.NewLine;
sqlString += "left join ShiftInfo_sifo" + Environment.NewLine;
sqlString += "on spl_SIFOID=sifo_RecordID" + Environment.NewLine;
sqlString += "left join StaffInfoMaster_sim" + Environment.NewLine;
sqlString += "on sim_iRecordID=sifo_DutyCaptainID" + Environment.NewLine;
sqlString += "where 1=1" + Environment.NewLine;
if (objInfo.MachineID != "" && objInfo.MachineID != Guid.Empty.ToString())
{
sqlString += "and mmt_cMachineID='" + objInfo.MachineID + "'" + Environment.NewLine;
}
else
{
if (objInfo.MachineType != "")
{
sqlString += "and mmt_cMachineType='" + objInfo.MachineType + "'" + Environment.NewLine;
}
if (objInfo.MacStatus != "")
{
sqlString += "and swl_cProjStatus='" + objInfo.MacStatus + "'" + Environment.NewLine;
}
}
IEnumerable<ProStatusMonitoring> infos = null;
try
{
infos = db.ExecuteQuery<ProStatusMonitoring>(sqlString, new object[] { });
if (infos != null)
{
foreach (ProStatusMonitoring item in infos)
{
proStatusMonitoring.Add(item);
}
}
}
catch (Exception Ex)
{
throw Ex;
}
}
return proStatusMonitoring;
}
private List<Sys_UserMaster_usm_Info> RoleFunction(List<Sys_UserMaster_usm_Info> info)
{
string sqlString = string.Empty;
string whereString = string.Empty;
sqlString = " SELECT distinct TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine;
sqlString += " fum_cFunctionNumber " + Environment.NewLine;
sqlString += " FROM Sys_FunctionMaster_fum" + Environment.NewLine;
sqlString += " LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine;
sqlString += " ON frp_cFunctionNumber=fum_cFunctionNumber" + Environment.NewLine;
//****
sqlString += " LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine;
sqlString += " ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine;
sqlString += " LEFT JOIN Sys_RoleMaster_rlm" + Environment.NewLine;
sqlString += " ON rlm_cRoleID=usp_cRoleID" + Environment.NewLine;
sqlString += " LEFT JOIN Sys_UserRoles_usr" + Environment.NewLine;
sqlString += " ON usr_cRoleID=rlm_cRoleID" + Environment.NewLine;
sqlString += " LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine;
sqlString += " ON usr_cUserLoginID=usm_cUserLoginID" + Environment.NewLine;
//****
sqlString += " LEFT JOIN Sys_FormMaster_fom" + Environment.NewLine;
sqlString += " ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine;
if (info != null)
{
whereString = " WHERE 1=1 ";
if (info[0].formMasterList.Count > 0)
{
if (info[0].formMasterList[0].fom_cFormNumber.Trim() != "")
{
if (info[0].formMasterList[0].fom_cFormNumber.ToString().Contains("*") || info[0].formMasterList[0].fom_cFormNumber.ToString().Contains("?"))
{
whereString += " AND fom_cFormNumber LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info[0].formMasterList[0].fom_cFormNumber) + "'";
}
else
{
whereString += "AND fom_cFormNumber = N'" + info[0].formMasterList[0].fom_cFormNumber.ToString().Trim() + "'";
}
}
}
//**
if (info[0].usm_cUserLoginID.ToString().ToUpper() != "SA")
{
if (info[0].usm_cUserLoginID != "")
{
if (info[0].usm_cUserLoginID.ToString().Contains("*") || info[0].usm_cUserLoginID.ToString().Contains("?"))
{
whereString += " AND usm_cUserLoginID LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info[0].usm_cUserLoginID) + "'";
}
else
{
whereString += "AND usm_cUserLoginID = N'" + info[0].usm_cUserLoginID.ToString().Trim() + "'";
}
}
}
//*****
}
sqlString += whereString;
IEnumerable<Sys_FunctionMaster_fum_Info> infos = null;
List<Sys_FunctionMaster_fum_Info> infoList = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<Sys_FunctionMaster_fum_Info>(sqlString, new object[] { });
if (infos != null)
{
infoList = infos.ToList<Sys_FunctionMaster_fum_Info>();
foreach (Sys_FunctionMaster_fum_Info item in infoList)
{
if (info[0].functionMasterList.SingleOrDefault(d => d.fum_cFunctionNumber == item.fum_cFunctionNumber) == null)
{
info[0].functionMasterList.Add(item);
}
}
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return info;
}
private List<ComboboxDataInfo> GetShiftMaster()
{
List<ComboboxDataInfo> infoList = new List<ComboboxDataInfo>();
string strSQL = string.Empty;
strSQL += " SELECT CONVERT(varchar(max),stm_iRecordID) AS [ValueMember],stm_cShiftName AS [DisplayMember]" + Environment.NewLine;
strSQL += " FROM dbo.ShiftTypeMaster_stm WITH(NOLOCK)" + Environment.NewLine;
strSQL += " WHERE stm_lIsAtive = 1";
IEnumerable<ComboboxDataInfo> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<ComboboxDataInfo>(strSQL, new object[] { });
if (infos != null)
{
foreach (ComboboxDataInfo item in infos)
{
infoList.Add(item);
}
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return infoList;
}
List<StopReasonMaster_srm_Info> SearchRecord(StopReasonMaster_srm_Info searchInfo)
{
List<StopReasonMaster_srm_Info> listReturn = new List<StopReasonMaster_srm_Info>();
try
{
StopReasonMaster_srm_Info srmSearchInfo = searchInfo as StopReasonMaster_srm_Info;
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString());
sbSQL.AppendLine("cmt2.cmt_cRemark as srm_cMachineTypeName,cmt1.cmt_cRemark as srm_cReasonTypeName,cmt3.cmt_cRemark as cPlanTypeDec ,*");
sbSQL.AppendLine("FROM dbo.StopReasonMaster_srm");
sbSQL.AppendLine("left join CodeMaster_cmt as cmt2");
sbSQL.AppendLine("on cmt2.cmt_cKey1='" + Common.DefineConstantValue.CodeMasterDefine.KEY1_TYPEVALUE + "' and cmt2.cmt_cKey2='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_MACHINEMODEL + "' and cmt2.cmt_cValue=srm_cMachineType");
sbSQL.AppendLine("left join CodeMaster_cmt as cmt1");
sbSQL.AppendLine("on cmt1.cmt_cKey1='" + Common.DefineConstantValue.CodeMasterDefine.KEY1_TYPEVALUE + "' and cmt1.cmt_cKey2='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_STOPREASONTYPE + "' and cmt1.cmt_cValue=srm_cReasonType");
sbSQL.AppendLine("left join CodeMaster_cmt as cmt3");
sbSQL.AppendLine("on cmt3.cmt_cKey1='" + Common.DefineConstantValue.CodeMasterDefine.KEY1_TYPEVALUE + "' and cmt3.cmt_cKey2='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_PLANTYPE + "' and cmt3.cmt_cValue=srm_cPlanType");
sbSQL.AppendLine();
sbSQL.AppendLine();
sbSQL.AppendLine("WHERE 1=1");
string strSQL = sbSQL.ToString();
if (srmSearchInfo != null)
{
#region WhereSQL
StringBuilder sbWhere = new StringBuilder();
if (!string.IsNullOrEmpty(srmSearchInfo.srm_cReasonCode))
{
sbWhere.AppendLine("AND [srm_cReasonCode] ='" + srmSearchInfo.srm_cReasonCode + "'");
}
if (!string.IsNullOrEmpty(srmSearchInfo.srm_cMachineType))
{
sbWhere.AppendLine("AND [srm_cMachineType] ='" + srmSearchInfo.srm_cMachineType + "' ");
}
if (!string.IsNullOrEmpty(srmSearchInfo.srm_cReasonType))
{
sbWhere.AppendLine("AND [srm_cReasonType] ='" + srmSearchInfo.srm_cReasonType + "' ");
}
if (!string.IsNullOrEmpty(srmSearchInfo.srm_cReasonName))
{
sbWhere.AppendLine("AND [srm_cReasonName] ='" + srmSearchInfo.srm_cReasonName + "' ");
}
if (srmSearchInfo.srm_iSeq > 0)
{
sbWhere.AppendLine("AND [srm_iSeq] =" + srmSearchInfo.srm_iSeq + " ");
}
if (srmSearchInfo.srm_iPredictedTime > 0)
{
sbWhere.AppendLine("AND [srm_iPredictedTime] =" + srmSearchInfo.srm_iPredictedTime + " ");
}
sbWhere.AppendLine("AND [srm_lIsAtive] =" + (srmSearchInfo.srm_lIsAtive ? "1" : "0") + " ");
if (!string.IsNullOrEmpty(sbWhere.ToString()))
{
strSQL += sbWhere.ToString();
}
strSQL += "order by srm_iSeq";
#endregion
}
IEnumerable<StopReasonMaster_srm_Info> infos = null;
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<StopReasonMaster_srm_Info>(strSQL, new object[] { });
if (infos != null)
{
listReturn = infos.ToList();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listReturn;
}
public List<Model.SysMaster.Sys_UserMaster_usm_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
{
string sqlString = string.Empty;
string whereString = string.Empty;
sqlString = "SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine;
sqlString += " usm_iRecordID," + Environment.NewLine;
sqlString += " usm_cUserLoginID," + Environment.NewLine;
sqlString += " usm_cChaName," + Environment.NewLine;
sqlString += " usm_cEMail," + Environment.NewLine;
sqlString += " (CASE usm_iLock WHEN 1 THEN N'已锁' WHEN 0 THEN N'未锁' END) AS iLock," + Environment.NewLine;
sqlString += " usm_cRemark," + Environment.NewLine;
sqlString += " usm_cAdd," + Environment.NewLine;
sqlString += " usm_dAddDate," + Environment.NewLine;
sqlString += " usm_cLast," + Environment.NewLine;
sqlString += " usm_iLock," + Environment.NewLine;
sqlString += " usm_dLastDate " + Environment.NewLine;
sqlString += " FROM Sys_UserMaster_usm" + Environment.NewLine;
Sys_UserMaster_usm_Info info = null;
info = searchCondition as Sys_UserMaster_usm_Info;
if (info != null)
{
whereString = " WHERE 1=1 ";
if (info.usm_iRecordID != 0)
{
whereString += "AND usm_iRecordID = " + info.usm_iRecordID.ToString() + "";
}
else
{
if (!string.IsNullOrEmpty(info.usm_cUserLoginID))
{
if (info.usm_cUserLoginID.ToString().Contains("*") || info.usm_cUserLoginID.ToString().Contains("?"))
{
whereString += " AND usm_cUserLoginID LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.usm_cUserLoginID) + "'";
}
else
{
whereString += "AND usm_cUserLoginID = N'" + info.usm_cUserLoginID.ToString().Trim() + "'";
}
}
if (!string.IsNullOrEmpty(info.usm_cChaName))
{
if (info.usm_cChaName.ToString().Contains("*") || info.usm_cChaName.ToString().Contains("?"))
{
whereString += " AND usm_cChaName LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.usm_cChaName) + "'";
}
else
{
whereString += "AND usm_cChaName = N'" + info.usm_cChaName.ToString().Trim() + "'";
}
}
if (!string.IsNullOrEmpty(info.usm_cEMail))
{
if (info.usm_cEMail.ToString().Contains("*") || info.usm_cEMail.ToString().Contains("?"))
{
whereString += " AND usm_cEMail LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.usm_cEMail) + "'";
}
else
{
whereString += "AND usm_cEMail = N'" + info.usm_cEMail.ToString().Trim() + "'";
}
}
}
}
sqlString += whereString;
IEnumerable<Sys_UserMaster_usm_Info> infos = null;
List<Sys_UserMaster_usm_Info> infoList = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<Sys_UserMaster_usm_Info>(sqlString, new object[] { });
if (infos != null)
{
infoList = infos.ToList<Sys_UserMaster_usm_Info>();
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return infoList;
}
public List<MaterialProfile_mlpf_Info> SearchRecords(IModelObject searchCondition)
{
List<MaterialProfile_mlpf_Info> list = new List<MaterialProfile_mlpf_Info>();
MaterialProfile_mlpf_Info info = new MaterialProfile_mlpf_Info();
info = searchCondition as MaterialProfile_mlpf_Info;
StringBuilder sqlString = new StringBuilder();
sqlString.AppendLine("SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString());
sqlString.AppendLine("*");
sqlString.AppendLine("FROM MaterialProfile_mlpf ");
sqlString.AppendLine("WHERE 1=1 And mlpf_lIsDeleted=0");
if (info.mlpf_cMaterialType != "" && info.mlpf_cMaterialType != null)
{
sqlString.AppendLine("AND mlpf_cMaterialType=N'" + info.mlpf_cMaterialType + "'");
}
if (info.mlpf_cMaterialCode != "" && info.mlpf_cMaterialCode != null)
{
sqlString.AppendLine("AND mlpf_cMaterialCode='" + info.mlpf_cMaterialCode + "'");
}
if (info.mlpf_cMaterialName != "" && info.mlpf_cMaterialName != null)
{
sqlString.AppendLine("AND mlpf_cMaterialName=N'" + info.mlpf_cMaterialName + "'");
}
if (info.mlpf_cDescription != "" && info.mlpf_cDescription != null)
{
sqlString.AppendLine("AND mlpf_cDescription=N'" + info.mlpf_cDescription + "'");
}
if (info.mlpf_cMaterialBrand != "" && info.mlpf_cMaterialBrand != null)
{
sqlString.AppendLine("AND mlpf_cMaterialBrand=N'" + info.mlpf_cMaterialBrand + "'");
}
if (info.mlpf_cMaterialAbbreviation != "" && info.mlpf_cMaterialAbbreviation != null)
{
sqlString.AppendLine("AND mlpf_cMaterialAbbreviation=N'" + info.mlpf_cMaterialAbbreviation + "'");
}
IEnumerable<MaterialProfile_mlpf_Info> infos = null;
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<MaterialProfile_mlpf_Info>(sqlString.ToString(), new object[] { });
if (infos != null)
{
list = infos.ToList<MaterialProfile_mlpf_Info>();
}
}
return list;
}
public List<CodeMaster_cmt_Info> FindRecord(CodeMaster_cmt_Info info)
{
//throw new NotImplementedException();
List<CodeMaster_cmt_Info> list = new List<CodeMaster_cmt_Info>();
//CodeMaster_cmt_Info info = new CodeMaster_cmt_Info();
//info = searchCondition as CodeMaster_cmt_Info;
string sqlString = string.Empty;
string whereString = string.Empty;
sqlString = "SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine;
sqlString += "*" + Environment.NewLine;
sqlString += "FROM CodeMaster_cmt" + Environment.NewLine;
whereString = "WHERE 1=1" + Environment.NewLine;
if (info.cmt_cKey1 != "")
{
whereString += "AND cmt_cKey1='" + info.cmt_cKey1 + "' " + Environment.NewLine;
}
if (info.cmt_cKey2 != "")
{
whereString += "AND cmt_cKey2='" + info.cmt_cKey2 + "' " + Environment.NewLine;
}
if (info.cmt_cValue != "")
{
if (info.cmt_cValue.ToString().Contains("*") || info.cmt_cValue.ToString().Contains("?"))
{
whereString += "AND cmt_cValue LIKE N'" + info.cmt_cValue.ToString().Replace("*", "%").Replace("?", "_") + "' " + Environment.NewLine;
}
else
{
whereString += "AND cmt_cValue LIKE N'%" + info.cmt_cValue + "%' " + Environment.NewLine;
}
}
if (info.cmt_fNumber != 0)
{
whereString += "AND cmt_fNumber=Convert(decimal," + info.cmt_fNumber.ToString() + ")" + Environment.NewLine;
}
IEnumerable<CodeMaster_cmt_Info> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<CodeMaster_cmt_Info>(sqlString + whereString, new object[] { });
if (infos != null)
{
list = infos.ToList<CodeMaster_cmt_Info>();
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return list;
}
public List<ListColumnsMaster_lcm_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
{
List<ListColumnsMaster_lcm_Info> list = new List<ListColumnsMaster_lcm_Info>();
ListColumnsMaster_lcm_Info info = new ListColumnsMaster_lcm_Info();
info = searchCondition as ListColumnsMaster_lcm_Info;
StringBuilder sqlString = new StringBuilder();
sqlString.AppendLine("SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString());
sqlString.AppendLine("*");
sqlString.AppendLine("FROM dbo.ListColumnsMaster_lcm");
sqlString.AppendLine("WHERE 1=1");
if (info.lcm_cMainType != "")
{
sqlString.AppendLine("AND lcm_cMainType='" + info.lcm_cMainType + "'");
}
if (info.lcm_cSubType != "")
{
sqlString.AppendLine("AND lcm_cSubType='" + info.lcm_cSubType + "'");
}
if (info.lcm_cValue != "")
{
sqlString.AppendLine("AND lcm_cSubType='" + info.lcm_cValue + "'");
}
IEnumerable<ListColumnsMaster_lcm_Info> infos = null;
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<ListColumnsMaster_lcm_Info>(sqlString.ToString(), new object[] { });
if (infos != null)
{
list = infos.ToList<ListColumnsMaster_lcm_Info>();
}
}
return list;
}
public bool UpdateRecord(Sys_RoleMaster_rlm_Info infoObject)
{
string sqlString = string.Empty;
using (MainDBDataContext db = new MainDBDataContext())
{
Sys_RoleMaster_rlm rlm = new Sys_RoleMaster_rlm();
Sys_RoleMaster_rlm_Info info = new Sys_RoleMaster_rlm_Info();
try
{
rlm = db.Sys_RoleMaster_rlm.SingleOrDefault(t => t.rlm_iRecordID == infoObject.rlm_iRecordID);
List<Sys_UserRoles_usr> usrs = db.Sys_UserRoles_usr.Where(d => d.usr_cRoleID == infoObject.rlm_cRoleID).ToList();
for (int i = 0; i < usrs.Count; i++)
{
Sys_UserRoles_usr sta = usrs[i];
db.Sys_UserRoles_usr.DeleteOnSubmit(sta);
}
IEnumerable<Sys_RoleMaster_rlm_Info> IErlm = null;
IEnumerable<Sys_UserRoles_usr_Info> IEusr = null;
sqlString += "UPDATE Sys_RoleMaster_rlm" + Environment.NewLine;
sqlString += " SET rlm_cRoleID='" + infoObject.rlm_cRoleID + "'," + Environment.NewLine;
sqlString += " rlm_cRoleDesc='" + infoObject.rlm_cRoleDesc + "'," + Environment.NewLine;
sqlString += " rlm_cLast='" + infoObject.rlm_cLast + "'," + Environment.NewLine;
sqlString += " rlm_dLastDate='" + DateTime.Now.ToString(DefineConstantValue.gc_DateTimeFormat) + "'" + Environment.NewLine;
sqlString += " WHERE rlm_iRecordID='" + infoObject.rlm_iRecordID + "'";
IErlm = db.ExecuteQuery<Sys_RoleMaster_rlm_Info>(sqlString, new object[] { });
sqlString = string.Empty;
sqlString += "DELETE FROM Sys_UserRoles_usr WHERE usr_cRoleID='" + infoObject.rlm_cRoleID + "'";
IEusr = db.ExecuteQuery<Sys_UserRoles_usr_Info>(sqlString, new object[] { });
if (infoObject.userMasterList != null && infoObject.userMasterList.Count > 0)
{
for (int i = 0; i < infoObject.userMasterList.Count; i++)
{
sqlString = string.Empty;
Sys_UserMaster_usm_Info usmInfo = infoObject.userMasterList[i];
sqlString += "INSERT INTO Sys_UserRoles_usr" + Environment.NewLine;
sqlString += "(usr_cUserLoginID, usr_cRoleID)" + Environment.NewLine;
sqlString += " VALUES " + Environment.NewLine;
sqlString += "('" + usmInfo.usm_cUserLoginID + "','" + infoObject.rlm_cRoleID + "')";
IEusr = db.ExecuteQuery<Sys_UserRoles_usr_Info>(sqlString, new object[] { });
}
}
return true;
}
catch (Exception Ex)
{
throw Ex;
}
}
}
private Sys_UserMaster_usm_Info FindAllForm(Sys_UserMaster_usm_Info info)
{
string sqlString = string.Empty;
sqlString += "SELECT * " + Environment.NewLine;
sqlString += "FROM Sys_FormMaster_fom" + Environment.NewLine;
//sqlString += "FROM Sys_FormMaster_fom WHERE fom_iWebForm='false'" + Environment.NewLine;
IEnumerable<Sys_FormMaster_fom_Info> infos = null;
List<Sys_FormMaster_fom_Info> infoList = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<Sys_FormMaster_fom_Info>(sqlString, new object[] { });
if (infos != null)
{
infoList = infos.ToList<Sys_FormMaster_fom_Info>();
}
Sys_UserMaster_usm_Info cond = new Sys_UserMaster_usm_Info();
//// 请处理
cond.usm_cUserLoginID = info.usm_cUserLoginID;
//////
foreach (Sys_FormMaster_fom_Info t in infoList)
{
info.formMasterList.Add(t);
//////////////////////// 请处理
cond.formMasterList.Clear();
cond.formMasterList.Add(t);
var userInfos = SearchRecords(cond);
if (userInfos.Count > 0)
{
t.functionMaster = userInfos[0].functionMasterList;
}
//////////////////////
}
return info;
}
}
catch (Exception Ex)
{
throw Ex;
}
}
public List<VacationMaster_vcm_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
{
List<VacationMaster_vcm_Info> list = new List<VacationMaster_vcm_Info>();
VacationMaster_vcm_Info info = new VacationMaster_vcm_Info();
info = searchCondition as VacationMaster_vcm_Info;
string strSQL = "";
strSQL += " SELECT vcm_RecordID,vcm_cHolidayName,vcm_cType,vcm_TargetID,vcm_dStartDate,vcm_dEndDate,vcm_cWeekDate,vcm_lIsAtive " + Environment.NewLine;
strSQL += " FROM VacationMaster_vcm " + Environment.NewLine;
//strSQL += " WHERE vcm_lIsAtive =0 " + Environment.NewLine;
strSQL += " WHERE 1 =1 " + Environment.NewLine;
if (info.vcm_TargetID != Guid.Empty) //小於零時搜索全部
{
strSQL += " AND vcm_TargetID = '" + info.vcm_TargetID + "' " + Environment.NewLine;
}
IEnumerable<VacationMaster_vcm_Info> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<VacationMaster_vcm_Info>(strSQL, new object[] { });
if (infos != null)
{
list = infos.ToList<VacationMaster_vcm_Info>();
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return list;
}
private Sys_UserMaster_usm_Info FindAllFunction(Sys_UserMaster_usm_Info info)
{
string sqlString = string.Empty;
sqlString += "SELECT * " + Environment.NewLine;
sqlString += "FROM Sys_FunctionMaster_fum " + Environment.NewLine;
IEnumerable<Sys_FunctionMaster_fum_Info> infos = null;
List<Sys_FunctionMaster_fum_Info> infoList = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<Sys_FunctionMaster_fum_Info>(sqlString, new object[] { });
if (infos != null)
{
infoList = infos.ToList<Sys_FunctionMaster_fum_Info>();
}
foreach (Sys_FunctionMaster_fum_Info t in infoList)
{
info.functionMasterList.Add(t);
}
return info;
}
}
catch (Exception Ex)
{
throw Ex;
}
}
public List<StructureMaster_stt_Info> StructureGetAll()
{
List<StructureMaster_stt_Info> RstList = new List<StructureMaster_stt_Info>();
string sqlString = string.Empty;
string whereString = string.Empty;
sqlString = "SELECT " + Environment.NewLine;
sqlString += "*" + Environment.NewLine;
sqlString += "FROM dbo.StructureMaster_stt" + Environment.NewLine;
whereString = "WHERE 1=1 " + Environment.NewLine;
IEnumerable<StructureMaster_stt_Info> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<StructureMaster_stt_Info>(sqlString + whereString, new object[] { });
if (infos != null)
{
List<StructureMaster_stt_Info> list = infos.ToList<StructureMaster_stt_Info>();
RstList = StructureConversion(list);
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return RstList;
}
private Sys_UserMaster_usm_Info FindForm(Sys_UserMaster_usm_Info info)
{
string sqlString = string.Empty;
sqlString += "SELECT distinct fom_iRecordID, fom_iParentID, fom_iIndex, fom_cFormNumber, fom_cFormDesc, fom_cExePath,fom_cWebPath,fom_iWebForm " + Environment.NewLine;
sqlString += "FROM Sys_FormMaster_fom " + Environment.NewLine;
sqlString += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine;
sqlString += "ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine;
sqlString += "LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine;
sqlString += "ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine;
sqlString += "LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine;
sqlString += "ON usp_cUserLoginID=usm_cUserLoginID WHERE usm_cUserLoginID='" + info.usm_cUserLoginID + "'";
//sqlString += "ON usp_cUserLoginID=usm_cUserLoginID WHERE usm_cUserLoginID='" + info.usm_cUserLoginID + "' AND fom_iWebForm='false'";
string sqlRole = string.Empty;
sqlRole += "SELECT distinct fom_iRecordID, fom_iParentID, fom_iIndex, fom_cFormNumber, fom_cFormDesc, fom_cExePath,fom_cWebPath,fom_iWebForm " + Environment.NewLine;
sqlRole += "FROM Sys_FormMaster_fom " + Environment.NewLine;
sqlRole += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine;
sqlRole += "ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine;
sqlRole += "LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine;
sqlRole += "ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine;
sqlRole += "LEFT JOIN Sys_RoleMaster_rlm" + Environment.NewLine;
sqlRole += "ON usp_cRoleID=rlm_cRoleID" + Environment.NewLine;
sqlRole += "LEFT JOIN Sys_UserRoles_usr" + Environment.NewLine;
sqlRole += "ON rlm_cRoleID=usr_cRoleID" + Environment.NewLine;
sqlRole += "LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine;
sqlRole += "ON usr_cUserLoginID=usm_cUserLoginID WHERE usm_cUserLoginID='" + info.usm_cUserLoginID + "'";
string sql = "SELECT distinct a.* FROM( (" + sqlString + ") union (" + sqlRole + ")) as a";
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
Sys_UserMaster_usm_Info cond = new Sys_UserMaster_usm_Info();
//// 请处理
cond.usm_cUserLoginID = info.usm_cUserLoginID;
//////
List<Sys_FormMaster_fom_Info> list = new List<Sys_FormMaster_fom_Info>();
var a = db.ExecuteQuery<Sys_FormMaster_fom_Info>(sql, new object[] { });
foreach (Sys_FormMaster_fom_Info t in a)
{
info.formMasterList.Add(t);
////////////////////// 请处理
cond.formMasterList.Clear();
cond.formMasterList.Add(t);
t.functionMaster = SearchRecords(cond)[0].functionMasterList;
//////////////////////
}
return info;
}
}
catch (Exception Ex)
{
throw Ex;
}
}
public List<AheadOfOffDuty_aod_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
{
List<AheadOfOffDuty_aod_Info> list = new List<AheadOfOffDuty_aod_Info>();
AheadOfOffDuty_aod_Info info = new AheadOfOffDuty_aod_Info();
info = searchCondition as AheadOfOffDuty_aod_Info;
string strSQL = "";
strSQL += " SELECT aod_RecordID,aod_cName,aod_dOffDutyTime,aod_iShiftTypeID,aod_lIsAtive " + Environment.NewLine;
strSQL += " FROM AheadOfOffDuty_aod " + Environment.NewLine;
//strSQL += " WHERE aod_lIsDeleted =0 " + Environment.NewLine;
strSQL += " WHERE 1 =1 " + Environment.NewLine;
if (info.aod_TargetID != Guid.Empty) //小於零時搜索全部
{
strSQL += " AND aod_TargetID = '" + info.aod_TargetID + "' " + Environment.NewLine;
}
IEnumerable<AheadOfOffDuty_aod_Info> infos = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<AheadOfOffDuty_aod_Info>(strSQL, new object[] { });
if (infos != null)
{
list = infos.ToList<AheadOfOffDuty_aod_Info>();
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return list;
}
private Sys_UserMaster_usm_Info FindFunction(Sys_UserMaster_usm_Info info)
{
string sqlString = string.Empty;
sqlString += "SELECT fum_iRecordID, fum_cFunctionNumber, fum_cFunctionDesc, fum_cRemark " + Environment.NewLine;
sqlString += "FROM Sys_FunctionMaster_fum " + Environment.NewLine;
sqlString += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine;
sqlString += "ON fum_cFunctionNumber=frp_cFunctionNumber" + Environment.NewLine;
sqlString += "LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine;
sqlString += "ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine;
sqlString += "LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine;
sqlString += "ON usp_cUserLoginID=usm_cUserLoginID WHERE usm_cUserLoginID='" + info.usm_cUserLoginID + "'";
IEnumerable<Sys_FunctionMaster_fum_Info> infos = null;
List<Sys_FunctionMaster_fum_Info> infoList = null;
string sqlRole = string.Empty;
sqlRole += "SELECT fum_iRecordID, fum_cFunctionNumber, fum_cFunctionDesc, fum_cRemark " + Environment.NewLine;
sqlRole += "FROM Sys_FunctionMaster_fum " + Environment.NewLine;
sqlRole += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine;
sqlRole += "ON fum_cFunctionNumber=frp_cFunctionNumber" + Environment.NewLine;
sqlRole += "LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine;
sqlRole += "ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine;
sqlRole += "LEFT JOIN Sys_RoleMaster_rlm" + Environment.NewLine;
sqlRole += "ON usp_cRoleID=rlm_cRoleID" + Environment.NewLine;
sqlRole += "LEFT JOIN Sys_UserRoles_usr" + Environment.NewLine;
sqlRole += "ON rlm_cRoleID=usr_cRoleID" + Environment.NewLine;
sqlRole += "LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine;
sqlRole += "ON usr_cUserLoginID=usm_cUserLoginID WHERE usm_cUserLoginID='" + info.usm_cUserLoginID + "'";
IEnumerable<Sys_FunctionMaster_fum_Info> roleInfos = null;
List<Sys_FunctionMaster_fum_Info> roleInfoList = null;
try
{
using (MainDBDataContext db = new MainDBDataContext())
{
infos = db.ExecuteQuery<Sys_FunctionMaster_fum_Info>(sqlString, new object[] { });
if (infos != null)
{
infoList = infos.ToList<Sys_FunctionMaster_fum_Info>();
}
foreach (Sys_FunctionMaster_fum_Info t in infoList)
{
info.functionMasterList.Add(t);
}
roleInfos = db.ExecuteQuery<Sys_FunctionMaster_fum_Info>(sqlRole, new object[] { });
if (roleInfos != null)
{
roleInfoList = roleInfos.ToList<Sys_FunctionMaster_fum_Info>();
}
foreach (Sys_FunctionMaster_fum_Info t in roleInfoList)
{
info.functionMasterList.Add(t);
}
return info;
}
}
catch (Exception Ex)
{
throw Ex;
}
}
