protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { vw_invr302 invr302Model; StringBuilder sbSql = null; string sqlBody = ""; string sqlOrderBy = ""; DataTable dtMaster; List <Master> masterList; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; try { if (Vw_Invr302 != null) //他窗引用時 { invr302Model = Vw_Invr302; } else { invr302Model = DrMaster.ToItem <vw_invr302>(); } queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(invr302Model.iga01)) { queryModel = new QueryInfo(); queryModel.TableName = "iga_tb"; queryModel.ColumnName = "iga01"; queryModel.ColumnType = TabMaster.DtSource.Columns["iga01"].DataType.Name; queryModel.Value = invr302Model.iga01; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(invr302Model.iga02_s)) { sbQuerySingle.AppendLine("AND iga02>=@iga02_s"); sqlParmList.Add(new SqlParameter("@iga02_s", invr302Model.iga02_s)); } if (!GlobalFn.varIsNull(invr302Model.iga02_e)) { sbQuerySingle.AppendLine("AND iga02<=@iga02_e"); sqlParmList.Add(new SqlParameter("@iga02_e", invr302Model.iga02_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @"SELECT iga_tb.*, bec02 as iga03_c, beb02 as iga04_c, bab02 as iga01_c, igb02,igb03,igb04,igb05,igb06, igb09,igb10, ica03 FROM iga_tb LEFT JOIN igb_tb ON iga01=igb01 LEFT JOIN bec_tb ON iga03=bec01 --員工 LEFT JOIN beb_tb ON iga04=beb01 --部門 LEFT JOIN baa_tb ON 1=1 LEFT JOIN bab_tb ON substring(iga01,1,baa06)=bab01 LEFT JOIN ica_tb ON igb03=ica01 WHERE 1=1 AND igaconf='Y' AND iga00='2' "; dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlOrderBy), sqlParmList.ToArray()); dtMaster.TableName = "Master"; if (dtMaster == null || dtMaster.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } masterList = dtMaster.ToList <Master>(true); foreach (Master masterModel in masterList) { var bejModel = BoBas.OfGetBejModel(masterModel.igb06); if (bejModel != null) { masterModel.igb05_str = string.Format("{0:N" + bejModel.bej03 + "}", masterModel.igb05);//數量 } else { masterModel.igb05_str = masterModel.igb05.ToString(); } } pReport.RegData("Master", masterList); pReport.CacheAllData = true; ////處理跳頁 StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; if (invr302Model.jump_yn.ToUpper() == "Y") { footerBand1.NewPageAfter = true; footerBand1.ResetPageNumber = true; } else { footerBand1.NewPageAfter = false; footerBand1.ResetPageNumber = false; } return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { vw_invr401 invr401Model; StringBuilder sbSql = null; string sqlBody = ""; string sqlOrderBy = ""; DataTable dtMaster; List <Master> masterList; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; try { if (Vw_Invr401 != null) //他窗引用時 { invr401Model = Vw_Invr401; } else { invr401Model = DrMaster.ToItem <vw_invr401>(); } queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(invr401Model.ila01)) { queryModel = new QueryInfo(); queryModel.TableName = "ila_tb"; queryModel.ColumnName = "ila01"; queryModel.ColumnType = TabMaster.DtSource.Columns["ila01"].DataType.Name; queryModel.Value = invr401Model.ila01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invr401Model.ila03)) { queryModel = new QueryInfo(); queryModel.TableName = "ila_tb"; queryModel.ColumnName = "ila03"; queryModel.ColumnType = TabMaster.DtSource.Columns["ila03"].DataType.Name; queryModel.Value = invr401Model.ila03; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(invr401Model.ila02_s)) { sbQuerySingle.AppendLine("AND ila02>=@ila02_s"); sqlParmList.Add(new SqlParameter("@ila02_s", invr401Model.ila02_s)); } if (!GlobalFn.varIsNull(invr401Model.ila02_e)) { sbQuerySingle.AppendLine("AND ila02<=@ila02_e"); sqlParmList.Add(new SqlParameter("@ila02_e", invr401Model.ila02_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @"SELECT ila_tb.*, sca02 as ila03_c, bec02 as ila04_c, beb02 as ila05_c, bab02 as ila01_c, bel02 as ila14_c, sbg1.sbg02 as ila15_c, sbg2.sbg02 as ila16_c, ilb02,ilb03,ilb04,ilb05,ilb06, ilb11,ilb13, bej03 FROM ila_tb LEFT JOIN ilb_tb ON ila01=ilb01 LEFT JOIN sca_tb ON ila03=sca01 --客戶 LEFT JOIN bec_tb ON ila04=bec01 --員工 LEFT JOIN beb_tb ON ila05=beb01 --部門 LEFT JOIN baa_tb ON 1=1 LEFT JOIN bab_tb ON substring(ila01,1,baa06)=bab01 LEFT JOIN ica_tb ON ilb03=ica01 LEFT JOIN bej_tb ON ilb03=bej01 LEFT JOIN bel_tb ON ila14=bel01 --貨運方式 LEFT JOIN sbg_tb sbg1 ON ila15=sbg1.sbg01 --運送起點 LEFT JOIN sbg_tb sbg2 ON ila16=sbg2.sbg01 --運送終點 WHERE 1=1 AND ilaconf='Y' "; //處理排序 switch (invr401Model.order_by) { case "1": //1.依借出日期 sqlOrderBy = " ORDER BY ila02"; break; case "2": //2.依客戶 sqlOrderBy = " ORDER BY ila03"; break; } dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlOrderBy), sqlParmList.ToArray()); dtMaster.TableName = "Master"; if (dtMaster == null || dtMaster.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } masterList = dtMaster.ToList <Master>(true); foreach (Master masterModel in masterList) { masterModel.ilb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ilb05);//數量 } pReport.RegData("Master", masterList); pReport.CacheAllData = true; ////處理跳頁 StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; if (invr401Model.jump_yn.ToUpper() == "Y") { footerBand1.NewPageAfter = true; footerBand1.ResetPageNumber = true; } else { footerBand1.NewPageAfter = false; footerBand1.ResetPageNumber = false; } return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { //DataSet ds; vw_manr311 manr311Model; StringBuilder sbSql = null; string sqlBody = ""; DataTable dtMaster; List <YR.ERP.DAL.YRModel.Reports.Man.Manr311.Master> masterList; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; try { if (Vw_Manr311 != null) //他窗引用時 { manr311Model = Vw_Manr311; } else { manr311Model = DrMaster.ToItem <vw_manr311>(); } queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(manr311Model.mfa01)) { queryModel = new QueryInfo(); queryModel.TableName = "mfa_tb"; queryModel.ColumnName = "mfa01"; queryModel.ColumnType = TabMaster.DtSource.Columns["mfa01"].DataType.Name; queryModel.Value = manr311Model.mfa01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(manr311Model.mfa03)) { queryModel = new QueryInfo(); queryModel.TableName = "mfa_tb"; queryModel.ColumnName = "mfa03"; queryModel.ColumnType = TabMaster.DtSource.Columns["mfa03"].DataType.Name; queryModel.Value = manr311Model.mfa03; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); if (!GlobalFn.varIsNull(manr311Model.mfa06)) { queryModel = new QueryInfo(); queryModel.TableName = "mfa_tb"; queryModel.ColumnName = "mfa06"; queryModel.ColumnType = TabMaster.DtSource.Columns["mfa06"].DataType.Name; queryModel.Value = manr311Model.mfa06; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(manr311Model.mfa02_s)) { sbQuerySingle.AppendLine("AND mfa02>=@mfa02_s"); sqlParmList.Add(new SqlParameter("@mfa02_s", manr311Model.mfa02_s)); } if (!GlobalFn.varIsNull(manr311Model.mfa02_e)) { sbQuerySingle.AppendLine("AND mfa02<=@mfa02_e"); sqlParmList.Add(new SqlParameter("@mfa02_e", manr311Model.mfa02_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @"SELECT mfa00,mfa01,mfa02,mfa03,mfa04,mfa05, mfa06,mfa07,mfa08, bab02 as mfa01_c, pca02 as mfa03_c, bec02 as mfa04_c,beb02 as mfa05_c, mfb02,mfb03,mfb04,mfb05,mfb06, mfb07,mfb09, ica.ica03 as ica03, bej.bej03 as bej03, '' as mfb05_str, pcb03,pcb04,pcb06,pcb07 FROM mfa_tb LEFT JOIN mfb_tb On mfa01=mfb01 LEFT JOIN baa_tb ON 1=1 LEFT JOIN bab_tb ON substring(mfa01,1,baa06)=bab01 LEFT JOIN pca_tb ON mfa03=pca01 --廠商 LEFT JOIN bec_tb ON mfa04=bec01 --員工 LEFT JOIN beb_tb ON mfa05=beb01 --部門 LEFT JOIN ica_tb ica ON mfb03=ica.ica01 LEFT JOIN bej_tb bej ON mfb06=bej.bej01 LEFT JOIN pcb_tb ON mfa03=pcb01 and mfa07=pcb02 WHERE 1=1 AND mfaconf='Y' AND mfa00='2' "; dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray()); dtMaster.TableName = "Master"; if (dtMaster == null || dtMaster.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } masterList = dtMaster.ToList <Master>(); foreach (Master masterModel in masterList) { masterModel.mfb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.mfb05);//子件數量 } pReport.RegData(dtMaster); pReport.RegData("Master", masterList); pReport.CacheAllData = true; //處理排序 StiDataBand stiDataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"]; switch (manr311Model.order_by) { case "1": //1.依出庫日期 stiDataBand1.Sort = new string[] { "ASC", "mfa02" }; break; case "2": //2.依廠商 stiDataBand1.Sort = new string[] { "ASC", "mfa03" }; break; } //處理跳頁 StiGroupFooterBand grouperBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; if (manr311Model.jump_yn.ToUpper() == "Y") { grouperBand1.NewPageAfter = true; grouperBand1.ResetPageNumber = true; } else { grouperBand1.NewPageAfter = false; grouperBand1.ResetPageNumber = false; } pReport.Compile(); return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { //DataSet ds; vw_invr522 invr522Model; string sqlBody = ""; DataTable dtIlaTb; List<QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere, strOrderBy; StringBuilder sbQuerySingle = null; List<SqlParameter> sqlParmList; List<Master> resultList = null; try { invr522Model = DrMaster.ToItem<vw_invr522>(); resultList = new List<Master>(); queryInfoList = new List<QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(invr522Model.ipa01)) { queryModel = new QueryInfo(); queryModel.TableName = "ipa_tb"; queryModel.ColumnName = "ipa01"; queryModel.ColumnType = TabMaster.DtSource.Columns["ipa01"].DataType.Name; queryModel.Value = invr522Model.ipa01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invr522Model.ipacreu)) { queryModel = new QueryInfo(); queryModel.TableName = "ipa_tb"; queryModel.ColumnName = "ipacreu"; queryModel.ColumnType = TabMaster.DtSource.Columns["ipacreu"].DataType.Name; queryModel.Value = invr522Model.ipacreu; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invr522Model.ipacreg)) { queryModel = new QueryInfo(); queryModel.TableName = "ipa_tb"; queryModel.ColumnName = "ipacreg"; queryModel.ColumnType = TabMaster.DtSource.Columns["ipacreg"].DataType.Name; queryModel.Value = invr522Model.ipacreg; queryInfoList.Add(queryModel); } sqlParmList = new List<SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(invr522Model.ipa03_s)) { sbQuerySingle.AppendLine("AND ipa03>=@ipa03_s"); sqlParmList.Add(new SqlParameter("@ila02_s", invr522Model.ipa03_s)); } if (!GlobalFn.varIsNull(invr522Model.ipa03_e)) { sbQuerySingle.AppendLine("AND ipa03<=@ipa03_e"); sqlParmList.Add(new SqlParameter("@ipa03_e", invr522Model.ipa03_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) strWhere += strSecurity; //取得單頭 sqlBody = @" SELECT ipa01,ipb02,ipb03,ipb04,ipb05, ipb06,ipb07,ipb30,ipb40,ipb50, ica02,ica03, bej03, CONVERT(DECIMAL,0) AS qty1, CONVERT(DECIMAL,0) AS qty2, CONVERT(DECIMAL,0) AS dif_qty, CONVERT(NVARCHAR(20),0) AS qty1_str, CONVERT(NVARCHAR(20),0) AS qty2_str, CONVERT(NVARCHAR(20),0) AS dif_qty_str FROM ipa_tb INNER JOIN ipb_tb ON ipa01=ipb01 LEFT JOIN ica_tb ON ipb03=ica01 LEFT JOIN bej_tb ON ipb07=bej01 WHERE ipa05='Y' "; strOrderBy = " ORDER BY ipa01,ipb02"; dtIlaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, strOrderBy), sqlParmList.ToArray()); dtIlaTb.TableName = "Master"; if (dtIlaTb != null) { resultList.AddRange(dtIlaTb.ToList<Master>()); } if (resultList == null || resultList.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return false; } foreach (Master masterModel in resultList) { switch (invr522Model.type1) { case "1": masterModel.qty1 = masterModel.ipb30; break; case "2": masterModel.qty1 = masterModel.ipb40; break; case "3": masterModel.qty1 = masterModel.ipb50; break; case "4": masterModel.qty1 = masterModel.ipb06; break; } switch (invr522Model.type2) { case "1": masterModel.qty2 = masterModel.ipb30; break; case "2": masterModel.qty2 = masterModel.ipb40; break; case "3": masterModel.qty2 = masterModel.ipb50; break; case "4": masterModel.qty2 = masterModel.ipb06; break; } masterModel.dif_qty = masterModel.qty2 - masterModel.qty1; masterModel.qty1_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.qty1); masterModel.qty2_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.qty2); masterModel.dif_qty_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.dif_qty); } if (invr522Model.dif_yn == "Y")//只顯示差異 { if (resultList.Where(x => x.dif_qty != 0).Count() == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return false; } pReport.RegData("master", resultList.Where(x => x.dif_qty != 0)); } else { pReport.RegData("master", resultList); } pReport.CacheAllData = true; StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; footerBand1.NewPageAfter = true; footerBand1.ResetPageNumber = true; pReport.Compile(); return true; } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { //DataSet ds; vw_invr410 invr410Model; StringBuilder sbSql = null; string sqlBody = ""; DataTable dtIlaTb; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; List <Master> resultList = null; try { invr410Model = DrMaster.ToItem <vw_invr410>(); resultList = new List <Master>(); queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(invr410Model.ila03)) { queryModel = new QueryInfo(); queryModel.TableName = "ila_tb"; queryModel.ColumnName = "ila03"; queryModel.ColumnType = TabMaster.DtSource.Columns["ila03"].DataType.Name; queryModel.Value = invr410Model.ila03; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invr410Model.ila04)) { queryModel = new QueryInfo(); queryModel.TableName = "ila_tb"; queryModel.ColumnName = "ila04"; queryModel.ColumnType = TabMaster.DtSource.Columns["ila04"].DataType.Name; queryModel.Value = invr410Model.ila04; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invr410Model.ila05)) { queryModel = new QueryInfo(); queryModel.TableName = "ila_tb"; queryModel.ColumnName = "ila05"; queryModel.ColumnType = TabMaster.DtSource.Columns["ila05"].DataType.Name; queryModel.Value = invr410Model.ila05; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(invr410Model.ila02_s)) { sbQuerySingle.AppendLine("AND ila02>=@ila02_s"); sqlParmList.Add(new SqlParameter("@ila02_s", invr410Model.ila02_s)); } if (!GlobalFn.varIsNull(invr410Model.ila02_e)) { sbQuerySingle.AppendLine("AND ila02<=@ila02_e"); sqlParmList.Add(new SqlParameter("@ila02_e", invr410Model.ila02_e)); } if (invr410Model.conf_yn != "0") { if (invr410Model.conf_yn == "1") { sbQuerySingle.AppendLine("AND ilaconf = 'Y'"); } if (invr410Model.conf_yn == "2") { sbQuerySingle.AppendLine("AND ilaconf = 'N'"); } } if (invr410Model.close_yn != "0") { if (invr410Model.close_yn == "1")//未結案 { sbQuerySingle.AppendLine("AND (ilb05-ilb15) >0 "); } if (invr410Model.close_yn == "2")//已結案 { sbQuerySingle.AppendLine("AND (ilb05-ilb15) <=0 "); } } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @" SELECT ila01,ila02,ila03,sca03 as ila03_c , ilb02,ila04,bec02 AS ila04_c,ila05,beb03 AS ila05_c, ilb03,ilb04,ilb05,'' as ilb05_str,ilb06, bej03,ilb11, ilb15,'' as ilb15_str, ilb16 FROM ila_tb LEFT JOIN ilb_tb ON ila01=ilb01 LEFT JOIN sca_tb ON ila03=sca01 LEFT JOIN bec_tb ON ila04=bec01 LEFT JOIN beb_tb ON ila05=beb01 LEFT JOIN bej_tb ON ilb06=bej01 WHERE ilaconf='Y' AND ilb01 IS NOT NULL "; dtIlaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray()); dtIlaTb.TableName = "Master"; if (dtIlaTb != null) { resultList.AddRange(dtIlaTb.ToList <Master>()); } if (resultList == null || resultList.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } foreach (Master masterModel in resultList) { //數量處理 masterModel.ilb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ilb05); //數量 masterModel.ilb15_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ilb15); //數量 } pReport.RegData("master", resultList); pReport.CacheAllData = true; //處理排序--這裡利用group來達到 StiGroupHeaderBand stiGroupHeaderBand1 = (StiGroupHeaderBand)pReport.GetComponents()["GroupHeaderBand1"]; switch (invr410Model.order_by) { case "1": //1.依出庫日期 stiGroupHeaderBand1.Condition.Value = "{Master.ila02}"; break; case "2": //2.依客戶 stiGroupHeaderBand1.Condition.Value = "{Master.ila03}"; break; case "3": //3.依部門 stiGroupHeaderBand1.Condition.Value = "{Master.ila05}"; break; case "4": //4.依業務 stiGroupHeaderBand1.Condition.Value = "{Master.ila04}"; break; case "5": //4.依出庫倉 stiGroupHeaderBand1.Condition.Value = "{Master.ila11}"; break; } //處理跳頁 StiDataBand dataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"]; if (invr410Model.jump_yn.ToUpper() == "Y") { dataBand1.NewPageAfter = true; dataBand1.ResetPageNumber = true; } else { dataBand1.NewPageAfter = false; dataBand1.ResetPageNumber = false; } pReport.Compile(); return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { vw_purr500 purr500Model; StringBuilder sbSql = null; string sqlBody = ""; string sqlOrderBy = ""; DataTable dtMaster; List <Master> masterList; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; try { if (Vw_Purr500 != null) //他窗引用時 { purr500Model = Vw_Purr500; } else { purr500Model = DrMaster.ToItem <vw_purr500>(); } queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(purr500Model.pha01)) { queryModel = new QueryInfo(); queryModel.TableName = "pha_tb"; queryModel.ColumnName = "pha01"; queryModel.ColumnType = TabMaster.DtSource.Columns["pha01"].DataType.Name; queryModel.Value = purr500Model.pha01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(purr500Model.pha03)) { queryModel = new QueryInfo(); queryModel.TableName = "pha_tb"; queryModel.ColumnName = "pha03"; queryModel.ColumnType = TabMaster.DtSource.Columns["pha03"].DataType.Name; queryModel.Value = purr500Model.pha03; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(purr500Model.pha02_s)) { sbQuerySingle.AppendLine("AND pha02>=@pha02_s"); sqlParmList.Add(new SqlParameter("@pha02_s", purr500Model.pha02_s)); } if (!GlobalFn.varIsNull(purr500Model.pha02_e)) { sbQuerySingle.AppendLine("AND pha02<=@pha02_e"); sqlParmList.Add(new SqlParameter("@pha02_e", purr500Model.pha02_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @"SELECT pha_tb.*, pca02 as pha03_c, bec02 as pha04_c, beb02 as pha05_c, bab02 as pha01_c, bea03,bea04,bea05, phb02,phb03,phb04,phb05,phb06, phb09,phb10,phb10t, phb16, ica03, bej03 FROM pha_tb LEFT JOIN phb_tb ON pha01=phb01 LEFT JOIN pca_tb ON pha03=pca01 --廠商 LEFT JOIN bec_tb ON pha04=bec01 --員工 LEFT JOIN beb_tb ON pha05=beb01 --部門 LEFT JOIN baa_tb ON 1=1 LEFT JOIN bab_tb ON substring(pha01,1,baa06)=bab01 LEFT JOIN bea_tb ON phacomp=beacomp LEFT JOIN ica_tb ON phb03=ica01 LEFT JOIN bej_tb ON phb03=bej01 WHERE 1=1 AND phaconf='Y' "; //處理排序 switch (purr500Model.order_by) { case "1": //1.依出貨日期 sqlOrderBy = " ORDER BY pha02"; break; case "2": //2.依客戶 sqlOrderBy = " ORDER BY pha03"; break; } dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlOrderBy), sqlParmList.ToArray()); dtMaster.TableName = "Master"; if (dtMaster == null || dtMaster.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } masterList = dtMaster.ToList <Master>(true); foreach (Master masterModel in masterList) { //處理金額 if (!GlobalFn.varIsNull(masterModel.pha10)) { var bekModel = BoBas.OfGetBekModel(masterModel.pha10); if (bekModel != null) { //單頭 masterModel.pha13_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.pha13); masterModel.pha13t_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.pha13t); masterModel.pha13g_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.pha13g); //單身 masterModel.phb09_str = string.Format("{0:N" + bekModel.bek03 + "}", masterModel.phb09); masterModel.phb10_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.phb10); masterModel.phb10t_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.phb10t); } } masterModel.phb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.phb05);//數量 } pReport.RegData("Master", masterList); pReport.CacheAllData = true; ////處理跳頁 StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; if (purr500Model.jump_yn.ToUpper() == "Y") { footerBand1.NewPageAfter = true; footerBand1.ResetPageNumber = true; } else { footerBand1.NewPageAfter = false; footerBand1.ResetPageNumber = false; } return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { //DataSet ds; vw_stpr500 stpr500Model; StringBuilder sbSql = null; DataTable dtSeaTb, dtSebTb; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; try { if (Vw_Stpr500 != null) //他窗引用時 { stpr500Model = Vw_Stpr500; } else { stpr500Model = DrMaster.ToItem <vw_stpr500>(); } queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(stpr500Model.sha01)) { queryModel = new QueryInfo(); queryModel.TableName = "sha_tb"; queryModel.ColumnName = "sha01"; queryModel.ColumnType = TabMaster.DtSource.Columns["sha01"].DataType.Name; queryModel.Value = stpr500Model.sha01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(stpr500Model.sha03)) { queryModel = new QueryInfo(); queryModel.TableName = "sha_tb"; queryModel.ColumnName = "sha03"; queryModel.ColumnType = TabMaster.DtSource.Columns["sha03"].DataType.Name; queryModel.Value = stpr500Model.sha03; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(stpr500Model.sha02_s)) { sbQuerySingle.AppendLine("AND sha02>=@sha02_s"); sqlParmList.Add(new SqlParameter("@sha02_s", stpr500Model.sha02_s)); } if (!GlobalFn.varIsNull(stpr500Model.sha02_e)) { sbQuerySingle.AppendLine("AND sha02<=@sha02_e"); sqlParmList.Add(new SqlParameter("@sha02_e", stpr500Model.sha02_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sbSql = new StringBuilder(); sbSql.AppendLine("SELECT sha_tb.*,"); sbSql.AppendLine(" sca03 AS sha03_c,"); sbSql.AppendLine(" bec02 AS sha04_c,"); sbSql.AppendLine(" beb02 AS sha05_c,"); sbSql.AppendLine(" bab02 AS sha01_c,"); sbSql.AppendLine(" bea04,"); sbSql.AppendLine(" bea05,"); sbSql.AppendLine(" bea06"); sbSql.AppendLine("FROM sha_tb"); sbSql.AppendLine(" LEFT JOIN sca_tb ON sha03=sca01"); sbSql.AppendLine(" LEFT JOIN bec_tb ON sha04=bec01"); sbSql.AppendLine(" LEFT JOIN beb_tb ON sha05=beb01"); sbSql.AppendLine(" LEFT JOIN baa_tb ON 1=1"); sbSql.AppendLine(" LEFT JOIN bab_tb ON substring(sha01,1,baa06)=bab01"); sbSql.AppendLine(" LEFT JOIN bea_tb ON beacomp=shacomp"); sbSql.AppendLine("WHERE 1=1"); sbSql.AppendLine(" AND shaconf='Y'"); dtSeaTb = BoMaster.OfGetDataTable(string.Concat(sbSql.ToString(), strWhere), sqlParmList.ToArray()); dtSeaTb.TableName = "Master"; if (dtSeaTb == null || dtSeaTb.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } //取得明細 sbSql = new StringBuilder(); sbSql.AppendLine("SELECT * FROM shb_tb"); sbSql.AppendLine("WHERE EXISTS("); sbSql.AppendLine(" SELECT 1 FROM sha_tb"); sbSql.AppendLine(" WHERE sha01=shb01"); sbSql.AppendLine(strWhere); sbSql.AppendLine(")"); dtSebTb = BoMaster.OfGetDataTable(sbSql.ToString(), sqlParmList.ToArray()); dtSebTb.TableName = "Detail"; pReport.RegData(dtSeaTb); pReport.RegData(dtSebTb); pReport.CacheAllData = true; //處理排序 StiDataBand stiDataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"]; switch (stpr500Model.order_by) { case "1": //1.依出貨日期 stiDataBand1.Sort = new string[] { "ASC", "sha02" }; break; case "2": //2.依客戶 stiDataBand1.Sort = new string[] { "ASC", "sha03" }; break; } //處理跳頁 StiFooterBand footerBand1 = (StiFooterBand)pReport.GetComponents()["FooterBand1"]; if (stpr500Model.jump_yn.ToUpper() == "Y") { footerBand1.NewPageAfter = true; footerBand1.ResetPageNumber = true; } else { footerBand1.NewPageAfter = false; footerBand1.ResetPageNumber = false; } //pReport.Compile(); return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { //DataSet ds; vw_manr210 manr210Model; StringBuilder sbSql = null; string sqlBody = ""; DataTable dtMaster; List <YR.ERP.DAL.YRModel.Reports.Man.Manr210.Master> masterList; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; try { if (Vw_Manr210 != null) //他窗引用時 { manr210Model = Vw_Manr210; } else { manr210Model = DrMaster.ToItem <vw_manr210>(); } queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(manr210Model.mea01)) { queryModel = new QueryInfo(); queryModel.TableName = "mea_tb"; queryModel.ColumnName = "mea01"; queryModel.ColumnType = TabMaster.DtSource.Columns["mea01"].DataType.Name; queryModel.Value = manr210Model.mea01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(manr210Model.mea03)) { queryModel = new QueryInfo(); queryModel.TableName = "mea_tb"; queryModel.ColumnName = "mea03"; queryModel.ColumnType = TabMaster.DtSource.Columns["mea03"].DataType.Name; queryModel.Value = manr210Model.mea03; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(manr210Model.mea20)) { queryModel = new QueryInfo(); queryModel.TableName = "mea_tb"; queryModel.ColumnName = "mea20"; queryModel.ColumnType = TabMaster.DtSource.Columns["mea20"].DataType.Name; queryModel.Value = manr210Model.mea03; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(manr210Model.mea02_s)) { sbQuerySingle.AppendLine("AND mea02>=@mea02_s"); sqlParmList.Add(new SqlParameter("@mea02_s", manr210Model.mea02_s)); } if (!GlobalFn.varIsNull(manr210Model.mea02_e)) { sbQuerySingle.AppendLine("AND mea02<=@mea02_e"); sqlParmList.Add(new SqlParameter("@mea02_e", manr210Model.mea02_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @"SELECT mea00,mea01,mea02,mea03,mea04,mea05, mea06,mea07,mea08,mea09,mea10, mea11,mea12,mea13,mea14,mea14t, mea15,mea16,mea17,mea18,mea19, mea20,mea21,mea22,mea23,mea24, mea25,mea26,mea27,mea28,mea29, bab02 as mea01_c, pca02 as mea03_c, bec02 as mea04_c,beb02 as mea05_c, bef03 as mea11_c,pbb02 as mea12_c, ica_main.ica03 as ica03_main, '' as mea13_str,'' as mea14_str,'' as mea14t_str, '' as mea22_str, bej_main.bej03 as bej03_main, meb02,meb03,meb04,meb05,meb06, meb07, ica_detail.ica03 as ica03_detail, bej_detail.bej03 as bej03_detail, '' as meb05_str FROM mea_tb LEFT JOIN meb_tb On mea01=meb01 LEFT JOIN pca_tb ON mea03=pca01 --廠商 LEFT JOIN bec_tb ON mea04=bec01 --員工 LEFT JOIN beb_tb ON mea05=beb01 --部門 LEFT JOIN bef_tb ON mea11=bef02 AND bef01='1' --收付款條件 LEFT JOIN pbb_tb ON mea12=pbb01 --採購取價原則 LEFT JOIN baa_tb ON 1=1 LEFT JOIN bab_tb ON substring(mea01,1,baa06)=bab01 LEFT JOIN ica_tb ica_main ON mea20=ica_main.ica01 LEFT JOIN bej_tb bej_main ON mea23=bej_main.bej01 LEFT JOIN ica_tb ica_detail ON meb03=ica_detail.ica01 LEFT JOIN bej_tb bej_detail ON meb06=bej_detail.bej01 WHERE 1=1 AND meaconf='Y' AND mea00='2' "; dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray()); dtMaster.TableName = "Master"; if (dtMaster == null || dtMaster.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } masterList = dtMaster.ToList <Master>(); foreach (Master masterModel in masterList) { masterModel.mea22_str = string.Format("{0:N" + masterModel.bej03_main + "}", masterModel.mea22); //主件數量 masterModel.meb05_str = string.Format("{0:N" + masterModel.bej03_detail + "}", masterModel.meb05); //子件數量 var bekModel = BoBas.OfGetBekModel(masterModel.mea10); masterModel.mea13_str = string.Format("{0:N" + bekModel.bek03 + "}", masterModel.mea13); masterModel.mea14_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.mea14); masterModel.mea14t_str = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.mea14t); } pReport.RegData(dtMaster); pReport.RegData("Master", masterList); pReport.CacheAllData = true; //處理排序 StiDataBand stiDataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"]; switch (manr210Model.order_by) { case "1": //1.依託工日期 stiDataBand1.Sort = new string[] { "ASC", "mea02" }; break; case "2": //2.依廠商 stiDataBand1.Sort = new string[] { "ASC", "mea03" }; break; } //處理跳頁 StiGroupFooterBand grouperBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; if (manr210Model.jump_yn.ToUpper() == "Y") { grouperBand1.NewPageAfter = true; grouperBand1.ResetPageNumber = true; } else { grouperBand1.NewPageAfter = false; grouperBand1.ResetPageNumber = false; } pReport.Compile(); return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { //DataSet ds; vw_stpr410 stpr410Model; StringBuilder sbSql = null; string sqlBody = ""; DataTable dtSgaTb, dtShaTb; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; List <MasterA> resultList = null; try { stpr410Model = DrMaster.ToItem <vw_stpr410>(); resultList = new List <MasterA>(); #region 處理銷售 if (stpr410Model.sale_type == "0" || stpr410Model.sale_type == "1") { queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(stpr410Model.sga03)) { queryModel = new QueryInfo(); queryModel.TableName = "sga_tb"; queryModel.ColumnName = "sga03"; queryModel.ColumnType = TabMaster.DtSource.Columns["sga03"].DataType.Name; queryModel.Value = stpr410Model.sga03; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(stpr410Model.sga04)) { queryModel = new QueryInfo(); queryModel.TableName = "sga_tb"; queryModel.ColumnName = "sga04"; queryModel.ColumnType = TabMaster.DtSource.Columns["sga04"].DataType.Name; queryModel.Value = stpr410Model.sga04; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(stpr410Model.sga05)) { queryModel = new QueryInfo(); queryModel.TableName = "sga_tb"; queryModel.ColumnName = "sga05"; queryModel.ColumnType = TabMaster.DtSource.Columns["sga05"].DataType.Name; queryModel.Value = stpr410Model.sga05; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(stpr410Model.sga02_s)) { sbQuerySingle.AppendLine("AND sga02>=@sga02_s"); sqlParmList.Add(new SqlParameter("@sga02_s", stpr410Model.sga02_s)); } if (!GlobalFn.varIsNull(stpr410Model.sga02_e)) { sbQuerySingle.AppendLine("AND sga02<=@sga02_e"); sqlParmList.Add(new SqlParameter("@sga02_e", stpr410Model.sga02_e)); } if (stpr410Model.confirm_type == "1") { sbQuerySingle.AppendLine("AND sgaconf ='Y'"); } else if (stpr410Model.confirm_type == "2") { sbQuerySingle.AppendLine("AND ISNULL(sgaconf,'') <>'Y'"); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @" SELECT '銷貨' AS sale_type, sga01,sga02,sga03,sca03 as sga03_c , sgb02,sga04,bec02 AS sga04_c,sga05,beb03 AS sga05_c, sga10,sga21, sgb03,sgb04,sgb05,'' as sgb05_str,sgb06, bej03, 0.0 as price,'' as price_string, sgb10,'' as sgb10_string, sgb10t,'' as sgb10t_string, sgb16 FROM sga_tb LEFT JOIN sgb_tb ON sga01=sgb01 LEFT JOIN sca_tb ON sga03=sca01 LEFT JOIN bec_tb ON sga04=bec01 LEFT JOIN beb_tb ON sga05=beb01 LEFT JOIN bej_tb ON sgb06=bej01 WHERE sgb01 IS NOT NULL "; dtSgaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray()); dtSgaTb.TableName = "Master"; if (dtSgaTb != null) { resultList.AddRange(dtSgaTb.ToList <YR.ERP.DAL.YRModel.Reports.Stp.Stpr410.MasterA>()); } } #endregion #region 處理退貨/折讓 if (stpr410Model.sale_type == "0" || stpr410Model.sale_type == "2") { queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(stpr410Model.sga03)) { queryModel = new QueryInfo(); queryModel.TableName = "sha_tb"; queryModel.ColumnName = "sha03"; queryModel.ColumnType = TabMaster.DtSource.Columns["sga03"].DataType.Name; queryModel.Value = stpr410Model.sga03; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(stpr410Model.sga04)) { queryModel = new QueryInfo(); queryModel.TableName = "sha_tb"; queryModel.ColumnName = "sha04"; queryModel.ColumnType = TabMaster.DtSource.Columns["sga04"].DataType.Name; queryModel.Value = stpr410Model.sga04; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(stpr410Model.sga05)) { queryModel = new QueryInfo(); queryModel.TableName = "sha_tb"; queryModel.ColumnName = "sha05"; queryModel.ColumnType = TabMaster.DtSource.Columns["sga05"].DataType.Name; queryModel.Value = stpr410Model.sga05; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(stpr410Model.sga02_s)) { sbQuerySingle.AppendLine("AND sha02>=@sha02_s"); sqlParmList.Add(new SqlParameter("@sha02_s", stpr410Model.sga02_s)); } if (!GlobalFn.varIsNull(stpr410Model.sga02_e)) { sbQuerySingle.AppendLine("AND sha02<=@sha02_e"); sqlParmList.Add(new SqlParameter("@sha02_e", stpr410Model.sga02_e)); } if (stpr410Model.confirm_type == "1") { sbQuerySingle.AppendLine("AND shaconf ='Y'"); } else if (stpr410Model.confirm_type == "2") { sbQuerySingle.AppendLine("AND ISNULL(shaconf,'') <>'Y'"); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @" SELECT CASE WHEN shb17='1' THEN '銷退' ELSE '折讓' END AS sale_type, sha01 AS sga01,sha02 AS sga02,sha03 AS sga03,sca03 as sga03_c , shb02 AS sgb02,sha04 AS sga04,bec02 AS sga04_c,sha05 AS sga05,beb03 AS sga05_c, sha10 AS sga10,shb19 AS sga21, shb03 AS sgb03,shb04 AS sgb04,shb05 AS sgb05,'' as sgb05_str,shb06 AS sgb06, bej03, 0.0 as price,'' as price_string, -1*shb10 AS sgb10,'' as sgb10_string, -1*shb10t AS sgb10t,'' as sgb10t_string, shb16 AS sgb16 FROM sha_tb LEFT JOIN shb_tb ON sha01=shb01 LEFT JOIN sca_tb ON sha03=sca01 LEFT JOIN bec_tb ON sha04=bec01 LEFT JOIN beb_tb ON sha05=beb01 LEFT JOIN bej_tb ON shb06=bej01 WHERE shb01 IS NOT NULL "; dtSgaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray()); dtSgaTb.TableName = "Master"; if (dtSgaTb != null) { resultList.AddRange(dtSgaTb.ToList <YR.ERP.DAL.YRModel.Reports.Stp.Stpr410.MasterA>()); } } #endregion if (resultList == null || resultList.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } foreach (MasterA masterModel in resultList) { var bekModel = BoBas.OfGetBekModel(masterModel.sga10); //處理單價 if (masterModel.sgb05 > 0) { masterModel.price = Math.Round((masterModel.sgb10 / masterModel.sgb05), Convert.ToInt16(bekModel.bek03), MidpointRounding.AwayFromZero); } else { masterModel.price = masterModel.sgb10; } masterModel.price_string = string.Format("{0:N" + bekModel.bek03 + "}", masterModel.price); masterModel.sgb10_string = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.sgb10); masterModel.sgb10t_string = string.Format("{0:N" + bekModel.bek04 + "}", masterModel.sgb10t); //數量處理 masterModel.sgb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.sgb05);//數量 } pReport.RegData("master", resultList); pReport.CacheAllData = true; //處理排序--這裡利用group來達到 StiGroupHeaderBand stiGroupHeaderBand1 = (StiGroupHeaderBand)pReport.GetComponents()["GroupHeaderBand1"]; StiDataBand StiDataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"]; switch (stpr410Model.order_by) { case "1": //1.依出庫日期 stiGroupHeaderBand1.Condition.Value = "{Master.sga02}"; StiDataBand1.Sort = new string[6] { "ASC", "sga02", "ASC", "sga01", "ASC", "sgb02", }; break; case "2": //2.依客戶 stiGroupHeaderBand1.Condition.Value = "{Master.sga03}"; StiDataBand1.Sort = new string[6] { "ASC", "sga03", "ASC", "sga01", "ASC", "sgb02", }; break; case "3": //3.依部門 stiGroupHeaderBand1.Condition.Value = "{Master.sga05}"; StiDataBand1.Sort = new string[6] { "ASC", "sga05", "ASC", "sga01", "ASC", "sgb02", }; break; case "4": //4.依業務 stiGroupHeaderBand1.Condition.Value = "{Master.sga04}"; StiDataBand1.Sort = new string[6] { "ASC", "sga04", "ASC", "sga01", "ASC", "sgb02", }; break; } //處理跳頁 StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; if (stpr410Model.jump_yn.ToUpper() == "Y") { footerBand1.NewPageAfter = true; footerBand1.ResetPageNumber = true; } else { footerBand1.NewPageAfter = false; footerBand1.ResetPageNumber = false; } pReport.Compile(); return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { //DataSet ds; vw_invr520 invr520Model; StringBuilder sbSql = null; string sqlBody = ""; DataTable dtIlaTb; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere, strOrderBy; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; List <Master> resultList = null; try { invr520Model = DrMaster.ToItem <vw_invr520>(); resultList = new List <Master>(); queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(invr520Model.ipa01)) { queryModel = new QueryInfo(); queryModel.TableName = "ipa_tb"; queryModel.ColumnName = "ipa01"; queryModel.ColumnType = TabMaster.DtSource.Columns["ipa01"].DataType.Name; queryModel.Value = invr520Model.ipa01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invr520Model.ipacreu)) { queryModel = new QueryInfo(); queryModel.TableName = "ipa_tb"; queryModel.ColumnName = "ipacreu"; queryModel.ColumnType = TabMaster.DtSource.Columns["ipacreu"].DataType.Name; queryModel.Value = invr520Model.ipacreu; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invr520Model.ipacreg)) { queryModel = new QueryInfo(); queryModel.TableName = "ipa_tb"; queryModel.ColumnName = "ipacreg"; queryModel.ColumnType = TabMaster.DtSource.Columns["ipacreg"].DataType.Name; queryModel.Value = invr520Model.ipacreg; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(invr520Model.ipa03_s)) { sbQuerySingle.AppendLine("AND ipa03>=@ipa03_s"); sqlParmList.Add(new SqlParameter("@ila02_s", invr520Model.ipa03_s)); } if (!GlobalFn.varIsNull(invr520Model.ipa03_e)) { sbQuerySingle.AppendLine("AND ipa03<=@ipa03_e"); sqlParmList.Add(new SqlParameter("@ipa03_e", invr520Model.ipa03_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @" SELECT ipa01,ipa02,ipa03,ipa04,ipa05, ipa06,ipa07,ipa08, ipb02,ipb03,ipb04,ipb05, ipb06,ipb07, ica02,ica03 FROM ipa_tb LEFT JOIN ipb_tb ON ipa01=ipb01 LEFT JOIN ica_tb ON ipb03=ica01 WHERE 1=1 "; strOrderBy = " ORDER BY ipa01,ipb02"; dtIlaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, strOrderBy), sqlParmList.ToArray()); dtIlaTb.TableName = "Master"; if (dtIlaTb != null) { resultList.AddRange(dtIlaTb.ToList <Master>()); } if (resultList == null || resultList.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } //foreach (Master masterModel in resultList) //{ // //數量處理 // masterModel.ilb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ilb05);//數量 // masterModel.ilb15_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ilb15);//數量 //} pReport.RegData("master", resultList); pReport.CacheAllData = true; StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; footerBand1.NewPageAfter = true; footerBand1.ResetPageNumber = true; pReport.Compile(); return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { //DataSet ds; vw_stpr501 stpr501Model; //StringBuilder sbSql = null; string sqlBody; string sqlOrderBy = ""; DataTable dtSeaTb; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; try { if (Vw_Stpr501 != null) //他窗引用時 { stpr501Model = Vw_Stpr501; } else { stpr501Model = DrMaster.ToItem <vw_stpr501>(); } queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(stpr501Model.sha01)) { queryModel = new QueryInfo(); queryModel.TableName = "sha_tb"; queryModel.ColumnName = "sha01"; queryModel.ColumnType = TabMaster.DtSource.Columns["sha01"].DataType.Name; queryModel.Value = stpr501Model.sha01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(stpr501Model.sha03)) { queryModel = new QueryInfo(); queryModel.TableName = "sha_tb"; queryModel.ColumnName = "sha03"; queryModel.ColumnType = TabMaster.DtSource.Columns["sha03"].DataType.Name; queryModel.Value = stpr501Model.sha03; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(stpr501Model.sha02_s)) { sbQuerySingle.AppendLine("AND sha02>=@sha02_s"); sqlParmList.Add(new SqlParameter("@sha02_s", stpr501Model.sha02_s)); } if (!GlobalFn.varIsNull(stpr501Model.sha02_e)) { sbQuerySingle.AppendLine("AND sha02<=@sha02_e"); sqlParmList.Add(new SqlParameter("@sha02_e", stpr501Model.sha02_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } sqlBody = @"SELECT sha01,sha02,sha03,sca02 as sha03_c, sha06,sha09, sha13,sha13t,sha13g, '' as sha13_str,'' as sha13t_str,'' as sha13g_str, bea01,bea03,bea04,bea05,bea06, sca12,sca17, bek03,bek04, shb02,shb03,shb04,shb05, shb09,shb10,shb10t, shb17,shb19,shb20, bej03, '' as shb05_str, '' as shb09_str,'' as shb10_str,'' as shb10g_str FROM sha_tb LEFT JOIN shb_tb ON sha01=shb01 LEFT JOIN sca_tb ON sha03=sca01 LEFT JOIN baa_tb ON 1=1 LEFT JOIN bab_tb ON substring(sha01,1,baa06)=bab01 LEFT JOIN bea_tb ON beacomp=shacomp LEFT JOIN bek_tb ON sha10=bek01 LEFT JOIN bej_tb ON shb06=bej01 WHERE 1=1 AND shaconf='Y' "; //處理排序 switch (stpr501Model.order_by) { case "1": //1.依出貨日期 sqlOrderBy = " ORDER BY sha02"; break; case "2": //2.依客戶 sqlOrderBy = " ORDER BY sha03"; break; } dtSeaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlOrderBy), sqlParmList.ToArray()); dtSeaTb.TableName = "Master"; if (dtSeaTb == null || dtSeaTb.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } var MasterList = dtSeaTb.ToList <YR.ERP.DAL.YRModel.Reports.Stp.Stpr501.Master>(); foreach (YR.ERP.DAL.YRModel.Reports.Stp.Stpr501.Master masterModel in MasterList) { //處理單頭金額 masterModel.sha13_str = string.Format("{0:N" + masterModel.bek04 + "}", masterModel.sha13); masterModel.sha13t_str = string.Format("{0:N" + masterModel.bek04 + "}", masterModel.sha13t); masterModel.sha13g_str = string.Format("{0:N" + masterModel.bek04 + "}", masterModel.sha13g); if (masterModel.shb17 == "1") { masterModel.shb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.shb05); //數量 masterModel.shb09_str = string.Format("{0:N" + masterModel.bek03 + "}", masterModel.shb09); //單價 } else//折讓 { masterModel.shb05_str = ""; masterModel.shb09_str = ""; } masterModel.shb10_str = string.Format("{0:N" + masterModel.bek03 + "}", masterModel.shb10); //未稅 masterModel.shb10g_str = string.Format("{0:N" + masterModel.bek03 + "}", masterModel.shb10t - masterModel.shb10); //稅額 } pReport.RegData("Master", MasterList); //pReport.RegData(dtSeaTb); pReport.CacheAllData = true; ////處理跳頁 StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; if (stpr501Model.jump_yn.ToUpper() == "Y") { footerBand1.NewPageAfter = true; footerBand1.ResetPageNumber = true; } else { footerBand1.NewPageAfter = false; footerBand1.ResetPageNumber = false; } //pReport.Compile(); return(true); } catch (Exception ex) { throw ex; } }