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();
}
}
