private void SaveSalesDets(TSales sales, FormCollection formCollection) { TSalesDet det = null; string detNumber, gameId; decimal detValue = 0; IDictionary <string, MGame> dictGame = GetDictGame(); MGame game = null; var agentComms = (from agentComm in sales.AgentId.AgentComms select agentComm).ToList(); decimal?comm = null; for (int i = 0; i < 40; i++) { detNumber = formCollection[string.Format("txtSalesDetNumber_{0}", i)]; if (!string.IsNullOrEmpty(detNumber)) { detValue = decimal.Parse(formCollection[string.Format("txtSalesDetValue_{0}", i)].Replace(",", "")); gameId = formCollection[string.Format("gameId_{0}", i)]; if (!string.IsNullOrEmpty(gameId)) { if (gameId.Equals("D4.BB")) { dictGame.TryGetValue("D4", out game); } else if (gameId.Equals("D3.BB")) { dictGame.TryGetValue("D3", out game); } else { dictGame.TryGetValue(gameId, out game); } if (agentComms.Count > 0) { comm = (from agentComm in agentComms where agentComm.GameId == game select agentComm.CommValue).First(); } } //recursive and calculate for BB if (gameId.Equals("D4.BB")) { SaveDetsForBB(sales, detNumber, detValue, game, comm, 4); } else if (gameId.Equals("D3.BB")) { SaveDetsForBB(sales, detNumber, detValue, game, comm, 3); } else { SaveSalesDet(sales, detNumber, detValue, game, comm, null); } } } }
private void SaveSalesDet(TSales sales, string detNumber, decimal detValue, MGame gameId, decimal?comm, string desc) { TSalesDet det = new TSalesDet(sales); det.SetAssignedIdTo(Guid.NewGuid().ToString()); det.SalesDetNumber = detNumber; det.SalesDetValue = detValue; det.GameId = gameId; det.SalesDetComm = comm; det.SalesDetDesc = desc; det.CreatedDate = DateTime.Now; det.CreatedBy = User.Identity.Name; det.DataStatus = EnumDataStatus.New.ToString(); _tSalesDetRepository.Save(det); }
public void CalculatePrize(DateTime resultDate) { // StringBuilder sql = new StringBuilder(); // sql.AppendLine(@" // update TSalesDet det // set det.SalesDetStatus = :detStatus // , det.SalesDetPrize = g.GamePrize // from TSalesDet det, // TSales s, // TResult res, // TResultDet res_det, // MGame g // where s.SalesDate = res.ResultDate // and det.SalesId.Id = s.Id // and res.Id = res_det.ResultId.Id // and det.GameId.Id = res_det.GameId.Id // and g.Id = res_det.GameId.Id // and s.SalesDate = :resultDate "); // IQuery q = Session.CreateQuery(sql.ToString()); // q.SetString("detStatus", Enums.EnumSalesDetStatus.Win.ToString()); // q.SetDateTime("resultDate", resultDate); //q.ExecuteUpdate(); StringBuilder sql = new StringBuilder(); //EXECUTE [SP_CALCULATE_PRIZE] // @SalesDetStatus = :detStatus // ,@SalesDate = :resultDate //calculate use query, not SP cause sql compact not support SP //SQL CE not support update table from other table, use subquery in where sql.AppendLine(@" update T_SALES_DET set SALES_DET_STATUS = null , SALES_DET_PRIZE = null , DATA_STATUS = :DATA_STATUS , MODIFIED_BY = :MODIFIED_BY , MODIFIED_DATE = :MODIFIED_DATE where SALES_ID in ( select s.SALES_ID from T_SALES s where s.SALES_DATE = :resultDate ); "); //update det // set SALES_DET_STATUS = null // ,SALES_DET_PRIZE = null // from T_SALES_DET det, T_SALES s // where det.SALES_ID = s.SALES_ID // and s.SALES_DATE = :resultDate; //update det //set SALES_DET_STATUS = :detStatus // ,SALES_DET_PRIZE = prize.PRIZE_VALUE //from T_SALES_DET det, T_SALES s, T_RESULT res, T_RESULT_DET res_det, M_GAME g, M_GAME_PRIZE prize //where s.SALES_DATE = res.RESULT_DATE // and det.SALES_ID = s.SALES_ID // and res.RESULT_ID = res_det.RESULT_ID // and det.GAME_ID = res_det.GAME_ID // and g.GAME_ID = res_det.GAME_ID // and s.SALES_DATE = :resultDate // and res_det.RESULT_DET_NUMBER = det.SALES_DET_NUMBER // and prize.GAME_ID = g.GAME_ID // and res_det.RESULT_DET_ORDER_NO between prize.PRIZE_NO_START and prize.PRIZE_NO_END; ISQLQuery q = Session.CreateSQLQuery(sql.ToString()); //q.SetString("detStatus", Enums.EnumSalesDetStatus.Win.ToString()); q.SetDateTime("resultDate", resultDate); q.SetString("DATA_STATUS", Enums.EnumDataStatus.Updated.ToString()); q.SetString("MODIFIED_BY", ""); q.SetDateTime("MODIFIED_DATE", DateTime.Now); //set all detail sales to default q.ExecuteUpdate(); //search detail that win sql = new StringBuilder(); sql.AppendLine(@" select det.SALES_DET_ID , sum(prize.PRIZE_VALUE) as PRIZE_VALUE from T_SALES_DET det, T_SALES s, T_RESULT res, T_RESULT_DET res_det, M_GAME g, M_GAME_PRIZE prize where s.SALES_DATE = res.RESULT_DATE and det.SALES_ID = s.SALES_ID and res.RESULT_ID = res_det.RESULT_ID and det.GAME_ID = res_det.GAME_ID and g.GAME_ID = res_det.GAME_ID and s.SALES_DATE = :resultDate and res_det.RESULT_DET_NUMBER = det.SALES_DET_NUMBER and prize.GAME_ID = g.GAME_ID and res_det.RESULT_DET_ORDER_NO between prize.PRIZE_NO_START and prize.PRIZE_NO_END group by det.SALES_DET_ID; "); q = Session.CreateSQLQuery(sql.ToString()); q.SetDateTime("resultDate", resultDate); q.AddScalar("SALES_DET_ID", NHibernateUtil.String); q.AddScalar("PRIZE_VALUE", NHibernateUtil.Decimal); IList list = q.List(); object[] obj; string detailId; decimal? prize; TSalesDet det = null; for (int i = 0; i < list.Count; i++) { obj = (object[])list[i]; detailId = obj[0].ToString(); prize = Convert.ToDecimal(obj[1]); det = Session.Get(typeof(TSalesDet), detailId) as TSalesDet; if (det != null) { det.DataStatus = Enums.EnumDataStatus.Updated.ToString(); det.ModifiedBy = ""; det.ModifiedDate = DateTime.Now; det.SalesDetStatus = Enums.EnumSalesDetStatus.Win.ToString(); det.SalesDetPrize = prize; Session.Update(det); } } }