/// <summary>
/// 删除系统权限信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM");
try
{
string strAuthorization_ID = m_request.Tables[0].Rows[0]["Authorization_ID"].ToString();
m_request.Tables[0].Columns.Remove("Authorization_ID");
ArrayList listTable = new ArrayList();
listTable.Add("MDM_System_Authorization");
listTable.Add("MDM_System_Menu_AUZ");
conn.BeginTransaction();
conn.TableLock(listTable);
if (strAuthorization_ID != "")
{
string strWhere = " Authorization_ID=" + strAuthorization_ID;
conn.Delete("MDM_System_Authorization", strWhere);
conn.Delete("MDM_System_Menu_AUZ", strWhere);
}
conn.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
/// <summary>
/// 编辑系统属性信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM");
try
{
string strProperty_ID = m_request.Tables[0].Rows[0]["Property_ID"].ToString();
m_request.Tables[0].Columns.Remove("Property_ID");
ArrayList listTable = new ArrayList();
listTable.Add("MDM_System_Property");
conn.BeginTransaction();
conn.TableLock(listTable);
if (strProperty_ID == "")
{
conn.Insert("MDM_System_Property", m_request.Tables[0]);
}
else
{
string strWhere = " Property_ID=" + strProperty_ID;
conn.Update("MDM_System_Property", m_request.Tables[0], strWhere);
}
conn.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
/// <summary>
/// 编辑WS信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("WSRR");
try
{
if(m_request == null
|| m_request.Tables.Count==0
|| m_request.Tables[0].Rows.Count == 0)
{
return true;
}
string strParam_ID = m_request.Tables[0].Rows[0]["Param_ID"].ToString();
ArrayList listTable = new ArrayList();
listTable.Add("WS_Param");
conn.BeginTransaction();
conn.TableLock(listTable);
string strWhere = " Param_ID = '" + strParam_ID + "'";
conn.Delete("WS_Param", strWhere);
conn.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
/// <summary>
/// EnvoTemplates信息的CRUD
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
DataSet ds_Return = new DataSet();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
if (strXML == "")
{
throw new Exception("XML不能为空");
}
string strTemplate_ID = m_request.Tables[0].Rows[0]["Template_ID"].ToString();
if (strTemplate_ID == "")
{
throw new Exception("Template_ID不能为空");
}
DataSet ds = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = ds.Tables["OPTYPE"];
Dbconn conn = new Dbconn("HZY_SDK");
if (dt_OPTYPE.Rows[0][0].ToString().ToUpper() == "INSERT")
{
bool bResult = true;
DataTable dt = new DataTable();
dt.Columns.Add("Template_ID");
dt.Rows.Add(new object[] { strTemplate_ID });
Commom_Ds.SetUpdateInfo(dt, hzyMessage);
conn.BeginTransaction();
bResult = conn.Insert("Log_ViewTemlpates", dt);
conn.ExcuteQuerryByTran("UPDATE EnvoTemplates set ViewCount=ViewCount+1 where Template_ID=" + strTemplate_ID);
conn.CommitTransaction();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "修改成功" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "修改失败" });
}
ds_Return.Tables.Add(dt_EditResult);
}
return ds_Return;
}
public DataSet GetData()
{
Dbconn conn = new Dbconn("WSRR");
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataTable dt_EditResult = new dsCommon.dtResultDataTable();
DataSet dsRequest = new DataSet();
DataSet ds_Return = new DataSet();
dsRequest = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = dsRequest.Tables["OPTYPE"];
DataTable dt_LIST = dsRequest.Tables["LIST"];
for (int i = 0; i < dt_OPTYPE.Rows.Count; i++)
{
//获取用户权限信息
if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GET")
{
string strUserName = dt_LIST.Rows[0]["UserName"].ToString();
string strSQL = @"
SELECT DISTINCT
T1.[AUZID]
,T1.[AUZName]
,0 AS Authorization_ID_F
, CASE WHEN T2.USERNAME IS NULL THEN '0'
ELSE '1' END HavaFlag
FROM [WSRR_Manage].[F01_CERT].[AUZ] T1 WITH ( NOLOCK )
LEFT JOIN [WSRR_Manage].[F01_CERT].[UserAUZ] T2 WITH ( NOLOCK ) ON T1.AUZID=T2.AUZID
AND T2.USERNAME=@param0";
ds_Return = conn.GetDataSet(strSQL, new string[] { strUserName });
}
//更新用户权限
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "UPDATE")
{
string strUserName = dt_LIST.Rows[0]["UserName"].ToString();
conn.LockTableList.Add("[WSRR_Manage].[F01_CERT].[UserAUZ]");//锁定表
conn.BeginTransaction();
//1.删除该用户的所有权限
string strWhere = " UserName="******"[WSRR_Manage].[F01_CERT].[UserAUZ]", strWhere);
//2.新增勾选的权限
bool insertFlag = conn.Insert("[WSRR_Manage].[F01_CERT].[UserAUZ]", dt_LIST);
conn.CommitTransaction();
//删除操作和插入操作都正确的情况下
if (deleteFlag && insertFlag)
{
dt_EditResult.Rows.Add(new object[] { true, strUserName + "权限更新成功!" });
}
else
{
dt_EditResult.Rows.Add(new object[] { true, strUserName + "权限更新失败!" });
}
ds_Return.Tables.Add(dt_EditResult);
}
}
return ds_Return;
}
public 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()
{
DataSet ds_Return = null;
Dbconn conn = new Dbconn("WSRR_Log");
conn.BeginTransaction();
conn.LockTableList.Add("Log_CallByXML");
conn.LockTableList.Add("Log_CallByXML_His");
string time = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss.fff");
DataSet ds = conn.GetDataSetInTrans(@" SELECT
*,
NULL AS ElapsedTime,
NULL AS WS_ID,
NULL AS Session_ID,
NULL AS AppSN
FROM Log_CallByXML WHERE Log_Time<'" + time + "'");
conn.ExcuteQuerryByTran("INSERT INTO Log_CallByXML_HIS SELECT * FROM Log_CallByXML WHERE Log_Time<'" + time + "'");
conn.CommitTransaction();
//开始分析
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string strXML = ds.Tables[0].Rows[i]["XmlInput"].ToString();
string strWS_ID = "";
string strSession_ID = "";
string strAppSN = "";
try
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(strXML);
XmlNode xml = xmlDoc.DocumentElement;
strAppSN = xml.Attributes["App"].Value;
strWS_ID = xml.Attributes["WSID"].Value;
strSession_ID = xml.Attributes["SessionID"].Value;
xml = null;
xmlDoc = null;
}
catch (Exception)
{
}
//ds.Tables[0].Rows
}
return ds_Return;
}
/// <summary>
/// 获取系统环境信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM");
try
{
string strEnv_ID = m_request.Tables[0].Rows[0]["Env_ID"].ToString();
string strEnv_SN = m_request.Tables[0].Rows[0]["Env_SN"].ToString();
m_request.Tables[0].Columns.Remove("Env_ID");
ArrayList listTable = new ArrayList();
listTable.Add("MDM_System_Env");
conn.BeginTransaction();
conn.TableLock(listTable);
//核对环境简称是否存在
string strSQL = "SELECT 1 AS a FROM MDM_System_Env where Env_SN='" + strEnv_SN + "'";
if(strEnv_ID != "")
{
strSQL += " AND Env_ID !=" + strEnv_ID;
}
DataTable dtTemp = conn.GetDataTableInTrans(strSQL);
if (dtTemp.Rows.Count > 0)
{
throw new Exception(strEnv_SN + "已存在！");
}
if (strEnv_ID == "")
{
conn.Insert("MDM_System_Env", m_request.Tables[0]);
}
else
{
string strWhere = " Env_ID=" + strEnv_ID;
conn.Update("MDM_System_Env", m_request.Tables[0], strWhere);
}
conn.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
/// <summary>
/// 供销售日报使用
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn_PLM_AS = new Dbconn("F22GP_ForSalesPlan");
DataSet ds = conn_PLM_AS.GetDataSet(
@" SELECT 94 AS Env_ID , --HZY_Sale_Report
null AS VexSSONewID ,
b.userid AS App_UserName ,
RIGHT(sys.fn_VarBinToHexStr(hashbytes('MD5', CONVERT(VARCHAR(100), b.password))), 32) AS App_Password ,
b.password AS App_PasswordSrc ,
b.username AS App_UserName_CN ,
GETDATE() Chang_Password_Time ,
1 Checked ,
'system' Check_User ,
GETDATE() Check_Time ,
GETDATE() Createtime ,
GETDATE() Updatetime
FROM j_depot a ,
j_user b
WHERE a.depotid = b.depotid
AND a.m_type = 11
");
Dbconn connMDM = new Dbconn("VexSSO");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("MDM_System_User");
connMDM.TableLock(tableList);
//删除数据
connMDM.ExcuteQuerryByTran(@"DELETE FROM MDM_System_User WHERE ENV_ID=94");
connMDM.InsertBYSQLBC("MDM_System_User", ds.Tables[0]);
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
/// <summary>
/// 编辑系统属性值信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM");
try
{
string strSystem_ID = m_request.Tables[0].Rows[0]["System_ID"].ToString();
string strSystem_Code = m_request.Tables[0].Rows[0]["System_Code"].ToString();
DataTable dt_Add = new DataTable();
dt_Add.Columns.Add("System_ID");
dt_Add.Columns.Add("System_Code");
dt_Add.Columns.Add("Property_ID");
dt_Add.Columns.Add("Property_Text");
for (int i = 2; i < m_request.Tables[0].Columns.Count; i++)
{
string strValue = m_request.Tables[0].Rows[0][i].ToString();
if (strValue != "")
{
dt_Add.Rows.Add(new object[] { strSystem_ID, strSystem_Code, m_request.Tables[0].Columns[i].ToString(),strValue});
}
}
ArrayList listTable = new ArrayList();
listTable.Add("MDM_System_Property_info");
conn.BeginTransaction();
conn.TableLock(listTable);
string strWhere = " System_ID=" + strSystem_ID;
conn.Delete("MDM_System_Property_info", strWhere);
conn.InsertBYSQLBC("MDM_System_Property_info", dt_Add);
conn.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
/// <summary>
/// 删除任务参数信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM"); //创建连接类
try
{
if (m_request == null
|| m_request.Tables.Count == 0
|| m_request.Tables[0].Rows.Count == 0)
{
return true;
}
string strParam_ID = m_request.Tables[0].Rows[0]["param_id"].ToString(); //获取参数Param_ID
string strTASK_ID = m_request.Tables[0].Rows[0]["task_id"].ToString(); //获取参数Task_ID
ArrayList listTable = new ArrayList();
listTable.Add("WS_SchedulingParam");
conn.BeginTransaction(); //开启事务
conn.TableLock(listTable); //锁表WS_SchedulingParam
string strWhere = " Param_ID = '" + strParam_ID + "'"; //创建where语句
conn.Delete("WS_SchedulingParam", strWhere); //删除参数
//更新主表
DataTable dtScheduling = new DataTable();
dtScheduling.Columns.Add("UpdateTime");
DataRow drScheduling = dtScheduling.NewRow();
drScheduling["UpdateTime"] = DateTime.Now;
dtScheduling.Rows.Add(drScheduling);
string strSchedulingWhere = " 1=1 AND TASK_ID = '" + strTASK_ID + "'";
conn.Update("WS_Scheduling", dtScheduling, strSchedulingWhere); //更新数据
conn.CommitTransaction(); //提交事务
return true;
}
catch
{
conn.RollbackTransaction(); //回滚
throw;
}
}
/// <summary>
/// 删除任务信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM");
try
{
if (m_request == null
|| m_request.Tables.Count == 0
|| m_request.Tables[0].Rows.Count == 0)
{
return true;
}
string strTASK_ID = m_request.Tables[0].Rows[0]["task_id"].ToString(); //获取参数TASK_ID
ArrayList listTable = new ArrayList();
listTable.Add("WS_Scheduling");
conn.BeginTransaction(); //事物事务
conn.TableLock(listTable); //锁表WS_Scheduling
DataTable dt_Add = new DataTable() ;
dt_Add.Columns.Add("IsDeleted");
dt_Add.Columns.Add("Enabled");
dt_Add.Columns.Add("UpdateUser");
dt_Add.Columns.Add("UpdateTime");
DataRow dr = dt_Add.NewRow();
dr["IsDeleted"] = "True"; //将删除标志设为“True”
dr["Enabled"] = "False"; //将可用标志设为“False”
dr["UpdateTime"]=DateTime.Now; //更新时间设为当前时间
dr["UpdateUser"] = hzyMessage.User_Name; //设置更新者
dt_Add.Rows.Add(dr);
string strWhere = " TASK_ID = '" + strTASK_ID + "'";
conn.Update("WS_Scheduling", dt_Add, strWhere); //更新操作
conn.CommitTransaction(); //提交事务
return true;
}
catch
{
conn.RollbackTransaction(); //回滚
throw;
}
}
/// <summary>
/// 对CFG_RequestType的CRUD,为Easyui专用
/// </summary>
/// <returns></returns>
public bool Execute()
{
DateTime time = DateTime.Now;
Dbconn conn = new Dbconn("VexSSO");
conn.LockTableList.Add("SSO_Login");
conn.BeginTransaction();
conn.TableLock();
string strSQL = @"
UPDATE dbo.SSO_Login
SET Logout_Time='" + time.ToString("yyyy-MM-dd HH:mm:ss.fff")+ @"',
MARK='SYSTEM'
WHERE Last_Request_Time<='" + time.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss.fff") + @"'
AND Logout_Time IS NULL
";
conn.ExcuteQuerryByTran(strSQL);
conn.CommitTransaction();
return true ;
}
/// <summary>
/// 编辑县级市信息
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
Dbconn conn = new Dbconn("MDM_Master2");
try
{
DataSet ds_Return = new DataSet();
DataSet dsRequest = new DataSet();
string strXML = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
if (strXML == "")
{
throw new Exception("提交的XML为空！");
}
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = dsXML.Tables["OPTYPE"];
string strMDM_Class = "0016";
string strMDM_LinkClass = "L0011";
conn.LockTableList.Add("MDM_" + strMDM_LinkClass + "_B");
conn.LockTableList.Add("MDM_" + strMDM_Class + "_B");
conn.LockTableList.Add("MDM_" + strMDM_LinkClass + "_E");
conn.LockTableList.Add("MDM_" + strMDM_Class + "_E");
conn.BeginTransaction();
//获取所有属性
MDM2_GetEntityPropertyList cls = new MDM2_GetEntityPropertyList();
cls.hzyMessage = this.hzyMessage;
cls.MainEntity = strMDM_Class;
DataTable dtPorperty = cls.GetData().Tables[0];
for (int i = 0; i < dt_OPTYPE.Rows.Count; i++)
{
DataTable dt_List = dsXML.Tables[i + 1];
if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "UPDATE"
|| dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERT"
)
{
//插入BODY表
for (int j = 0; j < dt_List.Rows.Count; j++)
{
string strBody_id_0016 = "";
string strBody_id_L0011 = "";
string strBody_id_0015 = null;//用于存放地级市的信息，如果为null，表示不做变更，如果不为NULL,需要变更
if (dt_List.Columns.Contains("Body_id_0015"))
{
strBody_id_0015 = dt_List.Rows[j]["Body_id_0015"].ToString();
if (strBody_id_0015 != "")
{
//核对boid_ID是否存在
MDM2_BodyIDCheck clsCheck = new MDM2_BodyIDCheck();
clsCheck.Conn = conn;
clsCheck.hzyMessage = this.hzyMessage;
clsCheck.MDMClass = "0015";
clsCheck.Body_ID = strBody_id_0015;
if (clsCheck.Execute() == false)
{
throw new Exception("省的信息不正确！");
}
}
}
strBody_id_0016 = dt_List.Rows[j]["body_ID_0016"].ToString();
strBody_id_L0011 = dt_List.Rows[j]["body_ID"].ToString();
MDM2_EntityCreatedBodyID_Edit clsBody = new MDM2_EntityCreatedBodyID_Edit();
if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERT")
{
//处理0016的实体
clsBody.Conn = conn;
clsBody.hzyMessage = hzyMessage;
clsBody.MDMClass = strMDM_Class;
clsBody.Execute();
strBody_id_0016 = clsBody.BodyID;
if (strBody_id_0016 == "")
{
throw new Exception("BodyID_0016创建失败！");
}
if (strBody_id_0015 == null)
{
throw new Exception("省信息不正确！");
}
strBody_id_L0011 = "";
}
else if (strBody_id_0016 != "" && strBody_id_L0011 != "") //更新
{
//核对0010是否存在
MDM2_BodyIDCheck clsCheck = new MDM2_BodyIDCheck();
clsCheck.hzyMessage = this.hzyMessage;
clsCheck.MDMClass = strMDM_Class;
clsCheck.Body_ID = strBody_id_0016;
clsCheck.Conn = conn;
if (clsCheck.Execute() == false)
{
throw new Exception("Body_id_0016信息不正确！");
}
//核对L0010是否存在
clsCheck.hzyMessage = this.hzyMessage;
clsCheck.MDMClass = strMDM_LinkClass;
clsCheck.Body_ID = strBody_id_L0011;
clsCheck.Conn = conn;
if (clsCheck.Execute() == false)
{
throw new Exception("Body_id_L0011信息不正确！");
}
}
else
{
throw new Exception("传入的参数有误，如果反复出现，请重新登录！");
}
clsBody.MDMClass = strMDM_LinkClass;
clsBody.BodyID = strBody_id_L0011;
clsBody.LinkBodyIDList = strBody_id_0015 + "," + strBody_id_0016;
clsBody.Execute();
strBody_id_L0011 = clsBody.BodyID;
//处理0015的实体
for (int k = 0; k < dt_List.Columns.Count; k++)
{
string strColumnName = dt_List.Columns[k].ColumnName;
if (dtPorperty.Select("Property_Name='" + strColumnName + "'").Length == 0)
{
continue;
}
MDM2_EntityPropertyValue_Edit clsProperty = new MDM2_EntityPropertyValue_Edit();
clsProperty.Conn = conn;
clsProperty.hzyMessage = hzyMessage;
clsProperty.MDMClass = strMDM_Class;
clsProperty.ExpressionName = "CN";
clsProperty.PropertyName = strColumnName;
clsProperty.BodyID = strBody_id_0016;
clsProperty.PropertyValue = dt_List.Rows[j][k].ToString();
if (clsProperty.Execute() == false)
{
throw new Exception("修改失败，" + clsProperty.Message);
}
}
}
}
else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "DELETE")
{
for (int rowIndex = 0; rowIndex < dt_List.Rows.Count; rowIndex++)
{
string strBody_id_L0011 = dt_List.Rows[rowIndex]["body_id"].ToString();
string strBody_ID_0016 = conn.GetDataTableFirstValue("select body_id_0016 from MDM_L0011_B where Body_id=@param0", new string[1] { strBody_id_L0011 }).ToString();
MDM2_EntityDeleteBodyID_Edit clsDelete = new MDM2_EntityDeleteBodyID_Edit();
clsDelete.Conn = conn;
clsDelete.hzyMessage = hzyMessage;
//删除行政区实体的数据
if (strBody_ID_0016 != "")
{
clsDelete.MDMClass = strMDM_Class;
clsDelete.BodyID = strBody_ID_0016;
if (clsDelete.Execute() == false)
{
throw new Exception("删除失败。");
}
}
//删除Link数据
clsDelete.MDMClass = strMDM_LinkClass;
clsDelete.BodyID = strBody_id_L0011;
if (clsDelete.Execute() == false)
{
throw new Exception("删除失败。");
}
}
}
}
dt_EditResult.Rows.Add(new object[] { true, "操作成功" });
ds_Return.Tables.Add(dt_EditResult);
conn.CommitTransaction();
return ds_Return;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("AM");
Dbconn connMDM = new Dbconn("VexSSO");
conn.BeginTransaction();
connMDM.BeginTransaction();
try
{
DataSet ds = conn.GetDataSet(
@"
DECLARE @maxid AS BINARY(10);
SELECT t1.[__$start_lsn] AS LogID,
t1.[__$seqval] ,
t1.[__$operation] OprationType ,
t1.CN_ID ,
t1.CN_LOGIN ,
t1.CN_USER_NAME ,
t1.CN_PASSWORD ,
Convert(int,t1.CN_ISDELETE) as CN_ISDELETE ,
Convert(int,t1.CN_DISABLED) as CN_DISABLED
INTO #tmp
FROM [cdc].[dbo_TN_SYS_EM_USER_CT] t1 ,
[M01_CONF].[S01_Sync] t2
WHERE 1=1
AND t1.[__$start_lsn]> ISNULL(t2.MaxStart_lsn,0x00023FE600000FBF0004)
AND t2.SyncName='MDM数据获取'
AND t1.[__$update_mask] !=0x00004000
AND t1.[__$update_mask] !=0x00800000
AND t1.[__$update_mask] !=0x00804000
SELECT @maxid=MAX(LogID)
FROM #tmp
if(@maxid is not null)
begin
UPDATE [M01_CONF].[S01_Sync] SET MaxStart_lsn=@maxid
WHERE SyncName='MDM数据获取'
end
select * from #tmp
order by [LogID] ASC,[__$seqval] ASC ,[OprationType] ASC
");
string strSQL = "";
Byte[] strMaxID = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
strMaxID = (Byte[])ds.Tables[0].Rows[i]["LogID"];
string strCN_LOGIN = ds.Tables[0].Rows[i]["CN_LOGIN"].ToString();
string strCN_PASSWORD = ds.Tables[0].Rows[i]["CN_PASSWORD"].ToString();
string strCN_ISDELETE = ds.Tables[0].Rows[i]["CN_ISDELETE"].ToString();
string strOprationType = ds.Tables[0].Rows[i]["OprationType"].ToString();
if (strOprationType == "1" || strOprationType == "4") //添加或者更新
{
if (connMDM.GetDataTableRowCount("select 1 as a from MDM_Employees where SSO_UserName=@param0", new string[] { strCN_LOGIN }) == 0)
{
strSQL = @"INSERT INTO MDM_Employees (Name,SSO_UserName,SSO_Password,Leave_Flag,IsActived)
Values(@param0,@param1,@param2,@param3,'1')";
}
else
{
strSQL = @"update MDM_Employees
SET SSO_Password=@param2,Leave_Flag=@param3
WHERE SSO_UserName=@param1
";
}
connMDM.ExcuteQuerryByTran(strSQL, new string[] { strCN_LOGIN, strCN_LOGIN, strCN_PASSWORD, strCN_ISDELETE });
}
else if (strOprationType == "2" || strOprationType == "3") //删除
{
strSQL = @"update MDM_Employees
SET Leave_Flag=@param0
WHERE SSO_UserName=@param1";
connMDM.ExcuteQuerryByTran(strSQL, new string[] { "1", strCN_LOGIN });
}
}
if (strMaxID != null)
{
strSQL = "UPDATE M01_CONF.S01_Sync SET MaxStart_lsn=@param0,LastSyncTime=getdate() WHERE SyncName='MDM数据获取'";
conn.ExcuteQuerryByTran(strSQL, new object[] { strMaxID });
}
conn.CommitTransaction();
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
conn.RollbackTransaction();
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(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 SetBMTypes(Dbconn conn, String strType)
{
String strCode = "";
DataSet ds_Return = new DataSet();
string strActor = m_request.Tables[0].Rows[0]["actor"].ToString();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
String strOPType = dsXML.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper();
String strID = dsXML.Tables["LIST"].Rows[0]["ct1_id"].ToString();
//String strFatherID = dsXML.Tables["LIST"].Rows[0]["ct1_fatherid"].ToString();
DataTable dt_List = dsXML.Tables["LIST"];
if (strID == "")
{
throw new Exception("数据异常，缺少主键，请联系管理员！");
}
bool bResult = false;
if (strType == "NewBMType") //新建大小类
{
conn.BeginTransaction();
bResult = conn.Insert("M01_CONF.CT0_CSYTAB", dt_List);
conn.CommitTransaction();
}
else if (strType == "UpdBMType") //修改大小类
{
conn.BeginTransaction();
bResult = conn.Update("M01_CONF.CT0_CSYTAB", dt_List, "ct1_id='" + strID + "'");
conn.CommitTransaction();
}
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, strCode });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "保存失败！" });
}
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
/// <summary>
/// 新建或修改固定资产类别记录
/// </summary>
/// <returns></returns>
public DataSet SetFATypes(Dbconn conn, String strType)
{
String strCode = "";
DataSet ds_Return = new DataSet();
string strActor = m_request.Tables[0].Rows[0]["actor"].ToString();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
String strOPType = dsXML.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper();
String strID = dsXML.Tables["LIST"].Rows[0]["ct1_id"].ToString();
String strFatherID = dsXML.Tables["LIST"].Rows[0]["ct1_fatherid"].ToString();
DataTable dt_List = dsXML.Tables["LIST"];
if (strID == "")
{
throw new Exception("数据异常，缺少主键，请联系管理员！");
}
bool bResult = false;
if (strType == "NewFATypeDL") //新建大类
{
String strSQL1 = @" SELECT MAX(CT1_Code) AS code "
+ @" FROM M01_CONF.CT0_CSYTAB WHERE 1=1 "
+ " AND ISNULL(CONVERT(VARCHAR(50),CT1_FatherID),'') = '" + strFatherID
+ "'";
DataSet ds1 = conn.GetDataSet(strSQL1);
if (ds1.Tables == null)
{
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = "01";
}
else
{
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = int.Parse(ds1.Tables[0].Rows[0]["code"].ToString()) + 1;
if (int.Parse(ds1.Tables[0].Rows[0]["code"].ToString()) < 9)
{
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = "0" + dsXML.Tables["LIST"].Rows[0]["ct1_code"].ToString();
}
}
conn.BeginTransaction();
bResult = conn.Insert("M01_CONF.CT0_CSYTAB", dt_List);
conn.CommitTransaction();
}
else if (strType == "NewFATypeXL") //新建小类
{
String strSQL1 = @" SELECT MAX(CT1_Code) AS code "
+ @" FROM M01_CONF.CT0_CSYTAB WHERE 1=1 "
+ " AND ISNULL(CONVERT(VARCHAR(50),CT1_FatherID),'') = '" + strFatherID
+ "'";
DataSet ds1 = conn.GetDataSet(strSQL1);
if (ds1.Tables == null)
{
return ds_Return;
}
else
{
if (ds1.Tables[0].Rows[0]["code"].ToString() == "")
{
String strSQL2 = @" SELECT CT1_Code "
+ @" FROM M01_CONF.CT0_CSYTAB WHERE 1=1 "
+ " AND ISNULL(CONVERT(VARCHAR(50),CT1_ID),'') = '" + strFatherID
+ "'";
DataSet ds2 = conn.GetDataSet(strSQL2);
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = ds2.Tables[0].Rows[0]["CT1_Code"].ToString() + "001";
}
else
{
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = int.Parse(ds1.Tables[0].Rows[0]["code"].ToString()) + 1;
if (int.Parse(ds1.Tables[0].Rows[0]["code"].ToString()) < 9999)
{
dsXML.Tables["LIST"].Rows[0]["ct1_code"] = "0" + dsXML.Tables["LIST"].Rows[0]["ct1_code"].ToString();
}
}
}
//dt_List.Columns.Add("CT1_RgUser");
//dt_List.Rows[0]["CT1_RgUser"] = strActor;
conn.BeginTransaction();
bResult = conn.Insert("M01_CONF.CT0_CSYTAB", dt_List);
conn.CommitTransaction();
}
else if (strType == "UpdFAType") //修改大小类
{
conn.BeginTransaction();
bResult = conn.Update("M01_CONF.CT0_CSYTAB", dt_List, "ct1_id='" + strID + "'");
conn.CommitTransaction();
}
strCode = dsXML.Tables["LIST"].Rows[0]["ct1_code"].ToString();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, strCode });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "保存失败！" });
}
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
/// <summary>
/// 新建或修改记录
/// </summary>
/// <returns></returns>
public DataSet SaveRows(Dbconn conn, String strType)
{
DataSet ds_Return = new DataSet();
string strActor = m_request.Tables[0].Rows[0]["actor"].ToString();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
DataTable dtOptypes = dsXML.Tables["OPTYPE"];
bool bResult = true;
conn.BeginTransaction();
for (int i = 0; i < dtOptypes.Rows.Count; i++)
{
//如果包含rowindex，就循环移除
if (dsXML.Tables[i + 1].Columns.Contains("rowindex"))
{
dsXML.Tables[i + 1].Columns.Remove("rowindex");
}
//如果子表解析出来的XML，类型有insert，做新增
if (dtOptypes.Rows[i][0].ToString().ToUpper() == "INSERT")
{
DataTable dt_List1 = dsXML.Tables[i + 1];
//如果包含bg3_lmdt，就循环移除
if (dt_List1.Columns.Contains("ct1_lmdt"))
{
dt_List1.Columns.Remove("ct1_lmdt");
}
bResult = conn.Insert("M01_CONF.CT0_CSYTAB", dt_List1);
}
//如果类型包含，update，解析成表，做update
else if (dtOptypes.Rows[i][0].ToString().ToUpper() == "UPDATE")
{
DataTable dt_List2 = dsXML.Tables[i + 1];
//添加更新主键，按照GUID更新
ArrayList ListKey = new ArrayList();
ListKey.Add("ct1_id");
bResult = conn.Update("M01_CONF.CT0_CSYTAB", dt_List2, ListKey);
}
//如果类型包含，DELETE，解析成表，做DELETE
else if (dtOptypes.Rows[i][0].ToString().ToUpper() == "DELETE")
{
DataTable dt_List3 = dsXML.Tables[i + 1];
for (int k = 0; k < dt_List3.Rows.Count; k++)
{
string strID1 = dt_List3.Rows[k]["ct1_id"].ToString();
bResult = conn.Delete("M01_CONF.CT0_CSYTAB", "ct1_id=" + "'" + strID1 + "'");
}
}
}
conn.CommitTransaction();
if (bResult)
{
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>
/// 新建或修改鉴定单记录
/// </summary>
/// <returns></returns>
public DataSet SetITAppraisal(Dbconn conn, String strType)
{
DataSet ds_Return = new DataSet();
string strActor = m_request.Tables[0].Rows[0]["actor"].ToString();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//获取申购单主表数据
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
String strOPType = dsXML.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper();
String strID = dsXML.Tables["LIST"].Rows[0]["fa3_id"].ToString();
DataTable dt_List = dsXML.Tables["LIST"];
if (strID == "")
{
throw new Exception("数据异常，缺少主键，请联系管理员！");
}
bool bResult = false;
if (strType == "New") //新建单据
{
conn.BeginTransaction();
bResult = conn.Insert("[B02_BILL].[FA3_ITAppraisal]", dt_List);
conn.CommitTransaction();
}
else if (strType == "Upd") //修改单据
{
conn.BeginTransaction();
bResult = conn.Update("[B02_BILL].[FA3_ITAppraisal]", dt_List, "fa3_id='" + strID+"'");
conn.CommitTransaction();
}
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "保存成功！" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "保存失败！" });
}
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
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>
/// EnvoTemplates信息的CRUD
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
DataSet ds_Return = new DataSet();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
if (strXML == "")
{
throw new Exception("XML不能为空");
}
string strEnvo_ID = m_request.Tables[0].Rows[0]["Envo_ID"].ToString();
DataSet ds = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = ds.Tables["OPTYPE"];
Dbconn conn = new Dbconn("HZY_SDK");
if (dt_OPTYPE.Rows[0][0].ToString().ToUpper() == "GET")
{
if (strEnvo_ID == "")
{
throw new Exception("Envo_ID不能为空");
}
ds_Return = conn.GetDataSet(@"
SELECT
Template_ID ,
Envo_ID ,
Template_Name ,
--replace(replace(REPLACE([Text],CHAR(9),' '),char(13),'ㄧ'),char(10),'ㄧ') Text ,
Text ,
ViewCount ,
UpdateTime ,
UpdateUser
FROM dbo.EnvoTemplates
WHERE Envo_ID=@param0
ORDER BY ViewCount DESC
", new string[] { strEnvo_ID });
}
else if (dt_OPTYPE.Rows[0][0].ToString().ToUpper() == "UPDATE")
{
bool bResult = true;
if (ds.Tables["list"].Rows.Count > 0)
{
string strTemplate_ID = ds.Tables["list"].Rows[0]["Template_ID"].ToString();
if (strTemplate_ID == "")
{
throw new Exception("Template_ID不能为空");
}
ds.Tables["list"].Columns.Remove("Template_ID");
Commom_Ds.SetUpdateInfo(ds.Tables["list"], hzyMessage);
conn.BeginTransaction();
if (CheckName(ds.Tables["list"], conn) == false)
{
conn.RollbackTransaction();
throw new Exception("模板名称已经存在");
}
bResult = conn.Update("EnvoTemplates", ds.Tables["list"], "Template_ID='" + strTemplate_ID + "'");
conn.CommitTransaction();
}
if (bResult)
{
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[0][0].ToString().ToUpper() == "INSERT")
{
bool bResult = true;
if (ds.Tables["list"].Rows.Count > 0)
{
ds.Tables["list"].Columns.Remove("Template_ID");
Commom_Ds.SetUpdateInfo(ds.Tables["list"], hzyMessage);
conn.BeginTransaction();
if (CheckName(ds.Tables["list"], conn) == false)
{
conn.RollbackTransaction();
throw new Exception("模板名称已经存在");
}
bResult = conn.Insert("EnvoTemplates", ds.Tables["list"]);
conn.CommitTransaction();
}
if (bResult)
{
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[0][0].ToString().ToUpper() == "DELETE")
{
bool bResult = true;
if (ds.Tables["list"].Rows.Count > 0)
{
string strTemplate_ID = ds.Tables["list"].Rows[0]["Template_ID"].ToString();
if (strTemplate_ID == "")
{
throw new Exception("Template_ID不能为空");
}
conn.BeginTransaction();
bResult = conn.Delete("EnvoTemplates", "Template_ID='" + strTemplate_ID + "'");
conn.CommitTransaction();
}
if (bResult)
{
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>
/// 部署WS
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("WSRR");
Dbconn connDeploy = new Dbconn("WSRR_Deploy");
try
{
if(m_request == null
|| m_request.Tables.Count==0
|| m_request.Tables[0].Rows.Count == 0)
{
return true;
}
string strWS_ID = m_request.Tables[0].Rows[0]["WS_ID"].ToString();
string strSQL = "";
DataSet ds = conn.GetDataSet(@"
SELECT * FROM dbo.WS
WHERE WS_ID=@param0;
SELECT * FROM dbo.WS_Param
WHERE WS_ID=@param0",new string[]{strWS_ID});
if (ds.Tables.Count !=2 || ds.Tables[0].Rows.Count == 0)
{
throw new Exception("WS_ID的信息不正确！");
}
string strENV = ConfigurationManager.AppSettings["WSRR_ENV"];
if (strENV.ToLower() == "prod")
{
ds.Tables[0].Rows[0]["WS_Host"] = ds.Tables[0].Rows[0]["WS_Host_Test"].ToString();
}
else
{
ds.Tables[0].Rows[0]["WS_Host"] = ds.Tables[0].Rows[0]["WS_Host_Prod"].ToString();
}
ArrayList tableList = new ArrayList();
tableList.Add("WS");
tableList.Add("WS_Param");
connDeploy.BeginTransaction();
connDeploy.TableLock(tableList);
connDeploy.Delete("WS", "WS_ID='" + strWS_ID + "'");
connDeploy.Delete("WS_Param", "WS_ID='" + strWS_ID + "'");
connDeploy.Insert("WS", ds.Tables[0]);
connDeploy.Insert("WS_Param", ds.Tables[1]);
connDeploy.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
public bool Execute()
{
string strEnv_ID = "";
string strEnv_SN = "HZY_CMT";
Dbconn conn_HZY_SBS = new Dbconn("F22GP_ForSalesPlan");
DataSet ds = conn_HZY_SBS.GetDataSet(
@" SELECT
--98 AS Env_ID ,
NULL AS VexSSONewID ,
b.userid AS App_UserName ,
RIGHT(sys.fn_VarBinToHexStr(hashbytes('MD5', CONVERT(VARCHAR(100), b.password))), 32) AS App_Password ,
b.password AS App_PasswordSrc ,
CASE WHEN b.dptype <> 0 THEN ISNULL(a.depotid,'') + '_' + d_name ELSE b.username END AS App_UserName_CN ,
GETDATE() Chang_Password_Time ,
1 Checked ,
'system' Check_User ,
GETDATE() Check_Time ,
GETDATE() Createtime ,
GETDATE() Updatetime,
0 as oprationFlag
FROM
j_user b
LEFT JOIN j_depot a ON a.depotid = b.depotid
");
Dbconn connMDM = new Dbconn("VexSSO");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("MDM_System_User");
connMDM.TableLock(tableList);
strEnv_ID = connMDM.GetDataTableFirstValue("SELECT [Env_ID] FROM [MDM_System_Env] WHERE Env_SN='" + strEnv_SN + "'").ToString();
DataTable dtMDM = connMDM.GetDataTable(@"
SELECT [App_UserID]
,[Env_ID]
,[VexSSONewID]
,[App_UserName]
,[App_Password]
,[App_PasswordSrc]
,[App_UserName_CN]
,[Chang_Password_Time]
,[Checked]
,[Check_User]
,[Check_Time]
,[Createtime]
,[Updatetime]
FROM [dbo].[MDM_System_User]
WHERE Env_ID='" + strEnv_ID + @"'
");
DataTable dtMDMUpdate = dtMDM.Clone();
DataTable dtMDMInsert = dtMDM.Clone();
DataTable dtMDMDelete = dtMDM.Clone();
for (int i = 0; i < dtMDM.Rows.Count; i++)
{
string strApp_UserName = dtMDM.Rows[i]["App_UserName"].ToString();
DataRow[] dr = ds.Tables[0].Select("App_UserName='******'");
if (dr.Length > 0)
{
dtMDM.Rows[i]["App_Password"] = dr[0]["App_Password"];
dtMDM.Rows[i]["App_PasswordSrc"] = dr[0]["App_PasswordSrc"];
dtMDM.Rows[i]["App_UserName_CN"] = dr[0]["App_UserName_CN"];
dtMDMUpdate.ImportRow(dtMDM.Rows[i]);
dr[0]["oprationFlag"] = "1";
}
else
{
dtMDMDelete.ImportRow(dtMDM.Rows[i]);
}
}
DataRow[] drInsert = ds.Tables[0].Select("oprationFlag=0");
for (int i = 0; i < drInsert.Length; i++)
{
DataRow dr = dtMDMInsert.NewRow();
dr["Env_ID"] = strEnv_ID;
dr["VexSSONewID"] = drInsert[i]["VexSSONewID"];
dr["App_UserName"] = drInsert[i]["App_UserName"];
dr["App_Password"] = drInsert[i]["App_Password"];
dr["App_PasswordSrc"] = drInsert[i]["App_PasswordSrc"];
dr["App_UserName_CN"] = drInsert[i]["App_UserName_CN"];
dr["Chang_Password_Time"] = drInsert[i]["Chang_Password_Time"];
dr["Checked"] = drInsert[i]["Checked"];
dr["Check_User"] = drInsert[i]["Check_User"];
dr["Check_Time"] = drInsert[i]["Check_Time"];
dr["Createtime"] = drInsert[i]["Createtime"];
dr["Updatetime"] = drInsert[i]["Updatetime"];
dtMDMInsert.Rows.Add(dr);
}
if (dtMDMDelete.Rows.Count > 0)
{
for (int i = 0; i < dtMDMDelete.Rows.Count; i++)
{
string strWhere = "App_UserID='" + dtMDMDelete.Rows[i]["App_UserID"] + "'";
connMDM.Delete("MDM_System_User", strWhere);
}
}
if (dtMDMUpdate.Rows.Count > 0)
{
ArrayList listKey = new ArrayList();
listKey.Add("App_UserID");
connMDM.Update("MDM_System_User", dtMDMUpdate, listKey);
}
if (dtMDMInsert.Rows.Count > 0)
{
dtMDMUpdate.Columns.Remove("App_UserID");
connMDM.InsertBYSQLBC("MDM_System_User", dtMDMInsert);
}
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
/// <summary>
/// 编辑供应商信息
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
Dbconn conn = new Dbconn("MDM_Master2");
try
{
DataSet ds_Return = new DataSet();
DataSet dsRequest = new DataSet();
string strXML = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
if (strXML == "")
{
throw new Exception("提交的XML为空！");
}
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = dsXML.Tables["OPTYPE"];
string strMDM_Class = "0007";
conn.LockTableList.Add("MDM_" + strMDM_Class + "_B");
conn.LockTableList.Add("MDM_" + strMDM_Class + "_E");
conn.BeginTransaction();
for (int i = 0; i < dt_OPTYPE.Rows.Count; i++)
{
DataTable dt_List = dsXML.Tables[Common.Common.GetListTableName(i)];
if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "UPDATE"
|| dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERT"
)
{
//插入BODY表
for (int j = 0; j < dt_List.Rows.Count; j++)
{
string strBody_id = "";
strBody_id = dt_List.Rows[j]["body_ID"].ToString();
if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "INSERT")
{
MDM2_EntityCreatedBodyID_Edit clsBody = new MDM2_EntityCreatedBodyID_Edit();
clsBody.Conn = conn;
clsBody.hzyMessage = hzyMessage;
clsBody.MDMClass = strMDM_Class;
clsBody.Execute();
strBody_id = clsBody.BodyID;
if (strBody_id == "")
{
throw new Exception("BodyID创建失败！");
}
}
else if (strBody_id == "")
{
throw new Exception("传入的参数有误，如果反复出现，请重新登录！");
}
for (int k = 0; k < dt_List.Columns.Count; k++)
{
string strColumnName = dt_List.Columns[k].ColumnName;
if (strColumnName.ToLower() == "rowindex" ||
strColumnName.ToLower() == "timestamps" ||
strColumnName.ToLower() == "body_id"
)
{
continue;
}
MDM2_EntityPropertyValue_Edit clsProperty = new MDM2_EntityPropertyValue_Edit();
clsProperty.Conn = conn;
clsProperty.hzyMessage = hzyMessage;
clsProperty.MDMClass = strMDM_Class;
clsProperty.ExpressionName = "CN";
clsProperty.PropertyName = strColumnName;
clsProperty.BodyID = strBody_id;
clsProperty.PropertyValue = dt_List.Rows[j][k].ToString();
if (clsProperty.Execute() == false)
{
throw new Exception("修改失败，" + clsProperty.Message);
}
}
}
}
else
{
throw new Exception("提交了错误的操作类型");
}
}
dt_EditResult.Rows.Add(new object[] { true, "操作成功" });
ds_Return.Tables.Add(dt_EditResult);
conn.CommitTransaction();
return ds_Return;
}
catch
{
conn.RollbackTransaction();
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>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM"); //创建连接类
try
{
if (m_request == null
|| m_request.Tables.Count == 0
|| m_request.Tables[0].Rows.Count == 0)
{
return true;
}
string strTASK_ID = m_request.Tables[0].Rows[0]["task_id"].ToString(); //获取参数Task_ID
string strParam_Name = m_request.Tables[0].Rows[0]["param_name"].ToString(); //获取参数Param_Name
string strParam_ID = m_request.Tables[0].Rows[0]["param_id"].ToString(); //获取参数Param_ID
ArrayList listTable = new ArrayList();
listTable.Add("WS_SchedulingParam");
conn.BeginTransaction(); //开启事务
conn.TableLock(listTable); //锁表WS_SchedulingParam
string strWhere = " 1=1 ";
if (!string.IsNullOrEmpty(strTASK_ID))
{
strWhere += " AND TASK_ID = '" + strTASK_ID + "'";
}
if (!string.IsNullOrEmpty(strParam_Name))
{
strWhere += " AND Param_Name = '" + strParam_Name + "'";
}
//创建DataTable
DataTable dt_Add = new DataTable();
dt_Add.Columns.Add("Param_ID");
dt_Add.Columns.Add("TASK_ID");
dt_Add.Columns.Add("Param_Name");
dt_Add.Columns.Add("Param_Value");
DataRow dr = dt_Add.NewRow();
for (int i = 0; i < dt_Add.Columns.Count; i++)
{
dr[i] = m_request.Tables[0].Rows[0][dt_Add.Columns[i].ColumnName].ToString(); //将m_request中的数据逐个赋值
}
dt_Add.Rows.Add(dr);
if (strParam_ID == "") //如果为空，说明是复制数据
{
//判断是否已经存在
int nRow = conn.GetDataTableRowCount("SELECT * FROM WS_SchedulingParam WHERE " + strWhere, new string[0]);
if (nRow > 0)
{
throw new Exception(strParam_Name + "已经存在，不能更新");
}
strParam_ID = Guid.NewGuid().ToString(); //创建GUID
dt_Add.Rows[0]["Param_ID"] = strParam_ID;
conn.Insert("WS_SchedulingParam", dt_Add); //插入数据
}
else
{
dt_Add.Columns.Remove("Param_ID"); //删除Param_ID，更新不需要
dt_Add.Columns.Remove("Task_ID"); //删除Task_ID，更新不需要
strWhere = " 1=1 AND Param_ID = '" + strParam_ID + "'"; //重新设置where语句
conn.Update("WS_SchedulingParam", dt_Add, strWhere); //更新数据
}
//更新主表
DataTable dtScheduling = new DataTable();
dtScheduling.Columns.Add("UpdateTime");
DataRow drScheduling = dtScheduling.NewRow();
drScheduling["UpdateTime"] = DateTime.Now;
dtScheduling.Rows.Add(drScheduling);
string strSchedulingWhere=" 1=1 AND TASK_ID = '" + strTASK_ID + "'";
conn.Update("WS_Scheduling", dtScheduling, strSchedulingWhere); //更新数据
conn.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
private void Resend()
{
string strName = Request["Name"];
string strOldPhone = Request["oldphone"];
string strNewPhone = Request["newphone"];
string strcrdate = Request["crdate"].ToString();
string strsums = Request["sums"].ToString();
string strticketid = Request["ticketid"].ToString();
string strverifycode = Request["verifycode"].ToString();
string strVipID = Request["vipid"].ToString();
string strUid = Request["uid"].ToString();
if (string.IsNullOrEmpty(strOldPhone) ||
string.IsNullOrEmpty(strcrdate) ||
string.IsNullOrEmpty(strsums) ||
string.IsNullOrEmpty(strticketid) ||
string.IsNullOrEmpty(strVipID) ||
string.IsNullOrEmpty(strUid)
)
{
Response.Write("{'Message':'非法提交'}");
return;
}
else
{
if (strNewPhone == null)
{
strNewPhone = "";
}
string strPhone = strNewPhone;
if (strPhone == "")
{
strPhone = strOldPhone;
}
string strMessage = "尊敬的" + strName;
strMessage += "贵宾,您已通过EP雅莹店铺" + strcrdate.ToString() + "成功以";
strMessage += Convert.ToDecimal(strsums) * 25;
strMessage += "分兑换面额为" + strsums + "的券，券号" + strticketid;
strMessage += "，使用即使验证码：" + strverifycode;
strMessage += "。生活之美，EP雅莹愿与您一同缔造。";
AMSendWS.MsgCenter cls = new AMSendWS.MsgCenter();
string strResult = cls.SendSubjectMsgImmediately("SMS", "ep_actualtime", "积分兑换券验证码", strPhone, "信息部服务中心", strMessage, DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:mm:ss"), "1");
if (strResult == "0")
{
Response.Write("{'Message':'发送成功'}");
}
else
{
Response.Write("{'Message':'发送失败，原因：" + strResult.Remove('\n') + "'}");
}
//写日志
DataTable dt = new DataTable();
dt.Columns.Add("Account_Id");
dt.Columns.Add("Phone_Old");
dt.Columns.Add("Phone_New");
dt.Columns.Add("Ticket_Id");
dt.Columns.Add("Create_UserId");
dt.Columns.Add("Create_Time");
DataRow dr = dt.NewRow();
dr["Account_Id"] = strVipID;
dr["Phone_Old"] = strOldPhone;
dr["Phone_New"] = strNewPhone;
dr["Ticket_Id"] = strticketid;
dr["Create_UserId"] = strUid;
dr["Create_Time"] = DateTime.Now;
dt.Rows.Add(dr);
string strConnstr = ConfigurationManager.AppSettings["ConnectionString_CRMCUS"];
Dbconn conn = new Dbconn(strConnstr, true);
conn.BeginTransaction();
conn.Insert("Log_PhoneChange", dt);
conn.CommitTransaction();
return;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("AM");
DataSet ds = conn.GetDataSet(
@"SELECT cn_id as AM_User_id,
CN_LOGIN as AM_login,
CN_USER_NAME as AM_User_Name,
CN_PASSWORD AM_Password,
CN_EMAIL AM_Email,
CN_TITLE AM_Title,
CN_HANDSET AM_Handset,
CN_ISDELETE AM_IsDelete
FROM dbo.TN_SYS_EM_USER t1 ,
dbo.TN_SYS_EM_USER_PROP t2
WHERE t1.cn_id = t2.CN_USERID
");
Dbconn connMDM = new Dbconn("MDM");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("AM_User_log");
tableList.Add("AM_User");
connMDM.TableLock(tableList);
// //存入日志数据
// connMDM.ExcuteQuerryByTran(@"INSERT INTO dbo.AM_User_log
// ( Log_datetime ,
// AM_User_id ,
// AM_LOGIN ,
// AM_USER_NAME,
// AM_PASSWORD,
// AM_EMAIL,
// AM_TITLE,
// AM_HANDSET,
// AM_ISDELETE,
// CreateTime ,
// UpdateTime ,
// Timestamps_old
//
// )
// SELECT GETDATE() ,
// AM_User_id ,
// AM_LOGIN ,
// AM_USER_NAME,
// AM_PASSWORD,
// AM_EMAIL,
// AM_TITLE,
// AM_HANDSET,
// AM_ISDELETE,
// CreateTime ,
// UpdateTime,
// CONVERT(BIGINT,Timestamps)
// FROM AM_User;");
//删除数据
connMDM.ExcuteQuerryByTran("DELETE FROM AM_User");
connMDM.InsertBYSQLBC("AM_User", ds.Tables[0]);
//更新MDM_Employees的用户信息
connMDM.ExcuteQuerryByTran(@"UPDATE MDM_Employees
SET SSO_Password=t2.AM_Password,
Leave_Flag=AM_ISDELETE
FROM dbo.AM_User t2 WHERE MDM_Employees.SSO_UserName = t2.AM_login
"
);
//并添加新入职的数据
connMDM.ExcuteQuerryByTran(@"
INSERT INTO dbo.MDM_Employees
(
Name ,
Sex ,
Phone ,
Moblie ,
SSO_UserName ,
SSO_Password ,
In_Time ,
Leave_Time ,
Leave_Flag ,
IsActived
)
SELECT
AM_login ,
NULL,
NULL,
NULL,
AM_login ,
AM_Password ,
GETDATE(),
NULL,
AM_ISDELETE,
1 FROM dbo.AM_User
WHERE AM_login NOT IN (SELECT SSO_UserName FROM MDM_Employees)
"
);
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
/// <summary>
/// 新建或修改部门记录
/// </summary>
/// <returns></returns>
public DataSet SetDeptInfo(Dbconn conn, String strType)
{
String strCode = "";
DataSet ds_Return = new DataSet();
string strActor = m_request.Tables[0].Rows[0]["actor"].ToString();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
String strOPType = dsXML.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper();
String strID = dsXML.Tables["LIST"].Rows[0]["oz1_id"].ToString();
DataTable dt_List = dsXML.Tables["LIST"];
if (strID == "")
{
throw new Exception("数据异常，缺少主键，请联系管理员！");
}
bool bResult = false;
conn.BeginTransaction();
if (strType == "NewDept") //新建部门
{
bResult = conn.Insert("[F01_CERT].[OZ1_Organization] ", dt_List);
}
else if (strType == "UpdDept") //修改部门
{
bResult = conn.Update("[F01_CERT].[OZ1_Organization] ", dt_List, "oz1_id='" + strID + "'");
}
else if (strType == "UpdDept1") //修改部门
{
//添加更新主键，按照GUID更新
ArrayList ListKey = new ArrayList();
ListKey.Add("oz1_id");
bResult = conn.Update("F01_CERT.OZ1_Organization", dt_List, ListKey);
}
else if (strType == "DelDept") //删除部门
{
for (int k = 0; k < dt_List.Rows.Count; k++)
{
string strID1 = dt_List.Rows[k]["oz1_id"].ToString();
bResult = conn.Delete("F01_CERT.OZ1_Organization", "oz1_id=" + "'" + strID1 + "'");
}
}
conn.CommitTransaction();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, strCode });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "保存失败！" });
}
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
/// <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;
}
}
