Tuesday, 2 April 2019

insert

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace POS
{
    public partial class EditBilling : Form
    {
        public EditBilling()
        {
            InitializeComponent();
        }

        private void EditBilling_Load(object sender, EventArgs e)
        {
            lbl_billno.Text = BillingGridform.stpid1.ToString();
            display();
            Billed();

            DiscountAvg();
            CellSum_total_dis();
            CellSum_dis();
            CellSum_total_dis_amount();
        }
        public void Billed() // display to fill Gridvalue
        {

            SqlConnection con = new SqlConnection(Connection.myconnection());
            con.Open();
            string sql = "select ROW_NUMBER() OVER(ORDER BY invoice_no) AS ItemNo,EmployeeID,PartNo,pname, rate,qty,qty_tot,prod_discount,prod_disc_amt,tax_per,tax_amt as tax_amt1,sub_tot from product_GetName where invoice_no ='" + lbl_billno.Text + "' and  FiscalYear='" + login.fin_year + "'";
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            // cmd.Parameters.AddWithValue("@InvoiceNo", lbl_billno.Text);
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            da.Fill(dt);

            dataGridView1.DataSource = dt;
            con.Close();

        }
        public void display() // Display to fill Textbox values
        {
            //try
            //{

            SqlConnection con = new SqlConnection(Connection.myconnection());
            con.Open();
            //,pid,pname,qty,rate,qty_tot,prod_disc_amt,prod_amt,
            //;select pid,pname,buying_Amt,qty,rate,qty_tot,prod_discount,prod_disc_amt,sub_tot from product_GetName where invoice_no = @InvoiceNo
            //   select DISTINCT Product_GetName.invoice_no,tax,tax_amt,billing_product.sub_tot,total,grand_total,convert(datetime,date,103)as 'Date',product_GetName.EmployeeID as employee,Account_Type,billing_product.cus_name,billing_product.cust_phone,cust_address,discount,discount_amt from product_GetName left join billing_product on billing_product.invoice_no = Product_GetName.invoice_no
            string sql = " select DISTINCT Product_GetName.invoice_no,billing_product.tax_amt,billing_product.sub_tot,total,grand_total,date,product_GetName.EmployeeID as employee,Account_Type,billing_product.cus_name,billing_product.cust_phone,cust_address,discount,discount_amt from product_GetName left join billing_product on billing_product.invoice_no = Product_GetName.invoice_no where billing_product.invoice_no = @invoice_no and billing_product.FiscalYear='" + login.fin_year + "'";
            // "select invoice_no,sum(sub_tot),tax,tax_amt,sum(total),discount,sum(grand_total),convert(datetime,date,103) as 'Date',employee,Account_Type from billing_product where invoice_no = @InvoiceNo  group by invoice_no,tax,tax_amt,discount,employee,Account_Type,date";

            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            cmd.Parameters.AddWithValue("@invoice_no", lbl_billno.Text);
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                Datelabel27.Text = dt.Rows[0]["date"].ToString();
                txt_sub_tot.Text = dt.Rows[0]["sub_tot"].ToString();
              //  txt_tax.Text = dt.Rows[0]["tax"].ToString();
                txt_tax_amt.Text = dt.Rows[0]["tax_amt"].ToString();
              //  txt_subtotal.Text = dt.Rows[0]["total"].ToString();
                discount_amt.Text = dt.Rows[0]["discount_amt"].ToString();
                txt_grand_total.Text = dt.Rows[0]["grand_total"].ToString();
                // dtp_Date.Text = dt.Rows[0]["date"].ToString();
                txt_staff_name.Text = dt.Rows[0]["employee"].ToString();
                txt_AccountType.Text = dt.Rows[0]["Account_Type"].ToString();
                txt_CustomerName.Text = dt.Rows[0]["cus_name"].ToString();
                txt_custphone.Text = dt.Rows[0]["cust_phone"].ToString();
                txt_custaddress.Text = dt.Rows[0]["cust_address"].ToString();
                txt_discount.Text = dt.Rows[0]["discount"].ToString();
            }
            cmd.ExecuteNonQuery();
            con.Close();

            //}
            //catch(Exception)
            //{
            //}
        }
        public void update1()
        {
            SqlConnection con = new SqlConnection(Connection.myconnection());
            con.Open();
            SqlCommand cmd = new SqlCommand("update billing_product set sub_tot=@sub_tot,tax=@tax,tax_amt=@tax_amt,total=@total,discount=@discount,discount_amt=@discount_amt,grand_total=@grand_total where invoice_no=" + lbl_billno.Text + "", con);
            cmd.Parameters.Add(new SqlParameter("@sub_tot", txt_subtotal.Text));
            cmd.Parameters.Add(new SqlParameter("@tax", txt_tax.Text));
            cmd.Parameters.Add(new SqlParameter("@tax_amt", txt_tax_amt.Text));
            cmd.Parameters.Add(new SqlParameter("@total", txt_sub_tot.Text));
            cmd.Parameters.Add(new SqlParameter("@discount", txt_discount.Text));
            cmd.Parameters.Add(new SqlParameter("@discount_amt", discount_amt.Text));
            cmd.Parameters.Add(new SqlParameter("@grand_total", txt_grand_total.Text));
            cmd.ExecuteNonQuery();
            con.Close();

        }
        public void update2()
        {
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                SqlConnection con = new SqlConnection(Connection.myconnection());
                con.Open();
                SqlCommand cmd = new SqlCommand("update product_GetName set qty=@qty,qty_tot=@qty_tot,prod_discount=@prod_discount,prod_disc_amt=@prod_disc_amt,sub_tot=@sub_tot where PartNo=@PartNo", con);
                SqlParameter Invoice = cmd.Parameters.AddWithValue("@PartNo", dataGridView1.Rows[i].Cells[3].Value);
                cmd.Parameters.AddWithValue("@qty", dataGridView1.Rows[i].Cells[6].Value);
                cmd.Parameters.AddWithValue("@qty_tot", dataGridView1.Rows[i].Cells[7].Value);
                cmd.Parameters.AddWithValue("@sub_tot", dataGridView1.Rows[i].Cells[10].Value);
                cmd.Parameters.AddWithValue("@prod_discount", dataGridView1.Rows[i].Cells[8].Value);
                cmd.Parameters.AddWithValue("@prod_disc_amt", dataGridView1.Rows[i].Cells[9].Value);
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        private void CellSum_dis()
        {
            try
            {
                decimal sum = 0;
                for (int i = 0; i < dataGridView1.Rows.Count; ++i)
                {
                    decimal d = 0;
                    decimal.TryParse(dataGridView1.Rows[i].Cells[9].Value.ToString(), out d);
                    sum += d;
                    label77.Text = sum.ToString("F");
                }

            }
            catch (Exception)
            {
            }
        }
        private void CellSum_total_dis()
        {
            try
            {
                decimal bill_discount = decimal.Parse(txt_discount.Text);
                decimal Avg_dis = decimal.Parse(lbl_DiscountAvg.Text);
                label83.Text = (bill_discount + Avg_dis).ToString("F");
                CellSum_total_dis_amount();
            }
            catch (Exception)
            {
            }
        }
        private void CellSum_total_dis_amount()
        {
            try
            {
                decimal bill_discount = decimal.Parse(label77.Text);
                decimal Avg_dis = decimal.Parse(discount_amt.Text);
                label80.Text = (bill_discount + Avg_dis).ToString("F");
            }
            catch (Exception)
            {
            }
        }
        private void DiscountAvg()
        {
            try
            {
                decimal sum = 0;
                for (int i = 0; i < dataGridView1.Rows.Count; ++i)
                {
                    sum += Convert.ToDecimal(dataGridView1.Rows[i].Cells[8].Value);
                }
                int val = dataGridView1.Rows.Count;
                lbl_DiscountAvg.Text = decimal.Round((sum / (val)), 2).ToString("F");
            }
            catch (Exception)
            { }
        }
        private void CellSum()
        {
            try
            {
                decimal sum = 0;
                for (int i = 0; i < dataGridView1.Rows.Count; ++i)
                {
                    decimal d = 0;
                    decimal.TryParse(dataGridView1.Rows[i].Cells[7].Value.ToString(), out d);
                    if (d > 0)
                    {
                        sum += d;
                    }
                    txt_subtotal.Text = sum.ToString("F");
                }
                CellSum_dis();
                CellSum_total_dis();
            }
            catch (Exception)
            {
            }

        }
        public void tax() // Display Tax in Textbox
        {
            try
            {
                decimal a, b, c;
                bool isAvalid = decimal.TryParse(txt_sub_tot.Text, out a);
                bool isBvalid = decimal.TryParse(txt_tax.Text, out b);
                if (isAvalid && isBvalid)
                {
                    c = (a * b / 100);
                    txt_tax_amt.Text = c.ToString("F");
                }
                else
                    txt_tax_amt.Text = "0";
            }

            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
        public void discount1() // Display Grand total in textbox
        {
            decimal a;
            decimal b;
            decimal c;
            bool isAValid = decimal.TryParse(txt_discount.Text, out b);
            bool isBValid = decimal.TryParse(txt_subtotal.Text, out a);
            if (isAValid && isBValid)
            {
                c = a * b / 100;
                discount_amt.Text = c.ToString("F");
                txt_sub_tot.Text = decimal.Round((a - c), 2).ToString("F");
            }
            else
                txt_sub_tot.Text = " ";
            CellSum_dis();
            CellSum_total_dis();
        }
        public void Add() // Display SubTotal in Textbox
        {
            decimal a, b;
            bool isAValid = decimal.TryParse(txt_tax_amt.Text, out a);
            bool isBValid = decimal.TryParse(txt_sub_tot.Text, out b);

            if (isAValid && isBValid)
                txt_grand_total.Text = (Math.Round((a + b), 0)).ToString("F");
            else
                txt_grand_total.Text = "";
        }

        private void totalcal() // Calculate Cost
        {
            try
            {
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    int a = Convert.ToInt32(row.Cells[5].Value);
                    //int a = int.Parse(row.Cells[3].Value);
                    decimal b = Convert.ToDecimal(row.Cells[6].Value);
                    decimal c = a * b;
                    row.Cells[7].Value = decimal.Round(c, 2).ToString("F");
                }
            }
            catch (Exception)
            {
            }
        }

        private void qtySum()
        {
            try
            {
                decimal sum = 0;
                for (int i = 0; i < dataGridView1.Rows.Count; ++i)
                {
                    decimal d = 0;
                    decimal.TryParse(dataGridView1.Rows[i].Cells["qty"].Value.ToString(), out d);
                    sum += d;
                    label42.Text = sum.ToString("F");
                }
            }
            catch (Exception)
            {
            }
        }

        private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
        {
            int net = 15;
            // store_details();
            e.Graphics.DrawString("Design World", new Font("Arial", 12, FontStyle.Bold), Brushes.Black, new Point(80, 20 + net));
            e.Graphics.DrawString("No.4/6, Bharani Flats, 48th Street,", new Font("Arial", 7), Brushes.Black, new Point(60, 40 + net));
            e.Graphics.DrawString("Nanganallur, CHENNAI-600061", new Font("Arial", 7), Brushes.Black, new Point(65, 52 + net));
            e.Graphics.DrawString("Ph : 044-22241272", new Font("Arial", 7), Brushes.Black, new Point(95, 65 + net));

            //e.Graphics.DrawString("No.2/590, Medavakkam, Main Road", new Font("Arial", 7), Brushes.Black, new Point(60, 40));
            //e.Graphics.DrawString("Kovilambakkam, CHENNAI-6000117", new Font("Arial", 7), Brushes.Black, new Point(65, 52));
            //e.Graphics.DrawString("Ph : 044-22683100", new Font("Arial", 7), Brushes.Black, new Point(95, 65));

            e.Graphics.DrawString("CASH BILL", new Font("Arial", 12, FontStyle.Bold), Brushes.Black, new Point(93, 80 + net));
            e.Graphics.DrawString("BILL NO:" + lbl_billno.Text, new Font("Arial", 8), Brushes.Black, new Point(10, 105 + net));
            e.Graphics.DrawString("GSTIN : 33AAJFD9370K1Z3", new Font("Arial", 8), Brushes.Black, new Point(10, 5));
            e.Graphics.DrawString("DT: " + DateTime.Now.ToString("dd/MM/yyyy"), new Font("Arial", 8), Brushes.Black, new Point(195, 105 + net));
            e.Graphics.DrawString("", new Font("Arial", 8), Brushes.Black, new Point(195, 5));
            e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(10, 115 + net));
            //  e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(25, 254));

            e.Graphics.DrawString("EMP ", new Font("Arial", 8), Brushes.Black, new Point(10, 128 + net)); //10
            e.Graphics.DrawString("T.No ", new Font("Arial", 8), Brushes.Black, new Point(45, 128 + net)); //40
            e.Graphics.DrawString("QTY", new Font("Arial", 8), Brushes.Black, new Point(98, 128 + net)); //73
            e.Graphics.DrawString("RATE", new Font("Arial", 8), Brushes.Black, new Point(140, 128 + net));//130
            e.Graphics.DrawString("DISC ", new Font("Arial", 8), Brushes.Black, new Point(189, 128 + net)); //170
            e.Graphics.DrawString("AMT", new Font("Arial", 8), Brushes.Black, new Point(230, 128 + net));
            // e.Graphics.DrawString("User Rendered", new Font("Arial", 7), Brushes.Black, new Point(750, 280));
            //e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(5, 140));
            int yPos = 145 + net;
            StringFormat stringFormat = new StringFormat();
            stringFormat.Alignment = StringAlignment.Far;

            for (int i = 0; i < dataGridView1.RowCount; i++)
            {
                try
                {
                    e.Graphics.DrawString(dataGridView1.Rows[i].Cells[1].Value.ToString(), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(13, yPos));
                    e.Graphics.DrawString(Convert.ToString(dataGridView1.Rows[i].Cells[3].Value.ToString()), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(70, yPos));
                    //e.Graphics.DrawString(Convert.ToString(dataGridView1.Rows[i].Cells[3].Value), new Font("Arial", 7, FontStyle.Regular), Brushes.Black, new Point(150, yPos));
                    e.Graphics.DrawString("(" + Convert.ToString(dataGridView1.Rows[i].Cells[2].Value + ")"), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(43, yPos));
                    e.Graphics.DrawString(dataGridView1.Rows[i].Cells[6].Value.ToString(), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(135, yPos));
                    e.Graphics.DrawString(Convert.ToString(dataGridView1.Rows[i].Cells[5].Value), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(207, yPos), stringFormat);
                    e.Graphics.DrawString(dataGridView1.Rows[i].Cells[10].Value.ToString(), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(270, yPos), stringFormat);
                    //i = i = i + 1;
                    yPos += 16;
                }
                catch (Exception ex)
                { MessageBox.Show(ex.Message); }
            }


            e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(10, yPos + 5));
            e.Graphics.DrawString("Sub Total : ", new Font("Arial", 8), Brushes.Black, new Point(10, yPos + 17));
            e.Graphics.DrawString(label42.Text, new Font("Arial", 8), Brushes.Black, new Point(135, yPos + 17));
            e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(10, yPos + 27));
            //e.Graphics.DrawString("Tax %         : ", new Font("Arial", 7), Brushes.Black, new Point(160, yPos + 30));
            //e.Graphics.DrawString("Tax Amount :", new Font("Arial", 7), Brushes.Black, new Point(160, yPos + 45));
            e.Graphics.DrawString("Discount " + label83.Text + " %  ", new Font("Arial", 8), Brushes.Black, new Point(135, yPos + 41));


            e.Graphics.DrawString("GST (Amt) ", new Font("Arial", 8), Brushes.Black, new Point(135, yPos + 41 + 20));
            e.Graphics.DrawString(Math.Round(float.Parse(txt_tax_amt.Text), 2).ToString("F"), new Font("Arial", 8), Brushes.Black, new Point(270, yPos + 41 + 20), stringFormat);


            e.Graphics.DrawString("Net Total  ", new Font("Arial", 8), Brushes.Black, new Point(10, yPos + 61+18));
            //   e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(25, yPos + 30));
            e.Graphics.DrawString(txt_subtotal.Text, new Font("Arial", 8), Brushes.Black, new Point(270, yPos + 17), stringFormat);
            // e.Graphics.DrawString(txt_tax.Text, new Font("Arial", 7), Brushes.Black, new Point(210, yPos + 75));
            // e.Graphics.DrawString(txt_tax_amt.Text, new Font("Arial", 7), Brushes.Black, new Point(210, yPos + 75));
            e.Graphics.DrawString("- " + label80.Text, new Font("Arial", 8), Brushes.Black, new Point(270, yPos + 41), stringFormat);
            e.Graphics.DrawString(txt_grand_total.Text, new Font("Arial", 10), Brushes.Black, new Point(270, yPos + 61 + 18), stringFormat);
            e.Graphics.DrawString("Amount : " + txt_AccountType.Text, new Font("Arial", 8), Brushes.Black, new Point(10, yPos + 41));
            // e.Graphics.DrawString("User Rendered :" + textBox5.Text, new Font("Arial", 8), Brushes.Black, new Point(500, yPos + 180));
            // e.Graphics.DrawString("Balance :" + textBox6.Text, new Font("Arial", 8), Brushes.Black, new Point(500, yPos + 212));

            e.Graphics.DrawString("THANK YOU!  VISIT AGAIN ! ", new Font("Arial", 9, FontStyle.Bold), Brushes.Black, new Point(65, yPos + 100 + 20));
            e.Graphics.DrawString("EXCHANGE WITHIN A WEEK", new Font("Arial", 8), Brushes.Black, new Point(65, yPos + 130 + 12));
            e.Graphics.DrawString("IMITATION NO GUARANTEE! & NO EXCHANGE !", new Font("Arial", 8), Brushes.Black, new Point(12, yPos + 145 + 12));
            e.Graphics.DrawString("NO COLOUR GUARANTEE!", new Font("Arial", 8), Brushes.Black, new Point(77, yPos + 160 + 12));

        }

        private void btn_print_Click(object sender, EventArgs e)
        {
            //try
            //{
                qtySum();
            CellSum();
                printDialog1.Document = printDocument2;
               // printPreviewDialog.ShowDialog();
                printDocument3.Print();
            //}
            //catch (Exception)
            //{
            //}
        }

        private void mt_home_Click(object sender, EventArgs e)
        {

            this.Hide();
            Form1 f = new Form1();
            f.Show();
        }

        private void mbtn_Close_Click(object sender, EventArgs e)
        {
            BillingGridform f2 = new BillingGridform();
            f2.MdiParent = this.ParentForm;
            f2.Show();
        }

        private void printDocument2_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
        {
       
        }

        private void printDocument3_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
        {
            int net = 15;
            // store_details();
            e.Graphics.DrawString("Design World", new Font("Arial", 12, FontStyle.Bold), Brushes.Black, new Point(80, 20 + net));
            e.Graphics.DrawString("No.4/6, Bharani Flats, 48th Street,", new Font("Arial", 7), Brushes.Black, new Point(60, 40 + net));
            e.Graphics.DrawString("Nanganallur, CHENNAI-600061", new Font("Arial", 7), Brushes.Black, new Point(65, 52 + net));
            e.Graphics.DrawString("Ph : 044-22241272", new Font("Arial", 7), Brushes.Black, new Point(95, 65 + net));

            //e.Graphics.DrawString("No.2/590, Medavakkam, Main Road", new Font("Arial", 7), Brushes.Black, new Point(60, 40));
            //e.Graphics.DrawString("Kovilambakkam, CHENNAI-6000117", new Font("Arial", 7), Brushes.Black, new Point(65, 52));
            //e.Graphics.DrawString("Ph : 044-22683100", new Font("Arial", 7), Brushes.Black, new Point(95, 65));

            e.Graphics.DrawString("CASH BILL", new Font("Arial", 12, FontStyle.Bold), Brushes.Black, new Point(93, 80 + net));
            e.Graphics.DrawString("BILL NO:" + lbl_billno.Text, new Font("Arial", 8), Brushes.Black, new Point(10, 105 + net));
            e.Graphics.DrawString("GSTIN : 33AAJFD9370K1Z3", new Font("Arial", 8), Brushes.Black, new Point(10, 5));
            e.Graphics.DrawString("DT: " + DateTime.Now.ToString("dd/MM/yyyy"), new Font("Arial", 8), Brushes.Black, new Point(195, 105 + net));
            e.Graphics.DrawString("", new Font("Arial", 8), Brushes.Black, new Point(195, 5));
            e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(10, 115 + net));
            //  e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(25, 254));

            e.Graphics.DrawString("EMP ", new Font("Arial", 8), Brushes.Black, new Point(10, 128 + net)); //10
            e.Graphics.DrawString("T.No ", new Font("Arial", 8), Brushes.Black, new Point(45, 128 + net)); //40
            e.Graphics.DrawString("QTY", new Font("Arial", 8), Brushes.Black, new Point(98, 128 + net)); //73
            e.Graphics.DrawString("RATE", new Font("Arial", 8), Brushes.Black, new Point(140, 128 + net));//130
            e.Graphics.DrawString("DISC ", new Font("Arial", 8), Brushes.Black, new Point(189, 128 + net)); //170
            e.Graphics.DrawString("AMT", new Font("Arial", 8), Brushes.Black, new Point(230, 128 + net));
            // e.Graphics.DrawString("User Rendered", new Font("Arial", 7), Brushes.Black, new Point(750, 280));
            //e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(5, 140));
            int yPos = 145 + net;
            StringFormat stringFormat = new StringFormat();
            stringFormat.Alignment = StringAlignment.Far;

            for (int i = 0; i < dataGridView1.RowCount ; i++)
            {
                try
                {
                    e.Graphics.DrawString(dataGridView1.Rows[i].Cells[1].Value.ToString(), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(13, yPos));
                    e.Graphics.DrawString(Convert.ToString(dataGridView1.Rows[i].Cells[3].Value.ToString()), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(70, yPos));
                    //e.Graphics.DrawString(Convert.ToString(dataGridView1.Rows[i].Cells[3].Value), new Font("Arial", 7, FontStyle.Regular), Brushes.Black, new Point(150, yPos));
                    e.Graphics.DrawString("(" + Convert.ToString(dataGridView1.Rows[i].Cells[2].Value + ")"), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(43, yPos));
                    e.Graphics.DrawString(dataGridView1.Rows[i].Cells[6].Value.ToString(), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(135, yPos));
                    e.Graphics.DrawString(Convert.ToString(dataGridView1.Rows[i].Cells[5].Value), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(207, yPos), stringFormat);
                    e.Graphics.DrawString(dataGridView1.Rows[i].Cells[7].Value.ToString(), new Font("Arial", 8, FontStyle.Regular), Brushes.Black, new Point(270, yPos), stringFormat);
                    //i = i = i + 1;
                    yPos += 16;
                }
                catch (Exception ex)
                {  }
            }


            e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(10, yPos + 5));
            e.Graphics.DrawString("Sub Total : ", new Font("Arial", 8), Brushes.Black, new Point(10, yPos + 17));
            e.Graphics.DrawString(label42.Text, new Font("Arial", 8), Brushes.Black, new Point(135, yPos + 17));
            e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(10, yPos + 27));
            //e.Graphics.DrawString("Tax %         : ", new Font("Arial", 7), Brushes.Black, new Point(160, yPos + 30));
            //e.Graphics.DrawString("Tax Amount :", new Font("Arial", 7), Brushes.Black, new Point(160, yPos + 45));
            e.Graphics.DrawString("Disc (-) " + label83.Text + " %  ", new Font("Arial", 8), Brushes.Black, new Point(135, yPos + 41));

            e.Graphics.DrawString("CGST " + Math.Round(((Math.Round(float.Parse(txt_tax_amt.Text) / 2, 2) / (float.Parse(txt_subtotal.Text) - float.Parse(label80.Text)) * 100)), 2) + " %", new Font("Arial", 8), Brushes.Black, new Point(135, yPos + 41 + 20));
            e.Graphics.DrawString(Math.Round(float.Parse(txt_tax_amt.Text)/2, 2).ToString("F"), new Font("Arial", 8), Brushes.Black, new Point(270, yPos + 41 + 20), stringFormat);

            e.Graphics.DrawString("SGST " + Math.Round(((Math.Round(float.Parse(txt_tax_amt.Text) / 2, 2) / (float.Parse(txt_subtotal.Text) - float.Parse(label80.Text)) * 100)), 2) + " %", new Font("Arial", 8), Brushes.Black, new Point(135, yPos + 41 + 20 + 17));
            e.Graphics.DrawString( Math.Round(float.Parse(txt_tax_amt.Text)/2, 2).ToString("F"), new Font("Arial", 8), Brushes.Black, new Point(270, yPos + 41 + 20 + 17), stringFormat);

            e.Graphics.DrawString("Net Total  ", new Font("Arial", 8), Brushes.Black, new Point(10, yPos + 61 + 18 + 17));
            //   e.Graphics.DrawString(Dashlabel.Text, new Font("Arial", 7), Brushes.Black, new Point(25, yPos + 30));
            e.Graphics.DrawString(txt_subtotal.Text, new Font("Arial", 8), Brushes.Black, new Point(270, yPos + 17), stringFormat);
            // e.Graphics.DrawString(txt_tax.Text, new Font("Arial", 7), Brushes.Black, new Point(210, yPos + 75));
            // e.Graphics.DrawString(txt_tax_amt.Text, new Font("Arial", 7), Brushes.Black, new Point(210, yPos + 75));
            e.Graphics.DrawString(label80.Text, new Font("Arial", 8), Brushes.Black, new Point(270, yPos + 41 ), stringFormat);
            e.Graphics.DrawString(txt_grand_total.Text, new Font("Arial", 10), Brushes.Black, new Point(270, yPos + 61 + 18 + 17), stringFormat);
            e.Graphics.DrawString("Amount : " + txt_AccountType.Text, new Font("Arial", 8), Brushes.Black, new Point(10, yPos + 41 + 17));
            // e.Graphics.DrawString("User Rendered :" + textBox5.Text, new Font("Arial", 8), Brushes.Black, new Point(500, yPos + 180));
            // e.Graphics.DrawString("Balance :" + textBox6.Text, new Font("Arial", 8), Brushes.Black, new Point(500, yPos + 212));
            e.Graphics.DrawString("THANK YOU!  VISIT AGAIN ! ", new Font("Arial", 9, FontStyle.Bold), Brushes.Black, new Point(65, yPos + 100 + 20 + 17));
            e.Graphics.DrawString("EXCHANGE WITHIN A WEEK", new Font("Arial", 8), Brushes.Black, new Point(65, yPos + 130 + 12 + 17));
            e.Graphics.DrawString("IMITATION NO GUARANTEE! & NO EXCHANGE !", new Font("Arial", 8), Brushes.Black, new Point(12, yPos + 145 + 12 + 17));
            e.Graphics.DrawString("NO COLOUR GUARANTEE!", new Font("Arial", 8), Brushes.Black, new Point(77, yPos + 160 + 12 + 17));
        }
    }
}


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace POS
{
    class Connection
    {
        public static string myconnection()
        {
        string connstr = @"Data Source=(local);Initial Catalog=billing;User Id=sa;Password=;";
        //   string connstr = @"Data Source=DESKTOP-RJ8CUCP\SQLEXPRESS01;Initial Catalog=billing;Integrated Security=True";
          //string connstr = @"Data Source=USER-PC\SQLEXPRESS;Initial Catalog=billing74;Integrated Security=True";

          //  string connstr = @"Data Source = DESIGNWORLD-PC; Initial Catalog = billing_new; Integrated Security = True";
         //  string connstr = @"Data Source=192.168.1.5;Initial Catalog=billing_20_04;Integrated Security=True;";
           //  string connstr = @"Data Source=DESIGNWORLD-PC;Initial Catalog=billing;User Id=sa;Password=;Max Pool Size=1000";
            return connstr;
        }
    }
}


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.Reporting.WinForms;

namespace POS
{
    public partial class commisson : Form
    {
        public commisson()
        {
            InitializeComponent();
        }

        private void commisson_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'billing1DataSet.Staff_Details' table. You can move, or remove it, as needed.
            this.staff_DetailsTableAdapter.Fill(this.billing1DataSet.Staff_Details);
            dataGridView1.AutoGenerateColumns = false;
            dateTimePicker1.Format = DateTimePickerFormat.Custom;
            dateTimePicker1.CustomFormat = "dd-MM-yyyy";
            dateTimePicker2.Format = DateTimePickerFormat.Custom;
            dateTimePicker2.CustomFormat = "dd-MM-yyyy";

            comboBox1.SelectedIndex = -1;
            DateTime now = DateTime.Now;
            var startDate = new DateTime(now.Year, now.Month, 1);
            var endDate = startDate.AddMonths(1).AddDays(-1);

            dateTimePicker1.Text = startDate.ToString();
            dateTimePicker2.Text = startDate.AddMonths(1).AddDays(-1).ToString();
          
                GetData();
         
            this.WindowState = FormWindowState.Maximized;

        }
        private void GetData()
        {

            using (SqlConnection con = new SqlConnection(Connection.myconnection()))
            {
                string x = "";

                string sql = "  SELECT Staff_Details.Name, View_Billing_Report.EmployeeID, round(convert(float,commission),2) as commission,round(selling_Amount * (convert(float,commission)/100),2) as commission_Amount,  InvoiceNo, date, qty, buyingAmt, margin_amt, margin, selling_Amount, Item_Discount_per, Item_Discount_Amount, Subtotal, Bill_disc_per, tax_per, tax_amt, grand_total  FROM dbo.View_Billing_Report left join Staff_Details on View_Billing_Report.EmployeeID = Staff_Details.EmployeeID ";

                // string sql = "select invoice_no,billing_product.date,sell_amt,prod_discount,billing_product.prod_disc_amt,sub_tot,tax,tax_amt as 'TaxAmount',grand_total from billing_product left join product on billing_product.pid = product.pid ";

                if (comboBox1.Text != "")
                {
                    if (x == "")
                    {
                        x += " where  View_Billing_Report.EmployeeID = '" + comboBox1.SelectedValue + "' ";
                    }
                    else
                    {
                        x += " and  View_Billing_Report.EmployeeID = '" + comboBox1.SelectedValue + "' ";
                    }
                }
                if (dateTimePicker1.Text != "" || dateTimePicker2.Text != "")
                {

                    if (x == "")
                    {
                        if (dateTimePicker1.Text != "" || dateTimePicker2.Text != "")
                        {


                            if (dateTimePicker1.Text != "" && dateTimePicker2.Text == "")
                            {
                                x = x + " where convert(DateTime,date,103) >= convert(DateTime,'" + dateTimePicker1.Text + "',103) ";

                            }
                            else if (dateTimePicker1.Text == "" && dateTimePicker2.Text != "")
                            {
                                x = x + " where convert(DateTime,date,103) <=convert(DateTime,'" + dateTimePicker2.Text + "',103) ";

                            }
                            else
                            {
                                x = x + " where convert(DateTime,date,103)  BETWEEN convert(DateTime,'" + dateTimePicker1.Text + "',103) AND convert(DateTime,'" + dateTimePicker2.Text + "',103) ";

                            }


                        }
                    }
                    else
                    {
                        if (dateTimePicker1.Text != "" || dateTimePicker2.Text != "")
                        {


                            if (dateTimePicker1.Text != "" && dateTimePicker2.Text == "")
                            {
                                x = x + " AND convert(DateTime,date,103) >= convert(DateTime,'" + dateTimePicker1.Text + "',103) ";

                            }
                            else if (dateTimePicker1.Text == "" && dateTimePicker2.Text != "")
                            {
                                x = x + " AND convert(DateTime,date,103) <=convert(DateTime,'" + dateTimePicker2.Text + "',103) ";

                            }
                            else
                            {
                                x = x + " AND convert(DateTime,date,103)  BETWEEN convert(DateTime,'" + dateTimePicker1.Text + "',103) AND convert(DateTime,'" + dateTimePicker2.Text + "',103) ";

                            }
                        }

                    }
                }
                string finalsql = sql + x.ToString();
             
                using (SqlCommand cmd = new SqlCommand(finalsql, con))
                {

                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            dataGridView1.DataSource = dt;
                            // Billed();
                            //  dataGridView1.DataSource = dt;
                        }
                    }
                }
            }
        }

        private void mbtn_Search_Click(object sender, EventArgs e)
        {
            GetData();
      
        }
        public void SerialNo() // Auto SerialNo
        {
            int i = 1;
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                row.Cells["Serial_no"].Value = i;
                i++;
            }
        }
        private void CellSum() // Display Total value in label
        {


            int sum = 0;
            for (int i = 0; i < dataGridView1.Rows.Count; ++i)
            {
                try
                {
                    sum += Convert.ToInt32(dataGridView1.Rows[i].Cells[4].Value);
                }
                catch (Exception)
                {
                }
            }
            label4.Text = sum.ToString();


      
        }

        private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            SerialNo();
            CellSum();
        }

        private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            this.dataGridView1.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            this.dataGridView1.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            this.dataGridView1.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            this.dataGridView1.Columns[6].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            this.dataGridView1.Columns[7].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            this.dataGridView1.Columns[8].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            this.dataGridView1.Columns[9].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            this.dataGridView1.Columns[10].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            this.dataGridView1.Columns[11].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            this.dataGridView1.Columns[12].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            this.dataGridView1.Columns[13].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            this.dataGridView1.Columns[14].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            this.dataGridView1.Columns[15].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

        }
    }
}


Posted by at No comments:
Subscribe to: Posts (Atom)