public JsonResult SaveData(TBL_EMPLOYEE item)
{
string flag = "";
try
{
item.IsLeaveOut = item.IsLeaveOut == true ? false : true;
item.PositionID1 = item.PositionID1.ToString() == "-1" ? null : item.PositionID1;
item.PositionID2 = item.PositionID2.ToString() == "-1" ? null : item.PositionID2;
item.PositionID3 = item.PositionID3.ToString() == "-1" ? null : item.PositionID3;
DA_Employee.Instance.Insert(item);
//Ghi log
WriteLog("Employee", "Create", null, item);
flag = "1";
return(Json(flag, JsonRequestBehavior.AllowGet));
}
catch
{
flag = "-1";
throw;
}
}
public JsonResult DeleteEmployee(long ID)
{
DA_Employee.Instance.Delete(ID);
//Ghi log
TBL_EMPLOYEE oldEmployee = DA_Employee.Instance.GetById(ID);
WriteLog("Employee", "Delete", oldEmployee, null);
return(Json(1, JsonRequestBehavior.AllowGet));
}
public ActionResult Edit(int EmployeeID)
{
GetComboBox();
TBL_EMPLOYEE cus = DA_Employee.Instance.GetById(EmployeeID);
if (cus == null)
{
return(HttpNotFound());
}
return(View(cus));
}
public bool CheckBeforeDelete(int PositionID)
{
TBL_EMPLOYEE Employee = DA_Employee.Instance.GetAll().FirstOrDefault(x => x.PositionID1 == PositionID || x.PositionID2 == PositionID || x.PositionID3 == PositionID);
if (Employee != null)
{
return(false);
}
return(true);
}
public JsonResult AddOrUpdateEntity(TBL_EMPLOYEE item, bool isEdit)
{
if (!string.IsNullOrWhiteSpace(item.FullName) && ((isEdit && item.EmployeeID > 0) || !isEdit))
{
try
{
int result = isEdit ? DA_Employee.Instance.Update(item) : DA_Employee.Instance.Insert(item);
return(Json(result > 0 ? 1 : 0));
}
catch (Exception ex) { }
}
return(Json(0));
}
public ActionResult Edit(TBL_EMPLOYEE item)
{
try
{
DA_Employee.Instance.Update(item);
//Ghi log
TBL_EMPLOYEE oldEmployee = DA_Employee.Instance.GetById(item.EmployeeID);
WriteLog("Employee", "Edit", oldEmployee, item);
return(RedirectToAction("Index"));
}
catch
{
GetComboBox(); return(View());
}
}
public JsonResult DeleteMultiEmployee(string lstEmployeeID)
{
var lstID = JArray.Parse(lstEmployeeID);
List <TBL_EMPLOYEE> lstEmployee = new List <TBL_EMPLOYEE>();
foreach (var item in lstID)
{
TBL_EMPLOYEE Employee = DA_Employee.Instance.GetById(int.Parse(item.ToString()));
lstEmployee.Add(Employee);
DA_Employee.Instance.Delete(int.Parse(item.ToString()));
}
//Ghi log
WriteLog("Employee", "MultiDelete", lstEmployee, null);
return(Json(true, JsonRequestBehavior.AllowGet));
}
public JsonResult delete()
{
try
{
//jQuery DataTables Param
string id = (Request.Form.GetValues("id").FirstOrDefault() == null
? ""
: Request.Form.GetValues("id").FirstOrDefault().ToString());
if (id.All(char.IsDigit))
{
TBL_EMPLOYEE employee = DA_Employee.Instance.GetById(Convert.ToInt32(id));
return(Json(employee == null ? 0 : DA_Employee.Instance.Delete(employee)));
}
return(Json(0));
}
catch (Exception ex)
{
return(Json(0));
}
}
public ActionResult Index(HttpPostedFileBase fileUpload)
{
if (!CheckPermission("Employee", 1))
{
return(RedirectToAction("Login", "Login", "Login"));
}
string path = "";
OleDbConnection connection = new OleDbConnection();
DataTable dt;
DataTable dt_2;
try
{
if (fileUpload != null && fileUpload.ContentLength > 0)
{
if (fileUpload != null && fileUpload.ContentLength > 0)
{
path = Path.Combine(Server.MapPath(@"~/Content/Excel/"), fileUpload.FileName);
fileUpload.SaveAs(path);
string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
connection.ConnectionString = excelConnectionString;
OleDbCommand command = new OleDbCommand("select * from [Employee$]", connection);
OleDbCommand command_2 = new OleDbCommand("select Distinct(EmployeeCode) from [Employee$]", connection);
connection.Open();
OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = command;
OleDbDataAdapter oleAdapter_2 = new OleDbDataAdapter();
oleAdapter_2.SelectCommand = command_2;
// Sao chép các dòng dữ liệu từ file excel vào Datatable
dt = new DataTable();
oleAdapter.FillSchema(dt, SchemaType.Source);
oleAdapter.Fill(dt);
dt_2 = new DataTable();
oleAdapter_2.FillSchema(dt_2, SchemaType.Source);
oleAdapter_2.Fill(dt_2);
// Đóng các kết nối lại
command.Dispose();
command = null;
oleAdapter.Dispose();
oleAdapter = null;
command_2.Dispose();
command_2 = null;
oleAdapter_2.Dispose();
oleAdapter_2 = null;
connection.Dispose();
connection = null;
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
bool Error = false;
#region Khai báo chuỗi gán lỗi
string CheckFullNameNull = "", CheckFullNameLenght = "";
string CheckEmployeeCodeExist = "", CheckEmployeeCodeLenght = "";
string CheckContractNoExist = "", CheckContractNoLenght = "";
string CheckCardNumberExist = "", CheckCardNumberLenght = "";
string CheckDepartmentCodeExist = "", CheckDepartmentCodeLenght = "";
string CheckPositionCodeExist_1 = "", CheckPositionCodeLenght_1 = "";
string CheckPositionCodeExist_2 = "", CheckPositionCodeLenght_2 = "";
string CheckPositionCodeExist_3 = "", CheckPositionCodeLenght_3 = "";
#endregion
#region Kiểm tra rỗng và độ dài chuỗi
for (int i = 0; i < dt.Rows.Count; i++)
{
string FullName = dt.Rows[i][0].ToString();
string EmployeeCode = dt.Rows[i][1].ToString();
string ContractNo = dt.Rows[i][2].ToString();
string CardNumber = dt.Rows[i][3].ToString();
string DepartmentCode = dt.Rows[i][4].ToString();
string PositionCode1 = dt.Rows[i][5].ToString();
string PositionCode2 = dt.Rows[i][6].ToString();
string PositionCode3 = dt.Rows[i][7].ToString();
if (FullName != "" || EmployeeCode != "" || ContractNo != "" || CardNumber != "" || DepartmentCode != "" || PositionCode1 != "" || PositionCode2 != "" || PositionCode3 != "")
{
// Check FullName
if (string.IsNullOrEmpty(FullName))
{
CheckFullNameNull += (i + 2) + ", ";
Error = true;
}
if (FullName.Length > 200)
{
CheckFullNameLenght += (i + 2) + ", ";
Error = true;
}
//Check Employee Code
if (EmployeeCode != "")
{
if (EmployeeCode.Length > 20)
{
CheckEmployeeCodeLenght += (i + 2) + ", ";
Error = true;
}
if (!DA_Employee.Instance.CheckEmployeeCodeNotExist(EmployeeCode))
{
CheckEmployeeCodeExist += (i + 2) + ", ";
Error = true;
}
}
//Check ContractNo
if (ContractNo != "")
{
if (ContractNo.Length > 20)
{
CheckContractNoLenght += (i + 2) + ", ";
Error = true;
}
if (!DA_Employee.Instance.CheckEmployeeContractNoExist(ContractNo))
{
CheckContractNoExist += (i + 2) + ", ";
Error = true;
}
}
//Card Number
if (CardNumber != "")
{
if (CardNumber.Length > 20)
{
CheckCardNumberLenght += (i + 2) + ", ";
Error = true;
}
}
//Check Department Code
if (DepartmentCode != "")
{
if (DepartmentCode.Length > 20)
{
CheckDepartmentCodeLenght += (i + 2) + ", ";
Error = true;
}
if (DA_Department.Instance.CheckDepartmentCodeExist(DepartmentCode))
{
CheckDepartmentCodeExist += (i + 2) + ", ";
Error = true;
}
}
//Check Position Code 1
if (PositionCode1 != "")
{
if (PositionCode1.Length > 20)
{
CheckPositionCodeLenght_1 += (i + 2) + ", ";
Error = true;
}
if (DA_Position.Instance.CheckPositionCodeNotExist(PositionCode1))
{
CheckPositionCodeExist_1 += (i + 2) + ", ";
Error = true;
}
}
//Check Position Code 2
if (PositionCode2 != "")
{
if (PositionCode2.Length > 20)
{
CheckPositionCodeLenght_2 += (i + 2) + ", ";
Error = true;
}
if (DA_Position.Instance.CheckPositionCodeNotExist(PositionCode2))
{
CheckPositionCodeExist_2 += (i + 2) + ", ";
Error = true;
}
}
//Check Position Code 3
if (PositionCode3 != "")
{
if (PositionCode3.Length > 20)
{
CheckPositionCodeLenght_3 += (i + 2) + ", ";
Error = true;
}
if (DA_Position.Instance.CheckPositionCodeNotExist(PositionCode3))
{
CheckPositionCodeExist_3 += (i + 2) + ", ";
Error = true;
}
}
}
}
#endregion
#region Kiểm tra Card Number trùng trong excel
if (dt.Rows.Count > dt_2.Rows.Count)
{
string lineSameCardNumber = "";
int count = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
if (dt.Rows[i]["CardNumber"].ToString() == dt.Rows[j]["CardNumber"].ToString() && (dt.Rows[i]["CardNumber"].ToString() != ""))
{
if (i != j)
{
lineSameCardNumber += (i + 2) + ", " + (j + 2) + ", ";
count++;
}
}
}
}
if (count > 1)
{
CheckCardNumberExist += lineSameCardNumber;
Error = true;
}
}
#endregion
#region Gán Error vào Viewbag
if (Error == true)
{
ViewBag.errorDetails = "<div class='alert alert-danger alert-dismissable'>" +
//Full Name
(CheckFullNameNull != "" ? "<p>Vui lòng nhập Full Name dòng thứ: " + CheckFullNameNull + "</p>" : "") +
(CheckFullNameLenght != "" ? "<p>Vui lòng nhập Full Name không vượt quá 200 ký tự dòng thứ: " + CheckFullNameLenght + "</p>" : "") +
//Employee
(CheckEmployeeCodeLenght != "" ? "<p>Vui lòng nhập Employee Code không vượt quá 20 ký tự dòng thứ: " + CheckEmployeeCodeLenght + "</p>" : "") +
(CheckEmployeeCodeExist != "" ? "<p>Employee Code đã được sử dụng, vui lòng nhập lại dòng thứ: " + CheckEmployeeCodeExist + "</p>" : "") +
//Contract
(CheckContractNoLenght != "" ? "<p>Vui lòng nhập ContractNo không vượt quá 20 ký tự dòng thứ: " + CheckContractNoLenght + "</p>" : "") +
(CheckContractNoExist != "" ? "<p>ContractNo đã được sử dụng, vui lòng nhập lại dòng thứ: " + CheckContractNoExist + "</p>" : "") +
//Card Number
(CheckCardNumberLenght != "" ? "<p>Vui lòng nhập Card Number không vượt quá 20 ký tự dòng thứ: " + CheckCardNumberLenght + "</p>" : "") +
(CheckCardNumberExist != "" ? "<p>Card Number bị trùng nhau, vui lòng nhập lại dòng thứ: " + CheckCardNumberExist + "</p>" : "") +
//Department
(CheckDepartmentCodeLenght != "" ? "<p>Vui lòng nhập Department Code không vượt quá 20 ký tự dòng thứ: " + CheckDepartmentCodeLenght + "</p>" : "") +
(CheckDepartmentCodeExist != "" ? "<p>Không tìm thấy Department Code, vui lòng nhập lại dòng thứ: " + CheckDepartmentCodeExist + "</p>" : "") +
//Position 1
(CheckPositionCodeLenght_1 != "" ? "<p>Vui lòng nhập Position Code 1 không vượt quá 20 ký tự dòng thứ: " + CheckPositionCodeLenght_1 + "</p>" : "") +
(CheckPositionCodeExist_1 != "" ? "<p>Không tìm thấy Position Code 1, vui lòng nhập lại dòng thứ: " + CheckPositionCodeExist_1 + "</p>" : "") +
//Position 2
(CheckPositionCodeLenght_2 != "" ? "<p>Vui lòng nhập Position Code 2 không vượt quá 20 ký tự dòng thứ: " + CheckPositionCodeLenght_2 + "</p>" : "") +
(CheckPositionCodeExist_2 != "" ? "<p>Không tìm thấy Position Code 2, vui lòng nhập lại dòng thứ: " + CheckPositionCodeExist_2 + "</p>" : "") +
//Position 3
(CheckPositionCodeLenght_3 != "" ? "<p>Vui lòng nhập Position Code 3 không vượt quá 20 ký tự dòng thứ: " + CheckPositionCodeLenght_3 + "</p>" : "") +
(CheckPositionCodeExist_3 != "" ? "<p>Không tìm thấy Position Code 3, vui lòng nhập lại dòng thứ: " + CheckPositionCodeExist_3 + "</p>" : "") +
"</div>";
}
#endregion
#region Nhập data
if (Error == false)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string FullName = dt.Rows[i][0].ToString();
string EmployeeCode = dt.Rows[i][1].ToString();
string ContractNo = dt.Rows[i][2].ToString();
string CardNumber = dt.Rows[i][3].ToString();
string DepartmentCode = dt.Rows[i][4].ToString();
string PositionCode1 = dt.Rows[i][5].ToString();
string PositionCode2 = dt.Rows[i][6].ToString();
string PositionCode3 = dt.Rows[i][7].ToString();
if (FullName != "" || EmployeeCode != "" || ContractNo != "" || CardNumber != "" || DepartmentCode != "" || PositionCode1 != "" || PositionCode2 != "" || PositionCode3 != "")
{
TBL_EMPLOYEE Employee = new TBL_EMPLOYEE();
Employee.FullName = FullName == "" ? null : FullName;
Employee.EmployeeCode = EmployeeCode == "" ? null : EmployeeCode;
Employee.ContractNo = ContractNo == "" ? null : ContractNo;
Employee.DepartmentID = DA_Department.Instance.GetDepartmentIDByCode(DepartmentCode);
Employee.PositionID1 = DA_Position.Instance.GetPositionIDByCode(PositionCode1);
Employee.PositionID2 = DA_Position.Instance.GetPositionIDByCode(PositionCode2);
Employee.PositionID3 = DA_Position.Instance.GetPositionIDByCode(PositionCode3);
Employee.IsLeaveOut = false;
if (CardNumber == "" || DA_Employee.Instance.CheckEmployeeCardNumbertExist(CardNumber))
{
Employee.CardNumber = CardNumber == "" ? null : CardNumber;
DA_Employee.Instance.Insert(Employee);
}
else
{
TBL_EMPLOYEE tbl_Employee = DA_Employee.Instance.GetAll().FirstOrDefault(x => x.CardNumber == CardNumber);
Employee.CardNumber = CardNumber;
Employee.EmployeeID = tbl_Employee.EmployeeID;
tbl_Employee = Employee;
DA_Employee.Instance.Update(tbl_Employee);
}
}
}
}
#endregion
}
}
return(View());
}
catch
{
ViewBag.errorDetails = "<div class='alert alert-danger alert-dismissable'>File excel không hợp lệ, vui lòng kiểm tra lại hoặc có thể tại file mẫu tại đây <a href='~/Content/Excel/FileMau/01_Import_Product.xlsx'><span class='fa fa-cloud-download'></span></a></div>";
if (connection != null)
{
connection.Close();
connection.Dispose();
}
if (path != "")
{
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
}
return(View());
}
}
public HttpResponseMessage EmployeeAdd(EmployeeModel _empModel)
{
try
{
bool conn = false;
conn = db.Database.Exists();
if (!conn)
{
ConnectionTools.changeToLocalDB(db);
conn = db.Database.Exists();
}
if (conn)
{
TBL_EMPLOYEE emp = new TBL_EMPLOYEE();
emp.ADDRESS_1 = _empModel.ADDRESS_1;
emp.ADDRESS_2 = _empModel.ADDRESS_2;
emp.BUSINESS_LOCATION = _empModel.BUSINESS_LOCATION;
emp.CITY = _empModel.CITY;
emp.COMPANY_ID = _empModel.COMPANY_ID;
emp.DATE_OF_JOIN = _empModel.DATE_OF_JOIN;
emp.DEPARTMENT = _empModel.DEPARTMENT;
emp.DOB = _empModel.DOB;
emp.EMAIL = _empModel.EMAIL;
emp.EMPLOYEE_CODE = _empModel.EMPLOYEE_CODE;
emp.EMPLOYEE_DESIGNATION = _empModel.EMPLOYEE_DESIGNATION;
emp.FAX_NO = _empModel.FAX_NO;
emp.FIRST_NAME = _empModel.FIRST_NAME;
emp.GENDER = _empModel.GENDER;
emp.IMAGE = _empModel.IMAGE;
emp.IS_APPOINTMENT = _empModel.IS_APPOINTMENT;
emp.IS_APPROVE_ACCESS_VAI_SMS = _empModel.IS_APPROVE_ACCESS_VAI_SMS;
emp.IS_ATTACHED_INVOICE = _empModel.IS_ATTACHED_INVOICE;
emp.IS_NOT_AN_EMPLOYEE = _empModel.IS_NOT_AN_EMPLOYEE;
emp.IS_REQUEST_VAI_SMS = _empModel.IS_REQUEST_VAI_SMS;
emp.LAST_NAME = _empModel.LAST_NAME;
emp.MARITAL_STATUS = _empModel.MARITAL_STATUS;
emp.MAX_SPOT_DISCOUNT = _empModel.MAX_SPOT_DISCOUNT;
emp.MIDDLE_NAME = _empModel.MIDDLE_NAME;
emp.MOBILE_NO = _empModel.MOBILE_NO;
emp.PIN_NO = _empModel.PIN_NO;
emp.SEARCH_CODE = _empModel.SEARCH_CODE;
emp.STATE = _empModel.STATE;
emp.TELEPHONE_NO = _empModel.TELEPHONE_NO;
emp.WEBSITE = _empModel.WEBSITE;
emp.WORKING_SHIFT = _empModel.WORKING_SHIFT;
emp.SALES_PERCENT = _empModel.SALES_PERCENT;
emp.COMMISSION_QUICK_POSITION = _empModel.COMMISSION_QUICK_POSITION;
emp.IS_DELETE = false;
db.TBL_EMPLOYEE.Add(emp);
db.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.OK, "Success"));
}
else
{
return(Request.CreateResponse(HttpStatusCode.ExpectationFailed));
}
}
catch (Exception ex)
{
throw;
}
finally
{
ConnectionTools.ChangeToRemoteDB(db);
}
}
