[DependsOn(typeof(AbpZeroCoreEntityFrameworkCoreModule))]
    [DependsOn(typeof(AbpDapperModule))]
    public class SalesDataModule : AbpModule {
        public override void Initialize() {         
            IocManager.RegisterAssemblyByConvention(typeof(SalesDataModule).GetAssembly());
            DapperExtensions.DapperExtensions.SetMappingAssemblies(new List<Assembly> { typeof(SalesDataModule).GetAssembly() });
        }
    }

public virtual void StartModules()
      {
          var sortedModules = _modules.GetSortedModuleListByDependency();
          sortedModules.ForEach(module => module.Instance.PreInitialize());
          sortedModules.ForEach(module => module.Instance.Initialize());
          sortedModules.ForEach(module => module.Instance.PostInitialize());
      }

public sealed class VehicleOrderPlanMapper : ClassMapper<VehicleOrderPlan> {
    public VehicleOrderPlanMapper() {
        Table("VehicleOrderPlan");
        Map(x => x.Branch).Ignore();
        AutoMap();
    }
}

public class VehicleOrderPlanDapperRepository : DcsDapperRepositoryBase<VehicleOrderPlan>, IVehicleOrderPlanDapperRepository {
        public VehicleOrderPlanDapperRepository(IActiveTransactionProvider activeTransactionProvider) : base(activeTransactionProvider) {
        }
 
        public IEnumerable<WeeklyOrderPlanSummary> GetWeeklyOrderPlanSummary(int? yearOfPlan, int? weekOfPlan, string provinceName,
            [CanBeNull]VehicleOrderPlanType[] planType, string marketName, PageRequest page) {
            var sqlParam = new StringBuilder()
                .AppendIf(yearOfPlan.HasValue, $" AND p.YearOfPlan = :{nameof(yearOfPlan)}")
                .AppendIf(weekOfPlan.HasValue, $" AND p.WeekOfPlan = :{nameof(weekOfPlan)}")
                .AppendIf(!provinceName.IsNullOrWhiteSpace(), $@" AND EXISTS ( SELECT 1
    FROM Company C WHERE c.Id = p.DealerId AND c.Status <> {(int)MasterDataStatus.作废}
        AND c.ProvinceName like '%' || :{nameof(provinceName)} || '%')");
 
            var planTypes = new[] {
                VehicleOrderPlanType.周度计划,
                VehicleOrderPlanType.小品种计划,
                VehicleOrderPlanType.移库计划
            };
            if (planType != null && planType.Length > 0)
                planTypes = planTypes.Intersect(planType).ToArray();
 
            var departmentParam = string.Empty;
            if (!marketName.IsNullOrWhiteSpace())
                departmentParam = $" AND (m.Name LIKE '%' || :{nameof(marketName)} || '%')";
 
            var sql = $@"
SELECT p.YearOfPlan, p.WeekOfPlan, TRUNC(p.CreateTime) AS CreateTime, TRUNC(p.StartTime) AS StartTime, TRUNC(p.EndTime) AS EndTime,
       pd.ProductCode, pd.ProductName, pd.ProductType, pd.ProductCategoryCode AS VehicleModelCode, pd.ProductCategoryName AS VehicleModelName,
       Sum(pd.PlannedQuantity) AS PlannedQuantity, Sum(pd.FirstPlannedQuantity) AS FirstPlannedQuantity,
       Sum(pd.QuantityOfAssessment) AS QuantityOfAssessment, Sum(pd.ConfirmedQuantity) AS ConfirmedQuantity
FROM VehicleOrderPlan p
CROSS JOIN VehicleOrderPlanDetail pd
WHERE (p.Status <> {(int)VehicleOrderPlanStatus.作废} AND p.Type in {planTypes.ToSqlInParam()} {sqlParam} AND EXISTS (
    SELECT 1
    FROM DealerMarketDptRelation dm
    WHERE (((dm.BranchId = p.BranchId) AND (dm.DealerId = p.DealerId)) AND (dm.Status = {(int)BaseDataStatus.有效})) AND EXISTS (
        SELECT 1
        FROM MarketingDepartment m
        WHERE ((m.BranchCode = {SunlightConsts.DEFAULT_BRANCH_QRSALESLTD}) AND (m.Status = {(int)BaseDataStatus.有效})) {departmentParam}
            AND (m.Id = dm.MarketId)))) AND (p.Id = pd.VehicleOrderPlanId)
GROUP BY p.YearOfPlan, p.WeekOfPlan, TRUNC(p.CreateTime), TRUNC(p.StartTime), TRUNC(p.EndTime), pd.ProductCode,
         pd.ProductName, pd.ProductType, pd.ProductCategoryCode, pd.ProductCategoryName";
 
            return QueryPaged<WeeklyOrderPlanSummary>(sql, page, new {
                yearOfPlan,
                weekOfPlan,
                provinceName,
                marketName
            });
        }
    }

public class DcsDapperRepositoryBase<TEntity> : DapperEfRepositoryBase<DcsDbContext, TEntity>
        where TEntity : class, IEntity<int> {
        public DcsDapperRepositoryBase(IActiveTransactionProvider activeTransactionProvider) : base(activeTransactionProvider) {
        }
 
        /// <summary>
        /// 以分页的形式查询数据
        /// </summary>
        /// <typeparam name="TValueObject"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pageRequest"></param>
        /// <param name="parameters">参数的匿名对象</param>
        /// <returns></returns>
        protected IEnumerable<TValueObject> QueryPaged<TValueObject>(string sql, PageRequest pageRequest, object parameters = null)
            where TValueObject : ValueObjectBase {
            var orderCondition = (string.IsNullOrWhiteSpace(pageRequest.Ordering) ? string.Empty : "ORDER BY " + pageRequest.Ordering);
            orderCondition.SqlInjectionInspect();
            var pagedSql = $@"WITH ""_data"" AS ({sql}),
     ""_count"" AS (SELECT COUNT(0) AS OverallCount FROM ""_data"")
SELECT *
FROM (SELECT A.*, ROWNUM AS ""RowNum""
      FROM (SELECT * FROM ""_data""
            {orderCondition}) A
      WHERE ROWNUM <= {pageRequest.PageSize * (pageRequest.PageIndex + 1)}) B,
     ""_count""
WHERE ""RowNum"" > {pageRequest.PageSize * pageRequest.PageIndex}";
            return Query<TValueObject>(pagedSql, parameters);
        }
    }

public interface IVehicleOrderPlanDapperRepository : IDapperRepository<VehicleOrderPlan> {
       IEnumerable<WeeklyOrderPlanSummary> GetWeeklyOrderPlanSummary(int? yearOfPlan, int? weekOfPlan, string provinceName,
           [CanBeNull] VehicleOrderPlanType[] planType, string marketName, PageRequest page);
   }