ASP.NET MVC (Fatura Kayıtları) - Controller

InvoicesWeb uygulamasında şu anki noktaya kadar müşteri kayıtlarını işleyebileceğiniz CustomerController oluşturuldu. Faturalar için iki ayrı controller oluşturulabilir. Fatura genel bilgileri için InvoiceController, fatura satırları içinse InvoiceLineController oluşturulabilir.

Bu işlemler için gereken prosedürler InvoiceDB içerisinde tanımlanmış halde bulunuyor. CustomerModel veri türünün içinde bulunduğu Models dizini içerisine iki adet veri modeli daha oluşturun.

InvoiceModel ve InvoiceLineModel sınıfları fatura ve fatura satırlarını temsil eden veri modelleri olarak kullanılacaktır.

DBHelper sınıfı içerisine iki metot daha ekleyin. Bu metotlar (GetInvoice ve ListInvoices) tek bir faturayı veritabanından çekmek ve veritabanındaki faturaları listelemek için kullanılacak.

// Tek bir fatura kaydını almak için
public async Task<InvoiceModel> GetInvoice(int id)
{
    InvoiceModel invoice = new InvoiceModel() { InvoiceId = id };
    var connection = await OpenConnection();
    var command = invoice.ToGetOneCommand(connection);
    var dataReader = await command.ExecuteReaderAsync();
    while (await dataReader.ReadAsync())
    {
        invoice.GetFromDataReader(dataReader);
    }
    CloseConnection(connection);
    return invoice;
}

// Faturaları Listelemek için kullanılan sorgu
public async Task<IEnumerable<InvoiceModel>> ListInvoices()
{
    var connection = await OpenConnection();
    var command = new SqlCommand(@"
        select colInvoiceID, colInvoiceNumber, colTitle, i.colCustomerID, c.colName as colCustomerName, 
            i.colAddress, i.colInvoiceStatusID, s.colInvoiceStatus, i.colInvoiceTypeID, it.colInvoiceType, 
            i.colPaymentTypeID, p.colPaymentType, colPaymentDate,
            colCreateDateTime, i.colUserID, u.colFullName as colUserName
        from tblInvoices i
            left join tblInvoiceTypes it on i.colInvoiceTypeID = it.colInvoiceTypeID
            left join tblInvoiceStatuses s on i.colInvoiceStatusID = s.colInvoiceStatusID
            left join tblPaymentTypes p on i.colPaymentTypeID = p.colPaymentTypeID
            left join tblCustomers c on i.colCustomerID = c.colCustomerID
            left join tblUsers u on u.colUserID = i.colUserID", connection);
    var dataReader = await command.ExecuteReaderAsync();
    List<InvoiceModel> invoices = new List<InvoiceModel>();
    while (await dataReader.ReadAsync())
    {
        InvoiceModel invoice = new InvoiceModel();
        invoice.GetFromDataReader(dataReader);
        invoices.Add(invoice);
    }
    CloseConnection(connection);
    return invoices;
}

InvoiceModel sınıfı tıpkı CustomerModel sınıfı gibi IProcedure arabirimini kullanan bir sınıf olmalıdır.

public class InvoiceModel: IProcedure
{
    public int InvoiceId { get; set; }
    [Display(Name = "Fatura No")]
    public string InvoiceNumber { get; set; }
    [Display(Name = "Başlık")]
    public string Title { get; set; }
    [Display(Name = "Müşteri")]
    public int CustomerId { get; set; }
    [Display(Name = "Müşteri")]
    public string CustomerName { get; set; }
    [Display(Name = "Adres")]
    public string Address { get; set; }
    [Display(Name = "Fatura Durumu")]
    public int InvoiceStatusId { get; set; }
    [Display(Name = "Fatura Durumu")]
    public string InvoiceStatus { get; set; }
    [Display(Name = "Fatura Türü")]
    public int InvoiceTypeId { get; set; }
    [Display(Name = "Fatura Türü")]
    public string InvoiceType { get; set; }
    [Display(Name = "Ödeme Türü")]
    public int PaymentTypeId { get; set; }
    [Display(Name = "Ödeme Türü")]
    public string PaymentType { get; set; }
    [Display(Name = "Ödeme Tarihi")]
    public DateTime PaymentDate { get; set; }
    [Display(Name = "Oluşturulma")]
    public DateTime CreateDateTime { get; set; }
    [Display(Name = "Kullanıcı")]
    public int UserId { get; set; }
    [Display(Name = "Kullanıcı")]
    public string User { get; set; }
    public List<InvoiceLineModel> Lines { get; set; }

    public void GetFromDataReader(SqlDataReader dataReader)
    {
        InvoiceId = Convert.ToInt32(dataReader["colInvoiceID"]);
        InvoiceNumber = dataReader["colInvoiceNumber"].ToString();
        Title = dataReader["colTitle"].ToString();
        CustomerId = Convert.ToInt32(dataReader["colCustomerID"]);
        Address = dataReader["colAddress"].ToString();
        InvoiceTypeId = Convert.ToInt32(dataReader["colInvoiceTypeID"]);
        InvoiceStatusId = Convert.ToInt32(dataReader["colInvoiceStatusID"]);
        PaymentTypeId = Convert.ToInt32(dataReader["colPaymentTypeID"]);
        InvoiceType = dataReader["colInvoiceType"].ToString();
        InvoiceStatus = dataReader["colInvoiceStatus"].ToString();
        PaymentType = dataReader["colPaymentType"].ToString();
        PaymentDate = Convert.ToDateTime(dataReader["colPaymentDate"]);
        CreateDateTime = Convert.ToDateTime(dataReader["colCreateDateTime"]);
        UserId = Convert.ToInt32(dataReader["colUserID"]);
        User = dataReader["colUserName"].ToString();
    }
    
    public SqlCommand ToCreateCommand(SqlConnection connection)
    {
        SqlCommand command = new SqlCommand("dbo.procCreateInvoice", connection)
        { CommandType = System.Data.CommandType.StoredProcedure };
        command.Parameters.AddRange(new SqlParameter[] { PrmInvoiceNumber, PrmTitle, PrmCustomerId,
            PrmAddress, PrmInvoiceStatusId, PrmInvoiceTypeId, PrmPaymentTypeId, PrmUserId, PrmPaymentDate });
        return command;
    }

    public SqlCommand ToUpdateCommand(SqlConnection connection)
    {
        SqlCommand command = new SqlCommand("dbo.procUpdateInvoice", connection)
        { CommandType = System.Data.CommandType.StoredProcedure };
        command.Parameters.AddRange(new SqlParameter[] { PrmInvoiceId, PrmInvoiceNumber, PrmTitle, PrmCustomerId,
            PrmAddress, PrmInvoiceStatusId, PrmInvoiceTypeId, PrmPaymentTypeId, PrmPaymentDate });
        return command;
    }

    public SqlCommand ToDeleteCommand(SqlConnection connection)
    {
        SqlCommand command = new SqlCommand("dbo.procDeleteInvoice", connection)
        { CommandType = System.Data.CommandType.StoredProcedure };
        command.Parameters.AddRange(new SqlParameter[] { PrmInvoiceId });
        return command;
    }

    public SqlCommand ToGetOneCommand(SqlConnection connection)
    {
        SqlCommand command = new SqlCommand(@"
            select colInvoiceID, colInvoiceNumber, colTitle, i.colCustomerID, c.colName as colCustomerName, 
                i.colAddress, i.colInvoiceStatusID, s.colInvoiceStatus, i.colInvoiceTypeID, it.colInvoiceType, 
                i.colPaymentTypeID, p.colPaymentType, colPaymentDate,
                colCreateDateTime, i.colUserID, u.colFullName as colUserName
            from tblInvoices i
                left join tblInvoiceTypes it on i.colInvoiceTypeID = it.colInvoiceTypeID
                left join tblInvoiceStatuses s on i.colInvoiceStatusID = s.colInvoiceStatusID
                left join tblPaymentTypes p on i.colPaymentTypeID = p.colPaymentTypeID
                left join tblCustomers c on i.colCustomerID = c.colCustomerID
                left join tblUsers u on u.colUserID = i.colUserID
            where colInvoiceID = @_invoiceId
            select colInvoiceLineID, colDescription, colUnit, colPrice, colVatRatio 
            from tblInvoiceLines 
            where colInvoiceID = @_invoiceId", connection);
        command.Parameters.Add(PrmInvoiceId);
        return command;
    }

    private SqlParameter PrmInvoiceId { get { return new SqlParameter("@_invoiceID", System.Data.SqlDbType.Int) { Value = InvoiceId }; } }
    private SqlParameter PrmInvoiceNumber { get { return new SqlParameter("@_invoiceNumber", System.Data.SqlDbType.VarChar, 16) { Value = InvoiceNumber ?? "" }; } }
    private SqlParameter PrmCustomerId { get { return new SqlParameter("@_customerID", System.Data.SqlDbType.Int) { Value = CustomerId }; } }
    private SqlParameter PrmAddress { get { return new SqlParameter("@_address", System.Data.SqlDbType.NVarChar, 256) { Value = Address ?? "" }; } }
    private SqlParameter PrmTitle { get { return new SqlParameter("@_title", System.Data.SqlDbType.NVarChar, 64) { Value = Title ?? (object)DBNull.Value }; } }
    private SqlParameter PrmInvoiceTypeId { get { return new SqlParameter("@_invoiceTypeID", System.Data.SqlDbType.Int) { Value = InvoiceTypeId }; } }
    private SqlParameter PrmInvoiceStatusId { get { return new SqlParameter("@_invoiceStatusID", System.Data.SqlDbType.Int) { Value = InvoiceStatusId }; } }
    private SqlParameter PrmPaymentTypeId { get { return new SqlParameter("@_paymentTypeID", System.Data.SqlDbType.Int) { Value = PaymentTypeId }; } }
    private SqlParameter PrmPaymentDate { get { return new SqlParameter("@_paymentDate", System.Data.SqlDbType.Date) { Value = PaymentDate }; } }
    private SqlParameter PrmUserId { get { return new SqlParameter("@_userID", System.Data.SqlDbType.Int) { Value = UserId }; } }
}

Bu sınıfın yanı sıra oluşturma ve düzenleme sayfalarına çekilecek sabit veriler (Fatura Türü, Fatura Durumu, Ödeme Türü) için de aynı dosyada sınıflar oluşturun.

public class InvoiceTypeModel
{
    public int Id { get; set; }
    public string Value { get; set; }

    public void GetFromDataReader(SqlDataReader dataReader)
    {
        Id = Convert.ToInt32(dataReader["colInvoiceTypeID"]);
        Value = dataReader["colInvoiceType"].ToString();
    }
}

public class InvoiceStatusModel
{
    public int Id { get; set; }
    public string Value { get; set; }

    public void GetFromDataReader(SqlDataReader dataReader)
    {
        Id = Convert.ToInt32(dataReader["colInvoiceStatusID"]);
        Value = dataReader["colInvoiceStatus"].ToString();
    }
}

public class PaymentTypeModel
{
    public int Id { get; set; }
    public string Value { get; set; }

    public void GetFromDataReader(SqlDataReader dataReader)
    {
        Id = Convert.ToInt32(dataReader["colPaymentTypeID"]);
        Value = dataReader["colPaymentType"].ToString();
    }
}

public class InvoiceCustomerModel
{
    public int Id { get; set; }
    public string Value { get; set; }

    public void GetFromDataReader(SqlDataReader dataReader)
    {
        Id = Convert.ToInt32(dataReader["colCustomerID"]);
        Value = dataReader["colName"].ToString();
    }
}

public class InvoiceValuesModel
{
    public List<InvoiceTypeModel> InvoiceTypes { get; set; }
    public List<InvoiceStatusModel> InvoiceStatuses { get; set; }
    public List<PaymentTypeModel> PaymentTypes { get; set; }
    public List<InvoiceCustomerModel> InvoiceCustomers { get; set; }

    public InvoiceValuesModel() { InvoiceTypes = new List<InvoiceTypeModel>(); InvoiceStatuses = new List<InvoiceStatusModel>(); PaymentTypes = new List<PaymentTypeModel>(); }
}

Veritabanından nasıl veri çekileceği CustomerModel sınıfı ile aynı mantıkta işlemektedir. Ardından faturalar için oluşturulacak controller içerisinde aynı metotlar kullanılacaktır.

public class InvoiceController : Controller
{
    public async Task<ActionResult> List()
    {
        DBHelper db = new DBHelper();
        ViewBag.Invoices = await db.ListInvoices();
        return View();
    }

    public async Task<ActionResult> Create()
    {
        DBHelper db = new DBHelper();
        ViewBag.InvoiceValues = await db.GetInvoiceValues();
        return View();
    }

    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<ActionResult> Create(InvoiceModel invoice)
    {
        DBHelper db = new DBHelper();
        ViewBag.InvoiceValues = await db.GetInvoiceValues();
        invoice.UserId = 1;
        // geçici olarak her oluşturulan fatura 1 id kullanıcı sayılsın
        var result = await db.CreateProcedure(invoice);
        if (result.Success) ModelState.Clear();
        ViewBag.Result = result;
        return View();
    }
    
    public async Task<ActionResult> Edit(int id)
    {
        DBHelper db = new DBHelper();
        var invoice = await db.GetInvoice(id);
        ViewBag.Invoice = invoice;
        ViewBag.InvoiceValues = await db.GetInvoiceValues();
        return View(invoice);
    }

    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<ActionResult> Edit(InvoiceModel invoice)
    {
        DBHelper db = new DBHelper();
        ViewBag.InvoiceValues = await db.GetInvoiceValues();
        var result = await db.UpdateProcedure(invoice);
        ViewBag.Result = result;
        return View();
    }

    public async Task<ActionResult> Delete(int id)
    {
        DBHelper db = new DBHelper();
        var invoice = await db.GetInvoice(id);
        if (invoice == null) return RedirectToAction("List", "Invoice");
        return View(invoice);
    }

    [HttpPost, ActionName("Delete")]
    public async Task<ActionResult> DeleteConfirm(int id)
    {
        DBHelper db = new DBHelper();
        InvoiceModel customer = new InvoiceModel() { InvoiceId = id };
        var result = await db.DeleteProcedure(customer);
        return RedirectToAction("List", "Invoice");
    }
}

Fatura oluşturmak için Create.cshtml dosyasını CustomerController içerisinde yaptığınız aynı işlemleri yaparak oluşturup doldurun. Create metoduna sağ tıklayıp Add View işlemi ile kolayca View oluşturabilirsiniz.

@model InvoicesWeb.Models.InvoiceModel

@{
    ViewBag.Title = "Fatura Oluştur";
    InvoicesWeb.Models.InvoiceValuesModel invoiceValues = ViewBag.InvoiceValues;
    List<SelectListItem> listInvoiceTypes = new List<SelectListItem>();
    foreach (var type in invoiceValues.InvoiceTypes)
    {
        listInvoiceTypes.Add(new SelectListItem() { Text = type.Value, Value = type.Id.ToString() });
    }
    List<SelectListItem> listInvoiceStatuses = new List<SelectListItem>();
    foreach (var type in invoiceValues.InvoiceStatuses)
    {
        listInvoiceStatuses.Add(new SelectListItem() { Text = type.Value, Value = type.Id.ToString() });
    }
    List<SelectListItem> listPaymentTypes = new List<SelectListItem>();
    foreach (var type in invoiceValues.PaymentTypes)
    {
        listPaymentTypes.Add(new SelectListItem() { Text = type.Value, Value = type.Id.ToString() });
    }
    List<SelectListItem> listInvoiceCustomers = new List<SelectListItem>();
    foreach (var type in invoiceValues.InvoiceCustomers)
    {
        listInvoiceCustomers.Add(new SelectListItem() { Text = type.Value, Value = type.Id.ToString() });
    }
    InvoicesWeb.Helpers.DBResult result = ViewBag.Result;
}

<h2>Fatura Oluştur</h2>

@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
    <div class="form-horizontal">
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.InvoiceNumber, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.InvoiceNumber, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.InvoiceNumber, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.Title, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Title, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Title, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.CustomerId, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownListFor(model => model.CustomerId, listInvoiceCustomers, new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.CustomerId, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.InvoiceTypeId, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownListFor(model => model.InvoiceTypeId, listInvoiceTypes, new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.InvoiceTypeId, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.InvoiceStatusId, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownListFor(model => model.InvoiceStatusId, listInvoiceStatuses, new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.InvoiceStatusId, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.PaymentTypeId, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownListFor(model => model.PaymentTypeId, listPaymentTypes, new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.PaymentTypeId, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.PaymentDate, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.PaymentDate, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.PaymentDate, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Oluştur" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Listeye dön", "Index")
</div>

Fatura genel bilgilerini düzenlemek için kullanılan Edit.cshtml dosyası aşağıdaki gibi olacaktır.

@model InvoicesWeb.Models.InvoiceModel

@{
    ViewBag.Title = "Fatura Düzenle";
    InvoicesWeb.Models.InvoiceValuesModel invoiceValues = ViewBag.InvoiceValues;
    List<SelectListItem> listInvoiceTypes = new List<SelectListItem>();
    foreach (var type in invoiceValues.InvoiceTypes)
    {
        listInvoiceTypes.Add(new SelectListItem() { Text = type.Value, Value = type.Id.ToString() });
    }
    List<SelectListItem> listInvoiceStatuses = new List<SelectListItem>();
    foreach (var type in invoiceValues.InvoiceStatuses)
    {
        listInvoiceStatuses.Add(new SelectListItem() { Text = type.Value, Value = type.Id.ToString() });
    }
    List<SelectListItem> listPaymentTypes = new List<SelectListItem>();
    foreach (var type in invoiceValues.PaymentTypes)
    {
        listPaymentTypes.Add(new SelectListItem() { Text = type.Value, Value = type.Id.ToString() });
    }
    List<SelectListItem> listInvoiceCustomers = new List<SelectListItem>();
    foreach (var type in invoiceValues.InvoiceCustomers)
    {
        listInvoiceCustomers.Add(new SelectListItem() { Text = type.Value, Value = type.Id.ToString() });
    }
    InvoicesWeb.Helpers.DBResult result = ViewBag.Result;
}

<h2>Fatura Düzenle</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        @Html.HiddenFor(model => model.InvoiceId)
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.InvoiceNumber, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.InvoiceNumber, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.InvoiceNumber, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.Title, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Title, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Title, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.CustomerId, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownListFor(model => model.CustomerId, listInvoiceCustomers, new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.CustomerId, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.InvoiceTypeId, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownListFor(model => model.InvoiceTypeId, listInvoiceTypes, new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.InvoiceTypeId, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.InvoiceStatusId, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownListFor(model => model.InvoiceStatusId, listInvoiceStatuses, new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.InvoiceStatusId, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.PaymentTypeId, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownListFor(model => model.PaymentTypeId, listPaymentTypes, new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.PaymentTypeId, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.PaymentDate, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.PaymentDate, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.PaymentDate, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Düzenle" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    <a href="@Url.Action("List", "Invoice")">Listeye Dön</a>
</div>