//public FileResult Export([DataSourceRequest]DataSourceRequest request)
        //{
        //    ExcelPackage pck = new ExcelPackage(new FileInfo(Server.MapPath("~/ExportTemplate/DanhMucAnPham.xlsx")));
        //    ExcelWorksheet ws = pck.Workbook.Worksheets["Data"];
        //    if (userAsset["Export"])
        //    {
        //        string whereCondition = "";
        //        if (request.Filters.Count > 0)
        //        {
        //            whereCondition = new KendoApplyFilter().ApplyFilter(request.Filters[0]);
        //        }
        //        IDbConnection db = new OrmliteConnection().openConn();
        //        var lstResult = db.Select<Products>(whereCondition).ToList();
        //        int rowNum = 2;
        //        foreach (var item in lstResult)
        //        {
        //            ws.Cells["A" + rowNum].Value = item.Code;
        //            ws.Cells["B" + rowNum].Value = item.Name;
        //            ws.Cells["C" + rowNum].Value = item.Size;
        //            ws.Cells["D" + rowNum].Value = item.VATPrice;
        //            ws.Cells["E" + rowNum].Value = item.Type;
        //            ws.Cells["F" + rowNum].Value = item.Unit;
        //            ws.Cells["G" + rowNum].Value = item.WHID;
        //            ws.Cells["H" + rowNum].Value = item.WHLID;
        //            ws.Cells["I" + rowNum].Value = item.ShapeTemplate;
        //            if (item.Status == true)
        //            {
        //                ws.Cells["J" + rowNum].Value = "Đang hoạt động";
        //            }
        //            else
        //            {
        //                ws.Cells["J" + rowNum].Value = "Ngưng hoạt động";
        //            }
        //            ws.Cells["K" + rowNum].Value = item.CreatedBy;
        //            ws.Cells["L" + rowNum].Value = item.CreatedAt;
        //            ws.Cells["M" + rowNum].Value = item.UpdatedBy;
        //            if (item.UpdatedAt != DateTime.Parse("1900-01-01"))
        //            {
        //                ws.Cells["N" + rowNum].Value = item.UpdatedAt;
        //            }
        //            else
        //            {
        //                ws.Cells["N" + rowNum].Value = "";
        //            }
        //            rowNum++;
        //        }
        //        db.Close();
        //    }
        //    else
        //    {
        //        ws.Cells["A2:E2"].Merge = true;
        //        ws.Cells["A2"].Value = "Bạn không có quyền";
        //    }
        //    MemoryStream output = new MemoryStream();
        //    pck.SaveAs(output);
        //    return File(output.ToArray(), //The binary data of the XLS file
        //                "application/vnd.ms-excel", //MIME type of Excel files
        //                "DanhMucAnPham_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx");     //Suggested file name in the "Save as" dialog which will be displayed to the end user
        //}
        public ActionResult ImportData()
        {
            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")
                    {
                        IDbConnection dbConn = new OrmliteConnection().openConn();
                        using (var dbTrans = dbConn.OpenTransaction(IsolationLevel.ReadCommitted))
                        {
                            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 Size = oSheet.Cells[i, 3].Value != null ? oSheet.Cells[i, 3].Value.ToString() : "";
                                string Priece = oSheet.Cells[i, 4].Value != null ? oSheet.Cells[i, 4].Value.ToString() : "0";
                                string Type = oSheet.Cells[i, 5].Value != null ? oSheet.Cells[i, 5].Value.ToString() : "";
                                string Unit = oSheet.Cells[i, 6].Value != null ? oSheet.Cells[i, 6].Value.ToString() : "";
                                string[] UnitID = Unit.Split('/');
                                string WH = oSheet.Cells[i, 7].Value != null ? oSheet.Cells[i, 7].Value.ToString() : "";
                                string[] WHID = WH.Split('/');
                                string WHL = oSheet.Cells[i, 8].Value != null ? oSheet.Cells[i, 8].Value.ToString() : "";
                                string[] WHLID = WHL.Split('/');
                                string Templete = oSheet.Cells[i, 9].Value != null ? oSheet.Cells[i, 9].Value.ToString() : "";
                                //string Status = oSheet.Cells[i, 9].Value != null ? oSheet.Cells[i, 9].Value.ToString() : "Ngưng hoạt động";
                                string Status = "false";
                                if (oSheet.Cells[i, 10].Value != null)
                                {
                                    if (oSheet.Cells[i, 10].Value.ToString() == "Đang hoạt động")
                                    {
                                        Status = "true";
                                    }
                                }
                                try
                                {
                                    if (string.IsNullOrEmpty(Name) || string.IsNullOrEmpty(Size) || string.IsNullOrEmpty(Priece))
                                    {
                                        ws.Cells["A" + 2].Value = Name;
                                        ws.Cells[rownumber, 14].Value = "Vui lòng nhập (*).";
                                        rownumber++;
                                    }
                                    else
                                    {
                                        var checkexists = dbConn.SingleOrDefault<Products>("SELECT * FROM Products WHERE Code = '" + ID + "'");
                                        if (checkexists != null)
                                        {
                                            checkexists.Code = ID;
                                            checkexists.Name = Name;
                                            checkexists.Size = Name;
                                            checkexists.Price = int.Parse(Priece)/1.1;
                                            checkexists.VATPrice = int.Parse(Priece);
                                            checkexists.Type = Type;
                                            checkexists.Unit = Unit != null ? UnitID[UnitID.Count() - 1] : "";
                                            checkexists.WHID = WH != null ? WHID[WHID.Count() - 1] : "";
                                            checkexists.WHLID = WHL != null ? WHLID[WHLID.Count() - 1] : "";
                                            checkexists.ShapeTemplate = Templete;
                                            checkexists.Status = Boolean.Parse(Status);
                                            checkexists.UpdatedAt = DateTime.Now;
                                            checkexists.UpdatedBy = currentUser.UserID;
                                            dbConn.Update<Products>(checkexists);
                                        }
                                        else
                                        {
                                            string id = "";
                                            var checkID = dbConn.SingleOrDefault<Products>("SELECT Code, Id FROM dbo.Products ORDER BY Id DESC");
                                            if (checkID != null)
                                            {
                                                var nextNo = int.Parse(checkID.Code.Substring(2, checkID.Code.Length - 2)) + 1;
                                                id = "AD" + String.Format("{0:00000000}", nextNo);
                                            }
                                            else
                                            {
                                                id = "AD00000001";
                                            }
                                            var item = new Products();
                                            item.Code = ID;
                                            item.Name = Name;
                                            item.Size = Name;
                                            item.Price = int.Parse(Priece) / 1.1;
                                            item.VATPrice = int.Parse(Priece);
                                            item.Type = Type;
                                            item.Unit = Unit != null ? UnitID[UnitID.Count() - 1] : "";
                                            item.WHID = WH != null ? WHID[WHID.Count() - 1] : "";
                                            item.WHLID = WHL != null ? WHLID[WHLID.Count() - 1] : "";
                                            item.ShapeTemplate = Templete;
                                            item.Status = Boolean.Parse(Status);
                                            item.CreatedAt = DateTime.Now;
                                            item.CreatedBy = currentUser.UserID;
                                            item.UpdatedAt = DateTime.Parse("1900-01-01");
                                            item.UpdatedBy = "";
                                            item.Status = Boolean.Parse(Status);
                                            dbConn.Insert<Products>(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 });
            }
        }
        public ActionResult Create(Products item)
        {
            IDbConnection db = new OrmliteConnection().openConn();
            try
            {
                var isExist = db.SingleOrDefault<Products>("SELECT Code, Id FROM dbo.Products Where Code ='"+item.Code+"'");
                if (userAsset.ContainsKey("Insert") && userAsset["Insert"] && item.CreatedAt == null && item.CreatedBy == null)
                {
                    if (isExist != null)
                    {
                        return Json(new { success = false, message = "Ấn phẩm đã tồn tại." });
                    }
                    string id = "";
                    var checkID = db.SingleOrDefault<Products>("SELECT Code, Id FROM dbo.Products ORDER BY Id DESC");
                    if (checkID != null)
                    {
                        var nextNo = int.Parse(checkID.Code.Substring(2, checkID.Code.Length - 2)) + 1;
                        id = "AD" + String.Format("{0:00000000}", nextNo);
                    }
                    else
                    {
                        id = "AD00000001";
                    }
                    item.Code = id;
                    item.Name = !string.IsNullOrEmpty(item.Name) ? item.Name.Trim() : "";
                    item.Price = item.VATPrice / 1.1;
                    item.VATPrice = item.VATPrice;
                    item.Size = !string.IsNullOrEmpty(item.Size) ? item.Size.Trim() : ""; ;
                    item.Unit = !string.IsNullOrEmpty(item.Unit) ? item.Unit.Trim() : ""; ;
                    item.Type = !string.IsNullOrEmpty(item.Type) ? item.Type.Trim() : ""; ;
                    item.WHID = !string.IsNullOrEmpty(item.WHID) ? item.WHID : "";
                    item.WHLID = !string.IsNullOrEmpty(item.WHLID) ? item.WHLID : "";
                    item.Desc = !string.IsNullOrEmpty(item.Desc) ? item.Desc.Trim() : "";
                    item.ShapeTemplate = !string.IsNullOrEmpty(item.ShapeTemplate) ? item.ShapeTemplate.Trim() : "";
                    item.CreatedAt = DateTime.Now;
                    item.CreatedBy = currentUser.UserID;
                    item.UpdatedAt = DateTime.Parse("1900-01-01");
                    item.UpdatedBy = "";
                    item.Status = item.Status;
                    db.Insert<Products>(item);

                    return Json(new { success = true, Code = item.Code, createdat = item.CreatedAt, createdby = item.CreatedBy });
                }
                else if (userAsset.ContainsKey("Update") && userAsset["Update"] && isExist != null)
                {
                    var success = db.Execute(@"UPDATE Products SET Status = @Status, VATPrice = @VATPrice, Size= @Size, Unit=@Unit,Type=@Type, WHID=@WHID, WHLID=@WHLID,
                    ShapeTemplate = @ShapeTemplate, UpdatedAt = @UpdatedAt,UpdatedBy =@UpdatedBy, Price=@Price,[Desc]=@Desc, Name = @Name  WHERE Code = '" + item.Code + "'", new
                    {
                        Status = item.Status,
                        Price = item.VATPrice / 1.1,
                        VATPrice = item.VATPrice,
                        Size = !string.IsNullOrEmpty(item.Size) ? item.Size.Trim() : "",
                        Unit = !string.IsNullOrEmpty(item.Unit) ? item.Unit.Trim() : "",
                        Type = !string.IsNullOrEmpty(item.Type) ? item.Type.Trim() : "",
                        WHID = !string.IsNullOrEmpty(item.WHID) ? item.WHID : "",
                        WHLID = !string.IsNullOrEmpty(item.WHLID) ? item.WHLID : "",
                        ShapeTemplate = !string.IsNullOrEmpty(item.ShapeTemplate) ? item.ShapeTemplate.Trim() : "",
                        UpdatedAt = DateTime.Now,
                        UpdatedBy = currentUser.UserID,
                        Desc = !string.IsNullOrEmpty(item.Desc) ? item.Desc.Trim() : "",
                        Name = !string.IsNullOrEmpty(item.Name) ? item.Name.Trim() : "",
                    }) == 1;
                    if (!success)
                    {
                        return Json(new { success = false, message = "Cập nhật không thành công." });
                    }
                    //item.Price = item.VATPrice / 1.1;
                    //item.VATPrice = item.VATPrice;
                    //item.Size = !string.IsNullOrEmpty(item.Size) ? item.Size.Trim() : ""; ;
                    //item.Unit = !string.IsNullOrEmpty(item.Unit) ? item.Unit.Trim() : ""; ;
                    //item.Type = !string.IsNullOrEmpty(item.Type) ? item.Type.Trim() : ""; ;
                    //item.WHID = !string.IsNullOrEmpty(item.WHID) ? item.WHID : "";
                    //item.WHLID = !string.IsNullOrEmpty(item.WHLID) ? item.WHLID : "";
                    //item.Desc = !string.IsNullOrEmpty(item.Desc) ? item.Desc.Trim() : "";
                    //item.ShapeTemplate = !string.IsNullOrEmpty(item.ShapeTemplate) ? item.ShapeTemplate.Trim() : "";
                    //item.UpdatedAt = DateTime.Now;
                    //item.UpdatedBy = currentUser.UserID;
                    //item.Status = item.Status;
                    //db.Update<Products>(item);
                    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(); }
        }
Exemple #3
0
        public List<Products> GetStockReportInOut(int page, int pageSize, string whereCondition, DateTime from, DateTime end)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(new SqlParameter("@Page", page));
            param.Add(new SqlParameter("@PageSize", pageSize));
            param.Add(new SqlParameter("@WhereCondition", whereCondition));
            param.Add(new SqlParameter("@FromDate", from));
            param.Add(new SqlParameter("@EndDate", end));
            DataTable dt = new SqlHelper().ExecuteQuery("p_Report_StockInOut", param);
            var lst = new List<Products>();
            foreach (DataRow row in dt.Rows)
            {
                var item = new Products();
                item.Code = !row.IsNull("Code") ? row["Code"].ToString() : "";
                item.Name = !row.IsNull("Name") ? row["Name"].ToString() : "";
                item.UnitID = !row.IsNull("UnitID") ? row["UnitID"].ToString() : "";
                item.UnitName = !row.IsNull("UnitName") ? row["UnitName"].ToString() : "";
                item.WHID = !row.IsNull("WHID") ? row["WHID"].ToString() : "";
                item.WHName = !row.IsNull("WHName") ? row["WHName"].ToString() : "";
                item.WHLID = !row.IsNull("WHLID") ? row["WHLID"].ToString() : "";
                item.WHLName = !row.IsNull("WHLName") ? row["WHLName"].ToString() : "";
                item.WHLID = !row.IsNull("WHLID") ? row["WHLID"].ToString() : "";
                item.QtyIn = !row.IsNull("QtyIn") ? double.Parse(row["QtyIn"].ToString()) : 0;
                item.QtyOut = !row.IsNull("QtyOut") ? double.Parse(row["QtyOut"].ToString()) : 0;
                item.QtyAvailable = !row.IsNull("QtyAvailable") ? double.Parse(row["QtyAvailable"].ToString()) : 0;
                item.UpdatedAt = !row.IsNull("UpdatedAt") ? DateTime.Parse(row["UpdatedAt"].ToString()) : DateTime.Parse("1900-01-01");
                item.CreatedAt = !row.IsNull("CreatedAt") ? DateTime.Parse(row["CreatedAt"].ToString()) : DateTime.Parse("1900-01-01");
                item.UpdatedBy = !row.IsNull("UpdatedBy") ? row["UpdatedBy"].ToString() : "";
                item.CreatedBy = !row.IsNull("CreatedBy") ? row["CreatedBy"].ToString() : "";
                item.Status = !row.IsNull("Status") ? Convert.ToBoolean(row["Status"].ToString()) : false;

                lst.Add(item);
            }
            return lst;
        }