public ActionResult CreateUpdate(UserProductCategory data)
{
try
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
if (data.lstma_phan_cap != null && data.lstma_phan_cap.Count() > 0)
{
foreach (string item in data.lstma_phan_cap)
{
var exist = dbConn.SingleOrDefault <UserProductCategory>("id_nguoi_dung={0} AND ma_phan_cap={1}", data.id_nguoi_dung, item);
if (exist == null)
{
var newdata = new UserProductCategory();
newdata.id_nguoi_dung = data.id_nguoi_dung;
newdata.ma_nguoi_dung = data.ma_nguoi_dung;
newdata.ma_phan_cap = item;
newdata.ngay_tao = DateTime.Now;
newdata.nguoi_tao = currentUser.ma_nguoi_dung;
newdata.ngay_cap_nhat = DateTime.Now;
newdata.nguoi_cap_nhat = currentUser.ma_nguoi_dung;
dbConn.Insert(newdata);
}
}
dbConn.Delete <UserProductCategory>("id_nguoi_dung={0} AND ma_phan_cap NOT IN(" + String.Join(",", data.lstma_phan_cap.Select(s => ("'" + s + "'"))) + ")", data.id_nguoi_dung);
}
else
{
dbConn.Delete <UserProductCategory>("id_nguoi_dung={0}", data.id_nguoi_dung);
}
}
return(Json(new { success = true, data = data }));
}
catch (Exception e)
{
return(Json(new { success = false, error = e.Message }));
}
}
public ActionResult Import()
{
var file = Request.Files["FileUpload"];
try
{
if (file == null || file.ContentLength == 0)
{
return(Json(new { success = false, message = "File rỗng." }));
}
var fileExtension = System.IO.Path.GetExtension(file.FileName);
if (fileExtension != ".xlsx" && fileExtension != ".xls")
{
return(Json(new { success = false, message = "File không không đúng định dạng excel *.xlsx,*.xls" }));
}
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.ma_nguoi_dung + "-" + datetime + file.FileName);
string errorFileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.ma_nguoi_dung + "-" + datetime + "-Error]" + file.FileName);
string linkerror = "[" + currentUser.ma_nguoi_dung + "-" + datetime + "-Error]" + file.FileName;
if (!System.IO.Directory.Exists(Server.MapPath("~/ExcelImport")))
{
System.IO.Directory.CreateDirectory(fileLocation);
}
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
file.SaveAs(fileLocation);
var rownumber = 2;
var total = 0;
var error = 0;
FileInfo fileInfo = new FileInfo(fileLocation);
var excelPkg = new ExcelPackage(fileInfo);
FileInfo template = new FileInfo(Server.MapPath(@"~\ExportExcelFile\User_Template.xlsx"));
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"];
int totalRows = oSheet.Dimension.End.Row;
using (var dbConn = OrmliteConnection.openConn())
{
for (int i = 2; i <= totalRows; i++)
{
if (oSheet.Cells[i, 1].Value == null)
{
i = totalRows;
}
else
{
string ma_nguoi_dung = oSheet.Cells[i, 1].Value != null ? oSheet.Cells[i, 1].Value.ToString().Trim() : "";
string domain = oSheet.Cells[i, 2].Value != null ? oSheet.Cells[i, 2].Value.ToString().Trim() : "";
string ma_nguoi_dung_domain = oSheet.Cells[i, 3].Value != null ? oSheet.Cells[i, 3].Value.ToString().Trim() : "";
string ma_nhan_vien = oSheet.Cells[i, 4].Value != null ? oSheet.Cells[i, 4].Value.ToString().Trim() : "";
string ten_nguoi_dung = oSheet.Cells[i, 5].Value != null ? oSheet.Cells[i, 5].Value.ToString().Trim() : "";
string gioi_tinh = oSheet.Cells[i, 6].Value != null ? oSheet.Cells[i, 6].Value.ToString().Split('-')[0].ToString().Trim() : "";
string email = oSheet.Cells[i, 7].Value != null ? oSheet.Cells[i, 7].Value.ToString().Trim() : "";
string dien_thoai = oSheet.Cells[i, 8].Value != null ? oSheet.Cells[i, 8].Value.ToString().Trim() : "";
string trang_thai = "true";
if (oSheet.Cells[i, 9].Value.ToString() == "Hoạt động")
{
trang_thai = "true";
}
else if (oSheet.Cells[i, 9].Value.ToString() == "Ngưng hoạt động")
{
trang_thai = "false";
}
string don_vi = oSheet.Cells[i, 10].Value != null ? oSheet.Cells[i, 10].Value.ToString().Trim() : "";
string don_vi_quan_ly = oSheet.Cells[i, 11].Value != null ? oSheet.Cells[i, 11].Value.ToString().Split('-')[0].ToString().Trim() : "";
string profit = oSheet.Cells[i, 12].Value != null ? oSheet.Cells[i, 12].Value.ToString().Split('-')[0].ToString().Trim() : "";
string chuc_danh = oSheet.Cells[i, 13].Value != null ? oSheet.Cells[i, 13].Value.ToString().Split('-')[0].ToString().Trim() : "";
string cap_bac = oSheet.Cells[i, 14].Value != null ? oSheet.Cells[i, 14].Value.ToString().Split('-')[0].ToString().Trim() : "";
string bc_dinh_bien = oSheet.Cells[i, 15].Value != null ? oSheet.Cells[i, 15].Value.ToString().Split('-')[0].ToString().Trim() : "";
string dia_chi = oSheet.Cells[i, 16].Value != null ? oSheet.Cells[i, 16].Value.ToString().Trim() : "";
string ghi_chu = oSheet.Cells[i, 17].Value != null ? oSheet.Cells[i, 17].Value.ToString().Trim() : "";
string ngay_vao_lam = oSheet.Cells[i, 18].Value != null ? oSheet.Cells[i, 18].Value.ToString().Trim() : "";
don_vi_quan_ly = don_vi_quan_ly.Replace(" ", "");
try
{
var itemeexit = dbConn.FirstOrDefault <User>(s => s.ma_nguoi_dung == ma_nguoi_dung);
var exituserBrand = dbConn.FirstOrDefault <UsersBranch>(s => s.ma_nguoi_dung == ma_nguoi_dung);
var itemubr = new UsersBranch();
var listBranch = dbConn.Select <Branch>();
if (itemeexit != null)
{
itemeexit.domain = domain;
itemeexit.ten_nguoi_dung = ten_nguoi_dung;
itemeexit.gioi_tinh = gioi_tinh;
itemeexit.email = email;
itemeexit.dien_thoai = dien_thoai;
itemeexit.trang_thai = trang_thai;
itemeexit.dia_chi = dia_chi;
itemeexit.ghi_chu = ghi_chu;
itemeexit.ngay_cap_nhat = DateTime.Now;
itemeexit.nguoi_cap_nhat = currentUser.ma_nguoi_dung;
if (exituserBrand != null)
{
dbConn.Update <User>(itemeexit);
dbConn.Update <UsersBranch>(exituserBrand);
}
else
{
itemubr.id_nguoi_dung = itemeexit.id;
itemubr.ma_nguoi_dung = ma_nguoi_dung;
itemubr.ma_chi_nhanh = don_vi_quan_ly;
itemubr.ngay_tao = DateTime.Now;
itemubr.nguoi_tao = currentUser.ma_nguoi_dung;
itemubr.ngay_cap_nhat = DateTime.Parse("1900-01-01");
itemubr.nguoi_cap_nhat = "";
dbConn.Insert <UsersBranch>(itemubr);
}
}
else
{
var item = new User();
var product_user = new UserProductCategory();
var userproductcate = dbConn.Select <ProductCategory>("Select id ,isnull(ma_phan_cap,'') as ma_phan_cap , isnull(ten_phan_cap,'') as ten_phan_cap From [ProductCategory]");
item.ma_nguoi_dung = ma_nguoi_dung;
item.domain = domain;
item.ten_nguoi_dung = ten_nguoi_dung;
item.gioi_tinh = gioi_tinh;
item.email = email;
item.dien_thoai = dien_thoai;
item.mat_khau = Helpers.GetMd5Hash.Generate(ma_nguoi_dung + "@123");
item.trang_thai = trang_thai;
item.dia_chi = dia_chi;
item.ghi_chu = ghi_chu;
item.ngay_tao = DateTime.Now;
item.nguoi_tao = currentUser.ma_nguoi_dung;
item.ngay_cap_nhat = DateTime.Parse("1900-01-01");
item.nguoi_cap_nhat = "";
dbConn.Insert <User>(item);
int id = (int)dbConn.GetLastInsertId();
itemubr.id_nguoi_dung = id;
itemubr.ma_nguoi_dung = ma_nguoi_dung;
itemubr.ma_chi_nhanh = don_vi_quan_ly;
itemubr.ngay_tao = DateTime.Now;
itemubr.nguoi_tao = currentUser.ma_nguoi_dung;
itemubr.ngay_cap_nhat = DateTime.Parse("1900-01-01");
itemubr.nguoi_cap_nhat = "";
dbConn.Insert <UsersBranch>(itemubr);
foreach (var itemuser in userproductcate)
{
product_user.id_nguoi_dung = id;
product_user.ma_nguoi_dung = item.ma_nguoi_dung;
product_user.ma_phan_cap = itemuser.ma_phan_cap;
product_user.ngay_tao = DateTime.Now;
product_user.nguoi_tao = currentUser.ma_nguoi_dung;
product_user.ngay_cap_nhat = DateTime.Parse("1900-01-01");
product_user.nguoi_cap_nhat = "";
dbConn.Insert(product_user);
}
total++;
rownumber++;
}
total++;
rownumber++;
}
catch (Exception e)
{
eSheet.Cells[rownumber, 1].Value = ma_nguoi_dung;
eSheet.Cells[rownumber, 2].Value = domain;
eSheet.Cells[rownumber, 3].Value = ma_nguoi_dung_domain;
eSheet.Cells[rownumber, 4].Value = ma_nhan_vien;
eSheet.Cells[rownumber, 5].Value = ten_nguoi_dung;
eSheet.Cells[rownumber, 6].Value = gioi_tinh;
eSheet.Cells[rownumber, 7].Value = email;
eSheet.Cells[rownumber, 8].Value = dien_thoai;
eSheet.Cells[rownumber, 9].Value = trang_thai;
eSheet.Cells[rownumber, 10].Value = don_vi;
eSheet.Cells[rownumber, 11].Value = don_vi_quan_ly;
eSheet.Cells[rownumber, 12].Value = profit;
eSheet.Cells[rownumber, 13].Value = chuc_danh;
eSheet.Cells[rownumber, 14].Value = cap_bac;
eSheet.Cells[rownumber, 15].Value = bc_dinh_bien;
eSheet.Cells[rownumber, 16].Value = dia_chi;
eSheet.Cells[rownumber, 17].Value = ghi_chu;
eSheet.Cells[rownumber, 18].Value = ngay_vao_lam;
eSheet.Cells[rownumber, 19].Value = e.Message;
rownumber++;
error++;
continue;
}
}
}
}
_excelPkg.Save();
return(Json(new { success = true, total = total, totalError = error, link = linkerror }));
}
catch (Exception ex)
{
return(Json(new { success = false, message = ex.Message }));
}
}
public ActionResult CreateUpdate(User data)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
try
{
if (data.id > 0)
{
if (accessDetail.sua)
{
var exist = dbConn.SingleOrDefault <User>("id ={0}", data.id);
exist.dia_chi = !string.IsNullOrEmpty(data.dia_chi) ? data.dia_chi : "";
exist.dien_thoai = !string.IsNullOrEmpty(data.dien_thoai) ? data.dien_thoai : "";
exist.ghi_chu = !string.IsNullOrEmpty(data.ghi_chu) ? data.ghi_chu : "";
exist.ten_nguoi_dung = data.ten_nguoi_dung;
exist.domain = data.domain;
exist.trang_thai = data.trang_thai;
exist.nguoi_cap_nhat = currentUser.ma_nguoi_dung;
exist.ngay_cap_nhat = DateTime.Now;
exist.gioi_tinh = !string.IsNullOrEmpty(data.gioi_tinh) ? data.gioi_tinh : "";
dbConn.Update(exist);
}
else
{
return(Json(new { success = false, error = "Bạn không có quyền cập nhập tài khoản" }));
}
}
else
{
if (accessDetail.them)
{
var exist = dbConn.SingleOrDefault <User>("ma_nguoi_dung={0} or email={1}", data.ma_nguoi_dung, data.email);
var userproductcate = dbConn.Select <ProductCategory>("Select id ,isnull(ma_phan_cap,'') as ma_phan_cap , isnull(ten_phan_cap,'') as ten_phan_cap From [ProductCategory]");
var product_user = new UserProductCategory();
if (exist != null)
{
return(Json(new { success = false, error = "Mã người dùng hoặc email đã tồn tại." }));
}
data.mat_khau = Helpers.GetMd5Hash.Generate(data.mat_khau);
data.ngay_tao = DateTime.Now;
data.nguoi_tao = currentUser.ma_nguoi_dung;
data.ngay_cap_nhat = DateTime.Parse("1900-01-01");
data.dia_chi = !string.IsNullOrEmpty(data.dia_chi) ? data.dia_chi : "";
data.dien_thoai = !string.IsNullOrEmpty(data.dien_thoai) ? data.dien_thoai : "";
data.ghi_chu = !string.IsNullOrEmpty(data.ghi_chu) ? data.ghi_chu : "";
dbConn.Insert(data);
data.id = (int)dbConn.GetLastInsertId();
int id = data.id;
foreach (var itemuser in userproductcate)
{
product_user.id_nguoi_dung = id;
product_user.ma_nguoi_dung = data.ma_nguoi_dung;
product_user.ma_phan_cap = itemuser.ma_phan_cap;
product_user.ngay_tao = DateTime.Now;
product_user.nguoi_tao = currentUser.ma_nguoi_dung;
product_user.ngay_cap_nhat = DateTime.Parse("1900-01-01");
product_user.nguoi_cap_nhat = "";
dbConn.Insert(product_user);
}
}
else
{
return(Json(new { success = false, error = "Bạn không có quyền tạo tài khoản" }));
}
}
dbConn.Delete <UserInGroup>("ma_nguoi_dung={0}", data.ma_nguoi_dung);
if (data.groupId != null && data.groupId.Count() > 0)
{
foreach (var item in data.groupId)
{
var exist = dbConn.SingleOrDefault <UserInGroup>("ma_nguoi_dung={0} AND id_nhom_nguoi_dung={1}", data.ma_nguoi_dung, item);
if (exist == null)
{
var userInGroup = new UserInGroup();
userInGroup.ma_nguoi_dung = data.ma_nguoi_dung;
userInGroup.id_nhom_nguoi_dung = item;
userInGroup.ngay_tao = DateTime.Now;
userInGroup.nguoi_tao = currentUser.ma_nguoi_dung;
userInGroup.ngay_cap_nhat = DateTime.Parse("1900-01-01");
dbConn.Insert(userInGroup);
}
}
//dbConn.Delete<UserInGroup>("ma_nguoi_dung = {0} AND id_nhom_nguoi_dung NOT IN (" + String.Join(",", data.groupId.Select(s => s)) + ")", data.ma_nguoi_dung);
}
dbConn.Delete <UsersBranch>("id_nguoi_dung={0}", data.id);
if (data.lstbranch != null)
{
foreach (string item in data.lstbranch)
{
var newdata = new UsersBranch();
newdata.id_nguoi_dung = data.id;
newdata.ma_nguoi_dung = data.ma_nguoi_dung;
newdata.ma_chi_nhanh = item;
newdata.ngay_tao = DateTime.Now;
newdata.nguoi_tao = currentUser.ma_nguoi_dung;
newdata.ngay_cap_nhat = DateTime.Now;
newdata.nguoi_cap_nhat = currentUser.ma_nguoi_dung;
dbConn.Insert(newdata);
}
}
}
catch (Exception ex)
{
return(Json(new { success = false, error = ex.Message }));
}
return(Json(new { success = true }));
}
}
