public static bool Update(SqlConnection connection, SO_Header_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_SO_Header SET
SO_Number = @SO_Number
,idPOHeader = @idPOHeader
,idCustomer = @idCustomer
,Due_Date = @Due_Date
,Customer_PO = @Customer_PO
,Salesman = @Salesman
,Ship_Code = @Ship_Code
,Gross_Amount = @Gross_Amount
,Final_Discount = @Final_Discount
,Freight_Charges = @Freight_Charges
,Other_Charges = @Other_Charges
,Net_Amount = @Net_Amount
,Tax_Amount = @Tax_Amount
,credit_term = @credit_term
,Remarks = @Remarks
,idSite = @idSite
,currency_code = @currency_code
,Special_Concession = @Special_Concession
,Stock_Status = @Stock_Status
,End_User = @End_User
,End_User_City = @End_User_City
,Forex_Rate = @Forex_Rate
WHERE idSOHeader = @idSOHeader
");
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idSOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idSOHeader
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@SO_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.SO_Number
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@idPOHeader",
SqlDbType = SqlDbType.Int,
Value = model.idPOHeader
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@idCustomer",
SqlDbType = SqlDbType.Int,
Value = model.idCustomer
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter();
parm5.ParameterName = "@Forex_Rate";
parm5.SqlDbType = SqlDbType.Decimal;
parm5.Value = model.Forex_Rate;
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@Due_Date",
SqlDbType = SqlDbType.DateTime,
Value = model.Due_Date
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@Customer_PO",
SqlDbType = SqlDbType.NVarChar,
Value = model.Customer_PO
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@Salesman",
SqlDbType = SqlDbType.NVarChar,
Value = model.Salesman
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@Ship_Code",
SqlDbType = SqlDbType.Int,
Value = model.Ship_Code
};
cmd.Parameters.Add(parm9);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@Gross_Amount",
SqlDbType = SqlDbType.Decimal,
Value = model.Gross_Amount
};
cmd.Parameters.Add(parm11);
SqlParameter parm12 = new SqlParameter
{
ParameterName = "@Final_Discount",
SqlDbType = SqlDbType.Decimal,
Value = model.Final_Discount
};
cmd.Parameters.Add(parm12);
SqlParameter parm13 = new SqlParameter
{
ParameterName = "@Freight_Charges",
SqlDbType = SqlDbType.Decimal,
Value = model.Freight_Charges
};
cmd.Parameters.Add(parm13);
SqlParameter parm14 = new SqlParameter
{
ParameterName = "@Other_Charges",
SqlDbType = SqlDbType.Decimal,
Value = model.Other_Charges
};
cmd.Parameters.Add(parm14);
SqlParameter parm15 = new SqlParameter
{
ParameterName = "@Net_Amount",
SqlDbType = SqlDbType.Decimal,
Value = model.Net_Amount
};
cmd.Parameters.Add(parm15);
SqlParameter parm16 = new SqlParameter
{
ParameterName = "@Tax_Amount",
SqlDbType = SqlDbType.Decimal,
Value = model.Tax_Amount
};
cmd.Parameters.Add(parm16);
SqlParameter parm17 = new SqlParameter
{
ParameterName = "@credit_term",
SqlDbType = SqlDbType.NVarChar,
Value = model.credit_term
};
cmd.Parameters.Add(parm17);
SqlParameter parm18 = new SqlParameter
{
ParameterName = "@Remarks",
SqlDbType = SqlDbType.NVarChar,
Value = model.Remarks
};
cmd.Parameters.Add(parm18);
SqlParameter parm19 = new SqlParameter
{
ParameterName = "@idSite",
SqlDbType = SqlDbType.Int,
Value = model.idSite
};
cmd.Parameters.Add(parm19);
SqlParameter parm20 = new SqlParameter
{
ParameterName = "@currency_code",
SqlDbType = SqlDbType.NVarChar,
Value = model.currency_code
};
cmd.Parameters.Add(parm20);
SqlParameter parm21 = new SqlParameter
{
ParameterName = "@Special_Concession",
SqlDbType = SqlDbType.NVarChar,
Value = model.Special_Concession
};
cmd.Parameters.Add(parm21);
SqlParameter parm22 = new SqlParameter
{
ParameterName = "@Stock_Status",
SqlDbType = SqlDbType.NVarChar,
Value = model.Stock_Status
};
cmd.Parameters.Add(parm22);
SqlParameter parm25 = new SqlParameter
{
ParameterName = "@End_User",
SqlDbType = SqlDbType.NVarChar,
Value = model.End_User
};
cmd.Parameters.Add(parm25);
SqlParameter parm26 = new SqlParameter
{
ParameterName = "@End_User_City",
SqlDbType = SqlDbType.NVarChar,
Value = model.End_User_City
};
cmd.Parameters.Add(parm26);
try
{
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static List <SO_Header_Model> RetrieveData(SqlConnection connection, string SO_Number, bool forPicking)
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"SELECT idSOHeader
,SO_Number
,idPOHeader
,Order_Date
,Due_Date
,Customer_PO
,Salesman
,idCustomer
,Ship_Code
,Gross_Amount
,Final_Discount
,Freight_Charges
,Other_Charges
,Net_Amount
,Tax_Amount
,credit_term
,transaction_ID
,Remarks
,idSite
,currency_code
,Pick_Status
,Special_Concession
,SO_Status
,Stock_Status
,Active
,CreatedBy
,End_User
,End_User_City
,Forex_Rate
FROM a_SO_Header
WHERE idSOHeader <> 0
");
if (SO_Number != "")
{
sQuery.Append(" AND SO_Number = @SO_Number ");
}
if (forPicking == true)
{
sQuery.Append(" AND Pick_Status = 'N' ");
}
var lmodel = new List <SO_Header_Model>();
connection.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
if (SO_Number != "")
{
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@SO_Number",
SqlDbType = SqlDbType.NVarChar,
Value = SO_Number.Trim()
};
cmd.Parameters.Add(parm2);
}
var oreader = cmd.ExecuteReader();
while (oreader.Read())
{
SO_Header_Model oModel = new SO_Header_Model
{
idSOHeader = (int)oreader["idSOHeader"],
SO_Number = (string)oreader["SO_Number"],
idPOHeader = (int)oreader["idPOHeader"],
Order_Date = (DateTime)oreader["Order_Date"],
Due_Date = (DateTime)oreader["Due_Date"],
Customer_PO = (string)oreader["Customer_PO"],
Salesman = (string)oreader["Salesman"],
idCustomer = (int)oreader["idCustomer"],
Ship_Code = (int)oreader["Ship_Code"],
Gross_Amount = (decimal)oreader["Gross_Amount"],
Final_Discount = (decimal)oreader["Final_Discount"],
Freight_Charges = (decimal)oreader["Freight_Charges"],
Other_Charges = (decimal)oreader["Other_Charges"],
Net_Amount = (decimal)oreader["Net_Amount"],
Tax_Amount = (decimal)oreader["Tax_Amount"],
credit_term = (string)oreader["credit_term"],
transaction_ID = (string)oreader["transaction_ID"],
Remarks = (string)oreader["Remarks"],
idSite = (int)oreader["idSite"],
currency_code = (string)oreader["currency_code"],
Pick_Status = (string)oreader["Pick_Status"],
Special_Concession = (string)oreader["Special_Concession"],
SO_Status = (string)oreader["SO_Status"],
Stock_Status = (string)oreader["Stock_Status"],
CreatedBy = (string)oreader["CreatedBy"],
End_User = (string)oreader["End_User"],
End_User_City = (string)oreader["End_User_City"],
Forex_Rate = (decimal)oreader["Forex_Rate"]
};
lmodel.Add(oModel);
}
oreader.Close();
cmd.Dispose();
}
connection.Close();
return(lmodel);
}