public Boolean Execute()
{
bool bReturn = true;
string strMessage = "";
try
{
Dbconn conn_DRP_EC = new Dbconn("DRP_EC");
string strDepotID = "";
DataTable dt = conn_DRP_EC.GetDataTable(@"
SELECT [CfgValue]
FROM [DRP_EC].[dbo].[Config]
WHERE Type='StockSync_DRPToWCS'
AND CfgName='Depot'
");
for (int i = 0; i < dt.Rows.Count; i++)
{
if (strDepotID != "")
{
strDepotID += ",";
}
strDepotID += "'" + dt.Rows[i]["CfgValue"].ToString() + "'";
}
string strSQL = @"
SELECT clothingid AS SKU ,
SUM(k_num) AS totalInventory
FROM dbo.j_stock WITH(NOLOCK)
WHERE depotid in (" + strDepotID + @")
GROUP BY clothingid
HAVING SUM(k_num)>0
";
DataTable dt1 = m_conn.GetDataTable(strSQL);
conn_DRP_EC.BeginTransaction();
conn_DRP_EC.InsertBYSQLBC("Put_SkuStock", dt1);
conn_DRP_EC.CommitTransaction();
return bReturn;
}
catch (Exception ex)
{
m_conn.RollbackTransaction();
bReturn = false;
strMessage = ex.ToString();
throw ex;
}
finally
{
//HZY.COM.Common.Log.WirteLogWS(bReturn.ToString() + strMessage,null);
}
}
/// <summary>
/// 供销售日报使用
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn_PLM_AS = new Dbconn("F22GP_ForSalesPlan");
DataSet ds = conn_PLM_AS.GetDataSet(
@" SELECT 94 AS Env_ID , --HZY_Sale_Report
null AS VexSSONewID ,
b.userid AS App_UserName ,
RIGHT(sys.fn_VarBinToHexStr(hashbytes('MD5', CONVERT(VARCHAR(100), b.password))), 32) AS App_Password ,
b.password AS App_PasswordSrc ,
b.username AS App_UserName_CN ,
GETDATE() Chang_Password_Time ,
1 Checked ,
'system' Check_User ,
GETDATE() Check_Time ,
GETDATE() Createtime ,
GETDATE() Updatetime
FROM j_depot a ,
j_user b
WHERE a.depotid = b.depotid
AND a.m_type = 11
");
Dbconn connMDM = new Dbconn("VexSSO");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("MDM_System_User");
connMDM.TableLock(tableList);
//删除数据
connMDM.ExcuteQuerryByTran(@"DELETE FROM MDM_System_User WHERE ENV_ID=94");
connMDM.InsertBYSQLBC("MDM_System_User", ds.Tables[0]);
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
/// <summary>
/// 编辑系统属性值信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM");
try
{
string strSystem_ID = m_request.Tables[0].Rows[0]["System_ID"].ToString();
string strSystem_Code = m_request.Tables[0].Rows[0]["System_Code"].ToString();
DataTable dt_Add = new DataTable();
dt_Add.Columns.Add("System_ID");
dt_Add.Columns.Add("System_Code");
dt_Add.Columns.Add("Property_ID");
dt_Add.Columns.Add("Property_Text");
for (int i = 2; i < m_request.Tables[0].Columns.Count; i++)
{
string strValue = m_request.Tables[0].Rows[0][i].ToString();
if (strValue != "")
{
dt_Add.Rows.Add(new object[] { strSystem_ID, strSystem_Code, m_request.Tables[0].Columns[i].ToString(),strValue});
}
}
ArrayList listTable = new ArrayList();
listTable.Add("MDM_System_Property_info");
conn.BeginTransaction();
conn.TableLock(listTable);
string strWhere = " System_ID=" + strSystem_ID;
conn.Delete("MDM_System_Property_info", strWhere);
conn.InsertBYSQLBC("MDM_System_Property_info", dt_Add);
conn.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
public bool Execute()
{
string strEnv_ID = "";
string strEnv_SN = "HZY_CMT";
Dbconn conn_HZY_SBS = new Dbconn("F22GP_ForSalesPlan");
DataSet ds = conn_HZY_SBS.GetDataSet(
@" SELECT
--98 AS Env_ID ,
NULL AS VexSSONewID ,
b.userid AS App_UserName ,
RIGHT(sys.fn_VarBinToHexStr(hashbytes('MD5', CONVERT(VARCHAR(100), b.password))), 32) AS App_Password ,
b.password AS App_PasswordSrc ,
CASE WHEN b.dptype <> 0 THEN ISNULL(a.depotid,'') + '_' + d_name ELSE b.username END AS App_UserName_CN ,
GETDATE() Chang_Password_Time ,
1 Checked ,
'system' Check_User ,
GETDATE() Check_Time ,
GETDATE() Createtime ,
GETDATE() Updatetime,
0 as oprationFlag
FROM
j_user b
LEFT JOIN j_depot a ON a.depotid = b.depotid
");
Dbconn connMDM = new Dbconn("VexSSO");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("MDM_System_User");
connMDM.TableLock(tableList);
strEnv_ID = connMDM.GetDataTableFirstValue("SELECT [Env_ID] FROM [MDM_System_Env] WHERE Env_SN='" + strEnv_SN + "'").ToString();
DataTable dtMDM = connMDM.GetDataTable(@"
SELECT [App_UserID]
,[Env_ID]
,[VexSSONewID]
,[App_UserName]
,[App_Password]
,[App_PasswordSrc]
,[App_UserName_CN]
,[Chang_Password_Time]
,[Checked]
,[Check_User]
,[Check_Time]
,[Createtime]
,[Updatetime]
FROM [dbo].[MDM_System_User]
WHERE Env_ID='" + strEnv_ID + @"'
");
DataTable dtMDMUpdate = dtMDM.Clone();
DataTable dtMDMInsert = dtMDM.Clone();
DataTable dtMDMDelete = dtMDM.Clone();
for (int i = 0; i < dtMDM.Rows.Count; i++)
{
string strApp_UserName = dtMDM.Rows[i]["App_UserName"].ToString();
DataRow[] dr = ds.Tables[0].Select("App_UserName='******'");
if (dr.Length > 0)
{
dtMDM.Rows[i]["App_Password"] = dr[0]["App_Password"];
dtMDM.Rows[i]["App_PasswordSrc"] = dr[0]["App_PasswordSrc"];
dtMDM.Rows[i]["App_UserName_CN"] = dr[0]["App_UserName_CN"];
dtMDMUpdate.ImportRow(dtMDM.Rows[i]);
dr[0]["oprationFlag"] = "1";
}
else
{
dtMDMDelete.ImportRow(dtMDM.Rows[i]);
}
}
DataRow[] drInsert = ds.Tables[0].Select("oprationFlag=0");
for (int i = 0; i < drInsert.Length; i++)
{
DataRow dr = dtMDMInsert.NewRow();
dr["Env_ID"] = strEnv_ID;
dr["VexSSONewID"] = drInsert[i]["VexSSONewID"];
dr["App_UserName"] = drInsert[i]["App_UserName"];
dr["App_Password"] = drInsert[i]["App_Password"];
dr["App_PasswordSrc"] = drInsert[i]["App_PasswordSrc"];
dr["App_UserName_CN"] = drInsert[i]["App_UserName_CN"];
dr["Chang_Password_Time"] = drInsert[i]["Chang_Password_Time"];
dr["Checked"] = drInsert[i]["Checked"];
dr["Check_User"] = drInsert[i]["Check_User"];
dr["Check_Time"] = drInsert[i]["Check_Time"];
dr["Createtime"] = drInsert[i]["Createtime"];
dr["Updatetime"] = drInsert[i]["Updatetime"];
dtMDMInsert.Rows.Add(dr);
}
if (dtMDMDelete.Rows.Count > 0)
{
for (int i = 0; i < dtMDMDelete.Rows.Count; i++)
{
string strWhere = "App_UserID='" + dtMDMDelete.Rows[i]["App_UserID"] + "'";
connMDM.Delete("MDM_System_User", strWhere);
}
}
if (dtMDMUpdate.Rows.Count > 0)
{
ArrayList listKey = new ArrayList();
listKey.Add("App_UserID");
connMDM.Update("MDM_System_User", dtMDMUpdate, listKey);
}
if (dtMDMInsert.Rows.Count > 0)
{
dtMDMUpdate.Columns.Remove("App_UserID");
connMDM.InsertBYSQLBC("MDM_System_User", dtMDMInsert);
}
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("AM");
DataSet ds = conn.GetDataSet(
@"SELECT cn_id as AM_User_id,
CN_LOGIN as AM_login,
CN_USER_NAME as AM_User_Name,
CN_PASSWORD AM_Password,
CN_EMAIL AM_Email,
CN_TITLE AM_Title,
CN_HANDSET AM_Handset,
CN_ISDELETE AM_IsDelete
FROM dbo.TN_SYS_EM_USER t1 ,
dbo.TN_SYS_EM_USER_PROP t2
WHERE t1.cn_id = t2.CN_USERID
");
Dbconn connMDM = new Dbconn("MDM");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("AM_User_log");
tableList.Add("AM_User");
connMDM.TableLock(tableList);
// //存入日志数据
// connMDM.ExcuteQuerryByTran(@"INSERT INTO dbo.AM_User_log
// ( Log_datetime ,
// AM_User_id ,
// AM_LOGIN ,
// AM_USER_NAME,
// AM_PASSWORD,
// AM_EMAIL,
// AM_TITLE,
// AM_HANDSET,
// AM_ISDELETE,
// CreateTime ,
// UpdateTime ,
// Timestamps_old
//
// )
// SELECT GETDATE() ,
// AM_User_id ,
// AM_LOGIN ,
// AM_USER_NAME,
// AM_PASSWORD,
// AM_EMAIL,
// AM_TITLE,
// AM_HANDSET,
// AM_ISDELETE,
// CreateTime ,
// UpdateTime,
// CONVERT(BIGINT,Timestamps)
// FROM AM_User;");
//删除数据
connMDM.ExcuteQuerryByTran("DELETE FROM AM_User");
connMDM.InsertBYSQLBC("AM_User", ds.Tables[0]);
//更新MDM_Employees的用户信息
connMDM.ExcuteQuerryByTran(@"UPDATE MDM_Employees
SET SSO_Password=t2.AM_Password,
Leave_Flag=AM_ISDELETE
FROM dbo.AM_User t2 WHERE MDM_Employees.SSO_UserName = t2.AM_login
"
);
//并添加新入职的数据
connMDM.ExcuteQuerryByTran(@"
INSERT INTO dbo.MDM_Employees
(
Name ,
Sex ,
Phone ,
Moblie ,
SSO_UserName ,
SSO_Password ,
In_Time ,
Leave_Time ,
Leave_Flag ,
IsActived
)
SELECT
AM_login ,
NULL,
NULL,
NULL,
AM_login ,
AM_Password ,
GETDATE(),
NULL,
AM_ISDELETE,
1 FROM dbo.AM_User
WHERE AM_login NOT IN (SELECT SSO_UserName FROM MDM_Employees)
"
);
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("AM");
DataSet ds = conn.GetDataSet(
@"SELECT DISTINCT t1.CN_S_EMITEM_ID AS AM_USER_ID ,
t1.CN_P_EMITEM_ID AS AM_S_Dept_ID
FROM TN_SYS_EM_USER t2 WITH ( NOLOCK )
LEFT JOIN TN_SYS_EM_RELATION t1 WITH ( NOLOCK ) ON t1.CN_S_EMITEM_ID = t2.CN_ID
WHERE 1 = 1
AND t2.CN_ISDELETE = 0
AND t2.CN_DISABLED = 0
AND t1.CN_REL_TYPE = 1
AND CN_S_EMITEM_TYPE=0
AND (
( CN_P_EMITEM_TYPE = 1
AND EXISTS ( SELECT 1
FROM TN_SYS_EM_GROUP t3 WITH ( NOLOCK )
WHERE T1.CN_P_EMITEM_ID = T3.cn_id
AND T3.CN_OWNERID = 0 )
)
OR
( CN_P_EMITEM_TYPE = 3
AND EXISTS ( SELECT 1
FROM TN_SYS_EM_VIEW T4 WITH ( NOLOCK )
WHERE 1 = 1
AND CN_VIEW_TYPE = 1
AND CN_OWNERID = 0
AND CN_P_EMITEM_ID = T4.cn_id )
)
)
");
Dbconn connMDM = new Dbconn("MDM");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("Am_User_Dept_log");
tableList.Add("Am_User_Dept");
connMDM.TableLock(tableList);
// //存入日志数据
// connMDM.ExcuteQuerryByTran(@"INSERT INTO dbo.Am_User_Dept_log
// ( Log_datetime ,
// AM_USER_ID,
// AM_S_Dept_ID ,
// CreateTime ,
// UpdateTime,
// Timestamps_old
//
// )
// SELECT GETDATE() ,
// AM_USER_ID,
// AM_S_Dept_ID ,
// CreateTime ,
// UpdateTime,
// convert(bigint,Timestamps)
// FROM Am_User_Dept");
//删除数据
connMDM.ExcuteQuerryByTran("DELETE FROM Am_User_Dept");
connMDM.InsertBYSQLBC("Am_User_Dept", ds.Tables[0]);
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
public bool Execute()
{
string strEnv_ID = "";
string strEnv_SN = "HZY_SCM";
Dbconn conn_PLM_AS = new Dbconn("ERP_PROD");
DataSet ds = conn_PLM_AS.GetDataSet(
@"
SELECT NULL AS VexSSONewID ,
IDSUNO AS App_UserName ,
RIGHT(sys.fn_VarBinToHexStr(HASHBYTES('MD5',
CONVERT(VARCHAR(100), '123456a'))),
32) AS App_PasswordSrc ,
RIGHT(sys.fn_VarBinToHexStr(HASHBYTES('MD5',
CONVERT(VARCHAR(100), '123456a'))),
32) AS App_Password ,
IDSUNM AS App_UserName_CN ,
GETDATE() Chang_Password_Time ,
1 Checked ,
'system' Check_User ,
GETDATE() Check_Time ,
GETDATE() Createtime ,
GETDATE() Updatetime ,
0 AS oprationFlag
FROM CIDMAS
WHERE IDCONO = 100
AND IDSTAT = 20");
Dbconn connMDM = new Dbconn("VexSSO");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("MDM_System_User");
connMDM.TableLock(tableList);
strEnv_ID = connMDM.GetDataTableFirstValue("SELECT [Env_ID] FROM [MDM_System_Env] WHERE Env_SN='" + strEnv_SN + "'").ToString();
DataTable dtMDM = connMDM.GetDataTable(@"
SELECT [App_UserID]
,[Env_ID]
,[VexSSONewID]
,[App_UserName]
,[App_Password]
,[App_PasswordSrc]
,[App_UserName_CN]
,[Chang_Password_Time]
,[Checked]
,[Check_User]
,[Check_Time]
,[Createtime]
,[Updatetime]
FROM [dbo].[MDM_System_User]
WHERE Env_ID='" + strEnv_ID + @"'
");
DataTable dtMDMUpdate = dtMDM.Clone();
DataTable dtMDMInsert = dtMDM.Clone();
DataTable dtMDMDelete = dtMDM.Clone();
for (int i = 0; i < dtMDM.Rows.Count; i++)
{
string strApp_UserName = dtMDM.Rows[i]["App_UserName"].ToString();
DataRow[] dr = ds.Tables[0].Select("App_UserName='******'");
if (dr.Length > 0)
{
//只修改供应商名称不修改帐号密码
//dtMDM.Rows[i]["App_Password"] = dr[0]["App_Password"];
//dtMDM.Rows[i]["App_PasswordSrc"] = dr[0]["App_PasswordSrc"];
dtMDM.Rows[i]["App_UserName_CN"] = dr[0]["App_UserName_CN"];
dtMDMUpdate.ImportRow(dtMDM.Rows[i]);
dr[0]["oprationFlag"] = "1";
}
else
{
dtMDMDelete.ImportRow(dtMDM.Rows[i]);
}
}
DataRow[] drInsert = ds.Tables[0].Select("oprationFlag=0");
for (int i = 0; i < drInsert.Length; i++)
{
DataRow dr = dtMDMInsert.NewRow();
dr["Env_ID"] = strEnv_ID;
dr["VexSSONewID"] = drInsert[i]["VexSSONewID"];
dr["App_UserName"] = drInsert[i]["App_UserName"];
dr["App_Password"] = drInsert[i]["App_Password"];
dr["App_PasswordSrc"] = drInsert[i]["App_PasswordSrc"];
dr["App_UserName_CN"] = drInsert[i]["App_UserName_CN"];
dr["Chang_Password_Time"] = drInsert[i]["Chang_Password_Time"];
dr["Checked"] = drInsert[i]["Checked"];
dr["Check_User"] = drInsert[i]["Check_User"];
dr["Check_Time"] = drInsert[i]["Check_Time"];
dr["Createtime"] = drInsert[i]["Createtime"];
dr["Updatetime"] = drInsert[i]["Updatetime"];
dtMDMInsert.Rows.Add(dr);
}
if (dtMDMDelete.Rows.Count > 0)
{
for (int i = 0; i < dtMDMDelete.Rows.Count; i++)
{
string strWhere = "App_UserID='" + dtMDMDelete.Rows[i]["App_UserID"] + "'";
connMDM.Delete("MDM_System_User", strWhere);
}
}
if (dtMDMUpdate.Rows.Count > 0)
{
ArrayList listKey = new ArrayList();
listKey.Add("App_UserID");
connMDM.Update("MDM_System_User", dtMDMUpdate, listKey);
}
if (dtMDMInsert.Rows.Count > 0)
{
dtMDMUpdate.Columns.Remove("App_UserID");
connMDM.InsertBYSQLBC("MDM_System_User", dtMDMInsert);
}
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("DBDict");
//取到所有的数据库服务器
DataTable dt_DBServer = conn.GetDataTable("SELECT * FROM DBServer ", new string[0]);
//清空数据
conn.ExcuteQuerryByTran(@"
TRUNCATE TABLE DBDict.dbo.TableDict
TRUNCATE TABLE DBDict.dbo.TableSize
TRUNCATE TABLE DBDict.dbo.DBInfo
TRUNCATE TABLE DBDict.dbo.TableIndex
TRUNCATE TABLE DBDict.dbo.DBOtherInfo
TRUNCATE TABLE DBDict.dbo.LinkServers
");
if (dt_DBServer.Rows.Count > 0)
{
for (int i = 0; i < dt_DBServer.Rows.Count; i++)
{
string strConnString = "DataBase=master;Password="******"sql密码"].ToString() + ";User ID=" + dt_DBServer.Rows[i]["sql用户名"].ToString() + ";Data Source=" + dt_DBServer.Rows[i]["ip"].ToString() + ";";
string strSQL = "SELECT Name FROM Master..SysDatabases ORDER BY Name";
Dbconn conn_AllDB = new Dbconn(strConnString, true);
//所有的数据库名
DataTable dt_AllDB = conn_AllDB.GetDataTable(strSQL, new string[0]);
for (int k = 0; k < dt_AllDB.Rows.Count; k++)
{
if (dt_AllDB.Rows[k][0].ToString() == "tempdb")
{
continue;
}
strConnString = "DataBase=" + dt_AllDB.Rows[k][0].ToString() + ";Password="******"sql密码"].ToString() + ";User ID=" + dt_DBServer.Rows[i]["sql用户名"].ToString() + ";Data Source=" + dt_DBServer.Rows[i]["ip"].ToString() + ";";
Dbconn conn_DB = new Dbconn(strConnString, true);
string str_获取数据库中所有表的字段属性 = @"
SELECT @@SERVERNAME 服务器,db_name() 数据库名,d.name AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,
a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间,
GETDATE() 采集时间
FROM dbo.syscolumns a with(nolock) LEFT OUTER JOIN
dbo.systypes b with(nolock) ON a.xtype = b.xusertype INNER JOIN
dbo.sysobjects d with(nolock) ON a.id = d.id AND d.xtype = 'U' AND
d.status >= 0 LEFT OUTER JOIN
dbo.syscomments e with(nolock) ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties g with(nolock) ON a.id = g.major_id AND a.colid = g.minor_id AND
g.name = 'MS_Description' LEFT OUTER JOIN
sys.extended_properties f with(nolock) ON d.id = f.major_id AND f.minor_id = 0 AND
f.name = 'MS_Description'
ORDER BY d.name, 字段序号
";
string str_显示当前数据库中用户表的记录数量及大小 = @"
SELECT @@servername 服务器,db_name() 数据库,OBJECT_NAME(id) AS 表名,
rowcnt AS 记录数量,
8 * reserved / 1024 AS '大小(MB)',
GETDATE() 采集时间
FROM sysindexes a with(nolock)
LEFT JOIN sys.objects b with(nolock)
ON a.name = b.name
WHERE indid < 2
AND OBJECTPROPERTY(id, 'isusertable ') = 1
ORDER BY
rowcnt DESC
";
string str_查询所有表的索引 = @"
SELECT @@SERVERNAME AS 服务器,DB_NAME() AS 数据库,
c.name AS 表名,
a.name AS 索引名称, d.name AS 列名,
b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending')
WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL
THEN '' ELSE '√' END AS 主键, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered')
WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY(c.id,
a.name, 'IsUnique') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一,
CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一约束,
a.OrigFillFactor AS 填充因子, c.crdate AS 创建时间, c.refdate AS 更改时间,
GETDATE() AS 采集时间
FROM dbo.sysindexes a with(nolock) INNER JOIN
dbo.sysindexkeys b with(nolock) ON a.id = b.id AND a.indid = b.indid INNER JOIN
dbo.syscolumns d with(nolock) ON b.id = d.id AND b.colid = d.colid INNER JOIN
dbo.sysobjects c with(nolock) ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN
dbo.sysobjects e with(nolock) ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN
dbo.sysobjects p with(nolock) ON p.name = a.name AND p.xtype = 'PK'
WHERE (OBJECTPROPERTY(a.id, N'IsUserTable') = 1) AND (OBJECTPROPERTY(a.id,
N'IsMSShipped') = 0) AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0)
ORDER BY c.name, a.name, b.keyno
";
string str_表视图函数存储过程触发器主键外键约束规则 = @"
SELECT DISTINCT @@SERVERNAME AS 服务器,DB_NAME() AS 数据库,
isnull(p.name,'') AS 父对象, o.xtype,
CASE o.xtype WHEN 'C' THEN 'CHECK 约束' WHEN 'D' THEN '默认值或DEFAULT约束'
WHEN 'F' THEN 'FOREIGNKEY约束' WHEN 'L' THEN '日志' WHEN 'FN' THEN '标量函数'
WHEN 'IF' THEN '内嵌表函数' WHEN 'P' THEN '存储过程' WHEN 'PK' THEN 'PRIMARYKEY约束'
WHEN 'RF' THEN '复制筛选存储过程' WHEN 'S' THEN '系统表' WHEN 'TF' THEN '表函数'
WHEN 'TR' THEN '触发器' WHEN 'U' THEN '用户表' WHEN 'UQ' THEN 'UNIQUE 约束'
WHEN 'V' THEN '视图' WHEN 'X' THEN '扩展存储过程' WHEN 'R' THEN '规则' ELSE NULL
END AS 类型, o.name AS 对象名, o.crdate AS 创建时间, o.refdate AS 更改时间,
c.text AS 声明语句,
GETDATE() AS 采集时间
FROM dbo.sysobjects o with(nolock) Left JOIN
dbo.sysobjects p with(nolock) ON o.parent_obj = p.id LEFT OUTER JOIN
dbo.syscomments c with(nolock) ON o.id = c.id
WHERE --(o.xtype IN ('C','D','F','PK','UQ','L','FN','IF','TF','TR','P','R','RF','X','S','U','V')) AND
(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0) AND (isnull(p.name,'') <> N'dtproperties')
ORDER BY o.xtype DESC
";
string str_取本机已建立的链接服务器 = @"
SELECT @@servername AS SeverName,s.name linkedServerName,
s.data_source linkedserver_source,
s.is_linked,
s.modify_date,
ll.remote_name,
ll.local_principal_id,
ll.uses_self_credential,
p.name localLoginName,
GETDATE() AS CollecterDate
FROM sys.Linked_logins ll with(nolock)
INNER JOIN sys.servers s with(nolock)
ON s.server_id = ll.server_id
LEFT JOIN sys.server_principals p with(nolock)
ON p.principal_id = ll.local_principal_id
WHERE s.is_linked = 1
";
DataTable dt_获取数据库中所有表的字段属性 = conn_DB.GetDataTable(str_获取数据库中所有表的字段属性, new string[0]);
DataTable dt_显示当前数据库中用户表的记录数量及大小 = conn_DB.GetDataTable(str_显示当前数据库中用户表的记录数量及大小, new string[0]);
DataTable dt_查询所有表的索引 = conn_DB.GetDataTable(str_查询所有表的索引, new string[0]);
DataTable dt_表视图函数存储过程触发器主键外键约束规则 = conn_DB.GetDataTable(str_表视图函数存储过程触发器主键外键约束规则, new string[0]);
DataTable dt_取本机已建立的链接服务器 = conn_DB.GetDataTable(str_取本机已建立的链接服务器, new string[0]);
DataTable dt_数据库名 = dt_获取数据库中所有表的字段属性.DefaultView.ToTable(true, new string[1] { "数据库名" });
DataTable dt_DBInfo = new DataTable();
if(dt_数据库名.Rows.Count>0)
{
string str_DBInfo = @"
SELECT @@servername AS ServerName,
DB_NAME() AS DBName,
type_desc,
name,
physical_name,
size,
growth,
GETDATE() AS CollecterDate
FROM " + dt_数据库名.Rows[0][0].ToString() + @".sys.database_files with(nolock)
";
dt_DBInfo = conn_DB.GetDataTable(str_DBInfo, new string[0]);
}
ArrayList listTable = new ArrayList();
listTable.Add("TableDict");
listTable.Add("TableSize");
listTable.Add("DBInfo");
listTable.Add("TableIndex");
listTable.Add("DBOtherInfo");
listTable.Add("LinkServers");
//conn.ConnOpen();
conn.BeginTransaction();
try
{
conn.TableLock(listTable);
if (dt_DBInfo.Rows.Count > 0)
{
conn.InsertBYSQLBC("DBInfo", dt_DBInfo);
}
conn.InsertBYSQLBC("TableDict", dt_获取数据库中所有表的字段属性);
conn.InsertBYSQLBC("TableSize", dt_显示当前数据库中用户表的记录数量及大小);
conn.InsertBYSQLBC("TableIndex", dt_查询所有表的索引);
conn.InsertBYSQLBC("DBOtherInfo", dt_表视图函数存储过程触发器主键外键约束规则);
conn.InsertBYSQLBC("LinkServers", dt_取本机已建立的链接服务器);
conn.CommitTransaction();
//conn.ConnClose();
}
catch
{
conn.RollbackTransaction();
}
}
}
}
return true;
}
public bool Execute()
{
string strSystem_Fid = "144";
string strSystem_id = "ST0301A";
string strSystem_Name = "F22分销系统 CS总部版";
string strCompany_id = "235";
Dbconn conn = new Dbconn("F22");
Dbconn conn_MDM = new Dbconn("MDM");
//取到总部的数据
DataSet ds = conn.GetDataSet(
@" SELECT userid ,
username
FROM j_user t1 WITH ( NOLOCK )
WHERE 1 = 1
AND dptype = 0
AND notuse = 0
");
DataTable dt_Add = new DataTable();
dt_Add.Columns.Add("System_FID");
dt_Add.Columns.Add("System_ID");
dt_Add.Columns.Add("System_Name");
dt_Add.Columns.Add("AM_User_ID");
dt_Add.Columns.Add("AM_Login");
dt_Add.Columns.Add("Company_ID");
dt_Add.Columns.Add("System_User_ID");
dt_Add.Columns.Add("System_User_Name");
dt_Add.Columns.Add("Create_AM_User_ID");
dt_Add.Columns.Add("CreateTime");
dt_Add.Columns.Add("UpdateTime");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string strName = ds.Tables[0].Rows[i]["username"].ToString();
string strUserID = ds.Tables[0].Rows[i]["userid"].ToString();
DataTable dt_AM = conn_MDM.GetDataTable(@"SELECT AM_User_ID,AM_login FROM dbo.AM_User
WHERE AM_LOGIN=@param0", new string[1] { strName });
if (dt_AM.Rows.Count > 0)
{
DataRow dr_add = dt_Add.NewRow();
dr_add["System_FID"] = strSystem_Fid;
dr_add["System_ID"] = strSystem_id;
dr_add["System_Name"] = strSystem_Name;
dr_add["AM_User_ID"] = dt_AM.Rows[0]["AM_User_ID"].ToString();
dr_add["AM_Login"] = dt_AM.Rows[0]["AM_Login"].ToString();
dr_add["Company_ID"] = strCompany_id;
dr_add["System_User_ID"] = strUserID;
dr_add["System_User_Name"] = strName;
dr_add["Create_AM_User_ID"] = "0"; //默认为系统自动建立
dr_add["CreateTime"] = DateTime.Now;
dr_add["UpdateTime"] = DateTime.Now;
dt_Add.Rows.Add(dr_add);
}
}
if (dt_Add.Rows.Count > 0)
{
ArrayList listLockTable = new ArrayList();
listLockTable.Add("SSO_System_Relation");
conn_MDM.BeginTransaction();
try
{
conn_MDM.TableLock(listLockTable);
conn_MDM.Excute("DELETE FROM SSO_System_Relation WHERE System_FID='" + strSystem_Fid + "'");
conn_MDM.InsertBYSQLBC("SSO_System_Relation", dt_Add);
conn_MDM.CommitTransaction();
}
catch (Exception)
{
conn_MDM.RollbackTransaction();
throw;
}
}
return true;
}
public bool Execute()
{
Dbconn m_Conn_MDM = new Dbconn("MDM");
DataSet Config = new DataSet();
string strSql = @" SELECT [Com] ,
[Type] ,
[Conn] ,
[SKUTable]
FROM [DRPStlyeInfoSyncConfig] ";
Config = m_Conn_MDM.GetDataSet(strSql);
for (int i = 0; i < Config.Tables[0].Rows.Count; i++)
{
string Com = Config.Tables[0].Rows[i]["Com"].ToString();
string Type = Config.Tables[0].Rows[i]["Type"].ToString();
string SKUTable = Config.Tables[0].Rows[i]["SKUTable"].ToString();
string Conn = Config.Tables[0].Rows[i]["Conn"].ToString();
Dbconn BillConn = new Dbconn(Conn);
DataSet DRPSKU = m_Conn_F22GP_DRP_REPORT.GetDataSetBySP("SP_OnlineStyle", new string[3] { "@COM", "@Type", "@Styleid" },
new string[3] { Com, Type, "" });
try
{
BillConn.BeginTransaction();
if (DRPSKU.Tables[0] == null || DRPSKU.Tables[0].Rows.Count == 0)
{
throw new Exception("请传入需要导入的数据！");
}
BillConn.ExcuteQuerryByTran("TRUNCATE TABLE " + SKUTable);
BillConn.InsertBYSQLBC(SKUTable, DRPSKU.Tables[0]);
BillConn.CommitTransaction();
//HZY.COM.Common.Log.WirteLogWS("成功", null);
//return true;
}
catch (SqlException ex)
{
BillConn.RollbackTransaction();
if (ex.Message.Contains("唯一索引"))
{
dt_EditResult.Rows.Add(new object[] { 1, "插入了重复键值，请检查！" });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
}
else
{
dt_EditResult.Rows.Add(new object[] { 1, ex.Message });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
}
HZY.COM.Common.Log.WirteLogWS(ex, null);
return false;
}
catch (Exception ex)
{
BillConn.RollbackTransaction();
dt_EditResult.Rows.Add(new object[] { 1, ex.Message });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
HZY.COM.Common.Log.WirteLogWS(ex, null);
return false;
}
}
dt_EditResult.Rows.Add(new object[] { 0, "成功！" });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
return true;
}
/// <summary>
/// 获取MDM应用的数据
/// </summary>
/// <returns></returns>
public bool Execute()
{
DateTime dateStart = DateTime.Now;
DateTime dateEnd = DateTime.Now;
if (m_request != null && m_request.Tables.Count >0 && m_request.Tables[0].Rows.Count == 1)
{
if (m_request.Tables[0].Columns.Contains("dateStart"))
{
dateStart = Convert.ToDateTime(m_request.Tables[0].Rows[0]["dateStart"]).AddDays(-1);
}
if (m_request.Tables[0].Columns.Contains("dateEnd"))
{
dateEnd = Convert.ToDateTime(m_request.Tables[0].Rows[0]["dateEnd"]).AddDays(-1);
}
}
DateTime currentDate = dateStart;
Dbconn conn = new Dbconn("BI_CustomerFlow");
DataTable dtLog = new DataTable();
dtLog.Columns.Add("Execute_Date");
dtLog.Columns.Add("Result");
dtLog.Columns.Add("Message");
dtLog.Columns.Add("Exception");
while (currentDate <= dateEnd)
{
try
{
dtLog.Clear();
//获取FTP的数据
GetFTPData(currentDate);
conn.LockTableList.Add("T_CustomerFlow");
conn.BeginTransaction();
conn.Delete("T_CustomerFlow", "Local_Date_and_Time between '" + currentDate.ToString("yyyy-MM-dd 00:00:00.000") + "' and '" + currentDate.ToString("yyyy-MM-dd 23:59:59.999") + "' ");
//插入数据
conn.InsertBYSQLBC("T_CustomerFlow", m_dtFTP);
dtLog.Rows.Add(new object[] { currentDate, true, "", "" });
conn.Insert("Log_GetFTPData", dtLog);
//throw new Exception("test");
conn.CommitTransaction();
}
catch (Exception ex)
{
conn.RollbackTransaction();
try
{
dtLog.Rows.Add(new object[] { currentDate, false, ex.Message, ex.ToString() });
conn.BeginTransaction();
conn.Insert("Log_GetFTPData", dtLog);
conn.CommitTransaction();
}
catch (Exception ex1)
{
}
try
{
AMSendWS.MsgCenter cls = new AMSendWS.MsgCenter();
cls.SendMsg("AM", "BI客流数据获取", "马卫清", "信息部服务中心", "BI客流数据获取:执行日期：“" + currentDate.ToString("yyyy-MM-dd") + "”时发生错误：" + ex.ToString(), DateTime.Now.AddDays(-1).ToString());
}
catch (Exception ex2)
{
}
}
currentDate = currentDate.AddDays(1);
}
return true;
}
public bool Execute()
{
string strEnv_SN = "Present";
string strEnv_ID = "";
Dbconn conn_EHR = new Dbconn("EHR");
DataSet ds = conn_EHR.GetDataSet(
@"
SELECT NULL AS VexSSONewID ,
t2.C_PASSWORD [App_Password],
t2.C_PASSWORD App_PasswordSrc,
t1.C_CODE App_UserName,
t1.C_NAME App_UserName_CN,
GETDATE() Chang_Password_Time ,
1 Checked ,
'system' Check_User ,
GETDATE() Check_Time ,
GETDATE() Createtime ,
GETDATE() Updatetime ,
0 AS oprationFlag
FROM ehr.TB_INF_EMPLOYEE t1
LEFT JOIN ehr.TB_SYS_USER t2 ON t1.C_OID = t2.C_EMPLOYEEID
WHERE t1.C_EMPLOYEESTATUS!=3
");
Dbconn connMDM = new Dbconn("VexSSO");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("MDM_System_User");
connMDM.TableLock(tableList);
strEnv_ID = connMDM.GetDataTableFirstValue("SELECT [Env_ID] FROM [MDM_System_Env] WHERE Env_SN='" + strEnv_SN + "'").ToString();
DataTable dtMDM = connMDM.GetDataTable(@"
SELECT [App_UserID]
,[Env_ID]
,[VexSSONewID]
,[App_UserName]
,[App_Password]
,[App_PasswordSrc]
,[App_UserName_CN]
,[Chang_Password_Time]
,[Checked]
,[Check_User]
,[Check_Time]
,[Createtime]
,[Updatetime]
FROM [dbo].[MDM_System_User]
WHERE Env_ID='" + strEnv_ID + @"'");
DataTable dtMDMUpdate = dtMDM.Clone();
DataTable dtMDMInsert = dtMDM.Clone();
DataTable dtMDMDelete = dtMDM.Clone();
for (int i = 0; i < dtMDM.Rows.Count; i++)
{
string strApp_UserName = dtMDM.Rows[i]["App_UserName"].ToString();
DataRow[] dr = ds.Tables[0].Select("App_UserName='******'");
if (dr.Length > 0)
{
dtMDM.Rows[i]["App_Password"] = dr[0]["App_Password"];
dtMDM.Rows[i]["App_PasswordSrc"] = dr[0]["App_PasswordSrc"];
dtMDM.Rows[i]["App_UserName_CN"] = dr[0]["App_UserName_CN"];
dtMDMUpdate.ImportRow(dtMDM.Rows[i]);
dr[0]["oprationFlag"] = "1";
}
else
{
dtMDMDelete.ImportRow(dtMDM.Rows[i]);
}
}
DataRow[] drInsert = ds.Tables[0].Select("oprationFlag=0");
for (int i = 0; i < drInsert.Length; i++)
{
DataRow dr = dtMDMInsert.NewRow();
dr["Env_ID"] = strEnv_ID;
dr["VexSSONewID"] = drInsert[i]["VexSSONewID"];
dr["App_UserName"] = drInsert[i]["App_UserName"];
dr["App_Password"] = drInsert[i]["App_Password"];
dr["App_PasswordSrc"] = drInsert[i]["App_PasswordSrc"];
dr["App_UserName_CN"] = drInsert[i]["App_UserName_CN"];
dr["Chang_Password_Time"] = drInsert[i]["Chang_Password_Time"];
dr["Checked"] = drInsert[i]["Checked"];
dr["Check_User"] = drInsert[i]["Check_User"];
dr["Check_Time"] = drInsert[i]["Check_Time"];
dr["Createtime"] = drInsert[i]["Createtime"];
dr["Updatetime"] = drInsert[i]["Updatetime"];
dtMDMInsert.Rows.Add(dr);
}
if (dtMDMDelete.Rows.Count > 0)
{
for (int i = 0; i < dtMDMDelete.Rows.Count; i++)
{
string strWhere = "App_UserID='" + dtMDMDelete.Rows[i]["App_UserID"] + "'";
connMDM.Delete("MDM_System_User", strWhere);
}
}
if (dtMDMUpdate.Rows.Count > 0)
{
ArrayList listKey = new ArrayList();
listKey.Add("App_UserID");
connMDM.Update("MDM_System_User", dtMDMUpdate, listKey);
}
if (dtMDMInsert.Rows.Count > 0)
{
dtMDMUpdate.Columns.Remove("App_UserID");
connMDM.InsertBYSQLBC("MDM_System_User", dtMDMInsert);
}
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
public bool Execute()
{
string strEnv_ID = "";
string strEnv_SN = "EP_PLMFZ";
Dbconn conn_PLM_AS = new Dbconn("PLM_AS");
DataSet ds = conn_PLM_AS.GetDataSet(
@" SELECT
--44 as Env_ID,
null as VexSSONewID,
username as App_UserName,
RIGHT(sys.fn_VarBinToHexStr(hashbytes('MD5', convert(vARCHAR(100),PASSWORD))), 32) as App_Password,
PASSWORD as App_PasswordSrc,
userNameC as App_UserName_CN,
getdate() Chang_Password_Time ,
1 Checked ,
'system' Check_User ,
getdate() Check_Time ,
getdate() Createtime ,
getdate() Updatetime,
0 as oprationFlag
FROM PLMAss.dbo.USERINFO u
");
Dbconn connMDM = new Dbconn("VexSSO");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("MDM_System_User");
connMDM.TableLock(tableList);
strEnv_ID = connMDM.GetDataTableFirstValue("SELECT [Env_ID] FROM [MDM_System_Env] WHERE Env_SN='" + strEnv_SN + "'").ToString();
DataTable dtMDM = connMDM.GetDataTable(@"
SELECT [App_UserID]
,[Env_ID]
,[VexSSONewID]
,[App_UserName]
,[App_Password]
,[App_PasswordSrc]
,[App_UserName_CN]
,[Chang_Password_Time]
,[Checked]
,[Check_User]
,[Check_Time]
,[Createtime]
,[Updatetime]
FROM [dbo].[MDM_System_User]
WHERE Env_ID='" + strEnv_ID + @"'
");
DataTable dtMDMUpdate = dtMDM.Clone();
DataTable dtMDMInsert = dtMDM.Clone();
DataTable dtMDMDelete = dtMDM.Clone();
for (int i = 0; i < dtMDM.Rows.Count; i++)
{
string strApp_UserName = dtMDM.Rows[i]["App_UserName"].ToString();
DataRow[] dr = ds.Tables[0].Select("App_UserName='******'");
if (dr.Length > 0)
{
dtMDM.Rows[i]["App_Password"] = dr[0]["App_Password"];
dtMDM.Rows[i]["App_PasswordSrc"] = dr[0]["App_PasswordSrc"];
dtMDM.Rows[i]["App_UserName_CN"] = dr[0]["App_UserName_CN"];
dtMDMUpdate.ImportRow(dtMDM.Rows[i]);
dr[0]["oprationFlag"] = "1";
}
else
{
dtMDMDelete.ImportRow(dtMDM.Rows[i]);
}
}
DataRow[] drInsert = ds.Tables[0].Select("oprationFlag=0");
for (int i = 0; i < drInsert.Length; i++)
{
DataRow dr = dtMDMInsert.NewRow();
dr["Env_ID"] = strEnv_ID;
dr["VexSSONewID"] = drInsert[i]["VexSSONewID"];
dr["App_UserName"] = drInsert[i]["App_UserName"];
dr["App_Password"] = drInsert[i]["App_Password"];
dr["App_PasswordSrc"] = drInsert[i]["App_PasswordSrc"];
dr["App_UserName_CN"] = drInsert[i]["App_UserName_CN"];
dr["Chang_Password_Time"] = drInsert[i]["Chang_Password_Time"];
dr["Checked"] = drInsert[i]["Checked"];
dr["Check_User"] = drInsert[i]["Check_User"];
dr["Check_Time"] = drInsert[i]["Check_Time"];
dr["Createtime"] = drInsert[i]["Createtime"];
dr["Updatetime"] = drInsert[i]["Updatetime"];
dtMDMInsert.Rows.Add(dr);
}
if (dtMDMDelete.Rows.Count > 0)
{
for (int i = 0; i < dtMDMDelete.Rows.Count; i++)
{
string strWhere = "App_UserID='" + dtMDMDelete.Rows[i]["App_UserID"] + "'";
connMDM.Delete("MDM_System_User", strWhere);
}
}
if (dtMDMUpdate.Rows.Count > 0)
{
ArrayList listKey = new ArrayList();
listKey.Add("App_UserID");
connMDM.Update("MDM_System_User", dtMDMUpdate, listKey);
}
if (dtMDMInsert.Rows.Count > 0)
{
dtMDMUpdate.Columns.Remove("App_UserID");
connMDM.InsertBYSQLBC("MDM_System_User", dtMDMInsert);
}
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
public bool Execute()
{
string strBatchID = Guid.NewGuid().ToString();
Dbconn conn = new Dbconn("MDM");
DataSet ds = conn.GetDataSet(
@"
SELECT CONVERT(UNIQUEIDENTIFIER,'" + strBatchID + @"') as BatchID
,[AM_User_id]
,[AM_login]
,[AM_User_Name]
,[AM_Password]
FROM [MDM].[dbo].[AM_User]
WHERE am_isdelete =0
");
Dbconn connVexSSO = new Dbconn("VEXSSO");
try
{
connVexSSO.BeginTransaction();
connVexSSO.InsertBYSQLBC("Tmp_AM_User", ds.Tables[0]);
string strSQL = @"
DECLARE @envid INT
SELECT @envid = Env_ID
FROM dbo.MDM_System_Env
WHERE Env_SN = 'AM'
SELECT [NewID] ,
[BatchID] ,
RIGHT('888888888888888' + CONVERT(VARCHAR(100), AM_User_id), 11) [AM_User_id] ,
[AM_login] ,
[AM_User_Name] ,
[AM_Password]
INTO #tmp
FROM [Tmp_AM_User]
WHERE batchID = '" + strBatchID + @"'
AND AM_login NOT IN ( SELECT App_UserName
FROM dbo.MDM_System_User
WHERE Env_ID = @envid
AND checked = 1 )
AND RIGHT('888888888888888' + CONVERT(VARCHAR(100), AM_User_id), 11) NOT IN ( SELECT VexSSOLoginUser
FROM B01_MDM.VEXSSOUser
)
DELETE FROM [dbo].[Tmp_AM_User]
WHERE batchID = '" + strBatchID + @"'
INSERT INTO B01_MDM.VEXSSOUser
( VexSSONewID ,
VexSSOLoginUser ,
VexSSOPassWord ,
VexSSOPassWordMd52 ,
Disabled
)
SELECT NEWID() ,
AM_User_id ,
AM_Password ,
right(sys.fn_VarBinToHexStr(hashbytes('MD5',Convert(varchar(100), right(sys.fn_VarBinToHexStr(hashbytes('MD5',Convert(varchar(100),AM_Password))),32)))),32) ,
0
FROM #tmp
INSERT INTO [VexSSO].[dbo].[MDM_System_User]
( [Env_ID]
,[VexSSONewID]
,[App_UserName]
,[App_PasswordSrc]
,[App_Password]
,[App_UserName_CN]
,[Chang_Password_Time]
,[Checked]
,[Check_User]
,[Check_Time])
SELECT @envid,VexSSONewID,AM_login,AM_Password,AM_Password,AM_User_Name,GETDATE(),1,'sysytem',GETDATE()
FROM #tmp
LEFT JOIN B01_MDM.VEXSSOUser ON AM_User_id = VexSSOLoginUser
INSERT INTO [B01_MDM].[VexSSOUserInfo]
([VexSSONewId],[Name],[CreateTime],[CreateUser],[UpdateTime],[UpdateUser])
SELECT VexSSONewID,AM_login ,GETDATE(),'system',GETDATE(),'system'
FROM #tmp
LEFT JOIN B01_MDM.VEXSSOUser ON AM_User_id = VexSSOLoginUser
DROP TABLE #tmp
";
connVexSSO.ExcuteQuerryByTran(strSQL);
connVexSSO.CommitTransaction();
return true;
}
catch
{
connVexSSO.RollbackTransaction();
throw;
}
}
