/// <summary>
/// 查詢所有商品項目
/// </summary>
/// <param name="CTSC">傳入SIS.Configuration.ClsTakeStockConfig CTSC</param>
/// <returns></returns>
public bool QueryAllItems(SIS.Configuration.ClsTakeStockConfig CTSC)
{
int MAX = QueryItemsCount(); //先算出商品總數
InitDB();
string selectCmd;
selectCmd = "Select * From " + TableName + " Order by ItemsID";
try
{
cmd = new SqlCommand(selectCmd, conn);
dr = cmd.ExecuteReader();
int i = 0;
SIS.Configuration.TakeStockItem[] takeStockItems = new Configuration.TakeStockItem[MAX];
SIS.Configuration.TakeStockItem takeStockItem;
while (dr.Read())
{
takeStockItem = new Configuration.TakeStockItem();
takeStockItem.ItemsID = dr["ItemsID"].ToString();
takeStockItem.NAME = dr["NAME"].ToString();
takeStockItem.Inventory = int.Parse(dr["Inventory"].ToString());
takeStockItem.TakeStockInventory = takeStockItem.Inventory; //預設盤點數量 = 庫存數量
takeStockItem.GainLossInventory = takeStockItem.Inventory - takeStockItem.TakeStockInventory;
takeStockItem.IsTakeStock = false;
takeStockItem.ItemsUnit = dr["ItemsUnit"].ToString();
takeStockItem.Price = int.Parse(dr["CostPrice"].ToString());
takeStockItem.Totals = takeStockItem.TakeStockInventory * takeStockItem.Price;
takeStockItem.Notes = "";
takeStockItems[i] = takeStockItem;
i = i + 1;
}
CTSC.TakeStockItems = takeStockItems;
conn.Close();
return(true);
}
catch (Exception ex)
{
errorMsg = ex.Message;
conn.Close();
return(false);
}
}
/// <summary>
/// 查詢盤點單相關資料
/// </summary>
/// <param name="TakeStockID">傳入盤點單編號</param>
/// <param name="CTSC">SIS.Configuration.ClsTakeStockConfig CTSC</param>
/// <returns></returns>
public bool QueryData(string TakeStockID, SIS.Configuration.ClsTakeStockConfig CTSC)
{
InitDB();
string selectCmd;
selectCmd = "Select * From " + TableName + " Where TakeStockID='" + TakeStockID + "'";
try
{
cmd = new SqlCommand(selectCmd, conn);
dr = cmd.ExecuteReader();
if (dr.Read())
{
CTSC.TakeStockID = TakeStockID;
CTSC.TakeStockDate = dr["TakeStockDate"].ToString();
CTSC.TakeStockStaff = dr["TakeStockStaff"].ToString();
CTSC.Notes = dr["Notes"].ToString();
conn.Close();
SIS.DBClass.DBClassTakeStockDetails DBTSD = new DBClassTakeStockDetails();
CTSC.TakeStockItems = DBTSD.QueryData(TakeStockID);
if (CTSC.TakeStockItems == null)
{
return(false);
}
else
{
return(true);
}
}
else
{
conn.Close();
return(false);
}
}
catch (Exception ex)
{
errorMsg = ex.Message;
conn.Close();
return(false);
}
}
/// <summary>
/// 新增一筆資料
/// </summary>
/// <param name="CTSC">傳入存放更新資料的SIS.Configuration.ClsTakeStockConfig CTSC</param>
/// <returns></returns>
public bool InsertData(SIS.Configuration.ClsTakeStockConfig CTSC)
{
InitDB();
string insertCmd, insertCmd2;
insertCmd = "Insert Into " + TableName + " (TakeStockID,TakeStockDate,TakeStockStaff ,Notes";
insertCmd = insertCmd + ") Values(";
insertCmd = insertCmd + "'" + CTSC.TakeStockID + "',";
insertCmd = insertCmd + "'" + CTSC.TakeStockDate + "',";
insertCmd = insertCmd + "'" + CTSC.TakeStockStaff + "',";
insertCmd = insertCmd + "'" + CTSC.Notes + "'";
insertCmd = insertCmd + ")";
transaction = conn.BeginTransaction("MyInsertTransaction");
try
{
cmd = conn.CreateCommand(); // new SqlCommand(insertCmd, conn);
cmd.CommandText = insertCmd;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
for (int i = 0; i < CTSC.TakeStockItems.Length; i++)
{
insertCmd2 = "Insert Into " + TableName2 + " (TakeStockID ,ItemsID ,NAME ,Inventory ,TakeStockInventory ,GainLossInventory ," +
"IsTakeStock,ItemsUnit ,Price ,Totals ,Notes";
insertCmd2 = insertCmd2 + ") Values(";
insertCmd2 = insertCmd2 + "'" + CTSC.TakeStockID + "',";
insertCmd2 = insertCmd2 + "'" + CTSC.TakeStockItems[i].ItemsID + "',";
insertCmd2 = insertCmd2 + "'" + CTSC.TakeStockItems[i].NAME + "',";
insertCmd2 = insertCmd2 + "" + CTSC.TakeStockItems[i].Inventory + ",";
insertCmd2 = insertCmd2 + "" + CTSC.TakeStockItems[i].TakeStockInventory + ",";
insertCmd2 = insertCmd2 + "" + CTSC.TakeStockItems[i].GainLossInventory + ",";
insertCmd2 = insertCmd2 + "'" + CTSC.TakeStockItems[i].IsTakeStock + "',";
insertCmd2 = insertCmd2 + "'" + CTSC.TakeStockItems[i].ItemsUnit + "',";
insertCmd2 = insertCmd2 + "" + CTSC.TakeStockItems[i].Price + ",";
insertCmd2 = insertCmd2 + "" + CTSC.TakeStockItems[i].Totals + ",";
insertCmd2 = insertCmd2 + "'" + CTSC.TakeStockItems[i].Notes + "'";
insertCmd2 = insertCmd2 + ")";
cmd.CommandText = insertCmd2;//new SqlCommand(insertCmd2, conn);
cmd.ExecuteNonQuery();
}
transaction.Commit(); //try to Commit above sql command
conn.Close();
return(true);
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
errorMsg = ex2.Message;
}
errorMsg = ex.Message;
conn.Close();
return(false);
}
}
/// <summary>
/// 更新一筆資料
/// </summary>
/// <param name="CTSC">傳入存放更新資料的SIS.Configuration.ClsTakeStockConfig CTSC</param>
/// <returns></returns>
public bool Update(SIS.Configuration.ClsTakeStockConfig CTSC)
{
InitDB();
string updateCmd, updateCmd2;
updateCmd = "UPDATE " + TableName + " SET ";
updateCmd = updateCmd + " TakeStockID='" + CTSC.TakeStockID + "',";
updateCmd = updateCmd + " TakeStockDate='" + CTSC.TakeStockDate + "',";
updateCmd = updateCmd + " TakeStockStaff='" + CTSC.TakeStockStaff + "',";
updateCmd = updateCmd + " Notes='" + CTSC.Notes + "'";
updateCmd = updateCmd + " WHERE TakeStockID='" + CTSC.TakeStockID + "'";
transaction = conn.BeginTransaction("MyUpdateTransaction");
try
{
cmd = conn.CreateCommand(); // new SqlCommand(updateCmd, conn);
cmd.CommandText = updateCmd;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
for (int i = 0; i < CTSC.TakeStockItems.Length; i++)
{
updateCmd2 = "UPDATE " + TableName2 + " SET ";
updateCmd2 = updateCmd2 + " TakeStockID='" + CTSC.TakeStockID + "',";
updateCmd2 = updateCmd2 + " ItemsID='" + CTSC.TakeStockItems[i].ItemsID + "',";
updateCmd2 = updateCmd2 + " NAME='" + CTSC.TakeStockItems[i].NAME + "',";
updateCmd2 = updateCmd2 + " Inventory=" + CTSC.TakeStockItems[i].Inventory + ",";
updateCmd2 = updateCmd2 + " TakeStockInventory=" + CTSC.TakeStockItems[i].TakeStockInventory + ",";
updateCmd2 = updateCmd2 + " GainLossInventory=" + CTSC.TakeStockItems[i].GainLossInventory + ",";
updateCmd2 = updateCmd2 + " IsTakeStock='" + CTSC.TakeStockItems[i].IsTakeStock + "',";
updateCmd2 = updateCmd2 + " ItemsUnit='" + CTSC.TakeStockItems[i].ItemsUnit + "',";
updateCmd2 = updateCmd2 + " Price=" + CTSC.TakeStockItems[i].Price + ",";
updateCmd2 = updateCmd2 + " Totals=" + CTSC.TakeStockItems[i].Totals + ",";
updateCmd2 = updateCmd2 + " Notes='" + CTSC.TakeStockItems[i].Notes + "'";
updateCmd2 = updateCmd2 + " WHERE TakeStockID='" + CTSC.TakeStockID + "' And ItemsID='" + CTSC.TakeStockItems[i].ItemsID + "'";
cmd.CommandText = updateCmd2;
cmd.ExecuteNonQuery();
}
transaction.Commit(); //try to Commit above sql command
conn.Close();
return(true);
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
errorMsg = ex2.Message;
return(false);
}
errorMsg = ex.Message;
conn.Close();
return(false);
}
}
