public void insert(SupplierPriceSheetTable record)
{
string insert = "INSERT INTO [dbo].[PURCHASE_SUPPLIER_PRICE_SHEET]([SUPPLIER_ID],[MATERIEL_ID],[ORNM_FROM_VALUE],";
insert += "[ORNM_TO_VALUE],[PRICE],[NOTE]) VALUES (";
insert += record.supplierId + ",";
insert += record.matetielId + ",";
insert += "'" + record.ORNMFromValue + "',";
insert += "'" + record.ORNMToValue + "',";
insert += record.pirce + ",";
insert += "'" + record.note + "'";
insert += ")";
try
{
DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, insert);
MessageBoxExtend.messageOK("数据保存成功");
}
catch (Exception error)
{
MessageBoxExtend.messageWarning(error.Message);
return;
}
}
public SupplierPriceSheetTable getSupplierPriceSheetInfoFromPkey(int pkey)
{
string sql = "SELECT A1.PKEY, A1.SUPPLIER_ID, A2.[NAME], A1.MATERIEL_ID, A1.ORNM_FROM_VALUE, A1.ORNM_TO_VALUE, ";
sql += "A1.PRICE, A1.DATE, A2.CONTACT, A2.TEL, A1.NOTE FROM [dbo].[PURCHASE_SUPPLIER_PRICE_SHEET] A1, ";
sql += "[BASE_SUPPLIER_LIST] A2 WHERE A1.SUPPLIER_ID = A2.PKEY AND A1.PKEY = " + pkey;
SupplierPriceSheetTable record = new SupplierPriceSheetTable();
using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql))
{
foreach (DataRow row in dataTable.Rows)
{
record.pkey = DbDataConvert.ToInt32(row["PKEY"]);
record.supplierId = DbDataConvert.ToInt32(row["SUPPLIER_ID"]);
record.supplierName = DbDataConvert.ToString(row["NAME"]);
record.matetielId = DbDataConvert.ToInt32(row["MATERIEL_ID"]);
record.ORNMFromValue = DbDataConvert.ToString(row["ORNM_FROM_VALUE"]);
record.ORNMToValue = DbDataConvert.ToString(row["ORNM_TO_VALUE"]);
record.pirce = DbDataConvert.ToString(row["PRICE"]);
record.date = DbDataConvert.ToDateTime(row["DATE"]).ToString("yyyy-MM-dd");
record.contact = DbDataConvert.ToString(row["CONTACT"]);
record.tel = DbDataConvert.ToString(row["TEL"]);
record.note = DbDataConvert.ToString(row["NOTE"]);
}
}
return(record);
}
public SortedDictionary <int, SupplierPriceSheetTable> getAllSupplierPriceSheetInfo(int matetielID)
{
string sql = "SELECT [PKEY],[SUPPLIER_ID],[MATERIEL_ID] ,[ORNM_FROM_VALUE],[ORNM_TO_VALUE],[PRICE],[DATE],[NOTE] ";
sql += "FROM [dbo].[PURCHASE_SUPPLIER_PRICE_SHEET] WHERE MATERIEL_ID = " + matetielID + " ORDER BY PKEY";
SortedDictionary <int, SupplierPriceSheetTable> tableDataList = new SortedDictionary <int, SupplierPriceSheetTable>();
using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql))
{
foreach (DataRow row in dataTable.Rows)
{
SupplierPriceSheetTable record = new SupplierPriceSheetTable();
record.pkey = DbDataConvert.ToInt32(row["PKEY"]);
record.supplierId = DbDataConvert.ToInt32(row["SUPPLIER_ID"]);
if (record.supplierId != -1)
{
SupplierTable supplier = Supplier.getInctance().getSupplierInfoFromPkey(record.supplierId);
record.supplierName = supplier.name;
record.contact = supplier.contact;
record.tel = supplier.tel;
}
record.matetielId = DbDataConvert.ToInt32(row["MATERIEL_ID"]);
record.ORNMFromValue = DbDataConvert.ToString(row["ORNM_FROM_VALUE"]);
record.ORNMToValue = DbDataConvert.ToString(row["ORNM_TO_VALUE"]);
record.pirce = DbDataConvert.ToString(row["PRICE"]);
record.date = DbDataConvert.ToDateTime(row["DATE"]).ToString("yyyy-MM-dd");
record.note = DbDataConvert.ToString(row["NOTE"]);
tableDataList.Add(tableDataList.Count, record);
}
}
return(tableDataList);
}
public void update(int pkey, SupplierPriceSheetTable record)
{
string update = "UPDATE [dbo].[PURCHASE_SUPPLIER_PRICE_SHEET] SET ";
update += "[SUPPLIER_ID] = " + record.supplierId + ",";
update += "[MATERIEL_ID] = " + record.matetielId + ",";
update += "[ORNM_FROM_VALUE] = '" + record.ORNMFromValue + "',";
update += "[ORNM_TO_VALUE] = '" + record.ORNMToValue + "',";
update += "[PRICE] = " + record.pirce + ",";
update += "[NOTE] = '" + record.note + "'";
update += " WHERE PKEY = " + Convert.ToString(pkey);
try
{
DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, update);
MessageBoxExtend.messageOK("数据修改成功");
}
catch (Exception error)
{
MessageBoxExtend.messageWarning(error.Message);
return;
}
}