public ActionResult CreateGoodReceivedNote(Merchant_StockInHeader GRN, List <Merchant_StockInDetail> details, List <Merchant_StockInDetail> lstImport, int error = 0, string linkerror = "", bool IsImport = false, bool trang_thai = false)
{
if (accessDetail != null && (accessDetail.access["all"] || accessDetail.access["update"]))
{
if (GRN.id == 0)
{
GRN.ma_gian_hang = currentUser.ma_gian_hang;
}
string rs = new Merchant_StockInHeader_DAO().Upsert(GRN, details, lstImport, trang_thai, currentUser.name, AppConfigs.MCCConnectionString);
string[] separators = { "@@" };
var str = rs.Split(separators, StringSplitOptions.RemoveEmptyEntries);
if (str[0] == "true")
{
Merchant_Stock_ViewModel data;
var item = new Merchant_StockInHeader();
using (var db = MCC.Helpers.OrmliteConnection.openConn())
{
data = db.FirstOrDefault <Merchant_Stock_ViewModel>(@"select
a.ma_phieu_nhap_kho as ma_phieu,
b.ten_kho,
b.ma_kho,
a.ma_don_hang,
a.ngay_nhap_kho as ngay_nhap_xuat,
a.thu_kho,
a.nguoi_giao as nguoi_giao_nhan,
a.nguoi_kiem_tra,
a.ngay_tao,
a.nguoi_tao,
a.dia_diem,
a.trang_thai,
a.ghi_chu,
'GRN' as loai_phieu from Merchant_StockInHeader a left join Merchant_WareHouse b
on a.ma_kho = b.ma_kho
where ma_phieu_nhap_kho = {0}", str[1]);
if (trang_thai)
{
List <Merchant_StockInDetail> stockDetail = db.Select <Merchant_StockInDetail>("ma_phieu_nhap_kho={0} and ma_gian_hang={1}", data.ma_phieu, currentUser.ma_gian_hang);
foreach (var row in stockDetail)
{
var exist = db.Scalar <bool>("SELECT TOP 1 1 FROM Merchant_DetailLocation WHERE ma_kho = {0} and ma_gian_hang={1} and ten_vi_tri={2} and ma_san_pham={3}", data.ma_kho, currentUser.ma_gian_hang, row.vi_tri, row.ma_san_pham);
if (exist)
{
db.Update <Merchant_DetailLocation>("so_luong=so_luong+{0}".Params(row.so_luong_thuc_te), "ma_kho = {0} and ma_gian_hang={1} and ten_vi_tri={2} and ma_san_pham={3}".Params(data.ma_kho, currentUser.ma_gian_hang, row.vi_tri, row.ma_san_pham));
}
else
{
Merchant_DetailLocation detailLocation = new Merchant_DetailLocation();
detailLocation.ma_kho = data.ma_kho;
detailLocation.ten_kho = data.ten_kho;
detailLocation.ma_gian_hang = currentUser.ma_gian_hang;
detailLocation.ten_vi_tri = row.vi_tri == null ? "" : row.vi_tri;
detailLocation.so_luong = row.so_luong_thuc_te;
detailLocation.ma_san_pham = row.ma_san_pham;
detailLocation.don_vi_tinh = row.don_vi_tinh;
detailLocation.ngay_tao = DateTime.Now;
detailLocation.nguoi_tao = currentUser.name;
detailLocation.ngay_cap_nhat = DateTime.Now;
detailLocation.nguoi_cap_nhat = currentUser.name;
db.Insert <Merchant_DetailLocation>(detailLocation);
}
}
//Cập nhật tồn kho
string result = new Merchant_StockInHeader_DAO().UpdateInventory_StockIN(data.ma_phieu);
//db.ExecuteNonQuery("exec p_Merchant_Product_Warehouse_SyncToMySQL {0}".Params(data.ma_phieu));
}
item = db.FirstOrDefault <Merchant_StockInHeader>(s => s.ma_phieu_nhap_kho == str[1]);
}
//new Merchant_StockInHeader_DAO().UpdateInventoryMySQL(data.ma_phieu, currentUser.ma_gian_hang, currentUser.name, AppConfigs.MCCConnectionString);
// return Json(new { success = true, data = item, JsonRequestBehavior.AllowGet });
return(Json(new { success = true, data = item, total = str[2], totalError = error, link = linkerror, JsonRequestBehavior.AllowGet }));
}
else
{
return(Json(new { success = false, error = str[2], JsonRequestBehavior.AllowGet }));
}
}
else
{
return(Json(new { success = false, error = "Bạn không có quyền thực hiên chức năng này" }));
}
}
public ActionResult Import(Merchant_StockInHeader data, List <Merchant_StockInDetail> details, HttpPostedFileBase FileUpload)
{
try
{
if (FileUpload == null || FileUpload.ContentLength == 0)
{
return(Json(new { success = false, message = "File rỗng." }));
}
var fileExtension = System.IO.Path.GetExtension(FileUpload.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 timenow = DateTime.Now.ToString("yyyyMMddHHmmss");
string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.name + "-" + timenow + FileUpload.FileName);
string errorFileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.name + "-" + timenow + "-Error]" + FileUpload.FileName);
string linkerror = "[" + currentUser.name + "-" + timenow + "-Error]" + FileUpload.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);
}
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
FileUpload.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\StockIn_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;
string productcode = "";
string unitcode = "";
List <Merchant_StockInDetail> lstImport = new List <Merchant_StockInDetail>();
var item = new Merchant_StockInHeader();
if (data.id == 0)
{
data.ma_gian_hang = currentUser.ma_gian_hang;
}
using (var dbConn = OrmliteConnection.openConn())
{
string culture = Request.Cookies["_culture"] != null ? Request.Cookies["_culture"].Value : "vi";
var listProduct = dbConn.Select <Merchant_Product>(s => s.trang_thai_duyet == AllConstant.trang_thai_duyet.DA_DUYET && s.trang_thai == AllConstant.trang_thai.DANG_SU_DUNG && s.ma_gian_hang == currentUser.ma_gian_hang);
var listUnit = dbConn.Select <Code_Master>(s => s.Type == "UnitProduct");
for (int i = 2; i <= totalRows; i++)
{
string masanpham = oSheet.Cells[i, 1].Value != null ? oSheet.Cells[i, 1].Value.ToString() : "";
string donvitinh = oSheet.Cells[i, 2].Value != null ? oSheet.Cells[i, 2].Value.ToString() : "";
string soluong = oSheet.Cells[i, 3].Value != null ? oSheet.Cells[i, 3].Value.ToString() : "0";
string ghichu = oSheet.Cells[i, 4].Value != null ? oSheet.Cells[i, 4].Value.ToString() : "";
if (string.IsNullOrEmpty(masanpham) || string.IsNullOrEmpty(donvitinh) || string.IsNullOrEmpty(soluong))
{
eSheet.Cells[rownumber, 1].Value = masanpham;
eSheet.Cells[rownumber, 2].Value = donvitinh;
eSheet.Cells[rownumber, 3].Value = soluong;
eSheet.Cells[rownumber, 4].Value = ghichu;
eSheet.Cells[rownumber, 5].Value = "Vui lòng nhập (*).";
rownumber++;
error++;
continue;
}
int sl = 0;
bool result = int.TryParse(soluong, out sl);
if (!result)
{
eSheet.Cells[rownumber, 1].Value = masanpham;
eSheet.Cells[rownumber, 2].Value = donvitinh;
eSheet.Cells[rownumber, 3].Value = soluong;
eSheet.Cells[rownumber, 4].Value = ghichu;
eSheet.Cells[rownumber, 5].Value = "Vui lòng nhập số nguyên.";
rownumber++;
error++;
continue;
}
if (sl <= 0)
{
eSheet.Cells[rownumber, 1].Value = masanpham;
eSheet.Cells[rownumber, 2].Value = donvitinh;
eSheet.Cells[rownumber, 3].Value = soluong;
eSheet.Cells[rownumber, 4].Value = ghichu;
eSheet.Cells[rownumber, 5].Value = "Vui lòng số lượng > 0.";
rownumber++;
error++;
continue;
}
if (masanpham.Split('-')[0].Length > 0)
{
productcode = masanpham.Split('-')[0];
if (listProduct.Where(s => s.ma_san_pham == productcode).FirstOrDefault() == null)
{
eSheet.Cells[rownumber, 1].Value = masanpham;
eSheet.Cells[rownumber, 2].Value = donvitinh;
eSheet.Cells[rownumber, 3].Value = soluong;
eSheet.Cells[rownumber, 4].Value = ghichu;
eSheet.Cells[rownumber, 5].Value = "Mã sản phẩm không tồn tại";
rownumber++;
error++;
continue;
}
}
else
{
eSheet.Cells[rownumber, 1].Value = masanpham;
eSheet.Cells[rownumber, 2].Value = donvitinh;
eSheet.Cells[rownumber, 3].Value = soluong;
eSheet.Cells[rownumber, 4].Value = ghichu;
eSheet.Cells[rownumber, 5].Value = "Mã sản phẩm không hợp lệ";
rownumber++;
error++;
continue;
}
if (donvitinh.Split('-')[0].Length > 0)
{
unitcode = donvitinh.Split('-')[0];
if (listUnit.Where(s => s.Value == unitcode).FirstOrDefault() == null)
{
eSheet.Cells[rownumber, 1].Value = masanpham;
eSheet.Cells[rownumber, 2].Value = donvitinh;
eSheet.Cells[rownumber, 3].Value = soluong;
eSheet.Cells[rownumber, 4].Value = ghichu;
eSheet.Cells[rownumber, 5].Value = "Đơn vị tính không tồn tại";
rownumber++;
error++;
continue;
}
}
else
{
eSheet.Cells[rownumber, 1].Value = masanpham;
eSheet.Cells[rownumber, 2].Value = donvitinh;
eSheet.Cells[rownumber, 3].Value = soluong;
eSheet.Cells[rownumber, 4].Value = ghichu;
eSheet.Cells[rownumber, 5].Value = "Đơn vị tính không hợp lệ";
rownumber++;
error++;
continue;
}
foreach (var items in lstImport)
{
if (items.ma_san_pham == productcode)
{
eSheet.Cells[rownumber, 1].Value = masanpham;
eSheet.Cells[rownumber, 2].Value = donvitinh;
eSheet.Cells[rownumber, 3].Value = soluong;
eSheet.Cells[rownumber, 4].Value = ghichu;
eSheet.Cells[rownumber, 5].Value = "Sản phẩm này đã được Import phía trên!";
rownumber++;
error++;
continue;
}
}
//Khoi tao
Merchant_StockInDetail detail = new Merchant_StockInDetail();
detail.ma_san_pham = productcode;
detail.don_vi_tinh = unitcode;
detail.so_luong_thuc_te = detail.so_luong_yeu_cau = sl;
detail.ghi_chu = ghichu;
//them vao lstImport
lstImport.Add(detail);
}
//string msg = new Merchant_StockInHeader_DAO().Upsert(data, details, false, currentUser.name, true, AppConfigs.MCCConnectionString);
//string[] separators = { "@@" };
//total = int.Parse(msg.Split(separators, StringSplitOptions.RemoveEmptyEntries)[2]);
//item = dbConn.FirstOrDefault<Merchant_StockInHeader>(s => s.ma_phieu_nhap_kho == msg.Split(separators, StringSplitOptions.RemoveEmptyEntries)[1]);
}
_excelPkg.Save();
return(CreateGoodReceivedNote(data, details, lstImport, error, linkerror, true, false));
//_excelPkg.Save();
//return Json(new { success = true,data = item, total = total, totalError = error, link = linkerror, JsonRequestBehavior.AllowGet });
}
catch (Exception ex)
{
return(Json(new
{
success = false,
message = ex.Message,
JsonRequestBehavior.AllowGet
}));
}
}
public string Upsert(Merchant_StockInHeader data, List <Merchant_StockInDetail> listGrid, List <Merchant_StockInDetail> lstImport, bool trang_thai, string UserName, string connectionString)
{
using (var db = new OrmliteConnection().openConn(connectionString))
{
using (var dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
var checkID = db.SingleOrDefault <Merchant_StockInHeader>("ma_phieu_nhap_kho={0} and ma_gian_hang = {1}", data.ma_phieu_nhap_kho, data.ma_gian_hang);
try
{
if (checkID != null)// cap nhat merchant_stockin_header
{
if (checkID.trang_thai == AllConstant.trang_thai_duyet.DA_DUYET)
{
dbTrans.Rollback();
return("Phiếu nhập kho này đã được duyệt không được cập nhật nữa!");
}
checkID.trang_thai = trang_thai ? AllConstant.trang_thai_duyet.DA_DUYET : AllConstant.trang_thai_duyet.NHAP;
checkID.nguoi_cap_nhat = UserName;
checkID.ngay_cap_nhat = DateTime.Now;
checkID.ngay_nhap_kho = DateTime.Now;
checkID.nguoi_giao = data.nguoi_giao;
checkID.ma_kho = data.ma_kho;
checkID.thu_kho = data.thu_kho;
checkID.ghi_chu = data.ghi_chu;
checkID.dia_diem = data.dia_diem;
db.Update(checkID);
}
else// tao moi merchant_stockin_header
{
var lastId = db.FirstOrDefault <Merchant_StockInHeader>("SELECT TOP 1 * FROM Merchant_StockInHeader ORDER BY id DESC");
if (lastId != null)
{
if (lastId.ma_phieu_nhap_kho.Contains("GRN"))
{
var nextNo = Int32.Parse(lastId.ma_phieu_nhap_kho.Substring(3, 10)) + 1;
data.ma_phieu_nhap_kho = "GRN" + String.Format("{0:0000000000}", nextNo);
}
}
else
{
data.ma_phieu_nhap_kho = "GRN" + "0000000001";
}
data.ngay_nhap_kho = DateTime.Now;
data.ngay_tao = DateTime.Now;
data.nguoi_tao = UserName;
data.ngay_cap_nhat = DateTime.Parse("1900-01-01");
data.trang_thai = trang_thai ? AllConstant.trang_thai_duyet.DA_DUYET : AllConstant.trang_thai_duyet.NHAP;
db.Insert(data);
}
dbTrans.Commit();
}
catch (Exception e)
{
dbTrans.Rollback();
return(e.Message.ToString());
}
}
using (var dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
try
{
int count = 0;
if (listGrid != null) // kiem tra data trong listGrid xem co gi ko
{
var checkData = db.Select <Merchant_StockInDetail>("ma_phieu_nhap_kho = {0} and ma_gian_hang = {1}", data.ma_phieu_nhap_kho, data.ma_gian_hang); // data ban đầu
if (checkData.Count == 0) // trong merchant_stockin_detail chua co data => them moi tất cả
{
foreach (Merchant_StockInDetail item in listGrid)
{
var checkProDetail = checkData.FirstOrDefault(s => s.ma_san_pham == item.ma_san_pham);
if (checkProDetail == null)// kiem tra xem sản phẩm này đã được thêm mới chưa
{
//item.id = 0;
item.ma_phieu_nhap_kho = data.ma_phieu_nhap_kho;
item.ma_gian_hang = data.ma_gian_hang;
item.vi_tri = !string.IsNullOrEmpty(item.vi_tri) ? item.vi_tri : "";
item.nguoi_tao = UserName;
item.ngay_tao = DateTime.Now;
item.trang_thai = trang_thai ? AllConstant.trang_thai_duyet.DA_DUYET : AllConstant.trang_thai_duyet.NHAP;
db.Insert(item);
}
}
}
else// trong merchant_stockin_detail da tồn tại data => cap nhật cái đã tồn tại
{
foreach (Merchant_StockInDetail item in listGrid)
{
item.vi_tri = !string.IsNullOrEmpty(item.vi_tri) ? item.vi_tri : "";
var checkProDetail = checkData.FirstOrDefault(s => s.ma_san_pham == item.ma_san_pham); // check với data ban đầu
if (checkProDetail == null) // cái chưa tồn tại => thêm mới
{
var checkProDetailDatabase = db.Select <Merchant_StockInDetail>("ma_phieu_nhap_kho = {0} and ma_gian_hang = {1} and ma_san_pham = {2}", data.ma_phieu_nhap_kho, data.ma_gian_hang, item.ma_san_pham); // data đã thay dổi
if (checkProDetailDatabase == null) // check với data đã thay dổi
{
//item.id = 0;
item.ma_phieu_nhap_kho = data.ma_phieu_nhap_kho;
item.ma_gian_hang = data.ma_gian_hang;
item.nguoi_tao = UserName;
item.ngay_tao = DateTime.Now;
item.trang_thai = trang_thai ? AllConstant.trang_thai_duyet.DA_DUYET : AllConstant.trang_thai_duyet.NHAP;
db.Insert(item);
}
}
else // cái đã tồn tại => cập nhật
{
if (item.id > 0)//kiểm tra them san pham moi mà sản phẩm đã tồn tại => ko cho phép thêm mới
{
//checkProDetail.vi_tri = !string.IsNullOrEmpty(item.vi_tri) ? item.vi_tri : "";
checkProDetail.ghi_chu = !string.IsNullOrEmpty(item.vi_tri) ? checkProDetail.vi_tri : "";
checkProDetail.so_luong_yeu_cau = item.so_luong_yeu_cau;
checkProDetail.so_luong_thuc_te = item.so_luong_thuc_te;
checkProDetail.don_vi_tinh = item.don_vi_tinh;
checkProDetail.nguoi_cap_nhat = UserName;
checkProDetail.ngay_cap_nhat = DateTime.Now;
checkProDetail.trang_thai = trang_thai ? AllConstant.trang_thai_duyet.DA_DUYET : AllConstant.trang_thai_duyet.NHAP;
db.Update(checkProDetail);
checkData.Remove(checkProDetail);
}
}
}
if (checkData != null)
{
foreach (Merchant_StockInDetail item in checkData)
{
db.Delete <Merchant_StockInDetail>("id = {0}", item.id);
}
}
}
}
//else // nếu trong listGrid k có data thì xóa tất cả record đang có trong database merchant_stockin_detail
//{
// db.Delete<Merchant_StockInDetail>("ma_phieu_nhap_kho = {0} and ma_gian_hang = {1}", data.ma_phieu_nhap_kho, data.ma_gian_hang);
//}
if (lstImport != null) // kiểm tra lstImport có data ko
{
var checkData = db.Select <Merchant_StockInDetail>("ma_phieu_nhap_kho = {0} and ma_gian_hang = {1}", data.ma_phieu_nhap_kho, data.ma_gian_hang);
if (checkData.Count == 0) // kiểm tra xem trong merchant_stockin_detail ko thấy có data => thêm mới toàn bộ
{
foreach (Merchant_StockInDetail item in lstImport)
{
item.id = 0;
item.ma_phieu_nhap_kho = data.ma_phieu_nhap_kho;
item.ma_gian_hang = data.ma_gian_hang;
item.vi_tri = !string.IsNullOrEmpty(item.vi_tri) ? item.vi_tri : "";
item.nguoi_tao = UserName;
item.ngay_tao = DateTime.Now;
item.trang_thai = trang_thai ? AllConstant.trang_thai_duyet.DA_DUYET : AllConstant.trang_thai_duyet.NHAP;
db.Insert(item);
count++;
}
}
else // thấy trong merchant_stockin_detail có data => thêm mới + cập nhật
{
foreach (Merchant_StockInDetail item in lstImport)
{
item.vi_tri = !string.IsNullOrEmpty(item.vi_tri) ? item.vi_tri : "";
var checkProDetail = checkData.FirstOrDefault(s => s.ma_san_pham == item.ma_san_pham);
if (checkProDetail == null)// sản phẩm này chưa có => thêm mới
{
item.id = 0;
item.ma_phieu_nhap_kho = data.ma_phieu_nhap_kho;
item.ma_gian_hang = data.ma_gian_hang;
item.nguoi_tao = UserName;
item.ngay_tao = DateTime.Now;
item.trang_thai = trang_thai ? AllConstant.trang_thai_duyet.DA_DUYET : AllConstant.trang_thai_duyet.NHAP;
db.Insert(item);
count++;
}
else // sản phẩm đã có => (UPDATE) cộng dồn số lượng
{
checkProDetail.so_luong_thuc_te = checkProDetail.so_luong_thuc_te + item.so_luong_thuc_te;
checkProDetail.so_luong_yeu_cau = checkProDetail.so_luong_yeu_cau + item.so_luong_yeu_cau;
checkProDetail.ghi_chu = !string.IsNullOrEmpty(item.ghi_chu) ? item.ghi_chu : checkProDetail.ghi_chu;
checkProDetail.don_vi_tinh = item.don_vi_tinh;
checkProDetail.nguoi_cap_nhat = UserName;
checkProDetail.ngay_cap_nhat = DateTime.Now;
checkProDetail.trang_thai = trang_thai ? AllConstant.trang_thai_duyet.DA_DUYET : AllConstant.trang_thai_duyet.NHAP;
db.Update(checkProDetail);
count++;
checkData.Remove(checkProDetail);
}
}
if (checkData != null)
{
foreach (Merchant_StockInDetail item in checkData)
{
db.Delete <Merchant_StockInDetail>("id = {0}", item.id);
}
}
}
}
if (listGrid == null && lstImport == null)// xóa hết tất cả data trong Merchant_StockInDetail khi cả 2 list data k có data nào
{
db.Delete <Merchant_StockInDetail>("ma_phieu_nhap_kho = {0} and ma_gian_hang = {1}", data.ma_phieu_nhap_kho, data.ma_gian_hang);
}
//else
//{
// db.Delete<Merchant_StockInDetail>("ma_phieu_nhap_kho = {0} and ma_gian_hang = {1}", data.ma_phieu_nhap_kho, data.ma_gian_hang);
//}
dbTrans.Commit();
return("true@@" + data.ma_phieu_nhap_kho + "@@" + count.ToString());
}
catch (Exception ex)
{
dbTrans.Rollback();
return("false@@" + data.ma_phieu_nhap_kho + " " + ex.Message);
}
}
}
}
