public bool insert(order[] sorder)
{
log = new db();
int partyno = getpartyno(party);
string query = "insert into entry values(" + saleno + ",'" + type + "'," + totalamount + "," + partyno + ",'" + desc + "','" + date + "')";
log.open();
if (log.insertquery(query))
{
for (int i = 0; i < rowcount; i++)
{
query = "insert into sale values(" + sorder[i].no + ",'SALE','" + sorder[i].code + "'," + sorder[i].amt + "," + sorder[i].kg + "," + sorder[i].pc + "," + sorder[i].bag + "," + sorder[i].rate + ")";
if (!log.insertquery(query))
{
return(false);
}
}
}
else
{
log.close();
return(false);
}
log.close();
return(true);
}
public void update()
{
log = new db();
log.open();
log.close();
}
public DataTable getpname(string key)
{
string query = "select name from partysearch where name like '" + key + "%'";
log = new db();
log.open();
DataTable read = log.searchquery(query);
log.close();
return(read);
}
public DataTable loadpr()
{
string query = "Select pname from product";
log = new db();
log.open();
DataTable read = log.searchquery(query);
log.close();
return(read);
}
public DataTable salerep(string key, string start, string end)
{
DataTable dt = new DataTable();
string query = "select p.pname+' '+p.city,e.eno,FORMAT (e.edate, 'dd-MM-yy') as date,s.bag,s.pc,s.kg,s.rate,s.amount from entry e inner join sale s on e.eno = s.eno and e.etype = 'SALE' inner join party p on p.id = e.partyid inner join product r on s.pcode = r.pcode where s.pcode = '" + key + "' and e.edate between '" + start + "' and '" + end + "' ";
db log = new db();
log.open();
dt = log.searchquery(query);
log.close();
return(dt);
}
public bool points(int start, int end)
{
log = new db();
int partyno = getpartyno(partyname);
string query = "insert into cr values(" + start + "," + end + ")";
log.open();
bool sucess = log.insertquery(query);
log.close();
return(sucess);
}
public int getsaleid()
{
DataTable read;
log = new db();
log.open();
string query = "select ISNULL(CAST(max(eno)+1 as int),0) from entry where etype='SALE'";
read = log.searchquery(query);
int key = Convert.ToInt32(read.Rows[0][0].ToString());
log.close();
return(key);
}
public bool insert()
{
log = new db();
int partyno = getpartyno(partyname);
string query = "insert into entry values(" + eno + ",'CRV','" + amt + "'," + partyno + ",'" + description + "','" + date + "')";
log.open();
bool sucess = log.insertquery(query);
log.close();
return(sucess);
}
public int getpartyno(string pname)
{
log = new TMS.db();
log.open();
int key;
DataTable read;
string query = "select id from partysearch where name = '" + pname + "'";
read = log.searchquery(query);
log.close();
key = Convert.ToInt32(read.Rows[0][0].ToString());
return(key);
}
public string getpartyname(int key)
{
log = new TMS.db();
log.open();
string pname;
DataTable read;
string query = "select name from partysearch where id = '" + key + "'";
read = log.searchquery(query);
log.close();
pname = read.Rows[0][0].ToString();
return(pname);
}
public void del(string searchkey)
{
log = new TMS.db();
log.open();
int key;
DataTable read;
string query = "select id from partysearch where name = '" + searchkey + "'";
read = log.searchquery(query);
key = Convert.ToInt32(read.Rows[0][0].ToString());
query = "delete from party where id = " + key + "";
log.deletequery(query);
log.close();
}
public bool del(string key)
{
log = new db();
log.open();
string query = "delete from product where pcode = '" + key + "' ";
bool success = log.insertquery(query);
log.close();
if (success)
{
return(true);
}
else
{
return(false);
}
}
public bool update(string key)
{
log = new db();
log.open();
string query = "update product set pcode='" + pcode + "',pname='" + pname + "',srate=" + srate + ",prate=" + prate + " where pcode = '" + key + "'";
bool success = log.insertquery(query);
log.close();
if (success)
{
return(true);
}
else
{
return(false);
}
}
public void update(string searchkey)
{
log = new TMS.db();
log.open();
int key;
DataTable read;
string query = "select id from partysearch where name = '" + searchkey + "'";
read = log.searchquery(query);
key = Convert.ToInt32(read.Rows[0][0].ToString());
query = "update party set pname='" + pname + "',address='" + paddress + "',city='" + pcity + "',contact='" + pcontact + "',balance=" + pbalance + " where id = " + key + "";
log.updatequery(query);
log.close();
}
public point getpoint(int key)
{
point p = new point();
DataTable read;
log = new db();
log.open();
string query = "select * from cr where start <= " + key + " and fin >= " + key + " ";
read = log.searchquery(query);
p.start = Convert.ToInt32(read.Rows[0][0].ToString());
p.end = Convert.ToInt32(read.Rows[0][1].ToString());
log.close();
return(p);
}
public bool delete(int key)
{
log = new TMS.db();
string query = "delete from entry where eno=" + key + " and etype='SALE'";
log.open();
bool success = log.deletequery(query);
log.close();
if (success)
{
return(true);
}
else
{
return(false);
}
}
public bool add()
{
log = new TMS.db();
log.open();
string query;
query = "insert into product(pcode,pname,srate,prate) values('" + pcode + "', '" + pname + "', " + srate + ", " + prate + ")";
bool success = log.insertquery(query);
log.close();
if (success)
{
return(true);
}
else
{
return(false);
}
}
public bool add()
{
log = new TMS.db();
log.open();
string query;
query = "insert into party(pname,address,city,contact,balance) values('" + pname + "', '" + paddress + "', '" + pcity + "', '" + contact + "', '" + pbalance + "')";
bool success = log.insertquery(query);
log.close();
if (success)
{
return(true);
}
else
{
return(false);
}
}
public string getprname(string code)
{
string pname;
DataTable read;
string query;
query = "select pname from product where pcode='" + code + "'";
log.open();
read = log.searchquery(query);
log.close();
if (read.Rows.Count != 0)
{
pname = read.Rows[0]["pname"].ToString();
}
else
{
pname = "";
}
return(pname);
}
private void CityWiseReport_Load(object sender, EventArgs e)
{
string query = "select city,sum(balance) as amount from party group by city";
db log = new db();
log.open();
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
read = log.searchquery(query);
log.close();
if (read.Rows.Count != 0)
{
for (int i = 0; i < read.Rows.Count; i++)
{
dataGridView1.Rows.Add(1);
dataGridView1[0, i].Value = read.Rows[i][0].ToString();
dataGridView1[1, i].Value = read.Rows[i][1].ToString();
}
}
}
public bool search(string key)
{
log = new db();
log.open();
string query = "select * from product where pcode = '" + key + "' or pname = '" + key + "'";
DataTable read = log.searchquery(query);
log.close();
if (read.Rows.Count != 0)
{
pname = read.Rows[0]["pname"].ToString();
code = read.Rows[0]["pcode"].ToString();
srate = Convert.ToInt32(read.Rows[0]["srate"]);
prate = Convert.ToInt32(read.Rows[0]["prate"]);
return(true);
}
else
{
return(false);
}
}
public bool search(int key)
{
log = new TMS.db();
log.open();
DataTable read;
string query = "select * from payselect where eno=" + key + " and etype='CPV'";
read = log.searchquery(query);
bool success = false;
log.close();
if (read.Rows.Count != 0)
{
success = true;
eno = Convert.ToInt32(read.Rows[0]["eno"]);
amount = Convert.ToInt32(read.Rows[0]["amount"]);
party = Convert.ToInt32(read.Rows[0]["partyid"]);
partyname = read.Rows[0]["name"].ToString();
description = read.Rows[0]["dscrpt"].ToString();
date = read.Rows[0]["edate"].ToString();
}
return(success);
}
public order[] search(int key)
{
log = new TMS.db();
string query = "select * from selectsale1 where eno=" + key + " and etype='SALE'";
log.open();
DataTable read;
order[] s = new order[100];
read = log.searchquery(query);
log.close();
if (read.Rows.Count != 0)
{
saleno = Convert.ToInt32(read.Rows[0]["eno"].ToString());
type = read.Rows[0]["etype"].ToString();
totalamount = Convert.ToInt32(read.Rows[0]["tm"].ToString());
party = read.Rows[0]["name"].ToString();
date = read.Rows[0]["edate"].ToString();
desc = read.Rows[0]["dsc"].ToString();
row = 0;
for (int i = 0; i < read.Rows.Count; i++)
{
s[i] = new order();
s[i].kg = Convert.ToInt32(read.Rows[i]["kg"].ToString());
s[i].pc = Convert.ToInt32(read.Rows[i]["pc"].ToString());
s[i].bag = Convert.ToInt32(read.Rows[i]["bag"].ToString());
s[i].rate = Convert.ToInt32(read.Rows[i]["rate"].ToString());
s[i].amt = Convert.ToInt32(read.Rows[i]["amount"].ToString());
s[i].pname = read.Rows[i]["pname"].ToString();
s[i].code = read.Rows[i]["pcode"].ToString();
row++;
}
}
return(s);
}
public void search(string searchkey)
{
log = new TMS.db();
log.open();
int key;
DataTable read;
string query = "select id from partysearch where name = '" + searchkey + "'";
read = log.searchquery(query);
key = Convert.ToInt32(read.Rows[0][0].ToString());
query = "select * from party where id = " + key + "";
read = log.searchquery(query);
log.close();
pname = read.Rows[0]["pname"].ToString();
pcity = read.Rows[0]["city"].ToString();
pbalance = Convert.ToInt32(read.Rows[0]["balance"]);
paddress = read.Rows[0]["address"].ToString();
pcontact = read.Rows[0]["contact"].ToString();
}
public legdertable[] show(string startdate, string enddate, string pname)
{
legdertable[] l = new legdertable[1000];
int partyid = getpartyno(pname);
int prevbal = prevbalance(startdate, partyid);
string query = "select * from entry where edate between '" + startdate + "' and '" + enddate + "' and partyid = " + partyid + " order by edate";
log = new db();
log.open();
DataTable read = log.searchquery(query);
log.close();
totalcredit = 0;
totaldebit = 0;
balance = 0;
row = 0;
l[0] = new legdertable();
l[0].balance = Math.Abs(prevbal);
l[0].description = "Brought Forward";
l[0].type = "";
l[0].no = 0;
if (prevbal < 0)
{
l[0].CRDR = "DR";
}
else
{
l[0].CRDR = "CR";
}
l[0].formdate = startdate;
balance = prevbal;
if (read.Rows.Count != 0)
{
for (int i = 0; i < read.Rows.Count; i++)
{
l[i + 1] = new legdertable();
l[i + 1].no = Convert.ToInt32(read.Rows[i]["eno"]);
l[i + 1].description = read.Rows[i]["dscrpt"].ToString();
l[i + 1].formdate = read.Rows[i]["edate"].ToString();
l[i + 1].type = read.Rows[i]["etype"].ToString();
if (l[i + 1].type == "SALE" || l[i + 1].type == "CPV")
{
l[i + 1].credit = Convert.ToInt32(read.Rows[i]["amount"]);
balance += l[i + 1].credit;
totalcredit += l[i + 1].credit;
}
else
{
l[i + 1].debit = Convert.ToInt32(read.Rows[i]["amount"]);
balance -= l[i + 1].debit;
totaldebit += l[i + 1].debit;
}
if (balance >= 0)
{
l[i + 1].CRDR = "CR";
}
else
{
l[i + 1].CRDR = "DR";
}
l[i + 1].balance = Math.Abs(balance);
row++;
}
}
return(l);
}
