public JsonResult ToRongBao()
{
JsonMessage resultData = new JsonMessage ();
System.Data.DataTable dt = null;
int RandomNum;
Random MyRandom = new Random ();
RandomNum = MyRandom.Next (1001, 9999);
string batchCurrnum = DateTime.Now.ToString ("yyyyMMddHHmmss") + RandomNum;
string selectSqlFields = "select top 1 a.id,a.terminal,a.finallyMoney money,a.sum2Id,a.tradeMoney,a.createDate, b.faren,b.shanghuName,b.bankName ,b.bankName2,b.bankName3,b.province,b.bankAccount,b.city,b.tel,b.sourceAccount,isnull(b.dayMax,0) dayMax,isnull(b.dayMin,0) dayMin,isnull(eachMin,0) eachMin , isnull(eachMax,0) eachMax,isnull(b.daifufei,0) daifufei from transactionSum a join customers b on b.terminal = a.terminal and b.status > -1";
string sql;
try
{
string todayStr = DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd 23:00:00");
string todayEndStr = DateTime.Today.ToString("yyyy-MM-dd 23:00:00");
string batchDate = DateTime.Now.ToString("yyyyMMdd");
DataRow dr;//要上传的数据行
string checkPass = null;
string terminal;
inUploadRongbao = true;
using (DBContext db = new DBContext(true))
{
LogStep("查询结算汇总表，查询第一条结算数据");//开始
sql = selectSqlFields+" where a.status = 0 and b.status <> -1 and b.frozen <> 1 and createDate < '" + todayEndStr + "' order by a.createDate";//查询结算汇总表
//注意：不要用事务，防止提交融宝成功后的异常又回滚
dt = db.QueryTable(sql);
if (dt.Rows.Count == 0) return Json(resultData, JsonRequestBehavior.AllowGet);
if (dt.Columns.IndexOf("id") == -1) throw new Exception("查询送盘数据失败，没有ID列");
dr = dt.Rows[0];
if (dr["id"].Equals(DBNull.Value)) throw new Exception("ID列数据为空");
if (dr["terminal"].Equals(DBNull.Value)) throw new Exception("terminal列数据为空");
if (dr["money"].Equals(DBNull.Value)) throw new Exception("money列数据为空");
if (dr["sum2Id"] == DBNull.Value) dr["sum2Id"] = 0;
terminal = dr["terminal"].ToString();
if (Convert.ToInt32(dr["sum2Id"]) != -1)//如果是合并后的数据，不检测金额
{
LogStep("终端" + terminal + "开始结算上传,判断金额");
decimal minMoney = Convert.ToDecimal(dr["dayMin"]);
decimal maxMoney = Convert.ToDecimal(dr["dayMax"]);
decimal eachMinMoney = Convert.ToDecimal(dr["eachMin"]);
decimal eachMaxMoney = Convert.ToDecimal(dr["eachMax"]);
decimal currentMoney = Convert.ToDecimal(dr["tradeMoney"]);
if (currentMoney > eachMaxMoney)
{
db.ExecuteCommand("update transactionsum set status =-2,results ='单笔金额超过上限' where id=" + dr["id"]);
checkPass = "******" + currentMoney + "超过单笔上限";
}
else if (currentMoney < eachMinMoney)
{
db.ExecuteCommand("update transactionsum set status =-3,results ='单笔金额未到下限' where id=" + dr["id"]);
checkPass = "******" + currentMoney + "未到单笔下限";
}
//当日已结算金额,已送的不管成不成功
sql = string.Format("select isnull(sum(tradeMoney),0) from transactionsum where terminal='{0}' and (status = -2 or status >0) and uploadDate between '{1}' and '{2}'", terminal, todayStr, todayEndStr);
decimal daySumedMoney = Convert.ToDecimal(db.ExecScalar(sql));
daySumedMoney += currentMoney;
//当日待结算的金额,该笔之前的
sql = string.Format("select isnull(sum(tradeMoney),0) from transactionsum where terminal='{0}' and status in (0,-3) and createDate <'{2}'", terminal, todayStr, dr["createDate"]);
decimal dayUnSumMoney = Convert.ToDecimal(db.ExecScalar(sql));
dayUnSumMoney += currentMoney;
if (daySumedMoney > maxMoney)
{
string reason = checkPass == null ? "金额超过当日上限" : checkPass;
db.ExecuteCommand("update transactionsum set status =-3,results ='" + reason + "' where id=" + dr["id"]);
checkPass = "******" + daySumedMoney + "," + reason;
}
else if (dayUnSumMoney < minMoney)
{
string reason = checkPass == null ? "金额小于当日下限" : checkPass;
db.ExecuteCommand("update transactionsum set status =-3,results ='" + reason + "' where id=" + dr["id"]);
checkPass = "******" + dayUnSumMoney + "," + reason;
}
else
{
//满足条件后消除限额
stepName = "查询是否有满足条件后消除限额的数据";
DataTable beforeDisabelDt = db.QueryTable("select id,tradeMoney from transactionsum where status = -3 and terminal ='" + terminal + "' and createDate < '" + todayEndStr + "'");
if (beforeDisabelDt.Rows.Count > 0)
{ //如果有恢复原来的，则组织成一条再发
LogStep("合并数据,之前暂停的有" + beforeDisabelDt.Rows.Count + "条");
decimal tempMoney = 0;
string canUploadIdArr = "";
int canUploadCount = 0;
for (int i = 0; i < beforeDisabelDt.Rows.Count; i++)
{
decimal rowMoney = Convert.ToDecimal(beforeDisabelDt.Rows[i]["tradeMoney"]);
if (tempMoney + rowMoney > maxMoney) continue;//如果合并后的金额还是大于当日限额，则超出部分不结
tempMoney += rowMoney;
canUploadCount++;
canUploadIdArr += "," + beforeDisabelDt.Rows[i]["id"];
}
if (canUploadCount == 0) LogStep("汇总成一条记录，但还是超过限额,有" + beforeDisabelDt.Rows.Count + "条记录不会被上传结算");
else
{
if (tempMoney > minMoney) checkPass = null;//如果当日累计达到条件，则该笔就算未达到也加入合并
if (checkPass == null)
{
canUploadIdArr = dr["id"] + canUploadIdArr;//加上自己（满足条件的这条+原来被暂停的那条）
//汇总成新的记录
object scalar = db.ExecScalar("insert into transactionsum(sum2Id,tradeMoney,discountMoney,tixianfeiMoney,finallyMoney,status,terminal,createDate,batchCount) select -1,sum(tradeMoney),sum(discountMoney),sum(tixianfeiMoney),sum(finallyMoney),0," + terminal + ",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',sum(batchCount) from transactionsum where id in (" + canUploadIdArr + "); select @@identity");
if (scalar == null) throw new Exception("汇总成新的记录异常，identity is null");
int newSumId = Convert.ToInt32(scalar);
//合并后作废原来的
db.ExecuteCommand("update transactionsum set status = -1,sum2Id=" + newSumId + ",results= isnull(results,'')+ ' 合并到" + newSumId + "' where id in ( " + canUploadIdArr + ")");
sql = selectSqlFields+" where a.id=" + newSumId;
dr = db.QueryTable(sql).Rows[0];//提交合并后的到rongbao
}
}
}
}
}
else
{
LogStep("终端" + terminal + "(合并),开始上传");
}
db.Close();
}
if (checkPass != null)
{
resultData.Message = terminal + ":" + checkPass;
}
else
{
SentRowToRongBao( dr, batchCurrnum, batchDate, terminal, ref stepName);
resultData.Result = new string[] { batchCurrnum, batchDate, terminal };
}
}
catch (Exception ex)
{
resultData.Message = stepName + "时发生异常：" + ex.Message;
LogError(stepName, ex);
if (dt != null)
{
try
{
using (DBContext context = new DBContext())
{
foreach (DataRow dr in dt.Rows)
{
resultData.Message = resultData.Message.Replace('\'', ' ');
string errorStatus = "-2";
if (ex is System.Net.WebException && ex.Message.IndexOf("The remote name could not be resolved") > -1)
{
LogStep("网络问题，下次继续传");
errorStatus = "0";
}
sql = "update transactionSum set status= " + errorStatus + " ,results = '" + resultData.Message + "' where batchCurrnum='" + batchCurrnum + "' and id='" + dr["id"] + "' and status= 1";
context.ExecuteCommand(sql);
}
}
}
catch (MySqlException e)
{
resultData.Message += " , 更新失败标记也失败！" + e.Message;
LogError("更新失败标记", ex);
}
}
}
finally
{
inUploadRongbao = false;
}
return Json (resultData, JsonRequestBehavior.AllowGet);
}
public JsonMessage SyncJingKongPosData(string terminal, string json)
{
if (DateTime.Now.Day != lastSyncTime.Day) {
//每天同步一次，一次同步一个月
SyncHolday();
}
lastSyncTime = DateTime.Now;
while (inUploadRongbao) {
System.Threading.Thread.Sleep(500);
}
if (clients.ContainsKey("xiandaijk")) clients["xiandaijk"].lastAccess = DateTime.Now;
else {
//iis reset register again
RegisterClient("xiandaijk");
}
DBContext db = new DBContext(true);
JsonMessage resultData = new JsonMessage();
DateTime maxTime = DateTime.MinValue;
List<TransactionLog> newItemList = new List<TransactionLog>();
System.Web.HttpContext.Current.Application.Lock();
try
{
LogStep("获取客户信息");
Customer customerInfo = db.QuerySingle<Customer>(new { terminal = terminal, status = ">-1 " });
//SourceAccount customerSource = db.QuerySingle<SourceAccount>(customerInfo.sourceAccount.Value);
if (customerInfo == null) { throw new Exception("获取客户信息is null, terminal：" + terminal); }
if (customerInfo.discount == null) { throw new Exception("获取客户信息discount is null or NaN,terminal：" + terminal); }
if (customerInfo.tixianfei == null) { throw new Exception("获取客户信息tixianfei is null or NaN, terminal：" + terminal); }
if (customerInfo.tixianfeiEles == null) { throw new Exception("获取客户信息tixianfeiEles is null or NaN, terminal：" + terminal); }
stepName = "解析json : \r\n" + json;
JingKongResult jkResult = JsonConvert.DeserializeObject<JingKongResult>(json);
if (jkResult == null) throw new Exception("序列化出错：" + json);
TransactionLog[] lst = jkResult.ToTransactionArray();
foreach (TransactionLog localItem in lst)
{
//判断记录是否存在,存在的话忽略,不存在则插入
var whereSql = new { terminal = terminal, timeStr = localItem.timeStr, tradeName = localItem.tradeName, tradeMoney = localItem.tradeMoney };
stepName = localItem.time + " " + localItem.tradeName + "是否已经保存过";
if (db.Exists("", "TransactionLog", whereSql)) continue;
LogStep("分析时间，23点后算次日");
string tempTime;
DateTime datetime = Convert.ToDateTime(localItem.time);
if (datetime > maxTime) maxTime = datetime;
//tomorrw is holdday
if (datetime.Hour > 22) datetime = datetime.AddDays(2);
else datetime = datetime.AddDays(1);
tempTime = datetime.ToString("yyyy-MM-dd");
if (tempTime != lastTransDay)
{
LogStep("获取是否节假日");
lastTransDay = tempTime;
GetLocalHolday(tempTime, db);
//MyHttpUtility http = new MyHttpUtility();
//int tryCount = 10;
//while (true)
//{
// try
// {
// string timeJson = http.DoGet("http://www.easybots.cn/api/holiday.php?d=" + tempTime);
// if (string.IsNullOrEmpty(timeJson)) throw new Exception("节假日返回值为空");
// string[] tempArr = timeJson.Substring(1, timeJson.Length - 2).Split(':');
// if (tempArr.Length != 2) throw new Exception("节假日返回值有变动");
// isHoldDay = Convert.ToInt32(tempArr[1].Replace('\"', ' ').Trim()) > 0;
// break;
// }
// catch (System.Net.WebException ex)
// {
// tryCount--;
// if (tryCount < 0) { GetLocalHolday(tempTime, db); break; }
// LogError("使用网络节假日发生网络异常，重试"+ tryCount, ex);
// }
// catch (Exception ex)
// {
// LogStep("easybots节假日返回异常(" + ex.Message + ")，尝试读取本地设置");
// }
// break;
//}
}
LogStep("计算手续费");
calcMoney(customerInfo, localItem);
LogStep("保存交易记录");
localItem.status = 0;
db.DoSave("", localItem);
newItemList.Add(localItem);
}
LogStep("查询是否有结算交易");
string sql = "select time,id from TransactionLogs where terminal='" + terminal + "' and tradeName in ('批上送结束(平账)','批上送结束(不平账)') and resultCode='00' and status =0";
DataTable dt = db.QueryTable(sql);
if (dt.Rows.Count > 0)
{
LogStep("有" + dt.Rows.Count + "笔结算数据，开始结算");
foreach (DataRow dr in dt.Rows)
{
string time = dr["time"].ToString();
string sumLogId = dr["id"].ToString();
sql = "select max(time) prevTime from TransactionLogs where terminal='" + terminal + "' and Status=0 and tradeName in ('批上送结束(平账)','批上送结束(不平账)') and resultCode='00' and time < '" + time + "'";
object o = db.ExecScalar(sql);
string prevTime = o.ToString();
//sum log of (消费)
sql = "select count(0) batchCount,sum(tradeMoney) tradeMoney,sum(discountMoney) discountMoney,sum(tixianfeiMoney) tixianfeiMoney from transactionLogs ";
string publicWhere = " where terminal='" + terminal + "' and Status=0 and isValid = 1 and resultCode='00'";
if (string.IsNullOrEmpty(prevTime))
publicWhere += " and time < '" + time + "'";
else
publicWhere += " and time between '" + prevTime + "' and '" + time + "'";
sql += publicWhere;
DataTable dtSum = db.QueryTable(sql);
if (dtSum.Rows.Count == 0 || dtSum.Rows[0]["batchCount"].Equals(0) || dtSum.Rows[0]["tradeMoney"] == DBNull.Value) continue;
TransactionSum sumData = new TransactionSum();
sumData.shanghuName = customerInfo.shanghuName;
sumData.faren = customerInfo.faren;
sumData.tradeMoney = Convert.ToDouble(dtSum.Rows[0]["tradeMoney"]);
sumData.discountMoney = Convert.ToDouble(dtSum.Rows[0]["discountMoney"]);
sumData.tixianfeiMoney = Convert.ToDouble(dtSum.Rows[0]["tixianfeiMoney"]);
sumData.finallyMoney = Convert.ToDouble(sumData.tradeMoney) - Convert.ToDouble(sumData.discountMoney) - Convert.ToDouble(sumData.tixianfeiMoney);
sumData.status = 0;
sumData.batchCount = Convert.ToInt32(dtSum.Rows[0]["batchCount"]);
sumData.createDate = time;//(new Date()).Format("yyyy-MM-dd hh:mm:ss")；用结算记录的时间
sumData.terminal = terminal;
db.DoSave("", sumData);//保存结算数据
//更新交易记录的标记
sql = "update TransactionLogs set sumid=" + sumData.id + " , status =1 " + publicWhere;
db.ExecuteCommand(sql);
sql = "update TransactionLogs set sumid=" + sumData.id + " , status =1 where id = " + sumLogId;
db.ExecuteCommand(sql);
}
}
else
{
LogStep("不需要结算");
}
if (maxTime == DateTime.MinValue) maxTime = DateTime.Today;
//update lastQuery
sql = "update Customers set lastQuery ='" + maxTime.ToString("yyyy-MM-dd") + "' where terminal='" + terminal + "' ";
db.ExecuteCommand(sql);
LogStep("关闭连接");
db.Close();
resultData.Result = newItemList;
}
catch (Exception ex)
{
resultData.Message = stepName + "时发生异常：" + ex.Message + "\r\nDBStepMessage:" + db.GetStepMsg();
LogError(stepName, ex);
db.Dispose();
}
finally {
System.Web.HttpContext.Current.Application.UnLock();
}
return resultData;
}