protected void filldropdownlist()
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
//for product dropdown
string productquery = "SELECT ProductId,ProductName FROM Product";
cmd = new SqlCommand(productquery, sqlcon);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "product");
DropDownProduct.DataSource = ds.Tables["product"];
DropDownProduct.DataTextField = "ProductName";
DropDownProduct.DataValueField = "ProductId";
DropDownProduct.DataBind();
//for supplier dropdownlist
string supplierquery = "SELECT SupplierId,CompanyName FROM Supplier";
cmd = new SqlCommand(supplierquery, sqlcon);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "supplier");
DropDownSupplier.DataSource = ds.Tables["supplier"];
DropDownSupplier.DataTextField = "CompanyName";
DropDownSupplier.DataValueField = "SupplierId";
DropDownSupplier.DataBind();
sqlcon.Close();
}
public void clear()
{
hfPurchaseId.Value = "";
txtQuantity.Text = txtOthers.Text = "";
DropDownProduct.ClearSelection();
DropDownSupplier.ClearSelection();
lblerrormessage.Text = lblsuccessmassage.Text = "";
btnsave.Text = "Save";
btndelete.Enabled = true;
}
protected void btndelete_Click(object sender, EventArgs e)
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand cmd = new SqlCommand("PurchaseDeleteById", sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PurchaseId", Convert.ToInt32(hfPurchaseId.Value));
cmd.ExecuteNonQuery();
sqlcon.Close();
hfPurchaseId.Value = "";
txtQuantity.Text = txtOthers.Text = "";
DropDownProduct.ClearSelection();
DropDownSupplier.ClearSelection();
FillGridView();
lblsuccessmassage.Text = ("Delete Successfully!");
}
protected void btnsave_Click(object sender, EventArgs e)
{
//Console.Write(DropDownProduct.SelectedValue);
//Console.Write(DropDownProduct.SelectedItem.Text);
//Console.WriteLine(DropDownSupplier.SelectedValue);
//Console.WriteLine(DropDownSupplier.SelectedItem.Text);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sqlcmd = new SqlCommand("PurchaseCreateOrUpdate", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@PurchaseId", hfPurchaseId.Value == "" ? 0 : Convert.ToInt32(hfPurchaseId.Value));
sqlcmd.Parameters.AddWithValue("@ProductId", Convert.ToInt32(DropDownProduct.SelectedValue));
sqlcmd.Parameters.AddWithValue("@SupplierId", Convert.ToInt32(DropDownSupplier.SelectedValue));
sqlcmd.Parameters.AddWithValue("@Quantity", Convert.ToInt32(txtQuantity.Text.Trim()));
sqlcmd.Parameters.AddWithValue("@Others", txtOthers.Text);
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
string PurchaseId = hfPurchaseId.Value;
if (PurchaseId == "")
{
lblsuccessmassage.Text = "Saved Successfully";
}
else
{
lblsuccessmassage.Text = "Updated Successfully";
}
FillGridView();
clear();
DropDownProduct.ClearSelection();
DropDownSupplier.ClearSelection();
}
protected void btnsave_Click(object sender, EventArgs e)
{
//Console.Write(DropDownProduct.SelectedValue);
//Console.Write(DropDownProduct.SelectedItem.Text);
//Console.WriteLine(DropDownSupplier.SelectedValue);
//Console.WriteLine(DropDownSupplier.SelectedItem.Text);
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
//SqlCommand sqlcmd = new SqlCommand("PurchaseCreateOrUpdate", sqlcon);
//sqlcmd.CommandType = CommandType.StoredProcedure;
//sqlcmd.Parameters.AddWithValue("@PurchaseId", hfPurchaseId.Value == "" ? 0 : Convert.ToInt32(hfPurchaseId.Value));
//sqlcmd.Parameters.AddWithValue("@ProductId", Convert.ToInt32(DropDownProduct.SelectedValue));
//sqlcmd.Parameters.AddWithValue("@SupplierId", Convert.ToInt32(DropDownSupplier.SelectedValue));
//sqlcmd.Parameters.AddWithValue("@Quantity", Convert.ToInt32(txtQuantity.Text.Trim()));
//sqlcmd.Parameters.AddWithValue("@DateOfPurchase", Convert.ToInt32(txtDate.Text.Trim()));
//sqlcmd.Parameters.AddWithValue("@Others", txtOthers.Text);
//sqlcmd.ExecuteNonQuery();
//sqlcon.Close();
//string PurchaseId = hfPurchaseId.Value;
///here productId is DepartmentId
SqlCommand cmd = new SqlCommand("Insert into Purchase values(@PurchaseId,@ProductId,@SupplierId,@Quantity,@TotalAmount,@DateOfPurchase,@Others)", sqlcon);
cmd.Parameters.AddWithValue("@PurchaseId", DropDownDepartment.SelectedValue);
cmd.Parameters.AddWithValue("@ProductId", DropDownProduct.SelectedItem.Text);
cmd.Parameters.AddWithValue("@SupplierId", DropDownSupplier.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Quantity", txtQuantity.Text.Trim());
cmd.Parameters.AddWithValue("@TotalAmount", txtAmount.Text.Trim());
cmd.Parameters.AddWithValue("@DateOfPurchase", txtDate.Text.Trim());
cmd.Parameters.AddWithValue("@Others", txtOthers.Text.Trim());
int i = cmd.ExecuteNonQuery();
sqlcon.Close();
string PurchaseId = hfPurchaseId.Value;
if (i > 0)
{
lblsuccessmassage.Text = "Saved Successfully";
}
else
{
lblsuccessmassage.Text = "Updated Successfully";
FillGridView();
clear();
DropDownProduct.ClearSelection();
DropDownSupplier.ClearSelection();
DropDownDepartment.ClearSelection();
}
//void FillGridView()
//{
// if (sqlcon.State == ConnectionState.Closed)
// sqlcon.Open();
// string pronamequery = "Select Purchase.PurchaseID,Product.ProductName,Supplier.CompanyName,Purchase.Quantity,Purchase.Others from purchase INNER JOIN Product on Product.ProductId = Purchase.PurchaseId INNER JOIN Supplier on Supplier.SupplierId = Purchase.PurchaseId";
// SqlCommand scmd = new SqlCommand(pronamequery, sqlcon);
// SqlDataAdapter sda = new SqlDataAdapter(scmd);
// DataTable dt = new DataTable();
// sda.Fill(dt);
// sqlcon.Close();
// purchaseGrid.DataSource = dt;
// purchaseGrid.DataBind();
//SqlDataAdapter sqlDa = new SqlDataAdapter("ViewPurchaseGrid", sqlcon);
//sqlDa.SelectCommand.CommandType = CommandType.StoredProcedure;
//DataTable dtbl = new DataTable();
//sqlDa.Fill(dtbl);
//sqlcon.Close();
//purchaseGrid.DataSource = dtbl;
//purchaseGrid.DataBind();
}
public VMHome HomeModel()
{
VMHome home = new VMHome();
home.TopProducts = new List <Product>();
home.DropDownSizes = dbContext.Sizes.ToList();
home.DropDownProducts = new List <DropDownProduct>();
foreach (var p in dbContext.Products)
{
DropDownProduct d = new DropDownProduct();
d.Id = p.ID;
d.Name = p.Name;
d.Img = p.Img;
d.Games = new List <string>();
foreach (var g in p.Size.Games)
{
d.Games.Add(g.Name);
}
home.DropDownProducts.Add(d);
}
var products = from p in dbContext.Products
join s in dbContext.Sales on p.ID equals s.ProductID
where p.ID == s.ProductID
group p by new
{
Id = p.ID,
Name = p.Name,
Height = p.Size.Height,
Width = p.Size.Width,
Img = p.Img,
Color = p.Color,
Price = p.Price,
} into gro
select new
{
Id = gro.Key.Id,
Name = gro.Key.Name,
Height = gro.Key.Height,
Width = gro.Key.Width,
Img = gro.Key.Img,
TotalSales = dbContext.Sales.Where(y => y.ProductID == gro.Key.Id).Sum(x => x.Quantity),
Color = gro.Key.Color,
Price = gro.Key.Price,
};
foreach (var p in products.OrderByDescending(x => x.TotalSales).Take(3))
{
Product product = new Product();
product.ID = (int)p.Id;
product.Name = (string)p.Name;
product.Color = (string)p.Color;
product.Size = new Size();
product.Size.Height = (int)p.Height;
product.Size.Width = (int)p.Width;
product.Img = (string)p.Img;
product.Price = (decimal)p.Price;
home.TopProducts.Add(product);
}
return(home);
}
