public void CreateExcelReportForObj()
{
dcObj = new PartDataContext();
Warehouse w = string.IsNullOrEmpty(WarehouseId) ? null : dcObj.ActiveWarehouses.SingleOrDefault(wh => wh.WarehouseId == long.Parse(WarehouseId));
Dealer d = dcObj.Dealers.SingleOrDefault(dl => dl.DealerCode == DealerCode);
if (d == null) return;
string path = Path.Combine(HttpRuntime.AppDomainAppPath, PartMonthlyReport.GetMonthlyReportObjFilePath(w == null ? null : w.Code, DealerCode, Month, Year));
List<MonthlyReportObject> data = null;
//for (int i = 0; i < 40; i++)
{
data = GetMonthlyReportObjects(w, d, Month, Year);
}
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets.Add(string.Format("{0}.{1}", DealerCode, w == null ? "All" : w.Code));
#region Setting - styles
// Some optional properties of the Document
book.ExcelWorkbook.ActiveSheetIndex = 1;
book.Properties.Author = "ThangLong";
book.Properties.Title = "Stock monthly report by components";
book.Properties.Created = DateTime.Now;
// Add some styles to the Workbook
WorksheetStyle style = book.Styles.Add("HeaderStyle");
//style.Font.FontName = "Tahoma";
//style.Font.Size = 13;
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous).Weight = 1;
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous).Weight = 1;
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous).Weight = 1;
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous).Weight = 1;
style.Font.Bold = true;
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Font.Color = "White";
style.Interior.Color = "#555555";
style.Interior.Pattern = StyleInteriorPattern.Solid;
style = book.Styles.Add("Group");
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Interior.Color = "#CCCCCC";
style.Interior.Pattern = StyleInteriorPattern.Solid;
style = book.Styles.Add("GroupCenter");
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Interior.Color = "#CCCCCC";
style.Interior.Pattern = StyleInteriorPattern.Solid;
style = book.Styles.Add("DetailOdd");
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style = book.Styles.Add("DetailEven");
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Interior.Color = "#f1f5fa";
style.Interior.Pattern = StyleInteriorPattern.Solid;
style = book.Styles.Add("DetailOddCenter");
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style = book.Styles.Add("DetailEvenCenter");
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Interior.Color = "#f1f5fa";
style.Interior.Pattern = StyleInteriorPattern.Solid;
// Create the Default Style to use for everyone
style = book.Styles.Add("Default");
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
style.Alignment.Vertical = StyleVerticalAlignment.Center;
#endregion
#region column settings
WorksheetColumn col;
col = new WorksheetColumn(30); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // plType
col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // plCode
col = new WorksheetColumn(100); sheet.Table.Columns.Add(col); col.AutoFitWidth = false; // plName
col = new WorksheetColumn(20); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // No
col = new WorksheetColumn(30); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // pType
col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // pCode
col = new WorksheetColumn(100); sheet.Table.Columns.Add(col); col.AutoFitWidth = false; // pEname
col = new WorksheetColumn(100); sheet.Table.Columns.Add(col); col.AutoFitWidth = false; // pVname
col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // Begin
col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // In
col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // InAmount
col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // Out
col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // OutAmount
col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // Balance
#endregion
#region header rows
WorksheetCell cell;
WorksheetRow row = sheet.Table.Rows.Add();
cell = row.Cells.Add("Report for", DataType.String, "HeaderStyle"); cell.MergeAcross = 2;
cell = row.Cells.Add("Part", DataType.String, "HeaderStyle"); cell.MergeAcross = 4;
cell = row.Cells.Add("Begin", DataType.String, "HeaderStyle"); cell.MergeDown = 1;
cell = row.Cells.Add("In", DataType.String, "HeaderStyle"); cell.MergeAcross = 1;
cell = row.Cells.Add("Out", DataType.String, "HeaderStyle"); cell.MergeAcross = 1;
cell = row.Cells.Add("Balance", DataType.String, "HeaderStyle"); cell.MergeDown = 1;
row = sheet.Table.Rows.Add();
cell = row.Cells.Add("Type", DataType.String, "HeaderStyle");
cell = row.Cells.Add("Code", DataType.String, "HeaderStyle");
cell = row.Cells.Add("Name", DataType.String, "HeaderStyle");
cell = row.Cells.Add("No", DataType.String, "HeaderStyle");
cell = row.Cells.Add("Type", DataType.String, "HeaderStyle");
cell = row.Cells.Add("Code", DataType.String, "HeaderStyle");
cell = row.Cells.Add("English name", DataType.String, "HeaderStyle");
cell = row.Cells.Add("Vietnamese name", DataType.String, "HeaderStyle");
cell = row.Cells.Add("Quantity", DataType.String, "HeaderStyle"); cell.Index = 10;
cell = row.Cells.Add("Amount", DataType.String, "HeaderStyle");
cell = row.Cells.Add("Quantity", DataType.String, "HeaderStyle");
cell = row.Cells.Add("Amount", DataType.String, "HeaderStyle");
#endregion
#region bind data
string pOddStyle = w == null ? "Group" : "DetailOdd",
pEvenStyle = w == null ? "Group" : "DetailOdd",
pOddCenterStyle = w == null ? "GroupCenter" : "DetailOddCenter",
EvenCenterStyle = w == null ? "GroupCenter" : "DetailOddCenter",
dOddStyle = "DetailOdd", dEvenStyle = "DetailEven",
dOddCenterStyle = "DetailOddCenter", dEvenCenterStyle = "DetailEvenCenter";
int pIndex = 0;
data.ForEach(part =>
{
pIndex++;
bool odd = pIndex % 2 == 1;
string pStyle = odd ? pOddStyle : pEvenStyle;
string pcStyle = odd ? pOddCenterStyle : EvenCenterStyle;
// group line
row = sheet.Table.Rows.Add();
cell = row.Cells.Add(part.PlaceType, DataType.String, pcStyle);
cell = row.Cells.Add(part.PlaceCode, DataType.String, pStyle);
cell = row.Cells.Add(part.PlaceName, DataType.String, pStyle);
cell = row.Cells.Add("", DataType.String, pcStyle);
cell = row.Cells.Add("", DataType.String, pcStyle);
cell = row.Cells.Add("", DataType.String, pStyle);
cell = row.Cells.Add("", DataType.String, pStyle);
cell = row.Cells.Add("", DataType.String, pStyle);
cell = row.Cells.Add(part.BeginQuantity.ToString(), DataType.Number, pStyle); //cell.Index = 9;
cell = row.Cells.Add(part.InQuantity.ToString(), DataType.Number, pStyle);
cell = row.Cells.Add(part.InAmount.ToString(), DataType.Number, pStyle);
cell = row.Cells.Add(part.OutQuantity.ToString(), DataType.Number, pStyle);
cell = row.Cells.Add(part.OutAmount.ToString(), DataType.Number, pStyle);
cell = row.Cells.Add(part.Balance.ToString(), DataType.Number, pStyle);
// detail lines
int index = 0;
AddMonthlyReportObjDetailRows(ref index, sheet, part.PartList, dOddStyle, dEvenStyle, dOddCenterStyle, dEvenCenterStyle);
AddMonthlyReportObjDetailRows(ref index, sheet, part.AccList, dOddStyle, dEvenStyle, dOddCenterStyle, dEvenCenterStyle);
});
#endregion
dcObj.Dispose(); dcObj = null;
// Save the file
book.Save(path);
}
public bool DoCloseAll(DateTime defaultCloseDate)
{
if (this.Closing) return false;
this.Closing = true;
VehicleDataContext vdc = new VehicleDataContext();
PartDataContext pdc = new PartDataContext();
DateTime crrMonth = DataFormat.DateOfFirstDayInMonth(DateTime.Now);
LogMessage(string.Format("Starting Close {0} ----------", this.ForceClose ? ", forced by user" : "Automaticaly"));
foreach (var d in pdc.Dealers)
{
bool hasW = false;
LogMessage(string.Format("Closing dealer {0}: ", d.DealerCode));
try
{
// close warehouses
foreach (var w in d.ActiveWarehouses.Where(w => w.Type == VDMS.II.Entity.WarehouseType.Vehicle))
{
LogBeginMessage(string.Format(" Close warehouse {0}: ", w.Code));
try
{
hasW = true;
SaleInventoryLock wlck = InventoryHelper.GetInventoryLock(w.DealerCode, w.Code);
if (wlck == null)
{
InventoryHelper.DoCloseW(w.Code, w.DealerCode, defaultCloseDate.Month, defaultCloseDate.Year, vdc);
}
else
{
DateTime lastWLock = new DateTime((int)wlck.Year, (int)wlck.Month, 1);
lastWLock = lastWLock.AddMonths(1);
while (lastWLock < crrMonth)
{
InventoryHelper.DoCloseW(w.Code, w.DealerCode, lastWLock.Month, lastWLock.Year, vdc);
lastWLock = lastWLock.AddMonths(1);
}
}
LogEndMessage("Done!");
}
catch (Exception ex)
{
LogEndMessage(string.Format("Failed: {0}", DataFormat.TraceExceptionMessage(ex)));
}
}
// close Dealers
if (hasW)
{
SaleInventoryLock dlck = InventoryHelper.GetInventoryLock(d.DealerCode, 0);
if (dlck == null)
{
InventoryHelper.DoCloseD(d.DealerCode, defaultCloseDate.Month, defaultCloseDate.Year, vdc);
}
else
{
DateTime lastDLock = new DateTime((int)dlck.Year, (int)dlck.Month, 1);
lastDLock = lastDLock.AddMonths(1);
while (lastDLock < crrMonth)
{
InventoryHelper.DoCloseD(d.DealerCode, lastDLock.Month, lastDLock.Year, vdc);
lastDLock = lastDLock.AddMonths(1);
}
}
LogMessage(string.Format("Close dealer {0} done!", d.DealerCode));
}
else
{
LogMessage(string.Format("{0} has no warehouses!", d.DealerCode));
}
}
catch (Exception ex)
{
LogMessage(string.Format("Close dealer {0} failed: {1}", d.DealerCode, DataFormat.TraceExceptionMessage(ex)));
}
LogEndMessage(" ");
}
LogMessage("AutoClose finished ----------");
LogEndMessage(" ");
vdc.Dispose();
pdc.Dispose();
this.Closing = false;
return true;
}