/// <summary>
/// 获取系统环境信息
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn = new Dbconn("MDM");
try
{
string strEnv_ID = m_request.Tables[0].Rows[0]["Env_ID"].ToString();
string strEnv_SN = m_request.Tables[0].Rows[0]["Env_SN"].ToString();
m_request.Tables[0].Columns.Remove("Env_ID");
ArrayList listTable = new ArrayList();
listTable.Add("MDM_System_Env");
conn.BeginTransaction();
conn.TableLock(listTable);
//核对环境简称是否存在
string strSQL = "SELECT 1 AS a FROM MDM_System_Env where Env_SN='" + strEnv_SN + "'";
if(strEnv_ID != "")
{
strSQL += " AND Env_ID !=" + strEnv_ID;
}
DataTable dtTemp = conn.GetDataTableInTrans(strSQL);
if (dtTemp.Rows.Count > 0)
{
throw new Exception(strEnv_SN + "已存在！");
}
if (strEnv_ID == "")
{
conn.Insert("MDM_System_Env", m_request.Tables[0]);
}
else
{
string strWhere = " Env_ID=" + strEnv_ID;
conn.Update("MDM_System_Env", m_request.Tables[0], strWhere);
}
conn.CommitTransaction();
return true;
}
catch
{
conn.RollbackTransaction();
throw;
}
}
/// <summary>
/// 根据XML内容更新到货计划字表
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
DataSet ds = new DataSet();
DataSet ds_Return = new DataSet();
DataSet dsRequest = new DataSet();
ArrayList listAMSendList = new ArrayList();
Dbconn conn = new Dbconn("GL_ERP");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "isnull(PRAD_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
string strWHERE1 = "isnull(PRAD_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
if (ds.Tables["LIST"].Rows[0]["Condition1"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE1 += " AND (" + ds.Tables["LIST"].Rows[0]["Condition1"].ToString().Trim() + ")";
}
}
strSQL = @"SELECT * FROM B02_Bill.PRAD_ArrivalPlanDetail WHERE " + strWHERE;
DataTable dt_SR = conn.GetDataTable(strSQL);
if (dt_SR.Rows.Count > 0)
{
strSQL = @"SELECT
[PRAD_CONO]
,[PRAD_DIVI]
,[PRAD_ArrivalPlanDetailId]
,[PRAD_PRA_ArrivalPlanId]
,[PRAD_PD0_ProductId]
,[PRAD_UnitID]
,[PRAD_PR1D_OrderDetailId]
,[PRAD_ArrivalDate]
,[PRAD_PerchaseTotal]
,[PRAD_ArrivalTotal]
,[PRAD_PlanTotal]
,[PRAD_PlanArrival]
,[PRAD_PlanArrivalAmount]
,[PRAD_TotalDiff]
,[PRAD_Status]
,[PRAD_RgUser]
,[PRAD_RgDt]
,[PRAD_LmUser]
,[PRAD_LmDt]
,[PRAD_UptNo]
FROM B02_Bill.PRAD_ArrivalPlanDetail
WHERE " + strWHERE;
}
else
{
strSQL = @"SELECT
[PRAD_CONO]
,[PRAD_DIVI]
,'' AS [PRAD_ArrivalPlanDetailId]
,[PRA_ArrivalPlanId]
,[PR1D_OrderDetailId]
,[PR1D_PD0_ProductId]
,[PR1D_UnitID]
,'' AS [PRAD_ArrivalDate]
,ISNULL([PR1D_Total],'0') AS [PR1D_Total]
,SUM(ISNULL(PRAD_PlanArrival,'0')) AS PRAD_PlanTotal
,SUM(ISNULL([PRAD_ArrivalTotal],'0')) as PRAD_ArrivalTotal
,'0' AS [PRAD_PlanArrival]
,'0' AS [PRAD_PlanArrivalAmount]
,'0' as [PRAD_TotalDiff]
,'' as [PRAD_Status]
,'' as [PRAD_RgUser]
,'' as [PRAD_RgDt]
,'' as [PRAD_LmUser]
,'' as [PRAD_LmDt]
,'0' as [PRAD_UptNo]
FROM B02_Bill.PRA_ArrivalPlan
LEFT JOIN B02_Bill.PR1D_OrderDetail ON PRA_PR1_OrderId=PR1D_PR1_OrderId
LEFT JOIN B02_Bill.PRAD_ArrivalPlanDetail ON PR1D_OrderDetailId=PRAD_PR1D_OrderDetailId
WHERE " + strWHERE1
+ @"GROUP BY [PRAD_CONO],[PRAD_DIVI] ,[PRA_ArrivalPlanId],[PR1D_PD0_ProductId]
,[PR1D_OrderDetailId],[PR1D_UnitID]
,[PR1D_Total]";
}
ds_Return = conn.GetDataSet(strSQL);
// }
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PRAD_ArrivalPlanDetail");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PRAD_ArrivalPlanDetail SET PRAD_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PRAD_PRA_ArrivalPlanId='" + ds.Tables["LIST"].Rows[i]["PRAD_PRA_ArrivalPlanId"].ToString() + @"'
AND iusnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.PRAD_ArrivalPlanDetail WHERE PRAD_ArrivalPlanDetailId=@param0
AND PRAD_Status<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PRAD_ArrivalPlanDetailId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["PRAD_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PRAD_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PRAD_LmDt"))
{
dt.Columns.Add("PRAD_LmDt");
}
if (!dt.Columns.Contains("PRAD_UptNo"))
{
dt.Columns.Add("PRAD_UptNo");
}
//if (!dt.Columns.Contains("PRA_Status"))
//{
// dt.Columns.Add("PRA_Status");
//}
//dt.Rows[0]["PRA_Status"] = '0';
dt.Rows[0]["PRA_LmDt"] = DateTime.Now;
dt.Rows[0]["PRA_UptNo"] = strCode;
}
conn.Update("B02_Bill.PRAD_ArrivalPlanDetail", dt, "PRAD_ArrivalPlanDetailId='" + ds.Tables["LIST"].Rows[i]["PRAD_ArrivalPlanDetailId"].ToString() + @"'");
strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal=BB.PR1_PurchaseTotal,
AA.PR1_PurchaseAmount=BB.PR1_PurchaseAmount,AA.PR1_POriginalAmount=BB.PR1_POriginalAmount,
AA.PR1_LmUser='******',
AA.PR1_LmDt='" + ds.Tables["LIST"].Rows[i]["PR1D_LmDt"].ToString() + @"',
AA.PR1_UptNo=AA.PR1D_UptNo+1
FROM
B02_Bill.PR1_Order AA,
(SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_PurchaseAmount,
SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_POriginalAmount,PR1D_PR1_OrderId
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
GROUP BY PR1D_PR1_OrderId)BB
WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
conn.ExcuteQuerryByTran(strSQL);
}
else //add
{
DataTable dtTemp = conn.GetDataTableInTrans(
@" SELECT " + ds.Tables["LIST"].Rows[i]["PRAD_PerchaseTotal"].ToString() + @" -
" + ds.Tables["LIST"].Rows[i]["PRAD_ArrivalTotal"].ToString() + @" -
" + ds.Tables["LIST"].Rows[i]["PRAD_PlanArrival"].ToString() + @" as PRAD_TotalDiff
");
string PRAD_TotalDiff = dtTemp.Rows[0]["PRAD_TotalDiff"].ToString();
DataTable dtAmot = conn.GetDataTableInTrans(
@" SELECT ISNULL(PR1D_POriginalPrice * " + ds.Tables["LIST"].Rows[i]["PRAD_PlanArrival"].ToString() + @",'0')
as PRAD_PlanArrivalAmount
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_OrderDetailId='" + ds.Tables["LIST"].Rows[i]["PRAD_PR1D_OrderDetailId"].ToString() + @"'
AND PR1D_PD0_ProductId='" + ds.Tables["LIST"].Rows[i]["PRAD_PD0_ProductId"].ToString() + @"'
");
string PRAD_PlanArrivalAmount = dtAmot.Rows[0]["PRAD_PlanArrivalAmount"].ToString();
string PRAD_ArrivalPlanDetailId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("PRAD_ArrivalPlanDetailId"))
{
dt.Columns.Add("PRAD_ArrivalPlanDetailId");
}
if (!dt.Columns.Contains("PRAD_RgDt"))
{
dt.Columns.Add("PRAD_RgDt");
}
if (!dt.Columns.Contains("PRAD_LmDt"))
{
dt.Columns.Add("PRAD_LmDt");
}
if (!dt.Columns.Contains("PRAD_UptNo"))
{
dt.Columns.Add("PRAD_UptNo");
}
if (!dt.Columns.Contains("PRAD_TotalDiff"))
{
dt.Columns.Add("PRAD_TotalDiff");
}
if (!dt.Columns.Contains("PRAD_PlanArrivalAmount"))
{
dt.Columns.Add("PRAD_PlanArrivalAmount");
}
dt.Rows[0]["PRAD_ArrivalPlanDetailId"] = PRAD_ArrivalPlanDetailId;
dt.Rows[0]["PRAD_RgDt"] = DateTime.Now;
dt.Rows[0]["PRAD_LmDt"] = DateTime.Now;
dt.Rows[0]["PRAD_TotalDiff"] = PRAD_TotalDiff;
dt.Rows[0]["PRAD_PlanArrivalAmount"] = PRAD_PlanArrivalAmount;
conn.Insert("B02_Bill.PRAD_ArrivalPlanDetail", dt);
strSQL = @"UPDATE AA SET AA.PRA_PlanTotal=BB.PRAD_PlanTotal,
AA.PRA_PlanAmount=BB.PRAD_PlanArrivalAmount,
AA.PRA_LmUser='******',
AA.PRA_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PRA_TotalDiff=AA.PRA_PerchaseTotal-AA.PRA_ArrivalTotal-BB.PRA_PlanTotal,
AA.PRA_AmountDiff=AA.PRA_PerchaseAmount-AA.PRA_ArrivalAmount-BB.PRA_PlanAmount,
AA.PRA_UptNo=AA.PRA_UptNo+1
FROM
PRA_ArrivalPlan AA,
(SELECT PRAD_PRA_ArrivalPlanId,SUM(ISNULL(PRAD_PlanArrival,'0')) AS PRAD_PlanTotal,
SUM(ISNULL(PRAD_PlanArrivalAmount,'0')) AS PRAD_PlanArrivalAmount
FROM B02_Bill.PRAD_ArrivalPlanDetail
WHERE PRAD_PRA_ArrivalPlanId=' " + ds.Tables["LIST"].Rows[i]["PRAD_PRA_ArrivalPlanId"].ToString() + @" '
GROUP BY PRAD_PRA_ArrivalPlanId)BB
WHERE AA.PRA_ArrivalPlanId=BB.PRAD_PRA_ArrivalPlanId";
conn.ExcuteQuerryByTran(strSQL);
// strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal=BB.PR1_PurchaseTotal,
// AA.PR1_PurchaseAmount=BB.PR1_PurchaseAmount,AA.PR1_POriginalAmount=BB.PR1_POriginalAmount,
// AA.PR1_LmUser='******',
// AA.PR1_LmDt='" + ds.Tables["LIST"].Rows[i]["PR1D_LmDt"].ToString() + @"',
// AA.PR1_UptNo=AA.PR1D_UptNo+1
// FROM
// B02_Bill.PR1_Order AA,
// (SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_PurchaseAmount,
// SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_POriginalAmount,PR1D_PR1_OrderId
// FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
// GROUP BY PR1D_PR1_OrderId)BB
// WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
// conn.ExcuteQuerryByTran(strSQL);
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 根据XML内容更新合同
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
DataSet ds = new DataSet();
DataSet ds_Return = new DataSet();
DataSet dsRequest = new DataSet();
ArrayList listAMSendList = new ArrayList();
Dbconn conn = new Dbconn("GL_ERP");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "ISNULL(PR0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @"SELECT
[PR0_CONO]
,[PR0_DIVI]
,[PR0_ContractId]
,[PR0_ContractCode]
,[PR0_SU0_SupplierId]
,CAST([PR0_Amount] AS DECIMAL(20,2)) as PR0_Amount
,[PR0_CurrencyId]
,[PR0_Description]
,[PR0_Status]
,[PR0_ECopyPath]
,[PR0_PrePayRate]
,LEFT(CONVERT(VARCHAR(20),[PR0_StartDate],121),10) AS PR0_StartDate
,LEFT(CONVERT(VARCHAR(20),[PR0_EndDate],121),10) AS PR0_EndDate
,[PR0_RgUser]
,[PR0_RgDt]
,[PR0_LmUser]
,[PR0_LmDt]
,[PR0_UptNo]
,[PR0_SettleAccount]
,[PR0_Total]
,A.[SU0_SupplierCode] as SU0_SupplierCode
,A.[SU0_SupplierName] as SU0_SupplierName
,B.WFT_NODE AS WFT_NODE1
,C.WFT_NODE AS WFT_NODE2
,D.WFT_NODE AS WFT_NODE3
,E.WFT_NODE AS WFT_NODE4
,F.WFT_NODE AS WFT_NODE5
,L.DC0_DICTNAME1 AS DC0_DICTNAME1
,M.DC0_DICTNAME1 AS DC0_DICTNAME2
,N.DC0_DICTNAME1 AS DC0_DICTNAME3
,O.DC0_DICTNAME1 AS DC0_DICTNAME4
,P.DC0_DICTNAME1 AS DC0_DICTNAME5
,G.WFP_AuditTime AS WFP_AuditTime1
,H.WFP_AuditTime AS WFP_AuditTime2
,I.WFP_AuditTime AS WFP_AuditTime3
,J.WFP_AuditTime AS WFP_AuditTime4
,K.WFP_AuditTime AS WFP_AuditTime5
,G.WFP_AFID AS WFP_AFID1
,H.WFP_AFID AS WFP_AFID2
,I.WFP_AFID AS WFP_AFID3
,J.WFP_AFID AS WFP_AFID4
,K.WFP_AFID AS WFP_AFID5
,Q.[DC0_DictName1] AS CurrencyDictName
,R.[DC0_DictName1] AS StatusDictName
,S.[DC0_DictName1] AS AccountDictName
FROM B02_Bill.PR0_Contract WITH ( NOLOCK )
LEFT JOIN B01_MDM.SU0_Supplier A ON PR0_SU0_SupplierId=A.SU0_SupplierId
LEFT JOIN M02_BPM.WFT_AuditFlowDefine B ON B.WFT_Type='Contract' and B.WFT_Step='1'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine C ON C.WFT_Type='Contract' and C.WFT_Step='2'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine D ON D.WFT_Type='Contract' and D.WFT_Step='3'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine E ON E.WFT_Type='Contract' and E.WFT_Step='4'
LEFT JOIN M02_BPM.WFT_AuditFlowDefine F ON F.WFT_Type='Contract' and F.WFT_Step='5'
LEFT JOIN M02_BPM.WFP_AuditFlow G ON PR0_ContractId=G.WFP_BillId AND G.WFP_WFT_AFDId=B.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow H ON PR0_ContractId=H.WFP_BillId AND H.WFP_WFT_AFDId=C.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow I ON PR0_ContractId=I.WFP_BillId AND I.WFP_WFT_AFDId=D.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow J ON PR0_ContractId=J.WFP_BillId AND J.WFP_WFT_AFDId=E.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow K ON PR0_ContractId=K.WFP_BillId AND K.WFP_WFT_AFDId=F.WFT_AFDId
LEFT JOIN M01_CONF.DC0_Dictionary L ON G.WFP_AFResultId=L.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary M ON H.WFP_AFResultId=M.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary N ON I.WFP_AFResultId=N.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary O ON J.WFP_AFResultId=O.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary P ON K.WFP_AFResultId=P.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary Q ON PR0_CurrencyId=Q.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary R ON PR0_Status=R.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary S ON PR0_SettleAccount=S.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [PR0_LmDt] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PR0_Contract");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PR0_Contract SET PR0_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PR0_ContractId='" + ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() + @"'
AND ISNULL(PR0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.PR0_Contract WHERE PR0_ContractId=@param0
AND ISNULL(PR0_Status,'') <>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
strSQL = @"SELECT * FROM M02_BPM.WFP_AuditFlow WHERE WFP_BillId=@param0
AND WFP_AFResultId<>''
";
DataTable dt_CO = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() });
//update
if (dt_CO.Rows.Count > 0)
{
throw new Exception("合同已审核，不能进行修改！");
}
else
//处理结果
if (ds.Tables["LIST"].Rows[i]["PR0_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PR0_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PR0_LmDt"))
{
dt.Columns.Add("PR0_LmDt");
}
// if (!dt.Columns.Contains("PR0_Status"))
// {
// dt.Columns.Add("PR0_Status");
// }
if (!dt.Columns.Contains("PR0_UptNo"))
{
dt.Columns.Add("PR0_UptNo");
}
// dt.Rows[0]["PR0_Status"] = '0';
dt.Rows[0]["PR0_LmDt"] = DateTime.Now;
dt.Rows[0]["PR0_UptNo"] = strCode;
}
conn.Update("B02_Bill.PR0_Contract", dt, "PR0_ContractId='" + ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() + @"'");
}
else //add
{
DataTable dtTemp = conn.GetDataTableInTrans(
@"SELECT ISNULL(MAX(PR0_ContractCode),'') AS MaxCode,
RIGHT(left( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2)+'-'+
LEFT(RIGHT( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2) as PreCode FROM B02_Bill.PR0_Contract where
left(PR0_ContractCode,'2')=RIGHT(left( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2)
AND left(right(PR0_ContractCode,'4'),'2')=LEFT(RIGHT( CONVERT(VARCHAR(100),DateAdd(m,+0,GETDATE()),112),4),2) ");
//供应商编号生成
string strCode = dtTemp.Rows[0]["MaxCode"].ToString();
if (strCode == "")
{
strCode = dtTemp.Rows[0]["PreCode"].ToString() + "01";
// strCode = strCode.Substring(strCode.Substring(DateTime.Now.ToString().Length + 4).Length + 4)
// RIGHT(left(DateTime.Now.ToString(), 4), 2) + "-" + RIGHT(left(DateTime.Now.ToString(), 7), 2) + "01";
//dtTemp.Rows[0]["PreCode"].ToString() + "0001";
}
else
{
strCode = dtTemp.Rows[0]["PreCode"].ToString() + (Convert.ToInt32(strCode.Substring(strCode.Length - 2)) + 1).ToString("00");
//strCode = left(strCode, 2) + "-" + left(RIGHT(strCode, 4), 2) + (Convert.ToInt32(strCode.Substring(strCode.Length - 2)) + 1).ToString("00");
// dtTemp.Rows[0]["PreCode"].ToString() + (Convert.ToInt32(strCode.Substring(strCode.Length - 4)) + 1).ToString("0000");
}
string PR0_ContractId = Guid.NewGuid().ToString();
if (!dt.Columns.Contains("PR0_ContractId"))
{
dt.Columns.Add("PR0_ContractId");
}
// if (!dt.Columns.Contains("PR0_Status"))
// {
// dt.Columns.Add("PR0_Status");
// }
if (!dt.Columns.Contains("PR0_RgDt"))
{
dt.Columns.Add("PR0_RgDt");
}
if (!dt.Columns.Contains("PR0_LmDt"))
{
dt.Columns.Add("PR0_LmDt");
}
if (!dt.Columns.Contains("PR0_ContractCode"))
{
dt.Columns.Add("PR0_ContractCode");
}
if (!dt.Columns.Contains("PR0_UptNo"))
{
dt.Columns.Add("PR0_UptNo");
}
dt.Rows[0]["PR0_ContractId"] = PR0_ContractId;
// dt.Rows[0]["PR0_Status"] = '0';
dt.Rows[0]["PR0_RgDt"] = DateTime.Now;
dt.Rows[0]["PR0_LmDt"] = DateTime.Now;
dt.Rows[0]["PR0_ContractCode"] = strCode;
dt.Rows[0]["PR0_UptNo"] = '0';
conn.Insert("B02_Bill.PR0_Contract", dt);
GL_ERP_Common clsCommon = new GL_ERP_Common();
GL_ERP_Common.CreateWorkFlow(conn, "Contract", ds.Tables["LIST"].Rows[i]["PR0_CONO"].ToString(), ds.Tables["LIST"].Rows[i]["PR0_DIVI"].ToString(), PR0_ContractId,
"Contract", "", "", ds.Tables["LIST"].Rows[i]["PR0_Status"].ToString(), ds.Tables["LIST"].Rows[i]["PR0_RgUser"].ToString(),
ds.Tables["LIST"].Rows[i]["PR0_LmUser"].ToString(), "", "PR0_ContractId", "B02_Bill.PR0_Contract");
}
}
}
conn.CommitTransaction();
ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" });
}
catch
{
conn.RollbackTransaction();
//事物结束
throw;
}
}
return ds_Return;
}
catch
{
throw;
}
}
/// <summary>
/// 根据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("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;
}
}
/// <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;
}
}