//ͳ��һ��ʱ���ڵij����¼
public List<Inventory> CountInventoryByTime(DateTime startdate, DateTime enddate)
{
string querysql = "";
querysql += " where InventoryType = 'O'";
querysql += " and day >= '" + startdate.ToString("s") + "'";
querysql += " and day <= '" + enddate.ToString("s") + "'";
querysql += " group by ProductSn,CustomerName";
command.CommandText = "select sum(Counts),sum(Boxs),sum(Counts*Price),ProductSn,CustomerName from Inventory" + querysql;
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
List<Inventory> listInventory = new List<Inventory>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Inventory inventory = new Inventory();
inventory.Counts = Convert.ToInt16(dt.Rows[i][0].ToString());
inventory.Boxs = Convert.ToInt16(dt.Rows[i][1].ToString());
inventory.Price = Convert.ToDouble(dt.Rows[i][2]);
inventory.ProductSn = dt.Rows[i][3].ToString();
inventory.CustomerName = dt.Rows[i][4].ToString();
listInventory.Add(inventory);
}
return listInventory;
}
//��ѯ���/�����¼
public List<Inventory> SelectInventoryForList(List<Inventory> listInventory)
{
string querysql = "";
int j = 0;
while (j < listInventory.Count)
{
querysql = querysql + ",'" + listInventory[j].Id + "'";
j++;
}
querysql = querysql.Substring(1);
command.CommandText = "SELECT * FROM Inventory where Id in (" + querysql + ")";
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
DataTable dt = new DataTable("Inventory");
da.Fill(dt);
List<Inventory> listtemp = new List<Inventory>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Inventory temp = new Inventory();
temp.Id = Convert.ToInt16(dt.Rows[i][0].ToString());
temp.InventoryId = dt.Rows[i][1].ToString();
temp.ProductId = Convert.ToInt16(dt.Rows[i][2].ToString());
temp.CustomerId = Convert.ToInt16(dt.Rows[i][3].ToString());
temp.Boxs = Convert.ToInt16(dt.Rows[i][4].ToString());
temp.Counts = Convert.ToInt16(dt.Rows[i][5].ToString());
temp.Price = Convert.ToDouble(dt.Rows[i][6].ToString());
temp.InventoryType = dt.Rows[i][7].ToString();
temp.Day = Convert.ToDateTime(dt.Rows[i][8].ToString());
temp.UserId = dt.Rows[i][9].ToString();
temp.ProductSn = dt.Rows[i][10].ToString();
temp.ProductName = dt.Rows[i][11].ToString();
temp.CustomerName = dt.Rows[i][12].ToString();
listtemp.Add(temp);
}
return listtemp;
}
//UpdateStock
private void button23_Click(object sender, EventArgs e)
{
textBox1.Text = "Stocks";
List<Inventory> listinventory = new List<Inventory>();
int i = 1;
while (i < 3)
{
Inventory inventory = new Inventory();
inventory.InventoryId = "1";
inventory.ProductId = i;
inventory.CustomerId = i;
inventory.Counts = 1000 * i + i * 100 + i * 10 + i;
inventory.Price = i * 0.13 + 200;
inventory.InventoryType = "O";
inventory.Day = System.DateTime.Now;
listinventory.Add(inventory);
i++;
}
dba.InsertInventory(listinventory);
MessageBox.Show(dba.UpdateStock(listinventory));
}
//�������/�����ʱ�䷶Χ����ѯ���/�����¼
public List<Inventory> SelectInventoryByTypeAndTimeToList(string type, DateTime startdate, DateTime enddate)
{
string querysql = "";
querysql += " where InventoryType = '" + type + "'";
querysql += " and day >= '" + startdate.ToString("s") + "'";
querysql += " and day <= '" + enddate.ToString("s") + "'";
command.CommandText = "SELECT * FROM Inventory" + querysql;
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
DataTable dt = new DataTable("Inventory");
da.Fill(dt);
List<Inventory> listInventory = new List<Inventory>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Inventory inventory = new Inventory();
inventory.Id = Convert.ToInt16(dt.Rows[i][0].ToString());
inventory.InventoryId = dt.Rows[i][1].ToString();
inventory.ProductId = Convert.ToInt16(dt.Rows[i][2].ToString());
inventory.CustomerId = Convert.ToInt16(dt.Rows[i][3].ToString());
inventory.Boxs = Convert.ToInt16(dt.Rows[i][4].ToString());
inventory.Counts = Convert.ToInt16(dt.Rows[i][5].ToString());
inventory.Price = Convert.ToDouble(dt.Rows[i][6]);
inventory.InventoryType = dt.Rows[i][7].ToString();
inventory.Day = Convert.ToDateTime(dt.Rows[i][8].ToString());
inventory.UserId = dt.Rows[i][9].ToString();
inventory.ProductSn = dt.Rows[i][10].ToString();
inventory.ProductName = dt.Rows[i][11].ToString();
inventory.CustomerName = dt.Rows[i][12].ToString();
listInventory.Add(inventory);
}
return listInventory;
}
//SelectInventory
private void button18_Click(object sender, EventArgs e)
{
textBox1.Text = "Inventory";
List<Inventory> listinventory = new List<Inventory>();
int i = 1;
while (i < 20)
{
Inventory inventory = new Inventory();
inventory.Id = i;
inventory.InventoryId = "1";
inventory.ProductId = i;
inventory.CustomerId = i;
inventory.Counts = 1000 * i + i * 100 + i * 10 + i;
inventory.Price = i * 0.13 + 200;
inventory.InventoryType = "O";
inventory.Day = System.DateTime.Now;
//MessageBox.Show(inventory.ToString());
listinventory.Add(inventory);
i++;
}
MessageBox.Show(dba.SelectInventory(listinventory).Rows.Count.ToString());
List<Inventory> temp = new List<Inventory>();
temp = dba.SelectInventoryForList(listinventory);
for (int j = 0; j < temp.Count; j++)
{
MessageBox.Show(temp[j].ToString());
}
}