protected void AutoRefreshTimer_Tick(object sender, EventArgs e)
{
QueueView();
GridLocal.DataBind();
GridExp.DataBind();
}
protected void ChkLocReq_CheckedChanged(object sender, EventArgs e)
{
if (IsPostBack)
{
var strSql = new StringBuilder(string.Empty);
var con = new SqlConnection(strConnection);
var cmd = new SqlCommand();
var ChkRequeue = (CheckBox)sender;
var grdRow = (GridViewRow)ChkRequeue.NamingContainer;
var lblID = (Label)grdRow.FindControl("Que_ID");
RequeueTruck(con, cmd, ChkRequeue, lblID);
//update grid
GridLocal.DataBind();
}
}
private void QueueOrder(object sender, string type, int total)
{
if (IsPostBack)
{
var conSAP = new SqlConnection(strConnection);
var cmd = new SqlCommand();
var btnQueue = (Button)sender;
var grdRow = (GridViewRow)btnQueue.NamingContainer;
var lblSAPid = (Label)grdRow.FindControl("SAP_ID");
var Shipper = (Label)grdRow.FindControl("SHIPPER");
var Registration = (Label)grdRow.FindControl("REGISTRATION");
var LO_NO = (Label)grdRow.FindControl("LONO");
var MSP = (Label)grdRow.FindControl("MSP");
var AGO = (Label)grdRow.FindControl("AGO");
var KERO = (Label)grdRow.FindControl("KERO");
var JET = (Label)grdRow.FindControl("JET");
var DrNo = (Label)grdRow.FindControl("DRIVER_NUMBER");
var TrailerNo = (Label)grdRow.FindControl("TRAILER_TEXT");
//TextBox txtNo = (TextBox)grdRow.FindControl("txtNo");
total = total + 1;
var named = User.Identity.Name;
var newQueueId = Guid.NewGuid().ToString();
var statusTime = DateTime.Now;
var setDate = DateTime.Today;
try
{
var query =
"INSERT INTO NewQueue([SAP_Request_No],[Queue_No],[Shipper],[Registration],[Type],[LO_NO],[MSP],[AGO],[KERO],[JET],[Status],[QueueID],[EnterName],[SetDate],[StatusTime],[DRIVER_NUMBER],[TRAILER_TEXT]) values('" +
lblSAPid.Text + "','" + total + "','" + Shipper.Text + "','" + Registration.Text + "','" + type +
"','" + LO_NO.Text + "','" + MSP.Text + "','" + AGO.Text + "','" + KERO.Text + "','" + JET.Text +
"','SECTA','" + newQueueId + "','" + named + "','" + setDate + "','" + statusTime + "','" +
DrNo.Text + "','" + TrailerNo.Text + "');";
var objda = new SqlDataAdapter(query, objcon);
objcon.Open();
objda.SelectCommand.ExecuteNonQuery();
objcon.Close();
//add unique guid to all tables
AddGuidToTables(newQueueId);
//then sap status
UpdateSAP(conSAP, cmd, lblSAPid);
}
catch (SqlException ex)
{
var errorMsg = "Error in Updation";
errorMsg += ex.Message;
throw new Exception(errorMsg);
}
finally
{
conSAP.Close();
GridLocal.DataBind();
GridExp.DataBind();
}
}
}
protected void AutoRefreshTimer_Tick(object sender, EventArgs e)
{
GridLocal.DataBind();
}
protected void ButtonLoc_Click(object sender, EventArgs e)
{
var connectionString = "";
try
{
if (FileUploadLoc.HasFile)
{
if (ValidExport(FileUploadLoc))
{
var fileName = Path.GetFileName(FileUploadLoc.PostedFile.FileName);
var fileExtension = Path.GetExtension(FileUploadLoc.PostedFile.FileName);
var fileLocation = Server.MapPath("~/App_Data/" + fileName);
FileUploadLoc.SaveAs(fileLocation);
//Check whether file extension is xls or xslx
if (fileExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation +
";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation +
";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=2\"";
}
//Create OleDB Connection and OleDb Command
var con = new OleDbConnection(connectionString);
var cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
var dAdapter = new OleDbDataAdapter(cmd);
var dtExcelRecords = new DataTable();
con.Open();
var dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [LOCAL$A3:H] WHERE F1 IS NOT NULL";
dAdapter.SelectCommand = cmd;
var dsbd = new DataSet();
dAdapter.Fill(dsbd);
var status = SaveLocal(dsbd);
if (status == 1)
{
GridLocal.DataBind();
if (GridLocal.Rows.Count >= 1)
{
//show grid results
lblmessageLoc.Text = GridLocal.Rows.Count + " Duplicates detected.";
var connectLoc1 =
ConfigurationManager.ConnectionStrings["SecureConnectionString"].ConnectionString;
var deleteLocal1 = "DELETE FROM LocalQueue";
using (var myLocConnection1 = new SqlConnection(connectLoc1))
{
myLocConnection1.Open();
var myLocCommand1 = new SqlCommand(deleteLocal1, myLocConnection1);
myLocCommand1.ExecuteNonQuery();
myLocConnection1.Close();
}
}
else
{
//Fill dataset from staging table
var adapterDb = new SqlDataAdapter("Select * FROM LocalQueue", strConnection);
var datasetDb = new DataSet();
adapterDb.Fill(datasetDb);
//Check integer status returned
var newstatus = SaveNewLocal(datasetDb);
if (newstatus == 1)
{
//Delete localQueue
var connectLoc =
ConfigurationManager.ConnectionStrings["SecureConnectionString"]
.ConnectionString;
var deleteLocal = "DELETE FROM LocalQueue";
using (var myLocConnection = new SqlConnection(connectLoc))
{
myLocConnection.Open();
var myLocCommand = new SqlCommand(deleteLocal, myLocConnection);
myLocCommand.ExecuteNonQuery();
myLocConnection.Close();
}
lblmessageLoc.Text = "Local Queue updated successfully.";
}
}
}
else
{
lblmessageLoc.Text = "Upload Error.Please try again";
}
con.Close();
}
}
else
{
lblmessageLoc.Text = "Please select an Excel file to upload.";
}
}
catch (Exception ex)
{
lblmessageLoc.Text = "Invalid Excel document.";
}
}