public static void WriteOff(PaymentNote target)
{
using (var cmd = new System.Data.SQLite.SQLiteCommand(DataBase.Instance.sqlConnection))
{
cmd.Parameters.Add(cmd.CreateParameter());
cmd.CommandText = "update DebitNotes set WroteOff = DateTime('now','localtime') where Id = " + target.Id;
cmd.ExecuteNonQuery();
}
}
public static void DeleteReceiptXLSX(PaymentNote target)
{
string targetFileName = "Receipts/Receipts-" + target.OwnerId.ToString() + "-" + target.Id.ToString() + ".xlsx";
if (System.IO.File.Exists(targetFileName))
System.IO.File.Delete(targetFileName);
}
public static void Delete(PaymentNote target)
{
using (var cmd = new System.Data.SQLite.SQLiteCommand(DataBase.Instance.sqlConnection))
{
cmd.Parameters.Add(cmd.CreateParameter());
cmd.CommandText = "delete from DebitNotes where Id = " + target.Id;
cmd.ExecuteNonQuery();
}
}
public static void CreateReceiptXLSX(PaymentNote target, IList<Company> companies)
{
if (!System.IO.Directory.Exists("Receipts"))
System.IO.Directory.CreateDirectory("Receipts");
string templateFileName = "Templates.xlsx";
string targetFileName = "Receipts/Receipts-" + target.OwnerId.ToString() + "-" + target.Id.ToString() + ".xlsx";
if (System.IO.File.Exists(targetFileName))
try
{
System.IO.File.Delete(targetFileName);
}
catch(Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message, "Failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
OfficeOpenXml.ExcelPackage templatePackage = new OfficeOpenXml.ExcelPackage(new System.IO.FileInfo(templateFileName));
OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(new System.IO.FileInfo(targetFileName));
package.Workbook.Worksheets.Add("Receipt", templatePackage.Workbook.Worksheets["Receipt"]);
OfficeOpenXml.ExcelWorksheet receipt = package.Workbook.Worksheets["Receipt"];
//Insert company-SerialNumber.
receipt.Cells[6, 7].Value = Company.GetCompanyById(companies, target.OwnerId).SerialNumber;
receipt.Cells[9, 3].Value = Company.GetCompanyById(companies, target.OwnerId).CompanyName;
receipt.Cells[11, 3, 11, 5].Style.Numberformat.Format += "\"元\"\"整\"";
receipt.Cells[11, 3, 11, 5].Value = target.total;
receipt.Cells[11, 6].Value = target.total;
package.Save();
while (!System.IO.File.Exists(targetFileName))
System.Threading.Thread.Sleep(100);
}
public static void CreatePaymentNoteXLSX(PaymentNote target, IList<Company> companies)
{
if (!System.IO.Directory.Exists("PaymentNotes"))
System.IO.Directory.CreateDirectory("PaymentNotes");
string templateFileName = "Templates.xlsx";
string targetFileName = "PaymentNotes/PaymentNote-" + target.OwnerId.ToString() + "-" + target.Id.ToString() + ".xlsx";
if (System.IO.File.Exists(targetFileName))
return;
/*try
{
System.IO.File.Delete(targetFileName);
}
catch(Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message, "Failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}*/
OfficeOpenXml.ExcelPackage templatePackage = new OfficeOpenXml.ExcelPackage(new System.IO.FileInfo(templateFileName));
OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(new System.IO.FileInfo(targetFileName));
package.Workbook.Worksheets.Add("Payment Note", templatePackage.Workbook.Worksheets["Payment Note"]);
OfficeOpenXml.ExcelWorksheet paymentNote = package.Workbook.Worksheets["Payment Note"];
string format = "_-* #,##0_-;-* #,##0_-;_-* \" - \"??_-;_-@_-";
//Prepare spaces
if(target.Services.Count > 0)
paymentNote.InsertRow(10, target.Services.Count, 10);
if(target.Advances.Count > 0)
paymentNote.InsertRow(13 + target.Services.Count, target.Advances.Count, 12 + target.Services.Count);
//Insert company-name.
paymentNote.Cells[5, 2].Value = Company.GetCompanyById(companies, target.OwnerId).CompanyName;
//Insert services
for (int i = 0; i < target.Services.Count; i++)
{
paymentNote.Cells[10 + i, 2].Value = target.Services[i].title;
paymentNote.Cells[10 + i, 6].Value = target.Services[i].amount;
paymentNote.Cells[10 + i, 6].Style.Numberformat.Format = format;
}
//Insert tax
paymentNote.Cells[11 + target.Services.Count, 6].Style.Numberformat.Format = format;
paymentNote.Cells[11 + target.Services.Count, 6].Value = -target.tax;
//Insert advances
for (int i = 0; i < target.Advances.Count; i++)
{
paymentNote.Cells[13 + i + target.Services.Count, 2].Value = target.Advances[i].title;
paymentNote.Cells[13 + i + target.Services.Count, 6].Value = target.Advances[i].amount;
paymentNote.Cells[13 + i + target.Services.Count, 6].Style.Numberformat.Format = format;
}
//Insert total
paymentNote.Cells[15 + target.Services.Count + target.Advances.Count, 6].Value = target.total;
paymentNote.Cells[15 + target.Services.Count + target.Advances.Count, 6].Style.Numberformat.Format = format;
package.Save();
while (!System.IO.File.Exists(targetFileName))
System.Threading.Thread.Sleep(100);
}
void ReloadDebitNotes(BindingList<PaymentNote> target)
{
target.Clear();
PaymentNote.ClearCategoryList();
using (System.Data.SQLite.SQLiteDataReader reader = DataBase.Instance.ExecuteReader("SELECT * FROM DebitNotes"))
{
while (reader.Read())
{
reader.GetValue(0);
PaymentNote debitNote = new PaymentNote(
reader.GetInt32(0), //Id
reader.GetInt32(1), //OwnerId
reader.IsDBNull(2) ? string.Empty : reader.GetString(2), //Category
reader.GetDateTime(3), //Date
reader.IsDBNull(4) ? string.Empty : reader.GetString(4), //Services
reader.IsDBNull(5) ? string.Empty : reader.GetString(5), //Advances
reader.GetInt32(6), //Tax
reader.GetInt32(7), //Total
reader.IsDBNull(8) ? (Nullable<DateTime>)null : reader.GetDateTime(8)
);
target.Add(debitNote);
}
}
filterCategory.DataSource = null;
filterCategory.DataSource = PaymentNote.CategoryList;
filters_Changed(null, null);
}