public Boolean InsertDiv_master(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Div_master (divid,divname) values(@divid,@divname)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
int newid = g.GetNextId("Div_master", "divid");
cmd.Parameters.AddWithValue("@divid", newid);
cmd.Parameters.AddWithValue("@divname", f["divname"]);
int ab = cmd.ExecuteNonQuery();
String Sql = "insert into autoidgen(id,cadtvoc,padtvoc,sadtvoc,radtvoc,adjvoc,cadtbpo,padtbpo,radtbpo,sadtbpo,divid,cash) values(@id,0,0,0,0,0,0,0,0,0,@divid,0)";
SqlCommand cmd1 = new SqlCommand(Sql, con);
cmd1.Parameters.AddWithValue("@id", g.GetNextId("autoidgen", "id"));
cmd1.Parameters.AddWithValue("@divid", newid);
int ab1 = cmd1.ExecuteNonQuery();
con.Close();
if (ab > 0 && ab1 > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean Insertvoucherdata(FormCollection f, int divid, int sectionid, int userid)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into accounttrans(id,trtype,accno,sectionid,divid,dramount,cramount,userid) values(@id,@trtype,@accno,@sectionid,@divid,@dramount,@cramount,@userid)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@id", g.GetNextId("accounttrans", "id"));
cmd.Parameters.AddWithValue("@trtype", f["trtype"]);
cmd.Parameters.AddWithValue("@accno", f["accno"]);
cmd.Parameters.AddWithValue("@sectionid", sectionid);
cmd.Parameters.AddWithValue("@divid", divid);
cmd.Parameters.AddWithValue("@dramount", f["dramount"]);
cmd.Parameters.AddWithValue("@cramount", f["cramount"]);
cmd.Parameters.AddWithValue("@userid", userid);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean InsertParty_master(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Party_master (partyid,partyname,divid,sectionid,Address,city,state,pincode,MobileNo,PhoneNo,GSTNo,TANNo,PANNo) values(@partyid,@partyname,@divid,@sectionid,@Address,@city,@state,@pincode,@MobileNo,@PhoneNo,@GSTNo,@TANNo,@PANNo)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@partyid", g.GetNextId("Party_master", "partyid"));
cmd.Parameters.AddWithValue("@partyname", f["partyname"]);
cmd.Parameters.AddWithValue("@divid", f["divid"]);
cmd.Parameters.AddWithValue("@sectionid", f["sectionid"]);
cmd.Parameters.AddWithValue("@Address", f["Address"]);
cmd.Parameters.AddWithValue("@city", f["city"]);
cmd.Parameters.AddWithValue("@state", f["state"]);
cmd.Parameters.AddWithValue("@pincode", f["pincode"]);
cmd.Parameters.AddWithValue("@MobileNo", f["MobileNo"]);
cmd.Parameters.AddWithValue("@PhoneNo", f["PhoneNo"]);
//cmd.Parameters.AddWithValue("@sectionid", f["MobileNo"]);
cmd.Parameters.AddWithValue("@GSTNo", f["GSTNo"]);
cmd.Parameters.AddWithValue("@TANNo", f["TANNo"]);
cmd.Parameters.AddWithValue("@PANNo", f["PANNo"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean InsertVoucher_master(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Voucher_master (id,voucherno,vodate,billtypeid,sectionid,divid,depoid,rvnumber,ponumber,invoiceno,userid,status,enteredon) values(@id,@voucherno,@vodate,@billtypeid,@sectionid,@divid,@depoid,@rvnumber,@ponumber,@invoiceno,@userid,@status,@enteredon)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@id", g.GetNextId("Voucher_master", "id"));
cmd.Parameters.AddWithValue("@voucherno", f["voucherno"]);
cmd.Parameters.AddWithValue("@vodate", f["vodate"]);
cmd.Parameters.AddWithValue("@billtypeid", f["billtypeid"]);
cmd.Parameters.AddWithValue("@sectionid", f["sectionid"]);
cmd.Parameters.AddWithValue("@divid", f["divid"]);
cmd.Parameters.AddWithValue("@depoid", f["depoid"]);
cmd.Parameters.AddWithValue("@rvnumber", f["rvnumber"]);
cmd.Parameters.AddWithValue("@ponumber", f["ponumber"]);
cmd.Parameters.AddWithValue("@invoiceno", f["invoiceno"]);
cmd.Parameters.AddWithValue("@userid", f["userid"]);
cmd.Parameters.AddWithValue("@status", f["status"]);
cmd.Parameters.AddWithValue("@enteredon", f["enteredon"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean InsertAccount_master(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Account_master (accountid,accountnumber,accountname,parentid,groupid,sectionid,allow) values(@accountid,@accountnumber,@accountname,@parentid,@groupid,@sectionid,@allow)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@accountid", g.GetNextId("Account_master", "accountid"));
cmd.Parameters.AddWithValue("@accountnumber", f["accountnumber"]);
cmd.Parameters.AddWithValue("@accountname", f["accountname"]);
cmd.Parameters.AddWithValue("@parentid", f["parentid"]);
cmd.Parameters.AddWithValue("@groupid", f["groupid"]);
cmd.Parameters.AddWithValue("@sectionid", f["sectionid"]);
cmd.Parameters.AddWithValue("@allow", f["allow"]);
//cmd.Parameters.AddWithValue("@active", f["active"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean InsertUsers(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Users (id,username,password,sectionid,divid,mobileno,usertypeid) values(@id,@username,@password,@sectionid,@divid,@mobileno,@usertypeid)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@id", g.GetNextId("Users", "id"));
cmd.Parameters.AddWithValue("@username", f["username"]);
cmd.Parameters.AddWithValue("@password", f["password"]);
cmd.Parameters.AddWithValue("@sectionid", f["sectionid"]);
cmd.Parameters.AddWithValue("@divid", f["divid"]);
cmd.Parameters.AddWithValue("@mobileno", f["mobileno"]);
cmd.Parameters.AddWithValue("@usertypeid", f["usertypeid"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean InsertBank_master(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Bank_master (bankid,bankname,accountnumber,bankaccno,chequestart,chequeend,divid,nextchequeno) values(@bankid,@bankname,@accountnumber,@bankaccno,@chequestart,@chequeend,@divid,@nextchequeno)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@bankid", g.GetNextId("Bank_master", "bankid"));
cmd.Parameters.AddWithValue("@bankname", f["bankname"]);
cmd.Parameters.AddWithValue("@accountnumber", f["accountnumber"]);
cmd.Parameters.AddWithValue("@bankaccno", f["bankaccno"]);
cmd.Parameters.AddWithValue("@chequestart", f["chequestart"]);
cmd.Parameters.AddWithValue("@chequeend", f["chequeend"]);
cmd.Parameters.AddWithValue("@divid", f["divid"]);
cmd.Parameters.AddWithValue("@nextchequeno", f["nextchequeno"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean InsertBilltype_master(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Billtype_master (billtypeid,billtypename,sectionid,divid) values(@billtypeid,@billtypename,@sectionid,@divid)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@billtypeid", g.GetNextId("Billtype_master", "billtypeid"));
cmd.Parameters.AddWithValue("@billtypename", f["billtypename"]);
cmd.Parameters.AddWithValue("@sectionid", f["sectionid"]);
cmd.Parameters.AddWithValue("@divid", f["divid"]);
// cmd.Parameters.AddWithValue("@active", f["active"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean InsertAccount_parent(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Account_parent (parentid,parentname) values(@parentid,@parentname)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@parentid", g.GetNextId("Account_parent", "parentid"));
cmd.Parameters.AddWithValue("@parentname", f["parentname"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean InsertSection_master(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Section_master (sectionid,sectionname) values(@sectionid,@sectionname)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@sectionid", g.GetNextId("Section_master", "sectionid"));
cmd.Parameters.AddWithValue("@sectionname", f["sectionname"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean InsertUser_types(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into User_types (usertypeid,usertypename) values(@usertypeid,@usertypename)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@usertypeid", g.GetNextId("User_types", "usertypeid"));
cmd.Parameters.AddWithValue("@usertypename", f["usertypename"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean InsertDepot_master(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Depot_master (depotid,depotname,divid) values(@depotid,@depotname,@divid)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@depotid", g.GetNextId("Depot_master", "depotid"));
cmd.Parameters.AddWithValue("@depotname", f["depotname"]);
cmd.Parameters.AddWithValue("@divid", f["divid"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
/** public Int32 GetOrderedRecordsCountbilltype(int btypeid, String OrderBy)
* {
*
* String Qry = "SELECT * FROM ( SELECT row_number() OVER (ORDER BY " + OrderBy + ") AS rownum, * FROM Billtype_details ) AS A Where billtypeid=" + billtypeid.ToString();
* return DBClass.GetAllRecords(Qry).Rows.Count;
* } **/
/** public Boolean InsertBilltype_details(FormCollection f)
* {
* GenHelper g = new GenHelper();
* String Sqltext = "insert into Billtype_details (id,billtypeid,accountnumber,type) values(@id,@billtypeid,@accountnumber,@type)";
* SqlConnection con = DBClass.mycon();
* SqlCommand cmd = new SqlCommand(Sqltext, con);
* cmd.Parameters.AddWithValue("@id", g.GetNextId("Billtype_details", "id"));
* cmd.Parameters.AddWithValue("@billtypeid", f["billtypeid"]);
* cmd.Parameters.AddWithValue("@accountnumber", f["accountnumber"]);
* cmd.Parameters.AddWithValue("@type", f["type"]);
*
* int ab = cmd.ExecuteNonQuery();
* con.Close();
* if (ab > 0) return true; else return false;
* } **/
public Boolean InsertBilltype_details(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Billtype_details (id,billtypeid,accountnumber,type) values(@id,@billtypeid,@accountnumber,@type)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@id", g.GetNextId("Billtype_details", "id"));
cmd.Parameters.AddWithValue("@billtypeid", f["billtypeid"]);
cmd.Parameters.AddWithValue("@accountnumber", f["accountnumber"]);
cmd.Parameters.AddWithValue("@type", f["type"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean InsertUser_menus(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into User_menus (menuid,menuname,menuurl,parentid) values(@menuid,@menuname,@menuurl,@parentid)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@menuid", g.GetNextId("User_menus", "menuid"));
cmd.Parameters.AddWithValue("@menuname", f["menuname"]);
cmd.Parameters.AddWithValue("@menuurl", f["menuurl"]);
cmd.Parameters.AddWithValue("@parentid", f["parentid"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
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 UpdateMyVoucher(FormCollection f, DataTable udt, DataTable cdt, ref string trid, DateTime finst, DateTime finend, int divisionid, String divname, int secid, int uid)
{
SqlTransaction tr = null;
GenHelper g = new GenHelper();
Int32 vid = g.GetNextId("voucher_master", "id");
Int32 newid = g.GetNextId("accounttrans", "id");
Int32 prtytrid = g.GetNextId("party_ledger", "id");
SqlConnection con = DBClass.mycon();
tr = con.BeginTransaction();
try
{
String Q1 = "delete from voucher_master where voucherno='" + f["voucherno"] + "'";
String Q2 = "delete from accounttrans where voucherno='" + f["voucherno"] + "'";
String Q3 = "delete from party_ledger where voucherno='" + f["voucherno"] + "'";
SqlCommand c1 = new SqlCommand(Q1, con);
c1.Transaction = tr;
c1.ExecuteNonQuery();
SqlCommand c2 = new SqlCommand(Q2, con);
c2.Transaction = tr;
c2.ExecuteNonQuery();
SqlCommand c3 = new SqlCommand(Q3, con);
c3.Transaction = tr;
c3.ExecuteNonQuery();
String Sql = "insert into voucher_master (id,voucherno,vodate,billtypeid,rvnumber,ponumber,invoiceno,sectionid,divid,userid,enteredon,partyid,trtypeid,rvdate, podate,invdate) ";
Sql += "values(@id,@voucherno,@vodate,@billtypeid,@rvnumber,@ponumber,@invoiceno,@sectionid,@divid,@userid,@enteredon,@partyid,@trtypeid,@rvdate, @podate,@invdate)";
//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", vid);
cmd.Parameters.AddWithValue("@voucherno", f["voucherno"]);
cmd.Parameters.AddWithValue("@vodate", g.SqlDate(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", g.SqlDate(f["rvdate"]));
cmd.Parameters.AddWithValue("@podate", g.SqlDate(f["podate"]));
cmd.Parameters.AddWithValue("@invdate", g.SqlDate(f["invdate"]));
cmd.Parameters.AddWithValue("@partyid", f["partyid"]);
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();
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", f["voucherno"]);
cmdtr.Parameters.AddWithValue("@accno", cdt.Rows[i]["accountnumber"].ToString());
cmdtr.Parameters.AddWithValue("@trdate", g.SqlDate(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);
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", f["voucherno"]);
cmdprty.Transaction = tr;
cmdprty.ExecuteNonQuery();
newid++;
}
}
tr.Commit();
return(true);
}
catch (Exception ex)
{
g.logerror(ex);
tr.Rollback();
return(false);
}
finally
{
con.Close();
}
}
public Boolean SavePer(String PerStr, int groupid)
{
GenHelper g = new GenHelper();
String[] lst = PerStr.Substring(0, PerStr.Length - 1).Split('|');
String Qry; String Qry1;
if (lst.Length > 0)
{
Qry1 = "delete from user_permissions where usertypeid=" + groupid.ToString();
DBClass.NonQuery(Qry1);
for (int i = 0; i < lst.Length; i++)
{
Qry = "insert into User_permissions(id,usertypeid,menuid) values(" + g.GetNextId("user_permissions", "id").ToString() + "," + groupid.ToString() + "," + lst[i] + ")";
if (!DBClass.NonQuery(Qry))
{
return(false);
}
}
return(true);
}
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 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 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();
}
}
