public JsonResult XuLyKhuyenMai(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 = "";
//PERCENT
qry = $"UPDATE hd SET hd.TONG=hd.TONG*((100-dc.VALUE)/100) FROM HD_TSL hd INNER JOIN DISCOUNT dc ON hd.MA_TB=dc.MA_TB WHERE hd.TYPE_BILL={TYPE_BILL} AND FORMAT(hd.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND FORMAT(dc.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND dc.FLAG=1 AND dc.TYPEID={(int)Common.Objects.TYPE_DISCOUNT.PERCENT} AND ((hd.TIME_BILL>=dc.NGAY_BD AND dc.NGAY_KT IS NULL) OR (hd.TIME_BILL BETWEEN dc.NGAY_BD AND dc.NGAY_KT));";
SQLServer.Connection.Query(qry);
//MONEY
qry = $"UPDATE hd SET hd.TONG=hd.TONG-dc.VALUE FROM HD_TSL hd INNER JOIN DISCOUNT dc ON hd.MA_TB=dc.MA_TB WHERE hd.TYPE_BILL={TYPE_BILL} AND FORMAT(hd.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND FORMAT(dc.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND dc.FLAG=1 AND dc.TYPEID={(int)Common.Objects.TYPE_DISCOUNT.MONEY} AND ((hd.TIME_BILL>=dc.NGAY_BD AND dc.NGAY_KT IS NULL) OR (hd.TIME_BILL BETWEEN dc.NGAY_BD AND dc.NGAY_KT));";
SQLServer.Connection.Query(qry);
//FIX
qry = $"UPDATE hd SET hd.TONG=dc.VALUE FROM HD_TSL hd INNER JOIN DISCOUNT dc ON hd.MA_TB=dc.MA_TB WHERE hd.TYPE_BILL={TYPE_BILL} AND FORMAT(hd.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND FORMAT(dc.TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND dc.FLAG=1 AND dc.TYPEID={(int)Common.Objects.TYPE_DISCOUNT.FIX} AND ((hd.TIME_BILL>=dc.NGAY_BD AND dc.NGAY_KT IS NULL) OR (hd.TIME_BILL BETWEEN dc.NGAY_BD AND dc.NGAY_KT));";
SQLServer.Connection.Query(qry);
//UPDATE VAT
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}';";
SQLServer.Connection.Query(qry);
//UPDATE TONGCONG
qry = $"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 khuyến mại thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
}
public JsonResult GeneralUpload(Common.DefaultObj obj)
{
var index = 0;
obj.DataSource = Common.Directories.HDDataSourceGeneral;
obj = getDefaultObj(obj);
string strUpload = "Tải tệp thành công!";
try
{
TM.IO.FileDirectory.CreateDirectory(obj.DataSource, false);
var uploadData = UploadBase(obj.DataSource, strUpload);
if (uploadData == (int)Common.Objects.ResultCode._extension)
{
return(Json(new { danger = "Tệp phải định dạng .dbf!" }, JsonRequestBehavior.AllowGet));
}
else if (uploadData == (int)Common.Objects.ResultCode._length)
{
return(Json(new { danger = "Chưa đủ tệp!" }, JsonRequestBehavior.AllowGet));
}
//else if (uploadData == (int)Common.Objects.ResultCode._success)
// return Json(new { success = strUpload }, JsonRequestBehavior.AllowGet);
else
{
return(Json(new { success = strUpload }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
}
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 CreateExport(Common.DefaultObj obj)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
obj.DataSource = Common.Directories.HDData;
//
try
{
var qry = $"SELECT a.*,a.data_type COLUMN_TYPE,data_length COLUMN_LENGTH,a.column_id ORDERS FROM all_tab_cols a WHERE a.owner='{OWNER}' order by a.table_name";
var data = Oracle.Connection.Query <Models.EXPORT_TABLE>(qry).ToList();
foreach (var i in data)
{
var MapDBF = MappingOracleDBF(i.COLUMN_TYPE);
i.TABLE_TYPE = (int)Common.Objects.TABLE_TYPE.EXPORT_CUSTOM;
i.COLUMN_NAME_EXPORT = i.COLUMN_NAME.Length > 10 ? i.COLUMN_NAME.Substring(0, 10).Trim('-').Trim('_') : i.COLUMN_NAME;
i.COLUMN_TYPE_EXPORT = MapDBF[0];
i.COLUMN_LENGTH_EXPORT = MapDBF[1];
i.CONDITION = "";
i.FLAG = 1;
}
return(Json(new { data = data, success = "Xử lý thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.Close(); }
}
public JsonResult UpdateDBDT(Common.DefaultObj obj)
{
var HNIVNPTBACKAN1 = new TM.Core.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 = HNIVNPTBACKAN1.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";
_Con.Connection.Query(qry);
//Insert Data
_Con.Connection.Insert(data_hni);
return(Json(new { success = $"DB_DUONGTHU_BKN - Cập nhật thành công!" }));
} catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index })); } finally { HNIVNPTBACKAN1.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 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_CD} WHERE KYHOADON='{obj.KYHD}'";
SQLServer.Connection.Query(qry);
qry = $@"INSERT INTO {Common.Objects.TYPE_HD.HD_CD}
SELECT NEWID() AS ID,KYHOADON,ID AS CD_ID,NEWID() AS DBKH_ID,TYPE_BILL,APP_ID,SO_TB,KIEU,TB_DAUCUOI,CUOC_TB,CUOC_DV,CUOC_NH,CUOC_NT,CUOC_PSTN,
CUOC_V171,CUOC_VDNK,CUOC_DD,CUOC_IDD,CUOC_V171Q,CUOC_VDNKQ,CUOC_CI,CUOC_KM,CHIET_KHAU,0 AS TONG_IN,TONG,VAT,TONGCONG,DUPE_FLAG
FROM {Common.Objects.TYPE_HD.CD} 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_CD} 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_CD} where KYHOADON='{obj.KYHD}'";
var data = SQLServer.Connection.Query <Models.HD_CD>(qry);
return(Json(new { success = $"{Common.Objects.TYPE_HD.HD_CD} - 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 UpdateTTHD(Common.DefaultObj obj)
{
var HNIVNPTBACKAN1 = new TM.Core.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 = HNIVNPTBACKAN1.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 = _Con.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;
}
}
_Con.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}'";
_Con.Connection.Query(qry);
return(Json(new { success = $"HD_TSL - Cập nhật trạng thái hợp đồng thành công!" }));
} catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index })); } finally { HNIVNPTBACKAN1.Close(); }
}
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 ExportToDBF(Common.DefaultObj obj)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
obj.DataSource = Common.Directories.HDData;
FileManagerController.InsertDirectory(obj.DataSource);
obj = getDefaultObj(obj);
FileManagerController.InsertDirectory(obj.DataSource, false);
//
var FoxPro = new TM.Connection.OleDBF(obj.DataSource);
try
{
var tmp_qry_fox = "";
var tmp_qry_sql = "";
var condition = "";
var qry = $"SELECT * FROM EXPORT_TABLE WHERE TABLE_TYPE={(int)Common.Objects.TABLE_TYPE.DBF} AND TABLE_NAME='{obj.file}' AND FLAG=1 ORDER BY ORDERS";
var EXPORT_TABLE = Oracle.Connection.Query <Models.EXPORT_TABLE>(qry).ToList();
//
if (EXPORT_TABLE.Count <= 0)
{
return(Json(new { warning = "Vui lòng cấu hình dữ liệu Export trước!" }, JsonRequestBehavior.AllowGet));
}
qry = CreateTable(obj.DataSource, obj.file, EXPORT_TABLE);
FoxPro.Connection.Query(qry);
//
var ColumnExport = new List <string>();
foreach (var i in EXPORT_TABLE)
{
ColumnExport.Add(i.COLUMN_NAME_EXPORT);
tmp_qry_fox += $"{i.COLUMN_NAME_EXPORT},";
tmp_qry_sql += $"{i.COLUMN_NAME},";
if (!string.IsNullOrEmpty(i.CONDITION))
{
condition += MappingCondition(i.COLUMN_TYPE, i.COLUMN_NAME, i.CONDITION) + " AND ";
}
}
//
qry = $"SELECT {tmp_qry_sql.Trim(',')} FROM {obj.file} {(!string.IsNullOrEmpty(condition) ? "WHERE " + condition.Substring(0, condition.Length - 5) : "")}";
var data = Oracle.Connection.Query <dynamic>(qry).ToList();
//FoxPro.Insert(data);
//
qry = $"INSERT INTO {obj.file}({tmp_qry_fox.Trim(',')}) VALUES(";
foreach (var i in data)
{
qry += "";
}
CRUDFoxPro.InsertList(FoxPro.Connection, obj.file, ColumnExport, data);
FileManagerController.InsertFile(obj.DataSource + obj.file + ".DBF", false);
return(Json(new { success = "Xử lý thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.Close(); FoxPro.Connection.Close(); }
}
public JsonResult ImportGDSExcel(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var HNIVNPTBACKAN2 = new TM.Connection.Oracle("HNIVNPTBACKAN2");
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var UploadFiles = new List <string>();
string strUpload = "Nhập file GDS thành công!";
try
{
TM.IO.FileDirectory.CreateDirectory(obj.DataSource, false);
var uploadData = UploadBase(obj.DataSource, strUpload, UploadFiles, ".xls");
if (UploadFiles.Count < 1)
{
return(Json(new { danger = "Chưa tải được tệp, Vui lòng thử lại!" }, JsonRequestBehavior.AllowGet));
}
//
var xxxx = TM.OleExcel.ToDataSet(obj.DataSource + UploadFiles[0]);
var excel = xxxx.Tables[0];
var list = new List <Models.CUOC_FIBERVNN>();
foreach (System.Data.DataRow r in excel.Rows)
{
index++;
var tmp = new Models.CUOC_FIBERVNN();
tmp.MA_MEN = r["MA_MEN"].ToString();
tmp.CUOC = r["CUOC"] != null?decimal.Parse(r["CUOC"].ToString()) : 0;
tmp.THANGNAM = obj.month_year_time;
list.Add(tmp);
}
//list.Add(new Models.GDSIMPORT { MA_MEN = r["MA_MEN"].ToString(), CUOC = (int)r["CUOC"], THANGNAM = ((DateTime)r["THANGNAM"]).ToString("MM/yyyy") });
//HNIVNPTBACKAN2.Connection.Query("DELETE CUOC_FIBERVNN WHERE THANGNAM='11/2017'");
TM.Dapper.CRUDOracle.InsertList(HNIVNPTBACKAN2.Connection, list);
//
if (uploadData == (int)Common.Objects.ResultCode._extension)
{
return(Json(new { danger = "Tệp phải định dạng .dbf!" }, JsonRequestBehavior.AllowGet));
}
else if (uploadData == (int)Common.Objects.ResultCode._length)
{
return(Json(new { danger = "Chưa đủ tệp!" }, JsonRequestBehavior.AllowGet));
}
//else if (uploadData == (int)Common.Objects.ResultCode._success)
// return Json(new { success = strUpload }, JsonRequestBehavior.AllowGet);
else
{
return(Json(new { success = strUpload }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
}
public JsonResult UpdateUseDay(Common.DefaultObj obj)
{
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}'";
_Con.Connection.Query(qry);
return(Json(new { success = $"HD_TSL - Cập nhật ngày sử dụng!" }));
} catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index })); }
}
public JsonResult GetReportDetailCustom(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var index = 0;
obj.DataSource = Common.Directories.HDData;
obj = getDefaultObj(obj);
string msg = "Lấy báo cáo thành công!";
try
{
string qry = $"SELECT * FROM ITEMS WHERE GROUPID={obj.data_id} AND FLAG=1 ORDER BY ORDERS";
var item = SQLServer.Connection.Query <Models.ITEMS>(qry);
var data = new ReportDetailCustom();
foreach (var i in item)
{
if (i.APPKEY == "header")
{
data.hd = i.DESCRIPTION;
data.hc = i.CONTENTS;
}
else if (i.APPKEY == "footer")
{
data.fd = i.DESCRIPTION;
data.fc = i.CONTENTS;
}
else if (i.APPKEY == "content")
{
if (i.QUANTITY == 0)
{
var FoxPro = new TM.Connection.OleDBF(obj.DataSource);
data.cd = FoxPro.Connection.Query(i.DESCRIPTION);
data.cc = FixContent(i.CONTENTS);
FoxPro.Close();
}
else if (i.QUANTITY == 1)
{
data.cd = SQLServer.Connection.Query(i.DESCRIPTION);
data.cc = FixContent(i.CONTENTS);
}
}
}
return(Json(new { data = data, datetime = obj.datetime, success = msg }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
}
public JsonResult RemoveExportCustom(Common.DefaultObj obj)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
obj.DataSource = Common.Directories.HDData;
obj = getDefaultObj(obj);
try
{
var qry = $@"DELETE EXPORT_TABLE WHERE APP_KEY='{obj.file}';
DELETE EXPORT_CUSTOM WHERE ID='{obj.file}';";
Oracle.Connection.Query(qry);
return(Json(new { success = "Xóa kết xuất thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.Close(); }
}
public JsonResult GeneralUpload(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
string strUpload = "Cập nhật thành công ";
var qry = "";
try
{
//
qry = "SELECT * FROM DICHVU_VT_BKN WHERE TT_SUDUNG=2";
var DICHVU_VT_BKN = SQLServer.Connection.Query <Models.DICHVU_VT_BKN>(qry);
var fileUpload = new List <string>();
var dvvt = DICHVU_VT_BKN.FirstOrDefault(d => d.DICHVUVT_ID == obj.data_id);
if (obj.data_type == 0 || obj.data_type == 2)
{
if (dvvt != null)
{
fileUpload.Add($"{dvvt.MA_DVVT.ToLower()}.dbf");
}
var upload = UploadData(obj.DataSource, strUpload, fileUpload);
if (upload == (int)Common.Objects.ResultCode._extension)
{
return(Json(new { danger = "Tệp phải định dạng .dbf!" }, JsonRequestBehavior.AllowGet));
}
else if (upload == (int)Common.Objects.ResultCode._length)
{
return(Json(new { danger = "Chưa đủ tệp!" }, JsonRequestBehavior.AllowGet));
}
}
if (dvvt != null && obj.data_type > 0)
{
strUpload += ImportData(obj, dvvt);
}
//Delete .BAK
FileManagerController.RemoveFileSource(obj.DataSource, false);
return(Json(new { success = strUpload }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
}
public JsonResult UpdateData(Common.DefaultObj obj)
{
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
try {
var qry = $"DELETE {Common.Objects.TYPE_HD.HD_CD} WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
_Con.Connection.Query(qry);
qry = $@"INSERT INTO {Common.Objects.TYPE_HD.HD_CD}
SELECT NEWID() AS ID,ID AS CD_ID,NEWID() AS DBKH_ID,TYPE_BILL,TIME_BILL,APP_ID,SO_TB,KIEU,TB_DAUCUOI,CUOC_TB,CUOC_DV,CUOC_NH,CUOC_NT,CUOC_PSTN,
CUOC_V171,CUOC_VDNK,CUOC_DD,CUOC_IDD,CUOC_V171Q,CUOC_VDNKQ,CUOC_CI,CUOC_KM,CHIET_KHAU,0 AS TONG_IN,TONG,VAT,TONGCONG,DUPE_FLAG
FROM {Common.Objects.TYPE_HD.CD} WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND TONGCONG>0";
_Con.Connection.Query(qry);
qry = $"UPDATE a SET a.DBKH_ID=b.ID FROM {Common.Objects.TYPE_HD.HD_CD} a INNER JOIN {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} b ON a.SO_TB=b.ACCOUNT WHERE a.TYPE_BILL=b.TYPE_BILL AND b.FIX=0 AND b.FLAG=1";
_Con.Connection.Query(qry);
return(Json(new { success = $"{Common.Objects.TYPE_HD.HD_CD} - Cập nhật dữ liệu thành công" }));
} catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index })); } finally { _Con.Close(); }
}
public JsonResult UpdateData(Common.DefaultObj obj)
{
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
try {
var qry = $"DELETE {Common.Objects.TYPE_HD.HD_DD} WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
_Con.Connection.Query(qry);
qry = $@"INSERT INTO {Common.Objects.TYPE_HD.HD_DD}
SELECT NEWID() AS ID,ID AS DD_ID,NEWID() AS DBKH_ID,TYPE_BILL,TIME_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 FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND TONGCONG>0";
_Con.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.ACCOUNT WHERE a.TYPE_BILL=b.TYPE_BILL AND b.FIX=0 AND b.FLAG=1";
_Con.Connection.Query(qry);
return(Json(new { success = $"{Common.Objects.TYPE_HD.HD_DD} - Cập nhật dữ liệu thành công" }));
} catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index })); } finally { _Con.Close(); }
}
public JsonResult DeleteExportCustom(Common.DefaultObj obj)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
obj.DataSource = Common.Directories.HDData;
obj = getDefaultObj(obj);
try
{
var data = Oracle.Connection.Get <Models.EXPORT_CUSTOM>(obj.file);
if (data != null)
{
var qry = $"UPDATE EXPORT_CUSTOM SET FLAG={(data.FLAG == 0 ? 1 : 0)} WHERE ID='{obj.file}'";
Oracle.Connection.Query(qry);
}
return(Json(new { success = "Cập nhật thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.Close(); }
}
//Common
Common.DefaultObj getDefaultObj(Common.DefaultObj obj)
{
//Kiểm tra tháng đầu vào
if (obj.ckhMerginMonth)
{
obj.time = DateTime.Now.AddMonths(-1).ToString("yyyyMM");
obj.year_time = int.Parse(obj.time.Substring(0, 4));
obj.month_time = int.Parse(obj.time.Substring(4, 2));
}
obj.month_time = int.Parse(obj.time.Substring(4, 2));
obj.year_time = int.Parse(obj.time.Substring(0, 4));
obj.day_in_month = DateTime.DaysInMonth(obj.year_time, obj.month_time);
obj.datetime = new DateTime(obj.year_time, obj.month_time, 1);
obj.month_year_time = (obj.month_time < 10 ? "0" + obj.month_time.ToString() : obj.month_time.ToString()) + "/" + obj.year_time;
obj.month_before = DateTime.Now.AddMonths(-2).ToString("yyyyMM");
obj.time = obj.time;
obj.ckhMerginMonth = obj.ckhMerginMonth;
//obj.file = $"BKN_th";
obj.DataSource = Server.MapPath("~/" + obj.DataSource) + obj.time + "\\";
return(obj);
}
public JsonResult XuLyHD(Common.DefaultObj obj, bool?ckhExcept)
{
var ok = new System.Text.StringBuilder();
var err = new System.Text.StringBuilder();
var index = 0;
obj.DataSource = Common.Directories.HDData;
obj = getDefaultObj(obj);
var hddttime = obj.datetime.ToString("MMyyyy");
var FoxPro = new TM.Connection.OleDBF(obj.DataSource);
var indexError = "";
// Loại bỏ
obj.data_value = string.IsNullOrEmpty(obj.data_value) ? null : $",{obj.data_value.Trim(',')},";
try
{
var hdall = "hdall" + obj.time;
var hdallhddt = "hdall_hddt" + obj.time;
TM.OleDBF.DataSource = TM.Common.Directories.HDData + obj.time + "\\";
//Khai báo biến
var listKey = new List <string>();
var NumberToLeter = new TM.Helper.NumberToLeter();
var hasError = false;
string fileName = "hoadon";
string fileNameDBFHDDT = TM.OleDBF.DataSource + hdallhddt + ".dbf";
string fileNameXMLFull = TM.OleDBF.DataSource + fileName + ".xml";
string fileNameZIPFull = TM.OleDBF.DataSource + fileName + ".zip";
string fileNameXMLError = TM.OleDBF.DataSource + fileName + "_Error.xml";
//Xóa file HDDT cũ
FileManagerController.DeleteDirFile(fileNameDBFHDDT);
FileManagerController.DeleteDirFile(fileNameZIPFull);
FileManagerController.DeleteDirFile(fileNameXMLError);
//
TM.IO.FileDirectory.Copy(TM.OleDBF.DataSource + hdall + ".dbf", fileNameDBFHDDT);
//Xử lý trùng mã thanh toán khác đơn vị
RemoveDuplicate(TM.OleDBF.DataSource, hdallhddt,
new string[] { "tong_cd", "tong_dd", "tong_net", "tong_tv", "vat", "tong", "kthue", "giam_tru", "tongcong" },
new string[] { "acc_net", "acc_tv", "so_dd", "so_cd", "diachi_tt", "ma_tuyen", "ms_thue", "ma_dt", "ma_cbt", "banknumber" },
"Remove Duplicate hoadon", null);
//Get data from HDDT
var data = FoxPro.Connection.Query <Models.HD_DT>($"SELECT * FROM {hdallhddt}").ToList().Trim(); //TM.OleDBF.ToDataTable("SELECT * FROM " + hdallhddt);
//HDDT Hoa don
using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLFull), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite))
{
ok.Append($"<Invoices><BillTime>{obj.time}</BillTime>").AppendLine();
err.Append($"<Invoices><BillTime>{obj.time}</BillTime>").AppendLine();
foreach (var i in data)
{
index++;
indexError = $"Index: {index}, APP_ID: {i.APP_ID}";
#region List Product
//<Product>
//<ProdName>Dịch vụ Internet</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>1</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ MyTv</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ Cố định</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>20000</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ Di động</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
//<Product>
//<ProdName>Khuyến mại, giảm trừ</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
#endregion
var check = true;
var MA_TT = i.MA_TT.Replace("-", "");
//Check EzPay
if (i.KIEU_TT == 1)
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Is Ezpay</error></Inv>").AppendLine();
check = false;
hasError = true;
}
//Check Error
if (string.IsNullOrEmpty(MA_TT))
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Null Or Empty</error></Inv>").AppendLine();
check = false;
hasError = true;
}
else
{
if (listKey.Contains(MA_TT))
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Trùng mã thanh toán</error></Inv>").AppendLine();
check = false;
hasError = true;
}
}
if (ckhExcept.HasValue)
{
if (!string.IsNullOrEmpty(obj.data_value) && obj.data_value.IndexOf($",{MA_TT},") < 0)
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Loại bỏ</error></Inv>").AppendLine();
check = false;
hasError = true;
}
}
else
{
if (!string.IsNullOrEmpty(obj.data_value) && obj.data_value.IndexOf($",{MA_TT},") < 0)
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Loại bỏ</error></Inv>").AppendLine();
check = false;
hasError = true;
}
}
//Run
if (check)
{
var Products = $"{getProduct("Dịch vụ Internet", i.TONG_NET)}" +
$"{getProduct("Dịch vụ MyTv", i.TONG_TV)}" +
$"{getProduct("Dịch vụ Cố định", i.TONG_CD)}" +
$"{getProduct("Dịch vụ Di động", i.TONG_DD)}" +
$"{getProduct("Khuyến mại, giảm trừ", i.GIAM_TRU)}";
//var so_cd = string.IsNullOrEmpty(dt.Rows[i]["so_cd"].ToString().Trim());
//var so_dd = string.IsNullOrEmpty(dt.Rows[i]["so_dd"].ToString().Trim());
//var acc_net = string.IsNullOrEmpty(dt.Rows[i]["acc_net"].ToString().Trim());
//var acc_tv = string.IsNullOrEmpty(dt.Rows[i]["acc_tv"].ToString().Trim());
//var ma_in = dt.Rows[i]["ma_in"].ToString().Trim();
var Invoice = "<Invoice>" +
$"<MaThanhToan><![CDATA[{getMaThanhToanHD(hddttime, MA_TT, i.MA_IN == 2 ? Common.HDDT.DetailDiDong : Common.HDDT.DetailCoDinh)}]]></MaThanhToan>" +
$"<CusCode><![CDATA[{MA_TT}]]></CusCode>" +
$"<CusName><![CDATA[{(obj.ckhTCVN3 ? i.TEN_TT.TCVN3ToUnicode() : i.TEN_TT)}]]></CusName>" +
$"<CusAddress><![CDATA[{(obj.ckhTCVN3 ? i.DIACHI_TT.TCVN3ToUnicode() : i.DIACHI_TT)}]]></CusAddress>" +
$"<CusPhone><![CDATA[{getCusPhone(i.ACC_NET, i.ACC_TV, i.SO_CD, i.SO_DD)}]]></CusPhone>" +
$"<CusTaxCode>{i.MS_THUE}</CusTaxCode>" +
$"<PaymentMethod>{"TM/CK"}</PaymentMethod>" +
$"<KindOfService>Cước Tháng {obj.month_year_time}</KindOfService>" +
$"<Products>{Products}</Products>" +
$"<Total>{i.TONG}</Total>" +
$"<DiscountAmount>{i.GIAM_TRU}</DiscountAmount>" +
"<VATRate>10</VATRate>" +
$"<VATAmount>{i.VAT}</VATAmount>" +
$"<Amount>{i.TONGCONG}</Amount>" +
$"<AmountInWords><![CDATA[{NumberToLeter.DocTienBangChu(i.TONGCONG, "")}]]></AmountInWords>" +
$"<PaymentStatus>0</PaymentStatus>" +
$"<Extra>{i.MA_CBT};0;0</Extra>" +
$"<ResourceCode>{i.MA_CBT}</ResourceCode>" +
"</Invoice>";
Invoice = $"<Inv><key>{MA_TT}{hddttime}</key>{Invoice}</Inv>";
ok.Append(Invoice).AppendLine();
listKey.Add(MA_TT);
}
if (index % 100 == 0)
{
outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString()));
ok = new System.Text.StringBuilder();
}
}
ok.Append("</Invoices>");
outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString()));
//
outfile.Close();
}
if (hasError)
{
using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLError), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite))
{
err.Append("</Invoices>");
outfile.Write(System.Text.Encoding.UTF8.GetBytes(err.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(err.ToString()));
outfile.Close();
}
//this.danger("Có lỗi xảy ra Truy cập File Manager \"~\\" + TM.OleDBF.DataSource.Replace("Uploads\\", "") + "\" để tải file");
}
//ZipFile
if (obj.ckhZipFile)
{
TM.IO.Zip.ZipFile(new List <string>()
{
fileNameXMLFull
}, fileNameZIPFull, 9);
}
//Insert To File Manager
FileManagerController.DeleteDirFile(fileNameXMLFull);
FileManagerController.InsertFile(fileNameZIPFull);
FileManagerController.InsertFile(fileNameXMLError);
return(Json(new { success = $"Tạo hóa đơn điện tử thành công! Truy cập File Manager \"~\\{TM.OleDBF.DataSource.Replace("Uploads\\", "")}\" để tải file" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = $"{ex.Message} - Index: {index}" }, JsonRequestBehavior.AllowGet)); }
finally { FoxPro.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 XuLyNhapTextData(Common.DefaultObj obj, int rdoTextAddType, string txtDataVal)
{
long index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var provider = System.Globalization.CultureInfo.InvariantCulture;
var msg = "Cập nhật thành công";
try {
//
if (string.IsNullOrEmpty(txtDataVal))
{
return(Json(new { danger = "Vui lòng nhập giá trị!" }));
}
var qry = "";
var dataRow = txtDataVal.Split('\n');
//
if (rdoTextAddType == 9999) //Tích hợp
{
//Remove old
if (obj.data_id == 2)
{
qry = $"DELETE DANHBA_GOICUOC_TICHHOP WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND FIX=1";
_Con.Connection.Query(qry);
}
qry = "SELECT MAX(ID) ID FROM DANHBA_GOICUOC_TICHHOP";
var rs_tmp = _Con.Connection.QueryFirstOrDefault(qry).ID;
if (rs_tmp == null)
{
return(Json(new { danger = "Vui lòng lấy danh sách biến động tích hợp trước!" }));
}
index = rs_tmp;
//
var dataList = new List <Models.DANHBA_GOICUOC_TICHHOP>();
foreach (var i in dataRow)
{
index++;
var tmp = i.Trim('\r').Split('\t');
if (index == rs_tmp + 1)
{
continue;
}
if (tmp.Length > 4)
{
dataList.Add(new Models.DANHBA_GOICUOC_TICHHOP()
{
ID = index,
TIME_BILL = obj.datetime,
MA_TB = tmp[0],
ACCOUNT = tmp[0],
DICHVUVT_ID = int.Parse(tmp[1]),
GOICUOC_ID = int.Parse(tmp[2]),
LOAIGOICUOC_ID = int.Parse(tmp[3]),
DETAILS = tmp[4],
NGAY_BD = obj.datetime,
FIX = 1,
EXTRA_TYPE = 0,
FLAG = 1
});
}
}
//
_Con.Connection.Insert(dataList);
//
qry = $"UPDATE DANHBA_GOICUOC_TICHHOP SET EXTRA_TYPE=1 WHERE LOAIGOICUOC_ID IN (SELECT GOICUOCID FROM BGCUOC WHERE EXTRA_TYPE=1) AND FIX=1 AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
_Con.Connection.Query(qry);
//
//qry = $@"INSERT DANHBA_GOICUOC_TICHHOP select ((select MAX(ID) from DANHBA_GOICUOC_TICHHOP)+ROW_NUMBER() OVER(ORDER BY ID)) as [ID],[TIME_BILL],[MA_TB],[ACCOUNT],[TEN_GOICUOC],[LOAIGOICUOC_ID],[DICHVUVT_ID],[LOAIMAY_ID],[GOICUOC_ID],[NGAY_BD],[NGAY_KT],[DUPE_COUNT],[DUPE_FLAG],[NO_DUPE],[FIX_NGAY_KT],[TH_THANG],[TH_SO_NGAY],[FIX],[EXTRA_TYPE],0 as [FLAG] from DANHBA_GOICUOC_TICHHOP a where FIX=1 and FLAG=1 and FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' and ACCOUNT in (select ACCOUNT from DANHBA_GOICUOC_TICHHOP where FIX=0 and LOAIGOICUOC_ID!=a.LOAIGOICUOC_ID and FLAG=1 and FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}');
// UPDATE DANHBA_GOICUOC_TICHHOP SET EXTRA_TYPE=1 WHERE LOAIGOICUOC_ID IN (SELECT GOICUOCID FROM BANGGIACUOC WHERE EXTRA_TYPE=1) AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
//_Con.Connection.Query(qry, null, null, true, 0);
//qry = $"update a set a.FLAG=2 from DANHBA_GOICUOC_TICHHOP a where FIX=1 and FLAG=1 and FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' and ACCOUNT in (select ACCOUNT from DANHBA_GOICUOC_TICHHOP where FIX=0 and LOAIGOICUOC_ID!=a.LOAIGOICUOC_ID and FLAG=1 and FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}')";
//_Con.Connection.Query(qry);
//qry = $"update a set a.FLAG=1,a.LOAIGOICUOC_ID=(select MAX(LOAIGOICUOC_ID) from DANHBA_GOICUOC_TICHHOP where FIX=0 and LOAIGOICUOC_ID!=a.LOAIGOICUOC_ID and FLAG=1 and FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}') from DANHBA_GOICUOC_TICHHOP a where a.FIX=1 and a.FLAG=2 and FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
//_Con.Connection.Query(qry);
msg += $" tích hợp - {dataList.Count} thuê bao";
}
else if (rdoTextAddType == 9001) //Khuyến mại
{
//Remove old
if (obj.data_id == 2)
{
qry = $"DELETE DISCOUNT WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'"; //AND TYPEID={(int)Common.Objects.TYPE_DISCOUNT.FIX_IN}
_Con.Connection.Query(qry);
}
var dataList = new List <Models.DISCOUNT>();
index = 0;
foreach (var i in dataRow)
{
index++;
var tmp = i.Trim('\r').Split('\t');
if (index == 1)
{
continue;
}
if (tmp.Length > 6)
{
var _data = new Models.DISCOUNT();
_data.ID = Guid.NewGuid();
_data.TYPE_BILL = 9001;
_data.TIME_BILL = obj.datetime;
_data.ACCOUNT = string.IsNullOrEmpty(tmp[0]) ? null : tmp[0].Trim();
_data.TYPE_HD = int.Parse(tmp[1]);
_data.TYPEID = int.Parse(tmp[2]);
_data.TYPE = Enum.GetName(typeof(Common.Objects.TYPE_DISCOUNT), int.Parse(tmp[2])); //Common.Objects.TYPE_DISCOUNT.FIX_IN.ToString(),
_data.VALUE = decimal.Parse(tmp[3]);
_data.NGAY_DK = obj.datetime;
if (!string.IsNullOrEmpty(tmp[4]))
{
_data.NGAY_BD = DateTime.ParseExact(tmp[4], "dd/MM/yyyy", provider);
}
if (!string.IsNullOrEmpty(tmp[5]))
{
_data.NGAY_KT = DateTime.ParseExact(tmp[5], "dd/MM/yyyy", provider);
}
_data.DETAILS = string.IsNullOrEmpty(tmp[6]) ? null : tmp[6].Trim();
_data.FLAG = 1;
dataList.Add(_data);
}
}
_Con.Connection.Insert(dataList);
msg += $" khuyến mại - {dataList.Count} thuê bao";
}
else if (rdoTextAddType == 9006) //Thanh toán trước
{
//Remove old
if (obj.data_id == 2)
{
qry = $"DELETE THANHTOANTRUOC WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND TYPE_BILL=9006";
_Con.Connection.Query(qry);
}
//Get data ttt
qry = $"SELECT * FROM THANHTOANTRUOC WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND TYPE_BILL IN(9002,9006)";
var datattt = _Con.Connection.Query <Models.THANHTOANTRUOC>(qry).ToList();
var dataList = new List <Models.THANHTOANTRUOC>();
index = 0;
//
foreach (var i in dataRow)
{
index++;
var tmp = i.Trim('\r').Split('\t');
if (index == 1)
{
continue;
}
if (tmp.Length > 7)
{
var account = string.IsNullOrEmpty(tmp[0]) ? null : tmp[0].Trim();
//Check exist ttt
if (datattt.Any(d => d.ACCOUNT == account))
{
continue;
}
//Check exist List Insert
if (dataList.Any(d => d.ACCOUNT == account))
{
continue;
}
var _data = new Models.THANHTOANTRUOC();
_data.ID = Guid.NewGuid();
_data.TYPE_BILL = 9006;
_data.TIME_BILL = obj.datetime;
_data.ACCOUNT = _data.MA_TB = _data.SO_MAY = account;
_data.DVVT_ID = int.Parse(tmp[1]);
_data.TOCDO_ID = 0;
_data.GOICUOC_ID = 0;
_data.TONGHANMUC = _data.TIENHANMUC = int.Parse(tmp[2]);
_data.SODU = int.Parse(tmp[3]);
_data.KHOANTIEN = "TTT";
_data.THUC_TRU = 0;
_data.ID_CV = 0;
_data.NGUONDL = 0;
_data.SOTHANG = int.Parse(tmp[6]);
_data.THANG = int.Parse(tmp[4]);
_data.NAM = int.Parse(tmp[5]);
_data.NGAY_TT = DateTime.Now;
_data.GHI_CHU = string.IsNullOrEmpty(tmp[7]) ? null : tmp[7].Trim();
_data.FLAG = 1;
dataList.Add(_data);
}
}
_Con.Connection.Insert(dataList);
//Update NGAY_BD
qry = $"UPDATE THANHTOANTRUOC SET NGAY_BD=CAST(CAST(NAM AS varchar(4))+'-'+CAST(THANG AS varchar(2))+'-1' as datetime) WHERE TYPE_BILL=9006 AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
_Con.Connection.Query(qry);
//Update NGAY_KT
qry = $"UPDATE THANHTOANTRUOC SET NGAY_KT=DATEADD(MONTH,SOTHANG,CAST(CAST(NAM as varchar(4))+'-'+CAST(THANG as varchar(2))+'-01' as datetime)) WHERE TYPE_BILL=9006 AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
_Con.Connection.Query(qry);
msg += $" thanh toán trước fix - {dataList.Count} thuê bao";
}
else if (rdoTextAddType == 9003) //Đặt cọc
{
//Remove old
if (obj.data_id == 2)
{
qry = $"DELETE THANHTOANTRUOC WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND TYPE_BILL=9003";
_Con.Connection.Query(qry);
}
var dataList = new List <Models.THANHTOANTRUOC>();
index = 0;
foreach (var i in dataRow)
{
index++;
var tmp = i.Trim('\r').Split('\t');
if (index == 1)
{
continue;
}
if (tmp.Length > 4)
{
var account = string.IsNullOrEmpty(tmp[0]) ? null : tmp[0].Trim();
//Check exist
qry = $"SELECT * FROM THANHTOANTRUOC WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND TYPE_BILL=9003 AND ACCOUNT='{account}'";
if (_Con.Connection.Query <Models.THANHTOANTRUOC>(qry).Count() > 0)
{
continue;
}
if (dataList.Any(d => d.ACCOUNT == account))
{
continue;
}
var _data = new Models.THANHTOANTRUOC();
_data.ID = Guid.NewGuid();
_data.TYPE_BILL = 9003;
_data.TIME_BILL = obj.datetime;
_data.ACCOUNT = _data.MA_TB = _data.SO_MAY = account;
_data.DVVT_ID = int.Parse(tmp[1]);
_data.TOCDO_ID = 0;
_data.GOICUOC_ID = 0;
_data.TONGHANMUC = int.Parse(tmp[2]);
_data.SODU = int.Parse(tmp[3]);
_data.KHOANTIEN = "DC";
_data.THUC_TRU = 0;
_data.THANG = obj.datetime.Month;
_data.NAM = obj.datetime.Year;
_data.NGAY_TT = DateTime.Now;
_data.GHI_CHU = string.IsNullOrEmpty(tmp[4]) ? null : tmp[4].Trim();
_data.FLAG = 1;
dataList.Add(_data);
}
}
_Con.Connection.Insert(dataList);
msg += $" đặt cọc - {dataList.Count} thuê bao";
}
return(Json(new { success = msg }));
} catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index })); }
}
private string ImportData(Common.DefaultObj obj, Models.DICHVU_VT_BKN DVVT)
{
// var FoxPro = new TM.Connection.OleDBF(obj.DataSource);
// try
// {
// if (DVVT.TABLE_TARGET == "NET")
// {
// var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
// var collection = FoxPro.Connection.Query<Models.NET>(qry).ToList();
// //
// var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
// UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
// //Delete old
// _Con.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'");
// //
// _Con.Connection.Insert(collection);
// }
// if (DVVT.TABLE_TARGET == "MEGA")
// {
// var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
// var collection = FoxPro.Connection.Query<Models.MEGA>(qry).ToList();
// //
// var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
// UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
// //Delete old
// _Con.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'");
// //
// _Con.Connection.Insert(collection);
// }
// if (DVVT.TABLE_TARGET == "HD_NET")
// {
// //
// var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
// var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
// //
// var collection = FoxPro.Connection.Query<Models.HD_NET>(qry).ToList(); ;
// UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
// var collectionInsert = new List<Models.HD_NET>();
// //
// var new_dbkh = FoxPro.Connection.Query<Models.DB_THANHTOAN_BKN>(qry).ToList();
// UpdateProperties(new_dbkh, TABLE_FIELD_SET, DVVT, obj.datetime);
// var old_dbkh = _Con.Connection.Query<Models.DB_THANHTOAN_BKN>($"SELECT * FROM {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} WHERE FIX=1");
// var DataInsert = new List<Models.DB_THANHTOAN_BKN>();
// var DataUpdate = new List<Models.DB_THANHTOAN_BKN>();
// foreach (var i in new_dbkh)
// {
// var _tmp = old_dbkh.FirstOrDefault(d => d.ACCOUNT == i.ACCOUNT);
// var _tmp_collection = collection.FirstOrDefault(d => d.ACCOUNT == i.ACCOUNT);
// if (_tmp_collection != null)
// _tmp_collection.DBKH_ID = i.ID;
// collectionInsert.Add(_tmp_collection);
// if (_tmp != null)
// {
// if (!string.IsNullOrEmpty(i.MA_TB)) _tmp.MA_TB = i.MA_TB;
// 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.MA_DVI)) _tmp.MA_DVI = i.MA_DVI.Trim();
// if (!string.IsNullOrEmpty(i.MA_CBT)) _tmp.MA_CBT = i.MA_CBT.Trim();
// if (!string.IsNullOrEmpty(i.MA_TUYEN)) _tmp.MA_TUYEN = i.MA_TUYEN.Trim();
// if (!string.IsNullOrEmpty(i.MA_KH)) _tmp.MA_KH = i.MA_KH.Trim();
// if (!string.IsNullOrEmpty(i.MA_TT_HNI)) _tmp.MA_TT_HNI = i.MA_TT_HNI.Trim();
// if (!string.IsNullOrEmpty(i.MS_THUE)) _tmp.MS_THUE = i.MS_THUE.Trim();
// _tmp.MA_DT = i.MA_DT;
// _tmp.KHLON_ID = i.KHLON_ID;
// _tmp.LOAIKH_ID = i.LOAIKH_ID;
// _tmp.TH_SD = 1;
// _tmp.ISNULL = 0;
// _tmp.ISNULLMT = 0;
// _tmp.FIX = 1;
// _tmp.FLAG = 1;
// DataUpdate.Add(_tmp);
// }
// else
// {
// i.MA_DT = 1;
// i.TH_SD = 1;
// i.ISNULL = 1;
// i.ISNULLMT = 1;
// i.FIX = 1;
// i.FLAG = 1;
// DataInsert.Add(i);
// }
// }
// //Delete old
// _Con.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'");
// //
// _Con.Connection.Insert(collectionInsert);
// _Con.Connection.Insert(DataInsert);
// _Con.Connection.Update(DataUpdate);
// }
// else if (DVVT.TABLE_TARGET == "MYTV")
// {
// var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
// var collection = FoxPro.Connection.Query<Models.MYTV>(qry).ToList();
// //
// var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
// UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
// //Delete old
// _Con.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'");
// //
// _Con.Connection.Insert(collection);
// }
// else if (DVVT.TABLE_TARGET == "HD_MYTV")
// {
// var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
// var collection = FoxPro.Connection.Query<Models.HD_MYTV>(qry).ToList();
// //
// var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
// UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
// //Delete old
// _Con.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'");
// //
// _Con.Connection.Insert(collection);
// }
// else if (DVVT.TABLE_TARGET == "DISCOUNT")
// {
// var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
// var collection = FoxPro.Connection.Query<Models.DISCOUNT>(qry).ToList();
// //
// var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
// UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
// //Delete old
// _Con.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'");
// //
// _Con.Connection.Insert(collection);
// }
// else if (DVVT.TABLE_TARGET == "THANHTOANTRUOC")
// {
// var qry = $"SELECT a.*,a.MA_TB AS ACCOUNT FROM {DVVT.MA_DVVT} a";
// var collection = FoxPro.Connection.Query<Models.THANHTOANTRUOC>(qry).ToList();
// //
// var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
// UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
// //Delete old
// _Con.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'");
// //
// _Con.Connection.Insert(collection);
// //Update NGAY_BD
// qry = $"UPDATE THANHTOANTRUOC SET NGAY_BD=CAST(CAST(NAM AS varchar(4))+'-'+CAST(THANG AS varchar(2))+'-1' as datetime) WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
// _Con.Connection.Query(qry);
// //Update NGAY_KT
// qry = $"UPDATE THANHTOANTRUOC SET NGAY_KT=DATEADD(MONTH,SOTHANG,CAST(CAST(NAM as varchar(4))+'-'+CAST(THANG as varchar(2))+'-01' as datetime)) WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
// _Con.Connection.Query(qry);
// }
// else if (DVVT.TABLE_TARGET == "CD")
// {
// var qry = $"ALTER TABLE {DVVT.MA_DVVT} ALTER COLUMN dia_chi c(100)";
// FoxPro.Connection.Query(qry);
// qry = $"SELECT a.*,a.MA_KH1 AS MA_TT,a.TEN_CQ AS TEN_TT,a.DIA_CHI AS DIACHI_TT,a.MA_ST AS MS_THUE,a.dvql_id AS MA_DVI,a.TONG_CUOC AS TONGCONG FROM {DVVT.MA_DVVT} a";
// var collection = FoxPro.Connection.Query<Models.CD>(qry).ToList();
// //
// var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
// UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
// //Delete old
// _Con.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'");
// //
// _Con.Connection.Insert(collection);
// }
// else if (DVVT.TABLE_TARGET == "DD")
// {
// var qry = $"SELECT a.*,a.cuoc_cthue AS TONG,a.thue AS VAT,a.ma_cq AS MA_KH,a.ma_cq AS MA_TT,a.taikhoan AS BANKNUMBER,GIAMTRU AS GIAM_TRU FROM {DVVT.MA_DVVT} a";
// var collection = FoxPro.Connection.Query<Models.DD>(qry).ToList();
// //
// var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
// UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
// //Delete old
// _Con.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'");
// //
// _Con.Connection.Insert(collection);
// }
// else if (DVVT.TABLE_TARGET == "HD_TSL")
// {
// var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
// var collection = FoxPro.Connection.Query<Models.HD_TSL>(qry).ToList();
// //
// var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
// UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
// //Delete old
// _Con.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'");
// //
// _Con.Connection.Insert(collection);
// }
// }
// catch (Exception) { throw; }
// finally { _Con.Close(); FoxPro.Close(); }
return(DVVT.TEN_DVVT);
}
public JsonResult GetPreData(Common.DefaultObj obj, string TimePreData)
{
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
string dvvv = "???";
var qry = "";
try {
qry = "SELECT * FROM DICHVU_VT_BKN WHERE TT_SUDUNG=2";
var DICHVU_VT_BKN = _Con.Connection.Query <Models.DICHVU_VT_BKN>(qry);
var dvvt = DICHVU_VT_BKN.FirstOrDefault(d => d.DICHVUVT_ID == obj.data_id);
if (dvvt != null)
{
dvvv = dvvt.TEN_DVVT;
}
var OldObj = new Common.DefaultObj();
OldObj.time = TimePreData;
OldObj = getDefaultObj(OldObj);
if (obj.data_id == 9001) //Khuyến Mại
{
qry = $"DELETE FROM DISCOUNT WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
_Con.Connection.Query(qry);
//
qry = $@"INSERT INTO DISCOUNT
SELECT NEWID() AS ID,TYPE_HD,TYPE_BILL,CAST('{obj.datetime.ToString("yyyy/MM/dd")}' AS datetime) AS TIME_BILL,ACCOUNT,TYPE,TYPEID,VALUE,DETAILS,NGAY_DK,NGAY_BD,NGAY_KT,FLAG
FROM DISCOUNT WHERE FORMAT(TIME_BILL,'MM/yyyy')='{OldObj.month_year_time}' AND FLAG=1 AND ((NGAY_KT IS NULL) OR (CAST('{obj.datetime.ToString("yyyy/MM/dd")}' AS datetime) BETWEEN NGAY_BD AND NGAY_KT))";
_Con.Connection.Query(qry);
}
else if (obj.data_id == 9003) //Đặt cọc
{
qry = $"DELETE FROM THANHTOANTRUOC WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND TYPE_BILL=9003";
_Con.Connection.Query(qry);
//
qry = $@"INSERT INTO THANHTOANTRUOC
SELECT NEWID() AS ID,TYPE_BILL,CAST('{obj.datetime.ToString("yyyy/MM/dd")}' AS datetime) AS TIME_BILL,ID_TTT,ACCOUNT,MA_TB,SO_MAY,DVVT_ID,TOCDO_ID,0 AS GOICUOC_ID,TIENHANMUC,TONGHANMUC,TT,KHOANTIEN,THANG,NAM,NGAY_BD,NGAY_KT,SODU_TONG AS SODU,ID_CV,NGAY_TT,NGUONDL,CUOC_FIX,TEN_DVVT,SOTHANG,0 AS THUC_TRU,NGAY_SD,GHI_CHU,0 AS TONG,0 AS EXTRA_TONG,0 AS SODU_TONG,1 AS FLAG
FROM THANHTOANTRUOC WHERE FORMAT(TIME_BILL,'MM/yyyy')='{OldObj.month_year_time}' AND TYPE_BILL=9003 AND SODU_TONG>0";
_Con.Connection.Query(qry);
}
else if (obj.data_id == 9682) //MyTV Mergin
{
qry = $"DELETE FROM HD_MYTV WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND TYPE_BILL=9682";
_Con.Connection.Query(qry);
//
qry = $@"INSERT INTO HD_MYTV
SELECT NEWID() AS ID,MYTV_ID,DBKH_ID,TYPE_BILL,CAST('{obj.datetime.ToString("yyyy/MM/dd")}' AS datetime) AS TIME_BILL,ACCOUNT,TOC_DO,TT_THANG,NGAY_TB,NGAY_TB_PTTB,GOICUOCID,TH_THANG,TH_HUY,DUPECOUNT,ISDATMOI,ISHUY,ISTTT,ISDATCOC,PAYTV_FEE,SUB_FEE,GIAM_TRU,TONG_TTT,TONG_DC,TONG_IN,TONG,VAT,TONGCONG,SIGNDATE,REGISTDATE,NGAY_SD,NGAY_KHOA,NGAY_MO,NGAY_KT
FROM HD_MYTV WHERE FORMAT(TIME_BILL,'MM/yyyy')='{OldObj.month_year_time}' AND TYPE_BILL=9682";
_Con.Connection.Query(qry);
}
else if (obj.data_id == 9662) //MegaVNN Mergin
{
qry = $"DELETE FROM HD_NET WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND TYPE_BILL=9662";
_Con.Connection.Query(qry);
//
qry = $@"INSERT INTO HD_NET
SELECT NEWID() AS ID,NET_ID,DBKH_ID,TYPE_BILL,CAST('{obj.datetime.ToString("yyyy/MM/dd")}' AS datetime) AS TIME_BILL,ACCOUNT,MA_TB,TOC_DO,TT_THANG,TBTHG,NGAY_TB,NGAY_TB_PTTB,GOICUOCID,TH_THANG,TH_HUY,DUPECOUNT,ISDATMOI,ISHUY,ISTTT,ISDATCOC,GIAM_TRU,CUOC_IP,CUOC_EMAIL,CUOC_DATA,CUOC_SD,CUOC_TB,TONG_TTT,TONG_DC,TONG_IN,TONG,VAT,TONGCONG,NGAY_DKY,NGAY_CAT,NGAY_HUY,NGAY_CHUYEN,NGAY_SD,NGAY_KHOA,NGAY_MO,NGAY_KT
FROM HD_NET WHERE FORMAT(TIME_BILL,'MM/yyyy')='{OldObj.month_year_time}' AND TYPE_BILL=9662";
_Con.Connection.Query(qry);
}
else if (obj.data_id == 9692) //Fiber Mergin
{
qry = $"DELETE FROM HD_NET WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND TYPE_BILL=9692";
_Con.Connection.Query(qry);
//
qry = $@"INSERT INTO HD_NET
SELECT NEWID() AS ID,NET_ID,DBKH_ID,TYPE_BILL,CAST('{obj.datetime.ToString("yyyy/MM/dd")}' AS datetime) AS TIME_BILL,ACCOUNT,MA_TB,TOC_DO,TT_THANG,TBTHG,NGAY_TB,NGAY_TB_PTTB,GOICUOCID,TH_THANG,TH_HUY,DUPECOUNT,ISDATMOI,ISHUY,ISTTT,ISDATCOC,GIAM_TRU,CUOC_IP,CUOC_EMAIL,CUOC_DATA,CUOC_SD,CUOC_TB,TONG_TTT,TONG_DC,TONG_IN,TONG,VAT,TONGCONG,NGAY_DKY,NGAY_CAT,NGAY_HUY,NGAY_CHUYEN,NGAY_SD,NGAY_KHOA,NGAY_MO,NGAY_KT
FROM HD_NET WHERE FORMAT(TIME_BILL,'MM/yyyy')='{OldObj.month_year_time}' AND TYPE_BILL=9692";
_Con.Connection.Query(qry);
}
else if (obj.data_id == 9999) //Tích hợp
{
qry = $"DELETE FROM DANHBA_GOICUOC_TICHHOP WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}' AND FIX=1 AND FLAG=1";
_Con.Connection.Query(qry);
//
qry = $@"INSERT INTO DANHBA_GOICUOC_TICHHOP
SELECT NEWID() AS ID,CAST('{obj.datetime.ToString("yyyy/MM/dd")}' AS datetime) AS TIME_BILL,MA_TB,ACCOUNT,TEN_GOICUOC,LOAIGOICUOC_ID,DICHVUVT_ID,LOAIMAY_ID,GOICUOC_ID,NGAY_BD,NGAY_KT,DUPE_COUNT,DUPE_FLAG,NO_DUPE,FIX_NGAY_KT,TH_THANG,TH_SO_NGAY,FIX,EXTRA_TYPE,DETAILS,FLAG
FROM HD_NET WHERE FORMAT(TIME_BILL,'MM/yyyy')='{OldObj.month_year_time}' AND FIX=1 AND FLAG=1";
_Con.Connection.Query(qry);
}
return(Json(new { success = $"Nhập dữ liệu {dvvv} từ {OldObj.datetime.ToString("MM/yyyy")} sang {obj.datetime.ToString("MM/yyyy")} thành công" }));
} catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index })); }
}
public JsonResult UpdateContact(Common.DefaultObj obj)
{
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var TYPE_BILL = "2";
try {
var qry = $"SELECT * FROM {Common.Objects.TYPE_HD.DD} WHERE FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
var data = _Con.Connection.Query <Models.DD>(qry);
//
qry = $"SELECT * FROM {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} WHERE FIX=0 AND FLAG=1 AND TYPE_BILL IN({TYPE_BILL})";
var dbkh = _Con.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.ACCOUNT == i.SO_TB);
if (_tmp != null)
{
_tmp.MA_KH = i.MA_KH;
_tmp.MA_TT_HNI = 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;
if (i.MA_DVI.HasValue)
{
_tmp.MA_DVI = i.MA_DVI.Value.ToString();
}
if (i.MA_CBT.HasValue)
{
_tmp.MA_CBT = i.MA_CBT.Value.ToString();
}
_tmp.MA_TUYEN = i.MA_TUYEN;
//_tmp.CUSTCATE = i.CUSTCATE;
//_tmp.STK = i.STK;
_tmp.MA_DT = i.DTUONG_ID.HasValue ? i.DTUONG_ID.Value : 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.ACCOUNT = _d.MA_TB = i.SO_TB;
_d.MA_KH = i.MA_KH;
_d.MA_TT_HNI = 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;
if (i.MA_DVI.HasValue)
{
_d.MA_DVI = i.MA_DVI.Value.ToString();
}
if (i.MA_CBT.HasValue)
{
_d.MA_CBT = i.MA_CBT.Value.ToString();
}
_d.MA_TUYEN = i.MA_TUYEN;
//_d.CUSTCATE = i.CUSTCATE;
//_d.STK = i.STK;
_d.MA_DT = i.DTUONG_ID.HasValue ? i.DTUONG_ID.Value : 1;
_d.TH_SD = 1;
_d.ISNULL = 0;
_d.ISNULLMT = 0;
_d.FIX = 0;
_d.FLAG = 1;
DataInsert.Add(_d);
}
}
//
if (DataInsert.Count > 0)
{
_Con.Connection.Insert(DataInsert);
}
if (DataUpdate.Count > 0)
{
_Con.Connection.Update(DataUpdate);
}
//
return(Json(new { success = $"{Common.Objects.TYPE_HD.HD_CD} - Cập nhật: {DataUpdate.Count} - Thêm mới: {DataInsert.Count}" }));
} catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index })); } finally { _Con.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 XuLyKH(Common.DefaultObj obj)
{
var ok = new System.Text.StringBuilder();
var err = new System.Text.StringBuilder();
var index = 0;
obj.DataSource = Common.Directories.HDData;
obj = getDefaultObj(obj);
var FoxPro = new TM.Connection.OleDBF(obj.DataSource);
var hddttime = obj.datetime.ToString("MMyyyy");
// Loại bỏ
obj.data_value = string.IsNullOrEmpty(obj.data_value) ? null : $",{obj.data_value.Trim(',')},";
try
{
var listKey = new List <string>();
var hasError = false;
var fileName = "cus";
var hdallhddt = "hdall_hddt" + obj.time;
var fileNameDBFHDDT = TM.OleDBF.DataSource + hdallhddt + ".dbf";
var fileNameXMLFull = TM.OleDBF.DataSource + fileName + ".xml";
var fileNameZIPFull = TM.OleDBF.DataSource + fileName + ".zip";
var fileNameXMLError = TM.OleDBF.DataSource + fileName + "_Error.xml";
//Xóa file HDDT cũ
FileManagerController.DeleteDirFile(fileNameZIPFull);
FileManagerController.DeleteDirFile(fileNameXMLError);
//Get data from HDDT
var data = FoxPro.Connection.Query <Models.HD_DT>($"SELECT * FROM {hdallhddt}").ToList().Trim(); //TM.OleDBF.ToDataTable("SELECT * FROM " + hdallhddt);
using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLFull), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite))
{
ok.Append("<Customers>").AppendLine();
err.Append("<Customers>").AppendLine();
foreach (var i in data)
{
index++;
var MA_TT = i.MA_TT.Replace("-", "");
#region List Product
//<Product>
//<ProdName>Dịch vụ Internet</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>1</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ MyTv</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ Cố định</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>20000</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ Di động</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
//<Product>
//<ProdName>Khuyến mại, giảm trừ</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
#endregion
//Check EzPay
var check = true;
if (i.KIEU_TT == 1)
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Is Ezpay</error></Inv>").AppendLine();
check = false;
hasError = true;
}
//Check Error
if (string.IsNullOrEmpty(MA_TT))
{
err.Append($"<Customer><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Null Or Empty HDDT Khách hàng</error></Customer>").AppendLine();
check = false;
hasError = true;
}
else
{
if (listKey.Contains(MA_TT))
{
err.Append($"<Customer><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Trùng mã thanh toán Khách hàng</error></Customer>").AppendLine();
check = false;
hasError = true;
}
}
if (!string.IsNullOrEmpty(obj.data_value) && obj.data_value.IndexOf($",{MA_TT}{hddttime},") < 0)
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Loại bỏ</error></Inv>").AppendLine();
check = false;
hasError = true;
}
//Run
if (check)
{
var customer = "<Customer>" +
$"<Name><![CDATA[{(obj.ckhTCVN3 ? i.TEN_TT.TCVN3ToUnicode() : i.TEN_TT)}]]></Name>" +
$"<Code>{MA_TT}</Code>" +
$"<TaxCode>{i.MS_THUE}</TaxCode>" +
$"<Address><![CDATA[{(obj.ckhTCVN3 ? i.DIACHI_TT.TCVN3ToUnicode() : i.TEN_TT)}]]></Address>" +
"<BankAccountName></BankAccountName>" +
"<BankName></BankName>" +
$"<BankNumber>{i.BANKNUMBER}</BankNumber>" +
"<Email></Email>" +
"<Fax></Fax>" +
$"<Phone>{getCusPhone(i.ACC_NET, i.ACC_TV, i.SO_DD, i.SO_CD)}</Phone>" +
"<ContactPerson></ContactPerson>" +
"<RepresentPerson></RepresentPerson>" +
"<CusType>0</CusType>" +
$"<MaThanhToan><![CDATA[{getMaThanhToanKH(obj.month_year_time, MA_TT, i.MA_IN == 2 ? Common.HDDT.DetailDiDong : Common.HDDT.DetailCoDinh)}]]></MaThanhToan>" +
"</Customer>";
ok.Append(customer).AppendLine();
listKey.Add(MA_TT);
}
if (index % 100 == 0)
{
outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString()));
ok = new System.Text.StringBuilder();
}
}
ok.Append("</Customers>");
outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString()));
//
outfile.Close();
}
if (hasError)
{
using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLError), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite))
{
err.Append("</Customers>");
outfile.Write(System.Text.Encoding.UTF8.GetBytes(err.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(err.ToString()));
outfile.Close();
}
//this.danger("Có lỗi xảy ra Truy cập File Manager \"~\\" + TM.OleDBF.DataSource.Replace("Uploads\\", "") + "\" để tải file");
}
//ZipFile
if (obj.ckhZipFile)
{
TM.IO.Zip.ZipFile(new List <string>()
{
fileNameXMLFull
}, fileNameZIPFull, 9);
}
//Insert To File Manager
FileManagerController.DeleteDirFile(fileNameXMLFull);
FileManagerController.InsertFile(fileNameZIPFull);
FileManagerController.InsertFile(fileNameXMLError);
//Insert To File Manager hdallhddt
FileManagerController.InsertFile(fileNameDBFHDDT);
//Delete .BAK
FileManagerController.RemoveFileSource(obj.DataSource, false);
return(Json(new { success = $"Tạo hóa đơn điện tử thành công! Truy cập File Manager \"~\\{TM.OleDBF.DataSource.Replace("Uploads\\", "")}\" để tải file" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = $"{ex.Message} - Index: {index}" }, JsonRequestBehavior.AllowGet)); }
finally { FoxPro.Close(); }
}
public JsonResult XuLyHuyHD(Common.DefaultObj obj)
{
var index = 0;
try
{
var source = Common.Directories.orther;
var mapSource = Server.MapPath("~/" + source);
string strUpload = "Cập nhật thành công ";
var fileUpload = new List <string>();
var upload = UploadBase(mapSource, strUpload, fileUpload, ".zip");
var file = "";
if (fileUpload.Count > 0) //return Json(new { danger = "Không có tệp!" }, JsonRequestBehavior.AllowGet);
{
if (upload == (int)Common.Objects.ResultCode._extension)
{
return(Json(new { danger = "Tệp phải định dạng .zip!" }, JsonRequestBehavior.AllowGet));
}
else if (upload == (int)Common.Objects.ResultCode._length)
{
return(Json(new { danger = "Chưa đủ tệp!" }, JsonRequestBehavior.AllowGet));
}
file = mapSource + fileUpload[0];
}
else
{
file = mapSource + "hoadon.zip";
}
//Xóa file HDDT cũ
// FileManagerController.DeleteDirFile(file);
var ok = new System.Text.StringBuilder();
//
TM.IO.Zip.ExtractZipFile(file, null, mapSource);
//
var doc = new System.Xml.XmlDocument();
file = file.Replace(".zip", ".xml");
doc.Load(file);
var file_hoadonhuy = "hoadonhuy.xml";
var file_hoadonhuy_zip = "hoadonhuy.zip";
// Loại bỏ
obj.data_value = string.IsNullOrEmpty(obj.data_value) ? null : $",{obj.data_value.Trim(',')},";
using (System.IO.Stream outfile = new System.IO.FileStream(mapSource + file_hoadonhuy, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite))
{
ok.Append("<Inv>");
foreach (System.Xml.XmlNode node in doc.DocumentElement.ChildNodes)
{
if (node.Name == "Inv")
{
foreach (System.Xml.XmlNode cnode in node.ChildNodes)
{
if (cnode.Name == "key")
{
if (string.IsNullOrEmpty(obj.data_value))
{
index++;
// string x = cnode.InnerText;
ok.Append($"<key>{cnode.InnerText}</key>").AppendLine();
}
else
{
if (obj.data_value.IndexOf($",{cnode.InnerText},") < 0)
{
index++;
// string x = cnode.InnerText;
ok.Append($"<key>{cnode.InnerText}</key>").AppendLine();
}
}
}
}
}
// string text = node.InnerText; //or loop through its children as well
}
ok.Append("</Inv>").AppendLine();
outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString()));
//
outfile.Close();
}
//ZipFile
if (obj.ckhZipFile)
{
TM.IO.Zip.ZipFile(new List <string>()
{
source + file_hoadonhuy
}, source + file_hoadonhuy_zip, 9);
}
//Insert To File Manager
FileManagerController.InsertFile(source + file_hoadonhuy);
FileManagerController.InsertFile(source + file_hoadonhuy_zip);
return(Json(new
{
success = $"Tạo hóa đơn hủy thành công! Truy cập File Manager \"~\\{source.Replace("Uploads\\", "")}\" để tải file",
url = UrlDownloadFiles(source + file_hoadonhuy_zip)
}, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = $"{ex.Message} - Index: {index}" }, JsonRequestBehavior.AllowGet)); }
finally { }
}
public JsonResult XuLyHuyHDTheoMATT(Common.DefaultObj obj)
{
var index = 0;
try
{
obj.DataSource = Common.Directories.HDData;
obj = getDefaultObj(obj);
var source = Common.Directories.HDData + obj.time;
var hddttime = obj.datetime.ToString("MMyyyy");
string strUpload = "Cập nhật thành công ";
// var file = obj.DataSource + "hoadon.zip";
//
// TM.IO.Zip.ExtractZipFile(file, null, obj.DataSource);
// FileManagerController.DeleteDirFile(file);
var ok = new System.Text.StringBuilder();
//
var doc = new System.Xml.XmlDocument();
// file = file.Replace(".zip", ".xml");
// doc.Load(file);
var file_hoadonhuy = "hoadonhuy.xml";
var file_hoadonhuy_zip = "hoadonhuy.zip";
// Loại bỏ
obj.data_value = string.IsNullOrEmpty(obj.data_value) ? null : $",{obj.data_value.Trim(',')},";
using (System.IO.Stream outfile = new System.IO.FileStream(obj.DataSource + file_hoadonhuy, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite))
{
ok.Append("<Inv>");
var tthuy = obj.data_value.Trim().Trim(',').Split(',');
foreach (var item in tthuy)
{
ok.Append($"<key>{item}{hddttime}</key>").AppendLine();
}
ok.Append("</Inv>").AppendLine();
outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString()));
//
outfile.Close();
}
//ZipFile
if (obj.ckhZipFile)
{
TM.IO.Zip.ZipFile(new List <string>()
{
obj.DataSource + file_hoadonhuy
}, obj.DataSource + file_hoadonhuy_zip, 9, false);
}
//Insert To File Manager
FileManagerController.InsertFile(obj.DataSource + file_hoadonhuy);
FileManagerController.InsertFile(obj.DataSource + file_hoadonhuy_zip);
FileManagerController.DeleteDirFile(file_hoadonhuy);
return(Json(new
{
success = $"Tạo hóa đơn hủy thành công! Truy cập File Manager \"~\\{source.Replace("Uploads\\", "")}\" để tải file",
url = UrlDownloadFiles($"{source}\\{file_hoadonhuy_zip}")
}, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = $"{ex.Message} - Index: {index}" }, JsonRequestBehavior.AllowGet)); }
finally { }
}
