public List <TargetEntryDetailsCustomEntity> GetReportSalesListSavedTarget(string strSubQuery)
{
TargetEntryDetailsCustomEntity tedc = new TargetEntryDetailsCustomEntity();
_bmsUnit = new BMS_UnitOfWork();
LILI_BMSEntities1 bEntities = new DAL.BMS.LILI_BMSEntities1();
// var query = @"SELECT ProductCode, ISNULL(January,0) AS January, ISNULL(February,0) AS February, ISNULL(March,0) AS March, ISNULL(April,0) AS April,
// ISNULL(May,0) AS May, ISNULL(June,0) AS June, ISNULL(July,0) AS July, ISNULL(August,0) AS August,
// ISNULL(September,0) AS September, ISNULL(October,0) AS October, ISNULL(November,0) AS November, ISNULL(December,0) AS December
// FROM
// (SELECT PTM.Business, PTM.Level1, ProductCode, Product, Pack, left(Period,4) as [Year],
// DateName( month , DateAdd( month , Convert(INT, right(Period,2)) , 0 ) - 1 ) as [Month],
// Target
// FROM ProductTargetMaster PTM INNER JOIN ProductTarget PT ON PTM.Id = PT.MasterId
// WHERE " + strSubQuery + " ) as q" +
// " PIVOT( SUM(Target) FOR [month] in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvot";
var query = @"SELECT Category, ProductCode, Product, ProductCost, ISNULL(January,0) AS January, ISNULL(February,0) AS February, ISNULL(March,0) AS March, ISNULL(April,0) AS April,
ISNULL(May,0) AS May, ISNULL(June,0) AS June, ISNULL(July,0) AS July, ISNULL(August,0) AS August,
ISNULL(September,0) AS September, ISNULL(October,0) AS October, ISNULL(November,0) AS November, ISNULL(December,0) AS December
FROM
(SELECT PTM.Business, PTM.Level1, PT.ProductCode,
(SELECT TOP 1 Cost FROM ProductCost PC WHERE PC.ProductCode = PT.ProductCode) AS ProductCost,
(SELECT TOP 1 Category FROM GetSales GS WHERE GS.ProductCode=PT.ProductCode) AS Category,
Product, Pack, left(PT.Period,4) as [Year],
DateName( month , DateAdd( month , Convert(INT, right(PT.Period,2)) , 0 ) - 1 ) as [Month],
Target
FROM ProductTargetMaster PTM INNER JOIN ProductTarget PT ON PTM.Id = PT.MasterId
WHERE " + strSubQuery + " ) as q" +
" PIVOT( SUM(Target) FOR [month] in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvot";
BMS_GenericRepository <TargetEntryDetailsCustomEntity> GetSalesDataList = new BMS_GenericRepository <TargetEntryDetailsCustomEntity>(bEntities);
var data = GetSalesDataList.GetWithRawSql(query);// BMSUnit.GetSalesDataList.GetWithRawSql(query);
return(data.ToList());
}
public BMSCommonSevice(BMS_UnitOfWork uow)
{
_bmsUnit = uow;
}
public ResourceInfoService(BMS_UnitOfWork uow) : base(uow)
{
}
public ResourceCategoryService(BMS_UnitOfWork uow) : base(uow)
{
}
