protected override bool WfQuery() { DataRow drNew; try { //DtMaster = BoMaster.OfGetDataTable(StrSqlBody + " AND 1<>1 "); DtMaster = BoMaster.OfGetDataTable(StrSqlBody); ////修改column 型別為string 並將實際的型別丟到 column.prefix //foreach (DataColumn ldc_temp in DtMaster.Columns) //{ // if (ldc_temp.Prefix != "") // continue; // ldc_temp.Prefix = ldc_temp.DataType.Name; // ldc_temp.DataType = typeof(string); //} BindingMaster.DataSource = DtMaster; uGrid_Master.DataSource = BindingMaster; drNew = DtMaster.NewRow(); DtMaster.Rows.Add(drNew); uGrid_Master.ActiveCell = uGrid_Master.Rows[0].Cells[1]; return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { vw_glar300 glar300Model; StringBuilder sbSql = null; string sqlBody = ""; string sqlOrderBy = ""; DataTable dtMaster; List <Master> masterList; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; decimal maxPageRows = 5; //每頁限定筆數 List <Master> addMasterList = null; //用來增加空白筆數的集合 List <SqlParameter> sqlParmList; try { if (Vw_Galr300 != null) //他窗引用時 { glar300Model = Vw_Galr300; } else { glar300Model = DrMaster.ToItem <vw_glar300>(); } queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(glar300Model.gfa01)) { queryModel = new QueryInfo(); queryModel.TableName = "gfa_tb"; queryModel.ColumnName = "gfa01"; queryModel.ColumnType = TabMaster.DtSource.Columns["gfa01"].DataType.Name; queryModel.Value = glar300Model.gfa01; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); //他窗引用時,僅查詢單號 if (Vw_Galr300 == null) { if (!GlobalFn.varIsNull(glar300Model.gfa02_s)) { sbQuerySingle.AppendLine("AND gfa02>=@gfa02_s"); sqlParmList.Add(new SqlParameter("@gfa02_s", glar300Model.gfa02_s)); } if (!GlobalFn.varIsNull(glar300Model.gfa02_e)) { sbQuerySingle.AppendLine("AND gfa02<=@gfa02_e"); sqlParmList.Add(new SqlParameter("@gfa02_e", glar300Model.gfa02_e)); } //過帳否 if (glar300Model.gfapost == "1") { sbQuerySingle.AppendLine("AND gfapost='N'"); } else if (glar300Model.gfapost == "2") { sbQuerySingle.AppendLine("AND gfapost='Y'"); } //有效否 if (glar300Model.gfaconf == "1") { sbQuerySingle.AppendLine("AND gfaconf <>'X' "); } else if (glar300Model.gfaconf == "2") { sbQuerySingle.AppendLine("AND gfapost='X' "); } //列印否 if (glar300Model.gfaprno == 1) { sbQuerySingle.AppendLine("AND gfaprno =0"); } else if (glar300Model.gfapost == "2") { sbQuerySingle.AppendLine("AND gfaprno >0 "); } } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @"SELECT gfa01,gfa02,gfa03,gfa04,gfa05, gfa06,gfa07,gfa08,gfa09, gfaprno,gfaconf,gfacond,gfaconu, gfapost,gfaposd,gfaposu, gac02 as gfa01_c, gfb02,gfb03,gfb04,gfb05, gfb06,gfb07,gfb08,gfb09,gfb10, gba02 AS gfb03_c, beb03 AS gfb05_c, bek04, gba05 FROM gfa_tb LEFT JOIN gfb_tb ON gfa01=gfb01 LEFT JOIN baa_tb ON 1=1 LEFT JOIN gac_tb ON substring(gfa01,1,baa06)=gac01 LEFT JOIN gba_tb ON gfb03=gba01 LEFT JOIN bek_tb ON gfb08=bek01 LEFT JOIN beb_tb ON gfb05=beb01 WHERE 1=1 "; sqlOrderBy = " ORDER BY gfa01,gfa02"; 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); //取得本幣資料 var baaModel = BoBas.OfGetBaaModel(); if (baaModel == null) { WfShowErrorMsg("未設定基本參數,請先設定!"); return(false); } var baa04 = baaModel.baa04; var bekUsModel = BoBas.OfGetBekModel(baa04); if (bekUsModel == null) { WfShowErrorMsg("查無本幣資料,請先設定!"); return(false); } //依群組來處理 var gfa01GroupList = from o in masterList group o by o.gfa01 into g select g.First() ; addMasterList = new List <Master>(); foreach (Master groupModel in gfa01GroupList) { var gfa01 = groupModel.gfa01; var groupList = masterList.Where(p => p.gfa01 == gfa01).ToList(); int groupTotalRecs = groupList.Count(); int groupTotalPages = Convert.ToInt16(Math.Ceiling(Convert.ToDecimal(groupTotalRecs / maxPageRows))); for (int i = 0; i < groupList.Count(); i++) { var masterModel = groupList[i]; masterModel.gfa03_str = string.Format("{0:N" + bekUsModel.bek04 + "}", masterModel.gfa03); masterModel.gfa04_str = string.Format("{0:N" + bekUsModel.bek04 + "}", masterModel.gfa04); if (masterModel.gfb06 == "1") { masterModel.gfb07d_str = string.Format("{0:N" + bekUsModel.bek04 + "}", masterModel.gfb07); masterModel.gfb10d_str = string.Format("{0:N" + masterModel.bek04 + "}", masterModel.gfb10); } else { masterModel.gfb07c_str = string.Format("{0:N" + bekUsModel.bek04 + "}", masterModel.gfb07); masterModel.gfb10c_str = string.Format("{0:N" + masterModel.bek04 + "}", masterModel.gfb10); } masterModel.groupPageNo = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal((i + 1) / maxPageRows))); masterModel.groupSeqNo = i + 1; masterModel.groupTotalPages = groupTotalPages; if (masterModel.groupTotalPages != masterModel.groupPageNo) { masterModel.isPageEnd = false; } else { masterModel.isPageEnd = true; } if (i == groupTotalRecs - 1) { //處理要新增的空白筆數 var mod = groupTotalRecs % maxPageRows; if (mod != 0) { var addRecs = maxPageRows - mod; if (addRecs > 0) { for (int j = 0; j < addRecs; j++) { var addMasterModel = new Master(); addMasterModel.gfa01 = masterModel.gfa01; addMasterModel.gfa02 = masterModel.gfa02; addMasterModel.gfa03 = masterModel.gfa03; addMasterModel.gfa04 = masterModel.gfa04; addMasterModel.gfa05 = masterModel.gfa05; addMasterModel.gfa06 = masterModel.gfa06; addMasterModel.gfa07 = masterModel.gfa07; addMasterModel.gfa08 = masterModel.gfa08; addMasterModel.gfa09 = masterModel.gfa09; addMasterModel.gfaprno = masterModel.gfaprno; addMasterModel.gfaconf = masterModel.gfaconf; addMasterModel.gfacond = masterModel.gfacond; addMasterModel.gfaconu = masterModel.gfaconu; addMasterModel.gfapost = masterModel.gfapost; addMasterModel.gfaposd = masterModel.gfaposd; addMasterModel.gfaposu = masterModel.gfaposu; addMasterModel.gfa01_c = masterModel.gfa01_c; addMasterModel.gfb02 = 0; addMasterModel.gfb03 = ""; addMasterModel.gfb04 = ""; addMasterModel.gfb05 = ""; addMasterModel.gfb06 = ""; addMasterModel.gfb07 = 0; addMasterModel.gfb08 = ""; addMasterModel.gfb09 = 0; addMasterModel.gfb10 = 0; addMasterModel.gfb03_c = ""; addMasterModel.gfb05_c = ""; addMasterModel.bek04 = 0; addMasterModel.gba05 = ""; addMasterModel.gfa03_str = masterModel.gfa03_str; addMasterModel.gfa04_str = masterModel.gfa04_str; addMasterModel.gfb07c_str = ""; addMasterModel.gfb07d_str = ""; addMasterModel.gfb10c_str = ""; addMasterModel.gfb10d_str = ""; addMasterModel.groupPageNo = masterModel.groupPageNo; addMasterModel.groupTotalPages = masterModel.groupTotalPages; addMasterModel.groupSeqNo = i + j + 2; addMasterModel.isPageEnd = masterModel.isPageEnd; addMasterList.Add(addMasterModel); } } } } } } if (addMasterList != null && addMasterList.Count > 0) { masterList.AddRange(addMasterList); } pReport.RegData("Master", masterList); pReport.CacheAllData = true; ////處理跳頁 //StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; //if (glar300Model.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_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) { //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; } }
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 void WfIniSqlBody() { StringBuilder sbSql; try { if (MsgInfoReturned == null) { throw new Exception("未實體化msgInfoReturned"); } sbSql = new StringBuilder(); sbSql.AppendLine("SELECT CONVERT(NVARCHAR(1),'N') is_pick,"); sbSql.AppendLine("CONVERT(NVARCHAR(100),'') AS action,"); sbSql.AppendLine("CONVERT(NVARCHAR(100),'') AS action_c"); StrSqlBody = sbSql.ToString(); DtMaster = BoMaster.OfGetDataTable(sbSql.ToString()); Admi650Model = new vw_admi650() { azp01 = StrPickNo, azp02 = "action功能挑選", azp03 = "", azp03_c = "", azp04 = "", azp05 = "", azp06 = "N", azp07 = "", azp08 = "Y", }; Admi650sList = new List <vw_admi650s>() { new vw_admi650s() { azq01 = Admi650Model.azp01, azq02 = 1, azq03 = "", azq04 = "action", azq04_c = "action", azq06 = "1", azq08 = 100 }, new vw_admi650s() { azq01 = Admi650Model.azp01, azq02 = 2, azq03 = "", azq04 = "action_c", azq04_c = "action名稱", azq06 = "1", azq08 = 200 } }; //修改column 型別為string 並將實際的型別丟到 column.prefix foreach (DataColumn dcTempColumn in DtMaster.Columns) { if (dcTempColumn.Prefix != "") { continue; } dcTempColumn.Prefix = dcTempColumn.DataType.Name; dcTempColumn.DataType = typeof(string); } BindingMaster.DataSource = DtMaster; uGrid_Master.DataSource = BindingMaster; } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { //DataSet ds; vw_invr336 invr336Model; 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 { invr336Model = DrMaster.ToItem <vw_invr336>(); resultList = new List <Master>(); queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(invr336Model.ifa01)) { queryModel = new QueryInfo(); queryModel.TableName = "ifa_tb"; queryModel.ColumnName = "ifa01"; queryModel.ColumnType = TabMaster.DtSource.Columns["ifa01"].DataType.Name; queryModel.Value = invr336Model.ifa01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invr336Model.ifa03)) { queryModel = new QueryInfo(); queryModel.TableName = "ifa_tb"; queryModel.ColumnName = "ifa03"; queryModel.ColumnType = TabMaster.DtSource.Columns["ifa03"].DataType.Name; queryModel.Value = invr336Model.ifa03; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invr336Model.ifa04)) { queryModel = new QueryInfo(); queryModel.TableName = "ifa_tb"; queryModel.ColumnName = "ifa04"; queryModel.ColumnType = TabMaster.DtSource.Columns["ifa04"].DataType.Name; queryModel.Value = invr336Model.ifa04; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(invr336Model.ifa02_s)) { sbQuerySingle.AppendLine("AND ifa02>=@ifa02_s"); sqlParmList.Add(new SqlParameter("@ifa02_s", invr336Model.ifa02_s)); } if (!GlobalFn.varIsNull(invr336Model.ifa02_e)) { sbQuerySingle.AppendLine("AND ifa02<=@ifa02_e"); sqlParmList.Add(new SqlParameter("@ifa02_s", invr336Model.ifa02_e)); } if (invr336Model.conf_yn != "0") { if (invr336Model.conf_yn == "1") { sbQuerySingle.AppendLine("AND ifaconf = 'Y'"); } if (invr336Model.conf_yn == "2") { sbQuerySingle.AppendLine("AND ifaconf = 'N'"); } } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @" SELECT ifa01,ifa02,ifa03,bec02 as ifa03_c , ifa04,beb03 AS ifa04_c,ifa05,ifaconf, ifb02,ifb03,ifb04,ifb05,'' as ifb05_str,ifb06, bej03,ifb07, ifb08,'' as ifb08_str,ifb09,ifb10, ica03 FROM ifa_tb LEFT JOIN ifb_tb ON ifa01=ifb01 LEFT JOIN bec_tb ON ifa03=bec01 LEFT JOIN beb_tb ON ifa04=beb01 LEFT JOIN bej_tb ON ifb06=bej01 LEFT JOIN ica_tb ON ifb03=ica01 WHERE ifa00='1' AND ifb01 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.ifb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ifb05); //數量 masterModel.ifb08_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ifb08); //數量 } pReport.RegData("master", resultList); pReport.CacheAllData = true; 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) { vw_invr102 invr102Model; StringBuilder sbSql = null; string sqlBody = ""; string sqlOrderBy = ""; DataTable dtMaster; List <Master> masterList; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; object icp03; string selectSql = ""; List <SqlParameter> sqlParmList; try { //if (Vw_Invr301 != null) //他窗引用時 // invr301Model = Vw_Invr301; //else // invr301Model = DrMaster.ToItem<vw_invr301>(); queryInfoList = new List <QueryInfo>(); #region range 處理 invr102Model = DrMaster.ToItem <vw_invr102>(); if (!GlobalFn.varIsNull(invr102Model.ica01)) { queryModel = new QueryInfo(); queryModel.TableName = "ica_tb"; queryModel.ColumnName = "ica01"; queryModel.ColumnType = TabMaster.DtSource.Columns["ica01"].DataType.Name; queryModel.Value = invr102Model.ica01; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion strWhere = strQueryRange; //var strSecurity = WfGetSecurityString(); //取得權限字串 //if (!GlobalFn.varIsNull(strSecurity)) // strWhere += strSecurity; //取得單頭 //sqlBody = @"SELECT V.ica01,v.ica02,v.ica11,v.icc05_tot sqlBody = @"SELECT V.* FROM ( SELECT a.*, ( SELECT SUM(icc05) as icc05_tot FROM icc_tb p WHERE p.icc01=a.ica01 ) AS icc05_tot FROM ica_tb a WHERE LEN(a.ica01)=11 AND a.icaconf='Y' AND ISNULL(a.ica30,'') <>'Y' ) V WHERE V.icc05_tot>0 order by 2,1 "; 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) { selectSql = @" SELECT TOP 1 icp03 FROM icp_tb WHERE icp01=@icp01 ORDER BY ISNULL(icp06,'N') DESC, icp05 "; sqlParmList = new List <SqlParameter>(); sqlParmList.Add(new SqlParameter("@icp01", masterModel.ica01)); icp03 = BoInv.OfGetFieldValue(selectSql, sqlParmList.ToArray()); if (icp03 != null && icp03 != DBNull.Value) { masterModel.icp03 = Image.FromStream(new MemoryStream((byte[])icp03));; } masterModel.ica03 = masterModel.ica02; } pReport.RegData("Master", masterList); pReport.CacheAllData = true; ////處理跳頁 //StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; //if (invr101Model.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_glar301 glar301Model; 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_Galr301 != null) //他窗引用時 { glar301Model = Vw_Galr301; } else { glar301Model = DrMaster.ToItem <vw_glar301>(); } queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(glar301Model.gfa01)) { queryModel = new QueryInfo(); queryModel.TableName = "gfa_tb"; queryModel.ColumnName = "gfa01"; queryModel.ColumnType = TabMaster.DtSource.Columns["gfa01"].DataType.Name; queryModel.Value = glar301Model.gfa01; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (Vw_Galr301 == null) { if (!GlobalFn.varIsNull(glar301Model.gfa02_s)) { sbQuerySingle.AppendLine("AND gfa02>=@gfa02_s"); sqlParmList.Add(new SqlParameter("@gfa02_s", glar301Model.gfa02_s)); } if (!GlobalFn.varIsNull(glar301Model.gfa02_e)) { sbQuerySingle.AppendLine("AND gfa02<=@gfa02_e"); sqlParmList.Add(new SqlParameter("@gfa02_e", glar301Model.gfa02_e)); } //過帳否 if (glar301Model.gfapost == "1") { sbQuerySingle.AppendLine("AND gfapost='N'"); } else if (glar301Model.gfapost == "2") { sbQuerySingle.AppendLine("AND gfapost='Y'"); } //有效否 if (glar301Model.gfaconf == "1") { sbQuerySingle.AppendLine("AND gfaconf <>'X' "); } else if (glar301Model.gfaconf == "2") { sbQuerySingle.AppendLine("AND gfapost='X' "); } //列印否 if (glar301Model.gfaprno == 1) { sbQuerySingle.AppendLine("AND gfaprno =0"); } else if (glar301Model.gfapost == "2") { sbQuerySingle.AppendLine("AND gfaprno >0 "); } } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @"SELECT gfa01,gfa02,gfa03,gfa04,gfa05, gfa06,gfa07,gfa08,gfa09, gfaprno,gfaconf,gfacond,gfaconu, gfapost,gfaposd,gfaposu, gfacreu,gfacred, gac02 as gfa01_c, gfb02,gfb03,gfb04,gfb05, gfb06,gfb07,gfb08,gfb09,gfb10, gba02 AS gfb03_c, beb03 AS gfb05_c, bek04, gba05 FROM gfa_tb LEFT JOIN gfb_tb ON gfa01=gfb01 LEFT JOIN baa_tb ON 1=1 LEFT JOIN gac_tb ON substring(gfa01,1,baa06)=gac01 LEFT JOIN gba_tb ON gfb03=gba01 LEFT JOIN bek_tb ON gfb08=bek01 LEFT JOIN beb_tb ON gfb05=beb01 WHERE 1=1 "; sqlOrderBy = " ORDER BY gfa01,gfa02"; 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); //取得本幣資料 var baaModel = BoBas.OfGetBaaModel(); if (baaModel == null) { WfShowErrorMsg("未設定基本參數,請先設定!"); return(false); } var baa04 = baaModel.baa04; var bekUsModel = BoBas.OfGetBekModel(baa04); if (bekUsModel == null) { WfShowErrorMsg("查無本幣資料,請先設定!"); return(false); } masterList = dtMaster.ToList <Master>(true); foreach (Master masterModel in masterList) { masterModel.gfa03_str = string.Format("{0:N" + bekUsModel.bek04 + "}", masterModel.gfa03); masterModel.gfa04_str = string.Format("{0:N" + bekUsModel.bek04 + "}", masterModel.gfa04); masterModel.gfb07_str = string.Format("{0:N" + bekUsModel.bek04 + "}", masterModel.gfb07); masterModel.gfb10_str = string.Format("{0:N" + masterModel.bek04 + "}", masterModel.gfb10); } pReport.RegData("Master", masterList); pReport.CacheAllData = true; ////處理跳頁 //StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; //if (glar300Model.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_glar321 glar321Model; string sqlBody = ""; string sqlOrderBy = ""; DataTable dtMaster; List <Master> masterList; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; string sqlDistinct = "";//取得會科 DataTable dtDistinct = null; try { glar321Model = DrMaster.ToItem <vw_glar321>(); queryInfoList = new List <QueryInfo>(); sqlParmList = new List <SqlParameter>(); #region range 處理 if (!GlobalFn.varIsNull(glar321Model.gfb03)) { queryModel = new QueryInfo(); queryModel.TableName = "gba_tb"; queryModel.ColumnName = "gba01"; queryModel.ColumnType = TabMaster.DtSource.Columns["gba01"].DataType.Name; queryModel.Value = glar321Model.gfb03; queryInfoList.Add(queryModel); } strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion //strWhere = strQueryRange + " " + sbQuerySingle.ToString(); //var strSecurity = WfGetSecurityString(); //取得權限字串 //if (!GlobalFn.varIsNull(strSecurity)) // strWhere += strSecurity; //無異動科目列印時,以會計科目來處理 if (glar321Model.c1 == "Y") { sqlDistinct = @"SELECT gba01 FROM gba_tb WHERE gba06 in ('1','3') "; strWhere = string.Concat(sbQuerySingle.ToString()); } else //以期間內的傳票資料做為處理來源 { sqlDistinct = @"SELECT gba01 FROM gfa_tb INNER JOIN gfb_tb ON gfa01=gfb01 INNER JOIN gba_tb on gfb03=gba01 WHERE gba06 in ('1','3') "; #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(glar321Model.gfa02_s)) { sbQuerySingle.AppendLine("AND gfa02>=@gfa02_s"); sqlParmList.Add(new SqlParameter("@gfa02_s", glar321Model.gfa02_s)); } if (!GlobalFn.varIsNull(glar321Model.gfa02_e)) { sbQuerySingle.AppendLine("AND gfa02<=@gfa02_e"); sqlParmList.Add(new SqlParameter("@gfa02_e", glar321Model.gfa02_e)); } #endregion strWhere = string.Concat(sbQuerySingle.ToString(), strQueryRange); } sqlOrderBy = " ORDER BY gba01"; dtDistinct = BoMaster.OfGetDataTable(string.Concat(sqlDistinct, strWhere, sqlOrderBy), sqlParmList.ToArray()); if (dtDistinct == null || dtDistinct.Rows.Count == 0) { WfShowErrorMsg("查無資料!"); return(false); } foreach (DataRow drDistinct in dtDistinct.Rows) { var gba01 = drDistinct[0].ToString(); } //處理排序 //switch (glar321Model.order_by) //{ // case "1"://1.依借出日期 // sqlOrderBy = " ORDER BY ila02"; // break; // case "2"://2.依客戶 // sqlOrderBy = " ORDER BY ila03"; // break; //} //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_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_purr200 purr200Model; StringBuilder sbSql = null; string sqlBody = ""; DataTable dtSeaTb, dtSebTb; List <YR.ERP.DAL.YRModel.Reports.Pur.Purr200.Detail> detailList; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; try { if (Vw_Purr200 != null) //他窗引用時 { purr200Model = Vw_Purr200; } else { purr200Model = DrMaster.ToItem <vw_purr200>(); } queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(purr200Model.pea01)) { queryModel = new QueryInfo(); queryModel.TableName = "pea_tb"; queryModel.ColumnName = "pea01"; queryModel.ColumnType = TabMaster.DtSource.Columns["pea01"].DataType.Name; queryModel.Value = purr200Model.pea01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(purr200Model.pea03)) { queryModel = new QueryInfo(); queryModel.TableName = "pea_tb"; queryModel.ColumnName = "pea03"; queryModel.ColumnType = TabMaster.DtSource.Columns["pea03"].DataType.Name; queryModel.Value = purr200Model.pea03; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(purr200Model.pea02_s)) { sbQuerySingle.AppendLine("AND pea02>=@pea02_s"); sqlParmList.Add(new SqlParameter("@pea02_s", purr200Model.pea02_s)); } if (!GlobalFn.varIsNull(purr200Model.pea02_e)) { sbQuerySingle.AppendLine("AND pea02<=@pea02_e"); sqlParmList.Add(new SqlParameter("@pea02_e", purr200Model.pea02_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @"SELECT pea01,pea02,pea03,pea04,pea05, pea06,pea07,pea08,pea10, pea11,pea12,peacomp, pca02 as pea03_c, bec02 as pea04_c, beb02 as pea05_c, bef03 as pea11_c, pbb02 as pea12_c, bab02 as pea01_c, bea03,bea04,bea05 FROM pea_tb LEFT JOIN pca_tb ON pea03=pca01 --廠商 LEFT JOIN bec_tb ON pea04=bec01 --員工 LEFT JOIN beb_tb ON pea05=beb01 --部門 LEFT JOIN bef_tb ON pea11=bef02 AND bef01='1' --收付款條件 LEFT JOIN pbb_tb ON pea12=pbb01 --採購取價原則 LEFT JOIN baa_tb ON 1=1 LEFT JOIN bab_tb ON substring(pea01,1,baa06)=bab01 LEFT JOIN bea_tb ON peacomp=beacomp WHERE 1=1 AND peaconf='Y' "; dtSeaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray()); dtSeaTb.TableName = "Master"; if (dtSeaTb == null || dtSeaTb.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } //取得明細 sbSql = new StringBuilder(); sbSql.AppendLine("SELECT "); sbSql.AppendLine(" peb01, "); sbSql.AppendLine(" peb02,peb03,peb04,peb05,peb06,"); sbSql.AppendLine(" ica03,bej03,"); sbSql.AppendLine(" '' AS peb05_str"); sbSql.AppendLine("FROM peb_tb"); sbSql.AppendLine(" LEFT JOIN ica_tb ON peb03=ica01"); sbSql.AppendLine(" LEFT JOIN bej_tb ON peb06=bej01"); sbSql.AppendLine("WHERE EXISTS("); sbSql.AppendLine(" SELECT 1 FROM pea_tb"); sbSql.AppendLine(" WHERE pea01=peb01"); sbSql.AppendLine(strWhere); sbSql.AppendLine(")"); dtSebTb = BoMaster.OfGetDataTable(sbSql.ToString(), sqlParmList.ToArray()); dtSebTb.TableName = "Detail"; detailList = dtSebTb.ToList <Detail>(); foreach (Detail detailModel in detailList) { detailModel.peb05_str = string.Format("{0:N" + detailModel.bej03 + "}", detailModel.peb05);//數量 } pReport.RegData(dtSeaTb); pReport.RegData("Detail", detailList); pReport.CacheAllData = true; ////處理排序 //StiDataBand stiDataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"]; //switch (purr200Model.order_by) //{ // case "1"://1.依報價日期 // stiDataBand1.Sort = new string[] { "ASC", "pea02" }; // break; // case "2"://2.依客戶 // stiDataBand1.Sort = new string[] { "ASC", "pea03" }; // break; //} ////處理跳頁 //StiFooterBand footerBand1 = (StiFooterBand)pReport.GetComponents()["FooterBand1"]; //if (purr200Model.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) { vw_invr101 invr101Model; 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_Invr301 != null) //他窗引用時 // invr301Model = Vw_Invr301; //else // invr301Model = DrMaster.ToItem<vw_invr301>(); queryInfoList = new List <QueryInfo>(); #region range 處理 invr101Model = DrMaster.ToItem <vw_invr101>(); if (!GlobalFn.varIsNull(invr101Model.ica01)) { queryModel = new QueryInfo(); queryModel.TableName = "ica_tb"; queryModel.ColumnName = "ica01"; queryModel.ColumnType = TabMaster.DtSource.Columns["ica01"].DataType.Name; queryModel.Value = invr101Model.ica01; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion strWhere = strQueryRange; //var strSecurity = WfGetSecurityString(); //取得權限字串 //if (!GlobalFn.varIsNull(strSecurity)) // strWhere += strSecurity; //取得單頭 sqlBody = @"SELECT ica_tb.* FROM ica_tb WHERE 1=1 "; 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 (invr101Model.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 WfExecute() { vw_invb500 invb500Model; List <QueryInfo> queryInfoList; QueryInfo queryModel; List <SqlParameter> sqlParmList; string strQueryRange, strWhere, strOrderBy; DataTable dtMain, dtIpaInsert, dtIpbInsert; DataRow drIpa, drIpb; StringBuilder sbSql, sbInsert; int chkCnts = 0; string ipa01New = "", errMsg = ""; try { //取得交易物件 BoMaster.TRAN = BoMaster.OfGetConntion().BeginTransaction(IsolationLevel.ReadUncommitted); BoInv.TRAN = BoMaster.TRAN; BoBas.TRAN = BoMaster.TRAN; invb500Model = DrMaster.ToItem <vw_invb500>(); #region range 查詢條件 queryInfoList = new List <QueryInfo>(); if (!GlobalFn.varIsNull(invb500Model.icc01)) { queryModel = new QueryInfo(); queryModel.TableName = "icc_tb"; queryModel.ColumnName = "icc01"; queryModel.ColumnType = TabMaster.DtSource.Columns["icc01"].DataType.Name; queryModel.Value = invb500Model.icc01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invb500Model.icc02)) { queryModel = new QueryInfo(); queryModel.TableName = "icc_tb"; queryModel.ColumnName = "icc02"; queryModel.ColumnType = TabMaster.DtSource.Columns["icc02"].DataType.Name; queryModel.Value = invb500Model.icc02; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invb500Model.icc03)) { queryModel = new QueryInfo(); queryModel.TableName = "icc_tb"; queryModel.ColumnName = "icc03"; queryModel.ColumnType = TabMaster.DtSource.Columns["icc03"].DataType.Name; queryModel.Value = invb500Model.icc02; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); strWhere = strQueryRange; #endregion var strSecurity = WfGetSecurityString(); //暫時無權限問題,先保留 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得資料 sbSql = new StringBuilder(); sbSql.AppendLine("SELECT * FROM icc_tb"); sbSql.AppendLine("WHERE 1=1"); #region 加入排序 strOrderBy = ""; switch (invb500Model.order_by_1) { case "1": strOrderBy = " ORDER BY icc03,"; break; case "2": strOrderBy = " ORDER BY icc01,"; break; case "3": strOrderBy = " ORDER BY icc02,"; break; } switch (invb500Model.order_by_2) { case "1": strOrderBy += "icc03,"; break; case "2": strOrderBy += "icc01,"; break; case "3": strOrderBy += "icc02,"; break; } switch (invb500Model.order_by_3) { case "1": strOrderBy += "icc03"; break; case "2": strOrderBy += "icc01"; break; case "3": strOrderBy += "icc02"; break; } #endregion dtMain = BoMaster.OfGetDataTable(string.Concat(sbSql.ToString(), strWhere, strOrderBy), sqlParmList.ToArray()); dtMain.TableName = "Master"; if (dtMain == null || dtMain.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); BoMaster.TRAN.Rollback(); return(false); } #region 寫入盤點清冊 //新增單頭 if (BoBas.OfGetAutoNo(invb500Model.ipa01, ModuleType.stp, (DateTime)invb500Model.ipa02, out ipa01New, out errMsg) == false) { WfShowErrorMsg(errMsg); return(false); } BoInv.OfCreateDao("ipa_tb", "*", ""); sbInsert = new StringBuilder(); sbInsert.AppendLine("SELECT * FROM ipa_tb"); sbInsert.AppendLine("WHERE 1<>1"); dtIpaInsert = BoInv.OfGetDataTable(sbInsert.ToString()); drIpa = dtIpaInsert.NewRow(); drIpa["ipa01"] = ipa01New; drIpa["ipa02"] = invb500Model.ipa02; drIpa["ipa03"] = invb500Model.ipa03; drIpa["ipa04"] = invb500Model.ipa04; drIpa["ipa05"] = "N"; drIpa["ipa06"] = DBNull.Value; drIpa["ipa07"] = "N"; drIpa["ipa08"] = DBNull.Value; drIpa["ipacomp"] = LoginInfo.CompNo; drIpa["ipasecu"] = LoginInfo.UserNo; drIpa["ipasecg"] = LoginInfo.GroupNo; drIpa["ipacreu"] = LoginInfo.UserNo; drIpa["ipacreg"] = LoginInfo.DeptNo; drIpa["ipacred"] = Now; drIpa["ipamodu"] = DBNull.Value; drIpa["ipamodg"] = DBNull.Value; drIpa["ipamodd"] = DBNull.Value; dtIpaInsert.Rows.Add(drIpa); if (BoInv.OfUpdate(dtIpaInsert) != 1) { errMsg = "新增盤點清冊(ipa_tb)失敗!"; BoMaster.TRAN.Rollback(); WfShowErrorMsg(errMsg); return(false); } BoInv.OfCreateDao("ipb_tb", "*", ""); sbInsert = new StringBuilder(); sbInsert.AppendLine("SELECT * FROM ipb_tb"); sbInsert.AppendLine("WHERE 1<>1"); dtIpbInsert = BoInv.OfGetDataTable(sbInsert.ToString()); var i = 0; foreach (DataRow drIcc in dtMain.Rows) { i++; drIpb = dtIpbInsert.NewRow(); var iccModel = drIcc.ToItem <icc_tb>(); drIpb["ipb01"] = ipa01New; drIpb["ipb02"] = i; drIpb["ipb03"] = iccModel.icc01; drIpb["ipb04"] = iccModel.icc02; drIpb["ipb05"] = iccModel.icc03; drIpb["ipb06"] = iccModel.icc05; drIpb["ipb07"] = iccModel.icc04; drIpb["ipb30"] = 0; drIpb["ipb31"] = DBNull.Value; drIpb["ipb32"] = DBNull.Value; drIpb["ipb40"] = 0; drIpb["ipb41"] = DBNull.Value; drIpb["ipb42"] = DBNull.Value; drIpb["ipb50"] = 0; drIpb["ipb51"] = DBNull.Value; drIpb["ipb52"] = DBNull.Value; drIpb["ipbcomp"] = LoginInfo.CompNo; drIpb["ipbcreu"] = LoginInfo.UserNo; drIpb["ipbcreg"] = LoginInfo.DeptNo; drIpb["ipbcred"] = Now; drIpb["ipbmodu"] = DBNull.Value; drIpb["ipbmodg"] = DBNull.Value; drIpb["ipbmodd"] = DBNull.Value; dtIpbInsert.Rows.Add(drIpb); } if (BoInv.OfUpdate(dtIpbInsert) <= 0) { errMsg = "新增盤點清冊(ipb_tb)失敗!"; BoMaster.TRAN.Rollback(); WfShowErrorMsg(errMsg); return(false); } #endregion BoMaster.TRAN.Commit(); #region 檢視產生資料 if (WfShowConfirmMsg("盤點清冊展開完成,是否要檢視盤點資料?") == DialogResult.Yes) { sbSql = new StringBuilder(); sbSql.AppendLine(string.Format(" AND ipa01='{0}'", ipa01New)); WfShowForm("invb501", false, new object[] { "admi600", this.LoginInfo, sbSql.ToString() }); } #endregion return(true); } catch (Exception ex) { BoMaster.TRAN.Rollback(); 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_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 WfExecute() { vw_invb600 invb600Model; sga_tb sgaModel; List <QueryInfo> queryInfoList; QueryInfo queryModel; List <SqlParameter> sqlParmList; string strQueryRange, strWhere; DataTable dtMain; StringBuilder sbSql; int chkCnts = 0; try { //取得交易物件 BoMaster.TRAN = BoMaster.OfGetConntion().BeginTransaction(); BoInv.TRAN = BoMaster.TRAN; invb600Model = DrMaster.ToItem <vw_invb600>(); #region range 查詢條件 queryInfoList = new List <QueryInfo>(); if (!GlobalFn.varIsNull(invb600Model.icc01)) { queryModel = new QueryInfo(); queryModel.TableName = "icc_tb"; queryModel.ColumnName = "icc01"; queryModel.ColumnType = TabMaster.DtSource.Columns["icc01"].DataType.Name; queryModel.Value = invb600Model.icc01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invb600Model.icc02)) { queryModel = new QueryInfo(); queryModel.TableName = "icc_tb"; queryModel.ColumnName = "icc02"; queryModel.ColumnType = TabMaster.DtSource.Columns["icc02"].DataType.Name; queryModel.Value = invb600Model.icc02; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); strWhere = strQueryRange; #endregion var strSecurity = WfGetSecurityString(); //暫時無權限問題,先保留 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sbSql = new StringBuilder(); sbSql.AppendLine("SELECT * FROM sga_tb"); sbSql.AppendLine("WHERE sgaconf='N' "); dtMain = BoMaster.OfGetDataTable(string.Concat(sbSql.ToString(), strWhere), sqlParmList.ToArray()); dtMain.TableName = "Master"; if (dtMain == null || dtMain.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); BoMaster.TRAN.Rollback(); return(false); } foreach (DataRow drIga in dtMain.Rows) { var iccModel = drIga.ToItem <sga_tb>(); //取得ina_tb庫存歷史檔資料! sbSql = new StringBuilder(); chkCnts = BoInv.OfExecuteNonquery(sbSql.ToString(), sqlParmList.ToArray()); //if (chkCnts<=0) //{ // WfShowErrorMsg("查無可異動icc_tb資料表,請檢核!"); // BoMaster.TRAN.Rollback(); // return false; //} } BoMaster.TRAN.Commit(); return(true); } catch (Exception ex) { BoMaster.TRAN.Rollback(); throw ex; } }
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) { //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_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_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) { vw_cspr110 cspr110Model; 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) //他窗引用時 // cspr110Model = Vw_Invr302; //else // cspr110Model = DrMaster.ToItem<vw_invr302>(); cspr110Model = DrMaster.ToItem <vw_cspr110>(); queryInfoList = new List <QueryInfo>(); #region range 處理 //if (!GlobalFn.varIsNull(cspr110Model.iga01)) //{ // queryModel = new QueryInfo(); // queryModel.TableName = "iga_tb"; // queryModel.ColumnName = "iga01"; // queryModel.ColumnType = TabMaster.DtSource.Columns["iga01"].DataType.Name; // queryModel.Value = cspr110Model.iga01; // queryInfoList.Add(queryModel); //} sqlParmList = new List <SqlParameter>(); //strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); sqlParmList = new List <SqlParameter>(); if (!GlobalFn.varIsNull(cspr110Model.jja16_s)) { //sbQuerySingle.AppendLine("AND jja16>=@jja16_s"); sqlParmList.Add(new SqlParameter("@jja16_s", cspr110Model.jja16_s)); } if (!GlobalFn.varIsNull(cspr110Model.jja16_e)) { //sbQuerySingle.AppendLine("AND jja16<=@jja16_e"); sqlParmList.Add(new SqlParameter("@jja16_e", cspr110Model.jja16_e)); } if (!GlobalFn.varIsNull(cspr110Model.icc05_s)) { //sbQuerySingle.AppendLine("AND icc05>=@icc05_s"); sqlParmList.Add(new SqlParameter("@icc05_s", cspr110Model.icc05_s)); } if (!GlobalFn.varIsNull(cspr110Model.icc05_e)) { //sbQuerySingle.AppendLine("AND icc05>=@icc05_e"); sqlParmList.Add(new SqlParameter("@icc05_e", cspr110Model.icc05_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @" select a.*,b.icc05 from ( select jja04,jja05 from jja_tb where jja23='Y' AND jja16>=@jja16_s AND jja16<=@jja16_e group by jja04,jja05 ) a left join icc_tb b on a.jja04=b.icc01 where icc05>=@icc05_s AND icc05<=@icc05_e "; 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 (cspr110Model.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 WfExecute() { vw_invb600 invb600Model; List <QueryInfo> queryInfoList; QueryInfo queryModel; List <SqlParameter> sqlParmList; string strQueryRange, strWhere; DataTable dtMain; StringBuilder sbSql; int chkCnts = 0; try { //取得交易物件 BoMaster.TRAN = BoMaster.OfGetConntion().BeginTransaction(); BoStp.TRAN = BoMaster.TRAN; invb600Model = DrMaster.ToItem <vw_invb600>(); #region range 查詢條件 queryInfoList = new List <QueryInfo>(); if (!GlobalFn.varIsNull(invb600Model.icc01)) { queryModel = new QueryInfo(); queryModel.TableName = "icc_tb"; queryModel.ColumnName = "icc01"; queryModel.ColumnType = TabMaster.DtSource.Columns["icc01"].DataType.Name; queryModel.Value = invb600Model.icc01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invb600Model.icc02)) { queryModel = new QueryInfo(); queryModel.TableName = "icc_tb"; queryModel.ColumnName = "icc02"; queryModel.ColumnType = TabMaster.DtSource.Columns["icc02"].DataType.Name; queryModel.Value = invb600Model.icc02; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); strWhere = strQueryRange; #endregion var strSecurity = WfGetSecurityString(); //暫時無權限問題,先保留 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sbSql = new StringBuilder(); sbSql.AppendLine("SELECT * FROM icc_tb"); sbSql.AppendLine("WHERE 1=1"); dtMain = BoMaster.OfGetDataTable(string.Concat(sbSql.ToString(), strWhere), sqlParmList.ToArray()); dtMain.TableName = "Master"; if (dtMain == null || dtMain.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); BoMaster.TRAN.Rollback(); return(false); } foreach (DataRow drIcc in dtMain.Rows) { var iccModel = drIcc.ToItem <vw_invb600>(); //取得ina_tb庫存歷史檔資料! sbSql = new StringBuilder(); sbSql.AppendLine(string.Format("SELECT SUM(CASE WHEN ina03='1' THEN ina10 ELSE ina10*-1 END)")); sbSql.AppendLine(string.Format("FROM ina_tb")); sbSql.AppendLine(string.Format("WHERE ina05=@ina05")); sbSql.AppendLine(string.Format("AND ina06=@ina06")); sqlParmList = new List <SqlParameter>(); sqlParmList.Add(new SqlParameter("@ina05", iccModel.icc01)); sqlParmList.Add(new SqlParameter("@ina06", iccModel.icc02)); var compueQty = GlobalFn.isNullRet(BoStp.OfGetFieldValue(sbSql.ToString(), sqlParmList.ToArray()), 0); sbSql = new StringBuilder(); sbSql.AppendLine(string.Format("UPDATE icc_tb")); sbSql.AppendLine(string.Format("SET icc05=@icc05")); sbSql.AppendLine(string.Format("WHERE icc01=@icc01")); sbSql.AppendLine(string.Format("AND icc02=@icc02")); sqlParmList = new List <SqlParameter>(); sqlParmList.Add(new SqlParameter("@icc01", iccModel.icc01)); sqlParmList.Add(new SqlParameter("@icc02", iccModel.icc02)); sqlParmList.Add(new SqlParameter("@icc05", compueQty)); chkCnts = BoStp.OfExecuteNonquery(sbSql.ToString(), sqlParmList.ToArray()); if (chkCnts <= 0) { WfShowErrorMsg("查無可異動icc_tb資料表,請檢核!"); BoMaster.TRAN.Rollback(); return(false); } } BoMaster.TRAN.Commit(); return(true); } catch (Exception ex) { BoMaster.TRAN.Rollback(); throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { //DataSet ds; vw_invr215 invr215Model; StringBuilder sbSql = null; string sqlBody = ""; DataTable dtMaster; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; List <Master> resultList = null; try { invr215Model = DrMaster.ToItem <vw_invr215>(); resultList = new List <Master>(); #region 處理類型 queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(invr215Model.icc01)) { queryModel = new QueryInfo(); queryModel.TableName = "icc_tb"; queryModel.ColumnName = "icc01"; queryModel.ColumnType = TabMaster.DtSource.Columns["icc01"].DataType.Name; queryModel.Value = invr215Model.icc01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(invr215Model.icc02)) { queryModel = new QueryInfo(); queryModel.TableName = "icc_tb"; queryModel.ColumnName = "icc02"; queryModel.ColumnType = TabMaster.DtSource.Columns["icc02"].DataType.Name; queryModel.Value = invr215Model.icc02; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion sbQuerySingle = new StringBuilder(); strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 if (invr215Model.show_warehouse_yn == "Y") { sqlBody = @" SELECT icc01,icc02,icc03,icc05, ica02,ica03,ica07, bej03,'' as icc05_str FROM icc_tb LEFT JOIN ica_tb ON icc01=ica01 LEFT JOIN bej_tb ON ica07=bej01 WHERE ica05>0 "; dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray()); } else { sqlBody = @" SELECT icc01,'' AS icc02,icc03,sum(icc05) as icc05, ica02,ica03,ica07, bej03,'' as icc05_str FROM icc_tb LEFT JOIN ica_tb ON icc01=ica01 LEFT JOIN bej_tb ON ica07=bej01 WHERE ica05>0 "; var sqlGroupBy = @" GROUP BY icc01,icc03, ica02,ica03,ica07, bej03"; dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlGroupBy), sqlParmList.ToArray()); } dtMaster.TableName = "Master"; if (dtMaster != null) { resultList.AddRange(dtMaster.ToList <Master>()); } #endregion if (resultList == null || resultList.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } foreach (Master masterModel in resultList) { //數量處理 masterModel.icc05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.icc05);//數量 } pReport.RegData("master", resultList); pReport.CacheAllData = true; pReport.Compile(); return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { vw_glar311 glar311Model; 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 { glar311Model = DrMaster.ToItem <vw_glar311>(); queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(glar311Model.gfa01)) { queryModel = new QueryInfo(); queryModel.TableName = "gfa_tb"; queryModel.ColumnName = "gfa01"; queryModel.ColumnType = TabMaster.DtSource.Columns["gfa01"].DataType.Name; queryModel.Value = glar311Model.gfa01; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(glar311Model.gfa02_s)) { sbQuerySingle.AppendLine("AND gfa02>=@gfa02_s"); sqlParmList.Add(new SqlParameter("@gfa02_s", glar311Model.gfa02_s)); } if (!GlobalFn.varIsNull(glar311Model.gfa02_e)) { sbQuerySingle.AppendLine("AND gfa02<=@gfa02_e"); sqlParmList.Add(new SqlParameter("@gfa02_e", glar311Model.gfa02_e)); } //過帳否 if (glar311Model.gfapost == "1") { sbQuerySingle.AppendLine("AND gfapost='N'"); } else if (glar311Model.gfapost == "2") { sbQuerySingle.AppendLine("AND gfapost='Y'"); } //有效否 if (glar311Model.gfaconf == "1") { sbQuerySingle.AppendLine("AND gfaconf <>'X' "); } else if (glar311Model.gfaconf == "2") { sbQuerySingle.AppendLine("AND gfapost='X' "); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @"SELECT gfa01,gfa02,gfa03,gfa04,gfa05, gfa06,gfa07,gfa08,gfa09, gfaprno,gfaconf,gfacond,gfaconu, gfapost,gfaposd,gfaposu, gfacreu,gfacred, gac02 as gfa01_c, gfb02,gfb03,gfb04,gfb05, gfb06,gfb07,gfb08,gfb09,gfb10, gba02 AS gfb03_c, beb03 AS gfb05_c, bek04, gba05 FROM gfa_tb LEFT JOIN gfb_tb ON gfa01=gfb01 LEFT JOIN baa_tb ON 1=1 LEFT JOIN gac_tb ON substring(gfa01,1,baa06)=gac01 LEFT JOIN gba_tb ON gfb03=gba01 LEFT JOIN bek_tb ON gfb08=bek01 LEFT JOIN beb_tb ON gfb05=beb01 WHERE 1=1 "; #region 排序處理 if (!GlobalFn.varIsNull(glar311Model.order1)) { switch (glar311Model.order1) { case "1": sqlOrderBy = "ORDER BY gfa01"; break; case "2": sqlOrderBy = "ORDER BY gfa02"; break; case "3": sqlOrderBy = "ORDER BY gfb03"; break; case "4": sqlOrderBy = "ORDER BY gfb05"; break; case "5": sqlOrderBy = "ORDER BY gfb02"; break; case "6": sqlOrderBy = "ORDER BY gfa06"; break; } } if (!GlobalFn.varIsNull(glar311Model.order2)) { if (GlobalFn.varIsNull(sqlOrderBy)) { switch (glar311Model.order2) { case "1": sqlOrderBy = "ORDER BY gfa01"; break; case "2": sqlOrderBy = "ORDER BY gfa02"; break; case "3": sqlOrderBy = "ORDER BY gfb03"; break; case "4": sqlOrderBy = "ORDER BY gfb05"; break; case "5": sqlOrderBy = "ORDER BY gfb02"; break; case "6": sqlOrderBy = "ORDER BY gfa06"; break; } } else { switch (glar311Model.order2) { case "1": sqlOrderBy += ",gfa01"; break; case "2": sqlOrderBy += ",gfa02"; break; case "3": sqlOrderBy += ",gfb03"; break; case "4": sqlOrderBy += ",gfb05"; break; case "5": sqlOrderBy += ",gfb02"; break; case "6": sqlOrderBy += ",gfa06"; break; } } } if (!GlobalFn.varIsNull(glar311Model.order3)) { if (GlobalFn.varIsNull(sqlOrderBy)) { switch (glar311Model.order3) { case "1": sqlOrderBy = "ORDER BY gfa01"; break; case "2": sqlOrderBy = "ORDER BY gfa02"; break; case "3": sqlOrderBy = "ORDER BY gfb03"; break; case "4": sqlOrderBy = "ORDER BY gfb05"; break; case "5": sqlOrderBy = "ORDER BY gfb02"; break; case "6": sqlOrderBy = "ORDER BY gfa06"; break; } } else { switch (glar311Model.order3) { case "1": sqlOrderBy += ",gfa01"; break; case "2": sqlOrderBy += ",gfa02"; break; case "3": sqlOrderBy += ",gfb03"; break; case "4": sqlOrderBy += ",gfb05"; break; case "5": sqlOrderBy += ",gfb02"; break; case "6": sqlOrderBy += ",gfa06"; break; } } } #endregion 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); //取得本幣資料 var baaModel = BoBas.OfGetBaaModel(); if (baaModel == null) { WfShowErrorMsg("未設定基本參數,請先設定!"); return(false); } var baa04 = baaModel.baa04; var bekUsModel = BoBas.OfGetBekModel(baa04); if (bekUsModel == null) { WfShowErrorMsg("查無本幣資料,請先設定!"); return(false); } masterList = dtMaster.ToList <Master>(true); foreach (Master masterModel in masterList) { if (masterModel.gfb06 == "1") { masterModel.gfb07d = masterModel.gfb07; masterModel.gfb07d_str = string.Format("{0:N" + bekUsModel.bek04 + "}", masterModel.gfb07); masterModel.gfb10d = masterModel.gfb10; masterModel.gfb10d_str = string.Format("{0:N" + masterModel.bek04 + "}", masterModel.gfb10); } else { masterModel.gfb07c = masterModel.gfb07; masterModel.gfb07c_str = string.Format("{0:N" + bekUsModel.bek04 + "}", masterModel.gfb07); masterModel.gfb10c = masterModel.gfb10; masterModel.gfb10c_str = string.Format("{0:N" + masterModel.bek04 + "}", masterModel.gfb10); } } pReport.RegData("Master", masterList); pReport.CacheAllData = true; #region 列印選項處理 StiGroupHeaderBand headerBand1 = (StiGroupHeaderBand)pReport.GetComponents()["GroupHeaderBand1"]; StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; StiGroupHeaderBand headerBand2 = (StiGroupHeaderBand)pReport.GetComponents()["GroupHeaderBand2"]; StiGroupFooterBand footerBand2 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand2"]; StiGroupHeaderBand headerBand3 = (StiGroupHeaderBand)pReport.GetComponents()["GroupHeaderBand3"]; StiGroupFooterBand footerBand3 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand3"]; if (!GlobalFn.varIsNull(glar311Model.order1) && (glar311Model.order1_jump_yn == "Y" || glar311Model.order1_sum_yn == "Y")) { switch (glar311Model.order1) { case "1": headerBand1.Condition.Value = "{Master.gfa01}"; break; case "2": headerBand1.Condition.Value = "{Master.gfa02}"; break; case "3": headerBand1.Condition.Value = "{Master.gfb03}"; break; case "4": headerBand1.Condition.Value = "{Master.gfb05}"; break; case "5": headerBand1.Condition.Value = "{Master.gfb02}"; break; case "6": headerBand1.Condition.Value = "{Master.gfa06}"; break; } //處理跳頁 if (glar311Model.order1_jump_yn == "Y") { headerBand1.NewPageBefore = true; headerBand1.ResetPageNumber = true; } else { headerBand1.NewPageBefore = false; headerBand1.ResetPageNumber = false; } //處理小計 if (glar311Model.order1_sum_yn.ToUpper() != "Y") { footerBand1.Enabled = false; } } else { footerBand1.Enabled = false; } if (!GlobalFn.varIsNull(glar311Model.order2) && (glar311Model.order2_jump_yn == "Y" || glar311Model.order2_sum_yn == "Y")) { switch (glar311Model.order2) { case "1": headerBand2.Condition.Value = "{Master.gfa01}"; break; case "2": headerBand2.Condition.Value = "{Master.gfa02}"; break; case "3": headerBand2.Condition.Value = "{Master.gfb03}"; break; case "4": headerBand2.Condition.Value = "{Master.gfb05}"; break; case "5": headerBand2.Condition.Value = "{Master.gfb02}"; break; case "6": headerBand2.Condition.Value = "{Master.gfa06}"; break; } //處理跳頁 if (glar311Model.order2_jump_yn.ToUpper() == "Y") { headerBand2.NewPageBefore = true; headerBand2.ResetPageNumber = true; } else { headerBand2.NewPageBefore = false; headerBand2.ResetPageNumber = false; } //處理小計 if (glar311Model.order2_sum_yn.ToUpper() != "Y") { footerBand2.Enabled = false; } } else { footerBand2.Enabled = false; } if (!GlobalFn.varIsNull(glar311Model.order3) && (glar311Model.order3_jump_yn == "Y" || glar311Model.order3_sum_yn == "Y")) { switch (glar311Model.order3) { case "1": headerBand3.Condition.Value = "{Master.gfa01}"; break; case "2": headerBand3.Condition.Value = "{Master.gfa02}"; break; case "3": headerBand3.Condition.Value = "{Master.gfb03}"; break; case "4": headerBand3.Condition.Value = "{Master.gfb05}"; break; case "5": headerBand3.Condition.Value = "{Master.gfb02}"; break; case "6": headerBand3.Condition.Value = "{Master.gfa06}"; break; } //處理跳頁 if (glar311Model.order3_jump_yn.ToUpper() == "Y") { headerBand3.NewPageBefore = true; headerBand3.ResetPageNumber = true; } else { headerBand3.NewPageBefore = false; headerBand3.ResetPageNumber = false; } //處理小計 if (glar311Model.order3_sum_yn.ToUpper() != "Y") { footerBand3.Enabled = false; } } else { footerBand3.Enabled = false; } #endregion return(true); } catch (Exception ex) { throw ex; } }