private StandardResult Z9EARS_DLL(int DocEntry, string U_ARH, string U_ARD, string U_ARQ, decimal U_ARM, string U_ARS) { var result = new StandardResult(); var connstring = GetDllConnectionString(); using (Sap.Data.Hana.HanaConnection connection = new Sap.Data.Hana.HanaConnection(connstring)) //创建connection连接对象 { connection.Open(); //打开链接 var sql = new System.Text.StringBuilder(); //SQL拼接,无参数化 sql.Append("UPDATE \"" + GetDbName() + "\".\"OINV\" SET "); sql.AppendFormat("\"OINV\".\"U_ARH\" =\'{0}\',", U_ARH); sql.AppendFormat("\"OINV\".\"U_ARD\" =\'{0}\',", U_ARD); sql.AppendFormat("\"OINV\".\"U_ARQ\" =\'{0}\',", U_ARQ); sql.AppendFormat("\"OINV\".\"U_ARM\" =\'{0}\',", U_ARM); sql.AppendFormat("\"OINV\".\"U_ARS\" =\'{0}\'", U_ARS); sql.AppendFormat(" where \"OINV\".\"DocEntry\"={0}", DocEntry); //参数化 //sql.Append("UPDATE \"BS_SBO_1970_AR\".\"OINV\" SET "); //sql.AppendFormat("\"OINV\".\"U_ARH\" =:U_ARH,", U_ARH); //sql.AppendFormat("\"OINV\".\"U_ARD\" =:U_ARD,", U_ARD); //sql.AppendFormat("\"OINV\".\"U_ARQ\" =:U_ARQ,", U_ARQ); //sql.AppendFormat("\"OINV\".\"U_ARE\" =:U_ARE,", U_ARE); //sql.AppendFormat("\"OINV\".\"U_ARS\" =:U_ARS", U_ARS); //sql.AppendFormat(" where \"OINV\".\"DocEntry\"=:DocEntry", DocEntry); var strSql = sql.ToString(); using (Sap.Data.Hana.HanaCommand command = new Sap.Data.Hana.HanaCommand(strSql))//command 对象 { command.Connection = connection; command.Parameters.Add(new Sap.Data.Hana.HanaParameter() { ParameterName = ":DocEntry", HanaDbType = Sap.Data.Hana.HanaDbType.Integer, Value = DocEntry }); command.Parameters.Add(new Sap.Data.Hana.HanaParameter() { ParameterName = ":U_ARH", HanaDbType = Sap.Data.Hana.HanaDbType.VarChar, Value = U_ARH }); command.Parameters.Add(new Sap.Data.Hana.HanaParameter() { ParameterName = ":U_ARD", HanaDbType = Sap.Data.Hana.HanaDbType.VarChar, Value = U_ARD }); command.Parameters.Add(new Sap.Data.Hana.HanaParameter() { ParameterName = ":U_ARQ", HanaDbType = Sap.Data.Hana.HanaDbType.VarChar, Value = U_ARQ }); command.Parameters.Add(new Sap.Data.Hana.HanaParameter() { ParameterName = ":U_ARM", HanaDbType = Sap.Data.Hana.HanaDbType.Decimal, Value = U_ARM }); command.Parameters.Add(new Sap.Data.Hana.HanaParameter() { ParameterName = ":U_ARS", HanaDbType = Sap.Data.Hana.HanaDbType.VarChar, Value = U_ARS }); if (command.ExecuteNonQuery() > 0) { result.Code = "Y"; result.Msg = "成功"; } else { result.Code = "N"; result.Msg = "失败,更新影响行数为0"; } } } return(result); }
private SearchResult <List <InvoiceModel> > Z9EAR_DLL(int DocEntry, List <InvoiceView> list) { var result = new SearchResult <List <InvoiceModel> >(); var connstring = GetDllConnectionString(); using (Sap.Data.Hana.HanaConnection connection = new Sap.Data.Hana.HanaConnection(connstring)) //创建connection连接对象 { connection.Open(); //打开链接 var sql = "SELECT * FROM \"" + GetDbName() + "\".\"CBIC_AR\""; if (DocEntry > 0) { sql = sql + " where \"CBIC_AR\".\"DocEntry\"=" + DocEntry; } using (Sap.Data.Hana.HanaCommand command = new Sap.Data.Hana.HanaCommand(sql)) //command 对象 { command.Connection = connection; Sap.Data.Hana.HanaDataReader reader = command.ExecuteReader(); while (reader.Read()) { var viewModel = new InvoiceView(); viewModel.DocEntry = reader["DocEntry"] == null ? -1 : reader["DocEntry"].ToInt(); viewModel.DocNum = reader["DocNum"] == null ? -1 : reader["DocNum"].ToInt(); viewModel.TaxDate = reader["TaxDate"] == null ? DateTime.MinValue : reader["TaxDate"].ToDateTime(); viewModel.U_ARS = reader["U_ARS"] == null ? "" : reader["U_ARS"].ToString(); viewModel.U_ART = reader["U_ART"] == null ? "" : reader["U_ART"].ToString(); viewModel.U_ARC = reader["U_ARC"] == null ? "" : reader["U_ARC"].ToString(); viewModel.CardCode = reader["CardCode"] == null ? "" : reader["CardCode"].ToString(); viewModel.CardName = reader["CardName"] == null ? "" : reader["CardName"].ToString(); viewModel.GTSRegNum = reader["GTSRegNum"] == null ? "" : reader["GTSRegNum"].ToString(); viewModel.GTSBilAddr = reader["GTSBilAddr"] == null ? "" : reader["GTSBilAddr"].ToString(); viewModel.Phone1 = reader["Phone1"] == null ? "" : reader["Phone1"].ToString(); viewModel.U_CV_BankName = reader["U_CV_BankName"] == null ? "" : reader["U_CV_BankName"].ToString(); viewModel.GTSBankAct = reader["GTSBankAct"] == null ? "" : reader["GTSBankAct"].ToString(); viewModel.LineNum = reader["LineNum"] == null ? -1 : reader["LineNum"].ToInt(); viewModel.ItemCode = reader["ItemCode"] == null ? "" : reader["ItemCode"].ToString(); viewModel.Quantity = reader["Quantity"] == null ? -1 : reader["Quantity"].ToDecimal(); viewModel.unitMsr = reader["unitMsr"] == null ? "" : reader["unitMsr"].ToString(); viewModel.VatPrcnt = reader["VatPrcnt"] == null ? -1 : reader["VatPrcnt"].ToDecimal(); viewModel.GTotalSC = reader["GTotalSC"] == null ? -1 : reader["GTotalSC"].ToDecimal(); list.Add(viewModel); } reader.Close(); } } var invoiceList = list.GroupBy(x => new { x.DocEntry, x.DocNum, x.TaxDate, x.U_ARS, x.U_ART, x.U_ARC, x.CardCode, x.CardName, x.GTSRegNum, x.GTSBilAddr, x.Phone1, x.U_CV_BankName, x.GTSBankAct }) .Select(x => new { DocEntry = x.Key.DocEntry, DocNum = x.Key.DocNum, TaxDate = x.Key.TaxDate, U_ARS = x.Key.U_ARS, U_ART = x.Key.U_ART, U_ARC = x.Key.U_ARC, CardCode = x.Key.CardCode, CardName = x.Key.CardName, GTSRegNum = x.Key.GTSRegNum, GTSBilAddr = x.Key.GTSBilAddr, Phone1 = x.Key.Phone1, U_CV_BankName = x.Key.U_CV_BankName, GTSBankAct = x.Key.GTSBankAct }).ToList(); var invoice = new List <InvoiceModel>(); invoiceList.ForEach(x => { var data = new InvoiceModel() { DocEntry = x.DocEntry, DocNum = x.DocNum, TaxDate = x.TaxDate, U_ARS = x.U_ARS, U_ART = x.U_ART, U_ARC = x.U_ARC, CardCode = x.CardCode, CardName = x.CardName, GTSRegNum = x.GTSRegNum, GTSBilAddr = x.GTSBilAddr, Phone1 = x.Phone1, U_CV_BankName = x.U_CV_BankName, GTSBankAct = x.GTSBankAct, Contact = x.GTSBilAddr + x.Phone1, BankAccount = x.U_CV_BankName + x.GTSBankAct, DetailList = new List <Detail>() }; invoice.Add(data); var temp = list.Where(q => q.DocEntry == x.DocEntry).ToList(); temp.ForEach(q => { data.DetailList.Add(new Detail() { GTotalSC = q.GTotalSC, ItemCode = q.ItemCode, LineNum = q.LineNum, Quantity = q.Quantity, unitMsr = q.unitMsr, VatPrcnt = q.VatPrcnt }); }); }); logger.Info("返回结果行数:" + invoice.Count); result.Code = "Y"; result.Msg = "成功"; result.TotalCount = invoice.Count; result.Body = invoice; return(result); }