public int UpdateStatus(int ID, int status)
{
string commandText = string.Format("update {0} set status = {1} where ID={2}",
tableName, status, ID);
return(DbHelperAccess.executeNonQuery(commandText));
}
public void UpdateCompanyInfo(string company, string address, string contract, string phone, string mobile, string bank, string other, string pic)
{
string commandText = string.Format("update Conf set conf='{0}' where ID=3", company);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("update Conf set conf='{0}' where ID=4", address);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("update Conf set conf='{0}' where ID=5", contract);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("update Conf set conf='{0}' where ID=6", phone);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("update Conf set conf='{0}' where ID=7", mobile);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("update Conf set conf='{0}' where ID=8", bank);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("update Conf set conf='{0}' where ID=9", other);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("update Conf set conf='{0}' where ID=14", pic);
DbHelperAccess.executeNonQuery(commandText);
}
public int UpdatePay(int ID, double pay, double payed)
{
string commandText = string.Format("update ProductCirculation set pay = {0}, payed={1} where ID={2}",
pay, payed, ID);
return(DbHelperAccess.executeNonQuery(commandText));
}
public void UpdateBaiscInfo(ProductCirculation info)
{
string commandText = string.Format("update ProductCirculation set code='{0}', circulationTime='{1}', comment='{2}', customerID={3}, operator='{4}' where ID={5}",
info.Code, info.CirculationTime, info.Comment, info.CustomerID <= 0 ? "null" : info.CustomerID.ToString(), info.Oper, info.ID);
DbHelperAccess.executeNonQuery(commandText);
}
public int Update(Customer info)
{
string commandText = string.Format("update Customer set name='{0}', comment='{1}', tel='{2}', phone='{3}', address='{4}', arrear={5}, receipt={6}, parent={7} where ID={8}",
info.Name, info.Comment, info.Tel, info.Phone, info.Address, info.arrear, info.receipt, info.Parent, info.ID);
return(DbHelperAccess.executeNonQuery(commandText));
}
public int Update(Consume info)
{
string commandText = string.Format("update Consume set code='{0}', consumeTime='{1}',cardID={2}, status={3}, type={4}, num={5}, operator='{6}', comment='{7}', leftNum={8} where ID={9}",
info.Code, info.ConsumeTime, info.CardID, info.Status, info.Type, info.Number, info.Oper, info.Comment, info.LeftNumber, info.ID);
return(DbHelperAccess.executeNonQuery(commandText));
}
public int Update(CharactorValue info)
{
string commandText = string.Format("update CharactorValue set name='{0}' where ID={1}",
info.Name, info.Id);
return(DbHelperAccess.executeNonQuery(commandText));
}
private void setAttribute(int productID, Product info, List <CharactorValue> colors, List <CharactorValue> sizes)
{
string commandText = "";
foreach (CharactorValue color in colors)
{
commandText = string.Format("insert into ProductAttribute(productID, charactorID, charactorValueID) values({0},{1},{2})", productID, color.CharactorId, color.Id);
DbHelperAccess.executeNonQuery(commandText);
}
foreach (CharactorValue size in sizes)
{
commandText = string.Format("insert into ProductAttribute(productID, charactorID, charactorValueID) values({0},{1},{2})", productID, size.CharactorId, size.Id);
DbHelperAccess.executeNonQuery(commandText);
}
foreach (CharactorValue color in colors)
{
foreach (CharactorValue size in sizes)
{
string composeID = string.Format("{0}:{1};{2}:{3}", color.CharactorId, color.Id, size.CharactorId, size.Id);
commandText = string.Format("insert into ProductSKU(productID, composeID, price, colorID, colorName, sizeID, sizeName) values({0},'{1}',{2}, {3}, '{4}', {5}, '{6}')",
productID, composeID, info.PricePurchase, color.Id, color.Name, size.Id, size.Name);
DbHelperAccess.executeNonQuery(commandText);
}
}
}
//仅限于同级相邻两个节点，而且node比preNode大
public bool nodeUp(string tableName, Category node, Category preNode)
{
//先把node挪开，要不等下会重叠
int maxRight = DbHelperAccess.executeMax("rgt", tableName);
maxRight++;
int offset = maxRight - node.Left;
string commandText = string.Format("update {0} set {0}.rgt={0}.rgt+{1}, {0}.lft={0}.lft+{1} where lft>={2} and lft<={3}", tableName, offset, node.Left, node.Right);
DbHelperAccess.executeNonQuery(commandText);
//把preNode移过去新位置
int span = node.Right - node.Left + 1;
commandText = string.Format("update {0} set {0}.rgt={0}.rgt+{1}, {0}.lft={0}.lft+{1} where lft>={2} and lft<={3}", tableName, span, preNode.Left, preNode.Right);
DbHelperAccess.executeNonQuery(commandText);
node = this.FindById(tableName, node.Id);
span = preNode.Right - preNode.Left + 1 + offset;
commandText = string.Format("update {0} set {0}.rgt={0}.rgt-{1}, {0}.lft={0}.lft-{1} where lft>={2} and lft<={3}", tableName, span, node.Left, node.Right);
DbHelperAccess.executeNonQuery(commandText);
return(true);
}
public void UpdateBaiscInfo(ProductCirculation info)
{
string commandText = string.Format("update {0} set code='{1}', circulationTime='{2}', comment='{3}', customerID={4}, total={5}, backFreightPerPiece={6}, realTotal={7}, previousArrears={8}, thisPayed ={9}, freight={10}, operator='{11}', lastPayReceipt='{12}' where ID={13}",
tableName, info.Code, info.CirculationTime, info.Comment, info.CustomerID <= 0 ? "null" : info.CustomerID.ToString(), info.Total, info.BackFreightPerPiece, info.RealTotal, info.PreviousArrears, info.ThisPayed, info.Freight, info.Oper, info.LastPayReceipt, info.ID);
DbHelperAccess.executeNonQuery(commandText);
}
public int UpdateStatus(int sellID, int status)
{
string commandText = string.Format("update ProductSell set status = {0} where ID={1}",
status, sellID);
return(DbHelperAccess.executeNonQuery(commandText));
}
public int Update(ProductJobRecord info)
{
string commandText = string.Format("update ProductJobRecord set productID={0}, num={1}, arrivalNum={2} where ID={3}",
info.ProductID, info.Number, info.ArrivalNum, info.ID);
return(DbHelperAccess.executeNonQuery(commandText));
}
public int update_purchase_price_by_id(int id, double purchase_price)
{
string commandText = string.Format("update ProductStainless set pricePurchase={0} where ID={1}",
purchase_price, id);
return(DbHelperAccess.executeNonQuery(commandText));
}
public int UpdateArrivalNum(int ID, int arrivalNum)
{
string commandText = string.Format("update ProductJobRecord set arrivalNum={0} where ID={1}",
arrivalNum, ID);
return(DbHelperAccess.executeNonQuery(commandText));
}
/*
* public DataTable FindListForStatistic(Category parent)
* {
* string commandText = "select ID, name from Customer";
* if (parent != null)
* commandText = string.Format("select Customer.ID, Customer.name from Customer, CustomerCategory where Customer.parent=CustomerCategory.ID and CustomerCategory.lft>={0} and CustomerCategory.rgt<={1}", parent.Left, parent.Right);
* return DbHelperAccess.executeQuery(commandText);
* }*/
public int Update(Card info)
{
string commandText = string.Format("update Card set code='{0}', cardTime='{1}', status={2}, customerID={3}, type={4}, total={5}, num={6}, operator='{7}', comment='{8}', leftNum={9} where ID={10}",
info.Code, info.CardTime, info.Status, info.CustomerID, info.Type, info.Total, info.Number, info.Oper, info.Comment, info.LeftNumber, info.ID);
return(DbHelperAccess.executeNonQuery(commandText));
}
public int Insert(ProductSell info, List <ProductSellRecord> records)
{
try
{
//string commandText = @"insert into Users(userName,userPassword,userLevel,userPhone,userAddress) values (
//?userName,?userPassword,?userLevel,?userPhone,?userAddress)";
string commandText = string.Format("insert into ProductSell(name, sellTime, comment, status, customerID) values('{0}', '{1}', '{2}', '{3}', {4})", info.Name, info.SellTime, info.Comment, info.Status, info.CustomerID);
DbHelperAccess.executeNonQuery(commandText);
int ProductSellID = DbHelperAccess.executeMax("ID", "ProductSell");
ProductSellRecordDao dao = new ProductSellRecordDao();
foreach (ProductSellRecord record in records)
{
record.SellID = ProductSellID;
dao.Insert(record);
}
return(ProductSellID);
}
catch (Exception ex)
{
throw ex;
}
}
//没有加入cashDirection和arrearDirection, status, hide
public void Update(PayReceipt info)
{
string commandText = string.Format("update PayReceipt set serial='{0}', bill_time='{1}', comment='{2}', customer_id={3}, bill_type={4}, handle_people='{5}', previousArrears={6}, amount={7}, thisPayed={8}, status={9} where ID={10}",
info.serial, info.bill_time, info.comment, info.customer_id <= 0 ? "null" : info.customer_id.ToString(), (int)info.bill_type, info.handle_people, info.previousArrears, info.amount, info.thisPayed, info.status, info.id);
DbHelperAccess.executeNonQuery(commandText);
}
//
public bool DeleteDeaf(string tableName, int id)
{
Category category = this.FindById(tableName, id);
//not leaf node
if (category.Right > category.Left + 1)
{
return(false);
}
int point = category.Right;
try
{
string commandText = string.Format("delete from {0} where ID={1}", tableName, id);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("update {0} set {0}.lft={0}.lft-2 where lft>{1}", tableName, point);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("update {0} set {0}.rgt={0}.rgt-2 where rgt>{1}", tableName, point);
DbHelperAccess.executeNonQuery(commandText);
}
catch {
return(false);
}
return(true);
}
public int UpdateNum(int id, int num)
{
string commandText = string.Format("update ProductSKU set num={0} where ID={1}",
num, id);
return(DbHelperAccess.executeNonQuery(commandText));
}
private void delAttribute(int productID)
{
string commandText = string.Format("delete from ProductAttribute where productID = {0}", productID);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("delete from ProductSKU where productID = {0}", productID);
DbHelperAccess.executeNonQuery(commandText);
}
public bool Delete(int id)
{
try
{
string commandText = string.Format("delete from CharactorValue where ID={0}", id);
DbHelperAccess.executeNonQuery(commandText);
return(true);
}
catch {
return(false);
}
}
private void button3_Click(object sender, EventArgs e)
{
string commandText = "";
commandText = string.Format("delete * from ProductStainlessCirculationRecord;");
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("delete * from ProductStainlessCirculation;");
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("delete * from ProductStainless;");
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("delete * from SellProfit;");
DbHelperAccess.executeNonQuery(commandText);
}
public bool Delete(int id)
{
try
{
string commandText = string.Format("delete from ProductStainless where ID={0}", id);
DbHelperAccess.executeNonQuery(commandText);
return(true);
}
catch (Exception ex) {
throw ex;
return(false);
}
}
public int Insert(SellProfit one)
{
try
{
string commandText = string.Format("insert into SellProfit(cnt, price, sum_price, cost, profit, profit_margin, sum_cost, record_id) values({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})",
one.cnt, one.price, one.sum_price, one.cost, one.profit, one.profit_margin, one.sum_cost, one.record_id);
return(DbHelperAccess.executeNonQuery(commandText));
}
catch (Exception ex)
{
throw ex;
}
}
public bool UpdateBasicInfo(Product info)
{
try
{
string commandText = string.Format("update Product set name='{0}', price={1}, comment='{2}', parent={3} where ID={4}",
info.Name, info.PricePurchase, info.Comment, info.CategoryID, info.ID);
DbHelperAccess.executeNonQuery(commandText);
return(true);
}
catch (Exception ex) {
return(false);
}
}
public int Insert(ProductClothesCirculationSKURecord info)
{
try
{
string commandText = string.Format("insert into ProductCirculationSKURecord(productSKUID, num, price, recordID) values('{0}', '{1}', '{2}', '{3}')",
info.ProductSKUID, info.Num, info.Price, info.RecordID);
return(DbHelperAccess.executeNonQuery(commandText));
}
catch (Exception ex)
{
throw ex;
}
}
public bool Update(ProductStainless info)
{
try
{
string commandText = string.Format("update ProductStainless set serial='{0}', name='{1}', comment='{2}', parent={3}, pricePurchase={4}, priceCost = {5}, priceSell={6}, unit='{7}', quantityPerPiece={8}, num={9}, disable={10} where ID={11}",
info.Serial, info.Name, info.Comment, info.CategoryID, info.PricePurchase, info.PriceCost, info.PriceSell, info.Unit, info.QuantityPerPiece, info.Num, info.Disable, info.ID);
DbHelperAccess.executeNonQuery(commandText);
return(true);
}
catch (Exception ex) {
return(false);
}
}
public int Insert(CharactorValue info)
{
try
{
string commandText = string.Format("insert into CharactorValue(name, charactorID) values('{0}', {1})", info.Name, info.CharactorId);
DbHelperAccess.executeNonQuery(commandText);
int id = DbHelperAccess.executeMax("ID", "CharactorValue");
return(id);
}
catch (Exception ex)
{
throw ex;
}
}
public bool UpdateParent(string tableName, Category node, Category preParent, Category newParent)
{
if (preParent == null && newParent == null || preParent != null && newParent != null && preParent.Id == newParent.Id)
{
return(false);
}
string commandText = string.Format("update {0} set parent={1} where ID={2}", tableName, newParent == null?"null":newParent.Id.ToString(), node.Id);
DbHelperAccess.executeNonQuery(commandText);
int newParentRight = 0;
int nodeSpan = node.Right - node.Left + 1;
if (newParent != null)
{
newParentRight = newParent.Right;
//先腾出位置，包括newParent的right，也包括node
commandText = string.Format("update {0} set {0}.lft={0}.lft+{1} where lft>={2}", tableName, nodeSpan, newParentRight);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("update {0} set {0}.rgt={0}.rgt+{1} where rgt>={2}", tableName, nodeSpan, newParentRight);
DbHelperAccess.executeNonQuery(commandText);
}
else
{
int maxRight = DbHelperAccess.executeMax("rgt", tableName);
newParentRight = maxRight + 1;
}
//刷新下node1
node = this.FindById(tableName, node.Id);
//node的left从newParentRight开始
int offset = newParentRight - node.Left;
//更新下node2
commandText = string.Format("update {0} set {0}.rgt={0}.rgt+{1}, {0}.lft={0}.lft+{1} where lft>={2} and lft<={3}", tableName, offset, node.Left, node.Right);
DbHelperAccess.executeNonQuery(commandText);
//用node1来判断，右边的才需要删除位置
commandText = string.Format("update {0} set {0}.lft={0}.lft-{1} where lft>{2}", tableName, nodeSpan, node.Right);
DbHelperAccess.executeNonQuery(commandText);
commandText = string.Format("update {0} set {0}.rgt={0}.rgt-{1} where rgt>{2}", tableName, nodeSpan, node.Right);
DbHelperAccess.executeNonQuery(commandText);
return(true);
}
public void Update(ProductSell info, List <ProductSellRecord> records)
{
string commandText = string.Format("update ProductSell set name='{0}', sellTime='{1}', comment='{2}', customerID='{3}' where ID={4}",
info.Name, info.SellTime, info.Comment, info.CustomerID, info.ID);
DbHelperAccess.executeNonQuery(commandText);
ProductSellRecordDao.getInstance().DeleteBySellID(info.ID);
foreach (ProductSellRecord record in records)
{
record.SellID = info.ID;
ProductSellRecordDao.getInstance().Insert(record);
}
}
