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);
}
}
}
