Beispiel #1
0
        /// <summary>
        /// 获取可比公司数量o
        /// </summary>
        /// <param name="FinYear"></param>
        /// <param name="FinMonth"></param>
        /// <param name="SystemID"></param>
        /// <returns></returns>
        public IList <ContrastAllCompanyVM> GetContrastCompanyTotal(int FinYear, int FinMonth, Guid ID, Guid TargetID, bool IfNow)
        {
            string sql = "";

            if (IfNow == true)
            {
                sql = @"SELECT  CompanyID  ,
        CompanyName
FROM    dbo.C_Company
        RIGHT JOIN dbo.B_MonthlyReportDetail ON B_MonthlyReportDetail.CompanyID = C_Company.ID
WHERE   B_MonthlyReportDetail.IsDeleted = 0
        AND C_Company.IsDeleted = 0
        AND MonthlyReportID = @ID
		AND TargetID=@TargetID
        AND C_Company.ID NOT IN ( SELECT    CompanyID
                                  FROM      dbo.C_ExceptionTarget
                                  WHERE     C_ExceptionTarget.IsDeleted = 0
                                            AND (ExceptionType = 5 OR  ExceptionType = 2) AND TargetID=@TargetID )
	    AND CompanyName NOT LIKE'%总部%'"    ;
            }
            else
            {
                sql = @" SELECT  CompanyID ,
        CompanyName
FROM    dbo.C_Company
        RIGHT JOIN dbo.A_MonthlyReportDetail ON A_MonthlyReportDetail.CompanyID = C_Company.ID
WHERE   A_MonthlyReportDetail.IsDeleted = 0
        AND C_Company.IsDeleted = 0
        AND FinMonth = @FinMonth
        AND FinYear = @FinYear
        AND A_MonthlyReportDetail.SystemID = @ID
		AND TargetID=@TargetID
        AND C_Company.ID NOT IN ( SELECT    CompanyID
                                  FROM      dbo.C_ExceptionTarget
                                  WHERE     C_ExceptionTarget.IsDeleted = 0
                                            AND (ExceptionType = 5 OR  ExceptionType = 2) AND TargetID=@TargetID)
        AND CompanyName NOT LIKE'%总部%'";
            }


            SqlParameter pSystemID           = CreateSqlParameter("@ID", System.Data.DbType.Guid, ID);
            SqlParameter pFinYear            = CreateSqlParameter("@FinYear", System.Data.DbType.Int32, FinYear);
            SqlParameter pFinMonth           = CreateSqlParameter("@FinMonth", System.Data.DbType.Int32, FinMonth);
            SqlParameter pTarget             = CreateSqlParameter("@TargetID", System.Data.DbType.Guid, TargetID);
            var          ds                  = DbHelper.RunSqlReturnDS(sql, ConnectionName, pSystemID, pFinYear, pFinMonth, pTarget);
            List <ContrastAllCompanyVM> data = new List <ContrastAllCompanyVM>();

            ds.Tables[0].Rows.Cast <System.Data.DataRow>().ForEach(row =>
            {
                ContrastAllCompanyVM item = new ContrastAllCompanyVM();
                ORMapping.DataRowToObject(row, item);
                data.Add(item);
            });
            return(data);
        }
Beispiel #2
0
        /// <summary>
        /// 获取不可比公司数量o
        /// </summary>
        /// <param name="FinYear"></param>
        /// <param name="FinMonth"></param>
        /// <param name="ID">如果IFB==true 则SystemID 否则为MonthlyReportID</param>
        /// <returns></returns>
        public IList <ContrastAllCompanyVM> GetNoContrastCompanyTotal(int FinYear, int FinMonth, Guid ID, Guid TargetID, bool IFB)
        {
            string sql = "";

            if (IFB == true)
            {
                sql = @"SELECT   CompanyID  ,
        CompanyName,NAccumulativeActualAmmount,OpeningTime
        FROM    ( SELECT    B_MonthlyReportDetail.CompanyID ,
                    B_MonthlyReportDetail.TargetID ,
                    NAccumulativeActualAmmount
          FROM      dbo.C_ExceptionTarget
                    RIGHT JOIN dbo.B_MonthlyReportDetail ON B_MonthlyReportDetail.CompanyID = C_ExceptionTarget.CompanyID
          WHERE     ExceptionType = 5
                    AND B_MonthlyReportDetail.IsDeleted = 0
                    AND C_ExceptionTarget.IsDeleted = 0
                    AND MonthlyReportID = @ID
					AND B_MonthlyReportDetail.TargetID=@TargetID
					AND C_ExceptionTarget.TargetID=@TargetID
        ) AA
        LEFT JOIN C_Company ON AA.CompanyID = C_Company.ID
		WHERE  CompanyName NOT LIKE '%总部%'
        ORDER BY OpeningTime";
            }
            else
            {
                sql = @"SELECT  CompanyID ,
        CompanyName ,
        NAccumulativeActualAmmount ,
        OpeningTime
FROM    ( SELECT    *
          FROM      dbo.A_MonthlyReportDetail
          WHERE     SystemID = @ID
                    AND TargetID = @TargetID
                    AND FinYear = @FinYear
                    AND FinMonth = @FinMonth
                    AND IsDeleted = 0
        ) aa
        RIGHT JOIN ( SELECT CompanyName ,
                            C_Company.ID ,
                            OpeningTime
                     FROM   dbo.C_ExceptionTarget
                            LEFT JOIN dbo.C_Company ON C_Company.ID = C_ExceptionTarget.CompanyID
                     WHERE  TargetID = @TargetID
                            AND ExceptionType = 5
                            AND C_Company.IsDeleted = 0
                            AND C_ExceptionTarget.IsDeleted = 0
                   ) bb ON bb.ID = aa.CompanyID
WHERE   NAccumulativeActualAmmount != 0
        AND CompanyName NOT LIKE '%总部%'
ORDER BY OpeningTime
";
            }
            SqlParameter pSystemID           = CreateSqlParameter("@ID", System.Data.DbType.Guid, ID);
            SqlParameter pFinYear            = CreateSqlParameter("@FinYear", System.Data.DbType.Int32, FinYear);
            SqlParameter pFinMonth           = CreateSqlParameter("@FinMonth", System.Data.DbType.Int32, FinMonth);
            SqlParameter pTargetID           = CreateSqlParameter("@TargetID", System.Data.DbType.Guid, TargetID);
            var          ds                  = DbHelper.RunSqlReturnDS(sql, ConnectionName, pSystemID, pFinYear, pFinMonth, pTargetID);
            List <ContrastAllCompanyVM> data = new List <ContrastAllCompanyVM>();

            ds.Tables[0].Rows.Cast <System.Data.DataRow>().ForEach(row =>
            {
                ContrastAllCompanyVM item = new ContrastAllCompanyVM();
                ORMapping.DataRowToObject(row, item);
                data.Add(item);
            });
            return(data);
        }