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));
}