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 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 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 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);
}
}
}
}
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
{
}
}
}
private void ExportSelected()
{
for (int i = 0; i < listView1.CheckedItems.Count; i++)
{
ListViewItem item = listView1.CheckedItems[i];
if (item.SubItems[0].Text != string.Empty &&
item.SubItems[1].Text != string.Empty &&
item.SubItems[2].Text != string.Empty &&
item.SubItems[3].Text != string.Empty &&
item.SubItems[4].Text != string.Empty &&
item.SubItems[5].Text != string.Empty &&
item.SubItems[6].Text != string.Empty &&
item.SubItems[7].Text != string.Empty &&
item.SubItems[8].Text != string.Empty &&
item.SubItems[11].Text != string.Empty &&
item.SubItems[12].Text != string.Empty &&
item.SubItems[13].Text != string.Empty &&
item.SubItems[14].Text != string.Empty &&
item.SubItems[15].Text != string.Empty &&
item.SubItems[16].Text != string.Empty &&
item.SubItems[17].Text != string.Empty &&
item.SubItems[18].Text != string.Empty &&
item.SubItems[19].Text != string.Empty)
{
}
else
{
blankCheck++;
}
}
if (blankCheck == 0)
{
// DateTime DateAndTime = DateTime.Parse(DateTime.Now.ToString());
// string newDT = DateAndTime.ToString("M-d-yyyy HHmm");
saveFileDialog1.Filter = "WFA files (*.wfa)|*.wfa";
saveFileDialog1.FileName = "OFFICE RDD " + dtRDD.Text + "";
saveFileDialog1.Title = "Export to Excel";
StringBuilder sb = new StringBuilder();
foreach (ListViewItem lvi in listView1.CheckedItems)
{
sb.Append(lvi.SubItems[0].Text + ",");
sb.Append(lvi.SubItems[1].Text + ",");
sb.Append(lvi.SubItems[2].Text + ",");
sb.Append(lvi.SubItems[3].Text + ",");
sb.Append(lvi.SubItems[4].Text + ",");
sb.Append(lvi.SubItems[5].Text + ",");
sb.Append(lvi.SubItems[6].Text + ",");
sb.Append(lvi.SubItems[7].Text + ",");
sb.Append(lvi.SubItems[8].Text + ",");
sb.Append(lvi.SubItems[9].Text + ",");
sb.Append(lvi.SubItems[10].Text + ",");
sb.Append(lvi.SubItems[11].Text + ",");
sb.Append(lvi.SubItems[12].Text + ",");
sb.Append(lvi.SubItems[13].Text + ",");
sb.Append(lvi.SubItems[14].Text + ",");
sb.Append(lvi.SubItems[15].Text + ",");
sb.Append(lvi.SubItems[16].Text + ",");
sb.Append(lvi.SubItems[17].Text + ",");
sb.Append(lvi.SubItems[18].Text + ",");
sb.Append(lvi.SubItems[19].Text + ",");
sb.Append(lvi.SubItems[20].Text + ",");
sb.AppendLine();
}
DialogResult dr = saveFileDialog1.ShowDialog();
if (dr == DialogResult.OK)
{
StreamWriter sw = new StreamWriter(saveFileDialog1.FileName);
sw.Write(sb.ToString());
sw.Close();
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.AuditTrail("OFFICE", "Docu", "Export a Selected Excel File");
Obj.CloseConnection();
MessageBox.Show("Data Export Done!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
blankCheck = 0;
}
}
else if (blankCheck > 1)
{
MessageBox.Show("Cannot Extract File! Please Complete Time details!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
RefreshListData();
blankCheck = 0;
}
}
private void saveToExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
if (listView1.Items.Count != 0)
{
foreach (ListViewItem item in listView1.Items)
{
if (item.SubItems[0].Text != string.Empty &&
item.SubItems[1].Text != string.Empty &&
item.SubItems[3].Text != string.Empty &&
item.SubItems[4].Text != string.Empty &&
item.SubItems[5].Text != string.Empty &&
item.SubItems[6].Text != string.Empty &&
item.SubItems[7].Text != string.Empty &&
item.SubItems[8].Text != string.Empty &&
item.SubItems[9].Text != string.Empty &&
item.SubItems[10].Text != string.Empty &&
item.SubItems[11].Text != string.Empty &&
item.SubItems[12].Text != string.Empty &&
item.SubItems[13].Text != string.Empty &&
item.SubItems[14].Text != string.Empty &&
item.SubItems[15].Text != string.Empty &&
item.SubItems[16].Text != string.Empty &&
item.SubItems[17].Text != string.Empty &&
item.SubItems[18].Text != string.Empty &&
item.SubItems[19].Text != string.Empty &&
item.SubItems[20].Text != string.Empty &&
item.SubItems[21].Text != string.Empty &&
item.SubItems[22].Text != string.Empty)
{
}
else
{
blankCheck++;
}
}
if (blankCheck == 0)
{
//DateTime DateAndTime = DateTime.Parse(DateTime.Now.ToString());
//string newDT = DateAndTime.ToString("M-d-yyyy HHmm");
saveFileDialog1.Filter = "WFA file (*.wfa)|*.wfa";
saveFileDialog1.FileName = "CD RDD " + dtRDD.Text + "";
saveFileDialog1.Title = "Export to Excel";
StringBuilder sb = new StringBuilder();
string oldInv,newdrive, newcust, newhelp, nload;
foreach (ListViewItem lvi in listView1.Items)
{
sb.Append(lvi.SubItems[0].Text + ",");
sb.Append(lvi.SubItems[1].Text + ",");
sb.Append(lvi.SubItems[2].Text + ",");
sb.Append(lvi.SubItems[3].Text + ",");
sb.Append(lvi.SubItems[4].Text + ",");
sb.Append(newdrive=lvi.SubItems[5].Text.Replace(",", "") + ",");
sb.Append(newhelp = lvi.SubItems[6].Text.Replace(",", "/") + ",");
sb.Append(lvi.SubItems[7].Text + ",");
sb.Append(lvi.SubItems[8].Text + ",");
sb.Append(lvi.SubItems[9].Text + ",");
sb.Append(lvi.SubItems[10].Text + ",");
sb.Append(lvi.SubItems[11].Text + ",");
sb.Append(newcust = lvi.SubItems[12].Text.Replace(",", " ") + ",");
sb.Append(lvi.SubItems[13].Text + ",");
sb.Append(lvi.SubItems[14].Text + ",");
sb.Append(lvi.SubItems[15].Text + ",");
sb.Append(lvi.SubItems[16].Text + ",");
sb.Append(lvi.SubItems[17].Text + ",");
sb.Append(lvi.SubItems[18].Text + ",");
sb.Append(lvi.SubItems[19].Text + ",");
oldInv = lvi.SubItems[20].Text.Replace("\r\n", "");
sb.Append(oldInv + ",");
sb.Append(lvi.SubItems[21].Text + ",");
sb.Append(lvi.SubItems[22].Text + ",");
sb.Append(nload = lvi.SubItems[23].Text.Replace(",", " ") + ",");
sb.AppendLine();
}
DialogResult dr = saveFileDialog1.ShowDialog();
if (dr == DialogResult.OK)
{
StreamWriter sw = new StreamWriter(saveFileDialog1.FileName);
sw.Write(sb.ToString());
sw.Close();
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.AuditTrail("CD", "LOADING FORM", "EXTRACT DATA");
Obj.CloseConnection();
MessageBox.Show("Data Export Done!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (dr == DialogResult.Cancel)
{ }
}
else if (blankCheck > 1)
{
blankCheck = 0;
MessageBox.Show("Cannot Extract File! Please Complete details!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
}
private void ExportSelected()
{
for (int es = 0; es < listView1.CheckedItems.Count; es++)
{
ListViewItem item = listView1.CheckedItems[es];
if (listView1.Items.Count != 0)
{
if (item.SubItems[0].Text != string.Empty &&
item.SubItems[1].Text != string.Empty &&
item.SubItems[2].Text != string.Empty &&
item.SubItems[3].Text != string.Empty &&
item.SubItems[4].Text != string.Empty &&
item.SubItems[5].Text != string.Empty &&
item.SubItems[6].Text != string.Empty &&
item.SubItems[7].Text != string.Empty &&
item.SubItems[8].Text != string.Empty &&
item.SubItems[9].Text != string.Empty &&
item.SubItems[10].Text != string.Empty &&
item.SubItems[11].Text != string.Empty &&
item.SubItems[12].Text != string.Empty &&
item.SubItems[13].Text != string.Empty &&
item.SubItems[14].Text != string.Empty &&
item.SubItems[15].Text != string.Empty &&
item.SubItems[16].Text != string.Empty &&
item.SubItems[17].Text != string.Empty &&
item.SubItems[18].Text != string.Empty &&
item.SubItems[19].Text != string.Empty &&
item.SubItems[20].Text != string.Empty &&
item.SubItems[21].Text != string.Empty &&
item.SubItems[24].Text != string.Empty &&
item.SubItems[25].Text != string.Empty &&
item.SubItems[26].Text != string.Empty &&
item.SubItems[27].Text != string.Empty &&
item.SubItems[28].Text != string.Empty &&
item.SubItems[29].Text != string.Empty &&
item.SubItems[30].Text != string.Empty &&
item.SubItems[31].Text != string.Empty &&
item.SubItems[32].Text != string.Empty &&
item.SubItems[34].Text != string.Empty)
{ }
else
{
blankCheck++;
}
}
}
if (blankCheck == 0)
{
object misValue = System.Reflection.Missing.Value;
//DateTime DateAndTime = DateTime.Parse(DateTime.Now.ToString());
// string newDT = DateAndTime.ToString("M-d-yyyy HHmm");
saveFileDialog1.Filter = "Excel file (*.xls)|*.xls";
saveFileDialog1.FileName = "TRIP REPORT RDD " + dtRDD.Text + "";
saveFileDialog1.Title = "Export to Excel";
DialogResult dr = saveFileDialog1.ShowDialog();
CheckExcelProcesses();
if (dr == DialogResult.OK)
{
XL.Application app = null;
XL.Workbook wb = null;
XL.Worksheet ws = null;
app = new Microsoft.Office.Interop.Excel.Application();
wb = app.Workbooks.Add(misValue);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
int i = 1;
int i2 = 2;
int x = 1;
int x2 = 1;
try
{
foreach (ColumnHeader ch in listView1.Columns)
{
ws.Cells[x, x2] = ch.Text;
ws.Cells[x, x2].HorizontalAlignment = XL.XlHAlign.xlHAlignCenter;
ws.Cells[x, x2].VerticalAlignment = XL.XlVAlign.xlVAlignCenter;
ws.Cells[x, x2].Borders.LineStyle = XL.XlLineStyle.xlContinuous;
ws.Cells[x, x2].Font.Bold = true;
ws.Cells[x, x2].Interior.Color = XL.XlRgbColor.rgbLightGray;
x2++;
}
foreach (ListViewItem lvi in listView1.CheckedItems)
{
i = 1;
foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
{
ws.Cells[i2, i] = lvs.Text;
ws.Cells[i2, i].HorizontalAlignment = XL.XlHAlign.xlHAlignCenter;
ws.Cells[i2, i].VerticalAlignment = XL.XlVAlign.xlVAlignCenter;
ws.Cells[i2, i].Borders.LineStyle = XL.XlLineStyle.xlContinuous;
ws.Cells.EntireColumn.AutoFit();
ws.Cells.WrapText = true;
i++;
}
i2++;
}
ws.Name = dtRDD.Text;
wb.SaveAs(saveFileDialog1.FileName);
wb.Close(true, Type.Missing, Type.Missing);
app.Quit();
Marshal.ReleaseComObject(ws);
Marshal.ReleaseComObject(wb);
saveFileDialog1.Dispose();
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.AuditTrail("NDC", "TripReport", "Export an Excel File");
Obj.CloseConnection();
MessageBox.Show("Data Export Done!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
blankCheck = 0;
KillExcel();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
else if (blankCheck > 1)
{
MessageBox.Show("Cannot Extract File! Please Complete Time details!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
blankCheck = 0;
}
}
