private DataSet GetDataSetBySp(string inName, OracleParameter[] inParams)
{
using (var IPPDB = new IPPEntities())
{
using (var spCmd = new OracleCommand())
{
DataSet ds = null;
IPPDB.Database.Connection.Open();
spCmd.Connection = new OracleConnection(IPPDB.Database.Connection.ConnectionString);
spCmd.CommandText = inName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
if (inParams != null)
{
spCmd.Parameters.AddRange(inParams);
}
var da = new OracleDataAdapter(spCmd);
ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
}
public List <MODULEINFO> GetModuleList()
{
using (var IPPDB = new IPPEntities())
{
return((from m in IPPDB.MODULEINFOs
select m
).OrderBy(m => m.ID).ToList());
}
}
public int?GetModuleIdByTopicId(int topId)
{
using (var IPPDB = new IPPEntities())
{
return((from t in IPPDB.TOPICs
where t.ID == topId
select t.MODULEID).ToList().FirstOrDefault());
}
}
public List <string> GetTopicApprovers(int topId)
{
using (var IPPDB = new IPPEntities())
{
return((from t in IPPDB.TOPICAPPROVERs
where t.TOPICID == topId
select t.APPROVER).ToList());
}
}
public List <string> GetTopicApproversByFileId(int fileId)
{
using (var IPPDB = new IPPEntities())
{
var topicId = (from f in IPPDB.FILEINFOs
where f.ID == fileId
select f.TOPICID).ToList().FirstOrDefault();
return(GetTopicApprovers((int)topicId));
}
}
public int GetTopicIdByFileId(long?fileId)
{
using (var IPPDB = new IPPEntities())
{
var topicId = (from f in IPPDB.FILEINFOs
where f.ID == fileId
select f.TOPICID).ToList().FirstOrDefault();
return((int)topicId);
}
}
public VAV.Model.Data.IPP.Submitter GeSubmitterById(string id)
{
using (var IPPDB = new IPPEntities())
{
return((from s in IPPDB.SUBMITTERs
where s.ID == id
select new VAV.Model.Data.IPP.Submitter {
ID = s.ID, Email = s.EMAIL, RM = s.RM, Name = s.NAME
}).ToList().FirstOrDefault());
}
}
public Tuple <bool, bool> GetTopicApporve(int topId)
{
using (var IPPDB = new IPPEntities())
{
var topic = (from t in IPPDB.TOPICs
where t.ID == topId
select t).ToList();
var topicApp = topic.Select(t => new Tuple <bool, bool>(t.ISAPPROVE == 1, t.ISINTERNALAPPROVE == 1)).ToList().FirstOrDefault();
return(topicApp);
}
}
protected void ExecNonQuerySp(string inName, OracleParameter[] inParms)
{
using (var IPPDB = new IPPEntities())
{
IPPDB.Database.Connection.Open();
DbCommand cmd = IPPDB.Database.Connection.CreateCommand();
cmd.CommandText = inName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.AddRange(inParms);
cmd.ExecuteNonQuery();
}
}
protected void ExecNonQuerySpWithResult(string inName, OracleParameter[] inParms, out string result)
{
using (var IPPDB = new IPPEntities())
{
IPPDB.Database.Connection.Open();
DbCommand cmd = IPPDB.Database.Connection.CreateCommand();
cmd.CommandText = inName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.AddRange(inParms);
cmd.ExecuteNonQuery();
result = Convert.ToString(cmd.Parameters["V_Result"].Value);
}
}
public static string UpdateFileTopic(DateTime lastSyncTime, DateTime currentSyncTime)
{
var result = "Success";
using (var IPPDB = new IPPEntities())
{
try
{
IPPDB.Database.Connection.Open();
var cmd = IPPDB.Database.Connection.CreateCommand();;
cmd.CommandText = "UpdateFileTopic";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.AddRange(new[] {
new OracleParameter("V_LastSyncTime", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)lastSyncTime
},
new OracleParameter("V_CurrentSyncTime", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)currentSyncTime
}
});
OracleParameter outPar = new OracleParameter();
outPar.ParameterName = "V_Result";
outPar.DbType = DbType.String;
outPar.Size = 400;
outPar.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outPar);
cmd.ExecuteNonQuery();
var ret = (string)cmd.Parameters["V_Result"].Value;
result = ret.Split('!').FirstOrDefault() == "Success" ? "Success" : "Fail";
}
catch (Exception ex)
{
result = "Fail" + ex.Message;
}
finally
{
IPPDB.Database.Connection.Close();
}
return(result);
}
}
public List <TOPIC> GetTopicListByModuleId(int moduleId)
{
if (moduleId != 0)
{
using (var IPPDB = new IPPEntities())
{
return((from m in IPPDB.TOPICs
where m.MODULEID == moduleId
select m).OrderBy(t => t.ID).ToList());
}
}
else
{
using (var IPPDB = new IPPEntities())
{
return((from m in IPPDB.TOPICs
select m).OrderBy(t => t.ID).ToList());
}
}
}
public CmaFile GetFileById(long id, string para)
{
string path = "";
string fileName = "";
string fileType = "";
if (para == "VAV") //for home page download
{
path = "|VAV|Home";
using (var db = new CMAEntities())
{
fileType = db.HOMEFILEs.Where(f => f.ID == id).Select(f => f.FILETYPE).ToList().FirstOrDefault();
fileName = id + "." + fileType;
}
}
else if (para == "IPP") //for macro-ecnomic 100 charts and user uploaded file
{
using (var db = new IPPEntities())
{
var subPath = (from f in db.FILEINFOs
join t in db.TOPICs on f.TOPICID equals t.ID
join m in db.MODULEINFOs on t.MODULEID equals m.ID
where f.ID == id
select new { m.NAMEEN, t.ID, f.FILETYPE }).ToList().FirstOrDefault();
path = "|IPP|" + subPath.NAMEEN + "|" + subPath.ID;
fileName = id + "." + subPath.FILETYPE;
fileType = subPath.FILETYPE;
}
}
else if (para.Contains("WMP"))//cmafiledb
{
using (var db = new Genius_HistEntities())
{
if (para == "WMP_PROSP")
{
var accRoute = db.BANK_FIN_PRD_PROSP.Where(f => f.INNER_CODE == id).ToList().Select(f => f.ACCE_ROUTE).FirstOrDefault().Replace("\\", "|");
path = "|WMP|RROSP|" + accRoute.Substring(0, accRoute.LastIndexOf("|"));
fileName = id.ToString() + accRoute.Substring(accRoute.LastIndexOf("."));
fileType = accRoute.Substring(accRoute.LastIndexOf(".") + 1);
}
else if (para == "WMP_REP")
{
var accRoute = db.FIN_PRD_RPT.Where(f => f.RPT_ID == id).ToList().Select(f => f.ACCE_ROUTE).FirstOrDefault().Replace("\\", "|");
path = "|WMP|PRD_RPT|" + accRoute.Substring(0, accRoute.LastIndexOf("|"));
fileName = id.ToString() + accRoute.Substring(accRoute.LastIndexOf("."));
fileType = accRoute.Substring(accRoute.LastIndexOf(".") + 1);
}
else if (para == "WMP_DISC")
{
var accRoute = db.DISC_ACCE_CFP.Where(f => f.SEQ == id).ToList().Select(f => f.ACCE_ROUTE).FirstOrDefault().Replace("\\", "|");
var accOrder = db.DISC_ACCE_CFP.Where(f => f.SEQ == id).ToList().Select(f => f.ACCE_ORDER).FirstOrDefault().ToString();
var disc_id = db.DISC_ACCE_CFP.Where(f => f.SEQ == id).ToList().Select(f => f.DISC_ID).FirstOrDefault().ToString();
path = "|WMP|DISC_CFP|" + accRoute.Substring(0, accRoute.LastIndexOf("|"));
fileName = disc_id + "_" + accOrder + accRoute.Substring(accRoute.LastIndexOf("."));
fileType = accRoute.Substring(accRoute.LastIndexOf(".") + 1);
}
}
}
else if (para == "ZCX")
{
using (var db = new ZCXEntities())
{
var accRoute = db.RATE_REP.Where(f => f.RATE_ID == id).ToList().Select(f => f.RATE_FILE_PATH).FirstOrDefault().Replace("/", "|");
path = "|ZCX|RATE" + accRoute.Substring(0, accRoute.LastIndexOf("|"));
fileName = accRoute.Substring(accRoute.LastIndexOf("|") + 1);
fileType = accRoute.Substring(accRoute.LastIndexOf(".") + 1);
}
}
else if (para == "RR")
{
using (var db = new ResearchReportEntities())
{
path = "|RR|" + db.ALLFILESINFOes.Where(f => f.FILEID == id).ToList().Select(f => f.BIZCODE + "|" + f.INSTITUTIONINFOCODE + "|" + f.FILETYPECODE).FirstOrDefault();
fileName = db.ALLFILESINFOes.Where(f => f.FILEID == id).ToList().Select(f => f.FILEID.ToString() + "." + f.EXTENSION).FirstOrDefault();
}
}
else if (para.ToUpper() == "LOGO")
{
using (var db = new ResearchReportEntities())
{
var institution = db.INSTITUTIONINFOes.FirstOrDefault(i => i.ID_C == (decimal)id);
if (institution != null)
{
path = "|RR|Logo";
fileName = string.Format("{0}.{1}", institution.CODE, institution.EXTENSION);
}
}
}
var storage = new StorageServiceClient();
var obj = new fileEntity();
try
{
obj = storage.RetriveFileObj(path, fileName);
}
catch (Exception ex)
{
throw ex;
}
if (obj.fileData == null || obj.fileData.Length == 0)
{
return(null);
}
return(new CmaFile {
Id = id, Content = obj.fileData, FileType = fileType
});
}
public void UploadFile(FileCreate fileCreate)
{
var paramArray = new List <OracleParameter>
{
new OracleParameter("V_ID", OracleDbType.Int64)
{
Value = fileCreate.ID
},
new OracleParameter("V_TitleCn", OracleDbType.NVarchar2)
{
Value = fileCreate.TitleCn
},
new OracleParameter("V_TitleEn", OracleDbType.NVarchar2)
{
Value = fileCreate.TitleEn
},
new OracleParameter("V_TopicID", OracleDbType.Int32)
{
Value = fileCreate.TopicID
},
new OracleParameter("V_Status", OracleDbType.Int32, ParameterDirection.InputOutput)
{
Value = fileCreate.Status
},
new OracleParameter("V_SubmitDate", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)fileCreate.SubmitDate
},
new OracleParameter("V_ReportDate", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)fileCreate.ReportDate
},
new OracleParameter("V_FileType", OracleDbType.NVarchar2)
{
Value = fileCreate.FileType
},
//new OracleParameter("V_Doc", OracleDbType.VarBinary) {Value = fileCreate.Doc},
new OracleParameter("V_RIC", OracleDbType.NVarchar2)
{
Value = fileCreate.RIC
},
new OracleParameter("V_DescrCn", OracleDbType.NVarchar2)
{
Value = fileCreate.DescrCn
},
new OracleParameter("V_DescrEn", OracleDbType.NVarchar2)
{
Value = fileCreate.DescrEn
},
new OracleParameter("V_AuthorRM", OracleDbType.NVarchar2)
{
Value = fileCreate.AuthorRM
},
new OracleParameter("V_AuthorEmail", OracleDbType.NVarchar2)
{
Value = fileCreate.AuthorEmail
},
new OracleParameter("V_Author", OracleDbType.NVarchar2)
{
Value = fileCreate.Author
},
new OracleParameter("V_Tag", OracleDbType.NVarchar2)
{
Value = fileCreate.Tag
},
new OracleParameter("V_UploadType", OracleDbType.NVarchar2)
{
Value = fileCreate.UploadType
},
new OracleParameter("V_Source", OracleDbType.NVarchar2)
{
Value = fileCreate.Source
},
new OracleParameter("V_FileName", OracleDbType.NVarchar2)
{
Value = fileCreate.FileName
},
new OracleParameter("V_SubmitterID", OracleDbType.NVarchar2)
{
Value = fileCreate.SubmitterID
},
new OracleParameter("V_DisplayOrder", OracleDbType.NVarchar2)
{
Value = fileCreate.DisplayOrder
},
};
OracleParameter outPar = new OracleParameter();
outPar.ParameterName = "V_Result";
outPar.DbType = DbType.Int32;
outPar.Size = 400;
outPar.Direction = ParameterDirection.Output;
paramArray.Add(outPar);
string fileId = "";
ExecNonQuerySpWithResult("UploadFile", paramArray.ToArray(), out fileId);
if (!string.IsNullOrEmpty(fileId))
{
string path = "";
using (var db = new IPPEntities())
{
var id = Convert.ToInt32(fileId);
var subPath = (from f in db.FILEINFOs
join t in db.TOPICs on f.TOPICID equals t.ID
join m in db.MODULEINFOs on t.MODULEID equals m.ID
where f.ID == id
select new { m.NAMEEN, t.ID }).ToList().FirstOrDefault();
path = "/IPP/" + subPath.NAMEEN + "/" + subPath.ID;
}
FileService.UploadFile(path, fileId + "." + fileCreate.FileType, fileCreate.Doc);
}
}
