public List <PoComments> GetPoCommentList(string seasonId, string seasonYear, string styleNumber, string headOfficeId, string branchOfficeId)
{
List <PoComments> objPoCommentses = new List <PoComments>();
var sql = "SELECT " +
"TRAN_ID, " +
"PO_NO, " +
"STYLE_NO, " +
"SEASON_ID, " +
"SEASON_YEAR, " +
"PO_COMMENT, " +
"HEAD_OFFICE_ID, " +
"BRANCH_OFFICE_ID " +
" FROM VIEW_PO_COMMENT where SEASON_ID = '" + seasonId.Trim() + "' AND SEASON_YEAR = '" + seasonYear.Trim() + "' AND STYLE_NO = '" + styleNumber.Trim() + "' AND HEAD_OFFICE_ID = '" + headOfficeId + "' AND BRANCH_OFFICE_ID = '" + branchOfficeId + "' ";
OracleCommand objCommand = new OracleCommand(sql);
OracleDataReader objDataReader;
using (OracleConnection strConn = GetConnection())
{
objCommand.Connection = strConn;
strConn.Open();
objDataReader = objCommand.ExecuteReader();
try
{
while (objDataReader.Read())
{
PoComments model = new PoComments();
model.TranId = objDataReader.GetValue(0).ToString();
model.PoNumber = objDataReader.GetValue(1).ToString();
model.StyleNo = objDataReader.GetValue(2).ToString();
model.SeasoneId = objDataReader.GetValue(3).ToString();
model.SeasoneYear = objDataReader.GetValue(4).ToString();
model.PoComment = objDataReader.GetValue(5).ToString();
model.HeadOfficeId = objDataReader.GetValue(6).ToString();
model.BranchOfficeId = objDataReader.GetValue(7).ToString();
objPoCommentses.Add(model);
}
}
catch (Exception ex)
{
throw new Exception("Error : " + ex.Message);
}
finally
{
strConn.Close();
}
}
return(objPoCommentses);
}
public string SavePoComment(PoComments objPoComments)
{
string strMsg;
OracleCommand objOracleCommand = new OracleCommand("pro_PO_COMMENT_SAVE");
objOracleCommand.CommandType = CommandType.StoredProcedure;
objOracleCommand.Parameters.Add("P_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.TranId) ? objPoComments.TranId : null;
objOracleCommand.Parameters.Add("P_PO_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.PoNumber) ? objPoComments.PoNumber : null;
objOracleCommand.Parameters.Add("P_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.StyleNo) ? objPoComments.StyleNo : null;
objOracleCommand.Parameters.Add("P_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.SeasoneId) ? objPoComments.SeasoneId : null;
objOracleCommand.Parameters.Add("P_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.SeasoneYear) ? objPoComments.SeasoneYear : null;
objOracleCommand.Parameters.Add("P_PO_COMMENT", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.PoComment) ? objPoComments.PoComment : null;
objOracleCommand.Parameters.Add("P_UPDATE_BY", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.UpdateBy) ? objPoComments.UpdateBy : null;
objOracleCommand.Parameters.Add("P_HEAD_OFFICE_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.HeadOfficeId) ? objPoComments.HeadOfficeId : null;
objOracleCommand.Parameters.Add("P_BRANCH_OFFICE_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.BranchOfficeId) ? objPoComments.BranchOfficeId : null;
objOracleCommand.Parameters.Add("P_MESSAGE", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output;
using (OracleConnection strConn = GetConnection())
{
try
{
objOracleCommand.Connection = strConn;
strConn.Open();
trans = strConn.BeginTransaction();
objOracleCommand.ExecuteNonQuery();
trans.Commit();
strConn.Close();
strMsg = objOracleCommand.Parameters["P_MESSAGE"].Value.ToString();
}
catch (Exception ex)
{
throw new Exception("Error : " + ex.Message);
}
finally
{
strConn.Close();
}
}
return(strMsg);
}
public string PoCommentDelete(PoComments objPoComments)
{
string strMsg;
OracleCommand objOracleCommand = new OracleCommand("pro_delete_po_comment");
objOracleCommand.CommandType = CommandType.StoredProcedure;
objOracleCommand.Parameters.Add("p_tran_id", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.TranId) ? objPoComments.TranId : null;
objOracleCommand.Parameters.Add("p_style_no", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.StyleNo) ? objPoComments.StyleNo : null;
objOracleCommand.Parameters.Add("p_season_year", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.SeasoneYear) ? objPoComments.SeasoneYear : null;
objOracleCommand.Parameters.Add("p_season_id", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.SeasoneId) ? objPoComments.SeasoneId : null;
objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.HeadOfficeId) ? objPoComments.HeadOfficeId : null;
objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objPoComments.BranchOfficeId) ? objPoComments.BranchOfficeId : null;
objOracleCommand.Parameters.Add("P_MESSAGE", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output;
using (OracleConnection strConn = GetConnection())
{
try
{
objOracleCommand.Connection = strConn;
strConn.Open();
trans = strConn.BeginTransaction();
objOracleCommand.ExecuteNonQuery();
trans.Commit();
strConn.Close();
strMsg = objOracleCommand.Parameters["P_MESSAGE"].Value.ToString();
}
catch (Exception ex)
{
throw new Exception("Error : " + ex.Message);
}
finally
{
strConn.Close();
}
}
return(strMsg);
}
