public void FillListView()
{
listView1.Items.Clear();
Obj.Connection();
Obj.datAdap = new MySqlDataAdapter(@"SELECT * FROM tblTruck", Obj.conn);
Obj.datTab = new DataTable();
try
{
Obj.datAdap.Fill(Obj.datTab);
for (int i = 0; i < Obj.datTab.Rows.Count; i++)
{
ListViewItem item = new ListViewItem(Obj.datTab.Rows[i]["BodyNum"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["PlateNum"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["Type"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["BGar"].ToString());
listView1.Items.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Obj.CloseConnection();
lblBNum.Text = string.Empty;
cbSearch.Text = "ALL";
lblCount.Text = listView1.Items.Count.ToString();
}
private void btnRestore_Click(object sender, EventArgs e)
{
var constring = System.Configuration.ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
string file = lblRes.Text;
DialogResult dr = MessageBox.Show(this, "Do you want to Restore Database?", "Confimation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dr == DialogResult.Yes)
{
using (MySqlConnection conn = new MySqlConnection(constring))
{
using (MySqlCommand cmd = new MySqlCommand())
{
using (MySqlBackup mb = new MySqlBackup(cmd))
{
try
{
cmd.Connection = conn;
conn.Open();
mb.ImportFromFile(file);
conn.Close();
MessageBox.Show("Database Restored", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information);
Obj.Connection();
Obj.AuditTrail("Admin", "RESTORE", "Restored a Database");
Obj.CloseConnection();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
}
}
private void FetchBodyNum()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.datAdap = new MySqlDataAdapter(@"SELECT BodyNum FROM tbltruck WHERE PlateNum = '" + txtPlateNum.Text + "'", Obj.conn);
Obj.datTab = new DataTable();
try
{
Obj.datAdap.Fill(Obj.datTab);
if (Obj.datTab.Rows.Count == 0)
{
txtBodyNum.Text = string.Empty;
}
else
{
txtBodyNum.Text = Obj.datTab.Rows[0]["BodyNum"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Obj.CloseConnection();
}
private void FillAuditData()
{
listView1.Items.Clear();
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.datAdap = new MySqlDataAdapter(@"SELECT * FROM tblaudit WHERE DateOccured >='" + dtSDate.Text + "' AND DateOccured <= '" + dtEDate.Text + "' ", Obj.conn);
Obj.datTab = new DataTable();
try
{
Obj.datAdap.Fill(Obj.datTab);
for (int i = 0; i < Obj.datTab.Rows.Count; i++)
{
ListViewItem item = new ListViewItem(Obj.datTab.Rows[i]["AuditNum"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["UserName"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["Module"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["Activity"].ToString());
if (Obj.datTab.Rows[i]["DateOccured"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DateOccured"].ToString()).ToString("MM/dd/yyyy HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["DateOccured"].ToString());
}
listView1.Items.Add(item);
}
lblAuditNum.Text = string.Empty;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Obj.CloseConnection();
}
private void SendToEmail()
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Upload an Excel File";
ofd.Filter = "Excel File|*.xls";
ofd.InitialDirectory = @"C:\";
if (ofd.ShowDialog() == DialogResult.OK)
{
var fileNames = ofd.FileName;
filesname = fileNames;
}
if (string.IsNullOrEmpty(filesname))
{ }
else
{
var lines = File.ReadAllLines(filesname);
if (filesname.Contains("TRIP REPORT CD"))
{
DateTime DateAndTime = DateTime.Parse(DateTime.Now.ToString());
string newDT = DateAndTime.ToString("M-d-yyyy HHmm");
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress("*****@*****.**");
mail.To.Add("*****@*****.**");
mail.Subject = "TRIP REPORT CD " + newDT;
System.Net.Mail.Attachment attachment;
attachment = new System.Net.Mail.Attachment(filesname);
mail.Attachments.Add(attachment);
SmtpServer.Port = 587;
SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "Wfagro123");
SmtpServer.EnableSsl = true;
Thread T1 = new Thread(delegate()
{
try
{
SmtpServer.Send(mail);
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.AuditTrail("NDC", "TripReport", "Send to Email");
Obj.CloseConnection();
MessageBox.Show("Email Sent!", "Success!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}); T1.Start();
}
else
{
MessageBox.Show("Wrong file Uploaded!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
}
private void btnEmail_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Upload an Excel File";
ofd.Filter = "WFA/Excel File|*.wfa; *.xls;";
ofd.InitialDirectory = @"C:\";
if (ofd.ShowDialog() == DialogResult.OK)
{
var fileNames = ofd.FileName;
filesname = fileNames;
}
if (string.IsNullOrEmpty(filesname))
{
}
else
{
try
{
DateTime DateAndTime = DateTime.Parse(DateTime.Now.ToString());
string newDT = DateAndTime.ToString("M-d-yyyy HHmm");
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress(txtSMail.Text);
mail.To.Add(txtRMail.Text);
mail.Subject = System.IO.Path.GetFileNameWithoutExtension(ofd.FileName);
System.Net.Mail.Attachment attachment;
attachment = new System.Net.Mail.Attachment(filesname);
mail.Attachments.Add(attachment);
SmtpServer.Port = 587;
SmtpServer.Credentials = new System.Net.NetworkCredential(txtSMail.Text, txtPass.Text);
SmtpServer.EnableSsl = true;
Thread T1 = new Thread(delegate()
{
try
{
SmtpServer.Send(mail);
MessageBox.Show("Email Sent!", "Success!");
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.AuditTrail("ANY", "EMAIL", "Send to Email");
Obj.CloseConnection();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}); T1.Start();
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
DatabaseLibrary Obj = new DatabaseLibrary();
//string dtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
try
{
Obj.Connection();
Obj.cmd = new MySqlCommand(@"SELECT * FROM tbluser WHERE BINARY UserName=@UserN AND Password=@PassW LIMIT 1", Obj.conn);
Obj.cmd.Parameters.AddWithValue("@UserN", txtUser.Text);
Obj.cmd.Parameters.AddWithValue("@PassW", txtPass.Text);
Obj.datRead = Obj.cmd.ExecuteReader();
if (Obj.datRead.HasRows)
{
Obj.datRead.Read();
uL = Obj.datRead.GetString(2);
Obj.CloseConnection();
Obj.Connection();
Obj.WriteAudit("INSERT INTO tblaudit (UserName,Module,Activity,DateOccured) VALUES ('"
+ txtUser.Text + "','Login','Logged-In','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "')");
MessageBox.Show("Access Granted!", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information);
Obj.CloseConnection();
this.Hide();
var form2 = new frmMain(txtUser.Text, uL);
form2.Closed += (s, args) => this.Close();
form2.Show();
}
else
{
MessageBox.Show("Access Denied!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtUser.Text = string.Empty;
txtPass.Text = string.Empty;
txtUser.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void logoutToolStripMenuItem_Click(object sender, EventArgs e)
{
DialogResult Diag = new DialogResult();
Diag = MessageBox.Show("Do you want to logout?", "Confirmation", MessageBoxButtons.YesNo);
if (Diag == DialogResult.Yes)
{
Obj.Connection();
Obj.WriteAudit("INSERT INTO tblaudit (UserName,Module,Activity,DateOccured) VALUES ('"
+ user + "','Dashboard','Logged-Out','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "')");
Obj.CloseConnection();
this.Hide();
var form2 = new frmLogin();
form2.Closed += (s, args) => this.Close();
form2.Show();
}
else if (Diag == DialogResult.No)
{
}
}
private void FillListData()
{
listView1.Items.Clear();
Obj.Connection();
Obj.datAdap = new MySqlDataAdapter(@"SELECT * FROM tbluser", Obj.conn);
Obj.datTab = new DataTable();
try
{
Obj.datAdap.Fill(Obj.datTab);
for (int i = 0; i < Obj.datTab.Rows.Count; i++)
{
ListViewItem item = new ListViewItem(Obj.datTab.Rows[i]["Username"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["UserLevel"].ToString());
listView1.Items.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Obj.CloseConnection();
}
private void FetchLoadDetails()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.datAdap = new MySqlDataAdapter(@"SELECT * FROM tblndc WHERE TripNum = '" + int.Parse(lblTripNum.Text) + "'", Obj.conn);
Obj.datTab = new DataTable();
try
{
Obj.datAdap.Fill(Obj.datTab);
if (Obj.datTab.Rows.Count == 0)
{
dtComTime.Text = string.Empty;
txtLN.Text = string.Empty;
txtTReq.Text = string.Empty;
txtINV.Text = string.Empty;
dtTruckIn.Text = string.Empty;
dtChecklist.Text = string.Empty;
dtSCheck.Text = string.Empty;
dtFCheck.Text = string.Empty;
dtSLoad.Text = string.Empty;
dtFLoad.Text = string.Empty;
dtDocRec.Text = string.Empty;
dtTruckOut.Text = string.Empty;
txtDwell.Text = string.Empty;
txtLN.Text = string.Empty;
}
else
{
dtComTime.Text = Obj.datTab.Rows[0]["CommitTime"].ToString();
txtLN.Text = Obj.datTab.Rows[0]["Cases"].ToString();
txtTReq.Text = Obj.datTab.Rows[0]["TruckReq"].ToString();
txtINV.Text = Obj.datTab.Rows[0]["INVCDN"].ToString();
dtTruckIn.Text = Obj.datTab.Rows[0]["TruckIn"].ToString();
dtChecklist.Text = Obj.datTab.Rows[0]["Checklist"].ToString();
dtSCheck.Text = Obj.datTab.Rows[0]["SCheck"].ToString();
dtFCheck.Text = Obj.datTab.Rows[0]["FCheck"].ToString();
dtSLoad.Text = Obj.datTab.Rows[0]["SLoad"].ToString();
dtFLoad.Text = Obj.datTab.Rows[0]["FLoad"].ToString();
dtDocRec.Text = Obj.datTab.Rows[0]["DocRec"].ToString();
dtTruckOut.Text = Obj.datTab.Rows[0]["TruckOut"].ToString();
txtDwell.Text = Obj.datTab.Rows[0]["SDwell"].ToString();
txtLN.Text = Obj.datTab.Rows[0]["LoadNum"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Obj.CloseConnection();
}
private void FillPlateNum()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.cmd = new MySqlCommand(@"SELECT PlateNum FROM tbltruck", Obj.conn);
Obj.datRead = Obj.cmd.ExecuteReader();
AutoCompleteStringCollection pnum = new AutoCompleteStringCollection();
while (Obj.datRead.Read())
{
pnum.Add(Obj.datRead.GetString(0));
}
txtPlateNum.AutoCompleteCustomSource = pnum;
Obj.CloseConnection();
}
private void FetchUnloadDetails()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.datAdap = new MySqlDataAdapter(@"SELECT * FROM tbldocu WHERE TripNum = '" + int.Parse(lblTripNum.Text) + "'", Obj.conn);
Obj.datTab = new DataTable();
try
{
Obj.datAdap.Fill(Obj.datTab);
if (Obj.datTab.Rows.Count == 0)
{
dtGarIn.Text = string.Empty;
dtGarOut.Text = string.Empty;
dtCustIn.Text = string.Empty;
dtSU.Text = string.Empty;
dtFU.Text = string.Empty;
dtRDoc.Text = string.Empty;
dtCustOut.Text = string.Empty;
txtDwell.Text = string.Empty;
dtTruckGar.Text = string.Empty;
dtDocRec.Text = string.Empty;
dtDocTrans.Text = string.Empty;
txtRemarks.Text = string.Empty;
}
else
{
dtGarIn.Text = Obj.datTab.Rows[0]["GarageIn"].ToString();
dtGarOut.Text = Obj.datTab.Rows[0]["GarageOut"].ToString();
dtCustIn.Text = Obj.datTab.Rows[0]["CustomerIn"].ToString();
dtSU.Text = Obj.datTab.Rows[0]["StartUnload"].ToString();
dtFU.Text = Obj.datTab.Rows[0]["FinishUnload"].ToString();
dtRDoc.Text = Obj.datTab.Rows[0]["ReleaseDoc"].ToString();
dtCustOut.Text = Obj.datTab.Rows[0]["CustomerOut"].ToString();
txtDwell.Text = Obj.datTab.Rows[0]["CDwell"].ToString();
dtTruckGar.Text = Obj.datTab.Rows[0]["TruckAtGarage"].ToString();
dtDocRec.Text = Obj.datTab.Rows[0]["OfficeReceive"].ToString();
dtDocTrans.Text = Obj.datTab.Rows[0]["DocTransmit"].ToString();
txtRemarks.Text = Obj.datTab.Rows[0]["Remarks"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Obj.CloseConnection();
}
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 CountFile()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.datAdap = new MySqlDataAdapter(@"SELECT MAX(TripNum) as Tot FROM tblndc", Obj.conn);
Obj.datTab = new DataTable();
Obj.datAdap.Fill(Obj.datTab);
if (Obj.datTab.Rows[0]["Tot"] != DBNull.Value)
{
nCount = Obj.datTab.Rows[0]["Tot"].ToString();
}
else
{
nCount = "0";
}
}
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 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 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 listView1_DoubleClick(object sender, EventArgs e)
{
DatabaseLibrary Obj = new DatabaseLibrary();
DialogResult Diag = new DialogResult();
Diag = MessageBox.Show("Do you want to Remove this data?", "Confirmation", MessageBoxButtons.YesNo);
if (Diag == DialogResult.Yes)
{
Obj.Connection();
Obj.Delete(@"DELETE FROM tblaudit WHERE AuditNum='" + int.Parse(lblAuditNum.Text) + "'");
Obj.CloseConnection();
FillAuditData();
}
else if (Diag == DialogResult.No)
{
FillAuditData();
}
}
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 btnDelete_Click(object sender, EventArgs e)
{
DatabaseLibrary Obj = new DatabaseLibrary();
DialogResult Diag = new DialogResult();
Diag = MessageBox.Show("Do you want to Remove this data?", "Confirmation", MessageBoxButtons.YesNo);
if (Diag == DialogResult.Yes)
{
Obj.Connection();
Obj.Delete(@"DELETE FROM tblndc WHERE TripNum='" + int.Parse(lblTrip.Text) + "'");
Obj.Delete(@"DELETE FROM tbldocu WHERE TripNum='" + int.Parse(lblTrip.Text) + "'");
Obj.Delete(@"DELETE FROM tblsd WHERE TripNum='" + int.Parse(lblTrip.Text) + "'");
Obj.CloseConnection();
frm1.RefreshRDD();
this.Close();
}
else if (Diag == DialogResult.No)
{
}
}
private void btnSave_Click(object sender, EventArgs e)
{
int tnum;
CountFile();
tnum = Convert.ToInt32(nCount);
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++)
{
tnum++;
Obj.Connection();
Obj.Insert(@"INSERT INTO tblndc(TripNum,DispDate,Waybill,BodyNum,PlateNum,Driver,Helper,Source,RDD,CommitTime,Cases,TruckReq,CustName,TruckIn,Checklist,SCheck,FCheck,SLoad,FLoad,DocRec,INVCDN,TruckOut,SDwell,LoadNum) VALUES
('" + tnum + "','" + listView1.Items[i].SubItems[0].Text + "','" + listView1.Items[i].SubItems[1].Text + "','" + listView1.Items[i].SubItems[2].Text + "','" + listView1.Items[i].SubItems[3].Text + "','" + listView1.Items[i].SubItems[4].Text + "','" + listView1.Items[i].SubItems[5].Text + "','" + listView1.Items[i].SubItems[6].Text + "','" +
listView1.Items[i].SubItems[7].Text + "','" + listView1.Items[i].SubItems[8].Text + "','" + listView1.Items[i].SubItems[9].Text + "','" + listView1.Items[i].SubItems[10].Text + "','" + listView1.Items[i].SubItems[11].Text + "','" + listView1.Items[i].SubItems[12].Text + "','" + listView1.Items[i].SubItems[13].Text + "','" + listView1.Items[i].SubItems[14].Text + "','" +
listView1.Items[i].SubItems[15].Text + "','" + listView1.Items[i].SubItems[16].Text + "','" + listView1.Items[i].SubItems[17].Text + "','" + listView1.Items[i].SubItems[18].Text + "','" + listView1.Items[i].SubItems[19].Text + "','" + listView1.Items[i].SubItems[20].Text + "','" + listView1.Items[i].SubItems[21].Text + "','" + listView1.Items[i].SubItems[22].Text + "')");
Obj.CloseConnection();
}
frm1.RefreshRDD();
this.Close();
MessageBox.Show("Data Saved!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }
}
else
{
}
}
}
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
{
}
}
}
public void RefreshRDD()
{
listView1.Items.Clear();
DatabaseLibrary Obj = new DatabaseLibrary();
string query = @"SELECT tbldocu.`TripNum` AS TripDocu,tblndc.`DispDate`,tblndc.`Waybill`,tblndc.`BodyNum`,tblndc.`PlateNum`,
tblndc.`Driver`,tblndc.`Helper`,tblndc.`Source`,tblndc.`RDD`,tblndc.LoadNum,tblndc.`CustName`,
tblsd.`DocReceiveNDC`,tblsd.`ReleaseSD`,tblsd.`SDNumber`,tblsd.`Remarks`
FROM tblsd
INNER JOIN tbldocu ON tblsd.`TripNum`=tbldocu.`TripNum`
INNER JOIN tblndc ON tblsd.`TripNum`=tblndc.`TripNum` WHERE RDD = '" +
dtRDD.Text + "'ORDER BY tblndc.`RDD` DESC";
Obj.Connection();
Obj.datAdap = new MySqlDataAdapter(query, Obj.conn);
Obj.datTab = new DataTable();
try
{
Obj.datAdap.Fill(Obj.datTab);
for (int i = 0; i < Obj.datTab.Rows.Count; i++)
{
ListViewItem item = new ListViewItem(Obj.datTab.Rows[i]["TripDocu"].ToString());
if (Obj.datTab.Rows[i]["DispDate"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DispDate"].ToString()).ToString("MM/dd/yyyy"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["DispDate"].ToString());
}
item.SubItems.Add(Obj.datTab.Rows[i]["Waybill"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["BodyNum"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["PlateNum"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["Driver"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["Source"].ToString());
if (Obj.datTab.Rows[i]["RDD"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["RDD"].ToString()).ToString("MM/dd/yyyy"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["RDD"].ToString());
}
item.SubItems.Add(Obj.datTab.Rows[i]["LoadNum"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["CustName"].ToString());
if (Obj.datTab.Rows[i]["DocReceiveNDC"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DocReceiveNDC"].ToString()).ToString("MM/dd/yyyy HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["DocReceiveNDC"].ToString());
}
if (Obj.datTab.Rows[i]["ReleaseSD"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["ReleaseSD"].ToString()).ToString("MM/dd/yyyy HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["ReleaseSD"].ToString());
}
item.SubItems.Add(Obj.datTab.Rows[i]["SDNumber"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["Remarks"].ToString());
listView1.Items.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Obj.CloseConnection();
lblCount.Text = listView1.Items.Count.ToString();
}
private void btnUpload_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();
// Obj.Insert(@"INSERT IGNORE INTO tblndc(TripNum,DispDate,Waybill,BodyNum,PlateNum,Driver,Helper,Source,RDD,CommitTime,Cases,TruckReq,CustName,TruckIn,Checklist,SCheck,FCheck,SLoad,FLoad,DocRec,INVCDN,TruckOut,SDwell,LoadNum) VALUES
// ('" + listView1.Items[i].SubItems[0].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[1].Text).ToString("yyyy-MM-dd") + "','" + listView1.Items[i].SubItems[2].Text + "','" + listView1.Items[i].SubItems[3].Text + "','" +
// listView1.Items[i].SubItems[4].Text + "','" +listView1.Items[i].SubItems[5].Text + "','" + listView1.Items[i].SubItems[6].Text + "','" +listView1.Items[i].SubItems[7].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[8].Text).ToString("yyyy-MM-dd") + "','" +
// DateTime.Parse(listView1.Items[i].SubItems[9].Text).ToString("yyyy-MM-dd HH:mm") + "','" + listView1.Items[i].SubItems[10].Text + "','" + listView1.Items[i].SubItems[11].Text + "','" + listView1.Items[i].SubItems[12].Text + "','" + 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") + "','" + DateTime.Parse(listView1.Items[i].SubItems[16].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
// 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 + "','" +
// DateTime.Parse(listView1.Items[i].SubItems[21].Text).ToString("yyyy-MM-dd HH:mm") + "','" + listView1.Items[i].SubItems[22].Text + "','" + listView1.Items[i].SubItems[23].Text + "')");
Obj.Insert(@"INSERT INTO tblndc(TripNum,DispDate,Waybill,BodyNum,PlateNum,Driver,Helper,Source,RDD,CommitTime,Cases,TruckReq,CustName,TruckIn,Checklist,SCheck,FCheck,SLoad,FLoad,DocRec,INVCDN,TruckOut,SDwell,LoadNum) VALUES
('" + listView1.Items[i].SubItems[0].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[1].Text).ToString("yyyy-MM-dd") + "','" + listView1.Items[i].SubItems[2].Text + "','" + listView1.Items[i].SubItems[3].Text + "','" +
listView1.Items[i].SubItems[4].Text + "','" + listView1.Items[i].SubItems[5].Text + "','" + listView1.Items[i].SubItems[6].Text + "','" + listView1.Items[i].SubItems[7].Text + "','" + DateTime.Parse(listView1.Items[i].SubItems[8].Text).ToString("yyyy-MM-dd") + "','" +
DateTime.Parse(listView1.Items[i].SubItems[9].Text).ToString("yyyy-MM-dd HH:mm") + "','" + listView1.Items[i].SubItems[10].Text + "','" + listView1.Items[i].SubItems[11].Text + "','" + listView1.Items[i].SubItems[12].Text + "','" + 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") + "','" + DateTime.Parse(listView1.Items[i].SubItems[16].Text).ToString("yyyy-MM-dd HH:mm") + "','" +
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 + "','" +
DateTime.Parse(listView1.Items[i].SubItems[21].Text).ToString("yyyy-MM-dd HH:mm") + "','" + listView1.Items[i].SubItems[22].Text + "','" + listView1.Items[i].SubItems[23].Text + "') ON DUPLICATE KEY UPDATE DispDate = '" + DateTime.Parse(listView1.Items[i].SubItems[1].Text).ToString("yyyy-MM-dd") + "',BodyNum = '" + listView1.Items[i].SubItems[3].Text + "',PlateNum = '" + listView1.Items[i].SubItems[4].Text + "',Driver = '" + listView1.Items[i].SubItems[5].Text + "',Helper = '" + listView1.Items[i].SubItems[6].Text + "',Source = '" + listView1.Items[i].SubItems[7].Text + "',RDD = '" +
DateTime.Parse(listView1.Items[i].SubItems[8].Text).ToString("yyyy-MM-dd") + "',CommitTime = '" + DateTime.Parse(listView1.Items[i].SubItems[9].Text).ToString("yyyy-MM-dd HH:mm") + "',Cases = '" + listView1.Items[i].SubItems[10].Text + "',TruckReq = '" + listView1.Items[i].SubItems[11].Text + "',CustName = '" +
listView1.Items[i].SubItems[12].Text + "',TruckIn = '" + DateTime.Parse(listView1.Items[i].SubItems[13].Text).ToString("yyyy-MM-dd HH:mm") + "',Checklist = '" + DateTime.Parse(listView1.Items[i].SubItems[14].Text).ToString("yyyy-MM-dd HH:mm") + "',SCheck = '" + DateTime.Parse(listView1.Items[i].SubItems[15].Text).ToString("yyyy-MM-dd HH:mm") + "',FCheck = '" +
DateTime.Parse(listView1.Items[i].SubItems[16].Text).ToString("yyyy-MM-dd HH:mm") + "',SLoad = '" + DateTime.Parse(listView1.Items[i].SubItems[17].Text).ToString("yyyy-MM-dd HH:mm") + "',FLoad = '" + DateTime.Parse(listView1.Items[i].SubItems[18].Text).ToString("yyyy-MM-dd HH:mm") + "',DocRec = '" +
DateTime.Parse(listView1.Items[i].SubItems[19].Text).ToString("yyyy-MM-dd HH:mm") + "',INVCDN = '" + listView1.Items[i].SubItems[20].Text + "',TruckOut = '" + DateTime.Parse(listView1.Items[i].SubItems[21].Text).ToString("yyyy-MM-dd HH:mm") + "',SDwell = '" + listView1.Items[i].SubItems[22].Text + "',LoadNum = '" + listView1.Items[i].SubItems[23].Text + "' ");
Obj.Insert(@"INSERT IGNORE INTO tbldocu(TripNum)VALUES('" + 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("OFFICE", "IMPORT CD", "Import an Excel File");
Obj.CloseConnection();
}
catch
{
MessageBox.Show("Cannot Import File! Possible Data Corrupted!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
}
}
public void RefreshRDD()
{
DateTime DocDate;
DatabaseLibrary Obj = new DatabaseLibrary();
listView1.Items.Clear();
string query = @"SELECT tblndc.`TripNum` AS TripSum, tblndc.`DispDate`,tblndc.`Waybill`,tblndc.`BodyNum`,tblndc.`PlateNum`,tblndc.`Source`,tblndc.`RDD`,tblndc.LoadNum,
tblndc.`CustName`,tblndc.`INVCDN`,tblsd.`DocReceiveNDC`
FROM tblndc
INNER JOIN tblsd ON tblndc.`TripNum`=tblsd.`TripNum`
WHERE tblsd.`ReleaseSD` IS NULL AND RDD='" + dtRDD.Text + "' ORDER BY tblsd.`DocReceiveNDC` ASC";
Obj.Connection();
Obj.datAdap = new MySqlDataAdapter(query, Obj.conn);
Obj.datTab = new DataTable();
try
{
Obj.datAdap.Fill(Obj.datTab);
for (int i = 0; i < Obj.datTab.Rows.Count; i++)
{
ListViewItem item = new ListViewItem(Obj.datTab.Rows[i]["TripSum"].ToString());
if (Obj.datTab.Rows[i]["DispDate"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DispDate"].ToString()).ToString("MM/dd/yyyy"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["DispDate"].ToString());
}
item.SubItems.Add(Obj.datTab.Rows[i]["Waybill"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["BodyNum"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["PlateNum"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["Source"].ToString());
if (Obj.datTab.Rows[i]["RDD"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["RDD"].ToString()).ToString("MM/dd/yyyy"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["RDD"].ToString());
}
item.SubItems.Add(Obj.datTab.Rows[i]["LoadNum"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["CustName"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["INVCDN"].ToString());
if (Obj.datTab.Rows[i]["DocReceiveNDC"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DocReceiveNDC"].ToString()).ToString("MM/dd/yyyy HH:mm"));
DocDate = Convert.ToDateTime(item.SubItems[10].Text);
TimeSpan TimeDiff = DateTime.Now.Subtract(DocDate);
item.SubItems.Add(TimeDiff.Days.ToString());
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["DocReceiveNDC"].ToString());
}
listView1.Items.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Obj.CloseConnection();
lblCount.Text = listView1.Items.Count.ToString();
}
