Friday, August 29, 2014

How to Increasing “MaxStringContentLength” for connecting to webservice(WCF) ?


Today I discovered the some configuration impacts from my choice to return the XML document as a string property.  Because I am using a string property to return the XML generated by my service. I had to adjust three properties in the service and client configuration.

 MaxBufferSize property - (From MSDN) Gets or sets the maximum size of the buffer to use. For buffered messages this value is the same as MaxReceivedMessageSize. For streamed messages, this value is the maximum size of the SOAP headers, which must be read in buffered mode.

 MaxReceivedMessageSize must also match what you put in the MaxBufferSize.  Default is 65536.

 MaxStringContentLength - (From MSDN) Gets and sets the maximum string length returned by the reader. Default is 8192.

for example:

System.ServiceModel.BasicHttpBinding ad = new System.ServiceModel.BasicHttpBinding();
            ad.ReaderQuotas.MaxStringContentLength = 2147483647;
            ad.MaxReceivedMessageSize = 2147483647;
            ad.MaxBufferSize = 2147483647;
        

  XYZervices.XYZServiceClient proxy = new XYZServices.XYZServiceClient(ad, new
System.ServiceModel.EndpointAddress("http://localhost:58297/XYZService.svc"));

            var Data = proxy.GetBookingData("ABX", "TTT", 900);

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