public DataTable GetAllStyleByYear(BuyerEnquiryModel objBuyerEnquiryModel) { DataTable dt = new DataTable(); string sql = ""; sql = "SELECT " + "TO_CHAR (NVL (BUYER_ID,'0'))BUYER_ID, " + "TO_CHAR (NVL (BUYER_NAME,'N/A'))BUYER_NAME, " + "TO_CHAR (NVL (SEASON_ID,'0'))SEASON_ID, " + "TO_CHAR (NVL (SEASON_NAME,'N/A'))SEASON_NAME, " + "TO_CHAR (NVL (SEASON_YEAR,'0'))SEASON_YEAR, " + "TO_CHAR (NVL (STYLE_NO,'N/A'))STYLE_NO " + " FROM VEW_STYLE_ENQUIRY where head_office_id = '" + objBuyerEnquiryModel.HeadOfficeId + "' AND branch_office_id = '" + objBuyerEnquiryModel.BranchOfficeId + "' "; if (!string.IsNullOrEmpty(objBuyerEnquiryModel.SeasonYear)) { sql = sql + "and SEASON_YEAR = '" + objBuyerEnquiryModel.SeasonYear + "'"; } if (!string.IsNullOrEmpty(objBuyerEnquiryModel.StyleNo)) { sql = sql + " and STYLE_NO = '" + objBuyerEnquiryModel.StyleNo + "' "; } if (!string.IsNullOrEmpty(objBuyerEnquiryModel.SeasonName)) { sql = sql + " and SEASON_NAME = '" + objBuyerEnquiryModel.SeasonName + "' "; } if (!string.IsNullOrEmpty(objBuyerEnquiryModel.BuyerName)) { sql = sql + " and BUYER_NAME = '" + objBuyerEnquiryModel.BuyerName + "' "; } sql = sql + " ORDER BY SEASON_YEAR DESC"; OracleCommand objCommand = new OracleCommand(sql); OracleDataAdapter objDataAdapter = new OracleDataAdapter(objCommand); using (OracleConnection strConn = GetConnection()) { try { objCommand.Connection = strConn; strConn.Open(); objDataAdapter.Fill(dt); dt.AcceptChanges(); } catch (Exception ex) { throw new Exception("Error : " + ex.Message); } finally { strConn.Close(); } } return(dt); }
public string DeleteBuyerEnquiryRecord(BuyerEnquiryModel objBuyerEnquiryModel) { string strMsg = ""; OracleTransaction objOracleTransaction = null; OracleCommand objOracleCommand = new OracleCommand("pro_style_enquiry_delete"); objOracleCommand.CommandType = CommandType.StoredProcedure; if (objBuyerEnquiryModel.SeasonYear != "") { objOracleCommand.Parameters.Add("P_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBuyerEnquiryModel.SeasonYear.Trim(); } else { objOracleCommand.Parameters.Add("P_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (objBuyerEnquiryModel.SeasonId != "") { objOracleCommand.Parameters.Add("P_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBuyerEnquiryModel.SeasonId.Trim(); } else { objOracleCommand.Parameters.Add("P_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (objBuyerEnquiryModel.StyleNo != "") { objOracleCommand.Parameters.Add("P_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBuyerEnquiryModel.StyleNo.Trim(); } else { objOracleCommand.Parameters.Add("P_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (objBuyerEnquiryModel.BuyerId != "") { objOracleCommand.Parameters.Add("P_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBuyerEnquiryModel.BuyerId.Trim(); } else { objOracleCommand.Parameters.Add("P_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, 100, ParameterDirection.Input).Value = objBuyerEnquiryModel.HeadOfficeId; objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, 100, ParameterDirection.Input).Value = objBuyerEnquiryModel.BranchOfficeId; 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); objOracleTransaction.Rollback(); } finally { strConn.Close(); } } return(strMsg); }
public BuyerEnquiryModel GetBuyerEnquiryById(BuyerEnquiryModel objBuyerEnquiryModel) { string sql = ""; sql = "SELECT " + "TO_CHAR (NVL (BUYER_ID,'0'))BUYER_ID, " + "TO_CHAR (NVL (SEASON_ID,'0'))SEASON_ID, " + "TO_CHAR (NVL (SEASON_YEAR,'0'))SEASON_YEAR, " + "TO_CHAR (NVL (STYLE_NO,'N/A'))STYLE_NO, " + "TO_CHAR (NVL (BRAND_ID,'0'))BRAND_ID, " + "TO_CHAR (NVL (ITEM_ID,'0'))ITEM_ID, " + "TO_CHAR (NVL (ORDER_QUANTITY,'0'))ORDER_QUANTITY, " + "TO_CHAR (NVL (FOB,'0'))FOB, " + "TO_CHAR (NVL (CURRENCY_ID,'0'))CURRENCY_ID " + " FROM VEW_STYLE_ENQUIRY where head_office_id = '" + objBuyerEnquiryModel.HeadOfficeId + "' AND branch_office_id = '" + objBuyerEnquiryModel.BranchOfficeId + "' "; if (!string.IsNullOrEmpty(objBuyerEnquiryModel.SeasonYear)) { sql = sql + " and SEASON_YEAR = '" + objBuyerEnquiryModel.SeasonYear + "' "; } if (!string.IsNullOrEmpty(objBuyerEnquiryModel.SeasonId)) { sql = sql + " and SEASON_ID = '" + objBuyerEnquiryModel.SeasonId + "' "; } if (!string.IsNullOrEmpty(objBuyerEnquiryModel.StyleNo)) { sql = sql + " and STYLE_NO = '" + objBuyerEnquiryModel.StyleNo + "' "; } if (!string.IsNullOrEmpty(objBuyerEnquiryModel.BuyerId)) { sql = sql + " and BUYER_ID = '" + objBuyerEnquiryModel.BuyerId + "' "; } using (OracleConnection objConnection = GetConnection()) { OracleCommand objCommand = new OracleCommand(sql, objConnection); objConnection.Open(); OracleDataReader objReader = objCommand.ExecuteReader(); try { while (objReader.Read()) { objBuyerEnquiryModel.BuyerId = objReader.GetString(0); objBuyerEnquiryModel.SeasonId = objReader.GetString(1); objBuyerEnquiryModel.SeasonYear = objReader.GetString(2); objBuyerEnquiryModel.StyleNo = objReader.GetString(3); objBuyerEnquiryModel.BrandId = objReader.GetString(4); objBuyerEnquiryModel.ItemId = objReader.GetString(5); objBuyerEnquiryModel.OrderQuantity = objReader.GetString(6); objBuyerEnquiryModel.FOB = objReader.GetString(7); objBuyerEnquiryModel.CurrencyTypeId = objReader.GetString(8); } } catch (Exception ex) { throw new Exception("Error : " + ex.Message); } finally { objReader.Close(); objConnection.Close(); } } return(objBuyerEnquiryModel); }
public DataTable GetBuyerEnquiryRecord(BuyerEnquiryModel objBuyerEnquiryModel) { DataTable dt = new DataTable(); string sql = ""; sql = "SELECT " + "rownum sl, " + "TO_CHAR (NVL (BUYER_ID,'0'))BUYER_ID, " + "TO_CHAR (NVL (BUYER_NAME,'N/A'))BUYER_NAME, " + "TO_CHAR (NVL (SEASON_ID,'0'))SEASON_ID, " + "TO_CHAR (NVL (SEASON_NAME,'N/A'))SEASON_NAME, " + "TO_CHAR (NVL (SEASON_YEAR,'0'))SEASON_YEAR, " + "TO_CHAR (NVL (STYLE_NO,'N/A'))STYLE_NO, " + "TO_CHAR (NVL (BRAND_ID,'0'))BRAND_ID, " + "TO_CHAR (NVL (BRAND_NAME,'N/A'))BRAND_NAME, " + "TO_CHAR (NVL (ITEM_ID,'0'))ITEM_ID, " + "TO_CHAR (NVL (ITEM_NAME,'N/A'))ITEM_NAME, " + "TO_CHAR (NVL (ORDER_QUANTITY,'0'))ORDER_QUANTITY, " + "TO_CHAR (NVL (FOB,'0'))FOB, " + "TO_CHAR (NVL (CURRENCY_ID,'0'))CURRENCY_ID, " + "TO_CHAR (NVL (CURRENCY_NAME,'N/A'))CURRENCY_NAME " + " FROM VEW_STYLE_ENQUIRY where head_office_id = '" + objBuyerEnquiryModel.HeadOfficeId + "' AND branch_office_id = '" + objBuyerEnquiryModel.BranchOfficeId + "' "; if (!string.IsNullOrEmpty(objBuyerEnquiryModel.SeasonYear)) { sql = sql + "and SEASON_YEAR = '" + objBuyerEnquiryModel.SeasonYear + "'"; } if (!string.IsNullOrEmpty(objBuyerEnquiryModel.StyleNo)) { sql = sql + " and STYLE_NO = '" + objBuyerEnquiryModel.StyleNo + "' "; } if (!string.IsNullOrEmpty(objBuyerEnquiryModel.SeasonName)) { sql = sql + " and SEASON_NAME = '" + objBuyerEnquiryModel.SeasonName + "' "; } if (!string.IsNullOrEmpty(objBuyerEnquiryModel.BuyerName)) { sql = sql + " and BUYER_NAME = '" + objBuyerEnquiryModel.BuyerName + "' "; } if (!string.IsNullOrEmpty(objBuyerEnquiryModel.SearchBy)) { sql = sql + "and (lower(SEASON_YEAR) like lower( '%" + objBuyerEnquiryModel.SearchBy + "%') or upper(SEASON_YEAR)like upper('%" + objBuyerEnquiryModel.SearchBy + "%') ) or (lower(STYLE_NO) like lower( '%" + objBuyerEnquiryModel.SearchBy + "%') or upper(STYLE_NO)like upper('%" + objBuyerEnquiryModel.SearchBy + "%') or (lower(BUYER_NAME) like lower( '%" + objBuyerEnquiryModel.SearchBy + "%') or upper(BUYER_NAME)like upper('%" + objBuyerEnquiryModel.SearchBy + "%') ) )"; } sql = sql + " ORDER BY sl"; OracleCommand objCommand = new OracleCommand(sql); OracleDataAdapter objDataAdapter = new OracleDataAdapter(objCommand); using (OracleConnection strConn = GetConnection()) { try { objCommand.Connection = strConn; strConn.Open(); objDataAdapter.Fill(dt); dt.AcceptChanges(); } catch (Exception ex) { throw new Exception("Error : " + ex.Message); } finally { strConn.Close(); } } return(dt); }
public string SaveBuyerEnquiry(BuyerEnquiryModel objBuyerEnquiryModel) { string strMsg = ""; OracleTransaction objOracleTransaction = null; OracleCommand objOracleCommand = new OracleCommand("pro_style_enquiry_save"); objOracleCommand.CommandType = CommandType.StoredProcedure; if (objBuyerEnquiryModel.BuyerId != "") { objOracleCommand.Parameters.Add("P_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBuyerEnquiryModel.BuyerId; } else { objOracleCommand.Parameters.Add("P_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBuyerEnquiryModel.SeasonId != "") { objOracleCommand.Parameters.Add("P_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBuyerEnquiryModel.SeasonId; } else { objOracleCommand.Parameters.Add("P_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (objBuyerEnquiryModel.SeasonYear != "") { objOracleCommand.Parameters.Add("P_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBuyerEnquiryModel.SeasonYear.Trim(); } else { objOracleCommand.Parameters.Add("P_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBuyerEnquiryModel.StyleNo != "") { objOracleCommand.Parameters.Add("P_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBuyerEnquiryModel.StyleNo.Trim(); } else { objOracleCommand.Parameters.Add("P_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBuyerEnquiryModel.BrandId != "") { objOracleCommand.Parameters.Add("P_BRAND_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBuyerEnquiryModel.BrandId; } else { objOracleCommand.Parameters.Add("P_BRAND_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBuyerEnquiryModel.ItemId != "") { objOracleCommand.Parameters.Add("P_ITEM_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBuyerEnquiryModel.ItemId; } else { objOracleCommand.Parameters.Add("P_ITEM_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBuyerEnquiryModel.OrderQuantity != "") { objOracleCommand.Parameters.Add("P_ORDER_QUANTITY", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBuyerEnquiryModel.OrderQuantity.Trim(); } else { objOracleCommand.Parameters.Add("P_ORDER_QUANTITY", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBuyerEnquiryModel.FOB != "") { objOracleCommand.Parameters.Add("P_FOB", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBuyerEnquiryModel.FOB.Trim(); } else { objOracleCommand.Parameters.Add("P_FOB", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBuyerEnquiryModel.CurrencyTypeId != "") { objOracleCommand.Parameters.Add("P_CURRENCY_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBuyerEnquiryModel.CurrencyTypeId; } else { objOracleCommand.Parameters.Add("P_FOB", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } objOracleCommand.Parameters.Add("p_update_by", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBuyerEnquiryModel.UpdateBy; objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBuyerEnquiryModel.HeadOfficeId; objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBuyerEnquiryModel.BranchOfficeId; 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); objOracleTransaction.Rollback(); } finally { strConn.Close(); } } return(strMsg); }