/// <summary>
/// 检查店铺库存
/// </summary>
/// <param name="DisplaceOrderId">换货单号</param>
/// <returns></returns>
public static bool CheckStoreQuantity(string DisplaceOrderId, string storeID)
{
//检查店铺库存
SqlParameter[] para1 = new SqlParameter[] { new SqlParameter("@StoreID", storeID), new SqlParameter("@DisplaceOrderID", DisplaceOrderId) };
return(Convert.ToInt32(DBHelper.ExecuteScalar("CheckStoreGreaterThanDisplaceQuantity", para1, CommandType.StoredProcedure)) > 0);
}
/// <summary>
/// 获取输入会员编号和单据号是否一致
/// </summary>
/// <param name="orderid">报单号</param>
/// <param name="number">会员编号</param>
/// <returns></returns>
public static int SelectMemberAndOrder(string orderid, string number)
{
return(Convert.ToInt32(DBHelper.ExecuteScalar("select count(0) from memberorder where orderid=@orderid and number=@num ", new SqlParameter[] { new SqlParameter("@orderid", orderid), new SqlParameter("@num", number) }, CommandType.Text)));
}
public static int GetBiaoZhunBiZhong()
{
return(Convert.ToInt32(DBHelper.ExecuteScalar("select top 1 standardMoney from currency")));
}
/// <summary>
/// 根据银行获取国家 ---DS2012
/// </summary>
/// <param name="BankName"></param>
/// <returns></returns>
public static string GetCountryByBank(string BankName)
{
return(DBHelper.ExecuteScalar("select b.name from memberbank a,country b where a.countrycode=b.id and a.BankCode=@name", new SqlParameter("@name", BankName), CommandType.Text).ToString());
}
/// <summary>
/// 获取报单是否是首次报单
/// </summary>
/// <param name="OrderId"></param>
/// <returns></returns>
public static int SelectIsAgain(string OrderId)
{
return(Convert.ToInt32(DBHelper.ExecuteScalar("select IsAgain from memberorder where orderid=@orderid", new SqlParameter[] { new SqlParameter("@orderid", OrderId) }, CommandType.Text)));
}
/// <summary>
/// 通过标准汇率
/// </summary>
/// <param name="zongjing"></param>
/// <returns></returns>
public decimal GetBzMoney(decimal zongjing)
{
decimal C_huilv = Convert.ToDecimal(DBHelper.ExecuteScalar("select Rate from currency where id= StandardMoney"));
return(Convert.ToDecimal((Convert.ToDecimal(zongjing * C_huilv)).ToString("0.0000")));
}
/// <summary>
/// 返回系统最大期数
/// </summary>
/// <returns></returns>
public static int GetMaxExpectNum()
{
string sql = @"select max(expectNum) from config";
return(Convert.ToInt32(DBHelper.ExecuteScalar(sql)));
}
//使用本地账户支付
public static int PayOrder(string number, string orderid, double aneed, double bneed, double cneed, double eneed, int lv, string remark)
{
int res = 0;
try
{
//判断订单
int ispay = Convert.ToInt32(DBHelper.ExecuteScalar("select DefrayState from memberorder where orderid='" + orderid + "'"));
if (ispay > 0)
{
return(1);
}
else
{
int maxexpt = ConfigDAL.GetMaxExpectNum();
SqlTransaction tran = null;
SqlConnection conn = null;
using (conn = DBHelper.SqlCon())
{
conn.Open();
tran = conn.BeginTransaction();
if (aneed > 0 || bneed > 0 || cneed > 0)
{
//修改会员账户
int r1 = DBHelper.ExecuteNonQuery(tran, "update memberinfo set pointAout=pointAOut+" + aneed + " ,pointBout=pointBout+" + bneed + ", ,pointCout=pointCout+" + cneed + " where number='" + number + "'");
//更新销毁字段
DBHelper.ExecuteNonQuery(tran, "update CoinPlant set CoinDestroy=CoinDestroy+" + aneed + " where CoinIndex='CoinA' ;update CoinPlant set CoinDestroy=CoinDestroy+" + bneed + " where CoinIndex='CoinB' ;update CoinPlant set CoinDestroy=CoinDestroy+" + cneed + " where CoinIndex='CoinC' ; ");
if (r1 == 0)
{
tran.Rollback();
}
}
string ddremark = "支付订单:";
//插入对账单
if (aneed > 0)
{
ddremark += "A币支付 " + aneed;
int c = D_AccountDAL.AddAccount("A", number, aneed, D_AccountSftype.MemberType, D_AccountKmtype.Declarations, DirectionEnum.AccountReduced, "购买矿机支付", tran);
if (c == 0)
{
tran.Rollback();
}
}
if (bneed > 0)
{
ddremark += " B币支付 " + bneed;
int c = D_AccountDAL.AddAccount("B", number, bneed, D_AccountSftype.MemberType, D_AccountKmtype.Declarations, DirectionEnum.AccountReduced, "购买矿机支付,订单号" + orderid, tran);
if (c == 0)
{
tran.Rollback();
}
}
if (cneed > 0)
{
ddremark += " C币支付 " + cneed;
int c = D_AccountDAL.AddAccount("C", number, cneed, D_AccountSftype.MemberType, D_AccountKmtype.Declarations, DirectionEnum.AccountReduced, "购买矿机支付,订单号" + orderid, tran);
if (c == 0)
{
tran.Rollback();
}
}
int rr = 0;
//修改订单状态
rr = DBHelper.ExecuteNonQuery(tran, "update memberorder set DefrayState=1 ,PayExpectNum=" + maxexpt + " ,remark='" + remark + "' where orderid='" + orderid + "' ");
double ttpv = Convert.ToDouble(DBHelper.ExecuteScalar(tran, "select isnull(sum(totalpv),0) from memberorder where orderid='" + orderid + "' ", CommandType.Text));
if (ttpv == 0)
{
DBHelper.ExecuteNonQuery(tran, "update memberinfo set levelint=1 where number='" + number + "' ; update memberinfobalance1 set level=1 where number='" + number + "' ");
DBHelper.ExecuteNonQuery(tran, "update memberorder set DefrayState=1 ,PayExpectNum=" + maxexpt + " ,remark='" + remark + "',isactive=1,activeExpectNum=" + maxexpt + " where orderid='" + orderid + "' ");
}
if (rr == 1)
{
tran.Commit();
res = 1;
}
else
{
tran.Rollback();
}
}
}
}
catch (Exception ee)
{
return(res);
}
return(res);
}
public static int payOrderEcoin(string number, string orderid, double neede, string remark)
{
int res = 0;
if (neede > 0)
{
try
{
int maxexpt = ConfigDAL.GetMaxExpectNum();
//判断订单是否支付 未激活
int ckorder = Convert.ToInt32(DBHelper.ExecuteScalar("select isnull(count(0),0) from memberorder where orderid='" + orderid + "' and DefrayState=1 and isactive =0 "));
if (ckorder == 1)
{
//判断E币是否足够支付
double blac = Convert.ToDouble(DBHelper.ExecuteScalar("select pointEin -pointEout from memberinfo where number='" + number + "'"));
if (neede > blac)
{
return(-1); //余额不足
}
// 扣除对应 E币
SqlTransaction tran = null;
SqlConnection conn = null;
using (conn = DBHelper.SqlCon())
{
conn.Open();
tran = conn.BeginTransaction();
int r1 = DBHelper.ExecuteNonQuery(tran, "update memberinfo set pointEout=pointEout+" + neede + " where number='" + number + "'");
if (r1 == 0)
{
tran.Rollback();
}
int c = D_AccountDAL.AddAccount("E", number, neede, D_AccountSftype.MemberType, D_AccountKmtype.Declarations, DirectionEnum.AccountReduced, remark, tran);
if (c == 0)
{
tran.Rollback();
}
//更新订单 激活业绩
//修改订单状态
int rr = DBHelper.ExecuteNonQuery(tran, "update memberorder set isactive=1 ,activeExpectNum=" + maxexpt + " ,remark=remark+'，会员支付E币激活' where orderid='" + orderid + "' ");
double ttpv = Convert.ToDouble(DBHelper.ExecuteScalar(tran, "select isnull(sum(totalpv),0) from memberorder where number='" + number + "' and DefrayState=1 and isactive=1 and ordertype in(23,24) ", CommandType.Text));
int lv = 1;
switch (ttpv)
{
case 50:
lv = 2;
break;
case 100:
lv = 3;
break;
case 500:
lv = 4;
break;
case 1000:
lv = 5;
break;
case 3000:
lv = 6;
break;
case 10000:
lv = 7;
break;
}
//修改会员账户
int r = DBHelper.ExecuteNonQuery(tran, "update memberinfo set levelint=" + lv + " where number='" + number + "' ");
DBHelper.ExecuteNonQuery(tran, "update memberinfobalance" + maxexpt + " set level =" + lv + " where number='" + number + "' ");
if (r == 0)
{
tran.Rollback();
}
double cupv = Convert.ToDouble(DBHelper.ExecuteScalar(tran, "select totalpv from memberorder where orderid='" + orderid + "' and DefrayState=1 and isactive=1 ", CommandType.Text));
if (cupv > 0)
{
//计算业绩
SqlParameter[] sp = new SqlParameter[] {
new SqlParameter("@Number", number),
new SqlParameter("@CurrentOneMark", cupv),
new SqlParameter("@ExpectNum", maxexpt)
};
DBHelper.ExecuteNonQuery(tran, "js_addnew_PV", sp, CommandType.StoredProcedure);
}
if (rr == 1)
{
tran.Commit();
res = 1;
}
else
{
tran.Rollback();
}
}
}
}
catch (Exception)
{
res = -2;
}
}
return(res);
}
/// <summary>
/// Initialize values
/// </summary>
public void Initialize()
{
ConnectionParts();
string existsMyData = string.Format("SELECT IF(EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '{0}'), 'Yes','No')", database);
try {
string result = (string)(_dbHelper.ExecuteScalar(existsMyData));
if (result.Length > 0)
{
if (result == "Yes")
{
_dbexists = true;
}
else
{
string createMySql = string.Format("CREATE DATABASE IF NOT EXISTS {0}", database);
_dbHelper.ExecuteNonQuery(createMySql);
Restore();
}
}
}
catch {
//InstallerHelper.InstallHelper ();
_dbexists = false;
}
#region Old
//server = Properties.Settings.Default.server.ToString (); //"localhost"
//database = "projectdb";
//uid = "root";
//password = "******";
//string connectionString = "SERVER=" + server + ";" + "UID=" + uid + ";" + "PASSWORD="******";" + "Character Set=cp1251";
//try {
// connection = new MySqlConnection ( connectionString );
// string existsMyData = string.Format ( "SELECT IF(EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '{0}'), 'Yes','No')", database );
// using ( MySqlCommand existscomm = new MySqlCommand ( existsMyData, connection ) ) {
// existscomm.Connection.Open ();
// string report = existscomm.ExecuteScalar ().ToString ();
// existscomm.Connection.Close ();
// if ( report.Length > 0 ) {
// if ( report == "Yes" ) {
// _dbexists = true;
// //MessageBox.Show ( "Database exists!" );
// } else {
// string createMySql = string.Format ( "CREATE DATABASE IF NOT EXISTS {0}", database );
// using ( MySqlCommand createcomm = new MySqlCommand ( createMySql, connection ) ) {
// createcomm.Connection.Open ();
// createcomm.ExecuteNonQuery ();
// createcomm.Connection.Close ();
// }
// //MessageBox.Show ( "Database created!" );
// Restore ();
// }
// }
// }
// connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD="******";" + "Character Set=cp1251";
// connection.ConnectionString = connectionString;
//}
//catch {
// InstallerHelper.InstallHelper ();
// _dbexists = false;
//}
#endregion
}
/// <summary>
/// 查询报单个数
/// </summary>
/// <param name="orderid"></param>
/// <returns></returns>
public static int GetOrderCount(string orderid)
{
return(Convert.ToInt32(DBHelper.ExecuteScalar("select count(0) from memberorder where orderid=@orderid ", new SqlParameter[] { new SqlParameter("@orderid", orderid) }, CommandType.Text)));
}
/// <summary>
/// 根据单据类型编号查询单据名称
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string GetDocNameByDocTypeID(string id)
{
return(DBHelper.ExecuteScalar("GetDocNameByDocTypeID", new SqlParameter("@id", id), CommandType.StoredProcedure).ToString());
}
/// <summary>
/// 查询换货后店铺金额
/// </summary>
/// <param name="returnid"> 换货单号 </param>
/// <returns></returns>
public static double GetGoodsReturnMoney(string returnid)
{
return(Convert.ToDouble(DBHelper.ExecuteScalar("select isnull((sum(outquantity*price)-sum(inquantity*price)),0) from ReplacementDetail where displaceorderid=@orderid", new SqlParameter[] { new SqlParameter("@orderid", returnid) }, CommandType.Text)));
}
/// <summary>
/// 验证店铺编号是否足够
/// </summary>
/// <param name="DisplaceOrderId"></param>
/// <returns></returns>
public static bool CheckStoreMoney(string DisplaceOrderId, string storeID)
{
//检查店铺余额
SqlParameter[] para2 = new SqlParameter[] { new SqlParameter("@StoreID", storeID), new SqlParameter("@DisplaceOrderID", DisplaceOrderId) };
return(Convert.ToDouble(DBHelper.ExecuteScalar("CheckMoneyWheatherEnough", para2, CommandType.StoredProcedure)) < 0);
}
/// <summary>
/// 开始/结束 编号
/// </summary>
/// <param name="begincard"></param>
/// <param name="endcard"></param>
/// <returns></returns>
public static int BetweenCard(int begincard, int endcard)
{
return(Convert.ToInt32(DBHelper.ExecuteScalar("SELECT COUNT(*) FROM MemberInfo WHERE VIPCard>=" + begincard + " AND VIPCard<=" + endcard)));
}
/// <summary>
/// 根据报单编号，获取店铺编号
/// </summary>
/// <param name="orderid">报单编号</param>
/// <returns>店铺编号</returns>
public static string GetStoreIdByOrderId(string orderid)
{
return(DBHelper.ExecuteScalar("select storeid from memberorder where orderid=@orderid ", new SqlParameter[] { new SqlParameter("@orderid", orderid) }, CommandType.Text).ToString());
}
/// <summary>
/// 根据币种类型获取汇率
/// </summary>
/// <param name="id">币种ID</param>
/// <returns>汇率</returns>
public static double GetBzHl(int id)
{
return(Convert.ToDouble(DBHelper.ExecuteScalar("select rate from currency where id=@id", new SqlParameter[] { new SqlParameter("@id", id) }, CommandType.Text)));
}
/// <summary>
/// 根据编号获取名称
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static string GetDocTypeNameByID(int id)
{
return(Convert.ToString(DBHelper.ExecuteScalar("GetDocTypeNameById", new SqlParameter("@id", id), CommandType.StoredProcedure)));
}
/// <summary>
/// 获取系统标准币种
/// </summary>
/// <param name="countriName"></param>
/// <returns></returns>
public static int GetCurrenT()
{
string SQL = "select distinct(standardmoney) from currency";
return(Convert.ToInt32(DBHelper.ExecuteScalar(SQL, CommandType.Text).ToString()));
}
/// <summary>
/// 登录判断
/// </summary>
/// <param name="Account"></param>
/// <param name="Pwd"></param>
/// <returns></returns>
public int Login(string Account, string Pwd)
{
return((int)DBHelper.ExecuteScalar($"select 1 from UserInfo where UserAccount='{Account}' and UserPwd='{Pwd}'"));
}
//
//登陆
public int Login(Users users)
{
return((int)DBHelper.ExecuteScalar(string.Format("select * from Users where Uname='{0}' and Upwd='{1}'", users.Uname, users.Upwd)));
}
/// <summary>
/// 根据店铺获取库存实际数量
/// </summary>
/// <param name="proid"></param>
/// <param name="storeid"></param>
/// <returns></returns>
public static int GetCountByProIdAndStoreId(int proid, string storeid)
{
return(Convert.ToInt32(DBHelper.ExecuteScalar("select actualstorage from stock where storeid=@storeid and productid=@proid", new SqlParameter[2] {
new SqlParameter("@storeid", storeid), new SqlParameter("@proid", proid)
}, CommandType.Text)));
}
/// <summary>
/// 添加黑名单组到数据库
/// </summary>
/// <param name="blackGroup">黑名单组</param>
/// <param name="operateIP">操作人IP</param>
/// <param name="operateNum">操作人编号</param>
/// <returns></returns>
public static int AddBlackGroup(BlackGroupModel blackGroup, string operateIP, string operateNum)
{
if (blackGroup.IntGroupType == 3)
{
//保存区域黑名单组
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@GroupValue", blackGroup.IntGroupValue),
new SqlParameter("@operateIP", operateIP),
new SqlParameter("@operateNum", operateNum)
};
object obj = null;
//保存店辖黑名单组
try
{
obj = DBHelper.ExecuteScalar("AddAreaBlackGroup", paras, CommandType.StoredProcedure);
}
catch (SqlException)
{
return(-2);
}
return(1);
}
else if (blackGroup.IntGroupType == 4)
{
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@GroupValue", blackGroup.IntGroupValue),
new SqlParameter("@operateIP", operateIP),
new SqlParameter("@operateNum", operateNum)
};
object obj = null;
//保存店辖黑名单组
try
{
obj = DBHelper.ExecuteScalar("AddStoreBlackGroup", paras, CommandType.StoredProcedure);
}
catch (SqlException)
{
return(-2);
}
//if (obj != null)
//{
// return Convert.ToInt32(obj.ToString());
//}
//else
//{
// return -3;
//}
return(1);
}
else if (blackGroup.IntGroupType == 5 || blackGroup.IntGroupType == 6)
{
//保存安置网络黑名单组
//保存推荐网络黑名单组
object obj = null;
int groupid = 0;
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@GroupValue", blackGroup.IntGroupValue),
new SqlParameter("@GroupType", blackGroup.IntGroupType)
};
SqlConnection conn = new SqlConnection(DBHelper.connString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
string sql = "insert into blackgroup values(@GroupValue,@GroupType)";
DBHelper.ExecuteNonQuery(sql, paras, CommandType.Text);
paras = new SqlParameter[] {
new SqlParameter("@GroupValue", blackGroup.IntGroupValue),
new SqlParameter("@GroupType", blackGroup.IntGroupType)
};
sql = "select id from BlackGroup where GroupValue=@GroupValue and GroupType=@GroupType";
obj = DBHelper.ExecuteScalar(sql, paras, CommandType.Text);
if (obj == null)
{
tran.Rollback();
return(-2);
}
groupid = int.Parse(obj.ToString());
AddBlackList(tran, blackGroup.IntGroupValue, blackGroup.IntGroupType, operateIP, operateNum, groupid);
///////限制该会员登录
BlacklistModel blackListModel = new BlacklistModel();
blackListModel.UserType = 0;
blackListModel.UserID = blackGroup.IntGroupValue;
blackListModel.OperateBH = operateNum;
blackListModel.OperateIP = operateIP;
blackListModel.GroupID = groupid;
blackListModel.BlackDate = DateTime.Now.ToUniversalTime();
AddBlackList(tran, blackListModel, "AddBlackList");
tran.Commit();
}
catch (SqlException)
{
tran.Rollback();
DelBlackGroup(groupid, blackGroup.IntGroupType);
return(-2);
}
finally
{
conn.Close();
}
return(1);
}
else
{
//数据异常
return(-3);
}
}
public int CountSaishi()
{
string sql = "select count(*) from saishi";
return(DBHelper.ExecuteScalar <int>(sql));
}
/// <summary>
/// 获取安置人注册期数
/// </summary>
/// <param name="number"></param>
/// <returns></returns>
public static int SelectAZQiShu(string number)
{
return(Convert.ToInt32(DBHelper.ExecuteScalar("select expectnum from memberinfo where number=(select placement from memberinfo where number=@num)", new SqlParameter[] { new SqlParameter("@num", number) }, CommandType.Text)));
}
/// <summary>
/// 检查用户是否在黑名单列表中的登陆状态
/// </summary>
/// <param name="userid">用户编号:包括管理员、店铺、会员</param>
/// <param name="usertype">类别：0会员，1店铺，2管理员</param>
/// <returns>返回true，禁止登陆；false，允许登陆</returns>
public static bool CheckBlacklistLogin(string userid, int usertype, string UserAddress)
{
ArrayList list = new ArrayList();
string[] SecPostion = UserAddress.Split('.');
string strIP = "";
strIP = SecPostion[0];
SqlDataReader dr = DBHelper.ExecuteReader("select userid from Blacklist where usertype=3 and userid like '" + strIP + ".%'");
while (dr.Read())
{
list.Add(new ListItem(dr[0].ToString()));
}
dr.Close();
foreach (ListItem al in list)
{
string[] userIP = al.Value.Split('.');
string PiPei = "";
string addressIP = "";
string PiPei1 = "";
string addressIP1 = "";
for (int i = 0; i < 4; i++)
{
if (userIP[i].ToString() != "*")
{
PiPei += userIP[i].ToString() + ".";
addressIP += SecPostion[i].ToString() + ".";
}
else
{
for (int j = 0; j < i; j++)
{
PiPei1 += userIP[j].ToString() + ".";
addressIP1 += SecPostion[j].ToString() + ".";
}
if (PiPei1 == addressIP1)
{
return(true);
}
}
}
if (PiPei == addressIP)
{
return(true);
}
}
SqlParameter[] parms = new SqlParameter[2];
parms[0] = new SqlParameter("@UserID", userid);
parms[1] = new SqlParameter("@UserType", usertype);
Object objResult = DBHelper.ExecuteScalar("CheckBlacklistLogin", parms, CommandType.StoredProcedure);
if (objResult != null)
{
try
{
if ((int)objResult > 0)
{
return(true);
}
}
catch (Exception ex)
{
HttpContext.Current.Response.Write(ex.ToString());
}
}
return(false);
}
/// <summary>
/// 获取最大的权限编号
/// </summary>
/// <returns>返回最大的权限编号</returns>
public static int GetMaxWareControlFromWareHouse()
{
return(Convert.ToInt32(DBHelper.ExecuteScalar("GetMaxWareControlFromWareHouse", CommandType.StoredProcedure)));
}
/// <summary>
/// 获得最大值
/// </summary>
/// <returns></returns>
public int MaxCard()
{
return(Convert.ToInt32(DBHelper.ExecuteScalar("SELECT ISNULL(MAX(EndCard),0) FROM VIPcardrange")));
}
/// <summary>
/// 登录用户
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
public int Login(Users m)
{
string sql = $"select count(1) from Users where Name='{m.Name}'and Email='{m.Email}'";
return((int)DBHelper.ExecuteScalar(sql));
}
//根据换货单号得到换货的数量
/// <summary>
/// 验证公司库存
/// </summary>
/// <param name="DisplaceOrderId">换货单号</param>
/// <returns></returns>
public static bool CheckCompanyQuantity(string DisplaceOrderId)
{
//检查公司库存
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@DisplaceOrderID", DisplaceOrderId) };
return(Convert.ToInt32(DBHelper.ExecuteScalar("CheckCompanyGreaterThanOderQuantity", para, CommandType.StoredProcedure)) > 0);
}