Saturday, March 22, 2014

How To split string in Sql Server?

Split String Function

Sometime we need to split string in sql  server like Comma sparated(,) or colon(:) or dot(.) or (#)

For examples:


declare @Pname Nvarchar(MAX)
SET @Pname ='XZY,TTT,YYY,UUU'

select *
    FROM [dbo].[SplitString] (@Pname,',')

You can Use Following Function to Split Sting in Sql Sever


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[SplitString](@text varchar(8000), @delimiter varchar(1) = ',')
-- This function splits a string of CSV values and creates a table variable with the values.
-- Returns the table variable that it creates
RETURNS @Strings TABLE
(
    position int IDENTITY PRIMARY KEY,
    member_id varchar(8000)
)

AS

BEGIN
    Declare @index int  
    Set @index = -1  
 
    WHILE (LEN(@text) > 0)  
       BEGIN
        SET @index = CHARINDEX(@delimiter , @text)
        IF (@index = 0) AND (LEN(@text) > 0)
               BEGIN
               INSERT INTO @Strings VALUES (@text)
            BREAK
           END
 
        IF (@index > 1)
                 BEGIN
            INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
            SET @text = RIGHT(@text, (LEN(@text) - @index))
           END
            ELSE  
            SET @text = RIGHT(@text, (LEN(@text) - @index))
 
       END  
    RETURN
END

Friday, March 21, 2014

Closing Stock Store Procedure

Friends Today i share With You Closing Stock Report Example


 ALTER PROCEDURE [dbo].[ClosingStock]
      -- Add the parameters for the stored procedure here
      @startdate datetime,
      @enddate datetime,
      @than nvarchar(1)='<'
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      select itemname ,opening,inwardqty,outwardqty ,
(isnull(opening,0) + isnull(inwardqty,0) - isnull(outwardqty,0)) as closing
from (
select m.Itemname as itemname,sum(isnull(openinward,0)-isnull(openoutward,0)) as opening,
isnull((inwardqty),0)as inwardqty,isnull((outwardqty),0)as outwardqty
from itemmaster m
--------Opening---------------------------------------
left outer join(select id.itemid, isnull(sum(id.qty),0) as openinward  from inwarddetail id
left outer join inward i on i.inwardid = id.inwardid
where  inwarddate < @startdate
 group by itemid) k
on m.itemid = k.itemid left outer join
( select od.itemid, isnull(sum(od.qty),0) as openoutward  from outwarddetail od
left outer join outward i on i.outwardid = od.outwardid
where  outwarddate < @startdate
 group by itemid
) oo
on  m.itemid = oo.itemid
-----------Inward qty---------------------------------------
left outer join(
select itemid, sum(qty) as inwardqty  from inwarddetail id
left outer join inward i on i.inwardid = id.inwardid
where inwarddate >= @startdate  and inwarddate <= @enddate
 group by itemid
) i
 on m.itemid = i.itemid
------------------Outward qty--------------------------------
left outer join( select itemid, sum(qty) as outwardqty  from outwarddetail od
left outer join outward i on i.outwardid = od.outwardid
where outwarddate >= @startdate and outwarddate <= @enddate 
 group by itemid
) o
on  m.itemid = o.itemid
group by m.Itemname,inwardqty,outwardqty ) t
where
(case when @than ='<' Then (isnull(opening,0) + isnull(inwardqty,0) - isnull(outwardqty,0))End) <= 0 
or
(case when @than ='>' Then (isnull(opening,0) + isnull(inwardqty,0) - isnull(outwardqty,0))End) > 0

End

CenterWise Stock Store Procedure…

ALTER PROCEDURE [dbo].[CenterWiseStock]
      -- Add the parameters for the stored procedure here
      @startdate datetime,
      @enddate datetime,
      @centerid int,
      @than nvarchar(1)='<'


AS
BEGIN
      select      itemname,
                  opening,
                  inwardqty,
                  InwardTransfer,
                  outwardqty,
                  OutwardTransfer,
(isnull(opening,0) + isnull(inwardqty,0) + isnull(InwardTransfer,0)-
isnull(outwardqty,0)-isnull(OutwardTransfer,0) ) as closing

From (
select m.Itemname as itemname,
sum(isnull(openinward,0) + isnull(ITopen,0) - isnull(openoutward,0)- isnull(OTopen,0))
 as opening,
isnull((inwardqty),0)as inwardqty,
isnull((TInward),0)as InwardTransfer,
isnull((outwardqty),0)as outwardqty,isnull((TOutward),0)as OutwardTransfer
from itemmaster m
--------Opening---------------------------------------
left outer join(select id.itemid, isnull(sum(id.qty),0) as openinward  from inwarddetail id
left outer join inward i on i.inwardid = id.inwardid
where  inwarddate < @startdate And i.CenterId =@centerid
 group by itemid) k
on m.itemid = k.itemid left outer join
( select od.itemid, isnull(sum(od.qty),0) as openoutward  from outwarddetail od
left outer join outward i on i.outwardid = od.outwardid
where  outwarddate < @startdate And i.CenterId =@centerid
 group by itemid
) oo
on  m.itemid = oo.itemid left outer join
(
select itemid, sum(qty) as OTopen  from Transfer T
left outer join TransferDetail TD on TD.TransterId = T.TransferId
where  Transferdate < @startdate And T.SourceCenterId =@centerid
 group by itemid
) ITO
on  m.itemid = ITO.itemid
left outer join
(
select itemid, sum(qty) as ITopen  from Transfer T
left outer join TransferDetail TD on TD.TransterId = T.TransferId
where  Transferdate < @startdate And T.DestinationCenterId =@centerid
 group by itemid
) OTo
on  m.itemid = OTo.itemid
left outer join
-------------------inwardqty----------------------------
(
select itemid, sum(qty) as inwardqty  from inwarddetail id
left outer join inward i on i.inwardid = id.inwardid
where inwarddate >= @startdate  and inwarddate <= @enddate And i.CenterId =@centerid
group by itemid
) i
 on m.itemid = i.itemid left outer join
--------------------------outwardqty----------------------
( select itemid, sum(qty) as outwardqty  from outwarddetail od
left outer join outward i on i.outwardid = od.outwardid
where outwarddate >= @startdate and outwarddate <= @enddate And i.CenterId =@centerid
 group by itemid
) o
on  m.itemid = o.itemid left outer join
--------------------------Transfer outward------------------
(
select itemid, sum(qty) as TOutward  from Transfer T
left outer join TransferDetail TD on TD.TransterId = T.TransferId
where Transferdate >= @startdate and Transferdate <= @enddate And T.SourceCenterId =@centerid
 group by itemid
) IT
on  m.itemid = IT.itemid left outer join
------------------------Transfer Inward-------------------------
(
select itemid, sum(qty) as TInward  from Transfer T
left outer join TransferDetail TD on TD.TransterId = T.TransferId
where Transferdate >= @startdate and Transferdate <= @enddate And T.DestinationCenterId =@centerid
 group by itemid
) OT
on  m.itemid = OT.itemid
group by m.Itemname,inwardqty,outwardqty,TOutward,TInward )
Temp
where
(case when @than ='<' Then (isnull(opening,0) + isnull(inwardqty,0) + isnull(InwardTransfer,0)-
isnull(outwardqty,0)-isnull(OutwardTransfer,0) )End) <= 0 
or
(case when @than ='>' Then (isnull(opening,0) + isnull(inwardqty,0) + isnull(InwardTransfer,0)-
isnull(outwardqty,0)-isnull(OutwardTransfer,0))End) > 0



END


How To Create Dynamic Store Procedure More Angle Filterable Store Procedure?

Dynamic store Procedure example

 ALTER PROCEDURE [dbo].[Dynamic_Store_Procedure]
      -- Add the parameters for the stored procedure here
 @startdate as datetime,
 @enddate as datetime,
 @than as Nvarchar(MAX)='>',
 @num as int=0,
 @khojitype as Nvarchar(50),
 @sharvanthanName as Nvarchar(max),
 @actual bit=1,
 @seva bit=1
AS

BEGIN
 DECLARE @SQLString Nvarchar(max)

set @SQLString='select khojiname,totalpravachan,sevacount,Pointscount,MAdate,sharvanthanName,Khojitype,MobileNo from (
select kh.khojiname,isnull(temp.sc,0) as totalpravachan,Convert(Varchar,kh.Madate,103)as MAdate,sm.sharvanthanName ,kh.Khojitype,kh.MobileNo
,isnull(seva.sevacount,0) as sevacount,isnull(seva.points,0)as Pointscount from khojimaster kh left outer join
(select ad.khojiid,count(*) as sc from attendencedetail ad left outer join
attendence a on ad.attendenceid = a.attendenceid Where 1=1'

If Isnull(@startdate,'')!=''
BEGIN
      SET @SQLString = @SQLString + ' And a.attendenceDate >=     ''' + convert(nvarchar(10),@startdate,101) + ''''
end

If Isnull(@enddate,'')!=''
BEGIN
      SET @SQLString = @SQLString + ' AND a.attendenceDate <=     ''' + convert(nvarchar(10),@enddate,101) + ''''      
END
SET @SQLString = @SQLString + ' GROUP BY ad.khojiid '

--SET @SQLString =@SQLString + ' Having Count(*) >= ''' + convert(Nvarchar(20),isnull(@num,0))+ ''' '

 SET @SQLString =@SQLString + ' Having
(Case 
 When ('''+ @than + ''')=''>'' Then Count(*) End)>= ''' + convert(Nvarchar(20),isnull(@num,0))+ '''
OR
(Case
When ('''+ @than + ''')=''<'' Then Count(*) End)<= ''' + convert(Nvarchar(20),isnull(@num,0))+ ''' '

SET @SQLString =@SQLString + ' )temp on temp.khojiid = kh.khojiid '

SET @SQLString =@SQLString + 'left outer join(select khojiid,count(*)as sevacount,sum(stm.Points)as points  from sevadetail sd left outer join sevaMaster
sm on sm.sevamasterid=sd.sevamasterid left outer join sevatypemaster
stm on stm.SevaTypeId=sm.SevaTypeId  Where 1=1 '

If Isnull(@startdate,'')!=''
BEGIN
      SET @SQLString = @SQLString + ' And sm.sevadate >= ''' + convert(nvarchar(10),@startdate,101) + ''''
end

If Isnull(@enddate,'')!=''
BEGIN
      SET @SQLString = @SQLString + ' AND sm.sevadate <= ''' + convert(nvarchar(10),@enddate,101) + ''''      
END

SET @SQLString = @SQLString + ' group by khojiid)seva on seva.khojiid=kh.khojiid  '

SET @SQLString =@SQLString + 'Left outer join sharvanthanMaster as sm on sm.sharvanthanid=kh.sharvanthanid Where 1=1 '

if isnull(@khojitype,'')<>''
Begin
set @SQLString =@SQLString + ' And kh.khojitype in (select member_id from  SplitString('''+ @khojitype + ''','',''))'
End

if isnull(@sharvanthanName,'')<>''
Begin
set @SQLString =@SQLString + ' And sm.sharvanthanName In (select member_id from SplitString('''+ @sharvanthanName +''','',''))) p Where 1=1'
End
if isnull(@actual,0)=0
Begin
set @SQLString =@SQLString + ' And totalpravachan >'''+ convert(Nvarchar(20),isnull(@actual,0))  + ''''
End

if isnull(@seva,0)=0
Begin
set @SQLString =@SQLString + ' And sevacount >'''+ convert(Nvarchar(20),isnull(@seva,0))  + ''''
End


set @SQLString =@SQLString + ' Order by  khojiName'


Exec(@SQLString)
--print @SQLString

END

How to Create Graph And Drill Downgraph Using Telerik Graph Using StoreProcedure And Wpf ?

Here Sample code of Drill Down Chart using store Procedure and WPF


XAML Code

<ScrollViewer VerticalScrollBarVisibility="Auto" Grid.Column="1">
                           <ScrollViewer.Background>
                                         <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">
                                                <GradientStop Color="Black" Offset="0"/>
                                                <GradientStop Color="White" Offset="1"/>
                                         </LinearGradientBrush>
                                </ScrollViewer.Background> 
                            <Grid x:Name="chartgrid" ShowGridLines="True" Grid.Column="1"  />
    </ScrollViewer>

Code Behind
public partial class SharvanGraph : Window
    {
        HTBookEntities proentity = new HTBookEntities();

        public SharvanGraph()
        {
            InitializeComponent();

            radChart1.DefaultView.ChartArea.ItemClick += new EventHandler<ChartItemClickEventArgs>(ChartArea_ItemClick);

        }

        SeriesMapping sm = new SeriesMapping();
        RadChart radChart1 = new RadChart();
        RadChart radchart2 = new RadChart();
        SeriesMapping sm2 = new SeriesMapping();
     
       
        private void cmbkhojiname_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                if (datePicker1.SelectedDate == null || datePicker2.SelectedDate == null)
                {
                    MessageBox.Show("Please Select First Date");
                }
                else
                {
                    int kid = (int)cmbkhojiname.SelectedValue;
                    string kname = (from k in proentity.KhojiMasters where k.KhojiId == kid select k.KhojiName).FirstOrDefault();
                    var data = proentity.AllMonthdataGraph((DateTime)datePicker1.SelectedDate, (DateTime)datePicker2.SelectedDate, null, kid).ToList();
                  
                    if (chartgrid.RowDefinitions.Count <= 2)
                    {
                        RowDefinition rd2 = new RowDefinition();
                        rd2.Height = new GridLength(0, GridUnitType.Star);
                        chartgrid.RowDefinitions.Add(rd2);
                        for (int i = 0; i <= 1; i++)
                        {
                            RowDefinition rd = new RowDefinition();
                            rd.Height = new GridLength(1, GridUnitType.Auto);
                            chartgrid.RowDefinitions.Add(rd);
                        }
                    }

                    radChart1.SetValue(Grid.RowProperty, 1);
                    radChart1.Height = 350;
                    if (sm.SeriesDefinition == null)
                    {
                        chartgrid.Children.Clear();
                        chartgrid.Children.Add(radChart1);
                        sm.SeriesDefinition = new BarSeriesDefinition();
                        sm.LegendLabel = "Pravachan" + "\n" + "Count" + "\n" + "Each" + "\n" + "Month";
                        sm.ItemMappings.Add(new ItemMapping("Monthyear", DataPointMember.XCategory));
                        sm.ItemMappings.Add(new ItemMapping("pcount", DataPointMember.YValue));
                        radChart1.SeriesMappings.Add(sm);
                    }
                    else
                    {
                        radChart1.ItemsSource = data;
                    }
                    radChart1.ItemsSource = data;
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
           
        }
         ------------------------------------------------------------------
        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            try
            {
                cmbkhojiname.ItemsSource = (from k in proentity.KhojiMasters
                                            orderby k.KhojiName
                                            select new
                                            {
                                                k.KhojiId,
                                                k.KhojiName
                                            });
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
          
        }

        private void expCentredetail_Expanded(object sender, Telerik.Windows.RadRoutedEventArgs e)
        {
        }
        #region code Sharvanthanwise Chart
        private void All_Checked(object sender, RoutedEventArgs e)
        {
            System.Windows.Input.Cursor savecursor = this.Cursor;
            this.Cursor = Cursors.Wait;
            var data = (from k in proentity.KhojiMasters
                        select new
                        {
                            k.KhojiId
                        });
            WrapPanel wrap = new WrapPanel();

            chartgrid.Children.Add(wrap);
            foreach (var id in data)
            {
                #region scollviewINCode
                //ScrollViewer scroll = new ScrollViewer();
                //scroll.SetValue(Grid.ColumnProperty, 1);

                //ScrollViewer.SetVerticalScrollBarVisibility(wrap, ScrollBarVisibility.Auto);

                //ScrollViewer.SetHorizontalScrollBarVisibility(wrap, ScrollBarVisibility.Auto);
                #endregion

                int kid = (Int32)id.KhojiId;
                var chartdata = proentity.MonthNameWiseCount(kid).ToList();
                string kname = (from k in proentity.KhojiMasters where k.KhojiId == kid select k.KhojiName).FirstOrDefault();
                Telerik.Windows.Controls.RadChart radchart1 = new Telerik.Windows.Controls.RadChart();
                radchart1.HorizontalAlignment = HorizontalAlignment.Left;
                radchart1.Margin = new Thickness(10, 10.076, 10, 7.01);
                radchart1.FontFamily = new FontFamily("Comic Sans MS");
                radchart1.Width = 321.07;
                radchart1.Height = 250;
                wrap.Children.Add(radchart1);
                SeriesMapping sm = new SeriesMapping();
                sm.SeriesDefinition = new BarSeriesDefinition();
                sm.LegendLabel = "P";
                sm.ItemMappings.Add(new ItemMapping("Adate", DataPointMember.XCategory));
                sm.ItemMappings.Add(new ItemMapping("Pcount", DataPointMember.YValue));
                radchart1.DefaultView.ChartArea.AxisX.LabelRotationAngle = 50;
                radchart1.SeriesMappings.Add(sm);
                radchart1.Content = kname;
                radchart1.ItemsSource = chartdata;


            }

            this.Cursor = savecursor;

        }

        private void Happyshop_Checked(object sender, RoutedEventArgs e)
        {
     
      chartgrid.Children.Clear();
            katargam.IsChecked = false;
            HSmorning.IsChecked = false;
            LH.IsChecked = false;
            valamnagar.IsChecked = false;
            Udhana.IsChecked = false;
            sachin.IsChecked = false;
            Rishikesh.IsChecked = false;
            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;
            this.Cursor = Cursors.Wait;
            Multiplechart((string)Happyshop.Content);
            this.Cursor = savecursor;

            //gridname.IsBusy = false;

        }

        private void katargam_Checked(object sender, RoutedEventArgs e)
        {

            chartgrid.Children.Clear();
            Happyshop.IsChecked = false;
            HSmorning.IsChecked = false;
            LH.IsChecked = false;
            valamnagar.IsChecked = false;
            Udhana.IsChecked = false;
            sachin.IsChecked = false;
            Rishikesh.IsChecked = false;
            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;
            this.Cursor = Cursors.Wait;
            Multiplechart((string)katargam.Content);
            this.Cursor = savecursor;

        }

        private void HSmorning_Checked(object sender, RoutedEventArgs e)
        {
            chartgrid.Children.Clear();
            Happyshop.IsChecked = false;
            katargam.IsChecked = false;
            LH.IsChecked = false;
            valamnagar.IsChecked = false;
            Udhana.IsChecked = false;
            sachin.IsChecked = false;
            Rishikesh.IsChecked = false;
            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;
            this.Cursor = Cursors.Wait;
            Multiplechart((string)HSmorning.Content);
            this.Cursor = savecursor;
        }

        private void LH_Checked(object sender, RoutedEventArgs e)
        {
            chartgrid.Children.Clear();
            Happyshop.IsChecked = false;
            katargam.IsChecked = false;
            HSmorning.IsChecked = false;
            valamnagar.IsChecked = false;
            Udhana.IsChecked = false;
            sachin.IsChecked = false;
            Rishikesh.IsChecked = false;
            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;
            this.Cursor = Cursors.Wait;
            Multiplechart((string)LH.Content);
            this.Cursor = savecursor;
        }

        private void valamnagar_Checked(object sender, RoutedEventArgs e)
        {
            chartgrid.Children.Clear();
            Happyshop.IsChecked = false;
            katargam.IsChecked = false;
            HSmorning.IsChecked = false;
            LH.IsChecked = false;
            Udhana.IsChecked = false;
            sachin.IsChecked = false;
            Rishikesh.IsChecked = false;
            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;
            this.Cursor = Cursors.Wait;
            Multiplechart((string)valamnagar.Content);
            this.Cursor = savecursor;
        }

        private void Udhana_Checked(object sender, RoutedEventArgs e)
        {
            chartgrid.Children.Clear();
            Happyshop.IsChecked = false;
            katargam.IsChecked = false;
            HSmorning.IsChecked = false;
            LH.IsChecked = false;
            valamnagar.IsChecked = false;
            sachin.IsChecked = false;
            Rishikesh.IsChecked = false;
            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;
            this.Cursor = Cursors.Wait;
            Multiplechart((string)Udhana.Content);
            this.Cursor = savecursor;

        }

        private void sachin_Checked(object sender, RoutedEventArgs e)
        {
            chartgrid.Children.Clear();
            Happyshop.IsChecked = false;
            katargam.IsChecked = false;
            HSmorning.IsChecked = false;
            LH.IsChecked = false;
            valamnagar.IsChecked = false;
            Udhana.IsChecked = false;
            Rishikesh.IsChecked = false;
            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;
            this.Cursor = Cursors.Wait;
            Multiplechart((string)sachin.Content);
            this.Cursor = savecursor;

        }

        private void Rishikesh_Checked(object sender, RoutedEventArgs e)
        {
            chartgrid.Children.Clear();
            Happyshop.IsChecked = false;
            katargam.IsChecked = false;
            HSmorning.IsChecked = false;
            LH.IsChecked = false;
            valamnagar.IsChecked = false;
            Udhana.IsChecked = false;
            sachin.IsChecked = false;
            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;
            this.Cursor = Cursors.Wait;
            Multiplechart((string)Rishikesh.Content);
            this.Cursor = savecursor;
        }

        private void Citylight_Checked(object sender, RoutedEventArgs e)
        {
            chartgrid.Children.Clear();
            Happyshop.IsChecked = false;
            katargam.IsChecked = false;
            HSmorning.IsChecked = false;
            LH.IsChecked = false;
            valamnagar.IsChecked = false;
            Udhana.IsChecked = false;
            sachin.IsChecked = false;
            Rishikesh.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;
            this.Cursor = Cursors.Wait;
            Multiplechart((string)Citylight.Content);
            this.Cursor = savecursor;
        }
        #endregion
        private void Happyshop_Unchecked(object sender, RoutedEventArgs e)
        {


        }

        private void btndetail_Click(object sender, RoutedEventArgs e)
        {
            chartgrid.Children.Clear();

        }
        private void ChartArea_ItemClick(object sender, ChartItemClickEventArgs e)
        {
            try
            {
                //MonthNameWiseCount_Result p = (e.DataPoint.DataItem as MonthNameWiseCount_Result);
            //string month = p.Adate;

                AllMonthdataGraph_Result p = (e.DataPoint.DataItem as AllMonthdataGraph_Result);
           
            Int32 month = (Int32)p.PMonth;
            Int32 year = (Int32)p.Pyear;
            Int32 id = (Int32)p.kid;



            var pravachan = proentity.GetPravachanInMonth(id, month,year).ToList();
            radchart2.SetValue(Grid.RowProperty, 2);
            radchart2.Height = 350;

            {

                if (sm2.SeriesDefinition == null)
                {

                    chartgrid.Children.Add(radchart2);
                    sm2.SeriesDefinition = new BarSeriesDefinition();

                    sm2.LegendLabel = "Pravachan" + "\n" + "Name";
                    sm2.ItemMappings.Add(new ItemMapping("PravachanName", DataPointMember.XCategory));
                    sm2.ItemMappings.Add(new ItemMapping("Pcount", DataPointMember.YValue));
                    radchart2.DefaultView.ChartArea.AxisX.LabelRotationAngle = 65;
                    radchart2.SeriesMappings.Add(sm2);
                }


            }

            //radChart1.Content = kname;
            this.radchart2.ItemsSource = pravachan;

    
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }

        }

        private void Multiplechart(string sharvanthanName)
        {
            try
            {
                if (datePicker1.SelectedDate == null || datePicker2.SelectedDate == null)
                {
                    MessageBox.Show("Please Select First Date");
                }
                else
                {
                    WrapPanel wrap = new WrapPanel();
                    //gridname.IsBusy = true;
                    int sid = (from s in proentity.SharvanthanMasters
                               where s.SharvanthanName == sharvanthanName
                               select s.SharvanthanId).FirstOrDefault();
                    var data = (from s in proentity.KhojiMasters
                                where s.SharvanthanId == sid
                                select new
                                {
                                    s.KhojiId
                                });


                    chartgrid.Children.Add(wrap);
                    foreach (var id in data)
                    {
                        int kid = (Int32)id.KhojiId;
                        var chartdata = proentity.AllMonthdataGraph((DateTime)datePicker1.SelectedDate, (DateTime)datePicker2.SelectedDate, null, kid).ToList();
                        string kname = (from k in proentity.KhojiMasters where k.KhojiId == kid select k.KhojiName).FirstOrDefault();
                        Telerik.Windows.Controls.RadChart radchart1 = new Telerik.Windows.Controls.RadChart();
                        radchart1.HorizontalAlignment = HorizontalAlignment.Left;
                        radchart1.Margin = new Thickness(10, 10.076, 10, 7.01);
                        radchart1.FontFamily = new FontFamily("Comic Sans MS");
                        radchart1.Width = 321.07;
                        radchart1.Height = 250;
                        wrap.Children.Add(radchart1);
                        SeriesMapping sm = new SeriesMapping();
                        sm.SeriesDefinition = new SplineAreaSeriesDefinition();
                        sm.LegendLabel = "P";
                        sm.ItemMappings.Add(new ItemMapping("Monthyear", DataPointMember.XCategory));
                        sm.ItemMappings.Add(new ItemMapping("pcount", DataPointMember.YValue));
                        radchart1.DefaultView.ChartArea.AxisX.LabelRotationAngle = 90;
                        radchart1.SeriesMappings.Add(sm);
                        radchart1.Content = kname;
                        radchart1.ItemsSource = chartdata;
                        //Yearmonth
                        
                    }
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
           
        }

    }

Store Procedure
----------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[AllMonthdataGraph]
      -- Add the parameters for the stored procedure here
     
 @fromdate datetime=null,
  @todate datetime=null,
  @startdate datetime=null,
  @khojiid int

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
       


Create table #temp5
(
kid int,
Monthyear varchar(20),
PMonth int,
Pyear int,
pcount int)
set @startdate=@fromdate
while (@startdate<=@todate)
begin

declare @M int
 set @M=Month(@startdate)
declare @y int
set @y=Year(@startdate)
declare @mName varchar(20)
set @mName=Convert(varchar(3),Datename(Month,@startdate))+ '-' + Right(YEAR(@startdate)+0,2)

Insert into #temp5 values(@khojiid,@mName,@M,@y,0)
set @startdate= DATEADD(Month,1,@startdate)
End



declare @id int
declare @monthyear Varchar(20)
declare @Year int
Declare @Month int
declare @pcount int

--

declare UpdateCursor Cursor
For Select kid,PMonth,pyear,pcount from #temp5

Open UpdateCursor
Fetch Next from UpdateCursor Into @id,@month,@Year,@pcount
WHILE @@FETCH_STATUS = 0
BEGIN
select @id=khojiid ,@Year=year(attendencedate)
,@Month=month(attendencedate)
,@pcount=count(pravachanid)

 from Attendencedetail ad
left outer join Attendence a
on ad.attendenceid = a.attendenceid
where ad.khojiid=@khojiid And month(attendencedate)=@Month and year(attendencedate)=@Year
group by khojiid,year(attendencedate),month(attendencedate)

Update #temp5 Set pcount=@pcount Where pyear=@Year And PMonth=@Month

Fetch Next from UpdateCursor Into @id,@month,@Year,@pcount

End
CLOSE UpdateCursor
DEALLOCATE UpdateCursor
--insert into temp5
Select * from #temp5

 END



Drill Down StoreProcedure
ALTER PROCEDURE  [dbo].[PravachanInMonth]
      -- Add the parameters for the stored procedure here
     
       
        @khoji int=null,
        @month int=null,
        @year int=null
AS
BEGIN
       
      SET NOCOUNT ON;



 select ad.khojiid,MONTH(a.attendencedate) as m, Datename(MONTH,a.AttendenceDate) as Adate,count(*)as Pcount,pm.PravachanName  from Attendencedetail ad
Left outer join Attendence a on a.attendenceid=ad.attendenceid
left outer join PravachanMaster pm on a.pravachanid=pm.pravachanid
where ad.KhojiId=@khoji And MONTH(a.attendencedate)=@month And Year(a.attendencedate)=@year

group by ad.khojiid,datename(month,a.attendencedate),MONTH(a.attendencedate),pm.PravachanName
 order by  MONTH(a.attendencedate)


END
Subscribe to: Posts ( Atom )
Powered by Blogger.

Followers

About

Popular Posts

Copyright © Dipak Sutare | Powered by Blogger