public Receipt GetReceipt(int receiptId)
{
var receipt = new Receipt();
var con = new SqlConnection(_connectionString);
using (con)
{
con.Open();
var sql = "Select * from Receipts where IndexNumber=@rid";
var cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@rid", receiptId);
var rdr = cmd.ExecuteReader();
using (rdr)
{
while (rdr.Read())
{
receipt.ClientId = Convert.ToInt32(rdr["ClientId"].ToString());
receipt.Date = Convert.ToDateTime(rdr["Date"].ToString());
receipt.IndexNumber = Convert.ToInt32(rdr["IndexNumber"]);
receipt.TotalAmount = Convert.ToDouble(rdr["TotalAmount"]);
receipt.NetAmount = Convert.ToDouble(rdr["NetAmount"]);
receipt.VatAmount = Convert.ToDouble(rdr["VatAmount"]);
receipt.VatPercent = Convert.ToInt32(rdr["VatPercent"]);
receipt.IsPrinted = Convert.ToBoolean(rdr["IsPrinted"]);
receipt.ReceiptDescription = rdr["ReceiptDescription"].ToString();
}
}
}
return receipt;
}
public void CreateReceipt(Receipt receipt, int receiptType)
{
throw new NotImplementedException();
}
public void CreateReceipt(Receipt receipt, int receiptType)
{
var con = new SqlConnection(_connectionString);
using (con)
{
con.Open();
string table = "";
switch (receiptType)
{
case 1:
table = "Receipts";
break;
case 2:
table = "Invoices";
break;
case 3:
table = "DeliveryInvoices";
break;
}
//Always reseed the identity to ensure continuous invoice numbers.
string strMaxIndex = "SELECT MAX(IndexNumber) from " + table;
var cmdMaxIndex = new SqlCommand(strMaxIndex, con);
var max = cmdMaxIndex.ExecuteScalar();
string sqlIdentity = "DBCC CHECKIDENT ( " + table + ", RESEED, " + Convert.ToInt32(max) + " )";
var cmdIdentity = new SqlCommand(sqlIdentity, con);
cmdIdentity.ExecuteNonQuery();
string sql = "Insert into " + table + " (ClientId, Date, TotalAmount, NetAmount, VatAmount, VatPercent, IsPrinted, ReceiptDescription) Values (@cid, @date, @totalamount, @netamount, @vatamount, @vatPercent, @isprinted, @descr)";
var cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@cid", receipt.ClientId);
cmd.Parameters.AddWithValue("@date", DateTime.MinValue == receipt.Date ? DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") : receipt.Date.ToString("yyyy-MM-dd hh:mm:ss"));
cmd.Parameters.AddWithValue("@totalamount", receipt.TotalAmount);
cmd.Parameters.AddWithValue("@netamount", receipt.NetAmount);
cmd.Parameters.AddWithValue("@vatAmount", receipt.VatAmount);
cmd.Parameters.AddWithValue("@vatPercent", receipt.VatPercent);
cmd.Parameters.AddWithValue("@isprinted", "1");
cmd.Parameters.AddWithValue("@descr", receipt.ReceiptDescription ?? "");
cmd.ExecuteNonQuery();
if (receiptType == 3)
{
const string strMaxIndexNumber = "SELECT MAX(IndexNumber) from DeliveryInvoices";
var cmdMaxIndexNumber = new SqlCommand(strMaxIndexNumber, con);
var maxIndex = cmdMaxIndexNumber.ExecuteScalar();
const string updateDeliveryInvoices = "Update DeliveryInvoices set PlaceOfOrigin=@origin, PlaceofDelivery=@delivery where IndexNumber=@id";
var cmdUpdateDel = new SqlCommand(updateDeliveryInvoices, con);
cmdUpdateDel.Parameters.AddWithValue("@id", Convert.ToInt32(maxIndex));
cmdUpdateDel.Parameters.AddWithValue("@origin", receipt.PlaceOfOrigin);
cmdUpdateDel.Parameters.AddWithValue("@delivery", receipt.PlaceOfDelivery);
cmdUpdateDel.ExecuteNonQuery();
if (receipt.DeliveryInvoiceItems != null && receipt.DeliveryInvoiceItems.Count > 0)
SaveDeliveryInvoiceItems(receipt.DeliveryInvoiceItems, Convert.ToInt32(maxIndex));
}
if (receiptType == 2)
{
const string strMaxIndexNumber = "SELECT MAX(IndexNumber) from Invoices";
var cmdMaxIndexNumber = new SqlCommand(strMaxIndexNumber, con);
var maxIndex = cmdMaxIndexNumber.ExecuteScalar();
SaveInvoiceItems(receipt.InvoiceItems, Convert.ToInt32(maxIndex));
}
}
}
public void SaveReceipt(Receipt receipt, int receiptType)
{
var con = new SqlConnection(_connectionString);
using (con)
{
con.Open();
string table = "";
switch (receiptType)
{
case 1:
table = "Receipts";
break;
case 2:
table = "Invoices";
break;
case 3:
table = "DeliveryInvoices";
break;
}
string sql = "Update " + table + " set ClientId = @cid, Date = @date, TotalAmount = @totalamount, NetAmount=@netamount, VatAmount=@vatAmount, VatPercent=@vatPercent, IsPrinted = @isprinted, ReceiptDescription = @descr where IndexNumber=@id";
var cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@id", receipt.IndexNumber);
cmd.Parameters.AddWithValue("@cid", receipt.ClientId);
cmd.Parameters.AddWithValue("@date", receipt.Date.ToString("yyyy-MM-dd hh:mm:ss"));
cmd.Parameters.AddWithValue("@totalamount", receipt.TotalAmount);
cmd.Parameters.AddWithValue("@netamount", receipt.NetAmount);
cmd.Parameters.AddWithValue("@vatAmount", receipt.VatAmount);
cmd.Parameters.AddWithValue("@vatPercent", receipt.VatPercent);
cmd.Parameters.AddWithValue("@isprinted", "1");
cmd.Parameters.AddWithValue("@descr", receipt.ReceiptDescription ?? "");
cmd.ExecuteNonQuery();
if (receiptType == 3)
{
const string updateDeliveryInvoices = "Update DeliveryInvoices set PlaceOfOrigin=@origin, PlaceofDelivery=@delivery where IndexNumber=@id";
var cmdUpdateDel = new SqlCommand(updateDeliveryInvoices, con);
cmdUpdateDel.Parameters.AddWithValue("@id", receipt.IndexNumber);
cmdUpdateDel.Parameters.AddWithValue("@origin", receipt.PlaceOfOrigin ?? "");
cmdUpdateDel.Parameters.AddWithValue("@delivery", receipt.PlaceOfDelivery ?? "");
cmdUpdateDel.ExecuteNonQuery();
SaveDeliveryInvoiceItems(receipt.DeliveryInvoiceItems, receipt.IndexNumber);
}
if (receiptType == 2)
{
SaveInvoiceItems(receipt.InvoiceItems, receipt.IndexNumber);
}
}
}
public List<Receipt> GetClientReceipts(int clientId)
{
var receipts = new List<Receipt>();
var con = new SqlConnection(_connectionString);
using (con)
{
con.Open();
const string sql = "Select * from Receipts where ClientId=@cid";
var cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("cid", clientId);
var rdr = cmd.ExecuteReader();
using (rdr)
{
while (rdr.Read())
{
var receipt = new Receipt
{
Date = Convert.ToDateTime(rdr["Date"]),
IndexNumber = Convert.ToInt32(rdr["IndexNumber"]),
TotalAmount = Convert.ToDouble(rdr["TotalAmount"]),
NetAmount = Convert.ToDouble(rdr["NetAmount"]),
VatAmount = Convert.ToDouble(rdr["VatAmount"]),
VatPercent = Convert.ToInt32(rdr["VatPercent"]),
IsPrinted = Convert.ToBoolean(rdr["IsPrinted"]),
ReceiptDescription = rdr["ReceiptDescription"].ToString(),
ReceiptType = 1
};
receipts.Add(receipt);
}
}
}
return receipts;
}
public void InsertReceipt(Receipt receipt)
{
_dal.CreateReceipt(receipt, receipt.ReceiptType);
}
public void SaveReceipt(Receipt receipt)
{
_dal.SaveReceipt(receipt, receipt.ReceiptType);
}
