public TechpackFileUploadModel ViewPdfFile(TechpackFileUploadModel objTechpackFileUploadModel) { if (objTechpackFileUploadModel.TranId != null && objTechpackFileUploadModel.StyleNo != null && objTechpackFileUploadModel.SeasonId != null && objTechpackFileUploadModel.BuyerId != null && objTechpackFileUploadModel.SeasonYear != null) { using (OracleConnection strConn = GetConnection()) { using (OracleCommand cmd = new OracleCommand()) { cmd.CommandText = "SELECT FILE_SIZE,FILE_NAME FROM TECH_PACK_UPLOAD_SUB WHERE TRAN_ID ='" + objTechpackFileUploadModel.TranId + "' and STYLE_NO ='" + objTechpackFileUploadModel.StyleNo + "' and SEASON_ID ='" + objTechpackFileUploadModel.SeasonId + "' and BUYER_ID ='" + objTechpackFileUploadModel.BuyerId + "' and SEASON_YEAR ='" + objTechpackFileUploadModel.SeasonYear + "' and UPLOAD_DATE =TO_DATE('" + objTechpackFileUploadModel.UploadDate + "','dd/mm/yyyy') "; cmd.Connection = strConn; strConn.Open(); using (OracleDataReader sdr = cmd.ExecuteReader()) { sdr.Read(); objTechpackFileUploadModel.bytes = (byte[])sdr["FILE_SIZE"]; objTechpackFileUploadModel.FileName = sdr["FILE_NAME"].ToString(); } strConn.Close(); } } } return(objTechpackFileUploadModel); }
//datewise all file show public DataTable DateWiseTechpackFileUpload(TechpackFileUploadModel objTechpackFileUploadModel) { DataTable dt = new DataTable(); string sql = ""; sql = "SELECT " + "rownum sl, " + "TO_CHAR (NVL (TRAN_ID,'0'))TRAN_ID, " + "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 (FILE_NAME,'N/A'))FILE_NAME, " + "TO_CHAR(UPLOAD_DATE, 'dd/mm/yyyy')UPLOAD_DATE " + " FROM VEW_TECHPACK_UPLOAD_SUB_RECORD where head_office_id = '" + objTechpackFileUploadModel.HeadOfficeId + "' AND branch_office_id = '" + objTechpackFileUploadModel.BranchOfficeId + "' "; if (!string.IsNullOrEmpty(objTechpackFileUploadModel.StyleNo)) { sql = sql + " and STYLE_NO = '" + objTechpackFileUploadModel.StyleNo + "' "; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.SeasonId)) { sql = sql + " and SEASON_ID = '" + objTechpackFileUploadModel.SeasonId + "' "; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.SeasonYear)) { sql = sql + " and SEASON_YEAR = '" + objTechpackFileUploadModel.SeasonYear + "' "; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.BuyerId)) { sql = sql + " and BUYER_ID = '" + objTechpackFileUploadModel.BuyerId + "' "; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.UploadDate)) { sql = sql + " and UPLOAD_DATE = TO_DATE('" + objTechpackFileUploadModel.UploadDate + "','dd/mm/yyyy') "; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.SearchBy)) { sql = sql + "and (lower(SEASON_YEAR) like lower( '%" + objTechpackFileUploadModel.SearchBy + "%') or upper(SEASON_YEAR)like upper('%" + objTechpackFileUploadModel.SearchBy + "%') ) or (lower(STYLE_NO) like lower( '%" + objTechpackFileUploadModel.SearchBy + "%') or upper(STYLE_NO)like upper('%" + objTechpackFileUploadModel.SearchBy + "%') or (lower(BUYER_NAME) like lower( '%" + objTechpackFileUploadModel.SearchBy + "%') or upper(BUYER_NAME)like upper('%" + objTechpackFileUploadModel.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 DataTable SearchStyleFromTechpackUpload(TechpackFileUploadModel objTechpackFileUploadModel) { DataTable dt1 = 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(UPLOAD_DATE, 'dd/mm/yyyy')UPLOAD_DATE, " + "TO_CHAR (NVL (UPLOAD_FILES,'0'))UPLOAD_FILES " + " FROM VEW_TECHPACK_UPLOAD_RECORD where head_office_id = '" + objTechpackFileUploadModel.HeadOfficeId + "' AND branch_office_id = '" + objTechpackFileUploadModel.BranchOfficeId + "' "; if (!string.IsNullOrEmpty(objTechpackFileUploadModel.StyleNo)) { sql = sql + " and STYLE_NO = '" + objTechpackFileUploadModel.StyleNo + "' "; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.SeasonName)) { sql = sql + " and SEASON_NAME = '" + objTechpackFileUploadModel.SeasonName + "' "; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.SeasonYear)) { sql = sql + " and SEASON_YEAR = '" + objTechpackFileUploadModel.SeasonYear + "' "; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.BuyerName)) { sql = sql + " and BUYER_NAME = '" + objTechpackFileUploadModel.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(dt1); dt1.AcceptChanges(); } catch (Exception ex) { throw new Exception("Error : " + ex.Message); } finally { strConn.Close(); } } return(dt1); }
public string DeleteTechpackUploadFile(TechpackFileUploadModel objTechpackFileUploadModel) { string strMsg = ""; OracleTransaction objOracleTransaction = null; OracleCommand objOracleCommand = new OracleCommand("pro_techpack_file_delete"); objOracleCommand.CommandType = CommandType.StoredProcedure; if (!string.IsNullOrEmpty(objTechpackFileUploadModel.TranId)) { objOracleCommand.Parameters.Add("P_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.TranId; } else { objOracleCommand.Parameters.Add("P_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.BuyerId)) { objOracleCommand.Parameters.Add("P_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.BuyerId; } else { objOracleCommand.Parameters.Add("P_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.SeasonId)) { objOracleCommand.Parameters.Add("P_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.SeasonId; } else { objOracleCommand.Parameters.Add("P_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.SeasonYear)) { objOracleCommand.Parameters.Add("P_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.SeasonYear; } else { objOracleCommand.Parameters.Add("P_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.StyleNo)) { objOracleCommand.Parameters.Add("STYLE_NO", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.StyleNo; } else { objOracleCommand.Parameters.Add("STYLE_NO", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (!string.IsNullOrEmpty(objTechpackFileUploadModel.UploadDate)) { objOracleCommand.Parameters.Add("UPLOAD_DATE", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.UploadDate; } else { objOracleCommand.Parameters.Add("UPLOAD_DATE", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } objOracleCommand.Parameters.Add("p_update_by", OracleDbType.Varchar2, 100, ParameterDirection.Input).Value = objTechpackFileUploadModel.UpdateBy; objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, 100, ParameterDirection.Input).Value = objTechpackFileUploadModel.HeadOfficeId; objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, 100, ParameterDirection.Input).Value = objTechpackFileUploadModel.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 string SaveTechpackFileUpload(TechpackFileUploadModel objTechpackFileUploadModel) { string strMsg = ""; OracleTransaction objOracleTransaction = null; OracleCommand objOracleCommand = new OracleCommand("pro_techpack_upload_save"); objOracleCommand.CommandType = CommandType.StoredProcedure; if (objTechpackFileUploadModel.BuyerId != "") { objOracleCommand.Parameters.Add("P_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.BuyerId; } else { objOracleCommand.Parameters.Add("P_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (objTechpackFileUploadModel.SeasonId != "") { objOracleCommand.Parameters.Add("P_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.SeasonId; } else { objOracleCommand.Parameters.Add("P_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (objTechpackFileUploadModel.SeasonYear != "") { objOracleCommand.Parameters.Add("P_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.SeasonYear; } else { objOracleCommand.Parameters.Add("P_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (objTechpackFileUploadModel.StyleNo != "") { objOracleCommand.Parameters.Add("P_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.StyleNo; } else { objOracleCommand.Parameters.Add("P_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (objTechpackFileUploadModel.UploadDate != "") { objOracleCommand.Parameters.Add("P_UPLOAD_DATE", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.UploadDate; } else { objOracleCommand.Parameters.Add("P_UPLOAD_DATE", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (objTechpackFileUploadModel.FileName != "") { objOracleCommand.Parameters.Add("p_file_name", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.FileName; } else { objOracleCommand.Parameters.Add("p_file_name", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (objTechpackFileUploadModel.CVSize != null) { byte[] array = System.Convert.FromBase64String(objTechpackFileUploadModel.CVSize); objOracleCommand.Parameters.Add("P_FILE_SIZE", OracleDbType.Blob, ParameterDirection.Input).Value = array; //objOracleCommand.Parameters.Add("P_FILE_SIZE", OracleDbType.Clob, ParameterDirection.Input).Value = array; } else { objOracleCommand.Parameters.Add("P_FILE_SIZE", OracleDbType.Clob, ParameterDirection.Input).Value = null; } if (objTechpackFileUploadModel.FileExtension != "") { objOracleCommand.Parameters.Add("P_FILE_EXTENSION", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTechpackFileUploadModel.FileExtension; } else { objOracleCommand.Parameters.Add("P_FILE_EXTENSION", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } objOracleCommand.Parameters.Add("p_update_by", OracleDbType.Varchar2, 100, ParameterDirection.Input).Value = objTechpackFileUploadModel.UpdateBy; objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, 100, ParameterDirection.Input).Value = objTechpackFileUploadModel.HeadOfficeId; objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, 100, ParameterDirection.Input).Value = objTechpackFileUploadModel.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); }