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;
        }