public DataSet GetData()
{
try
{
string strSQL = "";
string strwhere = m_request.Tables[0].Rows[0]["where"].ToString();
//if (strwhere == "")
//{
// strwhere = " 1=1 ";
//}ERP_TEST
Dbconn conn = new Dbconn("ERP_PROD");
strSQL = @"SELECT rtrim(CTSTKY) CTSTKY,rtrim(CTTX40) CTTX40 FROM CSYTAB WHERE CTSTKY like 'N2%' and CTSTKY not in ('N207','N209','N211') and CTSTCO= '" + strwhere + "'";
DataSet ds = conn.GetDataSet(strSQL);
return ds;
}
catch
{
throw;
}
}
/// <summary>
/// 获取某个Market的Entity的所有属性列表
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
DataSet ds = new DataSet();
DataSet dsRequest = new DataSet();
string strEntity = "";
string strMarketTableName = "";
strEntity = m_request.Tables[0].Rows[0]["Entity"].ToString();
strMarketTableName = m_request.Tables[0].Rows[0]["MarketTableName"].ToString();
//获取所有的列
Dbconn conn = new Dbconn("MDM_Master");
ds = conn.GetDataSet(@"
SELECT t1.MainEntity,t2.Porperty_Name,t2.Table_Property_Name FROM dbo.Lib_View_Table t1
LEFT JOIN dbo.Lib_View_TableColumn t2 ON t1.Table_ID = t2.Table_ID
WHERE Table_Name='" + strMarketTableName + @"'
AND Entity='" + strEntity + @"'
AND Table_Type='Market'
");
return ds;
}
catch
{
throw;
}
}
public DataSet GetData()
{
Dbconn conn = new Dbconn("MDM");
return conn.GetDataSet(@"
SELECT t1.WS_ID ,
WS_LogicName
FROM dbo.WS_Scheduling t1 WITH ( NOLOCK )
WHERE t1.Enabled = 1
AND ( t1.Last_Execute_Right_Time IS NULL
OR ( DATEDIFF(s, t1.Last_Execute_Right_Time, GETDATE()) > t1.Excute_cycle
AND ISNULL(t1.Error_Count, 0) < 5
AND ISNULL(Execute_type, 'cycle') = 'cycle'
)
--按日
OR ( ( t1.Last_Execute_Right_Time IS NULL
--取今天没有跑过的数据
OR t1.Last_Execute_Right_Time < CONVERT(VARCHAR(10), GETDATE(), 23)
+ ' ' + t1.Execute_StartTime
--取今天跑过了，但是到了下一次的执行时间
OR DATEDIFF(s, t1.Last_Execute_Right_Time, GETDATE()) > t1.Excute_cycle
)
AND ISNULL(t1.Error_Count, 0) < 5
AND CONVERT(VARCHAR(8), GETDATE(), 14) > Execute_StartTime
AND ISNULL(Execute_type, 'cycle') = 'day'
)
)
ORDER BY WS_ID ;
SELECT *
FROM WS_SchedulingParam
");
}
/// <summary>
/// 根据Entity的编号获取MDM中Entity列的配置
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
DataSet ds = new DataSet();
DataSet dsRequest = new DataSet();
string strMainEntity = "";
strMainEntity = m_request.Tables[0].Rows[0]["Entity"].ToString();
//获取所有的列
Dbconn conn = new Dbconn("MDM_Master");
ds = conn.GetDataSet(@"
SELECT Property_Name FROM dbo.Lib_ExdPropertyList
WHERE Class_Name='" + strMainEntity + @"'
Order BY ColumnIndex
");
return ds;
}
catch
{
throw;
}
}
public DataSet GetData()
{
try
{
if (m_request == null || m_request.Tables.Count == 0 || m_request.Tables[0].Rows.Count == 0)
{
return null;
}
string strUser_id = m_request.Tables[0].Rows[0]["user_id"].ToString();
Dbconn conn = new Dbconn("MDM");
DataSet ds = conn.GetDataSet(@"
SELECT
t1.AM_S_dept_ID ,
t2.AM_Dept_NAME ,
t2.AM_Dept_NAME_all
FROM dbo.AM_User_Dept t1 WITH ( NOLOCK )
LEFT JOIN dbo.AM_Dept t2 WITH ( NOLOCK ) ON t1.am_S_dept_ID = t2.am_S_dept_ID
WHERE t1.AM_User_ID = "+strUser_id+@"
");
return ds;
}
catch
{
throw ;
}
}
/// <summary>
/// 获取公司信息
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
Dbconn conn = new Dbconn("MDM");
DataSet ds = conn.GetDataSet(@"
SELECT
[Company_ID]
,[Company_Name]
,[Company_sn]
,[Company_Address]
,[Fax]
,[Tax_ID]
,[Bank_Name]
,[Bank_ID]
,[Createtime]
,[Updatetime]
,Convert(bigint,t1.[Timestamps]) Timestamps
FROM [MDM].[dbo].[MDM_Company] t1 WITH(NOLOCK)
");
return ds;
}
catch
{
throw;
}
}
/// <summary>
/// 获取MDM人员信息
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
string strVexSSONewID = m_hzyMessage.VexSSONewID;
Dbconn conn = new Dbconn("VexSSO");
DataSet ds = conn.GetDataSet(@"
SELECT 0 [Employee_ID] ,
t2.[Name] ,
'' [Sex] ,
t2.Phone [Phone] ,
t1.VexSSOLoginUser [Moblie] ,
t1.VexSSOLoginUser [SSO_UserName] ,
NULL [In_Time] ,
NULL [Leave_Time] ,
NULL [Leave_Flag] ,
NULL [IsActived]
FROM B01_MDM.[VEXSSOUser] t1
LEFT JOIN B01_MDM.[VexSSOUserInfo] t2 ON t2.VexSSONewId = t1.VexSSONewID
WHERE t1.VexSSONewId=@param0
", new string[1] { strVexSSONewID });
return ds;
}
catch
{
throw;
}
}
public DataSet GetData()
{
string strVexSSONewID = m_hzyMessage.VexSSONewID;
string strApp_UserID = m_hzyMessage.User_ID;
string strEnv_ID = m_hzyMessage.Env_ID;
if (string.IsNullOrEmpty(strVexSSONewID))
{
strVexSSONewID = "00000000-0000-0000-0000-000000000000";
}
if (strApp_UserID == "0")
{
strApp_UserID = "-1";
}
Dbconn conn = new Dbconn("VexSSO");
string strSQL = @"
SELECT t1.Authorization_ID ,
t1.Authorization_Name
FROM dbo.MDM_System_Authorization t1 WITH ( NOLOCK )
JOIN dbo.MDM_System_User_Authorization t2 WITH ( NOLOCK ) ON t1.Authorization_ID = t2.Authorization_ID
WHERE Env_ID = @param1
AND (VexSSONewID = @param0 OR App_UserID=@param2)
AND ISNULL(HavaFlag, 0)=1
";
DataSet ds = conn.GetDataSet(strSQL, new string[3] { strVexSSONewID, strEnv_ID ,strApp_UserID});
return ds;
}
public DataSet GetData()
{
string strEnv_ID = m_request.Tables[0].Rows[0]["Env_ID"].ToString();
string strAuthorization_ID = m_request.Tables[0].Rows[0]["Authorization_ID"].ToString();
Dbconn conn = new Dbconn("MDM");
string strSQL = @"
SELECT t1.Menu_Id ,
t1.Menu_Code +' ' + t1.Menu_Name Menu_Name,
t1.Menu_Father_ID ,
ISNULL(t2.HavaFlag, 0) HavaFlag
FROM MDM_System_Menu t1 WITH ( NOLOCK )
LEFT JOIN dbo.MDM_System_Menu_AUZ t2 WITH ( NOLOCK ) ON t1.Menu_Id = t2.Menu_ID
AND Authorization_ID=@param0
WHERE t1.env_id = @param1
";
DataSet ds = conn.GetDataSet(strSQL, new string[2] { strAuthorization_ID,strEnv_ID });
//添加根目录行
ds.Tables[0].Rows.Add(new object[] { "0", "根目录", "-1", "0" });
return ds;
}
/// <summary>
/// 获取系统用户信息
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
string strUser_ID = m_hzyMessage.User_ID;
Dbconn conn = new Dbconn("VexSSO");
DataSet ds = conn.GetDataSet(@"
SELECT
[App_UserID]
,[App_UserName]
,[App_UserName_CN]
,[Chang_Password_Time]
FROM [dbo].[MDM_System_User]
WHERE App_UserID=@param0
", new string[1]{strUser_ID});
return ds;
}
catch
{
throw;
}
}
/// <summary>
/// 根据权限和Market表名，获取Filter信息
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
string strAUZ = m_request.Tables[0].Rows[0]["AUZ"].ToString();
string strMarketTableName = m_request.Tables[0].Rows[0]["MarketTableName"].ToString();
Dbconn conn = new Dbconn("MDM_Master");
DataSet returnDs = conn.GetDataSet(@"
SELECT Main_Entity ,
t3.Table_Property_Name + ' ' + t1.Fittler AS Filter
FROM dbo.Lib_AUZ_Fittler t1 WITH ( NOLOCK )
FULL JOIN dbo.Lib_View_TableColumn t3 WITH ( NOLOCK ) ON t3.Porperty_ID = t1.property_ID
LEFT JOIN [dbo].[Lib_View_Table] t4 WITH ( NOLOCK ) ON t4.Table_ID = t3.Table_ID
LEFT JOIN dbo.Lib_ExdPropertyList t2 WITH ( NOLOCK ) ON t1.property_ID = t2.Property_ID
WHERE Table_Name = '" + strMarketTableName + @"'
AND AUZ LIKE '%【" + strAUZ + @"】%'
");
return returnDs;
}
catch
{
throw;
}
}
public DataSet GetData()
{
DataSet ds = null;
Dbconn conn = new Dbconn("MDM");
string strSQL = @" SELECT t1.WS_ID ,
TASK_ID,
WS_LogicName,
WS_Name,
URL,
Execute_type,
Execute_StartTime,
Execute_EndTime,
Execute_cycle,
Execute_DayStartTime,
Execute_DayEndTime,
Execute_WeekTime,
Execute_MonthTime,
Enabled
FROM dbo.WS_Scheduling t1 WITH ( NOLOCK )
WHERE
ISNULL(t1.Error_Count, 0) < 5";
if (m_request.Tables[0].Rows.Count >0 && m_request.Tables[0].Rows[0][0] != null)
{
strSQL += " AND UpdateTime > '" + Convert.ToDateTime(m_request.Tables[0].Rows[0][0]).ToString("yyyy-MM-dd HH:mm:ss") + "' ";
}
strSQL += "; SELECT * FROM WS_SchedulingParam;";
ds = conn.GetDataSet(strSQL);
return ds;
}
/// <summary>
/// 获取系统环境信息
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
string strWS_ID = m_request.Tables[0].Rows[0]["ws_id"].ToString();
Dbconn conn = new Dbconn("WSRR");
DataSet ds = conn.GetDataSet(@"
SELECT
Param_ID,
[Param_Index]
,[Param_Content]
,[Param_Name]
,[Param_Type]
,CASE ISNULL(Param_Must_Flag,0) WHEN 1 THEN '必须' WHEN '0' THEN '否' WHEN '2' THEN '系统默认' END AS Param_Must_Flag
,ISNULL(Param_Must_Flag,0) as Param_Must_Flagint
,[Default_Value]
FROM [WSRR].[dbo].[WS_Param]
WHERE WS_ID='" + strWS_ID + "' ");
return ds;
}
catch
{
throw;
}
}
/// <summary>
/// 获取系统用户信息
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
string strEnv_SN = "";
string strApp_UserName = "";
if (m_request != null && m_request.Tables[0].Rows.Count > 0)
{
if (m_request.Tables[0].Columns.Contains("Env_SN"))
{
strEnv_SN = m_request.Tables[0].Rows[0]["Env_SN"].ToString();
}
if (m_request.Tables[0].Columns.Contains("App_UserName"))
{
strApp_UserName = m_request.Tables[0].Rows[0]["App_UserName"].ToString();
if (strApp_UserName != "")
{
strApp_UserName = "******" + strApp_UserName.Replace("'", "''").Replace(",", "','") + "'";
}
}
}
if (strApp_UserName == "")
{
throw new Exception("请输入用户名");
}
if (strEnv_SN == "")
{
throw new Exception("请输入环境简称");
}
Dbconn conn = new Dbconn("VexSSO");
DataSet ds = conn.GetDataSet(@"
SELECT
[App_UserID]
,[App_UserName]
,[App_UserName_CN]
,[Chang_Password_Time]
FROM [dbo].[MDM_System_User] with(nolock)
WHERE App_UserName in (" + strApp_UserName + @")
AND EXISTS(SELECT 1 FROM dbo.MDM_System_Env with(nolock)
WHERE Env_SN=@Param0
AND MDM_System_User.Env_ID=MDM_System_Env.Env_ID
)
", new string[1] { strEnv_SN });
return ds;
}
catch
{
throw;
}
}
/// <summary>
/// 根据XML内容询店铺查询
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
DataSet ds = new DataSet();
DataSet ds_Return = new DataSet();
DataSet dsRequest = new DataSet();
Dbconn conn = new Dbconn();
ArrayList listAMSendList = new ArrayList();
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
conn = new Dbconn("MDM");
//if (ds.Tables["LIST"].Rows[0]["CONO"].ToString().Trim() == "YL")
//{
// conn = new Dbconn("YL_F22");
//}
string strWHERE = " and 1=1";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @" SELECT
DEPOT.J_DQ,
DEPOT.J_DEPOTID,
SUBSTRING(POSUSER.J_NOTES,3,LEN(POSUSER.J_NOTES)) AS J_NOTES
FROM POSUSER,DEPOT
WHERE POSUSER.J_DEPOTID=DEPOT.J_DEPOTID " + strWHERE;
ds_Return = conn.GetDataSet(strSQL);
}
return ds_Return;
}
catch
{
throw;
}
}
public DataSet GetData()
{
Dbconn conn = new Dbconn("MDM");
DataSet ds = conn.GetDataSet(@"
SELECT [Env_ID] 环境ID,
[Env_SN] +' ' + [Env_Name] 环境名称
FROM dbo.MDM_System_Env WITH(NOLOCK)
ORDER BY 环境名称 ASC");
return ds;
}
public DataSet GetData()
{
Dbconn conn = new Dbconn("VexSSO");
DataSet ds = conn.GetDataSet(@"
SELECT [system_ID] 系统ID,
[system_code] +' ' + [System_Name] 系统编号
FROM dbo.MDM_System WITH(NOLOCK)
ORDER BY system_code ");
return ds;
}
public DataSet GetData()
{
Dbconn conn = new Dbconn("WSRR");
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataTable dt_EditResult = new dsCommon.dtResultDataTable();
DataSet dsRequest = new DataSet();
DataSet ds_Return = new DataSet();
dsRequest = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = dsRequest.Tables["OPTYPE"];
DataTable dt_LIST = dsRequest.Tables["LIST"];
for (int i = 0; i < dt_OPTYPE.Rows.Count; i++)
{
//获取用户权限信息
if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GET")
{
string strUserName = dt_LIST.Rows[0]["UserName"].ToString();
string strSQL = @"
SELECT DISTINCT
T1.[AUZID]
,T1.[AUZName]
,0 AS Authorization_ID_F
, CASE WHEN T2.USERNAME IS NULL THEN '0'
ELSE '1' END HavaFlag
FROM [WSRR_Manage].[F01_CERT].[AUZ] T1 WITH ( NOLOCK )
LEFT JOIN [WSRR_Manage].[F01_CERT].[UserAUZ] T2 WITH ( NOLOCK ) ON T1.AUZID=T2.AUZID
AND T2.USERNAME=@param0";
ds_Return = conn.GetDataSet(strSQL, new string[] { strUserName });
}
//更新用户权限
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "UPDATE")
{
string strUserName = dt_LIST.Rows[0]["UserName"].ToString();
conn.LockTableList.Add("[WSRR_Manage].[F01_CERT].[UserAUZ]");//锁定表
conn.BeginTransaction();
//1.删除该用户的所有权限
string strWhere = " UserName="******"[WSRR_Manage].[F01_CERT].[UserAUZ]", strWhere);
//2.新增勾选的权限
bool insertFlag = conn.Insert("[WSRR_Manage].[F01_CERT].[UserAUZ]", dt_LIST);
conn.CommitTransaction();
//删除操作和插入操作都正确的情况下
if (deleteFlag && insertFlag)
{
dt_EditResult.Rows.Add(new object[] { true, strUserName + "权限更新成功!" });
}
else
{
dt_EditResult.Rows.Add(new object[] { true, strUserName + "权限更新失败!" });
}
ds_Return.Tables.Add(dt_EditResult);
}
}
return ds_Return;
}
public DataSet GetData()
{
int nPage = 1;
int nPageRowCount = 1000000;
string strOrder = " DESC ";
string strSort = " System_ID";
string strWhere = " ";
if (m_request != null && m_request.Tables.Contains("Page_Info"))
{
nPage = Convert.ToInt32(m_request.Tables[0].Rows[0]["page"]);
nPageRowCount = Convert.ToInt32(m_request.Tables[0].Rows[0]["pagerows"]);
strOrder = m_request.Tables[0].Rows[0]["order"].ToString() != "" ? m_request.Tables[0].Rows[0]["order"].ToString() : strOrder;
strSort = m_request.Tables[0].Rows[0]["sort"].ToString() != "" ? m_request.Tables[0].Rows[0]["sort"].ToString() : strSort;
strWhere = m_request.Tables[0].Rows[0]["where"].ToString() != "" ? m_request.Tables[0].Rows[0]["where"].ToString() : strWhere;
if (strWhere.Trim() != "")
{
strWhere = " AND " + strWhere;
}
}
Dbconn conn = new Dbconn("MDM");
DataSet ds = conn.GetDataSet(@"
SELECT * FROM
(
SELECT
ROW_NUMBER()OVER(ORDER BY " + strSort + " " + strOrder + @") AS ROW_NUMBER_INDEX,
[System_ID],
[System_Code],
[System_Name],
[System_SN]
FROM [mdm].[dbo].[MDM_System]
WHERE 1=1 " + strWhere + @"
) t1
WHERE ROW_NUMBER_INDEX >= " + (nPage * nPageRowCount - nPageRowCount +1 ).ToString() + " AND ROW_NUMBER_INDEX<=" + (nPage * nPageRowCount).ToString() + @"
");
DataTable dt = conn.GetDataTable(@" SELECT
Count(1) as AllRowCount
FROM dbo.MDM_System WITH(NOLOCK)
WHERE 1=1 " + strWhere + @"", new string[0]);
dt.TableName = "PageRowCount";
ds.Tables.Add(dt.Copy());
return ds;
}
/// <summary>
/// 根据角色名称来获取所有用户,还具备其他的哪些权限
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
//这个类为了销售预算管理多加了一些功能,如果有通用的需求,需要重新修改:马卫清,2013-07-24
string strEnvSN = m_request.Tables[0].Rows[0]["EnvSN"].ToString().Trim();
string strAuthorizationName = m_request.Tables[0].Rows[0]["AuthorizationName"].ToString().Trim();
if (strEnvSN == "" || strAuthorizationName == "")
{
throw new Exception("参数不能为空");
}
Dbconn conn = new Dbconn("VexSSO");
DataSet ds = conn.GetDataSet(@"
SELECT t4.VexSSONewID,t4.VexSSOLoginUser
INTO #tmp
FROM dbo.MDM_System_User_Authorization t1
JOIN dbo.MDM_System_Authorization t2 ON t1.Authorization_ID = t2.Authorization_ID
JOIN dbo.MDM_System_Env t3 ON t2.Env_ID = t3.Env_ID
JOIN B01_MDM.VEXSSOUser t4 ON t1.VexSSONewID = t4.VexSSONewID
WHERE Env_SN = @param0
AND Authorization_Name = @param1
SELECT DISTINCT #tmp.*,t2.Authorization_Name FROM #tmp
LEFT JOIN dbo.MDM_System_User_Authorization t1 ON t1.VexSSONewID=#tmp.VexSSONewID
LEFT JOIN dbo.MDM_System_Authorization t2 ON t1.Authorization_ID = t2.Authorization_ID
LEFT JOIN dbo.MDM_System_Env t3 ON t2.Env_ID = t3.Env_ID
WHERE Env_SN =@param0
AND Authorization_Name <> @param1
ORDER BY 2
DROP TABLE #tmp
", new string[2] { strEnvSN, strAuthorizationName });
return ds;
}
catch
{
throw;
}
}
/// <summary>
/// 获取SessionID获取MKT的Body权限表
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
string strMarketTableName = m_request.Tables[0].Rows[0]["MarketTableName"].ToString();
this.GetAUZList();
if (m_UpdateAUZ == "")
{
throw new Exception("您没有任何权限");
}
Dbconn conn = new Dbconn("MDM_Master");
DataSet returnDs = conn.GetDataSet(@"
SELECT Main_Entity ,
Entity ,
CASE WHEN " + m_UpdateAUZ + @" THEN 1
ELSE 0
END AS Update_AUZ,
CASE WHEN " + m_InsertAUZ + @" THEN 1
ELSE 0
END AS Insert_AUZ,
CASE WHEN " + m_DeleteAUZ + @" THEN 1
ELSE 0
END AS Delete_AUZ,
CASE WHEN " + m_ViewAUZ + @" THEN 1
ELSE 0
END AS ViewAUZ
FROM [MDM_Master].[dbo].[Lib_AUZ_Body] t1
LEFT JOIN dbo.Lib_View_Table t2 ON t1.Main_Entity = t2.MainEntity
WHERE Table_Name = 'Store_For_FM'
AND ( " + m_UpdateAUZ + @"
OR " + m_ViewAUZ + @"
OR " + m_InsertAUZ + @"
OR " + m_DeleteAUZ + @"
)
");
return returnDs;
}
catch
{
throw;
}
}
/// <summary>
/// 根据XML内容查询高单奖金
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
DataSet ds = new DataSet();
DataSet ds_Return = new DataSet();
DataSet dsRequest = new DataSet();
ArrayList listAMSendList = new ArrayList();
Dbconn conn = new Dbconn("X6_EPApp");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "AND 1=1";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @" SELECT B.WS_EMPLOYEEID,A.WP_COSTCENTER,
left(CONVERT(VARCHAR(100),DateAdd(m,-1,A.WP_DATE),121),7)as DATES,
'高单奖金' as GDJJ,B.WS_TMONEY
FROM
TB_FYSP_WAGEPAYMENTS A,TB_FYSP_WAGEPAYMENTSSUB B
WHERE A.WP_FID=B.WS_MASTERID " + strWHERE
;
ds_Return = conn.GetDataSet(strSQL);
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 编辑用户信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM_Master");
Dbconn conn_Market = new Dbconn("MDM_Market");
try
{
string strexpression_id = m_request.Tables[0].Rows[0]["expression_id"].ToString();
string strbody_id = m_request.Tables[0].Rows[0]["body_id"].ToString();
string struser_login_name = m_request.Tables[0].Rows[0]["user_login_name"].ToString();
string struser_name = m_request.Tables[0].Rows[0]["user_name"].ToString();
string struser_notuse = m_request.Tables[0].Rows[0]["user_notuse"].ToString();
string strAction = m_request.Tables[0].Rows[0]["Action"].ToString();
DataSet ds = conn_Market.GetDataSet(@"SELECT [Body_ID] FROM [MKT_User_CN_For_HZY_F22] WHERE
User_Login_Name=@Param0",new string[1]{struser_login_name});
//如果是添加，先核对用户ID对不对
if (strAction == "Add" && ds.Tables[0].Rows.Count>0)
{
throw new Exception("要添加的用户登录名已经存在，请核对");
}
if (strAction == "Add")
{
strbody_id = conn.GetDataTable("SELECT NEWID() as ID", new string[0]).Rows[0][0].ToString();
conn.GetDataSetBySP("SP_CreateMasterData", new string[2] { "@MDMName", "@bodyID" }, new string[2] { "User", strbody_id });
AddNewRow(conn, strbody_id, struser_name, struser_login_name, struser_notuse);
}
else
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
strbody_id = ds.Tables[0].Rows[i]["Body_ID"].ToString();
AddNewRow(conn, strbody_id, struser_name, struser_login_name, struser_notuse);
}
}
return true;
}
catch
{
throw;
}
}
/// <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;
}
}
public DataSet GetData()
{
try
{
string strWhere = string.Empty;
string strSearchFilter = " ";
if (m_request != null)
{
//strWhere = m_request.Tables[0].Rows[0]["where"].ToString() != "" ? m_request.Tables[0].Rows[0]["where"].ToString() : strWhere;
//if (m_request.Tables[0].Columns.Contains("searchFilter"))
//{
// strSearchFilter = m_request.Tables[0].Rows[0]["searchFilter"].ToString() != "" ? m_request.Tables[0].Rows[0]["searchFilter"].ToString() : strSearchFilter;
//}
//if (strWhere.Trim() != "")
//{
// strWhere = " AND " + strWhere;
//}
//if (strSearchFilter.Trim() != "")
//{
// strWhere = " AND " + strSearchFilter;
//}
}
Dbconn conn = new Dbconn("WSRR");
string strSQL = @"
SELECT [WSID]
,[ServicePath]
,[WSName]
FROM [WSRR_Manage].[B01_MDM].[WS0_WSMainDoc] WITH ( NOLOCK ) WHERE 1=1 " + strWhere +
"order by updatetime desc";
DataSet ds = conn.GetDataSet(strSQL);
return ds;
}
catch
{
throw;
}
}
/// <summary>
/// 根据权限获取MDM实体权限表
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
string strAUZ = m_request.Tables[0].Rows[0]["AUZ"].ToString();
string strMianEntity = m_request.Tables[0].Rows[0]["Entity"].ToString();
Dbconn conn = new Dbconn("MDM_Master");
DataSet returnDs = conn.GetDataSet(@"
DECLARE @str VARCHAR(max)
SELECT Entity
INTO #tmp
FROM dbo.Lib_AUZ_Body WITH(NOLOCK)
WHERE Insert_AUZ LIKE '%【" + strAUZ + @"】%'
AND Main_Entity = '" + strMianEntity + @"'
AND Entity NOT LIKE 'L%'
UNION ALL
SELECT Ref_Base_Entity
FROM dbo.Lib_MDM_Config WITH(NOLOCK)
WHERE MDM_Entity IN ( SELECT Entity
FROM dbo.Lib_AUZ_Body WITH(NOLOCK)
WHERE Insert_AUZ LIKE '%【" + strAUZ + @"】%'
AND Main_Entity = '" + strMianEntity + @"'
AND Entity LIKE 'L%' )
SELECT @str = ISNULL(@str,'')+',' + Entity FROM #tmp
DROP TABLE #tmp
SELECT STUFF(@str, 1, 1, '') AS Entity
");
return returnDs;
}
catch
{
throw;
}
}
/// <summary>
/// 获取MDM实体的列表树
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
Dbconn conn = new Dbconn("MDM_Market2");
DataSet returnDs = conn.GetDataSet(@"
SELECT 组织单元系统编号,REPLACE(名称,'&','&') 名称 ,上级组织单元系统编号,t1.Body_ID,t2.成本中心ID,t2.Body_ID ORGBody_ID
FROM [dbo].[MKT_0020] t1
LEFT JOIN [dbo].[MKT_0019] t2 ON t2.系统编号 = t1.组织单元系统编号
WHERE t1.是否有效 = '1'
");
return returnDs;
}
catch
{
throw;
}
}
private void Getdata()
{
string strConnstr = ConfigurationManager.AppSettings["ConnectionString_POS"];
Dbconn conn = new Dbconn(strConnstr, true);
string strPhone = this.txtPhone.Text;
if (strPhone.Trim() != "")
{
string strName = conn.GetDataTableFirstValue(@"SELECT vipname as 姓名, mobtel as 手机号, vipcode as 卡号 FROM vip_user WHERE mobtel=@Param0 OR vipcode=@Param0", new string[] { strPhone }).ToString();
this.lblName.Text = strName;
DataSet ds = conn.GetDataSet(@"SELECT b.ticketid AS 券号 ,
a.sums AS 面值 ,
CASE ISNULL(b.getsure,0)WHEN 0 THEN '否' ELSE '是' END AS 是否使用 ,
b.crdate AS 创建日期 ,
b.verifycode AS 验证码,a.mobtel, a.vipcode, a.vipid
FROM vip_user a
INNER JOIN vip_ticket b ON a.vipid = b.vipid
WHERE b.tictypeid = '31'
AND (a.mobtel=@param0 OR a.vipcode=@param0)
ORDER BY b.crdate DESC
", new string[] { strPhone });
RpList.DataSource = ds.Tables[0];
RpList.DataBind();
if (ds.Tables[0].Rows.Count == 0)
{
this.lblMessage.Text = "<font color='red'>没有查询到手机号/卡号为" + strPhone + "的相关信息，请重试</font>";
}
else
{
this.lblMessage.Text = "";
}
}
else
{
this.lblMessage.Text = "";
RpList.DataSource = new DataTable();
RpList.DataBind();
}
}
public DataSet GetData()
{
string strTid = "";
try
{
if (m_request != null && m_request.Tables.Count > 0)
{
strTid = m_request.Tables[0].Rows[0]["tid"].ToString();
}
if (strTid == "")
{
throw new Exception("tid为空");
}
Dbconn conn = new Dbconn("X6_EPApp");
DataSet ds = conn.GetDataSet(@"
SELECT fid ,
tid ,
fTaskID ,
fProjectName ,
fPrincipal ,
fPlanningFinishDate ,
fActualFinishDate ,
fDays ,
fDifficultyLevel ,
fState ,
fGrade ,
fProgressDescription ,
fConfirmNote
FROM dbo.IT_TASKNODE WITH ( NOLOCK )
WHERE tid = '" + strTid + @"'
ORDER BY fTaskNumber ASC
");
return ds;
}
catch
{
throw;
}
}
/// <summary>
/// 根据Entity的编号获取MDM中Entity列的配置
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
DataSet ds = new DataSet();
DataSet dsRequest = new DataSet();
string strMainEntity = "";
if (m_request != null)
{
strMainEntity = m_request.Tables[0].Rows[0]["Entity"].ToString();
}
else
{
strMainEntity = m_MainEntity;
}
if (strMainEntity == "")
{
throw new Exception("实体编号不能为空！");
}
//获取所有的列
Dbconn conn = new Dbconn("MDM_Master2");
ds = conn.GetDataSet(@"
SELECT Property_Name FROM dbo.Lib_ExdPropertyList
WHERE MDM_Entity='" + strMainEntity + @"'
");
return ds;
}
catch
{
throw;
}
}
public DataSet GetData()
{
Dbconn conn = new Dbconn("HZY_Mat");
string strSQL = @"
SELECT
[Inventort_Authority_ID] ,
[Store_prefix] ,
[AreaName] ,
[Branch_POS] ,
[Branch_AM] ,
[General_POS] ,
[General_AM] ,
[Financial_POS] ,
[Financial_AM]
FROM [inventory].[Inventort_Authority]
where 1=1 ";
DataSet ds = conn.GetDataSet(strSQL);
return ds;
}
