예제 #1
0
        public ActionResult ImportUnit()
        {
            IDbConnection dbConn = new OrmliteConnection().openConn();
            try
            {
                if (Request.Files["FileUpload"] != null && Request.Files["FileUpload"].ContentLength > 0)
                {
                    string fileExtension =
                        System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);

                    if (fileExtension == ".xlsx" || fileExtension == ".xls")
                    {

                            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
                            string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.UserID + "-" + datetime + Request.Files["FileUpload"].FileName);
                            string errorFileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.UserID + "-" + datetime + "-Error]" + Request.Files["FileUpload"].FileName);
                            string linkerror = "[" + currentUser.UserID + "-" + datetime + "-Error]" + Request.Files["FileUpload"].FileName;

                            if (System.IO.File.Exists(fileLocation))
                                System.IO.File.Delete(fileLocation);

                            Request.Files["FileUpload"].SaveAs(fileLocation);

                            var rownumber = 2;
                            var total = 0;
                            FileInfo fileInfo = new FileInfo(fileLocation);
                            var excelPkg = new ExcelPackage(fileInfo);
                            //FileInfo template = new FileInfo(Server.MapPath(errorFileLocation));
                            //template.CopyTo(errorFileLocation);
                            //FileInfo _fileInfo = new FileInfo(errorFileLocation);
                            //var _excelPkg = new ExcelPackage(_fileInfo);
                            ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets["Data"];
                            //ExcelWorksheet eSheet = _excelPkg.Workbook.Worksheets["Data"];
                            ExcelPackage pck = new ExcelPackage(new FileInfo(errorFileLocation));
                            ExcelWorksheet ws = pck.Workbook.Worksheets["Data"];
                            int totalRows = oSheet.Dimension.End.Row;
                            for (int i = 2; i <= totalRows; i++)
                            {
                                string ID = oSheet.Cells[i, 1].Value != null ? oSheet.Cells[i, 1].Value.ToString() : "";
                                string Name = oSheet.Cells[i, 2].Value != null ? oSheet.Cells[i, 2].Value.ToString() : "";
                                string Note = oSheet.Cells[i, 3].Value != null ? oSheet.Cells[i, 3].Value.ToString() : "";
                                //string Status = oSheet.Cells[i, 4].Value == "Đang hoạt động" ? "true" : "false";
                                string Status = "false";
                                if (oSheet.Cells[i, 6].Value != null)
                                {
                                    if (oSheet.Cells[i, 4].Value.ToString() == "Đang hoạt động")
                                    {
                                        Status = "true";
                                    }
                                }
                                try
                                {
                                    if (string.IsNullOrEmpty(Name))
                                    {
                                        ws.Cells["A" + 2].Value = Name;
                                        ws.Cells[rownumber, 14].Value = "Vui lòng nhập (*).";
                                        rownumber++;
                                    }
                                    else
                                    {
                                        var checkexists = dbConn.SingleOrDefault<DC_AD_Unit>("SELECT * FROM DC_AD_Unit WHERE UnitID = '" + ID + "'");
                                        if (checkexists != null)
                                        {
                                            checkexists.UnitID = ID;
                                            checkexists.UnitName = Name;
                                            checkexists.Status = Boolean.Parse(Status);
                                            checkexists.UpdatedAt = DateTime.Now;
                                            checkexists.UpdatedBy = currentUser.UserID;
                                            dbConn.Update<DC_AD_Unit>(checkexists);
                                        }
                                        else
                                        {
                                            string id = "";
                                            var checkID = dbConn.SingleOrDefault<DC_AD_Unit>("SELECT UnitID, Id FROM dbo.DC_AD_Unit ORDER BY Id DESC");
                                            if (checkID != null)
                                            {
                                                var nextNo = int.Parse(checkID.UnitID.Substring(3, checkID.UnitID.Length - 3)) + 1;
                                                id = "UIT" + String.Format("{0:00000000}", nextNo);
                                            }
                                            else
                                            {
                                                id = "UIT00000001";
                                            }
                                            var item = new DC_AD_Unit();
                                            item.UnitID = id;
                                            item.UnitName = !string.IsNullOrEmpty(Name) ? Name.Trim() : "";
                                            item.Note = !string.IsNullOrEmpty(Note) ? Note.Trim() : "";
                                            item.CreatedAt = DateTime.Now;
                                            item.CreatedBy = currentUser.UserID;
                                            item.UpdatedAt = DateTime.Parse("1900-01-01");
                                            item.UpdatedBy = "";
                                            item.Status = Boolean.Parse(Status);
                                            dbConn.Insert<DC_AD_Unit>(item);
                                        }
                                        total++;
                                    }
                                }
                                catch (Exception e)
                                {
                                    return Json(new { success = false, message = e.Message });
                                }
                            }
                            return Json(new { success = true, total = total, totalError = rownumber - 2, link = linkerror });
                        }

                    else
                    {
                        return Json(new { success = false, message = "Không phải là file Excel. *.xlsx" });
                    }
                }
                else
                {
                    return Json(new { success = false, message = "Không có file hoặc file không phải là Excel" });
                }
            }
            catch (Exception ex)
            {
                return Json(new { success = false, message = ex.Message });
            }
        }
예제 #2
0
        public ActionResult CreateUnit(DC_AD_Unit item)
        {
            IDbConnection db = new OrmliteConnection().openConn();
            try
            {
                var isExist = db.SingleOrDefault<DC_AD_Unit>("SELECT UnitID, Id FROM dbo.DC_AD_Unit Where UnitID ='" + item.UnitID + "'");
                if (userAsset.ContainsKey("Insert") && userAsset["Insert"] && item.CreatedAt == null && item.CreatedBy ==null)
                {
                    if (isExist != null)
                    {
                        return Json(new { success = false, message = "Đơn vị tính đã tồn tại." });
                    }
                    string id = "";
                    var checkID = db.SingleOrDefault<DC_AD_Unit>("SELECT UnitID, Id FROM dbo.DC_AD_Unit ORDER BY Id DESC");
                    if (checkID != null)
                    {
                        var nextNo = int.Parse(checkID.UnitID.Substring(3, checkID.UnitID.Length - 3)) + 1;
                        id = "UIT" + String.Format("{0:00000000}", nextNo);
                    }
                    else
                    {
                        id = "UIT00000001";
                    }
                    item.UnitID = id;
                    item.UnitName = !string.IsNullOrEmpty(item.UnitName) ? item.UnitName.Trim() : "";
                    item.Note = !string.IsNullOrEmpty(item.Note) ? item.Note.Trim() : "";
                    item.CreatedAt = DateTime.Now;
                    item.CreatedBy = currentUser.UserID;
                    item.UpdatedAt = DateTime.Parse("1900-01-01");
                    item.UpdatedBy = "";
                    item.Status = item.Status;
                    db.Insert<DC_AD_Unit>(item);

                    return Json(new { success = true, Code = item.UnitID, createdate = item.CreatedAt, createdby = item.CreatedBy });
                }
                else if (userAsset.ContainsKey("Update") && userAsset["Update"] && isExist != null)
                {
                    var success = db.Execute(@"UPDATE DC_AD_Unit SET Status = @Status,
                    Note = @Note,  UpdatedAt = @UpdatedAt, UpdatedBy = @UpdatedBy, UnitName = @UnitName
                    WHERE UnitID = '" + item.UnitID + "'", new
                    {
                        Status = item.Status,
                        //WHName = !string.IsNullOrEmpty(item.WHName) ? item.WHName.Trim() : "",
                        Note = !string.IsNullOrEmpty(item.Note) ? item.Note.Trim() : "",
                        UpdatedAt = DateTime.Now,
                        UpdatedBy = currentUser.UserID,
                        UnitName = !string.IsNullOrEmpty(item.UnitName) ? item.UnitName.Trim() : "",
                    }) == 1;
                    if (!success)
                    {
                        return Json(new { success = false, message = "Cập nhật không thành công." });
                    }

                    return Json(new { success = true });
                }
                else
                    return Json(new { success = false, message = "Bạn không có quyền" });
            }
            catch (Exception e)
            {
                log.Error(" ListPublication - Create - " + e.Message);
                return Json(new { success = false, message = e.Message });
            }
            finally { db.Close(); }
        }