public String ChangePasswordByAccount(String account, String newPass) { // string sql = @"update [dbo].[User] set password = @newP where account = @acct"; DbCommand cmd = base.GetDbCommandObject(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; DBUtility db = new DBUtility(); try { if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } db.NewParaWithValue("newP", DbType.String, newPass, ref cmd); db.NewParaWithValue("acct", DbType.String, account, ref cmd); cmd.ExecuteNonQuery(); } catch (Exception e) { throw e; } finally { cmd.Dispose(); } StringBuilder sb = new StringBuilder(); sb.Append("user account:").Append(account); BusinessUtility bu = new BusinessUtility(); bu.WriteDataChangeLog("U", "User.PASSWORD", sb.ToString(), "", "自助"); return("done"); }
public void DeleteOrder(string ordID) { string sql = @"delete from order_head where order_id = @ordID"; DbCommand cmd = base.GetDbCommandObject(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; DBUtility db = new DBUtility(); try { if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } db.NewParaWithValue("ordID", DbType.String, ordID, ref cmd); cmd.ExecuteNonQuery(); } catch (Exception e) { throw e; } finally { cmd.Dispose(); } StringBuilder sb = new StringBuilder(); sb.Append("order id:").Append(ordID); BusinessUtility bu = new BusinessUtility(); bu.WriteDataChangeLog("D", "ORDER", sb.ToString(), "", "自助"); }
public void UpdateOrderStatus(string ordID, string statusTo, string userID, string remark = "") { string sql = @"begin update order_head set order_status = @stat, [remark] = @remark where order_id = @ordID insert into order_status_log(order_id, order_status_to, changed_by) values(@ordID, @stat, @usrid) end"; DbCommand cmd = base.GetDbCommandObject(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; DBUtility db = new DBUtility(); try { if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } db.NewParaWithValue("stat", DbType.String, statusTo, ref cmd); db.NewParaWithValue("ordID", DbType.String, ordID, ref cmd); db.NewParaWithValue("usrid", DbType.String, userID, ref cmd); db.NewParaWithValue("remark", DbType.String, remark, ref cmd); cmd.ExecuteNonQuery(); } catch (Exception e) { throw e; } finally { cmd.Dispose(); } StringBuilder sb = new StringBuilder(); sb.Append("order id:").Append(ordID) .Append(";order status:").Append(statusTo); BusinessUtility bu = new BusinessUtility(); bu.WriteDataChangeLog("U", "ORDER", sb.ToString(), "", (userID == "" ? "-" : userID)); }
public void Add(Customer entity) { int newCustID = GetNextCustID(); string sqlCust = @" insert into customer( customer_id, customer_name, contacts, contact_tel, contact_mob, cust_address, contact_number, Cust_type, advance_amount, credit, account_method, payment_method, bank, bank_account ) values( @customer_id, @customer_name, @contacts, @contact_tel, @contact_mob, @cust_address, @contact_number, @cust_type, @advance_amount, @credit, @account_method, @payment_method, @bank, @bank_account )"; DBUtility db = new DBUtility(); DbCommand cmd = base.GetDbCommandObject(); cmd.CommandText = sqlCust; db.NewParaWithValue("customer_id", DbType.String, newCustID.ToString(), ref cmd); db.NewParaWithValue("customer_name", DbType.String, entity.Customer_Name, ref cmd); db.NewParaWithValue("contacts", DbType.String, entity.Contacts, ref cmd); db.NewParaWithValue("contact_tel", DbType.String, entity.Contact_Tel, ref cmd); db.NewParaWithValue("contact_mob", DbType.String, entity.Contact_Mob, ref cmd); db.NewParaWithValue("cust_address", DbType.String, entity.Customer_Addr, ref cmd); db.NewParaWithValue("contact_number", DbType.String, entity.Contract_Num, ref cmd); db.NewParaWithValue("Cust_type", DbType.String, entity.Customer_Type, ref cmd); db.NewParaWithValue("advance_amount", DbType.Decimal, entity.Advance_Amt, ref cmd); db.NewParaWithValue("credit", DbType.Decimal, entity.Credit, ref cmd); db.NewParaWithValue("account_method", DbType.String, entity.Amount_Method, ref cmd); db.NewParaWithValue("payment_method", DbType.String, entity.Payment_Method, ref cmd); db.NewParaWithValue("bank", DbType.String, entity.Bank, ref cmd); db.NewParaWithValue("bank_account", DbType.String, entity.Bank_Acct, ref cmd); string sqlUser = @" begin insert into [user]( id, Account, Password, Name, Sex, Status, Type, CreateTime, CrateId, customer_id ) values( newid(), @Account, @Password, @Name, 0, 0, 0, getdate(), NULL, @customer_id ) insert into Relevance(Id, [Description], [Key], [Status], OperateTime, OperatorId, FirstId, SecondId) select newid(), '', 'UserRole', 0, getdate(), 0, u.Id, r.Id from [user] u, [role] r where u.Account = @Account and r.name = '外部客户下单' end"; DbTransaction dt = null; try { if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } dt = cmd.Connection.BeginTransaction(); cmd.Transaction = dt; cmd.ExecuteNonQuery(); cmd.CommandText = sqlUser; cmd.Parameters.Clear(); db.NewParaWithValue("Account", DbType.String, entity.User_Account, ref cmd); db.NewParaWithValue("Password", DbType.String, entity.User_Password, ref cmd); db.NewParaWithValue("Name", DbType.String, entity.User_Name, ref cmd); db.NewParaWithValue("customer_id", DbType.String, newCustID.ToString(), ref cmd); cmd.ExecuteNonQuery(); dt.Commit(); } catch (Exception e) { if (dt != null) { dt.Rollback(); } throw; } finally { if (dt != null) { dt = null; } cmd.Dispose(); } //System.Diagnostics.Debug.WriteLine("1111111"); StringBuilder sb = new StringBuilder(); sb.Append("user id:").Append(entity.User_Account) .Append(";user name:").Append(entity.User_Name); BusinessUtility bu = new BusinessUtility(); bu.WriteDataChangeLog("A", "USER", sb.ToString(), "", "自助"); }
public void SaveOrderCatch(OrderHeader view) { string ord_id = GetNextOrderNumber(); string sqlH = @"insert into order_head( order_id, customer_id, customer_name, contacts, contact_tel, contact_address, order_date, contract_id, sales_name )values( '" + ord_id + @"', @customer_id, @customer_name, @contacts, @contact_tel, @contact_address, @order_date, @contract_id, @sales_name )"; string sqlD = @"insert into order_details( order_line_id, order_id, category_id, quantity, unit_uom, unit_quantity, unit_price, amount, spec, service_item, storage ) values( @order_line_id, '" + ord_id + @"', @category_id, @quantity, @unit_uom, @unit_quantity, @unit_price, @amount, @spec, @service_item, @storage )"; DbCommand cmd = base.GetDbCommandObject(); DbTransaction dt = null; cmd.CommandText = sqlH; cmd.CommandType = CommandType.Text; DBUtility db = new DBUtility(); try { if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } dt = cmd.Connection.BeginTransaction(); cmd.Transaction = dt; db.NewParaWithValue("customer_id", DbType.String, view.Customer_id, ref cmd); db.NewParaWithValue("customer_name", DbType.String, view.Customer_name, ref cmd); db.NewParaWithValue("contacts", DbType.String, view.Contacts, ref cmd); db.NewParaWithValue("contact_tel", DbType.String, view.Contact_tel, ref cmd); db.NewParaWithValue("contact_address", DbType.String, view.Contact_address, ref cmd); db.NewParaWithValue("order_date", DbType.DateTime, view.Order_date, ref cmd); db.NewParaWithValue("contract_id", DbType.String, view.Contract_id == null ? DBNull.Value.ToString() : view.Contract_id, ref cmd); db.NewParaWithValue("sales_name", DbType.String, view.Sales_name == null ? DBNull.Value.ToString() : view.Sales_name, ref cmd); cmd.ExecuteNonQuery(); foreach (OrderDetail od in view.orderDetail) { cmd.Parameters.Clear(); cmd.CommandText = sqlD; db.NewParaWithValue("order_line_id", DbType.String, od.Order_line_id, ref cmd); db.NewParaWithValue("category_id", DbType.String, od.Category_id == null ? DBNull.Value.ToString() : od.Category_id, ref cmd); db.NewParaWithValue("quantity", DbType.Decimal, od.Quantity, ref cmd); db.NewParaWithValue("unit_uom", DbType.String, od.Qnit_uom == null ? DBNull.Value.ToString() : od.Qnit_uom, ref cmd); db.NewParaWithValue("unit_quantity", DbType.Decimal, od.Unit_quantity == null ? 0 : od.Unit_quantity, ref cmd); db.NewParaWithValue("unit_price", DbType.Decimal, od.Unit_price, ref cmd); db.NewParaWithValue("amount", DbType.Decimal, od.Amount, ref cmd); db.NewParaWithValue("spec", DbType.String, od.Spec == null ? DBNull.Value.ToString() : od.Spec, ref cmd); db.NewParaWithValue("service_item", DbType.String, od.Service_item, ref cmd); db.NewParaWithValue("storage", DbType.Decimal, od.Storage, ref cmd); cmd.ExecuteNonQuery(); } dt.Commit(); } catch (Exception e) { if (dt != null) { dt.Rollback(); } throw; } finally { if (dt != null) { dt.Dispose(); } cmd.Dispose(); } StringBuilder sb = new StringBuilder(); sb.Append("order id:").Append(view.Order_id) .Append(";order tel:").Append(view.Contact_tel) .Append(";order address:").Append(view.Contact_address) .Append(";quantity:").Append(view.orderDetail[0].Quantity) .Append(";unit price:").Append(view.orderDetail[0].Unit_price) .Append(";amount:").Append(view.orderDetail[0].Amount); BusinessUtility bu = new BusinessUtility(); bu.WriteDataChangeLog("A", "ORDER", sb.ToString(), "", (view.Sales_name == "" ? "-" : view.Sales_name)); }
public void Update(OrderHeader view) { string sqlH = @"update order_head set customer_id = @customer_id, customer_name = @customer_name, contacts = @contacts, contact_tel = @contact_tel, contact_address = @contact_address where order_id = @order_id"; string sqlD = @"update order_details set quantity = @quantity, unit_price = @unit_price, amount = @amount, service_item = @service_item where order_id = @order_id and order_line_id = @order_line_id"; DbCommand cmd = base.GetDbCommandObject(); DbTransaction dt = null; cmd.CommandText = sqlH; cmd.CommandType = CommandType.Text; DBUtility db = new DBUtility(); try { if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } dt = cmd.Connection.BeginTransaction(); cmd.Transaction = dt; db.NewParaWithValue("customer_id", DbType.String, view.Customer_id, ref cmd); db.NewParaWithValue("customer_name", DbType.String, view.Customer_name, ref cmd); db.NewParaWithValue("contacts", DbType.String, view.Contacts, ref cmd); db.NewParaWithValue("contact_tel", DbType.String, view.Contact_tel, ref cmd); db.NewParaWithValue("contact_address", DbType.String, view.Contact_address, ref cmd); db.NewParaWithValue("order_id", DbType.String, view.Order_id, ref cmd); cmd.ExecuteNonQuery(); foreach (OrderDetail od in view.orderDetail) { cmd.Parameters.Clear(); cmd.CommandText = sqlD; db.NewParaWithValue("order_id", DbType.String, view.Order_id, ref cmd); db.NewParaWithValue("order_line_id", DbType.String, od.Order_line_id, ref cmd); db.NewParaWithValue("quantity", DbType.Decimal, od.Quantity, ref cmd); db.NewParaWithValue("unit_price", DbType.Decimal, od.Unit_price, ref cmd); db.NewParaWithValue("amount", DbType.Decimal, od.Amount, ref cmd); db.NewParaWithValue("service_item", DbType.String, od.Service_item, ref cmd); cmd.ExecuteNonQuery(); } dt.Commit(); } catch (Exception e) { if (dt != null) { dt.Rollback(); } throw; } finally { if (dt != null) { dt.Dispose(); } cmd.Dispose(); } StringBuilder sb = new StringBuilder(); sb.Append("order id:").Append(view.Order_id) .Append(";order tel:").Append(view.Contact_tel) .Append(";order address:").Append(view.Contact_address) .Append(";quantity:").Append(view.orderDetail[0].Quantity) .Append(";unit price:").Append(view.orderDetail[0].Unit_price) .Append(";amount:").Append(view.orderDetail[0].Amount); BusinessUtility bu = new BusinessUtility(); bu.WriteDataChangeLog("U", "ORDER", sb.ToString(), "", (view.Sales_name == "" ? "-" : view.Sales_name)); }