TEST2
2014/01/22
·
0
意見
private ListQueryDataFromDb(List list) { if (list.Count == 0) return list; var sql = @" SELECT F7110.RETAIL_CODE, MST_ST01.STORE_NAME, F7110.DC_CODE, F7101.DC_NAME, MST_ST01.CLOSE_DATE FROM F7110, F7101, ( SELECT * FROM MST_ST01 WHERE (MST_ST01.STORE_NO, MST_ST01.EFF_DATE_TO) IN (SELECT STORE_NO, MIN(EFF_DATE_TO) FROM MST_ST01 WHERE EFF_DATE_TO>=TRUNC(SYSDATE) GROUP BY STORE_NO) ) MST_ST01 WHERE F7110.DC_CODE=F7101.DC_CODE AND F7110.RETAIL_CODE=MST_ST01.STORE_NO AND F7110.CUST_CODE='100002' AND F7110.RETAIL_CODE IN ({0}) "; var allStores = list.GroupBy(p => p.StoreNo).Select(p => p.Key).ToList(); var sqlCount = allStores.Count() / 1000; var allSql = ""; for (var i = 0; i <= sqlCount; i++) { var stores = string.Format("'{0}'", string.Join("','", allStores.Skip(i * 1000).Take(1000))); allSql += (string.IsNullOrWhiteSpace(allSql) ? "" : "UNION ") + string.Format(sql, stores); } var dsStores = CommonDataAccessUtility.ExecuteDataSet(allSql); var dbData = new List (); foreach (DataRow row in dsStores.Tables[0].Rows) { dbData.Add(new DeliveryCheckEntity { StoreNo = row["RETAIL_CODE"].ToString().Trim(), SysStoreName = row["STORE_NAME"].ToString().Trim(), DcCode = row["DC_CODE"].ToString().Trim(), DcName = row["DC_NAME"].ToString().Trim(), CloseDate = Convert.ToDateTime(row["CLOSE_DATE"]) }); } foreach (var e in list) { var dbStore = dbData.FirstOrDefault(p => p.StoreNo == e.StoreNo); var sameStoreCount = list.Count(p => p.StoreNo == e.StoreNo); var msg = ""; if (dbStore != null) { //門市存在 e.SysStoreName = dbStore.SysStoreName; e.DcCode = dbStore.DcCode; e.DcName = dbStore.DcName; e.CloseDate = dbStore.CloseDate; if (e.StoreName != e.SysStoreName) msg = "店號/店名不符"; } else { //門市不存在 msg = "無此店號"; } e.SameStoreCount = sameStoreCount; if (sameStoreCount > 1) msg += (string.IsNullOrWhiteSpace(msg) ? "" : "，且") + "店號重複"; e.CheckResult = msg; } return list; }
