sarpay
7/31/2018 - 9:23 AM

Dapper

public List<long> SearchPolicy(SearchPolicy searchPolicy, List<string> selectedCategories)
{
  var culture = new CultureInfo("tr-TR");

  var outerSql = "SELECT * FROM (";
  var innerSql = "SELECT " +
                      "P.ID, " +
                      "ROW_NUMBER() OVER(PARTITION BY P.ID ORDER BY P.ID DESC) rn " +
                 "FROM SGL.POLICY P";

  var gotPolicyInsuredJoin = false;
  const string policyInsuredJoin = " INNER JOIN POLICY_INSURED PI ON P.ID = PI.POLICY_ID";

  var gotMustTanimJoin = false;
  const string mustTanimJoin = " INNER JOIN SG.MUST_TANIM_TABLE M" +
                               " ON P.POLICY_HOLDER_ID = M.SQ_MUST_TANIM_TABLE";

  var gotPlanJoin = false;
  const string planJoin = " INNER JOIN PLAN PL ON PL.ID = PI.PLAN_ID";

  var conditions = " WHERE 1 = 1";

  /* POLICY FILTERS */
  if (selectedCategories.Contains("policyContent"))
  {
      if (searchPolicy.PolicyNo == 0
        && searchPolicy.ProposalStatus == null
        && searchPolicy.StartDate == null
        && searchPolicy.EndDate == null
        && searchPolicy.FromIssueDate == null
        && searchPolicy.ToIssueDate == null)
      {
        throw new UserFriendlyException("\"Poliçe\" arama kriteri eksik.");
      }

      if (searchPolicy.PolicyNo > 0)
      {
        switch (searchPolicy.PolicyType)
        {
          case PolicyTypeEnum.New:
            conditions += " AND P.POLICY_NO = :PolicyNo";
            break;
          case PolicyTypeEnum.Old:
            conditions += " AND P.POLICY_NO = :PolicyNo";
            break;
        }
      }
      if (searchPolicy.ProposalStatus != null)
      {
          //conditions += pConditions.Length > 0 ? " AND " : "";
        conditions += " AND P.PROPOSAL_STATUS = :ProposalStatus";
      }
      if (searchPolicy.StartDate != null)
      {
        conditions += " AND P.START_DATE >= :StartDate";
      }
      if (searchPolicy.EndDate != null)
      {
        conditions += " AND P.END_DATE <= :EndDate";
      }
      if (searchPolicy.FromIssueDate != null)
      {
        conditions += " AND P.ISSUE_DATE >= :FromIssueDate";
      }
      if (searchPolicy.ToIssueDate != null)
      {
        conditions += " AND P.ISSUE_DATE <= :ToIssueDate";
      }
  }

  /* INSURED & POLICY HOLDER FILTERS */
  if (selectedCategories.Contains("policyPersonContent"))
  {
    switch (searchPolicy.PolicyPersonType)
    {
      case "insured":

        if (string.IsNullOrEmpty(searchPolicy.Name)
          && string.IsNullOrEmpty(searchPolicy.Surname)
          && string.IsNullOrEmpty(searchPolicy.IdentityNo)
          && string.IsNullOrEmpty(searchPolicy.RegistryNumber)
          && string.IsNullOrEmpty(searchPolicy.MustKod)
          && string.IsNullOrEmpty(searchPolicy.OldMustKod))
        {
          throw new UserFriendlyException("\"Sigortalı\" arama kriteri eksik.");
        }

        if (!gotPolicyInsuredJoin)
        {
          innerSql += policyInsuredJoin;
          gotPolicyInsuredJoin = true;
        }

        if (gotPolicyInsuredJoin)
        {
          if (!string.IsNullOrEmpty(searchPolicy.Name))
          {
              searchPolicy.Name = "%" + searchPolicy.Name.ToUpper(culture) + "%";
              conditions += " AND PI.NAME LIKE :Name";
          }
          if (!string.IsNullOrEmpty(searchPolicy.Surname))
          {
              searchPolicy.Surname = "%" + searchPolicy.Surname.ToUpper(culture) + "%";
              conditions += " AND PI.SURNAME LIKE :Surname";
          }
          if (!string.IsNullOrEmpty(searchPolicy.IdentityNo))
          {
              conditions += " AND PI.IDENTITY_NO = :IdentityNo";
              if (searchPolicy.IdentityType != null)
              {
                  conditions += " AND PI.IDENTITY_TYPE = :IdentityType";
              }
          }
          if (!string.IsNullOrEmpty(searchPolicy.RegistryNumber))
          {
              conditions += " AND PI.REGISTRY_NUMBER = :RegistryNumber";
          }
          if (!string.IsNullOrEmpty(searchPolicy.MustKod))
          {
              if (!gotMustTanimJoin)
              {
                  innerSql += mustTanimJoin;
                  gotMustTanimJoin = true;
              }
              if (gotMustTanimJoin)
              {
                  conditions += " AND M.MUST_KOD = :MustKod";
              }
          }
          if (!string.IsNullOrEmpty(searchPolicy.OldMustKod))
          {
              conditions += " AND PI.EXTERNAL_CUSTOMER_CODE = :OldMustKod";
          }
        }

        break;

      case "holder":

        if (string.IsNullOrEmpty(searchPolicy.Name)
          && string.IsNullOrEmpty(searchPolicy.Surname)
          && string.IsNullOrEmpty(searchPolicy.IdentityNo)
          && string.IsNullOrEmpty(searchPolicy.MustKod))
        {
          throw new UserFriendlyException("\"Sigorta Ettiren\" arama kriteri eksik.");
        }

        if (!gotMustTanimJoin)
        {
          innerSql += mustTanimJoin;
          gotMustTanimJoin = true;
        }

        if (gotMustTanimJoin)
        {
          conditions += " AND M.KAYNAK_KODU = 1"; /* sadece Sigortalilar */

          if (!string.IsNullOrEmpty(searchPolicy.Name))
          {
              searchPolicy.Name = "%" + searchPolicy.Name.ToUpper(culture) + "%";
              conditions += " AND M.MUST_ADI LIKE :Name";
          }
          if (!string.IsNullOrEmpty(searchPolicy.Surname))
          {
              searchPolicy.Surname = "%" + searchPolicy.Surname.ToUpper(culture) + "%";
              conditions += " AND M.MUST_SOYADI LIKE :Surname";
          }
          if (!string.IsNullOrEmpty(searchPolicy.IdentityNo))
          {
              if (searchPolicy.IdentityType != null)
              {
                switch (searchPolicy.IdentityType)
                {
                  case IdentityTypeEnum.IdentityNo:
                    conditions += " AND M.TC_KIMLIK_NO = :IdentityNo";
                    break;
                  case IdentityTypeEnum.ForeignIdentityNo:
                    conditions += " AND M.TC_KIMLIK_NO = :IdentityNo";
                    break;
                  case IdentityTypeEnum.PassportNo:
                    conditions += " AND M.PASAPORT_NO = :IdentityNo";
                    break;
                  case IdentityTypeEnum.TaxNo:
                    conditions += " AND M.VERGI_NO = :IdentityNo";
                    break;
                }
              }
              else
              {
                conditions += " AND M.TC_KIMLIK_NO = :IdentityNo";
              }
          }
          if (!string.IsNullOrEmpty(searchPolicy.MustKod))
          {
            searchPolicy.MustKod = searchPolicy.MustKod.ToUpper(culture);
            conditions += " AND M.MUST_KOD = :MustKod";
          }
        }

        break;
    }
  }

  /* PRODUCT - PLAN FILTERS */
  if (selectedCategories.Contains("productContent"))
  {
    if (string.IsNullOrEmpty(searchPolicy.ProductId)
      && searchPolicy.PlanId == 0)
    {
      throw new UserFriendlyException("\"Ürün / Plan\" arama kriteri eksik.");
    }

    if (!string.IsNullOrEmpty(searchPolicy.ProductId))
    {
      conditions += " AND P.PRODUCT_ID = :ProductId";
    }
    if (searchPolicy.PlanId > 0)
    {
      if (!gotPolicyInsuredJoin)
      {
        innerSql += policyInsuredJoin;
        gotPolicyInsuredJoin = true;
      }
      if (gotPolicyInsuredJoin && !gotPlanJoin)
      {
        innerSql += planJoin;
        gotPlanJoin = true;
      }
      if (gotPolicyInsuredJoin && gotPlanJoin)
      {
        conditions += " AND PL.ID = :PlanId";
      }
    }
  }

  /* BRANCH - SOURCE - AGENT FILTERS */
  if (selectedCategories.Contains("agencyContent"))
  {
    if (searchPolicy.BranchCode == 0
      && searchPolicy.SourceCode == 0
      && string.IsNullOrEmpty(searchPolicy.AgentCode))
    {
      throw new UserFriendlyException("\"Şube / Kaynak / Acente\" arama kriteri eksik.");
    }

    if (searchPolicy.BranchCode > 0)
    {
      conditions += " AND P.BRANCH_CODE = :BranchCode";
    }
    if (searchPolicy.SourceCode > 0)
    {
      conditions += " AND P.SOURCE_CODE = :SourceCode";
    }
    if (!string.IsNullOrEmpty(searchPolicy.AgentCode))
    {
      conditions += " AND P.AGENT_CODE = :AgentCode";
    }
  }

  innerSql += conditions;
  innerSql += " GROUP BY P.ID";

  outerSql += innerSql + ")";
  outerSql += " WHERE rn = 1";
  outerSql += " ORDER BY ID DESC";
  outerSql += " FETCH FIRST 100 ROWS ONLY";

  var sqlParams = new
  {
    searchPolicy.PolicyNo,
    searchPolicy.ProposalStatus,
    searchPolicy.StartDate,
    searchPolicy.EndDate,
    searchPolicy.FromIssueDate,
    searchPolicy.ToIssueDate,
    searchPolicy.Name,
    searchPolicy.Surname,
    searchPolicy.IdentityType,
    searchPolicy.IdentityNo,
    searchPolicy.RegistryNumber,
    searchPolicy.MustKod,
    searchPolicy.OldMustKod,
    searchPolicy.ProductId,
    searchPolicy.PlanId,
    searchPolicy.BranchCode,
    searchPolicy.SourceCode,
    searchPolicy.AgentCode
  };

  /* execute query using dapper */
  /* https://github.com/StackExchange/Dapper 
   * https://github.com/StackExchange/dapper-dot-net*/
  Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;
  List<long> policyList;

  using (var connection = HealthConsts.DbConn.ConnectionFactory())
  {
    connection.Open();
    policyList = connection.Query<long>(outerSql, sqlParams).ToList();
  }

  return policyList;
}