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_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)
{
//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_invr302 invr302Model;
StringBuilder sbSql = null;
string sqlBody = "";
string sqlOrderBy = "";
DataTable dtMaster;
List <Master> masterList;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
try
{
if (Vw_Invr302 != null) //他窗引用時
{
invr302Model = Vw_Invr302;
}
else
{
invr302Model = DrMaster.ToItem <vw_invr302>();
}
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(invr302Model.iga01))
{
queryModel = new QueryInfo();
queryModel.TableName = "iga_tb";
queryModel.ColumnName = "iga01";
queryModel.ColumnType = TabMaster.DtSource.Columns["iga01"].DataType.Name;
queryModel.Value = invr302Model.iga01;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(invr302Model.iga02_s))
{
sbQuerySingle.AppendLine("AND iga02>=@iga02_s");
sqlParmList.Add(new SqlParameter("@iga02_s", invr302Model.iga02_s));
}
if (!GlobalFn.varIsNull(invr302Model.iga02_e))
{
sbQuerySingle.AppendLine("AND iga02<=@iga02_e");
sqlParmList.Add(new SqlParameter("@iga02_e", invr302Model.iga02_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"SELECT iga_tb.*,
bec02 as iga03_c,
beb02 as iga04_c,
bab02 as iga01_c,
igb02,igb03,igb04,igb05,igb06,
igb09,igb10,
ica03
FROM iga_tb
LEFT JOIN igb_tb ON iga01=igb01
LEFT JOIN bec_tb ON iga03=bec01 --員工
LEFT JOIN beb_tb ON iga04=beb01 --部門
LEFT JOIN baa_tb ON 1=1
LEFT JOIN bab_tb ON substring(iga01,1,baa06)=bab01
LEFT JOIN ica_tb ON igb03=ica01
WHERE 1=1
AND igaconf='Y'
AND iga00='2'
";
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlOrderBy), sqlParmList.ToArray());
dtMaster.TableName = "Master";
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
masterList = dtMaster.ToList <Master>(true);
foreach (Master masterModel in masterList)
{
var bejModel = BoBas.OfGetBejModel(masterModel.igb06);
if (bejModel != null)
{
masterModel.igb05_str = string.Format("{0:N" + bejModel.bej03 + "}", masterModel.igb05);//數量
}
else
{
masterModel.igb05_str = masterModel.igb05.ToString();
}
}
pReport.RegData("Master", masterList);
pReport.CacheAllData = true;
////處理跳頁
StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
if (invr302Model.jump_yn.ToUpper() == "Y")
{
footerBand1.NewPageAfter = true;
footerBand1.ResetPageNumber = true;
}
else
{
footerBand1.NewPageAfter = false;
footerBand1.ResetPageNumber = false;
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
vw_invr401 invr401Model;
StringBuilder sbSql = null;
string sqlBody = "";
string sqlOrderBy = "";
DataTable dtMaster;
List <Master> masterList;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
try
{
if (Vw_Invr401 != null) //他窗引用時
{
invr401Model = Vw_Invr401;
}
else
{
invr401Model = DrMaster.ToItem <vw_invr401>();
}
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(invr401Model.ila01))
{
queryModel = new QueryInfo();
queryModel.TableName = "ila_tb";
queryModel.ColumnName = "ila01";
queryModel.ColumnType = TabMaster.DtSource.Columns["ila01"].DataType.Name;
queryModel.Value = invr401Model.ila01;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(invr401Model.ila03))
{
queryModel = new QueryInfo();
queryModel.TableName = "ila_tb";
queryModel.ColumnName = "ila03";
queryModel.ColumnType = TabMaster.DtSource.Columns["ila03"].DataType.Name;
queryModel.Value = invr401Model.ila03;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(invr401Model.ila02_s))
{
sbQuerySingle.AppendLine("AND ila02>=@ila02_s");
sqlParmList.Add(new SqlParameter("@ila02_s", invr401Model.ila02_s));
}
if (!GlobalFn.varIsNull(invr401Model.ila02_e))
{
sbQuerySingle.AppendLine("AND ila02<=@ila02_e");
sqlParmList.Add(new SqlParameter("@ila02_e", invr401Model.ila02_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"SELECT ila_tb.*,
sca02 as ila03_c,
bec02 as ila04_c,
beb02 as ila05_c,
bab02 as ila01_c,
bel02 as ila14_c,
sbg1.sbg02 as ila15_c,
sbg2.sbg02 as ila16_c,
ilb02,ilb03,ilb04,ilb05,ilb06,
ilb11,ilb13,
bej03
FROM ila_tb
LEFT JOIN ilb_tb ON ila01=ilb01
LEFT JOIN sca_tb ON ila03=sca01 --客戶
LEFT JOIN bec_tb ON ila04=bec01 --員工
LEFT JOIN beb_tb ON ila05=beb01 --部門
LEFT JOIN baa_tb ON 1=1
LEFT JOIN bab_tb ON substring(ila01,1,baa06)=bab01
LEFT JOIN ica_tb ON ilb03=ica01
LEFT JOIN bej_tb ON ilb03=bej01
LEFT JOIN bel_tb ON ila14=bel01 --貨運方式
LEFT JOIN sbg_tb sbg1 ON ila15=sbg1.sbg01 --運送起點
LEFT JOIN sbg_tb sbg2 ON ila16=sbg2.sbg01 --運送終點
WHERE 1=1
AND ilaconf='Y'
";
//處理排序
switch (invr401Model.order_by)
{
case "1": //1.依借出日期
sqlOrderBy = " ORDER BY ila02";
break;
case "2": //2.依客戶
sqlOrderBy = " ORDER BY ila03";
break;
}
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, sqlOrderBy), sqlParmList.ToArray());
dtMaster.TableName = "Master";
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
masterList = dtMaster.ToList <Master>(true);
foreach (Master masterModel in masterList)
{
masterModel.ilb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.ilb05);//數量
}
pReport.RegData("Master", masterList);
pReport.CacheAllData = true;
////處理跳頁
StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
if (invr401Model.jump_yn.ToUpper() == "Y")
{
footerBand1.NewPageAfter = true;
footerBand1.ResetPageNumber = true;
}
else
{
footerBand1.NewPageAfter = false;
footerBand1.ResetPageNumber = false;
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
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)
{
//DataSet ds;
vw_manr311 manr311Model;
StringBuilder sbSql = null;
string sqlBody = "";
DataTable dtMaster;
List <YR.ERP.DAL.YRModel.Reports.Man.Manr311.Master> masterList;
List <QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere;
StringBuilder sbQuerySingle = null;
List <SqlParameter> sqlParmList;
try
{
if (Vw_Manr311 != null) //他窗引用時
{
manr311Model = Vw_Manr311;
}
else
{
manr311Model = DrMaster.ToItem <vw_manr311>();
}
queryInfoList = new List <QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(manr311Model.mfa01))
{
queryModel = new QueryInfo();
queryModel.TableName = "mfa_tb";
queryModel.ColumnName = "mfa01";
queryModel.ColumnType = TabMaster.DtSource.Columns["mfa01"].DataType.Name;
queryModel.Value = manr311Model.mfa01;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(manr311Model.mfa03))
{
queryModel = new QueryInfo();
queryModel.TableName = "mfa_tb";
queryModel.ColumnName = "mfa03";
queryModel.ColumnType = TabMaster.DtSource.Columns["mfa03"].DataType.Name;
queryModel.Value = manr311Model.mfa03;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
if (!GlobalFn.varIsNull(manr311Model.mfa06))
{
queryModel = new QueryInfo();
queryModel.TableName = "mfa_tb";
queryModel.ColumnName = "mfa06";
queryModel.ColumnType = TabMaster.DtSource.Columns["mfa06"].DataType.Name;
queryModel.Value = manr311Model.mfa06;
queryInfoList.Add(queryModel);
}
sqlParmList = new List <SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(manr311Model.mfa02_s))
{
sbQuerySingle.AppendLine("AND mfa02>=@mfa02_s");
sqlParmList.Add(new SqlParameter("@mfa02_s", manr311Model.mfa02_s));
}
if (!GlobalFn.varIsNull(manr311Model.mfa02_e))
{
sbQuerySingle.AppendLine("AND mfa02<=@mfa02_e");
sqlParmList.Add(new SqlParameter("@mfa02_e", manr311Model.mfa02_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
{
strWhere += strSecurity;
}
//取得單頭
sqlBody = @"SELECT
mfa00,mfa01,mfa02,mfa03,mfa04,mfa05,
mfa06,mfa07,mfa08,
bab02 as mfa01_c,
pca02 as mfa03_c,
bec02 as mfa04_c,beb02 as mfa05_c,
mfb02,mfb03,mfb04,mfb05,mfb06,
mfb07,mfb09,
ica.ica03 as ica03,
bej.bej03 as bej03,
'' as mfb05_str,
pcb03,pcb04,pcb06,pcb07
FROM mfa_tb
LEFT JOIN mfb_tb On mfa01=mfb01
LEFT JOIN baa_tb ON 1=1
LEFT JOIN bab_tb ON substring(mfa01,1,baa06)=bab01
LEFT JOIN pca_tb ON mfa03=pca01 --廠商
LEFT JOIN bec_tb ON mfa04=bec01 --員工
LEFT JOIN beb_tb ON mfa05=beb01 --部門
LEFT JOIN ica_tb ica ON mfb03=ica.ica01
LEFT JOIN bej_tb bej ON mfb06=bej.bej01
LEFT JOIN pcb_tb ON mfa03=pcb01 and mfa07=pcb02
WHERE 1=1
AND mfaconf='Y'
AND mfa00='2'
";
dtMaster = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere), sqlParmList.ToArray());
dtMaster.TableName = "Master";
if (dtMaster == null || dtMaster.Rows.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return(false);
}
masterList = dtMaster.ToList <Master>();
foreach (Master masterModel in masterList)
{
masterModel.mfb05_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.mfb05);//子件數量
}
pReport.RegData(dtMaster);
pReport.RegData("Master", masterList);
pReport.CacheAllData = true;
//處理排序
StiDataBand stiDataBand1 = (StiDataBand)pReport.GetComponents()["DataBand1"];
switch (manr311Model.order_by)
{
case "1": //1.依出庫日期
stiDataBand1.Sort = new string[] { "ASC", "mfa02" };
break;
case "2": //2.依廠商
stiDataBand1.Sort = new string[] { "ASC", "mfa03" };
break;
}
//處理跳頁
StiGroupFooterBand grouperBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
if (manr311Model.jump_yn.ToUpper() == "Y")
{
grouperBand1.NewPageAfter = true;
grouperBand1.ResetPageNumber = true;
}
else
{
grouperBand1.NewPageAfter = false;
grouperBand1.ResetPageNumber = false;
}
pReport.Compile();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
//DataSet ds;
vw_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)
{
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_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_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)
{
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_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 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)
{
//DataSet ds;
vw_invr522 invr522Model;
string sqlBody = "";
DataTable dtIlaTb;
List<QueryInfo> queryInfoList;
QueryInfo queryModel;
string strQueryRange, strWhere, strOrderBy;
StringBuilder sbQuerySingle = null;
List<SqlParameter> sqlParmList;
List<Master> resultList = null;
try
{
invr522Model = DrMaster.ToItem<vw_invr522>();
resultList = new List<Master>();
queryInfoList = new List<QueryInfo>();
#region range 處理
if (!GlobalFn.varIsNull(invr522Model.ipa01))
{
queryModel = new QueryInfo();
queryModel.TableName = "ipa_tb";
queryModel.ColumnName = "ipa01";
queryModel.ColumnType = TabMaster.DtSource.Columns["ipa01"].DataType.Name;
queryModel.Value = invr522Model.ipa01;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(invr522Model.ipacreu))
{
queryModel = new QueryInfo();
queryModel.TableName = "ipa_tb";
queryModel.ColumnName = "ipacreu";
queryModel.ColumnType = TabMaster.DtSource.Columns["ipacreu"].DataType.Name;
queryModel.Value = invr522Model.ipacreu;
queryInfoList.Add(queryModel);
}
if (!GlobalFn.varIsNull(invr522Model.ipacreg))
{
queryModel = new QueryInfo();
queryModel.TableName = "ipa_tb";
queryModel.ColumnName = "ipacreg";
queryModel.ColumnType = TabMaster.DtSource.Columns["ipacreg"].DataType.Name;
queryModel.Value = invr522Model.ipacreg;
queryInfoList.Add(queryModel);
}
sqlParmList = new List<SqlParameter>();
strQueryRange = BoMaster.WfGetQueryString(queryInfoList, out sqlParmList);
#endregion
#region single 處理
sbQuerySingle = new StringBuilder();
if (!GlobalFn.varIsNull(invr522Model.ipa03_s))
{
sbQuerySingle.AppendLine("AND ipa03>=@ipa03_s");
sqlParmList.Add(new SqlParameter("@ila02_s", invr522Model.ipa03_s));
}
if (!GlobalFn.varIsNull(invr522Model.ipa03_e))
{
sbQuerySingle.AppendLine("AND ipa03<=@ipa03_e");
sqlParmList.Add(new SqlParameter("@ipa03_e", invr522Model.ipa03_e));
}
#endregion
strWhere = strQueryRange + " " + sbQuerySingle.ToString();
var strSecurity = WfGetSecurityString(); //取得權限字串
if (!GlobalFn.varIsNull(strSecurity))
strWhere += strSecurity;
//取得單頭
sqlBody = @"
SELECT
ipa01,ipb02,ipb03,ipb04,ipb05,
ipb06,ipb07,ipb30,ipb40,ipb50,
ica02,ica03,
bej03,
CONVERT(DECIMAL,0) AS qty1,
CONVERT(DECIMAL,0) AS qty2,
CONVERT(DECIMAL,0) AS dif_qty,
CONVERT(NVARCHAR(20),0) AS qty1_str,
CONVERT(NVARCHAR(20),0) AS qty2_str,
CONVERT(NVARCHAR(20),0) AS dif_qty_str
FROM ipa_tb
INNER JOIN ipb_tb ON ipa01=ipb01
LEFT JOIN ica_tb ON ipb03=ica01
LEFT JOIN bej_tb ON ipb07=bej01
WHERE
ipa05='Y'
";
strOrderBy = " ORDER BY ipa01,ipb02";
dtIlaTb = BoMaster.OfGetDataTable(string.Concat(sqlBody, strWhere, strOrderBy), sqlParmList.ToArray());
dtIlaTb.TableName = "Master";
if (dtIlaTb != null)
{
resultList.AddRange(dtIlaTb.ToList<Master>());
}
if (resultList == null || resultList.Count == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return false;
}
foreach (Master masterModel in resultList)
{
switch (invr522Model.type1)
{
case "1":
masterModel.qty1 = masterModel.ipb30;
break;
case "2":
masterModel.qty1 = masterModel.ipb40;
break;
case "3":
masterModel.qty1 = masterModel.ipb50;
break;
case "4":
masterModel.qty1 = masterModel.ipb06;
break;
}
switch (invr522Model.type2)
{
case "1":
masterModel.qty2 = masterModel.ipb30;
break;
case "2":
masterModel.qty2 = masterModel.ipb40;
break;
case "3":
masterModel.qty2 = masterModel.ipb50;
break;
case "4":
masterModel.qty2 = masterModel.ipb06;
break;
}
masterModel.dif_qty = masterModel.qty2 - masterModel.qty1;
masterModel.qty1_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.qty1);
masterModel.qty2_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.qty2);
masterModel.dif_qty_str = string.Format("{0:N" + masterModel.bej03 + "}", masterModel.dif_qty);
}
if (invr522Model.dif_yn == "Y")//只顯示差異
{
if (resultList.Where(x => x.dif_qty != 0).Count() == 0)
{
WfShowErrorMsg("查無資料,請重新過濾條件!");
return false;
}
pReport.RegData("master", resultList.Where(x => x.dif_qty != 0));
}
else
{
pReport.RegData("master", resultList);
}
pReport.CacheAllData = true;
StiGroupFooterBand footerBand1 = (StiGroupFooterBand)pReport.GetComponents()["GroupFooterBand1"];
footerBand1.NewPageAfter = true;
footerBand1.ResetPageNumber = true;
pReport.Compile();
return true;
}
catch (Exception ex)
{
throw ex;
}
}
protected override bool WfExecReport(Stimulsoft.Report.StiReport pReport)
{
//DataSet ds;
vw_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_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_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_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;
}
}
