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 }