Ejemplo n.º 1
0
        private void LoadReport()
        {
            try
            {
                SQLConn.sqL = "SELECT ProductCode, P.Description, STR_TO_DATE(REPLACE(TDate, '-', '/'), '%m/%d/%Y') as DateOut, SUM(TD.Quantity) as Quantity, TD.ItemPrice as Price, (SUM(TD.Quantity) * TD.ItemPrice) as TotalAmount FROM Product as P, Transactions as T, TransactionDetails as TD WHERE P.ProductNo = TD.ProductNo AND TD.InvoiceNo = T.InvoiceNo AND STR_TO_DATE(REPLACE(TDate, '-', '/'), '%m/%d/%Y') BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.ToString("yyyy-MM-dd") + "' GROUP BY P.ProductNo, TDate ORDER By TDate, Description";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.da  = new MySqlDataAdapter(SQLConn.cmd);

                this.dsReportC.StocksOut.Clear();
                SQLConn.da.Fill(this.dsReportC.StocksOut);

                ReportParameter startDate = new ReportParameter("StartDate", StartDate.ToString());
                ReportParameter endDate   = new ReportParameter("EndDate", EndDate.ToString());
                this.reportViewer1.LocalReport.SetParameters(new ReportParameter[] { startDate, endDate });

                this.reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
                this.reportViewer1.ZoomPercent = 40;
                this.reportViewer1.ZoomMode    = Microsoft.Reporting.WinForms.ZoomMode.Percent;

                this.reportViewer1.RefreshReport();
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
        }
        public void priftLoss()
        {
            try
            {
                SQLConn.sqL = "select SUM(Total),sum(PurchasePrice * Quantity) from transactiondetails INNER JOIN transactions ON transactiondetails.InvoiceNo = transactions.InvoiceNo where TDate like '7/9/2019' ";
                SQLConn.ConnDB();
                SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();


                if (SQLConn.dr.Read())
                {
                    //txtName = SQLConn.dr[0].ToString();
                    //txtName.Text = SQLConn.dr[1].ToString();

                    totalselling  = int.Parse(SQLConn.dr[0].ToString());
                    totalpurchase = int.Parse(SQLConn.dr[1].ToString());
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 3
0
        private bool IsPasswordCorrect()
        {
            bool ret = false;

            try
            {
                SQLConn.sqL = "SELECT * FROM Staff WHERE Role ='Admin' AND UPassword = '******'";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                if (SQLConn.dr.Read() == true)
                {
                    ret = true;
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.Message);
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }

            return(ret);
        }
Ejemplo n.º 4
0
        private void GetProductNo()
        {
            try
            {
                SQLConn.sqL = "SELECT ProductNo FROM Product ORDER BY ProductNo DESC";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                if (SQLConn.dr.Read() == true)
                {
                    lblProductNo.Text = (Convert.ToInt32(SQLConn.dr["ProductNo"]) + 1).ToString();
                }
                else
                {
                    lblProductNo.Text = "1";
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 5
0
        public void LoadProducts(string strSearch)
        {
            try
            {
                SQLConn.sqL = "SELECT ProductNo, ProductCOde, P.Description, Barcode, UnitPrice, StocksOnHand, ReorderLevel, CategoryName FROM Product as P LEFT JOIN Category C ON P.CategoryNo = C.CategoryNo WHERE P.Description LIKE '" + strSearch + "%' ORDER BY Description";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                ListViewItem x = null;
                ListView1.Items.Clear();

                while (SQLConn.dr.Read() == true)
                {
                    x = new ListViewItem(SQLConn.dr["ProductNo"].ToString());
                    x.SubItems.Add(SQLConn.dr["ProductCode"].ToString());
                    x.SubItems.Add(SQLConn.dr["Description"].ToString());
                    x.SubItems.Add(SQLConn.dr["CategoryName"].ToString());
                    x.SubItems.Add(Strings.Format(SQLConn.dr["UnitPrice"], "#,##0.00"));
                    x.SubItems.Add(SQLConn.dr["StocksOnHand"].ToString());
                    x.SubItems.Add(SQLConn.dr["ReOrderLevel"].ToString());

                    ListView1.Items.Add(x);
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 6
0
        public void GetInvoiceNo()
        {
            try
            {
                SQLConn.sqL = "SELECT Id FROM Invoice ORDER BY Id DESC";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                if (SQLConn.dr.Read() == true)
                {
                    txtInvoiceNo.Text = "INV-000-" + (Convert.ToInt32(SQLConn.dr["Id"]) + 1).ToString();
                }
                else
                {
                    txtInvoiceNo.Text = "INV-000-1";
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
        private void LoadInvoices(DateTime startDate, DateTime endDate)
        {
            try
            {
                SQLConn.sqL = "SELECT STR_TO_DATE(REPLACE(TDate, '-', '/'), '%m/%d/%Y') as TDate, InvoiceNo, CONCAT(lastname, ', ', firstname, ' ', MI) as StaffName, TotalAmount, Status FROM Transactions T INNER JOIN staff S ON T.StaffID = S.StaffID WHERE STR_TO_DATE(REPLACE(TDATE, '-', '/'), '%m/%d/%Y') BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' AND InvoiceNo LIKE '%" + txtName.Text + "%' ORDER BY TDATE, InvoiceNo Desc";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                dgw.Rows.Clear();
                string status = "";
                while (SQLConn.dr.Read() == true)
                {
                    if (Convert.ToInt32(SQLConn.dr["Status"]) == 0)
                    {
                        status = "Hide";
                    }
                    else
                    {
                        status = "Unhide";
                    }
                    dgw.Rows.Add(status, Strings.Format(SQLConn.dr["TDate"], "MM/dd/yyyy"), SQLConn.dr["InvoiceNo"].ToString(), SQLConn.dr["StaffName"].ToString(), Strings.FormatNumber(SQLConn.dr["TotalAmount"]));
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 8
0
        public void LoadReturnItems(string invoiceNo)
        {
            try
            {
                SQLConn.sqL = "SELECT ProductCode, Description, Quantity, UnitPrice, (Quantity * UnitPrice) ExtendedPrice FROM SalesReturnItem sri INNER JOIN Product p ON p.ProductNo = sri.ProductID WHERE Sri.InvoiceNo = '" + invoiceNo + "' ORDER BY Description ";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                ListViewItem x = null;
                ListView2.Items.Clear();


                while (SQLConn.dr.Read() == true)
                {
                    x = new ListViewItem(SQLConn.dr["ProductCode"].ToString());
                    x.SubItems.Add(SQLConn.dr["Description"].ToString());
                    x.SubItems.Add(SQLConn.dr["Quantity"].ToString());
                    x.SubItems.Add(Strings.Format(SQLConn.dr["UnitPrice"], "#,##0.00"));
                    x.SubItems.Add(Strings.Format(SQLConn.dr["ExtendedPrice"], "#,##0.00"));
                    //Strings.Format(SQLConn.dr["ExtendedPrice"], "#,##0.00")
                    ListView2.Items.Add(x);
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 9
0
        public void LoadCategories(string strSearch)
        {
            try
            {
                SQLConn.sqL = "SELECT * FROM CATEGORY WHERE CategoryName LIKE '" + strSearch + "%' ORDER By CategoryName";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader(CommandBehavior.CloseConnection);

                ListViewItem x = null;
                ListView1.Items.Clear();

                while (SQLConn.dr.Read() == true)
                {
                    x = new ListViewItem(SQLConn.dr["CategoryNo"].ToString());
                    x.SubItems.Add(SQLConn.dr["CategoryName"].ToString());
                    x.SubItems.Add(SQLConn.dr["Description"].ToString());
                    ListView1.Items.Add(x);
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 10
0
        private void LoadReceiptInfo()
        {
            try
            {
                SQLConn.sqL = "SELECT T.InvoiceNo, CONCAT(Lastname,', ', Firstname, ' ', MI, '.') as StaffName, Quantity, Description, ItemPrice, (ItemPRice * Quantity) as ItemAmount, VatAmount, NonVatTotal, TotalAmount, Cash, PChange FROM Product as P, TransactionDetails as TD, Transactions as T, Staff as S, Payment as Pay WHERE P.ProductNo = TD.ProductNo AND TD.InvoiceNo = T.InvoiceNo AND S.StaffID = T.StaffID AND Pay.InvoiceNo = T.InvoiceNO AND T.InvoiceNo = '" + InvoiceNo + "'";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                if (SQLConn.dr.Read() == true)
                {
                    lblInvoice.Text  = SQLConn.dr["InvoiceNo"].ToString();
                    lblEmpName.Text  = SQLConn.dr["StaffName"].ToString();
                    lblDate.Text     = System.DateTime.Now.ToString("ddMMMyy") + " " + System.DateTime.Now.ToString("hh:mm tt");
                    lblVat.Text      = Strings.FormatNumber(SQLConn.dr["VatAmount"]);
                    lblSubtotal.Text = Strings.FormatNumber(SQLConn.dr["NonVatTotal"]);
                    lblTotal.Text    = Strings.FormatNumber(SQLConn.dr["TotalAmount"]);
                    lblCash.Text     = Strings.FormatNumber(SQLConn.dr["Cash"]);
                    lblChange.Text   = Strings.FormatNumber(SQLConn.dr["PChange"]);
                    LoadItemstoDatagrid();
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 11
0
        public void SalesReturns(DateTime startDate, DateTime endDate, string searchString)
        {
            try
            {
                SQLConn.sqL = "SELECT SalesReturnId, ReturnDate, sr.InvoiceNo, CONCAT(lastname, ', ', firstname, ' ', MI) as StaffName, TotalAmount, SUM(sri.Quantity) as TotalQuantity FROM SalesReturn sr INNER JOIN SalesReturnItem sri ON sr.InvoiceNo = sri.InvoiceNo INNER JOIN Staff s ON s.StaffId = sr.userID WHERE ReturnDate  BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' AND sr.InvoiceNo LIKE '%" + txtName.Text + "%' GROUP BY sr.InvoiceNo ORDER BY ReturnDate, sr.InvoiceNo DESC";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                ListViewItem x = null;
                ListView1.Items.Clear();


                while (SQLConn.dr.Read() == true)
                {
                    x = new ListViewItem(SQLConn.dr["SalesReturnId"].ToString());
                    x.SubItems.Add(Strings.Format(SQLConn.dr["ReturnDate"], "MM/dd/yyyy"));
                    x.SubItems.Add(SQLConn.dr["InvoiceNo"].ToString());
                    x.SubItems.Add(SQLConn.dr["StaffName"].ToString());
                    x.SubItems.Add(Strings.Format(SQLConn.dr["TotalAmount"], "#,##0.00"));
                    x.SubItems.Add(SQLConn.dr["TotalQuantity"].ToString());
                    //Strings.Format(SQLConn.dr["TotalAmount"], "#,##0.00")
                    ListView1.Items.Add(x);
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 12
0
        public void GetVATInfo()
        {
            try
            {
                SQLConn.sqL = "SELECT VATID, VatPercent FROM VatSetting";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();


                if (SQLConn.dr.Read())
                {
                    txtPercent.Tag  = SQLConn.dr[0];
                    txtPercent.Text = SQLConn.dr[1].ToString();
                    isAddingVat     = false;
                }
                else
                {
                    isAddingVat = true;
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 13
0
        private bool IsAdding()
        {
            bool ret = false;

            try
            {
                SQLConn.sqL = "SELECT * FROM Company";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();
                if (SQLConn.dr.Read() == true)
                {
                    ret = false;
                }
                else
                {
                    ret = true;
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
            return(ret);
        }
Ejemplo n.º 14
0
        private void GetProductInfo()
        {
            try
            {
                SQLConn.sqL = "SELECT ProductCode, Description,PurchasePrice, StocksOnHand FROM Product WHERE ProductNo =" + productID + "";
                SQLConn.ConnDB();
                SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                if (SQLConn.dr.Read() == true)
                {
                    lblProductCode.Text   = SQLConn.dr[0].ToString();
                    lblDescription.Text   = SQLConn.dr[1].ToString();
                    lblPrice.Text         = Strings.FormatNumber(SQLConn.dr[2]).ToString();
                    lblCurrentStocks.Text = SQLConn.dr[3].ToString();
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 15
0
        private void GetCustomerInfo(int custId)
        {
            try
            {
                SQLConn.sqL = "SELECT address FROM customer where id = " + custId;
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                while (SQLConn.dr.Read() == true)
                {
                    txtAddress.Text = SQLConn.dr[0].ToString();
                    txtShipTo.Text  = SQLConn.dr[0].ToString();
                }
            }
            catch (Exception ex)
            {
                //Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 16
0
        private void LoadUpdateStaff()
        {
            try
            {
                SQLConn.sqL = "SELECT * FROM STAFF WHERE StaffID = '" + LSStaffID + "'";
                SQLConn.ConnDB();
                SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                if (SQLConn.dr.Read() == true)
                {
                    lblProductNo.Text  = SQLConn.dr["StaffID"].ToString();
                    txtLastname.Text   = SQLConn.dr["lastname"].ToString();
                    txtFirstname.Text  = SQLConn.dr["Firstname"].ToString();
                    txtStreet.Text     = SQLConn.dr["Street"].ToString();
                    txtCity.Text       = SQLConn.dr["City"].ToString();
                    txtProvince.Text   = SQLConn.dr["Province"].ToString();
                    txtContractNo.Text = SQLConn.dr["ContactNo"].ToString();
                    txtUsername.Text   = SQLConn.dr["username"].ToString();
                    txtRole.Text       = SQLConn.dr["Role"].ToString();
                    txtPassword.Text   = SQLConn.dr["Password"].ToString();
                    txtConfirmPWD.Text = SQLConn.dr["Password"].ToString();
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 17
0
        private double GetTax()
        {
            double vatDecimal = 0;

            try
            {
                SQLConn.sqL = "SELECT VatPercent FROm VatSetting";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();
                if (SQLConn.dr.Read() == true)
                {
                    vatDecimal  = (Convert.ToDouble(SQLConn.dr[0]) / 100);
                    tax_percent = Convert.ToDouble(SQLConn.dr[0]);
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }

            return(vatDecimal);
        }
Ejemplo n.º 18
0
        //Decrease product's stocks on hand
        public void UpdateProductQuantity()
        {
            try
            {
                SQLConn.sqL = "UPDATE Product SET StocksOnHand = StocksOnHand - @Quantity WHERE ProductNo = @ProductNo";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.cmd.Parameters.Add("ProductNo", MySql.Data.MySqlClient.MySqlDbType.Int32);
                SQLConn.cmd.Parameters.Add("Quantity", MySql.Data.MySqlClient.MySqlDbType.Double);

                for (int i = 0; i <= dgw.Rows.Count - 1; i++)
                {
                    SQLConn.cmd.Parameters["ProductNo"].Value = dgw.Rows[i].Cells[0].Value;
                    SQLConn.cmd.Parameters["Quantity"].Value  = Convert.ToDouble(dgw.Rows[i].Cells[1].Value);
                    SQLConn.cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
        private void LoadInvoices(DateTime startDate, DateTime endDate, string searchString)
        {
            try
            {
                SQLConn.sqL = "SELECT STR_TO_DATE(REPLACE(TDate, '-', '/'), '%m/%d/%Y') as TDate, InvoiceNo, CONCAT(lastname, ', ', firstname, ' ', MI) as StaffName FROM Transactions T INNER JOIN staff S ON T.StaffID = S.StaffID WHERE STR_TO_DATE(REPLACE(TDATE, '-', '/'), '%m/%d/%Y') BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' AND InvoiceNo LIKE '%" + txtName.Text + "%' ORDER BY TDATE, InvoiceNo Desc";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                ListViewItem x = null;
                ListView1.Items.Clear();

                while (SQLConn.dr.Read() == true)
                {
                    x = new ListViewItem(Strings.Format(SQLConn.dr["TDate"], "MM/dd/yyyy"));
                    x.SubItems.Add(SQLConn.dr["InvoiceNo"].ToString());
                    x.SubItems.Add(SQLConn.dr["StaffName"].ToString());

                    ListView1.Items.Add(x);
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 20
0
 private void UpdateInvoice()
 {
     try
     {
         SQLConn.sqL = "UPDATE invoice SET InvoiceDate=@InvoiceDate,  CustomerPONo=@CustomerPONo, Terms=@Terms, Duration=@Duration, SalesPerson=@SalesPerson, DiscountPercent=@DiscountPercent, DiscountAmount=@DiscountAmount, TotalAmount=@TotalAmount, Subtotal=@Subtotal, TaxAmount=@TaxAmount, Note=@Note, CustomerId =@CustomerId, BillAddress=@BillAddress, Customer=@Customer WHERE InvoiceNo=@InvoiceNo";
         SQLConn.ConnDB();
         SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
         SQLConn.cmd.Parameters.AddWithValue("@InvoiceDate", dtpDate.Value.ToString("yyyy-MM-dd"));
         SQLConn.cmd.Parameters.AddWithValue("@InvoiceNo", txtInvoiceNo.Text);
         SQLConn.cmd.Parameters.AddWithValue("@CustomerPONo", txtPONumber.Text);
         SQLConn.cmd.Parameters.AddWithValue("@Terms", cmbTerms.Text);
         SQLConn.cmd.Parameters.AddWithValue("@Duration", txtDuration.Text);
         SQLConn.cmd.Parameters.AddWithValue("@SalesPerson", txtSalesperson.Text);
         SQLConn.cmd.Parameters.AddWithValue("@DiscountPercent", lblDiscountPercent.Text);
         SQLConn.cmd.Parameters.AddWithValue("@DiscountAmount", lblDiscountAmount.Text.Replace(",", ""));
         SQLConn.cmd.Parameters.AddWithValue("@TotalAmount", lblTotalAmount.Text.Replace(",", ""));
         SQLConn.cmd.Parameters.AddWithValue("@Subtotal", lblSubtotal.Text.Replace(",", ""));
         SQLConn.cmd.Parameters.AddWithValue("@TaxAmount", lblTax.Text.Replace(",", ""));
         SQLConn.cmd.Parameters.AddWithValue("@Note", txtNoteComment.Text);
         SQLConn.cmd.Parameters.AddWithValue("@CustomerId", customer_id);
         SQLConn.cmd.Parameters.AddWithValue("@BillAddress", txtAddress.Text);
         SQLConn.cmd.Parameters.AddWithValue("@Customer", cmbCustomer.Text);
         SQLConn.cmd.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
         is_invoice_success = false;
         Interaction.MsgBox(ex.ToString());
     }
 }
        private void LoadCompanySetting()
        {
            try
            {
                SQLConn.sqL = "SELECT HInvoice FROM Company";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();


                if (SQLConn.dr.Read() == true)
                {
                    if (Convert.ToInt32(SQLConn.dr["HInvoice"]) == 1)
                    {
                        checkBox1.Checked = true;
                    }
                    else
                    {
                        checkBox1.Checked = false;
                    }
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 22
0
 private void GetInvoiceShipping()
 {
     try
     {
         SQLConn.sqL = "SELECT ShiptoAddress, ShipBy, TrackingNo, ShippingCost, ShippingTax FROM InvoiceShipping where InvoiceNo ='" + txtInvoiceNo.Text + "'";
         SQLConn.ConnDB();
         SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
         SQLConn.dr  = SQLConn.cmd.ExecuteReader();
         if (SQLConn.dr.Read())
         {
             txtShipTo.Text       = SQLConn.dr[0].ToString();
             txtShipBy.Text       = SQLConn.dr[1].ToString();
             txtTrackingNo.Text   = SQLConn.dr[2].ToString();
             txtShippingCost.Text = SQLConn.dr[3].ToString();
             cmbShippingTax.Text  = SQLConn.dr[4].ToString();
         }
     }
     catch (Exception ex)
     {
         Interaction.MsgBox(ex.ToString());
     }
     finally
     {
         SQLConn.cmd.Dispose();
         SQLConn.conn.Close();
     }
 }
Ejemplo n.º 23
0
        private void LoadUpdateCategory()
        {
            try
            {
                SQLConn.sqL = "SELECT ProductNo, ProductCode, P.Description, Barcode, P.CategoryNo, CategoryName, UnitPrice, StocksOnHand, ReorderLevel FROM Product as P LEFT JOIN Category as C ON P.CategoryNo = C.CategoryNo WHERE ProductNo = '" + productID + "'";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                if (SQLConn.dr.Read() == true)
                {
                    lblProductNo.Text    = SQLConn.dr["ProductNo"].ToString();
                    txtProductCode.Text  = SQLConn.dr["ProductCode"].ToString();
                    txtDescription.Text  = SQLConn.dr["Description"].ToString();
                    txtCategory.Text     = SQLConn.dr["CategoryName"].ToString();
                    txtCategory.Tag      = SQLConn.dr["CategoryNo"];
                    txtUnitPrice.Text    = Strings.Format(SQLConn.dr["UnitPrice"], "#,##0.00");
                    txtStocksOnHand.Text = SQLConn.dr["StocksOnHand"].ToString();
                    txtReorderLevel.Text = SQLConn.dr["ReorderLevel"].ToString();
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 24
0
        private void AddItemDisplay(int itemNo)
        {
            try
            {
                SQLConn.sqL = "SELECT ProductNo, ProductCode, Description, UnitPrice, ProductNo FROM Product WHERE  ProductNo = '" + itemNo + "'";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                if (SQLConn.dr.Read() == true)
                {
                    dgw.Rows.Add(SQLConn.dr[0].ToString(), 1, SQLConn.dr[1].ToString(), SQLConn.dr[2].ToString(), Strings.FormatNumber(SQLConn.dr[3]).ToString(), '0', cmbCustomerTax.Text, Strings.FormatNumber(SQLConn.dr[3]).ToString(), "Remove");
                }

                //display amounts
                ComupteAmount();

                //dgw.SelectedRows.Clear();
                dgw.ClearSelection();
                selected_item = "";

                //disable remove button
                btnRemove.Enabled = false;
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
        }
        public void totalAmount()
        {
            try
            {
                SQLConn.sqL = "SELECT sum(TotalAmount) FROM transactions WHERE TDate BETWEEN '" + fromdate.Value.ToShortDateString() + "' AND '" + todate.Value.ToShortDateString() + "'";
                SQLConn.ConnDB();
                SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();


                if (SQLConn.dr.Read())
                {
                    //txtName = SQLConn.dr[0].ToString();
                    //txtName.Text = SQLConn.dr[1].ToString();
                    total = SQLConn.dr[0].ToString();
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 26
0
        private void LoadCustomer()
        {
            try
            {
                SQLConn.sqL = "SELECT Id, CONCAT(Lastname,', ', Firstname) customername FROM customer ORDER BY lastname";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                cmbCustomer.Items.Clear();
                Dictionary <string, string> customer = new Dictionary <string, string>();

                while (SQLConn.dr.Read() == true)
                {
                    customer.Add(SQLConn.dr[0].ToString(), SQLConn.dr[1].ToString());
                }

                cmbCustomer.DataSource    = new BindingSource(customer, null);
                cmbCustomer.DisplayMember = "Value";
                cmbCustomer.ValueMember   = "Key";

                cmbCustomer.SelectedIndex = -1;
            }
            catch (Exception)
            {
                //  Interaction.MsgBox(ex.ToString());
            }

            SQLConn.cmd.Dispose();
            SQLConn.conn.Close();
        }
Ejemplo n.º 27
0
        private void LoadReport()
        {
            try
            {
                SQLConn.sqL = "SELECT ProductCode, Description, SUM(Quantity) as Quantity, STR_TO_DATE(REPLACE(DateIn, '-', '/'), '%m/%d/%Y') as DateIn FROM Product as P, StockIn as S WHERE S.ProductNo = P.ProductNo AND STR_TO_DATE(REPLACE(DateIn, '-', '/'), '%m/%d/%Y') BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.ToString("yyyy-MM-dd") + "' GROUP BY P.ProductNo, DateIN ORDER BY DateIn, Description";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.da  = new MySqlDataAdapter(SQLConn.cmd);

                this.dsReportC.StocksIn.Clear();
                SQLConn.da.Fill(this.dsReportC.StocksIn);

                ReportParameter startDate = new ReportParameter("StartDate", StartDate.ToString());
                ReportParameter endDate   = new ReportParameter("EndDate", EndDate.ToString());
                this.reportViewer1.LocalReport.SetParameters(new ReportParameter[] { startDate, endDate });

                this.reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
                this.reportViewer1.ZoomPercent = 40;
                this.reportViewer1.ZoomMode    = Microsoft.Reporting.WinForms.ZoomMode.Percent;

                this.reportViewer1.RefreshReport();
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
        }
Ejemplo n.º 28
0
        private void getStaffInfo()
        {
            try
            {
                SQLConn.sqL = "SELECT CONCAT(lastname, ', ', Firstname, ' ', MI) as StaffName, StaffId FROM Staff WHERE StaffID = '" + SQLConn.staffid + "'";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();

                if (SQLConn.dr.Read() == true)
                {
                    txtSalesperson.Tag  = SQLConn.dr["StaffId"].ToString();
                    txtSalesperson.Text = SQLConn.dr["Staffname"].ToString();
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.Message);
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
Ejemplo n.º 29
0
        public void GetCompanyInfo()
        {
            try
            {
                SQLConn.sqL = "SELECT CompanyID, Name, Address, PhoneNo, Email, Website, TINNumber FROM Company";
                SQLConn.ConnDB();
                SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.dr  = SQLConn.cmd.ExecuteReader();


                if (SQLConn.dr.Read())
                {
                    //txtName = SQLConn.dr[0].ToString();
                    //txtName.Text = SQLConn.dr[1].ToString();
                    txtAddress = SQLConn.dr[2].ToString();
                    // txtPhoneNo = SQLConn.dr[3].ToString();
                    txtEmail   = SQLConn.dr[4].ToString();
                    txtWebsite = SQLConn.dr[5].ToString();
                    //txtTINNumber.Text = SQLConn.dr[6].ToString();
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }
        public void LoadProducts(string strSearch)
        {
            try
            {
                SQLConn.sqL = "SELECT ProductNo, ProductCOde, P.Description,  UnitPrice, StocksOnHand FROM Product as P LEFT JOIN Category C ON P.CategoryNo = C.CategoryNo WHERE P.Description LIKE  @Description OR P.ProductCOde LIKE @ProductCOde   ORDER BY Description";
                SQLConn.ConnDB();
                SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
                SQLConn.cmd.Parameters.AddWithValue("@Description", "%" + strSearch + "%");
                SQLConn.cmd.Parameters.AddWithValue("@ProductCOde", "%" + strSearch + "%");
                SQLConn.dr = SQLConn.cmd.ExecuteReader();

                dgw.Rows.Clear();
                while (SQLConn.dr.Read() == true)
                {
                    dgw.Rows.Add(SQLConn.dr[0].ToString(), SQLConn.dr[1].ToString(), SQLConn.dr[2].ToString(), Strings.Format(SQLConn.dr[3], "#,##0.00"), SQLConn.dr[4].ToString());
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.ToString());
            }
            finally
            {
                SQLConn.cmd.Dispose();
                SQLConn.conn.Close();
            }
        }