DataTable ProTable = new DataTable();
ProTable.Columns.Add("Product Code", typeof(string));
ProTable.Columns.Add("ProdDesc", typeof(string));
ProTable.Columns.Add("UOM", typeof(string));
ProTable.Columns.Add("OnHand", typeof(string));
ProTable.Columns.Add("Price", typeof(string));
ProTable.Columns.Add("Amount", typeof(string));
probel.prod_cate = Ddl_Brand.Text;
Dgv_Summary.DataSource = srbll.CategoryData(probel, stock , dblAmount);
prodmax = srbll.prodmax(probel, prod_max);
for (int i = 0; i < Dgv_Summary.RowCount; i++)
{
try
{
double ProdPrice =IsNumber(prodmax.ToString());
stock = int.Parse(srbll.getonhand(sqldateconverion(Dtp_ToDate.Value), Dgv_Summary[0, i].Value.ToString(), probel.comp_id.ToString()));
dblAmount = Convert.ToDouble(((stock * ProdPrice).ToString()));
ProTable.Rows.Add(Dgv_Summary[0, i].Value.ToString(), Dgv_Summary[1, i].Value.ToString(), Dgv_Summary[2, i].Value.ToString(),
stock, ProdPrice, dblAmount);
}
catch { }
}
Dgv_Summary.DataSource = ProTable;
Dgv_Summary.Columns["Product Code"].Width = 100;
Dgv_Summary.Columns["ProdDesc"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
Dgv_Summary.Columns["UOM"].Width = 100;
Dgv_Summary.Columns["OnHand"].Width = 100;
Dgv_Summary.Columns["Price"].Width = 100;
Dgv_Summary.Columns["Amount"].Width = 100;
Dgv_Summary.Columns["OnHand"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
Dgv_Summary.Columns["Price"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
Dgv_Summary.Columns["Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
decimal sum = 0;
foreach (DataRow dr in ProTable.Rows)
{
sum +=Convert.ToDecimal(dr["Amount"].ToString());
}
lbl_TotalAmount.Text = sum.ToString();
}
public double IsNumber(string strData)
{
double Num;
double fnum = 0;
Boolean isNum = double.TryParse(strData, out Num);
if (isNum == true)
fnum = Convert.ToDouble(strData);
return fnum;
}
STORED PROCEDURE
ALTER procedure [dbo].[SP_StockReport_Category]
@prod_cate nvarchar(30)
as begin
select prod_code as[Product Code], prod_desc as[Description], prod_uom as UOM ,prod_max from products
where prod_cate=@prod_cate and comp_id=1 and prod_deleted=0 and prod_stock=1 and prod_suspend = 0 order by prod_code
end
DAL
public DataTable CategoryData(ProductBEL probel, int stock, double dblAmount)
{
if (cn.State != ConnectionState.Open)
cn.Open();
cmd.Connection = cn;
cmd = new SqlCommand("SP_StockReport_Category", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@prod_cate", probel.prod_cate);
sda = new SqlDataAdapter(cmd);
ds = new DataSet();
sda.Fill(ds, "products");
return ds.Tables["products"];
}
public double prodmax(ProductBEL probel, double prodmax)
{
if (cn.State != ConnectionState.Open)
cn.Open();
cmd.Connection = cn;
cmd = new SqlCommand("SP_StockReport_Category", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@prod_cate", probel.prod_cate);
tdr = cmd.ExecuteReader();
while (tdr.Read())
{
prodmax =Convert.ToDouble( tdr["prod_max"].ToString());
}
tdr.Close();
return prodmax;
}