/// <summary> /// 获取执行任务参数信息 /// </summary> /// <returns></returns> public DataSet GetData() { try { string strXML = string.Empty; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); //获取参数XML DataSet dsRequest = Common.Common.GetDSByExcelXML(strXML); //转换XML到DataSet string strTaskID = dsRequest.Tables[1].Rows[0]["task_id"].ToString(); //获取参数TaskID if (string.IsNullOrEmpty(m_hzyPageInfo.Sort)) { m_hzyPageInfo.Sort = "CreateTime"; m_hzyPageInfo.Order = "desc "; } Dbconn conn = new Dbconn("WSRR"); //创建连接类 string strSQL = @"SELECT [Auto_ID] ,[TASK_ID] ,[Execute_StartTime] ,[Execute_EndTime] ,[Execute_Result] ,[Execute_Message] ,[CreateTime] FROM [dbo].[WS_Scheduling_Execute_log] WHERE TASK_ID='" + strTaskID + "' "; DataSet ds = conn.GetDataSetForPageList(strSQL, new string[0], m_hzyPageInfo); //调用方法,获取分页数据 return ds; } catch { throw; } }
/// <summary> /// 获取申购单主/从数据集 可条件-分页-排序 /// </summary> /// <returns></returns> public DataSet GetPOApplyList(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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[] { }; if (strType == "GetHs") { strSQL = @" SELECT A.* FROM B02_BILL.LV1_POApplyHead A LEFT JOIN F01_CERT.OZ1_Organization B ON A.LV1_DeptID = B.OZ1_OrgCode WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetDs") { strSQL = @" SELECT * FROM B02_BILL.LV1_POApplyDetail WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } return ds; }
/// <summary> /// 获取符合条件的申购单信息 /// </summary> /// <param name="conn"></param> /// <param name="strType"></param> /// <returns></returns> public DataSet GetPOApplyDetails(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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 B02_BILL.V_LV1_POApplyDetail WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); return ds; }
/// <summary> /// 根据条件获取鉴定单数据集 条件-分页-排序等 /// </summary> /// <returns></returns> public DataSet GetITAppraisalList(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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 FA3_ID, FA3_CONO, FA3_DIVI, FA3_State, FA3_Department, FA3_Name, FA3_DeviceNo, FA3_AssetsNo, CONVERT(VARCHAR(10),FA3_FillDate,120) FA3_FillDate, FA3_FaultPhenomenon, FA3_DiagnosisResults, FA3_Solution, FA3_DiagnosisDept, FA3_DiagnosisPeople, FA3_Audit, CONVERT(VARCHAR(10),FA3_AuditDate,120) FA3_AuditDate, FA3_BrandModel1, FA3_No1, FA3_Num1, FA3_Remarks1, FA3_BrandModel2, FA3_No2, FA3_Num2, FA3_Remarks2, FA3_BrandModel3, FA3_No3, FA3_Num3, FA3_Remarks3, CONVERT(VARCHAR(20),FA3_RgDt,120) FA3_RgDt, FA3_RgUser, CONVERT(VARCHAR(20),FA3_LmDt,120) FA3_LmDt, FA3_LmUser, FA3_UptNo, FA3_RefID FROM B02_BILL.FA3_ITAppraisal WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); return ds; }
/// <summary> /// 获取Ehr用户信息 /// </summary> private void GetPosPOSEmployee() { Dbconn conn = new Dbconn("F22GP"); string strSQL = @" SELECT employeeid,names,valid FROM j_employee t1 , j_depot t2 WHERE t1.depotid = t2.depotid " + m_hzyPageInfo.Where; m_dsRequturn = conn.GetDataSetForPageList(strSQL, new string[] { }, m_hzyPageInfo); }
/// <summary> /// 根据条件获取报销单主表数据集 条件-分页-排序等 /// </summary> /// <returns></returns> public DataSet GetBillList(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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[] { }; if (strType == "GetH" ) { strSQL = @" SELECT EA1_ID, EA1_CONO, EA1_DIVI, EA1_BillCode, EA1_State, EA1_ApplyUser, CONVERT(VARCHAR(10),EA1_ApplyDt,120) EA1_ApplyDt, EA1_DeptName, EA1_CenterCode, EA1_EmployeeId, EA1_EmployeeName, EA1_Superior, EA1_Tel, EA1_ProjectCode, EA1_TAmount, EA1_BankName, EA1_BankAccount, EA1_Comment FROM B02_BILL.EA1_CashAppHead WHERE 1=1 " + strCons; } else if (strType == "GetD") { strSQL = @" SELECT EA2_ID, EA2_CONO, EA2_DIVI, EA2_BillCode, CONVERT(VARCHAR(10),EA2_UseDt,120) EA2_UseDt, EA2_Comment, EA2_Amount FROM B02_BILL.EA1_CashAppDetail WHERE 1=1 " + strCons; } ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); return ds; }
/// <summary> /// 根据条件获取店铺销售预算 条件-分页-排序 求和等 /// </summary> /// <returns></returns> public DataSet GetSSBRows(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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[] { }; if (strType == "GetShopSBHs") { strSQL = @" SELECT * FROM B01_MDM.BG5_ShopSaleBudgets WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetShopSBHs1") { //不同标签页 string strXX = dsXML.Tables["LIST"].Rows[0]["XXStr"].ToString(); strSQL = @" SELECT BG5_ID, BG5_CONO, BG5_DIVI, BG5_Year, BG5_Brand, BG5_Com, BG5_Branch, BG5_ShopType, BG5_ShopType1, BG5_ShopType2, BG5_CenterCode, BG5_CenterName, BG5_DeptID, BG5_DeptName, BG5_City, BG5_State, BG5_RgDt, BG5_RgUser, BG5_LmDt, BG5_LmUser, BG5_UptNo "; if (strXX != "") { for (int i = 1; i < 13; i++) { strSQL = strSQL + @", BG5_" + strXX + "M"+i.ToString(); } } strSQL = strSQL + @" FROM B01_MDM.BG5_ShopSaleBudgets WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetShopSBSums") { strSQL = @" SELECT SUM(BG3_Total) AS sTotal , COUNT(bg3_id) AS totalCount FROM B01_MDM.BG5_ShopSaleBudgets WHERE 1=1 " + strCons; ds = conn.GetDataSet(strSQL); } return ds; }
/// <summary> /// 根据条件-分页-排序 /// </summary> /// <returns></returns> public DataSet GetBaseDate13(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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[] { }; if (strType == "gbd13") { strSQL = @" SELECT * FROM M01_CONF.CT0_CSYTAB WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } return ds; }
/// <summary> /// 根据条件获取数据集 条件-分页-排序 求和等 /// </summary> /// <returns></returns> public DataSet GetRows(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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[] {}; if (strType == "GetHs" ) { strSQL = @" SELECT * FROM B01_MDM.BG3_ExpenseBudget WHERE 1=1 " + strCons ; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetSums") { strSQL = @" SELECT SUM(BG3_Total) AS sTotal , COUNT(bg3_id) AS totalCount FROM B01_MDM.BG3_ExpenseBudget WHERE 1=1 " + strCons; ds = conn.GetDataSet(strSQL); } else if (strType == "GetHAs") //(追加) { strSQL = @" SELECT * FROM B01_MDM.BG8_ExpenseBudgetAdd WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetASums")//(追加) { strSQL = @" SELECT SUM(BG8_Total) AS sTotal , COUNT(bg8_id) AS totalCount FROM B01_MDM.BG8_ExpenseBudgetAdd WHERE 1=1 " + strCons; ds = conn.GetDataSet(strSQL); } else if (strType == "GetHAAs") //(追加明细带主题等) { strSQL = @" SELECT * FROM B01_MDM.V_BM_BudgetDeptZQueryAdd WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetHAAlls") //(全部含追加的明细带主题等) { strSQL = @" SELECT * FROM B01_MDM.V_BM_BudgetDeptZQueryAll WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } return ds; }
/// <summary> /// 根据条件获取获取审批记录 /// </summary> /// <returns></returns> public DataSet GetApproveRows(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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[] { }; if (strType == "GetApH") //(编制) { strSQL = @" SELECT * FROM B01_MDM.BG4_EBudgetApprove WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetApAH") //(追加) { strSQL = @" SELECT * FROM B01_MDM.BG7_EBudgetApproveAdd WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetApAAH") //(编制+追加) { strSQL = @" SELECT NEWID() AS BG4_id, BG4_Year, BG4_Brand, BG4_DeptID, BG4_DeptName, SUM(BG4_Total) BG4_Total FROM (SELECT BG4_Year, BG4_State, BG4_Brand, BG4_DeptID, BG4_DeptName, BG4_Type, BG4_Total FROM B01_MDM.BG4_EBudgetApprove UNION ALL SELECT BG7_Year, BG7_State, BG7_Brand, BG7_DeptID, BG7_DeptName, BG7_Type, BG7_Total FROM B01_MDM.BG7_EBudgetApproveAdd) T WHERE 1=1 " + strCons + " GROUP BY BG4_Year, BG4_Brand, BG4_DeptID, BG4_DeptName "; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } return ds; }
/// <summary> /// 编辑渠道店铺装修历史记录 /// </summary> /// <returns></returns> public DataSet GetData() { Dbconn conn = new Dbconn("MDM"); Dbconn conn_MDM = new Dbconn("MDM_Master2"); DataSet ds_Return = 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"]; 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" ) { conn.LockTableList.Add("CM_Depot_Opration_History"); conn.BeginTransaction(); conn_MDM.BeginTransaction(); try { for (int j = 0; j < dt_List.Rows.Count; j++) { string strBody_id = dt_List.Rows[j]["body_id"].ToString(); string str合同面积 = dt_List.Rows[j]["合同面积"].ToString(); string str楼层 = dt_List.Rows[j]["楼层"].ToString(); string str地址 = dt_List.Rows[j]["地址"].ToString(); string str商务状态 = dt_List.Rows[j]["商务状态"].ToString(); string str拓展类型 = dt_List.Rows[j]["拓展类型"].ToString(); if (strBody_id == "" || str商务状态 == "" || str拓展类型 == "") { throw new Exception("Body_id,商务状态,拓展类型不能为空"); } //获取MDM当前的数据 DataTable dt_ResposeGet = this.GetMDMData(strBody_id); //设置CM的数据 DataSet ds_CM = SetCMData(strBody_id, conn, dt_ResposeGet, dt_List, j); //更新CM UpdateCMData(conn, ds_CM.Tables[0]); //更新MDM UpdateMDMData(conn_MDM, dt_List, j); } dt_EditResult.Rows.Add(new object[] { true, "操作成功" }); ds_Return.Tables.Add(dt_EditResult); conn.CommitTransaction(); conn_MDM.CommitTransaction(); } catch { conn.RollbackTransaction(); conn_MDM.RollbackTransaction(); throw; } } else if (dt_OPTYPE.Rows[i][0].ToString().ToUpper() == "GET" ) { int nPage = 1; int nPageRowCount = 20; string strOrder = " desc "; string strSort = " J_DEPOTID "; string strWhere = " 1=1 "; string strSearchFilter = " "; if (m_request != null && m_request.Tables[0].Columns.Contains("page")) { nPage = Convert.ToInt32(m_request.Tables[0].Rows[0]["page"]); nPageRowCount = Convert.ToInt32(m_request.Tables[0].Rows[0]["pagerows"]); strOrder = m_request.Tables[0].Rows[0]["order"].ToString() != "" ? m_request.Tables[0].Rows[0]["order"].ToString() : strOrder; strSort = m_request.Tables[0].Rows[0]["sort"].ToString() != "" ? m_request.Tables[0].Rows[0]["sort"].ToString() : strSort; strWhere = m_request.Tables[0].Rows[0]["where"].ToString() != "" ? m_request.Tables[0].Rows[0]["where"].ToString() : strWhere; if (m_request.Tables[0].Columns.Contains("searchFilter")) { strSearchFilter = m_request.Tables[0].Rows[0]["searchFilter"].ToString() != "" ? m_request.Tables[0].Rows[0]["searchFilter"].ToString() : strSearchFilter; } if (strWhere.Trim() != "") { strWhere += " AND " + strWhere; } if (strSearchFilter.Trim() != "") { strWhere += " AND " + strSearchFilter; } } ds_Return = conn.GetDataSetForPageList("SELECT * FROM CM_Depot_Opration_History WHERE " + strWhere, new string[] { }, nPage, nPageRowCount, strSort + " " + strOrder); } } return ds_Return; }
public DataSet GetData() { DataSet ds = new DataSet(); DataSet ds_Return = new DataSet(); string strXML = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); ds = Common.Common.GetDSByExcelXML(strXML); Dbconn conn = new Dbconn("WSRR"); DataTable dtOptype = ds.Tables["OPTYPE"]; DataTable dtList = ds.Tables["LIST"]; if (dtOptype.Rows[0][0].ToString().ToUpper() == "GET") { string strKeyWord = dtList.Rows[0]["KeyWord"].ToString(); string strSQL = @" SELECT WS_Applay_ID , WS_Name , CONVERT(VARCHAR(10),Applay_Time,21) Applay_Time , Appliay_User , Function_Name , state , WS_Content , Input_Format , Return_Format , WS_DeployServer , WS_SPName , Logic_Content , Code , Service_Path , WS_SP_Server FROM [dbo].[WS_Applay] WITH ( NOLOCK ) WHERE FREETEXT ( WS_Name, @Param0 ) OR FREETEXT ( WS_Name, @Param0 ) OR FREETEXT ( Appliay_User, @Param0 ) OR FREETEXT ( state, @Param0 ) OR FREETEXT ( WS_Content, @Param0 ) OR FREETEXT ( Input_Format, @Param0 ) OR FREETEXT ( Return_Format, @Param0 ) OR FREETEXT ( WS_DeployServer, @Param0 ) OR FREETEXT ( WS_SPName, @Param0 ) OR FREETEXT ( Logic_Content, @Param0 ) OR FREETEXT ( Code, @Param0 ) OR FREETEXT ( Service_Path, @Param0 ) OR FREETEXT ( Function_Name, @Param0 ) OR FREETEXT ( WS_SP_Server, @Param0 ) "; ds_Return = conn.GetDataSetForPageList(strSQL, new string[1] { strKeyWord }, m_hzyPageInfo); } return ds_Return; }
/// <summary> /// 根据条件获取数据集 条件-分页-排序 求和等 /// </summary> /// <returns></returns> public DataSet GetRows(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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[] { }; if (strType == "GetHs") // 加班主 { strSQL = @" SELECT * FROM B02_BILL.HR1_OverTimeApply WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetDs") // 加班主从 { strSQL = @" SELECT * FROM B02_BILL.HR2_OverTimeRecode WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetAs") // 任务 { strSQL = @" SELECT * FROM B02_BILL.HR3_Approve WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetAc") // 根据员工号获取用户角色 { strSQL = @" SELECT * FROM B02_BILL.HR4_Authority WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetPerRecord") // 人员信息记录 { strSQL = @" SELECT * FROM B02_BILL.HR5_EMPLOYEE WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetPerTurnRecord") // 人员调转记录 { strSQL = @" SELECT * FROM B02_BILL.HR7_STAFFRECORD WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetPerDimissRecord") // 人员离职记录 { strSQL = @" SELECT * FROM B02_BILL.HR8_DIMISSION WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetSYSConfig") // 人员离职记录 { strSQL = @" SELECT * FROM B02_BILL.HR9_SYSConfig WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } //获取当前用户加班权限配置信息 else if (strType == "GetOTSpecialDay") // 加班配置 { strSQL = @" SELECT * FROM B02_BILL.HR10_OTSpecialDay WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } return ds; }
/// <summary> /// 任务列表项目列表获取 /// </summary> /// <returns></returns> public DataSet GetData() { try { string strWhere = string.Empty; //where条件 string strSearchFilter = string.Empty; //筛选条件 if (m_request != null && m_request.Tables.Contains("Page_Info")) { strWhere = m_request.Tables[0].Rows[0]["where"].ToString() ; //获取where条件 if (m_request.Tables[0].Columns.Contains("searchFilter")) //获取筛选条件 { strSearchFilter = m_request.Tables[0].Rows[0]["searchFilter"].ToString() != "" ? m_request.Tables[0].Rows[0]["searchFilter"].ToString() : strSearchFilter; } if (strWhere.Trim() != "") { strWhere = " AND " + strWhere; } if (strSearchFilter.Trim() != "") { strWhere = " AND " + strSearchFilter; } if (string.IsNullOrEmpty(m_hzyPageInfo.Sort)) { m_hzyPageInfo.Sort = "UpdateTime"; m_hzyPageInfo.Order = "desc "; } } Dbconn conn = new Dbconn("WSRR"); //创建连接类 string strSQL = @" SELECT [TASK_ID] ,[WS_ID] ,[URL] ,[WS_Name] ,[WS_LogicName] ,[WS_PID] ,[Execute_Type] ,[Execute_StartTime] ,[Execute_EndTime] ,[Execute_Cycle] ,[Execute_DayStartTime] ,[Execute_DayEndTime] ,[Execute_WeekTime] ,[Execute_MonthTime] ,[Enabled] ,[IsDeleted] ,[Last_Execute_Time] ,[Last_Execute_Right_Time] ,[Error_Count] ,[Remind_ErrorCount] ,[Remind_UserName] ,[CreateUser] ,[CreateTime] ,[UpdateUser] ,[UpdateTime] FROM WS_Scheduling WITH ( NOLOCK ) WHERE 1=1 AND IsDeleted='False' " + strWhere; DataSet ds = conn.GetDataSetForPageList(strSQL, new string[0], m_hzyPageInfo); //调用方法,获取分页数据 return ds; } catch { throw; } }
/// <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; }
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 GetCCASList(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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 FA4_ID, FA4_CONO, FA4_DIVI, FA4_State, FA4_Department, FA4_Name, CONVERT(VARCHAR(10),FA4_FillDate,120) FA4_FillDate, FA4_DiagnosisPeople, FA4_NoteBook, FA4_Computer, FA4_DeviceNo, FA4_CPU, FA4_MEM, FA4_HD, FA4_BuyDate, FA4_OMoney, FA4_RMoney, FA4_OfficeWareBef1, FA4_OfficeWareBef2, FA4_OfficeWareBef3, FA4_OfficeWareBef4, FA4_OfficeWareBef5, FA4_OfficeWareBef6, FA4_OfficeWareBef7, FA4_OfficeWareBef8, FA4_UnOfficeWareBef1, FA4_UnOfficeWareBef2, FA4_UnOfficeWareBef3, FA4_UnOfficeWareBef4, FA4_UnOfficeWareBef5, FA4_UnOfficeWareBef6, FA4_UnOfficeWareBef7, FA4_UnOfficeWareBef8, FA4_OpenTimeBef1, FA4_OpenTimeBef2, FA4_OpenTimeBef3, FA4_OpenTimeBef4, FA4_OfficeWareAft1, FA4_OfficeWareAft2, FA4_OfficeWareAft3, FA4_OfficeWareAft4, FA4_OfficeWareAft5, FA4_OfficeWareAft6, FA4_OfficeWareAft7, FA4_OfficeWareAft8, FA4_UnOfficeWareAft1, FA4_UnOfficeWareAft2, FA4_UnOfficeWareAft3, FA4_UnOfficeWareAft4, FA4_UnOfficeWareAft5, FA4_UnOfficeWareAft6, FA4_UnOfficeWareAft7, FA4_UnOfficeWareAft8, FA4_OpenTimeAft1, FA4_OpenTimeAft2, FA4_OpenTimeAft3, FA4_OpenTimeAft4, FA4_CompreheEvaluation, FA4_SpecificRequire, FA4_Suggest, FA4_Audit, CONVERT(VARCHAR(10),FA4_AuditDate,120) FA4_AuditDate, CONVERT(VARCHAR(20),FA4_RgDt,120) FA4_RgDt, FA4_RgUser, CONVERT(VARCHAR(20),FA4_LmDt,120) FA4_LmDt, FA4_LmUser, FA4_UptNo, FA4_RefID FROM B02_BILL.FA4_ComputerCAStandard WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); return ds; }
/// <summary> /// 获取参考项目 /// </summary> /// <returns></returns> public DataSet GetCKXMs(Dbconn conn, String strType) { DataSet ds1 = new DataSet(); //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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[] { }; String strSQL = @" SELECT DISTINCT B.OZ1_Brand+'-'+A.CT1_Code+'-'+A.CT1_CodeName AS XMID, A.CT1_Code, A.CT1_CodeName, B.OZ1_Brand FROM ( SELECT CT1_Code, CT1_CodeName, CT1_FatherID FROM [PE_OA].[M01_CONF].[CT0_CSYTAB] WHERE [CT1_FieldValues1] = 'XM' ) A JOIN F01_CERT.OZ1_Organization B ON A.[CT1_FatherID] = B.OZ1_id WHERE 1=1 " + strCons ; ds1 = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); return ds1; }
/// <summary> /// 根据条件获取数据集 条件-分页-排序 求和等 /// </summary> /// <returns></returns> public DataSet GetRows(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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[] { }; if (strType == "GetHs") { strSQL = @" SELECT * FROM B01_MDM.BG1_FABudget WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetSums") { strSQL = @" SELECT SUM(BG1_SureAmount) AS sTotal , COUNT(bg1_id) AS totalCount FROM B01_MDM.BG1_FABudget WHERE 1=1 " + strCons; ds = conn.GetDataSet(strSQL); } else if (strType == "GetHsT") //行政部统一编制 { strSQL = @" SELECT * FROM B01_MDM.BG1_FABudgetT WHERE 1=1 " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); } else if (strType == "GetSumsT")//行政部统一编制 { strSQL = @" SELECT SUM(BG1_SureAmount) AS sTotal , COUNT(bg1_id) AS totalCount FROM B01_MDM.BG1_FABudgetT WHERE 1=1 " + strCons; ds = conn.GetDataSet(strSQL); } return ds; }
/// <summary> /// 根据dept编码 获取dept信息 /// </summary> /// <returns></returns> public DataSet GetDeptByID(Dbconn conn, String strType) { DataSet ds = new DataSet(); String strSQL = ""; //获取条件值 string strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); 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 [F01_CERT].[OZ1_Organization] WHERE OZ1_State='启用' AND OZ1_OrgType <> '业务线' " + strCons; ds = conn.GetDataSetForPageList(strSQL, strParam, int.Parse(strPage), int.Parse(strNum), strOrderBy); return ds; }
/// <summary> /// 部署WS,为Easyui专用 /// </summary> /// <returns></returns> public DataSet GetData() { try { DataSet ds = new DataSet(); DataSet dsRequest = new DataSet(); int nPage = 1; int nPageRowCount = 200000; string strOrder = " desc "; string strSort = " body_id "; string strWhere = " 1=1 "; string strSearchFilter = ""; string strXML = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); if (strXML == "") { throw new Exception("提交的XML为空!"); } string strWhereTemp = ""; if (m_request != null && m_request.Tables[0].Columns.Contains("where")) { strWhereTemp = m_request.Tables[0].Rows[0]["where"].ToString(); } if (m_request != null && m_request.Tables[0].Columns.Contains("page")) { nPage = Convert.ToInt32(m_request.Tables[0].Rows[0]["page"]); nPageRowCount = Convert.ToInt32(m_request.Tables[0].Rows[0]["pagerows"]); strOrder = m_request.Tables[0].Rows[0]["order"].ToString() != "" ? m_request.Tables[0].Rows[0]["order"].ToString() : strOrder; strSort = m_request.Tables[0].Rows[0]["sort"].ToString() != "" ? m_request.Tables[0].Rows[0]["sort"].ToString() : strSort; strSearchFilter = m_request.Tables[0].Rows[0]["searchFilter"].ToString() != "" ? m_request.Tables[0].Rows[0]["searchFilter"].ToString() : strSearchFilter; if (strSearchFilter.Trim() != "") { strWhere += " AND " + strSearchFilter; } } if (strWhereTemp.Trim() != "") { strWhere += " AND " + strWhereTemp; } DataSet dsXML = Common.Common.GetDSByExcelXML(strXML); string strTableName = ""; strTableName = dsXML.Tables["List"].Rows[0]["TableName"].ToString(); if (strTableName == "") { throw new Exception("TableName不能为空!"); } Dbconn conn = new Dbconn("MDM_Market2"); string strSQL = @" select CASE WHEN name = 'timestamps' THEN 'ISNULL(CONVERT(bigint,' + c.name + '),0' + ') as ' + c.name + ',' ELSE 'ISNULL(CONVERT(VARCHAR(MAX),' + c.name + '),''''' + ') as ' + c.name + ',' END from syscolumns c where c.id = object_id('dbo." + strTableName + @"') FOR XML path('') "; string strColumns = ""; DataTable dtTmp = conn.GetDataTable(strSQL); for (int i = 0; i < dtTmp.Rows.Count; i++) { strColumns += dtTmp.Rows[i][0].ToString(); } strColumns = strColumns.Substring(0, strColumns.Length - 1); strSQL = " SELECT " + strColumns + " FROM " +strTableName+@" WHERE "+strWhere; ds = conn.GetDataSetForPageList(strSQL,new string[]{},nPage,nPageRowCount,strSort+" " + strOrder); return ds; } catch { throw; } }