private void btnsubmit() { try { if (cmbcustname.Text == "") { MessageBox.Show("Select Party Name"); cmbcustname.Focus(); } else if (cmbsaletype.Text == "") { MessageBox.Show("Select Sale type"); cmbsaletype.Focus(); } else if (LVFO.Items.Count == 0) { MessageBox.Show("Please Enter atleast one item to generate sale"); txtitemname.Focus(); } else { DataTable dtpid = new DataTable(); if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); DialogResult dr = MessageBox.Show("Do you want to Save?", "Bill", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dr == DialogResult.Yes) { this.Enabled = false; if (BtnPayment.Text == "Update") { SqlCommand cmd2 = new SqlCommand("Update billproductmaster set isactive='0' where billno='" + TxtBillNo.Text + "' and BillType='P'", con); cmd2.ExecuteNonQuery(); for (int i = 0; i < LVFO.Items.Count; i++) { dtpid = conn.getdataset("Select Productid from productmaster where product_name='" + LVFO.Items[i].SubItems[2].Text.Replace(",", "") + "'"); SqlCommand cmd1 = new SqlCommand("INSERT INTO [dbo].[BillProductMaster]([BillNo],[Bill_Run_Date],[Productname],[Packing],[Bags],[MRP],[Pqty],[Aqty],[Rate],[Per],[Total],[Tax],[addtax],[Amount],[isactive],[qty],[BillType],[Bill_no],[productid])VALUES('" + TxtBillNo.Text + "','" + Convert.ToDateTime(TxtRundate.Text).ToString("dd/MMM/yyyy") + "','" + LVFO.Items[i].SubItems[2].Text + "','" + LVFO.Items[i].SubItems[1].Text + "','" + LVFO.Items[i].SubItems[0].Text + "','','" + LVFO.Items[i].SubItems[4].Text + "','','" + LVFO.Items[i].SubItems[5].Text + "','" + LVFO.Items[i].SubItems[6].Text + "','" + LVFO.Items[i].SubItems[7].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[8].Text + "','" + LVFO.Items[i].SubItems[9].Text + "','" + LVFO.Items[i].SubItems[10].Text.Replace(",", "") + "',1,'" + LVFO.Items[i].SubItems[3].Text + "','P','0','" + dtpid.Rows[0][0].ToString() + "')", con); cmd1.ExecuteNonQuery(); } SqlCommand cmd = new SqlCommand("UPDATE [dbo].[BillMaster]SET [BillNo] = '" + TxtBillNo.Text + "',[Bill_Date] = '" + Convert.ToDateTime(TxtRundate.Text).ToString("dd/MMM/yyyy") + "',[Terms] = '" + cmbterms.Text + "',[ClientID] = '" + cmbcustname.SelectedValue + "',[PO_No] = '" + txtpono.Text + "',[SaleType] = '" + cmbsaletype.SelectedValue + "',[count] = " + lbltotcount.Text + ",[totalqty] = " + lbltotpqty.Text + ",[totalbasic] = " + lblbasictot.Text + ",[totaltax] =" + lbltaxtot.Text.Replace(",", "") + " ,[totalnet] = " + Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00") + ",[isactive]=1, [apprweight]='" + txtweight.Text + "',[dispatchdetails]='" + txtdispatch.Text + "',[totaladdtax]='" + txttotaddvat.Text + "',[remarks]='" + txtremarks.Text + "',[billtype]='P',[roudoff]='" + txtroundoff.Text + "' where BillNo='" + TxtBillNo.Text + "' and [billtype]='P' and [CompanyId]=" + Master.companyId + "", con); cmd.ExecuteNonQuery(); string vno = conn.ExecuteScalar("select voucherid from ledger where [VoucherID]= '" + TxtBillNo.Text + "' and [TranType] = 'Purchase'"); if (vno != "0") { conn.execute("UPDATE [dbo].[Ledger] SET [Date1]='" + Convert.ToDateTime(TxtRundate.Text).ToString("dd/MMM/yyyy") + "',[TranType] = 'Purchase',[AccountID] = '" + cmbcustname.SelectedValue + "',[AccountName]='" + cmbcustname.Text + "' ,[Amount] = '" + Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00") + "',[DC] = 'C' where [VoucherID]= '" + TxtBillNo.Text + "' and [TranType] = 'Purchase'"); } else { conn.execute("INSERT INTO [dbo].[Ledger]([VoucherID],[Date1],[TranType],[AccountID],[AccountName],[Amount],[DC],[isactive],[CompanyId]) values ('" + TxtBillNo.Text + "','" + Convert.ToDateTime(TxtRundate.Text).ToString("dd/MMM/yyyy") + "','Purchase','" + cmbcustname.SelectedValue + "','" + cmbcustname.Text + "','" + Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00") + "','C',1," + Master.companyId + ")"); } ChangeNumbersToWords sh = new ChangeNumbersToWords(); String s1 = Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00"); string[] words = s1.Split('.'); string str = sh.changeToWords(words[0]); string str1 = sh.changeToWords(words[1]); if (str1 == " " || str1 == null || words[1] == "00") { str1 = "Zero "; } String inword = "In words: " + str + "and " + str1 + "Paise Only"; // Phrase inwrd = new Phrase(inword, _font2); clearitem(); clearall(); LVFO.Items.Clear(); MessageBox.Show("Data Updated Successfully."); BtnPayment.Text = "Save"; this.Hide(); } else { // getsr(); for (int i = 0; i < LVFO.Items.Count; i++) { dtpid = conn.getdataset("Select Productid from productmaster where product_name='" + LVFO.Items[i].SubItems[2].Text.Replace(",", "") + "'"); SqlCommand cmd1 = new SqlCommand("INSERT INTO [dbo].[BillProductMaster]([BillNo],[Bill_Run_Date],[Productname],[Packing],[Bags],[MRP],[Pqty],[Aqty],[Rate],[Per],[Total],[Tax],[addtax],[Amount],[isactive],[qty],[BillType],[Bill_no],[productid])VALUES('" + TxtBillNo.Text + "','" + Convert.ToDateTime(TxtRundate.Text).ToString("dd/MMM/yyyy") + "','" + LVFO.Items[i].SubItems[2].Text + "','" + LVFO.Items[i].SubItems[1].Text + "','" + LVFO.Items[i].SubItems[0].Text + "','','" + LVFO.Items[i].SubItems[4].Text + "','','" + LVFO.Items[i].SubItems[5].Text + "','" + LVFO.Items[i].SubItems[6].Text + "','" + LVFO.Items[i].SubItems[7].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[8].Text + "','" + LVFO.Items[i].SubItems[9].Text + "','" + LVFO.Items[i].SubItems[10].Text.Replace(",", "") + "',1,'" + LVFO.Items[i].SubItems[3].Text + "','P','0','" + dtpid.Rows[0][0].ToString() + "')", con); cmd1.ExecuteNonQuery(); } SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[BillMaster]([BillNo],[Bill_Date],[Terms],[ClientID],[PO_No],[SaleType] ,[count],[totalqty],[totalbasic],[totaltax],[totalnet],[isactive],[apprweight],[dispatchdetails],[remarks],[billtype],[Bill_no],[totaladdtax],[roudoff],[CompanyId])VALUES('" + TxtBillNo.Text + "','" + Convert.ToDateTime(TxtRundate.Text).ToString("dd/MMM/yyyy") + "','" + cmbterms.Text + "','" + cmbcustname.SelectedValue + "','" + txtpono.Text + "','" + cmbsaletype.SelectedValue + "'," + Math.Round(Convert.ToDouble(lbltotcount.Text), 2) + "," + Math.Round(Convert.ToDouble(lbltotpqty.Text), 2) + "," + Math.Round(Convert.ToDouble(lblbasictot.Text), 2) + "," + Math.Round(Convert.ToDouble(lbltaxtot.Text), 2) + "," + Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00") + ",1,'" + txtweight.Text + "','" + txtdispatch.Text + "','" + txtremarks.Text + "','P','0','" + Math.Round(Convert.ToDouble(txttotaddvat.Text), 2) + "','" + Math.Round(Convert.ToDouble(txtroundoff.Text), 2) + "'," + Master.companyId + ")", con); cmd.ExecuteNonQuery(); conn.execute("INSERT INTO [dbo].[Ledger]([VoucherID],[Date1],[TranType],[AccountID],[AccountName],[Amount],[DC],[isactive],[CompanyId]) values ('" + TxtBillNo.Text + "','" + Convert.ToDateTime(TxtRundate.Text).ToString("dd/MMM/yyyy") + "','Purchase','" + cmbcustname.SelectedValue + "','" + cmbcustname.Text + "','" + Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00") + "','C',1," + Master.companyId + ")"); clearitem(); clearall(); LVFO.Items.Clear(); MessageBox.Show("Data Inserted Successfully."); this.Hide(); } } else { MessageBox.Show("please fill all information"); } } } catch (Exception ex) { MessageBox.Show("Error:" + ex.Message); } finally { this.Enabled = true; con.Close(); } }
private void btnsubmit() { try { //if (cmbCustName.Text == "") //{ // MessageBox.Show("Select Party Name"); // cmbCustName.Focus(); //} //else if (cmbPurchaseType.Text == "") //{ // MessageBox.Show("Select Sale type"); // cmbPurchaseType.Focus(); //} //else if (LVFO.Items.Count == 0) //{ // MessageBox.Show("Please Enter atleast one item to generate sale return"); // txtItemName.Focus(); //} //else //{ DataTable dtpid = new DataTable(); getcon(); if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); DialogResult dr = MessageBox.Show("Do you want to Generate Bill?", "Bill", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dr == DialogResult.Yes) { if (cmbPurchaseType.Text == "" || cmbCustName.Text == "" || cmbTerms.Text == "") { MessageBox.Show("Please fill all the information."); if (cmbTerms.Text == "") { cmbTerms.Focus(); } else if (cmbCustName.Text == "") { cmbCustName.Focus(); } else if (cmbPurchaseType.Text == "") { cmbPurchaseType.Focus(); } } else { if (btnPayment.Text == "Update") { SqlCommand cmd2 = new SqlCommand("Update billproductmaster set isactive='0' where Bill_No='" + txtVchNo.Text + "' and BillType='PR'", con); cmd2.ExecuteNonQuery(); for (int i = 0; i < LVFO.Items.Count; i++) { dtpid = conn.getdataset("Select Productid from productmaster where product_name='" + LVFO.Items[i].SubItems[0].Text.Replace(",", "") + "'"); SqlCommand cmd1 = new SqlCommand("INSERT INTO [BillProductMaster]([Bill_No],[Bill_Run_Date],[ProductName],[Pqty],[Bags],[Rate],[Per],[Amount],[DiscountPer],[Discountamt],[Tax],[Addtax],[Total],[isactive],[BillType],[productid])VALUES('" + txtVchNo.Text + "','" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "','" + LVFO.Items[i].SubItems[0].Text + "','" + LVFO.Items[i].SubItems[1].Text + "','" + LVFO.Items[i].SubItems[2].Text + "','" + LVFO.Items[i].SubItems[3].Text + "','" + LVFO.Items[i].SubItems[4].Text + "','" + LVFO.Items[i].SubItems[5].Text + "','" + LVFO.Items[i].SubItems[6].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[7].Text + "','" + LVFO.Items[i].SubItems[8].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[9].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[10].Text.Replace(",", "") + "',1,'PR','" + dtpid.Rows[0][0].ToString() + "')", con); cmd1.ExecuteNonQuery(); } SqlCommand cmd = new SqlCommand("UPDATE [BillMaster] SET [Bill_No] = '" + txtVchNo.Text + "',[Bill_Date] = '" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "',[Terms] = '" + cmbTerms.Text + "',[ClientID] = '" + cmbCustName.SelectedValue + "',[SaleType] = '" + cmbPurchaseType.SelectedValue + "',[Count] = " + lbltotcount.Text + ",[TotalQty] = " + lbltotpqty.Text + ",[TotalBasic] = " + lblbasictot.Text + ",[TotalTax] =" + lbltaxtot.Text + " ,[TotalAddTax] =" + txtaddtax.Text + " ,[TotalDiscount] =" + lbltotaldiscount.Text + " ,[TotalNet] = " + Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00") + ",[isactive]=1,[DispatchDetails]='" + txtdispatch.Text + "',[Remarks]='" + txtremarks.Text + "',[billtype]='PR',[CompanyId]=" + Master.companyId + " where Bill_No='" + txtVchNo.Text + "' and [billtype]='PR' and [CompanyId]=" + Master.companyId + "", con); cmd.ExecuteNonQuery(); conn.execute("UPDATE [Ledger] SET [Date1]='" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "',[TranType] = 'PurchaseReturn',[AccountID] = '" + cmbCustName.SelectedValue + "',[AccountName]='" + cmbCustName.Text + "' ,[Amount] = '" + Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00") + "',[DC] = 'D',[CompanyId]=" + Master.companyId + " where [VoucherID]= '" + txtVchNo.Text + "' and [TranType] = 'PurchaseReturn' and CompanyId=" + Master.companyId + ""); ChangeNumbersToWords sh = new ChangeNumbersToWords(); String s1 = Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00"); string[] words = s1.Split('.'); string str = sh.changeToWords(words[0]); string str1 = sh.changeToWords(words[1]); if (str1 == " " || str1 == null || words[1] == "00") { str1 = "Zero "; } String inword = "In words: " + str + "and " + str1 + "Paise Only"; //Phrase inwrd = new Phrase(inword, _font2); DataTable client = conn.getdataset("select * from clientmaster where isactive=1 and clientID='" + cmbCustName.SelectedValue + "'"); DataTable dt1 = conn.getdataset("select * from company WHERE isactive=1"); prn.execute("delete from printing"); int j = 1; for (int i = 0; i < LVFO.Items.Count; i++) { try { string qry = "INSERT INTO Printing(T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16,T17,T18,T19,T20,T21,T22,T23,T24,T25,T26,T27,T28,T29,T30,T31,T32,T33,T34,T35,T36,T37,T38,T39,T40,T41,T42,T43,T44,T45,T46,T47,T48,T49,T50,T51,T52,T53,T54,T55,T56,T57,T58,T59,T60,T61,T62,T63,T64,T65,T66,T67,T68,T69,T70,T71,T72,T73,T74,T75,T76,T77,T78,T79,T80)VALUES"; qry += "('" + j++ + "','" + TxtBillNo.Text + "','" + TxtBillDate.Text + "','" + cmbTerms.Text + "','0','" + cmbCustName.Text + "','0','" + cmbPurchaseType.Text + "','" + LVFO.Items[i].SubItems[0].Text + "','" + LVFO.Items[i].SubItems[1].Text + "','" + LVFO.Items[i].SubItems[2].Text + "','" + LVFO.Items[i].SubItems[3].Text + "','" + LVFO.Items[i].SubItems[4].Text + "','" + LVFO.Items[i].SubItems[5].Text + "','" + LVFO.Items[i].SubItems[6].Text + "','" + LVFO.Items[i].SubItems[7].Text + "','" + LVFO.Items[i].SubItems[8].Text + "','" + LVFO.Items[i].SubItems[9].Text + "','" + LVFO.Items[i].SubItems[10].Text + "','" + LVFO.Items[i].SubItems[11].Text + "','" + LVFO.Items[i].SubItems[12].Text + "','" + LVFO.Items[i].SubItems[13].Text + "','" + LVFO.Items[i].SubItems[14].Text + "','" + lbltotcount.Text + "','" + lbltotpqty.Text + "','0','0','" + lblbasictot.Text + "','0','0','0','0','0','0','0','0','" + TxtBillTotal.Text + "','" + dt1.Rows[0][0].ToString() + "','" + dt1.Rows[0][1].ToString() + "','" + dt1.Rows[0][2].ToString() + "','" + dt1.Rows[0][3].ToString() + "','" + dt1.Rows[0][4].ToString() + "','" + dt1.Rows[0][5].ToString() + "','" + dt1.Rows[0][6].ToString() + "','" + dt1.Rows[0][7].ToString() + "','" + dt1.Rows[0][8].ToString() + "','" + dt1.Rows[0][9].ToString() + "','" + dt1.Rows[0][10].ToString() + "','" + dt1.Rows[0][11].ToString() + "','" + dt1.Rows[0][12].ToString() + "','" + dt1.Rows[0][13].ToString() + "','" + inword + "','0','0','0','0','0','" + txtremarks.Text + "','0','0','0','0','0','0','0','0','" + client.Rows[0][0].ToString() + "','" + client.Rows[0][1].ToString() + "','" + client.Rows[0][2].ToString() + "','" + client.Rows[0][3].ToString() + "','" + client.Rows[0][4].ToString() + "','" + client.Rows[0][5].ToString() + "','" + client.Rows[0][6].ToString() + "','" + client.Rows[0][7].ToString() + "','" + client.Rows[0][8].ToString() + "','" + client.Rows[0][9].ToString() + "','" + client.Rows[0][10].ToString() + "','" + client.Rows[0][11].ToString() + "','" + client.Rows[0][12].ToString() + "','" + client.Rows[0][13].ToString() + "')"; prn.execute(qry); } catch { } } Print popup = new Print("Purchase Return"); popup.ShowDialog(); popup.Dispose(); clearitem(); clearall(); LVFO.Items.Clear(); MessageBox.Show("Data Updated Successfully."); btnPayment.Text = "Save"; this.Hide(); } else { getsr(); for (int i = 0; i < LVFO.Items.Count; i++) { dtpid = conn.getdataset("Select Productid from productmaster where product_name='" + LVFO.Items[i].SubItems[0].Text.Replace(",", "") + "'"); SqlCommand cmd1 = new SqlCommand("INSERT INTO [BillProductMaster]([Bill_No],[Bill_Run_Date],[ProductName],[Pqty],[Bags],[Rate],[Per],[Amount],[DiscountPer],[Discountamt],[Tax],[Addtax],[Total],[isactive],[BillType],[productid])VALUES('" + txtVchNo.Text + "','" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "','" + LVFO.Items[i].SubItems[0].Text + "','" + LVFO.Items[i].SubItems[1].Text + "','" + LVFO.Items[i].SubItems[2].Text + "','" + LVFO.Items[i].SubItems[3].Text + "','" + LVFO.Items[i].SubItems[4].Text + "','" + LVFO.Items[i].SubItems[5].Text + "','" + LVFO.Items[i].SubItems[6].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[7].Text + "','" + LVFO.Items[i].SubItems[8].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[9].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[10].Text.Replace(",", "") + "',1,'PR','" + dtpid.Rows[0][0].ToString() + "')", con); //SqlCommand cmd1 = new SqlCommand("INSERT INTO [BillProductMaster]([VchNo],[BillNo],[BillRunDate],[ProductName],[Qty],[Free],[Rate],[Per],[BasicAmount],[DiscountPer],[Discount],[Vat],[AddVat],[Total],[isactive],[BillType])VALUES('" + txtVchNo.Text + "','"+TxtBillNo.Text+"','" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "','" + LVFO.Items[i].SubItems[0].Text + "','" + LVFO.Items[i].SubItems[1].Text + "','" + LVFO.Items[i].SubItems[2].Text + "','" + LVFO.Items[i].SubItems[3].Text + "','" + LVFO.Items[i].SubItems[4].Text + "','" + LVFO.Items[i].SubItems[5].Text + "','" + LVFO.Items[i].SubItems[6].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[7].Text + "','" + LVFO.Items[i].SubItems[8].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[9].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[10].Text.Replace(",", "") + "',1,'PR')", con); cmd1.ExecuteNonQuery(); } SqlCommand cmd = new SqlCommand("INSERT INTO [BillMaster]([Bill_No],[Bill_Date],[Terms],[ClientID],[SaleType],[Count],[TotalQty],[TotalBasic],[TotalTax],[TotalAddTax],[TotalDiscount],[TotalNet],[isactive],[DispatchDetails],[Remarks],[BillType],[CompanyId])VALUES('" + txtVchNo.Text + "','" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "','" + cmbTerms.Text + "','" + cmbCustName.SelectedValue + "','" + cmbPurchaseType.SelectedValue + "'," + lbltotcount.Text + "," + lbltotpqty.Text + "," + lblbasictot.Text + "," + lbltaxtot.Text + "," + txtaddtax.Text + "," + lbltotaldiscount.Text + "," + Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00") + ",1,'" + txtdispatch.Text + "','" + txtremarks.Text + "','PR'," + Master.companyId + ")", con); cmd.ExecuteNonQuery(); conn.execute("INSERT INTO [Ledger]([VoucherID],[Date1],[TranType],[AccountID],[AccountName],[Amount],[DC],[CompanyId],[isActive]) values ('" + txtVchNo.Text + "','" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "','PurchaseReturn','" + cmbCustName.SelectedValue + "','" + cmbCustName.Text + "','" + TxtBillTotal.Text + "','D'," + Master.companyId + ",1)"); ChangeNumbersToWords sh = new ChangeNumbersToWords(); String s1 = Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00"); string[] words = s1.Split('.'); string str = sh.changeToWords(words[0]); string str1 = sh.changeToWords(words[1]); if (str1 == " " || str1 == null || words[1] == "00") { str1 = "Zero "; } String inword = "In words: " + str + "and " + str1 + "Paise Only"; DataTable client = conn.getdataset("select * from clientmaster where isactive=1 and clientID='" + cmbCustName.SelectedValue + "'"); DataTable dt1 = conn.getdataset("select * from company WHERE isactive=1"); prn.execute("delete from printing"); int j = 1; for (int i = 0; i < LVFO.Items.Count; i++) { try { string qry = "INSERT INTO Printing(T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16,T17,T18,T19,T20,T21,T22,T23,T24,T25,T26,T27,T28,T29,T30,T31,T32,T33,T34,T35,T36,T37,T38,T39,T40,T41,T42,T43,T44,T45,T46,T47,T48,T49,T50,T51,T52,T53,T54,T55,T56,T57,T58,T59,T60,T61,T62,T63,T64,T65,T66,T67,T68,T69,T70,T71,T72,T73,T74,T75,T76,T77,T78,T79,T80)VALUES"; qry += "('" + j++ + "','" + TxtBillNo.Text + "','" + TxtBillDate.Text + "','" + cmbTerms.Text + "','0','" + cmbCustName.Text + "','0','" + cmbPurchaseType.Text + "','" + LVFO.Items[i].SubItems[0].Text + "','" + LVFO.Items[i].SubItems[1].Text + "','" + LVFO.Items[i].SubItems[2].Text + "','" + LVFO.Items[i].SubItems[3].Text + "','" + LVFO.Items[i].SubItems[4].Text + "','" + LVFO.Items[i].SubItems[5].Text + "','" + LVFO.Items[i].SubItems[6].Text + "','" + LVFO.Items[i].SubItems[7].Text + "','" + LVFO.Items[i].SubItems[8].Text + "','" + LVFO.Items[i].SubItems[9].Text + "','" + LVFO.Items[i].SubItems[10].Text + "','" + LVFO.Items[i].SubItems[11].Text + "','" + LVFO.Items[i].SubItems[12].Text + "','" + LVFO.Items[i].SubItems[13].Text + "','" + LVFO.Items[i].SubItems[14].Text + "','" + lbltotcount.Text + "','" + lbltotpqty.Text + "','0','0','" + lblbasictot.Text + "','0','0','0','0','0','0','0','0','" + TxtBillTotal.Text + "','" + dt1.Rows[0][0].ToString() + "','" + dt1.Rows[0][1].ToString() + "','" + dt1.Rows[0][2].ToString() + "','" + dt1.Rows[0][3].ToString() + "','" + dt1.Rows[0][4].ToString() + "','" + dt1.Rows[0][5].ToString() + "','" + dt1.Rows[0][6].ToString() + "','" + dt1.Rows[0][7].ToString() + "','" + dt1.Rows[0][8].ToString() + "','" + dt1.Rows[0][9].ToString() + "','" + dt1.Rows[0][10].ToString() + "','" + dt1.Rows[0][11].ToString() + "','" + dt1.Rows[0][12].ToString() + "','" + dt1.Rows[0][13].ToString() + "','" + inword + "','0','0','0','0','0','" + txtremarks.Text + "','0','0','0','0','0','0','0','0','" + client.Rows[0][0].ToString() + "','" + client.Rows[0][1].ToString() + "','" + client.Rows[0][2].ToString() + "','" + client.Rows[0][3].ToString() + "','" + client.Rows[0][4].ToString() + "','" + client.Rows[0][5].ToString() + "','" + client.Rows[0][6].ToString() + "','" + client.Rows[0][7].ToString() + "','" + client.Rows[0][8].ToString() + "','" + client.Rows[0][9].ToString() + "','" + client.Rows[0][10].ToString() + "','" + client.Rows[0][11].ToString() + "','" + client.Rows[0][12].ToString() + "','" + client.Rows[0][13].ToString() + "')"; prn.execute(qry); } catch { } } Print popup = new Print("Purchase Return"); popup.ShowDialog(); popup.Dispose(); clearitem(); clearall(); LVFO.Items.Clear(); this.Hide(); } } } else { MessageBox.Show("please fill all information"); } } catch (Exception ex) { MessageBox.Show("Error:" + ex.Message); } finally { con.Close(); } }
private void btnsubmit() { try { getcon(); if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); DialogResult dr = MessageBox.Show("Do you want to Generate Bill?", "Bill", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dr == DialogResult.Yes) { if (btnPayment.Text == "Update") { SqlCommand cmd2 = new SqlCommand("Update billproductmaster set isactive='0' where VchNo='" + txtVchNo.Text + "' and BillType='SR'", con); cmd2.ExecuteNonQuery(); for (int i = 0; i < LVFO.Items.Count; i++) { SqlCommand cmd1 = new SqlCommand("INSERT INTO [BillProductMaster]([VchNo],[BillNo],[BillRunDate],[ProductName],[Qty],[Free],[Rate],[Per],[BasicAmount],[DiscountPer],[Discount],[Vat],[AddVat],[Total],[isactive],[BillType])VALUES('" + txtVchNo.Text + "','" + TxtBillNo.Text + "','" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "','" + LVFO.Items[i].SubItems[0].Text + "','" + LVFO.Items[i].SubItems[1].Text + "','" + LVFO.Items[i].SubItems[2].Text + "','" + LVFO.Items[i].SubItems[3].Text + "','" + LVFO.Items[i].SubItems[4].Text + "','" + LVFO.Items[i].SubItems[5].Text + "','" + LVFO.Items[i].SubItems[6].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[7].Text + "','" + LVFO.Items[i].SubItems[8].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[9].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[10].Text.Replace(",", "") + "',1,'SR')", con); cmd1.ExecuteNonQuery(); } SqlCommand cmd = new SqlCommand("UPDATE [BillMaster]SET [VChNo]='" + txtVchNo.Text + "',[BillNo] = '" + TxtBillNo.Text + "',[BillDate] = '" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "',[Terms] = '" + cmbTerms.Text + "',[ClientID] = '" + cmbCustName.SelectedValue + "',[SaleType] = '" + cmbPurchaseType.SelectedValue + "',[Count] = " + lbltotcount.Text + ",[TotalQty] = " + lbltotpqty.Text + ",[TotalBasic] = " + lblbasictot.Text + ",[TotalTax] =" + lbltaxtot.Text + " ,[TotalAddTax] =" + txtaddtax.Text + " ,[TotalDiscount] =" + lbltotaldiscount.Text + " ,[TotalNet] = " + Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00") + ",[isactive]=1,[DispatchDetails]='" + txtdispatch.Text + "',[Remarks]='" + txtremarks.Text + "',[billtype]='SR' where VchNo='" + txtVchNo.Text + "' and CompanyId=" + Master.companyId + "", con); cmd.ExecuteNonQuery(); conn.execute("UPDATE [Ledger] SET [Date1]='" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "',[TranType] = 'SaleReturn',[AccountID] = '" + cmbCustName.SelectedValue + "',[AccountName]='" + cmbCustName.Text + "' ,[Amount] = '" + Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00") + "',[DC] = 'C' where [VoucherID]= '" + txtVchNo.Text + "' and [TranType] = 'PurchaseReturn' and CompanyId=" + Master.companyId + ""); ChangeNumbersToWords sh = new ChangeNumbersToWords(); String s1 = Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00"); string[] words = s1.Split('.'); string str = sh.changeToWords(words[0]); string str1 = sh.changeToWords(words[1]); if (str1 == " " || str1 == null || words[1] == "00") { str1 = "Zero "; } String inword = "In words: " + str + "and " + str1 + "Paise Only"; // Phrase inwrd = new Phrase(inword, _font2); clearitem(); clearall(); LVFO.Items.Clear(); MessageBox.Show("Data Updated Successfully."); btnPayment.Text = "Save"; } else { getsr(); for (int i = 0; i < LVFO.Items.Count; i++) { SqlCommand cmd1 = new SqlCommand("INSERT INTO [BillProductMaster]([VchNo],[BillNo],[BillRunDate],[ProductName],[Qty],[Free],[Rate],[Per],[BasicAmount],[DiscountPer],[Discount],[Vat],[AddVat],[Total],[isactive],[BillType])VALUES('" + txtVchNo.Text + "','" + TxtBillNo.Text + "','" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "','" + LVFO.Items[i].SubItems[0].Text + "','" + LVFO.Items[i].SubItems[1].Text + "','" + LVFO.Items[i].SubItems[2].Text + "','" + LVFO.Items[i].SubItems[3].Text + "','" + LVFO.Items[i].SubItems[4].Text + "','" + LVFO.Items[i].SubItems[5].Text + "','" + LVFO.Items[i].SubItems[6].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[7].Text + "','" + LVFO.Items[i].SubItems[8].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[9].Text.Replace(",", "") + "','" + LVFO.Items[i].SubItems[10].Text.Replace(",", "") + "',1,'SR')", con); cmd1.ExecuteNonQuery(); } SqlCommand cmd = new SqlCommand("INSERT INTO [BillMaster]([VchNo],[BillNo],[BillDate],[Terms],[ClientID],[SaleType],[Count],[TotalQty],[TotalBasic],[TotalTax],[TotalAddTax],[TotalDiscount],[TotalNet],[isactive],[DispatchDetails],[Remarks],[BillType],[CompanyId])VALUES('" + txtVchNo.Text + "','" + TxtBillNo.Text + "','" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "','" + cmbTerms.Text + "','" + cmbCustName.SelectedValue + "','" + cmbPurchaseType.SelectedValue + "'," + lbltotcount.Text + "," + lbltotpqty.Text + "," + lblbasictot.Text + "," + lbltaxtot.Text + "," + txtaddtax.Text + "," + lbltotaldiscount.Text + "," + Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00") + ",1,'" + txtdispatch.Text + "','" + txtremarks.Text + "','SR'," + Master.companyId + ")", con); cmd.ExecuteNonQuery(); conn.execute("INSERT INTO [Ledger]([VoucherID],[Date1],[TranType],[AccountID],[AccountName],[Amount],[DC],[CompanyId],[isActive]) values ('" + txtVchNo.Text + "','" + Convert.ToDateTime(TxtBillDate.Text).ToString("dd/MMM/yyyy") + "','SaleReturn','" + cmbCustName.SelectedValue + "','" + cmbCustName.Text + "','" + TxtBillTotal.Text + "','C'," + Master.companyId + ",1)"); ChangeNumbersToWords sh = new ChangeNumbersToWords(); String s1 = Math.Round(Convert.ToDouble(TxtBillTotal.Text), 2).ToString("########.00"); string[] words = s1.Split('.'); string str = sh.changeToWords(words[0]); string str1 = sh.changeToWords(words[1]); if (str1 == " " || str1 == null || words[1] == "00") { str1 = "Zero "; } String inword = "In words: " + str + "and " + str1 + "Paise Only"; clearitem(); clearall(); LVFO.Items.Clear(); } } else { MessageBox.Show("please fill all information"); } } catch (Exception ex) { MessageBox.Show("Error:" + ex.Message); } finally { con.Close(); } }