public string DeleteTrimsAccEntry(TrimsAccessoriesOrderModel objAccessoriesOrderModel) { string vMessage = ""; OracleCommand objCommand = new OracleCommand("PRO_TRIMS_ACCESSORIES_DELETE") { CommandType = CommandType.StoredProcedure }; objCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = !string.IsNullOrWhiteSpace(objAccessoriesOrderModel.GridTranId) ? objAccessoriesOrderModel.GridTranId : null; //objCommand.Parameters.Add("p_UPDATE_BY", OracleDbType.Varchar2, ParameterDirection.Input).Value = objFabricModel.UpdateBy; objCommand.Parameters.Add("p_HEAD_OFFICE_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.HeadOfficeId; objCommand.Parameters.Add("p_BRANCH_OFFICE_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.BranchOfficeId; objCommand.Parameters.Add("p_MESSAGE", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output; using (OracleConnection objConnection = GetConnection()) { try { objCommand.Connection = objConnection; objConnection.Open(); trans = objConnection.BeginTransaction(); objCommand.ExecuteNonQuery(); trans.Commit(); objConnection.Close(); vMessage = objCommand.Parameters["P_MESSAGE"].Value.ToString(); } catch (Exception ex) { trans.Rollback(); throw new Exception("Error : " + ex.Message); } finally { trans.Dispose(); objConnection.Close(); } } return(vMessage); }
//after adding three datetime column public string AuthorizeTrimsAcc(TrimsAccessoriesOrderModel objAccessoriesOrderModel) { string strMsg = ""; OracleCommand objOracleCommand = new OracleCommand("pro_trims_acc_authorize"); objOracleCommand.CommandType = CommandType.StoredProcedure; //if (objAccessoriesOrderModel.InHouseDate != "") if (!string.IsNullOrEmpty(objAccessoriesOrderModel.InHouseDate)) { objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.GridTranId != "" ? objAccessoriesOrderModel.GridTranId : null; objOracleCommand.Parameters.Add("P_SupplierDate", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.SupplierDate != "" ? objAccessoriesOrderModel.SupplierDate : null; objOracleCommand.Parameters.Add("P_RevisedDate", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.RevisedDate ?? null; objOracleCommand.Parameters.Add("P_InHouseDate", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.InHouseDate ?? null; objOracleCommand.Parameters.Add("P_Pending_YN", OracleDbType.Varchar2, ParameterDirection.Input).Value = 'Y'; } else { objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.GridTranId != "" ? objAccessoriesOrderModel.GridTranId : null; objOracleCommand.Parameters.Add("P_SupplierDate", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.SupplierDate != "" ? objAccessoriesOrderModel.SupplierDate : null; objOracleCommand.Parameters.Add("P_RevisedDate", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.RevisedDate ?? null; objOracleCommand.Parameters.Add("P_InHouseDate", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.InHouseDate ?? null; objOracleCommand.Parameters.Add("P_Pending_YN", OracleDbType.Varchar2, ParameterDirection.Input).Value = 'N'; } //if (objAccessoriesOrderModel.InHouseDate != null) //{ // objOracleCommand.Parameters.Add("P_InHouseDate", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.InHouseDate; //} //else //{ // objOracleCommand.Parameters.Add("P_InHouseDate", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; //} objOracleCommand.Parameters.Add("p_update_by", OracleDbType.Varchar2, 100, ParameterDirection.Input).Value = objAccessoriesOrderModel.UpdateBy; objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, 100, ParameterDirection.Input).Value = objAccessoriesOrderModel.HeadOfficeId; objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, 100, ParameterDirection.Input).Value = objAccessoriesOrderModel.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) { trans.Rollback(); throw new Exception("Error : " + ex.Message); } finally { strConn.Close(); } } return(strMsg); }
public string SaveTrimsEntry(TrimsAccessoriesOrderModel objAccessoriesOrderModel) { string strMsg = ""; int x = objAccessoriesOrderModel.ItemCode.Count(); for (int i = 0; i < x; i++) { var arrayOrderDate = objAccessoriesOrderModel.OrderDate[i]; var arrayItemId = objAccessoriesOrderModel.ItemId[i]; var arrayItemCode = objAccessoriesOrderModel.ItemCode[i]; var arrayOrderQty = objAccessoriesOrderModel.OrderQty[i]; var arrayUnitId = objAccessoriesOrderModel.UnitId[i]; var arrayDeliveryDate = objAccessoriesOrderModel.DeliveryDate[i]; var arrayRemarks = objAccessoriesOrderModel.Remarks[i]; var arrayTranId = objAccessoriesOrderModel.TranId[i]; var arraySupplierId = objAccessoriesOrderModel.SupplierId[i]; var arrayStoreId = objAccessoriesOrderModel.StoreId[i]; var arrayUnitPrice = objAccessoriesOrderModel.UnitPrice[i]; var arrayStyleNo = objAccessoriesOrderModel.SyleNo[i]; OracleTransaction objOracleTransaction = null; OracleCommand objOracleCommand = new OracleCommand("pro_trims_accessories_save"); objOracleCommand.CommandType = CommandType.StoredProcedure; //p_arrayOrderDate p_arrayItemId p_arrayItemCode p_arrayOrderQty p_arrayUnitId p_arrayDeliveryDate p_arrayRemarks p_TRAN_ID objOracleCommand.Parameters.Add("p_arrayOrderDate", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayOrderDate.Length > 0 ? arrayOrderDate : null; objOracleCommand.Parameters.Add("p_arrayItemId", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayItemId.Length > 0 ? arrayItemId : null; objOracleCommand.Parameters.Add("p_arrayItemCode", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayItemCode.Length > 0 ? arrayItemCode : null; objOracleCommand.Parameters.Add("p_arrayOrderQty", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayOrderQty.Length > 0 ? arrayOrderQty : null; objOracleCommand.Parameters.Add("p_arrayUnitId", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayUnitId.Length > 0 ? arrayUnitId : null; objOracleCommand.Parameters.Add("p_arrayDeliveryDate", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayDeliveryDate.Length > 0 ? arrayDeliveryDate : null; objOracleCommand.Parameters.Add("p_arrayRemarks", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayRemarks.Length > 0 ? arrayRemarks : null; objOracleCommand.Parameters.Add("p_SupplierId", OracleDbType.Varchar2, ParameterDirection.Input).Value = arraySupplierId.Length > 0 ? arraySupplierId : null; objOracleCommand.Parameters.Add("p_StoreId", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayStoreId.Length > 0 ? arrayStoreId : null; objOracleCommand.Parameters.Add("p_UnitPrice", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayUnitPrice.Length > 0 ? arrayUnitPrice : null; objOracleCommand.Parameters.Add("p_StyleNo", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayStyleNo.Length > 0 ? arrayStyleNo : null; objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayTranId.Length > 0 ? arrayTranId : null; objOracleCommand.Parameters.Add("p_update_by", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.UpdateBy; objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.HeadOfficeId; objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objAccessoriesOrderModel.BranchOfficeId; // objOracleCommand.Parameters.Add("p_create_by", OracleDbType.Varchar2, ParameterDirection.Input).Value = objTrimsModel.UpdateBy; 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) { objOracleTransaction.Rollback(); throw new Exception("Error : " + ex.Message); } finally { strConn.Close(); } } } return(strMsg); }
public DataTable GetTrimsRecord(TrimsAccessoriesOrderModel objAccessoriesOrderModel) { //Master DataTable dt1 = new DataTable(); string sql = ""; sql = "SELECT " + "ROWNUM SL, " + "TO_CHAR(ORDER_DATE, 'dd/mm/yyyy') ORDER_DATE, " + "ITEM_ID, " + "ITEM_NAME, " + "ITEM_CODE, " + "ORDER_QUANTITY, " + "UNIT_ID, " + "UNIT_NAME, " + "TO_CHAR(DELIVERY_DATE, 'dd/mm/yyyy') DELIVERY_DATE, " + "REMARKS, " + "SUPPLIER_ID, " + "SUPPLIER_NAME, " + "STORE_ID, " + "STORE_NAME, " + "UNIT_PRICE, " + "STYLE_NO, " + "PENDING_YN, " + //new update for confirmation on 12 Dec 18 "TO_CHAR(SUPPLIER_DATE, 'dd/mm/yyyy') SUPPLIER_DATE," + "TO_CHAR(REVISED_DATE, 'dd/mm/yyyy') REVISED_DATE," + "TO_CHAR(IN_HOUSE_DATE, 'dd/mm/yyyy') IN_HOUSE_DATE," + "TRAN_ID, " + "UPDATE_BY, " + "UPDATE_DATE, " + "CREATE_BY, " + "CREATE_DATE, " + "HEAD_OFFICE_ID, " + "BRANCH_OFFICE_ID " + "FROM VEW_TRIMS_ACCESSORIES_ORDER WHERE HEAD_OFFICE_ID = '" + objAccessoriesOrderModel.HeadOfficeId + "' AND BRANCH_OFFICE_ID = '" + objAccessoriesOrderModel.BranchOfficeId + "' "; //"FROM VEW_BILL_OF_MATERIAL_MAIN WHERE HEAD_OFFICE_ID = '" + objTrimsModel.HeadOfficeId + "' AND BRANCH_OFFICE_ID = '" + objTrimsModel.BranchOfficeId + "' "; if (!string.IsNullOrWhiteSpace(objAccessoriesOrderModel.SearchBy)) { sql = sql + "and ((lower(ITEM_NAME) like lower( '%" + objAccessoriesOrderModel.SearchBy.Trim() + "%') " + "or upper(ITEM_NAME)like upper('%" + objAccessoriesOrderModel.SearchBy.Trim() + "%')) " + "or (TO_CHAR (ORDER_DATE, 'dd/mm/yyyy') like '" + objAccessoriesOrderModel.SearchBy.Trim() + "' or TO_CHAR (ORDER_DATE, 'dd/mm/yyyy') like '" + objAccessoriesOrderModel.SearchBy.Trim() + "')" + "or (lower(STORE_NAME) like lower( '%" + objAccessoriesOrderModel.SearchBy.Trim() + "%') " + "or upper(STORE_NAME)like upper('%" + objAccessoriesOrderModel.SearchBy.Trim() + "%')) " + "or (TO_CHAR (DELIVERY_DATE, 'dd/mm/yyyy') like '" + objAccessoriesOrderModel.SearchBy.Trim() + "' or TO_CHAR (DELIVERY_DATE, 'dd/mm/yyyy') like '" + objAccessoriesOrderModel.SearchBy.Trim() + "')" + "or (lower(ITEM_NAME) like lower( '%" + objAccessoriesOrderModel.SearchBy.Trim() + "%') " + "or upper(ITEM_NAME)like upper('%" + objAccessoriesOrderModel.SearchBy.Trim() + "%')) " + "or (lower(SUPPLIER_NAME) like lower( '%" + objAccessoriesOrderModel.SearchBy.Trim() + "%') " + "or upper(SUPPLIER_NAME)like upper('%" + objAccessoriesOrderModel.SearchBy.Trim() + "%')) " + "or (lower(STYLE_NO) like lower( '%" + objAccessoriesOrderModel.SearchBy.Trim() + "%') " + "or upper(STYLE_NO)like upper('%" + objAccessoriesOrderModel.SearchBy.Trim() + "%')) " + "or (lower(ITEM_CODE) like lower( '%" + objAccessoriesOrderModel.SearchBy.Trim() + "%') " + "or upper(ITEM_CODE)like upper('%" + objAccessoriesOrderModel.SearchBy.Trim() + "%')))"; } //else //{ // sql = sql + "and (ORDER_DATE like TO_DATE( '" + objAccessoriesOrderModel.SearchBy + "', 'dd/mm/yyyy') " + // "or ORDER_DATE like TO_DATE('" + objAccessoriesOrderModel.SearchBy + "', 'dd/mm/yyyy') )" + // "or (DELIVERY_DATE like TO_DATE( '" + objAccessoriesOrderModel.SearchBy + "', 'dd/mm/yyyy') " + // "or DELIVERY_DATE like TO_DATE( '" + objAccessoriesOrderModel.SearchBy + "', 'dd/mm/yyyy')))"; //} /*"or (TO_CHAR (PURCHASE_DATE, 'dd/mm/yyyy') like '" + objFabricModel.SearchBy + "' or TO_CHAR (PURCHASE_DATE, 'dd/mm/yyyy') like '" + objFabricModel.SearchBy + "')" +*/ //sql = sql + " ORDER BY sl"; sql = sql + " ORDER BY ORDER_DATE 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 DataTable GetTrimsRecordForEdit(TrimsAccessoriesOrderModel objAccessoriesOrderModel) { DataTable dt2 = new DataTable(); string sql = ""; sql = "SELECT " + "ROWNUM SL, " + "TO_CHAR(ORDER_DATE, 'dd/mm/yyyy') ORDER_DATE," + "ITEM_ID, " + "ITEM_NAME, " + "ITEM_CODE, " + "ORDER_QUANTITY, " + "UNIT_ID, " + "UNIT_NAME, " + "TO_CHAR(DELIVERY_DATE, 'dd/mm/yyyy') DELIVERY_DATE," + "REMARKS, " + "TRAN_ID, " + "SUPPLIER_ID, " + "SUPPLIER_NAME, " + "STORE_ID, " + "STORE_NAME, " + "UNIT_PRICE, " + "STYLE_NO, " + "UPDATE_BY, " + "UPDATE_DATE, " + "CREATE_BY, " + "CREATE_DATE, " + "HEAD_OFFICE_ID, " + "BRANCH_OFFICE_ID " + "FROM VEW_TRIMS_ACCESSORIES_ORDER WHERE HEAD_OFFICE_ID = '" + objAccessoriesOrderModel.HeadOfficeId + "' AND BRANCH_OFFICE_ID = '" + objAccessoriesOrderModel.BranchOfficeId + "' "; //if (!string.IsNullOrEmpty(objAccessoriesOrderModel.GridOrderDate)) //{ // sql = sql + "and ORDER_DATE =To_Date( '" + objAccessoriesOrderModel.GridOrderDate + "', 'dd/mm/yy')"; //} //if (!string.IsNullOrEmpty(objAccessoriesOrderModel.GridItemId)) //{ // sql = sql + "and ITEM_ID= '" + objAccessoriesOrderModel.GridItemId + "'"; //} //if (!string.IsNullOrEmpty(objAccessoriesOrderModel.GridItemCode)) //{ // sql = sql + "and ITEM_CODE = '" + objAccessoriesOrderModel.GridItemCode + "'"; //} if (!string.IsNullOrEmpty(objAccessoriesOrderModel.GridItemId)) { sql = sql + "and TRAN_ID= '" + objAccessoriesOrderModel.GridTranId + "'"; } sql = sql + " ORDER BY ORDER_DATE DESC"; OracleCommand objCommand = new OracleCommand(sql); OracleDataAdapter objDataAdapter = new OracleDataAdapter(objCommand); using (OracleConnection strConn = GetConnection()) { try { objCommand.Connection = strConn; strConn.Open(); objDataAdapter.Fill(dt2); dt2.AcceptChanges(); } catch (Exception ex) { throw new Exception("Error : " + ex.Message); } finally { strConn.Close(); } } return(dt2); }