private void LoadData()
{
ArrayList al = DBLayer.Products.GetRest(Date1, Date2);
double abon = 0;
double goods = 0;
double serv = 0;
double charges = 0;
double total = 0;
DataTable dt = new DataTable();
dt.Columns.Add("Dimension");
dt.Columns.Add("Group");
dt.Columns.Add("Good");
dt.Columns.Add("Total", typeof(double));
dt.Columns.Add("Price", typeof(double));
dt.Columns.Add("Cap", typeof(double));
dt.Columns.Add("Id", typeof(int));
for (int i = 0; i < al.Count; i++)
{
DBLayer.Products.Rest_Details det = (DBLayer.Products.Rest_Details)al[i];
DataRow dr = dt.Rows.Add();
dr["Group"] = det.GroupName;
dr["Good"] = det.ProductName;
dr["Dimension"] = det.DimensionName;
dr["Id"] = det.Id;
if (det.Rest >= 0)
{
dr["Total"] = det.Rest;
}
dr["Price"] = det.Price;
if (det.Rest >= 0)
{
dr["Cap"] = det.Rest * det.Price;
}
if (det.Rest >= 0)
{
total += (det.Rest * det.Price);
}
}
lblTotal.Text = total.ToString();
grSales.DataSource = dt;
advBandedGridView1.BestFitColumns();
}
public static ArrayList GetRest(DateTime date1, DateTime date2)
{
string sql = "SELECT c.[ProductId], c.ProductName, c.DimensionName, c.GroupName, SUM(c.Arrived) AS Rest, pr.Price FROM ";
sql += " (SELECT * FROM ";
sql += " (SELECT DISTINCT p.[Id] AS ProductId, p.[Name] AS ProductName, ISNULL(SUM(ad.Quantity), 0) AS Arrived, d.[Name] AS DimensionName, pg.[Name] AS GroupName, ad.[Date] ";
sql += " FROM Products AS p INNER JOIN ArrivalDetails AS ad ON ad.[ProductId] = p.[Id] ";
sql += " INNER JOIN Dimensions AS d ON d.[Id] = p.DimensionId ";
sql += " INNER JOIN ProductGroup AS pg ON pg.[Id] = p.GroupId ";
sql += " GROUP BY p.[Name], d.[Name], pg.[Name], ad.[Date], p.[Id]) as a ";
sql += " UNION ";
sql += " SELECT * FROM ";
sql += " (SELECT DISTINCT p.[Id] AS ProductId, p.[Name] AS ProductName, -1 * ISNULL(SUM(s.Quantity), 0) AS Arrived, d.[Name] AS DimensionName, pg.[Name] AS GroupName, s.[Date] ";
sql += " FROM Products AS p INNER JOIN Sales AS s ON s.[ProductId] = p.[Id] ";
sql += " INNER JOIN Dimensions AS d ON d.[Id] = p.DimensionId ";
sql += " INNER JOIN ProductGroup AS pg ON pg.[Id] = p.GroupId ";
sql += " GROUP BY p.[Name], d.[Name], pg.[Name], s.[Date], p.[Id]) as b) AS c ";
sql += " INNER JOIN Products AS pr ON pr.[Id] = c.ProductId ";
sql += " WHERE c.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "'";
sql += " GROUP BY c.ProductName, c.DimensionName, c.GroupName, c.ProductId, pr.Price ";
DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql);
ArrayList al = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
DBLayer.Products.Rest_Details det = new DBLayer.Products.Rest_Details();
det.DimensionName = dr["DimensionName"].ToString();
det.GroupName = dr["GroupName"].ToString();
det.ProductName = dr["ProductName"].ToString();
if (!dr.IsNull("Rest"))
{
det.Rest = Convert.ToDouble(dr["Rest"]);
}
if (!dr.IsNull("Price"))
{
det.Price = Convert.ToDouble(dr["Price"]);
}
if (!dr.IsNull("ProductId"))
{
det.Id = Convert.ToInt32(dr["ProductId"]);
}
al.Add(det);
}
return(al);
}
