public JsonResult UpdateData(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
try
{
var qry = $"DELETE {Common.Objects.TYPE_HD.HD_DD} WHERE KYHOADON='{obj.KYHD}'";
SQLServer.Connection.Query(qry);
qry = $@"INSERT INTO {Common.Objects.TYPE_HD.HD_DD}
SELECT NEWID() AS ID,KYHOADON,ID AS DD_ID,NEWID() AS DBKH_ID,TYPE_BILL,APP_ID,SO_TB,MA_DVI1,MA_CBT1,KIEU,INCHITIET,EZPAY,IS_GROUP,SL_MAY,
TIEN_SDTK,CUOC_TB,CAREPLUS,CUOC_KTHUE,CUOC_KM,CUOC_CDVU,CUOC_TT,CUOC_PSDV,TIEN_TTRUO,TIEN_DCOC,GIAM_TRU,CUOC_GTRU,0 AS TONG_IN,TONG,VAT,TONGCONG,DUPE_FLAG
FROM {Common.Objects.TYPE_HD.DD} WHERE KYHOADON='{obj.KYHD}' AND TONGCONG>0";
SQLServer.Connection.Query(qry);
qry = $"UPDATE a SET a.DBKH_ID=b.ID FROM {Common.Objects.TYPE_HD.HD_DD} a INNER JOIN {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} b ON a.SO_TB=b.MA_TB WHERE a.TYPE_BILL=b.TYPE_BILL AND b.FIX=0 AND b.FLAG=1 and KYHOADON='{obj.KYHD}'";
SQLServer.Connection.Query(qry);
qry = $"select * from {Common.Objects.TYPE_HD.HD_DD} where KYHOADON='{obj.KYHD}'";
var data = SQLServer.Connection.Query <Models.HD_DD>(qry);
return(Json(new { success = $"{Common.Objects.TYPE_HD.HD_DD} - Cập nhật dữ liệu thành công - {data.Count()} thuê bao" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
}
public JsonResult UpdateQuanHuyen(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1");
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
try
{
//Get Data HNI
var qry = $"SELECT * FROM QUAN_HUYEN_BKN";
var data_hni = Oracle.Connection.Query <Models.QUAN_HUYEN_BKN>(qry).ToList();
//Delete Old
qry = "DELETE QUAN_HUYEN_BKN";
SQLServer.Connection.Query(qry);
//Insert Data
SQLServer.Connection.Insert(data_hni);
return(Json(new { success = $"QUAN_HUYEN_BKN - Cập nhật thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally
{
SQLServer.Close();
Oracle.Close();
}
}
public ActionResult BackupDatabase()
{
var SqlServer = new TM.Connection.SQLServer();
try
{
var path = TM.Common.Directories.DBBak;
TM.IO.FileDirectory.CreateDirectory(path, false);
//TM.IO.FileDirectory.SetAccessRule(path, false);
var backup = new TM.SQLServer.Backup(path);
backup.BackingAll(SqlServer.Connection);
//InsertDirectory(TM.Common.Directories.DBBak + "test\\abc");
var a = InsertDirectoriesFiles(path);
this.success("thành công");
}
catch (Exception ex)
{
this.danger(ex.Message);
}
finally
{
SqlServer.Close();
}
return(RedirectToAction("Index"));
}
public JsonResult UpdateDBDT(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1");
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
try
{
//Get Data HNI
var qry = $"SELECT * FROM DB_DUONGTHU_BKN";
var data_hni = Oracle.Connection.Query <Models.DB_DUONGTHU_BKN>(qry).ToList();
//Update Data
foreach (var i in data_hni)
{
i.ID = Guid.NewGuid();
i.FIX = 0;
i.FLAG = 1;
i.MA_DT = i.MA_DT.Trim().ToUpper();
}
//Delete Old
qry = "DELETE DB_DUONGTHU_BKN WHERE FIX=0";
SQLServer.Connection.Query(qry);
//Insert Data
SQLServer.Connection.Insert(data_hni);
return(Json(new { success = $"DB_DUONGTHU_BKN - Cập nhật thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally
{
SQLServer.Close();
Oracle.Close();
}
}
public JsonResult UpdatePrice(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var TYPE_BILL = "4";
try
{
//Cập nhật tổng
var qry = $"UPDATE hd SET hd.TONG=bg.GIA FROM HD_TSL hd INNER JOIN BGCUOC bg ON bg.PROFILEIP LIKE '%,'+hd.TOC_DO+',%' WHERE TYPE_BILL={TYPE_BILL} AND bg.GOICUOCID=0 AND TT_THANG=1 AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
SQLServer.Connection.Query(qry);
qry = $"UPDATE hd SET hd.TONG=ROUND((bg.GIA/{obj.day_in_month})*NGAY_TB,0) FROM HD_TSL hd INNER JOIN BGCUOC bg ON bg.PROFILEIP LIKE '%,'+hd.TOC_DO+',%' WHERE TYPE_BILL={TYPE_BILL} AND bg.GOICUOCID=0 AND TT_THANG=2 AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
SQLServer.Connection.Query(qry);
//Cập nhật vat và tổng cộng
qry = $@"UPDATE HD_TSL SET VAT=ROUND(TONG*0.1,0) WHERE TYPE_BILL={TYPE_BILL} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}';
UPDATE HD_TSL SET TONGCONG=TONG+VAT WHERE TYPE_BILL={TYPE_BILL} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}';";
SQLServer.Connection.Query(qry);
return(Json(new { success = $"HD_TSL - Cập nhật giá thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally
{
SQLServer.Close();
}
}
public JsonResult XuLyTichHopxxx(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var TYPE_BILL = "4";
try
{
var qry = $@"UPDATE hd SET hd.GOICUOCID=thdv.LOAIGOICUOC_ID FROM HD_TSL hd,DANHBA_GOICUOC_TICHHOP thdv WHERE hd.MA_TB=thdv.MA_TB AND thdv.NGAY_KT>=CAST('{obj.block_time}' as datetime) AND hd.TYPE_BILL=8 AND FORMAT(hd.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND FORMAT(thdv.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND thdv.FIX=0 AND thdv.FLAG=1;
UPDATE hd SET hd.GOICUOCID=thdv.LOAIGOICUOC_ID FROM HD_TSL hd,DANHBA_GOICUOC_TICHHOP thdv WHERE hd.MA_TB=thdv.MA_TB AND thdv.NGAY_BD<CAST('{obj.block_time}' as datetime) AND thdv.NGAY_KT IS NULL AND hd.TYPE_BILL=8 AND FORMAT(hd.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND FORMAT(thdv.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND thdv.FIX=0 AND thdv.FLAG=1;";
SQLServer.Connection.Query(qry);
//Xử lý tích hợp thêm
qry = $"UPDATE hd SET hd.GOICUOCID=thdv.LOAIGOICUOC_ID FROM HD_TSL hd INNER JOIN DANHBA_GOICUOC_TICHHOP thdv ON hd.MA_TB=thdv.MA_TB WHERE hd.TYPE_BILL=8 AND FORMAT(hd.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND thdv.DICHVUVT_ID=8 AND thdv.FIX=1";
SQLServer.Connection.Query(qry);
//Cập nhật giá từ bảng giá đối với thuê bao tích hợp
qry = $@"UPDATE hd SET hd.TONG=bg.GIA+hd.PAYTV_FEE FROM HD_TSL hd INNER JOIN BGCUOC bg ON hd.GOICUOCID=bg.GOICUOCID WHERE hd.GOICUOCID>0 AND bg.DICHVUVT_ID=8 AND hd.TYPE_BILL=8 AND FORMAT(hd.TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
SQLServer.Connection.Query(qry);
//Cập nhật thuê bao tích hợp không tròn tháng
//qry = $@"UPDATE HD_TSL SET TONG=PAYTV_FEE+SUB_FEE,GOICUOCID=0 WHERE GOICUOCID>0 AND (PAYTV_FEE+SUB_FEE)<TONG AND (NGAY_KHOA is not null or NGAY_MO is not null or NGAY_KT is not null) AND TYPE_BILL=8 AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
SQLServer.Connection.Query(qry);
//Cập nhật vat và tổng cộng
qry = $@"UPDATE HD_TSL SET VAT=ROUND(TONG*0.1,0) WHERE TYPE_BILL={TYPE_BILL} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}';
UPDATE HD_TSL SET TONGCONG=TONG+VAT WHERE TYPE_BILL={TYPE_BILL} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}';";
SQLServer.Connection.Query(qry);
return(Json(new { success = $"{Common.Objects.TYPE_HD.HD_MYTV} - Cập nhật tích hợp thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally
{
SQLServer.Close();
}
}
public JsonResult UpdateStatus(Guid id, int op = 2, string url = null)
{
try
{
if (!Authentication.Auth.isAuth)
{
return(Json(new { success = "Ok!" }, JsonRequestBehavior.AllowGet));
}
var SQLServer = new TM.Connection.SQLServer();
var qry = $"UPDATE NOTIFICATION SET FLAG={op} WHERE ID='{id}'";
SQLServer.Connection.Query(qry);
SQLServer.Close();
if (string.IsNullOrEmpty(url))
{
return(Json(new { success = "Cập nhật thành công!" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { url = url, success = "Cập nhật thành công!" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }//"Không tìm thấy dữ liệu, vui lòng thực hiện lại!"
finally { }
}
public JsonResult Login(AuthObj obj)
{
try
{
SQLServer = new TM.Connection.SQLServer("Portal");
var qry = "";
//var collection = HttpContext.Request.ReadFormAsync();
//string username = collection["username"].ToString();
//string password = collection["password"].ToString();
//AuthStatic
var AuthStatic = Authentication.Auth.isAuthStatic(obj.username, obj.password);
if (AuthStatic != null)
{
Authentication.Auth.SetAuth(AuthStatic);
return(Json(new { success = "Đăng nhập thành công!", url = TM.Url.RedirectContinue() }, JsonRequestBehavior.AllowGet));
}
//AuthDB
qry = $"SELECT * FROM users WHERE username='******'";
var user = SQLServer.Connection.QueryFirstOrDefault <Authentication.Users>(qry);// db.users.SingleOrDefault(u => u.username == username);
//Account not Exist
if (user == null)
{
return(Json(new { danger = "Sai tên tài khoản hoặc mật khẩu!" }, JsonRequestBehavior.AllowGet));
}
//Password wrong
obj.password = TM.Encrypt.CryptoMD5TM(obj.password + user.salt);
if (user.password != obj.password)
{
return(Json(new { danger = "Sai tên tài khoản hoặc mật khẩu!" }, JsonRequestBehavior.AllowGet));
}
//Account is locked
if (user.flag < 1)
{
return(Json(new { danger = "Tài khoản đã bị khóa. Vui lòng liên hệ admin!" }, JsonRequestBehavior.AllowGet));
}
//Update last login
user.last_login = DateTime.Now;
SQLServer.Connection.Update(user);
//Set Auth Account
Authentication.Auth.SetAuth(user);
//return Redirect(TM.Url.RedirectContinue());
}
catch (Exception ex)
{
return(Json(new { danger = "Đăng nhập không thành công, vui lòng liên hệ admin!" }, JsonRequestBehavior.AllowGet));
}
finally { SQLServer.Close(); }
return(Json(new { success = "Đăng nhập thành công!", url = TM.Url.RedirectContinue(obj.currentUrl, true) }, JsonRequestBehavior.AllowGet));
}
public async Task <IActionResult> Delete(string id)
{
try {
var SQLServer = new TM.Connection.SQLServer();
var sql = "SELECT TOP 30 * FROM db_goitichhop ORDER BY tengoi";
//
SQLServer.Close();
return(Json(new { total = "total", data = id }));
} catch (Exception ex) {
return(Json(new { Error = ex.Message }));
}
}
public JsonResult InsertUpdate(Models.BGCUOC obj, long?id)
{
//var provider = System.Globalization.CultureInfo.InvariantCulture;
var index = 0;
var qry = "";
var msg = "Cập nhật thông tin thành công!";
var profile_ip = "profile_ip";
try
{
SQLServer = new TM.Connection.SQLServer();
//
qry = $"SELECT i.*,g.TITLE AS GROUPTITLE FROM ITEMS i,GROUPS g WHERE i.GROUPID=g.GROUPID AND i.APPKEY='{profile_ip}' AND g.APPKEY='{profile_ip}' AND i.FLAG=1 AND g.FLAG=1 ORDER BY i.TITLE";
var ProfileIPList = SQLServer.Connection.QueryFirstOrDefault <Models.GROUPS>(qry);
//
if (id == null)
{
obj.CREATEDBY = Authentication.Auth.AuthUser.username;
obj.CREATEDAT = DateTime.Now;
SQLServer.Connection.Insert(obj);
msg = "Tạo mới thông tin thành công!";
}
else
{
qry = $"SELECT * FROM BGCUOC WHERE BGCUOCID={id}";
var data = SQLServer.Connection.QueryFirstOrDefault <Models.BGCUOC>(qry);
data.TENGOI = obj.TENGOI;
data.PROFILE = obj.PROFILE;
data.PROFILEIP = getProfileIP(ProfileIPList.TITLE, data.PROFILE);
data.TOCDO = obj.TOCDO;
data.GIA = obj.GIA;
data.DICHVUVT_ID = obj.DICHVUVT_ID;
data.GOICUOCID = obj.GOICUOCID;
data.TICHHOPID = obj.TICHHOPID;
data.IS_DATA = obj.IS_DATA;
data.IS_TH = obj.IS_TH;
data.GHICHU = obj.GHICHU;
data.NGAY_BD = obj.NGAY_BD;
data.NGAY_KT = obj.NGAY_KT;
data.EXTRA_TYPE = obj.EXTRA_TYPE;
data.UPDATEDBY = Authentication.Auth.AuthUser.username;
data.UPDATEDAT = DateTime.Now;
data.FLAG = obj.FLAG;
SQLServer.Connection.Update(data);
}
var ReturnJson = Json(new { success = msg }, JsonRequestBehavior.AllowGet);
ReturnJson.MaxJsonLength = int.MaxValue;
return(ReturnJson);
}
catch (Exception ex) { return(Json(new { danger = "Lỗi hệ thống vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
}
public async Task <IActionResult> Update(int id, Billing.Core.Models.db_goitichhop data)
{
try {
var SQLServer = new TM.Connection.SQLServer();
var sql = "SELECT TOP 30 * FROM db_goitichhop ORDER BY tengoi";
//
SQLServer.Close();
return(Json(new { total = "total", data = data }));
} catch (Exception ex) {
return(Json(new { Error = ex.Message }));
}
}
public static void Set()
{
TM.Connection.SQLServer SQLServer = new TM.Connection.SQLServer();
try
{
var settings = SQLServer.Connection.GetAll <Models.Settings>();
TMAppContext.Http.Session.Set(SessionSettings, JsonConvert.SerializeObject(settings));
}
catch (Exception) { throw; }
finally
{
SQLServer.Close();
}
}
public async Task <IActionResult> Select(int id)
{
try {
var SQLServer = new TM.Connection.SQLServer();
var sql = $"SELECT * FROM db_goitichhop WHERE id_goitichhop={id}";
var data = await SQLServer.Connection.QueryAsync <Billing.Core.Models.db_goitichhop>(sql);
var total = data.Count();
//
SQLServer.Close();
return(Json(new { total = total, data = data }));
} catch (Exception ex) {
return(Json(new { Error = ex.Message }));
}
}
public ActionResult InsertUpdate(Models.ITEMS obj, long?id) //string sort, string order, string search, int offset = 0, int limit = 10, int flag = 1
{
//var provider = System.Globalization.CultureInfo.InvariantCulture;
var index = 0;
var qry = "";
var msg = "Cập nhật thông tin thành công!";
try
{
SQLServer = new TM.Connection.SQLServer();
//
if (id == null)
{
obj.CREATEDBY = Authentication.Auth.AuthUser.username;
obj.CREATEDAT = DateTime.Now;
SQLServer.Connection.Insert(obj);
msg = "Cập nhật thông tin thành công!";
}
else
{
qry = $"SELECT * FROM ITEMS WHERE ITEMID={id}";
var data = SQLServer.Connection.QueryFirstOrDefault <Models.ITEMS>(qry);
data.APPKEY = obj.APPKEY;
data.GROUPID = obj.GROUPID;
data.TITLE = obj.TITLE;
data.DESCRIPTION = obj.DESCRIPTION;
data.CONTENTS = obj.CONTENTS;
data.AMOUNT_START = obj.AMOUNT_START;
data.AMOUNT_END = obj.AMOUNT_END;
data.IMAGES = obj.IMAGES;
data.ATTACH = obj.ATTACH;
data.AUTHOR = obj.AUTHOR;
data.QUANTITY = obj.QUANTITY;
data.POSITION = obj.POSITION;
data.ORDERS = obj.ORDERS;
data.FLAG = obj.FLAG;
data.UPDATEDBY = Authentication.Auth.AuthUser.username;
data.UPDATEDAT = DateTime.Now;
SQLServer.Connection.Update(data);
}
var ReturnJson = Json(new { success = msg }, JsonRequestBehavior.AllowGet);
ReturnJson.MaxJsonLength = int.MaxValue;
return(ReturnJson);
}
catch (Exception) { return(Json(new { danger = "Lỗi hệ thống vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
}
public JsonResult ChangePassword(Guid id, string password)
{
try
{
SQLServer = new TM.Connection.SQLServer("Portal");
var qry = $"SELECT * FROM users WHERE id='{id.ToString()}'";
var user = SQLServer.Connection.QueryFirstOrDefault <Authentication.Users>(qry);
user.password = TM.Encrypt.CryptoMD5TM(password + user.salt);
SQLServer.Connection.Update(user);
}
catch (Exception)
{
return(Json(new { danger = "Cập nhật mật khẩu không thành công, vui lòng thử lại sau!" }, JsonRequestBehavior.AllowGet));
}
finally { SQLServer.Close(); }
return(Json(new { success = "Cập nhật mật khẩu thành công!" }, JsonRequestBehavior.AllowGet));
}
public JsonResult Get(long id)
{
var index = 0;
var qry = "";
try
{
SQLServer = new TM.Connection.SQLServer();
qry = $"SELECT * FROM BGCUOC WHERE BGCUOCID={id}";
var data = SQLServer.Connection.QueryFirstOrDefault <Models.BGCUOC>(qry);
var ReturnJson = Json(new { data = data, success = "Lấy dữ liệu thành công!" }, JsonRequestBehavior.AllowGet);
ReturnJson.MaxJsonLength = int.MaxValue;
return(ReturnJson);
}
catch (Exception) { return(Json(new { danger = "Không tìm thấy dữ liệu, vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
}
public ActionResult GetAppKey()
{
var index = 0;
var qry = "";
try
{
SQLServer = new TM.Connection.SQLServer();
qry = $"SELECT DISTINCT APPKEY FROM GROUPS";
var data = SQLServer.Connection.Query <Models.GROUPS>(qry);
var ReturnJson = Json(new { data = data, success = "Lấy dữ liệu thành công!" }, JsonRequestBehavior.AllowGet);
ReturnJson.MaxJsonLength = int.MaxValue;
return(ReturnJson);
}
catch (Exception) { return(Json(new { danger = "Không tìm thấy dữ liệu, vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
}
public ActionResult InsertUpdate(Models.GROUPS obj, long?id)
{
//var provider = System.Globalization.CultureInfo.InvariantCulture;
var index = 0;
var qry = "";
var msg = "Cập nhật thông tin thành công!";
try
{
SQLServer = new TM.Connection.SQLServer();
//
if (id == null)
{
obj.LEVELS = 0;
obj.CREATEDBY = Authentication.Auth.AuthUser.username;
obj.CREATEDAT = DateTime.Now;
SQLServer.Connection.Insert(obj);
msg = "Tạo mới thông tin thành công!";
}
else
{
qry = $"SELECT * FROM GROUPS WHERE GROUPID={id}";
var data = SQLServer.Connection.QueryFirstOrDefault <Models.GROUPS>(qry);
data.APPKEY = obj.APPKEY;
data.TITLE = obj.TITLE;
data.DESCRIPTION = obj.DESCRIPTION;
data.PARENT_ID = obj.PARENT_ID;
data.PARENTS_ID = obj.PARENTS_ID;
data.IMAGES = obj.IMAGES;
data.ICONS = obj.ICONS;
data.QUANTITY = obj.QUANTITY;
data.POSITION = obj.POSITION;
data.ORDERS = obj.ORDERS;
data.FLAG = obj.FLAG;
data.UPDATEDBY = Authentication.Auth.AuthUser.username;
data.UPDATEDAT = DateTime.Now;
SQLServer.Connection.Update(data);
}
var ReturnJson = Json(new { success = msg }, JsonRequestBehavior.AllowGet);
ReturnJson.MaxJsonLength = int.MaxValue;
return(ReturnJson);
}
catch (Exception ex) { return(Json(new { danger = "Lỗi hệ thống vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
}
public static List <Models.Settings> Get()
{
TM.Connection.SQLServer SQLServer = new TM.Connection.SQLServer();
try
{
if (TMAppContext.Http.Session.Get <string>(SessionSettings) == null)
{
var settings = SQLServer.Connection.GetAll <Models.Settings>();
TMAppContext.Http.Session.Set(SessionSettings, JsonConvert.SerializeObject(settings));
}
return(JsonConvert.DeserializeObject <List <Models.Settings> >(TMAppContext.Http.Session.Get <string>(SessionSettings)));
}
catch (Exception) { throw; }
finally
{
SQLServer.Close();
}
}
public async Task <JsonResult> update_flag(string uid)
{
try
{
SQLServer = new TM.Connection.SQLServer();
var data = SQLServer.Connection.Query <Models.DICHVU_VT_BKN>($"SELECT * FROM DICHVU_VT_BKN WHERE IN({uid})").ToList();
var FLAG = 0;
foreach (var i in data)
{
i.FLAG = FLAG = i.FLAG == 1 ? 0 : 1;
}
await SQLServer.Connection.UpdateAsync(data);
return(Json(new { success = (FLAG == 0 ? TM.Common.Language.msgDeleteSucsess : TM.Common.Language.msgRecoverSucsess) }, JsonRequestBehavior.AllowGet));
}
catch (Exception) { return(Json(new { danger = TM.Common.Language.msgError }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
}
public JsonResult BackupDatabase()
{
var SqlServer = new TM.Connection.SQLServer();
try
{
var path = TM.Common.Directories.DBBak;
TM.IO.FileDirectory.CreateDirectory(path, false);
//TM.IO.FileDirectory.SetAccessRule(path, false);
var backup = new TM.SQLServer.Backup(path);
backup.BackingAll(SqlServer.Connection);
//InsertDirectory(TM.Common.Directories.DBBak + "test\\abc");
var a = InsertDirectoriesFiles(path);
return(Json(new { success = "Backup Database thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }
finally { SqlServer.Close(); }
}
public JsonResult getNotification(int offset = 0, int limit = 10)
{
try
{
if (!Authentication.Auth.isAuth)
{
return(Json(new { success = "Ok!" }, JsonRequestBehavior.AllowGet));
}
var SQLServer = new TM.Connection.SQLServer();
var qry = $"SELECT * FROM NOTIFICATION WHERE DESTINATION='{Authentication.Auth.AuthUser.username}' AND FLAG>0 ORDER BY FLAG ASC,CREATEDAT DESC";
var data = SQLServer.Connection.Query <Billing.Models.NOTIFICATION>(qry);
var total = data.Where(d => d.FLAG == 1).Count();
data = data.Skip(offset).Take(limit).ToList();
SQLServer.Close();
return(Json(new { data = data, total = total }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }//"Không tìm thấy dữ liệu, vui lòng thực hiện lại!"
finally { }
}
public JsonResult UpdateUseDay(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var TYPE_BILL = "4";
try
{
//Cập nhật ngày sử dụng
var qry = $"UPDATE tsl SET tsl.TT_THANG=2,tsl.NGAY_TB={obj.day_in_month}-DAY(NGAY_SD) FROM HD_TSL tsl WHERE tsl.TYPE_BILL={TYPE_BILL} AND FORMAT(tsl.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND FORMAT(tsl.NGAY_SD,'MM/yyyy')='{obj.month_year_time}'";
SQLServer.Connection.Query(qry);
return(Json(new { success = $"HD_TSL - Cập nhật ngày sử dụng!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally
{
SQLServer.Close();
}
}
public JsonResult UpdateTTHD(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1");
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var TYPE_BILL = "4";
var TTHD_ID_HUY = "10,12,13,14,16,17";
try
{
//Cập nhật tổng
var qry = $"select a.*,b.TTHD_ID from DB_THUEBAO_BKN a,HD_THUEBAO_BKN b,LOAIHINH_TB_BKN lhtb where a.MA_TB=b.MA_TB and a.LOAIHINHTB_ID=lhtb.LOAIHINHTB_ID and lhtb.DICHVUVT_ID=4";
var dbpttb = Oracle.Connection.Query <Models.TTHD_TSL>(qry);
qry = $"SELECT * FROM HD_TSL WHERE TYPE_BILL={TYPE_BILL} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
var data = SQLServer.Connection.Query <Models.HD_TSL>(qry);
foreach (var i in data)
{
var _tmp = dbpttb.FirstOrDefault(d => d.MA_TB == i.MA_TB);
if (_tmp != null)
{
i.TTHD_ID = _tmp.TTHD_ID;
}
}
SQLServer.Connection.Update(data);
//
qry = $"UPDATE HD_TSL SET TYPE_BILL=-1 WHERE TYPE_BILL={TYPE_BILL} AND (TTHD_ID IN({TTHD_ID_HUY}) OR TTHD_ID IS NULL) AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
SQLServer.Connection.Query(qry);
return(Json(new { success = $"HD_TSL - Cập nhật trạng thái hợp đồng thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally
{
SQLServer.Close();
Oracle.Close();
}
}
public ActionResult Delete(string id)
{
var index = 0;
var qry = "";
var msg = "Xóa bản ghi thành công!";
try
{
SQLServer = new TM.Connection.SQLServer();
var _id = id.Trim(',');
qry = $"SELECT * FROM GROUPS WHERE GROUPID IN({_id})";
var data = SQLServer.Connection.QueryFirstOrDefault <Models.GROUPS>(qry);
if (data == null)
{
return(Json(new { danger = "Không tìm thấy dữ liệu!" }));
}
if (data.FLAG == 0)
{
qry = $"UPDATE GROUPS SET FLAG=1 WHERE GROUPID IN({_id})";
msg = "Khôi phục bản ghi thành công!";
}
else
{
qry = $"UPDATE GROUPS SET FLAG=0,DELETEDBY='{Authentication.Auth.AuthUser.username}',DELETEDAT=GETDATE() WHERE GROUPID IN({_id})";
}
SQLServer.Connection.Query(qry);
var ReturnJson = Json(new { success = msg }, JsonRequestBehavior.AllowGet);
ReturnJson.MaxJsonLength = int.MaxValue;
return(ReturnJson);
}
catch (Exception) { return(Json(new { danger = "Lỗi hệ thống vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
}
public ActionResult Select(objBST obj)//string sort, string order, string search, int offset = 0, int limit = 10, int flag = 1
{
var index = 0;
var qry = "";
var cdt = "";
try
{
SQLServer = new TM.Connection.SQLServer();
//
qry = $"SELECT * FROM GROUPS WHERE FLAG={obj.flag}";
//Get data for Search
if (!string.IsNullOrEmpty(obj.search))
{
cdt += $"(TITLE LIKE '%{obj.search}%') AND ";
}
if (!string.IsNullOrEmpty(obj.appKey) && obj.appKey != "0")
{
cdt += $"(APPKEY='{obj.appKey}') AND ";
}
if (!string.IsNullOrEmpty(cdt))
{
qry += $" AND {cdt.Substring(0, cdt.Length - 5)}";
}
//export
if (obj.export == 1)
{
//var startDate = DateTime.ParseExact($"{obj.startDate}", "dd/MM/yyyy HH:mm", provider);
//var endDate = DateTime.ParseExact($"{obj.endDate}", "dd/MM/yyyy HH:mm", provider);
//qry += $" AND tb.FLAG=2 AND tb.UPDATEDAT>=CAST('{startDate.ToString("yyyy-MM-dd")}' as datetime) AND tb.UPDATEDAT<=CAST('{endDate.ToString("yyyy-MM-dd")}' as datetime) ORDER BY tb.MA_DVI,tb.UPDATEDAT";
//var export = SQLServer.Connection.Query<Portal.Areas.ND49.Models.ND49Export>(qry);
//qry = "SELECT * FROM users";
//var user = SQLServer.Connection.Query<Authentication.user>(qry);
//foreach (var i in export)
//{
// var tmp = user.FirstOrDefault(d => d.username == i.NVQL);
// i.TEN_NVQL = tmp != null ? tmp.full_name : null;
//}
//var rsJson = Json(new { data = export, SHA = Guid.NewGuid() }, JsonRequestBehavior.AllowGet);
//rsJson.MaxJsonLength = int.MaxValue;
//return rsJson;
}
//
var data = SQLServer.Connection.Query <Models.GROUPS>(qry);
////Get data for Search
//if (!string.IsNullOrEmpty(obj.search))
// data = data.Where(d =>
// d.MA_KH.Contains(obj.search) ||
// d.MA_TT_HNI.Contains(obj.search) ||
// d.ACCOUNT.Contains(obj.search) ||
// d.MA_TB.Contains(obj.search) ||
// d.TEN_TT.Contains(obj.search) ||
// d.DIACHI_TT.Contains(obj.search));
//
if (data.ToList().Count < 1)
{
return(Json(new { total = 0, rows = data }, JsonRequestBehavior.AllowGet));
}
//Get total item
var total = data.Count();
//Sort And Orders
if (!string.IsNullOrEmpty(obj.sort))
{
if (obj.sort.ToUpper() == "TITLE" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.TITLE);
}
else if (obj.sort.ToUpper() == "TITLE" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.TITLE);
}
else
{
data = data.OrderBy(m => m.ORDERS).ThenBy(m => m.APPKEY).ThenBy(m => m.TITLE);
}
}
else
{
data = data.OrderBy(m => m.ORDERS).ThenBy(m => m.APPKEY).ThenBy(m => m.TITLE);
}
//Page Site
var rs = data.Skip(obj.offset).Take(obj.limit).ToList();
var ReturnJson = Json(new { total = total, rows = rs }, JsonRequestBehavior.AllowGet);
ReturnJson.MaxJsonLength = int.MaxValue;
return(ReturnJson);
}
catch (Exception) { return(Json(new { danger = "Không tìm thấy dữ liệu, vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
//return Json(new { success = "Cập nhật thành công!" }, JsonRequestBehavior.AllowGet);
}
public JsonResult ImportTextData(string txtDataVal, int actionType)
{
var SQLServer = new TM.Connection.SQLServer();
long index = 0;
var provider = System.Globalization.CultureInfo.InvariantCulture;
var msg = "Cập nhật thành công";
var profile_ip = "profile_ip";
try
{
//
if (string.IsNullOrEmpty(txtDataVal))
{
return(Json(new { danger = "Vui lòng nhập giá trị!" }, JsonRequestBehavior.AllowGet));
}
//
//var qry = $"SELECT i.*,g.TITLE AS GROUPTITLE FROM ITEMS i,GROUPS g WHERE i.GROUPID=g.GROUPID AND i.APPKEY='{profile_ip}' AND g.APPKEY='{profile_ip}' AND i.FLAG=1 AND g.FLAG=1 ORDER BY i.TITLE";
var qry = $"SELECT * FROM GROUPS WHERE APPKEY='{profile_ip}' AND FLAG=1 ORDER BY TITLE";
var ProfileIPList = SQLServer.Connection.QueryFirstOrDefault <Models.GROUPS>(qry);
var dataRow = txtDataVal.Split('\n');
//Remove old
if (actionType == 2)
{
qry = $"DELETE BGCUOC";
SQLServer.Connection.Query(qry);
}
index = 0;
//
var dataList = new List <Models.BGCUOC>();
foreach (var i in dataRow)
{
index++;
var tmp = i.Trim('\r').Split('\t');
if (index == 1)
{
continue;
}
if (tmp.Length > 13)
{
var _data = new Models.BGCUOC();
_data.TENGOI = string.IsNullOrEmpty(tmp[0]) ? null : tmp[0].Trim();
_data.PROFILE = string.IsNullOrEmpty(tmp[1]) ? null : tmp[1].Trim();
_data.PROFILEIP = getProfileIP(ProfileIPList.TITLE, _data.PROFILE);
_data.TOCDO = string.IsNullOrEmpty(tmp[2]) ? 0 : int.Parse(tmp[2].Trim());
_data.GIA = string.IsNullOrEmpty(tmp[3]) ? 0 : decimal.Parse(tmp[3].Trim());
_data.DICHVUVT_ID = string.IsNullOrEmpty(tmp[4]) ? 0 : int.Parse(tmp[4].Trim());
_data.GOICUOCID = string.IsNullOrEmpty(tmp[5]) ? 0 : int.Parse(tmp[5].Trim());
_data.TICHHOPID = string.IsNullOrEmpty(tmp[6]) ? 0 : int.Parse(tmp[6].Trim());
_data.IS_DATA = string.IsNullOrEmpty(tmp[7]) ? 0 : int.Parse(tmp[7].Trim());
_data.IS_TH = string.IsNullOrEmpty(tmp[8]) ? 0 : int.Parse(tmp[8].Trim());
if (!string.IsNullOrEmpty(tmp[9]))
{
_data.NGAY_BD = DateTime.ParseExact(tmp[9], "dd/MM/yyyy", provider);
}
if (!string.IsNullOrEmpty(tmp[10]))
{
_data.NGAY_KT = DateTime.ParseExact(tmp[10], "dd/MM/yyyy", provider);
}
_data.EXTRA_TYPE = string.IsNullOrEmpty(tmp[11]) ? 0 : int.Parse(tmp[11].Trim());
_data.FLAG = string.IsNullOrEmpty(tmp[12]) ? 0 : int.Parse(tmp[12].Trim());
_data.GHICHU = string.IsNullOrEmpty(tmp[13]) ? null : tmp[13].Trim();
_data.CREATEDBY = Authentication.Auth.AuthUser.username;
_data.CREATEDAT = DateTime.Now;
//PROFILEIP
//var PROFILEIP = ProfileIPList.TITLE.Trim(',').Split(',');
//_data.PROFILEIP = ",";
//if (!string.IsNullOrEmpty(tmp[1]))
// foreach (var item in PROFILEIP)
// {
// var PROFILEList = tmp[1].Split('_');
// _data.PROFILEIP += PROFILEList.Length > 1 ? $"{PROFILEList[0]}{item}_{PROFILEList[1]}," : "";
// }
////_data.PROFILEIP = string.IsNullOrEmpty(tmp[0]) ? null : tmp[0].Trim();
dataList.Add(_data);
}
}
//
SQLServer.Connection.Insert(dataList);
return(Json(new { success = $"{msg} - Count: {dataList.Count}" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
}
public JsonResult UpdateContactData(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1");
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var TYPE_BILL = "4";
try
{
//Get DB PTTB
var qry = $"SELECT kh.KHACHHANG_ID,kh.MA_KH,a.THANHTOAN_ID,tt.MA_TT AS MA_TT,tt.MAPHO_ID,b.DICHVUVT_ID,tt.TEN_TT,tt.DIACHI_TT,tt.DIENTHOAI_TT AS DIENTHOAI,tt.MST AS MS_THUE,tt.MA_TUYENTHU AS MA_TUYEN,tt.DONVIQL_ID,a.DOITUONG_ID AS MA_DT,tttb.TRANGTHAITB_ID AS TH_SD,a.MA_TB,a.MA_TB AS MA_TB,a.DOITUONG_ID,a.GHICHU,a.LOAIHINHTB_ID,a.TRANGTHAITB_ID,a.TBDAYCHUNG_ID,a.NGAY_TRANGTHAITB AS NGAY_TTTB,a.NGAY_SUDUNG AS NGAY_SD,a.NGAY_CN,a.NGAY_HT,a.NGAY_CAT,qh.MA_QUANHUYEN AS MA_DVI,b.MA_LHTB FROM DB_THUEBAO_BKN a,DB_THANHTOAN_BKN tt,DB_KHACHHANG_BKN kh,LOAIHINH_TB_BKN b,TRANGTHAI_TB_BKN tttb, MA_PHO_BKN mp,PHUONG_XA_BKN px,QUAN_HUYEN_BKN qh WHERE a.THANHTOAN_ID=tt.THANHTOAN_ID AND tt.KHACHHANG_ID=kh.KHACHHANG_ID AND a.LOAIHINHTB_ID=b.LOAIHINHTB_ID AND tt.MAPHO_ID=mp.MAPHO_ID AND mp.PHUONGXA_ID=px.PHUONGXA_ID AND px.QUANHUYEN_ID=qh.QUANHUYEN_ID AND b.DICHVUVT_ID={TYPE_BILL} AND a.TRANGTHAITB_ID=tttb.TRANGTHAITB_ID ORDER BY qh.MA_QUANHUYEN,a.NGAY_CN,a.MA_TB";
var dbpttb = Oracle.Connection.Query <Models.DANH_BA_TSL>(qry);
//Get data DB_DUONGTHU_BKN
qry = $"SELECT * FROM DB_DUONGTHU_BKN";
var dbdt = SQLServer.Connection.Query <Models.DB_DUONGTHU_BKN>(qry);
//Get data DB KH
qry = $"SELECT * FROM {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} WHERE FIX=0 AND FLAG=1";//AND TYPE_BILL IN({TYPE_BILL})
var dbkh = SQLServer.Connection.Query <Models.DB_THANHTOAN_BKN>(qry);
//Get data TSL Remove
qry = $"SELECT * FROM HD_TSL WHERE TYPE_BILL=9641 AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
var tslRemove = SQLServer.Connection.Query <Models.HD_TSL>(qry);
//
var DataInsert = new List <Models.DB_THANHTOAN_BKN>();
var DataUpdate = new List <Models.DB_THANHTOAN_BKN>();
var DataInsertHD = new List <Models.HD_TSL>();
var _dbkh_id = Guid.Empty;
//Xóa dữ liệu cũ
qry = $"DELETE HD_TSL WHERE TYPE_BILL IN({TYPE_BILL},-1) AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
SQLServer.Connection.Query(qry);
foreach (var i in dbpttb)
{
//check data TSL Remove
if (tslRemove.Any(d => d.MA_TB == i.MA_TB))
{
continue;
}
//Cập nhật hóa đơn TSL
var hdtsl = new Models.HD_TSL();
hdtsl.ID = Guid.NewGuid();
hdtsl.DBKH_ID = _dbkh_id;
hdtsl.TYPE_BILL = int.Parse(TYPE_BILL);
hdtsl.KYHOADON = obj.KYHD;
hdtsl.MAPHO_ID = i.MAPHO_ID;
hdtsl.THANHTOAN_ID = i.THANHTOAN_ID;
hdtsl.THUEBAO_ID = i.THUEBAO_ID;
hdtsl.MA_TB = i.MA_TB;
hdtsl.MA_TB = i.MA_TB;
hdtsl.DOITUONG_ID = i.DOITUONG_ID;
hdtsl.TOC_DO = "TSL";
hdtsl.GHICHU = i.GHICHU;
hdtsl.TT_THANG = 1;
hdtsl.NGAY_TB = obj.day_in_month;
hdtsl.LOAIHINHTB_ID = i.LOAIHINHTB_ID;
hdtsl.MA_LHTB = i.MA_LHTB;
hdtsl.TRANGTHAITB_ID = i.TRANGTHAITB_ID;
hdtsl.TBDAYCHUNG_ID = i.TBDAYCHUNG_ID;
hdtsl.GOICUOCID = i.GOICUOCID;
//hdtsl.ISDATMOI = 0;
//hdtsl.ISHUY = 0;
//hdtsl.ISTTT = 0;
//hdtsl.ISDATCOC = 0;
hdtsl.NGAY_TTTB = i.NGAY_TTTB;
hdtsl.NGAY_SD = i.NGAY_SD;
hdtsl.NGAY_CN = i.NGAY_CN;
hdtsl.NGAY_HT = i.NGAY_HT;
hdtsl.NGAY_CAT = i.NGAY_CAT;
DataInsertHD.Add(hdtsl);
//Cập nhật danh bạ TSL
var _tmp = dbkh.FirstOrDefault(d => d.MA_TB == i.MA_TB);
if (_tmp != null)
{
if (DataUpdate.Any(d => d.MA_TB == i.MA_TB))
{
continue;
}
if (!string.IsNullOrEmpty(i.MA_KH))
{
_tmp.MA_KH = i.MA_KH.Trim();
}
if (!string.IsNullOrEmpty(i.MA_TT))
{
_tmp.MA_TT = i.MA_TT.Trim();
}
if (!string.IsNullOrEmpty(i.TEN_TT))
{
_tmp.TEN_TT = i.TEN_TT.Trim();
}
if (!string.IsNullOrEmpty(i.DIACHI_TT))
{
_tmp.DIACHI_TT = i.DIACHI_TT.Trim();
}
if (!string.IsNullOrEmpty(i.DIENTHOAI))
{
_tmp.DIENTHOAI = i.DIENTHOAI.Trim();
}
//if (!string.IsNullOrEmpty(i.MS_THUE)) _tmp.MS_THUE = i.MS_THUE.Trim();
//_tmp.BANKNUMBER = null;
if (!string.IsNullOrEmpty(i.MA_DVI))
{
_tmp.MA_DVI = i.MA_DVI.Trim();
}
if (!string.IsNullOrEmpty(i.MA_TUYEN))
{
_tmp.MA_TUYEN = i.MA_TUYEN.Trim().ToUpper();
}
//if (!string.IsNullOrEmpty(i.MA_CBT)) _tmp.MA_CBT = i.MA_CBT.Trim();
var ma_cbt = dbdt.FirstOrDefault(d => d.MA_DT == _tmp.MA_TUYEN);
_tmp.MA_CBT = ma_cbt != null ? ma_cbt.MA_DT_GOC : null;
//if (!string.IsNullOrEmpty(i.CUSTCATE)) _tmp.CUSTCATE = i.CUSTCATE.Trim();
//_tmp.STK = null;
_tmp.DONVI_ID = i.DONVI_ID;
_tmp.KHACHHANG_ID = i.KHACHHANG_ID;
_tmp.THANHTOAN_ID = i.THANHTOAN_ID;
_tmp.MA_DT = i.MA_DT;
_tmp.TH_SD = i.TH_SD;
_tmp.ISNULL = 0;
_tmp.ISNULLMT = 0;
_tmp.FIX = 0;
_tmp.FLAG = 1;
//MA_TB Json
//var MA_TB_json = Newtonsoft.Json.JsonConvert.DeserializeObject<Models.MA_TB_JSON>(_tmp.MA_TB);
//MA_TB_json.TSL = i.MA_TB;
//_tmp.MA_TB = Newtonsoft.Json.JsonConvert.SerializeObject(MA_TB_json);
_tmp.MA_TB = i.MA_TB;
//
_dbkh_id = _tmp.ID;
DataUpdate.Add(_tmp);
//SQLServer.Connection.Update(_tmp);
}
else
{
if (DataInsert.Any(d => d.MA_TB == i.MA_TB))
{
continue;
}
var _d = new Models.DB_THANHTOAN_BKN();
_d.ID = _dbkh_id = Guid.NewGuid();
_d.TYPE_BILL = int.Parse(TYPE_BILL);
_d.MA_TB = _d.MA_TB = i.MA_TB;
if (!string.IsNullOrEmpty(i.MA_KH))
{
_d.MA_KH = i.MA_KH.Trim();
}
if (!string.IsNullOrEmpty(i.MA_TT))
{
_d.MA_TT = i.MA_TT.Trim();
}
if (!string.IsNullOrEmpty(i.TEN_TT))
{
_d.TEN_TT = i.TEN_TT.Trim();
}
if (!string.IsNullOrEmpty(i.TEN_TT))
{
_d.DIACHI_TT = i.TEN_TT.Trim();
}
if (!string.IsNullOrEmpty(i.DIENTHOAI))
{
_d.DIENTHOAI = i.DIENTHOAI.Trim();
}
//if (!string.IsNullOrEmpty(i.MA_ST)) _d.MS_THUE = i.MA_ST.Trim();
//_tmp.BANKNUMBER = null;
if (!string.IsNullOrEmpty(i.MA_DVI))
{
_d.MA_DVI = i.MA_DVI.Trim();
}
if (!string.IsNullOrEmpty(i.MA_TUYEN))
{
_d.MA_TUYEN = i.MA_TUYEN.Trim().ToUpper();
}
//if (!string.IsNullOrEmpty(i.MA_CBT)) _d.MA_CBT = i.MA_CBT.Trim();
var ma_cbt = dbdt.FirstOrDefault(d => d.MA_DT == _d.MA_TUYEN);
_d.MA_CBT = ma_cbt != null ? ma_cbt.MA_DT_GOC : null;
//if (!string.IsNullOrEmpty(i.CUSTCATE)) _d.CUSTCATE = i.CUSTCATE.Trim();
//_tmp.STK = null;
_d.DONVI_ID = i.DONVI_ID;
_d.KHACHHANG_ID = i.KHACHHANG_ID;
_d.THANHTOAN_ID = i.THANHTOAN_ID;
_d.MA_DT = i.MA_DT;
_d.TH_SD = i.TH_SD;
_d.ISNULL = 0;
_d.ISNULLMT = 0;
_d.FIX = 0;
_d.FLAG = 1;
//MA_TB Json
//var MA_TB_json = new Models.MA_TB_JSON();
//MA_TB_json.TSL = i.MA_TB;
//_d.MA_TB = Newtonsoft.Json.JsonConvert.SerializeObject(MA_TB_json);
_d.MA_TB = i.MA_TB;
DataInsert.Add(_d);
}
}
//
if (DataInsert.Count > 0)
{
SQLServer.Connection.Insert(DataInsert);
}
if (DataUpdate.Count > 0)
{
SQLServer.Connection.Update(DataUpdate);
}
//
if (DataInsertHD.Count > 0)
{
SQLServer.Connection.Insert(DataInsertHD);
}
//UPDATE NULL
qry = $@"UPDATE {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} SET ISNULLMT=1,MA_TUYEN=REPLACE(MA_TUYEN,'000','001') WHERE MA_TUYEN LIKE '%000' AND FIX=0 AND FLAG=1 AND TYPE_BILL IN({TYPE_BILL});
UPDATE {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} SET MA_CBT=CAST(CAST(ma_dvi as varchar)+'01' as int) WHERE ISNULLMT=1 AND FIX=0 AND FLAG=1 AND TYPE_BILL IN({TYPE_BILL});";
SQLServer.Connection.Query(qry);
//
return(Json(new { success = $"TSL - Cập nhật: {DataUpdate.Count} - Thêm mới: {DataInsert.Count}" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally
{
SQLServer.Close();
Oracle.Close();
}
}
public JsonResult UpdateContact(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
//var Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1");
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var TYPE_BILL = "1";
try
{
var qry = $"SELECT * FROM {Common.Objects.TYPE_HD.CD} WHERE KYHOADON='{obj.KYHD}'";
var data = SQLServer.Connection.Query <Models.CD>(qry);
//Get DB PTTB
qry = "SELECT a.*,a.TRANGTHAI_ AS TRANGTHAI FROM DANH_BA_CO_DINH a";
//var dbpttb = Oracle.Connection.Query<Models.DANH_BA_CO_DINH>(qry).ToList();
//
qry = $"SELECT * FROM {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} WHERE FIX=0 AND FLAG=1 AND TYPE_BILL IN({TYPE_BILL})";
var dbkh = SQLServer.Connection.Query <Models.DB_THANHTOAN_BKN>(qry);
var DataInsert = new List <Models.DB_THANHTOAN_BKN>();
var DataUpdate = new List <Models.DB_THANHTOAN_BKN>();
foreach (var i in data)
{
var _tmp = dbkh.FirstOrDefault(d => d.MA_TB == i.SO_TB);
//var pttb = dbpttb.FirstOrDefault(d => d.MA_TB.Trim() == i.SO_TB);
if (_tmp != null)
{
//if (pttb != null)
//{
// if (!string.IsNullOrEmpty(pttb.MA_KH)) _tmp.MA_KH = pttb.MA_KH.Trim();
//}
_tmp.MA_TT = i.MA_TT;
_tmp.TEN_TT = i.TEN_TT;
_tmp.DIACHI_TT = i.DIACHI_TT;
_tmp.DIENTHOAI = i.SO_TB;
_tmp.MS_THUE = i.MS_THUE;
_tmp.BANKNUMBER = i.BANKNUMBER;
_tmp.MA_DVI = i.MA_DVI;
_tmp.MA_CBT = i.MA_CBT;
_tmp.MA_TUYEN = i.MA_TUYEN;
//_tmp.CUSTCATE = i.CUSTCATE;
//_tmp.STK = i.STK;
_tmp.MA_DT = (!string.IsNullOrEmpty(i.MA_DT) ? Int32.Parse(i.MA_DT) : 1);
_tmp.TH_SD = 1;
_tmp.ISNULL = 0;
_tmp.ISNULLMT = 0;
_tmp.FIX = 0;
_tmp.FLAG = 1;
DataUpdate.Add(_tmp);
}
else
{
var _d = new Models.DB_THANHTOAN_BKN();
_d.ID = Guid.NewGuid();
_d.TYPE_BILL = i.TYPE_BILL;
_d.MA_TB = i.SO_TB;
//if (pttb != null)
//{
// if (!string.IsNullOrEmpty(pttb.MA_KH)) _d.MA_KH = pttb.MA_KH.Trim();
//}
_d.MA_TT = i.MA_TT;
_d.TEN_TT = i.TEN_TT;
_d.DIACHI_TT = i.DIACHI_TT;
_d.DIENTHOAI = i.SO_TB;
_d.MS_THUE = i.MS_THUE;
_d.BANKNUMBER = i.BANKNUMBER;
_d.MA_DVI = i.MA_DVI;
_d.MA_CBT = i.MA_CBT;
_d.MA_TUYEN = i.MA_TUYEN;
//_d.CUSTCATE = i.CUSTCATE;
//_d.STK = i.STK;
_d.MA_DT = (!string.IsNullOrEmpty(i.MA_DT) ? Int32.Parse(i.MA_DT) : 1);
_d.TH_SD = 1;
_d.ISNULL = 0;
_d.ISNULLMT = 0;
_d.FIX = 0;
_d.FLAG = 1;
DataInsert.Add(_d);
}
}
//
if (DataInsert.Count > 0)
{
SQLServer.Connection.Insert(DataInsert);
}
if (DataUpdate.Count > 0)
{
SQLServer.Connection.Update(DataUpdate);
}
//
qry = $"update db set db.DONVI_ID=dv.DONVI_ID,db.DONVI_QL_ID=dv.DONVI_QL_ID from DB_THANHTOAN_BKN db,DB_DONVI_BKN dv where dv.MA_DVI=db.MA_DVI and type_bill in({TYPE_BILL})";
SQLServer.Connection.Query(qry);
//
return(Json(new { success = $"{Common.Objects.TYPE_HD.HD_CD} - Cập nhật: {DataUpdate.Count} - Thêm mới: {DataInsert.Count}" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
}
public JsonResult Select(objBST obj)//string sort, string order, string search, int offset = 0, int limit = 10, int flag = 1
{
var index = 0;
var qry = "";
var cdt = "";
try
{
SQLServer = new TM.Connection.SQLServer();
obj.extension = "'.dbf','.txt','.xls','.xlsx','.zip','.xml'";
//Select
qry = $"SELECT * FROM FILE_MANAGER WHERE FLAG={obj.flag}";
//Extension
if (!Authentication.Auth.inRoles(new[] { Authentication.Roles.admin, Authentication.Roles.superadmin }))
{
if (!string.IsNullOrEmpty(obj.extension))
{
cdt += $"(EXTENSION IN({obj.extension}) OR TYPE='{TM.Common.Objects.FileManager.directory}') AND ";
}
}
//Sub Dir
if (!string.IsNullOrEmpty(obj.subDir))
{
cdt += $"SUBDIRECTORY IN('{obj.subDir}') AND ";
}
else
{
cdt += $"LEVELS=0 AND ";
}
//Get data for Search
//if (!String.IsNullOrEmpty(obj.search) && obj.search.isNumber())
// cdt += $"(GOICUOCID={obj.search}) AND ";
if (!string.IsNullOrEmpty(obj.search))
{
cdt += $"(PARENT LIKE '%{obj.search}%' OR NAME LIKE '%{obj.search}%' OR SUBDIRECTORY LIKE '%,{obj.search},%' OR EXTENSION LIKE '%,{obj.search},%') AND ";
}
//
if (!string.IsNullOrEmpty(cdt))
{
qry += $" AND {cdt.Substring(0, cdt.Length - 5)}";
}
//export
if (obj.export == 1)
{
//var startDate = DateTime.ParseExact($"{obj.startDate}", "dd/MM/yyyy HH:mm", provider);
//var endDate = DateTime.ParseExact($"{obj.endDate}", "dd/MM/yyyy HH:mm", provider);
//qry += $" AND tb.FLAG=2 AND tb.UPDATEDAT>=CAST('{startDate.ToString("yyyy-MM-dd")}' as datetime) AND tb.UPDATEDAT<=CAST('{endDate.ToString("yyyy-MM-dd")}' as datetime) ORDER BY tb.MA_DVI,tb.UPDATEDAT";
//var export = SQLServer.Connection.Query<Portal.Areas.ND49.Models.ND49Export>(qry);
//qry = "SELECT * FROM users";
//var user = SQLServer.Connection.Query<Authentication.user>(qry);
//foreach (var i in export)
//{
// var tmp = user.FirstOrDefault(d => d.username == i.NVQL);
// i.TEN_NVQL = tmp != null ? tmp.full_name : null;
//}
//var rsJson = Json(new { data = export, SHA = Guid.NewGuid() }, JsonRequestBehavior.AllowGet);
//rsJson.MaxJsonLength = int.MaxValue;
//return rsJson;
}
//
var data = SQLServer.Connection.Query <Models.FILE_MANAGER>(qry);
if (data.ToList().Count < 1)
{
return(Json(new { total = 0, rows = data }, JsonRequestBehavior.AllowGet));
}
//Get total item
var total = data.Count();
//Sort And Orders
if (!string.IsNullOrEmpty(obj.sort))
{
if (obj.sort.ToUpper() == "NAME" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.NAME);
}
else if (obj.sort.ToUpper() == "NAME" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.NAME);
}
else if (obj.sort.ToUpper() == "ATTRIBUTES" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.ATTRIBUTES);
}
else if (obj.sort.ToUpper() == "ATTRIBUTES" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.ATTRIBUTES);
}
else if (obj.sort.ToUpper() == "LENGTH" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.LENGTH);
}
else if (obj.sort.ToUpper() == "LENGTH" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.LENGTH);
}
else if (obj.sort.ToUpper() == "CREATIONTIME" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.CREATIONTIME);
}
else if (obj.sort.ToUpper() == "CREATIONTIME" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.CREATIONTIME);
}
else if (obj.sort.ToUpper() == "LASTWRITETIME" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.LASTWRITETIME);
}
else if (obj.sort.ToUpper() == "LASTWRITETIME" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.LASTWRITETIME);
}
else
{
data = data.OrderByDescending(m => m.CREATIONTIME).ThenBy(m => m.NAME);
}
}
else
{
data = data.OrderByDescending(m => m.CREATIONTIME).ThenBy(m => m.NAME);
}
//Page Site
var rs = data.Skip(obj.offset).Take(obj.limit).ToList();
var ReturnJson = Json(new { total = total, rows = rs }, JsonRequestBehavior.AllowGet);
ReturnJson.MaxJsonLength = int.MaxValue;
return(ReturnJson);
}
catch (Exception) { return(Json(new { danger = "Không tìm thấy dữ liệu, vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
//return Json(new { success = "Cập nhật thành công!" }, JsonRequestBehavior.AllowGet);
}