public List <ProductCirculationRecord> FindList(int circulationID)
{
List <ProductCirculationRecord> records = new List <ProductCirculationRecord>();
string commandText = string.Format("select * from ProductCirculationRecord, Product where ProductCirculationRecord.productID = Product.ID and circulationID = {0} order by ProductCirculationRecord.ID", circulationID);
DataTable dt = DbHelperAccess.executeQuery(commandText);
foreach (DataRow dr in dt.Rows)
{
ProductCirculationRecord record = new ProductCirculationRecord();
record.CirculationID = circulationID;
record.ID = (int)dr["ProductCirculationRecord.ID"];
double price;
double.TryParse(dr["ProductCirculationRecord.price"].ToString(), out price);
record.Price = price;
record.ProductID = (int)dr["Product.ID"];
record.ProductName = dr["name"].ToString();
record.TotalNum = (int)dr["totalNum"];
records.Add(record);
}
return(records);
}
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);
}
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);
}
}
}
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 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 List <Consume> FindList(int cardId, int status)
{
String commandText = string.Format("select * from Card, Customer, Consume where Consume.cardID = Card.ID and Card.customerID = Customer.ID");
if (cardId > 0)
{
commandText += string.Format(" and Consume.cardID={0}", cardId);
}
if (status > 0)
{
commandText += string.Format(" and Consume.status={0}", status);
}
commandText += " order by Consume.ID desc";
DataTable dt = DbHelperAccess.executeQuery(commandText);
List <Consume> consumes = new List <Consume>();
foreach (DataRow dr in dt.Rows)
{
consumes.Add(this.getBeanFromDataRow(dr));
}
return(consumes);
}
public Customer FindByID(int ID)
{
string commandText = string.Format("select * from Customer where ID={0}", ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
return(getBeanFromDataRow(dr));
}
public Consume FindByID(int ID)
{
string commandText = string.Format("select * from Card, Customer, Consume where Consume.ID={0} and Consume.cardID = Card.ID and Card.customerID = Customer.ID", ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
return(getBeanFromDataRow(dr));
}
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));
}
public int FindCount(int productID)
{
string commandText = string.Format("select count(*) from ProductCirculationRecord where ProductCirculationRecord.productID = {0}", productID);
int result = DbHelperAccess.executeQueryNum(commandText);
return(result);
}
/// <summary>
/// 核心方法
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
private static List <T> GetListBySql <T>(string sql) where T : new()
{
var type = typeof(T);
var st = type.GetTblStruct();
var list = new List <T>();
using (var reader = DbHelperAccess.GetReader(sql))
{
//组装数据
while (reader.Read())
{
var t = new T();
foreach (var item in st.ListCol)
{
if (item.NotSearch)
{
continue;
}
object obj = reader[item.ColName];
var p = type.GetProperty(item.ClassColName);
if (p != null)
{
var setValue = p.DealSetValue(obj);
p.SetValue(t, setValue, null);
}
}
list.Add(t);
}
}
return(list);
}
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 List <Category> FindByParentId(string tableName, int ID)
{
string idString = "=" + ID.ToString();
if (ID < 0)
{
idString = " is null";
}
string commandText = string.Format("select * from {0} where parent{1} order by lft", tableName, idString);
DataTable dt = DbHelperAccess.executeQuery(commandText);
List <Category> categorys = new List <Category>();
foreach (DataRow dr in dt.Rows)
{
Category category = new Category();
category.Id = (int)dr["ID"];
category.Name = dr["name"] as string;
string parent = dr["parent"] as string;
if (parent != null && parent != "")
{
category.Parent = int.Parse(parent);
}
category.Left = (int)dr["lft"];
category.Right = (int)dr["rgt"];
categorys.Add(category);
}
return(categorys);
}
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));
}
//仅限于同级相邻两个节点，而且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 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 UpdateArrivalNum(int ID, int arrivalNum)
{
string commandText = string.Format("update ProductJobRecord set arrivalNum={0} where ID={1}",
arrivalNum, 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 DataTable FindList(int type, DateTime startTime, DateTime endTime, int status, string customerName)
{
StringBuilder commandText = null;
if (type < 3)
{
commandText = new StringBuilder(string.Format("select {0}.*, Customer.name from {0}, Customer where Customer.ID = {0}.customerID and circulationTime between #{1}# and #{2}# ", tableName, startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
}
else if (type >= 3)
{
commandText = new StringBuilder(string.Format("select {0}.* from {0} where circulationTime between #{1}# and #{2}# ", tableName, startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
}
if (type > 0)
{
commandText.Append(string.Format(" and type between {0} and {1}", type * 2 - 1, type * 2));
}
if (status > 0)
{
commandText.Append(string.Format(" and status = {0}", status));
}
if (!string.IsNullOrEmpty(customerName))
{
commandText.Append(string.Format(" and Customer.name like '%{0}%'", customerName));
}
commandText.Append(string.Format(" order by {0}.circulationTime desc", tableName));
return(DbHelperAccess.executeQuery(commandText.ToString()));
}
//没有加入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 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 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 ProductCirculation FindLastestAccReceiptZero(int customerID)
{
string commandText = string.Format("select top 1 {0}.*, Customer.name from {0} left join Customer on Customer.ID = {0}.customerID where Customer.ID={1} and (arrearDirection * previousArrears + flowType * (realTotal - thisPayed)) < 0 and arrearDirection * previousArrears >=0 order by {0}.ID desc", tableName, customerID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
return(this.formatProductCirculation(dr));
}
public virtual ProductCirculation FindByID(int ID)
{
string commandText = string.Format("select {0}.*, Customer.name from {0} left join Customer on Customer.ID = {0}.customerID where {0}.ID={1}", tableName, ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
return(this.formatProductCirculation(dr));
}
/*
* 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;
}
}
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 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 PayReceipt FindLastestAccReceiptZero(int customerID)
{
string commandText = string.Format("select top 1 PayReceipt.*, Customer.name from PayReceipt left join Customer on PayReceipt.customer_id = Customer.ID where Customer.ID={0} and (arrearDirection * previousArrears - cashDirection * (amount - thisPayed)) < 0 and previousArrears >=0 order by PayReceipt.ID desc", customerID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
//要测试字段是否正确
return(formatPayReceipt(dr));
}
