dccloud/WebAPIBase.NetCore/WebAPIBase.NetCore.BusinessCore/BaseCore/UnitManager.cs

using Sugar.Enties;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Linq.Expressions;
using WebAPIBase.Utils;

public class UnitManager : DbContext<Unit>
{

    //当前类已经继承了 DbContext增、删、查、改的方法

    //这里面写的代码不会给覆盖,如果要重新生成请删除 UnitManager.cs
    /// <summary>
    /// 根据项目ID获取项目下的部门，及部门下面的人员，获取到的是树形结构
    /// </summary>
    /// <param name="projectid">项目ID</param>
    public TreeDTO GetUnitUser(string projectid)
    {
        //获取项目
        var root = UnitDb.GetList(m => m.UnitCode == projectid).FirstOrDefault();
        var tree = new TreeDTO();
        tree.Id = root.UnitCode;
        tree.Name = root.UnitName;
        tree.User = false;
        tree.Checked = false;
        tree.Pid = "-1";
        //获取部门
        var subList = UnitDb.GetList(m => m.ParentUnitCode == projectid);
        var list = new List<TreeDTO>();
        foreach (var item in subList)
        {
            var subTree = new TreeDTO();
            subTree.Id = item.UnitCode;
            subTree.Name = item.UnitName;
            subTree.User = false;
            subTree.Checked = false;
            subTree.Pid = item.ParentUnitCode;
            //获取岗位
            var stationList = StationDb.GetList(g => g.UnitCode == item.UnitCode);
            var subStationList = new List<TreeDTO>();
            foreach (var station in stationList)
            {
                var subStationTree = new TreeDTO();
                subStationTree.Id = station.StationCode;
                subStationTree.Name = station.StationName;
                subStationTree.User = false;
                subStationTree.Checked = false;
                subStationTree.Pid = item.UnitCode;
                //自用户表和岗位用户关联表中获取用户数据
                var queryList = Db.Queryable<SystemUser, StationUser>((su, st) => new object[] { JoinType.Inner, su.UserCode == st.UserCode }).Where((su, st) => st.StationCode == station.StationCode).Select((su, st) => new SubNodeDTO { Id = su.UserCode, Name = su.UserName, Pid = st.StationCode }).ToList();
                var userList = new List<TreeDTO>();
                foreach (var item1 in queryList)
                {
                    var subUserTree = new TreeDTO();
                    subUserTree.Id = item1.Id;
                    subUserTree.Name = item1.Name;
                    subUserTree.Pid = item1.Pid;
                    subUserTree.User = true;
                    subUserTree.Checked = false;
                    userList.Add(subUserTree);
                }
                subStationTree.Children = userList;

                subStationList.Add(subStationTree);
            }
            subTree.Children = subStationList;
            list.Add(subTree);
        }
        tree.Children = list;

        return tree;
    }

    /// <summary>
    /// 根据项目ID获取项目下的部门，树形结构
    /// </summary>
    /// <param name="projectid"></param>
    /// <returns></returns>
    public TreeDTO GetUnit(string projectid)
    {
        //获取项目
        var root = UnitDb.GetList(m => m.UnitCode == projectid).FirstOrDefault();
        var tree = new TreeDTO();
        tree.Id = root.UnitCode;
        tree.Name = root.UnitName;
        tree.User = false;
        tree.Checked = false;
        tree.Pid = "-1";
        //获取部门
        var subList = UnitDb.GetList(m => m.ParentUnitCode == projectid);
        var list = new List<TreeDTO>();
        foreach (var item in subList)
        {
            var subTree = new TreeDTO();
            subTree.Id = item.UnitCode;
            subTree.Name = item.UnitName;
            subTree.User = true;
            subTree.Checked = false;
            subTree.Pid = item.ParentUnitCode;

            list.Add(subTree);
        }
        tree.Children = list;

        return tree;
    }


    public List<TreeDTO> GetMaterial(string classCode)
    {
        var returnList = new List<TreeDTO>();
        //获取项目
        var list = SystemGroupDb.GetList(m => m.ClassCode == classCode && string.IsNullOrEmpty(m.ParentCode));
        foreach (var item in list)
        {
            var tree = new TreeDTO();
            tree.Id = item.GroupCode;
            tree.Name = item.GroupName;
            tree.User = false;
            tree.Checked = false;
            tree.Pid = "-1";

            tree.Children = GetMaterialRecursion(tree, item);


            returnList.Add(tree);
        }
        return returnList;
    }

    private List<TreeDTO> GetMaterialRecursion(TreeDTO tree, SystemGroup parentItem)
    {
        var list1 = new List<TreeDTO>();
        var subList = SystemGroupDb.GetList(m => m.ParentCode == parentItem.GroupCode);
        foreach (var subItem in subList)
        {
            var subTree = new TreeDTO();
            subTree.Id = subItem.GroupCode;
            subTree.Name = subItem.GroupName;
            subTree.User = true;
            subTree.Checked = false;
            subTree.Pid = subItem.ParentCode;
            subTree.Children = GetMaterialRecursion(subTree, subItem);
            list1.Add(subTree);
        }
        if (subList.Count <= 0)
        {
            tree.User = true;
            tree.Children = null;
        }
        else
        {
            tree.Children = list1;
        }

        return list1;
    }
    /// <summary>
    /// 根据用户代码获取所属部门
    /// </summary>
    /// <param name="userCode"></param>
    /// <returns></returns>
    public string GetUserDepartment(string userCode)
    {
        string sql = $"SELECT stationcode FROM stationuser WHERE usercode='{userCode}'";
        var stationcode = Db.Ado.GetString(sql);
        if (string.IsNullOrEmpty(stationcode))
        {
            return "";
        }
        sql = $"SELECT UnitCode FROM station WHERE stationcode='{stationcode}'";
        var unitcode = Db.Ado.GetString(sql);
        if (string.IsNullOrEmpty(unitcode))
        {
            return "";
        }
        sql = $"SELECT FullCode FROM unit WHERE unitcode='{unitcode}'";
        var fullcode = Db.Ado.GetString(sql);
        if (string.IsNullOrEmpty(fullcode))
        {
            return "";
        }
        var arr = fullcode.Split('-');
        var list = Db.Queryable<Unit>().Where(u => arr.Contains(u.UnitCode)).ToList();
        var str = "";
        foreach (var item in list)
        {
            str += item.UnitName + "-";
        }
        if (str.EndsWith("-"))
        {
            str = str.Substring(0, str.Length - 1);
        }
        return str;
    }

    /// <summary>
    /// 获取用户所在部门的编码
    /// </summary>
    /// <param name="userCode"></param>
    /// <returns></returns>
    public string GetUserDepartmentCode(string userCode)
    {
        string sql = $"SELECT stationcode FROM stationuser WHERE usercode='{userCode}'";
        var stationcode = Db.Ado.GetString(sql);
        if (string.IsNullOrEmpty(stationcode))
        {
            return "";
        }
        sql = $"SELECT UnitCode FROM station WHERE stationcode='{stationcode}'";
        var unitcode = Db.Ado.GetString(sql);

        return unitcode;
    }
    /// <summary>
    /// 根据用户号获取岗位信息
    /// </summary>
    /// <param name="userCode"></param>
    /// <returns></returns>
    public Station GetUserStation(string userCode)
    {
        string sql = $"SELECT stationcode FROM stationuser WHERE usercode='{userCode}'";
        var stationcode = Db.Ado.GetString(sql);
        if (string.IsNullOrEmpty(stationcode))
        {
            return null;
        }
        sql = $"SELECT * FROM station WHERE stationcode='{stationcode}'";
        var station = Db.SqlQueryable<Station>(sql).First();

        return station;
    }
    /// <summary>
    /// 获取项目列表
    /// </summary>
    /// <returns></returns>
    public List<Unit> GetProjectList()
    {
        var list = UnitDb.GetList(mbox => mbox.UnitType == "项目").ToList();
        return list;
    }

    /// <summary>
    /// 根据规则返回系统编码
    /// </summary>
    /// <param name="codeName"></param>
    /// <param name="codeRule"></param>
    /// <param name="startNum"></param>
    /// <param name="systemCode"></param>
    /// <param name="projectCode"></param>
    /// <param name="buildingCode"></param>
    /// <param name="roomCode"></param>
    /// <param name="billDate"></param>
    /// <returns></returns>
    public string GetNewSysCode(string codeName, string codeRule, int startNum, int systemCode, string projectCode, string buildingCode, string roomCode, DateTime? billDate)
    {
        string text = codeName;
        int length = 0;
        string text2 = "";
        string text3 = "『number』";
        if (!string.IsNullOrEmpty(projectCode))
        {
            text = codeName + "-" + projectCode;
        }
        DateTime dateTime = DateTime.Today;
        if (billDate.HasValue)
        {
            dateTime = billDate.Value;
        }
        StringBuilder stringBuilder = new StringBuilder(codeRule);
        int num = 0;
        while (num < 100)
        {
            num++;
            int num2 = stringBuilder.ToString().IndexOf("{");
            if (num2 < 0)
            {
                text2 += stringBuilder.ToString();
                break;
            }
            int num3 = stringBuilder.ToString().IndexOf("}", num2);
            if (num3 < 0)
            {
                break;
            }
            if (num2 > 0)
            {
                text2 += stringBuilder.ToString(0, num2);
            }
            string text4 = stringBuilder.ToString(num2 + 1, num3 - num2 - 1);
            string text5 = "";
            if (text4.StartsWith("#"))
            {
                length = text4.Length;
                text2 += text3;
            }
            else
            {
                switch (text4.ToLower())
                {
                    case "y":
                        text5 = dateTime.Year.ToString();
                        break;
                    case "yy":
                        text5 = dateTime.Year.ToString().PadLeft(2, '0');
                        break;
                    case "yyyy":
                        text5 = dateTime.Year.ToString().PadLeft(4, '0');
                        break;
                    case "m":
                        text5 = dateTime.Month.ToString();
                        break;
                    case "mm":
                        text5 = dateTime.Month.ToString().PadLeft(2, '0');
                        break;
                    case "d":
                        text5 = dateTime.Day.ToString();
                        break;
                    case "dd":
                        text5 = dateTime.Day.ToString().PadLeft(2, '0');
                        break;
                    case "p":
                        text5 = GetProjectId(projectCode, systemCode);
                        break;
                    case "b":
                        text5 = ((!string.IsNullOrEmpty(buildingCode) || string.IsNullOrEmpty(roomCode)) ? GetBuildingName(buildingCode, systemCode) : GetBuildingName(GetBuildingCodeByRoom(roomCode, systemCode), systemCode));
                        break;
                    case "r":
                        text5 = GetRoomName(roomCode, systemCode);
                        break;
                }
                if (text5 != "")
                {
                    text = text + "-" + text5;
                    text2 += text5;
                }
            }
            stringBuilder.Remove(0, num3 + 1);
        }
        string sysCodeFromDatabase = GetSysCodeFromDatabase(text, systemCode, startNum, length);
        if (text2 == "")
        {
            text2 = text3;
        }
        string text6 = text2.Replace(text3, sysCodeFromDatabase);
        if (text6.Length > 50)
        {
            throw new ApplicationException("自动生成的编号长度超出50位，请检查编号规则");
        }
        return text6;
    }

    public string GetNewSysCode(string codeName, string codeRule, int startNum, int systemCode, string projectCode, string buildingCode, string roomCode)
    {
        return GetNewSysCode(codeName, codeRule, startNum, systemCode, projectCode, buildingCode, roomCode, null);
    }

    public string GetNewSysCode(string codeName, string codeRule, int startNum, int systemCode, string projectCode)
    {
        return GetNewSysCode(codeName, codeRule, startNum, systemCode, projectCode, "", "");
    }




    private string GetProjectId(string projectCode, int systemCode)
    {
        switch (systemCode)
        {
            case 0:
                return GetPmProjectId(projectCode);
            case 1:
                return GetSaProjectId(projectCode);
            case 3:
                return GetPrManagementId(projectCode);
            default:
                return "";
        }
    }

    private string GetPmProjectId(string projectCode)
    {
        if (!string.IsNullOrEmpty(projectCode))
        {
            string commandText = "select ProjectId from project where ProjectCode = @projectCode";

            DataTable dataTable = Db.Ado.GetDataTable(commandText, new List<SugarParameter>() {
                new SugarParameter("@projectCode", projectCode)
            });
            if (dataTable.Rows.Count > 0)
            {
                return string.Concat(dataTable.Rows[0][0], "");
            }
        }
        return "";
    }

    private string GetSaProjectId(string projectCode)
    {
        if (!string.IsNullOrEmpty(projectCode))
        {
            string commandText = "select pCode from SaProject where pId = @projectCode";
            DataTable dataTable = Db.Ado.GetDataTable(commandText, new List<SugarParameter>()  {

                new SugarParameter("@projectCode", projectCode)
            });
            if (dataTable.Rows.Count > 0)
            {
                return string.Concat(dataTable.Rows[0][0], "");
            }
        }
        return "";
    }

    private string GetPrManagementId(string projectCode)
    {
        if (!string.IsNullOrEmpty(projectCode))
        {
            string commandText = "select mCode from PrManagement where mId = @projectCode";
            DataTable dataTable = Db.Ado.GetDataTable(commandText, new List<SugarParameter>()  {

                new SugarParameter("@projectCode", projectCode)
            });
            if (dataTable.Rows.Count > 0)
            {
                return string.Concat(dataTable.Rows[0][0], "");
            }
        }
        return "";
    }

    private string GetBuildingName(string buildingCode, int systemCode)
    {
        if (systemCode == 1)
        {
            return GetSaBuildingName(buildingCode);
        }
        return "";
    }

    private string GetSaBuildingName(string buildingCode)
    {
        if (!string.IsNullOrEmpty(buildingCode))
        {
            string commandText = "select bName from SaBuilding where bId = @buildingCode";
            DataTable dataTable = Db.Ado.GetDataTable(commandText, new List<SugarParameter>()  {

                new SugarParameter("@buildingCode", buildingCode)
            });
            if (dataTable.Rows.Count > 0)
            {
                return string.Concat(dataTable.Rows[0][0], "");
            }
        }
        return "";
    }

    private string GetBuildingCodeByRoom(string roomCode, int systemCode)
    {
        if (systemCode == 1)
        {
            return GetSaBuildingCodeByRoom(roomCode);
        }
        return "";
    }

    private string GetSaBuildingCodeByRoom(string roomCode)
    {
        if (!string.IsNullOrEmpty(roomCode))
        {
            string commandText = string.Format("select buildingId from SaRoom where rId = {0}", "@roomCode");
            DataTable dataTable = Db.Ado.GetDataTable(commandText, new List<SugarParameter>()  {

                new SugarParameter("@roomCode", roomCode)
            });
            if (dataTable.Rows.Count > 0)
            {
                return string.Concat(dataTable.Rows[0][0], "");
            }
        }
        return "";
    }

    private string GetRoomName(string roomCode, int systemCode)
    {
        if (systemCode == 1)
        {
            return GetSaRoomName(roomCode);
        }
        return "";
    }

    private string GetSaRoomName(string roomCode)
    {
        if (!string.IsNullOrEmpty(roomCode))
        {
            string commandText = string.Format("select rFullName from SaRoom where rId = {0}", "@roomCode");
            DataTable dataTable = Db.Ado.GetDataTable(commandText, new List<SugarParameter>()  {

                new SugarParameter("@roomCode", roomCode)
            });
            if (dataTable.Rows.Count > 0)
            {
                return string.Concat(dataTable.Rows[0][0], "");
            }
        }
        return "";
    }
    private string GetSysCodeFromDatabase(string codeName, int systemCode, int startNum, int length)
    {
        int num = 0;
        string commandText = "select CodeValue from SysCode where CodeName = @codeName and SystemName = @systemCode";
        DataTable dataTable = Db.Ado.GetDataTable(commandText, new List<SugarParameter>()  {

                new SugarParameter("@codeName", codeName),
                new SugarParameter("@systemCode", systemCode)
            });
        if (dataTable.Rows.Count > 0)
        {
            try
            {
                num = int.Parse(string.Concat(dataTable.Rows[0]["CodeValue"], ""));
            }
            catch
            {
                throw new Exception(string.Format("自动编号SysCode“{0}”不是有效的数值", dataTable.Rows[0]["CodeValue"]));
            }
        }
        num++;
        if (num < startNum)
        {
            num = startNum;
        }
        commandText = ((dataTable.Rows.Count <= 0) ? "insert into SysCode (CodeName, CodeValue, SystemName) values (@codeName, @num, @systemCode)" : "update SysCode set CodeValue = @num where CodeName = @codeName and SystemName = @systemCode");

        Db.Ado.ExecuteCommand(commandText, new List<SugarParameter>()  {

                new SugarParameter("@codeName", codeName),
                new SugarParameter("@num", num),
                new SugarParameter("@systemCode", systemCode)
            });

        string text = num.ToString();
        if (length > 0)
        {
            text = text.PadLeft(length, '0');
        }
        return text;
    }

    /// <summary>
    /// 根据项目代码获取材料合同
    /// </summary>
    /// <param name="projectcode"></param>
    /// <param name="searchValue"></param>
    /// <returns></returns>
    public List<Contract> GetContracts(string projectcode, string searchValue)
    {
        var sql = $"SELECT  * FROM dbo.Contract WHERE ProjectCode='{projectcode}' AND contracttype='材料合同' AND Status IN (0,2)";
        if (!string.IsNullOrEmpty(searchValue))
        {
            sql += $" and (ContractID like '%{searchValue}%' or ContractName like '%{searchValue}%')";
        }
        var list = Db.Ado.SqlQuery<Contract>(sql);
        return list;
    }
    /// <summary>
    /// 材料合同或工程合同列表选择，供入库单和领料单添加或修改使用
    /// </summary>
    /// <param name="lamda"></param>
    /// <returns></returns>
    public List<Contract> GetContracts(Expression<Func<Contract, bool>> lamda)
    {
        var list = Db.Queryable<Contract>().Where(lamda).OrderBy("ContractID DESC").ToList();
        return list;
    }

    /// <summary>
    /// 根据cbs费用项表中的费用编码，获取该费用各级费用编码的全编码
    /// </summary>
    /// <param name="costCode"></param>
    /// <returns></returns>
    public string GetCBSFullCode(string costCode)
    {
        var sql = $"SELECT dbo.GetCBSFullCode('{costCode}')";
        var fullCode = Db.Ado.GetString(sql);
        return fullCode;
    }
    /// <summary>
    /// 根据费用代码获取该费用的全名称
    /// </summary>
    /// <param name="code"></param>
    /// <returns></returns>
    public string GetCostFullName(string code)
    {
        string text = "";
        if (code.IsNullOrEmpty())
        {
            return text;
        }

        string cBSFullCode = GetCBSFullCode(code);
        string[] array = cBSFullCode.Split('-');
        int num = 0;

        for (int i = num; i < array.Length; i++)
        {
            if (text != "")
            {
                text += "->";
            }
            text += GetCostName(array[i]);
        }
        return text;

    }

    /// <summary>
    /// 根据费用代码获取费用名称
    /// </summary>
    /// <param name="code"></param>
    /// <returns></returns>
    public string GetCostName(string code)
    {
        string result = "";
        if (code.IsNullOrEmpty())
        {
            return result;
        }
        var sql = $"SELECT CostName FROM dbo.cbs WHERE CostCode='{code}'";
        result = Db.Ado.GetString(sql);
        return result;

    }
}