/// <summary>
/// 根据审批类型，单据类型，审批流程编码获取审批流程
/// </summary>
/// <param name="approvalTypeId">审批类型id</param>
/// <param name="bType">单据类型</param>
/// <param name="procCode">审批流程编码</param>
/// <returns></returns>
public GAppvalProcModel GetAppvalProc(long approvalTypeId, string bType, string procCode)
{
GAppvalProcModel procModel = null;
SqlDao sqlDao = new SqlDao();
DataTable dataTable = sqlDao.GetAppvalProc(approvalTypeId, bType, procCode);
if (dataTable == null || dataTable.Rows.Count == 0)
{
procModel = new GAppvalProcModel();
}
else
{
IList <GAppvalProcModel> procModels = DCHelper.DataTable2List <GAppvalProcModel>(dataTable);
List <Organize> organizes = new List <Organize>();
foreach (GAppvalProcModel model in procModels)
{
Organize organize = new Organize();
organize.OrgId = model.OrgPhid;
organize.OrgCode = model.OrgCode;
organize.OrgName = model.OrgName;
organizes.Add(organize);
}
procModel = procModels[0];
procModel.Organizes = organizes;
}
return(procModel);
}
public string GetNote()
{
string str = this.Request.QueryString["id"];
if (str.IsNullOrEmpty())
{
return("");
}
Organize organize = new Organize();
Users users = new Users();
if (str.StartsWith("u_"))
{
Guid guid = users.RemovePrefix1(str).ToGuid();
return(organize.GetAllParentNames(users.GetMainStation(guid), false, " / ") + " / " + users.GetName(guid));
}
if (str.StartsWith("w_"))
{
return(new WorkGroup().GetUsersNames(WorkGroup.RemovePrefix(str).ToGuid(), '、'));
}
Guid test;
if (str.IsGuid(out test))
{
return(organize.GetAllParentNames(test, false, " / "));
}
return("");
}
/// <summary>
/// Create movie list every x minutes or hours, depends how it is set up
/// </summary>
/// <returns>null</returns>
public static async Task <bool> List()
{
try
{
await History.Create(History.Type.API, new History_API()
{
api_action = "Creating new movie list",
api_type = "Task -> status",
api_datetime = DateTime.Now
});
AllMovies = await db.Movie_Data.Select(x => x).OrderByDescending(x => x.FileCreationDate).ToListAsync();
Organize.ByDate();
await History.Create(History.Type.API, new History_API()
{
api_action = "Waiting for next movie list cycle ",
api_type = "Task -> waiting",
api_datetime = DateTime.Now
});
return(true);
}
catch (Exception e)
{
await History.Create(History.Type.API, new History_API()
{
api_action = "Exception thrown at -> Movie.Create.List() | Error -> " + e.Message,
api_type = "Task -> Error | Exception caught -> Movie.Create.List()",
api_datetime = DateTime.Now
});
return(false);
}
}
public int AddOrganize(Organize org)
{
org.Create();
var ret = organize.Insert(org);
return(ret);
}
public int ModifyOrganize(Organize org, string keyValue)
{
org.Modify(keyValue);
var ret = organize.Update(org);
return(ret);
}
public OrganizeOutputDto GetForm(string keyValue)
{
var id = Convert.ToInt64(keyValue);
OrganizeOutputDto organizeOutputDto = new OrganizeOutputDto();
Organize organize = organizeRepository.Get(id);
AutoMapper.Mapper.Map <Organize, OrganizeOutputDto>(organize, organizeOutputDto);
return(organizeOutputDto);
}
public void DeleteForm(string keyValue)
{
var id = Convert.ToInt64(keyValue);
Organize organize = organizeRepository.Get(id);
organize.DeletedMark = true;
organize.DeletionTime = DateTime.Now;
organizeRepository.Update(organize);
}
public bool UpdateOrganize([FromBody] OrganizeModel model)
{
IAccountLogic IComponent = container.Resolve <IAccountLogic>();
Mapper.CreateMap <OrganizeModel, Organize>(); // 配置
Organize organize = Mapper.Map <OrganizeModel, Organize>(model); // 使用AutoMapper自动映射
return(IComponent.UpdateOrganize(organize));
}
public OrganizeModel GetOrganize(Guid id)
{
IAccountLogic IComponent = container.Resolve <IAccountLogic>();
Organize organize = IComponent.GetOrganize(id);
Mapper.CreateMap <Organize, OrganizeModel>(); // 配置
OrganizeModel model = Mapper.Map <Organize, OrganizeModel>(organize); // 使用AutoMapper自动映射
return(model);
}
/// <summary>
/// 保存机构表单（新增、修改）
/// </summary>
/// <param name="keyValue">主键值</param>
/// <param name="organizeEntity">机构实体</param>
/// <returns></returns>
public object SaveForm(Organize organize)
{
if (!string.IsNullOrEmpty(organize.OrganizeId))
{
return(_organize.Update(organize));
}
else
{
organize.OrganizeId = new Guid().ToString();
return(_organize.Insert(organize));
}
}
/// <summary>
/// 查询记录
/// </summary>
/// <param name="inputDto">查询实体</param>
/// <returns></returns>
public PagerResult <Dtos.GetDepartmentOutputDTO> GetDepartment(Dtos.GetDepartmentInputDTO inputDto)
{
var pagerWrapper = new PagerResult <Dtos.GetDepartmentOutputDTO>();
var table = from c in _deptRepository.GetAll()
join p in _deptRepository.GetAll()
on c.ParentId equals p.Id into Parent
from P in Parent.DefaultIfEmpty()
join o in _organizeRepository.GetAll()
on c.OrganizeId equals o.Id into Organize
from O in Organize.DefaultIfEmpty()
join mu in _userRepository.GetAll()
on c.ManagerId equals mu.Id into Manager
from MU in Manager.DefaultIfEmpty()
join cu in _userRepository.GetAll()
on c.Creator equals cu.Id into Creator
from CU in Creator.DefaultIfEmpty()
join uu in _userRepository.GetAll()
on c.LastModifier equals uu.Id into LastModifier
from UU in LastModifier.DefaultIfEmpty()
select new Dtos.GetDepartmentOutputDTO
{
Id = c.Id.ToString(),
Name = c.Name,
Email = c.Email,
ManagerId = c.ManagerId.ToString(),
Fax = c.Fax,
Layer = c.Layer,
ParentId = c.ParentId,
ParentName = P.Name,
OrganizeName = O.Name,
Phone = c.Phone,
CreateTime = c.CreateTime,
Creator = CU == null ? "" : CU.UserName,
Description = c.Description,
OrganizeId = c.OrganizeId,
LastModifier = UU == null ? "" : UU.UserName,
LastModifyTime = c.LastModifyTime,
SortCode = c.SortCode,
Manager = MU == null ? "" : MU.UserName,
};
var result = table
.WhereIf(!inputDto.DepartmentName.IsNullOrEmpty(), p => p.Name.Contains(inputDto.DepartmentName))
.WhereIf(inputDto.OrganizeId.HasValue, p => p.OrganizeId == inputDto.OrganizeId)
.WhereIf(!inputDto.Manager.IsNullOrEmpty(), p => p.Manager.Contains(inputDto.Manager))
.WhereIf(inputDto.ParentId.HasValue, p => p.ParentId == inputDto.ParentId)
.Page(inputDto, pagerWrapper)
.ToList();
pagerWrapper.Result = result;
return(pagerWrapper);
}
public ActionResult ModifyOrganize(Organize org, string keyValue)
{
var ret = organizeApp.ModifyOrganize(org, keyValue);
if (ret == 1)
{
return(Success("修改机构成功"));
}
else
{
return(Error("修改机构失败"));
}
}
public ActionResult AddOrganize(Organize org)
{
var ret = organizeApp.AddOrganize(org);
if (ret == 1)
{
return(Success("添加机构成功"));
}
else
{
return(Error("添加机构失败"));
}
}
public bool InsertOrganize(Organize model)
{
using (IDbConnection conn = OpenConnection())
{
Guid oID = conn.Insert <Guid>(model);
if (oID == Guid.Empty || oID == null)
{
return(false);
}
else
{
return(true);
}
}
}
public bool UpdateOrganize(Organize model)
{
using (IDbConnection conn = OpenConnection())
{
int row = conn.Update(model);
if (row > 0)
{
return(true);
}
else
{
return(false);
}
}
}
public int DeleteOrganize(string keyValue)
{
var userCount = user.IQueryable(x => x.OrgID == keyValue).Count();
if (userCount > 0)
{
return(0);
}
else
{
Organize org = new Organize();
org.ID = keyValue;
org.Remove();
var ret = organize.Update(org);
return(ret);
}
}
/// <summary>
/// 获取审批流程明细
/// </summary>
/// <param name="approvalTypeId">审批类型id</param>
/// <param name="bType">单据类型</param>
/// <param name="proc_code">审批流程编码</param>
/// <param name="orgids">组织id集合</param>
/// <returns></returns>
public GAppvalProcModel GetProcDetail(long approvalTypeId, string bType, string proc_code, List <long> orgids)
{
//获取审批流程
List <GAppvalProcModel> procModels = GAppvalProcFacade.GetAppvalProc(orgids, bType, approvalTypeId);
if (procModels == null || procModels.Count == 0)
{
throw new Exception("审批流程为空！");
}
else
{
procModels = procModels.FindAll(t => t.FCode == proc_code);
}
List <Organize> organizes = new List <Organize>();
foreach (GAppvalProcModel model in procModels)
{
Organize organize = new Organize();
organize.OrgId = model.OrgPhid;
organize.OrgCode = model.OrgCode;
organize.OrgName = model.OrgName;
organizes.Add(organize);
}
GAppvalProcModel procModel = procModels[0];
procModel.Organizes = organizes;
//获取审批流程对应的审批岗位
List <GAppvalPostModel> postModels = GAppvalPostFacade.FindAppvalPostByProcID(procModel.PhId);
procModel.PostModels = postModels;
//获取审批流程的启用条件
FindedResults <GAppvalProcCondsModel> findedResults = GAppvalProcCondsFacade.Find(t => t.ProcPhid == procModel.PhId);
if (findedResults != null && findedResults.Data != null && findedResults.Data.Count > 0)
{
List <GAppvalProcCondsModel> procCondsModels = findedResults.Data.ToList();
procModel.CondsModels = procCondsModels;
}
return(procModel);
}
/// <summary>
/// 获取产品类别
/// </summary>
/// <param name="productClassID">产品类别ID，有值则选择当前值</param>
/// <param name="isEdit">是否可以编辑</param>
/// <returns></returns>
//public ActionResult GetProductClass(string productClassID, bool? isEdit, string name, string errMsg = "产品类别不能为空！")
//{
// ViewData["productClassID"] = productClassID;
// ViewData["errMsg"] = errMsg;
// ViewData["name"] = name ?? "productClassID";
// ViewData["isEdit"] = isEdit.GetValueOrDefault(true);
// ProductClassEntity productClassEntity = new ProductClassEntity();
// productClassEntity.ResetForSearch();
// productClassEntity.PageSize = 0;
// return View(productClassEntity.ToList<ProductClassEntity>());
//}
/// <summary>
/// 返回产品类型
/// </summary>
/// <param name="productClassID">产品类别</param>
/// <param name="productTypeID">产品类型ID，有值则选择当前值</param>
/// <param name="isEdit">是否可以编辑</param>
/// <param name="name">html控件的Name</param>
/// <param name="textName">下拉框显示的列表，默认为中文类型</param>
/// <returns></returns>
//public ActionResult GetProductType(string productClassID, string productTypeID, bool? isEdit, string name, string textName, string errMsg = "产品类型不能为空！")
//{
// ViewData["productTypeID"] = productTypeID;
// ViewData["errMsg"] = errMsg;
// ViewData["name"] = name ?? "productTypeID";
// ViewData["isEdit"] = isEdit.GetValueOrDefault(true);
// ViewData["textName"] = textName ?? "ProductTypeName";
// ProductTypeEntity productTypeEntity = new ProductTypeEntity();
// productTypeEntity.ResetForSearch();
// productTypeEntity.PageSize = 0;
// // productTypeEntity.ProductClassID = productClassID;
// productTypeEntity.ProductTypeID = productTypeID;
// return View(productTypeEntity.ToList<ProductTypeEntity>());
//}
/// <summary>
/// 获取产品列表
/// </summary>
/// <param name="productNo">产品编码ID，有值则选中</param>
/// <param name="productClassID">产品类别ID</param>
/// <param name="productTypeID">产品类型ID</param>
/// <param name="isEdit"></param>
/// <returns></returns>
//public ActionResult GetProduct(string productNo, string productClassID, string productTypeID, bool? isEdit, string name, string type, string errMsg = "产品名称不能为空！")
//{
// ViewData["productNo"] = productNo;
// ViewData["isEdit"] = isEdit.GetValueOrDefault(true);
// ViewData["name"] = name ?? "ProductNo";
// ViewData["type"] = type;
// ViewData["errMsg"] = errMsg;
// ProductEntity productEntity = new ProductEntity();
// productEntity.ResetForSearch();
// productEntity.PageSize = 0;
// productEntity.ProductClassID = productClassID;
// // productEntity.ProductTypeID = productTypeID;
// productEntity.ProductNo = productNo;
// return View(productEntity.ToList<ProductEntity>());
//}
/// <summary>
/// 获取产品阶段
/// </summary>
/// <param name="ProductStageID">产品阶段ID，有值则选择当前值</param>
/// <param name="isEdit">是否可以编辑</param>
/// <returns></returns>
//public ActionResult GetProductStage(string ProductStageID, bool? isEdit)
//{
// ViewData["ProductStageID"] = ProductStageID;
// ViewData["isEdit"] = isEdit.GetValueOrDefault(true);
// ProductStageEntity productStageEntity = new ProductStageEntity();
// productStageEntity.ResetForSearch();
// productStageEntity.ProductStageID = ProductStageID;
// return View(productStageEntity.ToList<ProductStageEntity>());
//}
/// <summary>
/// 获取分公司
/// </summary>
/// <param name="OrganizeCode">组织代码，有值则选择当前值</param>
/// <param name="isEdit">是否可以编辑</param>
/// <param name="name">客户端Name</param>
/// <param name="step">步骤序号</param>
/// <returns></returns>
public ActionResult GetCompanyList(string OrganizeCode, bool?isEdit, string name, string step = "0", string errMsg = "部门不能为空！")
{
ViewData["name"] = name;
ViewData["OrganizeCode"] = OrganizeCode;
ViewData["isEdit"] = isEdit.GetValueOrDefault(true);
ViewData["step"] = step;
ViewData["errMsg"] = errMsg;
Organize organize = new Organize();
organize.ResetForSearch();
if (!string.IsNullOrEmpty(OrganizeCode))
{
organize.organizeid = OrganizeCode;
organize.GetTable(true);
}
// organizeEntity.Organizeid = OrganizeCode;
// return View(organizeEntity.ToList<Organize>());
return(View(organize));
}
public void SubmitForm(OrganizeInputDto organizeInputDto, string keyValue)
{
Organize organize = new Organize();
if (!string.IsNullOrEmpty(keyValue))
{
var id = Convert.ToInt64(keyValue);
organize = organizeRepository.Get(id);
AutoMapper.Mapper.Map <OrganizeInputDto, Organize>(organizeInputDto, organize);
organize.LastModificationTime = DateTime.Now;
organizeRepository.Update(organize);
}
else
{
AutoMapper.Mapper.Map <OrganizeInputDto, Organize>(organizeInputDto, organize);
organize.Id = IdWorkerHelper.GenId64();
organize.DeletedMark = false;
organize.CreationTime = DateTime.Now;
organizeRepository.Add(organize);
}
}
public async Task SaveOrganize([FromBody] Organize organize)
{
var fatherId = "0";// (((HttpContextBase)Request.Properties["MS_HttpContext"])).Request.QueryString["FatherId"];
organize.FatherId = int.Parse(fatherId);
try
{
if (organize.Id > 0)
{
await _organizeRepository.UpdateAsync(organize);
}
else
{
await _organizeRepository.InsertAsync(organize);
}
}
catch (Exception exception)
{
_logger.Error("组织机构保存时发生异常", exception);
}
}
/// <summary>
/// 获取产品名称
/// </summary>
/// <param name="term">关键词</param>
/// <returns></returns>
//public ActionResult GetTextSelectUrlProduct(string term)
//{
// System.Data.DataTable dtContent = new System.Data.DataTable();
// ProductEntity obj = new ProductEntity();
// obj.ResetForSearch();
// obj.PageSize = 10;
// obj.ProductName = term;
// obj.IsUseLike = true;
// DataTable dt = obj.GetTable();
// dtContent.Columns.Add("value");
// dtContent.Columns.Add("label");
// dtContent.Columns.Add("desc");
// dtContent.Columns.Add("icon");
// foreach (DataRow drUser in dt.Rows)
// {
// //if (i.ToString().IndexOf(term) == -1)
// //{//模拟查询
// // continue;
// //}
// DataRow dr = dtContent.NewRow();
// dr[0] = drUser["ProductNoSimple"].ToString();
// dr[1] = drUser["ProductName"].ToString();
// dr[2] = "";
// dr[3] = "";
// dtContent.Rows.Add(dr);
// }
// //select a as value,b as label,c as desc ,d as icon from table where title='term'
// Donlim.Library.Entitys.EntityBase entityBase = new Donlim.Library.Entitys.EntityBase();
// entityBase.Dt = dtContent;
// JsonResult jsonResult = entityBase.ToJsonOnlyData();
// return jsonResult;
//}
/// <summary>
/// 查询组织
/// </summary>
/// <param name="term">关键词</param>
/// <returns></returns>
public JsonResult GetTextSelectUrlOrganize(string term)
{
System.Data.DataTable dtContent = new System.Data.DataTable();
Organize organize = new Organize();
organize.ResetForSearch();
organize.Parameters[0] = new SqlParameter("@term", term);
organize.SqlCustomer = " AND (organizeid LIKE '%'+@term+'%' OR organizename LIKE '%'+@term+'%')";
organize.PageSize = 10;
DataTable dt = organize.GetTable();
dtContent.Columns.Add("value");
dtContent.Columns.Add("label");
dtContent.Columns.Add("desc");
dtContent.Columns.Add("icon");
foreach (DataRow drUser in dt.Rows)
{
//if (i.ToString().IndexOf(term) == -1)
//{//模拟查询
// continue;
//}
DataRow dr = dtContent.NewRow();
dr[0] = drUser["organizeid"].ToString();
string longname = drUser["longname"].ToString();
if (longname != "广东东菱凯琴集团总部" && longname != "广东新宝电器股份有限公司")
{
longname = longname.Replace("广东东菱凯琴集团总部", "").Replace("广东新宝电器股份有限公司", "");
}
dr[1] = longname;
dr[2] = drUser["organizename"].ToString();
dr[3] = "";
dtContent.Rows.Add(dr);
}
//select a as value,b as label,c as desc ,d as icon from table where title='term'
Donlim.Library.Entitys.EntityBase entityBase = new Donlim.Library.Entitys.EntityBase();
entityBase.Dt = dtContent;
JsonResult jsonResult = entityBase.ToJsonOnlyData();
return(jsonResult);
}
/// <summary>
/// 查询记录
/// </summary>
/// <param name="inputDto">查询实体</param>
/// <returns></returns>
public PagerResult <Dtos.GetRoleOutputDTO> GetRoles(Dtos.GetRoleInputDTO inputDto)
{
var pagerWrapper = new PagerResult <Dtos.GetRoleOutputDTO>();
var table = from c in _roleRepository.GetAll()
join o in _organizeRepository.GetAll()
on c.OrganizeId equals o.Id into Organize
from O in Organize.DefaultIfEmpty()
join cu in _userRepository.GetAll()
on c.Creator equals cu.Id into Creator
from CU in Creator.DefaultIfEmpty()
join uu in _userRepository.GetAll()
on c.LastModifier equals uu.Id into Modifier
from UU in Modifier.DefaultIfEmpty()
select new Dtos.GetRoleOutputDTO
{
Id = c.Id.ToString(),
Name = c.Name,
CreateTime = c.CreateTime,
Creator = CU == null ? "" : CU.UserName,
Description = c.Description,
DataAuthority = c.DataAuthority,
IsEnabled = c.IsEnabled,
OrganizeId = c.OrganizeId,
LastModifier = UU == null ? "":UU.UserName,
LastModifyTime = c.LastModifyTime,
OrganizeName = O == null ? "" : O.Name,
SortCode = c.SortCode,
};
var result = table
.WhereIf(!inputDto.Name.IsNullOrEmpty(), p => p.Name.Contains(inputDto.Name))
.WhereIf(!inputDto.OrganizeId.IsNullOrEmpty(), p => p.OrganizeId == new Guid(inputDto.OrganizeId))
.WhereIf(!inputDto.OrganizeName.IsNullOrEmpty(), p => p.OrganizeName.Contains(inputDto.OrganizeName))
.Page(inputDto, pagerWrapper)
.ToList();
pagerWrapper.Result = result;
return(pagerWrapper);
}
/// <summary>
/// 修改组织机构信息
/// </summary>
/// <param name="cache">当前操作的缓存用户</param>
/// <param name="inputDto">信息输入模型</param>
/// <returns></returns>
public async Task <OperationResult> EditOrganizes(CacheUser cache, params OrganizeInputDto[] inputDto)
{
ICache iCache = CacheManager.GetCacher("CacheOrganizeId");
var cacheIds = iCache.Get(cache.UserName.AESEncrypt128()) as IEnumerable <Guid>;
List <string> names = new List <string>();
OrganizeRepository.UnitOfWork.BeginTransaction();
foreach (var i in inputDto)
{
if (!cache.IsAdministrator && !cacheIds.Contains(i.Id))
{
throw new Exception("id:当前操作不在权限范围内");
}
var value = OrganizeRepository.Entities.FirstOrDefault(m => m.Id == i.Id);
if (value == null)
{
throw new Exception($"id:主键为:{i.Id}的组织机构信息不存在");
}
Organize dto = i.MapTo <Organize>();
dto.CreatedTime = value.CreatedTime;
dto.CreatorUserId = value.CreatorUserId;
dto.LastUpdatedTime = DateTime.Now;
dto.LastUpdatorUserId = cache.UserName;
dto.SortCode = value.SortCode;
await OrganizeRepository.UpdateAsync(dto);
names.Add(dto.FullName);
}
OrganizeRepository.UnitOfWork.Commit();
if (names.Count > 0)
{
return(new OperationResult(OperationResultType.Success, "修改组织机构:{0}成功".FormatWith(names.ExpandAndToString())));
}
else
{
return(new OperationResult(OperationResultType.NoChanged, "操作失败，修改数据请求已取消"));
}
}
/// <summary>
/// 定义种子数据初始化过程
/// </summary>
/// <param name="context">数据上下文</param>
public void Action(DbContext context)
{
try
{
context.Database.Log = new Action <string>(m => { LogManager.GetLogger(GetType()).Info(m); });
#region 初始化添加组织类型
//创建Organize<组织类型>表的类型字典目录
DataItem organizeItem = new DataItem
{
CreatedTime = DateTime.Now,
QueryCoding = "OrganizeType",
FullName = "组织类型",
IsPublic = false,
ParentId = null,
IsSystem = true
};
organizeItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = organizeItem,
IsLocked = false,
QueryCoding = "OrganizeType",
FullName = "机构",
Index = 0,
IsSystem = true
});
organizeItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = organizeItem,
IsLocked = false,
QueryCoding = "OrganizeType",
FullName = "项目",
Index = 1,
IsSystem = true
});
organizeItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = organizeItem,
IsLocked = false,
QueryCoding = "OrganizeType",
FullName = "小组",
Index = 2,
IsSystem = true
});
context.Set <DataItem>().Add(organizeItem);
#endregion
#region 初始化添加黑名单类型
//创建UserBlackList<黑名单类型>表的类型字典目录
DataItem userBlackListItem = new DataItem
{
CreatedTime = DateTime.Now,
FullName = "黑名单类型",
IsPublic = false,
QueryCoding = "UserBlackListType",
IsSystem = true
};
userBlackListItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = userBlackListItem,
IsLocked = false,
QueryCoding = "UserBlackListType",
FullName = "主机黑名单",
Index = 0,
IsSystem = true
});
userBlackListItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = userBlackListItem,
IsLocked = false,
QueryCoding = "UserBlackListType",
FullName = "分控黑名单",
Index = 1,
IsSystem = true
});
context.Set <DataItem>().Add(userBlackListItem);
#endregion
#region 初始化添加信息类型
//创建Information<信息类型>表的类型字典目录
DataItem informationItem = new DataItem
{
CreatedTime = DateTime.Now,
FullName = "信息类型",
IsPublic = false,
QueryCoding = "InformationType",
IsSystem = true
};
//主机类型 0-10
informationItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = informationItem,
IsLocked = false,
QueryCoding = "InformationType",
FullName = "主机断线",
Index = 0,
IsSystem = true
});
informationItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = informationItem,
IsLocked = false,
QueryCoding = "InformationType",
FullName = "主机电压超压",
Index = 1,
IsSystem = true
});
informationItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = informationItem,
IsLocked = false,
QueryCoding = "InformationType",
FullName = "主机电压欠压",
Index = 2,
IsSystem = true
});
informationItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = informationItem,
IsLocked = false,
QueryCoding = "InformationType",
FullName = "主机门被打开",
Index = 3,
IsSystem = true
});
informationItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = informationItem,
IsLocked = false,
QueryCoding = "InformationType",
FullName = "主机功率异常",
Index = 4,
IsSystem = true
});
//分控类型11-20
informationItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = informationItem,
IsLocked = false,
QueryCoding = "InformationType",
FullName = "分控断线",
Index = 11,
IsSystem = true
});
informationItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = informationItem,
IsLocked = false,
QueryCoding = "InformationType",
FullName = "分机功率异常",
Index = 12,
IsSystem = true
});
context.Set <DataItem>().Add(informationItem);
#endregion
#region 初始化添加主机类型
//创建Host<主机类型>表的类型字典目录
DataItem hostItem = new DataItem
{
CreatedTime = DateTime.Now,
FullName = "主机类型",
IsPublic = true,
QueryCoding = "HostType",
IsSystem = true
};
hostItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = hostItem,
IsLocked = false,
QueryCoding = "HostType",
FullName = "主机类型1",
Index = 0,
IsSystem = true
});
hostItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = hostItem,
IsLocked = false,
QueryCoding = "HostType",
FullName = "主机类型2",
Index = 1,
IsSystem = true
});
context.Set <DataItem>().Add(hostItem);
#endregion
#region 初始化添加分控类型
DataItem subItem = new DataItem
{
CreatedTime = DateTime.Now,
FullName = "分控类型",
IsPublic = true,
QueryCoding = "SubType",
IsSystem = true
};
subItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = subItem,
IsLocked = false,
QueryCoding = "SubType",
FullName = "分控类型1",
Index = 0,
IsSystem = true
});
subItem.DataItemDetailMany.Add(
new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = subItem,
IsLocked = false,
QueryCoding = "SubType",
FullName = "分控类型2",
Index = 1,
IsSystem = true
});
context.Set <DataItem>().Add(subItem);
#endregion
#region 初始化添加灯杆类型
DataItem lightPoleItem = new DataItem
{
CreatedTime = DateTime.Now,
FullName = "灯杆类型",
IsPublic = true,
QueryCoding = "PoleType",
IsSystem = true
};
lightPoleItem.DataItemDetailMany.Add(new DataItemDetail {
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = lightPoleItem,
IsLocked = false,
QueryCoding = "PoleType",
FullName = "灯杆类型1",
Index = 0,
IsSystem = true
});
lightPoleItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = lightPoleItem,
IsLocked = false,
QueryCoding = "PoleType",
FullName = "灯杆类型2",
Index = 1,
IsSystem = true
});
context.Set <DataItem>().Add(lightPoleItem);
#endregion
#region 初始化添加灯具类型
DataItem lightsItem = new DataItem
{
CreatedTime = DateTime.Now,
FullName = "灯具类型",
IsPublic = true,
QueryCoding = "LigthsType",
IsSystem = true
};
lightsItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = lightsItem,
IsLocked = false,
QueryCoding = "LigthsType",
FullName = "灯具类型1",
Index = 0,
IsSystem = true
});
lightsItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = lightsItem,
IsLocked = false,
QueryCoding = "LigthsType",
FullName = "灯具类型2",
Index = 1,
IsSystem = true
});
context.Set <DataItem>().Add(lightsItem);
#endregion
#region 初始化光照计划类型
DataItem lightPlanItem = new DataItem
{
CreatedTime = DateTime.Now,
FullName = "光照计划类型",
IsPublic = true,
QueryCoding = "LigthPlanType",
IsSystem = true
};
lightPlanItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = lightPlanItem,
IsLocked = false,
QueryCoding = "LigthPlanType",
FullName = "标准光照计划",
Index = 0,
IsSystem = true
});
lightPlanItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = lightPlanItem,
IsLocked = false,
QueryCoding = "LigthPlanType",
FullName = "隧道光照计划",
Index = 1,
IsSystem = true
});
context.Set <DataItem>().Add(lightPlanItem);
#endregion
#region 初始化升级包的类型
DataItem PackItem = new DataItem
{
CreatedTime = DateTime.Now,
FullName = "升级包类型",
IsPublic = true,
QueryCoding = "PacketType",
IsSystem = true
};
PackItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = PackItem,
IsLocked = false,
QueryCoding = "PacketType",
FullName = "主机升级包",
Index = 0,
IsSystem = true
});
PackItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = PackItem,
IsLocked = false,
QueryCoding = "PacketType",
FullName = "分控升级包",
Index = 1,
IsSystem = true
});
context.Set <DataItem>().Add(PackItem);
#endregion
#region 初始化策略下发状态类型
DataItem HostPolicyItem = new DataItem
{
CreatedTime = DateTime.Now,
FullName = "策略下发类型",
IsPublic = false,
QueryCoding = "PolicyType",
IsSystem = true
};
HostPolicyItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = HostPolicyItem,
IsLocked = false,
QueryCoding = "PolicyType",
FullName = "策略下发等待中",
Index = 0,
IsSystem = true
});
HostPolicyItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = HostPolicyItem,
IsLocked = false,
QueryCoding = "PolicyType",
FullName = "策略下发已成功",
Index = 1,
IsSystem = true
});
HostPolicyItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = HostPolicyItem,
IsLocked = false,
QueryCoding = "PolicyType",
FullName = "策略下发已失败",
Index = 2,
IsSystem = true
});
context.Set <DataItem>().Add(HostPolicyItem);
#endregion
#region 初始化分组的类型
DataItem GroupItem = new DataItem
{
CreatedTime = DateTime.Now,
FullName = "分组类型",
IsPublic = true,
QueryCoding = "GroupType",
IsSystem = true
};
GroupItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = GroupItem,
IsLocked = false,
QueryCoding = "GroupType",
FullName = "主机上的节点分组",
Index = 0,
IsSystem = true
});
GroupItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = true,
DataItemOne = GroupItem,
IsLocked = false,
QueryCoding = "GroupType",
FullName = "未命名分组",
Index = 1,
IsSystem = true
});
context.Set <DataItem>().Add(GroupItem);
#endregion
#region 初始化能耗存储类型
DataItem ECTItem = new DataItem
{
CreatedTime = DateTime.Now,
FullName = "能耗存储类型",
IsPublic = false,
QueryCoding = "ECType",
IsSystem = true
};
ECTItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = false,
DataItemOne = ECTItem,
IsLocked = false,
QueryCoding = "ECType",
FullName = "主机能耗",
Index = 0,
IsSystem = true
});
ECTItem.DataItemDetailMany.Add(new DataItemDetail
{
CreatedTime = DateTime.Now,
IsPublic = false,
DataItemOne = ECTItem,
IsLocked = false,
QueryCoding = "ECType",
FullName = "分控能耗",
Index = 1,
IsSystem = true
});
context.Set <DataItem>().Add(ECTItem);
#endregion
#region 初始化系统内置组织和默认帐号
Organize organize = new Organize
{
DataItemDetailOne = organizeItem.DataItemDetailMany.First(m => m.QueryCoding == "OrganizeType" && m.Index == 0),
Email = "*****@*****.**",
Fax = "0755-29686239",
FullName = "系统内建组织",
Address = "深圳市宝安区石岩街道洲石路达成工业园",
City = "深圳市",
Country = "中国",
County = "宝安区",
CreatedTime = DateTime.Now,
Province = "广东省",
Remark = "该数据由系统初始化时创建",
SortCode = (int)DateTime.Now.GetTimeStamp(),
TelePhone = "0755-29686239",
OrganizeLogo = Properties.Resources.OrganizeLog.ToBytes()
};
string key = new Random().NextLetterString(16).ToUpper();
UserLogin userLogin = new UserLogin
{
UserName = "******",
SecretKey = key,
Password = ($"12345678").AESEncrypt128(key),
AccessFailedCount = 10,
IsAdministrator = true,
IsLocked = false,
Level = 1,
OrganizeOne = organize,
LoginCount = 0,
PermissionList = "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30",
LockoutEnabled = true,
};
userLogin.UserMany.Add(new User
{
CreatedTime = DateTime.Now,
Email = "*****@*****.**",
NickName = "系统超级管理员",
PhoneNumber = "13102263109",
RealName = "莫奕宁",
Sex = 1,
WeChat = "myining",
UserLoginOne = userLogin,
HeadIcon = Properties.Resources._default.ToBytes()
});
userLogin.UserBlackListMany.Add(new UserBlackList
{
CreatedTime = DateTime.Now,
BlackList = "",
DataItemDetailOne = userBlackListItem.DataItemDetailMany.First(),
});
userLogin.UserOrganizeMapMany.Add(new UserOrganizeMap
{
CreatedTime = DateTime.Now,
OrganizeOne = organize,
});
organize.UserLoginMany.Add(userLogin);
context.Set <Organize>().Add(organize);
#endregion
#region 添加初始化测试主机
Host host = new Host
{
CreatedTime = DateTime.Now,
Address = "深圳市宝安区石岩街道洲石路达成工业园",
DataItemDetailOne = hostItem.DataItemDetailMany.First(),
FullName = "雷雁测试主机",
HeartBag = "000000",
OrganizeOne = organize,
IsEnergySwitch = true,
PhoneNum = "13102263109",
Remark = "系统初始化生成主机",
RegPackage = "000000",
IsLocked = false,
};
host.HostRealTimeDataMany.Add(new HostRealTimeData
{
Temperature = 20,
Voltage = "210,200,243",
Power = "220,220,220",
Current = "0.78,0.25,0.28",
HostOne = host,
IsAlarmMark = false,
IsDataError = false,
IsOnline = true,
Latitude = 70,
Longitude = 120,
LoopState = "1,1,1,1,0,0,1,1",
TimeZone = 8,
UpdateTime = DateTime.Now
});
LightPole pole = new LightPole
{
Address = "深圳市宝安区石岩街道洲石路达成工业园西南角",
CreatedTime = DateTime.Now,
DataItemDetailOne = lightPoleItem.DataItemDetailMany.First(),
PoleName = "灯杆1号",
PoleNum = 1,
Remark = "3.5m双灯灯杆",
};
SubControl sub = new SubControl
{
CreatedTime = DateTime.Now,
DataItemDetailOne = subItem.DataItemDetailMany.First(),
Remark = "系统初始化生成测试分控",
SubName = "测试分控1",
SubNum = 1
};
sub.SubRealTimeDataMany.Add(
new SubRealTimeData
{
IsAlarmMark = false,
CreatedTime = DateTime.Now,
UpdateTime = DateTime.Now,
Current = 0.2,
Brightness = 100,
DataItemDetailOne = lightsItem.DataItemDetailMany.First(),
DimmingPort = 1,
Frequency = 9600,
Power = 200,
Voltage = 230,
SubControlOne = sub,
Temperature = 20
});
pole.SubControlMany.Add(sub);
host.LightPoleMany.Add(pole);
context.Set <Host>().Add(host);
#endregion
#region 灯具聚合
SubAggregation subAggregation = new SubAggregation
{
CreatedTime = DateTime.Now,
HostOne = host,
SubControlOne = sub,
OrganizeOne = organize,
LightPoleOne = host.LightPoleMany.First()
};
context.Set <SubAggregation>().Add(subAggregation);
#endregion
#region 添加初始化测试警报信息
//context.Set<Information>().AddRange(
// new List<Information>()
// {
// new Information{
// CreatedTime = DateTime.Now,
// DataItemDetailOne = informationItem.DataItemDetailMany.First(m=>m.Index==0),
// IsReaded = false,
// ObjectId = host.Id,
// OrganizeOne = organize,
// UserLoginOne = userLogin,
// },
// new Information
// {
// CreatedTime = DateTime.Now,
// DataItemDetailOne = informationItem.DataItemDetailMany.First(m=>m.Index==11),
// IsReaded = false,
// ObjectId = sub.Id,
// OrganizeOne = organize,
// UserLoginOne = userLogin,
// }
//});
#endregion
#region 添加组织机构树结构查询存储过程
string organizeTreeSqlText =
#region 数据库语句
@"
CREATE PROCEDURE Tree_Organize
@Id uniqueidentifier,
@SPAction bit = 'TRUE'
AS
BEGIN
SET NOCOUNT ON;
IF @SPAction='TRUE'
BEGIN
WITH Cxt_Child (
Id,
DataItemDetail_Id,
OrganizeLogo,
OrganizeLogoPath,
ParentId,
TelePhone,
Fax,
FullName,
Email,
Country,
Province,
City,
County,
Address,
Remark,
SortCode,
CreatedTime,
CreatorUserId,
LastUpdatedTime,
LastUpdatorUserId,
LEVEL) AS
(
SELECT *,0 AS LEVEL
FROM
dbo.Organize
WHERE Id=@Id
UNION ALL
SELECT
o.*,
b.LEVEL + 1
FROM
dbo.Organize o
INNER JOIN Cxt_Child b ON (o.ParentId = b.Id)
)
SELECT * FROM Cxt_Child
END
ELSE
BEGIN
WITH Cxt_Child ( Id,DataItemDetail_Id,OrganizeLogo,OrganizeLogoPath,ParentId,TelePhone,Fax,FullName,Email,Country,Province,City,County,Address,Remark,SortCode,CreatedTime,CreatorUserId,LastUpdatedTime,LastUpdatorUserId ) AS
(
SELECT *
FROM
dbo.Organize
WHERE Id=@Id
UNION ALL
SELECT
o.*
FROM
dbo.Organize o
INNER JOIN Cxt_Child b ON (o.Id = b.ParentId)
)
SELECT * FROM Cxt_Child
END
END
";
#endregion
context.Database.ExecuteSqlCommand(organizeTreeSqlText);
#endregion
#region 添加查询月能耗统计存储过程
string sum_MonthSqlText =
#region 数据库语句
@"
CREATE PROCEDURE [dbo].[Sp_SumYearPower]
@DataItemDetailId uniqueidentifier, -- 指定能耗类型主键
@ThisYear INT, -- 指定年能耗
@OrganizeId uniqueidentifier -- 指定组织机构
AS
BEGIN
SELECT
dbo.AnnualElectricity.[Year],
dbo.AnnualElectricity.DataItemDetail_Id,
Sum(dbo.AnnualElectricity.M1) AS Month_Sum1,
Sum(dbo.AnnualElectricity.M2) AS Month_Sum2,
Sum(dbo.AnnualElectricity.M3) AS Month_Sum3,
Sum(dbo.AnnualElectricity.M4) AS Month_Sum4,
Sum(dbo.AnnualElectricity.M5) AS Month_Sum5,
Sum(dbo.AnnualElectricity.M6) AS Month_Sum6,
Sum(dbo.AnnualElectricity.M7) AS Month_Sum7,
Sum(dbo.AnnualElectricity.M8) AS Month_Sum8,
Sum(dbo.AnnualElectricity.M9) AS Month_Sum9,
Sum(dbo.AnnualElectricity.M10) AS Month_Sum10,
Sum(dbo.AnnualElectricity.M11) AS Month_Sum11,
Sum(dbo.AnnualElectricity.M12) AS Month_Sum12,
Sum(dbo.AnnualElectricity.YearTotal) AS YearTotal_Sum,
Sum(dbo.AnnualElectricity.Cumulative) AS Cumulative_Sum
FROM
dbo.AnnualElectricity
WHERE
dbo.AnnualElectricity.DataItemDetail_Id=@DataItemDetailId
AND dbo.AnnualElectricity.[Year]=@ThisYear
AND dbo.AnnualElectricity.Organzie_Id=@OrganizeId
GROUP BY
dbo.AnnualElectricity.[Year],
dbo.AnnualElectricity.DataItemDetail_Id
END
";
#endregion
context.Database.ExecuteSqlCommand(sum_MonthSqlText);
#endregion
#region 添加查询天能耗统计的存储过程
string sum_DaySqlText =
#region 数据库语句
@"
CREATE PROCEDURE [dbo].[Sp_SumMonthPower]
@Month AS int ,
@OrganizeId AS uniqueidentifier ,
@ItemId AS uniqueidentifier,
@Year AS INT
AS
BEGIN
SELECT
Sum(t1.D1) AS Sum_D1,
Sum(t1.D2) AS Sum_D2,
Sum(t1.D3) AS Sum_D3,
Sum(t1.D4) AS Sum_D4,
Sum(t1.D5) AS Sum_D5,
Sum(t1.D6) AS Sum_D6,
Sum(t1.D7) AS Sum_D7,
Sum(t1.D8) AS Sum_D8,
Sum(t1.D9) AS Sum_D9,
Sum(t1.D10) AS Sum_D10,
Sum(t1.D11) AS Sum_D11,
Sum(t1.D12) AS Sum_D12,
Sum(t1.D13) AS Sum_D13,
Sum(t1.D14) AS Sum_D14,
Sum(t1.D15) AS Sum_D15,
Sum(t1.D16) AS Sum_D16,
Sum(t1.D17) AS Sum_D17,
Sum(t1.D18) AS Sum_D18,
Sum(t1.D19) AS Sum_D19,
Sum(t1.D20) AS Sum_D20,
Sum(t1.D21) AS Sum_D21,
Sum(t1.D22) AS Sum_D22,
Sum(t1.D23) AS Sum_D23,
Sum(t1.D24) AS Sum_D24,
Sum(t1.D25) AS Sum_D25,
Sum(t1.D26) AS Sum_D26,
Sum(t1.D27) AS Sum_D27,
Sum(t1.D28) AS Sum_D28,
Sum(t1.D29) AS Sum_D29,
Sum(t1.D30) AS Sum_D30,
Sum(t1.D31) AS Sum_D31,
SUM(t1.MonthTotal) AS Sum_MonthTotal
t1.DataItemDetail_Id,
t1.[Month]
FROM
(SELECT
dbo.MonthElectricity.*,
dbo.AnnualElectricity.DataItemDetail_Id
FROM
dbo.MonthElectricity
INNER JOIN dbo.AnnualElectricity
ON dbo.MonthElectricity.AnnualElectricity_Id = dbo.AnnualElectricity.Id
WHERE
dbo.AnnualElectricity.DataItemDetail_Id = @ItemId
AND dbo.AnnualElectricity.Organzie_Id = @OrganizeId
AND dbo.MonthElectricity.[Month]=@Month
AND dbo.AnnualElectricity.[Year]=@Year) AS t1
GROUP BY
t1.DataItemDetail_Id,
t1.[Month]
END
";
#endregion
context.Database.ExecuteSqlCommand(sum_DaySqlText);
#endregion
#region 添加查询按小时统计能耗的存储过程
string sum_HourSqlText =
#region 数据库语句
@"
CREATE PROCEDURE [dbo].[Sp_SumDayPower]
@Day INT,
@OrganizeId uniqueidentifier ,
@ItemId uniqueidentifier,
@Year INT,
@Month INT
AS
BEGIN
SELECT
Sum(t1.H1) AS Sum_H1,
Sum(t1.H2) AS Sum_H2,
Sum(t1.H3) AS Sum_H3,
Sum(t1.H4) AS Sum_H4,
Sum(t1.H5) AS Sum_H5,
Sum(t1.H6) AS Sum_H6,
Sum(t1.H7) AS Sum_H7,
Sum(t1.H8) AS Sum_H8,
Sum(t1.H9) AS Sum_H9,
Sum(t1.H10) AS Sum_H10,
Sum(t1.H11) AS Sum_H11,
Sum(t1.H12) AS Sum_H12,
Sum(t1.H13) AS Sum_H13,
Sum(t1.H14) AS Sum_H14,
Sum(t1.H15) AS Sum_H15,
Sum(t1.H16) AS Sum_H16,
Sum(t1.H17) AS Sum_H17,
Sum(t1.H18) AS Sum_H18,
Sum(t1.H19) AS Sum_H19,
Sum(t1.H20) AS Sum_H20,
Sum(t1.H21) AS Sum_H21,
Sum(t1.H22) AS Sum_H22,
Sum(t1.H23) AS Sum_H23,
Sum(t1.H24) AS Sum_H24,
Sum(t1.DayTotal) AS Sum_DayTotal,
t1.DataItemDetail_Id
FROM
(SELECT
dbo.DayElectricity.*,
dbo.AnnualElectricity.DataItemDetail_Id,
dbo.AnnualElectricity.Organzie_Id
FROM
dbo.DayElectricity
INNER JOIN dbo.MonthElectricity ON dbo.DayElectricity.MonthElectricity_Id = dbo.MonthElectricity.Id
INNER JOIN dbo.AnnualElectricity ON dbo.MonthElectricity.AnnualElectricity_Id = dbo.AnnualElectricity.Id
WHERE
dbo.AnnualElectricity.DataItemDetail_Id = @ItemId
AND dbo.AnnualElectricity.Organzie_Id = @OrganizeId
AND dbo.AnnualElectricity.[Year]=@Year
AND dbo.MonthElectricity.[Month]=@Month
AND dbo.DayElectricity.Today=@Day
) AS t1
GROUP BY
t1.DataItemDetail_Id
END
";
#endregion
context.Database.ExecuteSqlCommand(sum_HourSqlText);
#endregion
#region 添加掉线主机查询视图
string GetOffineHosts =
#region 数据库语句
@"
CREATE VIEW [dbo].[View_HostOffines] AS
SELECT DISTINCT
dbo.UserLogin.UserName,
dbo.[User].NickName,
dbo.[User].Email,
dbo.Organize.FullName AS OrgName,
dbo.Host.RegPackage,
dbo.Host.FullName AS HostName,
dbo.HostRealTimeData.Voltage,
dbo.HostRealTimeData.[Current],
dbo.HostRealTimeData.Power,
dbo.HostRealTimeData.Temperature,
dbo.HostRealTimeData.UpdateTime,
[Level]
FROM
dbo.UserLogin
INNER JOIN dbo.[User] ON dbo.[User].UserLogin_Id = dbo.UserLogin.Id
INNER JOIN dbo.UserOrganizeMap ON dbo.UserOrganizeMap.UserLogin_Id = dbo.UserLogin.Id
INNER JOIN dbo.Organize ON dbo.UserLogin.Organize_Id = dbo.Organize.Id AND dbo.UserOrganizeMap.Organize_Id = dbo.Organize.Id
INNER JOIN dbo.Host ON dbo.Host.Organize_Id = dbo.Organize.Id
INNER JOIN dbo.HostRealTimeData ON dbo.HostRealTimeData.Host_Id = dbo.Host.Id
WHERE
DATEDIFF(mi,dbo.HostRealTimeData.UpdateTime,GETDATE()) >= 30 AND
DATEDIFF(d, HostRealTimeData.UpdateTime, GETDATE()) <= 1 AND
dbo.[User].Email IS NOT NULL AND
dbo.[User].IsAlarm =1
GO
";
#endregion
context.Database.ExecuteSqlCommand(GetOffineHosts);
#endregion
#region 添加掉线分控查询视图
string GetOffineSubs =
#region 数据库语句
@"
SELECT DISTINCT
dbo.UserLogin.UserName,
dbo.[User].NickName,
dbo.[User].Email,
dbo.Organize.FullName AS OrgName,
dbo.Host.FullName AS HostName,
dbo.Host.RegPackage,
dbo.LightPole.PoleNum,
dbo.LightPole.PoleName AS PoleName,
dbo.SubControl.SubNum,
dbo.SubControl.SubName,
dbo.SubRealTimeData.DimmingPort,
dbo.SubRealTimeData.UpdateTime,
[Level]
FROM
dbo.UserLogin
INNER JOIN dbo.[User] ON dbo.[User].UserLogin_Id = dbo.UserLogin.Id
INNER JOIN dbo.UserOrganizeMap ON dbo.UserOrganizeMap.UserLogin_Id = dbo.UserLogin.Id
INNER JOIN dbo.Organize ON dbo.UserLogin.Organize_Id = dbo.Organize.Id AND dbo.UserOrganizeMap.Organize_Id = dbo.Organize.Id
INNER JOIN dbo.Host ON dbo.Host.Organize_Id = dbo.Organize.Id
INNER JOIN dbo.LightPole ON dbo.LightPole.Host_Id = dbo.Host.Id
INNER JOIN dbo.SubControl ON dbo.SubControl.LigthPoleOne_Id = dbo.LightPole.Id
INNER JOIN dbo.SubRealTimeData ON dbo.SubRealTimeData.SubControl_Id = dbo.SubControl.Id
WHERE
DATEDIFF(mi,dbo.SubRealTimeData.UpdateTime,GETDATE()) >= 60
AND DATEDIFF(d,dbo.SubRealTimeData.UpdateTime,GETDATE()) <= 1
AND dbo.[User].Email IS NOT NULL
AND dbo.[User].IsAlarm=1
GO
";
#endregion
#endregion
context.Database.ExecuteSqlCommand(GetOffineSubs);
}
catch (Exception ex)
{
LogManager.GetLogger(GetType()).Fatal(ex);
}
}
/// <summary>
/// 分页获取审批流程数据
/// </summary>
/// <param name="orgid">组织id</param>
/// <param name="approvalTypeId">审批类型id</param>
/// <param name="bType">单据类型</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">每页条数</param>
/// <param name="queryStr">流程编码或流程名称的查询条件</param>
/// <param name="count">总记录条数</param>
/// <returns></returns>
public List <GAppvalProcModel> GetProcList(long orgid, long approvalTypeId, string bType, int pageIndex, int pageSize, string queryStr, out int count)
{
List <GAppvalProcModel> procModels = new List <GAppvalProcModel>();
if (orgid == 0 || approvalTypeId == 0 || string.IsNullOrEmpty(bType))
{
count = 0;
return(procModels);
}
List <long> orgids = new List <long>();
orgids.Add(orgid);
//取当前组织的所有下级组织部门
List <OrganizeModel> organizeModels = OrganizationFacade.GetAllChildList(orgid);
if (organizeModels != null && organizeModels.Count > 0)
{
orgids.AddRange(organizeModels.Select(t => t.PhId));
}
//根据组织id，单据类型，审批类型获取所有的审批流程
procModels = GAppvalProcFacade.GetAppvalProc(orgids, bType, approvalTypeId);
//同一个审批流程的，不同启用组织的数据合并到同一个审批流程中
Dictionary <string, GAppvalProcModel> map = new Dictionary <string, GAppvalProcModel>();
foreach (GAppvalProcModel model in procModels)
{
if (map.ContainsKey(model.FCode))
{
Organize organize = new Organize();
organize.OrgId = model.OrgPhid;
organize.OrgCode = model.OrgCode;
organize.OrgName = model.OrgName;
GAppvalProcModel procModel = map[model.FCode];
procModel.Organizes.Add(organize);
}
else
{
List <Organize> organizes = new List <Organize>();
Organize organize = new Organize();
organize.OrgId = model.OrgPhid;
organize.OrgCode = model.OrgCode;
organize.OrgName = model.OrgName;
organizes.Add(organize);
model.Organizes = organizes;
map.Add(model.FCode, model);
}
}
procModels = map.Values.ToList();
//筛选数据
if (!string.IsNullOrEmpty(queryStr))
{
procModels = procModels.FindAll(t => (t.FCode != null && t.FCode.IndexOf(queryStr) > -1) || (t.FName != null && t.FName.IndexOf(queryStr) > -1));
}
count = procModels.Count;
//分页
procModels = procModels.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
return(procModels);
}
public SIIS_OrganizeClass()
{
organize = new OrganizeClass();
}
/// <summary>
/// 创建问卷
/// </summary>
/// <param name="global">问卷全局信息</param>
/// <param name="message">处理结果信息</param>
private QssResult QssCreateQuesGlobal(JObject global, User user, out Question ques, out string message)
{
// 校验时间
var beginTime = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(Convert.ToInt64(global["BeginTime"])).ToLocalTime();
var endTime = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(Convert.ToInt64(global["EndTime"])).ToLocalTime();
if (endTime < DateTime.Now || endTime < beginTime)
{
ques = null;
message = "问卷结束时间不能早于当前时间和开始时间!";
return(QssResult.Fail);
}
List <Organize> rdOrgs = new List <Organize>();
List <Organize> wtOrgs = new List <Organize>();
Organize organize = null;
// 问卷状态
QssQuesAndVoteState state = QssQuesAndVoteState.NoCreate;
User auditor = null;
bool isBelongOrganize = Convert.ToBoolean(global["IsBelongOrganize"]);
// 获取问卷所属组织
string orgName = "";
if (isBelongOrganize)
{
organize = DbSession.OrganizeDal.GetEntity(Convert.ToInt32(global["BelongTo"]));
if (organize == null)
{
ques = null;
message = "获取问卷所属组织失败!";
QssLogHelper.Log("获取问卷所属组织失败!", "创建问卷时，获取问卷所属组织失败!", QssLogType.Error);
return(QssResult.Fail);
}
orgName = organize.Name;
}
// 判断组织类型
if (isBelongOrganize && organize.IsTemp)
{
// 临时组织
rdOrgs.Add(organize);
wtOrgs.Add(organize);
state = QssQuesAndVoteState.Pass;
}
else
{
// 允许查看结果的组织
List <int> rdIds = new List <int>();
rdIds.AddRange(((JArray)global["RdOrganize"]).Select(x => Convert.ToInt32(x["Id"])));
if (isBelongOrganize && !rdIds.Contains(organize.Id))
{
rdIds.Add(organize.Id); // 可查看组织必须包含所属组织
}
rdOrgs.AddRange(DbSession.OrganizeDal.GetEntities(p => rdIds.Contains(p.Id) && p.State == QssOrganizeState.Pass));
// 允许填写的组织
List <int> wtIds = new List <int>();
wtIds.AddRange(((JArray)global["WtOrganize"]).Select(x => Convert.ToInt32(x["Id"])));
if (isBelongOrganize && !wtIds.Contains(organize.Id))
{
wtIds.Add(organize.Id); // 可填写组织必须包含所属组织
}
wtOrgs.AddRange(DbSession.OrganizeDal.GetEntities(p => wtIds.Contains(p.Id) && p.State == QssOrganizeState.Pass));
// 以个人名义创建或创建者为所属组织创建者
if (!isBelongOrganize || (isBelongOrganize && organize.Admin.Id == user.Id))
{
state = QssQuesAndVoteState.NoAudited;
}
// 系统管理员创建的直接通过
if (user.Role.Name == QssRoleType.SysAdmin.ToString())
{
state = QssQuesAndVoteState.Pass; auditor = user;
}
}
// 创建问卷
Question question = new Question()
{
Title = (string)global["Title"],
Introduct = (string)global["Introduct"],
IsBelongOrganize = isBelongOrganize,
BelongTo = Convert.ToInt32(global["BelongTo"]),
Publisher = isBelongOrganize ? orgName : user.Name,
CreateTime = DateTime.Now,
Creator = user,
Auditor = auditor,
BeginTime = beginTime,
EndTime = endTime,
State = state,
QuesRdOrganizes = rdOrgs,
QuesWtOrganizes = wtOrgs,
IsNotice = false,
};
// 更新添加的问卷数量
user.AddQuesNum = user.LastAddQuesTime.AddDays(1) < DateTime.Now ? 1 : user.AddQuesNum++;
// 保存问卷
try
{
CurrentDal.Add(question);
DbSession.SaveChanges();
// 保存成功
ques = question;
message = "创建问卷成功";
QssLogHelper.Log("创建问卷成功", $"{user.Name}({user.Account}) 创建问卷 {question.Title}({question.Id}) 成功!", QssLogType.Info);
return(QssResult.Success);
}
catch (Exception exception)
{
// 保存失败
ques = null;
message = $"创建问卷失败！失败原因：{exception}；请重试。";
QssLogHelper.Log("创建问卷失败", $"{user.Name}({user.Account}) 创建问卷 {question.Title}({question.Id}) 失败!", QssLogType.Error, exception);
return(QssResult.Fail);
}
}
public Organize GetOrganize(string f_OrganizeId)
{
Sys_Organize sys_organize = _repository.FindEntity <Sys_Organize>(p => p.F_Id == f_OrganizeId);
var organize = new Organize();
if (sys_organize != null)
{
if (!string.IsNullOrEmpty(sys_organize.F_Address))
{
organize.F_Address = sys_organize.F_Address;
}
if (!string.IsNullOrEmpty(sys_organize.F_AreaId))
{
organize.F_AreaId = sys_organize.F_AreaId;
}
if (!string.IsNullOrEmpty(sys_organize.F_CategoryId))
{
organize.F_CategoryId = sys_organize.F_CategoryId;
}
organize.F_CreatorTime = sys_organize.F_CreatorTime;
if (!string.IsNullOrEmpty(sys_organize.F_CreatorUserId))
{
organize.F_CreatorUserId = sys_organize.F_CreatorUserId;
}
if (!string.IsNullOrEmpty(sys_organize.F_DeleteUserId))
{
organize.F_DeleteUserId = sys_organize.F_DeleteUserId;
}
if (!string.IsNullOrEmpty(sys_organize.F_Description))
{
organize.F_Description = sys_organize.F_Description;
}
if (!string.IsNullOrEmpty(sys_organize.F_Email))
{
organize.F_Email = sys_organize.F_Email;
}
//organize.F_EnabledMark = sys_organize.F_EnabledMark;
if (!string.IsNullOrEmpty(sys_organize.F_EnCode))
{
organize.F_EnCode = sys_organize.F_EnCode;
}
if (!string.IsNullOrEmpty(sys_organize.F_Fax))
{
organize.F_Fax = sys_organize.F_Fax;
}
if (!string.IsNullOrEmpty(sys_organize.F_FullName))
{
organize.F_FullName = sys_organize.F_FullName;
}
organize.F_Id = sys_organize.F_Id;
if (sys_organize.F_LastModifyTime != null)
{
organize.F_LastModifyTime = sys_organize.F_LastModifyTime;
}
if (!string.IsNullOrEmpty(sys_organize.F_LastModifyUserId))
{
organize.F_LastModifyUserId = sys_organize.F_LastModifyUserId;
}
if (sys_organize.F_Layers != null)
{
organize.F_Layers = sys_organize.F_Layers;
}
if (!string.IsNullOrEmpty(sys_organize.F_ManagerId))
{
organize.F_ManagerId = sys_organize.F_ManagerId;
}
if (!string.IsNullOrEmpty(sys_organize.F_MobilePhone))
{
organize.F_MobilePhone = sys_organize.F_MobilePhone;
}
if (!string.IsNullOrEmpty(sys_organize.F_ParentId))
{
organize.F_ParentId = sys_organize.F_ParentId;
}
if (!string.IsNullOrEmpty(sys_organize.F_ShortName))
{
organize.F_ShortName = sys_organize.F_ShortName;
}
if (sys_organize.F_SortCode != null)
{
organize.F_SortCode = sys_organize.F_SortCode;
}
if (!string.IsNullOrEmpty(sys_organize.F_TelePhone))
{
organize.F_TelePhone = sys_organize.F_TelePhone;
}
if (!string.IsNullOrEmpty(sys_organize.F_WeChat))
{
organize.F_WeChat = sys_organize.F_WeChat;
}
}
return(organize);
}
/// <summary>
/// Force creating the movie list when an element is deleted from db or added
/// </summary>
public static void RefreshAndOrganize()
{
AllMovies = db.Movie_Data.Select(x => x).ToList();
Organize.ByDate();
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="organize"></param>
public QssSendOrgLetterEmail(Organize organize)
{
currentOrg = organize;
}
