Vfrdtyky

I have the below stored procedure which I use to show data on multiple ASP chart items.

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetChartApprovedData`(in siteValue varchar(45),

in skillValue varchar(100), in shiftValue varchar(100), in tmValue varchar(45), 

in grpmValue varchar(45), in dateValue date, in dateValue1 date)

BEGIN

SELECT count(agentlogin) AS totalApproved, shift AS Shift, skill AS Skill, tm AS TM, grpM AS GrpM

   FROM approved

   WHERE (sitevalue IS NULL

           OR site = sitevalue)

         AND (skillvalue IS NULL

               OR skill = skillvalue)

         AND (shiftvalue IS NULL

               OR shift = shiftvalue)

         AND (tmValue IS NULL

                OR tm = tmValue)

         AND (grpmValue IS NULL

                OR grpM = grpmValue)

         AND (dateValue IS NULL

                OR date BETWEEN dateValue AND dateValue1)

                group by shift, skill;

END

And when I use the above stored procedure to show the data in ASP chart I get the below result

Both the chart are giving the same grouping result. What I want is for the first chart I want it to group by shift and for the second chart I want it to be grouped by skill. Is it possible to achieve this without using separate stored procedure for both? Please let me know. Thanks in advance :)

private void GetChartData()

{

    string MyConString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

    MySqlConnection con = new MySqlConnection(MyConString);

    MySqlCommand cmd = new MySqlCommand("GetChartApprovedData");

    cmd.CommandType = CommandType.StoredProcedure;

    string siteValue = null;

    DateTime? dateValue = null;

    DateTime? dateValue1 = null;

    if (ddlSite.SelectedValue != null && ddlSite.SelectedValue != "0")

    {

       siteValue = ddlSite.SelectedValue;

    }

    if (ViewState["Date"] != null && ViewState["Date"].ToString() != "0")

    {

        dateValue = DateTime.Parse(ViewState["Date"].ToString());

    }

    if (ViewState["Date1"] != null && ViewState["Date1"].ToString() != "0")

    {

        dateValue1 = DateTime.Parse(ViewState["Date1"].ToString());

    }

    cmd.Parameters.AddWithValue("siteValue", siteValue);

    cmd.Parameters.AddWithValue("dateValue", dateValue);

    cmd.Parameters.AddWithValue("dateValue1", dateValue1);

    cmd.Connection = con;

    con.Open();

    MySqlDataReader myread = cmd.ExecuteReader();

    while (myread.Read())

    {

        this.Chart2.Series["Series1"].Points.AddXY(myread["Shift"], myread["totalApproved"]);

        Chart2.Series["Series1"].IsValueShownAsLabel = true;

        Chart2.Series["Series1"].Label = "#VALY(#PERCENT)";

        Chart2.Series["Series1"].ToolTip = "Shift: #VALX \nCount: #VALY";

        Chart2.ChartAreas["ChartArea1"].AxisX.LabelStyle.Interval = 1;

        Chart2.Legends.Clear();

        Chart2.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;

        Chart2.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;

        Chart2.Series["Series1"].Color = Color.DarkOrange;





        this.Chart1.Series["Series1"].Points.AddXY(myread["Skill"], myread["totalApproved"]);

        Chart1.Series["Series1"].IsValueShownAsLabel = true;

        Chart1.Series["Series1"].Label = "#VALY(#PERCENT)";

        Chart1.Series["Series1"].ToolTip = "Skill: #VALX \nCount: #VALY";

        Chart1.ChartAreas["ChartArea1"].AxisX.LabelStyle.Interval = 1;

        Chart1.Series["Series1"].Color = Color.DarkOrange;

        Chart1.Series["Series1"].LabelBackColor = Color.White;

        Chart1.Legends.Clear();

        Chart1.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;

        Chart1.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;

   }

   con.Close();

}

One way around your problem would be to do both queries inside your procedure (one to GROUP BY shift, and one to GROUP BY skill, and use a flag in the results to indicate if the result was data grouped by shift or skill:

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetChartApprovedData`(in siteValue varchar(45),

in skillValue varchar(100), in shiftValue varchar(100), in tmValue varchar(45), 

in grpmValue varchar(45), in dateValue date, in dateValue1 date)

BEGIN

SELECT 'skill' AS type, count(agentlogin) AS totalApproved, skill AS Skill, tm AS TM, grpM AS GrpM

   FROM approved

   WHERE (sitevalue IS NULL

           OR site = sitevalue)

         AND (skillvalue IS NULL

               OR skill = skillvalue)

         AND (shiftvalue IS NULL

               OR shift = shiftvalue)

         AND (tmValue IS NULL

                OR tm = tmValue)

         AND (grpmValue IS NULL

                OR grpM = grpmValue)

         AND (dateValue IS NULL

                OR date BETWEEN dateValue AND dateValue1)

                group by skill;

SELECT 'shift' AS type, count(agentlogin) AS totalApproved, shift AS Shift, tm AS TM, grpM AS GrpM

   FROM approved

   WHERE (sitevalue IS NULL

           OR site = sitevalue)

         AND (skillvalue IS NULL

               OR skill = skillvalue)

         AND (shiftvalue IS NULL

               OR shift = shiftvalue)

         AND (tmValue IS NULL

                OR tm = tmValue)

         AND (grpmValue IS NULL

                OR grpM = grpmValue)

         AND (dateValue IS NULL

                OR date BETWEEN dateValue AND dateValue1)

                group by shift;

END

Then in your c# code you would change these lines:

    this.Chart2.Series["Series1"].Points.AddXY(myread["Shift"], myread["totalApproved"]);

    this.Chart1.Series["Series1"].Points.AddXY(myread["Skill"], myread["totalApproved"]);

to:

if (myread["Type"] == "shift") {

     this.Chart2.Series["Series1"].Points.AddXY(myread["Shift"], myread["totalApproved"]);

}

if (myread["Type"] == "skill") {

     this.Chart1.Series["Series1"].Points.AddXY(myread["Skill"], myread["totalApproved"]);

}

Yes, it is possible using a Dynamic SQL. We can create a query string, and then prepare and execute it. This will allow us to specify dynamic column name, which is not possible directly.

You will also need to use one more parameter to specify which column to use in Group By

DELIMITER $$



CREATE DEFINER=`root`@`localhost` 

  PROCEDURE `GetChartApprovedData`(in siteValue varchar(45),

                                   in skillValue varchar(100), 

                                   in shiftValue varchar(100), 

                                   in tmValue varchar(45), 

                                   in grpmValue varchar(45), 

                                   in dateValue date, 

                                   in dateValue1 date, 

                                   in groupByColumn varchar(64)) 

-- add extra in parameter, groupByColumn, to specify which column to group upon 



BEGIN



SET query_str = CONCAT('SELECT 

                          count(agentlogin) AS totalApproved, 

                          shift AS Shift, 

                          skill AS Skill, 

                          tm AS TM, 

                          grpM AS GrpM

                        FROM approved

                        WHERE (sitevalue IS NULL

                               OR site = sitevalue)

                          AND (skillvalue IS NULL

                               OR skill = skillvalue)

                          AND (shiftvalue IS NULL

                               OR shift = shiftvalue)

                          AND (tmValue IS NULL

                               OR tm = tmValue)

                          AND (grpmValue IS NULL

                               OR grpM = grpmValue)

                          AND (dateValue IS NULL

                               OR date BETWEEN dateValue AND dateValue1)

                        GROUP BY ', 

                        groupByColumn); -- concatenate the group by column param



-- prepare the query

PREPARE stmt FROM query_str;



-- execute the query

EXECUTE stmt;



-- Clear up

DEALLOCATE PREPARE stmt;



END $$



DELIMITER ;

If you just want to reduse c# code size than you can use the stored procedure provided by Madhu Bhaiya with the code bellow to achieve desired result:

    private void GetChartData()

    {

        string MyConString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        MySqlConnection con = new MySqlConnection(MyConString);





        //PTK: TypeOfChartValue should be change with chart type you are using (I did not know what chart library you are using in your code

        Dictionary<string, TypeOfChartValue> charts = new Dictionary<string, Chart>()

        {

            { "shift", this.Chart2 },  //PTK: the first value here is a column name to use in group by , the second is the chart to fill with resulted data. this line means that the Chart2 must be filled with data grouped by  'shift'

            { "skill", this.Chart1 }  //PTK: the first value here is a column name to use in group by , the second is the chart to fill with resulted data. this line means that the Chart1 must be filled with data grouped by  'skill'

            //PTK: you can add here as many charts as you wish

        };

        foreach (string groupby in charts.Keys)

        {

            //PTK: TypeOfChartValue should be change with chart type you are using (I did not know what chart library you are using in your code

            TypeOfChartValue chart = charts[groupby];



            MySqlCommand cmd = new MySqlCommand("GetChartApprovedData");

            cmd.CommandType = CommandType.StoredProcedure;

            string siteValue = null;

            DateTime? dateValue = null;

            DateTime? dateValue1 = null;

            if (ddlSite.SelectedValue != null && ddlSite.SelectedValue != "0")

            {

                siteValue = ddlSite.SelectedValue;

            }

            if (ViewState["Date"] != null && ViewState["Date"].ToString() != "0")

            {

                dateValue = DateTime.Parse(ViewState["Date"].ToString());

            }

            if (ViewState["Date1"] != null && ViewState["Date1"].ToString() != "0")

            {

                dateValue1 = DateTime.Parse(ViewState["Date1"].ToString());

            }

            cmd.Parameters.AddWithValue("siteValue", siteValue);

            cmd.Parameters.AddWithValue("dateValue", dateValue);

            cmd.Parameters.AddWithValue("groupByColumn", groupby);



            cmd.Connection = con;

            con.Open();

            MySqlDataReader myread = cmd.ExecuteReader();

            while (myread.Read())

            {

                chart.Series["Series1"].Points.AddXY(myread["Shift"], myread["totalApproved"]);

                chart.Series["Series1"].IsValueShownAsLabel = true;

                chart.Series["Series1"].Label = "#VALY(#PERCENT)";

                chart.Series["Series1"].ToolTip = "Shift: #VALX \nCount: #VALY";

                chart.ChartAreas["ChartArea1"].AxisX.LabelStyle.Interval = 1;

                chart.Legends.Clear();

                chart.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;

                chart.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;

                chart.Series["Series1"].Color = Color.DarkOrange;



            }

            con.Close();

        }

    }