public List <DeliveryReceiptItemInfoNew> getSelectedItems(SqlConnection connection, int id, string _userID)
{
List <DeliveryReceiptItemInfoNew> ret = new List <DeliveryReceiptItemInfoNew>();
using (var command = new SqlCommand(
"select * from [tbl_DeliveryReceipt_Items] where DeliveryReceiptID = @id"
, connection))
{
AddSqlParameter(command, "@id", id, System.Data.SqlDbType.Int);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new DeliveryReceiptItemInfoNew();
item.AutoID = GetDbReaderValue <int>(reader["AutoID"]);
item.QuoteItemID = GetDbReaderValue <int>(reader["QuoteItemID"]);
item.ItemCode = GetDbReaderValue <string>(reader["ItemCode"]);
item.ItemID = GetDbReaderValue <int>(reader["ItemID"]);
item.Amount = GetDbReaderValue <double>(reader["Amount"]);
item.ItemUnit = GetDbReaderValue <string>(reader["ItemUnit"]);
item.Description = GetDbReaderValue <string>(reader["Description"]);
item.ItemPrice = GetDbReaderValue <double>(reader["ItemPrice"]);
item.ItemName = GetDbReaderValue <string>(reader["ItemName"]);
item.TotalPrice = item.ItemPrice * item.Amount;
item.AcceptanceResult = GetDbReaderValue <bool>(reader["AcceptanceResult"]);
ret.Add(item);
}
}
}
return(ret);
}
public List <DeliveryReceiptItemInfoNew> GetItems(SqlConnection connection, int id)
{
List <DeliveryReceiptItemInfoNew> ret = new List <DeliveryReceiptItemInfoNew>();
using (var command = new SqlCommand(@"select tdi.* , i.ItemName , tdi.ItemUnit, ISNULL(tdi.ItemCode, i.ItemCode) as ItemCode from
(select * from tbl_DeliveryReceipt_Items where DeliveryReceiptID = @DeliveryReceiptID) tdi
inner join tbl_items as i on tdi.ItemID = i.ItemID
", connection))
{
AddSqlParameter(command, "@DeliveryReceiptID", id, System.Data.SqlDbType.Int);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new DeliveryReceiptItemInfoNew();
item.AutoID = GetDbReaderValue <int>(reader["AutoID"]);
item.ItemID = GetDbReaderValue <int>(reader["ItemID"]);
item.ItemCode = GetDbReaderValue <string>(reader["ItemCode"]);
item.Amount = GetDbReaderValue <double>(reader["Amount"]);
item.DeliveryReceiptID = GetDbReaderValue <int>(reader["DeliveryReceiptID"]);
item.ItemUnit = GetDbReaderValue <string>(reader["ItemUnit"]);
item.ItemName = GetDbReaderValue <string>(reader["ItemName"]);
item.ItemPrice = GetDbReaderValue <double>(reader["Price"]);
item.TotalPrice = GetDbReaderValue <double>(reader["TotalPrice"]);
ret.Add(item);
}
}
}
return(ret);
}
public void UpdateDeliveryReceiptItem(SqlConnection connection, DeliveryReceiptItemInfoNew itemObj, string userID)
{
/* string strAddNewCode =
* "UPDATE tbl_DeliveryReceipt_Items " +
* " SET ItemID = @ItemID, ItemCode = @ItemCode , ItemManufactureCountry = @ItemManufactureCountry, " +
* " ItemUnit = @ItemUnit, Amount = @Amount, Price = @Price, " +
* " TotalPrice = @TotalPrice, ShipCost = @ShipCost, TestCost = @TestCost, " +
* " ItemDocument = @ItemDocument, IsSub = @IsSub , UserU=@UserU , DeliveryNote=@DeliveryNote , ManufactureYear=@ManufactureYear , StartUseYear=@StartUseYear " +
* " WHERE AutoID = @AutoID";
*
* using (var command = new SqlCommand(strAddNewCode))
* {
* AddSqlParameter(command, "@ItemID", itemObj.ItemID, System.Data.SqlDbType.Int);
* AddSqlParameter(command, "@ItemCode", itemObj.ItemCode, System.Data.SqlDbType.VarChar);
* AddSqlParameter(command, "@ItemUnit", itemObj.ItemUnit, System.Data.SqlDbType.NVarChar);
* AddSqlParameter(command, "@Amount", itemObj.Amount, System.Data.SqlDbType.Float);
* AddSqlParameter(command, "@Price", itemObj.ItemPrice, System.Data.SqlDbType.Float);
* AddSqlParameter(command, "@TotalPrice", itemObj.TotalPrice, System.Data.SqlDbType.Float);
*
* AddSqlParameter(command, "@DeliveryReceiptID", itemObj.DeliveryReceiptID, System.Data.SqlDbType.Int);
* AddSqlParameter(command, "@UserU", itemObj.UserU, System.Data.SqlDbType.VarChar);
*
* AddSqlParameter(command, "@AutoID", itemObj.AutoID, System.Data.SqlDbType.VarChar);
* db.ExcuteScalar(connection, command);
* }*/
}
public List <DeliveryReceiptItemInfoNew> getDetailsAcceptanceByProposalID(SqlConnection connection, int id)
{
List <DeliveryReceiptItemInfoNew> ret = new List <DeliveryReceiptItemInfoNew>();
using (var command = new SqlCommand(
" select * from [tbl_DeliveryReceipt_Items] where DeliveryReceiptID in ( " +
" select DeliveryReceiptID from tbl_DeliveryReceipt where ProposalID = " + id +
" ) "
, connection))
{
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new DeliveryReceiptItemInfoNew();
item.AutoID = GetDbReaderValue <int>(reader["AutoID"]);
item.QuoteItemID = GetDbReaderValue <int>(reader["QuoteItemID"]);
item.ItemCode = GetDbReaderValue <string>(reader["ItemCode"]);
item.ItemID = GetDbReaderValue <int>(reader["ItemID"]);
item.Amount = GetDbReaderValue <double>(reader["Amount"]);
item.ItemUnit = GetDbReaderValue <string>(reader["ItemUnit"]);
item.Description = GetDbReaderValue <string>(reader["Description"]);
item.ItemPrice = GetDbReaderValue <double>(reader["ItemPrice"]);
item.ItemName = GetDbReaderValue <string>(reader["ItemName"]);
item.TotalPrice = item.ItemPrice * item.Amount;
item.AcceptanceResult = GetDbReaderValue <bool>(reader["AcceptanceResult"]);
ret.Add(item);
}
}
}
return(ret);
}
public void CreateDeliveryReceiptItem(SqlConnection connection,
DeliveryReceiptItemInfoNew itemObj, string userID)
{
/* DateTime localDate = DateTime.Now;
*
* string strAddNewCode = "Insert into tbl_DeliveryReceipt_Items " +
* " (ItemID, ItemCode,ItemManufactureCountry,ItemUnit, Amount, Price," +
* " TotalPrice , ShipCost ,TestCost ,ItemDocument , IsSub ,DeliveryReceiptID ,UserU , CreateTime , DeliveryNote , ManufactureYear , StartUseYear)" +
* "VALUES (@ItemID, @ItemCode, @ItemManufactureCountry, @ItemUnit, @Amount, @Price, " +
* " @TotalPrice , @ShipCost , @TestCost , @ItemDocument , @IsSub , @DeliveryReceiptID , @UserU , @CreateTime , @DeliveryNote , @ManufactureYear , @StartUseYear)";
*
* using (var command = new SqlCommand(strAddNewCode))
* {
* AddSqlParameter(command, "@ItemID", itemObj.ItemID, System.Data.SqlDbType.Int);
* AddSqlParameter(command, "@ItemCode", itemObj.ItemCode, System.Data.SqlDbType.VarChar);
* AddSqlParameter(command, "@ItemManufactureCountry", itemObj.ItemManufactureCountry, System.Data.SqlDbType.NVarChar);
* AddSqlParameter(command, "@DeliveryNote", itemObj.DeliveryNote, System.Data.SqlDbType.NVarChar);
* AddSqlParameter(command, "@ItemUnit", itemObj.ItemUnit, System.Data.SqlDbType.NVarChar);
* AddSqlParameter(command, "@Amount", itemObj.Amount, System.Data.SqlDbType.Float);
* AddSqlParameter(command, "@Price", itemObj.Price, System.Data.SqlDbType.Float);
* AddSqlParameter(command, "@TotalPrice", itemObj.TotalPrice, System.Data.SqlDbType.Float);
* AddSqlParameter(command, "@ShipCost", itemObj.ShipCost, System.Data.SqlDbType.Float);
* AddSqlParameter(command, "@TestCost", itemObj.TestCost, System.Data.SqlDbType.Float);
* AddSqlParameter(command, "@ItemDocument", itemObj.ItemDocument, System.Data.SqlDbType.NVarChar);
* AddSqlParameter(command, "@IsSub", itemObj.IsSub, System.Data.SqlDbType.Bit);
* AddSqlParameter(command, "@DeliveryReceiptID", itemObj.DeliveryReceiptID, System.Data.SqlDbType.Int);
* AddSqlParameter(command, "@UserU", itemObj.UserU, System.Data.SqlDbType.VarChar);
* AddSqlParameter(command, "@CreateTime", localDate, System.Data.SqlDbType.DateTime);
* AddSqlParameter(command, "@ManufactureYear", itemObj.ManufactureYear, System.Data.SqlDbType.Int);
* AddSqlParameter(command, "@StartUseYear", itemObj.StartUseYear, System.Data.SqlDbType.Int);
* db.ExcuteScalar(connection, command);
* }*/
string query = " insert into tbl_DeliveryReceipt_Items " +
"(DeliveryReceiptID , QuoteItemID, ItemName, Description ,ItemUnit ,ItemPrice , Amount , TotalPrice ) " +
" VALUES(@DeliveryReceiptID , @QuoteItemID, @ItemName, @Description , @ItemUnit , @ItemPrice , @Amount , @TotalPrice) ";
using (var command = new SqlCommand(query, connection))
{
AddSqlParameter(command, "@QuoteItemID", itemObj.QuoteItemID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@DeliveryReceiptID", itemObj.DeliveryReceiptID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@ItemName", itemObj.ItemName, System.Data.SqlDbType.NText);
AddSqlParameter(command, "@Description", itemObj.Description, System.Data.SqlDbType.NText);
AddSqlParameter(command, "@ItemUnit", itemObj.ItemUnit, System.Data.SqlDbType.NText);
AddSqlParameter(command, "@ItemPrice", itemObj.ItemPrice, System.Data.SqlDbType.Float);
AddSqlParameter(command, "@Amount", itemObj.Amount, System.Data.SqlDbType.Float);
AddSqlParameter(command, "@TotalPrice", itemObj.TotalPrice, System.Data.SqlDbType.Float);
db.ExcuteScalar(connection, command);
}
}