Esempio n. 1
0
        public MemoryStream ReportDatHangChiTiet(NvPhieuDatHangVm.ParameterDatHang pi)
        {
            List <NvPhieuDatHangVm.DatHangExpImpChiTiet> data = GetDataReportDatHangChiTiet(pi);
            DateTime beginDay = new DateTime(pi.ToDate.Year, pi.ToDate.Month, pi.ToDate.Day, 0, 0, 0);
            DateTime endDay   = new DateTime(pi.FromDate.Year, pi.FromDate.Month, pi.FromDate.Day, 0, 0, 0);

            using (ExcelPackage package = new ExcelPackage())
            {
                var ms = new MemoryStream();
                package.Workbook.Worksheets.Add("Data");
                var worksheet   = package.Workbook.Worksheets[1];
                int startRow    = 4;
                int startColumn = 1;
                worksheet.Cells[1, 1, 1, 7].Merge = true;
                worksheet.Cells[1, 1].Value       = "Báo cáo Đặt hàng"; worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                worksheet.Cells[2, 1].Value       = string.Format("Từ Ngày: {0}/{1}/{2} Đến Ngày: {3}/{4}/{5}", beginDay.Day, beginDay.Month, beginDay.Year, endDay.Day, endDay.Month, endDay.Year);
                worksheet.Cells[2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                worksheet.Cells[2, 1, 2, 7].Merge = true;
                worksheet.Cells[3, 1, 3, 7].Merge = true;
                worksheet.Cells[3, 1].Value       = "Điều kiện, Nhóm theo";
                worksheet.Cells[4, 1].Value       = "STT"; worksheet.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                worksheet.Cells[4, 2].Value       = "Mã"; worksheet.Cells[5, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                worksheet.Cells[4, 3].Value       = "Tên hàng"; worksheet.Cells[5, 4].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                worksheet.Cells[4, 4].Value       = "Tên"; worksheet.Cells[5, 5].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                worksheet.Cells[4, 5].Value       = "Đơn giá"; worksheet.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                worksheet.Cells[4, 6].Value       = "Số lượng"; worksheet.Cells[5, 6].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                worksheet.Cells[4, 7].Value       = "Thành tiền"; worksheet.Cells[5, 7].Style.Border.BorderAround(ExcelBorderStyle.Thin);

                var itemTotal  = new NvPhieuDatHangVm.DatHangExpImpChiTiet();
                int currentRow = startRow;
                int stt        = 0;
                foreach (var item in data)
                {
                    stt = 0;
                    worksheet.Cells[currentRow, 1, currentRow, 7].Merge             = true;
                    worksheet.Cells[currentRow, startColumn].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    worksheet.Cells[currentRow, startColumn].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(252, 245, 76));
                    worksheet.Cells[currentRow, startColumn].Value = item.Code + " - " + item.Name;
                    currentRow++;
                    foreach (var itemdetail in item.DataDetail)
                    {
                        ++stt;
                        worksheet.Cells[currentRow, startColumn].Value     = stt.ToString();
                        worksheet.Cells[currentRow, startColumn + 1].Value = itemdetail.Code;
                        worksheet.Cells[currentRow, startColumn + 2].Value = itemdetail.MaHang;
                        worksheet.Cells[currentRow, startColumn + 3].Value = itemdetail.TenHang;
                        //worksheet.Cells[currentRow, startColumn + 4].Value = itemdetail.Name;
                        worksheet.Cells[currentRow, startColumn + 4].Value = itemdetail.DonGia; worksheet.Cells[currentRow, startColumn + 4].Style.Numberformat.Format = "#,##0.00";
                        worksheet.Cells[currentRow, startColumn + 5].Value = itemdetail.SoLuong; worksheet.Cells[currentRow, startColumn + 5].Style.Numberformat.Format = "#,##0.00";
                        worksheet.Cells[currentRow, startColumn + 6].Value = itemdetail.ThanhTien; worksheet.Cells[currentRow, startColumn + 6].Style.Numberformat.Format = "#,##0.00";

                        worksheet.Cells[currentRow, 1, currentRow, startColumn + 6].Style.Border.BorderAround(ExcelBorderStyle.Dotted);

                        itemTotal.SoLuong   = itemTotal.SoLuong + itemdetail.SoLuong;
                        itemTotal.ThanhTien = itemTotal.ThanhTien + itemdetail.ThanhTien;

                        currentRow++;
                    }
                }
                worksheet.Cells[currentRow, 1, currentRow, startColumn + 4].Merge = true;
                worksheet.Cells[currentRow, startColumn].Value     = "TỔNG CỘNG"; worksheet.Cells[currentRow, startColumn].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                worksheet.Cells[currentRow, startColumn + 5].Value = itemTotal.SoLuong; worksheet.Cells[currentRow, startColumn + 4].Style.Numberformat.Format = "#,##0.00";
                worksheet.Cells[currentRow, startColumn + 6].Value = itemTotal.ThanhTien; worksheet.Cells[currentRow, startColumn + 4].Style.Numberformat.Format = "#,##0.00";

                worksheet.Column(1).AutoFit();
                worksheet.Column(2).AutoFit();
                worksheet.Column(3).AutoFit();
                worksheet.Column(4).AutoFit();
                worksheet.Column(5).AutoFit();
                worksheet.Column(6).AutoFit();
                worksheet.Column(7).AutoFit();
                int totalRows = worksheet.Dimension.End.Row;
                int totalCols = worksheet.Dimension.End.Column;
                var dataCells = worksheet.Cells[1, 1, totalRows, totalCols];
                var dataFont  = dataCells.Style.Font;
                dataFont.SetFromFont(new System.Drawing.Font("Times New Roman", 10));
                package.SaveAs(ms);
                return(ms);
            }
        }
Esempio n. 2
0
        public List <NvPhieuDatHangVm.DatHangExpImpChiTiet> GetDataReportDatHangChiTiet(NvPhieuDatHangVm.ParameterDatHang pi)
        {
            List <NvPhieuDatHangVm.DatHangExpImpChiTiet>       data     = new List <NvPhieuDatHangVm.DatHangExpImpChiTiet>();
            List <NvPhieuDatHangVm.DatHangExpImpDetailChiTiet> dataTemp = new List <NvPhieuDatHangVm.DatHangExpImpDetailChiTiet>();

            DateTime beginDay, endDay;
            string   ky = string.Empty;

            beginDay = new DateTime(pi.FromDate.Year, pi.FromDate.Month, pi.FromDate.Day, 0, 0, 0);
            endDay   = new DateTime(pi.FromDate.Year, pi.FromDate.Month, pi.FromDate.Day, 23, 59, 59);
            string merchandiseCodes = "";
            string nhaCungCapCodes  = "";
            string NhanVienCodes    = "";
            string groupby          = "";

            using (var ctx = new ERPContext())
            {
                using (var dbContextTransaction = ctx.Database.BeginTransaction())
                {
                    try
                    {
                        merchandiseCodes = _convertToArrayCondition(pi.MerchandiseCodes);
                        nhaCungCapCodes  = _convertToArrayCondition(pi.NhaCungCapCodes);
                        NhanVienCodes    = _convertToArrayCondition(pi.NhanVienCodes);
                        groupby          = pi.GroupBy.ToString();
                        var pGroupBy         = new OracleParameter("pGroupBy", OracleDbType.NVarchar2, groupby, ParameterDirection.Input);
                        var pMerchandiseCode = new OracleParameter("pMerchandiseCode", OracleDbType.NVarchar2, merchandiseCodes, ParameterDirection.Input);
                        var pNhaCungCapCode  = new OracleParameter("pNhaCungCapCode", OracleDbType.NVarchar2, nhaCungCapCodes, ParameterDirection.Input);
                        var pNhanVienCode    = new OracleParameter("pNhanVienCode", OracleDbType.NVarchar2, NhanVienCodes, ParameterDirection.Input);
                        var pTrangThai       = new OracleParameter("pTrangThai", OracleDbType.NVarchar2, pi.TrangThaiDatHang, ParameterDirection.Input);

                        var pUnitCode = new OracleParameter("pUnitCode", OracleDbType.NVarchar2, pi.UnitCode, ParameterDirection.Input);
                        var pToDate   = new OracleParameter("pToDate", OracleDbType.Date, pi.ToDate, ParameterDirection.Input);
                        var pFromDate = new OracleParameter("pFromDate", OracleDbType.Date, pi.FromDate, ParameterDirection.Input);

                        var outRef = new OracleParameter("outRef", OracleDbType.RefCursor, ParameterDirection.Output);
                        var str    = "BEGIN TBNETERP.DATHANG.DATHANGCHITIET(:pGroupBy, :pTrangThai, :pNhanVienCode, :pMerchandiseCode, :pNhaCungCapCode,:pUnitCode, :pToDate,:pFromDate, :outRef); END;";
                        ctx.Database.ExecuteSqlCommand(str, pGroupBy, pTrangThai, pNhanVienCode, pMerchandiseCode, pNhaCungCapCode, pUnitCode, pToDate, pFromDate, outRef);
                        OracleDataReader reader = ((OracleRefCursor)outRef.Value).GetDataReader();
                        while (reader.Read())
                        {
                            decimal soLuong, donGia;
                            var     isSoLuong = decimal.TryParse(reader["SOLUONG"].ToString(), out soLuong);
                            var     isDonGia  = decimal.TryParse(reader["DONGIA"].ToString(), out donGia);
                            var     item      = new NvPhieuDatHangVm.DatHangExpImpDetailChiTiet();
                            switch ((int)pi.GroupBy)
                            {
                            case 1:
                                item = new NvPhieuDatHangVm.DatHangExpImpDetailChiTiet()
                                {
                                    Code      = reader["TRANGTHAI"].ToString(),
                                    Name      = _parseTrangThai(reader["TRANGTHAI"].ToString()),
                                    MaHang    = reader["MAHANG"].ToString(),
                                    TenHang   = reader["TENHANG"].ToString(),
                                    SoLuong   = isSoLuong ? soLuong : 0,
                                    DonGia    = isDonGia ? donGia : 0,
                                    ThanhTien = (isSoLuong ? soLuong : 0) * (isDonGia ? donGia : 0),
                                };
                                dataTemp.Add(item);
                                break;

                            case 2:
                                item = new NvPhieuDatHangVm.DatHangExpImpDetailChiTiet()
                                {
                                    //SoPhieu = reader["SOPHIEU"].ToString(),
                                    SoLuong   = isSoLuong ? soLuong : 0,
                                    DonGia    = isDonGia ? donGia : 0,
                                    MaHang    = reader["MAHANG"].ToString(),
                                    TenHang   = reader["TENHANG"].ToString(),
                                    ThanhTien = (isSoLuong ? soLuong : 0) * (isDonGia ? donGia : 0),
                                    Code      = reader["MANHANVIEN"].ToString(),
                                    Name      = reader["TENNHANVIEN"].ToString(),
                                };
                                dataTemp.Add(item);
                                break;

                            case 3:
                                break;

                            case 4:
                                item = new NvPhieuDatHangVm.DatHangExpImpDetailChiTiet()
                                {
                                    //SoPhieu = reader["SOPHIEU"].ToString(),
                                    SoLuong   = isSoLuong ? soLuong : 0,
                                    DonGia    = isDonGia ? donGia : 0,
                                    MaHang    = reader["MAHANG"].ToString(),
                                    TenHang   = reader["TENHANG"].ToString(),
                                    ThanhTien = (isSoLuong ? soLuong : 0) * (isDonGia ? donGia : 0),
                                    Code      = reader["MAKHACHHANG"].ToString(),
                                    Name      = reader["Tenkh"].ToString(),
                                };
                                dataTemp.Add(item);
                                break;
                            }
                        }
                        var lstTemp = dataTemp.GroupBy(x => x.Code);
                        lstTemp.ToList().ForEach(x => {
                            NvPhieuDatHangVm.DatHangExpImpChiTiet model = new NvPhieuDatHangVm.DatHangExpImpChiTiet();
                            model.DataDetail = new List <NvPhieuDatHangVm.DatHangExpImpDetailChiTiet>();
                            model.Code       = x.Key;
                            var children     = dataTemp.Where(i => i.Code == x.Key).ToList();
                            if (children[0] != null)
                            {
                                model.Name = children[0].Name;
                            }
                            model.DataDetail.AddRange(children);
                            data.Add(model);
                        });
                        dbContextTransaction.Commit();
                        return(data);
                    }
                    catch
                    {
                        dbContextTransaction.Rollback();
                        throw new Exception("Xảy ra lỗi trong khi chạy Store_Procude. Tự động rollback!");
                    }
                }
            }
        }