private void btnViewReport_Click(object sender, EventArgs e)
{
if (cbVessel.Text.Equals(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.cboDefaultText))
MessageBox.Show(this, "Please choose vessel", "Information");
else if(etVoy.Text.Trim().Length == 0)
MessageBox.Show(this, "Please fill voy", "Information");
else if (pickerATD.Value.Date > DateTime.Now.Date)
MessageBox.Show(this, "ATD must be lower from today", "Information");
else
{
string query = CreateSelectQueryAndParameters();
if (!query.Contains("Column"))
{
MessageBox.Show("No selection to display!");
return;
}
objReportContainer = new RptContainer();
try
{
SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
SqlDataAdapter da = new SqlDataAdapter(query, VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
ShippingMainDataSet ds = new ShippingMainDataSet();
da.Fill(ds, "CONTAINER");
objReportContainer.SetDataSource(ds);
crystalReportViewerContainer.ReportSource = objReportContainer;
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.Message);
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message);
}
}
}
public DataTable ReportInvoice(Guid ID)
{
DataTable dt = null;
SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
try
{
Constant.VisitaJayaPerkasaApplication.anyConnection = false;
con.Open();
Constant.VisitaJayaPerkasaApplication.anyConnection = true;
string strQry = "Select ctd.no_ba, ctd.no_container, tc.type_name, pd.vessel_name, ctd.ta, ctd.terima_toko, cd.city_name, ctd.price " +
"FROM [Customer_Trans_Detail] ctd " +
"INNER JOIN [Type_Cont] tc ON tc.type_id = ctd.type_id " +
"INNER JOIN [Pelayaran_Detail] pd ON pd.pelayaran_detail_id = ctd.pelayaran_detail_id " +
"INNER JOIN [Pelayaran] p ON p.pelayaran_id = pd.pelayaran_id " +
"INNER JOIN [City] cd ON cd.city_id = ctd.destination " +
"Where ctd.customer_trans_id = '" + ID + "'";
SqlDataAdapter da = new SqlDataAdapter(strQry, VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
ShippingMainDataSet ds = new ShippingMainDataSet();
da.Fill(ds, "INVOICE_NONPPN");
dt = ds.Tables[5];
}
catch (Exception e)
{
Logging.Error("SqlCustomerTransRepository.cs - ReportCustomerTransDetail() " + e.Message);
}
con.Close();
return dt;
}
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 DataTable ReportCustomerTransDetail(Guid ID)
{
DataTable dt = null;
SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
try
{
Constant.VisitaJayaPerkasaApplication.anyConnection = false;
con.Open();
Constant.VisitaJayaPerkasaApplication.anyConnection = true;
string strQry = "Select tc.type_name, pd.vessel_name, co.city_name as origin, cd.city_name as destination, " +
"cnd.condition_name as condition, ctd.no_seal, ctd.truck_number, ctd.voy, " +
"ctd.stuffing_date, ctd.stuffing_place, ctd.etd, ctd.td, ctd.eta, ctd.ta, ctd.unloading, ctd.price, " +
"r.recipient_name, ctd.jenis_barang, ctd.no_container, ctd.quantity " +
"ctd.sj1, ctd.sj2, ctd.sj3, ctd.sj4, ctd.sj5, ctd.sj6, ctd.sj7, ctd.sj8, ctd.sj9, ctd.sj10, " +
"ctd.sj11, ctd.sj12, ctd.sj13, ctd.sj14, ctd.sj15, ctd.sj16, ctd.sj17, ctd.sj18, ctd.sj19, ctd.sj20, " +
"ctd.sj21, ctd.sj22, ctd.sj23, ctd.sj24, ctd.sj25, ctd.terima_toko, ctd.keterangan, ctd.no_ba " +
"FROM [Customer_Trans_Detail] ctd " +
"INNER JOIN [Type_Cont] tc ON tc.type_id = ctd.type_id " +
"INNER JOIN [Pelayaran_Detail] pd ON pd.pelayaran_detail_id = ctd.pelayaran_detail_id " +
"INNER JOIN [Pelayaran] p ON p.pelayaran_id = pd.pelayaran_id " +
"INNER JOIN [City] co ON co.city_id = ctd.origin " +
"INNER JOIN [City] cd ON cd.city_id = ctd.destination " +
"INNER JOIN [Condition] cnd ON cnd.condition_id = ctd.condition_id " +
"INNER JOIN [RECIPIENT] r ON r.recipient_id = ctd.recipient_id " +
"Where (ctd.deleted is null OR ctd.deleted = '0') " +
"AND ctd.customer_trans_id = '" + ID + "'";
SqlDataAdapter da = new SqlDataAdapter(strQry, VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
ShippingMainDataSet ds = new ShippingMainDataSet();
da.Fill(ds, "REPORT_CUST");
dt = ds.Tables[3];
}
catch (Exception e)
{
Logging.Error("SqlCustomerTransRepository.cs - ReportCustomerTransDetail() " + e.Message);
}
con.Close();
return dt;
}
private void btnShowReport_Click(object sender, EventArgs e)
{
objRpt = new RptCustomerTrans();
string query = CreateSelectQueryAndParameters();
if (!query.Contains("Column"))
{
MessageBox.Show("No selection to display!");
return;
}
try
{
SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
SqlDataAdapter da = new SqlDataAdapter(query, VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
ShippingMainDataSet ds = new ShippingMainDataSet();
da.Fill(ds, "CUSTOMER_TRANS");
objRpt.SetDataSource(ds);
crystalReportViewer1.ReportSource = objRpt;
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.Message);
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message);
}
}
public PrintLeadTime(Surat objSurat)
{
InitializeComponent();
this.objSurat = objSurat;
sqlCustomerTransRepository = new SqlCustomerTransRepository();
reportDocument = new ReportDocument();
paramFields = new ParameterFields();
crtLeadTime = new CrtLeadTime();
//Execute Query
String strQry = "Select [CUSTOMER_TRANS_DETAIL].no_container as Column1, " +
"(Select Top 1 address From WareHouse Where stuffing_place_id = stuffing_place) as Column2, " +
"[CUSTOMER_TRANS_DETAIL].terima_toko as Column3 From [CUSTOMER_TRANS_DETAIL] " +
"Join [Customer_Trans] On " +
"[Customer_Trans].tgl_transaksi = cast('" + Utility.Utility.ConvertDateToString(objSurat.Tgl) + "' as date) And " +
"[Customer_Trans].Customer_id = '" + objSurat.CustomerID + "' And " +
"([Customer_Trans].deleted is not null Or [Customer_Trans].deleted = 0) " +
"And [Customer_Trans].id = [Customer_Trans_Detail].customer_trans_id";
int columnNo = 1;
paramField = new ParameterField();
paramField.Name = "col" + columnNo.ToString();
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = "Container";
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
columnNo++;
paramField = new ParameterField();
paramField.Name = "col" + columnNo.ToString();
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = "Tgl penerimaan Container";
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
columnNo++;
paramField = new ParameterField();
paramField.Name = "col" + columnNo.ToString();
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = "Gudang";
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
paramField = new ParameterField();
paramField.Name = "noSurat";
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = objSurat.NoSurat;
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
paramField = new ParameterField();
paramField.Name = "parameterReceive";
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = objSurat.CustomerName;
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
crystalReportViewer1.ParameterFieldInfo = paramFields;
SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
SqlDataAdapter da = new SqlDataAdapter(strQry, VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString);
ShippingMainDataSet ds = new ShippingMainDataSet();
da.Fill(ds, "Surat_Lead_Time");
crtLeadTime.SetDataSource(ds);
crystalReportViewer1.ReportSource = crtLeadTime;
}
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();
}
}
private void radButton1_Click(object sender, EventArgs e)
{
// Set path report.
string strPath = String.Empty;
if (chkStatus.Checked)
strPath = Application.StartupPath + @"\Form\Report\Invoice\RptInvoice.rpt";
else
strPath = Application.StartupPath + @"\Form\Report\Invoice\RptInvoicePPN.rpt";
// Object for load report.
ReportDocument rpt = new ReportDocument();
rpt.Load(strPath);
string query = "SELECT ROW_NUMBER() over (ORDER BY ct.tgl_transaksi) AS no_urut, " +
" ctd.no_ba, ctd.no_container, tcnt.type_name, pd.vessel_name, ctd.ta, " +
" ctd.terima_toko, des.city_name, ctd.price, ctd.unloading AS tgl_angkat, " +
" ctd.no_seal AS seal, ctd.voy " +
"FROM CUSTOMER_TRANS ct " +
" INNER JOIN CUSTOMER c ON c.customer_id = ct.customer_id " +
" LEFT OUTER JOIN CUSTOMER_TRANS_DETAIL ctd ON ctd.customer_trans_id = ct.id " +
" LEFT OUTER JOIN TYPE_CONT tcnt ON tcnt.type_id = ctd.type_id " +
" 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 " +
"WHERE ct.id = '" + searchResultCustomerTrans.CustomerTransID.ToString() + "'" +
"";
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, "INVOICE_NONPPN");
ParameterFields pluralParameter = new ParameterFields();
ParameterField singleParameter;
ParameterDiscreteValue parameterDiscreteValue;
singleParameter = new ParameterField();
singleParameter.Name = "userName";
parameterDiscreteValue = new ParameterDiscreteValue();
parameterDiscreteValue.Value = userName;
singleParameter.CurrentValues.Add(parameterDiscreteValue);
pluralParameter.Add(singleParameter);
singleParameter = new ParameterField();
singleParameter.Name = "customerName";
parameterDiscreteValue = new ParameterDiscreteValue();
parameterDiscreteValue.Value = customerName;
singleParameter.CurrentValues.Add(parameterDiscreteValue);
pluralParameter.Add(singleParameter);
singleParameter = new ParameterField();
singleParameter.Name = "invoiceNo";
parameterDiscreteValue = new ParameterDiscreteValue();
parameterDiscreteValue.Value = invoiceNo;
singleParameter.CurrentValues.Add(parameterDiscreteValue);
pluralParameter.Add(singleParameter);
DataTable dt = ds.Tables["INVOICE_NONPPN"];
//MessageBox.Show(dt.Rows.Count.ToString());
decimal total = 0;
foreach (DataRow row in dt.Rows)
{
if (row[7].ToString() != "")
total += Convert.ToDecimal(row[7].ToString());
//total += 100000;//dr.
}
singleParameter = new ParameterField();
singleParameter.Name = "totalTagihan";
parameterDiscreteValue = new ParameterDiscreteValue();
parameterDiscreteValue.Value = total;
singleParameter.CurrentValues.Add(parameterDiscreteValue);
pluralParameter.Add(singleParameter);
if (!chkStatus.Checked)
{
singleParameter = new ParameterField();
singleParameter.Name = "ppn";
parameterDiscreteValue = new ParameterDiscreteValue();
parameterDiscreteValue.Value = total * (decimal)0.1;
singleParameter.CurrentValues.Add(parameterDiscreteValue);
pluralParameter.Add(singleParameter);
singleParameter = new ParameterField();
singleParameter.Name = "grandTotal";
parameterDiscreteValue = new ParameterDiscreteValue();
parameterDiscreteValue.Value = total * (decimal)1.1;
singleParameter.CurrentValues.Add(parameterDiscreteValue);
pluralParameter.Add(singleParameter);
}
crystalReportViewerContainer.ParameterFieldInfo = pluralParameter;
rpt.SetDataSource(ds);
crystalReportViewerContainer.ReportSource = rpt;
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.Message);
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message);
}
finally
{
con.Close();
}
}
