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 WfAppendUpdate() { icm_tb icmOrgModel; DataTable dtIcm; DataRow drIcm; CommonBLL boAppend; StringBuilder sbSql; try { icmOrgModel = DrMaster.ToItem <icm_tb>(); boAppend = new CommonBLL(BoMaster.OfGetConntion()); boAppend.TRAN = BoMaster.TRAN; boAppend.OfCreateDao("icm_tb", "*", ""); sbSql = new StringBuilder(); sbSql.AppendLine("SELECT * FROM icm_tb"); sbSql.AppendLine(string.Format("WHERE icm01='{0}'", icmOrgModel.icm02)); sbSql.AppendLine(string.Format("AND icm02='{0}'", icmOrgModel.icm01)); dtIcm = boAppend.OfGetDataTable(sbSql.ToString()); if (dtIcm.Rows.Count == 0)//新增 { drIcm = dtIcm.NewRow(); drIcm["icm01"] = icmOrgModel.icm02; drIcm["icm02"] = icmOrgModel.icm01; drIcm["icm03"] = icmOrgModel.icm04; drIcm["icm04"] = icmOrgModel.icm03; drIcm["icm05"] = ""; drIcm["icmvali"] = icmOrgModel.icmvali; drIcm["icmsecu"] = LoginInfo.UserNo; drIcm["icmsecg"] = LoginInfo.GroupNo; drIcm["icmcreu"] = LoginInfo.UserNo; drIcm["icmcreg"] = LoginInfo.DeptNo; drIcm["icmcred"] = Now; dtIcm.Rows.Add(drIcm); } else { drIcm = dtIcm.Rows[0]; drIcm["icm03"] = icmOrgModel.icm04; drIcm["icm04"] = icmOrgModel.icm03; drIcm["icmvali"] = icmOrgModel.icmvali; drIcm["icmmodu"] = LoginInfo.UserNo; drIcm["icmmodg"] = LoginInfo.DeptNo; drIcm["icmmodd"] = Now; } boAppend.OfUpdate(dtIcm); return(true); } catch (Exception ex) { throw ex; } }
//*****************************表單自訂Fuction**************************************** #region WfConfirm 確認 private void WfConfirm() { vw_stpi100 masterModel = null; try { if (DrMaster == null) { return; } if (WfRetrieveMaster() == false) { return; } if (WfLockMasterRow() == false) //這裡會LOCK資料 { return; } WfSetBllTransaction(); masterModel = DrMaster.ToItem <vw_stpi100>(); if (masterModel.scavali == "N") { WfShowErrorMsg("客戶已失效!"); WfRollback(); return; } if (GlobalFn.isNullRet(masterModel.scaconf, "N") != "N") { WfShowErrorMsg("客戶非未確認狀態!"); WfRollback(); return; } DrMaster["scaconf"] = "Y"; DrMaster["scavali"] = "Y"; DrMaster["scamodu"] = LoginInfo.UserNo; DrMaster["scamodg"] = LoginInfo.DeptNo; DrMaster["scamodd"] = Now; BoMaster.OfUpdate(DrMaster.Table); WfCommit(); DrMaster.AcceptChanges(); WfShowBottomStatusMsg("作業成功!"); masterModel = DrMaster.ToItem <vw_stpi100>(); WfSetDocPicture(masterModel.scavali, masterModel.scaconf, "", pbxDoc); } catch (Exception ex) { WfRollback(); DrMaster.RejectChanges(); throw ex; } }
protected override bool WfExecute() { vw_glab313 masterModel = null; int chkCnts = 0; StringBuilder sbResult = null; try { //取得交易物件 BoMaster.TRAN = BoMaster.OfGetConntion().BeginTransaction(); BoGlab313.TRAN = BoMaster.TRAN; masterModel = DrMaster.ToItem <vw_glab313>(); var securityString = WfGetSecurityString(); var resultList = BoGlab313.OfGlab313Post(masterModel, securityString, "", LoginInfo); if (resultList == null || resultList.Count == 0) { WfShowBottomStatusMsg("無可過帳還原資料!"); BoGlab313.TRAN.Rollback(); return(true); } chkCnts = resultList.Where(p => p.Success == false).Count(); if (chkCnts > 0) { BoGlab313.TRAN.Rollback(); sbResult = new StringBuilder(); sbResult.AppendLine(string.Format("執行失敗!")); sbResult.AppendLine(string.Format("過帳還原傳票筆數【{0}】 成功:【{1}】 失敗【{2}】", resultList.Count, resultList.Count - chkCnts, chkCnts )); sbResult.AppendLine(); sbResult.AppendLine(string.Format("錯誤內容如下")); sbResult.AppendLine("===================================="); foreach (Result result in resultList.Where(p => p.Success == false)) { sbResult.AppendLine(string.Format("key1:【{0}】 錯誤訊息:【{1}】", result.Key1, result.Message)); } WfShowErrorMsg(sbResult.ToString()); BoGlab313.TRAN.Rollback(); return(false); } BoMaster.TRAN.Commit(); return(true); } catch (Exception ex) { if (BoMaster.TRAN != null) { BoMaster.TRAN.Rollback(); } throw ex; } }
protected override void WfActionClick(string pActionName) { vw_admq910 masterModel; string killSql; try { switch (pActionName.ToLower()) { case "killsession": if (DrMaster == null) { return; } masterModel = DrMaster.ToItem <vw_admq910>(); if (masterModel.blkBy <= 0) { WfShowBottomStatusMsg("連線未被Lock不可刪除"); return; } if (WfShowConfirmMsg("請確認是否要刪除連線?") == DialogResult.Yes) { killSql = string.Concat("kill ", masterModel.blkBy, ";"); BoMaster.OfExecuteNonquery(killSql); WfShowBottomStatusMsg("連線刪除成功!"); if (WfQueryOk()) { WfDisplayMode(); WfAfterfDisplayMode(); } } break; case "refresh": if (FormEditMode != YREditType.NA) { return; } //新增一列,模擬查詢 TabMaster.DtSource.Rows.Add(TabMaster.DtSource.NewRow()); WfGetActiveDatarow(); if (WfQueryOk()) { WfDisplayMode(); WfAfterfDisplayMode(); } break; } } catch (Exception ex) { throw ex; } }
private bool WfReCalculate(string ipa01) { icc_tb iccModel; string selectSql; try { //lock master row 並取得transaction物件 if (WfLockMasterRow() == false) { WfShowErrorMsg("Lock ipa_file 失敗!"); return(false); } WfRetrieveMaster(); //更新主檔資料 WfRetrieveDetail(); //更新明細資料 WfSetBllTransaction(); //相關bll 註冊transaction TabDetailList[0].BoBasic.TRAN = BoMaster.TRAN; foreach (DataRow drDetail in TabDetailList[0].DtSource.Rows) { var ipbModel = drDetail.ToItem <ipb_tb>(); iccModel = BoInv.OfGetIccModel(ipbModel.ipb03, ipbModel.ipb04); drDetail["ipb06"] = iccModel.icc05; drDetail["ipbmodu"] = LoginInfo.UserNo; drDetail["ipbmodg"] = LoginInfo.DeptNo; drDetail["ipbmodd"] = Now; } DrMaster["ipa05"] = "Y"; DrMaster["ipa06"] = Today; DrMaster["ipamodu"] = LoginInfo.UserNo; DrMaster["ipamodg"] = LoginInfo.DeptNo; DrMaster["ipamodd"] = Now; BoMaster.OfUpdate(TabMaster.DtSource); TabDetailList[0].BoBasic.OfUpdate(TabDetailList[0].DtSource); if (WfCommit() == false) { DrMaster.RejectChanges(); TabDetailList[0].DtSource.RejectChanges(); WfRollback(); WfShowErrorMsg("執行commit失敗!"); return(false); } return(false); } catch (Exception ex) { DrMaster.RejectChanges(); TabDetailList[0].DtSource.RejectChanges(); WfRollback(); throw ex; } }
private bool WfCancelPosting(string ipa01) { icc_tb iccModel; string selectSql; try { //lock master row 並取得transaction物件 if (WfLockMasterRow() == false) { WfShowErrorMsg("Lock ipa_file 失敗!"); return(false); } WfSetBllTransaction(); //相關bll 註冊transaction TabDetailList[0].BoBasic.TRAN = BoMaster.TRAN; DrMaster["ipa07"] = "N"; DrMaster["ipa08"] = DBNull.Value; DrMaster["ipamodu"] = LoginInfo.UserNo; DrMaster["ipamodg"] = LoginInfo.DeptNo; DrMaster["ipamodd"] = Now; BoMaster.OfUpdate(TabMaster.DtSource); if (WfCommit() == false) { DrMaster.RejectChanges(); TabDetailList[0].DtSource.RejectChanges(); WfRollback(); WfShowErrorMsg("執行commit失敗!"); return(false); } return(false); } catch (Exception ex) { DrMaster.RejectChanges(); WfRollback(); throw ex; } }
protected override bool WfPreInInsertModeCheck() { int iChkCnts = 0; StringBuilder sbSql; try { sbSql = new StringBuilder(); sbSql.AppendLine("SELECT COUNT(1) FROM baa_tb"); iChkCnts = GlobalFn.isNullRet(BoMaster.OfGetFieldValue(sbSql.ToString()), 0); if (iChkCnts == 0) { WfShowErrorMsg("請先設定共用參數作業(basi001)!"); return(false); } return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecute() { vw_glab321 glab312Model = null; try { glab312Model = DrMaster.ToItem <vw_glab321>(); //取得交易物件 BoMaster.TRAN = BoMaster.OfGetConntion().BeginTransaction(); BoGlab321.TRAN = BoMaster.TRAN; var result = BoGlab321.OfGlab321Post(glab312Model, LoginInfo); if (result == null) { WfShowBottomStatusMsg("查無可年結資料!"); BoMaster.TRAN.Rollback(); return(true); } if (result.Success == false) { WfShowErrorMsg(result.Message); BoMaster.TRAN.Rollback(); return(false); } BoMaster.TRAN.Commit(); return(true); } catch (Exception ex) { if (BoMaster.TRAN != null) { BoMaster.TRAN.Rollback(); } 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_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 WfAppendUpdate() { DataTable dtAddTb; AdmBLL boAppend; StringBuilder sbSql; vw_admi600 masterModel = null; List <SqlParameter> sqlParmList = null; try { masterModel = DrMaster.ToItem <vw_admi600>(); boAppend = new AdmBLL(BoMaster.OfGetConntion()); boAppend.TRAN = BoMaster.TRAN; boAppend.OfCreateDao("add_tb", "*", ""); sbSql = new StringBuilder(); sbSql.AppendLine("SELECT * FROM add_tb"); sbSql.AppendLine("WHERE add01=@add01"); sbSql.AppendLine("AND add02=@add02"); sqlParmList = new List <SqlParameter>(); sqlParmList.Add(new SqlParameter("@add01", LoginInfo.UserRole)); sqlParmList.Add(new SqlParameter("@add02", masterModel.ado01)); dtAddTb = boAppend.OfGetDataTable(sbSql.ToString(), sqlParmList.ToArray()); if (masterModel.ado07.ToUpper() == "M")//menus 時 { if (dtAddTb != null && dtAddTb.Rows.Count > 0) { dtAddTb.Rows[0].Delete(); if (boAppend.OfUpdate(dtAddTb) < 1) { WfShowErrorMsg("刪除權限資料檔(add_tb)失敗!"); return(false); } } } else //報表或程式時 { if (dtAddTb.Rows.Count == 0 && GlobalFn.isNullRet(masterModel.ado16, "") != "Y") { var result = WfShowConfirmMsg("無此程式的權限,請問是否自動新增 ?"); //var i = WfShowConfirmMsg("無此程式的權限,請問是否自動新增"); //if (i == 1) if (result == DialogResult.Yes) { var drNew = dtAddTb.NewRow(); drNew["add01"] = LoginInfo.UserRole; drNew["add02"] = masterModel.ado01; drNew["add03"] = "Y"; drNew["add04"] = "Y"; drNew["add05"] = "Y"; drNew["add06"] = "Y"; drNew["add07"] = "Y"; drNew["add08"] = "Y"; drNew["add09"] = "Y"; drNew["add10"] = "Y"; drNew["add11"] = "Y"; drNew["add12"] = "Y"; dtAddTb.Rows.Add(drNew); if (boAppend.OfUpdate(dtAddTb) < 1) { WfShowErrorMsg("刪除權限資料檔(add_tb)失敗!"); return(false); } } } } 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 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 void WfActionClick(string pActionName) { vw_admi600 masterModel; StringBuilder sbSql; YR.ERP.Base.Forms.FrmBase frmActive = null; try { switch (pActionName.ToLower()) { case "admi601": if (FormEditMode != YREditType.NA) { return; } if (DrMaster == null) { return; } masterModel = DrMaster.ToItem <vw_admi600>(); sbSql = new StringBuilder(); sbSql.AppendLine(string.Format(" AND ado01='{0}'", masterModel.ado01)); WfShowForm("admi601", false, new object[] { "admi600", this.LoginInfo, sbSql.ToString() }); break; case "admi602": if (FormEditMode != YREditType.NA) { return; } if (DrMaster == null) { return; } masterModel = DrMaster.ToItem <vw_admi600>(); sbSql = new StringBuilder(); sbSql.AppendLine(string.Format(" AND ado01='{0}'", masterModel.ado01)); WfShowForm("admi602", false, new object[] { "admi600", this.LoginInfo, sbSql.ToString() }); break; #region genaction case "genaction": if (DrMaster == null) { return; } if (FormEditMode != YREditType.NA) { return; } masterModel = DrMaster.ToItem <vw_admi600>(); var adoModle = BoAdm.OfGetAdoModel(masterModel.ado01); if (AdoModel == null) { WfShowErrorMsg("無此程式代號"); return; } //先檢查是否有transction if (BoMaster.TRAN != null) { WfCommit(); } if (TabMaster.CanUseRowLock == true) { WfLockMasterRow(); //Lock row 並且產生transaction } else { WfBeginTran(); } try { var assembly = System.Reflection.Assembly.LoadFile(Path.Combine(Application.StartupPath, adoModle.ado03)); var type = assembly.GetType(adoModle.ado04); frmActive = Activator.CreateInstance(type) as YR.ERP.Base.Forms.FrmBase; frmActive.LoginInfo = this.LoginInfo; frmActive.WindowState = FormWindowState.Minimized; frmActive.Show(); Dictionary <string, string> dic = new Dictionary <string, string>(); var actionDic = frmActive.ActionDic; var reportDic = frmActive.ReportDic; if (actionDic != null) { foreach (KeyValuePair <string, string> keyValue in actionDic) { dic.Add(keyValue.Key, keyValue.Value); } } if (reportDic != null) { foreach (KeyValuePair <string, string> keyValue in reportDic) { dic.Add(keyValue.Key, keyValue.Value); } } StringBuilder sb = new StringBuilder(); if (dic != null) { var i = 0; foreach (KeyValuePair <string, string> keyValue in dic) { i++; sb.Append(keyValue.Key); if (i < dic.Count) { sb.Append(","); } } DrMaster["ado15"] = sb.ToString(); //WfShowMsg(sb.ToString()); } frmActive.Close(); BoMaster.OfUpdate(DrMaster.Table); WfCommit(); DrMaster.Table.AcceptChanges(); } catch (Exception ex) { WfRollback(); DrMaster.Table.RejectChanges(); throw ex; } finally { if (frmActive != null) { frmActive.Close(); } } break; #endregion } } 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) { 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) { 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_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 WfQueryOk() { DataTable dtSource = null; string strQueryAll; string strQueryApend = ""; List <SqlParameter> sqlParmList; string strQueryExternal = ""; try { uGridMaster.PerformAction(UltraGridAction.ExitEditMode); this.TabMaster.DtSource.EndInit(); sqlParmList = new List <SqlParameter>(); this.StrQueryWhere = BoMaster.WfCombineQuerystring(TabMaster, out sqlParmList); StrQueryWhere = GlobalFn.isNullRet(StrQueryWhere, ""); StrQueryWhereAppend = GlobalFn.isNullRet(StrQueryWhereAppend, ""); //額外加入的查詢內容------開始 if (ucx_qryIsBloced.Checked) { strQueryExternal = " AND blkBy >0"; } //額外加入的查詢內容------ end //不下條件仍可以查詢,後續要再做處理 if (StrQueryWhereAppend.Trim().Length > 0) { if (StrQueryWhereAppend.ToLower().TrimStart().IndexOf("and ") < 0) { strQueryApend = " AND " + StrQueryWhereAppend.TrimStart(); } else { strQueryApend = StrQueryWhereAppend.TrimStart(); } } //額外加入的查詢內容------開始 strQueryAll = StrQueryWhere + strQueryApend + this.StrQuerySecurity + strQueryExternal; //額外加入的查詢內容------ end dtSource = this.TabMaster.BoBasic.OfSelect(strQueryAll, sqlParmList); this.ResumeLayout(true); this.FormEditMode = YREditType.NA; //這裡就要把行為改為 YREditType.因為會觸發 retrieve detail for multi form this.TabMaster.DtSource = dtSource; this.WfSetMasterDatasource(this.TabMaster.DtSource); if (this.uGridMaster.Rows.Count > 0) { this.uGridMaster.PerformAction(UltraGridAction.FirstRowInGrid); uGridMaster.ActiveRow = uGridMaster.DisplayLayout.Rows.GetRowAtVisibleIndex(0); //因為這裡不會將第一列設定activeRow 所以人工處理 DrMaster = WfGetActiveDatarow(); } } catch (Exception ex) { throw ex; } finally { this.FormEditMode = YREditType.NA; WfShowRibbonGroup(FormEditMode, TabMaster.IsCheckSecurity, TabMaster.AddTbModel); this.IsChanged = false; } return(true); }
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 WfAppendUpdate() { vw_admi611 admi611Model = null; int chkCnts = 0; StringBuilder sbSql = null; List <SqlParameter> sqlParmsList; CommonBLL boAppend; DataTable dtady = null; try { boAppend = new InvBLL(BoMaster.OfGetConntion()); boAppend.TRAN = BoMaster.TRAN; boAppend.OfCreateDao("ady_tb", "*", ""); admi611Model = DrMaster.ToItem <vw_admi611>(); //處理明細--均先刪後新增 if (FormEditMode != YREditType.新增) { sbSql = new StringBuilder(); sqlParmsList = new List <SqlParameter>(); sbSql.AppendLine("DELETE FROM ady_tb WHERE ady01=@ady01"); sqlParmsList.Add(new SqlParameter("@ady01", admi611Model.adx01)); chkCnts = boAppend.OfExecuteNonquery(sbSql.ToString(), sqlParmsList.ToArray()); sbSql = new StringBuilder(); sqlParmsList = new List <SqlParameter>(); sbSql.AppendLine("SELECT * FROM ady_tb"); sbSql.AppendLine("WHERE 1<>1"); dtady = boAppend.OfGetDataTable(sbSql.ToString()); foreach (vw_admi611s detailModel in Admi611sList) { var drady = dtady.NewRow(); drady["ady01"] = detailModel.ady01; drady["ady02"] = detailModel.ady02; drady["ady03"] = detailModel.ady03; drady["ady04"] = detailModel.ady04; drady["ady05"] = detailModel.ady05; drady["ady06"] = detailModel.ady06; drady["ady07"] = detailModel.ady07; drady["adycreu"] = detailModel.adycreu; drady["adycreg"] = detailModel.adycreg; if (detailModel.adycred == null) { drady["adycred"] = DBNull.Value; } else { drady["adycred"] = detailModel.adycred; } drady["adymodu"] = detailModel.adymodu; drady["adymodg"] = detailModel.adymodg; if (detailModel.adymodd == null) { drady["adymodd"] = DBNull.Value; } else { drady["adymodd"] = detailModel.adymodd; } dtady.Rows.Add(drady); } boAppend.OfUpdate(dtady); } 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_manr311 manr311Model; StringBuilder sbSql = null; string sqlBody = ""; DataTable dtMaster; List <YR.ERP.DAL.YRModel.Reports.Man.Manr311.Master> masterList; List <QueryInfo> queryInfoList; QueryInfo queryModel; string strQueryRange, strWhere; StringBuilder sbQuerySingle = null; List <SqlParameter> sqlParmList; try { if (Vw_Manr311 != null) //他窗引用時 { manr311Model = Vw_Manr311; } else { manr311Model = DrMaster.ToItem <vw_manr311>(); } queryInfoList = new List <QueryInfo>(); #region range 處理 if (!GlobalFn.varIsNull(manr311Model.mfa01)) { queryModel = new QueryInfo(); queryModel.TableName = "mfa_tb"; queryModel.ColumnName = "mfa01"; queryModel.ColumnType = TabMaster.DtSource.Columns["mfa01"].DataType.Name; queryModel.Value = manr311Model.mfa01; queryInfoList.Add(queryModel); } if (!GlobalFn.varIsNull(manr311Model.mfa03)) { queryModel = new QueryInfo(); queryModel.TableName = "mfa_tb"; queryModel.ColumnName = "mfa03"; queryModel.ColumnType = TabMaster.DtSource.Columns["mfa03"].DataType.Name; queryModel.Value = manr311Model.mfa03; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); if (!GlobalFn.varIsNull(manr311Model.mfa06)) { queryModel = new QueryInfo(); queryModel.TableName = "mfa_tb"; queryModel.ColumnName = "mfa06"; queryModel.ColumnType = TabMaster.DtSource.Columns["mfa06"].DataType.Name; queryModel.Value = manr311Model.mfa06; queryInfoList.Add(queryModel); } sqlParmList = new List <SqlParameter>(); strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList); #endregion #region single 處理 sbQuerySingle = new StringBuilder(); if (!GlobalFn.varIsNull(manr311Model.mfa02_s)) { sbQuerySingle.AppendLine("AND mfa02>=@mfa02_s"); sqlParmList.Add(new SqlParameter("@mfa02_s", manr311Model.mfa02_s)); } if (!GlobalFn.varIsNull(manr311Model.mfa02_e)) { sbQuerySingle.AppendLine("AND mfa02<=@mfa02_e"); sqlParmList.Add(new SqlParameter("@mfa02_e", manr311Model.mfa02_e)); } #endregion strWhere = strQueryRange + " " + sbQuerySingle.ToString(); var strSecurity = WfGetSecurityString(); //取得權限字串 if (!GlobalFn.varIsNull(strSecurity)) { strWhere += strSecurity; } //取得單頭 sqlBody = @"SELECT mfa00,mfa01,mfa02,mfa03,mfa04,mfa05, mfa06,mfa07,mfa08, bab02 as mfa01_c, pca02 as mfa03_c, bec02 as mfa04_c,beb02 as mfa05_c, mfb02,mfb03,mfb04,mfb05,mfb06, mfb07,mfb09, ica.ica03 as ica03, bej.bej03 as bej03, '' as mfb05_str, pcb03,pcb04,pcb06,pcb07 FROM mfa_tb LEFT JOIN mfb_tb On mfa01=mfb01 LEFT JOIN baa_tb ON 1=1 LEFT JOIN bab_tb ON substring(mfa01,1,baa06)=bab01 LEFT JOIN pca_tb ON mfa03=pca01 --廠商 LEFT JOIN bec_tb ON mfa04=bec01 --員工 LEFT JOIN beb_tb ON mfa05=beb01 --部門 LEFT JOIN ica_tb ica ON mfb03=ica.ica01 LEFT JOIN bej_tb bej ON mfb06=bej.bej01 LEFT JOIN pcb_tb ON mfa03=pcb01 and mfa07=pcb02 WHERE 1=1 AND mfaconf='Y' AND mfa00='2' "; dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray()); dtMaster.TableName = "Master"; if (dtMaster == null || dtMaster.Rows.Count == 0) { WfShowErrorMsg("查無資料,請重新過濾條件!"); return(false); } masterList = dtMaster.ToList <Master>(); foreach (Master masterModel in masterList) { masterModel.mfb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.mfb05);//子件數量 } pReport.RegData(dtMaster); pReport.RegData("Master", masterList); pReport.CacheAllData = true; //處理排序 StiDataBand stiDataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"]; switch (manr311Model.order_by) { case "1": //1.依出庫日期 stiDataBand1.Sort = new string[] { "ASC", "mfa02" }; break; case "2": //2.依廠商 stiDataBand1.Sort = new string[] { "ASC", "mfa03" }; break; } //處理跳頁 StiGroupFooterBand grouperBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"]; if (manr311Model.jump_yn.ToUpper() == "Y") { grouperBand1.NewPageAfter = true; grouperBand1.ResetPageNumber = true; } else { grouperBand1.NewPageAfter = false; grouperBand1.ResetPageNumber = false; } pReport.Compile(); return(true); } catch (Exception ex) { throw ex; } }
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport) { 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 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; } }