Beispiel #1
0
        private void tựĐộngThanhLýToolStripMenuItem_Click(object sender, EventArgs e)
        {
            int           count = 0;
            List <TaiSan> liTS  = bll.GetListTS_BLL();

            foreach (TaiSan ts in liTS)
            {
                List <DTO.ChungTuGiam> ThongtinCtg = bll.GetThongTinCTGbyMaTS_BLL(ts.MaTaiSan);
                if (ThongtinCtg.Count > 0)
                {
                    continue;
                }

                int nsd = ts.NgayGhiTang.Value.Year;
                int now = DateTime.Now.Year;

                if (ts.TyLeCL - ts.TyLeHM * (now - nsd) == 0)
                {
                    count++;
                    DTO.ChungTuGiam ctg = new DTO.ChungTuGiam();
                    ctg.MaChungTuGiam = "02-TDTL-" + RandomString(8);
                    ctg.MaTaiSan      = ts.MaTaiSan;
                    ctg.NgayGhiGiam   = DateTime.Now;
                    ctg.SoLuong       = ts.SoLuong;
                    ctg.ThanhTien     = ts.ThanhTien;
                    ctg.NoiDung       = "Thanh lý hao mòn dưới 0%";
                    ctg.GhiChu        = "ghi chu 1";
                    bll.UpdateSL(ctg.MaTaiSan, 0, 0);
                    bll.AddChungTuGiam_BLL(ctg);
                }
            }
            addCTG();
            addVePhong();
            MessageBox.Show("Đã thanh lý " + count.ToString() + " tài sản có tỷ lệ còn lại dưới 0%!");
        }
 private void detach_ChungTuGiams(ChungTuGiam entity)
 {
     this.SendPropertyChanging();
     entity.TaiSan = null;
 }
 partial void DeleteChungTuGiam(ChungTuGiam instance);
 partial void UpdateChungTuGiam(ChungTuGiam instance);
 partial void InsertChungTuGiam(ChungTuGiam instance);
Beispiel #6
0
        void exportToExcel(string fullpath)
        {
            Excel.Application xlApp;
            Excel.Workbook    xlWorkBook;

            object misValue = System.Reflection.Missing.Value;

            xlApp      = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);


            List <string> ListKhoa = bll.GetListKhoa_BLL();

            ListKhoa.RemoveAt(ListKhoa.Count - 1);
            foreach (string tenkhoa in ListKhoa)
            {
                tbStatusBar.Text = "Saving... Please Wait";
                Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, xlWorkBook.Worksheets[xlWorkBook.Worksheets.Count], 1, XlSheetType.xlWorksheet);
                xlWorkSheet.Name = tenkhoa;



                Range r = xlWorkSheet.Range["A8:A10"];
                r.Merge();
                r.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r.Font.Bold   = true;
                r.Cells[1, 1] = "Số TT";


                Range r1 = xlWorkSheet.Range["B8:N8"];
                r1.Merge();
                r1.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r1.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r1.Font.Bold            = true;
                xlWorkSheet.Cells[8, 2] = "Ghi tăng tài sản, dụng cụ, đồ gỗ";

                Range r2 = xlWorkSheet.Range["O8:S8"];
                r2.Merge();
                r2.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r2.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r2.Font.Bold   = true;
                r2.Cells[1, 1] = "Ghi giảm tài sản";

                Range r3 = xlWorkSheet.Range["B9:D9"];
                r3.Merge();
                r3.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r3.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r3.Font.Bold             = true;
                r3.Cells[1, 1]           = "Chứng từ";
                xlWorkSheet.Cells[10, 2] = "Mã TS";
                xlWorkSheet.Cells[10, 3] = "SLTS Nhập về";
                xlWorkSheet.Cells[10, 4] = "Mã NSD";

                Range r4 = xlWorkSheet.Range["E9:E10"];
                r4.Merge();
                r4.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r4.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r4.Font.Bold   = true;
                r4.Cells[1, 1] = "Tên tài sản cố định, CC, DC  và đồ gỗ ...";

                Range r5 = xlWorkSheet.Range["F9:F10"];
                r5.Merge();
                r5.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r5.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r5.Font.Bold   = true;
                r5.Cells[1, 1] = "Số hiệu, thông số kỹ thuật";

                Range r6 = xlWorkSheet.Range["G9:G10"];
                r6.Merge();
                r6.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r6.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r6.Font.Bold   = true;
                r6.Cells[1, 1] = "SL Tại Phòng";

                Range r7 = xlWorkSheet.Range["H9:H10"];
                r7.Merge();
                r7.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r7.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r7.Font.Bold   = true;
                r7.Cells[1, 1] = "Thành tiền";

                Range r8 = xlWorkSheet.Range["I9:I10"];
                r8.Merge();
                r8.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r8.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r8.Font.Bold   = true;
                r8.Cells[1, 1] = "Tỷ lệ %CL";

                Range r9 = xlWorkSheet.Range["J9:J10"];
                r9.Merge();
                r9.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r9.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r9.Font.Bold   = true;
                r9.Cells[1, 1] = "Tỷ lệ %CL";

                Range r10 = xlWorkSheet.Range["K9:K10"];
                r10.Merge();
                r10.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r10.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r10.Font.Bold   = true;
                r10.Cells[1, 1] = "Tỷ lệ %CL";

                Range r11 = xlWorkSheet.Range["L9:L10"];
                r11.Merge();
                r11.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r11.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r11.Font.Bold   = true;
                r11.Cells[1, 1] = "Tỷ lệ %CL";

                Range r12 = xlWorkSheet.Range["M9:M10"];
                r12.Merge();
                r12.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r12.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r12.Font.Bold   = true;
                r12.Cells[1, 1] = "Tỷ lệ %CL";

                Range r13 = xlWorkSheet.Range["N9:N10"];
                r13.Merge();
                r13.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r13.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r13.Font.Bold   = true;
                r13.Cells[1, 1] = "Tỷ lệ HM";

                Range r14 = xlWorkSheet.Range["O9:P9"];
                r14.Merge();
                r14.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r14.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r14.Font.Bold   = true;
                r14.Cells[1, 1] = "Chứng từ";

                Range r15 = xlWorkSheet.Range["O10:O10"];
                r15.Merge();
                r15.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r15.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r15.Font.Bold   = true;
                r15.Cells[1, 1] = "Ngày";

                Range r16 = xlWorkSheet.Range["P10:P10"];
                r16.Merge();
                r16.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r16.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r16.Font.Bold   = true;
                r16.Cells[1, 1] = "Lý do";

                Range r17 = xlWorkSheet.Range["Q9:Q10"];
                r17.Merge();
                r17.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r17.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r17.Font.Bold   = true;
                r17.Cells[1, 1] = "Số lượng Thanh lý";

                Range r18 = xlWorkSheet.Range["R9:R10"];
                r18.Merge();
                r18.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r18.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r18.Font.Bold   = true;
                r18.Cells[1, 1] = "Thành tiền";

                Range r19 = xlWorkSheet.Range["S9:S10"];
                r19.Merge();
                r19.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                r19.Borders.LineStyle         = Excel.XlLineStyle.xlContinuous;
                r19.Font.Bold   = true;
                r19.Cells[1, 1] = "Ghi chú";

                QLTSDataContext db = new QLTSDataContext();

                var s = db.TaiSans.Where(p => p.MaTaiSan.Substring(4, 3) == bll.GetMaKhoa_BLL(tenkhoa) && p.MaTaiSan.Substring(8, 3) == "000").Select(p => p);

                List <TaiSan> li = s.ToList <TaiSan>();


                int begini = 12;
                int beginj = 2;
                int STT    = 0;
                foreach (TaiSan ts in li)
                {
                    xlWorkSheet.Cells[begini, beginj - 1]          = ++STT;
                    xlWorkSheet.Cells[begini, beginj]              = ts.MaTaiSan;
                    xlWorkSheet.Cells[begini, beginj + 1]          = ts.SoLuongNhap;
                    xlWorkSheet.Cells[begini, beginj + 2]          = ts.NgayGhiTang.Value.Year.ToString();
                    xlWorkSheet.Cells[begini, beginj + 3]          = ts.TenTaiSan;
                    xlWorkSheet.Cells[begini, beginj + 4]          = ts.ThongSoKyThuat;
                    xlWorkSheet.Cells[begini, beginj + 5]          = bll.GetSLTSOPhong_BLL(ts.MaTaiSan.Substring(0, 7));
                    xlWorkSheet.Cells[begini, beginj + 6]          = ts.SoLuongNhap * 10;
                    xlWorkSheet.Cells[begini, beginj + 7]          = ts.TyLeCL;
                    xlWorkSheet.Cells[begini, beginj + 8].Formula  = "=I" + begini + "-N" + begini;
                    xlWorkSheet.Cells[begini, beginj + 9].Formula  = "=J" + begini + "-N" + begini;
                    xlWorkSheet.Cells[begini, beginj + 10].Formula = "=K" + begini + "-N" + begini;
                    xlWorkSheet.Cells[begini, beginj + 11].Formula = "=L" + begini + "-N" + begini;
                    xlWorkSheet.Cells[begini, beginj + 12]         = ts.TyLeHM;
                    List <DTO.ChungTuGiam> k = db.ChungTuGiams.Where(p => p.MaTaiSan.Substring(0, 7) == ts.MaTaiSan.Substring(0, 7)).Select(p => p).ToList <DTO.ChungTuGiam>();

                    { } DTO.ChungTuGiam ctg = null;
                    if (k.Count > 0)
                    {
                        ctg = k[0];
                    }
                    if (ctg != null)
                    {
                        xlWorkSheet.Cells[begini, beginj + 13] = ctg.NgayGhiGiam.Value.Year.ToString();
                        xlWorkSheet.Cells[begini, beginj + 14] = ctg.NoiDung;
                        xlWorkSheet.Cells[begini, beginj + 15] = bll.GetSLThanhLy_DAL(ts.MaTaiSan.Substring(0, 7));
                        xlWorkSheet.Cells[begini, beginj + 16] = ctg.ThanhTien.ToString();
                        xlWorkSheet.Cells[begini, beginj + 17] = ctg.GhiChu;
                    }
                    begini++;
                }

                Range b = xlWorkSheet.Range[xlWorkSheet.Cells[11, 1], xlWorkSheet.Cells[begini, beginj + 17]];
                b.Borders.LineStyle = XlLineStyle.xlDot;
                b.Borders[XlBordersIndex.xlEdgeTop].LineStyle    = XlLineStyle.xlContinuous;
                b.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
                b.Borders[XlBordersIndex.xlEdgeLeft].LineStyle   = XlLineStyle.xlContinuous;
                b.Borders[XlBordersIndex.xlEdgeRight].LineStyle  = XlLineStyle.xlContinuous;
                xlWorkSheet.Columns.AutoFit();
                xlWorkSheet.Rows.AutoFit();

                Range x = xlWorkSheet.Range["E4:E4"];
                x.Merge();
                xlWorkSheet.Cells[4, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                x.Font.Bold   = true;
                x.Font.Size   = 22;
                x.Cells[1, 1] = "BẢNG KIỂM KÊ, ĐÁNH GIÁ TÀI SẢN NĂM 2018";

                x = xlWorkSheet.Range["C5:C5"];
                x.Merge();
                xlWorkSheet.Cells[5, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                x.Font.Bold   = true;
                x.Font.Size   = 16;
                x.Cells[1, 1] = "SỔ THEO DÕI TÀI SẢN CỐ ĐỊNH VÀ CÔNG CỤ, DỤNG CỤ, VRTMH TẠI NƠI SỬ DỤNG";

                xlWorkSheet.Cells[begini + 3, 2] = "  - Gồm : */ Thiết bị : ................mục ; */ Đồ gỗ, VRTNH :.............mục ";
                xlWorkSheet.Cells[begini + 3, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

                xlWorkSheet.Cells[begini + 4, 2] = "  - Sổ này có ..................trang, đánh số từ trang 01 đến ...........";
                xlWorkSheet.Cells[begini + 4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

                xlWorkSheet.Cells[begini + 5, 2] = "  - Ngày mở sổ:.......................";
                xlWorkSheet.Cells[begini + 5, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

                xlWorkSheet.Cells[begini + 6, 3]           = "Hiệu trưởng";
                xlWorkSheet.Cells[begini + 6, 3].Font.Bold = true;

                xlWorkSheet.Cells[begini + 6, 5]                     = "Kế toán tài sản";
                xlWorkSheet.Cells[begini + 6, 5].Font.Bold           = true;
                xlWorkSheet.Cells[begini + 5, beginj + 14]           = "Đà Nẵng, ngày        tháng      năm";
                xlWorkSheet.Cells[begini + 6, beginj + 14]           = "Người ghi sổ";
                xlWorkSheet.Cells[begini + 6, beginj + 14].Font.Bold = true;



                Marshal.ReleaseComObject(xlWorkSheet);
            }


            xlWorkBook.SaveAs(fullpath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

            xlWorkBook.Close(0);
            xlApp.Quit();

            Marshal.ReleaseComObject(xlWorkBook);
            Marshal.ReleaseComObject(xlApp);
            tbStatusBar.Text = "Working normally";
        }