mssp 发表于 2013-2-7 09:50:28

CreateSQLQuery

public float[] GetSumAssignedResourceByFYandDep(long fiscalYearId, long departmentId)      {            /*            ICriteria criteria = NHibernateSessionManager.Instance.GetSession().CreateCriteria(typeof(ResourceAssign), "ra")                .Add(Expression.Eq("ra.TargetFiscalYear.Id", fiscalYearId))            .Add(Expression.Eq("ra.TargetMemberResource.Team.Department.Id", departmentId));            IList o = criteria.SetProjection(Projections.Sum("Month10")).List();            */            StringBuilder sbSQL = new StringBuilder("SELECT ");            sbSQL.Append("SUM(MANMONTH_10) AS s1, SUM(MANMONTH_11) AS s2, ");            sbSQL.Append("SUM(MANMONTH_12) AS s3, SUM(MANMONTH_01) AS s4, ");            sbSQL.Append("SUM(MANMONTH_02) AS s5, SUM(MANMONTH_03) AS s6, ");            sbSQL.Append("SUM(MANMONTH_04) AS s7, SUM(MANMONTH_05) AS s8, ");            sbSQL.Append("SUM(MANMONTH_06) AS s9, SUM(MANMONTH_07) AS s10, ");            sbSQL.Append("SUM(MANMONTH_08) AS s11, SUM(MANMONTH_09) AS s12");            sbSQL.Append(" FROM RESOURCE_ASSIGN INNER JOIN MEMBERRESOURCE ON RESOURCE_ASSIGN.FK_MEMBERSOURCE_ID = MEMBERRESOURCE.MEMBERRESOURCE_ID");            sbSQL.Append(" INNER JOIN TEAM ON MEMBERRESOURCE.FK_TEAM_ID = TEAM.TEAM_ID");            sbSQL.Append(" WHERE (RESOURCE_ASSIGN.FK_FISCAL_YEAR_ID =:fyid) AND (TEAM.FK_DIVISION_ID =:depid)");            IQuery sqlQuery = NHibernateSessionManager.Instance.GetSession().CreateSQLQuery(sbSQL.ToString())             .AddScalar("s1", NHibernateUtil.Single)             .AddScalar("s2", NHibernateUtil.Single)             .AddScalar("s3", NHibernateUtil.Single)             .AddScalar("s4", NHibernateUtil.Single)             .AddScalar("s5", NHibernateUtil.Single)             .AddScalar("s6", NHibernateUtil.Single)             .AddScalar("s7", NHibernateUtil.Single)             .AddScalar("s8", NHibernateUtil.Single)             .AddScalar("s9", NHibernateUtil.Single)             .AddScalar("s10", NHibernateUtil.Single)             .AddScalar("s11", NHibernateUtil.Single)             .AddScalar("s12", NHibernateUtil.Single)             .SetInt64("fyid", fiscalYearId)             .SetInt64("depid", departmentId);            object[] os = (object[])(sqlQuery.UniqueResult() ?? new float);            float[] f = new float { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };            for (int j = 0; j
页: [1]
查看完整版本: CreateSQLQuery