//删除订单
public int OrderDel(int oid)
{
return(DBHelper.ExecuteNonQuery("delete from Orderfood where Ouid=" + oid));
}
public int UptDept1(Dept d)
{
string str = $"Update Dept set IsOK=1 where DeptId='{d.DeptId}'";
return(DBHelper.ExecuteNonQuery(str));
}
/// <summary>
/// 修改用户信息
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
public int AlterUsers(Users m)
{
string str = $"update Users set Name='{m.Name}',Pwd='123',Email='{m.Email}'where Uid={m.Uid}";
return(DBHelper.ExecuteNonQuery(str));
}
/// <summary>
/// 修改部门信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool UpdateDepartmentInfo(Model.Department model)
{
string sql = "update Department set DeptName='" + model.DeptName + "',ManagerID='" + model.ManagerID + "',DeptInfo='" + model.DeptInfo + "' where DeptID ='" + model.DeptID + "'";
return(DBHelper.ExecuteNonQuery(sql, null));;
}
public int UptUser(UserInfo u)
{
string str = $"Update UserInfo set IsOK=0 where UserId='{u.UserId}' and UserName<>'zk'";
return(DBHelper.ExecuteNonQuery(str));
}
/// <summary>
/// 插入方法
/// </summary>
/// <param name="Users">Users表实例</param>
/// <returns>int</returns>
public int Insert(Users UsersExample)
{
DBHelper.sqlstr = "insert into Users (Account,Password,LoginTime,State,Name,Birthday,Phone,Address,Remark,Sex)values(@Account,@Password,@LoginTime,@State,@Name,@Birthday,@Phone,@Address,@Remark,@Sex)";
return(DBHelper.ExecuteNonQuery(GetSqlParameters(UsersExample)));
}
public int AddStudent(Student s)
{
string sql = $"insert into Student values('{s.Sname}',{s.Sage},'{s.Simg}')";
return(DBHelper.ExecuteNonQuery(sql));
}
public int AddShopCar(Shopping shop)
{
string sql = string.Format("insert into Shopping values('{0}','{1}','{2}')", shop.SNum, shop.SState, shop.GId);
return(DBHelper.ExecuteNonQuery(sql));
}
public int UpdateShopCar(Shopping shop)
{
string sql = string.Format("update Shopping set SState = 1 where SId = '{0}'", shop.SId);
return(DBHelper.ExecuteNonQuery(sql));
}
//生成订单
public int Lineitem(int oid)
{
return(DBHelper.ExecuteNonQuery("update Lineitem set Ltoid=" + oid));
}
//删除订单详情
public int lineitemDel(int lid)
{
return(DBHelper.ExecuteNonQuery("delete from Lineitem where Ltid=" + lid));
}
//添加订单
public int OrderAdd(Orderfood orderfood)
{
orderfood.Onumber = DateTime.Now.ToString("yyyyMMddhhmmsss");
return(DBHelper.ExecuteNonQuery(string.Format("insert into Orderfood values('{0}','{1}','{2}')", orderfood.Onumber, orderfood.Odate, orderfood.Ouid)));
}
//点餐
public int LineitemAdd(Lineitem lineitem)
{
return(DBHelper.ExecuteNonQuery(string.Format("insert into Lineitem values ('{0}','{1}','{2}','{3}',0)", lineitem.Ltmid, lineitem.Lttid, lineitem.Ltnum, lineitem.Ltmoney)));
}
//注册
public int Register(Users users)
{
return(DBHelper.ExecuteNonQuery(string.Format("insert into Users values('{0}','{1}','1')", users.Uname, users.Upwd)));
}
/// <summary>
/// 插入方法
/// </summary>
/// <param name="Signs">Signs表实例</param>
/// <returns>int</returns>
public int Insert(Signs SignsExample)
{
DBHelper.sqlstr = "insert into Signs (SignTime,Remark,UId,UName,UState,UType)values(@SignTime,@Remark,@UId,@UName,@UState,@UType)";
return(DBHelper.ExecuteNonQuery(GetSqlParameters(SignsExample)));
}
public int AddTallyOrder(Market m)
{
string sql = string.Format("insert into Market values('{0}','{1}','{2}','{3}')", m.MCard, m.MNnum, m.MTime, m.ShopId);
return(DBHelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="Signs">Signs表实例</param>
/// <returns>int</returns>
public int Update(Signs SignsExample)
{
DBHelper.sqlstr = "update Signs set SignTime=@SignTime,Remark=@Remark,UId=@UId,UName=@UName,UState=@UState,UType=@UType where Id=" + SignsExample.Id;
return(DBHelper.ExecuteNonQuery(GetSqlParameters(SignsExample)));
}
public int UpdateKu(Goods goods)
{
string sql = string.Format("update Goods set GQuantity -='{0}' where GId='{1}'", goods.GQuantity, goods.GId);
return(DBHelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="Users">Users表实例</param>
/// <returns>int</returns>
public int Update(Users UsersExample)
{
DBHelper.sqlstr = "update Users set Account=@Account,Password=@Password,LoginTime=@LoginTime,State=@State,Name=@Name,Birthday=@Birthday,Phone=@Phone,Address=@Address,Remark=@Remark,Sex=@Sex where Id=" + UsersExample.Id;
return(DBHelper.ExecuteNonQuery(GetSqlParameters(UsersExample)));
}
/// <summary>
/// 插入方法
/// </summary>
/// <param name="yh_keys">yh_keys表实例</param>
/// <returns>int</returns>
public int Insert(yh_keys yh_keysExample)
{
DBHelper.sqlstr = "insert into yh_keys (ckid,k_standby1,k_standby2,k_standby3,kcishu,kdate,kdgbbsscs,kdianjilv,kdianjizhishu,kgoodsnum,kname,kqgbbcjcs,kremark,krenshu,kscdianjizhanbi,ksousuozhanbi,kstate,kzhitongche,kzhuanhualv)values(@ckid,@k_standby1,@k_standby2,@k_standby3,@kcishu,'" + yh_keysExample.kdate.ToString() + "',@kdgbbsscs,@kdianjilv,@kdianjizhishu,@kgoodsnum,@kname,@kqgbbcjcs,@kremark,@krenshu,@kscdianjizhanbi,@ksousuozhanbi,@kstate,@kzhitongche,@kzhuanhualv)";
return(DBHelper.ExecuteNonQuery(GetSqlParameter(yh_keysExample)));
}
/// <summary>
/// 新增部门信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool AddDepartmentInfo(Model.Department model)
{
string sql = "insert into Department values ('" + model.DeptName + "','" + model.ManagerID + "','" + model.DeptInfo + "')";
return(DBHelper.ExecuteNonQuery(sql, null));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="yh_keys">yh_keys表实例</param>
/// <returns>int</returns>
public int Update(yh_keys yh_keysExample)
{
DBHelper.sqlstr = "update yh_keys set ckid=@ckid,k_standby1=@k_standby1,k_standby2=@k_standby2,k_standby3=@k_standby3,kcishu=@kcishu,kdate='" + yh_keysExample.kdate.ToString() + "',kdgbbsscs=@kdgbbsscs,kdianjilv=@kdianjilv,kdianjizhishu=@kdianjizhishu,kgoodsnum=@kgoodsnum,kname=@kname,kqgbbcjcs=@kqgbbcjcs,kremark=@kremark,krenshu=@krenshu,kscdianjizhanbi=@kscdianjizhanbi,ksousuozhanbi=@ksousuozhanbi,kstate=@kstate,kzhitongche=@kzhitongche,kzhuanhualv=@kzhuanhualv where kid=" + yh_keysExample.kid;
return(DBHelper.ExecuteNonQuery(GetSqlParameter(yh_keysExample)));
}
/// <summary>
/// 删除部门信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool DelDepartmentInfo(Model.Department model)
{
string sql = " delete Department where DeptID ='" + model.DeptID + "'";
return(DBHelper.ExecuteNonQuery(sql, null));
}
/// <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 UptUser1(UserInfo u)
{
string str = $"Update UserInfo set IsOK=1 where UserId='{u.UserId}'";
return(DBHelper.ExecuteNonQuery(str));
}
public static int insertUserInfo(Entity.UserInfo UserInfo)
{
string sql = string.Format("insert into UserInfo(UserName,UserPwd,registerDay,Email,permission) values('{0}','{1}','{2}','{3}',3)", UserInfo.UserName, UserInfo.UserPwd, DateTime.Now.ToString(), UserInfo.Email);
return(DBHelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 注册用户
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
public int AddUsers(Users m)
{
string str = $"insert into Users values('{m.Name}','123','{m.Email}')";
return(DBHelper.ExecuteNonQuery(str));
}
public static bool AddBill(ref Bill model, out string errMsg)
{
errMsg = "";
bool result;
try
{
model.DriverName = model.DriverName.Trim();
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Car", model.Car),
new SqlParameter("@Instance", model.Instance),
new SqlParameter("@Project", model.Project),
new SqlParameter("@Department", model.Department),
new SqlParameter("@Oil", model.Oil),
new SqlParameter("@Volume", model.Volume),
new SqlParameter("@Mileage", model.Mileage),
new SqlParameter("@DriverName", model.DriverName),
new SqlParameter("@Signature", model.Signature),
new SqlParameter("@Rate", model.Rate),
new SqlParameter("@Oiler", model.Oiler),
new SqlParameter("@Time", model.Time),
new SqlParameter("@IsLost", model.IsLost),
new SqlParameter("@Creater", model.Creater),
new SqlParameter("@CreatedDate", model.CreatedDate),
new SqlParameter("@OutState", SqlDbType.Int),
new SqlParameter("@return", SqlDbType.Int)
};
para[15].Direction = ParameterDirection.Output;
para[16].Direction = ParameterDirection.ReturnValue;
DBHelper.ExecuteNonQuery(CommandType.StoredProcedure, "proc_BillInsert", para);
int outState = int.Parse(para[15].Value.ToString());
int returnValue = int.Parse(para[16].Value.ToString());
if (returnValue > 0)
{
model.Id = returnValue;
EventLogService.AddEventLog <Bill>(new EventLog
{
TargetIds = returnValue.ToString(),
CodeTag = "AddBill",
LogName = "添加加油单"
}, model);
result = true;
}
else
{
switch (outState)
{
case -100:
errMsg = "该车辆下对应加油申请单已结束，不能加油";
break;
case -200:
errMsg = "该车辆下对应加油申请单没有通过审核或被审核员中止，不能加油";
break;
case -300:
errMsg = "该车辆下对应加油申请单已被暂停，不能加油";
break;
case -400:
errMsg = "该车辆下对应加油申请单可加油时间不在当前时间范围，不能加油";
break;
case -500:
errMsg = "车辆里程数重复，不能重复提交相同里程数的加油单";
break;
case -1:
errMsg = "添加加油单失败";
break;
case -2:
errMsg = "更新申请单信息失败";
break;
case -3:
errMsg = "更新车辆信息失败";
break;
default:
errMsg = "异常错误";
break;
}
ErrorLog e = new ErrorLog();
e.TargetIds = model.Car.ToString();
e.CodeTag = "AddBill";
e.LogName = "添加加油单";
e.ErrorMsg = errMsg;
ErrorLogService.AddErrorLog <Model.Bill>(e, model);
result = false;
}
}
catch (Exception ex)
{
errMsg = ex.Message;
ErrorLogService.AddErrorLog <Bill>(new ErrorLog
{
TargetIds = "0",
CodeTag = "AddBill",
LogName = "添加加油单",
ErrorMsg = ex.Message.ToString()
}, model);
result = false;
}
return(result);
}
/// <summary>
/// 删除用户信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int DelUsers(string id)
{
string str = $"delete from Users where Uid in(" + id + ")";
return(DBHelper.ExecuteNonQuery(str));
}
/// <summary>
/// 填写退货退款单 ——ds2012——tianfeng
/// </summary>
/// <param name="DocID">订单号</param>
/// <param name="StoreID">店编号</param>
/// <returns></returns>
public static Boolean UPtInventoryDoc(string DocID, string StoreID, int flag, double money, string reason, Boolean bol, string opbh, string opip, int qishu)
{
SqlParameter[] parameter =
{
new SqlParameter("@DocID", SqlDbType.VarChar, 20),
new SqlParameter("@StoreID", SqlDbType.VarChar, 20),
new SqlParameter("@Flag", SqlDbType.Int),
new SqlParameter("@Charged", SqlDbType.Money, 20),
new SqlParameter("@Reason", SqlDbType.VarChar, 250),
new SqlParameter("@error", SqlDbType.VarChar, 20)
};
parameter[0].Value = DocID;
parameter[1].Value = StoreID;
parameter[2].Value = flag;
parameter[3].Value = money;
parameter[4].Value = reason;
parameter[5].Direction = ParameterDirection.Output;
double totalFmoney = GetInventoryTotalMoney(DocID);
using (SqlConnection conn = new SqlConnection(DBHelper.connString))
{
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction())
{
try
{
//修改
double Fmoney = GetInventoryMoney(tran, DocID);
string huidan = "HK" + Model.Other.MYDateTime.ToYYMMDDHHmmssString();
//判断汇单号是否存在:true存在,false不存在
bool isExist = RemittancesDAL.isMemberExistsHuiDan(huidan);
while (isExist)
{
huidan = "HK" + Model.Other.MYDateTime.ToYYMMDDHHmmssString();
isExist = RemittancesDAL.isMemberExistsHuiDan(huidan);
}
if (bol)
{
AddOrderDataDAL.AddDataTORemittances(tran, huidan, StoreID, (Fmoney * (-1)), DocID, opip, opbh, qishu);
DBHelper.ExecuteNonQuery(tran, "update storeinfo set totalaccountmoney=totalaccountmoney - @money where storeid=@storeid", new SqlParameter[2] {
new SqlParameter("@money", Fmoney), new SqlParameter("@storeid", StoreID)
}, CommandType.Text);
D_AccountDAL.AddStoreAccount(StoreID, Fmoney, D_AccountSftype.StoreDingHuokuan, S_Sftype.dianhuo, D_AccountKmtype.ReturnCharge, DirectionEnum.AccountReduced, "返回上次店铺退货单款" + DocID, tran);
DBHelper.ExecuteNonQuery(tran, "UPtInventoryDoc", parameter, CommandType.StoredProcedure);
AddOrderDataDAL.AddDataTORemittances(tran, "", StoreID, totalFmoney, DocID, opip, opbh, qishu);
DBHelper.ExecuteNonQuery(tran, "update storeinfo set totalaccountmoney=totalaccountmoney + @money where storeid=@storeid", new SqlParameter[2] {
new SqlParameter("@money", totalFmoney), new SqlParameter("@storeid", StoreID)
}, CommandType.Text);
D_AccountDAL.AddStoreAccount(StoreID, totalFmoney, D_AccountSftype.StoreDingHuokuan, S_Sftype.dianhuo, D_AccountKmtype.ReturnRebate, DirectionEnum.AccountsIncreased, "店铺退货单退款" + DocID, tran);
AddOrderDataDAL.AddDataTORemittances(tran, huidan, StoreID, (money * (-1)), DocID, opip, opbh, qishu);
DBHelper.ExecuteNonQuery(tran, "update storeinfo set totalaccountmoney=totalaccountmoney - @money where storeid=@storeid", new SqlParameter[2] {
new SqlParameter("@money", money), new SqlParameter("@storeid", StoreID)
}, CommandType.Text);
D_AccountDAL.AddStoreAccount(StoreID, money, D_AccountSftype.StoreDingHuokuan, S_Sftype.dianhuo, D_AccountKmtype.ReturnCharge, DirectionEnum.AccountReduced, "店铺退货单扣款" + DocID, tran);
}
else
{
//添加
DBHelper.ExecuteNonQuery(tran, "UPtInventoryDoc", parameter, CommandType.StoredProcedure);
AddOrderDataDAL.AddDataTORemittances(tran, huidan, StoreID, totalFmoney, DocID, opip, opbh, qishu);
DBHelper.ExecuteNonQuery(tran, "update storeinfo set totalaccountmoney=totalaccountmoney + @money where storeid=@storeid", new SqlParameter[2] {
new SqlParameter("@money", totalFmoney), new SqlParameter("@storeid", StoreID)
}, CommandType.Text);
D_AccountDAL.AddStoreAccount(StoreID, totalFmoney, D_AccountSftype.StoreDingHuokuan, S_Sftype.dianhuo, D_AccountKmtype.ReturnRebate, DirectionEnum.AccountsIncreased, "店铺退货单退款" + DocID, tran);
AddOrderDataDAL.AddDataTORemittances(tran, huidan, StoreID, (money * (-1)), DocID, opip, opbh, qishu);
DBHelper.ExecuteNonQuery(tran, "update storeinfo set totalaccountmoney=totalaccountmoney - @money where storeid=@storeid", new SqlParameter[2] {
new SqlParameter("@money", money), new SqlParameter("@storeid", StoreID)
}, CommandType.Text);
D_AccountDAL.AddStoreAccount(StoreID, money, D_AccountSftype.StoreDingHuokuan, S_Sftype.dianhuo, D_AccountKmtype.ReturnRebate, DirectionEnum.AccountReduced, "店铺退货单扣款" + DocID, tran);
}
tran.Commit();
}
catch (Exception ex1)
{
tran.Rollback();
return(false);
}
finally
{
conn.Close();
}
}
}
return(int.Parse(parameter[5].Value.ToString()) == 0 ? true : false);
}