private void DisplayMOP() { gSB.Clear(); gSB.Append(" SELECT MO.MOPType, MO.NetAmount, MO.CHQNo, MO.Bank, MO.Branch, MO.CardCompany, MO.MOPID, BR.BillID AS SaleBillID " + " FROM tblMOP AS MO INNER JOIN " + " tblSaleBrief AS BR ON BR.BillID = MO.BillID " + " WHERE BR.BillNo = '" + txtRefNo.Text.Trim() + "' "); dataTable = GlbData.getDataTable(gSB.ToString()); dgvMOP.DataSource = dataTable; SettingGridMOP(); }
private void DisplayMOP() { gSB.Clear(); gSB.Append(" SELECT MO.MOPType, MO.CashTendered, MO.CHQNo, MO.Bank, MO.Branch, MO.CardCompany, MO.MOPID, CP.BillID " + " FROM tblMOP AS MO INNER JOIN " + " tblCustPayBrief AS CP ON MO.MopID = CP.MopId " + " WHERE CP.BillNo = '" + txtRefNo.Text.Trim() + "' "); dataTable = GlbData.getDataTable(gSB.ToString()); dgvMOP.DataSource = dataTable; SettingGridMOP(); }
public void FillBranch() { try { string strQuery = " SELECT CustID, ADD1 + ' - ' + CITY AS NameOf FROM tblCompany ORDER BY NameOF"; dataTable = GlbData.getDataTable(strQuery); cboBranch.Items.Clear(); DataRow row = dataTable.NewRow(); row["NameOF"] = "ALL"; dataTable.Rows.InsertAt(row, 0); cboBranch.DataSource = dataTable; cboBranch.ValueMember = "CustID"; cboBranch.DisplayMember = "NameOF"; } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } finally { dataTable = null; } }
private void DispalyAllCHQ() { gSB.Clear(); gSB.Append(" SELECT * FROM tblMOP AS MO INNER JOIN tblBank AS BK ON MO.BankID = BK.CustID" + " WHERE (MO.IsDeposited = 'Y') AND (MO.IsRealized = 'N') AND (MO.IsReturned = 'N') AND (MO.MopType = 'CHQ') "); dataTable = GlbData.getDataTable(gSB.ToString()); lstGrid.Items.Clear(); foreach (DataRow dRow in dataTable.Rows) { ListViewItem LItem = new ListViewItem(); LItem.Text = dRow["RefNo"].ToString(); LItem.SubItems.Add(Convert.ToDateTime(dRow["CHQDate"]).ToShortDateString()); LItem.SubItems.Add(dRow["CHQNo"].ToString()); LItem.SubItems.Add(string.Format("{0:N}", Convert.ToDouble(dRow["NetAmount"]))); LItem.SubItems.Add(dRow["PerformedOn"].ToString()); LItem.SubItems.Add(dRow["Username"].ToString()); LItem.SubItems.Add(dRow["MopID"].ToString()); LItem.SubItems.Add(dRow["BankID"].ToString()); LItem.SubItems.Add(dRow["NameOF"].ToString()); LItem.SubItems.Add(dRow["OwnerID"].ToString()); LItem.SubItems.Add(dRow["OwnerName"].ToString()); lstGrid.Items.Add(LItem); } dataTable.Clear(); dataTable = null; TotalVal(); }
private void FillGrid() { gSB.Clear(); gSB.Append("SELECT * FROM tblSetFreeScheme WHERE ItemID = " + gItemID + ""); dataTable = GlbData.getDataTable(gSB.ToString()); foreach (DataRow dRow in dataTable.Rows) { dgvPrice.Rows.Add(Convert.ToDouble(dRow["FromQty"]).ToString(), Convert.ToDouble(dRow["ToQty"]).ToString(), Convert.ToDouble(dRow["FreeQty"]).ToString()); } dataTable = null; }
private void FillPrice() { gSB.Clear(); gSB.Append("SELECT * FROM tblSetBatchPrice WHERE ItemID = " + gItemID + ""); dataTable = GlbData.getDataTable(gSB.ToString()); foreach (DataRow dRow in dataTable.Rows) { dgvPrice.Rows.Add(string.Format("{0:N}", Convert.ToDouble(dRow["Price"]))); } dataTable = null; }
private void DispalySales() { InsertRecords(); gSB.Clear(); gSB.Append("SELECT MonthOf, Sales, CCN, MonthIndex FROM tblTempChart ORDER BY MonthIndex"); dataTable = GlbData.getDataTable(gSB.ToString()); chart1.DataSource = dataTable; chart1.Series["Sales"].XValueMember = "MonthOf"; chart1.Series["Sales"].YValueMembers = "Sales"; chart1.Series["Returns"].YValueMembers = "CCN"; chart1.Titles.Add("Sales Comparison"); }
private void getCompanyInfo() { gSB.Clear(); gSB.Append("SELECT * FROM tblCompany WHERE CustID = " + GlobalData.GCompID + " "); dataTable = GlbData.getDataTable(gSB.ToString()); foreach (DataRow dRow in dataTable.Rows) { GlobalData.GCompName = dRow["NameOF"].ToString(); GlobalData.GComAdd1 = dRow["Add1"].ToString(); GlobalData.GComAdd2 = dRow["Add2"].ToString(); GlobalData.GComCity = dRow["City"].ToString(); GlobalData.GComTp1 = dRow["Tp1"].ToString(); } dataTable = null; }
private void FillStockRoom() { gSB.Clear(); gSB.Append("SELECT ST.CompID, CM.Add1 + ' - ' + CM.City AS Company, ST.StockRoomID, SM.NameOF AS StockRoom " + " FROM tblSetCompStockRoom AS ST INNER JOIN " + " tblStockRoom AS SM ON SM.CustID = ST.StockRoomID INNER JOIN " + " tblCompany AS CM ON CM.CustID = ST.CompID "); dataTable = GlbData.getDataTable(gSB.ToString()); foreach (DataRow row in dataTable.Rows) { dgvGrid.Rows.Add(Convert.ToInt32(row["CompID"]), row["Company"].ToString(), Convert.ToInt32(row["StockRoomID"]), row["StockRoom"].ToString()); } dataTable.Clear(); dataTable = null; }
private void FillCustomer() { gSB.Clear(); gSB.Append("SELECT CU.Code, CU.NameOf, SE.ID FROM tblDefSetting AS SE INNER JOIN tblCustomer AS CU ON SE.ID = CU.CustID WHERE SE.TranCode = 'DEFCUS'"); DataTable dataTable = new DataTable(); dataTable = GlbData.getDataTable(gSB.ToString()); foreach (DataRow dRow in dataTable.Rows) { txtSuppCode.Text = dRow["Code"].ToString(); lblSupplier.Text = dRow["NameOf"].ToString(); gCustID = Convert.ToInt32(dRow["ID"]); } dataTable = null; }
private void InsertRecord() { MySqlCommand cmd5 = new MySqlCommand("TRUNCATE TABLE tblTEMPPOAnalyser", GlobalData.GCon); cmd5.ExecuteNonQuery(); gSB.Clear(); gSB.Append(" INSERT INTO tblTEMPPOAnalyser ( " + " ItemId, ItemCode, ItemName, CategoryId, CategoryCode, CategoryName, " + " SupplierId, SupplierName, SupplierCode, CostPrice, SellingPrice, WholeSalePrice, " + " OpenStk, Purchase, PurchaseFree, SCN, SCNFree, Sale, SaleFree, CCN, CCNFree, STKAdd, " + " StkDED, CloseStk ) " + " SELECT IT.CustId AS ItemId, IT.Code AS ItemCode, IT.NameOF AS ItemName, MC.CustID AS CategoryID, " + " MC.Code AS CategoryCode, MC.NameOF AS CategoryName, SU.CustID AS SupplierId, SU.NAmeOf AS SupplierName, " + " SU.Code AS SupplierCode, IT.Cost AS CostPrice, IT.SellingPrice AS SellingPrice, IT.WholeSalePrice AS WholeSalePrice, " + " 0 AS OpenStk, 0 AS Purchase, 0 AS PurchaseFree, 0 AS SCN, 0 AS SCNFree, 0 AS Sale, 0 AS SaleFree, 0 AS CCN, 0 AS CCNFree, " + " 0 AS StkAdd, 0 AS StkDED, 0 AS CloseStk " + " FROM tblItem AS IT LEFT OUTER JOIN " + " tblComSTKStore AS STK ON IT.CustID = STK.ItemID INNER JOIN " + " tblStockRoom AS ST ON ST.CustID = STK.StockRoomID INNER JOIN " + " tblSupplier AS SU ON IT.SupplierID = SU.CustID LEFT OUTER JOIN " + " tblMajorCategory AS MC ON IT.CategoryID = MC.CustID "); MySqlCommand cmd = new MySqlCommand(gSB.ToString(), GlobalData.GCon); cmd.ExecuteNonQuery(); //PURCHASE - FREE gSB.Clear(); gSB.Append("UPDATE t1 SET t1.Purchase = PR.Purchase, t1.PurchaseFree = PR.PurchaseFree " + " FROM tblTEMPPOAnalyser AS t1 INNER JOIN " + " (SELECT ItemId, SUM(QtyIn) AS Purchase, SUM(FreeIn) AS PurchaseFree " + " FROM tblBinCard WHERE DateOn BETWEEN CONVERT(date, '" + dtpFrom.Text + "') AND CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'SUP002' " + " GROUP BY ItemId) AS PR ON PR.ItemId = t1.ItemId "); MySqlCommand command1 = new MySqlCommand(gSB.ToString(), GlobalData.GCon); command1.ExecuteNonQuery(); //SCN - FREE gSB.Clear(); gSB.Append("UPDATE t1 SET t1.SCN = SR.SCN, t1.SCNFree = SR.SCNFree " + " FROM tblTEMPPOAnalyser AS t1 INNER JOIN " + " (SELECT ItemId, SUM(QtyOut) AS SCN, SUM(FreeOut) AS SCNFree " + " FROM tblBinCard WHERE DateOn BETWEEN CONVERT(date, '" + dtpFrom.Text + "') AND CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'SUP003' " + " GROUP BY ItemId) AS SR ON SR.ItemId = t1.ItemId "); MySqlCommand command2 = new MySqlCommand(gSB.ToString(), GlobalData.GCon); command2.ExecuteNonQuery(); //SALE -FREE gSB.Clear(); gSB.Append("UPDATE t1 SET t1.Sale = SAL.Sale, t1.SaleFree = SAL.SaleFree " + " FROM tblTEMPPOAnalyser AS t1 INNER JOIN " + " (SELECT ItemId, SUM(QtyOut) AS Sale, SUM(FreeOut) AS SaleFree " + " FROM tblBinCard WHERE DateOn BETWEEN CONVERT(date, '" + dtpFrom.Text + "') AND CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'CUS002' " + " GROUP BY ItemId) AS SAL ON SAL.ItemId = t1.ItemId "); MySqlCommand command3 = new MySqlCommand(gSB.ToString(), GlobalData.GCon); command3.ExecuteNonQuery(); //CCN - FREE gSB.Clear(); gSB.Append("UPDATE t1 SET t1.CCN = CN.CCN, t1.CCNFree = CN.CCNFree " + " FROM tblTEMPPOAnalyser AS t1 INNER JOIN " + " (SELECT ItemId, SUM(QtyIn) AS CCN, SUM(FreeIn) AS CCNFree " + " FROM tblBinCard WHERE DateOn BETWEEN CONVERT(date, '" + dtpFrom.Text + "') AND CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'CUS003' " + " GROUP BY ItemId) AS CN ON CN.ItemId = t1.ItemId "); MySqlCommand command4 = new MySqlCommand(gSB.ToString(), GlobalData.GCon); command4.ExecuteNonQuery(); //ADJUSTMENT gSB.Clear(); gSB.Append("UPDATE t1 SET t1.StkADD = ADJ.StkADD, t1.StkDED = ADJ.StkDED " + " FROM tblTEMPPOAnalyser AS t1 INNER JOIN " + " (SELECT ItemId, SUM(QtyIn) AS StkADD, SUM(QtyOut) AS StkDED " + " FROM tblBinCard WHERE DateOn BETWEEN CONVERT(date, '" + dtpFrom.Text + "') AND CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'STO003' " + " GROUP BY ItemId) AS ADJ ON ADJ.ItemId = t1.ItemId "); MySqlCommand cmd4 = new MySqlCommand(gSB.ToString(), GlobalData.GCon); cmd4.ExecuteNonQuery(); DataTable dataTable = GlbData.getDataTable("SELECT ItemId FROM tblTEMPPOAnalyser"); for (int i = 0; i < dataTable.Rows.Count; i++) { gSB.Clear(); gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS Purchase, ISNULL(SUM(FreeIn),0) AS PurchaseFree " + " FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpFrom.Text + "') AND TranCode = 'SUP002' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " + " GROUP BY ItemId"); string purchaseOP = GlbData.GetValue(gSB.ToString(), "Purchase").ToString(); string purchaseFreeOP = GlbData.GetValue(gSB.ToString(), "PurchaseFree").ToString(); gSB.Clear(); gSB.Append("SELECT ItemId, ISNULL(SUM(QtyOut),0) AS SCN, ISNULL(SUM(FreeOut),0) AS SCNFree " + " FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpFrom.Text + "') AND TranCode = 'SUP003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " + " GROUP BY ItemId"); string scnOP = GlbData.GetValue(gSB.ToString(), "SCN").ToString(); string scnFreeOP = GlbData.GetValue(gSB.ToString(), "SCNFree").ToString(); gSB.Clear(); gSB.Append("SELECT ItemId, ISNULL(SUM(QtyOut),0) AS Sale, ISNULL(SUM(FreeOut),0) AS SaleFree " + " FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpFrom.Text + "') AND TranCode = 'CUS002' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " + " GROUP BY ItemId"); string saleOP = GlbData.GetValue(gSB.ToString(), "Sale").ToString(); string saleFreeOP = GlbData.GetValue(gSB.ToString(), "SaleFree").ToString(); gSB.Clear(); gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS CCN, ISNULL(SUM(FreeIn),0) AS CCNFree " + " FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpFrom.Text + "') AND TranCode = 'CUS003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " + " GROUP BY ItemId"); string ccnOP = GlbData.GetValue(gSB.ToString(), "CCN").ToString(); string ccnFreeOP = GlbData.GetValue(gSB.ToString(), "CCNFree").ToString(); gSB.Clear(); gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS StkADD, ISNULL(SUM(QtyOut),0) AS StkDED " + " FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpFrom.Text + "') AND TranCode = 'STO003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " + " GROUP BY ItemId"); string stkAddOP = GlbData.GetValue(gSB.ToString(), "StkADD").ToString(); string stkDedOP = GlbData.GetValue(gSB.ToString(), "StkDED").ToString(); double purchaseOPSTK = 0; double purchaseFreeOPSTK = 0; double scnOPSTK = 0; double scnFreeOPSTK = 0; double saleOPSTK = 0; double saleFreeOPSTK = 0; double ccnOPSTK = 0; double ccnFreeOPSTK = 0; double stkAddOPSTK = 0; double stkDedOPSTK = 0; if (string.IsNullOrEmpty(purchaseOP)) { purchaseOPSTK = 0; } else { purchaseOPSTK = Convert.ToDouble(purchaseOP); } if (string.IsNullOrEmpty(purchaseFreeOP)) { purchaseFreeOPSTK = 0; } else { purchaseFreeOPSTK = Convert.ToDouble(purchaseFreeOP); } if (string.IsNullOrEmpty(scnOP)) { scnOPSTK = 0; } else { scnOPSTK = Convert.ToDouble(scnOP); } if (string.IsNullOrEmpty(scnFreeOP)) { scnFreeOPSTK = 0; } else { scnFreeOPSTK = Convert.ToDouble(scnFreeOP); } if (string.IsNullOrEmpty(saleOP)) { saleOPSTK = 0; } else { saleOPSTK = Convert.ToDouble(saleOP); } if (string.IsNullOrEmpty(saleFreeOP)) { saleFreeOPSTK = 0; } else { saleFreeOPSTK = Convert.ToDouble(saleFreeOP); } if (string.IsNullOrEmpty(ccnOP)) { ccnOPSTK = 0; } else { ccnOPSTK = Convert.ToDouble(ccnOP); } if (string.IsNullOrEmpty(ccnFreeOP)) { ccnFreeOPSTK = 0; } else { ccnFreeOPSTK = Convert.ToDouble(ccnFreeOP); } if (string.IsNullOrEmpty(stkAddOP)) { stkAddOPSTK = 0; } else { stkAddOPSTK = Convert.ToDouble(stkAddOP); } if (string.IsNullOrEmpty(stkDedOP)) { stkDedOPSTK = 0; } else { stkDedOPSTK = Convert.ToDouble(stkDedOP); } double openSTK = (purchaseOPSTK + purchaseFreeOPSTK - scnOPSTK - scnFreeOPSTK - saleOPSTK - saleFreeOPSTK + ccnOPSTK + ccnFreeOPSTK + stkAddOPSTK - stkDedOPSTK); MySqlCommand cmdOP = new MySqlCommand("UPDATE tblTEMPPOAnalyser SET OpenStk = " + openSTK + " WHERE ItemId=" + dataTable.Rows[i][0].ToString() + " ", GlobalData.GCon); cmdOP.ExecuteNonQuery(); gSB.Clear(); gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS Purchase, ISNULL(SUM(FreeIn),0) AS PurchaseFree " + " FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'SUP002' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " + " GROUP BY ItemId"); string purchaseCL = GlbData.GetValue(gSB.ToString(), "Purchase").ToString(); string purchaseFreeCL = GlbData.GetValue(gSB.ToString(), "PurchaseFree").ToString(); gSB.Clear(); gSB.Append("SELECT ItemId, ISNULL(SUM(QtyOut),0) AS SCN, ISNULL(SUM(FreeOut),0) AS SCNFree " + " FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'SUP003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " + " GROUP BY ItemId"); string scnCL = GlbData.GetValue(gSB.ToString(), "SCN").ToString(); string scnFreeCL = GlbData.GetValue(gSB.ToString(), "SCNFree").ToString(); gSB.Clear(); gSB.Append("SELECT ItemId, ISNULL(SUM(QtyOut),0) AS Sale, ISNULL(SUM(FreeOut),0) AS SaleFree " + " FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'CUS002' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " + " GROUP BY ItemId"); string saleCL = GlbData.GetValue(gSB.ToString(), "Sale").ToString(); string saleFreeCL = GlbData.GetValue(gSB.ToString(), "SaleFree").ToString(); gSB.Clear(); gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS CCN, ISNULL(SUM(FreeIn),0) AS CCNFree " + " FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'CUS003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " + " GROUP BY ItemId"); string ccnCL = GlbData.GetValue(gSB.ToString(), "CCN").ToString(); string ccnFreeCL = GlbData.GetValue(gSB.ToString(), "CCNFree").ToString(); gSB.Clear(); gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS StkADD, ISNULL(SUM(QtyOut),0) AS StkDED " + " FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'STO003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " + " GROUP BY ItemId"); string stkAddCL = GlbData.GetValue(gSB.ToString(), "StkADD").ToString(); string stkDedCL = GlbData.GetValue(gSB.ToString(), "StkDED").ToString(); double purchaseCLSTK = 0; double purchaseFreeCLSTK = 0; double scnCLSTK = 0; double scnFreeCLSTK = 0; double saleCLSTK = 0; double saleFreeCLSTK = 0; double ccnCLSTK = 0; double ccnFreeCLSTK = 0; double stkAddCLSTK = 0; double stkDedCLSTK = 0; if (string.IsNullOrEmpty(purchaseCL)) { purchaseCLSTK = 0; } else { purchaseCLSTK = Convert.ToDouble(purchaseCL); } if (string.IsNullOrEmpty(purchaseFreeCL)) { purchaseFreeCLSTK = 0; } else { purchaseFreeCLSTK = Convert.ToDouble(purchaseFreeCL); } if (string.IsNullOrEmpty(scnCL)) { scnCLSTK = 0; } else { scnCLSTK = Convert.ToDouble(scnCL); } if (string.IsNullOrEmpty(scnFreeCL)) { scnFreeCLSTK = 0; } else { scnFreeCLSTK = Convert.ToDouble(scnFreeCL); } if (string.IsNullOrEmpty(saleCL)) { saleCLSTK = 0; } else { saleCLSTK = Convert.ToDouble(saleCL); } if (string.IsNullOrEmpty(saleFreeCL)) { saleFreeCLSTK = 0; } else { saleFreeCLSTK = Convert.ToDouble(saleFreeCL); } if (string.IsNullOrEmpty(ccnCL)) { ccnCLSTK = 0; } else { ccnCLSTK = Convert.ToDouble(ccnCL); } if (string.IsNullOrEmpty(ccnFreeCL)) { ccnFreeCLSTK = 0; } else { ccnFreeCLSTK = Convert.ToDouble(ccnFreeCL); } if (string.IsNullOrEmpty(stkAddCL)) { stkAddCLSTK = 0; } else { stkAddCLSTK = Convert.ToDouble(stkAddCL); } if (string.IsNullOrEmpty(stkDedCL)) { stkDedCLSTK = 0; } else { stkDedCLSTK = Convert.ToDouble(stkDedCL); } double closeSTK = (purchaseCLSTK + purchaseFreeCLSTK - scnCLSTK - scnFreeCLSTK - saleCLSTK - saleFreeCLSTK + ccnCLSTK + ccnFreeCLSTK + stkAddCLSTK - stkDedCLSTK); MySqlCommand cmdCL = new MySqlCommand("UPDATE tblTEMPPOAnalyser SET CloseStk = " + closeSTK + " WHERE ItemId=" + dataTable.Rows[i][0].ToString() + " ", GlobalData.GCon); cmdCL.ExecuteNonQuery(); } }