public DataSet GetCustomerAllocListByQC(CustomerAllocQueryDataContract aQC)
{
StringBuilder sql = new StringBuilder();
sql.Append(@"SELECT * FROM
(SELECT
'01' AS CUSTOMERALLOCTYPE,'新车客户' AS CUSTOMERALLOCTYPENAME,
CASE WHEN T1.CUSTOMERGRADE = 'A' THEN 'A'
WHEN T1.CUSTOMERGRADE = 'B' THEN 'B'
WHEN T1.CUSTOMERGRADE = 'C' THEN 'C'
WHEN T1.CUSTOMERGRADE = 'D' THEN 'D'
WHEN T1.CUSTOMERGRADE IS NULL THEN 'D' END AS CUSTOMERGRADE,
T4.CUSTOMERCODE,T4.CUSTOMERNAME,
T4.MOBILEPHONE,T4.MOBILEPHONE1,T4.TELEPHONE,
CASE WHEN T4.SEX = 'F' THEN '?' WHEN T4.SEX = 'M' THEN '?' END AS SEX,
T4.PROFESSIONCODE,T5.COMMONNAME AS PROFESSIONNAME,
T2.CARLICENSENO,T2.FRAMENO,T2.CARMODELPKID,
T8.COMMONNAME AS INSURERNAME, T6.INSURERCODE , T6.INSURANCEBUYDATE, T6.INSURANCEMATURITYDATE
,T3.ORIGINALSALEID,T7.FULLNAME AS ORIGINALSALENAME
,T3.ISALLOC
FROM RM_CARINFO T1
LEFT JOIN VM_CARINFO T2 ON T1.FRAMENO = T2.FRAMENO
LEFT JOIN RT_CARALLOCINFO T3 ON T1.FRAMENO = T3.FRAMENO
LEFT JOIN UM_CUSTOMER T4 ON T4.CUSTOMERCODE = T2.CUSTOMERCODE
LEFT JOIN CM_COMMONCODE T5 ON T4.PROFESSIONCODE = T5.COMMONCODE AND T5.CODETYPE = 'CC01'
LEFT JOIN (SELECT MAX(BUYDATE) AS BUYDATE ,FRAMENO FROM RT_INSURANCEBUY GROUP BY FRAMENO) T61 ON T1.FRAMENO =T61.FRAMENO
LEFT JOIN RT_INSURANCEBUY T6 ON T61.FRAMENO=T6.FRAMENO AND T61.BUYDATE=T6.BUYDATE
LEFT JOIN CM_STAFF T7 ON T3.ORIGINALSALEID = T7.STAFFPKID
LEFT JOIN CM_COMMONCODE T8 ON T6.INSURERCODE = T8.COMMONCODE AND T5.CODETYPE = 'CC12'
WHERE YEAR(T1.PURCHASEDATE) = YEAR(CURRENT TIMESTAMP) - 1 AND T6.INSURERCODE IS NOT NULL AND T3.ISALLOC = 'N' AND VALUE(T3.NOTCALL,'N') = 'N'
AND VALUE(T1.DELETED,'0') = '0'
UNION ALL
SELECT
'02' AS CUSTOMERALLOCTYPE,'续保客户' AS CUSTOMERALLOCTYPENAME,
CASE WHEN T1.CUSTOMERGRADE = 'A' THEN 'A'
WHEN T1.CUSTOMERGRADE = 'B' THEN 'B'
WHEN T1.CUSTOMERGRADE = 'C' THEN 'C'
WHEN T1.CUSTOMERGRADE = 'D' THEN 'D'
WHEN T1.CUSTOMERGRADE IS NULL THEN 'D' END AS CUSTOMERGRADE,
T4.CUSTOMERCODE,T4.CUSTOMERNAME,
T4.MOBILEPHONE,T4.MOBILEPHONE1,T4.TELEPHONE,
CASE WHEN T4.SEX = 'F' THEN '?' WHEN T4.SEX = 'M' THEN '?' END AS SEX,
T4.PROFESSIONCODE,T5.COMMONNAME AS PROFESSIONNAME,
T2.CARLICENSENO,T2.FRAMENO,T2.CARMODELPKID,
T8.COMMONNAME AS INSURERNAME, T6.INSURERCODE , T6.INSURANCEBUYDATE, T6.INSURANCEMATURITYDATE
,T3.ORIGINALSALEID,T7.FULLNAME AS ORIGINALSALENAME
,T3.ISALLOC
FROM RM_CARINFO T1
LEFT JOIN VM_CARINFO T2 ON T1.FRAMENO = T2.FRAMENO
LEFT JOIN RT_CARALLOCINFO T3 ON T1.FRAMENO = T3.FRAMENO
LEFT JOIN UM_CUSTOMER T4 ON T4.CUSTOMERCODE = T2.CUSTOMERCODE
LEFT JOIN CM_COMMONCODE T5 ON T4.PROFESSIONCODE = T5.COMMONCODE AND T5.CODETYPE = 'CC01'
LEFT JOIN (SELECT MAX(BUYDATE) AS BUYDATE ,FRAMENO FROM RT_INSURANCEBUY GROUP BY FRAMENO) T61 ON T1.FRAMENO =T61.FRAMENO
LEFT JOIN RT_INSURANCEBUY T6 ON T61.FRAMENO=T6.FRAMENO AND T61.BUYDATE=T6.BUYDATE
LEFT JOIN CM_STAFF T7 ON T3.ORIGINALSALEID = T7.STAFFPKID
LEFT JOIN CM_COMMONCODE T8 ON T6.INSURERCODE = T8.COMMONCODE AND T5.CODETYPE = 'CC12'
WHERE YEAR(T1.PURCHASEDATE) <> YEAR(CURRENT TIMESTAMP) - 1 AND T6.INSURERCODE IS NOT NULL AND T6.SALESTYPE = '1' AND T3.ISALLOC = 'N' AND VALUE(T3.NOTCALL,'N') = 'N'
AND VALUE(T1.DELETED,'0') = '0'
UNION ALL
SELECT
'03' AS CUSTOMERALLOCTYPE, '应到期客户' AS CUSTOMERALLOCTYPENAME,
CASE WHEN T1.CUSTOMERGRADE = 'A' THEN 'A'
WHEN T1.CUSTOMERGRADE = 'B' THEN 'B'
WHEN T1.CUSTOMERGRADE = 'C' THEN 'C'
WHEN T1.CUSTOMERGRADE = 'D' THEN 'D'
WHEN T1.CUSTOMERGRADE IS NULL THEN 'D' END AS CUSTOMERGRADE,
T4.CUSTOMERCODE,T4.CUSTOMERNAME,
T4.MOBILEPHONE,T4.MOBILEPHONE1,T4.TELEPHONE,
CASE WHEN T4.SEX = 'F' THEN '?' WHEN T4.SEX = 'M' THEN '?' END AS SEX,
T4.PROFESSIONCODE,T5.COMMONNAME AS PROFESSIONNAME,
T2.CARLICENSENO,T2.FRAMENO,T2.CARMODELPKID,
T8.COMMONNAME AS INSURERNAME, T6.INSURERCODE , T6.INSURANCEBUYDATE, T6.INSURANCEMATURITYDATE
,T3.ORIGINALSALEID,T7.FULLNAME AS ORIGINALSALENAME
,T3.ISALLOC
FROM RM_CARINFO T1
LEFT JOIN VM_CARINFO T2 ON T1.FRAMENO = T2.FRAMENO
LEFT JOIN RT_CARALLOCINFO T3 ON T1.FRAMENO = T3.FRAMENO
LEFT JOIN UM_CUSTOMER T4 ON T4.CUSTOMERCODE = T2.CUSTOMERCODE
LEFT JOIN CM_COMMONCODE T5 ON T4.PROFESSIONCODE = T5.COMMONCODE AND T5.CODETYPE = 'CC01'
LEFT JOIN (SELECT MAX(BUYDATE) AS BUYDATE ,FRAMENO FROM RT_INSURANCEBUY GROUP BY FRAMENO) T61 ON T1.FRAMENO =T61.FRAMENO
LEFT JOIN RT_INSURANCEBUY T6 ON T61.FRAMENO=T6.FRAMENO AND T61.BUYDATE=T6.BUYDATE
LEFT JOIN CM_STAFF T7 ON T3.ORIGINALSALEID = T7.STAFFPKID
LEFT JOIN CM_COMMONCODE T8 ON T6.INSURERCODE = T8.COMMONCODE AND T5.CODETYPE = 'CC12'
WHERE T6.INSURERCODE IS NOT NULL AND T6.SALESTYPE = '0' AND T3.ISALLOC = 'N' AND VALUE(T3.NOTCALL,'N') = 'N'
AND VALUE(T1.DELETED,'0') = '0'
) T
WHERE 1=1 ");
if(aQC.CustomerName != string.Empty)
{
sql.Append(" and T.CustomerName like '%" +aQC.CustomerName+ "%'");
}
if(aQC.CallNo != string.Empty)
{
sql.Append(" and (T.MobilePhone like '% " +aQC.CallNo+ "%' or T.MobilePhone1 like '%" +aQC.CallNo+ "%' or T.Telephone like '%" +aQC.CallNo+ "%')");
}
if(aQC.ProfessionCode != string.Empty)
{
sql.Append(" and T.ProfessionCode = "+ aQC.ProfessionCode);
}
if(aQC.CarLicenseNo != string.Empty)
{
sql.Append(" and T.CarLicenseNo like '%" +aQC.CarLicenseNo+ "%'");
}
if(aQC.FrameNo != string.Empty)
{
sql.Append(" and T.FrameNo like '%" +aQC.FrameNo+ "%' ");
}
if(aQC.CustomerAllocType != string.Empty)
{
sql.Append(" and T.CustomerAllocType = " + aQC.CustomerAllocType);
}
if(aQC.InsureCompanyCode != string.Empty)
{
sql.Append(" and T.InSurerCode = " + aQC.InsureCompanyCode);
}
if(aQC.CustomerGrade != string.Empty)
{
sql.Append(" and T.CustomerGrade = " + aQC.CustomerGrade);
}
if(aQC.InsuranceMaturityDateFrom != string.Empty)
{
sql.Append(" and T.InsuranceMaturityDate >= " + aQC.InsuranceMaturityDateFrom);
}
if(aQC.InsuranceMaturityDateEnd != string.Empty)
{
sql.Append(" and T.InsuranceMaturityDate <= " + aQC.InsuranceMaturityDateEnd);
}
DataSet ds = new DataSet();
FillDataSet(sql.ToString(),ds,new string[]{"Tmp_CustomerAllocInfo"});
ds.AcceptChanges();
return ds;
}
