protected void grdMain_RowCommand(object sender, GridViewCommandEventArgs e)
{
string strMsg = string.Empty;
if (e.CommandName == "cmddelete")
{
Int64 UserIdno = Convert.ToInt64(Session["UserIdno"]);
GrPrepOntohireDAL obj = new GrPrepOntohireDAL();
Int32 intValue = obj.DeleteHireGrDetail(Convert.ToInt32(e.CommandArgument));
obj = null;
if (intValue > 0)
{
this.BindGrid();
strMsg = "Record deleted successfully.";
}
else
{
if (intValue == -1)
{
strMsg = "Record can not be deleted. It is in use.";
}
else
{
strMsg = "Record not deleted.";
}
}
ScriptManager.RegisterStartupScript(this, this.GetType(), "alertstrMsg", "PassMessage('" + strMsg + "')", true);
}
}
private void BindGrid()
{
GrPrepOntohireDAL obj = new GrPrepOntohireDAL();
DataTable dtRcptDetl = new DataTable();
dtRcptDetl = obj.SelectGrPrep(Convert.ToInt64(ddldateRange.SelectedValue), txtReceiptDatefrom.Text, txtReceiptDateto.Text, ApplicationFunction.ConnectionString());
if ((dtRcptDetl != null && dtRcptDetl.Rows.Count > 0))
{
grdMain.DataSource = dtRcptDetl;
grdMain.DataBind();
lblTotalRecord.Text = "T. Record (s): " + dtRcptDetl.Rows.Count;
Double TotalNetAmount = 0;
for (int i = 0; i < dtRcptDetl.Rows.Count; i++)
{
TotalNetAmount += Convert.ToDouble((dtRcptDetl.Rows[i]["Total_Amnt"]));
}
lblNetTotalAmount.Text = TotalNetAmount.ToString("N2");
int startRowOnPage = (grdMain.PageIndex * grdMain.PageSize) + 1;
int lastRowOnPage = startRowOnPage + grdMain.Rows.Count - 1;
lblcontant.Text = "Showing " + startRowOnPage.ToString() + " - " + lastRowOnPage.ToString() + " of " + dtRcptDetl.Rows.Count.ToString();
lblcontant.Visible = true;
divpaging.Visible = true;
imgBtnExcel.Visible = true;
ViewState["Dt"] = dtRcptDetl;
}
else
{
grdMain.DataSource = null;
grdMain.DataBind();
lblTotalRecord.Text = "T. Record (s): 0 ";
lblcontant.Visible = false;
divpaging.Visible = false;
imgBtnExcel.Visible = false;
}
}
protected void lnkbtnUpload_Click(object sender, EventArgs e)
{
string msg = string.Empty;
if (FileUpload.HasFile)
{
GrPrepOntohireDAL obj = new GrPrepOntohireDAL();
string excelfilename = string.Empty;
#region UPLOAD EXCEL AT SERVER
excelfilename = ApplicationFunction.UploadFileServerControl(FileUpload, "ItemsGronhire", "Grprepexcel");
#endregion
if ((System.IO.Path.GetExtension(excelfilename) == ".xls") || (System.IO.Path.GetExtension(excelfilename) == ".xlsx"))
{
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
BindDropdownDAL objDal = new BindDropdownDAL();
string filepath = Server.MapPath(@"~/ItemsGronhire/" + excelfilename);
string constring = string.Empty;
if (System.IO.Path.GetExtension(filepath) == ".xls")
{
constring = "Provider=Microsoft.Jet.OLEDB.4.0;OLE DB Services=-4;Data Source='" + filepath + "';Extended Properties=\"Excel 8.0;HDR=Yes;\"";
}
else if (System.IO.Path.GetExtension(filepath) == ".xlsx")
{
constring = "Provider= Microsoft.ACE.OLEDB.12.0;OLE DB Services=-4;Data Source='" + filepath + "'; Extended Properties=\"Excel 12.0;HDR=YES;\"";
}
#region Select Excel
OleDbConnection con = new OleDbConnection(constring);
con.Open();
DataTable ExcelTable = new DataTable();
ExcelTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string SheetName = Convert.ToString(ExcelTable.Rows[0][2]);
OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + SheetName + "] WHERE [GRNo] IS NOT NULL OR [GRDate] IS NOT NULL OR [CHDate] IS NOT NULL OR [CHNo] IS NOT NULL OR [From] IS NOT NULL OR [To] IS NOT NULL OR [LrryNo] IS NOT NULL OR [OwnerName] IS NOT NULL OR [PanNo] IS NOT NULL OR [DspQty] IS NOT NULL OR [Rate] IS NOT NULL OR [Amount] IS NOT NULL", con); DataSet ds = new DataSet();
da.Fill(ds);
#endregion
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0 && ds.Tables[0].Columns[0].Caption == "SrNo" && ds.Tables[0].Columns[1].Caption == "GRNo" && ds.Tables[0].Columns[2].Caption == "GRDate" && ds.Tables[0].Columns[3].Caption == "CHDate" && ds.Tables[0].Columns[4].Caption == "CHNo" && ds.Tables[0].Columns[5].Caption == "From" && ds.Tables[0].Columns[6].Caption == "To" && ds.Tables[0].Columns[7].Caption == "LrryNo" && ds.Tables[0].Columns[8].Caption == "OwnerName" && ds.Tables[0].Columns[9].Caption == "PanNo" && ds.Tables[0].Columns[10].Caption == "DspQty" && ds.Tables[0].Columns[11].Caption == "Rate" && ds.Tables[0].Columns[12].Caption == "Amount")
{
#region INSERT RECORD IN tblChlnUploadFromExcel TABLE
Int64 intResult = 0;
using (TransactionScope Tran = new TransactionScope(TransactionScopeOption.Required))
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
intResult = obj.InsertInGrExcel(
Convert.ToDateTime(Convert.ToString(ds.Tables[0].Rows[i]["GRDate"].ToString() == "" ? "" : ds.Tables[0].Rows[i]["GRDate"])),
Convert.ToInt64(Convert.ToString(ds.Tables[0].Rows[i]["GRNo"]) == "" ? "0" : ds.Tables[0].Rows[i]["GRNo"]),
Convert.ToDateTime(Convert.ToString(ds.Tables[0].Rows[i]["CHDate"].ToString() == "" ? "" : ds.Tables[0].Rows[i]["CHDate"])),
Convert.ToInt64(Convert.ToString(ds.Tables[0].Rows[i]["CHNo"]) == "" ? "0" : ds.Tables[0].Rows[i]["CHNo"]),
Convert.ToString(Convert.ToString(ds.Tables[0].Rows[i]["From"]) == "" ? "" : ds.Tables[0].Rows[i]["From"]),
Convert.ToString(Convert.ToString(ds.Tables[0].Rows[i]["To"]) == "" ? "" : ds.Tables[0].Rows[i]["To"]),
Convert.ToString(Convert.ToString(ds.Tables[0].Rows[i]["LrryNo"]) == "" ? "" : ds.Tables[0].Rows[i]["LrryNo"]),
Convert.ToString(Convert.ToString(ds.Tables[0].Rows[i]["OwnerName"]) == "" ? "" : ds.Tables[0].Rows[i]["OwnerName"]),
Convert.ToString(Convert.ToString(ds.Tables[0].Rows[i]["PanNo"]) == "" ? "" : ds.Tables[0].Rows[i]["PanNo"]),
Convert.ToInt64(Convert.ToString(ds.Tables[0].Rows[i]["DspQty"]) == "" ? "0" : ds.Tables[0].Rows[i]["DspQty"]),
Convert.ToDouble(Convert.ToString(ds.Tables[0].Rows[i]["Rate"]) == "" ? "" : ds.Tables[0].Rows[i]["Rate"]),
Convert.ToDouble(Convert.ToString(ds.Tables[0].Rows[i]["Amount"]) == "" ? "" : ds.Tables[0].Rows[i]["Amount"])
, Convert.ToInt64(ddldateRange.SelectedValue == "" ? "0" : ddldateRange.SelectedValue));
}
if (intResult > 0)
{
msg = "Excel uploaded successfully";
ShowMessage(msg);
Tran.Complete();
}
else
{
Tran.Dispose();
}
}
#endregion
}
else
{
msg = "Excel is blank or Excel is not in correct format.";
ShowMessageErr(msg);
return;
}
}
}
else
{
msg = "Please Upload Correct Excel File";
ShowMessageErr(msg);
return;
}
}
}
