private void radButtonElementBtnSearch_Click(object sender, EventArgs e)
{
if (Utility.Utility.ConvertStringToDate(Utility.Utility.ChangeDateMMDD(rbBeginDate.Text)) > Utility.Utility.ConvertStringToDate(Utility.Utility.ChangeDateMMDD(rbEndDate.Text)))
{
MessageBox.Show(this, "End date must be greather than begin date", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else {
List<ReportSchedule> listReportSchedules = GetReportSchedule(rbBeginDate.Text, rbEndDate.Text);
if (!Constant.VisitaJayaPerkasaApplication.anyConnection)
MessageBox.Show(this, "Please check your connection", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
else if (listReportSchedules != null) {
ShippingMainDataSet ds = new ShippingMainDataSet();
rptSchedule = new rptSchedule();
SetParameter();
while(ds.Schedules.Rows.Count < listReportSchedules.Count)
ds.Schedules.Rows.Add();
for (int i = 0; i < listReportSchedules.Count; i++)
{
if (i > 0) {
ReportSchedule obj0 = listReportSchedules.ElementAt(i - 1);
ReportSchedule obj1 = listReportSchedules.ElementAt(i);
if (obj0.tujuan.Equals(obj1.tujuan))
listReportSchedules.ElementAt(i).tujuan = "";
if (obj0.pelayaran.Equals(obj1.pelayaran) && listReportSchedules.ElementAt(i).tujuan.Equals(""))
listReportSchedules.ElementAt(i).pelayaran = "";
obj0 = null;
obj1 = null;
}
}
for (int i = 0; i < listReportSchedules.Count; i++)
{
ReportSchedule obj = listReportSchedules.ElementAt(i);
ds.Schedules.Rows[i][0] = obj.tujuan;
ds.Schedules.Rows[i][1] = obj.pelayaran;
ds.Schedules.Rows[i][2] = obj.vessel;
ds.Schedules.Rows[i][3] = obj.voy;
ds.Schedules.Rows[i][4] = obj.tglClosing;
ds.Schedules.Rows[i][5] = obj.etd;
ds.Schedules.Rows[i][6] = "";
ds.Schedules.Rows[i][7] = "";
obj = null;
}
rptSchedule.SetDataSource(ds);
crystalReportViewer1.ReportSource = rptSchedule;
listReportSchedules = null;
}
else
MessageBox.Show(this, "No data found!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public List<ReportSchedule> GetReportSchedule(string beginDate, string endDate)
{
List<ReportSchedule> listReportSchedule = null;
try
{
using (SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString))
{
con.Open();
using (SqlCommand command = new SqlCommand(
"select 'Jakarta - ' + c.city_name as tujuan," +
"( " +
" select TOP 1 supplier_name FROM pelayaran_detail pd JOIN pelayaran p " +
" ON p.pelayaran_id = pd.pelayaran_id " +
" AND pd.pelayaran_detail_id = s.pelayaran_detail_id " +
" JOIN supplier s " +
" ON s.supplier_id = p.supplier_id " +
") as pelayaran, " +
"(select TOP 1 vessel_name FROM pelayaran_detail pd WHERE pd.pelayaran_detail_id = s.pelayaran_detail_id) as vessel, " +
"s.voy, s.tgl_closing, s.etd " +
"FROM schedule s, city c " +
"WHERE ta is null AND s.tujuan = c.city_id AND " +
"(cast(s.etd as date) >= cast('" + beginDate + "' as date) " +
"AND cast(s.etd as date) <= cast('" + endDate + "' as date)) " +
"order by tujuan, pelayaran, vessel"
, con))
{
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ReportSchedule rptSchedule = new ReportSchedule();
rptSchedule.tujuan = reader.GetString(0);
rptSchedule.pelayaran = reader.GetString(1);
rptSchedule.vessel = reader.GetString(2);
rptSchedule.voy = reader.GetString(3);
rptSchedule.tglClosing = Utility.Utility.GetDateOnly(reader.GetDateTime(4).ToString());
rptSchedule.etd = Utility.Utility.GetDateOnly(reader.GetDateTime(5).ToString());
if (listReportSchedule == null)
listReportSchedule = new List<ReportSchedule>();
listReportSchedule.Add(rptSchedule);
rptSchedule = null;
}
}
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.Message);
}
catch (Exception Ex)
{
Logging.Error("RptScheduleControl.cs - GetReportSchedule() " + Ex.Message);
MessageBox.Show(Ex.Message);
}
return listReportSchedule;
}
public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport() {
rptSchedule rpt = new rptSchedule();
rpt.Site = this.Site;
return rpt;
}
