public bool SaveStatementToExcel(string filename, string account, double TotPurchases, double TotPayment, double Balance, List <clsAccountStatement> lstStatement)
{
Workbook xlsBook = new Workbook();
Worksheet xlsSheet;
int iRow = 0;
xlsBook.LoadTemplateFromFile("template\\accountstatement.xls");
xlsSheet = xlsBook.Worksheets["Statement"];
xlsSheet.Range[2, 3].Text = account;
xlsSheet.Range[3, 3].Text = TotPurchases.ToString("0.00");
xlsSheet.Range[4, 3].Text = TotPayment.ToString("0.00");
xlsSheet.Range[5, 3].Text = Balance.ToString("0.00");
xlsSheet.Range[2, 6].Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
iRow = 7;
frmProgress progress = new frmProgress(lstStatement.Count);
progress.Caption = "Export in progress";
progress.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
progress.Show();
int ctr = 0;
foreach (clsAccountStatement acc in lstStatement)
{
AddToExcel(acc.ToString(), xlsSheet, ref iRow);
progress.Val = ++ctr;
}
progress.Close();
xlsBook.Worksheets.Add(xlsSheet);
xlsBook.SaveToFile(filename);
System.Windows.Forms.MessageBox.Show("Export Completed!", "Export", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return(File.Exists(filename));
}
public bool SaveToExcelServiceFee(string filename, string columns, List <string> values, string DateAsOf = "Export")
{
Workbook xlsBook = new Workbook();
Worksheet xlsSheet;
int iRow = 0;
ClearSheets(xlsBook, DateAsOf);
xlsSheet = xlsBook.Worksheets[DateAsOf];
AddHeader(columns.Split("\t".ToCharArray()), xlsSheet, iRow, true);
frmProgress progress = new frmProgress(values.Count);
progress.Caption = "Export in progress";
progress.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
progress.Show();
int ctr = 0;
foreach (string strdata in values)
{
AddToExcel(strdata, xlsSheet, ref iRow);
progress.Val = ++ctr;
}
progress.Close();
xlsBook.Worksheets.Add(xlsSheet);
xlsBook.SaveToFile(filename);
System.Windows.Forms.MessageBox.Show("Export Completed!", "Export", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return(File.Exists(filename));
}
public string UpdateInventoryFromTemp(List <clsProductItem> lstProdItems, string username)
{
Receipt m_receipt = new Receipt();
frmTempTrans tmp = new frmTempTrans(clsUsers.GetUser(username));
if (tmp.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
if (tmp.SelectedTempOR != null)
{
m_receipt = tmp.SelectedTempOR;
}
}
Dictionary <string, clsProductItem> dicProd = new Dictionary <string, clsProductItem>();
foreach (clsProductItem c in lstProdItems)
{
if (dicProd.ContainsKey(c.BarCode) == false)
{
dicProd.Add(c.BarCode.Trim(), c);
}
}
frmProgress progress = new frmProgress(lstProdItems.Count);
progress.Caption = "Update in Progress";
progress.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
progress.Show();
foreach (KeyValuePair <string, clsPurchasedItem> p in m_receipt.PurchasedItems)
{
progress.Val++;
if (dicProd.ContainsKey(p.Key))
{
clsProductItem prod = dicProd[p.Key];
if (prod.StocksRemainingQty != p.Value.Qty)
{
double addQtyToInventory = p.Value.Qty - prod.StocksRemainingQty;
clsInventory itemIventory = new clsInventory();
itemIventory.BarCode = prod.BarCode;
itemIventory.Capital = prod.Capital;
itemIventory.Quantity = addQtyToInventory;
itemIventory.DateAdded = DateTime.Now;
itemIventory.Remarks = string.Format("{0}: TotInv={1} + {2}", username, prod.StocksRemainingQty, addQtyToInventory);
itemIventory.Save();
prod.TotalInventoryQty += addQtyToInventory;
prod.Save();
}
}
progress.Close();
m_receipt.DeleteTempReceipt();
}
return("");
}
public bool SaveToExcelWithSummary(string filename, string columns, List <string> values, string summarycolumns, string summaryvalues)
{
Workbook xlsBook = new Workbook();
Worksheet xlsSheet;
int iRow = 0;
ClearSheets(xlsBook, "Export");
xlsSheet = xlsBook.Worksheets["Export"];
AddHeader(summarycolumns.Split("\t".ToCharArray()), xlsSheet, iRow, false);
AddToExcel(summaryvalues, xlsSheet, ref iRow);
iRow += 3;
AddHeader(columns.Split("\t".ToCharArray()), xlsSheet, iRow, false);
frmProgress progress = new frmProgress(values.Count);
progress.Caption = "Export in progress";
progress.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
progress.Show();
int ctr = 0;
foreach (string strdata in values)
{
if (strdata.Contains("HEADER1"))
{
string tmp = strdata.Replace("HEADER1", "");
iRow++;
AddHeader(tmp.Split("\t".ToCharArray()), xlsSheet, iRow, false);
}
else if (strdata.Contains("HEADER2"))
{
string tmp = strdata.Replace("HEADER2", "");
iRow++;
AddHeader(tmp.Split("\t".ToCharArray()), xlsSheet, iRow, true);
}
else
{
AddToExcel(strdata, xlsSheet, ref iRow);
}
progress.Val = ++ctr;
}
progress.Close();
xlsBook.Worksheets.Add(xlsSheet);
xlsBook.SaveToFile(filename);
System.Windows.Forms.MessageBox.Show("Export Completed!", "Export", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return(File.Exists(filename));
}
public string UpdateInventory(string filename, List <clsProductItem> lstProdItems, string username)
{
Workbook xlsBook = new Workbook();
Worksheet xlsSheet = null;
if (File.Exists(filename))
{
xlsBook.LoadFromFile(filename);
}
Dictionary <string, clsProductItem> dicProd = new Dictionary <string, clsProductItem>();
foreach (clsProductItem c in lstProdItems)
{
if (dicProd.ContainsKey(c.BarCode) == false)
{
dicProd.Add(c.BarCode.Trim(), c);
}
}
Dictionary <int, string> categories = dbConnect.GetCategories();
foreach (Worksheet sheet in xlsBook.Worksheets)
{
xlsSheet = sheet;
if (xlsSheet[1, 1].Value.Trim() == "Barcode" && xlsSheet[1, 2].Value.Trim() == "Description" &&
xlsSheet[1, 3].Value.Trim() == "Capital" && xlsSheet[1, 4].Value.Trim() == "Retail Amount" && xlsSheet[1, 5].Value.Trim() == "Wholesale Amount" &&
xlsSheet[1, 6].Value.Trim() == "Category" && xlsSheet[1, 7].Value.Trim() == "Total Inventory" && xlsSheet[1, 8].Value.Trim() == "Quantity Sold" &&
xlsSheet[1, 9].Value.Trim() == "Remaining Quantity" && xlsSheet[1, 10].Value.Trim() == "Actual Qty" && xlsSheet[1, 11].Value.Trim() == "Difference")
{
int ctr = 2;
frmProgress progress = new frmProgress(lstProdItems.Count);
progress.Caption = "Update in Progress";
progress.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
progress.Show();
while (xlsSheet[ctr, 1].Value != "")
{
progress.Val += 1;
if (dicProd.ContainsKey(xlsSheet[ctr, 1].Value.Trim()))
{
clsProductItem prod = dicProd[xlsSheet[ctr, 1].Value.Trim()];
try
{
double addQtyToInventory = Convert.ToDouble(xlsSheet[ctr, 10].Value) - prod.StocksRemainingQty;
if (prod.Description != xlsSheet[ctr, 2].Value.Trim() || prod.Capital != double.Parse(xlsSheet[ctr, 3].Value.Trim()) ||
prod.Amount != double.Parse(xlsSheet[ctr, 4].Value.Trim()) || prod.WSAmount != double.Parse(xlsSheet[ctr, 5].NumberText) ||
Convert.ToDouble(xlsSheet[ctr, 10].Value) != prod.StocksRemainingQty || xlsSheet[ctr, 6].Value.Trim() != prod.Category)
{
if (Convert.ToDouble(xlsSheet[ctr, 10].Value) - prod.StocksRemainingQty != 0)
{
clsInventory itemIventory = new clsInventory();
itemIventory.BarCode = prod.BarCode;
itemIventory.Capital = prod.Capital;
itemIventory.Quantity = addQtyToInventory;
itemIventory.DateAdded = DateTime.Now;
itemIventory.Remarks = string.Format("{0}: TotInv={1} + {2}", username, prod.StocksRemainingQty, addQtyToInventory);
itemIventory.Save();
prod.Description = xlsSheet[ctr, 2].Value.Trim();
prod.Capital = double.Parse(xlsSheet[ctr, 3].Value.Trim());
prod.Amount = double.Parse(xlsSheet[ctr, 4].Value.Trim());
prod.WSAmount = double.Parse(xlsSheet[ctr, 5].NumberText);
prod.TotalInventoryQty += addQtyToInventory;
if (categories.ContainsValue(xlsSheet[ctr, 6].Value.Trim()) == false)
{
dbConnect.AddCategory(xlsSheet[ctr, 6].Value.Trim());
categories = dbConnect.GetCategories();
}
if (categories != null && categories.ContainsValue(xlsSheet[ctr, 6].Value.Trim()))
{
foreach (KeyValuePair <int, string> str in categories)
{
if (str.Value == xlsSheet[ctr, 6].Value.Trim())
{
prod.CategoryId = str.Key;
break;
}
}
}
prod.Save();
}
}
}
catch { }
}
else
{
clsProductItem proditem = new clsProductItem();
proditem.BarCode = xlsSheet[ctr, 1].Value.Trim().ToUpper();
proditem.Description = xlsSheet[ctr, 2].Value.Trim();
proditem.Amount = double.Parse(xlsSheet[ctr, 4].Value.Trim());
proditem.WSAmount = double.Parse(xlsSheet[ctr, 5].Value.Trim());
proditem.WSMinimum = 0;
proditem.Capital = double.Parse(xlsSheet[ctr, 3].Value.Trim());
proditem.Imagepath = "";
proditem.Unit = "pc";
proditem.InStorage = 0;
proditem.CriticalLevel = 5;
proditem.QtySold = 0;
clsInventory itemIventory = new clsInventory();
itemIventory.BarCode = proditem.BarCode;
itemIventory.Capital = proditem.Capital;
itemIventory.DateAdded = DateTime.Now;
itemIventory.Quantity = Convert.ToDouble(xlsSheet[ctr, 10].Value);
itemIventory.Remarks = string.Format("{0}: TotInv={1} + {2}", username, 0, itemIventory.Quantity);
itemIventory.Save();
proditem.TotalInventoryQty = itemIventory.Quantity;
if (categories.ContainsValue(xlsSheet[ctr, 6].Value.Trim()) == false)
{
dbConnect.AddCategory(xlsSheet[ctr, 6].Value.Trim());
categories = dbConnect.GetCategories();
}
if (categories != null && categories.ContainsValue(xlsSheet[ctr, 6].Value.Trim()))
{
foreach (KeyValuePair <int, string> str in categories)
{
if (str.Value == xlsSheet[ctr, 6].Value.Trim())
{
proditem.CategoryId = str.Key;
break;
}
}
}
proditem.Save();
}
ctr++;
}
progress.Close();
}
break;
}
return("");
}
