private void CreateDocumentView(WIKIDbContext context)
{
string delete_view = @"
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[view_Document]') AND type in (N'V'))
DROP VIEW [dbo].[view_Document]
";
string create_view = @"
CREATE VIEW [dbo].[view_Document]
AS
SELECT d.[Id]
,[ContentNo]
,[Title]
,[Sticky]
,[Important]
,[Description]
,d.[CreatedTime]
,d.CreatedBy
,a.FullName as CreatedBy_FullName
,a.Department as CreatedBy_Department
,d.[UpdatedTime]
,d.[UpdatedBy]
FROM [WIKI].[dbo].[Documents] as d
join Accounts as a on a.UserName = d.CreatedBy
";
context.Database.ExecuteSqlCommand(delete_view);
context.Database.ExecuteSqlCommand(create_view);
}
private void CreateLogTable(WIKIDbContext context)
{
// 不存在日志表则增加
string createLogSql = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Logs]') AND type IN (N'U')) " +
"BEGIN " +
" CREATE TABLE [dbo].[Logs]( " +
" [Id] [INT] IDENTITY(1,1) NOT NULL, " +
" [Level] [NVARCHAR](128) NULL, " +
" [TimeStamp] [DATETIMEOFFSET](7) NOT NULL, " +
" [Exception] [NVARCHAR](MAX) NULL, " +
" [LogEvent] [NVARCHAR](200) NULL, " +
" [LogType] [INT] NULL, " +
" [UserId] [NVARCHAR](100) NULL, " +
" [UserName] [NVARCHAR](200) NULL, " +
" [FullName] [NVARCHAR](200) NULL, " +
" [IP] [NVARCHAR](100) NULL, " +
" [RefUrl] [NVARCHAR](4000) NULL, " +
" [Url] [NVARCHAR](4000) NULL, " +
" [Module] [NVARCHAR](200) NULL, " +
" [Action] [NVARCHAR](200) NULL, " +
" [HttpMethod] [NVARCHAR](50) NULL, " +
" [Content] [NVARCHAR](MAX) NULL, " +
" CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED " +
" ( " +
" [Id] ASC " +
" )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] " +
" ) ON [PRIMARY] " +
"END ";
context.Database.ExecuteSqlCommand(createLogSql);
}
public IHttpActionResult Get(ODataQueryOptions <QuestionView> queryOptions)
{
var Db = new WIKIDbContext();
return(Ok <IEnumerable <QuestionView> >(Db.Set <QuestionView>().AsQueryable()));
}
//[EnableQuery(AllowedArithmeticOperators = System.Web.OData.Query.AllowedArithmeticOperators.All, AllowedLogicalOperators = AllowedLogicalOperators.All, MaxNodeCount = 1000)]
public IHttpActionResult Search(ODataQueryOptions <Tag> queryOptions)
{
var Db = new WIKIDbContext();
var query = queryOptions.ApplyTo(Db.Tag).Cast <Tag>()
.Select(m => m.Value)
.Distinct();
return(Json(query.ToList()));
}
private Task OnValidateIdentityHandle(OAuthValidateIdentityContext context)
{
var Db = new WIKIDbContext();
var cache = MemoryCache.Default;
CacheItemPolicy policy = new CacheItemPolicy();
policy.AbsoluteExpiration = DateTimeOffset.FromUnixTimeSeconds(60 * 30);
var userId = Guid.Parse(context.Ticket.Identity.FindFirst("sub").Value);
var obj = cache.Get(userId.ToString());
if (obj == null)
{
var entity = Db.Account.Where(m => m.WUCC_UserId == userId).FirstOrDefault();
if (entity == null)
{
entity = new Account
{
WUCC_UserId = userId,
Department = context.Ticket.Identity.Claims.FirstOrDefault(m => m.Type == "SalesDepartment").Value,
UserName = context.Ticket.Identity.Name,
FullName = context.Ticket.Identity.Claims.FirstOrDefault(m => m.Type == "FullName").Value,
};
Db.Account.Add(entity);
}
else
{
entity.Department = context.Ticket.Identity.Claims.FirstOrDefault(m => m.Type == "SalesDepartment").Value;
entity.UserName = context.Ticket.Identity.Name;
entity.FullName = context.Ticket.Identity.Claims.FirstOrDefault(m => m.Type == "FullName").Value;
}
Db.SaveChanges();
cache.Add(userId.ToString(), entity, policy);
}
return(Task.FromResult(0));
}
//[EnableQuery(AllowedArithmeticOperators = System.Web.OData.Query.AllowedArithmeticOperators.All, AllowedLogicalOperators = AllowedLogicalOperators.All, MaxNodeCount = 1000)]
public IHttpActionResult SearchQuestion(ODataQueryOptions <Content> queryOptions)
{
var Db = new WIKIDbContext();
var contentType = ContentTypeEnum.Question.ToString();
var query = from q in queryOptions.ApplyTo(Db.Content).Cast <Content>()
join d in Db.Question on q.Id equals d.Id
from a in Db.Account.Where(m => m.UserName == d.CreatedBy).DefaultIfEmpty()
where q.ContentType == contentType
select new
{
Question = d,
ExpandProperty = new ExpandPropertyDto
{
CreatedBy = a.FullName,
CreatedByDepartment = a.Department
},
Tags = Db.Tag.Where(m => m.ContentId == d.Id).Select(m => m.Value)
};
var result = query.ToList().Select(m =>
{
var dto = m.Question.MapToDto();
dto.ExpandProperty = m.ExpandProperty;
return(new
{
Question = dto,
Tags = m.Tags
});
});
long?count = 0;
if (queryOptions.Count != null)
{
count = queryOptions.Request.ODataProperties().TotalCount;
}
return(Json(new { Datas = result, Count = count }));
}
private void CreateQuestionView(WIKIDbContext context)
{
string delete_question_view = @"
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[view_Question]') AND type in (N'V'))
DROP VIEW [dbo].[view_Question]
";
string create_question_view = @"
CREATE VIEW [dbo].[view_Question]
AS
SELECT q.[Id]
,q.[ContentNo]
,q.[Title]
,q.[Text]
,[Sticky]
,[Important]
,q.[CreatedTime]
,q.[UpdatedTime]
,q.UpdatedBy
,q.CreatedBy
,cb.FullName as CreatedBy_FullName
,cb.Department as CreatedBy_Department
,a.Id as Answer_Id
,a.Text as Answer_Text
,a.CreatedTime as Answer_CreatedTime
,a.UpdatedTime as Answer_UpdatedTime
,a.UpdatedBy as Answer_UpdatedBy
,a.CreatedBy as Answer_CreatedBy
,rb.FullName as Answer_CreatedBy_FullName
,rb.Department as Answer_CreatedBy_Department
FROM [WIKI].[dbo].[Questions] as q
left join Answers as a on a.QuestionId = q.Id
left join Accounts as cb on cb.UserName = q.CreatedBy
left join Accounts as rb on rb.UserName = a.CreatedBy
";
context.Database.ExecuteSqlCommand(delete_question_view);
context.Database.ExecuteSqlCommand(create_question_view);
}
