private void WithoutSD()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Edit(@"UPDATE tblsd SET DocReceiveNDC='" + dtDocRecNDC.Text + "',ReleaseSD=NULL ,SDNumber=NULL,Remarks='" + txtRemark.Text + "' WHERE TripNum='" + int.Parse(lblTrip.Text) + "'");
Obj.CloseConnection();
frm1.RefreshRDD();
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ClearGarInfo()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Edit(@"UPDATE tblDocu SET GarageIn=NULL,GarageOut=NULL WHERE TripNum='" + int.Parse(lblTrip.Text) + "'");
Obj.CloseConnection();
frm1.RefreshRDD();
MessageBox.Show("Success!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ClearTableDocu()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Edit(@"UPDATE tblDocu SET CustomerIn=NULL,StartUnload=NULL,FinishUnload=NULL,ReleaseDoc=NULL,CustomerOut=NULL,CDwell=NULL,TruckAtGarage=NULL,OfficeReceive=NULL,DocTransmit=NULL,Remarks=NULL WHERE TripNum='" + int.Parse(lblTrip.Text) + "'");
Obj.CloseConnection();
frm1.RefreshRDD();
MessageBox.Show("Success!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void UpdateUnload()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Edit(@"UPDATE tblDocu SET CustomerIn='" + dtCustIn.Text + "',StartUnload='" + dtSU.Text + "',FinishUnload='" + dtFU.Text + "',ReleaseDoc='" + dtRDoc.Text + "',CustomerOut='" + dtCustOut.Text + "',CDwell='" + txtDwell.Text + "'WHERE TripNum='" + int.Parse(lblTrip.Text) + "'");
Obj.CloseConnection();
frm1.RefreshRDD();
MessageBox.Show("Success!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ClearTableSD()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Edit(@"UPDATE tblsd SET DocReceiveNDC=NULL,ReleaseSD=NULL,SDNumber=NULL,Remarks=NULL WHERE TripNum='" + int.Parse(lblTrip.Text) + "'");
Obj.CloseConnection();
frm1.RefreshRDD();
this.Close();
MessageBox.Show("Success!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void UpdateTableDocu()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Edit(@"UPDATE tblDocu SET TruckAtGarage='" + dtTruckGar.Text + "',OfficeReceive='" + dtDocRec.Text + "',DocTransmit='" + dtDocTrans.Text + "',Remarks='" + txtRemarks.Text + "' WHERE TripNum='" + int.Parse(lblTrip.Text) + "'");
Obj.CloseConnection();
frm1.RefreshRDD();
this.Close();
MessageBox.Show("Success!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
Obj.Connection();
if (txtBodyNum.Text != string.Empty &&
txtPlateNum.Text != string.Empty &&
cbType.Text != string.Empty &&
cbGar.Text != string.Empty)
{
if (ctr == 1)
{
try
{
Obj.Insert(@"INSERT INTO tbltruck (BodyNum,PlateNum,Type,BGar) VALUES ('" + txtBodyNum.Text + "','" + txtPlateNum.Text + "','" + cbType.Text + "','" + cbGar.Text + "')");
Obj.CloseConnection();
MessageBox.Show("Done!", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information);
frm1.FillListView();
ctr = 0;
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else if (ctr == 2)
{
try
{
Obj.Edit(@"UPDATE tbltruck SET BodyNum='" + txtBodyNum.Text + "',PlateNum='" + txtPlateNum.Text + "',Type='" + cbType.Text + "',BGar='" + cbGar.Text + "' WHERE BodyNum = '" + int.Parse(oldb) + "'");
Obj.CloseConnection();
MessageBox.Show("Done!", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information);
frm1.FillListView();
ctr = 0;
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
private void UpdateTableNDC()
{
string query = @"UPDATE tblndc SET DispDate='" + dtDD.Text + "',Waybill='" + txtWaybill.Text + "',BodyNum='" + txtBodyNum.Text + "',PlateNum='" + txtPlateNum.Text + "',Driver='" + txtDriver.Text + "',Helper='" + txtHelper.Text + "',Source='" + txtSource.Text + "',RDD='" +
dtRDD.Text + "',CommitTime='" + dtComTime.Text + "',Cases='" + txtCase.Text + "',TruckReq='" + txtTruckReq.Text + "',CustName='" + txtCustName.Text + "',TruckIn='" + dtTruckIn.Text + "',Checklist='" + dtChecklist.Text + "',SCheck='" + dtSCheck.Text + "',FCheck='" + dtFCheck.Text + "',SLoad='" + dtSLoad.Text + "',FLoad='" +
dtFLoad.Text + "',DocRec='" + dtDocRec.Text + "',INVCDN='" + txtINV.Text + "',TruckOut='" + dtTruckOut.Text + "',SDwell='" + txtDwell.Text + "',LoadNum='" + txtLN.Text + "' WHERE TripNum='" + int.Parse(lblTrip.Text) + "'";
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Edit(query);
frm1.RefreshRDD();
MessageBox.Show("Edit Success!", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information);
Obj.CloseConnection();
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void insertWB()
{
if (String.IsNullOrWhiteSpace(txtWaybill.Text))
{
MessageBox.Show("Please enter correct Waybill number", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Edit(@"UPDATE tblndc SET Waybill='" + txtWaybill.Text + "' WHERE TripNum='" + int.Parse(lblTrip.Text) + "'");
Obj.CloseConnection();
frm1.RefreshRDD();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void UpdateTableSD()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
if (txtSD.Text != string.Empty)
{
try
{
Obj.Edit(@"UPDATE tblsd SET DocReceiveNDC='" + dtDocRecNDC.Text + "',ReleaseSD='" + dtSDRelease.Text + "',SDNumber='" + txtSD.Text + "',Remarks='" + txtRemark.Text + "' WHERE TripNum='" + int.Parse(lblTrip.Text) + "'");
Obj.CloseConnection();
frm1.RefreshRDD();
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Please Enter SD Number", "", MessageBoxButtons.OK);
}
}
private void uploadToDatabaseToolStripMenuItem_Click(object sender, EventArgs e)
{
DatabaseLibrary Obj = new DatabaseLibrary();
if (listView1.Items.Count == 0)
{
MessageBox.Show("Please Add Details!");
}
else if (listView1.Items.Count != 0)
{
DialogResult dr = MessageBox.Show(this, "Do you want to Save?", "Confimation", MessageBoxButtons.YesNo, MessageBoxIcon.Stop);
if (dr == DialogResult.Yes)
{
try
{
for (int i = 0; i < listView1.Items.Count; i++)
{
try
{
Obj.Connection();
if (listView1.Items[i].SubItems[9].Text != string.Empty &&
listView1.Items[i].SubItems[10].Text != string.Empty)
{
Obj.Insert(@"INSERT INTO tbldocu(TripNum,GarageIn,GarageOut,CustomerIn,StartUnload,FinishUnload,ReleaseDoc,CustomerOut,CDwell,TruckAtGarage,OfficeReceive,DocTransmit,Remarks) VALUES
('" + listView1.Items[i].SubItems[0].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[9].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[10].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[11].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
DateTime.Parse(listView1.Items[i].SubItems[12].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[13].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[14].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[15].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
listView1.Items[i].SubItems[16].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[17].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[18].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[19].Text).ToString("yyyy-MM-dd HH:mm") + "','" + listView1.Items[i].SubItems[20].Text + "') ON DUPLICATE KEY UPDATE GarageIn = '"
+ DateTime.Parse(listView1.Items[i].SubItems[9].Text).ToString("yyyy-MM-dd HH:mm") + "',GarageOut = '" + DateTime.Parse(listView1.Items[i].SubItems[10].Text).ToString("yyyy-MM-dd HH:mm") + "',CustomerIn = '" + DateTime.Parse(listView1.Items[i].SubItems[11].Text).ToString("yyyy-MM-dd HH:mm") + "',StartUnload = '" + DateTime.Parse(listView1.Items[i].SubItems[12].Text).ToString("yyyy-MM-dd HH:mm") + "',FinishUnload = '"
+ DateTime.Parse(listView1.Items[i].SubItems[13].Text).ToString("yyyy-MM-dd HH:mm") + "',ReleaseDoc = '" + DateTime.Parse(listView1.Items[i].SubItems[14].Text).ToString("yyyy-MM-dd HH:mm") + "',CustomerOut = '" + DateTime.Parse(listView1.Items[i].SubItems[15].Text).ToString("yyyy-MM-dd HH:mm") + "',CDwell = '" + listView1.Items[i].SubItems[16].Text + "', TruckAtGarage = '" + DateTime.Parse(listView1.Items[i].SubItems[17].Text).ToString("yyyy-MM-dd HH:mm") + "', OfficeReceive = '"
+ DateTime.Parse(listView1.Items[i].SubItems[18].Text).ToString("yyyy-MM-dd HH:mm") + "',DocTransmit = '" + DateTime.Parse(listView1.Items[i].SubItems[19].Text).ToString("yyyy-MM-dd HH:mm") + "',Remarks = '" + listView1.Items[i].SubItems[20].Text + "' ");
Obj.Insert(@"INSERT IGNORE INTO tblsd(TripNum)VALUES('" + listView1.Items[i].SubItems[0].Text + "')");
}
else if (listView1.Items[i].SubItems[9].Text != string.Empty)
{
Obj.Insert(@"INSERT INTO tbldocu(TripNum,GarageIn,GarageOut,CustomerIn,StartUnload,FinishUnload,ReleaseDoc,CustomerOut,CDwell,TruckAtGarage,OfficeReceive,DocTransmit,Remarks) VALUES
('" + listView1.Items[i].SubItems[0].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[9].Text).ToString("yyyy-MM-dd HH:mm") + "',NULL,'" + DateTime.Parse(listView1.Items[i].SubItems[11].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
DateTime.Parse(listView1.Items[i].SubItems[12].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[13].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[14].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[15].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
listView1.Items[i].SubItems[16].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[17].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[18].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[19].Text).ToString("yyyy-MM-dd HH:mm") + "','" + listView1.Items[i].SubItems[20].Text + "') ON DUPLICATE KEY UPDATE GarageIn = '"
+ DateTime.Parse(listView1.Items[i].SubItems[9].Text).ToString("yyyy-MM-dd HH:mm") + "',GarageOut = NULL,CustomerIn = '" + DateTime.Parse(listView1.Items[i].SubItems[11].Text).ToString("yyyy-MM-dd HH:mm") + "',StartUnload = '" + DateTime.Parse(listView1.Items[i].SubItems[12].Text).ToString("yyyy-MM-dd HH:mm") + "',FinishUnload = '"
+ DateTime.Parse(listView1.Items[i].SubItems[13].Text).ToString("yyyy-MM-dd HH:mm") + "',ReleaseDoc = '" + DateTime.Parse(listView1.Items[i].SubItems[14].Text).ToString("yyyy-MM-dd HH:mm") + "',CustomerOut = '" + DateTime.Parse(listView1.Items[i].SubItems[15].Text).ToString("yyyy-MM-dd HH:mm") + "',CDwell = '" + listView1.Items[i].SubItems[16].Text + "', TruckAtGarage = '" + DateTime.Parse(listView1.Items[i].SubItems[17].Text).ToString("yyyy-MM-dd HH:mm") + "', OfficeReceive = '"
+ DateTime.Parse(listView1.Items[i].SubItems[18].Text).ToString("yyyy-MM-dd HH:mm") + "',DocTransmit = '" + DateTime.Parse(listView1.Items[i].SubItems[19].Text).ToString("yyyy-MM-dd HH:mm") + "',Remarks = '" + listView1.Items[i].SubItems[20].Text + "' ");
// Obj.Insert(@"INSERT IGNORE INTO tbldocu(TripNum,GarageIn,GarageOut,CustomerIn,StartUnload,FinishUnload,ReleaseDoc,CustomerOut,CDwell,TruckAtGarage,OfficeReceive,DocTransmit,Remarks) VALUES
// ('" + listView1.Items[i].SubItems[0].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[9].Text).ToString("yyyy-MM-dd HH:mm") + "',NULL,'" + DateTime.Parse(listView1.Items[i].SubItems[11].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
// DateTime.Parse(listView1.Items[i].SubItems[12].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[13].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[14].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[15].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
// listView1.Items[i].SubItems[16].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[17].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[18].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[19].Text).ToString("yyyy-MM-dd HH:mm") + "','" + listView1.Items[i].SubItems[20].Text + "')");
Obj.Insert(@"INSERT IGNORE INTO tblsd(TripNum)VALUES('" + listView1.Items[i].SubItems[0].Text + "')");
}
else
{
// Obj.Insert(@"INSERT IGNORE INTO tbldocu(TripNum,GarageIn,GarageOut,CustomerIn,StartUnload,FinishUnload,ReleaseDoc,CustomerOut,CDwell,TruckAtGarage,OfficeReceive,DocTransmit,Remarks) VALUES
// ('" + listView1.Items[i].SubItems[0].Text + "',NULL,NULL,'" + DateTime.Parse(listView1.Items[i].SubItems[11].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
// DateTime.Parse(listView1.Items[i].SubItems[12].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[13].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[14].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[15].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
// listView1.Items[i].SubItems[16].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[17].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[18].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[19].Text).ToString("yyyy-MM-dd HH:mm") + "','" + listView1.Items[i].SubItems[20].Text + "')");
Obj.Insert(@"INSERT INTO tbldocu(TripNum,GarageIn,GarageOut,CustomerIn,StartUnload,FinishUnload,ReleaseDoc,CustomerOut,CDwell,TruckAtGarage,OfficeReceive,DocTransmit,Remarks) VALUES
('" + listView1.Items[i].SubItems[0].Text + "',NULL,NULL,'" + DateTime.Parse(listView1.Items[i].SubItems[11].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
DateTime.Parse(listView1.Items[i].SubItems[12].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[13].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[14].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[15].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
listView1.Items[i].SubItems[16].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[17].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[18].Text).ToString("yyyy-MM-dd HH:mm") + "','" + DateTime.Parse(listView1.Items[i].SubItems[19].Text).ToString("yyyy-MM-dd HH:mm") + "','" + listView1.Items[i].SubItems[20].Text + "') ON DUPLICATE KEY UPDATE GarageIn = NULL ,GarageOut = NULL,CustomerIn = '" + DateTime.Parse(listView1.Items[i].SubItems[11].Text).ToString("yyyy-MM-dd HH:mm") + "',StartUnload = '" + DateTime.Parse(listView1.Items[i].SubItems[12].Text).ToString("yyyy-MM-dd HH:mm") + "',FinishUnload = '"
+ DateTime.Parse(listView1.Items[i].SubItems[13].Text).ToString("yyyy-MM-dd HH:mm") + "',ReleaseDoc = '" + DateTime.Parse(listView1.Items[i].SubItems[14].Text).ToString("yyyy-MM-dd HH:mm") + "',CustomerOut = '" + DateTime.Parse(listView1.Items[i].SubItems[15].Text).ToString("yyyy-MM-dd HH:mm") + "',CDwell = '" + listView1.Items[i].SubItems[16].Text + "', TruckAtGarage = '" + DateTime.Parse(listView1.Items[i].SubItems[17].Text).ToString("yyyy-MM-dd HH:mm") + "', OfficeReceive = '"
+ DateTime.Parse(listView1.Items[i].SubItems[18].Text).ToString("yyyy-MM-dd HH:mm") + "',DocTransmit = '" + DateTime.Parse(listView1.Items[i].SubItems[19].Text).ToString("yyyy-MM-dd HH:mm") + "',Remarks = '" + listView1.Items[i].SubItems[20].Text + "' ");
Obj.Insert(@"INSERT IGNORE INTO tblsd(TripNum)VALUES('" + listView1.Items[i].SubItems[0].Text + "')");
}
Obj.Edit(@"UPDATE tblndc SET Waybill='" + listView1.Items[i].SubItems[2].Text + "' WHERE TripNum='" + int.Parse(listView1.Items[i].SubItems[0].Text) + "'");
Obj.CloseConnection();
}
catch
{
continue;
}
}
frm1.RefreshListData();
this.Close();
MessageBox.Show("Data Uploaded!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
Obj.Connection();
Obj.AuditTrail("NDC", "NDC Form", "Upload an Excel File");
Obj.CloseConnection();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
//MessageBox.Show("Cannot Import File! Possible Data Corrupted!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
else
{
}
}
}
