private void LoadData()
{
ArrayList al = DBLayer.Users.GetUserIncome(this.Date1, this.Date2);
DataTable dt = new DataTable();
dt.Columns.Add("User");
dt.Columns.Add("PercentForClients", typeof(double));
dt.Columns.Add("PercentForGoods", typeof(double));
dt.Columns.Add("PercentForService", typeof(double));
dt.Columns.Add("Constant", typeof(double));
dt.Columns.Add("AbonementIncome", typeof(double));
dt.Columns.Add("GoodsIncome", typeof(double));
dt.Columns.Add("ServiceIncome", typeof(double));
dt.Columns.Add("Abonements", typeof(double));
dt.Columns.Add("Goods", typeof(double));
dt.Columns.Add("Services", typeof(double));
dt.Columns.Add("Total", typeof(double));
dt.Columns.Add("AbonementFitness", typeof(double));
dt.Columns.Add("ServiceMassage", typeof(double));
dt.Columns.Add("ServiceMassageIncome", typeof(double));
dt.Columns.Add("ClientFitnessIncome", typeof(double));
double total = 0;
for (int i = 0; i < al.Count; i++)
{
DBLayer.Users.UserIncome_Details det = (DBLayer.Users.UserIncome_Details)al[i];
DataRow dr = dt.Rows.Add();
dr["User"] = det.User;
dr["PercentForClients"] = det.PercentForClients;
dr["PercentForGoods"] = det.PercentForSales;
dr["PercentForService"] = det.PercentForService;
dr["Constant"] = det.Constant;
dr["AbonementIncome"] = det.ClientIncome;
dr["GoodsIncome"] = det.GoodIncome;
dr["ServiceIncome"] = det.ServiceIncome;
dr["Abonements"] = det.Abonements;
dr["Goods"] = det.Goods;
dr["Services"] = det.Services;
dr["AbonementFitness"] = det.Fitness;
dr["ServiceMassageIncome"] = det.MassageIncome;
dr["ServiceMassage"] = det.Massage;
dr["ClientFitnessIncome"] = det.FitnessIncome;
dr["Total"] = det.ClientIncome + det.GoodIncome + det.ServiceIncome;
total += det.Constant + det.ClientIncome + det.GoodIncome + det.ServiceIncome;
}
lblRest.Text = total.ToString();
grSales.DataSource = dt;
advBandedGridView1.BestFitColumns();
}
public static ArrayList GetUserIncome(DateTime date1, DateTime date2)
{
string sql = " SELECT DISTINCT a.[User], a.UserId, ISNULL((SELECT Sum(Summ) FROM Cash AS c INNER JOIN Abonements AS ab ON c.AbonementId = ab.[Id] WHERE ab.[AbonementGroup] = 0 AND Type = 0 AND c.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND c.UserId = a.UserId GROUP BY [User]), 0) AS Abonements, ";
sql += " ISNULL((SELECT Sum(Summ) FROM Cash AS c INNER JOIN Abonements AS ab ON c.AbonementId = ab.[Id] WHERE ab.[AbonementGroup] = 1 AND Type = 0 AND c.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND c.UserId = a.UserId GROUP BY [User]), 0) AS AbonementFitness, ";
sql += " ISNULL((SELECT Sum(Summ) FROM Cash AS c WHERE Type = 1 AND c.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND c.UserId = a.UserId GROUP BY [User]), 0) AS Goods, ";
sql += " ISNULL((SELECT Sum(Summ) FROM Cash AS c INNER JOIN Services AS s ON c.AbonementId = s.[Id] WHERE c.Type = 2 AND s.[Type] = 0 AND c.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND c.UserId = a.UserId GROUP BY [User]), 0) AS Services, ";
sql += " ISNULL((SELECT Sum(Summ) FROM Cash AS c INNER JOIN Services AS s ON c.AbonementId = s.[Id] WHERE c.Type = 2 AND s.[Type] = 1 AND c.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND c.UserId = a.UserId GROUP BY [User]), 0) AS ServiceMassage, ";
sql += " ur.Constant, ur.PercentForClients, ur.PercentForSales, ur.PercentForService, ur.PercentForFitness, ur.PercentForMassage, ";
sql += " ((ur.PercentForClients * ISNULL((SELECT Sum(Summ) FROM Cash AS c INNER JOIN Abonements AS ab ON c.AbonementId = ab.[Id] WHERE ab.[AbonementGroup] = 0 AND Type = 0 AND c.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND c.UserId = a.UserId GROUP BY [User]), 0))/100) AS ClientIncome, ";
sql += " ((ur.PercentForClients * ISNULL((SELECT Sum(Summ) FROM Cash AS c INNER JOIN Abonements AS ab ON c.AbonementId = ab.[Id] WHERE ab.[AbonementGroup] = 1 AND Type = 0 AND c.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND c.UserId = a.UserId GROUP BY [User]), 0))/100) AS ClientFitnessIncome, ";
sql += " ((ur.PercentForSales * ISNULL((SELECT Sum(Summ) FROM Cash AS c WHERE Type = 1 AND c.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND c.UserId = a.UserId GROUP BY [User]), 0))/100) AS GoodIncome, ";
sql += " ((ur.PercentForService * ISNULL((SELECT Sum(Summ) FROM Cash AS c INNER JOIN Services AS s ON c.AbonementId = s.[Id] WHERE c.Type = 2 AND s.[Type] = 0 AND c.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND c.UserId = a.UserId GROUP BY [User]), 0))/100) AS ServiceIncome, ";
sql += " ((ur.PercentForMassage * ISNULL((SELECT Sum(Summ) FROM Cash AS c INNER JOIN Services AS s ON c.AbonementId = s.[Id] WHERE c.Type = 2 AND s.[Type] = 1 AND c.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND c.UserId = a.UserId GROUP BY [User]), 0))/100) AS ServiceMassageIncome ";
sql += " FROM Cash AS a ";
sql += " LEFT JOIN Users AS u ON u.[Id] = a.UserId ";
sql += " LEFT JOIN UserRate AS ur ON ur.[UserId] = u.[Id] ";
sql += " WHERE a.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND [User] <> '' ";
DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql);
ArrayList al = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
DBLayer.Users.UserIncome_Details det = new DBLayer.Users.UserIncome_Details();
if (!dr.IsNull("Abonements"))
{
det.Abonements = Convert.ToDouble(dr["Abonements"]);
}
if (!dr.IsNull("ClientIncome"))
{
det.ClientIncome = Convert.ToDouble(dr["ClientIncome"]);
}
if (!dr.IsNull("Constant"))
{
det.Constant = Convert.ToDouble(dr["Constant"]);
}
if (!dr.IsNull("GoodIncome"))
{
det.GoodIncome = Convert.ToDouble(dr["GoodIncome"]);
}
if (!dr.IsNull("Goods"))
{
det.Goods = Convert.ToDouble(dr["Goods"]);
}
if (!dr.IsNull("AbonementFitness"))
{
det.Fitness = Convert.ToDouble(dr["AbonementFitness"]);
}
if (!dr.IsNull("ServiceMassage"))
{
det.Massage = Convert.ToDouble(dr["ServiceMassage"]);
}
if (!dr.IsNull("PercentForClients"))
{
det.PercentForClients = Convert.ToDouble(dr["PercentForClients"]);
}
if (!dr.IsNull("PercentForSales"))
{
det.PercentForSales = Convert.ToDouble(dr["PercentForSales"]);
}
if (!dr.IsNull("PercentForService"))
{
det.PercentForService = Convert.ToDouble(dr["PercentForService"]);
}
if (!dr.IsNull("ServiceIncome"))
{
det.ServiceIncome = Convert.ToDouble(dr["ServiceIncome"]);
}
if (!dr.IsNull("ClientFitnessIncome"))
{
det.FitnessIncome = Convert.ToDouble(dr["ClientFitnessIncome"]);
}
if (!dr.IsNull("ServiceMassageIncome"))
{
det.MassageIncome = Convert.ToDouble(dr["ServiceMassageIncome"]);
}
if (!dr.IsNull("Services"))
{
det.Services = Convert.ToDouble(dr["Services"]);
}
det.User = dr["User"].ToString();
if (!dr.IsNull("UserId"))
{
det.UserId = Convert.ToInt32(dr["UserId"]);
}
al.Add(det);
}
return(al);
}