public ActionResult TrialBal(FormCollection fc)
{
GenHelper g = new GenHelper();
DataSet ds = new DataSet();
String repname = "TrialBalance";
String Tblname = "mytrialbal";
DBClass.NonQuery("drop table " + Tblname);
Reports rp = new Reports();
String Mon = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(Convert.ToInt32(fc["mon"]));
String Stdate = fc["year"] + "-" + fc["mon"] + "-" + "01";
DateTime sd = DateTime.Parse(Stdate);
DateTime ed = sd.AddMonths(1); ed = ed.AddDays(-1);
String edate = g.SqlDate(ed.ToString());
DataTable trbal = rp.PrepareTraialBal(Tblname, Stdate, edate);
String SumQry = "select sum(receipts) as TotalCredits,sum(payments) as TotalDebits from " + Tblname;
DataTable sumdt = DBClass.GetData(SumQry);
ds.Tables.Add(trbal); ds.Tables.Add(sumdt);
float[] cols = new float[] { 60f, 180f, 65f, 65f, 65f, 65f };
String path = Server.MapPath("/pdfs/trialbalance.pdf");
String[] rephead = new String[] { "N.EW.K.R.T.C Hubli", "Trial Balance For the Month of " + Mon + " " + fc["year"] };
String[] tblhead = new string[] { "Account Number", "Account Name", "Opening Balance", "Receipts", "Payments", "Closing Balance" };
pdf ph = new pdf(ds, cols, rephead, tblhead, path, repname, Tblname);
ph.GenerateReport();
//String DropQry = "drop table " + Tblname;
//DBClass.NonQuery(DropQry);
return(File(path, "application/pdf"));
}
public ActionResult SaveBpo()
{
GenHelper g = new GenHelper();
Users user = new Users();
DataTable userdt = user.GetUserRecord(Convert.ToInt32(Session["Userid"]));
DateTime fst = (DateTime)Session["finstart"];
DateTime fend = (DateTime)Session["finend"];
Int32 divid = Convert.ToInt32(Session["divid"]);
String divname = Session["divname"].ToString();
Int32 sectionid = Convert.ToInt32(Session["sectionid"]);
CadtBpo cb = new CadtBpo();
String test = Request.QueryString["chkval"];
String bpdate = Request.QueryString["bpodate"];
String bpodate = g.SqlDate(bpdate);
if (cb.SaveBpo(test, bpodate, fst, fend, divid, divname, sectionid, Convert.ToInt32(userdt.Rows[0]["id"])))
{
return(Content("Transaction Successfull"));
}
else
{
return(Content("Transaction Un Succeccful. Try Again Later"));
}
}
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 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 ActionResult printrp(FormCollection fc)
{
DataSet ds = new DataSet();
String repname = "ReceiptPayment";
Reports rs = new Reports(); GenHelper g = new GenHelper();
float[] cols = new float[] { 40f, 220f, 60f, 60f };
String path = Server.MapPath("/pdfs/receiptpayments.pdf");
String[] rephead = new String[] { "N.EW.K.R.T.C Hubli", "Receipts & Payments From " + fc["stdate"] + " To " + fc["edate"] };
String[] tblhead = new string[] { "Account Number", "Account Name", "Receipts", "Payments" };
DataTable dt = rs.GetRpData(fc["stdate"], fc["edate"]);
ds.Tables.Add(dt);
DataTable dt1 = DBClass.GetData("select sum(cramount) receipts,sum(dramount) payments from accounttrans where trdate>='" + g.SqlDate(fc["stdate"]) + "' and trdate<='" + g.SqlDate(fc["edate"]) + "' and divid=1");
ds.Tables.Add(dt1);
pdf ph = new pdf(ds, cols, rephead, tblhead, path, repname);
ph.GenerateReport();
return(File(path, "application/pdf"));
}
public DataTable GetRpData(String sdate, String edate)
{
GenHelper g = new GenHelper();
String Qry = "SELECT [at].accountnumber, am.accountname,Sum([at].dramount) AS Receipts,Sum([at].cramount) AS Payments FROM dbo.account_master AS am";
Qry += " INNER JOIN dbo.accounttrans AS [at] ON am.accountnumber = [at].accountnumber WHERE [at].divid = 1 and trdate>='" + g.SqlDate(sdate) + "' and trdate<='" + g.SqlDate(edate) + "' GROUP BY [at].accountnumber,am.accountname";
return(DBClass.GetData(Qry));
}
