private void btnUpdate_Click(object sender, EventArgs e)
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
if (txtDN.Text != string.Empty &&
txtSD.Text != string.Empty &&
txtDest.Text != string.Empty)
{
Obj.Edit(@"UPDATE tblrspickup SET TruckIn='" + dtTruckIn.Text + "',SLoad='" + dtSLoad.Text + "',FLoad='" + dtFLoad.Text + "',DocuRec='" + dtDocRec.Text + "',TruckOut='" +
dtTruckOut.Text + "',SDwell='" + txtDwell.Text + "',DNum='" + txtDN.Text + "',SDNum='" + txtSD.Text + "',Destination='" + txtDest.Text + "' WHERE TripNum='" + int.Parse(lblTNum.Text) + "'");
Obj.CloseConnection();
frm1.FillDatas();
this.Close();
}
else
{
MessageBox.Show("Please Enter Information!", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public List <Person> GetContacts()
{
List <Person> people = new List <Person>();
try
{
string query = @"SELECT * FROM PEOPLE";
DatabaseLibrary.ExecSqlReader(query, dr =>
{
while (dr.Read())
{
people.Add(new Person()
{
Id = dr.GetInt32(0),
FirstName = dr.GetString(1).Trim(),
LastName = dr.GetString(2).Trim(),
Address1 = dr.GetString(3).Trim(),
Address2 = dr.GetString(4).Trim(),
City = dr.GetString(5).Trim(),
State = dr.GetString(6).Trim(),
PostalCode = dr.GetString(7).Trim(),
Phone = dr.GetString(8).Trim(),
Email = dr.GetString(9).Trim()
});
}
});
}
catch (Exception ex)
{
Logger.Error(ex);
return(null);
}
return(people);
}
public ActionResult AllCompanies()
{
dbl = new DatabaseLibrary();
List <GlobaCompanyModel> all = dbl.tbl.glblCompany.ToList();
return(View(all));
}
private void WithOfficeDoc()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Connection();
Obj.cmd = new MySqlCommand(@"UPDATE tblrsunload SET DestIn=@DI,SULoad=@SU,FULoad=@FU,DesDoc=@DD,DestOut=@DO,CDwell=@DT WHERE TripNum=@TN", Obj.conn);
Obj.cmd.Parameters.AddWithValue("@DI", dtDesIn.Text);
Obj.cmd.Parameters.AddWithValue("@SU", dtSUnload.Text);
Obj.cmd.Parameters.AddWithValue("@FU", dtFUnload.Text);
Obj.cmd.Parameters.AddWithValue("@DD", dtRDoc.Text);
Obj.cmd.Parameters.AddWithValue("@DO", dtDesOut.Text);
Obj.cmd.Parameters.AddWithValue("@DT", txtDwell.Text);
Obj.cmd.Parameters.AddWithValue("@TN", lblTNum.Text);
Obj.cmd.ExecuteNonQuery();
Obj.CloseConnection();
Obj.Connection();
Obj.Edit(@"UPDATE tblrsdoc SET OfficeRec='" + dtDocRec.Text + "',OffRem='" + txtOffRem.Text + "' ,DocTrans='" + dtTransPoro.Text + "', Remarks='" + txtRem.Text + "' WHERE TripNum='" + int.Parse(lblTNum.Text) + "'");
Obj.CloseConnection();
frm1.FillDatas();
this.Close();
}
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 btnSave_Click(object sender, EventArgs e)
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
if (txtBodyNum.Text != string.Empty &&
txtPlateNum.Text != string.Empty &&
txtDriver.Text != string.Empty &&
txtHelper.Text != string.Empty &&
txtSource.Text != string.Empty &&
txtWeek.Text != string.Empty)
{
Obj.Edit(@"UPDATE tblrsload SET DispDate='" + dtDD.Text + "',BodyNum='" + txtBodyNum.Text + "',PlateNum='" + txtPlateNum.Text + "',Driver='" + txtDriver.Text + "',Helper='" +
txtHelper.Text + "',Waybill='" + txtWaybill.Text + "',Source='" + txtSource.Text + "',Week='" + txtWeek.Text + "',ETA='" + dtETA.Text + "' WHERE TripNum='" + int.Parse(lblTNum.Text) + "'");
Obj.CloseConnection();
frm1.FillDatas();
this.Close();
}
else
{
MessageBox.Show("Please Enter Information!", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
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 RESUPPLY"))
{
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 RESUPPLY " + 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);
}
}
}
public void FillDatas()
{
dataGridView1.Columns.Clear();
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
string query = @"SELECT tblrsload.`TripNum` AS tAll,tblrsload.`DispDate`,tblrsload.`BodyNum`,tblrsload.`PlateNum`,tblrsload.`Driver`,
tblrsload.Helper,tblrsload.`Waybill`,tblrsload.`Source`,tblrsload.`Week`,tblrsload.`ETA`,
tblrspickup.`TruckIn`,tblrspickup.`SLoad`,tblrspickup.`FLoad`,tblrspickup.`DocuRec`,tblrspickup.`TruckOut`,tblrspickup.`SDwell`,
tblrspickup.`DNum`,tblrspickup.`SDNum`,tblrspickup.`Destination`
FROM tblrsload
INNER JOIN tblrspickup ON tblrsload.`TripNum`= tblrspickup.`TripNum`
ORDER BY tblrsload.`ETA` DESC";
try
{
DataGridViewButtonColumn btn = new DataGridViewButtonColumn();
btn.Text = "Edit";
btn.HeaderText = "";
btn.Name = "btnEdit";
btn.Width = 70;
btn.FlatStyle = FlatStyle.Flat;
btn.UseColumnTextForButtonValue = true;
this.dataGridView1.Columns.Add(btn);
DataGridViewButtonColumn btn2 = new DataGridViewButtonColumn();
btn2.Text = "Delete";
btn2.HeaderText = "";
btn2.Name = "btnDelete";
btn2.Width = 70;
btn2.FlatStyle = FlatStyle.Flat;
btn2.UseColumnTextForButtonValue = true;
this.dataGridView1.Columns.Add(btn2);
dataGridView1.DataSource = Obj.ShowData(query);
dataGridView1.Columns[3].HeaderText = "DispatchDate";
dataGridView1.Columns[4].HeaderText = "Body#";
dataGridView1.Columns[5].HeaderText = "Plate#";
dataGridView1.Columns[13].HeaderText = "StartLoading";
dataGridView1.Columns[14].HeaderText = "FinishLoading";
dataGridView1.Columns[15].HeaderText = "DocumentReceive";
dataGridView1.Columns[17].HeaderText = "Dwell Time";
dataGridView1.Columns[18].HeaderText = "DeliveryNote";
dataGridView1.Columns[19].HeaderText = "Ship Doc";
dataGridView1.Columns["tAll"].Visible = false;
dataGridView1.Columns["DispDate"].DefaultCellStyle.Format = "MM/dd/yyyy";
dataGridView1.Columns["TruckIn"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["SLoad"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["FLoad"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["ETA"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["DocuRec"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["TruckOut"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
lblTot.Text = dataGridView1.Rows.Count.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Obj.CloseConnection();
}
public ActionResult AllAdverts()
{
ViewBag.Header = "Tüm İlanlar";
_Session = Session["userInfo"] as SessionModel;
dbl = new DatabaseLibrary(_Session._companyName);
tabels = new TablesController();
List <AdvertModal> all = tabels.tblAdverts.ToList();
return(View(all));
}
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.cmd = new MySqlCommand(@"INSERT INTO tblrsload(TripNum,DispDate,BodyNum,PlateNum,Driver,Helper,Waybill,Source,Week,ETA)VALUES
(@TNum,@DD,@BNum,@PNum,@Drivers,@Helpers,@Wbill,@Source,@Weeks,@ETAs)", Obj.conn);
Obj.cmd.Parameters.AddWithValue("@TNum", tnum);
Obj.cmd.Parameters.AddWithValue("@DD", listView1.Items[i].SubItems[0].Text);
Obj.cmd.Parameters.AddWithValue("@BNum", listView1.Items[i].SubItems[1].Text);
Obj.cmd.Parameters.AddWithValue("@PNum", listView1.Items[i].SubItems[2].Text);
Obj.cmd.Parameters.AddWithValue("@Drivers", listView1.Items[i].SubItems[3].Text);
Obj.cmd.Parameters.AddWithValue("@Helpers", listView1.Items[i].SubItems[4].Text);
Obj.cmd.Parameters.AddWithValue("@Wbill", listView1.Items[i].SubItems[5].Text);
Obj.cmd.Parameters.AddWithValue("@Source", listView1.Items[i].SubItems[6].Text);
Obj.cmd.Parameters.AddWithValue("@Weeks", listView1.Items[i].SubItems[7].Text);
Obj.cmd.Parameters.AddWithValue("@ETAs", listView1.Items[i].SubItems[8].Text);
Obj.cmd.ExecuteNonQuery();
Obj.Insert("INSERT INTO tblrspickup(TripNum)VALUES('" + tnum + "')");
Obj.Insert("INSERT INTO tblrsunload(TripNum)VALUES('" + tnum + "')");
Obj.Insert("INSERT INTO tblrsdoc(TripNum)VALUES('" + tnum + "')");
Obj.CloseConnection();
}
frm1.FillDatas();
this.Close();
MessageBox.Show("Data Saved!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }
}
else
{
}
}
}
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 CountFile()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.datAdap = new MySqlDataAdapter(@"SELECT MAX(TripNum) as Tot FROM tblrsload", 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 btnClear_Click(object sender, EventArgs e)
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Edit(@"UPDATE tblrspickup SET TruckIn=NULL,SLoad=NULL,FLoad=NULL,DocuRec=NULL,TruckOut=NULL,DNum=NULL,SDNum=NULL,Destination=NULL WHERE TripNum='" + int.Parse(lblTNum.Text) + "'");
Obj.CloseConnection();
frm1.FillDatas();
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public int SaveContact(Person person)
{
try
{
string query = @"
INSERT INTO [dbo].[People]
([FirstName]
,[LastName]
,[Address1]
,[Address2]
,[City]
,[State]
,[Zip]
,[Phone]
,[Email])
VALUES
(@FirstName
,@LastName
,@Address1
,@Address2
,@City
,@State
,@Zip
,@Phone
,@Email)";
DatabaseLibrary.ExecSql(query, cmd =>
{
cmd.Parameters.AddWithValue("FirstName", person.FirstName);
cmd.Parameters.AddWithValue("LastName", person.LastName);
cmd.Parameters.AddWithValue("Address1", person.Address1);
cmd.Parameters.AddWithValue("Address2", person.Address2);
cmd.Parameters.AddWithValue("City", person.City);
cmd.Parameters.AddWithValue("State", person.State);
cmd.Parameters.AddWithValue("Zip", person.PostalCode);
cmd.Parameters.AddWithValue("Phone", person.Phone);
cmd.Parameters.AddWithValue("Email", person.Email);
});
}
catch (Exception ex)
{
Logger.Error(ex);
return(-1);
}
return(1);
}
private void UpdateGarageInfo()
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Edit(@"UPDATE tblrsunload SET GarageIn='" + dtGarIn.Text + "',GarageOut='" + dtGarOut.Text + "',GarageRem='" + txtGarRem.Text + "' WHERE TripNum='" + int.Parse(lblTNum.Text) + "'");
Obj.CloseConnection();
frm1.FillDatas();
this.Close();
MessageBox.Show("Success!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void DeleteItems()
{
DialogResult dr = MessageBox.Show(this, "Are you sure to Delete this data?", "Confimation", MessageBoxButtons.YesNo, MessageBoxIcon.Stop);
if (dr == DialogResult.Yes)
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
Obj.Delete(@"DELETE FROM tblrsload WHERE TripNum = '" + dataGridView1.CurrentRow.Cells["tAll"].Value.ToString() + "'");
Obj.Delete(@"DELETE FROM tblrspickup WHERE TripNum = '" + dataGridView1.CurrentRow.Cells["tAll"].Value.ToString() + "'");
Obj.Delete(@"DELETE FROM tblrsunload WHERE TripNum = '" + dataGridView1.CurrentRow.Cells["tAll"].Value.ToString() + "'");
Obj.Delete(@"DELETE FROM tblrsdoc WHERE TripNum = '" + dataGridView1.CurrentRow.Cells["tAll"].Value.ToString() + "'");
Obj.CloseConnection();
FillDatas();
}
else
{
FillDatas();
}
}
private void btnClear_Click(object sender, EventArgs e)
{
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
try
{
Obj.Connection();
Obj.cmd = new MySqlCommand(@"UPDATE tblrsunload SET DestIn=NULL,SULoad=NULL,FULoad=NULL,DesDoc=NULL,DestOut=NULL,CDwell=NULL WHERE TripNum='" + int.Parse(lblTNum.Text) + "'", Obj.conn);
Obj.cmd.ExecuteNonQuery();
Obj.CloseConnection();
Obj.Connection();
Obj.Edit(@"UPDATE tblrsdoc SET OfficeRec=NULL,OffRem=NULL, DocTrans=NULL, Remarks=NULL WHERE TripNum='" + int.Parse(lblTNum.Text) + "'");
Obj.CloseConnection();
frm1.FillDatas();
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public int UpdateContact(Person person)
{
try
{
string query = @"
UPDATE [dbo].[People]
SET [FirstName] = @FirstName
,[LastName] = @LastName
,[Address1] = @Address1
,[Address2] = @Address2
,[City] = @City
,[State] = @State
,[Zip] = @Zip
,[Phone] = @Phone
,[Email] = @Email
WHERE ID = @Id";
DatabaseLibrary.ExecSql(query, cmd =>
{
cmd.Parameters.AddWithValue("Id", person.Id);
cmd.Parameters.AddWithValue("FirstName", person.FirstName);
cmd.Parameters.AddWithValue("LastName", person.LastName);
cmd.Parameters.AddWithValue("Address1", person.Address1);
cmd.Parameters.AddWithValue("Address2", person.Address2);
cmd.Parameters.AddWithValue("City", person.City);
cmd.Parameters.AddWithValue("State", person.State);
cmd.Parameters.AddWithValue("Zip", person.PostalCode);
cmd.Parameters.AddWithValue("Phone", person.Phone);
cmd.Parameters.AddWithValue("Email", person.Email);
});
}
catch (Exception ex)
{
Logger.Error(ex);
return(-1);
}
return(1);
}
protected SessionModel checkCredentials()
{
dbl = new DatabaseLibrary();
if (_companyName != null && _userEmail != null && _password != null)
{
_session = new SessionModel();
int companyID = dbl.tbl.glblCompany.Where(u => u.companyName == _companyName).Select(u => u.companyID).SingleOrDefault();
List <GlobalUsersModel> list = dbl.tbl.glblUsers.Where(u => u.userEmail == _userEmail && u.userPassword == _password && u.companyID == companyID).ToList();
if (list.Count == 1)
{
int userRoleID = list[0].userRoleID;
string roleName = dbl.tbl.glblUserRoles.Where(u => u.userRoleID == userRoleID).Select(u => u.userRoleName).SingleOrDefault();
_session = new SessionModel()
{
_userInfo = list[0],
_roleName = roleName,
_companyName = _companyName
};
}
}
return(_session);
}
private void FillRDD()
{
DatabaseLibrary Obj = new DatabaseLibrary();
listView1.Items.Clear();
string query = @"SELECT tblrsload.`DispDate`,tblrsload.`BodyNum`,tblrsload.`PlateNum`,tblrsload.`Driver`,tblrsload.`Helper`,tblrsload.`Waybill`,tblrsload.`Source`,tblrsload.`Week`,tblrsload.`ETA`,
tblrspickup.`TruckIn`,tblrspickup.`SLoad`,tblrspickup.`FLoad`,tblrspickup.`DocuRec`,tblrspickup.`TruckOut`,tblrspickup.`SDwell`,tblrspickup.`DNum`,tblrspickup.`SDNum`,tblrspickup.`Destination`,
tblrsunload.`GarageIn`,tblrsunload.`GarageOut`,tblrsunload.`GarageRem`,tblrsunload.`DestIn`,tblrsunload.`SULoad`,tblrsunload.`FULoad`,tblrsunload.`DesDoc`,tblrsunload.`DestOut`,tblrsunload.`CDwell`,
tblrsdoc.`OfficeRec`,tblrsdoc.`OffRem`,tblrsdoc.`DocTrans`,tblrsdoc.`Remarks`
FROM tblrsload
INNER JOIN tblrspickup ON tblrsload.`TripNum`=tblrspickup.`TripNum`
INNER JOIN tblrsunload ON tblrsload.`TripNum`=tblrsunload.`TripNum`
INNER JOIN tblrsdoc ON tblrsload.`TripNum`=tblrsdoc.`TripNum`
WHERE DATE(tblrsload.`ETA`)='" + dtRDD.Text + "'ORDER BY tblrsload.`ETA` 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(DateTime.Parse(Obj.datTab.Rows[i]["DispDate"].ToString()).ToString("MM/dd/yyyy"));
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]["Waybill"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["Source"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["Week"].ToString());
if (Obj.datTab.Rows[i]["ETA"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["ETA"].ToString()).ToString("HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["ETA"].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]["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]["DocuRec"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DocuRec"].ToString()).ToString("MM/dd/yyyy HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["DocuRec"].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]["DNum"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["SDNum"].ToString());
item.SubItems.Add(Obj.datTab.Rows[i]["Destination"].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());
}
item.SubItems.Add(Obj.datTab.Rows[i]["GarageRem"].ToString());
if (Obj.datTab.Rows[i]["DestIn"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DestIn"].ToString()).ToString("MM/dd/yyyy HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["DestIn"].ToString());
}
if (Obj.datTab.Rows[i]["SULoad"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["SULoad"].ToString()).ToString("MM/dd/yyyy HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["SULoad"].ToString());
}
if (Obj.datTab.Rows[i]["FULoad"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["FULoad"].ToString()).ToString("MM/dd/yyyy HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["FULoad"].ToString());
}
if (Obj.datTab.Rows[i]["DesDoc"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DesDoc"].ToString()).ToString("MM/dd/yyyy HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["DesDoc"].ToString());
}
if (Obj.datTab.Rows[i]["DestOut"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DestOut"].ToString()).ToString("MM/dd/yyyy HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["DestOut"].ToString());
}
item.SubItems.Add(Obj.datTab.Rows[i]["CDwell"].ToString());
if (Obj.datTab.Rows[i]["OfficeRec"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["OfficeRec"].ToString()).ToString("MM/dd/yyyy HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["OfficeRec"].ToString());
}
item.SubItems.Add(Obj.datTab.Rows[i]["OffRem"].ToString());
if (Obj.datTab.Rows[i]["DocTrans"].ToString() != string.Empty)
{
item.SubItems.Add(DateTime.Parse(Obj.datTab.Rows[i]["DocTrans"].ToString()).ToString("MM/dd/yyyy HH:mm"));
}
else
{
item.SubItems.Add(Obj.datTab.Rows[i]["DocTrans"].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();
}
public void FillRDD()
{
dataGridView1.Columns.Clear();
DatabaseLibrary Obj = new DatabaseLibrary();
Obj.Connection();
string query = @"SELECT tblrsunload.`TripNum` AS tAll,tblrsload.`DispDate`,tblrsload.`BodyNum`,tblrsload.`PlateNum`,tblrsload.`Driver`,tblrsload.`Source`,tblrsload.`ETA`,
tblrspickup.`DNum`,tblrspickup.`SDNum`,tblrspickup.`Destination`,tblrsunload.`GarageIn`,tblrsunload.`GarageOut`,
tblrsunload.`GarageRem`,tblrsunload.`DestIn`,tblrsunload.`SULoad`,tblrsunload.`FULoad`,tblrsunload.`DesDoc`,tblrsunload.`DestOut`,tblrsunload.`CDwell`,
tblrsdoc.`OfficeRec`,tblrsdoc.`OffRem`,tblrsdoc.`DocTrans`,tblrsdoc.`Remarks`
FROM tblrsload
INNER JOIN tblrspickup ON tblrsload.`TripNum`= tblrspickup.`TripNum`
INNER JOIN tblrsunload ON tblrsload.`TripNum`= tblrsunload.`TripNum`
INNER JOIN tblrsdoc ON tblrsload.`TripNum`= tblrsdoc.`TripNum`
WHERE DATE(tblrsload.`ETA`)='" + dtRDD.Text + "'";
try
{
DataGridViewButtonColumn btn = new DataGridViewButtonColumn();
btn.Text = " Edit ";
btn.HeaderText = "";
btn.Name = "btnEdit";
btn.Width = 100;
btn.FlatStyle = FlatStyle.Flat;
btn.UseColumnTextForButtonValue = true;
this.dataGridView1.Columns.Add(btn);
dataGridView1.DataSource = Obj.ShowData(query);
lblTot.Text = dataGridView1.Rows.Count.ToString();
dataGridView1.Columns[2].HeaderText = "DispatchDate";
dataGridView1.Columns[3].HeaderText = "Body#";
dataGridView1.Columns[4].HeaderText = "Plate#";
dataGridView1.Columns[8].HeaderText = "DeliveryNote";
dataGridView1.Columns[9].HeaderText = "Ship Doc";
dataGridView1.Columns[11].HeaderText = "Garage In";
dataGridView1.Columns[12].HeaderText = "Garage Out";
dataGridView1.Columns[13].HeaderText = "Remarks";
dataGridView1.Columns[14].HeaderText = "Destination In";
dataGridView1.Columns[15].HeaderText = "Start Unload";
dataGridView1.Columns[16].HeaderText = "Finish Unload";
dataGridView1.Columns[17].HeaderText = "Release Docu";
dataGridView1.Columns[18].HeaderText = "DestinationOut";
dataGridView1.Columns[19].HeaderText = "Dwell Time";
dataGridView1.Columns[20].HeaderText = "DocReceiveAtOffice";
dataGridView1.Columns[21].HeaderText = "Remarks";
dataGridView1.Columns[22].HeaderText = "DocTransmitToPoro";
dataGridView1.Columns["DispDate"].DefaultCellStyle.Format = "MM/dd/yyyy";
dataGridView1.Columns["ETA"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["GarageIn"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["GarageOut"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["DestIn"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["SULoad"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["FULoad"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["DesDoc"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["DestOut"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["OfficeRec"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["DocTrans"].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm";
dataGridView1.Columns["tAll"].Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Obj.CloseConnection();
}
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[21].Text != string.Empty &&
item.SubItems[22].Text != string.Empty &&
item.SubItems[23].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[29].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 RESUPPLY " + 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;
}
}
