public static string ConvertToConsumeType(EnumConsumeType consumeType)
{
switch (consumeType)
{
case EnumConsumeType.AccountIncome: return "账户存入";
case EnumConsumeType.BuyEquipment: return "设备购买";
case EnumConsumeType.MessageAlarm: return "短信报警";
case EnumConsumeType.MessageQueryVehicle: return "短信查车";
case EnumConsumeType.RechargeToCustomer: return "给客户充值";
case EnumConsumeType.ServiceFee: return "服务费";
default : return "";
}
}
/// <summary>
/// 查询租户的账户信息
/// </summary>
/// <param name="strTenantCode">租户编码以逗号隔开</param>
/// <param name="StartTime">开始时间</param>
/// <param name="EndTime">结束时间</param>
/// <param name="EnumConsumeType?"></param>
/// <param name="rowIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowCount"></param>
/// <returns></returns>
public IList<EMTenantAccountDetail> SelectTenantCountDetail(string strTenantCode, DateTime StartTime, DateTime EndTime, EnumConsumeType? consumeType, int rowIndex, int pageSize, ref int rowCount)
{
rowCount = 0;
if (string.IsNullOrEmpty(strTenantCode))
{
strTenantCode = "''";
}
string consumeTypeCondition = consumeType.HasValue ? "and ConsumeType=" + Convert.ToInt32(consumeType.Value) : "";
List<EMTenantAccountDetail> list = new List<EMTenantAccountDetail>();
string sCmdText = string.Format(@"select * from movo_tenantaccount_detail where TenantCode in ({0})
and CreateTime between '{1}' and '{2}' {5} order by CreateTime desc limit {3},{4};", strTenantCode, StartTime, EndTime, rowIndex, pageSize, consumeTypeCondition);
string sql = string.Format(@"select count(*) from movo_tenantaccount_detail where TenantCode in ({0})
and CreateTime between '{1}' and '{2}' {3} ", strTenantCode, StartTime, EndTime, consumeTypeCondition);
using (DbDataReader sdrCount = MySqlDB.GetDataReader(CommandType.Text, sql))
{
if (sdrCount != null)
{
while (sdrCount.Read())
{
rowCount = rowCount + Convert.ToInt32(sdrCount[0]);
}
}
sdrCount.Close();
}
using (DbDataReader sdr = MySqlDB.GetDataReader(CommandType.Text, sCmdText))
{
if (sdr != null)
{
while (sdr.Read())
{
EMTenantAccountDetail acc = new EMTenantAccountDetail();
acc.ConsumeMoney = sdr["ConsumeMoney"].ToDecimal();
acc.ConsumeType = (EnumConsumeType)sdr["ConsumeType"].ToInt();
acc.CreateTime = sdr["CreateTime"].ToDateTime();
acc.OperateType = (EnumOperateType)sdr["OperateType"].ToInt();
acc.OperatorTenantCode = sdr["OperatorTenantCode"].ToString();
acc.Remark = sdr["Remark"].ToString();
acc.TargetTenantCode = sdr["TargetTenantCode"].ToString();
acc.TenantCode = sdr["TenantCode"].ToString();
list.Add(acc);
}
}
sdr.Close();
}
return list;
}
public void DecuntMoney(string tenantCode, string opratorTenantCode, EnumConsumeType type, decimal money, string remark)
{
var detail = new ETenantAccountDetail()
{
TenantCode = tenantCode,
ConsumeMoney = money,
OperateType = EnumOperateType.Outlay,
ConsumeType = type,
Remark = remark,
CreateTime = DateTime.Now,
OperatorTenantCode = opratorTenantCode,
};
using (var trans = DACFacade.Movo.TenantAccountDAC.BeginTransaction())
{
try
{
DACFacade.Movo.TenantAccountDAC.UpdateBalance(trans, tenantCode, -money);
DACFacade.Movo.TenantAccountDetailDAC.Insert(trans, detail);
DACFacade.Movo.TenantAccountDetailDAC.CommitTransaction(trans);
}
catch
{
DACFacade.Movo.TenantAccountDetailDAC.RollbackTransaction(trans);
throw;
}
}
}
// 平台商操作运营商
public void FutuoOperator(string fromTenantCode, string toTenantCode, decimal money,int operatorType,EnumConsumeType consumeType,string remark)
{
// 0为充值，1为扣款
//decimal balance = QueryBalance(toTenantCode);
//using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString))
//{
// conn.Open();
// var tran = conn.BeginTransaction();
// string sql = "";
// EMTenantAccountDetailService serv = new EMTenantAccountDetailService();
// if (operatorType == 0)
// {
// sql = string.Format(@"update movo_tenant_account set Balance = Balance + {0} where TenantCode = '{1}';",
// money, toTenantCode);
// MySqlHelper.ExecuteNonQuery(conn, sql);
// EMTenantAccountDetail fromTenantDetail = new EMTenantAccountDetail()
// {
// ConsumeMoney = money,
// ConsumeType = consumeType,
// OperateType = EnumOperateType.Income,
// CreateTime = DateTime.Now,
// TargetTenantCode = null,
// TenantCode = toTenantCode,
// Remark = remark,
// OperatorTenantCode = fromTenantCode,
// };
// serv.Insert(conn, fromTenantDetail);
// }
// else
// {
// if (balance < money)
// throw new Exception("对不起，余额不足，扣款失败！");
// sql = string.Format(@"update movo_tenant_account set Balance = Balance - {0} where TenantCode = '{1}';",
// money, toTenantCode);
// MySqlHelper.ExecuteNonQuery(conn, sql);
// EMTenantAccountDetail toTenantDetail = new EMTenantAccountDetail()
// {
// ConsumeMoney = money,
// ConsumeType = consumeType,
// OperateType = EnumOperateType.Outlay,
// TargetTenantCode = null,
// CreateTime = DateTime.Now,
// TenantCode = toTenantCode,
// Remark = remark,
// OperatorTenantCode = fromTenantCode,
// };
// serv.Insert(conn, toTenantDetail);
// }
// tran.Commit();
//}
TenantCountDetailDAL dal = new TenantCountDetailDAL();
dal.FutuoOperator(fromTenantCode, toTenantCode, money, operatorType, consumeType, remark);
}
public void DecuntMoney(string tenantCode, string opratorTenantCode, EnumConsumeType type, decimal money, string remark)
{
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString))
{
conn.Open();
var tran = conn.BeginTransaction();
string sql = string.Format(@"update movo_tenant_account set Balance = Balance - {0} where TenantCode = '{1}';",
money, tenantCode);
MySqlHelper.ExecuteNonQuery(conn, sql);
EMTenantAccountDetail detail = new EMTenantAccountDetail()
{
ConsumeMoney = money,
ConsumeType = type,
OperateType = EnumOperateType.Outlay,
CreateTime = DateTime.Now,
TenantCode = tenantCode,
Remark = remark,
OperatorTenantCode = opratorTenantCode,
};
IEMTenantAccountDetailService serv = new EMTenantAccountDetailService();
serv.Insert(conn, detail);
tran.Commit();
}
}
/// <summary>
/// 充值/扣款
/// </summary>
/// <param name="fromTenantCode"></param>
/// <param name="toTenantCode"></param>
/// <param name="money"></param>
/// <param name="operatorType">0为充值，1为扣款</param>
/// <param name="consumeType">消费类型</param>
/// <param name="remark"></param>
public void FutuoOperator(string fromTenantCode, string toTenantCode, decimal money, int operatorType, EnumConsumeType consumeType, string remark)
{
#region 查询余额
decimal balance = 0;
string sqlblance = string.Format(@"select * from movo_tenant_account where TenantCode={0};", toTenantCode);
DataSet ds = MySqlDB.GetDataSet(CommandType.Text, sqlblance);
if (ds == null || ds.Tables[0].Rows.Count == 0)
{
string sqlInsert = string.Format(@"INSERT INTO movo_tenant_account (`TenantCode`, `Balance`) VALUES ('{0}',0);", toTenantCode);
MySqlDB.ExecuteNonQuery(CommandType.Text, sqlInsert);
}
else
{
balance = Convert.ToDecimal(ds.Tables[0].Rows[0]["Balance"]);
}
#endregion
#region 充值/扣款采用事物
MySqlConnection con = new MySqlConnection(MySqlDB.ConnString);
con.Open();
MySqlTransaction trans = con.BeginTransaction();
MySqlCommand cmd = con.CreateCommand();
cmd.Transaction = trans;
if (operatorType == 0)//充值
{
#region 充值
try
{
string addsql = string.Format(@"update movo_tenant_account set Balance = Balance + {0} where TenantCode = '{1}';",
money, toTenantCode);
cmd.CommandText = addsql;
int upCount = cmd.ExecuteNonQuery();
string insertsql = string.Format(@"insert into movo_tenantaccount_detail (TenantCode, ConsumeMoney,OperateType,ConsumeType, TargetTenantCode, Remark, CreateTime,OperatorTenantCode) values(@TenantCode,@ConsumeMoney,@OperateType,@ConsumeType,@TargetTenantCode,@Remark,@CreateTime,@OperatorTenantCode)");
cmd.Parameters.AddRange(new MySqlParameter[] {
new MySqlParameter("@TenantCode", toTenantCode),
new MySqlParameter("@ConsumeMoney", money),
new MySqlParameter("@OperateType", EnumOperateType.Income),
new MySqlParameter("@ConsumeType", consumeType),
new MySqlParameter("@TargetTenantCode", null),
new MySqlParameter("@Remark", remark == null ? "" : remark),
new MySqlParameter("@CreateTime", DateTime.Now),
new MySqlParameter("@OperatorTenantCode", fromTenantCode)
});
cmd.CommandText = insertsql;
int inCount = cmd.ExecuteNonQuery();
if (upCount >= 1 && inCount >= 1)
{
trans.Commit();
}
}
catch (Exception ex)
{
trans.Rollback();
}
finally
{
con.Close();
}
#endregion
}
else//扣款
{
#region 扣款
try
{
if (balance < money)
throw new Exception("对不起，余额不足，扣款失败！");
string upsql = string.Format(@"update movo_tenant_account set Balance = Balance - {0} where TenantCode = '{1}'", money, toTenantCode);
cmd.CommandText = upsql;
int upCount = cmd.ExecuteNonQuery();
string insertsql = string.Format(@"insert into movo_tenantaccount_detail (TenantCode, ConsumeMoney,OperateType,ConsumeType, TargetTenantCode, Remark, CreateTime,OperatorTenantCode) values(@TenantCode,@ConsumeMoney,@OperateType,@ConsumeType,@TargetTenantCode,@Remark,@CreateTime,@OperatorTenantCode)");
cmd.Parameters.AddRange(new MySqlParameter[] {
new MySqlParameter("@TenantCode", toTenantCode),
new MySqlParameter("@ConsumeMoney", money),
new MySqlParameter("@OperateType", EnumOperateType.Outlay),
new MySqlParameter("@ConsumeType", consumeType),
new MySqlParameter("@TargetTenantCode", null),
new MySqlParameter("@Remark", remark == null ? "" : remark),
new MySqlParameter("@CreateTime", DateTime.Now),
new MySqlParameter("@OperatorTenantCode", fromTenantCode)
});
cmd.CommandText = insertsql;
int inCount = cmd.ExecuteNonQuery();
if (upCount >= 1 && inCount >= 1)
{
trans.Commit();
}
}
catch (Exception ex)
{
trans.Rollback();
}
#endregion
}
#endregion
}
/// <summary>
/// 获取消费类型
/// </summary>
/// <param name="cs"></param>
/// <returns></returns>
private string GetConsumeType(EnumConsumeType cs)
{
switch (cs)
{
case EnumConsumeType.AccountIncome:
return "账户存入";
case EnumConsumeType.BuyEquipment:
return "设备购买";
case EnumConsumeType.MessageAlarm:
return "短信报警";
case EnumConsumeType.MessageQueryVehicle:
return "短信查车";
case EnumConsumeType.RechargeToCustomer:
return "给客户充值";
case EnumConsumeType.ServiceFee:
return "服务费";
case EnumConsumeType.OnlinePayment:
return "在线支付";
case EnumConsumeType.OnlinePaymentFixed:
return "在线支付补款";
}
return "";
}
/// <summary>
/// 获取消费类型
/// </summary>
/// <param name="cs"></param>
/// <returns></returns>
private string GetConsumeType(EnumConsumeType consumeType)
{
string returnString = string.Empty;
switch (consumeType)
{
case EnumConsumeType.AccountIncome:
{
returnString = "账户存入";
} break;
case EnumConsumeType.BuyEquipment:
{
returnString = "设备购买";
} break;
case EnumConsumeType.MessageAlarm:
{
returnString = "短信报警";
} break;
case EnumConsumeType.MessageQueryVehicle:
{
returnString = "短信查车";
} break;
case EnumConsumeType.RechargeToCustomer:
{
returnString = "给客户充值";
} break;
case EnumConsumeType.ServiceFee:
{
returnString = "服务费";
} break;
}
return returnString;
}
public bool DecuntMoney(string tenantCode, string opratorTenantCode, EnumConsumeType type, decimal money, string remark)
{
try
{
EMTenantAccountService service = new EMTenantAccountService();
service.DecuntMoney(tenantCode, opratorTenantCode, type, money, remark);
return true;
}
catch (Exception ex)
{
Logger.Error(ex.Message, ex);
return false;
}
}
public IList<EMTenantAccountDetail> Select(List<string> tenantCodeList, DateTime StartTime, DateTime EndTime, EnumConsumeType? consumeType, int rowIndex, int pageSize, ref int rowCount, ref int IncomeRowCount, ref decimal IncomeAccount, ref int OutlayRowCount, ref decimal OutlayAccount)
{
IList<EMTenantAccountDetail> list = new List<EMTenantAccountDetail>();
if (tenantCodeList == null || tenantCodeList.Count == 0)
return list;
StringBuilder strTenantCode = new StringBuilder();
for (int i = 1, i_count = tenantCodeList.Count; i <= i_count; i++)
{
if (i == i_count)
strTenantCode.Append(string.Format("'{0}'", tenantCodeList[i - 1].ToString()));
else
strTenantCode.Append(string.Format("'{0}',", tenantCodeList[i - 1].ToString()));
}
TenantCountDetailDAL dal = new TenantCountDetailDAL();
#region 查询所有存入金额
// string sqlIncome = string.Format(@"select ConsumeMoney from movo_tenantaccount_detail where TenantCode in ({0})
// and CreateTime between '{1}' and '{2}' and OperateType=0;", strTenantCode, StartTime, EndTime);
// DataSet ds = MySqlHelper.ExecuteDataSet(ConnectionString, sqlIncome, null);
// if (ds == null || ds.Tables[0].Rows.Count == 0)
// {
// IncomeRowCount = 0;
// IncomeAccount = 0;
// }
// else
// {
// IncomeAccount = 0;
// IncomeRowCount = ds.Tables[0].Rows.Count;
// for (int i = 0, i_Count = ds.Tables[0].Rows.Count; i < i_Count; i++)
// {
// IncomeAccount += Convert.ToDecimal(ds.Tables[0].Rows[i]["ConsumeMoney"]);
// }
// }
dal.SelectIncomeAccount(strTenantCode.ToString(), StartTime, EndTime, ref IncomeRowCount, ref IncomeAccount);
#endregion
#region 查询所有扣款金额
// string sqlOutlay = string.Format(@"select ConsumeMoney from movo_tenantaccount_detail where TenantCode in ({0})
// and CreateTime between '{1}' and '{2}' and OperateType=1;", strTenantCode, StartTime, EndTime);
// ds = MySqlHelper.ExecuteDataSet(ConnectionString, sqlOutlay, null);
// if (ds == null || ds.Tables[0].Rows.Count == 0)
// {
// OutlayRowCount = 0;
// OutlayAccount = 0;
// }
// else
// {
// OutlayAccount = 0;
// OutlayRowCount = ds.Tables[0].Rows.Count;
// for (int i = 0, i_Count = ds.Tables[0].Rows.Count; i < i_Count; i++)
// {
// OutlayAccount += Convert.ToDecimal(ds.Tables[0].Rows[i]["ConsumeMoney"]);
// }
// }
dal.SelectOutlayAccount(strTenantCode.ToString(), StartTime, EndTime, ref OutlayRowCount, ref OutlayAccount);
#endregion
//string consumeTypeCondition = consumeType.HasValue ? "and ConsumeType=" + Convert.ToInt32(consumeType.Value) : "";
//查询总数据笔数
// string sqlAll = string.Format(@"select count(*) from movo_tenantaccount_detail where TenantCode in ({0})
// and CreateTime between '{1}' and '{2}' {3};", strTenantCode, StartTime, EndTime, consumeTypeCondition);
// ds = MySqlHelper.ExecuteDataSet(ConnectionString, sqlAll, null);
// if (ds == null || ds.Tables[0].Rows.Count == 0)
// {
// rowCount = 0;
// }
// else
// {
// rowCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
// }
// string sql = string.Format(@"select * from movo_tenantaccount_detail where TenantCode in ({0})
// and CreateTime between '{1}' and '{2}' {5} order by CreateTime desc limit {3},{4};", strTenantCode, StartTime, EndTime, rowIndex, pageSize, consumeTypeCondition);
// ds = MySqlHelper.ExecuteDataSet(ConnectionString, sql, null);
// if (ds == null || ds.Tables[0].Rows.Count == 0)
// return list;
// for (int i = 0, i_Count = ds.Tables[0].Rows.Count; i < i_Count; i++)
// {
// list.Add(ConvertInfo(ds.Tables[0].Rows[i]));
// }
//代码优化
list = dal.SelectTenantCountDetail(strTenantCode.ToString(), StartTime, EndTime, consumeType, rowIndex, pageSize, ref rowCount);
return list;
}
