public (bool MsgCode, string MsgName) InsertInvestmentTrust(string NO, string NAME, string IDNO)
{
string SQLQuery = " SELECT * FROM AMD..tblInvestmentTrust WHERE NO = @NO ";
var p = new DynamicParameters();
p.Add("@NO", NO);
ISqlHelper sqlHelper = SQLHelper.GetInstance("Dapper", Configurations.AMDConnectionString);
dynamic result = sqlHelper.Query <dynamic>(SQLQuery.ToString(), 1, (dynamic)p);
sqlHelper.Close();
if (((IEnumerable <dynamic>)result).AsList().Count > 0)
{
return(false, "失敗(己存在重覆編號資料)");
}
string SQL = "INSERT INTO AMD..tblInvestmentTrust VALUES (@NO, @NAME, @IDNO)";
p = new DynamicParameters();
p.Add("@NO", NO);
p.Add("@NAME", NAME);
p.Add("@IDNO", IDNO);
sqlHelper = SQLHelper.GetInstance("Dapper", Configurations.AMDConnectionString);
sqlHelper.Open();
result = sqlHelper.ExecuteNonQuery(SQL, 1, (dynamic)p);
sqlHelper.Close();
return(((bool)result) ? (true, "成功") : (false, "失敗"));
}
private IQueryable <MenuMapModel> GetMenu(int BaseCode)
{
var result = new StringBuilder();
string query = @"
SELECT A.SiteMapName, A.SiteMapURL,A.SiteMapID,A.ParentSiteMapID,A.ProgramID
FROM ( SELECT *
FROM SYSDATA..SMSiteMap WITH ( NOLOCK )
WHERE (ISNULL(DisplayMode, 0) = 0 OR DisplayMode = @DisplayMode)
AND LevelTree LIKE ';'+CAST(@BaseCode AS VARCHAR)+';%'
AND LEN(LevelOrder) >= 8
) A
INNER JOIN SYSDATA..SMPermissionCache B WITH ( NOLOCK ) ON A.ProgramID = B.ProgramID
AND B.EmpNo = @EmpNo
ORDER BY A.LevelOrder";
var p = new DynamicParameters();
p.Add("@EmpNo", _emp.EmpNo);
p.Add("@DisplayMode", (Environment.MachineName.Contains("SRVCRMWEBP")) ? 1 : 2);
p.Add("@BaseCode", BaseCode);
ISqlHelper sqlHelper = SQLHelper.GetInstance("Dapper", _configurations.SYSDATAConnectionString);
var data = sqlHelper.Query <MenuMapModel>(query, 1, p).AsQueryable();
sqlHelper.Close();
return(data);
}
public bool DeleteInvestmentTrust(string NO)
{
string SQL = "DELETE FROM AMD..tblInvestmentTrust WHERE NO = @NO";
var p = new DynamicParameters();
p.Add("@NO", NO);
ISqlHelper sqlHelper = SQLHelper.GetInstance("Dapper", Configurations.AMDConnectionString);
sqlHelper.Open();
bool result = sqlHelper.ExecuteNonQuery(SQL, 1, (dynamic)p);
sqlHelper.Close();
return(result);
}
public bool UpdateInvestmentTrust(string NO, string NAME, string IDNO)
{
string SQL = "UPDATE AMD..tblInvestmentTrust SET NAME = @NAME, IDNO = @IDNO WHERE NO = @NO";
var p = new DynamicParameters();
p.Add("@NO", NO);
p.Add("@NAME", NAME);
p.Add("@IDNO", IDNO);
ISqlHelper sqlHelper = SQLHelper.GetInstance("Dapper", Configurations.AMDConnectionString);
sqlHelper.Open();
bool result = sqlHelper.Execute(SQL, 1, (dynamic)p);
sqlHelper.Close();
return(result);
}
private List <MenuMapModel> GetMenu(string Url)
{
var MainUrl = Url.Substring(0, Url.LastIndexOf("/"));
var result = new StringBuilder();
var ListSiteMap = new List <MenuMapModel>();
string query = @"
IF EXISTS(SELECT LevelTree FROM SYSDATA..SMSiteMap WITH(NOLOCK) WHERE LevelTree like '%4034%' AND SiteMapURL like @SiteMapURL)
BEGIN
SELECT LevelTree FROM SYSDATA..SMSiteMap WITH(NOLOCK) WHERE LevelTree like '%4034%' AND SiteMapURL like @SiteMapURL
END
ELSE
BEGIN
SELECT LevelTree FROM SYSDATA..SMSiteMap WITH(NOLOCK) WHERE LevelTree like '%4034%' AND SiteMapURL like @SiteMapMainUrl
END";
var p = new DynamicParameters();
p.Add("@SiteMapURL", "%" + Url + "%");
p.Add("@SiteMapMainUrl", "%" + MainUrl + "%");
ISqlHelper sqlHelper = SQLHelper.GetInstance("Dapper", _configurations.SYSDATAConnectionString);
var data = sqlHelper.Query <string>(query, 1, p).FirstOrDefault();
var ListNode = data.Trim(';').Split(";");
ListNode = ListNode.Skip <string>(1).ToArray();
foreach (var SiteMapID in ListNode)
{
query = @"
SELECT SiteMapName,SiteMapURL,SiteMapID
FROM SYSDATA..SMSiteMap WITH(NOLOCK)
WHERE SiteMapID = @SiteMapID";
p = new DynamicParameters();
p.Add("@SiteMapID", SiteMapID);
var model = sqlHelper.Query <MenuMapModel>(query, 1, p).FirstOrDefault();
ListSiteMap.Add(model);
}
sqlHelper.Close();
return(ListSiteMap);
}
public IEnumerable <dynamic> GetInvestmentTrust(string NO, string NAME, string IDNO, int index, int PAGESIZE)
{
StringBuilder SQL = new StringBuilder();
SQL.Append("SELECT 編號=NO, 客戶名稱=NAME, 身份證字號或營利事業統一編號 = IDNO , COUNT(1) OVER() AS TOTALCNT FROM AMD..tblInvestmentTrust WHERE 1=1 ");
var p = new DynamicParameters();
if (!string.IsNullOrWhiteSpace(NO))
{
SQL.Append(" AND NO = @NO ");
p.Add("@NO", NO);
}
if (!string.IsNullOrWhiteSpace(NAME))
{
SQL.Append(" AND NAME like @NAME ");
p.Add("@NAME", "%" + NAME + "%");
}
if (!string.IsNullOrWhiteSpace(IDNO))
{
SQL.Append(" AND IDNO = @IDNO ");
p.Add("@IDNO", IDNO);
}
SQL.Append(" ORDER BY NO ");
if (PAGESIZE != 0)
{
SQL.Append(" OFFSET @index ROWS FETCH NEXT @PAGESIZE ROWS ONLY ");
p.Add("@index", index);
p.Add("@PAGESIZE", PAGESIZE);
}
ISqlHelper sqlHelper = SQLHelper.GetInstance("Dapper", Configurations.AMDConnectionString);
IEnumerable <dynamic> result = sqlHelper.Query <dynamic>(SQL.ToString(), 1, (dynamic)p);
sqlHelper.Close();
return(result);
}