public static Dictionary <String, String> getAllEntires(int type, String companyId, String divisionId, String keyStroke)
{
if (type == 1)
{
return(EmpId.getEmpByCompany(companyId, divisionId, keyStroke));
}
else if (type == 2)
{
return(SSN.getSSNByCompany(companyId, divisionId, keyStroke));
}
else if (type == 3)
{
return(ZipCode.getZipByCompany(companyId, divisionId, keyStroke));
}
else if (type == 4)
{
return(Address.getCityByCompany(companyId, divisionId, keyStroke));
}
return(new Dictionary <string, string>());
}
public static Dictionary <String, String> getAllEntires(int type)
{
if (type == 1)
{
return(EmpId.getAllEmpId());
}
else if (type == 2)
{
return(SSN.getAllSSN());
}
else if (type == 3)
{
return(ZipCode.getAllZipCode());
}
else if (type == 4)
{
return(Address.getAllCities());
}
return(new Dictionary <string, string>());
}
public static List <ZipCode> getZipByCompanyList(String companyId, String divisionId, String keyStroke)
{
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Entry Point", Logger.logLevel.Info);
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, companyId + divisionId + keyStroke, Logger.logLevel.Debug);
List <ZipCode> lstZipCode = new List <ZipCode>();
SqlConnection conn = null;
SqlDataReader reader = null;
try
{
// create and open a connection object
conn = ConnectionManager.getConnection();
conn.Open();
String query;
if (companyId.Equals("null") && divisionId.Equals("null"))
{
query = "select distinct TOP 50 P.address5 FROM person P WHERE P.address5 LIKE '%" + keyStroke + "%'";
}
else if (!companyId.Equals("null") && !divisionId.Equals("null"))
{
query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_company Comp, rs_division Div";
query = query + " WHERE P.department = D.id AND Comp.companyId = @company AND Div.divisionId = @division AND D.user1 LIKE Comp.companyName AND D.user2 LIKE Div.divisionName AND P.address5 LIKE '%" + keyStroke + "%'";
}
else if (companyId.Equals("null"))
{
query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_division Div";
query = query + " WHERE P.department = D.id AND Div.divisionId = @division AND D.user2 LIKE Div.divisionName AND P.address5 LIKE '%" + keyStroke + "%'";
}
else
{
query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_company Comp";
query = query + " WHERE P.department = D.id AND Comp.companyId = @company AND D.user1 LIKE Comp.companyName AND P.address5 LIKE '%" + keyStroke + "%'";
}
SqlCommand command = new SqlCommand(query, conn);
command.Parameters.AddWithValue("@company", companyId.Trim());
command.Parameters.AddWithValue("@division", divisionId.Trim());
using (reader = command.ExecuteReader())
{
while (reader.Read())
{
String strZipCode = reader.GetSqlValue(0).ToString().Trim();
if (!(strZipCode.Equals("null") || strZipCode.Equals("NULL") || strZipCode.Equals("Null") || strZipCode.Equals("")))
{
ZipCode zipObj = new ZipCode();
strZipCode = strZipCode.Substring(0, 5).Trim();
int n;
bool isNumeric = int.TryParse(strZipCode, out n);
if (isNumeric)
{
zipObj.value = strZipCode;
zipObj.key = strZipCode;
lstZipCode.Add(zipObj);
}
}
}
}
}
catch (Exception ex)
{
var stackTrace = new StackTrace(ex, true);
var line = stackTrace.GetFrame(0).GetFileLineNumber();
Logger.LogExceptions(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, ex.Message, line.ToString(), Logger.logLevel.Exception);
}
finally
{
if (conn != null)
{
conn.Close();
}
if (reader != null)
{
reader.Close();
}
}
lstZipCode = lstZipCode.OrderBy(x => x.value).ToList();
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug);
return(lstZipCode);
}
public static List <ZipCode> getAllZipCodeList()
{
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Entry Point", Logger.logLevel.Info);
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "", Logger.logLevel.Debug);
List <ZipCode> lstZipCode = new List <ZipCode>();
SqlConnection conn = null;
SqlDataReader reader = null;
try
{
// create and open a connection object
conn = ConnectionManager.getConnection();
conn.Open();
String query = "select distinct TOP 50 address5 from person";
SqlCommand command = new SqlCommand(query, conn);
using (reader = command.ExecuteReader())
{
while (reader.Read())
{
String strZipCode = reader.GetSqlValue(0).ToString().Trim();
if (!(strZipCode.Equals("null") || strZipCode.Equals("NULL") || strZipCode.Equals("Null") || strZipCode.Equals("")))
{
ZipCode zipObj = new ZipCode();
strZipCode = strZipCode.Substring(0, 5).Trim();
int n;
bool isNumeric = int.TryParse(strZipCode, out n);
if (isNumeric)
{
zipObj.value = strZipCode;
zipObj.key = strZipCode;
lstZipCode.Add(zipObj);
}
}
}
}
}
catch (Exception ex)
{
var stackTrace = new StackTrace(ex, true);
var line = stackTrace.GetFrame(0).GetFileLineNumber();
Logger.LogExceptions(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, ex.Message, line.ToString(), Logger.logLevel.Exception);
}
finally
{
if (conn != null)
{
conn.Close();
}
if (reader != null)
{
reader.Close();
}
}
lstZipCode = lstZipCode.OrderBy(x => x.value).ToList();
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug);
return(lstZipCode);
}
public static List<ZipCode> getAllZipCodeList()
{
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Entry Point", Logger.logLevel.Info);
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "", Logger.logLevel.Debug);
List<ZipCode> lstZipCode = new List<ZipCode>();
SqlConnection conn = null;
SqlDataReader reader = null;
try
{
// create and open a connection object
conn = ConnectionManager.getConnection();
conn.Open();
String query = "select distinct TOP 50 address5 from person";
SqlCommand command = new SqlCommand(query, conn);
using (reader = command.ExecuteReader())
{
while (reader.Read())
{
String strZipCode = reader.GetSqlValue(0).ToString().Trim();
if (!(strZipCode.Equals("null") || strZipCode.Equals("NULL") || strZipCode.Equals("Null") || strZipCode.Equals("")))
{
ZipCode zipObj = new ZipCode();
strZipCode = strZipCode.Substring(0, 5).Trim();
int n;
bool isNumeric = int.TryParse(strZipCode, out n);
if (isNumeric)
{
zipObj.value = strZipCode;
zipObj.key = strZipCode;
lstZipCode.Add(zipObj);
}
}
}
}
}
catch (Exception ex)
{
var stackTrace = new StackTrace(ex, true);
var line = stackTrace.GetFrame(0).GetFileLineNumber();
Logger.LogExceptions(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, ex.Message, line.ToString(), Logger.logLevel.Exception);
}
finally
{
if (conn != null)
{
conn.Close();
}
if (reader != null)
{
reader.Close();
}
}
lstZipCode = lstZipCode.OrderBy(x => x.value).ToList();
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug);
return lstZipCode;
}
public static List<ZipCode> getZipByCompanyList(String companyId, String divisionId, String keyStroke)
{
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Entry Point", Logger.logLevel.Info);
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, companyId + divisionId + keyStroke, Logger.logLevel.Debug);
List<ZipCode> lstZipCode = new List<ZipCode>();
SqlConnection conn = null;
SqlDataReader reader = null;
try
{
// create and open a connection object
conn = ConnectionManager.getConnection();
conn.Open();
String query;
if (companyId.Equals("null") && divisionId.Equals("null"))
{
query = "select distinct TOP 50 P.address5 FROM person P WHERE P.address5 LIKE '%" + keyStroke + "%'";
}
else if (!companyId.Equals("null") && !divisionId.Equals("null"))
{
query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_company Comp, rs_division Div";
query = query + " WHERE P.department = D.id AND Comp.companyId = @company AND Div.divisionId = @division AND D.user1 LIKE Comp.companyName AND D.user2 LIKE Div.divisionName AND P.address5 LIKE '%" + keyStroke + "%'";
}
else if (companyId.Equals("null"))
{
query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_division Div";
query = query + " WHERE P.department = D.id AND Div.divisionId = @division AND D.user2 LIKE Div.divisionName AND P.address5 LIKE '%" + keyStroke + "%'";
}
else
{
query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_company Comp";
query = query + " WHERE P.department = D.id AND Comp.companyId = @company AND D.user1 LIKE Comp.companyName AND P.address5 LIKE '%" + keyStroke + "%'";
}
SqlCommand command = new SqlCommand(query, conn);
command.Parameters.AddWithValue("@company", companyId.Trim());
command.Parameters.AddWithValue("@division", divisionId.Trim());
using (reader = command.ExecuteReader())
{
while (reader.Read())
{
String strZipCode = reader.GetSqlValue(0).ToString().Trim();
if (!(strZipCode.Equals("null") || strZipCode.Equals("NULL") || strZipCode.Equals("Null") || strZipCode.Equals("")))
{
ZipCode zipObj = new ZipCode();
strZipCode = strZipCode.Substring(0, 5).Trim();
int n;
bool isNumeric = int.TryParse(strZipCode, out n);
if (isNumeric)
{
zipObj.value = strZipCode;
zipObj.key = strZipCode;
lstZipCode.Add(zipObj);
}
}
}
}
}
catch (Exception ex)
{
var stackTrace = new StackTrace(ex, true);
var line = stackTrace.GetFrame(0).GetFileLineNumber();
Logger.LogExceptions(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, ex.Message, line.ToString(), Logger.logLevel.Exception);
}
finally
{
if (conn != null)
{
conn.Close();
}
if (reader != null)
{
reader.Close();
}
}
lstZipCode = lstZipCode.OrderBy(x => x.value).ToList();
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug);
return lstZipCode;
}