public bool _ExportExportCustom(TM.Connection.Oracle Oracle, TM.Connection.OleDBF FoxPro, Common.DefaultObj obj, Models.EXPORT_CUSTOM EXPORT_CUSTOM) { try { //Remove Old File TM.IO.FileDirectory.Delete($"{obj.DataSource}{EXPORT_CUSTOM.TABLE_NAME}.dbf", false); //Create New File FoxPro.Connection.Query(EXPORT_CUSTOM.QUERY_CREATE); var data = Oracle.Connection.Query(EXPORT_CUSTOM.QUERY_SELECT.Replace("$kyhoadon", obj.KYHD.ToString())).ToList(); var qry = $"SELECT * FROM EXPORT_TABLE WHERE APP_KEY='{EXPORT_CUSTOM.ID}' AND TABLE_TYPE={(int)Common.Objects.TABLE_TYPE.EXPORT_CUSTOM}"; var EXPORT_TABLE = Oracle.Connection.Query <Models.EXPORT_TABLE>(qry).ToList(); var ColumnExport = new Dictionary <string, string>(); FoxPro.Connection.InsertList3(EXPORT_CUSTOM.TABLE_NAME, EXPORT_TABLE, data); //Execute Query FoxPro.Connection.Query($"USE {EXPORT_CUSTOM.TABLE_NAME}"); _ExportQueryExportCustom(FoxPro, EXPORT_CUSTOM.TABLE_NAME, EXPORT_CUSTOM.QUERY_END); //FoxPro.Connection.Query($"USE {EXPORT_CUSTOM.TABLE_NAME}"); //if (EXPORT_CUSTOM.QUERY_END != null) //{ // var QUERY_END = EXPORT_CUSTOM.QUERY_END.Trim(';').Split(';'); // foreach (var i in QUERY_END) // FoxPro.Connection.Query(i.Trim().TrimStart('\n').Replace("$table", EXPORT_CUSTOM.TABLE_NAME)); //} //Delete .BAK FileManagerController.RemoveFileSource(obj.DataSource, false); return(true); } catch (Exception) { throw; } }
public JsonResult CreateExportSave(ExportCustom obj, List <Models.EXPORT_TABLE> DataList) { var OWNER = "TTKD_BKN"; var Oracle = new TM.Connection.Oracle(OWNER); var index = 0; try { if (string.IsNullOrEmpty(obj.ExportName) && string.IsNullOrEmpty(obj.ExportTableName)) { return(Json(new { danger = "Vui lòng nhập tên và tên bảng kết xuất!" }, JsonRequestBehavior.AllowGet)); } var checkUpdate = true; var tables = new List <string>(); var cols = new List <string>(); var qry = ""; var qry_table = "FROM "; var qry_create = $"CREATE TABLE {obj.ExportTableName}(";//CreateTable(Common.Directories.HDData, ExportTableName, data); var qry_select = "SELECT "; var qry_insert = "INSERT INTO"; var qry_Condition = !string.IsNullOrEmpty(obj.Condition) ? $"WHERE {obj.Condition}" : ""; qry = $"SELECT * FROM EXPORT_CUSTOM WHERE LOWER(NAME)=N'{obj.ExportName.ToLower()}' AND TYPE_NAME='{obj.ExportType}'"; var EXPORT_CUSTOM = Oracle.Connection.QueryFirstOrDefault <Models.EXPORT_CUSTOM>(qry); if (EXPORT_CUSTOM == null) { EXPORT_CUSTOM = new Models.EXPORT_CUSTOM(); EXPORT_CUSTOM.ID = Guid.NewGuid().ToString("N"); EXPORT_CUSTOM.NAME = obj.ExportName; EXPORT_CUSTOM.TABLE_NAME = obj.ExportTableName.ToLower(); checkUpdate = false; } EXPORT_CUSTOM.TABLE_LIST = ","; // if (obj.ExportType == Common.Objects.EXPORT_CUSTOM.DEFAULT.ToString()) { foreach (var i in DataList) { if (!tables.Contains(i.TABLE_NAME)) { tables.Add(i.TABLE_NAME); } //Create if (!cols.Contains(i.COLUMN_NAME_EXPORT)) { cols.Add(i.COLUMN_NAME_EXPORT); qry_create += $"[{i.COLUMN_NAME_EXPORT}] {i.COLUMN_TYPE_EXPORT}({i.COLUMN_LENGTH_EXPORT}),"; } //Select qry_select += $"{i.TABLE_NAME}.{i.COLUMN_NAME},"; // i.APP_KEY = EXPORT_CUSTOM.ID; i.TABLE_TYPE = (int)Common.Objects.TABLE_TYPE.EXPORT_CUSTOM; i.COLUMN_TYPE = i.COLUMN_TYPE.ToLower(); i.COLUMN_TYPE_EXPORT = i.COLUMN_TYPE_EXPORT.ToLower(); i.ORDERS = index; i.FLAG = 1; index++; } foreach (var i in tables) { qry_table += $"{i},"; EXPORT_CUSTOM.TABLE_LIST += $"{i},"; } // qry_create = $"{qry_create.Trim(',')})"; qry_select = $"{qry_select.Trim(',')} {qry_table.Trim(',')} {qry_Condition}"; // if (obj.chkUpdateQuery) { EXPORT_CUSTOM.QUERY_CREATE = obj.ExportQueryCreate; EXPORT_CUSTOM.QUERY_SELECT = obj.ExportQuerySelect; //EXPORT_CUSTOM.QUERY_INSERT = ExportQueryInsert; EXPORT_CUSTOM.QUERY_END = obj.ExportQueryEnd; } else { EXPORT_CUSTOM.QUERY_CREATE = qry_create; EXPORT_CUSTOM.QUERY_SELECT = qry_select; EXPORT_CUSTOM.QUERY_INSERT = qry_insert; } } else if (obj.ExportType == Common.Objects.EXPORT_CUSTOM.MODULES.ToString()) { EXPORT_CUSTOM.TABLE_LIST = !string.IsNullOrEmpty(obj.ExportTableList) ? ("," + obj.ExportTableList.Trim().Trim(',') + ",") : ""; EXPORT_CUSTOM.QUERY_CREATE = obj.ExportQueryCreate; EXPORT_CUSTOM.QUERY_SELECT = ""; // qry_select; EXPORT_CUSTOM.QUERY_INSERT = ""; // qry_insert; EXPORT_CUSTOM.QUERY_END = obj.ExportQueryEnd; } else { return(Json(new { danger = "Sai định dạng kết suất, Vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet)); } EXPORT_CUSTOM.TYPE_NAME = obj.ExportType.ToString(); EXPORT_CUSTOM.CONDITION = obj.Condition; EXPORT_CUSTOM.FLAG = 1; if (checkUpdate) { Oracle.Connection.Update(EXPORT_CUSTOM); //Remove EXPORT_TABLE Old qry = $"DELETE FROM EXPORT_TABLE WHERE APP_KEY='{EXPORT_CUSTOM.ID}'"; Oracle.Connection.Query(qry); } else { Oracle.Connection.Insert(EXPORT_CUSTOM); } //var x = new Models.TEST(); //x.ID = Guid.NewGuid().ToString("N"); //x.NAME = "tuanmjnh"; //x.LEVELS = 2; //x.MONEY = 3.5M; //x.TIME = DateTime.Now; //Oracle.Connection.Insert(x); //Insert EXPORT_TABLE New if (DataList != null && DataList.Count > 0) { Oracle.Connection.Insert(DataList); } return(Json(new { data = DataList, success = TM.Common.Language.msgUpdateSucsess }, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); } finally { Oracle.Connection.Close(); } }