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 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 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 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 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 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) { string connectionString = string.Format("server={0};userid={1};password={2};database={3};", txtServ.Text, txtUser.Text, txtPass.Text, txtDB.Text); try { SqlHelper helper = new SqlHelper(connectionString); if (helper.IsConnection) { DatabaseLibrary setting = new DatabaseLibrary(); setting.SaveConnectionString("cn", connectionString); MessageBox.Show("Connection Save!"); } } 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 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 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); } }
public void RefreshListData() { listView1.Items.Clear(); DatabaseLibrary Obj = new DatabaseLibrary(); string query = @"SELECT tblndc.`TripNum` AS TripSum,tblndc.`DispDate`,tblndc.`Waybill`,tblndc.`BodyNum`,tblndc.`PlateNum`, tblndc.`Driver`,tblndc.`Source`,tblndc.`RDD`,tblndc.`CustName`, tbldocu.`GarageIn`,tbldocu.`GarageOut`,tbldocu.`CustomerIn`,tbldocu.`StartUnload`,tbldocu.`FinishUnload`,tbldocu.`ReleaseDoc`, tbldocu.`CustomerOut`,tbldocu.`CDwell`,tbldocu.`TruckAtGarage`,tbldocu.`OfficeReceive`,tbldocu.`DocTransmit`,tbldocu.`Remarks` FROM tblndc INNER JOIN tbldocu ON tblndc.`TripNum`=tbldocu.`TripNum` 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]["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]["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]["CustName"].ToString()); if (Obj.datTab.Rows[i]["GarageIn"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["GarageIn"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["GarageIn"].ToString()); } if (Obj.datTab.Rows[i]["GarageOut"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["GarageOut"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["GarageOut"].ToString()); } if (Obj.datTab.Rows[i]["CustomerIn"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["CustomerIn"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["CustomerIn"].ToString()); } if (Obj.datTab.Rows[i]["StartUnload"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["StartUnload"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["StartUnload"].ToString()); } if (Obj.datTab.Rows[i]["FinishUnload"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["FinishUnload"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["FinishUnload"].ToString()); } if (Obj.datTab.Rows[i]["ReleaseDoc"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["ReleaseDoc"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["ReleaseDoc"].ToString()); } if (Obj.datTab.Rows[i]["CustomerOut"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["CustomerOut"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["CustomerOut"].ToString()); } item.SubItems.Add(Obj.datTab.Rows[i]["CDwell"].ToString()); if (Obj.datTab.Rows[i]["TruckAtGarage"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["TruckAtGarage"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["TruckAtGarage"].ToString()); } if (Obj.datTab.Rows[i]["OfficeReceive"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["OfficeReceive"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["OfficeReceive"].ToString()); } if (Obj.datTab.Rows[i]["DocTransmit"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DocTransmit"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["DocTransmit"].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 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; } }
public void RefreshListData() { listView1.Items.Clear(); DatabaseLibrary Obj = new DatabaseLibrary(); Obj.Connection(); Obj.datAdap = new MySqlDataAdapter(@"SELECT * FROM tblndc ORDER BY RDD DESC", 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]["TripNum"].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]["Helper"].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()); } if (Obj.datTab.Rows[i]["CommitTime"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["CommitTime"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["CommitTime"].ToString()); } item.SubItems.Add(Obj.datTab.Rows[i]["Cases"].ToString()); item.SubItems.Add(Obj.datTab.Rows[i]["TruckReq"].ToString()); item.SubItems.Add(Obj.datTab.Rows[i]["CustName"].ToString()); if (Obj.datTab.Rows[i]["TruckIn"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["TruckIn"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["TruckIn"].ToString()); } if (Obj.datTab.Rows[i]["Checklist"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["Checklist"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["Checklist"].ToString()); } if (Obj.datTab.Rows[i]["SCheck"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["SCheck"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["SCheck"].ToString()); } if (Obj.datTab.Rows[i]["FCheck"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["FCheck"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["FCheck"].ToString()); } if (Obj.datTab.Rows[i]["SLoad"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["SLoad"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["SLoad"].ToString()); } if (Obj.datTab.Rows[i]["FLoad"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["FLoad"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["FLoad"].ToString()); } if (Obj.datTab.Rows[i]["DocRec"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DocRec"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["DocRec"].ToString()); } item.SubItems.Add(Obj.datTab.Rows[i]["INVCDN"].ToString()); if (Obj.datTab.Rows[i]["TruckOut"].ToString() != string.Empty) { item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["TruckOut"].ToString()).ToString("MM/dd/yyyy HH:mm")); } else { item.SubItems.Add(Obj.datTab.Rows[i]["TruckOut"].ToString()); } item.SubItems.Add(Obj.datTab.Rows[i]["SDwell"].ToString()); item.SubItems.Add(Obj.datTab.Rows[i]["LoadNum"].ToString()); listView1.Items.Add(item); } lblCount.Text = listView1.Items.Count.ToString(); } catch (Exception ex) { MessageBox.Show(ex.Message); } Obj.CloseConnection(); }
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(); }
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 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 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); } } }