public static int Insert(Bill billDetails)
{
int result = 0;
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[Connection.ConnectionName].ConnectionString))
{
StringBuilder insertstmt = new StringBuilder("INSERT INTO [dbo].[Bill] ( [customerid], [pnumber], [UsageAmount], [DueDate], [FineAmount], [TotalAmount], [Status]) VALUES (@customerid,@pnumber,@UsageAmount,@DueDate,@FineAmount,@TotalAmount,@Status)");
SqlCommand command = new SqlCommand(insertstmt.ToString(), myConnection);
command.CommandType = CommandType.Text;
command.Parameters.Add("@customerid", SqlDbType.Int).Value = billDetails.CustomerId;
command.Parameters.Add("@pnumber", SqlDbType.VarChar, 10).Value = billDetails.PhoneNumber;
command.Parameters.Add("@UsageAmount", SqlDbType.Money).Value = billDetails.UsageAmount;
command.Parameters.Add("@DueDate", SqlDbType.Date).Value = billDetails.DueDate;
command.Parameters.Add("@FineAmount", SqlDbType.Money).Value = billDetails.FineAmount;
command.Parameters.Add("@TotalAmount", SqlDbType.Money).Value = billDetails.TotalAmount;
command.Parameters.Add("@Status", SqlDbType.Int).Value = billDetails.Status;
DbParameter returnValue;
returnValue = command.CreateParameter();
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);
myConnection.Open();
command.ExecuteNonQuery();
result = Convert.ToInt32(returnValue.Value);
myConnection.Close();
}
return result;
}
public static int AddPostPaidBill(Bill bill)
{
bill.Status = BillStatus.Pending;
if (bill.DueDate < DateTime.Now)
{
return -1;
}
return BillDB.Insert(bill);
}
public static Bill GetBillDetails(int id)
{
Bill bill = new Bill();
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[Connection.ConnectionName].ConnectionString))
{
string sqlstmt = "select id,customerid,pnumber,usageamount,duedate,fineamount,totalamount,status from [dbo].[Bill] where id = @id";
SqlCommand myCommand = new SqlCommand(sqlstmt, myConnection);
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("@id", id);
myConnection.Open();
using (SqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.Read())
{
bill = FillDataRecord(myReader);
}
myReader.Close();
}
myConnection.Close();
}
return bill;
}
public ActionResult GenerateBill(Bill billDetails)
{
if (ModelState.IsValid)
{
try
{
if (billDetails.PhoneNumber == null || billDetails.PhoneNumber == "0" || billDetails.PhoneNumber == "")
ModelState.AddModelError("", "Phone Number is mandatory.");
else
{
int retval = MobileManager.AddPostPaidBill(billDetails);
if (retval == -1)
{
ModelState.AddModelError("", "Invalid Due Date. It should be a future date.");
}
else
{
MvcApplication.SetMessage("Bill Generation Successful");
return RedirectToAction("Index", "Home");
}
}
}
catch (Exception e)
{
ModelState.AddModelError("", e.Message);
}
}
else
{
if (billDetails.PhoneNumber == null || billDetails.PhoneNumber == "0" || billDetails.PhoneNumber == "")
ModelState.AddModelError("", "Phone Number is mandatory.");
}
return View("GenerateBill");
}
private static Bill FillDataRecord(IDataRecord myDataRecord)
{
Bill bill = new Bill();
bill.Id = myDataRecord.GetInt32(myDataRecord.GetOrdinal("id"));
bill.CustomerId = myDataRecord.GetInt32(myDataRecord.GetOrdinal("customerid"));
bill.PhoneNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("pnumber"));
bill.UsageAmount = myDataRecord.GetDecimal(myDataRecord.GetOrdinal("usageamount"));
bill.DueDate = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("duedate"));
bill.FineAmount = myDataRecord.GetDecimal(myDataRecord.GetOrdinal("fineamount"));
bill.TotalAmount = myDataRecord.GetDecimal(myDataRecord.GetOrdinal("totalamount"));
bill.Status = (BillStatus)myDataRecord.GetInt32(myDataRecord.GetOrdinal("status"));
return bill;
}