private void btnViewReport_Click(object sender, EventArgs e)
{
//if no customer selected, alert and return
if (cboCustomer.SelectedIndex < 0)
{
MessageBox.Show("Please select customer.");
return;
}
//prepare parameter data
DateTime startDate = new DateTime((int)spnYear.Value, cboMonth.SelectedIndex + 1, 1);
int endMonth = cboMonth.SelectedIndex + 2;
int endYear = (int)spnYear.Value;
if (endMonth > 12)
{
endMonth = endMonth - 12;
endYear++;
}
DateTime endDate = new DateTime(endYear, endMonth, 1);
string customerId = cboCustomer.SelectedValue.ToString();
//build query
string query = "SELECT ROW_NUMBER() over (ORDER BY ct.tgl_transaksi) AS RowNo, " +
"ct.id, ct.customer_id AS CustomerId, c.customer_name AS CustomerName, " +
" ct.tgl_transaksi AS TanggalTransaksi, ctd.stuffing_date AS StuffingDate, " +
" w.address AS stuffingplace, des.city_name AS Tujuan, " +
" ctd.no_container AS ContainerNo, ctd.voy, ctd.td, " +
" pd.vessel_name AS VesselName, ctd.ta, ctd.unloading, " +
" ctd.terima_toko AS DateReceived, r.recipient_name AS Recipient " +
"FROM CUSTOMER_TRANS AS ct " +
" INNER JOIN CUSTOMER AS c ON c.customer_id = ct.customer_id " +
" LEFT OUTER JOIN CUSTOMER_TRANS_DETAIL AS ctd ON ctd.customer_trans_id = ct.id " +
" LEFT OUTER JOIN WAREHOUSE AS w ON w.stuffing_place_id = ctd.stuffing_place " +
" LEFT OUTER JOIN PELAYARAN_DETAIL AS pd ON pd.pelayaran_detail_id = ctd.pelayaran_detail_id " +
" LEFT OUTER JOIN CITY AS des ON des.city_id = ctd.destination " +
" LEFT OUTER JOIN RECIPIENT AS r ON r.recipient_id = ctd.recipient_id " +
"WHERE ct.tgl_transaksi >= '" + startDate + "' " +
"AND ct.tgl_transaksi < '" + endDate + "' " +
"AND ct.customer_id = '" + customerId + "' ";
rptDelivery = new RptDelivery();
SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
try
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(query, VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
ShippingMainDataSet ds = new ShippingMainDataSet();
da.Fill(ds, "DELIVERY");
//DataTable dt = ds.Tables["DELIVERY"];
//MessageBox.Show(dt.Rows.Count.ToString());
ParameterFields pluralParameter = new ParameterFields();
ParameterField singleParameter;
ParameterDiscreteValue parameterDiscreteValue;
singleParameter = new ParameterField();
singleParameter.Name = "strBulanTransaksi";
parameterDiscreteValue = new ParameterDiscreteValue();
parameterDiscreteValue.Value = cboMonth.Text + " " + spnYear.Value.ToString();
singleParameter.CurrentValues.Add(parameterDiscreteValue);
pluralParameter.Add(singleParameter);
reportViewer.ParameterFieldInfo = pluralParameter;
rptDelivery.SetDataSource(ds);
reportViewer.ReportSource = rptDelivery;
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.Message);
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message);
}
finally
{
con.Close();
}
}