/// <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;
}
}