protected void AutoRefreshTimer_Tick(object sender, EventArgs e)
{
QueueView();
GridLocal.DataBind();
GridExp.DataBind();
}
protected void AutoRefreshTimer_Tick(object sender, EventArgs e)
{
GridExp.DataBind();
GridLoc.DataBind();
if (IsPostBack)
{
checkExp();
checkLoc();
}
QueueView();
}
protected void GridExp_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "DeleteRecords")
{
DataTable ExpTable = (DataTable)ViewState["ExpTable"];
ExpTable.Rows.RemoveAt(int.Parse(e.CommandArgument.ToString()));
ViewState["ExpTable"] = ExpTable;
GridExp.DataSource = ExpTable;
GridExp.DataBind();
}
}
protected void clearGrid() // clear all grid or view state all tab data
{
grdB2B.DataSource = null;
grdB2B.DataBind();
GridB2cl.DataSource = null;
GridB2cl.DataBind();
GridB2cs.DataSource = null;
GridB2cs.DataBind();
GridCdnr.DataSource = null;
GridCdnr.DataBind();
GridCdnur.DataSource = null;
GridCdnur.DataBind();
GridExp.DataSource = null;
GridExp.DataBind();
GridAt.DataSource = null;
GridAt.DataBind();
GridAtAdj.DataSource = null;
GridAtAdj.DataBind();
GridHsn.DataSource = null;
GridHsn.DataBind();
GridExeMp.DataSource = null;
GridExeMp.DataBind();
GridDocs.DataSource = null;
GridDocs.DataBind();
ViewState["B2BTable"] = null;
ViewState["B2ClTable"] = null;
ViewState["B2CSTable"] = null;
ViewState["CDNRTable"] = null;
ViewState["CDNURTable"] = null;
ViewState["ExpTable"] = null;
ViewState["AtTable"] = null;
ViewState["AtAdjTable"] = null;
ViewState["HsnTable"] = null;
ViewState["ExeMpTable"] = null;
ViewState["DocsTable"] = null;
ClearB2B();
ClearB2cl();
ClearB2cs();
ClearCdnur();
ClearExp();
ClearAt();
ClearDocs();
ClearAtAdj();
ClearExeMp();
ClearHsn();
ClearCdnr();
}
protected void ChkExpReq_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
GridExp.DataBind();
}
}
protected void btnExpAdd_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
lblCdnrErrorMsg.Text = "";
if (ViewState["ExpTable"] == null)
{
dt.Columns.Add("Export Type");
dt.Columns.Add("Invoice Number");
dt.Columns.Add("Invoice date");
dt.Columns.Add("Invoice Value");
dt.Columns.Add("Port Code");
dt.Columns.Add("Shipping Bill Number");
dt.Columns.Add("Shipping Bill Date");
dt.Columns.Add("Rate");
dt.Columns.Add("Taxable Value");
}
else
{
dt = (DataTable)ViewState["ExpTable"];
}
DataRow dr = dt.NewRow();
dr["Export Type"] = ddlExpExportType.SelectedItem.Text.Trim();
dr["Invoice Number"] = txtExpInvoiceNo.Text;
dr["Invoice date"] = CLSCommon.DateFormat(txtExpInvoiceDate.Text);
dr["Invoice Value"] = txtExpInvoiceValue.Text.Trim() == "" ? "0" : txtExpInvoiceValue.Text.Trim();
dr["Port Code"] = txtExpPortCode.Text.Trim();
dr["Shipping Bill Number"] = txtExpShippingBillNo.Text;
dr["Shipping Bill Date"] = CLSCommon.DateFormat(txtExpShippingBillDate.Text.ToString());
dr["Rate"] = ddltxtExpRate.SelectedItem.Text.ToString();
dr["Taxable Value"] = txtExpTaxableValue.Text;
dt.Rows.Add(dr);
ViewState["ExpTable"] = dt;
GridExp.DataSource = dt;
GridExp.DataBind();
ClearExp();
}
protected void CheckExpApp_Click(object sender, EventArgs e)
{
if (IsPostBack)
{
var strSql = new StringBuilder(string.Empty);
var con = new SqlConnection(strConnection);
var conQueue = new SqlConnection(strConnection);
var cmd = new SqlCommand();
var CheckExpApp = (Button)sender;
var grdRow = (GridViewRow)CheckExpApp.NamingContainer;
var lblID = (Label)grdRow.FindControl("LabelID");
var cusTime = DateTime.Now;
var named = User.Identity.Name;
try
{
ApproveSecurity(con, conQueue, cmd, lblID, cusTime, named);
}
catch (SqlException ex)
{
var errorMsg = "Error in Updation";
errorMsg += ex.Message;
throw new Exception(errorMsg);
}
finally
{
con.Close();
conQueue.Close();
GridExp.DataBind();
}
}
}
private void RadioCheck(object sender)
{
var strSql = new StringBuilder(string.Empty);
var con = new SqlConnection(strConnection);
var conQueue = new SqlConnection(strConnection);
var cmd = new SqlCommand();
//CheckBox CheckNo = (CheckBox)sender;
var rdbl = (RadioButtonList)sender;
var grdRow = (GridViewRow)rdbl.NamingContainer;
//CheckBox chkNo = (CheckBox)grdRow.FindControl("CheckNo");
var chkRl = (RadioButtonList)grdRow.FindControl("RadioButtonList1");
var lblID = (Label)grdRow.FindControl("LabelID");
var cusTime = DateTime.Now;
var named = User.Identity.Name;
if (chkRl.SelectedValue == "False")
{
try
{
const string strQueueUpdate =
"Update NewQueue set Status = @Status, StatusTime = @StatusTime WHERE QueueID = @QueueID";
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQueueUpdate;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Status", "SECTNA");
cmd.Parameters.AddWithValue("@StatusTime", cusTime);
cmd.Parameters.AddWithValue("@QueueID", lblID.Text);
cmd.Connection = conQueue;
conQueue.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
var errorMsg = "Error in Updation";
errorMsg += ex.Message;
throw new Exception(errorMsg);
}
finally
{
con.Close();
conQueue.Close();
GridLoc.DataBind();
GridExp.DataBind();
}
}
else if (chkRl.SelectedValue == "True")
{
try
{
const string strQueueUpdate = "Update NewQueue set Status = @Status WHERE QueueID = @QueueID";
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQueueUpdate;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Status", "SECTA");
cmd.Parameters.AddWithValue("@QueueID", lblID.Text);
cmd.Connection = conQueue;
conQueue.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
var errorMsg = "Error in Updation";
errorMsg += ex.Message;
throw new Exception(errorMsg);
}
finally
{
con.Close();
conQueue.Close();
//GridLoc.DataBind();
//GridExp.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 ButtonExp_Click(object sender, EventArgs e)
{
var connectionString = "";
try
{
if (FileUploadExp.HasFile)
{
if (ValidExport(FileUploadExp))
{
var fileName = Path.GetFileName(FileUploadExp.PostedFile.FileName);
var fileExtension = Path.GetExtension(FileUploadExp.PostedFile.FileName);
var fileLocation = Server.MapPath("~/App_Data/" + fileName);
FileUploadExp.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 [EXPORT$A3:G] WHERE F1 IS NOT NULL";
dAdapter.SelectCommand = cmd;
var dsbd = new DataSet();
dAdapter.Fill(dsbd);
var status = SaveExport(dsbd);
if (status == 1)
{
GridExp.DataBind();
if (GridExp.Rows.Count >= 1)
{
//show grid results
lblmessageExp.Text = GridExp.Rows.Count + " Duplicates detected.";
var connectExp1 =
ConfigurationManager.ConnectionStrings["SecureConnectionString"].ConnectionString;
var deleteExport1 = "DELETE FROM ExportQueue";
using (var myExpConnection1 = new SqlConnection(connectExp1))
{
myExpConnection1.Open();
var myExpCommand1 = new SqlCommand(deleteExport1, myExpConnection1);
myExpCommand1.ExecuteNonQuery();
myExpConnection1.Close();
}
}
else
{
//Fill dataset from staging table
var adapterDb = new SqlDataAdapter("Select * FROM ExportQueue", strConnection);
var datasetDb = new DataSet();
adapterDb.Fill(datasetDb);
//Check integer status returned
var newstatus = SaveNewExport(datasetDb);
if (newstatus == 1)
{
//Delete exportQueue
var connectExp =
ConfigurationManager.ConnectionStrings["SecureConnectionString"]
.ConnectionString;
var deleteExport = "DELETE FROM ExportQueue";
using (var myExpConnection = new SqlConnection(connectExp))
{
myExpConnection.Open();
var myExpCommand = new SqlCommand(deleteExport, myExpConnection);
myExpCommand.ExecuteNonQuery();
myExpConnection.Close();
}
lblmessageExp.Text = "Export Queue updated successfully.";
}
}
}
else
{
lblmessageExp.Text = "Upload Error.Please try again";
}
con.Close();
}
}
else
{
lblmessageExp.Text = "Please select an Excel file to upload.";
}
}
catch (Exception ex)
{
lblmessageExp.Text = "Invalid Excel document.";
}
}