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