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