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 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 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 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 ActionResult Update() { try { SQLServer = new TM.Connection.SQLServer(); Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1"); var qry = "SELECT a.*,a.VI_TRI AS ORDERS FROM DICHVU_VT_BKN a"; var hni = Oracle.Connection.Query <Models.DICHVU_VT_BKN>(qry).ToList(); qry = "SELECT * FROM DICHVU_VT_BKN"; var data = SQLServer.Connection.Query <Models.DICHVU_VT_BKN>(qry).ToList(); // if (hni.Count < 0) { this.warning("Hệ thống HNI không có dữ liệu!"); return(RedirectToAction("Index")); } // if (data.Count < 1) { SQLServer.Connection.Insert(hni); return(RedirectToAction("Index")); } // var Update_Upload = new[] { 6, 8, 9 }; hni.AddRange(DichVuAddition()); // var insert = hni; foreach (var i in data) { var tmp = hni.FirstOrDefault(d => d.DICHVUVT_ID == i.DICHVUVT_ID); if (tmp != null) { i.MA_DVVT = tmp.MA_DVVT; i.TEN_DVVT = tmp.TEN_DVVT; i.GHICHU = tmp.GHICHU; i.TINHTP_ID = tmp.TINHTP_ID; i.LOAI_HD = tmp.LOAI_HD; i.MENU = tmp.MENU; i.QUYCHIEU = tmp.QUYCHIEU; i.ORDERS = tmp.ORDERS; i.UPDATEDBY = "Admin"; i.UPDATEDAT = DateTime.Now; i.TT_SUDUNG = tmp.TT_SUDUNG; i.TT_UPLOAD = tmp.TT_UPLOAD; if (i.TT_UPLOAD == 0 && Update_Upload.Contains(i.DICHVUVT_ID)) { i.TT_UPLOAD = 1; } i.FLAG = 1; insert.Remove(tmp); } } foreach (var i in insert) { i.CREATEDBY = "Admin"; i.CREATEDAT = DateTime.Now; i.UPDATEDBY = i.CREATEDBY; i.UPDATEDAT = i.CREATEDAT; if (i.TT_UPLOAD == 0 && Update_Upload.Contains(i.DICHVUVT_ID)) { i.TT_UPLOAD = 1; } i.FLAG = 1; } SQLServer.Connection.Update(data); if (insert.Count > 0) { SQLServer.Connection.Insert(insert); } this.success("Cập nhật dịch vụ viễn thông thành công!"); qry = "UPDATE DICHVU_VT_BKN SET ORDERS=999 WHERE orders IS NULL"; SQLServer.Connection.Query(qry); } catch (Exception ex) { this.danger(ex.Message); } finally { SQLServer.Close(); Oracle.Close(); } return(RedirectToAction("Index")); }