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 void GenerateReport()
{
rvBudgetEntry.Reset();
rvBudgetEntry.ProcessingMode = ProcessingMode.Local;
rvBudgetEntry.LocalReport.ReportPath = Server.MapPath("~/Reports/BMS/rdlc/BudgetEntryCOGSGPReport.rdlc");
//if (txtFromDate.Text.Trim() != "")
//{
// Global.breakdownDate = Convert.ToDateTime(txtFromDate.Text.Trim());
//}
#region Processing Report Data
LILI_BMSEntities1 bEntities = new DAL.BMS.LILI_BMSEntities1();
#endregion
string strSubQuery = "";
string strSubQuerySPLY = "";
if (ddlLevel1.SelectedValue != "0" && ddlLevel1.SelectedValue != "")
{
strSubQuery = " PTMJ.Business = '" + ddlBusiness.SelectedValue + "' AND PTMJ.BudgetYear = '" + ddlYear.SelectedValue + "' AND PTJ.ProductCode = PT.ProductCode AND PTMJ.Level1 = '" + ddlLevel1.SelectedValue + "' ";
strSubQuerySPLY = " GS.Business = '" + ddlBusiness.SelectedValue + "' AND GS.ProductCode = PT.ProductCode AND GS.Level1 = '" + ddlLevel1.SelectedValue + "'";
}
else if (ddlLevel2.SelectedValue != "0" && ddlLevel2.SelectedValue != "")
{
strSubQuery = " PTMJ.Business = '" + ddlBusiness.SelectedValue + "' AND PTMJ.BudgetYear = '" + ddlYear.SelectedValue + "' AND PTJ.ProductCode = PT.ProductCode AND PTMJ.Level1 IN (SELECT Level1 FROM Level1 WHERE Level2 = '" + ddlLevel2.SelectedValue + "') ";
strSubQuerySPLY = " GS.Business = '" + ddlBusiness.SelectedValue + "' AND GS.ProductCode = PT.ProductCode AND GS.Level1 IN (SELECT Level1 FROM Level1 WHERE Level2 = '" + ddlLevel2.SelectedValue + "')";
}
else if (ddlLevel3.SelectedValue != "0" && ddlLevel3.SelectedValue != "")
{
strSubQuery = " PTMJ.Business = '" + ddlBusiness.SelectedValue + "' AND PTMJ.BudgetYear = '" + ddlYear.SelectedValue + "' AND PTJ.ProductCode = PT.ProductCode AND PTMJ.Level1 IN (SELECT Level1 FROM Level1 WHERE Level2 IN (SELECT Level2 FROM Level2 WHERE Level3 = '" + ddlLevel3.SelectedValue + "') ) ";
strSubQuerySPLY = " GS.Business = '" + ddlBusiness.SelectedValue + "' AND GS.ProductCode = PT.ProductCode AND GS.Level1 IN (SELECT Level1 FROM Level1 WHERE Level2 IN (SELECT Level2 FROM Level2 WHERE Level3 = '" + ddlLevel3.SelectedValue + "') ) ";
}
else
{
strSubQuery = " PTMJ.Business = '" + ddlBusiness.SelectedValue + "' AND PTMJ.BudgetYear = '" + ddlYear.SelectedValue + "' AND PTJ.ProductCode = PT.ProductCode ";
}
string strQuery = "SELECT DISTINCT ProductCode, Product, " +
"(SELECT TOP 1 Cost FROM ProductCost PC WHERE PC.ProductCode = PT.ProductCode AND PC.Business = '" + ddlBusiness.SelectedValue + "') AS UnitCost, " +
"(SELECT TOP 1 Category FROM GetSales GS WHERE GS.ProductCode=PT.ProductCode) AS Category, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='01' AND " + strSubQuery + ") AS JanTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='01' AND " + strSubQuery + ") AS JanTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='01' AND " + strSubQuerySPLY + ") AS JanSPLY, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='02' AND " + strSubQuery + ") AS FebTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='02' AND " + strSubQuery + ") AS FebTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='02' AND " + strSubQuerySPLY + ") AS FebSPLY, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='03' AND " + strSubQuery + ") AS MarTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='03' AND " + strSubQuery + ") AS MarTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='03' AND " + strSubQuerySPLY + ") AS MarSPLY, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='04' AND " + strSubQuery + ") AS AprTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='04' AND " + strSubQuery + ") AS AprTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='04' AND " + strSubQuerySPLY + ") AS AprSPLY, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='05' AND " + strSubQuery + ") AS MayTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='05' AND " + strSubQuery + ") AS MayTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='05' AND " + strSubQuerySPLY + ") AS MaySPLY, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='06' AND " + strSubQuery + ") AS JunTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='06' AND " + strSubQuery + ") AS JunTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='06' AND " + strSubQuerySPLY + ") AS JunSPLY, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='07' AND " + strSubQuery + ") AS JulTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='07' AND " + strSubQuery + ") AS JulTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='07' AND " + strSubQuerySPLY + ") AS JulSPLY, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='08' AND " + strSubQuery + ") AS AugTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='08' AND " + strSubQuery + ") AS AugTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='08' AND " + strSubQuerySPLY + ") AS AugSPLY, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='09' AND " + strSubQuery + ") AS SepTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='09' AND " + strSubQuery + ") AS SepTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='09' AND " + strSubQuerySPLY + ") AS SepSPLY, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='10' AND " + strSubQuery + ") AS OctTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='10' AND " + strSubQuery + ") AS OctTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='10' AND " + strSubQuerySPLY + ") AS OctSPLY, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='11' AND " + strSubQuery + ") AS NovTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='11' AND " + strSubQuery + ") AS NovTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='11' AND " + strSubQuerySPLY + ") AS NovSPLY, " +
"(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='12' AND " + strSubQuery + ") AS DecTarget, " +
"(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='12' AND " + strSubQuery + ") AS DecTargetVal, " +
"(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='12' AND " + strSubQuerySPLY + ") AS DecSPLY " +
"FROM ProductTargetMaster PTM INNER JOIN ProductTarget PT ON PTM.Id = PT.MasterId " +
"WHERE PTM.Business = '" + ddlBusiness.SelectedValue + "' " +
"AND PTM.BudgetYear = '" + ddlYear.SelectedValue + "' "; // +
//"group BY ProductCode, Product";
//string strQuery = "SELECT DISTINCT ProductCode, Product, " +
// "(SELECT TOP 1 Category FROM GetSales GS WHERE GS.ProductCode=PT.ProductCode) AS Category, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='01' AND " + strSubQuery + ") AS JanTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='01' AND " + strSubQuery + ") AS JanTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='01' AND " + strSubQuery + ") AS JanGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='01' AND " + strSubQuerySPLY + ") AS JanSPLY, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='02' AND " + strSubQuery + ") AS FebTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='02' AND " + strSubQuery + ") AS FebTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='02' AND " + strSubQuery + ") AS FebGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='02' AND " + strSubQuerySPLY + ") AS FebSPLY, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='03' AND " + strSubQuery + ") AS MarTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='03' AND " + strSubQuery + ") AS MarTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='03' AND " + strSubQuery + ") AS MarGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='03' AND " + strSubQuerySPLY + ") AS MarSPLY, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='04' AND " + strSubQuery + ") AS AprTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='04' AND " + strSubQuery + ") AS AprTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='04' AND " + strSubQuery + ") AS AprGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='04' AND " + strSubQuerySPLY + ") AS AprSPLY, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='05' AND " + strSubQuery + ") AS MayTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='05' AND " + strSubQuery + ") AS MayTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='05' AND " + strSubQuery + ") AS MayGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='05' AND " + strSubQuerySPLY + ") AS MaySPLY, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='06' AND " + strSubQuery + ") AS JunTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='06' AND " + strSubQuery + ") AS JunTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='06' AND " + strSubQuery + ") AS JunGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='06' AND " + strSubQuerySPLY + ") AS JunSPLY, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='07' AND " + strSubQuery + ") AS JulTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='07' AND " + strSubQuery + ") AS JulTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='07' AND " + strSubQuery + ") AS JulGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='07' AND " + strSubQuerySPLY + ") AS JulSPLY, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='08' AND " + strSubQuery + ") AS AugTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='08' AND " + strSubQuery + ") AS AugTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='08' AND " + strSubQuery + ") AS AugGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='08' AND " + strSubQuerySPLY + ") AS AugSPLY, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='09' AND " + strSubQuery + ") AS SepTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='09' AND " + strSubQuery + ") AS SepTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='09' AND " + strSubQuery + ") AS SepGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='09' AND " + strSubQuerySPLY + ") AS SepSPLY, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='10' AND " + strSubQuery + ") AS OctTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='10' AND " + strSubQuery + ") AS OctTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='10' AND " + strSubQuery + ") AS OctGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='10' AND " + strSubQuerySPLY + ") AS OctSPLY, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='11' AND " + strSubQuery + ") AS NovTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='11' AND " + strSubQuery + ") AS NovTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='11' AND " + strSubQuery + ") AS NovGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='11' AND " + strSubQuerySPLY + ") AS NovSPLY, " +
// "(SELECT SUM(Target) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='12' AND " + strSubQuery + ") AS DecTarget, " +
// "(SELECT SUM(TargetVal) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='12' AND " + strSubQuery + ") AS DecTargetVal, " +
// "(SELECT SUM(Gr) FROM ProductTarget PTJ INNER JOIN ProductTargetMaster PTMJ ON PTMJ.Id = PTJ.MasterId WHERE Right(Period,2)='12' AND " + strSubQuery + ") AS DecGr, " +
// "(SELECT SUM(SalesQty) FROM GetSales GS WHERE Right(Period,2)='12' AND " + strSubQuerySPLY + ") AS DecSPLY " +
// "FROM ProductTargetMaster PTM INNER JOIN ProductTarget PT ON PTM.Id = PT.MasterId " +
// "WHERE PTM.Business = '" + ddlBusiness.SelectedValue + "' " +
// "AND PTM.BudgetYear = '" + ddlYear.SelectedValue + "' "; // +
// //"group BY ProductCode, Product";
if (ddlLevel1.SelectedValue != "0" && ddlLevel1.SelectedValue != "")
{
strQuery = strQuery + " AND PTM.Level1 = '" + ddlLevel1.SelectedValue + "' ";
}
else if (ddlLevel2.SelectedValue != "0" && ddlLevel2.SelectedValue != "")
{
strQuery = strQuery + " AND PTM.Level1 IN (SELECT Level1 FROM Level1 WHERE Level2 = '" + ddlLevel2.SelectedValue + "') ";
}
else if (ddlLevel3.SelectedValue != "0" && ddlLevel3.SelectedValue != "")
{
strQuery = strQuery + " AND PTM.Level1 IN (SELECT Level1 FROM Level1 WHERE Level2 IN (SELECT Level2 FROM Level2 WHERE Level3 = '" + ddlLevel3.SelectedValue + "') ) ";
}
strQuery = strQuery + " group BY ProductCode, Product, PTM.Business, PTM.BudgetYear, PTM.Level1";
strQuery = strQuery + " Order By Category, Product";
string strConn = ConfigurationManager.ConnectionStrings["LILI_BMSConnectionString"].ConnectionString;
SqlConnection objConnection = new SqlConnection(strConn);
SqlCommand objCommand = new SqlCommand(strQuery, objConnection);
objCommand.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(objCommand);
DataSet ds = new DataSet();
objConnection.Open();
da.Fill(ds);
objConnection.Close();
rvBudgetEntry.LocalReport.DataSources.Add(new ReportDataSource("DSBudgetTargetCOGSGPEntryDataSet", ds.Tables[0]));
//var generationDate = txtFromDate.Text.Trim();
//DateTime effectiveDate;
//if (DateTime.TryParse(generationDate, out effectiveDate))
//{
// string searchParameter = "Business :" + ddlBusiness.SelectedItem;
// string shiftName = "Year :" + ddlYear.SelectedItem;
// ReportParameter p1 = new ReportParameter("param", searchParameter);
// ReportParameter p2 = new ReportParameter("paramShiftName", shiftName);
// rvBudgetEntry.LocalReport.SetParameters(new ReportParameter[] { p1, p2 });
//}
string searchParameter = "Business :" + ddlBusiness.SelectedItem;
string shiftName = "Year :" + ddlYear.SelectedItem;
ReportParameter p1 = new ReportParameter("param", searchParameter);
ReportParameter p2 = new ReportParameter("paramShiftName", shiftName);
rvBudgetEntry.LocalReport.SetParameters(new ReportParameter[] { p1, p2 });
//this.rvBudgetEntry.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(localReport_SubreportProcessing);
rvBudgetEntry.DataBind();
}
