谁能优化一下下面的代码

Question

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;using SysSap.SAP.CONN;
using System.Xml.Linq;
using Model.baseCls.db;namespace SysSap.SAP.Master
{
    public  class MasterOITM
    {        public static string OITM(string ItemCode)
        {
            try
            {
                //查询物料主数据的信息
                SAPbobsCOM.Items oItems, iItems;
                oItems = (SAPbobsCOM.Items)DIConn.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oItems);
                iItems = (SAPbobsCOM.Items)DIConn.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oItems);
                int lRetCode;
                DIConn.oCompany.XmlExportType = SAPbobsCOM.BoXmlExportTypes.xet_ExportImportMode;
                SqlHelper obj_SqlHelper = new SqlHelper();
                string estrSql = "Select ItemCode  From SAP_OITM A LEFT JOIN SAP_OITB B ON A.ItmsGrpCod=B.Grpid Where ItemCode ='" + ItemCode + "'";
                string eStr = obj_SqlHelper.runCol(estrSql);
                if (eStr != "")
                {
                    System.Data.DataTable Items, ItemsuserField, Items_Price, ItemWare, ItemWareuserField, ItemCardCode;//定义数据表
                    SAPbobsCOM.Recordset oRecordset;
                    oRecordset = (SAPbobsCOM.Recordset)DIConn.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
                    string userField = @"Select A.name From   Syscolumns   A,Sysobjects   B 
                                   Where B.Name= 'SAP_OITM' And A.id=B.id And LEFT(a.name,2)='U_'";
                    ItemsuserField = obj_SqlHelper.retDataTable(userField);
                    userField = "";
                    for (int m = 0; m < ItemsuserField.Rows.Count; m++)
                    {
                        userField = userField + ItemsuserField.Rows[m][0].ToString() + ",";
                    }
                    if (userField != "")
                    {
                        userField = userField.Substring(0, userField.Length - 1);
                    }
                    string strSql = @"Select ItemCode,ItemName,B.DOCENTRY[ItemsGroupCode],-1[CustomsGroupCode],CODEBARS[BarCode],
                                FRGNNAME[ForeignName],ItemType,ISSUEMTHD[IssueMethod],MANBTCHNUM[ManageBatchNumbers],ManSerNum[ManageSerialNumbers],
                                ISNULL(MNGMETHOD,'R')[SRIAndBatchManageMethod],VALIDFOR[Valid],FROZENFOR[Frozen],USERTEXT[User_Text],CARDCODE[Mainsupplier],
                                BUYUNITMSR[PurchaseUnit],SALUNITMSR[SalesUnit],NUMINBUY[PurchaseQtyPerPackUnit],PLANINGSYS[PlanningSystem],
                                PRCRMNTMTD[ProcurementMethod],ORDRMULTI[OrderMultiple],LeadTime,MINORDRQTY[MinOrderQuantity],INVNTRYUOM[InventoryUOM],
                                EVALSYSTEM[CostAccountingMethod],PicturName[Picture],DfltWH[DefaultWarehouse],
                                CASE InvntItem when '0' then 'N' ELSE 'Y' END AS InventoryItem,CASE SellItem when '0' then 'N' ELSE 'Y' END AS SalesItem,
                                CASE PrchseItem when '0' then 'N' ELSE 'Y' END AS PurchaseItem,CASE AssetItem when '0' then 'N' ELSE 'Y' END AS AssetItem";                    if (userField != "")
                    {
                        strSql = strSql + "," + userField;
                    }
                    strSql += " From SAP_OITM A LEFT JOIN SAP_OITB B ON A.ItmsGrpCod=B.Grpid Where ItemCode ='" + ItemCode + "'";                    //讲查询的信息赋予DataTable
                    Items = obj_SqlHelper.retDataTable(strSql);                    //查询物料主数据对应的价格清单
                    string lineSql = @"SELECT Docid[PriceList],0.000000[Price] From SAP_OPLN Order By Docid ";
                    Items_Price = obj_SqlHelper.retDataTable(lineSql);
                    userField = @"Select A.name From   Syscolumns   A,Sysobjects   B 
                                   Where B.Name= 'SAP_OITW' And A.id=B.id And LEFT(a.name,2)='U_'";
                    ItemWareuserField = obj_SqlHelper.retDataTable(userField);
                    userField = "";
                    for (int m = 0; m < ItemWareuserField.Rows.Count; m++)
                    {
                        userField = userField + ItemWareuserField.Rows[m][0].ToString() + ",";
                    }

Answer 1

                    if (userField != "")
                    {
                        userField = userField.Substring(0, userField.Length - 1);
                    }
                    //查询员工对应的考评记录
                    string wareHouse = @"Select isnull(A.MINSTOCK,0)[MinimalStock],isnull(A.MAXSTOCK,0)[MaximalStock],isnull(A.AVGPRICE,0)[StandardAveragePrice],
                                 isnull(A.MINORDER,0)[MinimalOrder],'N' Locked,A.WhsCode[WarehouseCode]  ";
                    if (userField != "")
                    {
                        wareHouse = wareHouse + "," + userField;
                    }
                    // wareHouse += " From SAP_OITW A,SAP_OITM B  Where A.ItemCode='" + ItemCode + "' and A.ItemCode=B.ItemCode and B.DfltWH=A.WhsCode ";
                    wareHouse += " From SAP_OITW A Where A.ItemCode='" + ItemCode + "' ";
                    ItemWare = obj_SqlHelper.retDataTable(wareHouse);
                    //获取生成SAP标准的XML文件的地址
                    string SAPXML = SAP.XML.CreateOITMXml.Xml(Items, Items_Price, ItemWare, "4", "2");
                    Items.Dispose();//释放资源
                    iItems = (SAPbobsCOM.Items)DIConn.oCompany.GetBusinessObjectFromXML(SAPXML, 0); //初始化XML的文件
                    // string SAPDocentry = obj_SqlHelper.retCol("Select Docentry From SAP_OITM Where ItemCode='" + ItemCode + "'");
                    if (oItems.GetByKey(ItemCode))
                    {
                        oItems.ItemName = iItems.ItemName;
                        oItems.ItemsGroupCode = iItems.ItemsGroupCode;
                        oItems.CustomsGroupCode = iItems.CustomsGroupCode;
                        oItems.BarCode = iItems.BarCode;
                        oItems.ForeignName = iItems.ForeignName;
                        oItems.ItemType = iItems.ItemType;
                        oItems.IssueMethod = iItems.IssueMethod;
                        oItems.ManageBatchNumbers = iItems.ManageBatchNumbers;
                        oItems.ManageSerialNumbers = iItems.ManageSerialNumbers;
                        oItems.SRIAndBatchManageMethod = iItems.SRIAndBatchManageMethod;
                        oItems.Valid = iItems.Valid;
                        oItems.Frozen = iItems.Frozen;
                        oItems.User_Text = iItems.User_Text;
                        oItems.Mainsupplier = iItems.Mainsupplier;
                        oItems.PurchaseUnit = iItems.PurchaseUnit;
                        oItems.SalesUnit = iItems.SalesUnit;
                        oItems.PurchaseQtyPerPackUnit = iItems.PurchaseQtyPerPackUnit;
                        oItems.PlanningSystem = iItems.PlanningSystem;
                        oItems.ProcurementMethod = iItems.ProcurementMethod;
                        oItems.OrderMultiple = iItems.OrderMultiple;
                        oItems.LeadTime = iItems.LeadTime;
                        oItems.MinOrderQuantity = iItems.MinOrderQuantity;
                        oItems.InventoryUOM = iItems.InventoryUOM;
                        oItems.CostAccountingMethod = iItems.CostAccountingMethod;
                        oItems.Picture = iItems.Picture;
                        oItems.DefaultWarehouse = iItems.DefaultWarehouse;
                        oItems.PurchaseItem = iItems.PurchaseItem;
                        oItems.SalesItem = iItems.SalesItem;
                        oItems.InventoryItem = iItems.InventoryItem;
                        oItems.AssetItem = iItems.AssetItem;
                        //主表自定义字段
                        for (int m = 0; m < ItemsuserField.Rows.Count; m++)
                        {
                            oItems.UserFields.Fields.Item(ItemsuserField.Rows[m][0].ToString()).Value = iItems.UserFields.Fields.Item(ItemsuserField.Rows[m][0].ToString()).Value;
                        }
                        int t = 0, j = 0;
                        int x = 0;                        //删除系统内没有产生过交易的仓库
                        int whsCount;
                        whsCount = oItems.WhsInfo.Count;
                        for (j = 0; j < whsCount; j++)
                        {
                            oItems.WhsInfo.SetCurrentLine(t);
                            oRecordset.DoQuery("Select count(1) From OINM Where Warehouse ='" + oItems.WhsInfo.WarehouseCode + "' AND ItemCode ='" + ItemCode + "'");
                            if (oRecordset.RecordCount == 0)
                            {
                                oItems.WhsInfo.Delete();
                            }
                            else
                            {
                                t = t + 1;
                            }
                        }
                        for (int m = 0; m < ItemWareuserField.Rows.Count; m++)
                        {
                            oItems.WhsInfo.UserFields.Fields.Item(ItemWareuserField.Rows[m][0].ToString()).Value = iItems.WhsInfo.UserFields.Fields.Item(ItemWareuserField.Rows[m][0].ToString()).Value;
                        }
                        lRetCode = oItems.Update(); //更新SAP单据
                    }
                    else
                    {
                        oItems = (SAPbobsCOM.Items)DIConn.oCompany.GetBusinessObjectFromXML(SAPXML, 0); //初始化XML的文件
                        lRetCode = oItems.Add();   //创建SAP单据
                    }
                    if (lRetCode == 0)
                    {
                        //再次更新仓库
                        if (oItems.GetByKey(ItemCode))
                        {
                            oItems.DefaultWarehouse = iItems.DefaultWarehouse;
                            int whsCount = iItems.WhsInfo.Count;
                            int sCount = oItems.WhsInfo.Count;
                            for (int i = 0; i < whsCount; i++)
                            {
                                //判断WD的仓库在SAP中是否存在
                                iItems.WhsInfo.SetCurrentLine(i);
                                oRecordset.DoQuery("Select ItemCode From OITW Where WhsCode ='" + iItems.WhsInfo.WarehouseCode + "' AND ItemCode ='" + ItemCode + "'");
                                if (oRecordset.RecordCount > 0)
                                {
                                    //根据循环判断SAP中哪一行的仓库与WD当前循环的仓库是否存在，存在即更新
                                    for (int j = 0; j < sCount; j++)
                                    {
                                        oItems.WhsInfo.SetCurrentLine(j);
                                        if (oItems.WhsInfo.WarehouseCode == iItems.WhsInfo.WarehouseCode)
                                        {
                                            oItems.WhsInfo.MinimalStock = iItems.WhsInfo.MinimalStock;
                                            oItems.WhsInfo.MaximalStock = iItems.WhsInfo.MaximalStock;
                                            oItems.WhsInfo.Locked = iItems.WhsInfo.Locked;
                                            oItems.WhsInfo.MinimalOrder = iItems.WhsInfo.MinimalOrder;
                                            //oItems.WhsInfo.StandardAveragePrice = iItems.WhsInfo.StandardAveragePrice;
                                            //oItems.WhsInfo.WarehouseCode = iItems.WhsInfo.WarehouseCode;
                                        }
                                    }
                                }
                                else
                                {
                                    //添加WD中的仓库到SAP中                                    oItems.WhsInfo.MinimalStock = iItems.WhsInfo.MinimalStock;
                                    oItems.WhsInfo.MaximalStock = iItems.WhsInfo.MaximalStock;
                                    oItems.WhsInfo.Locked = iItems.WhsInfo.Locked;
                                    oItems.WhsInfo.MinimalOrder = iItems.WhsInfo.MinimalOrder;
                                    oItems.WhsInfo.StandardAveragePrice = iItems.WhsInfo.StandardAveragePrice;
                                    oItems.WhsInfo.WarehouseCode = iItems.WhsInfo.WarehouseCode;
                                    oItems.WhsInfo.Add();
                                }
                            }
                            lRetCode = oItems.Update(); //更新SAP单据
                        }
                        if (lRetCode == 0)
                        {
                            String tDocEntry = "";
                            DIConn.oCompany.GetNewObjectCode(out tDocEntry);             //获取SAP单据创建好单据
                            //Log.ErrLog("同步物料主数据成功,单据号：" + tDocEntry);        //同步成功
                            strSql = "Update SAP_OITM Set DocEntry='" + tDocEntry + "' Where ItemCode='" + ItemCode + "'";
                            obj_SqlHelper.runSQL(strSql);
                            return "0";
                        }
                        else
                        {
                            CONN.Conn.VoidMain();//判断sap同步失败是否断开连接，断开则重连
                            return "同步物料主数据失败:" + DIConn.oCompany.GetLastErrorDescription();
                        }
                    }
                    else
                    {
                        //Log.ErrLog("同步物料主数据失败:" + DIConn.oCompany.GetLastErrorDescription());   //获取错误信息
                        CONN.Conn.VoidMain();//判断sap同步失败是否断开连接，断开则重连
                        return "同步物料主数据失败:" + DIConn.oCompany.GetLastErrorDescription();
                    }
                }
                else
                {
                    if (oItems.GetByKey(ItemCode))
                    {
                        oItems.Remove();
                    }
                    return "0";
                }
                
            }
            catch (Exception ex)
            {
                DIConn.Conn();
                //Log.ErrLog("同步物料主数据失败:" + ex.Message);//错误日志
                return "同步物料主数据失败:" + ex.Message;
                
            }
        }
    }
}

Answer 2

至少要分个层吧，前台直接写sql语句，这样看起来好乱

Answer 3

一个方法最好不超过25行，你这个。。还有你SQL这么长，不能用存储过程么？