public Result Delete(FileDetailDto fileDetail)
{
var result = new Result();
var item = FileDetailRepository.Find(x => x.Id == fileDetail.Id);
FileDetailRepository.Delete(item);
return(result);
}
public CreateFileDetailDtoResult Create(FileDetailDto fileDetail)
{
var result = new CreateFileDetailDtoResult();
var item = Mapper.Map <FileDetail>(fileDetail);
FileDetailRepository.AddAsync(item);
return(result);
}
public Result Update(FileDetailDto fileDetail)
{
var result = new Result();
var item = Mapper.Map <FileDetail>(fileDetail);
FileDetailRepository.UpdateAsync(item, fileDetail.Id);
return(result);
}
public List <FileDetailDto> GetFileList(SearchFilesRequest request)
{
int start = request.CurrentPage * request.PageCount + 1 - request.PageCount;
int end = request.CurrentPage * request.PageCount;
string selPageModel = string.Format(@"SELECT * FROM(
Select ROW_NUMBER() OVER( ORDER BY {0} {1}) AS INDEX_ID ", request.OrderByExpression, request.OrderByDirection)
+ @",SearchResult.* From ({0}) as SearchResult
) as New_TB
Where INDEX_ID between @Start and @End
";
// string selPageModel = @"SELECT * FROM(
// Select ROW_NUMBER() OVER( ORDER BY FileTitle ASC) AS INDEX_ID,SearchResult.* From ({0}) as SearchResult
//
//
// ) as New_TB
// Where INDEX_ID between @Start and @End
// ";
string selCount = @"SELECT Count(1) FROM ({0}) as SearchResult ;";
StringBuilder strNoPageModal = new StringBuilder();
//strNoPageModal.Append(@"SELECT f.*,c.CompanyName,p.Title as ProjectTitle,t.Title as TicketTitle ,t.TicketCode + cast(t.TicketID as varchar) as TicketCode,fb.Title as FeedBackTitle
// ,u.FirstName,u.LastName
// FROM [Files] f left join Companys c on c.ComID=f.CompanyID
// left join Projects p on f.ProjectId=p.ProjectID
// left join Tickets t on f.TicketId=t.TicketID
// left join WorkRequest wr on f.WorkRequestId=wr.WorkRequestId
// left join FeedBacks fb on f.FeedbackId=fb.FeedBackID
// left join Users u on f.CreatedBy=u.UserID");
strNoPageModal.Append(@"SELECT f.*,c.CompanyName,p.Title as ProjectTitle,t.Title as TicketTitle ,t.TicketCode + cast(t.TicketID as varchar) as TicketCode,fb.Title as FeedBackTitle
,u.FirstName,u.LastName
FROM [Files] f left join Companys c on c.ComID=f.CompanyID
left join Projects p on f.ProjectId=p.ProjectID
left join Tickets t on f.TicketId=t.TicketID
left join FeedBacks fb on f.FeedbackId=fb.FeedBackID
left join Users u on f.CreatedBy=u.UserID");
strNoPageModal.Append(@" Where 1=1 AND f.Isdelete=0 AND ( f.TicketID=0 or t.Status>0 ) ");
if (request.IsPublic)
{
strNoPageModal.Append(@" And f.IsPublic=@IsPublic ");
}
switch (request.SearchType)
{
case SearchFileType.Company:
{
if (request.CompanyID > 0)
{
strNoPageModal.Append(" And f.CompanyID=@ComapnyID");
}
else
{
strNoPageModal.Append(" And f.CompanyID>1 ");
}
strNoPageModal.AppendFormat(" And f.SourceType={0} ", ((int)FileSourceType.Company).ToString());
if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "")
{
strNoPageModal.Append(" AND f.FileTitle like @Keyword ");
}
}
break;
case SearchFileType.Project:
{
if (request.CompanyID > 0)
{
strNoPageModal.Append(" And f.CompanyID=@ComapnyID");
}
if (request.ProjectID > 0)
{
strNoPageModal.Append(" and f.ProjectId=@ProjectID ");
}
strNoPageModal.AppendFormat(" And f.SourceType={0} ", ((int)FileSourceType.Project).ToString());
if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "")
{
strNoPageModal.Append(" AND f.FileTitle like @Keyword ");
}
}
break;
case SearchFileType.WorkRequest:
{
if (request.CompanyID > 0)
{
strNoPageModal.Append(" And f.CompanyID=@ComapnyID");
}
if (request.ProjectID > 0)
{
strNoPageModal.Append(" and f.ProjectId=@ProjectID ");
}
strNoPageModal.AppendFormat(" And f.SourceType in ({0},{1}) ", ((int)FileSourceType.WorkRequest).ToString(), ((int)FileSourceType.WorkRequestScope).ToString());
//if (request.UserID != 0)
//{
// strNoPageModal.AppendFormat(" AND wr.ProjectID IN (select ProjectID from ProjectUsers where UserId={0})", request.UserID);
//}
if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "")
{
strNoPageModal.Append(" AND f.Tags like @Keyword ");
}
}
break;
case SearchFileType.TicketAndFeedback:
{
if (request.CompanyID > 0)
{
strNoPageModal.Append(" And f.CompanyID=@ComapnyID");
}
if (request.ProjectID > 0)
{
strNoPageModal.Append(" and f.ProjectId=@ProjectID ");
}
strNoPageModal.AppendFormat(" And (f.SourceType={0} or f.SourceType={1}) ", ((int)FileSourceType.Ticket).ToString(), ((int)FileSourceType.FeedBack).ToString());
strNoPageModal.AppendFormat(" AND f.ProjectId in (select ProjectId from ProjectUsers h where h.UserID= {0} )", request.UserID);
if (request.KeywordType == SearchKeywordType.TicketCode)
{
if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "")
{
int ticketcode = 0;
int.TryParse(request.Keyword, out ticketcode);
if (ticketcode > 0)
{
strNoPageModal.AppendFormat(@" AND t.TicketID = {0} ", ticketcode);
}
}
}
else if (request.KeywordType == SearchKeywordType.TicketTitle)
{
if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "")
{
strNoPageModal.Append(@" AND t.Title like @Keyword ");
}
}
else if (request.KeywordType == SearchKeywordType.FileName)
{
if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "")
{
strNoPageModal.Append(" AND f.FileTitle like @Keyword ");
}
}
else if (request.KeywordType == SearchKeywordType.All)
{
if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "")
{
strNoPageModal.Append(@" AND ( t.Title like @Keyword or f.FileTitle like @Keyword ");
int ticketcode = 0;
int.TryParse(request.Keyword, out ticketcode);
if (ticketcode > 0)
{
strNoPageModal.AppendFormat(@" or t.TicketID = {0} ", ticketcode);
}
strNoPageModal.Append(")");
}
}
}
break;
default: break;
}
StringBuilder strSql = new StringBuilder();
if (request.IsPageModel)
{
strSql.AppendFormat(selCount, strNoPageModal.ToString());
strSql.AppendFormat(selPageModel, strNoPageModal.ToString());
}
else
{
strSql.Append(strNoPageModal);
}
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "Keyword", DbType.String, string.Format("%{0}%", request.Keyword.FilterSqlString()));
db.AddInParameter(dbCommand, "ComapnyID", DbType.Int32, request.CompanyID);
db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, request.ProjectID);
db.AddInParameter(dbCommand, "IsPublic", DbType.Boolean, request.IsPublic);
db.AddInParameter(dbCommand, "Start", DbType.Int32, start);
db.AddInParameter(dbCommand, "End", DbType.Int32, end);
List <FileDetailDto> list = new List <FileDetailDto>();
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
if (request.IsPageModel)
{
if (dataReader.Read())
{
request.RecordCount = dataReader.GetInt32(0);
}
dataReader.NextResult();
}
while (dataReader.Read())
{
list.Add(FileDetailDto.ReaderBind(dataReader));
}
return(list);
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:GetFileList = {0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(null);
}
}
}
