using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using GThinking.N_MISPRO; using GThinking.N_MISPRO.Data; using GThinking.N_MODEL.Page; using GThinking.N_MISPRO.Utility; using GThinking.N_CONTROL; using System.Collections.Generic; using System.Text; namespace GThinking.U20261363_N_XLXSBJ { [EntityType(Db = "DBERP", Table = "XLXSBJDCLHQ", IsWf = false, IsWhr = true, IsAttach = false)] public class XLBJD_CLHQTZ : EntityObject { public string sBJDID = ""; public string sVID = ""; public string sRYID = ""; public string sBJRQ = ""; public string sBJFLID = ""; public string sJGBID = ""; public string sJGBIDS = ""; public string sBJDHQLY = ""; public bool bHSBZ = true; public string sTBJCLID = ""; public string sLBJCLID = ""; public string sTJ = ""; public string sLJ = ""; public string sBJU057 = "N"; public override TSQL SetQuerySql() { TSQL SQL = base.SetQuerySql(); if (!XLBJDUtils.IsJGEJGB(sJGBID))//表价实时计算价格表 { if (sBJDHQLY.Equals("1")) { if (!sJGBIDS.Equals("")) { SQL.Add(GetBJDHQ_VSql(sJGBIDS).ToString()); if (sBJU057.Trim().Equals("Y")) { SQL.Add(" where isnull(BJCL_PX.XLJGCZBLM_BJCLID,' ') <> ' ' "); } SQL.Add(" order by px0,px,px1,isnull(XLXSBJCL_TYBZ,'N'),XLXSBJCL_BJCLID"); SQL.ParamByName("pJGBIDS").Value = sJGBIDS; } else { SQL.Add(GetBJDHQ_VSql().ToString()); if (sBJU057.Trim().Equals("Y")) { SQL.Add(" where isnull(BJCL_PX.XLJGCZBLM_BJCLID,' ') <> ' ' "); } SQL.Add(" order by px0,px,isnull(XLXSBJCL_TYBZ,'N'),XLXSBJCL_BJCLID"); } SQL.ParamByName("pBJDID").Value = sBJDID; SQL.ParamByName("pVID").Value = sVID; SQL.ParamByName("pRYID").Value = sRYID; SQL.ParamByName("pBJRQ").Value = sBJRQ; SQL.ParamByName("pBJFLID").Value = sBJFLID; SQL.ParamByName("pJGBID").Value = sJGBID; } else { SQL.Add(GetBJRHQ_VSql().ToString()); if (sBJU057.Trim().Equals("Y")) { SQL.Add(" where isnull(BJCL_PX.XLJGCZBLM_BJCLID,' ') <> ' ' "); } SQL.Add(" order by px0,px,isnull(XLXSBJCL_TYBZ,'N'),XLXSBJCL_BJCLID"); SQL.ParamByName("pBJDID").Value = sBJDID; SQL.ParamByName("pVID").Value = sVID; SQL.ParamByName("pRYID").Value = sRYID; SQL.ParamByName("pBJRQ").Value = sBJRQ; } } else { SQL.Add(GetJGEHQ_VSql(bHSBZ, sTBJCLID, sLBJCLID, sTJ, sLJ).ToString()); if (sBJU057.Trim().Equals("Y")) { SQL.Add(" where isnull(BJCL_PX.XLJGCZBLM_BJCLID,' ') <> ' ' "); } SQL.Add(" order by px0,px,isnull(XLXSBJCL_TYBZ,'N'),XLXSBJCL_BJCLID"); SQL.ParamByName("pBJDID").Value = sBJDID; SQL.ParamByName("pVID").Value = sVID; SQL.ParamByName("pRYID").Value = sRYID; SQL.ParamByName("pBJRQ").Value = sBJRQ; } return SQL; } public override bool Querying(TSQL sql) { Entites.Columns.Clear(); Entites.Clear(); return base.Querying(sql); } /// /// XLXSBJDCLHQ数据可能还没有的情况下，构建报价单行情sql /// /// public static StringBuilder GetBJDHQSql() { StringBuilder strSQL = new StringBuilder(); strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 1 ELSE 0 end as px, case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 'N' ELSE 'Y' end as SYF, "); strSQL.AppendLine(" XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else XLXSBJDCLHQ_WSJG end as CLJG,"); strSQL.AppendLine("XLXSBJCL_ZZSL,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else XLXSBJDCLHQ_HSJG end as HSJG,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else XLXSBJDCLHQ_WSJGF end as WSJGF,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else XLXSBJDCLHQ_HSJGF end as HSJGF,"); strSQL.AppendLine(" 0 as SYJGB_HSJG, 0 as px1, "); strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, "); strSQL.AppendLine("XLXSBJDCLHQ_WHR,XLXSBJDCLHQ_WHRID,XLXSBJDCLHQ_WHSJ, "); strSQL.AppendLine(" (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID "); strSQL.AppendLine(" from XLXSBJCL"); strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID "); strSQL.AppendLine(" left join XLXSBJDCLHQ on XLXSBJDCLHQ_BJDID=:pBJDID and XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID"); ///--------------------------------------------------------------------------- strSQL.AppendLine(" left join "); strSQL.AppendLine(" ("); //报价单报价日之材料行情 strSQL.AppendLine(" select BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF "); strSQL.AppendLine(" from XLXSCLHQ,"); strSQL.AppendLine(" ("); strSQL.AppendLine(" select XLXSCLHQ_BJCLID as BJCLID,"); strSQL.AppendLine(" max(XLXSCLHQ_YXQS)as maxYXQS"); strSQL.AppendLine(" from XLXSCLHQ,XLXSBJCL"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_YXQS<=:pBJRQ"); strSQL.AppendLine(" and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)"); strSQL.AppendLine(" and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID"); strSQL.AppendLine(" and exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//报价日行情之报价材料 strSQL.AppendLine(" group by XLXSCLHQ_BJCLID"); strSQL.AppendLine(" ) x"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_BJCLID=BJCLID"); strSQL.AppendLine(" and XLXSCLHQ_YXQS=maxYXQS"); strSQL.AppendLine(" union all "); //报价单非报价日之材料行情 strSQL.AppendLine(" select XLXSCLHQ_BJCLID as BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF "); strSQL.AppendLine(" from XLXSCLHQ,XLXSBJCL"); strSQL.AppendLine(" where XLXSCLHQ_JGBID<>'ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_JGBID=:pJGBID"); strSQL.AppendLine(" and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID"); strSQL.AppendLine(" and not exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//非报价日行情的报价材料 strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID"); ///--------------------------------------------------------------------------- //关联使用的材料，提上显示 strSQL.AppendLine(" LEFT JOIN"); strSQL.AppendLine(" ("); strSQL.AppendLine(" SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX"); strSQL.AppendLine(" LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') "); //利用版本限制 strSQL.AppendLine(" LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLJGCZBLM_JGCZBBBID = XLXSCLQD_JGCZBBBID "); strSQL.AppendLine(" WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'"); strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID "); //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID"); return strSQL; } public static StringBuilder GetBJDHQ_VSql() { return GetBJDHQ_VSql(""); } public static StringBuilder GetBJDHQ_VSql(string sJGBIDS) { StringBuilder strSQL = new StringBuilder(); strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 1 ELSE 0 end as px, case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 'N' ELSE 'Y' end as SYF, "); strSQL.AppendLine(" XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else V_XLXSBJDCLHQ_WSJG end as CLJG,"); strSQL.AppendLine("XLXSBJCL_ZZSL,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else V_XLXSBJDCLHQ_HSJG end as HSJG,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else V_XLXSBJDCLHQ_WSJGF end as WSJGF,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else V_XLXSBJDCLHQ_HSJGF end as HSJGF,"); if (!sJGBIDS.Equals("")) { strSQL.AppendLine(" isnull(e.XLXSCLHQ_HSJG,0.0) as SYJGB_HSJG, case when isnull(bjdclhq.XLXSCLHQ_HSJG,0.0) <> isnull(e.XLXSCLHQ_HSJG,0.0) then 0 else 1 end as px1, "); } else { strSQL.AppendLine(" 0 as SYJGB_HSJG, 0 as px1, "); } strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, "); strSQL.AppendLine("isnull(V_XLXSBJDCLHQ_JGYBJCLID1,' ') as V_XLXSBJDCLHQ_JGYBJCLID1, isnull(V_XLXSBJDCLHQ_JGBL1,0.0) as V_XLXSBJDCLHQ_JGBL1, "); strSQL.AppendLine("V_XLXSBJDCLHQ_WHR,V_XLXSBJDCLHQ_WHRID,V_XLXSBJDCLHQ_WHSJ, "); strSQL.AppendLine(" (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID "); strSQL.AppendLine(" from XLXSBJCL"); strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID "); strSQL.AppendLine(" left join V_XLXSBJDCLHQ on V_XLXSBJDCLHQ_BJDID=:pBJDID and V_XLXSBJDCLHQ_ID=:pVID and V_XLXSBJDCLHQ_TZRYID=:pRYID and V_XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID "); ///--------------------------------------------------------------------------- strSQL.AppendLine(" left join "); strSQL.AppendLine(" ("); //报价单报价日之材料行情 strSQL.AppendLine(" select BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF "); strSQL.AppendLine(" from XLXSCLHQ,"); strSQL.AppendLine(" ("); strSQL.AppendLine(" select XLXSCLHQ_BJCLID as BJCLID,"); strSQL.AppendLine(" max(XLXSCLHQ_YXQS)as maxYXQS"); strSQL.AppendLine(" from XLXSCLHQ,XLXSBJCL"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_YXQS<=:pBJRQ"); strSQL.AppendLine(" and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)"); strSQL.AppendLine(" and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID"); strSQL.AppendLine(" and exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//报价日行情之报价材料 strSQL.AppendLine(" group by XLXSCLHQ_BJCLID"); strSQL.AppendLine(" ) x"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_BJCLID=BJCLID"); strSQL.AppendLine(" and XLXSCLHQ_YXQS=maxYXQS"); strSQL.AppendLine(" union all "); //报价单非报价日之材料行情 strSQL.AppendLine(" select XLXSCLHQ_BJCLID as BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF "); strSQL.AppendLine(" from XLXSCLHQ,XLXSBJCL"); strSQL.AppendLine(" where XLXSCLHQ_JGBID<>'ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_JGBID=:pJGBID"); strSQL.AppendLine(" and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID"); strSQL.AppendLine(" and not exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//非报价日行情的报价材料 strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID"); if (!sJGBIDS.Equals("")) { ///---------------------------------------------------------------------------因素为1,'','' strSQL.AppendLine(" left join "); strSQL.AppendLine(" ("); //报价单报价日之材料行情 strSQL.AppendLine(" select BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF "); strSQL.AppendLine(" from XLXSCLHQ,"); strSQL.AppendLine(" ("); strSQL.AppendLine(" select XLXSCLHQ_BJCLID as BJCLID,"); strSQL.AppendLine(" max(XLXSCLHQ_YXQS)as maxYXQS"); strSQL.AppendLine(" from XLXSCLHQ,XLXSBJCL"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_YXQS<=:pBJRQ"); strSQL.AppendLine(" and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)"); strSQL.AppendLine(" and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID"); strSQL.AppendLine(" and exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//报价日行情之报价材料 strSQL.AppendLine(" group by XLXSCLHQ_BJCLID"); strSQL.AppendLine(" ) x"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_BJCLID=BJCLID"); strSQL.AppendLine(" and XLXSCLHQ_YXQS=maxYXQS"); strSQL.AppendLine(" union all "); //报价单非报价日之材料行情 strSQL.AppendLine(" select XLXSCLHQ_BJCLID as BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF "); strSQL.AppendLine(" from XLXSCLHQ,XLXSBJCL"); strSQL.AppendLine(" where XLXSCLHQ_JGBID<>'ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_JGBID=:pJGBIDS"); strSQL.AppendLine(" and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID"); strSQL.AppendLine(" and not exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//非报价日行情的报价材料 strSQL.AppendLine(" ) e on e.BJCLID=XLXSBJCL_BJCLID"); } ///--------------------------------------------------------------------------- //关联使用的材料，提上显示 strSQL.AppendLine(" LEFT JOIN"); strSQL.AppendLine(" ("); strSQL.AppendLine(" SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX"); strSQL.AppendLine(" LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') "); //利用版本限制 strSQL.AppendLine(" LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLJGCZBLM_JGCZBBBID = XLXSCLQD_JGCZBBBID "); strSQL.AppendLine(" WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'"); strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID "); //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID"); return strSQL; } public static StringBuilder GetBJRHQSql() { StringBuilder strSQL = new StringBuilder(); strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 1 ELSE 0 end as px, case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 'N' ELSE 'Y' end as SYF, "); strSQL.AppendLine(" XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else XLXSBJDCLHQ_WSJG end as CLJG,"); strSQL.AppendLine("XLXSBJCL_ZZSL,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else XLXSBJDCLHQ_HSJG end as HSJG,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else XLXSBJDCLHQ_WSJGF end as WSJGF,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else XLXSBJDCLHQ_HSJGF end as HSJGF,"); strSQL.AppendLine("0 as SYJGB_HSJG, 0 as px1,"); strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, "); strSQL.AppendLine("XLXSBJDCLHQ_WHR,XLXSBJDCLHQ_WHRID,XLXSBJDCLHQ_WHSJ, "); strSQL.AppendLine(" (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID "); strSQL.AppendLine(" from XLXSBJCL"); strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID "); strSQL.AppendLine(" left join XLXSBJDCLHQ on XLXSBJDCLHQ_BJDID=:pBJDID and XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID"); strSQL.AppendLine(" left join "); strSQL.AppendLine(" ("); strSQL.AppendLine("select BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF"); strSQL.AppendLine(" from XLXSCLHQ,"); strSQL.AppendLine(" ("); strSQL.AppendLine(" select XLXSCLHQ_BJCLID as BJCLID,"); strSQL.AppendLine(" max(XLXSCLHQ_YXQS)as maxYXQS"); strSQL.AppendLine(" from XLXSCLHQ,XLXSBJCL"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_YXQS<=:pBJRQ"); strSQL.AppendLine(" and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)"); strSQL.AppendLine(" and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID"); strSQL.AppendLine(" group by XLXSCLHQ_BJCLID"); strSQL.AppendLine(" ) x"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_BJCLID=BJCLID"); strSQL.AppendLine(" and XLXSCLHQ_YXQS=maxYXQS"); strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID"); //关联使用的材料，提上显示 strSQL.AppendLine(" LEFT JOIN"); strSQL.AppendLine(" ("); strSQL.AppendLine(" SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX"); strSQL.AppendLine(" LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') "); strSQL.AppendLine(" LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLXSCLQD_JGCZBBBID = XLJGCZBLM_JGCZBBBID "); strSQL.AppendLine(" WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'"); strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID "); //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID"); return strSQL; } public static StringBuilder GetBJRHQ_VSql() { StringBuilder strSQL = new StringBuilder(); strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 1 ELSE 0 end as px, case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 'N' ELSE 'Y' end as SYF, "); strSQL.AppendLine(" XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else V_XLXSBJDCLHQ_WSJG end as CLJG,"); strSQL.AppendLine("XLXSBJCL_ZZSL,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else V_XLXSBJDCLHQ_HSJG end as HSJG,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else V_XLXSBJDCLHQ_WSJGF end as WSJGF,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else V_XLXSBJDCLHQ_HSJGF end as HSJGF,"); strSQL.AppendLine("0 as SYJGB_HSJG, 0 as px1,"); strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, "); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_JGYBJCLID1 else isnull(V_XLXSBJDCLHQ_JGYBJCLID1,' ') end as V_XLXSBJDCLHQ_JGYBJCLID1,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_JGBL1 else isnull(V_XLXSBJDCLHQ_JGBL1,0.0) end as V_XLXSBJDCLHQ_JGBL1,"); //strSQL.AppendLine("isnull(V_XLXSBJDCLHQ_JGYBJCLID1,' ') as V_XLXSBJDCLHQ_JGYBJCLID1, isnull(V_XLXSBJDCLHQ_JGBL1,0.0) as V_XLXSBJDCLHQ_JGBL1, "); strSQL.AppendLine("V_XLXSBJDCLHQ_WHR,V_XLXSBJDCLHQ_WHRID,V_XLXSBJDCLHQ_WHSJ, "); strSQL.AppendLine(" (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID "); strSQL.AppendLine(" from XLXSBJCL"); strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID "); strSQL.AppendLine(" left join V_XLXSBJDCLHQ on V_XLXSBJDCLHQ_BJDID=:pBJDID and V_XLXSBJDCLHQ_ID=:pVID and V_XLXSBJDCLHQ_TZRYID=:pRYID and V_XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID "); strSQL.AppendLine(" left join "); strSQL.AppendLine(" ("); strSQL.AppendLine("select BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF,XLXSCLHQ_JGYBJCLID1,XLXSCLHQ_JGBL1 "); strSQL.AppendLine(" from XLXSCLHQ,"); strSQL.AppendLine(" ("); strSQL.AppendLine(" select XLXSCLHQ_BJCLID as BJCLID,"); strSQL.AppendLine(" max(XLXSCLHQ_YXQS)as maxYXQS"); strSQL.AppendLine(" from XLXSCLHQ,XLXSBJCL"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_YXQS<=:pBJRQ"); strSQL.AppendLine(" and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)"); strSQL.AppendLine(" and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID"); strSQL.AppendLine(" group by XLXSCLHQ_BJCLID"); strSQL.AppendLine(" ) x"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_BJCLID=BJCLID"); strSQL.AppendLine(" and XLXSCLHQ_YXQS=maxYXQS"); strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID"); //关联使用的材料，提上显示 strSQL.AppendLine(" LEFT JOIN"); strSQL.AppendLine(" ("); strSQL.AppendLine(" SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX"); strSQL.AppendLine(" LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') "); strSQL.AppendLine(" LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLXSCLQD_JGCZBBBID = XLJGCZBLM_JGCZBBBID "); strSQL.AppendLine(" WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'"); strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID "); //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID"); return strSQL; } public static StringBuilder GetJGEHQSql(bool bHSBZ, string sTBJCLID, string sLBJCLID, string sTJ, string sLJ) { StringBuilder strSQL = new StringBuilder(); strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 1 ELSE 0 end as px,case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 'N' ELSE 'Y' end as SYF,"); strSQL.AppendLine("XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else XLXSBJDCLHQ_WSJG end as CLJG,"); strSQL.AppendLine("XLXSBJCL_ZZSL,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else XLXSBJDCLHQ_HSJG end as HSJG,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else XLXSBJDCLHQ_WSJGF end as WSJGF,"); strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else XLXSBJDCLHQ_HSJGF end as HSJGF,"); strSQL.AppendLine("0 as SYJGB_HSJG, 0 as px1,"); strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, "); strSQL.AppendLine("XLXSBJDCLHQ_WHR,XLXSBJDCLHQ_WHRID,XLXSBJDCLHQ_WHSJ, "); strSQL.AppendLine(" (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID "); strSQL.AppendLine(" from XLXSBJCL"); strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID "); strSQL.AppendLine(" left join XLXSBJDCLHQ on XLXSBJDCLHQ_BJDID=:pBJDID and XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID"); strSQL.AppendLine(" left join "); strSQL.AppendLine(" ("); strSQL.AppendLine("select BJCLID,"); if (bHSBZ) { strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_HSJGF", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_HSJGF", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(" else XLXSCLHQ_HSJG end as XLXSCLHQ_HSJG, "); strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}/(1+ZZSL/100.0)", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}/(1+ZZSL/100.0)", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}/(1+ZZSL/100.0)+XLXSCLHQ_WSJGF", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}/(1+ZZSL/100.0)+XLXSCLHQ_WSJGF", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(" else XLXSCLHQ_WSJG end as XLXSCLHQ_WSJG, "); strSQL.AppendLine(" XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF "); } else { strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_WSJGF", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_WSJGF", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(" else XLXSCLHQ_WSJG end as XLXSCLHQ_WSJG, "); strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}*(1+ZZSL/100.0)", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}*(1+ZZSL/100.0)", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}*(1+ZZSL/100.0)+XLXSCLHQ_HSJGF", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}*(1+ZZSL/100.0)+XLXSCLHQ_HSJGF", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(" else XLXSCLHQ_HSJG end as XLXSCLHQ_HSJG, "); strSQL.AppendLine(" XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF "); } strSQL.AppendLine(" from XLXSCLHQ,"); strSQL.AppendLine(" ("); strSQL.AppendLine(" select XLXSCLHQ_BJCLID as BJCLID,XLXSBJCL_JGYBJCLID as JGYBJCLID,XLXSBJCL_ZZSL as ZZSL,"); strSQL.AppendLine(" max(XLXSCLHQ_YXQS)as maxYXQS"); strSQL.AppendLine(" from XLXSCLHQ,XLXSBJCL"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_YXQS<=:pBJRQ"); strSQL.AppendLine(" and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)"); strSQL.AppendLine(" and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID"); strSQL.AppendLine(" group by XLXSCLHQ_BJCLID,XLXSBJCL_JGYBJCLID,XLXSBJCL_ZZSL"); strSQL.AppendLine(" ) x"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_BJCLID=BJCLID"); strSQL.AppendLine(" and XLXSCLHQ_YXQS=maxYXQS"); strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID"); //关联使用的材料，提上显示 strSQL.AppendLine(" LEFT JOIN"); strSQL.AppendLine(" ("); strSQL.AppendLine(" SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX"); strSQL.AppendLine(" LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') "); strSQL.AppendLine(" LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLXSCLQD_JGCZBBBID = XLJGCZBLM_JGCZBBBID "); strSQL.AppendLine(" WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'"); strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID "); //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID"); return strSQL; } public static StringBuilder GetJGEHQ_VSql(bool bHSBZ, string sTBJCLID, string sLBJCLID, string sTJ, string sLJ) { StringBuilder strSQL = new StringBuilder(); strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 1 ELSE 0 end as px,case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN 'N' ELSE 'Y' end as SYF,"); strSQL.AppendLine("XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else V_XLXSBJDCLHQ_WSJG end as CLJG,"); strSQL.AppendLine("XLXSBJCL_ZZSL,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else V_XLXSBJDCLHQ_HSJG end as HSJG,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else V_XLXSBJDCLHQ_WSJGF end as WSJGF,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else V_XLXSBJDCLHQ_HSJGF end as HSJGF,"); strSQL.AppendLine("0 as SYJGB_HSJG, 0 as px1,"); strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, "); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_JGYBJCLID1 else isnull(V_XLXSBJDCLHQ_JGYBJCLID1,' ') end as V_XLXSBJDCLHQ_JGYBJCLID1,"); strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_JGBL1 else isnull(V_XLXSBJDCLHQ_JGBL1,0.0) end as V_XLXSBJDCLHQ_JGBL1,"); //strSQL.AppendLine("isnull(V_XLXSBJDCLHQ_JGYBJCLID1,' ') as V_XLXSBJDCLHQ_JGYBJCLID1, isnull(V_XLXSBJDCLHQ_JGBL1,0.0) as V_XLXSBJDCLHQ_JGBL1, "); strSQL.AppendLine("V_XLXSBJDCLHQ_WHR,V_XLXSBJDCLHQ_WHRID,V_XLXSBJDCLHQ_WHSJ, "); strSQL.AppendLine(" (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID "); strSQL.AppendLine(" from XLXSBJCL"); strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID "); strSQL.AppendLine(" left join V_XLXSBJDCLHQ on V_XLXSBJDCLHQ_BJDID=:pBJDID and V_XLXSBJDCLHQ_ID=:pVID and V_XLXSBJDCLHQ_TZRYID=:pRYID and V_XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID "); strSQL.AppendLine(" left join "); strSQL.AppendLine(" ("); strSQL.AppendLine("select BJCLID,XLXSCLHQ_JGYBJCLID1,XLXSCLHQ_JGBL1,"); if (bHSBZ) { strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_HSJGF", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_HSJGF", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(" else XLXSCLHQ_HSJG end as XLXSCLHQ_HSJG, "); strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}/(1+ZZSL/100.0)", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}/(1+ZZSL/100.0)", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}/(1+ZZSL/100.0)+XLXSCLHQ_WSJGF", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}/(1+ZZSL/100.0)+XLXSCLHQ_WSJGF", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(" else XLXSCLHQ_WSJG end as XLXSCLHQ_WSJG, "); strSQL.AppendLine(" XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF "); } else { strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_WSJGF", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_WSJGF", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(" else XLXSCLHQ_WSJG end as XLXSCLHQ_WSJG, "); strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}*(1+ZZSL/100.0)", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}*(1+ZZSL/100.0)", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}*(1+ZZSL/100.0)+XLXSCLHQ_HSJGF", sTBJCLID.Trim(), sTJ)); strSQL.AppendLine(string.Format(" when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}*(1+ZZSL/100.0)+XLXSCLHQ_HSJGF", sLBJCLID.Trim(), sLJ)); strSQL.AppendLine(" else XLXSCLHQ_HSJG end as XLXSCLHQ_HSJG, "); strSQL.AppendLine(" XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF "); } strSQL.AppendLine(" from XLXSCLHQ,"); strSQL.AppendLine(" ("); strSQL.AppendLine(" select XLXSCLHQ_BJCLID as BJCLID,XLXSBJCL_JGYBJCLID as JGYBJCLID,XLXSBJCL_ZZSL as ZZSL,"); strSQL.AppendLine(" max(XLXSCLHQ_YXQS)as maxYXQS"); strSQL.AppendLine(" from XLXSCLHQ,XLXSBJCL"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_YXQS<=:pBJRQ"); strSQL.AppendLine(" and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)"); strSQL.AppendLine(" and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID"); strSQL.AppendLine(" group by XLXSCLHQ_BJCLID,XLXSBJCL_JGYBJCLID,XLXSBJCL_ZZSL"); strSQL.AppendLine(" ) x"); strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'"); strSQL.AppendLine(" and XLXSCLHQ_BJCLID=BJCLID"); strSQL.AppendLine(" and XLXSCLHQ_YXQS=maxYXQS"); strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID"); //关联使用的材料，提上显示 strSQL.AppendLine(" LEFT JOIN"); strSQL.AppendLine(" ("); strSQL.AppendLine(" SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX"); strSQL.AppendLine(" LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') "); strSQL.AppendLine(" LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLXSCLQD_JGCZBBBID = XLJGCZBLM_JGCZBBBID "); strSQL.AppendLine(" WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'"); strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID "); //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID"); return strSQL; } } public partial class XLBJD_CLHQTZList : OperationBase { XLBJD_CLHQTZ entity = new XLBJD_CLHQTZ(); protected void Page_Load(object sender, EventArgs e) { this.UMID = 100860174; this.UMName = "线缆销售报价单"; this.CurMaster = Master; this.Tabs = Tab; this.PageHeight = 600; this.PageWidth = 850; CurMaster.BtnQueryVisible = false; CurMaster.BtnReportVisible = false; AddListSetMap(new ListSet(pagSHD.ID, null, grdSHD, entity, UPSHD)); base.CustomPageSize = 1000; base.InitPage(); if (!IsPostBack) InsertVHQB(PageParam.GetRTrimString("BJDID")); grdSHD.EnabledToolBar = false; CurMaster.PnlBill.Visible = false; CurMaster.PnlQuery.Visible = false; if (PageParam.GetRTrimString("JGBIDS").Equals("")) { grdSHD.FindColumnByFieldName("SYJGB_HSJG").Visible = false; } if (!IsPostBack) { Master_OnQuery(); GridDataBind(); InitControl(); //料价追溯界面调用，默认选中BJCLID所在行 if (!PageParam.GetRTrimString("BJCLID").Equals("")) { for (int i = 0; i < grdSHD.Rows.Count; i++) { if (grdSHD.GetValueByFieldName("XLXSBJCL_BJCLID", i).Trim().Equals(PageParam.GetRTrimString("BJCLID"))) { grdSHD.SelectedIndex = i; break; } } } } grdSHD.FindColumnByFieldName("CLJG").NumberDecimalPlace = XLBJDUtils.BJCLPriceDecimal; grdSHD.FindColumnByFieldName("HSJG").NumberDecimalPlace = XLBJDUtils.BJCLPriceDecimal; grdSHD.FindColumnByFieldName("WSJGF").NumberDecimalPlace = XLBJDUtils.BJCLPriceDecimal; grdSHD.FindColumnByFieldName("HSJGF").NumberDecimalPlace = XLBJDUtils.BJCLPriceDecimal; grdSHD.FindColumnByFieldName("SYJGB_HSJG").NumberDecimalPlace = XLBJDUtils.BJCLPriceDecimal; } #region 模板继承事件 protected override void Master_OnQuery() { entity.sBJDID = PageParam.GetRTrimString("BJDID"); entity.sVID = txtVCLHQID.Value; entity.sRYID = CurSession.RYID; entity.sBJRQ = PageParam.GetRTrimString("BJRQ"); entity.sBJFLID = PageParam.GetRTrimString("BJFLID"); entity.sJGBID = PageParam.GetRTrimString("JGBID"); entity.sJGBIDS = PageParam.GetRTrimString("JGBIDS"); entity.sBJDHQLY = PageParam.GetRTrimString("BJDHQLY"); entity.bHSBZ = PageParam.GetRTrimString("HSBZ").Equals("Y"); entity.sTBJCLID = PageParam.GetRTrimString("TBJCLID"); entity.sLBJCLID = PageParam.GetRTrimString("LBJCLID"); entity.sTJ = PageParam.GetRTrimString("TJ"); entity.sLJ = PageParam.GetRTrimString("LJ"); entity.sBJU057 = CurSession.GetOption(100860000, "BJU057"); base.Master_OnQuery(); grdSHD.SelectedIndex = -1; } protected override void GridDataBind() { base.GridDataBind(); } protected override bool DataLogicValid() { return base.DataLogicValid(); } protected override bool Master_OnOK() { bool bResult = base.Master_OnOK(); if (!bResult) return false; string sJGBBJBM = string.IsNullOrEmpty(PageParam.GetRTrimString("JGBBJBM")) ? "表价" : PageParam.GetRTrimString("JGBBJBM"); string sBJBM = string.IsNullOrEmpty(PageParam.GetRTrimString("BJBM")) ? "报价" : PageParam.GetRTrimString("BJBM"); string sBJDHQLJBM = PageParam.GetRTrimString("BJBM"); string sTBJLCID = PageParam.GetRTrimString("TBJCLID").Trim(); string sLBJCLID = PageParam.GetRTrimString("LBJCLID").Trim(); if (!sTBJLCID.Trim().Equals("") || !sLBJCLID.Trim().Equals("")) { string sTLJMsg = ""; for (int i = 0; i < grdSHD.NewRows.Count; i++) { if (!sTBJLCID.Trim().Equals("")) { if (grdSHD.GetValueByFieldName("XLXSBJCL_BJCLID", grdSHD.NewRows[i]).ToString().Trim() == sTBJLCID) { double dTJ = CMISproConvert.StrToFloat(grdSHD.GetValueByFieldName("CLJG", grdSHD.NewRows[i]).ToString().Trim()); if (dTJ == 0) { string sTCLMC = grdSHD.GetValueByFieldName("XLXSBJCL_BJCLID", grdSHD.NewRows[i]).ToString().Trim() + "|" + grdSHD.GetValueByFieldName("XLXSBJCL_BJCLMC", grdSHD.NewRows[i]).ToString().Trim(); if (!sTLJMsg.Equals("")) sTLJMsg += "、"; sTLJMsg += sTCLMC; } } } if (!sLBJCLID.Trim().Equals("")) { if (grdSHD.GetValueByFieldName("XLXSBJCL_BJCLID", grdSHD.NewRows[i]).ToString().Trim() == sLBJCLID) { double dLJ = CMISproConvert.StrToFloat(grdSHD.GetValueByFieldName("CLJG", grdSHD.NewRows[i]).ToString().Trim()); if (dLJ == 0) { string sLCLMC = grdSHD.GetValueByFieldName("XLXSBJCL_BJCLID", grdSHD.NewRows[i]).ToString().Trim() + "|" + grdSHD.GetValueByFieldName("XLXSBJCL_BJCLMC", grdSHD.NewRows[i]).ToString().Trim(); if (!sTLJMsg.Equals("")) sTLJMsg += "、"; sTLJMsg += sLCLMC; } } } } if (!sTLJMsg.Equals("")) { ShowMessage(sTLJMsg + "价格不允许为0！"); return false; } } string sMsg = ""; bool bCalcBJDHQCLCB_CLCB_JGBJJ_JGBBJ = false; if (XLBJDUtils.IsJGEJGB(PageParam.GetRTrimString("JGBID")))//表价实时计算价格表 { sMsg = "将使用调整后的材料行情重算" + sJGBBJBM + "，进而重算" + sBJBM + "。是否继续？"; bCalcBJDHQCLCB_CLCB_JGBJJ_JGBBJ = true; } else { if (PageParam.GetRTrimString("JGBTJYS").Equals("3")) { sMsg = "将使用调整后的材料行情重算" + sJGBBJBM + "，进而重算" + sBJBM + "。是否继续？"; bCalcBJDHQCLCB_CLCB_JGBJJ_JGBBJ = true; } else { if (PageParam.GetRTrimString("LJJCLLX").Equals("2")) { if (sBJDHQLJBM.Equals("")) sMsg = "将材料行情代入公式“sum(材料价格*材料用量)”，重新计算报价单行情料价，将重新计算的报价单行情料价代入公式“料价价差率=(报价-报价单行情料价)/报价*100”，重新计算料价价差率或报价。是否继续？"; else sMsg = "将材料行情代入公式“sum(材料价格*材料用量)”，重新计算" + sBJDHQLJBM + "，将重新计算的" + sBJDHQLJBM + "代入公式“" + sBJDHQLJBM + "价差率=(" + sBJBM + "-" + sBJDHQLJBM + ")/" + sBJBM + "*100”，重新计算" + sBJDHQLJBM + "价差率或" + sBJBM + "。是否继续？"; } else if (PageParam.GetRTrimString("LJJCLLX").Equals("3")) { if (sBJDHQLJBM.Equals("")) sMsg = "将材料行情代入公式“sum(材料价格*材料用量)”，重新计算报价单行情料价，将重新计算的报价单行情料价代入公式“料价价差率=(报价-报价单行情料价)/报价单行情料价*100”，重新计算料价价差率或报价。是否继续？"; else sMsg = "将材料行情代入公式“sum(材料价格*材料用量)”，重新计算" + sBJDHQLJBM + "，将重新计算的" + sBJDHQLJBM + "代入公式“" + sBJDHQLJBM + "价差率=(" + sBJBM + "-" + sBJDHQLJBM + ")/" + sBJDHQLJBM + "*100”，重新计算" + sBJDHQLJBM + "价差率或" + sBJBM + "。是否继续？"; } else if (PageParam.GetRTrimString("LJJCLLX").Equals("4")) { if (sBJDHQLJBM.Equals("")) sMsg = "将材料行情代入公式“sum(材料价格*材料用量)”，重新计算报价单行情料价，将重新计算的报价单行情料价代入公式“料价价差率=(报价单行情料价)/报价*100”，重新计算料价价差率或报价。是否继续？"; else sMsg = "将材料行情代入公式“sum(材料价格*材料用量)”，重新计算" + sBJDHQLJBM + "，将重新计算的" + sBJDHQLJBM + "代入公式“" + sBJDHQLJBM + "价差率=(" + sBJDHQLJBM + ")/" + sBJBM + "*100”，重新计算" + sBJDHQLJBM + "价差率或" + sBJBM + "。是否继续？"; } else { if (sBJDHQLJBM.Equals("")) sMsg = "将材料行情代入公式“sum(材料价格*材料用量)”，重新计算报价单行情料价。是否继续？"; else sMsg = "将材料行情代入公式“sum(材料价格*材料用量)”，重新计算" + sBJDHQLJBM + "。是否继续？"; } } } Confirm.Register("IsQD", sMsg); Confirm.ShowBtnText = TConfirm.cBtnText.Yes_No; if (Confirm.GetValue("IsQD").Trim().Equals("")) { Confirm.Ask("IsQD"); return false; } else if (Confirm.GetValue("IsQD").Trim().Equals("N")) { Confirm.Clear(); return false; } else if (Confirm.GetValue("IsQD").Trim().Equals("Y")) { Confirm.Clear(); } TDatabase DBERP = TContextDatabases.GetDatabase("DBERP"); string sBJDID = PageParam.GetRTrimString("BJDID"); if (!sBJDID.Equals("")) { string sErrMsg = ""; try { DBERP.BeginTrans(); //先删除材料行情、物料行情 DBERP.ExecSQL(string.Format("delete from XLXSBJDCLHQ where XLXSBJDCLHQ_BJDID='{0}'", sBJDID)); DBERP.ExecSQL(string.Format("delete from XLXSBJDWLHQ where XLXSBJDWLHQ_BJDID='{0}'", sBJDID)); TSQL tmpSQL = new TSQL(DBERP); //此处，直接利用上方插入的XLXSBJDCLHQ记录作为加工原料的价格 XLBJDUtils.InsertXLXSBJDWLHQbyBJDCLHQ_V(DBERP, tmpSQL, sBJDID, txtVWLHQID.Value, txtVCLHQID.Value, CurSession, PageParam.GetRTrimString("JGBID"), PageParam.GetRTrimString("BJRQ"), CurSession.UserName, CurSession.UserId); //将临时表内容更新至材料行情、物料行情 InsertHQB(DBERP, sBJDID, CurSession.RYID, txtVCLHQID.Value, txtVWLHQID.Value); double fTJ = 0; double fLJ = 0; for (int i = 0; i < grdSHD.NewRows.Count; i++) { //循环更新报价单行情时，一并记录铜价、铝价 if (grdSHD.GetValueByFieldName("XLXSBJCL_BJCLID", grdSHD.NewRows[i]).ToString().Trim() == PageParam.GetRTrimString("TBJCLID")) { if (PageParam.GetRTrimString("HSBZ").Equals("Y")) fTJ = CMISproConvert.StrToFloat(grdSHD.GetValueByFieldName("HSJG", grdSHD.NewRows[i]).ToString().Trim()); else fTJ = CMISproConvert.StrToFloat(grdSHD.GetValueByFieldName("CLJG", grdSHD.NewRows[i]).ToString().Trim()); } else if (grdSHD.GetValueByFieldName("XLXSBJCL_BJCLID", grdSHD.NewRows[i]).ToString().Trim() == PageParam.GetRTrimString("LBJCLID")) { if (PageParam.GetRTrimString("HSBZ").Equals("Y")) fLJ = CMISproConvert.StrToFloat(grdSHD.GetValueByFieldName("HSJG", grdSHD.NewRows[i]).ToString().Trim()); else fLJ = CMISproConvert.StrToFloat(grdSHD.GetValueByFieldName("CLJG", grdSHD.NewRows[i]).ToString().Trim()); } } //补充更新XLXSBJD_TJ、XLXSBJD_LJ if (!PageParam.GetRTrimString("TBJCLID").Equals("") || !PageParam.GetRTrimString("LBJCLID").Equals("")) { tmpSQL.Clear(); tmpSQL.Add("update XLXSBJD set XLXSBJD_TJ=:pTJ,XLXSBJD_LJ=:pLJ"); tmpSQL.Add(" where XLXSBJD_BJDID=:pBJDID"); tmpSQL.ParamByName("pTJ").Value = fTJ; tmpSQL.ParamByName("pLJ").Value = fLJ; tmpSQL.ParamByName("pBJDID").Value = sBJDID; DBERP.ExecSQL(tmpSQL); } if (bCalcBJDHQCLCB_CLCB_JGBJJ_JGBBJ)//根据最新报价单行情重算 { XLBJDCalcUtils.CalcAfterBJDHQChange(DBERP, tmpSQL, sBJDID, 1, GetOption(100860000,"BJU002"), GetOption(100860000,"BJU003"), GetOption(100860000,"BJU018"), CurSession.UserId, CurSession.UserName, ref sErrMsg); } else//否则只更新报价单行情料价 { XLBJDCalcUtils.CalcBJDHQCLCB(DBERP, tmpSQL, sBJDID, 1, PageParam.GetRTrimString("HSBZ").Equals("Y"), PageParam.GetRTrimString("JGBID"), PageParam.GetRTrimString("JGBIDS"), PageParam.GetRTrimString("JSBLFS"), GetOption(100860000,"BJU002"), GetOption(100860000,"BJU003"), GetOption(100860000,"BJU018"), CurSession.UserId, CurSession.UserName, ref sErrMsg); string sJGBJJLY = XLBJDUtils.GetXLXSJGBXXByJGBID(PageParam.GetRTrimString("JGBID")).XLXSJGB_JJLY; string ABJDHQJSBJ = XLBJDUtils.GetBJFLXX(PageParam.GetRTrimString("BJFLID")).XLXSBJFL_ABJDHQJSBJ; if (ABJDHQJSBJ == "Y" && sJGBJJLY == "A") { #region 配置信息 tmpSQL.Clear(); tmpSQL.Add("select XLXSBJD_JGBID,XLXSJGB_MXBH,XLXSJGB_JGBIDS,XLXSJGB_JGCIDS,XLXSJGB_BJLY,XLXSJGB_TJYS,XLXSJGB_JSBLFS, "); tmpSQL.Add(" XLXSBJD_BJFLID,XLXSBJFL_BJDHQLY,XLXSBJFL_TZCLHQ,XLXSBJFL_JGBTJYSS,XLXSBJFL_BJJCLLX,XLXSBJFL_LJJCLLX,XLXSBJFL_CPBJMRXSWS,"); tmpSQL.Add(" XLXSBJFL_ZDXJJCLLX,XLXSBJFL_JYF,XLXSBJFL_YFWLID,XLXSBJFL_BJHYF,XLXSBJFL_ZDXJGS,XLXSBJFL_JSJGS,"); tmpSQL.Add(" XLXSBJD_JZBJHS,XLXSBJD_BJRQ"); tmpSQL.Add(" from XLXSBJD "); tmpSQL.Add(" left join XLXSJGB on XLXSJGB_JGBID=XLXSBJD_JGBID "); tmpSQL.Add(" left join XLXSBJFL on XLXSBJFL_BJFLID=XLXSBJD_BJFLID"); tmpSQL.Add(" where XLXSBJD_BJDID=:pBJDID"); tmpSQL.ParamByName("pBJDID").Value = sBJDID; DataSet dsBJD = DBERP.OpenDataSet(tmpSQL); if (dsBJD.Tables[0].Rows.Count == 0) { return false; } string sBJU002 = GetOption(100860000,"BJU002"); int iXSWS = string.IsNullOrEmpty(sBJU002) ? 5 : CMISproConvert.StrToInt(sBJU002); if (iXSWS < 0) iXSWS = 0; else if (iXSWS > 8) iXSWS = 8; bool bHSBZ = dsBJD.Tables[0].Rows[0]["XLXSBJD_JZBJHS"].ToString().Trim().Equals("Y"); string sBJRQ = dsBJD.Tables[0].Rows[0]["XLXSBJD_BJRQ"].ToString().Trim(); string sJGBID = dsBJD.Tables[0].Rows[0]["XLXSBJD_JGBID"].ToString().Trim(); string sMXBH = dsBJD.Tables[0].Rows[0]["XLXSJGB_MXBH"].ToString().Trim(); string sJGBIDS = dsBJD.Tables[0].Rows[0]["XLXSJGB_JGBIDS"].ToString().Trim(); string sJGCIDS = dsBJD.Tables[0].Rows[0]["XLXSJGB_JGCIDS"].ToString().Trim(); string sBJLY = dsBJD.Tables[0].Rows[0]["XLXSJGB_BJLY"].ToString().Trim(); string sTJYS = dsBJD.Tables[0].Rows[0]["XLXSJGB_TJYS"].ToString().Trim(); string sJSBLFS = dsBJD.Tables[0].Rows[0]["XLXSJGB_JSBLFS"].ToString().Trim(); string sBJFLID = dsBJD.Tables[0].Rows[0]["XLXSBJD_BJFLID"].ToString().Trim(); string sBJDHQLY = dsBJD.Tables[0].Rows[0]["XLXSBJFL_BJDHQLY"].ToString().Trim();//1|价格表行情、2|报价日行情 string sTZCLHQ = dsBJD.Tables[0].Rows[0]["XLXSBJFL_TZCLHQ"].ToString().Trim();//允许调整材料行情 string sJGBTJYSS = dsBJD.Tables[0].Rows[0]["XLXSBJFL_JGBTJYSS"].ToString().Trim();//价格表调价因素 string sJCLLX = dsBJD.Tables[0].Rows[0]["XLXSBJFL_BJJCLLX"].ToString().Trim(); string sLJJCLLX = dsBJD.Tables[0].Rows[0]["XLXSBJFL_LJJCLLX"].ToString().Trim(); string sCPBJMRXSWS = dsBJD.Tables[0].Rows[0]["XLXSBJFL_CPBJMRXSWS"].ToString().Trim(); string sZDXJJCLLX = dsBJD.Tables[0].Rows[0]["XLXSBJFL_ZDXJJCLLX"].ToString().Trim(); string sJYF = dsBJD.Tables[0].Rows[0]["XLXSBJFL_JYF"].ToString().Trim(); string sYFWLID = dsBJD.Tables[0].Rows[0]["XLXSBJFL_YFWLID"].ToString().Trim(); string sBJHYF = dsBJD.Tables[0].Rows[0]["XLXSBJFL_BJHYF"].ToString().Trim(); string sZDXJGS = dsBJD.Tables[0].Rows[0]["XLXSBJFL_ZDXJGS"].ToString().Trim(); string sJSJGS = dsBJD.Tables[0].Rows[0]["XLXSBJFL_JSJGS"].ToString().Trim(); #endregion //同步报价单明细记录插入XLXSBJDCLCB、XLXSBJDJJYZ XLBJDInfo.InsertCLCBandJJYZ(DBERP, tmpSQL, TControlState.ctsEdit, sBJDID, sJGBID, sBJRQ, CurSession.UserId, CurSession.UserName, GetOption(100860000,"BJU003"), bHSBZ); XLBJDUtils.UpdateBJDMX(DBERP, tmpSQL, sBJFLID, sBJDID, iXSWS, sJCLLX, sLJJCLLX, sCPBJMRXSWS, "", 1); bool bZDXJTZ = false; if (sZDXJGS.Contains("CLCB") || sZDXJGS.Contains("JGBJJ") || sZDXJGS.Contains("JGBBJ") || sZDXJGS.Contains("CPBJ")) { XLBJDUtils.UpdateZDXJGS(DBERP, tmpSQL, sBJDID, sBJFLID, sBJU002, "", 1); bZDXJTZ = true; } if (sJSJGS.Contains("CLCB") || sJSJGS.Contains("JGBJJ") || sJSJGS.Contains("JGBBJ") || sJSJGS.Contains("CPBJ") || bZDXJTZ) { XLBJDUtils.UpdateJSJGS(DBERP, tmpSQL, sBJDID, sBJFLID, sBJU002, "", 1); } } else { UpdateLJJCL(DBERP, tmpSQL, sBJDID, PageParam.GetRTrimString("JCLLX"), PageParam.GetRTrimString("LJJCLLX"), PageParam.GetRTrimString("CPBJMRXSWS"), GetOption(100860000,"BJU002")); UpdateBJLJJCL(DBERP, tmpSQL, sBJDID, PageParam.GetRTrimString("BJLJJCLLX")); //只有最低限价公式中包含CPBJ，才需要触发计算最低限价 tmpSQL.Clear(); tmpSQL.Add("select XLXSBJFL_ZDXJGS,XLXSBJFL_JSJGS from XLXSBJFL where XLXSBJFL_BJFLID=:pBJFLID"); tmpSQL.ParamByName("pBJFLID").Value = PageParam.GetRTrimString("BJFLID"); DataSet dsFL = DBERP.OpenDataSet(tmpSQL); if (dsFL.Tables[0].Rows.Count > 0) { bool bZDXJTZ = false; if (dsFL.Tables[0].Rows[0]["XLXSBJFL_ZDXJGS"].ToString().Trim().Contains("CPBJ")) { XLBJDUtils.UpdateZDXJGS(DBERP, tmpSQL, sBJDID, PageParam.GetRTrimString("BJFLID"), GetOption(100860000,"BJU002"), "", 1); bZDXJTZ = true; } if (dsFL.Tables[0].Rows[0]["XLXSBJFL_JSJGS"].ToString().Trim().Contains("CPBJ") || bZDXJTZ) { XLBJDUtils.UpdateJSJGS(DBERP, tmpSQL, sBJDID, PageParam.GetRTrimString("BJFLID"), GetOption(100860000,"BJU002"), "", 1); } } } XLBJDUtils.UpdateXLXSBJD(DBERP, tmpSQL, sBJDID, PageParam.GetRTrimString("JCLLX"), PageParam.GetRTrimString("LJJCLLX"), PageParam.GetRTrimString("ZDXJJCLLX"), PageParam.GetRTrimString("JYF"), PageParam.GetRTrimString("YSJDID"), PageParam.GetRTrimString("GYSID"), PageParam.GetRTrimString("YFWLID"), PageParam.GetRTrimString("BJHYF")); } DBERP.CommitTrans(); DialogOperationScript dos = new DialogOperationScript(this); dos.ClickOwnerButton(PageParam.GetRTrimString("btnRefreshCID")); dos.CloseSelf(); dos.Execute(); } catch (Exception ex) { ShowMessage(sErrMsg + " " + ex.Message); DBERP.RollbackTrans(); return false; } } return true; } protected override void Grid_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { if (grdSHD.GetCellByFieldName("XLXSBJCL_BJHQ", e.Row).Text.Trim().Equals("1")) grdSHD.GetCellByFieldName("XLXSBJCL_BJHQ", e.Row).Text = "1|材料行情"; else if (grdSHD.GetCellByFieldName("XLXSBJCL_BJHQ", e.Row).Text.Trim().Equals("2")) grdSHD.GetCellByFieldName("XLXSBJCL_BJHQ", e.Row).Text = "2|物料行情"; } base.Grid_RowDataBound(sender, e); } protected void grdSHD_CalculateField(object sender, CalculateFieldEventArgs e) { TDatabase dberp = TContextDatabases.GetDatabase("DBERP"); TSQL SQL = new TSQL(dberp); string sBJDID = PageParam.GetRTrimString("BJDID"); string sVID = GetVID(sBJDID, CurSession.RYID); string sRYID = CurSession.RYID; string sBJCLID = e.RowData["XLXSBJCL_BJCLID"].ToString().Trim(); string sCLJG = e.RowData["CLJG"].ToString().Trim(); string sHSJG = e.RowData["HSJG"].ToString().Trim(); string sWSJGF = e.RowData["WSJGF"].ToString().Trim(); string sHSJGF = e.RowData["HSJGF"].ToString().Trim(); if (e.EventFieldName.Equals("CLJG")) { double dCLJG = NumberUtils.RoundX(CMISproConvert.StrToFloat(sCLJG), XLBJDUtils.BJCLPriceDecimal); double dHSJG = NumberUtils.RoundX(CMISproConvert.StrToFloat(e.EventFieldValue) * (1 + CMISproConvert.StrToFloat(e.RowData["XLXSBJCL_ZZSL"].ToString().Trim(), 0) / 100), XLBJDUtils.BJCLPriceDecimal); sCLJG = dCLJG.ToString(); sHSJG = dHSJG.ToString(); e.ResultPairs.Add("HSJG", dHSJG.ToString()); e.ResultPairs.Add("V_XLXSBJDCLHQ_WHR", CurSession.UserName); e.ResultPairs.Add("V_XLXSBJDCLHQ_WHRID", CurSession.UserId); e.ResultPairs.Add("V_XLXSBJDCLHQ_WHSJ", DateTime.Now.ToString().Trim()); } else if (e.EventFieldName.Equals("HSJG")) { double dCLJG = NumberUtils.RoundX(CMISproConvert.StrToFloat(e.EventFieldValue) / (1 + CMISproConvert.StrToFloat(e.RowData["XLXSBJCL_ZZSL"].ToString().Trim(), 0) / 100), XLBJDUtils.BJCLPriceDecimal); double dHSJG = NumberUtils.RoundX(CMISproConvert.StrToFloat(sHSJG), XLBJDUtils.BJCLPriceDecimal); sCLJG = dCLJG.ToString(); sHSJG = dHSJG.ToString(); e.ResultPairs.Add("CLJG", dCLJG.ToString()); e.ResultPairs.Add("V_XLXSBJDCLHQ_WHR", CurSession.UserName); e.ResultPairs.Add("V_XLXSBJDCLHQ_WHRID", CurSession.UserId); e.ResultPairs.Add("V_XLXSBJDCLHQ_WHSJ", DateTime.Now.ToString().Trim()); } else { double dCLJG = NumberUtils.RoundX(CMISproConvert.StrToFloat(sCLJG), XLBJDUtils.BJCLPriceDecimal); double dHSJG = NumberUtils.RoundX(CMISproConvert.StrToFloat(sHSJG), XLBJDUtils.BJCLPriceDecimal); sCLJG = dCLJG.ToString(); sHSJG = dHSJG.ToString(); } //因“调整物料行情”时需要传递加工原料的价格，所以这里更新后台有必要，得保证前端和后台价格一致 SQL.Clear(); SQL.Add(" select 1 from V_XLXSBJDCLHQ where V_XLXSBJDCLHQ_BJCLID = :pBJCLID and V_XLXSBJDCLHQ_BJDID = :pBJDID and V_XLXSBJDCLHQ_ID = :pVID and V_XLXSBJDCLHQ_TZRYID = :pRYID "); SQL.ParamByName("pBJCLID").Value = sBJCLID; SQL.ParamByName("pBJDID").Value = sBJDID; SQL.ParamByName("pVID").Value = sVID; SQL.ParamByName("pRYID").Value = sRYID; DataSet dsBJDCLHQ = dberp.OpenDataSet(SQL); if (dsBJDCLHQ.Tables[0].Rows.Count > 0) { SQL.Clear(); SQL.Add(" update V_XLXSBJDCLHQ "); SQL.Add(" set V_XLXSBJDCLHQ_WSJG = :XLXSBJDCLHQ_WSJG,V_XLXSBJDCLHQ_HSJG=:XLXSBJDCLHQ_HSJG, "); SQL.Add(" V_XLXSBJDCLHQ_WHRID = :XLXSBJDCLHQ_WHRID, V_XLXSBJDCLHQ_WHR = :XLXSBJDCLHQ_WHR, V_XLXSBJDCLHQ_WHSJ = :XLXSBJDCLHQ_WHSJ"); SQL.Add(" where V_XLXSBJDCLHQ_BJDID =:pBJDID and V_XLXSBJDCLHQ_BJCLID = :pBJCLID and V_XLXSBJDCLHQ_ID = :pVID and V_XLXSBJDCLHQ_TZRYID = :pRYID "); SQL.ParamByName("XLXSBJDCLHQ_WSJG").Value = sCLJG; SQL.ParamByName("XLXSBJDCLHQ_HSJG").Value = sHSJG; SQL.ParamByName("XLXSBJDCLHQ_WHR").Value = CurSession.UserName; SQL.ParamByName("XLXSBJDCLHQ_WHRID").Value = CurSession.UserId; SQL.ParamByName("XLXSBJDCLHQ_WHSJ").Value = DateTime.Now; SQL.ParamByName("pBJCLID").Value = sBJCLID; SQL.ParamByName("pBJDID").Value = sBJDID; SQL.ParamByName("pVID").Value = sVID; SQL.ParamByName("pRYID").Value = sRYID; dberp.ExecSQL(SQL); } else { //插入 SQL.Clear(); SQL.Add(" insert into V_XLXSBJDCLHQ( "); SQL.Add(" V_XLXSBJDCLHQ_BJCLID,V_XLXSBJDCLHQ_BJDID, V_XLXSBJDCLHQ_WSJG,V_XLXSBJDCLHQ_HSJG, V_XLXSBJDCLHQ_WSJGF,V_XLXSBJDCLHQ_HSJGF, "); SQL.Add(" V_XLXSBJDCLHQ_ID,V_XLXSBJDCLHQ_TZRYID,V_XLXSBJDCLHQ_TZRYGH,V_XLXSBJDCLHQ_TZRYMC,V_XLXSBJDCLHQ_TIMESTAMP,V_XLXSBJDCLHQ_WHRID, V_XLXSBJDCLHQ_WHR, V_XLXSBJDCLHQ_WHSJ )"); SQL.Add(" values("); SQL.Add(" :XLXSBJDCLHQ_BJCLID, :XLXSBJDCLHQ_BJDID, :XLXSBJDCLHQ_WSJG,:XLXSBJDCLHQ_HSJG, :XLXSBJDCLHQ_WSJGF, :XLXSBJDCLHQ_HSJGF,"); SQL.Add(" :pVID,:pRYID,:pRYGH,:pRYMC,:pTIMESTAMP,:XLXSBJDCLHQ_WHRID, :XLXSBJDCLHQ_WHR, :XLXSBJDCLHQ_WHSJ )"); SQL.ParamByName("XLXSBJDCLHQ_BJCLID").Value = sBJCLID; SQL.ParamByName("XLXSBJDCLHQ_BJDID").Value = sBJDID; SQL.ParamByName("pVID").Value = sVID; SQL.ParamByName("pRYID").Value = sRYID; SQL.ParamByName("pRYGH").Value = CurSession.UserId; SQL.ParamByName("pRYMC").Value = CurSession.UserName; SQL.ParamByName("pTIMESTAMP").Value = MISproUtils.GetServerTime(); SQL.ParamByName("XLXSBJDCLHQ_WSJG").Value = sCLJG; SQL.ParamByName("XLXSBJDCLHQ_HSJG").Value = sHSJG; SQL.ParamByName("XLXSBJDCLHQ_WSJGF").Value = sWSJGF; SQL.ParamByName("XLXSBJDCLHQ_HSJGF").Value = sHSJGF; SQL.ParamByName("XLXSBJDCLHQ_WHR").Value = CurSession.UserName; SQL.ParamByName("XLXSBJDCLHQ_WHRID").Value = CurSession.UserId; SQL.ParamByName("XLXSBJDCLHQ_WHSJ").Value = DateTime.Now; dberp.ExecSQL(SQL); } } #endregion #region 自定义函数 private void InitControl() { //调整物料行情=Y if (CMISproDataBase.ExistsRecord("DBERP", string.Format("select 1 from XLXSBJFL where XLXSBJFL_BJFLID='{0}' and XLXSBJFL_TZWLHQ='Y'", PageParam.GetRTrimString("BJFLID")))) { btnTZWLHQ.Visible = true; btnWLHQ.Visible = false; } else { btnTZWLHQ.Visible = false; TDatabase dberp = TContextDatabases.GetDatabase("DBERP"); TSQL SQL = new TSQL(dberp); SQL.Clear(); SQL.Add("select top 1 XLXSBJCL_BJHQ from XLXSBJCL where XLXSBJCL_BJHQ = '2' "); DataSet ds = dberp.OpenDataSet(SQL); if (ds.Tables[0].Rows.Count > 0) { btnWLHQ.Visible = true; } else { btnWLHQ.Visible = false; } } } private string GetBJCLMC(string sBJCLID) { TDatabase dberp = TContextDatabases.GetDatabase("DBERP"); TSQL SQL = new TSQL(dberp); SQL.Clear(); SQL.Add(" select XLXSBJCL_BJCLMC from XLXSBJCL "); SQL.Add(" where XLXSBJCL_BJCLID = :XLXSBJCL_BJCLID "); SQL.ParamByName("XLXSBJCL_BJCLID").Value = sBJCLID; DataSet ds = dberp.OpenDataSet(SQL); if (ds.Tables[0].Rows.Count > 0) { return ds.Tables[0].Rows[0]["XLXSBJCL_BJCLMC"].ToString().Trim(); } return ""; } //更新料价价差率 public static void UpdateLJJCL(TDatabase dberp, TSQL SQL, string sBJDID, string sJCLLX, string sLJJCLLX, string sCPBJMRXSWS, string sBJU002) { //按报价单行情重算表价、报价 int tmpXSWS = string.IsNullOrEmpty(sBJU002) ? 5 : CMISproConvert.StrToInt(sBJU002); if (tmpXSWS < 0) tmpXSWS = 0; else if (tmpXSWS > 8) tmpXSWS = 8; #region 2|(调整后报价金额-报价单行情料价金额)/调整后报价金额*100,3|(调整后报价金额-报价单行情料价金额)/报价单行情料价金额*100 if (sLJJCLLX.Equals("2") || sLJJCLLX.Equals("3") || sLJJCLLX.Equals("4"))//2|(调整后报价金额-报价单行情料价金额)/调整后报价金额*100,3|(调整后报价金额-报价单行情料价金额)/报价单行情料价金额*100 { //料价价差率未锁定的明细记录，更新料价价差率 SQL.Clear(); if (sLJJCLLX.Equals("2")) SQL.Add("update XLXSBJDMX set XLXSBJDMX_CLCBJCL=RoundFix(case when XLXSBJDMX_BJJE=0 then 0 else 100*(XLXSBJDMX_BJJE-RoundFix(XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL,2))/XLXSBJDMX_BJJE end,2)"); else if (sLJJCLLX.Equals("3")) SQL.Add("update XLXSBJDMX set XLXSBJDMX_CLCBJCL=RoundFix(case when XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL=0 then 0 else 100*(XLXSBJDMX_BJJE-RoundFix(XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL,2))/(XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL) end,2)"); else SQL.Add("update XLXSBJDMX set XLXSBJDMX_CLCBJCL=RoundFix(case when XLXSBJDMX_BJJE=0 then 0 else 100*(RoundFix(XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL,2))/XLXSBJDMX_BJJE end,2)"); SQL.Add(" where XLXSBJDMX_BJDID=:pBJDID"); SQL.Add(" and XLXSBJDMX_CLCBJCLSDBZ<>'Y'");//料价价差率未锁定 SQL.ParamByName("pBJDID").Value = sBJDID; dberp.ExecSQL(SQL); #region 按料价价差率算报价的明细记录，更新报价 string sqlBJJE = ""; if (sLJJCLLX.Equals("2")) sqlBJJE = "RoundFix(case WHEN XLXSBJDMX_CLCBJCL = 100 OR XLXSBJDMX_BJSL=0 THEN 0 else XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL/(1-XLXSBJDMX_CLCBJCL/100.0) END,2)"; else if (sLJJCLLX.Equals("3")) sqlBJJE = "RoundFix(case WHEN XLXSBJDMX_BJSL=0 THEN 0 else XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL*(1+XLXSBJDMX_CLCBJCL/100.0) END,2)"; else sqlBJJE = "RoundFix(case WHEN XLXSBJDMX_CLCBJCL = 0 THEN 0 else XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL/(XLXSBJDMX_CLCBJCL/100.0) END,2)"; SQL.Clear(); SQL.Add(" update XLXSBJDMX set "); SQL.Add(" XLXSBJDMX_TZQBJJE=RoundFix(" + sqlBJJE + " - XLXSBJDMX_TZBJJE,2),"); SQL.Add(" XLXSBJDMX_CPBJ=case when XLXSBJDMX_BJSL = 0 then 0 else RoundFix((" + sqlBJJE + " - XLXSBJDMX_TZBJJE)/XLXSBJDMX_BJSL," + (CMISproConvert.StrToInt(sCPBJMRXSWS) > tmpXSWS ? CMISproConvert.StrToInt(sCPBJMRXSWS) : tmpXSWS) + " ) end, ");//保留精度高的 SQL.Add(" XLXSBJDMX_TZCPBJ=case when XLXSBJDMX_TZQCPBJ=0 or XLXSBJDMX_BJSL = 0 then 0 else 100*((" + sqlBJJE + " - XLXSBJDMX_TZBJJE)/XLXSBJDMX_BJSL-XLXSBJDMX_TZQCPBJ)/XLXSBJDMX_TZQCPBJ end,"); SQL.Add(" XLXSBJDMX_BJJE=" + sqlBJJE + ","); //if (sBJLJJCLLX.Equals("1"))//表价料价价差率公式 //{ // SQL.Add("XLXSBJDMX_JGBBJLJJCL =case XLXSBJDMX_BJSL*XLXSBJDMX_JGBBJ when 0 then 0 else RoundFix((XLXSBJDMX_BJSL*XLXSBJDMX_JGBBJ - XLXSBJDMX_BJSL*XLXSBJDMX_BJDHQCLCB)/ (XLXSBJDMX_BJSL*XLXSBJDMX_JGBBJ) * 100,2) end,"); //} //else //{ // SQL.Add("XLXSBJDMX_JGBBJLJJCL=0,"); //} if (sJCLLX.Equals("1"))//表价价差率公式 { SQL.Add("XLXSBJDMX_JGBBJJCL =case when " + sqlBJJE + "=0.0 then 0.0 else RoundFix(100*(" + sqlBJJE + "-XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL)/(" + sqlBJJE + "),2) end,"); SQL.Add("XLXSBJDMX_JGBBJJCE =RoundFix(" + sqlBJJE + "-RoundFix(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL,2)," + tmpXSWS + ") "); } else if (sJCLLX.Equals("2")) { SQL.Add("XLXSBJDMX_JGBBJJCL =case when XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL=0.0 then 0.0 else RoundFix(100*(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL-" + sqlBJJE + ")/(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL),2) end,"); SQL.Add("XLXSBJDMX_JGBBJJCE =RoundFix(RoundFix(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL,2)-" + sqlBJJE + "," + tmpXSWS + ") "); } else if (sJCLLX.Equals("3")) { SQL.Add("XLXSBJDMX_JGBBJJCL =case when " + sqlBJJE + "=0.0 then 0.0 else RoundFix(100*(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL)/(" + sqlBJJE + "),2) end,"); SQL.Add("XLXSBJDMX_JGBBJJCE =0 "); } else if (sJCLLX.Equals("4")) { SQL.Add("XLXSBJDMX_JGBBJJCL =case when XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL=0.0 then 0.0 else RoundFix(100*(" + sqlBJJE + " - XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL)/(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL),2) end,"); SQL.Add("XLXSBJDMX_JGBBJJCE =RoundFix(" + sqlBJJE + " - RoundFix(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL,2)," + tmpXSWS + ") "); } else { SQL.Add("XLXSBJDMX_JGBBJJCL=0,"); SQL.Add("XLXSBJDMX_JGBBJJCE=0 "); } SQL.Add(" from "); SQL.Add(" XLXSBJDMX"); SQL.Add(" where XLXSBJDMX_BJDID=:pBJDID "); SQL.Add(" and XLXSBJDMX_BJSDBZ<>'Y'");//跳过报价锁定为Y的明细数据 SQL.Add(" and XLXSBJDMX_CLCBJCLSDBZ='Y'");//按料价价差率算报价 SQL.ParamByName("pBJDID").Value = sBJDID; dberp.ExecSQL(SQL); string sCPBJBLXSWS = Math.Pow(10, -1 * CMISproConvert.StrToFloat(sCPBJMRXSWS)).ToString("F9"); string sqlBJJE2 = string.Format("(RoundFix(RoundFix(XLXSBJDMX_CPBJ/{0},0)*{0}*XLXSBJDMX_BJSL,2)+XLXSBJDMX_TZBJJE)", sCPBJBLXSWS); SQL.Clear(); SQL.Add(" update XLXSBJDMX set "); SQL.Add(string.Format(" XLXSBJDMX_CPBJ=RoundFix(XLXSBJDMX_CPBJ/{0},0)*{0},", sCPBJBLXSWS)); SQL.Add(string.Format(" XLXSBJDMX_TZCPBJ=case when XLXSBJDMX_TZQCPBJ=0 then 0 else 100*(RoundFix(XLXSBJDMX_CPBJ/{0},0)*{0}-XLXSBJDMX_TZQCPBJ)/XLXSBJDMX_TZQCPBJ end,", sCPBJBLXSWS)); SQL.Add(string.Format(" XLXSBJDMX_TZQBJJE=RoundFix(RoundFix(XLXSBJDMX_CPBJ/{0},0)*{0}*XLXSBJDMX_BJSL,2),", sCPBJBLXSWS)); SQL.Add(" XLXSBJDMX_BJJE=" + sqlBJJE2 + ","); if (sJCLLX.Equals("1"))//表价价差率公式 { SQL.Add("XLXSBJDMX_JGBBJJCL =case when " + sqlBJJE2 + "=0.0 then 0.0 else RoundFix(100*(" + sqlBJJE2 + "-XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL)/(" + sqlBJJE2 + "),2) end,"); SQL.Add("XLXSBJDMX_JGBBJJCE =RoundFix(" + sqlBJJE2 + "-RoundFix(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL,2)," + tmpXSWS + "), "); } else if (sJCLLX.Equals("2")) { SQL.Add("XLXSBJDMX_JGBBJJCL =case when XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL=0.0 then 0.0 else RoundFix(100*(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL-" + sqlBJJE2 + ")/(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL),2) end,"); SQL.Add("XLXSBJDMX_JGBBJJCE =RoundFix(RoundFix(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL,2)-" + sqlBJJE2 + "," + tmpXSWS + "), "); } else if (sJCLLX.Equals("3")) { SQL.Add("XLXSBJDMX_JGBBJJCL =case when " + sqlBJJE2 + "=0.0 then 0.0 else RoundFix(100*(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL)/(" + sqlBJJE2 + "),2) end,"); SQL.Add("XLXSBJDMX_JGBBJJCE =0, "); } else if (sJCLLX.Equals("4")) { SQL.Add("XLXSBJDMX_JGBBJJCL =case when XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL=0.0 then 0.0 else RoundFix(100*(" + sqlBJJE2 + " - XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL)/(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL),2) end,"); SQL.Add("XLXSBJDMX_JGBBJJCE =RoundFix(" + sqlBJJE2 + " - RoundFix(XLXSBJDMX_JGBBJ*XLXSBJDMX_BJSL,2)," + tmpXSWS + "), "); } else { SQL.Add("XLXSBJDMX_JGBBJJCL=0,"); SQL.Add("XLXSBJDMX_JGBBJJCE=0,"); } if (sLJJCLLX.Equals("2")) SQL.Add("XLXSBJDMX_CLCBJCL =case when " + sqlBJJE2 + "=0.0 then 0.0 else RoundFix(100*(" + sqlBJJE2 + "-XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL)/(" + sqlBJJE2 + "),2) end"); else if (sLJJCLLX.Equals("3")) SQL.Add("XLXSBJDMX_CLCBJCL =case when XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL=0.0 then 0.0 else RoundFix(100*(" + sqlBJJE2 + "-XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL)/(XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL),2) end"); else SQL.Add("XLXSBJDMX_CLCBJCL =case when " + sqlBJJE2 + "=0.0 then 0.0 else RoundFix(100*(XLXSBJDMX_BJDHQCLCB*XLXSBJDMX_BJSL)/(" + sqlBJJE2 + "),2) end"); SQL.Add(" from XLXSBJDMX"); SQL.Add(" where XLXSBJDMX_BJDID=:pBJDID "); SQL.Add(" and XLXSBJDMX_BJSDBZ<>'Y'");//跳过报价锁定为Y的明细数据 SQL.Add(" and XLXSBJDMX_CLCBJCLSDBZ='Y'");//按料价价差率算报价 SQL.ParamByName("pBJDID").Value = sBJDID; dberp.ExecSQL(SQL); #endregion } #endregion } //更新表价料价价差率 public static void UpdateBJLJJCL(TDatabase dberp, TSQL SQL, string sBJDID, string sBJLJJCLLX) { if (sBJLJJCLLX.Equals("1")) { SQL.Clear(); SQL.Add("update XLXSBJDMX set XLXSBJDMX_JGBBJLJJCL =case XLXSBJDMX_BJSL*XLXSBJDMX_JGBBJ when 0 then 0 else RoundFix((XLXSBJDMX_BJSL*XLXSBJDMX_JGBBJ - XLXSBJDMX_BJSL*XLXSBJDMX_BJDHQCLCB)/ (XLXSBJDMX_BJSL*XLXSBJDMX_JGBBJ) * 100,2) end "); SQL.Add(" where XLXSBJDMX_BJDID=:pBJDID "); SQL.ParamByName("pBJDID").Value = sBJDID; dberp.ExecSQL(SQL); } } //插入行情V表 private void InsertVHQB(string sBJDID) { TDatabase dberp = TContextDatabases.GetDatabase("DBERP"); TSQL SQL = new TSQL(dberp); SQL.Clear(); SQL.Add(" delete from V_XLXSBJDCLHQ where V_XLXSBJDCLHQ_TZRYID = :pTZRYID and V_XLXSBJDCLHQ_BJDID = :pBJDID "); SQL.ParamByName("pTZRYID").Value = CurSession.RYID; SQL.ParamByName("pBJDID").Value = sBJDID; dberp.ExecSQL(SQL); int VnewCLHQID = CMISproDataBase.GetMaxXH("DBERP", "V_XLXSBJDCLHQ", "V_XLXSBJDCLHQ_ID", "") + 1; txtVCLHQID.Value = VnewCLHQID.ToString().Trim(); SQL.Clear(); SQL.Add(" insert into V_XLXSBJDCLHQ(V_XLXSBJDCLHQ_BJDID,V_XLXSBJDCLHQ_BJCLID,V_XLXSBJDCLHQ_WSJG,V_XLXSBJDCLHQ_HSJG,V_XLXSBJDCLHQ_WSJGF,V_XLXSBJDCLHQ_HSJGF, "); SQL.Add(" V_XLXSBJDCLHQ_JGYBJCLID1,V_XLXSBJDCLHQ_WSJG1,V_XLXSBJDCLHQ_HSJG1,V_XLXSBJDCLHQ_JGBL1, "); SQL.Add(" V_XLXSBJDCLHQ_ID,V_XLXSBJDCLHQ_TZRYID,V_XLXSBJDCLHQ_TZRYGH,V_XLXSBJDCLHQ_TZRYMC,V_XLXSBJDCLHQ_TIMESTAMP,V_XLXSBJDCLHQ_WHR,V_XLXSBJDCLHQ_WHRID,V_XLXSBJDCLHQ_WHSJ)"); SQL.Add(" select XLXSBJDCLHQ_BJDID,XLXSBJDCLHQ_BJCLID,XLXSBJDCLHQ_WSJG,XLXSBJDCLHQ_HSJG,XLXSBJDCLHQ_WSJGF,XLXSBJDCLHQ_HSJGF, "); SQL.Add(" XLXSBJDCLHQ_JGYBJCLID1,XLXSBJDCLHQ_WSJG1,XLXSBJDCLHQ_HSJG1,XLXSBJDCLHQ_JGBL1, "); SQL.Add(" :V_XLXSBJDCLHQ_ID,:V_XLXSBJDCLHQ_TZRYID,:V_XLXSBJDCLHQ_TZRYGH,:V_XLXSBJDCLHQ_TZRYMC,:V_XLXSBJDCLHQ_TIMESTAMP,XLXSBJDCLHQ_WHR,XLXSBJDCLHQ_WHRID,XLXSBJDCLHQ_WHSJ "); SQL.Add(" from XLXSBJDCLHQ where XLXSBJDCLHQ_BJDID = :pBJDID "); SQL.ParamByName("V_XLXSBJDCLHQ_ID").Value = txtVCLHQID.Value; SQL.ParamByName("V_XLXSBJDCLHQ_TZRYID").Value = CurSession.RYID; SQL.ParamByName("V_XLXSBJDCLHQ_TZRYGH").Value = CurSession.UserId; SQL.ParamByName("V_XLXSBJDCLHQ_TZRYMC").Value = CurSession.UserName; SQL.ParamByName("V_XLXSBJDCLHQ_TIMESTAMP").Value = MISproUtils.GetServerTime(); SQL.ParamByName("pBJDID").Value = sBJDID; dberp.ExecSQL(SQL); SQL.Clear(); SQL.Add(" delete from V_XLXSBJDWLHQ where V_XLXSBJDWLHQ_TZRYID = :pTZRYID and V_XLXSBJDWLHQ_BJDID = :pBJDID "); SQL.ParamByName("pTZRYID").Value = CurSession.RYID; SQL.ParamByName("pBJDID").Value = sBJDID; dberp.ExecSQL(SQL); int VnewWLHQID = CMISproDataBase.GetMaxXH("DBERP", "V_XLXSBJDWLHQ", "V_XLXSBJDWLHQ_ID", "") + 1; txtVWLHQID.Value = VnewWLHQID.ToString().Trim(); SQL.Clear(); SQL.Add(" insert into V_XLXSBJDWLHQ(V_XLXSBJDWLHQ_BJDID,V_XLXSBJDWLHQ_WLID,V_XLXSBJDWLHQ_WSJG,V_XLXSBJDWLHQ_HSJG,V_XLXSBJDWLHQ_WSJGF,V_XLXSBJDWLHQ_HSJGF, "); SQL.Add(" V_XLXSBJDWLHQ_JGYBJCLID1,V_XLXSBJDWLHQ_WSJG1,V_XLXSBJDWLHQ_HSJG1,V_XLXSBJDWLHQ_JGBL1, "); SQL.Add(" V_XLXSBJDWLHQ_ID,V_XLXSBJDWLHQ_TZRYID,V_XLXSBJDWLHQ_TZRYGH,V_XLXSBJDWLHQ_TZRYMC,V_XLXSBJDWLHQ_TIMESTAMP,V_XLXSBJDWLHQ_WHR,V_XLXSBJDWLHQ_WHRID,V_XLXSBJDWLHQ_WHSJ)"); SQL.Add(" select XLXSBJDWLHQ_BJDID,XLXSBJDWLHQ_WLID,XLXSBJDWLHQ_WSJG,XLXSBJDWLHQ_HSJG,XLXSBJDWLHQ_WSJGF,XLXSBJDWLHQ_HSJGF, "); SQL.Add(" XLXSBJDWLHQ_JGYBJCLID1,XLXSBJDWLHQ_WSJG1,XLXSBJDWLHQ_HSJG1,XLXSBJDWLHQ_JGBL1, "); SQL.Add(" :V_XLXSBJDWLHQ_ID,:V_XLXSBJDWLHQ_TZRYID,:V_XLXSBJDWLHQ_TZRYGH,:V_XLXSBJDWLHQ_TZRYMC,:V_XLXSBJDWLHQ_TIMESTAMP,XLXSBJDWLHQ_WHR,XLXSBJDWLHQ_WHRID,XLXSBJDWLHQ_WHSJ "); SQL.Add(" from XLXSBJDWLHQ where XLXSBJDWLHQ_BJDID = :pBJDID "); SQL.ParamByName("V_XLXSBJDWLHQ_ID").Value = txtVWLHQID.Value; SQL.ParamByName("V_XLXSBJDWLHQ_TZRYID").Value = CurSession.RYID; SQL.ParamByName("V_XLXSBJDWLHQ_TZRYGH").Value = CurSession.UserId; SQL.ParamByName("V_XLXSBJDWLHQ_TZRYMC").Value = CurSession.UserName; SQL.ParamByName("V_XLXSBJDWLHQ_TIMESTAMP").Value = MISproUtils.GetServerTime(); SQL.ParamByName("pBJDID").Value = sBJDID; dberp.ExecSQL(SQL); } //插入行情表 private void InsertHQB(TDatabase dberp, string sBJDID, string sTZRYID, string sCLVID, string sWLVID) { TSQL SQL = new TSQL(dberp); SQL.Clear(); SQL.Add(" insert into XLXSBJDCLHQ(XLXSBJDCLHQ_BJDID,XLXSBJDCLHQ_BJCLID,XLXSBJDCLHQ_WSJG,XLXSBJDCLHQ_HSJG,XLXSBJDCLHQ_WSJGF,XLXSBJDCLHQ_HSJGF, "); SQL.Add(" XLXSBJDCLHQ_JGYBJCLID1,XLXSBJDCLHQ_WSJG1,XLXSBJDCLHQ_HSJG1,XLXSBJDCLHQ_JGBL1, "); SQL.Add(" XLXSBJDCLHQ_WHR,XLXSBJDCLHQ_WHRID,XLXSBJDCLHQ_WHSJ)"); SQL.Add(" select V_XLXSBJDCLHQ_BJDID,V_XLXSBJDCLHQ_BJCLID,V_XLXSBJDCLHQ_WSJG,V_XLXSBJDCLHQ_HSJG,V_XLXSBJDCLHQ_WSJGF,V_XLXSBJDCLHQ_HSJGF, "); SQL.Add(" V_XLXSBJDCLHQ_JGYBJCLID1,V_XLXSBJDCLHQ_WSJG1,V_XLXSBJDCLHQ_HSJG1,V_XLXSBJDCLHQ_JGBL1, "); SQL.Add(" V_XLXSBJDCLHQ_WHR,V_XLXSBJDCLHQ_WHRID,V_XLXSBJDCLHQ_WHSJ "); SQL.Add(" from V_XLXSBJDCLHQ where V_XLXSBJDCLHQ_BJDID = :pBJDID and V_XLXSBJDCLHQ_TZRYID = :pTZRYID and V_XLXSBJDCLHQ_ID = :pCLVID "); SQL.ParamByName("pBJDID").Value = sBJDID; SQL.ParamByName("pTZRYID").Value = sTZRYID; SQL.ParamByName("pCLVID").Value = sCLVID; dberp.ExecSQL(SQL); SQL.Clear(); SQL.Add(" delete from V_XLXSBJDCLHQ where V_XLXSBJDCLHQ_TZRYID = :pTZRYID and V_XLXSBJDCLHQ_BJDID = :pBJDID and V_XLXSBJDCLHQ_ID = :pCLVID "); SQL.ParamByName("pTZRYID").Value = sTZRYID; SQL.ParamByName("pBJDID").Value = sBJDID; SQL.ParamByName("pCLVID").Value = sCLVID; dberp.ExecSQL(SQL); SQL.Clear(); SQL.Add(" insert into XLXSBJDWLHQ(XLXSBJDWLHQ_BJDID,XLXSBJDWLHQ_WLID,XLXSBJDWLHQ_WSJG,XLXSBJDWLHQ_HSJG,XLXSBJDWLHQ_WSJGF,XLXSBJDWLHQ_HSJGF, "); SQL.Add(" XLXSBJDWLHQ_JGYBJCLID1,XLXSBJDWLHQ_WSJG1,XLXSBJDWLHQ_HSJG1,XLXSBJDWLHQ_JGBL1, "); SQL.Add(" XLXSBJDWLHQ_WHR,XLXSBJDWLHQ_WHRID,XLXSBJDWLHQ_WHSJ)"); SQL.Add(" select V_XLXSBJDWLHQ_BJDID,V_XLXSBJDWLHQ_WLID,V_XLXSBJDWLHQ_WSJG,V_XLXSBJDWLHQ_HSJG,V_XLXSBJDWLHQ_WSJGF,V_XLXSBJDWLHQ_HSJGF, "); SQL.Add(" V_XLXSBJDWLHQ_JGYBJCLID1,V_XLXSBJDWLHQ_WSJG1,V_XLXSBJDWLHQ_HSJG1,V_XLXSBJDWLHQ_JGBL1, "); SQL.Add(" V_XLXSBJDWLHQ_WHR,V_XLXSBJDWLHQ_WHRID,V_XLXSBJDWLHQ_WHSJ "); SQL.Add(" from V_XLXSBJDWLHQ where V_XLXSBJDWLHQ_BJDID = :pBJDID and V_XLXSBJDWLHQ_TZRYID = :pTZRYID and V_XLXSBJDWLHQ_ID = :pWLVID "); SQL.ParamByName("pBJDID").Value = sBJDID; SQL.ParamByName("pTZRYID").Value = sTZRYID; SQL.ParamByName("pWLVID").Value = sWLVID; dberp.ExecSQL(SQL); SQL.Clear(); SQL.Add(" delete from V_XLXSBJDWLHQ where V_XLXSBJDWLHQ_TZRYID = :pTZRYID and V_XLXSBJDWLHQ_BJDID = :pBJDID and V_XLXSBJDWLHQ_ID = :pWLVID "); SQL.ParamByName("pTZRYID").Value = CurSession.RYID; SQL.ParamByName("pBJDID").Value = sBJDID; SQL.ParamByName("pWLVID").Value = sWLVID; dberp.ExecSQL(SQL); } private string GetVID(string sBJDID, string sTZRYID) { string sVID = ""; TDatabase dberp = TContextDatabases.GetDatabase("DBERP"); TSQL SQL = new TSQL(dberp); SQL.Clear(); SQL.Add(" select V_XLXSBJDCLHQ_ID from V_XLXSBJDCLHQ where V_XLXSBJDCLHQ_TZRYID = :pTZRYID and V_XLXSBJDCLHQ_BJDID = :pBJDID "); SQL.ParamByName("pTZRYID").Value = sTZRYID; SQL.ParamByName("pBJDID").Value = sBJDID; DataSet ds = dberp.OpenDataSet(SQL); if (ds.Tables[0].Rows.Count > 0) { sVID = ds.Tables[0].Rows[0]["V_XLXSBJDCLHQ_ID"].ToString().Trim(); } return sVID; } #endregion #region 控件事件 protected void btnSZHQ_Click(object sender, EventArgs e) { if (grdSHD.SelectedIndex < 0) { ShowMessage("请选择需要处理的记录！"); return; } else if (grdSHD.GetValueByFieldName("XLXSBJCL_BJHQ", grdSHD.NewRows[grdSHD.SelectedIndex]).Trim().Equals("2|物料行情")) { ShowMessage("仅能选择“报价行情”为“1|材料行情”的报价材料！"); return; } TDatabase dberp = TContextDatabases.GetDatabase("DBERP"); TSQL SQL = new TSQL(dberp); string sBJCLID = grdSHD.GetValueByFieldName("XLXSBJCL_BJCLID", grdSHD.NewRows[grdSHD.SelectedIndex]).Trim(); string sCLJG = grdSHD.GetValueByFieldName("CLJG", grdSHD.NewRows[grdSHD.SelectedIndex]).Trim(); string sHSJG = grdSHD.GetValueByFieldName("HSJG", grdSHD.NewRows[grdSHD.SelectedIndex]).Trim(); string sZZSL = grdSHD.GetValueByFieldName("XLXSBJCL_ZZSL", grdSHD.NewRows[grdSHD.SelectedIndex]).Trim(); string sWSJGF = grdSHD.GetValueByFieldName("WSJGF", grdSHD.NewRows[grdSHD.SelectedIndex]).Trim(); string sHSJGF = grdSHD.GetValueByFieldName("HSJGF", grdSHD.NewRows[grdSHD.SelectedIndex]).Trim(); //加工料对应加工原料 无税/含税价格 string sJGYBJCLID1 = grdSHD.GetValueByFieldName("V_XLXSBJDCLHQ_JGYBJCLID1", grdSHD.NewRows[grdSHD.SelectedIndex]).Trim(); string sJGBL1 = ""; string sJCCLJG = ""; string sJCHSJG = ""; string sSQL = string.Format(" select 1 from XLXSBJCL " + " where XLXSBJCL_JGYBJCLID = '{0}' or (XLXSBJCL_BJCLID ='{0}' and isnull(XLXSBJCL_JGYBJCLID,' ') <>' ' ) ", sBJCLID); if (!CMISproDataBase.ExistsRecord("DBERP", sSQL)) { ShowMessage("当前报价材料非加工料或加工原料！"); return; } else { if (!sJGYBJCLID1.Trim().Equals("")) { sJGBL1 = grdSHD.GetValueByFieldName("V_XLXSBJDCLHQ_JGBL1", grdSHD.NewRows[grdSHD.SelectedIndex]).Trim(); foreach (GridViewRow row in grdSHD.NewRows) { if (grdSHD.GetValueByFieldName("XLXSBJCL_BJCLID", row).ToString().Trim().Equals(sJGYBJCLID1.Trim())) { sJCCLJG = grdSHD.GetValueByFieldName("CLJG", row).ToString().Trim(); sJCHSJG = grdSHD.GetValueByFieldName("HSJG", row).ToString().Trim(); } } } TPageParams pageParams = new TPageParams(); pageParams.UrlPath = "XLBJD_JGFLDInfo.aspx"; pageParams.Title = "设置加工料/加工原料行情"; pageParams.AddString("BJCLID", sBJCLID); pageParams.AddString("CLJG", sCLJG); pageParams.AddString("HSJG", sHSJG); pageParams.AddString("ZZSL", sZZSL); pageParams.AddString("WSJGF", sWSJGF); pageParams.AddString("HSJGF", sHSJGF); pageParams.AddString("JCCLJG", sJCCLJG); pageParams.AddString("JCHSJG", sJCHSJG); pageParams.AddString("JGYBJCLID1", sJGYBJCLID1); pageParams.AddString("JGBL1", sJGBL1); pageParams.AddString("JGBID", PageParam.GetRTrimString("JGBID")); pageParams.AddString("BJDHQLY", PageParam.GetRTrimString("BJDHQLY")); pageParams.AddString("BJLY", PageParam.GetRTrimString("BJLY")); pageParams.AddString("BJRQ", PageParam.GetRTrimString("BJRQ")); pageParams.AddString("BJFLID", PageParam.GetRTrimString("BJFLID")); pageParams.AddString("BJDID", PageParam.GetRTrimString("BJDID")); pageParams.AddString("CLVID", txtVCLHQID.Value); pageParams.AddString("WLVID", txtVWLHQID.Value); //pageParams.AddString("TBJCLID", PageParam.GetRTrimString("TBJCLID")); //pageParams.AddString("LBJCLID", PageParam.GetRTrimString("LBJCLID")); //pageParams.AddString("HSBZ", PageParam.GetRTrimString("HSBZ")); pageParams.AddString("CallBackCtrlID", btnCallBack.ClientID.Trim()); OpenDialog(pageParams); } } protected void btnWLHQ_Click(object sender, EventArgs e) { if (grdSHD.SelectedIndex < 0) { ShowMessage("请选择需要处理的记录！"); return; } string sBJCLID = grdSHD.GetValueByFieldName("XLXSBJCL_BJCLID", grdSHD.NewRows[grdSHD.SelectedIndex]).Trim(); string sBJHQ = grdSHD.GetValueByFieldName("XLXSBJCL_BJHQ", grdSHD.NewRows[grdSHD.SelectedIndex]).Trim(); if (!sBJHQ.Split('|')[0].Equals("2")) { ShowMessage("仅能选择“报价行情”为“2|物料行情”的报价材料！"); return; } TPageParams pageParams = new TPageParams(); pageParams.UrlPath = "XLBJD_WLHQList.aspx"; pageParams.AddString("BJCLID", sBJCLID); pageParams.AddString("JGBID", PageParam.GetRTrimString("JGBID")); pageParams.AddString("BJDHQLY", PageParam.GetRTrimString("BJDHQLY")); pageParams.AddString("BJLY", PageParam.GetRTrimString("BJLY")); pageParams.AddString("BJRQ", PageParam.GetRTrimString("BJRQ")); pageParams.AddString("BJFLID", PageParam.GetRTrimString("BJFLID")); pageParams.AddString("BJDID", PageParam.GetRTrimString("BJDID")); OpenDialog(pageParams); } protected void btnCallBack_Click(object sender, EventArgs e) { int index = grdSHD.SelectedIndex; Master_OnQuery(); GridDataBind(); BillAreaUpdate(); FormAreaUpdate(); grdSHD.SelectedIndex = index; } //更新自材料行情(不取最新物料行情，仅根据更新后的材料行情更新加工物料的行情) protected void btnUpdCLHQ_Click(object sender, EventArgs e) { TDatabase dberp = TContextDatabases.GetDatabase("DBERP"); TSQL sSQL = new TSQL(dberp); string sBJDID = PageParam.GetRTrimString("BJDID"); if (sBJDID.Equals("")) return; string sTBJCLID = PageParam.GetRTrimString("TBJCLID"); string sLBJCLID = PageParam.GetRTrimString("LBJCLID"); string sTBJCLMC = GetBJCLMC(sTBJCLID); string sLBJCLMC = GetBJCLMC(sLBJCLID); //“铜价”和“铝价”都为空时，保持原先逻辑 if (sTBJCLMC.Equals("") && sLBJCLMC.Equals("")) { Confirm.Register("IsQD", "将使用今天最新的材料行情进行更新，是否继续？"); if (Confirm.GetValue("IsQD").Trim().Equals("")) { Confirm.Ask("IsQD"); return; } else if (Confirm.GetValue("IsQD").Trim().Equals("N")) { Confirm.Clear(); return; } else if (Confirm.GetValue("IsQD").Trim().Equals("Y")) { Confirm.Clear(); try { dberp.BeginTrans(); XLBJDUtils.InsertXLXSBJDCLHQ_WLHQ_V(dberp, sSQL, PageParam.GetRTrimString("BJDHQLY"), sBJDID, txtVWLHQID.Value, txtVCLHQID.Value, CurSession, DateTime.Now.ToShortDateString(), PageParam.GetRTrimString("HSBZ").Equals("Y"), sTBJCLID, sLBJCLID, CMISproConvert.StrToFloat(PageParam.GetRTrimString("TJ")), CMISproConvert.StrToFloat(PageParam.GetRTrimString("LJ")), CurSession.UserName, CurSession.UserId, PageParam.GetRTrimString("JGBID")); dberp.CommitTrans(); } catch (Exception ex) { dberp.RollbackTrans(); ShowMessage(ex.Message); return; } } } else { //“铜价”不为空，“铝价”为空 if (!string.IsNullOrEmpty(sTBJCLMC) && string.IsNullOrEmpty(sLBJCLMC)) { Confirm.Register("IsQD", string.Format("是否同步更新“{0}”？", sTBJCLMC)); } //“铜价”为空，“铝价”不为空 else if (string.IsNullOrEmpty(sTBJCLMC) && !string.IsNullOrEmpty(sLBJCLMC)) { Confirm.Register("IsQD", string.Format("是否同步更新“{0}”？", sLBJCLMC)); } //“铜价”，“铝价”都不为空 else if (!string.IsNullOrEmpty(sTBJCLMC) && !string.IsNullOrEmpty(sLBJCLMC)) { Confirm.Register("IsQD", string.Format("是否同步更新“{0}、{1}”？", sTBJCLMC, sLBJCLMC)); } else { return; } if (Confirm.GetValue("IsQD").Trim().Equals("")) { Confirm.Ask("IsQD"); return; } else if (Confirm.GetValue("IsQD").Trim().Equals("N")) { Confirm.Clear(); try { dberp.BeginTrans(); XLBJDUtils.InsertXLXSBJDCLHQ_WLHQ_V(dberp, sSQL, PageParam.GetRTrimString("BJDHQLY"), sBJDID, txtVWLHQID.Value, txtVCLHQID.Value, CurSession, DateTime.Now.ToShortDateString(), PageParam.GetRTrimString("HSBZ").Equals("Y"), sTBJCLID, sLBJCLID, CMISproConvert.StrToFloat(PageParam.GetRTrimString("TJ")), CMISproConvert.StrToFloat(PageParam.GetRTrimString("LJ")), CurSession.UserName, CurSession.UserId, PageParam.GetRTrimString("JGBID")); dberp.CommitTrans(); } catch (Exception ex) { dberp.RollbackTrans(); ShowMessage(ex.Message); return; } } else if (Confirm.GetValue("IsQD").Trim().Equals("Y")) { Confirm.Clear(); try { dberp.BeginTrans(); XLBJDUtils.InsertXLXSBJDCLHQ_WLHQ_V(dberp, sSQL, PageParam.GetRTrimString("BJDHQLY"), sBJDID, txtVWLHQID.Value, txtVCLHQID.Value, CurSession, DateTime.Now.ToShortDateString(), PageParam.GetRTrimString("HSBZ").Equals("Y"), "", "", 0, 0, CurSession.UserName, CurSession.UserId, PageParam.GetRTrimString("JGBID")); dberp.CommitTrans(); } catch (Exception ex) { dberp.RollbackTrans(); ShowMessage(ex.Message); return; } } } //刷新 Master_OnQuery(); GridDataBind(); FormAreaUpdate(); ShowMessage("更新完成！"); } protected void btnUpdCLHQ_MenuItemClick(object sender, string ItemIndex) { string sBJDID = PageParam.GetRTrimString("BJDID"); if (sBJDID.Equals("")) return; string sTBJCLID = PageParam.GetRTrimString("TBJCLID"); string sLBJCLID = PageParam.GetRTrimString("LBJCLID"); string sTBJCLMC = GetBJCLMC(sTBJCLID); string sLBJCLMC = GetBJCLMC(sLBJCLID); switch (ItemIndex) { case "CGHQ"://更新自采购行情 //“铜价”和“铝价”都为空时，保持原先逻辑 if (sTBJCLMC.Equals("") && sLBJCLMC.Equals("")) { Confirm.Register("IsQD", "将使用的采购行情进行更新，是否继续？"); if (Confirm.GetValue("IsQD").Trim().Equals("")) { Confirm.Ask("IsQD"); return; } else if (Confirm.GetValue("IsQD").Trim().Equals("N")) { Confirm.Clear(); return; } else if (Confirm.GetValue("IsQD").Trim().Equals("Y")) { Confirm.Clear(); XLBJDUtils.InsertXLXSBJDCLHQ_WLHQ_WithCGHQ_V(PageParam.GetRTrimString("BJDID"), txtVWLHQID.Value, txtVCLHQID.Value, CurSession, DateTime.Now.ToShortDateString(), PageParam.GetRTrimString("HSBZ").Equals("Y"), sTBJCLID, sLBJCLID, CMISproConvert.StrToFloat(PageParam.GetRTrimString("TJ")), CMISproConvert.StrToFloat(PageParam.GetRTrimString("LJ")), CurSession.UserName, CurSession.UserId, PageParam.GetRTrimString("JGBID")); } } else { //“铜价”不为空，“铝价”为空 if (!string.IsNullOrEmpty(sTBJCLMC) && string.IsNullOrEmpty(sLBJCLMC)) { Confirm.Register("IsQD", string.Format("是否同步更新“{0}”？", sTBJCLMC)); } //“铜价”为空，“铝价”不为空 else if (string.IsNullOrEmpty(sTBJCLMC) && !string.IsNullOrEmpty(sLBJCLMC)) { Confirm.Register("IsQD", string.Format("是否同步更新“{0}”？", sLBJCLMC)); } //“铜价”，“铝价”都不为空 else if (!string.IsNullOrEmpty(sTBJCLMC) && !string.IsNullOrEmpty(sLBJCLMC)) { Confirm.Register("IsQD", string.Format("是否同步更新“{0}、{1}”？", sTBJCLMC, sLBJCLMC)); } else { return; } if (Confirm.GetValue("IsQD").Trim().Equals("")) { Confirm.Ask("IsQD"); return; } else if (Confirm.GetValue("IsQD").Trim().Equals("N")) { Confirm.Clear(); XLBJDUtils.InsertXLXSBJDCLHQ_WLHQ_WithCGHQ_V(PageParam.GetRTrimString("BJDID"), txtVWLHQID.Value, txtVCLHQID.Value, CurSession, DateTime.Now.ToShortDateString(), PageParam.GetRTrimString("HSBZ").Equals("Y"), sTBJCLID, sLBJCLID, CMISproConvert.StrToFloat(PageParam.GetRTrimString("TJ")), CMISproConvert.StrToFloat(PageParam.GetRTrimString("LJ")), CurSession.UserName, CurSession.UserId, PageParam.GetRTrimString("JGBID")); } else if (Confirm.GetValue("IsQD").Trim().Equals("Y")) { Confirm.Clear(); XLBJDUtils.InsertXLXSBJDCLHQ_WLHQ_WithCGHQ_V(PageParam.GetRTrimString("BJDID"), txtVWLHQID.Value, txtVCLHQID.Value, CurSession, DateTime.Now.ToShortDateString(), PageParam.GetRTrimString("HSBZ").Equals("Y"), "", "", 0, 0, CurSession.UserName, CurSession.UserId, PageParam.GetRTrimString("JGBID")); } } //刷新 Master_OnQuery(); GridDataBind(); FormAreaUpdate(); ShowMessage("已完成'1|材料行情'类报价材料的更新！"); break; } } //调整物料行情 protected void btnTZWLHQ_Click(object sender, EventArgs e) { TPageParams pageParams = new TPageParams(); pageParams.UrlPath = "XLBJD_WLHQTZList.aspx"; pageParams.AddString("BJRQ", PageParam.GetRTrimString("BJRQ")); pageParams.AddString("BJDID", PageParam.GetRTrimString("BJDID")); pageParams.AddString("WLVID", txtVWLHQID.Value); pageParams.AddString("CLVID", txtVCLHQID.Value); OpenDialog(pageParams); } #endregion } }