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