/// <summary>
/// 5：待审核资源 数量统计
/// </summary>
/// <param name="model"></param>
/// <param name="user_id">用户id</param>
/// <returns></returns>
public int GetTableApproveDocCount(DocTableParamModel model, int user_id)
{
sb?.Clear();
sb.Append(@"SELECT COUNT(*) FROM
(SELECT file_id FROM T_DocumentFolderRelation WHERE folder_id=@folder_id) ration INNER JOIN
(SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status=1 ");
//文档名称
if (!string.IsNullOrEmpty(model.keyword))
{
sb.Append(string.Format(" AND (file_name like '%@keyword%' OR create_name like '%@keyword%') "));
}
if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
{
sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
}
else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
{
sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
}
else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
{
sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
}
sb.Append(@") info ON ration.file_id=info.file_id;");
_param.Clear();
_param.Add("@folder_id", model.folder_id);
if (!string.IsNullOrEmpty(model.keyword))
{
_param.Add("@keyword", model.keyword);
}
return(Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text, SqlHelper.GetParameters(_param))));
}
/// <summary>
/// 统计文档节点下的文档数量
/// </summary>
/// <param name="doc_type">节点类型</param>
/// <param name="folder_id">节点ID</param>
/// <returns></returns>
private int GetFileInfoCount(int doc_type, int folder_id)
{
int total = 0;
int user_id = HCQ2UI_Helper.OperateContext.Current.Usr.user_id;
var dal = DBSession.IT_DocumentInfoDAL;
DocTableParamModel model = new DocTableParamModel {
folder_id = folder_id
};
switch (doc_type)
{
case 1: total = dal.GetTableByOwnShareDocCount(model, user_id); break;
case 2: {
List <int> roles = HCQ2UI_Helper.Session.SysPermissSession.RolesList;
total = dal.GetTableShareByOwnDocCount(model, user_id, roles);
} break;
case 3: total = dal.GetTablePublicDocCountByOwn(model, user_id); break; //公用5
case 4: total = dal.GetTableRemoveDocCount(model, user_id); break;
case 5: total = dal.GetTableApproveDocCount(model, user_id); break; //待审核1
default: total = dal.GetTableByOwnDocCount(model, user_id); break;
}
return(total);
}
/// <summary>
/// 2. 分享文档
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List <DocTreeResultModel> GetTableShareByOwnDoc(DocTableParamModel model, int user_id, List <int> roles)
{
string _roles = (roles.Count > 0) ? string.Join(",", roles) : "0";
sb?.Clear();
sb.Append(string.Format(@"SELECT TOP {0} info.file_id,info.file_name,info.alias_name,info.file_type,info.file_size,info.issue_start,info.issue_end,info.create_time,info.create_name,info.file_note,info.url,doc_type=(CASE WHEN ISNULL(code.doc_type,'')='' THEN info.doc_type ELSE code.doc_type END),doc_number,info.file_money,info.down_number,codeType.file_classify,info.file_status FROM
(SELECT * FROM
(SELECT file_id FROM T_DocumentSetType WHERE folder_id=@folder_id AND role_id IN({1})
UNION
SELECT file_id FROM T_DocumentSetType WHERE folder_id=@folder_id AND user_id=@user_id
)a
) share INNER JOIN
(SELECT file_id,file_name,alias_name,file_type,file_size=(CASE WHEN file_size>999 THEN CAST(CAST(file_size/1024 AS DECIMAL(10,2)) AS NVARCHAR(20))+'M' ELSE CAST(file_size AS NVARCHAR(20))+'KB' END),
CONVERT(varchar(100),issue_start,23) AS issue_start,CONVERT(varchar(100),issue_end,23) AS issue_end,CONVERT(varchar(100),create_time,23) AS create_time,
issue_name,create_name,file_note,attach_url AS url,ROW_NUMBER() OVER(order by file_id asc) as DispOrder,create_id,doc_type,doc_number,file_money,down_number,file_classify,file_status FROM dbo.T_DocumentInfo WHERE if_remove=0", model.rows, _roles));
//文档名称
if (!string.IsNullOrEmpty(model.keyword))
{
sb.Append(string.Format(" AND (file_name like '%@keyword%' OR create_name like '%@keyword%') "));
}
if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
{
sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
}
else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
{
sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
}
else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
{
sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
}
sb.Append(string.Format(@") info
ON info.file_id=share.file_id LEFT JOIN
(SELECT item2.doc_type,item2.issue_value FROM
(SELECT item_id,item_code FROM dbo.T_ItemCode WHERE item_code='docType') item1 INNER JOIN
(SELECT code_name AS doc_type,code_value AS issue_value,item_id FROM dbo.T_ItemCodeMenum) item2 ON item1.item_id = item2.item_id) code
ON info.doc_type=code.issue_value LEFT JOIN (SELECT item2.file_classify,item2.file_classify_value FROM
(SELECT item_id,item_code FROM dbo.T_ItemCode WHERE item_code='FileClassify') item1 INNER JOIN
(SELECT code_name AS file_classify,code_value AS file_classify_value,item_id FROM dbo.T_ItemCodeMenum) item2 ON item1.item_id = item2.item_id) codeType ON
info.file_classify=codeType.file_classify_value WHERE info.DispOrder>{0};", (model.page - 1) * model.rows));
_param?.Clear();
_param.Add("@folder_id", model.folder_id);
_param.Add("@user_id", user_id);
if (!string.IsNullOrEmpty(model.keyword))
{
_param.Add("@keyword", model.keyword);
}
DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, SqlHelper.GetParameters(_param));
return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <DocTreeResultModel>(dt));
}
/// <summary>
/// 获取Table数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List <DocTreeResultModel> GetTableData(DocTableParamModel model, out int total)
{
total = 0;
if (null == model)
{
return(null);
}
List <DocTreeResultModel> list = new List <DocTreeResultModel>();
int user_id = HCQ2UI_Helper.OperateContext.Current.Usr.user_id;
IT_DocumentInfoDAL dal = DBSession.IT_DocumentInfoDAL;
switch (model.doc_type)
{
case 1: {
list = dal.GetTableByOwnShareDoc(model, user_id);
total = dal.GetTableByOwnShareDocCount(model, user_id);
} break;
case 2: {
List <int> roles = HCQ2UI_Helper.Session.SysPermissSession.RolesList;
list = dal.GetTableShareByOwnDoc(model, user_id, roles);
total = dal.GetTableShareByOwnDocCount(model, user_id, roles);
} break;
case 3: {
list = dal.GetTablePublicDoc(model, user_id);
total = dal.GetTablePublicDocCount(model, user_id);
} break;
case 4: {
list = dal.GetTableRemoveDoc(model, user_id);
total = dal.GetTableRemoveDocCount(model, user_id);
} break;
case 5: {
//待审核资源
list = dal.GetTableApproveDoc(model, user_id);
total = dal.GetTableApproveDocCount(model, user_id);
} break;
default: {
list = dal.GetTableByOwnDoc(model, user_id);
total = dal.GetTableByOwnDocCount(model, user_id);
} break;
}
if (null == list)
{
return(null);
}
return(list);
}
public ActionResult InitTable(DocTableParamModel model)
{
if (model.folder_id == 0)
{
return(null);
}
int total = 0;
model.user_id = operateContext.Usr.user_id;
List <DocTreeResultModel> list = operateContext.bllSession.T_DocumentInfo.GetTableData(model, out total);
TableModel tModel = new TableModel()
{
total = total,
rows = list
};
return(Json(tModel, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 2. 分享文档 数量统计
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int GetTableShareByOwnDocCount(DocTableParamModel model, int user_id, List <int> roles)
{
string _roles = (roles.Count > 0) ? string.Join(",", roles) : "0";
sb?.Clear();
sb.Append(string.Format(@"SELECT COUNT(*) FROM
(SELECT * FROM
(SELECT file_id FROM T_DocumentSetType WHERE folder_id=@folder_id AND role_id IN({0})
UNION
SELECT file_id FROM T_DocumentSetType WHERE folder_id=@folder_id AND user_id=@user_id
)a
) share INNER JOIN
(SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 ", _roles));
//文档名称
if (!string.IsNullOrEmpty(model.keyword))
{
sb.Append(string.Format(" AND (file_name like '%@keyword%' OR create_name like '%@keyword%') "));
}
if (!string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
{
sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) AND issue_start<=CAST('{1}' AS DATE) ", model.issue_start, model.issue_end));
}
else if (!string.IsNullOrEmpty(model.issue_start) && string.IsNullOrEmpty(model.issue_end))
{
sb.Append(string.Format(" AND issue_start>=CAST('{0}' AS DATE) ", model.issue_start));
}
else if (string.IsNullOrEmpty(model.issue_start) && !string.IsNullOrEmpty(model.issue_end))
{
sb.Append(string.Format(" AND issue_end<=CAST('{0}' AS DATE) ", model.issue_end));
}
sb.Append(@") info
ON info.file_id=share.file_id;");
_param?.Clear();
_param.Add("@folder_id", model.folder_id);
_param.Add("@user_id", user_id);
if (!string.IsNullOrEmpty(model.keyword))
{
_param.Add("@keyword", model.keyword);
}
return(Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text, SqlHelper.GetParameters(_param))));
}