C# MainDBDataContext.ExecuteQuery方法代码示例

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