//AM Issue#32801 04/09/2008 0.0.0.9 -- AM Issue#32801 09/17/2008 AntechCSM 1.0.13.0 (Search with Message Keyword list) public static DataTable getMessageDetails(String SearchText, String SearchOption) { string selectStatement = "SELECT MSG_Code, MSG_FirstLine, MSG_MessageText, MSG_AutoProblemComment,MSG_AutoProblemResolution, MSG_DefaultProblemCategoryDR,MSG_KeywordList FROM DIC_Message"; selectStatement = selectStatement + " WHERE 1=1 "; if (SearchOption == "Message_Code") { selectStatement = (SearchText != "" ? selectStatement + " AND %SQLUPPER MSG_Code %STARTSWITH %SQLUPPER '" + SearchText + "'" : selectStatement); } else { selectStatement = (SearchText != "" ? selectStatement + " AND %SQLUPPER MSG_KeywordList LIKE %SQLUPPER '%" + SearchText + "%'" : selectStatement); } CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass(); DataSet returnDS = cache.FillCacheDataSet(selectStatement); if (returnDS.Tables.Count > 0) { return(returnDS.Tables[0]); } else { return(null); } }
private static DataTable getProfilesByCode(String[] searchValues, String AndOrSwitch) { #region Reference Query //SELECT // UC_RowID, // SP_getProfileUnits(UC_RowID) AS ProfileDetails //FROM // DIC_UnitCode //WHERE // UC_IsProfile='Y' //AND // (SP_getProfileUnits(UC_RowID) ='100' OR SP_getProfileUnits(UC_RowID) LIKE '100,%' OR SP_getProfileUnits(UC_RowID) LIKE '%,100' OR SP_getProfileUnits(UC_RowID) LIKE '%,100,%') //AND/OR // (SP_getProfileUnits(UC_RowID) ='652' OR SP_getProfileUnits(UC_RowID) LIKE '652,%' OR SP_getProfileUnits(UC_RowID) LIKE '%,652' OR SP_getProfileUnits(UC_RowID) LIKE '%,652,%') #endregion Reference Query StringBuilder sb = new StringBuilder(); sb.Append("SELECT "); sb.Append("UC_RowID As ProfileCode, "); sb.Append("UC_DisplayReportingTitle ProfileName "); sb.Append("FROM DIC_UnitCode "); sb.Append("WHERE "); sb.Append("UC_IsProfile='Y' "); for (int i = 0; i < searchValues.Length; i++) { string searchvalue = searchValues[i].Trim(); if (searchvalue.Length > 0) { if (i == 0) { sb.Append("AND "); } else { sb.Append(AndOrSwitch + " "); } sb.Append("(SP_getProfileUnits(UC_RowID) ='" + searchvalue + "' "); sb.Append("OR "); sb.Append("SP_getProfileUnits(UC_RowID) LIKE '" + searchvalue + ",%' "); sb.Append("OR "); sb.Append("SP_getProfileUnits(UC_RowID) LIKE '%," + searchvalue + "' "); sb.Append("OR "); sb.Append("SP_getProfileUnits(UC_RowID) LIKE '%," + searchvalue + ",%') "); } } string selectStatement = sb.ToString(); CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass(); DataSet returnDS = cache.FillCacheDataSet(selectStatement); if (returnDS.Tables.Count > 0) { return(returnDS.Tables[0]); } else { return(null); } }
//AM Issue#37633 04/29/2008 0.0.0.9 public static DataTable getUsersByGroupID(string GroupID) { string selectStatement = "SELECT MGUL_UserDR->USER_UserID USER_ID,MGUL_DestinationDR->MDEST_ID SYSTEM_ID FROM DIC_MailGroupUserList WHERE UPPER(MGUL_MGRP_ParRef->MGRP_GroupID) = '" + GroupID + "' AND (MGUL_UserDR->USER_UserID <> '' AND MGUL_DestinationDR->MDEST_ID <> '')"; CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass(); DataSet returnDS = cache.FillCacheDataSet(selectStatement); if (returnDS.Tables.Count > 0) { return(returnDS.Tables[0]); } else { return(null); } }
//AM Issue#37633 04/29/2008 0.0.0.9 public static DataTable getGroupByGroupID(string GroupID) { string selectStatement = "SELECT MGRP_GroupID GROUP_ID,MGRP_GroupName GROUP_NAME,MGRP_UserList USERS FROM DIC_MailGroup WHERE UPPER(MGRP_GroupID) = '" + GroupID + "'"; CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass(); DataSet returnDS = cache.FillCacheDataSet(selectStatement); if (returnDS.Tables.Count > 0) { return(returnDS.Tables[0]); } else { return(null); } }
//AM Issue#37267 04/17/2008 0.0.0.9 public static DataTable getUsersByUserID(string userID) { string selectStatement = "SELECT MDUL_UserDR->USER_UserID USER_ID,MDUL_UserDR->USER_LastFirstName USER_NAME,MDUL_UserDR->USER_LabLocationDR->LABLO_LabName USER_LABLOCATION, MDUL_MDEST_ParRef->MDEST_ID USER_SYSTEM_ID, MDUL_MDEST_ParRef->MDEST_Name USER_SYSTEM_NAME FROM DIC_MailDestinationUserList WHERE UPPER(MDUL_UserDR->USER_UserID) = '" + userID + "'"; CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass(); DataSet returnDS = cache.FillCacheDataSet(selectStatement); if (returnDS.Tables.Count > 0) { return(returnDS.Tables[0]); } else { return(null); } }