OA2023/OASystem/OASystem.Api/OAMethodLib/ExcelOutput/Excel_BusConfItemList.cs

using Aspose.Cells;
using OASystem.Domain.Entities.Business;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Business;
using OASystem.Infrastructure.Repositories.Business;
using StackExchange.Redis;
using System.Web;

namespace OASystem.API.OAMethodLib.ExcelOutput
{
    public class Excel_BusConfItemList
    {
        private CommonBusRepository _busRep;
        private string url;
        private string path;
        public Excel_BusConfItemList()
        {
            _busRep = AutofacIocManager.Instance.GetService<CommonBusRepository>();
            url = AppSettingsHelper.Get("ExcelBaseUrl");
            path = AppSettingsHelper.Get("ExcelBasePath");
            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);//不存在就创建文件夹
            }
        }

        public string Excel(Bus_ConfItemListInfo _entity)
        {


            if (!string.IsNullOrEmpty(_entity.ExcelPath))
            {
                if (System.IO.File.Exists(_entity.ExcelPath))
                {
                    //存在 
                    string fileUrl = url + path + _entity.ExcelPath;
                    return fileUrl;
                }
                else
                {
                    //不存在 
                    return CreateExcel(_entity);
                }
            }
            else
            {
                return CreateExcel(_entity);
            }

            return "";
        }

        private string CreateExcel(Bus_ConfItemListInfo _entity)
        {
            string sql = string.Format(@" Select * From(
Select ROW_NUMBER() Over(order By Id desc) as RowNumber,riv.FullName as VendorName,rid.ItemName
,rit.TypeName as ItemTypeName,bci.[Count],bci.CurrCost as Price,bci.OpRemark as ItemRemark
From Bus_ConfItemList as bcil With(Nolock) 
Inner Join Bus_ConfItem as bci With(Nolock) On bcil.Id=bci.ConfListId
Inner Join Res_ItemDetail as rid With(Nolock) On bci.ItemId=rid.Id
Inner Join Res_ItemType as rit With(Nolock) On rid.ItemTypeId=rit.Id
Inner Join Res_ItemVendor as riv With(nolock) On rid.VendorId=riv.Id
Where bcil.Id={0}
) as tb  ", _entity.Id);

            List<Bus_ConfItemExportView> _confItemList = _busRep._sqlSugar.SqlQueryable<Bus_ConfItemExportView>(sql).ToList();
            Grp_DelegationInfo _DelegationInfo = _busRep.Query<Grp_DelegationInfo>(s => s.Id == _entity.Diid).First();
            string diName = _DelegationInfo != null ? _DelegationInfo.TeamName : "未知团组" + _entity.Diid;

            WorkbookDesigner designer = new WorkbookDesigner();
            designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/会务物料清单模板.xlsx");
            designer.SetDataSource("Export", _confItemList);
            designer.SetDataSource("ExportDiName", diName);
            designer.SetDataSource("ExportOutputDt", DateTime.Now.ToString("yyyy-MM-dd"));
            designer.SetDataSource("ExportListRemark", _entity.Remark);

            designer.Process();
            string fileName = HttpUtility.UrlEncode("") + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            designer.Workbook.Save(fileName);
            designer = null;

            _entity.ExcelPath = path + fileName;
            _busRep._sqlSugar.Updateable<Bus_ConfItemListInfo>()
                .Where(s => s.Id == _entity.Id)
                .UpdateColumns(s => s.ExcelPath)
                .ExecuteCommand();

            string fileUrl = url + path + _entity.ExcelPath;
            return fileUrl;
        }
    }
}