/// <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(); } } }