private void btnPrint_Click(object sender, EventArgs e) { try { string exeFolder = Application.StartupPath; string reportPath = Path.Combine(exeFolder, @"Reports\RevenueRpt.rdlc"); // using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ATVEntities"].ConnectionString)) { SqlDataAdapter da = null; try { string query = ""; if (byMonth) { query = "select ct.Code, ct.CustomerName, sum(pd.Cost)AS Cost, sum(pd.Cost) - (sum(pd.Cost) * ct.Discount / 100) AS TotalCost " + "from ProductScheduleShow pd inner join ContractItem cti on pd.ContractDetailId = cti.Id " + "inner join [Contract] ct on ct.Code = cti.ContractCode " + "where YEAR(ShowDate) = @rptYear and MONTH(ShowDate) = @rptMonth group by ct.Code, ct.CustomerName, ct.Discount"; } if (!byMonth) { query = "select ct.Code, ct.CustomerName, sum(pd.Cost)AS Cost, sum(pd.Cost) - (sum(pd.Cost) * ct.Discount / 100) AS TotalCost " + "from ProductScheduleShow pd inner join ContractItem cti on pd.ContractDetailId = cti.Id " + "inner join [Contract] ct on ct.Code = cti.ContractCode " + "where YEAR(ShowDate) = @rptYear and (MONTH(ShowDate) in ( "; switch (cboPeriod.SelectedValue) { case 1: query += "1, 2, 3"; break; case 2: query += "4, 5, 6"; break; case 3: query += "7, 8, 9"; break; case 4: query += "10, 11, 12"; break; } query += " )) group by ct.Code, ct.CustomerName, ct.Discount"; } var cmd = new SqlCommand(query, con); cmd.Parameters.Add(new SqlParameter("@rptYear", this.dtpFromMonth.Value.Year)); if (byMonth) { cmd.Parameters.Add(new SqlParameter("@rptMonth", this.dtpFromMonth.Value.Month)); } da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); List <ViewModel.RevenueRM> reportData = Utilities.ConvertDataTable <ViewModel.RevenueRM>(dt); DateTime today = Utilities.GetServerDateTimeNow(); string strToday = Utilities.DateToFormatVNDate(today); var totalCost = (decimal)reportData.Sum(r => r.Cost); string strTotal = MoneyToText.NumberToTextVN(totalCost); ReportParameterCollection reportParameters = new ReportParameterCollection(); reportParameters.Add(new ReportParameter("strMonthYear", this.dtpFromMonth.Value.Month + "/" + this.dtpFromMonth.Value.Year)); reportParameters.Add(new ReportParameter("strReportDate", strToday)); reportParameters.Add(new ReportParameter("strTotal", strTotal)); rptViewer.LocalReport.ReportPath = reportPath; rptViewer.LocalReport.DataSources.Clear(); rptViewer.LocalReport.SetParameters(reportParameters); rptViewer.LocalReport.DataSources.Add(new ReportDataSource("dsRevenues", dt)); rptViewer.RefreshReport(); Logging.LogBusiness(string.Format("{0} {1} {2}", Common.Session.GetUserName(), Common.Constants.LogAction.ExportData, "báo cáo doanh thu"), Common.Constants.BusinessLogType.ExportData); } catch (Exception) { throw; } finally { if (da != null) { da.Dispose(); } } }; } catch (Exception ex) { throw; } }
private void btnPrint_Click(object sender, EventArgs e) { try { string exeFolder = Application.StartupPath; string reportPath = Path.Combine(exeFolder, @"Reports\LiabilitiesReport.rdlc"); // using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ATVEntities"].ConnectionString)) { SqlDataAdapter da = null; try { string query = "SELECT ci.ProductName, ps.TimeSlot, ps.TimeSlotCode, ps.ShowTime, ps.TimeSlotLength, ps.Quantity, ps.Cost, ps.Cost * ps.Quantity as TotalCost, ct.Discount, ps.Cost * ps.Quantity - (ps.Cost * ps.Quantity * ct.Discount / 100) as FinalCost "+ "FROM ContractItem ci INNER JOIN ( " + "SELECT ContractDetailId, TimeSlot, TimeSlotCode, ShowTime, TimeSlotLength, Cost, COUNt(Id) as Quantity " + "FROM ProductScheduleShow " + "WHERE YEAR(ShowDate) = @rptYear " + "AND MONTH(ShowDate) = @rptMonth " + "GROUP BY ContractDetailId, TimeSlot, TimeSlotCode, ShowTime, TimeSlotLength, Cost) ps on ci.Id = ps.ContractDetailId " + "INNER JOIN[Contract] ct on ci.ContractCode = ct.Code " + "WHERE ct.CustomerCode = @rptCustomerCode AND ci.StatusId = " + Constants.CommonStatus.ACTIVE + " " + "GROUP BY ci.ProductName, ps.TimeSlot, ps.TimeSlotCode, ps.ShowTime, ps.TimeSlotLength, ps.Cost, ci.TotalCost, ct.Discount, ct.Cost, ps.Quantity"; var cmd = new SqlCommand(query, con); cmd.Parameters.Add(new SqlParameter("@rptYear", this.dtpMonth.Value.Year)); cmd.Parameters.Add(new SqlParameter("@rptMonth", this.dtpMonth.Value.Month)); cmd.Parameters.Add(new SqlParameter("@rptCustomerCode", cboCustomer.SelectedValue)); da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); List <ViewModel.LiabilitiesRM> reportData = Utilities.ConvertDataTable <ViewModel.LiabilitiesRM>(dt); DateTime today = Utilities.GetServerDateTimeNow(); string strToday = Utilities.DateToFormatVNDate(today); var firstDayOfMonth = new DateTime(this.dtpMonth.Value.Year, this.dtpMonth.Value.Month, 1); var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1); string customerName = new CustomerService().GetNameByCode(cboCustomer.SelectedValue.ToString()); var finalCost = (decimal)reportData.Sum(r => r.FinalCost); string strTotal = MoneyToText.NumberToTextVN(finalCost); ReportParameterCollection reportParameters = new ReportParameterCollection(); reportParameters.Add(new ReportParameter("strMonthYear", this.dtpMonth.Value.Month + "/" + this.dtpMonth.Value.Year)); reportParameters.Add(new ReportParameter("strReportFromDate", firstDayOfMonth.ToShortDateString())); reportParameters.Add(new ReportParameter("strReportToDate", lastDayOfMonth.ToShortDateString())); reportParameters.Add(new ReportParameter("strTotal", strTotal)); reportParameters.Add(new ReportParameter("strCustomerName", customerName)); rptViewer.LocalReport.ReportPath = reportPath; rptViewer.LocalReport.DataSources.Clear(); rptViewer.LocalReport.SetParameters(reportParameters); rptViewer.LocalReport.DataSources.Add(new ReportDataSource("dsLiabilities", dt)); rptViewer.RefreshReport(); Logging.LogBusiness(string.Format("{0} {1} {2}", Common.Session.GetUserName(), Common.Constants.LogAction.ExportData, "báo cáo công nợ của khách hàng " + cboCustomer.SelectedValue.ToString() + " trong " + this.dtpMonth.Value.Month + "/" + this.dtpMonth.Value.Year), Common.Constants.BusinessLogType.ExportData); } catch (Exception) { throw; } finally { if (da != null) { da.Dispose(); } } }; } catch (Exception ex) { throw; } }