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