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;
}