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()));
}
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 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 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 List <SellProfit> FindList(DateTime startTime, DateTime endTime, string product, string customer)
{
StringBuilder commandText = new StringBuilder();
//查找的字段名可以都使用<原始表名>.<字段>，至于结果，可以参考access查找出来的记
string temp = "select SellProfit.*, ProductStainlessCirculationRecord.unit, ProductStainlessCirculation.code, ProductStainlessCirculation.type, ProductStainlessCirculation.circulationTime, ProductStainless.serial, ProductStainless.name, ProductStainless.ID, Customer.name, Customer.ID"
+ " from SellProfit, ProductStainlessCirculationRecord, (select * from ProductStainlessCirculation left join Customer on Customer.ID = ProductStainlessCirculation.customerID ) circulation, ProductStainless"
+ " where SellProfit.record_id = ProductStainlessCirculationRecord.ID and"
+ " ProductStainlessCirculationRecord.circulationID = ProductStainlessCirculation.ID"
+ " and ProductStainlessCirculationRecord.productID = ProductStainless.ID"
+ " and ProductStainlessCirculation.circulationTime between #{0}# and #{1}#";
commandText.Append(string.Format(temp, startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
if (!string.IsNullOrEmpty(product))
{
commandText.Append(string.Format(" and ProductStainless.name like '%{0}%'", product));
}
else if (!string.IsNullOrEmpty(customer))
{
commandText.Append(string.Format(" and Customer.name like '%{0}%'", customer));
}
commandText.Append(" order by SellProfit.ID desc");
DataTable data = DbHelperAccess.executeQuery(commandText.ToString());
List <SellProfit> ls = new List <SellProfit>();
foreach (DataRow dr in data.Rows)
{
SellProfit sell = new SellProfit();
if (dr != null)
{
sell.ID = (int)dr["SellProfit.ID"];
sell.serial = dr["code"] as string;
sell.type = (int)dr["type"];
sell.sell_time = (DateTime)dr["circulationTime"];
sell.customerID = (int)dr["Customer.ID"];
sell.customer = dr["circulation.name"] as string;
sell.productID = (int)dr["ProductStainless.ID"];
sell.product = dr["ProductStainless.name"] as string;
sell.unit = dr["unit"] as string;
sell.cnt = (int)dr["cnt"];
sell.price = (double)dr["price"];
sell.sum_price = (double)dr["sum_price"];
sell.cost = (double)dr["cost"];
sell.profit = (double)dr["profit"];
sell.profit_margin = (double)dr["profit_margin"];
sell.sum_cost = (double)dr["sum_cost"];
sell.record_id = (int)dr["record_id"];
ls.Add(sell);
}
}
return(ls);
}
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 DataTable FindList(DateTime startTime, DateTime endTime, int status)
{
StringBuilder commandText = new StringBuilder(string.Format("select * from ProductJob where jobTime between #{0}# and #{1}# ", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
if (status > 0 && status <= 4)
{
commandText.Append(string.Format(" and status = {0}", status));
}
commandText.Append(" order by ID desc");
return(DbHelperAccess.executeQuery(commandText.ToString()));
}
public List <Customer> FindByParentId(int parentId)
{
String commandText = string.Format("select * from Customer where parent = {0} order by ID", parentId);
DataTable dt = DbHelperAccess.executeQuery(commandText);
List <Customer> categorys = new List <Customer>();
foreach (DataRow dr in dt.Rows)
{
categorys.Add(this.getBeanFromDataRow(dr));
}
return(categorys);
}
public DataTable FindList(Category parent, string name)
{
StringBuilder commandText = new StringBuilder("select * from Product, ProductCategory where Product.parent=ProductCategory.ID");
if (parent != null)
{
commandText.AppendFormat(" and ProductCategory.lft>={0} and ProductCategory.rgt<={1}", parent.Left, parent.Right);
}
if (!string.IsNullOrEmpty(name))
{
commandText.AppendFormat(" and Product.name like '%{0}%'", name);
}
return(DbHelperAccess.executeQuery(commandText.ToString()));
}
public override List <ProductCirculationRecord> FindList(int circulationID)
{
List <ProductCirculationRecord> records = new List <ProductCirculationRecord>();
string commandText = string.Format("select * from ProductStainlessCirculationRecord, ProductStainless where ProductStainlessCirculationRecord.productID = ProductStainless.ID and circulationID = {0} order by ProductStainlessCirculationRecord.ID", circulationID);
DataTable dt = DbHelperAccess.executeQuery(commandText);
foreach (DataRow dr in dt.Rows)
{
bool tempBool = false;
ProductStainlessCirculationRecord record = new ProductStainlessCirculationRecord();
record.CirculationID = circulationID;
record.ID = (int)dr["ProductStainlessCirculationRecord.ID"];
int pieces;
double price, totalPrice, quantityPerPiece;
double.TryParse(dr["price"].ToString(), out price);
record.Price = price;
record.Serial = dr["serial"].ToString();
record.ProductID = (int)dr["ProductStainless.ID"];
record.ProductName = dr["name"].ToString();
ValidateUtility.getDouble(dr, "ProductStainlessCirculationRecord.quantityPerPiece", out quantityPerPiece, out tempBool);
record.QuantityPerPiece = quantityPerPiece;
record.QuantityNull = tempBool;
ValidateUtility.getInt(dr, "pieces", out pieces, out tempBool);
record.Pieces = pieces;
record.PiecesNull = tempBool;
record.Unit = dr["ProductStainlessCirculationRecord.unit"].ToString();
double num;
ValidateUtility.getDouble(dr, "totalNum", out num, out tempBool);
record.TotalNum = num;
ValidateUtility.getDouble(dr, "totalPrice", out totalPrice, out tempBool);
record.TotalPrice = totalPrice;
record.Comment = dr["ProductStainlessCirculationRecord.comment"].ToString();
records.Add(record);
}
return(records);
}
public DataTable FindList(DateTime startTime, DateTime endTime, int status, int customerID)
{
StringBuilder commandText = new StringBuilder(string.Format("select ProductSell.*, Customer.name from ProductSell, Customer where Customer.ID = ProductSell.customerID and sellTime between #{0}# and #{1}# ", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
if (status > 0 && status <= 2)
{
commandText.Append(string.Format(" and status = {0}", status));
}
if (customerID > 0)
{
commandText.Append(string.Format(" and customerID={0}", customerID));
}
commandText.Append(" order by ProductSell.ID desc");
return(DbHelperAccess.executeQuery(commandText.ToString()));
}
public List <ProductAttribute> findAttributes(int productID, int charactorID)
{
string commandText = string.Format("select * from ProductAttribute where productID={0} and charactorID={1}", productID, charactorID);
DataTable dt = DbHelperAccess.executeQuery(commandText);
List <ProductAttribute> attrs = new List <ProductAttribute>();
foreach (DataRow dr in dt.Rows)
{
ProductAttribute attr = new ProductAttribute();
attr.ID = (int)dr["ID"];
attr.CharactorId = (int)dr["charactorID"];
attr.CharactorValueId = (int)dr["charactorValueId"];
//commented by stone: here can be improved for performance
attr.CharactorValue = CharactorValueDao.getInstance().findById(attr.CharactorValueId);
attrs.Add(attr);
}
return(attrs);
}
public DataTable FindList(DateTime startTime, DateTime endTime, int status, string customerName)
{
StringBuilder commandText = null;
commandText = new StringBuilder(string.Format("select Card.*, Customer.name from Card, Customer where Customer.ID = Card.customerID and cardTime between #{0}# and #{1}# ", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
if (!string.IsNullOrEmpty(customerName))
{
commandText.Append(string.Format(" and Customer.name like '%{0}%'", customerName));
}
if (status > 0)
{
commandText.Append(string.Format(" and Card.status={0}", status));
}
commandText.Append(" order by Card.ID desc");
return(DbHelperAccess.executeQuery(commandText.ToString()));
}
public List <ProductClothesCirculationSKURecord> FindList(int recordID)
{
List <ProductClothesCirculationSKURecord> records = new List <ProductClothesCirculationSKURecord>();
string commandText = string.Format("select * from ProductCirculationSKURecord where recordID = {0}", recordID);
DataTable dt = DbHelperAccess.executeQuery(commandText);
foreach (DataRow dr in dt.Rows)
{
ProductClothesCirculationSKURecord record = new ProductClothesCirculationSKURecord();
record.RecordID = recordID;
record.ID = (int)dr["ID"];
record.Num = (int)dr["num"];
record.ProductSKUID = (int)dr["productSKUID"];
record.ProductSKU = ProductSKUDao.getInstance().FindByID(record.ProductSKUID);
records.Add(record);
}
return(records);
}
public List <CharactorValue> FindList(int charactorID)
{
string commandText = "select * from CharactorValue where charactorID = " + charactorID.ToString();
DataTable dt = DbHelperAccess.executeQuery(commandText);
List <CharactorValue> elements = new List <CharactorValue>();
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
CharactorValue element = new CharactorValue();
element.Id = (int)dr["ID"];
element.Name = dr["name"] as string;
element.CharactorId = (int)dr["charactorID"];
elements.Add(element);
}
}
return(elements);
}
public List <string> FindPriceList(int type, int productID, int customerID)
{
//退货的价格以进货的价格为主
if (type == 2 || type == 4)
{
type--;
}
StringBuilder commandText = new StringBuilder();
string temp = "select distinct(ProductStainlessCirculationRecord.price) from ProductStainlessCirculationRecord, (select * from ProductStainlessCirculation left join Customer on Customer.ID = ProductStainlessCirculation.customerID ) circulation, ProductStainless"
+ " where ProductStainlessCirculationRecord.circulationID = ProductStainlessCirculation.ID and ProductStainlessCirculationRecord.productID = ProductStainless.ID and status = 4";
commandText.Append(temp);
if (type > 0)
{
commandText.Append(string.Format(" and ProductStainlessCirculation.type= {0}", type));
}
if (productID > 0)
{
commandText.Append(string.Format(" and ProductStainless.ID={0}", productID));
}
if (customerID > 0)
{
commandText.Append(string.Format(" and Customer.ID={0}", customerID));
}
//commandText.Append(" order by ProductStainlessCirculationRecord.ID desc");
DataTable dt = DbHelperAccess.executeQuery(commandText.ToString());
List <string> prices = new List <string>();
foreach (DataRow dr in dt.Rows)
{
prices.Add(dr["price"].ToString());
}
return(prices);
}
public DataTable FindList(Category parent, string name, bool notShowDisable)
{
StringBuilder commandText = new StringBuilder("select * from ProductStainless, ProductStainlessCategory where ProductStainless.parent=ProductStainlessCategory.ID");
if (parent != null)
{
commandText.AppendFormat(" and ProductStainlessCategory.lft>={0} and ProductStainlessCategory.rgt<={1}", parent.Left, parent.Right);
}
if (!string.IsNullOrEmpty(name))
{
commandText.AppendFormat(" and ( ProductStainless.name like '%{0}%' or ProductStainless.serial like '%{0}%')", name);
}
if (notShowDisable == true)
{
commandText.AppendFormat(" and ProductStainless.disable = false");
}
commandText.Append(" order by ProductStainless.ID");
return(DbHelperAccess.executeQuery(commandText.ToString()));
}
public DataTable FindList(DateTime startTime, DateTime endTime, int type, string product, int productID, string customer, int customerID)
{
StringBuilder commandText = new StringBuilder();
string temp = "select ProductCirculationSKURecord.*, ProductCirculationRecord.*, ProductCirculation.code, ProductCirculation.circulationTime, ProductCirculation.status, ProductCirculation.type, ProductCirculation.flowType, ProductSKU.colorName, ProductSKU.sizeName, Product.name, Customer.name"
+ " from ProductCirculationSKURecord, ProductCirculationRecord, (select * from ProductCirculation left join Customer on Customer.ID = ProductCirculation.customerID ) circulation, ProductSKU, Product"
+ " where ProductCirculationSKURecord.recordID = ProductCirculationRecord.ID"
+ " and ProductCirculationRecord.circulationID = ProductCirculation.ID"
+ " and ProductCirculationSKURecord.productSKUID = ProductSKU.ID"
+ " and ProductSKU.productID = Product.ID"
+ " and ProductCirculation.circulationTime between #{0}# and #{1}# and status = 4";
commandText.Append(string.Format(temp, startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
if (type > 0)
{
commandText.Append(string.Format(" and ProductCirculation.type between {0} and {1}", type, type + 1));
}
if (productID > 0)
{
commandText.Append(string.Format(" and Product.ID={0}", productID));
}
else if (!string.IsNullOrEmpty(product))
{
commandText.Append(string.Format(" and Product.name like '%{0}%'", product));
}
if (customerID > 0)
{
commandText.Append(string.Format(" and Customer.ID={0}", customerID));
}
else if (!string.IsNullOrEmpty(customer))
{
commandText.Append(string.Format(" and Customer.name like '%{0}%'", customer));
}
commandText.Append(" order by ProductCirculationSKURecord.ID desc");
return(DbHelperAccess.executeQuery(commandText.ToString()));
}
//DateTime? 相当于Nullable<DateTime>，调用时必须增加.value
public List <PayReceipt> FindPayReceiptList(DateTime?startTime, DateTime?endTime, int status, string name, int parent, int hide)
{
//要注意，这个语句会筛选掉没有Customer信息的
StringBuilder commandText = new StringBuilder("select PayReceipt.*, Customer.name from PayReceipt left join Customer on PayReceipt.customer_id = Customer.ID where 1=1 ");
if (startTime != null && endTime != null)
{
commandText.Append(string.Format(" and PayReceipt.bill_time between #{0}# and #{1}# ", startTime.Value.ToString("yyyy-MM-dd"), endTime.Value.ToString("yyyy-MM-dd")));
}
if (status > 0)
{
commandText.Append(string.Format(" and status = {0}", status));
}
if (parent > 0)
{
commandText.Append(string.Format(" and Customer.ID={0}", parent));
}
//hide = 0，只显示不隐藏的，hide=1，显示所有
commandText.Append(string.Format(" and hide <= {0}", hide));
if (!string.IsNullOrEmpty(name))
{
commandText.AppendFormat(" and Customer.name like '%{0}%'", name);
}
commandText.Append(" order by PayReceipt.bill_time desc");
DataTable dt = DbHelperAccess.executeQuery(commandText.ToString());
List <PayReceipt> list = new List <PayReceipt>();
foreach (DataRow pr in dt.Rows)
{
list.Add(this.formatPayReceipt(pr));
}
return(list);
}
public virtual List <ProductCirculation> FindProductCirculationList(int typeStart, int typeEnd, DateTime?startTime, DateTime?endTime, int status, string name, int parent)
{
//要注意，这个语句会筛选掉没有Customer信息的
StringBuilder commandText = new StringBuilder(string.Format("select {0}.*, Customer.name from {0} left join Customer on {0}.customerID = Customer.ID where 1=1 ", tableName));
if (typeStart > 0 && typeEnd > 0 && typeStart <= typeEnd)
{
commandText.Append(string.Format(" and type between {0} and {1}", typeStart, typeEnd));
}
if (startTime != null && endTime != null)
{
commandText.Append(string.Format(" and circulationTime between #{0}# and #{1}# ", startTime.Value.ToString("yyyy-MM-dd"), endTime.Value.ToString("yyyy-MM-dd")));
}
if (status > 0)
{
commandText.Append(string.Format(" and status = {0}", status));
}
if (!string.IsNullOrEmpty(name))
{
commandText.AppendFormat(" and Customer.name like '%{0}%'", name);
}
if (parent > 0)
{
commandText.Append(string.Format(" and Customer.ID={0}", parent));
}
DataTable dt = DbHelperAccess.executeQuery(commandText.ToString());
List <ProductCirculation> list = new List <ProductCirculation>();
foreach (DataRow pr in dt.Rows)
{
list.Add(this.formatProductCirculation(pr));
}
return(list);
}
public DataTable GetAll()
{
string commandText = string.Format("select * from Conf order by ID");
return(DbHelperAccess.executeQuery(commandText));
}
public DataTable FindList(int productJobID)
{
string commandText = string.Format("select * from ProductJobRecord where jobID = {0}", productJobID);
return(DbHelperAccess.executeQuery(commandText));
}