protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString.AllKeys.Length > 0)
{
DataBaseConfig dt = new DataBaseConfig();
if (Request.QueryString["op"] == "getcustomer")
{
dt.oCommand = new SqlCommand("select * from Customer where Id=" + Request.QueryString["Id"]);
dt.oDataReader = dt.ExecuteReader(dt.oCommand);
if (dt.oDataReader.Read())
{
//{"id_customer":7,"tax_office":"elazığ vergi bürosu","tax_number":1231231}
Response.Write(dt.oDataReader["Id"] + ":" + dt.oDataReader["Tax_Office"] + ":" + dt.oDataReader["Tax_Number"]);
}
}
else if (Request.QueryString["op"] == "getproductprice")
{
dt.oCommand = new SqlCommand("select * from Product where Id=" + Request.QueryString["Id"]);
dt.oDataReader = dt.ExecuteReader(dt.oCommand);
if (dt.oDataReader.Read())
{
//{ "id_product":6,"name":"Product1","price":"120.0000","tax_rate":"0.0500"}
Response.Write(dt.oDataReader["Id"] + ":" + dt.oDataReader["Name"] + ":" + dt.oDataReader["Price"] + ":" + dt.oDataReader["Tax_Rate"]);
}
}
}
}
public async Task Update(T entity, string updateSql)
{
using (IDbConnection conn = DataBaseConfig.Connection())
{
await conn.ExecuteAsync(updateSql, entity);
}
}
public static ResultMsg Execute(IDictionary <string, DataTable> dics)
{
var resultMsg = new ResultMsg();
using (var conn = DataBaseConfig.GetConnection())
{
var trans = conn.BeginTransaction();
var sqlBulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepNulls, trans)
{
BulkCopyTimeout = 600
};
try
{
foreach (var dic in dics)
{
sqlBulk.DestinationTableName = dic.Key;
sqlBulk.WriteToServer(dic.Value);
}
trans.Commit();
resultMsg.MsgState = true;
}
catch (Exception ex)
{
trans.Rollback();
resultMsg.MsgState = false;
resultMsg.MsgData = "操作失败,详细原因：" + ex.Message;
}
return(resultMsg);
}
}
public async Task Delete(Guid Id, string deleteSql)
{
using (IDbConnection conn = DataBaseConfig.Connection())
{
await conn.ExecuteAsync(deleteSql, new { Id = Id });
}
}
public async Task Insert(T entity, string insertSql)
{
using (IDbConnection conn = DataBaseConfig.Connection())
{
await conn.ExecuteAsync(insertSql, entity);
}
}
public PageData <News> GetNewsList(int channelId, int pageIndex, int pageSize)
{
int startIndex = (pageIndex - 1) * pageSize;
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT N.*,C.name as channelName FROM News N");
sql.Append(" left join Channels C on N.channelId=C.channelId");
if (channelId != -1)
{
sql.Append(" where N.channelId=@channelId");
}
sql.Append(" ORDER BY N.id ");
sql.Append(" OFFSET (@startIndex * @pageSize) ROWS");
sql.Append(" FETCH NEXT @pageSize ROWS ONLY");
var dataList = conn.Query <News>(sql.ToString(), new { channelId, startIndex, pageSize }).ToList();
StringBuilder sqlCount = new StringBuilder();
sqlCount.Append(" select count(1) from News N");
sqlCount.Append(" left join Channels C on N.channelId=C.channelId ");
if (channelId != -1)
{
sql.Append(" and N.channelId=@channelId");
}
var count = conn.QueryFirstOrDefault <int>(sqlCount.ToString(), new { channelId });
return(new PageData <News>(dataList, pageIndex, pageSize, count));
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request.QueryString.AllKeys.Length > 0)
{
if (Request.QueryString["Id"] != null)
{
DataBaseConfig db = new DataBaseConfig();
db.oCommand = new SqlCommand("Select * from Customer Where Id = " + Request.QueryString["Id"]);
db.oDataReader = db.ExecuteReader(db.oCommand);
if (db.oDataReader.Read())
{
Firstname.Text = db.oDataReader["Firstname"].ToString();
Lastname.Text = db.oDataReader["Lastname"].ToString();
Companyname.Text = db.oDataReader["Companyname"].ToString();
Addres.Text = db.oDataReader["Addres"].ToString();
Email.Text = db.oDataReader["Email"].ToString();
Phone.Text = db.oDataReader["Phone"].ToString();
Tax_Number.Text = db.oDataReader["Tax_Number"].ToString();
Tax_Office.Text = db.oDataReader["Tax_Office"].ToString();
}
}
}
}
}
/// <summary>
/// 通过角色名获取所有ASM
/// </summary>
/// <returns></returns>
public async Task <List <Users> > GetASMByRole()
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
return(await Task.Run(() => conn.Query <Users>("select * from SIDE_VW_GetASMByRole", null).ToList()));
}
}
private static string GetWebserviceDatabaseConfig()
{
var webDBconfigService = new WebDBConfigService();
var dbAccount = webDBconfigService.WS_AUTHENDB_DBConfig(DBConfig.ProjectID);
if (String.IsNullOrEmpty(dbAccount.Status) || dbAccount.Status != "0000")
{
String strError = "Project ID : " + DBConfig.ProjectID + " >> " + dbAccount.Status + " : " + dbAccount.Detail;
}
var dbConfig = new DataBaseConfig();
dbConfig.DecrptedServername = AuthenDecrypt.Decrypt.textDecrpyt(dbAccount.ServerName.ToString());
dbConfig.DatabaseName = DBConfig.POPDBname;
dbConfig.DecryptedUsername = AuthenDecrypt.Decrypt.textDecrpyt(dbAccount.UserName.ToString());
dbConfig.DecryptedPassword = AuthenDecrypt.Decrypt.textDecrpyt(dbAccount.Password.ToString());
var connectionString = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};Max Pool Size=400;Connect Timeout=600"
, dbConfig.DecrptedServername
, dbConfig.DatabaseName
, dbConfig.DecryptedUsername
, dbConfig.DecryptedPassword);
return(connectionString);
}
/// <summary>
/// 修改一个用户
/// </summary>
/// <param name="entity">要修改的用户</param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public Task <Boolean> Update(User entity, String connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string updateSql = @"UPDATE dbo.auth_User
SET TenantId = @tenantid,
Name = @name,
Password = @password,
SecurityStamp = @securitystamp,
FullName = @fullname,
Surname = @surname,
PhoneNumber = @phonenumber,
IsPhoneNumberConfirmed = @isphonenumberconfirmed,
EmailAddress = @emailaddress,
IsEmailConfirmed = @isemailconfirmed,
EmailConfirmationCode = @emailconfirmationcode,
IsActive = @isactive,
PasswordResetCode = @passwordresetcode,
LastLoginTime = @lastlogintime,
IsLockoutEnabled = @islockoutenabled,
AccessFailedCount = @accessfailedcount,
LockoutEndDateUtc = @lockoutenddateutc
WHERE Id = @Id";
return(Task.FromResult <Boolean>(conn.Execute(updateSql, entity) > 0));
}
}
/// <summary>
/// 获取所有用户
/// </summary>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public Task <IEnumerable <User> > GetAllEntity(String connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT Id ,
TenantId ,
Name ,
Password ,
SecurityStamp ,
FullName ,
Surname ,
PhoneNumber ,
IsPhoneNumberConfirmed ,
EmailAddress ,
IsEmailConfirmed ,
EmailConfirmationCode ,
IsActive ,
PasswordResetCode ,
LastLoginTime ,
IsLockoutEnabled ,
AccessFailedCount ,
LockoutEndDateUtc
FROM dbo.auth_User;";
return(Task.FromResult <IEnumerable <User> >(conn.Query <User>(querySql)));
}
}
/// <summary>
/// 根据主键Id获取一个用户
/// </summary>
/// <param name="id">主键Id</param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public Task <User> GetEntityById(Int64 id, String connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT Id ,
TenantId ,
Name ,
Password ,
SecurityStamp ,
FullName ,
Surname ,
PhoneNumber ,
IsPhoneNumberConfirmed ,
EmailAddress ,
IsEmailConfirmed ,
EmailConfirmationCode ,
IsActive ,
PasswordResetCode ,
LastLoginTime ,
IsLockoutEnabled ,
AccessFailedCount ,
LockoutEndDateUtc
FROM dbo.auth_User
WHERE Id = @Id";
return(Task.FromResult <User>(conn.QueryFirstOrDefault <User>(querySql, new { Id = id })));
}
}
/// <summary>
/// 修改
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Update(PublishHouse model)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(null))
{
return(conn.Update(model));
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public long Create(PublishHouse model)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(null))
{
return(conn.Insert(model));
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Create(Books model)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
return(conn.Insert(model) > 0);
}
}
public async Task <List <MD_WHS_Channel> > GetMD_WHS_Channel()
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
return(await Task.Run(() => conn.Query <MD_WHS_Channel>("select distinct [WHS_Channel] from SIDE_MD_WHS_Channel").ToList()));
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Delete(Books model)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
return(conn.Delete(model));
}
}
public async Task <List <MD_Product_Channel> > GetMD_Product_Channel()
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
return(await Task.Run(() => conn.Query <MD_Product_Channel>("select ID,CodeName from SIDE_MD_Product_Channel").ToList()));
}
}
public void CreateEasyJobTable(EntityDB.IDatabaseService db)
{
bool exists = true;
try
{
db.ExecSqlString("select * from __wayeasyjob");
}
catch
{
exists = false;
}
if (!exists)
{
db.ExecSqlString("CREATE TABLE [__wayeasyjob](contentConfig varchar(1000) NOT NULL)");
var dbconfig = new DataBaseConfig();
try
{
dbconfig.LastUpdatedID = Convert.ToInt32(db.ExecSqlString("select lastID from __EasyJob"));
}
catch
{
}
db.ExecSqlString("insert into __wayeasyjob (contentConfig) values (@p0)", dbconfig.ToJsonString());
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public int AddMD_POS_Type(MD_POS_Type model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSqlupdate = new StringBuilder(); // 修改有限顺序序号
strSqlupdate.Append(" if exists(select * from SIDE_MD_POS_Type where POS_Channel = @POS_Channel and POS_Segment = @POS_Segment and Type_Sequence = @Type_Sequence)");
strSqlupdate.Append(" begin");
strSqlupdate.Append(" update SIDE_MD_POS_Type set Type_Sequence = Type_Sequence + 1 where POS_Channel = @POS_Channel and POS_Segment = @POS_Segment and Type_Sequence>= @Type_Sequence");
strSqlupdate.Append(" end ");
strSql.Append("insert into SIDE_MD_POS_Type( POS_Channel, POS_Segment, POS_Type, Type_Sequence, Type_Status )");
strSql.Append(" values ( @POS_Channel, @POS_Segment, @POS_Type, @Type_Sequence, @Type_Status )");
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@POS_Channel", model.POS_Channel, DbType.String, ParameterDirection.Input);
parameters.Add("@POS_Segment", model.POS_Segment, DbType.String, ParameterDirection.Input);
parameters.Add("@POS_Type", model.POS_Type, DbType.String, ParameterDirection.Input);
parameters.Add("@Type_Sequence", model.Type_Sequence, DbType.Int32, ParameterDirection.Input);
parameters.Add("@Type_Status", 1, DbType.String, ParameterDirection.Input);
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
conn.Execute(strSqlupdate.ToString(), parameters);
return(conn.Execute(strSql.ToString(), parameters));
}
}
public void CreateEasyJobTable(EntityDB.IDatabaseService db)
{
bool exists = true;
try
{
db.ExecSqlString("select * from __wayeasyjob");
}
catch (Exception ex)
{
exists = false;
}
if (!exists)
{
db.ExecSqlString("CREATE TABLE [__wayeasyjob](contentConfig TEXT NOT NULL)");
var dbconfig = new DataBaseConfig();
try
{
dbconfig.LastUpdatedID = Convert.ToInt32(db.ExecSqlString("select lastID from __EasyJob"));
}
catch
{
}
db.ExecSqlString("insert into __wayeasyjob (contentConfig) values (@p0)", dbconfig.ToJsonString());
}
//try
//{
// db.ExecSqlString("CREATE TABLE [] ([lastID] integer NOT NULL)");
// db.ExecSqlString("insert into (lastID) values (0)");
//}
//catch
//{
//}
}
public int AddMD_BulkShipTo(MD_BulkShipTo model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into SIDE_MD_BulkShipTo(BRANCH_EN,BRANCH_CN,BLR,Province,City,StoreFormat,WHS_Channel,Bulk_Ship_to_Code_Core,Bulk_Ship_to_Code_Originals,Bulk_Ship_to_Code_Neo,Bulk_Ship_to_Code_Kids )");
strSql.Append(" values (@BRANCH_EN,@BRANCH_CN,@BLR,@Province,@City,@StoreFormat,@WHS_Channel,@Bulk_Ship_to_Code_Core,@Bulk_Ship_to_Code_Originals,@Bulk_Ship_to_Code_Neo,@Bulk_Ship_to_Code_Kids )");
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@BRANCH_EN", model.BRANCH_EN, DbType.String, ParameterDirection.Input);
parameters.Add("@BRANCH_CN", model.BRANCH_CN, DbType.String, ParameterDirection.Input);
parameters.Add("@WHS_Channel", model.WHS_Channel, DbType.String, ParameterDirection.Input);
parameters.Add("@BLR", model.BLR, DbType.String, ParameterDirection.Input);
parameters.Add("@City", model.City, DbType.String, ParameterDirection.Input);
parameters.Add("@Province", model.Province, DbType.String, ParameterDirection.Input);
parameters.Add("@StoreFormat", model.StoreFormat, DbType.String, ParameterDirection.Input);
parameters.Add("@Bulk_Ship_to_Code_Core", model.Bulk_Ship_to_Code_Core, DbType.String, ParameterDirection.Input);
parameters.Add("@Bulk_Ship_to_Code_Originals", model.Bulk_Ship_to_Code_Originals, DbType.String, ParameterDirection.Input);
parameters.Add("@Bulk_Ship_to_Code_Neo", model.Bulk_Ship_to_Code_Neo, DbType.String, ParameterDirection.Input);
parameters.Add("@Bulk_Ship_to_Code_Kids", model.Bulk_Ship_to_Code_Kids, DbType.String, ParameterDirection.Input);
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
return(conn.Execute(strSql.ToString(), parameters));
}
}
public async Task <T> Detail(Guid Id, string detailSql)
{
using (IDbConnection conn = DataBaseConfig.Connection())
{
return(await conn.QueryFirstOrDefault(detailSql, new { Id = Id }));
}
}
public async Task PutUser(Users entity)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
IDbTransaction transaction = conn.BeginTransaction();
try
{
string deleteSql = "DELETE FROM [dbo].[SIDE_PLM_EmployeeRole] WHERE EMPLOYEE=@ID";
await conn.ExecuteAsync(deleteSql, new { ID = entity.ID }, transaction);
foreach (var role in entity.Roles)
{
string insertSql = @"INSERT INTO SIDE_PLM_EmployeeRole(EMPLOYEE,ROLE) SELECT
@ID,ID FROM SIDE_PLM_Role WHERE NameCN=@RoleName";
await conn.ExecuteAsync(insertSql, new { ID = entity.ID, RoleName = role.RoleName }, transaction);
}
transaction.Commit();
}
catch (Exception exception)
{
transaction.Rollback();
}
}
}
public async Task <List <T> > Select(string selectSql)
{
using (IDbConnection conn = DataBaseConfig.Connection())
{
return(await Task.Run(() => conn.Query <T>(selectSql).ToList()));
}
}
public async Task <Users> GetUserAndRoles(int Id)
{
Users userList = new Users();
string selectSql = @"SELECT id,ntAccount,name,RoleName,RoleNameShort
FROM SIDE_PLM_V_Users
WHERE ID=@Id
";
using (IDbConnection connection = DataBaseConfig.GetSqlConnection())
{
var lookUp = new Dictionary <int, Users>();
userList = connection.Query <Users, Roles, Users>(selectSql,
(user, role) =>
{
if (!lookUp.TryGetValue(user.ID, out Users u))
{
lookUp.Add(user.ID, u = user);
u = user;
}
u.Roles.Add(role);
return(u);
}, splitOn: "RoleName", param: new { Id = Id }
).First();
return(userList);
}
}
public async Task <List <object> > GetCustomerGroupByGroup(string group)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
return(await Task.Run(() => conn.Query <object>("select distinct [Customer] from SIDE_PLM_BLR_Customer_Group where [group]=@group", new { group }).ToList()));
}
}
public async Task <Users> GetUserAndRolesByAccount(string account)
{
account = "ap\\" + account;
Users userList = new Users();
string selectSql = @"SELECT id,ntAccount,name,RoleName,RoleNameShort,RoleID
FROM SIDE_PLM_V_Users
WHERE ntAccount=@ntAccount
";
using (IDbConnection connection = DataBaseConfig.GetSqlConnection())
{
var lookUp = new Dictionary <int, Users>();
var result = await connection.QueryAsync <Users, Roles, Users>(selectSql,
(user, role) =>
{
if (!lookUp.TryGetValue(user.ID, out Users u))
{
lookUp.Add(user.ID, u = user);
u = user;
}
u.Roles.Add(role);
return(u);
}, splitOn : "RoleName", param : new { ntAccount = account }
);
return(result.First());
}
}
//根据Year Season POSCode 查询是否存在审批记录
public async Task <bool> IsUpdateBLR(string YEAR, string SEASON, string POSCode)
{
string sql = @" select * from SIDE_PLM_BLR_POSReview
where [Year] = @YEAR and [Season] = @SEASON and [POSCode] = @POSCode";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
BLR_POSReview result = await Task.Run(() => conn.QueryFirstOrDefault <BLR_POSReview>(sql, new { YEAR, SEASON, POSCode }));
if (result == null)
{
//未提交
return(true);
}
else
{
if (result.PosState == "Submitted By AS" || result.PosState == "Approved By ASM")
{
return(false);
}
else
{
return(true);
}
}
}
}
public async Task ExcuteSQL(string excuteSql)
{
using (MySqlConnection conn = DataBaseConfig.GetSqlConnection())
{
await conn.ExecuteAsync(excuteSql);
}
}
