public static ArrayList Decline(DateTime date1, DateTime date2) { string sql = " SELECT * FROM Declines AS a "; sql += " WHERE a.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "'"; DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql); ArrayList al = new ArrayList(); foreach (DataRow dr in dt.Rows) { DBLayer.AbonementIncome.Money_ReportDetails det = new DBLayer.AbonementIncome.Money_ReportDetails(); det.Date = Convert.ToDateTime(dr["Date"]).Date; det.DimensionName = dr["DimensionName"].ToString(); det.Name = dr["Name"].ToString(); det.Price = Convert.ToDouble(dr["Price"]); det.Quantity = Convert.ToInt32(dr["Quantity"]); det.Summ = Convert.ToDouble(dr["Summ"]); det.Time = dr["Time"].ToString(); det.UserName = dr["User"].ToString(); det.Type = Convert.ToInt32(dr["Type"]); det.GroupName = dr["Group"].ToString(); 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); }
public static ArrayList MoneyReport(DateTime date1, DateTime date2) { string sql = "SELECT * FROM Cash as a "; sql += " WHERE a.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "'"; sql += " ORDER BY a.[Date]"; DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql); ArrayList al = new ArrayList(); foreach (DataRow dr in dt.Rows) { DBLayer.AbonementIncome.Money_ReportDetails det = new DBLayer.AbonementIncome.Money_ReportDetails(); det.Date = Convert.ToDateTime(dr["Date"]).Date; det.DimensionName = dr["DimensionName"].ToString(); det.Name = dr["Name"].ToString(); det.Price = Convert.ToDouble(dr["Price"]); det.Quantity = Convert.ToInt32(dr["Quantity"]); det.Summ = Convert.ToDouble(dr["Summ"]); det.Time = dr["Time"].ToString(); det.UserName = dr["User"].ToString(); det.Type = Convert.ToInt32(dr["Type"]); det.GroupName = dr["Group"].ToString(); det.AbonementName = dr["AName"].ToString(); det.ChargeGroupName = dr["GroupName"].ToString(); //if (!dr.IsNull("AbonementGroup")) // det.AbonementGroup = Convert.ToInt32(dr["AbonementGroup"]); al.Add(det); } return(al); }
private void LoadData() { ArrayList al = DBLayer.AbonementIncome.MoneyReport(Date1, Date2); this.Al = al; double total = 0; double abonements = 0; double goods = 0; double services = 0; double charges = 0; tvdgCharges.Nodes.Clear(); tvdgAbonements.Nodes.Clear(); tvdgGoods.Nodes.Clear(); tvdgServices.Nodes.Clear(); for (int i = 0; i < al.Count; i++) { DBLayer.AbonementIncome.Money_ReportDetails det = (DBLayer.AbonementIncome.Money_ReportDetails)al[i]; total += det.Summ; switch (det.Type) { case 0: abonements += det.Summ; bool abonAdd = true; string abonName = det.AbonementName; for (int j = 0; j < tvdgAbonements.Nodes.Count; j++) { //abons.Add(det); if (det.AbonementName == tvdgAbonements.Nodes[j].Cells["colNameAbon"].Value.ToString()) { abonAdd = false; } else { abonName = det.AbonementName; } } if (abonAdd) { double summ = 0; ArrayList abons = new ArrayList(); for (int k = 0; k < al.Count; k++) { DBLayer.AbonementIncome.Money_ReportDetails detAb = (DBLayer.AbonementIncome.Money_ReportDetails)al[k]; if (detAb.AbonementName == abonName) { abons.Add(detAb); summ += det.Summ; } } AdvancedDataGridView.TreeGridNode node = tvdgAbonements.Nodes.Add(det.AbonementName, summ); for (int x = 0; x < abons.Count; x++) { DBLayer.AbonementIncome.Money_ReportDetails detAbX = (DBLayer.AbonementIncome.Money_ReportDetails)abons[x]; node.Nodes.Add(detAbX.Name, det.Summ); } } break; case 1: goods += det.Summ; bool goodAdd = true; string goodName = det.AbonementName; for (int j = 0; j < tvdgGoods.Nodes.Count; j++) { //abons.Add(det); if (det.AbonementName == tvdgGoods.Nodes[j].Cells["colNameGood"].Value.ToString()) { goodAdd = false; } else { goodName = det.AbonementName; } } if (goodAdd) { double summ = 0; ArrayList goodsAL = new ArrayList(); for (int k = 0; k < al.Count; k++) { DBLayer.AbonementIncome.Money_ReportDetails detG = (DBLayer.AbonementIncome.Money_ReportDetails)al[k]; if (detG.AbonementName == goodName) { goodsAL.Add(detG); summ += detG.Summ; } } AdvancedDataGridView.TreeGridNode node = tvdgGoods.Nodes.Add(det.AbonementName, summ); for (int x = 0; x < goodsAL.Count; x++) { DBLayer.AbonementIncome.Money_ReportDetails detGX = (DBLayer.AbonementIncome.Money_ReportDetails)goodsAL[x]; node.Nodes.Add(detGX.Date.ToString("dd-MMM-yyyy") + "/" + detGX.Quantity.ToString(), detGX.Summ); } } break; case 2: services += det.Summ; bool servAdd = true; string servName = det.AbonementName; for (int j = 0; j < tvdgServices.Nodes.Count; j++) { //abons.Add(det); if (det.AbonementName == tvdgServices.Nodes[j].Cells["colNameService"].Value.ToString()) { servAdd = false; } else { servName = det.AbonementName; } } if (servAdd) { double summ = 0; ArrayList servAL = new ArrayList(); for (int k = 0; k < al.Count; k++) { DBLayer.AbonementIncome.Money_ReportDetails detS = (DBLayer.AbonementIncome.Money_ReportDetails)al[k]; if (detS.AbonementName == servName) { servAL.Add(detS); summ += detS.Summ; } } AdvancedDataGridView.TreeGridNode node = tvdgServices.Nodes.Add(det.AbonementName, summ); for (int x = 0; x < servAL.Count; x++) { DBLayer.AbonementIncome.Money_ReportDetails detSX = (DBLayer.AbonementIncome.Money_ReportDetails)servAL[x]; node.Nodes.Add(detSX.Date.ToString("dd-MMM-yyyy") + "/" + detSX.Quantity.ToString(), detSX.Summ); } } break; case 3: charges += det.Summ; bool chAdd = true; string chName = det.ChargeGroupName; for (int j = 0; j < tvdgCharges.Nodes.Count; j++) { //abons.Add(det); if (det.ChargeGroupName == tvdgCharges.Nodes[j].Cells["colNameCharge"].Value.ToString()) { chAdd = false; } else { chName = det.ChargeGroupName; } } if (chAdd) { double summ = 0; ArrayList chAL = new ArrayList(); for (int k = 0; k < al.Count; k++) { DBLayer.AbonementIncome.Money_ReportDetails detC = (DBLayer.AbonementIncome.Money_ReportDetails)al[k]; if (detC.ChargeGroupName == chName) { chAL.Add(detC); summ += detC.Summ; } } AdvancedDataGridView.TreeGridNode node = tvdgCharges.Nodes.Add(det.ChargeGroupName, summ); for (int x = 0; x < chAL.Count; x++) { DBLayer.AbonementIncome.Money_ReportDetails detCX = (DBLayer.AbonementIncome.Money_ReportDetails)chAL[x]; node.Nodes.Add(detCX.AbonementName, detCX.Summ); } } break; } } slblTotal.Text = total.ToString(); pnlAbonements.PanelTitle = "Абонементы: " + abonements.ToString(); pnlBar.PanelTitle = "Бар: " + goods.ToString(); pnlServices.PanelTitle = "Услуги: " + services.ToString(); pnlCharges.PanelTitle = "Расходы: " + charges.ToString(); }
private void LoadData() { ArrayList al = DBLayer.AbonementIncome.MoneyReport(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("User"); dt.Columns.Add("Cost", typeof(double)); dt.Columns.Add("Quantity", typeof(double)); dt.Columns.Add("Total", typeof(double)); dt.Columns.Add("Date", typeof(DateTime)); dt.Columns.Add("Time"); dt.Columns.Add("Type", typeof(int)); dt.Columns.Add("Name"); dt.Columns.Add("ChargeGroupName"); dt.Columns.Add("AbonementType"); //lvData.Items.Clear(); for (int i = 0; i < al.Count; i++) { DBLayer.AbonementIncome.Money_ReportDetails det = (DBLayer.AbonementIncome.Money_ReportDetails)al[i]; DataRow dr = dt.Rows.Add(); dr["Group"] = det.GroupName; dr["Good"] = det.AbonementName; dr["Dimension"] = det.DimensionName; dr["User"] = det.UserName; if (det.Type != 3) { dr["Cost"] = det.Price; dr["Quantity"] = det.Quantity; } dr["Total"] = det.Summ; dr["Time"] = det.Time; dr["Date"] = det.Date; dr["Name"] = det.Name; dr["ChargeGroupName"] = det.ChargeGroupName; if (det.Type == 0) { abon += det.Summ; } if (det.Type == 1) { goods += det.Summ; } if (det.Type == 2) { serv += det.Summ; } if (det.Type == 3) { charges += det.Summ; } dr["Type"] = det.Type; string name = ""; switch (det.AbonementGroup) { case 0: name = "Тренажерный зал"; break; case 1: name = "Аэробный зал"; break; default: name = ""; break; } dr["AbonementType"] = name; } this.Abonements = abon; this.Goods = goods; this.Services = serv; this.Charges = charges; total = abon + goods + serv + charges; grSales.DataSource = dt; advBandedGridView1.BestFitColumns(); slblIncome.Text = (abon + serv + goods).ToString(); slblAbonements.Text = abon.ToString(); slblGoods.Text = goods.ToString(); slblServices.Text = serv.ToString(); slblCharges.Text = charges.ToString(); lblRest.Text = total.ToString(); lblChargesBig.Text = charges.ToString(); }
private void LoadData() { ArrayList al = DBLayer.AbonementIncome.Decline(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("User"); dt.Columns.Add("DeleteReason"); dt.Columns.Add("DeleteDate", typeof(DateTime)); dt.Columns.Add("Cost", typeof(double)); dt.Columns.Add("Quantity", typeof(double)); dt.Columns.Add("Total", typeof(double)); dt.Columns.Add("Date", typeof(DateTime)); dt.Columns.Add("Time"); //lvData.Items.Clear(); for (int i = 0; i < al.Count; i++) { DBLayer.AbonementIncome.Money_ReportDetails det = (DBLayer.AbonementIncome.Money_ReportDetails)al[i]; DataRow dr = dt.Rows.Add(); dr["Group"] = det.GroupName; dr["Good"] = det.Name; dr["Dimension"] = det.DimensionName; dr["User"] = det.UserName; dr["Cost"] = det.Price; dr["Quantity"] = det.Quantity; dr["Total"] = det.Summ; dr["Time"] = det.Time; dr["DeleteReason"] = det.DeleteReason; dr["DeleteDate"] = det.DeleteDate; if (det.Type == 0) { abon += det.Summ; } if (det.Type == 1) { goods += det.Summ; } if (det.Type == 2) { serv += det.Summ; } } total = abon + goods + serv + charges; grSales.DataSource = dt; advBandedGridView1.BestFitColumns(); slblAbonements.Text = abon.ToString(); slblGoods.Text = goods.ToString(); slblServices.Text = serv.ToString(); lblRest.Text = total.ToString(); }