using System; using System.Data; using System.Windows.Forms; using SAP.Middleware.Connector; namespace Thinkape { public partial class HXSAP : Form { public HXSAP() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { NewMethod1(); } /// <summary> /// 获取sap的数据 /// </summary> private void NewMethod1() { RfcDestination dest; RfcRepository rfcrep; NewMethod(out dest, out rfcrep); IRfcFunction myfun = null; //获取sap的数据 myfun = rfcrep.CreateFunction("ZFK_WERKS_TRAN");//凭证函数 myfun.Invoke(dest); //ITAB sap系统表名 IRfcTable retb = myfun.GetTable("ITAB"); string INBILLNO = ""; string INBILLNO2 = ""; prog.Maximum = retb.RowCount * 2; prog.Value = 0; textBox1.Text = ""; if (retb.RowCount == 0) { textBox1.Text = "暂时没有数据!无需导入..."; } else { for (int i = 0; i < retb.RowCount; i++) { retb.CurrentIndex = i; //失败的二次发送 string zbudn= retb.CurrentRow.GetValue("ZBUDN").ToString (); string ZITEM_NO = retb.CurrentRow.GetValue("ZITEM_NO").ToString(); //根据ZITEM_NO,ZBUDN 判断,ZITEM_NO(项目)+ZBUDN(门店预算号),这个组合才是唯一的 string str_con = " select m.BillNo from sap_detail d left join sap_main m on d.billid=m.billid where d.ZBUDN='" + zbudn + "' and d.ZITEM_NO='" + ZITEM_NO + "' and Isdiscard is null "; DataSet resultSecond = SapVoucher.SAPinsert.GetDataSet(str_con); if (resultSecond.Tables[0].Rows.Count > 0) { string BillNO = resultSecond.Tables[0].Rows[0][0].ToString(); INBILLNO += "'" + BillNO + "',"; } else { string BillNO = GetBillNO(); INBILLNO += "'" + BillNO + "',"; INBILLNO2 += "" + BillNO + ","; string sql = string.Format(@"INSERT INTO sapmain( BillID,billno,billdate,MANDT,ZBUDN,ZITEM_NO,WERKS,ZDATE,ZDEPT,ZFKACC,ZNETPR, ZJG,ZQTY,ZSTS_TR,ZERNAM,ZERDAT,ZERTIM,ZERPRG,ZUPNAM,ZUPDAT,ZUPTIM,ZUPPRG,CurrentProgramID,Create_Date,HsCompanyID) VALUES((SELECT CAST(ISNULL( MAX(BillID),0) AS INT)+1 FROM sapmain),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}',132102,'" + DateTime.Now + "',(SELECT HsCompanyID FROM sys_dept WHERE cDepCode='{21}'))", BillNO, retb.CurrentRow.GetValue("ZBUDAT"), retb.CurrentRow.GetValue("MANDT"), retb.CurrentRow.GetValue("ZBUDN"), retb.CurrentRow.GetValue("ZITEM_NO"), retb.CurrentRow.GetValue("WERKS"), retb.CurrentRow.GetValue("ZBUDAT"), retb.CurrentRow.GetValue("ZDEPT"), retb.CurrentRow.GetValue("ZFKACC"), retb.CurrentRow.GetValue("ZSTAJG"), retb.CurrentRow.GetValue("ZJG"), retb.CurrentRow.GetValue("ZQTY"), retb.CurrentRow.GetValue("ZSTS_TR"), retb.CurrentRow.GetValue("ZERNAM"), retb.CurrentRow.GetValue("ZERDAT"), retb.CurrentRow.GetValue("ZERTIM"), retb.CurrentRow.GetValue("ZERPRG"), retb.CurrentRow.GetValue("ZUPNAM"), retb.CurrentRow.GetValue("ZUPDAT"), retb.CurrentRow.GetValue("ZUPTIM"), retb.CurrentRow.GetValue("ZUPPRG"), retb.CurrentRow.GetValue("ZDEPT")); //新增字表数据 string sqlZB = string.Format("INSERT INTO sapdetail(BillID,BillMoney,DeptID,CostTypeID,ZJG,ZBUDN,ZITEM_NO,ZERDAT,FyType,ZDEPT,ZFKACC,WERKS) VALUES((SELECT CAST(ISNULL( MAX(BillID),0) AS INT)+1 FROM sapmain),'{0}',(SELECT iDepID FROM dbo.Sys_Dept WHERE cDepCode='{1}'),(select CostID from EB_CostType where CostCode ='{2}'),{3},'{4}','{5}','{6}','公司承担','{7}','{8}','{9}');", retb.CurrentRow.GetValue("ZJG"), retb.CurrentRow.GetValue("ZDEPT"), retb.CurrentRow.GetValue("ZFKACC"), retb.CurrentRow.GetValue("ZSTAJG"), retb.CurrentRow.GetValue("ZBUDN"), retb.CurrentRow.GetValue("ZITEM_NO"), retb.CurrentRow.GetValue("ZERDAT"), retb.CurrentRow.GetValue("ZDEPT"), retb.CurrentRow.GetValue("ZFKACC"), retb.CurrentRow.GetValue("WERKS")); SapVoucher.SAPinsert.InsertSAP(sqlZB); string msg = SapVoucher.SAPinsert.InsertSAP(sql); } } //合并单据 //string hbsql = string.Format("EXEC HX_SAPHB @billNO='{0}'", INBILLNO2); //DataSet s = SapVoucher.SAPinsert.GetDataSet(hbsql); // string sqlll = s.Tables[0].Rows[0][0].ToString(); //DataSet sss = SapVoucher.SAPinsert.GetDataSet(sqlll); //提交单据 INBILLNO = INBILLNO.Substring(0, INBILLNO.Length - 1); string INBILLNOsql = string.Format("SELECT BillID,* FROM sap_main WHERE BillNo IN({0})", INBILLNO); DataSet datads = SapVoucher.SAPinsert.GetDataSet(INBILLNOsql); double f = 0; foreach (DataRow a in datads.Tables[0].Rows ) { string tempsql = string.Format(" select billmoney from sap_detail where billid='{0}'", a[0]); DataSet tempdatads = SapVoucher.SAPinsert.GetDataSet(tempsql); foreach (DataRow b in tempdatads.Tables[0].Rows) { f += Convert.ToDouble(b[0]); } string updatesql = string.Format(" update sap_main set ZJG='{1}' where billid='{0}'", a[0],f); SapVoucher.SAPinsert.GetCount(updatesql); f = 0; } foreach (DataRow dr in datads.Tables[0].Rows) { ///未审核单据才提交扣减 string str_status = "select flowstatus from sap_Main where billNO= '" + dr["billNO"]+"'"; DataSet ds_status = SapVoucher.SAPinsert.GetDataSet(str_status); if (ds_status.Tables[0].Rows[0][0].ToString() == "0" || ds_status.Tables[0].Rows[0][0].ToString() == "") { #region 提交单据 //提交单据 string procsql = string.Format(@"declare @result varchar(8000); exec Sys_WFSubmit @ProgramID = 132102,@PrimaryKey ={0}, @BillNo = '{1}',@Uid = 7,@AppOpinion = '', @AppTerminal = 'PC',@FlowCode = 'submit',@SimSubmit = 0,@result = @result output; select @result as result; ", dr["BillID"].ToString(), dr["BillNo"].ToString()); DataSet result = SapVoucher.SAPinsert.GetDataSet(procsql); //预算是否充足 string str_param = "select m.BillNo,d.zdept,d.zfkacc,year(d.zerdat) as zerdat,c.CostName,de.cDepName from " + " sap_detail d left join sap_main m on d.billid=m.billid " + " left join Sys_Dept de on de.cDepCode=d.zdept left join EB_CostType c on c.CostCode=d.zfkacc " + " where m.BillNo='" + dr["BillNo"].ToString() + "'"; DataSet ds_param = SapVoucher.SAPinsert.GetDataSet(str_param); //预算扣减 string year = ds_param.Tables[0].Rows[0][3].ToString(); string cdepcode = ds_param.Tables[0].Rows[0][1].ToString(), costcode = ds_param.Tables[0].Rows[0][2].ToString(); string CostName = ds_param.Tables[0].Rows[0][4].ToString(), cDepName = ds_param.Tables[0].Rows[0][5].ToString(); if (result.Tables[0].Rows[0][0].ToString() == "ok") { textBox1.Text += dr["BillNo"].ToString() + "扣减预算成功!"; textBox1.Text += "\r\n\r\n"; string con_AdequacyOfBudget = "select sum(ISNULL(budmoney,0)-ISNULL(usedmoney,0)) as " + "money from (select b.*,(SELECT CostCode FROM dbo.EB_CostType WHERE EB_CostType.CostID=b.CostID) AS " + " CostCode from BudPlan b left join Sys_dept s on b.deptid=s.idepId where s.cDepCode='" + cdepcode + "') total " + " where total. CostCode='" + costcode + "' and year(begindate) ='" + year + "' group by year(begindate)"; DataSet ds_AdequacyOfBudget = SapVoucher.SAPinsert.GetDataSet(con_AdequacyOfBudget); try { if (Convert.ToDouble(ds_AdequacyOfBudget.Tables[0].Rows[0][0].ToString().Trim()) > 0) { //修改单据状态 string SQL2 = string.Format("UPDATE sap_main SET ZRETN='{0}',ZSTS_BU='Y',FlowStatus='3' WHERE billno='{1}'", dr["BillNo"].ToString(), dr["BillNo"].ToString()); SapVoucher.SAPinsert.GetCount(SQL2); } else { string SQL2 = string.Format("UPDATE sap_main SET ZRETN='{0}',ZSTS_BU='N',FlowStatus='3' WHERE billno='{1}'", dr["BillNo"].ToString(), dr["BillNo"].ToString()); SapVoucher.SAPinsert.GetCount(SQL2); } } catch { string SQL2 = string.Format("UPDATE sap_main SET ZRETN='{0}',ZSTS_BU='N',FlowStatus='3' WHERE billno='{1}'", dr["BillNo"].ToString(), dr["BillNo"].ToString()); SapVoucher.SAPinsert.GetCount(SQL2); } } else { textBox1.Text += dr["BillNo"].ToString() + "扣减预算失败!"; textBox1.Text += "\r\n\r\n"; //修改当前数据的状态result.Tables[0].Rows[0][0].ToString() string str_dept = "select cDepName,* from Sys_Dept where cDepCode='" + cdepcode + "'"; string str_class = ""; // string SQL2 = string.Format("UPDATE sap_main SET ZREASON='{0}',ZRETN='{2}' WHERE billno='{1}'", "" + cDepName + "(部门)" + CostName + "(科目)为预算受控项,请先导入预算 ", dr["BillNo"].ToString(), dr["BillNo"].ToString()); //result string reson = result.Tables[0].Rows[0][0].ToString(); reson = ReplaceHtmlTag(reson); // string temp = reson.Substring(reson.IndexOf('<')+1, reson.IndexOf('<') - reson.IndexOf('<')); // reson += "为预算受控项,请先导入预算"; string SQL2 = string.Format("UPDATE sap_main SET ZREASON='{0}',ZRETN='{2}',FlowStatus='3' WHERE billno='{1}'", "", dr["BillNo"].ToString(), dr["BillNo"].ToString()); SapVoucher.SAPinsert.GetCount(SQL2); prog.Value = prog.Value + 1; } #endregion } } if (INBILLNO.Length > 0) { //查询费控单据 string INBILLNOsql1 = string.Format("SELECT * FROM sap_main WHERE BillNo IN({0})", INBILLNO); DataSet datads1 = SapVoucher.SAPinsert.GetDataSet(INBILLNOsql1); IRfcFunction myfu = null; myfu = rfcrep.CreateFunction("ZFK_WERKS_CAL");//凭证函数 IRfcTable Itb_Money = myfu.GetTable("ITAB"); int ii = 0; foreach (DataRow dr in datads1.Tables[0].Rows) { string sql11 = "SELECT * FROM sap_detail WHERE BillID=" + dr["BillID"].ToString(); DataSet ds = SapVoucher.SAPinsert.GetDataSet(sql11); foreach (DataRow ds1 in ds.Tables[0].Rows) { string str_Dimentions = "select count(*) from BudSolutionMap b left join Sys_dept s on b.deptid =s.iDepID left join EB_CostType e on e.CostID=b.CostID where s.cDepCode='" + ds1["ZDEPT"].ToString() + "' and e.CostCode='" + ds1["ZFKACC"].ToString() + "'"; DataSet ds_Dimentions = SapVoucher.SAPinsert.GetDataSet(str_Dimentions); string con_AdequacyOfBudget = "select sum(ISNULL(budmoney,0)-ISNULL(usedmoney,0)) as " + "money from (select b.*,(SELECT CostCode FROM dbo.EB_CostType WHERE EB_CostType.CostID=b.CostID) AS " + " CostCode from BudPlan b left join Sys_dept s on b.deptid=s.idepId where s.cDepCode='" + ds1["ZDEPT"].ToString() + "') total " + " where total. CostCode='" + ds1["ZFKACC"].ToString() + "' and year(begindate) ='" + Convert.ToDateTime(ds1["ZERDAT"].ToString()).Year + "' group by year(begindate)"; DataSet ds_AdequacyOfBudget = SapVoucher.SAPinsert.GetDataSet(con_AdequacyOfBudget); double Budget = -9.9; double tag=0; double tag1 = 0; try { Budget=Convert.ToDouble(ds_AdequacyOfBudget.Tables[0].Rows[0][0].ToString().Trim()) ; }catch { tag=1; } if (Convert.ToInt32(ds_Dimentions.Tables[0].Rows[0][0].ToString()) > 0) { if (tag == 1) { string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='N' WHERE BillDetailID='{0}'", ds1["BillDetailID"]); SapVoucher.SAPinsert.GetCount(SQL2); tag1 = 1; } else { if (Budget > 0) { string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='Y' WHERE BillDetailID='{0}'", ds1["BillDetailID"]); SapVoucher.SAPinsert.GetCount(SQL2); tag1 = 3; } else { string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='N' WHERE BillDetailID='{0}'", ds1["BillDetailID"]); SapVoucher.SAPinsert.GetCount(SQL2); tag1 = 3; } } } else { if (tag == 1) { string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='', ZREASON='预算维度和预算数据 不存在' WHERE BillDetailID='{0}'", ds1["BillDetailID"]); SapVoucher.SAPinsert.GetCount(SQL2); tag1 = 2; } else { if (Budget > 0) { string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='Y' WHERE BillDetailID='{0}'", ds1["BillDetailID"]); SapVoucher.SAPinsert.GetCount(SQL2); tag1 = 3; } else { string SQL2 = string.Format("UPDATE sap_detail SET ZSTS_BU='N' WHERE BillDetailID='{0}'", ds1["BillDetailID"]); SapVoucher.SAPinsert.GetCount(SQL2); tag1 = 3; } } } string sql11temp = "SELECT * FROM sap_detail WHERE BillDetailID=" + ds1["BillDetailID"]; DataSet dstemp = SapVoucher.SAPinsert.GetDataSet(sql11temp); ii++; Itb_Money.Insert(); if (Convert.ToInt32(ds.Tables[0].Rows.Count) > 1) { Itb_Money.CurrentRow.SetValue("ZDEPT", ds1["ZDEPT"].ToString()); Itb_Money.CurrentRow.SetValue("ZFKACC", ds1["ZFKACC"].ToString()); Itb_Money.CurrentRow.SetValue("ZBUDN", ds1["ZBUDN"].ToString()); Itb_Money.CurrentRow.SetValue("ZITEM_NO", ds1["ZITEM_NO"].ToString()); } else { Itb_Money.CurrentRow.SetValue("ZDEPT", dr["ZDEPT"].ToString()); Itb_Money.CurrentRow.SetValue("ZFKACC", dr["ZFKACC"].ToString()); Itb_Money.CurrentRow.SetValue("ZBUDN", dr["ZBUDN"].ToString()); Itb_Money.CurrentRow.SetValue("ZITEM_NO", dr["ZITEM_NO"].ToString()); } Itb_Money.CurrentRow.SetValue("WERKS", ds1["WERKS"].ToString()); Itb_Money.CurrentRow.SetValue("ZBUDAT", dr["ZDATE"].ToString()); Itb_Money.CurrentRow.SetValue("ZSTAJG", dr["ZNETPR"].ToString()); Itb_Money.CurrentRow.SetValue("ZJG", dr["ZJG"].ToString()); Itb_Money.CurrentRow.SetValue("ZQTY", dr["ZQTY"].ToString()); Itb_Money.CurrentRow.SetValue("ZSTS_TR", dr["ZSTS_TR"].ToString()); Itb_Money.CurrentRow.SetValue("ZRETN", dr["ZRETN"].ToString()); if (tag1 == 2) { string SQLdept = string.Format(" select * from Sys_dept where cDepCode= (select zdept from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]); int dept_count = SapVoucher.SAPinsert.GetDataSet(SQLdept).Tables [0].Rows .Count ; if (dept_count > 0) { Itb_Money.CurrentRow.SetValue("ZSTS_BU", ""); Itb_Money.CurrentRow.SetValue("ZREASON", "预算维度和预算数据 不存在"); } else { Itb_Money.CurrentRow.SetValue("ZSTS_BU", ""); Itb_Money.CurrentRow.SetValue("ZREASON", "找不到此部门"); Itb_Money.CurrentRow.SetValue("ZRETN", ""); string SQLdiscard = string.Format(" update sap_main set isdiscard='1' where billid=( select BillID from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]); int discard_count = SapVoucher.SAPinsert.GetCount (SQLdiscard); } string SQLcost = string.Format(" select * from EB_CostType where CostCode= (select zfkacc from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]); int cost_count = SapVoucher.SAPinsert.GetCount(SQLcost); if (cost_count > 0) { Itb_Money.CurrentRow.SetValue("ZSTS_BU", ""); Itb_Money.CurrentRow.SetValue("ZREASON", "预算维度和预算数据 不存在"); } else { Itb_Money.CurrentRow.SetValue("ZSTS_BU", ""); Itb_Money.CurrentRow.SetValue("ZREASON", "找不到此科目"); Itb_Money.CurrentRow.SetValue("ZRETN", ""); string SQLdiscard = string.Format(" update sap_main set isdiscard='1' where billid=( select BillID from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]); int discard_count = SapVoucher.SAPinsert.GetCount(SQLdiscard); } } else { string SQLdept = string.Format(" select * from Sys_dept where cDepCode= (select zdept from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]); int dept_count = SapVoucher.SAPinsert.GetDataSet(SQLdept).Tables[0].Rows.Count; ; if(dept_count>0) { Itb_Money.CurrentRow.SetValue("ZSTS_BU", dstemp.Tables [0].Rows[0]["ZSTS_BU"].ToString()); Itb_Money.CurrentRow.SetValue("ZREASON", dstemp.Tables [0].Rows [0]["ZREASON"].ToString()); } else { Itb_Money.CurrentRow.SetValue("ZSTS_BU", ""); Itb_Money.CurrentRow.SetValue("ZREASON", "找不到此部门"); Itb_Money.CurrentRow.SetValue("ZRETN", ""); string SQLdiscard = string.Format(" update sap_main set isdiscard='1' where billid=( select BillID from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]); int discard_count = SapVoucher.SAPinsert.GetCount(SQLdiscard); } string SQLcost = string.Format(" select * from EB_CostType where CostCode= (select zfkacc from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]); int cost_count = SapVoucher.SAPinsert.GetDataSet(SQLcost).Tables[0].Rows.Count; ; if (cost_count > 0) { Itb_Money.CurrentRow.SetValue("ZSTS_BU", dstemp.Tables [0].Rows[0]["ZSTS_BU"].ToString()); Itb_Money.CurrentRow.SetValue("ZREASON", dstemp.Tables [0].Rows [0]["ZREASON"].ToString()); } else { Itb_Money.CurrentRow.SetValue("ZSTS_BU", ""); Itb_Money.CurrentRow.SetValue("ZREASON", "找不到此科目"); Itb_Money.CurrentRow.SetValue("ZRETN", ""); string SQLdiscard = string.Format(" update sap_main set isdiscard='1' where billid=( select BillID from sap_detail WHERE BillDetailID='{0}')", ds1["BillDetailID"]); int discard_count = SapVoucher.SAPinsert.GetCount(SQLdiscard); } } Itb_Money.CurrentRow.SetValue("ZERNAM", dr["ZERNAM"].ToString()); Itb_Money.CurrentRow.SetValue("ZERDAT", dr["ZERDAT"].ToString()); Itb_Money.CurrentRow.SetValue("ZERTIM", dr["ZERTIM"].ToString()); Itb_Money.CurrentRow.SetValue("ZERPRG", dr["ZERPRG"].ToString()); Itb_Money.CurrentRow.SetValue("ZUPNAM", dr["ZUPNAM"].ToString()); Itb_Money.CurrentRow.SetValue("ZUPDAT", dr["ZUPDAT"].ToString()); Itb_Money.CurrentRow.SetValue("ZUPTIM", dr["ZUPTIM"].ToString()); Itb_Money.CurrentRow.SetValue("ZUPPRG", dr["ZUPPRG"].ToString()); textBox1.Text += dr["ZRETN"].ToString() + "操作成功!"; textBox1.Text += "\r\n\r\n"; } } myfu.Invoke(dest);//提交 prog.Value = prog.Maximum; MessageBox.Show("一共返回" + ii + "条数据!"); SapVoucher.SAPinsert.GetCount(" exec Bud_RefreshAct "); NewMethod2(); } } } public static string ReplaceHtmlTag(string html, int length = 0) { string strText = System.Text.RegularExpressions.Regex.Replace(html, "<[^>]+>", ""); strText = System.Text.RegularExpressions.Regex.Replace(strText, "&[^;]+;", ""); if (length > 0 && strText.Length > length) return strText.Substring(0, length); return strText; } /// <summary> /// 链接SAP的配置 /// </summary> /// <param name="dest"></param> /// <param name="rfcrep"></param> private static void NewMethod(out RfcDestination dest, out RfcRepository rfcrep) { RfcConfigParameters rfcPar = new RfcConfigParameters(); rfcPar.Add(RfcConfigParameters.Name, "Conn"); rfcPar.Add(RfcConfigParameters.AppServerHost, "192.168.2.195"); rfcPar.Add(RfcConfigParameters.Client, "800"); rfcPar.Add(RfcConfigParameters.User, "THINKAPEUSER"); rfcPar.Add(RfcConfigParameters.Password, "Th123456"); rfcPar.Add(RfcConfigParameters.SystemNumber, "00"); rfcPar.Add(RfcConfigParameters.Language, "zh"); dest = RfcDestinationManager.GetDestination(rfcPar); rfcrep = dest.Repository; } /// <summary> /// 生成流水号 /// </summary> /// <param name="i"></param> /// <returns></returns> public static string GetBillNO() { string result = string.Empty; string prefix = "WFL"; string date = DateTime.Now.ToString("yyyyMMdd"); string sql = string.Format("SELECT TOP 1 BillNo FROM dbo.sapMain WHERE BillNo LIKE '%{0}%' and ISNULL(IsDiscard,0)!=1 ORDER BY BillID DESC", date); DataSet datads = SapVoucher.SAPinsert.GetDataSet(sql); if (datads.Tables[0].Rows.Count > 0) { string billno = datads.Tables[0].Rows[0][0].ToString(); billno = billno.Substring(3, billno.Length - 3); result = prefix + (Convert.ToInt64(billno) + 1).ToString(); } else { result = prefix + date + "0001"; } return result; } private void button2_Click(object sender, EventArgs e) { RfcDestination dest; RfcRepository rfcrep; NewMethod(out dest, out rfcrep); IRfcFunction myfun = null; myfun = rfcrep.CreateFunction("ZFK_WERKS_TRAN");//凭证函数 myfun.Invoke(dest); IRfcTable retb = myfun.GetTable("ITAB"); prog.Maximum = retb.RowCount; prog.Value = 0; textBox1.Text = ""; if (retb.RowCount == 0) { textBox1.Text = "暂时没有数据!"; } for (int i = 0; i < retb.RowCount; i++) { retb.CurrentIndex = i; prog.Value = Convert.ToInt32(prog.Value) + 1; textBox1.Text += retb.CurrentRow.GetValue("ZITEM_NO"); textBox1.Text += retb.CurrentRow[1]; textBox1.Text += retb.CurrentRow[2]; textBox1.Text += retb.CurrentRow[3]; textBox1.Text += retb.CurrentRow[4]; textBox1.Text += retb.CurrentRow[5]; textBox1.Text += retb.CurrentRow[6]; textBox1.Text += retb.CurrentRow[7]; textBox1.Text += retb.CurrentRow[8]; textBox1.Text += retb.CurrentRow[9]; textBox1.Text += retb.CurrentRow[10]; textBox1.Text += retb.CurrentRow[11]; textBox1.Text += retb.CurrentRow[12]; textBox1.Text += retb.CurrentRow[13]; textBox1.Text += retb.CurrentRow[14]; textBox1.Text += retb.CurrentRow[15]; textBox1.Text += retb.CurrentRow[16]; textBox1.Text += retb.CurrentRow[17]; textBox1.Text += retb.CurrentRow[18]; textBox1.Text += retb.CurrentRow[19]; textBox1.Text += retb.CurrentRow[20]; textBox1.Text += retb.CurrentRow[21]; textBox1.Text += retb.CurrentRow[22]; textBox1.Text += retb.CurrentRow[23]; textBox1.Text += retb.CurrentRow[24]; textBox1.Text += "\r\n\r\n"; } MessageBox.Show("一共" + retb.RowCount + "条数据!"); } private void button3_Click(object sender, EventArgs e) { textBox1.Text = ""; this.timer1.Start(); // NewMethod2(); } /// <summary> /// 返回待sap查询的数据! /// </summary> private void NewMethod2() { textBox1.Text += "\r\n\r\n"; textBox1.Text += "单据生成成功,预算扣减完成,正在导入预算数据"; RfcDestination dest; RfcRepository rfcrep; NewMethod(out dest, out rfcrep); IRfcFunction QUERY = null; QUERY = rfcrep.CreateFunction("ZFK_WERKS_QUERY");//凭证函数 QUERY.Invoke(dest); IRfcTable YSQUERY = QUERY.GetTable("ITAB"); string ZDEPT = "and DeptID in(";//部门 string ZFKACC = "and CostID in(";//科目 //string ZDEPT = string.Empty;//部门 //string ZFKACC = string.Empty;//科目 string MONTH = string.Empty; string YEAR = string.Empty; string SQLQUERY = @"SELECT * FROM HX_SAP_Ys WHERE 1=1 and"; for (int i = 0; i < YSQUERY.RowCount; i++) { YSQUERY.CurrentIndex = i; MONTH += YSQUERY.CurrentRow[18].GetValue() + ","; YEAR += YSQUERY.CurrentRow[15].GetValue() + ","; ZDEPT += "(SELECT iDepID FROM dbo.Sys_Dept WHERE cDepCode='" + YSQUERY.CurrentRow.GetValue("ZDEPT") + "'),"; ZFKACC += "(SELECT CostID FROM dbo.EB_CostType WHERE CostCode='" + YSQUERY.CurrentRow.GetValue("ZFKACC") + "'),"; } MONTH = MONTH.Substring(0, MONTH.Length - 1); YEAR = YEAR.Substring(0, YEAR.Length - 1); ZDEPT = ZDEPT.Substring(0, ZDEPT.Length - 1) + ")"; ZFKACC = ZFKACC.Substring(0, ZFKACC.Length - 1) + ")"; //查询预算金额 SQLQUERY += " MONTH in(" + MONTH + ")" + "and YEAR in(" + YEAR + ") " + ZDEPT + ZFKACC; DataSet BudPlands = SapVoucher.SAPinsert.GetDataSet(SQLQUERY); IRfcFunction myBudPlan = null; myBudPlan = rfcrep.CreateFunction("ZFK_WERKS_FINAL");//凭证函数 IRfcTable Itb_BudPlan = myBudPlan.GetTable("ITAB"); prog.Maximum = BudPlands.Tables[0].Rows.Count; if (BudPlands.Tables[0].Rows.Count > 0) { prog.Value = 0; foreach (DataRow dr in BudPlands.Tables[0].Rows) { string con_AdequacyOfBudget = "select sum(ISNULL(budmoney,0)-ISNULL(usedmoney,0)) as " + "money ,sum(ISNULL(budmoney,0)) as budmoney from (select b.*,(SELECT CostCode FROM dbo.EB_CostType WHERE EB_CostType.CostID=b.CostID) AS " + " CostCode from BudPlan b left join Sys_dept s on b.deptid=s.idepId where s.cDepCode='" + dr["cDepCode"].ToString() + "') total " + " where iLevel='1' and total. CostCode='" + dr["CostCode"].ToString() + "' and year(begindate) ='" + dr["YEAR"].ToString() + "' group by year(begindate)"; DataSet ds_AdequacyOfBudget = SapVoucher.SAPinsert.GetDataSet(con_AdequacyOfBudget); string con_AdequacyOfBudgetM = "select * from (select sum(ISNULL(budmoney,0)-ISNULL(usedmoney,0)) as " + "money ,sum(ISNULL(budmoney,0)) as budmoney , month(begindate) as months from (select b.*,(SELECT CostCode FROM dbo.EB_CostType WHERE EB_CostType.CostID=b.CostID) AS " + " CostCode from BudPlan b left join Sys_dept s on b.deptid=s.idepId where s.cDepCode='" + dr["cDepCode"].ToString() + "') total " + " where total. CostCode='" + dr["CostCode"].ToString() + "' and year(begindate) ='" + dr["YEAR"].ToString() + "' group by year(begindate), month(begindate) ) t where t.months='" + dr["MONTH"].ToString() + "'"; DataSet ds_AdequacyOfBudgetM = SapVoucher.SAPinsert.GetDataSet(con_AdequacyOfBudgetM); textBox1.Text += "成功导入" + prog.Value + 1 + "条"; textBox1.Text += "\r\n\r\n"; prog.Value = prog.Value + 1; Itb_BudPlan.Insert(); Itb_BudPlan.CurrentRow.SetValue("ZDEPT", dr["cDepCode"].ToString()); Itb_BudPlan.CurrentRow.SetValue("ZFKACC", dr["CostCode"].ToString()); Itb_BudPlan.CurrentRow.SetValue("ZPRD_Y", dr["YEAR"].ToString()); Itb_BudPlan.CurrentRow.SetValue("ZBUJG_Y", ds_AdequacyOfBudget.Tables[0].Rows[0]["budmoney"].ToString()); Itb_BudPlan.CurrentRow.SetValue("ZREMJG_Y", ds_AdequacyOfBudget.Tables[0].Rows[0]["money"].ToString()); Itb_BudPlan.CurrentRow.SetValue("ZPRD_M", dr["MONTH"].ToString()); Itb_BudPlan.CurrentRow.SetValue("ZBUJG_M", dr["BudMoney"].ToString()); Itb_BudPlan.CurrentRow.SetValue("ZREMJG_M", ds_AdequacyOfBudgetM.Tables [0].Rows[0]["money"].ToString()); } myBudPlan.Invoke(dest);//提交 } else { textBox1.Text += "\r\n\r\n"; textBox1.Text += "无有效预算数据!"; } } private void timer1_Tick(object sender, EventArgs e) { //每晚一点执行 int h = DateTime.Now.Hour; if (h == 1) { this.timer1.Enabled = false; NewMethod1(); NewMethod2(); this.timer1.Enabled = true; } } private void HXSAP_Load(object sender, EventArgs e) { } } }