[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.周度計(jì)劃,
                VehicleOrderPlanType.小品種計(jì)劃,
                VehicleOrderPlanType.移庫(kù)計(jì)劃
            };
            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>
        /// 以分頁(yè)的形式查詢數(shù)據(jù)
        /// </summary>
        /// <typeparam name="TValueObject"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pageRequest"></param>
        /// <param name="parameters">參數(shù)的匿名對(duì)象</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);
   }