public int UpdateShiftItemProductID(ShiftItemProductIDInfo oParam)
{
string sql = @"UPDATE St_Shift_Item_ProductID SET
StShiftItemSysNo=@StShiftItemSysNo, ProductIDSysNo=@ProductIDSysNo
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramStShiftItemSysNo = new SqlParameter("@StShiftItemSysNo", SqlDbType.Int, 4);
SqlParameter paramProductIDSysNo = new SqlParameter("@ProductIDSysNo", SqlDbType.Int, 4);
if (oParam.SysNo != AppConst.IntNull)
paramSysNo.Value = oParam.SysNo;
else
paramSysNo.Value = System.DBNull.Value;
if (oParam.StShiftItemSysNo != AppConst.IntNull)
paramStShiftItemSysNo.Value = oParam.StShiftItemSysNo;
else
paramStShiftItemSysNo.Value = System.DBNull.Value;
if (oParam.ProductIDSysNo != AppConst.IntNull)
paramProductIDSysNo.Value = oParam.ProductIDSysNo;
else
paramProductIDSysNo.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramStShiftItemSysNo);
cmd.Parameters.Add(paramProductIDSysNo);
return SqlHelper.ExecuteNonQuery(cmd);
}
public int InsertShiftItemProductID(ShiftItemProductIDInfo oParam)
{
string sql = @"INSERT INTO St_Shift_Item_ProductID
(
StShiftItemSysNo, ProductIDSysNo
)
VALUES (
@StShiftItemSysNo, @ProductIDSysNo
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramStShiftItemSysNo = new SqlParameter("@StShiftItemSysNo", SqlDbType.Int, 4);
SqlParameter paramProductIDSysNo = new SqlParameter("@ProductIDSysNo", SqlDbType.Int, 4);
paramSysNo.Direction = ParameterDirection.Output;
if (oParam.StShiftItemSysNo != AppConst.IntNull)
paramStShiftItemSysNo.Value = oParam.StShiftItemSysNo;
else
paramStShiftItemSysNo.Value = System.DBNull.Value;
if (oParam.ProductIDSysNo != AppConst.IntNull)
paramProductIDSysNo.Value = oParam.ProductIDSysNo;
else
paramProductIDSysNo.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramStShiftItemSysNo);
cmd.Parameters.Add(paramProductIDSysNo);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}
public ShiftItemProductIDInfo LoadShiftItemProductID(int StShiftItemSysNo, int ProductIDSysNo)
{
string sql = "select * from St_Shift_Item_ProductID where stshiftitemsysno= " + StShiftItemSysNo + " and productidsysno=" + ProductIDSysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
ShiftItemProductIDInfo oInfo = new ShiftItemProductIDInfo();
if (Util.HasMoreRow(ds))
map(oInfo, ds.Tables[0].Rows[0]);
else
oInfo = null;
return oInfo;
}
public void InsertShiftItemProductIDs(string[] ItemProductIDs)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
string ItemProductIDList = "";
foreach (string ItemProductID in ItemProductIDs)
{
int indexComma = ItemProductID.IndexOf(",");
ItemProductIDList += Int32.Parse(ItemProductID.Substring(0, indexComma)) + ",";
}
if (ItemProductIDList.Length > 0)
{
ItemProductIDList = ItemProductIDList.Substring(0, ItemProductIDList.Length - 1);
string sql = "delete from st_shift_item_productid where stshiftitemsysno in(" + ItemProductIDList + ")";
SqlHelper.ExecuteNonQuery(sql);
}
foreach (string ItemProductID in ItemProductIDs)
{
int indexComma = ItemProductID.IndexOf(",");
int lastIndexComma = ItemProductID.LastIndexOf(",");
ShiftItemProductIDInfo oInfo = new ShiftItemProductIDInfo();
oInfo.StShiftItemSysNo = Int32.Parse(ItemProductID.Substring(0, indexComma));
oInfo.ProductIDSysNo = Int32.Parse(ItemProductID.Substring(lastIndexComma + 1));
if (LoadShiftItemProductID(oInfo.StShiftItemSysNo, oInfo.ProductIDSysNo) == null)
{
this.InsertShiftItemProductID(oInfo);
}
}
scope.Complete();
}
}
public int InsertShiftItemProductID(ShiftItemProductIDInfo oParam)
{
return new ShiftDac().InsertShiftItemProductID(oParam);
}
private void map(ShiftItemProductIDInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.StShiftItemSysNo = Util.TrimIntNull(tempdr["StShiftItemSysNo"]);
oParam.ProductIDSysNo = Util.TrimIntNull(tempdr["ProductIDSysNo"]);
}
