C# SCommBB.ExecuteSql方法代码示例

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            if (Request.Params["functionNo"] != null && Request.Params["functionNo"].Trim() != "")
            {
                this.FunctionNo = Request.Params["functionNo"];
            }
        }

        if (this.SET_SQL.Value != "")
        {
            this.SET_SQL.Value = this.SET_SQL.Value.Substring(0, this.SET_SQL.Value.Length - 1);
            SCommBB commBB = new SCommBB();
            try
            {
                string[] updateSqls = this.SET_SQL.Value.Split(';');
                foreach (string updateSql in updateSqls)
                {
                    commBB.ExecuteSql(updateSql);
                }
            }
            finally
            {
                commBB.Dispose();
            }
            this.SET_SQL.Value = "";
        }

        this.ShowPullInfo(this.FunctionNo);
    }

    /// <summary>
    /// 获取SAP数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnData_Click(object sender, EventArgs e)
    {
        SCommBB commBB = new SCommBB();

        try
        {
            //获取SAP到货单、销售订单
            commBB.ExecuteSql("exec up_SAP_GetNewData");

            this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('同步SAP到货\\拣货数据成功！');", true);
        }
        catch (Exception ex)
        {
            this.ClientScript.RegisterStartupScript(this.GetType(), "ShowErr", "ShowErr(\"" + Server.UrlEncode(ex.Message) + "\",3);", true);
            return;
        }
        finally
        {
            commBB.Dispose();
        }
    }

    /// <summary>
    /// 修改关联的托盘号
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnChangeBox_Click(object sender, EventArgs e)
    {
        if (txtBoxNo.Text.ToString() == "")
        {
            this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"箱号不能为空!\");", true);
            return;
        }
        if (txtPalletNewNo.Text.ToString()=="")
        {
            this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"托盘号不能为空!\");", true);
            return;
        }
        if (txtBoxNo.Text.ToString().ToUpper().Substring(0, 1) != "X" || txtPalletNewNo.Text.ToString().ToUpper().Substring(0,1)!="T")
        {
            this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"箱号或是托盘号不正确!\");", true);
            return;
        }

        SCommBB comm = new SCommBB();
        try
        {
            comm.ExecuteSql("Update BarrangeBillBox set PalletNo = '" + txtPalletNewNo.Text.Trim().ToUpper() + "' where boxNo = '" + txtBoxNo.Text.Trim().ToUpper() + "'");

            this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"更新托盘号成功 箱号 " + txtBoxNo.Text.Trim().ToUpper() + " 更新后的托盘是" + txtPalletNewNo.Text.Trim().ToUpper() + "!\");", true);

        }
        catch (Exception ee)
        {

            this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"更新托盘号失败 " + ee.Message + "!\");", true);

        }
        finally
        {
            txtBoxNo.Text = "";
            txtPalletNewNo.Text = "";
            comm.Dispose();

        }
    }

    public bool UpdateLBoxNo(int BoxQty, string MaterialNo)
    {
        SCommBB commBB = new SCommBB();
        bool ret = false;
        string command = @"update LMaterial set U_BoxQty =" + BoxQty + " where materialNo ='" + MaterialNo + "'";
        try
        {
            commBB.ExecuteSql(command);
            ret = true;
        }
        catch
        {
            ret = false;
        }

        finally
        {
            commBB.Dispose();

        }
        return ret;
    }

 public bool UpdateTHBill(string backBillNo)
 {
     bool ret = false;
     string command = "Update BBackBill set instantState = '05' where backBillNo ='" + backBillNo + "' ";
     SCommBB commBB = new SCommBB();
     try
     {
         commBB.ExecuteSql(command);
         ret = true;
     }
     catch
     {
         ret = false;
     }
     commBB.Dispose();
     return ret;
 }

    protected void Button2_Click(object sender, EventArgs e)
    {
        foreach (GridViewRow gvrow in this.grid.Rows)
        {
              CheckBox chkId = (CheckBox)gvrow.FindControl("chkId");
              if (chkId.Checked == true)
              {

                  //string str = "update BArriveBill  set instantState='06' where id ='" + this.grid.DataKeys[gvrow.RowIndex]["chkId"].ToString() + "'";

                 // this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"" + this.grid.DataKeys[gvrow.RowIndex]["id"].ToString() + "\");", true);

                  string str = "update BArriveBill  set instantState='06' where id ='" + this.grid.DataKeys[gvrow.RowIndex]["id"].ToString() + "'";

                  SCommBB commBB = new SCommBB();
                  commBB.ExecuteSql(str);

              }

        }
        this.BindGrid();
    }

    public string SAPConverMaterialOutInStock(string strStockUpBillNo, string MarkText)
    {
        SapOnlineService.SapOnlineService sapService = new SapOnlineService.SapOnlineService();
        CSaleDetailBB saleDetailBB = new CSaleDetailBB();
        SCommBB commBB = new SCommBB();

        try
        {
            SapOnlineService.WebFaHuoModel2[] faHuoList = null;
            SapOnlineService.WebShouHuoModel2[] shouHuoList = null;
            SapOnlineService.IGE1_Return IGEReturn = null;
            SapOnlineService.IGN1_Return IGNReturn = null;
            SapOnlineService.IGE1[] IGEArray = null;
            StringBuilder struFaHuo = new StringBuilder();
            StringBuilder struShouHuo = new StringBuilder();
            DataTable dtPickOutPlan = new DataTable();
            string strResult = "", strError = "";

            //获取所有需要收发货的拣货计划
            dtPickOutPlan = commBB.Query("exec Proc_GetSAPKCSFH '" + strStockUpBillNo + "'").Tables[0];
            if (dtPickOutPlan.Rows.Count == 0)
            {
                strResult = "0&当前拣货单没有需要收发货物料！";
                return strResult;
            }

            #region 库存发货

            foreach (DataRow rowPickOutPlan in dtPickOutPlan.Rows)
            {
                double num = 0;
                string strAbsEntry = "", strDocEntry = "", strMaterialNo_FH = "", strMaterialNo_SH = "";

                strAbsEntry = rowPickOutPlan["absEntry"].ToString();//提货单ID
                strDocEntry = rowPickOutPlan["docEntry"].ToString();//销售订单ID
                strMaterialNo_FH = rowPickOutPlan["materialNo_FH"].ToString();//库存发货物料
                strMaterialNo_SH = rowPickOutPlan["materialNo_SH"].ToString();//库存收货物料
                num = Convert.ToDouble(rowPickOutPlan["num"]);//剩余未SAP库存收发货数量

                //库存发货
                if (Convert.ToBoolean(rowPickOutPlan["isSAPOutStock"]) == false)
                {
                    struFaHuo.Append(strMaterialNo_FH + "," + num + ",01,"
                        + strDocEntry + "," + strAbsEntry + ","
                        + strMaterialNo_SH + ",GHBZ-WMS;");
                }
            }

            if (struFaHuo.ToString() != "")
            {
                string[] array_FaHuo = struFaHuo.ToString().Trim(';').Split(';');

                faHuoList = new SapOnlineService.WebFaHuoModel2[array_FaHuo.Length];

                //发货
                for (int i = 0; i < array_FaHuo.Length; i++)
                {
                    string[] array_FaHuoDetail = array_FaHuo[i].Split(',');
                    SapOnlineService.WebFaHuoModel2 faHuo = new SapOnlineService.WebFaHuoModel2();

                    faHuo.ItemCode = array_FaHuoDetail[0];//ItemCode
                    faHuo.Quantity = Convert.ToDouble(array_FaHuoDetail[1]);//Quantity
                    faHuo.WhsCode = array_FaHuoDetail[2];//WhsCode
                    faHuo.U_ORDR = array_FaHuoDetail[3];//U_ORDR
                    faHuo.U_PKL = array_FaHuoDetail[4];//U_PKL
                    faHuo.U_ItemCode = array_FaHuoDetail[5];//U_ItemCode
                    faHuo.U_YongT = "GHBZ-WMS";//U_YongT

                    faHuoList[i] = faHuo;
                }

                //库存发货
                IGEReturn = sapService.IGE1_Web_Add_ForCangKu(System.DateTime.Now, "由WMS提交-" + MarkText, faHuoList);

                strResult = IGEReturn.ResultSucess;
                strError = IGEReturn.ErrMesg;

                if (strResult == "1")
                {
                    IGEArray = IGEReturn.ListIGE1;

                    for (int i = 0; i < IGEArray.Length; i++)
                    {
                        //更改发货状态
                        commBB.ExecuteSql("update dbo.CPickOutPlan set isSAPOutStock=1,sapStockPrice=" + IGEArray[i].StockPrice
                            + " where absEntry='" + IGEArray[i].U_PKL + "' and docEntry='" + IGEArray[i].U_ORDR
                            + "' and materialNo='" + IGEArray[i].ItemCode + "'");
                    }
                }
                else
                {
                    return "0&" + strError;
                }
            }

            #endregion 库存发货

            #region 库存收货

            //获取所有需要收货的拣货计划
//.........这里部分代码省略.........

    /// <summary>
    /// 提交SAP
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnCommit_Click(object sender, EventArgs e)
    {
        DMWJEXECUTE services = new DMWJEXECUTE();
        IList<string> messList = new List<string>();
        messList.Clear();
        try
        {
            bool isChecked = false;

            //获取选中的数据Id
            foreach (GridViewRow gvrow in this.grid.Rows)
            {
                CheckBox chkId = (CheckBox)gvrow.FindControl("chkId");

                if (chkId.Checked == true)
                {
                    int financeBillId = 0, financeBillLineNum = 0;
                    string strMaterialNo = "", custNo = "", custNm = "";
                    DateTime stockDt, arriveDt;
                    double num = 0;

                    financeBillId = Convert.ToInt32(this.grid.DataKeys[gvrow.RowIndex]["financeBillId"]);
                    financeBillLineNum = Convert.ToInt32(this.grid.DataKeys[gvrow.RowIndex]["financeBillLineNum"]);
                    strMaterialNo = this.grid.DataKeys[gvrow.RowIndex]["materialNo"].ToString();
                    num = Convert.ToDouble(this.grid.DataKeys[gvrow.RowIndex]["num"]);
                    stockDt = Convert.ToDateTime(this.grid.DataKeys[gvrow.RowIndex]["stockDt"]);
                    arriveDt = Convert.ToDateTime(this.grid.DataKeys[gvrow.RowIndex]["arriveDt"]);
                    custNo = this.grid.DataKeys[gvrow.RowIndex]["custNo"].ToString();
                    custNm = this.grid.DataKeys[gvrow.RowIndex]["custNm"].ToString();

                    string mess = Convert.ToInt32(chkId.ValidationGroup).ToString() + ";" + financeBillId.ToString() + ";" + financeBillLineNum.ToString() + ";" + strMaterialNo + ";" + num.ToString() + ";" + stockDt + ";" + arriveDt + ";" + custNo + ";" + custNm;

                    messList.Add(mess);
                    //strResult = services.SAPInStock(Convert.ToInt32(chkId.ValidationGroup), financeBillId,
                    //    financeBillLineNum, strMaterialNo, num, stockDt, arriveDt, custNo, custNm);

                    //if (strResult.Split('&')[0] == "1")
                    //{
                    //    isChecked = true;
                    //}
                    //else if (strResult.Split('&')[0] == "0")
                    //{
                    //    this.BindGrid();
                    //    this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"" + strResult.Split('&')[1] + "\");", true);
                    //    return;
                    //}
                }
            }

            if (messList.Count > 0)
            {
                SapOnlineService.SapOnlineService sapService = new SapOnlineService.SapOnlineService();
                SapOnlineService.POR1[] list = new SapOnlineService.POR1[messList.Count];
                for (int i = 0; i < messList.Count; i++)
                {
                    SapOnlineService.POR1 por1 = new SapOnlineService.POR1();
                    por1.DocEntry = Convert.ToInt32(messList[i].Split(';')[1].ToString());
                    por1.LineNum = Convert.ToInt32(messList[i].Split(';')[2].ToString());
                    por1.ItemCode = messList[i].Split(';')[3].ToString();
                    por1.Quantity = Convert.ToDouble(messList[i].Split(';')[4].ToString());//数量
                    list[i] = por1;
                }
                string strResult = sapService.Web_AddPDN_For_POR1(messList[0].Split(';')[7].ToString(), messList[0].Split(';')[8].ToString(), Convert.ToDateTime(messList[0].Split(';')[5].ToString()), "由WMS同步", Convert.ToDateTime(messList[0].Split(';')[5].ToString()), "由WMS同步", Convert.ToDateTime(messList[0].Split(';')[6].ToString()), list);

                if (strResult.Split('&')[0] == "1")
                {
                    for (int i = 0; i < messList.Count; i++)
                    {
                        BArriveDetailData arriveDetailModel = new BArriveDetailData();
                        BArriveDetailBB arriveDetailBB = new BArriveDetailBB();
                        SCommBB commBB = new SCommBB();
                        arriveDetailModel = arriveDetailBB.GetModel(Convert.ToInt32(messList[i].Split(';')[0]));

                        arriveDetailModel.isSapInStock = true;//是否已提交SAP入库

                        arriveDetailBB.ModifyRecord(arriveDetailModel);

                        //更改物料箱的“是否已提交SAP入库”状态
                        commBB.ExecuteSql(@"update dbo.BArrangeBillBox set isSapInStock=1 where isSapInStock=0
                                       and inStockDt is not null and arriveBillNo='" + arriveDetailModel.arriveBillNo
                                         + "' and financeBillId='" + messList[i].Split(';')[1].ToString() + "' and financeBillLineNum='" + messList[i].Split(';')[2].ToString()
                                         + "' and materialNo='" + messList[i].Split(';')[3].ToString() + "'"
                                         +" or oldMaterialNo ='"+ messList[i].Split(';')[3].ToString() + "'"
                                         +" and  isSapInStock=0  and inStockDt is not null and arriveBillNo='" + arriveDetailModel.arriveBillNo
                                         + "' and financeBillId='" + messList[i].Split(';')[1].ToString() + "' and financeBillLineNum='" + messList[i].Split(';')[2].ToString()+"'");

                    }

                    isChecked = true;
                }
                else if (strResult.Split('&')[0] == "0")
                {
                    this.BindGrid();
                    this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"" + strResult.Split('&')[1] + "\");", true);
                    return;
//.........这里部分代码省略.........

    /// <summary>
    /// 排托单列表行事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        SCommBB commBB = new SCommBB();

        try
        {
            GridViewRow gvRow = (e.CommandSource as LinkButton).NamingContainer as GridViewRow;
            string strArriveBillNo, strPalletIndex = "";

            strArriveBillNo = this.GridView1.DataKeys[gvRow.RowIndex]["arriveBillNo"].ToString();//到货单号
            strPalletIndex = this.GridView1.DataKeys[gvRow.RowIndex]["palletIndex"].ToString();//托盘序号

            #region 重新排托

            if (e.CommandName == "arrange")
            {
                //更改排托收货单状态为“01 收货中”
                commBB.ExecuteSql("update dbo.BArrangeBillFact set instantState='01' where arriveBillNo='"
                    + strArriveBillNo + "' and palletIndex='" + strPalletIndex + "'");

                //更改排托单明细状态为“02 排托中”
                commBB.ExecuteSql("update dbo.BArrangeBillDetail set instantState='02' where arriveBillNo='"
                    + strArriveBillNo + "' and palletIndex='" + strPalletIndex + "'");

                //更改排托单状态为“03 排托中”
                commBB.ExecuteSql("update dbo.BArrangeBill set instantState='03' where arriveBillNo='" + strArriveBillNo + "'");

                //更改到货单状态为“04 排托中”
                commBB.ExecuteSql("update dbo.BArriveBill set instantState='04' where billNo='" + strArriveBillNo + "'");

                return;
            }

            #endregion 重新排托
        }
        finally
        {
            commBB.Dispose();
        }
    }

 public void updateBForkliftTask(string palletNo)
 {
     string str = "update BForkliftTask set isDeal='1' where palletNo ='" + palletNo + "'and isDeal='0'";
     SCommBB commBB = new SCommBB();
     commBB.ExecuteSql(str);
     commBB.Dispose();
 }

    /// <summary>
    /// 确认发货
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnConfirmSend_Click(object sender, EventArgs e)
    {
        bool isChecked = false;

        //获取选中的数据Id
        foreach (GridViewRow gvrow in this.grid.Rows)
        {
            CheckBox chkId = (CheckBox)gvrow.FindControl("chkId");

            if (chkId.Checked == true)
            {
                isChecked = true;

                string id = this.grid.DataKeys[gvrow.RowIndex].Values["id"].ToString();
                string stockUpbillNo = this.grid.DataKeys[gvrow.RowIndex].Values["stockUpBillNo"].ToString();

                this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"选择的备货单号是" + stockUpbillNo + "!\");", true);
                string command = "select distinct palletNo,wareNo,wareLocatorNo from BArrangeBillBox where stockUpBillNo = '" + stockUpbillNo + "' and isnull(palletNo,'')<>''";//获取订单下的托盘号码 2015-06-25增加限制条件 排除生成空托盘的时候的bug
                SCommBB bb = new SCommBB();
                DataTable dt = bb.Query(command).Tables[0];
                bb.Dispose();
                BForkliftTaskBC bforkTaskBC = new BForkliftTaskBC();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    bforkTaskBC.SaveForkliftTask(dt.Rows[i][0].ToString(), "CKQ", "CKQ01", "20", this.currentUser.empId);
                }

                SCommBB commBB = new SCommBB();
                string strSql = "update dbo.CStockUpDetail set instantState='04' where stockUpBillNO='" + stockUpbillNo + "'";
                commBB.ExecuteSql(strSql);

                //将备货单属性改为09出库中
                strSql = @"update dbo.CStockUpBill set instantState='09'  where stockUpBillNo = '" + stockUpbillNo + "'";
                commBB.ExecuteSql(strSql);
                //实时获取订单状态
                //using (CStockUpBillBB billBB = new CStockUpBillBB())
                //{
                //    CStockUpBillData data = billBB.GetModel(Convert.ToInt32(id));

                //    data.instantState = "11";//状态更改为“确认发货”

                //    billBB.ModifyRecord(data);
                //}
            }
        }

        if (isChecked)
        {
            this.BindGrid();
            return;
        }

        this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"请选择一条记录!\");", true);
        return;
    }

 public bool UpdateBarrangeBillBox(string boxNO, string palletNo)
 {
     SCommBB commBB = new SCommBB();
     try
     {
         commBB.ExecuteSql("Update BarrangeBillBox set palletNo = '" + palletNo + "' where boxNo = '" + boxNO + "'");
         return true;
     }
     catch
     {
         return false;
     }
     finally
     {
         commBB.Dispose();
     }
 }

    public void updateBArrangeBillDetail(string financeBillNo, string arriveBillNo, string palletIndex)
    {
        string sql = "update BArrangeBillDetail set instantState='05' where financeBillNo='" + financeBillNo + "' AND arriveBillNo ='" + arriveBillNo + "' and palletIndex='" + palletIndex + "'";

        SCommBB commBB = new SCommBB();
        commBB.ExecuteSql(sql);
        commBB.Dispose();
    }

    public void SaveTSArriveBox(int arriveDetailId, string strArriveBillNo, string strFinanceBillNo, string strMaterialNo,
        string strBoxNo, string strPalletNo, int factNum, int isrtEmpId, string strWareLocatorNo)
    {
        TSBarriveDetailBB arriveDetailBB = new TSBarriveDetailBB();
        BArrangeBillBoxBB arrangeBillBoxBB = new BArrangeBillBoxBB();
        SCommBB commBB = new SCommBB();

        try
        {
            TSBarriveDetailData arriveDetailModel = new TSBarriveDetailData();
            BArrangeBillBoxData arrangeBillBoxModel = new BArrangeBillBoxData();
            DataTable dtWareLocator = new DataTable();

            //获取某库位信息
            dtWareLocator = this.GetWareLocatorInfo(strWareLocatorNo);

            //获取到货单明细实例
            arriveDetailModel = arriveDetailBB.GetModel(arriveDetailId);

            arrangeBillBoxModel.arriveBillNo = strArriveBillNo;//到货单号
            arrangeBillBoxModel.financeBillNo = strFinanceBillNo;//采购单号
            //arrangeBillBoxModel.financeBillId = arriveDetailModel.financeBillId;//采购订单ID
            //arrangeBillBoxModel.financeBillLineNum = arriveDetailModel.financeBillLineNum;//采购订单行号
            arrangeBillBoxModel.materialNo = strMaterialNo;//物料号
            arrangeBillBoxModel.boxNo = strBoxNo;//箱号
            arrangeBillBoxModel.palletNo = strPalletNo;//托盘号
            arrangeBillBoxModel.wareNo = dtWareLocator.Rows[0]["wareNo"].ToString();//库区
            arrangeBillBoxModel.wareLocatorNo = strWareLocatorNo;//库位
            arrangeBillBoxModel.isBoxArrange = false;//箱子是否已经排托
            arrangeBillBoxModel.isPalletUsing = false;//托盘是否占用
            arrangeBillBoxModel.acceptEmpId = isrtEmpId;//收货人
            arrangeBillBoxModel.acceptDt = System.DateTime.Now.ToString();//收货时间
            arrangeBillBoxModel.factNum = factNum;//收货数量

            arrangeBillBoxBB.AddRecord(arrangeBillBoxModel);

            //锁定库位
            commBB.ExecuteSql("update dbo.LWareLocator set isUsing=1 where wareLocatorNo='" + strWareLocatorNo + "'");
        }
        finally
        {
            arriveDetailBB.Dispose();
            arrangeBillBoxBB.Dispose();
            commBB.Dispose();
        }
    }

    public void FinishTSArriveDetail(int arriveDetailId)
    {
        BArriveDetailBB arriveDetailBB = new BArriveDetailBB();
        SCommBB commBB = new SCommBB();

        try
        {
            BArriveDetailData arriveDetailModel = new BArriveDetailData();
            object obj = null;
            string strArriveBillNo = "", strFinanceBillNo = "", strMaterialNo = "";

            arriveDetailModel = arriveDetailBB.GetModel(arriveDetailId);
            arriveDetailModel.isFinishReceive = true;//收货完成

            arriveDetailBB.ModifyRecord(arriveDetailModel);

            //更改到货单明细的排托数量
            strArriveBillNo = arriveDetailModel.arriveBillNo;
            strFinanceBillNo = arriveDetailModel.financeBillNo;
            strMaterialNo = arriveDetailModel.materialNo;

            obj = commBB.ExecuteScalar("select count(1) from dbo.BArrangeBillBox where arriveBillNo='"
                + strArriveBillNo + "' and financeBillNo='" + strFinanceBillNo + "' and materialNo='" + strMaterialNo + "'");
            if (obj != null)
            {
                commBB.ExecuteSql("update dbo.BarriveDetail set boxNum=" + obj.ToString() + " where id=" + arriveDetailId.ToString());
            }
        }
        finally
        {
            arriveDetailBB.Dispose();
            commBB.Dispose();
        }
    }