protected void _btnDelete_Click(object sender, EventArgs e)
{
int index = Convert.ToInt32(hdnFieldOTEOID.Value);
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
SqlCommand cmd1 = conn.CreateCommand();
cmd1.CommandText = "SELECT COUNT (*) FROM ReceivedItemsDetails INNER JOIN ReceivedItemsOTEO ON ReceivedItemsDetails.ReceivedItemsOTEOID = ReceivedItemsOTEO.ReceivedItemsOTEOID WHERE (ReceivedItemsDetails.ReceivedItemsOTEOID = @ReceivedItemsOTEOID)";
cmd1.Parameters.AddWithValue("@ReceivedItemsOTEOID", index);
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = "DELETE FROM [ReceivedItemsOTEO] WHERE [ReceivedItemsOTEOID] = @ReceivedItemsOTEOID";
cmd2.Parameters.AddWithValue("@ReceivedItemsOTEOID", index);
conn.Open();
int count = Convert.ToInt32(cmd1.ExecuteScalar());
if (count < 1)
{
cmd2.ExecuteNonQuery();
conn.Close();
Response.Redirect("ReceivedEntriesList.aspx");
}
}
protected void _btnDelete_Click(object sender, EventArgs e)
{
decimal index = Convert.ToDecimal(Request.QueryString["challanid"]);
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
SqlCommand cmd1 = conn.CreateCommand();
cmd1.CommandText = "Select count(*) as count from DeliveryItemsDetails where DeliveryItemsChallanID = @DeliveryItemsChallanID";
cmd1.Parameters.AddWithValue("@DeliveryItemsChallanID", index);
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = "DELETE FROM [DeliveryItemsChallan] WHERE [DeliveryItemsChallanID] = @DeliveryItemsChallanID";
cmd2.Parameters.AddWithValue("@DeliveryItemsChallanID", index);
conn.Open();
int count = Convert.ToInt32(cmd1.ExecuteScalar());
if (count < 1)
{
cmd2.ExecuteNonQuery();
conn.Close();
Response.Redirect("IssueEntriesList.aspx");
}
}
//1 primary table content
private void GetIssuedItemsDetails(decimal challanID) //string challanID
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
SqlDataReader dr = null;
try
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT IndentReference, IndentDate, ChallanDate, IndentingDivisionName, ChargeableHeadName, TotalAmount, IsDeliveredTemporary, vehiclenumber, receiverdesignation,Remarks FROM DeliveryItemsChallan where deliveryitemschallanID =@deliveryitemschallanID";
cmd.Parameters.AddWithValue("@deliveryitemschallanID", challanID);
conn.Open();
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read()) //ri.ChargeableHeadName, ri.IssueHeadName,
{
// hdnFieldChallanID.Value= _tbChalanNo.Text = challanID;
hdnFieldChallanID.Value = _tbChalanNo.Text = challanID.ToString();
_tbChallanDate.Text = Convert.ToDateTime(dr["ChallanDate"]).ToString("dd-MM-yyyy");
_tbIndentValue.Text = dr["IndentReference"].ToString();
_tbIntendDate.Text = Convert.ToDateTime(dr["IndentDate"]).ToString("dd-MM-yyyy");
// _ddIntendDivisions.SelectedValue = dr["ChargeableHeadID"].ToString(); instead of using ddl using label to display old data and giving option ddl seperately to change if user wants to
lblDivisionOld.Text = dr["IndentingDivisionName"].ToString();
lblChHeadOld.Text = dr["ChargeableHeadName"].ToString();
_tbtotalAmount.Text = dr["TotalAmount"].ToString();
tbVehicleNumberCaps.Text = dr["vehiclenumber"].ToString();
tbReceiverDesignation.Text = dr["receiverdesignation"].ToString();
tbremarks.Text = dr["Remarks"].ToString();
string isdelivered = dr["IsDeliveredTemporary"].ToString();
if (isdelivered == "Yes") //1 is for true
{
istemporary.Checked = true;
}
}
}
}
catch
{
throw;
}
finally
{
dr.Close();
conn.Close();
}
}
private void GetRecievedItemsDetails(string oteoID)
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
SqlDataReader dr = null;
try
{
SqlCommand cmd = conn.CreateCommand();
//cmd.CommandText = "select ri.ReceivedItemOTEODate,ri.SupplyOrderReference,ri.SupplyOrderDate, ri.Supplier, ri.TotalAmount, ch.ChargeableHeadID, ch.IssueHeadID from ReceivedItemsOTEO ri, ChargeableHeads ch where ri.ChargeableHeadName= ch.ChargeableHeadName and ri.ReceivedItemsOTEOID=@ReceivedItemsOTEOID";
cmd.CommandText = "select ri.ReceivedItemOTEODate,ri.SupplyOrderReference,ri.SupplyOrderDate,ChargeableHeadName, IssueHeadName ,ri.Supplier, ri.TotalAmount from ReceivedItemsOTEO ri where ri.ReceivedItemsOTEOID=@ReceivedItemsOTEOID";
cmd.Parameters.AddWithValue("@ReceivedItemsOTEOID", oteoID);
conn.Open();
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read()) //ri.ChargeableHeadName, ri.IssueHeadName,
{
hdnFieldOTEOID.Value = tbOtEONumber.Text = oteoID;
tbSupplyOrderReference.Text = dr["SupplyOrderReference"].ToString();
tbOTEODate.Text = Convert.ToDateTime(dr["ReceivedItemOTEODate"]).ToString("dd-MM-yyyy");
tbSupplyDate.Text = Convert.ToDateTime(dr["SupplyOrderDate"]).ToString("dd-MM-yyyy");
tbSupplierName.Text = dr["Supplier"].ToString();
// ddlChargeableHead.SelectedValue =
// ddlIssueHead.SelectedValue = dr["IssueHeadID"].ToString();
lblIssueHeadOld.Text = dr["IssueHeadName"].ToString();
lblChargeableHead.Text = dr["ChargeableHeadName"].ToString();
tbtotalAmountAddedItems.Text = dr["totalamount"].ToString();
}
}
}
catch
{
throw;
}
finally
{
dr.Close();
conn.Close();
}
}
private void TakeDBBackup()
{
try
{
string _DatabaseName = AppConns.GetDatabaseName();
string _BackupName = _DatabaseName + "_" + DateTime.Now.ToString("ddMMMMyyyy_hh_mm_tt") + ".bak";
// string _BackupName = _DatabaseName + ".bak";
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = AppConns.GetConnectionString();
string backupFolderPath = Server.MapPath(HttpContext.Current.Request.ApplicationPath + "\\DBBackup\\");
string sqlQuery = "Backup database [" + _DatabaseName + "] to disk='" + backupFolderPath + _BackupName + "'";
SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConnection);
sqlCommand.CommandType = CommandType.Text;
sqlConnection.Open();
int iRows = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
lblmessage.Text = "Back up created successfully.";
}
catch (SqlException ex)
{
throw ex;
}
}
//private void DeleteData(decimal index)
//{
// try
// {
// SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
// SqlCommand cmd2 = conn.CreateCommand();
// conn.Open();
// cmd2.CommandText = "DELETE FROM [ReceivedItemsOTEO] WHERE [ReceivedItemsOTEOID] = @ReceivedItemsOTEOID";
// cmd2.Parameters.AddWithValue("@ReceivedItemsOTEOID", index);
// cmd2.ExecuteNonQuery();
// retriveData();
// }
// catch
// {
// throw;
// }
//}
protected void _rprt_ItemCommand(object sender, ListViewCommandEventArgs e)
{
if (e.CommandName == "Delete")
{
int index = Convert.ToInt32(e.CommandArgument);
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
SqlCommand cmd1 = conn.CreateCommand();
cmd1.CommandText = "SELECT COUNT (*) FROM ReceivedItemsDetails INNER JOIN ReceivedItemsOTEO ON ReceivedItemsDetails.ReceivedItemsOTEOID = ReceivedItemsOTEO.ReceivedItemsOTEOID WHERE (ReceivedItemsDetails.ReceivedItemsOTEOID = @ReceivedItemsOTEOID)";
cmd1.Parameters.AddWithValue("@ReceivedItemsOTEOID", index);
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = "DELETE FROM [ReceivedItemsOTEO] WHERE [ReceivedItemsOTEOID] = @ReceivedItemsOTEOID";
cmd2.Parameters.AddWithValue("@ReceivedItemsOTEOID", index);
conn.Open();
int count = Convert.ToInt32(cmd1.ExecuteScalar());
if (count < 1)
{
cmd2.ExecuteNonQuery();
conn.Close();
if (ddlIssueHead.Text != "%")
{
SqlDataAdapter s;
DataSet d;
s = new SqlDataAdapter("SELECT * FROM [ReceivedItemsOTEO] where ChargeableHeadName='" + ddlIssueHead.SelectedItem + "' ", con);
d = new DataSet();
s.Fill(d);
_rprt.DataSource = d.Tables[0];
_rprt.DataBind();
}
else if (ddlChargeableHead.Text != "%")
{
SqlDataAdapter g;
DataSet fd;
g = new SqlDataAdapter("SELECT * FROM [ReceivedItemsOTEO] where ChargeableHeadName='" + ddlChargeableHead.SelectedItem + "' ", con);
fd = new DataSet();
g.Fill(fd);
_rprt.DataSource = fd.Tables[0];
_rprt.DataBind();
}
else if (tbStartDateSearch.Text != "" && tbEndDateSearch.Text != "")
{
SqlDataAdapter aa;
DataSet bb;
aa = new SqlDataAdapter("SELECT * FROM [ReceivedItemsOTEO] where ReceivedItemOTEODate between '" + tbStartDateSearch.Text + "' and '" + tbEndDateSearch.Text + "' or SupplyOrderDate between '" + tbStartDateSearch.Text + "' and '" + tbEndDateSearch.Text + "' ", con);
//'%" + _txtsearch.Value.ToString() + "%' and IndentRefernce '%" + _txtsearch.Value.ToString() + "%'
bb = new DataSet();
aa.Fill(bb);
_rprt.DataSource = bb.Tables[0];
_rprt.DataBind();
}
else
{
retriveData();
}
//DeleteData(index);
}
else
{
Response.Redirect("ReceivedItemsEdit.aspx?oteoid=" + index);
}
}
}
/// <summary>
/// //on row command of the items, mainly now 1. delete specfic row of slected item id in the secondary table
/// 2. update sum total amount in primary table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void gvItems_Edit_RowCommand(object sender, GridViewCommandEventArgs e)
{
//if (e.CommandName == "Delete")
//instead of using command name =delete, using amount value
if (e.CommandName != "")
{
//Label lblAmount = (Label) gvItems_Edit.FindControl(e.
double amount = Convert.ToDouble(e.CommandName);
string[] MyArraryOfCommand = e.CommandArgument.ToString().Split(new char[] { '`' });
string receivedItemID = MyArraryOfCommand[0];
string ItemID = MyArraryOfCommand[1];
string itemname = MyArraryOfCommand[2];
string rate = MyArraryOfCommand[3];
string issueheadname = MyArraryOfCommand[4];
string quantity = MyArraryOfCommand[5];
SqlTransaction tr = null;
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
//this will execute first
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM [ReceivedItemsDetails] WHERE [ReceivedItemID] = @ReceivedItemID";
cmd.Parameters.AddWithValue("@ReceivedItemID", receivedItemID);
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = "update ReceivedItemsOTEO set totalamount = totalamount-" + amount + " where receiveditemsoteoid =@receiveditemsoteoid";
//cmd2.Parameters.AddWithValue("@amount",);
cmd2.Parameters.AddWithValue("@receiveditemsoteoid", OTEOID);
SqlCommand cmd3 = conn.CreateCommand();
cmd3.CommandText = "SELECT COUNT (*) FROM ReceivedItemsDetails INNER JOIN ReceivedItemsOTEO ON ReceivedItemsDetails.ReceivedItemsOTEOID = ReceivedItemsOTEO.ReceivedItemsOTEOID WHERE (ReceivedItemsDetails.ReceivedItemsOTEOID = @ReceivedItemsOTEOID)";
cmd3.Parameters.AddWithValue("@ReceivedItemsOTEOID", OTEOID);
SqlCommand cmd4 = conn.CreateCommand();
//cmd4.CommandText = "DELETE FROM [Itemsratesecondary] WHERE [id] = @id ";
cmd4.CommandText = "update top(1) [Itemsratesecondary] set quantity=quantity-@quantity WHERE [itemname] = @itemname and " +
"[rate] = @rate and [issueheadname] = @issueheadname";
cmd4.Parameters.AddWithValue("@ItemID", ItemID);
cmd4.Parameters.AddWithValue("@quantity", quantity);
cmd4.Parameters.AddWithValue("@itemname", itemname);
cmd4.Parameters.AddWithValue("@rate", rate);
cmd4.Parameters.AddWithValue("@issueheadname", issueheadname);
cmd4.Parameters.AddWithValue("@receiveditemsoteoid", OTEOID);
try
{
using (conn)
{
conn.Open();
tr = conn.BeginTransaction();
cmd.Transaction = tr;
cmd2.Transaction = tr;
cmd4.Transaction = tr;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
int rowsAffected = cmd4.ExecuteNonQuery();
if (rowsAffected == 0)
{
tr.Rollback();
}
else
{
tr.Commit();
}
int count = Convert.ToInt32(cmd3.ExecuteScalar());
if (count < 1)
{
_btnDelete.Visible = true;
}
}
}
catch
{
tr.Rollback();
throw;
}
finally
{
conn.Close();
}
GetRecievedItemsDetails(OTEOID);
gvItems_Edit.DataBind();
}
}
protected void _rprt_ItemCommand(object sender, ListViewCommandEventArgs e)
{
if (e.CommandName == "Delete")
{
decimal index = Convert.ToDecimal(e.CommandArgument);
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
SqlCommand cmd1 = conn.CreateCommand();
cmd1.CommandText = "Select count(*) as count from DeliveryItemsDetails where DeliveryItemsChallanID = @DeliveryItemsChallanID";
cmd1.Parameters.AddWithValue("@DeliveryItemsChallanID", index);
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = "DELETE FROM [DeliveryItemsChallan] WHERE [DeliveryItemsChallanID] = @DeliveryItemsChallanID";
cmd2.Parameters.AddWithValue("@DeliveryItemsChallanID", index);
conn.Open();
int count = Convert.ToInt32(cmd1.ExecuteScalar());
if (count < 1)
{
cmd2.ExecuteNonQuery();
conn.Close();
// DeleteData(index);
if (_ddldivname.Text != "%")
{
dadapter = new SqlDataAdapter("SELECT * FROM [DeliveryItemsChallan] where IndentingDivisionName='" + _ddldivname.SelectedItem + "' ", con);
dset = new DataSet();
dadapter.Fill(dset);
_rprt.DataSource = dset.Tables[0];
_rprt.DataBind();
}
else if (ddlChargeableHead.Text != "%")
{
dadaptera = new SqlDataAdapter("SELECT * FROM [DeliveryItemsChallan] where ChargeableHeadName='" + ddlChargeableHead.SelectedItem + "' ", con);
dseta = new DataSet();
dadaptera.Fill(dseta);
_rprt.DataSource = dseta.Tables[0];
_rprt.DataBind();
}
else if (tbStartDateSearch.Text != "" && tbEndDateSearch.Text != "")
{
SqlDataAdapter aa;
DataSet bb;
aa = new SqlDataAdapter("SELECT * FROM [DeliveryItemsChallan] where IndentDate between '" + tbStartDateSearch.Text + "' and '" + tbEndDateSearch.Text + "' or ChallanDate between '" + tbStartDateSearch.Text + "' and '" + tbEndDateSearch.Text + "' ", con);
//'%" + _txtsearch.Value.ToString() + "%' and IndentRefernce '%" + _txtsearch.Value.ToString() + "%'
bb = new DataSet();
aa.Fill(bb);
_rprt.DataSource = bb.Tables[0];
_rprt.DataBind();
}
else
{
retriveData();
}
}
else
{
Response.Redirect("IssuedEntryEdit.aspx?challanid=" + index);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
//this is for one item
if (Request.QueryString["item"] != null)
{
string itemNameDecoded = HttpUtility.UrlDecode(Request.QueryString["item"].ToString());
con.Open();
SqlCommand cmdw = new SqlCommand("sp_GetDetailedInventoryByItemName '" + itemNameDecoded + "'", con);
SqlDataReader drd = cmdw.ExecuteReader();
GridView1.DataSource = drd;
GridView1.DataBind();
drd.Close();
con.Close();
GridView1.UseAccessibleHeader = true;
GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;
}
else if (Request.QueryString["IssueHeadName"] != null)
{
string decodedIHN = Utilities.QueryStringDecode(Request.QueryString["IssueHeadName"]);
string dateStockPosition = "";
//from date and to date - all issue head , specific issue head, dates can be empty or not empty
//1 no dates and all issuehead
//2 no dates and specific issuehead
//3 dates and all issue heads
//4 dates and specific issue head
if ((decodedIHN == "All") && (Request.QueryString["date"] == null)) //1
{
SqlConnection conn = new SqlConnection(AppConns.GetConnectionString());
SqlCommand cmd = new SqlCommand("sp_GetDetailedInventory", conn);
DataTable dt = new DataTable();
try
{
conn.Open();
SqlDataReader drd = cmd.ExecuteReader();
dt.Load(drd);
drd.Close();
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
conn.Close();
}
if (Request.QueryString["Id"] == "1")
{
GridView1.DataSource = dt;
GridView1.DataBind();
GridView1.UseAccessibleHeader = true;
GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;
GridView1.Visible = true;
}
else if (Request.QueryString["Id"] == "2")
{
GridView2.DataSource = dt;
GridView2.DataBind();
GridView2.UseAccessibleHeader = true;
GridView2.HeaderRow.TableSection = TableRowSection.TableHeader;
}
}
else if ((decodedIHN != "All") && (Request.QueryString["date"] == null)) //if specific Ihn 2
{
if (Request.QueryString["Id"] == "1")
{
GridView1.DataSource = SelectedIssueHeadDetails.GetSelectedIssueHeadDetails(decodedIHN).Tables[0];
GridView1.DataBind();
GridView1.UseAccessibleHeader = true;
GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;
GridView1.Visible = true;
}
else if (Request.QueryString["Id"] == "2")
{
GridView2.DataSource = SelectedIssueHeadDetails.GetSelectedIssueHeadDetails(decodedIHN).Tables[0];
GridView2.DataBind();
GridView2.Visible = true;
GridView2.UseAccessibleHeader = true;
GridView2.HeaderRow.TableSection = TableRowSection.TableHeader;
}
}
//3
else if ((decodedIHN == "All") && (Request.QueryString["date"] != null)) //if all Ihn and dates 3
{
dateStockPosition = Request.QueryString["date"];
lblFromDate.Visible = true;
lblFromDate.Text = "Stock Position Date: " + dateStockPosition;
///
if (Request.QueryString["Id"] == "1")
{
GridView1.DataSource = SelectedIssueHeadDetails.GetAllDetailsByDate(dateStockPosition);
GridView1.DataBind();
GridView1.UseAccessibleHeader = true;
GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;
GridView1.Visible = true;
}
else if (Request.QueryString["Id"] == "2")
{
GridView2.DataSource = SelectedIssueHeadDetails.GetAllDetailsByDate(dateStockPosition);
GridView2.DataBind();
GridView2.UseAccessibleHeader = true;
GridView2.HeaderRow.TableSection = TableRowSection.TableHeader;
GridView2.Visible = true;
}
}
else if ((decodedIHN != "All") && (Request.QueryString["date"] != "")) //if specific Ihn and dates 4
{
dateStockPosition = Request.QueryString["date"];
lblFromDate.Visible = true;
lblFromDate.Text = "Stock Position Date: " + dateStockPosition;
if (Request.QueryString["Id"] == "1")
{
GridView1.DataSource = SelectedIssueHeadDetails.GetSelectedIssueHeadDetails(decodedIHN, dateStockPosition);
GridView1.DataBind();
GridView1.UseAccessibleHeader = true;
GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;
GridView1.Visible = true;
}
else if (Request.QueryString["Id"] == "2")
{
GridView2.DataSource = SelectedIssueHeadDetails.GetSelectedIssueHeadDetails(decodedIHN, dateStockPosition);
GridView2.DataBind();
GridView2.UseAccessibleHeader = true;
GridView2.HeaderRow.TableSection = TableRowSection.TableHeader;
GridView2.Visible = true;
}
}
}
}