public decimal GetMonthlyBill(BillCollectionVM _obj)
{
try
{
DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("id", typeof(int)));
foreach (var id in _obj.billDetailsIds)
{
tvp.Rows.Add(id);
}
decimal result = 0;
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("gsp_getInternetMonthlyBillByCollectionId", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@billCollectionDetailIds", tvp);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result = Convert.ToDecimal(rdr["totalMonthlyBill"]);
}
con.Close();
}
return(result);
}
catch (Exception)
{
throw;
}
}
internal string DishMonthlyBillDelete(BillCollectionVM _obj)
{
string result = "";
DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("id", typeof(int)));
foreach (var id in _obj.billDetailsIds)
{
tvp.Rows.Add(id);
}
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("dsp_DishMonthlyBillDelete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@cid", _obj.cid);
cmd.Parameters.AddWithValue("@billCollectionDetailIds", tvp);
cmd.Parameters.AddWithValue("@createdBy", _obj.createdBy);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result = rdr["Result"].ToString();
}
con.Close();
}
return(result);
}
public JsonResult GetMonthlyBillByDetailIds(BillCollectionVM _obj)
{
try
{
decimal result = _internetBillCollectionDA.GetMonthlyBill(_obj);
return(Json(new { success = true, data = result }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
throw ex;
}
}
public JsonResult InsertBillCollection(BillCollectionVM _obj)
{
try
{
_obj.createdBy = Convert.ToInt32(Session["userId"]);
_obj.collectionDate = DateTime.ParseExact(_obj.collectedDateString, "dd/MM/yyyy", null);
string result = _internetBillCollectionDA.InsertBillCollection(_obj);
return(Json(new { success = true, message = "Data Saved. Reference Number is: " + result }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
if (ex.Message.Contains("Violation of UNIQUE KEY constraint"))
{
return(Json(new { success = false, message = "Dupliacte Page/Serial No" }, JsonRequestBehavior.AllowGet));
}
return(Json(new { success = false, message = ex.Message }, JsonRequestBehavior.AllowGet));
}
}
internal string DishCustomerDelete(BillCollectionVM _obj)
{
string result = "";
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("isp_DishCustomerDelete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@cid", _obj.cid);
cmd.Parameters.AddWithValue("@createdBy", _obj.createdBy);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result = rdr["Result"].ToString();
}
con.Close();
}
return(result);
}
internal int CheckPassword(BillCollectionVM _obj, string PageName)
{
int result = 0;
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("isp_GetPassword", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PageName", PageName);
cmd.Parameters.AddWithValue("@Password", _obj.Password);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result = int.Parse(rdr["Status"].ToString());
}
con.Close();
}
return(result);
}
public string InsertBillCollection(BillCollectionVM _obj)
{
string result = "";
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("isp_InternetCustomerBillCollection", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@cid", _obj.cid);
cmd.Parameters.AddWithValue("@connFee", _obj.connFee);
cmd.Parameters.AddWithValue("@reConnFee", _obj.reConnFee);
cmd.Parameters.AddWithValue("@othersAmount", _obj.othersAmount);
cmd.Parameters.AddWithValue("@monthlyFee", _obj.monthlyFee);
cmd.Parameters.AddWithValue("@fromMonth", _obj.fromMonth);
cmd.Parameters.AddWithValue("@toMonth", _obj.toMonth);
cmd.Parameters.AddWithValue("@shiftingCharge", _obj.shiftingCharge);
cmd.Parameters.AddWithValue("@description", _obj.description);
cmd.Parameters.AddWithValue("@netAmount", _obj.netAmount);
cmd.Parameters.AddWithValue("@rcvAmount", _obj.rcvAmount);
cmd.Parameters.AddWithValue("@discount", _obj.discount);
cmd.Parameters.AddWithValue("@adjustAdvance", _obj.adjustAdvance);
cmd.Parameters.AddWithValue("@customerSL", _obj.customerSL);
cmd.Parameters.AddWithValue("@pageNo", _obj.pageNo);
cmd.Parameters.AddWithValue("@collectionDate", _obj.collectionDate);
cmd.Parameters.AddWithValue("@collectedBy", _obj.collectedBy);
cmd.Parameters.AddWithValue("@receivedBy", _obj.receivedBy);
cmd.Parameters.AddWithValue("@createdBy", _obj.createdBy);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result = rdr["collectionId"].ToString();
}
con.Close();
}
return(result);
}
public JsonResult InternetCustomerDelete(BillCollectionVM _obj)
{
try
{
_obj.createdBy = Convert.ToInt32(Session["userId"]);
string PageName = "InternetCustomerDeleteController";
int status = _objInternetCustomerDeleteDA.CheckPassword(_obj, PageName);
if (status > 0)
{
string result = _objInternetCustomerDeleteDA.InternetCustomerDelete(_obj);
return(Json(new { success = true, message = "Deleted Successfully" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { success = true, message = "Please input correct Password." }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex)
{
return(Json(new { success = false, message = ex.Message }, JsonRequestBehavior.AllowGet));
}
}
