//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(); }
}
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;
}