public static ArrayList GetList(int id, DateTime date1, DateTime date2)
{
string sql = "SELECT ss.[Id], ss.Quantity, ss.[Date], ss.Time, s.Name AS ServiceName, ss.Quantity, d.[Name] AS DimensionName, u.FIO AS UserName, s.CostPerUnit, ss.IsDeleted, ss.DeleteDate, ss.DeleteReason, s.Type ";
sql += " FROM ServiceSales AS ss INNER JOIN Services AS s ON s.[Id] = ss.[ServiceId] ";
sql += " INNER JOIN Dimensions AS d ON d.[Id] = s.DimensionId INNER JOIN Users AS u ON u.[Id] = ss.UserId";
sql += " WHERE ss.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "'";
sql += " ORDER BY ss.[Date]";
DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql);
ArrayList al = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
DBLayer.ServiceSales.ServiceSales_WideDetails det = new DBLayer.ServiceSales.ServiceSales_WideDetails();
if (!dr.IsNull("Id"))
{
det.Id = Convert.ToInt32(dr["Id"]);
}
if (!dr.IsNull("Type"))
{
det.Type = Convert.ToInt32(dr["Type"]);
}
if (!dr.IsNull("Date"))
{
det.Date = Convert.ToDateTime(dr["Date"]);
}
if (!dr.IsNull("Quantity"))
{
det.Quantity = Convert.ToInt32(dr["Quantity"]);
}
det.ServiceName = dr["ServiceName"].ToString();
det.Time = dr["Time"].ToString();
det.UserName = dr["UserName"].ToString();
det.DimensionName = dr["DimensionName"].ToString();
det.Cost = Convert.ToDouble(dr["CostPerUnit"]);
if (!dr.IsNull("IsDeleted"))
{
det.IsDeleted = Convert.ToBoolean(dr["IsDeleted"]);
}
if (!dr.IsNull("DeleteDate"))
{
det.DeleteDate = Convert.ToDateTime(dr["DeleteDate"]);
}
det.DeleteReason = dr["DeleteReason"].ToString();
al.Add(det);
}
return(al);
}
private void LoadData()
{
ArrayList al = DBLayer.ServiceSales.GetList(0, Date1, Date2);
DataTable dt = new DataTable();
dt.Columns.Add("ServiceName");
dt.Columns.Add("DimensionName");
dt.Columns.Add("Cost", typeof(double));
dt.Columns.Add("Quantity", typeof(double));
dt.Columns.Add("User");
dt.Columns.Add("Type");
dt.Columns.Add("Date", typeof(DateTime));
dt.Columns.Add("Time");
dt.Columns.Add("Summ", typeof(double));
dt.Columns.Add("IsDeleted");
dt.Columns.Add("DeleteDate", typeof(DateTime));
dt.Columns.Add("DeleteReason");
dt.Columns.Add("Id", typeof(int));
int total = 0;
double totalSumm = 0;
for (int i = 0; i < al.Count; i++)
{
DBLayer.ServiceSales.ServiceSales_WideDetails det = (DBLayer.ServiceSales.ServiceSales_WideDetails)al[i];
DataRow dr = dt.Rows.Add();
if (!det.IsDeleted)
{
total++;
totalSumm += (det.Cost * det.Quantity);
}
dr["ServiceName"] = det.ServiceName;
dr["DimensionName"] = det.DimensionName;
dr["Cost"] = det.Cost;
dr["User"] = det.UserName;
dr["Date"] = det.Date;
dr["Time"] = det.Time;
dr["Quantity"] = det.Quantity;
dr["Summ"] = (det.Cost * det.Quantity);
string name = string.Empty;
switch (det.Type)
{
case 0:
name = "Солярий";
break;
case 1:
name = "Массаж";
break;
case 2:
name = "Сауна";
break;
case 3:
name = "Косметология";
break;
case 4:
name = "Аренда";
break;
}
dr["Type"] = name;
if (det.IsDeleted)
{
dr["IsDeleted"] = "Удален";
}
if (det.DeleteDate != DateTime.MinValue)
{
dr["DeleteDate"] = det.DeleteDate;
}
dr["DeleteReason"] = det.DeleteReason;
dr["Id"] = det.Id;
}
grSales.DataSource = dt;
slblTotal.Text = total.ToString();
slblTotalMoney.Text = totalSumm.ToString();
advBandedGridView1.BestFitColumns();
}
