private void LoadData()
{
ArrayList al = DBLayer.Products.GetList();
DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("GroupName");
dt.Columns.Add("Name");
dt.Columns.Add("Dimension");
dt.Columns.Add("Price", typeof(double));
for (int i = 0; i < al.Count; i++)
{
DBLayer.Products.Products_WideDetails det = (DBLayer.Products.Products_WideDetails)al[i];
DataRow dr = dt.Rows.Add();
dr["Id"] = det.Id;
dr["GroupName"] = det.GroupName;
dr["Name"] = det.Name;
dr["Dimension"] = det.DimensionName;
dr["Price"] = det.Price;
}
grGoods.DataSource = dt;
advBandedGridView1.BestFitColumns();
slblTotal.Text = advBandedGridView1.RowCount.ToString();
}
public void LoadData()
{
ArrayList al = DBLayer.Products.GetProducts(Convert.ToDateTime(tbDateFrom.Text));
DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Price", typeof(double));
dt.Columns.Add("Quantity", typeof(int));
dt.Columns.Add("Total", typeof(double));
dt.Columns.Add("GroupName");
dt.Columns.Add("ProductName");
dt.Columns.Add("DimensionName");
dt.Columns.Add("Code");
for (int i = 0; i < al.Count; i++)
{
DBLayer.Products.Products_WideDetails det = (DBLayer.Products.Products_WideDetails)al[i];
DataRow dr = dt.Rows.Add();
dr["GroupName"] = det.GroupName;
dr["ProductName"] = det.Name;
dr["DimensionName"] = det.DimensionName;
dr["Code"] = det.Barcode;
dr["Quantity"] = det.Sold;
dr["Price"] = det.Price;
dr["Total"] = det.Price * det.Sold;
dr["Id"] = det.Id;
}
grSales.DataSource = dt;
advBandedGridView1.BestFitColumns();
}
public static ArrayList GetList(int id)
{
string sql = "SELECT p.[Name], p.[Id], p.Barcode, p.GroupId, p.DimensionId, p.[Description], d.[Name] AS Dimension, g.[Name] AS [Group], p.Price ";
sql += " FROM Products AS p INNER JOIN ProductGroup AS g ON p.[GroupId] = g.[Id] ";
sql += " INNER JOIN Dimensions AS d ON d.[Id] = p.[DimensionId] WHERE GroupId = " + id.ToString();;
DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql);
ArrayList al = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
DBLayer.Products.Products_WideDetails det = new DBLayer.Products.Products_WideDetails();
if (!dr.IsNull("Id"))
{
det.Id = Convert.ToInt32(dr["Id"]);
}
det.Name = dr["Name"].ToString();
det.Barcode = dr["Barcode"].ToString();
det.Description = dr["Description"].ToString();
if (!dr.IsNull("GroupId"))
{
det.GroupId = Convert.ToInt32(dr["GroupId"]);
}
if (!dr.IsNull("Price"))
{
det.Price = Convert.ToDouble(dr["Price"]);
}
if (!dr.IsNull("DimensionId"))
{
det.DimensionId = Convert.ToInt32(dr["DimensionId"]);
}
det.DimensionName = dr["Dimension"].ToString();
det.GroupName = dr["Group"].ToString();
al.Add(det);
}
return(al);
}
private void LoadProducts(int id)
{
ArrayList al = DBLayer.Products.GetList();
cbProduct.Items.Clear();
for (int i = 0; i < al.Count; i++)
{
DBLayer.Products.Products_WideDetails det = (DBLayer.Products.Products_WideDetails)al[i];
Lib.ServiceFunctions.ListItem li = new FitnessProject.Lib.ServiceFunctions.ListItem();
li.ID = det.Id;
li.Name = det.Name;
cbProduct.Items.Add(li);
}
if (cbProduct.Items.Count > 0)
{
if (id == 0)
{
cbProduct.SelectedIndex = 0;
}
else
{
for (int i = 0; i < cbProduct.Items.Count; i++)
{
Lib.ServiceFunctions.ListItem li = (Lib.ServiceFunctions.ListItem)cbProduct.Items[i];
if (li.ID == id)
{
cbProduct.SelectedIndex = i;
break;
}
}
}
}
}
private void cbProductGroup_SelectedIndexChanged(object sender, EventArgs e)
{
Lib.ServiceFunctions.ListItem li = (Lib.ServiceFunctions.ListItem)cbProductGroup.SelectedItem;
if (cbProduct.Items.Count > 0)
{
cbProduct.Items.Clear();
}
ArrayList al = DBLayer.Products.GetList(li.ID);
for (int i = 0; i < al.Count; i++)
{
DBLayer.Products.Products_WideDetails det = (DBLayer.Products.Products_WideDetails)al[i];
Lib.ServiceFunctions.ListItem liP = new FitnessProject.Lib.ServiceFunctions.ListItem();
liP.ID = det.Id;
liP.Name = det.Name;
cbProduct.Items.Add(liP);
}
}
public static ArrayList GetProducts(DateTime date)
{
string sql = "SELECT DISTINCT p.[Id], p.[Name] AS ProductName, p.Barcode, pg.Name AS ProductGroup, d.[Name] AS DimensionName, p.Price, (SELECT SUM(Quantity) FROM Sales AS s WHERE s.ProductId = p.Id AND [Date] = '" + date.ToString("yyyyMMdd") + "') AS Sold";
sql += " FROM Products AS p INNER JOIN ProductGroup AS pg ON pg.[Id] = p.GroupId ";
sql += " INNER JOIN Dimensions AS d ON d.[Id] = p.DimensionId ";
DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql);
ArrayList al = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
DBLayer.Products.Products_WideDetails det = new DBLayer.Products.Products_WideDetails();
det.DimensionName = dr["DimensionName"].ToString();
if (!dr.IsNull("Sold"))
{
det.Sold = Convert.ToInt32(dr["Sold"]);
}
det.GroupName = dr["ProductGroup"].ToString();
det.Name = dr["ProductName"].ToString();
det.Price = Convert.ToDouble(dr["Price"]);
det.Id = Convert.ToInt32(dr["Id"]);
det.Barcode = dr["Barcode"].ToString();
al.Add(det);
}
return(al);
}
