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