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); }