Dinamik Sql (Dynamic Sql) ile Raporlama

Merhaba arkadaşlar,

bu yazımda, özellikle muhasebe programları için çokça talep edilen raporlardan biri olan nakit akış raporunun bir ucundan tutmaya çalışacağız. Oluşturacağım rapor scriptleri dinamik olarak oluşacak. Yani raporumun scripti kod içinde bazı karşılaştırmalar neticesinde adım adım oluşacak.

Öncelikle oluşturacağımız raporu tanımaya çalışalım;

nakit akış raporu, vadeli işlenen (cari) faturaların bir projeksiyonudur diyebiliriz. muhtelif vadelerle işlenmiş alış ya da satış faturaları, vadesi itibarıyla ilgili oldukları ayda tahsil ya da tediye edilecek. yani kestiğim faturalar için tahsilatlarım olacağı gibi bana kesilen faturaların vadeleri geldiğinde de ödemelerim olacak. bu sayede de ilgili dönemlerde firmamın mali durumunu gözlemlemiş olacağım (bunu sadece muhasebeyle sınırlandırmamak lazım. aynı yöntemle siparişlerimi ya da tekliflerimi termin tarihlerine göre gruplaya da bilirdim).

işte bu sebeplerden ötürü faturaları alış/satış tiplerine göre gruplayıp gelecek aylar bazında toplamlarını görmek istemekteyim.

elimizde alış ve satış faturalarını işlediğimiz "Faturalar" adında bir tablomuz var (tüm muhasebe programlarında aşağı yukarı bu tarz bir tablo vardır.).

tablonun yapısı; 



select faturatipi,SUM(tutar) as Toplam from Faturalar where vade>GETDATE() group by faturatipi  sorgusuyla vadesi gelmemiş faturalarımın sadece toplamlarını alabilmekteyim;



bu şekilde aldığım rapor bana yalnız toplam borç alacak durumumu vermekte. bu rakamlara bakarak bu ay ya da önümüzdeki ay yapacağım tahsilatları görmem mümkün olmamakta.

İşte bu amacı karşılayacak rapor şu şekilde olmalı;



talebi açıkça anladığımıza göre artık kodumuzu yazabiliriz;

--bu değişken dinamik scriptimizi adım adım oluşturup atadığımız değişkenimiz.
declare @SQLStr nvarchar(max)='',@ay int=0;
declare @minVade datetime = (select MIN(vade) from Faturalar);
--burada maximum vadeli faturaya kadar kaç ay olduğunu hesaplıyoruz.
declare @aySayisi int = (select DATEDIFF(MONTH,min(vade),max(vade)) from Faturalar);

set @SQLStr = ' 
select 
 
faturatipi, '
/*
scriptimizi toplam ay sayısı kadar döndürüp, her dönüşte fatura vadesinin ilgili ay içerisinde olup olmadığına bakıyorum.
eğer fatura vadesi ilgili ay içerisinde ise tutar değerini alıyorum değil ise 0 alıyorum
*/while(@ay<=@aySayisi)
begin
 set @SQLStr=@SQLStr+
 'sum(convert(decimal(18,2),case when dateadd(day,DATEDIFF(day,0,vade),0) between '''
--ayın ilk gününü buluyorum
 + 
 CONVERT(varchar(8),dateadd(MONTH,DATEDIFF(MONTH,0,dateadd(MONTH,@ay,@minVade)),0),112)
 + ''' and '''

--ayın son gününü buluyorum  
 + CONVERT(varchar(8),dateadd(month,1+datediff(month,0,dateadd(MONTH,@ay,@minVade)),-1),112)
 + ''' then tutar' 

--sıra geldi oluşturduğumuz alana isim vermeye. ismi "yıl-ay" kombinasyonu olarak ayarlıyorum.   
 set @SQLStr=@SQLStr+ 
 ' else 0.0 end)) as ['
 + datename(MONTH,dateadd(MONTH,@ay-1,@minVade))
 + '-'+ datename(YEAR,dateadd(MONTH,@ay-1,@minVade))
 + ']'
 if @ay<@aySayisi
   set @SQLStr = @SQLStr + ','

 set @ay=@ay+1
end
--son olarak ta dinamik scriptimin çalışacağı kaynak tabloyu gösteriyorum.
set @SQLStr=@SQLStr+ 
' 
from Faturalar
group by faturatipi  

'

exec (@SQLStr)
GO


tablonun create scriptini ve oluşturduğumuz dinamik scripti buradan indirebilirsiniz. bir kaç satır faturatipi, vade ve tutar bilgilerini doldurup kodu deneyebilirsiniz.

İhtiyaçlarımız doğrultusunda scripti geliştirebiliriz. Örneğin, scriptte haftalık bazda yapılacak revizyonla daha kısa dönemli projeksiyonlar yapılması mümkün kılınabilir. ayrıca işin içine farklı para tiplerini dahil ederek, istenen kura ya da fatura ya da vade tarihlerindeki kurlara göre değerlendirmeler yapılabilir.

Bir sonraki yazımda görüşmek dileğimle,
Yaşar Şahin.

C# Enum tipleri

Merhaba arkadaşlar,

Bu gün ilk C# makalemi oluşturacağım. değinmek istediğim konu Enum tipleri(numarandırıcılar). Bu tipler sayesinde sınırlı değerlere sahip olabilecek değişkenleri temsil edebilirler. bu sayede değişkenin alabileceği değerleri hem kodlamak zorunda kalmaz, hem de olası değer durumlarına kod yazacağımız durumlarda değeri hatırlamak zorunda kalmayız(örn. switch döngüleri.

enum Gunler
        {
            pazartesi, salı, çarşamba, perşembe, cuma
        } 

şeklinde tanımladığımız enum tipi için .Net ide ortamında "sw" yazıp tab yaparsak ide bize olası değerlerin tek tek işlendiği swith-case yapısını oluşturacaktır.).

Benim burda değinmek istediğim Enum tip değerlerini alıp kontrol içeriğini doldurmak ve kontrolden seçilen değerin enum tipine dönüştürülerek kullanılması olacak. Konunun daha iyi anlaşılması için koda geçelim;



using System;
using System.Drawing;
using System.Windows.Forms;

namespace EnumTypes
{
    class Colours
    {
        private KnownColor _color;
        public String Colour { 
get { return _color.ToString(); } 
set { _color = (KnownColor)Enum.Parse(typeof(KnownColor), value); } 
}
/*Tüm anlatılanların özeti burası. Burada, sınıfımız "Colour" özelliği ile, aldığı aldığı string değeri KnownColor enum tipine dönüştürmekte ve bunu _color isimli private KnownColor tipli üyeye atamaktadır.*/

        public String[] ColorNames()
        {
           return Enum.GetNames(typeof(KnownColor));
        }
//bu fonksiyonda parametresine aldığı controlü _color private üyesi değeriyle boyuyor.
        public void PaintMe(object sender)
        {
            Control ctrl = sender as Control;
            if (ctrl == null)
                return;

            try
            {
                ctrl.BackColor = Color.FromKnownColor(_color);
            }

            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
        }
    }
}


uygulama koduda aşağıda ekliyorum.

Örnek çok anlamlı olmasa da enum tiplerinin anlaşılması açısından faydalı olacaktır.

Uygulama kodunu ve tüm blog projelerini buradan indirebilirsiniz.

Faydalı olması dileğimle,
Yaşar Şahin

using System;
using System.Drawing;
using System.Windows.Forms;

namespace EnumTypes
{
    public partial class frmEnum : Form
    {
        Colours _renkler;

        public frmEnum()
        {
            InitializeComponent();

            comboBox1.SelectedIndexChanged += new EventHandler(comboBox1_SelectedIndexChanged);
            _renkler = new Colours();

            comboBox1.Items.AddRange(_renkler.ColorNames());
        }

        void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            toolStripStatusLabel1.Text = "";
            try
            {
                _renkler.Colour = ((ComboBox)sender).SelectedItem.ToString();
                _renkler.PaintMe(comboBox1);
            }
            catch (Exception ex)
            {
                toolStripStatusLabel1.Text = ex.Message;
            }
        }
    }
}

t-Sql Split

Merhaba arkadaşlar,

ilk makalemde, bir sonraki makalemde paylaşmak istediğim, veritabanındaki nesneleri(görünümler, prosedürler, fonksiyonlar gibi) güncellemek için oluşturduğum, bu nesnelerin oluşturma scriptlerini döndüren bir aracın kullandığı tablo değerli bir fonksiyonu (table-valued function) oluşturacağız. Oluşturacağımız bu fonksiyonun ayrı olarak ta işime yaradığını belirtmek isterim.

Aslında bu fonksiyon .Net programcılarının yapancı olmadıkları String sınıfının Split fonksiyonu benzeri olacak. .Net tarafında bu fonksiyonun ilk varyasyonunun görevi;

        // Summary:
        //     Returns a string array that contains the substrings in this instance that
        //     are delimited by elements of a specified Unicode character array. A parameter
        //     specifies the maximum number of substrings to return.
        public string[] Split(char[] separator, int count);

şeklinde belirtilmiş. Bizim oluşturacağımız fonsiyon ikinci parametredeki gibi bir sınırlandırıcı içermeyecek.

Buyrun başlayalım;

create function dbo.fn_Split
(
  @metin varchar(max)
  , @ayrac char(1)
)
returns @rTable table(id int identity(1,1),data varchar(64))
--sonuç tablomuzu tanımlıyoruz
as
begin

--@metin parametresi @ayraç parametresini içerdiği sürece döngü çalışacak.
while(CHARINDEX(@ayrac,@metin)>0)
begin
--ilk sonuç tabloya işleniyor
insert @rTable (data) values (ltrim(rtrim(SUBSTRING(@metin,0,CHARINDEX(@ayrac,@metin)))))
--bulunan alt metin @metin parametresinden çıkarılmalı.
set @metin = SUBSTRING(@metin,CHARINDEX(@ayrac,@metin)+1,LEN(@metin))
end
--@metin parametresinin son hali tabloya işleniyor. döngü dışındaki bu konumda @metin artık ayraç içermemektedir.
insert @rTable (data) values (ltrim(rtrim(SUBSTRING(@metin,1,LEN(@metin)))))
return
end;

çalışmasını görmek için;
select * from dbo.fn_Split('paylaşımın faydalı olması dileğiyle', N' ')

bir sonraki paylaşımda görüşmek dileğiyle.
Yaşar Şahin

Toplu Sql nesne scriptleri

Merhaba arkadaşlar,

otomatik olarak güncellenmeyen yazılımlar için, benim gibi yazılım desteğinde çalışanların en sık yaptıkları şey veritabanı neslelerini(görünümler, prosedürler, fonksiyonlar gibi) güncellemek olmaktadır. Sürekli değişen prosedürler, görünümler, fonksiyonların, uygulamalarla birlikte güncellenmeleri gerektiği muhakkak. Kaynak veritabanından her nesnenin scriptini alıp bunu hedef veritabanında tek tek çalıştırmak uzun ve dikkat gerektiren bir iştir. çünkü güncellemeyi atladığınız herhangi bir nesne size müşteri tarafından hata bildirimi olarak geri döner. Bu makalemde bu işlemleri kolaylaştırmak adına oluşturduğum bir prosedürü sizinle paylaşmak istiyorum.

Esas olarak bir sql prosedürü olan sp_helptext'in istenen tüm nesleler için çalışmasını sağlayacağız. Prosedür parametre olarak aldığı text içerisinden ,(virgül)'le ayrılan her bir nesne için sp_helptext sonucunu, tek alanı olan geçici tabloya işleyecek ve nihayetinde bu tabloyu döndürecek.

create procedure [dbo].[sp_returnObjectScripts]     
@objName nvarchar(512)  sp
as 
begin 

--bu tabloya sp_helptext sonuçlarını işleyeceğiz.
create table #tmpUpdatedText(clText nvarchar(max) collate database_default) 
 
declare @parobjName nvarchar(64) 
/*kürsör içinde her bir nesne ismini bu değişkene atıp, veritabanında varlığını kontrol eden ve
varsa silen kodu tabloya ekliyoruz.
*/
DECLARE c1 CURSOR FOR
    select data from fn_split(@objName,',') s
    join sys.objects o on o.name = s.data
    --kürsörümüz parametre içindeki tüm nesneler için dönecek. sys.object eşleşmesi ile nesnenin varlığını da kontrol etmiş oluyoruz.
 
    OPEN c1; 
    FETCH NEXT FROM c1 INTO @parobjName;    
 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
   insert #tmpUpdatedText(clText) 
/*kürsör içinde her bir nesne ismini bu değişkene atıp, veritabanında varlığını kontrol eden ve
varsa silen kodu tabloya ekliyoruz.*/  
   select  
'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+  @parobjName + ']'')) ' + CHAR(13) + CHAR(10) +  
'DROP ' +  
   (CASE WHEN type_desc in ('SQL_STORED_PROCEDURE') then 'PROCEDURE ' 
   WHEN type_desc in ('SQL_SCALAR_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION') then 'FUNCTION ' 
   WHEN type_desc in ('VIEW') then 'VIEW ' END) 
   + '[dbo].[' + @parobjName + ']' 
   from sys.objects where name = @parobjName; 
   insert #tmpUpdatedText(clText) 
   values 
   ('GO'); 
--burda da nesneyi sp_helptext prosedürüne parametre olarak verip sonucu yine geçici tabloya yazıyoruz.
   insert #tmpUpdatedText(clText) 
   exec sp_helptext @parobjName; 
   insert #tmpUpdatedText(clText) 
   values 
   ('GO'); 
     FETCH NEXT FROM c1 INTO @parobjName; 
     END; 
 
    CLOSE c1; 
DEALLOCATE c1; 
 
select * from #tmpUpdatedText; 
drop table #tmpUpdatedText;  
end;

böylece güncellemek istediğimiz prosedür view yahut fonksiyonları oluşturduğumuz bu araca parametre olarak verip toplu olarak oluşturma scriptlerine sahip olacağız.

örnek kullanım:
sp_returnObjectScripts 'fn_split,sp_helptext,sp_returnObjectScripts'

bir sonraki paylaşımda görüşmek dileğiyle.
Yaşar Şahin