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