SM3407btnZSBCAP

后台

using ClsPub;
using RDIFramework.Utilities;
using Salien.Utility;
using Salien.Utility.SUWF;
using System;
using System.Collections.Generic;
using System.Data;

namespace SM3407btnZSBCAP
{
    public class SM3407btnZSBCAP : ISuwfBus
    {
        /*SLSH34237031111ZSBCAP.dll*/
        private SlnSuwfPage _page;
        void ISuwfBus.Initial(SlnSuwfPage page)
        {
            _page = page;
           PUB.BindButtonEvent(this._page, "btnZSBCAP", new EventHandler(BtnZSBCAP_Click));           
        }
        #region BtnZSBCAP_Click    
        private void BtnZSBCAP_Click(object sender, EventArgs e)
        {
            string PK = _page.GetRowDataPrimaryKeys();/*BCAPID*/
            if (PK.Length == 0 || PK.IndexOf(",") >= 0)
            {
                _page.ClientScript.RegisterClientScriptBlock(base.GetType(), "Alert", "<script>alert('请只选择一行网格数据！');</script>");
            }
            else
            {
                if (_page.GetRowDataByKey("ZT") != "0")
                {
                    _page.ShowMessage("此班次已经安排生产了");
                    return;
                }
                SM_ZSBCAP_SP(PK);
                SlnDataAccess.ExecuteSQL("update SM_SC_ZSBCAP_TB set ZT=1 where ID='" + PK + "'");
                _page.btnQuery_Click(null, null);
                _page.Response.Write("<Script>window.open(\"GridCardPage2.aspx?PU=2202&BCAPID=" + PK + "\", \"_blank\")</script>");
            }
        }
        #endregion
        /// <summary>
        /// 返回标准模数
        /// </summary>
        /// <param name="MJID"></param>
        /// <param name="MJBH"></param>
        /// <param name="dr"></param>
        /// <returns></returns>
        private string ReturnBZQS(string MJID, string MJBH, DataRow dr)
        {
            return SlnString.IsNull(MJID) == true ? GetBZQSByMJBH(MJBH) : ConvertFunction.ObjToStr(dr["MX_QS"]);
        }

        private string ReturnHSGS(string LJ_LSH, string JT_LSH)
        {
            return HasDWHS(LJ_LSH, JT_LSH) == "0" ? "0" : GetStrBySQL("select nvl(HSGS,0) HSGS  from sm_zdb_dwhs_tb where WLID = "
                                                         + LJ_LSH + "  and  jtid = " + JT_LSH);
        }

        #region private void SM_ZSBCAP_SP(string PK)
        private void SM_ZSBCAP_SP(string PK)
        {
            DataTable dt = SlnDataAccess.GetDataTable("select BMID,RQ,BC From sm_sc_zsbcap_tb " +
                           " Where id= '" + PK + "'");

            string BMID = PUB.ToSafeString(dt.Rows[0]["BMID"]);
            string RQ = PUB.ToSafeString(dt.Rows[0]["RQ"]);
            string BC = PUB.ToSafeString(dt.Rows[0]["BC"]);
            dt = GetDTMJDTByBMID(BMID);

            if (dt == null || dt.Rows.Count == 0)
            {
                _page.Response.Write(SlnString.ScriptAlertMessage("【sm_sc_zsbcap_tb】中没有记录"));
                return;
            }
            foreach (DataRow dr in dt.Rows)
            {
                string JTID = PUB.ToSafeString(dr["JTID"]);
                string MJID = PUB.ToSafeString(dr["MJID"]);
                string MJBH = PUB.ToSafeString(dr["MJBH"]);
                string ZDBZ = ConvertFunction.ObjToStr(dr["ZDBZ"]);
                string BZQS = ReturnBZQS(MJID, MJBH, dr);
                string ZSMS = ConvertFunction.ObjToStr(dr["MX_ZSMS"]);
                string MXGID = GetStrBySQL("select SUM(MXGID) From SM_JT_MJ_TB Where JTID =" + JTID);

                if (HasJTMJJL(PK, JTID) != "0")
                {
                    _page.ShowMessage("【SM_SC_ZSBCRWB_TB】中已有记录");
                    return;
                }
                string SEQZSBCRWB = SlnDataAccess.GetSeqNumberByTable("SM_SC_ZSBCRWB_TB").ToString();
                AddZSBCRWB_Info(SEQZSBCRWB, JTID, MJID, PK, BC, MXGID, MJBH, ZDBZ, RQ);
                DataTable DTByBMID_JTID_RQ = GetDTByBMID_JTID_RQ(BMID, RQ, JTID);
                #region foreach
                foreach (DataRow dataRow2 in DTByBMID_JTID_RQ.Rows)
                {
                    string JTLJID_LSH = PUB.ToSafeString(dataRow2["JTLJID0"]);
                    string JT_LSH = PUB.ToSafeString(dataRow2["JTID0"]);
                    string MJ_LSH = PUB.ToSafeString(dataRow2["MJID0"]);
                    string LJ_LSH = PUB.ToSafeString(dataRow2["LJID0"]);
                    string HSGS = ReturnHSGS(LJ_LSH, JT_LSH);
                    if (HasJTJL(PK, JT_LSH, LJ_LSH) == "0")
                    {
                        AddZSBCRW_Info(PK, MJID, HSGS, "0", "0", "0", ZSMS, BZQS, JT_LSH, LJ_LSH, JTLJID_LSH, SEQZSBCRWB, MXGID, ZDBZ);
                        if (HasDDJL(JT_LSH, LJ_LSH, PK, RQ) != "0")
                        {
                            DataTable DTByJT_LJ_BC_RQ = GetDTByJT_LJ_BC_RQ(JT_LSH, LJ_LSH, PK, RQ);

                            foreach (DataRow dataRow3 in DTByJT_LJ_BC_RQ.Rows)
                            {
                                string ZSBCRWID = PUB.ToSafeString(dataRow3["IDD"]);
                                string JTDD_LS = PUB.ToSafeString(dataRow3["JTDDID"]);
                                string SCSL = PUB.ToSafeString(dataRow3["SCSL"]);
                                BC = PUB.ToSafeString(dataRow3["BC"]);
                                DataTable dataTable6 = GetDTByJTDD_RQ(JTDD_LS, RQ);

                                string RKSL_HJ = PUB.ToSafeString(dataTable6.Rows[0]["RKSL_HJ"]);
                                string RBSL_HJ = PUB.ToSafeString(dataTable6.Rows[0]["RBSL_HJ"]);
                                if (BC == "1")
                                {
                                    string RBSLYE = Convert.ToString(Convert.ToDecimal(SCSL) - Convert.ToDecimal(RBSL_HJ));
                                    string RKSLYE = Convert.ToString(Convert.ToDecimal(SCSL) - Convert.ToDecimal(RKSL_HJ));
                                    if (Convert.ToDecimal(RBSLYE) >= 0m && Convert.ToDecimal(RKSLYE) >= 0m)
                                    {
                                        AddZSBCRWMX_Info(ZSBCRWID, JTDD_LS, SCSL, RBSLYE, RKSLYE);
                                    }
                                    else if (Convert.ToDecimal(RBSLYE) >= 0m && Convert.ToDecimal(RKSLYE) < 0m)
                                    {
                                        AddZSBCRWMX_Info(ZSBCRWID, JTDD_LS, SCSL, RBSLYE, "0");
                                    }
                                    else if (Convert.ToDecimal(RBSLYE) < 0m && Convert.ToDecimal(RKSLYE) >= 0m)
                                    {
                                        AddZSBCRWMX_Info(ZSBCRWID, JTDD_LS, SCSL, "0", RKSLYE);
                                    }
                                    else
                                    {
                                        AddZSBCRWMX_Info(ZSBCRWID, JTDD_LS, SCSL, "0", "0");
                                    }
                                }
                                else if (BC == "0")
                                {
                                    DataTable dataTable7 = GetDTByRQ_JTDD(RQ, JTDD_LS);
                                    /*    DataTable dataTable7 = SlnDataAccess.GetDataTable(@"select nvl(sum(t.RKSL),0) RKSL, 
                        nvl(sum(t.RBSL),0) RBSL  from SM_SC_ZSBCRWMX_TB t  
                    where exists  (select w.id from SM_SC_ZSBCRW_TB u, SM_SC_ZSBCAP_TB v, SM_SC_ZSBCRWMX_TB w 
                    where u.bcapid = v.id and w.bcrwid = u.idd  and t.id = w.id and v.bc=1 and v.rq = "
                                     + ToSql(RQ) + " and w.JTDDID=" + JTDD_LS + ")");*/
                                    string RBSL_SUM = PUB.ToSafeString(dataTable7.Rows[0]["RBSL"]);
                                    string RKSL_SUM = PUB.ToSafeString(dataTable7.Rows[0]["RKSL"]);
                                    string RBSLYE_HJ = Convert.ToString(Convert.ToDecimal(SCSL) - Convert.ToDecimal(RBSL_HJ) - Convert.ToDecimal(RBSL_SUM));
                                    string RKSLYE_HJ = Convert.ToString(Convert.ToDecimal(SCSL) - Convert.ToDecimal(RKSL_HJ) - Convert.ToDecimal(RKSL_SUM));
                                    if (Convert.ToDecimal(RBSLYE_HJ) >= 0m && Convert.ToDecimal(RKSLYE_HJ) >= 0m)
                                    {
                                        AddZSBCRWMX_Info(ZSBCRWID, JTDD_LS, SCSL, RBSLYE_HJ, RKSLYE_HJ);
                                    }
                                    else if (Convert.ToDecimal(RBSLYE_HJ) >= 0m && Convert.ToDecimal(RKSLYE_HJ) < 0m)
                                    {
                                        AddZSBCRWMX_Info(ZSBCRWID, JTDD_LS, SCSL, RBSLYE_HJ, "0");
                                    }
                                    else if (Convert.ToDecimal(RBSLYE_HJ) < 0m && Convert.ToDecimal(RKSLYE_HJ) >= 0m)
                                    {
                                        AddZSBCRWMX_Info(ZSBCRWID, JTDD_LS, SCSL, "0", RKSLYE_HJ);
                                    }
                                    else
                                    {
                                        AddZSBCRWMX_Info(ZSBCRWID, JTDD_LS, SCSL, "0", "0");
                                    }
                                }
                            }
                        }
                    }
                }
                #endregion

            }


        }
        #endregion

        private DataTable GetDTByRQ_JTDD(string RQ, string JTDD_LS)
        {
            return SlnDataAccess.GetDataTable(@"select nvl(sum(t.RKSL),0) RKSL, 
                        nvl(sum(t.RBSL),0) RBSL  from SM_SC_ZSBCRWMX_TB t  
                    where exists  (select w.id from SM_SC_ZSBCRW_TB u, SM_SC_ZSBCAP_TB v, SM_SC_ZSBCRWMX_TB w 
                    where u.bcapid = v.id and w.bcrwid = u.idd  and t.id = w.id and v.bc=1 and v.rq = "
                     + ToSql(RQ) + " and w.JTDDID=" + JTDD_LS + ")");

        }

        #region GetDTMJDTByBMID
        private DataTable GetDTMJDTByBMID(string BMID)
        {
            string sql = "select ZPZDJ.ID as JTID,";
            sql += "JTMJDY.MJID,MJZD.MX_QS,MJZD.MX_ZSMS,JTMJDY.MJBH as MJBH,MJZD.ZDBZ";
            sql += " from SM_JC_ZPZDJML_TB ZPZDJ";
            sql += " left join SM_JT_MJ_TB JTMJDY on ZPZDJ.ID = JTMJDY.JTID";
            sql += " left join SM_MJ_MX_TB MJZD on MJZD.MX_ID = JTMJDY.MJID ";
            sql += " where ZPZDJ.SBLB = 0 and ZPZDJ.JTZTBH = 4";
            sql += " and ZPZDJ.ZYDLID =" + BMID + "";
            return SlnDataAccess.GetDataTable(sql);
        }
        #endregion

        #region GetDTByJTDD_RQ
        private DataTable GetDTByJTDD_RQ(string JTDD_LS, string RQ)
        {
            string sql = @"select nvl(sum(t.RKSL),0) as RKSL_HJ,
                         nvl(sum(t.RBSL),0) as RBSL_HJ from SM_SC_ZSBCRWMX_TB t 
                     where exists  (select 1 from SM_SC_ZSBCRW_TB u,SM_SC_ZSBCAP_TB v, SM_SC_ZSBCRWMX_TB w 
                       where u.bcapid = v.id and w.bcrwid = u.idd  and w.id = t.id  and w.JTDDID=" 
+ JTDD_LS + " and v.rq<" +  ToSql(RQ) + ")";
            return SlnDataAccess.GetDataTable(sql);
        }
        #endregion

        #region GetDTByBMID_JTID_RQ
        private DataTable GetDTByBMID_JTID_RQ(string BMID, string RQ, string JTID)
        {
            string sql = @"select u.ID as JTLJID0,t.JTID as JTID0,
                                              t.MJID as MJID0,u.LJID as LJID0  
                                       from SM_SC_ZSJTLJ_TB u,SM_SC_ZSJTMJ_TB t ,sm_sc_zsjtdd_tb v 
                                        where t.ID=u.JTMJID and t.RWZTBH=0 and t.JTZTBH in (1,2) 
                                       and u.id = v.jtljid and V.DDZTBH = 1 and u.LJZTBH in (1) 
                                       and t.BMID = '" + BMID + "'" +
                                            " and t.SJKGSJ <= " + SlnDataAccess.DbFormat(RQ, "DATE")
                                           + " and JTID = " + JTID;
            //ClsLog2.WriteLog2(_page.GetValueByName("PU"), "btnZSBCAP", sql);
            return SlnDataAccess.GetDataTable(sql);
        }
        #endregion


        #region GetDTByBMID_JTID_RQ
        private DataTable GetDTByJT_LJ_BC_RQ(string JT_LSH, string LJ_LSH, string BCAPID, string RQ)
        {
            string sql = @"select y.IDD, v.ID as JTDDID, v.SCSL, u.BC  
                            from SM_SC_ZSJTLJ_TB t, SM_SC_ZSJTDD_TB v,
                                SM_SC_ZSJTMJ_TB x, SM_SC_ZSBCRW_TB y, SM_SC_ZSBCAP_TB u  
                                Where x.id=t.jtmjid and t.id=v.jtljid and x.bmid=u.bmid and x.jtid=y.jtid   
                            and t.ljid = y.ljid and u.id = y.bcapid and y.jtid= "
                                                        + JT_LSH + " and y.ljid= " + LJ_LSH + "  and y.bcapid = " + BCAPID
                                                        + " and v.ddztbh = 1 and x.SJKGSJ< = " + ToSql(RQ)
                                                        + " and v.scddid is not null  order by v.jhrq";

            //ClsLog2.WriteLog2(_page.GetValueByName("PU"), "btnZSBCAP", sql);
            return SlnDataAccess.GetDataTable(sql);
        }
        #endregion
        #region 
        private string GetBZQSByMJBH(string MJBH)
        {
            DataTable dataTable2 = SlnDataAccess.GetDataTable("select nvl(MX_QS,'') MX_QS "
                                                                 + " from SM_MJ_JHGD_TB where MX_BH="
                                                                 + ObjToSqlChar(MJBH, "s"));
            return dataTable2.Rows.Count == 0 ? "0" : dataTable2.Rows[0]["MX_QS"].ToString();
        }
        #endregion

        #region 字符串转换成sql字符，""则返回null，否则返回指定的类型
        /// <summary>
        ///  字符串转换成sql字符，""则返回null，否则返回指定的类型
        /// </summary>
        /// <param name="str">要转换的字符串</param>
        /// <param name="strLX">sql类型，"d"表示时间,"n"表示数字,"s"表示字符串</param>
        /// <returns>""则返回null，否则返回指定的类型</returns>
        public  string ObjToSqlChar(string str, string strLX)//转换成SQL字符
        {
            string temp;
            temp = str;
            if (temp == "" || temp == null || temp.Trim() == "")
            {
                return "";
            }

            switch (strLX)
            {
                case "s":
                    {
                        temp = "'" + temp.Trim() + "'";
                        break;
                    }
                case "d":
                    {
                        temp = "to_date('" + temp.Trim() + "','yyyy-mm-dd hh24:mi:ss')";
                        break;
                    }
            }
            return temp;
        }
        #endregion

        #region 插入班次任务表 SM_SC_ZSBCRWB_TB
        /// <summary>
        /// 插入班次任务表 SM_SC_ZSBCRWB_TB
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="JTID"></param>
        /// <param name="MJID"></param>
        /// <param name="BCAPID"></param>
        /// <param name="BC"></param>
        /// <param name="MXGID"></param>
        /// <param name="MJBH"></param>
        /// <param name="ZDBZ"></param>
        /// <param name="RQ"></param>
        private void AddZSBCRWB_Info(string ID, string JTID, string MJID, string BCAPID,
            string BC, string MXGID, string MJBH, string ZDBZ, string RQ)
        {
            string sql = "insert into SM_SC_ZSBCRWB_TB(ID,JTID,MJID,BCAPID,BC,MXGID,MJBH,ZDBZ,RQ,ZDRQ) select ";
            List<string> aList = new List<string>
            {
                ID,
                JTID,
                MJID,
                BCAPID,
                BC,
                MXGID,
                MJBH,
                ZDBZ
            };
            sql += BusinessLogic.ObjectsToList(aList.ToArray());
            sql += "," + ToSql(RQ) + ",sysdate from dual";
            try
            {
                SlnDataAccess.ExecuteSQL(sql);
            }
            catch (Exception ex)
            {
                _page.ShowMessage(ex.Message);
                //ClsLog2.WriteLog2(_page.GetValueByName("PU"), "btnZSBCAP", ex.Message);
            }
        }
        #endregion
        private string ToSql(string sql)
        {
            return ObjToSqlChar(sql, "d");
        }

        private string HasJTMJJL(string PK, string JTID)
        {
            string sql = "select count(*) from SM_SC_ZSBCRWB_TB where JTID = " + JTID + " and BCAPID = " + PK;
            return GetStrBySQL(sql);
        }

        private string HasDWHS(string LJID, string JTID)
        {
            string sql = "select count(id)  from sm_zdb_dwhs_tb  where wlid = " + LJID + " and  jtid = " + JTID;
            return GetStrBySQL(sql);
        }
        private string HasJTJL(string PK, string JTID, string LJID)
        {
            string sql = " select count(t.IDD)  from SM_SC_ZSBCRW_TB t where t.JTID =";
            sql += JTID + " and t.ljid = ";
            sql += LJID + " and t.bcapid=";
            sql += PK;
            return GetStrBySQL(sql);
        }

        #region 插入班次任务记录 SM_SC_ZSBCRW_TB
        private void AddZSBCRW_Info(string BCAPID, string MJID, string HSGS, string RKZT, string CKZT, string FLZT, string BZMS, string BZQS, string JTID, string LJID, string JTLJID, string BCRWBID, string MXGID, string ZDBZ)
        {
            string sql = "insert into SM_SC_ZSBCRW_TB(IDD,BCAPID,MJID,HSGS,RKZT,CKZT,FLZT," +
                " BZMS,BZQS,JTID,LJID,JTLJID,BCRWBID,MXGID,JTLX,ZDRQ) " +
                " select SEQSM_SC_ZSBCRW.NEXTVAL,";
            List<string> alist = new List<string>
            {
                BCAPID,
                MJID,
                HSGS,
                RKZT,
                CKZT,
                FLZT,
                BZMS,
                BZQS,
                JTID,
                LJID,
                JTLJID,
                BCRWBID,
                MXGID,
                ZDBZ
            };
            sql += BusinessLogic.ObjectsToList(alist.ToArray()) + ",sysdate from dual";
            try
            {
                SlnDataAccess.ExecuteSQL(sql);
            }
            catch (Exception ex)
            {
                _page.ShowMessage(ex.Message);
                //ClsLog2.WriteLog2(_page.GetValueByName("PU"), "btnZSBCAP", ex.Message);
            }
        }

        #endregion

        private string HasDDJL(string JTID, string LJID, string BCAPID, string RQ)
        {
            string sql = "select count(t.id)  from SM_SC_ZSJTLJ_TB t, SM_SC_ZSJTDD_TB v, SM_SC_ZSJTMJ_TB x,";
            sql += "SM_SC_ZSBCRW_TB y, SM_SC_ZSBCAP_TB u ";
            sql += "  where x.id = t.jtmjid and t.id = v.jtljid and x.bmid = u.bmid and x.jtid = y.jtid";
            sql += "  and t.ljid = y.ljid and u.id = y.bcapid and y.jtid =";
            sql += JTID + " and y.ljid=";
            sql += LJID + " and y.bcapid =";
            sql += BCAPID + " and v.ddztbh = 1  and v.scddid is not null and x.SJKGSJ< = " + SlnDataAccess.DbFormat(RQ, "DATE");

            return GetStrBySQL(sql);
        }
        #region 添加任务明细记录
        private void AddZSBCRWMX_Info(string BCRWID, string JTDDID, string SCSL, string SYJHSL, string SYSCSL)
        {
            string sql = " insert into SM_SC_ZSBCRWMX_TB (ID,BCRWID,JTDDID,SCSL,SYJHSL,SYSCSL,zdrq) select SEQSM_SC_ZSBCRWMX.NEXTVAL,";

            List<string> aList = new List<string>
            {
                BCRWID,
                JTDDID,
                SCSL,
                SYJHSL,
                SYSCSL
            };
            sql += BusinessLogic.ObjectsToList(aList.ToArray());
            sql += ",sysdate from dual";
            try
            {
                SlnDataAccess.ExecuteSQL(sql);
            }
            catch (Exception ex)
            {
                _page.ShowMessage(ex.Message);
                //ClsLog2.WriteLog2(_page.GetValueByName("PU"), "btnZSBCAP", ex.Message);            
            }
        }
        #endregion

        private string GetStrBySQL(string sql)
        {
            return ConvertFunction.ObjToStr(SlnDataAccess.GetValueBySql(sql));
        }
    }
}