/// <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> /// 获取系统环境信息 /// </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 = 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; } }
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> /// 根据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 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 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; } }
/// <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; }
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 strSQL = @" SELECT [MenuID] ,[MenuName] ,[MenuFatherID] ,[ActionURL] FROM [WSRR_Manage].[F01_CERT].[Menu] "; ds_Return = conn.GetDataSet(strSQL); //ds_Return.Tables[0].Rows.Add(new object[] { "0", "00_根目录", "-1", "-1" }); } //更新 else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "EDIT") { string strMenuID = dt_LIST.Rows[0]["MenuID"].ToString(); bool bResult = false; conn.LockTableList.Add("[WSRR_Manage].[F01_CERT].[Menu]");//锁定表 conn.BeginTransaction();//开始事务 dt_LIST.Columns.Remove("MenuID");//移除属性,标识符列(ID)不能更新 bResult = conn.Update("[WSRR_Manage].[F01_CERT].[Menu]", dt_LIST, "MenuID=" + strMenuID);//update语句 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[i][0].ToString().ToUpper() == "INSERT") { string strMenuID = dt_LIST.Rows[0]["MenuID"].ToString(); bool bResult = false; conn.LockTableList.Add("[WSRR_Manage].[F01_CERT].[Menu]");//锁定表 conn.BeginTransaction();//开始事务 dt_LIST.Columns.Remove("MenuID");//移除属性,标识符列(ID)不能更新 bResult = conn.Insert("[WSRR_Manage].[F01_CERT].[Menu]", dt_LIST);//update语句 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[i][0].ToString().ToUpper() == "DELETE") { string strMenuID = dt_LIST.Rows[0]["MenuID"].ToString(); bool bResult = false; conn.LockTableList.Add("[WSRR_Manage].[F01_CERT].[Menu]");//锁定表 conn.BeginTransaction();//开始事务 dt_LIST.Columns.Remove("MenuID");//移除属性,标识符列(ID)不能更新 bResult = conn.Delete("[WSRR_Manage].[F01_CERT].[Menu]" , "MenuID=" + strMenuID);//update语句 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> /// 根据XML内容更新服务器申请单 /// </summary> /// <returns></returns> public DataSet GetData() { try { DataSet ds_Return = new DataSet(); DataSet ds = new DataSet(); DataSet dsRequest = new DataSet(); Dbconn conn = new Dbconn("HZY_ExcelAppStore"); string strXML = ""; string strSQL = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); ds = Common.Common.GetDSByExcelXML(strXML); if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET") { string strWHERE = " Deleted=0 "; if (ds.Tables["LIST"].Columns.Contains("Condition")) { if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "") { strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")"; } } strSQL = @" SELECT * FROM ExcelAppStore WITH ( NOLOCK ) WHERE " + strWHERE; ds_Return = conn.GetDataSet(strSQL); } else { ArrayList tableList = new ArrayList(); tableList.Add("ExcelAppStore"); 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 ExcelAppStore SET Deleted=1 WHERE AppName='" + ds.Tables["LIST"].Rows[i]["AppName"].ToString() + @"' AND Deleted=0 "; 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 ExcelAppStore WHERE AppName=@param0 AND Deleted=0 "; DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["AppName"].ToString() }); //update if (dt_SR.Rows.Count > 0) { //运维操作 if (ds.Tables["LIST"].Rows[i]["AppName"].ToString() != "") { conn.Update("ExcelAppStore", dt, "AppName='" + ds.Tables["LIST"].Rows[i]["AppName"].ToString() + @"'"); } } else //add { conn.Insert("ExcelAppStore", dt); } } } conn.CommitTransaction(); ds_Return = Common.Common.GetRequestDataSet(new string[]{"Result"},new string[]{"true"}); } catch { conn.RollbackTransaction(); throw; } } return ds_Return; } catch { throw; } }
public bool CheckUser(string SessionID) { if (SessionID == null || SessionID == "") { throw new Exception("身份验证错误"); } Dbconn conn = new Dbconn("VexSSO"); string strSQL = @" SELECT [SSO_Guid] ,[Company_sn] ,[Company_ID] ,[Env_sn] ,[Env_ID] ,[Request_IP] ,[Request_ComputerName] ,[Login_Env_sn] ,[Login_Env_ID] ,[VexSSONewID] ,[User_ID] ,[User_Name] FROM [dbo].[SSO_Login] with(nolock) where [SSO_Guid]=@param0 and Logout_Time is null "; DataTable dt = conn.GetDataTable(strSQL, new string[] { SessionID }); if (dt.Rows.Count == 1) { hzyMessage.Session_ID = dt.Rows[0]["SSO_Guid"].ToString(); //hzyMessage.Company_SN = dt.Rows[0]["Company_sn"].ToString(); //hzyMessage.Company_ID = dt.Rows[0]["Company_ID"].ToString(); hzyMessage.Env_SN = dt.Rows[0]["Env_sn"].ToString(); hzyMessage.Env_ID = dt.Rows[0]["Env_ID"].ToString(); hzyMessage.LoginEnv_SN = dt.Rows[0]["Login_Env_sn"].ToString(); hzyMessage.LoginEnv_ID = dt.Rows[0]["Login_Env_ID"].ToString(); hzyMessage.VexSSONewID = dt.Rows[0]["VexSSONewID"].ToString(); hzyMessage.User_ID = dt.Rows[0]["User_ID"].ToString(); hzyMessage.User_Name = dt.Rows[0]["User_Name"].ToString(); //需要更新用户的最后访问日期 DataTable dt_UpdateLastRequestTime = new System.Data.DataTable(); dt_UpdateLastRequestTime.Columns.Add("Last_Request_Time"); dt_UpdateLastRequestTime.Rows.Add(new object[] { DateTime.Now }); conn.BeginTransaction(); conn.Update("SSO_Login", dt_UpdateLastRequestTime, "SSO_Guid='" + hzyMessage.Session_ID + "'"); conn.CommitTransaction(); return true; } else { throw new Exception("身份验证错误"); } }
/// <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(FNP_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 [FNP_CONO] ,[FNP_DIVI] ,[FNP_PayApplyId] ,[FNP_PayApplyCode] ,[FNP_PR0_ContractId] ,[FNP_SU0_SupplierId] ,[FNP_Type] ,[FNP_BankReceipt] ,[FNP_Status] ,[FNP_RgUser] ,[FNP_RgDt] ,[FNP_LmUser] ,[FNP_LmDt] ,[FNP_UptNo] ,A.PR0_ContractCode ,B.SU0_SupplierCode ,C.DC0_DICTNAME1 AS TypeDictName ,D.WFT_NODE AS WFT_NODE1 ,E.WFT_NODE AS WFT_NODE2 ,F.WFT_NODE AS WFT_NODE3 ,G.WFT_NODE AS WFT_NODE4 ,N.DC0_DICTNAME1 AS DC0_DICTNAME1 ,O.DC0_DICTNAME1 AS DC0_DICTNAME2 ,P.DC0_DICTNAME1 AS DC0_DICTNAME3 ,Q.DC0_DICTNAME1 AS DC0_DICTNAME4 ,I.WFP_AuditTime AS WFP_AuditTime1 ,J.WFP_AuditTime AS WFP_AuditTime2 ,K.WFP_AuditTime AS WFP_AuditTime3 ,L.WFP_AuditTime AS WFP_AuditTime4 ,R.[DC0_DictName1] AS StatusDictName FROM B02_Bill.FNP_PayApply LEFT JOIN B02_Bill.PR0_Contract A ON FNP_PR0_ContractId=A.PR0_ContractId LEFT JOIN B01_MDM.SU0_Supplier B ON FNP_SU0_SupplierId=B.SU0_SupplierId LEFT JOIN M01_CONF.DC0_Dictionary C ON FNP_Type=C.DC0_DictId LEFT JOIN M02_BPM.WFT_AuditFlowDefine D ON D.WFT_Type='PayApply' and D.WFT_Step='1' LEFT JOIN M02_BPM.WFT_AuditFlowDefine E ON E.WFT_Type='PayApply' and E.WFT_Step='2' LEFT JOIN M02_BPM.WFT_AuditFlowDefine F ON F.WFT_Type='PayApply' and F.WFT_Step='3' LEFT JOIN M02_BPM.WFT_AuditFlowDefine G ON G.WFT_Type='PayApply' and G.WFT_Step='4' LEFT JOIN M02_BPM.WFP_AuditFlow I ON FNP_PayApplyId=I.WFP_BillId AND I.WFP_WFT_AFDId=D.WFT_AFDId LEFT JOIN M02_BPM.WFP_AuditFlow J ON FNP_PayApplyId=J.WFP_BillId AND J.WFP_WFT_AFDId=E.WFT_AFDId LEFT JOIN M02_BPM.WFP_AuditFlow K ON FNP_PayApplyId=K.WFP_BillId AND K.WFP_WFT_AFDId=F.WFT_AFDId LEFT JOIN M02_BPM.WFP_AuditFlow L ON FNP_PayApplyId=L.WFP_BillId AND L.WFP_WFT_AFDId=G.WFT_AFDId 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 L.WFP_AFResultId=Q.DC0_DictId LEFT JOIN M01_CONF.DC0_Dictionary R ON FNP_Status=R.DC0_DictId WHERE " + strWHERE; ds_Return = conn.GetDataSet(strSQL); } else { ArrayList tableList = new ArrayList(); tableList.Add("B02_Bill.FNP_PayApply"); 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.FNP_PayApply SET FNP_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1' WHERE FNP_PayApplyId='" + ds.Tables["LIST"].Rows[i]["FNP_PayApplyId"].ToString() + @"' AND ISNULL(FNP_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.FNP_PayApply WHERE FNP_PayApplyId=@param0 AND ISNULL(FNP_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' "; DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["FNP_PayApplyId"].ToString() }); //update if (dt_SR.Rows.Count > 0) { //处理结果 strSQL = @"SELECT * FROM M02_BPM.WFP_AuditFlow WHERE WFP_BillId=@param0 AND WFP_AFResultId<>'' AND ISNULL(WFP_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' "; DataTable dt_PA = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["FNP_PayApplyId"].ToString() }); //update if (dt_PA.Rows.Count > 0) { throw new Exception("付款申请单已审核,不能进行修改!"); } else if (ds.Tables["LIST"].Rows[i]["FNP_LmUser"].ToString() != "") { string strCode = dt_SR.Rows[0]["FNP_UptNo"].ToString(); if (strCode == "0") { strCode = Convert.ToInt32(strCode) + 1.ToString(); } else { strCode = "0"; } if (!dt.Columns.Contains("FNP_LmDt")) { dt.Columns.Add("FNP_LmDt"); } // if (!dt.Columns.Contains("FNP_Status")) // { // dt.Columns.Add("FNP_Status"); // } if (!dt.Columns.Contains("FNP_UptNo")) { dt.Columns.Add("FNP_UptNo"); } // dt.Rows[0]["FNP_Status"] = '0'; dt.Rows[0]["FNP_LmDt"] = DateTime.Now; dt.Rows[0]["FNP_UptNo"] = strCode; } conn.Update("B02_Bill.FNP_PayApply", dt, "FNP_PayApplyId='" + ds.Tables["LIST"].Rows[i]["FNP_PayApplyId"].ToString() + @"'"); } else //add { string FNP_PayApplyId = Guid.NewGuid().ToString(); if (!dt.Columns.Contains("FNP_PayApplyId")) { dt.Columns.Add("FNP_PayApplyId"); } // if (!dt.Columns.Contains("FNP_Status")) // { // dt.Columns.Add("FNP_Status"); // } if (!dt.Columns.Contains("FNP_RgDt")) { dt.Columns.Add("FNP_RgDt"); } if (!dt.Columns.Contains("FNP_LmDt")) { dt.Columns.Add("FNP_LmDt"); } if (!dt.Columns.Contains("FNP_UptNo")) { dt.Columns.Add("FNP_UptNo"); } dt.Rows[0]["FNP_PayApplyId"] = FNP_PayApplyId; // dt.Rows[0]["FNP_Status"] = '0'; dt.Rows[0]["FNP_RgDt"] = DateTime.Now; dt.Rows[0]["FNP_LmDt"] = DateTime.Now; dt.Rows[0]["FNP_UptNo"] = '0'; conn.Insert("B02_Bill.FNP_PayApply", dt); GL_ERP_Common.CreateWorkFlow(conn, "PayApply", ds.Tables["LIST"].Rows[i]["FNP_CONO"].ToString(), ds.Tables["LIST"].Rows[i]["FNP_DIVI"].ToString(), FNP_PayApplyId, "PayApply", "", "", ds.Tables["LIST"].Rows[i]["FNP_Status"].ToString(), ds.Tables["LIST"].Rows[i]["FNP_RgUser"].ToString(), ds.Tables["LIST"].Rows[i]["FNP_LmUser"].ToString(), "", "FNP_PayApplyId", "B02_Bill.FNP_PayApply"); } } } conn.CommitTransaction(); ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" }); } catch { conn.RollbackTransaction(); //事物结束 throw; } } return ds_Return; } catch { throw; } }
private void UpdateCMData(Dbconn conn, DataTable dt_CMData) { //更新旧数据 if (m_OldAutoId != "") { DataTable dt = new DataTable(); dt.Columns.Add("IsHistory"); dt.Rows.Add(new object[] { "1" }); conn.Update("CM_Depot_Opration_History", dt, "autoid =" + m_OldAutoId); } //插入新数据 conn.Insert("CM_Depot_Opration_History", dt_CMData); }
/// <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 = "1=1"; if (ds.Tables["LIST"].Columns.Contains("Condition")) { if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "") //读取LIST之间参数 { strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")"; } } strSQL = @" SELECT [WFT_CONO] ,[WFT_DIVI] ,[WFT_AFDId] ,[WFT_Type] ,[WFT_Node] ,[WFT_Person] ,[WFT_Step] ,[WFT_IsMust] ,[WFT_RgUser] ,[WFT_RgDt] ,[WFT_LmUser] ,[WFT_LmDt] ,[WFT_UptNo] FROM GL_ERP.M02_BPM.WFT_AuditFlowDefine WITH ( NOLOCK ) WHERE " + strWHERE; ds_Return = conn.GetDataSet(strSQL); } else { ArrayList tableList = new ArrayList(); tableList.Add("GL_ERP.M02_BPM.WFT_AuditFlowDefine"); 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 = @"DELETE FROM GL_ERP.M02_BPM.WFT_AuditFlowDefine WHERE WFT_AFDId='" + ds.Tables["LIST"].Rows[i]["WFT_AFDId"].ToString() + @"' "; 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 GL_ERP.M02_BPM.WFT_AuditFlowDefine WHERE WFT_AFDId=@param0 "; DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["WFT_AFDId"].ToString() }); //update if (dt_SR.Rows.Count > 0) { //处理结果 if (ds.Tables["LIST"].Rows[i]["WFT_LmUser"].ToString() != "") { string strCode = dt_SR.Rows[0]["WFT_UptNo"].ToString(); if (strCode == "0") { strCode = Convert.ToInt32(strCode) + 1.ToString(); } else { strCode = "0"; } if (!dt.Columns.Contains("WFT_LmDt")) { dt.Columns.Add("WFT_LmDt"); } if (!dt.Columns.Contains("WFT_UptNo")) { dt.Columns.Add("WFT_UptNo"); } dt.Rows[0]["WFT_LmDt"] = DateTime.Now; dt.Rows[0]["WFT_UptNo"] = strCode; } conn.Update("GL_ERP.M02_BPM.WFT_AuditFlowDefine", dt, "WFT_AFDId='" + ds.Tables["LIST"].Rows[i]["WFT_AFDId"].ToString() + @"'"); } else //add { string WFT_AFDId = Guid.NewGuid().ToString(); if (!dt.Columns.Contains("WFT_AFDId")) { dt.Columns.Add("WFT_AFDId"); } if (!dt.Columns.Contains("WFT_RgDt")) { dt.Columns.Add("WFT_RgDt"); } if (!dt.Columns.Contains("WFT_LmDt")) { dt.Columns.Add("WFT_LmDt"); } if (!dt.Columns.Contains("WFT_UptNo")) { dt.Columns.Add("WFT_UptNo"); } dt.Rows[0]["WFT_AFDId"] = WFT_AFDId; dt.Rows[0]["WFT_RgDt"] = DateTime.Now; dt.Rows[0]["WFT_LmDt"] = DateTime.Now; dt.Rows[0]["WFT_UptNo"] = '0'; conn.Insert("GL_ERP.M02_BPM.WFT_AuditFlowDefine", 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="pSessionID"></param> /// <returns></returns> private bool DoLogout(string pSessionID) { bool returnValue = false; Dbconn conn = new Dbconn("VexSSO"); DataTable dt = new System.Data.DataTable(); dt.Columns.Add("Logout_Time"); dt.Rows.Add(new object[] { DateTime.Now }); conn.BeginTransaction(); ArrayList tableList = new ArrayList(); tableList.Add("SSO_Login"); conn.TableLock(tableList); returnValue = conn.Update("SSO_Login", dt, "SSO_Guid='" + pSessionID + "'"); conn.CommitTransaction(); hzyMessage.ClearUserInfo(); hzyMessage.Message = "登出成功"; return returnValue; }
/// <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; }
/// <summary> /// 编辑任务信息 /// </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 strTASK_ID = m_request.Tables[0].Rows[0]["task_id"].ToString(); //获取参数TaskID string strWS_Name = m_request.Tables[0].Rows[0]["ws_name"].ToString(); //获取参数WS名称 ArrayList listTable = new ArrayList(); listTable.Add("WS_Scheduling"); conn.BeginTransaction(); //开启事务 conn.TableLock(listTable); //锁表WS_Scheduling string strWhere = string.Empty; //创建DataTable,用于传参 DataTable dt_Add = new DataTable(); dt_Add.Columns.Add("TASK_ID"); dt_Add.Columns.Add("WS_ID"); dt_Add.Columns.Add("URL"); dt_Add.Columns.Add("WS_Name"); dt_Add.Columns.Add("WS_LogicName"); dt_Add.Columns.Add("Execute_Type"); dt_Add.Columns.Add("Execute_StartTime"); dt_Add.Columns.Add("Execute_EndTime"); dt_Add.Columns.Add("Execute_Cycle"); dt_Add.Columns.Add("Execute_DayStartTime"); dt_Add.Columns.Add("Execute_DayEndTime"); dt_Add.Columns.Add("Execute_WeekTime"); dt_Add.Columns.Add("Execute_MonthTime"); dt_Add.Columns.Add("Enabled"); dt_Add.Columns.Add("Error_Count"); dt_Add.Columns.Add("Remind_ErrorCount"); dt_Add.Columns.Add("Remind_UserName"); dt_Add.Columns.Add("CreateUser"); dt_Add.Columns.Add("CreateTime"); dt_Add.Columns.Add("UpdateUser"); dt_Add.Columns.Add("UpdateTime"); DataRow dr = dt_Add.NewRow(); //循环DataTabel的列 for (int i = 0; i < dt_Add.Columns.Count-4 ; i++) { if (dt_Add.Columns[i].ColumnName == "TASK_ID") { continue; } else { dr[i] = m_request.Tables[0].Rows[0][dt_Add.Columns[i].ColumnName].ToString(); //对DataTabel中的每列设置值 } } dt_Add.Rows.Add(dr); if (strTASK_ID == "") //如果为空,说明是复制数据 { strTASK_ID = Guid.NewGuid().ToString(); //生成GUID dt_Add.Rows[0]["TASK_ID"] = strTASK_ID; dt_Add.Rows[0]["CreateTime"] = DateTime.Now; //设置创建时间 dt_Add.Rows[0]["CreateUser"] = hzyMessage.User_Name; //设置创建者 dt_Add.Rows[0]["UpdateTime"] = DateTime.Now; //设置更新时间 dt_Add.Rows[0]["UpdateUser"] = hzyMessage.User_Name; //设置更新者 dt_Add.Columns.Add("IsDeleted"); dt_Add.Rows[0]["IsDeleted"] = false; //将删除标志设为“False” conn.Insert("WS_Scheduling", dt_Add); //插入数据 } else { //更新 dt_Add.Columns.Remove("CreateUser"); //删除CreateUser列,更新不需要 dt_Add.Columns.Remove("CreateTime"); //删除CreateTime列,更新不需要 dt_Add.Columns.Remove("TASK_ID"); dt_Add.Rows[0]["UpdateTime"] = DateTime.Now; //将更新时间设为当前时间 dt_Add.Rows[0]["UpdateUser"] = hzyMessage.User_Name; //设置更新者 strWhere = " TASK_ID = '" + strTASK_ID + "'"; //设置更新条件 conn.Update("WS_Scheduling", dt_Add, strWhere); //更新数据 } conn.CommitTransaction(); //提交事务 return true; } catch { conn.RollbackTransaction(); //回滚 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; } }
/// <summary> /// 修改审批记录 /// </summary> /// <returns></returns> public DataSet SetApproveRows(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 = ""; //String strFatherID = dsXML.Tables["LIST"].Rows[0]["ct1_fatherid"].ToString(); DataTable dt_List = dsXML.Tables["LIST"]; if (strType == "SetApH" || strType == "NewApH") { strID = dsXML.Tables["LIST"].Rows[0]["bg4_id"].ToString(); } else if (strType == "SetApAH" || strType == "NewApAH") { strID = dsXML.Tables["LIST"].Rows[0]["bg7_id"].ToString(); } if (strID == "") { throw new Exception("数据异常,缺少主键,请联系管理员!"); } bool bResult = false; if (strType == "NewApH") //新建 { conn.BeginTransaction(); bResult = conn.Insert("B01_MDM.BG4_EBudgetApprove", dt_List); conn.CommitTransaction(); } else if (strType == "SetApH") //修改 { conn.BeginTransaction(); bResult = conn.Update("B01_MDM.BG4_EBudgetApprove", dt_List, "bg4_id='" + strID + "'"); conn.CommitTransaction(); } if (strType == "NewApAH") //新建 追加 { conn.BeginTransaction(); bResult = conn.Insert("B01_MDM.BG7_EBudgetApproveAdd", dt_List); conn.CommitTransaction(); } else if (strType == "SetApAH") //修改 追加 { conn.BeginTransaction(); bResult = conn.Update("B01_MDM.BG7_EBudgetApproveAdd", dt_List, "bg7_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> /// 利润中心损益表配置 配置-修改-查询 /// </summary> /// <returns></returns> public DataSet ISRows(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); DataSet ds = new DataSet(); String strSQL = ""; if (strType == "UpdISRows") { 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"); } if (dtOptypes.Rows[i][0].ToString().ToUpper() == "UPDATE") { DataTable dt_List2 = dsXML.Tables[i + 1]; //添加更新主键,按照GUID更新 ArrayList ListKey = new ArrayList(); ListKey.Add("bg6_id"); bResult = conn.Update("B01_MDM.BG6_IncomeStatement", dt_List2, ListKey); } } 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; } else if (strType == "ISRows") { //获取条件值 string strPage = dsXML.Tables["LIST"].Rows[0]["Page"].ToString(); string strNum = dsXML.Tables["LIST"].Rows[0]["Num"].ToString(); string strCons = dsXML.Tables["LIST"].Rows[0]["Cons"].ToString(); string strOrderBy = dsXML.Tables["LIST"].Rows[0]["OrderBy"].ToString(); string[] strParam = new string[] { }; strSQL = @" SELECT * FROM B01_MDM.BG6_IncomeStatement WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); return ds; } else if (strType == "ISRowsHZ") { //获取条件值 string strCONO = dsXML.Tables["LIST"].Rows[0]["CONO"].ToString(); string strDIVI = dsXML.Tables["LIST"].Rows[0]["DIVI"].ToString(); string strType1 = dsXML.Tables["LIST"].Rows[0]["Type"].ToString(); string strYear = dsXML.Tables["LIST"].Rows[0]["Year"].ToString(); string strBrand = dsXML.Tables["LIST"].Rows[0]["Brand"].ToString(); string strCostCenter = dsXML.Tables["LIST"].Rows[0]["CostCenter"].ToString(); string strShopType = dsXML.Tables["LIST"].Rows[0]["ShopType"].ToString(); string strDLCode = dsXML.Tables["LIST"].Rows[0]["DLCode"].ToString(); string strXLCode = dsXML.Tables["LIST"].Rows[0]["XLCode"].ToString(); string strCom = dsXML.Tables["LIST"].Rows[0]["Com"].ToString(); string strDept = dsXML.Tables["LIST"].Rows[0]["Dept"].ToString(); string strProject = dsXML.Tables["LIST"].Rows[0]["Project"].ToString(); string strState = dsXML.Tables["LIST"].Rows[0]["State"].ToString(); String strXMLC = "【操作类型,GET】【实体, 利润中心损益】" + "【CONO," + strCONO + "】【DIVI," + strDIVI + "】" + "【《W》汇总方式, " + strType1 + "】" + "【《W》年份," + strYear + "】 【《W》品牌," + strBrand + "】" + "【《W》成本中心," + strCostCenter + "】【《W》店铺类型," + strShopType + "】" + "【《W》大类编码," + strDLCode + "】【《W》小类编码," + strXLCode + "】" + "【《W》公司, " + strCom + "】" + "【《W》部门," + strDept + "】【《W》项目名称," + strProject + "】" + "【《W》状态," + strState + "】" + "【条件内容,】" + "【返回内容,】"; ds = conn.GetDataSetBySP("[API_BM].[IncomeStatement]", new string[4] { "@InputFormat", "@OutputFormat" , "@OutputContentType", "@ConditionStr" } , new string[4] { "AML", "DB", "RS", strXMLC }); return ds; } return ds; }
/// <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> /// 根据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; } }
/// <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> /// 根据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> /// 根据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; } }
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; } }
/// <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> /// 根据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; } }