public List <LineInvoice> ChargerLineInvoicesByInvoice(int id)
{
List <LineInvoice> list = new List <LineInvoice>();
if (OpenConnection() == false)
{
return(list);
}
string req = "SELECT line_invoice.id, line_invoice.description, line_invoice.pht, line_invoice.tva, line_invoice.pttc FROM line_invoice WHERE id_invoice = @id";
MySqlCommand mySqlCommand = new MySqlCommand(req, mySqlConnection);
mySqlCommand.Parameters.Add(new MySqlParameter("@id", id));
MySqlDataReader reader = mySqlCommand.ExecuteReader();
while (reader.Read())
{
LineInvoice lineInvoice = new LineInvoice
{
Id = reader.GetInt32("id"),
Description = reader.GetString("description"),
Pht = reader.GetDouble("pht"),
Tva = reader.GetDouble("tva"),
Pttc = reader.GetDouble("pttc")
};
list.Add(lineInvoice);
}
CloseConnection();
return(list);
}
public void addLineInvoiceToList(LineInvoice li)
{
LineInvoiceViewModel lineInvoice = new LineInvoiceViewModel(li);
ListLineInvoices.Add(lineInvoice);
}
/// <summary>
/// //opens a new worksheet in the ExcelPackage object and fills it with lineInvoice's data
/// </summary>
private void GetExcelLine(ExcelPackage excel, LineInvoice lineInvoice)
{
string nameWorksheets = "Line ";
nameWorksheets += lineInvoice.LineNumber;
excel.Workbook.Worksheets.Add(nameWorksheets);
var worksheet = excel.Workbook.Worksheets[nameWorksheets];
worksheet.Cells[2, 2].Value = "Line Number";
worksheet.Cells[2, 2].Style.Font.Bold = true;
worksheet.Cells[2, 3].Style.Font.Bold = true;
worksheet.Cells[2, 3].Value = lineInvoice.LineNumber;
var headerRowData = new List <string[]>()
{
new string[] { "Price", Math.Round(lineInvoice.TotalPrice, 2).ToString(), "Package info", lineInvoice.PackageInfo, "" }
};
string headerRangeData = "B3:" + Char.ConvertFromUtf32(headerRowData[0].Length + 64) + "3";
worksheet.Cells[headerRangeData].LoadFromArrays(headerRowData);
worksheet.Cells[headerRangeData].Style.Font.Bold = true;
worksheet.Cells[headerRangeData].Style.Font.Size = 12;
worksheet.Cells[2, 2, 3, 3].Style.Font.Color.SetColor(System.Drawing.Color.Blue);
worksheet.Cells[5, 2].Value = "Package";
worksheet.Cells[5, 2].Style.Font.Bold = true;
worksheet.Cells[5, 2].Style.Font.Size = 12;
worksheet.Cells[5, 2].Style.Font.UnderLine = true;
headerRowData = new List <string[]>()
{
new string[] { "Minutes", lineInvoice.Minutes.ToString(), "Minutes Left In Package", lineInvoice.MinutesLeftInPackage.ToString(),
"Package % Usage", lineInvoice.PackagePercentUsage.ToString() + "%" }
};
headerRangeData = "B6:" + Char.ConvertFromUtf32(headerRowData[0].Length + 64) + "6";
worksheet.Cells[headerRangeData].LoadFromArrays(headerRowData);
headerRowData = new List <string[]>()
{
new string[] { "Package Price", "", "", "",
"", lineInvoice.PackagePrice.ToString() }
};
headerRangeData = "B7:" + Char.ConvertFromUtf32(headerRowData[0].Length + 64) + "7";
worksheet.Cells[headerRangeData].LoadFromArrays(headerRowData);
worksheet.Cells[headerRangeData].Style.Font.Bold = true;
worksheet.Cells[9, 2].Value = "Out of Package";
worksheet.Cells[9, 2].Style.Font.Bold = true;
worksheet.Cells[9, 2].Style.Font.Size = 12;
worksheet.Cells[9, 2].Style.Font.UnderLine = true;
headerRowData = new List <string[]>()
{
new string[] { "Minutes Beyond Package Limit", Math.Round(lineInvoice.MinutesBeyondPackageLimit, 2).ToString(), "Price Per Minute", lineInvoice.MinutePrice.ToString(),
"Total", lineInvoice.TotalMinutesPrice.ToString() }
};
headerRangeData = "B10:" + Char.ConvertFromUtf32(headerRowData[0].Length + 64) + "10";
worksheet.Cells[headerRangeData].LoadFromArrays(headerRowData);
headerRowData = new List <string[]>()
{
new string[] { "SMS Beyond Package Limit", lineInvoice.SMS.ToString(), "Price Per SMS", lineInvoice.SMSPrice.ToString(),
"Total", lineInvoice.TotalSMSPrice.ToString() }
};
headerRangeData = "B11:" + Char.ConvertFromUtf32(headerRowData[0].Length + 64) + "11";
worksheet.Cells[headerRangeData].LoadFromArrays(headerRowData);
headerRowData = new List <string[]>()
{
new string[] { "Out of Package Price", "", "", "",
"", lineInvoice.OutOfPackageTotalPrice.ToString() }
};
headerRangeData = "B12:" + Char.ConvertFromUtf32(headerRowData[0].Length + 64) + "12";
worksheet.Cells[headerRangeData].Style.Font.Bold = true;
worksheet.Cells[headerRangeData].LoadFromArrays(headerRowData);
worksheet.DefaultColWidth = 15;
worksheet.Cells[7, 7].Style.Font.Bold = true;
worksheet.Cells[12, 7].Style.Font.Bold = true;
}
public LineInvoiceViewModel(LineInvoice lineInvoice)
{
LineInvoice = lineInvoice;
}
