public ReaderGroup GetReaderGroup(int groupID)
{
try
{
using (var dbhelper = new DbHelper())
{
// Fetch Reader Group
dbhelper.Command.CommandType = System.Data.CommandType.Text;
dbhelper.Command.CommandText = "SELECT [ReaderGroupID],[ReaderGroupName],[ReaderGroupDescription],[ReaderGroupLocation],[ReaderGroupZone] ,[ReaderDBConnectionString],[ReaderWCFConnectionString] FROM [RF].[ReaderGroup] WHERE [ReaderGroupID] = " + groupID.ToString();
DataSet ds = dbhelper.ExcuteDatset(dbhelper.Command);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
throw new Exception("Reader Group not found for ID " + groupID);
ReaderGroup rdrGrp = DbHelper.DataTableToList<ReaderGroup>(ds.Tables[0]).FirstOrDefault();
// Fetch Readers
dbhelper.Command.CommandType = System.Data.CommandType.Text;
dbhelper.Command.CommandText = "SELECT [ReaderID],[ReaderGroupID],[ReaderTypeID],[ReaderName],[ReaderDescription],[IPAddress],[PortNo],[MACAddress],[AntennaPower],[ReaderPower],[LocationRef],[LocationDescription],[IsDeleted] FROM [RF].[Reader] WHERE [IsDeleted] = 0 AND [ReaderGroupID] = " + groupID.ToString();
DataSet dsReaders = dbhelper.ExcuteDatset(dbhelper.Command);
List<ReaderInfo> readersInfo = DbHelper.DataTableToList<ReaderInfo>(dsReaders.Tables[0]).ToList();
rdrGrp.Readers = readersInfo;
return rdrGrp;
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Employee findEmployeeById(Guid id, DbHelper helper)
{
var query = from e in helper.Employees
where e.ID == id
select e;
var em = query.FirstOrDefault();
return em;
}
/// <summary>
/// 获取数据库帮助对象。
/// </summary>
/// <param name="database">数据库类型</param>
/// <param name="host">数据库主机</param>
/// <param name="instance">数据库实例</param>
/// <param name="account">账号</param>
/// <param name="password">密码</param>
/// <param name="port">端口号</param>
/// <returns>数据库帮助对象</returns>
public static DbHelper Create(
DatabaseType database,
string host,
string instance,
string account,
string password,
int? port = null)
{
var connectionString = GetConnectionString(database, host, instance, account, password, port);
var dbHelper = new DbHelper(Providers[(int)database], connectionString);
switch (database)
{
case DatabaseType.MSSQL:
dbHelper.DbMetadata = new MSSQLMetadata();
break;
case DatabaseType.Oracle:
dbHelper.DbMetadata = new OracleMetadata();
break;
}
return dbHelper;
}
public bool Delete(Where where) {
using (SqlCommand command = new DbHelper().Command) {
string sql = $"delete from {Table} where {where.Result};";
return command.ExecuteNonQueryExt(sql, where) > 0;
}
}
public static List<spPharmacyStockCard> GetspPharmacyStockCardList(String[] parameterField, String[] param)
{
List<spPharmacyStockCard> result = new List<spPharmacyStockCard>();
IDbContext ctx = DbFactory.Configure();
try
{
DbHelper helper = new DbHelper(typeof(spPharmacyStockCard));
ctx.CommandText = "spfmrpt_KartuPersediaanFarmasi";
ctx.CommandType = System.Data.CommandType.StoredProcedure;
//Add Parameter
int count = parameterField.Length;
for (int i = 0; i < count; i++)
{
ctx.Add(parameterField[i], param[i]);
}
using (IDataReader reader = DaoBase.GetDataReader(ctx))
while (reader.Read())
result.Add((spPharmacyStockCard)helper.IDataReaderToObject(reader, new spPharmacyStockCard()));
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return result;
}
public List<DadosAES> ObterItemAESPorNumeroAES(string numeroAES)
{
List<DadosAES> listaAES = new List<DadosAES>();
DbHelper db = new DbHelper();
string cmdText = @"select b.Numero_AES, b.Item_AES
from Frequencia4Edicao a, [dbo].[vw_mantida_curso_turma] b
where 1=1
and a.idCursoTurnoTurma = b.idCursoTurnoTurma
and b.Numero_AES = @Numero_AES
group by b.Numero_AES, b.Item_AES
order by 2";
db.AddParameter(new System.Data.SqlClient.SqlParameter("@Numero_AES", numeroAES));
SqlDataReader dr = db.GetDataReader(cmdText);
if (dr.HasRows)
{
while (dr.Read())
{
listaAES.Add(new DadosAES
{
NumeroAES = dr["Numero_AES"].ToString(),
ItemAES = Convert.ToInt32(dr["Item_AES"])
});
}
}
db.CloseDbConnection();
return listaAES;
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["uname"] == null)
{
Response.Write("<script>alert('系统超时或非法登录，请重新登录！');window.location.href='default.aspx';</script>");
return;
}
if (Session["uid"]==null)
{
Response.Write("<script>alert('系统超时或非法登录，请重新登录！');window.location.href='default.aspx';</script>");
return;
}
if (Session["ucount"] == null)
{
Response.Write("<script>alert('系统超时或非法登录，请重新登录！');window.location.href='default.aspx';</script>");
return;
}
string uid = Session["uid"].ToString();
dbh = new DbHelper();
string sql;
sql = string.Format("select count(*) from userinfo where parent_id='{0}' ", uid);
DbCommand dbc = dbh.GetSqlStringCommond(sql);
int count=int.Parse(dbh.ExecuteScalar(dbc).ToString());
int usercount = int.Parse(Session["ucount"].ToString()); ;
if (count >= usercount)
{
Response.Write("<script>alert('用户最大数超出设定范围，请联系管理员！');window.location.href='userlist.aspx';</script>");
return;
}
if (Request["id"] != null)
{
id = Request["id"];
sql = string.Format("select * from userinfo where id='{0}' ", Request["id"]);
DbCommand dbc3 = dbh.GetSqlStringCommond(sql);
dbr = dbh.ExecuteReader(dbc3);
dbr.Read();
tb_guid.Text = dbr["guid"].ToString();
tb_memo.Text = dbr["memo"].ToString();
tb_buydate.Text = dbr["buy_date"].ToString();
tb_stopdate.Text = dbr["stop_date"].ToString();
tb_username.Text = dbr["user_name"].ToString();
rb_stop.Checked = dbr["active"].ToString() == "0" ? true : false;
rb_active.Checked = dbr["active"].ToString() == "0" ? false : true;
}
else
{
tb_guid.ReadOnly = false;
tb_buydate.Text = DateTime.Now.ToString("yyyyMMdd");
tb_stopdate.Text = DateTime.Now.AddYears(1).ToString("yyyyMMdd");
rb_stop.Checked = true;
}
}
public static Employee findEmployeeByMail(String mail, DbHelper helper)
{
var query = from e in helper.Employees
where e.MailAddress == mail
select e;
var em = query.FirstOrDefault();
return em;
}
public static Employee findEmployeeByName(String name, DbHelper helper)
{
var query = from e in helper.Employees
where e.Name == name
select e;
var em = query.FirstOrDefault();
return em;
}
protected override void getTagSQL()
{
tempCreate = "";
rightsJoin = "";
this.oper = queryParams.GetValue<int>(SParam.OPERATOR);
DbHelper db = new DbHelper(conStr, true);
addSQLInner();
}
/// summary
/// �������ݿ�洢����
/// /summary
/// param name="procedureName"�洢������/param
/// param name="procedureText"�洢��������/param
public override void CreateProcedure(string procedureName, string procedureText)
{
if(procedureText != null && procedureText.Trim() != "")
{
DropProcedure(procedureName);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(procedureText);
}
}
/// summary
/// �������ݿ⺯��
/// /summary
/// param name="functionName"������/param
/// param name="functionText"��������/param
public override void CreateFunction(string functionName, string functionText)
{
if(functionText != null && functionText.Trim() != "")
{
DropFunction(functionName);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(functionText);
}
}
public frmGenres(DbHelper db, DataRow dr, FormMode mode)
: base(db, dr, mode)
{
InitializeComponent();
this.Load += frmGenres_Load;
this.FormClosing += frmGenres_FormClosing;
this.Shown += frmGenres_Shown;
}
public frmCinema(DbHelper db)
: base(db)
{
InitializeComponent();
this.Load += frmCinema_Load;
this.FormClosing += frmCinema_FormClosing;
this.Shown += frmCinema_Shown;
}
public frmGenres(DbHelper db)
: base(db)
{
InitializeComponent();
this.Load += frmGenres_Load;
this.FormClosing += frmGenres_FormClosing;
this.Shown += frmGenres_Shown;
}
public bool Insert(User tModel) {
using (SqlCommand command = new DbHelper().Command) {
command.CommandText =
"insert into eduuser(userid,userpass,name,age,birthday) values (@userid,@userpass,@name,@age,@birthday)";
command.Parameters.AddRange(CreateSqlParameters(tModel));
int ret = command.ExecuteNonQuery();
return ret > 0;
}
}
public bool Insert(User_Model tModel) {
using (SqlCommand command = new DbHelper().Command) {
command.CommandText =
$"insert into {Table}(UserID,[Password],Name,Sex,Birthday,Email) values (@UserID,@Password,@Name,@Sex,@Birthday,@Email)";
command.Parameters.AddRange(CreateSqlParameters(tModel));
int ret = command.ExecuteNonQuery();
return ret > 0;
}
}
static DbHelper()
{
BootStrapServers = new DbHelper<SqlConnection, SqlCommand, SqlParameter>();
BootStrapServers.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["BootStrapServers"].ConnectionString;
//OpenService_CDNCooperators = new DbHelper<MySqlConnection, MySqlCommand, MySqlParameter>();
//OpenService_CDNCooperators.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OpenService_CDNCooperators"].ConnectionString;
OpenService_Files = new DbHelper<MySqlConnection, MySqlCommand, MySqlParameter>();
OpenService_Files.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OpenService_Files"].ConnectionString;
}
public AutomaticCluster(ActorSystem system)
{
_system = system;
_cluster = Cluster.Get(system);
_persistence = SqlitePersistence.Get(system);
_dbHelper = new DbHelper(() =>
{
var conn = new SQLiteConnection(_persistence.JournalSettings.ConnectionString);
conn.Open();
return conn;
});
}
public AutomaticCluster(ActorSystem system)
{
_system = system;
_cluster = Cluster.Get(system);
_persistence = SqlitePersistence.Get(system);
_dbHelper = new DbHelper(() =>
{
var conn = new SQLiteConnection(_persistence.DefaultJournalConfig.GetString("connection-string"));
conn.Open();
return conn;
});
}
public User GetOne(Where @where, string fields) {
using (SqlCommand command = new DbHelper().Command) {
User user = new User();
string sql = $"select top 1 {fields} from {Table} where {where.Result};";
IDataReader reader = command.ExecuteReaderExt(sql, where);
if (reader.Read()) {
user = ReaderModel(reader, fields);
}
return user;
}
}
public bool Update(Update update) {
Dictionary<string, dynamic> dic = update.Dic;
string where = update.WhereCore.Result;
string fv = string.Join(",", dic.Select(pair => $"{pair.Key}=@{pair.Key}"));
string sql = $"update {Table} set {fv} where {where};";
using (SqlCommand command = new DbHelper().Command) {
command.CommandText = sql;
int ret = command.ExecuteNonQueryExt(sql, update);
return ret > 0;
}
}
/// <summary>
/// 获取数据库中的所有自定义对象。
/// </summary>
/// <param name="databaseName">数据库名称</param>
/// <returns>自定义对象名称集合</returns>
public override IList<CustomObject> GetCustomObjects(string databaseName)
{
var result = new List<CustomObject>();
var dbUtil = new DbHelper(MYSQL_PROVIDER, string.Format(MYSQL_CONNECTION_FORMAT, this.ServerUri, this.Account, this.Password, databaseName));
var reader = dbUtil.ExecuteReader("show TABLES");
while (reader.Read())
{
result.Add(new CustomObject { Name = reader.GetString(0) });
}
return result;
}
private void btn_Connect_Click(object sender, EventArgs e) {
FreedomDB.Helper.DbHelper dbHelper = new DbHelper(txt_ConnStr.Text.Trim());
using (SqlCommand command= dbHelper.Command) {
string sql = "SELECT Name FROM SysObjects Where XType='U' ORDER BY Name";
IDataReader reader = command.ExecuteReaderExt(sql);
while (reader.Read()) {
string tableName = reader.GetString(reader.GetOrdinal("Name"));
list_Tables.Items.Add(tableName);
}
}
}
/// <summary>
/// 获取数据库名称集合。
/// </summary>
/// <returns>数据库名称集合</returns>
public override IList<string> GetDatabaseNames()
{
var result = new List<string>();
var dbUtil = new DbHelper(MYSQL_PROVIDER, string.Format(MYSQL_CONNECTION_FORMAT, this.ServerUri, this.Account, this.Password, "mysql"));
var reader = dbUtil.ExecuteReader("show DATABASES");
while (reader.Read())
{
result.Add(reader.GetString(0));
}
return result;
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["uname"] == null)
{
Response.Write("<script>alert('系统超时或非法登录，请重新登录！');window.location.href='default.aspx';</script>");
}
dbh = new DbHelper();
string sql;
sql = string.Format("select * from version" );
DbCommand dbc = dbh.GetSqlStringCommond(sql);
dbr = dbh.ExecuteReader(dbc);
this.GridView1.DataSource = dbr;
this.GridView1.DataBind();
}
private void button5_Click(object sender, EventArgs e)
{
DbHelper dbh = new DbHelper();
PicksDB pdb = new PicksDB();
DataTable dt = pdb.getPicks();
foreach (DataRow r in dt.Rows)
{
if (dbh.savePicks(r["Entrant"].ToString(), r["Golfer"].ToString()) == 0)
{
MessageBox.Show("Error Entering " + r["Entrant"].ToString() + " " + r["Golfer"].ToString());
}
}
}
public void EntityFrameworkTest()
{
DbHelper helper = new DbHelper();
Leave l = new Leave();
l.ID = Guid.NewGuid();
//l.EmployId = 123;
//l.status = LeaveApproveStatus.Pending;
//l.Approver = "Some";
helper.Leaves.Add(l);
Employee e = new Employee();
e.ID = Guid.NewGuid();
helper.Employees.Add(e);
var a=helper.Leaves;
var b = helper.Employees;
}
public void TestMethod1()
{
IDbHelper worker = new DbHelper();
worker.Create(new Account { BalanceAmount = 1, Number = "1" });
var list = worker.Where<Account>((x) => true);
list.FirstOrDefault().BalanceAmount = 34534;
worker.Remove<Account>((x) => x.Id == 1);
var entity = worker.Update<Account>(list[0]);
worker.Dispose();
}
public bool CheckOpen(String Uid)
{
string sql;
dbh = new DbHelper();
sql = string.Format("select count(*) from userinfo where guid='{0}' and active=1", Uid);
DbCommand dbc = dbh.GetSqlStringCommond(sql);
String s = dbh.ExecuteScalar(dbc).ToString();
if (s.Equals("1"))
{
return true;
}
else
{
return false;
}
}
private UserInfo ReadUserInfo(DbCommand dc)
{
#region 用户查询
string AreaXml = "";
string CompanyRoleXml = "";
string tmpPermissionList = "";
UserInfo User = null;
using (IDataReader rdr = DbHelper.ExecuteReader(dc, this.UserStore))
{
if (rdr.Read())
{
User = new UserInfo();
User.UserName = rdr.GetString(rdr.GetOrdinal("UserName"));
User.ProvinceId = rdr.GetInt32(rdr.GetOrdinal("ProvinceId"));
User.CityId = rdr.GetInt32(rdr.GetOrdinal("CityId"));
User.CompanyName = rdr.IsDBNull(rdr.GetOrdinal("CompanyName")) ? "" : rdr.GetString(rdr.GetOrdinal("CompanyName"));
User.CompanyID = rdr.GetString(rdr.GetOrdinal("CompanyID"));
User.ContactInfo.Email = rdr.IsDBNull(rdr.GetOrdinal("ContactEmail")) ? "" : rdr.GetString(rdr.GetOrdinal("ContactEmail"));
User.ContactInfo.Fax = rdr.IsDBNull(rdr.GetOrdinal("ContactFax")) ? "" : rdr.GetString(rdr.GetOrdinal("ContactFax"));
User.ContactInfo.Mobile = rdr.IsDBNull(rdr.GetOrdinal("ContactMobile")) ? "" : rdr.GetString(rdr.GetOrdinal("ContactMobile"));
User.ContactInfo.ContactName = rdr.IsDBNull(rdr.GetOrdinal("ContactName")) ? "" : rdr.GetString(rdr.GetOrdinal("ContactName"));
User.ContactInfo.ContactSex = (EyouSoft.Model.CompanyStructure.Sex) int.Parse(rdr.GetString(rdr.GetOrdinal("ContactSex")));
User.ContactInfo.Tel = rdr.IsDBNull(rdr.GetOrdinal("ContactTel")) ? "" : rdr.GetString(rdr.GetOrdinal("ContactTel"));
User.ContactInfo.QQ = rdr.IsDBNull(rdr.GetOrdinal("QQ")) ? "" : rdr.GetString(rdr.GetOrdinal("QQ"));
User.ContactInfo.MQ = rdr.IsDBNull(rdr.GetOrdinal("MQ")) ? "" : rdr.GetString(rdr.GetOrdinal("MQ"));
User.DepartId = rdr.IsDBNull(rdr.GetOrdinal("DepartId")) ? "" : rdr.GetString(rdr.GetOrdinal("DepartId"));
User.DepartName = rdr.IsDBNull(rdr.GetOrdinal("DepartName")) ? "" : rdr.GetString(rdr.GetOrdinal("DepartName"));
User.ID = rdr.GetString(rdr.GetOrdinal("ID"));
User.IsAdmin = rdr.GetString(rdr.GetOrdinal("IsAdmin")) == "1" ? true : false;
User.IsEnable = rdr.GetString(rdr.GetOrdinal("IsEnable")) == "1" ? true : false;
User.ContactInfo.MQ = rdr.IsDBNull(rdr.GetOrdinal("MQ")) ? "" : rdr.GetString(rdr.GetOrdinal("MQ"));
User.ContactInfo.MSN = rdr.IsDBNull(rdr.GetOrdinal("MSN")) ? "" : rdr.GetString(rdr.GetOrdinal("MSN"));
User.OpUserId = rdr.GetInt32(rdr.GetOrdinal("OpUserId"));
User.PassWordInfo.SetEncryptPassWord(rdr.IsDBNull(rdr.GetOrdinal("Password")) == true ? "" : rdr.GetString(rdr.GetOrdinal("Password")), rdr.IsDBNull(rdr.GetOrdinal("EncryptPassword")) == true ? "" : rdr.GetString(rdr.GetOrdinal("EncryptPassword")), rdr.IsDBNull(rdr.GetOrdinal("MD5Password")) == true ? "" : rdr.GetString(rdr.GetOrdinal("MD5Password")));
//User.CompanyRole.SetRole(EyouSoft.Model.CompanyStructure.CompanyType.地接);
tmpPermissionList = rdr.IsDBNull(rdr.GetOrdinal("PermissionList")) ? "" : rdr.GetString(rdr.GetOrdinal("PermissionList"));
AreaXml = rdr.IsDBNull(rdr.GetOrdinal("AreaId")) ? "" : rdr.GetString(rdr.GetOrdinal("AreaId"));
CompanyRoleXml = rdr.IsDBNull(rdr.GetOrdinal("TypeId")) ? "" : rdr.GetString(rdr.GetOrdinal("TypeId"));
}
}
if (User != null)
{
if (!String.IsNullOrEmpty(tmpPermissionList))
{
string[] PermissionList = tmpPermissionList.Split(',');
User.PermissionList = new int[PermissionList.Length];
for (int i = 0; i < PermissionList.Length; i++)
{
if (EyouSoft.Common.Function.StringValidate.IsInteger(PermissionList[i]))
{
User.PermissionList[i] = int.Parse(PermissionList[i]);
}
}
}
else
{
User.PermissionList = new int[1] {
-1
};
}
if (!String.IsNullOrEmpty(AreaXml))
{
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.LoadXml(AreaXml);
System.Xml.XmlNodeList NodeList = xmlDoc.GetElementsByTagName("AreaId");
User.AreaId = new int[NodeList.Count];
for (int i = 0; i < NodeList.Count; i++)
{
if (EyouSoft.Common.Function.StringValidate.IsInteger(NodeList[i].FirstChild.Value))
{
User.AreaId[i] = int.Parse(NodeList[i].FirstChild.Value);
}
}
}
else
{
User.AreaId = new int[1] {
-1
};
}
if (!String.IsNullOrEmpty(CompanyRoleXml))
{
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.LoadXml(CompanyRoleXml);
System.Xml.XmlNodeList NodeList = xmlDoc.GetElementsByTagName("TypeId");
for (int i = 0; i < NodeList.Count; i++)
{
if (EyouSoft.Common.Function.StringValidate.IsInteger(NodeList[i].FirstChild.Value))
{
User.CompanyRole.SetRole((EyouSoft.Model.CompanyStructure.CompanyType) int.Parse(NodeList[i].FirstChild.Value));
}
}
}
#region 写登录日志
if (!dc.Parameters.Contains("MQID"))
{
dc.Parameters.Clear();
string GetRemoteIP = EyouSoft.Common.Utility.GetRemoteIP();
string GetRemoteArea = this.GetClientArea(GetRemoteIP);
string RequestUrl = EyouSoft.Common.Utility.GetRequestUrl();
dc = this.SystemStore.GetSqlStringCommand(SQL_USERLOGIN_LOG);
this.SystemStore.AddInParameter(dc, "UserId", DbType.AnsiStringFixedLength, User.ID);
this.SystemStore.AddInParameter(dc, "LastLoginIP", DbType.String, EyouSoft.Common.Utility.GetRemoteIP());
this.SystemStore.AddInParameter(dc, "CompanyId", DbType.AnsiStringFixedLength, User.CompanyID);
DbHelper.ExecuteSql(dc, this.SystemStore);
WriteLog(User.CompanyID, User.ID, User.UserName, User.ContactInfo.ContactName, GetRemoteArea,
"用户登录", "用户" + User.UserName + "于" + DateTime.Now.ToString() + "登陆系统", RequestUrl, GetRemoteIP);
}
#endregion
#region 写入组团在线用户
if (User.CompanyRole.HasRole(EyouSoft.Model.CompanyStructure.CompanyType.组团))
{
dc.Parameters.Clear();
dc = this.SystemStore.GetSqlStringCommand(SQL_ONLINELOGIN);
this.SystemStore.AddInParameter(dc, "ID", DbType.AnsiStringFixedLength, Guid.NewGuid().ToString());
this.SystemStore.AddInParameter(dc, "UserId", DbType.AnsiStringFixedLength, User.ID);
this.SystemStore.AddInParameter(dc, "CompanyId", DbType.AnsiStringFixedLength, User.CompanyID);
DbHelper.ExecuteSql(dc, this.SystemStore);
}
#endregion
}
return(User);
#endregion
}
public void TestMethod1()
{
Console.WriteLine(DbHelper.getConnectionString());
}
public static bool Add(SE_DecorativePattern model)
{
bool result = false;
string sql = @"INSERT INTO Configuration.dbo.SE_DecorativePatternConfig
( ID ,
Name ,
Brand ,
Flower ,
ImageUrl1 ,
ImageUrl2 ,
ImageUrl3,
Description,
ArticleID,
ArticleTitle,
CreateDT,
UpdateDT,
ShareParameter
)
VALUES ( @ID , -- ID - uniqueidentifier
@Name , -- Name - nvarchar(100)
@Brand , -- Brand - nvarchar(200)
@Flower , -- Flower - nvarchar(200)
@ImageUrl1 , -- ImageUrl1 - nvarchar(200)
@ImageUrl2 , -- ImageUrl2 - nvarchar(200)
@ImageUrl3 , -- ImageUrl3 - nvarchar(200)
@Description,
@ArticleID,
@ArticleTitle,
GETDATE(),
GETDATE(),
@ShareParameter
)";
var db = DbHelper.CreateDefaultDbHelper();
try
{
db.BeginTransaction();
model.ID = Guid.NewGuid();
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@ID", model.ID.ToString());
cmd.Parameters.AddWithValue("@Name", model.Name);
cmd.Parameters.AddWithValue("@Brand", model.Brand);
cmd.Parameters.AddWithValue("@Flower", model.Flower);
cmd.Parameters.AddWithValue("@ImageUrl1", model.ImageUrl1);
cmd.Parameters.AddWithValue("@ImageUrl2", model.ImageUrl2);
cmd.Parameters.AddWithValue("@ImageUrl3", model.ImageUrl3);
cmd.Parameters.AddWithValue("@Description", model.Description);
cmd.Parameters.AddWithValue("@ArticleID", model.ArticleID);
cmd.Parameters.AddWithValue("@ArticleTitle", model.ArticleTitle);
cmd.Parameters.AddWithValue("@ShareParameter", model.ShareParameter);
db.ExecuteNonQuery(cmd);
sql = @"INSERT INTO configuration.dbo.SE_DecorativePatternDetailConfig
( FK_DecorativePattern ,
ArticleID ,
ArticleTitle,
[Image],
[Description],
OrderBy
)
VALUES ( @FK_DecorativePattern , -- FK_DecorativePattern - uniqueidentifier
@ArticleID , -- ArticleID - int
@ArticleTitle, -- ArticleTitle - nvarchar(200)
@Image,
@Description,
@OrderBy
)";
foreach (var item in model.Items)
{
cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@FK_DecorativePattern", model.ID.ToString());
cmd.Parameters.AddWithValue("@ArticleID", item.ArticleID);
cmd.Parameters.AddWithValue("@ArticleTitle", item.ArticleTitle);
cmd.Parameters.AddWithValue("@Image", item.Image);
cmd.Parameters.AddWithValue("@Description", item.Description);
cmd.Parameters.AddWithValue("@OrderBy", item.OrderBy);
db.ExecuteNonQuery(cmd);
}
db.Commit();
result = true;
}
catch (Exception e)
{
db.Rollback();
}
finally
{
db.Dispose();
}
return(result);
}
public DataTable GetIconDt(string icontype)
{
return(DbHelper.GetDataTable("select * from fg3_funcicon where icontype = '" + icontype + "'"));
}
public TableProvider(DbHelper database, string tableName) : base(database)
{
this.m_tableName = "";
this.m_tableName = tableName;
}
public void InsertMenuFuncIcon(long phid, string busphid, string id, string name)
{
DbHelper.ExecuteNonQuery("insert into fg3_menufuncicon(phid,busphid,id,name) values ("
+ phid + "," + busphid + "," + id + ",'" + name + "')");
}
public async Task <ActionResult <ApiResult <Organization> > > Get(int id)
{
var org = await DbHelper.FindEntity <Organization>(Connector, id);
return(Success(org));
}
/// <summary>
/// 获取公众号领红包 - 明细数据
/// </summary>
/// <returns></returns>
public static async Task<List<OARedEnvelopeDetailModel>> GetOARedEnvelopeDetailAsync(
Guid userId
, string openId
, int officialAccountType
, string drivingLicense
)
{
var baseSql = @"
select [PKID]
,[UserId]
,[NickName]
,[OpenId]
,[ReferrerUserId]
,[GetMoney]
,[GetDate]
,[OfficialAccountType]
,[CreateDatetime]
,[LastUpdateDateTime]
,DrivingLicense
from [Activity].[dbo].[tbl_OARedEnvelopeDetail] with (nolock)
where UserId = @UserId and OfficialAccountType = @OfficialAccountType and IsDeleted = 0
";
if (!string.IsNullOrWhiteSpace(openId))
{
baseSql = baseSql + @"
UNION ALL
select [PKID]
,[UserId]
,[NickName]
,[OpenId]
,[ReferrerUserId]
,[GetMoney]
,[GetDate]
,[OfficialAccountType]
,[CreateDatetime]
,[LastUpdateDateTime]
,DrivingLicense
from [Activity].[dbo].[tbl_OARedEnvelopeDetail] with (nolock)
where OpenId = @OpenId and OfficialAccountType = @OfficialAccountType and IsDeleted = 0
";
}
if (!string.IsNullOrWhiteSpace(drivingLicense))
{
baseSql = baseSql + @"
UNION ALL
select [PKID]
,[UserId]
,[NickName]
,[OpenId]
,[ReferrerUserId]
,[GetMoney]
,[GetDate]
,[OfficialAccountType]
,[CreateDatetime]
,[LastUpdateDateTime]
,DrivingLicense
from [Activity].[dbo].[tbl_OARedEnvelopeDetail] with (nolock)
where DrivingLicense = @DrivingLicense and OfficialAccountType = @OfficialAccountType and IsDeleted = 0
";
}
using (var cmd = new SqlCommand(baseSql))
{
cmd.AddParameter("@UserId", userId);
cmd.AddParameter("@OpenId", openId ?? "");
cmd.AddParameter("@OfficialAccountType", officialAccountType);
cmd.AddParameter("@DrivingLicense", drivingLicense ?? "");
return (await DbHelper.ExecuteSelectAsync<OARedEnvelopeDetailModel>(false, cmd)).ToList();
}
}
public BankAccountHelper(DbHelper _dbHelper)
{
dbHelper = _dbHelper;
}
public void SetUp()
{
ReusableDocumentStore = DbHelper.Create();
}
public static void SqlTriggerUpdT_EtiqFecPag()
{
DbHelper.GenerarXml(SqlContext.TriggerContext, "T_EtiqFecPag");
}
public CPWebApiBaseReturnEntity DeleteGridData(int CurUserId, string CurUserIden, string GridCode, string DataPks)
{
base.SetHeader();
CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
try
{
if (this.CheckUserIden(CurUserId, CurUserIden) == false)
{
re.Result = false;
re.ErrorMsg = "系统检测到非法获取数据，请传入正确的用户会话Key与用户Id参数！";
return(re);
}
CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());
StringBuilder sb = new StringBuilder();
string[] dataPKValueArray = DataPks.Split('@');
if (string.IsNullOrEmpty(grid.DelDataSql))
{
string[] pkArray = grid.PKFieldName.Split(',');
for (int m = 0; m < dataPKValueArray.Length; m++)
{
string strSql = "";
strSql = "DELETE FROM " + grid.MainTableName + " WHERE ";
string[] vArray = dataPKValueArray[m].Split(',');
for (int i = 0; i < pkArray.Length; i++)
{
if (i == 0)
{
strSql += pkArray[i] + "='" + vArray[i] + "'";
}
else
{
strSql += " AND " + pkArray[i] + "='" + vArray[i] + "'";
}
}
sb.Append(strSql);
}
}
else
{
//自己配置了查询条件
string strSql = grid.DelDataSql;
DataPks = "'" + DataPks.Replace("@", "','") + "'";
strSql = strSql.Replace("{@PKValues@}", DataPks);
strSql = CPExpressionHelper.Instance.RunCompile(strSql);
sb.Append(strSql);
}
if (sb.Length > 0)
{
dbHelper.ExecuteNonQuery(sb.ToString());
}
re.Result = true;
return(re);
}
catch (Exception ex)
{
re.Result = false;
re.ErrorMsg = ex.Message.ToString();
return(re);
}
}
/// <summary>
/// 管理员登录
/// </summary>
/// <param name="UserName">用户名</param>
/// <param name="PWD">用户密码</param>
/// <returns>管理员信息</returns>
public MasterUserInfo MasterLogin(string UserName, string PWD)
{
string AreaXml = "";
string tmpPermissionList = "";
MasterUserInfo User = null;
#region 用户查询
DbCommand dc = this.UserStore.GetSqlStringCommand(SQL_MASTERUSERLOGIN);
this.UserStore.AddInParameter(dc, "UID", DbType.String, UserName);
this.UserStore.AddInParameter(dc, "PWD", DbType.String, PWD);
using (IDataReader rdr = DbHelper.ExecuteReader(dc, this.SystemStore))
{
if (rdr.Read())
{
User = new MasterUserInfo();
User.ID = rdr.GetInt32(rdr.GetOrdinal("ID"));
User.UserName = rdr.GetString(rdr.GetOrdinal("UserName"));
User.ContactFax = rdr.GetString(rdr.GetOrdinal("ContactFax"));
User.ContactMobile = rdr.GetString(rdr.GetOrdinal("ContactMobile"));
User.ContactName = rdr.GetString(rdr.GetOrdinal("ContactName"));
User.ContactTel = rdr.GetString(rdr.GetOrdinal("ContactTel"));
User.IsDisable = rdr.GetString(rdr.GetOrdinal("IsDisable")) == "1" ? true : false;
User.IsAdmin = rdr.GetString(rdr.GetOrdinal("IsAdmin")) == "1" ? true : false;
tmpPermissionList = rdr.IsDBNull(rdr.GetOrdinal("PermissionList")) ? "" : rdr.GetString(rdr.GetOrdinal("PermissionList"));
AreaXml = rdr.IsDBNull(rdr.GetOrdinal("AreaId")) ? "" : rdr.GetString(rdr.GetOrdinal("AreaId"));
}
}
if (User != null)
{
if (!String.IsNullOrEmpty(tmpPermissionList))
{
string[] PermissionList = tmpPermissionList.Split(',');
User.PermissionList = new int[PermissionList.Length + 1];
for (int i = 0; i < PermissionList.Length; i++)
{
User.PermissionList[i] = int.Parse(PermissionList[i]);
}
//给所有运营后台用户加上随便逛逛用户的管理权限
User.PermissionList[User.PermissionList.Length - 1] = 0;
}
if (!String.IsNullOrEmpty(AreaXml))
{
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.LoadXml(AreaXml);
System.Xml.XmlNodeList NodeList = xmlDoc.GetElementsByTagName("AreaId");
User.AreaId = new int[NodeList.Count + 1];
User.AreaId[0] = 0;//随便逛逛身份没有城市
for (int i = 1; i <= NodeList.Count; i++)
{
User.AreaId[i] = int.Parse(NodeList[i - 1].FirstChild.Value);
}
}
#region 获取用户所能查看的易诺用户池客户类型
//获取用户所能查看的易诺用户池客户类型
GetMasterUserCustomerType(ref User);
#endregion
UpdateMasterInfo(User);
}
#endregion
return(User);
}
public string GetMenuFuncIconCount(string busphid)
{
return(DbHelper.GetString("select count(*) from fg3_menufuncicon where busphid = " + busphid));
}
/// <summary>
/// 获取对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="database"></param>
/// <param name="procName"></param>
/// <param name="prams"></param>
/// <returns></returns>
public static T GetObject <T>(DbHelper database, string procName, List <DbParameter> prams)
{
return(database.RunProcObject <T>(procName, prams));
}
public UIResult GetMenuNew(MENUEntity data, string host)
{
List <MENUTREEModule> MENU_GROUPList = new List <MENUTREEModule>();
List <PLATFORMEntity> PlatFormList = DbHelper
.SelectList(new PLATFORMEntity()
{
ID = data.PLATFORMID
})
.GroupBy(a => a.ID)
.Select(a =>
{
var pt = a.FirstOrDefault(b => host.IsRegexMatch(b.MATCH));
if (pt == null)
{
pt = a.First();
}
return(new PLATFORMEntity()
{
ID = a.Key,
DOMAIN = pt.DOMAIN
});
}).ToList();
//子系统要多传递参数回来
string sqlgroup = @" SELECT MODULECODE ID,MODULENAME NAME,ICON FROM USERMODULE WHERE LENGTH(MODULECODE)=4 and ENABLE_FLAG=1";
if (int.Parse(employee.Id) > 0)
{
sqlgroup += @" and MODULECODE in (
SELECT DISTINCT SUBSTR(A.MODULECODE,1,4) FROM USERMODULE A,ROLE_MENU B,USER_ROLE C
WHERE A.MENUID=B.MENUID AND B.ROLEID=C.ROLEID
AND C.USERID=" + employee.Id + ")";
}
if (int.Parse(data.PLATFORMID) == 1)
{
sqlgroup += @" and MODULECODE like '02%'";
}
if (int.Parse(data.PLATFORMID) == 2)
{
sqlgroup += @" and MODULECODE like '05%'";
}
sqlgroup += @" ORDER BY MODULECODE";
DataTable menuGroup = DbHelper.ExecuteTable(sqlgroup);
if (menuGroup.IsNotNull())
{
MENU_GROUPList = menuGroup.ToList <MENUTREEModule>();
foreach (var menuGr in MENU_GROUPList)
{
string sql = @" select aa.moduleid id,
ab.id menuid,
aa.modulename name,
ab.url url,
ab.platformid
from usermodule aa, menu ab
where aa.menuid = ab.id and LENGTH(aa.MODULECODE)=6 and aa.ENABLE_FLAG=1
and aa.modulecode like '" + menuGr.ID + "%'";
if (int.Parse(employee.Id) > 0)
{
//因菜单树型权限 按扭权限不全选时 对应菜单未保存到ROLE_MENU
//暂改成 由按扭权单id截取6位关联菜单id by wangkx 20190705
//b.menuid 改为 to_number(substr( to_char(b.menuid),1,6))
// by DZK 20190716 ID取6位引起crm找不到子菜单，根据PLATFORMID 查询
if (int.Parse(data.PLATFORMID) == 1)
{
sql += @" and aa.menuid in (
select a.id
from menu a,
ROLE_MENU b,
USER_ROLE c
where a.id = to_number(substr( to_char(b.menuid),1,6))
and b.roleid = c.roleid
and c.userid = " + employee.Id + @" or
aa.menuid is null)";
}
else
{
sql += @" and aa.menuid in (
select a.id
from menu a,
ROLE_MENU b,
USER_ROLE c
where a.id = b.menuid
and b.roleid = c.roleid
and c.userid = " + employee.Id + @" or
aa.menuid is null)";
}
}
sql += " order by aa.modulecode ";
DataTable menu = DbHelper.ExecuteTable(sql);
foreach (DataRow dr in menu.Rows)
{
string url = dr["URL"].ToString();
dr["PLATFORMID"].ToString().TryToInt(PlatFormId =>
{
var pt = PlatFormList.FirstOrDefault(a => a.ID == PlatFormId.ToString());
if (pt != null)
{
dr["URL"] = pt.DOMAIN + url;
}
});
}
menuGr.MENUList = menu.ToList <MENUEntity>();
}
;
}
;
return(new UIResult(new
{
MENU = MENU_GROUPList
}));
}
/// <summary>
/// 汇付主动通知页面
/// </summary>
/// <returns></returns>
public ActionResult CashBgCallback()
{
string str1 = "";
ReCash m = new ReCash();
m.CmdId = DNTRequest.GetString("CmdId");
m.RespCode = DNTRequest.GetString("RespCode");
m.RespDesc = HttpUtility.UrlDecode(DNTRequest.GetString("RespDesc"));
m.MerCustId = DNTRequest.GetString("MerCustId");
m.OrdId = DNTRequest.GetString("OrdId");
m.UsrCustId = DNTRequest.GetString("UsrCustId");
m.TransAmt = DNTRequest.GetString("TransAmt");
m.OpenAcctId = DNTRequest.GetString("OpenAcctId");
m.OpenBankId = DNTRequest.GetString("OpenBankId");
m.FeeAmt = DNTRequest.GetString("FeeAmt");
m.FeeCustId = DNTRequest.GetString("FeeCustId");
m.FeeAcctId = DNTRequest.GetString("FeeAcctId");
m.ServFee = DNTRequest.GetString("ServFee");
m.ServFeeAcctId = DNTRequest.GetString("ServFeeAcctId");
m.RetUrl = HttpUtility.UrlDecode(DNTRequest.GetString("RetUrl"));
m.BgRetUrl = HttpUtility.UrlDecode(DNTRequest.GetString("BgRetUrl"));
m.MerPriv = DNTRequest.GetString("MerPriv");
m.RespExt = HttpUtility.UrlDecode(DNTRequest.GetString("RespExt"));
m.ChkValue = DNTRequest.GetString("ChkValue");
m.RealTransAmt = DNTRequest.GetString("RealTransAmt");
LoggerHelper.Info("后台取现返回报文:" + JsonHelper.Entity2Json(m));
//验签
StringBuilder chkVal = new StringBuilder();
chkVal.Append(m.CmdId);
chkVal.Append(m.RespCode);
chkVal.Append(m.MerCustId);
chkVal.Append(m.OrdId);
chkVal.Append(m.UsrCustId);
chkVal.Append(m.TransAmt);
chkVal.Append(m.OpenAcctId);
chkVal.Append(m.OpenBankId);
chkVal.Append(m.FeeAmt);
chkVal.Append(m.FeeCustId);
chkVal.Append(m.FeeAcctId);
chkVal.Append(m.ServFee);
chkVal.Append(m.ServFeeAcctId);
chkVal.Append(m.RetUrl);
chkVal.Append(m.BgRetUrl);
chkVal.Append(m.MerPriv);
chkVal.Append(m.RespExt);
string msg = chkVal.ToString();
//私钥文件的位置(这里是放在了站点的根目录下)
string merKeyFile = AppDomain.CurrentDomain.BaseDirectory + Settings.Instance.PgPubk;
//需要指定提交字符串的长度
int len = Encoding.UTF8.GetBytes(msg).Length;
StringBuilder sbChkValue = new StringBuilder(256);
int ret = DllInterop.VeriSignMsg(merKeyFile, msg, msg.Length, m.ChkValue);
// LoggerHelper.Info("验签返回参数:" + ret.ToString());
StringBuilder str = new StringBuilder();
if (ret == 0)
{
//提现成功后，得多事务处理账户金额，流水及冻结金额等
if (m.RespCode == "000")
{
string cachename = m.OrdId + "Cash" + m.UsrCustId;
if (Settings.Instance.GeTThirdCache(cachename) == 0)
{
Settings.Instance.SetThirdCache(cachename);
B_usercenter BUC = new B_usercenter();
M_ReqExt mr = new M_ReqExt();
mr = FastJSON.ToObject <M_ReqExt>(m.RespExt.Replace("[", "").Replace("]", ""));
int CashOp = BUC.CashTran(m.OpenAcctId, m.OpenBankId, m.OrdId, m.RealTransAmt, m.UsrCustId, m.FeeAmt, mr.FeeObjFlag, mr.CashChl);
if (CashOp > 0)
{
string sql = "select registerid,username,mobile,UsrCustId,available_balance from hx_member_table where UsrCustId='" + m.UsrCustId + "'";
LoggerHelper.Info("后台审请取现成功短信sql:" + sql);
DataTable dt = DbHelper.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
//短信通知
//尊敬的#USERANEM#,您已成功提现#MONEY#元,账户余额#MONEY1#.请注意查收!【创利投】
MemberLogic _logic = new MemberLogic();
var ebt = _logic.GetSmsEmailEntity(1, 12); // 获取内容
string contxt = ebt.SEContext;
StringBuilder sbsms = new StringBuilder(contxt);
sbsms = sbsms.Replace("#USERANEM#", dt.Rows[0]["username"].ToString());
sbsms = sbsms.Replace("#MONEY#", m.TransAmt);
decimal amt = decimal.Parse(dt.Rows[0]["available_balance"].ToString()) - decimal.Parse(m.TransAmt);
sbsms = sbsms.Replace("#MONEY1#", amt.ToString());
string mobile = dt.Rows[0]["mobile"].ToString();
M_td_SMS_record psms = new M_td_SMS_record();
B_td_SMS_record osms = new B_td_SMS_record();
int smstype = (int)Enum.Parse(typeof(EnumSMSType), EnumSMSType.取现成功.ToString());
psms.phone_number = mobile;
psms.sendtime = DateTime.Now;
psms.senduserid = int.Parse(dt.Rows[0]["registerid"].ToString());
psms.smstype = smstype;
psms.smscontext = sbsms.ToString();
psms.orderid = SendSMS.Send(mobile, sbsms.ToString());
psms.vcode = "";
osms.Add(psms);
DateTime dti = DateTime.Now;
M_td_System_message pm = new M_td_System_message();
pm.MReg = int.Parse(dt.Rows[0]["registerid"].ToString());
pm.Mstate = 0;
pm.MTitle = "投现成功";
pm.MContext = sbsms.ToString();
pm.PubTime = dti;
pm.Mtype = 3;
B_usercenter.AddMessage(pm);
//即时提现，提现金额小于等于20万自动审核，高于20万人工审核
string cashChl = Enum.GetName(typeof(EnumCommon.E_hx_td_UserCash.EnumCashChl), (int)EnumCommon.E_hx_td_UserCash.EnumCashChl.IMMEDIATE);
if (mr.CashChl == cashChl && decimal.Parse(m.TransAmt) <= 200000)
{
string retUrl = Settings.Instance.ImagesDomain + "/admin/UserCash/RePostCashProcessing";
string bgRetUrl = Settings.Instance.ImagesDomain + "/admin/Thirdparty/BgCashProcessing";
BusinessLogicHelper.AutoCheckCash(m.UsrCustId, retUrl, bgRetUrl);
}
}
}
}
str1 = "RECV_ORD_ID_" + m.OrdId;
}
}
return(Content(str1));
}
public ActionResult AddOrEdit(int id = 0, User user = null)
{
_repository.UserName = User?.Identity?.Name;
var roles = DbHelper.GetAllRoles(_repository.UserName);
if (user == null)
{
return(View(new User {
AllRoles = roles?.Values.ToList() ?? new List <Role>()
}));
}
if (!DbHelper.UserIsAdmin(_repository.UserName) && user.Name != _repository.UserName)
{
return(View("Unauthorize"));
}
user.AllRoles = roles?.Values.ToList() ?? new List <Role>();
//var model = new UserViewModel
//{
// User = user,
// Roles = roles?.Values.ToList() ?? new List<Role>()
//};
if (!ModelState.IsValid)
{
return(View(user));
}
var res = user.Id != 0
? DbHelper.EditUser(user.Id, user.Name, user.OldPass, user.Password, _repository.UserName)
: DbHelper.AddUser(user.Name, user.Password, _repository.UserName);
if (!res)
{
ModelState.AddModelError("Db", "Пользователь не был добавлен! Повторите попытку или свяжитесь с администратором.");
return(View(user));
}
user = id == 0 ? DbHelper.GetUser(DbHelper.GetAllUsers(_repository.UserName).Values.Max(gid => gid.Id), _repository.UserName) : DbHelper.GetUser(id, _repository.UserName);
//user = DbHelper.GetUser(id, _repository.UserName);
user.AllRoles = roles?.Values.ToList() ?? new List <Role>();
var result = false;
//if (!Request.Form.AllKeys.Any(k => k.StartsWith("chk_")))
// return View("Saved");
if (roles != null && roles.Any())
{
var newChecked = Request.Form.AllKeys;
if (_repository.UserName == user.Name)
{
int index = newChecked.Length;
Array.Resize <string>(ref newChecked, index + 1);
newChecked[index] = $"chk_{user.Id}_{Constants.IdRoleAdmin}";
}
var toDelete = new List <UserRole>();
var toAdd = new List <UserRole>();
foreach (var role in roles.Values)
{
var inNewChecked = newChecked.Any(k => k == $"chk_{user.Id}_{role.Id}" || k == $"chk_0_{role.Id}");
var inOldChecked = user.Roles.Any(r => r.Id == role.Id);
if (inNewChecked && inOldChecked)
{
continue;
}
if (inNewChecked)
{
toAdd.Add(new UserRole
{
IdUser = user.Id,
IdRole = role.Id
});
}
else if (inOldChecked)
{
toDelete.Add(new UserRole
{
IdUser = user.Id,
IdRole = role.Id
});
}
}
if (toAdd.Any() || toDelete.Any())
{
result = DbHelper.UpdateUserRoles(toAdd, toDelete, _repository.UserName);
}
else
{
result = true;
}
}
else
{
return(View("Saved"));
}
if (result)
{
//user = DbHelper.GetUser(_repository.UserName);
//roles = DbHelper.GetAllRoles(_repository.UserName);
//if (users == null || roles == null)
// return View(new UserRolesViewModel());
//var modelNew = new UserViewModel
//{
// User = users.Values,
// Roles = roles.Values
//};
return(View("Saved"));
}
ModelState.AddModelError("Db", "Роли пользователя не были изменены! Повторите попытку или свяжитесь с администратором.");
return(View(user));
}
public void UpdateMenuFuncIcon(string busphid, string id, string name)
{
DbHelper.ExecuteScalar("update fg3_menufuncicon set id = " + id + ", name = '" + name + "' where busphid = " + busphid);
}
public ActionResult UserRoles(int id = 0)
{
_repository.UserName = User?.Identity?.Name;
if (!DbHelper.UserIsAdmin(_repository.UserName))
{
return(View("Unauthorize"));
}
var users = DbHelper.GetAllUsers(_repository.UserName);
var roles = DbHelper.GetAllRoles(_repository.UserName);
if (users == null || roles == null)
{
return(View(new UserRolesViewModel()));
}
var model = new UserRolesViewModel
{
Users = users.Values,
Roles = roles.Values
};
//if (Request.Form["submitbutton"] == null || Request.Form["submitbutton"] != "Сохранить")
// return View("UserRoles", model);
var result = false;
if (users.Any() && roles.Any())
{
var newChecked = Request.Form.AllKeys;
int index = newChecked.Length;
Array.Resize <string>(ref newChecked, index + 1);
newChecked[index] = $"chk_{DbHelper.GetUserId(_repository.UserName, _repository.UserName)}_{Constants.IdRoleAdmin}";
var toDelete = new List <UserRole>();
var toAdd = new List <UserRole>();
foreach (var user in users.Values)
{
foreach (var role in roles.Values)
{
var inNewChecked = newChecked.Any(k => k == $"chk_{user.Id}_{role.Id}");
var inOldChecked = user.Roles.Any(r => r.Id == role.Id);
if (inNewChecked && inOldChecked)
{
continue;
}
if (inNewChecked)
{
toAdd.Add(new UserRole
{
IdUser = user.Id,
IdRole = role.Id
});
}
else if (inOldChecked)
{
toDelete.Add(new UserRole
{
IdUser = user.Id,
IdRole = role.Id
});
}
}
}
result = DbHelper.UpdateUserRoles(toAdd, toDelete, _repository.UserName);
}
if (result)
{
users = DbHelper.GetAllUsers(_repository.UserName);
roles = DbHelper.GetAllRoles(_repository.UserName);
if (users == null || roles == null)
{
return(View(new UserRolesViewModel()));
}
var modelNew = new UserRolesViewModel
{
Users = users.Values,
Roles = roles.Values
};
return(View("UserRoles", modelNew));
}
else
{
ModelState.AddModelError("Db", "Роли пользователей не был изменены! Повторите попытку или свяжитесь с администратором.");
return(View("UserRoles", model));
}
}
public virtual bool TestTran2()
{
DbCommand cmd = this.SystemStore.GetSqlStringCommand("INSERT INTO test_trans([id],[name]) values(1,'SQL0001')");
return(DbHelper.ExecuteSql(cmd, this.SystemStore) == 1 ? true : false);
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (textDateFrom.errorProvider1.GetError(textDateFrom) != "" ||
textDateTo.errorProvider1.GetError(textDateTo) != ""
)
{
MessageBox.Show(Lan.g(this, "Please fix data entry errors first."));
return;
}
if (listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (listProv.SelectedIndices[0] == 0 && listProv.SelectedIndices.Count > 1)
{
MsgBox.Show(this, "You cannot select 'all' providers as well as specific providers.");
return;
}
DateTime dateFrom = PIn.Date(textDateFrom.Text);
DateTime dateTo = PIn.Date(textDateTo.Text);
string whereProv = "";
if (listProv.SelectedIndices[0] != 0)
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereProv += " WHERE (";
}
else
{
whereProv += "OR ";
}
whereProv += "patient.PriProv = "
+ POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
}
whereProv += ") ";
}
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = @"SET @pos=0;
SELECT @pos:=@pos+1 patCount,result.* FROM (SELECT dateFirstProc,patient.LName,patient.FName,"
+ DbHelper.Concat("referral.LName", "IF(referral.FName='','',',')", "referral.FName") + " refname,SUM(procedurelog.ProcFee) ";//\"$HowMuch\"";
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "$HowMuch";
}
else //Oracle needs quotes.
{
report.Query += "\"$HowMuch\"";
}
if (checkAddress.Checked)
{
report.Query += ",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip";
}
report.Query += @" FROM
(SELECT PatNum, MIN(ProcDate) dateFirstProc FROM procedurelog
WHERE ProcStatus=2 GROUP BY PatNum
HAVING dateFirstProc >= " + POut.Date(dateFrom) + " "
+ "AND DATE(dateFirstProc) <= " + POut.Date(dateTo) + " ) table1 "
+ @"INNER JOIN patient ON table1.PatNum=patient.PatNum
LEFT JOIN procedurelog ON patient.PatNum=procedurelog.PatNum AND procedurelog.ProcStatus=2
LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.IsFrom=1
AND refattach.ItemOrder=(SELECT MIN(ra.ItemOrder) FROM refattach ra WHERE ra.PatNum=refattach.PatNum AND ra.IsFrom=1)
LEFT JOIN referral ON referral.ReferralNum=refattach.ReferralNum "
+ whereProv;
report.Query += "GROUP BY patient.LName,patient.FName,patient.PatNum," + DbHelper.Concat("referral.LName", "IF(referral.FName='','',',')", "referral.FName");
if (checkAddress.Checked)
{
report.Query += ",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip ";
}
if (checkProd.Checked)
{
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "HAVING $HowMuch > 0 ";
}
else //Oracle needs quotes.
{
report.Query += "HAVING \"$HowMuch\" > 0 ";
}
}
report.Query += "ORDER BY dateFirstProc,patient.LName,patient.FName) result";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "New Patients";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if (listProv.SelectedIndices[0] == 0)
{
report.SubTitle.Add(Lan.g(this, "All Providers"));
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
else if (listProv.SelectedIndices.Count == 1)
{
report.SubTitle.Add(Lan.g(this, "Prov: ") + ProviderC.ListShort[listProv.SelectedIndices[0] - 1].GetLongDesc());
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
else
{
//I'm too lazy to build a description for multiple providers as well as ensure that it fits the space.
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
report.SetColumnPos(this, 0, "#", 40);
report.SetColumnPos(this, 1, "Date", 120);
report.SetColumnPos(this, 2, "Last Name", 210);
report.SetColumnPos(this, 3, "First Name", 300);
report.SetColumnPos(this, 4, "Referral", 380);
report.SetColumnPos(this, 5, "Production", 450, HorizontalAlignment.Right);
if (checkAddress.Checked)
{
report.SetColumnPos(this, 6, "Pref'd", 500);
report.SetColumnPos(this, 7, "Address", 570);
report.SetColumnPos(this, 8, "Add2", 630);
report.SetColumnPos(this, 9, "City", 680);
report.SetColumnPos(this, 10, "ST", 730);
report.SetColumnPos(this, 11, "Zip", 880); //off the right side
}
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
public DataTable GetFuncIconDtByPhid(string id)
{
return(DbHelper.GetDataTable("select * from fg3_funcicon where phid = " + id));
}
/// <summary>
/// 获取用户后台所有的（已启用的）权限
/// </summary>
/// <param name="companyTypes">用户公司类型集合</param>
/// <returns></returns>
public Model.SystemStructure.SysPermissionCategory GetAllPermissionByUser(Model.CompanyStructure.CompanyType[] companyTypes)
{
var strSql = new StringBuilder();
strSql.Append(" declare @Category int; ");
//个人中心（用户后台）
strSql.AppendFormat(" select @Category = [Id] from tbl_SysPermissionCategory where [TypeId] = {0} and IsEnable = '1'; ", 2);
strSql.Append(" select * from tbl_SysPermissionCategory where [Id] = @Category; ");
strSql.Append(" select * ");
strSql.Append(
" ,(select * FROM tbl_SysPermissionList list WHERE list.CategoryId = @Category AND list.IsEnable = '1' and list.ClassId = tbl_SysPermissionClass.Id for xml raw,root('Root')) as PermissionList ");
strSql.Append(" from tbl_SysPermissionClass where CategoryId = @Category and IsEnable = '1' ");
//营销工具 = 97, 系统设置 = 98, 我的网店 = 99
//营销工具、系统设置、我的网店 所有身份公司都有这些权限
strSql.Append(" and tbl_SysPermissionClass.Id in (97,98,99 ");
if (companyTypes != null && companyTypes.Any())
{
foreach (var t in companyTypes)
{
switch (t)
{
case Model.CompanyStructure.CompanyType.专线:
case Model.CompanyStructure.CompanyType.组团:
case Model.CompanyStructure.CompanyType.地接:
case Model.CompanyStructure.CompanyType.景区:
strSql.AppendFormat(" ,{0} ", (int)t);
break;
default:
continue;
}
}
}
strSql.Append(" ) ; ");
DbCommand dc = SystemStore.GetSqlStringCommand(strSql.ToString());
var model = new Model.SystemStructure.SysPermissionCategory();
using (IDataReader dr = DbHelper.ExecuteReader(dc, SystemStore))
{
if (dr.Read())
{
if (!dr.IsDBNull(dr.GetOrdinal("Id")))
{
model.Id = dr.GetInt32(dr.GetOrdinal("Id"));
}
if (!dr.IsDBNull(dr.GetOrdinal("TypeId")))
{
model.TypeId = dr.GetInt32(dr.GetOrdinal("TypeId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("CategoryName")))
{
model.CategoryName = dr.GetString(dr.GetOrdinal("CategoryName"));
}
if (!dr.IsDBNull(dr.GetOrdinal("SortId")))
{
model.SortId = dr.GetInt32(dr.GetOrdinal("SortId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("IsEnable")))
{
if (dr.GetString(dr.GetOrdinal("IsEnable")) == "1" || dr.GetString(dr.GetOrdinal("IsEnable")).ToLower() == "ture")
{
model.IsEnable = true;
}
}
}
dr.NextResult();
model.SysPermissionClass = new List <Model.SystemStructure.SysPermissionClass>();
Model.SystemStructure.SysPermissionClass classModel;
while (dr.Read())
{
classModel = new Model.SystemStructure.SysPermissionClass();
if (!dr.IsDBNull(dr.GetOrdinal("Id")))
{
classModel.Id = dr.GetInt32(dr.GetOrdinal("Id"));
}
if (!dr.IsDBNull(dr.GetOrdinal("CategoryId")))
{
classModel.CategoryId = dr.GetInt32(dr.GetOrdinal("CategoryId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("SortId")))
{
classModel.SortId = dr.GetInt32(dr.GetOrdinal("SortId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("ClassName")))
{
classModel.ClassName = dr.GetString(dr.GetOrdinal("ClassName"));
}
if (!dr.IsDBNull(dr.GetOrdinal("IsEnable")))
{
if (dr.GetString(dr.GetOrdinal("IsEnable")) == "1" || dr.GetString(dr.GetOrdinal("IsEnable")).ToLower() == "ture")
{
classModel.IsEnable = true;
}
}
if (!dr.IsDBNull(dr.GetOrdinal("PermissionList")))
{
var xRoot = XElement.Parse(dr.GetString(dr.GetOrdinal("PermissionList")));
var xRows = Common.Utility.GetXElements(xRoot, "row");
if (xRows != null && xRows.Any())
{
classModel.SysPermission = new List <Model.SystemStructure.SysPermission>();
foreach (var t in xRows)
{
if (t == null)
{
continue;
}
var tmp = new Model.SystemStructure.SysPermission
{
CategoryId = model.Id,
ClassId = classModel.Id,
Id = Common.Utility.GetInt(Common.Utility.GetXAttributeValue(t, "Id")),
PermissionName =
Common.Utility.GetXAttributeValue(t, "PermissionName"),
SortId =
Common.Utility.GetInt(Common.Utility.GetXAttributeValue(t,
"SortId"))
};
string strt = Common.Utility.GetXAttributeValue(t, "IsEnable");
if (!string.IsNullOrEmpty(strt) && (strt == "1" || strt.ToLower() == "true"))
{
tmp.IsEnable = true;
}
classModel.SysPermission.Add(tmp);
}
}
}
model.SysPermissionClass.Add(classModel);
}
}
return(model);
}
/// <summary>
/// 取得選單所需的所有節點資訊內容
/// </summary>
/// <returns></returns>
protected DataTable getNodeInfoData()
{
if (PageViewState["_NodeInfoData"] == null)
{
DbHelper db = new DbHelper(ucDBName);
string strSQL = string.Format("Select * From viewNodeInfo Where IsEnabled = 'Y' And CultureCode = '{0}' ", Util.getUICultureCode());
//過濾 ChkGrantIDList
if (ucChkGrantIDList != null && ucChkGrantIDList.Count() > 0)
{
strSQL += string.Format(" And ChkGrantID in ('','{0}') ", Util.getStringJoin(ucChkGrantIDList, "','"));
}
DataTable dt = db.ExecuteDataSet(strSQL).Tables[0];
PageViewState["_NodeInfoData"] = dt;
}
else
{
//利用 PageViewState["_NodeInfoDataStatus"] 進行程式邏輯判斷 [W:等待檢查 Y:已檢查成功 N:已檢查但有Error]
if (PageViewState["_NodeInfoDataStatus"] != null && ((string)PageViewState["_NodeInfoDataStatus"]) == "W")
{
//若使用了[ucNodeInfoData]屬性設定了自訂節點資料，則使用前需檢查資料欄位是否合理
_IsAjax = false; //自動關閉Ajax模式，以提昇效能
PageViewState["_NodeInfoDataStatus"] = "Y";
DataTable dtNode = (DataTable)PageViewState["_NodeInfoData"];
if (dtNode != null && dtNode.Rows.Count > 0)
{
//檢查必要欄位(不存在就觸發Error並清空 PageViewState["_NodeInfoData"])
for (int i = 0; i < _NodeInfoData_NeedFldList.Count(); i++)
{
if ((string)PageViewState["_NodeInfoDataStatus"] == "Y")
{
if (!dtNode.Columns.Contains(_NodeInfoData_NeedFldList[i]))
{
PageViewState["_NodeInfoDataStatus"] = "N";
PageViewState["_NodeInfoData"] = null;
}
}
}
//處理非必要欄位(不存在就自動新增)
if ((string)PageViewState["_NodeInfoDataStatus"] == "Y")
{
for (int i = 0; i < _NodeInfoData_OptiFldList.Count(); i++)
{
if (!dtNode.Columns.Contains(_NodeInfoData_OptiFldList[i]))
{
dtNode.Columns.Add(_NodeInfoData_OptiFldList[i]);
PageViewState["_NodeInfoData"] = dtNode;
}
}
}
}
else
{
PageViewState["_NodeInfoData"] = null;
}
}
}
return((DataTable)(PageViewState["_NodeInfoData"]));
}
/// <summary>
/// 更新发送记录.
/// </summary>
/// <param name="ent">The ent.</param>
public void UpdateRecord(SmsRecordEntity ent)
{
var sql = "update hx_td_SMS_record set orderid=" + ent.orderid + ",hits=hits+1 where sms_record_id=" + ent.sms_record_id;
DbHelper.ExecuteSql(sql);
}
/// <summary>
/// 重设 IPAddress 限制，
/// 2015-12-21 吉日嘎拉 历史数据不应该被丢失才对
/// </summary>
/// <param name="userId">用户、接口主键</param>
/// <returns>影响行数</returns>
public int ResetIPAddress(string userId)
{
int result = 0;
// 把缓存里的先清理掉
ResetIPAddressByCache(userId);
// todo 吉日嘎拉 这个操作应该增加个操作日志、谁什么时间，把什么数据删除了？ 把登录日志按操作日志、系统日志来看待？
string commandText = string.Empty;
commandText = "UPDATE " + BaseParameterEntity.TableName
+ " SET " + BaseParameterEntity.FieldDeletionStateCode + " = 1 "
+ " , " + BaseParameterEntity.FieldEnabled + " = 0 "
+ " WHERE " + BaseParameterEntity.FieldCategoryCode + " = " + DbHelper.GetParameter(BaseParameterEntity.FieldCategoryCode)
+ " AND " + BaseParameterEntity.FieldParameterId + " = " + DbHelper.GetParameter(BaseParameterEntity.FieldParameterId);
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
dbParameters.Add(DbHelper.MakeParameter(BaseParameterEntity.FieldCategoryCode, "IPAddress"));
dbParameters.Add(DbHelper.MakeParameter(BaseParameterEntity.FieldParameterId, userId));
result = this.DbHelper.ExecuteNonQuery(commandText, dbParameters.ToArray());
return(result);
}
public static bool Update(SE_DecorativePattern model)
{
bool result = false;
string sql = @" UPDATE Configuration.dbo.SE_DecorativePatternConfig SET Name=@Name,Brand=@Brand, Flower=@Flower, ImageUrl1=@ImageUrl1, ImageUrl2=@ImageUrl2, ImageUrl3=@ImageUrl3,Description=@Description,ArticleID=@ArticleID,ArticleTitle=@ArticleTitle,UpdateDT=GETDATE(),ShareParameter=@ShareParameter WHERE ID=@ID ";
var db = DbHelper.CreateDefaultDbHelper();
try
{
db.BeginTransaction();
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@ID", model.ID.ToString());
cmd.Parameters.AddWithValue("@Name", model.Name);
cmd.Parameters.AddWithValue("@Brand", model.Brand);
cmd.Parameters.AddWithValue("@Flower", model.Flower);
cmd.Parameters.AddWithValue("@ImageUrl1", model.ImageUrl1);
cmd.Parameters.AddWithValue("@ImageUrl2", model.ImageUrl2);
cmd.Parameters.AddWithValue("@ImageUrl3", model.ImageUrl3);
cmd.Parameters.AddWithValue("@Description", model.Description);
cmd.Parameters.AddWithValue("@ArticleID", model.ArticleID);
cmd.Parameters.AddWithValue("@ArticleTitle", model.ArticleTitle);
cmd.Parameters.AddWithValue("@ShareParameter", model.ShareParameter);
db.ExecuteNonQuery(cmd);
sql = @"DELETE FROM Configuration.dbo.SE_DecorativePatternDetailConfig WHERE FK_DecorativePattern=@FK_DecorativePattern ";
cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@FK_DecorativePattern", model.ID.ToString());
db.ExecuteNonQuery(cmd);
sql = @"INSERT INTO configuration.dbo.SE_DecorativePatternDetailConfig
( FK_DecorativePattern ,
ArticleID ,
ArticleTitle,
Image,
[Description],
OrderBy
)
VALUES ( @FK_DecorativePattern , -- FK_DecorativePattern - uniqueidentifier
@ArticleID , -- ArticleID - int
@ArticleTitle , -- ArticleTitle - nvarchar(200)
@Image,
@Description,
@OrderBy
)";
foreach (var item in model.Items)
{
cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@FK_DecorativePattern", model.ID.ToString());
cmd.Parameters.AddWithValue("@ArticleID", item.ArticleID);
cmd.Parameters.AddWithValue("@ArticleTitle", item.ArticleTitle);
cmd.Parameters.AddWithValue("@Image", item.Image);
cmd.Parameters.AddWithValue("@Description", item.Description);
cmd.Parameters.AddWithValue("@OrderBy", item.OrderBy);
db.ExecuteNonQuery(cmd);
}
db.Commit();
result = true;
}
catch (Exception e)
{
db.Rollback();
}
finally
{
db.Dispose();
}
return(result);
}
public CPWebApiBaseReturnEntity UpdateGridData([FromBody] UpdateGridDataInput input)
{
base.SetHeader();
CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
if (this.CheckUserIden(input.CurUserId, input.CurUserIden) == false)
{
re.Result = false;
re.ErrorMsg = "系统检测到非法获取数据，请传入正确的用户会话Key与用户Id参数！";
return(re);
}
try
{
CPGrid grid = CPGridEngine.Instance(input.CurUserId).GetGrid(input.GridCode, false, false);
DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());
StringBuilder sb = new StringBuilder();
string[] pkArray = grid.PKFieldName.Split(',');
input.Items.ForEach(t =>
{
string strSql = "";
strSql = "UPDATE " + grid.MainTableName + " SET ";
for (int i = 0; i < t.FieldNamCol.Count; i++)
{
string sValue = t.FieldValueCol[i];
if (string.IsNullOrEmpty(sValue) == false)
{
sValue = sValue.Replace("'", "''");
}
else
{
sValue = "";
}
if (i == 0)
{
strSql += t.FieldNamCol[i] + "='" + sValue + "'";
}
else
{
strSql += "," + t.FieldNamCol[i] + "='" + sValue + "'";
}
}
string[] dataPKArray = t.DataPK.Split(',');
for (int i = 0; i < pkArray.Length; i++)
{
if (i == 0)
{
strSql += " WHERE " + pkArray[i] + "='" + dataPKArray[i] + "'";
}
else
{
strSql += " AND " + pkArray[i] + "='" + dataPKArray[i] + "'";
}
}
if (sb.Length > 0)
{
sb.Append(";");
}
sb.Append(strSql);
});
if (sb.Length > 0)
{
dbHelper.ExecuteNonQuery(sb.ToString());
}
re.Result = true;
return(re);
}
catch (Exception ex)
{
re.Result = false;
re.ErrorMsg = ex.Message.ToString();
return(re);
}
}
public async Task InsertProductAsync(Product product)
{
product.Id = Convert.ToInt32(await DbHelper.InsertAsync <Product>(product));
}
