public static String OrganizationList()
{
OrganizationListViewData view = new OrganizationListViewData();
view.List = new List <Organization>();
List <TreeNode> list;
view.PageData = new PaginationData();
view.PageData.PageIndex = 1;
view.PageData.PageSize = 10;
view.PageData.PagintionJsFunction = "window.System.OrganizationList.search()";
view.PageData.OrderBy = "Code";
string sql = @"SELECT Name AS Text, CAST(UnitGuid AS NVARCHAR(36)) AS ID, CAST(ParentGuid AS NVARCHAR(36)) AS ParentID, '<i class=''ace-icon ' + Icon + '''></i>' AS Icon FROM dbo.Organization ORDER BY Code";
using (DbConnectionScope dcs = new DbConnectionScope())
{
list = DbHelper.Db(sql).ToList <TreeNode>();
view.treeList = TreeNodeList.ToTreeNodeJson(list);
}
view.treeTitle = "组织架构";
TreeNode tr = list.FirstOrDefault();
if (tr == null)
{
tr = new TreeNode();
}
sql = @"SELECT A.*, B.Name AS ParentName
FROM dbo.Organization AS A
LEFT JOIN dbo.Organization AS B ON A.ParentGuid = B.UnitGuid
WHERE A.ParentGuid = @ParentGuid";
view.List = new Pagintion(sql, new { ParentGuid = tr.ID }, view.PageData).ToList <Organization>();
return(PageViewHelper.LoadPage("/System/OrganizationList.aspx", view));
}
public String RoleList()
{
RoleListViewData view = new RoleListViewData();
view.PageData.PagintionJsFunction = "window.System.RoleTab.searchRole()";
view.PageData.OrderBy = "Code";
string sql = @"SELECT A.*, CASE WHEN B.OrganizationName IS NOT NULL THEN STUFF(B.OrganizationName, 1, 1, '') ELSE '' END AS OrganizationName,
CASE WHEN C.UserName IS NOT NULL THEN STUFF(C.UserName, 1, 1, '') ELSE '' END AS UserName
FROM dbo.RoleInfo AS A
OUTER APPLY (SELECT ';' + IB.Name
FROM dbo.RoleToBusiness AS IA
INNER JOIN dbo.Organization AS IB ON IA.BusinessGuid = IB.UnitGuid
WHERE IA.RoleGuid = A.RoleGuid
AND IB.Name > ''
ORDER BY IB.Code
FOR XML PATH('')) AS B(OrganizationName)
OUTER APPLY (SELECT ';' + IB.UserName
FROM dbo.RoleToBusiness AS IA
INNER JOIN dbo.UserInfo AS IB ON IA.BusinessGuid = IB.UserGuid
WHERE IA.RoleGuid = A.RoleGuid
AND IB.UserName > ''
ORDER BY IB.UserCode
FOR XML PATH('')) AS C(UserName)";
using (DbConnectionScope dcs = new DbConnectionScope())
{
view.List = new Pagintion(sql, null, view.PageData).ToList <RoleInfo>();
}
return(PageViewHelper.LoadPage("/System/RoleList.aspx", view));
}
public DomainEFContext(DbConnectionScope dbConnectionScope, bool contextOwnsConnectionScope = false)
: base(dbConnectionScope.Connection, contextOwnsConnection: false)
{
Debug.WriteLine($"{ClassName} - Constructor({nameof(dbConnectionScope)}, {nameof(contextOwnsConnectionScope)}: {contextOwnsConnectionScope})");
_dbConnectionScope = dbConnectionScope;
_contextOwnsConnectionScope = contextOwnsConnectionScope;
}
public DbScope(TransactionScopeOption option, IsolationLevel isolationLevel, TimeSpan timeOut)
{
_disposed = true;
if (option != TransactionScopeOption.Suppress )
{
TransactionOptions tranOp = new TransactionOptions() { IsolationLevel = isolationLevel, Timeout = timeOut };
_tranScope = new TransactionScope(option, tranOp, TransactionScopeAsyncFlowOption.Enabled);
_connScope = new DbConnectionScope(option == TransactionScopeOption.RequiresNew? DbConnectionScopeOption.RequiresNew: DbConnectionScopeOption.Required);
}
else
{
_tranScope = new TransactionScope(TransactionScopeOption.Suppress, TransactionScopeAsyncFlowOption.Enabled);
_connScope = new DbConnectionScope(DbConnectionScopeOption.RequiresNew);
}
_disposed = false;
}
public String SystemTree()
{
TreeViewData view = new TreeViewData();
view.Title = "系统";
string sql = @"SELECT Name AS Text, CAST(SystemGuid AS NVARCHAR(36)) AS ID, CAST(NULL AS NVARCHAR(36)) AS ParentID, '<i class=''ace-icon ''></i>' AS Icon
FROM dbo.SystemInfo
ORDER BY Code";
view.List = "[]";
using (DbConnectionScope dcs = new DbConnectionScope())
{
view.List = TreeNodeList.ToTreeNodeJson(DbHelper.Db(sql).ToList <TreeNode>());
}
return(PageViewHelper.LoadPage("/System/CommonPage/SingleSelectedTree.aspx", view));
}
public String DepartmentTree()
{
TreeViewData view = new TreeViewData();
view.Title = "部门";
string sql = @"SELECT Name AS Text, CAST(UnitGuid AS NVARCHAR(36)) AS ID, CAST(ParentGuid AS NVARCHAR(36)) AS ParentID, '<i class=''ace-icon ' + Icon + '''></i>' AS Icon
FROM dbo.Organization
--WHERE ParentGuid IS NOT NULL
ORDER BY Code";
view.List = "[]";
using (DbConnectionScope dcs = new DbConnectionScope())
{
view.List = TreeNodeList.ToTreeNodeJson(DbHelper.Db(sql).ToList <TreeNode>());
}
return(PageViewHelper.LoadPage("/System/CommonPage/SingleSelectedTree.aspx", view));
}
public string Insert(Customer entity, string stepList)
{
entity.CustomerID = (entity.CustomerID == Guid.Empty ? Guid.NewGuid() : entity.CustomerID);
List <ProcessStep> list = JsonHelper.JsonTo <List <ProcessStep> >(stepList);
using (DbConnectionScope sc = new DbConnectionScope())
{
foreach (ProcessStep item in list)
{
item.ID = Guid.NewGuid();
item.CustomerID = entity.CustomerID;
item.CreatedOn = DateTime.Now;
item.CreatedBy = UserInfoBLL.GetCurrentUserGuid();
item.SetDefaultValue("FinishDate");
item.Insert();
}
return(bll.Insert(entity).ToString());
}
}
/// <summary>
/// 获取提醒记录数
/// </summary>
/// <returns></returns>
public static List <Customer> GetAlertCustomer()
{
string sql = @"SELECT A.*, B.Name AS CompanyName, C.Name AS BusinessTypeName, SUM(BidCost) OVER() AS TotalBidCost, SUM(SaleAmount) OVER() AS TotalSaleAmount,
SUM(FactCost) OVER() AS TotalFactCost, SUM(Profit) OVER() AS TotalProfit, D.StepName, D.EstimateDate
FROM dbo.Customer AS A
LEFT JOIN dbo.Company AS B ON A.CompanyID = B.CompanyID
LEFT JOIN dbo.BusinessType AS C ON A.BusinessType = C.TypeID
OUTER APPLY (SELECT TOP 1 IA.ID AS StepID, IA.StepName, IA.EstimateDate
FROM dbo.ProcessStep AS IA
WHERE A.CustomerID = IA.CustomerID
AND IA.EstimateDate > GETDATE()
AND IA.EstimateDate < GETDATE() + @AlertDays
AND FinishDate IS NULL
ORDER BY IA.EstimateDate, IA.CreatedOn) AS D
WHERE D.StepID IS NOT NULL";
using (DbConnectionScope cs = new DbConnectionScope())
{
return(DbHelper.Db(sql, new { AlertDays = Convert.ToInt32(ConfigurationManager.AppSettings["AlertDays"]) }).ToList <Customer>());
}
}
public string Update(Customer entity, string stepList)
{
List <ProcessStep> list = JsonHelper.JsonTo <List <ProcessStep> >(stepList);
using (DbConnectionScope sc = new DbConnectionScope())
{
List <string> guids = new List <string>()
{
Guid.Empty.ToString()
};
guids.AddRange(list.Select(item => item.ID.ToString()));
string sql = @"DELETE FROM dbo.ProcessStep WHERE CustomerID = @CustomerID AND ID NOT IN('" + string.Join("', '", guids.ToArray()) + "');";
DbHelper.Db(sql, new { CustomerID = entity.CustomerID });
foreach (ProcessStep item in list)
{
item.CustomerID = entity.CustomerID;
if (item.ID == Guid.Empty)
{
item.ID = Guid.NewGuid();
item.CreatedOn = DateTime.Now;
item.CreatedBy = UserInfoBLL.GetCurrentUserGuid();
item.SetDefaultValue("FinishDate");
item.Insert();
}
else
{
item.ModifiedOn = DateTime.Now;
item.ModifiedBy = UserInfoBLL.GetCurrentUserGuid();
item.SetDefaultValue("FinishDate");
item.Update();
}
}
entity.ModifiedOn = DateTime.Now;
entity.ModifiedBy = UserInfoBLL.GetCurrentUserGuid();
return(bll.Update(entity).ToString());
}
}
public async Task <SqlConnection> GetConnectionByNameAsync(string connectionName)
{
return(await DbConnectionScope.GetOpenConnectionAsync <SqlConnection>(_dbFactory.Value, connectionName));
}
public async Task <SqlConnection> GetConnectionByDbNameAsync(string dbname)
{
return(await DbConnectionScope.GetOpenConnectionAsync <SqlConnection>(_dbNameFactory.Value, dbname).ConfigureAwait(false));
}
