private void calculatetotal()
{
double payment, receipt, intrest, openbalance, openintrest, closingbalance, closingintrest;
payment = 0;
receipt = 0;
intrest = 0;
//4,5,7
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
payment += getdoublevalue(i, 4);
receipt += getdoublevalue(i, 5);
intrest += getdoublevalue(i, 7);
}
double.TryParse(textBox4.Text, out openbalance);
double.TryParse(textBox5.Text, out openintrest);
closingbalance = payment - receipt;
closingintrest = intrest + double.Parse(textBox5.Text);
string result;
Supporter.set_two_digit_precision(payment.ToString(), out result);
textBox6.Text = result;
Supporter.set_two_digit_precision(receipt.ToString(), out result);
textBox7.Text = result;
Supporter.set_two_digit_precision(intrest.ToString(), out result);
textBox8.Text = result;
Supporter.set_two_digit_precision(closingintrest.ToString(), out result);
textBox9.Text = result;
Supporter.set_two_digit_precision(closingbalance.ToString(), out result);
textBox10.Text = result;
}
private void txt_acntname_Leave(object sender, EventArgs e)
{
if (txt_acntname.Text == "" || btn_save.Text != "Save")
{
return;
}
DataTable dt = internalData.customer.get(new e_columns[] { e_columns.e_name }, e_db_operation.e_getUnique);
string x = Supporter.compare(txt_acntname.Text, dt, 3);
if (x != "")
{
txt_acntname.Text = ""; txt_acntname.Focus();
}
}
private void ledger()
{
if (textBox2.Text == "")
{
return;
}
if (textBox3.Text == "")
{
return;
}
float a;
int b;
if (!int.TryParse(textBox2.Text, out b) || !float.TryParse(textBox3.Text, out a))
{
return;
}
DataTable ledger = con.getTable(string.Format("select dbo.inddatevar(date) as date1,transtype,detail,exp,payment,recepit from ledger_showall where n ='{0}' and c = '{1}' order by date", comboBox1.Text, comboBox3.Text));
intrest_calculation ic = new intrest_calculation(ledger);
ic.calculate(gettype(), dateTimePicker3.Value, double.Parse(textBox3.Text), int.Parse(textBox2.Text), dateTimePicker1.Value, dateTimePicker2.Value);
DataTable dt = con.getTable(string.Format("select isnull(sum(isnull(payment,0)),0) - isnull(sum(isnull(recepit,0)),0) from ledger_showall where n = '{0}' and c = '{1}' and date < dbo.indvardate('{2}')", comboBox1.Text, comboBox3.Text, dateTimePicker1.Text.ToString().Split(Convert.ToChar(" "))[0]));
textBox4.Text = dt.Rows[0][0].ToString();
dt = con.getTable(string.Format("select isnull(sum(isnull(payment,0)),0) - isnull(sum(isnull(recepit,0)),0) from ledger_showall where n = '{0}' and c = '{1}' and date <= dbo.indvardate('{2}')", comboBox1.Text, comboBox3.Text, dateTimePicker2.Text.ToString().Split(Convert.ToChar(" "))[0]));
textBox10.Text = dt.Rows[0][0].ToString();
string res;
Supporter.set_two_digit_precision(ic.openint.ToString(), out res);
textBox5.Text = res;
dt = con.getTable(string.Format("select date,transtype,detail1 as detail,exp,payment,recepit,days,intrest from printledger where dbo.indvardate(date) between dbo.indvardate('{0}') and dbo.indvardate('{1}') order by dbo.indvardate(date)", dateTimePicker1.Text.ToString().Split(Convert.ToChar(" "))[0], dateTimePicker2.Text.ToString().Split(Convert.ToChar(" "))[0]));
dataGridView1.DataSource = dt;
dataGridView1.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dataGridView1.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dataGridView1.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dataGridView1.Columns[6].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dataGridView1.Columns[7].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
calculatetotal();
}
public static string withpoint(string amt)
{
string abc;
Supporter.set_two_digit_precision(amt, out abc);
return(abc);
//if (amt == "")
// return "0.00";
//if (amt.Contains("."))
//{
// string[] y = amt.Split(Convert.ToChar( "."));
// if (y[1].Length == 0)
// return amt + "00";
// else if (y[1].Length == 1)
// return amt + "0";
// else if (y[1].Length == 2)
// return amt;
// else return y[0] + "." + y[1].Substring(0, 2);
//}
//else
//{
// return amt + ".00";
//}
}
public void calculate(int type, DateTime caldate, double percent, int caldays, DateTime opendate, DateTime closedate)
{
//System.Windows.Forms.MessageBox.Show(gap_in_financial_year(new DateTime(2011, 4, 1), new DateTime(2013, 4, 1),0).ToString());
//type 0 month
//type 1 quater
//type 2 halfyearly
//type 3 1 yearly
double calintrest = 0;
Connection con = new Connection();
con.connent();
con.exeNonQurey("delete from printledger");
if (Dt.Rows.Count == 0)
{
return;
}
DateTime date = converttodate(Dt.Rows[0][0].ToString());
DateTime amountdate;
double amount = 0;
DateTime prevdate, curdate;
prevdate = getdate(0);
double total = 0;
double calint2 = 0;
double calint3 = 0;
for (int i = 0; i < Dt.Rows.Count; i++)
{
curdate = getdate(i);
DateTime pd1 = prevdate;
//-----------------------------------------------------------------------------------
int abc = gap_in_financial_year(prevdate, curdate, type);
DateTime p = prevdate;
int xyz = abc;
while (abc != 0)
{
DateTime d = getstartdate(getenddate(p, type).AddDays(1), type);
p = d;
amount = amount + calint2;
calint3 += calint2 + intrest(d, calint3 + calint2, caldate, percent, caldays, type, "Opening balance");
if (DateTime.Compare(d, opendate) <= 0)
{
openint = calint3;// +intrest(d, calint3, caldate, percent, caldays, type, "Closing intrest");
//openint = calintrest + intrest(d, calintrest, caldate, percent, caldays, type, "Closing intrest"); ;
}
//if (DateTime.Compare(d, opendate) < 0 && xyz == 1)
//{
// openint = calint3;//+ intrest(d, calint3, caldate, percent, caldays, type, "Closing intrest"); ;
// //openint = calintrest;// +intrest(d, calintrest, caldate, percent, caldays, type, "Closing intrest"); ;
//}
//calint3 += calint2 + intrest(d, total, caldate, percent, caldays, type, "Opening balance");
double ci1 = calintrest;
calintrest = calintrest + intrest(d, amount, caldate, percent, caldays, type, "Opening balance");
int a = getenddate(d, type).Subtract(d).Days + 1;
int b = caldate.Subtract(d).Days + 1;
if (b < a)
{
a = b;
}
string res;
Supporter.set_two_digit_precision(total.ToString(), out res);
string date11 = two_length_string(d.Day.ToString()) + "/" + two_length_string(d.Month.ToString()) + "/" + d.Year.ToString();
if (res != "0.00")
{
con.exeNonQurey(string.Format("insert into printledger(date,detail,exp,payment,recepit,days,intrest,transtype,detail1) values('{0}','{1}',{2},{3},{4},{5},{6},'Opening balance',NULL)",
date11, "Opening Balance", "NULL", res, "NULL",
a.ToString(),
intrest(d, total, caldate, percent, caldays, type, "Opening balance")));
}
calint2 = intrest(d, total, caldate, percent, caldays, type, "Opening balance");
abc--;
}
//-----------------------------------------------------------------------------------
//int abc = gap_in_financial_year(prevdate, curdate,type);
//bool xyz = false;
//if (abc == 1)
// xyz = true;
//while(abc!=0)
//{
// amount = amount + calintrest;
// calintrest += intrest(getstartdate(curdate, type), amount, caldate, percent, caldays, type, "Opening balance");
// pd1 = getstartdate(getenddate(pd1,type).AddDays(1),type);
// DateTime d = getstartdate(pd1, type);
// int a = getnextdate(getenddate(d,type), type).Subtract(d).Days + 1;
// int b = caldate.Subtract(d).Days + 1;
// if (b < a)
// a = b;
// string res;
// Supporter.set_two_digit_precision(total.ToString(), out res);
// string date11 = two_length_string(d.Day.ToString()) + "/" + two_length_string(d.Month.ToString()) + "/" + d.Year.ToString();
// con.exeNonQurey(string.Format("insert into printledger(date,detail,exp,payment,recepit,days,intrest,transtype,detail1) values('{0}','{1}',{2},{3},{4},{5},{6},'Opening balance',NULL)",
// date11, "Opening Balance", "NULL", res, "NULL",
// a.ToString(),
// intrest(getstartdate(curdate, type), total, caldate, percent, caldays, type, "Opening balance")));
// Supporter.set_two_digit_precision((amount - total).ToString(), out res);
// if (DateTime.Compare(d, opendate) < 0)
// {
// openint = calintrest + intrest(d, calintrest, caldate, percent, caldays, type, "Closing intrest"); ;
// }
// //curdate = getstartdate(getenddate(curdate, type).AddDays(1), type);
// abc--;
//}
{
int a = getnextdate(curdate, type).Subtract(getdate(i)).Days + 1;
int b = caldate.Subtract(getdate(i)).Days + 1;
if (b < a)
{
a = b;
}
if (Dt.Rows[i][1].ToString() == "Bill")
{
a -= 60;
}
double d = intrest(getdate(i), amount, caldate, percent, caldays, type, "Opening balance");
con.exeNonQurey(string.Format("insert into printledger(date,detail,exp,payment,recepit,days,intrest,transtype,detail1) values('{0}','{1}',{2},{3},{4},{5},{6},'{7}','{8}')",
Dt.Rows[i][0], Dt.Rows[i][1].ToString() + "--" + Dt.Rows[i][2].ToString(), getnullstr(i, 3),
getnullstr(i, 4), getnullstr(i, 5),
a.ToString(),
intrest(getdate(i), getamount(i), caldate, percent, caldays, type, Dt.Rows[i][1].ToString()), Dt.Rows[i][1].ToString(), Dt.Rows[i][2].ToString()));
}
amountdate = converttodate(Dt.Rows[i][0].ToString());
amount += getamount(i);
total = total + getamount(i);
calintrest += intrest(getdate(i), getamount(i), caldate, percent, caldays, type, Dt.Rows[i][1].ToString());
calint2 += intrest(getdate(i), getamount(i), caldate, percent, caldays, type, Dt.Rows[i][1].ToString());
if (DateTime.Compare(curdate, opendate) < 0)
{
double x = intrest(getstartdate(getenddate(curdate, type), type), calintrest, caldate, percent, caldays, type, "Closing intrest");
openint = calintrest + intrest(getstartdate(getenddate(curdate, type), type), calintrest, caldate, percent, caldays, type, "Closing intrest");
}
prevdate = curdate;
}
//=======================================================
int abc1 = gap_in_financial_year(converttodate(dt.Rows[dt.Rows.Count - 1][0].ToString()), caldate, type);
DateTime p1 = converttodate(dt.Rows[dt.Rows.Count - 1][0].ToString());
int xyz1 = abc1;
while (abc1 != 0)
{
DateTime d = getstartdate(getenddate(p1, type).AddDays(1), type);
p1 = d;
amount = amount + calint2;
double ci1 = calintrest;
calintrest = calintrest + intrest(d, amount, caldate, percent, caldays, type, "Opening balance");
int a = getenddate(d, type).Subtract(d).Days + 1;
int b = caldate.Subtract(d).Days + 1;
if (b < a)
{
a = b;
}
string res;
Supporter.set_two_digit_precision(total.ToString(), out res);
string date11 = two_length_string(d.Day.ToString()) + "/" + two_length_string(d.Month.ToString()) + "/" + d.Year.ToString();
if (res != "0.00")
{
con.exeNonQurey(string.Format("insert into printledger(date,detail,exp,payment,recepit,days,intrest,transtype,detail1) values('{0}','{1}',{2},{3},{4},{5},{6},'Opening balance',NULL)",
date11, "Opening Balance", "NULL", res, "NULL",
a.ToString(),
intrest(d, total, caldate, percent, caldays, type, "Opening balance")));
}
calint2 = intrest(d, total, caldate, percent, caldays, type, "Opening balance");
if (DateTime.Compare(d, opendate) <= 0 && xyz1 > 1)
{
openint = ci1 + intrest(d, ci1, caldate, percent, caldays, type, "Closing intrest");;
}
if (DateTime.Compare(d, opendate) < 0 && xyz1 == 1)
{
openint = calintrest;// +intrest(d, calintrest, caldate, percent, caldays, type, "Closing intrest"); ;
}
abc1--;
}
//=======================================================
con.disconnect();
total1 = total;
grandtotal1 = amount;
intresttotal1 = calintrest;
int aaaa = DateTime.Compare(getstartdate(caldate, type), getstartdate(getdate(dt.Rows.Count - 1), type));
//if ((DateTime.Compare(getstartdate(caldate, type), getstartdate(getdate(dt.Rows.Count - 1),type)) > 0) && (DateTime.Compare(getstartdate(caldate,type),getstartdate(opendate,type)) == 0))
//{
// DateTime d = getstartdate(caldate, type);
// string date11 = two_length_string(d.Day.ToString()) + "/" + two_length_string(d.Month.ToString()) + "/" + d.Year.ToString();
// string bal = total.ToString();
// int diff = caldate.Subtract(d).Days;
// con.connent();
// con.exeNonQurey(string.Format("insert into printledger(date,detail,exp,payment,recepit,days,intrest,transtype,detail1) values('{0}','{1}',{2},{3},{4},{5},{6},'Opening balance',NULL)",
// date11, "Opening Balance", "NULL", bal, "NULL",
// diff.ToString(),
// intrest(d,Convert.ToDouble(bal), caldate, percent, caldays, type, "Opening balance")));
// con.disconnect();
// openint = (calintrest) + intrest(d, (calintrest), caldate, percent, caldays, type, "Closing intrest");
//}
}
private int save()
{
int maxrecepitno, cuttent;
DataTable dt = internalData.viewRecepit.get(new e_columns[] { e_columns.e_recepitno }, e_db_operation.e_getAll);
maxrecepitno = Convert.ToInt32(dt.Compute(string.Format("max({0})", internalData.viewRecepit.column_to_str(e_columns.e_recepitno)), string.Empty)) + 1;
cuttent = Convert.ToInt32(textBox1.Text);
if (textBox1.Text == "" || comboBox2.Text == "" || comboBox1.Text == "" || textBox4.Text == "" || textBox2.Text == "")
{
MessageBox.Show("Please fill all fields", "warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(0);
}
string xxaabb = con.exesclr(string.Format("check_bill_receipt_number 'Receipt',{0}", textBox1.Text));
if (xxaabb == "1")
{
Supporter.message_error("Cannot use deleted Receipt number please change Receipt number");
textBox1.Select();
textBox1.Focus();
return(0);
}
if (textBox3.Text == "")
{
textBox3.Text = "0.00";
}
string zero = "null", four = "null", five = "null", six = "null", ten = "null";
if (textBox1.Text != "")
{
zero = textBox1.Text;
}
if (textBox2.Text != "")
{
four = textBox2.Text;
}
if (textBox3.Text != "")
{
five = textBox3.Text;
}
if (textBox4.Text != "")
{
six = textBox4.Text;
}
if (textBox8.Text != "")
{
ten = textBox8.Text;
}
string abc = dateTimePicker1.Text;
string x;
if (button1.Text == "Save")
{
x = con.exesclr(string.Format("exec add_recepit {0} , '{1}' , '{2}' , '{3}' , {4} , {5} , {6} , '{7}' , '{8}' , '{9}' , '{10}' , '{11}' , '{12}'", zero, dateTimePicker1.Text.ToString().Split(Convert.ToChar(" "))[0], comboBox1.Text, comboBox2.Text, four, five, six, textBox7.Text, comboBox3.Text, textBox6.Text, ten, textBox9.Text, textBox20.Text));
}
else
{
x = con.exesclr(string.Format("exec add_recepit {0} , '{1}' , '{2}' , '{3}' , {4} , {5} , {6} , '{7}' , '{8}' , '{9}' , '{10}' , '{11}' , '{12}'", zero, dateTimePicker1.Text.ToString().Split(Convert.ToChar(" "))[0], comboBox1.Text, comboBox2.Text, four, five, six, textBox7.Text, comboBox3.Text, textBox6.Text, ten, textBox9.Text, textBox20.Text));
}
if (x == "0")
{
MessageBox.Show("Invalid customer name", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
comboBox1.Focus(); comboBox1.Select();
company = 1;
}
else if (x == "1")
{
MessageBox.Show("Invalid Bank name", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
comboBox3.Focus(); comboBox3.Select();
company = 1;
}
else
{
if (button1.Text == "Save")
{
for (int i = maxrecepitno; i < cuttent; i++)
{
con.exeNonQurey(string.Format("insert into recepit(recepitno) values({0})", i.ToString()));
}
MessageBox.Show("Recepit Saved Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Recepit Updated Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
internalData.viewRecepit.emptyTable();
comboBox1.Focus(); comboBox1.Select();
con.exeNonQurey("delete from temp_recepit");
string zero1 = "null", four1 = "null", five1 = "null", six1 = "null", ten1 = "null";
if (textBox1.Text != "")
{
zero1 = textBox1.Text;
}
if (textBox2.Text != "")
{
four1 = textBox2.Text;
}
if (textBox3.Text != "")
{
five1 = textBox3.Text;
}
if (textBox4.Text != "")
{
six1 = textBox4.Text;
}
if (textBox8.Text != "")
{
ten1 = textBox8.Text;
}
if (ten1.ToLower() == "null")
{
ten1 = " ------";
}
create_backup.create();
con.exeNonQurey(string.Format("insert into temp_recepit(recepitno , date , customername , city , amount , cd , total , bankname , checknumber , rupeeword , billno , through , manualrecepit , note) values({0} ,'{1}' ,'{2}' ,'{3}' , {4},{5},{6},'{7}','{8}' , '{9}' , '{10}' , '{11}' , '{12}' , '{13}' )", zero1, dateTimePicker1.Text.ToString().Split(Convert.ToChar(Convert.ToChar(" ")))[0], comboBox1.Text + " " + comboBox2.Text, comboBox2.Text, four1, five1, six1, comboBox3.Text, textBox6.Text, textBox5.Text, ten1, textBox9.Text, textBox7.Text, textBox20.Text));
clear();
}
return(1);
}
