/// <summary>
/// uses the data provided by the <type>BillHeader</type> object to create a command with paramters to insert
/// data into the Customer TAble
/// </summary>
/// <param name="b"></param>
/// <returns><type>OleDbCommand</type></returns>
private OleDbCommand PrepareCustomerCommand(BillHeader b)
{
string queryInsertIntoCustomer = "Insert into Customer(CustomerName,AccountNumber,CustomerAddress,CustomerCity,CustomerState,CustomerZip,DateAdded) VALUES(?,?,?,?,?,?,?)";
OleDbCommand customerCommand = default;
try
{
customerCommand = new OleDbCommand(queryInsertIntoCustomer, con);
b.DateAdded = DateTime.Today;
customerCommand.Parameters.AddWithValue("@CustomerName", b.Customer_Name);
customerCommand.Parameters.AddWithValue("@AccountNumber", b.Account_No);
customerCommand.Parameters.AddWithValue("@CustomerAddress", b.Class_AddressInformation.Mailing_Address_1);
customerCommand.Parameters.AddWithValue("@CustomerCity", b.Class_AddressInformation.City);
customerCommand.Parameters.AddWithValue("@CustomerState", b.Class_AddressInformation.State);
customerCommand.Parameters.AddWithValue("@CustomerZip", b.Class_AddressInformation.Zip);
customerCommand.Parameters.AddWithValue("@DateAdded", b.DateAdded);
}
catch (Exception e)
{
if (e is OleDbException || e is OleDbException)
{
}
}
return(customerCommand);
}
public async Task <string> MakePaymentOnBill(int HeaderId, double NewPaymentAmt)
{
try
{
BillHeader Header = _db.BillHeader.FirstOrDefault(h => h.Id == HeaderId);
CustAcc Acc = _db.CustAcc.FirstOrDefault(ac => ac.CustId == Header.CustId);
// updating bill header will the payment
Header.PaidAmt += NewPaymentAmt;
// if the bill paid all, change status to completed
if (Header.TotalNetAmt == Header.PaidAmt)
{
Header.Status = SD.Completed;
}
// updating customer Acc
//Acc.Paid += PaidAmt;
//Acc.Debt -= PaidAmt;
UpdateCustomerAcc(Header.CustId ?? 0, NewPaymentAmt, NewPaymentAmt, "Old");
// add a new payment to bill payments table
AddBillPayment(Header.CustId ?? 0, Header.Id, NewPaymentAmt);
await _db.SaveChangesAsync();
return("تمت عملية الدفع");
}
catch (Exception ex)
{
return("لم تتم عملية الدفع");
}
}
public ActionResult OnPost(BillHeader billHeader)
{
string path = Request.Host.Value;
if (billHeader.Id != 0)
{
//return RedirectToPage("/Sales/Billings/PrintBill", new { BhId = BillHeader.Id });
var body = RazorPage.RenderToString("http://" + path + "/Sales/Billings/InvoicePrint?BhId=" + billHeader.Id);
var converter = new HtmlToPdf();
converter.Options.PdfPageSize = PdfPageSize.A4;
converter.Options.PdfPageOrientation = PdfPageOrientation.Portrait;
converter.Options.WebPageWidth = 1024;
converter.Options.WebPageHeight = 0;
converter.Options.WebPageFixedSize = false;
converter.Options.AutoFitWidth = HtmlToPdfPageFitMode.ShrinkOnly;
converter.Options.AutoFitHeight = HtmlToPdfPageFitMode.NoAdjustment;
// converter.Options.PdfPageCustomSize = new System.Drawing.SizeF(816, 1020);
PdfDocument doc = converter.ConvertHtmlString(body, "http://" + path + "/Sales/Billings/InvoicePrint?BhId=" + billHeader.Id);
byte[] pdf = doc.Save();
doc.Close();
return(new FileContentResult(pdf, "application/pdf")
{
FileDownloadName = billHeader.Id + "-" + "فاتورة.pdf"
});
}
return(RedirectToPage("/Sales/Billings/Details", new { BhId = billHeader.Id }));
}
/// <summary>
/// Creates a "HH~" formated line using the <param name="header"></param> data.
/// </summary>
/// <param name="header"></param>
/// <returns>String</returns>
private string CreateInvoiceRecordLine_Address(BillHeader header)
{
string line = string.Empty;
line = header.Account_No + "|" + header.Customer_Name + "|" + header.Class_AddressInformation.Mailing_Address_1 + "|" + header.Class_AddressInformation.Mailing_Address_2 + "|" + header.Class_AddressInformation.City + "|" + header.Class_AddressInformation.State + "|" + header.Class_AddressInformation.Zip + "";
return(line);
}
/// <summary>
/// uses a <type>Oledb</type> connection and a <type>OledbReader</type> instance to get the data from
/// both the Customer and Bills Table via a union. It returns a populated <type>BillHeader</type> list or a null list to indicate a failure in the operation
/// </summary>
/// <returns>List<BillHeader></BillHeader></returns>
public List <BillHeader> GetData()
{
List <BillHeader> returnData = new List <BillHeader>();
string getDataQuery = "SELECT b.CustomerID,c.CustomerName,c.AccountNumber,c.CustomerAddress,c.CustomerCity,c.CustomerState,c.CustomerZip,b.ID,b.BillDate,b.BillNumber,b.AccountBalance,b.DueDate,b.BillAmount,b.FormatGUID,c.DateAdded FROM Customer AS c Inner JOIN Bills as b ON c.ID=b.CustomerID";
OleDbCommand getDataCommand = new OleDbCommand(getDataQuery, con);
try
{
con.Open();
OleDbDataReader reader = getDataCommand.ExecuteReader();
while (reader.Read())
{
BillHeader b = new BillHeader();
b.Class_BillInfo.CustomerID = Convert.ToInt32(reader["CustomerID"].ToString());
b.Customer_Name = reader["CustomerName"].ToString();
b.Account_No = reader["AccountNumber"].ToString();
b.Class_AddressInformation.Mailing_Address_1 = reader["CustomerAddress"].ToString();
b.Class_AddressInformation.City = reader["CustomerCity"].ToString();
b.Class_AddressInformation.State = reader["CustomerState"].ToString();
b.Class_AddressInformation.Zip = reader["CustomerZip"].ToString();
b.Class_BillInfo.ID = Convert.ToInt32(reader["ID"].ToString());
b.Bill_Dt = Convert.ToDateTime(reader["BillDate"]);
b.Class_BillInfo.BillNumber = reader["BillNumber"].ToString();
b.Class_BillInfo.Balance_Due = Convert.ToDecimal(reader["AccountBalance"].ToString());
b.Due_Dt = Convert.ToDateTime(reader["DueDate"]);
b.Class_BillInfo.Bill_Amount = Convert.ToDecimal(reader["BillAmount"].ToString());
b.Class_BillInfo.FormatGUID = reader["FormatGUID"].ToString();
b.DateAdded = Convert.ToDateTime(reader["DateAdded"]);
returnData.Add(b);
}
}
catch (Exception e)
{
if (e is OleDbException)
{
return(returnData);
}
}
finally
{
con.Close();
}
return(returnData);
}
public async Task <ActionResult> OnGet(int BhId)
{
BillHeader BillHeader = await _db.BillHeader.Include(h => h.Customer)
.FirstOrDefaultAsync(h => h.Id == BhId);
HeaderId = BillHeader.Id;
CompanyName = BillHeader.Customer.CompanyName;
TotalNetAmt = BillHeader.TotalNetAmt;
PaidAmt = BillHeader.PaidAmt;
NewPayment = 0;
BalanceAmt = TotalNetAmt - PaidAmt;
return(Page());
}
/// <summary>
/// Parses through a RPT formated Document and Extracts the data into a <type>List<BillHeader</type> List.
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public List <BillHeader> ParseRPT(string filePath)
{
List <BillHeader> dataList = new List <BillHeader>();
if (string.IsNullOrEmpty(filePath))
{
return(dataList);
}
try
{
using (StreamReader reader = new StreamReader(filePath))
{
string header = reader.ReadLine();
//---->if does not contain("1~FR") aka header, return empty
if (string.IsNullOrEmpty(header))
{
return(dataList);
}
string line = string.Empty;
//every string objeect in "rowData" is a Line Read from the file
List <string> rowData = new List <string>();
while ((line = reader.ReadLine()) != null)
{
rowData.Add(line);
//When there are 2 items (AA,HH) in RowData Send off the pair to the helper function for data extraction
if (rowData.Count == 2)
{
BillHeader b = ParseRPTHelper(rowData);
if (b != null)
{
dataList.Add(b);
}
rowData.Clear();
}
}
}
}
catch (Exception e)
{
if (e is IOException)
{
return(dataList = default);
// this function will close a bill manually
public async Task <string> CloseBillManually(int HeaderId)
{
try
{
BillHeader Header = _db.BillHeader.FirstOrDefault(h => h.Id == HeaderId);
Header.Status = SD.Completed;
await _db.SaveChangesAsync();
return("تم اغلاق الفاتورة بنجاح");
}
catch
{
return("حصل خطأ لم يتم اغلاق الفاتورة");
}
}
/// <summary>
/// Creates a "AA~" formated line using the <paramref name="header"/> data.
/// </summary>
/// <param name="header"></param>
/// <returns>String</returns>
private string CreateInvoiceRecordLine_Invoice(BillHeader header)
{
string line = string.Empty;
string Current_Date = DateTime.Today.ToString("MM/dd/yyyy");
string First_Notification_Date = DateTime.Today.AddDays(5).ToString("MM/dd/yyyy");
string Second_Notifaction_Date = header.Due_Dt.AddDays(-3).ToString("MM/dd/yyyy");
line = _FieldTable["JJ"] + "|" + header.Invoice_No + "|" + header.Bill_Dt.ToString("MM/dd/yyyy") + "|" + header.Due_Dt.ToString("MM/dd/yyyy") + "|" + header.Class_BillInfo.Bill_Amount + "|" + First_Notification_Date + "|" + Second_Notifaction_Date + "|" + header.Class_BillInfo.Balance_Due + "|" + Current_Date + "|" + header.Service_Address + "";
return(line);
}
public async Task <string> DeleteBill(int HeaderId)
{
try
{
BillHeader Header = _db.BillHeader.FirstOrDefault(h => h.Id == HeaderId);
List <BillItems> BillItemList = _db.BillItems.Where(i => i.HeaderId == HeaderId).ToList();
// reverting customer Acc
double DebtAmt = Header.TotalNetAmt - Header.PaidAmt;
RevertCustomerAcc(Header.CustId ?? 0, Header.PaidAmt, DebtAmt);
// remove bill payment of bill payments table
DeleteBillPayment(Header.Id);
// Creating Bill items
foreach (BillItems Item in BillItemList)
{
// revert stock qty of that item
RevertStockQty(Item.ProdId ?? 0, Item.WhId, Item.Qty);
// _db.BillItems.Add(Bill);
}
// remove inv transaction list from InvTransaction Table
DeleteInvTransaction(HeaderId, SD.Sales);
// removing bill item list
_db.BillItems.RemoveRange(BillItemList);
//remove header
_db.BillHeader.RemoveRange(Header);
await _db.SaveChangesAsync();
return("تم حذف الفاتورة");
}
catch
{
return("Error! حصل خطأ لم يتم حذف الفاتورة");
}
}
/// <summary>
/// uses the data provided by the <type>BillHeader</type> object to create a command
/// with paramters that will insert data into the Bills table
/// </summary>
/// <param name="b"></param>
/// <returns>OleDbCommand</returns>
private OleDbCommand PrepareBillCommand(BillHeader b)
{
string queryInsertIntoBill = "Insert into Bills(BillDate,BillNumber,BillAmount,FormatGUID,AccountBalance,DueDate,ServiceAddress,FirstEmailDate,SecondEmailDate,CustomerID) VALUES(?,?,?,?,?,?,?,?,?,?)";
OleDbCommand billCommand = new OleDbCommand(queryInsertIntoBill, con);
billCommand.Parameters.AddWithValue("@BillDate", b.Bill_Dt);
billCommand.Parameters.AddWithValue("@BillNumber", b.Invoice_No);
billCommand.Parameters.AddWithValue("@BillAmount", b.Class_BillInfo.Bill_Amount);
billCommand.Parameters.AddWithValue("@FormatGUID", b.FormatGUID);
billCommand.Parameters.AddWithValue("@AccountBalance", b.Class_BillInfo.Balance_Due);
billCommand.Parameters.AddWithValue("@DueDate", b.Due_Dt);
billCommand.Parameters.AddWithValue("@ServiceAddress", b.SERVICE_ADDRESS);
billCommand.Parameters.AddWithValue("@FirstEmailDate", b.Class_BillInfo.FirstEmailDate);
billCommand.Parameters.AddWithValue("@SecondEmailDate", b.Class_BillInfo.SecondEmailDate);
billCommand.Parameters.AddWithValue("@CustomerID", b.Class_BillInfo.CustomerID);
return(billCommand);
}
static long BillHeader()
{
BillHeader e = new BillHeader()
{
BillDate = "BillDateLK",
BillNumber = 1,
ContractNumber = "ContractNumberLK",
InvestorID = 1,
OrganizationID = (int)LookUps.LookUpsValues["OrganizationID"],
RequestID = (long)LookUps.LookUpsValues["RequestID"],
RequestTypeID = (int)LookUps.LookUpsValues["RequestTypeID"]
};
billsUow.BillHeaders.Add<BillHeader, long>(e);
billsUow.BillHeaders.Commit();
return e.ID;
}
// this function will create a bill for the first time
public async Task <string> CreateBill(BillHeader Header, List <BillItems> BillItems, int WhId, string Type, int?OldBhId)
{
try
{
Header.Id = new int();
Header.CreatedById = GetLoggedInUserId();
Header.CreatedDataTime = DateTime.Now;
if (Header.TotalNetAmt == Header.PaidAmt)
{
Header.Status = SD.Completed;
}
else
{
Header.Status = SD.OpenBill;
}
_db.BillHeader.Add(Header);
await _db.SaveChangesAsync();
// updatinga customer Acc
double DebtAmt = Header.TotalNetAmt - Header.PaidAmt;
UpdateCustomerAcc(Header.CustId ?? 0, Header.PaidAmt, DebtAmt, "New");
// add a new payment to bill payments table
AddBillPayment(Header.CustId ?? 0, Header.Id, Header.PaidAmt);
// Creating Bill items
foreach (BillItems item in BillItems)
{
// item.ProdId = _db.ProdInfo.FirstOrDefault(pr => pr.ProdCode == item.ProdInfo.ProdCode).Id;
BillItems Bill = new BillItems
{
HeaderId = Header.Id,
ProdId = item.ProdId,
Qty = item.Qty,
WhId = WhId,
UnitPrice = item.UnitPrice,
TotalAmt = item.UnitPrice * item.Qty,
Note = item.Note
};
// decrease stock qty of that item
DecreaseStockQty(Bill.ProdId ?? 0, WhId, Bill.Qty);
// create inv transaction
CreateInvTransaction(Bill.ProdId ?? 0, WhId, Bill.Qty, Header.Id, SD.Sales);
_db.BillItems.Add(Bill);
}
await _db.SaveChangesAsync();
// if this function being used to edit an exisiting bill, then the old bill will be deleted
if (Type == "Edit")
{
DeleteBill(OldBhId ?? 0).GetAwaiter().GetResult();
return("تم التعديل على الفاتورة");
}
return("تمت اضافة فاتورة مبيعات جديدة");
}
catch (Exception ex)
{
return("Error! حصل خطأ لم يتم اضافة الفاتورة");
}
}
public void BillHeader()
{
int count = uow.BillHeaders.GetAll().AsQueryable<BillHeader>().Count<BillHeader>();
#region Add
BillHeader newEntity = new BillHeader()
{
BillDate = "BillDate",
BillNumber = 1,
ContractNumber = "ContractNumber",
InvestorID = 1,
OrganizationID = (int)LookUps.LookUpsValues["OrganizationID"],
RequestID = (long)LookUps.LookUpsValues["RequestID"],
RequestTypeID = (int)LookUps.LookUpsValues["RequestTypeID"]
};
uow.BillHeaders.Add<BillHeader, long>(newEntity);
uow.BillHeaders.Commit();
var result = uow.BillHeaders.GetAll().AsQueryable<BillHeader>();
Assert.AreEqual(count + 1, result.Count<BillHeader>(), "Adding Error");
#endregion
#region Update
BillHeader entity = uow.BillHeaders.GetById(newEntity.ID);
entity.BillDate = "BillDate2";
entity.BillNumber = 2;
entity.ContractNumber = "ContractNumber2";
entity.InvestorID = 2;
uow.BillHeaders.Update(entity);
uow.BillHeaders.Commit();
BillHeader entity2 = uow.BillHeaders.GetById(newEntity.ID);
Assert.AreEqual("BillDate2", entity2.BillDate, "Updating Error");
Assert.AreEqual(2, entity2.BillNumber, "Updating Error");
Assert.AreEqual("ContractNumber2", entity2.ContractNumber, "Updating Error");
Assert.AreEqual(2, entity2.InvestorID, "Updating Error");
#endregion
#region Delete
BillHeader entityDeleted = uow.BillHeaders.GetById(newEntity.ID);
uow.BillHeaders.Delete(entity2);
uow.BillHeaders.Commit();
Assert.AreEqual(count, uow.BillHeaders.GetAll().AsQueryable<BillHeader>().Count<BillHeader>(), "Deleting Error");
#endregion
}
/// <summary>
/// Parses the provided .xml file and extracts the data into a list of <type>BillHeader</type> objects. One object per each Parent Node.
/// </summary>
/// <param name="nodeList"></param>
/// <returns></returns>
public List <BillHeader> ParseXMLData(XmlNodeList nodeList)
{
List <BillHeader> invoiceRecords = new List <BillHeader>();
if (nodeList == null)
{
return(invoiceRecords);
}
foreach (XmlNode node in nodeList)
{
BillHeader b = new BillHeader();
#region Node Bill_Header DATA
b.Invoice_No = node["Invoice_No"].InnerText;
b.Account_No = node["Account_No"].InnerText;
b.Customer_Name = node["Customer_Name"].InnerText;
try
{
b.Bill_Dt = DateTime.ParseExact(node["Bill_Dt"].InnerText, "MMM-dd-yyyy", System.Globalization.CultureInfo.InvariantCulture);
}
catch (Exception ex)
{
if (ex is FormatException || ex is ArgumentNullException)
{
b.Bill_Dt = default;
}
}
try
{
b.Due_Dt = DateTime.ParseExact(node["Due_Dt"].InnerText, "MMM-dd-yyyy", System.Globalization.CultureInfo.InvariantCulture);
}
catch (Exception ex)
{
if (ex is FormatException || ex is ArgumentNullException)
{
b.Bill_Dt = default;
}
}
#endregion
#region Node Bill Data
XmlNodeList billNodechildren = node["Bill"].ChildNodes;
b.Class_BillInfo.Bill_Amount = Convert.ToDecimal(billNodechildren.Item(0).InnerText);
b.Class_BillInfo.Balance_Due = Convert.ToDecimal(billNodechildren.Item(1).InnerText);
#endregion
#region Node Address Data
XmlNodeList addressNodeChildren = node["Address_Information"].ChildNodes;
b.Class_AddressInformation.Mailing_Address_1 = addressNodeChildren.Item(0).InnerText;
b.Class_AddressInformation.Mailing_Address_2 = addressNodeChildren.Item(1).InnerText;
b.Class_AddressInformation.City = addressNodeChildren.Item(2).InnerText;
b.Class_AddressInformation.State = addressNodeChildren.Item(3).InnerText;
b.Class_AddressInformation.Zip = addressNodeChildren.Item(4).InnerText;
#endregion
invoiceRecords.Add(b);
}
return(invoiceRecords);
}