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; } }