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;
}
}