public List<DashboardEinvoiceMail> GetAllListOutbox(string connectionString)
        {
            using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                List<DashboardEinvoiceMail> DashboardEinvoiceMailList = new List<DashboardEinvoiceMail>();
                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
                {
                    cmd.CommandText = @" DECLARE @VKN NVARCHAR(MAX) = '';
                                         DECLARE @SQL2 NVARCHAR(MAX) = '';
                                         DECLARE @UNVAN NVARCHAR(MAX)='';

                                         DECLARE CRS_AKB CURSOR FOR SELECT VKN FROM Partner
                                         OPEN CRS_AKB
                                         FETCH NEXT FROM CRS_AKB INTO @VKN
                                         WHILE @@FETCH_STATUS = 0
                                         BEGIN

                                         SET @UNVAN = (SELECT TOP(1) UNVAN FROM Partner WHERE VKN = @VKN)
                                         SET @VKN = 'Efatura_' + @VKN

                                         IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = @VKN OR name = @VKN)))
                                         BEGIN

                                         IF @SQL2 != ''
                                         SET @SQL2 = @SQL2 + ' UNION '

                                         SET @SQL2 = @SQL2 + 'SELECT '''+@VKN+''' AS ''GidenŞirket'','''+@UNVAN+''' AS ''GidenÜnvan'',
                                         Year(outb.RecordDate)as GidenFaturaYil,
                                         Month(outb.RecordDate) as GidenFaturaAy,
                                         COUNT(*) AS ''Giden''
                                         FROM '+@VKN+'.dbo.OutboxInvoiceExtend outb
                                         GROUP BY
                                         Year(outb.RecordDate),
                                         Month(outb.RecordDate) '

                                         END
                                         FETCH NEXT FROM CRS_AKB INTO @VKN;
                                         END
                                         CLOSE CRS_AKB;
                                         DEALLOCATE CRS_AKB;
                                         EXEC (@SQL2)";
                    using (DataTable dt = DB_Gateway.ExecuteDataTable(cmd, connection))
                    {
                        if (dt != null)
                        {
                            foreach (DataRow dr in dt.Rows)
                            {
                                DashboardEinvoiceMail objDashboardEinvoiceMail = new DashboardEinvoiceMail();
                                objDashboardEinvoiceMail.TransferToClass(dr);
                                DashboardEinvoiceMailList.Add(objDashboardEinvoiceMail);
                            }
                        }
                    }
                }
                if (connection != null) connection.Close();
                return DashboardEinvoiceMailList;
            }
        }
        public DataTable GetAllListForMailing(SqlConnection connection)
        {
            DataTable objDataTableForMailing = new DataTable();
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
            {
                cmd.CommandText = @" DECLARE @VKN NVARCHAR(MAX) = '';
                                         DECLARE @SQL NVARCHAR(MAX) = '';
                                         DECLARE @UNVAN NVARCHAR(MAX)='';

                                         DECLARE CRS_AKB CURSOR FOR SELECT VKN FROM Partner
                                         OPEN CRS_AKB
                                         FETCH NEXT FROM CRS_AKB INTO @VKN
                                         WHILE @@FETCH_STATUS = 0
                                         BEGIN

                                         SET @UNVAN = (SELECT TOP(1) UNVAN FROM Partner WHERE VKN = @VKN)
                                         SET @VKN = 'Efatura_' + @VKN

                                         IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = @VKN OR name = @VKN)))
                                         BEGIN

                                         IF @SQL != ''
                                         SET @SQL = @SQL + ' UNION '

                                         SET @SQL = @SQL + 'SELECT '''+@VKN+''' AS ''Şirket'','''+@UNVAN+''' AS ''Ünvan'', i.GelenFaturaYil, i.GelenFaturaAy,
                                         o.GidenFaturaYil, o.GidenFaturaAy, i.Gelen AS Gelen, o.Giden AS Giden
                                         from
                                         (Select Year(inb.RecordDate) as GelenFaturaYil,
                                         Month(inb.RecordDate) as GelenFaturaAy,
                                         COUNT(*) AS Gelen
                                         FROM '+@VKN+'.dbo.InboxInvoiceExtend inb
                                         GROUP BY
                                         Year(inb.RecordDate),
                                         Month(inb.RecordDate) ) i

                                         FULL OUTER JOIN (Select Year(outb.RecordDate)as GidenFaturaYil,
                                         Month(outb.RecordDate) as GidenFaturaAy,
                                         COUNT(*) AS Giden
                                         FROM '+@VKN+'.dbo.OutboxInvoiceExtend outb
                                         GROUP BY
                                         Year(outb.RecordDate),
                                         Month(outb.RecordDate) ) o
                                         ON i.GelenFaturaYil = o.GidenFaturaYil
                                         and i.GelenFaturaAy = o.GidenFaturaAy  '

                                         END

                                         FETCH NEXT FROM CRS_AKB INTO @VKN;
                                         END
                                         CLOSE CRS_AKB;
                                         DEALLOCATE CRS_AKB;
                                         EXEC (@SQL)";
                using (DataTable dt = DB_Gateway.ExecuteDataTable(cmd, connection))
                {
                    if (dt != null)
                    {
                        objDataTableForMailing.Columns.Add("Şirket");
                        objDataTableForMailing.Columns.Add("Ünvan");
                        objDataTableForMailing.Columns.Add("Yil");
                        objDataTableForMailing.Columns.Add("Ay");
                        objDataTableForMailing.Columns.Add("Gelen");
                        objDataTableForMailing.Columns.Add("Giden");
                        objDataTableForMailing.Columns.Add("Toplam");

                        foreach (DataRow dr in dt.Rows)
                        {
                            DashboardEinvoiceMail objDashboardEinvoiceMail = new DashboardEinvoiceMail();
                            objDashboardEinvoiceMail.TransferToClass(dr);
                            DataRow dri = objDataTableForMailing.NewRow();
                            dri["Şirket"] = objDashboardEinvoiceMail.m_Şirket;
                            dri["Ünvan"] = objDashboardEinvoiceMail.m_Ünvan;
                            dri["Yil"] = objDashboardEinvoiceMail.m_GelenFaturaYil;
                            dri["Ay"] = objDashboardEinvoiceMail.m_GelenFaturaAy;
                            dri["Gelen"] = objDashboardEinvoiceMail.m_Gelen;
                            dri["Giden"] = objDashboardEinvoiceMail.m_Giden;
                            dri["Toplam"] = objDashboardEinvoiceMail.m_Toplam;
                            objDataTableForMailing.Rows.Add(dri);
                        }
                        connection.Close();
                    }
                }
                //return DB_Gateway.ExecuteDataTable(cmd, connection);
                return objDataTableForMailing;
            }
        }