public string Create(List <Merchant_Product_Image> list, string UserName, string connectionString)
{
using (var db = new OrmliteConnection().openConn(connectionString))
{
using (var dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
try
{
if (list == null)
{
dbTrans.Rollback();
return("No Data");
}
db.Delete <Merchant_Product_Image>("ma_san_pham = {0} and ma_gian_hang = {1}", list.ElementAt(0).ma_san_pham, list.ElementAt(0).ma_gian_hang);
foreach (Merchant_Product_Image item in list)
{
item.ngay_tao = DateTime.Now;
item.nguoi_tao = UserName;
}
dbTrans.Commit();
return("true");
}
catch (Exception e)
{
dbTrans.Rollback();
return(e.Message.ToString());
}
}
}
}
public ActionResult Deactive(string data)
{
if (userAsset.ContainsKey("Delete") && userAsset["Delete"])
{
IDbConnection dbConn = new OrmliteConnection().openConn();
try
{
var newdata = data.Split(',');
foreach (string id in newdata)
{
dbConn.Delete <Master_Announcement>(p => p.AnnouncementID == Convert.ToInt32(id));
}
return(Json(new { success = true, }));
}
catch (Exception ex)
{
return(Json(new { success = false, message = ex.Message }));
}
finally
{
dbConn.Close();
}
}
else
{
return(Json(new { success = false, message = "You don't have permission" }));
}
}
//public ActionResult ReadDetail([DataSourceRequest] DataSourceRequest request, string SONumber)
//{
// log4net.Config.XmlConfigurator.Configure();
// string whereCondition = "";
// if (request.Filters.Count > 0)
// {
// whereCondition = " AND " + new KendoApplyFilter().ApplyFilter(request.Filters[0]);
// }
// var data = new DC_AD_SO_Detail().GetPage(request, whereCondition, SONumber);
// return Json(data);
//}
public ActionResult Update([DataSourceRequest] DataSourceRequest request, [Bind(Prefix = "models")] IEnumerable <Products> list, string SONumber)
{
var dbConn = new OrmliteConnection().openConn();
try
{
if (list != null && ModelState.IsValid)
{
foreach (var item in list)
{
if (item.Qty > 0)
{
//string SONumber = Request["SONumber"];
var header = new SOHeader();
var detail = new SODetail();
if (dbConn.Select <SODetail>(s => s.SONumber == SONumber && s.ItemCode == item.Code).Count() > 0)
{
dbConn.Update <SODetail>(set: "Qty = '" + item.Qty + "', TotalAmt = '" + item.Qty * item.Price + "'", where : "SONumber = '" + SONumber + "'");
}
else
{
var data = new SODetail();
data.ItemName = item.Name;
data.ItemCode = item.Code;
data.Note = "";
data.Price = item.VATPrice;
data.Qty = item.Qty;
data.SONumber = SONumber;
data.UnitID = item.Unit;
data.UnitName = dbConn.Select <INUnit>(s => s.UnitID == item.Unit).FirstOrDefault().UnitName;
data.TotalAmt = item.Qty * item.VATPrice;
data.Status = "";
data.CreatedAt = DateTime.Now;
data.CreatedBy = currentUser.UserID;
//data.UpdatedAt = DateTime.Parse("1900-01-01");
data.UpdatedAt = DateTime.Now;
data.UpdatedBy = "";
dbConn.Insert <SODetail>(data);
}
dbConn.Update <SOHeader>(set: "TotalQty ='" + dbConn.Select <SODetail>(s => s.SONumber == SONumber).Sum(s => s.Qty) + "', TotalAmt = '" + +dbConn.Select <SODetail>(s => s.SONumber == SONumber).Sum(s => s.TotalAmt) + "'", where : "SONumber ='" + SONumber + "'");
}
else
{
dbConn.Delete <SOHeader>(s => s.SONumber == SONumber);
ModelState.AddModelError("error", "Đơn hàng được tạo khi số lượng > 0");
return(Json(list.ToDataSourceResult(request, ModelState)));
}
}
}
dbConn.Close();
}
catch (Exception e)
{
dbConn.Close();
ModelState.AddModelError("error", e.Message);
return(Json(list.ToDataSourceResult(request, ModelState)));
}
return(Json(new { sussess = true }));
}
public ActionResult AddUserToGroup(int id, string data)
{
IDbConnection db = new OrmliteConnection().openConn();
try
{
// Delete All User in Role
db.Delete <Auth_UserInRole>(p => p.RoleID == id);
// Add User Role
if (!string.IsNullOrEmpty(data))
{
string[] arr = data.Split(',');
foreach (string item in arr)
{
var detail = new Auth_UserInRole();
detail.UserID = item;
detail.RoleID = id;
detail.RowCreatedAt = DateTime.Now;
detail.RowCreatedBy = currentUser.UserID;
db.Insert <Auth_UserInRole>(detail);
}
}
return(Json(new { success = true }));
}
catch (Exception e) { return(Json(new { success = false, message = e.Message })); }
finally { db.Close(); }
}
public ActionResult AddTranporterToContract(string id, string data)
{
IDbConnection db = new OrmliteConnection().openConn();
try
{
db.Delete <DC_LG_Contract_Transporter>(p => p.ContractID == id);
if (!string.IsNullOrEmpty(data))
{
string[] arr = data.Split(',');
foreach (string item in arr)
{
var detail = new DC_LG_Contract_Transporter();
detail.ContractID = id;
detail.TransporterID = int.Parse(item);
detail.Note = "";
detail.UpdatedAt = DateTime.Now;
detail.CreatedAt = DateTime.Now;
detail.CreatedBy = currentUser.UserID;
detail.UpdatedBy = currentUser.UserID;
db.Insert <DC_LG_Contract_Transporter>(detail);
}
}
return(Json(new { success = true }));
}
catch (Exception e) { return(Json(new { success = false, message = e.Message })); }
finally { db.Close(); }
}
public ActionResult DeleteDetail(string data, string SONumber)
{
var dbConn = new OrmliteConnection().openConn();
if (userAsset.ContainsKey("Delete") && userAsset["Delete"])
{
try
{
string[] separators = { "@@" };
var listdata = data.Split(separators, StringSplitOptions.RemoveEmptyEntries);
if (dbConn.Select <SOHeader>(s => s.SONumber == SONumber && s.Status != "Mới").Count() > 0)
{
return(Json(new { success = false, message = "Đơn hàng đã xác nhận nên không được xóa." }));
}
foreach (var item in listdata)
{
dbConn.Delete <SODetail>(s => s.Id == int.Parse(item));
}
if (dbConn.Select <SODetail>(s => s.SONumber == SONumber).Count() <= 0)
{
dbConn.Delete <SOHeader>(s => s.SONumber == SONumber);
}
return(Json(new { success = true }));
}
catch (Exception e)
{
return(Json(new { success = false, message = e.Message }));
}
dbConn.Close();
}
else
{
dbConn.Close();
return(Json(new { success = false, message = "Bạn không có quyền xóa dữ liệu." }));
}
}
public string Delete(string[] ids, string connectionString)
{
using (var db = new OrmliteConnection().openConn(connectionString))
{
try
{
foreach (var id in ids)
{
db.Delete <Merchant_Warehouse>(s => s.ma_kho == id);
}
return("true");
}
catch (Exception e)
{
return(e.Message.ToString());
}
}
}
public string Delete(string[] ids, string connectionString)
{
using (var db = new OrmliteConnection().openConn(connectionString))
{
try
{
foreach (var id in ids)
{
db.Delete <Hierarchy>(s => s.id == int.Parse(id));
}
return("true");
}
catch (Exception e)
{
return(e.Message.ToString());
}
}
}
public string DeleteForCode(string ma_gian_hang, string connectionString)
{
using (var db = new OrmliteConnection().openConn(connectionString))
{
using (var dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
try
{
db.Delete <Merchant_Image>(s => s.ma_gian_hang == ma_gian_hang);
dbTrans.Commit();
return("true");
}
catch (Exception e)
{
dbTrans.Rollback();
return(e.Message.ToString());
}
}
}
}
public string Delete(string[] ids, string connectionString)
{
using (var db = new OrmliteConnection().openConn(connectionString))
{
using (var dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
try
{
foreach (var id in ids)
{
db.Delete <Merchant_Image>(s => s.id == int.Parse(id));
}
dbTrans.Commit();
return("true");
}
catch (Exception e)
{
dbTrans.Rollback();
return(e.Message.ToString());
}
}
}
}
public string Delete(string[] ids, string connectionString)
{
using (var db = new OrmliteConnection().openConn(connectionString))
{
using (var dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
try
{
var checkID = db.SingleOrDefault <Image_Info>("id={0}", ids[0]);
foreach (var id in ids)
{
db.Delete <Image_Info>(s => s.id == int.Parse(id));
}
dbTrans.Commit();
return("true");
}
catch (Exception e)
{
dbTrans.Rollback();
return(e.Message.ToString());
}
}
}
}
public string Delete(string[] ids, string connectionString)
{
using (var db = new OrmliteConnection().openConn(connectionString))
{
using (var dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
try
{
foreach (var id in ids)
{
var checkID = db.FirstOrDefault <Product_Info>(s => s.id == int.Parse(id));
db.Delete <Product_Info>(s => s.id == int.Parse(id));
// Xóa ảnh
if (checkID != null)
{
if (!String.IsNullOrEmpty(checkID.url))
{
var path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Images/Product_Info/"), checkID.url);
if (!String.IsNullOrEmpty(path))
{
System.IO.File.Delete(path);
}
}
}
}
dbTrans.Commit();
return("true");
}
catch (Exception e)
{
dbTrans.Rollback();
return(e.Message.ToString());
}
}
}
}
public string UpsertFull(Merchant_Product_Promotion promotion, List <string> products, string UserName, string connectionString)
{
using (var db = new OrmliteConnection().openConn(connectionString))
{
Merchant_Product_Promotion checkID;
checkID = db.SingleOrDefault <Merchant_Product_Promotion>("id={0} and ma_gian_hang = {1}", promotion.id, promotion.ma_gian_hang);
//Transaction for Promotion
using (var dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
try
{
if (checkID != null)
{
promotion.ma_chuong_trinh_km = checkID.ma_chuong_trinh_km;
checkID.nguoi_cap_nhat = UserName;
checkID.ngay_cap_nhat = DateTime.Now;
checkID.ten_chuong_trinh_km = promotion.ten_chuong_trinh_km;
checkID.loai = promotion.loai;
checkID.gia_tri = promotion.gia_tri;
checkID.trang_thai = promotion.trang_thai;
checkID.ngay_bat_dau = promotion.ngay_bat_dau;
checkID.ngay_ket_thuc = promotion.ngay_ket_thuc;
db.Update(checkID);
}
else
{
var lastId = db.FirstOrDefault <Merchant_Product_Promotion>("SELECT TOP 1 * FROM Merchant_Product_Promotion ORDER BY id DESC");
if (lastId != null)
{
if (lastId.ma_chuong_trinh_km.Contains("KM"))
{
var nextNo = Int32.Parse(lastId.ma_chuong_trinh_km.Substring(2, 7)) + 1;
promotion.ma_chuong_trinh_km = "KM" + String.Format("{0:0000000}", nextNo);
}
}
else
{
promotion.ma_chuong_trinh_km = "KM" + "0000001";
}
promotion.trang_thai = AllConstant.trang_thai.DANG_SU_DUNG;
promotion.ngay_tao = DateTime.Now;
promotion.nguoi_tao = UserName;
promotion.ngay_cap_nhat = DateTime.Parse("1900-1-1");
db.Insert(promotion);
}
dbTrans.Commit();
}
catch (Exception e)
{
dbTrans.Rollback();
return(e.Message.ToString());
}
}
//Transaction for Promotion Details
using (var dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
try
{
if (products != null)
{
var checkData = db.Select <Merchant_Product_Promotion_Detail>("ma_chuong_trinh_km = {0} and ma_gian_hang = {1}", promotion.ma_chuong_trinh_km, promotion.ma_gian_hang);
if (checkData == null)
{
foreach (string ma_san_pham in products)
{
Merchant_Product_Promotion_Detail item = new Merchant_Product_Promotion_Detail();
item.id = 0;
item.ma_san_pham = ma_san_pham;
item.ma_gian_hang = promotion.ma_gian_hang;
item.ma_chuong_trinh_km = promotion.ma_chuong_trinh_km;
item.nguoi_tao = UserName;
item.ngay_tao = DateTime.Now;
db.Insert(item);
}
}
else
{
foreach (string ma_san_pham in products)
{
var checkProDetail = checkData.FirstOrDefault(s => s.ma_san_pham == ma_san_pham);
if (checkProDetail == null)
{
Merchant_Product_Promotion_Detail item = new Merchant_Product_Promotion_Detail();
item.id = 0;
item.ma_san_pham = ma_san_pham;
item.ma_gian_hang = promotion.ma_gian_hang;
item.ma_chuong_trinh_km = promotion.ma_chuong_trinh_km;
item.nguoi_tao = UserName;
item.ngay_tao = DateTime.Now;
db.Insert(item);
}
else
{
checkData.Remove(checkProDetail);
}
}
if (checkData != null)
{
foreach (Merchant_Product_Promotion_Detail item in checkData)
{
db.Delete <Merchant_Product_Promotion_Detail>("ma_chuong_trinh_km = {0} and ma_gian_hang = {1} and ma_san_pham = {2}", item.ma_chuong_trinh_km, item.ma_gian_hang, item.ma_san_pham);
}
}
}
}
else
{
db.Delete <Merchant_Product_Promotion_Detail>("ma_chuong_trinh_km = {0} and ma_gian_hang = {1}", promotion.ma_chuong_trinh_km, promotion.ma_gian_hang);
}
dbTrans.Commit();
}
catch (Exception e)
{
dbTrans.Rollback();
return(e.Message.ToString());
}
}
}
return("true" + promotion.ma_chuong_trinh_km);
}
public ActionResult Create(DC_LG_Contract item)
{
IDbConnection db = new OrmliteConnection().openConn();
try
{
if (!string.IsNullOrEmpty(item.ContractID) &&
!string.IsNullOrEmpty(item.ContractName)
)
{
var isExist = db.SingleOrDefault <DC_LG_Contract>("ContractID={0}", item.ContractID);
var data = Request["TransporterID"];
//string data = !string.IsNullOrEmpty(item.TransporterID) ? item.TransporterID : "";
double n;
item.StartDate = item.StartDate != null ? item.StartDate : DateTime.Now;
item.EndDate = item.EndDate != null ? item.EndDate : DateTime.Now;
item.DiscountPercent = double.TryParse(item.DiscountPercent.ToString(), out n) ? item.DiscountPercent / 100 : 0;
if (item.StartDate > item.EndDate)
{
return(Json(new { success = false, message = "Ngày kết thúc không thể lớn hơn " + item.StartDate }));
}
item.Note = !string.IsNullOrEmpty(item.Note) ? item.Note : "";
item.DiscountPercent = !string.IsNullOrEmpty(item.DiscountPercent.ToString()) ? item.DiscountPercent : 0;
if (userAsset.ContainsKey("Insert") && userAsset["Insert"] && item.CreatedAt == null && item.CreatedBy == null)
{
if (isExist != null)
{
return(Json(new { success = false, message = "Mã hợp đồng đã tồn tại" }));
}
item.ContractName = !string.IsNullOrEmpty(item.ContractName) ? item.ContractName : "";
item.CreatedAt = DateTime.Now;
item.UpdatedAt = DateTime.Parse("1900-01-01");
item.CreatedBy = currentUser.UserID;
db.Insert(item);
db.Delete <DC_LG_Contract_Transporter>(p => p.ContractID == item.ContractID);
if (!string.IsNullOrEmpty(data))
{
string[] arr = data.Split(',');
foreach (string ite in arr)
{
var detail = new DC_LG_Contract_Transporter();
detail.ContractID = item.ContractID;
detail.TransporterID = int.Parse(ite);
detail.Note = "";
detail.UpdatedAt = DateTime.Now;
detail.CreatedAt = DateTime.Now;
detail.CreatedBy = currentUser.UserID;
detail.UpdatedBy = currentUser.UserID;
db.Insert <DC_LG_Contract_Transporter>(detail);
}
}
return(Json(new { success = true, ContractID = item.ContractID, CreatedBy = item.CreatedBy, CreatedAt = item.CreatedAt, }));
}
else if (userAsset.ContainsKey("Update") && userAsset["Update"] && isExist != null)
{
item.ContractName = !string.IsNullOrEmpty(item.ContractName) ? item.ContractName : "";
item.CreatedBy = isExist.CreatedBy;
item.CreatedAt = isExist.CreatedAt;
item.UpdatedAt = DateTime.Now;
item.UpdatedBy = currentUser.UserID;
db.Update(item);
db.Delete <DC_LG_Contract_Transporter>(p => p.ContractID == item.ContractID);
if (!string.IsNullOrEmpty(data))
{
string[] arr = data.Split(',');
foreach (string ite in arr)
{
var detail = new DC_LG_Contract_Transporter();
detail.ContractID = item.ContractID;
detail.TransporterID = int.Parse(ite);
detail.Note = "";
detail.UpdatedAt = DateTime.Now;
detail.CreatedAt = DateTime.Now;
detail.CreatedBy = currentUser.UserID;
detail.UpdatedBy = currentUser.UserID;
db.Insert <DC_LG_Contract_Transporter>(detail);
}
}
return(Json(new { success = true }));
}
else
{
return(Json(new { success = false, message = "Bạn không có quyền" }));
}
}
else
{
return(Json(new { success = false, message = "Chưa nhập đủ giá trị" }));
}
}
catch (Exception e)
{
log.Error("Contract - Create - " + e.Message);
return(Json(new { success = false, message = e.Message }));
}
finally { db.Close(); }
}
public string UpdateProduct(Merchant_Product product, Merchant_Product_Hierarchy hierarchy, List <Merchant_Product_Image> listimage, List <Merchant_Product_Property> listproperty, string connectstring)
{
using (var db = new OrmliteConnection().openConn(connectstring))
{
try
{
Merchant_Product checkProduct;
if (product.ma_gian_hang == "All")
{
checkProduct = db.FirstOrDefault <Merchant_Product>("ma_san_pham = {0}", product.ma_san_pham);
}
else
{
checkProduct = db.FirstOrDefault <Merchant_Product>("ma_san_pham = {0} and ma_gian_hang = {1}", product.ma_san_pham, product.ma_gian_hang);
}
if (checkProduct == null)
{
return("Thông tin không hợp lệ");
}
checkProduct.url = (!string.IsNullOrEmpty(product.url) && checkProduct.url != product.url) ? product.url : checkProduct.url;
checkProduct.catalog = (!string.IsNullOrEmpty(product.catalog) && checkProduct.catalog != product.catalog) ? product.catalog : checkProduct.catalog;
checkProduct.ma_loai_san_pham = !string.IsNullOrEmpty(product.ma_loai_san_pham) ? product.ma_loai_san_pham : checkProduct.ma_loai_san_pham;
checkProduct.part_no = !string.IsNullOrEmpty(product.part_no) ? product.part_no : checkProduct.part_no;
checkProduct.ten_san_pham = !string.IsNullOrEmpty(product.ten_san_pham) ? product.ten_san_pham : checkProduct.ten_san_pham;
checkProduct.mo_ta = !string.IsNullOrEmpty(product.mo_ta) ? product.mo_ta : "";
checkProduct.noi_dung = !string.IsNullOrEmpty(product.noi_dung) ? product.noi_dung : "";
checkProduct.tu_khoa = !string.IsNullOrEmpty(product.tu_khoa) ? product.tu_khoa : "";
checkProduct.tag = !string.IsNullOrEmpty(product.tag) ? product.tag : "";
checkProduct.slug = !string.IsNullOrEmpty(product.slug) ? product.slug : "";
checkProduct.xuat_xu = !string.IsNullOrEmpty(product.xuat_xu) ? product.xuat_xu : "";
checkProduct.thuong_hieu = !string.IsNullOrEmpty(product.thuong_hieu) ? product.thuong_hieu : "";
checkProduct.model = !string.IsNullOrEmpty(product.model) ? product.model : "";
checkProduct.khoi_luong = product.khoi_luong;
checkProduct.gia_si = product.gia_si;
checkProduct.don_gia = product.don_gia;
checkProduct.trang_thai = product.trang_thai;
checkProduct.trang_thai_xuat_ban = AllConstant.trang_thai_xuat_ban.CHUA_XUAT_BAN;
checkProduct.trang_thai_duyet = AllConstant.trang_thai_duyet.CHUA_DUYET;
checkProduct.nguoi_cap_nhat = product.nguoi_cap_nhat;
checkProduct.ngay_cap_nhat = DateTime.Now;
db.Update(checkProduct);
//List<SqlParameter> param = new List<SqlParameter>();
//param.Add(new SqlParameter("@id", checkProduct.id));
//new SqlHelper().ExecuteQuery("p_UpdateSlugForMerchantProduct", param);
// Update Hierarchy
var checkHierarchy = db.FirstOrDefault <Merchant_Product_Hierarchy>(@"ma_san_pham = {0} and ma_gian_hang = {1} and
ma_cay_phan_cap_1 = {2} and ma_cay_phan_cap_2 = {3}
and ma_cay_phan_cap_3 = {4} ",
checkProduct.ma_san_pham,
checkProduct.ma_gian_hang,
hierarchy.ma_cay_phan_cap_1,
hierarchy.ma_cay_phan_cap_2,
hierarchy.ma_cay_phan_cap_3);
if (checkHierarchy != null)
{
db.ExecuteNonQuery(@"update Merchant_Product_Hierarchy set
ma_cay_phan_cap_1 = {0},
ma_cay_phan_cap_2 = {1},
ma_cay_phan_cap_3 = {2},
nguoi_cap_nhat = {3},
ngay_cap_nhat = {4}
where ma_san_pham = {5} and ma_gian_hang = {6}".Params(hierarchy.ma_cay_phan_cap_1,
hierarchy.ma_cay_phan_cap_2,
hierarchy.ma_cay_phan_cap_3,
product.nguoi_cap_nhat,
DateTime.Now,
checkProduct.ma_san_pham,
checkProduct.ma_gian_hang));
}
else
{
hierarchy.ma_san_pham = checkProduct.ma_san_pham;
hierarchy.ma_gian_hang = checkProduct.ma_gian_hang;
hierarchy.ngay_tao = DateTime.Now;
hierarchy.nguoi_tao = checkProduct.nguoi_tao;
hierarchy.ngay_cap_nhat = DateTime.Parse("1900-01-01");
hierarchy.trang_thai = AllConstant.trang_thai.DANG_SU_DUNG; // Giá trị đang hoạt động
db.Insert(hierarchy);
}
// Update Thumbnail
if (listimage != null)
{
db.Delete <Merchant_Product_Image>("ma_san_pham = {0} and ma_gian_hang = {1}", checkProduct.ma_san_pham, checkProduct.ma_gian_hang);
foreach (Merchant_Product_Image item in listimage)
{
if (item.id == 0)
{
item.ma_san_pham = checkProduct.ma_san_pham;
item.ma_gian_hang = checkProduct.ma_gian_hang;
item.ngay_tao = DateTime.Now;
item.nguoi_tao = product.nguoi_tao;
item.ngay_cap_nhat = DateTime.Now;
item.nguoi_cap_nhat = product.nguoi_cap_nhat;
db.Insert(item);
}
else
{
return("Lỗi khi tạo thumbnail hình ảnh");
}
}
}
// Update Property
if (listproperty != null)
{
db.Delete <Merchant_Product_Property>("ma_san_pham = {0} and ma_gian_hang = {1}", checkProduct.ma_san_pham, checkProduct.ma_gian_hang);
foreach (Merchant_Product_Property item in listproperty)
{
if (item.id == 0)
{
item.ma_gian_hang = checkProduct.ma_gian_hang;
item.ma_san_pham = checkProduct.ma_san_pham;
item.ngay_tao = DateTime.Now;
item.ngay_cap_nhat = DateTime.Parse("1900-01-01");
item.nguoi_cap_nhat = product.nguoi_cap_nhat;
item.ngay_cap_nhat = DateTime.Now;
db.Insert(item);
}
else
{
return("Lỗi khi cập nhật thuộc tính");
}
}
}
//SyncToMySQL(checkProduct.id.ToString(), product.nguoi_cap_nhat);
SyncToMySQL(checkProduct.id.ToString(), connectstring);
return("true");
}
catch (Exception ex)
{
return("Lỗi thao tác dữ liệu");
}
}
}
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);
}
}
}
}