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