public Boolean SaveBpo(String bpos, String datebpo, DateTime fst, DateTime fend, Int32 divid, String divname, Int32 sectionid, Int32 uid)
{
SqlTransaction tr = null;
GenHelper g = new GenHelper();
String[] SelectedBpos = bpos.Split('|');
Int32 id = g.GetNextId("bpo_master", "id");
int did = g.GetNextId("bpo_trans", "id");
String bponumber = g.GetNewVoucherNo(this.transtype, fst, fend, divid, divname);
SqlConnection con = DBClass.mycon();
tr = con.BeginTransaction();
try
{
String Sql = "insert into bpo_master(id,bponumber,bpotype,bpodate,sectionid,divid,userid,enteredon,typeofbpo)";
Sql += " values(@id,@bponumber,@bpotype,@bpodate,@sectionid,@divid,@userid,@enteredon,@typeofbpo)";
SqlCommand cmd = new SqlCommand(Sql, con);
cmd.Parameters.AddWithValue("@id", g.GetNextId("bpo_master", "id"));
cmd.Parameters.AddWithValue("@bponumber", bponumber);
cmd.Parameters.AddWithValue("@bpotype", this.transtype);
cmd.Parameters.AddWithValue("@bpodate", datebpo);
cmd.Parameters.AddWithValue("@sectionid", sectionid);
cmd.Parameters.AddWithValue("@userid", uid);
cmd.Parameters.AddWithValue("@divid", divid);
cmd.Parameters.AddWithValue("@enteredon", DateTime.Today);
cmd.Parameters.AddWithValue("@typeofbpo", this.bpotype);
cmd.Transaction = tr;
cmd.ExecuteNonQuery();
String Sql1;
SqlCommand Cmd1;
for (int i = 0; i < SelectedBpos.Length - 1; i++)
{
DataTable dt = DBClass.GetData("select * from voucher_master where id=" + SelectedBpos[i].ToString());
Sql1 = "insert into bpo_trans(id,bponumber,accountnumber,partyid,sectionid,userid,enteredon,dramount,status,voucherno)";
Sql1 += " values(@id,@bponumber,@accountnumber,@partyid,@sectionid,@userid,@enteredon,@dramount,@status,@vcno)";
SqlCommand cmd1 = new SqlCommand(Sql1, con);
cmd1.Parameters.AddWithValue("@id", did);
cmd1.Parameters.AddWithValue("@bponumber", bponumber);
cmd1.Parameters.AddWithValue("@accountnumber", dt.Rows[0]["accountnumber"]);
cmd1.Parameters.AddWithValue("@partyid", dt.Rows[0]["partyid"]);
cmd1.Parameters.AddWithValue("@sectionid", sectionid);
cmd1.Parameters.AddWithValue("@userid", uid);
cmd1.Parameters.AddWithValue("@enteredon", DateTime.Today);
cmd1.Parameters.AddWithValue("@dramount", dt.Rows[0]["dueamount"]);
cmd1.Parameters.AddWithValue("@vcno", dt.Rows[0]["voucherno"]);
cmd1.Parameters.AddWithValue("@status", 0);
cmd1.Transaction = tr;
cmd1.ExecuteNonQuery();
did++;
SqlCommand cmd2;
String Sql2 = "update voucher_master set status=@status where id=" + SelectedBpos[i].ToString();
cmd2 = new SqlCommand(Sql2, con);
cmd2.Parameters.AddWithValue("@status", 1);
cmd2.Transaction = tr;
cmd2.ExecuteNonQuery();
}
tr.Commit();
return(true);
}
catch (Exception ex) {
g.logerror(ex);
tr.Rollback();
return(false);
}
}
public Boolean SaveMyVoucher(FormCollection f, DataTable udt, DataTable cdt, ref string trid, DateTime finst, DateTime finend, int divisionid, String divname, int secid, int uid, Double payamt, String accno)
{
/* begin transaction - generate voucher no - insert record into table voucher master -
* insert record into transaction table - insert record into party ledger if party selected
* update table autoidgen with the new voucher no
*/
SqlTransaction tr = null;
GenHelper g = new GenHelper();
trid = g.GetNewVoucherNo(transtype, finst, finend, divisionid, divname);
SqlConnection con = DBClass.mycon();
tr = con.BeginTransaction();
try
{
String Sql = "insert into voucher_master (id,voucherno,vodate,billtypeid,rvnumber,ponumber,invoiceno,sectionid,divid,userid,enteredon,partyid,trtypeid,rvdate, podate,invdate,dueamount,accountnumber) ";
Sql += "values(@id,@voucherno,@vodate,@billtypeid,@rvnumber,@ponumber,@invoiceno,@sectionid,@divid,@userid,@enteredon,@partyid,@trtypeid,@rvdate, @podate,@invdate,@topayamt,@accountnumber)";
//SqlConnection mcon = DBClass.mycon();
// String Sql = "insert into voucher_master (id,voucherno,vodate,billtypeid,partyid)";
//Sql += " values(@id,@voucherno,@vodate,@billtypeid,@partyid)";
SqlCommand cmd = new SqlCommand(Sql, con);
cmd.Parameters.AddWithValue("@id", g.GetNextId("voucher_master", "id"));
cmd.Parameters.AddWithValue("@voucherno", trid);
cmd.Parameters.AddWithValue("@vodate", f["vdate"]);
cmd.Parameters.AddWithValue("@billtypeid", f["billtypeid"]);
cmd.Parameters.AddWithValue("@rvnumber", f["rvno"]);
cmd.Parameters.AddWithValue("@ponumber", f["pono"]);
cmd.Parameters.AddWithValue("@invoiceno", f["invoice"]);
cmd.Parameters.AddWithValue("@rvdate", f["rvdate"]);
cmd.Parameters.AddWithValue("@podate", f["podate"]);
cmd.Parameters.AddWithValue("@invdate", f["invdate"]);
cmd.Parameters.AddWithValue("@partyid", f["partyid"]);
cmd.Parameters.AddWithValue("@accountnumber", accno);
cmd.Parameters.AddWithValue("@topayamt", payamt.ToString());
cmd.Parameters.AddWithValue("@trtypeid", this.transtype);
cmd.Parameters.AddWithValue("@sectionid", secid);
cmd.Parameters.AddWithValue("@userid", uid);
cmd.Parameters.AddWithValue("@divid", divisionid);
cmd.Parameters.AddWithValue("@enteredon", DateTime.Today);
cmd.Transaction = tr;
int a = cmd.ExecuteNonQuery();
Int32 newid = g.GetNextId("accounttrans", "id");
String Trqry = "insert into accounttrans (id,trtype,voucherno,accno,trdate,narration,dramount,cramount,partyid,divid,sectionid,userid,enteredon) ";
Trqry += " values(@id,@trtype,@voucherno,@accno,@trdate,@narration,@dramount,@cramount,@partyid,@divid,@sectionid,@userid,@enteredon)";
for (int i = 0; i < cdt.Rows.Count; i++)
{
if (Convert.ToInt32(cdt.Rows[i]["cramount"]) > 0 || Convert.ToInt32(cdt.Rows[i]["dramount"]) > 0)
{
SqlCommand cmdtr = new SqlCommand(Trqry, con);
cmdtr.Parameters.AddWithValue("@id", newid);
cmdtr.Parameters.AddWithValue("@trtype", this.transtype);
//cmdtr.Parameters.AddWithValue(@transid,
cmdtr.Parameters.AddWithValue("@voucherno", trid);
cmdtr.Parameters.AddWithValue("@accno", cdt.Rows[i]["accountnumber"].ToString());
cmdtr.Parameters.AddWithValue("@trdate", f["vdate"]);
cmdtr.Parameters.AddWithValue("@narration", "My Narration");
cmdtr.Parameters.AddWithValue("@dramount", cdt.Rows[i]["dramount"].ToString());
cmdtr.Parameters.AddWithValue("@cramount", cdt.Rows[i]["cramount"].ToString());
cmdtr.Parameters.AddWithValue("@partyid", f["partyid"]);
cmdtr.Parameters.AddWithValue("@divid", divisionid);
cmdtr.Parameters.AddWithValue("@sectionid", secid);
cmdtr.Parameters.AddWithValue("@userid", userid);
cmdtr.Parameters.AddWithValue("@enteredon", DateTime.Today);
//cmdtr.Parameters.AddWithValue("@depoid",
cmdtr.Transaction = tr;
cmdtr.ExecuteNonQuery();
newid++;
}
}
String PrtySql = "insert into party_ledger (partyid,id,divid,dbamount,cramount,trdate,narration,voucherno) ";
PrtySql += " values(@partyid,@id,@divid,@dbamount,@cramount,@trdate,@narration,@voucherno)";
SqlCommand cmdprty = new SqlCommand(PrtySql, con);
Int32 prtytrid = g.GetNextId("party_ledger", "id");
for (int i = 0; i < cdt.Rows.Count; i++)
{
if (Convert.ToInt32(cdt.Rows[i]["dramount"]) > 0 && Convert.ToInt32(cdt.Rows[i]["cramount"]) == 0)
{
cmdprty.Parameters.AddWithValue("@partyid", f["partyid"]);
cmdprty.Parameters.AddWithValue("@id", prtytrid);
cmdprty.Parameters.AddWithValue("divid", divisionid);
cmdprty.Parameters.AddWithValue("@dbamount", cdt.Rows[i]["dramount"].ToString());
cmdprty.Parameters.AddWithValue("@cramount", cdt.Rows[i]["cramount"].ToString());
cmdprty.Parameters.AddWithValue("@trdate", g.SqlDate(f["vdate"]));
cmdprty.Parameters.AddWithValue("@narration", "To Narration");
cmdprty.Parameters.AddWithValue("@voucherno", trid);
cmdprty.Transaction = tr;
cmdprty.ExecuteNonQuery();
newid++;
}
}
String AutoQry = "update autoidgen set radtvoc=radtvoc+1 where divid=" + divisionid.ToString();
SqlCommand cmdauto = new SqlCommand(AutoQry, con);
cmdauto.Transaction = tr;
cmdauto.ExecuteNonQuery();
tr.Commit();
return(true);
}
catch (Exception ex)
{
g.logerror(ex);
tr.Rollback();
return(false);
}
finally
{
con.Close();
}
}
public Boolean savevoucherdata(FormCollection f, DataTable userdt, DataTable Cardt, ref String trid, DateTime fst, DateTime fend, int divid, String divname, int userid, int sectionid, double payamt)
{
SqlTransaction tr = null;
GenHelper g = new GenHelper();
trid = g.GetNewVoucherNo(transtype, fst, fend, divid, divname);
SqlConnection con = DBClass.mycon();
tr = con.BeginTransaction();
try
{
String Sql = "insert into voucher_master(id,voucherno,vodate,billtypeid,divid,rvnumber,ponumber,invoiceno,userid,partyid,enteredon,trtypeid,sectionid,rvdate,podate,invdate)";
Sql += " values(@id,@voucherno,@vodate,@billtypeid,@divid,@rvnumber,@ponumber,@invoiceno,@userid,@partyid,@enteredon,@trtypeid,@sectionid,@rvdate,@podate,@invdate)";
SqlCommand cmd = new SqlCommand(Sql, con);
cmd.Parameters.AddWithValue("@id", g.GetNextId("voucher_master", "id"));
cmd.Parameters.AddWithValue("@voucherno", trid);
cmd.Parameters.AddWithValue("@vodate", f["vdate"]);
cmd.Parameters.AddWithValue("@billtypeid", f["billtypeid"]);
cmd.Parameters.AddWithValue("@divid", divid);
cmd.Parameters.AddWithValue("@rvnumber", f["rvno"]);
cmd.Parameters.AddWithValue("@ponumber", f["pono"]);
cmd.Parameters.AddWithValue("@invoiceno", f["invoice"]);
cmd.Parameters.AddWithValue("@userid", userid);
cmd.Parameters.AddWithValue("@partyid", f["partyid"]);
cmd.Parameters.AddWithValue("@trtypeid", this.transtype);
cmd.Parameters.AddWithValue("@sectionid", sectionid);
cmd.Parameters.AddWithValue("@rvdate", f["rvdate"]);
cmd.Parameters.AddWithValue("@podate", f["podate"]);
cmd.Parameters.AddWithValue("@invdate", f["invoicedate"]);
cmd.Parameters.AddWithValue("@enteredon", DateTime.Today);
cmd.Transaction = tr;
int a = cmd.ExecuteNonQuery();
Int32 newid = g.GetNextId("accounttrans", "id");
String Trqry = "insert into accounttrans(id,trtype,voucherno,accno,trdate,dramount,cramount,partyid,divid,sectionid,userid,enteredon)";
Trqry += " values(@id,@trtype,@voucherno,@accno,@trdate,@dramount,@cramount,@partyid,@divid,@sectionid,@userid,@enteredon)";
for (int i = 0; i < Cardt.Rows.Count; i++)
{
if (Convert.ToInt32(Cardt.Rows[i]["dramount"]) > 0 || Convert.ToInt32(Cardt.Rows[i]["cramount"]) > 0)
{
SqlCommand cmd1 = new SqlCommand(Trqry, con);
cmd1.Parameters.AddWithValue("@id", newid);
cmd1.Parameters.AddWithValue("@trtype", this.transtype);
cmd1.Parameters.AddWithValue("@voucherno", trid);
cmd1.Parameters.AddWithValue("@accno", Cardt.Rows[i]["accountnumber"]);
cmd1.Parameters.AddWithValue("@trdate", f["vdate"]);
cmd1.Parameters.AddWithValue("@dramount", Cardt.Rows[i]["dramount"]);
cmd1.Parameters.AddWithValue("@cramount", Cardt.Rows[i]["cramount"]);
cmd1.Parameters.AddWithValue("@partyid", f["partyid"]);
cmd1.Parameters.AddWithValue("@divid", divid);
cmd1.Parameters.AddWithValue("@sectionid", sectionid);
cmd1.Parameters.AddWithValue("@userid", userid);
cmd1.Parameters.AddWithValue("@enteredon", DateTime.Now);
cmd1.Transaction = tr;
int a1 = cmd1.ExecuteNonQuery();
newid++;
}
}
Int32 newplid = g.GetNextId("party_ledger", "id");
String Plqry = "insert into party_ledger(id,partyid,divid,dbamount,trdate,voucherno)";
Plqry += " values(@id,@partyid,@divid,@dbamount,@trdate,@voucherno)";
SqlCommand cmdpl = new SqlCommand(Plqry, con);
cmdpl.Parameters.AddWithValue("@id", newplid);
cmdpl.Parameters.AddWithValue("@partyid", f["partyid"]);
cmdpl.Parameters.AddWithValue("@divid", divid);
cmdpl.Parameters.AddWithValue("@dbamount", payamt);
cmdpl.Parameters.AddWithValue("@trdate", f["vdate"]);
cmdpl.Parameters.AddWithValue("@voucherno", trid);
cmdpl.Transaction = tr;
int a2 = cmdpl.ExecuteNonQuery();
String upautoid = "update autoidgen set cadtvoc=cadtvoc+1 where divid=" + divid;
SqlCommand cmdupd = new SqlCommand(upautoid, con);
cmdupd.Transaction = tr;
int a4 = cmdupd.ExecuteNonQuery();
tr.Commit();
return(true);
}
catch (Exception e)
{
tr.Rollback();
return(false);
}
finally
{
}
}
public Boolean SaveCash(FormCollection f, DataTable userdt, DataTable Cardt, DateTime fst, DateTime fend, int sectionid, int divid, String divname, int userid, ref String trid)
{
/* Steps for saving Cash Entry
* Generate new cash Trans id
* Save Cash Master Record
* Save Cash Trans Record
* Add Transaction to Transaction table
* Find Bpo Entry And Update The Record Status and amount paid
* Find Party in Party Master Post Debit Entry for that bpo
* update autoid generation table
*/
SqlTransaction tr = null;
GenHelper g = new GenHelper();
Int32 id = g.GetNextId("cash_master", "id");
Int32 cashtrid = g.GetNextId("cash_trans", "id");
String CashNo = g.GetNewVoucherNo(transtype, fst, fend, divid, divname);
trid = CashNo;
SqlConnection con = DBClass.mycon();
tr = con.BeginTransaction();
try
{
String Qry = "insert into cash_master(id,cashid,cashdate,status) values(@cid,@cashid,@cashdate,@status)";
SqlCommand cmd = new SqlCommand(Qry, con);
cmd.Parameters.AddWithValue("@cid", id);
cmd.Parameters.AddWithValue("@cashid", CashNo);
cmd.Parameters.AddWithValue("@cashdate", DateTime.Today);
cmd.Parameters.AddWithValue("@status", 0);
cmd.Transaction = tr;
cmd.ExecuteNonQuery();
String Sql = "insert into cash_trans(id,cashid,bponumber,paidamt,chequeno,accountnumber,chequedate,paymenttype) ";
//Sql += "values(" + cashtrid.ToString() + ",'" + CashNo + "','" + Cardt.Rows[i]["bponumber"] + "'," + Cardt.Rows[i]["paidamt"] + ",'";
//Sql += Cardt.Rows[i]["chequeno"] + "','" + Cardt.Rows[i]["accountnumber"] + "','" + Cardt.Rows[i]["chequedate"] + "'," + Cardt.Rows[i]["paytype"].ToString()+")";
Sql += " values(@cid,@cashid,@bponumber,@paidamt,@chequeno,@accountnumber,@chequedate,@paymenttype)";
for (int i = 0; i < Cardt.Rows.Count; i++)
{
if (Convert.ToDouble(Cardt.Rows[i]["paidamt"]) > 0)
{
SqlCommand trcmd = new SqlCommand(Sql, con);
trcmd.Parameters.AddWithValue("@cid", cashtrid.ToString());
trcmd.Parameters.AddWithValue("@cashid", CashNo);
trcmd.Parameters.AddWithValue("@bponumber", Cardt.Rows[i]["bponumber"]);
trcmd.Parameters.AddWithValue("@paidamt", Cardt.Rows[i]["paidamt"]);
trcmd.Parameters.AddWithValue("@chequeno", Cardt.Rows[i]["chequeno"]);
trcmd.Parameters.AddWithValue("@accountnumber", Cardt.Rows[i]["accountnumber"]);
trcmd.Parameters.AddWithValue("@chequedate", Cardt.Rows[i]["chequedate"]);
trcmd.Parameters.AddWithValue("@paymenttype", Cardt.Rows[i]["paytype"].ToString());
trcmd.Transaction = tr;
trcmd.ExecuteNonQuery();
cashtrid++;
}
}
/* find Bpo and update the status */
for (int i = 0; i < Cardt.Rows.Count; i++)
{
String BpoSql = "update bpo_trans set paidamt=paidamt+@paidamt,status=@status where bponumber='" + Convert.ToString(Cardt.Rows[i]["bponumber"]) + "'";
int bpostatus = SetBpoStatus(Convert.ToDouble(Cardt.Rows[i]["paidamt"]), Convert.ToString(Cardt.Rows[i]["bponumber"]), divid);
SqlCommand cmdbpo = new SqlCommand(BpoSql, con);
cmdbpo.Parameters.AddWithValue("@paidamt", Cardt.Rows[i]["paidamt"]);
cmdbpo.Parameters.AddWithValue("@status", bpostatus);
cmdbpo.Transaction = tr;
cmdbpo.ExecuteNonQuery();
}
/* party postings */
for (int i = 0; i < Cardt.Rows.Count; i++)
{
Int32 pid = g.GetNextId("party_ledger", "id");
String PrtSql = "insert into party_ledger(id,partyid,divid,dbamount,cramount,trdate,voucherno,cashid,bponumber)";
PrtSql += "values(@id,@partyid,@divid,@dbamount,@cramount,@trdate,@voucherno,@cashid,@bponumber)";
SqlCommand cmdprt = new SqlCommand(PrtSql, con);
cmdprt.Parameters.AddWithValue("@id", pid);
cmdprt.Parameters.AddWithValue("@partyid", Cardt.Rows[i]["partyid"]);
cmdprt.Parameters.AddWithValue("@divid", divid);
cmdprt.Parameters.AddWithValue("@dbamount", Cardt.Rows[i]["paidamt"]);
cmdprt.Parameters.AddWithValue("@cramount", 0);
cmdprt.Parameters.AddWithValue("@voucherno", Cardt.Rows[i]["voucherno"]);
cmdprt.Parameters.AddWithValue("@bponumber", Cardt.Rows[i]["bponumber"]);
cmdprt.Parameters.AddWithValue("@cashid", cashtrid);
cmdprt.Parameters.AddWithValue("@trdate", Cardt.Rows[i]["chequedate"]);
cmdprt.Transaction = tr;
cmdprt.ExecuteNonQuery();
}
Int32 newid = g.GetNextId("accounttrans", "id");
String Trqry = "insert into accounttrans (id,trtype,voucherno,accountnumber,trdate,narration,dramount,cramount,partyid,divid,sectionid,userid,enteredon) ";
Trqry += " values(@id,@trtype,@voucherno,@accno,@trdate,@narration,@dramount,@cramount,@partyid,@divid,@sectionid,@userid,@enteredon)";
for (int i = 0; i < Cardt.Rows.Count; i++)
{
if (Convert.ToInt32(Cardt.Rows[i]["paidamt"]) > 0)
{
SqlCommand cmdtr = new SqlCommand(Trqry, con);
cmdtr.Parameters.AddWithValue("@id", newid);
cmdtr.Parameters.AddWithValue("@trtype", this.transtype);
//cmdtr.Parameters.AddWithValue(@transid,
cmdtr.Parameters.AddWithValue("@voucherno", Cardt.Rows[i]["voucherno"]);
cmdtr.Parameters.AddWithValue("@accno", Cardt.Rows[i]["accountnumber"].ToString());
cmdtr.Parameters.AddWithValue("@trdate", Cardt.Rows[i]["chequedate"]);
cmdtr.Parameters.AddWithValue("@narration", "My Narration");
cmdtr.Parameters.AddWithValue("@dramount", Cardt.Rows[i]["paidamt"].ToString());
cmdtr.Parameters.AddWithValue("@cramount", 0);
cmdtr.Parameters.AddWithValue("@partyid", Cardt.Rows[i]["partyid"]);
cmdtr.Parameters.AddWithValue("@divid", divid);
cmdtr.Parameters.AddWithValue("@sectionid", sectionid);
cmdtr.Parameters.AddWithValue("@userid", userid);
cmdtr.Parameters.AddWithValue("@enteredon", DateTime.Today);
//cmdtr.Parameters.AddWithValue("@depoid",
cmdtr.Transaction = tr;
cmdtr.ExecuteNonQuery();
newid++;
SqlCommand cmdtr1 = new SqlCommand(Trqry, con);
cmdtr1.Parameters.AddWithValue("@id", newid);
cmdtr1.Parameters.AddWithValue("@trtype", this.transtype);
//cmdtr.Parameters.AddWithValue(@transid,
cmdtr1.Parameters.AddWithValue("@voucherno", Cardt.Rows[i]["voucherno"]);
cmdtr1.Parameters.AddWithValue("@accno", Cardt.Rows[i]["accountno"].ToString());
cmdtr1.Parameters.AddWithValue("@trdate", Cardt.Rows[i]["chequedate"]);
cmdtr1.Parameters.AddWithValue("@narration", "My Narration");
cmdtr1.Parameters.AddWithValue("@dramount", 0);
cmdtr1.Parameters.AddWithValue("@cramount", Cardt.Rows[i]["paidamt"].ToString());
cmdtr1.Parameters.AddWithValue("@partyid", Cardt.Rows[i]["partyid"]);
cmdtr1.Parameters.AddWithValue("@divid", divid);
cmdtr1.Parameters.AddWithValue("@sectionid", sectionid);
cmdtr1.Parameters.AddWithValue("@userid", userid);
cmdtr1.Parameters.AddWithValue("@enteredon", DateTime.Today);
//cmdtr.Parameters.AddWithValue("@depoid",
cmdtr1.Transaction = tr;
cmdtr1.ExecuteNonQuery();
}
}
String AutoQry = "update autoidgen set cash=cash+1 where divid=" + divid.ToString();
SqlCommand cmdauto = new SqlCommand(AutoQry, con);
cmdauto.Transaction = tr;
cmdauto.ExecuteNonQuery();
tr.Commit();
return(true);
}
catch (Exception ex)
{
g.logerror(ex);
tr.Rollback();
return(false);
}
finally
{
con.Close();
}
}
