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; } }
// ***************************************************************************************** // Raporu Mail Gönder // ***************************************************************************************** protected void btnSendMail_Click(object sender, EventArgs e) { // DataTable ile Excele Aktarma DashboardEinvoiceMail objInvoiceReport = new DashboardEinvoiceMail(); DataTable dtInvoiceReport = objInvoiceReport.GetAllListForMailing(new SqlConnection(LiveOrganizationConnectionString)); if (dtInvoiceReport.Rows.Count > 0) { ExportExcel(dtInvoiceReport, "Fatura_Raporu"); } }
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; } }
// ***************************************************************************************** // Gelen Fatura Raporu // ***************************************************************************************** protected void btnGetInvoice_Click(object sender, EventArgs e) { try { DashboardEinvoiceMail objInvoiceReport = new DashboardEinvoiceMail(); List<DashboardEinvoiceMail> dtInvoiceReport = objInvoiceReport.GetAllList(LiveOrganizationConnectionString); if (!Object.Equals(dtInvoiceReport, null) && dtInvoiceReport.Count > 0) { this.rptInvoiceReport.DataSource = dtInvoiceReport; this.rptInvoiceReport.DataBind(); } else { this.rptInvoiceReport.DataSource = null; this.rptInvoiceReport.DataBind(); } } catch (Exception ex) { this.InboxReportFaultMessage.Visible = true; this.lblInboxAlertMailMessage.Text = ex.Message.ToString(); } }