public List <CompanysEntity> GetCompaniesHasProject()
{
int comId = GetCompanyId("Sunnet");
string strSql = string.Format(" Select distinct C.* from Projects U inner join Companys C on U.CompanyID=C.ComID where C.ComID != '{0}' order by CompanyName", comId);
List <CompanysEntity> list = new List <CompanysEntity>();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql))
{
try
{
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
list.Add(CompanysEntity.ReaderBind(dataReader, false));
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql,
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
return(list);
}
}
/// <summary>
/// Get an object entity
/// </summary>
public CompanysEntity Get(int ComID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ComID,CompanyName,Phone,Fax,Website,AssignedSystemUrl,Address1,Address2,City,State,Logo,Status,CreatedOn,CreatedBy,CreateUserName,ModifiedOn,ModifiedBy from Companys ");
strSql.Append(" where ComID=@ComID ");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "ComID", DbType.Int32, ComID);
CompanysEntity model = null;
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
model = CompanysEntity.ReaderBind(dataReader, false);
}
}
return(model);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(null);
}
}
}
public SearchCompaniesResponse SearchCompanies(SearchCompaniesRequest request)
{
int start = request.CurrentPage * request.PageCount + 1 - request.PageCount;
int end = request.CurrentPage * request.PageCount;
string strSelCount = " SELECT COUNT(1) FROM [Companys] ";
string strOrderby = string.Format(" {0} {1} ", request.OrderExpression, request.OrderDirection);
string strSelAttrs = @" SELECT *
,(SELECT COUNT(1) FROM Projects WHERE CompanyID = COM.ComID) AS ProjectsCount
,(SELECT COUNT(1) FROM Users WHERE CompanyID = COM.ComID) AS ClientsCount
FROM [Companys] COM ";
string strSelAttrsOrderBy = string.Format(@" Order BY {0} ", strOrderby);
string strSelPageModel = string.Format(@"SELECT * FROM(
SELECT ROW_NUMBER() OVER(
Order BY {0}) as INDEX_ID,*
,(SELECT COUNT(1) FROM Projects WHERE CompanyID = COM.ComID) AS ProjectsCount
,(SELECT COUNT(1) FROM Users WHERE CompanyID = COM.ComID) AS ClientsCount
FROM [Companys] COM ", strOrderby);
string strWherePageModel = @") NEW_TB WHERE INDEX_ID BETWEEN @Strat AND @End;";
StringBuilder strWhere = new StringBuilder();
strWhere.Append(" WHERE 1=1 ");
switch (request.SearchType)
{
case SearchCompanyType.SingleCompany:
strWhere.Append(" AND [ComID]= @ComID ");
break;
case SearchCompanyType.Project:
strWhere.Append(" AND [ComID] IN (SELECT CompanyID FROM Projects WHERE ProjectID =@ProjectID) ");
break;
case SearchCompanyType.User:
strWhere.Append(" AND [ComID] IN (SELECT CompanyID FROM Users WHERE UserID =@UserID) ");
break;
case SearchCompanyType.List:
strWhere.Append(" AND (CompanyName like @CompanyName or State like @CompanyName or City like @CompanyName ) ");
break;
case SearchCompanyType.All:
break;
default:
break;
}
StringBuilder strSql = new StringBuilder();
if (request.IsPageModel)
{
strSql.Append(strSelCount);
strSql.Append(strWhere);
strSql.Append(";");
strSql.Append(strSelPageModel);
strSql.Append(strWhere);
strSql.Append(strWherePageModel);
}
else
{
strSql.Append(strSelAttrs);
strSql.Append(strWhere);
strSql.Append(strSelAttrsOrderBy);
strSql.Append(";");
}
SearchCompaniesResponse response = new SearchCompaniesResponse();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "UserID", DbType.Int32, request.UserID);
db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, request.ProjectID);
db.AddInParameter(dbCommand, "ComID", DbType.Int32, request.ComID);
db.AddInParameter(dbCommand, "CompanyName", DbType.String, string.Format("%{0}%", request.CompanyName.FilterSqlString()));
db.AddInParameter(dbCommand, "Strat", DbType.Int32, start);
db.AddInParameter(dbCommand, "End", DbType.Int32, end);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
List <CompanysEntity> list = new List <CompanysEntity>();
if (request.IsPageModel)
{
if (dataReader.Read())
{
response.ResultCount = dataReader.GetInt32(0);
dataReader.NextResult();
}
}
while (dataReader.Read())
{
list.Add(CompanysEntity.ReaderBind(dataReader, true));
}
response.ResultList = list;
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(response);
}