public bool UpdateFixTcChildQtxBugData(string year, string ordr, double ordr_cost, double ordr_tot_amt, List <Tstkm00> paramters)
{
bool result = true;
using (var conn = SQLFunc.OpenConnection(this._connectString))
{
using (var trans = conn.BeginTransaction())
{
try
{
foreach (var paramter in paramters)
{
result = result && UpdateTstkm00(paramter, conn, trans);
}
result = result && UpdateSSorm00(year, ordr, ordr_cost, ordr_tot_amt, conn, trans);
trans.Commit();
}
catch (SqlException sex)
{
trans.Rollback();
}
}
}
return(result);
}
public IEnumerable <FixTcChildQtxBugModel> getBugsList()
{
#region SQL 語法
string sqlQuery = @"
SELECT
*
FROM (SELECT
(SELECT
MAX(tick_qtax)
FROM tstkm00
WHERE tick_year = ordr_year
AND tick_ordr = ordr_ordr
AND tick_etit IN ('CHR', 'CHS', 'CHD', 'INF', 'C')
AND ordr_prof = tick_prof)
AS x
,(SELECT
MAX(tick_qtax)
FROM tstkm00
WHERE tick_year = ordr_year
AND tick_ordr = ordr_ordr
AND tick_etit NOT IN ('CHR', 'CHS', 'CHD', 'INF', 'C')
AND ordr_prof = tick_prof)
AS y
,ordr_year,ordr_ordr, s02.or02_wtyear, s02.or02_wtordr,Wor00_year,Wor00_ordr,Wor00_wtyear,Wor00_wtordr,ordr_date
FROM ssorm00
JOIN ssorm02 s02 ON s02.or02_wtyear = ordr_year and s02.or02_wtordr = ordr_ordr
JOIN wtorm00 ON Wor00_year = s02.or02_year and Wor00_ordr = s02.or02_ordr
WHERE ordr_year = '2016'
AND ordr_tkorder = '1'
--AND ordr_cost = 0
AND ordr_date >= '2016-11-22'
AND ordr_acclock2 = 0
AND ordr_sts = 0
AND (SELECT
SUM(tick_cost * tick_erat)
FROM tstkm00
WHERE tick_year = ordr_year
AND tick_ordr = ordr_ordr
AND tick_etit IN ('CHR', 'CHS', 'CHD', 'INF', 'C')
AND ordr_prof = tick_prof)
> 0) AS data
WHERE x = y and x > 0
ORDER BY ordr_date
" ;
#endregion
DynamicParameters sqlParam = new DynamicParameters();
#region SQL 查詢
using (SqlConnection conn = SQLFunc.OpenConnection(_connectString))
{
return(conn.Query <FixTcChildQtxBugModel>(sqlQuery, sqlParam));
}
#endregion
}
public double getErat(Tstkm00 paramter)
{
#region SQL 語法
string sqlQuery = @"
select tick_erat from tstkm00 WHERE tick_tkno = @tick_tkno AND tick_tkseq = @tick_tkseq ;" ;
#endregion
DynamicParameters sqlParam = new DynamicParameters();
sqlParam.Add("@tick_tkno", paramter.tick_tkno);
sqlParam.Add("@tick_tkseq", paramter.tick_tkseq);
#region SQL 查詢
using (SqlConnection conn = SQLFunc.OpenConnection(_connectString))
{
return(conn.Query <double>(sqlQuery, sqlParam).FirstOrDefault());
}
#endregion
}