/// <summary>
/// EnvoTemplates信息的CRUD
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
DataSet ds_Return = new DataSet();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
if (strXML == "")
{
throw new Exception("XML不能为空");
}
string strTemplate_ID = m_request.Tables[0].Rows[0]["Template_ID"].ToString();
if (strTemplate_ID == "")
{
throw new Exception("Template_ID不能为空");
}
DataSet ds = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = ds.Tables["OPTYPE"];
Dbconn conn = new Dbconn("HZY_SDK");
if (dt_OPTYPE.Rows[0][0].ToString().ToUpper() == "INSERT")
{
bool bResult = true;
DataTable dt = new DataTable();
dt.Columns.Add("Template_ID");
dt.Rows.Add(new object[] { strTemplate_ID });
Commom_Ds.SetUpdateInfo(dt, hzyMessage);
conn.BeginTransaction();
bResult = conn.Insert("Log_ViewTemlpates", dt);
conn.ExcuteQuerryByTran("UPDATE EnvoTemplates set ViewCount=ViewCount+1 where Template_ID=" + strTemplate_ID);
conn.CommitTransaction();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "修改成功" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "修改失败" });
}
ds_Return.Tables.Add(dt_EditResult);
}
return ds_Return;
}
public DataSet GetData()
{
DataSet ds_Return = null;
Dbconn conn = new Dbconn("WSRR_Log");
conn.BeginTransaction();
conn.LockTableList.Add("Log_CallByXML");
conn.LockTableList.Add("Log_CallByXML_His");
string time = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss.fff");
DataSet ds = conn.GetDataSetInTrans(@" SELECT
*,
NULL AS ElapsedTime,
NULL AS WS_ID,
NULL AS Session_ID,
NULL AS AppSN
FROM Log_CallByXML WHERE Log_Time<'" + time + "'");
conn.ExcuteQuerryByTran("INSERT INTO Log_CallByXML_HIS SELECT * FROM Log_CallByXML WHERE Log_Time<'" + time + "'");
conn.CommitTransaction();
//开始分析
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string strXML = ds.Tables[0].Rows[i]["XmlInput"].ToString();
string strWS_ID = "";
string strSession_ID = "";
string strAppSN = "";
try
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(strXML);
XmlNode xml = xmlDoc.DocumentElement;
strAppSN = xml.Attributes["App"].Value;
strWS_ID = xml.Attributes["WSID"].Value;
strSession_ID = xml.Attributes["SessionID"].Value;
xml = null;
xmlDoc = null;
}
catch (Exception)
{
}
//ds.Tables[0].Rows
}
return ds_Return;
}
/// <summary>
/// 供销售日报使用
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn_PLM_AS = new Dbconn("F22GP_ForSalesPlan");
DataSet ds = conn_PLM_AS.GetDataSet(
@" SELECT 94 AS Env_ID , --HZY_Sale_Report
null AS VexSSONewID ,
b.userid AS App_UserName ,
RIGHT(sys.fn_VarBinToHexStr(hashbytes('MD5', CONVERT(VARCHAR(100), b.password))), 32) AS App_Password ,
b.password AS App_PasswordSrc ,
b.username AS App_UserName_CN ,
GETDATE() Chang_Password_Time ,
1 Checked ,
'system' Check_User ,
GETDATE() Check_Time ,
GETDATE() Createtime ,
GETDATE() Updatetime
FROM j_depot a ,
j_user b
WHERE a.depotid = b.depotid
AND a.m_type = 11
");
Dbconn connMDM = new Dbconn("VexSSO");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("MDM_System_User");
connMDM.TableLock(tableList);
//删除数据
connMDM.ExcuteQuerryByTran(@"DELETE FROM MDM_System_User WHERE ENV_ID=94");
connMDM.InsertBYSQLBC("MDM_System_User", ds.Tables[0]);
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
/// <summary>
/// 对CFG_RequestType的CRUD,为Easyui专用
/// </summary>
/// <returns></returns>
public bool Execute()
{
DateTime time = DateTime.Now;
Dbconn conn = new Dbconn("VexSSO");
conn.LockTableList.Add("SSO_Login");
conn.BeginTransaction();
conn.TableLock();
string strSQL = @"
UPDATE dbo.SSO_Login
SET Logout_Time='" + time.ToString("yyyy-MM-dd HH:mm:ss.fff")+ @"',
MARK='SYSTEM'
WHERE Last_Request_Time<='" + time.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss.fff") + @"'
AND Logout_Time IS NULL
";
conn.ExcuteQuerryByTran(strSQL);
conn.CommitTransaction();
return true ;
}
/// <summary>
/// 根据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;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("DBDict");
//取到所有的数据库服务器
DataTable dt_DBServer = conn.GetDataTable("SELECT * FROM DBServer ", new string[0]);
//清空数据
conn.ExcuteQuerryByTran(@"
TRUNCATE TABLE DBDict.dbo.TableDict
TRUNCATE TABLE DBDict.dbo.TableSize
TRUNCATE TABLE DBDict.dbo.DBInfo
TRUNCATE TABLE DBDict.dbo.TableIndex
TRUNCATE TABLE DBDict.dbo.DBOtherInfo
TRUNCATE TABLE DBDict.dbo.LinkServers
");
if (dt_DBServer.Rows.Count > 0)
{
for (int i = 0; i < dt_DBServer.Rows.Count; i++)
{
string strConnString = "DataBase=master;Password="******"sql密码"].ToString() + ";User ID=" + dt_DBServer.Rows[i]["sql用户名"].ToString() + ";Data Source=" + dt_DBServer.Rows[i]["ip"].ToString() + ";";
string strSQL = "SELECT Name FROM Master..SysDatabases ORDER BY Name";
Dbconn conn_AllDB = new Dbconn(strConnString, true);
//所有的数据库名
DataTable dt_AllDB = conn_AllDB.GetDataTable(strSQL, new string[0]);
for (int k = 0; k < dt_AllDB.Rows.Count; k++)
{
if (dt_AllDB.Rows[k][0].ToString() == "tempdb")
{
continue;
}
strConnString = "DataBase=" + dt_AllDB.Rows[k][0].ToString() + ";Password="******"sql密码"].ToString() + ";User ID=" + dt_DBServer.Rows[i]["sql用户名"].ToString() + ";Data Source=" + dt_DBServer.Rows[i]["ip"].ToString() + ";";
Dbconn conn_DB = new Dbconn(strConnString, true);
string str_获取数据库中所有表的字段属性 = @"
SELECT @@SERVERNAME 服务器,db_name() 数据库名,d.name AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,
a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间,
GETDATE() 采集时间
FROM dbo.syscolumns a with(nolock) LEFT OUTER JOIN
dbo.systypes b with(nolock) ON a.xtype = b.xusertype INNER JOIN
dbo.sysobjects d with(nolock) ON a.id = d.id AND d.xtype = 'U' AND
d.status >= 0 LEFT OUTER JOIN
dbo.syscomments e with(nolock) ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties g with(nolock) ON a.id = g.major_id AND a.colid = g.minor_id AND
g.name = 'MS_Description' LEFT OUTER JOIN
sys.extended_properties f with(nolock) ON d.id = f.major_id AND f.minor_id = 0 AND
f.name = 'MS_Description'
ORDER BY d.name, 字段序号
";
string str_显示当前数据库中用户表的记录数量及大小 = @"
SELECT @@servername 服务器,db_name() 数据库,OBJECT_NAME(id) AS 表名,
rowcnt AS 记录数量,
8 * reserved / 1024 AS '大小(MB)',
GETDATE() 采集时间
FROM sysindexes a with(nolock)
LEFT JOIN sys.objects b with(nolock)
ON a.name = b.name
WHERE indid < 2
AND OBJECTPROPERTY(id, 'isusertable ') = 1
ORDER BY
rowcnt DESC
";
string str_查询所有表的索引 = @"
SELECT @@SERVERNAME AS 服务器,DB_NAME() AS 数据库,
c.name AS 表名,
a.name AS 索引名称, d.name AS 列名,
b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending')
WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL
THEN '' ELSE '√' END AS 主键, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered')
WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY(c.id,
a.name, 'IsUnique') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一,
CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一约束,
a.OrigFillFactor AS 填充因子, c.crdate AS 创建时间, c.refdate AS 更改时间,
GETDATE() AS 采集时间
FROM dbo.sysindexes a with(nolock) INNER JOIN
dbo.sysindexkeys b with(nolock) ON a.id = b.id AND a.indid = b.indid INNER JOIN
dbo.syscolumns d with(nolock) ON b.id = d.id AND b.colid = d.colid INNER JOIN
dbo.sysobjects c with(nolock) ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN
dbo.sysobjects e with(nolock) ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN
dbo.sysobjects p with(nolock) ON p.name = a.name AND p.xtype = 'PK'
WHERE (OBJECTPROPERTY(a.id, N'IsUserTable') = 1) AND (OBJECTPROPERTY(a.id,
N'IsMSShipped') = 0) AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0)
ORDER BY c.name, a.name, b.keyno
";
string str_表视图函数存储过程触发器主键外键约束规则 = @"
SELECT DISTINCT @@SERVERNAME AS 服务器,DB_NAME() AS 数据库,
isnull(p.name,'') AS 父对象, o.xtype,
CASE o.xtype WHEN 'C' THEN 'CHECK 约束' WHEN 'D' THEN '默认值或DEFAULT约束'
WHEN 'F' THEN 'FOREIGNKEY约束' WHEN 'L' THEN '日志' WHEN 'FN' THEN '标量函数'
WHEN 'IF' THEN '内嵌表函数' WHEN 'P' THEN '存储过程' WHEN 'PK' THEN 'PRIMARYKEY约束'
WHEN 'RF' THEN '复制筛选存储过程' WHEN 'S' THEN '系统表' WHEN 'TF' THEN '表函数'
WHEN 'TR' THEN '触发器' WHEN 'U' THEN '用户表' WHEN 'UQ' THEN 'UNIQUE 约束'
WHEN 'V' THEN '视图' WHEN 'X' THEN '扩展存储过程' WHEN 'R' THEN '规则' ELSE NULL
END AS 类型, o.name AS 对象名, o.crdate AS 创建时间, o.refdate AS 更改时间,
c.text AS 声明语句,
GETDATE() AS 采集时间
FROM dbo.sysobjects o with(nolock) Left JOIN
dbo.sysobjects p with(nolock) ON o.parent_obj = p.id LEFT OUTER JOIN
dbo.syscomments c with(nolock) ON o.id = c.id
WHERE --(o.xtype IN ('C','D','F','PK','UQ','L','FN','IF','TF','TR','P','R','RF','X','S','U','V')) AND
(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0) AND (isnull(p.name,'') <> N'dtproperties')
ORDER BY o.xtype DESC
";
string str_取本机已建立的链接服务器 = @"
SELECT @@servername AS SeverName,s.name linkedServerName,
s.data_source linkedserver_source,
s.is_linked,
s.modify_date,
ll.remote_name,
ll.local_principal_id,
ll.uses_self_credential,
p.name localLoginName,
GETDATE() AS CollecterDate
FROM sys.Linked_logins ll with(nolock)
INNER JOIN sys.servers s with(nolock)
ON s.server_id = ll.server_id
LEFT JOIN sys.server_principals p with(nolock)
ON p.principal_id = ll.local_principal_id
WHERE s.is_linked = 1
";
DataTable dt_获取数据库中所有表的字段属性 = conn_DB.GetDataTable(str_获取数据库中所有表的字段属性, new string[0]);
DataTable dt_显示当前数据库中用户表的记录数量及大小 = conn_DB.GetDataTable(str_显示当前数据库中用户表的记录数量及大小, new string[0]);
DataTable dt_查询所有表的索引 = conn_DB.GetDataTable(str_查询所有表的索引, new string[0]);
DataTable dt_表视图函数存储过程触发器主键外键约束规则 = conn_DB.GetDataTable(str_表视图函数存储过程触发器主键外键约束规则, new string[0]);
DataTable dt_取本机已建立的链接服务器 = conn_DB.GetDataTable(str_取本机已建立的链接服务器, new string[0]);
DataTable dt_数据库名 = dt_获取数据库中所有表的字段属性.DefaultView.ToTable(true, new string[1] { "数据库名" });
DataTable dt_DBInfo = new DataTable();
if(dt_数据库名.Rows.Count>0)
{
string str_DBInfo = @"
SELECT @@servername AS ServerName,
DB_NAME() AS DBName,
type_desc,
name,
physical_name,
size,
growth,
GETDATE() AS CollecterDate
FROM " + dt_数据库名.Rows[0][0].ToString() + @".sys.database_files with(nolock)
";
dt_DBInfo = conn_DB.GetDataTable(str_DBInfo, new string[0]);
}
ArrayList listTable = new ArrayList();
listTable.Add("TableDict");
listTable.Add("TableSize");
listTable.Add("DBInfo");
listTable.Add("TableIndex");
listTable.Add("DBOtherInfo");
listTable.Add("LinkServers");
//conn.ConnOpen();
conn.BeginTransaction();
try
{
conn.TableLock(listTable);
if (dt_DBInfo.Rows.Count > 0)
{
conn.InsertBYSQLBC("DBInfo", dt_DBInfo);
}
conn.InsertBYSQLBC("TableDict", dt_获取数据库中所有表的字段属性);
conn.InsertBYSQLBC("TableSize", dt_显示当前数据库中用户表的记录数量及大小);
conn.InsertBYSQLBC("TableIndex", dt_查询所有表的索引);
conn.InsertBYSQLBC("DBOtherInfo", dt_表视图函数存储过程触发器主键外键约束规则);
conn.InsertBYSQLBC("LinkServers", dt_取本机已建立的链接服务器);
conn.CommitTransaction();
//conn.ConnClose();
}
catch
{
conn.RollbackTransaction();
}
}
}
}
return true;
}
public bool Execute()
{
Dbconn m_Conn_MDM = new Dbconn("MDM");
DataSet Config = new DataSet();
string strSql = @" SELECT [Com] ,
[Type] ,
[Conn] ,
[SKUTable]
FROM [DRPStlyeInfoSyncConfig] ";
Config = m_Conn_MDM.GetDataSet(strSql);
for (int i = 0; i < Config.Tables[0].Rows.Count; i++)
{
string Com = Config.Tables[0].Rows[i]["Com"].ToString();
string Type = Config.Tables[0].Rows[i]["Type"].ToString();
string SKUTable = Config.Tables[0].Rows[i]["SKUTable"].ToString();
string Conn = Config.Tables[0].Rows[i]["Conn"].ToString();
Dbconn BillConn = new Dbconn(Conn);
DataSet DRPSKU = m_Conn_F22GP_DRP_REPORT.GetDataSetBySP("SP_OnlineStyle", new string[3] { "@COM", "@Type", "@Styleid" },
new string[3] { Com, Type, "" });
try
{
BillConn.BeginTransaction();
if (DRPSKU.Tables[0] == null || DRPSKU.Tables[0].Rows.Count == 0)
{
throw new Exception("请传入需要导入的数据！");
}
BillConn.ExcuteQuerryByTran("TRUNCATE TABLE " + SKUTable);
BillConn.InsertBYSQLBC(SKUTable, DRPSKU.Tables[0]);
BillConn.CommitTransaction();
//HZY.COM.Common.Log.WirteLogWS("成功", null);
//return true;
}
catch (SqlException ex)
{
BillConn.RollbackTransaction();
if (ex.Message.Contains("唯一索引"))
{
dt_EditResult.Rows.Add(new object[] { 1, "插入了重复键值，请检查！" });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
}
else
{
dt_EditResult.Rows.Add(new object[] { 1, ex.Message });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
}
HZY.COM.Common.Log.WirteLogWS(ex, null);
return false;
}
catch (Exception ex)
{
BillConn.RollbackTransaction();
dt_EditResult.Rows.Add(new object[] { 1, ex.Message });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
HZY.COM.Common.Log.WirteLogWS(ex, null);
return false;
}
}
dt_EditResult.Rows.Add(new object[] { 0, "成功！" });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
return true;
}
/// <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;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("AM");
Dbconn connMDM = new Dbconn("VexSSO");
conn.BeginTransaction();
connMDM.BeginTransaction();
try
{
DataSet ds = conn.GetDataSet(
@"
DECLARE @maxid AS BINARY(10);
SELECT t1.[__$start_lsn] AS LogID,
t1.[__$seqval] ,
t1.[__$operation] OprationType ,
t1.CN_ID ,
t1.CN_LOGIN ,
t1.CN_USER_NAME ,
t1.CN_PASSWORD ,
Convert(int,t1.CN_ISDELETE) as CN_ISDELETE ,
Convert(int,t1.CN_DISABLED) as CN_DISABLED
INTO #tmp
FROM [cdc].[dbo_TN_SYS_EM_USER_CT] t1 ,
[M01_CONF].[S01_Sync] t2
WHERE 1=1
AND t1.[__$start_lsn]> ISNULL(t2.MaxStart_lsn,0x00023FE600000FBF0004)
AND t2.SyncName='MDM数据获取'
AND t1.[__$update_mask] !=0x00004000
AND t1.[__$update_mask] !=0x00800000
AND t1.[__$update_mask] !=0x00804000
SELECT @maxid=MAX(LogID)
FROM #tmp
if(@maxid is not null)
begin
UPDATE [M01_CONF].[S01_Sync] SET MaxStart_lsn=@maxid
WHERE SyncName='MDM数据获取'
end
select * from #tmp
order by [LogID] ASC,[__$seqval] ASC ,[OprationType] ASC
");
string strSQL = "";
Byte[] strMaxID = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
strMaxID = (Byte[])ds.Tables[0].Rows[i]["LogID"];
string strCN_LOGIN = ds.Tables[0].Rows[i]["CN_LOGIN"].ToString();
string strCN_PASSWORD = ds.Tables[0].Rows[i]["CN_PASSWORD"].ToString();
string strCN_ISDELETE = ds.Tables[0].Rows[i]["CN_ISDELETE"].ToString();
string strOprationType = ds.Tables[0].Rows[i]["OprationType"].ToString();
if (strOprationType == "1" || strOprationType == "4") //添加或者更新
{
if (connMDM.GetDataTableRowCount("select 1 as a from MDM_Employees where SSO_UserName=@param0", new string[] { strCN_LOGIN }) == 0)
{
strSQL = @"INSERT INTO MDM_Employees (Name,SSO_UserName,SSO_Password,Leave_Flag,IsActived)
Values(@param0,@param1,@param2,@param3,'1')";
}
else
{
strSQL = @"update MDM_Employees
SET SSO_Password=@param2,Leave_Flag=@param3
WHERE SSO_UserName=@param1
";
}
connMDM.ExcuteQuerryByTran(strSQL, new string[] { strCN_LOGIN, strCN_LOGIN, strCN_PASSWORD, strCN_ISDELETE });
}
else if (strOprationType == "2" || strOprationType == "3") //删除
{
strSQL = @"update MDM_Employees
SET Leave_Flag=@param0
WHERE SSO_UserName=@param1";
connMDM.ExcuteQuerryByTran(strSQL, new string[] { "1", strCN_LOGIN });
}
}
if (strMaxID != null)
{
strSQL = "UPDATE M01_CONF.S01_Sync SET MaxStart_lsn=@param0,LastSyncTime=getdate() WHERE SyncName='MDM数据获取'";
conn.ExcuteQuerryByTran(strSQL, new object[] { strMaxID });
}
conn.CommitTransaction();
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
conn.RollbackTransaction();
throw;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("AM");
DataSet ds = conn.GetDataSet(
@"WITH cte ( AM_S_Dept_ID, AM_P_Dept_ID, AM_Dept_NAME, AM_Dept_NAME_ALL, OrderIndex,AM_Company,AM_Dept1_ID, AM_Dept2_ID, AM_Dept3_ID, AM_Dept4_ID)
AS (--父项
SELECT cn_id AS CN_S_EMITEM_ID ,
0 AS CN_P_EMITEM_ID ,
CONVERT(VARCHAR(200), CN_VIEW_NAME) AS CN_GROUP_NAME ,
CONVERT(VARCHAR(200), CN_VIEW_NAME) AS CN_GROUP_NAME_ALL,
CONVERT(VARCHAR(100), RIGHT('0000'
+ CONVERT(VARCHAR(10), ROW_NUMBER() OVER ( ORDER BY CN_LEVEL )),
4)) OrderIndex ,
cn_id AS Company ,
NULL AS Dept1 ,
NULL AS Dept2 ,
NULL AS Dept3 ,
NULL AS Dept4
FROM dbo.TN_SYS_EM_VIEW WITH ( NOLOCK )
WHERE 1 = 1
AND CN_VIEW_TYPE = 1
AND CN_OWNERID = 0
UNION ALL
--递归结果集中的下级
SELECT t1.CN_S_EMITEM_ID ,
t1.CN_P_EMITEM_ID ,
CONVERT(VARCHAR(200), t3.CN_GROUP_NAME) ,
CONVERT(VARCHAR(200), t2.AM_Dept_NAME_All + '\'
+ t3.CN_GROUP_NAME) AS CN_GROUP_NAME,
CONVERT(VARCHAR(100),OrderIndex + RIGHT('0000'
+ CONVERT(VARCHAR(4), ROW_NUMBER() OVER ( ORDER BY CN_LEVEL, t3.CN_GROUP_NAME )),
4)) ,
t2.AM_Company ,
CASE WHEN LEN(OrderIndex) = 4 THEN t3.CN_ID
WHEN LEN(OrderIndex) > 4 THEN t2.AM_Dept1_ID
ELSE NULL
END AS Dept1 ,
CASE WHEN LEN(OrderIndex) = 8 THEN t3.CN_ID
WHEN LEN(OrderIndex) > 8 THEN t2.AM_Dept2_ID
ELSE NULL
END AS Dept2 ,
CASE WHEN LEN(OrderIndex) = 12 THEN t3.CN_ID
WHEN LEN(OrderIndex) > 12 THEN t2.AM_Dept3_ID
ELSE NULL
END AS Dept3 ,
CASE WHEN LEN(OrderIndex) = 16 THEN t3.CN_ID
WHEN LEN(OrderIndex) > 16 THEN t2.AM_Dept4_ID
ELSE NULL
END AS Dept4
FROM dbo.TN_SYS_EM_RELATION t1 WITH ( NOLOCK )
INNER JOIN cte AS t2 ON t1.CN_P_EMITEM_ID = t2.AM_S_Dept_ID
INNER JOIN dbo.TN_SYS_EM_GROUP t3 WITH ( NOLOCK ) ON t3.cn_id = t1.CN_S_EMITEM_ID
WHERE cn_s_emitem_type = 1
AND CN_OWNERID = 0 --在陈杰的基础上加了这个条件，经过测试，与小翅膀的组织框架一致
)
SELECT *
FROM cte ");
Dbconn connMDM = new Dbconn("MDM");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("AM_Dept_log");
tableList.Add("am_dept");
connMDM.TableLock(tableList);
//存入日志数据
connMDM.ExcuteQuerryByTran(@"INSERT INTO dbo.AM_Dept_log
( Log_datetime ,
AM_S_Dept_ID ,
AM_P_Dept_ID ,
AM_Dept_NAME ,
AM_Dept_NAME_ALL,
AM_Company,
AM_Dept1_ID,
AM_Dept2_ID,
AM_Dept3_ID,
AM_Dept4_ID,
CreateTime ,
UpdateTime ,
Timestamps_old
)
SELECT GETDATE() ,
AM_S_Dept_ID ,
AM_P_Dept_ID ,
AM_Dept_NAME ,
AM_Dept_NAME_ALL,
AM_Company,
AM_Dept1_ID,
AM_Dept2_ID,
AM_Dept3_ID,
AM_Dept4_ID,
CreateTime ,
UpdateTime,
Convert(bigint,Timestamps)
FROM am_dept;");
//删除数据
connMDM.ExcuteQuerryByTran("DELETE FROM am_dept");
connMDM.Insert("am_dept", ds.Tables[0]);
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("AM");
DataSet ds = conn.GetDataSet(
@"SELECT cn_id as AM_User_id,
CN_LOGIN as AM_login,
CN_USER_NAME as AM_User_Name,
CN_PASSWORD AM_Password,
CN_EMAIL AM_Email,
CN_TITLE AM_Title,
CN_HANDSET AM_Handset,
CN_ISDELETE AM_IsDelete
FROM dbo.TN_SYS_EM_USER t1 ,
dbo.TN_SYS_EM_USER_PROP t2
WHERE t1.cn_id = t2.CN_USERID
");
Dbconn connMDM = new Dbconn("MDM");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("AM_User_log");
tableList.Add("AM_User");
connMDM.TableLock(tableList);
// //存入日志数据
// connMDM.ExcuteQuerryByTran(@"INSERT INTO dbo.AM_User_log
// ( Log_datetime ,
// AM_User_id ,
// AM_LOGIN ,
// AM_USER_NAME,
// AM_PASSWORD,
// AM_EMAIL,
// AM_TITLE,
// AM_HANDSET,
// AM_ISDELETE,
// CreateTime ,
// UpdateTime ,
// Timestamps_old
//
// )
// SELECT GETDATE() ,
// AM_User_id ,
// AM_LOGIN ,
// AM_USER_NAME,
// AM_PASSWORD,
// AM_EMAIL,
// AM_TITLE,
// AM_HANDSET,
// AM_ISDELETE,
// CreateTime ,
// UpdateTime,
// CONVERT(BIGINT,Timestamps)
// FROM AM_User;");
//删除数据
connMDM.ExcuteQuerryByTran("DELETE FROM AM_User");
connMDM.InsertBYSQLBC("AM_User", ds.Tables[0]);
//更新MDM_Employees的用户信息
connMDM.ExcuteQuerryByTran(@"UPDATE MDM_Employees
SET SSO_Password=t2.AM_Password,
Leave_Flag=AM_ISDELETE
FROM dbo.AM_User t2 WHERE MDM_Employees.SSO_UserName = t2.AM_login
"
);
//并添加新入职的数据
connMDM.ExcuteQuerryByTran(@"
INSERT INTO dbo.MDM_Employees
(
Name ,
Sex ,
Phone ,
Moblie ,
SSO_UserName ,
SSO_Password ,
In_Time ,
Leave_Time ,
Leave_Flag ,
IsActived
)
SELECT
AM_login ,
NULL,
NULL,
NULL,
AM_login ,
AM_Password ,
GETDATE(),
NULL,
AM_ISDELETE,
1 FROM dbo.AM_User
WHERE AM_login NOT IN (SELECT SSO_UserName FROM MDM_Employees)
"
);
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}
/// <summary>
/// 根据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 = "";
DateTime PR1_LmDt = DateTime.Now;
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(PR1_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
[PR1_CONO]
,[PR1_DIVI]
,[PR1_OrderId]
,[PR1_OrderCode]
,[PR1_OrderTypeId]
,[PR1_PR0_ContractId]
,[PR1_SU0_SupplierId]
,[PR1_BD0_BrandId]
,CAST([PR1_PurchaseTotal] AS DECIMAL(20,0)) as PR1_PurchaseTotal
,CAST([PR1_POriginalAmount] AS DECIMAL(20,2)) as PR1_POriginalAmount
,CAST([PR1_PurchaseAmount] AS DECIMAL(20,2)) as PR1_PurchaseAmount
,CAST([PR1_Discount] AS DECIMAL(20,2)) as PR1_Discount
,CAST([PR1_ExchangeRate] AS DECIMAL(20,2)) as PR1_ExchangeRate
,CAST([PR1_Tariff] AS DECIMAL(20,2)) as PR1_Tariff
,CAST([PR1_VAT] AS DECIMAL(20,2)) as PR1_VAT
,CAST([PR1_Freight] AS DECIMAL(20,2)) as PR1_Freight
,CAST([PR1_Times] AS DECIMAL(20,2)) as PR1_Times
,[PR1_Status]
,[PR1_RgUser]
,[PR1_RgDt]
,[PR1_LmUser]
,[PR1_LmDt]
,[PR1_UptNo]
,[PR1_CurrencyTypeId]
,A.[SU0_SupplierCode] AS SU0_SupplierCode
,A.[SU0_SupplierType] AS SU0_SupplierType
,B.[BD0_BrandName] AS BD0_BrandName
,C.PR0_ContractCode AS PR0_ContractCode
,D.[DC0_DictName1] AS OrderTypeDictName
,F.[DC0_DictName1] AS StatusDictName
,G.[DC0_DictName1] AS CurrencyTypeDictName
,H.[DC0_DictName1] AS PR1_SupplierType
FROM GL_ERP.B02_Bill.PR1_Order WITH ( NOLOCK )
LEFT JOIN B01_MDM.SU0_Supplier A ON PR1_SU0_SupplierId=A.SU0_SupplierId
LEFT JOIN B01_MDM.BD0_Brand B ON PR1_BD0_BrandId=B.BD0_BrandId
LEFT JOIN B02_Bill.PR0_Contract C ON PR1_PR0_ContractId=C.PR0_ContractId
LEFT JOIN M01_CONF.DC0_Dictionary D ON PR1_OrderTypeId=D.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary F ON PR1_Status=F.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary G ON PR1_CurrencyTypeId=G.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary H ON A.SU0_SupplierType=H.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [PR1_LmDt] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("GL_ERP.B02_Bill.PR1_Order");
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 GL_ERP.B02_Bill.PR1_Order SET PR1_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1_OrderId"].ToString() + @"'
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
strSQL = @"UPDATE B02_Bill.PR1D_OrderDetail SET PR1D_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1_OrderId"].ToString() + @"'
AND isnull(PR1D_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 GL_ERP.B02_Bill.PR1_Order WHERE PR1_OrderId=@param0
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR1_OrderId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["PR1_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PR1_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PR1_LmDt"))
{
dt.Columns.Add("PR1_LmDt");
}
if (!dt.Columns.Contains("PR1_UptNo"))
{
dt.Columns.Add("PR1_UptNo");
}
dt.Rows[0]["PR1_LmDt"] = DateTime.Now;
dt.Rows[0]["PR1_UptNo"] = strCode;
}
dt.Columns.Remove("PR1_SupplierType");
conn.Update("GL_ERP.B02_Bill.PR1_Order", dt, "PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1_OrderId"].ToString() + @"'");
if (ds.Tables["LIST"].Rows[i]["PR1_ExchangeRate"].ToString() != ""
|| ds.Tables["LIST"].Rows[i]["PR1_Tariff"].ToString() != ""
|| ds.Tables["LIST"].Rows[i]["PR1_VAT"].ToString() != ""
|| ds.Tables["LIST"].Rows[i]["PR1_Freight"].ToString() != ""
)
{
strSQL = @" UPDATE DD SET DD.PR1D_CurrencyAmount=EE.PR1D_CurrencyAmount,
DD.PR1D_EstimateCost=EE.PR1D_EstimateCost,
DD.PR1D_PCurrencyPrice=EE.PR1D_PCurrencyPrice
FROM B02_Bill.PR1D_OrderDetail DD,
(SELECT CC.PR1D_OrderDetailId,CC.PR1D_CurrencyAmount,CC.PR1D_EstimateCost,
CC.PR1D_PCurrencyPrice, case when cast(right(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),2)as int)<30 and '" + ds.Tables["LIST"].Rows[i]["PR1_SupplierType"].ToString() + @"'='国外'
then CAST(SUBSTRING(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',CC.PR1D_SCurrencyPrice)-1),1,len(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1))-2)-1 AS VARCHAR(20))+'99'
when cast(right(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),2)as int)>=30 and '" + ds.Tables["LIST"].Rows[i]["PR1_SupplierType"].ToString() + @"'='国外'
then SUBSTRING(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),1,len(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1))-2)+'99'
when '" + ds.Tables["LIST"].Rows[i]["PR1_SupplierType"].ToString() + @"'='国内' then '0'
END AS PR1D_SCurrencyPrice FROM
(SELECT AA.PR1D_OrderDetailId,AA.PR1D_CurrencyAmount as PR1D_CurrencyAmount,
AA.PR1D_EstimateCost as PR1D_EstimateCost,
CASE WHEN '" + ds.Tables["LIST"].Rows[i]["PR1_SupplierType"].ToString() + @"'='国内' THEN '0'
ELSE AA.PR1D_EstimateCost/AA.PR1D_Discount END AS PR1D_SCurrencyPrice,AA.PR1D_PCurrencyPrice as PR1D_PCurrencyPrice
FROM
(select PR1D_OrderDetailId,PR1D_Discount,PR1D_PR1_OrderId,PR1D_POriginalPrice*PR1_ExchangeRate AS PR1D_PCurrencyPrice,
CASE WHEN '" + ds.Tables["LIST"].Rows[i]["PR1_SupplierType"].ToString() + @"'='国内' THEN PR1D_POriginalPrice*PR1_ExchangeRate
ELSE PR1D_POriginalPrice*PR1_ExchangeRate*(1+PR1_Tariff)*(1+PR1_VAT)*(1+PR1_Freight)
END AS PR1D_EstimateCost,
PR1D_POriginalPrice*PR1_ExchangeRate*PR1D_Total AS PR1D_CurrencyAmount
FROM B02_Bill.PR1_Order,B02_Bill.PR1D_OrderDetail
WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1_OrderId"].ToString() + @"'
AND PR1_OrderId=PR1D_PR1_OrderId
)AA,
B02_Bill.PR1_Order BB WHERE AA.PR1D_PR1_OrderId=BB.PR1_OrderId) CC)EE
WHERE DD.PR1D_OrderDetailId=EE.PR1D_OrderDetailId";
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='" + PR1_LmDt + @"',
AA.PR1_UptNo=AA.PR1_UptNo+1
FROM
B02_Bill.PR1_Order AA,
(SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_PurchaseAmount,
SUM(PR1D_CurrencyAmount) AS PR1_POriginalAmount,PR1D_PR1_OrderId
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1_OrderId"].ToString() + @"'
GROUP BY PR1D_PR1_OrderId)BB
WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
conn.ExcuteQuerryByTran(strSQL);
strSQL = @"UPDATE A SET A.PR0_Amount=B.PR1_POriginalAmount
FROM B02_Bill.PR0_Contract A,
(select SUM(PR1_POriginalAmount) as PR1_POriginalAmount,PR1_PR0_ContractId
from B02_Bill.PR1_Order
WHERE PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1_OrderId"].ToString() + @"'
GROUP BY PR1_PR0_ContractId)B
WHERE A.PR0_ContractId=B.PR1_PR0_ContractId "
;
conn.ExcuteQuerryByTran(strSQL);
}
}
else //add
{
string PR1_OrderId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("PR1_OrderId"))
{
dt.Columns.Add("PR1_OrderId");
}
if (!dt.Columns.Contains("PR1_RgDt"))
{
dt.Columns.Add("PR1_RgDt");
}
if (!dt.Columns.Contains("PR1_LmDt"))
{
dt.Columns.Add("PR1_LmDt");
}
if (!dt.Columns.Contains("PR1_UptNo"))
{
dt.Columns.Add("PR1_UptNo");
}
dt.Rows[0]["PR1_OrderId"] = PR1_OrderId;
dt.Rows[0]["PR1_RgDt"] = DateTime.Now;
dt.Rows[0]["PR1_LmDt"] = DateTime.Now;
dt.Rows[0]["PR1_UptNo"] = '0';
dt.Columns.Remove("PR1_SupplierType");
conn.Insert("GL_ERP.B02_Bill.PR1_Order", dt);
}
}
}
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 = "";
string strSQL1 = "";
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(PR1D_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
[PR1D_CONO]
,[PR1D_DIVI]
,[PR1D_OrderDetailId]
,[PR1D_PR1_OrderId]
,[PR1D_PD0_ProductId]
,CAST([PR1D_Total] AS DECIMAL(20,0)) AS PR1D_Total
,[PR1D_UnitID]
,CAST([PR1D_POriginalPrice] AS DECIMAL(20,2)) as PR1D_POriginalPrice
,CAST([PR1D_PCurrencyPrice] AS DECIMAL(20,2)) as PR1D_PCurrencyPrice
,CAST([PR1D_SCurrencyPrice] AS DECIMAL(20,2)) as PR1D_SCurrencyPrice
,CAST([PR1D_Discount] AS DECIMAL(20,2)) as PR1D_Discount
,CAST([PR1D_EstimateCost] AS DECIMAL(20,2)) as PR1D_EstimateCost
,CAST([PR1D_OriginalAmount] AS DECIMAL(20,2)) as PR1D_OriginalAmount
,CAST([PR1D_CurrencyAmount] AS DECIMAL(20,2)) as PR1D_CurrencyAmount
,[PR1D_Status]
,[PR1D_SendDate]
,[PR1D_Seq]
,[PR1D_RgUser]
,[PR1D_RgDt]
,[PR1D_LmUser]
,[PR1D_LmDt]
,[PR1D_UptNo]
,PR1D_CurrencyTypeId
,PD0_ColorId
,PD0_ColorSUId
,PD0_ColorBSUId
,PD0_SizeSUId
,A.PR1_OrderCode
,B.PD0_ProductCodeSU
,B.PD0_SKUSU as PR1D_PD0_SKUSU
,B.PD0_SKU as PR1D_PD0_SKU
,C.SU0_SupplierCode
,C.SU0_SupplierType
,D.DC0_DictName1 AS UnitDictName
,E.DC0_DictName1 AS StatusDictName
,H.DC0_DictName1 AS CurrencyTypeDictName
,I.DC0_DictName1 AS ColorTypeDictName
FROM B02_Bill.PR1D_OrderDetail
LEFT JOIN B02_Bill.PR1_Order A ON A.PR1_OrderId=PR1D_PR1_OrderId
LEFT JOIN B01_MDM.PD0_Product B ON B.PD0_ProductId=PR1D_PD0_ProductId
LEFT JOIN B01_MDM.SU0_Supplier C ON PR1_SU0_SupplierId=C.SU0_SupplierId
LEFT JOIN M01_CONF.DC0_Dictionary D ON PR1D_UnitID=D.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary E ON PR1D_Status=E.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary F ON B.PD0_SKUSU=F.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary G ON B.PD0_Code=G.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary H ON PR1D_CurrencyTypeId=H.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary I ON B.PD0_ColorId=H.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [PR1D_LmDt] DESC ";
// ,PD0_SizeSUId
// ,I.DC0_DictName2 AS ColorBSUIdDictName
// ,I.DC0_DictName1 AS ColorSUIdDictName
// ,K.DC0_DictName1 AS SizeSUIdTypeDictName
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PR1D_OrderDetail");
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.PR1D_OrderDetail SET PR1D_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PR1D_OrderDetailId='" + ds.Tables["LIST"].Rows[i]["PR1D_OrderDetailId"].ToString() + @"'
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
strSQL = @"SELECT * FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId=@param0
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
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=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PR1_UptNo=AA.PR1_UptNo+1
FROM
B02_Bill.PR1_Order AA,
(SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_POriginalAmount,
SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_PurchaseAmount,PR1D_PR1_OrderId
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
GROUP BY PR1D_PR1_OrderId)BB
WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
conn.ExcuteQuerryByTran(strSQL);
}
else
{
strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal='0',
AA.PR1_PurchaseAmount='0',AA.PR1_POriginalAmount='0',
AA.PR1_LmUser='******',
AA.PR1_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PR1_UptNo=AA.PR1_UptNo+1
FROM
B02_Bill.PR1_Order AA
WHERE AA.PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].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 B02_Bill.PR1D_OrderDetail WHERE PR1D_OrderDetailId=@param0
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR1D_OrderDetailId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
strSQL = @"SELECT * FROM B01_MDM.PD0_Product WHERE PD0_ProductId=@param0
AND PD0_SKUSU=@param1 AND isnull(PD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SU = conn.GetDataTable(strSQL, new string[2] { ds.Tables["LIST"].Rows[i]["PR1D_PD0_ProductId"].ToString(), ds.Tables["LIST"].Rows[i]["PR1D_PD0_SKUSU"].ToString() });
//update
if (dt_SU.Rows.Count > 0)
{
//处理结果
// if (ds.Tables["LIST"].Rows[i]["PR1D_LmUser"].ToString() != "")
// {
DataTable dtTemp = conn.GetDataTableInTrans(
@" SELECT CC.PR1D_CurrencyAmount,CC.PR1D_POriginalPrice,CC.PR1D_OriginalAmount,CC.PR1D_EstimateCost,CC.PR1D_PCurrencyPrice,
case when cast(right(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),2)as int)<30 and '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国外'
then CAST(SUBSTRING(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',CC.PR1D_SCurrencyPrice)-1),1,len(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1))-2)-1 AS VARCHAR(20))+'99'
when cast(right(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),2)as int)>=30 and '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国外'
then SUBSTRING(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),1,len(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1))-2)+'99'
when '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' then '0'
END AS PR1D_SCurrencyPrice FROM
(SELECT AA.PR1D_CurrencyAmount as PR1D_CurrencyAmount,AA.PR1D_POriginalPrice,AA.PR1D_OriginalAmount as PR1D_OriginalAmount,AA.PR1D_EstimateCost as PR1D_EstimateCost,
CASE WHEN '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' THEN '0'
ELSE AA.PR1D_EstimateCost/" + ds.Tables["LIST"].Rows[i]["PR1D_Discount"].ToString() + @" END AS PR1D_SCurrencyPrice,AA.PR1D_PCurrencyPrice as PR1D_PCurrencyPrice
FROM
(select PR1_OrderId," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @" as PR1D_POriginalPrice ," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate AS PR1D_PCurrencyPrice,
CASE WHEN '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' THEN " + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate
ELSE " + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate*(1+PR1_Tariff)*(1+PR1_VAT)*(1+PR1_Freight)
END AS PR1D_EstimateCost," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*" + ds.Tables["LIST"].Rows[i]["PR1D_Total"].ToString() + @" AS PR1D_OriginalAmount,
" + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate*" + ds.Tables["LIST"].Rows[i]["PR1D_Total"].ToString() + @" AS PR1D_CurrencyAmount
FROM B02_Bill.PR1_Order
WHERE PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1')AA,
B02_Bill.PR1_Order BB WHERE AA.PR1_OrderId=BB.PR1_OrderId)CC
");
string PR1D_CurrencyAmount = dtTemp.Rows[0]["PR1D_CurrencyAmount"].ToString();
string PR1D_PCurrencyPrice = dtTemp.Rows[0]["PR1D_PCurrencyPrice"].ToString();
string PR1D_OriginalAmount = dtTemp.Rows[0]["PR1D_OriginalAmount"].ToString();
string PR1D_EstimateCost = dtTemp.Rows[0]["PR1D_EstimateCost"].ToString();
string PR1D_SCurrencyPrice = dtTemp.Rows[0]["PR1D_SCurrencyPrice"].ToString();
string PR1D_POriginalPrice = dtTemp.Rows[0]["PR1D_POriginalPrice"].ToString();
if (PR1D_SCurrencyPrice == "0")
{
PR1D_SCurrencyPrice = ds.Tables["LIST"].Rows[i]["PR1D_SCurrencyPrice"].ToString();
}
string strCode = dt_SR.Rows[0]["PR1D_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PR1D_LmDt"))
{
dt.Columns.Add("PR1D_LmDt");
}
if (!dt.Columns.Contains("PR1D_CurrencyAmount"))
{
dt.Columns.Add("PR1D_CurrencyAmount");
}
if (!dt.Columns.Contains("PR1D_PCurrencyPrice"))
{
dt.Columns.Add("PR1D_PCurrencyPrice");
}
if (!dt.Columns.Contains("PR1D_OriginalAmount"))
{
dt.Columns.Add("PR1D_OriginalAmount");
}
if (!dt.Columns.Contains("PR1D_EstimateCost"))
{
dt.Columns.Add("PR1D_EstimateCost");
}
if (!dt.Columns.Contains("PR1D_POriginalPrice"))
{
dt.Columns.Add("PR1D_POriginalPrice");
}
if (!dt.Columns.Contains("PR1D_SCurrencyPrice"))
{
dt.Columns.Add("PR1D_SCurrencyPrice");
}
if (!dt.Columns.Contains("PR1D_UptNo"))
{
dt.Columns.Add("PR1D_UptNo");
}
dt.Rows[0]["PR1D_LmDt"] = DateTime.Now;
dt.Rows[0]["PR1D_CurrencyAmount"] = PR1D_CurrencyAmount;
dt.Rows[0]["PR1D_PCurrencyPrice"] = PR1D_PCurrencyPrice;
dt.Rows[0]["PR1D_OriginalAmount"] = PR1D_OriginalAmount;
dt.Rows[0]["PR1D_EstimateCost"] = PR1D_EstimateCost;
dt.Rows[0]["PR1D_SCurrencyPrice"] = PR1D_SCurrencyPrice;
dt.Rows[0]["PR1D_POriginalPrice"] = PR1D_POriginalPrice;
dt.Rows[0]["PR1D_UptNo"] = strCode;
dt.Columns.Remove("PR1D_SupplierType");
dt.Columns.Remove("PR1D_PD0_SKUSU");
conn.Update("B02_Bill.PR1D_OrderDetail", dt, "PR1D_OrderDetailId='" + ds.Tables["LIST"].Rows[i]["PR1D_OrderDetailId"].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=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PR1_UptNo=AA.PR1_UptNo+1
FROM
B02_Bill.PR1_Order AA,
(SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_POriginalAmount,
SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_PurchaseAmount,PR1D_PR1_OrderId
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
GROUP BY PR1D_PR1_OrderId)BB
WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
conn.ExcuteQuerryByTran(strSQL);
strSQL = @"UPDATE B01_MDM.PD0_Product SET PD0_PurchasePrice='" + PR1D_CurrencyAmount + @"',
PD0_SalePrice='" + PR1D_SCurrencyPrice + @"'，
PD0_LmUser='******',
PD0_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
PD0_UptNo=PD0_UptNo+1
WHERE PD0_ProductId='" + ds.Tables["LIST"].Rows[i]["PR1D_PD0_ProductId"].ToString() + @"' "
;
strSQL1 = @"UPDATE A SET A.PR0_Amount=B.PR1_POriginalAmount,A.PR0_Total=PR1_PurchaseTotal
FROM B02_Bill.PR0_Contract A,
(select SUM(PR1_POriginalAmount) as PR1_POriginalAmount,PR1_PR0_ContractId,SUM(PR1_PurchaseTotal) as PR1_PurchaseTotal
from B02_Bill.PR1_Order
WHERE PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
GROUP BY PR1_PR0_ContractId)B
WHERE A.PR0_ContractId=B.PR1_PR0_ContractId "
;
conn.ExcuteQuerryByTran(strSQL1);
}
}
else //add
{
strSQL = @"SELECT * FROM B01_MDM.PD0_Product WHERE PD0_ProductId=@param0
AND PD0_SKUSU=@param1
";
DataTable dt_SU = conn.GetDataTable(strSQL, new string[2] { ds.Tables["LIST"].Rows[i]["PR1D_PD0_ProductId"].ToString(), ds.Tables["LIST"].Rows[i]["PR1D_PD0_SKUSU"].ToString() });
//update
if (dt_SU.Rows.Count > 0)
{
DataTable dtTemp = conn.GetDataTableInTrans(
@"
SELECT CC.PR1D_CurrencyAmount,CC.PR1D_POriginalPrice,CC.PR1D_OriginalAmount,CC.PR1D_EstimateCost,CC.PR1D_PCurrencyPrice,
case when cast(right(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),2)as int)<30 and '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国外'
then CAST(SUBSTRING(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',CC.PR1D_SCurrencyPrice)-1),1,len(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1))-2)-1 AS VARCHAR(20))+'99'
when cast(right(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),2)as int)>=30 and '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国外'
then SUBSTRING(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1),1,len(SUBSTRING(cast(CC.PR1D_SCurrencyPrice as varchar(20)),1,charindex('.',cast(CC.PR1D_SCurrencyPrice as varchar(20)))-1))-2)+'99'
when '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' then '0'
END AS PR1D_SCurrencyPrice FROM
(SELECT AA.PR1D_CurrencyAmount as PR1D_CurrencyAmount,AA.PR1D_POriginalPrice,AA.PR1D_OriginalAmount as PR1D_OriginalAmount,AA.PR1D_EstimateCost as PR1D_EstimateCost,
CASE WHEN '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' THEN '0'
ELSE AA.PR1D_EstimateCost/" + ds.Tables["LIST"].Rows[i]["PR1D_Discount"].ToString() + @" END AS PR1D_SCurrencyPrice,AA.PR1D_PCurrencyPrice as PR1D_PCurrencyPrice
FROM
(select PR1_OrderId," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @" as PR1D_POriginalPrice ," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate AS PR1D_PCurrencyPrice,
CASE WHEN '" + ds.Tables["LIST"].Rows[i]["PR1D_SupplierType"].ToString() + @"'='国内' THEN " + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate
ELSE " + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate*(1+PR1_Tariff)*(1+PR1_VAT)*(1+PR1_Freight)
END AS PR1D_EstimateCost," + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*" + ds.Tables["LIST"].Rows[i]["PR1D_Total"].ToString() + @" AS PR1D_OriginalAmount,
" + ds.Tables["LIST"].Rows[i]["PR1D_POriginalPrice"].ToString() + @"*PR1_ExchangeRate*" + ds.Tables["LIST"].Rows[i]["PR1D_Total"].ToString() + @" AS PR1D_CurrencyAmount
FROM B02_Bill.PR1_Order
WHERE PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1')AA,
B02_Bill.PR1_Order BB WHERE AA.PR1_OrderId=BB.PR1_OrderId)CC
");
string PR1D_CurrencyAmount = dtTemp.Rows[0]["PR1D_CurrencyAmount"].ToString();
string PR1D_PCurrencyPrice = dtTemp.Rows[0]["PR1D_PCurrencyPrice"].ToString();
string PR1D_OriginalAmount = dtTemp.Rows[0]["PR1D_OriginalAmount"].ToString();
string PR1D_EstimateCost = dtTemp.Rows[0]["PR1D_EstimateCost"].ToString();
string PR1D_SCurrencyPrice = dtTemp.Rows[0]["PR1D_SCurrencyPrice"].ToString();
string PR1D_POriginalPrice = dtTemp.Rows[0]["PR1D_POriginalPrice"].ToString();
if (PR1D_SCurrencyPrice == "0")
{
PR1D_SCurrencyPrice = ds.Tables["LIST"].Rows[i]["PR1D_SCurrencyPrice"].ToString();
}
string PR1D_OrderDetailId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("PR1D_OrderDetailId"))
{
dt.Columns.Add("PR1D_OrderDetailId");
}
if (!dt.Columns.Contains("PR1D_RgDt"))
{
dt.Columns.Add("PR1D_RgDt");
}
if (!dt.Columns.Contains("PR1D_LmDt"))
{
dt.Columns.Add("PR1D_LmDt");
}
if (!dt.Columns.Contains("PR1D_UptNo"))
{
dt.Columns.Add("PR1D_UptNo");
}
if (!dt.Columns.Contains("PR1D_CurrencyAmount"))
{
dt.Columns.Add("PR1D_CurrencyAmount");
}
if (!dt.Columns.Contains("PR1D_PCurrencyPrice"))
{
dt.Columns.Add("PR1D_PCurrencyPrice");
}
if (!dt.Columns.Contains("PR1D_OriginalAmount"))
{
dt.Columns.Add("PR1D_OriginalAmount");
}
if (!dt.Columns.Contains("PR1D_EstimateCost"))
{
dt.Columns.Add("PR1D_EstimateCost");
}
if (!dt.Columns.Contains("PR1D_SCurrencyPrice"))
{
dt.Columns.Add("PR1D_SCurrencyPrice");
}
dt.Rows[0]["PR1D_OrderDetailId"] = PR1D_OrderDetailId;
dt.Rows[0]["PR1D_RgDt"] = DateTime.Now;
dt.Rows[0]["PR1D_LmDt"] = DateTime.Now;
dt.Rows[0]["PR1D_UptNo"] = '0';
dt.Rows[0]["PR1D_CurrencyAmount"] = PR1D_CurrencyAmount;
dt.Rows[0]["PR1D_PCurrencyPrice"] = PR1D_PCurrencyPrice;
dt.Rows[0]["PR1D_OriginalAmount"] = PR1D_OriginalAmount;
dt.Rows[0]["PR1D_EstimateCost"] = PR1D_EstimateCost;
dt.Rows[0]["PR1D_SCurrencyPrice"] = PR1D_SCurrencyPrice;
dt.Rows[0]["PR1D_POriginalPrice"] = PR1D_POriginalPrice;
dt.Columns.Remove("PR1D_SupplierType");
dt.Columns.Remove("PR1D_PD0_SKUSU");
conn.Insert("B02_Bill.PR1D_OrderDetail", dt);
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=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PR1_UptNo=AA.PR1_UptNo+1
FROM
B02_Bill.PR1_Order AA,
(SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_POriginalAmount,
SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_PurchaseAmount,PR1D_PR1_OrderId
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
GROUP BY PR1D_PR1_OrderId)BB
WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
conn.ExcuteQuerryByTran(strSQL);
strSQL1 = @"UPDATE B01_MDM.PD0_Product SET PD0_PurchasePrice='" + PR1D_CurrencyAmount + @"',
PD0_SalePrice='" + PR1D_SCurrencyPrice + @"',
PD0_LmUser='******',
PD0_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
PD0_UptNo=PD0_UptNo+1
WHERE PD0_ProductId='" + ds.Tables["LIST"].Rows[i]["PR1D_PD0_ProductId"].ToString() + @"' "
;
conn.ExcuteQuerryByTran(strSQL1);
strSQL1 = @"UPDATE A SET A.PR0_Amount=B.PR1_POriginalAmount,A.PR0_Total=PR1_PurchaseTotal
FROM B02_Bill.PR0_Contract A,
(select SUM(PR1_POriginalAmount) as PR1_POriginalAmount,PR1_PR0_ContractId,SUM(PR1_PurchaseTotal) as PR1_PurchaseTotal
from B02_Bill.PR1_Order
WHERE PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
GROUP BY PR1_PR0_ContractId)B
WHERE A.PR0_ContractId=B.PR1_PR0_ContractId "
;
conn.ExcuteQuerryByTran(strSQL1) ;
}
}
}
}
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(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;
}
}
/// <summary>
/// 上传图片并保存到数据库中
/// </summary>
/// <returns></returns>
private DataSet setPhotToSave(Dbconn conn, String strType)
{
DataSet ds_Return = new DataSet();
String remoteHost = ConfigurationSettings.AppSettings["remoteHostHR"].ToString();
String remotePath = "";
String remoteUser = ConfigurationSettings.AppSettings["remoteUserHR"].ToString();
String remotePass = ConfigurationSettings.AppSettings["remotePassHR"].ToString() + "i";
String remoteDir = ConfigurationSettings.AppSettings["remotePathHR"].ToString();
int remotePort = 21;
int i = 0;
//获取条件值
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
string strPhotoPath = dsXML.Tables["LIST"].Rows[0]["photoPath"].ToString();
string strPhotoID = dsXML.Tables["LIST"].Rows[0]["photoID"].ToString();
string strActor = dsXML.Tables["LIST"].Rows[0]["actor"].ToString();
string strActorID = dsXML.Tables["LIST"].Rows[0]["actorid"].ToString();
string strUpType = dsXML.Tables["LIST"].Rows[0]["upType"].ToString();
if (strPhotoPath == null || strPhotoPath=="")
{
dt_EditResult.Rows.Add(new object[] { false, "文件不能为空！" });
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
//获取 UNIQUE_KEY
if (strUpType == "new")
{
strPhotoID = getUniqueKey(conn);
if (strPhotoID == "")
{
dt_EditResult.Rows.Add(new object[] { false, "获取 UNIQUE_KEY失败！" });
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
}
//获取原始文件名
string filename = strPhotoPath.Substring(strPhotoPath.LastIndexOf("\\") + 1
, strPhotoPath.Length - strPhotoPath.LastIndexOf("\\") - 1);
filename = filename.Substring(0, filename.LastIndexOf("-"))
+ filename.Substring(filename.LastIndexOf("."), filename.Length - filename.LastIndexOf("."));
//图片另存为.jpg
string newfile = strPhotoPath.Substring(0, strPhotoPath.LastIndexOf("\\")+1) + strPhotoID + ".jpg";
System.IO.File.Copy(strPhotoPath, newfile, true);
//上传文件
var ftcc = new FTP.FTPClient(remoteHost, remotePath, remoteUser, remotePass, remotePort);
ftcc.Connect();
ftcc.ChDir(remoteDir); // 切换到ftp对应目录下的文件夹路径
ftcc.Put(newfile);
//删除图片另存为.jpg
System.IO.File.Delete(newfile);
//保存到数据库
Byte[] byteSQLData = setPhotoByte(strPhotoPath);
string strSQL2 = @" insert into B02_BILL.HR6_Blob (hr6_id, hr6_belongid, hr6_filename, hr6_content, hr6_type)
values( @Param0, @Param1, @Param2, @Param3, @Param4 ) ";
i = conn.ExcuteQuerryByTran(strSQL2, new object[] { strPhotoID, "0", filename, byteSQLData, "0" });
if (i==1)
{
dt_EditResult.Rows.Add(new object[] { true, strPhotoID });
}
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 = "";
string strCode = "";
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(SU0_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
[SU0_CONO]
,[SU0_DIVI]
,[SU0_SupplierId]
,[SU0_SupplierCode]
,[SU0_SupplierType]
,[SU0_SupplierName]
,[SU0_Address]
,[SU0_Contact]
,[SU0_Phone]
,[SU0_Bank]
,[SU0_BankAccount]
,[SU0_BankAddress]
,[SU0_BankSwift]
,[SU0_Status]
,[SU0_RgUser]
,[SU0_RgDt]
,[SU0_LmUser]
,[SU0_LmDt]
,[SU0_UptNo]
,[DC0_DictName1]
FROM B01_MDM.SU0_Supplier WITH ( NOLOCK )
LEFT JOIN M01_CONF.DC0_Dictionary ON SU0_SupplierType=DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [SU0_SupplierCode] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B01_MDM.SU0_Supplier");
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.SU0_Supplier SET SU0_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE SU0_SupplierId='" + ds.Tables["LIST"].Rows[i]["SU0_SupplierId"].ToString() + @"'
AND ISNULL(SU0_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.SU0_Supplier WHERE SU0_SupplierId=@param0
AND ISNULL(SU0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["SU0_SupplierId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["SU0_LmUser"].ToString() != "")
{
string strUptNo = dt_SR.Rows[0]["SU0_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("SU0_LmDt"))
{
dt.Columns.Add("SU0_LmDt");
}
// if (!dt.Columns.Contains("SU0_Status"))
// {
// dt.Columns.Add("SU0_Status");
// }
if (!dt.Columns.Contains("SU0_UptNo"))
{
dt.Columns.Add("SU0_UptNo");
}
// dt.Rows[0]["SU0_Status"] = '0';
dt.Rows[0]["SU0_LmDt"] = DateTime.Now;
dt.Rows[0]["SU0_UptNo"] = strUptNo;
}
conn.Update("B01_MDM.SU0_Supplier", dt, "SU0_SupplierId='" + ds.Tables["LIST"].Rows[i]["SU0_SupplierId"].ToString() + @"'");
}
else //add
{
DataTable dtTemp = conn.GetDataTableInTrans(
@"SELECT MAX(SU0_SupplierCode) AS MaxCode ,'A'AS PreCode FROM B01_MDM.SU0_Supplier
WHERE SU0_SupplierCode LIKE 'A'+'%'");
//供应商编号生成
strCode = dtTemp.Rows[0]["MaxCode"].ToString();
if (strCode == "")
{
strCode = dtTemp.Rows[0]["PreCode"].ToString() + "0001";
}
else
{
strCode = dtTemp.Rows[0]["PreCode"].ToString() + (Convert.ToInt32(strCode.Substring(strCode.Length - 4)) + 1).ToString("0000");
}
string SU0_SupplierId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("SU0_SupplierId"))
{
dt.Columns.Add("SU0_SupplierId");
}
// if (!dt.Columns.Contains("SU0_Status"))
// {
// dt.Columns.Add("SU0_Status");
//}
if (!dt.Columns.Contains("SU0_RgDt"))
{
dt.Columns.Add("SU0_RgDt");
}
if (!dt.Columns.Contains("SU0_LmDt"))
{
dt.Columns.Add("SU0_LmDt");
}
if (!dt.Columns.Contains("SU0_SupplierCode"))
{
dt.Columns.Add("SU0_SupplierCode");
}
if (!dt.Columns.Contains("SU0_UptNo"))
{
dt.Columns.Add("SU0_UptNo");
}
dt.Rows[0]["SU0_SupplierId"] =SU0_SupplierId;
// dt.Rows[0]["SU0_Status"] = '0';
dt.Rows[0]["SU0_RgDt"] = DateTime.Now;
dt.Rows[0]["SU0_LmDt"] = DateTime.Now;
dt.Rows[0]["SU0_SupplierCode"] = strCode;
dt.Rows[0]["SU0_UptNo"] = '0';
conn.Insert("B01_MDM.SU0_Supplier", dt);
//ITSM_Common.SendAMMessage("")
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result", "SU0_SupplierCode" }, new string[] { "true", strCode });
}
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(A.DC0_DictStatus,'')<>'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 = strSQL + @" SELECT
B.DC0_CONO
,B.DC0_DIVI
,B.DC0_DictId
,B.DC0_FartherDictId
,B.DC0_DictName1
,B.DC0_DictName2
,B.DC0_DictName3
,B.DC0_DictName4
,B.DC0_DictName5
,B.DC0_DictStatus
,B.DC0_RgUser
,B.DC0_RgDt
,B.DC0_LmUser
,B.DC0_LmDt
,B.DC0_UptNo
FROM M01_CONF.DC0_Dictionary A,M01_CONF.DC0_Dictionary B WITH ( NOLOCK )
WHERE A.DC0_DictId=B.DC0_FartherDictId AND " + strWHERE;
/* strSQL = @"SELECT A.DC0_CONO
,A.DC0_DIVI
,A.DC0_DictId
,A.DC0_FartherDictId
,A.DC0_DictName1
,A.DC0_DictName2
,A.DC0_DictName3
,A.DC0_DictName4
,A.DC0_DictName5
,A.DC0_DictStatus
,A.DC0_RgUser
,A.DC0_RgDt
,A.DC0_LmUser
,A.DC0_LmDt
,A.DC0_UptNo
FROM M01_CONF.DC0_Dictionary A WITH ( NOLOCK )
WHERE " + strWHERE;
strSQL = strSQL+ @"UNION SELECT
B.DC0_CONO
,B.DC0_DIVI
,B.DC0_DictId
,B.DC0_FartherDictId
,B.DC0_DictName1
,B.DC0_DictName2
,B.DC0_DictName3
,B.DC0_DictName4
,B.DC0_DictName5
,B.DC0_DictStatus
,B.DC0_RgUser
,B.DC0_RgDt
,B.DC0_LmUser
,B.DC0_LmDt
,B.DC0_UptNo
FROM M01_CONF.DC0_Dictionary A,M01_CONF.DC0_Dictionary B WITH ( NOLOCK )
WHERE A.DC0_DictId=B.DC0_FartherDictId AND " + strWHERE;*/
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("M01_CONF.DC0_Dictionary");
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 M01_CONF.DC0_Dictionary SET DC0_DictStatus='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE DC0_DictId='" + ds.Tables["LIST"].Rows[i]["DC0_DictId"].ToString() + @"'
AND ISNULL(DC0_DictStatus,'')<>'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 M01_CONF.DC0_Dictionary WHERE DC0_DictId=@param0
AND ISNULL(DC0_DictStatus,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["DC0_DictId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["DC0_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["DC0_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("DC0_LmDt"))
{
dt.Columns.Add("DC0_LmDt");
}
// if (!dt.Columns.Contains("DC0_DictStatus"))
// {
// dt.Columns.Add("DC0_DictStatus");
// }
if (!dt.Columns.Contains("DC0_UptNo"))
{
dt.Columns.Add("DC0_UptNo");
}
// dt.Rows[0]["DC0_DictStatus"] = '0';
dt.Rows[0]["DC0_LmDt"] = DateTime.Now;
dt.Rows[0]["DC0_UptNo"] = strCode;
}
conn.Update("M01_CONF.DC0_Dictionary", dt, " DC0_DictId='" + ds.Tables["LIST"].Rows[i]["DC0_DictId"].ToString() + @"'");
}
else //add
{
string DC0_DictId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("DC0_DictId"))
{
dt.Columns.Add("DC0_DictId");
}
// if (!dt.Columns.Contains("DC0_DictStatus"))
// {
// dt.Columns.Add("DC0_DictStatus");
// }
if (!dt.Columns.Contains("DC0_RgDt"))
{
dt.Columns.Add("DC0_RgDt");
}
if (!dt.Columns.Contains("DC0_LmDt"))
{
dt.Columns.Add("DC0_LmDt");
}
if (!dt.Columns.Contains("DC0_UptNo"))
{
dt.Columns.Add("DC0_UptNo");
}
dt.Rows[0]["DC0_DictId"] = DC0_DictId;
// dt.Rows[0]["DC0_DictStatus"] = '0';
dt.Rows[0]["DC0_RgDt"] = DateTime.Now;
dt.Rows[0]["DC0_LmDt"] = DateTime.Now;
dt.Rows[0]["DC0_UptNo"] = '0';
conn.Insert("M01_CONF.DC0_Dictionary", dt);
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
public static HZYmessage CheckUser(string SessionID,bool pUpdateLastReustTime)
{
HZYmessage message = new HZYmessage();
if (SessionID == null || SessionID == "")
{
throw new Exception("身份验证错误");
}
Dbconn conn = new Dbconn("WSRR");
string strSQL = @"
SELECT
[LoginSessionID]
,[LoginUser]
,[LoginTime]
FROM F01_Cert.Login
where LoginSessionID=@param0
and LogoutTime is null
";
DataTable dt = conn.GetDataTable(strSQL, new string[] { SessionID });
if (dt.Rows.Count == 1)
{
message.Session_ID = dt.Rows[0]["LoginSessionID"].ToString();
//message.Employee_ID = dt.Rows[0]["LoginUser"].ToString();
message.User_ID = dt.Rows[0]["LoginUser"].ToString();
message.User_Name = dt.Rows[0]["LoginUser"].ToString();
message.User_Name_CN = dt.Rows[0]["LoginUser"].ToString();
if (pUpdateLastReustTime)
{
//需要更新用户的最后访问日期
conn.BeginTransaction();
conn.ExcuteQuerryByTran("update F01_Cert.Login set LastVisitTime=getdate() where LoginSessionID=@param0", new string[] { message.Session_ID });
conn.CommitTransaction();
}
return message;
}
else
{
throw new Exception("身份验证错误");
}
}
public static void UpdateFullText(string pstrWSID, Dbconn pconn)
{
string strSQL = @"
DECLARE @cols NVARCHAR(MAX)
SET @cols = ''
SELECT @cols = @cols + 'convert(varchar(1000),isnull(' + name
+ ',''''),21)+'' ''+'
FROM syscolumns
WHERE ( id = ( SELECT id
FROM sysobjects
WHERE ( name = 'WS0E_WSExtDoc' )
) )
SELECT @cols as A
";
string strInfoColumns = pconn.GetDataTableFirstValue(strSQL).ToString();
strSQL = strInfoColumns.Substring(0, strInfoColumns.Length - 1);
strSQL = "SELECT " + strSQL + " FROM [B01_MDM].[WS0E_WSExtDoc] WHERE WSID=@Param0";
string strValue = pconn.GetDataTableFirstValue(strSQL, new string[] { pstrWSID }).ToString();
strSQL = @"
DECLARE @cols NVARCHAR(MAX)
SET @cols = ''
SELECT @cols = @cols + 'convert(varchar(1000),isnull(' + name
+ ',''''),21)+'' ''+'
FROM syscolumns
WHERE ( id = ( SELECT id
FROM sysobjects
WHERE ( name = 'WS0M_WSMethodDoc' )
) )
SELECT @cols as A
";
string strFunctionColumns = pconn.GetDataTableFirstValue(strSQL).ToString();
strSQL = "SELECT " + strFunctionColumns.Substring(0, strFunctionColumns.Length - 1) + " AS A FROM [B01_MDM].[WS0M_WSMethodDoc] WHERE WSID=@Param0";
DataTable dt = pconn.GetDataTable(strSQL, new string[] { pstrWSID });
for (int i = 0; i < dt.Rows.Count; i++)
{
strValue += " " + dt.Rows[i]["A"].ToString();
}
strSQL = " SELECT 1 AS A FROM [B05_RPT].[WS0V_WSMethodView] WHERE WSID=@Param0";
if (pconn.GetDataTableRowCount(strSQL, new string[] { pstrWSID }) == 1)
{
pconn.ExcuteQuerryByTran("UPDATE [B05_RPT].[WS0V_WSMethodView] SET fulltext=@Param0 WHERE WSID=@PARAM1", new string[] { strValue, pstrWSID });
}
else
{
pconn.ExcuteQuerryByTran("INSERT INTO [B05_RPT].[WS0V_WSMethodView](fulltext,WSID)Values(@Param0,@PARAM1)", new string[] { strValue, pstrWSID });
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("AM");
DataSet ds = conn.GetDataSet(
@"SELECT DISTINCT t1.CN_S_EMITEM_ID AS AM_USER_ID ,
t1.CN_P_EMITEM_ID AS AM_S_Dept_ID
FROM TN_SYS_EM_USER t2 WITH ( NOLOCK )
LEFT JOIN TN_SYS_EM_RELATION t1 WITH ( NOLOCK ) ON t1.CN_S_EMITEM_ID = t2.CN_ID
WHERE 1 = 1
AND t2.CN_ISDELETE = 0
AND t2.CN_DISABLED = 0
AND t1.CN_REL_TYPE = 1
AND CN_S_EMITEM_TYPE=0
AND (
( CN_P_EMITEM_TYPE = 1
AND EXISTS ( SELECT 1
FROM TN_SYS_EM_GROUP t3 WITH ( NOLOCK )
WHERE T1.CN_P_EMITEM_ID = T3.cn_id
AND T3.CN_OWNERID = 0 )
)
OR
( CN_P_EMITEM_TYPE = 3
AND EXISTS ( SELECT 1
FROM TN_SYS_EM_VIEW T4 WITH ( NOLOCK )
WHERE 1 = 1
AND CN_VIEW_TYPE = 1
AND CN_OWNERID = 0
AND CN_P_EMITEM_ID = T4.cn_id )
)
)
");
Dbconn connMDM = new Dbconn("MDM");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("Am_User_Dept_log");
tableList.Add("Am_User_Dept");
connMDM.TableLock(tableList);
// //存入日志数据
// connMDM.ExcuteQuerryByTran(@"INSERT INTO dbo.Am_User_Dept_log
// ( Log_datetime ,
// AM_USER_ID,
// AM_S_Dept_ID ,
// CreateTime ,
// UpdateTime,
// Timestamps_old
//
// )
// SELECT GETDATE() ,
// AM_USER_ID,
// AM_S_Dept_ID ,
// CreateTime ,
// UpdateTime,
// convert(bigint,Timestamps)
// FROM Am_User_Dept");
//删除数据
connMDM.ExcuteQuerryByTran("DELETE FROM Am_User_Dept");
connMDM.InsertBYSQLBC("Am_User_Dept", ds.Tables[0]);
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("HZY_ITSM");
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 [Request_ID]
,[Request_Code]
,[Request_UserName]
,[Request_Date]
,[Request_Type]
,[Prod]
,[Memory]
,[CPU]
,[Disk_Space]
,[Request_Reason]
,[Request_RequireMent]
,[State]
,[OM_Opration]
,[OM_Opration_Name]
,[OM_Opration_Date]
,[Deleted]
,[timestamps]+0 timestamps ,
t2.Check_User CIO ,
t2.Check_Date CIO_Check_Date ,
t2.Remark AS CIO_Remark ,
t2.Check_Result AS CIO_Check_Result ,
t2.ID AS CIO_Check_ID ,
t3.Check_User OM ,
t3.Check_Date OM_Check_Date ,
t3.Check_Result AS OM_Check_Result ,
t3.ID AS OM_Check_ID ,
t3.Remark AS OM_Remark
FROM Server_Request WITH ( NOLOCK )
LEFT JOIN dbo.Check_List t2 ON Server_Request.Request_Code = t2.Key_ID
AND t2.Check_Type = 'Server_Request'
AND t2.seq_name = 'CIO'
LEFT JOIN dbo.Check_List t3 ON Server_Request.Request_Code = t3.Key_ID
AND t3.Check_Type = 'Server_Request'
AND t3.seq_name = 'OM'
WHERE " + strWHERE;
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("Server_Request");
tableList.Add("Check_List");
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 Server_Request SET Deleted=1
WHERE Request_Code='" + ds.Tables["LIST"].Rows[i]["Request_Code"].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 Server_Request WHERE Request_Code=@param0
AND Deleted=0 ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["Request_Code"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//运维操作
if (ds.Tables["LIST"].Rows[i]["OM_Opration"].ToString()!= "")
{
if (!dt.Columns.Contains("State"))
{
dt.Columns.Add("State");
}
if (!dt.Columns.Contains("OM_Opration_Date"))
{
dt.Columns.Add("OM_Opration_Date");
}
dt.Rows[0]["State"] = "已完成";
dt.Rows[0]["OM_Opration_Date"] =DateTime.Now;
ITSM_Common.SendAMMessage("", dt_SR.Rows[0]["Request_UserName"].ToString(), "您的申请单已经处理完毕", "您的申请单（" + dt_SR.Rows[0]["Request_Code"].ToString() + "）已经处理完毕，请进入EXCEL查看");
}
conn.Update("Server_Request", dt, "Request_Code='" + ds.Tables["LIST"].Rows[i]["Request_Code"].ToString() + @"'");
}
else //add
{
DataTable dtTemp = conn.GetDataTableInTrans(
@"SELECT MAX(Request_Code) AS MaxCode ,'SR'+CONVERT(VARCHAR(10),GETDATE(),112) AS PreCode FROM dbo.Server_Request
WHERE Request_Code LIKE 'SR'+CONVERT(VARCHAR(10),GETDATE(),112) + '%'");
string strCode = dtTemp.Rows[0]["MaxCode"].ToString();
if (strCode == "")
{
strCode = dtTemp.Rows[0]["PreCode"].ToString() + "001";
}
else
{
strCode = dtTemp.Rows[0]["PreCode"].ToString() + (Convert.ToInt32(strCode.Substring(strCode.Length - 3)) + 1).ToString("000");
}
if (!dt.Columns.Contains("State"))
{
dt.Columns.Add("State");
}
dt.Rows[0]["State"] = "已提交";
dt.Rows[0]["Request_Code"] = strCode;
conn.Insert("Server_Request", dt);
ITSM_Check_List_Edit checkList = new ITSM_Check_List_Edit();
string[] strColumn = new string[3];
string[] strValue = new string[3];
strColumn[0] = "Check_Type";
strColumn[1] = "Seq_Name";
strColumn[2] = "Key_ID";
strValue[0] = "Server_Request";
strValue[1] = "";
strValue[2] = strCode;
checkList.Request = Common.Common.GetRequestDataSet(strColumn,strValue);
checkList.Conn = conn;
checkList.ListAMSendList = listAMSendList;
checkList.Execute();
//listAMSendList = checkList.ListAMSendList;
}
}
}
conn.CommitTransaction();
for (int i = 0; i < listAMSendList.Count; i++)
{
string[] strList= (string[])(listAMSendList[i]);
ITSM_Common.SendAMMessage(strList[0], strList[1],strList[2]);
}
ds_Return = Common.Common.GetRequestDataSet(new string[]{"Result"},new string[]{"true"});
}
catch
{
conn.RollbackTransaction();
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
public bool Execute()
{
Dbconn conn = new Dbconn("iCollecter");
DataSet ds = conn.GetDataSet(
@" SELECT AMLoginName AS CN_LOGIN,TfgMachineNo,ComputerName
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY TfgMachineNo ORDER BY collectDate DESC ) AS RowIndex ,
ComputerName ,
AMLoginName,
TfgMachineNo
FROM dbo.AMAndTFG WITH(NOLOCK)
WHERE 1 = 1
AND ISNULL(AMLoginUsers, '') <> ''
) t
WHERE RowIndex = 1
");
Dbconn connMDM = new Dbconn("MDM");
//获取用户ID
ds.Tables[0].Columns.Add("AM_User_id", typeof(Int32));
ds.Tables[0].AcceptChanges();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string[] strParm = new string[1];
strParm[0]=ds.Tables[0].Rows[i]["CN_LOGIN"].ToString();
DataTable dt = connMDM.GetDataTable("SELECT AM_User_id FROM AM_User WHERE AM_login=@Param0", strParm);
if (dt.Rows.Count > 0)
{
ds.Tables[0].Rows[i]["AM_User_id"] = dt.Rows[0]["AM_User_id"];
}
else
{
ds.Tables[0].Rows[i].Delete();
}
}
ds.Tables[0].Columns.Remove("CN_LOGIN");
ds.Tables[0].AcceptChanges();
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("AM_TFG_Relation_log");
tableList.Add("AM_TFG_Relation");
connMDM.TableLock(tableList);
//存入日志数据
connMDM.ExcuteQuerryByTran(@"INSERT INTO dbo.AM_TFG_Relation_log
( Log_datetime ,
AM_User_id ,
TfgMachineNo ,
ComputerName ,
CreateTime ,
UpdateTime ,
Timestamps_old
)
SELECT GETDATE() ,
AM_User_id ,
TfgMachineNo ,
ComputerName ,
CreateTime ,
UpdateTime,
Convert(bigint,Timestamps)
FROM AM_TFG_Relation;");
//删除数据
connMDM.ExcuteQuerryByTran("DELETE FROM AM_TFG_Relation");
connMDM.Insert("AM_TFG_Relation", ds.Tables[0]);
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 = "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>
/// <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>
/// 根据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;
}
}
/// <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>
/// 根据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;
}
}
/// <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 strBatchID = Guid.NewGuid().ToString();
Dbconn conn = new Dbconn("MDM");
DataSet ds = conn.GetDataSet(
@"
SELECT CONVERT(UNIQUEIDENTIFIER,'" + strBatchID + @"') as BatchID
,[AM_User_id]
,[AM_login]
,[AM_User_Name]
,[AM_Password]
FROM [MDM].[dbo].[AM_User]
WHERE am_isdelete =0
");
Dbconn connVexSSO = new Dbconn("VEXSSO");
try
{
connVexSSO.BeginTransaction();
connVexSSO.InsertBYSQLBC("Tmp_AM_User", ds.Tables[0]);
string strSQL = @"
DECLARE @envid INT
SELECT @envid = Env_ID
FROM dbo.MDM_System_Env
WHERE Env_SN = 'AM'
SELECT [NewID] ,
[BatchID] ,
RIGHT('888888888888888' + CONVERT(VARCHAR(100), AM_User_id), 11) [AM_User_id] ,
[AM_login] ,
[AM_User_Name] ,
[AM_Password]
INTO #tmp
FROM [Tmp_AM_User]
WHERE batchID = '" + strBatchID + @"'
AND AM_login NOT IN ( SELECT App_UserName
FROM dbo.MDM_System_User
WHERE Env_ID = @envid
AND checked = 1 )
AND RIGHT('888888888888888' + CONVERT(VARCHAR(100), AM_User_id), 11) NOT IN ( SELECT VexSSOLoginUser
FROM B01_MDM.VEXSSOUser
)
DELETE FROM [dbo].[Tmp_AM_User]
WHERE batchID = '" + strBatchID + @"'
INSERT INTO B01_MDM.VEXSSOUser
( VexSSONewID ,
VexSSOLoginUser ,
VexSSOPassWord ,
VexSSOPassWordMd52 ,
Disabled
)
SELECT NEWID() ,
AM_User_id ,
AM_Password ,
right(sys.fn_VarBinToHexStr(hashbytes('MD5',Convert(varchar(100), right(sys.fn_VarBinToHexStr(hashbytes('MD5',Convert(varchar(100),AM_Password))),32)))),32) ,
0
FROM #tmp
INSERT INTO [VexSSO].[dbo].[MDM_System_User]
( [Env_ID]
,[VexSSONewID]
,[App_UserName]
,[App_PasswordSrc]
,[App_Password]
,[App_UserName_CN]
,[Chang_Password_Time]
,[Checked]
,[Check_User]
,[Check_Time])
SELECT @envid,VexSSONewID,AM_login,AM_Password,AM_Password,AM_User_Name,GETDATE(),1,'sysytem',GETDATE()
FROM #tmp
LEFT JOIN B01_MDM.VEXSSOUser ON AM_User_id = VexSSOLoginUser
INSERT INTO [B01_MDM].[VexSSOUserInfo]
([VexSSONewId],[Name],[CreateTime],[CreateUser],[UpdateTime],[UpdateUser])
SELECT VexSSONewID,AM_login ,GETDATE(),'system',GETDATE(),'system'
FROM #tmp
LEFT JOIN B01_MDM.VEXSSOUser ON AM_User_id = VexSSOLoginUser
DROP TABLE #tmp
";
connVexSSO.ExcuteQuerryByTran(strSQL);
connVexSSO.CommitTransaction();
return true;
}
catch
{
connVexSSO.RollbackTransaction();
throw;
}
}
