private void pictureBox1_Click(object sender, EventArgs e) { frmDashBoardSettings settings = new frmDashBoardSettings(); settings.ShowDialog(); LoadShop(); }
private void frmDashBoard_Load(object sender, EventArgs e) { DataTable dtDashBoard = ObjDAL.ExecuteSelectStatement("SELECT COUNT(1) FROM " + clsUtility.DBName + ".[dbo].[tblDashBoard] WITH(NOLOCK)"); if (dtDashBoard.Rows.Count == 0) { if (clsFormRights.HasFormRight(clsFormRights.Forms.frmDashBoardSettings) || clsUtility.IsAdmin) { frmDashBoardSettings frmDashBoardSettings = new frmDashBoardSettings(); frmDashBoardSettings.ShowDialog(); LoadShop(); } else { clsUtility.ShowInfoMessage("You have no rights to perform this task", clsUtility.strProjectTitle); } } else { LoadShop(); ShowDashBoardDate(); } }
private void LoadShop() { TotalHeaderRate = 0; TotalHeaderQTY = 0; flowLayoutPanel1.Controls.Clear(); DataTable dtDashBoard = ObjDAL.ExecuteSelectStatement("SELECT * FROM " + clsUtility.DBName + ".[dbo].[tblDashBoard] WITH(NOLOCK)"); if (ObjUtil.ValidateTable(dtDashBoard)) { string[] strShops = dtDashBoard.Rows[0]["Shopes"].ToString().Split(','); int index = Convert.ToInt32(dtDashBoard.Rows[0]["Specification"]); DateTime fromDate = Convert.ToDateTime(dtDashBoard.Rows[0]["FromDate"]); DateTime toDate = Convert.ToDateTime(dtDashBoard.Rows[0]["ToDate"]); int TimerInterval = Convert.ToInt32(dtDashBoard.Rows[0]["RefreshRate"]) * 1000; timer1.Interval = TimerInterval; timer1.Enabled = true; for (int i = 0; i < strShops.Length; i++) { string shopID = strShops[i]; string TotalQTY_Rate = ""; string reportQuery = ""; string strShopeName = GetShopeName(shopID); if (index == 0) //Employee ( Sales Man) { reportQuery = " SELECT v1.Name as SalesMan , SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + " AND v2.Rate>0 Group by Name"; TotalQTY_Rate = "select SUM(QTY) as TotalQTY, SUM(Rate) as TotalRate from ( SELECT v1.Name as SalesMan , SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + " AND v2.Rate>0 Group by Name ) as tb"; } else if (index == 1) //Color { reportQuery = " SELECT v2.ColorName as Color , SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + " AND v2.Rate>0 Group by ColorName"; TotalQTY_Rate = " select SUM(QTY) as TotalQTY,SUM(Rate) as TotalRate from ( SELECT v2.ColorName as Color , SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + " AND v2.Rate>0 Group by ColorName ) as tb"; } else if (index == 2) //Product { reportQuery = " SELECT v2.ProductName as ItemName, SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + " AND v2.Rate>0 Group by v2.ProductName"; TotalQTY_Rate = " select SUM(QTY) as TotalQTY,SUM(Rate) as TotalRate from ( SELECT v2.ProductName as ItemName, SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + " AND v2.Rate>0 Group by v2.ProductName ) as tb"; } else if (index == 3) //Invoice/Bill No { reportQuery = " SELECT v1.InvoiceNumber as InvoiceNo, SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + "AND v2.Rate>0 Group by v1.InvoiceNumber"; TotalQTY_Rate = "select SUM(QTY) as TotalQTY,SUM(Rate) as TotalRate from ( SELECT v1.InvoiceNumber as InvoiceNo, SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + " AND v2.Rate>0 Group by v1.InvoiceNumber ) as tb"; } else if (index == 4) // Category Department { reportQuery = " SELECT (select CategoryName from CategoryMaster where CategoryID=v2.CategoryID ) as Category, SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + "AND v2.Rate>0 Group by v2.CategoryID"; TotalQTY_Rate = " select SUM(QTY) as TotalQTY,SUM(Rate) as TotalRate from ( SELECT (select CategoryName from CategoryMaster where CategoryID=v2.CategoryID ) as Category, SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + " AND v2.Rate>0 Group by v2.CategoryID ) as tb"; } else if (index == 5) // Model No { reportQuery = " SELECT v2.ModelNo, SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + "AND v2.Rate>0 Group by v2.ModelNo"; TotalQTY_Rate = " select SUM(QTY) as TotalQTY,SUM(Rate) as TotalRate from ( SELECT v2.ModelNo, SUM(v2.QTY) as QTY, SUM(v2.Rate) as Rate FROM " + " " + clsUtility.DBName + ".dbo.View_SalesBillDetails v1 JOIN " + clsUtility.DBName + ".dbo.View_SalesDetails v2 " + " ON v1.id = v2.InvoiceID " + " where v1.ShopeID = " + shopID + " AND v1.InvoiceDate between '" + fromDate.ToString("yyyy-MM-dd") + "' AND '" + toDate.ToString("yyyy-MM-dd") + "' " + " AND v2.Rate>0 Group by v2.ModelNo ) as tb"; } string TotalQTYValue = "0"; string TotalRateValue = "0"; DataTable dtQTY_Rate = ObjDAL.ExecuteSelectStatement(TotalQTY_Rate); if (ObjUtil.ValidateTable(dtQTY_Rate)) { TotalQTYValue = dtQTY_Rate.Rows[0]["TotalQTY"].ToString(); TotalRateValue = dtQTY_Rate.Rows[0]["TotalRate"].ToString(); if (TotalQTYValue.Trim().Length == 0) { TotalQTYValue = "0"; } if (TotalRateValue.Trim().Length == 0) { TotalRateValue = "0"; } } SPanel sPanel = new SPanel(); sPanel.Size = new Size(271, 307); sPanel.BackColor = Color.FromArgb(33, 140, 116); Label lblShopeName = new Label(); lblShopeName.Location = label4.Location; lblShopeName.Font = label4.Font; lblShopeName.ForeColor = label4.ForeColor; lblShopeName.Text = strShopeName; lblShopeName.Size = new Size(257, 22); lblShopeName.AutoSize = false; lblShopeName.TextAlign = ContentAlignment.MiddleCenter; sPanel.Controls.Add(lblShopeName); Label lblTotalQTY = new Label(); lblTotalQTY.Text = "Total QTY :"; lblTotalQTY.Font = label5.Font; lblTotalQTY.ForeColor = label5.ForeColor; lblTotalQTY.Location = label5.Location; sPanel.Controls.Add(lblTotalQTY); Label QTYvalue = new Label(); QTYvalue.Text = TotalQTYValue; QTYvalue.Font = label7.Font; QTYvalue.ForeColor = label7.ForeColor; QTYvalue.Location = label7.Location; sPanel.Controls.Add(QTYvalue); Label lblTotalRate = new Label(); lblTotalRate.Text = "Total Rate :"; lblTotalRate.Font = label6.Font; lblTotalRate.ForeColor = label6.ForeColor; lblTotalRate.Location = label6.Location; sPanel.Controls.Add(lblTotalRate); Label lblTotalRateValue = new Label(); lblTotalRateValue.Text = TotalRateValue; lblTotalRateValue.Font = label8.Font; lblTotalRateValue.ForeColor = label8.ForeColor; lblTotalRateValue.Location = label8.Location; sPanel.Controls.Add(lblTotalRateValue); DataGridView dgv = new DataGridView(); dgv.DataSource = ObjDAL.ExecuteSelectStatement(reportQuery); dgv.DataBindingComplete += Dgv_DataBindingComplete; dgv.Location = dataGridView1.Location; dgv.Size = dataGridView1.Size; dgv.AllowUserToDeleteRows = false; dgv.ColumnHeadersHeight = dgv.ColumnHeadersHeight + 2; sPanel.Controls.Add(dgv); flowLayoutPanel1.Controls.Add(sPanel); TotalHeaderRate = TotalHeaderRate + Convert.ToDecimal(TotalRateValue); TotalHeaderQTY = TotalHeaderQTY + Convert.ToDecimal(TotalQTYValue); } lblHeaderQTY.Text = TotalHeaderQTY.ToString(); lblHeaderTotalRate.Text = TotalHeaderRate.ToString(); } else { frmDashBoardSettings frmDashBoardSettings = new frmDashBoardSettings(); frmDashBoardSettings.ShowDialog(); this.Close(); } }