public IHttpActionResult PostKH_LIEN_HE(LienHeKH lh)
{
//if (!ModelState.IsValid)
//{
// return BadRequest(ModelState);
//}
//if(lh.NGUOI_LIEN_HE != "")
//{
KH_LIEN_HE lienhe = new KH_LIEN_HE();
lienhe.MA_KHACH_HANG = lh.MA_KHACH_HANG;
lienhe.NGUOI_LIEN_HE = lh.NGUOI_LIEN_HE;
lienhe.CHUC_VU = lh.CHUC_VU;
lienhe.PHONG_BAN = lh.PHONG_BAN;
if (lh.NGAY_SINH != null)
{
lienhe.NGAY_SINH = xlnt.Xulydatetime(lh.NGAY_SINH);
}
lienhe.GIOI_TINH = lh.GIOI_TINH;
lienhe.EMAIL_CA_NHAN = lh.EMAIL_CA_NHAN;
lienhe.EMAIL_CONG_TY = lh.EMAIL_CONG_TY;
lienhe.SKYPE = lh.SKYPE;
lienhe.FACEBOOK = lh.FACEBOOK;
lienhe.GHI_CHU = lh.GHI_CHU;
lienhe.SDT1 = lh.SDT1;
lienhe.SDT2 = lh.SDT2;
lienhe.TINH_TRANG_LAM_VIEC = lh.TINH_TRANG_LAM_VIEC;
lienhe.NGAY_TAO = DateTime.Today.Date;
db.KH_LIEN_HE.Add(lienhe);
db.SaveChanges();
var query = db.KH_LIEN_HE.Where(x => x.SDT1 == lh.SDT1).ToList();
var data = query.LastOrDefault();
KH_SALES_PHU_TRACH salept = new KH_SALES_PHU_TRACH();
salept.ID_LIEN_HE = data.ID_LIEN_HE;
salept.SALES_PHU_TRACH = lh.SALES_PHU_TRACH;
salept.NGAY_BAT_DAU_PHU_TRACH = DateTime.Today.Date;
salept.TRANG_THAI = true;
if (lh.SALES_CU == false && lh.SALES_MOI == false)
{
salept.SALES_MOI = true;
salept.SALES_CU = false;
}
else
{
salept.SALES_CU = lh.SALES_CU;
salept.SALES_MOI = lh.SALES_MOI;
}
db.KH_SALES_PHU_TRACH.Add(salept);
db.SaveChanges();
//}
return(Ok(lienhe));
}
// POST: api/Api_ArrayLienHeKH
//[HttpPost]
//[Route("api/Api_ArrayLienHeKH/{makh}")]
//public async Task<IHttpActionResult> PostMultiArrayLienHeKH(string makh, [FromBody] List<KH_LIEN_HE> qUY_CHI_TIET_PHIEU_CHI)
//{
// for (int i = 0; i < qUY_CHI_TIET_PHIEU_CHI.Count(); i++)
// {
// //nH_NTTKs[i].ID = (index + i + 1).ToString();
// db.KH_LIEN_HE.Add(qUY_CHI_TIET_PHIEU_CHI[i]);
// }
// try
// {
// await db.SaveChangesAsync();
// }
// catch (Exception ex)
// {
// return Ok(ex.Message);
// }
// return Ok(qUY_CHI_TIET_PHIEU_CHI);
//}
public void PostKH_LIEN_HE(List <LienHeKH> lh)
{
if (!ModelState.IsValid)
{
BadRequest(ModelState);
}
foreach (var item in lh)
{
KH_LIEN_HE lienhe = new KH_LIEN_HE();
lienhe.MA_KHACH_HANG = item.MA_KHACH_HANG;
lienhe.NGUOI_LIEN_HE = item.NGUOI_LIEN_HE;
lienhe.CHUC_VU = item.CHUC_VU;
lienhe.PHONG_BAN = item.PHONG_BAN;
//if (item.NGAY_SINH != null)
// lienhe.NGAY_SINH = xlnt.Xulydatetime(item.NGAY_SINH);
lienhe.GIOI_TINH = item.GIOI_TINH;
lienhe.EMAIL_CA_NHAN = item.EMAIL_CA_NHAN;
lienhe.EMAIL_CONG_TY = item.EMAIL_CONG_TY;
lienhe.SKYPE = item.SKYPE;
lienhe.FACEBOOK = item.FACEBOOK;
lienhe.GHI_CHU = item.GHI_CHU;
lienhe.SDT1 = item.SDT1;
lienhe.SDT2 = item.SDT2;
lienhe.TINH_TRANG_LAM_VIEC = item.TINH_TRANG_LAM_VIEC;
lienhe.NGAY_TAO = DateTime.Today.Date;
db.KH_LIEN_HE.Add(lienhe);
db.SaveChanges();
var query = db.KH_LIEN_HE.Where(x => x.SDT1 == item.SDT1).ToList();
var data = query.LastOrDefault();
KH_SALES_PHU_TRACH salept = new KH_SALES_PHU_TRACH();
salept.ID_LIEN_HE = data.ID_LIEN_HE;
salept.SALES_PHU_TRACH = item.SALES_PHU_TRACH;
salept.NGAY_BAT_DAU_PHU_TRACH = DateTime.Today.Date;
salept.TRANG_THAI = true;
if (item.SALES_CU == false && item.SALES_MOI == false)
{
salept.SALES_MOI = true;
salept.SALES_CU = false;
}
else
{
salept.SALES_CU = item.SALES_CU;
salept.SALES_MOI = item.SALES_MOI;
}
db.KH_SALES_PHU_TRACH.Add(salept);
db.SaveChanges();
//KH_CHUYEN_SALES chuyensale = new KH_CHUYEN_SALES();
//chuyensale.MA_KHACH_HANG = item.MA_KHACH_HANG;
//chuyensale.SALE_HIEN_THOI = item.SALES_PHU_TRACH;
//db.KH_CHUYEN_SALES.Add(chuyensale);
//db.SaveChanges();
}
}
public IHttpActionResult GetKH_SALES_PHU_TRACH(int id)
{
KH_SALES_PHU_TRACH kH_SALES_PHU_TRACH = db.KH_SALES_PHU_TRACH.Find(id);
if (kH_SALES_PHU_TRACH == null)
{
return(NotFound());
}
return(Ok(kH_SALES_PHU_TRACH));
}
public IHttpActionResult DeleteKH_SALES_PHU_TRACH(int id)
{
KH_SALES_PHU_TRACH kH_SALES_PHU_TRACH = db.KH_SALES_PHU_TRACH.Find(id);
if (kH_SALES_PHU_TRACH == null)
{
return(NotFound());
}
db.KH_SALES_PHU_TRACH.Remove(kH_SALES_PHU_TRACH);
db.SaveChanges();
return(Ok(kH_SALES_PHU_TRACH));
}
public IHttpActionResult PostKH_SALES_PHU_TRACH(SalesPhuTrach sale)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
KH_SALES_PHU_TRACH nv = new KH_SALES_PHU_TRACH();
nv.ID_LIEN_HE = sale.ID_LIEN_HE;
nv.SALES_PHU_TRACH = sale.SALES_PHU_TRACH;
nv.NGAY_BAT_DAU_PHU_TRACH = DateTime.Today.Date;
nv.TRANG_THAI = sale.TRANG_THAI;
if (sale.SALES_CU == false && sale.SALES_MOI == false)
{
nv.SALES_MOI = true;
nv.SALES_CU = false;
}
else
{
nv.SALES_CU = sale.SALES_CU;
nv.SALES_MOI = sale.SALES_MOI;
}
db.KH_SALES_PHU_TRACH.Add(nv);
try
{
db.SaveChanges();
}
catch (DbUpdateException)
{
if (KH_SALES_PHU_TRACHExists(sale.ID_LIEN_HE))
{
return(Conflict());
}
else
{
throw;
}
}
return(CreatedAtRoute("DefaultApi", new { id = nv.ID_LIEN_HE }, nv));
}
public void PostKH_LIEN_HE(List <LienHeKH> lh)
{
if (!ModelState.IsValid)
{
BadRequest(ModelState);
}
foreach (var item in lh)
{
KH_LIEN_HE lienhe = new KH_LIEN_HE();
lienhe.MA_KHACH_HANG = item.MA_KHACH_HANG;
lienhe.NGUOI_LIEN_HE = item.NGUOI_LIEN_HE;
lienhe.CHUC_VU = item.CHUC_VU;
lienhe.PHONG_BAN = item.PHONG_BAN;
if (item.NGAY_SINH != null)
{
lienhe.NGAY_SINH = xlnt.Xulydatetime(item.NGAY_SINH);
}
lienhe.GIOI_TINH = item.GIOI_TINH;
lienhe.EMAIL_CA_NHAN = item.EMAIL_CA_NHAN;
lienhe.EMAIL_CONG_TY = item.EMAIL_CONG_TY;
lienhe.SKYPE = item.SKYPE;
lienhe.FACEBOOK = item.FACEBOOK;
lienhe.GHI_CHU = item.GHI_CHU;
lienhe.SDT1 = item.SDT1;
lienhe.SDT2 = item.SDT2;
db.KH_LIEN_HE.Add(lienhe);
db.SaveChanges();
var query = db.KH_LIEN_HE.Where(x => x.SDT1 == item.SDT1).ToList();
var data = query.LastOrDefault();
KH_SALES_PHU_TRACH salept = new KH_SALES_PHU_TRACH();
salept.ID_LIEN_HE = data.ID_LIEN_HE;
salept.SALES_PHU_TRACH = item.SALES_PHU_TRACH;
salept.NGAY_BAT_DAU_PHU_TRACH = DateTime.Today.Date;
salept.TRANG_THAI = true;
db.KH_SALES_PHU_TRACH.Add(salept);
db.SaveChanges();
}
}
public ActionResult Import_KhachHang(HttpPostedFileBase file)
{
try
{
if (Request != null)
{
HttpPostedFileBase filetonkho = Request.Files["UploadedFile"];
if ((filetonkho != null) && (filetonkho.ContentLength > 0) && !string.IsNullOrEmpty(filetonkho.FileName))
{
string fileName = filetonkho.FileName;
string fileContentType = filetonkho.ContentType;
byte[] fileBytes = new byte[filetonkho.ContentLength];
var data = filetonkho.InputStream.Read(fileBytes, 0, Convert.ToInt32(filetonkho.ContentLength));
//var usersList = new List<Users>();
using (var package = new ExcelPackage(filetonkho.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
string makhach = workSheet.Cells[rowIterator, 1].Value.ToString();
var query = db.KHs.Where(x => x.MA_KHACH_HANG == makhach).FirstOrDefault();
if (query == null)
{
KH khachhang = new KH();
khachhang.MA_KHACH_HANG = makhach;
khachhang.TEN_CONG_TY = workSheet.Cells[rowIterator, 2].Value.ToString();
if (workSheet.Cells[rowIterator, 5].Value != null)
{
khachhang.VAN_PHONG_GIAO_DICH = workSheet.Cells[rowIterator, 5].Value.ToString();
}
if (workSheet.Cells[rowIterator, 6].Value != null)
{
khachhang.DIA_CHI_XUAT_HOA_DON = workSheet.Cells[rowIterator, 6].Value.ToString();
}
if (workSheet.Cells[rowIterator, 7].Value != null)
{
khachhang.MST = workSheet.Cells[rowIterator, 7].Value.ToString();
}
if (workSheet.Cells[rowIterator, 8].Value != null)
{
khachhang.HOTLINE = workSheet.Cells[rowIterator, 8].Value.ToString();
}
if (workSheet.Cells[rowIterator, 9].Value != null)
{
khachhang.FAX = workSheet.Cells[rowIterator, 9].Value.ToString();
}
if (workSheet.Cells[rowIterator, 10].Value != null)
{
khachhang.EMAIL = workSheet.Cells[rowIterator, 10].Value.ToString();
}
if (workSheet.Cells[rowIterator, 11].Value != null)
{
khachhang.LOGO = workSheet.Cells[rowIterator, 11].Value.ToString();
}
if (workSheet.Cells[rowIterator, 12].Value != null)
{
khachhang.WEBSITE = workSheet.Cells[rowIterator, 12].Value.ToString();
}
if (workSheet.Cells[rowIterator, 13].Value != null)
{
khachhang.TINH = workSheet.Cells[rowIterator, 13].Value.ToString();
}
if (workSheet.Cells[rowIterator, 14].Value != null)
{
khachhang.QUOC_GIA = workSheet.Cells[rowIterator, 14].Value.ToString();
}
if (workSheet.Cells[rowIterator, 15].Value != null)
{
khachhang.DIEU_KHOAN_THANH_TOAN = workSheet.Cells[rowIterator, 15].Value.ToString();
}
if (workSheet.Cells[rowIterator, 16].Value != null)
{
khachhang.SO_NGAY_DUOC_NO = Convert.ToInt32(workSheet.Cells[rowIterator, 16].Value);
}
if (workSheet.Cells[rowIterator, 17].Value != null)
{
khachhang.SO_NO_TOI_DA = Convert.ToInt32(workSheet.Cells[rowIterator, 17].Value);
}
khachhang.TRUC_THUOC = "HOPLONG";
if (workSheet.Cells[rowIterator, 19].Value != null)
{
khachhang.GHI_CHU = workSheet.Cells[rowIterator, 19].Value.ToString();
}
db.KHs.Add(khachhang);
db.SaveChanges();
var DATA = db.KHs.Where(x => x.MA_KHACH_HANG == makhach).FirstOrDefault();
if (DATA != null)
{
KH_PHAN_LOAI_KHACH plkhach = new KH_PHAN_LOAI_KHACH();
plkhach.MA_KHACH_HANG = makhach;
plkhach.MA_LOAI_KHACH = workSheet.Cells[rowIterator, 3].Value.ToString();
if (workSheet.Cells[rowIterator, 4].Value != null)
{
plkhach.NHOM_NGANH = workSheet.Cells[rowIterator, 4].Value.ToString();
}
db.KH_PHAN_LOAI_KHACH.Add(plkhach);
db.SaveChanges();
}
if (workSheet.Cells[rowIterator, 20].Value != null)
{
KH_LIEN_HE lhkhach = new KH_LIEN_HE();
lhkhach.MA_KHACH_HANG = makhach;
lhkhach.NGUOI_LIEN_HE = workSheet.Cells[rowIterator, 20].Value.ToString();
if (workSheet.Cells[rowIterator, 21].Value != null)
{
lhkhach.CHUC_VU = workSheet.Cells[rowIterator, 21].Value.ToString();
}
if (workSheet.Cells[rowIterator, 22].Value != null)
{
lhkhach.PHONG_BAN = workSheet.Cells[rowIterator, 22].Value.ToString();
}
if (workSheet.Cells[rowIterator, 23].Value != null)
{
lhkhach.NGAY_SINH = xulydate.Xulydatetime(workSheet.Cells[rowIterator, 23].Value.ToString());
}
if (workSheet.Cells[rowIterator, 24].Value != null)
{
lhkhach.GIOI_TINH = workSheet.Cells[rowIterator, 24].Value.ToString();
}
lhkhach.SDT1 = workSheet.Cells[rowIterator, 25].Value.ToString();
if (workSheet.Cells[rowIterator, 26].Value != null)
{
lhkhach.SDT2 = workSheet.Cells[rowIterator, 26].Value.ToString();
}
if (workSheet.Cells[rowIterator, 27].Value != null)
{
lhkhach.EMAIL_CA_NHAN = workSheet.Cells[rowIterator, 27].Value.ToString();
}
if (workSheet.Cells[rowIterator, 28].Value != null)
{
lhkhach.EMAIL_CONG_TY = workSheet.Cells[rowIterator, 28].Value.ToString();
}
if (workSheet.Cells[rowIterator, 29].Value != null)
{
lhkhach.SKYPE = workSheet.Cells[rowIterator, 29].Value.ToString();
}
if (workSheet.Cells[rowIterator, 30].Value != null)
{
lhkhach.FACEBOOK = workSheet.Cells[rowIterator, 30].Value.ToString();
}
if (workSheet.Cells[rowIterator, 31].Value != null)
{
lhkhach.GHI_CHU = workSheet.Cells[rowIterator, 31].Value.ToString();
}
db.KH_LIEN_HE.Add(lhkhach);
db.SaveChanges();
string idlh = workSheet.Cells[rowIterator, 25].Value.ToString();
var datalienhe = db.KH_LIEN_HE.Where(x => x.SDT1 == idlh).FirstOrDefault();
if (datalienhe != null)
{
KH_SALES_PHU_TRACH salept = new KH_SALES_PHU_TRACH();
salept.ID_LIEN_HE = datalienhe.ID_LIEN_HE;
salept.SALES_PHU_TRACH = workSheet.Cells[rowIterator, 32].Value.ToString();
salept.NGAY_BAT_DAU_PHU_TRACH = DateTime.Today.Date;
salept.TRANG_THAI = true;
db.KH_SALES_PHU_TRACH.Add(salept);
db.SaveChanges();
}
if (workSheet.Cells[rowIterator, 33].Value != null)
{
KH_TK_NGAN_HANG tkkhach = new KH_TK_NGAN_HANG();
tkkhach.MA_KHACH_HANG = makhach;
tkkhach.SO_TAI_KHOAN = workSheet.Cells[rowIterator, 33].Value.ToString();
if (workSheet.Cells[rowIterator, 34].Value != null)
{
tkkhach.TEN_TAI_KHOAN = workSheet.Cells[rowIterator, 34].Value.ToString();
}
if (workSheet.Cells[rowIterator, 35].Value != null)
{
tkkhach.TEN_NGAN_HANG = workSheet.Cells[rowIterator, 35].Value.ToString();
}
if (workSheet.Cells[rowIterator, 36].Value != null)
{
tkkhach.CHI_NHANH = workSheet.Cells[rowIterator, 36].Value.ToString();
}
if (workSheet.Cells[rowIterator, 37].Value != null)
{
tkkhach.TINH_TP = workSheet.Cells[rowIterator, 37].Value.ToString();
}
if (workSheet.Cells[rowIterator, 38].Value != null)
{
tkkhach.LOAI_TAI_KHOAN = workSheet.Cells[rowIterator, 38].Value.ToString();
}
if (workSheet.Cells[rowIterator, 39].Value != null)
{
tkkhach.GHI_CHU = workSheet.Cells[rowIterator, 39].Value.ToString();
}
db.KH_TK_NGAN_HANG.Add(tkkhach);
db.SaveChanges();
}
}
}
else
if (query != null)
{
if (workSheet.Cells[rowIterator, 20].Value != null)
{
KH_LIEN_HE lhkhach = new KH_LIEN_HE();
lhkhach.MA_KHACH_HANG = makhach;
lhkhach.NGUOI_LIEN_HE = workSheet.Cells[rowIterator, 20].Value.ToString();
if (workSheet.Cells[rowIterator, 21].Value != null)
{
lhkhach.CHUC_VU = workSheet.Cells[rowIterator, 21].Value.ToString();
}
if (workSheet.Cells[rowIterator, 22].Value != null)
{
lhkhach.PHONG_BAN = workSheet.Cells[rowIterator, 22].Value.ToString();
}
if (workSheet.Cells[rowIterator, 23].Value != null)
{
lhkhach.NGAY_SINH = xulydate.Xulydatetime(workSheet.Cells[rowIterator, 23].Value.ToString());
}
if (workSheet.Cells[rowIterator, 24].Value != null)
{
lhkhach.GIOI_TINH = workSheet.Cells[rowIterator, 24].Value.ToString();
}
lhkhach.SDT1 = workSheet.Cells[rowIterator, 25].Value.ToString();
if (workSheet.Cells[rowIterator, 26].Value != null)
{
lhkhach.SDT2 = workSheet.Cells[rowIterator, 26].Value.ToString();
}
if (workSheet.Cells[rowIterator, 27].Value != null)
{
lhkhach.EMAIL_CA_NHAN = workSheet.Cells[rowIterator, 27].Value.ToString();
}
if (workSheet.Cells[rowIterator, 28].Value != null)
{
lhkhach.EMAIL_CONG_TY = workSheet.Cells[rowIterator, 28].Value.ToString();
}
if (workSheet.Cells[rowIterator, 29].Value != null)
{
lhkhach.SKYPE = workSheet.Cells[rowIterator, 29].Value.ToString();
}
if (workSheet.Cells[rowIterator, 30].Value != null)
{
lhkhach.FACEBOOK = workSheet.Cells[rowIterator, 30].Value.ToString();
}
if (workSheet.Cells[rowIterator, 31].Value != null)
{
lhkhach.GHI_CHU = workSheet.Cells[rowIterator, 31].Value.ToString();
}
db.KH_LIEN_HE.Add(lhkhach);
db.SaveChanges();
string idlh = workSheet.Cells[rowIterator, 25].Value.ToString();
var datalienhe = db.KH_LIEN_HE.Where(x => x.SDT1 == idlh).FirstOrDefault();
if (datalienhe != null)
{
KH_SALES_PHU_TRACH salept = new KH_SALES_PHU_TRACH();
salept.ID_LIEN_HE = datalienhe.ID_LIEN_HE;
salept.SALES_PHU_TRACH = workSheet.Cells[rowIterator, 32].Value.ToString();
salept.NGAY_BAT_DAU_PHU_TRACH = DateTime.Today.Date;
salept.TRANG_THAI = true;
db.KH_SALES_PHU_TRACH.Add(salept);
db.SaveChanges();
}
if (workSheet.Cells[rowIterator, 33].Value != null)
{
KH_TK_NGAN_HANG tkkhach = new KH_TK_NGAN_HANG();
tkkhach.MA_KHACH_HANG = makhach;
tkkhach.SO_TAI_KHOAN = workSheet.Cells[rowIterator, 33].Value.ToString();
if (workSheet.Cells[rowIterator, 34].Value != null)
{
tkkhach.TEN_TAI_KHOAN = workSheet.Cells[rowIterator, 34].Value.ToString();
}
if (workSheet.Cells[rowIterator, 35].Value != null)
{
tkkhach.TEN_NGAN_HANG = workSheet.Cells[rowIterator, 35].Value.ToString();
}
if (workSheet.Cells[rowIterator, 36].Value != null)
{
tkkhach.CHI_NHANH = workSheet.Cells[rowIterator, 36].Value.ToString();
}
if (workSheet.Cells[rowIterator, 37].Value != null)
{
tkkhach.TINH_TP = workSheet.Cells[rowIterator, 37].Value.ToString();
}
if (workSheet.Cells[rowIterator, 38].Value != null)
{
tkkhach.LOAI_TAI_KHOAN = workSheet.Cells[rowIterator, 38].Value.ToString();
}
if (workSheet.Cells[rowIterator, 39].Value != null)
{
tkkhach.GHI_CHU = workSheet.Cells[rowIterator, 39].Value.ToString();
}
db.KH_TK_NGAN_HANG.Add(tkkhach);
db.SaveChanges();
}
}
}
so_dong_thanh_cong++;
dong = rowIterator;
}
}
}
}
}
catch (Exception Ex)
{
ViewBag.Error = " Đã xảy ra lỗi, Liên hệ ngay với admin. " + Environment.NewLine + " Thông tin chi tiết về lỗi:" + Environment.NewLine + Ex;
ViewBag.Information = "Lỗi tại dòng thứ: " + dong;
}
finally
{
ViewBag.Message = "Đã import thành công " + so_dong_thanh_cong + " dòng";
}
return(View());
}
public ActionResult Import_KhachHang(HttpPostedFileBase file)
{
try
{
if (Request != null)
{
HttpPostedFileBase filetonkho = Request.Files["UploadedFile"];
if ((filetonkho != null) && (filetonkho.ContentLength > 0) && !string.IsNullOrEmpty(filetonkho.FileName))
{
string fileName = filetonkho.FileName;
string fileContentType = filetonkho.ContentType;
byte[] fileBytes = new byte[filetonkho.ContentLength];
var data = filetonkho.InputStream.Read(fileBytes, 0, Convert.ToInt32(filetonkho.ContentLength));
//var usersList = new List<Users>();
using (var package = new ExcelPackage(filetonkho.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
makhach = workSheet.Cells[rowIterator, 1].Value.ToString();
tencongty = workSheet.Cells[rowIterator, 2].Value.ToString();
if (workSheet.Cells[rowIterator, 3].Value != null)
{
phanloaikhach = workSheet.Cells[rowIterator, 3].Value.ToString();
}
else
{
phanloaikhach = "";
}
if (workSheet.Cells[rowIterator, 4].Value != null)
{
nhomnganh = workSheet.Cells[rowIterator, 4].Value.ToString();
}
else
{
nhomnganh = "";
}
if (workSheet.Cells[rowIterator, 4].Value != null)
{
nhomnganh = workSheet.Cells[rowIterator, 4].Value.ToString();
}
else
{
nhomnganh = "";
}
if (workSheet.Cells[rowIterator, 5].Value != null)
{
diachivpgiaodich = workSheet.Cells[rowIterator, 5].Value.ToString();
}
else
{
diachivpgiaodich = "";
}
if (workSheet.Cells[rowIterator, 6].Value != null)
{
diachixuathoadon = workSheet.Cells[rowIterator, 6].Value.ToString();
}
else
{
diachixuathoadon = "";
}
if (workSheet.Cells[rowIterator, 7].Value != null)
{
MST = workSheet.Cells[rowIterator, 7].Value.ToString();
}
else
{
MST = "";
}
if (workSheet.Cells[rowIterator, 8].Value != null)
{
somayban = workSheet.Cells[rowIterator, 8].Value.ToString();
}
else
{
somayban = "";
}
if (workSheet.Cells[rowIterator, 9].Value != null)
{
fax = workSheet.Cells[rowIterator, 9].Value.ToString();
}
else
{
fax = "";
}
if (workSheet.Cells[rowIterator, 10].Value != null)
{
email = workSheet.Cells[rowIterator, 10].Value.ToString();
}
else
{
email = "";
}
if (workSheet.Cells[rowIterator, 11].Value != null)
{
logo = workSheet.Cells[rowIterator, 11].Value.ToString();
}
else
{
logo = "";
}
if (workSheet.Cells[rowIterator, 12].Value != null)
{
website = workSheet.Cells[rowIterator, 12].Value.ToString();
}
else
{
website = "";
}
if (workSheet.Cells[rowIterator, 13].Value != null)
{
tinh = workSheet.Cells[rowIterator, 13].Value.ToString();
}
else
{
tinh = "";
}
if (workSheet.Cells[rowIterator, 14].Value != null)
{
quocgia = workSheet.Cells[rowIterator, 14].Value.ToString();
}
else
{
quocgia = "";
}
if (workSheet.Cells[rowIterator, 15].Value != null)
{
dieukhoanthanhtoan = workSheet.Cells[rowIterator, 15].Value.ToString();
}
else
{
dieukhoanthanhtoan = "";
}
if (workSheet.Cells[rowIterator, 16].Value != null)
{
songayduocno = workSheet.Cells[rowIterator, 16].Value.ToString();
}
else
{
songayduocno = "";
}
if (workSheet.Cells[rowIterator, 17].Value != null)
{
sonotoida = workSheet.Cells[rowIterator, 17].Value.ToString();
}
else
{
sonotoida = "";
}
if (workSheet.Cells[rowIterator, 18].Value != null)
{
tinhtranghoatdong = workSheet.Cells[rowIterator, 18].Value.ToString();
}
else
{
tinhtranghoatdong = "";
}
if (workSheet.Cells[rowIterator, 19].Value != null)
{
tructhuoc = workSheet.Cells[rowIterator, 19].Value.ToString();
}
else
{
tructhuoc = "";
}
if (workSheet.Cells[rowIterator, 20].Value != null)
{
ghichu = workSheet.Cells[rowIterator, 20].Value.ToString();
}
else
{
ghichu = "";
}
if (workSheet.Cells[rowIterator, 21].Value != null)
{
phutrachhienthoi = workSheet.Cells[rowIterator, 21].Value.ToString();
}
else
{
phutrachhienthoi = "";
}
if (workSheet.Cells[rowIterator, 22].Value != null)
{
nguoilienhe = workSheet.Cells[rowIterator, 22].Value.ToString();
}
else
{
nguoilienhe = "";
}
if (workSheet.Cells[rowIterator, 23].Value != null)
{
chucvu = workSheet.Cells[rowIterator, 23].Value.ToString();
}
else
{
chucvu = "";
}
if (workSheet.Cells[rowIterator, 24].Value != null)
{
phongban = workSheet.Cells[rowIterator, 24].Value.ToString();
}
else
{
phongban = "";
}
if (workSheet.Cells[rowIterator, 25].Value != null)
{
ngaysinh = workSheet.Cells[rowIterator, 25].Value.ToString();
}
else
{
ngaysinh = "";
}
if (workSheet.Cells[rowIterator, 26].Value != null)
{
gioitinh = workSheet.Cells[rowIterator, 26].Value.ToString();
}
else
{
gioitinh = "";
}
if (workSheet.Cells[rowIterator, 27].Value != null)
{
sdt1 = workSheet.Cells[rowIterator, 27].Value.ToString();
}
else
{
sdt1 = "";
}
if (workSheet.Cells[rowIterator, 28].Value != null)
{
sdt2 = workSheet.Cells[rowIterator, 28].Value.ToString();
}
else
{
sdt2 = "";
}
if (workSheet.Cells[rowIterator, 29].Value != null)
{
emailcanhan = workSheet.Cells[rowIterator, 29].Value.ToString();
}
else
{
emailcanhan = "";
}
if (workSheet.Cells[rowIterator, 30].Value != null)
{
emailcongty = workSheet.Cells[rowIterator, 30].Value.ToString();
}
else
{
emailcongty = "";
}
if (workSheet.Cells[rowIterator, 31].Value != null)
{
skype = workSheet.Cells[rowIterator, 31].Value.ToString();
}
else
{
skype = "";
}
if (workSheet.Cells[rowIterator, 32].Value != null)
{
facebook = workSheet.Cells[rowIterator, 32].Value.ToString();
}
else
{
facebook = "";
}
if (workSheet.Cells[rowIterator, 33].Value != null)
{
ghichulienhe = workSheet.Cells[rowIterator, 33].Value.ToString();
}
else
{
ghichulienhe = "";
}
if (workSheet.Cells[rowIterator, 34].Value != null)
{
salephutrach = workSheet.Cells[rowIterator, 34].Value.ToString();
}
else
{
salephutrach = "";
}
if (workSheet.Cells[rowIterator, 35].Value != null)
{
sotknganhang = workSheet.Cells[rowIterator, 36].Value.ToString();
}
else
{
sotknganhang = "";
}
if (workSheet.Cells[rowIterator, 36].Value != null)
{
tentaikhoan = workSheet.Cells[rowIterator, 36].Value.ToString();
}
else
{
tentaikhoan = "";
}
if (workSheet.Cells[rowIterator, 37].Value != null)
{
tennganhang = workSheet.Cells[rowIterator, 37].Value.ToString();
}
else
{
tennganhang = "";
}
if (workSheet.Cells[rowIterator, 38].Value != null)
{
chinhanhnganhang = workSheet.Cells[rowIterator, 38].Value.ToString();
}
else
{
chinhanhnganhang = "";
}
if (workSheet.Cells[rowIterator, 39].Value != null)
{
tinhnganhang = workSheet.Cells[rowIterator, 39].Value.ToString();
}
else
{
tinhnganhang = "";
}
if (workSheet.Cells[rowIterator, 40].Value != null)
{
loaitaikhoan = workSheet.Cells[rowIterator, 40].Value.ToString();
}
else
{
loaitaikhoan = "";
}
if (workSheet.Cells[rowIterator, 41].Value != null)
{
ghichu = workSheet.Cells[rowIterator, 41].Value.ToString();
}
else
{
ghichu = "";
}
//Thêm khách hàng
var query = db.KHs.Where(x => x.MA_KHACH_HANG == makhach).FirstOrDefault();
if (query == null)
{
KH khachhang = new KH();
khachhang.MA_KHACH_HANG = makhach;
khachhang.TEN_CONG_TY = tencongty;
if (diachivpgiaodich != "")
{
khachhang.VAN_PHONG_GIAO_DICH = diachivpgiaodich;
}
if (diachixuathoadon != "")
{
khachhang.DIA_CHI_XUAT_HOA_DON = diachixuathoadon;
}
if (MST != "")
{
khachhang.MST = MST;
}
if (somayban != "")
{
khachhang.HOTLINE = somayban;
}
if (fax != "")
{
khachhang.FAX = fax;
}
if (email != "")
{
khachhang.EMAIL = email;
}
if (logo != "")
{
khachhang.LOGO = logo;
}
if (website != "")
{
khachhang.WEBSITE = website;
}
if (tinh != "")
{
khachhang.TINH = tinh;
}
if (quocgia != "")
{
khachhang.QUOC_GIA = quocgia;
}
if (dieukhoanthanhtoan != "")
{
khachhang.DIEU_KHOAN_THANH_TOAN = dieukhoanthanhtoan;
}
if (songayduocno != "")
{
khachhang.SO_NGAY_DUOC_NO = Convert.ToInt32(songayduocno);
}
if (sonotoida != "")
{
khachhang.SO_NO_TOI_DA = Convert.ToInt32(sonotoida);
}
if (tinhtranghoatdong != "")
{
khachhang.TINH_TRANG_HOAT_DONG = tinhtranghoatdong;
}
if (tructhuoc != "")
{
khachhang.TRUC_THUOC = tructhuoc;
}
if (ghichu != "")
{
khachhang.GHI_CHU = ghichu;
}
db.KHs.Add(khachhang);
db.SaveChanges();
//thêm phụ trách hiện thời
if (phutrachhienthoi != "")
{
KH_CHUYEN_SALES chuyensale = new KH_CHUYEN_SALES();
chuyensale.MA_KHACH_HANG = makhach;
chuyensale.SALE_HIEN_THOI = phutrachhienthoi;
db.KH_CHUYEN_SALES.Add(chuyensale);
}
//Thêm phân loại khách
var DATA = db.KH_PHAN_LOAI_KHACH.Where(x => x.MA_KHACH_HANG == makhach).FirstOrDefault();
if (DATA != null && phanloaikhach != "")
{
KH_PHAN_LOAI_KHACH plkhach = new KH_PHAN_LOAI_KHACH();
plkhach.MA_KHACH_HANG = makhach;
plkhach.MA_LOAI_KHACH = phanloaikhach;
if (nhomnganh != "")
{
plkhach.NHOM_NGANH = nhomnganh;
}
db.KH_PHAN_LOAI_KHACH.Add(plkhach);
db.SaveChanges();
}
//thêm người liên hệ
if (nguoilienhe != "")
{
KH_LIEN_HE lhkhach = new KH_LIEN_HE();
lhkhach.MA_KHACH_HANG = makhach;
lhkhach.NGUOI_LIEN_HE = nguoilienhe;
if (chucvu != "")
{
lhkhach.CHUC_VU = chucvu;
}
if (phongban != "")
{
lhkhach.PHONG_BAN = phongban;
}
if (ngaysinh != "")
{
lhkhach.NGAY_SINH = xulydate.Xulydatetime(ngaysinh);
}
if (gioitinh != "")
{
lhkhach.GIOI_TINH = gioitinh;
}
lhkhach.SDT1 = sdt1;
if (sdt2 != "")
{
lhkhach.SDT2 = sdt2;
}
if (emailcanhan != "")
{
lhkhach.EMAIL_CA_NHAN = emailcanhan;
}
if (emailcongty != "")
{
lhkhach.EMAIL_CONG_TY = emailcongty;
}
if (skype != "")
{
lhkhach.SKYPE = skype;
}
if (facebook != "")
{
lhkhach.FACEBOOK = facebook;
}
if (ghichulienhe != "")
{
lhkhach.GHI_CHU = ghichu;
}
db.KH_LIEN_HE.Add(lhkhach);
db.SaveChanges();
//thêm sale phụ trách
var datalienhe = db.KH_LIEN_HE.Where(x => x.SDT1 == sdt1).FirstOrDefault();
if (datalienhe != null)
{
KH_SALES_PHU_TRACH salept = new KH_SALES_PHU_TRACH();
salept.ID_LIEN_HE = datalienhe.ID_LIEN_HE;
salept.SALES_PHU_TRACH = salephutrach;
salept.NGAY_BAT_DAU_PHU_TRACH = DateTime.Today.Date;
salept.TRANG_THAI = true;
db.KH_SALES_PHU_TRACH.Add(salept);
db.SaveChanges();
}
//thêm tài khoản ngân hàng
if (sotknganhang != "")
{
KH_TK_NGAN_HANG tkkhach = new KH_TK_NGAN_HANG();
tkkhach.MA_KHACH_HANG = makhach;
tkkhach.SO_TAI_KHOAN = sotknganhang;
if (tentaikhoan != "")
{
tkkhach.TEN_TAI_KHOAN = tentaikhoan;
}
if (tennganhang != "")
{
tkkhach.TEN_NGAN_HANG = tennganhang;
}
if (chinhanhnganhang != "")
{
tkkhach.CHI_NHANH = chinhanhnganhang;
}
if (tinhnganhang != "")
{
tkkhach.TINH_TP = tinhnganhang;
}
if (loaitaikhoan != "")
{
tkkhach.LOAI_TAI_KHOAN = loaitaikhoan;
}
if (ghichutaikhoan != "")
{
tkkhach.GHI_CHU = ghichutaikhoan;
}
db.KH_TK_NGAN_HANG.Add(tkkhach);
db.SaveChanges();
}
}
}
//trường hợp đã có khách hàng, chỉ thêm liên hệ, ...
else
if (query != null)
{
//thêm liên hệ
if (nguoilienhe != "")
{
KH_LIEN_HE lhkhach = new KH_LIEN_HE();
lhkhach.MA_KHACH_HANG = makhach;
lhkhach.NGUOI_LIEN_HE = nguoilienhe;
if (chucvu != "")
{
lhkhach.CHUC_VU = chucvu;
}
if (phongban != "")
{
lhkhach.PHONG_BAN = phongban;
}
if (ngaysinh != "")
{
lhkhach.NGAY_SINH = xulydate.Xulydatetime(ngaysinh);
}
if (gioitinh != "")
{
lhkhach.GIOI_TINH = gioitinh;
}
lhkhach.SDT1 = sdt1;
if (sdt2 != "")
{
lhkhach.SDT2 = sdt2;
}
if (emailcanhan != "")
{
lhkhach.EMAIL_CA_NHAN = emailcanhan;
}
if (emailcongty != "")
{
lhkhach.EMAIL_CONG_TY = emailcongty;
}
if (skype != "")
{
lhkhach.SKYPE = skype;
}
if (facebook != "")
{
lhkhach.FACEBOOK = facebook;
}
if (ghichu != "")
{
lhkhach.GHI_CHU = ghichu;
}
db.KH_LIEN_HE.Add(lhkhach);
db.SaveChanges();
//thêm sale phụ trách
var datalienhe = db.KH_LIEN_HE.Where(x => x.SDT1 == sdt1).FirstOrDefault();
if (datalienhe != null)
{
KH_SALES_PHU_TRACH salept = new KH_SALES_PHU_TRACH();
salept.ID_LIEN_HE = datalienhe.ID_LIEN_HE;
salept.SALES_PHU_TRACH = salephutrach;
salept.NGAY_BAT_DAU_PHU_TRACH = DateTime.Today.Date;
salept.TRANG_THAI = true;
db.KH_SALES_PHU_TRACH.Add(salept);
db.SaveChanges();
}
//thêm tài khoản ngân hàng
if (sotknganhang != "")
{
KH_TK_NGAN_HANG tkkhach = new KH_TK_NGAN_HANG();
tkkhach.MA_KHACH_HANG = makhach;
tkkhach.SO_TAI_KHOAN = sotknganhang;
if (tentaikhoan != "")
{
tkkhach.TEN_TAI_KHOAN = tentaikhoan;
}
if (tennganhang != "")
{
tkkhach.TEN_NGAN_HANG = tennganhang;
}
if (chinhanhnganhang != "")
{
tkkhach.CHI_NHANH = chinhanhnganhang;
}
if (tinhnganhang != "")
{
tkkhach.TINH_TP = tinhnganhang;
}
if (loaitaikhoan != "")
{
tkkhach.LOAI_TAI_KHOAN = loaitaikhoan;
}
if (ghichutaikhoan != "")
{
tkkhach.GHI_CHU = ghichutaikhoan;
}
db.KH_TK_NGAN_HANG.Add(tkkhach);
db.SaveChanges();
}
}
}
so_dong_thanh_cong++;
dong = rowIterator;
}
}
}
}
}
catch (Exception Ex)
{
ViewBag.Error = " Đã xảy ra lỗi, Liên hệ ngay với admin. " + Environment.NewLine + " Thông tin chi tiết về lỗi:" + Environment.NewLine + Ex;
ViewBag.Information = "Lỗi tại các dòng: " + dong;
}
finally
{
ViewBag.Message = "Đã import thành công " + so_dong_thanh_cong + " dòng";
}
return(View());
}