public static void LineSignPayPoint(int count, DateTime dts, DateTime dte, int debug)
{
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
dts = DateTime.Parse(dts.ToString("yyyy/MM/dd") + " 00:00:00");
dte = DateTime.Parse(dte.ToString("yyyy/MM/dd") + " 23:59:59");
if (1 == debug)
{
pipe.Send(string.Format("时间段{0}-{1}", dts, dte));
}
connection.Open();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
var command = connection.CreateCommand();
command.Parameters.Clear();
command.CommandText = "SELECT COUNT(1) AS RecordCount,u001 FROM wgs046(NOLOCK) WHERE sign002 >= @DTStart AND sign002 <= @DTEnd GROUP BY u001;";
command.Parameters.AddWithValue("@DTStart", dts);
command.Parameters.AddWithValue("@DTEnd", dte);
da.SelectCommand = command;
da.Fill(ds, "GroupSet");
if (1 == debug)
{
pipe.Send(string.Format("Record count : {0}", ds.Tables["GroupSet"].Rows.Count));
}
command.Parameters.Clear();
command.CommandText = "SELECT cfg003 FROM wgs027(NOLOCK) WHERE cfg001=@cfg001;";
command.Parameters.AddWithValue("@cfg001", "SYS_LINE_SIGN_POINT");
var linePoint = Convert.ToDecimal(command.ExecuteScalar());
foreach (DataRow row in ds.Tables["GroupSet"].Rows)
{
var recordCount = (int)row["RecordCount"];
var userID = (int)row["u001"];
if (1 == debug)
{
pipe.Send(string.Format("User:{0},Count:{1}", userID, row[0]));
}
if (recordCount >= count)
{
if (1 == debug)
{
pipe.Send(string.Format("时间段{0}-{1}，用户ID：{2}，次数：{3}", dts, dte, userID, recordCount));
continue;
}
command.Parameters.Clear();
command.CommandText = "UPDATE wgs014 SET uf004=uf004+@uf004 WHERE u001=@u001;";
command.Parameters.AddWithValue("@uf004", linePoint);
command.Parameters.AddWithValue("@u001", userID);
command.ExecuteNonQuery();
command.CommandText = "SELECT u002,u003 FROM wgs012(NOLOCK) WHERE u001=@u001;";
da.Fill(ds, "UserInfo");
DataRow userRow = ds.Tables["UserInfo"].Rows[0];
string userName = userRow["u002"].ToString().Trim();
string userNickName = userRow.IsNull("u003") ? string.Empty : userRow["u003"].ToString().Trim();
UST.WriteDataChangeLog(ref command, userID, userName, userNickName, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, linePoint, 0, 34, ExtDCFunction.GetRDList()[34].Name, string.Empty, "连续签到奖励积分", 0, DateTime.Now);
UST.SetDayReport(ref command, userID, userName, userNickName, 0, 0, 0, 0, linePoint, 0, 0, 0, 0, 0, 0, 0, 0, 0);
ExtDCFunction.SendMessage(ref command, userID, userName, userNickName, "连续签到奖励积分", string.Format("您在{0}至{1}期间连续签到，系统奖励{2:N2}积分", dts.ToString("yyyy/MM/dd"), dte.ToString("yyyy/MM/dd"), linePoint), DateTime.Now);
}
}
}
}
public static void StockByLevel(DateTime dts, DateTime dte, int reStock, int debug)
{
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
int levelCount = 0;
int levelStart = 3;
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
if (1 == debug)
{
SqlContext.Pipe.Send(string.Format("dts：{0}-dte：{1}", dts, dte));
SqlContext.Pipe.Send(string.Format("dts：{0}-dte：{1}", dts, dte));
}
connection.Open();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
var command = connection.CreateCommand();
List <int> badList = new List <int>();
Dictionary <int, decimal> stockDicKey = new Dictionary <int, decimal>();
command.Parameters.Clear();
command.CommandText = "SELECT cfg003 FROM wgs027 WHERE cfg001=@cfg001;";
command.Parameters.AddWithValue("@cfg001", "SYS_STOCK_MAX");
var maxtStock = command.ExecuteScalar().ToString();
var maxtStockSplit = maxtStock.Split(',');
foreach (var item in maxtStockSplit)
{
var itemSplit = item.Split(':');
stockDicKey.Add(int.Parse(itemSplit[0]), decimal.Parse(itemSplit[1]));
}
if (1 == debug)
{
SqlContext.Pipe.Send(string.Format("SYS_STOCK_MAX：{0}", maxtStock));
}
SqlContext.Pipe.Send(ExtDCFunction.SplitLine);
command.Parameters.Clear();
command.CommandText = "SELECT cfg003 FROM wgs027 WHERE cfg001=@cfg001;";
command.Parameters.AddWithValue("@cfg001", "SYS_STOCK_LIMIT");
var lowLimit = Convert.ToDecimal(command.ExecuteScalar());
do
{
command.Parameters.Clear();
command.CommandText = "SELECT u001,u002,u002,u003,u012,u013,u014,u018,u019 FROM wgs049 WHERE u018=@u018;";
command.Parameters.AddWithValue("@u018", levelStart);
da.SelectCommand = command;
da.Fill(ds, "StartUserList");
levelCount = ds.Tables["StartUserList"].Rows.Count;
bool inBadList = false;
foreach (DataRow item in ds.Tables["StartUserList"].Rows)
{
var u001 = (int)item["u001"];
var u002 = item["u002"].ToString().Trim();
var u003 = item.IsNull("u003") ? string.Empty : item["u003"].ToString().Trim();
var u012 = (int)item["u012"];
var u018 = (int)item["u018"];
var u019 = (decimal)item["u019"];
var u014 = item.IsNull("u014") ? string.Empty : item["u014"].ToString().Trim();
decimal parentu019 = 0;
decimal parentHaveStock = 0.0000m;
foreach (var skip in badList)
{
command.Parameters.Clear();
command.CommandText = "SELECT COUNT(1) FROM wgs048(NOLOCK) WHERE u001=@u001 AND u002=@u002;";
command.Parameters.AddWithValue("@u001", skip);
command.Parameters.AddWithValue("@u002", u001);
var badCount = (int)command.ExecuteScalar();
if (0 < badCount)
{
inBadList = true;
}
}
if (true == inBadList)
{
inBadList = false;
continue;
}
command.Parameters.Clear();
command.CommandText = "SELECT u018 FROM wgs049 WHERE u001=@u012;";
command.Parameters.AddWithValue("@u012", u012);
var parentu018 = (int)command.ExecuteScalar();
if (2 == parentu018)
{
command.Parameters.Clear();
command.CommandText = "SELECT SUM(dr004) AS SumBet,SUM(dr006) AS SumWin,SUM(dr007) AS SumPoint,SUM(dr011) AS SumFreeGet, SUM(dr014) AS SumCFee, SUM(dr015) AS SumWFee FROM wgs042(NOLOCK) WHERE u001 IN(SELECT u002 FROM wgs048(NOLOCK) AS URT WHERE URT.u001=@u001 AND URT.u002<>@u001) AND (dr002 >=@dts AND dr002 <= @dte);";
command.Parameters.AddWithValue("@u001", u012);
command.Parameters.AddWithValue("@dts", dts);
command.Parameters.AddWithValue("@dte", dte);
da.SelectCommand = command;
da.Fill(ds, "DayParentSum");
DataRow calcRowP = ds.Tables["DayParentSum"].Rows[0];
var SumBetP = calcRowP.IsNull("SumBet") ? 0 : (decimal)calcRowP["SumBet"];
var SumWinP = calcRowP.IsNull("SumWin") ? 0 : (decimal)calcRowP["SumWin"];
var SumPointP = calcRowP.IsNull("SumPoint") ? 0 : (decimal)calcRowP["SumPoint"];
var SumFreeGetP = calcRowP.IsNull("SumFreeGet") ? 0 : (decimal)calcRowP["SumFreeGet"];
var SumAllP = (SumWinP + SumPointP + SumFreeGetP) - SumBetP;
if (SumAllP > 0 - lowLimit)
{
badList.Add(u012);
if (1 == debug)
{
SqlContext.Pipe.Send(string.Format("线长未达到最底要求，系统要求{0}，线程现在{1}", 0 - lowLimit, SumAllP));
continue;
}
}
ds.Tables["DayParentSum"].Clear();
}
command.Parameters.Clear();
command.CommandText = "SELECT u019 FROM wgs049 WHERE u001=@u012;";
command.Parameters.AddWithValue("@u012", u012);
parentu019 = (decimal)command.ExecuteScalar();
parentHaveStock = parentu019 - u019;
if (0 >= parentHaveStock)
{
continue;
}
if (1 == debug)
{
SqlContext.Pipe.Send(string.Format("上线可得：{0}:{1}-{2}:{3}", u002, u019, u014, parentu019));
SqlContext.Pipe.Send(string.Format("上级可得：{0}:{1}", u014, parentHaveStock));
}
command.Parameters.Clear();
command.CommandText = "SELECT SUM(dr004) AS SumBet,SUM(dr006) AS SumWin,SUM(dr007) AS SumPoint,SUM(dr011) AS SumFreeGet, SUM(dr014) AS SumCFee, SUM(dr015) AS SumWFee FROM wgs042(NOLOCK) WHERE u001 IN(SELECT u002 FROM wgs048(NOLOCK) AS URT WHERE URT.u001=@u001 AND URT.u002<>@u001) AND (dr002 >=@dts AND dr002 <= @dte);";
command.Parameters.AddWithValue("@u001", u001);
command.Parameters.AddWithValue("@dts", dts);
command.Parameters.AddWithValue("@dte", dte);
da.SelectCommand = command;
da.Fill(ds, "DaySum");
if (0 < ds.Tables["DaySum"].Rows.Count)
{
command.Parameters.Clear();
command.CommandText = "SELECT u003 FROM wgs049(NOLOCK) WHERE u001=@u001;";
command.Parameters.AddWithValue("@u001", u012);
var pResult = command.ExecuteScalar();
var parentNickName = pResult == null ? string.Empty : pResult.ToString().Trim();
command.Parameters.Clear();
command.CommandText = "SELECT u018 FROM wgs049(NOLOCK) WHERE u001=@u001;";
command.Parameters.AddWithValue("@u001", u012);
var pLevel = command.ExecuteScalar();
var pLevelValue = pLevel == null ? string.Empty : pLevel.ToString().Trim();
DataRow calcRow = ds.Tables["DaySum"].Rows[0];
var SumBet = calcRow.IsNull("SumBet") ? 0 : (decimal)calcRow["SumBet"];
var SumWin = calcRow.IsNull("SumWin") ? 0 : (decimal)calcRow["SumWin"];
var SumPoint = calcRow.IsNull("SumPoint") ? 0 : (decimal)calcRow["SumPoint"];
var SumFreeGet = calcRow.IsNull("SumFreeGet") ? 0 : (decimal)calcRow["SumFreeGet"];
var SumAll = (SumWin + SumPoint + SumFreeGet) - SumBet;
var SumAllOK = SumAll * (parentHaveStock / 100.0000m);
var SumAllOKData = Math.Abs(SumAllOK);
if (1 == debug)
{
SqlContext.Pipe.Send(string.Format("编号：{0}，账号：{1}，下线纯利（不包括自己）:{2}，分红：{3}", u001, u002, SumAll, SumAllOKData));
}
if (0 > SumAll)
{
ExtDCFunction.SendMessage(ref command, u012, u014, parentNickName, string.Format("{0}至{1}系统分红", dts.ToString(), dte.ToString()), string.Format("您在{0}至{1}期间获得系统分红，分红金额{2:N2}（{3:N2}%）", dts.ToString(), dte.ToString(), SumAllOKData, parentHaveStock), DateTime.Now);
UST.WriteDataChangeLog(ref command, u012, u014, parentNickName, 0, 0, 0, 0, 0, 0, 0, SumAllOKData, 0, 0, 0, 0, 0, 7, ExtDCFunction.GetRDList()[7].Name, "", "", 0, DateTime.Now);
UST.SetDayReport(ref command, u012, u014, parentNickName, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, SumAllOKData, 0);
command.Parameters.Clear();
command.CommandText = "UPDATE wgs014 SET uf001=uf001+@uf001 WHERE u001=@u001;";
command.Parameters.AddWithValue("@uf001", SumAllOKData);
command.Parameters.AddWithValue("@u001", u012);
command.ExecuteNonQuery();
command.Parameters.Clear();
command.CommandText = "INSERT INTO wgs051(us002,us003,us004,us005,us006,us007,us008,us009,us010,us011,us012,us013,us014,us015,us016) VALUES(@us002,@us003,@us004,@us005,@us006,@us007,@us008,@us009,@us010,@us011,@us012,@us013,@us014,@us015,@us016);";
command.Parameters.AddWithValue("@us002", u012);
command.Parameters.AddWithValue("@us003", u014);
command.Parameters.AddWithValue("@us004", parentNickName);
command.Parameters.AddWithValue("@us005", u001);
command.Parameters.AddWithValue("@us006", u002);
command.Parameters.AddWithValue("@us007", u003);
command.Parameters.AddWithValue("@us008", SumAllOKData);
command.Parameters.AddWithValue("@us009", parentHaveStock);
command.Parameters.AddWithValue("@us010", DateTime.Now);
command.Parameters.AddWithValue("@us011", dts);
command.Parameters.AddWithValue("@us012", dte);
command.Parameters.AddWithValue("@us013", SumAll);
command.Parameters.AddWithValue("@us014", pLevel);
command.Parameters.AddWithValue("@us015", u018);
command.Parameters.AddWithValue("@us016", u019);
command.ExecuteNonQuery();
if (1 == debug)
{
SqlContext.Pipe.Send(ExtDCFunction.SplitLine);
}
}
ds.Tables["DaySum"].Clear();
}
}
ds.Tables["StartUserList"].Clear();
levelStart++;
}while (0 < levelCount);
}
}
