/// <summary>
/// 新增一筆單價資訊
/// </summary>
/// <param name="item"></param>
public static void Create(UnitPriceItemModel item)
{
using (SQLiteConnection conn = new SQLiteConnection(AppSettings.ConnectString))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"INSERT INTO UnitPrice (CustomerCode, ProductCode, ProductName, Price, MaterialCode, SizeInfo, Combined, CombinedUnits, Processing0, Deleted, Color, UpdatedTime, PieceWeight, Package) VALUES (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14)";
cmd.Parameters.Add(new SQLiteParameter("@p1", item.CustomerCode));
cmd.Parameters.Add(new SQLiteParameter("@p2", item.Code));
cmd.Parameters.Add(new SQLiteParameter("@p3", item.Name));
cmd.Parameters.Add(new SQLiteParameter("@p4", item.Price));
cmd.Parameters.Add(new SQLiteParameter("@p5", item.MaterialCode));
cmd.Parameters.Add(new SQLiteParameter("@p6", item.Size));
cmd.Parameters.Add(new SQLiteParameter("@p7", item.IsCombined));
cmd.Parameters.Add(new SQLiteParameter("@p8", item.CombinedUnits));
cmd.Parameters.Add(new SQLiteParameter("@p9", item.Processing0));
cmd.Parameters.Add(new SQLiteParameter("@p10", item.IsDeleted));
cmd.Parameters.Add(new SQLiteParameter("@p11", item.ColorTypes));
cmd.Parameters.Add(new SQLiteParameter("@p12", DateTime.Now));
cmd.Parameters.Add(new SQLiteParameter("@p13", item.PieceWeight));
cmd.Parameters.Add(new SQLiteParameter("@p14", item.Package));
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 刪除一筆基本單價資訊(隱藏)
/// </summary>
/// <param name="item"></param>
public static void Delete(UnitPriceItemModel item)
{
using (SQLiteConnection conn = new SQLiteConnection(AppSettings.ConnectString))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"Update UnitPrice SET Deleted=@p0, UpdatedTime=@p1 WHERE ProductCode=@p2";
cmd.Parameters.AddWithValue("@p0", item.IsDeleted);
cmd.Parameters.AddWithValue("@p1", DateTime.Now);
cmd.Parameters.AddWithValue("@p2", item.Code);
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 回傳所有出貨紀錄
/// </summary>
/// <returns></returns>
public static List <ShipmentItemModel> GetShipmentRecordList()
{
List <ShipmentItemModel> rlt = new List <ShipmentItemModel>();
using (SQLiteConnection conn = new SQLiteConnection(AppSettings.ConnectString))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM ShipmentRecord order by ShipDate asc";
SQLiteDataReader sqlite_datareader = cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
long id = (long)sqlite_datareader["SerialNumber"];
string code = sqlite_datareader["ProductCode"].ToString();
double pricce = (double)sqlite_datareader["Price"];
string customer = sqlite_datareader["CustomerCode"].ToString();
int qty = Convert.ToInt32(sqlite_datareader["ShipQty"]);
DateTime dt = Convert.ToDateTime(sqlite_datareader["ShipDate"]);
string colorCode = sqlite_datareader["ColorCode"].ToString();
string colorName = sqlite_datareader["ColorName"].ToString();
bool isSample = Convert.ToBoolean((decimal)sqlite_datareader["IsSample"]);
UnitPriceListModel list = UnitPriceListModel.Units.FirstOrDefault(x => x._customerCode == customer);
UnitPriceItemModel unit = list.FirstOrDefault(x => x.Code == code);
rlt.Add(new ShipmentItemModel()
{
SerialNumber = id,
ProductCode = code,
UnitPrice = pricce,
CustomerCode = customer,
ShipQty = qty,
ShipDate = dt,
ColorCode = colorCode,
ColorName = colorName,
IsSample = isSample,
});
}
}
}
return(rlt);
}
/// <summary>
/// 更新一筆單價資訊
/// </summary>
/// <param name="item"></param>
public static void Update(UnitPriceItemModel item)
{
using (SQLiteConnection conn = new SQLiteConnection(AppSettings.ConnectString))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"Update UnitPrice SET ProductName=@p0, Price=@p1, MaterialCode=@p2, SizeInfo=@p3, Processing0=@p4, UpdatedTime=@p5, CombinedUnits=@p6, Color=@p7, PieceWeight=@p8, Package=@p9 WHERE ProductCode=@p10";
cmd.Parameters.AddWithValue("@p0", item.Name);
cmd.Parameters.AddWithValue("@p1", item.Price);
cmd.Parameters.AddWithValue("@p2", item.MaterialCode);
cmd.Parameters.AddWithValue("@p3", item.Size);
cmd.Parameters.AddWithValue("@p4", item.Processing0);
cmd.Parameters.AddWithValue("@p5", DateTime.Now);
cmd.Parameters.AddWithValue("@p6", item.CombinedUnits);
cmd.Parameters.AddWithValue("@p7", item.ColorTypes);
cmd.Parameters.AddWithValue("@p8", item.PieceWeight);
cmd.Parameters.AddWithValue("@p9", item.Package);
cmd.Parameters.AddWithValue("@p10", item.Code);
cmd.ExecuteNonQuery();
}
}
}