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);
}
}
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 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;
}
}
protected void GetData()
{
//Dbconn conn = new Dbconn("ConnectionString_CUSSMS");
string strConnstr = ConfigurationManager.AppSettings["ConnectionString_CUSSMS"];
Dbconn conn = new Dbconn(strConnstr, true);
string strMessage = "";
string strPhone = this.txtPhone.Text;
if (strPhone.Trim() != "")
{
DataTable dt = conn.GetDataTable(@"SELECT [VM1_CONO]
,[VM1_DIVI]
,[VM1_Ticketid]
,[VM1_Mobtel]
,[VM1_Rgdt]
,[VM1_LMDT]
FROM [CUSSMS].[B01_MDM].[VM1_VipticktMotelRecord]
WHERE VM1_Mobtel=@Param0
AND VM1_CONO=100
AND VM1_DIVI='D01'", new string[1] {
strPhone
});
if (dt.Rows.Count > 0)
{
strMessage = "<div>查询结果<p>";
strMessage += "手 机 号：" + dt.Rows[0]["VM1_Mobtel"].ToString() + "<p>";
strMessage += "券 号：" + dt.Rows[0]["VM1_Ticketid"].ToString() + "<p>";
strMessage += "申请时间：" + dt.Rows[0]["VM1_LMDT"].ToString() + "<p></div>";
}
else
{
strMessage = "<font color='red'>没有查询到手机号为" + strPhone + "的相关信息，请重试</font>";
}
}
else
{
strMessage = "<font color='red'>手机号不能为空</font>";
}
this.lblMessage.Text = strMessage;
}
protected void GetData()
{
//Dbconn conn = new Dbconn("ConnectionString_CUSSMS");
string strConnstr = ConfigurationManager.AppSettings["ConnectionString_CUSSMS"];
Dbconn conn = new Dbconn(strConnstr, true);
string strMessage = "";
string strPhone = this.txtPhone.Text;
if (strPhone.Trim() != "")
{
DataTable dt = conn.GetDataTable(@"SELECT [VM1_CONO]
,[VM1_DIVI]
,[VM1_Ticketid]
,[VM1_Mobtel]
,[VM1_Rgdt]
,[VM1_LMDT]
FROM [CUSSMS].[B01_MDM].[VM1_VipticktMotelRecord]
WHERE VM1_Mobtel=@Param0
AND VM1_CONO=100
AND VM1_DIVI='D01'", new string[1] { strPhone });
if (dt.Rows.Count > 0)
{
strMessage = "<div>查询结果<p>";
strMessage += "手 机 号：" + dt.Rows[0]["VM1_Mobtel"].ToString() + "<p>";
strMessage += "券 号：" + dt.Rows[0]["VM1_Ticketid"].ToString() + "<p>";
strMessage += "申请时间：" + dt.Rows[0]["VM1_LMDT"].ToString() + "<p></div>";
}
else
{
strMessage = "<font color='red'>没有查询到手机号为" + strPhone + "的相关信息，请重试</font>";
}
}
else
{
strMessage = "<font color='red'>手机号不能为空</font>";
}
this.lblMessage.Text = strMessage;
}
/// <summary>
/// VEX框架的主入口
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
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 ,
k_num AS totalInventory
FROM dbo.j_stock
WHERE depotid in (" + strDepotID + @")
AND k_num > 0
";
m_dsRequturn = m_conn.GetDataSet(strSQL);
return m_dsRequturn;
}
public bool Execute()
{
try
{
conn_MDM = new Dbconn("MDM");
DataTable dt_user = conn_MDM.GetDataTable(@"
SELECT DISTINCT AM_login FROM dbo.AM_User t1 WITH(NOLOCK)
JOIN dbo.AM_User_Dept t2 WITH(NOLOCK) ON t1.AM_User_id = t2.AM_USER_ID
JOIN dbo.AM_Dept t3 WITH(NOLOCK) ON t2.AM_S_Dept_ID = t3.AM_S_Dept_ID
WHERE AM_Company='235'
", new string[0]);
//目前只更新雅莹的数据
ldap = new Common.LdapConnect("192.168.0.171:389", "cn=root", "elegant.admin");
ldap = new Common.LdapConnect("172.16.205.73:389", "cn=root", "elegant");
ldap.TargetOU = "dc=hwayifashiongroup,dc=com";
ldap.TargetOU = "dc=elegant-prosper,dc=com,dc=cn";
if (!ldap.ConnectLDAP())
{
return false;
}
for (int i = 0; i < dt_user.Rows.Count; i++)
{
string strUser = dt_user.Rows[i]["AM_login"].ToString();
DirectoryAttribute[] dir = new DirectoryAttribute[4];
dir[0] = new DirectoryAttribute("cn", strUser);
dir[1] = new DirectoryAttribute("sn", strUser);
dir[2] = new DirectoryAttribute("objectClass", "inetOrgPerson");
dir[3] = new DirectoryAttribute("userPassword", "111111");
ldap.LadpAdd("uid=" + strUser + ",ou=peoples", dir).ToString();
}
//取二级部门是室和部结尾的所有雅莹的数据
DataTable dt_Groups = conn_MDM.GetDataTable(@"
SELECT t3.AM_S_dept_ID,
t3.AM_Dept_Name_All,
t3.AM_Dept_Name
FROM dbo.AM_Dept t3
LEFT JOIN dbo.AM_Dept t4 WITH ( NOLOCK ) ON t3.AM_Dept1_ID = t4.AM_S_Dept_ID
WHERE t3.AM_Company = '235'
AND ( t4.AM_Dept1_ID IS NULL
OR t4.AM_Dept_NAME LIKE '%室'
OR t4.AM_Dept_NAME LIKE '%部'
)
ORDER BY t3.OrderIndex
", new string[0]);
for (int i = 0; i < dt_Groups.Rows.Count; i++)
{
//添加组织架构
string strDEPT_Name = dt_Groups.Rows[i]["AM_Dept_Name"].ToString();
string strDEPT_Name_All = dt_Groups.Rows[i]["AM_Dept_Name_All"].ToString();
string[] strDEPTList = strDEPT_Name_All.Split('\\');
string strOU = "";
for (int j = strDEPTList.Length-1; j >=0 ; j--)
{
strOU += "cn="+strDEPTList[j]+",";
}
strOU += "ou=groups";
DirectoryAttribute[] dir = new DirectoryAttribute[2];
dir[0] = new DirectoryAttribute("cn", strDEPT_Name);
dir[1] = new DirectoryAttribute("objectClass", "container");
ldap.LadpAdd(strOU, dir).ToString();
}
//组织架构下面的成员
DataTable dt_Dept_User = conn_MDM.GetDataTable(@"
SELECT DISTINCT
AM_login ,
t3.AM_S_Dept_ID ,
t3.AM_p_Dept_ID ,
t3.AM_Dept_NAME ,
t3.AM_Dept_NAME_All
FROM dbo.AM_User t1 WITH ( NOLOCK )
JOIN dbo.AM_User_Dept t2 WITH ( NOLOCK ) ON t1.AM_User_id = t2.AM_USER_ID
JOIN dbo.AM_Dept t3 WITH ( NOLOCK ) ON t2.AM_S_Dept_ID = t3.AM_S_Dept_ID
LEFT JOIN dbo.AM_Dept t4 WITH ( NOLOCK ) ON t3.AM_Dept1_ID = t4.AM_S_Dept_ID
WHERE t3.AM_Company = '235'
AND ( t4.AM_Dept1_ID IS NULL
OR t4.AM_Dept_NAME LIKE '%室'
OR t4.AM_Dept_NAME LIKE '%部'
)
", new string[0]);
for (int i = 0; i < dt_Dept_User.Rows.Count; i++)
{
//添加组织架构下的成员
string strUser = dt_Dept_User.Rows[i]["AM_login"].ToString();
string strDEPT_Name = dt_Dept_User.Rows[i]["AM_Dept_Name"].ToString();
string strDEPT_Name_All = dt_Dept_User.Rows[i]["AM_Dept_Name_All"].ToString();
//string strOU = "uid=" + strUser + ",cn=" + strDEPT_Name_All.Replace("\\", ",cn=") + ",ou=groups," + ldap.TargetOU;
string[] strDEPTList = strDEPT_Name_All.Split('\\');
string strOU = "uid=" + strUser + ",";
for (int j = strDEPTList.Length - 1; j >= 0; j--)
{
strOU += "cn=" + strDEPTList[j] + ",";
}
strOU += "ou=groups";
DirectoryAttribute[] dir = new DirectoryAttribute[3];
dir[0] = new DirectoryAttribute("cn", strUser);
dir[1] = new DirectoryAttribute("sn", strUser);
dir[2] = new DirectoryAttribute("objectClass", "inetOrgPerson");
ldap.LadpAdd(strOU, dir).ToString();
}
return true;
}
catch
{
throw;
}
}
/// <summary>
/// 取到系统属性值的交叉表
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
//由于分页实现比较麻烦，目前只对行数进行控制，没有控制筛选和排序功能
int nPage = 1;
int nPageRowCount = 20;
string strOrder = " ASC ";
string strSort = " System_CODE";
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 (m_request.Tables[0].Rows[0]["where"].ToString().Trim() != ""
&& m_request.Tables[0].Rows[0]["where"].ToString().Trim().Length > 3
&& m_request.Tables[0].Rows[0]["where"].ToString().Substring(0, 3) == "col")
{
m_request.Tables[0].Rows[0]["where"] = "";
m_request.Tables[0].Rows[0]["page"] = 1;
m_request.Tables[0].Rows[0]["pagerows"] = 10000;
}
}
strOrder = " ASC ";
Dbconn conn = new Dbconn("MDM");
//取到系统列表(已将分页信息传入)
MDM_System_GetList sysList = new MDM_System_GetList();
sysList.Request = m_request;
DataSet ds_System = sysList.GetData();
//取到属性表
MDM_System_Property_GetList sysPorList = new MDM_System_Property_GetList();
DataSet ds_Property = sysPorList.GetData();
DataSet ds = new DataSet("Data");
string strSystem_IDList = "0";
for (int i = 0; i < ds_System.Tables[0].Rows.Count; i++)
{
if (strSystem_IDList.Length>0)
{
strSystem_IDList += ",";
}
strSystem_IDList += ds_System.Tables[0].Rows[i]["System_ID"].ToString();
}
//取到属性值数据
DataTable dt = conn.GetDataTable(@" SELECT [Property_Info_ID]
,[System_ID]
,[System_Code]
,[Property_ID]
,[Property_Text]
FROM [MDM].[dbo].[MDM_System_Property_Info] WITH(NOLOCK)
Where System_ID in (" + strSystem_IDList + ")", new string[0]);
//添加列:
for (int i = 0; i < ds_Property.Tables[0].Rows.Count; i++)
{
ds_System.Tables[0].Columns.Add("col"+ds_Property.Tables[0].Rows[i]["Property_ID"].ToString());
}
//将值放进去
for (int i = 0; i < dt.Rows.Count; i++)
{
string strSystem_ID = dt.Rows[i]["System_ID"].ToString();
string strProperty_ID = "col" + dt.Rows[i]["Property_ID"].ToString();
DataRow[] dr = ds_System.Tables[0].Select("System_ID=" + strSystem_ID);
if (dr.Length > 0 && ds_System.Tables[0].Columns.Contains(strProperty_ID))
{
dr[0][strProperty_ID] = dt.Rows[i]["Property_Text"].ToString();
}
}
DataSet ds_return = new DataSet();
DataTable dt1 = ds_System.Tables[0].Clone();
if (strWhere.Trim() != "")
{
DataRow[] dr1 = ds_System.Tables[0].Select(strWhere);
for (int i = (nPage-1)*nPageRowCount; i < dr1.Length; i++)
{
if (i == nPage * nPageRowCount-1)
{
break;
}
dt1.ImportRow(dr1[i]);
}
ds_return.Tables.Add(dt1);
ds_System.Tables["PageRowCount"].Rows[0][0] = dr1.Length;
}
else
{
ds_return.Tables.Add(ds_System.Tables["Data"].Copy());
}
ds_return.Tables.Add(ds_System.Tables["PageRowCount"].Copy());//分页信息
ds_System = null;
return ds_return;
}
/// <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("GL_ERP");
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 = "ISNULL(PR0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
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
[PR0_CONO]
,[PR0_DIVI]
,[PR0_ContractId]
,[PR0_ContractCode]
,[PR0_SU0_SupplierId]
,CAST([PR0_Amount] AS DECIMAL(20,2)) as PR0_Amount
,[PR0_CurrencyId]
,[PR0_Description]
,[PR0_Status]
,[PR0_ECopyPath]
,[PR0_PrePayRate]
,LEFT(CONVERT(VARCHAR(20),[PR0_StartDate],121),10) AS PR0_StartDate
,LEFT(CONVERT(VARCHAR(20),[PR0_EndDate],121),10) AS PR0_EndDate
,[PR0_RgUser]
,[PR0_RgDt]
,[PR0_LmUser]
,[PR0_LmDt]
,[PR0_UptNo]
,[PR0_SettleAccount]
,[PR0_Total]
,A.[SU0_SupplierCode] as SU0_SupplierCode
,A.[SU0_SupplierName] as SU0_SupplierName
,B.WFT_NODE AS WFT_NODE1
,C.WFT_NODE AS WFT_NODE2
,D.WFT_NODE AS WFT_NODE3
,E.WFT_NODE AS WFT_NODE4
,F.WFT_NODE AS WFT_NODE5
,L.DC0_DICTNAME1 AS DC0_DICTNAME1
,M.DC0_DICTNAME1 AS DC0_DICTNAME2
,N.DC0_DICTNAME1 AS DC0_DICTNAME3
,O.DC0_DICTNAME1 AS DC0_DICTNAME4
,P.DC0_DICTNAME1 AS DC0_DICTNAME5
,G.WFP_AuditTime AS WFP_AuditTime1
,H.WFP_AuditTime AS WFP_AuditTime2
,I.WFP_AuditTime AS WFP_AuditTime3
,J.WFP_AuditTime AS WFP_AuditTime4
,K.WFP_AuditTime AS WFP_AuditTime5
,G.WFP_AFID AS WFP_AFID1
,H.WFP_AFID AS WFP_AFID2
,I.WFP_AFID AS WFP_AFID3
,J.WFP_AFID AS WFP_AFID4
,K.WFP_AFID AS WFP_AFID5
,Q.[DC0_DictName1] AS CurrencyDictName
,R.[DC0_DictName1] AS StatusDictName
,S.[DC0_DictName1] AS AccountDictName
FROM B02_Bill.PR0_Contract WITH ( NOLOCK )
LEFT JOIN B01_MDM.SU0_Supplier A ON PR0_SU0_SupplierId=A.SU0_SupplierId
LEFT JOIN M02_BPM.WFT_AuditFlowDefine B ON B.WFT_Type='Contract' and B.WFT_Step='1'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine C ON C.WFT_Type='Contract' and C.WFT_Step='2'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine D ON D.WFT_Type='Contract' and D.WFT_Step='3'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine E ON E.WFT_Type='Contract' and E.WFT_Step='4'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine F ON F.WFT_Type='Contract' and F.WFT_Step='5'
LEFT JOIN M02_BPM.WFP_AuditFlow G ON PR0_ContractId=G.WFP_BillId AND G.WFP_WFT_AFDId=B.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow H ON PR0_ContractId=H.WFP_BillId AND H.WFP_WFT_AFDId=C.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow I ON PR0_ContractId=I.WFP_BillId AND I.WFP_WFT_AFDId=D.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow J ON PR0_ContractId=J.WFP_BillId AND J.WFP_WFT_AFDId=E.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow K ON PR0_ContractId=K.WFP_BillId AND K.WFP_WFT_AFDId=F.WFT_AFDId
LEFT JOIN M01_CONF.DC0_Dictionary L ON G.WFP_AFResultId=L.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary M ON H.WFP_AFResultId=M.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary N ON I.WFP_AFResultId=N.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary O ON J.WFP_AFResultId=O.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary P ON K.WFP_AFResultId=P.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary Q ON PR0_CurrencyId=Q.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary R ON PR0_Status=R.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary S ON PR0_SettleAccount=S.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [PR0_LmDt] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PR0_Contract");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PR0_Contract SET PR0_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PR0_ContractId='" + ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() + @"'
AND ISNULL(PR0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.PR0_Contract WHERE PR0_ContractId=@param0
AND ISNULL(PR0_Status,'') <>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
strSQL = @"SELECT * FROM M02_BPM.WFP_AuditFlow WHERE WFP_BillId=@param0
AND WFP_AFResultId<>''
";
DataTable dt_CO = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() });
//update
if (dt_CO.Rows.Count > 0)
{
throw new Exception("合同已审核，不能进行修改！");
}
else
//处理结果
if (ds.Tables["LIST"].Rows[i]["PR0_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PR0_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PR0_LmDt"))
{
dt.Columns.Add("PR0_LmDt");
}
// if (!dt.Columns.Contains("PR0_Status"))
// {
// dt.Columns.Add("PR0_Status");
// }
if (!dt.Columns.Contains("PR0_UptNo"))
{
dt.Columns.Add("PR0_UptNo");
}
// dt.Rows[0]["PR0_Status"] = '0';
dt.Rows[0]["PR0_LmDt"] = DateTime.Now;
dt.Rows[0]["PR0_UptNo"] = strCode;
}
conn.Update("B02_Bill.PR0_Contract", dt, "PR0_ContractId='" + ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() + @"'");
}
else //add
{
DataTable dtTemp = conn.GetDataTableInTrans(
@"SELECT ISNULL(MAX(PR0_ContractCode),'') AS MaxCode,
RIGHT(left( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2)+'-'+
LEFT(RIGHT( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2) as PreCode FROM B02_Bill.PR0_Contract where
left(PR0_ContractCode,'2')=RIGHT(left( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2)
AND left(right(PR0_ContractCode,'4'),'2')=LEFT(RIGHT( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2) ");
//供应商编号生成
string strCode = dtTemp.Rows[0]["MaxCode"].ToString();
if (strCode == "")
{
strCode = dtTemp.Rows[0]["PreCode"].ToString() + "01";
// strCode = strCode.Substring(strCode.Substring(DateTime.Now.ToString().Length + 4).Length + 4)
// RIGHT(left(DateTime.Now.ToString(), 4), 2) + "-" + RIGHT(left(DateTime.Now.ToString(), 7), 2) + "01";
//dtTemp.Rows[0]["PreCode"].ToString() + "0001";
}
else
{
strCode = dtTemp.Rows[0]["PreCode"].ToString() + (Convert.ToInt32(strCode.Substring(strCode.Length - 2)) + 1).ToString("00");
//strCode = left(strCode, 2) + "-" + left(RIGHT(strCode, 4), 2) + (Convert.ToInt32(strCode.Substring(strCode.Length - 2)) + 1).ToString("00");
// dtTemp.Rows[0]["PreCode"].ToString() + (Convert.ToInt32(strCode.Substring(strCode.Length - 4)) + 1).ToString("0000");
}
string PR0_ContractId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("PR0_ContractId"))
{
dt.Columns.Add("PR0_ContractId");
}
// if (!dt.Columns.Contains("PR0_Status"))
// {
// dt.Columns.Add("PR0_Status");
// }
if (!dt.Columns.Contains("PR0_RgDt"))
{
dt.Columns.Add("PR0_RgDt");
}
if (!dt.Columns.Contains("PR0_LmDt"))
{
dt.Columns.Add("PR0_LmDt");
}
if (!dt.Columns.Contains("PR0_ContractCode"))
{
dt.Columns.Add("PR0_ContractCode");
}
if (!dt.Columns.Contains("PR0_UptNo"))
{
dt.Columns.Add("PR0_UptNo");
}
dt.Rows[0]["PR0_ContractId"] = PR0_ContractId;
// dt.Rows[0]["PR0_Status"] = '0';
dt.Rows[0]["PR0_RgDt"] = DateTime.Now;
dt.Rows[0]["PR0_LmDt"] = DateTime.Now;
dt.Rows[0]["PR0_ContractCode"] = strCode;
dt.Rows[0]["PR0_UptNo"] = '0';
conn.Insert("B02_Bill.PR0_Contract", dt);
GL_ERP_Common clsCommon = new GL_ERP_Common();
GL_ERP_Common.CreateWorkFlow(conn, "Contract", ds.Tables["LIST"].Rows[i]["PR0_CONO"].ToString(), ds.Tables["LIST"].Rows[i]["PR0_DIVI"].ToString(), PR0_ContractId,
"Contract", "", "", ds.Tables["LIST"].Rows[i]["PR0_Status"].ToString(), ds.Tables["LIST"].Rows[i]["PR0_RgUser"].ToString(),
ds.Tables["LIST"].Rows[i]["PR0_LmUser"].ToString(), "", "PR0_ContractId", "B02_Bill.PR0_Contract");
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("iCollecter");
DataSet ds = conn.GetDataSet(
@" SELECT AMLoginName AS CN_LOGIN,TfgMachineNo,ComputerName
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY TfgMachineNo ORDER BY collectDate DESC ) AS RowIndex ,
ComputerName ,
AMLoginName,
TfgMachineNo
FROM dbo.AMAndTFG WITH(NOLOCK)
WHERE 1 = 1
AND ISNULL(AMLoginUsers, '') <> ''
) t
WHERE RowIndex = 1
");
Dbconn connMDM = new Dbconn("MDM");
//获取用户ID
ds.Tables[0].Columns.Add("AM_User_id", typeof(Int32));
ds.Tables[0].AcceptChanges();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string[] strParm = new string[1];
strParm[0]=ds.Tables[0].Rows[i]["CN_LOGIN"].ToString();
DataTable dt = connMDM.GetDataTable("SELECT AM_User_id FROM AM_User WHERE AM_login=@Param0", strParm);
if (dt.Rows.Count > 0)
{
ds.Tables[0].Rows[i]["AM_User_id"] = dt.Rows[0]["AM_User_id"];
}
else
{
ds.Tables[0].Rows[i].Delete();
}
}
ds.Tables[0].Columns.Remove("CN_LOGIN");
ds.Tables[0].AcceptChanges();
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("AM_TFG_Relation_log");
tableList.Add("AM_TFG_Relation");
connMDM.TableLock(tableList);
//存入日志数据
connMDM.ExcuteQuerryByTran(@"INSERT INTO dbo.AM_TFG_Relation_log
( Log_datetime ,
AM_User_id ,
TfgMachineNo ,
ComputerName ,
CreateTime ,
UpdateTime ,
Timestamps_old
)
SELECT GETDATE() ,
AM_User_id ,
TfgMachineNo ,
ComputerName ,
CreateTime ,
UpdateTime,
Convert(bigint,Timestamps)
FROM AM_TFG_Relation;");
//删除数据
connMDM.ExcuteQuerryByTran("DELETE FROM AM_TFG_Relation");
connMDM.Insert("AM_TFG_Relation", ds.Tables[0]);
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
private void FindFatherMenu(DataSet ds, Dbconn conn)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string strMenu_Father_ID = ds.Tables[0].Rows[i]["Menu_Father_ID"].ToString();
DataRow[] dr = ds.Tables[0].Select("Menu_ID=" + strMenu_Father_ID); //找到是否已经存在父节点
if (dr.Length > 0)
{
continue;
}
DataTable dt = conn.GetDataTable(@"
SELECT Menu_Id ,
Menu_Code ,
Menu_Name ,
Menu_Father_ID ,
ActionURL
FROM dbo.MDM_System_Menu
WHERE Menu_ID='" + strMenu_Father_ID + @"'
", new string[0]);
if (dt.Rows.Count > 0)
{
ds.Tables[0].ImportRow(dt.Rows[0]);
}
}
}
/// <summary>
/// 获取系统环境信息
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
int nPage = 1;
int nPageRowCount = 20;
string strOrder = " desc ";
string strSort = " Env_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,
t1.[Authorization_ID] ,
t1.[Env_ID] ,
t3.[Env_SN] ,
t3.[Env_Name] ,
t1.[Authorization_Name] ,
t1.[Createtime] ,
t1.[Updatetime] ,
CONVERT(BIGINT, t1.[Timestamps]) Timestamps
FROM [dbo].MDM_System_Authorization t1 WITH ( NOLOCK )
LEFT JOIN dbo.MDM_System_Env t3 WITH ( NOLOCK ) ON t3.Env_ID=t1.Env_ID
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_Authorization t1 WITH ( NOLOCK )
LEFT JOIN dbo.MDM_System_Env t3 WITH ( NOLOCK ) ON t3.Env_ID=t1.Env_ID
WHERE 1=1 " + strWhere + @"", new string[0]);
dt.TableName = "PageRowCount";
ds.Tables.Add(dt.Copy());
return ds;
}
catch
{
throw;
}
}
public static void UpdateFullText(string pstrWSID, Dbconn pconn)
{
string strSQL = @"
DECLARE @cols NVARCHAR(MAX)
SET @cols = ''
SELECT @cols = @cols + 'convert(varchar(1000),isnull(' + name
+ ',''''),21)+'' ''+'
FROM syscolumns
WHERE ( id = ( SELECT id
FROM sysobjects
WHERE ( name = 'WS0E_WSExtDoc' )
) )
SELECT @cols as A
";
string strInfoColumns = pconn.GetDataTableFirstValue(strSQL).ToString();
strSQL = strInfoColumns.Substring(0, strInfoColumns.Length - 1);
strSQL = "SELECT " + strSQL + " FROM [B01_MDM].[WS0E_WSExtDoc] WHERE WSID=@Param0";
string strValue = pconn.GetDataTableFirstValue(strSQL, new string[] { pstrWSID }).ToString();
strSQL = @"
DECLARE @cols NVARCHAR(MAX)
SET @cols = ''
SELECT @cols = @cols + 'convert(varchar(1000),isnull(' + name
+ ',''''),21)+'' ''+'
FROM syscolumns
WHERE ( id = ( SELECT id
FROM sysobjects
WHERE ( name = 'WS0M_WSMethodDoc' )
) )
SELECT @cols as A
";
string strFunctionColumns = pconn.GetDataTableFirstValue(strSQL).ToString();
strSQL = "SELECT " + strFunctionColumns.Substring(0, strFunctionColumns.Length - 1) + " AS A FROM [B01_MDM].[WS0M_WSMethodDoc] WHERE WSID=@Param0";
DataTable dt = pconn.GetDataTable(strSQL, new string[] { pstrWSID });
for (int i = 0; i < dt.Rows.Count; i++)
{
strValue += " " + dt.Rows[i]["A"].ToString();
}
strSQL = " SELECT 1 AS A FROM [B05_RPT].[WS0V_WSMethodView] WHERE WSID=@Param0";
if (pconn.GetDataTableRowCount(strSQL, new string[] { pstrWSID }) == 1)
{
pconn.ExcuteQuerryByTran("UPDATE [B05_RPT].[WS0V_WSMethodView] SET fulltext=@Param0 WHERE WSID=@PARAM1", new string[] { strValue, pstrWSID });
}
else
{
pconn.ExcuteQuerryByTran("INSERT INTO [B05_RPT].[WS0V_WSMethodView](fulltext,WSID)Values(@Param0,@PARAM1)", new string[] { strValue, pstrWSID });
}
}
/// <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("GL_ERP");
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 = "isnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
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
[PRA_CONO]
,[PRA_DIVI]
,[PRA_ArrivalPlanId]
,[PRA_ArrivalPlanCode]
,[PRA_PR1_OrderId]
,[PRA_PerchaseTotal]
,[PRA_ArrivalTotal]
,[PRA_PlanTotal]
,[PRA_PerchaseAmount]
,[PRA_ArrivalAmount]
,[PRA_PlanAmount]
,[PRA_TotalDiff]
,[PRA_AmountDiff]
,[PRA_Status]
,[PRA_RgUser]
,[PRA_RgDt]
,[PRA_LmUser]
,[PRA_LmDt]
,[PRA_UptNo]
,A.PR1_OrderCode
FROM B02_Bill.PRA_ArrivalPlan
LEFT JOIN B02_Bill.PR1_Order A ON PRA_PR1_OrderId=PR1_OrderId
WHERE " + strWHERE;
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PRA_ArrivalPlan");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PRA_ArrivalPlan SET PRA_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PRA_ArrivalPlanId='" + ds.Tables["LIST"].Rows[i]["PRA_ArrivalPlanId"].ToString() + @"'
AND isnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.PRA_ArrivalPlan WHERE PRA_ArrivalPlanId=@param0
AND isnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PRA_ArrivalPlanId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["PRA_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PRA_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PRA_LmDt"))
{
dt.Columns.Add("PRA_LmDt");
}
if (!dt.Columns.Contains("PRA_UptNo"))
{
dt.Columns.Add("PRA_UptNo");
}
// if (!dt.Columns.Contains("PRA_Status"))
// {
// dt.Columns.Add("PRA_Status");
// }
// dt.Rows[0]["PRA_Status"] = '0';
dt.Rows[0]["PRA_LmDt"] = DateTime.Now;
dt.Rows[0]["PRA_UptNo"] = strCode;
}
conn.Update("B02_Bill.PRA_ArrivalPlan", dt, "PRA_ArrivalPlanId='" + ds.Tables["LIST"].Rows[i]["PRA_ArrivalPlanId"].ToString() + @"'");
}
else //add
{
string PRA_ArrivalPlanId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("PRA_ArrivalPlanId"))
{
dt.Columns.Add("PRA_ArrivalPlanId");
}
// if (!dt.Columns.Contains("PRA_Status"))
// {
// dt.Columns.Add("PRA_Status");
// }
if (!dt.Columns.Contains("PRA_RgDt"))
{
dt.Columns.Add("PRA_RgDt");
}
if (!dt.Columns.Contains("PRA_LmDt"))
{
dt.Columns.Add("PRA_LmDt");
}
if (!dt.Columns.Contains("PRA_UptNo"))
{
dt.Columns.Add("PRA_UptNo");
}
dt.Rows[0]["PRA_ArrivalPlanId"] = PRA_ArrivalPlanId;
dt.Rows[0]["PRA_RgDt"] = DateTime.Now;
dt.Rows[0]["PRA_LmDt"] = DateTime.Now;
dt.Rows[0]["PRA_UptNo"] = '0';
// dt.Rows[0]["PRA_Status"] = '0';
conn.Insert("B02_Bill.PRA_ArrivalPlan", dt);
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 对审核步骤进行审核
/// </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("HZY_ITSM");
string strXML = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet ds = Common.Common.GetDSByExcelXML(strXML);
string strCheck_ID = "";
string strCheck_User = "";
string strCheck_Result = "";
string strCheck_RemarK = "";
string strKey_Value = "";
string strKeyColumn = "";
string strRequestTableName = "";
strCheck_ID = ds.Tables["LIST"].Rows[0]["ID"].ToString();
if (strCheck_ID == "")
{
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "1:您的请求有误!" });
return ds_Return;
}
DataTable dt = conn.GetDataTable(@"
SELECT t1.ID ,
t1.Key_ID ,
t1.Seq_Index ,
t1.Seq_Name ,
t1.Check_User ,
t1.Check_Type,
t1.Check_Type_Table_Name,
t1.Check_Type_Key_Column,
t1.Check_Date ,
t1.Check_Result ,
t1.Remark ,
t2.Table_Templete
FROM dbo.Check_List t1
LEFT JOIN dbo.check_Table_Type t2 ON t1.Check_Type = t2.Check_Type
WHERE t1.id = @Param0
", new string[1] { strCheck_ID });
if (dt.Rows.Count == 0)
{
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "2:您的请求有误！" });
return ds_Return;
}
strKey_Value = dt.Rows[0]["Key_ID"].ToString();
strKeyColumn = dt.Rows[0]["Check_Type_Key_Column"].ToString();
strRequestTableName = dt.Rows[0]["Check_Type_Table_Name"].ToString();
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
{
ITSM_Server_Request_EditByXML itsm_SR = new ITSM_Server_Request_EditByXML();
strXML = @"<ROOT><OPTYPE>GET</OPTYPE>
<LIST>
<ROW>
<condition>" + strKeyColumn + @"='" + strKey_Value + @"'</condition>
</ROW>
</LIST>
</ROOT>";
DataSet ds_ITSMSR_Request = Common.Common.GetRequestDataSet(new string[1] { "XML" }, new string[1] { strXML});
itsm_SR.Request = ds_ITSMSR_Request;
DataSet ds_ITSMSR_Response = itsm_SR.GetData();
if (ds_ITSMSR_Response.Tables.Count == 0 || ds_ITSMSR_Response.Tables[0].Rows.Count == 0)
{
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "3:获取原始单据有误！" });
return ds_Return;
}
string strTableTemplete = dt.Rows[0]["Table_Templete"].ToString();
for (int i = 0; i < ds_ITSMSR_Response.Tables[0].Columns.Count; i++)
{
string strTemp = ds_ITSMSR_Response.Tables[0].Rows[0][i].ToString().Replace("\n", "<p>");
if(strTemp == "")
{
strTemp = " ";
}
strTableTemplete = strTableTemplete.Replace("[@@" + ds_ITSMSR_Response.Tables[0].Columns[i].ColumnName + "]", strTemp);
}
dt.Rows[0]["Table_Templete"] = Microsoft.JScript.GlobalObject.escape(strTableTemplete);
dt.Columns.Add("timestamps");
dt.Rows[0]["timestamps"] = ds_ITSMSR_Response.Tables[0].Rows[0]["timestamps"];
ds_Return.Tables.Add(dt.Copy());
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
strCheck_ID = ds.Tables["LIST"].Rows[0]["ID"].ToString();
strCheck_User = ds.Tables["LIST"].Rows[0]["Check_User"].ToString();
strCheck_Result = ds.Tables["LIST"].Rows[0]["Check_Result"].ToString();
strCheck_RemarK = ds.Tables["LIST"].Rows[0]["Check_Remark"].ToString();
DataTable dtCheckList = conn.GetDataTable("SELECT * FROM Check_List WHERE ID='" + strCheck_ID + "'");
if (dt.Rows.Count == 0)
{
throw new Exception("该审核单已不存在，请联系管理员");
}
int nSeq_ID = Convert.ToInt32(dt.Rows[0]["Seq_Index"]);
//判断是否已经进行后续的审批
string strSql = "SELECT '1' as A from Check_List WHERE Check_Type='" + dt.Rows[0]["Check_Type"].ToString() + @"'
AND Key_ID='" + dt.Rows[0]["Key_ID"].ToString() + @"'
AND Check_Result is not null
AND Seq_Index>" + nSeq_ID.ToString() + "";
if (conn.GetDataTableRowCount(strSql) > 0)
{
throw new Exception("下一道的流程已经审批，本次审批不能再做修改！");
}
dtCheckList.Rows[0]["Check_User"] = strCheck_User;
dtCheckList.Rows[0]["Check_Date"] = DateTime.Now;
dtCheckList.Rows[0]["Check_Result"] = strCheck_Result;
dtCheckList.Rows[0]["Remark"] = strCheck_RemarK;
dtCheckList.Rows[0]["updateTime"] = DateTime.Now;
ArrayList listTable = new ArrayList();
listTable.Add("Check_List");
try
{
conn.BeginTransaction();
conn.TableLock(listTable);
//判断数据是否为最新数据
strSql = "SELECT timestamps+0 as timestamps from " + strRequestTableName
+ " WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'";
string strTimestamps = conn.GetDataTableFirstValue(strSql).ToString();
if (strTimestamps != ds.Tables["LIST"].Rows[0]["timestamps"].ToString())
{
throw new Exception("原申请单数据已被修改，请刷新后重试！");
}
//dt.Columns.Remove("timestamps");
conn.Update("Check_List", dtCheckList, "ID='" + strCheck_ID + "'");
string[] strColumn = new string[3];
string[] strValue = new string[3];
strColumn[0] = "Check_Type";
strColumn[1] = "Seq_Name";
strColumn[2] = "Key_ID";
strValue[0] = dt.Rows[0]["Check_Type"].ToString();
strValue[1] = dt.Rows[0]["Seq_Name"].ToString();
strValue[2] = dt.Rows[0]["Key_ID"].ToString();
DataSet requestDs = Common.Common.GetRequestDataSet(strColumn, strValue);
ITSM_Check_List_Edit checkListEdit = new ITSM_Check_List_Edit();
checkListEdit.Conn = conn;
checkListEdit.ListAMSendList = listAMSendList;
checkListEdit.Request = requestDs;
checkListEdit.Execute();
if (strCheck_Result != "同意")
{
//更新原表的状态为已驳回
strSql = "UPDATE " + strRequestTableName + " SET State='已驳回' WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'";
conn.ExcuteQuerryByTran(strSql);
}
else
{
//判断是否有已驳回的状态
strSql = "SELECT '1' as A from Check_List WHERE Check_Type='" + dt.Rows[0]["Check_Type"].ToString() + @"'
AND Key_ID='" + strKey_Value + @"'
AND (Seq_index = " + nSeq_ID.ToString() + @"
AND Check_Result='已驳回')";
//没有已驳回的记录
if (conn.GetDataTableRowCount(strSql) == 0)
{
//判断是否还有后续的流程
strSql = "SELECT '1' as A from Check_List WHERE Check_Type='" + dt.Rows[0]["Check_Type"].ToString() + @"'
AND Key_ID='" + strKey_Value + @"'
AND (Seq_Index>" + nSeq_ID.ToString() + @"
OR (Seq_Index=" + nSeq_ID.ToString() + @"
AND Must_Check=1
AND Check_Date IS NULL
))";
if (conn.GetDataTableRowCount(strSql) == 0)
{
//更新原表的状态为已审核
strSql = "UPDATE " + strRequestTableName + " SET State='已审核' WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'";
conn.ExcuteQuerryByTran(strSql);
ITSM_Common.SendAMMessage("", "马卫清", "有服务器申请表需要操作", "您有有服务器申请表需要操作，请进入EXCEL操作");
}
else
{
//更新原表的状态为审核中
strSql = "UPDATE " + strRequestTableName + " SET State='审核中' WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'";
conn.ExcuteQuerryByTran(strSql);
}
}
}
conn.CommitTransaction();
for (int i = 0; i < listAMSendList.Count; i++)
{
string[] strList = (string[])(listAMSendList[i]);
ITSM_Common.SendAMMessage(strList[0], strList[1], strList[2]);
}
}
catch (Exception)
{
conn.RollbackTransaction();
throw;
}
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
return ds_Return;
}
catch
{
throw;
}
}
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 static HZYmessage CheckUser(string SessionID,bool pUpdateLastReustTime)
{
HZYmessage message = new HZYmessage();
if (SessionID == null || SessionID == "")
{
throw new Exception("身份验证错误");
}
Dbconn conn = new Dbconn("WSRR");
string strSQL = @"
SELECT
[LoginSessionID]
,[LoginUser]
,[LoginTime]
FROM F01_Cert.Login
where LoginSessionID=@param0
and LogoutTime is null
";
DataTable dt = conn.GetDataTable(strSQL, new string[] { SessionID });
if (dt.Rows.Count == 1)
{
message.Session_ID = dt.Rows[0]["LoginSessionID"].ToString();
//message.Employee_ID = dt.Rows[0]["LoginUser"].ToString();
message.User_ID = dt.Rows[0]["LoginUser"].ToString();
message.User_Name = dt.Rows[0]["LoginUser"].ToString();
message.User_Name_CN = dt.Rows[0]["LoginUser"].ToString();
if (pUpdateLastReustTime)
{
//需要更新用户的最后访问日期
conn.BeginTransaction();
conn.ExcuteQuerryByTran("update F01_Cert.Login set LastVisitTime=getdate() where LoginSessionID=@param0", new string[] { message.Session_ID });
conn.CommitTransaction();
}
return message;
}
else
{
throw new Exception("身份验证错误");
}
}
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;
}
/// <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("GL_ERP");
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 = "isnull(PRAD_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
string strWHERE1 = "isnull(PRAD_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
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() + ")";
}
if (ds.Tables["LIST"].Rows[0]["Condition1"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE1 += " AND (" + ds.Tables["LIST"].Rows[0]["Condition1"].ToString().Trim() + ")";
}
}
strSQL = @"SELECT * FROM B02_Bill.PRAD_ArrivalPlanDetail WHERE " + strWHERE;
DataTable dt_SR = conn.GetDataTable(strSQL);
if (dt_SR.Rows.Count > 0)
{
strSQL = @"SELECT
[PRAD_CONO]
,[PRAD_DIVI]
,[PRAD_ArrivalPlanDetailId]
,[PRAD_PRA_ArrivalPlanId]
,[PRAD_PD0_ProductId]
,[PRAD_UnitID]
,[PRAD_PR1D_OrderDetailId]
,[PRAD_ArrivalDate]
,[PRAD_PerchaseTotal]
,[PRAD_ArrivalTotal]
,[PRAD_PlanTotal]
,[PRAD_PlanArrival]
,[PRAD_PlanArrivalAmount]
,[PRAD_TotalDiff]
,[PRAD_Status]
,[PRAD_RgUser]
,[PRAD_RgDt]
,[PRAD_LmUser]
,[PRAD_LmDt]
,[PRAD_UptNo]
FROM B02_Bill.PRAD_ArrivalPlanDetail
WHERE " + strWHERE;
}
else
{
strSQL = @"SELECT
[PRAD_CONO]
,[PRAD_DIVI]
,'' AS [PRAD_ArrivalPlanDetailId]
,[PRA_ArrivalPlanId]
,[PR1D_OrderDetailId]
,[PR1D_PD0_ProductId]
,[PR1D_UnitID]
,'' AS [PRAD_ArrivalDate]
,ISNULL([PR1D_Total],'0') AS [PR1D_Total]
,SUM(ISNULL(PRAD_PlanArrival,'0')) AS PRAD_PlanTotal
,SUM(ISNULL([PRAD_ArrivalTotal],'0')) as PRAD_ArrivalTotal
,'0' AS [PRAD_PlanArrival]
,'0' AS [PRAD_PlanArrivalAmount]
,'0' as [PRAD_TotalDiff]
,'' as [PRAD_Status]
,'' as [PRAD_RgUser]
,'' as [PRAD_RgDt]
,'' as [PRAD_LmUser]
,'' as [PRAD_LmDt]
,'0' as [PRAD_UptNo]
FROM B02_Bill.PRA_ArrivalPlan
LEFT JOIN B02_Bill.PR1D_OrderDetail ON PRA_PR1_OrderId=PR1D_PR1_OrderId
LEFT JOIN B02_Bill.PRAD_ArrivalPlanDetail ON PR1D_OrderDetailId=PRAD_PR1D_OrderDetailId
WHERE " + strWHERE1
+ @"GROUP BY [PRAD_CONO],[PRAD_DIVI] ,[PRA_ArrivalPlanId],[PR1D_PD0_ProductId]
,[PR1D_OrderDetailId],[PR1D_UnitID]
,[PR1D_Total]";
}
ds_Return = conn.GetDataSet(strSQL);
// }
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PRAD_ArrivalPlanDetail");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PRAD_ArrivalPlanDetail SET PRAD_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PRAD_PRA_ArrivalPlanId='" + ds.Tables["LIST"].Rows[i]["PRAD_PRA_ArrivalPlanId"].ToString() + @"'
AND iusnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.PRAD_ArrivalPlanDetail WHERE PRAD_ArrivalPlanDetailId=@param0
AND PRAD_Status<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PRAD_ArrivalPlanDetailId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["PRAD_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PRAD_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PRAD_LmDt"))
{
dt.Columns.Add("PRAD_LmDt");
}
if (!dt.Columns.Contains("PRAD_UptNo"))
{
dt.Columns.Add("PRAD_UptNo");
}
//if (!dt.Columns.Contains("PRA_Status"))
//{
// dt.Columns.Add("PRA_Status");
//}
//dt.Rows[0]["PRA_Status"] = '0';
dt.Rows[0]["PRA_LmDt"] = DateTime.Now;
dt.Rows[0]["PRA_UptNo"] = strCode;
}
conn.Update("B02_Bill.PRAD_ArrivalPlanDetail", dt, "PRAD_ArrivalPlanDetailId='" + ds.Tables["LIST"].Rows[i]["PRAD_ArrivalPlanDetailId"].ToString() + @"'");
strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal=BB.PR1_PurchaseTotal,
AA.PR1_PurchaseAmount=BB.PR1_PurchaseAmount,AA.PR1_POriginalAmount=BB.PR1_POriginalAmount,
AA.PR1_LmUser='******',
AA.PR1_LmDt='" + ds.Tables["LIST"].Rows[i]["PR1D_LmDt"].ToString() + @"',
AA.PR1_UptNo=AA.PR1D_UptNo+1
FROM
B02_Bill.PR1_Order AA,
(SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_PurchaseAmount,
SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_POriginalAmount,PR1D_PR1_OrderId
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
GROUP BY PR1D_PR1_OrderId)BB
WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
conn.ExcuteQuerryByTran(strSQL);
}
else //add
{
DataTable dtTemp = conn.GetDataTableInTrans(
@" SELECT " + ds.Tables["LIST"].Rows[i]["PRAD_PerchaseTotal"].ToString() + @" -
" + ds.Tables["LIST"].Rows[i]["PRAD_ArrivalTotal"].ToString() + @" -
" + ds.Tables["LIST"].Rows[i]["PRAD_PlanArrival"].ToString() + @" as PRAD_TotalDiff
");
string PRAD_TotalDiff = dtTemp.Rows[0]["PRAD_TotalDiff"].ToString();
DataTable dtAmot = conn.GetDataTableInTrans(
@" SELECT ISNULL(PR1D_POriginalPrice * " + ds.Tables["LIST"].Rows[i]["PRAD_PlanArrival"].ToString() + @",'0')
as PRAD_PlanArrivalAmount
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_OrderDetailId='" + ds.Tables["LIST"].Rows[i]["PRAD_PR1D_OrderDetailId"].ToString() + @"'
AND PR1D_PD0_ProductId='" + ds.Tables["LIST"].Rows[i]["PRAD_PD0_ProductId"].ToString() + @"'
");
string PRAD_PlanArrivalAmount = dtAmot.Rows[0]["PRAD_PlanArrivalAmount"].ToString();
string PRAD_ArrivalPlanDetailId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("PRAD_ArrivalPlanDetailId"))
{
dt.Columns.Add("PRAD_ArrivalPlanDetailId");
}
if (!dt.Columns.Contains("PRAD_RgDt"))
{
dt.Columns.Add("PRAD_RgDt");
}
if (!dt.Columns.Contains("PRAD_LmDt"))
{
dt.Columns.Add("PRAD_LmDt");
}
if (!dt.Columns.Contains("PRAD_UptNo"))
{
dt.Columns.Add("PRAD_UptNo");
}
if (!dt.Columns.Contains("PRAD_TotalDiff"))
{
dt.Columns.Add("PRAD_TotalDiff");
}
if (!dt.Columns.Contains("PRAD_PlanArrivalAmount"))
{
dt.Columns.Add("PRAD_PlanArrivalAmount");
}
dt.Rows[0]["PRAD_ArrivalPlanDetailId"] = PRAD_ArrivalPlanDetailId;
dt.Rows[0]["PRAD_RgDt"] = DateTime.Now;
dt.Rows[0]["PRAD_LmDt"] = DateTime.Now;
dt.Rows[0]["PRAD_TotalDiff"] = PRAD_TotalDiff;
dt.Rows[0]["PRAD_PlanArrivalAmount"] = PRAD_PlanArrivalAmount;
conn.Insert("B02_Bill.PRAD_ArrivalPlanDetail", dt);
strSQL = @"UPDATE AA SET AA.PRA_PlanTotal=BB.PRAD_PlanTotal,
AA.PRA_PlanAmount=BB.PRAD_PlanArrivalAmount,
AA.PRA_LmUser='******',
AA.PRA_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PRA_TotalDiff=AA.PRA_PerchaseTotal-AA.PRA_ArrivalTotal-BB.PRA_PlanTotal,
AA.PRA_AmountDiff=AA.PRA_PerchaseAmount-AA.PRA_ArrivalAmount-BB.PRA_PlanAmount,
AA.PRA_UptNo=AA.PRA_UptNo+1
FROM
PRA_ArrivalPlan AA,
(SELECT PRAD_PRA_ArrivalPlanId,SUM(ISNULL(PRAD_PlanArrival,'0')) AS PRAD_PlanTotal,
SUM(ISNULL(PRAD_PlanArrivalAmount,'0')) AS PRAD_PlanArrivalAmount
FROM B02_Bill.PRAD_ArrivalPlanDetail
WHERE PRAD_PRA_ArrivalPlanId=' " + ds.Tables["LIST"].Rows[i]["PRAD_PRA_ArrivalPlanId"].ToString() + @" '
GROUP BY PRAD_PRA_ArrivalPlanId)BB
WHERE AA.PRA_ArrivalPlanId=BB.PRAD_PRA_ArrivalPlanId";
conn.ExcuteQuerryByTran(strSQL);
// strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal=BB.PR1_PurchaseTotal,
// AA.PR1_PurchaseAmount=BB.PR1_PurchaseAmount,AA.PR1_POriginalAmount=BB.PR1_POriginalAmount,
// AA.PR1_LmUser='******',
// AA.PR1_LmDt='" + ds.Tables["LIST"].Rows[i]["PR1D_LmDt"].ToString() + @"',
// AA.PR1_UptNo=AA.PR1D_UptNo+1
// FROM
// B02_Bill.PR1_Order AA,
// (SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_PurchaseAmount,
// SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_POriginalAmount,PR1D_PR1_OrderId
// FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
// GROUP BY PR1D_PR1_OrderId)BB
// WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
// conn.ExcuteQuerryByTran(strSQL);
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 获取MDM人员信息
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
int nPage = 1;
int nPageRowCount = 20;
string strOrder = " desc ";
string strSort = " Employee_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,
[Employee_ID]
,[Name]
,[Sex]
,[Phone]
,[Moblie]
,[SSO_UserName]
-- ,[SSO_Password]
,[In_Time]
,[Leave_Time]
,[Leave_Flag]
,[Createtime]
,[Updatetime]
,[Timestamps]
FROM [MDM].[dbo].[MDM_Employees]
WHERE Leave_Flag=0 " + 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 [MDM].[dbo].[MDM_Employees] WITH(NOLOCK)
WHERE Leave_Flag=0 " + strWhere + @"", new string[0]);
dt.TableName = "PageRowCount";
ds.Tables.Add(dt.Copy());
return ds;
}
catch
{
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 DataSet GetData()
{
int nPage = 1;
int nPageRowCount = 20;
string strOrder = " DESC ";
string strSort = " fNeedDeliverDay "; //交付日期
string strWhere = " AND 1=1 ";
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;
}
try
{
Dbconn conn = new Dbconn("X6_EPApp");
DataSet ds = conn.GetDataSet(@"
SELECT * FROM
(
SELECT
ROW_NUMBER()OVER(ORDER BY " + strSort + " " + strOrder + @") AS ROW_NUMBER_INDEX,
fid,
fTaskType ,
ftaskid ,
ftaskname ,
fDept ,
fneeds ,
fImportanceDegree ,
fNeedDeliverDay ,
fConfirmDeliverDay ,
fTaskPrincipal ,
fTaskAllocatee ,
fTaskState ,
fTaskDepiction
FROM IT_TASKATTRIBUTE WITH(NOLOCK)
WHERE 1=1 AND AM_ISDELETE=0 " + 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.IT_TASKATTRIBUTE WITH(NOLOCK)
WHERE 1=1 " + strWhere + @"", new string[0]);
dt.TableName = "PageRowCount";
ds.Tables.Add(dt.Copy());
return ds;
}
catch
{
throw;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("MDM");
//取到用户信息
DataTable dt_User = conn.GetDataTable(
@" SELECT t1.AM_User_id ,
t1.TfgMachineNo ,
t1.ComputerName ,
t2.AM_LOGIN
FROM dbo.AM_TFG_Relation t1 WITH ( NOLOCK )
LEFT JOIN dbo.AM_User t2 WITH ( NOLOCK ) ON t1.AM_User_id = t2.AM_User_id
", new string[0]);
DbconnMysql connMysqlTFG = new DbconnMysql("TFG_infoguard");
//取到部门信息,只取到两级目录，并且只会室与部门
DataTable dt_user_dep = conn.GetDataTable(@"
SELECT DISTINCT
t1.AM_User_id ,
CASE WHEN t3.AM_Dept1_ID IS NULL THEN t3.AM_Company
ELSE t3.AM_Dept1_ID
END AM_S_Dept_ID
FROM dbo.AM_TFG_Relation t1 WITH ( NOLOCK )
LEFT JOIN dbo.AM_User_Dept t2 WITH ( NOLOCK ) ON t1.AM_User_id = t2.AM_User_id
LEFT JOIN dbo.AM_Dept t3 WITH ( NOLOCK ) ON t2.AM_S_Dept_ID = t3.AM_S_Dept_ID
LEFT JOIN dbo.AM_Dept t4 WITH ( NOLOCK ) ON t3.AM_Dept1_ID = t4.AM_S_Dept_ID
WHERE t3.[AM_Dept_NAME_All] LIKE '01%'
AND ( t4.AM_Dept_NAME IS NULL
OR t4.AM_Dept_NAME LIKE '%部'
OR t4.AM_Dept_NAME LIKE '%室'
)
", new string[0]);
try
{
ArrayList tableList = new ArrayList();
connMysqlTFG.BeginTransaction();
tableList.Add("Info_Term");
connMysqlTFG.TableLock(tableList);
//更新TFG服务器的用户姓名
for (int i = 0; i < dt_User.Rows.Count; i++)
{
DataTable dt_Update_Info_Term = new DataTable();
dt_Update_Info_Term.Columns.Add("sn");
dt_Update_Info_Term.Columns.Add("name");
DataRow dr = dt_Update_Info_Term.NewRow();
dr["sn"] = dt_User.Rows[i]["TfgMachineNo"];
dr["name"] = dt_User.Rows[i]["AM_LOGIN"];
dt_Update_Info_Term.Rows.Add(dr);
string strWhere = "sn='" + dt_User.Rows[i]["TfgMachineNo"] + "'";
connMysqlTFG.Update("Info_Term", dt_Update_Info_Term, strWhere);
}
//删除用户与部门数据
connMysqlTFG.Excute("Delete FROM info_dep_term");
//插入用户与部门数据
DataTable dt_trem_dep = new DataTable();
dt_trem_dep.Columns.Add("term_id");
dt_trem_dep.Columns.Add("dep_id");
for (int i = 0; i < dt_User.Rows.Count; i++)
{
string strSN = dt_User.Rows[i]["TfgMachineNo"].ToString();
string strcn_id = dt_User.Rows[i]["AM_User_id"].ToString();
string[] strParam = new string[1];
strParam[0] = dt_User.Rows[i]["TfgMachineNo"].ToString();
DataTable dt_info_term = connMysqlTFG.GetDataTable(@"
SELECT id,
sn
FROM info_term
WHERE sn=?Param0", strParam);
for (int j = 0; j < dt_info_term.Rows.Count; j++)
{
string strID = dt_info_term.Rows[j]["id"].ToString();
DataRow[] drList = dt_user_dep.Select("AM_User_id=" + strcn_id, "AM_S_Dept_ID ASC");
foreach (DataRow dr in drList)
{
DataRow drAdd = dt_trem_dep.NewRow();
drAdd["term_id"] = strID;
drAdd["dep_id"] = dr["AM_S_Dept_ID"];
dt_trem_dep.Rows.Add(drAdd);
}
}
}
if (dt_trem_dep.Rows.Count > 0)
{
connMysqlTFG.Insert("info_dep_term", dt_trem_dep);
}
//取到TFG的所有有效用户的没有小翅膀组织架构信息的铁卷用户
DataTable dt_NO_dept = connMysqlTFG.GetDataTable(@"select name,id from info_term
where enabled<>2
and not exists (select 1 from info_dep_term where info_term.id=info_dep_term.term_id)"
, new string[0]);
for (int i = 0; i < dt_NO_dept.Rows.Count; i++)
{
string strName = dt_NO_dept.Rows[i]["name"].ToString();
string strID = dt_NO_dept.Rows[i]["id"].ToString();
//找到小翅膀对应的用户的组织架构
DataTable dt_User_temp = conn.GetDataTable(@"
SELECT DISTINCT CASE WHEN t3.am_dept1_id IS NULL THEN t3.AM_Company ELSE t3.am_dept1_id END AM_S_Dept_ID
FROM dbo.AM_User t1 WITH ( NOLOCK )
JOIN dbo.AM_User_Dept t2 WITH ( NOLOCK ) ON t1.AM_User_id = t2.AM_USER_ID
JOIN dbo.AM_Dept t3 WITH ( NOLOCK ) ON t2.AM_S_Dept_ID = t3.AM_S_Dept_ID
LEFT JOIN dbo.AM_Dept t4 WITH(NOLOCK) ON t3.am_dept1_id=t4.AM_S_Dept_ID
WHERE t1.AM_login ='******'
AND (t4.AM_S_Dept_ID IS NULL --公司
OR t4.AM_Dept_NAME LIKE '%部'
OR t4.AM_Dept_NAME LIKE '%室'
)
AND t3.AM_Dept_NAME_All like '01%'
", new string[0]);
if (dt_User_temp.Rows.Count > 0)
{
DataTable dtUPdate = new DataTable();
dtUPdate.Columns.Add("term_id");
dtUPdate.Columns.Add("dep_id");
foreach (DataRow dr in dt_User_temp.Rows)
{
DataRow drNew = dtUPdate.NewRow();
drNew["dep_id"] = dr["AM_S_Dept_ID"];
drNew["term_id"] = strID;
dtUPdate.Rows.Add(drNew);
}
connMysqlTFG.Insert("info_dep_term", dtUPdate);
}
}
connMysqlTFG.CommitTransaction();
return true;
}
catch
{
connMysqlTFG.RollbackTransaction();
throw;
}
}
public DataSet GetData()
{
Dbconn conn = new Dbconn("WSRR");
string strWhere = string.Empty;
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 strSQL = @"
SELECT
[AUZID]
,[AUZName]
FROM [WSRR_Manage].[F01_CERT].[AUZ]";
ds_Return = conn.GetDataSetForPageList(strSQL,m_hzyPageInfo);
}
//新增环境的权限名称
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERT")
{
//String Env_ID = dt_LIST.Rows[0]["Env_ID"].ToString();
String strAuzName = dt_LIST.Rows[0]["AuzName"].ToString();
string strsql = @"
SELECT COUNT(1) AS Cnt
FROM [WSRR_Manage].[F01_CERT].[AUZ]
WHERE Authorization_Name='" + strAuzName + "'";
DataTable dt = conn.GetDataTable(strsql);
if (int.Parse(dt.Rows[0][0].ToString()) > 0)
{
dt_EditResult.Rows.Add(new object[] { false, "权限：" + strAuzName + "已存在!" });
}
else
{
//dt_LIST.Rows[0]["AuzID"] = Guid.NewGuid().ToString();
conn.BeginTransaction();
bool flag = conn.Insert(" [WSRR_Manage].[F01_CERT].[AUZ]", dt_LIST);
conn.CommitTransaction();
if (flag)
{
dt_EditResult.Rows.Add(new object[] { true, "权限：" + strAuzName + "添加成功!" });
}
else
{
dt_EditResult.Rows.Add(new object[] { true, "权限：" + strAuzName + "添加失败!" });
}
}
ds_Return.Tables.Add(dt_EditResult);
}
//修改权限的菜单树
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "EDIT")
{
String strAuZID = dt_LIST.Rows[0]["AuzID"].ToString();
strWhere = " AuzID=" + strAuZID;
dt_LIST.Columns.Remove("AuzID");//移除属性，标识符列（ID）不能更新
conn.LockTableList.Add("[WSRR_Manage].[F01_CERT].[AUZ]");
conn.BeginTransaction();
bool flag = conn.Update("[WSRR_Manage].[F01_CERT].[AUZ]", dt_LIST, strWhere);
conn.CommitTransaction();
if (flag)
{
dt_EditResult.Rows.Add(new object[] { true, "权限修改成功!" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "权限修改失败!" });
}
ds_Return.Tables.Add(dt_EditResult);
}
//删除权限
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "DELETE")
{
String strAuZID = dt_LIST.Rows[0]["AuzID"].ToString();
strWhere = " AuzID=" + strAuZID;
conn.BeginTransaction();
bool flag = conn.Delete("[WSRR_Manage].[F01_CERT].[AUZ]", strWhere);
conn.CommitTransaction();
if (flag)
{
dt_EditResult.Rows.Add(new object[] { true, "权限删除成功!" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "权限删除失败!" });
}
ds_Return.Tables.Add(dt_EditResult);
}
}
return ds_Return;
}
/// <summary>
/// 根据MDM店铺的Market数据来编辑Entity
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
Dbconn conn = new Dbconn("MDM_Master");
try
{
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
string strEntityCode = "0006";
string strMarketTableName = "Store_For_FM";
DataSet ds = Common.Common.GetDSByExcelXML(strXML);
//获取店铺的所有属性
MDM_Entity_Market_PropertyList clsPropertyList = new MDM_Entity_Market_PropertyList();
clsPropertyList.Request = Common.Common.GetRequestDataSet(new string[2] { "Entity", "MarketTableName" }, new string[2] { strEntityCode, strMarketTableName });
DataTable dt_PropertyList = clsPropertyList.GetData().Tables[0];
//修改店铺数据
DataTable dtStore = new DataTable(strEntityCode);
dtStore.Namespace = "CN"; //定义表达方式
DataTable dtStoreLink = new DataTable("L0013");
dtStoreLink.Namespace = "CN"; //定义表达方式
dtStoreLink.Columns.Add("Body_ID");
dtStoreLink.Columns.Add("Body_ID_0001");
dtStoreLink.Columns.Add("Body_ID_0006");
dtStoreLink.Columns.Add("Body_ID_0010");
dtStoreLink.Columns.Add("Body_ID_L0011");
dtStoreLink.Columns.Add("Body_ID_L0014");
bool bCreateTable = true;
GetBaseData(ds.Tables["LIST"]);
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataRow dr = dtStore.NewRow();
DataRow drLink = dtStoreLink.NewRow();
drLink["body_ID"] = ds.Tables["LIST"].Rows[i]["body_ID"].ToString();
DataTable dtLinkOld = new DataTable();
string strLinkBody_ID = drLink["body_ID"].ToString().Trim();
if (strLinkBody_ID != "")
{
//获取所有的BODY_ID数据
dtLinkOld = conn.GetDataTable(@"SELECT * FROM MDM_L0013_B WHERE Body_ID='"+strLinkBody_ID+"'");
if (dtLinkOld.Rows.Count == 0)
{
throw new Exception("传入的BodyID不正确");
}
}
//取到对应的BodyID
if (ds.Tables["LIST"].Columns.Contains("公司中文简称"))
{
string str公司中文简称 = ds.Tables["LIST"].Rows[i]["公司中文简称"].ToString();
if (str公司中文简称 != "")
{
drLink["Body_ID_0001"] = GetBodyID("公司", "公司中文简称='" + str公司中文简称 + "'");
}
if (drLink["Body_ID_0001"].ToString() == "")
{
if (strLinkBody_ID != "")
{
//如果没有传入公司信息的话，可能是由于用户没有权限，所以使用旧值
drLink["Body_ID_0001"] = dtLinkOld.Rows[0]["Body_ID_0001"];
}
else
{
//如果是添加状态的话，必须要有BodyID
throw new Exception("公司中文简称的信息不正确或者没有权限！");
}
}
}
else
{
if (strLinkBody_ID != "")
{
//如果没有传入公司信息的话，可能是由于用户没有权限，所以使用旧值
drLink["Body_ID_0001"] = dtLinkOld.Rows[0]["Body_ID_0001"];
}
}
if (ds.Tables["LIST"].Columns.Contains("加盟商编码"))
{
string str加盟商编码 = ds.Tables["LIST"].Rows[i]["加盟商编码"].ToString();
if (str加盟商编码 != "")
{
drLink["Body_ID_0010"] = GetBodyID("加盟商", "加盟商编码='" + str加盟商编码 + "'");
}
if (drLink["Body_ID_0010"].ToString() == "")
{
if (strLinkBody_ID != "")
{
//如果没有传入加盟商信息的话，可能是由于用户没有权限，所以使用旧值
drLink["Body_ID_0010"] = dtLinkOld.Rows[0]["Body_ID_0010"];
}
else
{
//如果是添加状态的话，必须要有BodyID
throw new Exception("加盟商编码的信息不正确或者没有权限！");
}
}
}
else
{
if (strLinkBody_ID != "")
{
//如果没有传入加盟商信息的话，可能是由于用户没有权限，所以使用旧值
drLink["Body_ID_0010"] = dtLinkOld.Rows[0]["Body_ID_0010"];
}
}
string str加盟商Body_id = drLink["Body_ID_0010"].ToString();
//添加状态下，如果加盟商编码为空的话，就无法生成POS编号，所以是不允许的
if (strLinkBody_ID == "" && str加盟商Body_id == "")
{
throw new Exception("加盟商编码的信息不正确");
}
if (ds.Tables["LIST"].Columns.Contains("县级市编号"))
{
string str县级市 = ds.Tables["LIST"].Rows[i]["县级市编号"].ToString();
if (str县级市 != "")
{
drLink["Body_ID_L0011"] = GetBodyID("县", "县级市编号='" + str县级市 + "'");
}
if (drLink["Body_ID_L0011"].ToString() == "")
{
if (strLinkBody_ID != "")
{
//如果没有传入县级市信息的话，可能是由于用户没有权限，所以使用旧值
drLink["Body_ID_L0011"] = dtLinkOld.Rows[0]["Body_ID_L0011"];
}
}
}
if (ds.Tables["LIST"].Columns.Contains("销售大区名称"))
{
string str销售大区名称 = ds.Tables["LIST"].Rows[i]["销售大区名称"].ToString();
if (str销售大区名称 != "")
{
drLink["Body_ID_L0014"] = GetBodyID("销售大区", "销售大区名称='" + str销售大区名称 + "'");
}
if (drLink["Body_ID_L0014"].ToString() == "")
{
if (strLinkBody_ID != "")
{
//如果没有传入销售大区信息的话，可能是由于用户没有权限，所以使用旧值
drLink["Body_ID_L0014"] = dtLinkOld.Rows[0]["Body_ID_L0014"];
}
}
}
dtStoreLink.Rows.Add(drLink);
if (bCreateTable)
{
dtStore.Columns.Add("body_ID");
}
dr["body_ID"] = ds.Tables["LIST"].Rows[i]["body_ID_0006"].ToString();
if (dr["body_ID"].ToString().Trim() == "")
{
ds.Tables["LIST"].Rows[i]["POS编号"] = GetStoreCode(str加盟商Body_id,conn);
}
for (int j = 0; j < ds.Tables["LIST"].Columns.Count; j++)
{
string strColumnName = ds.Tables["LIST"].Columns[j].ColumnName;
//如果修改的列是属于店铺信息的话,要对店铺的Entity进行修改
DataRow[] drList = dt_PropertyList.Select("Table_Property_Name = '" + strColumnName + "'");
if (drList.Length > 0)
{
string strPorpertyname = drList[0]["Porperty_Name"].ToString();
if (bCreateTable)
{
dtStore.Columns.Add(strPorpertyname);
}
dr[strPorpertyname] = ds.Tables["LIST"].Rows[i][j].ToString();
}
}
dtStore.Rows.Add(dr);
bCreateTable = false;
}
DataSet dsRequest = new DataSet();
dsRequest.Tables.Add(dtStore);
MDM_Entity_Edit cls_Entity_Edit = new MDM_Entity_Edit();
cls_Entity_Edit.Request = dsRequest;
cls_Entity_Edit.GetDataByRequestDs = true;
cls_Entity_Edit.Execute();
ArrayList listBody_0006 = cls_Entity_Edit.BodyID; //获取店铺的BODYID
//修改LINK数据
for (int i = 0; i < listBody_0006.Count; i++)
{
dtStoreLink.Rows[i]["Body_ID_0006"] = listBody_0006[i].ToString();
}
dsRequest.Clear();
dsRequest.Tables.Add(dtStoreLink);
cls_Entity_Edit.Request = dsRequest;
cls_Entity_Edit.Execute();
ArrayList listBody = cls_Entity_Edit.BodyID;
string strMessage = "影响的行数为:" + listBody.Count.ToString() + "行";
for (int i = 0; i < listBody.Count; i++)
{
strMessage += ";" + listBody[i].ToString();
}
return Common.Common.GetRequestDataSet(new string[2] { "result", "Message" }, new string[2] { "true", strMessage });
}
catch
{
throw;
}
}
/// <summary>
/// 短信发送
/// </summary>
private void HZYSendSMS()
{
DataTable dtList = ds.Tables["List"];
Dbconn SMSDB = new Dbconn("SMS");
//认证类型
string strContent = dtList.Rows[0]["Content"].ToString().ToUpper();//主题
string strPhone = dtList.Rows[0]["Phone"].ToString().ToUpper();//手机
string strUser = dtList.Rows[0]["User"].ToString().ToUpper();//用户
string strPassword = dtList.Rows[0]["Password"].ToString().ToUpper();//密码
string strHZYSQL = @"SELECT com ,
[user] ,
password ,
systems
FROM [dbo].[CFG_user]
WHERE Status = 1 AND UserType = 1 AND [user]=@param0 AND password = @param1 ";
DataTable dt = SMSDB.GetDataTable(strHZYSQL, new string[2] { strUser, strPassword });
if (dt.Rows.Count == 0 || dt.Rows[0]["com"].ToString() == "")
{
//throw new Exception("用户名密码错误！");
dt_EditResult.Rows.Add(new object[] { false, "用户名密码错误！" });
}
else
{
if (strPhone == "")
{
throw new Exception("Phone不能为空！");
}
m_conn.BeginTransaction();
Send(dt.Rows[0]["systems"].ToString(), strPhone, strContent, dt.Rows[0]["com"].ToString());
m_conn.CommitTransaction();
dt_EditResult.Rows.Add(new object[] { true, "发送成功！" });
}
m_dsRequturn.Tables.Add(dt_EditResult);
}
/// <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("GL_ERP");
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 = "ISNULL(BD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
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
[BD0_CONO]
,[BD0_DIVI]
,[BD0_BrandId]
,[BD0_BrandCode]
,[BD0_SU0_SupplierId]
,[BD0_BrandName]
,[BD0_CountryId]
,[BD0_StyleId]
,[BD0_CusGrpId]
,[BD0_Status]
,[BD0_RgUser]
,[BD0_RgDt]
,[BD0_LmUser]
,[BD0_LmDt]
,[BD0_UptNo]
,A.[SU0_SupplierCode]
,B.[DC0_DictName1] AS CountryDictName
,C.[DC0_DictName1] AS StyleDictName
,D.[DC0_DictName1] AS CusGrpDictName
FROM B01_MDM.BD0_Brand WITH ( NOLOCK )
LEFT JOIN B01_MDM.SU0_Supplier A ON BD0_SU0_SupplierId=A.SU0_SupplierId
LEFT JOIN M01_CONF.DC0_Dictionary B ON BD0_CountryId=B.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary C ON BD0_StyleId=C.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary D ON BD0_CusGrpId=D.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [BD0_LmDt] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B01_MDM.BD0_Brand");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B01_MDM.BD0_Brand SET BD0_Status=1
WHERE BD0_BrandId='" + ds.Tables["LIST"].Rows[i]["BD0_BrandId"].ToString() + @"'
AND ISNULL(BD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B01_MDM.BD0_Brand WHERE BD0_BrandId=@param0
AND ISNULL(BD0_Status,'') <>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["BD0_BrandId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["BD0_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["BD0_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("BD0_LmDt"))
{
dt.Columns.Add("BD0_LmDt");
}
// if (!dt.Columns.Contains("BD0_Status"))
// {
// dt.Columns.Add("BD0_Status");
// }
if (!dt.Columns.Contains("BD0_UptNo"))
{
dt.Columns.Add("BD0_UptNo");
}
// dt.Rows[0]["BD0_Status"] = '0';
dt.Rows[0]["BD0_LmDt"] = DateTime.Now;
dt.Rows[0]["BD0_UptNo"] = strCode;
}
conn.Update("B01_MDM.BD0_Brand", dt, "BD0_BrandId='" + ds.Tables["LIST"].Rows[i]["BD0_BrandId"].ToString() + @"'");
}
else //add
{
strSQL = @"SELECT BD0_BrandCode FROM B01_MDM.BD0_Brand WHERE BD0_BrandCode=@param0
";
DataTable dt_SU = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["BD0_BrandCode"].ToString()});
if (dt_SU.Rows.Count > 0)
{
string BD0_BrandCode = dt_SU.Rows[0]["BD0_BrandCode"].ToString();
// ITSM_Common.SendAMMessage("你输入的品牌编号已存在，最近添加的品牌编号是" + BD0_BrandCode);
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "你输入的品牌编号已存在，最近添加的品牌编号是" + BD0_BrandCode });
conn.RollbackTransaction();
return ds_Return;
}
else
{
string BD0_BrandId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("BD0_BrandId"))
{
dt.Columns.Add("BD0_BrandId");
}
// if (!dt.Columns.Contains("BD0_Status"))
// {
// dt.Columns.Add("BD0_Status");
// }
if (!dt.Columns.Contains("BD0_RgDt"))
{
dt.Columns.Add("BD0_RgDt");
}
if (!dt.Columns.Contains("BD0_LmDt"))
{
dt.Columns.Add("BD0_LmDt");
}
if (!dt.Columns.Contains("BD0_UptNo"))
{
dt.Columns.Add("BD0_UptNo");
}
dt.Rows[0]["BD0_BrandId"] = BD0_BrandId;
// dt.Rows[0]["BD0_Status"] = '0';
dt.Rows[0]["BD0_RgDt"] = DateTime.Now;
dt.Rows[0]["BD0_LmDt"] = DateTime.Now;
dt.Rows[0]["BD0_UptNo"] = '0';
conn.Insert("B01_MDM.BD0_Brand", dt);
}
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
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("GL_ERP");
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 = "ISNULL(PD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
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
[PD0_CONO]
,[PD0_DIVI]
,[PD0_ProductId]
,[PD0_SUO_SupplierId]
,[PD0_BD0_BrandId]
,[PD0_ProductCodeSU]
,[PD0_ColorSUId]
,[PD0_SizeSUId]
,[PD0_SKUSU]
,[PD0_Code]
,[PD0_ColorId]
,[PD0_SizeId]
,[PD0_SKU]
,[PD0_YearId]
,[PD0_SeasonId]
,[PD0_Lvl1]
,[PD0_Lvl2]
,[PD0_Lvl3]
,[PD0_SourceCountry]
,[PD0_Composition]
,[PD0_QulityLevel]
,[PD0_StandardId]
,[PD0_SecurityLevelId]
,cast([PD0_PurchasePrice] as decimal(20,2)) as PD0_PurchasePrice
,cast([PD0_SalePrice] as decimal(20,2)) as PD0_SalePrice
,[PD0_UnitId]
,[PD0_IsPrinted]
,[PD0_Status]
,[PD0_RgUser]
,[PD0_RgDt]
,[PD0_LmUser]
,[PD0_LmDt]
,[PD0_ColorSUId]
,[PD0_PicPath]
,[PD0_Lvl0]
,A.[SU0_SupplierCode]
,B.[BD0_BrandName]
,C.[DC0_DictName1] AS YearDictName
,D.[DC0_DictName1] AS SeasonDictName
,E.[DC0_DictName1] AS StandardDictName
,F.[DC0_DictName1] AS SecurityLevelDictName
,G.[DC0_DictName1] AS ColorSUDictName
,H.[DC0_DictName1] AS SizeSUDictName
,I.[DC0_DictName1] AS Lvl1DictName
,J.[DC0_DictName1] AS Lvl2DictName
,K.[DC0_DictName1] AS ColorBSUName
,M.[DC0_DictName1] AS ColorDictName
,N.[DC0_DictName1] AS SizeDictName
FROM B01_MDM.PD0_Product WITH ( NOLOCK )
LEFT JOIN B01_MDM.SU0_Supplier A ON PD0_SUO_SupplierId=A.SU0_SupplierId
LEFT JOIN B01_MDM.BD0_Brand B ON PD0_BD0_BrandId=B.BD0_BrandId
LEFT JOIN M01_CONF.DC0_Dictionary C ON PD0_YearId=C.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary D ON PD0_SeasonId=D.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary E ON PD0_StandardId=E.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary F ON PD0_SecurityLevelId=F.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary G ON PD0_ColorSUId=G.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary H ON PD0_SizeSUId=H.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary I ON PD0_Lvl1=I.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary J ON PD0_Lvl2=J.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary K ON PD0_ColorSUId=K.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary M ON PD0_ColorId=M.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary N ON PD0_SizeId=N.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [PD0_LmDt] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B01_MDM.PD0_Product");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B01_MDM.PD0_Product SET PD0_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PD0_ProductId='" + ds.Tables["LIST"].Rows[i]["PD0_ProductId"].ToString() + @"'
AND ISNULL(PD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B01_MDM.PD0_Product WHERE PD0_ProductId=@param0
AND ISNULL(PD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PD0_ProductId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["PD0_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PD0_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PD0_LmDt"))
{
dt.Columns.Add("PD0_LmDt");
}
// if (!dt.Columns.Contains("PD0_Status"))
// {
// dt.Columns.Add("PD0_Status");
// }
if (!dt.Columns.Contains("PD0_UptNo"))
{
dt.Columns.Add("PD0_UptNo");
}
// dt.Rows[0]["PD0_Status"] = '0';
dt.Rows[0]["PD0_LmDt"] = DateTime.Now;
dt.Rows[0]["PD0_UptNo"] = strCode;
}
conn.Update("B01_MDM.PD0_Product", dt, "PD0_ProductId='" + ds.Tables["LIST"].Rows[i]["PD0_ProductId"].ToString() + @"'");
}
else //add
{
// string PD0_ProductId = Guid.NewGuid().ToString();
// if (!dt.Columns.Contains("PD0_ProductId"))
// {
// dt.Columns.Add("PD0_ProductId");
// }
// if (!dt.Columns.Contains("PD0_Status"))
// {
// dt.Columns.Add("PD0_Status");
// }
if (!dt.Columns.Contains("PD0_RgDt"))
{
dt.Columns.Add("PD0_RgDt");
}
if (!dt.Columns.Contains("PD0_LmDt"))
{
dt.Columns.Add("PD0_LmDt");
}
if (!dt.Columns.Contains("PD0_UptNo"))
{
dt.Columns.Add("PD0_UptNo");
}
// dt.Rows[0]["PD0_ProductId"] = PD0_ProductId;
// dt.Rows[0]["PD0_Status"] = '0';
dt.Rows[0]["PD0_RgDt"] = DateTime.Now;
dt.Rows[0]["PD0_LmDt"] = DateTime.Now;
dt.Rows[0]["PD0_UptNo"] = '0';
conn.Insert("B01_MDM.PD0_Product", dt);
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 单点登录用户信息认证
/// </summary>
/// <param name="pLoginEnv">认证的环境</param>
/// <param name="pUserName">用户名</param>
/// <param name="pPassword">密码</param>
/// <returns></returns>
private bool CheckUserInfo(string pLoginEnv, string pUserName, string pPassword, string pEnv, bool pMD5)
{
bool returnValue = false;
try
{
if (pLoginEnv.ToUpper() == "SSO")
{
pLoginEnv = "AM";
}
string[] strParam = new string[1];
strParam[0] = pUserName.Trim();
Dbconn conn = new Dbconn("VexSSO");
VexSSOAccreditatio cls = new VexSSOAccreditatio();
cls.AccreditatioType = pLoginEnv;
cls.UserName = strParam[0];
cls.PassWord = pPassword;
cls.MD5 = pMD5;
cls.Execute();
//取到环境信息的相关信息
DataTable dt_Env = conn.GetDataTable(@"
SELECT t1.Env_ID ,
t2.Company_ID ,
t2.Company_sn
FROM DBO.MDM_System_Env t1 WITH ( NOLOCK )
JOIN dbo.MDM_Company t2 WITH ( NOLOCK ) ON t1.Company_ID = t2.Company_ID
WHERE ENV_SN=@param0", new string[1] { pEnv });
if (dt_Env.Rows.Count == 1)
{
if (pLoginEnv.ToUpper() == "VEXSSO")
{
hzyMessage.VexSSONewID = cls.AccreditatioSystemHZYMessage.VexSSONewID;
hzyMessage.LoginEnv_ID = "0";
}
else
{
//获取VexSSONewID及LoginEnvID
string strSQL = @"
SELECT VexSSONewID ,
t2.Env_ID
FROM [dbo].[MDM_System_Env] t2
LEFT JOIN [dbo].[MDM_System_User] t1 ON t2.Env_ID = t1.Env_ID
AND t1.App_UserName = @param1
WHERE t2.Env_SN = @param0
";
DataTable dtSSOInfo = conn.GetDataTable(strSQL, new string[] { pLoginEnv, strParam[0] });
string strVexSSONEWID = "";
string strLoginEnv_ID = "";
if (dtSSOInfo.Rows.Count > 0)
{
strLoginEnv_ID = dtSSOInfo.Rows[0]["Env_ID"].ToString();
strVexSSONEWID = dtSSOInfo.Rows[0]["VexSSONewID"].ToString();
}
hzyMessage.VexSSONewID = strVexSSONEWID;
hzyMessage.LoginEnv_ID = strLoginEnv_ID;
}
hzyMessage.Env_ID = dt_Env.Rows[0]["Env_ID"].ToString();
hzyMessage.Env_SN = pEnv;
hzyMessage.Company_ID = dt_Env.Rows[0]["Company_ID"].ToString();
hzyMessage.Company_SN = dt_Env.Rows[0]["Company_sn"].ToString();
hzyMessage.Message = "登录成功！";
hzyMessage.User_ID = cls.AccreditatioSystemHZYMessage.User_ID;
hzyMessage.User_Name = cls.AccreditatioSystemHZYMessage.User_Name;
hzyMessage.User_Name_CN = cls.AccreditatioSystemHZYMessage.User_Name_CN;
hzyMessage.LoginEnv_SN = pLoginEnv;
returnValue = true;
}
else
{
hzyMessage.Message = "环境信息错误！";
returnValue = false;
}
}
catch (Exception ex)
{
hzyMessage.Message = ex.Message;
returnValue = false;
}
return returnValue;
}
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;
}
}
/// <summary>
/// 进行登录信息写入
/// </summary>
/// <returns></returns>
private string DoLogin(string pIP, string pComputerName)
{
string strGUID = "";
Dbconn conn = new Dbconn("VexSSO");
DataTable dt_Guid = conn.GetDataTable("SELECT NEWID() AS GUID", new string[0]);
strGUID = dt_Guid.Rows[0][0].ToString();
DataTable dt = new System.Data.DataTable();
DateTime time = DateTime.Now;
dt.Columns.Add("SSO_Guid");
dt.Columns.Add("Company_sn");
dt.Columns.Add("Company_ID");
dt.Columns.Add("Env_sn");
dt.Columns.Add("Env_ID");
dt.Columns.Add("Login_Env_sn");
dt.Columns.Add("Login_Env_ID");
dt.Columns.Add("VexSSONewID");
dt.Columns.Add("User_ID");
dt.Columns.Add("User_Name");
dt.Columns.Add("User_Name_CN");
dt.Columns.Add("Request_IP");
dt.Columns.Add("Request_ComputerName");
dt.Columns.Add("Login_Time");
dt.Columns.Add("Last_Request_Time");
DataRow dr = dt.NewRow();
dr["SSO_Guid"] = strGUID;
dr["Company_sn"] = hzyMessage.Company_SN;
dr["Company_ID"] = hzyMessage.Company_ID;
dr["Env_sn"] = hzyMessage.Env_SN;
dr["Env_ID"] = hzyMessage.Env_ID;
dr["Login_Env_ID"] = hzyMessage.LoginEnv_ID;
dr["Login_Env_sn"] = hzyMessage.LoginEnv_SN;
dr["User_ID"] = hzyMessage.User_ID;
dr["User_Name"] = hzyMessage.User_Name;
dr["User_Name_CN"] = hzyMessage.User_Name_CN;
if (hzyMessage.VexSSONewID != "")
{
dr["VexSSONewID"] = hzyMessage.VexSSONewID;
}
dr["Request_IP"] = pIP;
dr["Request_ComputerName"] = pComputerName;
dr["Login_Time"] = time;
dr["Last_Request_Time"] = time;
dt.Rows.Add(dr);
conn.BeginTransaction();
try
{
conn.Insert("SSO_Login", dt);
conn.CommitTransaction();
}
catch (System.Exception ex)
{
conn.RollbackTransaction();
strGUID = "";
}
return strGUID;
}
