动态获取项加入到SQL中去统计

public static List<CustomerAppraisalInfo> ListCustomerAppraisal(int pageIndex, int pageSize, string fromDate, string toDate, string branchId, string departmentId, string referType)
{
DateTime dCurrentTime = DateTime.Now;
int rowCount, startRow, count = 0;

CustomerSurveyDAL.SurveyTemplateInfo surveyTemplate = CustomerSurveyDAL.SurveyTemplate.GetSurveyTemplateByType(referType);
if (surveyTemplate.surveyTemplateId==0)
{
surveyTemplate = CustomerSurveyDAL.SurveyTemplate.GetSurveyTemplateByType("1");
}


List<CustomerSurveyDAL.SurveyEntryListNameInfo> surveyEntrylist = CustomerSurveyDAL.SurveyEntry.ListSurveyEntryName("", "", "80", surveyTemplate.surveyTemplateId.ToString(), "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "");

 

List<CustomerAppraisalInfo> results = new List<CustomerAppraisalInfo>();
List<CustomerAppraisalInfo> tempResults = new List<CustomerAppraisalInfo>();

if (string.IsNullOrEmpty(fromDate) == false)
fromDate = DateTime.Parse(fromDate).ToString("u").Substring(0, 10);
if (string.IsNullOrEmpty(toDate) == false)
toDate = DateTime.Parse(toDate).ToString("u").Substring(0, 10) + " 23:59:59";

StringBuilder strSQL = new StringBuilder();
pageIndex--;
if (pageIndex < 0)
pageIndex = 0;
if (pageSize <= 0)
pageSize = 10;
//因为可能2行合并1行
startRow = pageIndex * pageSize;
rowCount = startRow + pageSize;
strSQL.Append("SELECT TOP ").Append(rowCount.ToString());

strSQL.Append(" b.customerId,SUM(b.actualScore) / COUNT(*)");
foreach (var item in surveyEntrylist)
{
strSQL.Append(",sum(case when c.surveyEntryName = '" + item.surveyEntryName + "' then c.actualScore else 0 end) / count(distinct(sr.requestId))");
}//动态添加获取到的项
strSQL.Append(" FROM FQ_ServiceRequest sr WITH(NOLOCK) LEFT JOIN KH_Survey b WITH(NOLOCK) ON sr.requestId = b.sourceId left join KH_SurveyEntry c WITH(NOLOCK)on b.surveyId = c.objectId ");
strSQL.Append(" WHERE b.customerId > 0 and b.reserveId3 = 0");

if (String.IsNullOrEmpty(fromDate) != true)
strSQL.Append(" AND sr.creationTime >= @fromDate ");

if (String.IsNullOrEmpty(toDate) != true)
strSQL.Append(" AND sr.creationTime <= @toDate ");

if (String.IsNullOrEmpty(branchId) != true)
strSQL.Append(" AND sr.requestBranchId = @branchId ");

if (String.IsNullOrEmpty(departmentId) != true)
strSQL.Append(" AND sr.supportDepartmentId = @departmentId ");

if (String.IsNullOrEmpty(referType) != true)
strSQL.Append(" AND sr.referType = @referType ");

strSQL.Append(" GROUP BY b.customerId ORDER BY b.customerId ");

string cmdText = strSQL.ToString();

ParmInfo[] parms = new ParmInfo[] {
new ParmInfo("@branchId", DataType.Int, 0, String.IsNullOrEmpty(branchId)? 0 : int.Parse(branchId)),
new ParmInfo("@departmentId", DataType.Int, 0, String.IsNullOrEmpty(departmentId)? 0 : int.Parse(departmentId.Trim())),
new ParmInfo("@referType", DataType.VarChar, 20, String.IsNullOrEmpty(referType)? "" : referType.Trim()),
new ParmInfo("@fromDate", DataType.VarChar, 20, String.IsNullOrEmpty(fromDate)? "": fromDate.Trim()),
new ParmInfo("@toDate", DataType.VarChar, 20, String.IsNullOrEmpty(toDate)? "": toDate.Trim())
};

DbConnection conn = ConnManager.OpenConnection(dbl, connectionString);

try
{
DbDataReader rdr = dbl.ExecuteReader(conn, cmdText, parms);

while (rdr.Read())
{
if (count >= startRow)
{

CustomerAppraisalInfo r = new CustomerAppraisalInfo();
r.fromDate = fromDate;
r.toDate = toDate;
r.customerId = rdr.IsDBNull(0) ? 0 : rdr.GetInt32(0);
r.surveyAverageScore = rdr.IsDBNull(1) ? 0 : rdr.GetInt32(1);
if (surveyEntrylist.ToArray().Length >= 1)
{
r.obj1 = rdr.IsDBNull(2) ? 0 : rdr.GetInt32(2);
r.objName1 = surveyEntrylist[0].surveyEntryName;
}
if (surveyEntrylist.ToArray().Length >= 2)
{
r.obj2 = rdr.IsDBNull(3) ? 0 : rdr.GetInt32(3);
r.objName2 = surveyEntrylist[1].surveyEntryName;
}
if (surveyEntrylist.ToArray().Length >= 3)
{
r.obj3 = rdr.IsDBNull(4) ? 0 : rdr.GetInt32(4);
r.objName3 = surveyEntrylist[2].surveyEntryName;
}
if (surveyEntrylist.ToArray().Length >= 4)
{
r.obj4 = rdr.IsDBNull(5) ? 0 : rdr.GetInt32(5);
r.objName4 = surveyEntrylist[3].surveyEntryName;
}
if (surveyEntrylist.ToArray().Length >= 5)
{
r.obj5 = rdr.IsDBNull(6) ? 0 : rdr.GetInt32(6);
r.objName5 = surveyEntrylist[4].surveyEntryName;
}
if (surveyEntrylist.ToArray().Length >= 6)
{
r.obj6 = rdr.IsDBNull(7) ? 0 : rdr.GetInt32(7);
r.objName6 = surveyEntrylist[5].surveyEntryName;
}
if (surveyEntrylist.ToArray().Length >= 7)
{
r.obj7 = rdr.IsDBNull(8) ? 0 : rdr.GetInt32(8);
r.objName7 = surveyEntrylist[6].surveyEntryName;
}
if (surveyEntrylist.ToArray().Length >= 8)
{
r.obj8 = rdr.IsDBNull(9) ? 0 : rdr.GetInt32(9);
r.objName8 = surveyEntrylist[7].surveyEntryName;
}
if (surveyEntrylist.ToArray().Length >= 9)
{
r.obj9 = rdr.IsDBNull(10) ? 0 : rdr.GetInt32(10);
r.objName9 = surveyEntrylist[8].surveyEntryName;
}
if (surveyEntrylist.ToArray().Length >= 10)
{
r.obj10 = rdr.IsDBNull(11) ? 0 : rdr.GetInt32(11);
r.objName10 = surveyEntrylist[9].surveyEntryName;
}

tempResults.Add(r);
}
count++;
}

rdr.Close();

results = tempResults;

foreach (CustomerAppraisalInfo r in tempResults)
{
if (r.customerId > 0)
r.customerName = Customers.GetCustomers(r.customerId).customerName;
}
ConnManager.CloseConnection();

return results;
}
catch (Exception e)
{
ConnManager.CloseConnection();
throw new ApplicationException(e.Message);
}
}