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 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 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 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));
}
public PayReceipt FindByID(int ID)
{
string commandText = string.Format("select PayReceipt.*, Customer.name from PayReceipt left join Customer on PayReceipt.customer_id = Customer.ID where PayReceipt.ID={0}", ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
//要测试字段是否正确
return(formatPayReceipt(dr));
}
public string Get(int id)
{
string commandText = string.Format("select * from Conf where ID={0}", id);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
//DbHelperAccess.executeQuery(commandText);
if (dr != null)
{
return(dr["conf"] as string);
}
return(null);
}
public PayReceipt FindLastestByCustomerID(int customerID, bool payNotNull)
{
////模仿FindByID，所以left join customer，其实可以不要
string commandText = string.Format("select * from PayReceipt where bill_time = (SELECT max(bill_time) from PayReceipt where customer_id={0} and status=4)", customerID);
if (payNotNull)
{
commandText = string.Format("select * from PayReceipt where bill_time = (SELECT max(bill_time) from PayReceipt where customer_id={0} and status=4 and thisPayed <>0)", customerID);
}
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
return(formatPayReceipt(dr));
}
public double FindPriceByID(int ID)
{
string commandText = string.Format("select price from Product where ID={0}", ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
double result = 0;
if (dr != null)
{
string str = dr[0].ToString();
double.TryParse(str, out result);
}
return(result);
}
public ProductCirculation FindLastestByCustomerID(int customerID, bool thisPayNotNull)
{
////模仿FindByID，所以left join customer，其实可以不要
string commandText = string.Format("select * from {0} where circulationTime = (SELECT max(circulationTime) from {0} where customerID={1} and status=4)", tableName, customerID);
if (thisPayNotNull)
{
commandText = string.Format("select * from {0} where circulationTime = (SELECT max(circulationTime) from {0} where customerID={1} and status=4 and thisPayed <>0 )", tableName, customerID);
}
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
return(this.formatProductCirculation(dr));
}
public int FindNumByID(int ID)
{
string commandText = string.Format("select num from ProductSKU where ID={0}", ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
int result = 0;
if (dr != null)
{
string str = dr[0].ToString();
int.TryParse(str, out result);
}
return(result);
}
public double find_purchase_price_by_id(int ID)
{
string commandText = string.Format("select pricePurchase from ProductStainless where ID={0}", ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
double result = 0;
if (dr != null)
{
string str = dr[0].ToString();
double.TryParse(str, out result);
}
return(result);
}
public int FindNumByComposeID(int productID, int colorID, int sizeID)
{
string composeID = string.Format("1:{0};2:{1}", colorID, sizeID);
string commandText = string.Format("select num from ProductSKU where productID={0} and composeID='{1}'", productID, composeID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
int result = 0;
if (dr != null)
{
string str = dr[0].ToString();
int.TryParse(str, out result);
}
return(result);
}
/*
* public int Insert(Product info, List<CharactorValue> colors, List<CharactorValue> sizes)
* {
* try
* {
* string commandText = string.Format("insert into Product(name, comment, parent) values('{0}', '{1}', 19)", info.Name, info.Comment);
* DbHelperAccess.executeNonQuery(commandText);
* int productID = DbHelperAccess.executeLastID("ID", "Product");
* 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) values({0},'{1}',{2})",
* productID, composeID, info.Price);
* DbHelperAccess.executeNonQuery(commandText);
* }
* }
* return productID;
* }
* catch (Exception ex)
* {
* throw ex;
* }
*
* }
*
* public void Update(Product info, List<int> elementIDs) {
* string commandText = string.Format("update Product set name='{0}', price='{1}', comment='{2}' where ID={3}",
* info.Name, info.Price, info.Comment, info.ID);
*
* DbHelperAccess.executeNonQuery(commandText);
*
* commandText = string.Format("delete from ProductElement where productID={0}", info.ID);
* DbHelperAccess.executeNonQuery(commandText);
*
* foreach (int elementID in elementIDs)
* {
* commandText = string.Format("insert into ProductElement(productID, elementID) values('{0}', '{1}')", info.ID, elementID);
* DbHelperAccess.executeNonQuery(commandText);
* }
* }
*
* public DataTable FindList(Category parent)
* {
* string commandText = "select * from Product";
* if (parent != null)
* commandText = string.Format("select * from Product, ProductCategory where Product.parent=ProductCategory.ID and ProductCategory.lft>={0} and ProductCategory.rgt<={1}", parent.Left, parent.Right);
* return DbHelperAccess.executeQuery(commandText);
* }
*
*
*/
public ProductSKU FindByComposeID(int productID, int colorID, int sizeID)
{
string composeID = string.Format("1:{0};2:{1}", colorID, sizeID);
string commandText = string.Format("select * from ProductSKU where productID={0} and composeID='{1}'", productID, composeID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
ProductSKU product = new ProductSKU();
if (dr != null)
{
product.ID = (int)dr["ID"];
product.Color = CharactorValueDao.getInstance().findById(colorID);// dr["name"] as string;
product.Size = CharactorValueDao.getInstance().findById(sizeID);
return(product);
}
return(null);
}
public CharactorValue findById(int charactorValueID)
{
string commandText = "select * from CharactorValue where ID = " + charactorValueID.ToString();
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
CharactorValue cv = new CharactorValue();
if (dr != null)
{
cv.Id = (int)dr["ID"];
cv.CharactorId = (int)dr["charactorID"];
cv.Name = dr["name"] as string;
}
return(cv);
}
public ProductJob FindByID(int ID)
{
string commandText = string.Format("select * from ProductJob where ID={0}", ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
ProductJob job = new ProductJob();
if (dr != null)
{
job.ID = (int)dr["ID"];
job.Name = dr["name"] as string;
job.JobTime = (DateTime)dr["jobTime"];
job.Status = (int)dr["status"];
job.Comment = dr["comment"] as string;
return(job);
}
return(null);
}
public ProductSell FindByID(int ID)
{
string commandText = string.Format("select * from ProductSell where ID={0}", ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
ProductSell sell = new ProductSell();
if (dr != null)
{
sell.ID = (int)dr["ID"];
sell.Name = dr["name"] as string;
sell.SellTime = (DateTime)dr["sellTime"];
sell.Comment = dr["comment"] as string;
sell.Status = (int)dr["status"];
sell.CustomerID = (int)dr["customerID"];
return(sell);
}
return(null);
}
/*
* public DataTable FindList()
* {
* string commandText = "select * from Customer";
* return DbHelperAccess.executeQuery(commandText);
* }
*/
public Category FindById(string tableName, int ID)
{
string commandText = string.Format("select * from {0} where ID = {1}", tableName, ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
if (dr != null)
{
Category category = new Category();
category.Id = (int)dr["ID"];
category.Name = dr["name"] as string;
try { category.Parent = (int)dr["parent"]; }
catch { }
category.Left = (int)dr["lft"];
category.Right = (int)dr["rgt"];
return(category);
}
return(null);
}
public ProductSKU FindByID(int ID)
{
string commandText = string.Format("select * from ProductSKU where ID={0}", ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
ProductSKU product = new ProductSKU();
if (dr != null)
{
product.ID = (int)dr["ID"];
string composeIDs = dr["composeID"] as string;
string [] composeIDstrs = composeIDs.Split(new char [] { ':', ';' });
int colorID = int.Parse(composeIDstrs[1]);
int sizeID = int.Parse(composeIDstrs[3]);
product.Color = CharactorValueDao.getInstance().findById(colorID);// dr["name"] as string;
product.Size = CharactorValueDao.getInstance().findById(sizeID);
return(product);
}
return(null);
}
public ProductStainless FindByID(int ID)
{
string commandText = string.Format("select * from ProductStainless where ID={0}", ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
ProductStainless product = new ProductStainless();
if (dr != null)
{
product.ID = (int)dr["ID"];
product.Name = dr["name"] as string;
product.Serial = dr["serial"] as string;
product.CategoryID = (int)dr["parent"];
double quantityPerPiece;
double.TryParse(dr["quantityPerPiece"].ToString(), out quantityPerPiece);
product.QuantityPerPiece = quantityPerPiece;
product.Unit = dr["unit"] as string;
double num;
double.TryParse(dr["num"].ToString(), out num);
product.Num = num;
product.Comment = dr["comment"] as string;
double pricePurchase, priceSell, priceCost;
double.TryParse(dr["pricePurchase"].ToString(), out pricePurchase);
product.PricePurchase = pricePurchase;
double.TryParse(dr["priceSell"].ToString(), out priceSell);
product.PriceSell = priceSell;
double.TryParse(dr["priceCost"].ToString(), out priceCost);
product.PriceCost = priceCost;
bool disable;
bool.TryParse(dr["disable"].ToString(), out disable);
product.Disable = disable;
return(product);
}
return(null);
}
public int getMaxCode(string code)
{
string commandText = string.Format("select max(code) from {0} where code like '{1}%'", tableName, code);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
string result = dr[0] as string;
if (string.IsNullOrEmpty(result))
{
return(0);
}
else
{
int max = 0;
if (int.TryParse(result.Substring(result.LastIndexOf('-')), out max))
{
return(Math.Abs(max));
}
else
{
return(0);
}
}
}
public Product FindByID(int ID)
{
string commandText = string.Format("select * from Product where ID={0}", ID);
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
Product product = new Product();
if (dr != null)
{
product.ID = (int)dr["ID"];
product.Name = dr["name"] as string;
product.CategoryID = (int)dr["parent"];
product.Comment = dr["comment"] as string;
product.Num = (int)dr["num"];
double price;
double.TryParse(dr["price"].ToString(), out price);
product.PricePurchase = price;
return(product);
}
return(null);
}
public int getMaxCode(string code)
{
string commandText = string.Format("select max(serial) from PayReceipt where serial like '{0}-{1}-%'", code, DateTime.Now.ToString("yyyyMMdd"));
DataRow dr = DbHelperAccess.executeQueryGetOneRow(commandText);
string result = dr[0] as string;
if (string.IsNullOrEmpty(result))
{
return(0);
}
else
{
int max = 0;
if (int.TryParse(result.Substring(result.LastIndexOf('-')), out max))
{
return(Math.Abs(max));
}
else
{
return(0);
}
}
}